private static DataIndexDbDefinition CreateIndexDefinition(ITableDefinition table, TableRelationshipDefinition relationship, string prefix = null)
{
var tableName = prefix + table.Name;
var indexColumns = new[] { relationship.IsManyToMany ? relationship.JoinFieldName : relationship.FKName };
var filter = relationship.ExcludeRemovedItems ? "([IsRemoved]=(0))" : string.Empty;
var index = new DataIndexDbDefinition(GetForeignKeyIndexName(tableName, indexColumns), tableName);
index.IndexFields.AddRange(indexColumns.Select(columnName => new DataIndexFieldDbDefinition(columnName, true)));
index.FilterDefinition = filter;
return index;
}
private static IEnumerable<DataIndexDbDefinition> GetTableIndexes(ITableDefinition table, string prefix)
{
const char EscapeCharacter = '!';
using (var ctx = GetRuntimeDatabaseConnectionManager())
{
var commandText = string.Format(CultureInfo.InvariantCulture, @"
SELECT
ind.name as IndexName
,t.name as ProcessName
,ind.filter_definition as FilterDefinition
,col.name as FieldName
,ic.is_included_column as IsIncluded
FROM
sys.indexes ind
INNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN sys.tables t ON ind.object_id = t.object_id AND ind.name LIKE '{0}%' ESCAPE '{1}'
WHERE ind.object_id = (SELECT OBJECT_ID(@tableName))
ORDER BY ind.name, ic.key_ordinal", AdoHelper.EscapeLikePattern(prefix, EscapeCharacter), EscapeCharacter);
using (var command = new SqlCommand(commandText, ctx.Connection))
{
command.Parameters.AddWithValue("@tableName", table.Name);
using (var reader = new SafeDataReader(command.ExecuteReader()))
{
var indexList = new Collection<DataIndexDbDefinition>();
while (reader.Read())
{
var indexName = reader.GetString(0);
var processName = reader.GetString(1);
var index = indexList.FirstOrDefault(ix => ix.IndexName == indexName && ix.ProcessName == processName);
if (index == null)
{
index = new DataIndexDbDefinition { IndexName = indexName, ProcessName = processName, FilterDefinition = reader.GetString(2) };
indexList.Add(index);
}
var fieldName = reader.GetString(3);
var included = reader.GetBoolean(4);
index.IndexFields.Add(new DataIndexFieldDbDefinition(fieldName, !included));
}
return indexList;
}
}
}
}
/// <summary>
/// Generates the SQL script that drops the specified index if the index exists.
/// </summary>
/// <param name="index">
/// The index.
/// </param>
/// <returns>
/// The SQL script.
/// </returns>
private static string GenerateDropIndexIfExists(DataIndexDbDefinition index)
{
return string.Format(CultureInfo.InvariantCulture,
@"IF EXISTS (
SELECT *
FROM sys.indexes ind
INNER JOIN sys.tables t ON ind.object_id = t.object_id
WHERE ind.name = '{0}' AND ind.object_id = (SELECT OBJECT_ID('{1}'))
)
BEGIN
DROP INDEX {0} ON [dbo].[{1}]
END", index.IndexName, index.ProcessName);
}
/// <summary>
/// Generates the SQL script that creates the specified index if the index doesn't exist.
/// </summary>
/// <param name="index">
/// The index.
/// </param>
/// <returns>
/// The SQL script.
/// </returns>
private static string GenerateCreateIndexIfNotExists(DataIndexDbDefinition index)
{
var createIndexSql = new StringBuilder();
createIndexSql.AppendFormat(
CultureInfo.InvariantCulture,
@"CREATE NONCLUSTERED INDEX {0} ON [{1}]({2})",
index.IndexName,
index.ProcessName,
string.Join(", ", index.KeyFields));
if (index.NonKeyFields.Any())
{
createIndexSql.AppendFormat(CultureInfo.InvariantCulture, " INCLUDE ({0})", string.Join(", ", index.NonKeyFields));
}
if (!string.IsNullOrEmpty(index.FilterDefinition))
{
createIndexSql.AppendFormat(CultureInfo.InvariantCulture, " WHERE {0}", index.FilterDefinition);
}
return string.Format(CultureInfo.InvariantCulture,
@"IF NOT EXISTS (
SELECT *
FROM sys.indexes ind
INNER JOIN sys.tables t ON ind.object_id = t.object_id
WHERE ind.name = '{0}' AND ind.object_id = (SELECT OBJECT_ID('{1}'))
)
BEGIN
{2}
END", index.IndexName, index.ProcessName, createIndexSql);
}
/// <summary>
/// Gets the data index definitions.
/// </summary>
/// <param name="process">
/// The process.
/// </param>
/// <returns>
/// The collection of data index definitions.
/// </returns>
private static IEnumerable<DataIndexDbDefinition> GetDataIndexDefinitions(IProcessDefinition process)
{
foreach (var processIndex in process.DataIndexList)
{
var databaseIndex = new DataIndexDbDefinition(GetDataIndexDbName(processIndex), process.Name);
databaseIndex.IndexFields.AddRange(processIndex.IndexFields.Select(f => new DataIndexFieldDbDefinition(f.FieldName, f.IsKey)));
if (processIndex.ExcludeRemovedItems)
{
databaseIndex.FilterDefinition = "([IsRemoved]=(0))";
}
yield return databaseIndex;
}
}
/// <summary>
/// Gets the update data indexes.
/// </summary>
/// <param name="process">The process.</param>
/// <returns>System.String.</returns>
public override string GetUpdateProcessIndexes(IProcessDefinition process)
{
var currentProcessIndexes = process.DataIndexList;
var currnetFields = process.FieldList;
var currentDatabaseIndexes = new List<DataIndexDbDefinition>();
var result = new StringBuilder();
using (var ctx = ConnectionManager<OracleConnection>.GetManager(Database.VeyronRuntime, false))
{
#region Obtain db Indexes
var dbResult = new List<DataIndexDbElement>();
var cn = ctx.Connection;
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
const string commandText = "SELECT index_name , table_name , column_name FROM user_ind_columns WHERE table_name=:tableName";
using (var command = new OracleCommand(commandText, cn))
{
command.BindByName = true;
command.Parameters.Add("tableName", OracleNamesTranslator.Translate(process.Name));
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
dbResult.Add(new DataIndexDbElement(
OracleNamesTranslator.TranslateBack(reader.GetString(0)),
OracleNamesTranslator.TranslateBack(reader.GetString(1)),
OracleNamesTranslator.TranslateBack(reader.GetString(2))));
}
}
}
var userIndexes = dbResult.Where(x => x.IndexName.StartsWith("UserIndex")).ToList();
var uniqueValues = userIndexes.GroupBy(x => x.IndexName);
var enumerable = uniqueValues as IList<IGrouping<string, DataIndexDbElement>> ?? uniqueValues.ToList();
for (var i = 0; i < enumerable.Count(); i++)
{
var val = uniqueValues.ToList()[i];
var dataIndexDbElement = val.FirstOrDefault();
if (dataIndexDbElement != null)
{
var indexDefinition = new DataIndexDbDefinition(dataIndexDbElement.IndexName, dataIndexDbElement.ProcessName);
indexDefinition.IndexFields.AddRange(val.Select(x => new DataIndexFieldDbDefinition(x.FieldSistemName, true)));
currentDatabaseIndexes.Add(indexDefinition);
}
}
#endregion
#region Delete old indexes
//indexes to delete
var deletedIndexes =
currentDatabaseIndexes.Where(
x =>
currentProcessIndexes.All(
y =>
x.IndexName != GetDataIndexDbName(true, y.UniqueName) &&
x.IndexName != GetDataIndexDbName(false, y.UniqueName)));
var dataIndexDbDefinitions = deletedIndexes as IList<DataIndexDbDefinition> ?? deletedIndexes.ToList();
if (dataIndexDbDefinitions.Count() != 0)
{
foreach (var dataIndexDbDefinition in dataIndexDbDefinitions)
{
result.AppendFormat(@"BEGIN EXECUTE IMMEDIATE 'DROP INDEX ""{0}""'; END;",
OracleNamesTranslator.Translate(dataIndexDbDefinition.IndexName)).AppendLine();
}
}
#endregion
#region Update/add Indexes
foreach (var currentProcessIndex in currentProcessIndexes)
{
try
{
var index = currentProcessIndex;
var processIndex = currentProcessIndex;
var indexFieldsNames = processIndex.IndexFields.Select(f => f.FieldName).ToList();
var element = new DataIndexDbDefinition(
GetDataIndexDbName(currentProcessIndex.IsUniqueIndex, currentProcessIndex.UniqueName),
process.Name);
element.IndexFields.AddRange(indexFieldsNames.Select(fieldName => new DataIndexFieldDbDefinition(fieldName, true)));
//Index Requires Update
var existingIndex = currentDatabaseIndexes.FirstOrDefault(x => x.IndexName == GetDataIndexDbName(true, index.UniqueName)
|| x.IndexName == GetDataIndexDbName(false, index.UniqueName));
if (existingIndex != null)
{
if (element.ToString() == existingIndex.ToString()) //Same index with no modifications
continue;
//Index modified
result.AppendFormat(@"BEGIN EXECUTE IMMEDIATE 'DROP INDEX ""{0}""'; END; ", OracleNamesTranslator.Translate(existingIndex.IndexName));
}
//Add new index To db
result.AppendFormat(
@"BEGIN EXECUTE IMMEDIATE 'CREATE INDEX ""{0}"" ON ""{1}""({2})'; END;",
OracleNamesTranslator.Translate(element.IndexName),
OracleNamesTranslator.Translate(process.Name),
element.KeyFields.Select(x => string.Format("\"{0}\"", OracleNamesTranslator.Translate(x))).Aggregate((a, b) => a + ", " + b));
}
catch (Exception)
{
throw;
}
}
#endregion
}
return result.ToString();
}