private IEnumerable<DbScriptToRun> GetScriptsToRun()
{
// get db versions
IEnumerable<string> versions =
_dbVersionProvider.GetVersions(_dbName, _sqlServerName);
var dbVersionsModel = new DbVersionsModel();
dbVersionsModel.AddDatabase(_environmentName, _dbName, versions);
// sort db versions
List<DbVersion> dbVersionsList =
dbVersionsModel.GetAllSortedDbVersions(_dbName)
.Select(DbVersion.FromString)
.ToList();
DbVersion currentDbVersion = dbVersionsList.LastOrDefault();
var dbVersionsSet = new HashSet<DbVersion>(dbVersionsList);
// collect scripts that weren't executed on database
string[] scriptFilePaths =
Directory.GetFiles(
_scriptsDirectoryPathProvider.Value,
"*.sql",
SearchOption.TopDirectoryOnly);
Dictionary<DbVersion, string> scriptsToRunDict =
(from filePath in scriptFilePaths
let dbVersion = DbVersion.FromString(Path.GetFileNameWithoutExtension(filePath))
where !dbVersionsSet.Contains(dbVersion)
select new { dbVersion, filePath })
.ToDictionary(x => x.dbVersion, x => x.filePath);
Dictionary<DbVersion, string> scriptsNewerThanCurrentVersion =
scriptsToRunDict
.Where(kvp => currentDbVersion == null || kvp.Key.IsGreatherThan(currentDbVersion))
.OrderBy(kvp => kvp.Key)
.Select(x => x)
.ToDictionary(x => x.Key, x => x.Value);
IEnumerable<DbVersion> scriptsToRunOlderThanCurrentVersion =
scriptsToRunDict.Keys.Except(scriptsNewerThanCurrentVersion.Keys)
.OrderBy(v => v);
foreach (DbVersion dbVersion in scriptsToRunOlderThanCurrentVersion)
{
if (!IsScriptSupported(dbVersion))
{
continue;
}
PostDiagnosticMessage(string.Format("This script should be run but it's older than the current version so we won't run it: '{0}'.", dbVersion), DiagnosticMessageType.Warn);
}
RemoveNotSupportedScripts(scriptsNewerThanCurrentVersion);
List<DbScriptToRun> scriptsToRun =
scriptsNewerThanCurrentVersion
.Select(x => new DbScriptToRun(x.Key, x.Value))
.ToList();
return scriptsToRun;
}
private void DoDiff()
{
// TODO IMM HI: refucktor
ThreadPool.QueueUserWorkItem(
state =>
{
try
{
ToggleIndeterminateProgress(true, pic_indeterminateProgress);
var selectedEnvironments = new List<EnvironmentInfo>();
for (int i = 0; i < dgv_environments.SelectedRows.Count; i++)
{
DataGridViewRow dataGridViewRow = dgv_environments.SelectedRows[i];
var dataBoundItem = (EnvironmentInfoRow)dataGridViewRow.DataBoundItem;
EnvironmentInfo environmentInfo = dataBoundItem.EnvironmentInfo;
selectedEnvironments.Add(environmentInfo);
}
if (selectedEnvironments.Count < 2)
{
throw new InternalException("At least 2 environments should've been selected.");
}
selectedEnvironments.Sort((ei1, ei2) => string.CompareOrdinal(ei1.Name, ei2.Name));
var dbVersionsModel = new DbVersionsModel();
foreach (EnvironmentInfo selectedEnvironment in selectedEnvironments)
{
string databaseServerMachineName = selectedEnvironment.DatabaseServerMachineName;
string connectionString = string.Format("Server={0};Integrated Security=SSPI", databaseServerMachineName);
using (var dbConnection = new SqlConnection(connectionString))
{
dbConnection.Open();
IEnumerable<dynamic> databases =
dbConnection.Query("select * from sys.databases");
foreach (dynamic database in databases)
{
string databaseName = database.name.ToUpper();
if (_IgnoredDatabaseNames.Contains(databaseName))
{
continue;
}
IEnumerable<dynamic> tables;
try
{
tables = dbConnection.Query(
string.Format(
"use [{0}]" + "\r\n" +
"select * from sys.tables",
databaseName));
}
catch (SqlException)
{
tables = new List<dynamic>();
}
IEnumerable<string> tableNames = tables.Select(t => ((string)t.name).ToUpper());
List<string> dbVersions;
// TODO IMM HI: parameterize
if (tableNames.Contains("VERSIONHISTORY") && TableContainsColumn(dbConnection, databaseName, "VERSIONHISTORY", "DBLabel"))
{
dbVersions = ObtainDbVersions(dbConnection, databaseName, "VERSIONHISTORY", "DBLabel");
}
else if (tableNames.Contains("VERSION") && TableContainsColumn(dbConnection, databaseName, "VERSION", "dbVersion"))
{
dbVersions = ObtainDbVersions(dbConnection, databaseName, "VERSION", "dbVersion");
}
else
{
dbVersions = new List<string>();
}
dbVersionsModel.AddDatabase(selectedEnvironment.Name, databaseName, dbVersions);
}
}
}
var databasesDataTable = new DataTable("Databases");
foreach (EnvironmentInfo selectedEnvironment in selectedEnvironments)
{
databasesDataTable.Columns.Add(selectedEnvironment.Name);
}
bool differencesOnly = cb_differencesOnly.Checked;
List<string> allSortedDatabaseNames = dbVersionsModel.GetAllSortedDatabaseNames().ToList();
var addedDatabaseNames = new List<string>();
foreach (string databaseName in allSortedDatabaseNames)
{
if (differencesOnly)
{
bool areDatabasesConsistent =
dbVersionsModel.AreDatabasesConsistentAcrossEnvironments(databaseName);
if (areDatabasesConsistent)
{
continue;
}
}
object[] values = new object[selectedEnvironments.Count];
int i = 0;
foreach (EnvironmentInfo selectedEnvironment in selectedEnvironments)
{
values[i++] = dbVersionsModel.IsDatabasePresentInEnvironment(selectedEnvironment.Name, databaseName) ? "V" : "X";
}
addedDatabaseNames.Add(databaseName);
databasesDataTable.Rows.Add(values);
}
GuiUtils.BeginInvoke(
this,
() =>
{
_currentDbVersionsModel = dbVersionsModel;
dgv_databasesInEnvironments.DataSource = databasesDataTable;
int i = 0;
foreach (string databaseName in addedDatabaseNames)
{
dgv_databasesInEnvironments.Rows[i++].HeaderCell.Value = databaseName;
}
dgv_databasesInEnvironments.RowHeadersWidth = dgv_databasesInEnvironments.Width / 2;
if (dgv_databasesInEnvironments.Rows.Count > 0)
{
// trigger SelectionChanged event
dgv_databasesInEnvironments.Rows[0].Selected = false;
dgv_databasesInEnvironments.Rows[0].Selected = true;
}
});
}
catch (Exception exc)
{
HandleThreadException(exc);
}
finally
{
ToggleIndeterminateProgress(false, pic_indeterminateProgress);
}
});
}
protected override void DoExecute()
{
// get db versions
var versions = _dbVersionProvider.GetVersions(_databaseName, _sqlServerName);
var dbVersionsModel = new DbVersionsModel();
dbVersionsModel.AddDatabase(_environmentName, _databaseName, versions);
// sort db versions
List<DbVersion> dbVersionsList =
dbVersionsModel.GetAllSortedDbVersions(_databaseName)
.Select(DbVersion.FromString)
.ToList();
DbVersion currentDbVersion = dbVersionsList.LastOrDefault();
var dbVersionsSet = new HashSet<DbVersion>(dbVersionsList);
// collect scripts that weren't executed on database
Dictionary<DbVersion, string> scriptsToRunDict =
(from filePath in Directory.GetFiles(_scriptsDirectoryPath, "*.sql")
let dbVersion = DbVersion.FromString(Path.GetFileNameWithoutExtension(filePath))
where !dbVersionsSet.Contains(dbVersion)
select new { dbVersion, filePath })
.ToDictionary(unknown => unknown.dbVersion, unknown => unknown.filePath);
var scriptsNewerThanCurrentVersion =
scriptsToRunDict
.Where(kvp => currentDbVersion == null || kvp.Key.IsGreatherThan(currentDbVersion))
.OrderBy(kvp => kvp.Key)
.Select(x => x)
.ToDictionary(x => x.Key, x => x.Value);
RemoveNotSupportedScripts(scriptsNewerThanCurrentVersion);
_scriptsToRun =
scriptsNewerThanCurrentVersion
.Select(x => x.Value)
.ToList();
}
