繁星3.0.sql

-- s_task 任务信息表

CREATE TABLE IF NOT EXISTS s_task(
	id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	unid VARCHAR(36) NOT NULL DEFAULT (UUID()),
	name VARCHAR(128) NOT NULL COMMENT '任务名称',
	alg_type VARCHAR(36) NOT NULL COMMENT '算法类型',
	resource_need FLOAT NOT NULL COMMENT '任务所需资源数量',
	priority INT NOT NULL DEFAULT 0 COMMENT '任务优先级',
	store_config_id BIGINT NOT NULL COMMENT '存储配置id',
	va_type VARCHAR(36) COMMENT '算法分析设备类型',
	runtime_type INT NOT NULL DEFAULT 0 COMMENT '任务执行时间类型,全天分时段等',
	runtime_conf TEXT COMMENT '任务执行时间段的配置',
	device_unid VARCHAR(36) COMMENT '相机设备unid',
	channel_unid VARCHAR(36) COMMENT '相机通道unid',
	stream_path VARCHAR(1024) COMMENT '视频流地址',
	stream_type INT COMMENT '视频流类型',
	scene TEXT COMMENT '场景配置,包含画线预置位等',
	create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	status INT NOT NULL DEFAULT 0 COMMENT '任务状态',
	INDEX s_task_name(name)
);


-- s_store_config 存储配置表

CREATE TABLE IF NOT EXISTS s_store_config(
	id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	unid VARCHAR(36) NOT NULL DEFAULT (UUID()),
	name VARCHAR(256) NOT NULL COMMENT '存储配置名称',
	content_format INT NOT NULL COMMENT '配置内容格式,如xml',
	content TEXT NOT NULL COMMENT '配置内容',
	create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	status INT NOT NULL DEFAULT 0 COMMENT '状态'
);


-- s_forward 转发信息表

CREATE TABLE IF NOT EXISTS s_forward(
	id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	unid VARCHAR(36) NOT NULL DEFAULT (UUID()),
	name VARCHAR(256) NOT NULL COMMENT '转发名称',
	url VARCHAR(1024) NOT NULL COMMENT '转发地址',
	total BIGINT NOT NULL DEFAULT 0 COMMENT '转发数据总量',
	failed BIGINT NOT NULL DEFAULT 0 COMMENT '失败数量',
	last_send_time TIMESTAMP COMMENT '最后发送时间',
	create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	status INT NOT NULL DEFAULT 0 COMMENT '状态'
);


-- s_forward_config 转发配置表

CREATE TABLE IF NOT EXISTS s_forward_config(
	id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	unid VARCHAR(36) NOT NULL DEFAULT (UUID()),
	forward_id BIGINT NOT NULL COMMENT '转发信息id',
	event_cate VARCHAR(36) NOT NULL,
	event_type VARCHAR(36) NOT NULL
);


-- d_traffic

CREATE TABLE IF NOT EXISTS d_traffic(
	id BIGINT NOT NULL AUTO_INCREMENT,
	unid VARCHAR(36) NOT NULL DEFAULT (UUID()),
	task_id BIGINT COMMENT '任务id',
	event_cate VARCHAR(36),
	event_type VARCHAR(36),
	event_time TIMESTAMP NOT NULL COMMENT '事件时间',
	channel_unid VARCHAR(36) COMMENT '通道unid',
	plate_color VARCHAR(36) COMMENT '车牌颜色',
	plate_number VARCHAR(36) COMMENT '车牌号码',
	location_code VARCHAR(36) COMMENT '位置编码',
	location_name VARCHAR(500) COMMENT '位置名称',
	lane_code VARCHAR(36) COMMENT '车道编码',
	direction_code VARCHAR(36)  COMMENT '卡口方向',
	vehicle_type VARCHAR(36) COMMENT '车辆类型',
	vehicle_color VARCHAR(36) COMMENT '车身颜色',
	vehicle_logo VARCHAR(36) COMMENT '车标编码',
	illegal_code VARCHAR(36) COMMENT '违法行为',
	illegal_state INT COMMENT '违法行为是否可用',
	feature_annual_inspection_mark SMALLINT DEFAULT 0 COMMENT '年检标',
	feature_pendant SMALLINT DEFAULT 0 COMMENT '吊坠',
	feature_decoration SMALLINT DEFAULT 0 COMMENT '摆件',
	feature_sun_shield SMALLINT DEFAULT 0 COMMENT '遮阳板',
	xcycle_type VARCHAR(36) COMMENT '非机动车类型',
	event_id VARCHAR(64) COMMENT '事件唯一id' ,
	special_type VARCHAR(36),
	with_helmet INT COMMENT '是否戴头盔0不带',
	json_data TEXT COMMENT '原始数据',
	pics VARCHAR(2048),
	video_name VARCHAR(256),
	status INT NOT NULL DEFAULT 0 COMMENT '状态',
	PRIMARY KEY(id,event_time)
);

