[GLLUG] BLOBs in Databases

[Jason L. Froebe]

When using blobs and databases,  store them in the database only when
forced to.  Databases are inefficient in retrieving and storing blobs.  In
the database you are better off storing the relative path with respect to
your ftp or Web server.

If you store the files on zfs,  btrfs or similar,  you can take advantage
of snapshots and such.

I've been a dba for about twenty years professionally.

Jason
On Feb 2, 2015 4:47 PM, "Clay Dowling" <clay at lazarusid.com> wrote:

> Kami's usage matches my own. Filesystem management of data can be a little
> involved, so a lot of people put that data in a blob. The other fields are
> there to provide as lookup and indexing information.
> On Feb 2, 2015 2:45 PM, "Kami Vaniea" <kami.vaniea at gmail.com> wrote:
>
>> I occasionally use BLOBs in my work, though I tend to use MySQL over
>> SQLite. For example, I once built a site that would do a kind of email
>> based version repository. So you would email the system your doc file,
>> and CC everybody who you wanted to view it. The system would then store
>> the doc file, along with all future emailed doc files in one easily
>> found place. Managing all that via the file system was annoying, and
>> security problematic, so we threw the doc files into BLOBs. When the
>> user wanted their file back the system would just read the contents of
>> the BLOB back to them like it was a regular file.
>>
>> I've also done research projects where we automatically screen captured
>> websites and needed to store the captures somewhere with a pile of
>> metadata. So again, we used BLOBs. Other projects I've worked on needed
>> to store binaries of various things. For example, web scraping projects
>> that wanted to store the binaries of free apps so they could be analyzed
>> in the future.
>>
>> I imagine BLOBs are also an excellent place to put things you don't want
>> MySQL getting smart about. I've had way too many issues in the past with
>> UTF8/16 text getting chewed up by either MySQL or whatever language I'm
>> using. Putting data in a BLOB is a good way to flag "here be dragons" to
>> the database so it doesn't try and index it or "improve" it in any way.
>>
>> I've never really tried reading the BLOB data directly from the DB.
>> Normally I just use the DB the same way I would use the file system and
>> code the application I'm writing to visualize whatever the binary is.
>>
>> - Kami
>>
>>
>> On 02/02/2015 02:17 PM, Chick Tower wrote:
>> > I've been doing a little research into SQLite, and they have a storage
>> > class (they say it's slightly more general than a datatype) for binary
>> > large objects (BLOB).  You can store photo images, or I suppose even
>> > executables, in the database.  (I assume the same is true for other
>> > databases.)  My question is why?  You can't view an image with SQLite.
>> > How do you even get an image out of a database to view it?
>> >
>> >
>> >
>> > Perhaps I'm wrong in the intended use of the BLOB storage class.  It
>> > says on the SQLite website "The value is a blob of data, stored exactly
>> > as it was input."  So maybe it's intended for something other than
>> > images, but, if so, what?  I still wonder how you extract a BLOB from a
>> > database in order to do anything with it.  Does anyone know what to do
>> > with BLOBs?
>> >
>> >
>> >
>>
>>
>> _______________________________________________
>> linux-user mailing list
>> linux-user at egr.msu.edu
>> http://mailman.egr.msu.edu/mailman/listinfo/linux-user
>>
>
> _______________________________________________
> linux-user mailing list
> linux-user at egr.msu.edu
> http://mailman.egr.msu.edu/mailman/listinfo/linux-user
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.egr.msu.edu/mailman/public/linux-user/attachments/20150202/7076706d/attachment.html>