Data Definition Language (DDL) Syntax

The SQL Data Definition Language (DDL) provides users with the capability to create tables, indexes, sequences, views, online aggregates, as well as databases, users, roles, and permissions. These are the supported statements:

ddlStatement:
	| createDatabaseStatement
	| dropDatabaseStatement
	| createSchemaStatement
	| dropSchemaStatement
	| createTableStatement
	| dropTableStatement
	| truncateTableStatement
	| alterTableStatement
	| createIndexStatement
	| dropIndexStatement
	| createViewStatement
	| dropViewStatement
	| createSequenceStatement
	| dropSequenceStatement
	| createOnlineAggregate
	| dropOnlineAggregate
	| createUserStatement
	| dropUserStatement
	| grantStatement
	| revokeStatement
	| createRoleStatement
	| dropRoleStatement
	| createTriggerStatement
	| dropTriggerStatement
	| enableTriggerStatement
	| disableTriggerStatement

1. CREATE DATABASE and DROP DATABASE Statements

The CREATE DATABASE allows to create a new database:

createDatabaseStatement=
	CREATE DATABASE databaseName [USER userName IDENTIFIED BY 'password']*

The user LXADMIN is administrator of all databases in the system.

The DROP DATABASE enables to remove a database:

dropDatabaseStatement=
	DROP DATABASE databaseName

1.1. Predefined Database, Schema, and Admin User

LeanXcale has a predefined database that is DB. LeanXcale has a predefined schema APP in all databases. The user LXADMIN is administrator of all schemas of all databases in the system.

2. CREATE SCHEMA AUTHORIZATION Statement

The CREATE SCHEMA AUTHORIZATION enables to perform a batch of creation of tables and views, and grant permissions on a given schema:

createSchemaStatement=
	CREATE SCHEMA AUTHORIZATION schemaName
		[ createAndGrantStatements ]*

createAndGrantStatements=
	createTableStatement
	| createViewStatement
	| grantStatement

Note that the schema should exist, i.e., a user with the schema name, since the statement only performs the specified batch of creates and grants.

3. CREATE TABLE Statement

The CREATE TABLE command allows the creation of a table and follows the specified syntax as outlined below:

createTableStatement=
	CREATE [CACHE] [DELTA] TABLE
	[ IF NOT EXISTS ] name
	[ NO OLD VERSIONS ]
	[ '(' tableElement [, tableElement ]* ')' ]
	[ AS queryStatement ] [ partitionBySpec [partitionBySpec]*  [ distributeBySpec  ]* ]

3.1. Table Types

The purpose of table creation is to define the structure of the table, including column names, attributes, and data types. There are three types of tables: regular, cache, and delta.

Regular tables are created using CREATE TABLE. Cache tables (CREATE CACHE TABLE) are always stored in memory, in addition to disk, to ensure rapid access. Delta tables (CREATE DELTA TABLE) include delta columns, allowing the accumulation of values with conflict-free updates, such as a sum.

3.2. IF NOT EXISTS Clause

Attempting to create a table that already exists results in an error. To avoid this error, the IF NOT EXISTS clause can be used, ensuring that the table is only created if it does not already exist.

3.3. Old Versions

LeanXcale as most modern databases, employs Multi-Version Concurrency Control (MVCC). Under MVCC, updates are not executed in place; instead, a fresh version of the tuple is generated with each update. This mechanism enables efficient concurrency control by avoiding read-write conflicts.

In specific scenarios, when it is known that the application exclusively performs either data insertion or data reading, but not both concurrently, it is possible to disable multi-versioning. This action is undertaken to remove the associated overhead and increase the efficiency of insert and update operations within the LeanXcale database.

3.4. Column Names, Types, Constraints, Online Aggregates

The list of tableElements is where it is specified the column names and any attributes associated to them. A tableElement can be either a column specification or a constraint specification as can be seen in the following syntax:

tableElement=
	tableColumn
	| [ CONSTRAINT name ] tableConstraint

A tableColumn has the following syntax:

tableColumn=
	columnName [kindOfDeltaColumn] type [ NOT NULL ] [ columnGenerator ] [ PRIMARY KEY ]

