public void DeleteQueue(int queueId) { const string sql = "DELETE FROM [Messenger].[Queues] WHERE [QueueId] = @queueId"; using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew)) { db.Connection.Execute(sql, new { queueId }); db.Complete(); } }
public int? FetchQueueId(string queueName) { const string sql = "SELECT [QueueId] FROM [Messenger].[Queues] (nolock) WHERE [Name] = @queueName"; int? queueId; using (var db = new FatQueueDatabase(_connectionFactory)) { queueId = db.Connection.ExecuteScalar<int?>(sql, new { queueName }); db.Complete(); } return queueId; }
public int? FetchQueueId(string queueName) { const string sql = "SELECT QueueId FROM Messenger.Queues WHERE Name = :queueName"; int? queueId; using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew, IsolationLevel.ReadUncommitted)) { queueId = db.Connection.ExecuteScalar<int?>(sql, new {queueName}); db.Complete(); } return queueId; }
public int CreateQueue(string queueName) { const string fetchSql = "SELECT [QueueId] FROM [Messenger].[Queues] WITH (UPDLOCK, ROWLOCK, HOLDLOCK) WHERE [Name] = @queueName"; const string insertSql = "INSERT INTO [Messenger].[Queues] ([Name]) VALUES(@queueName); SELECT SCOPE_IDENTITY();"; using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew)) { var queueId = db.Connection.ExecuteScalar<int?>(fetchSql, new { queueName }); if (!queueId.HasValue) queueId = db.Connection.ExecuteScalar<int>(insertSql, new { queueName }); db.Complete(); return queueId.Value; } }
public int CreateQueue(string queueName) { const string fetchSql = "SELECT QueueId FROM Messenger.Queues WHERE Name = :queueName"; const string insertSql = "INSERT INTO Messenger.Queues (Name) VALUES(:queueName); SELECT LASTVAL();"; const string lockSql = "LOCK TABLE Messenger.Queues"; int? queueId; using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew)) { queueId = db.Connection.ExecuteScalar<int?>(fetchSql, new {queueName}); if (!queueId.HasValue) { db.Connection.Execute(lockSql); queueId = db.Connection.ExecuteScalar<int?>(fetchSql, new {queueName}); if (!queueId.HasValue) { queueId = db.Connection.ExecuteScalar<int>(insertSql, new {queueName}); } } db.Complete(); } return queueId.Value; }
public void ReleaseProcessLock(params string [] processNames) { if (processNames == null || processNames.Length == 0) return; const string sqlUpdate = "update [Messenger].[Queues] set ProcessName = null, ProcessingStarted = null, NextTryTime = null where ProcessName in @processNames"; using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew)) { db.Connection.Execute(sqlUpdate, new {processNames}); db.Complete(); } }
public void PurgeCompletedMessages(DateTime olderThan) { const string sql = "delete from Messenger.CompletedMessages where CompletedDate < @olderThan"; using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew)) { db.Connection.Execute(sql, new { olderThan }); db.Complete(); } }
public void Heartbeat(string processName) { const string sql = //"MERGE [Messenger].[Heartbeat] WITH (HOLDLOCK) AS target " + "MERGE [Messenger].[Heartbeat] AS target " + "USING (SELECT @processName) AS source (ProcessName) " + "ON (target.ProcessName = source.ProcessName) " + "WHEN MATCHED THEN " + " UPDATE SET LastBeat = SYSUTCDATETIME() " + "WHEN NOT MATCHED THEN " + " INSERT (ProcessName, LastBeat) " + " VALUES (source.ProcessName, SYSUTCDATETIME());"; using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew)) { db.Connection.Execute(sql, new { processName }); db.Complete(); } }
public void ClearStaleProcesses(DateTime olderThan) { const string sql = "select ProcessName from [Messenger].[Heartbeat] where LastBeat < @olderThan"; const string sqlUpdate = "update [Messenger].[Queues] set ProcessName = null, ProcessingStarted = null, NextTryTime = null where ProcessName in @processNames"; const string sqlDelete = "delete from [Messenger].[Heartbeat] where ProcessName in @processNames"; using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew)) { var processNames = db.Connection.Query<string>(sql, new { olderThan }).ToArray(); if (processNames.Length > 0) { db.Connection.Execute(sqlUpdate, new {processNames}); db.Connection.Execute(sqlDelete, new {processNames}); } db.Complete(); } }
public QueueInfo LockQueue(string processName) { /* const string sql = "update top (1) Messenger.Queues with (rowlock) " + "set ProcessingStarted = SYSUTCDATETIME() " + ", ProcessName = @processName " + "output inserted.Name, inserted.QueueId, inserted.Retries " + "where QueueId = ( " + " select top 1 q.QueueId " + " from Messenger.Queues q (readpast) " + " join Messenger.Messages m with (readpast) on m.QueueId = q.QueueId " + " where q.ProcessingStarted is null and (q.NextTryTime is null or q.NextTryTime < SYSUTCDATETIME()) " + " order by m.CreateDate asc)"; */ const string fetchSql = "select top 1 q.QueueId " + "from Messenger.Queues q with (updlock, readpast, rowlock) " + "join Messenger.Messages m (nolock) on m.QueueId = q.QueueId " + "where q.ProcessingStarted is null and m.StartDate <= SYSUTCDATETIME() and (q.NextTryTime is null or q.NextTryTime < SYSUTCDATETIME()) " + "order by m.StartDate asc"; const string updateSql = "update Messenger.Queues " + "set ProcessingStarted = SYSUTCDATETIME() " + ", ProcessName = @processName " + ", ProcessedAt = SYSUTCDATETIME() " + "output inserted.Name, inserted.QueueId, inserted.Retries " + "where QueueId = @queueId"; QueueInfo queueInfo = null; using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew)) { var queueId = db.Connection.Query<int?>(fetchSql).FirstOrDefault(); if (queueId.HasValue) { queueInfo = db.Connection.Query<QueueInfo>(updateSql, new {queueId, processName }).FirstOrDefault(); } //var queue = db.Connection.Query<QueueInfo>(sql, new {processName}).FirstOrDefault(); db.Complete(); } return queueInfo; }
public void CreateMessage(int queueId, string contentType, string content, string context, string contextFactory, int delayInSeconds, Guid identity) { const string sql = "INSERT INTO Messenger.Messages (QueueId, ContentType, Content, StartDate, Context, Identity) " + "VALUES(:QueueId, :ContentType, :Content, CURRENT_TIMESTAMP + INTERVAL '{0} second', :Context, :Identity, :ContextFactory)"; var message = new { QueueId = queueId, ContentType = contentType, Content = content, DelayInSeconds = delayInSeconds, Context = context, Identity = identity, ContextFactory = contextFactory }; using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.Required)) { db.Connection.Execute(string.Format(sql, delayInSeconds), message); db.Complete(); } }
public void RemoveMessage(int [] messageId, ITransaction transaction = null) { if (messageId == null || messageId.Length == 0) return; const string sql = "delete from Messenger.Messages " + "where MessageId in @messageId"; using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.Required, externalTransaction: transaction)) { db.Connection.Execute(sql, new { messageId }); db.Complete(); } }
public IEnumerable<ProcessStatus> GetActiveProcesses() { const string sql = "select ProcessName, LastBeat as LastHeartbeat from Messenger.Heartbeat"; using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew, IsolationLevel.ReadUncommitted)) { var statuses = db.Connection.Query<ProcessStatus>(sql); db.Complete(); return statuses; } }
public int CancelMessages(Guid identity) { const string sql1 = "delete from [Messenger].[Messages] where [Identity] = @identity and StartDate > SYSUTCDATETIME()"; const string sql2 = "delete from [Messenger].[FailedMessages] where [Identity] = @identity"; using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew, IsolationLevel.Serializable)) { var affectedRows = db.Connection.Execute(sql1, new { identity }); if (affectedRows == 0) affectedRows = db.Connection.Execute(sql2, new {identity}); db.Complete(); return affectedRows; } }
public void RemoveMessage(int messageId, bool archiveMessage, ITransaction transaction = null) { const string sql = "delete from [Messenger].[Messages] where MessageId = @messageId"; const string sqlArchive = "insert into [Messenger].[CompletedMessages] (ContentType, Content, CreateDate, Context, CompletedDate, [Identity], ContextFactory) " + "select ContentType, Content, StartDate, Context, SYSUTCDATETIME(), [Identity], ContextFactory " + "from [Messenger].[Messages] " + "where MessageId = @messageId"; using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.Required, externalTransaction: transaction)) { if (archiveMessage) { db.Connection.Execute(sqlArchive, new {messageId}); } db.Connection.Execute(sql, new {messageId}); db.Complete(); } }
public IList<MessageInfo> FetchQueueMessages(int queueId, int messageCount) { const string sql = "select top(@messageCount) MessageId, Content, Context, StartDate, [Identity], ContextFactory " + "from Messenger.Messages " + "where QueueId = @queueId and StartDate <= SYSUTCDATETIME() " + "order by StartDate asc, MessageId asc"; using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.Suppress)) { var messages = db.Connection.Query<MessageInfo>(sql, new { queueId, messageCount }).ToList(); db.Complete(); return messages; } }
public void SetQueueFailure(int queueId, int retries, string formattedError, DateTime nextTryTime) { const string sql = "update [Messenger].[Queues] " + "set ProcessingStarted = null " + ", ProcessName = null " + ", Error = @formattedError " + ", Retries = @retries " + ", NextTryTime = @nextTryTime " + "where QueueId = @queueId"; using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew)) { db.Connection.Execute(sql, new { queueId, retries, formattedError, nextTryTime }); db.Complete(); } }
public void ReleaseQueue(int queueId, ITransaction transaction = null) { const string sql = "update Messenger.Queues " + "set ProcessingStarted = null " + ", ProcessName = null " + ", Error = null " + ", Retries = null " + ", NextTryTime = null " + "where QueueId = @queueId"; using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.Required, externalTransaction: transaction)) { db.Connection.Execute(sql, new {queueId}); db.Complete(); } }
// ------------- QUEUE MANAGEMENT ----------------------- public IEnumerable<MessengerStatus> GetMessengerStatus() { const string sql = "select 'Failed' as Status, count(*) as MessageCount from Messenger.FailedMessages " + "union " + "select 'Completed', count(*) from Messenger.CompletedMessages " + "union " + "select 'Ready', count(*) from Messenger.Messages"; using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew, IsolationLevel.ReadUncommitted)) { var status = db.Connection.Query<MessengerStatus>(sql); db.Complete(); return status; } }
public IEnumerable<MessageDetails> GetFailedMessages(int pageNo, int pageSize) { const string sql = "select FailedMessageId as MessageId, QueueId, ContentType, Content, CreateDate as StartDate, Context, [Identity], Error, FailedDate as CompletedDate, 'Failed' as State, ContextFactory " + "from Messenger.FailedMessages " + "order by CreateDate asc " + "offset @offset rows " + "fetch next @pageSize rows only"; int offset = (pageNo - 1) * pageSize; using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew, IsolationLevel.ReadUncommitted)) { var statuses = db.Connection.Query<MessageDetails>(sql, new { offset, pageSize }); db.Complete(); return statuses; } }
public IEnumerable<QueueStatus> GetQueueStatuses() { const string sql = "select q.QueueId, q.Name, q.ProcessedAt, q.ProcessingStarted, q.ProcessName, q.Retries, q.NextTryTime, q.Error " + ", sum(case when m.MessageId is null then 0 else 1 end) as MessageCount " + "from Messenger.Queues q " + "left join Messenger.Messages m on m.QueueId = q.QueueId " + "group by q.QueueId, q.Name, q.ProcessedAt, q.ProcessingStarted, q.ProcessName, q.Retries, q.NextTryTime, q.Error"; using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew, IsolationLevel.ReadUncommitted)) { var statuses = db.Connection.Query<QueueStatus>(sql); db.Complete(); return statuses; } }
public void RemoveMessages(params Guid[] identity) { if (identity == null || identity.Length == 0) return; const string readySql = "delete from Messenger.Messages where [Identity] in @identity"; const string failedSql = "delete from Messenger.FailedMessages where [Identity] in @identity"; using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew, IsolationLevel.ReadUncommitted)) { db.Connection.Execute(readySql, new { identity }); db.Connection.Execute(failedSql, new { identity }); db.Complete(); } }
public void CopyMessageToFailed(int messageId, ITransaction transaction = null) { const string sql = "insert into Messenger.FailedMessages (ContentType, Content, CreateDate, Error, FailedDate, Context, [Identity], ContextFactory, QueueId) " + "select m.ContentType, m.Content, m.StartDate, q.Error, SYSUTCDATETIME(), m.Context, m.[Identity], m.ContextFactory, m.QueueId " + "from Messenger.Messages m " + "join Messenger.Queues q on q.QueueId = m.QueueId " + "where m.MessageId = @messageId "; using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.Required, externalTransaction: transaction)) { db.Connection.Execute(sql, new { messageId }); db.Complete(); } }
public void InsertMessage(int queueId, string contentType, string content, string contextFactory, string context, Guid identity) { const string selectSql = "SELECT [StartDate] " + "FROM [Messenger].[Messages] " + "WHERE [QueueId] = @queueId " + "ORDER BY [StartDate] ASC"; const string sql = "INSERT INTO [Messenger].[Messages] ([QueueId], [ContentType], [Content], [StartDate], [Context], [Identity], [ContextFactory]) " + "VALUES(@QueueId, @ContentType, @Content, @StartDate, @Context, @Identity, @ContextFactory)"; using (var db = new FatQueueDatabase(_connectionFactory)) { var createDate = db.Connection.ExecuteScalar<DateTime?>(selectSql, new {queueId}); var parameters = new DynamicParameters(); parameters.Add("QueueId", queueId); parameters.Add("ContentType", contentType); parameters.Add("Content", content); parameters.Add("StartDate", createDate.GetValueOrDefault(DateTime.UtcNow), DbType.DateTime2); parameters.Add("Context", context); parameters.Add("Identity", identity); parameters.Add("ContextFactory", contextFactory); db.Connection.Execute(sql, parameters); db.Complete(); } }
public MessageDetails GetMessageDetails(Guid identity) { const string sql = "select * from( " + " select MessageId, QueueId, ContentType, Content, StartDate, Context, [Identity], Error, null as CompletedDate, 'Ready' as State, ContextFactory from Messenger.Messages " + " union " + " select CompletedMessageId, null, ContentType, Content, CreateDate, Context, [Identity], null as Error, CompletedDate, 'Completed', ContextFactory from Messenger.CompletedMessages " + " union " + " select FailedMessageId, QueueId, ContentType, Content, CreateDate, Context, [Identity], Error, FailedDate, 'Failed', ContextFactory from Messenger.FailedMessages " + ") as msg " + "where [Identity] = @identity"; using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew, IsolationLevel.ReadUncommitted)) { var statuses = db.Connection.Query<MessageDetails>(sql, new { identity }); db.Complete(); return statuses.FirstOrDefault(); } }
public MessageDetails GetMessageDetails(Guid identity) { const string sql = "select * " + "from Messenger.Messages " + "where Identity = :identity "; using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew, IsolationLevel.ReadUncommitted)) { var statuses = db.Connection.Query<MessageDetails>(sql, new { identity }); db.Complete(); return statuses.FirstOrDefault(); } }
public void ReenqueueFailedMessages(int queueId, params Guid[] identity) { const string sql = "insert into Messenger.Messages (QueueId, ContentType, Content, StartDate, Context, [Identity], ContextFactory) " + "select ISNULL(QueueId, @queueId), ContentType, Content, SYSUTCDATETIME(), Context, [Identity], ContextFactory " + "from Messenger.FailedMessages " + "where [Identity] in @identity"; const string sqlDelete = "delete from Messenger.FailedMessages where [Identity] in @identity"; using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew)) { db.Connection.Execute(sql, new { queueId, identity }); db.Connection.Execute(sqlDelete, new { queueId, identity }); db.Complete(); } }
public IEnumerable<MessageDetails> GetFailedMessages(int pageNo, int pageSize) { const string sql = "select * " + "from Messenger.FailedMessages " + "order by CreateDate asc " + "limit :pageSize " + "offset :offset"; int offset = (pageNo - 1) * pageSize; using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.RequiresNew, IsolationLevel.ReadUncommitted)) { var statuses = db.Connection.Query<MessageDetails>(sql, new { offset, pageSize }); db.Complete(); return statuses; } }
public void CreateMessageIfNotExists(int queueId, string contentType, string content, string contextFactory, string context, int delayInSeconds, Guid identity) { const string sql = "INSERT INTO [Messenger].[Messages] ([QueueId], [ContentType], [Content], [StartDate], [Context], [Identity], [ContextFactory]) " + "SELECT @QueueId, @ContentType, @Content, DATEADD(second, @DelayInSeconds, SYSUTCDATETIME()), @Context, @Identity, @ContextFactory " + "WHERE NOT EXISTS (SELECT * FROM [Messenger].[Messages] WHERE [Identity] = @Identity)"; var message = new { QueueId = queueId, ContentType = contentType, Content = content, DelayInSeconds = delayInSeconds, Context = context, Identity = identity, ContextFactory = contextFactory }; using (var db = new FatQueueDatabase(_connectionFactory)) { db.Connection.Execute(sql, message); db.Complete(); } }
public void MoveMessageToEnd(int messageId, ITransaction transaction = null) { const string sql = "update Messenger.Messages " + "set StartDate = SYSUTCDATETIME() " + "where MessageId = @messageId"; using (var db = new FatQueueDatabase(_connectionFactory, TransactionScopeOption.Required, externalTransaction: transaction)) { db.Connection.Execute(sql, new { messageId }); db.Complete(); } }