public void SchemaRegistryIsCreatedAutomatically([ValueSource("ConnectionStrings")] string connectionString) { TestWithRollback(connectionString, connection => { // create the registry SchemaRegistry registry = new SchemaRegistry(connection, TestSchemaGroup); // make sure the table exists Assert.AreEqual(1, connection.ExecuteScalarSql<int>("SELECT COUNT(*) FROM sys.objects WHERE name = @Name", new Dictionary<string, object> () { { "Name", SchemaRegistry.SchemaRegistryTableName } })); // make sure the entries are empty Assert.AreEqual(0, registry.Entries.Count); // create another registry to make sure that it doesn't blow up registry = new SchemaRegistry(connection, TestSchemaGroup); }); }
public void SchemaRegistryCanAddNewRecords([ValueSource("ConnectionStrings")] string connectionString) { TestWithRollback(connectionString, connection => { // create the registry SchemaRegistry registry = new SchemaRegistry(connection, TestSchemaGroup); // make sure the entries are empty Assert.AreEqual(0, registry.Entries.Count); // add an entry and save it to the database registry.Entries.Add(new SchemaRegistryEntry() { SchemaGroup = "test", ObjectName = "Beer", Type = SchemaObjectType.Table, Signature = "1234", OriginalOrder = 1 }); registry.Commit(); // create another registry and make sure it loads the entries registry = new SchemaRegistry(connection, TestSchemaGroup); Assert.AreEqual(1, registry.Entries.Count); // add an second entry and save it to the database registry.Entries.Add(new SchemaRegistryEntry() { SchemaGroup = "test", ObjectName = "Beer2", Type = SchemaObjectType.Table, Signature = "1234", OriginalOrder = 2 }); registry.Commit(); // create another registry and make sure it loads the entries registry = new SchemaRegistry(connection, TestSchemaGroup); Assert.AreEqual(2, registry.Entries.Count); }); }
/// <summary> /// Determine if the given schema has differences with the current schema. /// </summary> /// <param name="schemaGroup">The schema group to compare.</param> /// <param name="schema">The schema to compare with.</param> /// <returns>True if there are any differences.</returns> public bool Diff(string schemaGroup, SchemaObjectCollection schema) { // validate the arguments if (schemaGroup == null) throw new ArgumentNullException("schemaGroup"); if (schema == null) throw new ArgumentNullException("schema"); SchemaRegistry registry = new SchemaRegistry(_connection, schemaGroup); // if any objects are missing from the registry, then there is a difference if (schema.Any(o => registry.Find(o.Name) == null)) return true; // if there are any registry entries missing from the new schema, there is a difference if (registry.Entries.Any(e => !schema.Any(o => String.Compare(e.ObjectName, o.Name, StringComparison.OrdinalIgnoreCase) == 0))) return true; // if there are any matches, but have different signatures, there is a difference if (schema.Any(o => registry.Find(o.Name).Signature != o.GetSignature(_connection, schema))) return true; // didn't detect differences return false; }
/// <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 (); } }
private void CreateObjects (string schemaGroup, SchemaRegistry registry, List<SchemaObject> addObjects) { // create objects foreach (SchemaObject schemaObject in addObjects) { if (CreatingObject != null) CreatingObject (this, new SchemaEventArgs (SchemaEventType.BeforeCreate, schemaObject)); schemaObject.Install(this); if (schemaObject.SchemaObjectType != SchemaObjectType.Script) registry.UpdateObject (schemaObject, schemaGroup); if (CreatedObject != null) CreatedObject (this, new SchemaEventArgs (SchemaEventType.AfterCreate, schemaObject)); } }
private void UpdateTables (string schemaGroup, SchemaRegistry registry, List<SchemaObject> addObjects, List<SchemaObject> tableUpdates) { foreach (SchemaObject schemaObject in tableUpdates) { if (UpdatingTable != null) UpdatingTable (this, new SchemaEventArgs (SchemaEventType.BeforeTableUpdate, schemaObject)); DropTableDepencencies(schemaObject.Name, addObjects, TableScriptOptions.IncludeTableModifiers | TableScriptOptions.AllXmlIndexes, true); // signature has changed, so update the object UpdateTable (_connection, schemaObject); registry.UpdateObject (schemaObject, schemaGroup); if (UpdatedTable != null) UpdatedTable (this, new SchemaEventArgs (SchemaEventType.AfterTableUpdate, schemaObject)); } }
private void DropObjects (SchemaRegistry registry, List<string> dropObjects, List<SchemaObject> addObjects) { // drop objects foreach (string objectName in dropObjects) { if (DroppingObject != null) DroppingObject (this, new SchemaEventArgs (SchemaEventType.BeforeDrop, objectName)); // drop any table dependencies, if any SchemaObjectType type = registry.GetObjectType (objectName); switch (type) { case SchemaObjectType.UserDefinedType: DropTypeDependencies(objectName, addObjects); break; case SchemaObjectType.View: DropViewDependencies (objectName, addObjects); break; case SchemaObjectType.Table: DropTableDepencencies(objectName, null, TableScriptOptions.IncludeTableModifiers | TableScriptOptions.AllXmlIndexes, true); break; case SchemaObjectType.PrimaryKey: DropTableDepencencies(SchemaObject.TableNameFromIndexName(objectName), addObjects, TableScriptOptions.AddAtEnd | TableScriptOptions.AllXmlIndexes, false); break; case SchemaObjectType.PrimaryXmlIndex: DropTableDepencencies(SchemaObject.TableNameFromIndexName(objectName), addObjects, TableScriptOptions.AddAtEnd | TableScriptOptions.SecondaryXmlIndexes, false); break; } SchemaObject.Drop (this, _connection, type, objectName); registry.DeleteObject (objectName); ResetScripter (); } }
public bool Diff (string schemaGroup, SchemaObjectCollection schemaObjects) { try { OpenConnection (); SchemaRegistry registry = new SchemaRegistry (_connection); // drop and re-add everything else, using the scripting engine foreach (SchemaObject schemaObject in schemaObjects) { // if the registry is missing the object, it's new, that's a diff if (!registry.Contains (schemaObject.Name)) return true; // if the signatures don't match, that's a diff if (registry.GetSignature (schemaObject.Name) != schemaObject.Signature) return true; } // 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.FirstOrDefault (delegate (SchemaObject o) { return (o.Name.ToUpperInvariant() == objectName.ToUpperInvariant()); }); if (schemaObject == null) return true; } // didn't detect differences return false; } finally { _connection.Dispose (); } }
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> /// 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 (); } }
public void SchemaRegistryShouldNotExecuteSchemaUpdateInRecordOnlyMode([ValueSource("ConnectionStrings")] string connectionString) { TestWithRollback(connectionString, connection => { // only script the data connection.ScriptOnly = true; // create the registry SchemaRegistry registry = new SchemaRegistry(connection, TestSchemaGroup); registry.Entries.Add(new SchemaRegistryEntry() { SchemaGroup = "test", ObjectName = "Beer", Type = SchemaObjectType.Table, Signature = "1234", OriginalOrder = 1 }); registry.Commit(); // we want to script the delete or insert into the registry, but not execute them in script mode Assert.IsTrue(connection.ScriptLog.ToString().Contains(String.Format("DELETE FROM [{0}]", SchemaRegistry.SchemaRegistryTableName))); Assert.IsTrue(connection.ScriptLog.ToString().Contains(String.Format("INSERT INTO [{0}]", SchemaRegistry.SchemaRegistryTableName))); Assert.IsFalse(connection.ExecutionLog.ToString().Contains(String.Format("DELETE FROM [{0}]", SchemaRegistry.SchemaRegistryTableName))); Assert.IsFalse(connection.ExecutionLog.ToString().Contains(String.Format("INSERT INTO [{0}]", SchemaRegistry.SchemaRegistryTableName))); }); }
public void SchemaRegistryShouldScriptCreateTableAndSelect([ValueSource("ConnectionStrings")] string connectionString) { TestWithRollback(connectionString, connection => { // create the registry SchemaRegistry registry = new SchemaRegistry(connection, TestSchemaGroup); // we want to script the create table for the registry // we also need to execute it all the time otherwise the rest of the process blows up Assert.IsTrue(connection.ScriptLog.ToString().Contains(String.Format("CREATE TABLE [{0}]", SchemaRegistry.SchemaRegistryTableName))); Assert.IsTrue(connection.ExecutionLog.ToString().Contains(String.Format("CREATE TABLE [{0}]", SchemaRegistry.SchemaRegistryTableName))); // we don't want to script the select of the registry Assert.IsFalse(connection.ScriptLog.ToString().Contains(String.Format("SELECT * FROM [{0}]", SchemaRegistry.SchemaRegistryTableName))); Assert.IsTrue(connection.ExecutionLog.ToString().Contains(String.Format("SELECT * FROM [{0}]", SchemaRegistry.SchemaRegistryTableName))); }); }