This reference manual covers the mounting, connecting and programming of client applications which use a Lava Database as a storage mechanism. It does not cover the installation and administration of a Lava Database - for information on these topics consult the Lava Installation and Administration Guide.

This manual is targeted at programmers, power users and administrators who wish to :

• Obtain detailed information on specific interfaces and mechanisms in the Lava Database

• Learn in-depth technical detail on the operation and design of the Lava Database kernel

This PDF document is extensively hyperlinked. In order to derive the best usage from this document, note that the Acrobat PDF reader has a “back” button (just like a browser) located in the toolbar, which looks like this :

After following a hyperlink (any blue underlined text) by left-clicking on the hyperlink with your mouse, clicking the “back” button will return you to the hyperlink just accessed. The “back” button will work through multiple levels of links.

The “back” option can also be found in the mouse right-click pop-up menu, named “Go to Previous View”.

Note that as of Acrobat 6, the “back” option is no longer presented in the pop-up menu, and the toolbar looks slightly different :

Also, the toolbar is not displayed by default, and must be activated in the Toolbar options as follows :

There is a keyboard shortcut for the Previous View function, which is Alt+Left Arrow

The manual is divided into a number of sections, each of which addresses a specific topic.

This section should be read by any prospective power user of a Lava Database. It introduces major features, requirements and constraints of the Lava Database, and explains important concepts which distinguish the Lava Database from other SQL databases. All users of Lava Databases who intend to interact with the database through any of the interface mechanisms should at least read the Key Concepts subsection.

The Lava access privilege system allows control over user access to groups of tables (schemas) or individual tables, as well as limitation of certain other facilities in a Lava Database. This section describes the privilege mechanism and provides a comprehensive list of privileges available in the Lava Database.

This section covers usage of the Lava API to access low-level procedures for high speed and powerful interaction with the Lava Database. The topics covered are of an advanced nature, and will only be of interest to application designers and programmers, and to some advanced administrators who wish to acquire in-depth understanding of the operation of the Lava Database.

The system schemas provide information regarding the Lava management mechanisms and system tables which may be used to obtain information on database operation and user objects. This section will be of interest primarily to administrators and power users, but will also be consulted from time to time by application designers and programmers.

The API structures are intended to be used for reference only, and are comprehensively cross-linked and hyperlinked from the Lava API section.

The Lava error codes are presented for information and reference only, and may be consulted when errors are reported by Lava API procedures.

The code examples will be primarily of interest to programmers who wish to examine examples of working techniques to achieve specific results using the Lava API. Examples are provided in several programming languages and cover a range of Lava programming techniques.

The SQL examples provide a number of working examples of SQL statements ranging from simple to advanced, illustrating usage and capabilities of Lava SQL. It will be of interest to especially administrators, but also to programmers and power users who intend to use SQL to interact with and control the Lava Database.

All database objects (schemas, users, tables, indexes, synonyms, sequences...) are unlimited in number, except for limits on disk storage.

Total database size is limited only by disk storage on the server, but the client space is also limited in terms of client workstation capacity. As the Lava Database is a distributed database, Lava clients run a portion of the database on the client workstation. This implies that client workstation capacity does play a role in determining how many tables may be distributed to the client - limits in workstation memory and free disk space will reflect on Lava client capacity.

Client-based backup is supported even for relatively large databases, but there is a per-table limit of 2.1 Gb. Tables larger than this limit can only be processed by the backup system in exclusive mount. This limit only applies to individual tables - the total database size that may be backed up via a client connection is unlimited provided that no table exceeds this limit. Obviously, the total backup size is limited by the amount of free space available on the backup drive.

Table size is essentially unlimited (only bounded by hard disk size), with the exception of virtual tables which are limited by the amount of memory on the server (or client, in some cases).

Row size in all tables (with the exception of variable length columns, which are dealt with separately) is limited to a maximum of 15000 bytes. This excludes any variable length extensions to varstrings, but includes the varstring base length. Individual columns are limited to 1500 bytes maximum. Table rows are limited to a maximum of 500 columns.

A row may have any number of variable length columns, provided the sum of the base lengths of these columns (the portion stored within the originating row) does not exceed 15000 bytes less the other non-variable columns. The variable portion of these columns may be any length up to, but not exceeding, 100x10⁶ bytes (100 Mb).

Variable length columns may not be used as filter columns for any SQL or search operation.

A Lava Database is limited in total size to the space available on a single logical volume. This limit results from pragmatic design decisions taken to reduce database kernel complexity. At the time of writing, a well constructed server will support several 1 Tb hard drives, which may be striped into a single logical volume - this allows a potential database size of up to 7 Tb currently. This is viewed as a non-restrictive limit, especially as this limit is bound to increase rapidly in years to come provided current trends continue.

The Lava Database is highly dependent on memory for both performance and support of large databases. For this reason, the server should be configured with no less than 4 Gb of memory - more if a 64-bit version of Windows is in use on the server.

If you require access to the Lava Server through a formal Internet firewall or smart router, you need to provide access to the ports used by the Lava Server.

The ports 28500 and 28520 should be unblocked to permit Lava clients to connect to the Lava Server through such a firewall.

Windows XP, Windows Vista, Windows Server 2003, Windows Server 2008 - both 32-bit and 64-bit versions are supported.

Windows XP, Windows Vista or any server version from 2003 onward. Both 32-bit and 64-bit versions are supported.

Both uniqueness constraints as well as range constraints will be supported in future releases. Together with range constraints, domains (restricted column types) will be implemented to allow consistency between tables.

Although stored procedures are fully supported, and there is limited support for invoking stored procedures through SQL, using stored functions in SQL is as yet unsupported. This functionality will be integrated in an upcoming release.

The implementation of support for Qubik Lava access from within HTML for web page support is in process and currently in Beta testing. Full integration of this functionality will be released within the next few months.

The design and majority of the implementation of Time-domain functionality has already been completed. Release of this functionality is projected within the next year.

Similarly to the object-level access restriction already in place, column-level access control will be provided in a future release. This will allow individual users to be restricted in terms of access and update to specific columns in a table.

Definition and maintenance of views on data tables is partly implemented but not yet available through SQL. The required extensions for this functionality will be implemented soon. C

Facilities and support for nested schemas is already incorporated into the system tables. Completion of the implementation of this functionality is anticipated within the next 6 to 8 months.

The following concepts are central to the operation of the Lava database. Although much of the design and implementation of the Lava database is similar or, in some cases, identical to other SQL server databases, there are a number of important differences. These concepts illustrate the differences and key issues important to understanding how a Lava database operates.

Array Access to Virtual Tables	Defining and using pointers to arrays of structures to access table data directly
Boolean Variables	Dedicated TRUE / FALSE variable type
Column buffer	Lava equivalent for a table index
Column Sequence	The sequence (index) of a column within a table
Control file	The single most important Lava system table
DataGrid	An instant table display - extended list control
Distribution	Table and data distribution to client databases
Distributed Client	Mechanism for operating a client database with transparent server communication
Foreign keys, relations and joins	Lava approach to inter-table relations and join syntax, including inner and outer joins
Index	Lava indexing through column buffers
Join methodology	Lava approach to inter-table relations and join syntax, including inner and outer joins
Julian Date	A numeric date format used throughout the Lava database
Mount mode	The mode in which the database is mounted - can be Server, Satellite, Client or Exclusive.
Object / Object ID	The object concept and object types
Primary Key	The Primary Key of every standard table in a Lava Database is predefined as the row ID of the table
Physical Tables	Permanent disk-based tables, similar to a conventional table in a regular database
Range Buffers	A range buffer is a special-purpose range-limited column buffer
Raw Tables	Packed memory tables
Relations and joins	Lava approach to inter-table relations and join syntax, including inner and outer joins
Result set	Result tables for SQL queries
Return code (rc)	Error code philosophy, implementation and interpretation
Row ID /ID	Row identifiers : meaning and usage
Row status	Current and deleted rows, the deletion mechanism and row re-use
Schema	Grouping and isolation mechanism for sets of tables
Session / Session ID	Database sessions and their identification
SQL Command Execution	Logic behind execution of SQL commands in a Lava database
Stack Tables	A feature in the Lava database which allows tables to be created which behave like stacks, and offer extensible stack operations
System User	The system user is the only default user created with a new database
Transaction frame	Transactions, commit and rollback, client and server implications
User	User accounts and constraints
VDT	The Version Date Time concept and usage
Version Columns	A standard set of columns added automatically by the kernel to any non-raw table
Virtual Tables	Memory data tables

