yyc-quartz-service/db/20260824-调度服务迁移.sql

CREATE TABLE quartz_job_details
(
    SCHED_NAME        VARCHAR(120) NOT NULL,
    JOB_NAME          VARCHAR(190) NOT NULL,
    JOB_GROUP         VARCHAR(190) NOT NULL,
    DESCRIPTION       VARCHAR(250) NULL,
    JOB_CLASS_NAME    VARCHAR(250) NOT NULL,
    IS_DURABLE        VARCHAR(1)   NOT NULL,
    IS_NONCONCURRENT  VARCHAR(1)   NOT NULL,
    IS_UPDATE_DATA    VARCHAR(1)   NOT NULL,
    REQUESTS_RECOVERY VARCHAR(1)   NOT NULL,
    JOB_DATA          BLOB NULL,
    PRIMARY KEY (SCHED_NAME, JOB_NAME, JOB_GROUP)
) ENGINE=InnoDB;

CREATE TABLE quartz_triggers
(
    SCHED_NAME     VARCHAR(120) NOT NULL,
    TRIGGER_NAME   VARCHAR(190) NOT NULL,
    TRIGGER_GROUP  VARCHAR(190) NOT NULL,
    JOB_NAME       VARCHAR(190) NOT NULL,
    JOB_GROUP      VARCHAR(190) NOT NULL,
    DESCRIPTION    VARCHAR(250) NULL,
    NEXT_FIRE_TIME BIGINT(13) NULL,
    PREV_FIRE_TIME BIGINT(13) NULL,
    PRIORITY       INTEGER NULL,
    TRIGGER_STATE  VARCHAR(16)  NOT NULL,
    TRIGGER_TYPE   VARCHAR(8)   NOT NULL,
    START_TIME     BIGINT(13) NOT NULL,
    END_TIME       BIGINT(13) NULL,
    CALENDAR_NAME  VARCHAR(190) NULL,
    MISFIRE_INSTR  SMALLINT(2) NULL,
    JOB_DATA       BLOB NULL,
    PRIMARY KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP),
    FOREIGN KEY (SCHED_NAME, JOB_NAME, JOB_GROUP) REFERENCES quartz_job_details (SCHED_NAME, JOB_NAME, JOB_GROUP)
) ENGINE=InnoDB;

CREATE TABLE quartz_simple_triggers
(
    SCHED_NAME      VARCHAR(120) NOT NULL,
    TRIGGER_NAME    VARCHAR(190) NOT NULL,
    TRIGGER_GROUP   VARCHAR(190) NOT NULL,
    REPEAT_COUNT    BIGINT(7) NOT NULL,
    REPEAT_INTERVAL BIGINT(12) NOT NULL,
    TIMES_TRIGGERED BIGINT(10) NOT NULL,
    PRIMARY KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP),
    FOREIGN KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP) REFERENCES quartz_triggers (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
) ENGINE=InnoDB;

CREATE TABLE quartz_cron_triggers
(
    SCHED_NAME      VARCHAR(120) NOT NULL,
    TRIGGER_NAME    VARCHAR(190) NOT NULL,
    TRIGGER_GROUP   VARCHAR(190) NOT NULL,
    CRON_EXPRESSION VARCHAR(120) NOT NULL,
    TIME_ZONE_ID    VARCHAR(80),
    PRIMARY KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP),
    FOREIGN KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP) REFERENCES quartz_triggers (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
) ENGINE=InnoDB;

CREATE TABLE quartz_simprop_triggers
(
    SCHED_NAME    VARCHAR(120) NOT NULL,
    TRIGGER_NAME  VARCHAR(190) NOT NULL,
    TRIGGER_GROUP VARCHAR(190) NOT NULL,
    STR_PROP_1    VARCHAR(512) NULL,
    STR_PROP_2    VARCHAR(512) NULL,
    STR_PROP_3    VARCHAR(512) NULL,
    INT_PROP_1    INT NULL,
    INT_PROP_2    INT NULL,
    LONG_PROP_1   BIGINT NULL,
    LONG_PROP_2   BIGINT NULL,
    DEC_PROP_1    NUMERIC(13, 4) NULL,
    DEC_PROP_2    NUMERIC(13, 4) NULL,
    BOOL_PROP_1   VARCHAR(1) NULL,
    BOOL_PROP_2   VARCHAR(1) NULL,
    PRIMARY KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP),
    FOREIGN KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP) REFERENCES quartz_triggers (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
) ENGINE=InnoDB;

CREATE TABLE quartz_blob_triggers
(
    SCHED_NAME    VARCHAR(120) NOT NULL,
    TRIGGER_NAME  VARCHAR(190) NOT NULL,
    TRIGGER_GROUP VARCHAR(190) NOT NULL,
    BLOB_DATA     BLOB NULL,
    PRIMARY KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP),
    FOREIGN KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP) REFERENCES quartz_triggers (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
) ENGINE=InnoDB;

