On 3/25/17 6:52 PM, petern wrote:
I would like to construct a SQLite database for one writer and one or more
reader connections. The writer will be updating various data tables which
occasionally trigger one or more row inserts in a command table named
'cmd'. This command table is being polled by the readers for new commands.
To make the example more concrete, suppose the following DDL defines the
command table:
CREATE TABLE cmd(opcode TEXT, params TEXT);
Also assume each reader is in a different process which maintains its own
open db connection over which it periodically executes the following
command retrieval query,
SELECT * FROM cmd WHERE rowid>=$lastCmdRowid;
where $lastCmdRowid is a variable managed by the reader to keep track of
the last command that was seen from its point of view. [FYI yes, of course
there are other time stamp bookkeeping columns and variables that I've
omitted because they don't pertain this question.]
So finally, here is the question. Is there a SQLite API way for reader
connections to block and wait for a meaningful change, like a new row, in
the 'cmd' table instead of madly polling and using up database concurrency
resources? [Block with timeout would be even more desirable of course.]
I don't know SQLite well enough to say for certain, but to my knowledge
'blocking till something happens' isn't the sort of thing the SQL
vocabulary has. (There is the concept of a trigger, so you might be able
to get something to run when the insert happens but that is different
than blocking to something happens).
My best guess is that you want to use a real synchronization primitive
outside of SQLite sent by the writer, (or maybe you can put it into a
trigger) to hold off the reader, and then have it read the work that was
queued up.
--
Richard Damon
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
