NetCore集成Quartz使用自定义数据库连接

目前项目上使用的NetCore SDK版本为2.1.401，Quartz的nuget版本为3.0.7。简单的集成不再赘述，本次主要记录在开发过程中使用AdoJobStore和国产DM数据库的支持。

如何使用自定义数据库

使用StdSchedulerFactory新建调度实例：

                // 获取调度服务器配置
                NameValueCollection props = GSPScheduleConfig.GetScheduleConfig();

                // 从工厂中获取调度程序实例
                ISchedulerFactory sf = new StdSchedulerFactory(props);
                scheduler = await sf.GetScheduler();

其中Quartz的相关配置构造如下：
此处着重说明两个方面，首先是Quartz的基本配置，包括serializer.type、jobStore.type、threadPool.threadCount等，这些可以参考https://www.quartz-scheduler.net/。其次是数据库的连接构造，由于项目上目前只使用了PG、Oracle、SqlServer、DM，暂时先列举这么多，注意每种数据库的连接串组织以及driverDelegateType（这些其实是验证最久的）。详细的可以参考Git源码https://github.com/quartznet/quartznet

        /// <summary>
        /// 获取调度器配置
        /// </summary>
        /// <returns>调度器配置</returns>
        public static NameValueCollection GetScheduleConfig()
        {
            NameValueCollection props = new NameValueCollection
                {
                    { "quartz.serializer.type", GetConfigValue("serializer.type", "binary") },
                    { "quartz.jobStore.dataSource", GetConfigValue("jobStore.dataSource", "default") },
                    { "quartz.jobStore.type", GetConfigValue("jobStore.type", "Quartz.Impl.AdoJobStore.JobStoreTX, Quartz") },
                    { "quartz.threadPool.type", GetConfigValue("threadPool.type", "Quartz.Simpl.SimpleThreadPool, Quartz") },
                    { "quartz.threadPool.threadCount", GetConfigValue("threadPool.threadCount", "30") },
                    { "quartz.jobStore.misfireThreshold", GetConfigValue("jobStore.misfireThreshold", "60000") },
                    { "quartz.jobStore.clustered", GetConfigValue("jobStore.clustered", "true") },
                    { "quartz.jobStore.clusterCheckinInterval", GetConfigValue("jobStore.clusterCheckinInterval", "1000") }
                };
            var dbConfig = InitDBConfig();
            props.Add(dbConfig);
            return props;          
        }

        /// <summary>
        /// 根据类型构造数据库配置
        /// </summary>
        /// <param name="dbType"></param>
        /// <param name="str"></param>
        /// <returns></returns>
        private static NameValueCollection InitDBConfig()
        {
            var tenantId = GetDefaultTenantId();
            var connection = GetGSPDbConfig(tenantId, "pg01", "sys");//先默认获取sys作为调度库

            var server = connection.Source.Split(":")[0];
            var port = "";
            var database = connection.Catalog;
            var userId = connection.UserId;
            var pwb = connection.Password;

            if (connection.DbType == GSPDbType.Oracle)
            {
                port = connection.Source.Split(":")[1].Split("/")[0];
                database = connection.Source.Split(":")[1].Split("/")[1];              
            }
            else
            {
                if (connection.Source.Contains(":"))
                {
                    port = connection.Source.Split(":")[1];
                }
                else
                {
                    port = "1433";
                }
            }
            var dbConnection = "";
            var driverDelegateType = "";
            var provider = "";
            switch (connection.DbType.ToString())
            {
                case "SQLServer":
                    dbConnection = $"Server={server + ',' + port};Database={database};User Id={userId};Password={pwb};";
                    driverDelegateType = "Quartz.Impl.AdoJobStore.SqlServerDelegate, Quartz";
                    provider = "SqlServer";
                    break;
                case "Oracle":
                    dbConnection = $"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={server})(PORT={port})))(CONNECT_DATA=(SERVICE_NAME={database})));User Id={userId};Password={pwb};";
                    driverDelegateType = "Quartz.Impl.AdoJobStore.OracleDelegate, Quartz";
                    provider = "OracleODPManaged";
                    break;
                case "PgSQL":
                    dbConnection = $"server={server};port={port};database={database};user id={userId};pwd={pwb};";
                    driverDelegateType = "Quartz.Impl.AdoJobStore.PostgreSQLDelegate, Quartz";
                    provider = "Npgsql";
                    break;
                case "DM":
                    dbConnection = $"SERVER={server };PORT={port};USER Id={userId};SCHEMA={database};PASSWORD={pwb};";
                    driverDelegateType = "Inspur.Gsp.Sys.Scheduler.ExecuteEngine.DMDelegate, Inspur.Gsp.Sys.Scheduler.ExecuteEngine";
                    provider = "DM";
                    break;
            }
            NameValueCollection collection = new NameValueCollection
            {
                { "quartz.dataSource.default.connectionString", dbConnection },
                { "quartz.jobStore.driverDelegateType", driverDelegateType },
                { "quartz.dataSource.default.provider", provider }
            };
            return collection;
        }

