c# - How to set SQLite(SQLite.NET component) database Version in Xamarin Android -
i want give version sqlite(sqlite.net component) database while creating in xamarin(monodroid) , , how handle version of database during modifications in table , update in playstore. how can onupgrade functionality android native sqliteopenhelper class.
i 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>();
i´m not familiar "sqliteopenhelper", built sqlite "migration/upgrade" manager android app i´m doing. work ios i´m using sqlite-net pcl. i´m open hear thoughts on this. there better ways it, approach:
- my database has master tables (inmutable data) needs upgrading
- other tables user data, foreign keys referencing master data. tables won´t updated.
- the upgrade/migrations modify master data, respecting current user data
allow me explain actual case:
- the master data "ingredients"
- the user data table "stock"
- ingredients table can updated/upgraded when app upgrades
- the user can´t modify ingredients table, master data
my code still not perfect, should run every migration within transaction, it´s in todo list :)
"databasemigrationservice.runmigrations()" called when 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 transaction, otherwise, if , error found, app stay in 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 @ {0}", migrations[nextversion - 1].gettype().name); break; } } } } } the logic pretty simple. in "while" loope check current database version (persisted on device store). if there newer update(migration) run , update persisted "databaseversion" key.
as can see, there 2 helper classes provided in constructor: isqlite sqlite , isettingsservice settings i´m using mvvmcross (this not mandatory) , isqlite implemented on each platform (ios/android). i´ll show 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 class reads/writes simple values platform persistent store, based on 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 { { return crosssettings.current.getvalueordefault(databaseversionkey, 0); } set { crosssettings.current.addorupdatevalue(databaseversionkey, value); } } } finally, migration code. base class 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 \"ingredients\" values ('1', 'aceite', '1', '2', '730', 'z_aceite_de_oliva.jpg');\n", "insert \"ingredients\" values ('2', 'sal', '1', '1', '9999', 'z_sal.jpg');\n", "insert \"ingredients\" values ('3', 'cebolla', '3', '1', '30', 'z_cebolla.jpg');\n", "insert \"ingredients\" values ('4', 'naranja', '4', '1', '21', 'z_naranja.jpg');\n", "insert \"ingredients\" values ('5', 'bacalao', '5', '1', '2', 'z_bacalao.jpg');\n", "insert \"ingredients\" values ('6', 'yogur', '6', '2', '21', 'z_yogur.jpg');\n", "insert \"ingredients\" values ('7', 'garbanzos', '7', '1', '185', 'z_garbanzos.jpg');\n", "insert \"ingredients\" values ('8', 'pimienta', '8', '1', '3', 'z_pimienta.jpg');\n", "insert \"ingredients\" values ('9', 'chocolate', '9', '1', '90', 'z_chocolate.jpg');\n", "insert \"ingredients\" values ('10', 'ketchup', '10', '2', '365', 'z_ketchup.jpg');\n", "insert \"ingredients\" values ('11', 'espinaca', '3', '1', '5', 'z_espinaca.jpg');\n", "insert \"ingredients\" values ('12', 'limón', '4', '3', '30', 'z_limon.jpg');\n", "insert \"ingredients\" values ('13', 'calamar', '5', '1', '2', 'z_calamares.jpg');\n", "insert \"ingredients\" values ('14', 'mantequilla', '6', '1', '21', 'z_mantequilla.jpg');\n", "insert \"ingredients\" values ('15', 'perejil', '8', '1', '7', 'z_perejil.jpg');\n", "insert \"ingredients\" values ('16', 'cacao', '9', '1', '365', 'z_cacao.jpg');\n", "insert \"ingredients\" values ('17', 'mayonesa', '10', '2', '7', 'z_mayonesa.jpg');\n", "insert \"ingredients\" values ('18', 'arroz', '7', '1', '999', 'z_arroz.jpg');\n", "insert \"ingredients\" values ('19', 'pepino', '3', '1', '15', 'z_pepino.jpg');\n", "insert \"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 \"ingredients\" values ('21', 'otros (líquidos)', '0', '2', '365', 'z_otros_liquidos.png');\n", "insert \"ingredients\" values ('22', 'otros (sólidos)', '0', '1', '365', 'z_otros_solidos.png');\n", "insert \"ingredients\" values ('23', 'otros (unidades)', '0', '3', '365', 'z_otros_unidades.png');" }; } } example of migration update commands:
internal sealed class migration4 : basemigration { protected override list<string> getcommands() { return new list<string> { "update ingredients set measuretype = 3, name = 'ajo (diente)' id = 106", "update ingredients set measuretype = 3 id = 116", }; } } i hope helps. anyway if knows better way this, please, share
Comments
Post a Comment