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. Each user has a schema associated that has the same name as the user. The default schema for LXADMIN is APP. The default schema for all other users is their own schema. When database objects are created without qualifying the name with the schema they are created in the default schema of the user.

The CREATE DATABASE allows to create a new database:

The user LXADMIN is administrator of all databases in the system. If the optional USER clause is provided, then a user userName is created within the new database and it becomes the owner of the database, that is, it has all permissions over the newly created database. Note that a connection is over a particular database. So after creating a database to be able to access it, it is needed to reconnect to the system with the new database.

These are two examples of use:

The first example creates the database without specifying the owner, so the owner by default will be LXADMIN. The second example creates the database with user USR as owner. This user is created as part of the database creation and made the owner of the database. Remember that owners are local to each database, so USR is a user only on MYDB2 database.

The DROP DATABASE enables to remove a database:

An example removing one of the databases we created before is:

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

Note that the schema should exist, since the statement only performs the specified batch of creates and grants over the existing schema. If one of the create statements fails, it will not continue processing the rest.

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

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:

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:

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:

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:

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:

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:

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:

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.

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:

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:

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:

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:

For instance, for a sequence called 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:

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

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:

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

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:

A user can be removed by:

Permissions can be granted by:

The grant is for all the database unless the clause ON is provided. If the clause ON is provided then the grant is local to the object provided, that is, the table, sequence or schema specified in the ON clause.

And revoked by:

A permission can take three forms:

If it is a usrId or roleId the permission is actually the permission list associated to the user or role, otherwise, it is the permission to execute an action over a database object.

The supported actions are:

The supported database objects are:

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

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:

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:

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:

Triggers can be enabled by means of:

And disabled with:

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

Database transactions provide the so-called ACID properties. The I stands for isolation and it takes care of the consistency of the data in the advent of concurrent transactions updating the same row(s). The higher the consistency level the most consistency is attained, but it is more costly to execute transactions. The levels from less to more consistency are (short names between parenthesis):

The default isolation mode is read_committed. The isolation mode is specified in the connection string in the driver with the property mode using either the long or short name of the isolation mode. The isolation mode can be changed via the SQL statement SET ISOLATION LEVEL described in Section 5

The DML SQL statements supported are:

Multiple SQL statements can be provided by separating them with semicolons:

A query statement can have the following forms:

For modifying the database there are four kinds of statements allowing to upsert, insert, update, and delete rows from the database. Upsert updates or insert depending whether a row exists with the provided primary key or not. Insert adds a new row with that primary key, if there is no row with that primary key already in the table (otherwise it returns an error). Update modifies a row with a particular primary key, if there is a row in the table with that primary (otherwise it returns an error). Upsert is preferred over insert and update since it is the most efficient. Insert and update both require to perform an additional read to check whether the row exists or not, basically duplicating the cost of the statement. Their syntax is as follows:

In INSERT and UPSERT statements if the list of target columns is not provided, then the statement must have the same number of columns as the target table and the order of the fields in the VALUES clause must be the same as the order of the fields in the table.

The current isolation level can be changed within the transaction using the SET ISOLATION LEVEL statement that has the following syntax:

LeanXcale has multiversion concurrency control that provides highly efficient isolation management between reads and writes. The isolation levels were defined multiversion concurrency control was invented and are based on the notion of locking. LeanXcale supports 4 isolation levels:

The EXPLAIN statement information about the execution plan of a SQL query. The execution plan describes how the database engine will execute the query and retrieve the required data. This information can be helpful for database administrators, developers, and analysts to optimize and tune their queries for better performance:

A hint is an instruction to the optimizer. When writing SQL, you may know information about the data unknown to the optimizer. Hints enable you to force decisions otherwise made by the optimizer.

LeanXcale supports two kinds of hints:

This is the syntax:

The EXEC statement enables to invoke a table function. Its syntax is:

The parameters can be any expression that is not a query returning a value of the parameter type.

When auto-commit is set, and requesting an insert/update/delete operation from the result of a select statement with a large resultset, one might one instead of performing a large commit of the whole set of updates, a commit for smaller batches of updates. This can be performed with the BATCH statement that changes the implicit behavior of auto-commit. Instead of committing one time at the end of the statement, it will commit every number of indicate updates to commit every number of times indicated in the parameter of the BATCH statement:

Identifiers are the names of tables, columns and other metadata elements used in an SQL query. Identifiers can be quoted or unquoted. Quoted identifiers, such as "Employee Name", start and end with double quotes. Unquoted identifiers, such as employee, must start with a letter and can only contain letters, digits, and underscores. They are implicitly converted to upper case. Identifiers are case insensitive when unquoted (actually, it is as they were written in CAPS), and case sensitive when quoted. Quoted identifiers may contain virtually any character, including spaces and other punctuation. If you wish to include a double quote in an identifier, use another double quote to escape it, as in this example: "An employee called ""Fred"".". Matching identifiers to the name of the referenced object is case-sensitive.

