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);
}