I’ve been developing a mobile app for Android which needs to query data in an SQLite database. So the first task I needed to get done was to import data into the SQLite database. As I’m more used to writing .NET applications which use the NHibernate ORM I decided to write an import program on this technology stack.

For my first attempt at the import program I just wanted to focus on the import logic, so choose to use a ‘known working’ NHibernate configuration with a MS SQL 2005 database. While this worked fine for me it did mean that I had to manually copy all of the data from the MS SQL database into the SQLite database before it could be used in the Android app. This was quite laborious so when I needed to modify the importer at a later date I decided to change the NHibernate configuration to point directly to the SQLite database instead. This was the updated config file used:

<hibernate-configuration  xmlns="urn:nhibernate-configuration-2.2" >
    <session-factory>
        <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
        <property name="connection.driver_class">NHibernate.Driver.SQLiteDriver</property>
        <property name="connection.connection_string_name">FootyLinks</property>
        <property name="dialect">NHibernate.Dialect.SQLiteDialect</property>
        <property name="query.substitutions">true=1;false=0</property>
    </session-factory>
</hibernate-configuration>

And this is an example of the connection string format:

<configuration>
    <connectionStrings>
        <add name="FootyLinks" connectionString="Data Source=C:\path_to_the_SQLite_database_file;Version=3"/>
    </connectionStrings>
</configuration>

This configuration worked OK and the import program could connect to the SQLite database, however I hit a problem when trying to save new records to the database. This was the inner exception reported by NHibernate:

Abort due to constraint violation Club.Id may not be NULL

The problem was that the import program was using the default generator class of identity for in the NHibernate entity mappings. So NHibernate was expecting the database to generate the identity keys for the new records being saved to the database, however SQLite does not appear to support this. After some reading up on the options available for the NHibernate generator setting I decided to try the native setting. As this should select the implementation supported by the database. However this didn’t work either, I think because, of all the options native can select, none are actually supported by SQLite. So my next preferred selection was to use increment, this setting comes with a disclaimer that it should not be used in a cluster, which is absolutely fine as its very unlikely that I will ever be deploy the importer to a load balanced environment.

The increment setting worked fine with the SQLite database and should streamline my workflow nicely for updating the app data .