Qubik Lava SQL Technical Reference

Table of Contents

Lava SQL Reference

Introduction

Distributed Lava Database Operation

Supported Data Types

Data types and sizes

Variable length types

Nulls in the Lava Database

SQL Operators, Functions and Conditions

Functions

Aggregates

Reserved expressions

Comparisons

SQL in the Lava Database

SQL Command Categories

Database Manipulation

Schema Manipulation

User Manipulation

Table Manipulation

Data Extraction and Manipulation

Transaction Statements

Database Information

Date and Time Manipulation

Miscellaneous Statements and Clauses

Future Enhancement

SQL Command Reference

Alter schema

Alter table

Alter session

Alter user

Backup

Column List Clause - Insert

Column List Clause - Select

Column List Clause - Specification

Column List Clause - Drop

Column List Clause - Update

Commit

Connect

Create schema

Create relation

Create sequence

Create synonym / Create alias

Create table

Create user

Create view

Delete

Disable user

Enable user

Disconnect

Dismount

Distribute

Drop schema

Drop sequence

Drop relation

Drop synonym / Drop alias

Drop table

Drop user

Drop view

Grant Role

Grant privilege

Group by Clause

Index

Insert

Order by Clause

Rename schema

Rename sequence

Rename synonym / Rename alias

Rename table

Rename user

Rename view

Restore

Revoke privilege

Rollback

Savepoint

Select, Select Statement

Set

Subqueries

Lava pseudo-table

Table List Clause

Truncate

Undelete

Update

Value List Clause

Where Clause

SQL Syntax Specification

Lava SQL Reference

Introduction

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.

Distributed Lava Database Operation

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 :

   SELECT [SERVER] * FROM SYSTEM.SYS_OBJECTS

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

Similarly, it is possible to force a command to execute on the client.

   SELECT [CLIENT] * FROM SYSTEM.SYS_OBJECTS

The above command will unconditionally execute on the client.

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 :

   SELECT * FROM TESTSCHEMA.CUSTOMERS

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.

If, however, the command is stated as follows :

   SELECT [CLIENT] * FROM TESTSCHEMA.CUSTOMERS

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.

Supported Data Types

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.

Data types and sizes

Variable length types

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.

The currently supported variable length types are :

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.

Unsupported types

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

Decimal              Binary coded decimal type

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

Nulls in the Lava Database

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.

SQL Operators, Functions and Conditions

Functions

   ABS

   ARCCOS

   ARCSIN

   ARCTAN

   COS

   DEG

   EXP

   FORMAT

   INT

   LN

   LOG

   LOWER

   RAD

   ROUND

   SIN

   SLICE

   STRINGPOS

   SQRT

   SOUNDEX

   TAN

   TRUNC

   UPPER

Aggregates

   AVG

   COUNT

   MIN

   MAX

   SUM

Reserved expressions

   PI

   ROWID

   DATE

   TIME

   VDT

Comparisons

<

>

<=

>=

=

#, <>

LIKE

SQL in the Lava Database

General Approach to SQL Syntax

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.

Departures from the ISO syntax

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.

select

   schema_name

from

   sys_objects, sys_schemas

where

   sys_schemas.id = sys_objects.schema_id and

   sys_objects.id < 70

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

Comments in SQL statements

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 */

SQL Command Categories

Database Manipulation

Schema Manipulation

User Manipulation

Table Manipulation

Data Extraction and Manipulation

Transaction Statements

Database Information

Date and Time Manipulation

Miscellaneous Statements and Clauses

Future Enhancement

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

SQL Command Reference

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.

Alter schema

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.

     ALTER SCHEMA schemaname SET ACCESS accessmode

Prerequisites                         

The session must have ALTER SCHEMA privilege on the nominated schema.

Variants

     ALTER [CLIENT] SCHEMA schemaname SET ACCESS accessmode

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.

     ALTER [SERVER] SCHEMA schemaname SET ACCESS accessmode

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

Qualifiers and Parameters

              schemaname        The name of the schema to be altered.

              accessmode         The data access mode of the schema. Available modes are :

                                           READONLY      The schema allows read access only - updates are disallowed.

                                           READWRITE     The schema may be updated by sessions with appropriate privileges.

