A Driver Delegate for a New Database

Quartz ships driver delegates for SQL Server, PostgreSQL, MySQL, Oracle, SQLite and Firebird. A database that is not one of those — or one of those behind a provider that behaves differently — needs a delegate of its own.

The seam

StdAdoDelegate is public and unsealed, with a public parameterless constructor:

public sealed class MyDatabaseDelegate : StdAdoDelegate
{
    // override only what differs
}

Eight members are the dialect contract. Everything else on StdAdoDelegate is an implementation step that happens to be protected virtual so the class can be composed — treat them as private.

Row limiting

The default GetSelectNextTriggerToAcquireSql ignores maxCount entirely — "by default we don't support limits, this is db specific" — so a dialect that can limit rows should say so. Four shapes appear among the shipped dialects:

// append (PostgreSQL, Firebird)
protected override string GetSelectNextTriggerToAcquireSql(int maxCount)
    => base.GetSelectNextTriggerToAcquireSql(maxCount) + " LIMIT " + maxCount;

// splice a prefix (SQL Server: SELECT TOP n)
// wrap the whole statement (Oracle: SELECT * FROM ( … ) WHERE rownum <= n)
// append with an index hint (MySQL: FORCE INDEX (…) … LIMIT n)

Paging

The default is the ANSI form, understood by SQL Server 2012+, Oracle 12c+, PostgreSQL and Firebird 3+:

 OFFSET @pageSkip ROWS FETCH NEXT @pageTake ROWS ONLY

MySQL and SQLite have no such clause, so they override both members — and they must override AddPagingParameters too, because their clause names the parameters in the other order and providers that bind positionally take them in the order the statement mentions them:

protected override string ApplyPaging(string sql, bool takeLimited)
    => takeLimited
        ? sql + " LIMIT @pageTake OFFSET @pageSkip"
        : sql + " LIMIT -1 OFFSET @pageSkip";

protected override void AddPagingParameters(DbCommand cmd, int skip, int take, bool takeLimited)
{
    if (takeLimited)
    {
        AddCommandParameter(cmd, "pageTake", take);
    }

    AddCommandParameter(cmd, "pageSkip", skip);
}

takeLimited is false when the caller asked for an unbounded page (Take = int.MaxValue), which is the case a database with no offset-only form has to spell some other way — MySQL uses a LIMIT of the largest BIGINT UNSIGNED, SQLite uses LIMIT -1.

One detail to preserve: the take the base class passes is one more than the page size. That extra row is what tells the caller whether anything follows the page, which is how PagedResult<T>.HasMore is exact without a second query.

Booleans

Oracle has no boolean column type, so its delegate maps both directions:

public override object GetDbBooleanValue(bool booleanValue) => booleanValue ? "1" : "0";

public override bool GetBooleanFromDbValue(object columnValue) => Convert.ToInt32(columnValue) == 1;

GetDbBooleanValue is what every IS_DURABLE, REQUESTS_RECOVERY and similar column is bound through, so the two must agree exactly.

Parameters

AddCommandParameter is the last resort, and SQL Server's delegate shows why it is sometimes necessary: it converts booleans to 1/0, sets size = -1 for varbinary, and pins string parameters to size = 4000 to stop the server inferring a size from the value and building a separate query plan per length.

What the delegate cannot reach

For a delegate in your own assembly this means two things:

• You cannot write StdAdoConstants.SqlSelectNextTriggerToAcquire. Derive your statement from what the base returns — base.GetSelectNextTriggerToAcquireSql(maxCount) — and transform the string, which is exactly what MySQL's .Replace("{0}TRIGGERS t", …) does. Or write the statement whole.
• You can name tables, columns, trigger types and state values: AdoConstants.TableTriggers, AdoConstants.ColumnTriggerName, AdoConstants.StateWaiting and the rest are public precisely so a dialect can build its own SQL against the schema.

{0} is the table-prefix placeholder, and protected string ReplaceTablePrefix(string query) substitutes it. Statements the base class returns still contain it; the caller substitutes.

Initialization

StdAdoDelegate.Initialize(DriverDelegateContext context) is public virtual, and a dialect normally does not override it — none of the six shipped ones do. Override it only to register extra trigger persistence delegates or to capture something from the context, and call base.Initialize(context) first.

DriverDelegateContext carries everything the delegate needs to issue statements:

It arrives after construction rather than through the constructor because InstanceId is not settled until the scheduler starts — a generated instance id does not exist when the container builds the delegate.

Registering it

builder.Services.AddQuartz(q =>
{
    q.UsePersistentStore(s =>
    {
        s.UseDriverDelegate<MyDatabaseDelegate>();
        s.UseGenericDatabase("MyProvider", connectionString);
    });
});

The delegate is constructed with ActivatorUtilities, so constructor dependencies work — take an ILogger<MyDatabaseDelegate> or anything else in the container.

The legacy quartz.jobStore.driverDelegateType key still selects a delegate by type name, and stands on its own: an application that has moved store selection into code can still name its delegate in a configuration file.

Also needed: a DbMetadata and a schema

The delegate is one of three things a new database needs:

1. The delegate — this page.
2. A provider registration. UseGenericDatabase(provider, connectionString, describeMetadata) takes a Func<DbMetadata> describing the ADO.NET provider: its connection, command and parameter types, the parameter prefix, and how it spells a DbType. The provider name and the delegate are independent axes — the Use… shortcut methods just set both at once.
3. DDL. Copy the closest database/tables/tables_<dialect>.sql and adjust the column types.

See also

• Job Stores — how the ADO store is put together
• A Job Store of Your Own — the layer above this one
• Persisting a Custom Trigger Type — the other delegate seam