SQLite 2 kept one b-tree of 1,024-byte pages from 2.0.0 to 2.8.17, but the bytes inside its cells changed three times before settling in 2.6.0. The header, pages, cell chains, overflow pages and sqlite_master are as for later files. This page describes what differs, read from the source of each release. The explorer reads all of them.
The layouts
| Releases | Header text | Format number | Rowid key | Record offsets | Index keys | Free pages |
|---|---|---|---|---|---|---|
| 2.0.0 to 2.0.8 | SQLite 2.0 | – | big-endian | N, 16-bit, machine order | text with NUL | chain |
| 2.1.0 to 2.1.7 | SQLite 2.1 | 0 | big-endian | N + 1, 1 to 3 bytes, little-endian | sortable numbers, 00-prefixed | chain |
| 2.2.0 to 2.3.3 | SQLite 2.1 | 1 | big-endian, top bit flipped | as 2.1 | as 2.1 | chain |
| 2.4.0 to 2.5.6 | SQLite 2.1 | 2 (from 2.4.0); 1 in an older file | big-endian, top bit flipped | as 2.1 | as 2.1 | trunk |
| 2.6.0 to 2.8.17 | SQLite 2.1 | 3, then 4 | big-endian, top bit flipped | as 2.1 | type letters | trunk |
The numbers on page 1 and in the cell headers are in the byte order of the machine that wrote the file, shown by the byte-order magic, as in every SQLite 2 file. The format number is meta value 2, at offset 64.
SQLite 2.0 to 2.2 keep three meta values, at offsets 60 to 71 (SQLITE_N_BTREE_META
was 4, counting the freelist). The rest of the 96-byte header is unused and zero.
SQLite 2.0
The header text is ** This file contains an SQLite 2.0 database **.
A cell’s 12-byte header holds a 16-bit key size and a 32-bit data size, with no high bytes:
| Offset | Size | Type | Name | Description |
|---|---|---|---|---|
| 0 | 4 | u32 | left child | As in later files. |
| 4 | 2 | u16 | nKey | The key’s size. |
| 6 | 2 | u16 | next cell | As in later files. |
| 8 | 4 | u32 | nData | The data’s size. |
A table’s key is the rowid as 4 big-endian bytes, with no bit flipped.
A record is N 16-bit offsets in the machine’s byte order, one for each value, measured
from the start of the record; then the values. The first offset is therefore twice N.
There is no offset for the end: the last value runs to the end of the record.
A NULL has no bytes, so its offset equals the next value’s. Every other value is text
with a zero byte at the end, numbers as their digits (%d or
%.15g).
An index entry’s key is each value in turn, then the row’s 4-byte key, big-endian. A NULL is a single zero byte, and every other value is its text with a zero byte, so a number is written as its digits. The entry’s data is empty.
SQLite 2.1
The header text says 2.1, as it does in every later release, and the format number is 0. The cell sizes become 24-bit, split as in later files, and the record takes its later form of N + 1 little-endian offsets of 1 to 3 bytes.
Rowid keys are still 4 big-endian bytes with no bit flipped.
Index keys use the sortable number form of later files, without the type letters:
| Value | Bytes |
|---|---|
| NULL | 00 00 |
| number, or text that looks like one | 00, the sortable text, 00 |
| other text | the text, 00 |
Empty text and a NULL are indistinguishable by their first byte, since the empty text is
a lone 00: a reader that meets 00 00 cannot tell a NULL from
an empty string followed by a value that starts with a zero byte. The explorer reads
such a pair as a NULL.
SQLite 2.2 to 2.5
These releases write the 2.1 record and index-key forms, with rowid keys that have
their top bit flipped, as in later files. INTEGER PRIMARY KEY arrives in
2.2.0; its column in the record is NULL, and the rowid stands in for it. SQLite 2.5
chooses text or a number from a type string it adds to the instruction, but the bytes
are the same.
The format number is 1 from 2.2.0 and 2 from 2.4.0. A SQLite 2.4 or 2.5 file that was first made by 2.2 or 2.3 keeps format number 1.
The free page list changed from the chain to trunk pages at 2.4.0. A format 1 file can have either form.
What later releases do
From 2.6.0, the library converts a file of an older layout the first time it opens it, “automatically and irreversibly”, rebuilding every table and setting the format number to 4. Such files are unusual, since each layout lasted a few months, but the explorer reads them without changing them.