Results

The access mode of the nominated schema is modified as stipulated.

Remarks

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.

Backup operations are permitted on READONLY schemas.

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

Examples

     ALTER SCHEMA fred SET ACCESS READONLY;

     ALTER SCHEMA joe SET ACCESS READWRITE;

See also

Schema Manipulation

Alter table

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

              ALTER TABLE tablename ADD (column list - spec)

              ALTER TABLE tablename DROP (column list - drop)

              ALTER TABLE tablename MODIFY (column list - spec)

              ALTER TABLE tablename RENAME TO newtablename

              ALTER TABLE tablename SCHEMA newschema

              ALTER TABLE tablename DISTRIBUTION FULL | ONDEMAND

              ALTER TABLE tablename REPLICATION ON | OFF

Prerequisites

The session must have ALTER TABLE privilege on the nominated table.

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

Variants

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.

Qualifiers and Parameters

              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.

              newtablename                    The new table name for the nominated table.

              newschema                        The schema to which the table is to be moved.

Results

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.

Remarks

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.

Examples

Add two new columns to existing table fred :

     ALTER TABLE fred ADD (column_new_1 INTEGER, column_new_2 STRING(50));

Drop two existing columns from table fred :

     ALTER TABLE fred DROP (column_old_1, column_old_2);

Change the type of an existing column in table fred :

     ALTER TABLE fred MODIFY (column_old_3 INTEGER);

Rename table fred to table joe :

     ALTER TABLE fred RENAME TO joe;

Move table fred from its current schema to schema different_schema :

     ALTER TABLE fred SCHEMA different_schema;

See also

Rename Table, Table Manipulation

Alter session

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

           ALTER SESSION SCHEMA newschema

           ALTER SESSION BACKUP FOLDER newbackupfolder

Prerequisites

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

There are no prerequisites for modifying the backup folder.

Variants

None - the command acts on the current session.

Qualifiers and Parameters

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

              newbackupfolder               The full path of an existing folder on the workstation

Results

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.

Remarks

ALTER SCHEMA :

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.

ALTER BACKUP FOLDER :

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

Examples

Change the current schema to the EVENT schema :

     ALTER SESSION SCHEMA EVENT;

Change the default backup folder to a new folder :

     ALTER SESSION BACKUP FOLDER c:\lava\backup

See also

Alter User, Backup, User Manipulation

Alter user

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

           ALTER USER username SCHEMA newschema

           ALTER USER username PASSWORD newpassword

           ALTER USER username SCHEMA newschema PASSWORD newpassword

Prerequisites

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

Variants

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.

Qualifiers and Parameters

              username             The username for the account to be modified

              newschema          The new default schema for the user account

              newpassword       The new password for the user account

Results

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

Remarks

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.

Future enhancement

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

Examples

     ALTER USER fred SCHEMA UserSchema_1 PASSWORD jennifer;

See also

Alter Session, User Manipulation

Backup

The Backup command performs a backup on a nominated schema.

     BACKUP SCHEMA schemaname;

     BACKUP SCHEMA schemaname KEY encryptionkey;

Prerequisites

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.

Variants

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

Qualifiers and Parameters

              schemaname                      The schema on which the backup is to be performed.

              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.

Results

A backup file is created for the nominated schema.

Remarks

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).

Future enhancement

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.

Examples

Assert a backup folder and backup the schema fred :

     ALTER SESSION BACKUP FOLDER “q:\lava\backup”;

     BACKUP SCHEMA fred KEY “jzX12 owCP”;

See also

Alter Session, Restore, Schema Manipulation

Column List Clause - Insert

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).

The basic form of the column list is as follows :

     (column_1, column_2 ... column_n)

The syntax of the insert column list is :

     ColumnListInsert ::=  (ColumnList)

     ColumnList       ::=  ColumnSpec [, ColumnList]

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

Remarks

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.

See also

Insert, Data Extraction and Manipulation

Column List Clause - Select

This variant of the column list is used in the Select statement.

The basic form of the column list is as follows :

     column_1, column_2 ... column_n

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

     (column_1 + 3) * 4, log(column_2) / 20.5

