quartz.sql
7.76 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
-- ----------------------------
-- 1、存储每一个已配置的 jobDetail 的详细信息QRTZ_JOB_DETAILS
-- ----------------------------
drop table if exists qrtz_job_details;
create table qrtz_job_details(
sched_name varchar(120) not null,
jobName varchar(200) not null,
jobGroup 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,jobName,jobGroup)
) engine=innodb default charset=utf8;
-- ----------------------------
-- 2、 存储已配置的 Trigger 的信息QRTZ_TRIGGERS
-- ----------------------------
drop table if exists qrtz_triggers;
create table qrtz_triggers(
sched_name varchar(120) not null,
trigger_name varchar(200) not null,
trigger_group varchar(200) not null,
jobName varchar(200) not null,
jobGroup 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,jobName,jobGroup) references qrtz_job_details(sched_name,jobName,jobGroup)
) engine=innodb default charset=utf8;
-- ----------------------------
-- 3、 存储简单的 Trigger，包括重复次数，间隔，以及已触发的次数QRTZ_SIMPLE_TRIGGERS
-- ----------------------------
drop table if exists qrtz_simple_triggers;
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 default charset=utf8;
-- ----------------------------
-- 4、 存储 Cron Trigger，包括 Cron 表达式和时区信息
-- ----------------------------
drop table if exists qrtz_cron_triggers;
create table qrtz_cron_triggers (
sched_name varchar(120) not null,
trigger_name varchar(200) not null,
trigger_group varchar(200) not null,
cronExpression varchar(200) 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 default charset=utf8;
-- ----------------------------
-- 5、 Trigger 作为 Blob 类型存储(用于 Quartz 用户用 JDBC 创建他们自己定制的 Trigger 类型，JobStore 并不知道如何存储实例的时候)QRTZ_BLOB_TRIGGERS
-- ----------------------------
drop table if exists qrtz_blob_triggers;
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),
foreign key (sched_name,trigger_name,trigger_group) references qrtz_triggers(sched_name,trigger_name,trigger_group)
) engine=innodb default charset=utf8;
-- ----------------------------
-- 6、 以 Blob 类型存储存放日历信息， quartz可配置一个日历来指定一个时间范围QRTZ_CALENDARS
-- ----------------------------
drop table if exists qrtz_calendars;
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 default charset=utf8;
-- ----------------------------
-- 7、 存储已暂停的 Trigger 组的信息QRTZ_PAUSED_TRIGGER_GRPS
-- ----------------------------
drop table if exists qrtz_paused_trigger_gprs;
create table qrtz_paused_trigger_gprs (
sched_name varchar(120) not null,
trigger_group varchar(200) not null,
primary key (sched_name,trigger_group)
) engine=innodb default charset=utf8;
-- ----------------------------
-- 8、 存储与已触发的 Trigger 相关的状态信息，以及相联 Job 的执行信息QRTZ_FIRED_TRIGGERS
-- ----------------------------
drop table if exists qrtz_fired_triggers;
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,
jobName varchar(200) null,
jobGroup varchar(200) null,
is_nonconcurrent varchar(1) null,
requests_recovery varchar(1) null,
primary key (sched_name,entry_id)
) engine=innodb default charset=utf8;
-- ----------------------------
-- 9、 存储少量的有关 Scheduler 的状态信息，假如是用于集群中，可以看到其他的 Scheduler 实例
-- ----------------------------
drop table if exists qrtz_scheduler_state;
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 default charset=utf8;
-- ----------------------------
-- 10、 存储程序的悲观锁的信息(假如使用了悲观锁)QRTZ_LOCKS
-- ----------------------------
drop table if exists qrtz_locks;
create table qrtz_locks (
sched_name varchar(120) not null,
lock_name varchar(40) not null,
primary key (sched_name,lock_name)
) engine=innodb default charset=utf8;
-- QRTZ_SIMPROP_TRIGGERS
drop table if exists qrtz_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,
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 qrtz_triggers(sched_name,trigger_name,trigger_group)
) engine=innodb default charset=utf8;
commit;