Here's what I've tried so far, and why it was a miserable failure:Firebird:
This one almost
worked... I actually used it as a test backend for a bit, but it requires two DLLs (the .NET provider
and the engine dll) and it's larger than the other options (FB's 2MB vs others' ~250KB). Those aren’t show stoppers, though, and if none of my other options panned out, I was planning on going back to it as a "last resort". But then I found out that Firebird's embedded version can't be run on a network drive (which TR is sometimes run from)... so there goes my fall-back option. SQLite:
This one was out of the running pretty quick because it's not thread safe (despite what they claim about it
)... write operations lock the ENTIRE database *ack*... TR is heavily
multi-threaded, so that one didn't last long. It also requires two dlls (the .NET provider
and engine), but that wasn't what killed this option.BerkeleyDB:
This one doesn't have a .NET provider (nor is it SQL based). The Berkely XML
DB has a .NET provider (available from a third party) but who the heck wants to use XML for data storage? db4o:
This one is an object mapping db (basically).... I needed one to handle SQL. It also has some weird quirks like storing BLOBs outside of the database in individual files (whuh???) and since most of my objects are stored using binary serialization, I'd have a heck of a lot of extra files. (UPDATE: I was told by someone at db4o that the external BLOB storage was optional and could be turned off, but since I'm looking for an SQL db, this still didn't suit me)MSDE/JET/whatever:
All the Microsoft options were pretty much worthless right out of the gate, considering they have a foot-print that's.... oh.... I dunno.... fifty THOUSAND times the size of TaskRunner itself.
OK... so maybe I'm exaggerating... but they might as well be that big... no one is gonna want to download 45 freaking MB of backend database just to use a ~500KB app. It's bad enough that they have to download the ~23MB of .NET Framework, if they don't already have that.VistaDB:
I found out why this option wasn't working when I tried it the first time. An updated analysis of VistaDB can be found here
.) This was the most widely touted alternative to MS's options (seriously... the first ten pages of Google results for ".NET embedded database" is all them.
), and they seemed like they had their act together.... but their db costs money, so that's reason enough there not even to consider it.... but... at one point I was seriously considering paying for their db, just so I wouldn't have to deal with this stupid issue any more. (that's how desperate I was... and trust me... a nerd who is willing to actually pay cold, hard cash for software is one desperate nerd.
) So I tried their evaluation version, and it had more problems than most of the other options... lol. I kept getting errors when trying to open a connetion (both after creating the DB and after re-starting the app) and I couldn't create tables using DDL (for some reason). I can't say that their product is "bad", 'cause I didn't spend more than a couple of days (off and on) trying to get it to work... so there may be a reason for each issue I was encountering, but I can say this... every single other db that I tried (that had a .NET Provider) I was able to plug right in and go (by just changing the API code used to create the DB). Once the DB existed, all the .NET provider objects worked the same from one DB to another... except with VistaDB.
So... after all that, you're probably thinking that I gave up and went to cry in a corner... well... I can't gaurantee that there wasn't any crying involved, but I didn't give up. There was still one.... last... option...SharpHSQL:
I've been using #HSQL for the last month-ish while doing the TR upgrade, so thus far, it has been a functioning backend db. It is very thread safe (tested it every way from Sunday in that respect), it has a .NET provider, it's really small (~200KB or so compiled), and the best part is... it's written entirely in C#, so I can compile it right into my application. So here's where you'd be asking yourself, "So what's the problem??" Well... the problem is, it's still a little buggy... and there were a few flaws that made me ready to drop it altogether... but I stuck it out, and I think I have a working model now. The first issue involved committing transactions. If I executed a series of SQL commands and then killed the app, the last command (or two.. or whatever) that was run is not maintained... at least... that's what I thought
. In reality... the transaction was being restored (on restart) but something about how I was coordinating the population of my listviews was making it look like that object hadn't been saved. So I did some tweaking (and designed the Listviews to function like they should have in the first place) and that problem went away. The other issue was/is corruption of the db that happens when killing the app. Sometimes, not always (very flaky), after killing the app, you can't open the db again... something about corruption of a cfg file. So that's a big problem... I've got an idea, though, on how I'm gonna remedy this, so at this point, I'm thinking that SharpHQSL is my db of choice. (Update
: I have fixed the termination issue since first writting this entry. I talked it over with the #HSQL guy and came up with a way to mirror the cfg file, so it doesn't get corrupted.)
It does have a couple annoyances that I had to get around, though. It uses a number of db files (I prefer a single file), one of which is a transaction log file... which is stored in a plain text file
!! So you can see every single transaction being done on the database, including login
... lol. So don't bother using SharpHSQL if you need a secured backend... the user can just open the log file using Notepad and see what the passwords are. (doh!
) Anyway... those were minor things that didn't knock #HSQL out of the running. Plus, the guy keeping it up has been responsive so far in fixing every bug that I've found (which has been nice). He has kinda stalled on this app termination issue (which is why I'm trying to write a workaround myself), but it's good to know you can get quick turnaround from a developer when you find a bug.
At this point, I'm fairly settled on using SharpHSQL (assuming I can solve the app termination issue), but I'm still open to other options, so if anyone knows of a good, embedded db, just contact me or throw a post up on The WALL.
The issues that SharpHSQL has with crashes and multithreading caused it to be too unreliable to use as a stable backend, so I gave up on it... I did find an alternative... see here