Subject Re: [Firebird-Architect] External "procedural engine" modules
Author Jim Starkey
paulruizendaal wrote:

>As several of the project admins know, we've been busy developing an
>"Oracle"-mode for Firebird. They initial goal is to be able to run a
>single application, "Compiere". Progress has been good and large
>parts of the Compiere application already work with Firebird. :^)
>I am thinking that perhaps the most practical would be have an API
>for 'external' interpreters to hook into. My thoughts are for this
>API to be internal to the engine, an interface if you like. I am not
>thinking of an external API where external libraries can (un)register
>themselves at will.
The right way to implement PL/SQL is an interpreter sitting parallel to
but separate from looper that implement PL/SQL semantics.

The runtime is structured with three independent mechanisms. Record
streams are handled with a very general polymorphic RSB mechanism;
boolean and value expressions are handled by a recursive EVAL mechanism,
and BLR semantics are handled by a painfully tortuous "looper" designed
to allow a statement to stall in a non-threaded environment. Looper, in
turn, calls on the VIO (virtual I/O) layer to do all actual database
work. A PL/SQL (and/or a proper SQL) execution engine could exploit the
existing EVL and RSB mechanisms while providing an alternative path the
to very clean VIO layer. There is no reason to extend BLR (which is
akin to extending the dodo) when an relatively simple and more efficient
alternative engine would do the trick.

Adding support for an external PL/SQL client would necessarily impact
the may layers of plumbing, causing unnecessary architecture grief to
achieve a very inefficient implementation. The plumbing needs a better
API to support proper SQL, but that's a different issue. When that time
comes, a proper SQL interpreter is a no-brainer.

I strongly suggest that any attempt to extend BLR and looper to support
PL/SQL be abandoned forthwith and directly in favor an alternative
execution engine using otherwise existing mechanisms.

The question is sticking in a Java VM is a tad more complicated than has
been suggested. Getting control to flow from a UDF to a JVM is indeed a
piece of cake. Getting a proper JDBC callback mechanism to work is more
than a tad more work. Good work, important work, satisfying work,
certainly. But not a knock off.