SQLite3 database file: Python parsing library

SQLite3 is a popular serverless SQL engine, implemented as a library to be used within other applications. It keeps its databases as regular disk files.

Every database file is segmented into pages. First page (starting at the very beginning) is special: it contains a file-global header which specifies some data relevant to proper parsing (i.e. format versions, size of page, etc). After the header, normal contents of the first page follow.

Each page would be of some type, and generally, they would be reached via the links starting from the first page. First page type (root_page) is always "btree_page".

KS implementation details

License: CC0-1.0

This page hosts a formal specification of SQLite3 database file using Kaitai Struct. This specification can be automatically translated into a variety of programming languages to get a parsing library.

Usage

Parse a local file and get structure in memory:

data = Sqlite3.from_file("path/to/local/file.SQLite3 database file")

Or parse structure from a bytes:

from kaitaistruct import KaitaiStream, BytesIO

raw = b"\x00\x01\x02..."
data = Sqlite3(KaitaiStream(BytesIO(raw)))

After that, one can get various attributes from the structure by invoking getter methods like:

data.len_page_mod # => The database page size in bytes. Must be a power of two between
512 and 32768 inclusive, or the value 1 representing a page size
of 65536.

Python source code to parse SQLite3 database file

sqlite3.py

# This is a generated file! Please edit source .ksy file and use kaitai-struct-compiler to rebuild

from pkg_resources import parse_version
from kaitaistruct import __version__ as ks_version, KaitaiStruct, KaitaiStream, BytesIO
from enum import Enum


if parse_version(ks_version) < parse_version('0.7'):
    raise Exception("Incompatible Kaitai Struct Python API: 0.7 or later is required, but you have %s" % (ks_version))

