The scenario I'm looking at is "This program uses Postgres. Oh, you want to just use it single-user for the moment, and put off having to deal with installing a database server? Okay, in the meantime you can use it with the embedded single-user database." The question is then which embedded database is best.
As I understand it, the two main contenders are SQLite and Firebird; so which is better? Criteria:
Full SQL support, or as close as reasonably possible.
Full text search.
Easy to call from C#
Locks, or allows you to lock, the database file to make sure nobody tries to run it multiuser and ends up six months down the road with intermittent data corruption in all their backups.
Last but far from least, reliability.
As I understand it, the disadvantages of SQLite are,
No right outer join. Workaround: use left outer join instead.
Not much integrity checking. Workaround: be really careful in the application code.
No decimal numbers. Workaround: lots of aspirin.
None of the above are showstoppers. Are there any others I'm missing? (I know it doesn't support some administrative and code-within-database SQL features, that aren't relevant for this kind of use case.)
I don't know anything much about Firebird. What are its disadvantages?
