public VerifyQueueData(QueueConnection queueConnection, LiteDbMessageQueueTransportOptions options, ICreationScope scope)
{
_options = options;
_connection = new LiteDbConnectionInformation(queueConnection);
_tableNameHelper = new TableNameHelper(_connection);
_scope = scope;
}
/// <summary>
/// The get most visited clusters for the company self, not include competitor.
/// </summary>
/// <param name="start">The start.</param>
/// <param name="end">The end.</param>
/// <param name="count">The count.</param>
/// <returns>IList<System.String>.</returns>
public IList <string> GetMostVisitedClustersSelf(DateTime start, DateTime end, int count)
{
var ids = new List <string>();
using (var context = this.GetContext())
{
var filter = this.currentClientUser.UserFilter;
var pattern = this.indexHelper.BuildPatternFromFilter(filter);
for (var s = start; s <= end; s = s.AddDays(1))
{
var tableName = TableNameHelper.GetNewsStreamTableName(this.currentClientUser.GetProfile().Postfix);
try
{
var cluster = context.Database.SqlQuery <string>(
$@"select ClusterId0 from {tableName}
where Contains(keywords, {{0}})", pattern).ToList();
}
catch (Exception e) { continue; }
var clusters = context.Database.SqlQuery <string>(
$@"select ClusterId0 from {tableName}
where Contains(keywords, {{0}})", pattern).ToList();
var idList = clusters.ToList();
var items =
from vi in context.HostVisitCount
where vi.Date == s && idList.Contains(vi.ClusterId0)
group vi by vi.ClusterId0 into z
select new { id = z.Key, val = z.Sum(d => d.Count) };
var list = items.OrderByDescending(i => i.val).Take(count).ToList();
ids.AddRange(list.Where(id => id != null).Select(x => x.id));
}
return(ids.Distinct().ToList());
}
}
public VerifyQueueRecordCount(string queueName, string connectionString, LiteDbMessageQueueTransportOptions options, ICreationScope scope)
{
_options = options;
_connection = new LiteDbConnectionInformation(new QueueConnection(queueName, connectionString));
_tableNameHelper = new TableNameHelper(_connection);
_scope = scope;
}
public static void SetError(QueueConnection queueConnection, ICreationScope scope)
{
var connection = new LiteDbConnectionInformation(queueConnection);
var helper = new TableNameHelper(connection);
var connScope = scope.GetDisposable <LiteDbConnectionManager>();
if (connScope == null)
{
using (var conn = new LiteDatabase(connection.ConnectionString))
{
var col = conn.GetCollection <Schema.StatusTable>(helper.StatusName);
var results = col.Query()
.ToList();
foreach (var result in results)
{
result.Status = QueueStatuses.Error;
col.Update(result);
}
}
}
else
{
using (var conn = connScope.GetDatabase())
{
var col = conn.Database.GetCollection <Schema.StatusTable>(helper.StatusName);
var results = col.Query()
.ToList();
foreach (var result in results)
{
result.Status = QueueStatuses.Error;
col.Update(result);
}
}
}
}
/// <summary>
/// Initializes a new instance of the <see cref="SendMessageCommandHandlerAsync"/> class.
/// </summary>
/// <param name="connectionInformation">The connection information.</param>
/// <param name="tableNameHelper">The table name helper.</param>
/// <param name="serializer">The serializer.</param>
/// <param name="optionsFactory">The options factory.</param>
/// <param name="headers">The headers.</param>
/// <param name="configurationSend">The configuration send.</param>
/// <param name="sendJobStatus">The send job status.</param>
/// <param name="jobExistsHandler">The job exists handler.</param>
/// <param name="jobSchedulerMetaData">The job scheduler meta data.</param>
/// <param name="databaseExists">The database exists.</param>
public SendMessageCommandHandlerAsync(
LiteDbConnectionManager connectionInformation,
TableNameHelper tableNameHelper,
ICompositeSerialization serializer,
ILiteDbMessageQueueTransportOptionsFactory optionsFactory,
IHeaders headers,
TransportConfigurationSend configurationSend,
ICommandHandler <SetJobLastKnownEventCommand> sendJobStatus, IQueryHandler <DoesJobExistQuery, QueueStatuses> jobExistsHandler,
IJobSchedulerMetaData jobSchedulerMetaData,
DatabaseExists databaseExists)
{
Guard.NotNull(() => connectionInformation, connectionInformation);
Guard.NotNull(() => tableNameHelper, tableNameHelper);
Guard.NotNull(() => serializer, serializer);
Guard.NotNull(() => optionsFactory, optionsFactory);
Guard.NotNull(() => headers, headers);
Guard.NotNull(() => configurationSend, configurationSend);
Guard.NotNull(() => sendJobStatus, sendJobStatus);
Guard.NotNull(() => jobExistsHandler, jobExistsHandler);
Guard.NotNull(() => jobSchedulerMetaData, jobSchedulerMetaData);
Guard.NotNull(() => databaseExists, databaseExists);
_connectionInformation = connectionInformation;
_tableNameHelper = tableNameHelper;
_serializer = serializer;
_options = new Lazy <LiteDbMessageQueueTransportOptions>(optionsFactory.Create);
_headers = headers;
_configurationSend = configurationSend;
_sendJobStatus = sendJobStatus;
_jobExistsHandler = jobExistsHandler;
_jobSchedulerMetaData = jobSchedulerMetaData;
_databaseExists = databaseExists;
}
/// <summary>
/// Initializes a new instance of the <see cref="SendMessageCommandHandler" /> class.
/// </summary>
/// <param name="tableNameHelper">The table name helper.</param>
/// <param name="serializer">The serializer.</param>
/// <param name="optionsFactory">The options factory.</param>
/// <param name="headers">The headers.</param>
/// <param name="commandCache">The command cache.</param>
/// <param name="configurationSend">The configuration send.</param>
/// <param name="sendJobStatus">The send job status.</param>
/// <param name="jobExistsHandler">The job exists handler.</param>
/// <param name="jobSchedulerMetaData">The job scheduler meta data.</param>
public SendMessageCommandHandler(TableNameHelper tableNameHelper,
ICompositeSerialization serializer,
ISqlServerMessageQueueTransportOptionsFactory optionsFactory,
IHeaders headers,
SqlServerCommandStringCache commandCache,
TransportConfigurationSend configurationSend,
ICommandHandler <SetJobLastKnownEventCommand <SqlConnection, SqlTransaction> > sendJobStatus,
IQueryHandler <DoesJobExistQuery <SqlConnection, SqlTransaction>, QueueStatuses> jobExistsHandler,
IJobSchedulerMetaData jobSchedulerMetaData)
{
Guard.NotNull(() => tableNameHelper, tableNameHelper);
Guard.NotNull(() => serializer, serializer);
Guard.NotNull(() => optionsFactory, optionsFactory);
Guard.NotNull(() => headers, headers);
Guard.NotNull(() => commandCache, commandCache);
Guard.NotNull(() => configurationSend, configurationSend);
Guard.NotNull(() => sendJobStatus, sendJobStatus);
Guard.NotNull(() => jobExistsHandler, jobExistsHandler);
Guard.NotNull(() => jobSchedulerMetaData, jobSchedulerMetaData);
_tableNameHelper = tableNameHelper;
_serializer = serializer;
_options = new Lazy <SqlServerMessageQueueTransportOptions>(optionsFactory.Create);
_headers = headers;
_commandCache = commandCache;
_configurationSend = configurationSend;
_sendJobStatus = sendJobStatus;
_jobExistsHandler = jobExistsHandler;
_jobSchedulerMetaData = jobSchedulerMetaData;
}
public void GetTableName_OnExecute_IsCorreclyResolvedWhenTableNameAttributeIsSet()
{
var testDatabaseObjectRepository = new TableNameHelper <TestDatabaseObject>();
var result = testDatabaseObjectRepository.GetTableName();
Assert.AreEqual("Test", result);
}
/// <summary>
/// Gets the news stream.
/// </summary>
/// <param name="filterWords">The filter words.</param>
/// <param name="number">The number.</param>
/// <param name="userName">Name of the user.</param>
/// <returns>IEnumerable<NewsStream>.</returns>
public IEnumerable <NewsStream> GetNewsStream(List <string> filterWords, int number, ClientUser user)
{
var hourlyNewsTableName = this.GetNewsHourlyTableName();
var wordCloudTableName = TableNameHelper.GetWordCloudTableName(user.Postfix);
var sb = new StringBuilder();
if (filterWords != null && filterWords.Count > 0)
{
foreach (var keyWord in filterWords)
{
sb.Append($"KeyWords like N'%{keyWord}%'");
sb.Append(" and ");
}
sb.Remove(sb.Length - 4, 4);
}
var word = filterWords.Count > 0 ? filterWords.First() : string.Empty;
string sql =
$"select top {number} * from {hourlyNewsTableName} where Date >= dateadd(Day, -1, (select max(Date) from {wordCloudTableName} where Word=N'{word}')) ";
if (sb.Length > 0)
{
sql += $" and {sb}";
}
return(this.dbUtilities.ExecuteStoreQuery <NewsStream>(this.Context, sql));
}
/// <summary>
/// Clears the user tables.
/// </summary>
/// <param name="postfix">The postfix.</param>
public void ClearUserTables(string postfix)
{
var tableList = new List <string>
{
TableNameHelper.GetHostVisitCountTableName(postfix),
TableNameHelper.GetHostVisitCountHourlyTableName(postfix),
TableNameHelper.GetLocationAndUserDemoTableName(postfix),
TableNameHelper.GetNewsHourlyTableName(postfix),
TableNameHelper.GetNewsSentimentTableName(postfix),
TableNameHelper.GetNewsStreamTableName(postfix),
TableNameHelper.GetSentimentResultNewsTableName(postfix),
TableNameHelper.GetSentimentResultTableName(postfix),
TableNameHelper.GetWordCloudTableName(postfix)
};
using (var db = ContextFactory.GetProfileContext())
{
var sb = new StringBuilder();
foreach (var table in tableList)
{
sb.AppendLine($"Truncate Table {table};");
}
db.Database.CommandTimeout = 300;
try
{
db.Database.ExecuteSqlCommand(sb.ToString());
}catch (Exception e)
{
}
}
repository.CleanUserDataLoadHistory(postfix);
}
/// <summary>
/// Initializes a new instance of the <see cref="SendMessageCommandHandler" /> class.
/// </summary>
/// <param name="tableNameHelper">The table name helper.</param>
/// <param name="serializer">The serializer.</param>
/// <param name="optionsFactory">The options factory.</param>
/// <param name="headers">The headers.</param>
/// <param name="commandCache">The command cache.</param>
/// <param name="configurationSend">The configuration send.</param>
/// <param name="sendJobStatus">The send job status.</param>
/// <param name="jobExistsHandler">The job exists handler.</param>
/// <param name="jobSchedulerMetaData">The job scheduler meta data.</param>
/// <param name="getTimeFactory">The get time factory.</param>
public SendMessageCommandHandlerAsync(TableNameHelper tableNameHelper,
ICompositeSerialization serializer,
IPostgreSqlMessageQueueTransportOptionsFactory optionsFactory,
IHeaders headers,
PostgreSqlCommandStringCache commandCache,
TransportConfigurationSend configurationSend,
ICommandHandler <SetJobLastKnownEventCommand <NpgsqlConnection, NpgsqlTransaction> > sendJobStatus, RelationalDatabase.IQueryHandler <DoesJobExistQuery <NpgsqlConnection, NpgsqlTransaction>, QueueStatuses> jobExistsHandler,
IJobSchedulerMetaData jobSchedulerMetaData,
IGetTimeFactory getTimeFactory)
{
Guard.NotNull(() => tableNameHelper, tableNameHelper);
Guard.NotNull(() => serializer, serializer);
Guard.NotNull(() => optionsFactory, optionsFactory);
Guard.NotNull(() => headers, headers);
Guard.NotNull(() => commandCache, commandCache);
Guard.NotNull(() => configurationSend, configurationSend);
Guard.NotNull(() => sendJobStatus, sendJobStatus);
Guard.NotNull(() => jobExistsHandler, jobExistsHandler);
Guard.NotNull(() => jobSchedulerMetaData, jobSchedulerMetaData);
Guard.NotNull(() => getTimeFactory, getTimeFactory);
_tableNameHelper = tableNameHelper;
_serializer = serializer;
_options = new Lazy <PostgreSqlMessageQueueTransportOptions>(optionsFactory.Create);
_headers = headers;
_commandCache = commandCache;
_configurationSend = configurationSend;
_sendJobStatus = sendJobStatus;
_jobExistsHandler = jobExistsHandler;
_jobSchedulerMetaData = jobSchedulerMetaData;
_getTime = getTimeFactory.Create();
}
public void Returns_Properly_Escaped_TableName()
{
const string INITIAL_TABLENAME = "tablename.";
const string ESCAPED_TABLENAME = "[tablename]";
Assert.AreEqual(ESCAPED_TABLENAME, TableNameHelper.FormatTableName(INITIAL_TABLENAME, "[", "]"));
}
/// <summary>
/// Initializes a new instance of the <see cref="DoesJobExistQueryHandler{TConnection, TTransaction}" /> class.
/// </summary>
/// <param name="commandCache">The command cache.</param>
/// <param name="connectionInformation">The connection information.</param>
/// <param name="tableExists">The table exists.</param>
/// <param name="tableNameHelper">The table name helper.</param>
/// <param name="dbConnectionFactory">The database connection factory.</param>
/// <param name="transactionFactory">The transaction factory.</param>
/// <param name="prepareQuery">The prepare query.</param>
/// <param name="readColumn">The read column.</param>
public DoesJobExistQueryHandler(CommandStringCache commandCache,
IConnectionInformation connectionInformation,
IQueryHandler <GetTableExistsQuery, bool> tableExists,
TableNameHelper tableNameHelper,
IDbConnectionFactory dbConnectionFactory,
ITransactionFactory transactionFactory,
IPrepareQueryHandler <DoesJobExistQuery <TConnection, TTransaction>, QueueStatuses> prepareQuery,
IReadColumn readColumn)
{
Guard.NotNull(() => commandCache, commandCache);
Guard.NotNull(() => connectionInformation, connectionInformation);
Guard.NotNull(() => tableExists, tableExists);
Guard.NotNull(() => tableNameHelper, tableNameHelper);
Guard.NotNull(() => dbConnectionFactory, dbConnectionFactory);
Guard.NotNull(() => transactionFactory, transactionFactory);
Guard.NotNull(() => prepareQuery, prepareQuery);
Guard.NotNull(() => readColumn, readColumn);
_commandCache = commandCache;
_connectionInformation = connectionInformation;
_tableExists = tableExists;
_tableNameHelper = tableNameHelper;
_dbConnectionFactory = dbConnectionFactory;
_transactionFactory = transactionFactory;
_prepareQuery = prepareQuery;
_readColumn = readColumn;
}
/// <summary>
/// Initializes a new instance of the <see cref="ReceiveMessageQueryHandler" /> class.
/// </summary>
/// <param name="optionsFactory">The options factory.</param>
/// <param name="tableNameHelper">The table name helper.</param>
/// <param name="receivedMessageFactory">The received message factory.</param>
/// <param name="commandCache">The command cache.</param>
/// <param name="messageFactory">The message factory.</param>
/// <param name="headers">The headers.</param>
/// <param name="serialization">The serialization.</param>
/// <param name="getTimeFactory">The get time factory.</param>
public ReceiveMessageQueryHandler(IPostgreSqlMessageQueueTransportOptionsFactory optionsFactory,
TableNameHelper tableNameHelper,
IReceivedMessageFactory receivedMessageFactory,
PostgreSqlCommandStringCache commandCache,
IMessageFactory messageFactory,
IHeaders headers,
ICompositeSerialization serialization,
IGetTimeFactory getTimeFactory)
{
Guard.NotNull(() => optionsFactory, optionsFactory);
Guard.NotNull(() => tableNameHelper, tableNameHelper);
Guard.NotNull(() => receivedMessageFactory, receivedMessageFactory);
Guard.NotNull(() => commandCache, commandCache);
Guard.NotNull(() => messageFactory, messageFactory);
Guard.NotNull(() => serialization, serialization);
Guard.NotNull(() => headers, headers);
Guard.NotNull(() => getTimeFactory, getTimeFactory);
_options = new Lazy <PostgreSqlMessageQueueTransportOptions>(optionsFactory.Create);
_tableNameHelper = tableNameHelper;
_receivedMessageFactory = receivedMessageFactory;
_commandCache = commandCache;
_messageFactory = messageFactory;
_headers = headers;
_serialization = serialization;
_getTime = getTimeFactory.Create();
}
/// <inheritdoc />
public bool Create(LiteDbConnectionManager connection, LiteDbMessageQueueTransportOptions options,
TableNameHelper helper)
{
using (var db = connection.GetDatabase())
{
var col = db.Database.GetCollection <MetaDataTable>(helper.MetaDataName);
col.EnsureIndex(x => x.Id);
col.EnsureIndex(x => x.QueueId, true);
if (options.EnableStatus)
{
col.EnsureIndex(x => x.Status);
}
if (options.EnableMessageExpiration)
{
col.EnsureIndex(x => x.ExpirationTime);
}
if (options.EnableHeartBeat)
{
col.EnsureIndex(x => x.HeartBeat);
}
if (options.EnableRoute)
{
col.EnsureIndex(x => x.Route);
}
return(true);
}
}
/// <summary>
/// Initializes a new instance of the <see cref="ReceiveMessageQueryHandler" /> class.
/// </summary>
/// <param name="optionsFactory">The options factory.</param>
/// <param name="tableNameHelper">The table name helper.</param>
/// <param name="commandCache">The command cache.</param>
/// <param name="connectionInformation">The connection information.</param>
/// <param name="buildDequeueCommand">The build dequeue command.</param>
/// <param name="messageDeQueue">The message de queue.</param>
/// <param name="dbFactory">The transaction factory.</param>
/// <param name="databaseExists">The database exists.</param>
/// <param name="readerAsync">The reader asynchronous.</param>
public ReceiveMessageQueryHandlerAsync(ISqLiteMessageQueueTransportOptionsFactory optionsFactory,
TableNameHelper tableNameHelper,
IDbCommandStringCache commandCache,
IConnectionInformation connectionInformation,
BuildDequeueCommand buildDequeueCommand,
MessageDeQueue messageDeQueue,
IDbFactory dbFactory,
DatabaseExists databaseExists,
IReaderAsync readerAsync)
{
Guard.NotNull(() => optionsFactory, optionsFactory);
Guard.NotNull(() => tableNameHelper, tableNameHelper);
Guard.NotNull(() => commandCache, commandCache);
Guard.NotNull(() => buildDequeueCommand, buildDequeueCommand);
Guard.NotNull(() => messageDeQueue, messageDeQueue);
Guard.NotNull(() => databaseExists, databaseExists);
Guard.NotNull(() => readerAsync, readerAsync);
_options = new Lazy <SqLiteMessageQueueTransportOptions>(optionsFactory.Create);
_tableNameHelper = tableNameHelper;
_commandCache = commandCache;
_connectionInformation = connectionInformation;
_buildDequeueCommand = buildDequeueCommand;
_messageDeQueue = messageDeQueue;
_dbFactory = dbFactory;
_databaseExists = databaseExists;
_readerAsync = readerAsync;
}
/// <summary>
/// Gets the word cloud records.
/// </summary>
/// <param name="userName">Name of the user.</param>
/// <param name="key">The key.</param>
/// <returns>IEnumerable<WordCloudItem>.</returns>
public IEnumerable <WordCloudItem> GetWordCloudRecords(ClientUser user, string key)
{
var wordCloudTable = TableNameHelper.GetWordCloudTableName(user.Postfix);
var sql =
$"select Word, RelatedWords from {wordCloudTable} where Date = (select max(Date) from {wordCloudTable}) and Word = N'{key}'";
return(this.dbUtilities.ExecuteStoreQuery <WordCloudItem>(this.Context, sql));
}
/// <summary>
/// Initializes a new instance of the <see cref="GetMessageErrorsQueryHandler"/> class.
/// </summary>
/// <param name="connectionInformation">The connection information.</param>
/// <param name="tableNameHelper">The table name helper.</param>
public GetMessageErrorsQueryHandler(LiteDbConnectionManager connectionInformation,
TableNameHelper tableNameHelper)
{
Guard.NotNull(() => connectionInformation, connectionInformation);
Guard.NotNull(() => tableNameHelper, tableNameHelper);
_connectionInformation = connectionInformation;
_tableNameHelper = tableNameHelper;
}
/// <summary>
/// Initializes a new instance of the <see cref="PostgreSqlMessageQueueSchema"/> class.
/// </summary>
/// <param name="tableNameHelper">The table name helper.</param>
/// <param name="options">The options.</param>
public PostgreSqlMessageQueueSchema(TableNameHelper tableNameHelper,
IPostgreSqlMessageQueueTransportOptionsFactory options)
{
Guard.NotNull(() => tableNameHelper, tableNameHelper);
Guard.NotNull(() => options, options);
_tableNameHelper = tableNameHelper;
_options = new Lazy <PostgreSqlMessageQueueTransportOptions>(options.Create);
}
/// <summary>
/// Initializes a new instance of the <see cref="GetJobLastKnownEventQueryHandler"/> class.
/// </summary>
/// <param name="connectionInformation">The connection information.</param>
/// <param name="tableNameHelper">The table name helper.</param>
public GetJobLastKnownEventQueryHandler(LiteDbConnectionManager connectionInformation,
TableNameHelper tableNameHelper)
{
Guard.NotNull(() => connectionInformation, connectionInformation);
Guard.NotNull(() => tableNameHelper, tableNameHelper);
_connectionInformation = connectionInformation;
_tableNameHelper = tableNameHelper;
}
/// <summary>
/// Initializes a new instance of the <see cref="FindExpiredRecordsToDeleteQueryHandler"/> class.
/// </summary>
/// <param name="connectionInformation">The connection information.</param>
/// <param name="tableNameHelper">The table name helper.</param>
public FindExpiredRecordsToDeleteQueryHandler(LiteDbConnectionManager connectionInformation,
TableNameHelper tableNameHelper)
{
Guard.NotNull(() => connectionInformation, connectionInformation);
Guard.NotNull(() => tableNameHelper, tableNameHelper);
_connectionInformation = connectionInformation;
_tableNameHelper = tableNameHelper;
}
/// <summary>
/// Initializes a new instance of the <see cref="SendHeartBeatCommandHandler" /> class.
/// </summary>
public SendHeartBeatCommandHandler(LiteDbConnectionManager connectionInformation,
TableNameHelper tableNameHelper)
{
Guard.NotNull(() => connectionInformation, connectionInformation);
Guard.NotNull(() => tableNameHelper, tableNameHelper);
_connectionInformation = connectionInformation;
_tableNameHelper = tableNameHelper;
}
internal static SQLiteCommand CreateMetaDataRecord(TimeSpan? delay, TimeSpan expiration, SQLiteConnection connection,
IMessage message, IAdditionalMessageData data, TableNameHelper tableNameHelper,
IHeaders headers, SqLiteMessageQueueTransportOptions options, IGetTime getTime)
{
var command = new SQLiteCommand(connection);
BuildMetaCommand(command, tableNameHelper, headers,
data, message, 0, options, delay, expiration, getTime.GetCurrentUtcDate());
return command;
}
/// <summary>
/// Initializes a new instance of the <see cref="BuildMoveToErrorQueueSql" /> class.
/// </summary>
/// <param name="tableNameHelper">The table name helper.</param>
/// <param name="getColumns">The column query.</param>
public BuildMoveToErrorQueueSql(TableNameHelper tableNameHelper,
IGetColumnsFromTable getColumns)
{
Guard.NotNull(() => tableNameHelper, tableNameHelper);
Guard.NotNull(() => getColumns, getColumns);
_tableNameHelper = tableNameHelper;
_getColumns = getColumns;
}
/// <summary>
/// Gets the latest sentiment result.
/// </summary>
/// <param name="userName">Name of the user.</param>
/// <returns>IEnumerable<SentimentScanResult>.</returns>
public IEnumerable <SentimentScanResult> GetLatestSentimentResult(ClientUser user)
{
var userName = user.Name;
var postfix = user.Postfix;
var sentimentTableName = TableNameHelper.GetSentimentResultTableName(postfix);
var sql = $"select top 1 * from {sentimentTableName} where Name = N'{userName}' order by Date Desc";
return(this.dbUtilities.ExecuteStoreQuery <SentimentScanResult>(this.Context, sql));
}
/// <summary>
/// Initializes a new instance of the <see cref="ResetHeartBeatCommandHandler"/> class.
/// </summary>
/// <param name="tableNameHelper">The table name helper.</param>
/// <param name="connectionInformation">The connection information.</param>
public ResetHeartBeatCommandHandler(TableNameHelper tableNameHelper,
LiteDbConnectionManager connectionInformation)
{
_tableNameHelper = tableNameHelper;
_connectionInformation = connectionInformation;
Guard.NotNull(() => connectionInformation, connectionInformation);
Guard.NotNull(() => tableNameHelper, tableNameHelper);
}
/// <summary>
/// Initializes a new instance of the <see cref="DeleteQueueTablesCommandHandler"/> class.
/// </summary>
/// <param name="connectionInformation">The connection information.</param>
/// <param name="tableNameHelper">The table name helper.</param>
public DeleteQueueTablesCommandHandler(
LiteDbConnectionManager connectionInformation,
TableNameHelper tableNameHelper)
{
Guard.NotNull(() => connectionInformation, connectionInformation);
Guard.NotNull(() => tableNameHelper, tableNameHelper);
_connectionInformation = connectionInformation;
_tableNameHelper = tableNameHelper;
}
/// <summary>
/// Initializes a new instance of the <see cref="WeeklyReportRepository"/> class.
/// </summary>
/// <param name="user">The user.</param>
public WeeklyReportRepository(ClientUser user)
{
this.currentUser = user;
var postfix = this.currentUser.GetProfile().Postfix;
this.locationAndUserDemoTableName = TableNameHelper.GetLocationAndUserDemoTableName(postfix);
this.newsStreamTableName = TableNameHelper.GetNewsStreamTableName(postfix);
this.newsSentimentsTableName = TableNameHelper.GetNewsSentimentTableName(postfix);
this.hostVisitCountTableName = TableNameHelper.GetHostVisitCountTableName(postfix);
}
/// <summary>
/// Initializes a new instance of the <see cref="DoesJobExistQueryHandler" /> class.
/// </summary>
/// <param name="tableExists">The table exists.</param>
/// <param name="tableNameHelper">The table name helper.</param>
public DoesJobExistQueryHandler(
IQueryHandler <GetTableExistsQuery, bool> tableExists,
TableNameHelper tableNameHelper)
{
Guard.NotNull(() => tableExists, tableExists);
Guard.NotNull(() => tableNameHelper, tableNameHelper);
_tableExists = tableExists;
_tableNameHelper = tableNameHelper;
}
/// <summary>Initializes a new instance of the <see cref="SqlServerMessageQueueSchema"/> class.</summary>
/// <param name="tableNameHelper">The table name helper. Note this is the base module</param>
/// <param name="options">The options.</param>
/// <param name="schema">The schema that the queue is using</param>
public SqlServerMessageQueueSchema(TableNameHelper tableNameHelper,
ISqlServerMessageQueueTransportOptionsFactory options, ISqlSchema schema)
{
Guard.NotNull(() => tableNameHelper, tableNameHelper);
Guard.NotNull(() => options, options);
_tableNameHelper = tableNameHelper;
_options = new Lazy <SqlServerMessageQueueTransportOptions>(options.Create);
_schema = schema;
}
protected override void DisableTableConstraints(DataTable dataTable, IDbTransaction dbTransaction)
{
DbCommand sqlCommand =
(DbCommand)CreateDbCommand("ALTER TABLE " +
TableNameHelper.FormatTableName(dataTable.TableName, QuotePrefix, QuoteSuffix) +
" NOCHECK CONSTRAINT ALL");
sqlCommand.Connection = (DbConnection)dbTransaction.Connection;
sqlCommand.Transaction = (DbTransaction)dbTransaction;
sqlCommand.ExecuteNonQuery();
}
internal static void BuildStatusCommand(IDbCommand command,
TableNameHelper tableNameHelper,
IHeaders headers,
IAdditionalMessageData data,
IMessage message,
long id,
SqLiteMessageQueueTransportOptions options,
DateTime currentDateTime)
{
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, null, TimeSpan.Zero, currentDateTime);
if (id > 0)
{
var paramid = command.CreateParameter();
paramid.ParameterName = "@QueueID";
paramid.DbType = DbType.Int64;
paramid.Value = id;
command.Parameters.Add(paramid);
}
var param = command.CreateParameter();
param.ParameterName = "@CorrelationID";
param.DbType = DbType.StringFixedLength;
param.Size = 38;
param.Value = data.CorrelationId.Id.Value.ToString();
command.Parameters.Add(param);
//add configurable column command params - user
AddUserColumnsParams(command, data);
AddHeaderColumnParams(command, message, headers);
}
public CreateDequeueStatement(ISqlServerMessageQueueTransportOptionsFactory optionsFactory,
TableNameHelper tableNameHelper,
SqlServerCommandStringCache commandCache)
{
Guard.NotNull(() => optionsFactory, optionsFactory);
Guard.NotNull(() => tableNameHelper, tableNameHelper);
Guard.NotNull(() => commandCache, commandCache);
_options = new Lazy<SqlServerMessageQueueTransportOptions>(optionsFactory.Create);
_tableNameHelper = tableNameHelper;
_commandCache = commandCache;
}
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 BuildStatusCommand(SQLiteCommand command,
TableNameHelper tableNameHelper,
IHeaders headers,
IAdditionalMessageData data,
IMessage message,
long id,
SqLiteMessageQueueTransportOptions options,
DateTime currentDateTime)
{
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, null, TimeSpan.Zero, currentDateTime);
command.Parameters.Add("@QueueID", DbType.Int64, 8).Value = id;
command.Parameters.Add("@CorrelationID", DbType.StringFixedLength, 38).Value = data.CorrelationId.Id.Value.ToString();
//add configurable column command params - user
AddUserColumnsParams(command, data);
AddHeaderColumnParams(command, message, headers);
}
internal static void BuildStatusCommand(SqlCommand command,
TableNameHelper tableNameHelper,
IHeaders headers,
IAdditionalMessageData data,
IMessage message,
long id,
SqlServerMessageQueueTransportOptions 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", SqlDbType.BigInt, 8).Value = id;
command.Parameters.Add("@CorrelationID", SqlDbType.UniqueIdentifier, 16).Value = data.CorrelationId.Id.Value;
//add configurable column command params - user
AddUserColumnsParams(command, data);
AddHeaderColumnParams(command, message, headers);
}
internal static void BuildMetaCommand(SQLiteCommand command,
TableNameHelper tableNameHelper,
IHeaders headers,
IAdditionalMessageData data,
IMessage message,
long id,
SqLiteMessageQueueTransportOptions 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, @CurrentDate ");
//add the values for built in fields
options.AddBuiltInColumnValues(delay, expiration, sbMeta);
AddHeaderValues(sbMeta, message, headers);
sbMeta.Append(")"); //close the VALUES
command.CommandText = sbMeta.ToString();
options.AddBuiltInColumnsParams(command, data, delay, expiration, currentDateTime);
AddHeaderColumnParams(command, message, headers);
command.Parameters.Add("@QueueID", DbType.Int64, 8).Value = id;
command.Parameters.Add("@CorrelationID", DbType.StringFixedLength, 38).Value = data.CorrelationId.Id.Value.ToString();
command.Parameters.Add("@CurrentDate", DbType.Int64).Value = currentDateTime.Ticks;
}
/// <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());
}
}
