Thoughts on BLOBs in SQLite

[Tom Glod]

 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):
>
> http://post.spmailt.com/f/a/rz3eU0A3l6XAzDJK4wslUw~~/AAGp3AA~/RgRbf2uQP0EIACzK-d2tY0pXA3NwY1gEAAAAAFkGc2hhcmVkYQdoZWxsb18xYA01Mi4zOS4xODIuMjQ4QgoAABA4nlkyQ5UzUh11c2UtbGl2ZWNvZGVAbGlzdHMucnVucmV2LmNvbQlRBAAAAABEI2h0dHBzOi8vd3d3LnNxbGl0ZS5vcmcvZmFxLmh0bWwjcTEyRwJ7fQ~~
>
> "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://post.spmailt.com/f/a/WFdu5G1JItlH-tHmdT_7UQ~~/AAGp3AA~/RgRbf2uQP0EIACzK-d2tY0pXA3NwY1gEAAAAAFkGc2hhcmVkYQdoZWxsb18xYA01Mi4zOS4xODIuMjQ4QgoAABA4nlkyQ5UzUh11c2UtbGl2ZWNvZGVAbGlzdHMucnVucmV2LmNvbQlRBAAAAABELGh0dHA6Ly9wZmh5cGVyLmNvbS9vbGRzaXRlL2hjZmFxL2hjZmFxMi5odG1sRwJ7fQ~~
>
> "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
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://post.spmailt.com/f/a/GkDl5ZK9T9ggzDZNb1XcAw~~/AAGp3AA~/RgRbf2uQP0EIACzK-d2tY0pXA3NwY1gEAAAAAFkGc2hhcmVkYQdoZWxsb18xYA01Mi4zOS4xODIuMjQ4QgoAABA4nlkyQ5UzUh11c2UtbGl2ZWNvZGVAbGlzdHMucnVucmV2LmNvbQlRBAAAAABENWh0dHA6Ly9saXN0cy5ydW5yZXYuY29tL21haWxtYW4vbGlzdGluZm8vdXNlLWxpdmVjb2RlRwJ7fQ~~
>