Slight changes made to the hash functions.
The md2 / md4 / md5 / sha / sha1 / sha256 / sha384 / sha512 now return
uppercase hex strings. This is because of the addition of functions that
return the raw binary blob of the hash value, which will cut the bytes consumed
to store that hashes in half compared to text strings.
Binary BLOB versions of the functions simply prepend "bin" to the function
name, binmd2 / binmd4 / binmd5 / binsha / binsha1 / binsha256 / binsha384 /
binsha512 and return a binary blob of the computed hash. The hexified string
was changed to uppercase so that if you apply the builtin HEX() function to a
binary hash you get the same cased string as asking for the hexified hash in
the first place, that is:
select md5('Test String') == hex(binmd5('Text String'));
returns 1;
These functions can be most useful to maintain an in-table hash of the row
contents, useful for comparing data in the table without comparing all the
columns by using triggers.
create table data ( x text, y text, hash blob);
create trigger data_hash_insert after insert on data
begin
update data set hash = binmd5(x,y) where rowid = new.rowid;
end;
create trigger data_hash_update after update of x, y on data
begin
update data set hash = binmd5(x,y) where rowid = new.rowid;
end;
insert into data values ('Keith', 'Medcalf', null);
insert into data values ('Carl', 'Medcalf', null);
select x,y,hex(hash) from data;
Keith|Medcalf|8BAA8E0DCA5D1AAF55DF456965A201FB
Carl|Medcalf|AF64B81C92305E07CD76DBFEDB4E411C
update data set x='Carl' where x = 'Keith';
select x,y,hex(hash) from data;
Carl|Medcalf|AF64B81C92305E07CD76DBFEDB4E411C
Carl|Medcalf|AF64B81C92305E07CD76DBFEDB4E411C
All the functions take an unlimited* number of parameters. Each parameter is
retrieved using the sqlite3_value_blob function and passed in turn (except when
null -- determined because the number of bytes returned is 0 -- so an empty
string '' is the same as a null) into the hashing function. Once all the
values in turn are run though the hashing function, the final hash value is
returned.
So, if you have two tables and you want to "make them the same" you can simply
compare the hash values to find the changes**:
create table data1 (pk integer primary key, x text, y text, hash blob);
create table data2 (pk integer primary key, x text, y text, hash blob);
... plus the appropriate triggers to maintain the hash on each table
begin;
delete from data2 where exists (select 1 from data1, data2 where data1.pk =
data2.pk and data1.hash != data2.hash);
insert into data2 (pk, x, y) select pk, x, y from data1 where not exists
(select 1 from data2 where data2.pk=data1.pk);
delete from data2 where not exists (select 1 from data1 where
data2.pk=data1.pk);
commit;
of course, indexes on (pk, hash) on both tables would be useful for
performance. You can combine the two deletes together using an or condition to
remove all the rows at once. I kept them separate because, conceptually, they
are two operations.
* unlimited in the sense that it uses the limits described here:
http://www.sqlite.org/limits.html
** because sqlite does not support UPDATE ... FROM ... If it did, you could use
something like
update data2 set x=data1.x, y=data1.y from data1 where data1.pk=data2.pk and
data1.hash != data2.hash;
> You can download the file at
> http://www.dessus.com/files/SQLiteExtensions.zip
I doubt these functions ever need updating again until more hash types are
added to the Crypto API.
