Data tables: module tables as part of the system (6.0.15) #

Read this page if you write or maintain an extension with tables of its own (a persistent class, a module over it), if you want to keep such a table in another database than the installation's, or if you administer the databases an installation talks to. The passwords of those databases live in the secret store.

In short #

The catalogue #

expDataTableCatalogue lists every persistent class of the installation:

1. It reads the class lists of the autoloader, the kernel's (autoload/ezp_kernel.php) and the extensions' (var/autoload/ezp_extension.php). Test classes and namespaced classes are left out.
2. It reads each file as text and keeps only the files that declare a definition() method and a class that extends another. No file is loaded to find that out.
3. It loads the remaining classes and keeps those that are subclasses of eZPersistentObject, are not abstract, declare definition() themselves (a class that inherits its parent's definition is the parent's table, listed once) and whose definition names a table and fields.
4. A class whose definition() fails is listed with the error message instead of breaking the page.

Each entry has the class, the table, the file, its origin (kernel or the extension's name), the fields (type, default, required, short name, foreign class and attribute), the keys, the increment key, the default sort, the function attributes, the connection it uses (empty for the installation's database) and its relations.

The catalogue is kept in the cache directory (<cache>/datatable/catalogue.json, the directory name is [DataTableSettings] CatalogueCacheDir) and made again when one of the autoload arrays or settings/datatable.ini changes. Make the catalogue again on the Data tables page (policy datatable/manage) makes it at once. [DataTableSettings] IgnoreClasses[] leaves classes out, for example one whose definition() needs a request.

$catalogue = expDataTableCatalogue::instance();
$catalogue->classes();                    // class => entry
$catalogue->entry( 'eZViewCounter' );     // one entry, or null
$catalogue->byTable( 'ezview_counter' );  // the classes of a table
$catalogue->byOrigin();                   // kernel first, then each extension
$catalogue->pointingAt( 'node' );         // classes with a column that holds a node id

Relations #

A relation says that a column holds the id of something else: a content object, a node, a content class, a user, a section, or a row of another table. They are taken from, in this order (the first one for a column wins):

The kind follows from the target class: eZContentObject is an object, eZContentObjectTreeNode a node, eZContentClass a class, eZUser a user, eZSection a section, any other class a table. A table's own counted-up key is never taken for a relation. [DataTableSettings] InferRelations=disabled switches the guess by name off.

[Relation_myArchiveRow_contentobject_id]
Kind=object
Class=eZContentObject
Field=id

The relations key was already part of the definition format (eZViewCounter and eZTipafriendCounter use it), and eZPersistentObject::storeObject() still reads it as before.

Describe: the table as its database has it #

The page of a class (Data tables, then the class) shows its definition next to its table: expDataTableDescriber::describe( $entry ) asks the table's own connection

• whether the table exists, from the engine's catalogue (information_schema on MySQL, PostgreSQL and SQL Server, sqlite_master and PRAGMA table_info on SQLite, user_tables and user_tab_columns on Oracle, listCollectionNames() on MongoDB). Existence is never inferred from a query result: MongoDB answers an empty list for a collection that does not exist, and MySQL raises;
• its columns with type, whether they may be empty, and their default. On MongoDB the fields of a sample of documents ([DataTableSettings] MongoSampleSize, 50);
• the number of rows;
• where table and definition differ:

A field with a short name is matched by it when the long name has no column. The page also shows the relations and the first 20 rows; columns whose names say they hold a secret (password, hash, token, secret, salt, ciphertext, private_key, api_key, key) are not shown.

Connections #

A connection is a database the installation can use besides its own. There are three kinds, listed together on Setup > Data tables > Connections:

A declared connection cannot be overridden by a row of the same identifier. An identifier is lower case letters, digits and _, starting with a letter, at most 64 characters; site is reserved.

[Connection_archive]
Name=Archive database
Engine=mysql
Server=db2.example.com
Port=3306
Database=archive
User=archive
Secret=env:ARCHIVE_DB_PASSWORD
Charset=utf8mb4
ReadOnly=false

Further keys of a declared connection: Socket, DSN (a PDO data source name), Dialect, Driver, Description.

Engines #

