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
