Sunday, May 9, 2010

Free for commercial use + high performance + feature rich + client server + embeddable databases

Here's why what is tested and rejected:

SQLite: Client+server is not good (SQListening has a few limitations so don't go there! e.g. multiple columns with same name cannot be returned)  + writes lock the whole file (under milliseconds and reading is still allowed ... but you should be aware ... ) . And due to inherant complications of reliability of file sharing systems it cannot work over LAN (biggest bummer). Drop column is not supported (and that is something that my database will do quite commonly ... counter activation/deactivation etc. )

MySQL: Not free for commercial use (GPL). 500$ . Not to mention that oracle now owns it (and I am still convinced it will result in a conflict of interest). PS: it is highly feature limited.  Note : Interbase(IB) was the original source of Firebird in case you did not know.

ScimoreDB: Great but not feature rich (e.g. no upsert support , limited data types ... improved in version 3.0 (RC version) and password protection to be added by 3.5 ... but not yet )

So it boils down to:
Note both are GREAT databases. This is just my opinion as I have to make a choice of one :) .

+ Easier to Embed
+ Lesser strict system requirements
+ Easier to master :) .. with the right book ... "The Firebird Book"
+ Smaller installation (under 25 MB if you go crazy and include everything redundant as well)
+ Mature Entity framework Visual studio designer.
-ve Does not free disk space on deleting old data (put does recover it for usage by new data). You need to backup and restore. Will be fixed in firebird 2.5 though :)

+ .net 4.0 compiled driver already available ( may/9/2010 ). .net driver is called npgsql
+ more features (but I don't need them) e.g. PL/Python!  + partitioning + HUGE tables + partitions
-ve Can only work on NTFS. Not neccessarily a bad thing at all. They dont trust FAT ... and I don't blame them :). I wouldn't put firebird on it either (cause it will be limited to 4GB and once that overflows your database has a high likelihood of corruption)
-ve 30MB+ install. Not bad at all but still.

You should try out both. Tell me what you chose. I chose Firebird. It has greater .net support ... and .net guys seem to like it more .. probably because of its easier portability (one file db .. no installation required for running firebird on windows)

BTW for PostgreSQL I am reading PostgreSQL 8 for windows.
For Firebird I just read the manual and then reading  "The Firebird Book".