SQLite Extensions

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLite Extensions

Keith Medcalf

For anyone who is interested, I have compiled for 32-bit Windows all the extensions that are included in the SQLite3 distribution /ext/misc directory using MinGW (gcc 4.8.1) and have added a few others I have written that add useful functions, and even a few that are taken from other people -- see the source for information.  They are all statically linked, require only the standard subsystem runtime (MSVCRT) and standard windows system dlls.  There are no other external dependancies.

The modules that I wrote are:

sqlfunc - Useful running statistical calculations, windows authorization functions
sqlmath - Access to underlying math library (sin/cos/tan etc) as sql functions
sqlfcmp - functions to compare floating point numbers (default within 5 ULPs)
sqlhash - windows cryptographic hash functions (md2/md4/md5/sha/sha256/sha384/sha512)

You can download the file at http://www.dessus.com/files/SQLiteExtensions.zip

---
Life should not be a journey to the grave with the intention of arriving safely in a pretty and well preserved body, but rather to skid in broadside in a cloud of smoke, thoroughly used up, totally worn out, and loudly proclaiming "Wow! What a Ride!"
 -- Hunter S. Thompson





_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Extensions

Gert Van Assche-3
Thanks for sharing this, Keith!

2015-11-20 6:42 GMT+01:00 Keith Medcalf <[hidden email]>:

>
> For anyone who is interested, I have compiled for 32-bit Windows all the
> extensions that are included in the SQLite3 distribution /ext/misc
> directory using MinGW (gcc 4.8.1) and have added a few others I have
> written that add useful functions, and even a few that are taken from other
> people -- see the source for information.  They are all statically linked,
> require only the standard subsystem runtime (MSVCRT) and standard windows
> system dlls.  There are no other external dependancies.
>
> The modules that I wrote are:
>
> sqlfunc - Useful running statistical calculations, windows authorization
> functions
> sqlmath - Access to underlying math library (sin/cos/tan etc) as sql
> functions
> sqlfcmp - functions to compare floating point numbers (default within 5
> ULPs)
> sqlhash - windows cryptographic hash functions
> (md2/md4/md5/sha/sha256/sha384/sha512)
>
> You can download the file at
> http://www.dessus.com/files/SQLiteExtensions.zip
>
> ---
> Life should not be a journey to the grave with the intention of arriving
> safely in a pretty and well preserved body, but rather to skid in broadside
> in a cloud of smoke, thoroughly used up, totally worn out, and loudly
> proclaiming "Wow! What a Ride!"
>  -- Hunter S. Thompson
>
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Extensions

Gert Van Assche-2
In reply to this post by Keith Medcalf
Thanks for sharing this, Keith!

2015-11-20 6:42 GMT+01:00 Keith Medcalf <[hidden email]>:

>
> For anyone who is interested, I have compiled for 32-bit Windows all the
> extensions that are included in the SQLite3 distribution /ext/misc
> directory using MinGW (gcc 4.8.1) and have added a few others I have
> written that add useful functions, and even a few that are taken from other
> people -- see the source for information.  They are all statically linked,
> require only the standard subsystem runtime (MSVCRT) and standard windows
> system dlls.  There are no other external dependancies.
>
> The modules that I wrote are:
>
> sqlfunc - Useful running statistical calculations, windows authorization
> functions
> sqlmath - Access to underlying math library (sin/cos/tan etc) as sql
> functions
> sqlfcmp - functions to compare floating point numbers (default within 5
> ULPs)
> sqlhash - windows cryptographic hash functions
> (md2/md4/md5/sha/sha256/sha384/sha512)
>
> You can download the file at
> http://www.dessus.com/files/SQLiteExtensions.zip
>
> ---
> Life should not be a journey to the grave with the intention of arriving
> safely in a pretty and well preserved body, but rather to skid in broadside
> in a cloud of smoke, thoroughly used up, totally worn out, and loudly
> proclaiming "Wow! What a Ride!"
>  -- Hunter S. Thompson
>
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Extensions

David Bennett
In reply to this post by Keith Medcalf
Any chance of a GitHub etc repo for others to maybe fork?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org


-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of Keith
Medcalf
Sent: Friday, 20 November 2015 4:42 PM
To: SQLite Users ([hidden email])
<[hidden email]>
Subject: [sqlite] SQLite Extensions


For anyone who is interested, I have compiled for 32-bit Windows all the
extensions that are included in the SQLite3 distribution /ext/misc directory
using MinGW (gcc 4.8.1) and have added a few others I have written that add
useful functions, and even a few that are taken from other people -- see the
source for information.  They are all statically linked, require only the
standard subsystem runtime (MSVCRT) and standard windows system dlls.  There
are no other external dependancies.

