A Driver Delegate for a New Database

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

The seam

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

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

These ten members are the dialect contract. Treat its other protected virtual members as private implementation steps.

Row limiting

There is no ANSI row limit, so StdAdoDelegate applies none. Trigger acquisition and the misfire scan both ask GetRowLimit:

// … 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 go; the statement is built with it, not spliced.
• count is always at least one: "every row" (-1) arrives as SqlRowLimit.Unlimited.
• TriggerAcquisitionSqlShape carries what changes the statement text (the row limit, and how many job-type exclusion terms NOT IN needs). It is also the statement's cache key, so it holds a bucketed exclusion count, not the exact one.
• Override GetSelectNextTriggerToAcquireSql(shape) only for what a row limit cannot express, as MySQL does for FORCE INDEX, still calling base and leaving the limit alone.

Paging

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

 OFFSET @pageSkip ROWS FETCH NEXT @pageTake ROWS ONLY

MySQL and SQLite override ApplyPaging and AddPagingParameters: their clause names the parameters in the other order, and positional providers bind in statement order. Use AdoConstants.ParameterPageSkip and AdoConstants.ParameterPageTake, prefixed with @ in SQL and bare when binding:

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 for an unbounded page (Take = int.MaxValue). Without an offset-only form, MySQL uses a LIMIT of the largest BIGINT UNSIGNED, SQLite LIMIT -1.
• The base passes take as the page size plus one; the extra row makes PagedResult<T>.HasMore exact.

Booleans

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

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

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

IS_DURABLE, REQUESTS_RECOVERY and similar columns bind through GetDbBooleanValue; the two must agree.

Parameters

AddCommandParameter is the last resort. SQL Server's delegate converts booleans to 1/0, sets size = -1 for varbinary, and pins strings to size = 4000 so the server does not build a query plan per string length.

What the delegate cannot reach

• You cannot write StdAdoConstants.SqlSelectNextTriggerToAcquire. Transform base.GetSelectNextTriggerToAcquireSql(shape) (as MySQL's .Replace("{0}TRIGGERS t", …) does), or write the statement whole.
• You can name the schema: AdoConstants.TableTriggers, AdoConstants.ColumnTriggerName, AdoConstants.StateWaiting and the rest.
• {0} is the table-prefix placeholder; protected string ReplaceTablePrefix(string query) substitutes it. Statements from the base still contain it; the caller substitutes.

Initialization

StdAdoDelegate.Initialize(DriverDelegateContext context) is public virtual; no shipped dialect overrides it. Override it only to register extra trigger persistence delegates or capture something from the context, and call base.Initialize(context) first.

It arrives after construction because a generated InstanceId does not exist when the container builds the delegate. The base exposes protected string SchedulerName { get; } (which scopes nearly every statement) and protected IDbProvider DbProvider { get; }, so you need no copies.

Registering it

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

• The delegate is built with ActivatorUtilities, so constructor dependencies work (an ILogger<MyDatabaseDelegate>, anything in the container).
• UseDriverDelegate(factory) takes a delegate you build. The factory's provider resolves this scheduler's own parts; an IDriverDelegate registered on Services would be invisible to a named scheduler, which resolves under its own key. UseSerializer, UseLockHandler, UseConnectionProvider and UseTriggerPersistenceDelegate take factories the same way.
• The legacy quartz.jobStore.driverDelegateType key still selects a delegate by type name, even when the store is selected in code.

Also needed: a DbMetadata and a schema

1. The delegate — this page.
2. A provider registration. UseGenericDatabase(provider, connectionString, describeMetadata) takes a Func<DbMetadata> describing the ADO.NET provider: connection, command and parameter types, parameter prefix, and DbType spelling. Provider and delegate are independent; the Use… shortcuts set both.
3. DDL. Copy the closest database/tables/tables_<dialect>.sql and adjust the column types.

Running the DDL by hand satisfies the default SchemaProvisioning.Validate. For ProvisionSchema() to create the tables:

1. Embed the script (<EmbeddedResource Include="MyDatabase.sql" />).
2. Return its manifest resource name, <RootNamespace>.<folder path>.<file name>, from SchemaResourceName.
3. Separate statements with a line reading --;; (a semicolon may be inside a stored-procedure body), and write {0} for the table prefix.

// 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 up in the delegate type's assembly, then up its base chain, so a subclass of a shipped dialect inherits its script. Unset, ProvisionSchema() fails with a message naming this member.

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