Distribuzione delle modifiche allo schema del database in un processo di patching [duplicato]

Un approccio relativamente semplice che ho utilizzato per un'app simile di SQL CE è distribuire con l'aggiornamento un elenco di istruzioni SQL che contengono gli aggiornamenti cumulativi allo schema dalla prima versione dello schema inizialmente distribuito. L'istruzione SQL finale nell'elenco aggiornerà una riga in una tabella di ricerca in modo che il numero di versione dello schema sia lo stesso della versione. Le istruzioni SQL possono essere distribuite come file o come risorsa nell'applicazione.

UPDATE lookupTable
SET value = 'alarm.wav'
WHERE lookupType = 'sounds' AND lookupId = 'soundFile' AND value = 'test.wav';

ALTER TABLE Users DROP CONSTRAINT UQ__userEmail__00000000000000E0;

UPDATE lookupTable
SET value = '2.8.0.6'
WHERE lookupType = 'version' AND lookupId = 'DataModelVersion';

Quindi, ogni volta che viene eseguito il programma, è possibile verificare il valore del numero di versione del programma rispetto a quello dello schema corrente e se lo schema non è lo stesso, eseguirà il file di schema più recente per eseguire le modifiche dello schema e quindi aggiorna la versione dello schema, ad esempio

if (!Application.ProductVersion.Equals(schemaVersion)) {
    Schema.UpgradeDatabase(Schema.GetDbConnectionString(), upgradeFile);

Il metodo UpgradeDatabase deve essere in grado di gestire il fatto che, poiché l'elenco delle istruzioni SQL è cumulativo, le istruzioni di inserimento o modifica precedenti possono incontrare errori di vincolo quando vengono eseguite di nuovo su un aggiornamento successivo. Ciò può essere risolto generando solo eccezioni non previste, ad esempio

//SQL Lines are split on ';' and put in a commands array 
foreach (string command in commands) {
    cmd.CommandText = command;
    try {
        cmd.ExecuteNonQuery();
    } catch (Exception exc) {
        // Only throw if it's not an expected error due to rerun of the cumulative statements.
        if (!exc.Message.ToUpper().Contains("VIOLATION OF PRIMARY KEY CONSTRAINT") 
          && !exc.Message.ToUpper().Contains("A DUPLICATE VALUE CANNOT BE INSERTED INTO A UNIQUE INDEX") 
          && !exc.Message.ToUpper().Contains("THE FOREIGN KEY CONSTRAINT DOES NOT EXIST"))
            throw;
    }
}

Naturalmente è necessario eseguire test su ogni versione di patch per assicurarsi che SQL aggiorni correttamente il DB e che gli errori attesi vengano ignorati. Il fatto che le istruzioni SQL siano cumulative semplifica la gestione dei casi in cui un utente potrebbe aver saltato diverse versioni minori prima di installare un rilascio e mantenerlo piuttosto semplice.

Il metodo è relativamente semplice e ha funzionato in modo affidabile per l'aggiornamento delle app client che sono distribuite in remoto. L'unico lato negativo è che non gestisce il ritorno a una versione precedente, ma se è necessario emettere una patch per tornare a uno schema, è possibile aggiungerne altre per ripristinare lo schema precedente.

Scrivi script SQL con un'enorme quantità di errori nella loro gestione (cerca una colonna che non esiste prima di aggiungerla a una tabella, cerca la tabella per non esistere durante la sua creazione, ecc.). È possibile eseguire gli script come parte del pacchetto di installazione (i risultati possono variare in quanto i programmi di installazione diversi possono semplificare o molto più del necessario) oppure è possibile eseguire gli script all'avvio dell'applicazione. Se lo fai, ti consiglio di spostare gli script SQL in un'altra posizione in modo da non provare a eseguirli più di una volta.

Puoi guardare tutti gli installatori disponibili là fuori. Ho usato InnoSetup con buon successo eseguendo tutti i tipi di file eseguibili dopo aver posizionato i miei file nelle posizioni appropriate. Ho sentito che è possibile fare lo stesso con un MSI, ma non l'ho mai fatto funzionare. Se decidi di utilizzare WiX, il suo strumento di masterizzazione può creare un'installazione eseguibile che fornisce sia un programma di installazione eseguibile che un programma di installazione MSI nello stesso pacchetto.