SQL: DDL

#Problem statement

SQLite utilizes sqlite_master TABLE to maintain a persistent list of tables, indices, views and triggers. In our fork, SQL tables and indices are backed by Tarantool spaces and indices, respectively. Since Tarantool is already capable of tracking its own spaces and indices, sqlite_master looks largely redundant.

Since sqlite_master is to be removed, it's necessary to redesign the schema handling.

In the following sections, we provide information on Tarantool spaces and indices. Then we document the original SQLite schema design. We conclude with the new design proposal.

Spaces and Indices in Tarantool

Tarantool spaces are listed in _space system space. Indices are listed in _index.

_Space Trivia

CREATE TABLE _space (
  id INT PRIMARY KEY,
  owner INT,
  name TEXT,
  engine TEXT,
  field_count INT,
  opts,
  format
)

• id — spaces are identified by the numeric id;
• owner — owner id;
• name — space name;
• engine — vynil or memtx;
• field_count — number of fields in a tuple, enforced, 0 — variable length tuples;
• opts — ex: {"temporary": true};
• format — description of tuple fields, not enforced, ex: [{"name": "column1", "type": "number"}, ...].

_Index Trivia

CREATE TABLE _index (
  id INT,
  iid INT,
  name TEXT,
  type TEXT,
  opts,
  parts,
  PRIMARY KEY (id, iid)
)

• id — owning space id;
• iid — numeric id, indices are identified by (id, iid) tuple;
• name — index name;
• type — ex: tree;
• opts — ex: {"unique": true};
• parts — index key description: column numbers and types, ex: [[0, "scalar"], [1, "scalar"]].

A space may lack indexes. In order for the space to be capable of storing data at least one index is required. An index with iid 0 is the primary index (the name doesn't matter). Creating secondary index before the primary is not permitted. Names and iid-s scope is the owning space. Unlike SQL, two indexes with the same name may coexist provided that they belong to different spaces.

Triggers in Tarantool Spaces

Tarantool takes holistic approach to data management — _space and _index are regular spaces. They are persisted and replicated in exactly the same way regular spaces are. Every space has a list of triggers attached. Triggers fire when a tuple is inserted, removed or updated. When an entry in _space or _index is modified, triggers fire. Consider for instance the following tuple inserted in _space:

[1000, 1, "test", "memtx", 0, {}, []].

The system trigger creates a new space object and initialises it with the provided options. The object is added to internal hash table. Now it is possible to attempt storing and retrieving data from space 1000 (though these attempts will ultimately fail since the space lacks indices yet).

Transactions and _space

There's one burning issue: _space and _index don't support multi-statement transactions. It's only possible to modify these spaces in auto-commit mode (which is a severe limitation we really hope to be removed soon).

Schema Handling in Unpatched SQLite

New Design Proposal