from vlq_base128_be import VlqBase128Be
class Sqlite3(KaitaiStruct):
    """SQLite3 is a popular serverless SQL engine, implemented as a library
    to be used within other applications. It keeps its databases as
    regular disk files.
    
    Every database file is segmented into pages. First page (starting at
    the very beginning) is special: it contains a file-global header
    which specifies some data relevant to proper parsing (i.e. format
    versions, size of page, etc). After the header, normal contents of
    the first page follow.
    
    Each page would be of some type, and generally, they would be
    reached via the links starting from the first page. First page type
    (`root_page`) is always "btree_page".
    
    .. seealso::
       Source - https://www.sqlite.org/fileformat.html
    """

    class Versions(Enum):
        legacy = 1
        wal = 2

    class Encodings(Enum):
        utf_8 = 1
        utf_16le = 2
        utf_16be = 3
    def __init__(self, _io, _parent=None, _root=None):
        self._io = _io
        self._parent = _parent
        self._root = _root if _root else self
        self._read()

    def _read(self):
        self.magic = self._io.ensure_fixed_contents(b"\x53\x51\x4C\x69\x74\x65\x20\x66\x6F\x72\x6D\x61\x74\x20\x33\x00")
        self.len_page_mod = self._io.read_u2be()
        self.write_version = self._root.Versions(self._io.read_u1())
        self.read_version = self._root.Versions(self._io.read_u1())
        self.reserved_space = self._io.read_u1()
        self.max_payload_frac = self._io.read_u1()
        self.min_payload_frac = self._io.read_u1()
        self.leaf_payload_frac = self._io.read_u1()
        self.file_change_counter = self._io.read_u4be()
        self.num_pages = self._io.read_u4be()
        self.first_freelist_trunk_page = self._io.read_u4be()
        self.num_freelist_pages = self._io.read_u4be()
        self.schema_cookie = self._io.read_u4be()
        self.schema_format = self._io.read_u4be()
        self.def_page_cache_size = self._io.read_u4be()
        self.largest_root_page = self._io.read_u4be()
        self.text_encoding = self._root.Encodings(self._io.read_u4be())
        self.user_version = self._io.read_u4be()
        self.is_incremental_vacuum = self._io.read_u4be()
        self.application_id = self._io.read_u4be()
        self.reserved = self._io.read_bytes(20)
        self.version_valid_for = self._io.read_u4be()
        self.sqlite_version_number = self._io.read_u4be()
        self.root_page = self._root.BtreePage(self._io, self, self._root)

    class Serial(KaitaiStruct):
        def __init__(self, _io, _parent=None, _root=None):
            self._io = _io
            self._parent = _parent
            self._root = _root if _root else self
            self._read()

        def _read(self):
            self.code = VlqBase128Be(self._io)

        @property
        def is_blob(self):
            if hasattr(self, '_m_is_blob'):
                return self._m_is_blob if hasattr(self, '_m_is_blob') else None

            self._m_is_blob =  ((self.code.value >= 12) and ((self.code.value % 2) == 0)) 
            return self._m_is_blob if hasattr(self, '_m_is_blob') else None

        @property
        def is_string(self):
            if hasattr(self, '_m_is_string'):
                return self._m_is_string if hasattr(self, '_m_is_string') else None

            self._m_is_string =  ((self.code.value >= 13) and ((self.code.value % 2) == 1)) 
            return self._m_is_string if hasattr(self, '_m_is_string') else None

        @property
        def len_content(self):
            if hasattr(self, '_m_len_content'):
                return self._m_len_content if hasattr(self, '_m_len_content') else None

            if self.code.value >= 12:
                self._m_len_content = (self.code.value - 12) // 2

            return self._m_len_content if hasattr(self, '_m_len_content') else None


    class BtreePage(KaitaiStruct):
        def __init__(self, _io, _parent=None, _root=None):
            self._io = _io
            self._parent = _parent
            self._root = _root if _root else self
            self._read()

        def _read(self):
            self.page_type = self._io.read_u1()
            self.first_freeblock = self._io.read_u2be()
            self.num_cells = self._io.read_u2be()
            self.ofs_cells = self._io.read_u2be()
            self.num_frag_free_bytes = self._io.read_u1()
            if  ((self.page_type == 2) or (self.page_type == 5)) :
                self.right_ptr = self._io.read_u4be()

            self.cells = [None] * (self.num_cells)
            for i in range(self.num_cells):
                self.cells[i] = self._root.RefCell(self._io, self, self._root)



    class CellIndexLeaf(KaitaiStruct):
        """
        .. seealso::
           Source - https://www.sqlite.org/fileformat.html#b_tree_pages
        """
        def __init__(self, _io, _parent=None, _root=None):
            self._io = _io
            self._parent = _parent
            self._root = _root if _root else self
            self._read()

        def _read(self):
            self.len_payload = VlqBase128Be(self._io)
            self._raw_payload = self._io.read_bytes(self.len_payload.value)
            io = KaitaiStream(BytesIO(self._raw_payload))
            self.payload = self._root.CellPayload(io, self, self._root)


    class Serials(KaitaiStruct):
        def __init__(self, _io, _parent=None, _root=None):
            self._io = _io
            self._parent = _parent
            self._root = _root if _root else self
            self._read()

        def _read(self):
            self.entries = []
            i = 0
            while not self._io.is_eof():
                self.entries.append(VlqBase128Be(self._io))
                i += 1



    class CellTableLeaf(KaitaiStruct):
        """
        .. seealso::
           Source - https://www.sqlite.org/fileformat.html#b_tree_pages
        """
        def __init__(self, _io, _parent=None, _root=None):
            self._io = _io
            self._parent = _parent
            self._root = _root if _root else self
            self._read()

        def _read(self):
            self.len_payload = VlqBase128Be(self._io)
            self.row_id = VlqBase128Be(self._io)
            self._raw_payload = self._io.read_bytes(self.len_payload.value)
            io = KaitaiStream(BytesIO(self._raw_payload))
            self.payload = self._root.CellPayload(io, self, self._root)


    class CellPayload(KaitaiStruct):
        """
        .. seealso::
           Source - https://sqlite.org/fileformat2.html#record_format
        """
        def __init__(self, _io, _parent=None, _root=None):
            self._io = _io
            self._parent = _parent
            self._root = _root if _root else self
            self._read()

        def _read(self):
            self.len_header_and_len = VlqBase128Be(self._io)
            self._raw_column_serials = self._io.read_bytes((self.len_header_and_len.value - 1))
            io = KaitaiStream(BytesIO(self._raw_column_serials))
            self.column_serials = self._root.Serials(io, self, self._root)
            self.column_contents = [None] * (len(self.column_serials.entries))
            for i in range(len(self.column_serials.entries)):
                self.column_contents[i] = self._root.ColumnContent(self.column_serials.entries[i], self._io, self, self._root)



    class CellTableInterior(KaitaiStruct):
        """
        .. seealso::
           Source - https://www.sqlite.org/fileformat.html#b_tree_pages
        """
        def __init__(self, _io, _parent=None, _root=None):
            self._io = _io
            self._parent = _parent
            self._root = _root if _root else self
            self._read()

        def _read(self):
            self.left_child_page = self._io.read_u4be()
            self.row_id = VlqBase128Be(self._io)


    class CellIndexInterior(KaitaiStruct):
        """
        .. seealso::
           Source - https://www.sqlite.org/fileformat.html#b_tree_pages
        """
        def __init__(self, _io, _parent=None, _root=None):
            self._io = _io
            self._parent = _parent
            self._root = _root if _root else self
            self._read()

        def _read(self):
            self.left_child_page = self._io.read_u4be()
            self.len_payload = VlqBase128Be(self._io)
            self._raw_payload = self._io.read_bytes(self.len_payload.value)
            io = KaitaiStream(BytesIO(self._raw_payload))
            self.payload = self._root.CellPayload(io, self, self._root)


    class ColumnContent(KaitaiStruct):
        def __init__(self, ser, _io, _parent=None, _root=None):
            self._io = _io
            self._parent = _parent
            self._root = _root if _root else self
            self.ser = ser
            self._read()

        def _read(self):
            if  ((self.serial_type.code.value >= 1) and (self.serial_type.code.value <= 6)) :
                _on = self.serial_type.code.value
                if _on == 4:
                    self.as_int = self._io.read_u4be()
                elif _on == 6:
                    self.as_int = self._io.read_u8be()
                elif _on == 1:
                    self.as_int = self._io.read_u1()
                elif _on == 3:
                    self.as_int = self._io.read_bits_int(24)
                elif _on == 5:
                    self.as_int = self._io.read_bits_int(48)
                elif _on == 2:
                    self.as_int = self._io.read_u2be()

            if self.serial_type.code.value == 7:
                self.as_float = self._io.read_f8be()

            if self.serial_type.is_blob:
                self.as_blob = self._io.read_bytes(self.serial_type.len_content)

            self.as_str = (self._io.read_bytes(self.serial_type.len_content)).decode(u"UTF-8")

        @property
        def serial_type(self):
            if hasattr(self, '_m_serial_type'):
                return self._m_serial_type if hasattr(self, '_m_serial_type') else None

            self._m_serial_type = self.ser
            return self._m_serial_type if hasattr(self, '_m_serial_type') else None


    class RefCell(KaitaiStruct):
        def __init__(self, _io, _parent=None, _root=None):
            self._io = _io
            self._parent = _parent
            self._root = _root if _root else self
            self._read()

        def _read(self):
            self.ofs_body = self._io.read_u2be()

        @property
        def body(self):
            if hasattr(self, '_m_body'):
                return self._m_body if hasattr(self, '_m_body') else None

            _pos = self._io.pos()
            self._io.seek(self.ofs_body)
            _on = self._parent.page_type
            if _on == 13:
                self._m_body = self._root.CellTableLeaf(self._io, self, self._root)
            elif _on == 5:
                self._m_body = self._root.CellTableInterior(self._io, self, self._root)
            elif _on == 10:
                self._m_body = self._root.CellIndexLeaf(self._io, self, self._root)
            elif _on == 2:
                self._m_body = self._root.CellIndexInterior(self._io, self, self._root)
            self._io.seek(_pos)
            return self._m_body if hasattr(self, '_m_body') else None


    @property
    def len_page(self):
        if hasattr(self, '_m_len_page'):
            return self._m_len_page if hasattr(self, '_m_len_page') else None

        self._m_len_page = (65536 if self.len_page_mod == 1 else self.len_page_mod)
        return self._m_len_page if hasattr(self, '_m_len_page') else None