CREATE TABLE quartz_calendars
(
    SCHED_NAME    VARCHAR(120) NOT NULL,
    CALENDAR_NAME VARCHAR(190) NOT NULL,
    CALENDAR      BLOB         NOT NULL,
    PRIMARY KEY (SCHED_NAME, CALENDAR_NAME)
) ENGINE=InnoDB;

CREATE TABLE quartz_paused_trigger_grps
(
    SCHED_NAME    VARCHAR(120) NOT NULL,
    TRIGGER_GROUP VARCHAR(190) NOT NULL,
    PRIMARY KEY (SCHED_NAME, TRIGGER_GROUP)
) ENGINE=InnoDB;

CREATE TABLE quartz_fired_triggers
(
    SCHED_NAME        VARCHAR(120) NOT NULL,
    ENTRY_ID          VARCHAR(95)  NOT NULL,
    TRIGGER_NAME      VARCHAR(190) NOT NULL,
    TRIGGER_GROUP     VARCHAR(190) NOT NULL,
    INSTANCE_NAME     VARCHAR(190) NOT NULL,
    FIRED_TIME        BIGINT(13) NOT NULL,
    SCHED_TIME        BIGINT(13) NOT NULL,
    PRIORITY          INTEGER      NOT NULL,
    STATE             VARCHAR(16)  NOT NULL,
    JOB_NAME          VARCHAR(190) NULL,
    JOB_GROUP         VARCHAR(190) NULL,
    IS_NONCONCURRENT  VARCHAR(1) NULL,
    REQUESTS_RECOVERY VARCHAR(1) NULL,
    PRIMARY KEY (SCHED_NAME, ENTRY_ID)
) ENGINE=InnoDB;

CREATE TABLE quartz_scheduler_state
(
    SCHED_NAME        VARCHAR(120) NOT NULL,
    INSTANCE_NAME     VARCHAR(190) NOT NULL,
    LAST_CHECKIN_TIME BIGINT(13) NOT NULL,
    CHECKIN_INTERVAL  BIGINT(13) NOT NULL,
    PRIMARY KEY (SCHED_NAME, INSTANCE_NAME)
) ENGINE=InnoDB;

CREATE TABLE quartz_locks
(
    SCHED_NAME VARCHAR(120) NOT NULL,
    LOCK_NAME  VARCHAR(40)  NOT NULL,
    PRIMARY KEY (SCHED_NAME, LOCK_NAME)
) ENGINE=InnoDB;

INSERT INTO sys_job (job_name,
                     job_group,
                     job_order,
                     job_type,
                     execute_path,
                     method_name,
                     method_params_value,
                     cron_expression,
                     misfire_policy,
                     job_tenant_type,
                     job_status,
                     remark,
                     create_by,
                     create_time)
VALUES ('任务图片排重结果同步',
        'image_sync_group',
        '5',
        '3',
        'http://hnqz-upms-biz/schedule/common/sync-task-image-audit',
        NULL,
        NULL,
        '0 */10 * * * ?',
        '2',
        '2',
        '2',
        '从 @Scheduled 迁移，图片排重结果从 vector-search 增量同步到本地库',
        'admin',
        NOW()),
       ('结算平台和税源地数据同步',
        'settlement_sync_group',
        '5',
        '3',
        'http://hnqz-upms-biz/schedule/common/sync-settlement',
        NULL,
        NULL,
        '0 */10 * * * ?',
        '2',
        '2',
        '2',
        '从 @Scheduled 迁移，同步结算平台、税源地、税源地发票数据',
        'admin',
        NOW()),
       ('GIG签约回调处理',
        'gig_group',
        '5',
        '3',
        'http://gulop-gig-biz/gig/schedule/callback-sign',
        NULL,
        NULL,
        '0/10 * * * * ?',
        '2',
        '2',
        '2',
        'GIG签约回调处理',
        'admin',
        NOW()),
       ('GIG结算回调处理',
        'gig_group',
        '5',
        '3',
        'http://gulop-gig-biz/gig/schedule/callback-settle',
        NULL,
        NULL,
        '1/10 * * * * ?',
        '2',
        '2',
        '2',
        'GIG结算回调处理',
        'admin',
        NOW()),
       ('GIG结算步骤重试',
        'gig_group',
        '5',
        '3',
        'http://gulop-gig-biz/gig/schedule/settle-step-retry',
        NULL,
        NULL,
        '2/10 * * * * ?',
        '2',
        '2',
        '2',
        'GIG结算步骤重试',
        'admin',
        NOW()),
       ('产才易任务审核确认',
        'gig_group',
        '5',
        '3',
        'http://gulop-gig-biz/gig/schedule/szzgxs-task-audit-confirm',
        NULL,
        NULL,
        '3/10 * * * * ?',
        '2',
        '2',
        '2',
        '产才易任务审核确认',
        'admin',
        NOW()),
       ('产才易签约结果确认',
        'gig_group',
        '5',
        '3',
        'http://gulop-gig-biz/gig/schedule/szzgxs-sign-result-confirm',
        NULL,
        NULL,
        '4/10 * * * * ?',
        '2',
        '2',
        '2',
        '产才易签约结果确认',
        'admin',
        NOW()),
       ('合创优选林朗签约结果确认',
        'gig_group',
        '5',
        '3',
        'http://gulop-gig-biz/gig/schedule/hczylxlw-sign-result-confirm',
        NULL,
        NULL,
        '5/10 * * * * ?',
        '2',
        '2',
        '2',
        '合创优选林朗签约结果确认',
        'admin',
        NOW()),
       ('合创优选林朗结算结果确认',
        'gig_group',
        '5',
        '3',
        'http://gulop-gig-biz/gig/schedule/hczylxlw-settle-result-confirm',
        NULL,
        NULL,
        '6/10 * * * * ?',
        '2',
        '2',
        '2',
        '合创优选林朗结算结果确认',
        'admin',
        NOW()),
       ('BossKg签约结果确认',
        'gig_group',
        '5',
        '3',
        'http://gulop-gig-biz/gig/schedule/bosskgyjb-sign-result-confirm',
        NULL,
        NULL,
        '7/10 * * * * ?',
        '2',
        '2',
        '2',
        'BossKg签约结果确认',
        'admin',
        NOW()),
       ('芜湖金浩结算结果确认',
        'gig_group',
        '5',
        '3',
        'http://gulop-gig-biz/gig/schedule/wuhujinhao-settle-result-confirm',
        NULL,
        NULL,
        '8/10 * * * * ?',
        '2',
        '2',
        '2',
        '芜湖金浩结算结果确认',
        'admin',
        NOW()),
       ('平台申请通知',
        'gig_group',
        '5',
        '3',
        'http://gulop-gig-biz/gig/schedule/notify-platform-application',
        NULL,
        NULL,
        '9/10 * * * * ?',
        '2',
        '2',
        '2',
        '平台申请通知',
        'admin',
        NOW());

