I have the following questions regarding BLOBs in sqlite:
- Does sqlite keep track of sizes of BLOBs?
- I'm guessing that it does, but then, does the length function use it, or does it read the BLOB's content?
- If sqlite keeps track of the size of the BLOB and length doesn't use it, is the size accessible via some other functionality?
I'm asking this because I'm wondering if I should implement triggers that set BLOBs' sizes in additional columns, of if I can obtain the sizes dynamically without the performance hit of sqlite reading the BLOBs.
If you have access to the raw c api sqlite3_blob_bytes will do the job for you. If not please provide additional information.
Write a 1byte and a 10GB blob in a test database. If
length()
takes the same time for both blobs, the blob's length is probably accessed. Otherwise the blob is probably read.OR: download the source code and debug through it: http://www.sqlite.org/download.html. These are some relevant bits:
and then
You can see that the length of text data is calculated on the fly. That of blobs... well, I'm not fluent enough in C... :-)
From the source:
In other words, the blob size is in the serial, and it's length is simply "(serial_type-12)/2".
This serial is stored before the actual blob, so you don't need to read the blob to get its size.
Call sqlite3_blob_open and then sqlite3_blob_bytes to get this value.