On Sun, Apr 6, 2014 at 4:01 PM, <to...@acm.org> wrote:
> I haven't figured out how to load a blob (e.g., image) from the shell. I
> would think there should be something like this but can't find anything:
>
> insert into table values(file('path_to_binary_file'));
>
> Are blobs only loadable by using SQLite from C?
>
> Any ideas?
>
At http://www.sqlite.org/sar there is a utility program that generates an
"SQLite Archive", similar to a ZIP archive but using SQLite as the file
format instead of the ZIP format. You can use that utility (with the
just-added "-n" option to disable compression) to load one or more images
into a database. Then in the shell, transfer those images out of the "SAR"
table where the "sar" utility puts them and into the field and table of
your choice. Use the reverse procedures to extract the BLOBs. This is
more work (more commands) but has the advantage of being able to load many
thousands of BLOBs all at once, instead of one at a time. The "sar"
utility works on unix. I have made no effort to make it work on Windows,
but I will accept patches if that is important to you.
Larray Brasfield's extention to shell.c to support ".blobextract" and
".blobreplace" commands apparently uses the incremental BLOB I/O interface
to avoid the need to load entire images into memory. ("sar" does not do
this, btw. It loads each image into memory.) That is nice, but on a
modern workstation with many GB of RAM, is it really necessary? Maybe in
some obscure cases. But in the common case of a smaller BLOB (a few
megabytes) I think custom functions would work better:
INSERT INTO sometable(x) VALUES(fromfile('data/myblob.gif'));
UPDATE sometable SET x=fromfile('data/myblob.gif') WHERE rowid=123;
SELECT tofile('data/blob-out.gif', x) FROM sometable WHERE rowid=123;
-- Extract all blobs:
SELECT tofile('data/blob-out-'||rowid||'.gif', x) FROM sometable;
I'm open to the possibility of adding fromfile() and tofile() as extension
functions in shell.c. Maybe tomorrow sometime.
Another idea is to create a virtual table that wraps the filesystem:
CREATE VIRTUAL TABLE temp.fs AS fs;
INSERT INTO sometable(x) SELECT content FROM fs WHERE
name='data/myblob.gif';
UPDATE sometable SET x=(SELECT content FROM fs WHERE
name='data/myblob.gif)
WHERE rowid=123;
REPLACE INTO fs(name, content) SELECT 'data/blob-out.gif', x FROM
sometable
WHERE rowid=123;
REPLACE INTO fs(name, content SELECT 'data/blob-out'||rowid||'.gif, x
FROM sometable;
The virtual table is potentially a much more powerful abstraction, but as
you can see from the examples above, it requires a little more work to
actually use.
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
