I think this kind of problem (transfer of information between cooperating
processes) is best solved using the tools designed for inter-process
communication and not attempting to abuse a DB system designed to isolate
processes from unfinished changes.
Have the processes share a condition variable. The readers block on the
condition variable (with a timeout if desired, to guard against dropped
signals); the writer broadcasts the variable, waking up the readers which then
proceed to retrieve/process any new entries.
If you are really desperate, you could write use defined functions/virtual
table to do this over the SQL interface, e.g.
For creating a virtual table (allows keeping/querying housekeeping info about
condition variables)
CREATE VIRTUAL TABLE conditions USING posix_cond; -- allocate/connect to the
shared memory of your choice
INSERT INTO conditions (name) value ('cmd_ready'); -- create a condition
variable
Or just for creating with minimal housekeeping:
SELECT cond_init('cmd_readY');
And in both cases:
SELECT cond_wait('cmd_ready' [,<timeout>]); -- wait for condition [or timeout],
returns 1 for signal received, 0 for error/timeout
SELECT cond_broadcast('cmd_ready'); -- wake up readers
Gunter
-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von petern
Gesendet: Samstag, 25. März 2017 23:52
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Betreff: [sqlite] How does one block a reader connection?
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.]
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___________________________________________
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at
This communication (including any attachments) is intended for the use of the
intended recipient(s) only and may contain information that is confidential,
privileged or legally protected. Any unauthorized use or dissemination of this
communication is strictly prohibited. If you have received this communication
in error, please immediately notify the sender by return e-mail message and
delete all copies of the original communication. Thank you for your cooperation.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
