References

This document explains all the API that zeeSQL provide to the users.

zeeSQL is a Redis module, the command illustrate below are added on top of all the existing Redis commands. Using zeeSQL means using Redis at the same time. You can use any Redis client, in any programming language, even the redis-cli, to send the zeeSQL commands.

This document refers to the latest API, but zeeSQL commits to backward-compatible API.

For each command, it exposes first the name and then the syntax, and finally a brief explanation of what is going on inside the code.

Where is possible, it provides also an estimate of the complexity but since we are talking about databases not all queries have the same time and spatial complexity.

Finally, if it is appropriate the document also provides several references to external material that the interested reader can use to understand better the dynamics of every command.

ZEESQL.CREATE_DB

ZEESQL.CREATE_DB db_key [PATH path]

This command creates a new DB and associates it with the key.

The path argument is optional and, if provided is the file that SQLite will use. It can be an existing SQLite file or it can be a not existing file.

If the file exists and if it is a regular SQLite file that database will be used. If the file does not exist a new file will be created.

If the path is not provided it will open an in-memory database. Not providing a path is equivalent to provide the special string :memory: as the path argument.

After opening the database it inserts metadata into it and then starts a thread loop.

Complexity: O(1), it means constant, it does not necessarily mean fast. However, is fast enough for any use case facing human users (eg create a new database for every user logging into a website.)

Examples:

127.0.0.1:6379> ZEESQL.CREATE_DB DB
1) 1) "OK"

This command created an in-memory database. Persistency is managed by Redis with AOF or RDB following the setting of your Redis instance.

127.0.0.1:6379> ZEESQL.CREATE_DB on_disk_db PATH /tmp/foo.sqlite
1) 1) "OK"

This command created a database that uses a file as storage support, in this case /tmp/foo.sqlite.

If the file does not exist, it is created.

If the file is already an SQLite database, it gets used immediately with all the data already loaded.

If the file is not an SQLite database, an error is raised.

See also:

DEL

DEL db_key [key ...]

This command is a generic command from Redis.

It eliminates keys from Redis itself, as well if the key is a RediSQL database create with ZEESQL.CREATE_DB it will eliminate the SQLite database, stop the thread loop and clean up everything left.

If the database is backed by a file the file will be closed, but it won't be deleted.

Complexity: DEL is O(N) on the number of keys, if you are only eliminating the key associated with the SQLite database will be constant, O(1).

Examples:

127.0.0.1:6379> ZEESQL.CREATE_DB DB
1) 1) "OK"
127.0.0.1:6379> DEL DB
(integer) 1

See also:

  1. Redis DEL

ZEESQL.EXEC

ZEESQL.EXEC db_key
( (COMMAND "command") | (STATEMENT statement) )
[NOW]
[READ_ONLY]
[INTO stream]
[NO_HEADER]
[JSON]
[ARGS arg1 arg2 ... argn]

The EXEC command is the main command of zeeSQL. It allows interaction with the database stored in the db_key.

It takes as input the database db_key, either a COMMAND or a STATEMENT, an optional series of flags, and an optional variadic number of arguments.

You need to supply EITHER a command (using the COMMAND flag) or a statement (using the STATEMENT flag), but you need one of them.

Command vs Statement

The command is a valid SQL string. The command SQL string can contain arguments in the form ?n. Those arguments will be matched to the one provided at the end of the command. The first argument is bound against ?1, NOT against ?0. Arguments that are not provided will be bound to NULL.

The statement can be created with the ZEESQL.STATEMENT command. The arguments follow the same logic of the COMMAND variants.

Examples:

127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "create table foo(a INT, b string);"
1) 1) "DONE"
2) 1) (integer) 0

In this example, we create a new table.

127.0.0.1:6379> ZEESQL.STATEMENT DB NEW insert "insert into foo values(?1, ?2);"
1) 1) "OK"
127.0.0.1:6379> ZEESQL.EXEC DB STATEMENT insert ARGS 1 one
1) 1) "DONE"
2) 1) (integer) 1
127.0.0.1:6379> ZEESQL.EXEC DB STATEMENT insert ARGS 2 two
1) 1) "DONE"
2) 1) (integer) 1

