Migrating project from MySQL to embedded, relational Java-Database

Discussion in 'Java' started by Ingo R. Homann, Aug 14, 2007.

  1. 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!)
     
    Ingo R. Homann, Aug 14, 2007
    #1
    1. Advertising

  2. 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
     
    Thomas Kellerer, Aug 14, 2007
    #2
    1. Advertising

  3. Hi Thomas,

    Thank you very much for your feedback!

    But some more questions...:

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


    Where do you get that from?

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


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

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


    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!

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


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

    Bye,
    Ingo
     
    Ingo R. Homann, Aug 14, 2007
    #3
  4. Ingo R. Homann wrote:
    >> because of the backing community which products like HSQL and H2 lack
    >> (mostly driven by a single developer)

    >
    > 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-database/browse_frm/thread/2a9f146ab56b3778/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.

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


    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
     
    Thomas Kellerer, Aug 14, 2007
    #4
  5. 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
     
    Ingo R. Homann, Aug 14, 2007
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?S2VubmV0aCBQ?=

    Migrating schemata from MySql

    =?Utf-8?B?S2VubmV0aCBQ?=, Mar 14, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    300
    =?Utf-8?B?S2VubmV0aCBQ?=
    Mar 14, 2005
  2. Brent Ritchie

    XML as a relational database?

    Brent Ritchie, Aug 27, 2003, in forum: XML
    Replies:
    5
    Views:
    504
    Rosimildo da Silva
    Aug 28, 2003
  3. Brent Ritchie

    RE:XML as a relational database?

    Brent Ritchie, Aug 29, 2003, in forum: XML
    Replies:
    2
    Views:
    592
    Scott Raymond
    Aug 30, 2003
  4. JAF
    Replies:
    13
    Views:
    498
    Simon Brooke
    Nov 26, 2006
  5. sonos
    Replies:
    25
    Views:
    930
    Tor Rustad
    Aug 31, 2007
Loading...

Share This Page