Java Database Connectivity (JDBC)

Hi,

I'm creating some unit tests and set up an H2 in memory database. Part of the set up executes the DDL scripts used to create and upgrade the database. My problem is that there is some syntax not supported by H2. So far I've found that I can't specify DEFAULT nextval('sequence_name') on a column in CREATE TABLE, and some limitations on CREATE ROLE. H2 supports "identity" data type for auto incrementing, but PostgreSQL calls it bigserial.

Some ideas I have about this:

1) Maintain separate DDL scripts for my test environment. They will be almost duplicates of production DDL, with tweaks to work in H2.
2) Get rid of H2 in memory database, and run all the scripts in a real PostgreSQL server. Before starting the tests clear out all tables, sequences, users, etc. then rebuild the DB. I'll need to make sure that there is never more than one system executing tests on the same postgres server at the same time. Perhaps developers will configure their systems to execute tests on a local postgres server, and our CI server will be configured to execute tests on it's own local postgres server.

What do you guys do?


Thanks,
Ryan