Creating TestNG casesI was having issues creating proper data sets for our test suite since the data model is so complex. I was manually instantiation each class’ entity, populating the object, persisting, then committing the transaction manually. It became obscene for certain objects that referenced up to ten different classes with multiple constraints each. We already have SQL scripts to pre-populate our local JBoss environment so it was a possible solution to use that same data for our tests. I posed the question of a better solution on the interweb and it was suggested I try another DBMS that supports DB2 used by our site and also operates in-memory to increase speed. We already were using Hypersonic but it doesn’t support SQL import from DB2. So I tried H2, using this JDBC connect string to enable the DB2 compatibility mode and trigger our create + populate scripts on the test suite’s startup.

jdbc:h2:mem:localDB;MODE=DB2;INIT=RUNSCRIPT FROM ‘~/workspace/database/create.sql’\;RUNSCRIPT FROM ‘~/workspace/database/populate.sql’

As the H2 features page indicates, “for certain features, this database can emulate the behavior of specific databases. Not all features or differences of those databases are implemented.” Enter what I found out was in fact DB2 incompatibility mode.

CREATE TABLE MAIL_RECEIPT (
    MAIL_RECEIPT_KEY INTEGER NOT NULL,
    DATE TIMESTAMP NOT NULL,
    USER VARCHAR(255) NOT NULL,
    CONTENT VARCHAR(32) NOT NULL,
    FROM VARCHAR(255) NOT NULL,
    TO VARCHAR(1000),
    CC VARCHAR(1000),
    BCC VARCHAR(1000),
    SUBJECT VARCHAR(255) NOT NULL,
    BODY VARCHAR(2000) NOT NULL,
    PRIMARY KEY (MAIL_RECEIPT_KEY)
);

org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement

DB2 allows FROM as a column name. H2 does not. And no, I didn’t originally write this schema so don’t blame me for its poor naming.

CREATE VIEW REPORT_CAGE_MOVEMENT AS
        SELECT CURRENT_ROOM.ROOM_NAME AS CURRENT_ROOM_NAME,
                CURRENT_ROOM.ROOM_ID AS CURRENT_ROOM_ID,
                ANIMAL_PROTOCOL.PROTOCOL_NUMBER,
                PERSON.LAST_NAME,
                PERSON.FIRST_NAME,
                CAGE.BARCODE_VALUE,
                MOVE_FROM.CAGE_ID AS CAGE_ID,
                MOVE_FROM.ROOM_ID AS FROM_ROOM_ID,
                MOVE_TO.ROOM_ID AS TO_ROOM_ID,
                MOVE_FROM.TO_DATE AS EXIT_DATE,
                MOVE_TO.FROM_DATE AS ENTRY_DATE,
                FROM_ROOM.ROOM_NAME AS FROM_ROOM_NAME,
                TO_ROOM.ROOM_NAME AS TO_ROOM_NAME
        FROM CAGE_MOVEMENT AS MOVE_FROM
        INNER JOIN CAGE_MOVEMENT AS MOVE_TO
                ON MOVE_FROM.CAGE_ID = MOVE_TO.CAGE_ID
                AND MOVE_FROM.TO_DATE + 1 DAY = MOVE_TO.FROM_DATE
        INNER JOIN ROOM AS FROM_ROOM
                ON MOVE_FROM.ROOM_ID = FROM_ROOM.ROOM_ID
        INNER JOIN ROOM AS TO_ROOM
                ON MOVE_TO.ROOM_ID = TO_ROOM.ROOM_ID
        INNER JOIN CAGE
                ON MOVE_FROM.CAGE_ID = CAGE.CAGE_ID
        INNER JOIN PERSON
                ON CAGE.CAGE_OWNER_KEY = PERSON.PERSON_KEY
        INNER JOIN ANIMAL_PROTOCOL
                ON CAGE.ANIMAL_PROTOCOL_ID = ANIMAL_PROTOCOL.ANIMAL_PROTOCOL_ID
        INNER JOIN ROOM AS CURRENT_ROOM
                ON CAGE.ROOM_ID = CURRENT_ROOM.ROOM_ID

org.h2.jdbc.JdbcSQLException: Column “CURRENT_ROOM.ROOM_NAME” not found

Aliases defined in an INNER JOIN clause cannot be referenced in the SELECT expression (isn’t that how Hibernate builds all its join queries?)

GRANT CONNECT ON DATABASE TO USER SOMEUSER;

org.h2.jdbc.JdbcSQLException: Table “DATABASE” not found

Uh…

CREATE TRIGGER BI_STRG_ALLCTIN
NO CASCADE BEFORE INSERT ON STORAGE_ALLOCATION
REFERENCING NEW AS NEW_STORAGE
FOR EACH ROW
MODE DB2SQL
WHEN (NEW_STORAGE.STORAGE_ALLOCATION_KEY IS NULL)
SET (NEW_STORAGE.STORAGE_ALLOCATION_KEY) = (NEXTVAL FOR TCP_POID_SEQUENCE);

org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement “…”; expected “AFTER”

Well it looks like a DB2 trigger doesn’t need that AFTER keyword.

So we’ve switched back to Hypersonic and I’m now creating an XML-based data set using DBUnit.