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