Vulcan - Internal JDBC Support

From Jim Starkey on the Firebird Development List 26th Mar 2004

I have implemented a prototype, internal, in-engine JDBC-type interface in Vulcan. This means, for the first time in the long history of Firebird, InterBase, and Rdb/ELN, SQL is now available inside the database engine.

The ramifications are enormous. The following is now possible:

  1. The gradual phasing out of all engine preprocessed modules.
  2. In-line metadata access in ordinary engine objects.
  3. A mechanism to export internal database semantics to security plugins.
  4. A mechanism to export database semantics to user defined functions and blob filters.
  5. A mechanism to supported internal engine tables layered on a small set of core system tables.
  6. An architecturally supportable mechanism to implement "execute sql" functionality.
  7. A mechanism to export database semantics to alternative embedded virtual machines such as Java and PLSQL.

And these are just the beginning. Internal SQL brings the same benefits that database technology brings to application software to the database engine implementation itself.

The implementation is interesting. When I moved DSQL into the engine, I broke "sql statement" into two classes, CStatement, a potentially cached and shared compiled statement and DStatement, the statement instance object. The CStatement object contains the actual request handle, the message and parameter information, and specific knowledge of the statement type. The JDBC-style InternalStatement also uses the CStatement class and all of its goodies, but implements a different client interface and value handling faithful to the set of JDBC abstract interfaces.

Other than statement preparation, which is more expensive than a pure BLR statement but subject to caching and re-use, the JDBC layer is very thin and getting thinner as cleanups continue, so there is no performance reason to retain preprocessed code for even high volume metadata access.

I have also implemented "smart pointer" classes Connect, PStatement, RSet to manage object destruction of Connection (InternalConnection), PreparedStatement (InternalPreparedStatement), and ResultSet (InternalResultSet and a gob of InternalXXXResultSets for JDBC metadata access). A sample snippet of working code follows:

Connect connection = attachment->getUserConnection (transaction);
PStatement statement = connection->prepareStatement (
"select rdb$relation_name, rdb$relation_id from rdb$relations");
RSet resultSet = statement->executeQuery();

while (resultSet->next())
ib_printf ("%s %d\n", resultSet->getString (1),
resultSet->getInt (2));

I'm not sure I like the look of the code -- it looks naked to me without the close() operations. But it does clean up properly in the face of an exception.

The intention is to have two methods to get internal connections, a user connection, subject to user level access control and security, and a system connection with unlimited access.

I haven't figured out the correct transaction semantics. For the time being, a client supplies the transaction object when requesting a connection. I look forward to the debate as we try to figure out how it should work.

The existing implementation is a working prototype proof-of-concept. The list of what it does is much shorter than what it doesn't do, but the structure is in place, so the rest is just details.

I started originally with the existing IscDbc code from the ODBC driver. Unfortunately, it had mutated past use, so I reverted to a version corresponding to my original ODBC driver submission. Oddly enough, it has a striking resemblance to the internal Netfrastructure API, with which I have great familiarity. As a consequence, I have grown strangely territorial toward Connection.h, which defines the abstract interface. Muck with that and you will find a wolf on your case so fast your head will spin. I have every intention of using the same abstract interface to implement a JDBC client library then extend the plumbing to transparently drive the semantics all the way into the engine proper, bypassing and obsoleting the existing mega-ugly DSQL interface.

This step is, in essence, the final recognition that SQL won, BLR lost, and it's time to throw in the towel. Whether or not the JDBC mechanism is layered on or replaces the existing BLR-base mechanisms is now nothing more than a trivial implementation detail.

Good bye, BLR. And thanks for all the fish.