bool ColumnsChanged(DiffTable dif, Index mix)
{
if (this.Columns.Count != mix.Columns.Length)
return true;
var difColumns = this.Columns.Select(cn => dif.Columns.Values.SingleOrDefault(dc => dc.Name == cn)).ToList();
var perfect = difColumns.ZipOrDefault(mix.Columns, (dc, mc) => dc != null && mc != null && dc.ColumnEquals(mc, ignorePrimaryKey: true)).All(a => a);
return !perfect;
}
private static DiffIndexType? GetIndexType(Index mix)
{
if (mix is UniqueIndex && ((UniqueIndex)mix).ViewName != null)
return null;
if (mix is PrimaryClusteredIndex)
return DiffIndexType.Clustered;
return DiffIndexType.NonClustered;
}
public static SqlPreCommand CreateIndex(Index index)
{
string columns = index.Columns.ToString(c => c.Name.SqlEscape(), ", ");
if (!(index is UniqueIndex))
{
return new SqlPreCommandSimple("CREATE INDEX {0} ON {1}({2})".Formato(
index.IndexName,
index.Table.Name,
columns));
}
else
{
var uIndex = (UniqueIndex)index;
if (string.IsNullOrEmpty(uIndex.Where))
{
return new SqlPreCommandSimple("CREATE {0}INDEX {1} ON {2}({3})".Formato(
uIndex is UniqueIndex ? "UNIQUE " : null,
uIndex.IndexName,
uIndex.Table.Name,
columns));
}
if (uIndex.ViewName != null)
{
ObjectName viewName = new ObjectName(uIndex.Table.Name.Schema, uIndex.ViewName);
SqlPreCommandSimple viewSql = new SqlPreCommandSimple(@"CREATE VIEW {0} WITH SCHEMABINDING AS SELECT {1} FROM {2} WHERE {3}"
.Formato(viewName, columns, uIndex.Table.Name.ToStringDbo(), uIndex.Where)) { AddGo = true };
SqlPreCommandSimple indexSql = new SqlPreCommandSimple(@"CREATE UNIQUE CLUSTERED INDEX {0} ON {1}({2})"
.Formato(uIndex.IndexName, viewName, uIndex.Columns.ToString(c => c.Name.SqlEscape(), ", ")));
return SqlPreCommand.Combine(Spacing.Simple, viewSql, indexSql);
}
else
{
return new SqlPreCommandSimple("CREATE UNIQUE INDEX {0} ON {1}({2}) WHERE {3}".Formato(
uIndex.IndexName,
uIndex.Table.Name,
columns, uIndex.Where));
}
}
}
internal bool IndexEquals(DiffTable dif, Index mix)
{
if (this.ViewName != (mix as UniqueIndex)?.ViewName)
return false;
if (this.ColumnsChanged(dif, mix))
return false;
if (this.IsPrimary != mix is PrimaryClusteredIndex)
return false;
if (this.Type != GetIndexType(mix))
return false;
return true;
}
public static SqlPreCommand CreateIndex(Index index)
{
string columns = index.Columns.ToString(c => c.Name.SqlEscape(), ", ");
if (index is PrimaryClusteredIndex)
{
return new SqlPreCommandSimple("ALTER TABLE {0} ADD CONSTRAINT {1} PRIMARY KEY CLUSTERED({2})".FormatWith(
index.Table.Name,
index.IndexName,
columns));
}
if (!(index is UniqueIndex))
{
return new SqlPreCommandSimple("CREATE INDEX {0} ON {1}({2})".FormatWith(
index.IndexName,
index.Table.Name,
columns));
}
var uIndex = (UniqueIndex)index;
if (string.IsNullOrEmpty(uIndex.Where))
{
return new SqlPreCommandSimple("CREATE {0}INDEX {1} ON {2}({3})".FormatWith(
uIndex is UniqueIndex ? "UNIQUE " : null,
uIndex.IndexName,
uIndex.Table.Name,
columns));
}
if (uIndex.ViewName != null)
{
ObjectName viewName = new ObjectName(uIndex.Table.Name.Schema, uIndex.ViewName);
SqlPreCommandSimple viewSql = new SqlPreCommandSimple(@"CREATE VIEW {0} WITH SCHEMABINDING AS SELECT {1} FROM {2} WHERE {3}"
.FormatWith(viewName, columns, uIndex.Table.Name.ToString(), uIndex.Where))
{ GoBefore = true, GoAfter = true };
SqlPreCommandSimple indexSql = new SqlPreCommandSimple(@"CREATE UNIQUE CLUSTERED INDEX {0} ON {1}({2})"
.FormatWith(uIndex.IndexName, viewName, uIndex.Columns.ToString(c => c.Name.SqlEscape(), ", ")));
return SqlPreCommand.Combine(Spacing.Simple, viewSql, indexSql);
}
else
{
return new SqlPreCommandSimple("CREATE UNIQUE INDEX {0} ON {1}({2}) WHERE {3}".FormatWith(
uIndex.IndexName,
uIndex.Table.Name,
columns, uIndex.Where));
}
}