mysql.quartz2-upgrade.sql Maven / Gradle / Ivy

Go to download
-- ============================================================================
-- This upgrade script as an alternate to running upgrade-2_2.sql
-- Since mysql has issues with mixed case foreign keys this script was
-- created to overcome this by copying the data from the old tables into new
-- tables and then dropping the old tables
-- ============================================================================
--
-- drop tables that are no longer used
--
DROP TABLE QRTZ_JOB_LISTENERS;
DROP TABLE QRTZ_TRIGGER_LISTENERS;
--
-- drop columns that are no longer used
--
ALTER TABLE QRTZ_FIRED_TRIGGERS DROP COLUMN IS_VOLATILE;
--
-- add new columns that replace the 'is_stateful' column
--
ALTER TABLE QRTZ_FIRED_TRIGGERS ADD COLUMN IS_NONCONCURRENT BOOL;
UPDATE QRTZ_FIRED_TRIGGERS SET IS_NONCONCURRENT = IS_STATEFUL;
ALTER TABLE QRTZ_FIRED_TRIGGERS DROP COLUMN IS_STATEFUL;
--
-- add new 'sched_name' column to all tables
--
ALTER TABLE QRTZ_CALENDARS ADD COLUMN SCHED_NAME VARCHAR(120) NOT NULL DEFAULT 'QUARTZSCHEDULER';
ALTER TABLE QRTZ_FIRED_TRIGGERS ADD COLUMN SCHED_NAME VARCHAR(120) NOT NULL DEFAULT 'QUARTZSCHEDULER';
ALTER TABLE QRTZ_LOCKS ADD COLUMN SCHED_NAME VARCHAR(120) NOT NULL DEFAULT 'QUARTZSCHEDULER';
ALTER TABLE QRTZ_PAUSED_TRIGGER_GRPS ADD COLUMN SCHED_NAME VARCHAR(120) NOT NULL DEFAULT 'QUARTZSCHEDULER';
ALTER TABLE QRTZ_SCHEDULER_STATE ADD COLUMN SCHED_NAME VARCHAR(120) NOT NULL DEFAULT 'QUARTZSCHEDULER';
--
-- add new 'sched_time' column to all tables
--
ALTER TABLE QRTZ_FIRED_TRIGGERS ADD COLUMN SCHED_TIME BIGINT(13) NOT NULL;
--
-- drop intermediate tables
--
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS QRTZ_JOB_DETAILS_NEW;
DROP TABLE IF EXISTS QRTZ_TRIGGERS_NEW;
DROP TABLE IF EXISTS QRTZ_SIMPLE_TRIGGERS_NEW;
DROP TABLE IF EXISTS QRTZ_CRON_TRIGGERS_NEW;
DROP TABLE IF EXISTS QRTZ_BLOB_TRIGGERS_NEW;
DROP TABLE IF EXISTS QRTZ_JOB_DETAILS_OLD;
DROP TABLE IF EXISTS QRTZ_TRIGGERS_OLD;
DROP TABLE IF EXISTS QRTZ_SIMPLE_TRIGGERS_OLD;
DROP TABLE IF EXISTS QRTZ_CRON_TRIGGERS_OLD;
DROP TABLE IF EXISTS QRTZ_BLOB_TRIGGERS_OLD;
SET FOREIGN_KEY_CHECKS = 1;
--
-- create new tables
--
CREATE TABLE QRTZ_JOB_DETAILS_NEW (
SCHED_NAME VARCHAR(120) NOT NULL,
JOB_NAME VARCHAR(200) NOT NULL,
JOB_GROUP VARCHAR(200) 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 QRTZ_TRIGGERS_NEW (
SCHED_NAME VARCHAR(120) NOT NULL,
TRIGGER_NAME VARCHAR(200) NOT NULL,
TRIGGER_GROUP VARCHAR(200) NOT NULL,
JOB_NAME VARCHAR(200) NOT NULL,
JOB_GROUP VARCHAR(200) 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(200) NULL,
MISFIRE_INSTR SMALLINT(2) NULL,
JOB_DATA BLOB NULL,
PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP))
ENGINE=INNODB;

CREATE TABLE QRTZ_SIMPLE_TRIGGERS_NEW (
SCHED_NAME VARCHAR(120) NOT NULL,
TRIGGER_NAME VARCHAR(200) NOT NULL,
TRIGGER_GROUP VARCHAR(200) 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))
ENGINE=INNODB;

