SQLite database file format

Database Naming conventions

The Sqlite3_open () API uses the file name of the database, either relative to the current working directory or a full pathname starting from the system root file tree . Any formal file name accepted by the local filesystem is good.

If the file name is a null pointer in the C language (that is, 0), SQLite creates and opens a temporary file. If the file name is a memory address, SQLite creates a memory database. In both cases, when the application shuts down the database, the database is destroyed, that is, the database is not persistent. SQLite randomly selects temporary file names (starting with Sqlite_, followed by a 16-digit alphanumeric random string). The file tries to store in the following directory, (1)/var/tmp, (2)/usr/tmp, (3)/tmp, (4) the current working directory. Regardless of how open the database (file database, temporary database, memory database), within SQLite is named the main database.

Internally, the database file name is not the database name. They are related but different concepts. With the attach command, you can associate a database file with a different database alias to a database connection. You can access the database files through these aliases.

When an application opens a database with Sqlite3_open (), SQLite maintains a separate staging database for each connection. The staging database stores temporary objects (tables and their indexes). The application can use both names (main and temp) in its query, while select * from Temp.table1 returns all rows in table 1 in the staging database, not in the primary database. The catalog name for the staging database is Sqlite_temp_master. Temporary objects are visible only within the same database connection , and different connections to the same database are invisible, even for the same thread, process, or program. SQLite stores the staging database in a separate temporary file that differs from the primary database file. This temporary file is deleted when the application closes the connection to the primary database.

Database file System

In addition to the in-memory database, SQLite stores an entire database (main or temp) in a separate page.

Page: In order to facilitate the management of space, SQLite divides the database (including the memory database) into a fixed-size area called the Database page (the power of page size is 2, from 512 to 32768, the default 1024. The upper bound is determined by a 2-byte signed integer in the code or in the external store. A database is made up of an array of pages (which can be enlarged or shrunk), the subscript of an array is called a page number, and the subscript starts at 1 until 231-1 ends (the upper bound is determined by the maximum file length of the local file system). Page No. 0 is treated as if it is not a page or a page, in summary, a page that does not exist physically. Page 1th and subsequent pages are arranged sequentially from the location of the database file offset of 0.

Once you have created the database file, SQLite takes the default size, but you can also change the page size by compiling a command before creating the first table. SQLite stores the page size as part of the metadata. This value will be used instead of the default page size (the compile command is used to change the behavior of the database).

Page type: There are four kinds of pages: leaf page, internal page, overflow leaf, free page. free pages are invalid (not currently used), others are active pages.

B + Tree internal pages have search navigation information, leaf pages store the actual data (rows in the table), if a line of information is too large to be placed on a page, then part of the data into the leaf page , the remaining overflow page .

B-Trees have internal pages with search information and data.

File header: SQLite can use any page type for any database page, except for page 1th, which is always the B + tree's internal page. A 100-byte header record is stored at the page 0 offset address, and the file header record describes the data structure of the database file. SQLite initializes the file header when a database file is created.

Offset Size Description
0 Header String This is the byte string: "SQLite format 3."
2 Page size in bytes
1 File Format Write version
1 File Format Read version
1 Bytes reserved at the end of each page
1 Max Embedded Payload fraction
1 Min Embedded Payload fraction
1 Min leaf Payload fraction
4 File Change counter
4 Reserved for future use
4 First Freelist page
4 Number of freelist pages
4-byte Meta values

SQLite database file format