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);
}
}
// 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);
}
}
//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);
}
}
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);
}
}
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);
}
}
// 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);
}
}
// 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.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);
}
}
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);
}
}
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);
}
}
// 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);
}
}
// 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);
}
}
private static void UpdateToVersion2014_06_04(CruiseDatastore db)
{
var startVersion = db.DatabaseVersion;
try
{
db.BeginTransaction();
db.AddField("Sale", new ColumnInfo("LogGradingEnabled", "BOOLEAN")
{
Default = "0"
});
SetDatabaseVersion(db, "2014.06.04");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(startVersion, "2014.06.04", e);
}
}
private static void FixVersion_2_5_0(CruiseDatastore db)
{
var startVersion = db.DatabaseVersion;
var targetVersion = "2.5.1.1";
db.BeginTransaction();
try
{
db.Execute("UPDATE Tree SET Tree_GUID = NULL WHERE typeof(Tree_GUID) = 'text' AND Tree_GUID NOT LIKE '________-____-____-____-____________';");
db.Execute("UPDATE Log SET Log_GUID = NULL WHERE typeof(Log_GUID) = 'text' AND Log_GUID NOT LIKE '________-____-____-____-____________';");
db.Execute("UPDATE Stem SET Stem_GUID = NULL WHERE typeof(Stem_GUID) = 'text' AND Stem_GUID NOT LIKE '________-____-____-____-____________';");
db.Execute("UPDATE Plot SET Plot_GUID = NULL WHERE typeof(Plot_GUID) = 'text' AND Plot_GUID NOT LIKE '________-____-____-____-____________';");
db.Execute("UPDATE Component SET GUID = NULL WHERE typeof(GUID) = 'text' AND GUID NOT LIKE '________-____-____-____-____________';");
SetDatabaseVersion(db, targetVersion);
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(startVersion, targetVersion, e);
}
}
public static void UpdateToVersion2015_08_03(CruiseDatastore db)
{
var startVersion = db.DatabaseVersion;
try
{
db.BeginTransaction();
db.AddField("Plot", new ColumnInfo("Plot_GUID", "TEXT"));
db.AddField("Tree", new ColumnInfo("Tree_GUID", "TEXT"));
db.AddField("Log", new ColumnInfo("Log_GUID", "TEXT"));
db.AddField("Stem", new ColumnInfo("Stem_GUID", "TEXT"));
db.AddField("TreeEstimate", new ColumnInfo("TreeEstimate_GUID", "TEXT"));
SetDatabaseVersion(db, "2015.08.03");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(startVersion, "2015.08.03", e);
}
}
private void UpdateTo_3_5_0(CruiseDatastore db)
{
var curVersion = db.DatabaseVersion;
var targetVersion = "3.5.0";
db.BeginTransaction();
try
{
// create Species_Product table
CreateTable(db, new Species_ProductTableDefinition());
// populate Species_Product table from Species table
db.Execute("INSERT INTO Species_Product (" +
"CruiseID, " +
"SpeciesCode, " +
"PrimaryProduct, " +
"ContractSpecies " +
") " +
"SELECT CruiseID, SpeciesCode, null AS PrimaryProduct, ContractSpecies " +
"FROM Species WHERE ContractSpecies IS NOT NULL;");
// add Trigger to keep Species and Species_Product in sync
db.Execute(SpeciesTableDefinition.CREATE_TRIGGER_Species_OnUpdate_ContractSpecies);
// rebuild TallyLedger_Tree_Totals view
db.Execute("DROP VIEW TallyLedger_Tree_Totals;");
db.Execute(TallyLedgerViewDefinition.CREATE_VIEW_TallyLedger_Tree_Totals);
SetDatabaseVersion(db, targetVersion);
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(curVersion, targetVersion, e);
}
}
// update 3.3.4 notes:
// added change tracking fields to StratumTempalte, StratumTemplateTreeFieldSetup, and StratumTemplateLogFileSetup
// added tombstone tables for StratumTempalte, StratumTemplateTreeFieldSetup, and StratumTemplateLogFileSetup
private void UpdateTo_3_3_4(CruiseDatastore db)
{
var curVersion = db.DatabaseVersion;
var targetVersion = "3.3.4";
db.BeginTransaction();
try
{
var stratumTemplateTableDef = new StratumTemplateTableDefinition();
var stratumTemplateTreeFieldSetupTableDef = new StratumTemplateTreeFieldSetupTableDefinition();
var stratumTemplateLogFieldSetupTableDef = new StratumTemplateLogFieldSetupTableDefinition();
db.Execute("DROP VIEW StratumDefault;");
db.Execute("DROP VIEW TreeFieldSetupDefault;");
db.Execute("DROP VIEW LogFieldSetupDefault;");
db.Execute(stratumTemplateTableDef.CreateTombstoneTable);
db.Execute(stratumTemplateTreeFieldSetupTableDef.CreateTombstoneTable);
db.Execute(stratumTemplateLogFieldSetupTableDef.CreateTombstoneTable);
RebuildTable(db, stratumTemplateTableDef);
RebuildTable(db, stratumTemplateTreeFieldSetupTableDef);
RebuildTable(db, stratumTemplateLogFieldSetupTableDef);
db.Execute(new StratumDefaultViewDefinition().CreateView);
db.Execute(new TreeFieldSetupDefaultViewDefinition().CreateView);
db.Execute(new LogFieldSetupDefaultViewDefinition().CreateView);
SetDatabaseVersion(db, targetVersion);
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(curVersion, targetVersion, e);
}
}
private static void UpdateToVersion2014_07_24(CruiseDatastore db)
{
var startVersion = db.DatabaseVersion;
try
{
db.BeginTransaction();
db.Execute(@"
DROP TRIGGER IF EXISTS OnDeleteTree;
CREATE TRIGGER OnDeleteTree AFTER DELETE ON Tree BEGIN
INSERT INTO MessageLog (Message, Date, Time) VALUES (('Tree (' || OLD.Tree_CN || ') Deleted CU_cn:' || OLD.CuttingUnit_CN || ' St_cn:' || OLD.Stratum_CN || ' Plt_CN:' || ifnull(OLD.Plot_CN,'-') || ' T#:' || OLD.TreeNumber), date('now'), time('now')); END;
DROP TRIGGER IF EXISTS OnDeletePlot;
CREATE TRIGGER OnDeletePlot AFTER DELETE ON Plot BEGIN
INSERT INTO MessageLog (Message, Date, Time) VALUES (('Plot (' || OLD.Plot_CN || ') Deleted CU_cn:' || OLD.CuttingUnit_CN || ' St_cn:' || OLD.Stratum_CN || ' Plt#:' || OLD.PlotNumber), date('now'), time('now')); END;
");
SetDatabaseVersion(db, "2014.07.24");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(startVersion, "2014.07.24", e);
}
}
private static void UpdateTo_2_5_1(CruiseDatastore db)
{
var startVersion = db.DatabaseVersion;
var targetVersion = "2.5.1";
db.BeginTransaction();
try
{
db.Execute("ALTER TABLE TreeCalculatedValues ADD COLUMN TipwoodVolume REAL Default 0.0;");
db.Execute("ALTER TABLE LCD ADD COLUMN SumTipwood DOUBLE Default 0.0;");
db.Execute("ALTER TABLE CuttingUnit ADD COLUMN Rx TEXT;");
db.Execute("ALTER TABLE Stratum ADD COLUMN SamplingFrequency INTEGER Default 0;");
db.Execute("ALTER TABLE Stratum ADD COLUMN VolumeFactor REAL Default 0.333;");
db.Execute("ALTER TABLE Plot ADD COLUMN ThreePRandomValue INTEGER Default 0;");
SetDatabaseVersion(db, targetVersion);
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(startVersion, targetVersion, e);
}
}
private static void UpdateToVersion2014_07_02(CruiseDatastore db)
{
var startVersion = db.DatabaseVersion;
try
{
db.BeginTransaction();
db.AddField("LogStock", new ColumnInfo("BoardUtil", "REAL")
{
Default = "0.0"
});
db.AddField("LogStock", new ColumnInfo("CubicUtil", "REAL")
{
Default = "0.0"
});
SetDatabaseVersion(db, "2014.07.02");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(startVersion, "2014.07.02", e);
}
}
private static void UpdateToVersion2014_07_07(CruiseDatastore db)
{
var startVersion = db.DatabaseVersion;
try
{
db.BeginTransaction();
db.AddField("SampleGroup", new ColumnInfo("MinKPI", "INTEGER")
{
Default = "0"
});
db.AddField("SampleGroup", new ColumnInfo("MaxKPI", "INTEGER")
{
Default = "0"
});
SetDatabaseVersion(db, "2014.07.07");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(startVersion, "2014.07.07", e);
}
}
// remove foreign keys from TallyLedger table used to keep SpeciesCode, LiveDead, StratumCode and SampleGroupCode
// in sync between Tree and TallyLedger tables and replace them with triggers
// this works better in situations where either SpeciesCode or LiveDead were initialy null
// also removing indexes used to support those foreign keys from tree table
private void UpdateTo_3_4_4(CruiseDatastore db)
{
var curVersion = db.DatabaseVersion;
var targetVersion = "3.4.4";
var fKeys = db.ExecuteScalar <string>("PRAGMA foreign_keys;");
db.Execute("PRAGMA foreign_keys=OFF;");
db.BeginTransaction();
try
{
db.Execute("DROP VIEW TallyLedger_Totals;");
db.Execute("DROP VIEW TallyLedger_Tree_Totals;");
db.Execute("DROP VIEW TallyLedger_Plot_Totals;");
RebuildTable(db, new TallyLedgerTableDefinition());
db.Execute(new TallyLedgerViewDefinition().CreateView);
db.Execute(TreeTableDefinition.CREATE_TRIGGER_TREE_Cascade_Species_Updates);
db.Execute(TreeTableDefinition.CREATE_TRIGGER_TREE_Cascade_LiveDead_Updates);
db.Execute(TreeTableDefinition.CREATE_TRIGGER_TREE_Cascade_SampleGroupCode_Updates);
db.Execute(TreeTableDefinition.CREATE_TRIGGER_TREE_Cascade_StratumCode_Updates);
db.Execute("DROP INDEX UIX_Tree_TreeID_SpeciesCode;");
db.Execute("DROP INDEX UIX_Tree_TreeID_LiveDead;");
SetDatabaseVersion(db, targetVersion);
db.CommitTransaction();
db.Execute($"PRAGMA foreign_keys={fKeys};");
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(curVersion, targetVersion, e);
}
}
public static void UpdateToVersion2015_04_28(CruiseDatastore db)
{
var startVersion = db.DatabaseVersion;
try
{
db.BeginTransaction();
db.Execute(@"CREATE TABLE Util_Tombstone (
RecordID INTEGER ,
RecordGUID TEXT,
TableName TEXT NOT NULL COLLATE NOCASE,
Data TEXT,
DeletedDate DATETIME NON NULL);");
// db.Execute(@"
// CREATE VIEW CountTree_View AS
//SELECT Stratum.Code as StratumCode,
//Stratum.Method as Method,
//SampleGroup.Code as SampleGroupCode,
//SampleGroup.PrimaryProduct as PrimaryProduct,
//CountTree.*
//FROM CountTree JOIN SampleGroup USING (SampleGroup_CN) JOIN Stratum USING (Stratum_CN);");
db.Execute(@"ALTER TABLE Sale ADD COLUMN RowVersion INTEGER DEFAULT 0;
ALTER TABLE CuttingUnit ADD COLUMN RowVersion INTEGER DEFAULT 0;
ALTER TABLE Stratum ADD COLUMN RowVersion INTEGER DEFAULT 0;
ALTER TABLE SampleGroup ADD COLUMN RowVersion INTEGER DEFAULT 0;
ALTER TABLE TreeDefaultValue ADD COLUMN RowVersion INTEGER DEFAULT 0;
ALTER TABLE Plot ADD COLUMN RowVersion INTEGER DEFAULT 0;
ALTER TABLE Tree ADD COLUMN RowVersion INTEGER DEFAULT 0;
ALTER TABLE Log ADD COLUMN RowVersion INTEGER DEFAULT 0;
ALTER TABLE Stem ADD COLUMN RowVersion INTEGER DEFAULT 0;
ALTER TABLE CountTree ADD COLUMN RowVersion INTEGER DEFAULT 0;");
db.Execute(@"ALTER TABLE Stem ADD COLUMN CreatedBy TEXT;
ALTER TABLE Stem ADD COLUMN CreatedDate DATETIME;
ALTER TABLE Stem ADD COLUMN ModifiedBy TEXT;
ALTER TABLE Stem ADD COLUMN ModifiedDate DATETIME;
ALTER TABLE TreeEstimate ADD COLUMN CreatedBy TEXT;
ALTER TABLE TreeEstimate ADD COLUMN CreatedDate DATETIME;
ALTER TABLE TreeEstimate ADD COLUMN ModifiedBy TEXT;
ALTER TABLE TreeEstimate ADD COLUMN ModifiedDate DATETIME;
ALTER TABLE TreeDefaultValue ADD COLUMN CreatedBy TEXT;
ALTER TABLE TreeDefaultValue ADD COLUMN CreatedDate DATETIME;
ALTER TABLE TreeDefaultValue ADD COLUMN ModifiedBy TEXT;
ALTER TABLE TreeDefaultValue ADD COLUMN ModifiedDate DATETIME;");
db.Execute("ALTER TABLE SampleGroup ADD COLUMN SmallFPS REAL DEFAULT 0.0;");
db.Execute("ALTER TABLE Tree ADD COLUMN UpperStemDiameter REAL DEFAULT 0.0;");
db.Execute("UPDATE Tree SET UpperStemDiameter = UpperstemDOB;");
db.Execute("UPDATE TreeFieldSetup SET Field = 'UpperStemDiameter' WHERE Field = 'UpperStemDiameter';");
db.Execute("UPDATE TreeFieldSetupDefault SET Field = 'UpperStemDiameter' WHERE Field = 'UpperStemDiameter';");
db.Execute("ALTER TABLE Stratum ADD COLUMN YieldComponent TEXT DEFAULT 'CL';");
db.Execute("UPDATE TreeDefaultValue SET Chargeable = null;");
db.Execute("ALTER TABLE CuttingUnitStratum ADD COLUMN StratumArea REAL;");
db.Execute(@"CREATE VIEW StratumAcres_View AS
SELECT CuttingUnit.Code as CuttingUnitCode,
Stratum.Code as StratumCode,
ifnull(Area, CuttingUnit.Area) as Area,
CuttingUnitStratum.*
FROM CuttingUnitStratum
JOIN CuttingUnit USING (CuttingUnit_CN)
JOIN Stratum USING (Stratum_CN);");
db.Execute("PRAGMA user_version = 1");
SetDatabaseVersion(db, "2015.04.28");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(startVersion, "2015.04.28", e);
}
}
// update 3.3.0 notes
// redesign Stratum Template tables. Remove existing StratumDefault, LogFieldSetupDefault, TreeFielSetupDefault tables
// Replace with StratumTemplate and StratumTemplateTreeFieldSetup tables
// Updated schema is not backwards compatible with previous schema,
// but no application code relies on previous tables. Previous tables were only written to when
// file was created.
private void UpdateTo_3_3_0(CruiseDatastore ds)
{
var curVersion = ds.DatabaseVersion;
var targetVersion = "3.3.0";
ds.BeginTransaction();
try
{
var cruiseIDs = ds.QueryScalar <string>("SELECT CruiseID FROM Cruise;");
if (cruiseIDs.Count() == 1)
{
var cruiseID = cruiseIDs.Single();
ds.Execute(new StratumTemplateTableDefinition().CreateTable);
ds.Execute(
$@"INSERT INTO StratumTemplate (
StratumTemplateName,
CruiseID,
StratumCode,
Method,
BasalAreaFactor,
FixedPlotSize,
KZ3PPNT,
SamplingFrequency,
Hotkey,
FBSCode,
YieldComponent,
FixCNTField
) SELECT
(CASE WHEN sd.Method NOT NULL THEN sd.Method || ' ' ELSE '' END) || (CASE WHEN sd.StratumCode NOT NULL THEN sd.StratumCode || ' ' ELSE '' END) || ifnull(Description, '') AS StratumTemplateName,
'{cruiseID}' AS CruiseID,
StratumCode,
Method,
BasalAreaFactor,
FixedPlotSize,
KZ3PPNT,
SamplingFrequency,
Hotkey,
FBSCode,
YieldComponent,
FixCNTField
FROM StratumDefault AS sd;");
var sttfs = new StratumTemplateTreeFieldSetupTableDefinition();
ds.Execute(sttfs.CreateTable);
ds.Execute(sttfs.CreateIndexes);
ds.Execute(
$@"INSERT INTO StratumTemplateTreeFieldSetup (
StratumTemplateName,
CruiseID,
Field,
FieldOrder,
IsHidden,
IsLocked,
DefaultValueInt,
DefaultValueReal,
DefaultValueBool,
DefaultValueText
) SELECT
(CASE WHEN sd.Method NOT NULL THEN sd.Method || ' ' ELSE '' END) || (CASE WHEN sd.StratumCode NOT NULL THEN sd.StratumCode || ' ' ELSE '' END) || ifnull(Description, '') AS StratumTemplateName,
'{cruiseID}' AS CruiseID,
Field,
FieldOrder,
IsHidden,
IsLocked,
DefaultValueInt,
DefaultValueReal,
DefaultValueBool,
DefaultValueText
FROM TreeFieldSetupDefault AS tfsd
JOIN StratumDefault AS sd USING (StratumDefaultID);");
var stlfs = new StratumTemplateLogFieldSetupTableDefinition();
ds.Execute(stlfs.CreateTable);
ds.Execute(stlfs.CreateIndexes);
ds.Execute(
$@"INSERT INTO StratumTemplateLogFieldSetup (
StratumTemplateName,
CruiseID,
Field,
FieldOrder
) SELECT
(CASE WHEN sd.Method NOT NULL THEN sd.Method || ' ' ELSE '' END) || (CASE WHEN sd.StratumCode NOT NULL THEN sd.StratumCode || ' ' ELSE '' END) || ifnull(Description, '') AS StratumTemplateName,
'{cruiseID}' AS CruiseID,
Field,
FieldOrder
FROM LogFieldSetupDefault AS lfsd
JOIN StratumDefault AS sd USING (StratumDefaultID);");
ds.Execute("DROP TABLE StratumDefault;");
ds.Execute("DROP TABLE TreeFieldSetupDefault;");
ds.Execute("DROP TABLE LogFieldSetupDefault;");
ds.Execute(new StratumDefaultViewDefinition().CreateView);
ds.Execute(new TreeFieldSetupDefaultViewDefinition().CreateView);
ds.Execute(new LogFieldSetupDefaultViewDefinition().CreateView);
}
SetDatabaseVersion(ds, targetVersion);
ds.CommitTransaction();
}
catch (Exception e)
{
ds.RollbackTransaction();
throw new SchemaUpdateException(curVersion, targetVersion, e);
}
}
