public IActionResult GetIndices()
{
BaseResponse <Dictionary <uint, List <IndexData> > > result = new BaseResponse <Dictionary <uint, List <IndexData> > >();
HandleException(() =>
{
var databases = DBMSRepositories.GetDatabasesRepository().GetAll();
var indicesRepository = DBMSRepositories.GetIndicesRepository();
result.Data = new Dictionary <uint, List <IndexData> >();
foreach (var d in databases)
{
try
{
using (var scope = Converter.CreateDatabaseScope(d.ID))
{
var indices = indicesRepository.GetAll().OrderBy(x => x.Name);
foreach (var i in indices)
{
if (!result.Data.ContainsKey(i.RelationID))
{
result.Data.Add(i.RelationID, new List <IndexData>());
}
result.Data[i.RelationID].Add(Converter.Convert(i));
}
}
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine(ex.Message);
}
}
result.IsSuccess = result.Data != null;
}, ex => result.ErrorMessage = ex.Message);
return(Json(result));
}
public IActionResult GetDatabases()
{
BaseResponse <List <DatabaseData> > result = new BaseResponse <List <DatabaseData> >();
HandleException(() =>
{
var repository = DBMSRepositories.GetDatabasesRepository();
var databases = repository.GetAll();
result.Data = new List <DatabaseData>();
foreach (var d in databases)
{
result.Data.Add(Converter.Convert(d));
}
result.IsSuccess = result.Data != null;
}, ex => result.ErrorMessage = ex.Message);
return(Json(result));
}
public IActionResult GetUnusedObjects(GetUnusedObjectsRequest request)
{
GetUnusedObjectsResponse result = new GetUnusedObjectsResponse();
if (request != null)
{
HandleException(() =>
{
result.Data = new UnusedObjectsData();
result.Data.Objects = new List <UnusedObjectData>();
var now = DateTime.Now;
DateTime thresholdDate = now.AddDays(-30);
var totalIndexStatsRepo = DALRepositories.GetTotalIndexStatisticsRepository();
var totalRelationStatsRepo = DALRepositories.GetTotalRelationStatisticsRepository();
var totalProceduresStatsRepo = DALRepositories.GetTotalStoredProcedureStatisticsRepository();
var totalViewsStatsRepo = DALRepositories.GetTotalViewStatisticsRepository();
bool areDataAvailableForWholePeriod = totalIndexStatsRepo.AreDataAvailableForWholePeriod(thresholdDate, now.Date) ||
totalRelationStatsRepo.AreDataAvailableForWholePeriod(thresholdDate, now.Date) ||
totalProceduresStatsRepo.AreDataAvailableForWholePeriod(thresholdDate, now.Date) ||
totalViewsStatsRepo.AreDataAvailableForWholePeriod(thresholdDate, now.Date);
var indicesLastKnownCollectionDate = totalIndexStatsRepo.GetForAllLastKnownCollectionDate(request.DatabaseID);
var relationsLastKnownCollectionDate = totalRelationStatsRepo.GetForAllLastKnownCollectionDate(request.DatabaseID);
var proceduresLastKnownCollectionDate = totalProceduresStatsRepo.GetForAllLastKnownCollectionDate(request.DatabaseID);
var viewsLastKnownCollectionDate = totalViewsStatsRepo.GetForAllLastKnownCollectionDate(request.DatabaseID);
using (var scope = Converter.CreateDatabaseScope(request.DatabaseID))
{
foreach (var i in DBMSRepositories.GetIndicesRepository().GetAll())
{
bool?isUnused = null;
DateTime?lastKnownUsageDate = null;
if (indicesLastKnownCollectionDate.TryGetValue(i.ID, out var lastKnownCollectionDate))
{
lastKnownUsageDate = lastKnownCollectionDate;
if (areDataAvailableForWholePeriod)
{
isUnused = lastKnownCollectionDate < thresholdDate;
}
}
result.Data.Objects.Add(new UnusedObjectData()
{
IsUnused = isUnused, LastKnownUsageDate = lastKnownUsageDate,
Name = i.Name + $" ON {i.SchemaName}.{i.RelationName}", ObjectType = "Index", SchemaName = i.SchemaName
});
}
foreach (var r in DBMSRepositories.GetRelationsRepository().GetAll())
{
bool?isUnused = null;
DateTime?lastKnownUsageDate = null;
if (relationsLastKnownCollectionDate.TryGetValue(r.ID, out var lastKnownCollectionDate))
{
lastKnownUsageDate = lastKnownCollectionDate;
if (areDataAvailableForWholePeriod)
{
isUnused = lastKnownCollectionDate < thresholdDate;
}
}
result.Data.Objects.Add(new UnusedObjectData()
{
IsUnused = isUnused,
LastKnownUsageDate = lastKnownUsageDate,
Name = r.Name,
ObjectType = "Relation",
SchemaName = r.SchemaName
});
}
foreach (var p in DBMSRepositories.GetStoredProceduresRepository().GetAll())
{
bool?isUnused = null;
DateTime?lastKnownUsageDate = null;
if (proceduresLastKnownCollectionDate.TryGetValue(p.ID, out var lastKnownCollectionDate))
{
lastKnownUsageDate = lastKnownCollectionDate;
if (areDataAvailableForWholePeriod)
{
isUnused = lastKnownCollectionDate < thresholdDate;
}
}
result.Data.Objects.Add(new UnusedObjectData()
{
IsUnused = isUnused,
LastKnownUsageDate = lastKnownUsageDate,
Name = p.Name,
ObjectType = "Stored procedure",
SchemaName = p.SchemaName
});
}
foreach (var v in DBMSRepositories.GetViewsRepository().GetAll())
{
bool?isUnused = null;
DateTime?lastKnownUsageDate = null;
if (viewsLastKnownCollectionDate.TryGetValue(v.ID, out var lastKnownCollectionDate))
{
lastKnownUsageDate = lastKnownCollectionDate;
if (areDataAvailableForWholePeriod)
{
isUnused = lastKnownCollectionDate < thresholdDate;
}
}
result.Data.Objects.Add(new UnusedObjectData()
{
IsUnused = isUnused,
LastKnownUsageDate = lastKnownUsageDate,
Name = v.Name,
ObjectType = "View",
SchemaName = v.SchemaName
});
}
}
result.Data.Objects.Sort((x, y) =>
{
var cmp = (x.LastKnownUsageDate?.Ticks ?? double.MaxValue).CompareTo(y.LastKnownUsageDate?.Ticks ?? double.MaxValue);
if (cmp == 0)
{
cmp = x.SchemaName.CompareTo(y.SchemaName);
if (cmp == 0)
{
cmp = x.Name.CompareTo(y.Name);
}
}
return(cmp);
});
result.IsSuccess = result.Data != null;
}, ex => result.ErrorMessage = ex.Message);
}
return(Json(result));
}
