+972 52-548-6969
In search of an embedded DB
I need to use an embedded DB in a project. Naturally, I turned to my old friend, SQLite. It has served me well in the past, and I am well versed in all its tricks. Except... SQLite really fall apart when you are talking about even moderately multi threaded applications. By that I mean, it works just as advertised, for sure. It just lock the entire DB when used, which tend to kill other threads who want to access the poor DB.
I then turned to FireBird, but a short spike told me that it is... inadequate. I managed to get the database into inconsistent state, but I am not sure how or why. There seem to be very little information about it as well, so at the moment it is out of the game.
Sql CE is worse than SQLite when it comes to handling multiple threads, so that wasn't even in the running.
Vista DB is commercial, and I plan to make this an OSS project, so that is out as well.
I want to use Berkeley DB, but is seems like the .Net story is hard. There are CLR Binding, but I run into several blocking issues with them. There is no 1:1 mapping to the C API or the Java API, the error messages are... terse. And there aren't any tests at all! Even worse, the code is interfacing with native code, so it is C# after a rail road has hit it at 200 kph and having spent three days crying in a desert.
I put some time into trying to get this to work, and once the initial hump is over (understanding the association between environments, databases and transactions), it makes a lot of sense. Of course, there are a lot of features that I wouldn't even try to figure out.
First order of business, create a .Netified wrapper that will abstract the C like API away from me.
Okay, I am being ungracious here, the API make a lot of sense and the code is good for the purpose it was built, it was just that I had a moment of existential uncertainty when I looked at the code and it looked like C++#.
Comments
I'm using Berkeley DB to store diagnostic data (several Gb at month), the way to C# is C -> C++/CLI (managed API wrapper) -> C# (client).
Have you looked at SharpHSQL?
http://www.codeplex.com/sharphsql
Have you tried H2? It's a rewrite of the Java Hypersonic DB that SharpHSQL is based on by the original author. It works under .Net via IKVM. There is also an ADO.Net wrapper for it called H2Sharp.
http://www.h2database.com/
http://www.h2database.com/html/frame.html?advanced.html%23microsoft_dot_net&main
http://code.google.com/p/h2sharp/
Maybe db4o will be good for u in case u don't mind OO db.
Db4o? It plays with OSI compliant licenses and is optimistically concurrent by default.
I used Firebird for a .net app that supported embedded and on server database. The tools and documentation suck, but it's not very hard to use
I've used Db4O in a few projects as well. And like it allot, the new version also has LINQ support.
Bamboo Prevalence?
I'm using embed Firebird for a commercial application. It's really nice, easy to use and ease to upgrade to server database, except:
No encryption for embed database. You event don't need to provide a password to connect to the database even though you've set the password when you create it.
Application hang after exit. You have to explicitly call FbConnection.ClearAllPools. Put that in a destructor doesn't seem to always do the job.
Anyway, I"m using NHibernate, so change of database engine will not be a hell.
We've been sadly coming to the same realization with some recent experiences as well. Looking forward to hearing what you conclude.
Does "data source=.\sqlexpress;AttachDbFileName=path\file.mdf;Integrated Security=True;User Instance=True;" land too far from your requirements? Out of process, but it runs on a named db file, and with calling app credentials. Installation overhead -1.
What is the managed wrapper that you use?
Is it available?
Louis,
You need to install SQL Server Express for that, so there is installation overhead
I've never used it, but what's wrong with compact edition?
http://www.microsoft.com/sql/editions/compact/default.mspx
@Ayende:
is the need to bundle SQL Express is that bad?
You'd gain an excelent tool for free.
as for Firebird - it doesn't sound right. I had some experience with firebird, and apart from the fact that back then handling BLOBs was a PITA, it performed very well. Is it possible that you were using a dodgy .NET FB driver?
Perst.NET:
http://www.mcobject.com/perst/
I used firebird with a toy app I wrote (auto parsing of job posting and sending emails with resume attached). It was well behaved and performance was fine for what I was doing... until I decided to make it work with Mono too. I'm not sure if it was switching the db driver or some change I made, but I started getting weird results. I got a job and didn't really look at solving it.
firebird is still my embedded db choice, but I'll be looking into suggestions from other commentors.
Whats with VistaDB "Express"
"You can now use all the great power of VistaDB on your personal websites and other non commercial projects for FREE."
You should really check out db4o (DB For Objects, www.db4o.com). They had really done a good job in the data persistance area. And there are many tools for it that run on .NET and Java.
I have used it in commercial applications for Windows Desktop, ASP.NET Websites and Windows Mobile applications and never had a problem and they all got good performance in final users criteria.
Scott,
Utterly not thread safe.
Can leave the database locked and require a process restart to continue working.
Ken,
I am using FirebirdClient, which is pretty decent, as far as I understand.
The issue is that a) no tools, b) no docs for even the SQL dialects, c) I managed to corrupt the state very easily.
In general, it is a bad beginning, and I feel that while I could probably overcome it, there isn't a good reason to try,
ZODB works very well as an embedded persistent Python object store (handles mutli-threaded concurrency issues). Of course, it only works w/ Python, so ...
Firebird is definitely adequate: I've used it for many years in large, heavily stressed, enterprise applications. I think its simplicity, stability, and performance are difficult to match.
Derby
I bumped into this problem a few years ago; it just doesn't seem to go away. http://weblogs.asp.net/jgalloway/archive/2005/07/08/418474.aspx
Time for AyendeDB!
Comment preview