// 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; }