Migrating to SqLite - Part 2

What took us to migrate to SqLite?


In general we had the following checklist:
1.Replace the classes from the System.Data.SqlCe namespace with the classes from the System.Data.SqLite namespace.
2. DataType compatibility problems
3. Foreign key related problems
4. Sql syntax compatibility problems


The migration became really easy and smooth. The major reason was the system architecture. Our system design utilizes the common idea for separating the business and data access logic. Out business logic layer operates over custom business objects (not DataSets) and invokes the data access layer for fetching and persistence operations. It has a performance impact of course, however it is a reasonable tradeoff I may say.

Checklist item #1:
We have only one place in our code to create IDataConnection implementations:
The code is like:
public IDataConnection GetConnection()
{
return new System.Data.SqlServerCe.SqlCeConnection(cnnString)
}

so we've changed it to
public IDataConnection GetConnection()
{
return System.Data.SQLite.SQLiteConnection(cnnString)
}

We use IDataCommand, IDataParameter,IDataReader and other interfaces to perform the actual database interactions. So we didn't change anything in data manipulating routines. The code was like:
using(IDataConnection cnn=DataManager.GetConnection())
{
cnn.Open()
IDataCommand cmd = cnn.GetCommand(cnn);
cmd.CommandText = "Select * from sometable";
...
IDataReader reader = cmd.ExecuteDataReader()
...


Checklist item #2
Are we going to have issues related to data types. For example very often the datetime data type may be a big pain when using the same code to access different dbms. So we did a test application where we performed some database operations identified as potentially problematic. The operations involved reading, selecting, fitlering ,joining and aggregating in SqLite db populated with a real data (from a customer). We have operations with DataReaders,ExecuteScalar calls,etc. It showed us the problematic areas and they were few indeed.

Checklist item #3
Fortunately it was not a big deal for us. Our data access layer provide some features usually available in the ORM products and dealing with business objects relations at application and database levels was one of them.

Checklist item #4
The test application helped us to identify the possible problems. In general there was no need to rewrite sql statements. We had the following issues:
- statements for the foreign constraint creation
- primary key creation statements. We used ALTER TABLE ..ADD PRIMARY KEY, which is not supported in SqLite

Note that this post only scratches the surface of the possible problems around such a migration.The issues are of course specific to the actual application migrted.
What was the benefit?
The migration process helped us to identify some design and implementation issues in our code so in result we had a better code as a whole. And we have a really better performing application as a whole. The bottleneck features are performing up to 9 times faster after migration. It worth it!

Migrating from SqlMobile 2005 to SqLite requires understanding of the tradeoffs which have to be made.
Read the SqLite documentation and make a test bed before before proceeding.