Tableland understands a small subset of the standard SQL language. It does omit many features while at the same time adding a few features of its own. This document attempts to describe precisely what parts of the SQL language Tableland does and does not support. A list of supported data types is also provided. The SQL language supported by Tableland is a subset of the SQLite SQL language specification (and as such, we borrow heavily from their documentation with attribution), with additional constraints specific to Tableland operations.
This general SQL specification is broken down into two core sub-documents (which are embedded below). This specification is a living document, and as such, may be updated over time. Proposals for the addition of SQL language features and data types may be submitted by the Tableland community over time. These proposals will be evaluated for technical feasibility, utility to the community, and longer-term sustainability.
The core Tableland SQL parser accepts an SQL statement list which is a semicolon-separated list of statements. Each SQL statement in the statement list is an instance of one of the following specific statement types. All other standard SQL statement types are unavailable (at the moment). Each statement type is associated with a well-known SQL command (see following sections). In general, the entire Tableland SQL API can be summarized in seven command/statement types: CREATE TABLE, INSERT, UPDATE, DELETE, SELECT, GRANT, REVOKE.
<aside> ⚠️ The statement and data types provided here are part of the official minimal Tableland SQL specification. Additional functionality may be available in practice. However, it is not recommended that developers rely on SQL features outside of this minimal specification in the long-term.
</aside>
The CREATE TABLE command is used to create a new table on Tableland. A CREATE TABLE command specifies the following attributes of the new table:
table_name).column_name).data_type).PRIMARY KEY for the table. Both single column and composite (multiple column) primary keys are supported.UNIQUE, NOT NULL, CHECK and PRIMARY KEY constraints (see previous bullet).CREATE TABLE *table_name* ( [
{ *column_name* *data_type* [ *column_constraint* [, ... ] ]
| table_constraint }
[, ...]
] );
where column_constraint has structure
[ CONSTRAINT constraint_name ]
{ NOT NULL |
CHECK ( expression ) |
DEFAULT default_expr |
UNIQUE index_parameters |
PRIMARY KEY index_parameters |
}
and table_constraint has structure
[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) |
UNIQUE ( column_name [, ... ] ) |
PRIMARY KEY ( column_name [, ... ] )
Every CREATE TABLE statement must specify a fully-qualified table identifier (id) as the name of the new table. The fully-qualified table identifier has the following structure:
table_id = {prefix}_{chain_id}_{token_id}
Where the id prefix is optional, and may include any characters from the regular expression ([A-Za-z0-9\\_]+), but cannot start with a number. A prefix string may be up to 32 bytes in length. In practice, long names with spaces must be slug-ified with underscores. For example, “my amazing table" would become "my_amazing_table". The last two components of the table id, must be the chain id and the token id, which are numeric values separated by an underscore. For example, a valid table id without a prefix looks like _42_0, whereas a valid table id with a prefix might look like dogs_42_0.
<aside>
⚠️ It is not up to the caller to determine what token id to use in a CREATE TABLE statement. The token id is a monotonically-increasing numeric value which is provided by the smart contract that is processing the create statements. See the On-Chain API Specification for details on the smart contract calls used to generate CREATE TABLE statements in practice.
</aside>
Table identifiers must be globally unique. The combination of chain id and monotonically increasing token id ensures this is the case in practice. As such, the addition of a user-defined prefix string is an aesthetic feature that most developers will find useful (but is not required). The maximum (slug-ified) prefix length is 32 bytes.
<aside> ℹ️ Tableland also supports quoted identifiers (for table names, column names, etc). This allows callers to use SQL Keywords (see next section) as part of identifiers, etc. There are some limitations to this, and it does not circumvent any other naming constraints.
</aside>
The SQL standard specifies a large number of keywords which may not be used as the names of tables, indices, columns, databases, or any other named object. The list of keywords is often so long that few people can remember them all. For most SQL code, your safest bet is to never use any English language word as the name of a user-defined object.
If you want to use a keyword as a name, you need to quote it. There are four ways of quoting keywords in SQLite:
'keyword' — A keyword in single quotes is a string literal."keyword" — A keyword in double-quotes is an identifier.[keyword] — A keyword enclosed in square brackets is an identifier. This is not standard SQL, it is included in Tableland for compatibility.keyword — A keyword enclosed in grave accents (ASCII code 96) is an identifier. This is not standard SQL, it is included in Tableland for compatibility.The list below shows all possible reserved keywords used by Tableland (or SQLite). Any identifier that is not on the following element list is not considered a keyword to the SQL parser in Tableland:
ADD, ALL, ALTER, ALWAYS, AND, ANY, AS, ASC, AUTOINCREMENT, BETWEEN, BLOB, BY, CASE, CAST, CHECK, COLLATE, COMMIT, CONSTRAINT, CREATE, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, DEFAULT, DEFERRABLE, DELETE, DESC, DISTINCT, DROP, ELSE, END, ESCAPE, EXCEPT, EXISTS, FALSE, FILTER, FIRST, FOREIGN, FROM, GENERATED, GLOB, GRANT, GROUP, HAVING, IN, INDEX, INSERT, INT, INTEGER, INTERSECT, INTO, IS, ISNULL, JOIN, KEY, LAST, LIKE, LIMIT, MATCH, NONE, NOT, NOTHING, NOTNULL, NULL, NULLS, NUMERIC, OFFSET, ON, OR, ORDER, PRIMARY, REAL, REFERENCES, REGEXP, RETURNING, REVOKE, SELECT, SET, STORED, TABLE, TEXT, THEN, TO, TRANSACTION, TRUE, UNION, UNIQUE, UPDATE, USING, VALUES, VIRTUAL, WHEN, WHERE
<aside> ℹ️ You can also find the most up to date list of keywords used by Tableland in the reference parser implementation. See Implementation
</aside>
<aside>
⚠️ Table names that begin with sqlite, system or registry are also reserved for internal use. It is an error to attempt to create a table with a name that starts with these reserved names.
</aside>
Every CREATE TABLE statement includes one or more column definitions, optionally followed by a list of table constraints. Each column definition consists of the name of the column, followed by the declared type of the column (see Data Types), then one or more optional column constraints. Included in the definition of column constraints for the purposes of the previous statement is the DEFAULT clause, even though this is not really a constraint in the sense that it does not restrict the data that the table may contain. The other constraints, NOT NULL, CHECK, UNIQUE, and PRIMARY KEY constraints, impose restrictions on the table data.
<aside>
⚠️ The number of columns in a table is limited by the MaxColumns validator configuration parameters (defaults to 24). A single character fields in a table cannot store more than MaxTextLength bytes of data (defaults to 1024). The number of rows in a table is limited by the MaxRowCount validator configuration parameter (defaults to 100,000). This values are all configurable at the network-level, and may change in the future.
</aside>
<aside>
⚠️ In practice, a CREATE TABLE statement must be sent as a single top-level statement (i.e., it must be provided in a statement list of length one).
</aside>
<aside>
🚧 Feature At Risk: FOREIGN KEY constraints of the form FOREIGN KEY(column_name) REFERENCES table_id(column_name) are currently not supported across Tableland tables. Instead, dynamic JOINs can be used to reference columns in remote tables. However, inclusion of FOREIGN KEY constraints are being considered for inclusion in the Tableland SQL specification with some specific limitations. In particular, key constraint actions would be restricted to SET NULL or SET DEFAULT (see the section called SQLite foreign key constraint actions at the link below).
See SQLite Foreign Key
</aside>
The DEFAULT clause specifies a default value to use for the column if no value is explicitly provided by the user when doing an INSERT. If there is no explicit DEFAULT clause attached to a column definition, then the default value of the column is NULL. An explicit DEFAULT clause may specify that the default value is NULL, a string constant, a blob constant, a signed-number, or any constant expression enclosed in parentheses. For the purposes of the DEFAULT clause, an expression is considered constant if it contains no sub-queries, column, or table references, or string literals enclosed in double-quotes instead of single-quotes.
Each time a row is inserted into the table by an INSERT statement that does not provide explicit values for all table columns the values stored in the new row are determined by their default values, as follows:
NULL, text, blob or signed-number value, then that value is used directly in the new row.A column that includes a GENERATED ALWAYS AS clause is a generated column:
CREATE TABLE table_id (
...,
column_name data_type { GENERATED ALWAYS } AS (*expression*) { STORED | VIRTUAL }
);
Generated columns (also sometimes called "computed columns") are columns of a table whose values are a function of other columns in the same row. Generated columns can be read, but their values can not be directly written. The only way to change the value of a generated column is to modify the values of the other columns used to calculate the generated column.
The GENERATED ALWAYS keywords at the beginning of the constraint and the VIRTUAL or STORED keyword at the end are all optional. Only the AS keyword and the parenthesized expression are required. If the trailing VIRTUAL or STORED keyword is omitted, then VIRTUAL is the default.
The value of a VIRTUAL column is computed when read, whereas the value of a STORED column is computed when the row is written. STORED columns take up space in the database file, whereas VIRTUAL columns use more CPU cycles when being read.
Features and Limitations
NOT NULL, CHECK, and UNIQUE constraints, just like ordinary columns.DEFAULT clause. The value of a generated column is always the value specified by the expression that follows the AS keyword.PRIMARY KEY.GENERATED ALWAYS AS expression has no affect on the data type of the column data itself.Each table in Tableland may have at most one PRIMARY KEY. If the keywords PRIMARY KEY are added to a column definition, then the primary key for the table consists of that single column. Or, if a PRIMARY KEY clause is specified as a separate table constraint, then the primary key of the table consists of the list of columns specified as part of the PRIMARY KEY clause. The PRIMARY KEY clause must contain only column names. An error is raised if more than one PRIMARY KEY clause appears in a CREATE TABLE statement. The PRIMARY KEY is optional.
Each row in a table with a primary key must have a unique combination of values in its primary key columns. If an INSERT or UPDATE statement attempts to modify the table content so that two or more rows have identical primary key values, that is a constraint violation. Related, the SQL standard is that a PRIMARY KEY should always be NOT NULL, so Tableland enforces this constraint.
<aside>
🚧 Feature At Risk: It is not currently possible to ALTER TABLE after it has been created. As such, table structure in Tableland is considered immutable. The Tableland core development team is currently evaluating whether to allow ALTER TABLE only in the case of adding new columns.
</aside>
The DELETE command removes records from the table identified by the table id.
DELETE FROM table_id [ WHERE condition ]
If the WHERE clause is not present, all records in the table are deleted. If a WHERE clause is supplied, then only those rows for which the WHERE clause boolean expression is true are deleted. Rows for which the expression is false or NULL are retained.
The INSERT command creates new rows in a table identified by the table id.