Searching for the perfect in memory database
I've been using HSQLDB for my in memory database testing, but I think its time to find something else. Things have been getting pretty painful lately and I'm wondering what kind of experience others have with the alternatives. My criteria are:
- fast
- easy to embed in a Java test suite - so basically just drop in the database, a property file and the jdbc driver and go
- excellant support for the sql standard - including all the outer joins, some core set of conversion functions, etc
- support full outer join
- did I mention fast?
My latest query is doing full outer joins and HSQLDB doesn't like it. Just checked out Sqllite, but it has the same problem as HSQLDB - left outer joins supported, but nothing else.
Derby looks like an option, as they claim support for right outers and left outers, but no full outer. I may take a look at Firebird next, but I can't find much on doing things embedded in an app without too much pain.