internal ConvertTableEventArgs (SchemaEventType eventType, SchemaObject table, SqlConnection connection, string beforeTable, string afterTable) :
base (eventType, table)
{
_connection = connection;
_beforeTableName = beforeTable;
_afterTableName = afterTable;
}
/// <summary>
/// Schedule an update by adding the appropriate delete, update and add records
/// </summary>
/// <param name="context">The installation context.</param>
/// <param name="schemaObject">The object to update.</param>
private void ScriptUpdate(InstallContext context, SchemaObject schemaObject)
{
// if we are dropping and readding an object, then make sure we put the object in the drop list at the right time
if (schemaObject.OriginalOrder == 0)
{
// if the object matches an existing schema object (probably), then find it and copy its installation order
var originalObject = context.SchemaObjects.Find(o => schemaObject.SchemaObjectType == o.SchemaObjectType && String.Compare(schemaObject.Name, o.Name, StringComparison.OrdinalIgnoreCase) == 0);
if (originalObject != null)
schemaObject.OriginalOrder = originalObject.OriginalOrder;
else if (context.AddObjects.Any())
schemaObject.OriginalOrder = context.AddObjects.Max(o => o.OriginalOrder) + 1;
else
schemaObject.OriginalOrder = 1;
}
// if we have already scripted this object, then don't do it again
if (context.AddObjects.Any(o => o.Name == schemaObject.Name))
return;
// if this is a table, then let's see if we can just modify the table
if (schemaObject.SchemaObjectType == SchemaObjectType.Table)
{
ScriptStandardDependencies(context, schemaObject);
ScriptTableUpdate(context, schemaObject);
return;
}
// add the object to the add queue before anything that depends on it, as well as any permissions on the object
context.AddObjects.Add(schemaObject);
// don't log any of our scripting
ScriptPermissions(context, schemaObject);
ScriptStandardDependencies(context, schemaObject);
// handle dependencies for different types of objects
if (schemaObject.SchemaObjectType == SchemaObjectType.IndexedView)
{
ScriptIndexes(context, schemaObject);
}
else if (schemaObject.SchemaObjectType == SchemaObjectType.PrimaryKey)
{
ScriptForeignKeys(context, schemaObject);
ScriptXmlIndexes(context, schemaObject);
}
else if (schemaObject.SchemaObjectType == SchemaObjectType.PrimaryXmlIndex)
{
ScriptXmlIndexes(context, schemaObject);
}
else if (schemaObject.SchemaObjectType == SchemaObjectType.Index)
{
ScriptIndexes(context, schemaObject);
}
// drop the object after any dependencies are dropped
SchemaRegistryEntry dropEntry = context.SchemaRegistry.Find(schemaObject.Name);
if (dropEntry == null)
{
dropEntry = new SchemaRegistryEntry()
{
Type = schemaObject.SchemaObjectType,
ObjectName = schemaObject.Name
};
}
context.DropObjects.Add(dropEntry);
}
/// <summary>
/// Compares two schema objects to determine the appropriate installation order.
/// </summary>
/// <param name="o1">The first object to compare.</param>
/// <param name="o2">The second object to compere.</param>
/// <returns>The comparison result.</returns>
private static int CompareByInstallOrder(SchemaObject o1, SchemaObject o2)
{
int compare = o1.SchemaObjectType.CompareTo(o2.SchemaObjectType);
if (compare == 0)
compare = o1.OriginalOrder.CompareTo(o2.OriginalOrder);
if (compare == 0)
compare = String.Compare(o1.Name, o2.Name, StringComparison.OrdinalIgnoreCase);
return compare;
}
private void ScriptXmlIndexes(InstallContext context, SchemaObject schemaObject)
{
IList<FastExpando> xmlIndexes;
if (schemaObject.SchemaObjectType == SchemaObjectType.PrimaryXmlIndex)
{
// find any secondary indexes dependent upon the primary index
xmlIndexes = _connection.QuerySql(@"
IF NOT EXISTS (SELECT * FROM sys.system_objects WHERE name = 'xml_indexes') SELECT TOP 0 Nothing=NULL ELSE
SELECT ObjectID=i.object_id, IndexID=i.index_id,
Name=QUOTENAME(i.name),
TableName=QUOTENAME(OBJECT_SCHEMA_NAME(o.object_id)) + '.' + QUOTENAME(OBJECT_NAME(o.object_id)),
SecondaryType=i.secondary_type_desc, ParentIndexName=QUOTENAME(@ObjectName)
FROM sys.xml_indexes i
JOIN sys.objects o ON (i.object_id = o.object_id)
JOIN sys.xml_indexes p ON (p.index_id = i.using_xml_index_id)
WHERE p.name = @ObjectName",
new Dictionary<string, object>() { { "ObjectName", schemaObject.SqlName.Object } });
}
else
{
// for tables and primary keys, look for primary xml indexes
xmlIndexes = _connection.QuerySql(@"
IF NOT EXISTS (SELECT * FROM sys.system_objects WHERE name = 'xml_indexes') SELECT TOP 0 Nothing=NULL ELSE
SELECT ObjectID=i.object_id, IndexID=i.index_id,
Name=QUOTENAME(i.name),
TableName=QUOTENAME(OBJECT_SCHEMA_NAME(o.object_id)) + '.' + QUOTENAME(OBJECT_NAME(o.object_id)),
SecondaryType=i.secondary_type_desc, ParentIndexName=u.name
FROM sys.xml_indexes i
JOIN sys.objects o ON (i.object_id = o.object_id)
LEFT JOIN sys.xml_indexes u ON (i.using_xml_index_id = u.index_id)
WHERE i.object_id = OBJECT_ID(@ObjectName)",
new Dictionary<string, object>() { { "ObjectName", schemaObject.SqlName.FullName } });
}
foreach (dynamic xmlIndex in xmlIndexes)
{
// get the columns in the key from the database
var columns = _connection.QuerySql(@"
SELECT ColumnName=QUOTENAME(c.name)
FROM sys.xml_indexes i
JOIN sys.index_columns ic ON (i.object_id = ic.object_id AND i.index_id = ic.index_id)
JOIN sys.columns c ON (ic.object_id = c.object_id AND ic.column_id = c.column_id)
WHERE i.object_id = @ObjectID AND i.index_id = @IndexID",
new Dictionary<string, object>()
{
{ "ObjectID", xmlIndex.ObjectID },
{ "IndexID", xmlIndex.IndexID }
});
StringBuilder sb = new StringBuilder();
sb.AppendFormat("CREATE {0}XML INDEX {1} ON {2} (",
(xmlIndex.ParentIndexName == null) ? "PRIMARY " : "",
xmlIndex.Name,
xmlIndex.TableName);
sb.Append(String.Join(",", columns.Select((dynamic c) => SqlParser.FormatSqlName(c.ColumnName))));
sb.Append(")");
if (xmlIndex.SecondaryType != null)
{
sb.AppendFormat(" USING XML INDEX {0} FOR ", xmlIndex.ParentIndexName);
sb.Append(xmlIndex.SecondaryType);
}
var dropObject = new SchemaObject(sb.ToString());
ScriptUpdate(context, dropObject);
}
}
internal SchemaEventArgs (SchemaEventType eventType, SchemaObject schemaObject)
{
_eventType = eventType;
_schemaObject = schemaObject;
_objectName = schemaObject.Name;
}
/// <summary>
/// Finds an entry by name
/// </summary>
/// <param name="objectName">The name of the schema object.</param>
/// <returns>The schema entry or null if it can't be found.</returns>
public SchemaRegistryEntry Find(SchemaObject schemaObject)
{
return(Find(schemaObject.Name));
}
private void ScriptForeignKeys(InstallContext context, SchemaObject schemaObject)
{
IList<FastExpando> foreignKeys = null;
if (schemaObject.SchemaObjectType == SchemaObjectType.PrimaryKey)
{
foreignKeys = _connection.QuerySql(@"
SELECT ObjectID=f.object_id, Name=f.name,
TableName=QUOTENAME(OBJECT_SCHEMA_NAME(o.object_id)) + '.' + QUOTENAME(OBJECT_NAME(o.object_id)),
RefTableName=QUOTENAME(OBJECT_SCHEMA_NAME(ro.object_id)) + '.' + QUOTENAME(OBJECT_NAME(ro.object_id)),
DeleteAction=delete_referential_action_desc, UpdateAction=update_referential_action_desc
FROM sys.foreign_keys f
JOIN sys.key_constraints k ON (f.referenced_object_id = k.parent_object_id)
JOIN sys.objects o ON (f.parent_object_id = o.object_id)
JOIN sys.objects ro ON (k.parent_object_id = ro.object_id)
WHERE k.parent_object_id = OBJECT_ID(@ObjectID)",
new Dictionary<string, object>() { { "ObjectID", schemaObject.SqlName.SchemaQualifiedTable } });
}
foreach (dynamic foreignKey in foreignKeys)
{
// get the columns in the key from the database
var columns = _connection.QuerySql(@"
SELECT FkColumnName=fc.name, PkColumnName=kc.name
FROM sys.foreign_key_columns f
JOIN sys.columns fc ON (f.parent_object_id = fc.object_id AND f.parent_column_id = fc.column_id)
JOIN sys.columns kc ON (f.referenced_object_id = kc.object_id AND f.referenced_column_id = kc.column_id)
WHERE f.constraint_object_id = @KeyID",
new Dictionary<string, object>() { { "KeyID", foreignKey.ObjectID } });
StringBuilder sb = new StringBuilder();
sb.AppendFormat("ALTER TABLE {0} ADD CONSTRAINT {1} FOREIGN KEY (",
SqlParser.FormatSqlName(foreignKey.TableName),
SqlParser.FormatSqlName(foreignKey.Name));
sb.Append(String.Join(",", columns.Select((dynamic c) => SqlParser.FormatSqlName(c.FkColumnName))));
sb.AppendFormat(") REFERENCES {0} (", SqlParser.FormatSqlName(foreignKey.RefTableName));
sb.Append(String.Join(",", columns.Select((dynamic c) => SqlParser.FormatSqlName(c.PkColumnName))));
sb.AppendFormat(") ON DELETE {0} ON UPDATE {1}", foreignKey.DeleteAction.Replace("_", " "), foreignKey.UpdateAction.Replace("_", " "));
var dropObject = new SchemaObject(sb.ToString());
ScriptUpdate(context, dropObject);
}
}
/// <summary>
/// Script the permissions on an object and save the script to add the permissions back later
/// </summary>
/// <param name="context">The installation context.</param>
/// <param name="schemaObject">The object to drop</param>
private void ScriptPermissions(InstallContext context, SchemaObject schemaObject)
{
IList<FastExpando> permissions = null;
if (schemaObject.SchemaObjectType == SchemaObjectType.Role)
{
// get the current permissions on the object
permissions = _connection.QuerySql(@"SELECT UserName=u.name, Permission=p.permission_name, ClassType=p.class_desc, ObjectName=ISNULL(o.name, t.name)
FROM sys.database_principals u
JOIN sys.database_permissions p ON (u.principal_id = p.grantee_principal_id)
LEFT JOIN sys.objects o ON (p.class_desc = 'OBJECT_OR_COLUMN' AND p.major_id = o.object_id)
LEFT JOIN sys.types t ON (p.class_desc = 'TYPE' AND p.major_id = t.user_type_id)
WHERE u.name = @ObjectName",
new Dictionary<string, object>() { { "ObjectName", schemaObject.SqlName.Object } });
}
else if (schemaObject.SchemaObjectType == SchemaObjectType.AutoProc)
{
// handle permissions for autoprocs
foreach (var proc in new AutoProc(schemaObject.Name, new SqlColumnDefinitionProvider(_connection), context.SchemaObjects).GetProcs())
{
ScriptPermissions(context, new SchemaObject(SchemaObjectType.StoredProcedure, proc.Item2, ""));
}
return;
}
else if (schemaObject.SchemaObjectType == SchemaObjectType.UserDefinedType)
{
// get the current permissions on the object
permissions = _connection.QuerySql(@"SELECT UserName=u.name, Permission=p.permission_name, ClassType=p.class_desc, ObjectName=QUOTENAME(t.name)
FROM sys.database_principals u
JOIN sys.database_permissions p ON (u.principal_id = p.grantee_principal_id)
JOIN sys.types t ON (p.class_desc = 'TYPE' AND p.major_id = t.user_type_id)
WHERE t.name = @ObjectName",
new Dictionary<string, object>() { { "ObjectName", schemaObject.SqlName.Object } });
}
else
{
// get the current permissions on the object
permissions = _connection.QuerySql(@"SELECT UserName=u.name, Permission=p.permission_name, ClassType=p.class_desc,
ObjectName=QUOTENAME(OBJECT_SCHEMA_NAME(o.object_id)) + '.' + QUOTENAME(OBJECT_NAME(o.object_id))
FROM sys.database_principals u
JOIN sys.database_permissions p ON (u.principal_id = p.grantee_principal_id)
JOIN sys.objects o ON (p.class_desc = 'OBJECT_OR_COLUMN' AND p.major_id = o.object_id)
WHERE o.object_id = OBJECT_ID(@ObjectName)",
new Dictionary<string, object>() { { "ObjectName", schemaObject.SqlName.FullName } });
}
// create a new permission schema object to install for each existing permission
foreach (dynamic permission in permissions)
context.AddObjects.Add(new SchemaObject(String.Format("GRANT {0} ON {1}{2} TO {3} -- DEPENDENCY", permission.Permission, permission.ClassType == "TYPE" ? "TYPE::" : "", SqlParser.FormatSqlName(permission.ObjectName), SqlParser.FormatSqlName(permission.UserName))));
}
/// <summary>
/// Drop an object and generate the script to re-add it
/// </summary>
/// <param name="urn">The object to drop</param>
/// <param name="addObjects">The list of addObjects, or null to not re-add dependencies</param>
/// <param name="options">Options for scripting</param>
private void DropAndReAdd (Urn urn, List<SchemaObject> addObjects, TableScriptOptions options)
{
// generate the script to readd
if (addObjects != null)
{
_scripter.Options.ScriptDrops = false;
_scripter.Options.IncludeIfNotExists = true;
string addScript = GenerateScript (urn);
// unnamed primary keys and constraints need to not be auto-added, since they must be built into the table
if ((options & TableScriptOptions.ScriptAnonymousConstraints) != 0)
addScript = _anonymousReferenceRegex.Replace (addScript, "IF 0=1 $0");
else
{
addScript = _anonymousRegex.Replace (addScript, "IF 0=1 $0");
addScript = _anonymousDefaultRegex.Replace (addScript, "IF 0=1 $0");
}
// if the database has autostatistics, then skip all statistics
addScript = _statisticsRegex.Replace (addScript, "");
// create triggers must be the first statement in the batch
int pos = addScript.IndexOf("CREATE TRIGGER", StringComparison.OrdinalIgnoreCase);
if (pos >= 0)
addScript = addScript.Substring (pos);
// remove primary xml indexes if we don't need them
if ((options & TableScriptOptions.PrimaryXmlIndexes) == 0)
addScript = _primaryXmlIndex.Replace (addScript, "IF 0=1 $0");
if (addScript.Length > 0)
{
SchemaObject newObject = new SchemaObject (SchemaObjectType.Script, "Scripted Dependencies", addScript);
if ((options & TableScriptOptions.AddAtEnd) != 0)
addObjects.Add (newObject);
else
addObjects.Insert (0, newObject);
}
}
// script the drop of everything
_scripter.Options.ScriptDrops = true;
_scripter.Options.IncludeIfNotExists = true;
string dropScript = GenerateScript (urn);
// the scripter should not be scripting the table drop, so we have to comment it out
// note that the !PrimaryObject option above works for the create script
SqlSmoObject smo = _scripter.Server.GetSmoObject (urn);
Table table = smo as Table;
if (table != null)
dropScript = _dropTableRegex.Replace (dropScript, "SELECT 1");
// smo switches to master, so switch back to our db
_connection.ChangeDatabase (_databaseName);
if (!String.IsNullOrWhiteSpace(dropScript))
ExecuteNonQuery (dropScript);
ResetScripter ();
}
/// <summary>
/// Update a table object
/// </summary>
/// <param name="connection">The SqlConnection to use</param>
/// <remarks>This creates a copy of the table data, updates the table, then copies the data back into the new table.</remarks>
private void UpdateTable (SqlConnection connection, SchemaObject schemaObject)
{
// copy the table to a temp table and drop the old table
// NOTE: sp_rename can't be used because we're in a distributed transaction
SqlCommand command = new SqlCommand ();
command.Connection = connection;
//command.CommandText = String.Format (CultureInfo.InvariantCulture, "SELECT * INTO {0} FROM {1}; DROP TABLE {1}", TempTableName, schemaObject.Name);
command.CommandText = String.Format (CultureInfo.InvariantCulture, "sp_rename '{1}', '{0}'", TempTableName, schemaObject.Name);
command.CommandTimeout = 0;
command.ExecuteNonQuery ();
// create the new table using the script provided
schemaObject.Install(this);
if (!DoConvertTable (schemaObject, TempTableName, schemaObject.Name))
{
// get the columns for the two tables
command.CommandText = String.Format (CultureInfo.InvariantCulture, @"
select c.*, type_name = t.name from sys.columns c join sys.types t on (c.system_type_id = t.system_type_id and c.user_type_id = t.user_type_id) where object_id = object_id ('{0}');
select c.*, type_name = t.name from sys.columns c join sys.types t on (c.system_type_id = t.system_type_id and c.user_type_id = t.user_type_id) where object_id = object_id ('{1}');
", TempTableName, schemaObject.UnformattedName);
SqlDataAdapter adapter = new SqlDataAdapter ();
adapter.SelectCommand = command;
DataSet dataset = new DataSet ();
dataset.Locale = CultureInfo.InvariantCulture;
adapter.Fill (dataset);
// find all of the fields that match from the old table to the new table
// for these fields, we'll preserve the data
StringBuilder newFields = new StringBuilder ();
StringBuilder oldFields = new StringBuilder ();
foreach (DataRow newRow in dataset.Tables[1].Rows)
{
string columnName = newRow["name"].ToString ();
// don't map over timestamps and computed columns, they get done automatically
if (newRow["type_name"].ToString () == "timestamp")
continue;
if (Convert.ToInt32 (newRow ["is_computed"], CultureInfo.InvariantCulture) == 1)
continue;
// find a matching oldRow
DataRow[] oldRows = dataset.Tables[0].Select (String.Format (CultureInfo.InvariantCulture, "name = '{0}'", columnName));
// map old fields to new fields
if (oldRows.Length == 1)
{
if (newFields.Length > 0) newFields.Append (",");
newFields.AppendFormat ("[{0}]", columnName);
if (oldFields.Length > 0) oldFields.Append (",");
oldFields.AppendFormat ("[{0}]", columnName);
}
}
// copy the data into the new table
command.CommandText = String.Format (CultureInfo.InvariantCulture,
@" IF OBJECTPROPERTY (OBJECT_ID('{0}'), 'TableHasIdentity') = 1
SET IDENTITY_INSERT {0} ON;
INSERT INTO {0} ({1}) SELECT {2} FROM {3};
IF OBJECTPROPERTY (OBJECT_ID('{0}'), 'TableHasIdentity') = 1
SET IDENTITY_INSERT {0} OFF",
schemaObject.Name, newFields, oldFields, TempTableName);
command.ExecuteNonQuery ();
}
// drop the temp table
command.CommandText = String.Format (CultureInfo.InvariantCulture, "DROP TABLE {0}", TempTableName);
command.ExecuteNonQuery ();
}
private bool DoConvertTable (SchemaObject table, string beforeTable, string afterTable)
{
if (OnConvertTable != null)
{
ConvertTableEventArgs ce = new ConvertTableEventArgs (SchemaEventType.ConvertTable, table, _connection, beforeTable, afterTable);
OnConvertTable (this, ce);
return ce.Converted;
}
return false;
}
/// <summary>
/// Schedule an update by adding the appropriate delete, update and add records
/// </summary>
/// <param name="dropObjects">The list of tdrops</param>
/// <param name="addObjects">The list of adds</param>
/// <param name="tableUpdates">The list of table updates</param>
/// <param name="schemaObject">The object to update</param>
private void ScheduleUpdate (List<string> dropObjects, List<SchemaObject> addObjects, List<SchemaObject> tableUpdates, SchemaObject schemaObject, bool handleDependencies)
{
// if the object is a table, we need to update it
if (schemaObject.SchemaObjectType == SchemaObjectType.Table)
tableUpdates.Add (schemaObject);
else
{
// not a table, so add a drop and insert
// put the add in before scripting the permissions so the permissions execute after the add
addObjects.Add (schemaObject);
if (handleDependencies)
{
switch (schemaObject.SchemaObjectType)
{
case SchemaObjectType.StoredProcedure:
StoredProcedure sp = _database.StoredProcedures [schemaObject.UnformattedName];
if (sp != null)
ScriptPermissions (sp.Urn, addObjects);
break;
case SchemaObjectType.Function:
UserDefinedFunction udf = _database.UserDefinedFunctions [schemaObject.UnformattedName];
if (udf != null)
ScriptPermissions (udf.Urn, addObjects);
break;
case SchemaObjectType.View:
View view = _database.Views [schemaObject.UnformattedName];
if (view != null)
ScriptPermissions (view.Urn, addObjects);
break;
}
}
// insert at the beginning so the higher level objects get dropped before their dependencies
dropObjects.Add (schemaObject.Name);
}
}
/// <summary>
/// Imports an existing database into the schema registry
/// </summary>
/// <param name="schemaGroup">The name of the schema group to script to</param>
public void Import (string schemaGroup)
{
using (TransactionScope transaction = new TransactionScope (TransactionScopeOption.Required, new TimeSpan ()))
{
// open the connection
OpenConnection ();
// make sure we have a schema registry
SchemaRegistry registry = new SchemaRegistry (_connection);
// get all of the objects in the current database
_command.CommandText = @"
SELECT o.name, o.type, p.name
FROM sys.objects o
LEFT JOIN sys.objects p ON (o.parent_object_id = p.object_id)
LEFT JOIN sys.default_constraints df ON (o.object_id = df.object_id)
WHERE o.is_ms_shipped = 0
-- don't import anonymous defaults
AND (df.is_system_named IS NULL OR df.is_system_named = 0)
AND o.Name NOT LIKE '%Insight_SchemaRegistry%'
UNION
select i.name, 'IX', o.name
FROM sys.indexes i
JOIN sys.objects o ON (i.object_id = o.object_id)
WHERE o.is_ms_shipped = 0 AND i.type_desc <> 'HEAP' and is_primary_key = 0 and is_unique_constraint = 0";
using (SqlDataReader reader = _command.ExecuteReader ())
{
while (reader.Read ())
{
SchemaObjectType type;
string name = String.Format(CultureInfo.InvariantCulture, "[{0}]", reader.GetString(0));
string sqlType = reader.GetString (1);
switch (sqlType.Trim())
{
case "U":
type = SchemaObjectType.Table;
break;
case "P":
type = SchemaObjectType.StoredProcedure;
break;
case "V":
type = SchemaObjectType.View;
break;
case "FN":
case "TF":
type = SchemaObjectType.Function;
break;
case "D":
case "UQ":
case "C":
type = SchemaObjectType.Constraint;
name = String.Format(CultureInfo.InvariantCulture, "[{0}].[{1}]", reader.GetString(2), reader.GetString(0));
break;
case "PK":
type = SchemaObjectType.PrimaryKey;
name = String.Format(CultureInfo.InvariantCulture, "[{0}].[{1}]", reader.GetString(2), reader.GetString(0));
break;
case "F":
type = SchemaObjectType.ForeignKey;
name = String.Format(CultureInfo.InvariantCulture, "[{0}].[{1}]", reader.GetString(2), reader.GetString(0));
break;
case "IX":
type = SchemaObjectType.Index;
name = String.Format(CultureInfo.InvariantCulture, "[{0}].[{1}]", reader.GetString(2), reader.GetString(0));
break;
case "SQ":
// query notification, skip
continue;
default:
throw new InvalidOperationException(String.Format(CultureInfo.InvariantCulture, "Cannot import object {0} of type {1}", name, sqlType));
}
SchemaObject schemaObject = new SchemaObject (type, name, "");
registry.UpdateObject (schemaObject, schemaGroup);
}
}
registry.Update ();
transaction.Complete ();
}
}
internal SchemaEventArgs(SchemaEventType eventType, SchemaObject schemaObject)
{
_eventType = eventType;
_schemaObject = schemaObject;
_objectName = schemaObject.Name;
}
/// <summary>
/// Script the update of a table.
/// </summary>
/// <param name="context">The installation context.</param>
/// <param name="schemaObject">The object to update.</param>
private void ScriptTableUpdate(InstallContext context, SchemaObject schemaObject)
{
string oldTableName = schemaObject.SqlName.FullName;
string newTableName = InsightTemp + DateTime.Now.Ticks.ToString(CultureInfo.InvariantCulture);
try
{
// make a temporary table so we can analyze the difference
// note that we rename the table and its constraints so that we don't have conflicts when creating it
string tempTable = schemaObject.Sql;
tempTable = createTableRegex.Replace(tempTable, "CREATE TABLE " + newTableName);
tempTable = constraintRegex.Replace(tempTable, match => "CONSTRAINT " + SqlParser.FormatSqlName(InsightTemp + SqlParser.UnformatSqlName(match.Groups[1].Value)));
_connection.ExecuteSql(tempTable);
// detect if the table was created on a different data space and throw
var oldDataSpace = _connection.ExecuteScalarSql<int>("SELECT data_space_id FROM sys.indexes i WHERE i.object_id = OBJECT_ID(@Name) AND type <= 1", new Dictionary<string, object> { { "Name", oldTableName } });
var newDataSpace = _connection.ExecuteScalarSql<int>("SELECT data_space_id FROM sys.indexes i WHERE i.object_id = OBJECT_ID(@Name) AND type <= 1", new Dictionary<string, object> { { "Name", newTableName } });
if (oldDataSpace != newDataSpace)
throw new SchemaException(String.Format(CultureInfo.InvariantCulture, "Cannot move table {0} to another filegroup or partition", oldTableName));
// update the columns and constraints
ScriptColumnsAndConstraints(context, schemaObject, oldTableName, newTableName);
}
finally
{
try
{
// clean up the temporary table
_connection.ExecuteSql("DROP TABLE " + newTableName);
}
catch (SqlException)
{
// eat this and throw the original error
}
}
}
/// <summary>
/// Add or update an object in the schema registry
/// </summary>
/// <param name="schemaObject">The object to update</param>
/// <param name="schemaGroup">The name of the schema group</param>
public void UpdateObject (SchemaObject schemaObject, string schemaGroup)
{
DeleteObject (schemaObject.Name);
RegistryTable.Rows.Add (new object[] { schemaGroup, schemaObject.Name, schemaObject.Signature, schemaObject.SchemaObjectType.ToString (), schemaObject.OriginalOrder });
}
private void ScriptColumnsAndConstraints(InstallContext context, SchemaObject schemaObject, string oldTableName, string newTableName)
{
#region Detect Column Changes
Func<dynamic, dynamic, bool> compareColumns = (dynamic c1, dynamic c2) => (String.Compare(c1.Name, c2.Name, StringComparison.OrdinalIgnoreCase) == 0);
Func<dynamic, dynamic, bool> areColumnsEqual = (dynamic c1, dynamic c2) =>
c1.TypeName == c2.TypeName &&
c1.MaxLength == c2.MaxLength &&
c1.Precision == c2.Precision &&
c1.Scale == c2.Scale &&
c1.IsNullable == c2.IsNullable &&
c1.IsIdentity == c2.IsIdentity &&
c1.IdentitySeed == c2.IdentitySeed &&
c1.IdentityIncrement == c2.IdentityIncrement &&
c1.Definition == c2.Definition
;
Func<dynamic, dynamic, bool> areDefaultsEqual = (dynamic c1, dynamic c2) =>
((String.Compare(c1.DefaultName, c2.DefaultName, StringComparison.OrdinalIgnoreCase) == 0) || (c1.DefaultIsSystemNamed == true && c2.DefaultIsSystemNamed == true)) &&
c1.DefaultDefinition == c2.DefaultDefinition
;
Func<dynamic, string> getConstraintName = (dynamic c) => new SqlName(oldTableName, 2).Append(c.Name).FullName;
// get the columns for each of the tables
var oldColumns = GetColumnsForTable(oldTableName);
var newColumns = GetColumnsForTable(newTableName);
// if we are planning on dropping the constraint on a column, then clear it from the old column definition
foreach (dynamic oldColumn in oldColumns.Where(c => context.DropObjects.Any(d => String.Compare(d.ObjectName, getConstraintName(c), StringComparison.OrdinalIgnoreCase) == 0)))
{
oldColumn.DefaultName = null;
oldColumn.DefaultIsSystemNamed = false;
}
// calculate which columns changed
var missingColumns = oldColumns.Except(newColumns, compareColumns).ToList();
var addColumns = newColumns.Except(oldColumns, compareColumns).ToList();
var changedColumns = newColumns.Where((dynamic cc) =>
{
dynamic oldColumn = oldColumns.FirstOrDefault(oc => compareColumns(cc, oc));
return (oldColumn != null) && (!areColumnsEqual(oldColumn, cc) || !areDefaultsEqual(oldColumn, cc));
}).ToList();
#endregion
#region Change Columns
// if we want to modify a computed column, we have to drop/add it
var changedComputedColumns = changedColumns.Where(c => c.Definition != null).ToList();
foreach (var cc in changedComputedColumns)
{
missingColumns.Add(cc);
addColumns.Add(cc);
changedColumns.Remove(cc);
}
// delete old columns - this should be pretty free
if (missingColumns.Any())
{
// if the column has a default, drop it
foreach (dynamic oldColumn in missingColumns.Where(c => c.DefaultName != null))
{
// script the default drop
context.DropObjects.Add(new SchemaRegistryEntry() { Type = SchemaObjectType.Default, ObjectName = SqlParser.FormatSqlName(oldTableName, oldColumn.Name) });
}
// script the column drop
StringBuilder sb = new StringBuilder();
sb.AppendFormat("ALTER TABLE {0}", oldTableName);
sb.Append(" DROP");
sb.AppendLine(String.Join(",", missingColumns.Select((dynamic o) => String.Format(" COLUMN {0}", SqlParser.FormatSqlName(o.Name)))));
context.AddObjects.Add(new SchemaObject(SchemaObjectType.Table, oldTableName, sb.ToString()));
}
// add new columns - this is free when the columns are nullable and possibly with a default
if (addColumns.Any())
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("ALTER TABLE {0}", oldTableName);
sb.Append(" ADD ");
sb.AppendLine(String.Join(", ", addColumns.Select((dynamic o) => GetColumnDefinition(o) + GetDefaultDefinition(o))));
context.AddObjects.Add(new SchemaObject(SchemaObjectType.Table, oldTableName, sb.ToString()));
}
// alter columns - either the definition or the default
foreach (dynamic column in changedColumns)
{
// find any indexes that are on that column
ScriptIndexes(context, schemaObject, column.Name);
// find the old column
dynamic oldColumn = oldColumns.First(oc => compareColumns(column, oc));
// if the columns aren't equal then alter the column
if (!areColumnsEqual(column, oldColumn))
{
StringBuilder sb = new StringBuilder();
// if the old column is nullable and the new one is not, and there is a default, then convert the data
if (oldColumn.IsNullable && !column.IsNullable && column.DefaultName != null)
{
string defaultDefinition = column.DefaultDefinition.Substring(2, column.DefaultDefinition.Length - 4);
sb.AppendFormat("UPDATE {0} SET {1} = 2 WHERE {1} IS NULL\n", oldTableName, column.Name, defaultDefinition);
}
// alter the column
sb.AppendFormat("ALTER TABLE {0} ALTER COLUMN ", oldTableName);
sb.AppendFormat(GetColumnDefinition(column));
context.AddObjects.Add(new SchemaObject(SchemaObjectType.Table, oldTableName, sb.ToString()));
}
// modify the defaults
if (!areDefaultsEqual(column, oldColumn))
{
StringBuilder sb = new StringBuilder();
// delete the old default if it exists but it's not in the registry
if (oldColumn.DefaultName != null && !context.SchemaRegistry.Contains(getConstraintName(oldColumn)))
{
// script the default drop
sb.AppendFormat("ALTER TABLE {0} DROP CONSTRAINT {1}\nGO\n", oldTableName, SqlParser.FormatSqlName(oldColumn.DefaultName));
}
// add the new default if we want one
if (column.DefaultName != null)
{
// script the add
sb.AppendFormat("ALTER TABLE {0} ADD ", oldTableName);
sb.AppendFormat(GetDefaultDefinition(column));
sb.AppendFormat(" FOR {0}", SqlParser.FormatSqlName(column.Name));
}
context.AddObjects.Add(new SchemaObject(SchemaObjectType.Table, oldTableName, sb.ToString()));
}
}
#endregion
}
/// <summary>
/// Determine if the database already contains a given object
/// </summary>
/// <param name="schemaObject">The schema object to look for</param>
/// <returns>True if the object is in the registry, false otherwise</returns>
public bool Contains(SchemaObject schemaObject)
{
return Find(schemaObject) != null;
}
/// <summary>
/// Script the standard dependencies such as stored procs and triggers.
/// </summary>
/// <param name="context">The installation context.</param>
/// <param name="schemaObject">The schemaObject to script.</param>
private void ScriptStandardDependencies(InstallContext context, SchemaObject schemaObject)
{
// can only script dependencies for objects with names
if (schemaObject.SqlName.SchemaQualifiedObject == null)
return;
// find all of the dependencies on the object
// this will find things that use views or tables
// note that there will be more than one dependency if more than one column is referenced
// ignore USER_TABLE, since that is calculated columns
// for CHECK_CONSTRAINTS, ignore system-named constraints, since they are part of the table and will be handled there
var dependencies = _connection.QuerySql(@"
SELECT DISTINCT
Name = QUOTENAME(OBJECT_SCHEMA_NAME(o.object_id)) + '.' + QUOTENAME(OBJECT_NAME(o.object_id)),
SqlType = o.type_desc, IsSchemaBound=d.is_schema_bound_reference
FROM sys.sql_expression_dependencies d
JOIN sys.objects o ON (d.referencing_id = o.object_id)
LEFT JOIN sys.check_constraints c ON (o.object_id = c.object_id)
WHERE ISNULL(c.is_system_named, 0) = 0 AND
o.type_desc <> 'USER_TABLE' AND
(o.parent_object_id = OBJECT_ID(@QualifiedName) OR
d.referenced_id =
CASE WHEN d.referenced_class_desc = 'TYPE' THEN
(SELECT user_type_id
FROM sys.types t JOIN sys.schemas s ON (t.schema_id = s.schema_id)
WHERE s.name = @SchemaName AND t.name = @ObjectName)
ELSE OBJECT_ID(@QualifiedName)
END)",
new Dictionary<string, object>()
{
{ "QualifiedName", schemaObject.SqlName.SchemaQualifiedObject },
{ "SchemaName", schemaObject.SqlName.Schema },
{ "ObjectName", schemaObject.SqlName.Object },
});
foreach (dynamic dependency in dependencies)
{
// we only have to update schemabound dependencies
if (schemaObject.SchemaObjectType == SchemaObjectType.Table && !dependency.IsSchemaBound)
continue;
// since the object isn't already being dropped, create a new SchemaObject for it and rebuild that
SchemaObject dropObject = null;
string dependencyType = dependency.SqlType;
string dependencyName = dependency.Name;
switch (dependencyType)
{
case "SQL_STORED_PROCEDURE":
case "SQL_SCALAR_FUNCTION":
case "SQL_TABLE_VALUED_FUNCTION":
case "SQL_TRIGGER":
case "VIEW":
// these objects can be rebuilt from the definition of the object in the database
dropObject = new SchemaObject(_connection.ExecuteScalarSql<string>("SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID(@Name)", new Dictionary<string, object>() { { "Name", dependencyName } }));
break;
case "CHECK_CONSTRAINT":
// need to do a little work to re-create the check constraint
dynamic checkConstraint = _connection.QuerySql(@"
SELECT TableName=QUOTENAME(OBJECT_SCHEMA_NAME(o.object_id)) + '.' + QUOTENAME(OBJECT_NAME(o.object_id)),
ConstraintName=c.name, Definition=c.definition
FROM sys.check_constraints c
JOIN sys.objects o ON (c.parent_object_id = o.object_id) WHERE c.object_id = OBJECT_ID(@Name)",
new Dictionary<string, object>() { { "Name", dependencyName } }).First();
dropObject = new SchemaObject(String.Format(
"ALTER TABLE {0} ADD CONSTRAINT {1} CHECK {2}",
SqlParser.FormatSqlName(checkConstraint.TableName),
SqlParser.FormatSqlName(checkConstraint.ConstraintName),
checkConstraint.Definition));
break;
default:
throw new InvalidOperationException(String.Format(CultureInfo.InvariantCulture, "Cannot generate dependencies for object {0}.", dependencyName));
}
ScriptUpdate(context, dropObject);
}
}
/// <summary>
/// Finds an entry by name
/// </summary>
/// <param name="objectName">The name of the schema object.</param>
/// <returns>The schema entry or null if it can't be found.</returns>
public SchemaRegistryEntry Find(SchemaObject schemaObject)
{
return Find(schemaObject.Name);
}
private void ScriptIndexes(InstallContext context, SchemaObject schemaObject, string columnName = null)
{
// get the indexes and constraints on a table
// NOTE: we don't script system named indexes because we assume they are specified as part of the table definition
// NOTE: order by type: do the clustered indexes first because they also drop nonclustered indexes if the object is a view (not a table)
// generate some sql to determine the proper index
string sql = @"SELECT ObjectID=i.object_id, IndexID=i.index_id,
Name=QUOTENAME(i.name),
TableName=QUOTENAME(OBJECT_SCHEMA_NAME(o.object_id)) + '.' + QUOTENAME(OBJECT_NAME(o.object_id)),
Type=i.type_desc, IsUnique=i.is_unique, IsConstraint=CONVERT(bit, CASE WHEN k.object_id IS NOT NULL THEN 1 ELSE 0 END), IsPrimaryKey=CONVERT(bit, CASE WHEN k.type_desc = 'PRIMARY_KEY_CONSTRAINT' THEN 1 ELSE 0 END), DataSpace=";
sql += context.IsAzure ? "NULL" : "ISNULL(f.name, p.name)";
// get the data for the dependent indexes
if (schemaObject.SchemaObjectType == SchemaObjectType.Index)
{
sql += @" FROM sys.indexes currentindex
JOIN sys.indexes i ON (currentindex.object_id = i.object_id AND currentindex.index_id <> i.index_id)
JOIN sys.objects o ON (i.object_id = o.object_id)
LEFT JOIN sys.key_constraints k ON (o.object_id = k.parent_object_id AND i.index_id = k.unique_index_id AND is_system_named = 0)";
}
else
{
sql += @" FROM sys.indexes i
JOIN sys.objects o ON (i.object_id = o.object_id)
LEFT JOIN sys.key_constraints k ON (o.object_id = k.parent_object_id AND i.index_id = k.unique_index_id AND is_system_named = 0)";
}
// SQL Server may put the index on different filegroups
if (!context.IsAzure)
{
sql += @" LEFT JOIN sys.partition_schemes p ON (i.data_space_id = p.data_space_id)
LEFT JOIN sys.filegroups f ON (i.data_space_id = f.data_space_id)";
}
// add the where clause
if (schemaObject.SchemaObjectType == SchemaObjectType.Index)
{
sql += @" WHERE currentindex.object_id = OBJECT_ID(@ObjectName) AND currentIndex.type_desc = 'CLUSTERED' AND i.name IS NOT NULL";
}
else
{
sql += @" WHERE o.object_id = OBJECT_ID(@ObjectName) AND i.Name IS NOT NULL";
}
// filter by column if appropriate
if (columnName != null)
{
sql += @" AND i.index_id IN (SELECT index_id
FROM sys.index_columns ic
JOIN sys.columns c ON (c.object_id = ic.object_id AND c.column_id = ic.column_id)
WHERE ic.object_id = OBJECT_ID(@ObjectName) AND c.name = @ColumnName)";
}
sql += @" ORDER BY Type";
// find the indexes on the table
var indexes = _connection.QuerySql(
sql,
new Dictionary<string, object>()
{
{ "ObjectName", schemaObject.SqlName.FullName },
{ "ColumnName", columnName }
});
foreach (dynamic index in indexes)
{
// get the columns in the key from the database
var columns = _connection.QuerySql(@"
SELECT ColumnName=c.name
FROM sys.indexes i
JOIN sys.index_columns ic ON (i.object_id = ic.object_id AND i.index_id = ic.index_id)
JOIN sys.columns c ON (ic.object_id = c.object_id AND ic.column_id = c.column_id)
WHERE i.object_id = @ObjectID AND i.index_id = @IndexID",
new Dictionary<string, object>()
{
{ "ObjectID", index.ObjectID },
{ "IndexID", index.IndexID },
});
StringBuilder sb = new StringBuilder();
if (index.IsConstraint)
{
sb.AppendFormat("ALTER TABLE {3} ADD CONSTRAINT {2} {0}{1} (",
index.IsPrimaryKey ? "PRIMARY KEY " : index.IsUnique ? "UNIQUE " : "",
index.Type,
index.Name,
index.TableName);
}
else
{
sb.AppendFormat("CREATE {0}{1} INDEX {2} ON {3} (",
index.IsUnique ? "UNIQUE " : "",
index.Type,
index.Name,
index.TableName);
}
sb.Append(String.Join(",", columns.Select((dynamic c) => SqlParser.FormatSqlName(c.ColumnName))));
sb.Append(")");
// if the index is on another filegroup or partition scheme, add that
if (index.DataSpace != null)
{
sb.AppendFormat(" ON {0}", SqlParser.FormatSqlName(index.DataSpace));
// get the partition columns (this will be empty for non-partitioned indexes)
if (!context.IsAzure)
{
var partitionColumns = _connection.QuerySql(@"
SELECT ColumnName=c.name
FROM sys.index_columns ic
JOIN sys.columns c ON (ic.object_id = c.object_id AND ic.column_id = c.column_id)
JOIN sys.indexes i ON (i.object_id = ic.object_id AND i.index_id = ic.index_id)
WHERE i.object_id = @ObjectID AND i.index_id = @IndexID AND ic.partition_ordinal <> 0
ORDER BY ic.partition_ordinal",
new Dictionary<string, object>()
{
{ "ObjectID", index.ObjectID },
{ "IndexID", index.IndexID }
});
if (partitionColumns.Any())
{
sb.Append("(");
sb.Append(String.Join(",", partitionColumns.Select((dynamic p) => p.ColumnName)));
sb.Append(")");
}
}
}
var dropObject = new SchemaObject(sb.ToString());
ScriptUpdate(context, dropObject);
}
}
/// <summary>
/// Determine if the database already contains a given object
/// </summary>
/// <param name="schemaObject">The schema object to look for</param>
/// <returns>True if the object is in the registry, false otherwise</returns>
public bool Contains(SchemaObject schemaObject)
{
return(Find(schemaObject) != null);
}
