I am giving up on SQLite as my embedded DB of choice. The reason for this is very simple. I really want to be able to use the in memory features (and other nice stuff it has to offer), but it has the worst support for date/time manipulations that I have seen. I am trying to formulate a query that should be along the lines of:
where posts.blogId = blogs.id
and ( posts.date + blogs.maxDaysInFeed ) <= getdate() ;
As much as I dislike dates, I tend to work with them in complex ways quite a bit, and I really need a database that can work with me in this regard. I just finished moving my in memory infrastructure to using SQL Server Compact Edition. This means that my tests will have to touch the disk, but I am willing to make this compromise.
In favor of Sql CE are the following:
- Embedded DB, so it is faster (nor RPC call)
- It has a behavior that is very similar to SQL Server, so I can test on Sql CE and then move to use SQL Server with worrying about the differences.
- I can use Management Studio to query it! Killer feature to say the least.