` # Btree Meta Slots in SQLite and LumoSQL When SQLite, or an SQLite user, wants to store a small integer that describes something to do with a whole database instance, there is a mechanism for that. The first 100 bytes of a standard sqlite3 database format has space allocated for this purpose, called meta slots. LumoSQL can use an sqlite3 database format, and also two LMDB database formats, and has additional data it needs to store besides what SQLite already keeps. The obvious first need is for the LumoSQL version number (which will then identify the database as having been generated by LumoSQL, even if it can be read perfectly well by unmodified SQLite.) Other things it would be nice to know is "does this LumoSQL database have rowsums in it?", or "is this LumoSQL database encrypted?", and so on. Examples of how meta slots are used: * schema version for this database * the freelist page count * the `application_id`, and `user_version`, values SQLite offers to users for their own purposes (but seemingly not widely used?) This document explains what a meta slot is, how SQLite's native backend stores one, why that storage has a limit on how many slots are usable, and where the LumoSQL LMDB backend could differ. ## The btree meta API The meta interface is defined by index constants in `btree.h`: ``` #define BTREE_FREE_PAGE_COUNT 0 #define BTREE_SCHEMA_VERSION 1 #define BTREE_FILE_FORMAT 2 #define BTREE_DEFAULT_CACHE_SIZE 3 #define BTREE_LARGEST_ROOT_PAGE 4 #define BTREE_TEXT_ENCODING 5 #define BTREE_USER_VERSION 6 #define BTREE_INCR_VACUUM 7 #define BTREE_APPLICATION_ID 8 #define BTREE_DATA_VERSION 15 /* A virtual meta-value */ ``` `sqlite3BtreeGetMeta(Btree*, int idx, u32 *pValue)` reads slot index number `idx`; `sqlite3BtreeUpdateMeta()` writes it. The VDBE calls two opcodes, `OP_ReadCookie` and `OP_SetCookie` opcodes, while end users can set slots 8 and 6 with [`PRAGMA application_id` and `PRAGMA user_version`](https://sqlite.org/pragma.html#pragma_application_id). Slot 0 is read-only (it is the freelist count, maintained by the pager). Slot 15 is special and is discussed below. The comment in `btree.c` states that the remaining slots "are available for use by higher layers" and that is very interesting for LumoSQL. ## How the native backend stores a meta slot In SQLite's own backend it is four bytes inside the 100-byte database header. The header layout is given in [The SQLite Database File Format](https://sqlite.org/fileformat.html#the_database_header). `sqlite3BtreeGetMeta()` and `sqlite3BtreeUpdateMeta()` compute the byte offset directly: ``` *pMeta = get4byte(&pBt->pPage1->aData[36 + idx*4]); /* read */ put4byte(&pP1[36 + idx*4], iMeta); /* write */ ``` So slot `idx` lives at header offset `36 + idx*4`. The freelist count (slot 0) is at offset 36, and `application_id` (slot 8) is at offset 68, matching the named field at offset 68 in the file-format table. ## The sqlite3 meta slots are almost all allocated The btree API exposes indices 0 through 15. Indices 0–8 and 15 have assigned meanings, which leaves 9, 10, 11, 12, 13 and 14 unassigned at the API level. There are empty meta slots, but we cannot use them: | slot | offset | file-format meaning | |------|--------|---------------------| | 8 | 68 | Application ID | | 9 | 72 | reserved for expansion, must be zero | | 10 | 76 | reserved for expansion, must be zero | | 11 | 80 | reserved for expansion, must be zero | | 12 | 84 | reserved for expansion, must be zero | | 13 | 88 | reserved for expansion, must be zero | | 14 | 92 | reserved for expansion, must be zero | A later SQLite release may assigns a meaning to one of those offsets, so LumoSQL cannot use them. The "available for use by higher layers" comment is talking about the API it seems. Slot 15 (`BTREE_DATA_VERSION`) is different again. Its offset would be 96, but the value is never read from or written to the header. `sqlite3BtreeGetMeta()` intercepts index 15 and returns a number computed by the pager that detects whether another process has modified the database. It is a virtual meta value; there is no slot 15 on disk. LumoSQL implements this same behaviour for all backends. Question for SQLite gurus: does this mean that slot 15 is available for writing? That seems a bit messy, but the sqlite3 format is very constrained. ## Meta slots implemented in the LMDB backend LumoSQL replaces SQLite's `btree.c` with a backend that stores data in an LMDB database, so there is no 100-byte header. The API is still the same, the destination is very different though. LumoSQL implements the meta interface as key/value pairs in an LMDB sub-databasei so there are effectively no constraints. In `lumo_btree.c` the storage is: ``` unsigned char bidx = idx; /* the slot number is a one-byte key */ key.mv_data = &bidx; key.mv_size = 1; mdb_put(txn, dbi, &key, &data, 0); /* data is the 4-byte value */ ``` `mdb_get` returns the slot for keys 0-255, returning zero when the key is absent. Index 15 is a special case, returning the pager's virtual data version to match SQLite's behaviour with the native backend. `sqlite3BtreeGetMeta()` and `sqlite3BtreeUpdateMeta()` pass the index straight through with no range check. # Where to store LumoSQL metadata? One `application_id` or `user_version` (meta slots 8 and 6) are available, regardless of backend. Indices 9–14 are still not free in LumoSQL, because as soon as SQLite defines them then LumoSQL needs to be compatible with SQLite. It seems likely that LumoSQL needs to specify an additional range of meta slots, and that only some of this will be compatible with the sqlite3 database format. Questions for SQLite gurus include: * Are `application_id` and `user_version` really used much? According to https://sqlite.org/src/artifact?ci=trunk&filename=magic.txt nobody does, however perhaps lots of people do but never bother applying to the HRNA (Hipp Reserved Names Authority). * Let's say LumoSQL does apply for some numbers to the HRNA, what would this look like? How many dimensions are reasonable to list? Taking examples from disk label headers (see `cfdisk` etc) and other magic files, perhaps it is reasonable to have lots of numbers, but we would risk a combinatorial explosion: LumoSQL+rowsum; LumoSQL+rowsum+LMDB; LumoSQL+rowsum+LMDB+encryption and so on. ## References - [The SQLite Database File Format](https://sqlite.org/fileformat.html), section 1.3, the database header, and the statement that bytes from offset 72 to 99 are reserved and must be zero. - [PRAGMA application_id and PRAGMA user_version](https://sqlite.org/pragma.html#pragma_application_id). - SQLite source `btree.c`: `sqlite3BtreeGetMeta()` and `sqlite3BtreeUpdateMeta()`, offset formula `36 + idx*4`. - LumoSQL source `lumo_btree.c`: `get_meta_32_direct()`, `put_meta_32_direct()`, and the `sqlite3BtreeGetMeta`/`UpdateMeta` reimplementations.