> On 3 Mar 2016, at 8:16am, Eric Grange <zarglu at gmail.com> wrote: > >> All BLOBs are stored inline. The only complication comes when the total >> row length is longer than a page. > > Ok thanks! > > That is besides my original question but what is the complication in that > case? > Is the blob partially stored in the row, or is some indirection to other > pages used, with only some pointer stored in the row?
SQLite database files are split up into pages. Every page in a database is the same number of bytes long. Every page belongs to the header, or to free space, or to a specific TABLE or INDEX. SQLite stores all the values for a row together. Changing the value of any column for a row requires rewriting the entire row, and then the row entry for every index on that table which was changed. [1] Ideally a row of data would fit in a page. This simplifies the job of reading a row from disk or changing the data in a row. SQLite does this where possible. But it's possible for a row to be longer than a page. This happens especially where the row contains a long string or a long BLOB. In that case SQLite has to fetch more than one page from disk just to access the values for that row. And it's possible for a BLOB to be so long that it requires one or more pages entirely to itself. So the word 'inline' is a little tricky here because there is more than one 'line' involved. Simon. [1] Handwave a few exceptions.
