Save huge file-stream (considered about 2GB) in a Table as BLOB.

Amir · 2014-05-29 08:39:11

Hi, I need to store large-sized blobs in a local SQlite database, I'd like to know if anyone has advices or tricks for tuning and better performance gain.

Thanks in advance.

ab · 2014-05-29 11:02:40

The ORM would handle BLOB in memory, as RawByteString / TSQLRawBlob values.

You can store large-sized blobs in the SQLite3 database, using SynSQlite3.pas directly.
Use TSQLDataBase.Blob() and then access the data with the returned TSQLBlobStream instance.
This is just a wrapper around http://www.sqlite.org/c3ref/blob_open.html

Honestly, I would not use SQlite3 for storing such huge BLOBs, but plain files in a dedicated sub-folder, just below the main SQlite3 database, which will contain all "metadata".
It would be much more easy to backup the content, and work with the data.
And you could easily spread the files over several folders, drives or computers, if needed.

Amir · 2014-05-29 11:57:53

Thanks for your quick answer; Of course I also prefer to use dedicated files; but here security matters...
I've to hide the files from direct view and access !!!

I'd be thankful, if you suggest better solutions.

ab · 2014-05-29 13:25:57

What I may do:

1. mangle the file name on disk, and store both the "original" and "mangled" file name in the SQlite3 database;
2. encrypt the file content on the fly - a simple XOR within Read/Write should be enough to prevent most security leaks.

MichaelR · 2014-06-09 06:12:22

For big data blobs it always matters how it needs to be accessed later. Files like yours are either not important, or they are full of redundancy that has been ignored, or they are files like video that never belongs in a database ever anywhere.

If you are corporate and need lots and lots of big docs like this for some reason I would check out a full scale document management package like Alfresco. Do you need to access parts of this thing sometimes? Break it up. Do you have a document centric publish model going here with incremental changes? Publish each segment independently and use document references not copy/paste redundancy. Proper records management and publishing model is probably way over due. Capture the delta. If you know DDD then go look for the value objects.

Yes I know what it costs. Mostly it costs managers their brain power. Mostly it makes stupid people look stupid when you start designing this way. Sorry for their stupidity. You have put up with a lot for a long time. Now it is time to make stupid hard to do.

ab · 2014-06-09 08:04:50

You can use a versioning system like git or fossil.

Take a look at synproject versioning units like syndiff.

martin.suer · 2014-06-09 08:26:22

I agree with MichaelR. I have seen people putting db backup files in the very same db... (not for a long time of course ;-) )
If a system allows for such things, people will do.
You really should have a rather good reason if you want to put such big files in a db otherwise they should be stored elsewhere.

Last edited by martin.suer (2014-06-09 08:27:31)

mORMot Open Source

#1 2014-05-29 08:39:11

Save huge file-stream (considered about 2GB) in a Table as BLOB.

#2 2014-05-29 11:02:40

Re: Save huge file-stream (considered about 2GB) in a Table as BLOB.

#3 2014-05-29 11:57:53

Re: Save huge file-stream (considered about 2GB) in a Table as BLOB.

#4 2014-05-29 13:25:57

Re: Save huge file-stream (considered about 2GB) in a Table as BLOB.

#5 2014-06-09 06:12:22

Re: Save huge file-stream (considered about 2GB) in a Table as BLOB.

#6 2014-06-09 08:04:50

Re: Save huge file-stream (considered about 2GB) in a Table as BLOB.

#7 2014-06-09 08:26:22

Re: Save huge file-stream (considered about 2GB) in a Table as BLOB.

Board footer