Is it possible for SQLite to notify an application connected to it of database changes?

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

Is it possible for SQLite to notify an application connected to it of database changes?

Erin Drummond
Hi,

I am developing a p2p application (in Java) which has a SQLite
database attached. I am currently using the sqlitejdbc JDBC driver for
database access.
Ideally, I want SQLite to notify the application whenever a change is
made in the database, so it can propagate the change to other peers.
Currently it finds changes by constantly polling the database, however
this is not ideal as it wont scale well.
Is it possible for an application to be notified when a trigger inside
the database is fired?

Thanks,
Erin
_______________________________________________
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: Is it possible for SQLite to notify an application connected to it of database changes?

J. King-3
On Mon, 23 Nov 2009 23:12:12 -0500, Erin Drummond <[hidden email]>  
wrote:

> Is it possible for an application to be notified when a trigger inside
> the database is fired?

I imagine you could make the trigger call a user function which notifies  
the application...

--
J. King
_______________________________________________
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: Is it possible for SQLite to notify an applicationconnected to it of database changes?

Igor Tandetnik
In reply to this post by Erin Drummond
Erin Drummond <[hidden email]> wrote:
> I am developing a p2p application (in Java) which has a SQLite
> database attached. I am currently using the sqlitejdbc JDBC driver for
> database access.
> Ideally, I want SQLite to notify the application whenever a change is
> made in the database, so it can propagate the change to other peers.
> Currently it finds changes by constantly polling the database, however
> this is not ideal as it wont scale well.

An SQLite database connection can detect a change made to the database on another connection no better than a FILE* handle can detect an fwrite made to the same file on another FILE* handle. Which is to say, not at all.

> Is it possible for an application to be notified when a trigger inside
> the database is fired?

Trigger is not fired on a database - it's fired on a database connection.

There are plenty of interprocess communication mechanisms provided by any decent operating system. SQLite is not one of them.

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: Is it possible for SQLite to notify an applicationconnected to it of database changes?

Erin Drummond
Ok, thankyou for your replies

@Igor: I think you misunderstood. I only have one connection to the
database (provided by the JDBC driver). I only care about and use that
one connection (no other applications access the database). I was
wondering if database trigger could be used to notify the application
(for example, have a trigger (via CREATE TRIGGER etc) that gets
triggered when a table is updated).

@J. King: Do you mean like what is described here?
http://www.webdotdev.com/nvd/content/view/347/99999999/1/9/
I was trying to stay away from hacking SQLite directly (remember its a
Java application im developing, so I cant just extend the C library)

On Tue, Nov 24, 2009 at 5:29 PM, Igor Tandetnik <[hidden email]> wrote:

> Erin Drummond <[hidden email]> wrote:
>> I am developing a p2p application (in Java) which has a SQLite
>> database attached. I am currently using the sqlitejdbc JDBC driver for
>> database access.
>> Ideally, I want SQLite to notify the application whenever a change is
>> made in the database, so it can propagate the change to other peers.
>> Currently it finds changes by constantly polling the database, however
>> this is not ideal as it wont scale well.
>
> An SQLite database connection can detect a change made to the database on another connection no better than a FILE* handle can detect an fwrite made to the same file on another FILE* handle. Which is to say, not at all.
>
>> Is it possible for an application to be notified when a trigger inside
>> the database is fired?
>
> Trigger is not fired on a database - it's fired on a database connection.
>
> There are plenty of interprocess communication mechanisms provided by any decent operating system. SQLite is not one of them.
>
> 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: Is it possible for SQLite to notify anapplicationconnected to it of database changes?

Igor Tandetnik
Erin Drummond wrote:
> @Igor: I think you misunderstood. I only have one connection to the
> database (provided by the JDBC driver). I only care about and use that
> one connection (no other applications access the database). I was
> wondering if database trigger could be used to notify the application
> (for example, have a trigger (via CREATE TRIGGER etc) that gets
> triggered when a table is updated).

Yes, you can have a trigger calling a user-defined function. Though it's not clear why you would need to go to these lengths: if your application is the one making changes, can't it just notify itself at the same time?

Before you ask, I have no idea how or whether one can create a user-defined function with Java binding.

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: Is it possible for SQLite to notify an application connected to it of database changes?

Simon Slavin-3
In reply to this post by Erin Drummond

On 24 Nov 2009, at 4:12am, Erin Drummond wrote:

> Currently it finds changes by constantly polling the database

What, precisely, do you poll ?

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: Is it possible for SQLite to notify anapplicationconnected to it of database changes?

Erin Drummond
In reply to this post by Igor Tandetnik
> if your application is the one making changes, can't it just notify itself at the same time?
Actually, come to think of it, it probably could. *facepalms self*.
The application is going to expose a web interface which the user
interacts with (this is how the database gets changed in the first
place). The p2p engine is then responsible for propagating the changes
throughout the network. Both engines are in the same application, so
this should be possible. Thanks

> What, precisely, do you poll ?
A few mutually exclusive tables in the database to check for changes.
This involves selecting every record initially, storing the value of
the field that is going to change, and then selecting every record
again at regular intervals and comparing the values returned against
the stored values. If they are different, viola, we have a change.
Not efficient at all.

Thanks for your help guys, much appreciated

On Wed, Nov 25, 2009 at 1:55 AM, Igor Tandetnik <[hidden email]> wrote:

> Erin Drummond wrote:
>> @Igor: I think you misunderstood. I only have one connection to the
>> database (provided by the JDBC driver). I only care about and use that
>> one connection (no other applications access the database). I was
>> wondering if database trigger could be used to notify the application
>> (for example, have a trigger (via CREATE TRIGGER etc) that gets
>> triggered when a table is updated).
>
> Yes, you can have a trigger calling a user-defined function. Though it's not clear why you would need to go to these lengths: if your application is the one making changes, can't it just notify itself at the same time?
>
> Before you ask, I have no idea how or whether one can create a user-defined function with Java binding.
>
> 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: Is it possible for SQLite to notify anapplicationconnected to it of database changes?

Simon Slavin-3

On 24 Nov 2009, at 6:17pm, Erin Drummond wrote:

>> What, precisely, do you poll ?
> A few mutually exclusive tables in the database to check for changes.

Depending on what you care about, you might find it easier to check

PRAGMA count_changes

either instead of what you currently poll, or as an easy first stage check, to see whether you need to do the more 'expensive' reading of table contents.

But I see elsewhere in your reply you may not need to poll at all, so it's all good.

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