Re: [sqlite] notify all processes of database modification

[Rowan Worth]

On Mon, 10 Feb 2020 at 12:53, Simon Slavin  wrote:

> On 10 Feb 2020, at 4:26am, Rowan Worth  wrote:
>
> > See also PRAGMA data_version when it comes to polling the DB, the return
> value of which changes when another process modifies the DB. IIRC the
> implementation of this depends on a value in the DB header page, so it may
> be sufficient to only monitor the main DB file for changes.
>
> Theoretically, one monitors the database file for its "last change"
> timestamp (you might know this as the "touch timestamp").


If, as previously suggested, you're using inotify or FSEvents (or
ReadDirectoryChangesExW) you can avoid polling this metadata, although I
realised that in WAL mode the if the updated DB header page might only be
stored in the write ahead log for some period of time, in which case you
definitely need to watch both.

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

Re: [sqlite] notify all processes of database modification

[Simon Slavin]

On 10 Feb 2020, at 4:26am, Rowan Worth  wrote:

> See also PRAGMA data_version when it comes to polling the DB, the return 
> value of which changes when another process modifies the DB. IIRC the 
> implementation of this depends on a value in the DB header page, so it may be 
> sufficient to only monitor the main DB file for changes.

Theoretically, one monitors the database file for its "last change" timestamp 
(you might know this as the "touch timestamp").  In practise, some operating 
systems don't notice that this has been modified until other things have 
happened (directory info has been updated, storage driver has finished session, 
etc.).  But SQlite should always see a change in the content of the file first, 
so use the PRAGMA or manually read those bytes from the header.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] notify all processes of database modification

[Rowan Worth]

On Sat, 8 Feb 2020 at 04:02, Jens Alfke  wrote:

> > On Feb 7, 2020, at 6:23 AM, Kees Nuyt  wrote:
> >
> > Anyway, SQLite doesn't have such a mechanism by itself.
> > Maybe inotify is useful to you :
> >
> > https://en.wikipedia.org/wiki/Inotify <
> https://en.wikipedia.org/wiki/Inotify>
> > http://man7.org/linux/man-pages/man7/inotify.7.html <
> http://man7.org/linux/man-pages/man7/inotify.7.html>
>
> Or on Apple platforms, FSEvents.
>
> On any platform, you'd need to monitor both the main database and the .wal
> file.
>
> And the notification would trigger soon after a transaction began making
> changes, although the changes wouldn't be visible to you until the commit,
> so you'd probably need to start polling until you see the changes, with
> some heuristic about timing out if nothing happens for a while (e.g. if the
> transaction is aborted.)
>

See also PRAGMA data_version when it comes to polling the DB, the return
value of which changes when another process modifies the DB. IIRC the
implementation of this depends on a value in the DB header page, so it may
be sufficient to only monitor the main DB file for changes.

https://www.sqlite.org/pragma.html#pragma_data_version
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] notify all processes of database modification

[Jens Alfke]

> On Feb 7, 2020, at 6:23 AM, Kees Nuyt  wrote:
> 
> Anyway, SQLite doesn't have such a mechanism by itself.
> Maybe inotify is useful to you :
> 
> https://en.wikipedia.org/wiki/Inotify 
> http://man7.org/linux/man-pages/man7/inotify.7.html 
> 

Or on Apple platforms, FSEvents.

On any platform, you'd need to monitor both the main database and the .wal file.

And the notification would trigger soon after a transaction began making 
changes, although the changes wouldn't be visible to you until the commit, so 
you'd probably need to start polling until you see the changes, with some 
heuristic about timing out if nothing happens for a while (e.g. if the 
transaction is aborted.)

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

Re: [sqlite] notify all processes of database modification

[Kees Nuyt]

On Fri, 7 Feb 2020 00:13:00 -0700 (MST), you wrote:

> The documents are no longer available, can you kindly share it to my mail.
> I'll be extremely thankful to you. Mail is aliahmadqureshi...@gmail.com

You are probably following up a posting of about 10 years ago.
Most of us do not have that message anymore, so it is not totally clear what you
need.

Anyway, SQLite doesn't have such a mechanism by itself.
Maybe inotify is useful to you :

