, ,

I have been using SQLite for a couple of years now and I think it is a very good alternative to binary or XML based file formats. But I have also been using SQL Server and a commercial ORM called LLBLGenPro and I always wanted to have that same functionality for SQLite. The problem is that nobody seems interested enough to do it. On top of that SQLite is a bit awkward because it is not strongly typed (if you can say that about a database) and does not enforce referential integrity by itself.

After dabbling with several other options I ended up biting the bullet and decided to build one. Instead of starting from scratch I downloaded Subsonic, duplicated their MySQL data provider and did a search and replace of all the MySql.Data types by the corresponding System.Data.SQLite ones.

And it compiled! But alas, it did not work at all.

But before I go into details, let’s talk about the whys. I chose Subsonic because it was recommended recently in Jeff Atwood’s blog and because it follows the “convention over configuration” maxim which, by the way, meshes very well with the loose types and pseudo constraints of SQLite. I chose the MySQL provider instead of the SQL Server provider which is more mature and complete because MySQL SQL’s flavour uses LIMIT/OFFSET instead of TOP. This way I don’t have to rewrite all that SQL generation code! And finally, not so much a why but a list of the stuff I used:

1. Subsonic 2.0.3
2. ADO.NET 2.0 SQLite Data Provider
3. SQLite 3.3.12
4. MbUnit 2.4

The hidden reason for writing this provider was TDD practice. Being a newcomer to unit tests and test driven development this project provided a playground for test first practice. I don’t think I got there yet but I sure got a lot of practice. I did have a lot of fun with MbUnit and its generative tests. There’s something exhilarating about writing a single test and seeing it be multiplied automatically!

So far I spent three evenings and two days (rainy weekends this year) and I am nearly finished. This morning I had 64 successful tests and 95% of the code built against Subsonic 2.2 in a separate project. I say 95% because the MySQL provider get’s away with not implementing several features and I am following suit for the moment. Then I setup a local subversion sandbox and got the latest code from Codeplex. I integrated my changes into the main trunk, added my tests to the test project and run them. I got 20 failures, which was not bad at all! I mean, if I hadn’t written the tests how would I know where to start? It was a bit of an epiphany as it dawned on me how much time I was going to save because I had written those tests upfront!

As it happens most of the failures were due to configuration mistakes. After correcting it (bit of a struggle there) I managed to start the scaffolding pages and play around with it but that was a mistake. Suddenly several tests started failing for no reason and it took me another half an hour or so to find out that the webserver was locking the database. This was solved by creating a copy of the database for the test project and adjusting some more configuration, so if you plan to implement a Subsonic data provider for a file based database consider yourself warned.

Subsonic’s configuration is very simple but not terribly clear. And the latest code does not match the online videos so you are a bit on your own there. The code is also very straightforward and easy to read. This just about saves it from lack of comments, especially in the base classes. Some methods are reasonably confusing and I only managed to implement them by reverse engineering the SQL Server data provider. All in all I am impressed by the quality of the project even though it is very much work in progress.

To be continued…