In addition, any of the columns may be a subquery :

     column_1, (column_2 + 3) * 4, cos(PI),

     (select id from system.sys_objects where object_name = ‘mytable’),

     column_3

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

Finally, columns may specify aggregates :

     sum(column_1), avg(column_2), max(column_3)

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

The full syntax of the select column list may be found in the section SQL Syntax Specification.

Remarks

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.

See also

Select statement, Subquery, Data Extraction and Manipulation

Column List Clause - Specification

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).

The general form of the list is :

     (column_1 datatype_1, column_2 datatype_2, ..., column_n datatype_n)

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.

The list may contain any number of columns.

For a comprehensive list of allowable data types, see Supported Data Types.

Remarks

Each column name specified in the list must be unique.

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.)

See also

Create Table, Alter Table, Supported Data Types, Table Manipulation

Column List Clause - Drop

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).

The basic form of the column list is as follows :

     (column_1, column_2 ... column_n)

The syntax of the insert column list is :

     ColumnListDrop   ::=  (ColumnList)

     ColumnList       ::=  ColumnSpec [, ColumnList]

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

Remarks

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.

See also

Alter Table, Table Manipulation

Column List Clause - Update

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

The general form of the list is :

     column_1 = value_1, column_2 = value_2, ..., column_n = value_n

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.

The list may contain any number of columns.

The values specified may contain calculations :

     column_1 = (column_1 + 3) * 4, column_2 = log(35) / 20.5

Any value specification may be derived in terms of a subquery :

     column_1 = (select max(amount) from inv_details where ID = Inv_id)

Remarks

Each column name specified to be updated in the list must be unique.

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.

See also

Update, Subquery, Data Extraction and Manipulation

Commit

The Commit command commits open transaction frames for the current session.

     COMMIT

     COMMIT subframe

Prerequisites

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.

Variants

xxxxxxxxxxxxxxxx

[server] / [client]

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

Qualifiers and Parameters

              subframe                            The name of an existing transaction subframe - see Savepoint

Results

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

Remarks

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.

Examples

     DELETE fred WHERE ID = 5;

     SAVEPOINT newsubframe;

     DELETE fred WHERE ID = 6;

     COMMIT newsubframe;

     UPDATE fred SET name = ‘fred’ WHERE ID = 7;

     COMMIT;

See also

Rollback, Transaction Statements

Connect

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

     CONNECT SERVER servername USER username PASSWORD password

Connect ServerClause user [password password]

              ServerClause : Exclusive | server servername | serverip ipnumber

Prerequisites

A valid user account with the stated password on the nominated server.

Variants

     CONNECT SERVERIP serverIPaddress USER username PASSWORD password

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.

     CONNECT EXCLUSIVE USER username PASSWORD password

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

Qualifiers and Parameters

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

              username             The name of a valid user account.

              password             The password for the user account.

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

Results

A connection is established with the specified Lava Server.

Remarks

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.

Examples

     CONNECT SERVER CentralServer USER fred PASSWORD “long password”

See also

Database Manipulation, User Manipulation

Create schema

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.

     CREATE SCHEMA schemaname

Prerequisites

The session must have database wide CREATE privilege

Variants

     CREATE [CLIENT] SCHEMA schemaname

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.

     CREATE [SERVER] SCHEMA schemaname

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

Qualifiers and Parameters

              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.

Results

On successful completion, a new schema is created on the selected database.

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

Remarks

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

Future enhancement

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.

Examples

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.

     CREATE SCHEMA fred;

     ALTER TABLE existingtable SCHEMA fred;

     SELECT * FROM fred.existingtable;

     ALTER USER myuser SET SCHEMA fred;

     SELECT * FROM existingtable;

See also

Alter Table, Alter User, Schema Manipulation

Create relation

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

     CREATE RELATION FROM PARENT sourcetable TO targettable

           SOURCECOLUMN (sourcecolumn [, sourcecolumn])

           TARGETCOLUMN (targetcolumn [, targetcolumn])

           [SOURCECONDITIONAL ConditionColumn = ConditionValue]

           [TARGETCONDITIONAL ConditionColumn = ConditionValue]

           [DELETE CASCADE | RESTRICT]

