EF6 SQLite Migration

SQLite Datenbank-Aktualisierung mit Entity Framework 6 und Code First

Versionsgeschichte
Version Aktualisierungsdatum
0.1.1 (Tippfehler Korrekturen) 15.05.2014
0.1 (Erste Fassung) 04.05.2014

Seit einiger Zeit unterstützt das Entity Framework mit Code First Methode auch die Migration der Datenbank. Allerdings muss dieses Feature explizit von dem Datenbank-Provider unterstützt werden (bei MS SQL ist dies der Fall). Bei SQLite ist diese Funktion leider nicht implementiert.

Ich dachte lange, wie man die Migration auch mit SQLite nutzen kann. In diesem Tutorial zeige ich einen gangbaren Weg, wie dies mit EF6 und aktueller SQLite-Version umgesetzt werden kann. Die Basis-Idee stammt dabei von Android, wo eine SQLiteOpenHelper-Klasse die Migration steuert.

Ausgangssituation

SQLite Provider für ADO.Net / Entity Framework 6 unterstützt keine Datenbank Erzeugung und Migration, wie es zum Beispiel von MS SQL Provider unterstützt wird. Man muss also immer selbst dafür sorgen, dass bei dem Kunden die Datenbank immer in der korrekten Version vorliegt. Mit der Zeit wird es mit Sicherheit vorkommen, dass Kunden unterschiedliche Datenbankversionen besitzen und auf die neue Software wechseln wollen.

Wie aktualisiert man also mit so wenig Aufwand wie möglich sowohl die Datenbank-Struktur, als auch die Kundendaten? Bis jetzt war es zumindest in unserer Firma nicht wirklich festgelegt, wie dies zu geschehen ist.

Muss bei dem Kunden nur die Struktur geändert werden, aber keine Daten migriert werden, gibt es eine sehr einfache Lösung. Beim Update wird die Datenbank immer mit der aus der Installation ersetzt.

Lösungsidee

Unter Android wird die Migration der SQLite-Datenbank mit Hilfe der user_version angestoßen. Diese Versionsnummer kann bei SQLite vom Benutzer gesetzt werden. SQLiteOpenHelper hat dazu zwei Methoden, die die Erzeugung und Migration steuern und an den Entwickler weiter geben. Als Constructor-Parameter wird dabei immer die benötigte Version übergeben.

if (version == 0) {
    onCreate(db);
} else {
    onUpgrade(db, version, mNewVersion);
}

Ähnliches Verhalten wäre auch für Entity Framework interessant und für die meisten Anwendungsszenarien mit SQLite ausreichend. Der Weg dahin soll nun weiter unten beschrieben werden.

Ausgangsprojekt

Wir fangen ein einfaches Projekt mit EntityFramework an.

Das vollständige Projekt finden Sie auf Guthub

Den Anfang machen wir mit einem sehr einfachen Model, das nur aus einer einzigen Tabelle (Objektklasse) besteht.

using System;

namespace de.webducer.csharp.sqliteef6.BusinessData {

    public class WorkingTimeRange {
        public long Id { get; set; }
        public DateTime StartTime { get; set; }
        public DateTime EndTime { get; set; }
        public int PauseDuration { get; set; }
    }
}

Für das Mapping zischen der Klasse und der Tabelle erstellen wird anschließend eine Mapping-Klasse.

using de.webducer.csharp.sqliteef6.BusinessData;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.ModelConfiguration;

namespace de.webducer.csharp.sqliteef6.DatabaseContext.Mapping {

    public class WorkingTimeMapping : EntityTypeConfiguration<WorkingTimeRange> {
        public WorkingTimeMapping() {
            // Primary key
            this.HasKey(k => k.Id);

            // Nullables
            this.Property(p => p.StartTime).IsRequired();

            // Mapping
            this.ToTable("time_tracking");
            this.Property(p => p.Id).HasColumnName("_id").HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            this.Property(p => p.StartTime).HasColumnName("start_time");
            this.Property(p => p.EndTime).HasColumnName("end_time");
            this.Property(p => p.PauseDuration).HasColumnName("pause_duration");
        }
    }
}

Im Normalfall würde die Context-Klasse dann am Anfang wie folgt aussehen (Definition der Zugriffspunkte, Konstruktororen und Einbindung der Mappings).

