public string Install (string schemaGroup, SchemaObjectCollection objects, RebuildMode rebuildMode) { _scripts = new StringBuilder (); // validate the arguments if (schemaGroup == null) throw new ArgumentNullException ("schemaGroup"); if (objects == null) throw new ArgumentNullException ("objects"); // get the list of objects List<SchemaObject> schemaObjects = new List<SchemaObject> (objects); ValidateSchemaObjects (schemaObjects); for (int i = 0; i < objects.Count; i++) objects[i].OriginalOrder = i; // sort the list of objects in installation order schemaObjects.Sort (delegate (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; }); // the schema changes must be done in a transaction // since we don't pool the connection, we need to end the transaction before closing the connection try { using (TransactionScope transaction = new TransactionScope (TransactionScopeOption.Required, new TimeSpan (1, 0, 0, 0, 0))) { // open the connection OpenConnection (); // make sure we have a schema registry SchemaRegistry registry = new SchemaRegistry (_connection); // keep a list of all of the operations we need to perform List<string> dropObjects = new List<string> (); List<SchemaObject> addObjects = new List<SchemaObject> (); List<SchemaObject> tableUpdates = new List<SchemaObject> (); // look through all of the existing objects in the registry // create a delete instruction for all of the ones that should no longer be there foreach (string objectName in registry.GetObjectNames (schemaGroup)) { SchemaObject schemaObject = schemaObjects.Find (delegate (SchemaObject o) { return (o.Name.ToUpperInvariant() == objectName.ToUpperInvariant()); }); if (schemaObject == null) dropObjects.Add (objectName); } // sort to drop in reverse dependency order dropObjects.Sort (delegate (string o1, string o2) { int compare = -registry.GetObjectType (o1).CompareTo (registry.GetObjectType (o2)); if (compare == 0) compare = -registry.GetOriginalOrder(o1).CompareTo(registry.GetOriginalOrder(o2)); if (compare == 0) compare = -String.Compare(o1, o2, StringComparison.OrdinalIgnoreCase); return compare; }); // find out if we need to add anything foreach (SchemaObject schemaObject in schemaObjects) { // add any objects that aren't in the registry yet if (!registry.Contains (schemaObject.Name)) addObjects.Add (schemaObject); } // see if there are any drops or modifications bool hasChanges = dropObjects.Count != 0; if (!hasChanges) { foreach (SchemaObject schemaObject in schemaObjects) { if (registry.Contains (schemaObject.Name) && registry.GetSignature (schemaObject.Name) != schemaObject.Signature) { hasChanges = true; break; } } } // if there are changes, drop all of the easy items // drop and re-add all of the easy items if (hasChanges || (rebuildMode > RebuildMode.DetectChanges)) { for (int i = schemaObjects.Count - 1; i >= 0; i--) { SchemaObject schemaObject = schemaObjects [i]; if (registry.Contains (schemaObject.Name) && ( IsEasyToModify (schemaObject.SchemaObjectType) || (rebuildMode >= RebuildMode.RebuildSafe && CanRebuildSafely (schemaObject.SchemaObjectType)) || (rebuildMode >= RebuildMode.RebuildFull && CanRebuild (schemaObject.SchemaObjectType)) ) && !dropObjects.Contains (schemaObject.Name)) { dropObjects.Add (schemaObject.Name); addObjects.Add (schemaObject); } } } // drop and re-add everything else, using the scripting engine for (int i = schemaObjects.Count - 1; i >= 0; i--) { SchemaObject schemaObject = schemaObjects [i]; if (registry.Contains (schemaObject.Name) && registry.GetSignature (schemaObject.Name) != schemaObject.Signature && !IsEasyToModify (schemaObject.SchemaObjectType) && !dropObjects.Contains (schemaObject.Name)) ScheduleUpdate (dropObjects, addObjects, tableUpdates, schemaObject, true); } // sort to add in dependency order addObjects.Sort (delegate (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; }); // do the work DropObjects (registry, dropObjects, addObjects); UpdateTables (schemaGroup, registry, addObjects, tableUpdates); CreateObjects (schemaGroup, registry, addObjects); VerifyObjects (schemaObjects); // update the sigs on all of the records foreach (SchemaObject o in schemaObjects) registry.UpdateObject(o, schemaGroup); // commit the changes registry.Update (); transaction.Complete(); } } finally { _connection.Dispose (); } return _scripts.ToString (); }
/// <summary> /// Uninstall a schema group from the database. /// </summary> /// <remarks>This is a transactional operation</remarks> /// <param name="schemaGroup">The group to uninstall</param> /// <exception cref="ArgumentNullException">If schemaGroup is null</exception> /// <exception cref="SqlException">If any object fails to uninstall</exception> public void Uninstall (string schemaGroup) { // validate the arguments if (schemaGroup == null) throw new ArgumentNullException ("schemaGroup"); // the schema changes must be done in a transaction try { using (TransactionScope transaction = new TransactionScope ()) { // open the connection OpenConnection (); // make sure we have a schema registry SchemaRegistry registry = new SchemaRegistry (_connection); // sort the objects in drop order (reverse create order) List<string> names = registry.GetObjectNames (schemaGroup); names.Sort (delegate (string n1, string n2) { return -registry.GetObjectType (n1).CompareTo (registry.GetObjectType (n2)); }); // delete any objects that are in the specified schema group foreach (string objectName in names) { if (DroppingObject != null) DroppingObject (this, new SchemaEventArgs (SchemaEventType.BeforeDrop, objectName)); SchemaObjectType type = registry.GetObjectType (objectName); if (type == SchemaObjectType.Table) DropTableDepencencies(objectName, null, TableScriptOptions.IncludeTableModifiers, true); SchemaObject.Drop (this, _connection, type, objectName); registry.DeleteObject (objectName); } // commit the changes registry.Update (); transaction.Complete(); } } finally { _connection.Dispose (); } }
/// <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 (); } }