private string ToSQLTemp(String TableName) { string sql = ""; sql += "CREATE TABLE [" + Owner + "].[" + TableName + "]\r\n(\r\n"; Columns.Sort(); for (int index = 0; index < Columns.Count; index++) { if (Columns[index].Status != Enums.ObjectStatusType.DropStatus) { sql += "\t" + Columns[index].ToSql(true); if (index != Columns.Count - 1) { sql += ","; } sql += "\r\n"; } } if (HasFileStream) { sql = sql.Substring(0, sql.Length - 2); sql += ",\r\n"; Constraints.ForEach(item => { if ((item.Type == Constraint.ConstraintType.Unique) && (item.Status != Enums.ObjectStatusType.DropStatus)) { item.Name = "Temp_XX_" + item.Name; sql += "\t" + item.ToSql() + ",\r\n"; item.SetWasInsertInDiffList(Enums.ScripActionType.AddConstraint); item.Name = item.Name.Substring(8, item.Name.Length - 8); } }); sql = sql.Substring(0, sql.Length - 3) + "\r\n"; } else { sql += "\r\n"; if (!String.IsNullOrEmpty(CompressType)) { sql += "WITH (DATA_COMPRESSION = " + CompressType + ")\r\n"; } } sql += ")"; if (!String.IsNullOrEmpty(FileGroup)) { sql += " ON [" + FileGroup + "]"; } if (!String.IsNullOrEmpty(FileGroupText)) { if (HasBlobColumn) { sql += " TEXTIMAGE_ON [" + FileGroupText + "]"; } } if ((!String.IsNullOrEmpty(FileGroupStream)) && (HasFileStream)) { sql += " FILESTREAM_ON [" + FileGroupStream + "]"; } sql += "\r\n"; sql += "GO\r\n"; return(sql); }
private string ToSQLTableRebuild() { string sql = ""; string tempTable = "Temp" + Name; string listColumns = ""; string listValues = ""; Boolean IsIdentityNew = false; foreach (Column column in Columns) { if ((column.Status != Enums.ObjectStatusType.DropStatus) && !((column.Status == Enums.ObjectStatusType.CreateStatus) && column.IsNullable)) { if ((!column.IsComputed) && (!column.Type.ToLower().Equals("timestamp"))) { /*Si la nueva columna a agregar es XML, no se inserta ese campo y debe ir a la coleccion de Warnings*/ /*Si la nueva columna a agregar es Identity, tampoco se debe insertar explicitamente*/ if ( !((column.Status == Enums.ObjectStatusType.CreateStatus) && ((column.Type.ToLower().Equals("xml") || (column.IsIdentity))))) { listColumns += "[" + column.Name + "],"; if (column.HasToForceValue) { if (column.HasState(Enums.ObjectStatusType.UpdateStatus)) { listValues += "ISNULL([" + column.Name + "]," + column.DefaultForceValue + "),"; } else { listValues += column.DefaultForceValue + ","; } } else { listValues += "[" + column.Name + "],"; } } else { if (column.IsIdentity) { IsIdentityNew = true; } } } } } if (!String.IsNullOrEmpty(listColumns)) { listColumns = listColumns.Substring(0, listColumns.Length - 1); listValues = listValues.Substring(0, listValues.Length - 1); sql += ToSQLTemp(tempTable) + "\r\n"; if ((HasIdentityColumn) && (!IsIdentityNew)) { sql += "SET IDENTITY_INSERT [" + Owner + "].[" + tempTable + "] ON\r\n"; } sql += "INSERT INTO [" + Owner + "].[" + tempTable + "] (" + listColumns + ")" + " SELECT " + listValues + " FROM " + FullName + "\r\n"; if ((HasIdentityColumn) && (!IsIdentityNew)) { sql += "SET IDENTITY_INSERT [" + Owner + "].[" + tempTable + "] OFF\r\nGO\r\n\r\n"; } sql += "DROP TABLE " + FullName + "\r\nGO\r\n"; if (HasFileStream) { Constraints.ForEach(item => { if ((item.Type == Constraint.ConstraintType.Unique) && (item.Status != Enums.ObjectStatusType.DropStatus)) { sql += "EXEC sp_rename N'[" + Owner + "].[Temp_XX_" + item.Name + "]',N'" + item.Name + "', 'OBJECT'\r\nGO\r\n"; } }); } sql += "EXEC sp_rename N'[" + Owner + "].[" + tempTable + "]',N'" + Name + "', 'OBJECT'\r\nGO\r\n\r\n"; sql += OriginalTable.Options.ToSql(); } else { sql = ""; } return(sql); }
/// <summary> /// Devuelve el schema de la tabla en formato SQL. /// </summary> public string ToSql(Boolean showFK) { string sql = ""; string sqlPK = ""; string sqlUC = ""; string sqlFK = ""; if (columns.Count > 0) { sql += "CREATE TABLE " + FullName + "\r\n(\r\n"; sql += columns.ToSql(); if (Constraints.Count > 0) { sql += ",\r\n"; Constraints.ForEach(item => { if (item.Type == Constraint.ConstraintType.PrimaryKey) { sqlPK += "\t" + item.ToSql() + ",\r\n"; } if (item.Type == Constraint.ConstraintType.Unique) { sqlUC += "\t" + item.ToSql() + ",\r\n"; } if (showFK) { if (item.Type == Constraint.ConstraintType.ForeignKey) { sqlFK += "\t" + item.ToSql() + ",\r\n"; } } }); sql += sqlPK + sqlUC + sqlFK; sql = sql.Substring(0, sql.Length - 3) + "\r\n"; } else { sql += "\r\n"; if (!String.IsNullOrEmpty(CompressType)) { sql += "WITH (DATA_COMPRESSION = " + CompressType + ")\r\n"; } } sql += ")"; if (!String.IsNullOrEmpty(FileGroup)) { sql += " ON [" + FileGroup + "]"; } if (!String.IsNullOrEmpty(FileGroupText)) { if (HasBlobColumn) { sql += " TEXTIMAGE_ON [" + FileGroupText + "]"; } } if ((!String.IsNullOrEmpty(FileGroupStream)) && (HasFileStream)) { sql += " FILESTREAM_ON [" + FileGroupStream + "]"; } sql += "\r\n"; sql += "GO\r\n"; Constraints.ForEach(item => { if (item.Type == Constraint.ConstraintType.Check) { sql += item.ToSqlAdd() + "\r\n"; } }); if (HasChangeTracking) { sql += ToSqlChangeTracking(); } sql += Indexes.ToSql(); sql += FullTextIndex.ToSql(); sql += Options.ToSql(); sql += Triggers.ToSql(); } return(sql); }
/* * 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); }
/// <summary> /// Devuelve el schema de la tabla en formato SQL. /// </summary> public string ToSql(Boolean showFK) { Database database = null; ISchemaBase current = this; while (database == null && current.Parent != null) { database = current.Parent as Database; current = current.Parent; } var isAzure10 = database.Info.Version == DatabaseInfo.VersionTypeEnum.SQLServerAzure10; string sql = ""; string sqlPK = ""; string sqlUC = ""; string sqlFK = ""; if (Columns.Count > 0) { sql += "CREATE TABLE " + FullName + "\r\n(\r\n"; sql += Columns.ToSql(); if (Constraints.Count > 0) { sql += ",\r\n"; Constraints.ForEach(item => { if (item.Type == Constraint.ConstraintType.PrimaryKey) { sqlPK += "\t" + item.ToSql() + ",\r\n"; } if (item.Type == Constraint.ConstraintType.Unique) { sqlUC += "\t" + item.ToSql() + ",\r\n"; } if (showFK) { if (item.Type == Constraint.ConstraintType.ForeignKey) { sqlFK += "\t" + item.ToSql() + ",\r\n"; } } }); sql += sqlPK + sqlUC + sqlFK; sql = sql.Substring(0, sql.Length - 3) + "\r\n"; } else { sql += "\r\n"; if (!String.IsNullOrEmpty(CompressType)) { sql += "WITH (DATA_COMPRESSION = " + CompressType + ")\r\n"; } } sql += ")"; if (!isAzure10) { if (!String.IsNullOrEmpty(FileGroup)) { sql += " ON [" + FileGroup + "]"; } if (!String.IsNullOrEmpty(FileGroupText)) { if (HasBlobColumn) { sql += " TEXTIMAGE_ON [" + FileGroupText + "]"; } } if ((!String.IsNullOrEmpty(FileGroupStream)) && (HasFileStream)) { sql += " FILESTREAM_ON [" + FileGroupStream + "]"; } } sql += "\r\n"; sql += "GO\r\n"; Constraints.ForEach(item => { if (item.Type == Constraint.ConstraintType.Check) { sql += item.ToSqlAdd() + "\r\n"; } }); if (HasChangeTracking) { sql += ToSqlChangeTracking(); } sql += Indexes.ToSql(); sql += FullTextIndex.ToSql(); sql += Options.ToSql(); sql += Triggers.ToSql(); } return(sql); }