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

Question

I want to give the version for the sqlite(SQLite.NET component) database while creating it in Xamarin(Monodroid) , And how to handle the version of database during any modifications in the table and update in playstore. how I can get OnUpgrade functionality like android native SQLiteOpenHelper class.

I am using below way

string folder = Environment.GetFolderPath   (Environment.SpecialFolder.Personal);
var conn = new SQLiteConnection (System.IO.Path.Combine (folder, "stocks.db"));
conn.CreateTable<Stock>();
conn.CreateTable<Valuation>();

Answer 1

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 referencing master data. Those tables normally won´t be updated.
3. The upgrade/migrations basically modify the master data, respecting the 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 settings I´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