Then we create a new STATEMENT to insert values, and we execute the statement twice.

127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from foo;"
1) 1) "RESULT"
2) 1) "a"
2) "b"
3) 1) "INT"
2) "TEXT"
4) 1) (integer) 1
2) "one"
5) 1) (integer) 2
2) "two"

We queried the values just inserted in the table executing another command.

NOW flag

By default zeeSQL offload the SQL computation to a secondary thread. This free the main Redis thread and keep the Redis instance reactive.

The NOW flags force zeeSQL to run the SQL computation in the main Redis thread.

READ_ONLY flag

The READ_ONLY flags communicate to zeeSQL that the execution will not modify the database.

If the execution might modify the database, and the READ_ONLY flag is passed, zeeSQL will return an error.

Passing the READ_ONLY flags allow zeeSQL to not replicate the command. Possibly saving computing resources of the replicas.

Examples:

127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from foo where a > ?1;" READ_ONLY ARGS 1
1) 1) "RESULT"
2) 1) "a"
2) "b"
3) 1) "INT"
2) "TEXT"
4) 1) (integer) 2
2) "two"

Here we correctly used the READ_ONLY flag to communicate with zeeSQL that the command will not modify the database.

zeeSQL correctly executes the query.

127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "insert into foo values(3, 'three')" READ_ONLY
(error) Statement is not read only but it may modify the database, use `EXEC` instead.

In this other case, we ask zeeSQL to modify the database while using the READ_ONLY flag.

zeeSQL correctly refuses to modify the database and returns an error.

NO_HEADER flag

By default zeeSQL returns information about the result set. It returns the name of the columns and their type.

This information might not be useful nor desirable.

With the NO_HEADER flag, only the result itself is returned.

Examples:

127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select a as number_as_integer, b as number_as_string from foo;"
1) 1) "RESULT"
2) 1) "number_as_integer"
2) "number_as_string"
3) 1) "INT"
2) "TEXT"
4) 1) (integer) 1
2) "one"
5) 1) (integer) 2
2) "two"

This is the default result from zeeSQL. It reports the name of the columns (in this case number_as_integer and number_as_string and their type INT and TEXT).

If we are not intereted in such information:

127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select a as number_as_integer, b as number_as_string from foo;" NO_HEADER
1) 1) "RESULT"
2) 1) (integer) 1
2) "one"
3) 1) (integer) 2
2) "two"

the NO_HEADER flags will omit it for us.

JSON flag

By default zeeSQL returns its result as an array of array. This makes parsing the result a little complex in some programming languages.

The JSON flags instruct zeeSQL to return a single JSON string as result.

Examples:

127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select a as number_as_integer, b as number_as_string from foo;" JSON
"{\"rows\":[{\"number_as_integer\":1,\"number_as_string\":\"one\"},{\"number_as_integer\":2,\"number_as_string\":\"two\"}],\"number_of_rows\":2,\"columns\":{\"number_as_integer\":\"INT\",\"number_as_strin
g\":\"TEXT\"}}"

The JSON is valid JSON even if compressed:

$ redis-cli ZEESQL.EXEC DB COMMAND "select a as number_as_integer, b as number_as_string from foo;" JSON | jq
{
"rows": [
{
"number_as_integer": 1,
"number_as_string": "one"
},
{
"number_as_integer": 2,
"number_as_string": "two"
}
],
"number_of_rows": 2,
"columns": {
"number_as_integer": "INT",
"number_as_string": "TEXT"
}
}

Of course, this can be combined with the NO_HEADER flag.

$ redis-cli ZEESQL.EXEC DB COMMAND "select a as number_as_integer, b as number_as_string from foo;" JSON NO_HEADER | jq
{
"rows": [
{
"number_as_integer": 1,
"number_as_string": "one"
},
{
"number_as_integer": 2,
"number_as_string": "two"
}
]
}

INTO stream

zeeSQL can push the result of a computation in a Redis Stream.

