About Visual Studio, SSIS and DSLs

I have had the opportunity to discover Sql Server 2005 Integration Services (SSIS) lately. That was a double blow. At first, I found it highly usable, though rough on the edges : still only a very promising tool. And then I found it was the perfect example of a software factory created with the Visual Studio Domain-Specific Language Tools. It is great to see Microsoft eating its own dog food, and create a great tool with it.

I kept playing working with it for a while and then I slightly became more and more frustrated by this experience. For instance, the process of modifying a data flow is tedious and laborious if a data source structure has been changed. And as happened with the Windows Forms designer, I soon felt the urge to bypass the designer to quickly mess with the code. My deep feeling is that designers are great to get started, editors are great to get finished. But in the case of SSIS, there is no such thing as code. All you can have is a XML file containing your flow design, messed up with presentation concerns, useless metadata, magical numbers... Great for machines, useless for humans.

The problem here, is that you have no Domain-Specific Language (DSL) per se. Or, better, that the DSL has been generated by a machine. Some people have identified this flaw and felt the need for proper code, even for ETLs. I have even found a code only ETL being developed. This, in my opinion, is the right thing to do, and I shall remember it when I design my own Software Factories : a DSL is mandatory, the designer is here for comfort.

SSIS is a great tool. It just lacks a DSL.

HowTo : Log DDL events in your Oracle database

Update (30/10/2008) : trigger code updated, due to a bug pointed out by Michel in the comments.

Databases should be under version control. Not only that, but I believe that SQL scripts should be treated as regular code : can your database be built in one step ?

But if you do that, you will want to scripts database changes too. This seems quite easy to do if you manage the whole application, but far less so if your it falls under the responsibility of, say, your customer DBA. But in any case, I think it is a good idea to track all the changes made on the structure of your database. So here is some simple scripts that I use on a daily basis to achieve this.

First of all, create a table 

CREATE TABLE log$ddlevents
(
    EventDate DATE DEFAULT SYSDATE NOT NULL,
    Owner VARCHAR2(30),
    EventType VARCHAR2(30) NOT NULL,
    ObjectType VARCHAR2(18) NOT NULL,
    ObjectName VARCHAR2(128),
    DatabaseUser VARCHAR2(30) NOT NULL,
    OsUser VARCHAR2(30),
    MachineName VARCHAR2(64),
    ProgramName VARCHAR2(64),
    SqlText VARCHAR2(4000)
);

This table can be automatically filled thanks to the appropriate trigger :

CREATE OR REPLACE TRIGGER trg_ad_logddlevent
    AFTER ALTER OR CREATE OR DROP OR RENAME ON SCHEMA
DECLARE
    sqlTextPart ora_name_list_t;
    sqlText VARCHAR2(4000);
    i PLS_INTEGER;
BEGIN
    FOR i IN 1 .. ora_sql_txt(sqlTextPart)
    LOOP
        -- Make sure that we only retrieve the first 4000 characters
        DECLARE
            l PLS_INTEGER; -- Length of the current string
            l1 PLS_INTEGER; -- Length of the concatenated string (can be >=4000)
            lp PLS_INTEGER; -- Length to be concatenated
        BEGIN
            SELECT NVL2(sqlText, LENGTH(sqlText), 0)
                INTO l
                FROM DUAL;
            SELECT l+NVL2(sqlTextPart(i), LENGTH(sqlTextPart(i)), 0)
                INTO l1
                FROM DUAL;
            SELECT DECODE(SIGN(l1-4000), -1, l1, 4000-l)
                INTO lp
                FROM DUAL;
            sqlText:=CONCAT(sqlText, SUBSTR(sqlTextPart(i), 1, lp));

            IF (l1>=4000)
            THEN
                EXIT;
            END IF;
        END;
    END LOOP;

    INSERT INTO log$ddlevents (
        Owner,
        EventType,
        ObjectType,
        ObjectName,
        DatabaseUser,
        OsUser,
        MachineName,
        ProgramName,
        SqlText
    ) VALUES (
        ora_dict_obj_owner,
        ora_sysevent,
        ora_dict_obj_type,
        ora_dict_obj_name,
        ora_login_user,
        SYS_CONTEXT('USERENV', 'OS_USER'),
        SYS_CONTEXT('USERENV', 'HOST'),
        SYS_CONTEXT('USERENV', 'MODULE'),
        sqlText
    );
END;