public static async Task<IReadOnlyCollection<string>> ListInactivePackageIdReports(SqlConnectionStringBuilder sourceDatabase, DateTime reportGenerationTime)
{
using (var connection = await sourceDatabase.ConnectTo())
{
var command = new SqlCommand("[dbo].[DownloadReportListInactive]", connection);
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = _commandTimeout;
command.Parameters.Add("ReportGenerationTime", SqlDbType.DateTime).Value = reportGenerationTime;
var packageIds = new List<string>();
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
packageIds.Add(reader.GetString(0));
}
}
return packageIds;
}
}
private async Task<int> GetNumberOfRecordsToPurge(SqlConnectionStringBuilder Source, DateTime minTimestampToKeep)
{
var sql = @"
SELECT COUNT(*)
FROM PackageStatistics WITH (NOLOCK)
WHERE [Timestamp] < @MinTimestampToKeep";
using (var connection = await Source.ConnectTo())
{
var command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@MinTimestampToKeep", minTimestampToKeep);
return await command.ExecuteScalarAsync() as int? ?? 0;
}
}
private async Task<DateTime?> GetMinTimestampToKeep(SqlConnectionStringBuilder Destination)
{
// Get the most recent cursor window that is older than the days we want to keep.
// By getting the MAX(MinTimestamp), we'll delete statistics older than the beginning of the
// most recent window that has begun processing (but isn't guaranteed to have completed).
// Note that we made sure to treat DaysToKeep as a NEGATIVE number for the expected behavior
var sql = @"
SELECT MAX(MinTimestamp)
FROM CollectorCursor
WHERE MinTimestamp <= DATEADD(day, -ABS(@DaysToKeep), convert(date, GETUTCDATE()))";
using (var connection = await Destination.ConnectTo())
{
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@DaysToKeep", DaysToKeep);
return await command.ExecuteScalarAsync() as DateTime?;
}
}
public static async Task UpdateDirtyPackageIdCursor(SqlConnectionStringBuilder sourceDatabase, DateTime runToCursor)
{
using (var connection = await sourceDatabase.ConnectTo())
{
var command = new SqlCommand("[dbo].[UpdateDirtyPackageIdCursor]", connection);
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = _commandTimeout;
command.Parameters.Add("@Position", SqlDbType.DateTime).Value = runToCursor;
await command.ExecuteNonQueryAsync();
}
}
private static async Task PutDownloadRecords(SqlConnectionStringBuilder target, XDocument batch, ReplicationTargetMarker currentCursor, ReplicationTargetMarker newCursor)
{
using (var connection = await target.ConnectTo())
{
using (var transaction = connection.BeginTransaction())
{
using (var command = new SqlCommand("AddDownloadFacts", connection, transaction))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@facts", batch.ToString());
command.Parameters.AddWithValue("@cursorMinTimestamp", currentCursor.MinTimestamp);
command.Parameters.AddWithValue("@cursorMaxTimestamp", currentCursor.MaxTimestamp);
command.Parameters.AddWithValue("@cursor", newCursor.Cursor);
await command.ExecuteNonQueryAsync();
transaction.Commit();
}
}
}
}
private static async Task<IReadOnlyCollection<DirtyPackageId>> GetDirtyPackageIdsFromWarehouse(SqlConnectionStringBuilder sourceDatabase, DateTime reportGenerationTime)
{
using (var connection = await sourceDatabase.ConnectTo())
{
var command = new SqlCommand("[dbo].[GetDirtyPackageIds]", connection);
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = _commandTimeout;
command.Parameters.Add("ReportGenerationTime", SqlDbType.DateTime).Value = reportGenerationTime;
var packageIds = new List<DirtyPackageId>();
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
packageIds.Add(new DirtyPackageId(reader.GetString(0), reader.GetDateTime(1)));
}
}
return packageIds;
}
}
private static async Task<XDocument> GetDownloadRecords(SqlConnectionStringBuilder source, ReplicationSourceMarker sourceMarker, ReplicationTargetMarker targetMarker, int batchSize)
{
using (var connection = await source.ConnectTo())
{
using (var command = new SqlCommand(@"
SELECT TOP(@batchSize)
PackageStatistics.[Key] 'originalKey',
PackageRegistrations.[Id] 'packageId',
Packages.[Version] 'packageVersion',
Packages.[Listed] 'packageListed',
Packages.[Title] 'packageTitle',
Packages.[Description] 'packageDescription',
Packages.[IconUrl] 'packageIconUrl',
ISNULL(PackageStatistics.[UserAgent], '') 'downloadUserAgent',
ISNULL(PackageStatistics.[Operation], '') 'downloadOperation',
PackageStatistics.[Timestamp] 'downloadTimestamp',
PackageStatistics.[ProjectGuids] 'downloadProjectTypes',
PackageStatistics.[DependentPackage] 'downloadDependentPackageId'
FROM PackageStatistics
INNER JOIN Packages ON PackageStatistics.PackageKey = Packages.[Key]
INNER JOIN PackageRegistrations ON PackageRegistrations.[Key] = Packages.PackageRegistrationKey
WHERE PackageStatistics.[Key] >= @minSourceKey
AND PackageStatistics.[Key] <= @maxSourceKey
AND PackageStatistics.[Timestamp] >= @minTimestamp
AND PackageStatistics.[Timestamp] < @maxTimestamp
AND PackageStatistics.[Key] > @cursor
ORDER BY PackageStatistics.[Key]
FOR XML RAW('fact'), ELEMENTS, ROOT('facts')
", connection))
{
command.Parameters.AddWithValue("@batchSize", batchSize);
command.Parameters.AddWithValue("@minSourceKey", sourceMarker.MinKey);
command.Parameters.AddWithValue("@maxSourceKey", sourceMarker.MaxKey);
command.Parameters.AddWithValue("@cursor", targetMarker.Cursor ?? 0);
command.Parameters.AddWithValue("@minTimestamp", targetMarker.MinTimestamp);
command.Parameters.AddWithValue("@maxTimestamp", targetMarker.MaxTimestamp);
var factsReader = await command.ExecuteXmlReaderAsync();
var nodeType = factsReader.MoveToContent();
if (nodeType != XmlNodeType.None)
{
var factsDocument = XDocument.Load(factsReader);
return factsDocument;
}
else
{
// No data returned
return null;
}
}
}
}
private static async Task ClearDownloadFacts(SqlConnectionStringBuilder target, DateTime minTimestamp, DateTime maxTimestamp)
{
int totalRecordsCleared = 0;
int recordsClearedInBatch = 0;
do
{
JobEventSourceLog.ClearingDownloadFacts(minTimestamp, maxTimestamp);
using (var connection = await target.ConnectTo())
{
// This proc will delete 5000 records at a time, so we have to run in a loop until there's nothing more to delete
using (var command = new SqlCommand("ClearDownloadFacts", connection) { CommandTimeout = 60 * 30 }) // 30-minute timeout
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@minTimestamp", minTimestamp);
command.Parameters.AddWithValue("@maxTimestamp", maxTimestamp);
var recordsCleared = new SqlParameter() { Direction = ParameterDirection.ReturnValue };
command.Parameters.Add(recordsCleared);
await command.ExecuteNonQueryAsync();
recordsClearedInBatch = (int)recordsCleared.Value;
totalRecordsCleared += recordsClearedInBatch;
}
}
JobEventSourceLog.ClearedDownloadFacts(recordsClearedInBatch, "Batch Completed.");
}
while (recordsClearedInBatch == 5000); // Hard-coded to match the stored proc - allows us to stop when done
JobEventSourceLog.ClearedDownloadFacts(totalRecordsCleared, "Finished.");
}
private static async Task<int?> GetTargetCursor(SqlConnectionStringBuilder target, ReplicationTargetMarker targetMarker)
{
using (var connection = await target.ConnectTo())
{
using (var command = new SqlCommand("GetCursor", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@minTimestamp", targetMarker.MinTimestamp);
command.Parameters.AddWithValue("@maxTimestamp", targetMarker.MaxTimestamp);
return await command.ExecuteScalarAsync() as int?;
}
}
}
private static async Task<ReplicationTargetMarker> GetReplicationTargetMarker(SqlConnectionStringBuilder target, ReplicationSourceMarker sourceMarker)
{
using (var connection = await target.ConnectTo())
{
using (var command = new SqlCommand("CreateCursor", connection))
{
var minTimestamp = new SqlParameter("@minTimestamp", sourceMarker.MinTimestamp) { Direction = ParameterDirection.InputOutput };
var maxTimestamp = new SqlParameter("@maxTimestamp", sourceMarker.MaxTimestamp) { Direction = ParameterDirection.InputOutput };
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(minTimestamp);
command.Parameters.Add(maxTimestamp);
await command.ExecuteNonQueryAsync();
// If the min/max pair is null then that means there are no records missing
// from the target. So we use the MaxTimestamp as the null value for BOTH
// as that will result in no records to replicate, but we also set the flag.
var minTimestampValue = (minTimestamp.Value as DateTime?) ?? sourceMarker.MaxTimestamp;
var maxTimestampValue = (maxTimestamp.Value as DateTime?) ?? sourceMarker.MaxTimestamp;
return new ReplicationTargetMarker
{
MinTimestamp = minTimestampValue,
MaxTimestamp = maxTimestampValue,
TimeWindowNeedsReplication = (minTimestampValue < maxTimestampValue)
};
}
}
}
private static async Task<ReplicationSourceMarker> GetReplicationSourceMarker(SqlConnectionStringBuilder source, DateTime? minTimestamp, DateTime? maxTimestamp)
{
string sql = @"
SELECT MIN([Key]) AS MinKey
, MAX([Key]) AS MaxKey
, MIN([Timestamp]) AS MinTimestamp
, MAX([Timestamp]) AS MaxTimestamp
, COUNT(*) AS Records
FROM PackageStatistics
WHERE [Timestamp] >= @minTimestamp
AND [Timestamp] < @maxTimestamp";
using (var connection = await source.ConnectTo())
{
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@minTimestamp", minTimestamp ?? System.Data.SqlTypes.SqlDateTime.MinValue);
command.Parameters.AddWithValue("@maxTimestamp", maxTimestamp ?? System.Data.SqlTypes.SqlDateTime.MaxValue);
using (var result = await command.ExecuteReaderAsync(CommandBehavior.SingleResult | CommandBehavior.SingleRow | CommandBehavior.KeyInfo))
{
if (result.HasRows && result.Read())
{
if (!result.IsDBNull(result.GetOrdinal("MinKey")) && !result.IsDBNull(result.GetOrdinal("MaxKey")))
{
return new ReplicationSourceMarker
{
MinKey = result.GetInt32(result.GetOrdinal("MinKey")),
MaxKey = result.GetInt32(result.GetOrdinal("MaxKey")),
// Keep the original timestamp min/max values if specified
// Otherwise we lose the boundary values and might not process the window (thinking it's incomplete)
MinTimestamp = minTimestamp ?? result.GetDateTime(result.GetOrdinal("MinTimestamp")),
MaxTimestamp = maxTimestamp ?? result.GetDateTime(result.GetOrdinal("MaxTimestamp")),
RecordsToReplicate = result.GetInt32(result.GetOrdinal("Records"))
};
}
else
{
return new ReplicationSourceMarker { RecordsToReplicate = 0 };
}
}
}
}
return new ReplicationSourceMarker
{
MinKey = 0,
MaxKey = 0
};
}