Existing databases updates & comet
dtynan
JsonRestStore, HTTP Channels, etc and a have a couple of questions
about how things work.
I have an existing database (Postgresql) that is being updated by
existing programs. I'd like to expose a couple of the tables in that
DB, via Persevere, and access those using JsonRestStore, with,
hopefully, live-updating via comet (specifically to the 1.2 grid
control). This is basically a one-way feed (read-only) from
Postgresql to the user's browser. Just as an fyi, I'm currently
updating my dojo 1.2 grid by causing my store to periodically reload
itself (via a perl program i wrote which queries the db & converts
results to json). It works, but it's kinda ugly, and I dislike having
to reload the store vs. dojo data notifications via persevere/comet.
1. First question is whether or not I'm going to be able to get live-
data updates via Comet if my SQL tables are modified by some other,
non-Persevere source? I'm guessing that Persevere probably doesn't
send data-change/new-data notifications on the HTTP Channels unless
the Persevere "sees" those changes come through via a PUT or POST, so
it's not going to see any changes to the underlying Postgresql sql
tables...? Is that right?
2. Second question is, when using Persevere to access an SQL database,
does Persevere requery the database for each & every client REST
request, or does it try and cache data? So if my client does: GET /
Customers to retrieve all customers in the Customers table, I
assume it's going to requery everytime, right?
3. When defining classes in Persevere to map to a given table's sql
columns, can I just list some of the columns and it will still work,
or do I need to make sure every column in the sql table is represented
in the class definition in the Persevere config files?
Thanks!
Dylan Tynan
Kris Zyp
> data updates via Comet if my SQL tables are modified by some other,
> non-Persevere source? I'm guessing that Persevere probably doesn't
> send data-change/new-data notifications on the HTTP Channels unless
> the Persevere "sees" those changes come through via a PUT or POST, so
> it's not going to see any changes to the underlying Postgresql sql
> tables...? Is that right?
That's correct. However, I was thinking it may be possible to enable updates
through an SQL data source by having the user create a DB trigger that would
put changes in a change log table that Persevere could monitor with low-cost
polling (the log table would always be empty except when a change occured,
and then Persevere would empty it again, so the poll's would be very cheap).
I haven't implemented anything along these lines yet, but that is what I was
thinking could be good strategy for sending notifications from a database
that was modified by an alternate agent.
> 2. Second question is, when using Persevere to access an SQL database,
> does Persevere requery the database for each & every client REST
> request, or does it try and cache data? So if my client does: GET /
> Customers to retrieve all customers in the Customers table, I
> assume it's going to requery everytime, right?
Persevere does cache individual objects/rows, partly to maintain object
identity, but currently it doesn't cache queries. The assumption is that it
can be quite complex to update a cached queries as the data changes. I may
try to do some query caching in the future. Of course if a database is
updated from alternate sources (and there is no notification system), then
you must requery every time to get the correct results.
> 3. When defining classes in Persevere to map to a given table's sql
> columns, can I just list some of the columns and it will still work,
> or do I need to make sure every column in the sql table is represented
> in the class definition in the Persevere config files?
You can just do some of the columns, you don't need all of them. Of course
if you create new objects through Persevere, they will be recorded in the
database using INSERT statements that only refer to the columns you have
defined.
Thanks,
Kris
dtynan
Yes, after posting that I started thinking about DB notifications,
just as you had mentioned in #1. Found the LISTEN/NOTIFY interface in
PostgreSQL, which is exactly that -- a row-based or select-based
trigger that fires off a notification string (and perhaps the new row
and old row, if applicable) to an external interface. Looks pretty
simple and there is (at least) C and Perl/DBI support for it, which
would get me part-way there, minus the final part into Persevere.
But, on the downside, it also gets into a completely proprietary
postgresql feature, so that's a concern...
Perhaps my better option, is to just bite the bullet and modify the
existing processes to insert/update/delete into both PostgreSQL and
into Persevere, for those cases where it makes sense (just for
reporting some things really). I was hoping I might get to shortcut
around that :-) But, anyway, it's probably the best way & the
extra insert/connection is a non-issue from a performance standpoint.
I guess I'll still need a way to recreate the persevere db from the
postgres one, just in case things get out of sync. I think that'll
work & allow the JsonRestStore w/comet updates that I'm after.
Thanks again,
Dylan Tynan
Kris Zyp
> just as you had mentioned in #1. Found the LISTEN/NOTIFY interface in
> PostgreSQL, which is exactly that -- a row-based or select-based
> trigger that fires off a notification string (and perhaps the new row
> and old row, if applicable) to an external interface. Looks pretty
> simple and there is (at least) C and Perl/DBI support for it, which
> would get me part-way there, minus the final part into Persevere.
> But, on the downside, it also gets into a completely proprietary
> postgresql feature, so that's a concern...
I didn't realize that PostgreSQL had that feature, that's pretty cool. Of
course there can certainly be a PostgreSQL specific data source adapter for
Persevere, it just wouldn't be applicable to other databases.
Kris