The Lava database kernel supports three primary table modes. The first, Physical Tables, are analogous to the storage method used in all other SQL server databases. The second, Virtual Tables, are unique to Lava. Virtual tables are defined strictly in memory, and the data is of course transitory. The third, Replicator Tables, are also unique to Lava - these are physical tables which are “shadowed” to memory for high-speed access.

For Virtual Tables defined by the user and created on the client database, it is possible to gain access to the table through a pointer to an array of structures, where the structure type coincides exactly with the format definition (column information) for the virtual table. This may be achieved in one of two ways - the first is through the procedure AssertTablePointer, which informs the Lava database kernel that the array pointer is to be maintained whenever the memory allocation for the virtual table changes. The second is during creation of an instance table through use of the CreateTableInstance procedure, at which time an array pointer may be specified.

When either of these techniques is used, the Lava database kernel maintains the pointer to the array whenever the memory allocation for the virtual table in question changes for whatever reason - for example, if multiple AddRow actions are performed, or a SQL “insert into as select” results in many rows being added to the virtual table, the current memory allocation may prove to be inadequate for the new row requirement of the table. In this case, the memory allocation will be enlarged, which, depending on current memory usage, may result in the memory allocation for the table being moved to a different area of memory. In this case, the Lava database kernel will automatically update the nominated array pointer to the new memory address for the virtual table.

Note that although it is permissible to read and write existing rows from and to the virtual table in question, the programmer should take care not to overstep the current row boundaries on the table - this will most likely result in a memory access violation. The onus is on the programmer to ensure at all times that the array indexes in use are limited to the number of actual rows in the table - this can easily be determined through use of the TableRows procedure.

When new rows are to be added to the virtual table, these MUST be added using the AddRow procedure - adding rows via array access will almost certainly end in an access violation, as the Lava database kernel is unaware of the addition of new rows to the table and cannot validate memory allocation.

If the table in question permits row deletion (see DeleteRow) which depends on whether a Row status was defined in the table format, the user should take care when using array access to read data from the table to validate that the row is current.

In all Pascal language dialects (including Modula 2 and Oberon) the boolean type is separated from conventional numeric variables, and has valid values of TRUE and FALSE only, represented by 1 and 0 numerically. The size of the variable is a byte.

All current SQL databases use indexes (formerly known as indexed-sequential access) to provide random access (more correctly phrased keyed direct access) to table data within the database.

In contrast, the Lava Database does not use indexes for this purpose. Instead, a new concept in data access is employed, termed column buffers.

Column buffers are sorted memory-based binary-searchable reference arrays which allow ultra-fast access to any row of the table referenced by the column buffer.

Whereas index are disk based, slow to maintain and slow to construct, column buffers are very low cost to maintain and fast to construct. Indexes across two or more columns are needed for complex multi-column filters and joins to the tables represented, whereas column buffers always represent only one column, as range buffers are very fast to construct for special multi-column filter purposes and allow much faster access with much more flexible application than comparable indexes.

Column buffers may be specifically configured and created by the designer or programmer, but this is not required. The Lava kernel will, where a requirement is identified, automatically create a column buffer on a given table column instead of performing an exhaustive full-table scan of the data.

Similarly to the concept of an object ID referencing tables through a unique numeric reference identifier, column sequences are a numeric reference identifier used throughout the Lava API to uniquely reference a column within a table.

A column sequence is a 1-based index into the columns for a table (i.e. the first column in the table is declared to be column sequence 1) so that a pair of numbers, the object ID and the column sequence, uniquely identify every column in the entire database.

The Lava Control File (System_ControlFile, located in the System schema) is very useful to any advanced SQL user, as it presents a single reference providing object (table) identification, many important object attributes, and information ranging from deleted row counts to row size.

In order to access the control file, the user must have at least Select access to the System schema.

The Lava database includes a set of standard utilities provided to render certain commonplace tasks easy. One of these is an easily configured datagrid, which has a very simple interface and may be used either as a conventional grid or can be reconfigured into a data tree with highly flexible applications.

The datagrid is too complex to discuss fully in a concepts chapter - for detailed information on the datagrid and the associated data tree see the worked example Datagrid Construction in the examples section.

In contrast with other SQL or relational databases, the Lava database is designed around the concept of data distribution. Although the Lava Server is capable of performing similar functions to a conventional SQL server, and can execute SQL queries and commands on the central database, this is not the default or recommended mode of operation for the database. The most efficient way to operate the database is in Distributed Client mode, where the majority of the commands are executed on the client rather than on the server. Any queries are answered directly from data distributed to the client by the server, while updates which are performed on the client are distributed back to the server, and from the server to all other active clients.

In its native mode, a Lava database application runs not as a client-server application, as would be the case in most SQL server applications, but as a distributed client. In this mode, one or more schemas required by the client application are distributed to the client either completely or in part - depending on requirement and database size - and the client is designed and coded as if it has exclusive access to the database.

Queries directed to the database are, in most cases, answered directly by the client database, as all or most of the data relating to the client application is available on the client database itself.

Updates to data tables are also performed locally. The distributed client mechanism within the Lava kernel gathers these updates into small sets of updates which are then distributed back to the Primary server. The server, in turn, re-distributes these updates to any other active clients, thereby ensuring consistency of data between all clients.

This method of operation ensures that each client responds with maximum speed to any request, and also prevents any complex operations performed by one client (such as a large report or bulk update) from influencing the speed of response of any of the other client applications.

The Lava database does not implement indexes in the conventional way. Instead, a mechanism referred to as a column buffer is used to improve flexibility and performance.

For details on the relational integrity mechanism in the Lava Database, see Relational Integrity

The Lava approach to joins and inter-table relations is different from the general relational database approach to this topic. Starting from the conception of the Lava database kernel, a new and more rigid approach (explained in detail below) was applied to relations, for three major reasons :

• The first is relational integrity. In order to avoid the problem of relational key columns changing during the database lifecycle, the entire concept of related columns was revised.

• The second was performance. Using the Lava approach, the database becomes far more efficient and significantly faster.

• The third was the issue of relational design. Using the Lava approach to relations, it is more difficult to build inappropriate relations into the database design which fundamentally violate normalization rules.

In order to implement these design goals, a simple yet sufficient and effective strategy for inter-table relations was selected. The mechanism allows only one method for relating two tables, namely a join column in the many (child) table which unconditionally links to the row ID in the one (parent) table, which is automatically the primary key in the parent table. An example would be :

Consider the three tables in the illustration Simple join example. There are two related tables; Sys_Event_Class has a 1:M (one to many) relation with Sys_Event_Type, as does Sys_Event_Group. Each of these is achieved by placing a join column (Class_id and Group_id respectively) in the Sys_Event_Type table. Each of these columns is required to be a 32-bit integer (dword). These join columns contain, in all cases, the value of a row ID in the related table. For entries in the Sys_Event_Type table where no join entry exists (or is yet linked) the join column is null (0).

