CREATE database if NOT EXISTS `iailab_expert_tenant_zjgt` default character set utf8mb4 collate utf8mb4_general_ci; USE `iailab_expert_tenant_zjgt`; -- data CREATE TABLE `t_channel_opcda_device` ( `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'id', `server_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '服务名', `host` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT 'ip', `user` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '用户名', `password` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '密码', `prog_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '设备名', `cls_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '设备注册表ID', `create_time` datetime DEFAULT NULL COMMENT '创建时间', `update_time` datetime DEFAULT NULL COMMENT '更新时间', PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `uk_server_name` (`server_name`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='OPCDA表'; CREATE TABLE `t_channel_opcda_tag` ( `id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'id', `server_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '服务id', `tag_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '测点名称', `data_type` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '数据类型', `enabled` tinyint(1) DEFAULT NULL COMMENT '是否启用', `item_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT 'itemId', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='opcda测点表'; CREATE TABLE `t_channel_opcua_device` ( `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `server_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `endpoint_url` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `security_policy` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `security_mode` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `connection_type` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `user_name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `password` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `certificate_path` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `connect_inactivity_timeout` int DEFAULT NULL, `reconnect_interval` int DEFAULT NULL, `creator` bigint DEFAULT NULL, `create_time` datetime DEFAULT NULL, `updater` bigint DEFAULT NULL, `update_time` datetime DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `uk_server_name` (`server_name`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='OPCUA表'; CREATE TABLE `t_channel_opcua_tag` ( `id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `tag_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '测点名称', `data_type` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '数据类型', `enabled` tinyint(1) DEFAULT NULL COMMENT '是否启用', `device` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '关联设备', `address` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'opcda地址', `sampling_rate` smallint unsigned DEFAULT NULL COMMENT '采集频率', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`id`) USING BTREE, KEY `idx_device` (`device`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='opcda测点表'; CREATE TABLE `t_channel_modbus_device` ( `id` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '设备名称', `address` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'IP地址', `port` smallint unsigned NOT NULL COMMENT '端口', `connect_inactivity_timeout` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '不活动超时(ms)', `reconnect_interval` smallint unsigned DEFAULT NULL COMMENT '重连超时(ms)', `attempts_before_timeout` smallint unsigned DEFAULT NULL COMMENT '重试次数', `wait_to_retry_milliseconds` smallint unsigned DEFAULT NULL COMMENT '重试间隔(ms)', `read_timeout` smallint unsigned DEFAULT NULL COMMENT '读超时(ms)', `write_timeout` smallint unsigned DEFAULT NULL COMMENT '写超时(ms)', `use_optimized_block_read` tinyint(1) DEFAULT NULL COMMENT '是否使用优化', `project_reference` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '系统内部用，默认空字符串', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `uk_name` (`name`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='ModBus表'; CREATE TABLE `t_channel_modbus_tag` ( `id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `tag_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '测点名称', `data_type` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '数据类型', `enabled` tinyint(1) DEFAULT NULL COMMENT '是否启用', `format` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '大小端', `device` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '关联设备', `address` varchar(6) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'Modbus地址', `sampling_rate` smallint unsigned DEFAULT NULL COMMENT '采集频率', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `tag_desc` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '描述', PRIMARY KEY (`id`) USING BTREE, KEY `idx_device` (`device`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='ModBus测点表'; CREATE TABLE `t_channel_kio_device` ( `id` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `instance_name` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '实例名称', `address` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'IP地址', `port` smallint unsigned NOT NULL COMMENT '端口', `username` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名', `password` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '密码', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `uk_instance_name` (`instance_name`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='KIO实例表'; CREATE TABLE `t_channel_kio_tag` ( `id` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `tag_name` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '测点名称', `data_type` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '数据类型', `tag_id` int default NULL COMMENT '顺序号', `tag_desc` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci default NULL COMMENT '测点描述', `enabled` tinyint(1) DEFAULT NULL COMMENT '是否启用', `device` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '关联设备', `sampling_rate` smallint unsigned DEFAULT NULL COMMENT '采集频率', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`id`) USING BTREE, KEY `idx_device` (`device`) USING BTREE, KEY `idx_tag_name` (`tag_name`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='KIO测点表'; CREATE TABLE t_da_point( `id` VARCHAR(255) NOT NULL COMMENT 'ID' , `point_no` VARCHAR(36) COMMENT '测点编码' , `point_name` VARCHAR(36) COMMENT '测点名称' , `point_type` VARCHAR(10) COMMENT '测点类型' , `data_type` VARCHAR(10) COMMENT '数据类型' , `value_type` VARCHAR(10) COMMENT '值类型' , `store_type` VARCHAR(10) COMMENT '存储类型' , `unit` VARCHAR(36) COMMENT '测量单位' , `unittransfactor` DECIMAL(24,6) COMMENT '单位转换' , `default_value` DECIMAL(24,6) COMMENT '默认值' , `max_value` DECIMAL(24,6) COMMENT '最大值' , `min_value` DECIMAL(24,6) COMMENT '最小值' , `offset_value` DECIMAL(24,6) COMMENT '偏移量' , `minfreqid` VARCHAR(10) COMMENT '采集频率' , `remark` VARCHAR(255) COMMENT '备注' , `is_enable` tinyint(1) COMMENT '是否启用' , `creator` VARCHAR(255) COMMENT '创建人' , `create_time` DATETIME COMMENT '创建时间' , `updater` VARCHAR(255) COMMENT '修改人' , `update_time` DATETIME COMMENT '修改时间' , PRIMARY KEY (id) USING BTREE, UNIQUE KEY `uk_point_no` (`point_no`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '测点表;'; CREATE TABLE t_da_measure_point( `id` VARCHAR(36) NOT NULL COMMENT 'ID' , `point_id` VARCHAR(36) COMMENT '测点ID' , `source_type` VARCHAR(10) COMMENT '数据源类型', `source_id` VARCHAR(36) COMMENT '数据源ID', `tag_no` VARCHAR(64) COMMENT '测点Tag', `dimension` INT COMMENT '平滑尺度(min)' , `value_type` VARCHAR(36) COMMENT '值类型(数字量，模拟量)', PRIMARY KEY (id) USING BTREE, UNIQUE KEY `uk_point_id` (`point_id`) USING BTREE, KEY `uk_tag_no` (`tag_no`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '测量点表'; CREATE TABLE t_da_math_point( `id` VARCHAR(36) NOT NULL COMMENT 'ID' , `point_id` VARCHAR(36) COMMENT '测点ID' , `expression` VARCHAR(500) COMMENT '计算公式' , PRIMARY KEY (id) USING BTREE, UNIQUE KEY `uk_point_id` (`point_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '计算点表'; CREATE TABLE `t_da_point_value` ( `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'id', `point_no` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '测点编码', `data_time` datetime DEFAULT NULL COMMENT '数据时间', `data_value` decimal(24,6) DEFAULT NULL COMMENT '数据值', PRIMARY KEY (`id`) USING BTREE, KEY `idx_point_no` (`point_no`) USING BTREE, KEY `idx_data_time` (`data_time`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='点值表'; CREATE TABLE t_da_sequence_num( `id` VARCHAR(36) NOT NULL COMMENT 'ID' , `code` VARCHAR(36) COMMENT '编号', `name` VARCHAR(36) COMMENT '名称', `sequence_num` int COMMENT '序号' , `prefix` VARCHAR(10) COMMENT '前缀' , PRIMARY KEY (id) USING BTREE, UNIQUE KEY `uk_code` (`code`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '序号表'; INSERT INTO `t_da_sequence_num` (`id`, `code`, `name`, `sequence_num`, `prefix`) VALUES ('1', 'POINT_M', '测量点编码', 100000, 'M'); INSERT INTO `t_da_sequence_num` (`id`, `code`, `name`, `sequence_num`, `prefix`) VALUES ('2', 'POINT_C', '计算点编码', 100000, 'C'); CREATE TABLE `t_http_api` ( `id` varchar(36) NOT NULL COMMENT 'ID', `code` varchar(32) NOT NULL COMMENT '编码', `name` varchar(32) NOT NULL COMMENT '名称', `url` varchar(100) NOT NULL COMMENT 'URL', `method` varchar(10) NULL DEFAULT NULL COMMENT '请求方法', `param` varchar(200) NULL DEFAULT NULL COMMENT '参数', `descp` varchar(100) NULL DEFAULT NULL COMMENT '描述', `is_auth` tinyint(1) NOT NULL COMMENT '是否认证', `auth_url` varchar(36) NULL DEFAULT NULL COMMENT '认证地址', `creator` VARCHAR(64) COMMENT '创建人' , `create_time` DATETIME COMMENT '创建时间' , `updater` VARCHAR(64) COMMENT '修改人' , `update_time` DATETIME COMMENT '修改时间' , PRIMARY KEY (`ID`) USING BTREE, UNIQUE KEY `uk_code` (`code`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = 'Api接口' ROW_FORMAT = DYNAMIC; CREATE TABLE `t_http_token` ( `id` varchar(36) NOT NULL COMMENT 'ID', `login_url` varchar(200) DEFAULT NULL COMMENT '登录地址', `client_id` varchar(100) DEFAULT NULL COMMENT 'ClientId', `client_secret` varchar(100) DEFAULT NULL COMMENT 'ClientSecret', `username` varchar(50) DEFAULT NULL COMMENT '用户名', `password` varchar(50) DEFAULT NULL COMMENT '密码', `refresh_freq` varchar(10) DEFAULT NULL COMMENT '刷新频率', `token` varchar(2000) DEFAULT NULL COMMENT 'token', `expire_time` datetime DEFAULT NULL COMMENT '过期时间', `update_time` datetime DEFAULT NULL COMMENT '更新时间', PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `uk_login_url` (`login_url`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='接口Token'; CREATE TABLE `t_http_tag` ( `id` varchar(36) NOT NULL COMMENT 'ID', `api_id` varchar(36) NOT NULL COMMENT '接口ID', `tag_name` varchar(64) NOT NULL COMMENT '标签名称', `data_type` varchar(16) NOT NULL COMMENT '数据类型', `enabled` tinyint(1) DEFAULT NULL COMMENT '是否启用', `tag_desc` varchar(64) DEFAULT NULL COMMENT '描述', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT='接口Tag表'; -- 指标管理------------ CREATE TABLE t_ind_data_set( `id` VARCHAR(36) NOT NULL COMMENT 'ID' , `name` VARCHAR(30) NOT NULL COMMENT '名称' , `data_source` VARCHAR(64) NOT NULL COMMENT '数据源ID', `query_sql` VARCHAR(200) NOT NULL COMMENT '查询语句', `remark` VARCHAR(100) COMMENT '备注', `sort` int COMMENT '排序', `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (id) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '指标数据集'; CREATE TABLE t_ind_data_set_field( `id` VARCHAR(36) NOT NULL COMMENT 'ID' , `data_set_id` VARCHAR(36) NOT NULL COMMENT '数据集ID' , `field_code` VARCHAR(30) COMMENT '英文名' , `field_name` VARCHAR(30) COMMENT '中文名' , `field_type` VARCHAR(30) COMMENT '数据类型' , `sort` int COMMENT '排序', PRIMARY KEY (id) USING BTREE, INDEX `idx_data_set_id` (`data_set_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '指标数据集字段'; CREATE TABLE t_ind_item_category( `id` VARCHAR(36) NOT NULL COMMENT 'ID' , `label` VARCHAR(20) COMMENT '标签' , `pid` VARCHAR(36) COMMENT '父ID', `sort` int COMMENT '排序', `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (id) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '指标分类'; CREATE TABLE t_ind_item( `id` VARCHAR(36) NOT NULL COMMENT 'ID' , `item_no` VARCHAR(36) COMMENT '指标编码' , `item_name` VARCHAR(36) COMMENT '指标名称' , `item_type` VARCHAR(20) COMMENT '指标类型' , `item_category` VARCHAR(36) COMMENT '指标分类' , `coefficient` decimal(10, 4) COMMENT '转换系数', `precision` int COMMENT '指标精度', `time_granularity` VARCHAR(10) COMMENT '时间粒度' , `unit` VARCHAR(10) COMMENT '数量单位' , `remark` VARCHAR(255) COMMENT '备注' , `status` tinyint NOT NULL DEFAULT 0 COMMENT '状态（0正常 1停用）', `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (id) USING BTREE, UNIQUE INDEX `uk_item_no` (`item_no`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '指标项表'; CREATE TABLE t_ind_item_atom( `id` VARCHAR(36) NOT NULL COMMENT 'ID' , `item_id` VARCHAR(36) COMMENT '指标ID' , `data_source` VARCHAR(64) COMMENT '数据源', `data_set` VARCHAR(64) COMMENT '数据集', `using_field` VARCHAR(64) COMMENT '使用字段', `stat_func` VARCHAR(64) COMMENT '统计方式', PRIMARY KEY (id) USING BTREE, UNIQUE KEY `uk_item_id` (`item_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '原子指标'; CREATE TABLE t_ind_item_der( `id` VARCHAR(36) NOT NULL COMMENT 'ID' , `item_id` VARCHAR(36) COMMENT '指标ID' , `atom_item_id` VARCHAR(36) COMMENT '原子指标ID' , `time_label` VARCHAR(20) COMMENT '时间标识', `time_limit` VARCHAR(20) COMMENT '时间限定', `time_start` DATETIME COMMENT '开始时间', `time_end` DATETIME COMMENT '结束时间', `dimension` VARCHAR(200) COMMENT '分析维度', PRIMARY KEY (id) USING BTREE, UNIQUE KEY `uk_item_id` (`item_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '派生指标'; CREATE TABLE t_ind_item_cal( `id` VARCHAR(36) NOT NULL COMMENT 'ID' , `item_id` VARCHAR(36) COMMENT '指标ID' , `expression` varchar(100) DEFAULT NULL COMMENT '计算表达式', PRIMARY KEY (id) USING BTREE, UNIQUE KEY `uk_item_id` (`item_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '复合指标'; CREATE TABLE `t_ind_item_value` ( `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'id', `item_no` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '指标编码', `data_time` varchar(20) DEFAULT NULL COMMENT '数据时间', `data_value` decimal(24,6) DEFAULT NULL COMMENT '数据值', PRIMARY KEY (`id`) USING BTREE, KEY `idx_item_no` (`item_no`) USING BTREE, KEY `idx_data_time` (`data_time`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='指标值表'; -- 计划数据------------ CREATE TABLE t_plan_data_set( `id` VARCHAR(36) NOT NULL COMMENT 'ID' , `name` VARCHAR(30) NOT NULL COMMENT '名称' , `data_source` VARCHAR(64) NOT NULL COMMENT '数据源ID', `query_sql` VARCHAR(300) NOT NULL COMMENT '查询语句', `remark` VARCHAR(100) COMMENT '备注', `sort` int COMMENT '排序', `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (id) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '计划数据集'; CREATE TABLE t_plan_item_category( `id` VARCHAR(36) NOT NULL COMMENT 'ID' , `label` VARCHAR(20) COMMENT '标签' , `pid` VARCHAR(36) COMMENT '父ID', `sort` int COMMENT '排序', `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (id) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '计划数据分类'; CREATE TABLE t_plan_item( `id` VARCHAR(36) NOT NULL COMMENT 'ID' , `item_no` VARCHAR(36) COMMENT '指标编码' , `item_name` VARCHAR(36) COMMENT '指标名称' , `item_category` VARCHAR(36) COMMENT '指标分类', `time_granularity` VARCHAR(10) COMMENT '时间粒度', `data_set` VARCHAR(64) COMMENT '数据集', `remark` VARCHAR(255) COMMENT '备注' , `status` tinyint NOT NULL DEFAULT 0 COMMENT '状态（0正常 1停用）', `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (id) USING BTREE, UNIQUE INDEX `uk_item_no` (`item_no`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '计划数据项'; CREATE TABLE t_da_cumulate_point( `id` VARCHAR(36) NOT NULL COMMENT 'ID' , `point_id` VARCHAR(36) COMMENT '测点ID' , `point_no` VARCHAR(36) COMMENT '瞬时测点' , `length` int COMMENT '累计长度', `divisor` int COMMENT '除数', PRIMARY KEY (id) USING BTREE, UNIQUE KEY `uk_point_id` (`point_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '累计点表'; INSERT INTO `iailab_expert_tenant_shasteel`.`t_da_sequence_num` (`id`, `code`, `name`, `sequence_num`, `prefix`) VALUES ('8', 'POINT_L', '累计点编码', 100001, 'L'); INSERT INTO `iailab_plat_system`.`system_dict_data` (`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES (1657, 4, '累计点', 'CUMULATE', 'data_point_type', 0, '', '', '', '142', '2024-11-29 10:13:12', '142', '2024-11-29 10:13:12', b'0');