I have refactored the HSQLDBPersistenceManager to follow JDBC best practice.
I.e. use of prepared statements, closing resources in finally blocks etc.
The TRANSACTED_MESSAGEREFERENCES tables has been removed as part of the tx refactoring.
Each table (MESSAGE_REFERENCE and DELIVERY) that can store transacted data has a column "STATE". (similar to TX_OP in JBoss MQ)
It can have one of 3 values:
"C" - the row contains committed data
"+" - the row was inserted as part of the tx
"-" - the row was deleted as part of the tx.
"+" and "-" have basically the same meaning as "A" and "D" in JBoss MQ db.
Each transacted table also has an extra column "TRANSACTION_ID"
And we also have a new table "TRANSACTION" (same purpose as JMS_TRANSACTIONS) in JBoss MQ.
Note I have used the singular rather than the plural to name tables as per standard db design convention.
Primary keys have been added to relevant tables.
We still need to change some of the datatypes to more appropriate ones, possibly add another table to store properties that can be used by selector expressions, and choose our indexes.
I would also suggest renaming the class from HSQLDBPersistenceManager since it has nothing specific to HSQLDB in it afaict.