Prerequisites

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.

Variants

None. The command is always executed on the server.

Qualifiers and Parameters

              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.

Results

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

Remarks

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

If source or target conditions are specified, these operate as follows :

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.

Examples

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

     CREATE RELATION FROM PARENT Sys_Event_Type TO Sys_Event_Log

           SOURCECOLUMN (Event_Type_id)

           TARGETCOLUMN (ID)

See also

Relational Integrity, Table Manipulation

Create sequence

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.

The Create Sequence command creates a new sequence by the specified name.

     CREATE SEQUENCE sequencename STARTVAL startvalue ENDVAL endvalue

           INCREMENT incrementval termqualifier

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

     CREATE SEQUENCE sequencename STARTVAL startvalue ENDVAL endvalue

           INCREMENT incrementval CYCLE

Prerequisites

The session must have CREATE SEQUENCE privilege in the current schema.

Variants

     CREATE [CLIENT] SEQUENCE sequenceattributes

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.

     CREATE [SERVER] SEQUENCE sequenceattributes

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

Qualifiers and Parameters

              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.

              incrementval        The increment amount. This may be negative or fractional.

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

                                           TERMINATE       The sequence expires on reaching the maxvalue. An attempt to use the sequence after this fails.

                                           CYCLE                The sequence cycles back to the minvalue and continues incrementing.

Results

A sequence with the specified attributes is created in the current schema.

Remarks

The sequence is always created in the current schema.

The name of the sequence must be unique within the sequences defined in the schema.

Sequences are updated immediately on access, and do not revert on execution of a Rollback command.

If the incrementval is to be negative, the startvalue should be higher than the endvalue.

Future enhancement

The Create Sequence command is specified for future enhancement of the Lava Database, and will only be available in release 5.0 of the kernel and SQL engine.

Examples

     CREATE SEQUENCE integersequence STARTVAL 1 ENDVAL 20

           INCREMENT 2 CYCLE;

     CREATE SEQUENCE floatsequence STARTVAL 5.7 ENDVAL 2.3

           INCREMENT -0.05 TERMINATE;

See also

Miscellaneous Statements and Clauses

Create synonym / Create alias

The Create Synonym and Create Alias commands are equivalent alternatives of the command to create an alias (synonym) on a table. A synonym or alias is an alternative name for the table which will identify the table equivalently to the proper table name.

     CREATE SYNONYM tablealias FOR tablename

     CREATE ALIAS tablealias FOR tablename

Prerequisites

The session must have CREATE privilege on the schema to which the nominated table belongs.

Variants

None. The Create Synonym command always executes on the server.

Qualifiers and Parameters

              tablealias             A new alias for the nominated table.

              tablename            The table for which the alias is to be created. If the table is not in the current schema, the schema name must be prefixed to the table name.

Results

An alias as specified is created for the nominated table. The alias is persistent, and will exist until explicitly dropped.

Remarks

After creation of the alias, the table may be referred to by either the original table name or by the alias name.

The specified alias must be unique within the schema. This uniqueness requirement includes any tables defined within the schema.

The alias belongs to the same schema as the table, and if the alias is referred to from another current schema, the schema name for the alias / table must be prefixed to the alias as would be the case for the table.

Examples

Create an alias for the event log :

     CREATE ALIAS errorlog FOR event.sys_event_log;

Select from the new alias :

     SELECT * FROM event.errorlog;

See also

Table Manipulation

Create table

The Create Table command creates a new table in the current or specified schema.

     CREATE TABLE tablename

     (Column List - Spec)

           [tableattributes]

     CREATE TABLE tablename

     AS SELECT Select Statement

           [tableattributes]

A variant of the Create Table command allows creation of a table (client or server) from an ODBC source :

     CREATE TABLE tablename FROM ODBC {odbc select}

Prerequisites

The session must have CREATE TABLE privilege on the schema within which the table is to be created.

Variants

     CREATE [CLIENT] TABLE tablecreationstatement;

The nominated table is created on the client database. Note that in contrast with many SQL client / server options, in the case of Create Table, the Client mode is the default mode.