Several questions should immediately occur to you in connection with this method. These are answered superficially below, and the more complex ones are treated more comprehensively elsewhere in this reference.

A. In general the answer is no, if the columns are with respect to a single specific join. As the content of the join column is a row ID in the target table, and this is always unique, having more than one column for a single join is redundant as each would contain the same value. (See below for an example of a two-column join and the equivalent Lava implementation).

However, if more than one join is in question (for example, an exchange rate table linking both to the source currency and the target currency, source and target currency both being entries in the same currency table) then one should define two columns (say SourceCurrency_id and TargetCurrency_id) in the rate table, each relating to a specific and separate join to the currency table.

A. By using a join table. If table A has an M:N relation with table B, this is achieved by specifying a join column in table A (say A_J_id) which has a many to one relation with a separately defined join table (say J), and similarly table B contains a column (say B_J_id) which also has a many to one relation with table J. This successfully implements the M:N relation. Any attributes which belong to the join are now added as columns to the join table, J.

Q. What do I do in cases where my join was formed between two (or more) columns in the source table and two (or more) columns in the target table? (For the case where this implements a M:N join, see the previous example).

A. The easiest way to answer this is by way of an example. Consider the diagram provided, Two-column join. Clearly, in this case, both join columns are essential. However, what this join is in fact illustrating is a normalization error. The corrected design is provided below in the diagram Normalized join.

In the properly normalized design, not only do we obtain a significant performance enhancement through a forced optimization of the join between Customer and Branch, but we also are forced to normalize the Suburb and City tables correctly, which means that any attribute columns to the original non-normalized Branch table (in the diagram Two-column join) will now be correctly attributed where they belong - in the non-normalized example, unavoidable duplication of attribute values belonging to the City table would occur. At the very least, even if a City and / or Suburb table were implemented in the non-normalized case, duplication of the City name itself would occur, possibly creating erroneous mis-spelt city entries. In addition, maintenance of the original Branch table would rapidly become a nightmare, as there would be any number of City columns to correct if an error or change were to have to be made to the data (for example, if a city changes its name - uncommon, but it does happen; consider Leningrad becoming St Petersbug). In the normalized example, no duplication, no redundancy and no maintenance problem can arise.

Q. What about the case where a join absolutely requires more than one column specified between two tables in order to filter or limit the responses?

A. No problem. The above issues relate to database design - in design-relational terms only one join on the row ID of the foreign table is permitted, but in SQL you may use any number of join columns for filtering purposes. In fact, it is not even a requirement that a relation has been formally registered in the database in order to be able to perform a join between the tables in SQL - you may specify any number of any columns of any type in a SQL join between two tables.

A. Absolutely. In the SQL engine, a join is merely a match between the values of two columns in joining tables. These joining columns may have any type except boolean (boolean joins would be too expensive - by definition there are only two boolean values, 0 and 1, therefore a boolean join is half as bad as no join at all. Of course, you may use a boolean column as a filter in a SQL select.) and the SQL engine will simply match the values in the columns to arrive at the join. The only preclusion with string columns is in terms of Lava-maintained relations; these can only be between numeric columns where one is the row ID of the parent table. This having been said, string joins are not recommended as they are slower to process (and less definitive) than numeric joins, but if strings are all you have in your existing data, you can certainly use them.

In terms of the method implemented in Lava for inter-table relations, the issue of relational integrity is simplified quite significantly. As it is quite impossible for the join column to “change” (in terms of a renaming or alteration of spelling), there is no update cascade integrity (on modification) - if the join column is updated, this is merely a selection of a new related entry in the join table, and no cascaded update is required.

The provided integrity facilities relate strictly and only to deletion of linked entries in the related tables, and therefore are limited to delete restrict and delete cascade. The first will disallow a deletion of the parent entry if related entries in the child table exist, and the second will delete any related child entries if the parent entry is deleted.

With due respect to the efforts exerted by the ISO group on SQL syntax, we have decided to stay with the join mechanism conventionally used prior to the announcement of SQL-92. An example is provided below :

The join in the example, sc.id = so.schema_id, is stated - as was conventionally the case - in the where clause of the select.. As can be seen from the example, filters are also stated in the where clause.

The syntax for outer joins is simply an addition of an appended keyword, OJ or OUTERJOIN (both are recognized, and are case insensitive) to that end of the join which may require allowance of a missing join entry. For example, in the case above, the sensible outer join would be sc.id = so.schema_id oj, which would include any objects not belonging to (or unlinked to) a schema.

For purposes of compatibility with auto-generated SQL (such as that used in Crystal Reports, for example) certain forms of the new SQL-92 join syntax are supported - however, these are not considered native to the SQL engine and are therefore not explicitly documented.

In Exclusive mode, the database kernel does not accept connections from clients; in other words exclusive mount allows only the process which mounted the database to connect to that database (this is typically the Lava Query utility). The reason for this is that Exclusive mount allows (amongst other administrative tasks) Restore actions to be performed on the database, which requires that no client sessions be active in order that the restored data is consistent on completion.

Server mode is used only by the Primary Server - in other words, every distributed database network will have only one Primary Server, which is mounted in Server mode. This mode allows connections from both Lava Clients and Lava Satellites.

Satellite mode is used to mount Lava Satellite Servers. A Satellite Server is identical to the Primary Server from the perspective of a Lava Client - whether the client is connected to the Primary Server or a Satellite Server it functions completely identically.

• There is a low-bandwidth link (such as an Internet link or a VPN via the Internet) which would not support the number of clients wishing to connect from a particular site. A Satellite Server is configured at this site, which connects to the Primary Server and, in turn, provides distribution facilities to any clients at the site.

• The load on the Primary Server would be too high for a single Windows server to cope. A ring of Satellite Servers may be configured around the Primary Server, each accepting a smaller number of Client connections. In this way the load is shared between multiple Lava servers, and the load on the Primary Server drops to acceptable levels.

Satellite Servers may connect either to a Primary Server or to another Satellite Server - which, in turn, may connect to the Primary Server.

Client mode is used by all conventional client applications. A Client-mode Lava database still performs all the functions of a relational SQL database, but does not accept client connections itself. It is the end of the chain of distributed servers. Clients may connect either to a Satellite Server or to a Primary Server, with no discernible change in operation of the distribution mechanism.

In order to present a uniform interface to many of the Lava API interfaces which deal with different forms of entities in the Lava database, and to have a consistent way to address various elements within the database, many lava entities are addressed through an object entry in the Sys_Objects system table. These entries can represent tables of various kinds, indexes, stored procedures and functions, and in future releases other object types will be enabled and supported.

In API terms, the unambiguous identifier for an object is the object ID. This is the row ID of the entry for the object in the Sys_Objects table.

Thus, instead of many interfaces which process table requests receiving a table ID, the object ID is used instead. This allows easier and more consistent interpretation of the identifier for various interfaces and various object types, as a single point of departure is used throughout.

A physical table is the closest object in a Lava database to a conventional database table in a SQL database. Physical tables are stored on disk by the Server database, as with conventional database implementations.

By contrast, virtual tables are stored entirely in memory, typically on the client database.

Physical tables may either be stored only on disk (as is generally true with most relational databases) or may be fully replicated (see replicator tables) for performance.

The Primary Key for every non-Raw table in a Lava database is its row ID, which is a mandatory column and is system maintained. See row ID for further information. See also relational integrity as defined and implemented in a Lava database for information on primary and foreign keys and relational joins.

Range buffers represent the specific result set for a particular multi-column query on a table. For example, if a seek is performed on a table through the following SQL segment :

a range buffer will be created from the column buffer (index) for the type column, containing only those entries which match the filter requiremnts.