This is desirable if you want to:

  1. consume the result at a later time,

  2. or cache the result,

  3. or if the result is rather big and you don't want to send all of it over the network.

The INTO stream option will inform zeeSQL to push the result of the computation into the Redis STREAM called stream.

The INTO stream option is available only if the query is marked as READ_ONLY.

The command executes XADD to the stream.

If the stream does not exist a new one is created.

If the stream already exists the rows are simply appended.

The command itself is eager, hence it computes the whole result, append it into the stream, and then it returns. Once the command returns, the whole result set is already in the Redis stream.

The return value of the command depends on the result of the query:

If the result of the query is empty, it simply returns ["DONE", 0].

If at least one row is returned by the query the command returns:

1.the name of the stream where it appended the resulting rows, which is always the one passed as input 2. the first ID added to the stream 3. the last ID added to the stream 4. and the total number of entries added to the stream.

Using a standard Redis Stream all the standard consideration applies.

  1. The stream is not deleted by zeeSQL, hence it can be used for caching, on the other hand too many streams will use memory.

  2. The stream uses a standard Redis key, in a cluster environment you should be sure that the database that is executing the query and the stream that will accommodate the results are on the same cluster node.

This can be accomplished easily by forcing the stream name to hash to the same cluster node of the database, it is sufficient to use a stream_name composed as such {db_key}:what:ever:here. Redis will hash only the part between the { and } to compute the cluster node. 3. The result can be consumed using the standard Redis streams commands, two good starting points are XREAD and XRANGE.

The key of the stream elements are the tuple (type, column name) separated by a colon :.

Examples:

127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from foo;" INTO foo_stream
(error) Asked a STREAM, but the query is not `READ_ONLY` (flag not set), this is not supported.

At first, we tried to push the result of a query that is not marked as READ_ONLY and this correctly fails.

127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from foo where a > 100;" READ_ONLY INTO foo_stream
1) 1) "DONE"
2) 1) (integer) 0

Above we execute a query that returns an empty result.

zeeSQL simply communicates to us that the result is empty.

127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from foo;" READ_ONLY INTO foo_stream 1) 1) "RESULT"
2) 1) "foo_stream"
2) "1612797707753-0"
3) "1612797707753-1"
4) (integer) 2
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from foo;" READ_ONLY INTO foo_stream JSON
"{\"rows\":[{\"stream\":\"foo_stream\",\"first_id\":\"1612797722505-0\",\"last_id\":\"1612797722505-1\",\"size\":2}]}"

Then we push into the Redis stream foo_stream the result of the query select * from foo.

We do it twice, once using the standard return and the second time using the JSON return.

We pushed twice, a query that returned two results, so we expect 4 elements in the stream.

127.0.0.1:6379> XLEN foo_stream
(integer) 4

We can read the elements from the stream using the standard Redis stream interface. In this case, we are going to read only the first two elements.

127.0.0.1:6379> XRANGE foo_stream - + COUNT 2
1) 1) "1612797707753-0"
2) 1) "int:a"
2) "1"
3) "text:b"
4) "one"
2) 1) "1612797707753-1"
2) 1) "int:a"
2) "2"
3) "text:b"
4) "two"

Complexity: Besides the complexity of the query, the INTO stream options add the complexity of adding each row to the Redis stream, which is O(n) where n is the amount of row returned by the query.

See also:

  1. XADD

  2. XREAD

  3. XRANGE

ARGS arguments

The ARGS arguments are used to pass arguments to the statement or command.

They are variadic, and you can pass as many as you need.

In the SQL, the first argument will be bound to ?1, the second to ?2, and so on. Please note that the first argument is NOT bound to ?0.

If an argument is not bound, for instance, you pass a query with ?4 but only provide 3 arguments, then, that argument is bound to NULL.

Redis works using a text protocol, all the arguments are encoded as text, hence the module is forced to use the procedure sqlite3_bind_text, however, SQLite is smart enough to recognize numbers and treat them correctly. Numbers will be treated as numbers and text will be treated as text.

See also:

ZEESQL.QUERY

