Jump to content
Sign in to follow this  
plastix

FileRead image/Binary Size Query

Recommended Posts

plastix

hi all

I am planning to import some files into a SQLite DB (Word docs and the odd image file)

I know that SQLite has BLOBs in addition to TEXT fields... I am trying to come up with the most size-efficient process.

When I read a file in binary mode [FileRead(file,16)] or Binary(FileRead(file,16))] the resultant data is double the original file size - can you read a file into a variable without this doubling (I guess it converts to Hex etc) ? I was hoping it would be possible to read a file as raw binary and that saving as a BLOB wouldn't result in this doubling, but I can't see how to achieve this (if it is possible at all)...

Has anyone got any ideas / worked an example that does this ? The current system successfully can save / restore binary files with the Binary(FileRead) method ok - it is the size issue I am interested in...

TIA :idea:

Share this post


Link to post
Share on other sites
jchd

I guess you're storing an ASCII hex representation instead of _SQLite_Encoding your binary data, hence the doubling in size.

Show an exerpt of your code storing/retrieving.

BTW, if you have a significant amount in your blobs and a non-trivial number of rows, storing large blobs in main tables isn't efficient. You should adapt your design in this case (it isn't not hard).


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
plastix

Thanks jchd... I was checking StringLength of fileread result... inserting into blob doesn't double size... but for some reason when I try to extract image and save, I get an 8-byte file only... ultimately, I hope to expand the record to include a few integer / text fields and the image blob - but each record will contain a BLOB - you mentioned adapting design - if possible, I'd be interested in what you mean. TIA

#include <SQLite.au3>
_SQLite_Startup (@ScriptDir & "\sqlite3.dll")

Local $h, $vData, $sFileName, $sData, $hQuery, $hRow, $sMsg

_SQLite_Open(@ScriptDir & "\image.db")
_SQLite_Exec(-1,"CREATE TABLE IF NOT EXISTS Test (data blob);")
$h = FileOpen(@ScriptDir & "\cap.png", 16)
$vData = FileRead($h) ;or Binary(FileRead($h))
$sData = _SQLite_Encode($vData)
_SQLite_Exec(-1,"INSERT INTO Test VALUES (" & $sData & ");")

_SQLite_Query(-1,"SELECT data, * FROM Test WHERE rowid=1;",$hQuery)
_SQLite_FetchData($hQuery, $hRow)
_SQLite_QueryFinalize ($hQuery)
$sFileName = FileOpen(@ScriptDir & "\image_from_db.png", 2)
FileWrite($sFileName, Binary($hRow[1]))
_SQLite_Close()
_SQLite_Shutdown()

Share this post


Link to post
Share on other sites
jchd

Here's the light:

#include <SQLite.au3>
_SQLite_Startup (@ScriptDir & "\sqlite3.dll")

Local $h, $vData, $sFileName, $sData, $hQuery, $hRow, $sMsg

_SQLite_Open(@ScriptDir & "\image.db")
_SQLite_Exec(-1,"CREATE TABLE IF NOT EXISTS Test (data blob);")

; I borrowed the image of your avatar ;-)
$h = FileOpen(@ScriptDir & "\plastix.gif", 16)
$vData = FileRead($h)
$sData = _SQLite_Encode($vData)
ConsoleWrite($vData & @LF)
_SQLite_Exec(-1,"INSERT INTO Test VALUES (" & $sData & ");")

; that statement is misleading: you request the "data" column, then all columns
;~ _SQLite_Query(-1,"SELECT data, * FROM Test WHERE rowid=1;",$hQuery)
_SQLite_Query(-1,"SELECT data FROM Test WHERE rowid=1;", $hQuery)

; read again specification of the function and especially the third parameter...
;~ _SQLite_FetchData($hQuery, $hRow)
_SQLite_FetchData($hQuery, $hRow, 1)
ConsoleWrite(Hex($hRow[0]) & @LF)
_SQLite_QueryFinalize ($hQuery)
$sFileName = FileOpen(@ScriptDir & "\image_from_db.gif", 2)
FileWrite($sFileName, $hRow[0])
_SQLite_Close()
_SQLite_Shutdown()

Now to see why storing blobs directly in main tables is most of the times wrong, realize that SQLite works with data pages for storing everything (data, index, metadata). Then imagine a table like this:

Id (pk)..... UPC.......Familly........Item.......Supplier (fk)........Size.........Color........High Definition Image......

You see that if images are large, you have no chance of having a single product in one page. When you run a full scan table on a non-indexed column, say you look for red items in underwear familly, SQLite is going to read most irrelevant data (parts of images) uselessly. If instead you split your design into two tables, one for indexed and searchable data and one for images, then full-table scans will be order of magnitude faster, simply because the engine won't have to load hundreds of Mb but only hundreds of Kb to scan for searchable columns.

This situation is specific to SQLite, due to the DB residing in a single file. Corporate-type RDBMS are using separate areas of disk (or separate disks, or even machines) to store various columns and use sophisticated tuning algorithm to maximize transaction/second rate. DBAs nonetheless have to keep an eye on efficiency storing of data.

There other good reasons for storing such blobs separately.

-) Not only text and numeric columns get closer to each other (row after row), making the search faster, but also keeping the tree balanced is much more efficient with shorter rows.

-) In most situations, blob data is much less requested than text/numeric parts of the rows. Associating a picture to employees can be good, but those images are certainly less used than other employee columns.

-) When blob data is a large part in pages, cache is full of useless parts of blobs, taking the room that should be used for more "live" data

-) Finally, if you encrypt or compress your data, keeping less often used large blobs separate can make a very large difference in encryption time and compression ratios.

That's why, except in very particular instances, significantly large blobs are best kept in a separate table. The cost of a JOIN to get the blob when needed is negligible compared to the savings made.

Sorry for delay in answering and a verbose answer.

Edited by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
plastix

:idea: many thanks. i admit i didn't look at FetchData documentation ! I was going to make a separate table for the binaries - I just need to work out how to save the binary data only (i.e rowid & binary - then get the rowid reference into the information table to link to the image - would be the fastest method)

Thanks again for guidance / solution :)

Share this post


Link to post
Share on other sites
jchd

Use a foreign key for that. That will take care of reference errors for you, without relying on you writing triggers to do that.

If you need advice, you know where to ask.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
matwachich

Great thanks for these explanations (Ami jchd!) i am making an Image bank using SQLite and i had the same problem.

PS: I was about to post in ou forum (FR) but i searched first here, so tlem wont be angry!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.