In its most fundamental form, a column specification consists of a column name followed by its data type. The inclusion of the modifier NOT NULL imposes a constraint, indicating that the column cannot contain NULL values. Additionally, the PRIMARY KEY modifier designates that the column is a part of the primary key for the table.

Columns can also be automatically generated in three distinct ways. The first method involves providing a default value to be used when no explicit value is provided. The second method employs an auto-incrementing value, achieved through the use of the AS IDENTITY clause. The third method utilizes a delta column, wherein updates and inserts are treated as an accumulation function over the current value of the column. The first two ways are indicated in the columnGenerator syntax:

columnGenerator:
	DEFAULT expression
	| [ GENERATED ALWAYS ] AS IDENTITY
	[ START WITH initialValue ]
	[ INCREMENT BY incrementValue ]

The AS IDENTITY clause indicates that is auto-increment key. It supports also the form GENERATED ALWAYS AS IDENTITY for compatibility with the PosgreSQL dialect. The START WITH subclause allows to specify the first value to be given to the auto-increment column. The INCREMENT BY subclause enables to indicate the value by which to increment the auto-increment column.

The third way is specified with the kindOfDeltaColumn syntax:

kindOfDeltaColumn =
	( SUM | MIN | MAX | COUNT )

The specification of an accumulation function determines the manner in which results are aggregate. For instance, when utilizing the SUM function, each update entails updating the SUM column with the previous value of the column augmented by the value being updated. MIN and MAX functions operate by obtaining the minimum or maximum, respectively, over the current value of the column and the new value introduced in the update. In the case of COUNT, it increments the previous value of the column by one, irrespective of the updated column value.

Additionally, various constraints can be specified over the columns. This is the constraint syntax:

tableConstraint:
	PRIMARY KEY '(' columnName [, columnName ]* ')'
	| PRIMARY GEOHASH KEY '(' latitudeColumnName , longitudeColumnName ')'
	| PRIMARY GEOHASH KEY '(' geoColumnName ')'
	| FOREIGN KEY '(' columnName [, columnName ]* ')'
	| REFERENCES tableName '(' columnName [, columnName ]* ')'
	| UNIQUE '(' columnName [, columnName ]* ')'
	| GEOHASH '(' latitudeColumnName , longitudeColumnName ')'
	| GEOHASH '(' geoColumnName ')'
	| CHECK expression

The PRIMARY KEY constraint defines the columns that constitute the primary key of a table and establishes the order in which they are arranged.

The PRIMARY GEOHASH KEY constraint defines a geohash primary key. It can be defined in two ways. The first way is providing two FLOAT or DOUBLE columns with the latitude and longitude. The second way is providing a geo column that is a STRING column using WKT markup language representing a vector geometry object. The GEOHASH constraint generates a hidden STRING column with the geohash of the indicated column(s). This hidden column is the one used as primary key.

The FOREIGN KEY constraint indicates that one or more columns within a table refer to the primary key in another table. Following the FOREIGN KEY keyword, the specific columns in the current table are listed. Subsequently, the REFERENCES keyword is used to identify the table name of the referenced table, along with the columns serving as primary keys in the referenced table.

The GEOHASH constraint indicates what columns to use to generate a hidden STRING geohash column that is used to generate a secondary index over that column.ç This geohash secondary index enables to improve geo searches such as a point is contained in a geometric shape. A geohash column can also be used as part of a composed primary key, for instance, PRIMARY KEY (country, geoHashCol). In the example, one can do searches with country and a geo condition, and thus, a search will find the first row of a country, and then perform a geo search within the country.

The UNIQUE constraint mandates that the specified column or set of columns consistently maintain distinct values across various rows in the table.

The CHECK constraint ensures that the stipulated condition is met for any values inserted into the associated column.

3.5. AS Clause

The AS clause is used to define the table schema based on the result set of a query statement. The table is then populated with the rows from the result set. When you specify AS clause, you may omit the list of tableElement_s since they will be taken from the resultSet, or you can provide the columnName and omit the data type from any _tableElement, in which case it renames the underlying column.

3.6. PARTITION BY Clause

