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:
| blobtype | blobdata |
|---|---|
| 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:
execute( "select type, data where key=%s order by serial", key )
The ‘type’ can be “text” (plain text) or “data” (encoded binary data).