Index: lams_common/db/sql/create_quartz_table.sql =================================================================== RCS file: /usr/local/cvsroot/lams_common/db/sql/create_quartz_table.sql,v diff -u -r1.7 -r1.7.2.1 --- lams_common/db/sql/create_quartz_table.sql 19 Feb 2012 20:13:39 -0000 1.7 +++ lams_common/db/sql/create_quartz_table.sql 29 Jun 2015 10:39:56 -0000 1.7.2.1 @@ -1,171 +1,173 @@ --- CVS ID: $Id$ +# Quartz 2.2.1 -# LAMS1.1: quartz 1.5.2 table definition -# -# Quartz seems to work best with the driver mm.mysql-2.0.7-bin.jar -# -# In your Quartz properties file, you'll need to set -# org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate -# - -set foreign_key_checks = 0; - -DROP TABLE IF EXISTS lams_qtz_JOB_LISTENERS; -DROP TABLE IF EXISTS lams_qtz_TRIGGER_LISTENERS; 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_TRIGGERS; DROP TABLE IF EXISTS lams_qtz_JOB_DETAILS; DROP TABLE IF EXISTS lams_qtz_CALENDARS; +CREATE TABLE lams_qtz_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; -CREATE TABLE lams_qtz_JOB_DETAILS - ( - JOB_NAME VARCHAR(80) NOT NULL, - JOB_GROUP VARCHAR(80) NOT NULL, - DESCRIPTION VARCHAR(120) NULL, - JOB_CLASS_NAME VARCHAR(128) NOT NULL, - IS_DURABLE VARCHAR(1) NOT NULL, - IS_VOLATILE VARCHAR(1) NOT NULL, - IS_STATEFUL VARCHAR(1) NOT NULL, - REQUESTS_RECOVERY VARCHAR(1) NOT NULL, - JOB_DATA BLOB NULL, - PRIMARY KEY (JOB_NAME,JOB_GROUP) -)ENGINE=InnoDB; +CREATE TABLE lams_qtz_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 lams_qtz_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP)) +ENGINE=InnoDB; -CREATE TABLE lams_qtz_JOB_LISTENERS - ( - JOB_NAME VARCHAR(80) NOT NULL, - JOB_GROUP VARCHAR(80) NOT NULL, - JOB_LISTENER VARCHAR(80) NOT NULL, - PRIMARY KEY (JOB_NAME,JOB_GROUP,JOB_LISTENER), - FOREIGN KEY (JOB_NAME,JOB_GROUP) - REFERENCES lams_qtz_JOB_DETAILS(JOB_NAME,JOB_GROUP) -)ENGINE=InnoDB; +CREATE TABLE lams_qtz_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 lams_qtz_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)) +ENGINE=InnoDB; -CREATE TABLE lams_qtz_TRIGGERS - ( - TRIGGER_NAME VARCHAR(80) NOT NULL, - TRIGGER_GROUP VARCHAR(80) NOT NULL, - JOB_NAME VARCHAR(80) NOT NULL, - JOB_GROUP VARCHAR(80) NOT NULL, - IS_VOLATILE VARCHAR(1) NOT NULL, - DESCRIPTION VARCHAR(120) NULL, - NEXT_FIRE_TIME BIGINT(13) NULL, - PREV_FIRE_TIME BIGINT(13) 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(80) NULL, - MISFIRE_INSTR SMALLINT(2) NULL, - JOB_DATA BLOB NULL, - PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP), - FOREIGN KEY (JOB_NAME,JOB_GROUP) - REFERENCES lams_qtz_JOB_DETAILS(JOB_NAME,JOB_GROUP) -)ENGINE=InnoDB; +CREATE TABLE lams_qtz_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 lams_qtz_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)) +ENGINE=InnoDB; -CREATE TABLE lams_qtz_SIMPLE_TRIGGERS - ( - TRIGGER_NAME VARCHAR(80) NOT NULL, - TRIGGER_GROUP VARCHAR(80) NOT NULL, - REPEAT_COUNT BIGINT(7) NOT NULL, - REPEAT_INTERVAL BIGINT(12) NOT NULL, - TIMES_TRIGGERED BIGINT(7) NOT NULL, - PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP), - FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP) - REFERENCES lams_qtz_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP) -)ENGINE=InnoDB; +CREATE TABLE lams_qtz_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 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; -CREATE TABLE lams_qtz_CRON_TRIGGERS - ( - TRIGGER_NAME VARCHAR(80) NOT NULL, - TRIGGER_GROUP VARCHAR(80) NOT NULL, - CRON_EXPRESSION VARCHAR(80) NOT NULL, - TIME_ZONE_ID VARCHAR(80), - PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP), - FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP) - REFERENCES lams_qtz_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP) -)ENGINE=InnoDB; +CREATE TABLE lams_qtz_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 lams_qtz_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)) +ENGINE=InnoDB; -CREATE TABLE lams_qtz_BLOB_TRIGGERS - ( - TRIGGER_NAME VARCHAR(80) NOT NULL, - TRIGGER_GROUP VARCHAR(80) NOT NULL, - BLOB_DATA BLOB NULL, - PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP), - FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP) - REFERENCES lams_qtz_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP) -)ENGINE=InnoDB; +CREATE TABLE lams_qtz_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 lams_qtz_TRIGGER_LISTENERS - ( - TRIGGER_NAME VARCHAR(80) NOT NULL, - TRIGGER_GROUP VARCHAR(80) NOT NULL, - TRIGGER_LISTENER VARCHAR(80) NOT NULL, - PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_LISTENER), - FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP) - REFERENCES lams_qtz_TRIGGERS(TRIGGER_NAME,TRIGGER_GROUP) -)ENGINE=InnoDB; +CREATE TABLE lams_qtz_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 lams_qtz_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 lams_qtz_CALENDARS - ( - CALENDAR_NAME VARCHAR(80) NOT NULL, - CALENDAR BLOB NOT NULL, - PRIMARY KEY (CALENDAR_NAME) -)ENGINE=InnoDB; +CREATE TABLE lams_qtz_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 lams_qtz_LOCKS ( +SCHED_NAME VARCHAR(120) NOT NULL, +LOCK_NAME VARCHAR(40) NOT NULL, +PRIMARY KEY (SCHED_NAME,LOCK_NAME)) +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 TABLE lams_qtz_PAUSED_TRIGGER_GRPS - ( - TRIGGER_GROUP VARCHAR(80) NOT NULL, - PRIMARY KEY (TRIGGER_GROUP) -)ENGINE=InnoDB; +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 TABLE lams_qtz_FIRED_TRIGGERS - ( - ENTRY_ID VARCHAR(95) NOT NULL, - TRIGGER_NAME VARCHAR(80) NOT NULL, - TRIGGER_GROUP VARCHAR(80) NOT NULL, - IS_VOLATILE VARCHAR(1) NOT NULL, - INSTANCE_NAME VARCHAR(80) NOT NULL, - FIRED_TIME BIGINT(13) NOT NULL, - STATE VARCHAR(16) NOT NULL, - JOB_NAME VARCHAR(80) NULL, - JOB_GROUP VARCHAR(80) NULL, - IS_STATEFUL VARCHAR(1) NULL, - REQUESTS_RECOVERY VARCHAR(1) NULL, - PRIMARY KEY (ENTRY_ID) -)ENGINE=InnoDB; +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 lams_qtz_SCHEDULER_STATE - ( - INSTANCE_NAME VARCHAR(80) NOT NULL, - LAST_CHECKIN_TIME BIGINT(13) NOT NULL, - CHECKIN_INTERVAL BIGINT(13) NOT NULL, - RECOVERER VARCHAR(80) NULL, - PRIMARY KEY (INSTANCE_NAME) -)ENGINE=InnoDB; - -CREATE TABLE lams_qtz_LOCKS - ( - LOCK_NAME VARCHAR(40) NOT NULL, - PRIMARY KEY (LOCK_NAME) -)ENGINE=InnoDB; - - -INSERT INTO lams_qtz_LOCKS values('TRIGGER_ACCESS'); -INSERT INTO lams_qtz_LOCKS values('JOB_ACCESS'); -INSERT INTO lams_qtz_LOCKS values('CALENDAR_ACCESS'); -INSERT INTO lams_qtz_LOCKS values('STATE_ACCESS'); -INSERT INTO lams_qtz_LOCKS values('MISFIRE_ACCESS'); - - -set foreign_key_checks = 1; \ No newline at end of file +commit; \ No newline at end of file