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