notify all processes of database modification

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
18 messages Options
Reply | Threaded
Open this post in threaded view
|

notify all processes of database modification

arno renevier
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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: notify all processes of database modification

Igor Tandetnik
arno <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> wrote:

> arno <[hidden email]> 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
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: notify all processes of database modification

Igor Tandetnik
Rich Rattanni <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: notify all processes of database modification

Jay Kreibich
In reply to this post by Igor Tandetnik
On Tue, Jun 15, 2010 at 09:48:53PM -0400, Igor Tandetnik scratched on the wall:
> Rich Rattanni <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: notify all processes of database modification

Igor Tandetnik
In reply to this post by Roger Binns
Roger Binns <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: notify all processes of database modification

Jay Kreibich
In reply to this post by Jay 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 <[hidden email]> 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
> [hidden email]
> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: notify all processes of database modification

Roger Binns
In reply to this post by Igor Tandetnik
-----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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: notify all processes of database modification

Rich Rattanni
In reply to this post by Igor Tandetnik
> ....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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: notify all processes of database modification

Ed Scherer
In reply to this post by arno renevier
> 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 likely to
have undesirable vulnerabilities (change can occur that will go
undetected, etc.) Consider: (a) file-level copy operations,
(b) changes by other tools that don't "follow the rules," etc.

3. As I recall, there were a number of advantages to going with a more
"built into SQLite" approach. Among them (a few that I remember right
now):
- By building it into SQLite, it is more likely to work consistently and
robustly across all SQLite-based apps and tools and on all platforms,
even if the apps and tools were developed by different
people/organizations.
- There are certain control points in the lower levels of SQLite (e.g.,
the b-tree and pager layers) that make excellent places to wedge in
change notification mechanisms. In the absence of using such control
points, notification mechanisms are harder to implement and tend to
suffer from inefficiencies (e.g., doing certain notification-related
things when a particular database file is known to be locked and just
changed can be much more optimal than alternatives).
- Simplicity from the perspective of the SQLite library user.
- Who wants to keep re-inventing this wheel?

4. An interesting point to ponder as you're evaluating alternative
approaches to this problem: What layer of software really knows what
files are involved in a database connection over time? I'd claim
that it's SQLite (not the app layer above it). Take, for example, an
application that opens an SQLite database connection and then
executes arbitrary (e.g., from a console or an input file) SQL
commands against it. Some of those commands might be
ATTACH DATABASE or DETACH DATABASE commands. So... the set of files
that need to be monitored for change is itself changing. Where is
that information most readily available? Yup... at the SQLite level.

5. It would be possible to implement a good, high quality cross-process
notification mechanism without building it directly into SQLite if
SQLite were first enhanced with some new callback hooks at just the right
places (I won't elaborate on these further without first reviewing
our code to see where those hooks would need to be). Some SQLite VFS
extensions could also help put platform-dependent code where it belongs
and minimize the surface area of code for anyone wanting to port SQLite
(along with the cross-process notification mechanism) to a new platform.

6. The change granularity is a significant issue. It's not too hard or
expensive to provide change notification on a database file level.
Getting more fine grained, though, becomes much more difficult. While
it would be great to get table (or even row-level) cross-process
change notification, it is difficult to see how this could be done
efficiently without significant implementation done inside the
bowels of SQLite. Database-level granularity is enough to at least
get the ball rolling; additional mechanisms (e.g., trigger-based
change logging to a journal table or some such) can always be layered
on top of this if finer grain is needed. Of course, such finer-grained
features *could* be built into SQLite at a later date.

7. From the perspective of a user of the SQLite library, it's hard to
beat the simplicity of a function like what we did:

void * sqlite3_async_update_hook(sqlite3 *db,
  void(*)(void *context, char const *dbname), void *context);

8. I'd really like to either get this functionality built into SQLite
*or* go the opposite direction and get more-or-less the same API-level
functionality, but with the notification mechanism source code as
decoupled as much as possible from the SQLite source code. It's just
not desirable for us to maintain the customized-SQLite-source-code
solution we're using right now.

--
Ed Scherer
Innovision Corporation



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: notify all processes of database modification

