Created
April 15, 2016 10:10
-
-
Save matpag/b2545cc22c8e22449cd7eaf6b4910396 to your computer and use it in GitHub Desktop.
Xamarin Forms SQLite database upgrade strategy
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
using ProjectTest.MyModels; | |
using SQLite.Net; | |
using System; | |
using System.Collections.Generic; | |
using System.Linq; | |
using System.Text; | |
using System.Threading.Tasks; | |
using Xamarin.Forms; | |
namespace ProjectTest.Database | |
{ | |
/// <summary> | |
/// Class to handle DB connection | |
/// </summary> | |
public class Database | |
{ | |
//this have to reflect the last database version when | |
//the application is shipped | |
//must be updated with every database change | |
public const int LAST_DATABASE_VERSION = 1; | |
public static SQLiteConnection database; | |
/// <summary> | |
/// get the connection from the database using Xamarin Forms dependency service. | |
/// the real implementation (folder path) is in platform specific classes | |
/// </summary> | |
/// <returns></returns> | |
/// | |
public static SQLiteConnection GetDBConnection() | |
{ | |
if (database == null) | |
{ | |
//use dependency service for getting the platform connection | |
database = DependencyService.Get<ISQLite>().GetConnection(); | |
//create the tables, will be created only the first time | |
database.CreateTable<Log>(); | |
database.CreateTable<Attachment>(); | |
database.CreateTable<Customer>(); | |
database.CreateTable<ServiceCenter>(); | |
database.CreateTable<Notification>(); | |
database.CreateTable<Product>(); | |
database.CreateTable<RemoteData>(); | |
database.CreateTable<Activity>(); | |
database.CreateTable<ActivityProduct>(); | |
database.CreateTable<ActivityAttachment>(); | |
database.CreateTable<PlantMaintenanceBook>(); | |
database.CreateTable<PlantMaintenanceBookAttachment>(); | |
database.CreateTable<Contract>(); | |
database.CreateTable<ContractProduct>(); | |
database.CreateTable<ContractAttachment>(); | |
//TEST ONLY | |
database.CreateTable<Setting>(); | |
//enable foreign keys | |
database.Execute("PRAGMA foreign_keys = ON"); | |
UpgradeDatabaseIfNecessary(); | |
} | |
return database; | |
} | |
private static void SetDatabaseToVersion(int version) | |
{ | |
database.Execute("PRAGMA user_version = " + version); | |
} | |
private static int GetDatabaseVersion() | |
{ | |
return database.Execute("PRAGMA user_version"); | |
} | |
private static void UpgradeDatabaseIfNecessary() | |
{ | |
//the first time ever we get this value after the database creation | |
//this should be equals 0. but it's ok and will perform the correct | |
//updates in the switch. | |
int currentDbVersion = GetDatabaseVersion(); | |
if (currentDbVersion < LAST_DATABASE_VERSION) | |
{ | |
//we have to ignore the current database updates, so start from the next | |
int startUpgradingFrom = currentDbVersion + 1; | |
//if we are are, database upgrade is needed | |
switch (startUpgradingFrom) | |
{ | |
case 1: //starting version | |
case 2: UpgradeFrom1To2(); | |
goto case 3; | |
case 3: UpgradeFrom2To3(); | |
goto case 4; | |
case 4: //ecc.. ecc.. | |
break; | |
default: | |
//if we are here something with the update went wrong, | |
//deleting and recreating the database is the only | |
//possible action to perform | |
throw new Exception("something went really wrong"); | |
} | |
SetDatabaseToVersion(LAST_DATABASE_VERSION); | |
} | |
} | |
private static void UpgradeFrom1To2() {} | |
private static void UpgradeFrom2To3() {} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
at row 71 better using
return Database.ExecuteScalar<int>("pragma user_version");
instead of
Database.Execute
that throw an exception