[sqlite] SQLite Extensions

[David Bennett]

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: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Keith
Medcalf
Sent: Friday, 20 November 2015 4:42 PM
To: SQLite Users (sqlite-users at mailinglists.sqlite.org)

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
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite Extensions

[Keith Medcalf]

On Friday, 20 November, 2015 07:55, Gabor Grothendieck  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 
> 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
> > sqlite-users at mailinglists.sqlite.org
> > 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
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite Extensions

[Gert Van Assche]

Thanks for sharing this, Keith!

2015-11-20 6:42 GMT+01:00 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
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] SQLite Extensions

[Gabor Grothendieck]

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  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
> sqlite-users at mailinglists.sqlite.org
> 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] SQLite Extensions

[Gert Van Assche]

Thanks for sharing this, Keith!

2015-11-20 6:42 GMT+01:00 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
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] SQLite Extensions

[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] 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] SQLite extensions repository

[Marco Bambini]

Hello,
I am wondering if there is there a place that collect all available sqlite 
extensions.

Please let me know.
Thanks.
--
Marco Bambini
http://www.sqlabs.com
http://twitter.com/sqlabs







___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite extensions repository

[Alexey Pechnikov]

Developers support a private repositories usually. As example:

http://sqlite.mobigroup.ru/wiki?name=extensions

http://sqlite.mobigroup.ru/wiki?name=utils

2012/2/12 Marco Bambini :
> Hello,
> I am wondering if there is there a place that collect all available sqlite 
> extensions.
>
> Please let me know.
> Thanks.
> --
> Marco Bambini
> http://www.sqlabs.com
> http://twitter.com/sqlabs
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite extensions repository

[Luuk]

On 12-02-2012 19:48, Marco Bambini wrote:
> Hello,
> I am wondering if there is there a place that collect all available sqlite 
> extensions.
> 
> Please let me know.
> Thanks.

http://www.sqlite.org/contrib
looks like a good place for it ;)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite extensions repository

[Marco Bambini]

Hello,
I am wondering if there is there a place that collect all available sqlite 
extensions.

Please let me know.
Thanks.
--
Marco Bambini
http://www.sqlabs.com
http://twitter.com/sqlabs

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users