Re: [sqlite] DB change notification hooks across process

[Dominguez Bonini, David]

 What is the preferred way of receiving change notifications from
 the SQLite connection ?

Just in case it helps, we also had this problem in an embedded system where 
constant polling of sqlite tables was expensive and what we did is:

Add a directory in /tmp which every process connected to sqlite can monitor 
with inotify. When a process modifies a table, it writes a file named after the 
table name to this directory. Every process that is monitoring the directory  
then receives notification of the change and can decide (from the filename) if 
it is interested in reading the table that has changed. It's quick and dirty 
but it works...

If more fine-grained notification is needed in your case, you could add whether 
the change was a delte/update/insert ( or even the whole SQL statement) in the 
file contents or as an extension to the filename.

Regards

David

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

[sqlite] Performance of sparse sqlite tables

[Dominguez Bonini, David]

Hi,

I have an application where a table is filled with elements whose primary key 
is specified at insertion, and is actually a combination of several independent 
IDs. Example:  ItemID = (id0  32) + (id1  16) + (id2).
The range covered by each ID guarantees that their sum will never exceed the 64 
bit maximum size of an sqlite primary key. The advantage of this approach is 
that a function performing a SELECT statement can pre-compute the id that needs 
to be retrieved from the database. This is what I call a sparse table, meaning 
that the table will never have more than X items, but the primary key range is 
actually much bigger than X. Sorry if my definitions are not standard, SQL is 
not my native language :)

This scheme is used because IDs are usually inserted all at once in a single 
batch, and then they have to be regularly updated over a very long time. So, 
there are relatively few INSERTS and a LOT of UPDATES and SELECTS.

I'm wondering if the advantage in search speed obtained by this ID assignment 
scheme may somehow be offset by other factors like additional memory usage, 
less efficient inserts, etc. Can anyone offer counterarguments, or recommend a 
better scheme?

Regards

David

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