An engine the kernel has no driver for goes through expPDODB, a generic database driver over PDO: queries, limit and offset in the engine's dialect, transactions, the last inserted id, escaping, the table list. So every connection is a database object of the same kind as the installation's own, whatever its engine. [ConnectionSettings] Engines[] limits the engines that may be chosen; the Connections page lists each engine with whether this server can use it and, when not, why (the missing PHP extension or driver).

[ConnectionSettings] AllowedHosts[] limits the servers a connection may name (empty, or *: any). [ConnectionSettings] ConnectTimeout (5 seconds, at most 60) is how long a connection waits for its server.

The password is a secret's name #

A connection never holds a password. Its Secret (the "Password: the name of a secret" field) names a secret of the secret store: a secret stored there, encrypted, or env:VARIABLE for an environment variable of the PHP process. On the connection's page a password can be typed in for a test only; with Store it in the secret store when saving (policy secrets/manage too) it is stored, encrypted, under the name given, or under datatable.<identifier> when no name was given, and the connection keeps that name. A DSN that holds a password is refused.

Test connection #

Test on the list and Test connection on a connection's page open the connection, ask the server's version and its tables, and close it again. The result says the driver (ez for the kernel's own, pdo), the time in milliseconds, the version and the number of tables, or the error. A server that does not answer on its port within the timeout is reported as such before the driver is asked (a driver can wait a minute otherwise). Every message is masked: the value of the connection's secret, and of a password typed in for the test, never appears.

$result = expDataConnections::test( 'archive' );
// ok, engine, via, version, tables, ms, error

Using a connection in code #

$db = expDataConnections::db( 'archive' );     // the database of the connection, an eZDBInterface
$rows = $db->arrayQuery( 'SELECT id, title FROM archive_item', array( 'limit' => 10 ) );

db() opens each connection once per request and keeps it for the rest of it; db( 'site' ) and db( '' ) give the installation's database. A connection that does not exist or cannot be opened throws a RuntimeException whose message names the connection and never holds a secret's value.

On Exponential Velocity a worker serves one request after the other: the connections of a request are closed when the next request begins, so a worker never keeps a socket from one request to the next, nor one its parent opened before it was forked.

Persistent classes on a connection #

A persistent class names its connection in its definition:

public static function definition()
{
    return array(
        'fields' => array( ... ),
        'keys' => array( 'id' ),
        'increment_key' => 'id',
        'class_name' => 'myArchiveItem',
        'name' => 'archive_item',
        'connection' => 'archive',
    );
}

or the settings name it for a class that does not (an extension's class, without editing it):

[ClassSettings]
Connection[myArchiveItem]=archive

eZPersistentObject then reads and writes the class's rows there: fetchObject(), fetchObjectList(), count(), store(), remove(), removeObject(), updateObjectList(), newObjectOrder() and reorderObject() all ask eZPersistentObject::dbFor( $definition ). Everything the call builds is escaped for that connection, not for the installation's database (MySQL and SQLite escape a quote differently): the conditions, the arrays of values, the reordering and the short field names. A class of the installation's database read in the middle of such a call (a function attribute, a relation) goes back to the installation's database for that read.

A class without a connection costs two isset() checks and one lookup more than before; nothing else changes for it.

The settings of [ClassSettings] are read once per process. On Velocity a change of them reaches the workers with the next restart.

Schema: creating and upgrading tables on a connection #

expDataTableSchema puts a schema on a connection. The schema comes from:

• a persistent class's definition() (expDataTableSchema::fromDefinition()): integer becomes int(11), float a float, text a long text, anything else a varchar(255) (other lengths through the second argument); the counted-up key is an auto-increment column, the keys are the primary key, each relation column gets an index (the content view asks "which rows point at this item"), and the optional 'unique' => array( '<index>' => array( columns ) ) of the definition gives unique indexes. Index names are at most 30 characters (Oracle's limit);
• an extension's share/db_schema.dba (expKernelTableSchema::read()).

plan() says, table by table, what applying the schema would do, with the SQL, and runs nothing:

Dropping a column or an index the schema does not have, and changing a column or an index that differs, are held: listed, not done, unless asked ('drop' => true, 'alter' => true; the two boxes of the page). apply() runs the plan and stops a table at its first failing statement.

$schema = expDataTableSchema::fromDefinition( myArchiveRow::definition() );
$db = expDataConnections::db( 'archive' );
$plan = expDataTableSchema::plan( $schema, $db );             // action, statements, held, notes per table
$result = expDataTableSchema::apply( $schema, $db );          // ok, tables => action, ok, done, errors
$sql = expDataTableSchema::installSql( $schema );             // engine => SQL, for sql/<engine>/schema.sql

The page Setup > Data tables > Schema (datatable/schema, policy datatable/manage) takes a class (of an extension) or an extension's schema file, and a connection (the class's own by default; a read-only connection cannot be chosen). Show what would be done lists the plan with its SQL; Apply runs it and reports each table. The install SQL of every engine (MySQL, PostgreSQL, SQLite, Oracle, SQL Server) is shown below, to copy into an extension's sql/<engine>/schema.sql. A class's page links to it as Create the table or Upgrade the table.

