How to install SQLite (SQLite.NET) version of database in Xamarin Android
I want to provide a sqlite (SQLite.NET) database version when building it in Xamarin (Monodroid) . And how to handle the database version during any table changes and play store updates. how can i get the OnUpgrade functionality as native SQLiteOpenHelper class from android.
I am using below
string folder = Environment.GetFolderPath (Environment.SpecialFolder.Personal);
var conn = new SQLiteConnection (System.IO.Path.Combine (folder, "stocks.db"));
conn.CreateTable<Stock>();
conn.CreateTable<Valuation>();
+3
source to share
2 answers
I am not familiar with this "SQLiteOpenHelper" but have recently created a "migration / update" SQLite manager for an android application I am making. It will work with IOS as well as using SQLITE-NET PCL. I am open to hear your thoughts on this. There are probably better ways to do this, but this is my approach:
- My database has a few main tables (not to be processed) that need updating
- Other tables are just user data, with xrefs referenced by master data. These tables are usually not updated.
- The update / migration basically changes the master data, respecting the current user data
Let me explain this with a real case:
- The main data will be "Ingredients"
- User data table will be "Stock"
- Ingredient table can be updated / updated when the app is updated
- The user cannot change the ingredients table, as this is basic data
My code is still not perfect as I have to run every migration inside a transaction, but it is in my TODO list :)
"DatabaseMigrationService.RunMigrations ()" is called when the application 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 version of the database (stored in the device store). If there is a new update (migration), we will run it and update the saved key "DatabaseVersion".
As you can see, there are 2 helper classes in the constructor: ISQLite sqlite and ISettingsService Settings I am using MvvmCross (this is optional) and ISQLite is implemented on every platform (IOS / ANDROID). I'll demonstrate 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;
}
}
Settings is just a class that reads / writes simple values to platform persistent storage based on this plugin: https://github.com/jamesmontemagno/Xamarin.Plugins/tree/master/Settings p>
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 porting:
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 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",
};
}
}
Hope this helps. Anyway, if anyone knows a better way to do this, please share
+5
source to share
Have you created sqlite helper in Android-Xamarin?
Create a class that 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);
}
}
PS It has onCreate and onUpgrade methods to help you.
0
source to share