using de.webducer.csharp.sqliteef6.BusinessData;
using de.webducer.csharp.sqliteef6.DatabaseContext.Mapping;
using System;
using System.Data.Common;
using System.Data.Entity;
using System.Data.SQLite;
using System.Linq;

namespace de.webducer.csharp.sqliteef6.DatabaseContext {
    public class DatabaseContext : DbContext {
        private const string _DB_FILE_NAME = @"Data\Time.db";

        #region Constructors
        public DatabaseContext()
            : this(_DB_FILE_NAME) {

        }

        public DatabaseContext(string dbFileName)
            : base(GetConnection(dbFileName), true) {

        }
        #endregion

        #region Properties
        public IDbSet<WorkingTimeRange> WorkingTimes { get; set; }
        #endregion

        #region Mapping Configuration
        protected override void OnModelCreating(DbModelBuilder modelBuilder) {
            modelBuilder.Configurations.Add(new WorkingTimeMapping());
        }
        #endregion

        #region Helper Methods
        private static DbConnection GetConnection(string dbFileName) {
            var connectionString = new SQLiteConnectionStringBuilder() {
                BinaryGUID = true,
                DataSource = dbFileName,
                DateTimeFormat = SQLiteDateFormats.ISO8601,
                DateTimeKind = DateTimeKind.Local,
                FailIfMissing = true,
                ForeignKeys = true
            }.ToString();

            return new SQLiteConnection(connectionString);
        }
        #endregion
    }
}

Diese Basisimplementierung funktioniert leider nur, wenn die Datenbank bereits existiert und unserem Model entspricht. Ist die Datenbank nicht an der angegebenen Stelle, wird eine Ausnahme erzeugt (das geschieht in erster Linie durch die Einstellung FailIfMissing = true).

Man kann diese Ausnahme verhindern, in dem man FailIfMissing = false setzt. Das veranlasst den SQLite Provider eine neue leere Datenbank zu erzeugen, falls diese nicht existiert. Nur ist diese, wie bereits erwähnt, komplett leer. Somit ist immer eine Datenbank erforderlich, wenn keine weiteren Schritte implementiert sind.

Eine Auslieferung einer Datenbank stellt im Grunde nicht das Problem dar, wohl aber die Migration der Daten, wenn bei dem Kunden bereits eine Datenbank mit älterer Struktur vorliegt und diese mit seinen kostbaren Daten gefüllt ist. Diese müssen vor dem ersten Zugriff auf die Datenbank möglichst ohne Verlust migriert werden.

Vorbereitung für die Migrationsfähigkeit

Versionsnummer

Damit wir feststellen können, ob wir die Datenbank migrieren müssen, oder mit der vorliegenden weiter arbeiten können, müssen wir irgendwo festhalten, welchen Stand die Datenbank hat.

Die naheliegende Lösung wäre eine Tabelle in der Datenbank, die eine Versionsnummer vorhält. Das ist zwar machbar, aber dann müssen alle Datenbanken, die wir ausliefern, bereits diese Tabelle enthalten. Das ist relativ schwer, wenn die Datenbanken bereits im Einsatz sind.

SQLite hat aber noch eine zusätzliche Möglichkeit, user_version. SQLite verwaltet zwei Datenbank-Versionen (SQLite Pragmas):

  1. schema_version: wird automatisch von SQLite hochgezählt, wenn eine Änderung an der Struktur der Datenbank erfolgt (neue Tabellen, Views, Trigger, usw.)
  2. user_version: diese kann vom Benutzer selbst gesetzt werden.

Wir werden für unsere Zwecke die user_version nutzen, wie es bei Android auch der Fall ist.

Dazu benötigen wir nur wenige Zeilen Code in unserem Context. Ein mal das SQL, um die user_version lesen und schreiben zu können:

private const String _DB_VERSION = "PRAGMA user_version";

Und das Property Version, mit dem wir auf diese Information zugreifen können. Um die Anzahl der Zugriffe zu verringern, wird die Versionsnummer nur dann aus der Datenbank ausgelesen, wenn diese noch nicht abgefragt wurde, oder geändert wurde. Sonst greifen wir auf die zwischengespeicherte Versionsnummer zu.

private int? _databaseVersion = null;
public int Version {
	get {
		if(!_databaseVersion.HasValue) {
			_databaseVersion = Database.SqlQuery<int>(_DB_VERSION).Single();
		}
		return _databaseVersion.Value;
	}
	set {
		Database.ExecuteSqlCommand(_DB_VERSION + "=" + value);
		_databaseVersion = null;
	}
}

Damit haben wir bereits eine Möglichkeit, die aktuelle Version der Datenbank zu prüfen, ohne die komplette Struktur der Datenbank prüfen zu müssen.

Im nächsten Schritt müssen wir nun diese Information nutzen, um eine Erzeugung oder eine Migration anzustoßen.

Migrationsschritte

Um Migration durchführen zu können, definieren wir ein Interface, das einen Schritt abstrahiert und allgemeine Zugriffsmethoden anbietet.

using System.Data.Entity;

namespace de.webducer.csharp.sqliteef6.DatabaseContext.Interfaces {
    public interface IMigrationStep<T> where T : DbContext {
        void MigrateStructure(T context);

        void MigrateData(T context);
    }
}

Für Versionen die übersprungen werden sollen, definieren wir auch eine Standardimplementierung, die keine Aktionen durchführt.

Mit der Zeit werden mit Sicherheit mehrere Schritte zu einen zusammengefasst. Dann benötigt man eine solche Standardimplementierung, die für übersprungene Versionen nichts tut.

using de.webducer.csharp.sqliteef6.DatabaseContext.Interfaces;
using System.Data.Entity;

namespace de.webducer.csharp.sqliteef6.DatabaseContext.Migration {
    public class NullMigrationStep<T> : IMigrationStep<T> where T : DbContext {
        private NullMigrationStep() { }

        public void MigrateStructure(T context) {
            // Do nothing, skipp
        }

        public void MigrateData(T context) {
            // Do nothing, skipp
        }

        public static NullMigrationStep<T> GetInstance() {
            return new NullMigrationStep<T>();
        }
    }
}

Für den Anfang definieren wir nur einen Migrationsschritt, der die Datenbank, wenn diese noch nicht da ist, initialisiert.

using de.webducer.csharp.sqliteef6.DatabaseContext.Interfaces;

namespace de.webducer.csharp.sqliteef6.DatabaseContext.Migration {
    public class InitDatabaseStep : IMigrationStep<DatabaseContext> {
        public void MigrateStructure(DatabaseContext context) {
            context.Database.ExecuteSqlCommand(Properties.Resources.InitDatabase);
        }

        public void MigrateData(DatabaseContext context) {
            // No data changes bei initialization
        }
    }
}

Ich nutze für die Initialisierung einfach einen SQL-Script, den ich in den Resourcen der Bibliothek ablege. Man kann diese natürlich auch direkt im Code ablegen, oder in einer mitgelieferten Datei ablegen. Das kann abhängig vom Projekt unterschiedlich gehandhabt werden.

Durchführung der Migration

Jetzt kommt der Kleber, der die Vorbereitungen nun zu einer funktionierenden Lösung zusammensetzt.

Die Context-Klasse bekommt einen statischen Konstruktor, der den Initialisierer enthält. Dieser wird immer aufgerufen, wenn man eine Verbindung zu einer Datenbank aufbaut, die im laufenden Programm noch nicht kontaktiert wurde.

static DatabaseContext() {
	Database.SetInitializer<DatabaseContext>(new CreationionAndMigrationInitializer());
}

Der Initialisierer sorgt nun dafür, dass die Migration durchgeführt wird, abhängig von der aktuellen Version.

Microsoft liefert mit dem Entity Framework bereits einige Initialisierer mit, die vor allem während der Entwicklungsphase mit MS SQL benutzt werden können. Dazu gehören zum Beispiel CreateDatabaseIfNotExists, DropCreateDatabaseWhenModelChanges und DropCreateDatabaseAlways.

using de.webducer.csharp.sqliteef6.DatabaseContext.Interfaces;
using System;
using System.Data.Entity;
using System.Data.SQLite;

namespace de.webducer.csharp.sqliteef6.DatabaseContext.Migration {
    public class CreationionAndMigrationInitializer : IDatabaseInitializer<DatabaseContext> {
        #region Required version
        // Minimum version number of the database requiered to work with the programm
        private const int _REQUIRED_VERSION = 1;
        // Default version number bei creating empty databse (DEFAULT: 0)
        private const int _NO_DATABASE_VERSION = 0;
        #endregion

