Thursday, July 26, 2007

For the love of Embedded databases

mmm...there are a lot of dev blogs out there that focus on information for developers. they have guides, reviews and code samples.
i might do that (review, guide, code samples etc' ) from time to time, but really there's so much out there that i find it somewhat redundant to have yet another post on some tech or another.
the reason i am still posting this one is to share the results of some research, and to share what might happen when you fall in love with some crazy idea of embedded databases.

so , anyhow, today's topic is my experience with setting up O/R mapping and it's relational DB using .net.

first a short intro:
O/R Mapping stands for 'Object - Relational mapping' and it's a concept that makes databases a more useful , easier to work with thing. (it gets very close to making them worth while ;) ).
basically, it defines a way to easily map objects into database tables and structures. its a rather complicated problem when you start thinking about how to represent parent/child relations, or one to many ..or collection...joins...and other weird things db people like to do.
if you want more details, please check out the source of all knowledge for more details.

in the java world, there's only one O/R mapper worth its while - Hibernate. its a very powerful , well written , well maintained , open source O/R mapper.
the grate thing about .net is that its so much like java that all the cool projects out there started porting to .net (Spring, Log4Net, NUnit to name a few). its so much like java that you only have to learn how they renamed the classes and packages. the rest is pretty much the same.

anyhow, in the .net world there seem to be a few others. but i know the way the hibernate people do the job and i trust them more then some other projects. especially since they are amongst the few that are open source for .net . also, they already solved alot of the problems when they where writing the java version, so i guess they can do it better then other inexperienced dev's.
One other project i particularly liked the idea of was db4O. it used to be an open source, and is still free for non commercial use. its not actually an o/r mapper , but an embeddable object based database.
unfortunately , i am a commercial user , so we preferred the open and free option.

at first i thought i wanted an embedded database. it sounded so cool - no installation for the user, no DB administration for the user, none of the annoying hassle of a full blown DB.
sure, it wouldn't have the same performance , and it might have trouble working with too many concurrent users , but our project doesn't need either. but, as bono once said - love is blindness*.

since i already decided on hibernate , there was only the question of which db to use. this means we need a db with a dialect hibernate understands (one might think that SQL is enough of a standard , but as you soon find out, its very far from being standard. ). this gave me a very short list to work with. some databased i already knew and knew i didn't want :
Oracle - expensive and annoying
MS SQL - expensive and annoying, tho probably not as much as oracle
Access - not really a database
DB2 - expensive , annoying and made by IBM(to relieve and doubt - being made by ibm is a bad thing. one might think that its a big prestigious company , and it is. but their products suck. badly.)
so that left the databases i never heard about , and MySql. i'v always heard people say that its a grate open source database , and that everyone is using it. i'v never met someone who told me they where actually using it (they all use Oracle...those poor bastards)
now, MySql was such an obvious choice that i had to go with something else. and besides i was inlove with the concept of a the three no's of embedded databases : no-installation , no-configuration , no-administration.
so i checked out:
SqlLite - looked awful , no smooth integration with C# as far as i cared to look.
PostgreSQL - seemed nice but wasn't really embeddable.
Firebird - seemed nice and was also embeddable.
so i decided to give firebird a go. everything was fine for a was a bit annoying to install it's driver and it took some effort to properly configure nhibernate due to some mistakes in their guide (which is very much unlike them , particularly since the corrections where present as user comments on the bottom of the configuration page) , but anyway, i got it to work. i was very happy with my shiny new embeddable database, and then DISASTER! i started getting : Internal gds software consistency check (cannot find tip page (165)).
hmmm... i thought to myself...this cant be a major thing, i just finished setting up the db, just committed my first tiny bit of data...i probably configured something wrong. quick google search turned up this page ... and...oh my...not only is this not a configuration error, its a critical DB error....database is completely destroyed....
Wait a minuet...when i started reading up on firebird ..all this backup, repair and fix tools where mentioned...1 .... + ..... 1 = wtf???
well, i guess i am fortunate it failed so soon. it would have been far more disastrous if these crashes only happened after we delivered it to our customers.

for the random google searching user i will state this plainly :
Don't use Firebird. don't use it with .net . don't use it with NHibernate. Just don't use it at all.

i ran some more searches, looking for some obscure database i might have missed but no go.
i also took a short look at the embeddable version of MS SQL express edition, but it only supports a max 4 gig database (it has other annoying limitations as well) it was useless for our app.

eventually i came back to MySQl... as it turns out , if you want a free, open source real DB...its the only serious option out there.

EDIT: read update at the end of this post , turns out MySql is not actually free.

it took half an hour to install and configure it to work with hibernate. and aside for an annoying problem i had starting it up (i wanted to use UTF8 as the charset because our users might need internationalization set it up in MySQL you need to enter a freetext string, so i put in UTF8...turns out you have to put in lower case utf8....the the exception you get is just perfect "key not found in dictionary" worse yet - there's a gui and a ""test connection" which tells you everything is ok even tho you configured a nonexistent charset. how i figured it out i still don't know..i guess you come to expect silly things like that when you do this sort of work long enough) but after that everything seems to be working.. no complete database crashes for no reason , fast responses, and some nice db management tools.

As for my initial infatuation with the concept of embedded db, well, they** say "you never forget someone you loved" i still think some day embedded db's might be something we take for granted in every application. in the mean time we can all tell everyone that everyone is using mysql.

Join us next time for "Transparent Controls or: how i learned to stop trying and hate windows forms"

UPDATE (8/10/2007): German Viscuso mentioned that MySql isn't actually free. its distributed by a dual license - the dirty trick fake open sources use (free means free. not 'free under the following conditions') - a GPL infective**** or a Commercial license. so we turned to the only available alternative : PostrgreSQL. supported by NHibernate, works flawlessly and most impotently , doesn't infect my company with an evil licenses (PostgreSQL is distributed under BSD license - almost completely free. basically all they want is the credit for the db they wrote...) .

* - i told you it was the source of all knowledge :)
** - 5 points for anyone*** who guesses which happened first - i thought up the line, or i saw the quote in that link.
*** - only one vote per ip , the points don't matter.
**** - GPL is like a virus - if you want to use something licensed under GPL, you have to use GPL yourself. ad infinitum. they even have a philosophy as to why they have to make you be just as 'free' as they claim they are. so , in the eyes of the GPL people - software should be 'free' as long as they get to define the meaning of freedom.


German Viscuso said...

"it used to be an open source, and is still free for non commercial use"


That's not accurate. db4o IS an open source project licensed under the GPL. And also offers a commercial license for companies that cannot use open source software. It's a dual license model (the same as MySQL or Trolltech). So it doesn't matter whether you're a die-hard open source advocate or a company looking to buy a persistence solution. In both cases you can benefit from db4o.

Best regards and thanks for sharing your thoughts!

Yossi Naar said...

Point taken.
i looked at the MySQL license and now it seems we won't be using it either.

What happened to the "do whatever you want" model in open source. :)

if something is free, it should really be free and unconstrained.

Jeremy said...

You could have just phoned me and saved yourself a bunch of time...