Thoughts on BLOBs in SQLite

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Thoughts on BLOBs in SQLite

Ben Rubinstein via use-livecode
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
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Thoughts on BLOBs in SQLite

Ben Rubinstein via use-livecode
 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 <
[hidden email]> 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
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://post.spmailt.com/f/a/GkDl5ZK9T9ggzDZNb1XcAw~~/AAGp3AA~/RgRbf2uQP0EIACzK-d2tY0pXA3NwY1gEAAAAAFkGc2hhcmVkYQdoZWxsb18xYA01Mi4zOS4xODIuMjQ4QgoAABA4nlkyQ5UzUh11c2UtbGl2ZWNvZGVAbGlzdHMucnVucmV2LmNvbQlRBAAAAABENWh0dHA6Ly9saXN0cy5ydW5yZXYuY29tL21haWxtYW4vbGlzdGluZm8vdXNlLWxpdmVjb2RlRwJ7fQ~~
>
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Thoughts on BLOBs in SQLite

Ben Rubinstein via use-livecode
In reply to this post by Ben Rubinstein via use-livecode
Ahhh yes. I remember the first time Hypercard corrupted one of my stacks. It was like learning my girlfriend cheated on me for the first time. And then to discover that Hypercard was going to regularly corrupt my stacks was like learning she was a prostitute in her spare time! The disappointment was palpable.

Bob S


> On Aug 23, 2017, at 19:07 , Alejandro Tejada via use-livecode <[hidden email]> wrote:
>
> "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
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Thoughts on BLOBs in SQLite

Ben Rubinstein via use-livecode
In reply to this post by Ben Rubinstein via use-livecode
Tom Glod wrote:

    so I base encode the binary variable and then save it in a text field in
    table. no problems and no bs ever.
   

Others do this also. I'm curious from an "uber architecture" point of view, what pros there are to keeping the image data in a base encoded form in the data base versus just keeping a file.jpg on disk?

BR

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Thoughts on BLOBs in SQLite

Ben Rubinstein via use-livecode
The advantage comes if you are displaying the image through a web browser - you can put in the URL for the image and let the webview handle concurrent image loading.

I still went the other way, for other reasons, but that is one of the advantages.

The other is database file size.

Sent from my iPhone

> On Aug 24, 2017, at 10:56 AM, Sannyasin Brahmanathaswami via use-livecode <[hidden email]> wrote:
>
> Tom Glod wrote:
>
>    so I base encode the binary variable and then save it in a text field in
>    table. no problems and no bs ever.
>
>
> Others do this also. I'm curious from an "uber architecture" point of view, what pros there are to keeping the image data in a base encoded form in the data base versus just keeping a file.jpg on disk?
>
> BR
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode