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 }; }