Recall in the previous post that we parsed information about the page size of the database.

In this article, let's continue to parse the database file and print the number of tables defined in the database.

The Schema Table

sqlite_schema is a table that stores the schema for the DB. It contains one row for each table, index, view and trigger (known as objects).

The number of tables in the DB is equal to the number of cells on the sqlite_schema page.

We know:

• The first 100 bytes of the database file consist of the database file header.

• Pages are numbered beginning with 1

• The size of the page is a power of two between 512 - 65536. The actual size can be found in the headers as described above

• The 100-byte DB file header is found only on page 1. It's a table b-tree page.

• All other b-tree pages in the DB omit this 100-byte header

• The b-tree corresponding to the sqlite_schema table is always a table b-tree and always has a root page of 1.

• The sqlite_schema table contains the root page number for every other table and index in the database file.

B-tree page header

The b-tree page header is 8 bytes Offset 3, Size 2 - The two-byte integer at offset 3 gives the number of cells on the page.

A hexdump reveals the page header and the beginning of the sqlite_schema table.

00000000  53 51 4c 69 74 65 20 66  6f 72 6d 61 74 20 33 00  |SQLite format 3.|
00000010  10 00 01 01 00 40 20 20  00 00 00 05 00 00 00 04  |.....@  ........|
00000020  00 00 00 00 00 00 00 00  00 00 00 02 00 00 00 04  |................|
00000030  00 00 00 00 00 00 00 00  00 00 00 01 00 00 00 00  |................|
00000040  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00000050  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 05  |................|
00000060  00 2e 4b 90 0d 00 00 00  03 0e c3 00 0f 8f 0f 3d  |..K............=|
                               ^^  ^^

package main

import (
	"bytes"
	"encoding/binary"
	"fmt"
	"log"
	"os"
	// Available if you need it!
	//"github.com/xwb1989/sqlparser"
)

// Usage: your_program.sh sample.db .dbinfo
func main() {
	databaseFilePath := os.Args[1]
	command := os.Args[2]

	switch command {
	case ".dbinfo":
		databaseFile, err := os.Open(databaseFilePath)
		if err != nil {
			log.Fatal(err)
		}

		header := make([]byte, 108)

		_, err = databaseFile.Read(header)
		if err != nil {
			log.Fatal(err)
		}

		var pageSize uint16
		// https://www.sqlite.org/fileformat.html#the_database_header offset 16 is what we are after
		if err := binary.Read(bytes.NewReader(header[16:18]), binary.BigEndian, &pageSize); err != nil {
			fmt.Println("Failed to read integer:", err)
			return
		}

		var numTables uint16

		// https://www.sqlite.org/fileformat.html#b_tree_pages
		// get the number of cells, two bytes, per the B-tree Page Header Format, located at offset 3 (after 100 byte header offset)
		// 105 is because of the exclusive upperbound
		if err := binary.Read(bytes.NewReader(header[103:105]), binary.BigEndian, &numTables); err != nil {
			fmt.Println("Failed to read header:", err)
			return
		}

		fmt.Printf("database page size: %v\n", pageSize)
		fmt.Printf("number of tables: %v", numTables)
	default:
		fmt.Println("Unknown command", command)
		os.Exit(1)
	}
}

go build -o sqliteparser app/*.go
database page size: 4096
number of tables: 3

Conclusion

We extended our SQLite parser to determine the number of tables in a database file. We leveraged our understanding of the SQLite file format to extract this information by:

1. Identifying that the sqlite_schema table contains the schema information for the database
2. Learning that the number of tables equals the number of cells on the sqlite_schema page
3. Reading the B-tree page header, specifically the 2-byte integer at offset 3 (after the 100-byte database header)
4. Implementing this logic in Go using binary reading operations

Our parser now successfully reads both the page size (from the previous article) and the number of tables from a SQLite database file. This demonstrates how understanding the binary format of SQLite enables us to understanding the structure of the file to truly appreciate its capabilities as we use it to develop.