通过上述代码中可以留意到，DM的driverDelegateType是自己扩展的，需要继承StdAdoDelegate，然后重写其中的语法差异部分。具体代码如下：

using Quartz.Impl.AdoJobStore;
using System;
using System.Collections.Generic;
using System.Text;

namespace Inspur.Gsp.Sys.Scheduler.ExecuteEngine
{
    public class DMDelegate : StdAdoDelegate
    {
        protected override string GetSelectNextTriggerToAcquireSql(int maxCount)
        {
            return "SELECT * FROM (" + SqlSelectNextTriggerToAcquire + ") WHERE rownum <= " + maxCount;
        }

        protected override string GetSelectNextMisfiredTriggersInStateToAcquireSql(int count)
        {
            if (count != -1)
            {
                return "SELECT * FROM (" + SqlSelectHasMisfiredTriggersInState + ") WHERE rownum <= " + count;
            }
            return base.GetSelectNextMisfiredTriggersInStateToAcquireSql(count);
        }

        public override bool GetBooleanFromDbValue(object columnValue)
        {
            // we store things as string in oracle with 1/0 as value
            if (columnValue != null && columnValue != DBNull.Value)
            {
                return Convert.ToInt32(columnValue) == 1;
            }

            throw new ArgumentException("Value must be non-null.");
        }
    }
}

数据库连接和driverDelegateType写好之后，还有一个重要的步骤，就是需要在实例StdSchedulerFactory之前需要将DM注册到Quartz的DbMetadata中，否则会出现无法找到的报错，具体方法如下：

        /// <summary>
        /// 注册DM的数据库连接信息
        /// </summary>
        private static void RegisterDbMetadata()
        {
            var metaData = new DbMetadata();
            metaData.ProductName = "DM";
            metaData.AssemblyName = "DmProvider";
            metaData.BindByName = true;
            metaData.CommandType = GetType("DmProvider.dll", "Dm.DmCommand");
            metaData.ConnectionType = GetType("DmProvider.dll", "Dm.DmConnection");
            metaData.ParameterType = GetType("DmProvider.dll", "Dm.DmParameter");
            metaData.ParameterDbType = GetType("DmProvider.dll", "Dm.DbType");
            metaData.ParameterDbTypePropertyName = "DmDbType";
            metaData.ParameterNamePrefix = ":";
            metaData.ExceptionType = GetType("DmProvider.dll", "Dm.DmException");
            metaData.UseParameterNamePrefixInParameterCollection = true;
            DbProvider.RegisterDbMetadata("DM", metaData);
        }

这里的DmProvider是DM针对NetCore提供的驱动，其实通过反编译可以看出来，每个数据库都是集成自顶层db，然后实现自己的CommandType、ConnectionType、ParameterType、ExceptionType等。以后如果再支持一种新的数据库类型，仿照此流程即可。后续项目应该还有进一步支持瀚高、华为高斯、南大通用等，到时候再进一步验证

如何使用自定义触发器和自定义任务

由于项目容量较大，后期规划有触发器的单独管理界面，所以触发器和任务也都是存储在数据库中，所以就涉及到根据自己的实体表结构构造出Quartz本身的ITrigger和JobDetailImpl，仅供参考：
ITrigger

/// <summary>
        /// 创建类型Simple的触发器
        /// </summary>
        /// <param name="m"></param>
        /// <returns></returns>
        public ITrigger CreateSimpleTrigger(GSPTrigger t, GSPSimpleTrigger s)
        {
            //作业触发器
            if (s.RepeatCount != -1)
            {
                    return TriggerBuilder.Create()
                    .WithIdentity(t.Name, t.TriggerGroup)
                    .StartAt(t.StartTime)//开始时间
                    .WithSimpleSchedule(x => x
                    .WithInterval(TimeSpan.Parse(s.RepeatInterval))//执行时间间隔
                    .WithRepeatCount(s.RepeatCount))//执行次数、默认从0开始
                    .Build();
            }
            else
            {
                if (t.EndTime == null)
                {
                    return TriggerBuilder.Create()
                    .WithIdentity(t.Name, t.TriggerGroup)
                    .StartAt(t.StartTime)//开始时间
                    .WithSimpleSchedule(x => x
                    .WithInterval(TimeSpan.Parse(s.RepeatInterval))//执行时间间隔
                    .RepeatForever())//无限循环
                    .Build();
                }
                else
                {
                    return TriggerBuilder.Create()
                    .WithIdentity(t.Name, t.TriggerGroup)
                    .StartAt(t.StartTime)//开始时间
                    .EndAt(t.EndTime)//结束时间
                    .WithSimpleSchedule(x => x
                    .WithInterval(TimeSpan.Parse(s.RepeatInterval))//执行时间间隔
                    .RepeatForever())//无限循环
                    .Build();
                }
            }
        }

        /// <summary>
        /// 创建类型Cron的触发器
        /// </summary>
        /// <param name="m"></param>
        /// <returns></returns>
        public ITrigger CreateCronTrigger(GSPTrigger t, GSPCronTrigger c)
        {
            if (t.EndTime == null)
            {
                return TriggerBuilder.Create()
                   .WithIdentity(t.Name, t.TriggerGroup)
                   .StartAt(t.StartTime)//开始时间
                   .WithCronSchedule(c.CronExpression)//指定cron表达式
                   .Build();
            }
            else
            {
                return TriggerBuilder.Create()
                   .WithIdentity(t.Name, t.TriggerGroup)
                   .StartAt(t.StartTime)//开始时间
                   .EndAt(t.EndTime)//结束数据
                   .WithCronSchedule(c.CronExpression)//指定cron表达式
                   .Build();
            }
        }

JobDetailImpl

        /// <summary>
        /// 获取所有JobDetail
        /// </summary>
        /// <returns></returns>
        public List<JobDetailImpl> GetAllJobDetail()
        {
            List<JobDetailImpl> jobDetails = new List<JobDetailImpl>();
            var jobList = GetAllJobs();
            var jobTypes = GSPScheduleConfig.GetGSPJobTypes();
            foreach (var item in jobList)
            {
                var jobName = item.Name;
                var jobGroup = item.JobGroup;
                JobKey key = new JobKey(jobName, jobGroup);              

                var jobType = jobTypes.Find(x => x.Order == item.JobType);
                if (jobType == null)
                    throw new Exception($"未找到任务类型为{item.JobType}的配置节");
                JobDetailImpl job = ReflectIJob(item.Id, item.Name, item.JobGroup, jobType);

                //DataMap中参数后续根据需要添加
                JobDataMap jobDataMap = new JobDataMap();
                jobDataMap["ID"] = item.Id;
                jobDataMap["Name"] = item.Name;
                jobDataMap["TriggerId"] = item.TriggerID;
                jobDataMap["TriggerType"] = item.TriggerType;
                jobDataMap["Parameters"] = item.JobData;
                job.JobDataMap = jobDataMap;
                job.Description = item.Description;
                job.Key = key;
                job.Durable = item.Is_Durable == "1";
                job.RequestsRecovery = item.RequestsRecovery == "1";

                jobDetails.Add(job);
            }
            return jobDetails;
        }

        public JobDetailImpl ReflectIJob(string jobID, string jobName, string jobGroup, GSPJobType type)
        {
            try
            {
                //加载指定的程序集之内存中
                Assembly assembly = Assembly.Load(type.Assembly);
                //返加程序集中的一个指定的对象，哪果是返回所有对象，则用GetTypes()返回一个Type对象的数组.
                Type T = assembly.GetType(type.ClassName);

                //根据前面type类型创建一个对象
                return new JobDetailImpl(jobName, jobGroup, T);
            }
            catch (Exception e)
            {
                GSPSchedulerLogger.Instance.Info(GSPSchedulerLogCreator.CreateGSPScheduleLog("反射构造Ijob出错: " + e.ToString(), jobID, jobName, "任务初始"));
                throw e;
            }
        }

最后调度执行就完成了

scheduler.ScheduleJob(job, triggerForJob).Wait();
// 开启调度器
await scheduler.Start();

后续Quartz中的相关操作待补充