public static void SetDatabaseVersion(CruiseDatastore db, string newVersion)
{
string command = String.Format("UPDATE Globals SET Value = '{0}' WHERE Block = 'Database' AND Key = 'Version';", newVersion);
db.Execute(command);
db.LogMessage($"Updated structure version to {newVersion}");
}
private static void UpdateTo_2_1_1(CruiseDatastore db)
{
var startVersion = db.DatabaseVersion;
db.BeginTransaction();
try
{
db.Execute(@"CREATE TABLE IF NOT EXISTS FixCNTTallyClass (
FixCNTTallyClass_CN INTEGER PRIMARY KEY AUTOINCREMENT,
Stratum_CN INTEGER REFERENCES Stratum NOT NULL,
FieldName INTEGER Default 0);" );
db.Execute(@"CREATE TABLE IF NOT EXISTS FixCNTTallyPopulation (
FixCNTTallyPopulation_CN INTEGER PRIMARY KEY AUTOINCREMENT,
FixCNTTallyClass_CN INTEGER REFERENCES FixCNTTallyClass NOT NULL,
SampleGroup_CN INTEGER REFERENCES SampleGroup NOT NULL,
TreeDefaultValue_CN INTEGER REFERENCES TreeDefaultValue NOT NULL,
IntervalSize INTEGER Default 0,
Min INTEGER Default 0,
Max INTEGER Default 0);" );
SetDatabaseVersion(db, "2.1.1");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(startVersion, "2.1.1", e);
}
}
public static void LogMessage(this DbConnection connection, string message, string level = "I", string program = null, DbTransaction transaction = null)
{
if (connection is null)
{
throw new ArgumentNullException(nameof(connection));
}
if (message is null)
{
throw new ArgumentNullException(nameof(message));
}
if (program == null)
{
program = CruiseDatastore.GetCallingProgram();
}
Logger.Log(message, "LogMessage", LogLevel.Info);
connection.ExecuteNonQuery("INSERT INTO MessageLog (Program, Message, Level, Date, Time) " +
"VALUES " +
"(@p1, @p2, @p3, @p4, @p5)",
new object[] {
program,
message,
level,
DateTime.Now.ToString("yyyy/MM/dd"),
DateTime.Now.ToString("HH:mm")
}
, transaction
);
}
private static void UpdateToVersion2014_07_17(CruiseDatastore db)
{
var startVersion = db.DatabaseVersion;
try
{
db.BeginTransaction();
db.AddField("VolumeEquation", new ColumnInfo("MerchModFlag", "INTEGER")
{
Default = "0"
});
db.AddField("SampleGroupStats", new ColumnInfo("ReconPlots", "INTEGER")
{
Default = "0"
});
db.AddField("SampleGroupStats", new ColumnInfo("ReconTrees", "INTEGER")
{
Default = "0"
});
SetDatabaseVersion(db, "2014.07.17");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(startVersion, "2014.07.17", e);
}
}
private static void UpdateToVersion2014_10_01(CruiseDatastore db)
{
var startVersion = db.DatabaseVersion;
try
{
db.BeginTransaction();
db.Execute("DROP TABLE Regression;");
db.Execute(@"CREATE TABLE Regression (
Regression_CN INTEGER PRIMARY KEY AUTOINCREMENT,
rVolume TEXT,
rVolType TEXT,
rSpeices TEXT,
rProduct TEXT,
rLiveDead TEXT,
CoefficientA REAL Default 0.0,
CoefficientB REAL Default 0.0,
CoefficientC REAL Default 0.0,
TotalTrees INTEGER Default 0,
MeanSE REAL Default 0.0,
Rsquared REAL Default 0.0,
RegressModel TEXT,
rMinDbh REAL Default 0.0,
rMaxDbh REAL Default 0.0);" );
SetDatabaseVersion(db, "2014.10.01");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(startVersion, "2014.10.01", e);
}
}
// UpdateTo_3_4_1 forgot to add CountOrMeasure, TreeCount, and AverageHeight fields to
// the Plot_Stratum table. This update checks to see if they need to be added and adds them
// if missing
private void UpdateTo_3_4_2(CruiseDatastore db)
{
var curVersion = db.DatabaseVersion;
var targetVersion = "3.4.2";
if (db.CheckFieldExists("Plot_Stratum_Tombstone", "CountOrMeasure") is false)
{
db.BeginTransaction();
try
{
db.Execute("ALTER TABLE Plot_Stratum_Tombstone ADD COLUMN CountOrMeasure TEXT COLLATE NOCASE;");
db.Execute("ALTER TABLE Plot_Stratum_Tombstone ADD COLUMN TreeCount INTEGER Default 0;");
db.Execute("ALTER TABLE Plot_Stratum_Tombstone ADD COLUMN AverageHeight REAL Default 0.0;");
SetDatabaseVersion(db, targetVersion);
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(curVersion, targetVersion, e);
}
}
else
{
SetDatabaseVersion(db, targetVersion);
}
}
//patch for some a version that got out in the wild with bad triggers
private static void UpdateToVersion2015_09_01(CruiseDatastore db)
{
var startVersion = db.DatabaseVersion;
db.BeginTransaction();
try
{
//because there are a lot of changes with triggers
//lets just recreate all triggers
foreach (string trigName in ListTriggers(db))
{
db.Execute("DROP TRIGGER " + trigName + ";");
}
//db.Execute(Schema.Schema.CREATE_TRIGGERS);
SetDatabaseVersion(db, "2015.09.01");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(startVersion, "2015.09.01", e);
}
}
public static void RecreateView(CruiseDatastore datastore, IViewDefinition viewDef)
{
var viewName = viewDef.ViewName;
datastore.Execute($"DROP VIEW {viewName};");
datastore.Execute(viewDef.CreateView);
}
private static void UpdateTo_2_7_1(CruiseDatastore db)
{
var version = db.DatabaseVersion;
var targetVersion = "2.7.1";
try
{
db.Execute(
@"DROP TABLE TreeEstimate;
CREATE TABLE TreeEstimate (
TreeEstimate_CN INTEGER PRIMARY KEY AUTOINCREMENT,
CountTree_CN INTEGER,
TreeEstimate_GUID TEXT,
KPI REAL NOT NULL,
CreatedBy TEXT DEFAULT 'none',
CreatedDate DateTime DEFAULT (datetime(current_timestamp, 'localtime')) ,
ModifiedBy TEXT ,
ModifiedDate DateTime );" );
db.ReleaseConnection();
SetDatabaseVersion(db, targetVersion);
}
catch (Exception e)
{
throw new SchemaUpdateException(version, targetVersion, e);
}
}
// update 3.3.3 notes:
// added column TemplateFile to Cruise table
// added lookup table LK_TallyEntryType
// remove check constraint on EntryType and add FKey on EntryType
private void UpdateTo_3_3_3(CruiseDatastore db)
{
var curVersion = db.DatabaseVersion;
var targetVersion = "3.3.3";
db.BeginTransaction();
try
{
db.Execute("ALTER TABLE main.Cruise ADD COLUMN TemplateFile TEXT;");
// create table LK_TallyEntryType
var tallyEntryTypeTableDef = new LK_TallyEntryType();
CreateTable(db, tallyEntryTypeTableDef);
// remove check constraint on EntryType and add FKey on EntryType
var tallyLedgerTableDef = new TallyLedgerTableDefinition();
UpdateTableDDL(db, tallyLedgerTableDef);
SetDatabaseVersion(db, targetVersion);
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(curVersion, targetVersion, e);
}
}
// update 3.2.2 notes: Added table LK_District and updated initialization for LK_Forests
public static void UpdateTo_3_2_2(CruiseDatastore ds)
{
// create an in-memory database
// to migrate into
using (var newDatastore = new CruiseDatastore_V3())
{
var excludeTables = new[]
{
"LK_CruiseMethod",
"LK_District",
"LK_FIA",
"LK_Forest",
"LK_LoggingMethod",
"LK_Product",
"LK_Purpose",
"LK_Region",
"LK_UOM",
"LogField",
"TreeField",
};
// migrate contents of old db into new in-memory database
Migrate(ds, newDatastore, excludeTables);
// use back up rutine to replace old database with
// migrated contents
newDatastore.BackupDatabase(ds);
}
}
// update 3.3.2 notes:
// added Biomass and ValueEquation tables
// changed TallyPopulation view so that 3p methods are always treaded as tally by subpop
private void UpdateTo_3_3_2(CruiseDatastore db)
{
var curVersion = db.DatabaseVersion;
var targetVersion = "3.3.2";
db.BeginTransaction();
try
{
var biomassTableDef = new BiomassEquationTableDefinition();
var valueEqTableDef = new ValueEquationTableDefinition();
CreateTable(db, biomassTableDef);
CreateTable(db, valueEqTableDef);
// allways treat 3p methods as tally by subpop
var tallyPopViewDef = new TallyPopulationViewDefinition_3_3_2();
RecreateView(db, tallyPopViewDef);
SetDatabaseVersion(db, targetVersion);
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(curVersion, targetVersion, e);
}
}
private static void UpdateToVersion2015_08_19(CruiseDatastore db)
{
var startVersion = db.DatabaseVersion;
var tavCols = db.GetTableInfo("TreeAuditValue");
bool hasErrorMessageCol = false;
foreach (ColumnInfo col in tavCols)
{
if (col.Name == "ErrorMessage")
{
hasErrorMessageCol = true; break;
}
}
try
{
db.BeginTransaction();
if (!hasErrorMessageCol)
{
db.AddField("TreeAuditValue", new ColumnInfo("ErrorMessage", "TEXT"));
}
SetDatabaseVersion(db, "2015.08.19");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(startVersion, "2015.08.19", e);
}
}
public static string[] ListFieldsIntersect(CruiseDatastore db, string table1, string table2)
{
var sourceFields = db.QueryScalar2 <string>($@"SELECT '""' || Name || '""' FROM pragma_table_info('{table1}');");
var destFields = db.QueryScalar2 <string>($@"SELECT '""' || Name || '""' FROM pragma_table_info('{table2}');");
var both = sourceFields.Intersect(destFields).ToArray();
return(both);
}
public void Update(CruiseDatastore datastore)
{
//PatchSureToMeasure(db);
Update_Impl(datastore);
// the following method calls are not nessicary for updating the
// database. They just need to be ran to clean up potential errors.
CleanupErrorLog(datastore);
FixTreeAuditValueFKeyErrors(datastore);
}
// IMPORTANT: additional steps must be done before and after calling this method
// before you must:
// 1) set PRAGMA foreign_keys=off
// 2) begin a transaction
// after you must:
// 1) commit the transaction
// 2) set PRAGMA foreign_keys back to what it was beofore
// see https://www.sqlite.org/lang_altertable.html#otheralter for more details on the procedure for rebuilding a table.
public static void RebuildTable(CruiseDatastore db, ITableDefinition tableDef, IEnumerable <KeyValuePair <string, string> > customFieldMaps = null)
{
var tableName = tableDef.TableName;
var tempTableName = "new_" + tableName;
var createNewTable = tableDef.GetCreateTable(tempTableName);
db.Execute(createNewTable);
var fieldIntersectArray = ListFieldsIntersect(db, tableName, tempTableName);
var fieldListFrom = new List <string>(fieldIntersectArray);
var fieldListTo = new List <string>(fieldIntersectArray);
if (customFieldMaps != null)
{
foreach (var map in customFieldMaps)
{
var i = fieldListTo.FindIndex(x => string.Compare(x, map.Key, true) is 0);
if (i > 0)
{
fieldListTo[i] = map.Value;
}
else
{
fieldListTo.Add(map.Key);
fieldListFrom.Add(map.Value);
}
}
}
db.Execute($"INSERT INTO main.{tempTableName} ( {fieldListTo.Aggregate((a, b) => a + ", " + b)} ) SELECT {fieldListFrom.Aggregate((a, b) => a + ", " + b)} FROM main.{tableName};");
db.Execute($"DROP TABLE main.{tableName};");
db.Execute($"ALTER TABLE {tempTableName} RENAME TO {tableName}");
var createIndexes = tableDef.CreateIndexes;
if (createIndexes != null)
{
db.Execute(createIndexes);
}
var triggers = tableDef.CreateTriggers;
if (triggers != null)
{
foreach (var trigger in triggers)
{
db.Execute(trigger);
}
}
}
public static void Migrate(CruiseDatastore sourceDS, CruiseDatastore destinationDS, IEnumerable <string> excluding = null, bool excludeLookupTables = false)
{
var destConn = destinationDS.OpenConnection();
var sourceConn = sourceDS.OpenConnection();
try
{
Migrate(sourceConn, destConn, excluding);
}
finally
{
destinationDS.ReleaseConnection();
sourceDS.ReleaseConnection();
}
}
public static void UpdateTo_3_1_0(CruiseDatastore ds)
{
// create an in-memory database
// to migrate into
using (var newDatastore = new CruiseDatastore_V3())
{
var excludeTables = new[] { "SamplerState" };
// migrate contents of old db into new in-memory database
Migrate(ds, newDatastore, excludeTables);
// use back up rutine to replace old database with
// migrated contents
newDatastore.BackupDatabase(ds);
}
}
// update 3.3.1 notes:
// Add CruiseID fields to Log and Stem tables
// Change is fully backwards compatible with prior versions
private void UpdateTo_3_3_1(CruiseDatastore db)
{
var curVersion = db.DatabaseVersion;
var targetVersion = "3.3.1";
var fKeys = db.ExecuteScalar <string>("PRAGMA foreign_keys;");
db.Execute("PRAGMA foreign_keys=OFF;");
db.BeginTransaction();
try
{
// need to drop any views associated with tables we are rebuilding
db.Execute("DROP VIEW LogGradeError;");
var logTableDef = new LogTableDefinition();
var stemTableDef = new StemTableDefinition();
db.Execute("DROP TABLE Log_Tombstone;");
db.Execute("DROP TABLE Stem_Tombstone;");
db.Execute(logTableDef.CreateTombstoneTable);
db.Execute(stemTableDef.CreateTombstoneTable);
RebuildTable(db, logTableDef, customFieldMaps: new KeyValuePair <string, string>[]
{
new KeyValuePair <string, string>("CruiseID", "(SELECT CruiseID FROM Tree WHERE Tree.TreeID = Log.TreeID)"),
});
RebuildTable(db, stemTableDef, customFieldMaps: new KeyValuePair <string, string>[]
{
new KeyValuePair <string, string>("CruiseID", "(SELECT CruiseID FROM Tree WHERE Tree.TreeID = Stem.TreeID)"),
});
var lgeViewDef = new LogGradeErrorViewDefinition();
db.Execute(lgeViewDef.CreateView);
SetDatabaseVersion(db, targetVersion);
db.CommitTransaction();
db.Execute($"PRAGMA foreign_keys={fKeys};");
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(curVersion, targetVersion, e);
}
}
// some table alterations can be done by manualy editing the stored create table statments
// this method IS NOT APROPRIATE for removing or reordering colums, removing unique constraints or removing primary key constraints.
// this method IS APROPRIATE for removing check, foreign key, not null, or default value constraints.
// https://www.sqlite.org/lang_altertable.html#otheralter
// IMPORTANT: call BeginTransaction before calling this method and EndTransaction after
// you may allso need to drop and recreate any views, triggers, or indexes depending on what you have changed on the table
public static void UpdateTableDDL(CruiseDatastore db, ITableDefinition tableDef)
{
var schemaVersion = db.ExecuteScalar <int>("PRAGMA schema_version;");
db.Execute("PRAGMA writable_schema=ON;");
var tableName = tableDef.TableName;
db.Execute("UPDATE sqlite_master SET sql=@p1 WHERE type='table' AND name=@p2", tableDef.CreateTable, tableName);
db.Execute($"PRAGMA schema_version={schemaVersion + 1};");
var integrityCheck = db.QueryScalar <string>("PRAGMA main.integrity_check;").ToArray();
if (integrityCheck.FirstOrDefault() != "ok")
{
Logger.Log(String.Join("|", integrityCheck), "Updater_V3", FMSC.ORM.Logging.LogLevel.Trace);
throw new SchemaException("Integrity Check Failed While Updating Table Definition On " + tableName);
}
}
public void MigrateFromV3ToV2(string cruiseID, CruiseDatastore_V3 v3db, CruiseDatastore v2db, string createdBy = null)
{
var v3DbAlias = "v3";
v2db.AttachDB(v3db, v3DbAlias);
try
{
var connection = v2db.OpenConnection();
MigrateFromV3ToV2(cruiseID, connection, createdBy, v3DbAlias, v2db.ExceptionProcessor, Migrators);
}
finally
{
v3db.DetachDB(v3DbAlias);
v2db.ReleaseConnection();
}
}
private static void UpdateTo_2_6_1(CruiseDatastore db)
{
var version = db.DatabaseVersion;
var targetVersion = "2.6.1";
db.BeginTransaction();
try
{
db.Execute(
@"CREATE TABLE SamplerState (
SamplerState_CN INTEGER PRIMARY KEY AUTOINCREMENT,
SampleGroup_CN INTEGER NOT NULL,
SampleSelectorType TEXT COLLATE NOCASE,
BlockState TEXT,
SystematicIndex INTEGER DEFAULT 0,
Counter INTEGER DEFAULT 0,
InsuranceIndex DEFAULT -1,
InsuranceCounter DEFAULT -1,
ModifiedDate DateTime,
UNIQUE (SampleGroup_CN),
FOREIGN KEY (SampleGroup_CN) REFERENCES SampleGroup (SampleGroup_CN) ON DELETE CASCADE ON UPDATE CASCADE
);");
db.Execute(
@"CREATE TRIGGER SamplerState_OnUpdate
AFTER UPDATE OF
BlockState,
Counter,
InsuranceCounter
ON SamplerState
FOR EACH ROW
BEGIN
UPDATE SamplerState SET ModifiedDate = datetime('now', 'localtime') WHERE SamplerState_CN = old.SamplerState_CN;
END;
");
SetDatabaseVersion(db, targetVersion);
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(version, targetVersion, e);
}
}
private void UpdateTo_3_4_0(CruiseDatastore db)
{
var curVersion = db.DatabaseVersion;
var targetVersion = "3.4.0";
var fKeys = db.ExecuteScalar <string>("PRAGMA foreign_keys;");
db.Execute("PRAGMA foreign_keys=OFF;");
db.BeginTransaction();
try
{
RebuildTable(db, new CruiseTableDefinition_3_4_0(), customFieldMaps:
new KeyValuePair <string, string>[] { new KeyValuePair <string, string>(
"SaleNumber",
"(SELECT SaleNumber FROM Sale WHERE cruise.SaleID = sale.SaleID)") });
if (fKeys == "ON")
{
var keyCheck = db.QueryGeneric("PRAGMA foreign_key_check;");
if (keyCheck.Any())
{
throw new SchemaException("Foreign Key Check failed");
}
}
db.Execute("CREATE INDEX NIX_TreeDefaultValue_PrimaryProduct ON TreeDefaultValue ('PrimaryProduct');");
var tree_tdvViewDef = new Tree_TreeDefaultValue();
db.Execute(tree_tdvViewDef.CreateView);
var tm_defViewDef = new TreeMeasurment_DefaultResolved();
db.Execute(tm_defViewDef.CreateView);
SetDatabaseVersion(db, targetVersion);
db.CommitTransaction();
db.Execute($"PRAGMA foreign_keys={fKeys};");
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(curVersion, targetVersion, e);
}
}
private static void UpdateToVersion2014_09_02(CruiseDatastore db)
{
var startVersion = db.DatabaseVersion;
try
{
db.BeginTransaction();
db.AddField("SampleGroup", new ColumnInfo("TallyMethod", "TEXT"));
SetDatabaseVersion(db, "2014.09.02");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(startVersion, "2014.09.02", e);
}
}
// add validation on tree for DBH and DRC
private void UpdateTo_3_4_3(CruiseDatastore db)
{
var curVersion = db.DatabaseVersion;
var targetVersion = "3.4.3";
db.BeginTransaction();
try
{
db.Execute("DROP VIEW TreeError;");
db.Execute(TreeErrorViewDefinition.v3_4_3);
SetDatabaseVersion(db, targetVersion);
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(curVersion, targetVersion, e);
}
}
private static void UpdateTo_2_7_3(CruiseDatastore db)
{
var startVersion = db.DatabaseVersion;
var targetVersion = "2.7.3";
try
{
db.BeginTransaction();
db.Execute("DROP VIEW IF EXISTS CountTree_View;");
db.Execute("DROP VIEW IF EXISTS StratumAcres_View;");
SetDatabaseVersion(db, targetVersion);
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(startVersion, targetVersion, e);
}
}
// update notes: changed view TreeAuditError
public static void UpdateTo_3_2_3(CruiseDatastore datastore)
{
datastore.BeginTransaction();
try
{
var viewDef = new TreeAuditErrorViewDefinition();
RecreateView(datastore, viewDef);
datastore.Execute("DELETE FROM TreeField WHERE Field = 'MetaData';");
datastore.Execute("INSERT INTO TreeField (Field, DefaultHeading, DbType, IsTreeMeasurmentField) VALUES ('MetaData', 'Meta Data', 'TEXT', 1)");
SetDatabaseVersion(datastore, "3.2.3");
datastore.CommitTransaction();
}
catch
{
datastore.RollbackTransaction();
}
}
private static void UpdateToVersion2014_08_20(CruiseDatastore db)
{
var startVersion = db.DatabaseVersion;
try
{
db.BeginTransaction();
db.AddField("VolumeEquation", new ColumnInfo("EvenOddSegment", "INTEGER")
{
Default = "0"
});
SetDatabaseVersion(db, "2014.08.20");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(startVersion, "2014.08.20", e);
}
}
// Add TreeCount, Average Height, and CountOrMeasure fields to plot stratum
private void UpdateTo_3_4_1(CruiseDatastore db)
{
var curVersion = db.DatabaseVersion;
var targetVersion = "3.4.1";
var fKeys = db.ExecuteScalar <string>("PRAGMA foreign_keys;");
db.Execute("PRAGMA foreign_keys=OFF;");
db.BeginTransaction();
try
{
//Rebuild Plot_Stratum table
db.Execute("DROP VIEW main.PlotError");
RebuildTable(db, new Plot_StratumTableDefinition_3_4_1());
db.Execute(new PlotErrorViewDefinition().CreateView);
//create a bunch of clearTombstone triggers
db.Execute(CuttingUnit_StratumTableDefinition.CREATE_TRIGGER_CuttingUnit_Stratum_OnInsert_ClearTombstone);
db.Execute(LogFieldSetupTableDefinition.CREATE_TRIGGER_LogFieldSetup_OnInsert_ClearTombstone);
db.Execute(SubPopulationTableDefinition.CREATE_TRIGGER_SubPopulation_OnInsert_ClearTombstone);
db.Execute(TreeAuditRuleSelectorTableDefinition.CREATE_TRIGGER_TreeAuditRuleSelector_OnInsert_ClearTombstone);
db.Execute(TreeFieldSetupTableDefinition.CREATE_TRIGGER_TreeFieldSetup_OnInsert_ClearTombstone);
db.Execute(ReportsTableDefinition.CREATE_TRIGGER_Reports_OnInsert_ClearTombstone);
db.Execute(VolumeEquationTableDefinition.CREATE_TRIGGE_VolumeEquation_OnInsert_ClearTombstone);
// recreate index on Reports_Tombstone
db.Execute("DROP INDEX Reports_Tombstone_ReportID;");
db.Execute("CREATE INDEX Reports_Tombstone_ReportID_CruiseID ON Reports_Tombstone (ReportID, CruiseID);");
SetDatabaseVersion(db, targetVersion);
db.CommitTransaction();
db.Execute($"PRAGMA foreign_keys={fKeys};");
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(curVersion, targetVersion, e);
}
}
private static void UpdateTo_2_2_0(CruiseDatastore db)
{
var startVersion = db.DatabaseVersion;
try
{
db.BeginTransaction();
db.Execute(
@"CREATE TABLE LogGradeAuditRule (
Species TEXT,
DefectMax REAL Default 0.0,
ValidGrades TEXT);");
SetDatabaseVersion(db, "2.2.0");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(startVersion, "2.2.0", e);
}
}