As all column buffers in a Lava database are composed strictly of one column, it is not possible to directly answer a multi-column query which would require particular constraints on more than one column of the table.

In order to provide fast and consistent response to this form of query, the Lava kernel creates range buffers at the time of the query which answer the exact query parameters. These range buffers are transitory, and cannot be created by the user except indirectly through a query.

Range buffers are created automatically on demand by the Lava database kernel when a multi-column query is performed on a table, and are discarded when the query is terminated.

By default, Lava tables support all regular row operations, including transaction framed update and row delete. In some cases, a particular application may not require update or delete facilities to a table - specifically if the table is to be created in a single pass, and then used subsequently for read purposes only.

Secondly, there may be a requirement for a table to have no vacant slots. In these cases, especially where such a table is to be accessed directly through array access (see Array Access to Virtual Tables for further information), it may be desirable to have no vacant rows which need testing for in process loops.

Thirdly, the requirement to process transaction frames adds significant overhead to the processing requirement for an AddRow command. If this functionality is disabled, rows may be appended to a raw table much faster than would be the case with a conventional, framed, table.

Finally, raw tables present a facility which is not available for conventional tables. Due to the fact that raw table rows do not include an ID column in the row, it is possible to present a facility for inserting a row into the table at an arbitrary position, shifting up all subsequent rows. Since these rows do not contain an ID column, this does not cause inconsistency between the row and its new slot in the table. This facility allows for table manipulation such as sorted insert, or specific sequencing of entries, which would otherwise be impossible.

Raw tables are by their nature transitory - they are virtual tables by definition, and since transaction framing is not supported, they are essentially session-bound in terms of content. Raw tables are intended and can be used only as a data manipulation mechanism.

A result set is a table created by the Lava database kernel in response to a SQL query (select statement). In contrast with most SQL databases, the results of such a query are not presented as a block of data, but rather as a conventional SQL table, which itself may be queried or addressed through array techniques (see Array Access to Virtual Tables for further information),

This is true whether the query is executed on the client or on the server - a result table is unconditionally created.

Result sets are always defined as Raw Tables, as they are not intended to be permanent and thus are not intended for updates - only queries are permitted. It is possible, however, to use this data to provide entries to a permanent table through use of the insert into SQL command, for example.

Throughout the Lava system, return codes are standardized and have a uniform implication wherever encountered.

In all cases, a return code of 0 (zero) implies no error, i.e. successful completion.

A return code greater than 0 is an event ID, that is the row ID of an event entry in the system event log (Sys_EventLog). This entry in the event log will specify the error (or potentially the series of errors) which occurred during execution of the request.

A return code less than 0 is an error constant, as specified in the appendix Lava Error Codes

The Lava Database kernel is designed around a number of core table mechanisms, of which one of the most important is the concept of a unique row ID for each row in each table.

The row ID is the ordinal of the row in the table, with the first valid row being row one. Thus, the row ID is simply a numbering of rows in the table, counting all rows including any deleted rows (see Raw tables for information on non-deletable rows in packed tables).

In theory, since the row ID is identified by the database as part of the fundamental row access mechanism, it is not even necessary for the row ID to be represented in the table data. However, the moment the row data is removed from the table (through a GetRow, for example) into a local buffer, the row cannot be identified unless the row ID is stored within the column information.

For this reason, the first two columns of every standard, non-raw table are defined as being the row status and the row ID, as follows :

The ID field as depicted above is system maintained (i.e. on adding a new row to a table, either through a SQL insert command or through AddRow, the system asserts the correct value in the ID column).

In addition, the ID column is defined as the automatic and only Primary key for every table. All relations managed by the Lava Database with this table as the parent table, are defined from the ID column of the table as the primary key of the relation. See Relational Integrity for further information on this topic.

The Lava database deviates from conventional database practice in the implementation of a user-visible row status column (for tables which permit conventional deletion - see also Raw Tables for further information). The row status (a single byte-wide column, which is always the first column in the table) indicates the status of the row, including unused, current, deleted, locked, and owned. Amongst other advantages, the row status permits undeletion (recovery) of deleted rows provided the row has not yet been re-used (it is also possible to disable row re-use on any table). In addition, the row status is critical to the Lava implementation of distributed client operation, which allows the database to maintain the most current row information even on rows which are still within a transaction frame (uncommitted) on the originating client database.

A schema is an encapsulating or grouping entity that allows tables to be accumulated into a coherent set, and forms a primary attribute of all tables in a Lava database.

Schemas allow for a number of important facilities to be arranged in a more meaningful way. Amongst these are :

• Efficient backup strategies, as sets of tables belonging together can be backed up as a single group.

• Grouping of the tables for a particular application or application module, allowing reduction of visible data

• Isolation of tables to allow simple access permission strategies to be implemented; access permission may be granted or denied to an entire schema rather than the individual tables contained by the schema

• Distribution of data to clients may be specified by schema, providing an efficient way of listing the tables required by a particular client application

Each user, and therefore each session, has a default schema - tables within this schema may be accessed without nominating the schema for the table. Tables from non-default schemas may also be accessed (provided access permission exists for these tables) by nominating the appropriate schema when addressing the table.

In order to access a Lava database, the user must open a valid session. This session defines the nature of the connection to the server (exclusive, client) as well as defining the user account being used to access the database. This, in turn, will define the access privileges accorded the session.

When a valid session has been created (see OpenSession), an entry in the Sys_Sessions table is created for the session which defines the session attributes. Almost all interaction with the database requires specification of a valid session entry; this is achieved by providing the session ID, which is the row ID (or ID) of the session entry in Sys_Sessions.

SQL commands are, at code level, executed through use of the LavaCommand API function. This function returns an object ID which specifies the result set returned by the command, if any.

As far as can be seen by the end user, the Lava database executes SQL commands in the same way that any SQL database does. A command may be issued to the database, and after processing either an update is performed or the results of a query are returned.

Internally, however, the processing of a SQL command by a Lava database is very different from that of a normal SQL database. Conventionally, all SQL commands are sent to a database server, which then executes the command and responds to the client either with a success or failure code, or with a result data buffer.

In many cases, the Lava database does not send SQL commands to the server (unless specifically instructed to do so). Wherever possible, SQL commands (and, in fact, many operations including row-level operations) are executed on the client database directly. As, in many cases, the data tables which are related to a particular application are distributed to the client database, the SQL can be executed locally without incurring network delays or waiting for the Lava Server to process the command.

For many operations the use of a stack for storing and retrieving information is the natural method demanded by a particular algorithm. In order to render this form of implementation easier to code, the Lava database kernel provides a comprehensive mechanism for declaring and manipulating data tables which behave like LIFO (last-in, first-out) stacks. They are created as for any other data table, with a specific user-specified column layout, while an attribute to the table creation routine specifies that the table required should be a stack table.

Operations provided include push and pop, which behave as expected for a stack. A clear is also implemented for convenience.

Stack tables are by default transitory (although it is possible to declare a permanent stack table by manipulating the table creation attributes), and should not be manipulated using conventional table update techniques unless care is taken to ensure that such manipulation does not violate the principles of stack operation.

It is, however, permissible to use stack tables in SQL select statements, and access through array techniques is also permitted (see Array Access to Virtual Tables for further information),

On creation of a new database, a single user - SYSTEM - is created in order to allow further manipulation of the database by the administrator. The default password for the System user is MANAGER (case insensitive) - this should be changed by the administrator after creation to prevent unauthorized access to the database at system level.

The System user as full access permissions on the entire database, including any schemas or tables created subsequently. The System user also has full permission to create other users, and to grant or revoke permissions on any user.

The System user cannot be dropped, and access permissions cannot be revoked for the System user. Only the password of the System user may be changed.

