private static string GetAggregationQuery(SqlServerStorage storage)
{
return(string.Format(@"
DECLARE @RecordsToAggregate TABLE
(
[Key] NVARCHAR(100) NOT NULL,
[Value] SMALLINT NOT NULL,
[ExpireAt] DATETIME NULL
)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
DELETE TOP (@count) [{0}].[Counter] with (readpast)
OUTPUT DELETED.[Key], DELETED.[Value], DELETED.[ExpireAt] INTO @RecordsToAggregate
SET NOCOUNT ON
;MERGE [{0}].[AggregatedCounter] AS [Target]
USING (
SELECT [Key], SUM([Value]) as [Value], MAX([ExpireAt]) AS [ExpireAt] FROM @RecordsToAggregate
GROUP BY [Key]) AS [Source] ([Key], [Value], [ExpireAt])
ON [Target].[Key] = [Source].[Key]
WHEN MATCHED THEN UPDATE SET
[Target].[Value] = [Target].[Value] + [Source].[Value],
[Target].[ExpireAt] = (SELECT MAX([ExpireAt]) FROM (VALUES ([Source].ExpireAt), ([Target].[ExpireAt])) AS MaxExpireAt([ExpireAt]))
WHEN NOT MATCHED THEN INSERT ([Key], [Value], [ExpireAt]) VALUES ([Source].[Key], [Source].[Value], [Source].[ExpireAt]);
COMMIT TRAN", storage.GetSchemaName()));
}
public IList <ServerDto> Servers()
{
return(UseConnection <IList <ServerDto> >(connection =>
{
var servers = connection.Query <Entities.Server>(
string.Format(@"select * from [{0}].Server", _storage.GetSchemaName()))
.ToList();
var result = new List <ServerDto>();
foreach (var server in servers)
{
var data = JobHelper.FromJson <ServerData>(server.Data);
result.Add(new ServerDto
{
Name = server.Id,
Heartbeat = server.LastHeartbeat,
Queues = data.Queues,
StartedAt = data.StartedAt.HasValue ? data.StartedAt.Value : DateTime.MinValue,
WorkersCount = data.WorkerCount
});
}
return result;
}));
}
public void Execute(CancellationToken cancellationToken)
{
foreach (var table in ProcessedTables)
{
Logger.DebugFormat("Removing outdated records from table '{0}'...", table);
int removedCount = 0;
do
{
_storage.UseConnection(connection =>
{
SqlServerDistributedLock.Acquire(connection, DistributedLockKey, DefaultLockTimeout);
try
{
removedCount = connection.Execute(
String.Format(@"
set transaction isolation level read committed;
delete top (@count) from [{0}].[{1}] with (readpast) where ExpireAt < @now;", _storage.GetSchemaName(), table),
new { now = DateTime.UtcNow, count = NumberOfRecordsInSinglePass });
}
finally
{
SqlServerDistributedLock.Release(connection, DistributedLockKey);
}
});
if (removedCount > 0)
{
Logger.Trace(String.Format("Removed {0} outdated record(s) from '{1}' table.", removedCount,
table));
cancellationToken.WaitHandle.WaitOne(DelayBetweenPasses);
cancellationToken.ThrowIfCancellationRequested();
}
} while (removedCount != 0);
}
cancellationToken.WaitHandle.WaitOne(_checkInterval);
}
public override IDisposable AcquireDistributedLock(string resource, TimeSpan timeout)
{
return(new SqlServerDistributedLock(
_storage,
String.Format("{0}:{1}", _storage.GetSchemaName(), resource),
timeout));
}
public override void ExpireJob(string jobId, TimeSpan expireIn)
{
QueueCommand(x => x.Execute(
string.Format(@"update [{0}].Job set ExpireAt = @expireAt where Id = @id", _storage.GetSchemaName()),
new { expireAt = DateTime.UtcNow.Add(expireIn), id = jobId }));
}
private static string GetAggregationQuery(SqlServerStorage storage)
{
return string.Format(@"
DECLARE @RecordsToAggregate TABLE
(
[Key] NVARCHAR(100) NOT NULL,
[Value] SMALLINT NOT NULL,
[ExpireAt] DATETIME NULL
)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
DELETE TOP (@count) [{0}].[Counter] with (readpast)
OUTPUT DELETED.[Key], DELETED.[Value], DELETED.[ExpireAt] INTO @RecordsToAggregate
SET NOCOUNT ON
;MERGE [{0}].[AggregatedCounter] AS [Target]
USING (
SELECT [Key], SUM([Value]) as [Value], MAX([ExpireAt]) AS [ExpireAt] FROM @RecordsToAggregate
GROUP BY [Key]) AS [Source] ([Key], [Value], [ExpireAt])
ON [Target].[Key] = [Source].[Key]
WHEN MATCHED THEN UPDATE SET
[Target].[Value] = [Target].[Value] + [Source].[Value],
[Target].[ExpireAt] = (SELECT MAX([ExpireAt]) FROM (VALUES ([Source].ExpireAt), ([Target].[ExpireAt])) AS MaxExpireAt([ExpireAt]))
WHEN NOT MATCHED THEN INSERT ([Key], [Value], [ExpireAt]) VALUES ([Source].[Key], [Source].[Value], [Source].[ExpireAt]);
COMMIT TRAN", storage.GetSchemaName());
}
