public IQuery Add(Guid id, string key)
{
return
(RawQuery.Create(_scriptProvider.Get("KeyStore.Add"))
.AddParameterValue(Columns.Key, key)
.AddParameterValue(Columns.Id, id));
}
public IQuery GetSnapshot(Guid id)
{
return
(RawQuery.Create(
@"select Version, AssemblyQualifiedName, Data from [dbo].[SnapshotStore] es inner join [dbo].[TypeStore] ts on (es.TypeId = ts.Id) where es.Id = @Id")
.AddParameterValue(EventStoreColumns.Id, id));
}
public IEnumerable <string> GetSubscribedUris(object message)
{
Guard.AgainstNull(message, "message");
var messageType = message.GetType().FullName;
if (!_subscribers.ContainsKey(messageType))
{
lock (Padlock)
{
if (!_subscribers.ContainsKey(messageType))
{
DataTable table;
using (_databaseContextFactory.Create(_subscriptionProviderName, _subscriptionConnectionString))
{
table = _databaseGateway.GetDataTableFor(
RawQuery.Create(
_scriptProvider.Get(
Script.SubscriptionManagerInboxWorkQueueUris))
.AddParameterValue(SubscriptionManagerColumns.MessageType, messageType));
}
_subscribers.Add(messageType, (from DataRow row in table.Rows
select SubscriptionManagerColumns.InboxWorkQueueUri.MapFrom(row))
.ToList());
}
}
}
return(_subscribers[messageType]);
}
public void Initialize(IServiceBus bus)
{
using (
var connection = _databaseContextFactory.Create(SqlServerConfiguration.ProviderName,
_idempotenceConnectionString))
using (var transaction = connection.BeginTransaction())
{
if (_databaseGateway.GetScalarUsing <int>(
RawQuery.Create(
_scriptProvider.GetScript(
Script.IdempotenceServiceExists))) != 1)
{
throw new IdempotenceServiceException(SqlResources.IdempotenceDatabaseNotConfigured);
}
_databaseGateway.ExecuteUsing(
RawQuery.Create(
_scriptProvider.GetScript(
Script.IdempotenceInitialize))
.AddParameterValue(IdempotenceColumns.InboxWorkQueueUri,
bus.Configuration.Inbox.WorkQueue.Uri.ToString()));
transaction.CommitTransaction();
}
}
private void Initialize()
{
using (_databaseContextFactory.Create(_connectionName))
{
_createQuery = RawQuery.Create(_scriptProvider.Get(Script.QueueCreate, _tableName))
.AddParameterValue(QueueColumns.UnacknowledgedHash, _unacknowledgedHash)
.AddParameterValue(QueueColumns.MachineName, _machineName)
.AddParameterValue(QueueColumns.QueueName, _tableName)
.AddParameterValue(QueueColumns.BaseDirectory, _baseDirectory);
_existsQuery = RawQuery.Create(_scriptProvider.Get(Script.QueueExists, _tableName));
_dropQuery = RawQuery.Create(_scriptProvider.Get(Script.QueueDrop, _tableName));
_purgeQuery = RawQuery.Create(_scriptProvider.Get(Script.QueuePurge, _tableName));
_countQuery = RawQuery.Create(_scriptProvider.Get(Script.QueueCount, _tableName));
_enqueueQueryStatement = _scriptProvider.Get(Script.QueueEnqueue, _tableName);
_removeQueryStatement = _scriptProvider.Get(Script.QueueRemove, _tableName);
_dequeueIdQueryStatement = _scriptProvider.Get(Script.QueueDequeueId, _tableName);
}
Create();
using (_databaseContextFactory.Create(_connectionName))
{
_databaseGateway.ExecuteUsing(RawQuery.Create(_scriptProvider.Get(Script.QueueRelease, _tableName))
.AddParameterValue(QueueColumns.UnacknowledgedHash, _unacknowledgedHash));
}
}
public IQuery Add(OrderProcessRegisteredEvent message)
{
return(RawQuery.Create(@"
insert into dbo.OrderProcessView
(
Id,
CustomerName,
OrderNumber,
OrderDate,
OrderTotal,
Status,
TargetSystem,
TargetSystemUri
)
values
(
@Id,
@CustomerName,
@OrderNumber,
@OrderDate,
@OrderTotal,
@Status,
@TargetSystem,
@TargetSystemUri
)
")
.AddParameterValue(OrderProcessViewColumns.Id, message.OrderProcessId)
.AddParameterValue(OrderProcessViewColumns.CustomerName, message.CustomerName)
.AddParameterValue(OrderProcessViewColumns.OrderNumber, message.OrderNumber)
.AddParameterValue(OrderProcessViewColumns.OrderDate, message.OrderDate)
.AddParameterValue(OrderProcessViewColumns.OrderTotal, message.OrderTotal)
.AddParameterValue(OrderProcessViewColumns.Status, message.Status)
.AddParameterValue(OrderProcessViewColumns.TargetSystem, message.TargetSystem)
.AddParameterValue(OrderProcessViewColumns.TargetSystemUri, message.TargetSystemUri));
}
public IQuery FindContent(Guid id)
{
return(RawQuery.Create(@"
select
[Status],
case
when [Status] = 'Cleared' then
Content
when [Status] = 'Suspicious' then
SanitizedContent
else
null
end Content,
ContentType,
FileName
from
[dbo].[Document]
where
Id = @Id
or
(
ReferenceId = @ReferenceId
and
EffectiveToDate = @EffectiveToDate
)
")
.AddParameterValue(Columns.Id, id)
.AddParameterValue(Columns.ReferenceId, id)
.AddParameterValue(Columns.EffectiveToDate, DateTime.MaxValue));
}
public static TypedExpression MD5(TypedExpression query)
{
var rq = RawQuery.Create("MD5(").Append(query.RawQuery).Append(")");
// ReSharper disable once HeapView.ObjectAllocation.Evident
return(new TypedExpression(rq, KDPgValueTypeInstances.String));
}
public static TypedExpression Count(TypedExpression query)
{
var rq = RawQuery.Create("COUNT(").Append(query.RawQuery).Append(")");
// ReSharper disable once HeapView.ObjectAllocation.Evident
return(new TypedExpression(rq, KDPgValueTypeInstances.Integer64));
}
public static TypedExpression DatePart(ExtractField field, TypedExpression source)
{
var rq = RawQuery.Create("date_part(").AppendStringValue(field.GetMemberValue()).Append(",").Append(source.RawQuery).Append(")");
// ReSharper disable once HeapView.ObjectAllocation.Evident
return(new TypedExpression(rq, KDPgValueTypeInstances.Decimal));
}
public static TypedExpression Timezone(TypedExpression zone, TypedExpression timestamp)
{
var rq = RawQuery.Create("timezone(").Append(zone.RawQuery).Append(",").Append(timestamp.RawQuery).Append(")");
// ReSharper disable once HeapView.ObjectAllocation.Evident
return(new TypedExpression(rq, KDPgValueTypeInstances.DateTime));
}
public IQuery Register(Guid id, string name, Guid formulaId, string expectedResult,
string expectedResultDataTypeName, string comparison)
{
return(RawQuery.Create(@"
insert into Test
(
Id,
Name,
FormulaId,
ExpectedResult,
ExpectedResultDataTypeName,
Comparison
)
values
(
@Id,
@Name,
@FormulaId,
@ExpectedResult,
@ExpectedResultDataTypeName,
@Comparison
)")
.AddParameterValue(Columns.Id, id)
.AddParameterValue(Columns.Name, name)
.AddParameterValue(Columns.FormulaId, formulaId)
.AddParameterValue(Columns.ExpectedResult, expectedResult)
.AddParameterValue(Columns.ExpectedResultDataTypeName, expectedResultDataTypeName)
.AddParameterValue(Columns.Comparison, comparison));
}
public IQuery RemoveArgumentValue(Guid id, Guid argumentId)
{
return(RawQuery.Create(
"delete from TestArgument where TestId = @Id and ArgumentId = @ArgumentId")
.AddParameterValue(Columns.Id, id)
.AddParameterValue(Columns.ArgumentId, argumentId));
}
public IQuery Rekey(string key, string rekey)
{
return
(RawQuery.Create(_scriptProvider.Get("KeyStore.Rekey"))
.AddParameterValue(Columns.Key, key)
.AddParameterValue(Columns.Rekey, rekey));
}
public void Initialize(IServiceBus bus)
{
_serviceBusConfiguration = bus.Configuration;
using (_databaseContextFactory.Create(SqlServerConfiguration.ProviderName, _subscriptionConnectionString))
{
if (_databaseGateway.GetScalarUsing <int>(
RawQuery.Create(
_scriptProvider.GetScript(
Script.SubscriptionManagerExists))) != 1)
{
try
{
_databaseGateway.ExecuteUsing(RawQuery.Create(
_scriptProvider.GetScript(
Script.SubscriptionManagerCreate)));
}
catch (Exception ex)
{
if (!ex.Message.Equals("There is already an object named 'SubscriberMessageType' in the database.", StringComparison.OrdinalIgnoreCase))
{
throw new DataException(SqlResources.SubscriptionManagerCreateException, ex);
}
}
}
}
if (HasDeferredSubscriptions)
{
Subscribe(_deferredSubscriptions);
}
}
public static TypedExpression Now()
{
var rq = RawQuery.Create("NOW()");
// ReSharper disable once HeapView.ObjectAllocation.Evident
return(new TypedExpression(rq, KDPgValueTypeInstances.Time));
}
public IQuery Add(string machineName, string baseDirectory, string ipv4Address, string inboxWorkQueueUri,
string controlInboxWorkQueueUri)
{
return(RawQuery.Create(@"
insert into Server
(
MachineName,
BaseDirectory,
IPv4Address,
InboxWorkQueueUri,
ControlInboxWorkQueueUri
)
values
(
@MachineName,
@BaseDirectory,
@IPv4Address,
@InboxWorkQueueUri,
@ControlInboxWorkQueueUri
)
")
.AddParameterValue(ServerColumns.MachineName, machineName)
.AddParameterValue(ServerColumns.BaseDirectory, baseDirectory)
.AddParameterValue(ServerColumns.IPv4Address, ipv4Address)
.AddParameterValue(ServerColumns.InboxWorkQueueUri, inboxWorkQueueUri)
.AddParameterValue(ServerColumns.ControlInboxWorkQueueUri, controlInboxWorkQueueUri));
}
private void Connect()
{
if (string.IsNullOrEmpty(ConnectionString.Text))
{
return;
}
_tables.Clear();
try
{
using (_databaseContextFactory.Create("System.Data.SqlClient", ConnectionString.Text))
{
foreach (
var row in
_databaseGateway.GetRowsUsing(
RawQuery.Create("select TABLE_NAME from INFORMATION_SCHEMA.TABLES order by 1")))
{
_tables.Add(row[0].ToString());
}
}
FilterTables();
SaveConnectionString();
}
catch (Exception ex)
{
ShowMessage(ex.AllMessages());
}
}
public IQuery GetStatusEvents(IEnumerable <Guid> documentIds)
{
Guard.AgainstNull(documentIds, nameof(documentIds));
var ids = string.Join(",", documentIds.Select(id => $"'{id}'"));
if (string.IsNullOrEmpty(ids))
{
throw new ArgumentException($"Argument '{nameof(documentIds)}' may not be empty.");
}
return(RawQuery.Create($@"
select
DocumentId,
SequenceNumber,
Status,
DateRegistered
from
DocumentStatusEvent
where
DocumentId in ({ids})
order by
DocumentId,
SequenceNumber
"));
}
public IQuery AddMessageTypeDispatched(Guid endpointId, string dispatchedMessageType,
string recipientInboxWorkQueueUri)
{
return(RawQuery.Create(@"
if not exists
(
select
null
from
MessageTypeDispatched
where
EndpointId = @Id
and
MessageType = @MessageType
and
RecipientInboxWorkQueueUri = @RecipientInboxWorkQueueUri
)
insert into MessageTypeDispatched
(
EndpointId,
MessageType,
RecipientInboxWorkQueueUri
)
values
(
@Id,
@MessageType,
@RecipientInboxWorkQueueUri
)
")
.AddParameterValue(Columns.Id, endpointId)
.AddParameterValue(MessageColumns.MessageType, dispatchedMessageType)
.AddParameterValue(MessageColumns.RecipientInboxWorkQueueUri, recipientInboxWorkQueueUri));
}
public void ClearSubscriptions()
{
using (DatabaseContextFactory.Create(ProviderName, ConnectionString))
{
DatabaseGateway.ExecuteUsing(RawQuery.Create("delete from SubscriberMessageType"));
}
}
public IQuery AddMessageTypeAssociation(Guid endpointId, string messageTypeHandled,
string messageTypeDispatched)
{
return(RawQuery.Create(@"
if not exists
(
select
null
from
MessageTypeAssociation
where
EndpointId = @Id
and
MessageTypeHandled = @MessageTypeHandled
and
MessageTypeDispatched = @MessageTypeDispatched
)
insert into MessageTypeAssociation
(
EndpointId,
MessageTypeHandled,
MessageTypeDispatched
)
values
(
@Id,
@MessageTypeHandled,
@MessageTypeDispatched
)
")
.AddParameterValue(Columns.Id, endpointId)
.AddParameterValue(MessageColumns.MessageTypeHandled, messageTypeHandled)
.AddParameterValue(MessageColumns.MessageTypeDispatched, messageTypeDispatched));
}
public void Release(object acknowledgementToken)
{
var sequenceId = (int)acknowledgementToken;
if (sequenceId <= 0)
{
return;
}
using (var connection = _databaseContextFactory.Create(_connectionName))
using (var transaction = connection.BeginTransaction())
{
var row = _databaseGateway.GetSingleRowUsing(
RawQuery.Create(_dequeueIdQueryStatement)
.AddParameterValue(QueueColumns.SequenceId, sequenceId));
if (row != null)
{
_databaseGateway.ExecuteUsing(RawQuery.Create(_removeQueryStatement)
.AddParameterValue(QueueColumns.SequenceId, sequenceId));
_databaseGateway.ExecuteUsing(
RawQuery.Create(_enqueueQueryStatement)
.AddParameterValue(QueueColumns.MessageId, QueueColumns.MessageId.MapFrom(row))
.AddParameterValue(QueueColumns.MessageBody, row["MessageBody"]));
}
transaction.CommitTransaction();
}
}
public IQuery Add(Invoice invoice)
{
return(RawQuery.Create(@"
insert into dbo.Invoice
(
Id,
InvoiceNumber,
InvoiceDate,
OrderId,
AccountContactName,
AccountContactEMail
)
values
(
@Id,
@InvoiceNumber,
@InvoiceDate,
@OrderId,
@AccountContactName,
@AccountContactEMail
)
")
.AddParameterValue(InvoiceColumns.Id, invoice.Id)
.AddParameterValue(InvoiceColumns.InvoiceNumber, invoice.InvoiceNumber)
.AddParameterValue(InvoiceColumns.InvoiceDate, invoice.InvoiceDate)
.AddParameterValue(InvoiceColumns.OrderId, invoice.OrderId)
.AddParameterValue(InvoiceColumns.AccountContactName, invoice.AccountContact.Name)
.AddParameterValue(InvoiceColumns.AccountContactEMail, invoice.AccountContact.EMail));
}
public void Should_be_able_store_different_versions_of_the_same_reference_id()
{
var repository = new DocumentRepository(DatabaseGateway, new DocumentQueryFactory());
using (TransactionScopeFactory.Create())
using (DatabaseContextFactory.Create())
{
var referenceId = Guid.NewGuid();
var query = RawQuery.Create($"select count(*) from dbo.Document where ReferenceId = '{referenceId}'");
Assert.That(DatabaseGateway.GetScalarUsing <int>(query), Is.Zero);
repository.Save(_documentFixture.GetDocument(Guid.NewGuid(), referenceId));
Assert.That(DatabaseGateway.GetScalarUsing <int>(query), Is.EqualTo(1));
repository.Save(_documentFixture.GetDocument(Guid.NewGuid(), referenceId));
Assert.That(DatabaseGateway.GetScalarUsing <int>(query), Is.EqualTo(2));
repository.Save(_documentFixture.GetDocument(Guid.NewGuid(), referenceId));
Assert.That(DatabaseGateway.GetScalarUsing <int>(query), Is.EqualTo(3));
}
}
public IEnumerable <string> GetSubscribedUris(string messageType)
{
Guard.AgainstNullOrEmptyString(messageType, "messageType");
if (!subscribers.ContainsKey(messageType))
{
lock (padlock)
{
if (!subscribers.ContainsKey(messageType))
{
DataTable table;
using (databaseConnectionFactory.Create(_subscriptionDataSource))
{
table = databaseGateway.GetDataTableFor(
_subscriptionDataSource,
RawQuery.Create(
scriptProvider.GetScript(
Script.SubscriptionManagerInboxWorkQueueUris))
.AddParameterValue(SubscriptionManagerColumns.MessageType, messageType));
}
subscribers.Add(messageType, (from DataRow row in table.Rows
select SubscriptionManagerColumns.InboxWorkQueueUri.MapFrom(row))
.ToList());
}
}
}
return(subscribers[messageType]);
}
public IQuery Get(long sequenceNumber)
{
return
(RawQuery.Create(
@"select es.Id, Version, AssemblyQualifiedName, Data, SequenceNumber, DateRegistered from [dbo].[EventStore] es inner join [dbo].[TypeStore] ts on (es.TypeId = ts.Id) where es.SequenceNumber >= @SequenceNumber")
.AddParameterValue(EventStoreColumns.SequenceNumber, sequenceNumber));
}
public void Subscribe(IEnumerable <string> messageTypeFullNames)
{
Guard.AgainstNull(messageTypeFullNames, "messageTypeFullNames");
if (!Started)
{
deferredSubscriptions.AddRange(messageTypeFullNames);
return;
}
using (databaseConnectionFactory.Create(_subscriptionDataSource))
{
foreach (var messageType in messageTypeFullNames)
{
databaseGateway.ExecuteUsing(
_subscriptionDataSource,
RawQuery.Create(
scriptProvider.GetScript(Script.SubscriptionManagerSubscribe))
.AddParameterValue(SubscriptionManagerColumns.InboxWorkQueueUri,
serviceBusConfiguration.Inbox.WorkQueue.Uri.ToString())
.AddParameterValue(SubscriptionManagerColumns.MessageType, messageType));
}
}
}
public void SetUp()
{
using (DatabaseContextFactory.Create())
{
DatabaseGateway.ExecuteUsing(RawQuery.Create("delete from SubscriberMessageType"));
}
}
public IQuery Enqueue(string sourceQueueUri, TransportMessage transportMessage, Stream stream)
{
return(RawQuery.Create(@"insert into [dbo].[InspectionQueue] (SourceQueueUri, MessageId, MessageBody) values (@SourceQueueUri, @MessageId, @MessageBody)")
.AddParameterValue(InspectionQueueColumns.SourceQueueUri, sourceQueueUri)
.AddParameterValue(InspectionQueueColumns.MessageId, transportMessage.MessageId)
.AddParameterValue(InspectionQueueColumns.MessageBody, stream.ToBytes()));
}