-- d_traffic_face

CREATE TABLE IF NOT EXISTS d_traffic_face(
	id BIGINT NOT NULL AUTO_INCREMENT,
	unid VARCHAR(36) NOT NULL DEFAULT (UUID()),
	traffic_id BIGINT NOT NULL COMMENT 'trafficId',
	event_time TIMESTAMP NOT NULL COMMENT '事件时间',
	state SMALLINT NOT NULL DEFAULT 1,
	sex SMALLINT NOT NULL DEFAULT 0,
	upper_color VARCHAR(36),
	lower_color VARCHAR(36),
	INDEX s_task_name(traffic_id),
	PRIMARY KEY(id,event_time)
);

-- d_flow_data

CREATE TABLE IF NOT EXISTS d_flow_data(
	id BIGINT NOT NULL AUTO_INCREMENT,
	unid VARCHAR(36) NOT NULL DEFAULT (UUID()),
	flow_event_id BIGINT COMMENT 'eventId',
	task_id BIGINT,
	detection_type VARCHAR(36) COMMENT '检测类型(bus,big_vehicle,xcycle等)',
	road_code VARCHAR(36) COMMENT '道路编码',
	direction_code VARCHAR(36) COMMENT '方向编码',
	sample_dura BIGINT,
	sample_num FLOAT,
	velocity FLOAT COMMENT '速率',
	velocity_unit VARCHAR(16) COMMENT '速率单位',
	occupy FLOAT COMMENT '车道占有率',
	distance FLOAT COMMENT '车头间距',
	queue_length FLOAT COMMENT '车辆排队长度',
	region_id VARCHAR(36),
	region_name VARCHAR(128),
	position_content TEXT,
	head_content TEXT,
	area FLOAT,
	density FLOAT,
	sample_num_in FLOAT,
	sample_num_out FLOAT,
	event_time TIMESTAMP,
	dist_time FLOAT,
	time_occupy FLOAT,
	status INT NOT NULL DEFAULT 0 COMMENT '状态',
	PRIMARY KEY(id,event_time)
);


-- d_flow_event

CREATE TABLE IF NOT EXISTS d_flow_event(
	id BIGINT NOT NULL AUTO_INCREMENT,
	unid VARCHAR(36) NOT NULL DEFAULT (UUID()),
	task_id BIGINT COMMENT '任务id',
	event_type VARCHAR(36),
	event_time TIMESTAMP COMMENT '事件时间',
	event_id VARCHAR(64) COMMENT '事件唯一id',
	device_code VARCHAR(36),
	device_name VARCHAR(64),
	location_code VARCHAR(36),
	location_name VARCHAR(64),
	json_data TEXT,
	pics VARCHAR(2048),
	video_name VARCHAR(256),
	PRIMARY KEY(id,event_time)
);


-- d_behavior

CREATE TABLE IF NOT EXISTS d_behavior(
	id BIGINT NOT NULL AUTO_INCREMENT,
	unid VARCHAR(36) NOT NULL DEFAULT (UUID()),
	task_id BIGINT,
	event_type VARCHAR(36),
	event_cate VARCHAR(36),
	event_refid VARCHAR(64),
	event_time TIMESTAMP,
	event_data TEXT,
	pics VARCHAR(2048),
	video TEXT,
	channel_unid VARCHAR(36),
	task_name VARCHAR(128),
	event_data_num VARCHAR(36),
	PRIMARY KEY(id,event_time)
);

-- s_dict_cate

CREATE TABLE IF NOT EXISTS s_dict_cate(
	id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	unid VARCHAR(36) NOT NULL DEFAULT (UUID()),
	type VARCHAR(36) COMMENT '分类'
	code VARCHAR(36) COMMENT 'cate编码',
	name VARCHAR(36) NOT NULL COMMENT 'cate名称',
	note VARCHAR(36) COMMENT '注释'
);


--  s_dict_code

CREATE TABLE IF NOT EXISTS s_dict_code(
	id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	unid VARCHAR(36) NOT NULL DEFAULT (UUID()),
	cate_id BIGINT NOT NULL,
	parent_id BIGINT COMMENT '上级code的id',
	code VARCHAR(36) COMMENT 'code编码',
	name VARCHAR(36) NOT NULL COMMENT 'code名称',
	note VARCHAR(36) COMMENT '注释',
	INDEX s_dict_code(cate_id)
);

--  s_channel

