public async Task <List <ProgressItem> > LoadByCorrelationIdAsync(Guid correlationId, CancellationToken cancellationToken)
{
var items = new List <ProgressItem>();
await ExecuteAsync(async cmd =>
{
cmd.CommandText = await SqlLoader.GetScript("Progress.LoadByCorrelationId");
cmd.AddParameter("@CorrelationId", correlationId);
using (var rdr = await cmd.ExecuteReaderAsync(cancellationToken))
{
while (await rdr.ReadAsync(cancellationToken))
{
var workItem = new ProgressItem
{
CorrelationId = rdr.Map <Guid>(nameof(ProgressItem.CorrelationId)),
Id = rdr.Map <Guid>(nameof(ProgressItem.Id)),
Name = rdr.Map <string>(nameof(ProgressItem.Name)),
Note = rdr.Map <string>(nameof(ProgressItem.Note)),
Queue = rdr.Map <string>(nameof(ProgressItem.Queue)),
RecordedOn = rdr.Map <DateTime>(nameof(ProgressItem.RecordedOn)),
Value = rdr.Map <int>(nameof(ProgressItem.Value)),
WorkId = rdr.Map <Guid>(nameof(ProgressItem.WorkId))
};
items.Add(workItem);
}
}
}, cancellationToken);
return(items);
}
public async Task <IProgressProvider> CreateAsync(string[] queues, CancellationToken cancellationToken = default(CancellationToken))
{
if (_provider != null)
{
return(_provider);
}
try
{
await SyncLock.WaitAsync(cancellationToken);
await SqlRunner.ExecuteAsync(async cmd =>
{
cmd.CommandText = await SqlLoader.GetScript("Progress.Setup");
await cmd.ExecuteNonQueryAsync(cancellationToken);
}, _connectionString, false, cancellationToken);
_provider = new SqlProgressProvider(_connectionString);
return(_provider);
}
finally
{
SyncLock.Release();
}
}
public async Task <IWorkProvider> CreateAsync(string[] queues, CancellationToken cancellationToken = default(CancellationToken))
{
try
{
await SyncLock.WaitAsync(cancellationToken);
if (!_initialized)
{
await SqlRunner.ExecuteAsync(async cmd =>
{
var schemaSetup = await SqlLoader.GetScript("Schema.Setup");
var workSetup = await SqlLoader.GetScript("Work.Setup");
cmd.CommandText = $"{schemaSetup};\r\n{workSetup}";
await cmd.ExecuteNonQueryAsync(cancellationToken);
}, _sqlServerQuidjiboConfiguration.ConnectionString, false, cancellationToken);
_initialized = true;
}
return(new SqlWorkProvider(
_loggerFactory.CreateLogger <SqlWorkProvider>(),
_sqlServerQuidjiboConfiguration.ConnectionString,
queues,
_sqlServerQuidjiboConfiguration.LockInterval,
_sqlServerQuidjiboConfiguration.BatchSize,
_sqlServerQuidjiboConfiguration.DaysToKeep));
}
finally
{
SyncLock.Release();
}
}
public async Task <IWorkProvider> CreateAsync(string[] queues, CancellationToken cancellationToken = default(CancellationToken))
{
try
{
await SyncLock.WaitAsync(cancellationToken);
if (!_initialized)
{
await SqlRunner.ExecuteAsync(async cmd =>
{
var schemaSetup = await SqlLoader.GetScript("Schema.Setup");
var workSetup = await SqlLoader.GetScript("Work.Setup");
cmd.CommandText = $"{schemaSetup};\r\n{workSetup}";
await cmd.ExecuteNonQueryAsync(cancellationToken);
}, _connectionString, false, cancellationToken);
_initialized = true;
}
return(new SqlWorkProvider(_connectionString, queues, _visibilityTimeout, _batchSize));
}
finally
{
SyncLock.Release();
}
}
public async Task <List <WorkItem> > ReceiveAsync(string worker, CancellationToken cancellationToken)
{
var receiveOn = DateTime.UtcNow;
var deleteOn = _daysToKeep > 0 ? receiveOn.AddDays(-_daysToKeep) : receiveOn.AddHours(-1);
if (_receiveSql == null)
{
_receiveSql = await SqlLoader.GetScript("Work.Receive");
if (_queues.Length > 0)
{
_receiveSql = _receiveSql.Replace("@Queue1",
string.Join(",", _queues.Select((x, i) => $"@Queue{i}")));
}
}
var workItems = new List <WorkItem>(_batchSize);
await ExecuteAsync(async cmd =>
{
cmd.CommandText = _receiveSql;
cmd.AddParameter("@Worker", worker);
cmd.AddParameter("@Take", _batchSize);
cmd.AddParameter("@InFlight", StatusFlags.InFlight);
cmd.AddParameter("@VisibleOn", receiveOn.AddSeconds(Math.Max(_visibilityTimeout, 30)));
cmd.AddParameter("@ReceiveOn", receiveOn);
cmd.AddParameter("@MaxAttempts", _maxAttempts);
cmd.AddParameter("@DeleteOn", deleteOn);
// dynamic parameters
for (var i = 0; i < _queues.Length; i++)
{
cmd.Parameters.AddWithValue($"@Queue{i}", _queues[i]);
}
using (var rdr = await cmd.ExecuteReaderAsync(cancellationToken))
{
while (await rdr.ReadAsync(cancellationToken))
{
var workItem = new WorkItem
{
Attempts = rdr.Map <int>(nameof(WorkItem.Attempts)),
CorrelationId = rdr.Map <Guid>(nameof(WorkItem.CorrelationId)),
ExpireOn = rdr.Map <DateTime>(nameof(WorkItem.ExpireOn)),
Id = rdr.Map <Guid>(nameof(WorkItem.Id)),
Name = rdr.Map <string>(nameof(WorkItem.Name)),
Payload = rdr.Map <byte[]>(nameof(WorkItem.Payload)),
Queue = rdr.Map <string>(nameof(WorkItem.Queue)),
ScheduleId = rdr.Map <Guid?>(nameof(WorkItem.ScheduleId))
};
workItem.Token = workItem.Id.ToString();
workItems.Add(workItem);
}
}
}, cancellationToken);
return(workItems);
}
public async Task DeleteAsync(Guid id, CancellationToken cancellationToken = default(CancellationToken))
{
await ExecuteAsync(async cmd =>
{
cmd.CommandText = await SqlLoader.GetScript("Schedule.Delete");
cmd.AddParameter("@Id", id);
await cmd.ExecuteNonQueryAsync(cancellationToken);
}, cancellationToken);
}
public async Task CompleteAsync(WorkItem item, CancellationToken cancellationToken)
{
await ExecuteAsync(async cmd =>
{
cmd.CommandText = await SqlLoader.GetScript("Work.Complete");
cmd.AddParameter("@Id", item.Id);
cmd.AddParameter("@Complete", StatusFlags.Complete);
await cmd.ExecuteNonQueryAsync(cancellationToken);
}, cancellationToken);
}
public async Task CompleteAsync(ScheduleItem item, CancellationToken cancellationToken = default(CancellationToken))
{
await ExecuteAsync(async cmd =>
{
cmd.CommandText = await SqlLoader.GetScript("Schedule.Complete");
cmd.AddParameter("@Id", item.Id);
cmd.AddParameter("@EnqueueOn", item.EnqueueOn);
cmd.AddParameter("@EnqueuedOn", item.EnqueuedOn);
await cmd.ExecuteNonQueryAsync(cancellationToken);
}, cancellationToken);
}
public async Task FaultAsync(WorkItem item, CancellationToken cancellationToken)
{
var faultedOn = DateTime.UtcNow;
await ExecuteAsync(async cmd =>
{
cmd.CommandText = await SqlLoader.GetScript("Work.Fault");
cmd.AddParameter("@Id", item.Id);
cmd.AddParameter("@VisibleOn", faultedOn.AddSeconds(Math.Max(_visibilityTimeout, 30)));
cmd.AddParameter("@Faulted", StatusFlags.Faulted);
await cmd.ExecuteNonQueryAsync(cancellationToken);
}, cancellationToken);
}
public async Task <bool> ExistsAsync(string name, CancellationToken cancellationToken = default(CancellationToken))
{
var count = 0;
await ExecuteAsync(async cmd =>
{
cmd.CommandText = await SqlLoader.GetScript("Schedule.Exists");
cmd.AddParameter("@Name", name);
count = (int)await cmd.ExecuteScalarAsync(cancellationToken);
}, cancellationToken);
return(count > 0);
}
public async Task <DateTime> RenewAsync(WorkItem item, CancellationToken cancellationToken)
{
var lockExpireOn = (item.VisibleOn ?? DateTime.UtcNow).AddSeconds(Math.Max(_visibilityTimeout, 30));
await ExecuteAsync(async cmd =>
{
cmd.CommandText = await SqlLoader.GetScript("Work.Renew");
cmd.AddParameter("@Id", item.Id);
cmd.AddParameter("@VisibleOn", lockExpireOn);
await cmd.ExecuteNonQueryAsync(cancellationToken);
}, cancellationToken);
return(lockExpireOn);
}
public async Task CreateAsync(ScheduleItem item, CancellationToken cancellationToken = default(CancellationToken))
{
await ExecuteAsync(async cmd =>
{
cmd.CommandText = await SqlLoader.GetScript("Schedule.Create");
cmd.AddParameter("@Id", item.Id);
cmd.AddParameter("@Name", item.Name);
cmd.AddParameter("@Queue", item.Queue);
cmd.AddParameter("@CronExpression", item.CronExpression);
cmd.AddParameter("@CreatedOn", item.CreatedOn);
cmd.AddParameter("@EnqueuedOn", item.EnqueuedOn);
cmd.AddParameter("@EnqueueOn", item.EnqueueOn);
cmd.AddParameter("@VisibleOn", item.VisibleOn);
cmd.AddParameter("@Payload", item.Payload);
await cmd.ExecuteNonQueryAsync(cancellationToken);
}, cancellationToken);
}
public async Task ReportAsync(ProgressItem item, CancellationToken cancellationToken)
{
await ExecuteAsync(async cmd =>
{
cmd.CommandText = await SqlLoader.GetScript("Progress.Create");
cmd.AddParameter("@Id", item.Id);
cmd.AddParameter("@WorkId", item.WorkId);
cmd.AddParameter("@CorrelationId", item.CorrelationId);
cmd.AddParameter("@Name", item.Name);
cmd.AddParameter("@Queue", item.Queue);
cmd.AddParameter("@RecordedOn", item.RecordedOn);
cmd.AddParameter("@Value", item.Value);
cmd.AddParameter("@Note", item.Note);
await cmd.ExecuteNonQueryAsync(cancellationToken);
}, cancellationToken);
await Task.CompletedTask;
}
public async Task <ScheduleItem> LoadByNameAsync(string name, CancellationToken cancellationToken = default(CancellationToken))
{
var item = default(ScheduleItem);
await ExecuteAsync(async cmd =>
{
cmd.CommandText = await SqlLoader.GetScript("Schedule.LoadByName");
cmd.AddParameter("@Name", name);
using (var rdr = await cmd.ExecuteReaderAsync(cancellationToken))
{
if (await rdr.ReadAsync(cancellationToken))
{
item = MapScheduleItem(rdr);
}
}
}, cancellationToken);
return(item);
}
public async Task <IScheduleProvider> CreateAsync(string[] queues, CancellationToken cancellationToken = default(CancellationToken))
{
try
{
await SyncLock.WaitAsync(cancellationToken);
await SqlRunner.ExecuteAsync(async cmd =>
{
var schemaSetup = await SqlLoader.GetScript("Schema.Setup");
var scheduleSetup = await SqlLoader.GetScript("Schedule.Setup");
cmd.CommandText = $"{schemaSetup};\r\n{scheduleSetup}";
await cmd.ExecuteNonQueryAsync(cancellationToken);
}, _connectionString, false, cancellationToken);
return(await Task.FromResult <IScheduleProvider>(new SqlScheduleProvider(_connectionString, queues)));
}
finally
{
SyncLock.Release();
}
}
public async Task <List <ScheduleItem> > ReceiveAsync(CancellationToken cancellationToken = default(CancellationToken))
{
var receiveOn = DateTime.UtcNow;
if (_receiveSql == null)
{
_receiveSql = await SqlLoader.GetScript("Schedule.Receive");
if (_queues.Length > 0)
{
_receiveSql = _receiveSql.Replace("@Queue1",
string.Join(",", _queues.Select((x, i) => $"@Queue{i}")));
}
}
var items = new List <ScheduleItem>(100);
await ExecuteAsync(async cmd =>
{
cmd.CommandText = _receiveSql;
cmd.AddParameter("@Take", 100);
cmd.AddParameter("@ReceiveOn", receiveOn);
cmd.AddParameter("@VisibleOn", receiveOn.AddSeconds(60));
// dynamic parameters
_queues.Select((q, i) => cmd.Parameters.AddWithValue($"@Queue{i}", q)).ToList();
using (var rdr = await cmd.ExecuteReaderAsync(cancellationToken))
{
while (await rdr.ReadAsync(cancellationToken))
{
var item = MapScheduleItem(rdr);
items.Add(item);
}
}
}, cancellationToken);
return(items);
}
public async Task SendAsync(WorkItem item, int delay, CancellationToken cancellationToken)
{
var createdOn = DateTime.UtcNow;
var visibleOn = createdOn.AddSeconds(delay);
var expireOn = visibleOn.AddDays(7);
await ExecuteAsync(async cmd =>
{
cmd.CommandText = await SqlLoader.GetScript("Work.Send");
cmd.AddParameter("@Id", item.Id);
cmd.AddParameter("@ScheduleId", item.ScheduleId);
cmd.AddParameter("@CorrelationId", item.CorrelationId);
cmd.AddParameter("@Name", item.Name);
cmd.AddParameter("@Worker", item.Worker);
cmd.AddParameter("@Queue", item.Queue);
cmd.AddParameter("@Attempts", item.Attempts);
cmd.AddParameter("@CreatedOn", createdOn);
cmd.AddParameter("@ExpireOn", expireOn);
cmd.AddParameter("@VisibleOn", visibleOn);
cmd.AddParameter("@Status", StatusFlags.New);
cmd.AddParameter("@Payload", item.Payload);
await cmd.ExecuteNonQueryAsync(cancellationToken);
}, cancellationToken);
}