The modules that I wrote are:

sqlfunc - Useful running statistical calculations, windows authorization
functions sqlmath - Access to underlying math library (sin/cos/tan etc) as
sql functions sqlfcmp - functions to compare floating point numbers (default
within 5 ULPs) sqlhash - windows cryptographic hash functions
(md2/md4/md5/sha/sha256/sha384/sha512)

You can download the file at
http://www.dessus.com/files/SQLiteExtensions.zip

---
Life should not be a journey to the grave with the intention of arriving
safely in a pretty and well preserved body, but rather to skid in broadside
in a cloud of smoke, thoroughly used up, totally worn out, and loudly
proclaiming "Wow! What a Ride!"
 -- Hunter S. Thompson





_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Extensions

Keith Medcalf
In reply to this post by Keith Medcalf

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.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Extensions

Gabor Grothendieck
In reply to this post by Keith Medcalf
Can you include in the distribution the precise code (make file or
.bat file, etc.) used to build these.  Thanks.

On Fri, Nov 20, 2015 at 12:42 AM, Keith Medcalf <[hidden email]> wrote:

>
> For anyone who is interested, I have compiled for 32-bit Windows all the extensions that are included in the SQLite3 distribution /ext/misc directory using MinGW (gcc 4.8.1) and have added a few others I have written that add useful functions, and even a few that are taken from other people -- see the source for information.  They are all statically linked, require only the standard subsystem runtime (MSVCRT) and standard windows system dlls.  There are no other external dependancies.
>
> The modules that I wrote are:
>
> sqlfunc - Useful running statistical calculations, windows authorization functions
> sqlmath - Access to underlying math library (sin/cos/tan etc) as sql functions
> sqlfcmp - functions to compare floating point numbers (default within 5 ULPs)
> sqlhash - windows cryptographic hash functions (md2/md4/md5/sha/sha256/sha384/sha512)
>
> You can download the file at http://www.dessus.com/files/SQLiteExtensions.zip
>
> ---
> Life should not be a journey to the grave with the intention of arriving safely in a pretty and well preserved body, but rather to skid in broadside in a cloud of smoke, thoroughly used up, totally worn out, and loudly proclaiming "Wow! What a Ride!"
>  -- Hunter S. Thompson
>
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite Extensions

Keith Medcalf

On Friday, 20 November, 2015 07:55, Gabor Grothendieck <[hidden email]> said:

> Can you include in the distribution the precise code (make file or
> .bat file, etc.) used to build these.  Thanks.

Done.  The zip now contains a MakeExtGCC to compile with MinGW and a MakeExtMS to compile with MSVC

You just need to unzip the file to a directory, and plop in the sqlite3.h and sqlite3ext.h and run the appropriate CMD file (you may need to make some changes to the GCC file for the path to MinGW ...
 

> On Fri, Nov 20, 2015 at 12:42 AM, Keith Medcalf <[hidden email]>
> wrote:
> >
> > For anyone who is interested, I have compiled for 32-bit Windows all the
> extensions that are included in the SQLite3 distribution /ext/misc
> directory using MinGW (gcc 4.8.1) and have added a few others I have
> written that add useful functions, and even a few that are taken from
> other people -- see the source for information.  They are all statically
> linked, require only the standard subsystem runtime (MSVCRT) and standard
> windows system dlls.  There are no other external dependancies.
> >
> > The modules that I wrote are:
> >
> > sqlfunc - Useful running statistical calculations, windows authorization
> functions
> > sqlmath - Access to underlying math library (sin/cos/tan etc) as sql
> functions
> > sqlfcmp - functions to compare floating point numbers (default within 5
> ULPs)
> > sqlhash - windows cryptographic hash functions
> (md2/md4/md5/sha/sha256/sha384/sha512)
> >
> > You can download the file at
> http://www.dessus.com/files/SQLiteExtensions.zip
> >
> > ---
> > Life should not be a journey to the grave with the intention of arriving
> safely in a pretty and well preserved body, but rather to skid in
> broadside in a cloud of smoke, thoroughly used up, totally worn out, and
> loudly proclaiming "Wow! What a Ride!"
> >  -- Hunter S. Thompson
> >
> >
> >
> >
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> --
> Statistics & Software Consulting
> GKX Group, GKX Associates Inc.
> tel: 1-877-GKX-GROUP
> email: ggrothendieck at gmail.com
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users