/// <summary>
/// Indexes a set of columns defined by the dictionary that is passed.
/// </summary>
/// <param name="adapter">Open adapter to the database.</param>
/// <param name="schema">The schema of the table the index will be created on.</param>
/// <param name="columns">A dictionary where keys are the column names and boolean statements for whether the index is clustered.</param>
/// <returns>Returns true if the index is created.</returns>
private static bool CreateIndexes(IDbAdapter adapter, DataTable schema, IDictionary <string, bool> columns)
{
if (!adapter.ExistsTable(schema.TableName))
{
Log.Error(String.Format("Error creating index: Table {0} does not exist", schema.TableName));
return(false);
}
foreach (var column in columns)
{
try
{
var indexName = column.Key + "_idx";
adapter.CreateIndexOnTable(schema.TableName, column.Key, indexName);
if (column.Value == true)
{
adapter.ClusterIndex(schema.TableName, indexName);
}
}
catch (Exception ex)
{
Log.Error(String.Format("Error creating index on column '{0}' for table '{1}': {2}", column.Key, schema.TableName, ex.Message));
return(false);
}
}
return(true);
}
/// <summary>
/// Updates whether or not indexes are clustered.
/// </summary>
/// <param name="adapter">Open adapter to the database.</param>
/// <param name="schema">The schema of the table.</param>
/// <param name="columns">A dictionary where keys are the column names and boolean statements for whether the index is clustered.</param>
/// <returns>Returns true if the clusters on indexes were properly updated.</returns>
private static bool UpdateIndexClusters(IDbAdapter adapter, DataTable schema, IDictionary <string, bool> columns)
{
if (!adapter.ExistsTable(schema.TableName))
{
Log.Error(String.Format("Error updating index cluster status: Table {0} does not exist", schema.TableName));
return(false);
}
try
{
Log.Debug(String.Format("Checking to see if clusters on indexes should be updated for table '{0}'..", schema.TableName));
var dbIndexes = adapter.GetIndexes(schema.TableName);
// For every column associated with an index in the DB, gather the Index Name from the DB, whether the DB index is clustered,
// the name of the indexed column in the config, and whether or not the index is clustered IF the column exists in both the config and DB.
var indexesToCheck = from dbIndex in dbIndexes
from indexedColumn in dbIndex.IndexedColumns
join columnName in columns
on indexedColumn equals columnName.Key
select new
{
dbIndexName = dbIndex.IndexName,
dbIsCluster = dbIndex.IsClustered,
indexedColumn = columnName.Key,
isClustered = columnName.Value
};
foreach (var index in indexesToCheck)
{
if (index.dbIsCluster == false && index.isClustered == true)
{
adapter.ClusterIndex(schema.TableName, index.dbIndexName);
}
else if (index.dbIsCluster == true && index.isClustered == false)
{
adapter.DropIndex(index.dbIndexName);
adapter.CreateIndexOnTable(schema.TableName, index.indexedColumn, index.dbIndexName);
}
}
return(true);
}
catch
{
Log.Error(String.Format("Unable to update index cluster status for table '{0}'.", schema.TableName));
return(false);
}
}
/// <summary>
/// Updates whether or not indexes are clustered.
/// </summary>
/// <param name="adapter">Open adapter to the database.</param>
/// <param name="schema">The schema of the table.</param>
/// <param name="columns">A dictionary where keys are the column names and boolean statements for whether the index is clustered.</param>
/// <returns>Returns true if the clusters on indexes were properly updated.</returns>
private static bool UpdateIndexClusters(IDbAdapter adapter, DataTable schema, IDictionary<string, bool> columns)
{
if (!adapter.ExistsTable(schema.TableName))
{
Log.Error(String.Format("Error updating index cluster status: Table {0} does not exist", schema.TableName));
return false;
}
try
{
Log.Debug(String.Format("Checking to see if clusters on indexes should be updated for table '{0}'..", schema.TableName));
var dbIndexes = adapter.GetIndexes(schema.TableName);
// For every column associated with an index in the DB, gather the Index Name from the DB, whether the DB index is clustered,
// the name of the indexed column in the config, and whether or not the index is clustered IF the column exists in both the config and DB.
var indexesToCheck = from dbIndex in dbIndexes
from indexedColumn in dbIndex.IndexedColumns
join columnName in columns
on indexedColumn equals columnName.Key
select new
{
dbIndexName = dbIndex.IndexName,
dbIsCluster = dbIndex.IsClustered,
indexedColumn = columnName.Key,
isClustered = columnName.Value
};
foreach (var index in indexesToCheck)
{
if (index.dbIsCluster == false && index.isClustered == true)
{
adapter.ClusterIndex(schema.TableName, index.dbIndexName);
}
else if (index.dbIsCluster == true && index.isClustered == false)
{
adapter.DropIndex(index.dbIndexName);
adapter.CreateIndexOnTable(schema.TableName, index.indexedColumn, index.dbIndexName);
}
}
return true;
}
catch
{
Log.Error(String.Format("Unable to update index cluster status for table '{0}'.", schema.TableName));
return false;
}
}
/// <summary>
/// Indexes a set of columns defined by the dictionary that is passed.
/// </summary>
/// <param name="adapter">Open adapter to the database.</param>
/// <param name="schema">The schema of the table the index will be created on.</param>
/// <param name="columns">A dictionary where keys are the column names and boolean statements for whether the index is clustered.</param>
/// <returns>Returns true if the index is created.</returns>
private static bool CreateIndexes(IDbAdapter adapter, DataTable schema, IDictionary<string, bool> columns)
{
if (!adapter.ExistsTable(schema.TableName))
{
Log.Error(String.Format("Error creating index: Table {0} does not exist", schema.TableName));
return false;
}
foreach (var column in columns)
{
try
{
var indexName = column.Key + "_idx";
adapter.CreateIndexOnTable(schema.TableName, column.Key, indexName);
if (column.Value == true)
{
adapter.ClusterIndex(schema.TableName, indexName);
}
}
catch (Exception ex)
{
Log.Error(String.Format("Error creating index on column '{0}' for table '{1}': {2}", column.Key, schema.TableName, ex.Message));
return false;
}
}
return true;
}