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