/// <summary> /// Collect lists for drop/create/alter of tables, indexes, columns, triggers, foreignKeys and checks /// </summary> /// <param name="dropItems">Drop itmes list</param> /// <param name="createItems">Create itmes list</param> /// <param name="alterColumns">Alter itmes list</param> private void CollectTableItems( ICollection <MySmoObjectBase> dropItems, ICollection <MySmoObjectBase> createItems, ICollection <MySmoObjectBase> alterColumns) { // // We could not add table/column items to the items2GenerateScript right here. // E.g. because to drop column we first need drop all associated indexes, so // we use additional collections and will add table/column items after cycle. // #region Definition the collection of items var dropIndexItems = new List <MySmoObjectBase>(); var dropTableItems = new List <MySmoObjectBase>(); var dropColumnItems = new List <MySmoObjectBase>(); var dropForeignKeyItems = new List <MySmoObjectBase>(); var dropTriggerItems = new List <MySmoObjectBase>(); var createIndexItems = new List <MySmoObjectBase>(); var createTableItems = new List <MySmoObjectBase>(); var createColumnItems = new List <MySmoObjectBase>(); var createForeignKeyItems = new List <MySmoObjectBase>(); var createTriggerItems = new List <MySmoObjectBase>(); #endregion #region Add new tables to create objects list foreach (Table sourceTable in mSourceDatabase.Tables) { string sourceTableName = sourceTable.Name; if (sourceTable.IsSystemObject == false && !mProductionDatabase.Tables.Contains(sourceTableName)) { Trace.TraceInformation(string.Format("Database={0} Table={1} Collecting new tables...\r\n", mSourceDatabase.Name, sourceTable.Name)); var productionTable = new Table( mProductionDatabase, sourceTable.Name, sourceTable.Schema); mProductionDatabase.Tables.Add(productionTable); createTableItems.Add(new MySmoObjectBase( productionTable, productionTable.Name, productionTable.Parent.Name)); if (Database.IsSplitTable(sourceTableName)) { throw new Exception( string.Format( "New split table {0} added to the upgrade database or dropped in the production database. Upgrade utility does not support adding new template tables", sourceTableName)); } } } #endregion foreach (Table productionTable in mProductionDatabase.Tables) { // For new table IsSystemObject property is not set bool isSystemObject = false; try { isSystemObject = productionTable.IsSystemObject; } catch (Microsoft.SqlServer.Management.Smo.PropertyNotSetException) { } if (isSystemObject == false) { // // If its splittable then use original name to check if table exist or // to iterate through columns. // if (!string.IsNullOrEmpty(Database.GetOriginalTableNameByTemplateTableName(productionTable.Name))) { continue; } if (mSourceDatabase.Tables.Contains(productionTable.Name)) { var myProductionTable = new MyTable(productionTable); var mySourceTable = new MyTable(mSourceDatabase.Tables[productionTable.Name]); #region Collect Dinamical Items Trace.TraceInformation(string.Format("Database={0} Table={1} Collecting items for triggers...\r\n", mProductionDatabase.Name, productionTable.Name)); DifferentSplitItems( createTriggerItems, dropTriggerItems, null, myProductionTable.MyTriggers, mySourceTable.MyTriggers, productionTable); Trace.TraceInformation(string.Format("Database={0} Table={1} Collecting items for indexes...\r\n", mProductionDatabase.Name, productionTable.Name)); DifferentSplitItems( createIndexItems, dropIndexItems, null, myProductionTable.MyIndexes, mySourceTable.MyIndexes, productionTable); Trace.TraceInformation(string.Format("Database={0} Table={1} Collecting items for foreign keys...\r\n", mProductionDatabase.Name, productionTable.Name)); DifferentSplitItems( createForeignKeyItems, dropForeignKeyItems, null, myProductionTable.MyForeignKeys, mySourceTable.MyForeignKeys, productionTable); Trace.TraceInformation(string.Format("Database={0} Table={1} Collecting items for columns...\r\n", mProductionDatabase.Name, productionTable.Name)); DifferentSplitItems( createColumnItems, dropColumnItems, alterColumns, myProductionTable.MyColumns, mySourceTable.MyColumns, productionTable); #endregion } else { // // Table does not exists, we've to drop it. // dropTableItems.Add(new MySmoObjectBase( productionTable, productionTable.Name, productionTable.Parent.Name)); } } } #region Find and recreate all indexes, whitch have changed columns foreach (MySmoObjectBase alterColumn in alterColumns) { var column = (Column)alterColumn.SourceSmoObject; var columnParent = (Table)column.Parent; foreach (Index index in columnParent.Indexes) { if (index.IndexedColumns.Contains(column.Name)) { MySmoObjectBase foundIndex = new MyIndex(index, index.Name, columnParent.Name); bool isContains = false; foreach (MySmoObjectBase smoObject in dropIndexItems) { if (foundIndex.IsEqual(smoObject)) { isContains = true; break; } } if (!isContains) { dropForeignKeyItems.Add(foundIndex); createForeignKeyItems.Add(foundIndex); } } // if (index.IndexedColumns.Contains(column.Name)) } // foreach (Index index in columnParent.Indexes) } // foreach (MySmoObjectBase alterColumn in alterColumns) #endregion #region Find and recreate all FK's, whitch point to deleted PK's or UK's foreach (MySmoObjectBase dropIndexItem in dropIndexItems) { var index = (Index)dropIndexItem.SourceSmoObject; if (index.IndexKeyType == IndexKeyType.DriPrimaryKey || (index.IndexKeyType == IndexKeyType.DriUniqueKey)) { foreach (Table table in mProductionDatabase.Tables) { foreach (ForeignKey fk in table.ForeignKeys) { if (fk.ReferencedTable == ((Table)index.Parent).Name && fk.ReferencedKey == index.Name) { MySmoObjectBase foundFK = new MyIndex(fk, fk.Name, table.Name); bool isContains = false; foreach (MySmoObjectBase smoObject in dropForeignKeyItems) { if (foundFK.IsEqual(smoObject)) { isContains = true; break; } } if (!isContains) { dropForeignKeyItems.Add(foundFK); createForeignKeyItems.Add(foundFK); } } } // foreach (ForeignKey fk in table.ForeignKeys) } // foreach (Table table in _productionDatabase.Tables) } // if (index.IndexKeyType == IndexKeyType.DriPrimaryKey || (index.IndexKeyType == IndexKeyType.DriUniqueKey)) } // foreach (MySmoObjectBase dropIndexItem in dropIndexItems) #endregion #region Add table items to lists foreach (MySmoObjectBase dropForeignKeyItem in dropForeignKeyItems) { dropItems.Add(dropForeignKeyItem); } foreach (MySmoObjectBase dropIndexItem in dropIndexItems) { dropItems.Add(dropIndexItem); } foreach (MySmoObjectBase dropTriggerItem in dropTriggerItems) { dropItems.Add(dropTriggerItem); } foreach (MySmoObjectBase dropColumnItem in dropColumnItems) { dropItems.Add(dropColumnItem); } foreach (MySmoObjectBase dropTableItem in dropTableItems) { dropItems.Add(dropTableItem); } foreach (MySmoObjectBase createTableItem in createTableItems) { createItems.Add(createTableItem); } foreach (MySmoObjectBase createColumnItem in createColumnItems) { createItems.Add(createColumnItem); } foreach (MySmoObjectBase createTriggerItem in createTriggerItems) { createItems.Add(createTriggerItem); } foreach (MySmoObjectBase createIndexItem in createIndexItems) { createItems.Add(createIndexItem); } foreach (MySmoObjectBase createForeignKeyItem in createForeignKeyItems) { createItems.Add(createForeignKeyItem); } #endregion }
/// <summary> /// Collect items from table: drop tables, create/drop triggers, create/drop checks, /// create/drop foreignKeys, create/drop/alter indexes and create/drop/alter columns /// </summary> /// <param name="createItems">List of items for creating</param> /// <param name="dropItems">List of items for deleting</param> /// <param name="alterItems">List of items for altering (not null only for column)</param> /// <param name="productionCollection">Collection of objects from production database</param> /// <param name="sourceCollection">Collection of objects from sourse database</param> /// <param name="productionTable">Production database (null for stored procedure)</param> private void DifferentSplitItems( ICollection <MySmoObjectBase> createItems, ICollection <MySmoObjectBase> dropItems, ICollection <MySmoObjectBase> alterItems, Dictionary <string, MySmoObjectBase> productionCollection, Dictionary <string, MySmoObjectBase> sourceCollection, Table productionTable) { #region Find object from production collection in source collection foreach (string productionObjectName in productionCollection.Keys) { MySmoObjectBase productionObject = productionCollection[productionObjectName]; // // if productionObject is split stored procedure - move to next object // if (productionTable == null && !string.IsNullOrEmpty( mDatabaseEngine.GetOriginalProcedureNameByTemplateProcedureName(productionObject.Name))) { continue; } bool isNeedDrop = true; if (sourceCollection.ContainsKey(productionObjectName)) { MySmoObjectBase sourceObject = sourceCollection[productionObjectName]; // // Compare these objects (own compare for every object) // if (sourceObject.IsEqual(productionObject)) { continue; } // // if our object is column - add it into alter collection // otherwise - into create and drop collection // if (alterItems != null) { var newMySmoObject = new MySmoObjectBase(sourceObject.CreateSplitItem(productionTable, 0, null)) { MProductionSmoObject = productionObject.SourceSmoObject }; alterItems.Add(newMySmoObject); isNeedDrop = false; } else { createItems.Add( productionTable != null ? new MySmoObjectBase(sourceObject.CreateSplitItem(productionTable, 0, null)) : new MySmoObjectBase(sourceObject.CreateSplitItem(this.mProductionDatabase, 0, null))); } #region Cycle for all split objects. We will create new split versions of object, if it have split versions if ((productionTable != null && Database.IsSplitTable(productionTable.Name)) || Database.IsSplitProcedure(sourceObject.Name)) { foreach (int surveyId in mDatabaseEngine.SurveyIds) { MySmoObjectBase splitObject; var splitTable = new Table(); if (productionTable != null) { // // Get split table // string splitTableName = Database.GetTemplateTableNameByOriginalTableName( productionTable.Name, surveyId); splitTable = mProductionDatabase.Tables[splitTableName]; // // Create split object on this table // splitObject = sourceObject.CreateSplitItem( splitTable, surveyId, mDatabaseEngine); } else { // // Create split stored procedure // splitObject = sourceObject.CreateSplitItem(mProductionDatabase, surveyId, mDatabaseEngine); } // // if our object is column - add it into alter collection // otherwise - into create collection // if (alterItems != null) { // // Find old version of this object in production database // var myTable = new MyTable(splitTable); Dictionary <string, MySmoObjectBase> templateCollection = myTable.GetMyObjects(splitObject); splitObject.MProductionSmoObject = templateCollection[splitObject.Name].SourceSmoObject; alterItems.Add(new MySmoObjectBase(splitObject)); } else { createItems.Add(new MySmoObjectBase(splitObject)); } } } #endregion } #region Add object and its split versions to drop collection if (isNeedDrop) { dropItems.Add(new MySmoObjectBase(productionObject)); // Add split objects to drop collection, if this table (stored procedure) // have split versions if ((productionTable != null && Database.IsSplitTable(productionTable.Name)) || Database.IsSplitProcedure(productionObject.Name)) { foreach (int surveyId in mDatabaseEngine.SurveyIds) { if (productionTable != null) { // // Get object collection from each split table // string splitTableName = Database.GetTemplateTableNameByOriginalTableName( productionTable.Name, surveyId); var myTable = new MyTable(mProductionDatabase.Tables[splitTableName]); Dictionary <string, MySmoObjectBase> templateCollection = myTable.GetMyObjects(productionObject); string splitObjectName = productionObject.Name; if (productionObject.SourceSmoObject.GetType() != typeof(Column)) { splitObjectName = Database.GetTemplateNameByOriginalName(productionObject.Name, surveyId); } // // Find our object from this collection and add it into drop collection // MySmoObjectBase splitObject = templateCollection[splitObjectName]; dropItems.Add(new MySmoObjectBase(splitObject)); } else { StoredProcedure splitProcedure = mProductionDatabase.StoredProcedures[Database.GetTemplateNameByOriginalName(productionObject.Name, surveyId)]; // If splitProcedure is (added just now) new procedure - // getting IsSystemObject parameter will throw exception try { if (!splitProcedure.IsSystemObject) { dropItems.Add(new MySmoObjectBase( splitProcedure, splitProcedure.Name, mProductionDatabase.Name)); } } catch (Microsoft.SqlServer.Management.Smo.PropertyNotSetException) { break; } } } } } #endregion } #endregion #region Add new objects from source database foreach (string sourceObjectName in sourceCollection.Keys) { MySmoObjectBase sourceObject = sourceCollection[sourceObjectName]; // // If source database contains new object - add it into create collection // if (!productionCollection.ContainsKey(sourceObjectName)) { if (productionTable != null) { var newObject = new MySmoObjectBase(sourceObject.CreateSplitItem(productionTable, 0, null)); // If column added to new table, we wount create new column separate, // it will create with table creating if (newObject.SourceSmoObject != null) { createItems.Add(newObject); } } else { createItems.Add(new MySmoObjectBase(sourceObject.CreateSplitItem(mProductionDatabase, 0, null))); } // // Add split objects, if this table (stored procedure) have split version // if ((productionTable != null && Database.IsSplitTable(productionTable.Name)) || Database.IsSplitProcedure(sourceObject.Name)) { foreach (int surveyId in mDatabaseEngine.SurveyIds) { MySmoObjectBase splitObject; if (productionTable != null) { string splitTableName = Database.GetTemplateTableNameByOriginalTableName( productionTable.Name, surveyId); // Create split object for split table splitObject = sourceObject.CreateSplitItem( mProductionDatabase.Tables[splitTableName], surveyId, mDatabaseEngine); } else { // Create split stored procedure splitObject = sourceObject.CreateSplitItem(mProductionDatabase, surveyId, mDatabaseEngine); } createItems.Add(new MySmoObjectBase(splitObject)); } } } } #endregion }