The PARTITION BY clause plays a pivotal role in determining the fragmentation strategy of a table within LeanXcale, a critical factor for optimizing operational efficiency. If no partitioning is specified for a table, it means all the workload of the table will be handled by a single storage server and therefore, at most one core will be used to process that workload. If partitioning is specified, one can also indicate the criteria to distribute the partitions. The PARTITION BY clause has a crucial role in defining the fragmentation strategy employed by a table, representing a critical element for optimizing operational efficiency. When partitioning is explicitly specified, it becomes possible to define the criteria to distribute the partitions. The PARTITION BY clause syntax is as follows:

partitionByClause =
PARTITION BY
	KEY `(` columnName [, columnName]* `)` AT  tupleLiteral [, tupleLiteral]*
	| DIMENSION  columnName
		AT  literal [ , literal ]*
		| EVERY intervalLiteral
			[KEEP intervalLiteral]
		| EVERY unsignedIntegerLiteral
			[KEEP unsignedIntegerLiteral]
	| HASH `(` columnName [, columnName ]* `)`

tupleLiteral=
	`(` literal [ , literal ]* `)`

LeanXcale supports several methods for table partitioning that can be used in isolation or in a combined way:

Prefix of Primary Key: The table can be partitioned based on a prefix of its primary key.
Dimension-based Partitioning: Partitioning can be performed using a specified dimension, typically another column in the table. Each PARTITION BY DIMENSION clause only has a single column. Each clause adds a new dimension to the partitioning. The column should be part of the primary key. The columns of the primary key that are dimensions used for partitioning should be a suffix of the primary key (never including the first column of the primary, since this can be achieved with PARTITION BY KEY).
Auto-partitioning on Dimension Columns of temporal or auto-increment nature.
Hash partitioning: It can be specified over one or more columns of the primary key. It computes a hash over the specified columns and the value is stored in a new hidden column named HASHID. This column becomes the last column of the table and the last column of the primary key. There are as many partitions as storage servers.

Auto-partitioning can be applied to dimension columns of type TIMESTAMP, TIME, DATE, or an auto-increment integer. This automatic partitioning is particularly crucial for historical tables where data spans over time, such as sales or time series. As tables, especially historical ones, grow in size, accessing them becomes increasingly slower. Auto-partitioning based on temporal or auto-increment fields ensures that table fragments remain manageable in size, optimizing access efficiency. In the context of historical tables, the field keeping the temporal column or auto-increment counter is the one that can be used for auto-partitioning. Since in historic tables, it is the last fragment the one being consistently accessed, while the other fragments are infrequently accessed, auto-partitioning ensures that only the actively used fragments consume resources, mitigating inefficiencies associated with accessing large historical datasets.

When using auto-partitioning with TIMESTAMP the time span should be specified with INTERVAL. Some examples are:

INTERVAL '1' YEAR.
INTERVAL '1' YEAR TO '6' MONTH.
INTERVAL '6' MONTH.
INTERVAL '1' MONTH.
INTERVAL '90' DAY.
INTERVAL '7' DAY.
INTERVAL '1' DAY.
INTERVAL '12' HOUR.
INTERVAL '90' SECONDS.
INTERVAL '15' SECONDS.

The utilization of the PARTITION BY KEY AT clause offers the capability to partition data based on a prefix of the primary key (including the whole key),

On the other hand, the PARTITION BY DIMENSION clause allows partitioning using any other column designated as a dimension in the CREATE TABLE statement, e.g., 'state'. This feature ensures that all rows with a specified range of values in the chosen dimension are grouped into the same partition.

Within the AT subclause, specific split points for partitioning are provided. In contrast, the EVERY subclause triggers automatic partitioning at specified intervals, streamlining the process of creating partitions based on the defined dimension.

3.7. DISTRIBUTE BY Clause

If partitions have been specified, then it is possible to indicate how to distribute the partitions across the storage servers with the distribute by clause. It has the following syntax:

distributeBySpec=
	NO DISTRIBUTE
	| DISTRIBUTE BY HASH
	| DISTRIBUTE BY KEY
	| DISTRIBUTE BY DIMENSION dim

The distribution of data can be achieved through one several criteria in LeanXcale:

NO DISTRIBUTE. It does not distribute the table, so it will be stored at a single storage server that will handle all the workload for that table.
DISTRIBUTE BY KEY. Utilizes the primary key partitions to distribute across storage servers. Cannot be combined with other DISTRIBUTE BY clauses.
DISTRIBUTE BY DIMENSION. Uses the specified partitioned dimension column to distribute.
DISTRIBUTE BY HASH. Uses the specified hashing to distribute. Cannot be combined with other DISTRIBUTE BY clauses.
Default: If no DISTRIBUTE clause is specified, automatic distribution is used, i.e., the system decides how to distribute the partitions.

A new column, HASHID, will be appended to the primary key that will be the last column of the primary key. For insert operations, the column HASHID should be filled with 0 and the database will fill it with the right value. It will create as many partitions as the number of storage engines in the database. It should be noted that DISTRIBUTE BY HASH cannot be combined with other distribution methods. If HASH partitioning has been specified no other distribution criteria is allowed.

The HASH partitioning and distribution method is straightforward to specify as it doesn’t require knowledge of data distribution on column ranges. However, it comes with a significant tradeoff - reads become significantly more expensive since they must be performed across all partitions of the table. There is an overhead associated to compute the hash, so only add the columns really necessary for an even partitioning. Also using less columns than necessary will result in an skewed partitioning.

When using DISTRIBUTE BY KEY, it should be noted that the partitions made by primary key will be distributed among the storage servers. If there are less primary key partitions than storage servers, then only a subset of the storage servers will be used for the table. It is recommended to have as many partitions as storage servers or a multiple of them.

When using DISTRIBUTE BY DIMENSION, it happens the same as with DISTRIBUTE BY KEY. Only the partitions that have been defined will be distributed among the storage servers. If there less partitions than storage servers, only a subset of them will be used. Again it is recommended that the number of partitions used for distribution are the same as storage servers.

If NO DISTRIBUTION is indicated, it means that all the table fragments will remain on a single storage server. It should be noted that even with several storage servers, the common case, one can distribute tables across storage servers, so no distribution is need on a per table basis. Having said so, in most cases the most effective way to distribute the workload is distributing all tables across all storage servers, this is especially true for large tables over which large queries are performed and tables with high loads of any other kind, either ingestion or queries.

If partitioning is specified but no explicit distribution method is defined, automatic distribution will be performed based on the following criteria: If hash partitioning is defined it will be used as distribute criterion. Otherwise, it distributes the partitions across storage servers.

For more in-depth details, refer to the section on writing efficiently in LeanXcale.

4. DROP TABLE and TRUNCATE TABLE Statements

The DROP TABLE statement facilitates the deletion of a specified table in SQL. It includes an optional IF EXISTS clause, which, when utilized, prevents the statement from generating an error if the table does not exist. The syntax is:

dropTableStatement=
	DROP TABLE [ IF EXISTS ] name

The TRUNCATE TABLE statement in SQL is used to quickly and efficiently remove all rows from a table. Unlike the DELETE statement, which removes rows one by one and generates individual row delete operations, TRUNCATE TABLE is a more direct and faster operation for removing all data from a table. It is syntax is:

truncateTableStatement=
	TRUNCATE TABLE name

5. ALTER TABLE Statement

The ALTER TABLE statement is used to modify the structure of an existing table. It allows you to add, modify, or drop columns, constraints, indexes, or perform other changes to the table definition. Its syntax is:

alterTableStatement=
      ALTER TABLE name alterTableAction

alterTableAction=
	RENAME TO newName
	| DROP COLUMN columnName
	| DROP constraintName
	| ADD COLUMN columnName columnType
	| ADD [ CONSTRAINT name ] tableConstraint
	| ADD PARTITION
		(
		KEY `(` columnName [, columnName]* `)` AT tupleLiteral [, tupleLiteral]*
		| DIMENSION columnName AT literal [, literal]*
		)

6. CREATE ONLINE AGGREGATE and DROP ONLINE AGGREGATE Statements

Online aggregates is a novel feature that LeanXcale provides. It can be thought as a materialized aggregation view with very efficient real-time update (it is updated as part of the transaction modifying the parent table). Basically, an online aggregate is a materialized aggregation query that is updated as part of the transaction updating the parent table, i.e., the table from which is derived. The syntax is as follows:

createOnlineAggregate:
	CREATE [DURABLE] ONLINE AGGREGATE
		[ IF NOT EXISTS ] [ NO OLD VERSIONS] aggregateName
		AS { * | projectItem [, projectItem ]* }
	FROM tableExpression
		[WHERE booleanExpression]
			[ GROUP BY { * | projectItem [, projectItem ]* } ]
				[ ( AUTOSPLIT columnName splitperiod [ AUTOREMOVE AFTER persistperiod ]
					| SPLIT columnName EVERY numericOrIntervalExpression [ AUTOREMOVE AFTER numericOrIntervalExpression ]

The optional "DURABLE" parameter serves to determine the persistence behavior of the online aggregate in relation to the parent table. When specified, the "DURABLE" attribute ensures that the online aggregate remains intact even if the parent table undergoes deletion. In contrast, when "DURABLE" is not explicitly indicated, the deletion of the parent table triggers a cascade deletion, leading to the removal of the associated online aggregate. The "IF NOT EXISTS" clause, analogous to its usage in the CREATE TABLE statement, does not result in an error if the online aggregate table already exists. This provision ensures that the creation process proceeds smoothly without interruption in the presence of a pre-existing online aggregate table. The "NO OLD VERSIONS" clause, akin to its counterpart in the CREATE TABLE statement, serves to deactivate multi-versioning. Consequently, only the latest version of each aggregate is retained, foregoing the retrieval of versions corresponding to transaction snapshots. The "AS" clause facilitates the specification of aggregates that the online aggregate will keep updated (such as SUMs, COUNTs, MINs, MAXs) and designates the columns from the parent table over which these aggregates are computed. Online aggregates, derived from a designated parent table indicated in the FROM clause, can be further filtered using the optional WHERE clause. This clause allows for setting a boolean condition, determining which rows from the parent table are considered by the online aggregate. The optional GROUP BY clause provides a means to specify the grouping criteria for computing the aggregates. If left unspecified, the aggregation encompasses all rows that satisfy the filter condition (if specified) or all rows if no filter condition is provided. Given that online aggregates are commonly defined over historical tables, using auto-splitting becomes imperative. The AUTOSPLIT clause may be specified over a temporal column or an auto-increment integer column. The optional AUTOREMOVE clause offers the ability to specify how data should be pruned after a certain period. It is should be noted that the intervals specified in "AUTOSPLIT" and "AUTOREMOVE" should align with those of the parent table or be a multiple thereof.

An online aggregate can be deleted with DROP ONLINE AGGREGATE:

dropOnlineAggregate=
	DROP ONLINE AGGREGATE onlineaggregateName

7. CREATE INDEX and DROP INDEX Statements

Secondary indexes play a pivotal role in optimizing access to columns that do not constitute a prefix of the primary key. The creation of these indexes is accomplished through the utilization of the CREATE INDEX statement, adhering to the subsequent syntax:

createIndexStatement=
CREATE [UNIQUE] INDEX [ IF NOT EXISTS ] indexName
	ON tableName '(' columnName [, columnName ]* ')'
		[INCLUDE '(' columnName [, columnName ]* ')']
		[GLOBAL [ partitionBySpec [ distributeBySpec ] ]

The optional "UNIQUE" clause serves to specify that only a singular value can exist for each combination of columns within the index. This uniqueness constraint ensures that the indexed columns collectively have distinct values.

The index is delineated over a specific table, as denoted by the ON clause. Following the specification of the tableName, a sequence of columns constituting the secondary key is enumerated within parentheses. These columns define the structure of the secondary index, facilitating efficient retrieval of data based on the specified column combinations.

The INCLUDE clause allows to defined covered indexes, that is, indexes that include additional columns. This is useful for optimizing queries over the secondary index. Basically, it enable to recover the value of the columns without having to perform a search on the primary key.

The GLOBAL clause indicates that the index will be global. Without the GLOBAL clause, indexes are local to each table fragment. That is, each table fragment has a fragment for the secondary index that only contains secondary keys for the tuples stored in the associated fragment of the primary table. Secondary indexes are convenient because they enable to read the tuple within the same storage server. However, there is an involved tradeoff. When searching with a secondary key, all storage servers containing fragment of the table are sent the query, with the consequent overhead. Instead, global indexes are like a table and they can be stored on any storage server. In fact, one can specify the partitioning and distribution strategy as for regular tables. Searches in global indexes only impact the storage servers containing a fragment that has secondary keys in the searched range. So only the storage servers containing relevant information are contacted. However, after recovering the primary keys of the tuples satisfying the query, they have to be searched on the storage servers containing them. The best option is to combine the global index with and INCLUDE clause including all the columns that can be used by the query. In this way only the storage servers containing secondary keys in the searched interval are contacted and the query is solved without a second search step. Basically global covered indexes (GLOBAL plus INCLUDE) as vertical partitions in columnar data warehouses.

To eliminate an index, the DROP INDEX statement is employed, using the following syntax:

dropIndexStatement=
	DROP INDEX [ IF EXISTS ] indexName

The statement begins with the DROP INDEX keywords, followed by an optional "IF EXISTS" clause, which ensures that the operation does not result in an error if the specified index does not exist. The indexName parameter designates the identifier of the index intended for removal. This process effectively dismantles the specified index, revoking its association with the corresponding table and freeing up resources.

8. CREATE VIEW and DROP VIEW Statements

A view is a virtual table derived from the result of a SELECT query. Unlike a physical table, a view does not store the data itself but provides a way to represent the result of a query as if it were a table. Views are useful for simplifying complex queries, abstracting the underlying data model, and controlling access to specific columns or rows of a table. It has the following syntax:

createViewStatement=
	CREATE VIEW [ IF NOT EXISTS ] viewName AS query

That creates a view named "viewName" from the result of the query provided in the AS clause.

A view can be removed by the DROP VIEW statement:

dropViewStatement:
      DROP VIEW [ IF EXISTS ] viewName

9. Sequences

A sequence is an object used to generate unique numeric values in a specified order. Sequences are commonly used to produce primary key values for tables, ensuring that each new record has a unique identifier. Unlike auto-increment columns, sequences provide greater flexibility and can be shared among multiple tables. Sequences use a BIGINT type (long integer). They are created by means of the CREATE SEQUENCE statement:

createSequenceStatement=
	CREATE SEQUENCE [ IF NOT EXISTS ] sequenceName sequenceModifier*

sequenceModifier=
	AS type
		|   START WITH initialValue
		|   INCREMENT BY incrementValue
		|   CACHE cacheSize
		|	NOFETCH

It should be noted that LeanXcale is a distributed database and might have multiple query engines and have multiple storage servers. A particular sequence will be handled by one storage server. The storage server writes each sequence value assigned to persistent storage. When a sequence is used intensely, one can regulate this behavior assigning a value to the CACHE modifier that will have the effect of writing persistently one out of CACHE times increasing significantly the efficiency. It should be noted in case of a crash of that storage server the sequence might have gaps in the following values after the recovery.

The client application interacts with a query engine. The query engine consumes a sequence value by invoking storage server in charge of that sequence. Since this interaction implies some overhead, the CACHE modifier can be used to make the query engine(s) fetch a number of values from the sequence to consume them locally. This means that in a multi-node configuration with multiple query engines each query engine will fetch a number of values. Thus, values of the sequence will not be necessarily consecutive along time. Thus, the CACHE modifier also affects the number of fetched values by each query engine. It is possible to prevent fetching to guarantee that values of the sequence are consecutive (that will be less efficient) by specifying the NOFETCH modifier.

The sequenceModifiers specify the behavior of the sequence. The START WITH clause indicates the first value to be given in the sequence. The INCREMENT BY clause sets the step size by which the sequence increases or decreases. The CACHE clause makes that query engine instance fetches a stripe of cacheSize length to assign sequence identifiers. In this way, the query engine does not have to request to a storage engine the next value of the sequence every time. Additionally, the storage server handling the sequence will persist the value of the sequence every CACHE times instead of every time. This is the preferred way of using sequences, with the CACHE option. The NOFETCH modifier disables the fetching in the query engine and guarantees that identifiers are consecutive (it has a negative performance impact).

One can consume the next sequence value by means of:

    next value for sequenceName

For instance, for a sequence called mySeq:

   select next value for mySeq;

will consume the next value of the sequence mySeq.

It is also possible to check the current sequence value (the last value consumed) of a sequence without fetching (with NOFETCH modifier) by:

   current value for seqName

For the previous example of mySeq sequence the current sequence value can obtained with:

   select current value for mySeq

Note that with fetching the concept of current value is fuzzy since each query engine will have a number of values of the sequence fetched and there is no notion of current sequence value.

A sequence can be removed with the DROP SEQUENCE statement:

   dropSequenceStatement:
   DROP SEQUENCE [ IF EXISTS ] sequenceName

10. CREATE USER, ALTER USER, and DROP USER Statements

New users can be created by means of the CREATE USER statement:

createUserStatement=
	CREATE USER userName IDENTIFIED BY 'password'

Leanxcale has two predefine users: LXADMIN and NONE. Users are case sensitive. When the are written without quotes they are always stored as capitalized. When they are written between quotes the capitalization within the quotes is used. Note that in the connection string of the drivers (JDBC, Python, ODBC) the user names are always case sensitive.

The password of a user can be changed with:

alterUserStatement=
	ALTER USER userName IDENTIFIED BY 'password'

A user can be removed by:

dropUserStatement=
	DROP USER userName

11. GRANT and REVOKE Statements

Permissions can be granted by:

grantStatement=
	GRANT permissionList TO userList
permissionList=
	permission [, permission]*

And revoked by:

revokeStatement=
	REVOKE permissionList TO userList

A permission has two parts:

permission=
	action databaseObject

The supported actions are:

action=
	CREATE | ALTER | DROP | SELECT | USAGE | INSERT | DELETE | UPDATE | REFERENCES | COMMENT | BACKUP | BECOME ANY USER | ALL PRIVILEGES

The supported database objects are:

databaseObjects=
	TABLE | INDEX | VIEW | SEQUENCE | TRIGGER | PROCEDURE | ANY

12. CREATE ROLE and DROP ROLE Statements

A role is a collection of permissions. In that sense they are similar to a user. The syntax is:

createRoleStatement=
	CREATE ROLE roleName IDENTIFIED BY 'password'

Permissions can be granted to a role and a user can be granted roles that is equivalent to grant to the user each of the permissions in the role.

A role can be deleted with:

dropRoleStatement=
	DROP ROLE roleName

13. CREATE TRIGGER, DROP TRIGGER, ENABLE TRIGGER, and DISABLE TRIGGER Statements

Triggers are a predefined action that is automatically executed ("triggered") in response to certain events on a particular table or view. These events can include data modification statements (such as INSERT, UPDATE, DELETE) or specific system events. They are created by means of CREATE TRIGGER with the following syntax:

createTrigger=
	CREATE TRIGGER [ IF NOT EXISTS ] triggerName
		(BEFORE | AFTER)
		[INSERT] [UPDATE] [DELETE]
	ON tableName
	FOR EACH ROW EXECUTE triggerFunction ['(' stringLiteral ')']
	[PRIORITY intLiteral]

BEFORE and AFTER are used to indicate whether the trigger has to be executed before the triggering event or after. INSERT, UPDATE, and DELETE specify the triggering event. The ON clause specifies on which table the trigger is defined. triggerFunction LeanXcale currently only supports row level triggers, that is, triggers associated to row events. For this reason, the only form allowed is FOR EACH ROW EXECUTE that determines that is a row level trigger. triggerFunction is the name of a Java function that contains the code of the trigger. PRIORITY indicates the priority of the trigger, in case, there are several defined, they are executed in priority order.

Triggers can be removed with DROP TRIGGER:

dropTriggerStatement=
	DROP TRIGGER triggerName

Triggers can be enabled by means of:

enableTriggerStatement=
	ENABLE TRIGGER triggerName ON tableName

And disabled with:

disableTriggerStatement=
	DISABLE TRIGGER triggerName ON tableName

14. Auditing (Audit Logs)

In this section we describe how to activate and deactivate the different auditing levels.

14.1. Auditing Levels

Auditing is set for all users, unless a user is specified. LeanXcale allows to set the auditing level at database level, table level, and schema level.

For the level chosen then the auditing granularity can be set per session (session is the default granularity, if none specified), per transaction, or per operation. Session granularity means that accesses to a table is logged once per session, the first time the access happen in the session. Transaction granularity means that accesses are logged once per transaction, that is, the first access that happens to an audited table. *Operation granularity means that only accesses with a specific kind of operation will be audited: READ, WRITE, DDL, and ANY. In case all kinds of operations should be audited, then ANY can be used.

14.2. Auditing SQL Syntax

audit=
	[NO] AUDIT [USER userName] [ (TABLE tableName) | (SCHEMA schemaName) ]
		[ BY
		( SESSION
		| TRANSACTION
		| [ READ | WRITE | READ WRITE | DDL | ANY ]
		) ]

To activate audit use AUDIT and to deactivate audit use NO AUDIT. The auditing can be set for a particular user using the USER clause, or for all users if the USER clause is omitted.

To audit the full database to which you connected omit table and schema clauses, and to audit a full schema use the SCHEMA clause and to audit a particular table use the TABLE clause. Note that a tableName no qualified with the schema name is referring to the schema indicated in the connection. To refer to tables in another schema the table name has to be qualified with the schema name: schemaName.tableName. When no audit granularity is specified, that is, there is no BY clause, the default audit granularity will be SESSION.

The BY clause enables to specify the audit granularity as SESSION, TRANSACTION, or operation. In the last case, the operation kind to be audited is indicated, or ANY if all kinds of operations should be audited.

14.3. Audit Logs

The audit log file contains auditing entries, including user authentication. User authentication is always enabled and cannot be disabled. When there is no audit, one can still find the authentication audit records in the lxqe log. When the audit is on, authentication audit records will also appear in the audit log.

Logs are accessed via lx logs command. The command provides the -g option that performs a grep with the pattern specified after -g. For example, to locate authentication entries for the user lxadmin the flag -g (for grep) can be used as shown here:

	unix$ lx logs -g auth.*lxadmin audit
	logs...
	atlantis: [
		log/audit.240221.0708.log	1.75K [
		1:	2024-02-21 07:08:10 lxqe100: audit: auth: lxadmin: 127.0.0.1: local: yes
		2:	2024-02-21 07:08:12 lxqe100: audit: auth: db-USR2: 127.0.0.1: local: yes
		3:	2024-02-21 07:08:13 lxqe100: audit: auth: lxadmin: 127.0.0.1: local: yes
		4:	2024-02-21 07:08:13 lxqe100: audit: auth: lxadmin: 127.0.0.1: local: yes
		5:	2024-02-21 07:08:13 lxqe100: audit: auth: lxadmin: 127.0.0.1: local: yes
		...
		]
	]

The user names include both the database and the user name, as a convenience. An exception to this rule is lxadmin that is a user across all databases, while the rest of the users are local to one database.

Authentication failures are reported as follows:

2024-02-29 06:40:52 lxqe100: audit: auth: db-FOO: 127.0.0.1:39018: local: no:auth failed

Another examples is:

	AUDIT TABLE MYTBL
	AUDIT USER USR1
	AUDIT USER USR1 TABLE MYTBL

And after trying to update the table MYTABL by USR1, the audit log file will include this audit record:

2024-02-29 06:40:51 lxqe100: audit: write: db-USR1: /db/USR1/tbl/MYTBL

When setting auditing with operation granularity, the audit log will contain audit records like:

2024-02-29 06:40:51 lxqe100: audit: write: db-USR1: SELECT count(*) FROM MYTBL

When permission is denied to execute a statement, an audit record is added, if audit is enabled:

2024-02-29 06:40:51 lxqe100: audit: perm: db-TEST1: /db/APP/tbl/PERSONS: select on table: permission denied

Authentication audit records are as follows:

2024-02-16 08:45:08,752 INFO lxqe: authenticate: lxadmin: local: yes

report that the user lxadmin (or whoever it was) was authenticated by the local (i.e., the DB) user entry.

When using LDAP, the audit record shows the following:

2024-02-16 08:45:08,752 INFO lxqe: authenticate: db-USR1: ldap: yes

This reports both the database used (db) and the user involved (USR1).

Authentication errors are reported in a similar way:

2024-02-16 08:48:09,992 INFO lxqe: authenticate: db-USR2: ldap: no: [LDAP: error code 49 - Invalid Credentials]