public void ReadOneAllSetup(DAL db)
{
StringBuilder sb = new StringBuilder();
foreach (String tableName in CruiseDAL.Schema.Schema.TABLE_NAMES)
{
List<ColumnInfo> columnInfos = db.GetTableInfo(tableName);
String[] columnNames = (from ci in columnInfos
where ci.IsRequired
select ci.Name).ToArray();
String[] values = (from ci in columnInfos
where ci.IsRequired
select (ci.DBType == "TEXT") ? "'TestStr'" :
(ci.DBType == "INTEGER") ? "101010" :
(ci.DBType == "REAL") ? "202.02" : "'Something'").ToArray();
if (columnNames.Length > 0)
{
sb.AppendFormat("INSERT INTO {0} ({1}) VALUES ({2}); ", tableName, string.Join(",", columnNames), string.Join(",", values));
}
else
{
sb.AppendFormat("INSERT INTO {0} DEFAULT VALUES; ", tableName);
}
}
db.Execute(sb.ToString());
}
public void Execute(DAL database)
{
database.Execute(
@"BEGIN;
ALTER TABLE CountTree RENAME TO TempCountTree;
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));
INSERT INTO CountTree
(CountTree_CN, SampleGroup_CN, CuttingUnit_CN, Tally_CN, TreeDefaultValue_CN, Component_CN,
TreeCount, SumKPI,
CreatedBy, CreatedDate, ModifiedBy, ModifiedDate)
SELECT CountTree_CN, SampleGroup_CN, CuttingUnit_CN, Tally_CN, TreeDefaultValue_CN, Component_CN,
Sum(TreeCount), Sum(SumKPI),
CreatedBy, CreatedDate, ModifiedBy, ModifiedDate
FROM TempCountTree
GROUP BY SampleGroup_CN, CuttingUnit_CN,
ifnull(TreeDefaultValue_CN, 0), ifnull(Component_CN, 0);
DROP TABLE TempCountTree;
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;
COMMIT;"
);
}
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 void ProcessExistingMasterRecordsWithoutGuids(DAL mergeDB, MergeTableCommandBuilder commandBuider)
//{
// List<MergeObject> matchs = mergeDB.Query<MergeObject>(
// commandBuider.SelectNaturalAndCNMatches);
// this._progressInCurrentJob = 0;
// this._workInCurrentJob = matchs.Count + 1;
// this.NotifyProgressChanged(this._progressInCurrentJob, false, "Processing Records Without GUIDs", null);
// String updateCommand = "UPDATE " + commandBuider.MergeTableName + " SET MasterRowID = ?, MasterRowVersion = ? WHERE MergeRowID = ?;";
// foreach (MergeObject c in matchs)
// {
// CheckWorkerStatus();
// mergeDB.Execute(updateCommand, c.MasterRowID, c.MasterRowVersion, c.MergeRowID);
// this.NotifyProgressChanged(this._progressInCurrentJob++, false, null, null);
// }
//}
//private void ProcessExistingRecords(DAL mergeDB, MergeTableCommandBuilder commandBuider)
//{
// //match records in master with records in component and set Master row id
// List<MergeObject> matches = mergeDB.Query<MergeObject>(
// commandBuider.SelectGUIDMatches);
// this._progressInCurrentJob = 0;
// this._workInCurrentJob = matches.Count + 1;
// this.NotifyProgressChanged(this._progressInCurrentJob, false, "Processing Existing Records", null);
// String updateCommand = String.Format("UPDATE {0} SET MasterRowID = ?, MasterRowVersion = ? WHERE MergeRowID = ?;", commandBuider.MergeTableName);
// foreach (MergeObject item in matches)
// {
// CheckWorkerStatus();
// mergeDB.Execute(updateCommand, item.MasterRowID, item.MasterRowVersion, item.MergeRowID);
// this.NotifyProgressChanged(this._progressInCurrentJob++, false, null, null);
// }
//}
//private void ProcessCrossComponentConflicts(DAL mergeDB, MergeTableCommandBuilder commandBuider)
//{
// //check other merge records to see if there are any conflicts with other records being merged
// List<MergeObject> matches = mergeDB.Query<MergeObject>(
// commandBuider.NaturalCrossComponentConflictsCommand);
// this._workInCurrentJob += matches.Count;
// this.NotifyProgressChanged(this._progressInCurrentJob, false, "Processing Cross Component Conflicts", null);
// String updateCommand = "UPDATE " + commandBuider.MergeTableName + " SET ComponentConflict = ? WHERE MergeRowID = ?;";
// foreach (MergeObject c in matches)
// {
// CheckWorkerStatus();
// mergeDB.Execute(updateCommand, c.ComponentConflict, c.MergeRowID);
// this.NotifyProgressChanged(this._progressInCurrentJob++, false, null, null);
// }
//}
private void FindNaturalSiblingMatches(DAL mergeDB, MergeTableCommandBuilder cmdBldr)
{
List<MergeObject> naturalSiblings = mergeDB.Query<MergeObject>(
"SELECT CompoundNaturalKey, NaturalSiblings FROM (" +
"SELECT CompoundNaturalKey, group_concat(MergeRowID, ',') as NaturalSiblings, count(1) as size FROM " + cmdBldr.MergeTableName +
" GROUP BY CompoundNaturalKey) WHERE size > 1;");
string setNaturalSiblings = "UPDATE " + cmdBldr.MergeTableName + " SET NaturalSiblings = ? WHERE CompoundNaturalKey = ?;";
foreach (MergeObject groups in naturalSiblings)
{
mergeDB.Execute(setNaturalSiblings, groups.NaturalSiblings, groups.CompoundNaturalKey);
}
}
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 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);
}
}
/// <summary>
/// helper method for Finish, executes database command that creates field set up entries from field setup defaults
/// </summary>
/// <param name="stratum"></param>
/// <param name="database"></param>
private void SetFieldSetup(StratumDO stratum, DAL database)
{
string setTreeFieldCommand = String.Format(@"INSERT OR IGNORE INTO TreeFieldSetup (Stratum_CN, Field, FieldOrder, ColumnType, Heading, Width, Format, Behavior)
Select {0} as Stratum_CN, Field, FieldOrder, ColumnType, Heading, Width, Format, Behavior
FROM TreeFieldSetupDefault
WHERE Method = '{1}';", stratum.Stratum_CN, stratum.Method);
string setLogFieldCommand = String.Format(@"INSERT OR IGNORE INTO LogFieldSetup (Stratum_CN, Field, FieldOrder, ColumnType, Heading, Width, Format, Behavior)
Select {0} as Stratum_CN, Field, FieldOrder, ColumnType, Heading, Width, Format, Behavior
FROM LogFieldSetupDefault;",
stratum.Stratum_CN);
database.Execute(setTreeFieldCommand);
database.Execute(setLogFieldCommand);
}
private static void SetDatabaseVersion(DAL db, string newVersion)
{
string command = String.Format("UPDATE Globals SET Value = '{0}' WHERE Block = 'Database' AND Key = 'Version';", newVersion);
db.Execute(command);
db.LogMessage(String.Format("Updated structure version to {0}", newVersion), "I");
db.DatabaseVersion = newVersion;
}
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);
}
}
private void SetMasterRowVersion(DAL master, MergeTableCommandBuilder cmdBldr)
{
string setMasterRowVersion = "UPDATE " + cmdBldr.MergeTableName + " SET MasterRowVersion = " +
"(SELECT RowVersion FROM " + cmdBldr.ClientTableName + " AS client WHERE client.RowID = MatchRowID);";
master.Execute(setMasterRowVersion);
}
private void SetPartialMatches(DAL mergeDB, MergeTableCommandBuilder cmdBldr)
{
List<string> matchSources = new List<string>();
if (cmdBldr.DoNaturalMatch)
{
matchSources.Add("SELECT MergeRowID, NaturalMatch AS PartialMatch FROM " + cmdBldr.MergeTableName +
" WHERE NaturalMatch IS NOT NULL AND MatchRowID IS NULL");
//matchSources.Add("SELECT MergeRowID, NaturalMatch AS PartialMatch FROM " + cmdBldr.MergeTableName +
// " WHERE NaturalMatch IS NOT NULL AND (NaturalMatch != RowIDMatch OR NaturalMatch != GUIDMatch)");
}
if (cmdBldr.DoKeyMatch)
{
matchSources.Add("SELECT MergeRowID, RowIDMatch AS PartialMatch FROM " + cmdBldr.MergeTableName +
" WHERE RowIDMatch IS NOT NULL AND MatchRowID IS NULL");
}
if (cmdBldr.DoGUIDMatch)
{
matchSources.Add("SELECT MergeRowID, GUIDMatch AS PartialMatch FROM " + cmdBldr.MergeTableName +
" WHERE GUIDMatch IS NOT NULL AND MatchRowID IS NULL");
}
string selectPartialMatches = "SELECT MergeRowID, group_concat(PartialMatch, ',') AS PartialMatch FROM ( " +
string.Join(" UNION ", matchSources.ToArray()) + " ) GROUP BY MergeRowID;";
List<MergeObject> partialMatchs = mergeDB.Query<MergeObject>(selectPartialMatches);
string setPartialMatch = "UPDATE " + cmdBldr.MergeTableName + " SET PartialMatch = ? WHERE MergeRowID = ?;";
foreach (MergeObject mRec in partialMatchs)
{
mergeDB.Execute(setPartialMatch, mRec.PartialMatch, mRec.MergeRowID);
}
}
//private void ProcessMissingRecords(DAL mergeDB, MergeTableCommandBuilder commandBuider)
//{
// //check records in master against records in merge table, for missing matches
// //create merge record where component row id and component GUID is null
// List<MergeObject> missing = mergeDB.Query<MergeObject>(
// commandBuider.MissingRecords);
// this._workInCurrentJob += missing.Count;
// this.NotifyProgressChanged(this._progressInCurrentJob, false, "Processing Missing Records", null);
// string insertMissingCommand = String.Format("INSERT INTO Merge{0} (RowIDMatch, IsDeleted) VALUES (?, 1);", commandBuider.ClientTableName);
// foreach (MergeObject item in missing)
// {
// CheckWorkerStatus();
// mergeDB.Execute(insertMissingCommand, item.RowIDMatch);
// this.NotifyProgressChanged(this._progressInCurrentJob++, false, null, null);
// }
//}
private void ProcessFullMatchs(DAL master, MergeTableCommandBuilder cmdBldr)
{
List<MergeObject> matches = master.Query<MergeObject>(cmdBldr.SelectFullMatches);
this._workInCurrentJob += matches.Count;
string setMatches = "UPDATE " + cmdBldr.MergeTableName + " SET MatchRowID = ? WHERE MergeRowID = ?;";
foreach (MergeObject item in matches)
{
CheckWorkerStatus();
master.Execute(setMatches, cmdBldr.GetMatchRowID(item), item.MergeRowID);
this.NotifyProgressChanged(this._progressInCurrentJob++, false, null, null);
}
}
//private void SetIncomingPlaceholder(DAL master, MergeTableCommandBuilder cmdBldr)
//{
// List<MergeObject> incomming = master.Query<MergeObject>("SELECT * FROM " + cmdBldr.MergeTableName +
// " WHERE MatchRowID IS NULL AND MatchConflict IS NULL" +
// " GROUP BY " + String.Join(", ", cmdBldr.ClientUniqueFieldNames) +
// " ORDER BY ComponentID;");
// this._workInCurrentJob += incomming.Count;
// string setplaceHolder = "UPDATE " + cmdBldr.MergeTableName + " SET IncomingPlaceholder = ? WHERE MergeRowID = ?;";
// long placeHolderCounter = 0;
// foreach (MergeObject mRec in incomming)
// {
// CheckWorkerStatus();
// master.Execute(setplaceHolder, placeHolderCounter++, mRec.MergeRowID);
// this.NotifyProgressChanged(this._progressInCurrentJob++, false, null, null);
// }
//}
private void ProcessMasterNew(DAL master, MergeTableCommandBuilder cmdBldr)
{
foreach (ComponentFileVM comp in this.Components)
{
List<MergeObject> missingMatches = master.Query<MergeObject>(cmdBldr.SelectMissingMatches(comp));
string insertMissingMatch = "INSERT INTO " + cmdBldr.MergeTableName + " (MatchRowID, ComponentID) " +
"VALUES (?,?);";
foreach (MergeObject mRec in missingMatches)
{
master.Execute(insertMissingMatch, mRec.MatchRowID, comp.Component_CN);
}
}
}
//private void ProcessMasterConflicts(DAL mergeDB, MergeTableCommandBuilder commandBuider)
//{
// //check merge records agains master table for conflicts
// List<MergeObject> conflicts = mergeDB.Query<MergeObject>(
// commandBuider.MasterConflicts);
// this._progressInCurrentJob = 0;
// this._workInCurrentJob = conflicts.Count + 1;
// this.NotifyProgressChanged(this._progressInCurrentJob, false, "Processing Master Conflicts", null);
// String updateCommand = String.Format("UPDATE {0} SET MasterConflict = ? WHERE MergeRowID = ?;", commandBuider.MergeTableName);
// foreach (MergeObject item in conflicts)
// {
// CheckWorkerStatus();
// mergeDB.Execute(updateCommand, item.MasterConflict, item.MergeRowID);
// this.NotifyProgressChanged(this._progressInCurrentJob++, false, null, null);
// }
//}
private void ProcessComparisons(DAL master, MergeTableCommandBuilder cmdBldr)
{
if (cmdBldr.DoKeyMatch)
{
List<MergeObject> keyMatches = master.Query<MergeObject>(cmdBldr.SelectRowIDMatches);
this._workInCurrentJob += keyMatches.Count;
string setKeyMatch = "UPDATE " + cmdBldr.MergeTableName + " SET RowIDMatch = ? WHERE MergeRowID = ?;";
foreach (MergeObject item in keyMatches)
{
CheckWorkerStatus();
master.Execute(setKeyMatch, item.RowIDMatch, item.MergeRowID);
this.NotifyProgressChanged(this._progressInCurrentJob++, false, null, null);
}
}
if (cmdBldr.DoNaturalMatch)
{
List<MergeObject> natMatches = master.Query<MergeObject>(cmdBldr.SelectNaturalMatches);
this._workInCurrentJob += natMatches.Count;
string setNatMatch = "UPDATE " + cmdBldr.MergeTableName + " SET NaturalMatch = ? WHERE MergeRowID = ?;";
foreach (MergeObject mRec in natMatches)
{
CheckWorkerStatus();
master.Execute(setNatMatch, mRec.NaturalMatch, mRec.MergeRowID);
this.NotifyProgressChanged(this._progressInCurrentJob++, false, null, null);
}
}
if (cmdBldr.HasGUIDKey)
{
List<MergeObject> guidMatches = master.Query<MergeObject>(cmdBldr.SelectGUIDMatches);
this._workInCurrentJob += guidMatches.Count;
string setGuidMatch = "UPDATE " + cmdBldr.MergeTableName + " SET GUIDMatch = ? WHERE MergeRowID = ?;";
foreach (MergeObject mRec in guidMatches)
{
CheckWorkerStatus();
master.Execute(setGuidMatch, mRec.GUIDMatch, mRec.MergeRowID);
this.NotifyProgressChanged(this._progressInCurrentJob++, false, null, null);
}
}
}
private void PopulateMergeTable(DAL masterDB, MergeTableCommandBuilder table, ComponentFileVM comp)
{
CheckWorkerStatus();
masterDB.Execute(table.GetPopulateMergeTableCommand(comp));
masterDB.Execute(table.GetPopulateDeletedRecordsCommand(comp));
}
//private void ProcessInvalidMatchs(DAL master, MergeTableCommandBuilder cmdBldr)
//{
// string setConflict = "UPDATE " + cmdBldr.MergeTableName +
// " SET MatchConflict = 'invalid match' " +
// "WHERE MergeRowID IN (" + cmdBldr.SelectInvalidMatchs +");";
// master.Execute(setConflict);
//}
private void IdentifySiblingRecords(DAL master, MergeTableCommandBuilder cmdBldr)
{
List<string> matchSources = new List<string>();
if (cmdBldr.DoNaturalMatch)
{
matchSources.Add("SELECT MergeRowID, NaturalMatch AS PartialMatch FROM " + cmdBldr.MergeTableName +
" WHERE NaturalMatch IS NOT NULL");
}
if (cmdBldr.DoKeyMatch)
{
matchSources.Add("SELECT MergeRowID, RowIDMatch AS PartialMatch FROM " + cmdBldr.MergeTableName +
" WHERE RowIDMatch IS NOT NULL");
}
if (cmdBldr.DoGUIDMatch)
{
matchSources.Add("SELECT MergeRowID, GUIDMatch AS PartialMatch FROM " + cmdBldr.MergeTableName +
" WHERE GUIDMatch IS NOT NULL");
}
string selectSiblings = "SELECT SiblingRecords FROM (SELECT PartialMatch , group_concat(MergeRowID, ',') as SiblingRecords, count(1) as size FROM ( " +
string.Join(" UNION ", matchSources.ToArray()) + " ) GROUP BY PartialMatch) where size > 1;";
List<MergeObject> siblingsGroups = master.Query<MergeObject>(selectSiblings);
string setSiblingsformat = "UPDATE " + cmdBldr.MergeTableName + " SET SiblingRecords = ifnull(SiblingRecords, '') || ? WHERE MergeRowID in ({0});";
foreach (MergeObject mRec in siblingsGroups)
{
string setSiblings = String.Format(setSiblingsformat, mRec.SiblingRecords);
master.Execute(setSiblings, mRec.SiblingRecords);
}
//List<MergeObject> matchConflicts = master.Query<MergeObject>(cmdBldr.SelectSiblingRecords);
//this._workInCurrentJob += matchConflicts.Count;
//this.NotifyProgressChanged(this._progressInCurrentJob, false, "Processing Duplicate Match Conflicts", null);
//string setMatchConflicts = "UPDATE " + cmdBldr.MergeTableName + " SET SiblingRecords = ? WHERE PartialMatch IS NOT NULL;";
//foreach(MergeObject item in matchConflicts)
//{
// CheckWorkerStatus();
// master.Execute(setMatchConflicts, item.SiblingRecords, item.PartialMatch);
// this.NotifyProgressChanged(this._progressInCurrentJob++, false, null, null);
//}
}
private static void PatchSureToMeasure(DAL db)
{
string command = @"UPDATE TreeFieldSetup SET Field = 'STM' WHERE Field = 'SureToMeasure';
UPDATE TreeFieldSetupDefault SET Field = 'STM' WHERE Field = 'SureToMeasure';";
db.Execute(command);
}
//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 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 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);
}
}
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);
}
}
//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 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);
}
}
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();
}
}
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);
}
}
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);
}
}
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 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;
}
}