public void Fill(Database database, string connectionString)
{
//not supported in azure yet
if (database.Info.Version == DatabaseInfo.VersionTypeEnum.SQLServerAzure10) return;
int parentId = 0;
bool change = false;
Table parent = null;
root.RaiseOnReading(new ProgressEventArgs("Reading FullText Index...", Constants.READING_INDEXES));
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(FullTextIndexSQLCommand.Get(database.Info.Version), conn))
{
conn.Open();
command.CommandTimeout = 0;
using (SqlDataReader reader = command.ExecuteReader())
{
FullTextIndex item = null;
while (reader.Read())
{
root.RaiseOnReadingOne(reader["Name"]);
if (parentId != (int)reader["object_id"])
{
parentId = (int)reader["object_id"];
parent = database.Tables.Find(parentId);
change = true;
}
else
change = false;
if (change)
{
item = new FullTextIndex(parent);
item.Name = reader["Name"].ToString();
item.Owner = parent.Owner;
item.FullText = reader["FullTextCatalogName"].ToString();
item.Index = reader["IndexName"].ToString();
item.IsDisabled = !(bool)reader["is_enabled"];
item.ChangeTrackingState = reader["ChangeTracking"].ToString();
if (database.Info.Version == DatabaseInfo.VersionTypeEnum.SQLServer2008)
item.FileGroup = reader["FileGroupName"].ToString();
((Table)parent).FullTextIndex.Add(item);
}
FullTextIndexColumn ccon = new FullTextIndexColumn();
ccon.ColumnName = reader["ColumnName"].ToString();
ccon.Language = reader["LanguageName"].ToString();
item.Columns.Add(ccon);
}
}
}
}
}
public override ISchemaBase Clone(ISchemaBase parent)
{
FullTextIndex index = new FullTextIndex(parent);
index.ChangeTrackingState = this.ChangeTrackingState;
index.FullText = this.FullText;
index.Name = this.Name;
index.FileGroup = this.FileGroup;
index.Id = this.Id;
index.Index = this.Index;
index.IsDisabled = this.IsDisabled;
index.Status = this.Status;
index.Owner = this.Owner;
index.Columns = this.Columns;
this.ExtendedProperties.ForEach(item => index.ExtendedProperties.Add(item));
return index;
}
public override ISchemaBase Clone(ISchemaBase parent)
{
FullTextIndex index = new FullTextIndex(parent);
index.ChangeTrackingState = this.ChangeTrackingState;
index.FullText = this.FullText;
index.Name = this.Name;
index.FileGroup = this.FileGroup;
index.Id = this.Id;
index.Index = this.Index;
index.IsDisabled = this.IsDisabled;
index.Status = this.Status;
index.Owner = this.Owner;
index.Columns = this.Columns;
this.ExtendedProperties.ForEach(item => index.ExtendedProperties.Add(item));
return(index);
}
public Boolean Compare(FullTextIndex destino)
{
if (destino == null)
{
throw new ArgumentNullException("destino");
}
if (!this.ChangeTrackingState.Equals(destino.ChangeTrackingState))
{
return(false);
}
if (!this.FullText.Equals(destino.FullText))
{
return(false);
}
if (!this.Index.Equals(destino.Index))
{
return(false);
}
if (this.IsDisabled != destino.IsDisabled)
{
return(false);
}
if (this.Columns.Count != destino.Columns.Count)
{
return(false);
}
if (this.Columns.Exists(item => { return(!destino.Columns.Exists(item2 => item2.ColumnName.Equals(item.ColumnName))); }))
{
return(false);
}
if (destino.Columns.Exists(item => { return(!this.Columns.Exists(item2 => item2.ColumnName.Equals(item.ColumnName))); }))
{
return(false);
}
return(true);
}
/*
* 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);
}
public Boolean Compare(FullTextIndex destino)
{
if (destino == null) throw new ArgumentNullException("destino");
if (!this.ChangeTrackingState.Equals(destino.ChangeTrackingState)) return false;
if (!this.FullText.Equals(destino.FullText)) return false;
if (!this.Index.Equals(destino.Index)) return false;
if (this.IsDisabled != destino.IsDisabled) return false;
if (this.Columns.Count != destino.Columns.Count) return false;
if (this.Columns.Exists(item => { return !destino.Columns.Exists(item2 => item2.ColumnName.Equals(item.ColumnName)); })) return false;
if (destino.Columns.Exists(item => { return !this.Columns.Exists(item2 => item2.ColumnName.Equals(item.ColumnName)); })) return false;
return true;
}