CREATE TABLE QRTZ_CRON_TRIGGERS_NEW (
SCHED_NAME VARCHAR(120) NOT NULL,
TRIGGER_NAME VARCHAR(200) NOT NULL,
TRIGGER_GROUP VARCHAR(200) NOT NULL,
CRON_EXPRESSION VARCHAR(120) NOT NULL,
TIME_ZONE_ID VARCHAR(80),
PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP))
ENGINE=INNODB;

CREATE TABLE QRTZ_BLOB_TRIGGERS_NEW (
SCHED_NAME VARCHAR(120) NOT NULL,
TRIGGER_NAME VARCHAR(200) NOT NULL,
TRIGGER_GROUP VARCHAR(200) NOT NULL,
BLOB_DATA BLOB NULL,
PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP))
ENGINE=INNODB;
--
-- Copy data from old tables into new
--
INSERT INTO QRTZ_JOB_DETAILS_NEW (SCHED_NAME, JOB_NAME, JOB_GROUP, DESCRIPTION, JOB_CLASS_NAME, IS_DURABLE, IS_NONCONCURRENT, IS_UPDATE_DATA, REQUESTS_RECOVERY, JOB_DATA) SELECT 'QUARTZSCHEDULER', JOB_NAME, JOB_GROUP, DESCRIPTION, JOB_CLASS_NAME, IS_DURABLE, IS_STATEFUL, IS_STATEFUL, REQUESTS_RECOVERY, JOB_DATA FROM QRTZ_JOB_DETAILS;
INSERT INTO QRTZ_TRIGGERS_NEW (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP, JOB_NAME, JOB_GROUP, DESCRIPTION, NEXT_FIRE_TIME, PREV_FIRE_TIME, PRIORITY, TRIGGER_STATE, TRIGGER_TYPE, START_TIME, END_TIME, CALENDAR_NAME, MISFIRE_INSTR, JOB_DATA) SELECT 'QUARTZSCHEDULER', TRIGGER_NAME, TRIGGER_GROUP, JOB_NAME, JOB_GROUP, DESCRIPTION, NEXT_FIRE_TIME, PREV_FIRE_TIME, PRIORITY, TRIGGER_STATE, TRIGGER_TYPE, START_TIME, END_TIME, CALENDAR_NAME, MISFIRE_INSTR, JOB_DATA FROM QRTZ_TRIGGERS;
INSERT INTO QRTZ_SIMPLE_TRIGGERS_NEW (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP, REPEAT_COUNT, REPEAT_INTERVAL, TIMES_TRIGGERED) SELECT 'QUARTZSCHEDULER', TRIGGER_NAME, TRIGGER_GROUP, REPEAT_COUNT, REPEAT_INTERVAL, TIMES_TRIGGERED FROM QRTZ_SIMPLE_TRIGGERS;
INSERT INTO QRTZ_CRON_TRIGGERS_NEW (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP, CRON_EXPRESSION, TIME_ZONE_ID) SELECT 'QUARTZSCHEDULER', TRIGGER_NAME, TRIGGER_GROUP, CRON_EXPRESSION, TIME_ZONE_ID FROM QRTZ_CRON_TRIGGERS;
INSERT INTO QRTZ_BLOB_TRIGGERS_NEW (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP, BLOB_DATA) SELECT 'QUARTZSCHEDULER', TRIGGER_NAME, TRIGGER_GROUP, BLOB_DATA FROM QRTZ_BLOB_TRIGGERS;
--
-- Rename tables
--
RENAME TABLE QRTZ_JOB_DETAILS TO QRTZ_JOB_DETAILS_OLD, QRTZ_JOB_DETAILS_NEW TO QRTZ_JOB_DETAILS;
RENAME TABLE QRTZ_TRIGGERS TO QRTZ_TRIGGERS_OLD, QRTZ_TRIGGERS_NEW TO QRTZ_TRIGGERS;
RENAME TABLE QRTZ_SIMPLE_TRIGGERS TO QRTZ_SIMPLE_TRIGGERS_OLD, QRTZ_SIMPLE_TRIGGERS_NEW TO QRTZ_SIMPLE_TRIGGERS;
RENAME TABLE QRTZ_CRON_TRIGGERS TO QRTZ_CRON_TRIGGERS_OLD, QRTZ_CRON_TRIGGERS_NEW TO QRTZ_CRON_TRIGGERS;
RENAME TABLE QRTZ_BLOB_TRIGGERS TO QRTZ_BLOB_TRIGGERS_OLD, QRTZ_BLOB_TRIGGERS_NEW TO QRTZ_BLOB_TRIGGERS;
--
-- Drop old tables
--
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS QRTZ_JOB_DETAILS_OLD;
DROP TABLE IF EXISTS QRTZ_TRIGGERS_OLD;
DROP TABLE IF EXISTS QRTZ_SIMPLE_TRIGGERS_OLD;
DROP TABLE IF EXISTS QRTZ_CRON_TRIGGERS_OLD;
DROP TABLE IF EXISTS QRTZ_BLOB_TRIGGERS_OLD;
SET FOREIGN_KEY_CHECKS = 1;
--
-- Add keys
--
ALTER TABLE QRTZ_TRIGGERS ADD FOREIGN KEY (SCHED_NAME, JOB_NAME, JOB_GROUP) REFERENCES QRTZ_JOB_DETAILS (SCHED_NAME, JOB_NAME, JOB_GROUP);
ALTER TABLE QRTZ_BLOB_TRIGGERS ADD FOREIGN KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP) REFERENCES QRTZ_TRIGGERS(SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP);
ALTER TABLE QRTZ_CRON_TRIGGERS ADD FOREIGN KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP) REFERENCES QRTZ_TRIGGERS(SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP);
ALTER TABLE QRTZ_SIMPLE_TRIGGERS ADD FOREIGN KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP) REFERENCES QRTZ_TRIGGERS(SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP);
ALTER TABLE QRTZ_CALENDARS DROP PRIMARY KEY, ADD PRIMARY KEY (SCHED_NAME, CALENDAR_NAME);
ALTER TABLE QRTZ_LOCKS DROP PRIMARY KEY, ADD PRIMARY KEY (SCHED_NAME, LOCK_NAME);
ALTER TABLE QRTZ_PAUSED_TRIGGER_GRPS DROP PRIMARY KEY, ADD PRIMARY KEY (SCHED_NAME, TRIGGER_GROUP);
ALTER TABLE QRTZ_SCHEDULER_STATE DROP PRIMARY KEY, ADD PRIMARY KEY (SCHED_NAME, INSTANCE_NAME);
--
-- add new simprop_triggers table
--
CREATE TABLE QRTZ_SIMPROP_TRIGGERS
 (
    SCHED_NAME VARCHAR(120) NOT NULL,
    TRIGGER_NAME VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) 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 BOOL NULL,
    BOOL_PROP_2 BOOL NULL,
    PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),
    FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
    REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)
);
--
-- create indexes for faster queries
--
CREATE INDEX IDX_QRTZ_J_REQ_RECOVERY ON QRTZ_JOB_DETAILS(SCHED_NAME,REQUESTS_RECOVERY);
CREATE INDEX IDX_QRTZ_J_GRP ON QRTZ_JOB_DETAILS(SCHED_NAME,JOB_GROUP);
CREATE INDEX IDX_QRTZ_T_J ON QRTZ_TRIGGERS(SCHED_NAME,JOB_NAME,JOB_GROUP);
CREATE INDEX IDX_QRTZ_T_JG ON QRTZ_TRIGGERS(SCHED_NAME,JOB_GROUP);
CREATE INDEX IDX_QRTZ_T_C ON QRTZ_TRIGGERS(SCHED_NAME,CALENDAR_NAME);
CREATE INDEX IDX_QRTZ_T_G ON QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_GROUP);
CREATE INDEX IDX_QRTZ_T_STATE ON QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_STATE);
CREATE INDEX IDX_QRTZ_T_N_STATE ON QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_STATE);
CREATE INDEX IDX_QRTZ_T_N_G_STATE ON QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_GROUP,TRIGGER_STATE);
CREATE INDEX IDX_QRTZ_T_NEXT_FIRE_TIME ON QRTZ_TRIGGERS(SCHED_NAME,NEXT_FIRE_TIME);
CREATE INDEX IDX_QRTZ_T_NFT_ST ON QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_STATE,NEXT_FIRE_TIME);
CREATE INDEX IDX_QRTZ_T_NFT_ST_MISFIRE ON QRTZ_TRIGGERS(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_STATE);
CREATE INDEX IDX_QRTZ_T_NFT_ST_MISFIRE_GRP ON QRTZ_TRIGGERS(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_GROUP,TRIGGER_STATE);
CREATE INDEX IDX_QRTZ_FT_TRIG_INST_NAME ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,INSTANCE_NAME);
CREATE INDEX IDX_QRTZ_FT_INST_JOB_REQ_RCVRY ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,INSTANCE_NAME,REQUESTS_RECOVERY);
CREATE INDEX IDX_QRTZ_FT_J_G ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,JOB_NAME,JOB_GROUP);
CREATE INDEX IDX_QRTZ_FT_JG ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,JOB_GROUP);
CREATE INDEX IDX_QRTZ_FT_T_G ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP);
CREATE INDEX IDX_QRTZ_FT_TG ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,TRIGGER_GROUP);