public IEnumerable<string> GenerateUpdateSql(DatabaseInventory inventory, int currentVersion)
{
if (inventory == null) {
throw new ArgumentNullException("inventory");
}
SetQualification(inventory.SchemaName);
inventory.SetQualification(inventory.SchemaName);
try {
DependencyResolver resolver = new DependencyResolver();
List<IInstallStatement> alterUsingUpdateScript = new List<IInstallStatement>();
Dictionary<string, IScriptableStatement> dropStatements = new Dictionary<string, IScriptableStatement>(StringComparer.OrdinalIgnoreCase);
HashSet<string> newObjectNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
HashSet<string> refreshObjectNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
refreshObjectNames.UnionWith(inventory.Objects.OfType<CreateViewStatement>().Where(v => !(v.ViewOption is OptionSchemabindingToken)).Select(v => v.ObjectName));
// refreshObjectNames.UnionWith(inventory.Objects.OfType<CreateFunctionStatement>().Where(f => !(f.Option is OptionSchemabindingToken)).Select(f => f.ObjectName));
foreach (KeyValuePair<IAlterableCreateStatement, InventoryObjectDifference> pair in Compare(inventory, this, inventory.TargetEngine)) {
switch (pair.Value) {
case InventoryObjectDifference.None:
resolver.AddExistingObject(pair.Key.ObjectName);
break;
case InventoryObjectDifference.Different:
if (pair.Key.AlterUsingUpdateScript) {
alterUsingUpdateScript.Add(pair.Key);
} else {
AlterTableAddConstraintFragment alterConstraint = pair.Key as AlterTableAddConstraintFragment;
if (alterConstraint != null) {
dropStatements.Add(pair.Key.ObjectName, pair.Key.CreateDropStatement());
resolver.Add(pair.Key);
} else {
if (pair.Key.DisableUsagesForUpdate) {
Debug.Assert(!(pair.Key is CreateTableFragment)); // we must not wrap those - but they shouldn't return true for this flag
resolver.Add(new DependencyDisablingAlterStatement(pair.Key.CreateAlterStatement()));
} else {
resolver.Add(pair.Key.CreateAlterStatement());
}
}
refreshObjectNames.Remove(pair.Key.ObjectName);
}
break;
case InventoryObjectDifference.SourceOnly:
dropStatements.Add(pair.Key.ObjectName, pair.Key.CreateDropStatement());
refreshObjectNames.Remove(pair.Key.ObjectName);
break;
case InventoryObjectDifference.TargetOnly:
resolver.Add(pair.Key);
if (pair.Key.IsPartOfSchemaDefinition) {
newObjectNames.Add(pair.Key.ObjectName);
}
break;
}
}
StringBuilder builder = new StringBuilder(4096);
// first drop table constraints and indices (if any)
foreach (AlterTableDropConstraintStatement dropStatement in dropStatements.Values.OfType<AlterTableDropConstraintStatement>()) {
yield return WriteStatement(dropStatement, builder, inventory.TargetEngine);
}
foreach (DropIndexStatement dropStatement in dropStatements.Values.OfType<DropIndexStatement>()) {
yield return WriteStatement(dropStatement, builder, inventory.TargetEngine);
}
// now perform all possible actions which do not rely on tables which are altered
HashSet<string> createdTables = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
foreach (IInstallStatement statement in resolver.GetInOrder(false)) {
CreateTableFragment createTable = statement as CreateTableFragment;
if (createTable != null) {
yield return WriteStatement(createTable.Owner.CreateStatementFragments(CreateFragmentMode.CreateOnExistingSchema).OfType<CreateTableFragment>().Single(), builder, inventory.TargetEngine);
createdTables.Add(createTable.ObjectName);
} else if (!statement.IsTableUniqueConstraintOfTables(createdTables)) {
foreach (IInstallStatement innerStatement in HandleDependendObjects(statement, inventory, dropStatements.Keys)) {
yield return WriteStatement(innerStatement, builder, inventory.TargetEngine);
}
}
}
// then perform updates (if any)
HashSet<string> droppedTables = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
foreach (KeyValuePair<int, IScriptableStatement[]> update in updateStatements.Where(u => u.Key > currentVersion)) {
foreach (IScriptableStatement statement in update.Value) {
DropTableStatement dropTable = statement as DropTableStatement;
if (dropTable != null) {
droppedTables.Add(dropTable.ObjectName);
}
yield return WriteStatement(statement, builder, inventory.TargetEngine);
}
}
// now that the update scripts have updated the tables, mark the tables in the dependency resolver
foreach (IInstallStatement createTableStatement in alterUsingUpdateScript) {
resolver.AddExistingObject(createTableStatement.ObjectName);
}
// refresh the views and functions
foreach (string objectName in refreshObjectNames) {
yield return string.Format("EXEC [sp_refreshsqlmodule] '[{0}].[{1}]'", inventory.SchemaName, objectName);
}
// try to perform the remaining actions
foreach (IInstallStatement statement in resolver.GetInOrder(true).Where(statement => !(statement.IsTableUniqueConstraintOfTables(createdTables) || statement.DependsOnTables(droppedTables)))) {
foreach (IInstallStatement innerStatement in HandleDependendObjects(statement, inventory, dropStatements.Keys)) {
yield return WriteStatement(innerStatement, builder, inventory.TargetEngine);
}
}
// execute insert statements for table setup data
if (AdditionalSetupStatements.Any()) {
bool disabledChecks = false;
foreach (IScriptableStatement statement in AdditionalSetupStatements) {
Qualified<SchemaName, TableName> name = null;
InsertStatement insertStatement = statement as InsertStatement;
if (insertStatement != null) {
DestinationRowset<Qualified<SchemaName, TableName>> targetTable = insertStatement.DestinationRowset as DestinationRowset<Qualified<SchemaName, TableName>>;
if (targetTable != null) {
name = targetTable.Name;
}
} else {
SetIdentityInsertStatement setIdentityInsertStatement = statement as SetIdentityInsertStatement;
if (setIdentityInsertStatement != null) {
name = setIdentityInsertStatement.TableName;
}
}
if ((name != null) && name.IsQualified && string.Equals(name.Qualification.Value, inventory.SchemaName, StringComparison.OrdinalIgnoreCase) && newObjectNames.Contains(name.Name.Value)) {
if (!disabledChecks) {
foreach (CreateTableStatement table in Objects.OfType<CreateTableStatement>()) {
yield return WriteStatement(new AlterTableNocheckConstraintStatement(table.TableName, new TableCheckToken()), builder, inventory.TargetEngine);
}
disabledChecks = true;
}
yield return WriteStatement(statement, builder, inventory.TargetEngine);
}
}
if (disabledChecks) {
foreach (CreateTableStatement table in Objects.OfType<CreateTableStatement>()) {
yield return WriteStatement(new AlterTableCheckConstraintStatement(table.TableName, new TableWithCheckToken()), builder, inventory.TargetEngine);
}
}
}
// finally drop objects which are no longer used
foreach (IScriptableStatement dropStatement in dropStatements.Values.Where(s => !(s is AlterTableDropConstraintStatement || s is DropTableStatement || s is DropIndexStatement || s.DependsOnTables(droppedTables)))) {
yield return WriteStatement(dropStatement, builder, inventory.TargetEngine);
}
// refresh the SPs
foreach (string objectName in Objects.OfType<CreateProcedureStatement>().Where(sp => !(sp.Option is OptionSchemabindingToken)).Select(sp => sp.ObjectName)) {
yield return string.Format("EXEC [sp_refreshsqlmodule] '[{0}].[{1}]'", inventory.SchemaName, objectName);
}
} finally {
UnsetQualification();
inventory.UnsetQualification();
}
}
public IEnumerable<string> GenerateUninstallSql()
{
StringBuilder buffer = new StringBuilder(512);
SetQualification(SchemaName);
try {
DependencyResolver resolver = new DependencyResolver();
foreach (IAlterableCreateStatement statement in Objects.SelectMany(o => o.CreateStatementFragments(CreateFragmentMode.Alter))) {
resolver.Add(statement);
}
foreach (IAlterableCreateStatement statement in resolver.GetInOrder(true).Where(s => !(s is CreateIndexStatement)).Reverse()) {
AlterTableAddConstraintFragment addConstraint = statement as AlterTableAddConstraintFragment;
if ((addConstraint == null) || !(addConstraint.Constraint is TableUniqueConstraintBase)) {
yield return WriteStatement(statement.CreateDropStatement(), buffer, TargetEngine);
}
}
if (!schemaName.Equals("dbo", StringComparison.OrdinalIgnoreCase)) {
buffer.Length = 0;
using (TextWriter writer = new StringWriter(buffer)) {
SqlWriter sqlWriter = new SqlWriter(writer, TargetEngine);
sqlWriter.WriteKeyword("DROP SCHEMA ");
new SchemaName(SchemaName).WriteTo(sqlWriter);
}
yield return buffer.ToString();
}
} finally {
UnsetQualification();
}
}
public IEnumerable<string> GenerateInstallSql(DatabaseEngine targetEngine, string schemaName, string ownerName)
{
if (string.IsNullOrEmpty(schemaName)) {
throw new ArgumentNullException("schemaName");
}
bool newSchema = !schemaName.Equals("dbo", StringComparison.OrdinalIgnoreCase);
StringBuilder builder = new StringBuilder(4096);
DependencyResolver resolver = new DependencyResolver();
IEnumerable<IAlterableCreateStatement> createStatements = Objects.SelectMany(o => o.CreateStatementFragments(newSchema ? CreateFragmentMode.CreateOnNewSchema : CreateFragmentMode.CreateOnExistingSchema)).Where(s => s.DoesApplyToEngine(targetEngine));
if (newSchema) {
SetQualification(null);
try {
using (StringWriter writer = new StringWriter(builder)) {
SqlWriter sqlWriter = new SqlWriter(writer, targetEngine);
sqlWriter.WriteKeyword("CREATE SCHEMA");
using (sqlWriter.Indent()) {
sqlWriter.WriteScript(new SchemaName(schemaName), WhitespacePadding.SpaceBefore);
if (!string.IsNullOrEmpty(ownerName)) {
sqlWriter.Write(" AUTHORIZATION");
sqlWriter.WriteScript(new ObjectName(ownerName), WhitespacePadding.SpaceBefore);
}
DependencyResolver schemaResolver = new DependencyResolver();
foreach (IAlterableCreateStatement statement in createStatements) {
if (statement.IsPartOfSchemaDefinition) {
if (statement is CreateTableFragment) {
sqlWriter.WriteLine();
statement.WriteTo(sqlWriter);
resolver.AddExistingObject(statement.ObjectName);
schemaResolver.AddExistingObject(statement.ObjectName);
} else {
schemaResolver.Add(statement);
}
} else {
resolver.Add(statement);
}
}
try {
foreach (IInstallStatement statement in schemaResolver.GetInOrder(true)) {
sqlWriter.WriteLine();
statement.WriteTo(sqlWriter);
resolver.AddExistingObject(statement.ObjectName);
}
} catch (InvalidOperationException ex) {
schemaResolver.TransferPendingObjects(resolver);
log.DebugFormat("SCHEMA CREATE trimmed because of {0} - processing continues", ex, ex.Message);
}
}
yield return writer.ToString();
}
} finally {
UnsetQualification();
}
} else {
foreach (IAlterableCreateStatement statement in createStatements) {
resolver.Add(statement);
}
}
SetQualification(schemaName);
try {
foreach (IInstallStatement statement in resolver.GetInOrder(true)) {
yield return WriteStatement(statement, builder, targetEngine);
}
if (AdditionalSetupStatements.Any()) {
foreach (CreateTableStatement table in Objects.OfType<CreateTableStatement>()) {
yield return WriteStatement(new AlterTableNocheckConstraintStatement(table.TableName, new TableCheckToken()), builder, targetEngine);
}
foreach (IScriptableStatement additionalSetupStatement in AdditionalSetupStatements) {
yield return WriteStatement(additionalSetupStatement, builder, targetEngine);
}
foreach (CreateTableStatement table in Objects.OfType<CreateTableStatement>()) {
yield return WriteStatement(new AlterTableCheckConstraintStatement(table.TableName, new TableWithCheckToken()), builder, targetEngine);
}
}
} finally {
UnsetQualification();
}
}