ZEESQL.QUERY db_key
( (COMMAND "command") | (STATEMENT statement) )
[NOW]
[INTO stream]
[NO_HEADER]
[JSON]
[ARGS arg1 arg2 ... argn]

This command behaves similarly to ZEESQL.EXEC but it imposes an additional constraint on the statement it executes.

It only executes the statement if it is a read-only operation, otherwise, it returns an error.

A read-only operation is defined by the result of calling sqlite3_stmt_readonly on the compiled statement.

The statement is executed if and only if sqlite3_stmt_readonly returns true.

This command is exactly like ZEESQL.EXEC ... READ_ONLY however it can be executed against Redis replicas.

Complexity: Similar to ZEESQL.EXEC, however, if a statement is not read-only it is aborted immediately and it does return an appropriate error.

See also:

ZEESQL.STATEMENT

ZEESQL.STATEMENT db_key
(
(NEW stmt "query" [CAN_UPDATE]) |
(DELETE stmt) |
(UPDATE stmt "query" [CAN_CREATE]) |
(SHOW stmt) |
LIST
)
[NOW]

This command manages zeeSQL statements.

A statement is a pre-compiled SQL query, if you are going to execute your query over and over again, it is a good idea to make it into a statement. Under the hood it is a sqlite statement.

Statements can be used in the ZEESQL.EXEC db_key STATEMENT stmt command and in the ZEESQL.QUERY db_key STATEMENT stmt command.

Five different actions can be invoked with the STATEMENT command.

  1. Create a new statement with the NEW option.

  2. Delete a statement with the DELETE option.

  3. Update a statement with the UPDATE option.

  4. Show the SQL behind a statement with the SHOW option.

  5. List all the statements with the LIST option.

The STATEMENT command includes the NOW flag. The NOW flag forces zeeSQL to execute the action in the main Redis thread. In standard operations mode, it should not be used.

NEW

The NEW option takes as input the name to associate with the statement and an SQL query to compile.

The command compiles the SQL query into a pre-compiled statement, and associate it with the name.

The CAN_UPDATE flag to the NEW command, instruct zeeSQL to behave as an UPDATE if the statement name is already allocated to an old statement. Otherwise, without the CAN_UPDATE flag, if the statement name is already used by a different statement, the command fails with an error.

DELETE

The DELETE option deletes a statement.

UPDATE

The UPDATE option updates a statement, associating the old name with the statement compiled from the SQL query.

If the name does not exists, UPDATE fails, unless the CAN_CREATE flag is provided. In such a case UPDATE behave like NEW.

SHOW

The SHOW option returns the SQL query behind one statement.

LIST

The LIST option returns all the statements and their SQL queries.

Both SHOW and LIST will report:

  1. The name of the statement

  2. The SQL query associate with the statement

  3. The number of parameters the statement expects

  4. If the statement is read only or not

Complexity: Operation on the statements happens in constant time O(1). Listing the statements happens in O(n) with n number of statements present in the database.

Examples:

At first we create a new statement,

127.0.0.1:6379> ZEESQL.STATEMENT DB NEW select_1 "SELECT 1;"
1) 1) "OK"

We can then list, the statement:

127.0.0.1:6379> ZEESQL.STATEMENT DB LIST
1) 1) "RESULT"
2) 1) "identifier"
2) "SQL"
3) "parameters_count"
4) "read_only"
3) 1) "TEXT"
2) "TEXT"
3) "INT"
4) "INT"
4) 1) "select_1"
2) "SELECT 1;"
3) (integer) 0
4) (integer) 1

The statement can be updated, but we need to use the UPDATE command or the CAN_UPDATE flag.

127.0.0.1:6379> ZEESQL.STATEMENT DB NEW select_1 "SELECT '1';"
(error) The statement is already present in the database, try with UPDATE_STATEMENT
127.0.0.1:6379> ZEESQL.STATEMENT DB NEW select_1 "SELECT '1';" CAN_UPDATE
1) 1) "OK"
127.0.0.1:6379> ZEESQL.STATEMENT DB UPDATE select_1 "SELECT '1';"
1) 1) "OK"