Transaction frames, including nested transaction frames, are fully implemented in the Lava database kernel. For all non-raw tables, execution of any command (either at row level or through SQL) which modifies the table content will commence a transaction frame if none exists, or will add to the content of an existing transaction frame.

Transaction frames and nested transaction frames may be explicitly declared through either the API or through SQL.

By default, if a session is closed without a transaction frame having been explicitly committed, a rollback is performed on the pending transaction frame.

Both commit and rollback may be explicitly executed for a nested transaction frame only, thereby allowing partial rollback of an unsuccessful sub-transaction without the major transaction frame having to be aborted.

Both rollback and commit actions may be performed through SQL or through the API.

The concept of a user in a Lava database is very similar to that in most SQL databases. Users are allocated passwords which ensure protection of secure data, and each user is allocated certain permissions to access given schemas or tables in the database for read and/or write access. Specific permission may also be granted for operations such as truncate or drop.

Lava usernames are not case sensitive, and neither are passwords. Both usernames and passwords are limited to 50 characters in length. In addition, usernames must be unique within a Lava database.

In order to create a user account and manipulate the user’s access permissions, adequate permissions are required by the executing user. By definition, the system account (SYSTEM) has full permission on the database, and may create and modify other user accounts in any way.

Any number of users may be created in any Lava database, regardless of licensing. The user license on a Lava database only applies to the number of users which may log into the database concurrently, not the number of users listed in the database.

The acronym VDT expands to Version Date Time, and is used as the uniform Lava date-time stamp whenever chronology is logged in the Lava database.

The current VDT as defined by the Lava Server may be obtained using the GetServerDateTime procedure.

The VDT is stored in a longreal (8 byte float) in which the integer portion represents a Lava Julian date, and the fractional portion represents the time of day logged to the millisecond.

A Julian date is a numerically coded date, from a (normally arbitrary) starting date, which varies from application to application. In the case of the Lava Julian date, day 1 is January 1^st, 300 CE. This date was selected as this is the earliest date for which a Julian number can simply be calculated from a Gregorian calendar date.

The VDT can be decoded through use of several Lava procedures; such as Format_VDT (which returns a string with the date and time coded in text format), and GregorianDate (which returns individual date fields from the integer portion of a VDT). The time fields of a VDT can be decoded using the Extract_VDT_Time procedure

The Version Columns are a standard set of columns added (prepended) onto every table created in a Lava Database for non-raw tables. These columns allow the database to support data distribution, and are made visible to the programmer or administrative user as much of the data presented in these columns can be of value.

In most current databases, the only form of data table available is one which is stored on disk (referred to as physical tables in Lava terminology). Although most databases will perform some form of memory caching of disk data, this is often outside the control of the programmer or designer.

In the Lava database, several mechanisms exist to provide programmer or designer control over caching of data. The virtual table mechanism is one of these.

A virtual table is in many respects identical to a disk table, in that it stores data, allows retrieval and update of data rows, and may be used in SQL queries and other commands. The difference lies in two major contrasts with normal server-based disk tables.

The first is that virtual tables are typically created on the client (although virtual tables can and do exist on the server), and are not only locally accessible - yielding vastly improved data access rates - but are also accessible as an array of data for purposes of reference (this typically excludes update, creation of rows or deletion, as these actions would have implications on any indexes which must be performed by the database kernel). See Array Access to Virtual Tables for further information,

The second is that the data is transitory. Unlike disk-based tables (Physical Tables), the content of a virtual table is lost when the client disconnects. Although this is not suitable in all cases, as much of the data generated or used by the client must not only be stored permanently but also be communicated to other clients, in some cases where interim datasets are used to compile final results, or session-bound data is required, virtual tables can be an extremely flexible and fast method for doing this.

One of the large motivations for the mechanism of object oriented programming was to provide a method for storage of interim data (the object). Virtual tables provide a faster, more highly visible and accessible method for storing and retrieving interim data during program execution. In addition to many other benefits, during debugging the entire virtual dataset is visible both in the debugger (as an array) and for external query (through SQL) providing enormous advantages in testing and correcting software.

Lava SQL is derived from a number of current SQL dialects, as well as the ISO SQL-92 and SQL-99 specifications. In general, the SQL syntax implemented in the Lava SQL engine is very similar to (and in many respects the same as) most of the popular SQL implementations currently in use. In several cases, variants of commands are provided for to allow commonly used syntaxes with slightly different forms to function correctly.

There are, as with any SQL engine, certain limitations and unique characteristics which distinguish Lava SQL from other implementations. See the paragraph Relations, foreign keys and inter-table joins and specifically the sub-paragraph SQL Join Syntax for information on the largest deviations from standard or ISO-92 SQL.

In addition to the above, Lava SQL is distinguished from other implementations of SQL in that the Lava database is distributed, implying that the client runs a complete and independent Lava database. This allows for commands to be executed either on the client or on the server, as required by circumstances and application architecture. The following paragraph describes these options.

Unlike all other SQL databases, the Lava database is fully distributed. This means that every Lava client is, in fact, a fully operational Lava database, capable of performing any operation that the server can perform - including executing SQL commands.

When executing a SQL command on the client, the distributed kernel will examine the command and decide whether execution on the client is possible. If so, the command will be executed on the client as a matter of preference, as this both reduces the processing load of the server and (generally) allows faster execution and response - especially if the server is connected through a relatively slow Internet line.

It is, however, possible to force execution of a command on either the server or the client, where the programmer has special knowledge. For example, if the command to be executed is very complex and would execute far faster on the server (assuming that the server is a much more powerful machine than the average client workstation) and of course assuming that the greater majority of the client commands execute on the client, thereby freeing up the server to execute exactly these most demanding SQL statements. In order to do this, a command (such as a SELECT statement) can be forced to the server as follows :

The above command will unconditionally be executed on the server, as specified by the [SERVER] directive after the SELECT command.

Note that forcing a command to execute on a particular database may cause complications. A conventional SELECT statement may be specified as in the following example :

In this example, due to the fact that there is no database directive, the client database will attempt to determine the best location to execute the command. Firstly, the client will check whether the nominated table (Customers) has been distributed to the client. Assuming that this table has not been distributed, it will automatically pass the command to the server for execution. The command will execute correctly, and return the content of the Customers table.

the [CLIENT] directive will force execution on the client database. Given that the Customers table is not distributed to the client, execution of the command will fail.

The responsibility for ensuring the correct environment for the command falls on the programmer if a database directive is specified. If in doubt, allow the database to evaluate the best location for execution.

Each column defined in a Lava Table has a specified data type, which is numerically coded in the Sys_Table_Columns system table which stores all column data for all tables in the database. The supported data types are listed below, with the numeric data types used in the column definition.

