/// <summary>
/// Returns the next message, if any.
/// </summary>
/// <param name="context">The context.</param>
/// <returns>
/// A message if one is found; null otherwise
/// </returns>
public IReceivedMessageInternal GetMessage(IMessageContext context)
{
//if stopping, exit now
if (_cancelToken.Tokens.Any(t => t.IsCancellationRequested))
{
return(null);
}
//ask for the next message
var receivedTransportMessage =
_receiveMessage.Handle(new ReceiveMessageQuery <IDbConnection, IDbTransaction>(null, null, _configuration.Routes, _configuration.GetUserParameters(), _configuration.GetUserClause()));
//if no message (null) run the no message action and return
if (receivedTransportMessage == null)
{
return(null);
}
//set the message ID on the context for later usage
context.SetMessageAndHeaders(receivedTransportMessage.MessageId, receivedTransportMessage.Headers);
return(receivedTransportMessage);
}
/// <summary>Gets the de queue command.</summary>
/// <param name="userParams">The optional user de-queue params</param>
/// <param name="routes">The routes.</param>
/// <returns>
/// <br />
/// </returns>
public string GetDeQueueCommand(out List <SqlParameter> userParams, List <string> routes = null)
{
userParams = null;
var userQuery = _configuration.GetUserClause();
if ((routes == null || routes.Count == 0) && string.IsNullOrEmpty(userQuery))
{
if (_commandCache.Contains(DequeueKey))
{
return(_commandCache.Get(DequeueKey).CommandText);
}
}
var sb = new StringBuilder();
//NOTE - this could be optimized a little bit. We are always using a CTE, but that's not necessary if the queue is
//setup as a pure FIFO queue.
sb.AppendLine("declare @Queue1 table ");
sb.AppendLine("( ");
sb.AppendLine("QueueID bigint, ");
sb.AppendLine("CorrelationID uniqueidentifier ");
sb.AppendLine("); ");
sb.AppendLine("with cte as ( ");
sb.AppendLine("select top(1) ");
sb.AppendLine(_tableNameHelper.MetaDataName + ".QueueID, CorrelationID ");
if (_options.Value.EnableStatus)
{
sb.Append(", [status] ");
}
if (_options.Value.EnableHeartBeat)
{
sb.Append(", HeartBeat ");
}
sb.AppendLine($"from {_tableNameHelper.MetaDataName} with (updlock, readpast, rowlock) ");
//calculate where clause...
var needWhere = true;
if (_options.Value.EnableStatus && _options.Value.EnableDelayedProcessing)
{
sb.AppendFormat(" WHERE [Status] = {0} ", Convert.ToInt16(QueueStatuses.Waiting));
sb.AppendLine("and QueueProcessTime < getutcdate() ");
needWhere = false;
}
else if (_options.Value.EnableStatus)
{
sb.AppendFormat("WHERE [Status] = {0} ", Convert.ToInt16(QueueStatuses.Waiting));
needWhere = false;
}
else if (_options.Value.EnableDelayedProcessing)
{
sb.AppendLine("WHERE (QueueProcessTime < getutcdate()) ");
needWhere = false;
}
if (_options.Value.EnableRoute && routes != null && routes.Count > 0)
{
if (needWhere)
{
sb.AppendLine("where Route IN ( ");
needWhere = false;
}
else
{
sb.AppendLine("AND Route IN ( ");
}
for (var i = 1; i - 1 < routes.Count; i++)
{
sb.Append("@Route" + i);
if (i != routes.Count)
{
sb.Append(", ");
}
}
sb.Append(") ");
}
if (_options.Value.EnableMessageExpiration)
{
sb.AppendLine(needWhere
? "where ExpirationTime > getutcdate() "
: "AND ExpirationTime > getutcdate() ");
}
//if true, the query can be added to via user settings
if (_options.Value.AdditionalColumnsOnMetaData && !string.IsNullOrEmpty(userQuery))
{
userParams = _configuration.GetUserParameters(); //NOTE - could be null
sb.AppendLine(needWhere
? $"where {userQuery} "
: $"AND {userQuery} ");
}
//determine order by looking at the options
var bNeedComma = false;
sb.Append(" Order by ");
if (_options.Value.EnableStatus)
{
sb.Append(" [status] asc ");
bNeedComma = true;
}
if (_options.Value.EnablePriority)
{
if (bNeedComma)
{
sb.Append(", ");
}
sb.Append(" [priority] asc ");
bNeedComma = true;
}
if (_options.Value.EnableDelayedProcessing)
{
if (bNeedComma)
{
sb.Append(", ");
}
sb.AppendLine(" [QueueProcessTime] asc ");
bNeedComma = true;
}
if (_options.Value.EnableMessageExpiration)
{
if (bNeedComma)
{
sb.Append(", ");
}
sb.AppendLine(" [ExpirationTime] asc ");
bNeedComma = true;
}
if (bNeedComma)
{
sb.Append(", ");
}
sb.AppendLine(" [QueueID] asc ) ");
//determine if performing update or delete...
if (_options.Value.EnableStatus && !_options.Value.EnableHoldTransactionUntilMessageCommitted)
{ //update
sb.AppendFormat("update cte set status = {0} ", Convert.ToInt16(QueueStatuses.Processing));
if (_options.Value.EnableHeartBeat)
{
sb.AppendLine(", HeartBeat = GetUTCDate() ");
}
sb.AppendLine("output inserted.QueueID, inserted.CorrelationID into @Queue1 ");
}
else if (_options.Value.EnableHoldTransactionUntilMessageCommitted)
{
sb.AppendLine("update cte set queueid = QueueID ");
sb.AppendLine("output inserted.QueueID, inserted.CorrelationID into @Queue1 ");
}
else
{ //delete - note even if heartbeat is enabled, there is no point in setting it
//a delete here if not using transactions will actually remove the record from the queue
//it's up to the caller to handle error conditions in this case.
sb.AppendLine("delete from cte ");
sb.AppendLine("output deleted.QueueID, deleted.CorrelationID into @Queue1 ");
}
//grab the rest of the data - this is all standard
sb.AppendLine("select q.queueid, qm.body, qm.Headers, q.CorrelationID from @Queue1 q ");
sb.AppendLine($"INNER JOIN {_tableNameHelper.QueueName} qm with (nolock) "); //a dirty read on the data here should be ok, since we have exclusive access to the queue record on the meta data table
sb.AppendLine("ON q.QueueID = qm.QueueID ");
//if we are holding transactions, we can't update the status table as part of this query - has to be done after de-queue instead
if (_options.Value.EnableStatusTable && !_options.Value.EnableHoldTransactionUntilMessageCommitted)
{
sb.AppendFormat("update {0} set status = {1} where {0}.QueueID = (select q.queueid from @Queue1 q)", _tableNameHelper.StatusName, Convert.ToInt16(QueueStatuses.Processing));
}
if ((routes != null && routes.Count > 0) || !string.IsNullOrEmpty(userQuery))
{ //TODO - cache based on route
return(sb.ToString());
}
return(_commandCache.Add(DequeueKey, sb.ToString()));
}
/// <summary>
/// Returns the next message, if any.
/// </summary>
/// <param name="context">The context.</param>
/// <param name="connectionHolder">The connection.</param>
/// <param name="noMessageFoundActon">The no message found action.</param>
/// <returns>
/// A message if one is found; null otherwise
/// </returns>
public IReceivedMessageInternal GetMessage(IMessageContext context, IConnectionHolder <SqlConnection, SqlTransaction, SqlCommand> connectionHolder,
Action <IConnectionHolder <SqlConnection, SqlTransaction, SqlCommand> > noMessageFoundActon)
{
//if stopping, exit now
if (_cancelToken.Tokens.Any(t => t.IsCancellationRequested))
{
noMessageFoundActon(connectionHolder);
return(null);
}
//ask for the next message
var receivedTransportMessage =
_receiveMessage.Handle(new ReceiveMessageQuery <SqlConnection, SqlTransaction>(connectionHolder.Connection,
connectionHolder.Transaction, _configuration.Routes, _configuration.GetUserParameters(), _configuration.GetUserClause()));
//if no message (null) run the no message action and return
if (receivedTransportMessage == null)
{
noMessageFoundActon(connectionHolder);
return(null);
}
//set the message ID on the context for later usage
context.SetMessageAndHeaders(receivedTransportMessage.MessageId, receivedTransportMessage.Headers);
//if we are holding open transactions, we need to update the status table in a separate call
//When not using held transactions, this is part of the de-queue statement and so not needed here
//TODO - we could consider using a task to update the status table
//the status table drives nothing internally, however it may drive external processes
//because of that, we are not returning the message until the status table is updated.
//we could make this a configurable option in the future?
if (_configuration.Options().EnableHoldTransactionUntilMessageCommitted&&
_configuration.Options().EnableStatusTable)
{
_setStatusCommandHandler.Handle(
new SetStatusTableStatusCommand <long>(
(long)receivedTransportMessage.MessageId.Id.Value, QueueStatuses.Processing));
}
return(receivedTransportMessage);
}
/// <inheritdoc />
public IReceivedMessageInternal ReceiveMessage(IMessageContext context)
{
if (_configuration.Options().EnableHoldTransactionUntilMessageCommitted)
{
_commitConnection = (c, b) => _handleMessage.CommitMessage.Commit(context);
_rollbackConnection = (c, b) => _handleMessage.RollbackMessage.Rollback(context);
}
try
{
if (_cancelWork.Tokens.Any(m => m.IsCancellationRequested))
{
return(null);
}
var connection = GetConnectionAndSetOnContext(context);
try
{
return(_receiveMessages.GetMessage(context, connection, connection1 => _disposeConnection(connection), _configuration.Routes, _configuration.GetUserParameters(), _configuration.GetUserClause()));
}
finally
{
if (!_configuration.Options().EnableHoldTransactionUntilMessageCommitted)
{
_disposeConnection(connection);
}
}
}
catch (PoisonMessageException exception)
{
if (exception.MessageId != null && exception.MessageId.HasValue)
{
context.SetMessageAndHeaders(exception.MessageId, context.Headers);
}
throw;
}
catch (Exception exception)
{
throw new ReceiveMessageException("An error occurred while attempting to read messages from the queue",
exception);
}
}
/// <summary>Gets the de queue command.</summary>
/// <param name="metaTableName">Name of the meta table.</param>
/// <param name="queueTableName">Name of the queue table.</param>
/// <param name="statusTableName">Name of the status table.</param>
/// <param name="options">The options.</param>
/// <param name="configuration">Configuration module</param>
/// <param name="routes">The routes.</param>
/// <param name="userParams">Optional user params for de-queue</param>
/// <returns>
/// <br />
/// </returns>
public static CommandString GetDeQueueCommand(string metaTableName,
string queueTableName,
string statusTableName,
SqLiteMessageQueueTransportOptions options,
QueueConsumerConfiguration configuration,
List <string> routes, out List <SQLiteParameter> userParams)
{
userParams = null;
var sb = new StringBuilder();
var tempName = GenerateTempTableName();
sb.AppendLine($"CREATE TEMP TABLE {tempName}(QueueID Integer PRIMARY KEY, CurrentDateTime Integer);");
sb.AppendLine($"Insert into {tempName} (QueueID, CurrentDateTime)");
sb.AppendLine("select ");
sb.AppendLine(metaTableName + ".QueueID, ");
sb.AppendLine("@CurrentDateTime");
sb.AppendLine($"from {metaTableName} ");
//calculate where clause...
var needWhere = true;
if (options.EnableStatus && options.EnableDelayedProcessing)
{
sb.Append($" WHERE {metaTableName}.Status = {Convert.ToInt16(QueueStatuses.Waiting)} ");
sb.AppendLine("and QueueProcessTime < @CurrentDateTime ");
needWhere = false;
}
else if (options.EnableStatus)
{
sb.Append($" WHERE {metaTableName}.Status = {Convert.ToInt16(QueueStatuses.Waiting)} ");
needWhere = false;
}
else if (options.EnableDelayedProcessing)
{
sb.AppendLine("WHERE (QueueProcessTime < @CurrentDateTime) ");
needWhere = false;
}
if (options.EnableMessageExpiration)
{
if (needWhere)
{
sb.AppendLine("where ExpirationTime > @CurrentDateTime ");
needWhere = false;
}
else
{
sb.AppendLine("AND ExpirationTime > @CurrentDateTime ");
}
}
if (options.EnableRoute && routes != null && routes.Count > 0)
{
sb.AppendLine(needWhere ? "where Route IN ( " : "AND Route IN ( ");
for (var i = 1; i - 1 < routes.Count; i++)
{
sb.Append("@Route" + i);
if (i != routes.Count)
{
sb.Append(", ");
}
}
sb.Append(") ");
}
//if true, the query can be added to via user settings
var userQuery = configuration.GetUserClause();
if (options.AdditionalColumnsOnMetaData && !string.IsNullOrEmpty(userQuery))
{
userParams = configuration.GetUserParameters(); //NOTE - could be null
sb.AppendLine(needWhere
? $"where {userQuery} "
: $"AND {userQuery} ");
}
//determine order by looking at the options
var bNeedComma = false;
sb.Append(" Order by ");
if (options.EnableStatus)
{
sb.Append(" status asc ");
bNeedComma = true;
}
if (options.EnablePriority)
{
if (bNeedComma)
{
sb.Append(", ");
}
sb.Append(" priority asc ");
bNeedComma = true;
}
if (options.EnableDelayedProcessing)
{
if (bNeedComma)
{
sb.Append(", ");
}
sb.AppendLine(" QueueProcessTime asc ");
bNeedComma = true;
}
if (options.EnableMessageExpiration)
{
if (bNeedComma)
{
sb.Append(", ");
}
sb.AppendLine(" ExpirationTime asc ");
bNeedComma = true;
}
if (bNeedComma)
{
sb.Append(", ");
}
sb.AppendLine($" {metaTableName}.QueueID asc ");
sb.AppendLine(" LIMIT 1;");
//----------------------------
sb.AppendLine("");
sb.AppendLine("select ");
sb.AppendLine($"{tempName}.QueueID, {metaTableName}.CorrelationID, {queueTableName}.Body, {queueTableName}.Headers ");
if (options.EnableStatus)
{
sb.Append($", {metaTableName}.status ");
}
if (options.EnableHeartBeat)
{
sb.Append($", {metaTableName}.HeartBeat ");
}
sb.AppendLine($"from {tempName} ");
sb.AppendLine($"JOIN {metaTableName} ");
sb.AppendLine($"ON {metaTableName}.QueueID = {tempName}.QueueID ");
sb.AppendLine($"JOIN {queueTableName} ");
sb.AppendLine($"ON {metaTableName}.QueueID = {queueTableName}.QueueID; ");
sb.AppendLine("");
var additionalCommands = new List <string>();
//determine if performing update or delete...
var status = new StringBuilder();
if (options.EnableStatus)
{ //update
status.Append($"update {metaTableName} set status = {Convert.ToInt16(QueueStatuses.Processing)} ");
if (options.EnableHeartBeat)
{
status.AppendLine($", HeartBeat = (select {tempName}.CurrentDateTime from {tempName} LIMIT 1) ");
}
status.Append($" where {metaTableName}.QueueID = (select {tempName}.QueueID from {tempName} LIMIT 1);");
}
else
{ //delete - note even if heartbeat is enabled, there is no point in setting it
//a delete here if not using transactions will actually remove the record from the queue
//it's up to the caller to handle error conditions in this case.
status.AppendLine($"delete from {metaTableName} where {metaTableName}.QueueID = (select {tempName}.QueueID from {tempName} LIMIT 1); ");
}
additionalCommands.Add(status.ToString());
if (options.EnableStatusTable)
{
additionalCommands.Add($" update {statusTableName} set status = {Convert.ToInt16(QueueStatuses.Processing)} where {statusTableName}.QueueID = (select {tempName}.QueueID from {tempName} LIMIT 1);");
}
//will drop when the connection closes
//additionalCommands.Add($"drop table {tempName};");
return(new CommandString(sb.ToString(), additionalCommands));
}
/// <summary>Gets the de queue command.</summary>
/// <param name="commandCache">The command cache.</param>
/// <param name="tableNameHelper">The table name helper.</param>
/// <param name="options">The options.</param>
/// <param name="configuration">Queue Configuration</param>
/// <param name="routes">The routes.</param>
/// <param name="userParams">An optional collection of user params to pass to the query</param>
/// <returns>
/// <br />
/// </returns>
public static string GetDeQueueCommand(PostgreSqlCommandStringCache commandCache, ITableNameHelper tableNameHelper, PostgreSqlMessageQueueTransportOptions options, QueueConsumerConfiguration configuration, List <string> routes, out List <Npgsql.NpgsqlParameter> userParams)
{
userParams = null;
var userQuery = configuration.GetUserClause();
if ((routes == null || routes.Count == 0) && string.IsNullOrEmpty(userQuery))
{
if (commandCache.Contains(DequeueKey))
{
return(commandCache.Get(DequeueKey).CommandText);
}
}
var sb = new StringBuilder();
var needWhere = true;
if (options.EnableStatus)
{
sb.AppendLine($"update {tableNameHelper.MetaDataName} q");
sb.AppendLine($"set status = {Convert.ToInt16(QueueStatuses.Processing)}");
if (options.EnableHeartBeat)
{
sb.AppendLine(", HeartBeat = @CurrentDate");
}
sb.AppendLine($"from {tableNameHelper.QueueName} qm");
}
else
{
sb.AppendLine($"delete from {tableNameHelper.MetaDataName} q ");
sb.AppendLine($"using {tableNameHelper.QueueName} qm ");
}
sb.AppendLine(" where q.QueueID in (");
sb.AppendLine($"select q.QueueID from {tableNameHelper.MetaDataName} q");
//calculate where clause...
if (options.EnableStatus && options.EnableDelayedProcessing)
{
sb.AppendFormat(" WHERE q.Status = {0} ", Convert.ToInt16(QueueStatuses.Waiting));
sb.AppendLine("and q.QueueProcessTime < @CurrentDate ");
needWhere = false;
}
else if (options.EnableStatus)
{
sb.AppendFormat("WHERE q.Status = {0} ", Convert.ToInt16(QueueStatuses.Waiting));
needWhere = false;
}
else if (options.EnableDelayedProcessing)
{
sb.AppendLine("WHERE (q.QueueProcessTime < @CurrentDate) ");
needWhere = false;
}
if (options.EnableMessageExpiration)
{
if (needWhere)
{
sb.AppendLine("Where q.ExpirationTime > @CurrentDate ");
needWhere = false;
}
else
{
sb.AppendLine("AND q.ExpirationTime > @CurrentDate ");
}
}
if (options.EnableRoute && routes != null && routes.Count > 0)
{
sb.AppendLine(needWhere ? "where Route IN ( " : "AND Route IN ( ");
for (var i = 1; i - 1 < routes.Count; i++)
{
sb.Append("@Route" + i);
if (i != routes.Count)
{
sb.Append(", ");
}
}
sb.Append(") ");
}
//if true, the query can be added to via user settings
if (options.AdditionalColumnsOnMetaData && !string.IsNullOrEmpty(userQuery))
{
userParams = configuration.GetUserParameters(); //NOTE - could be null
sb.AppendLine(needWhere
? $"where {userQuery} "
: $"AND {userQuery} ");
}
//determine order by looking at the options
var bNeedComma = false;
sb.Append(" Order by ");
if (options.EnableStatus)
{
sb.Append(" q.status asc ");
bNeedComma = true;
}
if (options.EnablePriority)
{
if (bNeedComma)
{
sb.Append(", ");
}
sb.Append(" q.priority asc ");
bNeedComma = true;
}
if (options.EnableDelayedProcessing)
{
if (bNeedComma)
{
sb.Append(", ");
}
sb.AppendLine(" q.QueueProcessTime asc ");
bNeedComma = true;
}
if (options.EnableMessageExpiration)
{
if (bNeedComma)
{
sb.Append(", ");
}
sb.AppendLine(" q.ExpirationTime asc ");
bNeedComma = true;
}
if (bNeedComma)
{
sb.Append(", ");
}
sb.AppendLine(" q.QueueID asc limit 1 FOR UPDATE SKIP LOCKED) ");
sb.AppendLine(" AND q.QueueID = qm.QueueID");
sb.AppendLine("returning q.queueid, qm.body, qm.Headers, q.CorrelationID");
if ((routes != null && routes.Count > 0) || !string.IsNullOrEmpty(userQuery))
{ //TODO - cache based on route
return(sb.ToString());
}
return(commandCache.Add(DequeueKey, sb.ToString()));
}