        #region Available migration steps
        // Static migration step for skipping
        private static readonly NullMigrationStep<DatabaseContext> _SKIPP_MIGRATION_STEP = NullMigrationStep<DatabaseContext>.GetInstance();

        // List of all available migration steps. Index represents the version to migrate from to the next
        private static IMigrationStep<DatabaseContext>[] _MIGRATION_STEPS = {
                                                                                new InitDatabaseStep()
                                                                            };
        #endregion

        #region Migration
        public void InitializeDatabase(DatabaseContext context) {
            // get current version
            var currentVersion = context.Version;

            // Check all migration steps to the required version are available
            if(_REQUIRED_VERSION > _MIGRATION_STEPS.Length) {
                throw new IndexOutOfRangeException("Not all migration steps are implemented!");
            }

            if(currentVersion < _REQUIRED_VERSION) {
                // Migration of data and structure

                // Check we have SQLite as databse
                var connection = context.Database.Connection as SQLiteConnection;
                if(connection != null) {
                    // Close prior connection if open
                    if(connection.State == System.Data.ConnectionState.Open) {
                        connection.Close();
                    }

                    // get origin connection string
                    var originConnectionString = connection.ConnectionString;

                    // Create new connection for migration (required to override ForeignKey beahvior of the origin connection)
                    var migrationConnectionString = new SQLiteConnectionStringBuilder(originConnectionString) {
                        ForeignKeys = false
                    }.ToString();

                    // assign the new connection string to the context
                    connection.ConnectionString = migrationConnectionString;

                    // Open connection for migration
                    connection.Open();
                    using(var transaction = connection.BeginTransaction()) {
                        // Migrate structure, before migrating data
                        for(int i = currentVersion; i < _REQUIRED_VERSION; i++) {
                            _MIGRATION_STEPS[i].MigrateStructure(context);
                        }

                        for(int i = currentVersion; i < _REQUIRED_VERSION; i++) {
                            _MIGRATION_STEPS[i].MigrateData(context);
                        }

                        // Set Version to required version
                        context.Version = _REQUIRED_VERSION;

                        // Commit all migration changes to the database
                        transaction.Commit();
                    }

                    // Close migration connection
                    connection.Close();

                    // Set the connection string to the origin value
                    connection.ConnectionString = originConnectionString;
                }
            }
        }
        #endregion
    }
}

Wir legen in dem Initialisierer die Version fest, die wir momentan für unser Programm benötigen (_REQUIRED_VERSION). Im Array _MIGRATION_STEPS initialisieren wir alle Klassen, die für die Migration notwendig sind. Der Index entspricht dabei der Version, von der es zur nächsten Version migriert werden soll.

Im Beispiel haben wir nur einen Schritt, mit Index 0, der dann aufgerufen wird, wenn die Datenbank neu erzeugt wird (Version 0).

Durch die for-Schleife werden alle Migrationsschritte durchlaufen, angefangen mit der aktuellen Version der Datenbank, bis zur aktuell notwendigen Version. Die for-Schleife muss dabei zwei Mal durchlaufen werden. Beim ersten Lauf wird die Struktur der Datenbank angepasst (meistens durc SQL-Scripte), damit die Datenbank wieder dem aktuellen CodeFirst Modell entspricht. Im zweiten Lauf werden dann die eventuell notwendigen Daten hinzugefügt oder verändert.

Im letzten Schritt wird noch die Versionsnummer der Datenbank auf die benötigte Version gesetzt.

Die Gesamte Migration läuft in eigener Transaktion ab. Somit wird entweder die Datenbank mit allen Migrationsschritten durchlaufen, oder gar nicht.

Wenn man mit eingeschalteten ForeignKey-Constraint in der Verbindung arbeitet, muss ein kleiner Trick eingewendet werden, um Verletzungen bei Struktur-Änderungen zu vermeiden. Wenn ForeignKey bereits in der Verbindung gesetzt wird, kann dieser in den SQL-Scripten nicht mehr an- und ausgeschaltet werden. Aus diesem Grund erzeuge ich während der Migration eine eigene Verbindung, die FereignKey explizit ausschaltet und somit Strukturänderungen ohne Fehler erlaubt. Danach wird dem Context wieder die Originalverbindung zugeordnet.

Diskussion