EventStore 3.1 - SQL Persistence - How can I use a non-dbo schema?

Question

For the project I'm working on, I can't use the [dbo] schema. From looking at the EventStore source, it doesn't look trivial to use a non-dbo schema.

So far, the best I've come up with is to use a custom dialect like this:

Sub-class CommonSqlDialect
Add a private instance of MsSqlDialect
Then override all virtual properties of CommonSqlDialect to do something like

Example:

public override string AppendSnapshotToCommit
{
    get { return customizeSchema(_msSqlDialect.AppendSnapshotToCommit); }
}
private string customizeSchema(string dboStatement)
{
    // replace "[dbo]" with "[notdbo]", 
    // replace " Commits" with " [notdbo].Commits", 
    // replace " Snapshots" with " [notdbo].Snapshots"
}

I also have to customize the InitializeStorage property to replace "sysobjects" with "sys.objects" so I can add an additional constraint on the schema name.

This works, but it seems like there should be wireup options for customizing the schema and table names.

UsingSqlPersistence(...)
    .WithSchema(...)
    .WithCommitsTable(...)
    .WithSnapshotsTable(...)

Is there a clearly better way to handle this that I've missed?

score 3 · Accepted Answer · answered Dec 14 '11 at 02:10

While I can see a potential need to customize the table names, the existing version does not support that. All you need to do is to subclass the MsSqlDialect and then provide your custom version to the wireup like so:

UsingSqlPersistence(...)
    .WithDialect(new MsSqlDialectWithCustomTableNames());

score 1 · Answer 2 · answered Nov 14 '12 at 08:00

A solution that doesn't require any code changes, and is good for security:

Create a new user in database and give him read/write only access to the new schema.
Set the new schema as a default schema to the user.
Add a new 'EventStore' connection string to the config file.
Pass this new connection to UsingSqlPersistence constructor.

All the queries in the event store are not prefixed with schema name, so changing a default schema for the user effectively 'redirects' all the calls to the new schema.

What is more, having a specific user for event store with limited permissions is a good thing anyway. Make sure that other db users don't have access to your new schema.

EventStore 3.1 - SQL Persistence - How can I use a non-dbo schema?

2 Answers2