We change select_1 to return a string and not an integer.

127.0.0.1:6379> ZEESQL.STATEMENT DB UPDATE select_plus_one "SELECT ?1 + 1;" CAN_CREATE
1) 1) "OK"

We create another statement, this time we create the statement with the UPDATE command and the CAN_CREATE flag.

127.0.0.1:6379> ZEESQL.STATEMENT DB LIST
1) 1) "RESULT"
2) 1) "identifier"
2) "SQL"
3) "parameters_count"
4) "read_only"
3) 1) "TEXT"
2) "TEXT"
3) "INT"
4) "INT"
4) 1) "select_1"
2) "SELECT '1';"
3) (integer) 0
4) (integer) 1
5) 1) "select_plus_one"
2) "SELECT ?1 + 1;"
3) (integer) 1
4) (integer) 1
127.0.0.1:6379> ZEESQL.EXEC DB STATEMENT select_plus_one NO_HEADER ARGS 5
1) 1) "RESULT"
2) 1) (integer) 6

See also:

ZEESQL.COPY

ZEESQL.COPY
FROM db_key_source
TO db_key_destination
[NOW]

The command copies the source database into the destination database.

The content of the destination databases is completely ignored and lost.

It is not important if the databases are stored in memory or backed by disk, the COPY command will work nevertheless.

This command is useful to:

  1. Create backups of databases

  2. Load data from slow, disk-based, databases into a fast in-memory one

  3. To persist data from an in-memory database into a disk-based database

  4. Initialize a database with a predefined status

Usually, the destination database is an empty database just created, while the source one is a database where we have been working for a while.

This command use the backup API of sqlite.

Complexity: The complexity is linear on the number of pages (dimension) of the source database, beware it can be "slow" if the source database is big, during the copy the source database is busy and it cannot serve other queries.

Example:

127.0.0.1:6379> ZEESQL.CREATE_DB DB01
1) 1) "OK"
127.0.0.1:6379> ZEESQL.EXEC DB01 COMMAND "create table foo(a, b);"
1) 1) "DONE"
2) 1) (integer) 0
127.0.0.1:6379> ZEESQL.EXEC DB01 COMMAND "insert into foo values(1,2),(3,4);"
1) 1) "DONE"
2) 1) (integer) 2
127.0.0.1:6379> ZEESQL.CREATE_DB DB02
1) 1) "OK"
127.0.0.1:6379> ZEESQL.EXEC DB02 COMMAND "select * from foo"
(error) no such table: foo
127.0.0.1:6379> ZEESQL.COPY FROM DB01 TO DB02
1) 1) "OK"
127.0.0.1:6379> ZEESQL.EXEC DB02 COMMAND "select * from foo"
1) 1) "RESULT"
2) 1) "a"
2) "b"
3) 1) "INT"
2) "INT"
4) 1) (integer) 1
2) (integer) 2
5) 1) (integer) 3
2) (integer) 4

In the example we create a database, we create a table, and then we pushed few rows into the table.

We then create another database, but this time we didn't create the table, neither pushed any row.

As expected, trying to query the second database returned an error.

After copying the content of the first database into the second, the second database has become a perfect copy of the first one.

See also:

ZEESQL.INDEX

The index command accepts 3 different options:

  1. NEW

  2. LIST

  3. DELETE

We will illustrate them separately.

ZEESQL.INDEX NEW

ZEESQL.INDEX db_key
NEW
TABLE table_name
[PREFIX prefix]
SCHEMA column_name column_type [column_name column_type ...]

Creates a new secondary index table for the Redis hashes.

The secondary index will refer to the table table_name and will use the columns indicated in the SCHEMA parameter.

column_type can be whatever is accepted by SQLite as column name, suggestions are TEXT, INT, FLOAT or BLOB.

If a prefix is provided, only the Redis hashes that start with that prefix are indexed in the table. If the prefix is omitted, the * prefix (catch-all) is assumed.

If the table does not exists when the index is created, zeeSQL creates it.

If the table already exists, zeeSQL takes no action. However, if the table exists but contains the wrong columns, zeeSQL may find it impossible to insert the hashes into the secondary index.

