Data Definition Language (DDL) Syntax

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 aggregation of values with conflict-free updates, such as a sum. However, it is recommended to use online aggregates that is a high level functionality. Delta tables requires to have certain knowledge of commutative concurrency control.

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.

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 convenient to disable multi-versioning to improve performance. This action is undertaken to remove the associated overhead and increase the efficiency of insert and update operations within the LeanXcale database (i.e., all the overhead of concurrency control is avoided).

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:

A tableColumn has the following syntax:

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:

The AS IDENTITY clause indicates that is an 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:

It should be noted that in delta columns the value passed in the update, it is actually considered as a delta value to be aggregated with the current value of the column. The kind of delta column determines the aggregation to be applied with each update. 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 provided in the update. 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 value provided in the update.

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

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 a set of columns 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 referenced in the table. The referenced columns should be unique, that is, either the primary key (or prefix) that is always unique, or a set of columns that are set as unique (or a prefix of them). A FOREIGN KEY constraint results in creating an index in the referencing table with the referencing columns. This index is used to check efficiently whether a row in the referenced table has referencing rows in the referencing table. The unique requirement on the referenced columns of the referenced table guarantees that when adding a row in the referencing table it can be efficiently checked that the referenced columns exist in the referenced table by using the index created for the unique constraint.

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. This constraint results in creating and index with the columns referred in the constraint.

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

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.

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:

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

Auto-partitioning can be applied to dimension columns of type TIMESTAMP, TIME, DATE, or an auto-increment integer (it could also be an integer coding the date as YYYYMMDD). 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:

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.

When using the EVERY subclause for auto partitioning, sometimes one might insert data that is too far from the data used for current partitions. For instance, when using a timestamp one might be using timestamps from the last two years, but sometimes introduce a 0 value as timestamp. Since auto partitioned regions cannot have gaps to enable to compute fast in which region to store the data, for this reason when a gap is created LeanXcale fills the gap will all required regions. For instance, if one has rows with timestamps from 2023 and 2024 and then one introduces a row with a timestamp 0, then all regions between 0 and 2023 will be created that takes time and consumes disk space. To avoid this, one can specify ranges where gaps will not be filled with the clauses SINCE (meaning no auto split will happen before the specified value) and UNTIL (meaning no auto split will happen after the specified value). The dateTimeLiteral can be either a date with this form:

Or a timestamp:

The timestamp can also have fractions of second.

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:

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

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.

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.

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.

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:

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:

Another examples is:

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

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

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

Authentication audit records are as follows:

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:

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

Authentication errors are reported in a similar way: