DC-SERVER/da/src/main/resources/sql/数据采集提测.sql

/** 字典表-编码类型表 **/
CREATE TABLE `da_dim_sdt_type` (
  `id` varchar(36) NOT NULL COMMENT '主键值为uuid',
  `sdt_type_code` varchar(225) DEFAULT '-1' COMMENT '类型编码。',
  `sdt_type_name` varchar(100) DEFAULT NULL COMMENT '类型名称',
  `sdt_type_desc` text COMMENT '类型描述',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='编码类型表';

/** 字典表-编码表 **/
CREATE TABLE `da_dim_sdt_code` (
  `id` varchar(36) NOT NULL COMMENT '主键值为uuid',
  `sdt_type_code` varchar(225) DEFAULT NULL COMMENT '类型编码。',
  `sdt_code` varchar(100) DEFAULT NULL COMMENT '编码',
  `sdt_name` text COMMENT '编码名称',
  `sdt_desc` text COMMENT '编码描述',
  `sdt_order` int(11) DEFAULT '-1' COMMENT '排序字段',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='编码表';

/** 初始化字段表数据 **/
insert into da_dim_sdt_type (id, sdt_type_code, sdt_type_name, sdt_type_desc, create_time, update_time)
values (uuid(), 'period_type', '周期类型', '周期类型', now(), now());

insert into da_dim_sdt_type (id, sdt_type_code, sdt_type_name, sdt_type_desc, create_time, update_time)
values (uuid(), 'task_status', '任务状态', '任务状态', now(), now());

insert into da_dim_sdt_type (id, sdt_type_code, sdt_type_name, sdt_type_desc, create_time, update_time)
values (uuid(), 'run_status', '运行状态', '运行状态', now(), now());

insert into da_dim_sdt_code (id, sdt_type_code, sdt_code, sdt_name, sdt_desc, sdt_order, create_time)
values (uuid(), (select id from da_dim_sdt_type where sdt_type_code='period_type'), '5', '秒', '秒', '6', now());
insert into da_dim_sdt_code (id, sdt_type_code, sdt_code, sdt_name, sdt_desc, sdt_order, create_time)
values (uuid(), (select id from da_dim_sdt_type where sdt_type_code='period_type'), '4', '分', '分', '5', now());
insert into da_dim_sdt_code (id, sdt_type_code, sdt_code, sdt_name, sdt_desc, sdt_order, create_time)
values (uuid(), (select id from da_dim_sdt_type where sdt_type_code='period_type'), '3', '时', '时', '4', now());
insert into da_dim_sdt_code (id, sdt_type_code, sdt_code, sdt_name, sdt_desc, sdt_order, create_time)
values (uuid(), (select id from da_dim_sdt_type where sdt_type_code='period_type'), '2', '日', '日', '3', now());
insert into da_dim_sdt_code (id, sdt_type_code, sdt_code, sdt_name, sdt_desc, sdt_order, create_time)
values (uuid(), (select id from da_dim_sdt_type where sdt_type_code='period_type'), '1', '月', '月', '2', now());
insert into da_dim_sdt_code (id, sdt_type_code, sdt_code, sdt_name, sdt_desc, sdt_order, create_time)
values (uuid(), (select id from da_dim_sdt_type where sdt_type_code='period_type'), '0', '自定义', '自定义', '1', now());

insert into da_dim_sdt_code (id, sdt_type_code, sdt_code, sdt_name, sdt_desc, sdt_order, create_time)
values (uuid(), (select id from da_dim_sdt_type where sdt_type_code='task_status'), '1', '启用', '启用', '1', now());
insert into da_dim_sdt_code (id, sdt_type_code, sdt_code, sdt_name, sdt_desc, sdt_order, create_time)
values (uuid(), (select id from da_dim_sdt_type where sdt_type_code='task_status'), '2', '停用', '停用', '2', now());

insert into da_dim_sdt_code (id, sdt_type_code, sdt_code, sdt_name, sdt_desc, sdt_order, create_time)
values (uuid(), (select id from da_dim_sdt_type where sdt_type_code='run_status'), '0', '未运行', '未运行', '1', now());
insert into da_dim_sdt_code (id, sdt_type_code, sdt_code, sdt_name, sdt_desc, sdt_order, create_time)
values (uuid(), (select id from da_dim_sdt_type where sdt_type_code='run_status'), '1', '等待运行', '等待运行', '2', now());
insert into da_dim_sdt_code (id, sdt_type_code, sdt_code, sdt_name, sdt_desc, sdt_order, create_time)
values (uuid(), (select id from da_dim_sdt_type where sdt_type_code='run_status'), '2', '运行中', '运行中', '3', now());
insert into da_dim_sdt_code (id, sdt_type_code, sdt_code, sdt_name, sdt_desc, sdt_order, create_time)
values (uuid(), (select id from da_dim_sdt_type where sdt_type_code='run_status'), '3', '运行完成', '运行完成', '4', now());
insert into da_dim_sdt_code (id, sdt_type_code, sdt_code, sdt_name, sdt_desc, sdt_order, create_time)
values (uuid(), (select id from da_dim_sdt_type where sdt_type_code='run_status'), '4', '运行失败', '运行失败', '5', now());

/** 数据采集任务信息表 **/
CREATE TABLE `da_info_data_collection_task` (
  `id` varchar(36) NOT NULL COMMENT 'uuid主键，任务id',
  `task_name` varchar(50) NOT NULL COMMENT '任务名称',
  `period_type` varchar(4) NOT NULL COMMENT '周期类型，0：自定义，1：月，2：日，3：时，4：分，5：秒',
  `schedule_offset` varchar(4) NOT NULL DEFAULT '-1' COMMENT '调度偏移量',
  `task_status` varchar(4) NOT NULL COMMENT '任务状态，1：启用，2：停用',
  `run_status` varchar(4) NOT NULL COMMENT '任务运行状态，0：未运行，1：等待运行，2：运行中，3：运行完成，4：运行失败',
  `task_cron` varchar(50) NOT NULL COMMENT '任务定时cron表达式',
  `next_run_date` datetime DEFAULT NULL COMMENT '下次运行时间',
  `ddl_sql` text NOT NULL COMMENT '数据采集ddl语句',
  `source_database_id` varchar(36) NOT NULL COMMENT '外键，源库id',
  `source_table_id` varchar(50) NOT NULL COMMENT '源表id',
  `source_table_name` varchar(50) NOT NULL COMMENT '源库表名',
  `target_database_id` varchar(36) NOT NULL COMMENT '外键，目标库id',
  `target_table_id` varchar(50) NOT NULL COMMENT '目标表id',
  `target_table_name` varchar(50) NOT NULL COMMENT '目标库表名',
  `user_id` varchar(30) NOT NULL COMMENT '操作人id',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='数据采集任务信息表';