public static void SetDatabaseVersion(CruiseDatastore db, string newVersion)
        {
            string command = String.Format("UPDATE Globals SET Value = '{0}' WHERE Block = 'Database' AND Key = 'Version';", newVersion);

            db.Execute(command);
            db.LogMessage($"Updated structure version to {newVersion}");
        }
Пример #2
0
        private static void UpdateTo_2_1_1(CruiseDatastore db)
        {
            var startVersion = db.DatabaseVersion;

            db.BeginTransaction();
            try
            {
                db.Execute(@"CREATE TABLE IF NOT EXISTS FixCNTTallyClass (
				FixCNTTallyClass_CN INTEGER PRIMARY KEY AUTOINCREMENT,
				Stratum_CN INTEGER REFERENCES Stratum NOT NULL,
				FieldName INTEGER Default 0);"                );

                db.Execute(@"CREATE TABLE IF NOT EXISTS FixCNTTallyPopulation (
				FixCNTTallyPopulation_CN INTEGER PRIMARY KEY AUTOINCREMENT,
				FixCNTTallyClass_CN INTEGER REFERENCES FixCNTTallyClass NOT NULL,
				SampleGroup_CN INTEGER REFERENCES SampleGroup NOT NULL,
				TreeDefaultValue_CN INTEGER REFERENCES TreeDefaultValue NOT NULL,
				IntervalSize INTEGER Default 0,
				Min INTEGER Default 0,
				Max INTEGER Default 0);"                );

                SetDatabaseVersion(db, "2.1.1");
                db.CommitTransaction();
            }
            catch (Exception e)
            {
                db.RollbackTransaction();
                throw new SchemaUpdateException(startVersion, "2.1.1", e);
            }
        }
        public static void LogMessage(this DbConnection connection, string message, string level = "I", string program = null, DbTransaction transaction = null)
        {
            if (connection is null)
            {
                throw new ArgumentNullException(nameof(connection));
            }
            if (message is null)
            {
                throw new ArgumentNullException(nameof(message));
            }

            if (program == null)
            {
                program = CruiseDatastore.GetCallingProgram();
            }

            Logger.Log(message, "LogMessage", LogLevel.Info);

            connection.ExecuteNonQuery("INSERT INTO MessageLog (Program, Message, Level, Date, Time) " +
                                       "VALUES " +
                                       "(@p1, @p2, @p3, @p4, @p5)",
                                       new object[] {
                program,
                message,
                level,
                DateTime.Now.ToString("yyyy/MM/dd"),
                DateTime.Now.ToString("HH:mm")
            }
                                       , transaction
                                       );
        }
        private static void UpdateToVersion2014_07_17(CruiseDatastore db)
        {
            var startVersion = db.DatabaseVersion;

            try
            {
                db.BeginTransaction();

                db.AddField("VolumeEquation", new ColumnInfo("MerchModFlag", "INTEGER")
                {
                    Default = "0"
                });

                db.AddField("SampleGroupStats", new ColumnInfo("ReconPlots", "INTEGER")
                {
                    Default = "0"
                });
                db.AddField("SampleGroupStats", new ColumnInfo("ReconTrees", "INTEGER")
                {
                    Default = "0"
                });

                SetDatabaseVersion(db, "2014.07.17");
                db.CommitTransaction();
            }
            catch (Exception e)
            {
                db.RollbackTransaction();
                throw new SchemaUpdateException(startVersion, "2014.07.17", e);
            }
        }
        private static void UpdateToVersion2014_10_01(CruiseDatastore db)
        {
            var startVersion = db.DatabaseVersion;

            try
            {
                db.BeginTransaction();
                db.Execute("DROP TABLE Regression;");
                db.Execute(@"CREATE TABLE Regression (
				Regression_CN INTEGER PRIMARY KEY AUTOINCREMENT,
				rVolume TEXT,
				rVolType TEXT,
				rSpeices TEXT,
				rProduct TEXT,
				rLiveDead TEXT,
				CoefficientA REAL Default 0.0,
				CoefficientB REAL Default 0.0,
				CoefficientC REAL Default 0.0,
				TotalTrees INTEGER Default 0,
				MeanSE REAL Default 0.0,
				Rsquared REAL Default 0.0,
				RegressModel TEXT,
				rMinDbh REAL Default 0.0,
				rMaxDbh REAL Default 0.0);"                );
                SetDatabaseVersion(db, "2014.10.01");
                db.CommitTransaction();
            }
            catch (Exception e)
            {
                db.RollbackTransaction();
                throw new SchemaUpdateException(startVersion, "2014.10.01", e);
            }
        }
