Hi Richard,
'SAR' looks like a great utility!
However, on production *nix machines, the path to the SQLite 'sar'
will probably have to be absolute, or else the native 'sar' (System
Activity Reporter) will run instead. Perhaps add a caveat to the docs
for SQLite's 'sar?'
Keith
On Sun, Apr 6, 2014 at 7:43 PM, Richard Hipp <d...@sqlite.org> wrote:
> 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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
