.SQLITE File Format – The schema table

sqlite_schema

The schema table is stored as if it had been made by:

CREATE TABLE sqlite_schema(type text, name text, tbl_name text, rootpage integer, sql text);

The table can also be named sqlite_master, its older name, which works everywhere. In the TEMP database it is sqlite_temp_schema or sqlite_temp_master. The schema table itself has no row of its own.

The first row of the schema table in a database from the files examined for this site is decoded under Records: type table, name and table papers, root page 2, and its CREATE TABLE statement. That schema lists the tables papers, authors, authorspapers, tags, tagspapers, files and links, and four indexes named sqlite_autoindex_….

Tables

An ordinary table is a table b-tree, keyed by the 64-bit signed rowid. Each row is a table leaf cell: the rowid and a record of the columns in the order the table declares them.

A column declared INTEGER PRIMARY KEY, with the type written exactly as INTEGER, becomes another name for the rowid. Its value is the cell’s rowid, and the record stores NULL in its place.

A virtual table has no b-tree of its own: its rows come from the module that implements it, which may keep them in ordinary tables of its own.

WITHOUT ROWID tables

A table whose definition ends WITHOUT ROWID (SQLite 3.8.2 and later) has no rowid. It is stored in an index b-tree, whose entries are records of the PRIMARY KEY columns followed by the remaining columns. Its schema row has type table; only the SQL shows the difference.

Indexes

Every index, whether made with CREATE INDEX or by SQLite for a UNIQUE or PRIMARY KEY constraint, is an index b-tree. Each entry is a record of the indexed columns followed by the row’s key: the rowid, or for a WITHOUT ROWID table, its PRIMARY KEY columns. To find a row from an index, a reader takes the key from the end of the entry and looks it up in the table’s b-tree.

In a database of schema format 4, a column marked DESC in an index is stored in descending order.

Internal tables and indexes

Names that start with sqlite_ belong to SQLite.