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);
}
}
public List<MergeObject> ListNewRecords(DAL master, ComponentFileVM comp)
{
string selectNewRecordsCommand =
"SELECT * FROM " + this.MergeTableName +
this.FindNewRecords +
" AND ComponentID = ?;";
return master.Query<MergeObject>(selectNewRecordsCommand, comp.Component_CN);
}
//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 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 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 DoUpdate(DAL master, DAL[] components)
{
DAL[] allFiles = new DAL[components.Length + 1];
allFiles[0] = master;
Array.Copy(components, 0, allFiles, 1, components.Length);
foreach (DAL db in allFiles)
{
db.BeginTransaction();
}
try
{
foreach (TreeKey tk in master.Query<TreeKey>("SELECT * FROM TREE " + SELECT_TREES + ";"))
{
tk.Tree_GUID = Guid.NewGuid();
foreach (DAL db in allFiles)
{
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 master.Query<PlotKey>("SELECT * FROM Plot " + SELECT_PLOTS + ";"))
{
pk.Plot_GUID = Guid.NewGuid();
foreach (DAL db in allFiles)
{
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 master.Query<LogKey>("SELECT * FROM Log " + SELECT_LOGS + ";"))
{
lk.Log_GUID = Guid.NewGuid();
foreach (DAL db in allFiles)
{
db.Execute("UPDATE Log SET Log_GUID = ? WHERE Log_CN = ?;", lk.Log_GUID.ToString(), lk.Log_CN);
}
}
foreach (TreeEstimateKey tek in master.Query<TreeEstimateKey>("SELECT * FROM TreeEstimate " + SELECT_TREEEST + ";"))
{
tek.TreeEstimate_GUID = Guid.NewGuid();
foreach (DAL db in allFiles)
{
db.Execute("UPDATE TreeEstimate SET TreeEstimate_GUID = ? WHERE TreeEstimate_CN = ?", tek.TreeEstimate_GUID.ToString(), tek.TreeEstimate_CN);
}
}
foreach (DAL db in allFiles)
{
db.CommitTransaction();
}
}
catch
{
foreach (DAL db in allFiles)
{
db.RollbackTransaction();
}
throw;
}
}
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;
}
}