If the table is created on the client database, it is always VIRTUAL (no other qualification is permitted) and is always transitory - it will be dropped on dismount. Both physical and persistent virtual tables can only be created on the server.

     CREATE [SERVER] TABLE tablecreationstatement;

The nominated table is created on the server database. The table is not distributed by default; in order to distribute the table an explicit Distribute Table command must be issued. Note that in order to create the table on the server, the [SERVER] option must be explicitly stated, as client mode is the default for this command.

Qualifiers and Parameters

              tableattributes       An optional qualification of the type of table to be created. If omitted, a virtual table will be created (in the default mode, Client mode) or a physical table if server mode was specified.

                                                         Valid type qualifications are :

                                                         PHYSICAL         This is the default for server creation. A physical (conventional) table is created, without replication. Physical tables can only be created on the server.

                                                         VIRTUAL           A virtual table is created. This is the default for client creation, which is also the default mode for the Create Table command. The table exists purely in memory, and the content of the table is transitory; all data in the table is lost on dismount / mount. See Virtual Tables for further information on this type of table.

                                                         PERSISTENT     Indicates a non-transitory table - this qualifier is only valid for tables of type VIRTUAL. If not specified, virtual tables are dropped on dismount. Even for PERSISTENT tables the content of the table is lost on dismount, but the table itself still exists on re-mount.

                                                         REPLICATE       The table is a physical, replicated table (specification of PHYSICAL is redundant if the REPLICATE qualification is specified). Replicator tables can only be created on the server. See Replicator Tables for further information on this type of table.

Additional attributes may be specified depending on the type of table being created :

                                                         RAW                                 If the table being created is a Raw table (which is only permitted for virtual tables) this attribute may be specified, which prevents the system from adding the standard version columns to the column specification for the table. Note that Raw tables are very restricted in use and should only be created where absolutely required.

                                                         RECLAIM                         This is the default, and allows deleted rows to be re-used when row insertions are performed.

                                                         NORECLAIM                   Under special circumstances, it may be necessary to prevent the kernel from re-using rows which have been deleted, for example to use block references to associated rows of data in the table. The NORECLAIM attribute prevents the kernel from re-using deleted (free) rows when inserting new data rows.

                                                         INITIALSIZE                    Only valid for VIRTUAL tables, this attribute allows specification of the initial memory allocation for the table on creation or mount.

     CREATE TABLE tablecreationstatement VIRTUAL INITIALSIZE(100);

                                                         RESERVEROWS             Only valid for server tables, this attribute may be used to specify a non-standard number of rows to be reserved for addition when a session distributes the table.

     CREATE [SERVER] TABLE tablecreationstatement RESERVERROWS(200);

                                                         FRAMED                          Specifies that transactions acting on the table will be framed, i.e. will not be autocommit. This is the default creation mode, and can only be disabled by specifying the attribute RAW as described above. All FRAMED tables have the standard version columns added (prepended) to the user-specified column list.

                                                         TIMEDOMAIN                 Future provision. Not enabled in the current release of the Lava database.

                                                         SCHEMA                          Allows creation of a table in a schema other than the default schema for the current session.

     CREATE TABLE tablecreationstatement SCHEMA schemaname;

              tablename                          The name of the table to be created. If the table is to be created in a schema other than the current schema, the required schema name must be prefixed to the table name.

              Column List - Spec            The list of columns to be specified for the new table. See the specification of the column list for further details.

              Select Statement                 The table to be created is defined in terms of a select statement (of arbitrary complexity - any variant of the select statement is permitted) performed on existing tables. See the specification of the select statement for further details.

              odbc select                A valid select statement for the target ODBC database. The select statement will be transmitted to the target database, and the results of the select used in exactly the same way as a AS SELECT clause.

Results

A new table is created in the specified or current schema.

Remarks

The table name must be unique within the nominated or implied schema. This uniqueness requirement includes any aliases defined in the schema.

The nominated schema (if different from the current schema) must already exist.

Note that in the default mode for this command, a virtual table is created on the client database, which is transitory both in terms of content and the definition of the table itself - in this case the table will be dropped on dismount of the client database.

