.SQLITE File Format – Records and keys

Rowid keys

A table’s key is 4 bytes: the rowid as a 32-bit signed integer with its top bit flipped, stored big-endian whatever the file’s byte order. Flipping the top bit makes the keys sort as signed numbers when compared byte by byte with memcmp.

To read one: take the 4 bytes as a big-endian number, XOR it with 0x80000000, and read the result as a signed 32-bit number.

From SQLite 2.2.0 a column declared INTEGER PRIMARY KEY is the rowid. Whether the record then holds NULL or the number in that column is not confirmed; the explorer shows the rowid in that column when the record holds nothing for it.

Records

A row’s data is a record (made by the OP_MakeRecord operation in the 2.8.17 source): a list of offsets, then the values.

| idx0 | idx1 | … | idx(N−1) | idx(N) | data0 | … | data(N−1) |

• A record of N values starts with N + 1 offsets. idx(k) is the offset of value k from the start of the record, and idx(N) is the record’s length.
• The offsets are little-endian whatever the file’s byte order.
• Each offset is 1, 2 or 3 bytes, the same for every offset in the record, chosen by the record’s size: under 256 bytes 1, under 65,536 bytes 2, otherwise 3.
• Every value is text followed by a zero byte. NULL takes no bytes: its offset equals the next one. An empty string is the single zero byte. Numbers are stored as their text.

To read a record of S bytes (the cell’s data size):

1. Choose the offset width W: 1 if S < 256, 2 if S < 65,536, else 3.
2. Read idx0. The number of values is idx0 ÷ W − 1.
3. Read the remaining offsets. Value k is the bytes from idx(k) to idx(k + 1). If they are equal, the value is NULL; otherwise drop the final zero byte and read the rest as text.

The columns are in the order of the table’s CREATE TABLE statement. The text is decoded as UTF-8 by the explorer; the file itself does not record an encoding.

Example record

The row ('hi', NULL, '42') of a table with three columns, as in the cell example. Built from the 2.8.17 layout.

04 07 07 0A 68 69 00 34 32 00

• The record is 10 bytes, under 256, so the offsets are 1 byte each. idx0 is 4, so there are 4 ÷ 1 − 1 = 3 values.
• Value 0: bytes 4 to 7, 68 69 00, the text hi.
• Value 1: bytes 7 to 7, nothing: NULL.
• Value 2: bytes 7 to 10, 34 32 00, the text 42. Whether it was given as a number or as text, it is stored the same way.

Index keys

In file formats 3 and 4 (SQLite 2.6.0 and later) an index entry’s key is each indexed value in turn, as a type letter, its text and a zero byte, then the row’s 4-byte rowid key. Keys compare byte by byte, so the letters sort NULLs first, then numbers, then text.

• In format 4, a value in a text column (one whose declared type contains BLOB, CLOB, CHAR or TEXT) is always c.
• In format 3, any value that looks like a number is b, whatever the column.

The sortable form of a number (the function sqliteRealToSortable) is text whose byte order matches numerical order: a sign character (- or 0), two base-64 digits of a biased exponent (the exponent in powers of 64, plus 1,024), then up to 10 base-64 digits of the mantissa, using the digits 0-9A-Za-z|~ in that order. It is lossy; the table row keeps the value as it was written.

As far as the sources show, the data part of an index entry is empty; this is not confirmed.

Example, built from the 2.8.17 layout: an entry in an index on the first column of the table above, for the row with rowid 1 and the value 'hi':

63 68 69 00 80 00 00 01

c for text, hi and its zero byte, then the rowid key of row 1.

SQLite 2.2 to 2.5 (file formats 1 and 2) built index keys differently, which is why their files are a separate format; see older SQLite 2 files.