comparing a blob with a file (samefilecontent function proposal)
In one application I am using sqlite database as file container
I found very convenient to have a new function that permits comparing
the contents of
a blob and a file directly.
I've coded that function and it is tested successfully
So this is my proposal for adding this new function in sqlite (shell) or
just as an idea for someone who might be interested on it.
The function is attached. I just copy here the first documentation
// a new built function "samefilecontent(filename, blobfield)" in slite
// The useful SQL functions "readfile" and "writefile"
// allow transfering files into blobs and viceversa.
// Having files in blob columns allows file comparation
// in sql queries by simply comparing values of its blob columns.
// But in order to do so, we need that both files (blob values) are
// loaded in the database.
// A new function to compare a blob value directly with the content
// of a file without having to load the second one into the database
// may be convenient in many applications.
// For example, we want to know if the content of a file is already
// in the database and only if not found loading it into the database.
// So using "samefilecontent" function we can avoid expensive disc
// specially if the files are big.
// EXAMPLE: a table fileData (id, size, fcontent) a query to search for
// a specific file 'mybigfile.mpg' of size XXXX in the database could be
// SELECT id FROM fileData WHERE size+0 = XXXX AND