public void Create_Meta_Priority()
{
var tableName = GetTableNameHelper();
var options = new PostgreSqlMessageQueueTransportOptions {
EnablePriority = true
};
var factory = Substitute.For <IPostgreSqlMessageQueueTransportOptionsFactory>();
factory.Create().Returns(options);
var test = Create(factory, tableName);
var tables = test.GetSchema().ConvertAll(o => (Table)o);
var statusTable = tables.Find(item => item.Name == tableName.MetaDataName);
Assert.Contains(statusTable.Columns.Items, item => item.Name == "Priority");
}
internal static void BuildStatusCommand(NpgsqlCommand command,
ITableNameHelper tableNameHelper,
IHeaders headers,
IAdditionalMessageData data,
IMessage message,
long id,
PostgreSqlMessageQueueTransportOptions options)
{
var builder = new StringBuilder();
builder.AppendLine("Insert into " + tableNameHelper.StatusName);
builder.Append("(QueueID, Status, CorrelationID ");
//add configurable columns - user
if (!options.AdditionalColumnsOnMetaData)
{
AddUserColumns(builder, data);
}
//close the column list
builder.AppendLine(") ");
//add standard values that are always present
builder.Append("VALUES (");
builder.Append($"@QueueID, {Convert.ToInt32(QueueStatuses.Waiting)}, @CorrelationID");
//add configurable column value - user
if (!options.AdditionalColumnsOnMetaData)
{
AddUserColumnsValues(builder, data);
}
builder.Append(")"); //close the VALUES
command.CommandText = builder.ToString();
options.AddBuiltInColumnsParams(command, data);
command.Parameters.Add("@QueueID", NpgsqlDbType.Bigint, 8).Value = id;
command.Parameters.Add("@CorrelationID", NpgsqlDbType.Uuid, 16).Value = data.CorrelationId.Id.Value;
//add configurable column command params - user
if (!options.AdditionalColumnsOnMetaData)
{
AddUserColumnsParams(command, data);
}
}
public void Create_Status_Extra_Columns()
{
var tableName = GetTableNameHelper();
var options = new PostgreSqlMessageQueueTransportOptions {
EnableStatusTable = true
};
options.AdditionalColumns.Add(new Column("testing", ColumnTypes.Bigint, true));
var factory = Substitute.For <IPostgreSqlMessageQueueTransportOptionsFactory>();
factory.Create().Returns(options);
var test = Create(factory, tableName);
var tables = test.GetSchema().ConvertAll(o => (Table)o);
var statusTable = tables.Find(item => item.Name == tableName.StatusName);
Assert.Contains(statusTable.Columns.Items, item => item.Name == "testing");
}
public void Create_FIFO()
{
var tableName = GetTableNameHelper();
var options = new PostgreSqlMessageQueueTransportOptions
{
EnableStatus = false,
EnableHeartBeat = false,
EnablePriority = false
};
var factory = Substitute.For <IPostgreSqlMessageQueueTransportOptionsFactory>();
factory.Create().Returns(options);
var test = Create(factory, tableName);
var tables = test.GetSchema();
Assert.NotNull(tables);
}
internal static void BuildMetaCommand(NpgsqlCommand command,
TableNameHelper tableNameHelper,
IHeaders headers,
IAdditionalMessageData data,
IMessage message,
long id,
PostgreSqlMessageQueueTransportOptions options,
TimeSpan?delay,
TimeSpan expiration,
DateTime currentDateTime)
{
var sbMeta = new StringBuilder();
sbMeta.AppendLine("Insert into " + tableNameHelper.MetaDataName);
sbMeta.Append("(QueueID, CorrelationID, QueuedDateTime ");
//add configurable columns - queue
options.AddBuiltInColumns(sbMeta);
AddHeaderColumns(sbMeta, message, headers);
//close the column list
sbMeta.AppendLine(") ");
//add standard values that are always present
sbMeta.Append("VALUES (");
sbMeta.Append("@QueueID, @CorrelationID, now() at time zone 'utc' ");
//add the values for built in fields
options.AddBuiltInColumnValues(delay, expiration, currentDateTime, sbMeta);
AddHeaderValues(sbMeta, message, headers);
sbMeta.Append(")"); //close the VALUES
command.CommandText = sbMeta.ToString();
options.AddBuiltInColumnsParams(command, data);
AddHeaderColumnParams(command, message, headers);
command.Parameters.Add("@QueueID", NpgsqlDbType.Bigint, 8).Value = id;
command.Parameters.Add("@CorrelationID", NpgsqlDbType.Uuid, 16).Value = data.CorrelationId.Id.Value;
}
internal static void BuildMetaCommand(NpgsqlCommand command,
TableNameHelper tableNameHelper,
IHeaders headers,
IAdditionalMessageData data,
IMessage message,
long id,
PostgreSqlMessageQueueTransportOptions options,
TimeSpan? delay,
TimeSpan expiration,
DateTime currentDateTime)
{
var sbMeta = new StringBuilder();
sbMeta.AppendLine("Insert into " + tableNameHelper.MetaDataName);
sbMeta.Append("(QueueID, CorrelationID, QueuedDateTime ");
//add configurable columns - queue
options.AddBuiltInColumns(sbMeta);
AddHeaderColumns(sbMeta, message, headers);
//close the column list
sbMeta.AppendLine(") ");
//add standard values that are always present
sbMeta.Append("VALUES (");
sbMeta.Append("@QueueID, @CorrelationID, now() at time zone 'utc' ");
//add the values for built in fields
options.AddBuiltInColumnValues(delay, expiration, currentDateTime, sbMeta);
AddHeaderValues(sbMeta, message, headers);
sbMeta.Append(")"); //close the VALUES
command.CommandText = sbMeta.ToString();
options.AddBuiltInColumnsParams(command, data);
AddHeaderColumnParams(command, message, headers);
command.Parameters.Add("@QueueID", NpgsqlDbType.Bigint, 8).Value = id;
command.Parameters.Add("@CorrelationID", NpgsqlDbType.Uuid, 16).Value = data.CorrelationId.Id.Value;
}
/// <summary>
/// Creates new instance.
/// </summary>
/// <returns></returns>
public PostgreSqlMessageQueueTransportOptions Create()
{
if (string.IsNullOrEmpty(_connectionInformation.ConnectionString))
{
return new PostgreSqlMessageQueueTransportOptions();
}
if (_options != null) return _options;
lock (_creator)
{
if (_options == null)
{
_options = _queryOptions.Handle(new GetQueueOptionsQuery());
}
if (_options == null) //does not exist in DB; return a new copy
{
_options = new PostgreSqlMessageQueueTransportOptions();
}
}
return _options;
}
internal static void BuildStatusCommand(NpgsqlCommand command,
TableNameHelper tableNameHelper,
IHeaders headers,
IAdditionalMessageData data,
IMessage message,
long id,
PostgreSqlMessageQueueTransportOptions options)
{
var builder = new StringBuilder();
builder.AppendLine("Insert into " + tableNameHelper.StatusName);
builder.Append("(QueueID, Status, CorrelationID ");
//add configurable columns - user
AddUserColumns(builder, data);
//close the column list
builder.AppendLine(") ");
//add standard values that are always present
builder.Append("VALUES (");
builder.Append($"@QueueID, {Convert.ToInt32(QueueStatuses.Waiting)}, @CorrelationID");
//add configurable column value - user
AddUserColumnsValues(builder, data);
builder.Append(")"); //close the VALUES
command.CommandText = builder.ToString();
options.AddBuiltInColumnsParams(command, data);
command.Parameters.Add("@QueueID", NpgsqlDbType.Bigint, 8).Value = id;
command.Parameters.Add("@CorrelationID", NpgsqlDbType.Uuid, 16).Value = data.CorrelationId.Id.Value;
//add configurable column command params - user
AddUserColumnsParams(command, data);
AddHeaderColumnParams(command, message, headers);
}
/// <inheritdoc />
public PostgreSqlMessageQueueTransportOptions Create()
{
if (string.IsNullOrEmpty(_connectionInformation.ConnectionString))
{
return(new PostgreSqlMessageQueueTransportOptions());
}
if (_options != null)
{
return(_options);
}
lock (_creator)
{
if (_options == null)
{
_options = _queryOptions.Handle(new GetQueueOptionsQuery <PostgreSqlMessageQueueTransportOptions>());
}
if (_options == null) //does not exist in DB; return a new copy
{
_options = new PostgreSqlMessageQueueTransportOptions();
}
}
return(_options);
}
public void Test_DefaultNotReadOnly()
{
var test = new PostgreSqlMessageQueueTransportOptions();
Assert.False(test.IsReadOnly);
}
public void Validation()
{
var test = new PostgreSqlMessageQueueTransportOptions();
test.ValidConfiguration();
}
public VerifyQueueRecordCount(string queueName, PostgreSqlMessageQueueTransportOptions options)
{
_options = options;
_connection = new SqlConnectionInformation(queueName, ConnectionInfo.ConnectionString);
_tableNameHelper = new TableNameHelper(_connection);
}
/// <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="forRpc">if set to <c>true</c> [for RPC].</param>
/// <param name="routes">The routes.</param>
/// <returns></returns>
public static string GetDeQueueCommand(PostgreSqlCommandStringCache commandCache, TableNameHelper tableNameHelper, PostgreSqlMessageQueueTransportOptions options, bool forRpc, List<string> routes )
{
if (routes == null || routes.Count == 0)
{
if (forRpc && commandCache.Contains(RpcdequeueKey))
{
return commandCache.Get(RpcdequeueKey);
}
if (commandCache.Contains(DequeueKey))
{
return commandCache.Get(DequeueKey);
}
}
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 (forRpc)
{
if (needWhere)
{
sb.AppendLine("where q.SourceQueueID = @QueueID");
needWhere = false;
}
else
{
sb.AppendLine("AND q.SourceQueueID = @QueueID");
}
}
if (options.EnableMessageExpiration || options.QueueType == QueueTypes.RpcReceive || options.QueueType == QueueTypes.RpcSend)
{
if (needWhere)
{
sb.AppendLine("Where q.ExpirationTime > @CurrentDate ");
needWhere = false;
}
else
{
sb.AppendLine("AND q.ExpirationTime > @CurrentDate ");
}
needWhere = false;
}
if (options.EnableRoute && routes != null && routes.Count > 0)
{
if (needWhere)
{
sb.AppendLine("where Route IN ( ");
needWhere = false;
}
else
{
sb.AppendLine("AND Route IN ( ");
}
var routeCounter = 1;
foreach (var route in routes)
{
sb.Append("@Route" + routeCounter.ToString());
routeCounter++;
if (routeCounter != routes.Count + 1)
{
sb.Append(", ");
}
}
sb.Append(") ");
}
//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)
{ //TODO - cache based on route
return sb.ToString();
}
else
{
return commandCache.Add(forRpc ? RpcdequeueKey : DequeueKey, sb.ToString());
}
}
/// <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="forRpc">if set to <c>true</c> [for RPC].</param>
/// <param name="routes">The routes.</param>
/// <returns></returns>
public static string GetDeQueueCommand(PostgreSqlCommandStringCache commandCache, TableNameHelper tableNameHelper, PostgreSqlMessageQueueTransportOptions options, bool forRpc, List <string> routes)
{
if (routes == null || routes.Count == 0)
{
if (forRpc && commandCache.Contains(RpcdequeueKey))
{
return(commandCache.Get(RpcdequeueKey).CommandText);
}
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 (forRpc)
{
if (needWhere)
{
sb.AppendLine("where q.SourceQueueID = @QueueID");
needWhere = false;
}
else
{
sb.AppendLine("AND q.SourceQueueID = @QueueID");
}
}
if (options.EnableMessageExpiration || options.QueueType == QueueTypes.RpcReceive || options.QueueType == QueueTypes.RpcSend)
{
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(") ");
}
//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)
{ //TODO - cache based on route
return(sb.ToString());
}
return(commandCache.Add(forRpc ? RpcdequeueKey : DequeueKey, sb.ToString()));
}
/// <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()));
}
