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