Пример #6
0
        // UpdateTo_3_4_1 forgot to add CountOrMeasure, TreeCount, and AverageHeight fields to
        // the Plot_Stratum table. This update checks to see if they need to be added and adds them
        // if missing
        private void UpdateTo_3_4_2(CruiseDatastore db)
        {
            var curVersion    = db.DatabaseVersion;
            var targetVersion = "3.4.2";

            if (db.CheckFieldExists("Plot_Stratum_Tombstone", "CountOrMeasure") is false)
            {
                db.BeginTransaction();
                try
                {
                    db.Execute("ALTER TABLE Plot_Stratum_Tombstone ADD COLUMN CountOrMeasure TEXT COLLATE NOCASE;");
                    db.Execute("ALTER TABLE Plot_Stratum_Tombstone ADD COLUMN TreeCount INTEGER Default 0;");
                    db.Execute("ALTER TABLE Plot_Stratum_Tombstone ADD COLUMN AverageHeight REAL Default 0.0;");

                    SetDatabaseVersion(db, targetVersion);
                    db.CommitTransaction();
                }
                catch (Exception e)
                {
                    db.RollbackTransaction();
                    throw new SchemaUpdateException(curVersion, targetVersion, e);
                }
            }
            else
            {
                SetDatabaseVersion(db, targetVersion);
            }
        }
Пример #7
0
        //patch for some a version that got out in the wild with bad triggers
        private static void UpdateToVersion2015_09_01(CruiseDatastore db)
        {
            var startVersion = db.DatabaseVersion;

            db.BeginTransaction();
            try
            {
                //because there are a lot of changes with triggers
                //lets just recreate all triggers
                foreach (string trigName in ListTriggers(db))
                {
                    db.Execute("DROP TRIGGER " + trigName + ";");
                }

                //db.Execute(Schema.Schema.CREATE_TRIGGERS);

                SetDatabaseVersion(db, "2015.09.01");
                db.CommitTransaction();
            }
            catch (Exception e)
            {
                db.RollbackTransaction();
                throw new SchemaUpdateException(startVersion, "2015.09.01", e);
            }
        }
        public static void RecreateView(CruiseDatastore datastore, IViewDefinition viewDef)
        {
            var viewName = viewDef.ViewName;

            datastore.Execute($"DROP VIEW {viewName};");
            datastore.Execute(viewDef.CreateView);
        }
