public static SqlCommand BuildIncomingStorageCommand(IEnumerable <Envelope> envelopes,
SqlServerSettings settings)
{
var cmd = new SqlCommand();
var builder = new CommandBuilder(cmd);
foreach (var envelope in envelopes)
{
envelope.EnsureData();
var id = builder.AddParameter(envelope.Id, SqlDbType.UniqueIdentifier);
var status = builder.AddParameter(envelope.Status, SqlDbType.VarChar);
var owner = builder.AddParameter(envelope.OwnerId, SqlDbType.Int);
var attempts = builder.AddParameter(envelope.Attempts, SqlDbType.Int);
var time = builder.AddParameter(envelope.ExecutionTime, SqlDbType.DateTimeOffset);
var body = builder.AddParameter(envelope.Serialize(), SqlDbType.VarBinary);
builder.Append(
$"insert into {settings.SchemaName}.{IncomingTable} (id, status, owner_id, execution_time, attempts, body) values (@{id.ParameterName}, @{status.ParameterName}, @{owner.ParameterName}, @{time.ParameterName}, @{attempts.ParameterName}, @{body.ParameterName});");
}
builder.Apply();
return(cmd);
}
[Fact] // -- too slow
public async Task tx_session_locks()
{
var settings = new SqlServerSettings();
using (var conn1 = new SqlConnection(Servers.SqlServerConnectionString))
using (var conn2 = new SqlConnection(Servers.SqlServerConnectionString))
using (var conn3 = new SqlConnection(Servers.SqlServerConnectionString))
{
await conn1.OpenAsync();
await conn2.OpenAsync();
await conn3.OpenAsync();
var tx1 = conn1.BeginTransaction();
await settings.GetGlobalTxLock(conn1, tx1, 4);
// Cannot get the lock here
var tx2 = conn2.BeginTransaction();
(await settings.TryGetGlobalTxLock(conn2, tx2, 4)).ShouldBeFalse();
// Can get the new lock
var tx3 = conn3.BeginTransaction();
(await settings.TryGetGlobalTxLock(conn3, tx3, 5)).ShouldBeTrue();
// Cannot get the lock here
(await settings.TryGetGlobalTxLock(conn2, tx2, 5)).ShouldBeFalse();
tx1.Rollback();
tx2.Rollback();
tx3.Rollback();
}
}
public static SqlServerSettings SqlServerSettings(this IConfigurationRoot configuration)
{
var connectionString = configuration.GetConnectionString("default");
var settings = new SqlServerSettings(connectionString);
return(settings);
}
public SqlServerBackedDurableMessagingFactory(SqlServerSettings sqlServerSettings, ITransportLogger logger, MessagingSettings settings)
{
Settings = settings;
_sqlServerSettings = sqlServerSettings;
_logger = logger;
_persistor = new SqlServerEnvelopePersistor(sqlServerSettings);
_retries = new EnvelopeRetries(_persistor, logger, settings);
}
public static IServiceCollection AddRepositories(this IServiceCollection services, IConfiguration configuration)
{
var connectionString = configuration.GetConnectionString("default");
var settings = new SqlServerSettings(connectionString);
services.AddSingleton(settings);
services.AddSingleton <IArticles, Articles>();
return(services);
}
public RecoverIncomingMessages(IWorkerQueue workers, MessagingSettings settings, SqlServerSettings mssqlSettings,
ITransportLogger logger)
{
_workers = workers;
_settings = settings;
_mssqlSettings = mssqlSettings;
_logger = logger;
_findAtLargeEnvelopesSql = $"select top {settings.Retries.RecoveryBatchSize} body from {mssqlSettings.SchemaName}.{SqlServerEnvelopePersistor.IncomingTable} where owner_id = {TransportConstants.AnyNode} and status = '{TransportConstants.Incoming}'";
}
public SchedulingAgent(IChannelGraph channels, IWorkerQueue workers, SqlServerSettings mssqlSettings,
MessagingSettings settings, ITransportLogger logger, IRetries retries)
: base(settings, logger,
new RunScheduledJobs(workers, mssqlSettings, logger, retries, settings),
new RecoverIncomingMessages(workers, settings, mssqlSettings, logger),
new RecoverOutgoingMessages(channels, settings, mssqlSettings, logger),
new ReassignFromDormantNodes(mssqlSettings, settings)
)
{
_mssqlSettings = mssqlSettings;
}
public SchedulingAgent(ISubscriberGraph subscribers, IWorkerQueue workers, SqlServerSettings mssqlSettings,
JasperOptions settings, ITransportLogger logger, IRetries retries)
: base(settings, logger,
new RunScheduledJobs(workers, mssqlSettings, logger, retries, settings),
new RecoverIncomingMessages(workers, settings, mssqlSettings, logger),
new RecoverOutgoingMessages(subscribers, settings, mssqlSettings, logger),
new ReassignFromDormantNodes(mssqlSettings, settings)
)
{
_mssqlSettings = mssqlSettings;
}
public RunScheduledJobs(IWorkerQueue workers, SqlServerSettings mssqlSettings, ITransportLogger logger, IRetries retries, MessagingSettings settings)
{
_workers = workers;
_mssqlSettings = mssqlSettings;
_logger = logger;
_retries = retries;
_settings = settings;
_persistor = new SqlServerEnvelopePersistor(_mssqlSettings);
_findReadyToExecuteJobs = $"select body from {mssqlSettings.SchemaName}.{SqlServerEnvelopePersistor.IncomingTable} where status = '{TransportConstants.Scheduled}' and execution_time <= @time";
}
public RecoverOutgoingMessages(ISubscriberGraph subscribers, MessagingSettings settings, SqlServerSettings mssqlSettings,
ITransportLogger logger)
{
_subscribers = subscribers;
_settings = settings;
_mssqlSettings = mssqlSettings;
_logger = logger;
_findUniqueDestinations = $"select distinct destination from {_mssqlSettings.SchemaName}.{SqlServerEnvelopePersistor.OutgoingTable}";
_findOutgoingEnvelopesSql = $"select top {settings.Retries.RecoveryBatchSize} body from {_mssqlSettings.SchemaName}.{SqlServerEnvelopePersistor.OutgoingTable} where owner_id = {TransportConstants.AnyNode} and destination = @destination";
_deleteOutgoingSql = $"delete from {_mssqlSettings.SchemaName}.{SqlServerEnvelopePersistor.OutgoingTable} where owner_id = :owner and destination = @destination";
}
/// <summary>
/// Creates a new instance of the <see cref="TestEnvironment"/> class.
/// </summary>
public TestEnvironment()
{
const string localSettingsFile = "settings.local.json";
IConfigurationBuilder builder = new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory());
IConfigurationRoot configuration = builder
.AddJsonFile(localSettingsFile, true, true)
.Build();
SqlServerSettings = new SqlServerSettings()
{
ConnectionString = configuration["ConnectionString"]
};
}
public SqlServerBackedListenerContext()
{
new SqlServerEnvelopeStorageAdmin(new SqlServerSettings{ConnectionString = Servers.SqlServerConnectionString}).RecreateAll();
theSettings = new AdvancedSettings(null);
mssqlSettings = new SqlServerSettings
{
ConnectionString = Servers.SqlServerConnectionString
};
ThePersistence = new SqlServerEnvelopePersistence(mssqlSettings, theSettings);
thePipeline = Substitute.For<IHandlerPipeline>();
theWorkerQueue = new DurableWorkerQueue(new LocalQueueSettings("temp"), thePipeline, theSettings, ThePersistence, TransportLogger.Empty());
}
public ReassignFromDormantNodes(SqlServerSettings marker, MessagingSettings settings)
{
_settings = settings;
_fetchOwnersSql = $@"
select distinct owner_id from {marker.SchemaName}.{SqlServerEnvelopePersistor.IncomingTable} where owner_id != 0 and owner_id != @owner
union
select distinct owner_id from {marker.SchemaName}.{SqlServerEnvelopePersistor.OutgoingTable} where owner_id != 0 and owner_id != @owner";
_reassignDormantNodeSql = $@"
update {marker.SchemaName}.{SqlServerEnvelopePersistor.IncomingTable}
set owner_id = 0
where
owner_id = @owner;
update {marker.SchemaName}.{SqlServerEnvelopePersistor.OutgoingTable}
set owner_id = 0
where
owner_id = @owner;
";
}
public async Task explicitly_release_global_tx_session_locks()
{
var settings = new SqlServerSettings();
using (var conn1 = new SqlConnection(Servers.SqlServerConnectionString))
using (var conn2 = new SqlConnection(Servers.SqlServerConnectionString))
using (var conn3 = new SqlConnection(Servers.SqlServerConnectionString))
{
await conn1.OpenAsync();
await conn2.OpenAsync();
await conn3.OpenAsync();
var tx1 = conn1.BeginTransaction();
await settings.GetGlobalTxLock(conn1, tx1, 2);
// Cannot get the lock here
var tx2 = conn2.BeginTransaction();
(await settings.TryGetGlobalTxLock(conn2, tx2, 2)).ShouldBeFalse();
tx1.Rollback();
for (var j = 0; j < 5; j++)
{
if (await settings.TryGetGlobalTxLock(conn2, tx2, 2))
{
tx2.Rollback();
return;
}
await Task.Delay(250);
}
throw new Exception("Advisory lock was not released");
}
}
public async Task explicitly_release_global_session_locks()
{
var settings = new SqlServerSettings();
using (var conn1 = new SqlConnection(Servers.SqlServerConnectionString))
using (var conn2 = new SqlConnection(Servers.SqlServerConnectionString))
using (var conn3 = new SqlConnection(Servers.SqlServerConnectionString))
{
await conn1.OpenAsync();
await conn2.OpenAsync();
await conn3.OpenAsync();
await settings.GetGlobalLock(conn1, 1);
// Cannot get the lock here
(await settings.TryGetGlobalLock(conn2, 1)).ShouldBeFalse();
await settings.ReleaseGlobalLock(conn1, 1);
for (var j = 0; j < 5; j++)
{
if (await settings.TryGetGlobalLock(conn2, 1))
{
return;
}
await Task.Delay(250);
}
throw new Exception("Advisory lock was not released");
}
}
public SqlServerBackedListenerContext()
{
new SchemaLoader(ConnectionSource.ConnectionString).RecreateAll();
theWorkerQueue = Substitute.For <IWorkerQueue>();
theSettings = new MessagingSettings();
mssqlSettings = new SqlServerSettings
{
ConnectionString = ConnectionSource.ConnectionString
};
thePersistor = new SqlServerEnvelopePersistor(mssqlSettings);
retries = new EnvelopeRetries(thePersistor, TransportLogger.Empty(), theSettings);
theListener = new DurableListener(
Substitute.For <IListeningAgent>(),
theWorkerQueue,
TransportLogger.Empty(), theSettings, retries, thePersistor);
}
public SqlServerBackedListenerContext()
{
new SqlServerEnvelopeStorageAdmin(new SqlServerSettings {
ConnectionString = Servers.SqlServerConnectionString
}).RecreateAll();
theWorkerQueue = Substitute.For <IWorkerQueue>();
theSettings = new JasperOptions();
mssqlSettings = new SqlServerSettings
{
ConnectionString = Servers.SqlServerConnectionString
};
ThePersistence = new SqlServerEnvelopePersistence(mssqlSettings, new JasperOptions());
theListener = new DurableListener(
Substitute.For <IListeningAgent>(),
theWorkerQueue,
TransportLogger.Empty(), theSettings, ThePersistence);
}
public static SqlCommand BuildOutgoingStorageCommand(Envelope[] envelopes, int ownerId,
SqlServerSettings settings)
{
var cmd = new SqlCommand();
var builder = new CommandBuilder(cmd);
builder.AddNamedParameter("owner", ownerId).SqlDbType = SqlDbType.Int;
foreach (var envelope in envelopes)
{
envelope.EnsureData();
var id = builder.AddParameter(envelope.Id, SqlDbType.UniqueIdentifier);
var destination = builder.AddParameter(envelope.Destination.ToString(), SqlDbType.VarChar);
var deliverBy = builder.AddParameter(envelope.DeliverBy, SqlDbType.DateTimeOffset);
var body = builder.AddParameter(envelope.Serialize(), SqlDbType.VarBinary);
builder.Append(
$"insert into {settings.SchemaName}.{OutgoingTable} (id, owner_id, destination, deliver_by, body) values (@{id.ParameterName}, @owner, @{destination.ParameterName}, @{deliverBy.ParameterName}, @{body.ParameterName});");
}
builder.Apply();
return(cmd);
}
[Fact] // - too slow
public async Task global_session_locks()
{
var settings = new SqlServerSettings();
using (var conn1 = new SqlConnection(Servers.SqlServerConnectionString))
using (var conn2 = new SqlConnection(Servers.SqlServerConnectionString))
using (var conn3 = new SqlConnection(Servers.SqlServerConnectionString))
{
await conn1.OpenAsync();
await conn2.OpenAsync();
await conn3.OpenAsync();
await settings.GetGlobalLock(conn1, 24);
try
{
// Cannot get the lock here
(await settings.TryGetGlobalLock(conn2, 24)).ShouldBeFalse();
// Can get the new lock
(await settings.TryGetGlobalLock(conn3, 25)).ShouldBeTrue();
// Cannot get the lock here
(await settings.TryGetGlobalLock(conn2, 25)).ShouldBeFalse();
}
finally
{
await settings.ReleaseGlobalLock(conn1, 24);
await settings.ReleaseGlobalLock(conn3, 25);
}
}
}
public SqlServerEnvelopePersistor(SqlServerSettings settings)
{
_settings = settings;
}
public DatabaseFixture()
{
settings = TestHelper.GetConfiguration().SqlServerSettings();
}
public BlogDbContext(DbContextOptions options, SqlServerSettings settings)
: base(options)
{
_settings = settings;
}
public SqlServerEnvelopePersistence(SqlServerSettings settings, JasperOptions options)
: base(settings, options, new SqlServerEnvelopeStorageAdmin(settings), new SqlServerDurabilityAgentStorage(settings, options))
{
_settings = settings;
}
/// <summary>
/// Initializes a new instance of the <see cref="ElementSqlServerDapperRepository"/> class.
/// </summary>
/// <param name="logger">The logger.</param>
/// <param name="sqlServerSettings">The sql server settings.</param>
public ElementSqlServerDapperRepository(ILogger <ElementSqlServerDapperRepository> logger, SqlServerSettings sqlServerSettings)
{
this.logger = logger;
this.sqlServerSettings = sqlServerSettings;
}
public BulkArticlesTest(DatabaseFixture fixture)
{
settings = TestHelper.GetConfiguration().SqlServerSettings();
articles = new Articles(settings);
}
public SqlServerEnvelopePersistence(SqlServerSettings databaseSettings, AdvancedSettings settings)
: base(databaseSettings, settings, new SqlServerEnvelopeStorageAdmin(databaseSettings), new SqlServerDurabilityAgentStorage(databaseSettings, settings))
{
_databaseSettings = databaseSettings;
}
