public void EqualsTest()
{
// Arrange.
var index1 = new DbIndexDefinition
{
Name = "TestIndex",
TableName = "TestTable",
IsUnique = false,
Columns = { new DbIndexColumnDefinition { Name = "TestColumn", SortOrder = DbSortOrder.Ascending } }
};
var index2 = new DbIndexDefinition
{
Name = "TestIndex",
TableName = "TestTable",
IsUnique = false,
Columns = { new DbIndexColumnDefinition { Name = "TestColumn", SortOrder = DbSortOrder.Ascending } }
};
var index3 = new DbIndexDefinition
{
Name = "TestIndexNew",
TableName = "TestTable",
IsUnique = false,
Columns = { new DbIndexColumnDefinition { Name = "TestColumn", SortOrder = DbSortOrder.Ascending } }
};
var index4 = new DbIndexDefinition
{
Name = "TestIndex",
TableName = "TestTableNew",
IsUnique = false,
Columns = { new DbIndexColumnDefinition { Name = "TestColumn", SortOrder = DbSortOrder.Ascending } }
};
var index5 = new DbIndexDefinition
{
Name = "TestIndex",
TableName = "TestTable",
IsUnique = true,
Columns = { new DbIndexColumnDefinition { Name = "TestColumn", SortOrder = DbSortOrder.Ascending } }
};
var index6 = new DbIndexDefinition
{
Name = "TestIndex",
TableName = "TestTable",
IsUnique = false,
Columns = { new DbIndexColumnDefinition { Name = "TestColumnNew", SortOrder = DbSortOrder.Ascending } }
};
// Act / Assert.
Assert.IsFalse(index1.Equals(null as object));
Assert.IsTrue(index1.Equals(index2 as object));
Assert.IsFalse(index1.Equals(index3 as object));
Assert.IsFalse(index1.Equals(index4 as object));
Assert.IsFalse(index1.Equals(index5 as object));
Assert.IsFalse(index1.Equals(index6 as object));
}
/// <summary>
/// Gets the system index definitions for all processes in the runtime database.
/// </summary>
/// <returns>
/// The collection of index definitions.
/// </returns>
private IEnumerable<DbIndexDefinition> GetExistingSystemIndexes()
{
const string CommandText = @"
DECLARE @Indexes TABLE
(
object_id INT
,index_id INT
,[TableName] NVARCHAR(128)
,[IndexName] NVARCHAR(128)
,[IsUnique] BIT
)
INSERT INTO @Indexes
SELECT
i.object_id
,i.index_id
,t.name AS [TableName]
,i.name AS [IndexName]
,i.is_unique AS [IsUnique]
FROM
sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE s.name = 'dbo' AND i.name LIKE 'SystemIndex-%'
SELECT
[TableName]
,[IndexName]
,[IsUnique]
FROM
@Indexes
SELECT
i.[TableName]
,i.[IndexName]
,c.name AS [ColumnName]
,ic.is_descending_key AS [IsDescending]
FROM
sys.index_columns ic
INNER JOIN @Indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
INNER JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
ORDER BY i.[TableName], i.[IndexName], ic.key_ordinal";
using (var ctx = GetRuntimeDatabaseConnectionManager())
{
using (var cmd = new SqlCommand(CommandText, ctx.Connection))
{
using (var reader = cmd.ExecuteReader())
{
var indexes = new List<DbIndexDefinition>();
while (reader.Read())
{
var index = new DbIndexDefinition
{
TableName = reader.GetString("TableName"),
Name = reader.GetString("IndexName"),
IsUnique = reader.GetBoolean("IsUnique")
};
indexes.Add(index);
}
reader.NextResult();
DbIndexDefinition currentIndex = null;
while (reader.Read())
{
var tableName = reader.GetString("TableName");
var indexName = reader.GetString("IndexName");
if (currentIndex == null || currentIndex.TableName != tableName || currentIndex.Name != indexName)
{
currentIndex = indexes.First(i => i.TableName == tableName && i.Name == indexName);
}
currentIndex.Columns.Add(
new DbIndexColumnDefinition
{
Name = reader.GetString("ColumnName"),
SortOrder = reader.GetBoolean("IsDescending") ? DbSortOrder.Descending : DbSortOrder.Ascending
});
}
return indexes;
}
}
}
}
/// <summary>
/// Drops the specified index.
/// </summary>
/// <param name="index">
/// The index.
/// </param>
private void TryDropIndex(DbIndexDefinition index)
{
try
{
var commandText = string.Format(CultureInfo.InvariantCulture, "DROP INDEX [{0}] ON [dbo].[{1}]", index.Name, index.TableName);
using (var ctx = GetRuntimeDatabaseConnectionManager())
{
using (var cmd = new SqlCommand(commandText, ctx.Connection))
{
cmd.ExecuteNonQuery();
}
}
}
catch (SqlException ex)
{
Logger.Log(LogSeverity.Error, "SqlServerDatabaseGenerator", ex);
}
}
/// <summary>
/// Creates the specified index in the runtime database.
/// </summary>
/// <param name="indexDefinition">
/// The index definition.
/// </param>
private void TryCreateIndex(DbIndexDefinition indexDefinition)
{
try
{
var indexType = indexDefinition.IsUnique ? "UNIQUE" : "NONCLUSTERED";
var indexColumnSelectStatements = indexDefinition.Columns.Select(c => string.Format(" SELECT '{0}' AS [ColumnName]", c.Name));
var indexColumnSelectSeparator = Environment.NewLine + string.Format(" UNION") + Environment.NewLine;
var commandText = new StringBuilder();
commandText.AppendFormat(
@"
DECLARE @totalBytes INT;
WITH IndexColumns
AS
(
{4}
)
SELECT @totalBytes = SUM(c.max_length)
FROM
sys.columns c
INNER JOIN sys.tables t ON t.object_id = c.object_id
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
INNER JOIN IndexColumns ic ON ic.[ColumnName] = c.name
WHERE t.name = '{2}' AND s.name = 'dbo'
IF ((@totalBytes IS NULL) OR (@totalBytes <= 900))
BEGIN
CREATE {0} INDEX [{1}] ON [dbo].[{2}] ({3}) WHERE [IsRemoved] = 0
END
ELSE
BEGIN
RAISERROR(N'Cannot create the index ''{1}'' because the index key size is greater than 900 bytes.', 16, 0)
END
",
indexType,
indexDefinition.Name,
indexDefinition.TableName,
string.Join(", ", indexDefinition.Columns.Select(FormatIndexColumn)),
string.Join(indexColumnSelectSeparator, indexColumnSelectStatements));
using (var ctx = GetRuntimeDatabaseConnectionManager())
{
using (var cmd = new SqlCommand(commandText.ToString(), ctx.Connection))
{
cmd.ExecuteNonQuery();
}
}
}
catch (SqlException ex)
{
Logger.Log(LogSeverity.Error, "SqlServerDatabaseGenerator", ex);
}
}
/// <summary>
/// Determines whether the specified index is equivalent to the clustered index.
/// </summary>
/// <param name="index">
/// The index definition.
/// </param>
/// <returns>
/// <c>true</c> if the specified index is equivalent to the clustered index; otherwise, <c>false</c>.
/// </returns>
private static bool IsEquivalentToClusteredIndex(DbIndexDefinition index)
{
return index.Columns.Count == 1 && index.Columns[0].Name == Constants.IdColumnName;
}
/// <summary>
/// Determines whether the specified index is reduntant, i.e. there is an equivalent index on the same table.
/// </summary>
/// <param name="index">
/// The index definition.
/// </param>
/// <returns>
/// <c>true</c> if the specified index is redundant; otherwise, <c>false</c>.
/// </returns>
private static bool IsReduntant(DbIndexDefinition index)
{
return IsEquivalentToClusteredIndex(index);
}
/// <summary>
/// Gets the definitions of the system indexes that should be created for the processes in the specified libraries.
/// </summary>
/// <param name="libraryTypes">
/// The library types.
/// </param>
/// <returns>
/// The collection of index definitions.
/// </returns>
private static IEnumerable<DbIndexDefinition> GetProcessIndexDefinitions(LibraryTypes libraryTypes)
{
const string CommandText = @"
DECLARE @Indexes TABLE
(
[Id] INT
,[ProcessName] NVARCHAR(200)
,[IsUnique] BIT
);
INSERT INTO @Indexes
SELECT
i.[Id]
,p.[SystemName]
,i.[IsUnique]
FROM
[dbo].[ProcessIndexes] i
INNER JOIN [dbo].[Processes] p ON p.[Id] = i.[ProcessId]
WHERE p.[IsRemoved] = 0 AND p.[IsInactive] = 0 AND (
(p.[IsSystem] = 1 AND @includeSystemProcesses = 1) OR (p.[IsSystem] = 0 AND @includeCustomProcesses = 1))
SELECT *
FROM
@Indexes
SELECT
f.[IndexId]
,f.[FieldName]
,f.[SortOrder]
FROM
[dbo].[ProcessIndexFields] f
INNER JOIN @Indexes i ON i.[Id] = f.[IndexId]
ORDER BY f.[IndexId], f.[FieldOrdinal]";
using (var ctx = GetMetaDatabaseConnectionManager())
{
using (var cmd = new SqlCommand(CommandText, ctx.Connection))
{
cmd.Parameters.AddWithValue("@includeSystemProcesses", libraryTypes.HasFlag(LibraryTypes.System));
cmd.Parameters.AddWithValue("@includeCustomProcesses", libraryTypes.HasFlag(LibraryTypes.Custom));
using (var reader = cmd.ExecuteReader())
{
var indexList = new List<DbIndexDefinition>();
var indexMap = new Dictionary<int, DbIndexDefinition>();
while (reader.Read())
{
var indexId = reader.GetInt32("Id");
var indexName = string.Format("SystemIndex-{0}", indexId);
var index = new DbIndexDefinition
{
Name = indexName,
TableName = reader.GetString("ProcessName"),
IsUnique = reader.GetBoolean("IsUnique")
};
indexList.Add(index);
indexMap.Add(indexId, index);
}
reader.NextResult();
while (reader.Read())
{
var indexId = reader.GetInt32("IndexId");
var index = indexMap[indexId];
index.Columns.Add(
new DbIndexColumnDefinition
{
Name = reader.GetString("FieldName"),
SortOrder = reader.GetEnum("SortOrder", DbSortOrder.Unspecified)
});
}
return indexList;
}
}
}
}
public void GetHashCodeTest()
{
// Arrange.
var index1 = new DbIndexDefinition
{
Name = "TestIndex",
TableName = "TestTable",
IsUnique = false,
Columns = { new DbIndexColumnDefinition { Name = "TestColumn", SortOrder = DbSortOrder.Ascending } }
};
var index2 = new DbIndexDefinition
{
Name = "TestIndex",
TableName = "TestTable",
IsUnique = false,
Columns = { new DbIndexColumnDefinition { Name = "TestColumn", SortOrder = DbSortOrder.Ascending } }
};
// Act / Assert.
Assert.AreEqual(index1.GetHashCode(), index2.GetHashCode());
}
