Support separate database for storing messages?

[jlwalkerlg]

Hi, I'm wondering if it would be possible to specify the name of the database to store published/received messages.

The reason is that I have a multi-tenant system in which each business use case may write to a different database using a different connection string, so I'd like a "master" database where I can tell CAP to write all of its messages, while using the same connection that EF uses per tenant database so I can use transactions.

For example,

await using var transaction = apiDbContext.Database.BeginTransaction(publisher, autoCommit: false);

await apiDbContext.Users.AddAsync(new User
{
    Id = Guid.NewGuid(),
    Name = nameof(MessagesController),
});

await publisher.PublishAsync(nameof(Message), new Message
{
    Value = "CAP",
});

await apiDbContext.SaveChangesAsync();
await transaction.CommitAsync();

I want apiDbContext.Users.AddAsync() to write to the Users table in the Demo.Api database, and I want CAP to store messages in the Published table of the Demo.Messages database. Both databases exist on the same server so can share a connection and use the same transaction.

I think it would just require that CAP specifies the database name in the SQL query string when it writes messages to the database.

I'm using SQL Server and got it working locally as follows.

1. I added a public string Database { get; set; } property to DotNetCore.CAP.EFOptions.
2. I changed the implementation of GetPublishedTableName() and GetReceivedTableName() in DotNetCore.CAP.SqlServer.SqlServerStorageInitializer to:

public virtual string GetPublishedTableName()
{
    return _options.Value.Database is null
        ? $"{_options.Value.Schema}.Published"
        : $"[{_options.Value.Database}].[{_options.Value.Schema}].[Published]";
}

public virtual string GetReceivedTableName()
{
    return _options.Value.Database is null
        ? $"{_options.Value.Schema}.Received"
        : $"[{_options.Value.Database}].[{_options.Value.Schema}].[Received]";
}

If the Database property is not set by the user, it defaults to null, so will be ignored and work as normal.

3. I changed the query that initializes the database in DotNetCore.CAP.SqlServer.SqlServerStorageInitializer.CreateDbTablesScript() so that it ignores the new Database property.

protected virtual string CreateDbTablesScript(string schema)
{
    var publishedTableName = $"{_options.Value.Schema}.Published";
    var receivedTableName = $"{_options.Value.Schema}.Received";

    var batchSql =
        $@"
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = '{schema}')
BEGIN
EXEC('CREATE SCHEMA [{schema}]')
END;

IF OBJECT_ID(N'{receivedTableName}',N'U') IS NULL
BEGIN
CREATE TABLE {receivedTableName}(
[Id] [bigint] NOT NULL,
[Version] [nvarchar](20) NOT NULL,
[Name] [nvarchar](200) NOT NULL,
[Group] [nvarchar](200) NULL,
[Content] [nvarchar](max) NULL,
[Retries] [int] NOT NULL,
[Added] [datetime2](7) NOT NULL,
[ExpiresAt] [datetime2](7) NULL,
[StatusName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_{receivedTableName}] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END;

IF OBJECT_ID(N'{publishedTableName}',N'U') IS NULL
BEGIN
CREATE TABLE {publishedTableName}(
[Id] [bigint] NOT NULL,
[Version] [nvarchar](20) NOT NULL,
[Name] [nvarchar](200) NOT NULL,
[Content] [nvarchar](max) NULL,
[Retries] [int] NOT NULL,
[Added] [datetime2](7) NOT NULL,
[ExpiresAt] [datetime2](7) NULL,
[StatusName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_{publishedTableName}] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END;";
    return batchSql;
}

This worked locally on my PC: I was able to write to one database with EF, and another with CAP, all in the same transaction. Would this be something you'd consider supporting? Happy to submit a PR if so.

[yang-xiaodong]

Hello,

We have a unified discussion on multi-tenant support in #699.

As you said, this approach is restrictive, all databases must be located on a single server.
The current schema configuration option looks inappropriate in terms of naming, but it can achieve the same purpose, and we have marked GetPublishedTableName and GetReceivedTableName as vitual for the purpose of easy customization