Subject RE: Generic Triggers
Author Mark O'Donohue
Hi Jim

>Before they changed the water we had been discussing alternative
>architectures for triggers. I, as usual, was plugging Java as
>a trigger language. Other folks were looking for ways to avoid
>defining vast number of highly similar triggers for various tables.

I agree java would be a useful way to do this.

>Partly due to the urging of Greg Dietz, I finally got around to
> designing triggers for Netfrastructure. Since some of the ideas
> that went into the design came from the architecture list, I thought
> it only fair that I report back the architectural design. Whether
> or not it is practical or even desirable for Interbase (or IBPhoenix
> or FireBird or IBAlbuquerque) I will leave to the folks doing the
> work.

Maybe this shows my ignorance but what is netfrastructure, and have you
implemented this yet or is it still in the design phases?

It is pretty easy to call a java method from C, I've had a brief look at
how that would work for UDF's and it could be done fairly simply. Java
also has a lot of nice reflection stuff, so you can do some nice syntax
checking of the parameters before making a call from the DB then through
to java when passing arguments. Longer term it also has some nice
ability to restrict users functions from corrupting the database.

But the trickiest part that I see is having all the backing up details
that support the Connection parameter that is passed into the java code.
This implies you also have implementations of Connection, Statement,
CallableStatement, ResultSet etc that link back into the current
connection being used by the transactions, probably back through native
C calls. I assume you have something (NfsConnection, or know of
something) that does this. It is not possible, as I see it, to use
interclient in this way.

Also I believe that the implementation will probably work differently
for super server and classic. For example in super generally I would
expect we have one instance of the virtual machine and variables will be
shared amongst all open databases. Whereas with classic, running as a
service, there will be probably one per client, as their would be for a
direct local connections. Java static or "global" variables will act
differently in these cases.

A super server running one java virtual machine (or a few) could be a
good thing, but also may be a pain, for instance if you use the same
trigger in several related databases or even several tables in the same
database you would have to resort to some tricks to keep local
statistics in java for each seperate call.

It might be worth considering not calling a static method, but a real
object with some ability to control scope of the object, say being able
to declare that it exists at a global, database, table or per call

Otherwise it was nice reading