On 10/29/15, Dominique Devienne <ddevienne at gmail.com> wrote:
>
> I've discussed blobs a few times already :). Especially the fact you can't
> have transactional and incremental updates of blobs (i.e. the transaction
> "copies" only the blob pages actually modified).
Large blobs are transactional when they are stored in SQLite tables.
Perhaps you are talking about storing BLOBs in separate files and then
just storing the filename in the SQLite table?
If you do have large BLOBs or strings, SQLite handles this best if the
large blob/string is stored in a table by itself and then linked into
other tables using an integer primary key. For example:
CREATE TABLE BigBlobTab (
blobid INTEGER PRIMARY KEY,
content BLOB -- big BLOB or text field.
);
CREATE TABLE OtherStuff (
field1 VARCHAR(10),
field2 FLOAT,
field3 BOOLEAN,
blobid INT REFERENCES BigBlobTab
);
This technique work similarly to storing BLOBs in separate files, in
that it does not mix the BLOB content with the rest of the row, making
the row faster to search and update. But it has the advantage over
separate storage that BLOB changes are transactional and all content
is stored in a single file on disk.
This is how the Fossil VCS (https://www.fossil-scm.org/) stores content, fwiw.
--
D. Richard Hipp
drh at sqlite.org
