Saturday, May 19, 2007

Migrating to Derby (JavaDB) with schema

I went to a lot of trouble lately trying to migrate a database from MS SQL Server to Derby (aka JavaDB) [1]. I've used the DDLUtils ant tasks to export-import the database.

The major issue was that in my previous database we used a given schema and that schema wasn't recreated now by DDLUtils. I've tried tweaking the ant task parameters but still nothing. I even done some quick sed scripts [2] to add the schema myself (rename tables from "oldname" to "schema.oldname" basically). But all this meant nothing. Plus that the database was 100MB+ and import time was well over an hour so I couldn't actually try a lot of times.

The solution resided in the Derby FAQ but it wasn't until someone from the mailing list pointed it to me that it struck me:
The current schema for any connection defaults to a schema corresponding to the user name.
You see, I was creating the database beforehand with some random user like "admin". I didn't notice this was the default schema. Also, while reading the same FAQ, I just skipped to the part with SQL.

So: if you migrate to Derby, always create an username with the same name as your schema (the one you import).

1. I wouldn't actually put Derby on a production server but I feel better during development having the data in a more cross-platform DBMS . Plus, I'm spared of all the security issues MS SQL has (or at least my fear of having those ports open).

2. Yep, sed. I know some XSLT might have been smarter but I'm quite rusty at that and there is no easy xslt shell/script I could use.


David Van Couvering said...

Hi, Emilian. I saw your comment on Google Analytics and decided to check out your blog. I saw this entry on Java DB.

I am curious, why wouldn't you put Java DB in production? Many people are, but I know there are others who like you believe somehow that it's not production quality.

I'd like to either understand your issues and work to get them fixed or debunk them :)

Emilian Bold said...


Derby feels to me like a developer's DB (think sqlite/hsqldb with some extra features and some IBM history behind).

I frankly don't know about the transaction support, recovery options or how many parallel connections it can handle, but when I go to a customer, I would rather suggest a "big" DBMS that has stood the test of time and eventually has some paid-support at hand if needed. I see no need to risk suggesting some other DB since I'm not up-to-date at all with their specifics and data is really important.

Plus that, usually, the client already has some DBMS in-place (most likely not Derby) and I use that.

There's a lot of hear-say in my little note above. Basically if I have a choice, I use PostgreSQL and I avoid MySQL (which needed some special engine to have referential integrity); I also avoid anything not open-source. All the other DBMS are just fighting for my attention, but I like to focus on coding stuff.

A conservative, shallow DB user

Fair Source and the Fair Source Initiative

There's been some uproar about the MongoDB Server Side Public License which tries to prevent cloud vendors like Amazon take all the mon...