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