private void CompareTableIndexes(Index i1, Index i2)
{
Assert.IsNotNull(i1);
Assert.IsNotNull(i2);
Assert.AreEqual(i1.DataTable.Name, i2.DataTable.Name);
Assert.AreEqual(i1.Filegroup, i2.Filegroup);
Version ver = connection.Driver.CoreServerInfo.ServerVersion;
if (ver.Major * 100 + ver.Minor >= 802)
{
if (i1.FillFactor != null)
{
Assert.AreEqual(i1.FillFactor, i2.FillFactor);
}
}
Assert.AreEqual(i1.IsBitmap, i2.IsBitmap);
Assert.AreEqual(i1.IsClustered, i2.IsClustered);
Assert.AreEqual(i1.IsUnique, i2.IsUnique);
Assert.AreEqual(i1.Columns.Count, i2.Columns.Count);
foreach (IndexColumn ic1 in i1.Columns)
{
IndexColumn ic2 = i2.Columns[ic1.Name];
Assert.IsNotNull(ic2);
CompareIndexColumns(ic1, ic2);
}
}
public override string Translate(SqlCompilerContext context, SqlCreateIndex node, CreateIndexSection section)
{
Index index = node.Index;
switch (section)
{
case CreateIndexSection.Entry:
return(string.Format("CREATE {0}INDEX {1} ON {2} {3}("
, index.IsUnique ? "UNIQUE " : string.Empty
, QuoteIdentifier(index.Name)
, Translate(context, index.DataTable)
, index.IsSpatial ? "USING GIST" : string.Empty));
case CreateIndexSection.StorageOptions:
var builder = new StringBuilder(")");
AppendIndexStorageParameters(builder, index);
if (!string.IsNullOrEmpty(index.Filegroup))
{
_ = builder.Append(" TABLESPACE " + QuoteIdentifier(index.Filegroup));
}
return(builder.ToString());
case CreateIndexSection.Exit:
return(string.Empty);
case CreateIndexSection.Where:
return(" WHERE ");
default:
return(string.Empty);
}
}
/// <inheritdoc/>
protected override IPathNode VisitIndex(Index index)
{
var tableInfo = currentTable;
var secondaryIndexInfo = new SecondaryIndexInfo(tableInfo, index.Name)
{
IsUnique = index.IsUnique,
IsClustered = index.IsClustered,
Filter = GetFilter(index),
};
foreach (var keyColumn in index.Columns)
{
var columnInfo = tableInfo.Columns[keyColumn.Column.Name];
new KeyColumnRef(secondaryIndexInfo,
columnInfo, keyColumn.Ascending ? Direction.Positive : Direction.Negative);
}
foreach (var valueColumn in index.NonkeyColumns)
{
var columnInfo = tableInfo.Columns[valueColumn.Name];
new IncludedColumnRef(secondaryIndexInfo, columnInfo);
}
secondaryIndexInfo.PopulatePrimaryKeyColumns();
return(secondaryIndexInfo);
}
/// <inheritdoc/>
public override string Translate(SqlCompilerContext context, SqlCreateIndex node, CreateIndexSection section)
{
switch (section)
{
case CreateIndexSection.Entry:
Index index = node.Index;
var builder = new StringBuilder();
builder.Append("CREATE ");
if (index.IsUnique)
{
builder.Append("UNIQUE ");
}
//else if (!index.IsAscending)
// builder.Append("DESC ");
builder.Append("INDEX " + QuoteIdentifier(index.DbName));
builder.Append(" ON " + Translate(context, index.DataTable));
return(builder.ToString());
case CreateIndexSection.ColumnsEnter:
if (node.Index.Columns[0].Expression != null)
{
if (node.Index.Columns.Count > 1)
{
SqlHelper.NotSupported("expression index with multiple column");
}
return("COMPUTED BY (");
}
else
{
return("(");
}
}
return(base.Translate(context, node, section));
}
/// <inheritdoc/>
protected override void ReadSpecialIndexProperties(DbDataReader dr, Index i)
{
base.ReadSpecialIndexProperties(dr, i);
if (dr["reloptions"] != DBNull.Value)
{
var ro = ParseRelOptions(dr["reloptions"]);
i.FillFactor = ro.FillFactor;
}
}
public void Test161()
{
var t = Catalog.Schemas["main"].Tables["customer"];
Index index = t.CreateIndex("MegaIndex196");
index.CreateIndexColumn(t.TableColumns[0]);
SqlDropIndex drop = SqlDdl.Drop(index);
Console.Write(Compile(drop));
}
public void Test160()
{
var t = Catalog.Schemas["main"].Tables["customer"];
Index index = t.CreateIndex("MegaIndex195");
index.CreateIndexColumn(t.TableColumns[0]);
SqlCreateIndex create = SqlDdl.Create(index);
Console.Write(Compile(create));
}
private PartialIndexFilterInfo GetFilter(Index index)
{
var tableName = resolver.GetNodeName(index.DataTable);
var result = partialIndexMap.FindIndex(tableName, index.DbName);
if (result == null)
{
return(null);
}
return(new PartialIndexFilterInfo(result.Filter));
}
private void ExtractIndexColumns(Table table, Index index)
{
var query = BuildExtractIndexColumnsQuery(index.Name);
using var cmd = Connection.CreateCommand(query);
using IDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
index.CreateIndexColumn(table.TableColumns[ReadStringOrNull(reader, 2)]);
}
}
private async Task ExtractIndexColumnsAsync(Table table, Index index, CancellationToken token)
{
var query = BuildExtractIndexColumnsQuery(index.Name);
var cmd = Connection.CreateCommand(query);
await using (cmd.ConfigureAwait(false)) {
var reader = await cmd.ExecuteReaderAsync(token).ConfigureAwait(false);
await using (reader.ConfigureAwait(false)) {
while (await reader.ReadAsync(token).ConfigureAwait(false))
{
index.CreateIndexColumn(table.TableColumns[ReadStringOrNull(reader, 2)]);
}
}
}
}
public override string Translate(SqlCompilerContext context, SqlCreateIndex node, CreateIndexSection section)
{
Index index = node.Index;
switch (section)
{
case CreateIndexSection.Entry:
return(string.Format("CREATE {0} INDEX {1} ON {2} ", index.IsUnique ? "UNIQUE" : String.Empty, QuoteIdentifier(index.Name), QuoteIdentifier(index.DataTable.Name)));
case CreateIndexSection.Exit:
return(string.Empty);
default:
return(base.Translate(context, node, section));
}
}
private void CompareTables(Table t1, Table t2)
{
Assert.IsNotNull(t1);
Assert.IsNotNull(t2);
Assert.AreEqual(t1.Filegroup, t2.Filegroup);
Assert.AreEqual(t1.Schema.Name, t2.Schema.Name);
Assert.AreEqual(t1.TableColumns.Count, t2.TableColumns.Count);
Assert.AreEqual(t1.TableConstraints.Count, t2.TableConstraints.Count);
foreach (TableColumn c1 in t1.TableColumns)
{
TableColumn c2 = t2.TableColumns[c1.Name];
Assert.IsNotNull(c2);
CompareTableColumns(c1, c2);
}
foreach (Index i1 in t1.Indexes)
{
Index i2 = t2.Indexes[i1.Name];
Assert.IsNotNull(i2);
CompareTableIndexes(i1, i2);
}
foreach (TableConstraint tc1 in t1.TableConstraints)
{
TableConstraint tc2 = t2.TableConstraints[tc1.Name];
Assert.IsNotNull(tc2);
if (tc1 is CheckConstraint)
{
CompareCheckConstraints(tc1 as CheckConstraint, tc2 as CheckConstraint);
}
else if (tc1 is PrimaryKey)
{
ComparePrimaryKeys(tc1 as PrimaryKey, tc2 as PrimaryKey);
}
else if (tc1 is UniqueConstraint)
{
CompareUniqueConstraints(tc1 as UniqueConstraint, tc2 as UniqueConstraint);
}
else if (tc1 is ForeignKey)
{
CompareForeignKeys(tc1 as ForeignKey, tc2 as ForeignKey);
}
}
}
private DataTableColumn[] GetNonKeyColumns(DataTable newTable, Index sourceIndex)
{
var table = newTable as Table;
if (table != null)
{
return(sourceIndex.NonkeyColumns.Select(el => table.TableColumns[el.Name]).Cast <DataTableColumn>().ToArray());
}
var view = newTable as View;
if (view != null)
{
return(sourceIndex.NonkeyColumns.Select(el => view.ViewColumns[el.Name]).Cast <DataTableColumn>().ToArray());
}
throw new ArgumentOutOfRangeException("newTable", Strings.ExUnexpectedTypeOfParameter);
}
private static bool ReadIndexData(IDataReader reader, Table table, out Index index)
{
index = null;
var indexName = ReadStringOrNull(reader, 1);
if (indexName.StartsWith("sqlite_autoindex_", StringComparison.Ordinal))
{
// Special index used for primary keys
// It should be hidden here, because PK are already extracted in ExtractColumns()
return(false);
}
var unique = reader.GetBoolean(2);
index = table.CreateIndex(indexName);
index.IsUnique = unique;
return(true);
}
private void ExtractIndexes(ExtractionContext context)
{
var query = BuildExtractIndexesQuery(context);
const int spatialIndexType = 4;
var tableId = 0;
ColumnResolver table = null;
Index index = null;
PrimaryKey primaryKey = null;
UniqueConstraint uniqueConstraint = null;
using var cmd = Connection.CreateCommand(query);
using var reader = cmd.ExecuteReader();
while (reader.Read())
{
ReadIndexColumnData(reader, context,
ref tableId, spatialIndexType, ref primaryKey, ref uniqueConstraint, ref index, ref table);
}
}
private async Task ExtractIndexesAsync(ExtractionContext context, CancellationToken token)
{
var query = BuildExtractIndexesQuery(context);
const int spatialIndexType = 4;
var tableId = 0;
ColumnResolver table = null;
Index index = null;
PrimaryKey primaryKey = null;
UniqueConstraint uniqueConstraint = null;
var cmd = Connection.CreateCommand(query);
await using (cmd.ConfigureAwait(false)) {
var reader = await cmd.ExecuteReaderAsync(token).ConfigureAwait(false);
await using (reader.ConfigureAwait(false)) {
while (await reader.ReadAsync(token).ConfigureAwait(false))
{
ReadIndexColumnData(reader, context,
ref tableId, spatialIndexType, ref primaryKey, ref uniqueConstraint, ref index, ref table);
}
}
}
}
private void ExtractIndexes()
{
string query = GetIndexQuery();
const int spatialIndexType = 4;
int tableId = 0;
ColumnResolver table = null;
Index index = null;
PrimaryKey primaryKey = null;
UniqueConstraint uniqueConstraint = null;
using (var cmd = Connection.CreateCommand(query))
using (var reader = cmd.ExecuteReader())
while (reader.Read())
{
int columnId = reader.GetInt32(10);
int indexType = reader.GetByte(5);
// First column in index => new index or index is spatial (always has exactly one column)
if (reader.GetByte(12) == 1 || indexType == spatialIndexType)
{
primaryKey = null;
uniqueConstraint = null;
index = null;
// Table could be changed only on new index creation
GetDataTable(reader.GetInt32(1), ref tableId, ref table);
var indexId = reader.GetInt32(3);
var indexName = reader.GetString(4);
// Index is a part of primary key constraint
if (reader.GetBoolean(6))
{
primaryKey = ((Table)table.Table).CreatePrimaryKey(indexName);
if (Driver.ServerInfo.PrimaryKey.Features.Supports(PrimaryKeyConstraintFeatures.Clustered))
{
primaryKey.IsClustered = reader.GetByte(5) == 1;
}
}
else
{
// Spatial index
if (indexType == spatialIndexType)
{
index = table.Table.CreateSpatialIndex(indexName);
index.FillFactor = reader.GetByte(9);
}
else
{
index = table.Table.CreateIndex(indexName);
index.IsUnique = reader.GetBoolean(7);
if (Driver.ServerInfo.Index.Features.Supports(IndexFeatures.Clustered))
{
index.IsClustered = reader.GetByte(5) == 1;
}
index.FillFactor = reader.GetByte(9);
if (!reader.IsDBNull(15) && reader.GetBoolean(15))
{
index.Where = SqlDml.Native(reader.GetString(16));
}
// Index is a part of unique constraint
if (reader.GetBoolean(8))
{
uniqueConstraint = ((Table)table.Table).CreateUniqueConstraint(indexName);
if (index.IsClustered && Driver.ServerInfo.UniqueConstraint.Features.Supports(UniqueConstraintFeatures.Clustered))
{
uniqueConstraint.IsClustered = true;
}
}
}
}
}
// Column is a part of a primary index
if (reader.GetBoolean(6))
{
primaryKey.Columns.Add((TableColumn)table.GetColumn(columnId));
}
else
{
// Column is a part of unique constraint
if (reader.GetBoolean(8))
{
uniqueConstraint.Columns.Add((TableColumn)table.GetColumn(columnId));
}
if (index != null)
{
// Column is non key column
if (reader.GetBoolean(14))
{
index.NonkeyColumns.Add(table.GetColumn(columnId));
}
else
{
index.CreateIndexColumn(table.GetColumn(columnId), !reader.GetBoolean(13));
}
}
}
}
}
public static SqlCreateIndex Create(Index index)
{
ArgumentValidator.EnsureArgumentNotNull(index, "index");
return(new SqlCreateIndex(index));
}
// Constructors
internal SqlCreateIndex(Index index)
: base(SqlNodeType.Create)
{
Index = index;
}
private void CloneIndex(DataTable newTable, Index sourceIndex)
{
var ftIndex = sourceIndex as FullTextIndex;
if (ftIndex != null)
{
var ft = newTable.CreateFullTextIndex(ftIndex.Name);
CopyDbName(ft, ftIndex);
foreach (var tableColumn in GetKeyColumns(newTable, sourceIndex))
{
ft.CreateIndexColumn(tableColumn);
}
ft.NonkeyColumns.AddRange(GetNonKeyColumns(newTable, ft));
ft.Filegroup = ftIndex.Filegroup;
ft.FillFactor = ftIndex.FillFactor;
ft.FullTextCatalog = ftIndex.FullTextCatalog;
ft.IsBitmap = ftIndex.IsBitmap;
ft.IsClustered = ftIndex.IsClustered;
ft.IsUnique = ftIndex.IsUnique;
ft.UnderlyingUniqueIndex = ftIndex.UnderlyingUniqueIndex;
if (ftIndex.Where != null)
{
ft.Where = (SqlExpression)ftIndex.Where.Clone();
}
ClonePartitionDescriptor(ft, sourceIndex);
return;
}
var spatialIndex = sourceIndex as SpatialIndex;
if (spatialIndex != null)
{
var spatial = newTable.CreateSpatialIndex(spatialIndex.Name);
CopyDbName(spatial, spatialIndex);
foreach (var tableColumn in GetKeyColumns(newTable, sourceIndex))
{
spatial.CreateIndexColumn(tableColumn);
}
spatial.NonkeyColumns.AddRange(GetNonKeyColumns(newTable, spatial));
spatial.Filegroup = spatialIndex.Filegroup;
spatial.FillFactor = spatialIndex.FillFactor;
spatial.IsBitmap = spatialIndex.IsBitmap;
spatial.IsClustered = spatialIndex.IsClustered;
spatial.IsUnique = spatialIndex.IsUnique;
if (spatialIndex.Where != null)
{
spatial.Where = (SqlExpression)spatialIndex.Where.Clone();
}
ClonePartitionDescriptor(spatialIndex, sourceIndex);
return;
}
var index = newTable.CreateIndex(sourceIndex.Name);
CopyDbName(index, sourceIndex);
foreach (var tableColumn in GetKeyColumns(newTable, sourceIndex))
{
index.CreateIndexColumn(tableColumn);
}
index.Filegroup = sourceIndex.Filegroup;
index.FillFactor = sourceIndex.FillFactor;
index.IsUnique = sourceIndex.IsUnique;
index.IsClustered = sourceIndex.IsClustered;
if (sourceIndex.Where != null)
{
index.Where = (SqlExpression)sourceIndex.Where.Clone();
}
index.NonkeyColumns.AddRange(GetNonKeyColumns(newTable, sourceIndex));
index.IsBitmap = sourceIndex.IsBitmap;
ClonePartitionDescriptor(index, sourceIndex);
}
public static SqlDropIndex Drop(Index index)
{
ArgumentValidator.EnsureArgumentNotNull(index, "index");
return(new SqlDropIndex(index));
}
protected virtual void AppendIndexStorageParameters(StringBuilder builder, Index index)
{
}
private void ReadIndexColumnData(DbDataReader reader, ExtractionContext context,
ref int tableId, int spatialIndexType, ref PrimaryKey primaryKey,
ref UniqueConstraint uniqueConstraint, ref Index index, ref ColumnResolver table)
{
var columnId = reader.GetInt32(10);
int indexType = reader.GetByte(5);
// First column in index => new index or index is spatial (always has exactly one column)
if (reader.GetByte(12) == 1 || indexType == spatialIndexType)
{
primaryKey = null;
uniqueConstraint = null;
index = null;
// Table could be changed only on new index creation
GetDataTable(reader.GetInt32(1), context, ref tableId, ref table);
var indexId = reader.GetInt32(3);
var indexName = reader.GetString(4);
// Index is a part of primary key constraint
if (reader.GetBoolean(6))
{
primaryKey = ((Table)table.Table).CreatePrimaryKey(indexName);
if (Driver.ServerInfo.PrimaryKey.Features.Supports(PrimaryKeyConstraintFeatures.Clustered))
{
primaryKey.IsClustered = reader.GetByte(5) == 1;
}
}
else
{
// Spatial index
if (indexType == spatialIndexType)
{
index = table.Table.CreateSpatialIndex(indexName);
index.FillFactor = reader.GetByte(9);
}
else
{
index = table.Table.CreateIndex(indexName);
index.IsUnique = reader.GetBoolean(7);
if (Driver.ServerInfo.Index.Features.Supports(IndexFeatures.Clustered))
{
index.IsClustered = reader.GetByte(5) == 1;
}
index.FillFactor = reader.GetByte(9);
if (!reader.IsDBNull(15) && reader.GetBoolean(15))
{
index.Where = SqlDml.Native(reader.GetString(16));
}
// Index is a part of unique constraint
if (reader.GetBoolean(8))
{
uniqueConstraint = ((Table)table.Table).CreateUniqueConstraint(indexName);
if (index.IsClustered &&
Driver.ServerInfo.UniqueConstraint.Features.Supports(UniqueConstraintFeatures.Clustered))
{
uniqueConstraint.IsClustered = true;
}
}
}
}
}
// Column is a part of a primary index
if (reader.GetBoolean(6))
{
primaryKey.Columns.Add((TableColumn)table.GetColumn(columnId));
}
else
{
// Column is a part of unique constraint
if (reader.GetBoolean(8))
{
uniqueConstraint.Columns.Add((TableColumn)table.GetColumn(columnId));
}
if (index != null)
{
// Column is non key column
if (reader.GetBoolean(14))
{
index.NonkeyColumns.Add(table.GetColumn(columnId));
}
else
{
_ = index.CreateIndexColumn(table.GetColumn(columnId), !reader.GetBoolean(13));
}
}
}
}
public virtual string Translate(Index node)
{
return(node.DataTable.Schema != null
? QuoteIdentifier(node.DataTable.Schema.DbName, node.DbName)
: QuoteIdentifier(node.DbName));
}
internal SqlDropIndex(Index index)
: base(SqlNodeType.Drop)
{
this.index = index;
}