Tesseract¶

See Also¶

• Transactions

Syntax¶

COMMIT [ WORK | TRANSACTION ]

WORK

Optional keyword. It has no effect.

TRANSACTION

Optional keyword. It has no effect.

Overview¶

COMMIT commits the current transaction. All changes made by the transaction become visible to others.

Compatibility¶

The SQL standard only specifies the two forms COMMIT and COMMIT WORK.

See Also¶

• Transactions

Syntax¶

CREATE INDEX <index_name> ON <table_name> (<field>)

index_name

The name of the index not exist. Index names must be globally unique - that is different tables cannot have a index with the same name.

table_name

The table to create the index on. Every time a record is added, modified or deleted it will require all index entries for that row to be updated.

field

The field to index.

Overview¶

For those already comfortable with SQL and indexes you need not read must more beyond this point. Indexes are intended to work exactly the same way in tesseract.

Each index will require space proportional to the amount of data that is in the original table and how big each entry is to store. As well as the space consideration every time a record is modified it will have to update all the indexes for that table with respect to the new or modified record. This is why it is not wise to create lots of indexes unless you have a specific need to filter on that attribute.

Lets take a real world example, lets say we have a simple address book that stores entries like:

{
    "first_name": "Bob",
    "last_name": "Smith",
    "phone": "1234 5678",
}

Finding all Bob’s is simple:

SELECT * FROM contacts WHERE first_name = 'Bob'

This is fine for a small address book because computer can search very quickly. But there is a point in which this starts to make less sense and require more and more resources for the same operation - what if there were thousands or even million of contacts?

Using the example above we can run an explain to see how tesseract is deciding to carry out the SQL:

EXPLAIN SELECT * FROM contacts WHERE first_name = 'Bob'

[
    {"description": "Full table scan of 'contacts'"}
    {"description": "Filter: first_name = \"Bob\""}
]

It is choosing to do it this wway becuase it has no other choice. But we can introduce and index on the first_name:

CREATE INDEX contacts_first_name ON contacts (first_name)

Since indexes names must be globally unique it is a good idea (and often common practice) to put the table name and column name as the index name for both clarity and avoiding naming collisions.

By creating the index it will store all the `first_name`s in a separate place, in a special order/structure and will automaitcally maintain this index with every modification.

Now we can run the same EXPLAIN:

EXPLAIN SELECT * FROM contacts WHERE first_name = 'Bob'

[
    {"description": "Index lookup using contacts_first_name for value \"Bob\""}
]

It does not matter if we have 10 contact records or 10 million the lookup time will be almost the same (provided the number of Bob’s that exists doesn’t scale up with the number of records) - at the cost of slightly more RAM.

Syntax¶

CREATE NOTIFICATION <notification_name>
ON <table_name>
[ WHERE <where_clause> ]

notification_name

The name of the notification must be unique and non-existent. It follows the same rules as naming an entity like a table.

table_name

The table to watch for changes.

where_clause

Any expression which will cause the notification to fire only if it evaluates to true:

CREATE NOTIFICATION bobs
ON people
WHERE first_name = 'Bob';

Notes¶

Multiple notifications can be fired for a single insert but is limited to one notification per NOTIFICATION defined.

Notification use the Redis PUB/SUB model, so when the actual notification is fired it is sent as a publish through Redis. Your notification name is the channel that the message will be published to.

This means that software does not need to know anything about the tesseract server if they only intent to subscribe. Published notifications are the complete JSON record.

Syntax¶

DELETE FROM <table_name>
[ WHERE <where_clause> ]

table_name

The table name to remove the objects from. If the table does not exist then the statement will have no effect.

where_clause

An expression that determines which records should be removed. If the WHERE clause is omitted all records will be deleted.

Syntax¶

DROP INDEX <index_name>

index_name

The name of the index must already exist. Index names must be globally unique - that is different tables cannot have a index with the same name.

Syntax¶

DROP NOTIFICATION <notification_name>

notification_name

The name of the notification must already exist or an error it returned.

Syntax¶

DROP TABLE <table_name>

table_name

The name of table. If the table does not exist this command will effectively do nothing.

Syntax¶

INSERT INTO <table_name>
<json_object>

table_name

The table name to insert the object into. This table will be created by simply adding an object into it if it has never been inserted into.

json_object

A JSON object.

Examples¶

INSERT INTO people
{ "first_name": "John", "last_name": "Smith" }

Syntax¶

ROLLBACK [ WORK | TRANSACTION ]

WORK

Optional keyword. It has no effect.

TRANSACTION

Optional keyword. It has no effect.

Overview¶

ROLLBACK rolls back the current transaction and causes all the updates made by the transaction to be discarded.

