Blame view

sql/quartz.sql 7.76 KB
tangying authored
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;