https://en.wikipedia.org/wiki/Inotify
http://man7.org/linux/man-pages/man7/inotify.7.html

-- 
Regards,
Kees Nuyt
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] notify all processes of database modification

[Ali Ahmad Qureshi]

The documents are no longer available, can you kindly share it to my mail.
I'll be extremely thankful to you. Mail is aliahmadqureshi...@gmail.com



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] notify all processes of database modification

[Ed Scherer]

(Arggg!!! Second try sending this; first attempt seems to have been
truncated.
If you don't see my signature at the end of this one, it got truncated
again.)

> Hi,
> I have two processes connecting to a database. Both can modify database.
When
> some process modify database, I want the other to be notified (so, it can
> reload data).
> Currently, I use unix sockets, so a process can notify all listener
process
> that something has changed. But I wonder if there's a better way to
achieve
> that.
> I've tried to a create a custom function (with sqlite3_create_function),
and
> use triggers. But unfortunately, my trigger was executed for the modifying
> process.
> So, is there a sqlitish way to have process notifications, or should I
stick
> to using unix sockets to tell other processes something has changed in the
> database.
> I'm using C++ language (C api for sqlite)
>
> regards
> arno
Howdy, Arno.

I'm a long-time lurker on this list, but until now I haven't had much to
contribute.

However... on this topic, I might finally have something to offer.

About a year ago, I was working on this very problem, and have a working
prototype of an SQLite library with a cross-process asynchronous update
notification mechanism.

