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