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()
{
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;
}
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;
}
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()
{
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;
}
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;
}
/// <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;
}
/// <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 SQLScriptList ToSQLDiff()
{
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.DropTrigger);
}
if (this[index].Status == StatusEnum.ObjectStatusType.CreateStatus)
{
list.Add(this[index].ToSQL(), parent.DependenciesCount, StatusEnum.ScripActionType.AddTrigger);
}
if (this[index].Status == StatusEnum.ObjectStatusType.AlterStatus)
{
list.Add(this[index].ToSQLDrop(), parent.DependenciesCount, StatusEnum.ScripActionType.DropTrigger);
list.Add(this[index].ToSQL(), parent.DependenciesCount, StatusEnum.ScripActionType.AddTrigger);
}
}
return(list);
}
/// <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);
}
public override SQLScriptList ToSqlDiff(System.Collections.Generic.ICollection <ISchemaBase> schemas)
{
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 SQLScriptList ToSQLDiff()
{
SQLScriptList list = new SQLScriptList();
int index;
for (index = 0; index < this.Count; index++)
{
StatusEnum.ScripActionType actionDrop = StatusEnum.ScripActionType.DropConstraint;
StatusEnum.ScripActionType actionAdd = StatusEnum.ScripActionType.AddConstraint;
if (this[index].Type == Constraint.ConstraintType.ForeignKey)
{
actionDrop = StatusEnum.ScripActionType.DropConstraintFK;
actionAdd = StatusEnum.ScripActionType.AddConstraintFK;
}
if (this[index].Type == Constraint.ConstraintType.PrimaryKey)
{
actionAdd = StatusEnum.ScripActionType.AddConstraintPK;
actionDrop = StatusEnum.ScripActionType.DropConstraintPK;
}
if (this[index].Status == StatusEnum.ObjectStatusType.DropStatus)
{
list.Add(this[index].ToSQLDrop(), parent.DependenciesCount, actionDrop);
}
if (this[index].Status == StatusEnum.ObjectStatusType.CreateStatus)
{
list.Add(this[index].ToSQLAdd(), parent.DependenciesCount, actionAdd);
}
if (this[index].Status == StatusEnum.ObjectStatusType.AlterStatus)
{
list.Add(this[index].ToSQLDrop(), parent.DependenciesCount, actionDrop);
list.Add(this[index].ToSQLAdd(), parent.DependenciesCount, actionAdd);
}
}
return(list);
}
public override SQLScriptList ToSqlDiff(System.Collections.Generic.ICollection <ISchemaBase> schemas)
{
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
{
return(null);
}
}
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)
{
return(null);
}
}
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;
}
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 void OriginHasExtraColumn_NotChangedColumnSelected_ShouldBeEmptyScript()
{
int idStorage = 1;
System.Func <int> getId = new Func <int>(() => ++ idStorage);
Database originDatabase = new Database();
originDatabase.Info = new DatabaseInfo()
{
Collation = "SQL_Latin1_General_CP1_CI_AS"
};
originDatabase.Id = getId();
originDatabase.Options = new Options.SqlOption();
Table originTable = new Table(originDatabase);
originTable.Name = "Example";
originTable.Id = getId();
var originColumn1 = new Column(originTable)
{
Name = "Test",
Type = "int",
Id = getId()
};
var originColumn2 = new Column(originTable)
{
Name = "Test2",
Type = "varchar(20)",
Id = getId()
};
var originColumn3 = new Column(originTable)
{
Name = "Test3",
Type = "bigint",
Id = getId()
};
originTable.Columns.Add(originColumn1);
originTable.Columns.Add(originColumn3);
originTable.Columns.Add(originColumn2);
originDatabase.Tables.Add(originTable);
Database destinationDatabase = new Database();
destinationDatabase.Info = new DatabaseInfo()
{
Collation = "SQL_Latin1_General_CP1_CI_AS"
};
destinationDatabase.Id = getId();
destinationDatabase.Options = new Options.SqlOption();
Table destinationTable = new Table(destinationDatabase);
destinationTable.Name = "Example";
destinationTable.Id = getId();
var destinationColumn1 = new Column(destinationTable)
{
Name = "Test",
Type = "int",
Id = getId()
};
var destinationColumn3 = new Column(destinationTable)
{
Name = "Test3",
Type = "bigint",
Id = getId()
};
destinationTable.Columns.Add(destinationColumn1);
destinationTable.Columns.Add(destinationColumn3);
destinationDatabase.Tables.Add(destinationTable);
originTable.OriginalTable = (Table)originTable.Clone((Database)originTable.Parent);
new CompareColumns().GenerateDifferences <Table>(originTable.Columns, destinationTable.Columns);
SQLScriptList sqlList = originTable.ToSqlDiff(new List <ISchemaBase>()
{
originColumn3
});
string sql = sqlList.ToSQL();
Assert.AreEqual("", sql);
}
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 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 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;
}
/*
* private SQLScriptList BuildSQLFileGroup()
* {
* var listDiff = new SQLScriptList();
*
* Boolean found = false;
* Index clustered = Indexes.Find(item => item.Type == Index.IndexTypeEnum.Clustered);
* if (clustered == null)
* {
* foreach (Constraint cons in Constraints)
* {
* if (cons.Index.Type == Index.IndexTypeEnum.Clustered)
* {
* listDiff.Add(cons.ToSqlDrop(FileGroup), dependenciesCount, ScripActionType.DropConstraint);
* listDiff.Add(cons.ToSqlAdd(), dependenciesCount, ScripActionType.AddConstraint);
* found = true;
* }
* }
* if (!found)
* {
* Status = ObjectStatusType.RebuildStatus;
* listDiff = ToSqlDiff();
* }
* }
* else
* {
* listDiff.Add(clustered.ToSqlDrop(FileGroup), dependenciesCount, ScripActionType.DropIndex);
* listDiff.Add(clustered.ToSqlAdd(), dependenciesCount, ScripActionType.AddIndex);
* }
* return listDiff;
* }
*/
/// <summary>
/// Devuelve el schema de diferencias de la tabla en formato SQL.
/// </summary>
public override SQLScriptList ToSqlDiff(ICollection <ISchemaBase> schemas)
{
var listDiff = new SQLScriptList();
if (Status != ObjectStatus.Original)
{
if (((Database)Parent).Options.Ignore.FilterTable)
{
RootParent.ActionMessage.Add(this);
}
}
if (Status == ObjectStatus.Drop)
{
if (((Database)Parent).Options.Ignore.FilterTable)
{
listDiff.Add(ToSqlDrop(), dependenciesCount, ScriptAction.DropTable);
listDiff.AddRange(ToSQLDropFKBelow());
}
}
if (Status == ObjectStatus.Create)
{
var sql = new StringBuilder();
Constraints.ForEach(item =>
{
if (item.Type == Constraint.ConstraintType.ForeignKey)
{
sql.AppendLine(item.ToSqlAdd());
}
});
listDiff.Add(ToSql(false), dependenciesCount, ScriptAction.AddTable);
listDiff.Add(sql.ToString(), dependenciesCount, ScriptAction.AddConstraintFK);
}
if (HasState(ObjectStatus.RebuildDependencies))
{
GenerateDependencies();
listDiff.AddRange(ToSQLDropDependencies());
listDiff.AddRange(Columns.ToSqlDiff(schemas));
listDiff.AddRange(ToSQLCreateDependencies());
listDiff.AddRange(Constraints.ToSqlDiff());
listDiff.AddRange(Indexes.ToSqlDiff());
listDiff.AddRange(Options.ToSqlDiff());
listDiff.AddRange(Triggers.ToSqlDiff());
listDiff.AddRange(CLRTriggers.ToSqlDiff());
listDiff.AddRange(FullTextIndex.ToSqlDiff());
}
if (HasState(ObjectStatus.Alter))
{
listDiff.AddRange(Columns.ToSqlDiff(schemas));
listDiff.AddRange(Constraints.ToSqlDiff());
listDiff.AddRange(Indexes.ToSqlDiff());
listDiff.AddRange(Options.ToSqlDiff());
listDiff.AddRange(Triggers.ToSqlDiff());
listDiff.AddRange(CLRTriggers.ToSqlDiff());
listDiff.AddRange(FullTextIndex.ToSqlDiff());
}
if (HasState(ObjectStatus.Rebuild))
{
GenerateDependencies();
listDiff.AddRange(ToSQLRebuild());
listDiff.AddRange(Columns.ToSqlDiff());
listDiff.AddRange(Constraints.ToSqlDiff());
listDiff.AddRange(Indexes.ToSqlDiff());
listDiff.AddRange(Options.ToSqlDiff());
//Como recrea la tabla, solo pone los nuevos triggers, por eso va ToSQL y no ToSQLDiff
listDiff.Add(Triggers.ToSql(), dependenciesCount, ScriptAction.AddTrigger);
listDiff.Add(CLRTriggers.ToSql(), dependenciesCount, ScriptAction.AddTrigger);
listDiff.AddRange(FullTextIndex.ToSqlDiff());
}
if (HasState(ObjectStatus.Disabled))
{
listDiff.Add(ToSqlChangeTracking(), 0, ScriptAction.AlterTableChangeTracking);
}
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()
{
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(@"/*
数据库差异脚本
脚本创建于： {1}\{2} 时间： {3} {4}
创建人：{5}
参考数据库：{6} 地址：{7}
目标数据库：{8} 地址：{9}
*/
",
System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString(),
Environment.UserDomainName,
Environment.MachineName,
DateTime.Now.ToShortDateString(),
DateTime.Now.ToLongTimeString(),
Environment.UserName,
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);
}
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;
}
private SQLScriptList RebuildDependencys(Table table)
{
var list = new SQLScriptList();
List<ISchemaBase> items = ((Database) table.Parent).Dependencies.Find(table.Id);
items.ForEach(item =>
{
ISchemaBase realItem = ((Database) table.Parent).Find(item.FullName);
if (realItem.IsCodeType)
list.AddRange(((ICode) realItem).Rebuild());
});
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;
}
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;
}
/// <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;
}
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(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;
}
/*
* private SQLScriptList BuildSQLFileGroup()
* {
* var listDiff = new SQLScriptList();
*
* Boolean found = false;
* Index clustered = Indexes.Find(item => item.Type == Index.IndexTypeEnum.Clustered);
* if (clustered == null)
* {
* foreach (Constraint cons in Constraints)
* {
* if (cons.Index.Type == Index.IndexTypeEnum.Clustered)
* {
* listDiff.Add(cons.ToSqlDrop(FileGroup), dependenciesCount, Enums.ScripActionType.DropConstraint);
* listDiff.Add(cons.ToSqlAdd(), dependenciesCount, Enums.ScripActionType.AddConstraint);
* found = true;
* }
* }
* if (!found)
* {
* Status = Enums.ObjectStatusType.RebuildStatus;
* listDiff = ToSqlDiff();
* }
* }
* else
* {
* listDiff.Add(clustered.ToSqlDrop(FileGroup), dependenciesCount, Enums.ScripActionType.DropIndex);
* listDiff.Add(clustered.ToSqlAdd(), dependenciesCount, Enums.ScripActionType.AddIndex);
* }
* return listDiff;
* }
*/
/// <summary>
/// Devuelve el schema de diferencias de la tabla en formato SQL.
/// </summary>
public override SQLScriptList ToSqlDiff()
{
var listDiff = new SQLScriptList();
if (Status != Enums.ObjectStatusType.OriginalStatus)
{
if (((Database)Parent).Options.Ignore.FilterTable)
{
RootParent.ActionMessage.Add(this);
}
}
if (Status == Enums.ObjectStatusType.DropStatus)
{
if (((Database)Parent).Options.Ignore.FilterTable)
{
listDiff.Add(ToSqlDrop(), dependenciesCount, Enums.ScripActionType.DropTable);
listDiff.AddRange(ToSQLDropFKBelow());
}
}
if (Status == Enums.ObjectStatusType.CreateStatus)
{
string sql = "";
Constraints.ForEach(item =>
{
if (item.Type == Constraint.ConstraintType.ForeignKey)
{
sql += item.ToSqlAdd() + "\r\n";
}
});
listDiff.Add(ToSql(false), dependenciesCount, Enums.ScripActionType.AddTable);
listDiff.Add(sql, dependenciesCount, Enums.ScripActionType.AddConstraintFK);
}
if (HasState(Enums.ObjectStatusType.RebuildDependenciesStatus))
{
GenerateDependencis();
listDiff.AddRange(ToSQLDropDependencis());
listDiff.AddRange(columns.ToSqlDiff());
listDiff.AddRange(ToSQLCreateDependencis());
listDiff.AddRange(Constraints.ToSqlDiff());
listDiff.AddRange(Indexes.ToSqlDiff());
listDiff.AddRange(Options.ToSqlDiff());
listDiff.AddRange(Triggers.ToSqlDiff());
listDiff.AddRange(CLRTriggers.ToSqlDiff());
listDiff.AddRange(FullTextIndex.ToSqlDiff());
}
if (HasState(Enums.ObjectStatusType.AlterStatus))
{
listDiff.AddRange(columns.ToSqlDiff());
listDiff.AddRange(Constraints.ToSqlDiff());
listDiff.AddRange(Indexes.ToSqlDiff());
listDiff.AddRange(Options.ToSqlDiff());
listDiff.AddRange(Triggers.ToSqlDiff());
listDiff.AddRange(CLRTriggers.ToSqlDiff());
listDiff.AddRange(FullTextIndex.ToSqlDiff());
}
if (HasState(Enums.ObjectStatusType.RebuildStatus))
{
GenerateDependencis();
listDiff.AddRange(ToSQLRebuild());
listDiff.AddRange(columns.ToSqlDiff());
listDiff.AddRange(Constraints.ToSqlDiff());
listDiff.AddRange(Indexes.ToSqlDiff());
listDiff.AddRange(Options.ToSqlDiff());
//Como recrea la tabla, solo pone los nuevos triggers, por eso va ToSQL y no ToSQLDiff
listDiff.Add(Triggers.ToSql(), dependenciesCount, Enums.ScripActionType.AddTrigger);
listDiff.Add(CLRTriggers.ToSql(), dependenciesCount, Enums.ScripActionType.AddTrigger);
listDiff.AddRange(FullTextIndex.ToSqlDiff());
}
if (HasState(Enums.ObjectStatusType.DisabledStatus))
{
listDiff.Add(ToSqlChangeTracking(), 0, Enums.ScripActionType.AlterTableChangeTracking);
}
return(listDiff);
}
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;
}
public override SQLScriptList ToSqlDiff()
{
string sqlDrop = "";
string sqlAdd = "";
string sqlCons = "";
string sqlBinds = "";
SQLScriptList list = new SQLScriptList();
if (Parent.Status != Enums.ObjectStatusType.RebuildStatus)
{
this.ForEach(item =>
{
if (item.HasState(Enums.ObjectStatusType.DropStatus))
{
if (item.DefaultConstraint != null)
{
list.Add(item.DefaultConstraint.Drop());
}
/*Si la columna formula debe ser eliminada y ya fue efectuada la operacion en otro momento, no
* se borra nuevamente*/
if (!item.GetWasInsertInDiffList(Enums.ScripActionType.AlterColumnFormula))
{
sqlDrop += "[" + item.Name + "],";
}
}
if (item.HasState(Enums.ObjectStatusType.CreateStatus))
{
sqlAdd += "\r\n" + item.ToSql(true) + ",";
}
if ((item.HasState(Enums.ObjectStatusType.AlterStatus) || (item.HasState(Enums.ObjectStatusType.RebuildDependenciesStatus))))
{
if ((!item.Parent.HasState(Enums.ObjectStatusType.RebuildDependenciesStatus) || (!item.Parent.HasState(Enums.ObjectStatusType.RebuildStatus))))
{
list.AddRange(item.RebuildSchemaBindingDependencies());
}
list.AddRange(item.RebuildConstraint(false));
list.AddRange(item.RebuildDependencies());
list.AddRange(item.Alter(Enums.ScripActionType.AlterTable));
}
if (item.HasState(Enums.ObjectStatusType.UpdateStatus))
{
list.Add("UPDATE " + Parent.FullName + " SET [" + item.Name + "] = " + item.DefaultForceValue + " WHERE [" + item.Name + "] IS NULL\r\nGO\r\n", 0, Enums.ScripActionType.UpdateTable);
}
if (item.HasState(Enums.ObjectStatusType.BindStatus))
{
if (item.Rule.Id != 0)
{
sqlBinds += item.Rule.ToSQLAddBind();
}
if (item.Rule.Id == 0)
{
sqlBinds += item.Rule.ToSQLAddUnBind();
}
}
if (item.DefaultConstraint != null)
{
list.AddRange(item.DefaultConstraint.ToSqlDiff());
}
});
if (!String.IsNullOrEmpty(sqlDrop))
{
sqlDrop = "ALTER TABLE " + Parent.FullName + " DROP COLUMN " + sqlDrop.Substring(0, sqlDrop.Length - 1) + "\r\nGO\r\n";
}
if (!String.IsNullOrEmpty(sqlAdd))
{
sqlAdd = "ALTER TABLE " + Parent.FullName + " ADD " + sqlAdd.Substring(0, sqlAdd.Length - 1) + "\r\nGO\r\n";
}
if (!String.IsNullOrEmpty(sqlDrop + sqlAdd + sqlCons + sqlBinds))
{
list.Add(sqlDrop + sqlAdd + sqlBinds, 0, Enums.ScripActionType.AlterTable);
}
}
else
{
this.ForEach(item =>
{
if (item.Status != Enums.ObjectStatusType.OriginalStatus)
{
item.RootParent.ActionMessage[item.Parent.FullName].Add(item);
}
});
}
return(list);
}
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 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;
}
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);
}
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;
}
public override SQLScriptList ToSqlDiff(System.Collections.Generic.ICollection <ISchemaBase> schemas)
{
Boolean ToSqlDiffOption = false; ////zjh
string sqlDrop = "";
string sqlAdd = "";
string sqlCons = "";
string sqlBinds = "";
string sqlDropGO = ""; ////zjh
string sqlAddGO = "";
string sqlConsGO = "";
string sqlBindsGO = "";
SQLScriptList list = new SQLScriptList();
if (Parent.Status != ObjectStatus.Rebuild)
{
this.ForEach(item =>
{
bool isIncluded = schemas.Count == 0;
if (!isIncluded)
{
foreach (var selectedSchema in schemas)
{
if (selectedSchema.Id == item.Id)
{
isIncluded = true;
break;
}
}
}
if (isIncluded)
{
if (item.HasState(ObjectStatus.Drop))
{
if (item.DefaultConstraint != null)
{
list.Add(item.DefaultConstraint.Drop());
}
/*Si la columna formula debe ser eliminada y ya fue efectuada la operacion en otro momento, no
* se borra nuevamente*/
if (!item.GetWasInsertInDiffList(ScriptAction.AlterColumnFormula))
{
sqlDrop += "[" + item.Name + "],";
}
}
if (item.HasState(ObjectStatus.Create))
{
sqlAdd += "\r\n" + item.ToSql(true) + ",";
}
if ((item.HasState(ObjectStatus.Alter) || (item.HasState(ObjectStatus.RebuildDependencies))))
{
if ((!item.Parent.HasState(ObjectStatus.RebuildDependencies) || (!item.Parent.HasState(ObjectStatus.Rebuild))))
{
list.AddRange(item.RebuildSchemaBindingDependencies());
}
list.AddRange(item.RebuildConstraint(false));
list.AddRange(item.RebuildDependencies());
list.AddRange(item.Alter(ScriptAction.AlterTable));
}
if (item.HasState(ObjectStatus.Update))
{
list.Add("UPDATE " + Parent.FullName + " SET [" + item.Name + "] = " + item.DefaultForceValue + " WHERE [" + item.Name + "] IS NULL\r\nGO\r\n", 0, ScriptAction.UpdateTable);
}
if (item.HasState(ObjectStatus.Bind))
{
if (item.Rule.Id != 0)
{
sqlBinds += item.Rule.ToSQLAddBind();
}
if (item.Rule.Id == 0)
{
sqlBinds += item.Rule.ToSQLAddUnBind();
}
}
if (item.DefaultConstraint != null)
{
list.AddRange(item.DefaultConstraint.ToSqlDiff(schemas));
}
}
if (ToSqlDiffOption == false) ////zjh
{
if (sqlAdd.Contains("xxxxxxxx"))
{
//Detect
}
if (!String.IsNullOrEmpty(sqlDrop))
{
sqlDropGO = "ALTER TABLE " + Parent.FullName + " DROP COLUMN " + sqlDrop.Substring(0, sqlDrop.Length - 1) + "\r\nGO\r\n";
}
if (!String.IsNullOrEmpty(sqlAdd))
{
sqlAddGO = "ALTER TABLE " + Parent.FullName + " ADD " + sqlAdd.Substring(0, sqlAdd.Length - 1) + "\r\nGO\r\n";
}
if (!String.IsNullOrEmpty(sqlDropGO + sqlAddGO + sqlCons + sqlBinds))
{
list.Add(sqlDropGO + sqlAddGO + sqlBinds, 0, ScriptAction.AlterTable);
}
sqlDrop = String.Empty;
sqlAdd = String.Empty;
sqlDropGO = String.Empty;
sqlAddGO = String.Empty;
}
});
if (ToSqlDiffOption == false) ////zjh
{
sqlBinds = String.Empty;
sqlCons = String.Empty;
sqlDropGO = String.Empty;
sqlAddGO = String.Empty;
sqlCons = String.Empty;
sqlBinds = String.Empty;
}
if (!String.IsNullOrEmpty(sqlDrop))
{
sqlDropGO = "ALTER TABLE " + Parent.FullName + " DROP COLUMN " + sqlDrop.Substring(0, sqlDrop.Length - 1) + "\r\nGO\r\n";
}
if (!String.IsNullOrEmpty(sqlAdd))
{
sqlAddGO = "ALTER TABLE " + Parent.FullName + " ADD " + sqlAdd.Substring(0, sqlAdd.Length - 1) + "\r\nGO\r\n";
}
if (!String.IsNullOrEmpty(sqlDropGO + sqlAddGO + sqlCons + sqlBinds))
{
list.Add(sqlDropGO + sqlAddGO + sqlBinds, 0, ScriptAction.AlterTable);
}
}
else
{
this.ForEach(item =>
{
if (item.Status != ObjectStatus.Original)
{
item.RootParent.ActionMessage[item.Parent.FullName].Add(item);
}
});
}
return(list);
}
