On Thu, 15 Jul 2004, Paul Malcher wrote:
>Dennis Volodomanov wrote:
>
>>Hello all,
>>
>>Can someone please tell me if there's any limit on the size of data
>>stored in one field (BLOB) in SQLite3?
>>
>>
>Hi, I spent all day yesterday picking through SQLite3 docs, trying to
>solved a problem that turned out was my own fault. I never saw any
>mention of 16 MB Blob limit. Either way I intend to find out , I'm gonna
>lay some serious abuse on SQLite today and see what can and cannot
>handle. I'll check the docs again maybe I missed it either way I'll let
>you know what I find out.
If you don't mind splitting a file, you could store it in a schema like
this:
create table files {
name text,
id integer
};
create table file_frags {
id integer,
offset,
data blob
};
create index file_frags_idx on file_frags(id,offset);
Then split the file into, for example, 1k-4k fragments. Retrieving the
file is then a case of:
select frags.id, frags.offset, frags.data
fromfile_frags as frags,files
where frags.id = files.id and name = 'somefile';
You can then reconstruct the file from each row. This'll allow you to
store arbitrary sized files without the performance problems of the
overflow pages and memory usage. You may want to store the blob size for
each fragment.
There, you have your own mini-XFS filesystem:) Makes handling files a bit
more work, but you'll have all the ACID transaction support you won't get
with an external file store.
Christian
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
