// 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);
}
}
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);
}
}
// 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);
}
}
// 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);
}
}
//remove unique constraint from tree.treeNumber
//when users were trying to renumber trees in a
//transaction it would fail. Even though at the end
//of the transaction the constraint would not be in
//violation. Because there is no way to fix this bug
//in software with previous versions, aswell, the unique
// constraint was only working properly when Plot_CN was
// not null I'm deciding to remove it alltogether. RIP
private static void UpdateTo_2_1_2(CruiseDatastore db)
{
var startVersion = db.DatabaseVersion;
try
{
var fk = db.ExecuteScalar("Pragma foreign_keys;");
var treeTriggerDDL = GetTriggerDDL(db, "Tree");
db.Execute(
"PRAGMA foreign_keys = off;\r\n" +
"BEGIN;\r\n" +
"CREATE TABLE new_Tree (\r\n" +
"Tree_CN INTEGER PRIMARY KEY AUTOINCREMENT,\r\n" +
"Tree_GUID TEXT," +
"TreeDefaultValue_CN INTEGER REFERENCES TreeDefaultValue,\r\n" +
"Stratum_CN INTEGER REFERENCES Stratum NOT NULL,\r\n" +
"SampleGroup_CN INTEGER REFERENCES SampleGroup,\r\n" +
"CuttingUnit_CN INTEGER REFERENCES CuttingUnit NOT NULL,\r\n" +
"Plot_CN INTEGER REFERENCES Plot,\r\n" +
"TreeNumber INTEGER NOT NULL,\r\n" +
"Species TEXT,\r\n" +
"CountOrMeasure TEXT,\r\n" +
"TreeCount REAL Default 0.0,\r\n" +
"KPI REAL Default 0.0,\r\n" +
"STM TEXT Default 'N',\r\n" +
"SeenDefectPrimary REAL Default 0.0,\r\n" +
"SeenDefectSecondary REAL Default 0.0,\r\n" +
"RecoverablePrimary REAL Default 0.0,\r\n" +
"HiddenPrimary REAL Default 0.0,\r\n" +
"Initials TEXT,\r\n" +
"LiveDead TEXT,\r\n" +
"Grade TEXT,\r\n" +
"HeightToFirstLiveLimb REAL Default 0.0,\r\n" +
"PoleLength REAL Default 0.0,\r\n" +
"ClearFace TEXT,\r\n" +
"CrownRatio REAL Default 0.0,\r\n" +
"DBH REAL Default 0.0,\r\n" +
"DRC REAL Default 0.0,\r\n" +
"TotalHeight REAL Default 0.0,\r\n" +
"MerchHeightPrimary REAL Default 0.0,\r\n" +
"MerchHeightSecondary REAL Default 0.0,\r\n" +
"FormClass REAL Default 0.0,\r\n" +
"UpperStemDOB REAL Default 0.0,\r\n" +
"UpperStemDiameter REAL Default 0.0,\r\n" +
"UpperStemHeight REAL Default 0.0,\r\n" +
"DBHDoubleBarkThickness REAL Default 0.0,\r\n" +
"TopDIBPrimary REAL Default 0.0,\r\n" +
"TopDIBSecondary REAL Default 0.0,\r\n" +
"DefectCode TEXT,\r\n" +
"DiameterAtDefect REAL Default 0.0,\r\n" +
"VoidPercent REAL Default 0.0,\r\n" +
"Slope REAL Default 0.0,\r\n" +
"Aspect REAL Default 0.0,\r\n" +
"Remarks TEXT,\r\n" +
"XCoordinate DOUBLE Default 0.0,\r\n" +
"YCoordinate DOUBLE Default 0.0,\r\n" +
"ZCoordinate DOUBLE Default 0.0,\r\n" +
"MetaData TEXT,\r\n" +
"IsFallBuckScale INTEGER Default 0,\r\n" +
"ExpansionFactor REAL Default 0.0,\r\n" +
"TreeFactor REAL Default 0.0,\r\n" +
"PointFactor REAL Default 0.0,\r\n" +
"CreatedBy TEXT DEFAULT 'none',\r\n" +
"CreatedDate DateTime DEFAULT(datetime('now')),\r\n" +
"ModifiedBy TEXT,\r\n" +
"ModifiedDate DateTime,\r\n" +
"RowVersion INTEGER DEFAULT 0);" +
"INSERT INTO new_Tree ( " +
"Tree_CN,\r\n" +
"Tree_GUID," +
"TreeDefaultValue_CN,\r\n" +
"Stratum_CN,\r\n" +
"SampleGroup_CN,\r\n" +
"CuttingUnit_CN,\r\n" +
"Plot_CN,\r\n" +
"TreeNumber,\r\n" +
"Species,\r\n" +
"CountOrMeasure,\r\n" +
"TreeCount,\r\n" +
"KPI,\r\n" +
"STM,\r\n" +
"SeenDefectPrimary,\r\n" +
"SeenDefectSecondary,\r\n" +
"RecoverablePrimary,\r\n" +
"HiddenPrimary,\r\n" +
"Initials,\r\n" +
"LiveDead,\r\n" +
"Grade,\r\n" +
"HeightToFirstLiveLimb,\r\n" +
"PoleLength,\r\n" +
"ClearFace,\r\n" +
"CrownRatio,\r\n" +
"DBH,\r\n" +
"DRC,\r\n" +
"TotalHeight,\r\n" +
"MerchHeightPrimary,\r\n" +
"MerchHeightSecondary,\r\n" +
"FormClass,\r\n" +
"UpperStemDOB,\r\n" +
"UpperStemDiameter,\r\n" +
"UpperStemHeight,\r\n" +
"DBHDoubleBarkThickness,\r\n" +
"TopDIBPrimary,\r\n" +
"TopDIBSecondary,\r\n" +
"DefectCode,\r\n" +
"DiameterAtDefect,\r\n" +
"VoidPercent,\r\n" +
"Slope,\r\n" +
"Aspect,\r\n" +
"Remarks,\r\n" +
"XCoordinate,\r\n" +
"YCoordinate,\r\n" +
"ZCoordinate,\r\n" +
"MetaData,\r\n" +
"IsFallBuckScale,\r\n" +
"ExpansionFactor,\r\n" +
"TreeFactor,\r\n" +
"PointFactor,\r\n" +
"CreatedBy,\r\n" +
"CreatedDate,\r\n" +
"ModifiedBy,\r\n" +
"ModifiedDate,\r\n" +
"RowVersion " +
") " +
" SELECT " +
"Tree_CN,\r\n" +
"Tree_GUID," +
"TreeDefaultValue_CN,\r\n" +
"Stratum_CN,\r\n" +
"SampleGroup_CN,\r\n" +
"CuttingUnit_CN,\r\n" +
"Plot_CN,\r\n" +
"TreeNumber,\r\n" +
"Species,\r\n" +
"CountOrMeasure,\r\n" +
"TreeCount,\r\n" +
"KPI,\r\n" +
"STM,\r\n" +
"SeenDefectPrimary,\r\n" +
"SeenDefectSecondary,\r\n" +
"RecoverablePrimary,\r\n" +
"HiddenPrimary,\r\n" +
"Initials,\r\n" +
"LiveDead,\r\n" +
"Grade,\r\n" +
"HeightToFirstLiveLimb,\r\n" +
"PoleLength,\r\n" +
"ClearFace,\r\n" +
"CrownRatio,\r\n" +
"DBH,\r\n" +
"DRC,\r\n" +
"TotalHeight,\r\n" +
"MerchHeightPrimary,\r\n" +
"MerchHeightSecondary,\r\n" +
"FormClass,\r\n" +
"UpperStemDOB,\r\n" +
"UpperStemDiameter,\r\n" +
"UpperStemHeight,\r\n" +
"DBHDoubleBarkThickness,\r\n" +
"TopDIBPrimary,\r\n" +
"TopDIBSecondary,\r\n" +
"DefectCode,\r\n" +
"DiameterAtDefect,\r\n" +
"VoidPercent,\r\n" +
"Slope,\r\n" +
"Aspect,\r\n" +
"Remarks,\r\n" +
"XCoordinate,\r\n" +
"YCoordinate,\r\n" +
"ZCoordinate,\r\n" +
"MetaData,\r\n" +
"IsFallBuckScale,\r\n" +
"ExpansionFactor,\r\n" +
"TreeFactor,\r\n" +
"PointFactor,\r\n" +
"CreatedBy,\r\n" +
"CreatedDate,\r\n" +
"ModifiedBy,\r\n" +
"ModifiedDate,\r\n" +
"RowVersion " +
"FROM Tree;\r\n" +
"DROP Table Tree;\r\n" +
"ALTER Table new_Tree RENAME TO Tree;\r\n" +
"COMMIT;\r\n" +
"PRAGMA primary_keys = on; " +
treeTriggerDDL);
SetDatabaseVersion(db, "2.1.2");
}
catch (Exception e)
{
throw new SchemaUpdateException(startVersion, "2.1.2", e);
}
}
private static bool HasSampleGroupUOMErrors(this CruiseDatastore dal)
{
return((dal.ExecuteScalar <long>("Select Count(DISTINCT UOM) FROM SampleGroup WHERE UOM != '04';")) > 1L);
//return this.GetRowCount("SampleGroup", "WHERE UOM != '04' GROUP BY UOM") > 1;
}