When we originally encountered this problem, we tried to characterize it
as best we could and then considered a few alternative solutions
(which we numbered Option #1 through Option #5 in our documents). One of
our (formerly) internal engineering documents includes discussion of this
subject and might provide some useful context; see

http://www.innovision.com/techdocs/sqlite/PersistentDataSharingAndConcurrenc
yControl.pdf

Ultimately, we developed prototypes (i.e., enhanced SQLite libraries)
for a couple of the options (first, Option #5, and later, Option #3).

Another (formerly) internal document further expands on the requirements we
wanted to satisfy and how we went about solving (at least sufficient for a
prototype) the problem; see

http://www.innovision.com/techdocs/sqlite/SQLiteAsyncDatabaseChange.pdf

I'll warn you that the document I just referenced probably needs to be
updated to be a more accurate reflection of our latest solution to the
problem (which is now based on Option #3 rather than Option #5).

>From the perspective of a programmer, what they're seeing with the
enhanced SQLite library is a new SQLite API function:

/*
** The sqlite3_async_update_hook() interface registers a callback function
** with the [database connection] identified by the first argument
** to be invoked whenever the database has been modified in any way.
**
** The monitoring of the database occurs in a separate thread (one thread
** per monitored connection) from the thread that invoked
** sqlite3_async_update_hook().  When a database modification is detected,
** the callback function is called from the monitoring thread.  Thus,
** some thread-level synchronization mechanism (e.g., appropriate thread
** safety mode and database locking) should be used as appropriate.
**
** If the async update hook callback function pointer is NULL then the no
** update callbacks are made.
**
** If an async update hook callback function has been registered with a
** database connection at the time it is closed, the callback function
** will automatically be unregistered as part of the connection close
** operation.
**
** The second argument is a pointer to the function to invoke when the
** database is modified.
** The first argument to the callback is a copy of the third argument
** to sqlite3_async_update_hook().
** The second callback argument is a pointer to the database name.
**
** If another function was previously registered, its pArg value
** is returned.  Otherwise NULL is returned.
*/
void *sqlite3_async_update_hook(
  sqlite3*,
  void(*)(void *, const char *),
  void*
);


When I was working on the Option #5 prototype; I created a video demo.
If I did it again for Option #3, it probably wouldn't be much different.
The demo shows the mechanism working both within a single process as
well as across two processes (a Lua-based GUI prototype app, and the
sqlite3.exe command line utility, each linked against the enhanced
SQLite library). (The Lua aspect is irrelevant here; LuaSQL just happened
to be another thing we wanted to evaluate in this prototype).

The video demo is available in two formats an AVI (codec available at
http://www.techsmith.com/download/codecs.asp):

http://www.innovision.com/techdocs/sqlite/SQLiteAsyncDatabaseChangeExample3.
avi

and a lower-quality MPEG:

http://www.innovision.com/techdocs/sqlite/SQLiteAsyncDatabaseChangeExample3.
mp4


Now... I've been away from this for at least half a year, but here are
some of my thoughts at this point:

1. It appears that our desire to have cross-process update notification
is shared by others, so maybe it's time to have the SQLite community dig
into this issue more and see if we can arrive at a mutually beneficial
solution.

2. Any solution 

Re: [sqlite] notify all processes of database modification

[Ed Scherer]

> Hi,
> I have two processes connecting to a database. Both can modify database.
When
> some process modify database, I want the other to be notified (so, it can
> reload data).
> Currently, I use unix sockets, so a process can notify all listener
process
> that something has changed. But I wonder if there's a better way to
achieve
> that.
> I've tried to a create a custom function (with sqlite3_create_function),
and
> use triggers. But unfortunately, my trigger was executed for the modifying
> process.
> So, is there a sqlitish way to have process notifications, or should I
stick
> to using unix sockets to tell other processes something has changed in the
> database.
> I'm using C++ language (C api for sqlite)
>
> regards
> arno
Howdy, Arno.

I'm a long-time lurker on this list, but until now I haven't had much to
contribute.

However... on this topic, I might finally have something to offer.

About a year ago, I was working on this very problem, and have a working
prototype of an SQLite library with a cross-process asynchronous update
notification mechanism.

When we originally encountered this problem, we tried to characterize it
as best we could and then considered a few alternative solutions
(which we numbered Option #1 through Option #5 in our documents). One of
our (formerly) internal engineering documents includes discussion of this
subject and might provide some useful context; see

http://www.innovision.com/techdocs/sqlite/PersistentDataSharingAndConcurrenc
yControl.pdf

Ultimately, we developed prototypes (i.e., enhanced SQLite libraries)
for a couple of the options (first, Option #5, and later, Option #3).

Another (formerly) internal document further expands on the requirements we
wanted to satisfy and how we went about solving (at least sufficient for a
prototype) the problem; see

http://www.innovision.com/techdocs/sqlite/SQLiteAsyncDatabaseChange.pdf

I'll warn you that the document I just referenced probably needs to be
updated to be a more accurate reflection of our latest solution to the
problem (which is now based on Option #3 rather than Option #5).

>From the perspective of a programmer, what they're seeing with the
enhanced SQLite library is a new SQLite API function:

/*
** The sqlite3_async_update_hook() interface registers a callback function
** with the [database connection] identified by the first argument
** to be invoked whenever the database has been modified in any way.
**
** The monitoring of the database occurs in a separate thread (one thread
** per monitored connection) from the thread that invoked
** sqlite3_async_update_hook().  When a database modification is detected,
** the callback function is called from the monitoring thread.  Thus,
** some thread-level synchronization mechanism (e.g., appropriate thread
** safety mode and database locking) should be used as appropriate.
**
** If the async update hook callback function pointer is NULL then the no
** update callbacks are made.
**
** If an async update hook callback function has been registered with a
** database connection at the time it is closed, the callback function
** will automatically be unregistered as part of the connection close
** operation.
**
** The second argument is a pointer to the function to invoke when the
** database is modified.
** The first argument to the callback is a copy of the third argument
** to sqlite3_async_update_hook().
** The second callback argument is a pointer to the database name.
**
** If another function was previously registered, its pArg value
** is returned.  Otherwise NULL is returned.
*/
void *sqlite3_async_update_hook(
  sqlite3*,
  void(*)(void *, const char *),
  void*
);


When I was working on the Option #5 prototype; I created a video demo.
If I did it again for Option #3, it probably wouldn't be much different.
The demo shows the mechanism working both within a single process as
well as across two processes (a Lua-based GUI prototype app, and the
sqlite3.exe command line utility, each linked against the enhanced
SQLite library). (The Lua aspect is irrelevant here; LuaSQL just happened
to be another thing we wanted to evaluate in this prototype).

The video demo is available in two formats an AVI (codec available at
http://www.techsmith.com/download/codecs.asp):

http://www.innovision.com/techdocs/sqlite/SQLiteAsyncDatabaseChangeExample3.
avi

and a lower-quality MPEG:

http://www.innovision.com/techdocs/sqlite/SQLiteAsyncDatabaseChangeExample3.
mp4


Now... I've been away from this for at least half a year, but here are
some of my thoughts at this point:

1. It appears that our desire to have cross-process update notification
is shared by others, so maybe it's time to have the SQLite community dig
into this issue more and see if we can arrive at a mutually beneficial
solution.

2. Any solution involving the database writer/changer to take any
explicit action (e.g. signaling, etc.) to notify other processes
possibly using that database file that it has changed is 

Re: [sqlite] notify all processes of database modification

[Rich Rattanni]

> That is, other than polling, .

Yep, that trick.  (Why does everyone dismiss polling...don't we have
any embedded programmers here?  Hey Windows CE guy, don't raise your
hand...)

Roger summed it up, and labeled it correctly (crappy).  But if it
works sufficiently for him, why not explore it?

Arno:  If you are still reading this, please let us know what solution
you eventually took.  I like to see closure on these threads, and
perhaps we will learn something from you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] notify all processes of database modification

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 06/15/2010 08:02 PM, Igor Tandetnik wrote:
> That's just polling, really. SQLite's busy handler tries to access the 
> database, sleeps a little, tries again, sleeps again, and so on. It's not 
> "woken up" the way, say, a thread waiting on a mutex would be.

True, However it does correctly deal with things like a process going away
abruptly which is nice logic to not have to reimplement and test.  I did use
the word "crappy" :-)

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkwYSw8ACgkQmOOfHg372QQFYwCeLyq8dYG8S97yuOl7eImgLNzT
ZG8An1a/JS3KXEDkCEDuk0rGDtpydnGe
=CoTo
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] notify all processes of database modification

[Jay A. Kreibich]

On Tue, Jun 15, 2010 at 09:47:23PM -0500, Jay A. Kreibich scratched on the wall:
> On Tue, Jun 15, 2010 at 09:48:53PM -0400, Igor Tandetnik scratched on the 
> wall:
> > Rich Rattanni  wrote:
> > > The creator of SQLite actually gave a talk about using an SQLite
> > > database as a means for IPC (it was available on youtube, maybe you
> > > can find it).  If you want an 'sqlit-ish' way, why not use that trick?
> > 
> > Well, what trick? There is no way I know of for another process to
> > get notified of changes to the database. That is, other than polling,
> > or a side channel independent of SQLite.
> 
>   Depends on the type of RPC.  SQLite makes a pretty good message queue

Err... make that IPC.  We all know SQLite for RPC doesn't work that well.

>   system, since clients can read/write/disconnect/reconnect at will,
>   and the transaction system makes it all safe and sound.
> 
> 
>   If polling produces acceptable speed, where one applicatoin can react
>   to changes made by the other in a 10 to 15 secnod window, the best
>   bet is likely to be PRAGMA user_version.
> 
>   http://www.sqlite.org/pragma.html#pragma_schema_version
> 
>-j
> 
> -- 
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
> 
> "Intelligence is like underwear: it is important that you have it,
>  but showing it to the wrong people has the tendency to make them
>  feel uncomfortable." -- Angela Johnson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] notify all processes of database modification

[Igor Tandetnik]

Roger Binns  wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> On 06/15/2010 06:48 PM, Igor Tandetnik wrote:
>> Well, what trick? There is no way I know of for another process to get 
>> notified of changes to the database. That is, other than
>> polling, or a side channel independent of SQLite. 
> 
> One way I have done it in the past is to have a second database that is
> exclusively locked by whoever does writing.  Waiters then also try a begin
> immediate with infinite timeouts.  After the writer updates the main
> database they release the lock on the second one which wakes up all the
> waiters.  Rinse and repeat.

That's just polling, really. SQLite's busy handler tries to access the 
database, sleeps a little, tries again, sleeps again, and so on. It's not 
"woken up" the way, say, a thread waiting on a mutex would be.
-- 
Igor Tandetnik

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

Re: [sqlite] notify all processes of database modification

[Jay A. Kreibich]

On Tue, Jun 15, 2010 at 09:48:53PM -0400, Igor Tandetnik scratched on the wall:
> Rich Rattanni  wrote:
> > The creator of SQLite actually gave a talk about using an SQLite
> > database as a means for IPC (it was available on youtube, maybe you
> > can find it).  If you want an 'sqlit-ish' way, why not use that trick?
> 
> Well, what trick? There is no way I know of for another process to
> get notified of changes to the database. That is, other than polling,
> or a side channel independent of SQLite.

  Depends on the type of RPC.  SQLite makes a pretty good message queue
  system, since clients can read/write/disconnect/reconnect at will,
  and the transaction system makes it all safe and sound.


  If polling produces acceptable speed, where one applicatoin can react
  to changes made by the other in a 10 to 15 secnod window, the best
  bet is likely to be PRAGMA user_version.

  http://www.sqlite.org/pragma.html#pragma_schema_version

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] notify all processes of database modification

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 06/15/2010 06:48 PM, Igor Tandetnik wrote:
> Well, what trick? There is no way I know of for another process to get 
> notified of changes to the database. That is, other than polling, or a side 
> channel independent of SQLite.

One way I have done it in the past is to have a second database that is
exclusively locked by whoever does writing.  Waiters then also try a begin
immediate with infinite timeouts.  After the writer updates the main
database they release the lock on the second one which wakes up all the
waiters.  Rinse and repeat.

This is an extremely crappy signaling mechanism, but does work if the use
case is fully understood.  My reason for using it was because it works on
all platforms and saved me from having to come up with and test various
platform specific mechanisms.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkwYNHAACgkQmOOfHg372QQcggCcDcPa2k0LCaQ8pMumMPT0dXdQ
ytEAn3TMFVGTfKn6EJxEoerRifV34tgT
=Eoem
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] notify all processes of database modification

[Igor Tandetnik]

Rich Rattanni  wrote:
> The creator of SQLite actually gave a talk about using an SQLite
> database as a means for IPC (it was available on youtube, maybe you
> can find it).  If you want an 'sqlit-ish' way, why not use that trick?

Well, what trick? There is no way I know of for another process to get notified 
of changes to the database. That is, other than polling, or a side channel 
independent of SQLite.
-- 
Igor Tandetnik

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

Re: [sqlite] notify all processes of database modification

[Rich Rattanni]

The creator of SQLite actually gave a talk about using an SQLite
database as a means for IPC (it was available on youtube, maybe you
can find it).  If you want an 'sqlit-ish' way, why not use that trick?

One advantage of using SQLite is that debugging / backtracing becomes
easier.  Since your messages are passed through a database, you can
easily backtrace IPC calls (by perhaps setting a 'complete' field
instead of deleting a IPC message) and you can inject IPC messages
easily by using the SQLite CLI.


On Tue, Jun 15, 2010 at 5:01 PM, Igor Tandetnik  wrote:
> arno  wrote:
>> I have two processes connecting to a database. Both can modify database. When
>> some process modify database, I want the other to be notified (so, it can
>> reload data).
>> Currently, I use unix sockets, so a process can notify all listener process
>> that something has changed. But I wonder if there's a better way to achieve
>> that.
>> I've tried to a create a custom function (with sqlite3_create_function), and
>> use triggers. But unfortunately, my trigger was executed for the modifying
>> process.
>
> SQLite is not an interprocess communication mechanism. Any notification of 
> the sort you envision must be done outside of SQLite.
> --
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] notify all processes of database modification

[Igor Tandetnik]

arno  wrote:
> I have two processes connecting to a database. Both can modify database. When
> some process modify database, I want the other to be notified (so, it can
> reload data).
> Currently, I use unix sockets, so a process can notify all listener process
> that something has changed. But I wonder if there's a better way to achieve
> that.
> I've tried to a create a custom function (with sqlite3_create_function), and
> use triggers. But unfortunately, my trigger was executed for the modifying
> process.

SQLite is not an interprocess communication mechanism. Any notification of the 
sort you envision must be done outside of SQLite.
-- 
Igor Tandetnik


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