Use COMMIT to successfully terminate a transaction.

Issuing ROLLBACK when not inside a transaction does no harm, but it will provoke a warning message.

Compatibility¶

The SQL standard only specifies the two forms ROLLBACK and ROLLBACK WORK.

See Also¶

• Transactions

Syntax¶

[ EXPLAIN ]
SELECT <column_definitions>
[ FROM <table_name> ]
[ WHERE <condition> ]
[ GROUP BY <group_field> ]
[ ORDER BY <order_field> ]
[ LIMIT <limit> ]

EXPLAIN

If the EXPLAIN keyword is present the SQL query is not actually run - but rather the query plan (the internal steps required to carry out the task) are returned. This is very useful for debugging slow queries and otherwise examining what tesseract is doing with a particular query.

column_definitions

This can be an asterisk (*) to represent that the entire object should be retrieved without modification or a list of expressions.

table_name

The table name to fetch the objects from. If the table does not exist (i.e. has no records) then no records will be returned since tables only come into existence when they have objects added to them.

You may omit the table just to parse expressions that do no need a table like:

SELECT 3 + 4

condition

A filter expression.

group_field

Rows will be collapsed into groups of distinct values of this field.

order_field

This can be any field you wish to sort by. If field does not exist in a given record it will be sorted as if the value were null.

There are two modifiers for a column; ASC and DESC which represent **asc**ending and **desc**ending respectively. If no sort order is specified then ASC is assumed.

When data is sorted it is separated into four types:

• Booleans. Explicit true and false.
• Numbers. Integers and floating point, not including strings that look like numbers like "123".
• Strings.
• Nulls.

Data will sorted by type, then value in the same order as above. That is to say:

• Any number is considered greater than a boolean.
• Any string is considered greater than a number.
• null is considered to be greater than any non-null value.

limit

Limit the amount of records to be returned. This is performed after all operations on the sets are finished (such as ordering and grouping).

If you specify a LIMIT higher than the total number of records it will be the same as not specifying the limit at all (all records). Alternatively you can use LIMIT ALL to return all records.

You may also skip rows before the limit with OFFSET:

SELECT * FROM bla LIMIT 20 OFFSET 10

This will skip the first 10 rows and return a maximum of 20 rows - the limit is exclusive of the offset.

LIMIT is optional like OFFSET:

SELECT * FROM bla OFFSET 10

If the offset is larger than the available rows then no rows will be returned.

It is important to note that all the rows up to the ``LIMIT`` + the
``OFFSET`` must be calculated internally so using a large ``OFFSET`` can be
expensive. In some cases all records of the entire set must be calculated
before the limit can be applied - such as when there is an ``ORDER BY`` or
``GROUP BY`` clauses.

Syntax¶

START TRANSACTION
BEGIN [ WORK | TRANSACTION ]

Overview¶

START TRANSACTION or the alias BEGIN starts a transaction.

Compatibility¶

In the standard, it is not necessary to issue START TRANSACTION to start a transaction block: any SQL command implicitly begins a block. Tesseract’s behavior can be seen as implicitly issuing a COMMIT after each command that does not follow START TRANSACTION (or BEGIN), and it is therefore often called “autocommit”.

Syntax¶

UPDATE <table_name>
SET <column_expressions>
[ WHERE <condition> ]

table_name

The table that will have its object modified. If the table does not exist (i.e. has no records) then no records will be updated since tables only come into existence when they have objects added to them.

column_expressions

<column_name1> = <expression1>, ...

A list of columns to be updated by an expression.

name = “Bob”, something_else = 3 + 5 * 2

You may use any value of the record in the expression as well:

counter = counter + 1, old_value = counter - 1

condition

A filter expression. If no WHERE clause is provided then all objects in the table will be updated.

Arithmetic¶

Addition:

a + b

Subtraction:

a - b

Multiplication:

a * b

Division:

a / b

Modulo (remainder):

a % b

Power:

a ^ b

a and b must be null or number.

Equality¶

Equality:

a = b

For inequality:

a != b
a <> b

a and b must be the same type, and accepted types are:

• null
• number
• string
• array
• object

In the case of an array both arrays must be the same length and contain exactly the same element in the same order:

SELECT [1, 2] = [1, 2]

true

SELECT [1, 2] = [1, "2"]

false

SELECT [1, 2] = [2, 1]

false

In the case of an object both objects must contain the same amount of keys and each key must exist in both objects with the exact same value.

SELECT {"foo": 123} = {"foo": 123}

true

SELECT {"foo": 123} = {"foo": 123, "bar": null}

false

Inequality has all the same rules but in reverse.

