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