public DatabaseRunHistory GetDatabaseHistoryDifference(ConnectionData goldenCopy, ConnectionData toBeUpdated) { var golden = GetDatabaseRunHistory(goldenCopy).BuildFileHistory.OrderByDescending(x => x.CommitDate); var toUpdate = GetDatabaseRunHistory(toBeUpdated).BuildFileHistory.OrderByDescending(x => x.CommitDate); //Get the most recent time that the two databases were in sync (ie had the same package run against it) var lastSyncDate = DateTime.MinValue; if (toUpdate.Any() && golden.Any()) { var matchingHistory = golden.Where(g => toUpdate.Any(t => t.BuildFileHash == g.BuildFileHash)); if (matchingHistory.Any()) { lastSyncDate = matchingHistory.Max(g => g.CommitDate); } } //Get the packages that are different and put them in chronological order for running... var unique = golden.Where(p => !toUpdate.Any(p2 => p2.BuildFileHash == p.BuildFileHash)) .OrderBy(x => x.CommitDate) .Where(g => g.CommitDate > lastSyncDate); DatabaseRunHistory uniqueHistory = new DatabaseRunHistory(); uniqueHistory.BuildFileHistory.AddRange(unique); return(uniqueHistory); }
/// <summary> /// This method will only be used if the target SQL server is an older version that does /// not have the FIRST_VALUE analytical command. This method will be slower as it makes multiple calls /// to the database to get the same information /// </summary> /// <param name="dbConnData"></param> /// <returns></returns> internal DatabaseRunHistory GetDatabaseRunHistoryOldSqlServer(ConnectionData dbConnData) { SqlConnection conn = SqlSync.Connection.ConnectionHelper.GetConnection(dbConnData); //Get the latest run of all the unique build hashes along with the build file name... string sql = @"SELECT BuildProjectHash, max(CommitDate) as CommitDate FROM SqlBuild_Logging WHERE BuildProjectHash <> '' AND BuildProjectHash IS NOT NULL GROUP BY BuildProjectHash ORDER BY CommitDate DESC"; DatabaseRunHistory history = new DatabaseRunHistory(); try { SqlCommand cmd = new SqlCommand(sql, conn); conn.Open(); using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (reader.Read()) { history.BuildFileHistory.Add(new BuildFileHistory() { BuildFileHash = reader["BuildProjectHash"].ToString(), CommitDate = (DateTime)reader["CommitDate"] }); } conn.Close(); } var dates = "'" + history.BuildFileHistory.Select(d => d.CommitDate.ToString("yyyy-MM-dd HH:mm:ss.FFF")) .Aggregate((root, add) => root + "','" + add) + "'"; string sql2 = String.Format( "SELECT DISTINCT BuildProjectHash, BuildFileName FROM SqlBuild_Logging WHERE CommitDate IN ({0})", dates); cmd = new SqlCommand(sql2, conn); conn.Open(); using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (reader.Read()) { history.BuildFileHistory.FirstOrDefault( h => h.BuildFileHash == reader["BuildProjectHash"].ToString()) .BuildFileName = reader["BuildFileName"].ToString(); } conn.Close(); } } catch (Exception exe) { log.LogError(exe, $"Unable to get build history for {dbConnData.SQLServerName}.{dbConnData.DatabaseName}"); } return(history); }
public Boolean SyncronizeDatabases(ConnectionData gold, ConnectionData toUpdate, bool continueOnFailure) { DatabaseDiffer diff = new DatabaseDiffer(); DatabaseRunHistory toBeRun = diff.GetDatabaseHistoryDifference(gold, toUpdate); PushInfo(string.Format("{0} database packages found to run on {1}.{2}", toBeRun.BuildFileHistory.Count, toUpdate.SQLServerName, toUpdate.DatabaseName)); if (toBeRun.BuildFileHistory.Count == 0) //already in sync { return(true); } //Make temp directory for rebuild packages... string tempPath = System.IO.Path.GetTempPath() + System.Guid.NewGuid(); Directory.CreateDirectory(tempPath); List <string> rebuiltPackages = new List <string>(); //Create SBM packages for each foreach (var buildFileHistory in toBeRun.BuildFileHistory) { PushInfo(string.Format("Rebuilding Package {0} (Hash:{1})", buildFileHistory.BuildFileName, buildFileHistory.BuildFileHash)); var fileName = tempPath + "\\" + Path.GetFileNameWithoutExtension(buildFileHistory.BuildFileName) + ".sbm"; //Make sure it creates and SBM and not an SBX var rebuildData = Rebuilder.RetreiveBuildData(gold, buildFileHistory.BuildFileHash, buildFileHistory.CommitDate); rebuildData.ForEach(h => h.ScriptFileName = Path.GetFileName(h.ScriptFileName)); //trim off the path, we just want the file name bool success = Rebuilder.RebuildBuildManagerFile(500, fileName, rebuildData); if (!success) { PushInfo(string.Format("Error creating package {0} (Hash:{1}) see error log for details.", buildFileHistory.BuildFileName, buildFileHistory.BuildFileHash)); ProcessDirectoryCleanup(tempPath); return(false); } rebuiltPackages.Add(fileName); } bool syncronized = ProcessSyncronizationPackages(rebuiltPackages, toUpdate, false, continueOnFailure); if (syncronized) { PushInfo(string.Format("Syncronized database {0}.{1} to source {2}.{3}", toUpdate.SQLServerName, toUpdate.DatabaseName, gold.SQLServerName, gold.DatabaseName)); } else { PushInfo(string.Format("Syncronize failed to {0}.{1} from source {2}.{3}. See log for details.", toUpdate.SQLServerName, toUpdate.DatabaseName, gold.SQLServerName, gold.DatabaseName)); } ProcessDirectoryCleanup(tempPath); return(syncronized); }
public DatabaseRunHistory GetDatabaseRunHistory(ConnectionData dbConnData) { SqlConnection conn = SqlSync.Connection.ConnectionHelper.GetConnection(dbConnData); //Get the latest run of all the unique build hashes along with the build file name... string sql = @"SELECT DISTINCT BuildProjectHash, FIRST_VALUE(CommitDate) OVER (PARTITION BY BuildProjectHash ORDER BY CommitDate DESC) as [CommitDate], FIRST_VALUE(BuildFileName) OVER (PARTITION BY BuildProjectHash ORDER BY CommitDate DESC) as [BuildFileName] FROM SqlBuild_Logging WHERE BuildProjectHash <> '' AND BuildProjectHash IS NOT NULL ORDER BY CommitDate DESC"; DatabaseRunHistory history = new DatabaseRunHistory(); try { SqlCommand cmd = new SqlCommand(sql, conn); conn.Open(); using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (reader.Read()) { history.BuildFileHistory.Add(new BuildFileHistory() { BuildFileHash = reader["BuildProjectHash"].ToString(), BuildFileName = reader["BuildFileName"].ToString(), CommitDate = (DateTime)reader["CommitDate"] }); } conn.Close(); } } catch (Exception exe) { if (exe.Message.IndexOf("FIRST_VALUE", 0, StringComparison.InvariantCultureIgnoreCase) > -1) { return(GetDatabaseRunHistoryOldSqlServer(dbConnData)); } log.LogError(exe, $"Unable to get build history for {dbConnData.SQLServerName}.{dbConnData.DatabaseName}"); } return(history); }