R - how to react to database inserts/updates/deletes?

Question

I'm reading in data from an SQLite database table into a data.frame with R's DBI. Often (as often as every 5 secs), new records get added into the database table externally, or existing ones updated/deleted, at which point I need to propagate these changes to my data.frame.

So the question is how can I hook onto and respond to these database events in R? I don't want to have to keep querying the database every 5 secs just to make sure nothing has changed. Is there some callback mechanism at my disposal?

Metaphysic

You could save the time of the last modification with file.info( "myDB.sqlite" )$mtime and compare with the current modification time regularly. A difference then would trigger a new query, saving the most recent modification date again for further comparison. – Pritchard

@vaettchen, thanks - I was thinking that too, but this method seems too coarse for my needs: I have a number of tables in the database and updates/inserts/deletions can happen randomly across all of them. Thus a small change in any one table would trigger a change in the database file's 'time modified' stamp and cause all my tables to be marked as needing to be reread - this is too inefficient. – Metaphysic

Answer 1

If you have access to the C code that is writing your SQL data, then you can implement a callback:

http://www.sqlite.org/c3ref/update_hook.html

and then in your callback function you could update the timestamp of a file if the table being modified is one your R code cares about. Then your R code checks the timestamp of that file, and if its changed, only then does it need to query the SQLite database.

Now I don't know if you could add a callback to the SQLite connection held by R and expect to get a callback if another SQLite connection/process changes the database table. I doubt it, I suspect the callbacks are only triggered if the connection they are registered with does the update, because otherwise all sorts of asynchronous things happen, and there's no event handler.

Another idea is to use triggers to update a database table of modification times. Define triggers on all tables you care about so that they update a row in a "last modified" table. Then use the file modification time to check for any change to the database, and then your R only has to query the "last modified" table to see what specific table has changed since last check.

Appeasement