Thoughts on BLOBs in SQLite

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

Thoughts on BLOBs in SQLite

Ben Rubinstein via use-livecode
Hello,

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.

Any help in understanding possible performance and memory issues is what I
am looking for.

Can I assume that any issues would be the same for both Android and iOS?

thanks,

Glen
_______________________________________________
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
Better is a relative term. Do you need to occasionally edit the images? Will the database continue to grow over time? My instinct is to keep files as files on the disk, because managing them is not dependent on access to a database. However, having the images universally accessible in a multiuser application is much easier if you store them in a database because then you wouldn't have to deal with maintaining images on a users file system.

I keep the binary equivalent of blank PDF forms in a binary blob in mySQL. This way I can create new PDF forms on the fly without much fuss. I do NOT however, keep the FILLED forms in the database (although I've been tempted to) because these will keep multiplying over the life of the application, and I am worried about database bloat. Backups get larger and take longer, and eventually this would pose a scalability problem if the app was used extensively by a whole lot of people.

Bob S


> On Aug 23, 2017, at 09:11 , Glen Bojsza via use-livecode <[hidden email]> wrote:
>
> Hello,
>
> 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.
>
> Any help in understanding possible performance and memory issues is what I
> am looking for.
>
> Can I assume that any issues would be the same for both Android and iOS?
>
> thanks,
>
> Glen


_______________________________________________
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
You can also store multiple images in a single blob, which can be helpful.

Sent from my iPhone

> On Aug 23, 2017, at 12:25 PM, Bob Sneidar via use-livecode <[hidden email]> wrote:
>
> Better is a relative term. Do you need to occasionally edit the images? Will the database continue to grow over time? My instinct is to keep files as files on the disk, because managing them is not dependent on access to a database. However, having the images universally accessible in a multiuser application is much easier if you store them in a database because then you wouldn't have to deal with maintaining images on a users file system.
>
> I keep the binary equivalent of blank PDF forms in a binary blob in mySQL. This way I can create new PDF forms on the fly without much fuss. I do NOT however, keep the FILLED forms in the database (although I've been tempted to) because these will keep multiplying over the life of the application, and I am worried about database bloat. Backups get larger and take longer, and eventually this would pose a scalability problem if the app was used extensively by a whole lot of people.
>
> Bob S
>
>
>> On Aug 23, 2017, at 09:11 , Glen Bojsza via use-livecode <[hidden email]> wrote:
>>
>> Hello,
>>
>> 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.
>>
>> Any help in understanding possible performance and memory issues is what I
>> am looking for.
>>
>> Can I assume that any issues would be the same for both Android and iOS?
>>
>> thanks,
>>
>> Glen
>
>
> _______________________________________________
> 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
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
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.

This is a religious issue in some circles, and there may be technical
details beyond my grasp which merit such fervor.

But in my simple world I tend to store images and other BLOBs outside of
the DB, leaving the DB itself for those things it does uniquely well:
efficient storage of elements smaller than the block size of the host
file system, and efficient indexing and retrieval of elements based on
their content.

--
  Richard Gaskin
  Fourth World Systems
  Software Design and Development for the Desktop, Mobile, and the Web
  ____________________________________________________________________
  [hidden email]                http://www.FourthWorld.com

_______________________________________________
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
My databases will be fixed in their content (basically they are going to be
different test engines for students) so the images will not be edited nor
changed.

I am thinking that this may help me avoid any future issues that may arise
if different platforms have different directory structures and also for
maintenance ... a single database file is easy to deliver and easy to
delete.

So my next question would be does someone have a simple example stack in
inserting an image and retrieving the image in a LC stack?

Should the image be encoded prior to inserting or is native format fine?

Glen

On Wed, Aug 23, 2017 at 12:25 PM, Bob Sneidar via use-livecode <
[hidden email]> wrote:

> Better is a relative term. Do you need to occasionally edit the images?
> Will the database continue to grow over time? My instinct is to keep files
> as files on the disk, because managing them is not dependent on access to a
> database. However, having the images universally accessible in a multiuser
> application is much easier if you store them in a database because then you
> wouldn't have to deal with maintaining images on a users file system.
>
> I keep the binary equivalent of blank PDF forms in a binary blob in mySQL.
> This way I can create new PDF forms on the fly without much fuss. I do NOT
> however, keep the FILLED forms in the database (although I've been tempted
> to) because these will keep multiplying over the life of the application,
> and I am worried about database bloat. Backups get larger and take longer,
> and eventually this would pose a scalability problem if the app was used
> extensively by a whole lot of people.
>
> Bob S
>
>
_______________________________________________
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
For Augmented Earth, the images are base 64 encoded. This is needed for transmitting. Also, I can store a variable number of images in a single blob cell, using a character outside of the base64 range as a delimiter between encoded images.

Sent from my iPhone

> On Aug 23, 2017, at 12:48 PM, Glen Bojsza via use-livecode <[hidden email]> wrote:
>
> My databases will be fixed in their content (basically they are going to be
> different test engines for students) so the images will not be edited nor
> changed.
>
> I am thinking that this may help me avoid any future issues that may arise
> if different platforms have different directory structures and also for
> maintenance ... a single database file is easy to deliver and easy to
> delete.
>
> So my next question would be does someone have a simple example stack in
> inserting an image and retrieving the image in a LC stack?
>
> Should the image be encoded prior to inserting or is native format fine?
>
> Glen
>
> On Wed, Aug 23, 2017 at 12:25 PM, Bob Sneidar via use-livecode <
> [hidden email]> wrote:
>
>> Better is a relative term. Do you need to occasionally edit the images?
>> Will the database continue to grow over time? My instinct is to keep files
>> as files on the disk, because managing them is not dependent on access to a
>> database. However, having the images universally accessible in a multiuser
>> application is much easier if you store them in a database because then you
>> wouldn't have to deal with maintaining images on a users file system.
>>
>> I keep the binary equivalent of blank PDF forms in a binary blob in mySQL.
>> This way I can create new PDF forms on the fly without much fuss. I do NOT
>> however, keep the FILLED forms in the database (although I've been tempted
>> to) because these will keep multiplying over the life of the application,
>> and I am worried about database bloat. Backups get larger and take longer,
>> and eventually this would pose a scalability problem if the app was used
>> extensively by a whole lot of people.
>>
>> Bob S
>>
>>
> _______________________________________________
> 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
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
Hi Glen,

First, as Richard G. mention, internet wars have been fought over this issue… to me it’s your choice.

I’ve done both and both have their ups and downs.

For mobile, I’ve sided with keeping it in the DB. It keeps everything together and the way I’m querying the DB, I’m already pulling the data and the image just comes along with it. My experience is that it’s very fast.

As does Jonathan L, I store my images as base64 encoded so they can be transmitted over the internet as needed and decode them on the fly when needed by the app. The added benefit is you can store them in a TXT field in SQLite. LC is very fast at decoding them.

As for growing in size, especially for mobile, something is going to grow either way… wether it’s the files on the file system or the DB file, as you add, it’s going to grow.

Sorry, I don’t have anything resembling a example stack to share, but in keeping the above in mind, it’s not too hard to implement.

HTH,

Steve MacLean

 

> On Aug 23, 2017, at 12:11 PM, Glen Bojsza via use-livecode <[hidden email]> wrote:
>
> Hello,
>
> 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.
>
> Any help in understanding possible performance and memory issues is what I
> am looking for.
>
> Can I assume that any issues would be the same for both Android and iOS?
>
> thanks,
>
> Glen
> _______________________________________________
> 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
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

> On Aug 23, 2017, at 09:36 , Richard Gaskin 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.
>
> This is a religious issue in some circles, and there may be technical details beyond my grasp which merit such fervor.

Dam! Now I suppose I'm expected to tithe!!!

Bob S



_______________________________________________
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
Thanks Steve...it looks like (in my current case) that the encode / decode
SQLite text field is the easiest way to get me solution.

Glen

On Wed, Aug 23, 2017 at 1:43 PM, Stephen MacLean via use-livecode <
[hidden email]> wrote:

> Hi Glen,
>
> First, as Richard G. mention, internet wars have been fought over this
> issue… to me it’s your choice.
>
> I’ve done both and both have their ups and downs.
>
> For mobile, I’ve sided with keeping it in the DB. It keeps everything
> together and the way I’m querying the DB, I’m already pulling the data and
> the image just comes along with it. My experience is that it’s very fast.
>
> As does Jonathan L, I store my images as base64 encoded so they can be
> transmitted over the internet as needed and decode them on the fly when
> needed by the app. The added benefit is you can store them in a TXT field
> in SQLite. LC is very fast at decoding them.
>
> As for growing in size, especially for mobile, something is going to grow
> either way… wether it’s the files on the file system or the DB file, as you
> add, it’s going to grow.
>
> Sorry, I don’t have anything resembling a example stack to share, but in
> keeping the above in mind, it’s not too hard to implement.
>
> HTH,
>
> Steve MacLean
>
>
> > On Aug 23, 2017, at 12:11 PM, Glen Bojsza via use-livecode <
> [hidden email]> wrote:
> >
> > Hello,
> >
> > 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.
> >
> > Any help in understanding possible performance and memory issues is what
> I
> > am looking for.
> >
> > Can I assume that any issues would be the same for both Android and iOS?
> >
> > thanks,
> >
> > Glen
> > _______________________________________________
> > 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
>
_______________________________________________
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