Extend OutboxProcessingOptions to include optional ISqlTransactionalOutboxTableConfig Update SQLRepository operations to pass as optional

[jkears]

This is working great for our needs, however we now need to merge more than one service, each with separate schemas but in the same DB into a single process.

Currently for as many merged services, each would run the following code setup code, however this only has a single shared instance thus when running with multiple Outboxes in same DB but each with different Schema, the Repository Data operations allways resolve to the state last initialized. For example, this is one of possible many configurations ... 

namespace NextWare.CoreServices.Cicd.EnvironmentServices.Application.DependencyInjection
{
    public static class TransactionalOutBoxConfiguration
    {
        public static IServiceCollection AddTransactionalOutbox(this IServiceCollection serviceCollection, SecretClient secretClient)
        {
            var lockAcquisitionTimeoutSecondsSecret = secretClient.GetSecret("TransactionalOutBox-LockAcquisitionTimeoutSeconds");
            //This is the global SqlTransactionalOutbox initializer that allows configuring custom settings to be used...
            //NOTE: Not all values need to be specified, any values that are not specified (e.g. or are set to null) will retain the default values.
            SqlTransactionalOutboxInitializer.Configure(config =>
            {
                config.WithOutboxTableConfig(
                    new OutboxTableConfig(transactionalOutboxSchemaName: "NextWare_CoreServices_Cicd_EnvironmentServices", transactionalOutboxTableName: "TransactionalOutBoxQueue", pkeyFieldName: "Id", payloadFieldName: "Payload", uniqueIdentifierFieldName: "UniqueIdentifier", fifoGroupingIdentifier: "FifoGroupingIdentifier", statusFieldName: "Status", publishTargetFieldName: "PublishTarget", publishAttemptsFieldName: "PublishAttempts", createdDateTimeUtcFieldName: "CreatedDateTimeUtc")
                    ).WithDistributedMutexLockSettings(lockAcquisitionTimeoutSeconds: int.Parse(lockAcquisitionTimeoutSecondsSecret.Value.Value), lockNamePrefix: "NextWare_CoreServices_Cicd_EnvironmentServices_OutboxDistributedLock::");
            });
            return serviceCollection;
        }
    }
}

Even as a merged product, there is still a dedicated background task per outbox (unique schema), that runs the code below to push events to the ESB and as such I would somehow need to override the default OutBoxTableConfig, so that I am hitting the correct schema (in same DB), and not that last that was initialized as shown above.

I was thinking perhaps to extend OutboxProcessingOptions to include an optional ISqlTransactionalOutboxTableConfig? that can be set prior to invocation of ProcessPendingOutboxItemsAsync , and if set, the new logic would replace the default Outbox when accessing Outbox table.

Existing :

 await using var sqlConnection = new Npgsql.NpgsqlConnection(SqlConnectionString);
                await sqlConnection.OpenAsync().ConfigureAwait(false);
                await sqlConnection.ProcessPendingOutboxItemsAsync(OutboxPublisher, SqlTransactionalOutboxProcessingOptions).ConfigureAwait(false);

**Proposed : adds SqlOutbox property to OutboxProcessingOptions as an optional **

SqlTransactionalOutboxProcessingOptions.SqlOutbox  = new OutboxTableConfig(transactionalOutboxSchemaName: "NextWare_CoreServices_Cicd_EnvironmentServices", transactionalOutboxTableName: "TransactionalOutBoxQueue", pkeyFieldName: "Id", payloadFieldName: "Payload", uniqueIdentifierFieldName: "UniqueIdentifier", fifoGroupingIdentifier: "FifoGroupingIdentifier", statusFieldName: "Status", publishTargetFieldName: "PublishTarget", publishAttemptsFieldName: "PublishAttempts", createdDateTimeUtcFieldName: "CreatedDateTimeUtc");
 await using var sqlConnection = new Npgsql.NpgsqlConnection(SqlConnectionString);
                await sqlConnection.OpenAsync().ConfigureAwait(false);
                await sqlConnection.ProcessPendingOutboxItemsAsync(OutboxPublisher, SqlTransactionalOutboxProcessingOptions).ConfigureAwait(false);

Or is there a better way?

[cajuncoding]

@jkears sorry for the delayed response.... been traveling and things for the holidays :-).

You do have a use case that I had not envisioned before, but I can see the value in what you are doing 👍. And overall your thoughts are very reasonable and will be a good approach....

I was thinking perhaps to extend OutboxProcessingOptions to include an optional ISqlTransactionalOutboxTableConfig

Though, in thinking on it a little more -- along with the the value of configuration being encapsulated in the application root -- perhaps a feature to pre-configure your Tables in the app root with Keyed configurations (along with everything else) would be helpful; and then only have to pass in the table config key/identifier could greatly simplify the consumption in other places. I can see that both approaches could be used and may be helpful so I think that might be the most flexible thing to offer both.

1. Support passing in a Table Configuration as you outlined as priority Initial development BETA release merge into Main #1.
2. Support passing in a Table Configuration Key (which may be a string, Enum, or any other class that returns a valid unique string) as priority Feature/migrate to azure messaging servicebus library #2.
3. If neither of these are provided then fallback to the global default as the final fallback.

This would enable quite a bit of flexibility I think 🙂, thoughts?