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