繁星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 PRIMARY KEY AUTO_INCREMENT,
	unid VARCHAR(36) NOT NULL DEFAULT (UUID()),
	task_id BIGINT COMMENT '任务id',
	event_cate VARCHAR(36),
	event_type VARCHAR(36),
	event_dt 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(36) COMMENT '事件唯一id' ,
	special_type VARCHAR(36),
	with_helmet INT COMMENT '是否戴头盔0不带',
	json_data TEXT COMMENT '原始数据',
	pic_name VARCHAR(256),
	video_name VARCHAR(256),
	status INT NOT NULL DEFAULT 0 COMMENT '状态'
);

-- d_traffic_face

CREATE TABLE IF NOT EXISTS d_traffic_face(
	id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	unid VARCHAR(36) NOT NULL DEFAULT (UUID()),
	traffic_id BIGINT NOT NULL COMMENT 'trafficId',
	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)
);

-- d_flow_data

CREATE TABLE IF NOT EXISTS d_flow_data(
	id BIGINT NOT NULL PRIMARY KEY 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 '状态'
);


-- d_flow_event

CREATE TABLE IF NOT EXISTS d_flow_event(
	id BIGINT NOT NULL PRIMARY KEY 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(36) COMMENT '事件唯一id',
	device_code VARCHAR(36),
	device_name VARCHAR(64),
	location_code VARCHAR(36),
	location_name VARCHAR(64),
	json_data TEXT,
	pic_name VARCHAR(256),
	video_name VARCHAR(256)
);


-- d_behavior

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

-- 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)
);