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(); }