Greater or Less Than¶

Greater than:

a > b

Greater than or equal to:

a >= b

Less than:

a < b

Less than or equal to:

a <= b

a and b must be the same type, and accepted types are:

• null
• number
• string

When comparing strings it follows the same rules as how Lua compares strings.

Concatenation¶

Concatenation:

a || b

Will concatenate the string representations of both sides. For example 3 || 5 is 35. Special values will be converted as follows:

You cannot concatenate arrays or objects on either or both sides.

Logical¶

For all logical operations a and b are only allowed to be null or boolean.

Logical AND:

a AND b

Results:

Logical OR:

a OR b

Results:

Regular Expressions¶

Regular Expressions:

value LIKE regex
value NOT LIKE regex

value must be a string, but can be of any length.

regex uses the SQL rules for LIKE expressions.

SELECT "Bob Smith" LIKE "Bob %"

SELECT "Bob Smith" LIKE "% Smith"

Checking Types¶

The following can be used to test the types of a value:

value IS null
value IS true
value IS false
value IS boolean
value IS number
value IS string
value IS array
value IS object

Each of the combinations can be used with NOT like:

value IS NOT boolean

The case of the type (boolean) is not important and there is no specific convention on case.

Set Membership¶

To test the existence of a value in a set:

a IN (b1, b2, ...)
a NOT IN (b1, b2, ...)

Will return true if a exists in one of the b values. There must be at least one b value. Comparison of each element follows the same rules as the = operator.

If a is null or any of the b values are null then the result is null. This is to conform is the SQL standard in dealing with null values.

Containment¶

To test if a value sits between two other values (inclusive):

a BETWEEN b AND c
a NOT BETWEEN b AND c

Is exactly equivalent to:

a >= b AND a <= c
a < b OR a > c

If at least one of a, b or c is null then the result will always be null.

Operator Precedence¶

AVG() – Average¶

avg(<number>)

COUNT() – Count records¶

count(*)
count(<any>)

When the argument is * it will count all records without needing to provide any value.

MAX() – Maximum value¶

max(<number>)

MIN() – Minimum value¶

min(<number>)

SUM() – Total¶

sum(<number>)

ABS() – Absolute value¶

Return the absolute value (positive value) of a number.

abs(<number>)

CEIL() – Round up¶

Round up to the next whole number.

ceil(<number>)

COS() – Cosine¶

Calculates the cosine of an angle.

cos(<number>)

FLOOR() – Round down¶

Remove the fractional part of a number. This is often refered to as truncating a number.

floor(<number>)

SIN() – Sine¶

Calculates the sine of an angle.

sin(<number>)

SQRT() – Square root¶

Calculates the square root of a number.

sqrt(<number>)

If the number is negative an error will be thrown:

Cannot calculate square root with negative number -17

TAN() – Tangent¶

Calculates the tangent of an angle.

tan(<number>)

BIT_LENGTH() – Bit Length¶

Return the bit length of a string.

bit_length(<string>)

CHAR_LENGTH() – Character Length¶

Return the character length of a string.

char_length(<string>)

OCTET_LENGTH() – Octet Length¶

Return the octet length of a string.

octet_length(<string>)

Extract Expressions¶

Gather up all the aggregate columns in the query and make their expressions unique, for example:

SELECT value, sum(a), sum(a + 2), sum(a) * 2
FROM some_table

The unique aggregates would be (in no particular order):

sum(a)
sum(a + 2)

It is important they are unique because we don’t want to calculate them twice. Even if we didn’t care about the performance hit it is still critical they are unique because these expressions become the key as we aggregate and so multiple expressions would cause all values to be run twice through all the aggregate functions.

Each unique expression becomes a key and is rendered so sum(x + 1) is the same as sum(x+1). However, sum(x + 1) and sum(1 + x) are not the same thing.

Grouping¶

As we iterate the records we maintain a Redis hash called group which contains keys that represent JSON strings and a value of 1.

Since a query does not need to have a GROUP BY clause this effectively means that all the rows in the set belong to the same group. So we give this master group a true value to group on.

In any case the value is encoded to JSON - this ensures the value is both a string and unique across values and equivalent values in different data types.

The following records (grouped by a):

{"a": 123}
{"a": true}
{"a": "123"}
{"a": 123}

Would be:

"true"
"123"
"\"123\""

A query could contain multiple aggregate expressions and we want to keep them independent. Our two possible solutions is to maintain a different hash for each expression, or use a single hash but have a prefix/suffix to the keys. I chose to use the latter separated by a colon. The hash in Redis will look like this:

The value is an integer that is incremented with each unique key. The number 123 appears twice.

Lua Processing¶