Code		Internal Datatype	SQL syntax	Description
1	1H	byte	BYTE
33	21H	Row status	ROWSTATUS
8	8H	boolean	BOOLEAN
2	2H	short integer (2-byte)	SHORTINTEGER
3	3H	integer (4-byte)	INTEGER
35	23H	Row ID	ROWID
14	0EH	Address (32-bit Pointer)	ADDRESS
4	4H	quad integer (8-byte)	QUADINTEGER
51	33H	Julian Date	DATE	Only Julian dates (dates represented as an integer number of days from 1 January 300) are natively supported in the Lava database. Although there are built-in conversion facilities to and from text date representations, these are supported only for data entry purposes.
67	43H	Time	TIME	Time is represented as a fraction of a day, in order to fully support arithmetic on date-time (VDT) variables and columns. In other words, 1 hour into the day (1AM) is represented as 1/24, being 0.04167.
55	37H	(DateTime (VDT)	VDT	A DateTime or VDT (Version Date-Time) is represented in an 8-byte Float variable (or column). The integer portion is a Julian date representation, while the fraction is a time representation as a fraction of a day (24 hours). This allows arithmetic on date-time columns or variables with guaranteed correct results.
6	6H	short float (4-byte)	SHORTFLOAT
7	7H	float (8-byte)	FLOAT
15	0FH	IP (Internet Protocol Address)	IP	IP addresses are internally stored as a 4-byte integer.
9	9H	character (1 byte)	CHAR
10	0AH	string (fixed length)	STRING
11	0BH	varstring (fixed length base with variable length extension to 2x10⁹)	VARSTRING
28	1BH	packed varstring	PACKEDVARSTRING
42	2AH	unicode string (fixed length)	UNICODESTRING
43	2BH	varunicode (fixed length base, variable length extension to 2x10⁹)	VARUNICODE
13	0DH	varbyte (fixed length base, variable length extension to 2x10⁹)	VARBYTE
29	1DH	packed varbyte	PACKEDVARBYTE
100	64H	Structure (Single-depth record comprised of basic data types)	Available in API - Future provision for SQL
-	-	Array (1-dimensional array of any fixed length type, including structures)	ARRAY nn OF simpletype	Only arrays of simple datatypes are supported - specifically, any VAR-type (such as VARSTRING) or structure is excluded.

Variable length columns are implemented in the Lava database as a fixed-length portion (length defined by the user at time of table creation), stored within the fixed-length row definition, and a variable length portion, unlimited in length, stored separately in the database and referenced to the row by the kernel using internal links.

Variable data in a given row of data can neither be stored nor retrieved using row-level operations (with the exception of the fixed-length portion). To store variable data, a PutColumn must be executed, and to retrieve variable data, a GetColumn must be executed. Using these commands, variable data of any length may be accessed.

varstring A variable length ASCII string. Although data containing nulls may be stored and retrieved, most string procedures and functions will regard the first null character as the end of the string.

varunicode A variable length Unicode string. Most unicode string procedures will regard the first double-null entry pair as the terminator to the Unicode string.

varbyte Variable data of any type, represented as an array of byte. Any values are permissible in the stipulated data length, including nulls.

The following types are listed for completeness, but are not supported in the current revision of the database.

Currency Decimal type flagged as a particular currency in the default format for the column

The current release of the Lava Database does not support null column values. This feature is provided for in the Lava design, and will be implemented by the next major release.

Taking cognisance of the effort expended on the ISO SQL standard, it is the opinion of the Lava system architects that for the majority of users the ISO syntax is somewhat clumsy and unwieldy, and the specification is not in all cases easy to interpret. In particular, the method proposed for specifying joins is, in our opinion, not as elegant or easy to understand, code and interpret as a simple filter clause in the where clause of an SQL statement. After careful consideration, and with due respect to the detail in the ISO specification, we have therefore decided to depart from this standard in a number of respects.

As a general rule, the syntax used in Lava SQL is very similar or in many cases identical to that stipulated by the ISO standards. In all cases, the ISO standard was taken into account before deciding on a particular syntax (as were a number of other de-facto industry standards used by major SQL databases in the past and present).

There are a few minor departures from strict ISO syntax in certain cases, but the only major departure is the specification of joins in the select statement. In the following example, the joins are specified in the where clause in the style adopted by Lava SQL. This should be easy to interpret, and equally easy to duplicate.

SQL-92 style joins are - to a reasonable degree - supported, but are not considered part of the core of the Lava SQL syntax.

Comments may be specified at any point in a SQL statement, and comments may be nested to any level. A comment is delimited by /* and */

The following commands are not yet implemented in the current release, but are listed as the keywords are reserved and the command syntax is finalized

This section documents all the SQL commands implemented in the Lava SQL engine. The commands have been ordered alphabetically, including clauses such as (for example) the Group By clause. Every attempt has been made to place reference hyperlinks wherever appropriate, but if the reader does not wish to read the entire SQL command reference, the best access point is through the SQL command category listing which provides a means of locating associated commands in given command domains. The hyperlinks from the individual commands can then be used to access related references.

The Alter Schema command is used to define the allowable access to the schema. This permits certain schemas with reference information only to be rendered read-only, disallowing any change to the schema content whatsoever.

The [client] qualifier instructs the SQL engine to perform the alteration on the client database only. This will not affect the status of the schema on the server at all.

The [server] qualifier causes the schema to be altered on the server to which the session is connected. This is the default operation.

If the schema is set to READONLY mode, no modifications may be made to the objects contained by the schema. This includes dropping or creating tables, truncating tables, or any table row modification such as deletion or update.

READONLY schemas will not permit data restore using the Restore command, as this will require truncation or drop of tables within the schema, which is not permitted in this case. If a schema which is flagged as READONLY is to be restored, the schema must first be set to READWRITE, after which the restore may be performed and the schema can be set back to READONLY.

If the schema is set to READWRITE, normal modifications to table content as well as creation and dropping of tables is permitted.

The Alter Table command is used to alter attributes of the nominated table, including column definitions, the table name or the table schema.

For the ALTER TABLE ... SCHEMA command, the session must in addition have ALTER TABLE privileges in the specified new schema for the table.

There is no permitted [client] variant of the alter table command, as this would render distributed data tables inconsistent with the server. As a result, if the table is distributed this command always acts on both the server and client copies of the table to ensure consistency.

If the table is non-distributed and occurs only on the server, the command acts on the server table.

If the table is defined only on the client, the command acts on the client table.

tablename In all versions of this command, the tablename parameter specifies the table to be altered. If the table is not in the session’s current schema, a schema prefix is required to fully identify the table.

Column list - spec The column list specification allows the definition of a list of column names and types to be added or modified for the nominated table. See Column List - Specification for details.

Column list - drop The column drop list allows nomination of a list of columns to be removed from the table. See Column List - Drop for details.

ALTER TABLE ... ADD : The specified columns are added to the table on both the server and the client databases if the table is distributed, or to the server or client table only if the table occurs on only that database.

ALTER TABLE ... DROP : The listed columns are dropped from the table on both the server and client databases if the table is distributed, or from the server or client table only if the table occurs on only that database.

ALTER TABLE .. MODIFY : The specified columns are modified to new column types on both the server and client databases if the table is distributed, or on the server or client table only if the table occurs on only that database.

ALTER TABLE ... RENAME : The table is renamed on both the server and client databases if the table is distributed, or on the server or client database only if the table occurs on only that database.

ALTER TABLE ... SCHEMA : The table is moved to the specified new schema on both the server and client databases if the table is distributed, or on the server or client database only if the table occurs on only that database.

If the table column layout is modified through the ADD, MODIFY or DROP forms of the command, table backups performed before the alteration will still restore to the modified table, except for added or dropped columns. In other words, if columns are added to a table and a restore is performed, the new columns will be empty after the restore throughout the table. If columns are dropped, those columns will (obviously) not be restored during the restore operation, but all remaining tables that match the column list prior to the restore will be restored correctly. If column types are modified using the MODIFY form of the command, the restore mechanism will attempt to convert the data in the backup to the new column type. If a sensible conversion can be performed, the restored data will comply with the new column type. If no sensible conversion can be performed (such as from non-numeric string data to a numeric column type) the column is left blank by the restore.

If the table name or schema is altered using the RENAME or SCHEMA forms of the command, the restore will no longer successfully identify the table and will create a new copy of the backup table by the original name in the original schema (or in the session’s current schema if the backup set does not specify a source schema).

If the table is moved to a different schema using the ALTER TABLE .. SCHEMA command, the table will be moved only if the user has sufficient privilege to alter the table in both the table’s current schema and the proposed new schema for the table. This is to avoid the possibility of the user moving the table to a schema where it is no longer accessible.

The Alter Session command may be used to modify the current schema or default backup folder for an existing session.

To modify the current schema the user must have at least READ access to the nominated schema.

newschema The name of an existing schema which becomes the current schema for the session.

ALTER SCHEMA : The current schema for the session is changed to the nominated schema.

ALTER BACKUP FOLDER : The default backup folder for the session is changed to the nominated folder path.

The nominated schema must exist, and the user must be permitted to read the schema.

Even if the user may see the schema and may change the current schema as nominated, this does not guarantee that any tables will be visible to the user in the new schema - if the user is denied access permission to all tables in the new schema, the schema will appear empty.

The current schema for this session is changed only - the default schema for the user account is not modified, and the next connect will revert to the default schema for the user. See Alter User for information on how to change the default schema for a user account.

The nominated folder must be fully specified (full file path) and the folder must exist.

The Alter User command may be used to modify the default schema or the password for an existing database user.

The specified user must be the user account for the current session, or the session must have ALTER USER privilege.

None. The user account on the server is always changed - modifying the user on the client would not be sensible, as this modification would be lost on dismount.

The default schema and / or the password for the nominated user account is modified.

The new default schema and / or new password are immediately implemented, but since there may be current sessions using the nominated user account, these sessions will continue to operate using the schema or password which were valid at the time the sessions were established. Any new sessions established on the nominated user account will see the new attributes for the user account.

The password as specified is unencrypted. On execution of the command, the password is encrypted in the system user table.

Passwords will have an expiry attribute to allow password expiry at synchronous intervals

The session must have BACKUP privilege on the schema. In addition, any tables within the schema for which the session does not have READ access will not be backed up.

The nominated schema will automatically be distributed if the backup command is executed on a client, regardless of whether the schema was distributed before issuing the command.

A backup folder for the session must have been asserted - see Alter Session. If the command is being issued from the Lava Query interface, it is possible to assert the backup folder in the command parameters.

None. The backup is always performed from the client, although the data in the backup is always server data.

encryptionkey An optional key to be applied to the backup. If an encryption key is specified, the backup can only be restored if the exact key is specified.

The mount mode must be Exclusive, or the nominated schema must be distributed to the client, in order for the backup to succeed.

The backup process creates a single file backup in the current default backup folder - see Alter Session.

The default file type (file extension) for Lava backup files is .lbs (Lava Backup Set).

Currently it is not possible to freeze updates to the database during execution of the backup. A planned enhancement will allow suspension of any commits during the backup process to ensure that the backup data is not inconsistent in any way as a result of partial updates.

This variant of the column list is used in the insert command, and simply specifies a list of columns from the nominated table which are to be initialized to values stipulated in the value list (insert ... values variant) or the select column list (insert ... as select variant).

where ColumnSpec is the name of a column in the table nominated for the insert.

Any number of columns from the table may be specified, but each column may only be specified once.

Each column specified in the column list must be matched by a corresponding value in the value list, or a corresponding select column in the select column list, depending on the form of the insert command used.

The type of the column to be inserted and the corresponding value or select column do not have to be identical. The SQL engine will do a best-case conversion of the value to be inserted into the data type of the column specified for insertion.

Any columns not specified in the insert column list will be left blank (empty string for string types, 0 for numeric types.

The Row_status and ID columns must not be specified in the column list for standard (non-Raw) tables. These columns are maintained by the Lava Database kernel, and cannot be set by the user. The Row_status column will be set to CURRENT, and the ID column will be set to the correct row ID value for the row used when the resulting data row is inserted into the table. The row to be used will depend on whether the table allows re-use of deleted rows, and on whether unused (deleted) rows are found in the table.

Each of the columns specified may include calculations or functions, as follows :

In the above example, the third column to be selected will be the object ID of the user’s table, named mytable.

Where aggregates are used, the results may be grouped by further columns - see the Group by clause for further details.

Any number of columns from the nominated tables in the table list of the select command may be specified, and any column may be specified any number of times.

Subqueries may be nested to any depth. Note, however, that as subqueries are evaluated separately from the main query, in certain cases it is more efficient to specify a ‘flattened’ query. The Lava SQL engine will attempt to evaluate queries as economically as possible, but this will not always result in the best possible evaluation of the required results.

When a column is coded as a subquery, that subquery must return exactly one row with exactly one column. As the subquery is intended to replace a single column (or calculated value), the result of the subquery must be a single value (any data type is permitted). If the specified subquery results in more than one column or more than one row in its result set, an error will be returned and the select will fail.

The Column List Specification is used in both the Alter Table and the Create Table commands, and specifies the names and data types of a list of 1 or more columns for addition to (Alter Table) or specification of a complete table (Create Table).

Each entry in the list comprises a pair of specifications separated by one or more space characters; the first is the name of the column, the second is the data type for the column.

In addition to being unique within the specified list, column names specified in an Alter Table command must comply with the following constraints :

• If the Alter command specifies the modify option, each column name specified must exactly match an existing column in the table to be altered

• If he Alter command specifies the add option, each column name specified must also be unique considering the existing columns of the table to be altered.

The total number of columns specified for the table in both the Create command and the Alter ... add command may not exceed 500. In addition, the sum of the column sizes for all columns specified may not exceed 1500 bytes. (This does not include the variable portion of variable length column types - see Variable length types for more information.)

This variant of the column list, identical in syntax to the form used in insert commands (see Column List Clause - Insert), and simply specifies a list of columns from the nominated table which are to be dropped (deleted).

where ColumnSpec is the name of a column which is to be dropped from the nominated table.

Any number of columns from the table may be specified, but each column may only be specified once.

The Row_status and ID columns may not be specified for standard (non-Raw) tables. These columns are mandatory, and cannot be dropped by the user.

This form of the column list is used in the Update command to specify columns to update and corresponding values.

Each entry in the list comprises a pair of specifications separated by an equals sign; the first is the name of the column, the second is the value to be assigned to the column.

If a subquery is used to specify the value for the column, the subquery must return exactly one row and one column. If more rows or more columns are returned, the query will return an error and the update will fail.

None. The prerequisites apply to the transactions that comprise the transaction frame on which the commit is to be performed; the commit itself has no prerequisites.

None. The Commit command always acts on the current session, which determines the appropriate server on which the commit is executed.

The transaction frame is partially committed if a subframe is specified, and fully committed if no subframe is specified.

If a Savepoint is executed before any modifications are performed in the session, specifying that savepoint as the subframe to be committed is equivalent to specifying commit without a subframe.

If a subframe is specified which is partway through the current transaction frame, the commit does a partial commit from that savepoint onward - effectively what this does is to remove the subframe and combine it with the root transaction frame for the session.

The Connect command attempts to connect to a Lava Database. Both exclusive and server connections are supported.

The SERVERIP variant of the connect command may be used to connect to servers visible only via an IP network. This is generally true of servers accessed via the internet.

The CONNECT EXCLUSIVE variant of the command can only be used on databases which are mounted Exclusive - Client mode databases only support SERVER connections.

servername The network server name of a server which can be addressed by name on a local Windows network.

serverIPaddress The IP address (in conventional a.b.c.d numeric format) for the server

Establishing a connection to the Lava Server is required to create a current session to the server. A valid session is required for all Lava commands.

Prior to connecting to a server when mounted in Client mode, the client database must be mounted - see Mount for more information.

The Create Schema command is used to create a new schema in the database. The schema is normally created on the server, but it is possible to specify creation on the client.

A schema is an encapsulating or grouping entity that allows tables to be accumulated into a coherent set, and user permissions and access to be limited to the schema. Schemas allow for efficient backup strategies, as sets of tables belonging together can be backed up as a single group.

A schema may be used as a means of nominating sets of tables (and in the future, other objects) for purposes such as data distribution, as well as being a useful mechanism in large databases with many tables to divide the database into more manageable sets of objects.

The [client] qualifier instructs the SQL engine to create the schema on the client database only. This implies that the schema will be transient, existing only as long as this particular client session is mounted. On dismount of the client database, the schema is dropped, together with any tables that were created within the schema.

The [server] qualifier causes the schema to be created on the Lava Server to which this client is connected. This is the default operation.

schemaname The parameter schemaname specifies the name of the newly created schema. This name must be unique across all schemas currently existing on the database within which the schema is being created.

The schema is initially empty, with the exception of the default system variable length column tables, which are for system use only.

In order to access the new schema, the schema and any objects within the schema can only be accessed by prefixing any object to be accessed by the schema name

In the current release of the Lava Database, schemas are non-hierarchical, although provision has been made in the system design for schema hierarchies. Functionality will be added to allow schemas to be created as subordinates of master schemas, to arbitrary depth. This will allow greater flexibility in controlling sets of tables and other objects both in terms of access privileges and in terms of data management for backup and distribution purposes.

In the following example, a new schema (fred) is created. An existing table in the current schema, existingtable, is then moved to the new schema. A select is first performed using a specified schema prefix to access the schema which is non-default to the current user. The current schema is then set for the current session (this will not alter the default schema for the user on next connection) and the table is once again queried, this time without the need for the schema prefix.

The Create Relation command creates a relation between two tables which will be used for relational integrity and (potentially) slice backup / restore purposes.

The session must have CREATE RELATION privilege on the schema to which the tables belong, as well as ALTER TABLE privilege on both of the tables.

sourcetable The parent (source) table in the relation (the one table in a one : many relationship)

targettable The child (target) table in the relation (the many table in a one : many relationship)

sourcecolumn The column in the parent (source) table which stores the value of the master table to which a row entry in the child table is related. If more than one source column is required, multiple columns may be specified (comma-separated)

targetcolumn The column in the child (target) table which stores the value of the master table to which a row entry in the child table is related. If more than one target column is required, multiple columns may be specified (comma-separated)

ConditionColumn A source or target column on which a conditional relation is based. The condition column must be an integer (32-bit) column.

ConditionValue The nominated value for a condition column for which the relation is valid. This will be an integer value.

A relation is created between the nominated parent and child tables on the nominated child table column.

If multiple source columns are specified, the exact same number of target columns must be specified for the target table.

For the nominated condition column (source or target as applies), the relation is only regarded as valid for the nominated condition value. In other words, if a conditional relation is specified, that relation will only apply where the nominated column has the nominated condition value. For all other values in that column, the relation is ignored (as if it did not exist).

If DELETE CASCADE is specified, deleting a row in the parent (source) table will cause cascade delete of all rows satisfying the relation in the child (target) table.

If DELETE RESTRICT is specified, an attempt to delete a row in the parent (source) table will be denied if any rows exist in the child (target) table which satisfy the relation.

In the current release, referential update of target values for update of the value in the parent (source) table is not supported. This is due to the fact that native relations in a Lava database are always to or from the Row ID of either table, and therefore referential updates do not apply. This feature will be added in a future release of the database.

For a complete and detailed description of the implementation of relational integrity in a Lava Database, see Relational Integrity.

The following is an actual relation in the EVENT schema - for a graphical depiction of the relation see the illustration under Event Schema.

The Create Sequence command is stipulated as a future provision, and is not available in the current release of the database. The specification below is preliminary, but should be as implemented in release 5.0 of the Lava SQL engine.

If the CYCLE option is specified, the sequence wraps to the specified MINVAL when the MAXVAL is reached.

The [client] qualifier instructs the SQL engine to create the sequence on the client database only. This implies that the sequence will be transient, existing only as long as this particular client session is mounted. On dismount of the client database, the sequence is dropped.

The [server] qualifier causes the sequence to be created on the Lava Server to which this client is connected. This is the default operation.

sequencename The name of the sequence to be created. This must be unique within the current schema.

startvalue The starting value of the sequence. This is the first value returned by the sequence. It may be any numeric value, including fractional values.

endvalue The final value of the sequence. This is the last value returned by the sequence before either terminating or cycling. It may be any numeric value, including fractional values.

termqualifier The method used to terminate the sequence. This method is applied when the maxvalue is reached.

Column Sequence	Column Name	Column Type	Generic Type
1	Row_status	RowStatus	Byte
2	ID	RowID	longint (dword)

Column	Type	System Use
Row_status	ROWSTATUS	Indicates the status of the row; status bits may include CURRENT, DELETED, UNUSED, LOCKED, OWNED. Some of these statuses are mutually exclusive.
ID	ROWID	Indicates the Row ID of the row - required for distribution purposes. Within a data table is always identical to the actual Row ID of the row.
VDT	VDT	Version Date-Time. Logs the date and time of the last transaction performed on the row.
User_id	4-byte Integer	The ID of the user who performed the last transaction on the row (as defined in Sys_Users)
Cache_id	4-byte Integer	Internal system use only.
bDistributed	Boolean	Internal system use only

Backup	Backup system - create a schema backup
Restore	Backup system - restore a schema backup
Connect	Connect to a nominated server
Disconnect	Disconnect from a server

Create	Create a new schema
Drop	Drop an existing schema
Rename	Rename an existing schema
Alter	Alter attributes of an existing schema
Distribute	Distribute a schema from the server to the local client database
Restore	Backup system - restore a schema backup
Backup	Backup system - create a schema backup

Create	Create a new user account
Drop	Drop an existing user account
Rename	Rename the username for an existing user account
Disable user	Disable a user account
Enable user	Enable a user account which is currently disabled
Grant	Grant privileges to a user account
Revoke	Revoke privileges from a user account
Alter user	Alter attributes of a user account
Alter session	Alter session default attributes
Connect	Connect to a nominated server
Disconnect	Disconnect from a server

Create Table	Create a new data table
Drop	Drop an existing data table
Alter	Alter attributes for a data table
Distribute	Distribute a table to the local client database
Create alias	Create an alias (synonym) for a data table
Drop alias	Drop an alias of a data table
Truncate	Truncate a data table
Create Relation	Create a system relation between two data tables
Drop Relation	Drop a system relation between to data tables
Index	Create an index on a table column
Drop Index	Drop index(es) on a table column

Select	Query the database and produce a result set
Update	Update data rows in a data table
Delete	Delete data rows in a data table
Insert	Insert data rows into a data table
Subqueries	Correlated subqueries to a select statement

Commit	Commit a pending transaction frame
Rollback	Rollback a pending transaction frame
Savepoint	Create a nested transaction frame

Show alias
Show index
Show schemas
Show sessions
Show tables
Describe table

CalendarDate	Convert a Julian date into calendar date format
CalendarDateTime	Convert a Julian date-time (VDT) into calendar / clock date-time format
ClockTime	Convert a numeric Lava time column into clock time
JulianDate	Convert calendar (text) date into a Julian date
JulianTime	Convert clock (text) time into a Lava numeric time
SysDate	Obtain the system (server) date
SysDateTime	Obtain the system (server) date time - UDT.
SysTime	Obtain the system (server) time - UDT.

System pseudo-table	Table name used in non-table queries
Column list - Select Clause	List of columns for selection
Group by Clause	Group specification in selects
Order by Clause	Sorting in selects
Table List Clause	List of tables for selection
Where Clause	Specification of joins or filters

Grant role	Establishment and management of roles is not yet supported
Create view	Views are not yet supported
Drop view	Views are not yet supported
Rename view	Views are not yet supported
Undelete	Undelete (recovery of deleted rows) is not yet supported
Create sequence	Sequences are not yet implemented
Drop sequence	Sequences are not yet implemented
Set	Provision for future functionality