Sqlite Database File Structure

Whether it is the main database or the temporary one, Sqlite stores the entire database in the form of a single database file. In over last blog we have given a brief over on the Framework Of Sqlite, know continuing the series we will move forward and cover the Sqlite Database File Structure. However, this doesn’t include the in-memory databases. In its entire lifespan, the database first grows in size and then it falls off. The database file increases in size as long as the operating system or the file system entitle it to enhance in terms of size. Let’s know about the Sqlite database file format with the help of this given article.

Hot Journal/Hot WAL File

At the start of a transaction, any additional information is stored in another file which is known “rollback journal”. This condition also applies to scenarios when the Sqlite is in WAL mode. If there is a system crash or failure before the completion of a transaction, then we can restore the main database from the content of rollback journal or from the WAL log files.

The rollback journal and the WAL files are often referred as hot WAL file or hot journal. Reason being whenever there is a suck kind of system failure, the content of the rollback journal /WAL is written back to the database file in order to restore or revert the database back to its original mode.

Let us know understand how these database files are structured into pages

Database Page

For flexibility in reading or writing of data from databases and for the better management of space, each database is further divided by the Sqlite into pertinent regions known as pages or database pages.

Page Size: The size for a page of Sqlite database is a power of 2 and comes in the range of 512 (2⁹) to 65,336 (2¹⁶) inclusive of both the numbers, whereas the default size is 1024. The database is considered to be an array of pages and an index to these page array is known as page number. Page number start with the digit “1” and extend up to 2147483647.

Page Type: Based on their usage, the Sqlite database consists of four different types of pages

1. Leaf
2. Internal
3. Overflow
4. Free

Free Page

Free pages are those pages which are currently not used. In other words, they are in an inactive state, whereas the other pages are in active state.

B⁺-tree Internal Pages

These pages contain tree search related navigational information and data.

Leaf Pages

They store real data in B⁺-trees for example table rows

Overflow Pages

If the data of row is too large to fit into the page, then some data is stored in B⁺ tree and the remaining in the overflow pages.

File Header:

The Sqlite can employ any database page for any page type other than the Page 1 which is a B⁺-tree internal page. In any database file, the first 100 bytes contains a file header record which is stored at the beginning file offset of 0. This header information defines the database file structure. The file header is divided into a number of fields which is given below.

Here you can find the details of each header element

Header String:

Any database of Sqlite which is valid begins is of 16 bytes and this sequence corresponds to the Sqlite format 3.

Page size:

This refers to the size of each database page

File Format:

The two bytes at 18 and 19 offsets are used to allow for augmentation of the file format in the coming versions of Sqlite. If their value is not 1 in the current version of Sqlite then error is reported. If enhancements are done to the file formats in the future, then the numbers will be more than 1 in order to show that it is the new file format version.

Reserved Space:

Sqlite reserves a small amount of space at the end of every page for its own purpose and at offset 20, this space is allocated. It has a value of <=225 bytes.

Embedded Payload:

The max embedded payload fraction represent a value at offset 21 which is the measure of the total space that can be used for a single entry known a record.

As we know that Sqlite has set aside an overflow page by which it moves a many bytes to the overflow page without making the cell size to have a value below the min embedded payload fraction which is at offset 22. It’s default value is 32.

The min leaf Payload fraction value at offset 23 is just like the min embedded payload fraction excluding the scenarios of B⁺-tree leaf pages and its default value is 32.

The max payload fraction value for a leaf node is always 255.

File Change Counter

The file change counter at offset 24 is a 4 byte integer which is employed by transactions and this value increases with every transaction.

Freelist

All the unused pages of the database are stored in the Freelist. It occurs at the file header at offset value 32.

Meta Variables

At offset value of 40, there are 15 4-byte integers which are allocated for B⁺-tree and for the virtual machine modules.

The above terms and fields are concerned with the Sqlite database file format .All the fields and their details discussed above clearly gives us an idea how the Sqlite database has divided the database into pages for better management and flexibility.

Conclusion

The above elaborated description of Sqlite Database File format is sufficient enough, but if we want to practically analysis the structure we can go for a free utility like Sqlite Viewer. The utility will provide a interface to explore more about the Sqlite structure by providing the user a complete view of Sqlite database along with its all supported components.

Let’s know about the Sqlite Database File Structure and Sqlite Page Types including all the fields and their details with the help of this nice article.