Пример #9
0
        private static void UpdateTo_2_7_1(CruiseDatastore db)
        {
            var version       = db.DatabaseVersion;
            var targetVersion = "2.7.1";

            try
            {
                db.Execute(
                    @"DROP TABLE TreeEstimate;
CREATE TABLE TreeEstimate (
			TreeEstimate_CN INTEGER PRIMARY KEY AUTOINCREMENT,
			CountTree_CN INTEGER,
			TreeEstimate_GUID TEXT,
			KPI REAL NOT NULL,
			CreatedBy TEXT DEFAULT 'none',
			CreatedDate DateTime DEFAULT (datetime(current_timestamp, 'localtime')) ,
			ModifiedBy TEXT ,
			ModifiedDate DateTime );"            );

                db.ReleaseConnection();


                SetDatabaseVersion(db, targetVersion);
            }
            catch (Exception e)
            {
                throw new SchemaUpdateException(version, targetVersion, e);
            }
        }
Пример #10
0
        // update 3.3.3 notes:
        // added column TemplateFile to Cruise table
        // added lookup table LK_TallyEntryType
        // remove check constraint on EntryType and add FKey on EntryType
        private void UpdateTo_3_3_3(CruiseDatastore db)
        {
            var curVersion    = db.DatabaseVersion;
            var targetVersion = "3.3.3";

            db.BeginTransaction();
            try
            {
                db.Execute("ALTER TABLE main.Cruise ADD COLUMN TemplateFile TEXT;");

                // create table LK_TallyEntryType
                var tallyEntryTypeTableDef = new LK_TallyEntryType();
                CreateTable(db, tallyEntryTypeTableDef);

                // remove check constraint on EntryType and add FKey on EntryType
                var tallyLedgerTableDef = new TallyLedgerTableDefinition();
                UpdateTableDDL(db, tallyLedgerTableDef);

                SetDatabaseVersion(db, targetVersion);
                db.CommitTransaction();
            }
            catch (Exception e)
            {
                db.RollbackTransaction();
                throw new SchemaUpdateException(curVersion, targetVersion, e);
            }
        }
Пример #11
0
        // update 3.2.2 notes: Added table LK_District and updated initialization for LK_Forests
        public static void UpdateTo_3_2_2(CruiseDatastore ds)
        {
            // create an in-memory database
            // to migrate into
            using (var newDatastore = new CruiseDatastore_V3())
            {
                var excludeTables = new[]
                {
                    "LK_CruiseMethod",
                    "LK_District",
                    "LK_FIA",
                    "LK_Forest",
                    "LK_LoggingMethod",
                    "LK_Product",
                    "LK_Purpose",
                    "LK_Region",
                    "LK_UOM",
                    "LogField",
                    "TreeField",
                };
                // migrate contents of old db into new in-memory database
                Migrate(ds, newDatastore, excludeTables);

                // use back up rutine to replace old database with
                // migrated contents
                newDatastore.BackupDatabase(ds);
            }
        }
Пример #12
0
        // update 3.3.2 notes:
        // added Biomass and ValueEquation tables
        // changed TallyPopulation view so that 3p methods are always treaded as tally by subpop
        private void UpdateTo_3_3_2(CruiseDatastore db)
        {
            var curVersion    = db.DatabaseVersion;
            var targetVersion = "3.3.2";

            db.BeginTransaction();
            try
            {
                var biomassTableDef = new BiomassEquationTableDefinition();
                var valueEqTableDef = new ValueEquationTableDefinition();

                CreateTable(db, biomassTableDef);
                CreateTable(db, valueEqTableDef);

                // allways treat 3p methods as tally by subpop
                var tallyPopViewDef = new TallyPopulationViewDefinition_3_3_2();
                RecreateView(db, tallyPopViewDef);

                SetDatabaseVersion(db, targetVersion);
                db.CommitTransaction();
            }
            catch (Exception e)
            {
                db.RollbackTransaction();
                throw new SchemaUpdateException(curVersion, targetVersion, e);
            }
        }
Пример #13
0
        private static void UpdateToVersion2015_08_19(CruiseDatastore db)
        {
            var startVersion = db.DatabaseVersion;

            var  tavCols            = db.GetTableInfo("TreeAuditValue");
            bool hasErrorMessageCol = false;

            foreach (ColumnInfo col in tavCols)
            {
                if (col.Name == "ErrorMessage")
                {
                    hasErrorMessageCol = true; break;
                }
            }

            try
            {
                db.BeginTransaction();
                if (!hasErrorMessageCol)
                {
                    db.AddField("TreeAuditValue", new ColumnInfo("ErrorMessage", "TEXT"));
                }

                SetDatabaseVersion(db, "2015.08.19");
                db.CommitTransaction();
            }
            catch (Exception e)
            {
                db.RollbackTransaction();
                throw new SchemaUpdateException(startVersion, "2015.08.19", e);
            }
        }
        public static string[] ListFieldsIntersect(CruiseDatastore db, string table1, string table2)
        {
            var sourceFields = db.QueryScalar2 <string>($@"SELECT '""' || Name || '""' FROM pragma_table_info('{table1}');");

            var destFields = db.QueryScalar2 <string>($@"SELECT '""' || Name || '""' FROM pragma_table_info('{table2}');");

            var both = sourceFields.Intersect(destFields).ToArray();

            return(both);
        }
Пример #15
0
        public void Update(CruiseDatastore datastore)
        {
            //PatchSureToMeasure(db);

            Update_Impl(datastore);

            // the following method calls are not nessicary for updating the
            // database. They just need to be ran to clean up potential errors.
            CleanupErrorLog(datastore);
            FixTreeAuditValueFKeyErrors(datastore);
        }
        // IMPORTANT: additional steps must be done before and after calling this method
        // before you must:
        //      1) set PRAGMA foreign_keys=off
        //      2) begin a transaction
        // after you must:
        //      1) commit  the transaction
        //      2) set PRAGMA foreign_keys back to what it was beofore

        // see https://www.sqlite.org/lang_altertable.html#otheralter for more details on the procedure for rebuilding a table.

        public static void RebuildTable(CruiseDatastore db, ITableDefinition tableDef, IEnumerable <KeyValuePair <string, string> > customFieldMaps = null)
        {
            var tableName = tableDef.TableName;

            var tempTableName  = "new_" + tableName;
            var createNewTable = tableDef.GetCreateTable(tempTableName);

            db.Execute(createNewTable);

            var fieldIntersectArray = ListFieldsIntersect(db, tableName, tempTableName);

            var fieldListFrom = new List <string>(fieldIntersectArray);
            var fieldListTo   = new List <string>(fieldIntersectArray);

            if (customFieldMaps != null)
            {
                foreach (var map in customFieldMaps)
                {
                    var i = fieldListTo.FindIndex(x => string.Compare(x, map.Key, true) is 0);
                    if (i > 0)
                    {
                        fieldListTo[i] = map.Value;
                    }
                    else
                    {
                        fieldListTo.Add(map.Key);
                        fieldListFrom.Add(map.Value);
                    }
                }
            }

            db.Execute($"INSERT INTO main.{tempTableName} ( {fieldListTo.Aggregate((a, b) => a + ", " + b)} ) SELECT {fieldListFrom.Aggregate((a, b) => a + ", " + b)} FROM main.{tableName};");

            db.Execute($"DROP TABLE main.{tableName};");
            db.Execute($"ALTER TABLE {tempTableName} RENAME TO {tableName}");

            var createIndexes = tableDef.CreateIndexes;

            if (createIndexes != null)
            {
                db.Execute(createIndexes);
            }

            var triggers = tableDef.CreateTriggers;

            if (triggers != null)
            {
                foreach (var trigger in triggers)
                {
                    db.Execute(trigger);
                }
            }
        }
        public static void Migrate(CruiseDatastore sourceDS, CruiseDatastore destinationDS, IEnumerable <string> excluding = null, bool excludeLookupTables = false)
        {
            var destConn   = destinationDS.OpenConnection();
            var sourceConn = sourceDS.OpenConnection();

            try
            {
                Migrate(sourceConn, destConn, excluding);
            }
            finally
            {
                destinationDS.ReleaseConnection();
                sourceDS.ReleaseConnection();
            }
        }
Пример #18
0
        public static void UpdateTo_3_1_0(CruiseDatastore ds)
        {
            // create an in-memory database
            // to migrate into
            using (var newDatastore = new CruiseDatastore_V3())
            {
                var excludeTables = new[] { "SamplerState" };
                // migrate contents of old db into new in-memory database
                Migrate(ds, newDatastore, excludeTables);

                // use back up rutine to replace old database with
                // migrated contents
                newDatastore.BackupDatabase(ds);
            }
        }
Пример #19
0
        // 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);
            }
        }
