/// <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;
}
}
}
}
/// <summary>
/// Determines whether the specified process is included in the specified libraries.
/// </summary>
/// <param name="libraryTypes">
/// The library types.
/// </param>
/// <param name="processName">
/// The process name.
/// </param>
/// <returns>
/// <c>true</c> if the specified process is included in the specified libraries; otherwise, <c>false</c>.
/// </returns>
private static bool IsProcessIncluded(LibraryTypes libraryTypes, string processName)
{
return Constants.AllSystemProcesses.Contains(processName) ? libraryTypes.HasFlag(LibraryTypes.System) : libraryTypes.HasFlag(LibraryTypes.Custom);
}
/// <summary>
/// Retrieves the scripts that update the full-text indexes on processes in the specified libraries.
/// </summary>
/// <param name="libraryTypes">
/// The library types.
/// </param>
/// <returns>
/// The collection of scripts.
/// </returns>
public IList<string> GetFullTextUpdateScripts(LibraryTypes libraryTypes)
{
const string CommandText = @"
SELECT
pp.[FullTextUpdateScript]
FROM
[dbo].[PublishedProcesses] pp
INNER JOIN [dbo].[Processes] p ON p.[Id] = pp.[ProcessId]
WHERE p.[IsRemoved] = 0
AND p.[IsInactive] = 0
AND LEN(pp.[FullTextUpdateScript]) > 0
AND (p.[IsSystem] = 1 AND @includeSystemProcesses = 1 OR p.[IsSystem] = 0 AND @includeUserProcesses = 1)";
using (var connectionManager = GetConnectionManager())
{
using (var cmd = new SqlCommand(CommandText, connectionManager.Connection))
{
cmd.Parameters.AddWithValue("@includeSystemProcesses", libraryTypes.HasFlag(LibraryTypes.System));
cmd.Parameters.AddWithValue("@includeUserProcesses", libraryTypes.HasFlag(LibraryTypes.Custom));
using (var reader = new SafeDataReader(cmd.ExecuteReader()))
{
var result = new List<string>();
while (reader.Read())
{
result.Add(reader.GetString(0));
}
return result;
}
}
}
}