コード例 #1
0
        /// <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
        }
コード例 #2
0
        /// <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
        }