SQLite introduction and analysis (8)-storage model (1)

Preface: SQLite is an embedded database. The data volume of applications is usually smaller than that of DBMS. Therefore, the storage model is very simple. In general, SQLite divides a data file into several equal-size pages and organizes these pages in the form of B trees. For large database management systems, such as Oracle or DM, the storage model is much more complex. Let's take Oracle as an example. It not only blocks data files physically, but also segments data files logically. the disk and page layers are also divided by DM. In any case, database files need to store a large amount of data. To better manage, query, and operate data files, DBMS has to physically and logically organize the data files. This section mainly describes the file format. The following section describes the page format.

1. File Format

1.1 Database Name
The application opens the database through sqlite3_open API. A parameter of this function is the name of the database file. SQLite is named as the main database (except the temporary database and memory database ). SQLite creates an independent file for each database.
In SQLite, the data file name is not the database name. SQLite maintains a separate temporary database (temp database) for each connection of the application, and temporary objects of temporary data inventory, such as tables and corresponding indexes. These temporary objects are only visible to the same connection (other connections to the process are invisible to the same thread). SQLite stores the temporary database in a separate temporary file, when the application closes the connection to the main database, it deletes the temporary file.

1.2 database file structure

In addition to the memory database, SQLite stores a database (main and temp) to a separate file.

 

1.2.1 page)
To better manage and read/write databases, SQLite divides a database (including a memory database) into fixed pages. The page size ranges from 512 to 32768 (both inclusive). The default page size is 1024 bytes (1 kb). In fact, the maximum page size is determined by the signed integer of 2 bytes. The entire database can be seen as an array of these pages. The subscript of the page array is the page number, starting from 1 to 2,147,483,647 (2 ^ 31-1 ). In fact, the upper limit of the array is determined by the maximum file size allowed by the file system. Page 0 is regarded as a NULL page, which does not exist physically. page 1 starts from the 0 offset of the object, and one page goes on to the next page.

Note: After a database is created, SQLite uses the default page size determined during compilation. Of course, you can run the pragma command to change the page size before creating the first table. SQLite stores the value as a part of the metadata in the file.

 

1.2.2. Page type
There are four types of pages: leaf pages (leaf), internal pages (internal), overflow pages (overflow), and free pages ). The internal page contains the navigation information during query. The leaf page stores data, such as tuples. If the data of a single tuples is too large to accommodate a single page, some data is stored on B-tree pages, and the rest are stored on overflow pages.
1.2.3. file header)

Page 1, which starts as a file, is special. It contains a file header of 100 bytes. When SQLite creates a file, the file header is initialized in the following format:

Structure of database file header
Offset	Size	Description
0	16	Header string
16	2	Page size in bytes
18	1	File format write version
19	1	File format read version
20	1	Bytes reserved at the end of each page
21	1	Max embedded payload fraction
22	1	Min embedded payload fraction
23	1	Min leaf payload fraction
24	4	File change counter
28	4	Reserved for future use
32	4	First freelist page
36	4	Number of freelist pages
40	60	15 4-byte meta values

Sample Data (100 bytes ):

 

53 51 4C 69 74 65 20 66 SQLite f 6F 72 6D 61 74 20 33 00 ormat 3. 04 00 01 01 00 40 20 20 .....@ 00 00 00 11 00 00 00 00 ........ 00 00 00 00 00 00 00 ........ 00 00 00 01 00 00 01 ........ 00 00 00 00 00 00 00 ........ 00 00 00 01 00 00 00 00 ........ 00 00 00 00 00 00 00 ........ 00 00 00 00 00 00 00 ........ 00 00 00 00 00 00 00 ........ 00 00 00 00 00 00 00 ........ 00 00 00 00

Header string (Header string ):
16 bytes: "SQLite format 3 ."

Page size:
Page size: 0x04 00, that is, 1024
File format:
File Format: 0x01, 0x01. The current version is 1.

Reserved space:
Reserved space: 0x00, 1 byte. SQLite retains a certain amount of space at the end of each page for use. The default value is 0.

Embedded payload:
The value of max embedded payload fraction (offset 21) limits the space that a tuples (records, units) in the nodes (pages) in the B tree can use most. 255 means 100%. The default value is 0x40, that is, 64 (25%). This ensures that a node (PAGE) has at least four units. If the load of a unit (payload, that is, the amount of data) exceeds the maximum value, the overflow data is saved to the overflow page. Once SQLite allocates an overflow page, it will move as much data as possible to the overflow page, the lower limit is min embedded payload fraction value (offset is 22), the default value is 32, that is, 12.5%.

The meaning of min leaf payload fraction is similar to that of min embedded payload fraction, except that it is a leaf node for B trees. The default value is 32, that is, 12.5%, the maximum load of leaf nodes is usually 100%, which does not need to be saved.

File change counter:
File Modification count, which is usually used by transactions, and is increased by the transaction value. The main purpose of this value is to avoid flushing the cache when the database changes.
Freelist:
The idle page linked list records the first page of the idle page chain in four bytes at the file header offset of 32. The four bytes at the offset of 36 are the number of idle pages. The idle page linked list is organized as follows:

Free pages can be divided into trunk pages (home page) and leaf pages (leaf page ). The file header Pointer Points to the first trunk page of the idle linked list, and each trunk page points to multiple leaf pages.
The Trunk page format is as follows, starting from the beginning of the page:
(1) Four bytes, pointing to the page number of the next trunk page;
(2) 4 bytes, number of leaf page pointers on the page;
(3) the page number pointing to the leaf page, 4 bytes for each item.

When a page is no longer used, SQLite adds it to the idle page linked list and does not release it from the local file system. When new data is added to the database, SQLite extracts the idle page from the idle linked list to store data. When the idle linked list is empty, SQLite adds a new page to the end of the database file through the local file system.

Note: You can use the vacuum command to delete idle linked lists. This Command copies data in the database to a temporary file and overwrites the original database file with a copy of the temporary file under the protection of transactions.

Meta variables
Metadata variable: a 15-4-byte metadata variable starting from the offset of 40, which is mainly related to the B-tree and VM. As follows:

** Meta values are as follows: ** Meta [0] Schema cookie. Changes with each schema change. ** Meta [1] File format of schema layer. ** Meta [2] Size of the page cache. ** Meta [3] Use freelist if 0. Autovacuum if greater than zero. ** Meta [4] Db text encoding. 1: UTF-8 2: UTF-16LE 3: UTF-16BE ** Meta [5] The user cookie. Used by the application. ** Meta [6] ** Meta [7] ** Meta [8] ** Meta [9]

1.2.4. Read the file header
When the application calls the API sqlite3_open to open the database file, SQLite reads the file header for database initialization.

Int sqlite3BtreeOpen (
Const char * zFilename,/* Name of the file containing the BTree database */
Sqlite3 * pSqlite,/* Associated database handle */
Btree ** ppBtree,/* Pointer to new Btree object written here */
Int flags/* Options */
){
// Read the file header
Sqlite3pager_read_fileheader (pBt-> pPager, sizeof (zDbHeader), zDbHeader );
// Set the page size
PBt-> pageSize = get2byte (& zDbHeader [16]);
//...
}

 

From: http://www.cnblogs.com/hustcat