/// <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
}