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