Lately we have been tasked with porting some existing DB infrastructure from PostgreSQL to HSQLDB. Yes, I know what you're thinking (why on Earth would anybody do such a thing?). But unfortunately for reasons I can't explain here this is our task and there's no way around it.
We don't need to port any data, just the queries: we need to plug an HSQLDB layer into our software to replace the PostgreSQL layer.
There are thousands of hardcoded PostgreSQL queries -- and while I thought initially that HSQLDB syntax is similar (somewhat compatible) with PostgreSQL syntax, I was quite surprised. For instance, statements including
SERIAL,RETURNING,- and other more complex queries involving foreign keys,
are not supported in HSQLDB. Note that we are using the latest HSQLDB driver (2.5.1), but if any other version is more compatible that's fine as well.
I can of course go through each and every query and ''translate'' them into HSQLDB syntax, but that is not very efficient.
Any ideas?