-- 2. 执行迁移：更新 job_type 为 REST(3)，设置对应的 execute_path
UPDATE sys_job
SET job_type     = '3',
    execute_path = CASE method_name
                       WHEN 'dailyGMVsStatistic'
                           THEN 'http://hnqz-upms-biz/schedule/statistics/daily-gmvs-statistic'
                       WHEN 'setHotArtileTask'
                           THEN 'http://hnqz-upms-biz/schedule/article/set-hot-article'
                       WHEN 'singleMemberSettleSameAmountInSeveralMonths'
                           THEN 'http://hnqz-upms-biz/schedule/risk/single-member-settle-same-amount'
                       WHEN 'singleMemberSettleAmountOverrunInSeveralMonths'
                           THEN 'http://hnqz-upms-biz/schedule/risk/single-member-settle-amount-overrun'
                       WHEN 'singleEnterpriseSettleSameAmountForSeveralMembersInCurrentMonth'
                           THEN 'http://hnqz-upms-biz/schedule/risk/single-enterprise-settle-same-amount'
                       WHEN 'singleMemberWithoutSuchTaskTypesInSeveralMonths'
                           THEN 'http://hnqz-upms-biz/schedule/risk/single-member-without-task-types'
                       WHEN 'sendCurrentDaySettleDataToDeptDaily'
                           THEN 'http://hnqz-upms-biz/schedule/task/send-settle-data-to-dept'
                       WHEN 'sendFxyApply'
                           THEN 'http://hnqz-upms-biz/schedule/promotion/send-fxy-apply'
                       WHEN 'sendPromotionRecord'
                           THEN 'http://hnqz-upms-biz/schedule/promotion/send-promotion-record'
                       WHEN 'batchUpdatePackageStatus'
                           THEN 'http://hnqz-upms-biz/schedule/task/batch-update-package-status'
                       WHEN 'refreshFinalReviewScoreForSettlement'
                           THEN 'http://hnqz-upms-biz/schedule/task/refresh-final-review-score'
                       WHEN 'asignPullFacePhoto'
                           THEN 'http://gulop-gig-biz/gig/cert/pull-face-img'
                       ELSE execute_path
        END,
    -- REST 模式不需要 className 和 methodName，清空
    class_name   = NULL,
    method_name  = NULL,
    update_by    = 'admin',
    update_time  = NOW()
WHERE job_type = '2'
  AND class_name = 'demo'
  AND method_name IN (
                      'dailyGMVsStatistic',
                      'setHotArtileTask',
                      'singleMemberSettleSameAmountInSeveralMonths',
                      'singleMemberSettleAmountOverrunInSeveralMonths',
                      'singleEnterpriseSettleSameAmountForSeveralMembersInCurrentMonth',
                      'singleMemberWithoutSuchTaskTypesInSeveralMonths',
                      'sendCurrentDaySettleDataToDeptDaily',
                      'sendFxyApply',
                      'sendPromotionRecord',
                      'batchUpdatePackageStatus',
                      'refreshFinalReviewScoreForSettlement',
                      'asignPullFacePhoto'
    );