How to set SQLite(SQLite.NET component) database Version in Xamarin Android

I´m not familiar with that "SQLiteOpenHelper", but I recently built a SQLite "migration/upgrade" manager for the Android app I´m doing. It will work with IOS as well as I´m using SQLITE-NET PCL. I´m open to hear your thoughts on this. Probably there are better ways to do it, but this is my approach:

1. My database has some master tables (inmutable data) that needs upgrading
2. Other tables are just user data, with foreign keys referencingmaster data. Those tables normally won´t be updated.
3. The upgrade/migrations basically modify the master data, respectingthe current user data

Allow me to explain it with an actual case:

1. The master data would be "Ingredients"
2. The user data table would be "Stock"
3. Ingredients table can be updated/upgraded when the app upgrades
4. The user can´t modify Ingredients table, as it is master data

My code is still not perfect, as I should run every migration within a transaction, but it´s in my TODO list :)

"DatabaseMigrationService.RunMigrations()" is called when the app starts:

public interface IMigrationService{    Task RunMigrations();}public interface IMigration{    IMigration UseConnection(SQLiteAsyncConnection connection);    Task<bool> Run();}

DatabaseMigrationService

public sealed class DatabaseMigrationService : IMigrationService    {        private ISQLite sqlite;        private ISettingsService settings;        private List<IMigration> migrations;        public DatabaseMigrationService(ISQLite sqlite, ISettingsService settings)        {            this.sqlite = sqlite;            this.settings = settings;            SetupMigrations();        }        private void SetupMigrations()        {            migrations = new List<IMigration> {                new Migration1(),                new Migration2(),                new Migration3(),                new Migration4(),                new Migration5(),                new Migration6()            };        }        public async Task RunMigrations()        {            // TODO run migrations in a transaction, otherwise, if and error is found, the app could stay in a horrible state            if (settings.DatabaseVersion < migrations.Count)            {                var connection = new SQLiteAsyncConnection(() => sqlite.GetConnectionWithLock());                while (settings.DatabaseVersion < migrations.Count)                {                    var nextVersion = settings.DatabaseVersion + 1;                    var success = await migrations[nextVersion - 1].UseConnection(connection).Run();                    if (success)                    {                        settings.DatabaseVersion = nextVersion;                    }                    else                    {                        MvxTrace.Error("Migration process stopped after error found at {0}", migrations[nextVersion - 1].GetType().Name);                        break;                    }                }            }        }    }

The logic is pretty simple. In the "while" loope we check the current database version (persisted on the device store). If there is a newer update(migration) we run it and update the persisted "DatabaseVersion" key.

As you can see, there are 2 helper classes provided in the constructor:ISQLite sqlite and ISettingsService settingsI´m using MvvmCross (this is not mandatory) and ISQLite is implemented on each platform (IOS/ANDROID). I´ll show the Android implementation:

public class SqliteAndroid : ISQLite    {        private SQLiteConnectionWithLock persistentConnection;        public SQLiteConnectionWithLock GetConnectionWithLock()        {            if (persistentConnection == null)            {                var dbFilePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), Constants.DB_FILE_NAME);                var platform = new SQLitePlatformAndroid();                var connectionString = new SQLiteConnectionString(dbFilePath, true);                persistentConnection = new SQLiteConnectionWithLock(platform, connectionString);            }            return persistentConnection;        }    }

The settings is just a class that reads/writes simple values to the platform persistent store, based on this plugin: https://github.com/jamesmontemagno/Xamarin.Plugins/tree/master/Settings

public interface ISettingsService    {        int DatabaseVersion { get; set; }        [...]    }    public class SettingsService : ISettingsService    {        private string databaseVersionKey = "DatabaseVersion";        public int DatabaseVersion        {            get { return CrossSettings.Current.GetValueOrDefault(databaseVersionKey, 0); }             set            {                CrossSettings.Current.AddOrUpdateValue(databaseVersionKey, value);            }        }}

Finally, the migration code. This is the base class for migrations:

public abstract class BaseMigration : IMigration    {        protected SQLiteAsyncConnection connection;        protected string migrationName;        public IMigration UseConnection(SQLiteAsyncConnection connection)        {            this.connection = connection;            migrationName = this.GetType().Name;            return this;        }        public virtual async Task<bool> Run()        {            try            {                MvxTrace.Trace("Executing {0}", migrationName);                int result = 0;                var commands = GetCommands();                foreach (var command in commands)                {                    MvxTrace.Trace("Executing command: '{0}'", command);                    try                    {                        var commandResult = await connection.ExecuteAsync(command);                        MvxTrace.Trace("Executed command {0}. Rows affected {1}", command, commandResult);                        result = result + commandResult;                    }                    catch (Exception ex)                    {                        MvxTrace.Error("Command execution error: {0}", ex.Message);                        throw ex;                    }                }                MvxTrace.Trace("{0} completed. Rows affected {1}", migrationName, result);                return result > 0;            }            catch (Exception ex)            {                MvxTrace.Error("{0} error: {1}", migrationName, ex.Message);                return false;            }        }        protected abstract List<string> GetCommands();    }

Migration 1:

internal sealed class Migration1 : BaseMigration    {        override protected List<string> GetCommands()        {            return new List<string> {                "DROP TABLE IF EXISTS \"Recipes\";\n",                "DROP TABLE IF EXISTS \"RecipeIngredients\";\n",                "DROP TABLE IF EXISTS \"Ingredients\";\n",                "CREATE TABLE \"Ingredients\" (\n\t " +                "\"Id\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n\t " +                "\"Name\" TEXT(35,0) NOT NULL COLLATE NOCASE,\n\t " +                "\"Family\" INTEGER NOT NULL,\n\t " +                "\"MeasureType\" INTEGER NOT NULL,\n\t " +                "\"DaysToExpire\" INTEGER NOT NULL,\n\t " +                "\"Picture\" TEXT(100,0) NOT NULL\n" +                ");",                "INSERT INTO \"Ingredients\" VALUES ('1', 'Aceite', '1', '2', '730', 'z_aceite_de_oliva.jpg');\n",                "INSERT INTO \"Ingredients\" VALUES ('2', 'Sal', '1', '1', '9999', 'z_sal.jpg');\n",                "INSERT INTO \"Ingredients\" VALUES ('3', 'Cebolla', '3', '1', '30', 'z_cebolla.jpg');\n",                "INSERT INTO \"Ingredients\" VALUES ('4', 'Naranja', '4', '1', '21', 'z_naranja.jpg');\n",                "INSERT INTO \"Ingredients\" VALUES ('5', 'Bacalao', '5', '1', '2', 'z_bacalao.jpg');\n",                "INSERT INTO \"Ingredients\" VALUES ('6', 'Yogur', '6', '2', '21', 'z_yogur.jpg');\n",                "INSERT INTO \"Ingredients\" VALUES ('7', 'Garbanzos', '7', '1', '185', 'z_garbanzos.jpg');\n",                "INSERT INTO \"Ingredients\" VALUES ('8', 'Pimienta', '8', '1', '3', 'z_pimienta.jpg');\n",                "INSERT INTO \"Ingredients\" VALUES ('9', 'Chocolate', '9', '1', '90', 'z_chocolate.jpg');\n",                "INSERT INTO \"Ingredients\" VALUES ('10', 'Ketchup', '10', '2', '365', 'z_ketchup.jpg');\n",                "INSERT INTO \"Ingredients\" VALUES ('11', 'Espinaca', '3', '1', '5', 'z_espinaca.jpg');\n",                "INSERT INTO \"Ingredients\" VALUES ('12', 'Limón', '4', '3', '30', 'z_limon.jpg');\n",                "INSERT INTO \"Ingredients\" VALUES ('13', 'Calamar', '5', '1', '2', 'z_calamares.jpg');\n",                "INSERT INTO \"Ingredients\" VALUES ('14', 'Mantequilla', '6', '1', '21', 'z_mantequilla.jpg');\n",                "INSERT INTO \"Ingredients\" VALUES ('15', 'Perejil', '8', '1', '7', 'z_perejil.jpg');\n",                "INSERT INTO \"Ingredients\" VALUES ('16', 'Cacao', '9', '1', '365', 'z_cacao.jpg');\n",                "INSERT INTO \"Ingredients\" VALUES ('17', 'Mayonesa', '10', '2', '7', 'z_mayonesa.jpg');\n",                "INSERT INTO \"Ingredients\" VALUES ('18', 'Arroz', '7', '1', '999', 'z_arroz.jpg');\n",                "INSERT INTO \"Ingredients\" VALUES ('19', 'Pepino', '3', '1', '15', 'z_pepino.jpg');\n",                "INSERT INTO \"Ingredients\" VALUES ('20', 'Frambuesa', '4', '1', '3', 'z_frambuesa.jpg');"            };        }    }

Migration 2:

internal sealed class Migration2 : BaseMigration    {        override protected List<string> GetCommands()        {            return new List<string> {                "INSERT INTO \"Ingredients\" VALUES ('21', 'Otros (líquidos)', '0', '2', '365', 'z_otros_liquidos.png');\n",                "INSERT INTO \"Ingredients\" VALUES ('22', 'Otros (sólidos)', '0', '1', '365', 'z_otros_solidos.png');\n",                "INSERT INTO \"Ingredients\" VALUES ('23', 'Otros (unidades)', '0', '3', '365', 'z_otros_unidades.png');"            };        }    }

Example of migration with update commands:

internal sealed class Migration4 : BaseMigration    {        protected override List<string> GetCommands()        {            return new List<string> {                "UPDATE Ingredients SET MeasureType = 3, Name = 'Ajo (diente)' WHERE Id = 106",                "UPDATE Ingredients SET MeasureType = 3 WHERE Id = 116",            };        }    }

I hope this helps. Anyway if someone knows a better way to do this, please, share

Did you create a sqlite helper in Android-Xamarin ?

Create a class which extends SqliteHelper

private class DatabaseHelper : SQLiteOpenHelper{    internal DatabaseHelper(Context context)        : base(context, dBName, null, databaseVersion){    }    public override void OnCreate(SQLiteDatabase db){       db = SQLiteDatabase.OpenDatabase (destinationPath, null, 0);    }    public override void OnUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){        Log.Wtf(tag, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data");        this.OnCreate(db);    }}

P.S It has onCreate and onUpgrade methods to help you.