It is possible to create multiple indexes, with the same table, same schema, but different prefix.

zeeSQL store in the secondary index table, the main Redis hash key, as primary key.

The table created by zeeSQL behaves like any other table, so it can be queried, modified, and indexed, using the standard ZEESQL.EXEC interface.

No steps are taken to avoid manual deletions or updates of the secondary index table by the user.

Secondary indexes are univocally identified by the combination of the table in which they write and the prefix.

Example:

In this example, we can see how to create a secondary index, and how the secondary index automatically keeps the values between Redis and zeeSQL in synchronism.

127.0.0.1:6379> ZEESQL.INDEX DB NEW TABLE users prefix user:* SCHEMA username STRING score INT
OK
127.0.0.1:6379> HMSET user:1001 username aaa score 0
OK
127.0.0.1:6379> HMSET user:1002 username bbb score 0
OK
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from users"
1) 1) "RESULT"
2) 1) "key"
2) "username"
3) "score"
3) 1) "TEXT"
2) "TEXT"
3) "INT"
4) 1) "user:1001"
2) "aaa"
3) (integer) 0
5) 1) "user:1002"
2) "bbb"
3) (integer) 0
127.0.0.1:6379> HINCRBY user:1002 score 3
(integer) 3
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from users"
1) 1) "RESULT"
2) 1) "key"
2) "username"
3) "score"
3) 1) "TEXT"
2) "TEXT"
3) "INT"
4) 1) "user:1001"
2) "aaa"
3) (integer) 0
5) 1) "user:1002"
2) "bbb"
3) (integer) 3

ZEESQL.INDEX LIST

The list option shows the active secondary index.

The secondary indexes are identified by the table in which they write and by the prefix they use to filter the keys.

Example:

127.0.0.1:6379> ZEESQL.INDEX DB LIST
1) 1) "RESULT"
2) 1) "users"
2) "user:*"
127.0.0.1:6379> ZEESQL.INDEX DB NEW TABLE games prefix games:* SCHEMA first_player STRING second_player STRING score_player_1 INT score_player_2 INT
OK
127.0.0.1:6379> ZEESQL.INDEX DB LIST
1) 1) "RESULT"
2) 1) "users"
2) "user:*"
3) 1) "games"
2) "games:*"

ZEESQL.INDEX DELETE

ZEESQL.INDEX db_key
DELETE
TABLE table_name
[PREFIX prefix]

The DELETE option removes a secondary index.

Hashes that match the prefix are not inserted anymore in the table after the index is removed.

The DELETE option takes as input the table and the prefix that identifies the secondary index to remove.

If the prefix is omitted the * (catch-all) prefix is assumed.

Example:

127.0.0.1:6379> ZEESQL.INDEX DB NEW TABLE users PREFIX user:* SCHEMA username STRING score INT
OK
127.0.0.1:6379> HMSET user:1001 username first_user score 12
OK
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from users" NO_HEADER
1) 1) "RESULT"
2) 1) "user:1001"
2) "first_user"
3) (integer) 12
127.0.0.1:6379> ZEESQL.INDEX DB DELETE TABLE users PREFIX user:*
1) 1) "DONE"
2) 1) (integer) 1
127.0.0.1:6379> HMSET user:1002 username second_user score 5
OK
127.0.0.1:6379> ZEESQL.EXEC DB COMMAND "select * from users" NO_HEADER
1) 1) "RESULT"
2) 1) "user:1001"
2) "first_user"
3) (integer) 12

In the example, we first create an index.

Then we add a Redis hash that matches the secondary index prefix, so it is added to the secondary index table.

Then, delete the index.

And we can confirm that new users are not added any more to the secondary index table.

ZEESQL.LICENSE

ZEESQL.LICENSE
( SET "license" ) | SHOW

The SET option will set the license to use for zeeSQL.

The license is first checked against our backend server, and if the license is correct, it will return OK otherwise it will return an error.

It is required an internet connection to set the license.

The SHOW option will show the license that is actually in use in your zeeSQL process.