private static void PrintNotUsed(DatabasesUsage dbUsage)
{
string dbName = "test";
TimeSpan notUsedThreshold = TimeSpan.FromDays(1);
DateTime atLeastAccessDay = DateTime.Now.Date.Subtract(notUsedThreshold);
ISet <string> allTables = new HashSet <string>();
QueryPostgresContext postgresContext = new QueryPostgresContext();
postgresContext.Query = String.Format("SELECT * FROM pg_catalog.pg_tables where schemaname = '{0}'", dbName);
new QueryPostgresCommand(postgresContext).Execute();
foreach (var item in postgresContext.Records)
{
allTables.Add(item.tablename);
}
var db = dbUsage.Databases[dbName];
foreach (var tableName in allTables)
{
if (!db.Tables.ContainsKey(tableName))
{
if (dbUsage.DayGenerationExecutions.FirstOrDefault().Key <= atLeastAccessDay)
{
Console.WriteLine("Not used table: {0} since: {1}", tableName, dbUsage.DayGenerationExecutions.FirstOrDefault().Key);
}
}
else
{
var table = db.Tables[tableName];
SortedList <DateTime, long> setToUse = table.IdxScans;
if (table.IdxScans.LastOrDefault().Key == default(DateTime))
{
setToUse = table.SeqScans;
}
DateTime lastUsedDay = table.IdxScans.LastOrDefault().Key == default(DateTime) ? dbUsage.DayGenerationExecutions.FirstOrDefault().Key : table.IdxScans.LastOrDefault().Key;
if (lastUsedDay <= atLeastAccessDay)
{
Console.WriteLine("Not used table: {0} since: {1}", tableName, lastUsedDay);
}
ISet <string> allIndices = new HashSet <string>();
postgresContext = new QueryPostgresContext();
postgresContext.Query = String.Format("SELECT * FROM pg_catalog.pg_indexes where schemaname = '{0}' and tablename = '{1}'", dbName, tableName);
new QueryPostgresCommand(postgresContext).Execute();
foreach (var item in postgresContext.Records)
{
allIndices.Add(item.indexname);
}
foreach (var indexName in allIndices)
{
if (!table.Indices.ContainsKey(indexName))
{
if (dbUsage.DayGenerationExecutions.FirstOrDefault().Key <= atLeastAccessDay)
{
Console.WriteLine("Not used index: {0} since: {1}", indexName, dbUsage.DayGenerationExecutions.FirstOrDefault().Key);
}
}
else
{
var index = table.Indices[indexName];
lastUsedDay = index.IdxScans.LastOrDefault().Key == default(DateTime) ? dbUsage.DayGenerationExecutions.FirstOrDefault().Key : index.IdxScans.LastOrDefault().Key;
if (lastUsedDay <= atLeastAccessDay)
{
Console.WriteLine("Not used index: {0} since: {1}", indexName, lastUsedDay);
}
}
}
}
}
ISet <string> allProcedures = new HashSet <String>();
postgresContext = new QueryPostgresContext();
postgresContext.Query = String.Format("SELECT n.nspname, p.proname FROM pg_catalog.pg_proc p INNER JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE n.nspname = '{0}'", dbName);
new QueryPostgresCommand(postgresContext).Execute();
foreach (var item in postgresContext.Records)
{
allProcedures.Add(item.proname);
}
foreach (var procName in allProcedures)
{
if (!db.Procedures.ContainsKey(procName))
{
if (dbUsage.DayGenerationExecutions.FirstOrDefault().Key <= atLeastAccessDay)
{
Console.WriteLine("Not used proc: {0} since: {1}", procName, dbUsage.DayGenerationExecutions.FirstOrDefault().Key);
}
}
else
{
var proc = db.Procedures[procName];
DateTime lastUsedDay = proc.Calls.LastOrDefault().Key == default(DateTime) ? dbUsage.DayGenerationExecutions.FirstOrDefault().Key : proc.Calls.LastOrDefault().Key;
if (lastUsedDay <= atLeastAccessDay)
{
Console.WriteLine("Not used proc: {0} since: {1}", procName, lastUsedDay);
}
}
}
}
public NonQueryPostgresCommand(QueryPostgresContext context)
{
this.context = context;
}