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
}

Ten 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

There is no ANSI row limit, so StdAdoDelegate applies none and a dialect that can limit rows says where its clause goes. Two statements carry one — trigger acquisition and the misfire scan — and both ask the same member, so a dialect says it once:

// … LIMIT n (PostgreSQL, MySQL, SQLite) — or "ROWS" on Firebird
protected override SqlRowLimit GetRowLimit(int count)
    => SqlRowLimit.AtStatementEnd("LIMIT", count);

// SELECT TOP n …                              SqlRowLimit.InProjection("TOP", count)
// SELECT * FROM ( … ) WHERE rownum <= n       SqlRowLimit.InEnclosingSelect("rownum", count)

SqlRowLimit names the three places a limit can sit, and the statement is built with the clause already in it. Nothing is spliced into finished SQL, so a dialect no longer depends on the statement starting with a particular keyword or ending with a particular clause. count is always at least one: the -1 that means "every row" is turned into SqlRowLimit.Unlimited before the member is called, so an override never has to test for it.

TriggerAcquisitionSqlShape carries everything about an acquisition attempt that changes the text of the statement — the row limit's count, and how many job-type exclusion terms the NOT IN clause needs. It is also the key the finished statement is cached under, which is why it holds the bucketed exclusion count rather than the caller's exact one. Override GetSelectNextTriggerToAcquireSql(shape) only for something a row limit cannot express; MySQL is the one shipped dialect that does, for its FORCE INDEX hint, and it still calls base and leaves the limit alone.

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.

The two parameter names are the one thing the two overrides have to agree about, so they are constants rather than literals: AdoConstants.ParameterPageSkip and AdoConstants.ParameterPageTake, spliced into the statement with an @ and bound by the bare name.

protected override string ApplyPaging(string sql, bool takeLimited)
    => takeLimited
        ? sql + " LIMIT @" + AdoConstants.ParameterPageTake + " OFFSET @" + AdoConstants.ParameterPageSkip
        : sql + " LIMIT -1 OFFSET @" + AdoConstants.ParameterPageSkip;

protected override void AddPagingParameters(DbCommand cmd, int skip, int take, bool takeLimited)
{
    if (takeLimited)
    {
        AddCommandParameter(cmd, AdoConstants.ParameterPageTake, take);
    }

    AddCommandParameter(cmd, AdoConstants.ParameterPageSkip, 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(shape) — 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.

Two of those the base class then hands back, so a delegate writing a statement of its own does not override Initialize merely to keep a second copy: protected string SchedulerName { get; }, which nearly every statement is scoped by, and protected IDbProvider DbProvider { get; }.

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.

For a delegate that needs more than the container can supply — a constructor argument only the caller has, or a property set before it is handed over — UseDriverDelegate(factory) takes the delegate you built. The factory is given a provider that resolves this scheduler's own parts, which is what registering IDriverDelegate against Services would not do: a named scheduler resolves its delegate under its own key and would never see an unkeyed registration. UseSerializer, UseLockHandler, UseConnectionProvider and UseTriggerPersistenceDelegate all take a factory the same way.

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.

Running that DDL by hand is enough for the default SchemaProvisioning.Validate. To let ProvisionSchema() create the tables instead, embed the script in your own assembly (<EmbeddedResource Include="MyDatabase.sql" />) and name it from SchemaResourceName, using the manifest resource name the compiler gives it — <RootNamespace>.<folder path>.<file name>. Statements are separated by a line reading --;; rather than by a semicolon, because a semicolon ends a statement on one dialect and a line inside a stored-procedure body on another, and {0} stands where the table prefix goes:

// The schema script this delegate creates its tables from, embedded beside it with
// <EmbeddedResource Include="MyDatabase.sql" /> and named the way the compiler names one:
// <RootNamespace>.<folder path>.<file name>. Statements are separated by a line reading "--;;".
protected override string? SchemaResourceName => "MyCompany.Quartz.MyDatabase.sql";

The name is looked for in the assembly of the delegate's own type and then in each assembly up its base chain, so a delegate that subclasses a shipped dialect to change a statement or two inherits that dialect's script without carrying a copy. Left unset, ProvisionSchema() fails with a message naming this member rather than with a SQL error.

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