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.

2 comments:

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...

Hy,

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.

Signed,
A conservative, shallow DB user