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
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
|
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
create database if not exists lhotse default character set utf8mb4 collate utf8mb4_general_ci;
use lhotse;
-- drop table if exists lb_task_type;
create table if not exists lb_task_type (
type_id smallint not null comment '类型ID',
type_desc varchar(100) not null comment '类型描述',
type_sort varchar(100) not null comment '所属大类',
in_charge varchar(30) not null comment '负责人',
create_time datetime not null comment '创建时间',
killable smallint not null default 0 comment '可KILL',
global_parallelism smallint not null default 20 comment '全局并发度',
broker_parallelism smallint not null default 20 comment '每个Broker上并发度',
task_parallelism smallint not null default 10 comment '每个任务最大并发度',
server_parallelism smallint not null default 10 comment 'server并发度',
default_alive_wait smallint not null default 30 comment '默认存活等待(分钟)',
polling_seconds smallint not null default 60 comment '轮询周期(秒)',
retry_wait smallint not null default 1 comment '重试等待(分钟)',
source_server_type varchar(100) comment '源服务器类型',
target_server_type varchar(100) comment '目标服务器类型',
bg_special varchar(10) not null default 'all' comment 'BG专用',
param_list varchar(10240) comment '参数列表XML',
primary key (type_id)
) ENGINE = InnoDB default character set = utf8mb4 collate = utf8mb4_general_ci comment = '任务类型' ROW_FORMAT = Dynamic;
-- drop table if exists lb_task_type_ext;
create table if not exists lb_task_type_ext (
type_id smallint not null comment '任务类型ID',
prop_name varchar(255) not null comment '属性名',
prop_tag varchar(100) not null comment '属性标签(UI展示用)',
prop_desc varchar(500) comment '描述提示信息(UI展示用)',
rank_id smallint not null default 1 comment '排列ID',
input_type varchar(50) not null comment '控件类型',
value_type varchar(50) not null comment '值类型(integer,string)',
default_value varchar(1000) comment '缺省值',
candidate_values varchar(1000) comment '候选值列表(,分隔)',
candidate_texts varchar(2000) comment '候选文本列表(,分隔)',
is_mandatory smallint not null default 0 comment '是否必填',
max_value int comment '最大值',
min_value int comment '最小值',
conf_level smallint not null default 1 comment '配置等级(1-普通,2-高级,3-管理员)',
primary key (type_id, prop_name),
constraint fk_task_type_ext_ref_task_type foreign key (type_id) references lb_task_type (type_id)
on delete restrict on update restrict
) ENGINE = InnoDB default character set = utf8mb4 collate = utf8mb4_general_ci comment = '任务类型扩展配置信息';
-- drop table if exists lb_server;
create table if not exists lb_server (
server_tag varchar(200) not null comment '服务器名',
server_type varchar(20) not null comment '服务器类型',
server_alias varchar(50) not null comment '服务器别名',
is_group smallint not null default 0 comment '是否为组',
host varchar(2000) comment '主机名',
port int comment '端口',
service varchar(30) comment '服务名',
user_name varchar(100) comment '用户名',
user_group varchar(30) comment '用户组',
password varchar(100) comment '密码',
in_charge varchar(100) not null comment '负责人',
create_time datetime not null comment '创建时间',
parallelism smallint not null default 5 comment '最大并发度(每种任务类型独立)',
server_version varchar(50) comment '服务器版本',
special_reserve smallint not null default 10 comment '特殊保留百分比',
special_priority smallint not null default 1 comment '特殊保留的优先级限制',
primary key (server_tag)
) ENGINE = InnoDB default character set = utf8mb4 collate = utf8mb4_general_ci comment = '服务器信息';
create table if not exists lb_server_group (
group_tag varchar(200) not null comment '组标签',
server_tag varchar(200) not null comment '服务器标签',
order_id smallint not null default 1 comment '排序ID',
create_time datetime not null comment '创建时间',
in_charge varchar(100) not null comment '负责人',
primary key (group_tag, server_tag),
constraint fk_serv_group_ref_serv_1 foreign key (group_tag) references lb_server (server_tag)
on delete restrict on update restrict,
constraint fk_serv_group_ref_serv_2 foreign key (server_tag) references lb_server (server_tag)
on delete restrict on update restrict
) ENGINE = InnoDB default character set = utf8mb4 collate = utf8mb4_general_ci comment = 'Server组与Sever关系';
-- drop table if exists lb_task;
create table if not exists lb_task (
task_id char(17) not null comment '任务ID',
task_type smallint not null comment '类型ID',
task_name varchar(200) not null comment '任务描述',
broker_ip varchar(20) not null default 'any' comment 'broker机器IP',
source_server varchar(200) not null comment '源Server',
target_server varchar(200) comment '目标Server',
cycle_unit char(1) not null comment '周期单位(M/W/D/H/I)',
cycle_num smallint not null default 1 comment '周期数',
start_date datetime not null comment '生效日期',
end_date datetime comment '失效日期',
self_depend smallint not null default 1 comment '自身依赖(1-时序/2-非时序/3-并行)',
task_action varchar(255) not null comment '任务执行目标',
delay_time int not null default 0 comment '延迟时间(分)',
startup_time smallint not null default 0 comment '启动时间(分)',
alive_wait smallint not null default 60 comment '最长存活等待(分钟)',
try_limit smallint not null default -1 comment '可尝试次数上限',
status char(1) not null default 'C' comment '状态(F:冻结/C:草稿/Y:正常/N:删除)',
task_priority smallint not null default 4 comment '优先级(0,1,2,3)，越小优先级越高',
task_group varchar(255) comment '所属组',
notes varchar(1000) comment '备注',
in_charge varchar(100) not null comment '负责人',
create_time datetime not null comment '创建时间',
last_update datetime not null default current_timestamp comment '最后修改时间',
primary key pk_task_id(task_id),
unique key uq_task_name(task_name),
constraint fk_task_ref_serv_1 foreign key (source_server) references lb_server (server_tag)
on delete restrict on update restrict,
constraint fk_task_ref_serv_2 foreign key (target_server) references lb_server (server_tag)
on delete restrict on update restrict,
constraint fk_task_ref_task_type foreign key (task_type) references lb_task_type (type_id)
on delete restrict on update restrict
) ENGINE = InnoDB default character set = utf8mb4 collate = utf8mb4_general_ci comment = '任务配置表';
-- drop table if exists lb_task_ext;
create table if not exists lb_task_ext (
task_id char(17) not null comment '任务ID',
prop_name varchar(255) not null comment '扩展属性名',
prop_value varchar(10240) comment '扩展属性值',
primary key (task_id, prop_name),
constraint fk_task_ext_ref_task foreign key (task_id) references lb_task (task_id)
on delete restrict on update restrict
) ENGINE = InnoDB default character set = utf8mb4 collate = utf8mb4_general_ci comment = '任务扩展属性';
-- drop table if exists lb_task_link;
create table if not exists lb_task_link (
task_from char(17) not null comment '前置任务ID',
task_to char(17) not null comment '后置任务ID',
dependence_type smallint not null default 1 comment '依赖类型(1-同周期,2-大依赖小,3-小依赖大)',
in_charge varchar(30) not null comment '负责人',
create_time datetime not null comment '创建时间',
status char(1) not null default 'Y' comment '状态',
primary key (task_from, task_to),
constraint fk_task_link_ref_task_1 foreign key (task_from) references lb_task (task_id)
on delete cascade on update cascade,
constraint fk_task_link_ref_task_2 foreign key (task_to) references lb_task (task_id)
on delete cascade on update cascade
) ENGINE = InnoDB default character set = utf8mb4 collate = utf8mb4_general_ci comment = '任务依赖关系表' ROW_FORMAT = Compact;
-- drop table if exists lb_task_run;
create table if not exists lb_task_run (
task_id char(17) not null comment '任务ID',
task_type smallint not null comment '任务类型(分区)',
cur_run_date datetime not null comment '当前运行时间',
next_run_date datetime not null comment '下一运行时间',
state smallint not null default 0 comment '状态',
redo_flag smallint not null default 0 comment '重做标记(1-重做,0-非重做)',
tries smallint not null default 0 comment '尝试次数',
try_limit smallint not null default -1 comment '可尝试次数上限',
start_time datetime comment '开始时间',
end_time datetime comment '截止时间',
run_priority smallint not null default 9 comment '运行优先级',
runtime_broker varchar(20) comment '运行时Broker',
runtime_id varchar(100) comment '运行时ID',
last_update datetime default current_timestamp comment '最后更新时间',
last_log varchar(4000) comment '最后日志',
create_time datetime comment '创建实际',
primary key (task_id, cur_run_date, task_type)
) ENGINE = InnoDB default character set = utf8mb4 collate = utf8mb4_general_ci comment = '任务运行实例表'
partition by list(task_type)(
partition p_1 values in (1),
partition p_2 values in (2)
);
-- drop table if exists lb_command;
create table if not exists lb_command(
command_id bigint unsigned not null auto_increment comment '命令ID',
command_name varchar(30) not null comment '命令名',
set_time datetime not null comment '设置时间',
setter varchar(20) not null comment '命令提交者',
task_id varchar(17) not null comment '任务ID',
date_from datetime comment '起始时间',
date_to datetime comment '截止时间',
param1 varchar(30) comment '参数1',
state smallint default 0 comment '状态',
start_time datetime comment '执行开始日期',
end_time datetime comment '执行截止日期',
exec_desc varchar(100) comment '执行描述',
last_update datetime comment '更新时间',
primary key (command_id)
) ENGINE = InnoDB AUTO_INCREMENT = 1 default character set = utf8mb4 collate = utf8mb4_general_ci comment = '命令执行表';
-- drop table if exists lb_parameter;
create table if not exists lb_parameter(
param_name varchar(100) not null comment '参数名',
param_desc varchar(500) comment '参数描述',
param_owner varchar(30) not null comment '参数所有者(BASE/RUNNER)',
param_value varchar(200) not null comment '参数值',
value_type varchar(10) not null comment '值类型',
in_charge varchar(100) not null comment '所有者',
create_time datetime not null comment '创建时间',
primary key (param_name)
) ENGINE = InnoDB default character set = utf8mb4 collate = utf8mb4_general_ci comment = '系统参数表';
-- drop table if exists lb_runner;
create table if not exists lb_runner (
type_id smallint not null comment '任务类型ID',
broker_ip varchar(30) not null comment '部署BrokerIP',
executable_path varchar(500) not null comment '可执行程序路径',
in_charge varchar(30) not null comment '负责人',
status char(1) not null default 'Y' comment '状态(Y-启用/N-未启用)',
startup_time datetime comment '启动时间',
last_heartbeat datetime comment '最后心跳时间',
priority_limit smallint not null default 9 comment '任务优先级上限',
primary key (type_id, broker_ip),
constraint fk_runner_ref_task_type foreign key (type_id) references lb_task_type (type_id)
on delete restrict on update restrict
) ENGINE = InnoDB default character set = utf8mb4 collate = utf8mb4_general_ci comment = '任务Runner部署及运行情况';
-- drop table if exists lb_run_stat;
create table if not exists lb_run_stat (
task_id char(17) not null comment '任务ID',
task_type smallint not null comment '任务类型',
cur_run_date datetime not null comment '统计日期',
order_id smallint not null comment '排序ID',
cycle_unit char(1) comment '任务周期',
in_charge varchar(100) not null comment '任务ID',
task_name varchar(200) comment '任务名',
duration_range_id smallint comment '运行时长范围ID',
duration_range varchar(100) not null comment '运行时长范围描述',
flutter_range_id smallint not null comment '波动率范围ID',
flutter_range varchar(100) not null comment '波动率范围描述',
duration float not null comment '运行时长(分钟)',
flutter float not null comment '波动率(百分比)',
last_update datetime not null comment '数据更新时间'
) ENGINE = InnoDB default character set = utf8mb4 collate = utf8mb4_general_ci comment = '运行统计明细'
partition by range(order_id) (
partition p_1 values less than (2),
partition p_max values less than (maxvalue)
);
create table if not exists lb_sys_log (
log_source varchar(100) comment '产生源',
log_tigger varchar(20) comment '触发者',
log_type varchar(10) comment '类型',
log_desc varchar(4000) comment '描述',
broker_ip varchar(30) comment 'BROKER地址',
task_type smallint comment '任务类型',
time_elapsed int comment '时间消耗(毫秒)',
log_time datetime comment '日志时间',
log_time_ms smallint comment '日志时间(毫秒)'
) ENGINE = InnoDB default character set = utf8mb4 collate = utf8mb4_general_ci comment = '系统日志'
partition by range columns(log_time)(
partition p19700101 values less than ('1970-01-02 00:00:00'),
partition p_max values less than (maxvalue)
);
-- drop table if exists lb_task_log;
create table if not exists lb_task_log (
task_id char(17) comment '任务ID',
cur_run_date datetime comment '运行日期',
next_run_date datetime comment '下一周期',
log_type varchar(10) comment '类型',
tries smallint comment '尝试次数',
state smallint comment '状态',
log_desc varchar(4000) comment '日志描述',
runtime_broker varchar(20) comment '运行时Broker',
runtime_id varchar(100) comment '运行时ID',
log_time datetime comment '日志时间',
log_time_ms smallint comment '日志时间(毫秒)',
key idx_task_id(task_id)
) ENGINE = InnoDB default character set = utf8mb4 collate = utf8mb4_general_ci comment = '任务运行日志'
partition by range columns(log_time)(
partition p19700101 values less than ('1970-01-02 00:00:00'),
partition p_max values less than (maxvalue)
);
-- drop table if exists lb_role_server;
create table if not exists lb_role_server (
role_name varchar(50) not null comment '角色名',
server_tag varchar(200) not null comment '服务器标识',
in_charge varchar(50) not null comment '负责人',
create_time datetime not null comment '创建时间',
primary key (role_name, server_tag),
constraint fk_role_serv_ref_serv foreign key (server_tag) references lb_server (server_tag)
on delete restrict on update restrict
) ENGINE = InnoDB default character set = utf8mb4 collate = utf8mb4_general_ci comment = '角色与server对照关系表';
-- drop table if exists lb_user_role;
create table if not exists lb_user_role (
user_name varchar(50) not null comment '用户名',
role_name varchar(50) not null comment '角色名',
server_tag varchar(200) comment '服务器标识',
in_charge varchar(50) not null comment '负责人',
create_time datetime not null comment '创建时间',
primary key (user_name, role_name),
constraint fk_user_role_ref_role foreign key (role_name, server_tag) references lb_role_server (role_name, server_tag)
on delete restrict on update restrict
) ENGINE = InnoDB default character set = utf8mb4 collate = utf8mb4_general_ci comment = '用户与角色对照关系表';
-- drop table if exists lb_user_server;
create table if not exists lb_user_server (
user_name varchar(50) not null comment '用户名',
server_tag varchar(200) not null comment '服务器标识',
in_charge varchar(50) not null comment '负责人',
create_time datetime not null comment '创建时间',
primary key (user_name, server_tag),
constraint fk_user_serv_ref_serv foreign key (server_tag) references lb_server (server_tag)
on delete restrict on update restrict
) ENGINE = InnoDB default character set = utf8mb4 collate = utf8mb4_general_ci comment = '用户与服务器对照关系表';
-- drop table if exists lb_view;
create table if not exists lb_view (
view_id char(14) not null comment '试图ID(yyyyMMddHHmmss)',
view_desc varchar(200) not null comment '试图描述',
in_charge varchar(30) not null comment '所有者',
create_time datetime not null comment '创建时间',
primary key (view_id)
) ENGINE = InnoDB default character set = utf8mb4 collate = utf8mb4_general_ci comment = '视图信息';
-- drop table if exists lb_view_task;
create table if not exists lb_view_task (
view_id char(14) not null comment '试图ID',
task_id char(17) not null comment '任务ID',
pos_x int not null comment 'X坐标',
pos_y int not null comment 'Y坐标',
in_charge varchar(30) not null comment '负责人',
create_time datetime not null comment '创建时间',
primary key (view_id, task_id),
constraint fk_task_view_ref_task foreign key (task_id) references lb_task (task_id)
on delete restrict on update restrict,
constraint fk_task_view_ref_view foreign key (view_id) references lb_view (view_id)
on delete restrict on update restrict
) ENGINE = InnoDB default character set = utf8mb4 collate = utf8mb4_general_ci comment = '视图与任务对照表';
create table if not exists t_data_conn (
f_id int not null AUTO_INCREMENT comment '集群id，自动增长',
f_name varchar(255) not null comment '集群名称，有前缀',
f_type varchar(255) not null comment '集群类型',
f_principal varchar(255) not null comment '集群负责人',
f_host varchar(255) comment '连接ip地址或域名',
f_port int comment '连接端口',
f_user varchar(255) comment '连接用户名',
f_password varchar(255) comment '加密密码',
primary key (f_id)
)
ENGINE=InnoDB
default character set=utf8mb4
collate=utf8mb4_unicode_ci
AUTO_INCREMENT=1000
comment='数据源连接信息';
SET FOREIGN_KEY_CHECKS = 1;
|