Index: lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch02040038.sql =================================================================== RCS file: /usr/local/cvsroot/lams_common/src/java/org/lamsfoundation/lams/dbupdates/Attic/patch02040038.sql,v diff -u -r1.1.2.5 -r1.1.2.6 --- lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch02040038.sql 20 Dec 2014 20:39:06 -0000 1.1.2.5 +++ lams_common/src/java/org/lamsfoundation/lams/dbupdates/patch02040038.sql 22 Dec 2014 16:55:03 -0000 1.1.2.6 @@ -3,114 +3,69 @@ -- LDEV-3337: Update Quartz library to version 2.2.1 --- drop tables that are no longer used +-- create new tables as in the original Quartz script -DROP TABLE lams_qtz_JOB_LISTENERS; -DROP TABLE lams_qtz_TRIGGER_LISTENERS; +CREATE TABLE QRTZ_JOB_DETAILS( +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; --- drop columns that are no longer used +CREATE TABLE QRTZ_TRIGGERS ( +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), +FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP) +REFERENCES QRTZ_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP)) +ENGINE=InnoDB; -ALTER TABLE lams_qtz_JOB_DETAILS DROP COLUMN is_volatile; -ALTER TABLE lams_qtz_TRIGGERS DROP COLUMN is_volatile; -ALTER TABLE lams_qtz_FIRED_TRIGGERS DROP COLUMN is_volatile; -ALTER TABLE lams_qtz_SCHEDULER_STATE DROP COLUMN recoverer; +CREATE TABLE QRTZ_SIMPLE_TRIGGERS ( +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), +FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) +REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)) +ENGINE=InnoDB; --- add and modify column definitions to match new schema +CREATE TABLE QRTZ_CRON_TRIGGERS ( +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), +FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) +REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)) +ENGINE=InnoDB; -ALTER TABLE lams_qtz_JOB_DETAILS MODIFY COLUMN job_name VARCHAR(200); -ALTER TABLE lams_qtz_JOB_DETAILS MODIFY COLUMN job_group VARCHAR(200); -ALTER TABLE lams_qtz_JOB_DETAILS MODIFY COLUMN description VARCHAR(250); -ALTER TABLE lams_qtz_JOB_DETAILS MODIFY COLUMN job_class_name VARCHAR(250); -ALTER TABLE lams_qtz_TRIGGERS MODIFY COLUMN trigger_name VARCHAR(200); -ALTER TABLE lams_qtz_TRIGGERS MODIFY COLUMN trigger_group VARCHAR(200); -ALTER TABLE lams_qtz_TRIGGERS MODIFY COLUMN job_name VARCHAR(200); -ALTER TABLE lams_qtz_TRIGGERS MODIFY COLUMN job_group VARCHAR(200); -ALTER TABLE lams_qtz_TRIGGERS MODIFY COLUMN description VARCHAR(250); -ALTER TABLE lams_qtz_TRIGGERS MODIFY COLUMN calendar_name VARCHAR(200); -ALTER TABLE lams_qtz_TRIGGERS ADD COLUMN priority INT AFTER prev_fire_time; -ALTER TABLE lams_qtz_SIMPLE_TRIGGERS MODIFY COLUMN trigger_name VARCHAR(200); -ALTER TABLE lams_qtz_SIMPLE_TRIGGERS MODIFY COLUMN trigger_group VARCHAR(200); -ALTER TABLE lams_qtz_SIMPLE_TRIGGERS MODIFY COLUMN times_triggered BIGINT(10); -ALTER TABLE lams_qtz_CRON_TRIGGERS MODIFY COLUMN trigger_name VARCHAR(200); -ALTER TABLE lams_qtz_CRON_TRIGGERS MODIFY COLUMN trigger_group VARCHAR(200); -ALTER TABLE lams_qtz_CRON_TRIGGERS MODIFY COLUMN cron_expression VARCHAR(120); -ALTER TABLE lams_qtz_BLOB_TRIGGERS MODIFY COLUMN trigger_name VARCHAR(200); -ALTER TABLE lams_qtz_BLOB_TRIGGERS MODIFY COLUMN trigger_group VARCHAR(200); -ALTER TABLE lams_qtz_CALENDARS MODIFY COLUMN calendar_name VARCHAR(200); -ALTER TABLE lams_qtz_PAUSED_TRIGGER_GRPS MODIFY COLUMN trigger_group VARCHAR(200); -ALTER TABLE lams_qtz_FIRED_TRIGGERS MODIFY COLUMN trigger_name VARCHAR(200); -ALTER TABLE lams_qtz_FIRED_TRIGGERS MODIFY COLUMN trigger_group VARCHAR(200); -ALTER TABLE lams_qtz_FIRED_TRIGGERS MODIFY COLUMN instance_name VARCHAR(200); -ALTER TABLE lams_qtz_FIRED_TRIGGERS MODIFY COLUMN job_name VARCHAR(200); -ALTER TABLE lams_qtz_FIRED_TRIGGERS MODIFY COLUMN job_group VARCHAR(200); -ALTER TABLE lams_qtz_FIRED_TRIGGERS ADD COLUMN sched_time BIGINT(13) NOT NULL AFTER fired_time; -ALTER TABLE lams_qtz_FIRED_TRIGGERS ADD COLUMN priority INTEGER NOT NULL AFTER sched_time; -ALTER TABLE lams_qtz_SCHEDULER_STATE MODIFY COLUMN instance_name VARCHAR(200); - --- add new columns that replace the 'is_stateful' column - -ALTER TABLE lams_qtz_JOB_DETAILS ADD COLUMN is_nonconcurrent bool AFTER is_durable; -ALTER TABLE lams_qtz_JOB_DETAILS ADD COLUMN is_update_data bool AFTER is_nonconcurrent; -UPDATE lams_qtz_JOB_DETAILS SET is_nonconcurrent = is_stateful; -UPDATE lams_qtz_JOB_DETAILS SET is_update_data = is_stateful; -ALTER TABLE lams_qtz_JOB_DETAILS DROP COLUMN is_stateful; -ALTER TABLE lams_qtz_FIRED_TRIGGERS ADD COLUMN is_nonconcurrent bool; -UPDATE lams_qtz_FIRED_TRIGGERS SET is_nonconcurrent = is_stateful; -ALTER TABLE lams_qtz_FIRED_TRIGGERS DROP COLUMN is_stateful; - --- add new 'sched_name' columnD to all tables - -ALTER TABLE lams_qtz_BLOB_TRIGGERS ADD COLUMN sched_name VARCHAR(120) NOT NULL DEFAULT 'LAMS' FIRST; -ALTER TABLE lams_qtz_CALENDARS ADD COLUMN sched_name VARCHAR(120) NOT NULL DEFAULT 'LAMS' FIRST; -ALTER TABLE lams_qtz_CRON_TRIGGERS ADD COLUMN sched_name VARCHAR(120) NOT NULL DEFAULT 'LAMS' FIRST; -ALTER TABLE lams_qtz_FIRED_TRIGGERS ADD COLUMN sched_name VARCHAR(120) NOT NULL DEFAULT 'LAMS' FIRST; -ALTER TABLE lams_qtz_JOB_DETAILS ADD COLUMN sched_name VARCHAR(120) NOT NULL DEFAULT 'LAMS' FIRST; -ALTER TABLE lams_qtz_LOCKS ADD COLUMN sched_name VARCHAR(120) NOT NULL DEFAULT 'LAMS' FIRST; -ALTER TABLE lams_qtz_PAUSED_TRIGGER_GRPS ADD COLUMN sched_name VARCHAR(120) NOT NULL DEFAULT 'LAMS' FIRST; -ALTER TABLE lams_qtz_SCHEDULER_STATE ADD COLUMN sched_name VARCHAR(120) NOT NULL DEFAULT 'LAMS' FIRST; -ALTER TABLE lams_qtz_SIMPLE_TRIGGERS ADD COLUMN sched_name VARCHAR(120) NOT NULL DEFAULT 'LAMS' FIRST; -ALTER TABLE lams_qtz_TRIGGERS ADD COLUMN sched_name VARCHAR(120) NOT NULL DEFAULT 'LAMS' FIRST; - --- drop all primary and foreign key constraints, so that we can define new ones - -ALTER TABLE lams_qtz_TRIGGERS DROP FOREIGN KEY lams_qtz_TRIGGERS_ibfk_1; -ALTER TABLE lams_qtz_TRIGGERS DROP INDEX job_name; -ALTER TABLE lams_qtz_BLOB_TRIGGERS DROP FOREIGN KEY lams_qtz_BLOB_TRIGGERS_ibfk_1; -ALTER TABLE lams_qtz_BLOB_TRIGGERS DROP PRIMARY KEY; -ALTER TABLE lams_qtz_SIMPLE_TRIGGERS DROP FOREIGN KEY lams_qtz_SIMPLE_TRIGGERS_ibfk_1; -ALTER TABLE lams_qtz_SIMPLE_TRIGGERS DROP PRIMARY KEY; -ALTER TABLE lams_qtz_CRON_TRIGGERS DROP FOREIGN KEY lams_qtz_CRON_TRIGGERS_ibfk_1; -ALTER TABLE lams_qtz_CRON_TRIGGERS DROP PRIMARY KEY; -ALTER TABLE lams_qtz_JOB_DETAILS DROP PRIMARY KEY; -ALTER TABLE lams_qtz_JOB_DETAILS ADD PRIMARY KEY (sched_name, job_name, job_group); -ALTER TABLE lams_qtz_TRIGGERS DROP PRIMARY KEY; - - --- add all primary and foreign key constraints, based on new columns - -ALTER TABLE lams_qtz_TRIGGERS ADD PRIMARY KEY (sched_name, trigger_name, trigger_group); -ALTER TABLE lams_qtz_TRIGGERS ADD FOREIGN KEY (sched_name, job_name, job_group) REFERENCES lams_qtz_JOB_DETAILS(sched_name, job_name, job_group); -ALTER TABLE lams_qtz_BLOB_TRIGGERS ADD PRIMARY KEY (sched_name, trigger_name, trigger_group); -ALTER TABLE lams_qtz_BLOB_TRIGGERS ADD FOREIGN KEY (sched_name, trigger_name, trigger_group) REFERENCES lams_qtz_TRIGGERS(sched_name, trigger_name, trigger_group); -ALTER TABLE lams_qtz_CRON_TRIGGERS ADD PRIMARY KEY (sched_name, trigger_name, trigger_group); -ALTER TABLE lams_qtz_CRON_TRIGGERS ADD FOREIGN KEY (sched_name, trigger_name, trigger_group) REFERENCES lams_qtz_TRIGGERS(sched_name, trigger_name, trigger_group); -ALTER TABLE lams_qtz_SIMPLE_TRIGGERS ADD PRIMARY KEY (sched_name, trigger_name, trigger_group); -ALTER TABLE lams_qtz_SIMPLE_TRIGGERS ADD FOREIGN KEY (sched_name, trigger_name, trigger_group) REFERENCES lams_qtz_TRIGGERS(sched_name, trigger_name, trigger_group); -ALTER TABLE lams_qtz_FIRED_TRIGGERS DROP PRIMARY KEY; -ALTER TABLE lams_qtz_FIRED_TRIGGERS ADD PRIMARY KEY (sched_name, entry_id); -ALTER TABLE lams_qtz_CALENDARS DROP PRIMARY KEY; -ALTER TABLE lams_qtz_CALENDARS ADD PRIMARY KEY (sched_name, calendar_name); -ALTER TABLE lams_qtz_LOCKS DROP PRIMARY KEY; -ALTER TABLE lams_qtz_LOCKS ADD PRIMARY KEY (sched_name, lock_name); -ALTER TABLE lams_qtz_PAUSED_TRIGGER_GRPS DROP PRIMARY KEY; -ALTER TABLE lams_qtz_PAUSED_TRIGGER_GRPS ADD PRIMARY KEY (sched_name, trigger_group); -ALTER TABLE lams_qtz_SCHEDULER_STATE DROP PRIMARY KEY; -ALTER TABLE lams_qtz_SCHEDULER_STATE ADD PRIMARY KEY (sched_name, instance_name); - --- add new simprop_triggers table - -CREATE TABLE lams_qtz_SIMPROP_TRIGGERS - ( +CREATE TABLE QRTZ_SIMPROP_TRIGGERS + ( SCHED_NAME VARCHAR(120) NOT NULL, TRIGGER_NAME VARCHAR(200) NOT NULL, TRIGGER_GROUP VARCHAR(200) NOT NULL, @@ -123,36 +78,166 @@ 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, + 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 lams_qtz_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) -) ENGINE=InnoDB; + FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) + REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)) +ENGINE=InnoDB; --- create indexes for faster queries +CREATE TABLE QRTZ_BLOB_TRIGGERS ( +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), +INDEX (SCHED_NAME,TRIGGER_NAME, TRIGGER_GROUP), +FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) +REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)) +ENGINE=InnoDB; -CREATE INDEX idx_lams_qtz_j_req_recovery ON lams_qtz_JOB_DETAILS(SCHED_NAME,REQUESTS_RECOVERY); -CREATE INDEX idx_lams_qtz_j_grp ON lams_qtz_JOB_DETAILS(SCHED_NAME,JOB_GROUP); -CREATE INDEX idx_lams_qtz_t_j ON lams_qtz_TRIGGERS(SCHED_NAME,JOB_NAME,JOB_GROUP); -CREATE INDEX idx_lams_qtz_t_jg ON lams_qtz_TRIGGERS(SCHED_NAME,JOB_GROUP); -CREATE INDEX idx_lams_qtz_t_c ON lams_qtz_TRIGGERS(SCHED_NAME,CALENDAR_NAME); -CREATE INDEX idx_lams_qtz_t_g ON lams_qtz_TRIGGERS(SCHED_NAME,TRIGGER_GROUP); -CREATE INDEX idx_lams_qtz_t_state ON lams_qtz_TRIGGERS(SCHED_NAME,TRIGGER_STATE); -CREATE INDEX idx_lams_qtz_t_n_state ON lams_qtz_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_STATE); -CREATE INDEX idx_lams_qtz_t_n_g_state ON lams_qtz_TRIGGERS(SCHED_NAME,TRIGGER_GROUP,TRIGGER_STATE); -CREATE INDEX idx_lams_qtz_t_next_fire_time ON lams_qtz_TRIGGERS(SCHED_NAME,NEXT_FIRE_TIME); -CREATE INDEX idx_lams_qtz_t_nft_st ON lams_qtz_TRIGGERS(SCHED_NAME,TRIGGER_STATE,NEXT_FIRE_TIME); -CREATE INDEX idx_lams_qtz_t_nft_misfire ON lams_qtz_TRIGGERS(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME); -CREATE INDEX idx_lams_qtz_t_nft_st_misfire ON lams_qtz_TRIGGERS(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_STATE); -CREATE INDEX idx_lams_qtz_t_nft_st_misfire_grp ON lams_qtz_TRIGGERS(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_GROUP,TRIGGER_STATE); -CREATE INDEX idx_lams_qtz_ft_trig_inst_name ON lams_qtz_FIRED_TRIGGERS(SCHED_NAME,INSTANCE_NAME); -CREATE INDEX idx_lams_qtz_ft_inst_job_req_rcvry ON lams_qtz_FIRED_TRIGGERS(SCHED_NAME,INSTANCE_NAME,REQUESTS_RECOVERY); -CREATE INDEX idx_lams_qtz_ft_j_g ON lams_qtz_FIRED_TRIGGERS(SCHED_NAME,JOB_NAME,JOB_GROUP); -CREATE INDEX idx_lams_qtz_ft_jg ON lams_qtz_FIRED_TRIGGERS(SCHED_NAME,JOB_GROUP); -CREATE INDEX idx_lams_qtz_ft_t_g ON lams_qtz_FIRED_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP); -CREATE INDEX idx_lams_qtz_ft_tg ON lams_qtz_FIRED_TRIGGERS(SCHED_NAME,TRIGGER_GROUP); +CREATE TABLE QRTZ_CALENDARS ( +SCHED_NAME VARCHAR(120) NOT NULL, +CALENDAR_NAME VARCHAR(200) NOT NULL, +CALENDAR BLOB NOT NULL, +PRIMARY KEY (SCHED_NAME,CALENDAR_NAME)) +ENGINE=InnoDB; +CREATE TABLE QRTZ_PAUSED_TRIGGER_GRPS ( +SCHED_NAME VARCHAR(120) NOT NULL, +TRIGGER_GROUP VARCHAR(200) NOT NULL, +PRIMARY KEY (SCHED_NAME,TRIGGER_GROUP)) +ENGINE=InnoDB; + +CREATE TABLE QRTZ_FIRED_TRIGGERS ( +SCHED_NAME VARCHAR(120) NOT NULL, +ENTRY_ID VARCHAR(95) NOT NULL, +TRIGGER_NAME VARCHAR(200) NOT NULL, +TRIGGER_GROUP VARCHAR(200) NOT NULL, +INSTANCE_NAME VARCHAR(200) 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(200) NULL, +JOB_GROUP VARCHAR(200) NULL, +IS_NONCONCURRENT VARCHAR(1) NULL, +REQUESTS_RECOVERY VARCHAR(1) NULL, +PRIMARY KEY (SCHED_NAME,ENTRY_ID)) +ENGINE=InnoDB; + +CREATE TABLE QRTZ_SCHEDULER_STATE ( +SCHED_NAME VARCHAR(120) NOT NULL, +INSTANCE_NAME VARCHAR(200) 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 QRTZ_LOCKS ( +SCHED_NAME VARCHAR(120) NOT NULL, +LOCK_NAME VARCHAR(40) NOT NULL, +PRIMARY KEY (SCHED_NAME,LOCK_NAME)) +ENGINE=InnoDB; + +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_MISFIRE ON QRTZ_TRIGGERS(SCHED_NAME,MISFIRE_INSTR,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); + + +-- migrate data from old tables to new ones + +INSERT INTO QRTZ_JOB_DETAILS + SELECT 'LAMS', JOB_NAME, JOB_GROUP, DESCRIPTION, JOB_CLASS_NAME, IS_DURABLE, IS_STATEFUL, IS_STATEFUL, REQUESTS_RECOVERY, JOB_DATA + FROM lams_qtz_JOB_DETAILS; + +INSERT INTO QRTZ_TRIGGERS + SELECT 'LAMS', TRIGGER_NAME, TRIGGER_GROUP, JOB_NAME, JOB_GROUP, DESCRIPTION, + NEXT_FIRE_TIME, PREV_FIRE_TIME, NULL, TRIGGER_STATE, TRIGGER_TYPE, START_TIME, END_TIME, + CALENDAR_NAME, MISFIRE_INSTR, JOB_DATA + FROM lams_qtz_TRIGGERS; + +INSERT INTO QRTZ_SIMPLE_TRIGGERS + SELECT 'LAMS', TRIGGER_NAME, TRIGGER_GROUP, REPEAT_COUNT, REPEAT_INTERVAL, TIMES_TRIGGERED + FROM lams_qtz_SIMPLE_TRIGGERS; + +INSERT INTO QRTZ_CRON_TRIGGERS + SELECT 'LAMS', TRIGGER_NAME, TRIGGER_GROUP, CRON_EXPRESSION, TIME_ZONE_ID + FROM lams_qtz_CRON_TRIGGERS; + +INSERT INTO QRTZ_BLOB_TRIGGERS + SELECT 'LAMS', TRIGGER_NAME, TRIGGER_GROUP, BLOB_DATA + FROM lams_qtz_BLOB_TRIGGERS; + +INSERT INTO QRTZ_CALENDARS + SELECT 'LAMS', CALENDAR_NAME, CALENDAR + FROM lams_qtz_CALENDARS; + +INSERT INTO QRTZ_PAUSED_TRIGGER_GRPS + SELECT 'LAMS', TRIGGER_GROUP + FROM lams_qtz_PAUSED_TRIGGER_GRPS; + +INSERT INTO QRTZ_FIRED_TRIGGERS + SELECT 'LAMS', ENTRY_ID, TRIGGER_NAME, TRIGGER_GROUP, INSTANCE_NAME, FIRED_TIME, FIRED_TIME, 5, + STATE, JOB_NAME, JOB_GROUP, IS_STATEFUL, REQUESTS_RECOVERY + FROM lams_qtz_FIRED_TRIGGERS; + +INSERT INTO QRTZ_SCHEDULER_STATE + SELECT 'LAMS', INSTANCE_NAME, LAST_CHECKIN_TIME, CHECKIN_INTERVAL + FROM lams_qtz_SCHEDULER_STATE; + +INSERT INTO QRTZ_LOCKS + SELECT 'LAMS', LOCK_NAME + FROM lams_qtz_LOCKS; + +-- drop old tables + +DROP TABLE IF EXISTS lams_qtz_FIRED_TRIGGERS; +DROP TABLE IF EXISTS lams_qtz_PAUSED_TRIGGER_GRPS; +DROP TABLE IF EXISTS lams_qtz_SCHEDULER_STATE; +DROP TABLE IF EXISTS lams_qtz_LOCKS; +DROP TABLE IF EXISTS lams_qtz_SIMPLE_TRIGGERS; +DROP TABLE IF EXISTS lams_qtz_SIMPROP_TRIGGERS; +DROP TABLE IF EXISTS lams_qtz_CRON_TRIGGERS; +DROP TABLE IF EXISTS lams_qtz_BLOB_TRIGGERS; +DROP TABLE IF EXISTS lams_qtz_JOB_LISTENERS; +DROP TABLE IF EXISTS lams_qtz_TRIGGER_LISTENERS; +DROP TABLE IF EXISTS lams_qtz_TRIGGERS; +DROP TABLE IF EXISTS lams_qtz_JOB_DETAILS; +DROP TABLE IF EXISTS lams_qtz_CALENDARS; + +-- rename the new tables with proper prefix + +RENAME TABLE QRTZ_FIRED_TRIGGERS TO lams_qtz_FIRED_TRIGGERS; +RENAME TABLE QRTZ_PAUSED_TRIGGER_GRPS TO lams_qtz_PAUSED_TRIGGER_GRPS; +RENAME TABLE QRTZ_SCHEDULER_STATE TO lams_qtz_SCHEDULER_STATE; +RENAME TABLE QRTZ_LOCKS TO lams_qtz_LOCKS; +RENAME TABLE QRTZ_SIMPLE_TRIGGERS TO lams_qtz_SIMPLE_TRIGGERS; +RENAME TABLE QRTZ_CRON_TRIGGERS TO lams_qtz_CRON_TRIGGERS; +RENAME TABLE QRTZ_BLOB_TRIGGERS TO lams_qtz_BLOB_TRIGGERS; +RENAME TABLE QRTZ_TRIGGERS TO lams_qtz_TRIGGERS; +RENAME TABLE QRTZ_JOB_DETAILS TO lams_qtz_JOB_DETAILS; +RENAME TABLE QRTZ_CALENDARS TO lams_qtz_CALENDARS; + COMMIT; SET AUTOCOMMIT = 1; SET FOREIGN_KEY_CHECKS = 1; \ No newline at end of file