Thoughts on BLOBs in SQLite
tom at makeshyft.com
Wed Aug 23 22:20:22 EDT 2017
I rely very much on SQLite and I do not use & store blobs at all.
reason 1 is that it always required special handling which is a red flag
reason 2 when I googled it ..it was all problems.
so I base encode the binary variable and then save it in a text field in
table. no problems and no bs ever.
i don't know if thats at all similar to your use case.......until I am
shown otherwise ..... no blobs for me.
On Wed, Aug 23, 2017 at 10:07 PM, Alejandro Tejada via use-livecode <
use-livecode at lists.runrev.com> wrote:
> 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):
> "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:
> "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."
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
More information about the Use-livecode