Hey, neat idea! To expand on my previous post:
CREATE TRIGGER wakeup AFTER INSERT ON cmd BEGIN SELECT
cond_broadcast('cmd_ready'); END;
-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[email protected]] Im
Auftrag von Richard Damon
Gesendet: Sonntag, 26. März 2017 03:43
An: [email protected]
Betreff: Re: [sqlite] How does one block a reader connection?
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
[email protected]
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: [email protected]
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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
