/// <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>
/// Removes indexes from database if indexes are not in the dictionary.
/// </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 index(es) were properly removed.</returns>
private static bool RemoveDBIndexesToMatch(IDbAdapter adapter, DataTable schema, IDictionary <string, bool> columns)
{
if (!adapter.ExistsTable(schema.TableName))
{
Log.Error(String.Format("Error updating indexes: Table {0} does not exist", schema.TableName));
return(false);
}
try
{
Log.Debug(String.Format("Checking to see if indexes should be removed from table '{0}'..", schema.TableName));
var dbIndexes = adapter.GetIndexes(schema.TableName.ToString());
var indexesToDrop = new HashSet <string>();
foreach (var dbIndex in dbIndexes)
{
foreach (var indexedColumn in dbIndex.IndexedColumns)
{
if (!columns.Keys.Contains(indexedColumn))
{
indexesToDrop.Add(dbIndex.IndexName);
}
}
}
foreach (var indexToDrop in indexesToDrop)
{
adapter.DropIndex(indexToDrop);
}
return(true);
}
catch (Exception ex)
{
Log.Error(String.Format("Unable to remove indexes from table '{0}': {1}", schema.TableName, ex.Message));
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>
/// Removes indexes from database if indexes are not in the dictionary.
/// </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 index(es) were properly removed.</returns>
private static bool RemoveDBIndexesToMatch(IDbAdapter adapter, DataTable schema, IDictionary<string, bool> columns)
{
if (!adapter.ExistsTable(schema.TableName))
{
Log.Error(String.Format("Error updating indexes: Table {0} does not exist", schema.TableName));
return false;
}
try
{
Log.Debug(String.Format("Checking to see if indexes should be removed from table '{0}'..", schema.TableName));
var dbIndexes = adapter.GetIndexes(schema.TableName.ToString());
var indexesToDrop = new HashSet<string>();
foreach (var dbIndex in dbIndexes)
{
foreach (var indexedColumn in dbIndex.IndexedColumns)
{
if (!columns.Keys.Contains(indexedColumn))
{
indexesToDrop.Add(dbIndex.IndexName);
}
}
}
foreach (var indexToDrop in indexesToDrop)
{
adapter.DropIndex(indexToDrop);
}
return true;
}
catch (Exception ex)
{
Log.Error(String.Format("Unable to remove indexes from table '{0}': {1}", schema.TableName, ex.Message));
return false;
}
}