If the user wishes to create a physical table on the server, as would be the case with a conventional SQL server database, the following form of the command must be used :

     CREATE [SERVER] TABLE tablename (Column List - Spec);

Note that in the above example, the PHYSICAL qualifier is omitted because this is the default table type for server creation.

For all standard tables (created without the RAW attribute) the system will add the standard version columns to the front of the user-specified column list - this is mandatory for distributed operation, and these columns are manipulated by the Lava Database exclusively - they may, however, be accessed by the user for reference purposes (strictly read-only).

In the CREATE AS SELECT form of the command, the individual columns of the table created are of the same data type as the selected columns in the select statement. In cases where operations are performed on data columns, the columns created will comply with the data type resulting from the relevant operation.

In the case of a CREATE AS SELECT, if the source table is FRAMED (the default) and therefore already has the standard version columns in its column list, these columns will be inherited by the newly created table and the database kernel will therefore not add these onto the column list.

Future enhancement

In a future release of the Lava kernel, the option TIME-DOMAIN will be permitted, which creates a Time-Domain table set with advanced auditing and timeslicing features. For more information on the Time-Domain mechanism, see Time-Domain. See Lava Kernel Releases for the planned release schedule.

Examples

Note that although upper and lower case are used in the examples below for clarity, all SQL commands are case insensitive.

Create a new physical replicator table named fred on the server in the schema testschema :

     CREATE [SERVER] TABLE testschema.fred (

                                         Name       STRING(50),

                                         Ownership  FLOAT

                             ) REPLICATOR;

Create a new virtual table (the only allowable type) on the client, using a select. This example will create an identical copy of the system event log table in the schema clientschema.

     CREATE TABLE clientschema.fred

           AS SELECT * FROM EVENT.Sys_Event_Log;

Create a virtual table on the client and specify an initial memory allocation :

     CREATE TABLE fred (

                       Name       STRING(50),

                       Ownership  FLOAT

                       ) VIRTUAL INITIALSIZE(3M);

Create a new physical replicator table named fred on the server and specify no re-use of deleted rows and a non-standard row reservation :

     CREATE [SERVER] TABLE fred (

                             Name       STRING(50),

                             Ownership  FLOAT

                             ) REPLICATOR NORECLAIM RESERVEROWS(200);

Create a virtual raw table on the client (note that in this case the system will not add any columns to the user specified list) :

     CREATE TABLE fred (

                       Name       STRING(50),

                       Ownership  FLOAT

                       ) VIRTUAL RAW;

In all of the above cases except the last (in which the table is specified as RAW), the column list for the resultant table will include 5 additional columns (as described in version columns) :

     Row_status       ROWSTATUS,

     ID               ROWID,

     VDT              VDT,

     User_id          LONGINT,

     Cache_id         LONGINT

These are unconditional, and should be taken into account for any operations (add, put, get) performed on the table through API calls.

See also

Table Manipulation

Create user

The Create User command creates a new user account on the server.

     CREATE USER username PASSWORD password SCHEMA schema

Prerequisites

The session must have database wide CREATE privilege.

Variants

None. The Create User command always executes on the server.

Qualifiers and Parameters

              username             The new user account name.

              password             The initial password for the user account.

              schema                The initial default schema for the user account.

Results

A new user account is created on the server.

Remarks

The new user name must be unique across the server database.

The password may be null; this will result in a user account which may be used without a password. This mode is not recommended, as it permits access to the database without proper security; however, if the user account is limited to non-hazardous access and privileges, this can be a useful form for guest users.

The schema specified is the schema which is automatically set as the current schema for any session logged in to the new user account.

Initially, the user account is allocated only READ privileges on the default schema - all other privileges on the default or any other schemas must be explicitly allocated.

Future enhancement

User passwords will have an expiry attribute to allow password expiry at synchronous intervals

Examples

The following example creates a new schema, creates a user account with the default schema set to the schema just created, and allocates full privileges on the schema to the account, with the exception of the right to drop objects in the schema or drop the schema itself :

     CREATE SCHEMA fredschema;

     CREATE USER fred PASSWORD fredspassword SCHEMA fredschema;

     GRANT ALL ON SCHEMA fredschema TO fred;

     REVOKE DROP ON SCHEMA fredschema FROM fred;

