how to detect database change made by another application

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

how to detect database change made by another application

Will Parsons
I have two applications that access the same database and that can run
at the same time.  If changes are made to the database by one
application, I would like the other application to update its display
to reflect the change.  The total_changes() function only works from a
single connexion so doesn't help here, and it appears there is no API
to access to the file change counter that's kept in an SQLite3 database.

Any suggestions on how to handle this?  In case it's relevant, the
*only* purpose of the 2nd application's being able to detect a change
is so that it can update its own display, and I will be using the Ruby
bindings to SQLite3 in both programs.

--
Will

_______________________________________________
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: how to detect database change made by another application

Eduardo Morras-2
On Fri, 13 Dec 2013 23:19:55 +0000 (UTC)
Will Parsons <[hidden email]> wrote:

> I have two applications that access the same database and that can run
> at the same time.  If changes are made to the database by one
> application, I would like the other application to update its display
> to reflect the change.  The total_changes() function only works from a
> single connexion so doesn't help here, and it appears there is no API
> to access to the file change counter that's kept in an SQLite3 database.
>
> Any suggestions on how to handle this?  In case it's relevant, the
> *only* purpose of the 2nd application's being able to detect a change
> is so that it can update its own display, and I will be using the Ruby
> bindings to SQLite3 in both programs.

There are many ways to do that, for example using pragma user_version. Each time you commit a change, upgrade the user_version and when you want to check if the other app has made a change check if it's equal to your user_version value.

You can do similar with a simple table with a single column row, every modification ends with updating the table row value.

I use user_version for other things, I put there the version of my app/db, but it can fit your needs.

>
> --
> Will
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


---   ---
Eduardo Morras <[hidden email]>
_______________________________________________
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: how to detect database change made by another application

Simon Slavin-3
In reply to this post by Will Parsons

On 13 Dec 2013, at 11:19pm, Will Parsons <[hidden email]> wrote:

> The total_changes() function only works from a
> single connexion so doesn't help here, and it appears there is no API
> to access to the file change counter that's kept in an SQLite3 database.

You're right.  There's no good way to do it.  Though I don't see why it shouldn't be added as a PRAGMA: make sure you have a lock on the file, grab those bytes from the header, unlock the file and return the value.  It would be useful for those doing multi-access with caching (an onscreen copy is a cache).

Simon.
_______________________________________________
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: how to detect database change made by another application

Richard Hipp-3
On Sat, Dec 14, 2013 at 5:46 AM, Simon Slavin <[hidden email]> wrote:

>
> On 13 Dec 2013, at 11:19pm, Will Parsons <[hidden email]> wrote:
>
> > The total_changes() function only works from a
> > single connexion so doesn't help here, and it appears there is no API
> > to access to the file change counter that's kept in an SQLite3 database.
>
> You're right.  There's no good way to do it.  Though I don't see why it
> shouldn't be added as a PRAGMA: make sure you have a lock on the file, grab
> those bytes from the header,


More complicated than that:  The change counter in the header does not get
updated in WAL mode.



> unlock the file and return the value.  It would be useful for those doing
> multi-access with caching (an onscreen copy is a cache).
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
[hidden email]
_______________________________________________
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: how to detect database change made by another application

Simon Slavin-3

On 14 Dec 2013, at 10:57am, Richard Hipp <[hidden email]> wrote:

> More complicated than that:  The change counter in the header does not get
> updated in WAL mode.

Ah.  Would there be a way to do it in WAL mode ?  An analogous counter in the WAL journal file ?  Or perhaps it could be implemented but in WAL mode always return -1.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users