Пример #21
0
        public void MigrateFromV3ToV2(string cruiseID, CruiseDatastore_V3 v3db, CruiseDatastore v2db, string createdBy = null)
        {
            var v3DbAlias = "v3";

            v2db.AttachDB(v3db, v3DbAlias);

            try
            {
                var connection = v2db.OpenConnection();
                MigrateFromV3ToV2(cruiseID, connection, createdBy, v3DbAlias, v2db.ExceptionProcessor, Migrators);
            }
            finally
            {
                v3db.DetachDB(v3DbAlias);
                v2db.ReleaseConnection();
            }
        }
Пример #22
0
        private static void UpdateTo_2_6_1(CruiseDatastore db)
        {
            var version       = db.DatabaseVersion;
            var targetVersion = "2.6.1";

            db.BeginTransaction();
            try
            {
                db.Execute(
                    @"CREATE TABLE SamplerState (
        SamplerState_CN INTEGER PRIMARY KEY AUTOINCREMENT, 
        SampleGroup_CN INTEGER NOT NULL,
        SampleSelectorType TEXT COLLATE NOCASE, 
        BlockState TEXT, 
        SystematicIndex INTEGER DEFAULT 0, 
        Counter INTEGER DEFAULT 0, 
        InsuranceIndex DEFAULT -1,
        InsuranceCounter DEFAULT -1,
        ModifiedDate DateTime,

        UNIQUE (SampleGroup_CN),

        FOREIGN KEY (SampleGroup_CN) REFERENCES SampleGroup (SampleGroup_CN) ON DELETE CASCADE ON UPDATE CASCADE
);");

                db.Execute(
                    @"CREATE TRIGGER SamplerState_OnUpdate 
    AFTER UPDATE OF 
        BlockState, 
        Counter, 
        InsuranceCounter 
    ON SamplerState 
    FOR EACH ROW 
    BEGIN 
        UPDATE SamplerState SET ModifiedDate = datetime('now', 'localtime') WHERE SamplerState_CN = old.SamplerState_CN;
    END;
");
                SetDatabaseVersion(db, targetVersion);
                db.CommitTransaction();
            }
            catch (Exception e)
            {
                db.RollbackTransaction();
                throw new SchemaUpdateException(version, targetVersion, e);
            }
        }
Пример #23
0
        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);
            }
        }
        private static void UpdateToVersion2014_09_02(CruiseDatastore db)
        {
            var startVersion = db.DatabaseVersion;

            try
            {
                db.BeginTransaction();
                db.AddField("SampleGroup", new ColumnInfo("TallyMethod", "TEXT"));
                SetDatabaseVersion(db, "2014.09.02");
                db.CommitTransaction();
            }
            catch (Exception e)
            {
                db.RollbackTransaction();

                throw new SchemaUpdateException(startVersion, "2014.09.02", e);
            }
        }
