SQLite internal tables

Original author: Official site
  • Transfer
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”, containing the special table “sqlite_master” (or “sqlite_temp_master” for the temporary database). The structure of the table is described in table 1.

Table 1


Normalizing the SQL field means that the query matches the following properties:
  1. Keywords are in capital letters.
  2. The keywords TEMP and TEMPORARY are deleted if they come after the word CREATE .
  3. Leading spaces are removed
  4. Multiple whitespace converts to one


In this table, one row is one database object. In addition to user objects, sqlite_master also stores internal database objects, with the exception of the sqlite_master table itself. The names of internal database objects begin with "sqlite_", respectively, the database forbids the user to create objects whose names begin with "sqlite_".

SQLITE_SEQUENCE


Internal table required to implement AUTOINCREMENT . For each user table using an increment, there corresponds a table row “sqlite_sequence”.
Table schema:
CREATE TABLE sqlite_sequence (name, seq);
  1. name - the name of the table using the increment.
  2. seq is the increment value. Increases when adding rows to the corresponding table. If the maximum value is exceeded (9223372036854775807), a SQLITE_FULL error occurs.

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

SQLITE_STAT1


An internal table 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 schema:
СREATE 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, ... Nth number is the approximate average number of rows in the table that have the same values ​​in first (N-1) columns of the index.

The stat field, after a sequence of numbers, may contain the keyword UNORDERED (separated by a space from the last number). If this keyword is present, the scheduler does not use the 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


Deprecated internal table for SQLite 3.6.18 - 3.7.8. Contained additional key distribution information.

SQLITE_STAT3


Internal table for SQLite versions 3.7.9 and higher. It is used if the database is assembled with the SQLITE_ENABLE_STAT3 or SQLITE_ENABLE_STAT4 parameter. Contains additional information about the distribution of keys within the index.
Table schema:
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 a table whose left column is smaller than sample.
  6. sample - contains the value of the leftmost column of the index


SQLITE_STAT4


Internal table for SQLite versions 3.8.1 and higher. It is used if the database is assembled with the SQLITE_ENABLE_STAT4 parameter. Contains additional information about the distribution of keys within the index.
Table schema:
CREATE TABLE sqlite_stat4 (tbl, idx, nEq, nLt, nDLt, sample);
  1. tbl - table name, idx - index name
  2. nEq is a list of numbers, where the k-th number is the approximate number of entries in the table, in which k left columns are equal to k left columns of the index.
  3. nLt is a list of numbers, where the k-th number is the approximate number of entries in the table in which k left columns are, in total, less than k left columns of the index.
  4. nDLt is a list of numbers, where the k-th number is the approximate number of different entries in the table, in which k left columns are, in aggregate, less than k left columns of the index.
  5. sample - the contents of the index in the recording 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 index columns.

RECORD FORMAT


A record format (RF) defines a sequence of values ​​corresponding to columns in a table or index. RF defines the number of columns, the data type of each column, and the content of each column. For recording, VARINT is used (a variable integer is long, 1-9 bytes in length, the Huffman static encoding method).
The RF header begins with one VARINT, which determines the total number of bytes in the header (including the VARINT itself). This is followed by several VARINT (one for each column of the table / index). These additional numbers are called Serial Type values ​​which are listed in table 2. The value of each column immediately follows the heading.

table 2


An example to understand the operation of SQLITE_STAT1:


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 column a:
    (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

Only registered users can participate in the survey. Please come in.

How often do you use SQLite?

  • 23.8% in 90% of projects 68
  • 19.2% in 50% of projects 55
  • 14.7% in 25% of projects 42
  • 24.2% Very rarely use 69
  • 17.8% I do not use at all 51

Also popular now: