Here I'm going to explain how Firebird DDL commands works in the architecture, why it stops innovation and how it is supposed to work in Firebird 3.0.

DDL in FB works more or less like DML, so first a briefly explanation of how DML works. When a DML command is prepared, it starts in the parser constructing a tree of nodes. That nodes are all a single pointer type, used for all node types and others usages (like storing constants). A node have a list of child nodes.

After parsing, it enters in the semantics phase, where things are verified and adjusted with execution in mind. After that, it enters in the generation phase, that outputs BLR bytes and stores them in the statement.

The DSQL API is a layer around the engine API. The engine API knows only how to execute BLR. Hence the DSQL API does passes the stored BLR to that lower level functions (engine API).

For engine API, there is no knowledge of DSQL nodes and the processing done in the semantics phase. The engine does a parse of BLR, reconstruct another tree and compiles it. That does almost what DSQL does, but DSQL does it on SQL code and engine does it on BLR bytes.

When a statement is executed, the BLR tree is traversed and everything runs.

So back to DDL... DDL is not described as BLR, but in an equivalent binary format: DYN. Some DYN verbs have BLR bytes embedded, like the body of a stored procedure, but that is not relevant for generic DDL handling.

As in DML case, the engine knows nothing about DDL nodes constructed in DSQL. The engine knows only how to run DYN, with the isc_ddl API. In DSQL, everything works as in DML, but the generation phase emits DYN bytes, and statement execution is layered around isc_ddl.

So you might ask, what's wrong with DDL execution if it's so consistent with DML?

A lot of things, I'd say:

  • DYN codes are very badly structured. Some codes that would be private for some specific verbs shares the "number space" of all verbs. That make things visible wrong.
  • Related to above, we're going out of space. In v2.5, we're at number 247. We could go only at 254, and use the 255 to make a second number space. This would make things even worse.
  • While some may say that BLR is also bad, this is a model not invented in InterBase/Firebird. This is how compilers works (assembly, byte codes, etc). But that model does not have any practical benefit in DDL.
  • Too much code is used to convert DSQL structures to bytes and to engine again.
  • There is large code duplication to handle similar things, like CREATE / ALTER commands.
  • DYN execution is totally unnatural for a RDBMS system. A CREATE PROCEDURE command is more or less defined this way:

isc_dyn_def_procedure, <name>,
isc_dyn_prc_source, <source>,
...,
// for each parameter - begin
isc_dyn_def_parameter, <name>,
isc_dyn_prm_number, <number>,
isc_dyn_prm_type, <type>,
...,
isc_dyn_end,
// for each parameter - end
isc_dyn_prc_blr, <bytes>,
isc_dyn_end

And is executed in this way (pseudo-code):

function DYN_execute()
{
    while ((dynVerb = getDynByte()) != isc_dyn_end)
    {
        switch (dynVerb)
        {
            case isc_dyn_def_procedure:
                DYN_define_procedure();
                break;
            case isc_dyn_def_parameter:
                DYN_define_parameter();
                break;
            ...
        }
    }
}

function DYN_define_procedure()
{
    procName = getString();

    while ((dynVerb = getDynByte()) != isc_dyn_end)
    {
        switch (dynVerb)
        {
            case isc_dyn_prc_source:
                procSource = getString();
                break;
            case isc_dyn_prc_blr:
                procBlr = getBytes();
                break;
            ...
            default:
                DYN_execute();
        }
    }

    INSERT INTO RDB$PROCEDURES
}

function DYN_define_parameter()
{
    paramName = getString();

    while ((dynVerb = getDynByte()) != isc_dyn_end)
    {
        switch (dynVerb)
        {
            case isc_dyn_prm_number:
                paramNumber = getNumber();
                break;
            case isc_dyn_prm_type:
                paramType = getParamType();
                break;
            ...
        }
    }

    INSERT INTO RDB$PROCEDURE_PARAMETERS
}

Did you see what is wrong? Did you ever asked why Firebird system tables does not have primary and foreign keys?

The answer is simple, there is no way to have a FK from RDB$PROCEDURE_PARAMETERS to RDB$PROCEDURES if the records in RDB$PROCEDURE_PARAMETERS are inserted first.

With these problems defined, I started an alternate DDL path in v2.5, with the ALTER CHARACTER SET command. This command does not emit DYN, and when asked to execute, it does execute directly in DSQL with a new C++ friendly node (also used in the parser).

This was not sufficient through. To have benefits, existing commands shall also be migrated to the new scheme, and DYN should better be totally eliminated. The problem of this is that, documented or not, DYN and isc_ddl are part of the API, and used by client tools. The GDEF utility is totally based on it, as well DDL commands of GPRE.

These archaic utilities are the reason of how things had been defined in this way and why it never changed. Natively, former InterBase versions didn't understand text commands. DDL and DML had been part of client utilities, that compiles them and send BLR/DYN to the server.

Fortunately the Firebird team agreed to deprecate (in the sense of warn to tell to not use anymore) these things in v2.5, so it will be eliminated in v3.0.

In my branch for v3.0, I started to implement this conversion. Procedures and triggers had been refactored and their DYN handling was eliminated. The new external function was also done in the new scheme. With nice C++ classes, the architecture of PACKAGES became smooth. Also, the implementation of these commands became shorter, self-contained and much more readable.

Yesterday, I did a step further and did some unification of EXECUTE BLOCK (DML) with procedures and triggers (DDL) code. A prototype of sub-procedures is in the way...