.SQLITE File Format – The schema table

SQLite 2 database

An SQLite 2 database describes itself in one table, sqlite_master. Its rows name every table, index, view and trigger, give the CREATE statement for each, and give the root page of every table and index b-tree. A reader starts here to find everything else in the file.

The table

sqlite_master is an ordinary table b-tree whose root is always page 2. The page number is fixed in the source and not written anywhere in the file. The table is as if made by:

CREATE TABLE sqlite_master(type text, name text, tbl_name text, rootpage integer, sql text)
ColumnHolds
typetable, index, view or trigger
namethe object’s name
tbl_namethe table an index or trigger belongs to; a table’s or view’s own name
rootpagethe root page of a table or index b-tree, stored as text like every value; 0 for views
sqlthe CREATE statement as it was written

Indexes that SQLite makes for UNIQUE and PRIMARY KEY constraints are named (TABLE autoindex N), where TABLE is the table’s name and N counts from 1. Their sql is empty, probably NULL; this is not confirmed.

The TEMP database’s schema, sqlite_temp_master, lives in a separate temporary file, never in the main file.

Reading the schema

  1. Walk the b-tree from page 2 in key order (see leaf and interior pages).
  2. For each cell, read the data as a record of five values.
  3. For each row of type table or index, parse rootpage as a decimal number: that page is the root of its b-tree.
  4. For a table, the sql gives the column names in the order the records hold them.

Example

The schema row for create table t(a,b,c) with its root on page 3, the first row of sqlite_master. Built from the 2.8.17 layout. The key is the rowid key 80 00 00 01; the data, 40 bytes, is:

06 0C 0E 10 12 28 74 61 62 6C 65 00 74 00 74 00 33 00
63 72 65 61 74 65 20 74 61 62 6C 65 20 74 28 61 2C 62 2C 63 29 00
  • Six 1-byte offsets for five values: 6, 12, 14, 16, 18 and 40, the record’s length.
  • type table (bytes 6 to 12), name t, tbl_name t, rootpage 3, then the 21 characters of the statement and its zero byte (bytes 18 to 40).