...

Reply-To: stef@chronozon.dyndns.org
Subject: Re: [SQL] how to know when a table is altered
X-Mailer: NeoMail 0.95
X-IPAddress: 204.148.73.2

Ed wrote:
> Vincenzo Passoli wrote:
> > i'm developing a framework (mod_perl+apache) that reads the db-schema
and
> > explode html forms.
> > my problem is to recognise when a table is altered so that the framework
can
> > update the related forms connected to the db tables.
> > i don't want to read the schema every time.
> > How can i implement this ?
> My sub-optimal approach was to cache all of the generally static tables
> (requiring a restart to reload them if they changed), and query the
> rest.  You can avoid a lot of joins by querying the db for the foreign
> keys to static tables and then looking them up only in the app cache. 
> But caching query results and invalidating them when the underlying
> tables changed would greatly simplify my app and speed things up, so I'd
> love to hear if others have a better/faster solution here.
> 

Personally, we did something similiar here. There is a textfile
that was used to create the schema, and so we read in that
into memory and then did an checked the parameters passed
in to see which tables were affected (the param('client_billing'
or such like). If you pass in parameters in this fashion, then
you can instantly rule out most tables from the update.

Once you have that, you need to check each value for a
change. Its not that slow (2-4 seconds roughly on a p2-266
with Sybase12) but there is a better way to do this. there
+must+ be ;)

of course, you could try a 'select' on the table with all those
values as match criteria, if you dont find an exact match then 
insert it. otherwise dont. dont know how fast that will be though. 

If you are only interested in certain 'key values' then why not
pollute the apache global space with an associative array ?
(assuming of course you are running apache and perl ;)


ymmv.

regards,
Stefs