private IEnumerable <IPlatformTableSourceIndexInfo> GetIndexes(TableSourceInfo tableSource, IEnumerable <IPlatformTableSourceColumnInfo> columns)
{
IDictionary <string, IPlatformTableSourceIndexInfo> indexes = new Dictionary <string, IPlatformTableSourceIndexInfo>();
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
using (IDataReader reader = GetTableSourceIndexes(conn, tableSource)) {
while (reader.Read())
{
string indexName = Convert.ToString(reader["idxName"]);
if (indexName.StartsWith("_WA_SYS_")) // _WA_SYS_* are statistics, not indexes
{
continue;
}
bool isUnique = (bool)reader["isUnique"];
bool isPrimaryKey = (bool)reader["isPrimaryKey"];
string columnName = Convert.ToString(reader["colName"]);
if (!indexName.IsNullOrEmpty())
{
IPlatformTableSourceIndexInfo index;
if (!indexes.TryGetValue(indexName, out index))
{
index = new PlatformTableSourceIndexInfo(tableSource, indexName, isUnique, isPrimaryKey);
indexes.Add(indexName, index);
}
IPlatformTableSourceColumnInfo column = columns.Single(c => c.Name.EqualsIgnoreCase(columnName));
((PlatformTableSourceIndexInfo)index).AddColumn(column);
}
}
return(indexes.Values.ToList());
}
}
}
private void InitializeIndexes(IDictionary <ITableSourceInfo, IPlatformTableSourceInfo> tablesDetails)
{
PlatformTableSourceIndexInfo currentIndex = null;
var execService = databaseServices.ExecutionService as Oracle.ExecutionService.ExecutionService;
using (var conn = databaseServices.TransactionService.CreateConnection()) {
// #417717 - In these queries we don't want to use the CIAI settings
if (execService != null)
{
execService.ForceCSASSetting(conn);
}
using (var cmd = execService.CreateCommand(conn, GetIndexesStatement(tablesDetails.Keys.Cast <TableSourceInfo>()))) {
cmd.CommandTimeout = QueryTimeout;
using (var reader = databaseServices.ExecutionService.ExecuteReader(cmd)) {
while (reader.Read())
{
string tableName = Convert.ToString(reader["TABLE_NAME"]).ToUpperInvariant();
string indexName = Convert.ToString(reader["INDEX_NAME"]).ToUpperInvariant();
var tableSource = (PlatformTableSourceInfo)tablesDetails.Values.FirstOrDefault(t => t.Name.EqualsIgnoreCase(tableName));
if (tableSource == null)
{
continue;
}
// If this is the first index or if it is different from the current index, add it to the list
if (currentIndex == null || !indexName.EqualsIgnoreCase(currentIndex.Name) || !tableName.EqualsIgnoreCase(currentIndex.TableSource.Name))
{
bool isPrimaryKey = Convert.ToString(reader["CONSTRAINT_TYPE"]).EqualsIgnoreCase("P");
bool isUnique = Convert.ToString(reader["UNIQUENESS"]).EqualsIgnoreCase("UNIQUE");
currentIndex = new PlatformTableSourceIndexInfo(tableSource, indexName, isUnique, isPrimaryKey);
tableSource.AddIndex(currentIndex);
}
// If a function index was created, COLUMN_NAME should be something like SYS_NC0003$ and COLUMN_EXPRESSION should be something like:
// - NLSSORT("COLUMN NAME", 'NLS_COMP=BINARY_AI') or
// - CASE "CREATIONDATE" WHEN TIMESTAMP '1900-01-01 00:00:00.000000000' THEN NULL ELSE "CREATIONDATE" END (example)
string columnName = Convert.ToString(reader["COLUMN_NAME"]).ToUpperInvariant();
string expression = Convert.ToString(reader["COLUMN_EXPRESSION"]);
var columnsFromExpression = TryGetColumnsFromExpression(indexName, expression, tableSource.Columns);
if (columnsFromExpression.IsEmpty())
{
currentIndex.AddColumn(tableSource.Columns.Single(col => col.Name.EqualsIgnoreCase(columnName)), /*isFunctionIndex*/ false);
}
else
{
columnsFromExpression.Apply(c => { currentIndex.AddColumn(c, /*isFunctionIndex*/ true); });
}
}
}
}
}
}
