void IOracleModelVisitor.VisitIndexColumn(IndexColumnModel item)
{
if (this._filter(item))
{
_items.Add(item);
}
}
private void GetIndexes()
{
foreach (var table in _databaseModel.Tables)
{
var indexInfo = _connection.CreateCommand();
indexInfo.CommandText = $"PRAGMA index_list(\"{table.Name.Replace("\"", "\"\"")}\");";
using (var reader = indexInfo.ExecuteReader())
{
while (reader.Read())
{
var indexName = reader.GetValueOrDefault <string>("name");
var isUnique = reader.GetValueOrDefault <bool>("unique");
var index = table.Indexes.FirstOrDefault(i => i.Name.Equals(indexName, StringComparison.OrdinalIgnoreCase));
if (index != null)
{
index.IsUnique = isUnique;
}
}
}
foreach (var index in table.Indexes)
{
var indexColumns = _connection.CreateCommand();
indexColumns.CommandText = $"PRAGMA index_info(\"{index.Name.Replace("\"", "\"\"")}\");";
index.IndexColumns = new List <IndexColumnModel>();
using (var reader = indexColumns.ExecuteReader())
{
while (reader.Read())
{
var columnName = reader.GetValueOrDefault <string>("name");
if (string.IsNullOrEmpty(columnName))
{
continue;
}
var indexOrdinal = reader.GetValueOrDefault <int>("seqno");
var column = _tableColumns[ColumnKey(index.Table, columnName)];
var indexColumn = new IndexColumnModel
{
Ordinal = indexOrdinal,
Column = column
};
index.IndexColumns.Add(indexColumn);
}
}
}
}
}
protected override IndexColumnModel MapIndexColumn(DataRow row)
{
var indexColumn = new IndexColumnModel();
indexColumn.TableCatalog = row.GetString("table_catalog");
indexColumn.TableSchema = row.GetString("table_schema");
indexColumn.TableName = row.GetString("table_name");
indexColumn.IndexName = row.GetString("index_name");
indexColumn.ColumnName = row.GetString("column_name");
indexColumn.OrdinalPostion = row.GetInt("ordinal_position");
return(indexColumn);
}
protected override IndexColumnModel MapIndexColumn(DataRow row)
{
var indexColumn = new IndexColumnModel();
indexColumn.TableCatalog = row.GetString("INDEX_SCHEMA");
indexColumn.TableSchema = null;
indexColumn.TableName = row.GetString("TABLE_NAME");
indexColumn.IndexName = row.GetString("INDEX_NAME");
indexColumn.ColumnName = row.GetString("COLUMN_NAME");
indexColumn.OrdinalPostion = row.GetInt("ORDINAL_POSITION");
return(indexColumn);
}
public override async Task MapReaderData(OracleDataReader reader, CancellationToken cancellationToken)
{
while (await reader.ReadAsynchronous(cancellationToken))
{
var indexOwner = (string)reader["INDEX_OWNER"];
var indexName = (string)reader["INDEX_NAME"];
var indexIdentifier = OracleObjectIdentifier.Create(indexOwner, indexName);
if (!_indexes.TryGetValue(indexIdentifier, out IndexDetailsModel indexModel))
{
continue;
}
var indexColumn =
new IndexColumnModel
{
ColumnName = (string)reader["COLUMN_NAME"],
SortOrder = (string)reader["DESCEND"] == "ASC" ? SortOrder.Ascending : SortOrder.Descending
};
indexModel.Columns.Add(indexColumn);
}
}
private void GetIndexes()
{
foreach (var table in _databaseModel.Tables)
{
using (var indexInfo = _connection.CreateCommand())
{
indexInfo.CommandText = $"PRAGMA index_list(\"{table.Name.Replace("\"", "\"\"")}\");";
using (var reader = indexInfo.ExecuteReader())
{
while (reader.Read())
{
var index = new IndexModel
{
Name = reader.GetValueOrDefault <string>("name"),
Table = table,
IsUnique = reader.GetValueOrDefault <bool>("unique")
};
Logger.LogTrace(SqliteDesignStrings
.FoundIndex(index.Name, table.Name, index.IsUnique));
table.Indexes.Add(index);
}
}
foreach (var index in table.Indexes)
{
var indexColumns = _connection.CreateCommand();
indexColumns.CommandText = $"PRAGMA index_info(\"{index.Name.Replace("\"", "\"\"")}\");";
index.IndexColumns = new List <IndexColumnModel>();
using (var reader = indexColumns.ExecuteReader())
{
while (reader.Read())
{
var columnName = reader.GetValueOrDefault <string>("name");
var indexOrdinal = reader.GetValueOrDefault <int>("seqno");
Logger.LogTrace(SqliteDesignStrings.FoundIndexColumn(
index.Name, table.Name, columnName, indexOrdinal));
if (string.IsNullOrEmpty(columnName))
{
Logger.LogWarning(SqliteDesignStrings
.ColumnNameEmptyOnIndex(index.Name, table.Name));
continue;
}
var column = _tableColumns[ColumnKey(index.Table, columnName)];
var indexColumn = new IndexColumnModel
{
Ordinal = indexOrdinal,
Column = column
};
index.IndexColumns.Add(indexColumn);
}
}
}
}
}
}
private void GetIndexes()
{
var command = _connection.CreateCommand();
command.CommandText = @"SELECT
object_schema_name(i.object_id) AS [schema_name],
object_name(i.object_id) AS [table_name],
i.name AS [index_name],
i.is_unique,
c.name AS [column_name],
i.type_desc,
ic.key_ordinal
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_id
INNER JOIN sys.tables t ON t.object_id = i.object_id
WHERE object_schema_name(i.object_id) <> 'sys'
AND object_name(i.object_id) <> '" + HistoryRepository.DefaultTableName + @"'" +
TemporalTableWhereClause + @"
ORDER BY object_schema_name(i.object_id), object_name(i.object_id), i.name, ic.key_ordinal";
using (var reader = command.ExecuteReader())
{
IndexModel index = null;
while (reader.Read())
{
var schemaName = reader.GetValueOrDefault <string>("schema_name");
var tableName = reader.GetValueOrDefault <string>("table_name");
var indexName = reader.GetValueOrDefault <string>("index_name");
var isUnique = reader.GetValueOrDefault <bool>("is_unique");
var typeDesc = reader.GetValueOrDefault <string>("type_desc");
var columnName = reader.GetValueOrDefault <string>("column_name");
var indexOrdinal = reader.GetValueOrDefault <byte>("key_ordinal");
Logger.LogDebug(
RelationalDesignEventId.FoundIndexColumn,
() => SqlServerDesignStrings.FoundIndexColumn(
schemaName, tableName, indexName, isUnique, typeDesc, columnName, indexOrdinal));
if (!_tableSelectionSet.Allows(schemaName, tableName))
{
Logger.LogDebug(
RelationalDesignEventId.IndexColumnSkipped,
() => SqlServerDesignStrings.IndexColumnNotInSelectionSet(
columnName, indexName, schemaName, tableName));
continue;
}
if (string.IsNullOrEmpty(indexName))
{
Logger.LogWarning(
SqlServerDesignEventId.IndexMustBeNamedWarning,
() => SqlServerDesignStrings.IndexNameEmpty(schemaName, tableName));
continue;
}
Debug.Assert(index == null || index.Table != null);
if (index == null ||
index.Name != indexName ||
index.Table.Name != tableName ||
index.Table.SchemaName != schemaName)
{
TableModel table;
if (!_tables.TryGetValue(TableKey(tableName, schemaName), out table))
{
Logger.LogWarning(
SqlServerDesignEventId.IndexTableMissingWarning,
() => SqlServerDesignStrings.UnableToFindTableForIndex(indexName, schemaName, tableName));
continue;
}
index = new IndexModel
{
Table = table,
Name = indexName,
IsUnique = isUnique
};
index.SqlServer().IsClustered = typeDesc == "CLUSTERED";
table.Indexes.Add(index);
}
ColumnModel column;
if (string.IsNullOrEmpty(columnName))
{
Logger.LogWarning(
SqlServerDesignEventId.IndexColumnMustBeNamedWarning,
() => SqlServerDesignStrings.ColumnNameEmptyOnIndex(
schemaName, tableName, indexName));
}
else if (!_tableColumns.TryGetValue(ColumnKey(index.Table, columnName), out column))
{
Logger.LogWarning(
RelationalDesignEventId.IndexColumnsNotMappedWarning,
() => SqlServerDesignStrings.UnableToFindColumnForIndex(
indexName, columnName, schemaName, tableName));
}
else
{
var indexColumn = new IndexColumnModel
{
Index = index,
Column = column,
Ordinal = indexOrdinal
};
index.IndexColumns.Add(indexColumn);
}
}
}
}
private void GetIndexes()
{
var command = _connection.CreateCommand();
command.CommandText = @"SELECT
object_schema_name(i.object_id) AS [schema_name],
object_name(i.object_id) AS [table_name],
i.name AS [index_name],
i.is_unique,
c.name AS [column_name],
i.type_desc,
ic.key_ordinal
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_id
WHERE object_schema_name(i.object_id) <> 'sys'
AND object_name(i.object_id) <> '" + HistoryRepository.DefaultTableName + @"'
ORDER BY object_schema_name(i.object_id), object_name(i.object_id), i.name, ic.key_ordinal";
using (var reader = command.ExecuteReader())
{
IndexModel index = null;
while (reader.Read())
{
var schemaName = reader.GetString(0);
var tableName = reader.GetString(1);
var indexName = reader.GetStringOrNull(2);
if (!_tableSelectionSet.Allows(schemaName, tableName))
{
continue;
}
if (string.IsNullOrEmpty(indexName))
{
Logger.LogWarning(SqlServerDesignStrings.IndexNameEmpty(schemaName, tableName));
continue;
}
if (index == null ||
index.Name != indexName ||
index.Table.Name != tableName ||
index.Table.SchemaName != schemaName)
{
TableModel table;
if (!_tables.TryGetValue(TableKey(tableName, schemaName), out table))
{
Logger.LogWarning(
SqlServerDesignStrings.UnableToFindTableForIndex(indexName, schemaName, tableName));
continue;
}
index = new IndexModel
{
Table = table,
Name = indexName,
IsUnique = reader.IsDBNull(3) ? false : reader.GetBoolean(3),
};
index.SqlServer().IsClustered = reader.GetStringOrNull(5) == "CLUSTERED";
table.Indexes.Add(index);
}
var columnName = reader.GetStringOrNull(4);
var indexOrdinal = reader.GetByte(6);
ColumnModel column = null;
if (string.IsNullOrEmpty(columnName))
{
Logger.LogWarning(
SqlServerDesignStrings.ColumnNameEmptyOnIndex(
schemaName, tableName, indexName));
}
else if (!_tableColumns.TryGetValue(ColumnKey(index.Table, columnName), out column))
{
Logger.LogWarning(
SqlServerDesignStrings.UnableToFindColumnForIndex(
indexName, columnName, schemaName, tableName));
}
else
{
var indexColumn = new IndexColumnModel
{
Index = index,
Column = column,
Ordinal = indexOrdinal
};
index.IndexColumns.Add(indexColumn);
}
}
}
}
public static ColumnModel GetColumn(this IndexColumnModel indexColumn)
{
return(indexColumn.Column);
}
void GetIndexes()
{
var command = _connection.CreateCommand();
var dbName = _connection.Database;
command.CommandText = $@"SELECT DISTINCT
s.table_schema,
s.table_name,
s.non_unique,
s.index_name,
s.column_name,
s.seq_in_index,
t.constraint_type
FROM information_schema.statistics s
LEFT OUTER JOIN information_schema.table_constraints t
ON t.table_schema=s.table_schema
AND t.table_name=s.table_name
AND s.index_name=t.constraint_name
WHERE s.table_schema IN ({_schemaList})
AND s.table_name <> '{HistoryRepository.DefaultTableName}'
ORDER BY s.table_schema, s.table_name, s.non_unique, s.index_name, s.seq_in_index";
using (var reader = command.ExecuteReader())
{
IndexModel index = null;
while (reader.Read())
{
var tableSchema = reader.GetValueOrDefault <string>("table_schema");
var tableName = reader.GetValueOrDefault <string>("table_name");
var indexName = reader.GetValueOrDefault <string>("index_name");
var isUnique = reader.GetInt32("non_unique") == 0 ? true : false;
var columnName = reader.GetValueOrDefault <string>("column_name");
var indexOrdinal = reader.GetInt32("seq_in_index");
if (!_tableSelectionSet.Allows(tableSchema, tableName))
{
Logger.LogDebug("Index column skipped", new string[] { columnName, indexName, tableSchema, tableName });
continue;
}
if (string.IsNullOrEmpty(indexName))
{
Logger.LogWarning("Index unnamed", new string[] { tableSchema, tableName });
continue;
}
if (index == null || index.Name != indexName || index.Table.Name != tableName || index.Table.SchemaName != tableSchema)
{
TableModel table = null;
if (!_tables.TryGetValue(TableKey(tableName, tableSchema), out table))
{
Logger.LogWarning("Index table missing", new string[] { indexName, tableSchema, tableName });
continue;
}
index = new IndexModel
{
Table = table,
Name = indexName,
IsUnique = isUnique
};
table.Indexes.Add(index);
}
ColumnModel column;
if (string.IsNullOrEmpty(columnName))
{
Logger.LogWarning("Index column must be named", new string[] { tableSchema, tableName, indexName });
}
else if (!_tableColumns.TryGetValue(ColumnKey(index.Table, columnName), out column))
{
Logger.LogWarning("Index columns not mapped", new string[] { indexName, columnName, tableSchema, tableName });
}
else
{
var indexColumn = new IndexColumnModel
{
Index = index,
Column = column,
Ordinal = (int)indexOrdinal
};
index.IndexColumns.Add(indexColumn);
}
}
}
}
private void GetIndexes()
{
var command = _connection.CreateCommand();
command.CommandText = @"SELECT
ix.[INDEX_NAME] AS [index_name],
NULL AS [schema_name],
ix.[TABLE_NAME] AS [table_name],
ix.[UNIQUE] AS is_unique,
ix.[COLUMN_NAME] AS [column_name],
ix.[ORDINAL_POSITION]
FROM INFORMATION_SCHEMA.INDEXES ix
WHERE ix.PRIMARY_KEY = 0
AND (SUBSTRING(TABLE_NAME, 1,2) <> '__')
ORDER BY ix.[INDEX_NAME], ix.[ORDINAL_POSITION];";
using (var reader = command.ExecuteReader())
{
IndexModel index = null;
while (reader.Read())
{
var indexName = reader.GetValueOrDefault <string>("index_name");
var tableName = reader.GetValueOrDefault <string>("table_name");
if (!_tableSelectionSet.Allows(tableName))
{
continue;
}
if ((index == null) ||
(index.Name != indexName))
{
TableModel table;
if (!_tables.TryGetValue(TableKey(tableName), out table))
{
continue;
}
index = new IndexModel
{
Table = table,
Name = indexName,
IsUnique = reader.GetValueOrDefault <bool>("is_unique")
};
table.Indexes.Add(index);
}
var columnName = reader.GetValueOrDefault <string>("column_name");
var column = _tableColumns[ColumnKey(index.Table, columnName)];
var indexOrdinal = reader.GetValueOrDefault <int>("ORDINAL_POSITION");
var indexColumn = new IndexColumnModel
{
Index = index,
Column = column,
Ordinal = indexOrdinal
};
index.IndexColumns.Add(indexColumn);
}
}
}
public void VisitIndexColumn(IndexColumnModel item)
{
}
public override List <IndexColumnQueryTable_11> Resolve(DbContextOracle context, Action <IndexColumnQueryTable_11> action)
{
List <IndexColumnQueryTable_11> List = new List <IndexColumnQueryTable_11>();
this.OracleContext = context;
var db = context.Database;
if (action == null)
{
action =
t =>
{
if (t.Column_Table_name.ExcludIfStartwith(t.Column_Table_Owner, Models.Configurations.ExcludeKindEnum.Table))
{
return;
}
string key = t.Column_Table_Owner + "." + t.Column_Table_name;
var key2 = t.Index_Owner + "." + t.Index_Name;
IndexModel index;
if (!db.Indexes.Contains(key2))
{
index = new IndexModel()
{
Key = key2,
Name = t.Index_Name,
IndexOwner = t.Index_Owner,
IndexType = t.Index_Type,
Unique = t.uniqueness == "UNIQUE",
// Cache = t.Cache,
// Chunk = t.Chunk,
Compress = t.Compression,
Compression_Prefix = "",
// Deduplication = t.Deduplication,
// FreePools = t.Freepools,
// In_Row = t.In_row,
// SecureFile = t.Securefile,
// SegmentName = t.Segment_name,
// InitialExtent = t.initial_extent,
Bitmap = false,
};
index.TableReference.Owner = t.Column_Table_Owner;
index.TableReference.Name = t.Column_Table_name;
// PctVersion = t.Pctversion,
index.PhysicalAttributes.Tablespace.Name = t.tablespace_name;
index.PhysicalAttributes.BufferPool = t.buffer_pool;
index.PhysicalAttributes.FreelistGroups = t.freelist_groups;
index.PhysicalAttributes.Freelists = t.freelists;
index.PhysicalAttributes.InitialExtent = t.initial_extent;
index.PhysicalAttributes.MaxExtents = t.max_extents;
index.PhysicalAttributes.MinExtents = t.min_extents;
index.PhysicalAttributes.NextExtent = t.next_extent;
index.PhysicalAttributes.PctIncrease = t.pct_increase;
index.PhysicalAttributes.Logging = t.Logging;
db.Indexes.Add(index);
}
else
{
index = db.Indexes[key2];
}
IndexColumnModel col1 = new IndexColumnModel()
{
Name = t.Column_Name, Rule = t.column_expression ?? t.Column_Name, Asc = t.Descend == "ASC"
};
index.Columns.Add(col1);
}
}
;
IndexColumnQueryDescriptor_11 IndexColumn = new IndexColumnQueryDescriptor_11(context.Manager.ConnectionString);
sql = string.Format(sql, TableQueryWhereCondition("i"));
using (var reader = context.Manager.ExecuteReader(CommandType.Text, sql, QueryBase.DbParams.ToArray()))
{
List = IndexColumn.ReadAll(reader, action).ToList();
}
return(List);
}
}