Ed Scherer
In reply to this post by arno renevier
(Arggghhhh!!! 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 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 likely to
have undesirable vulnerabilities (change can occur that will go
undetected, etc.) Consider: (a) file-level copy operations,
(b) changes by other tools that don't "follow the rules," etc.

3. As I recall, there were a number of advantages to going with a more
"built into SQLite" approach. Among them (a few that I remember right
now):
- By building it into SQLite, it is more likely to work consistently and
robustly across all SQLite-based apps and tools and on all platforms,
even if the apps and tools were developed by different
people/organizations.
- There are certain control points in the lower levels of SQLite (e.g.,
the b-tree and pager layers) that make excellent places to wedge in
change notification mechanisms. In the absence of using such control
points, notification mechanisms are harder to implement and tend to
suffer from inefficiencies (e.g., doing certain notification-related
things when a particular database file is known to be locked and just
changed can be much more optimal than alternatives).
- Simplicity from the perspective of the SQLite library user.
- Who wants to keep re-inventing this wheel?

4. An interesting point to ponder as you're evaluating alternative
approaches to this problem: What layer of software really knows what
files are involved in a database connection over time? I'd claim
that it's SQLite (not the app layer above it). Take, for example, an
application that opens an SQLite database connection and then
executes arbitrary (e.g., from a console or an input file) SQL
commands against it. Some of those commands might be
ATTACH DATABASE or DETACH DATABASE commands. So... the set of files
that need to be monitored for change is itself changing. Where is
that information most readily available? Yup... at the SQLite level.

5. It would be possible to implement a good, high quality cross-process
notification mechanism without building it directly into SQLite if
SQLite were first enhanced with some new callback hooks at just the right
places (I won't elaborate on these further without first reviewing
our code to see where those hooks would need to be). Some SQLite VFS
extensions could also help put platform-dependent code where it belongs
and minimize the surface area of code for anyone wanting to port SQLite
(along with the cross-process notification mechanism) to a new platform.

6. The change granularity is a significant issue. It's not too hard or
expensive to provide change notification on a database file level.
Getting more fine grained, though, becomes much more difficult. While
it would be great to get table (or even row-level) cross-process
change notification, it is difficult to see how this could be done
efficiently without significant implementation done inside the
bowels of SQLite. Database-level granularity is enough to at least
get the ball rolling; additional mechanisms (e.g., trigger-based
change logging to a journal table or some such) can always be layered
on top of this if finer grain is needed. Of course, such finer-grained
features *could* be built into SQLite at a later date.

7. From the perspective of a user of the SQLite library, it's hard to
beat the simplicity of a function like what we did:

void * sqlite3_async_update_hook(sqlite3 *db,
  void(*)(void *context, char const *dbname), void *context);

8. I'd really like to either get this functionality built into SQLite
*or* go the opposite direction and get more-or-less the same API-level
functionality, but with the notification mechanism source code as
decoupled as much as possible from the SQLite source code. It's just
not desirable for us to maintain the customized-SQLite-source-code
solution we're using right now.

--
Ed Scherer
Innovision Corporation



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: notify all processes of database modification

Ali Ahmad Qureshi
In reply to this post by Ed Scherer
The documents are no longer available, can you kindly share it to my mail.
I'll be extremely thankful to you. Mail is [hidden email]



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 [hidden email]

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: notify all processes of database modification

Jens Alfke-2


> On Feb 7, 2020, at 6:23 AM, Kees Nuyt <[hidden email]> 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.)

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: notify all processes of database modification

Rowan Worth-2
On Sat, 8 Feb 2020 at 04:02, Jens Alfke <[hidden email]> wrote:

> > On Feb 7, 2020, at 6:23 AM, Kees Nuyt <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: notify all processes of database modification

Simon Slavin-3
On 10 Feb 2020, at 4:26am, Rowan Worth <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: notify all processes of database modification

Rowan Worth-2
On Mon, 10 Feb 2020 at 12:53, Simon Slavin <[hidden email]> wrote:

> On 10 Feb 2020, at 4:26am, Rowan Worth <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users