LeanXcale supports both implicit and explicit conversion in expressions.

The following sections provides a summary of all operators and functions supported by LeanXcale.

LeanXcale is extensible. One can define new functions using user code. There are several ways to define a function.

To implement a scalar function, there are 3 options:

To implement an aggregate function, there are 2 options:

Optionally, add a public merge method to the class; this allows Calcite to generate code that merges sub-totals.

Optionally, make your class implement the SqlSplittableAggFunction interface; this allows Calcite to decompose the function across several stages of aggregation, roll up from summary tables, and push it through joins.

To implement a table function, there are 3 options:

To implement a table macro, there are 3 options:

LeanXcale infers the parameter types and result type of a function from the parameter and return types of the Java method that implements it. Further, you can specify the name and optionality of each parameter using the Parameter annotation.

MATCH_RECOGNIZE is a SQL extension for recognizing sequences of events in complex event processing (CEP).

The syntax is as follows:

In patternQuantifier, repeat is a positive integer, and minRepeat and maxRepeat are non-negative integers.

Currently, the syntax CREATE TABLE …​ AS SELECT …​ is not supported. In order to copy data from one table to another, one first need to create the new table and then do INSERT/UPSERT …​ SELECT. This is an example:

Additionally, the WITH TEMPORAL AS clause for using a temporal table can be used as in the following example:

JDBC function escapes are a mechanism provided by the JDBC API to invoke database-specific functions and procedures using standard SQL syntax within a Java application. JDBC function escapes are useful to use functions in Java code.

The syntax for JDBC function escapes involves using curly braces {} to enclose the call to the database-specific functions. The format typically looks like {fn functionName(argument1, argument2, …​)}. The following sections provide the different functions for different data types.

The syntax to create a trigger is as follow:

where

To find out the loaded trigger functions, you can query TRIGGER_FUNCTIONS system table.

Trigger functions are java objects that are automatically loaded into the Query Engine and provide a mechanism to build the Trigger’s executors, that holds the actual trigger procedural code. In order to create a custom trigger function, you need to implement from TriggerFunctionI Interface and define the following methods:

An instance of trigger utils provides connection information and some other functionalities that could be useful in order to implemet a trigger like creating a no-SQL direct session, get the next or current value of a sequence, generate a uuid, etc. Find below the available methods:

As you might realize, TableFunctionUtils is an extended implementation of TriggerUtils, see Table Function Utils. You could use TableFunctionUtils within trigger’s code but we strongly recommend to use the given TriggerUtils instance so the trigger can be fired by no-SQL API events too.

For developments environments, it could be interesting to execute a table funtion in order to test a trigger behavior. In order to do this, we provide an abstract class TriggerTableFunction that will save you from do it yourself. As you can see in the following example, you would just need to add the definitions of the table’s function methods getFunctionName() and eval(..).

Don’t forget to return the evalTrigger(NamedValuesI newValues, NamedValuesI oldValues, String args) result so the trigger is executed and its result is returned by the table function. Note that evalTrigger params will be mapped to the triggerExecutor ones, and it will invoke buildArguemnts.

Despite of custom triggers, we provide some system triggers that could be used directly by the user.

First you need to add the LeanXcale maven repository and the qe-tableFunctions dependency to your pom.xml file:

In order to create a custom table function, a Java class has to be written implementing the AutoloadTableFunction interface and defining the following methods:

You can define as many eval functions as you need as long as their signatures are different. In order to write the eval functions returning ScannableTable, the following methods should be implemented:

All the table function classes must belong to the package com.leanxcale in order to have them auto-loaded when LeanXcale starts.

Find below a complete example:

Once you do mvn package, a .jar file gets created with the custom table functions you defined. This .jar should be included in the Query Engine’s classpath (lxs/LX-BIN/lib). The Query Engine should be restarted after adding the table function .jar file. For the cloud version, the leanxcale console will provide the means to add the table function code to your database.

You can use a table function in the FROM clause of a query as the first argument to the function table. In this example, series_generator is the table function:

Take into account that if the parameter you’re passing to your table function is a string, you’ll need to use single quotes. Double quotes are not allowed. This is an example:

But this does not work:

In order to ease the writing of table function, you can use the TableFunctionUtils class. You will be able to execute SQL queries within your Table Function.

Find below an example:

LeanXcale can speed up queries over geospatial data by using Geohash indexing. Geohash is an encoding geographic location on Base 32 where the even bits represent the longitude precision and the odd bits represent the latitude precision. Geohash has some interesting properties so from an envelop you can narrow down the bins in which you have to look for geometries.

More information can be found in the following links:

The geohash index can be the primary key of a table or a secondary. In general the primary key performs better and is the preferred way to define a geom table.

To create a table with geohash you need the following syntax:

Instead of a geometry field which can be an arbitrary geometry, you may use two fields that will be used as latitude and longitude:

When running a geo query, LeanXcale’s query optimizer will automatically detect the Geohash index and use it to narrow down your search:

Most frequently, all the functionality for geohashing is done internally so there is no need to explicitly define any condition for geohash. Anyway, the following functions are supported to query through the Geohash indexes:

The following functions combine 2D geometries.

See also: the ST_Union aggregate function.

Inline hints are defined as special comments. Depending on the hint type, it can be written in two locations:

Here you can see the kinds of hint:

The syntax is as follows:

Context Hints are established within a session for a given connection and affect all queries issued in that session after being set. The actions that can be performed for context hints are:

In the following subsections we explain the different steps.

This table function removes all the query plans that have been cached for a given table (not only for this connection but for every query in the server). It can be useful to ensure that a new query plan will be calculated when a hint is added or removed. However, it has an associated overhead, it will force to generate the plan for all subsequent queries that is an expensive operation.

It is invoked as follows:

Where TABLE_NAME is the qualified name of the table (i.e. db-APP-CUSTOMER) from which the query plans will be removed.

These two functions let the user to enable or disable the optimizer logger. To be able to use it, you have to define a logger in your log4j configuration for the class org.apache.calcite.plan.RelOptPlanner with the level you want.

When enabling it, the log level for the RelOptPlanner logger will be set to ALL, and the planner will log all the query optimization steps in the appender configured for the RelOptPlanner logger.

Use it with caution because the amount of generated data can affect negatively the performance of the query preparation.

They can be invoked as follows:

It shows all the tables. Initially, only metadata tables are shown. The columns of the table are as follows:

Note that indexes do not appear in this list because they are not tables by themselves.

This system table shows all the columns of all tables in the system. It has the following columns:

Here there is a fragment of the table contents after creating a user table T with two columns PK and C:

It shows the primary keys for user created tables.

The column names of this system table are:

This is the contents after creating a table t with a column pk as primary key:

It contains all the declared foreign keys in the system for user created tables. The column names of this system table are:

It shows all the indexes created for user created tables in the system.

The columns are:

An example of the table contents with an index created for a table is:

It shows all the table integrity constraints defined in user created tables (NOT NULL, …​). The columns are:

It shows all active transactions in the Query Engine relating the information to the connection. The following fields are showed:

It contains all the currently open connections with the database.

The columns are:

This is an example of the table contents with just the connection being used by the SQL console:

It contains the user defined online aggregations.

Its columns are:

An example follows:

It shows information about the group by expressions for existing online aggregation relations

It shows information about the filter expressions for existing online aggregation relations

It shows information about the aggregation columns for user defined tables.

It shows information about the table partitions and the split points for each partition.

The columns are:

This is an example with a table with a single partition containing all data.

It contains the collected statistics and their values.

Its columns are:

This is an example of the stats of an empty table:

It shows information about table statistics kept by the storage servers.

Its columns are:

This shows the number of blocks held in each storage server:

Deprecated. Not to be used.

It contains the permissions that have been granted. Its columns are:

It shows execution plans that are currently cached.

Its columns are:

An example with a fragment of the contents is:

It contains all predefined and user defined trigger functions.

Its columns are:

An example is:

It contains all predefined and user defined trigger functions.

Its columns are:

It shows available table functions.

Its columns are:

An example with a short fragment of the contents is:

It shows user defined views.

Its columns are:

And example is:

First you need to add the LeanXcale maven repository and the qe-tableFunctions dependency to your pom.xml file:

In order to create a custom table function, a Java class has to be written implementing the AutoloadTableFunction interface and defining the following methods:

You can define as many eval functions as you need as long as their signatures are different. In order to write the eval functions returning ScannableTable, the following methods should be implemented:

All the table function classes must belong to the package com.leanxcale in order to have them auto-loaded when LeanXcale starts.

Find below a complete example:

Once you do mvn package, a .jar file gets created with the custom table functions you defined. This .jar should be included in the Query Engine’s classpath (lxs/LX-BIN/lib). The Query Engine should be restarted after adding the table function .jar file. For the cloud version, the leanxcale console will provide the means to add the table function code to your database.

You can use a table function in the FROM clause of a query as the first argument to the function table. In this example, series_generator is the table function:

Take into account that if the parameter you’re passing to your table function is a string, you’ll need to use single quotes. Double quotes are not allowed. This is an example:

But this does not work:

In order to ease the writing of table function, you can use the TableFunctionUtils class. You will be able to execute SQL queries within your Table Function.

Find below an example:

The syntax to create a trigger is as follow:

where

To find out the loaded trigger functions, you can query TRIGGER_FUNCTIONS system table.

Trigger functions are java objects that are automatically loaded into the Query Engine and provide a mechanism to build the Trigger’s executors, that holds the actual trigger procedural code. In order to create a custom trigger function, you need to implement from TriggerFunctionI Interface and define the following methods: