At 06:31 PM 10/19/01 +0200, Nando Dessena wrote:

>Hello,
>I was wondering what would it take to be able to pass context
>information (namely connection and transaction handle) to a UDF, in
>order to:
>
>- let UDF access data without the need for an additional connection,
>which would be a very bad idea.
>- commit/rollback work done by a UDF.
>
>To me, it seems a really useful thing WRT extending the server, and I
>was wondering if there would be any serious disadvantages to that.
>
>Any comments?*

I've explored some of these issues in Netfrastructure. At the
risk of further alienating those folks thoroughly bored with
hearing of my afterlife, here are some thoughts.

UDFs and triggers should merge in capabilities if not syntax
(I favor Java for both). Each needs access to full DML
capabilities as well as certain connection information (more
on this later). However, neither a UDF nor a trigger should
have the capability to either commit or rollback a transacation.

In the current Firebird implementation, triggers and UDFs are
radically different beasts. Triggers are written in an
interpretive language that can be carefully controlled. UDFs,
on the other hand, run in the same process/thread context as
the engine. Although there is no technical problem to exposing
the attachment and transaction handles to a UDF, doing so would
significantly increase the risk to the engine from bugs or
attacks from UDFs. The current UDF architecture (I'll take
full resposibility here) is a major security liability within
the product.

A compromise solution might be a second flavor of UDF utilitizing
trigger/store procedure syntax to allow more capability without
sacrificing stability, security, and control.

The long run answer, I believe, is shifting triggers, procedures,
and UDFs to sandbox language, for which Java is the obvious
contender. I've done this in Netfrastructure and am delighted
with the results.

A second issue is passing connection information to triggers,
UDFs, and stored procedures. A scenario for consideration
is this: A web application needs to track all database changes
by user. The application server, which logs into the database
with a generic account, knows the identity of the guy at the
browser and needs a way to make this available to the trigger(s)
that post record changes to a log table.

Netfrastructure addresses this problem with concept of connection
variables. A connection variable has a name and a value, both
strings, and is assigned programmatically (a method in the
JDBC Connection class). Any DML code (including triggers)
can access a connection variable with the sql value
expression "cnct.<name>". Connection variable are properties
of the attachment/connection and persist through transaction
commit and rollback. There are a small number of system
defined connection variable to pass miscellaneous useful
stuff.

I don't know what forms of abuse I've let myself in for, but
the potential problems seem bounded, particularly relative to
the plagues of triggers I loosed on mankind. But from fire
comes burns.

Jim Starkey