CREATE TABLE IF NOT EXISTS s_channel(
	id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	unid VARCHAR(36) NOT NULL DEFAULT (UUID()),
	channel_unid VARCHAR(64) NOT NULL,
	device_unid VARCHAR(64) NOT NULL,
	type INT NOT NULL default 0,
	stream_type INT,
	stream_path VARCHAR(1024),
	username VARCHAR(36),
	password VARCHAR(64),
	address_unid VARCHAR(36),
	name VARCHAR(128),
	ip VARCHAR(36),
	port INT,
	expand VARCHAR(128),
	longitude FLOAT,
	latitude FLOAT,
	status INT NOT NULL DEFAULT 0,
	INDEX s_channel(channel_unid)
);




-- 分表
alter TABLE d_traffic PARTITION by RANGE(UNIX_TIMESTAMP(event_time))(
		PARTITION d_traffic20210701 VALUES less than (UNIX_TIMESTAMP('2021-07-02'))
);
alter TABLE d_traffic_face PARTITION by RANGE(UNIX_TIMESTAMP(event_time))(
		PARTITION d_traffic_face20210701 VALUES less than (UNIX_TIMESTAMP('2021-07-02'))
);
alter TABLE d_flow_event PARTITION by RANGE(UNIX_TIMESTAMP(event_time))(
		PARTITION d_flow_event20210701 VALUES less than (UNIX_TIMESTAMP('2021-07-02'))
);
alter TABLE d_flow_data PARTITION by RANGE(UNIX_TIMESTAMP(event_time))(
		PARTITION d_flow_data20210701 VALUES less than (UNIX_TIMESTAMP('2021-07-02'))
);
alter TABLE d_behavior PARTITION by RANGE(UNIX_TIMESTAMP(event_time))(
		PARTITION d_behavior20210701 VALUES less than (UNIX_TIMESTAMP('2021-07-02'))
);

delimiter $$
DROP PROCEDURE IF EXISTS timestamp_partition 
$$
CREATE PROCEDURE timestamp_partition(v_table_name VARCHAR(128),v_schema VARCHAR(128))
BEGIN
  DECLARE v_sysdate date; # 声明 当前时间
  DECLARE v_mindate date; # 声明 目前分区的最小值
  DECLARE v_maxdate date; # 声明 目前分区值中的最大值
  DECLARE v_pt varchar(20); # 声明 分区名称 数字部分
  DECLARE v_maxval varchar(20); # 声明 最大值
  DECLARE i int; # 声明 ??
  
  -- 增加新分区
  SELECT max(CAST(FROM_UNIXTIME(partition_description) as date)) AS val
  INTO   v_maxdate
  FROM   INFORMATION_SCHEMA.PARTITIONS
  WHERE  TABLE_NAME = v_table_name AND TABLE_SCHEMA = v_schema;
  
  set v_sysdate = sysdate(); # 赋值v_sysdate为当前时间
  
  -- INTERVAL 时间计算的关键字
  WHILE v_maxdate <= (v_sysdate + INTERVAL 7 DAY) DO
    SET v_pt = date_format(v_maxdate ,'%Y%m%d');
    SET v_maxval = date_format(v_maxdate + INTERVAL 1 DAY, '%Y-%m-%d');
    SET @sql = concat('alter table ',v_table_name,' add partition (partition ',v_table_name, v_pt, ' values less than(UNIX_TIMESTAMP(''',  v_maxval , ''')))');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    -- 删除预处理
    DEALLOCATE PREPARE stmt;
    SET v_maxdate = v_maxdate + INTERVAL 1 DAY; # 最大值 加一操作
  END WHILE;
	
	
	-- 删除旧分区
  SELECT min(CAST(FROM_UNIXTIME(partition_description) as date)) AS val
  INTO   v_mindate
  FROM   INFORMATION_SCHEMA.PARTITIONS
  WHERE  TABLE_NAME = v_table_name AND TABLE_SCHEMA = v_schema;
    
  -- 删除7天前的旧分区
  WHILE v_mindate <= (v_sysdate - INTERVAL 7 DAY) DO
    SET v_pt = date_format(v_mindate - INTERVAL 1 DAY,'%Y%m%d');
    SET @sql = concat('alter table ',v_table_name,' drop partition ',v_table_name, v_pt);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET v_mindate = v_mindate + INTERVAL 1 DAY;
  END WHILE;
	
 
END$$
delimiter ;

-- 创建定时任务
delimiter $$
drop event if exists auto_pt $$
create event auto_pt
on schedule
every 1 day
do
BEGIN
    call timestamp_partition('d_traffic','{schema}');
    call timestamp_partition('d_behavior','{schema}');
    call timestamp_partition('d_flow_event','{schema}');
    call timestamp_partition('d_flow_data','{schema}');
    call timestamp_partition('d_traffic_face','{schema}');
END$$
delimiter ;