Skip to content

Instantly share code, notes, and snippets.

@matpag
Created April 15, 2016 10:10
Show Gist options
  • Star 6 You must be signed in to star a gist
  • Fork 3 You must be signed in to fork a gist
  • Save matpag/b2545cc22c8e22449cd7eaf6b4910396 to your computer and use it in GitHub Desktop.
Save matpag/b2545cc22c8e22449cd7eaf6b4910396 to your computer and use it in GitHub Desktop.
Xamarin Forms SQLite database upgrade strategy
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() {}
}
}
@picce
Copy link

picce commented Apr 11, 2018

at row 71 better using return Database.ExecuteScalar<int>("pragma user_version");
instead of Database.Execute that throw an exception

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment