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