There are two Lua functions required to produce the final result. Lets take AVG() as an example. The two Lua functions would be:

function_avg(group, value)
function_avg_post(unique_group, group)

function_avg is run with each value as it’s encountered. This is an opportunity to track values that may be needed for post processing:

function_avg('count(*)', 123)
function_avg('count(*)', true)
function_avg('count(*)', "123")
function_avg('count(*)', 123)

Once the grouping is complete we use a post processing Lua function to calculate the final result:

function_avg_post('agg_key', 'count(*)')

The first argument is the Redis key that contains the original grouping hash so you can lookup the original count if you need to.

Ensure Single Row¶

If there is no GROUP BY clause we must return one row, even if the original set did not have any rows. At the moment the default value will always be 0, except in the case of MIN() and MAX() which is hard-coded to return null.

Overview¶

A transaction is a block of work that will not be visible until a COMMIT has been issued. Or alternatively a ROLLBACK issued will completely undo any changes for the entire block.

Tesseract uses a MVCC (Multiversion Concurrency Control) methodology for record visibility. There is a lot of information on how MVCC works so for the purpose of the following example I will cover the important bits.

Most people that have used a database before expect “autocommit” where any statement that is not explicitly in a transaction is automatically committed. This is different to what the SQL standard states where START TRANSACTION is implicit but the COMMIT is not.

Internals¶

Following on, rather than surrounding every non-explicit transaction with a START TRANSACTION and COMMIT we define two states; in or not in a transaction.

Each connection maintains its current transaction ID. The TransactionManager maintains a set of all the transaction ID for all the connections that are explicitly in a transaction. Another way to look at it is when we say “in a transaction” we mean the current connection’s transaction ID is in this set.

Each record has two special properties (amongst others) - prefixed with a colon:

• xid: The transaction ID when the record was created.
• xex: The transaction ID when the record was deleted. This will be 0 initially.

A record is visible only when all the following are true:

1. The xid is not in the active transactions.
2. The xex is 0 OR xex is not in the active transactions.

Collisions¶

One implicit limitation of transactions is that a connection must see the same database state, no matter how long it has been running or how many modifications to the databases have been made. But at the same time it must guarantee that multiple transactions are editing the most recent version of rows (updating an expired row would have the changes lost).

At the moment tesseract handles collisions by simply throwing the error:

Transaction failed. Will ROLLBACK.

For the transaction that does not hold the lock for the row. This is crude solution as we should wait for that lock to be released but that’s an improvement for another day.

Deadlocks¶

A deadlock should be impossible given that a transaction will immediately fail and ROLLBACK. When we have a scheduler for transactions then this will be a legitimate concern.

Connect¶

There is currently no authentication required for connecting. So simply knowing the host and port is sufficient for making a connection.

It is a plain TCP connection that normally runs on port 3679.

Request¶

The request is a JSON object:

{
  "sql": "SELECT 1 + 2"
}

Response¶

If the response is successful:

{
  "success": true,
  "data": [
    {
      "col1": 3
    }
  ]
}

If the response is an error:

{
  "success": false,
  "error": "Oh noes!"
}

Warnings are not considered a failure, so a prudent client should check to see if the warnings is present in the response:

{
  "success": true,
  "data": [
    {
      "col1": 3
    }
  ],
  "warnings": [
    "Take note..."
  ]
}

Basic¶

Always H1 for first level headings and always use an underlined heading rather than hashes:

Heading 1
=========

Heading 2
---------

Heading 3
^^^^^^^^^

Each heading of any strength should have two blank lines above it (unless it is the first line of the file.)

SQL Examples¶

SQL examples should be used in fenced blocks and not include the following semi-colon:

.. code-block:: sql

   SELECT foo FROM bar

SELECT foo FROM bar

If you would like to show the result of the SQL it must be in a separate block for JSON:

.. code-block:: json

   {"foo": "bar"}

{"foo": "bar"}

Syntax Descriptions¶

When describing SQL syntax use a SQL block:

.. code-block:: sql

   SELECT <some_columns>
   FROM <table>

SELECT <some_columns>
FROM <table>

For each of the placeholders use a definition style with a blank line between each definition:

some_columns
  Lots of nice description here.

table
  Even more wonderful information!

some_columns

Lots of nice description here.

table

Even more wonderful information!

Notes¶

Notes are meant to stand out from other text and contain important information.

.. highlights::

This is important information.

This is important information.

Tables¶

There are two types of table syntax that make the table as small as possible around the text or type to span as much space as possible. Always use the greater span syntax:

.. table::

   =========  =============
   full span  syntax
   =========  =============
   for        tables
   with       multiple rows
   =========  =============