Implement option to configure custom table names for Hangfire SQL structures

[m-wilczynski]

Idea

I'd like to propose implementing way for configuring custom table names that Hangfire uses in underlying SQL Server storage.

How do I see it

SqlServerStorageOptions should make possible to define key-value map of initial Hangfire structure names to custom structure name, for ex:

private IDictionary<string, string> _customTableNames = new Dictionary<string, string>
{
    { "Job", "hangfire-job" },
    { "Hash", "hangfire-hash" },
    { "Set", "hangfire-set" }
    //And rest of the tables defined here
}

And expose them somehow with fallback provided, ie:

public string GetTableNameFor(string hangfireTable)
{
    if (_customTableNames.Contains(hangfireTable)
    {
        return _customTableNames(hangfireTable);
    }
    return hangfireTable;
}

This will make possible for enterprise environments to use Hangfire seamlessly with their table-naming conventions included.

I would gladly code this in my spare time, as I cannot do this during my office hours.

Any further thoughts?

[m-wilczynski]

Further thought - for not breaking automatic table scaffolding, I would recommend allowing it only for:

new SqlServerStorageOptions
{
    PrepareSchemaIfNecessary = false
}

[burningice2866]

Assigning its own schema to the Hangfire db user will prevent any such issues with clashing table names. Is there any reason why you can't have the Hangfire table in its own distinct schema?

[m-wilczynski]

It's not an issue with conflicting table names (and I did not said so anywhere 🙆‍♂️ ).

My point is - not every company uses app-layer-driven naming conventions for tables (for ex. PascalCase in .NET world), but rather would choose naming conventions that suit their workflow. I can't see a reason why I should make exceptions for Hangfire structures. I'd rather have them comply to our style rules, rather than forcing ourselves to break (or bend) them to for this particular use case.

[burningice2866]

@m-wilczynski my bad, i just assumed that was the issue since IMHO i would be the only reason that would call for such a feature.

[m-wilczynski]

I've come to the point (see last commit) when I'm left with replacing all table names. Now it came to my mind that - possibly - it would be better to offer full configuration of both table names and table columns.

It would be then possible, to simply acquire custom table config via:

var jobTableDefinition = _options.CustomTableNames["Job"]; //perhaps CustomTables would be now more appropriate name

and custom table's column name via:

var jobIdColumnName = _options.CustomTableNames["Job"]["JobId"];

Any thoughts on this are welcome. 😄

[jtrout]

Hey Michal,

We've run into this exact same scenario. In our case, we're integrating with an off-the-shelf package that also has a table named Counter in the dbo schema. At one point in their code, they're querying INFORMATION_SCHEMA without specifying the schema, and all hell breaks lose because they're getting back information about their own dbo.Counter table, but also the hangfire.Counter table. We'll probably work around it by putting Hangfire in it's own database, but that's unfortunate, because your solution would help us elegantly work around this code limitation in the other package.

What's stopping you from PR'ing your code, might I ask?

Thanks for the work!

[m-wilczynski]

Hey Justin,

All what's left is replacing hardcoded table names with access to custom tables config, that is:

var jobTableDefinition = _options.CustomTableNames["Job"];

If I recall correctly, I've stopped on this because I wanted to implement an "all-in" configuration with ability to change column names too (see my last comment). Possibly, I could go with table names approach for initial PR for now and expand the solution to column names in another PR. Also, there's a need for revising SQL files and how to tackle names replacement there (but my memory could be rusty here, maybe that's not a big issue). As my current employer keeps on investing in using Hangfire in production, I'd gladly finish the job if any of the PR reviewers let me know if I'm not missing a thing in current approach.

Any insight guys: @pieceofsummer @odinserj ?