Meta Sqlite3

Recursive Database Stores

Sqlite3 is a versatile database that can be embedded into larger programs, used as a datastore for web servers, processing scripts, and even as a binary file format for storing configurations. It can use a file as a storage medium, or it can run a database completely in memory. We are going to recursively store a sqlite3 database inside of another sqlite3 database. The only legitimate use case I see for this is like a mini-database-as-a-service, where each database is stored as a single record in a parent database, providing compartmentalization at a low resource cost.

Why? ^{Because I can.}

What I’m about to do doesn’t seem to have many applications, and it’s hard to justify. Basically, I did it because I could.

BLOB Storage

Sqlite3 provides 5 primitive “storage classes”:

• NULL
• INTEGER
• REAL
• TEXT
• BLOB

Before we get into storing data that doesn’t qualify as any of these, it’s important to understand that the sqlite backend itself only supports these types. That’s why, if we want to store something that is not one of these, we have to convert it first. For some simple classes, it may be possible to convert to an integer or to text. For example, datetimes could plausibly be converted to either integer or text. A complex number could be converted to text. However, for more obscure or complicated datatypes, it is more practical to convert to a binary format and store it as a blob. When the object is retrieved, it can be converted back to the native datatype. For example, a numpy array can be converted to binary using numpy.ndarray.dump(file), and loaded with numpy.load(file). Python objects can be “pickled” into byte arrays using pickle.dump(obj,file) and loaded back with pickle.load(file).

Our goal is to save a sqlite database into a BLOB object. To do that, we need a way to dump the sqlite database into a bytestring. sqlite3.Connection objects profide an iterdump() method that dumps lines of sql script that will recreate the database when run against a fresh DB using dest.executescript(sqldata.decode('utf-8')).

Adapters and Converters

Sqlite3 exposes the adapter and converter interfaces through which callbacks can be registered for custom datatypes when storing them and retrieving them from a database. We are going to be passing the sqlite3.Connection type to a sql insert statement, so first we write two functions, adapt_sqlite3(con), and convert_sqlite3(sqldata). adapt_sqlite3(con) takes in a connection, and using iterdump() returns a binary blob of sql statements. convert_sqlite3(sqldata) is called when retrieving the blob from the database. It takes in that binary blob, and returns a new sqlite3.Connection object after recreating the database. Clearly, this presents some limitations to this strategy: every time you retrieve a database from a row in the parent database, the child database is not simply loaded from memory, but is created as an empty database and filled with the old data. Because of the nature of transactional databases, the databases retrieved from storage are not modified in-place, but have to be re-saved if they are updated. Some work would need to be done to make these more than just effectively read-only meta-databases.

Procedure

#!/usr/bin/env python3
import sqlite3
import io

def adapt_sqlite3(con):
    '''
    Called when inserting a sqlite3 db connection into a sqlite3 db
    Takes in a sqlite3.Connection, and returns a binary dump of the db
    '''
    out = io.BytesIO()
    for line in con.iterdump():
        out.write("{}\n".format(line).encode('utf-8'))
    out.flush()
    out.seek(0)
    return sqlite3.Binary(out.read())

def convert_sqlite3(sqldata):
    '''
    Called when retrieving a sqlite3 db connection from a sqlite3 db
    Takes in a binary dump of the stored db record, and returns a connection after
    regenerating the stored db
    '''
    dest = sqlite3.connect(":memory:")
    dest.executescript(sqldata.decode('utf-8'))
    return dest

sqlite3.register_adapter(sqlite3.Connection, adapt_sqlite3)
sqlite3.register_converter("sql3", convert_sqlite3)

# sqlite3 DBs to be inserted into parent DB
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
cur = con.cursor()
cur.execute("create table test(i integer, j integer)")
cur.execute("insert into test(i, j) values (?, ?)", (1, 2))
cur.execute("insert into test(i, j) values (?, ?)", (3, 4))

# sqlite3 DB to store other sqlite3 DBs
conparent = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
curparent = conparent.cursor()
curparent.execute("create table test(db sql3)")

# Insert child DB into the parent DB
curparent.execute("insert into test(db) values (?)", (con, ))

# Retrieve child DB from rows of parent DB
curparent.execute("select db from test")
retrieved_db = curparent.fetchone()
retrieved_cursor = retrieved_db[0].cursor()
retrieved_cursor.execute("select i, j from test")

print("Child DB after retrieving from parent DB: ", retrieved_cursor.fetchall())

Can We Store a Database in Itself? ^{Yes, and it’s an abomination}

# sqlite3 DBs to be inserted into parent DB
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
cur = con.cursor()
cur.execute("create table test(i integer, j integer)")
cur.execute("create table dbs(db sql3)")
cur.execute("insert into test(i, j) values (?, ?)", (1, 2))
cur.execute("insert into test(i, j) values (?, ?)", (3, 4))
cur.execute("insert into dbs(db) values (?)", (con, ))

# Retrieve child DB from rows of parent DB
cur.execute("select db from dbs")
retrieved_db = cur.fetchone()
retrieved_cursor = retrieved_db[0].cursor()
retrieved_cursor.execute("select i, j from test")

print("Recursive DB after retrieving from parent DB: ", retrieved_cursor.fetchall())