Rows #

datatable/rows/<class> (policy datatable/read) lists the rows of a class's table, 25 a page, on the class's connection. /(<field>)/<value> keeps the rows where one field has one value (the content view's tab links there). A value of a relation column links to what it points at: the node's or the object's page, the class, the section, or the rows of the other table. Columns whose names say they hold a secret (password, hash, token, secret, salt, ciphertext, private_key, api_key, key) are left out, here and in the first rows of a class's page. The page only reads.

Relations in the content view #

The node view of the administration has a tab Data tables (admininterface.ini [AdditionalTab_datatable]) for a user with datatable/read, shown only when some row points at the node or its object. It lists, per table and column, how many rows point there and the first ten, with a link to all of them (expDataTableRelation::pointingAt(), the fetch function pointing_at).

Only the tables of extensions and of named connections are asked: the kernel's own tables relate to content everywhere, and a class of an extension over a kernel table is left out too. Only declared relations are asked (the definition's relations, a field's foreign_class, a [Relation_*] block), one count per relation and node view; datatable.ini [DataTableSettings] ContentTabInferred=enabled also asks the relations inferred from column names.

expDataTableRelation::target( $relation, $value ) gives what a value points at (an eZContentObject, eZContentObjectTreeNode, eZContentClass, eZUser, eZSection, or the row of the other table) and url() its address in the administration.

Writing rows #

Every tool that puts rows into a data table (an import, the synchronisation, a module of its own) goes through expDataTableWriter, never raw SQL: it checks the rows against the class's definition and writes on the class's connection.

$writer = new expDataTableWriter( 'myArchiveRow', array( 'keys' => array( 'remote_id' ), 'mode' => 'upsert' ) );
$result = $writer->write( array(
    array( 'remote_id' => 'a-1', 'title' => 'First', 'node_id' => '@node:4f9c2e…' ),
    array( 'remote_id' => 'a-2', 'title' => 'Second', 'contentobject_id' => '@path:news/second' ),
) );
// inserted, updated, unchanged, skipped, failed, errors (list), ids (row number => key)

Values are typed by the definition (integer, float; a date in an integer field becomes a timestamp; an array in a text field becomes JSON). A row that does not change anything is counted as unchanged and not written. A relation column may hold a reference instead of an id: @object:<remote id>, @node:<remote id> or @path:<url alias>; a reference nothing has fails the row. Each chunk is one transaction; when the database refuses a row, the chunk is rolled back and written again row by row, each in a transaction of its own, so that bad row fails alone and the others are written. An error names the row's number and the field, never a value.

On SQLite, eZPersistentObject writes a new row with INSERT OR REPLACE: a row whose unique column another row has already replaces that row instead of failing (MySQL and PostgreSQL refuse it). Match by the unique column (keys) to update the existing row instead.

A class the catalogue does not list yet (made after the catalogue was built) is read from its own definition.

Importing rows with 7x SQLI Import #

The extension 7x SQLI Import (2.0) fills a data table from any of its formats (a CSV file, a spreadsheet, a feed, an API, another database), with the same editor, dry run, schedules and run pages as a content import. Its rows go through expDataTableWriter, as above. The group Data tables of its Catalogue (sqliimport/catalogue) has the three ways in:

Import into a data table. Example: three books from a CSV file into the table datatabledemo_book of the demo module datatabledemo (class datatableDemoBook):

title,isbn,price,in_print,summary
The test garden (second edition),SQ688-0001,12.50,1,An invented book for the check
Tea for testers,SQ688-0002,9.90,1,Another invented book
Old test notes,SQ688-0003,4.00,0,Out of print

1. Imports > New import, source CSV and delimited text, upload the file.
2. Step Processing: choose Rows of a data table (a persistent class), the class datatableDemoBook, the mode upsert (add new rows, update existing ones) and the key isbn. Leave columns empty: every field whose name is a column is written. To rename or convert, write one line per column, such as price = price | number.
3. Dry run: 3 would be created, nothing is written. Save and run: 3 created.
4. Change one title in the file and run again: 1 updated, 2 unchanged.

The rows are then in the table, here on the module's own list page (datatabledemo/list) and in Setup > Data tables, datatable/rows/datatableDemoBook:

A relation column (object, node, user) takes a reference instead of an id: @object:<remote id>, @node:<remote id> or @path:<url alias>.

A new table from an import. On the Imports dashboard, More > New data table from its fields (policy setup/setup) opens the module table wizard with the source sqliimport and the import's id. The wizard guesses each column from a sample of the source (name, type, length, required, unique); Copy its fields into the list turns them into the editable list, where you correct the guesses before the wizard writes the class, the table and its pages:

Content and tables in sync. The third card opens a synchronisation with the data table and the content class filled in. Imports copies nothing itself here; the kernel runs the synchronisation.

The extension's own page on all of this, with more examples: /exphelp/viewer/ext/sqliimport/datatables.md. How imports work in general: 7x SQLI Import: start here.

In templates #

The module datatable has five fetch functions (kernel/datatable/function_definition.php, expDataTableFunctionCollection):

{def $rows = fetch( 'datatable', 'list', hash( 'class', 'myArchiveRow',
                                             'conditions', hash( 'state', 'open' ),
                                             'sort', array( 'created', false() ),
                                             'offset', 0, 'limit', 20 ) )}
{fetch( 'datatable', 'count', hash( 'class', 'myArchiveRow', 'conditions', hash( 'state', 'open' ) ) )}
{def $row = fetch( 'datatable', 'row', hash( 'class', 'myArchiveRow', 'key', hash( 'id', 42 ) ) )}
{def $found = fetch( 'datatable', 'pointing_at', hash( 'object_id', $node.contentobject_id, 'node_id', $node.node_id ) )}
{if fetch( 'datatable', 'can', hash( 'function', 'read' ) )}…{/if}

list, count and row read the classes of extensions and of named connections for anyone who may see the template; a class of the kernel's own database only for a user with datatable/read. Conditions are equality on fields of the class (other keys are ignored), sort is a field and ascending (true()) or descending, limit is between 1 and 500. row needs every key it is given to be a field. pointing_at needs datatable/read; can answers for read, manage, connections and sync.

In the registry #

The data tables are a tool of the transfer registry (transfer-registry.md): [Service_transfer.datatable] in kernelservices.ini, named Data tables, direction both, scopes data, class and subtree, entry page datatable/dashboard (policy datatable/read), the secrets module belonging to it, two Setup entries ("Create or upgrade a table on a database connection…" with datatable/manage, "Browse the tables of the persistent classes…"), and 7x SQLI Import as its round-trip partner, so each appears in the other's Looking for…? box.

The administration #

Only the Administrator role has them in a new installation. The Setup menu has a group Data tables with Data tables, Connections, Secrets and Synchronisations.

The kernel tables #

A new installation has them from the kernel schema. An installation made before 6.0.15 gets them from the update files of MySQL, PostgreSQL and SQLite (update/database/<engine>/6.0/dbupdate-6.0.0-6.0.15.sql, which create them only when they are missing), or from the administration: while one of them is missing the page says so, and Create the tables creates them on the installation's database through the engine's schema handler, on every engine the kernel supports, MongoDB included: the connections and the secret store on the Data tables page (policy datatable/manage), the three tables of the synchronisation on the Synchronisations page (policy datatable/sync). expKernelTableSchema does this for any table of a .dba schema:

expKernelTableSchema::exists( 'exp_secret' );          // true, false, or null when it cannot be told
expKernelTableSchema::install( array( 'exp_data_connection', 'exp_secret' ), null, null, $messages );
expKernelTableSchema::tables( $db );                   // every table of a connection, from its catalogue

Example: a class on its own SQLite file #

1. Setup > Data tables: if the page says the tables are missing, Create the tables.

2. Connections > New connection: identifier archive, engine SQLite, database var/datatable/archive.sqlite. Test connection reports the SQLite version and 0 tables; Save.

3. The extension's class names the connection in its definition ('connection' => 'archive'), or the settings do ([ClassSettings] Connection[myArchiveItem]=archive).

4. The table is created on that connection, for example from the extension's share/db_schema.dba:

   $db = expDataConnections::db( 'archive' );
   expKernelTableSchema::install( array( 'archive_item' ), $db, 'extension/myarchive/share/db_schema.dba' );

5. From then on the class works as any other:

   $item = new myArchiveItem( array( 'title' => "O'Brien's notes", 'node_id' => 42 ) );
   $item->store();
   $items = myArchiveItem::fetchObjectList( myArchiveItem::definition(), null, array( 'node_id' => 42 ) );

6. Data tables lists myArchiveItem with the connection archive; its page shows the table in the SQLite file, its rows, and node_id as a relation to a node.

The module table wizard #

Setup > RAD > Module table (setup/moduletable, policy setup/setup) makes a NEW data table and everything around it, as a new extension or added to an existing one. It is expModuleTableWizard (the page) and expModuleTableCode (the code it writes), beside the module extension wizard, which does the same for tables that exist already.

Where the fields come from #

The types are string (with a length, 255 by default), text, integer, float, boolean, date, datetime, json, and the relations to content object (a content object id), node (a node id) and user (a user's object id). Every table also gets id, a key the database counts up. The page can add contentobject_id and/or node_id to tie each row to content, and created and modified. Copy its fields into the list turns the fields of a class, a table or a source into the editable list.

title string(120) required
remote_id string(64) unique
price float
published date
article object
notes text

A link can open the wizard with a source already chosen: setup/moduletable/(source)/<source id>/(ref)/<reference>, or setup/moduletable/(class)/<class identifier> for a content class. This is how a catalogue card of 7x SQLI Import opens it for an import's fields.

An extension offers its own source as a kernel service tagged datatable.field_source, a class implementing expDataTableFieldSource: dataTableFields( array( 'ref' => ... ) ) (the fields as above: name, label, type, length, required, unique) and dataTableSourceLabel(); it may also have dataTableSourceName() (the name in the wizard's list of sources; the id when it has none) and dataTableReferences() (a list to choose from; none: the user types the reference). The id of the source is the service id without datatable.field_source..

[Service_datatable.field_source.myimport]
Class=myImportFieldSource
Scope=worker
Tags[]=datatable.field_source
Description=The fields of an import of my tool.

What it writes #

The page shows every file before anything is written. A table name starting with ez or exp_ is refused on the site's database; on a named connection the name is free.

A new extension or an existing one #

When the extension does not exist, Write the extension writes all files below extension/<name>. When it exists, Add to the extension adds what is missing and changes nothing that is there:

• a file the extension has already is left as it is and named on the page;
• settings/module.ini.append.php, design.ini.append.php and menu.ini.append.php get the wizard's block appended once (marked # module table wizard: <module>), inside the file's PHP wrapper;
• share/db_schema.dba gets the new table beside the tables it has;
• sql/<engine>/schema.sql gets the table's statements appended once.

Download as zip hands the same files over instead, for a server whose extension/ the web server cannot write.

Installing from the page #

No step needs the command line. The roles that should see or change the rows get <module>/read and <module>/edit.

The JSON endpoint #

<module>/json answers in JSON ({"data": ...}, or {"error": ..., "problems": [...]}) with the HTTP status:

Reading needs <module>/read (the view's policy), writing <module>/edit (403 without it). A row that cannot be stored is 422 with the problems of fromInput(). A write from a page of the site carries the form token like every POST (the header X-CSRF-Token).

Synchronisation #

A synchronisation keeps a data table and content of one class in step (Setup > Data tables > Synchronisations, datatable/syncs, policy datatable/sync). It names:

A source is an attribute identifier of the content class, or one of @object_id, @node_id, @parent_node_id, @remote_id, @node_remote_id, @name, @published, @modified, @class, @section, @owner and @path (the node's URL alias). An attribute is read with its datatype's toString() and written with fromString(); the @ sources are written to the table only, never to content. Propose the map fills the map with the columns named like an attribute (or attr_<identifier>, as the module table wizard names them) or like a known source (contentobject_id, node_id, remote_id, name, published, modified ...).

contentobject_id = @object_id
node_id = @node_id
remote_id = @remote_id
name = @name
modified = @modified
title = title
intro = intro

What a run does:

Values are compared as the table's fields type them: an empty relation is 0 in an integer column and '' as an attribute's string, and both mean the same. Only the attributes whose value differs are written to an object, so an image or a relation list is never written again from its own string. Content that a synchronisation writes is not mirrored back by the live mirror.

Every run goes through the transfer runner (the job queue, the content jobs or the request itself) and is recorded in the run history of the import and export tools (tool transfer.datatable); the page of a synchronisation lists its last runs. Run now (on the list) and Save and run now (on its page) start one. The cronjob part datatable (cronjobs/datatablesync.php, also in the frequent group) starts every enabled scheduled or live synchronisation whose interval has passed: php runcronjobs.php datatable.

$sync = expDataSync::define( array( 'name' => 'Articles to the archive', 'direction' => 'content_to_table', 'mode' => 'scheduled',
                                    'class' => 'myArchiveRow', 'content_class' => 'article', 'subtree' => 2,
                                    'map' => "contentobject_id = @object_id\ntitle = title", 'keys' => 'contentobject_id',
                                    'conflict' => 'newest', 'interval' => 3600 ), $problems );
$run = expDataSync::start( $sync );     // through the transfer runner; $run['engine'], $run['run_id']
$counts = expDataSync::run( $sync );    // the same work in this process

Live mirror #

The kernel notifies three events (ezpEvent), which any listener can take (site.ini [Event] Listeners[]):

For the live mirror the kernel calls expDataSyncListener itself, in every process (a web request, a command, a queue worker), at publish, move, location removal, purge and trash. The listener notes the objects and does the work at the end of the request, after the publish transaction, and only when a live synchronisation exists: each object is written to the tables of the live synchronisations of its class whose subtree holds it. An object that left the subtree, went to the trash, was purged or lost its last location loses its rows (unless the synchronisation keeps them). A failure is logged and never reaches the editor. A long process mirrors every 50 objects.

The workflow event Mirror to data tables (expdatatablemirror, on content publish after) does the same as a step of a workflow, for the synchronisations ticked in the event (or every synchronisation of the object's class when none is ticked), whatever their mode: for a site that wants the mirror after an approval, or before a notification. It never stops a workflow.

Conflicts #

A two-way run finds a conflict when both the content and the row changed since the last run. The conflict rule decides:

Setup > Data tables > Synchronisations > Conflicts (datatable/conflicts) lists the open conflicts with the values that differ, and Take the content or Take the table resolves one: the other side is written over, the pair remembers both sides, and the conflict is closed with who resolved it and how.

Tests #

In tests/tests/kernel/classes/datatable/ (testsuite kernel-classes, no installation needed): the catalogue, the description and its differences, the connections (values, checks, plans, data source names, testing on SQLite), persistent classes on a SQLite connection (store, fetch, update, remove, escaping), and the two kernel tables, which must be the same in share/db_schema.dba, the persistent classes, the three kernel schemas and the three update files. The schema (definitions to .dba, the SQL of every engine, plan and apply on SQLite: create, add a column, held drops), the writer (types, modes, dry run, a refused row failing alone, references, the class map), the relations and the fetch functions, and the registry entry with the content view's tab are tested there too, on a SQLite connection with a SQLite database in memory as the site's. The module table wizard is tested there as well: the field list and the field sources, the settings and their problems, every file it writes (every PHP file passes php -l, no template holds inline styles), the definition it makes, adding a table to an existing extension (files left alone, settings and schema files extended once), and the generated class storing, reading and checking rows on a SQLite connection. The synchronisation is tested there too: its definitions (values, the map and its text, the problems), the hashes, keys, conflict rules and typed values, the definitions, pairs and conflicts on a SQLite database (with the values that differ), the live mirror's listener (what it notes and when it flushes), the cronjob part, the workflow event type, and the kernel's calls of the listener; its three tables must be the same everywhere as the others. What reads or publishes content (a run in each direction, a conflict resolved, the live mirror after a publish and a removal) is tested in a throwaway installation. tests/tests/lib/ezdb/expPDODBTest.php tests the PDO driver on pdo_sqlite and the SQL of the other dialects.