static string GetGetCommand(SqlVariant sqlVariant, string tableName)
{
switch (sqlVariant)
{
case SqlVariant.MsSqlServer:
case SqlVariant.MySql:
return($@"
select
Dispatched,
Operations
from {tableName}
where MessageId = @MessageId");
case SqlVariant.Oracle:
return($@"
select
Dispatched,
Operations
from ""{tableName}""
where MessageId = :MessageId");
default:
throw new Exception($"Unknown SqlVariant: {sqlVariant}");
}
}
public static SubscriptionCommands Build(SqlVariant sqlVariant, string tablePrefix, string schema)
{
string tableName;
switch (sqlVariant)
{
case SqlVariant.MsSqlServer:
tableName = $"[{schema}].[{tablePrefix}SubscriptionData]";
break;
case SqlVariant.MySql:
tableName = $"`{tablePrefix}SubscriptionData`";
break;
case SqlVariant.Oracle:
tableName = $"{tablePrefix.ToUpper()}SS";
break;
default:
throw new Exception($"Unknown SqlVariant: {sqlVariant}.");
}
var subscribeCommand = GetSubscribeCommand(sqlVariant, tableName);
var unsubscribeCommand = GetUnsubscribeCommand(sqlVariant, tableName);
var getSubscribers = GetSubscribersFunc(sqlVariant, tableName);
return(new SubscriptionCommands(
subscribe: subscribeCommand,
unsubscribe: unsubscribeCommand,
getSubscribers: getSubscribers));
}
public static string FindScriptDirectory(SqlVariant sqlVariant)
{
var codeBase = Assembly.GetExecutingAssembly().CodeBase;
var currentDirectory = Directory.GetParent(new Uri(codeBase).LocalPath).FullName;
return(Path.Combine(currentDirectory, "NServiceBus.Persistence.Sql", sqlVariant.ToString()));
}
static string GetCleanupCommand(SqlVariant sqlVariant, string tableName)
{
switch (sqlVariant)
{
case SqlVariant.MsSqlServer:
return($@"
delete top (@BatchSize) from {tableName}
where Dispatched = 'true'
and DispatchedAt < @DispatchedBefore");
case SqlVariant.MySql:
return($@"
delete from {tableName}
where Dispatched = true
and DispatchedAt < @DispatchedBefore
limit @BatchSize");
case SqlVariant.Oracle:
return($@"
delete from ""{tableName}""
where Dispatched = 1
and DispatchedAt < :DispatchedBefore
and rownum <= :BatchSize");
default:
throw new Exception($"Unknown SqlVariant: {sqlVariant}");
}
}
static string GetSetAsDispatchedCommand(SqlVariant sqlVariant, string tableName)
{
switch (sqlVariant)
{
case SqlVariant.MsSqlServer:
case SqlVariant.MySql:
return($@"
update {tableName}
set
Dispatched = 1,
DispatchedAt = @DispatchedAt,
Operations = '[]'
where MessageId = @MessageId");
case SqlVariant.Oracle:
return($@"
update ""{tableName}""
set
Dispatched = 1,
DispatchedAt = :DispatchedAt,
Operations = '[]'
where MessageId = :MessageId");
default:
throw new Exception($"Unknown SqlVariant: {sqlVariant}");
}
}
static Func <List <MessageType>, string> GetSubscribersFunc(SqlVariant sqlVariant, string tableName)
{
switch (sqlVariant)
{
case SqlVariant.Oracle:
var getSubscribersPrefixOracle = $@"
select distinct Subscriber, Endpoint
from ""{tableName}""
where MessageType in (";
return(messageTypes =>
{
var builder = new StringBuilder(getSubscribersPrefixOracle);
for (var i = 0; i < messageTypes.Count; i++)
{
var paramName = $":type{i}";
builder.Append(paramName);
if (i < messageTypes.Count - 1)
{
builder.Append(", ");
}
}
builder.Append(")");
return builder.ToString();
});
default:
var getSubscribersPrefix = $@"
select distinct Subscriber, Endpoint
from {tableName}
where MessageType in (";
return(messageTypes =>
{
var builder = new StringBuilder(getSubscribersPrefix);
for (var i = 0; i < messageTypes.Count; i++)
{
var paramName = $"@type{i}";
builder.Append(paramName);
if (i < messageTypes.Count - 1)
{
builder.Append(", ");
}
}
builder.Append(")");
return builder.ToString();
});
}
}
public static void ValidateTableSettings(SqlVariant variant, string tablePrefix, string schema)
{
if (variant == SqlVariant.Oracle)
{
if (tablePrefix.Length > 25)
{
throw new Exception($"Table prefix '{tablePrefix}' contains more than 25 characters, which is not supported by SQL persistence using Oracle. Shorten the endpoint name or specify a custom tablePrefix using endpointConfiguration.{nameof(SqlPersistenceConfig.TablePrefix)}(tablePrefix).");
}
if (Encoding.UTF8.GetBytes(tablePrefix).Length != tablePrefix.Length)
{
throw new Exception($"Table prefix '{tablePrefix}' contains non-ASCII characters, which is not supported by SQL persistence using Oracle. Change the endpoint name or specify a custom tablePrefix using endpointConfiguration.{nameof(SqlPersistenceConfig.TablePrefix)}(tablePrefix).");
}
}
}
public static TimeoutCommands Build(SqlVariant sqlVariant, string tablePrefix, string schema)
{
switch (sqlVariant)
{
case SqlVariant.MySql:
return(BuildMySqlCommands($"`{tablePrefix}TimeoutData`"));
case SqlVariant.MsSqlServer:
return(BuildSqlServerCommands($"[{schema}].[{tablePrefix}TimeoutData]"));
case SqlVariant.Oracle:
return(BuildOracleCommands($"{tablePrefix.ToUpper()}TO"));
default:
throw new Exception($"Unknown SqlVariant: {sqlVariant}.");
}
}
public static BuildSqlVariant Convert(this SqlVariant sqlVariant)
{
switch (sqlVariant)
{
case SqlVariant.MsSqlServer:
return(BuildSqlVariant.MsSqlServer);
case SqlVariant.MySql:
return(BuildSqlVariant.MySql);
case SqlVariant.Oracle:
return(BuildSqlVariant.Oracle);
default:
throw new Exception($"Unknown SqlVariant: {sqlVariant}.");
}
}
private static DbParameter[] ConvertParameters(object[] input, SqlVariant databaseType)
{
DbParameter[] output = new DbParameter[input.Length];
for (int i = 0; i < input.Length; i++)
{
SqlDalParameter p = (SqlDalParameter)input[i];
if (databaseType == SqlVariant.SqlServerCompact)
{
output[i] = p.ToSqlCeParameter();
}
else
{
output[i] = p.ToSqlParameter();
}
}
return(output);
}
static string GetUnsubscribeCommand(SqlVariant sqlVariant, string tableName)
{
switch (sqlVariant)
{
case SqlVariant.Oracle:
return($@"
delete from ""{tableName}""
where
Subscriber = :Subscriber and
MessageType = :MessageType");
default:
return($@"
delete from {tableName}
where
Subscriber = @Subscriber and
MessageType = @MessageType");
}
}
static string GetStoreCommand(SqlVariant sqlVariant, string tableName)
{
switch (sqlVariant)
{
case SqlVariant.MsSqlServer:
case SqlVariant.MySql:
return($@"
insert into {tableName}
(
MessageId,
Operations,
PersistenceVersion
)
values
(
@MessageId,
@Operations,
@PersistenceVersion
)");
case SqlVariant.Oracle:
return($@"
insert into ""{tableName}""
(
MessageId,
Operations,
PersistenceVersion
)
values
(
:MessageId,
:Operations,
:PersistenceVersion
)");
default:
throw new Exception($"Unknown SqlVariant: {sqlVariant}");
}
}
public SagaInfoCache(
RetrieveVersionSpecificJsonSettings versionSpecificSettings,
JsonSerializer jsonSerializer,
Func <TextReader, JsonReader> readerCreator,
Func <TextWriter, JsonWriter> writerCreator,
SagaCommandBuilder commandBuilder,
string tablePrefix,
string schema,
SqlVariant sqlVariant,
SagaMetadataCollection metadataCollection,
Func <string, string> nameFilter)
{
this.versionSpecificSettings = versionSpecificSettings;
this.writerCreator = writerCreator;
this.readerCreator = readerCreator;
this.jsonSerializer = jsonSerializer;
this.commandBuilder = commandBuilder;
this.tablePrefix = tablePrefix;
this.schema = schema;
this.sqlVariant = sqlVariant;
this.nameFilter = nameFilter;
Initialize(metadataCollection);
}
public static OutboxCommands Build(string tablePrefix, string schema, SqlVariant sqlVariant)
{
string tableName;
switch (sqlVariant)
{
case SqlVariant.MsSqlServer:
tableName = $"[{schema}].[{tablePrefix}OutboxData]";
break;
case SqlVariant.MySql:
tableName = $"`{tablePrefix}OutboxData`";
break;
case SqlVariant.Oracle:
tableName = $"{tablePrefix.ToUpper()}OD";
break;
default:
throw new Exception($"Unknown SqlVariant: {sqlVariant}");
}
var storeCommandText = GetStoreCommand(sqlVariant, tableName);
var cleanupCommand = GetCleanupCommand(sqlVariant, tableName);
var getCommandText = GetGetCommand(sqlVariant, tableName);
var setAsDispatchedCommand = GetSetAsDispatchedCommand(sqlVariant, tableName);
return(new OutboxCommands(
store: storeCommandText,
get: getCommandText,
setAsDispatched: setAsDispatchedCommand,
cleanup: cleanupCommand));
}
private static void AddParameters(DbCommand command, object[] parameters, SqlVariant databaseType)
{
if (parameters.Length < 1)
{
return;
}
// 3 valid cases: SP + Params, SP + Values, Text + Params
if (parameters.Where(p => p is SqlDalParameter).Count() == parameters.Length)
{
//for SP + Params & Text + Params
command.Parameters.AddRange(ConvertParameters(parameters, databaseType));
}
else if (command.CommandType == CommandType.StoredProcedure && databaseType != SqlVariant.SqlServerCompact)
{
SqlCommand cmd = command as SqlCommand;
if (cmd != null)
{
SqlCommandBuilder.DeriveParameters(cmd);
}
int index = 0;
foreach (SqlParameter parameter in command.Parameters)
{
if (parameter.Direction == ParameterDirection.Input ||
parameter.Direction == ParameterDirection.InputOutput)
{
parameter.Value = parameters[index++];
}
}
}
else
{
// Text + Values is not valid
throw new ArgumentException("Could not add query parameters. For text queries, each parameter must be of type SqlDalParameter.");
}
}
public virtual void Visit(SqlVariant node)
{
VisitInternal(node.Alternative);
VisitInternal(node.Main);
}
public OutboxPersister(Func <DbConnection> connectionBuilder, string tablePrefix, string schema, SqlVariant sqlVariant, int cleanupBatchSize = 10000)
{
this.connectionBuilder = connectionBuilder;
this.cleanupBatchSize = cleanupBatchSize;
outboxCommands = OutboxCommandBuilder.Build(tablePrefix, schema, sqlVariant);
commandBuilder = new CommandBuilder(sqlVariant);
}
public RuntimeSagaInfo(
SagaCommandBuilder commandBuilder,
Type sagaDataType,
RetrieveVersionSpecificJsonSettings versionSpecificSettings,
Type sagaType,
JsonSerializer jsonSerializer,
Func <TextReader, JsonReader> readerCreator,
Func <TextWriter, JsonWriter> writerCreator,
string tablePrefix,
string schema,
SqlVariant sqlVariant,
Func <string, string> nameFilter)
{
this.sagaDataType = sagaDataType;
if (versionSpecificSettings != null)
{
deserializers = new ConcurrentDictionary <Version, JsonSerializer>();
}
this.versionSpecificSettings = versionSpecificSettings;
SagaType = sagaType;
this.jsonSerializer = jsonSerializer;
this.readerCreator = readerCreator;
this.writerCreator = writerCreator;
this.commandBuilder = new CommandBuilder(sqlVariant);
CurrentVersion = sagaDataType.Assembly.GetFileVersion();
ValidateIsSqlSaga();
var sqlSagaAttributeData = SqlSagaTypeDataReader.GetTypeData(sagaType);
var tableSuffix = nameFilter(sqlSagaAttributeData.TableSuffix);
switch (sqlVariant)
{
case SqlVariant.MsSqlServer:
TableName = $"[{schema}].[{tablePrefix}{tableSuffix}]";
FillParameter = ParameterFiller.Fill;
break;
case SqlVariant.MySql:
TableName = $"`{tablePrefix}{tableSuffix}`";
FillParameter = ParameterFiller.Fill;
break;
case SqlVariant.Oracle:
if (tableSuffix.Length > 27)
{
throw new Exception($"Saga '{tableSuffix}' contains more than 27 characters, which is not supported by SQL persistence using Oracle. Either disable Oracle script generation using the SqlPersistenceSettings assembly attribute, shorten the name of the saga, or specify an alternate table name by overriding the SqlSaga's TableSuffix property.");
}
if (Encoding.UTF8.GetBytes(tableSuffix).Length != tableSuffix.Length)
{
throw new Exception($"Saga '{tableSuffix}' contains non-ASCII characters, which is not supported by SQL persistence using Oracle. Either disable Oracle script generation using the SqlPersistenceSettings assembly attribute, change the name of the saga, or specify an alternate table name by overriding the SqlSaga's TableSuffix property.");
}
TableName = tableSuffix.ToUpper();
FillParameter = ParameterFiller.OracleFill;
break;
default:
throw new Exception($"Unknown SqlVariant: {sqlVariant}.");
}
CompleteCommand = commandBuilder.BuildCompleteCommand(TableName);
SelectFromCommand = commandBuilder.BuildSelectFromCommand(TableName);
GetBySagaIdCommand = commandBuilder.BuildGetBySagaIdCommand(TableName);
SaveCommand = commandBuilder.BuildSaveCommand(sqlSagaAttributeData.CorrelationProperty, sqlSagaAttributeData.TransitionalCorrelationProperty, TableName);
UpdateCommand = commandBuilder.BuildUpdateCommand(sqlSagaAttributeData.TransitionalCorrelationProperty, TableName);
CorrelationProperty = sqlSagaAttributeData.CorrelationProperty;
HasCorrelationProperty = CorrelationProperty != null;
if (HasCorrelationProperty)
{
GetByCorrelationPropertyCommand = commandBuilder.BuildGetByPropertyCommand(sqlSagaAttributeData.CorrelationProperty, TableName);
}
TransitionalCorrelationProperty = sqlSagaAttributeData.TransitionalCorrelationProperty;
HasTransitionalCorrelationProperty = TransitionalCorrelationProperty != null;
if (HasTransitionalCorrelationProperty)
{
TransitionalAccessor = sagaDataType.GetPropertyAccessor <IContainSagaData>(TransitionalCorrelationProperty);
}
}
public SagaPersister(SagaInfoCache sagaInfoCache, SqlVariant sqlVariant)
{
this.sagaInfoCache = sagaInfoCache;
commandBuilder = new CommandBuilder(sqlVariant);
}
Task InstallSubscriptions(string scriptDirectory, DbConnection connection, DbTransaction transaction, string tablePrefix, string schema, SqlVariant sqlVariant)
{
if (!settings.ShouldInstall <StorageType.Subscriptions>())
{
return(Task.FromResult(0));
}
var createScript = Path.Combine(scriptDirectory, "Subscription_Create.sql");
ScriptLocation.ValidateScriptExists(createScript);
log.Info($"Executing '{createScript}'");
if (sqlVariant == SqlVariant.Oracle)
{
return(connection.ExecuteTableCommand(
transaction: transaction,
script: File.ReadAllText(createScript),
tablePrefix: tablePrefix));
}
else
{
return(connection.ExecuteTableCommand(
transaction: transaction,
script: File.ReadAllText(createScript),
tablePrefix: tablePrefix,
schema: schema));
}
}
Task InstallSagas(string scriptDirectory, DbConnection connection, DbTransaction transaction, string tablePrefix, string schema, SqlVariant sqlVariant)
{
if (!settings.ShouldInstall <StorageType.Sagas>())
{
return(Task.FromResult(0));
}
var sagasDirectory = Path.Combine(scriptDirectory, "Sagas");
if (!Directory.Exists(sagasDirectory))
{
log.Info($"Diretory '{sagasDirectory}' not found so no saga creation scripts will be executed.");
return(Task.FromResult(0));
}
var scriptFiles = Directory.EnumerateFiles(sagasDirectory, "*_Create.sql").ToList();
log.Info($@"Executing saga creation scripts:
{string.Join(Environment.NewLine, scriptFiles)}");
var sagaScripts = scriptFiles
.Select(File.ReadAllText);
if (sqlVariant == SqlVariant.Oracle)
{
return(connection.ExecuteTableCommand(
transaction: transaction,
scripts: sagaScripts));
}
else
{
return(connection.ExecuteTableCommand(
transaction: transaction,
scripts: sagaScripts,
tablePrefix: tablePrefix,
schema: schema));
}
}
public SagaCommandBuilder(SqlVariant sqlVariant)
{
this.sqlVariant = sqlVariant;
}
static string GetSubscribeCommand(SqlVariant sqlVariant, string tableName)
{
switch (sqlVariant)
{
case SqlVariant.MsSqlServer:
return($@"
merge {tableName} with (holdlock) as target
using(select @Endpoint as Endpoint, @Subscriber as Subscriber, @MessageType as MessageType) as source
on target.Subscriber = source.Subscriber and
target.MessageType = source.MessageType and
((target.Endpoint = source.Endpoint) or (target.Endpoint is null and source.endpoint is null))
when not matched then
insert
(
Subscriber,
MessageType,
Endpoint,
PersistenceVersion
)
values
(
@Subscriber,
@MessageType,
@Endpoint,
@PersistenceVersion
);");
case SqlVariant.MySql:
return($@"
insert into {tableName}
(
Subscriber,
MessageType,
Endpoint,
PersistenceVersion
)
values
(
@Subscriber,
@MessageType,
@Endpoint,
@PersistenceVersion
)
on duplicate key update
Endpoint = @Endpoint,
PersistenceVersion = @PersistenceVersion
");
case SqlVariant.Oracle:
return($@"
begin
insert into ""{tableName}""
(
MessageType,
Subscriber,
Endpoint,
PersistenceVersion
)
values
(
:MessageType,
:Subscriber,
:Endpoint,
:PersistenceVersion
);
commit;
exception
when DUP_VAL_ON_INDEX
then ROLLBACK;
end;
");
default:
throw new Exception($"Unknown SqlVariant: {sqlVariant}.");
}
}
public SubscriptionPersister(Func <DbConnection> connectionBuilder, string tablePrefix, SqlVariant sqlVariant, string schema, TimeSpan?cacheFor)
{
this.connectionBuilder = connectionBuilder;
this.cacheFor = cacheFor;
subscriptionCommands = SubscriptionCommandBuilder.Build(sqlVariant, tablePrefix, schema);
commandBuilder = new CommandBuilder(sqlVariant);
if (cacheFor != null)
{
Cache = new ConcurrentDictionary <string, CacheItem>();
}
}
/// <summary>
/// Creates a new instance of SqlServerCoconutDal, using the supplied connection string
/// </summary>
/// <param name="doNotUseAppConfig">Indicates that app config should be ignored</param>
/// <param name="connectionString">A sql server connection string</param>
public SqlServerCoconutDal(ConfigurationBehaviour doNotUseAppConfig, string connectionString)
{
this.Connection = connectionString;
this._sqlDatabaseType = SqlVariant.SqlServer;
}
/// <summary>
/// Creates a new instance of SqlServerCoconutDal, using the active connection from the CoconutDalConfigurationSection in app.config
/// </summary>
public SqlServerCoconutDal()
{
this.Connection = CoconutDalConfigurationSection.GetEnvironmentConfig().ConnectionString;
this._sqlDatabaseType = SqlVariant.SqlServer;
}
/// <summary>
/// Sets the <see cref="SqlVariant"/> to use for communicating the the current database.
/// </summary>
public static void SqlVariant(this PersistenceExtensions <SqlPersistence> configuration, SqlVariant sqlVariant)
{
Guard.AgainstNull(nameof(configuration), configuration);
var settings = configuration.GetSettings();
settings.Set("SqlPersistence.SqlVariant", sqlVariant);
}
public TimeoutPersister(Func <DbConnection> connectionBuilder, string tablePrefix, SqlVariant sqlVariant, string schema, TimeSpan timeoutsCleanupExecutionInterval)
{
this.connectionBuilder = connectionBuilder;
this.timeoutsCleanupExecutionInterval = timeoutsCleanupExecutionInterval;
timeoutCommands = TimeoutCommandBuilder.Build(sqlVariant, tablePrefix, schema);
commandBuilder = new CommandBuilder(sqlVariant);
switch (sqlVariant)
{
case SqlVariant.MsSqlServer:
oldestSupportedTimeout = SqlDateTime.MinValue.Value;
break;
case SqlVariant.Oracle:
case SqlVariant.MySql:
oldestSupportedTimeout = new DateTime(1000, 1, 1);
break;
default:
throw new NotSupportedException("Not supported SQL dialect: " + sqlVariant);
}
}
static void Write(string testDirectory, SqlVariant variant, string suffix, string script)
{
Write(testDirectory, suffix, script, variant.ToString());
}
public void Visit(SqlVariant node)
{
}
