Page 1 of an SQLite 2 file is the database header. Its first 96 bytes hold the header text, a magic number that gives the file’s byte order, the start of the freelist and nine 4-byte meta values; the other 928 bytes of the page are not used. The schema table does not start here but on page 2.
Fields
Every number is in the byte order of the machine that wrote the file, as the
byte-order magic shows. In SQLite 2.8.17’s source the page is the struct
PageOne: char zMagic[48]; int iMagic; Pgno freeList; int nFree;
int aMeta[9];. The free page count is counted as meta value 0, so there are
ten meta values in all (SQLITE_N_BTREE_META is 10).
| Offset | Size | Type | Name | Description |
|---|---|---|---|---|
| 0 | 48 | char[48] | zMagic | The header text ** This file contains an SQLite 2.1 database ** (47 characters) and a zero byte. See below. |
| 48 | 4 | u32 | iMagic | 0xDAE37528, in the writer’s byte order. See Byte order. |
| 52 | 4 | u32 | freeList | The first freelist trunk page, or 0 when no page is free. |
| 56 | 4 | u32 | nFree | Meta value 0: how many pages are free, trunk pages included. |
| 60 | 4 | u32 | aMeta[0] | Meta value 1, the schema cookie. |
| 64 | 4 | u32 | aMeta[1] | Meta value 2, the file format number. |
| 68 | 4 | i32 | aMeta[2] | Meta value 3, the default cache size. |
| 72 | 4 | u32 | aMeta[3] | Meta value 4, the safety level (from 2.8.0). |
| 76 | 4 | u32 | aMeta[4] | Meta value 5, not used. |
| 80 | 4 | u32 | aMeta[5] | Meta value 6, the temp store. |
| 84 | 12 | u32[3] | aMeta[6..8] | Meta values 7 to 9, not used. |
| 96 | 928 | – | – | The rest of page 1, not used. |
Header text
The file starts with these 48 bytes: the text and a zero byte.
00000000 2A 2A 20 54 68 69 73 20 66 69 6C 65 20 63 6F 6E ** This file con
00000010 74 61 69 6E 73 20 61 6E 20 53 51 4C 69 74 65 20 tains an SQLite
00000020 32 2E 31 20 64 61 74 61 62 61 73 65 20 2A 2A 00 2.1 database **.The text says 2.1 in every version from 2.1.0 to 2.8.17, so it does not give the
version that wrote the file; the file format number does.
SQLite 2.0 wrote SQLite 2.0 in the same place, with an older layout (see
older SQLite 2 files).
An SQLite 3 file starts with SQLite format 3 instead.
Byte order
SQLite 2 writes its numbers in the native byte order of the machine it runs on. The 4 bytes at offset 48 hold 0xDAE37528 in that order, so a reader can tell which order the file uses:
- Read the 4 bytes at offset 48 as a big-endian number. If it is 0xDAE37528, the file is big-endian.
- Otherwise read them as little-endian. If that is 0xDAE37528, the file is little-endian.
- If neither matches, the file is not a valid SQLite 2 database.
| Bytes at offset 48 | File | Written on, for example |
|---|---|---|
DA E3 75 28 | big-endian | PowerPC Macs, SPARC |
28 75 E3 DA | little-endian | x86 PCs |
Every other number in the file follows that order, with two exceptions: the rowid keys of tables are always big-endian, and the offsets inside a record are always little-endian (see records and keys). SQLite 2.6.3 and later read files of either order; earlier versions could only read a file made on a machine of the same order.
Freelist
Offset 52 gives the first freelist trunk page, or 0 when the freelist is empty. Offset 56 counts every free page, the trunk pages included. A reader can check the count against the pages it finds by following the chain. The layout of the trunk pages is on the freelist page.
Meta values
SQLite 2 keeps its settings in meta values 1 to 9, after the free page count. In SQLite 2.1.0 there were only three meta values after the count.
| Meta value | Offset | Name | Meaning |
|---|---|---|---|
| 0 | 56 | free pages | The number of free pages, trunks included. |
| 1 | 60 | schema cookie | Changed whenever the schema changes: SQLite sets it to the old value plus 1 plus a random byte. A connection that sees a new cookie reads the schema again. |
| 2 | 64 | file format | The layout of the file; see File format. |
| 3 | 68 | default cache size | A signed number. Its absolute value is the default page cache size; a negative value also means synchronous writes are off. |
| 4 | 72 | safety level | PRAGMA default_synchronous, from SQLite 2.8.0: 1 off, 2 normal, 3 full. 0 means not set, taken as 2. |
| 5 | 76 | – | Not used. |
| 6 | 80 | temp store | PRAGMA default_temp_store: 0 the compiled-in default, 1 a file, 2 memory. |
| 7 to 9 | 84 | – | Not used. |
File format
Meta value 2, at offset 64, says which of SQLite 2’s layouts the file uses. This format is 3 or 4, or 0 in a database with no tables yet.
| Value | Written by | Meaning |
|---|---|---|
0 | 2.1.0, or any version before the first table | In an empty database, no layout yet; SQLite 2.8 treats it as 4. In a file with tables, SQLite 2.1’s layout, a separate format. |
1 | 2.2.0 | INTEGER PRIMARY KEY as the b-tree key; older index keys. A separate format. |
2 | 2.4.0 | Rowids given in sequence; older index keys. A separate format. |
3 | 2.6.0 | Index keys with the type letters a, b and c. This format. |
4 | 2.7.0 to 2.8.17 | Values in text columns are always indexed as text. This format. |
A comment in SQLite 2.8.17’s main.c dates format 1 to 2.1.0 and format 2 to 2.2.0; the older releases themselves show that the comment is one step off at the start, as in the table above.
When SQLite 2.8 opens a file, it treats format 0 as 4, reports “unsupported file format” above 4, and below 3 rebuilds every table and sets 4, after which older libraries cannot read the file.
Example
The first 96 bytes of a little-endian file holding one table, written as format 4. This example is built from the 2.8.17 layout; no SQLite 2 file was found among the files examined for this site.
00000000 2A 2A 20 54 68 69 73 20 66 69 6C 65 20 63 6F 6E
00000010 74 61 69 6E 73 20 61 6E 20 53 51 4C 69 74 65 20
00000020 32 2E 31 20 64 61 74 61 62 61 73 65 20 2A 2A 00
00000030 28 75 E3 DA 00 00 00 00 00 00 00 00 5A 00 00 00
00000040 04 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00000050 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00- 48:
28 75 E3 DA, 0xDAE37528 read little-endian: a little-endian file. - 52, 56: no free pages.
- 60:
5A 00 00 00, schema cookie 90. - 64:
04 00 00 00, file format 4. - 68 to 95: the cache size, safety level, temp store and unused values, all 0.
The same file written on a big-endian machine has DA E3 75 28 at 48,
00 00 00 5A at 60 and 00 00 00 04 at 64.