public void PushTreeDefaultInserts(ComponentFileVM comp) { StartJob("Push TreeDefaultValue Inserts"); int? rowsAffected = Master.Execute("INSERT OR IGNORE INTO " + comp.DBAlias + ".TreeDefaultValue " + "SELECT * FROM main.TreeDefaultValue;"); PostStatus(rowsAffected.GetValueOrDefault(0).ToString() + " Rows Affected"); EndJob(); }
public void PushNewStratumRecords(ComponentFileVM comp) { StartJob("Add New Strata"); int? rowsAffected = Master.Execute("INSERT OR IGNORE INTO " + comp.DBAlias + ".Stratum " + "SELECT * FROM main.Stratum;"); PostStatus(rowsAffected.GetValueOrDefault(0).ToString() + " Rows Affected"); EndJob(); }
public void PushNewUnitRecords(ComponentFileVM comp) { StartJob("Add New Units"); int? rowsAffected = Master.Execute("INSERT OR IGNORE INTO " + comp.DBAlias + ".CuttingUnit " + "SELECT * FROM main.CuttingUnit;"); EndJob(); }
public string SelectMissingMatches(ComponentFileVM comp) { return "SELECT client." + this.ClientPrimaryKey.Name + " AS MatchRowID FROM " + this.ClientTableName + " AS client " + "LEFT JOIN " + this.MergeTableName + " AS mrg " + "ON (client." + this.ClientPrimaryKey.Name + " = mrg.MatchRowID AND ComponentID = " + comp.Component_CN.ToString() + ") " + "WHERE mrg.MatchRowID IS NULL;"; }
private void PopulateMergeTables(ComponentFileVM comp) { MasterDB.AttachDB(comp.Database, "compDB"); //may throw exception MasterDB.BeginTransaction(); try { foreach (MergeTableCommandBuilder cmdBldr in this.CommandBuilders.Values) { PopulateMergeTable(this.MasterDB, cmdBldr, comp); } //PopulateMergeTable(this.MasterDB, this.CommandBuilders["Tree"], comp); //PopulateMergeTable(this.MasterDB, this.CommandBuilders["Log"], comp); //PopulateMergeTable(this.MasterDB, this.CommandBuilders["Stem"], comp); ////PopulateMergeTable(this.MasterDB, this.CommandBuilders["TreeEstimate"], comp); //PopulateMergeTable(this.MasterDB, this.CommandBuilders["Plot"], comp); MasterDB.CommitTransaction(); this.NotifyProgressChanged(this._progressInCurrentJob++, false, "Imported Merge Info For Component #" + comp.Component_CN.ToString(), null); } catch { MasterDB.RollbackTransaction(); throw; } finally { MasterDB.DetachDB("compDB"); } }
private void ResetRowVersion(ComponentFileVM comp, long masterRowID, long componentRowID, MergeTableCommandBuilder commBldr) { Master.Execute("UPDATE " + commBldr.ClientTableName + " SET RowVersion = 0 WHERE RowID = ?;", masterRowID); ResetComponentRowVersion(comp, componentRowID, commBldr); }
public String GetPopulateMergeTableCommand(ComponentFileVM component) { string clientFieldList = string.Empty; if (this.ClientFieldNames.Length > 0) { clientFieldList = ", " + String.Join(", ", this.ClientFieldNames); } String populateMergetableCMD = String.Format( "INSERT INTO {0} " + "(ComponentRowID{1}, ComponentID{2}{3}, CompoundNaturalKey) " + "SELECT {4}{5}" + ", {6} AS ComponentID{7}{3}, {9} " + "FROM compDB.{8} as compSource;", this.MergeTableName, (this.HasGUIDKey) ? ", ComponentRowGUID" : String.Empty, (this.HasRowVersion) ? ", ComponentRowVersion" : String.Empty, clientFieldList, this.ClientPrimaryKey.Name, (this.HasGUIDKey) ? ", " + this.ClientGUIDFieldName : String.Empty, component.Component_CN.ToString(), (this.HasRowVersion) ? ", RowVersion AS ComponentRowVersion" : String.Empty, this.ClientTableName, this.GetCompoundNaturalKeyExpression("compSource")); return populateMergetableCMD; }
public void PullNewSampleGroupTreeDefaults(ComponentFileVM comp) { StartJob("Update Sample Group Species Mappings"); int? rowsAffected = Master.Execute("INSERT OR IGNORE INTO main.SampleGroupTreeDefaultValue " + "SELECT * FROM " + comp.DBAlias + ".SampleGroupTreeDefaultValue;"); PostStatus(rowsAffected.GetValueOrDefault(0).ToString() + " Rows Affected"); EndJob(); }
public void PullNewTallyTable(ComponentFileVM comp) { StartJob("Add New CountTree Records"); List<TallyDO> compTallies = comp.Database.Read<TallyDO>("Tally", null); foreach (TallyDO tally in compTallies) { CheckWorkerStatus(); TallyDO match = Master.ReadSingleRow<TallyDO>("Tally", "WHERE HotKey = ? AND Description = ?", tally.Hotkey, tally.Description); if (match == null) { match = new TallyDO(Master); match.Hotkey = tally.Hotkey; match.Description = tally.Description; match.Save(); } } }
public void PullNew(MergeTableCommandBuilder cmdBldr, ComponentFileVM comp) { StartJob("Pull New From " + cmdBldr.ClientTableName); List<MergeObject> mergeRecords = cmdBldr.ListNewRecords(Master, comp); foreach (MergeObject mRec in mergeRecords) { CheckWorkerStatus(); DataObject newFromComp = cmdBldr.ReadSingleRow(comp.Database, mRec.ComponentRowID.Value); Master.Insert(newFromComp, OnConflictOption.Fail); this.ResetComponentRowVersion(comp, mRec.ComponentRowID.Value, cmdBldr); } EndJob(); }
public void PullNewSampleGroups(ComponentFileVM comp) { StartJob("Add New Sample Groups"); var compSGList = comp.Database.From<SampleGroupDO>().Query(); foreach (SampleGroupDO sg in compSGList) { SampleGroupDO match = Master.From<SampleGroupDO>() .Where("Code = ? AND Stratum_CN = ?") .Query(sg.Code, sg.Stratum_CN).FirstOrDefault(); if (match == null) { SampleGroupDO newSG = new SampleGroupDO(Master); newSG.SuspendEvents(); newSG.SetValues(sg); newSG.Stratum_CN = sg.Stratum_CN; newSG.ResumeEvents(); newSG.Save(); match = newSG; } if (sg.SampleGroup_CN != match.SampleGroup_CN) { comp.Database.Execute("UPDATE SampleGroup SET SampleGroup_CN = ? WHERE SampleGroup_CN = ?;", match.SampleGroup_CN, sg.SampleGroup_CN); } } EndJob(); }
public void PullMasterTreeUpdates(ComponentFileVM comp) { StartJob("Update Master Trees"); MergeTableCommandBuilder cmdBldr = this.CommandBuilders["Tree"]; List<MergeObject> pullList = cmdBldr.ListMasterUpdates(Master, comp); foreach (MergeObject mRec in pullList) { CheckWorkerStatus(); long matchRowid = mRec.MatchRowID.Value; TreeDO tree = comp.Database.From<TreeDO>().Where("rowid = ?") .Query(mRec.ComponentRowID).FirstOrDefault(); Master.Update(tree, matchRowid, OnConflictOption.Fail); this.ResetRowVersion(comp, matchRowid, mRec.ComponentRowID.Value, cmdBldr); IncrementProgress(); } EndJob(); }
public void PullMasterPlotUpdates(ComponentFileVM comp) { StartJob("Update Master Plots"); MergeTableCommandBuilder cmdBldr = this.CommandBuilders["Plot"]; List<MergeObject> pullList = cmdBldr.ListMasterUpdates(Master, comp); foreach (MergeObject mRec in pullList) { CheckWorkerStatus(); long matchRowid = mRec.MatchRowID.Value; PlotDO plot = comp.Database.ReadSingleRow<PlotDO>("Plot", mRec.ComponentRowID); Master.Update(plot, matchRowid, OnConflictOption.Fail); this.ResetRowVersion(comp, matchRowid, mRec.ComponentRowID.Value, cmdBldr); IncrementProgress(); } EndJob(); }
public void PullCountTreeChanges(ComponentFileVM comp) { StartJob("Add New CountTree Records"); var compCounts = comp.Database.From<CountTreeDO>().Query(); foreach (CountTreeDO count in compCounts) { CheckWorkerStatus(); CountTreeDO match = Master.From<CountTreeDO>() .Where("SampleGroup_CN = ? " + "AND ifnull(TreeDefaultValue_CN, 0) = ifnull(?, 0) " + "AND CuttingUnit_CN = ? " + "AND Component_CN = ?") .Query(count.SampleGroup_CN, count.TreeDefaultValue_CN, count.CuttingUnit_CN, comp.Component_CN).FirstOrDefault(); //use component cn from component record because component cn is not set when record is created by FScruiser if (match != null) { match.TreeCount = count.TreeCount; match.SumKPI = count.SumKPI; match.Save(); } else { TallyDO tally = count.Tally; TallyDO masterTally = Master.From<TallyDO>().Where("HotKey = ?") .Query(tally.Hotkey).FirstOrDefault(); if (masterTally == null) { //TODO unsupported } else { count.Tally_CN = masterTally.Tally_CN; } if (count.Component_CN == null) { count.Component_CN = comp.Component_CN; //Master.Execute("UPDATE " + comp.DBAlias + ".CountTree Set Component_CN = ? WHERE CountTree_CN = ?;", comp.Component_CN, count.CountTree_CN); } Master.Insert(count, null, OnConflictOption.Fail); } } EndJob(); }
public void PushUnitStratumChanges(ComponentFileVM comp) { StartJob("Update Component Unit Strata Mappings"); int? rowsAffected = Master.Execute("DELETE FROM " + comp.DBAlias + ".CuttingUnitStratum; " + "INSERT OR IGNORE INTO " + comp.DBAlias + ".CuttingUnitStratum " + "SELECT * FROM main.CuttingUnitStratum;"); PostStatus(rowsAffected.GetValueOrDefault(0).ToString() + " Rows Affected"); EndJob(); }
public void PullNewTreeRecords(ComponentFileVM comp) { StartJob("Add New Trees"); MergeTableCommandBuilder cmdBldr = this.CommandBuilders["Tree"]; List<MergeObject> mergeRecords = cmdBldr.ListNewRecords(Master, comp); foreach (MergeObject mRec in mergeRecords) { CheckWorkerStatus(); TreeDO tree = comp.Database.From<TreeDO>() .Where("rowid = ?").Query(mRec.ComponentRowID).FirstOrDefault(); Master.Insert(tree, OnConflictOption.Fail); this.ResetComponentRowVersion(comp, mRec.ComponentRowID.Value, cmdBldr); IncrementProgress(); } EndJob(); }
private void ResetComponentRowVersion(ComponentFileVM comp, long componentRowID, MergeTableCommandBuilder commBldr) { comp.Database.Execute("UPDATE " + commBldr.ClientTableName + " SET RowVersion = 0 WHERE RowID = ?;", componentRowID); }
public void PullTreeDefaultInserts(ComponentFileVM comp) { StartJob("Pull TreeDefault Inserts"); var compTreeDefaults = comp.Database.From<TreeDefaultValueDO>().Query(); foreach (TreeDefaultValueDO tdv in compTreeDefaults) { CheckWorkerStatus(); bool hasMatch = 0 < Master.GetRowCount("TreeDefaultValue", "WHERE Species = ? AND PrimaryProduct = ? AND LiveDead = ?", tdv.Species, tdv.PrimaryProduct, tdv.LiveDead); if (!hasMatch) { if (Master.GetRowCount("TreeDefaultValue", "WHERE TreeDefaultValue_CN = ?", tdv.TreeDefaultValue_CN) == 0) { Master.Insert(tdv, OnConflictOption.Fail); } else { throw new NotImplementedException("TreeDefaultValue row conflict condition not implemented"); //Master.Insert(tdv, false, OnConflictOption.Fail); //tdv.Save(); } } } EndJob(); }
public String GetPopulateDeletedRecordsCommand(ComponentFileVM component) { String populateDeletedRecordsCMD = String.Format( "INSERT INTO {0} " + "(ComponentRowID{1}, IsDeleted, ComponentID) " + "SELECT RecordID{2}, 1 AS IsDeleted, {3} AS ComponentID FROM Util_Tombstone WHERE TableName = '{4}';", this.MergeTableName, (this.HasGUIDKey) ? ", ComponentRowGUID" : String.Empty, (this.HasGUIDKey) ? ", RecordGUID" : String.Empty, component.Component_CN, this.ClientTableName); return populateDeletedRecordsCMD; }
public void PushComponentStemUpdates(ComponentFileVM comp) { StartJob("Update Component Stems"); MergeTableCommandBuilder cmdBldr = this.CommandBuilders["Stem"]; List<MergeObject> pushList = cmdBldr.ListComponentUpdates(Master, comp); foreach (MergeObject mRec in pushList) { CheckWorkerStatus(); long matchRowid = mRec.MatchRowID.Value; StemDO stem = Master.ReadSingleRow<StemDO>("Stem", matchRowid); comp.Database.Update(stem, mRec.ComponentRowID.Value, OnConflictOption.Fail); this.ResetRowVersion(comp, matchRowid, mRec.ComponentRowID.Value, cmdBldr); IncrementProgress(); } EndJob(); }
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); }
public void PushComponentTreeUpdates(ComponentFileVM comp) { StartJob("Update Component Trees"); MergeTableCommandBuilder cmdBldr = this.CommandBuilders["Tree"]; List<MergeObject> pushList = cmdBldr.ListComponentUpdates(Master, comp); foreach (MergeObject mRec in pushList) { CheckWorkerStatus(); long matchRowid = mRec.MatchRowID.Value; TreeDO tree = Master.ReadSingleRow<TreeDO>("Tree", matchRowid); //TODO need to handle condition where MasterRowID is different from ComponentRowID comp.Database.Update(tree, mRec.ComponentRowID.Value, OnConflictOption.Fail); this.ResetRowVersion(comp, matchRowid, mRec.ComponentRowID.Value, cmdBldr); IncrementProgress(); } EndJob(); }
private bool InitializeComponent(ComponentFileVM comp) { comp.ResetCounts(); if (!System.IO.File.Exists(comp.FullPath)) { comp.Errors = "File not found"; return false; } try { comp.Database = new DAL(comp.FullPath); if (UpdateMasterWorker.HasUnassignedGUIDs(comp.Database)) { UpdateMasterWorker.AssignGuids(comp.Database); } String[] errors; if (comp.Database.HasCruiseErrors(out errors)) { comp.Errors += string.Join("\r\n", errors); } return true; } catch (Exception e) { comp.Errors += e.Message; return false; } }
private void PopulateMergeTable(DAL masterDB, MergeTableCommandBuilder table, ComponentFileVM comp) { CheckWorkerStatus(); masterDB.Execute(table.GetPopulateMergeTableCommand(comp)); masterDB.Execute(table.GetPopulateDeletedRecordsCommand(comp)); }