Stages in SQL Translation
Date: Sat, 21 Oct 2006 15:23:28 GMT
Message-ID: <QXq_g.3087$k63.2738_at_trndny06>
This topic is an offshoot of Marshall's question regarding "embedded SQL".
I'm referring, once again, back to my first contact with relational DBMS, DEC Rdb/VMS. But I really want to expand the topic to the more general question of how one implements multistage translation, and even whether it's a good or bad idea to begin with.
DEC Rdb/VMS was built on top of a code base called KODA. VAX DBMS (A CODASYL DBMS) has already been built upon KODA, and it seemed good to the engineers to reuse it rather than reinvent it. KODA may or may not account for some of what follows. Note: I"m using the past tense even though Rdb is still in the field, because my point of reference to Rdb is way out of date.
In DEC Rdb/VMS, translation of database requests (DML, if you will) was carried out in two stages.
The first stage was translation of the SQL into an intermediate language called BLR (Binary language representation). BLR is more concise than SQL, and easier to parse. Among other things, tables and columns are refeered to by number in BLR, while they are refeered to by name in SQL. Interpreting BLR at runtime was faster than interpreting SQL. BLR is a little like the machine code for JVM. It was intended to be "run" by a software system.
The Rdb precompilers and the SQLMOD compiler knew how to express SQL requests in BLR. Not only that, they knew how to wrap the BLR in VAX assembly language, so that the VAX assembler (MACRO) could create object modules for the LINK utility. Interactive SQL and Datatrieve (an interpreted language for general use) both knew how to take their input language and turn it into BLR on the fly. In addition, some part of "SQL Services" could parse and translate Dynamic SQL and turn it into BLR.
Translating SQL into BLR requires access to the metadata. Typically, source code in a VAX language would specifiy two different filespecs for the compile time database and the runtime databaase. It was expected that the metadata would be equivalent between these two. This made it possible to isolate devlopment from production for purposes that need not concern us here. SQL was not the first language to be translated into BLR. RDML was the initial language of Rdb, before SQL was supported as of version 3 of Rdb.
BLR was not optimized. If dozens of possible strategies might satisfiy an SQL request, the BLR was sufficiently true to the original SQL so that all of those strategies would be conceived and evaluated by the optimizer.
Rdb internal documentation had another way of representing requests, called "compiled requests". Compiled requests are post optimization. The optimizer (which was cost based starting with version 1 of Rdb) kept optimized requests around for some period of time so that a request that was repeated soon after would not have to be reoptimized. This saved processing time and metadata requests for routine applications. I never studied what compiled requests looked like, but I'm given to understand that they were written in VAX machine language, with procedure calls to some of the built in functions and procedures that were implemented in the runtime package. The use of an actual machine language here, rather than another virtual machine was what bound DEC Rdb to DEC hardware, IIUC.
Anyway, if you've waded through all this, it probably means that you're interested in DBMS engineering. A possible topic of discussion might be:
How much of all this is rendered obsolete by subsequent inventions and discoveries in the field of DBMS engineering? How much of it remains to be rediscovered by cyberspace archeologists? How much of it has become (or been subsumed by) "standard best practices" in the field of DBMS engineering?
Figuring out that compiled requests and/or BLR has been rendered obsolete by data changes or metadata changes was eminently doable, but not exactly trivial. Received on Sat Oct 21 2006 - 17:23:28 CEST