public static void AssignGuids(DAL db)
{
db.BeginTransaction();
try
{
foreach (TreeKey tk in db.Query<TreeKey>("SELECT * FROM Tree " + SELECT_TREES + ";"))
{
tk.Tree_GUID = Guid.NewGuid();
db.Execute("UPDATE Tree SET Tree_GUID = ? WHERE Tree_CN = ?;", tk.Tree_GUID.ToString(), tk.Tree_CN);
//db.Execute("UPDATE Log SET Tree_GUID = ? WHERE Tree_CN = ?;", tk.Tree_GUID.ToString(), tk.Tree_CN);
//db.Execute("UPDATE Stem SET Tree_GUID = ? WHERE Tree_CN = ?;", tk.Tree_GUID.ToString(), tk.Tree_CN);
}
foreach (PlotKey pk in db.Query<PlotKey>("SELECT * FROM Plot " + SELECT_PLOTS + ";"))
{
pk.Plot_GUID = Guid.NewGuid();
db.Execute("UPDATE Plot SET Plot_GUID = ? WHERE Plot_CN = ?;", pk.Plot_GUID.ToString(), pk.Plot_CN);
//db.Execute("UPDATE Tree SET Plot_GUID = ? WHERE Plot_CN = ?;", pk.Plot_GUID.ToString(), pk.Plot_CN);
}
foreach (LogKey lk in db.Query<LogKey>("SELECT * FROM Log " + SELECT_LOGS + ";"))
{
lk.Log_GUID = Guid.NewGuid();
db.Execute("UPDATE Log SET Log_GUID = ? WHERE Log_CN = ?;", lk.Log_GUID.ToString(), lk.Log_CN);
}
foreach (TreeEstimateKey tek in db.Query<TreeEstimateKey>("SELECT * FROM TreeEstimate " + SELECT_TREEEST + ";"))
{
tek.TreeEstimate_GUID = Guid.NewGuid();
db.Execute("UPDATE TreeEstimate SET TreeEstimate_GUID = ? WHERE TreeEstimate_CN = ?", tek.TreeEstimate_GUID.ToString(), tek.TreeEstimate_CN);
}
db.CommitTransaction();
}
catch
{
db.RollbackTransaction();
throw;
}
}
private static void UpdateToVersion2014_08_20(DAL db)
{
try
{
db.BeginTransaction();
db.AddField(Schema.VOLUMEEQUATION._NAME, "EvenOddSegment INTEGER Default 0");
SetDatabaseVersion(db, "2014.08.20");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(db.DatabaseVersion, "2014.08.20", e);
}
}
private static void UpdateToVersion2014_10_01(DAL db)
{
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(db.DatabaseVersion, "2014.10.01", e);
}
}
private static void UpdateToVersion2014_07_02(DAL db)
{
try
{
db.BeginTransaction();
db.AddField("LogStock", "BoardUtil REAL Default 0.0");
db.AddField("LogStock", "CubicUtil REAL Default 0.0");
SetDatabaseVersion(db, "2014.07.02");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(db.DatabaseVersion, "2014.07.02", e);
}
}
private static void UpdateToVersion2014_07_17(DAL db)
{
try
{
db.BeginTransaction();
//rebuild Volume Equation table (remove MinLogLengthSecondary, MaxLogLengthSecondary; Add int MerchModFlag)
// RebuildTable(db, "VolumeEquation",
// @"
//CREATE TABLE VolumeEquation (
// Species TEXT NOT NULL,
// PrimaryProduct TEXT NOT NULL,
// VolumeEquationNumber TEXT NOT NULL,
// StumpHeight REAL Default 0.0,
// TopDIBPrimary REAL Default 0.0,
// TopDIBSecondary REAL Default 0.0,
// CalcTotal INTEGER Default 0,
// CalcBoard INTEGER Default 0,
// CalcCubic INTEGER Default 0,
// CalcCord INTEGER Default 0,
// CalcTopwood INTEGER Default 0,
// CalcBiomass INTEGER Default 0,
// Trim REAL Default 0.0,
// SegmentationLogic INTEGER Default 0,
// MinLogLengthPrimary REAL Default 0.0,
// MaxLogLengthPrimary REAL Default 0.0,
// MinMerchLength REAL Default 0.0,
// Model TEXT,
// CommonSpeciesName TEXT,
// MerchModFlag INTEGER Default 0,
// UNIQUE (Species, PrimaryProduct, VolumeEquationNumber));
//",
// @"
// Species,
// PrimaryProduct,
// VolumeEquationNumber,
// StumpHeight,
// TopDIBPrimary,
// TopDIBSecondary,
// CalcTotal,
// CalcBoard,
// CalcCubic,
// CalcCord,
// CalcTopWood,
// CalcBiomass,
// Trim,
// SegmentationLogic,
// MinLogLengthPrimary,
// MaxLogLengthPrimary,
// MinMerchLength,
// Model,
// CommonSpeciesName");
db.AddField("VolumeEquation", "MerchModFlag INTEGER Default 0");
//rebuild TreeAuditValue Table ( remove error message field)
// RebuildTable(db, "TreeAuditValue",
// @"
//CREATE TABLE TreeAuditValue (
// TreeAuditValue_CN INTEGER PRIMARY KEY AUTOINCREMENT,
// Field TEXT NOT NULL,
// Min REAL Default 0.0,
// Max REAL Default 0.0,
// ValueSet TEXT,
// Required BOOLEAN Default 0);
//",
// @"
//Field,
//Min,
//Max,
//ValueSet,
//Required");
//Add ReconPlots
db.AddField("SampleGroupStats", "ReconPlots INTEGER Default 0");
//Add ReconTrees
db.AddField("SampleGroupStats", "ReconTrees INTEGER Default 0");
SetDatabaseVersion(db, "2014.07.17");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(db.DatabaseVersion, "2014.07.17", e);
}
}
private static void UpdateToVersion2013_10_29(DAL db)
{
try
{
db.BeginTransaction();
db.AddField("Tree", "HiddenPrimary REAL Default 0.0");
SetDatabaseVersion(db, "2013.10.29");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(db.DatabaseVersion, "2013.10.29", e);
}
}
private static void UpdateToVersion2014_03_12(DAL db)
{
try
{
db.BeginTransaction();
db.AddField("SampleGroup", "SampleSelectorType TEXT");
db.AddField("SampleGroup", "SampleSelectorState TEXT");
RebuildTable(db, "CountTree",
@"CREATE TABLE CountTree (
CountTree_CN INTEGER PRIMARY KEY AUTOINCREMENT,
SampleGroup_CN INTEGER REFERENCES SampleGroup NOT NULL,
CuttingUnit_CN INTEGER REFERENCES CuttingUnit NOT NULL,
Tally_CN INTEGER REFERENCES Tally,
TreeDefaultValue_CN INTEGER REFERENCES TreeDefaultValue,
Component_CN INTEGER REFERENCES Component,
TreeCount INTEGER Default 0,
SumKPI INTEGER Default 0,
CreatedBy TEXT NOT NULL,
CreatedDate DATETIME,
ModifiedBy TEXT,
ModifiedDate DATETIME,
UNIQUE (SampleGroup_CN, CuttingUnit_CN, TreeDefaultValue_CN, Component_CN));",
"CountTree_CN, SampleGroup_CN, CuttingUnit_CN, Tally_CN, TreeDefaultValue_CN, Component_CN, TreeCount, SumKPI, CreatedBy, CreatedDate, ModifiedBy, ModifiedDate");
SetDatabaseVersion(db, "2014.03.12");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(db.DatabaseVersion, "2014.03.12", e);
}
}
public static void Update(DAL db)
{
//PatchSureToMeasure(db);
if (db.DatabaseVersion == "2013.05.28" || db.DatabaseVersion == "Unknown")
{
UpdateToVersion2013_05_30(db);
}
if (db.DatabaseVersion == "2013.05.30")
{
UpdateToVersion2013_06_12(db);
}
if (db.DatabaseVersion == "2013.06.12" || db.DatabaseVersion == "2013.06.13")
{
UpdateToVersion2013_06_17(db);
}
if (db.DatabaseVersion == "2013.06.17" || db.DatabaseVersion == "2013.06.18")
{
UpdateToVersion2013_06_19(db);
}
if (db.DatabaseVersion == "2013.06.19")
{
UpdateVersion2013_06_19(db);
UpdateToVersion2013_08_02(db);
}
if (db.DatabaseVersion == "2013.08.02")
{
UpdateToVersion2013_08_29(db);
}
if (db.DatabaseVersion == "2013.08.29")
{
UpdateToVersion2013_10_29(db);
}
if (db.DatabaseVersion == "2013.10.29")
{
UpdateToVersion2013_11_01(db);
}
if (db.DatabaseVersion == "2013.11.01")
{
UpdateToVersion2013_11_22(db);
}
if (db.DatabaseVersion == "2013.11.22")
{
UpdateToVersion2014_01_21(db);
}
if (db.DatabaseVersion == "2014.01.21")
{
UpdateToVersion2014_03_12(db);
}
if (db.DatabaseVersion == "2014.03.12")
{
UpdateToVersion2014_06_04(db);
}
if (db.DatabaseVersion == "2014.06.04")
{
UpdateToVersion2014_07_02(db);
}
if (db.DatabaseVersion == "2014.07.02")
{
UpdateToVersion2014_07_07(db);
}
if (db.DatabaseVersion == "2014.07.07")
{
UpdateToVersion2014_07_17(db);
}
if (db.DatabaseVersion == "2014.07.17")
{
UpdateToVersion2014_07_24(db);
}
if (db.DatabaseVersion == "2014.07.24")
{
UpdateToVersion2014_08_20(db);
}
if (db.DatabaseVersion == "2014.08.20")
{
UpdateToVersion2014_09_02(db);
}
if (db.DatabaseVersion == "2014.09.02")
{
UpdateToVersion2014_10_01(db);
}
if (db.DatabaseVersion == "2014.10.01" || db.DatabaseVersion == "2015.01.05")
{
UpdateToVersion2015_04_28(db);
}
if (db.DatabaseVersion == "2015.04.28")
{
UpdateToVersion2015_08_03(db);
}
if (db.DatabaseVersion == "2015.06.01")
{
SetDatabaseVersion(db, "2015.08.03");
}
if (db.DatabaseVersion == "2015.08.03")
{
UpdateToVersion2015_08_19(db);
}
if (db.DatabaseVersion == "2015.08.19")
{
UpdateToVersion2015_09_01(db);
}
if (db.DatabaseVersion == "2015.09.01"
|| db.DatabaseVersion == "2.0.0"
|| db.DatabaseVersion == "2.1.0")
{
UpdateTo_2_1_1(db);
}
if (db.DatabaseVersion == "2.1.1")
{
UpdateTo_2_1_2(db);
}
if (db.HasForeignKeyErrors(TREEDEFAULTVALUETREEAUDITVALUE._NAME))
{
try
{
db.BeginTransaction();
db.Execute("DELETE FROM TreeDefaultValueTreeAuditValue WHERE TreeDefaultValue_CN NOT IN (Select TreeDefaultValue_CN FROM TreeDefaultValue);");
db.Execute("DELETE FROM TreeDefaultValueTreeAuditValue WHERE TreeAuditValue_CN NOT IN (SELECT TreeAuditValue_CN FROM TreeAuditValue);");
db.CommitTransaction();
}
catch
{
db.RollbackTransaction();
}
}
foreach (ErrorLogDO el in db.From<ErrorLogDO>().Where("CN_Number != 0").Query())
{
InsureErrorLogEntry(db, el);
}
}
public static void UpdateToVersion2013_06_19(DAL db)
{
try
{
db.BeginTransaction();
string command = "DROP TABLE ErrorLog;";
db.Execute(command);
command = @"
CREATE TABLE ErrorLog (
TableName TEXT NOT NULL,
CN_Number INTEGER NOT NULL,
ColumnName TEXT NOT NULL,
Level TEXT NOT NULL,
Message TEXT,
Program TEXT,
Suppress BOOLEAN Default 0,
UNIQUE (TableName, CN_Number, ColumnName, Level));";
db.Execute(command);
////////////////////////////////////////////////////////////////////////Clean up
command = "DROP TABLE IF EXISTS TempGlobals;";
db.Execute(command);
////////////////////////////////////////////////////////////////////////
SetDatabaseVersion(db, "2013.06.19");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(db.DatabaseVersion, "2013.06.19", e);
}
}
private static void UpdateTo_2_1_2(DAL db)
{
db.BeginTransaction();
try
{
RebuildTable(db, "Tree",
@"CREATE TABLE Tree (
Tree_CN INTEGER PRIMARY KEY AUTOINCREMENT,
Tree_GUID TEXT,
TreeDefaultValue_CN INTEGER REFERENCES TreeDefaultValue,
Stratum_CN INTEGER REFERENCES Stratum NOT NULL,
SampleGroup_CN INTEGER REFERENCES SampleGroup,
CuttingUnit_CN INTEGER REFERENCES CuttingUnit NOT NULL,
Plot_CN INTEGER REFERENCES Plot,
TreeNumber INTEGER NOT NULL,
Species TEXT,
CountOrMeasure TEXT,
TreeCount REAL Default 0.0,
KPI REAL Default 0.0,
STM TEXT Default 'N',
SeenDefectPrimary REAL Default 0.0,
SeenDefectSecondary REAL Default 0.0,
RecoverablePrimary REAL Default 0.0,
HiddenPrimary REAL Default 0.0,
Initials TEXT,
LiveDead TEXT,
Grade TEXT,
HeightToFirstLiveLimb REAL Default 0.0,
PoleLength REAL Default 0.0,
ClearFace TEXT,
CrownRatio REAL Default 0.0,
DBH REAL Default 0.0,
DRC REAL Default 0.0,
TotalHeight REAL Default 0.0,
MerchHeightPrimary REAL Default 0.0,
MerchHeightSecondary REAL Default 0.0,
FormClass REAL Default 0.0,
UpperStemDOB REAL Default 0.0,
UpperStemDiameter REAL Default 0.0,
UpperStemHeight REAL Default 0.0,
DBHDoubleBarkThickness REAL Default 0.0,
TopDIBPrimary REAL Default 0.0,
TopDIBSecondary REAL Default 0.0,
DefectCode TEXT,
DiameterAtDefect REAL Default 0.0,
VoidPercent REAL Default 0.0,
Slope REAL Default 0.0,
Aspect REAL Default 0.0,
Remarks TEXT,
XCoordinate DOUBLE Default 0.0,
YCoordinate DOUBLE Default 0.0,
ZCoordinate DOUBLE Default 0.0,
MetaData TEXT,
IsFallBuckScale INTEGER Default 0,
ExpansionFactor REAL Default 0.0,
TreeFactor REAL Default 0.0,
PointFactor REAL Default 0.0,
CreatedBy TEXT DEFAULT 'none',
CreatedDate DateTime DEFAULT(datetime('now')),
ModifiedBy TEXT,
ModifiedDate DateTime,
RowVersion INTEGER DEFAULT 0);",
@"Tree_CN,
Tree_GUID,
TreeDefaultValue_CN,
Stratum_CN,
SampleGroup_CN,
CuttingUnit_CN,
Plot_CN,
TreeNumber,
Species,
CountOrMeasure,
TreeCount,
KPI,
STM,
SeenDefectPrimary,
SeenDefectSecondary,
RecoverablePrimary,
HiddenPrimary,
Initials,
LiveDead,
Grade,
HeightToFirstLiveLimb,
PoleLength,
ClearFace,
CrownRatio,
DBH,
DRC,
TotalHeight,
MerchHeightPrimary,
MerchHeightSecondary,
FormClass,
UpperStemDOB,
UpperStemDiameter,
UpperStemHeight,
DBHDoubleBarkThickness,
TopDIBPrimary,
TopDIBSecondary,
DefectCode,
DiameterAtDefect,
VoidPercent,
Slope,
Aspect,
Remarks,
XCoordinate,
YCoordinate,
ZCoordinate,
MetaData,
IsFallBuckScale,
ExpansionFactor,
TreeFactor,
PointFactor,
CreatedBy,
CreatedDate,
ModifiedBy,
ModifiedDate,
RowVersion");
SetDatabaseVersion(db, "2.1.2");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(db.DatabaseVersion, "2.1.2", e);
}
}
//fixes bug in database version 2013_06_19, doesn't alter schema
private static void UpdateVersion2013_06_19(DAL db)
{
try
{
db.BeginTransaction();
string command = "DELETE FROM ErrorLog WHERE Message LIKE 'Total Height, Merch Height Primary,%';";
db.Execute(command);
command = "DELETE FROM ErrorLog WHERE rowid IN (SELECT ErrorLog.rowid FROM ErrorLog JOIN Tree WHERE Tree.Tree_CN = ErrorLog.CN_Number AND ErrorLog.TableName = 'Tree');";
db.Execute(command);
command = "UPDATE TreeFieldSetup set ColumnType = 'Combo' WHERE Field = 'CountOrMeasure' OR Field = 'LiveDead';";
db.Execute(command);
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(db.DatabaseVersion, "2013.06.19", e);
}
}
private static void UpdateTo_2_1_1(DAL db)
{
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(db.DatabaseVersion, "2.1.1", e);
}
}
//patch for some a version that got out in the wild with bad triggers
private static void UpdateToVersion2015_09_01(DAL db)
{
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 + ";");
}
string createTriggers = CruiseDALDatastoreBuilder.GetCreateTriggers();
db.Execute(createTriggers);
SetDatabaseVersion(db, "2015.09.01");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(db.DatabaseVersion, "2015.09.01", e);
}
}
private void ProcessMergeTables(DAL mergeDB)
{
try
{
mergeDB.BeginTransaction();
foreach (MergeTableCommandBuilder cmdBldr in this.CommandBuilders.Values)
{
ProcessMergeTable(mergeDB, cmdBldr);
}
//ProcessMergeTable(mergeDB, this.CommandBuilders["Tree"]);
//ProcessMergeTable(mergeDB, this.CommandBuilders["Log"]);
//ProcessMergeTable(mergeDB, this.CommandBuilders["Stem"]);
////ProcessMergeTable(mergeDB, this.CommandBuilders["TreeEstimate"]);
//ProcessMergeTable(mergeDB, this.CommandBuilders["Plot"]);
mergeDB.CommitTransaction();
}
catch
{
mergeDB.RollbackTransaction();
throw;
}
}
private static void UpdateToVersion2013_08_02(DAL db)
{
try
{
db.BeginTransaction();
db.AddField("Sale", "DefaultUOM TEXT");
string command = "DROP TABLE LogFieldSetupDefault;";
db.Execute(command);
command = @"CREATE TABLE LogFieldSetupDefault (
LogFieldSetupDefault_CN INTEGER PRIMARY KEY AUTOINCREMENT,
Field TEXT NOT NULL,
FieldName TEXT,
FieldOrder INTEGER Default 0,
ColumnType TEXT,
Heading TEXT,
Width REAL Default 0.0,
Format TEXT,
Behavior TEXT,
UNIQUE (Field));";
db.Execute(command);
SetDatabaseVersion(db, "2013.08.02");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(db.DatabaseVersion, "2013.08.02", e);
}
}
public static void UpdateToVersion2013_11_22(DAL db)
{
try
{
db.BeginTransaction();
string command = @"CREATE TABLE TempCountTree (
CountTree_CN INTEGER PRIMARY KEY AUTOINCREMENT,
SampleGroup_CN INTEGER REFERENCES SampleGroup NOT NULL,
CuttingUnit_CN INTEGER REFERENCES CuttingUnit NOT NULL,
Tally_CN INTEGER REFERENCES Tally,
TreeDefaultValue_CN INTEGER REFERENCES TreeDefaultValue,
Component_CN INTEGER REFERENCES Component,
TreeCount INTEGER Default 0,
SumKPI INTEGER Default 0,
SampleSelectorType TEXT,
SampleSelectorState TEXT,
CreatedBy TEXT NOT NULL,
CreatedDate DATETIME,
ModifiedBy TEXT,
ModifiedDate DATETIME,
UNIQUE (SampleGroup_CN, CuttingUnit_CN, TreeDefaultValue_CN, Component_CN));";
db.Execute(command);
command = @"INSERT INTO TempCountTree
(CountTree_CN,
SampleGroup_CN,
CuttingUnit_CN,
Tally_CN,
TreeDefaultValue_CN,
TreeCount,
SumKPI,
SampleSelectorType,
SampleSelectorState,
CreatedBy,
CreatedDate,
ModifiedBy,
ModifiedDate)
SELECT
CountTree_CN,
SampleGroup_CN,
CuttingUnit_CN,
Tally_CN,
TreeDefaultValue_CN,
TreeCount,
SumKPI,
SampleSelectorType,
SampleSelectorState,
CreatedBy,
CreatedDate,
ModifiedBy,
ModifiedDate FROM CountTree;";
db.Execute(command);
command = "DROP TABLE CountTree;";
db.Execute(command);
command = "ALTER TABLE TempCountTree RENAME TO CountTree;";
db.Execute(command);
command = @"
CREATE TRIGGER OnNewCountTree AFTER INSERT ON CountTree BEGIN
UPDATE CountTree SET CreatedDate = datetime(current_timestamp, 'localtime') WHERE rowID = new.rowID; END;
CREATE TRIGGER OnUpdateCountTree UPDATE ON CountTree BEGIN
UPDATE CountTree SET ModifiedDate = datetime(current_timestamp, 'localtime') WHERE rowID = new.rowID; END;";
db.Execute(command);
command = @"CREATE TABLE Component (
Component_CN INTEGER PRIMARY KEY AUTOINCREMENT,
GUID TEXT,
LastMerge DATETIME,
FileName TEXT);";
db.Execute(command);
SetDatabaseVersion(db, "2013.11.22");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(db.DatabaseVersion, "2013.11.22", e);
}
}
private static void UpdateToVersion2013_08_29(DAL db)
{
try
{
db.BeginTransaction();
db.AddField("Stratum", "KZ3PPNT INTEGER Default 0");
SetDatabaseVersion(db, "2013.08.29");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(db.DatabaseVersion, "2013.08.29", e);
}
}
public static void UpdateToVersion2015_04_28(DAL db)
{
try
{
db.BeginTransaction();
db.Execute(@"CREATE TABLE Util_Tombstone (
RecordID INTEGER ,
RecordGUID TEXT,
TableName TEXT NOT NULL COLLATE NOCASE,
Data TEXT,
DeletedDate DATETIME NON NULL);");
// db.Execute(@"
// CREATE VIEW CountTree_View AS
//SELECT Stratum.Code as StratumCode,
//Stratum.Method as Method,
//SampleGroup.Code as SampleGroupCode,
//SampleGroup.PrimaryProduct as PrimaryProduct,
//CountTree.*
//FROM CountTree JOIN SampleGroup USING (SampleGroup_CN) JOIN Stratum USING (Stratum_CN);");
db.Execute(@"ALTER TABLE Sale ADD COLUMN RowVersion INTEGER DEFAULT 0;
ALTER TABLE CuttingUnit ADD COLUMN RowVersion INTEGER DEFAULT 0;
ALTER TABLE Stratum ADD COLUMN RowVersion INTEGER DEFAULT 0;
ALTER TABLE SampleGroup ADD COLUMN RowVersion INTEGER DEFAULT 0;
ALTER TABLE TreeDefaultValue ADD COLUMN RowVersion INTEGER DEFAULT 0;
ALTER TABLE Plot ADD COLUMN RowVersion INTEGER DEFAULT 0;
ALTER TABLE Tree ADD COLUMN RowVersion INTEGER DEFAULT 0;
ALTER TABLE Log ADD COLUMN RowVersion INTEGER DEFAULT 0;
ALTER TABLE Stem ADD COLUMN RowVersion INTEGER DEFAULT 0;
ALTER TABLE CountTree ADD COLUMN RowVersion INTEGER DEFAULT 0;");
db.Execute(@"ALTER TABLE Stem ADD COLUMN CreatedBy TEXT;
ALTER TABLE Stem ADD COLUMN CreatedDate DATETIME;
ALTER TABLE Stem ADD COLUMN ModifiedBy TEXT;
ALTER TABLE Stem ADD COLUMN ModifiedDate DATETIME;
ALTER TABLE TreeEstimate ADD COLUMN CreatedBy TEXT;
ALTER TABLE TreeEstimate ADD COLUMN CreatedDate DATETIME;
ALTER TABLE TreeEstimate ADD COLUMN ModifiedBy TEXT;
ALTER TABLE TreeEstimate ADD COLUMN ModifiedDate DATETIME;
ALTER TABLE TreeDefaultValue ADD COLUMN CreatedBy TEXT;
ALTER TABLE TreeDefaultValue ADD COLUMN CreatedDate DATETIME;
ALTER TABLE TreeDefaultValue ADD COLUMN ModifiedBy TEXT;
ALTER TABLE TreeDefaultValue ADD COLUMN ModifiedDate DATETIME;");
db.Execute("ALTER TABLE SampleGroup ADD COLUMN SmallFPS REAL DEFAULT 0.0;");
db.Execute("ALTER TABLE Tree ADD COLUMN UpperStemDiameter REAL DEFAULT 0.0;");
db.Execute("UPDATE Tree SET UpperStemDiameter = UpperstemDOB;");
db.Execute("UPDATE TreeFieldSetup SET Field = 'UpperStemDiameter' WHERE Field = 'UpperStemDiameter';");
db.Execute("UPDATE TreeFieldSetupDefault SET Field = 'UpperStemDiameter' WHERE Field = 'UpperStemDiameter';");
db.Execute("ALTER TABLE Stratum ADD COLUMN YieldComponent TEXT DEFAULT 'CL';");
db.Execute("UPDATE TreeDefaultValue SET Chargeable = null;");
db.Execute("ALTER TABLE CuttingUnitStratum ADD COLUMN StratumArea REAL;");
db.Execute(@"CREATE VIEW StratumAcres_View AS
SELECT CuttingUnit.Code as CuttingUnitCode,
Stratum.Code as StratumCode,
ifnull(Area, CuttingUnit.Area) as Area,
CuttingUnitStratum.*
FROM CuttingUnitStratum
JOIN CuttingUnit USING (CuttingUnit_CN)
JOIN Stratum USING (Stratum_CN);");
db.Execute("PRAGMA user_version = 1");
SetDatabaseVersion(db, "2015.04.28");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(db.DatabaseVersion, "2015.04.28", e);
}
}
private static void UpdateToVersion2013_11_01(DAL db)
{
try
{
db.BeginTransaction();
string command = @" CREATE TABLE IF NOT EXISTS LogMatrix (
ReportNumber TEXT,
GradeDescription TEXT,
LogSortDescription TEXT,
Species TEXT,
LogGrade1 TEXT,
LogGrade2 TEXT,
LogGrade3 TEXT,
LogGrade4 TEXT,
LogGrade5 TEXT,
LogGrade6 TEXT,
SEDlimit TEXT,
SEDminimum DOUBLE Default 0.0,
SEDmaximum DOUBLE Default 0.0);";
db.Execute(command);
SetDatabaseVersion(db, "2013.11.01");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(db.DatabaseVersion, "2013.11.01", e);
}
}
public static void UpdateToVersion2015_08_03(DAL db)
{
try
{
db.BeginTransaction();
db.AddField("Plot", "Plot_GUID TEXT");
db.AddField("Tree", "Tree_GUID TEXT");
db.AddField("Log", "Log_GUID TEXT");
db.AddField("Stem", "Stem_GUID TEXT");
db.AddField("TreeEstimate", "TreeEstimate_GUID");
SetDatabaseVersion(db, "2015.08.03");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(db.DatabaseVersion, "2015.08.03", e);
}
}
private static void UpdateToVersion2014_06_04(DAL db)
{
try
{
db.BeginTransaction();
db.AddField("Sale", "LogGradingEnabled BOOLEAN Default 0");
SetDatabaseVersion(db, "2014.06.04");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(db.DatabaseVersion, "2014.06.04", e);
}
}
private static void RebuildTable(DAL db, String tableName, String newTableDef, String columnList)
{
//get all triggers associated with table so we can recreate them later
var getTriggers = String.Format("SELECT group_concat(sql,';\r\n') FROM sqlite_master WHERE tbl_name LIKE '{0}' and type LIKE 'trigger';", tableName);
var triggers = db.ExecuteScalar(getTriggers) as string;
db.BeginTransaction();
try
{
db.Execute("PRAGMA foreign_keys = off;");
db.Execute("ALTER TABLE " + tableName + " RENAME TO " + tableName + "temp;");
//create rebuilt table
db.Execute(newTableDef + ";");
//copy data from existing table to rebuilt table
db.Execute(
"INSERT INTO " + tableName +
" ( " + columnList + ") " +
"SELECT " + columnList + " FROM " + tableName + "temp;");
db.Execute("DROP TABLE " + tableName + "temp;");
//recreate triggers
if (triggers != null)
{
db.Execute(triggers);
}
db.Execute("PRAGMA foreign_keys = on;");
db.CommitTransaction();
}
catch
{
db.RollbackTransaction();
throw;
}
}
private static void UpdateToVersion2014_07_07(DAL db)
{
try
{
db.BeginTransaction();
db.AddField("SampleGroup", "MinKPI INTEGER Default 0");
db.AddField("SampleGroup", "MaxKPI INTEGER Default 0");
SetDatabaseVersion(db, "2014.07.07");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(db.DatabaseVersion, "2014.07.07", e);
}
}
private static void UpdateToVersion2013_05_30(DAL db)
{
try
{
db.BeginTransaction();
RebuildTable(db, "Plot",
@"CREATE TABLE Plot (
Plot_CN INTEGER PRIMARY KEY AUTOINCREMENT,
Stratum_CN INTEGER REFERENCES Stratum NOT NULL,
CuttingUnit_CN INTEGER REFERENCES CuttingUnit NOT NULL,
PlotNumber INTEGER NOT NULL,
IsEmpty TEXT,
Slope REAL Default 0.0,
KPI REAL Default 0.0,
Aspect REAL Default 0.0,
Remarks TEXT,
XCoordinate REAL Default 0.0,
YCoordinate REAL Default 0.0,
ZCoordinate REAL Default 0.0,
MetaData TEXT,
Blob BLOB,
CreatedBy TEXT NOT NULL,
CreatedDate DATETIME,
ModifiedBy TEXT,
ModifiedDate DATETIME,
UNIQUE (Stratum_CN, CuttingUnit_CN, PlotNumber));",
"PlotNumber, IsEmpty, Slope, KPI, Aspect, Remarks, XCoordinate, YCoordinate," +
"ZCoordinate, MetaData, Blob, Stratum_CN, CuttingUnit_CN, CreatedBy, " +
"CreatedDate, ModifiedBy, ModifiedDate");
db.AddField("CuttingUnit", "TallyHistory TEXT");
SetDatabaseVersion(db, "2013.05.30");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(db.DatabaseVersion, "2013.05.30", e);
}
}
private static void UpdateToVersion2014_07_24(DAL db)
{
try
{
db.BeginTransaction();
db.Execute(@"
DROP TRIGGER IF EXISTS OnDeleteTree;
CREATE TRIGGER OnDeleteTree AFTER DELETE ON Tree BEGIN
INSERT INTO MessageLog (Message, Date, Time) VALUES (('Tree (' || OLD.Tree_CN || ') Deleted CU_cn:' || OLD.CuttingUnit_CN || ' St_cn:' || OLD.Stratum_CN || ' Plt_CN:' || ifnull(OLD.Plot_CN,'-') || ' T#:' || OLD.TreeNumber), date('now'), time('now')); END;
DROP TRIGGER IF EXISTS OnDeletePlot;
CREATE TRIGGER OnDeletePlot AFTER DELETE ON Plot BEGIN
INSERT INTO MessageLog (Message, Date, Time) VALUES (('Plot (' || OLD.Plot_CN || ') Deleted CU_cn:' || OLD.CuttingUnit_CN || ' St_cn:' || OLD.Stratum_CN || ' Plt#:' || OLD.PlotNumber), date('now'), time('now')); END;
");
SetDatabaseVersion(db, "2014.07.24");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(db.DatabaseVersion, "2014.07.24", e);
}
}
private static void UpdateToVersion2013_06_12(DAL db)
{
try
{
db.BeginTransaction();
db.AddField("ErrorLog", "Program TEXT");
//modify globals table, making Block & Key Unique
string command = @"CREATE TABLE TempGlobals (
Block TEXT,
Key TEXT,
Value TEXT,
UNIQUE (Block, Key));";
db.Execute(command);
command = "INSERT OR IGNORE INTO TempGlobals (Block, Key, Value) SELECT Block, Key, Value FROM Globals;";
db.Execute(command);
command = "DROP TABLE Globals;";
db.Execute(command);
command = "ALTER TABLE TempGlobals RENAME TO Globals;";
db.Execute(command);
command = "UPDATE CountTree SET TreeDefaultValue_CN = NULL WHERE TreeDefaultValue_CN = 0;";
db.Execute(command);
SetDatabaseVersion(db, "2013.06.12");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(db.DatabaseVersion, "2013.06.12", e);
}
}
private static void UpdateToVersion2014_09_02(DAL db)
{
try
{
db.BeginTransaction();
db.AddField(Schema.SAMPLEGROUP._NAME, "TallyMethod TEXT");
SetDatabaseVersion(db, "2014.09.02");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(db.DatabaseVersion, "2014.09.02", e);
}
}
private static void UpdateToVersion2013_06_17(DAL db)
{
try
{
db.BeginTransaction();
RebuildTable(db, "Tree",
@"CREATE TABLE Tree (
Tree_CN INTEGER PRIMARY KEY AUTOINCREMENT,
TreeDefaultValue_CN INTEGER REFERENCES TreeDefaultValue,
Stratum_CN INTEGER REFERENCES Stratum NOT NULL,
SampleGroup_CN INTEGER REFERENCES SampleGroup,
CuttingUnit_CN INTEGER REFERENCES CuttingUnit NOT NULL,
Plot_CN INTEGER REFERENCES Plot,
TreeNumber INTEGER NOT NULL,
Species TEXT,
CountOrMeasure TEXT,
TreeCount REAL Default 0.0,
KPI REAL Default 0.0,
STM TEXT Default 'N',
SeenDefectPrimary REAL Default 0.0,
SeenDefectSecondary REAL Default 0.0,
RecoverablePrimary REAL Default 0.0,
Initials TEXT,
LiveDead TEXT,
Grade TEXT,
HeightToFirstLiveLimb REAL Default 0.0,
PoleLength REAL Default 0.0,
ClearFace TEXT,
CrownRatio REAL Default 0.0,
DBH REAL Default 0.0,
DRC REAL Default 0.0,
TotalHeight REAL Default 0.0,
MerchHeightPrimary REAL Default 0.0,
MerchHeightSecondary REAL Default 0.0,
FormClass REAL Default 0.0,
UpperStemDOB REAL Default 0.0,
UpperStemHeight REAL Default 0.0,
DBHDoubleBarkThickness REAL Default 0.0,
TopDIBPrimary REAL Default 0.0,
TopDIBSecondary REAL Default 0.0,
DefectCode TEXT,
DiameterAtDefect REAL Default 0.0,
VoidPercent REAL Default 0.0,
Slope REAL Default 0.0,
Aspect REAL Default 0.0,
Remarks TEXT,
XCoordinate DOUBLE Default 0.0,
YCoordinate DOUBLE Default 0.0,
ZCoordinate DOUBLE Default 0.0,
MetaData TEXT,
IsFallBuckScale INTEGER Default 0,
CreatedBy TEXT NOT NULL,
CreatedDate DATETIME,
ModifiedBy TEXT,
ModifiedDate DATETIME,
ExpansionFactor REAL Default 0.0,
TreeFactor REAL Default 0.0,
PointFactor REAL Default 0.0,
UNIQUE (TreeDefaultValue_CN, Stratum_CN, SampleGroup_CN, CuttingUnit_CN, Plot_CN, TreeNumber))",
"TreeNumber, Species, CountOrMeasure, TreeCount, KPI, STM, SeenDefectPrimary, " +
"SeenDefectSecondary, RecoverablePrimary, Initials, LiveDead, Grade, " +
"HeightToFirstLiveLimb, PoleLength, ClearFace, CrownRatio, DBH, DRC, TotalHeight, " +
"MerchHeightPrimary, MerchHeightSecondary, FormClass, UpperStemDOB, UpperStemHeight, " +
"DBHDoubleBarkThickness, TopDIBPrimary, TopDIBSecondary, DefectCode, DiameterAtDefect, " +
"VoidPercent, Remarks, XCoordinate, YCoordinate, ZCoordinate, MetaData, " +
"IsFallBuckScale, ExpansionFactor, TreeFactor, PointFactor, TreeDefaultValue_CN, " +
"Stratum_CN, SampleGroup_CN, CuttingUnit_CN, Plot_CN, CreatedBy, CreatedDate, " +
"ModifiedBy, ModifiedDate");
//command = "ALTER TABLE ErrorLog ADD COLUMN Suppress BOOLEAN;";
//db.Execute(command);
SetDatabaseVersion(db, "2013.06.17");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(db.DatabaseVersion, "2013.06.17", e);
}
}
private static void UpdateToVersion2015_08_19(DAL db)
{
System.Collections.Generic.List<ColumnInfo> 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", "ErrorMessage TEXT");
}
SetDatabaseVersion(db, "2015.08.19");
db.CommitTransaction();
}
catch (Exception e)
{
db.RollbackTransaction();
throw new SchemaUpdateException(db.DatabaseVersion, "2015.08.19", e);
}
}
protected void CreateComponent(DAL masterDAL, int compNum, ComponentDO compInfo, String compPath)
{
//copy master to create component file
masterDAL.CopyTo(compPath);
var compDB = new DAL(compPath);
try
{
compDB.BeginTransaction();
compDB.Execute("DELETE FROM CountTree WHERE Component_CN IS NOT NULL;");
compDB.Execute(SQL.CLEAR_FIELD_DATA);
string command = string.Format("UPDATE CountTree Set Component_CN = {0};", compInfo.Component_CN);
compDB.Execute(command);
//Set the starting rowID for each component
compDB.SetTableAutoIncrementStart("Tree", GetComponentRowIDStart(compNum));
compDB.SetTableAutoIncrementStart("Log", GetComponentRowIDStart(compNum));
compDB.SetTableAutoIncrementStart("TreeEstimate", GetComponentRowIDStart(compNum));
compDB.SetTableAutoIncrementStart("Stem", GetComponentRowIDStart(compNum));
compDB.SetTableAutoIncrementStart("Plot", compNum * PLOT_ROW_SPACING);
compDB.Execute("DELETE FROM Globals WHERE Block = 'Comp' AND Key = 'ChildComponents';");
compDB.Execute("DELETE FROM Globals WHERE Block = 'Comp' AND Key = 'LastMerge';");
compDB.CommitTransaction();
}
catch (Exception)
{
compDB.RollbackTransaction();
try
{
//component is probably jacked up, so delete it
System.IO.File.Delete(compDB.Path);
}
catch { } //may throw exception if file doesn't exist, but we can ignore that
}
finally
{
compDB.Dispose();
}
}