We're back after a server migration that caused effbot.org to fall over a bit harder than expected. Expect some glitches.

Storing BLOB Data in SQLITE

Design Note | March 10, 2003 | Fredrik Lundh

The SQLITE database has limited support for large binary objects (BLOBS). There’s a limitation of 1 megabyte for each row of data, and the database uses NUL bytes to separate columns in the storage.

Note: This limitation has been removed in SQLITE 3.0.

For best operation in large tables, the SQLITE author recommends keeping the row size around 250 bytes or below.

In the StorageManager component, we’re using a simple approach to be able to store binary data in the database, or on disk.

For each blob, the table contains a type field and a data field:

create table mytable (..., blobtype, blobdata)

The blob columns have the following meaning:

text Contains plain text, without NUL bytes.
data Contains encoded binary data, using %00 to represent NUL bytes, and %25 to represent a percent sign.
file Contains a file name, pointing to a file on disk.
blob Contains a reference to a separate blob table.

The ‘blobtable’ has the following columns:

create table blobtable (key, serial, type, data)

where ‘key’ is the reference field used with the “blob” blobtype, and the ‘serial’ column is used to order blob fragments. Each row contains a limited amount of data (the exact size depends on the application, but is usually 64k or so).

To get all blob fragments, use the following search:

 "select type, data where key=%s order by serial", key

The ‘type’ can be “text” (plain text) or “data” (encoded binary data).