Basierend auf Datenbankprovider verschiedene Migrationen verwenden

Hallo zusammen,

ich wende mich an euch da ich nicht mehr weiter weis und ich wissen möchte ob ich mein Konzept falsch aufgezogen habe und dies anpassen muss.

Nun zu meiner Anwendung:

Aktuell ist diese nur lokal (SQLite) nutzbar. In einer weiteren Ausbaustufe ist es nun möglich diese mit einer PostgresSQL Datenbank zu verbinden.

Sobald die Anwendung startet wird geprüft ob wir lokal oder remote arbeiten möchten und dementsprechend wird der SQLite oder Postgres Factory geladen.

Anschließend wird die Datenbankstruktur erstellt (siehe "_CacheDbRessources()" und "_RunMigrations"), sollte diese noch nicht erstellt worden sein und weitere Migrationen werden durchgeführt.

        /// <summary>
        /// Reads all ressource names from the assembly and saves those containing the db namespace in a private list
        /// </summary>
        private void _CacheDbRessources()
        {
            _RessourcesNamespace = $"Foo.Data.{_ProviderInvariantName}";
            _RessourceNames = Assembly.GetExecutingAssembly().GetManifestResourceNames().Where(item => item.Contains(_RessourcesNamespace));
        }

        /// <summary>
        /// Does the first-time setup for the database and runs migrations. 
        /// </summary>
        private void _RunMigrations()
        {
            IEnumerable<string> migrations =
                _RessourceNames.Where(item => item.Contains("_Migrations"))
                    .Select(s => s.Replace(_RessourcesNamespace + ".", ""))
                    .OrderBy(item => item);

            if (migrations.Any())
                _Logger.Debug("Running database migrations...");

            using (DbConnection conn = GetConnection())
            {
                foreach (string item in migrations)
                {
                    using (DbTransaction tran = conn.BeginTransaction())
                    {
                        _Logger.Debug("Executing query '{0}'...", item);
                        using (DbCommand cmd = conn.CreateCommand(GetQueryResource(item), tran))
                        {
                            int rows = cmd.ExecuteNonQuery();
                            _Logger.Debug($"{rows} rows where affected.");
                        }

                        tran.Commit();
                    }
                }
            }
        }

Auschnitt aus 000_InitalCreate.sql

/* users */
CREATE TABLE IF NOT EXISTS "users" (
    "ident" INTEGER PRIMARY KEY AUTOINCREMENT,
    "user_name" TEXT NOT NULL UNIQUE,
    "real_name" TEXT,
    "password" TEXT NOT NULL,
    "userlevel" INTEGER NOT NULL DEFAULT 0,
    "enabled" INTEGER NOT NULL DEFAULT 1,
    "language" TEXT,
    "lock" TEXT
);

Die Idee dahinter, sobald der Kunde zum Beispiel MSSQL Support wünscht, erzeuge ich einen Ordner "MSSQL" und erstelle dort eine "000_InitialCreate.sql" Datei und den weiteren angepassten Abfragen.

Mein Problem

In der neuen Ausbaustufe muss eine zusätzliche Spalte ("skiptime") in der Tabelle "recipes" angelegt werden. In der postgres 000_InitalCreate.sql konnte ich dies ohne Probleme einfach einfügen

/* recipes */
CREATE TABLE IF NOT EXISTS "recipes" (
    "ident" SERIAL PRIMARY KEY,
    "identifier" TEXT NOT NULL UNIQUE,
    "name" TEXT,
    "description" TEXT,
    "crosshair_color" TEXT NOT NULL DEFAULT '#00ff00',
    "crosshair_weight" REAL NOT NULL DEFAULT 1.0,
    "crosshair_padding" REAL NOT NULL DEFAULT 2.5,
    "created_on" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    "created_by" TEXT,
    "modified_on" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    "modified_by" TEXT,
	"blocks" TEXT,
	"blocks_mode" INTEGER NOT NULL DEFAULT 0,
    "lock" TEXT,
    "skiptime" INTEGER NOT NULL DEFAULT 5
);

jedoch ist diese Spalte in der SQLite Datenbank noch nicht vorhanden. Über folgende Query

ALTER TABLE recipes ADD COLUMN skiptime INTEGER NOT NULL DEFAULT 5;

kann ich diese einfügen. Bei einem erneuten Aufruf würde dies jedoch eine Exception werfen, da die Spalte bereits vorhanden ist. In SQLite ist es nicht möglich mit IF,ELSE zu arbeiten und die Logik im C# Teil ausarbeiten wollte ich vermeiden.

Nun hat einer eine Idee wie ich das Problem lösen kann?

Danke
Gruß D.Jonas

Hat das einen Grund, dass Du zB nicht FluentMigrator nutzt, der sich darum automatisiert kümmert?

Also den FluentMigrator kenne ich nicht, habe ich auch noch nie gehört. Wir hatten am Anfang das EntityFramework im Einsatz. Mussten jedoch mit Erschrecken feststellen, dass es ein Horror ist und unseren Zwecken nicht gerecht wurde (anderes Thema). Demzufolge haben wir uns dazu entschieden alle Abfragen selbst zu schreiben damit wir genau wissen was passiert.

Ich habe mir jetzt schon ein Lösungsansatz überlegt. Ich verschiebe die "000_InitialCreate.sql" Dateien in einen separaten Ordner "_InitialCreate" und lasse diese immer zuerst durchlaufen. Diese erzeugen mir auch keine Exceptions (i.d.R.).

Im _Migrations Ordner bekommen die Abfragen eine neue Syntax. Jede Datei besteht nun quasi aus zwei Dateien. 1. "xxx_foo.sql" 2. "xxx_foo_condition.sql". Nur wenn die condition sql eine true zurück liefert, führe ich die zugehörige Datei auch aus.
In meinem Beispiel:

"001_ExtendRecipe.sql" und "001_ExtendRecipe_condition.sql".

In der "*condition.sql" steht nur eine Abfrage drinnen die überprüft ob die Spalte schon vorhanden ist oder eben nicht. Dann kann ich dieses Schema weiter verfolgen und bleibe dynamisch.

Gruß

@d.jones
Was ihr da baut entspricht fast schon den Migrations von Entity Framework, auch wenn ihr eher den Sql Befehl einfach in die DB pump während EF aus dem Code dann die spezifischen Anweisungen generiert und entsprechend alles selbst erledigt.

Was ihr braucht ist eine Versionierung der Tabellen.
Dazu wäre es ratsam, dass ihr eine Versiontabelle mit der Infor Tabelle + aktuelle Version pflegt.
Eure Migrationen müssten dann ebenfalls pro Version die beim Start der Anwendung durchlaufen werden und nur neue Migrationen durchgeführt werden.

Ansonsten liese sich euer Problem nicht einfach lösen, da ihr aktuell keine Informationen habt was in der DB Vorhanden ist und was nicht.
Ihr könntet sonst nur mit viel Aufwand die DB Schemas durchlaufen und alles einzeln prüfen.
Aber dies dürfte mit eurer aktuellen Migration mit einfachen Sql Befehlen nicht funktionieren.

Entsprechend ist euer System hier zu einfach um solche Fälle abzufangen.
Entweder ihr müsstest eurer System ausbauen, eben mit einer Versionierung, oder ihr müsst euren ganzen Code umbauen um die Schema durchzuarbeiten.

Beides dürfte nicht praktikabel sein.
Ich würde ihr eher zu einem ordentlichen OR Mapper greifen, der beide Systeme supportet.
EF Core und auch EF6 dürften beides abdecken.
Warum dies in eurem System nicht klappt, lässt sich nicht sagen.
Als Horror empfand ich EF Core bisher nicht.
War sogar recht simpel und extrem flexibel.
Hab sowohl mit Sqlite SQL Server 2016 und PostgreSql arbeiten und migrieren können.
Die Performance selbst war dann nur von der DB Performance abhängig und lief soweit ohne Probleme durch.

T-Virus

Von EF und EF Migrations bin ich auch kein Fan.
Aber Dapper (als Micro ORM) und FluentMigrator für die Schemen ist bestens zu empfehlen.

Was ihr da macht sieht schon sehr nach Not-invented-here-Syndrom aus und wird mit der Zeit sicherlich nicht besser und einfacher in der Erweiterung und Pflege.
Ohne in der Datenbank abzulegen, welche Version ihr aktuell ausgerollt habt und darauf zu vertrauen, wird das sehr wahrscheinlich nicht zuverlässig werden.

EF Core ist noch (leider) eher weit weg vom produktiven Einsatz, der mehr als hello world beinhaltet. EF Core kann aktuell nicht mal Group By (technologisch schon, aber es werden alle Daten auf den Client geladen und dort gruppiert. EF Core kann kein Group By auf DB Ebene).
Evtl. wird das mit EF Core 2.0 besser, aber 1.1 bitte nur zum Kennenlernen oder sehr einfache Szenarien verwenden.

Ich habs evaluiert einer meiner Projekte von Dapper auf EF Core zu migrieren.... ich habs nach einigen Tagen guten Willens und Ansporns dann wieder sein lassen, weil EF Core einfach noch nicht so weit ist.
Daher ein gut gemeinter Rat: aktuell eher Finger weg wenns produktiv werden soll. Wenn wirklich EF; dann eher noch EF 6.

Für sqlite empfehle ich dir eher migrator.net(https://www.nuget.org/packages/DotNetProjects.Migrator/). Ich hab da einiges an arbeit reingesteckt das in Sqlite fast alles möglich ist was auch in anderen DbEngines geht und in Sqlite normal nicht möglich ist (Spalten umbenennen, Indexe erzeugen, Datentyp ändern,...)

Hat das einen Grund, dass Du zB nicht FluentMigrator nutzt, der sich darum automatisiert kümmert?

Ich habe mich heute Morgen gleich mal hin gesetzt und den FluentMigrator näher angeschaut. Bis jetzt bin ich von dem Konzept ehrlich gesagt begeistert. Beim ApplicationStart kann ich die neue Tabelle gleich erstellen lassen. Sowohl in Postgres als auch in SQLite funktioniert dies einwandfrei. Auch in einer zweiten Migrierung "Add Column" gibts keine Probleme und keine neue Exception dank der Versionierung.

Aber: Query for dropping Sqlite columns is not supported by Sqlite
https://github.com/fluentmigrator/fluentmigrator/issues/7
Möchte ich in einer 3ten Migrierung eine Column wieder löschen, wird dies von SQLite nicht untersützt. Hast du dafür einen Workaround? Anderseits ob dies jemals gemacht werden muss sei dahin gestellt.

Frage: Wenn ich das richtig verstanden habe, benutzt man den FluentMigrator ausschließlich dafür und die Tatsache die SQL-Queries auszuprogrammieren bleibt?

Was ihr braucht ist eine Versionierung der Tabellen.
Dazu wäre es ratsam, dass ihr eine Versiontabelle mit der Infor Tabelle + aktuelle Version pflegt.
Eure Migrationen müssten dann ebenfalls pro Version die beim Start der Anwendung durchlaufen werden und nur neue Migrationen durchgeführt werden.

Das wird dank dem FluentMigrator nun auch erledigt. Danke trotzdem!

Als Horror empfand ich EF Core bisher nicht.
War sogar recht simpel und extrem flexibel.

Ein sehr großes Problem war das man nie nur ein Teil von Daten lesen konnte, sondern immer alles (also zum Beispiel auch Bilder die in der Spalte mit abgelegt sind) mit laden musste. Das hat enormen Traffic verursacht..
Wie Abt schon schrieb:

EF Core kann aktuell nicht mal Group By (technologisch schon, aber es werden alle Daten auf den Client geladen und dort gruppiert. EF Core kann kein Group By auf DB Ebene).

So damit meine existierene Datenbank (Die auch schon bereits bei mehreren Kunden vorhanden ist und mit Daten befüllt; SQLite) funktioniert würde, sollten folgende Befehle ausreichend sein, oder irre ich mich da?

Diese würde ich zusätzlich bei SQLite durchlaufen lassen

CREATE TABLE IF NOT EXISTS "VersionInfo" (
	"Version" INTEGER NOT NULL, 
	"AppliedOn" DATETIME, 
	"Description" TEXT
);

CREATE UNIQUE INDEX IF NOT EXISTS "UC_Version" ON "VersionInfo" ("Version" ASC);
INSERT OR IGNORE INTO `VersionInfo` VALUES (1,strftime('%Y-%m-%dT%H:%M:%S', datetime()),'_Migration');

EDIT:
Leider musste ich feststellen das man keine Trigger anlegen kann. Das ist dann doch ziemlich bescheiden..

Postgres

CREATE OR REPLACE FUNCTION update_component_timestamp_proc()
RETURNS TRIGGER AS $$
BEGIN
    NEW.modified_on = now();
    RETURN NEW;   
END;
$$ language 'plpgsql';

DROP TRIGGER IF EXISTS update_component_timestamp ON components;
CREATE TRIGGER update_component_timestamp AFTER UPDATE ON components FOR EACH ROW EXECUTE PROCEDURE update_component_timestamp_proc();

SQLite

CREATE TRIGGER IF NOT EXISTS "components_modified_on" AFTER UPDATE ON "components"
BEGIN
    UPDATE "components" SET "modified_on" = CURRENT_TIMESTAMP WHERE "ident" = NEW."ident";
END;

wegen der sqlite probleme hab ich migrator.net empfohlen, der sqlite support ist da um welten besser!