27. November 2007 05:55
I've just added support for SQLite to RikMigrations. This was done mainly because I use SQLite to store data in RikPVR and I needed to expand the schema. I tossed up whether to go with the embedded sql scripts like I have been doing or put in the time to add support to RikMigrations so I could use it. Eventually I decided it would be worth the work of adding to RikMigrations, if only to test how well it would support alternate database providers.
As expected, I had to refactor the code to support the change - alternate database providers wasn't in the original RikMigrations design, though I had already started refactoring towards a provider pattern. However, the changes were actually fairly minimal. I encourage others to sign up and start adding support for other engines.
Unfortunately, SQLite is a bit limited in some areas so not all features are supported. The biggest one is probably the inability to drop columns. There are workarounds for this in SQLite which I may look in to, but for the moment it will just throw an exception. Other ones I've run into is that you don't seem to be able to add columns with PRIMARY KEY or UNIQUE constraints on them once the table is created, but they work fine if they're added during the initial table construction.
In other news I've added a couple of extra pages to the RikMigrations doco. I'll keep adding to that bit by bit but if you have any specific questions drop me a comment or an email.
Finally, here's a quick bit of code in the meantime to show how to use SQLite instead of SQL Server. (This is only for programmatic access, I haven't added the command line interface yet)
DbProvider.DefaultConnectionString = @"Data Source=D:/TestDB.db";
The actual migrations stay exactly the same, except you may run into the limitations I mentioned earlier. I'll try to document them somewhere.