See also

User Manipulation

Create view

The Create View command is provision for future enhancement; the command is not available in the current release of the database.

Prerequisites

None - future provision

Variants

None - future provision

Qualifiers and Parameters

None - future provision

Results

None - future provision

Remarks

None - future provision

Future enhancement

This command will be available in release 5.0 of the Lava SQL engine. See Lava Kernel Releases for the planned release schedule.

Examples

None - future provision

See also

Table Manipulation

Delete

The Delete command deletes rows in a data table according to the filters specified.

     DELETE tablename WHERE Where Clause

Prerequisites

The session must have DELETE privilege on the table.

Variants

In general the answer is none. As a rule, the command is executed on the specified table, which must be unique, and therefore fully determines how and where the delete takes place. However, there are two cases where specification of the Server for execution can be legal.

The first is where the administrator knows that the table exists on the server, but is not distributed to the client. In this case, the command would fail validation on the client and would not execute. If the command is forced to execute on the server, provided that the command is syntactically correct it will execute.

The second case is where the table is a system table, and would execute on the client but the administrator wishes to specifically execute the command on the server, which would imply a different result - probably the persistence of the change, versus a transient change if executed on the client.

In these cases, the following variant may be used :

     DELETE [SERVER] tablename WHERE Where Clause

Qualifiers and Parameters

              tablename            The name of the table within which rows are to be deleted. If the table is not in the current schema, the correct schema name must be prefixed to the table name.

              whereclause        The filter clause (optional) which limits the rows deleted in terms of one or more filter specifications. See the syntax for the where clause for further information.

Results

The required (filtered) rows in the nominated table are deleted.

Remarks

The deletion occurs within a transaction frame which must be explicitly committed or rolled back.

The WHERE clause is optional, and if omitted will cause every row in the table to be deleted. A faster equivalent for this option is the Truncate command.

Examples

The following command deletes all rows in the table with row ID less than 10, and commits the results :

     DELETE fred WHERE ID < 10;

     COMMIT;

See also

Data Extraction and Manipulation

Disable user

The Disable User command disables a given user account until re-enabled

     DISABLE USER username

Prerequisites

The session must have database wide UPDATE privilege.

Variants

None. The Disable User command always executes on the server.

Qualifiers and Parameters

              username             The user account to be disabled.

Results

The nominated user account is disabled - connections with this user account are no longer permitted on the server.

Remarks

If the user account is already disabled at the time of execution, the command has no effect.

Examples

     DISABLE USER fred

See also

User Manipulation

Enable user

The Enable User command disables a given user account until re-enabled

     ENABLE USER username

Prerequisites

The session must have database wide UPDATE privilege.

Variants

None. The Enable User command always executes on the server.

Qualifiers and Parameters

              username             The user account to be enabled.

Results

The nominated user account is enabled - connections with this user account are once again permitted on the server.

Remarks

If the user account is already enabled at the time of execution, the command has no effect.

Examples

     ENABLE USER fred

See also

User Manipulation

Disconnect

The Disconnect command disconnects the current session - the session becomes immediately unusable.

     DISCONNECT

Prerequisites

A valid session must exist.

Variants

None.

Qualifiers and Parameters

None.

Results

The session is disconnected.

The current database remains mounted, and a connect can immediately be issued to any valid Lava Server.

While disconnected, no SQL commands other than Connect may be issued.

Remarks

Any open transaction frames are automatically rolled back.

See also

User Manipulation

Dismount

The Dismount command dismounts the database.

     DISMOUNT

Prerequisites

The database must currently be mounted.

Variants

There are no variants to the command; the database on which the command acts is implied in terms of the database operational rules. See Mount Modes for further information on Lava Database operational modes.

Qualifiers and Parameters

None.

Results

The database is dismounted.

Remarks

If in client mode (the default mode of operation), the client database is dismounted and discarded. A client database cannot be re-mounted.

If in Exclusive mode, the database currently mounted is dismounted, and placed in a mountable state. The database can subsequently be re-mounted in either exclusive or server mode.

See also

Database Manipulation