SQLite internal tables

This post describes the internal SQLite tables: their structure and purpose.

SQLITE_MASTER

The first page of the database file is the root page "table B - tree", which contains the special table "sqlite_master" (or "sqlite_temp_master" for the temporary database). The structure of the table is described in table 1.


')
Normalization of the SQL field means that the query has the following properties:

1. Keywords spelled in capital letters.
2. The keywords TEMP and TEMPORARY are deleted if they appear after the word CREATE .
3. Leading spaces removed
4. Multiple space converted to one

In this table, one row is one database object. In addition to user objects, the internal database objects are stored in “sqlite_master”, with the exception of the “sqlite_master” table itself. The names of the internal database objects begin with "sqlite_", respectively, the database prohibits the user to create objects whose names begin with "sqlite_".

SQLITE_SEQUENCE

Internal table required to implement AUTOINCREMENT . For each user table that uses increment, there is a table row “sqlite_sequence”.
Table layout:
CREATE TABLE sqlite_sequence (name, seq);

1. name - the name of the table using the increment.
2. seq - increment value. Increased when adding rows to the appropriate table. If the maximum value is exceeded (9223372036854775807), an error SQLITE_FULL occurs.

The user can add, modify, and delete rows from the “sqlite_sequence” table, but cannot delete the table itself.

SQLITE_STAT1

The internal table is created using the ANALYZE command. Used to store reference information about tables and indexes, which the scheduler can use to find an effective way to execute queries.
Table layout:
CREATE TABLE sqlite_stat1 (tbl, idx, stat)

1. tbl - table name
2. ind - index name
3. stat is a sequence of integers: the first is the approximate number of rows in the table, the second is the approximate average number of rows in the table that have the same value in the first column of the index, ... The Nth number is the approximate average number of rows in the table that have the same values ​​in first (N-1) index columns.

The stat field, after a sequence of numbers, may contain the keyword UNORDERED (separated from the last number by a space). If this keyword is present, the scheduler does not use an index for sorting or range query.
If the idx column is NULL , then the stat field contains the number of rows in the specified table.

SQLITE_STAT2

Outdated internal table for SQLite versions 3.6.18 - 3.7.8. Contained additional information about the distribution of keys.

SQLITE_STAT3

Internal table for SQLite versions 3.7.9 and higher. Used when the database is compiled with the SQLITE_ENABLE_STAT3 or SQLITE_ENABLE_STAT4 parameter. Contains additional information about the distribution of keys within the index.
Table layout:
CREATE TABLE sqlite_stat3 (tbl, idx, nEq, nLt, nDLt, sample)

1. tbl - table name
2. idx - index name
3. nEq is the approximate average number of records in the table whose left column is sample.
4. nLt is the approximate average number of records in the table whose left column is smaller than sample.
5. nDlt is the approximate average number of different records in the table whose left column is smaller than sample.
6. sample - contains the value of the leftmost index column

SQLITE_STAT4

Internal table for SQLite versions 3.8.1 and higher. Used if the database is compiled with the SQLITE_ENABLE_STAT4 parameter. Contains additional information about the distribution of keys within the index.
Table layout:
CREATE TABLE sqlite_stat4 (tbl, idx, nEq, nLt, nDLt, sample);

1. tbl is the table name, idx is the index name
2. nEq is a list of numbers, where the kth number is an approximate number of entries in the table, in which k left columns are equal to k left index columns.
3. nLt is a list of numbers, where the kth number is the approximate number of records in the table, in which k left columns, in aggregate, are less than k left columns of the index.
4. nDLt is a list of numbers, where the kth number is the approximate number of different entries in the table, in which k left columns, in aggregate, are less than k left columns of the index.
5. sample - the index content in the record format.

SQLITE_STAT3 vs SQLITE_STAT4

sqlite_stat4 is a generalization of the sqlite_stat3 table. The sqlite_stat3 table provides information about the leftmost column of the index, while the sqlite_stat4 table provides information about all the columns in the index.

RECORD FORMAT

Record Format (RF) defines the sequence of values ​​corresponding to columns in a table or index. RF determines the number of columns, the data type of each column, and the content of each column. VARINT is used for recording (variable integer, 1-9 bytes in length, Huffman static coding method).
The RF header starts with a single VARINT, which defines the total number of bytes in the header (including the VARINT itself). Then follow several VARINTs (one for each column of the table / index). These additional numbers are called Serial Type whose values ​​are listed in Table 2. The meaning of each column immediately follows the heading.

An example to understand how SQLITE_STAT1 works:

Suppose there is a database:
CREATE TABLE test (a, b, c);
CREATE INDEX ind ON test ("a", "b", "c")

Test table data:


After executing the ANALYZE command, the stat field for this table will look like (8 3 2 1):

1. 8 is the number of rows in the table.
2. 3 is the average number of rows that have the same a column:
   (4 + 3 + 1) / 3 = 2.67
3. 2 is the average number of rows that have the same columns a and b:
   (2 + 2 + 1) / 3 = 1.67
4. 1 is the average number of rows that have the same columns a, b, and c:
   (1 + 1 + 1) / 3 = 1