Пример #25
0
        // add validation on tree for DBH and DRC
        private void UpdateTo_3_4_3(CruiseDatastore db)
        {
            var curVersion    = db.DatabaseVersion;
            var targetVersion = "3.4.3";

            db.BeginTransaction();
            try
            {
                db.Execute("DROP VIEW TreeError;");
                db.Execute(TreeErrorViewDefinition.v3_4_3);
                SetDatabaseVersion(db, targetVersion);
                db.CommitTransaction();
            }
            catch (Exception e)
            {
                db.RollbackTransaction();
                throw new SchemaUpdateException(curVersion, targetVersion, e);
            }
        }
Пример #26
0
        private static void UpdateTo_2_7_3(CruiseDatastore db)
        {
            var startVersion  = db.DatabaseVersion;
            var targetVersion = "2.7.3";

            try
            {
                db.BeginTransaction();
                db.Execute("DROP VIEW IF EXISTS CountTree_View;");
                db.Execute("DROP VIEW IF EXISTS StratumAcres_View;");
                SetDatabaseVersion(db, targetVersion);
                db.CommitTransaction();
            }
            catch (Exception e)
            {
                db.RollbackTransaction();
                throw new SchemaUpdateException(startVersion, targetVersion, e);
            }
        }
Пример #27
0
        // update notes: changed view TreeAuditError
        public static void UpdateTo_3_2_3(CruiseDatastore datastore)
        {
            datastore.BeginTransaction();
            try
            {
                var viewDef = new TreeAuditErrorViewDefinition();
                RecreateView(datastore, viewDef);

                datastore.Execute("DELETE FROM TreeField WHERE Field = 'MetaData';");
                datastore.Execute("INSERT INTO TreeField (Field, DefaultHeading, DbType, IsTreeMeasurmentField) VALUES ('MetaData', 'Meta Data', 'TEXT', 1)");

                SetDatabaseVersion(datastore, "3.2.3");
                datastore.CommitTransaction();
            }
            catch
            {
                datastore.RollbackTransaction();
            }
        }
        private static void UpdateToVersion2014_08_20(CruiseDatastore db)
        {
            var startVersion = db.DatabaseVersion;

            try
            {
                db.BeginTransaction();
                db.AddField("VolumeEquation", new ColumnInfo("EvenOddSegment", "INTEGER")
                {
                    Default = "0"
                });
                SetDatabaseVersion(db, "2014.08.20");
                db.CommitTransaction();
            }
            catch (Exception e)
            {
                db.RollbackTransaction();
                throw new SchemaUpdateException(startVersion, "2014.08.20", e);
            }
        }
Пример #29
0
        // 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);
            }
        }
Пример #30
0
        private static void UpdateTo_2_2_0(CruiseDatastore db)
        {
            var startVersion = db.DatabaseVersion;

            try
            {
                db.BeginTransaction();
                db.Execute(
                    @"CREATE TABLE LogGradeAuditRule (
Species TEXT,
DefectMax REAL Default 0.0,
ValidGrades TEXT);");
                SetDatabaseVersion(db, "2.2.0");
                db.CommitTransaction();
            }
            catch (Exception e)
            {
                db.RollbackTransaction();
                throw new SchemaUpdateException(startVersion, "2.2.0", e);
            }
        }