[Tfug] more RDBMS

[Nate]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Primo:

The only way that I know of to do that in a portable fashion would be to
track it yourself.

1. Track the updates in a different table that you update every time you
do an update.  For instance, if you had another table that simply had a
timestamp of the last time you updated the table, that would work.  The
problem is that every place that updates your table has to also update
the last_update table.  This may not be a viable option in a large
complex application, or with multiple applications acting on that same
table.  There are other ways to get around this, with either dictionary
lookups or triggers, but they are not portable as every RDBMS will do
it's dictionary info different, and triggers aren't supported everywhere.

2. Another option is to look for obvious changes to a pattern in the
data.  For instance, if your table is only ever inserted into, and every
insert has a unique id that is sequentially incremented, then your
client just has to keep track of the last ID it knew about.  Then to
check if the table has been updated, simply do a, "select count(*) from
table where id > ?" and bind $last_update_id to it.  Or if every update
also includes a change to a timestamp column, then you can track it that
way.  "select count(*) from table where update_time > ?" and bind
$last_update_time to it.  I've used both techniques in the past.

Honestly the easiest and fastest method would probably be to do it
non-portable.  If your application supports 3 different databases, just
put in 3 different ways of checking it.  The best way being a dictionary
lookup of some sort.  Or triggers.

Secundo:

My first impression is that a SQL call to get the time is quite
expensive, especially if you do it often, like every second.  If they're
kiosks you control, why not just sync their time to the server using
existing tools, such as NTP or rdate.  If you can't change the local
time (such as if you don't have root) you can at least use rdate to do a
remote lookup.

Another method I have used in the past which is possibly a little
lighter is to do a HEAD request to a web server running on a box with
known good time, such as the database server itself.  For example:

> HEAD 'http://www.google.com'|egrep '^Date'
Date: Tue, 23 Oct 2007 17:25:43 GMT

You have to parse it, of course, which can be expensive.  You could do
the lookup every once in a while, and just store a delta value to use
from then on.  That way you're not contantly hitting the server for the
time, and instead you just store how many seconds the local machine is
off from the server, and adjust all of your time calculations accordingly.

So, use NTP if you can.  If you can't, then everything you do is going
to have a little overhead, and increase the complexity of your process.

Nate

Bexley Hall wrote:
> Hi,
>
> I *hope* these are simple RDBMS questions (even
> though the simple ones never actually *prove* to
> be so!  :< )
>
> Question primo:
>
> I'm looking for an inexpensive (computationally)
> way to see if a "database", or, specifically, a
> certain *table* has been *changed* since the last
> time I "noticed" it.  I.e. a way of stat(2)'ing
> it that is *portable* (i.e. lives in SQL land).
>
> Essentially, I need to do an expensive query
> but *only* if the dataset has changed since the
> last time I ran that query.  (presumably, I ran
> it last time *because* the dataset had changed!)
> I.e., I do some client-side caching...
>
> Question secundo:
>
> In a VERY LIGHT DUTY APPLICATION, how foolish is
> it for clients to periodically issue queries of
> the RDBMS like:
>
>   SELECT now;
>
> instead of tracking time "locally", themselves?
> I.e., use the RDBMS as a time reference instead
> of burdening the individual clients (which, in
> this case, are kiosks) with the overhead of a
> "real" time service?
>
> Thanks!
> --don
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> _______________________________________________
> Tucson Free Unix Group - tfug at tfug.org
> Subscription Options:
> http://www.tfug.org/mailman/listinfo/tfug_tfug.org

- --
Nate
System Admin Manager
System Administration
Ext 220
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.2 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFHHj6MRMRYK1K/wKQRAir+AKCdn7TVWziIU5fOLgBuN9uakpiZ0wCdGdTm
wovTWP4cghCTbu4LhPF2Gd4=
=UF3I
-----END PGP SIGNATURE-----