public IEnumerable <LogConflict> GetLogConflicts(CruiseDatastore_V3 source, CruiseDatastore_V3 destination, string cruiseID) { var sourceAlias = "src"; destination.AttachDB(source, sourceAlias); try { return(destination.Query <LogConflict>( $@"SELECT destT.CuttingUnitCode, destT.PlotNumber, destT.TreeNumber, destL.LogNumber, destL.LogID AS DestLogID, srcL.LogID AS SrcLogID FROM main.Log AS destL JOIN main.Tree AS destT USING (TreeID) JOIN {sourceAlias}.Tree AS srcT ON destT.CruiseID = srcT.CruiseID AND destT.CuttingUnitCode = srcT.CuttingUnitCode AND ifnull(destT.PlotNumber, 0) = ifnull(srcT.PlotNumber, 0) AND destT.TreeNumber = srcT.TreeNumber JOIN {sourceAlias}.Log AS srcL ON srcT.TreeID = srcL.TreeID AND destL.LogNumber = srcL.LogNumber WHERE destT.CruiseID = @p1 AND destL.LogID != srcL.LogID;", cruiseID).ToArray()); } finally { destination.DetachDB(sourceAlias); } }
public IEnumerable <TreeConflict> GetTreeConflicts(CruiseDatastore_V3 source, CruiseDatastore_V3 destination, string cruiseID) { var sourceAlias = "src"; destination.AttachDB(source, sourceAlias); try { return(destination.Query <TreeConflict>( $@"SELECT destT.CuttingUnitCode, destT.PlotNumber, destT.TreeNumber, srcT.TreeID AS SrcTreeID, destT.TreeID AS DestTreeID FROM main.Tree AS destT JOIN {sourceAlias}.Tree AS srcT ON destT.CruiseID = srcT.CruiseID AND destT.CuttingUnitCode = srcT.CuttingUnitCode AND destT.PlotNumber = srcT.PlotNumber AND destT.TreeNumber = srcT.TreeNumber WHERE destT.CruiseID = @p1 AND destT.TreeID != srcT.TreeID;", cruiseID).ToArray()); } finally { destination.DetachDB(sourceAlias); } }
public void MigrateFromV2ToV3_testUpdaterMigrate(string fileName) { var filePath = Path.Combine(TestFilesDirectory, fileName); // copy file to test temp dir var tempPath = Path.Combine(TestTempPath, fileName); File.Copy(filePath, tempPath); var newFilePath = new Migrator().MigrateFromV2ToV3(filePath, true); using (var destDB = new CruiseDatastore_V3()) using (var srcDB = new CruiseDatastore_V3(newFilePath)) { try { var destConn = destDB.OpenConnection(); Updater_V3.Migrate(srcDB, destDB); var dumpPath = newFilePath + ".dump.crz3"; RegesterFileForCleanUp(dumpPath); destDB.BackupDatabase(dumpPath); File.Exists(dumpPath).Should().BeTrue(); using (var newdb = new CruiseDatastore_V3(dumpPath)) { var tables = newdb.GetTableNames(); newdb.AttachDB(srcDB, "olddb"); foreach (var t in tables) { var stuff = newdb.QueryGeneric($"SELECT * FROM main.{t} EXCEPT SELECT * FROM olddb.{t};") .ToArray(); if (t == "MessageLog") { stuff.Should().NotBeEmpty(); continue; } stuff.Should().BeEmpty(); } } } finally { destDB.ReleaseConnection(); } } }
public void MigrateFromV2ToV3(CruiseDatastore v2db, CruiseDatastore_V3 v3db, string deviceID = null) { var oldDbAlias = "v2"; v3db.AttachDB(v2db, oldDbAlias); try { var connection = v3db.OpenConnection(); MigrateFromV2ToV3(connection, oldDbAlias, deviceID: deviceID, exceptionProcessor: v3db.ExceptionProcessor, migrators: Migrators); } finally { v3db.DetachDB(oldDbAlias); v3db.ReleaseConnection(); } }
public IEnumerable <StratumDiffResult> DiffStratumKeys(CruiseDatastore_V3 source, CruiseDatastore_V3 destination, string cruiseID) { var sourceAlias = "src"; destination.AttachDB(source, sourceAlias); try { return(destination.Query <StratumDiffResult>( $@"SELECT st1.StratumID, st1.StratumCode AS DestStratumCode, st2.StratumCode AS SrcStratumCode FROM main.Stratum AS st1 JOIN {sourceAlias}.Stratum AS st2 USING (StratumID) WHERE st1.CruiseID = @p1 AND st1.StratumCode != st2.StratumCode;", cruiseID).ToArray()); } finally { destination.DetachDB(sourceAlias); } }
public IEnumerable <CuttingUnitDiffResult> DiffCuttingUnitKeys(CruiseDatastore_V3 source, CruiseDatastore_V3 destination, string cruiseID) { var sourceAlias = "src"; destination.AttachDB(source, sourceAlias); try { return(destination.Query <CuttingUnitDiffResult>( $@"SELECT cu1.CuttingUnitID, cu1.CuttingUnitCode AS DestCuttingUnitCode, cu2.CuttingUnitCode AS SrcCuttingUnitCode FROM main.CuttingUnit AS cu1 JOIN {sourceAlias}.CuttingUnit AS cu2 USING (CuttingUnitID) WHERE cu1.CruiseID = @p1 AND cu1.CuttingUnitCode != cu2.CuttingUnitCode;", cruiseID).ToArray()); } finally { destination.DetachDB(sourceAlias); } }
public IEnumerable <PlotConflict> GetPlotConflicts(CruiseDatastore_V3 source, CruiseDatastore_V3 destination, string cruiseID) { var sourceAlias = "src"; destination.AttachDB(source, sourceAlias); try { return(destination.Query <PlotConflict>( $@"SELECT destP.CuttingUnitCode, destP.PlotNumber, srcP.PlotID AS SrcPlotID, destP.PlotID AS DestPlotID FROM main.Plot AS destP JOIN {sourceAlias}.Plot AS srcP USING (CuttingUnitCode, PlotNumber, CruiseID) WHERE destP.CruiseID = @p1 AND destP.PlotID != srcP.PlotID;", cruiseID).ToArray()); } finally { destination.DetachDB(sourceAlias); } }
public IEnumerable <CruiseConflict> GetCruiseConflicts(CruiseDatastore_V3 source, CruiseDatastore_V3 destination, string cruiseID) { var sourceAlias = "src"; destination.AttachDB(source, sourceAlias); try { return(destination.Query <CruiseConflict>( $@"SELECT srcCr.CruiseNumber, destCr.CruiseID AS DestCruiseID, srcCr.CruiseID AS SrcCruiseID FROM {sourceAlias}.Cruise AS srcCr JOIN main.Cruise AS destCr USING (CruiseNumber) WHERE srcCr.CruiseID =@p1 AND destCr.CruiseID != srcCr.CruiseID; ", cruiseID).ToArray()); } finally { destination.DetachDB(sourceAlias); } }
public IEnumerable <SubPopulationDiffResult> DiffSubPopulationKeys(CruiseDatastore_V3 source, CruiseDatastore_V3 destination, string cruiseID) { var sourceAlias = "src"; destination.AttachDB(source, sourceAlias); try { return(destination.Query <SubPopulationDiffResult>( $@"SELECT sp1.SubPopulationID, sp1.SpeciesCode AS DestSpeciesCode, sp2.SpeciesCode AS SrcSpeciesCode, sp1.LiveDead AS DestLiveDead, sp2.LiveDead AS SrcLiveDead FROM main.SubPopulation AS sp1 JOIN {sourceAlias}.SubPopulation AS sp2 USING (SubPopulationID) WHERE sp1.CruiseID = @p1 AND sp1.SpeciesCode != sp2.SpeciesCode OR sp1.LiveDead != sp2.LiveDead;", cruiseID).ToArray()); } finally { destination.DetachDB(sourceAlias); } }
public bool HasDesignKeyChanges(CruiseDatastore_V3 source, CruiseDatastore_V3 destination, string cruiseID) { var sourceAlias = "src"; destination.AttachDB(source, sourceAlias); try { var hasUnitChanges = destination.ExecuteScalar <int>( $@"SELECT count(*) FROM main.CuttingUnit AS cu1 JOIN {sourceAlias}.CuttingUnit AS cu2 USING (CuttingUnitID) WHERE cu1.CruiseID = @p1 AND cu1.CuttingUnitCode != cu2.CuttingUnitCode;", cruiseID) > 0; var hasStratumChanges = destination.ExecuteScalar <int>( $@"SELECT count(*) FROM main.Stratum as st1 JOIN {sourceAlias}.Stratum AS st2 USING (StratumID) WHERE st1.CruiseID = @p1 AND st1.StratumCode != st2.StratumCode;", cruiseID) > 0; var hasSampleGroupChanges = destination.ExecuteScalar <int>( $@"SELECT count(*) FROM main.SampleGroup AS sg1 JOIN {sourceAlias}.SampleGroup AS sg2 USING (SampleGroupID) WHERE sg1.CruiseID = @p1 AND sg1.StratumCode != sg2.StratumCode OR sg1.SampleGroupCode != sg2.SampleGroupCode;", cruiseID) > 0; var hasSubPopChanges = destination.ExecuteScalar <int>( $@"SELECT count(*) FROM main.SubPopulation AS sp1 JOIN {sourceAlias}.SubPopulation AS sp2 USING (SubPopulationID) WHERE sp1.CruiseID = @p1 AND sp1.SpeciesCode != sp2.SpeciesCode OR sp1.LiveDead != sp2.LiveDead;", cruiseID) > 0; return(hasUnitChanges || hasStratumChanges || hasStratumChanges || hasSubPopChanges); } finally { destination.DetachDB(sourceAlias); } }