Migrating project from MySQL to embedded, relational Java-Database

[Ingo R. Homann]

Hi,

I want to migrate a project that is based on MySQL, to a different,
relational, Java-embedded Database (non-commercial, if possible).

"Apache Derby" was my first idea.

As usual, it will not be as easy as it could be (e.g., I heard, that
Derby does not allow an unique index on columns that may be null,
whereas MySQL has no problem with that).

My research with google gave the following projects:

- HSQL (http://hsqldb.sourceforge.net/)
Seems to be active and praises itself to be used in OpenOffice, which
seems to look good.
- H2 (http://www.h2database.com/html/frame.html)
The hompage does not look too professional at first glance, but at
second glance it looks better...
- One$DB (http://daffodildb.com/one-dollar-db.html)
(Free, open source version of DaffodilDB, see below. The name only
suggests, that the support is 1$ per day.)

I must say, that my evaluation has just started and that I did not
really test one of the projects.

Below, there are some projects which I rejected at first glance (perhaps
too fast). (*)

So, my questions are:
Are there any other promising projects I did not realize (or rejected
hastily)?
Did anybody use one of these projects?
Which experiences have you made with migrating applications to different
databases (especially from MySQL)?

Bye,
Ingo


(*) rejected at first:

- db4o (http://www.db4o.com/)
Object-oriented. SQL only per special module, AFAICS.
- Oracle Berkley DB Java Edition
(http://www.oracle.com/database/berkeley-db/je/index.html)
Problematic license.
- Axion (http://axion.tigris.org/)
Last acitvity 7'2003
- tinySQL (http://www.jepstone.net/tinySQL/)
Looks really half-baked (many features missing).
- SmallSQL (http://www.smallsql.de/)
commercial: http://www.smallsql.de/faq-general.html
- Quadcup QED (http://www.quadcap.com/home.html)
commercial
- ozone (http://www.ozone-db.org/frames/home/what.html)
Objekt-oriented, not relational!
- DaffodilDB (http://www.daffodildb.com/embeddeddatabase.html)
commercial
- Mckoi (http://www.mckoi.com/database/)
Last activity 8'2004 (is that correct?)
- Ashpool (http://robrohan.com/projects/ashpool/)
Project ceased 9'2006
- BlockFile (http://www.metanotion.net/software/sandbox/block.html)
Designed for PDA, J2ME. Very small solution (no transactionen, SQL and
JDBC!)

 

[Thomas Kellerer]

Hello,

I want to migrate a project that is based on MySQL, to a different,
relational, Java-embedded Database (non-commercial, if possible).

"Apache Derby" was my first idea.

From all I have seen so far, this would be my choice - especially
because of the backing community which products like HSQL and H2 lack
(mostly driven by a single developer)

As usual, it will not be as easy as it could be (e.g., I heard, that
Derby does not allow an unique index on columns that may be null,
whereas MySQL has no problem with that).

Not sure what you mean with "does not allow".
You can create an index on columns that allow null, but it won't allow
to insert the same combination more than once.
E.g. ('A', null) will not allowed twice which is e.g. the same way as
Oracle handles this.
Postgres seems to go the MySQL way and will allow multiple records with
the same "values". This is - according to the Postgres manual -
compliant with the ANSI standard.

- HSQL (http://hsqldb.sourceforge.net/)
Seems to be active and praises itself to be used in OpenOffice, which
seems to look good.

Actually there isn't that much activity on that project. But the
maintainer said that 1.9 is due this year. Following the forum on the
sourceforge site and the mailing list, for my personal taste there are
too many posts with the topic "Lost my data because...".
My experience (though not in a real-world production system) it is nice
for moderately sized databases but when the data starts to grow
(Millions of rows) it does not scale very well.

- H2 (http://www.h2database.com/html/frame.html)
The hompage does not look too professional at first glance, but at
second glance it looks better...

It does look promising and it sure has (currently) more momentum than
HSQL. But taking into account that it is relatively new, I would be
reluctant to use it in a production system.

Regards
Thomas

 

[Ingo R. Homann]

Hi Thomas,

Thank you very much for your feedback!

But some more questions...:

...
because of the backing community which products like HSQL and H2 lack
(mostly driven by a single developer)

Where do you get that from?

Not sure what you mean with "does not allow".
You can create an index on columns that allow null, but it won't allow
to insert the same combination more than once.
E.g. ('A', null) will not allowed twice which is e.g. the same way as
Oracle handles this.
Postgres seems to go the MySQL way and will allow multiple records with
the same "values". This is - according to the Postgres manual -
compliant with the ANSI standard.

Do you know if there are any flags or environment variables that allow
to change the behaviour of Derby in this context?

Actually there isn't that much activity on that project.

Latest news are from 28 July 2007.
Sourceforge says: 34 Developers.
Ah, OK, I see: most current build from September 27, 2006, nearly one
year. OK, indeed...

> But the
maintainer said that 1.9 is due this year. Following the forum on the
sourceforge site and the mailing list, for my personal taste there are
too many posts with the topic "Lost my data because...".
Uh-oh!


It does look promising and it sure has (currently) more momentum than
HSQL. But taking into account that it is relatively new, I would be
reluctant to use it in a production system.

Yes, the curent version is 1.0! I did not realize that at first sight!

Bye,
Ingo

 

[Thomas Kellerer]

Where do you get that from?

Judging from the Google Groups for H2 the main developr of H2 is Thomas
Müller (btw: the original brain behind hsqldb as well):

<http://groups.google.com/group/h2-d...46ab56b3778/47f86f2f5fa7e605#47f86f2f5fa7e605>

Taking the participation from the sourceforge groups for hsqldb, I'd say
there are two main developers for hsqldb: Fredt Toussi (who is the
maintainer as far as I know) and Campbell Boucher-Burnet. But I might be
mistaken with that. As I said: that is my personal perception of the
discussions in the forum.

I have been monitoring the Apache Derby mailing list as well, and my
imporession was that the team behind that is a bit bigger, and the wiki
pages http://wiki.apache.org/db-derby/DerbyFaces seem to support my
assumption.

because...".

Uh-oh!

Looking at a forum is usually a single-sided viewpoint, because only
people with problems participate, so I don't know how many out there
don't actually have problems. Taking the popularity of hsqldb into
account those people with problems are probably a very small percentage,
but still for my personal taste there are a bit too many posts in that
direction. Some of those problems are easily solved by reading the
documentation carefully though.

Do you know if there are any flags or environment variables that
allow to change the behaviour of Derby in this context?

No idea (mostly because I prefer Derby's/Oracle's implementation) but
you might want to ask that on the mailing list which is mirrored on
news.gmane.org (regular newsreader access)


Regards
Thomas

 

[Ingo R. Homann]

Hi Thomas,

thanks again for your quick feedback. That all leads me to your first
sentence in this thread: "From all I have seen so far, this (Apache
Derby) would be my choice."

Ciao,
Ingo