public override SQLScriptList ToSqlDiff()
{
SQLScriptList list = new SQLScriptList();
if (this.HasState(Enums.ObjectStatusType.DropStatus))
list.Add(Drop());
if (this.HasState(Enums.ObjectStatusType.CreateStatus))
list.Add(Create());
if (this.Status == Enums.ObjectStatusType.AlterStatus)
{
list.AddRange(Rebuild());
}
list.AddRange(this.ExtendedProperties.ToSqlDiff());
return list;
}
public override SQLScriptList ToSqlDiff(System.Collections.Generic.ICollection <ISchemaBase> schemas)
{
SQLScriptList listDiff = new SQLScriptList();
if (this.Status == ObjectStatus.Drop)
{
listDiff.Add(ToSqlDrop(), 0, ScriptAction.DropFullText);
}
if (this.Status == ObjectStatus.Create)
{
listDiff.Add(ToSql(), 0, ScriptAction.AddFullText);
}
if (this.HasState(ObjectStatus.Alter))
{
listDiff.Add(ToSqlAlter(), 0, ScriptAction.AddFullText);
}
if (this.HasState(ObjectStatus.Disabled))
{
listDiff.Add(ToSqlAlterDefault(), 0, ScriptAction.AddFullText);
}
if (this.HasState(ObjectStatus.ChangeOwner))
{
listDiff.Add(ToSqlAlterOwner(), 0, ScriptAction.AddFullText);
}
return(listDiff);
}
/// <summary>
/// Devuelve el schema de diferencias del Schema en formato SQL.
/// </summary>
public override SQLScriptList ToSqlDiff()
{
SQLScriptList list = new SQLScriptList();
if (this.Status != Enums.ObjectStatusType.OriginalStatus)
{
RootParent.ActionMessage.Add(this);
}
if (this.HasState(Enums.ObjectStatusType.DropStatus))
{
list.Add(Drop());
}
if (this.HasState(Enums.ObjectStatusType.CreateStatus))
{
list.Add(Create());
}
if (this.HasState(Enums.ObjectStatusType.AlterStatus))
{
list.Add(ToSQLAlter(), 0, Enums.ScripActionType.AlterProcedure);
}
if (this.HasState(Enums.ObjectStatusType.AlterWhitespaceStatus))
{
list.Add(ToSQLAlter(), 0, Enums.ScripActionType.AlterProcedure);
}
return(list);
}
public override SQLScriptList ToSqlDiff(System.Collections.Generic.ICollection <ISchemaBase> schemas)
{
SQLScriptList list = new SQLScriptList();
if (this.Status != ObjectStatus.Original)
{
RootParent.ActionMessage[Parent.FullName].Add(this);
}
if (this.HasState(ObjectStatus.Drop))
{
list.Add(Drop());
}
if (this.HasState(ObjectStatus.Create))
{
list.Add(Create());
}
if (this.HasState(ObjectStatus.Alter))
{
list.Add(Drop());
list.Add(Create());
}
if (this.Status == ObjectStatus.Disabled)
{
list.Add(this.ToSqlEnabled(), Parent.DependenciesCount, ScriptAction.AlterFullTextIndex);
}
/*if (this.Status == StatusEnum.ObjectStatusType.ChangeFileGroup)
* {
* listDiff.Add(this.ToSQLDrop(this.FileGroup), ((Table)Parent).DependenciesCount, StatusEnum.ScripActionType.DropIndex);
* listDiff.Add(this.ToSQLAdd(), ((Table)Parent).DependenciesCount, StatusEnum.ScripActionType.AddIndex);
* }*/
list.AddRange(this.ExtendedProperties.ToSqlDiff());
return(list);
}
public override SQLScriptList ToSqlDiff()
{
try
{
SQLScriptList list = new SQLScriptList();
if (this.Status == Enums.ObjectStatusType.DropStatus)
{
list.Add(Drop());
}
if (this.HasState(Enums.ObjectStatusType.CreateStatus))
{
list.Add(Create());
}
if (this.Status == Enums.ObjectStatusType.AlterStatus)
{
list.Add(ToSqlDrop() + ToSql(), 0, Enums.ScripActionType.AddTableType);
}
return(list);
}
catch (Exception ex)
{
_logger.ErrorException(ex.Message, ex);
return(null);
}
}
public override SQLScriptList ToSqlDiff(System.Collections.Generic.ICollection <ISchemaBase> schemas)
{
SQLScriptList list = new SQLScriptList();
if (this.Status == ObjectStatus.Drop)
{
list.AddRange(RebuildDependencys());
list.Add(Drop());
}
if (this.Status == ObjectStatus.Create)
{
list.Add(Create());
}
if (this.HasState(ObjectStatus.Rebuild))
{
list.AddRange(Rebuild());
}
if (this.HasState(ObjectStatus.ChangeOwner))
{
list.Add(ToSQLAlterOwner(), 0, ScriptAction.AlterAssembly);
}
if (this.HasState(ObjectStatus.PermissionSet))
{
list.Add(ToSQLAlter(), 0, ScriptAction.AlterAssembly);
}
if (this.HasState(ObjectStatus.Alter))
{
list.AddRange(Files.ToSqlDiff());
}
list.AddRange(this.ExtendedProperties.ToSqlDiff());
return(list);
}
public override SQLScriptList ToSqlDiff()
{
SQLScriptList list = new SQLScriptList();
if (Status != Enums.ObjectStatusType.OriginalStatus)
{
RootParent.ActionMessage[Parent.FullName].Add(this);
}
if (HasState(Enums.ObjectStatusType.DropStatus))
{
list.Add(Drop());
}
if (HasState(Enums.ObjectStatusType.CreateStatus))
{
list.Add(Create());
}
if (HasState(Enums.ObjectStatusType.AlterStatus))
{
list.Add(Drop());
list.Add(Create());
}
if (Status == Enums.ObjectStatusType.DisabledStatus)
{
list.Add(ToSqlEnabled(), Parent.DependenciesCount, Enums.ScripActionType.AlterIndex);
}
/*if (this.Status == StatusEnum.ObjectStatusType.ChangeFileGroup)
* {
* listDiff.Add(this.ToSQLDrop(this.FileGroup), ((Table)Parent).DependenciesCount, StatusEnum.ScripActionType.DropIndex);
* listDiff.Add(this.ToSQLAdd(), ((Table)Parent).DependenciesCount, StatusEnum.ScripActionType.AddIndex);
* }*/
list.AddRange(ExtendedProperties.ToSqlDiff());
return(list);
}
public override SQLScriptList ToSqlDiff(System.Collections.Generic.ICollection <ISchemaBase> schemas)
{
SQLScriptList listDiff = new SQLScriptList();
if (this.Status == Enums.ObjectStatusType.DropStatus)
{
listDiff.Add(ToSqlDrop(), 0, Enums.ScripActionType.DropFullText);
}
if (this.Status == Enums.ObjectStatusType.CreateStatus)
{
listDiff.Add(ToSql(), 0, Enums.ScripActionType.AddFullText);
}
if (this.HasState(Enums.ObjectStatusType.AlterStatus))
{
listDiff.Add(ToSqlAlter(), 0, Enums.ScripActionType.AddFullText);
}
if (this.HasState(Enums.ObjectStatusType.DisabledStatus))
{
listDiff.Add(ToSqlAlterDefault(), 0, Enums.ScripActionType.AddFullText);
}
if (this.HasState(Enums.ObjectStatusType.ChangeOwner))
{
listDiff.Add(ToSqlAlterOwner(), 0, Enums.ScripActionType.AddFullText);
}
return(listDiff);
}
public SQLScriptList ToSQLDiff()
{
/*string sqlDrop = "";
* string sqlAdd = "";
* string sqlAlter = "";*/
SQLScriptList list = new SQLScriptList();
int index;
for (index = 0; index < this.Count; index++)
{
if (this[index].Status == StatusEnum.ObjectStatusType.DropStatus)
{
list.Add(this[index].ToSQLDrop(), parent.DependenciesCount, StatusEnum.ScripActionType.DropConstraint);
}
//sqlDrop += this[index].ToSQLDropConstraint();
if (this[index].Status == StatusEnum.ObjectStatusType.CreateStatus)
{
list.Add(this[index].ToSQLAdd(), parent.DependenciesCount, StatusEnum.ScripActionType.AddConstraint);
}
//sqlAdd += this[index].ToSQLAddConstraint();
if (this[index].Status == StatusEnum.ObjectStatusType.AlterStatus)
{
list.Add(this[index].ToSQLDrop(), parent.DependenciesCount, StatusEnum.ScripActionType.DropConstraint);
list.Add(this[index].ToSQLAdd(), parent.DependenciesCount, StatusEnum.ScripActionType.AddConstraint);
//sqlAlter += this[index].ToSQLDropConstraint() + this[index].ToSQLAddConstraint();
}
}
return(list);
//return sqlAlter+sqlDrop+sqlAdd;
}
private SQLScriptList ToSQLUnBindAll()
{
SQLScriptList listDiff = new SQLScriptList();
Hashtable items = new Hashtable();
List <UserDataType> useDataTypes = ((Database)this.Parent).UserTypes.FindAll(item => { return(item.Rule.FullName.Equals(this.FullName)); });
foreach (UserDataType item in useDataTypes)
{
foreach (ObjectDependency dependency in item.Dependencies)
{
Column column = ((Database)this.Parent).Tables[dependency.Name].Columns[dependency.ColumnName];
if ((!column.IsComputed) && (column.Status != ObjectStatus.Create))
{
if (!items.ContainsKey(column.FullName))
{
listDiff.Add("EXEC sp_unbindrule '" + column.FullName + "'\r\nGO\r\n", 0, ScriptAction.UnbindRuleColumn);
items.Add(column.FullName, column.FullName);
}
}
}
if (item.Rule.Status != ObjectStatus.Create)
{
listDiff.Add("EXEC sp_unbindrule '" + item.FullName + "'\r\nGO\r\n", 0, ScriptAction.UnbindRuleType);
}
}
return(listDiff);
}
private SQLScriptList RebuildFullTextIndex(string index)
{
bool it;
SQLScriptList list = new SQLScriptList();
((Table)Parent).FullTextIndex.ForEach(item =>
{
if (index == null)
{
it = item.Columns.Exists(col => { return(col.ColumnName.Equals(this.Name)); });
}
else
{
it = item.Index.Equals(index);
}
if (it)
{
if (item.Status != Enums.ObjectStatusType.CreateStatus)
{
list.Add(item.Drop());
}
if (item.Status != Enums.ObjectStatusType.DropStatus)
{
list.Add(item.Create());
}
}
}
);
return(list);
}
/// <summary>
/// Devuelve el schema de diferencias de la tabla en formato SQL.
/// </summary>
public SQLScriptList ToSQLDiff()
{
SQLScriptList listDiff = new SQLScriptList();
if (this.Status == StatusEnum.ObjectStatusType.DropStatus)
{
listDiff.Add(ToSQLDrop(), dependenciesCount, StatusEnum.ScripActionType.DropTable);
}
if (this.Status == StatusEnum.ObjectStatusType.CreateStatus)
{
listDiff.Add(ToSQL(), dependenciesCount, StatusEnum.ScripActionType.AddTable);
}
if (this.Status == StatusEnum.ObjectStatusType.AlterStatus)
{
listDiff.Add(columns.ToSQLDiff());
listDiff.Add(constraints.ToSQLDiff());
//listDiff.Add(indexes.ToSQLDiff());
listDiff.Add(triggers.ToSQLDiff());
}
if (this.Status == StatusEnum.ObjectStatusType.AlterRebuildStatus)
{
/*listDiff.Add(ToSQLRebuild(), dependenciesCount, StatusEnum.ScripActionType.RebuildTable);
* listDiff.Add(columns.ToSQLDiff());
* listDiff.Add(constraints.ToSQLDiff());
* listDiff.Add(indexes.ToSQLDiff());
* listDiff.Add(options.ToSQLDiff());
* //Como recrea la tabla, solo pone los nuevos triggers, por eso va ToSQL y no ToSQLDiff
* listDiff.Add(triggers.ToSQL(), dependenciesCount, StatusEnum.ScripActionType.AddTrigger);
*/
}
return(listDiff);
}
private SQLScriptList RebuildIndex()
{
SQLScriptList list = new SQLScriptList();
if (HasIndexDependencies)
{
((Table)Parent).Indexes.ForEach(item =>
{
IndexColumn ic = item.Columns.Find(this.Id);
if (ic != null)
{
if (item.Status != Enums.ObjectStatusType.CreateStatus)
{
list.Add(item.Drop());
}
if (item.Status != Enums.ObjectStatusType.DropStatus)
{
list.Add(item.Create());
}
list.AddRange(RebuildFullTextIndex(item.Name));
}
});
}
return(list);
}
/// <summary>
/// Devuelve el schema de diferencias del Schema en formato SQL.
/// </summary>
public override SQLScriptList ToSqlDiff(System.Collections.Generic.ICollection <ISchemaBase> schemas)
{
SQLScriptList list = new SQLScriptList();
if (this.Status != ObjectStatus.Original)
{
RootParent.ActionMessage.Add(this);
}
if (this.HasState(ObjectStatus.Drop))
{
list.Add(Drop());
}
if (this.HasState(ObjectStatus.Create))
{
list.Add(Create());
}
if (this.HasState(ObjectStatus.Alter))
{
list.Add(ToSQLAlter(), 0, ScriptAction.AlterProcedure);
}
if (this.HasState(ObjectStatus.AlterWhitespace))
{
list.Add(ToSQLAlter(), 0, ScriptAction.AlterProcedure);
}
return(list);
}
public override SQLScriptList ToSqlDiff()
{
SQLScriptList list = new SQLScriptList();
if (this.Status != Enums.ObjectStatusType.OriginalStatus)
RootParent.ActionMessage.Add(this);
if (this.HasState(Enums.ObjectStatusType.DropStatus))
list.Add(Drop());
if (this.HasState(Enums.ObjectStatusType.CreateStatus))
list.Add(Create());
if (this.HasState(Enums.ObjectStatusType.AlterStatus))
{
if (this.HasState(Enums.ObjectStatusType.RebuildDependenciesStatus))
list.AddRange(RebuildDependencys());
if (!this.GetWasInsertInDiffList(Enums.ScripActionType.DropFunction))
{
if (this.HasState(Enums.ObjectStatusType.RebuildStatus))
{
list.Add(Drop());
list.Add(Create());
}
if (this.HasState(Enums.ObjectStatusType.AlterBodyStatus))
{
int iCount = DependenciesCount;
list.Add(ToSQLAlter(), iCount, Enums.ScripActionType.AlterFunction);
}
}
}
return list;
}
public override SQLScriptList ToSqlDiff()
{
SQLScriptList list = new SQLScriptList();
if (this.Status == Enums.ObjectStatusType.DropStatus)
{
list.AddRange(RebuildDependencys());
list.Add(Drop());
}
if (this.Status == Enums.ObjectStatusType.CreateStatus)
{
list.Add(Create());
}
if (this.HasState(Enums.ObjectStatusType.RebuildStatus))
{
list.AddRange(Rebuild());
}
if (this.HasState(Enums.ObjectStatusType.ChangeOwner))
{
list.Add(ToSQLAlterOwner(), 0, Enums.ScripActionType.AlterAssembly);
}
if (this.HasState(Enums.ObjectStatusType.PermisionSet))
{
list.Add(ToSQLAlter(), 0, Enums.ScripActionType.AlterAssembly);
}
if (this.HasState(Enums.ObjectStatusType.AlterStatus))
{
list.AddRange(Files.ToSqlDiff());
}
list.AddRange(this.ExtendedProperties.ToSqlDiff());
return(list);
}
public override SQLScriptList ToSqlDiff(ICollection <ISchemaBase> schemas)
{
var isAzure10 = this.Info.Version == DatabaseInfo.SQLServerVersion.SQLServerAzure10;
var listDiff = new SQLScriptList();
var header = $@"/*
OpenDBDiff {System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString()}
https://github.com/OpenDBDiff/OpenDBDiff
Script created by {Environment.UserDomainName}\{Environment.UserName} on {DateTime.Now.ToShortDateString()} at {DateTime.Now.ToLongTimeString()}.
Created on: {Environment.MachineName}
Source: {SourceInfo?.Database ?? "Unknown"} on {SourceInfo?.Server ?? "Unknown"}
Destination: {Info?.Database ?? "Unknown"} on {Info?.Server ?? "Unknown"}
### This script performs actions to change the Destination schema to the Source schema. ###
*/
";
listDiff.Add(new SQLScript(header, 0, ScriptAction.None));
if (!isAzure10)
{
listDiff.Add("USE [" + Name + "]\r\nGO\r\n\r\n", 0, ScriptAction.UseDatabase);
listDiff.AddRange(Assemblies.ToSqlDiff(schemas));
listDiff.AddRange(UserTypes.ToSqlDiff(schemas));
}
listDiff.AddRange(TablesTypes.ToSqlDiff(schemas));
listDiff.AddRange(Tables.ToSqlDiff(schemas));
listDiff.AddRange(Rules.ToSqlDiff(schemas));
listDiff.AddRange(Schemas.ToSqlDiff(schemas));
listDiff.AddRange(XmlSchemas.ToSqlDiff(schemas));
listDiff.AddRange(Procedures.ToSqlDiff(schemas));
if (!isAzure10)
{
listDiff.AddRange(CLRProcedures.ToSqlDiff(schemas));
listDiff.AddRange(CLRFunctions.ToSqlDiff(schemas));
listDiff.AddRange(FileGroups.ToSqlDiff(schemas));
}
listDiff.AddRange(DDLTriggers.ToSqlDiff(schemas));
listDiff.AddRange(Synonyms.ToSqlDiff(schemas));
listDiff.AddRange(Views.ToSqlDiff(schemas));
listDiff.AddRange(Users.ToSqlDiff(schemas));
listDiff.AddRange(Functions.ToSqlDiff(schemas));
listDiff.AddRange(Roles.ToSqlDiff(schemas));
listDiff.AddRange(PartitionFunctions.ToSqlDiff(schemas));
listDiff.AddRange(PartitionSchemes.ToSqlDiff(schemas));
if (!isAzure10)
{
listDiff.AddRange(FullText.ToSqlDiff(schemas));
}
return(listDiff);
}
public SQLScriptList RebuildConstraint(Boolean Check)
{
SQLScriptList list = new SQLScriptList();
if (DefaultConstraint != null)
{
if ((!Check) || (DefaultConstraint.CanCreate)) list.Add(DefaultConstraint.Create());
list.Add(DefaultConstraint.Drop());
}
return list;
}
public override SQLScriptList ToSqlDiff()
{
try
{
var list = new SQLScriptList();
if (Status == Enums.ObjectStatusType.DropStatus)
{
if (!HasAnotherUDTClass())
{
list.Add(Drop());
}
}
if (HasState(Enums.ObjectStatusType.CreateStatus))
{
list.Add(Create());
}
if (Status == Enums.ObjectStatusType.AlterStatus)
{
if (Default.Status == Enums.ObjectStatusType.CreateStatus)
{
list.Add(Default.ToSQLAddBind(), 0, Enums.ScripActionType.AddUserDataType);
}
if (Default.Status == Enums.ObjectStatusType.DropStatus)
{
list.Add(Default.ToSQLAddUnBind(), 0, Enums.ScripActionType.UnbindRuleType);
}
if (Rule.Status == Enums.ObjectStatusType.CreateStatus)
{
list.Add(Rule.ToSQLAddBind(), 0, Enums.ScripActionType.AddUserDataType);
}
if (Rule.Status == Enums.ObjectStatusType.DropStatus)
{
list.Add(Rule.ToSQLAddUnBind(), 0, Enums.ScripActionType.UnbindRuleType);
}
}
if (Status == Enums.ObjectStatusType.RebuildStatus)
{
list.AddRange(ToSQLChangeColumns());
if (!GetWasInsertInDiffList(Enums.ScripActionType.DropUserDataType))
{
list.Add(ToSqlDrop() + ToSql(), 0, Enums.ScripActionType.AddUserDataType);
}
else
{
list.Add(Create());
}
}
if (HasState(Enums.ObjectStatusType.DropOlderStatus))
{
list.Add(SQLDropOlder(), 0, Enums.ScripActionType.AddUserDataType);
}
return(list);
}
catch (Exception ex)
{
_logger.ErrorException(ex.Message, ex);
return(null);
}
}
public override SQLScriptList ToSqlDiff(System.Collections.Generic.ICollection <ISchemaBase> schemas)
{
try
{
var list = new SQLScriptList();
if (Status == Enums.ObjectStatusType.DropStatus)
{
if (!HasAnotherUDTClass())
{
list.Add(Drop());
}
}
if (HasState(Enums.ObjectStatusType.CreateStatus))
{
list.Add(Create());
}
if (Status == Enums.ObjectStatusType.AlterStatus)
{
if (Default.Status == Enums.ObjectStatusType.CreateStatus)
{
list.Add(Default.ToSQLAddBind(), 0, Enums.ScripActionType.AddUserDataType);
}
if (Default.Status == Enums.ObjectStatusType.DropStatus)
{
list.Add(Default.ToSQLAddUnBind(), 0, Enums.ScripActionType.UnbindRuleType);
}
if (Rule.Status == Enums.ObjectStatusType.CreateStatus)
{
list.Add(Rule.ToSQLAddBind(), 0, Enums.ScripActionType.AddUserDataType);
}
if (Rule.Status == Enums.ObjectStatusType.DropStatus)
{
list.Add(Rule.ToSQLAddUnBind(), 0, Enums.ScripActionType.UnbindRuleType);
}
}
if (Status == Enums.ObjectStatusType.RebuildStatus)
{
list.AddRange(ToSQLChangeColumns());
if (!GetWasInsertInDiffList(Enums.ScripActionType.DropUserDataType))
{
list.Add(ToSqlDrop() + ToSql(), 0, Enums.ScripActionType.AddUserDataType);
}
else
{
list.Add(Create());
}
}
if (HasState(Enums.ObjectStatusType.DropOlderStatus))
{
list.Add(SQLDropOlder(), 0, Enums.ScripActionType.AddUserDataType);
}
return(list);
}
catch
{
return(null);
}
}
public override SQLScriptList ToSqlDiff()
{
SQLScriptList list = new SQLScriptList();
if (this.Status == Enums.ObjectStatusType.DropStatus)
list.Add(Drop());
if (this.Status == Enums.ObjectStatusType.CreateStatus)
list.Add(Create());
if (this.HasState(Enums.ObjectStatusType.AlterStatus))
list.AddRange(Rebuild());
if (this.HasState(Enums.ObjectStatusType.DisabledStatus))
list.Add(this.ToSQLEnabledDisabled(), 0, Enums.ScripActionType.EnabledTrigger);
return list;
}
/// <summary>
/// Devuelve el schema de diferencias del Schema en formato SQL.
/// </summary>
public override SQLScriptList ToSqlDiff(System.Collections.Generic.ICollection <ISchemaBase> schemas)
{
SQLScriptList listDiff = new SQLScriptList();
if (this.Status == ObjectStatus.Drop)
{
listDiff.Add(ToSqlDrop(), 0, ScriptAction.DropSchema);
}
if (this.Status == ObjectStatus.Create)
{
listDiff.Add(ToSql(), 0, ScriptAction.AddSchema);
}
return(listDiff);
}
/// <summary>
/// Devuelve el schema de diferencias del Schema en formato SQL.
/// </summary>
public override SQLScriptList ToSqlDiff()
{
SQLScriptList listDiff = new SQLScriptList();
if (this.Status == Enums.ObjectStatusType.DropStatus)
{
listDiff.Add(ToSqlDrop(), 0, Enums.ScripActionType.DropSchema);
}
if (this.Status == Enums.ObjectStatusType.CreateStatus)
{
listDiff.Add(ToSql(), 0, Enums.ScripActionType.AddSchema);
}
return listDiff;
}
/// <summary>
/// Devuelve el schema de diferencias del Schema en formato SQL.
/// </summary>
public override SQLScriptList ToSqlDiff(System.Collections.Generic.ICollection <OpenDBDiff.Schema.Model.ISchemaBase> schemas)
{
SQLScriptList listDiff = new SQLScriptList();
if (this.Status == Enums.ObjectStatusType.DropStatus)
{
listDiff.Add(ToSqlDrop(), 0, Enums.ScripActionType.DropSchema);
}
if (this.Status == Enums.ObjectStatusType.CreateStatus)
{
listDiff.Add(ToSql(), 0, Enums.ScripActionType.AddSchema);
}
return(listDiff);
}
/// <summary>
/// Devuelve el schema de diferencias del Schema en formato SQL.
/// </summary>
public override SQLScriptList ToSqlDiff()
{
SQLScriptList list = new SQLScriptList();
if (this.Status != Enums.ObjectStatusType.OriginalStatus)
RootParent.ActionMessage.Add(this);
if (this.HasState(Enums.ObjectStatusType.DropStatus))
list.Add(Drop());
if (this.HasState(Enums.ObjectStatusType.CreateStatus))
list.Add(Create());
if (this.Status == Enums.ObjectStatusType.AlterStatus)
list.Add(ToSQLAlter(), 0, Enums.ScripActionType.AlterProcedure);
return list;
}
/// <summary>
/// Devuelve el schema de diferencias del Schema en formato SQL.
/// </summary>
public override SQLScriptList ToSqlDiff()
{
SQLScriptList listDiff = new SQLScriptList();
if (this.Status == Enums.ObjectStatusType.DropStatus)
{
listDiff.Add(ToSqlDrop(), 0, Enums.ScripActionType.DropSchema);
}
if (this.Status == Enums.ObjectStatusType.CreateStatus)
{
listDiff.Add(ToSql(), 0, Enums.ScripActionType.AddSchema);
}
return(listDiff);
}
/// <summary>
/// Rebuilds the object and all its dependant objects.
/// </summary>
/// <returns></returns>
public SQLScriptList Rebuild()
{
SQLScriptList list = new SQLScriptList();
list.AddRange(RebuildDependencies());
if (this.Status != ObjectStatus.Create)
{
list.Add(Drop(), deepMin);
}
if (this.Status != ObjectStatus.Drop)
{
list.Add(Create(), deepMax);
}
return(list);
}
/// <summary>
/// Regenera el objeto, y todos sus objetos dependientes.
/// </summary>
/// <returns></returns>
public SQLScriptList Rebuild()
{
SQLScriptList list = new SQLScriptList();
list.AddRange(RebuildDependencys());
if (this.Status != Enums.ObjectStatusType.CreateStatus)
{
list.Add(Drop(), deepMin);
}
if (this.Status != Enums.ObjectStatusType.DropStatus)
{
list.Add(Create(), deepMax);
}
return(list);
}
private SQLScriptList RebuildConstraint()
{
SQLScriptList list = new SQLScriptList();
((Table)Parent).Constraints.ForEach(item =>
{
ConstraintColumn ic = item.Columns.Find(this.Id);
if (ic != null)
{
if (item.Status != ObjectStatus.Create) list.Add(item.Drop());
if (item.Status != ObjectStatus.Drop) list.Add(item.Create());
list.AddRange(RebuildFullTextIndex(item.Name));
}
});
return list;
}
public override SQLScriptList ToSqlDiff()
{
SQLScriptList listDiff = new SQLScriptList();
if (this.Status == Enums.ObjectStatusType.DropStatus)
listDiff.Add(ToSqlDrop(), 0, Enums.ScripActionType.DropAssemblyFile);
if (this.Status == Enums.ObjectStatusType.CreateStatus)
listDiff.Add(ToSqlAdd(), 0, Enums.ScripActionType.AddAssemblyFile);
if (this.HasState(Enums.ObjectStatusType.AlterStatus))
{
listDiff.Add(ToSqlDrop(), 0, Enums.ScripActionType.DropAssemblyFile);
listDiff.Add(ToSqlAdd(), 0, Enums.ScripActionType.AddAssemblyFile);
}
return listDiff;
}
public SQLScriptList RebuildSchemaBindingDependencies()
{
SQLScriptList list = new SQLScriptList();
List<ISchemaBase> items = ((Database)this.Parent.Parent).Dependencies.Find(this.Parent.Id, this.Id, 0);
items.ForEach(item =>
{
if ((item.ObjectType == ObjectType.Function) || (item.ObjectType == ObjectType.View))
{
if (item.Status != ObjectStatus.Create)
list.Add(item.Drop());
if (item.Status != ObjectStatus.Drop)
list.Add(item.Create());
}
});
return list;
}
public SQLScriptList ToSQLDiff()
{
string sqlDrop = "";
string sqlAdd = "";
string sqlAlter = "";
SQLScriptList list = new SQLScriptList();
int index;
for (index = 0; index < this.Count; index++)
{
if (this[index].Status == StatusEnum.ObjectStatusType.DropStatus)
{
sqlDrop += this[index].ToSQLDrop();
}
if (this[index].Status == StatusEnum.ObjectStatusType.CreateStatus)
{
sqlAdd += this[index].ToSQL();
}
if (this[index].Status == StatusEnum.ObjectStatusType.AlterStatus)
{
sqlAlter += this[index].ToSQLDrop() + this[index].ToSQL();
}
}
if (!String.IsNullOrEmpty(sqlAlter + sqlDrop + sqlAdd))
{
list.Add(sqlAlter + sqlDrop + sqlAdd, parent.DependenciesCount, StatusEnum.ScripActionType.AlterOptions);
}
return(list);
}
public SQLScriptList Alter(ScriptAction typeStatus)
{
SQLScriptList list = new SQLScriptList();
string sql = "ALTER TABLE " + Parent.FullName + " ALTER COLUMN " + this.ToSql(false) + "\r\nGO\r\n";
list.Add(sql, 0, typeStatus);
return list;
}
/*public List<ISchemaBase> FindAllByColumn(String ColumnName)
* {
* this.t
* }*/
public override SQLScriptList ToSqlDiff()
{
var listDiff = new SQLScriptList();
listDiff.Add("USE [" + Name + "]\r\nGO\r\n\r\n", 0, Enums.ScripActionType.UseDatabase);
listDiff.AddRange(Assemblies.ToSqlDiff());
listDiff.AddRange(Defaults.ToSqlDiff());
listDiff.AddRange(UserTypes.ToSqlDiff());
listDiff.AddRange(TablesTypes.ToSqlDiff());
listDiff.AddRange(Tables.ToSqlDiff());
listDiff.AddRange(Rules.ToSqlDiff());
listDiff.AddRange(Schemas.ToSqlDiff());
listDiff.AddRange(XmlSchemas.ToSqlDiff());
listDiff.AddRange(Procedures.ToSqlDiff());
listDiff.AddRange(CLRProcedures.ToSqlDiff());
listDiff.AddRange(CLRFunctions.ToSqlDiff());
listDiff.AddRange(FileGroups.ToSqlDiff());
listDiff.AddRange(DDLTriggers.ToSqlDiff());
listDiff.AddRange(Synonyms.ToSqlDiff());
listDiff.AddRange(Views.ToSqlDiff());
listDiff.AddRange(Users.ToSqlDiff());
listDiff.AddRange(Functions.ToSqlDiff());
listDiff.AddRange(Roles.ToSqlDiff());
listDiff.AddRange(PartitionFunctions.ToSqlDiff());
listDiff.AddRange(PartitionSchemes.ToSqlDiff());
listDiff.AddRange(FullText.ToSqlDiff());
return(listDiff);
}
/// <summary>
/// Genera una lista de script de DROPS de todas los constraints dependientes de la tabla.
/// Se usa cuando se quiere reconstruir una tabla y todos sus objectos dependientes.
/// </summary>
private SQLScriptList ToSQLDropDependencis()
{
bool addDependencie = true;
var listDiff = new SQLScriptList();
//Se buscan todas las table constraints.
for (int index = 0; index < dependencis.Count; index++)
{
if ((dependencis[index].Status == Enums.ObjectStatusType.OriginalStatus) ||
(dependencis[index].Status == Enums.ObjectStatusType.DropStatus))
{
addDependencie = true;
if (dependencis[index].ObjectType == Enums.ObjectType.Constraint)
{
if ((((Constraint)dependencis[index]).Type == Constraint.ConstraintType.Unique) &&
((HasFileStream) || (OriginalTable.HasFileStream)))
{
addDependencie = false;
}
if ((((Constraint)dependencis[index]).Type != Constraint.ConstraintType.ForeignKey) &&
(dependencis[index].Status == Enums.ObjectStatusType.DropStatus))
{
addDependencie = false;
}
}
if (addDependencie)
{
listDiff.Add(dependencis[index].Drop());
}
}
}
//Se buscan todas las columns constraints.
columns.ForEach(column =>
{
if (column.DefaultConstraint != null)
{
if (((column.DefaultConstraint.Status == Enums.ObjectStatusType.OriginalStatus) ||
(column.DefaultConstraint.Status == Enums.ObjectStatusType.DropStatus) ||
(column.DefaultConstraint.Status == Enums.ObjectStatusType.AlterStatus)) &&
(column.Status != Enums.ObjectStatusType.CreateStatus))
{
listDiff.Add(column.DefaultConstraint.Drop());
}
}
});
return(listDiff);
}
public override SQLScriptList ToSqlDiff()
{
SQLScriptList list = new SQLScriptList();
if (this.Parent.Status != Enums.ObjectStatusType.CreateStatus)
{
if (this.Status == Enums.ObjectStatusType.CreateStatus)
{
list.Add(this.Create());
}
if (this.Status == Enums.ObjectStatusType.DropStatus)
{
list.Add(this.Drop());
}
}
return(list);
}
/// <summary>
/// Devuelve el schema de diferencias del Schema en formato SQL.
/// </summary>
public override SQLScriptList ToSqlDiff()
{
SQLScriptList listDiff = new SQLScriptList();
if (this.Status == Enums.ObjectStatusType.DropStatus)
{
listDiff.AddRange(ToSQLUnBindAll());
listDiff.Add(Drop());
}
if (this.Status == Enums.ObjectStatusType.CreateStatus)
listDiff.Add(Create());
if (this.Status == Enums.ObjectStatusType.AlterStatus)
{
listDiff.AddRange(ToSQLUnBindAll());
listDiff.AddRange(Rebuild());
}
return listDiff;
}
private SQLScriptList ToSQLUnBindAll()
{
SQLScriptList listDiff = new SQLScriptList();
Hashtable items = new Hashtable();
List<UserDataType> useDataTypes = ((Database)this.Parent).UserTypes.FindAll(item => { return item.Rule.FullName.Equals(this.FullName); });
foreach (UserDataType item in useDataTypes)
{
foreach (ObjectDependency dependency in item.Dependencys)
{
Column column = ((Database)this.Parent).Tables[dependency.Name].Columns[dependency.ColumnName];
if ((!column.IsComputed) && (column.Status != Enums.ObjectStatusType.CreateStatus))
{
if (!items.ContainsKey(column.FullName))
{
listDiff.Add("EXEC sp_unbindrule '" + column.FullName + "'\r\nGO\r\n", 0, Enums.ScripActionType.UnbindRuleColumn);
items.Add(column.FullName, column.FullName);
}
}
}
if (item.Rule.Status != Enums.ObjectStatusType.CreateStatus)
listDiff.Add("EXEC sp_unbindrule '" + item.FullName + "'\r\nGO\r\n", 0, Enums.ScripActionType.UnbindRuleType);
}
return listDiff;
}
/// <summary>
/// Devuelve el schema de diferencias del Schema en formato SQL.
/// </summary>
public override SQLScriptList ToSqlDiff()
{
SQLScriptList list = new SQLScriptList();
if (this.Status == Enums.ObjectStatusType.DropStatus)
{
list.Add(ToSqlDrop(), 0, Enums.ScripActionType.DropXMLSchema);
}
if (this.Status == Enums.ObjectStatusType.CreateStatus)
{
list.Add(ToSql(), 0, Enums.ScripActionType.AddXMLSchema);
}
if (this.Status == Enums.ObjectStatusType.AlterStatus)
{
list.AddRange(ToSQLChangeColumns());
list.Add(ToSqlDrop() + ToSql(), 0, Enums.ScripActionType.AddXMLSchema);
}
return list;
}
public override SQLScriptList ToSqlDiff()
{
SQLScriptList list = new SQLScriptList();
if (this.Status != Enums.ObjectStatusType.OriginalStatus)
RootParent.ActionMessage[Parent.FullName].Add(this);
if (this.HasState(Enums.ObjectStatusType.DropStatus))
{
if (this.Parent.Status != Enums.ObjectStatusType.RebuildStatus)
list.Add(Drop());
}
if (this.HasState(Enums.ObjectStatusType.CreateStatus))
list.Add(Create());
if (this.HasState(Enums.ObjectStatusType.AlterStatus))
{
list.Add(Drop());
list.Add(Create());
}
if (this.HasState(Enums.ObjectStatusType.DisabledStatus))
{
list.Add(this.ToSQLEnabledDisabled(), ((Table)Parent).DependenciesCount, Enums.ScripActionType.AlterConstraint);
}
/*if (this.Status == StatusEnum.ObjectStatusType.ChangeFileGroup)
{
list.Add(this.ToSQLDrop(this.Index.FileGroup), ((Table)Parent).DependenciesCount, actionDrop);
list.Add(this.ToSQLAdd(), ((Table)Parent).DependenciesCount, actionAdd);
}*/
return list;
}
/*public List<ISchemaBase> FindAllByColumn(String ColumnName)
{
this.t
}*/
public override SQLScriptList ToSqlDiff()
{
var listDiff = new SQLScriptList();
listDiff.Add("USE [" + Name + "]\r\nGO\r\n\r\n", 0, Enums.ScripActionType.UseDatabase);
listDiff.AddRange(Assemblies.ToSqlDiff());
listDiff.AddRange(Defaults.ToSqlDiff());
listDiff.AddRange(UserTypes.ToSqlDiff());
listDiff.AddRange(TablesTypes.ToSqlDiff());
listDiff.AddRange(Tables.ToSqlDiff());
listDiff.AddRange(Rules.ToSqlDiff());
listDiff.AddRange(Schemas.ToSqlDiff());
listDiff.AddRange(XmlSchemas.ToSqlDiff());
listDiff.AddRange(Procedures.ToSqlDiff());
listDiff.AddRange(CLRProcedures.ToSqlDiff());
listDiff.AddRange(CLRFunctions.ToSqlDiff());
listDiff.AddRange(FileGroups.ToSqlDiff());
listDiff.AddRange(DDLTriggers.ToSqlDiff());
listDiff.AddRange(Synonyms.ToSqlDiff());
listDiff.AddRange(Views.ToSqlDiff());
listDiff.AddRange(Users.ToSqlDiff());
listDiff.AddRange(Functions.ToSqlDiff());
listDiff.AddRange(Roles.ToSqlDiff());
listDiff.AddRange(PartitionFunctions.ToSqlDiff());
listDiff.AddRange(PartitionSchemes.ToSqlDiff());
listDiff.AddRange(FullText.ToSqlDiff());
return listDiff;
}
public override SQLScriptList ToSqlDiff(List<ISchemaBase> schemas)
{
var isAzure10 = this.Info.Version == DatabaseInfo.VersionTypeEnum.SQLServerAzure10;
var listDiff = new SQLScriptList();
listDiff.Add(new SQLScript(String.Format(@"/*
Open DBDiff {0}
http://opendbiff.codeplex.com/
Script created by {1}\{2} on {3} at {4}.
Created on: {5}
Source: {6} on {7}
Destination: {8} on {9}
*/
",
System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString(),
Environment.UserDomainName,
Environment.UserName,
DateTime.Now.ToShortDateString(),
DateTime.Now.ToLongTimeString(),
Environment.MachineName,
SourceInfo != null ? SourceInfo.Database : "Uknown",
SourceInfo != null ? SourceInfo.Server : "Uknown",
Info != null ? Info.Database : "Uknown",
Info != null ? Info.Server : "Uknown",
0), 0, Enums.ScripActionType.None));
if (!isAzure10)
{
listDiff.Add("USE [" + Name + "]\r\nGO\r\n\r\n", 0, Enums.ScripActionType.UseDatabase);
listDiff.AddRange(Assemblies.ToSqlDiff(schemas));
listDiff.AddRange(UserTypes.ToSqlDiff(schemas));
}
listDiff.AddRange(TablesTypes.ToSqlDiff(schemas));
listDiff.AddRange(Tables.ToSqlDiff(schemas));
listDiff.AddRange(Rules.ToSqlDiff(schemas));
listDiff.AddRange(Schemas.ToSqlDiff(schemas));
listDiff.AddRange(XmlSchemas.ToSqlDiff(schemas));
listDiff.AddRange(Procedures.ToSqlDiff(schemas));
if (!isAzure10)
{
listDiff.AddRange(CLRProcedures.ToSqlDiff(schemas));
listDiff.AddRange(CLRFunctions.ToSqlDiff(schemas));
listDiff.AddRange(FileGroups.ToSqlDiff(schemas));
}
listDiff.AddRange(DDLTriggers.ToSqlDiff(schemas));
listDiff.AddRange(Synonyms.ToSqlDiff(schemas));
listDiff.AddRange(Views.ToSqlDiff(schemas));
listDiff.AddRange(Users.ToSqlDiff(schemas));
listDiff.AddRange(Functions.ToSqlDiff(schemas));
listDiff.AddRange(Roles.ToSqlDiff(schemas));
listDiff.AddRange(PartitionFunctions.ToSqlDiff(schemas));
listDiff.AddRange(PartitionSchemes.ToSqlDiff(schemas));
if (!isAzure10)
{
listDiff.AddRange(FullText.ToSqlDiff(schemas));
}
return listDiff;
}
public override SQLScriptList ToSqlDiff()
{
SQLScriptList list = new SQLScriptList();
if (this.Parent.Status != Enums.ObjectStatusType.CreateStatus)
{
if (this.Status == Enums.ObjectStatusType.CreateStatus)
list.Add(this.Create());
if (this.Status == Enums.ObjectStatusType.DropStatus)
list.Add(this.Drop());
}
return list;
}
public override SQLScriptList ToSqlDiff()
{
SQLScriptList listDiff = new SQLScriptList();
if (this.Status == Enums.ObjectStatusType.DropStatus)
{
listDiff.Add(ToSqlDrop(), 0, Enums.ScripActionType.DropFullText);
}
if (this.Status == Enums.ObjectStatusType.CreateStatus)
{
listDiff.Add(ToSql(), 0, Enums.ScripActionType.AddFullText);
}
if (this.HasState(Enums.ObjectStatusType.AlterStatus))
{
listDiff.Add(ToSqlAlter(), 0, Enums.ScripActionType.AddFullText);
}
if (this.HasState(Enums.ObjectStatusType.DisabledStatus))
{
listDiff.Add(ToSqlAlterDefault(), 0, Enums.ScripActionType.AddFullText);
}
if (this.HasState(Enums.ObjectStatusType.ChangeOwner))
{
listDiff.Add(ToSqlAlterOwner(), 0, Enums.ScripActionType.AddFullText);
}
return listDiff;
}
private SQLScriptList ToSQLChangeColumns()
{
var fields = new Hashtable();
var list = new SQLScriptList();
var listDependencys = new SQLScriptList();
if ((Status == Enums.ObjectStatusType.AlterStatus) || (Status == Enums.ObjectStatusType.RebuildStatus))
{
foreach (ObjectDependency dependency in Dependencys)
{
ISchemaBase itemDepens = ((Database) Parent).Find(dependency.Name);
/*Si la dependencia es una funcion o una vista, reconstruye el objecto*/
if (dependency.IsCodeType)
{
if (itemDepens != null)
list.AddRange(((ICode) itemDepens).Rebuild());
}
/*Si la dependencia es una tabla, reconstruye los indices, constraint y columnas asociadas*/
if (dependency.Type == Enums.ObjectType.Table)
{
Column column = ((Table) itemDepens).Columns[dependency.ColumnName];
if ((column.Parent.Status != Enums.ObjectStatusType.DropStatus) &&
(column.Parent.Status != Enums.ObjectStatusType.CreateStatus) &&
((column.Status != Enums.ObjectStatusType.CreateStatus) || (column.IsComputed)))
{
if (!fields.ContainsKey(column.FullName))
{
listDependencys.AddRange(RebuildDependencys((Table) itemDepens));
if (column.HasToRebuildOnlyConstraint)
//column.Parent.Status = Enums.ObjectStatusType.AlterRebuildDependenciesStatus;
list.AddRange(column.RebuildDependencies());
if (!column.IsComputed)
{
list.AddRange(column.RebuildConstraint(true));
list.Add(
"ALTER TABLE " + column.Parent.FullName + " ALTER COLUMN " +
column.ToSQLRedefine(Type, Size, null) + "\r\nGO\r\n", 0,
Enums.ScripActionType.AlterColumn);
/*Si la columna va a ser eliminada o la tabla va a ser reconstruida, no restaura la columna*/
if ((column.Status != Enums.ObjectStatusType.DropStatus) &&
(column.Parent.Status != Enums.ObjectStatusType.RebuildStatus))
list.AddRange(column.Alter(Enums.ScripActionType.AlterColumnRestore));
}
else
{
if (column.Status != Enums.ObjectStatusType.CreateStatus)
{
if (!column.GetWasInsertInDiffList(Enums.ScripActionType.AlterColumnFormula))
{
column.SetWasInsertInDiffList(Enums.ScripActionType.AlterColumnFormula);
list.Add(column.ToSqlDrop(), 0, Enums.ScripActionType.AlterColumnFormula);
List<ISchemaBase> drops =
((Database) column.Parent.Parent).Dependencies.Find(column.Parent.Id,
column.Id, 0);
drops.ForEach(item =>
{
if (item.Status != Enums.ObjectStatusType.CreateStatus)
list.Add(item.Drop());
if (item.Status != Enums.ObjectStatusType.DropStatus)
list.Add(item.Create());
});
/*Si la columna va a ser eliminada o la tabla va a ser reconstruida, no restaura la columna*/
if ((column.Status != Enums.ObjectStatusType.DropStatus) &&
(column.Parent.Status != Enums.ObjectStatusType.RebuildStatus))
list.Add(column.ToSqlAdd(), 0,
Enums.ScripActionType.AlterColumnFormulaRestore);
}
}
}
fields.Add(column.FullName, column.FullName);
}
}
}
}
}
list.AddRange(listDependencys);
return list;
}
public override SQLScriptList ToSqlDiff()
{
SQLScriptList listDiff = new SQLScriptList();
if (this.Status == Enums.ObjectStatusType.DropStatus)
listDiff.Add(this.ToSqlDrop(), 1, Enums.ScripActionType.DropFileGroup);
if (this.Status == Enums.ObjectStatusType.CreateStatus)
listDiff.Add(this.ToSqlAdd(), 1, Enums.ScripActionType.AddFileGroup);
if (this.Status == Enums.ObjectStatusType.AlterStatus)
listDiff.Add(this.ToSQLAlter(), 1, Enums.ScripActionType.AlterFileGroup);
return listDiff;
}
public override SQLScriptList ToSqlDiff()
{
SQLScriptList list = new SQLScriptList();
if (Status != Enums.ObjectStatusType.OriginalStatus)
RootParent.ActionMessage[Parent.FullName].Add(this);
if (HasState(Enums.ObjectStatusType.DropStatus))
list.Add(Drop());
if (HasState(Enums.ObjectStatusType.CreateStatus))
list.Add(Create());
if (HasState(Enums.ObjectStatusType.AlterStatus))
{
list.Add(Drop());
list.Add(Create());
}
if (Status == Enums.ObjectStatusType.DisabledStatus)
{
list.Add(ToSqlEnabled(), Parent.DependenciesCount, Enums.ScripActionType.AlterIndex);
}
/*if (this.Status == StatusEnum.ObjectStatusType.ChangeFileGroup)
{
listDiff.Add(this.ToSQLDrop(this.FileGroup), ((Table)Parent).DependenciesCount, StatusEnum.ScripActionType.DropIndex);
listDiff.Add(this.ToSQLAdd(), ((Table)Parent).DependenciesCount, StatusEnum.ScripActionType.AddIndex);
}*/
list.AddRange(ExtendedProperties.ToSqlDiff());
return list;
}
public override SQLScriptList ToSqlDiff()
{
try
{
var list = new SQLScriptList();
if (Status == Enums.ObjectStatusType.DropStatus)
{
if (!HasAnotherUDTClass())
list.Add(Drop());
}
if (HasState(Enums.ObjectStatusType.CreateStatus))
{
list.Add(Create());
}
if (Status == Enums.ObjectStatusType.AlterStatus)
{
if (Default.Status == Enums.ObjectStatusType.CreateStatus)
list.Add(Default.ToSQLAddBind(), 0, Enums.ScripActionType.AddUserDataType);
if (Default.Status == Enums.ObjectStatusType.DropStatus)
list.Add(Default.ToSQLAddUnBind(), 0, Enums.ScripActionType.UnbindRuleType);
if (Rule.Status == Enums.ObjectStatusType.CreateStatus)
list.Add(Rule.ToSQLAddBind(), 0, Enums.ScripActionType.AddUserDataType);
if (Rule.Status == Enums.ObjectStatusType.DropStatus)
list.Add(Rule.ToSQLAddUnBind(), 0, Enums.ScripActionType.UnbindRuleType);
}
if (Status == Enums.ObjectStatusType.RebuildStatus)
{
list.AddRange(ToSQLChangeColumns());
if (!GetWasInsertInDiffList(Enums.ScripActionType.DropUserDataType))
{
list.Add(ToSqlDrop() + ToSql(), 0, Enums.ScripActionType.AddUserDataType);
}
else
list.Add(Create());
}
if (HasState(Enums.ObjectStatusType.DropOlderStatus))
{
list.Add(SQLDropOlder(), 0, Enums.ScripActionType.AddUserDataType);
}
return list;
}
catch (Exception ex)
{
return null;
}
}
public override SQLScriptList ToSqlDiff(List<ISchemaBase> schemas)
{
var isAzure10 = this.Info.Version == DatabaseInfo.VersionTypeEnum.SQLServerAzure10;
var listDiff = new SQLScriptList();
listDiff.Add(new SQLScript(String.Format(@"/*
Open DBDiff {0}
http://opendbiff.codeplex.com/
Script created by {1}\{2} on {3} at {4}.
Created on: {5}
Source: {6} on {7}
Destination: {8} on {9}
*/
",
System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString(),
Environment.UserDomainName,
Environment.UserName,
DateTime.Now.ToShortDateString(),
DateTime.Now.ToLongTimeString(),
Environment.MachineName,
SourceInfo != null ? SourceInfo.Database : "Uknown",
SourceInfo != null ? SourceInfo.Server : "Uknown",
Info != null ? Info.Database : "Uknown",
Info != null ? Info.Server : "Uknown",
0), 0, Enums.ScripActionType.None));
listDiff.Add(new SQLScript(String.Format(@"
SET NOEXEC OFF
SET ANSI_WARNINGS ON
SET XACT_ABORT ON
SET IMPLICIT_TRANSACTIONS OFF
SET ARITHABORT ON
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
GO
"), 0, Enums.ScripActionType.PreSets));
if (!isAzure10)
{
listDiff.Add("USE [" + Name + "]\r\nGO\r\n\r\n", 0, Enums.ScripActionType.UseDatabase);
listDiff.AddRange(Assemblies.ToSqlDiff(schemas));
listDiff.AddRange(UserTypes.ToSqlDiff(schemas));
listDiff.Add("BEGIN TRAN\r\nGO\r\n\r\n", 0, Enums.ScripActionType.BeginTransaction);
}
listDiff.AddRange(TablesTypes.ToSqlDiff(schemas));
listDiff.AddRange(Tables.ToSqlDiff(schemas));
listDiff.AddRange(Rules.ToSqlDiff(schemas));
listDiff.AddRange(Schemas.ToSqlDiff(schemas));
listDiff.AddRange(XmlSchemas.ToSqlDiff(schemas));
listDiff.AddRange(Procedures.ToSqlDiff(schemas));
if (!isAzure10)
{
listDiff.AddRange(CLRProcedures.ToSqlDiff(schemas));
listDiff.AddRange(CLRFunctions.ToSqlDiff(schemas));
listDiff.AddRange(FileGroups.ToSqlDiff(schemas));
}
listDiff.AddRange(DDLTriggers.ToSqlDiff(schemas));
listDiff.AddRange(Synonyms.ToSqlDiff(schemas));
listDiff.AddRange(Views.ToSqlDiff(schemas));
listDiff.AddRange(Users.ToSqlDiff(schemas));
listDiff.AddRange(Functions.ToSqlDiff(schemas));
listDiff.AddRange(Roles.ToSqlDiff(schemas));
listDiff.AddRange(PartitionFunctions.ToSqlDiff(schemas));
listDiff.AddRange(PartitionSchemes.ToSqlDiff(schemas));
if (!isAzure10)
{
listDiff.AddRange(FullText.ToSqlDiff(schemas));
listDiff.Add("IF @@TRANCOUNT>0\r\n\tCOMMIT\r\nGO\r\n\r\n", 0, Enums.ScripActionType.EndTransaction);
}
listDiff.Add("SET NOEXEC OFF\r\n", 0, Enums.ScripActionType.PostSets);
return listDiff;
}
public override SQLScriptList ToSqlDiff()
{
SQLScriptList list = new SQLScriptList();
if (this.Status == Enums.ObjectStatusType.DropStatus)
{
list.AddRange(RebuildDependencys());
list.Add(Drop());
}
if (this.Status == Enums.ObjectStatusType.CreateStatus)
list.Add(Create());
if (this.HasState(Enums.ObjectStatusType.RebuildStatus))
list.AddRange(Rebuild());
if (this.HasState(Enums.ObjectStatusType.ChangeOwner))
list.Add(ToSQLAlterOwner(), 0, Enums.ScripActionType.AlterAssembly);
if (this.HasState(Enums.ObjectStatusType.PermisionSet))
list.Add(ToSQLAlter(), 0, Enums.ScripActionType.AlterAssembly);
if (this.HasState(Enums.ObjectStatusType.AlterStatus))
list.AddRange(Files.ToSqlDiff());
list.AddRange(this.ExtendedProperties.ToSqlDiff());
return list;
}
public override SQLScriptList ToSqlDiff()
{
SQLScriptList list = new SQLScriptList();
if (this.HasState(Enums.ObjectStatusType.DropStatus))
list.Add(Drop());
if (this.HasState(Enums.ObjectStatusType.CreateStatus))
list.Add(Create());
if (this.Status == Enums.ObjectStatusType.AlterStatus)
{
list.Add(Drop());
list.Add(Create());
}
return list;
}
/// <summary>
/// Regenera el objeto, y todos sus objetos dependientes.
/// </summary>
/// <returns></returns>
public SQLScriptList Rebuild()
{
SQLScriptList list = new SQLScriptList();
list.AddRange(RebuildDependencys());
if (this.Status != Enums.ObjectStatusType.CreateStatus) list.Add(Drop(), deepMin);
if (this.Status != Enums.ObjectStatusType.DropStatus) list.Add(Create(), deepMax);
return list;
}
/// <summary>
/// Devuelve el schema de diferencias del Schema en formato SQL.
/// </summary>
public override SQLScriptList ToSqlDiff()
{
SQLScriptList listDiff = new SQLScriptList();
if (this.Status == Enums.ObjectStatusType.DropStatus)
{
listDiff.Add(ToSqlDrop(), 0, Enums.ScripActionType.DropPartitionFunction);
}
if (this.Status == Enums.ObjectStatusType.RebuildStatus)
{
listDiff.Add(ToSqlDrop() + ToSqlAdd(), 0, Enums.ScripActionType.AlterPartitionFunction);
}
if (this.Status == Enums.ObjectStatusType.AlterStatus)
listDiff.Add(ToSqlAlter(), 0, Enums.ScripActionType.AlterPartitionFunction);
if (this.Status == Enums.ObjectStatusType.CreateStatus)
{
listDiff.Add(ToSqlAdd(), 0, Enums.ScripActionType.AddPartitionFunction);
}
return listDiff;
}
private SQLScriptList RebuildDependencys(List<string> depends, int deepMin, int deepMax)
{
int newDeepMax = (deepMax != 0) ? deepMax + 1 : 0;
int newDeepMin = (deepMin != 0) ? deepMin - 1 : 0;
SQLScriptList list = new SQLScriptList();
for (int j = 0; j < depends.Count; j++)
{
ISchemaBase item = ((Database)Parent).Find(depends[j]);
if (item != null)
{
if ((item.Status != Enums.ObjectStatusType.CreateStatus) && (item.Status != Enums.ObjectStatusType.DropStatus))
{
if ((item.ObjectType != Enums.ObjectType.CLRStoreProcedure) && (item.ObjectType != Enums.ObjectType.Assembly) && (item.ObjectType != Enums.ObjectType.UserDataType) && (item.ObjectType != Enums.ObjectType.View) && (item.ObjectType != Enums.ObjectType.Function))
{
newDeepMin = 0;
newDeepMax = 0;
}
if (item.Status != Enums.ObjectStatusType.DropStatus)
{
if (!((item.Parent.HasState(Enums.ObjectStatusType.RebuildStatus)) && (item.ObjectType == Enums.ObjectType.Trigger)))
list.Add(item.Drop(), newDeepMin);
}
if ((this.Status != Enums.ObjectStatusType.DropStatus) && (item.Status != Enums.ObjectStatusType.CreateStatus))
list.Add(item.Create(), newDeepMax);
if (item.IsCodeType)
list.AddRange(RebuildDependencys(((ICode)item).DependenciesOut, newDeepMin, newDeepMax));
}
}
};
return list;
}
private SQLScriptList ToSQLChangeColumns()
{
Hashtable fields = new Hashtable();
SQLScriptList list = new SQLScriptList();
if ((this.Status == Enums.ObjectStatusType.AlterStatus) || (this.Status == Enums.ObjectStatusType.RebuildStatus))
{
foreach (ObjectDependency dependency in this.Dependencys)
{
ISchemaBase itemDepens = ((Database)this.Parent).Find(dependency.Name);
if (dependency.IsCodeType)
{
list.AddRange(((ICode)itemDepens).Rebuild());
}
if (dependency.Type == Enums.ObjectType.Table)
{
Column column = ((Table)itemDepens).Columns[dependency.ColumnName];
if ((column.Parent.Status != Enums.ObjectStatusType.DropStatus) && (column.Parent.Status != Enums.ObjectStatusType.CreateStatus) && ((column.Status != Enums.ObjectStatusType.CreateStatus)))
{
if (!fields.ContainsKey(column.FullName))
{
if (column.HasToRebuildOnlyConstraint)
column.Parent.Status = Enums.ObjectStatusType.RebuildDependenciesStatus;
list.AddRange(column.RebuildConstraint(true));
list.Add("ALTER TABLE " + column.Parent.FullName + " ALTER COLUMN " + column.ToSQLRedefine(null,0, "") + "\r\nGO\r\n", 0, Enums.ScripActionType.AlterColumn);
/*Si la columna va a ser eliminada o la tabla va a ser reconstruida, no restaura la columna*/
if ((column.Status != Enums.ObjectStatusType.DropStatus) && (column.Parent.Status != Enums.ObjectStatusType.RebuildStatus))
list.AddRange(column.Alter( Enums.ScripActionType.AlterColumnRestore));
fields.Add(column.FullName, column.FullName);
}
}
}
}
}
return list;
}
