Thoughts on BLOBs in SQLite

Alejandro Tejada capellan2000 at gmail.com
Wed Aug 23 22:07:49 EDT 2017


Glen Bojsza wrote:

 > I was looking for feedback on whether it is better to store images as
 > BLOBs in an SQLite database for a LC app or store paths to the images
 > in the SQLite database and the images in a separate folder.

After reading this thread, I searched for "SQLite and rsync"
to learn if rsync could be used with SQLite, but
SQLite provides an unexpected surprise (or feature
if you prefer to call it in this way):

https://www.sqlite.org/faq.html#q12

"When you delete information from an SQLite database, the unused disk space
is added to an internal "free-list" and is reused the next time you insert
data. The disk space is not lost. But neither is it returned to the
operating system. If you delete a lot of data and want to shrink the
database file, run the VACUUM command. VACUUM will reconstruct the database
from scratch. This will leave the database with an empty free-list and a
file that is minimal in size. Note, however, that the VACUUM can take some
time to run and it can use up to twice as much temporary disk space as the
original file while it is running. An alternative to using the VACUUM
command is auto-vacuum mode, enabled using the auto_vacuum pragma."

This SQLite feature brings memories of HyperCard stacks:
http://pfhyper.com/oldsite/hcfaq/hcfaq2.html

"Each time you delete a card, background, field, or button, the space
  it occupied stays in the stack as unusable space called free space.
  As you work on a stack, it can accumulate a substantial amount of
  free space -- and the more free space a stack has, the slower it
  runs and the larger it is. There's also a better chance that your
  stack will become corrupted (meaning unusable) if you let the free
  space get out of hand."

Al



More information about the Use-livecode mailing list