Jay
2024-10-08 79914dabac38d83676ea16ff65da8d941a099285
提交 | 用户 | 时间
0fbd01 1 CREATE database if NOT EXISTS `iailab_expert_tenant_zjgt` default character set utf8mb4 collate utf8mb4_general_ci;
2 USE `iailab_expert_tenant_zjgt`;
3
4 -- data
5 CREATE TABLE `t_channel_opcda_device` (
6                                         `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'id',
7                                         `server_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '服务名',
8                                         `host` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT 'ip',
9                                         `user` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '用户名',
10                                         `password` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '密码',
11                                         `prog_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '设备名',
12                                         `cls_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '设备注册表ID',
13                                         `create_time` datetime DEFAULT NULL COMMENT '创建时间',
14                                         `update_time` datetime DEFAULT NULL COMMENT '更新时间',
15                                         PRIMARY KEY (`id`) USING BTREE,
16                                         UNIQUE KEY `uk_server_name` (`server_name`) USING BTREE
17 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='OPCDA表';
18
19 CREATE TABLE `t_channel_opcda_tag` (
20                                      `id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'id',
21                                      `server_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '服务id',
22                                      `tag_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '测点名称',
23                                      `data_type` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '数据类型',
24                                      `enabled` tinyint(1) DEFAULT NULL COMMENT '是否启用',
25                                      `item_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT 'itemId',
26                                      `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
27                                      `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
28                                      PRIMARY KEY (`id`) USING BTREE
29 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='opcda测点表';
30
31 CREATE TABLE `t_channel_opcua_device` (
32                                         `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
33                                         `server_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
34                                         `endpoint_url` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
35                                         `security_policy` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
36                                         `security_mode` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
37                                         `connection_type` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
38                                         `user_name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
39                                         `password` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
40                                         `certificate_path` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
41                                         `connect_inactivity_timeout` int DEFAULT NULL,
42                                         `reconnect_interval` int DEFAULT NULL,
43                                         `creator` bigint DEFAULT NULL,
44                                         `create_time` datetime DEFAULT NULL,
45                                         `updater` bigint DEFAULT NULL,
46                                         `update_time` datetime DEFAULT NULL,
47                                         PRIMARY KEY (`id`) USING BTREE,
48                                         UNIQUE KEY `uk_server_name` (`server_name`) USING BTREE
49 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='OPCUA表';
50
51 CREATE TABLE `t_channel_opcua_tag` (
52                                      `id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
53                                      `tag_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '测点名称',
54                                      `data_type` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '数据类型',
55                                      `enabled` tinyint(1) DEFAULT NULL COMMENT '是否启用',
56                                      `device` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '关联设备',
57                                      `address` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'opcda地址',
58                                      `sampling_rate` smallint unsigned DEFAULT NULL COMMENT '采集频率',
59                                      `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
60                                      `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
61                                      PRIMARY KEY (`id`) USING BTREE,
62                                      KEY `idx_device` (`device`) USING BTREE
63 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='opcda测点表';
64
65 CREATE TABLE `t_channel_modbus_device` (
66                                          `id` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
67                                          `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '设备名称',
68                                          `address` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'IP地址',
69                                          `port` smallint unsigned NOT NULL COMMENT '端口',
70                                          `connect_inactivity_timeout` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '不活动超时(ms)',
71                                          `reconnect_interval` smallint unsigned DEFAULT NULL COMMENT '重连超时(ms)',
72                                          `attempts_before_timeout` smallint unsigned DEFAULT NULL COMMENT '重试次数',
73                                          `wait_to_retry_milliseconds` smallint unsigned DEFAULT NULL COMMENT '重试间隔(ms)',
74                                          `read_timeout` smallint unsigned DEFAULT NULL COMMENT '读超时(ms)',
75                                          `write_timeout` smallint unsigned DEFAULT NULL COMMENT '写超时(ms)',
76                                          `use_optimized_block_read` tinyint(1) DEFAULT NULL COMMENT '是否使用优化',
77                                          `project_reference` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '系统内部用,默认空字符串',
78                                          `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
79                                          `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
80                                          PRIMARY KEY (`id`) USING BTREE,
81                                          UNIQUE KEY `uk_name` (`name`) USING BTREE
82 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='ModBus表';
83
84 CREATE TABLE `t_channel_modbus_tag` (
85                                       `id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
86                                       `tag_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '测点名称',
87                                       `data_type` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '数据类型',
88                                       `enabled` tinyint(1) DEFAULT NULL COMMENT '是否启用',
89                                       `format` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '大小端',
90                                       `device` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '关联设备',
91                                       `address` varchar(6) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 'Modbus地址',
92                                       `sampling_rate` smallint unsigned DEFAULT NULL COMMENT '采集频率',
93                                       `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
94                                       `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
95                                       `tag_desc` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '描述',
96                                       PRIMARY KEY (`id`) USING BTREE,
97                                       KEY `idx_device` (`device`) USING BTREE
98 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='ModBus测点表';
99
100
101 CREATE TABLE `t_channel_kio_device` (
102                                       `id` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
103                                       `instance_name` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '实例名称',
104                                       `address` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'IP地址',
105                                       `port` smallint unsigned NOT NULL COMMENT '端口',
106                                       `username` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',
107                                       `password` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '密码',
108                                       `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
109                                       `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
110                                       PRIMARY KEY (`id`) USING BTREE,
111                                       UNIQUE KEY `uk_instance_name` (`instance_name`) USING BTREE
112 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='KIO实例表';
113
114
115 CREATE TABLE `t_channel_kio_tag` (
116                                    `id` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
117                                    `tag_name` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '测点名称',
118                                    `data_type` varchar(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '数据类型',
3c10e5 119                                    `tag_id` int default NULL COMMENT '顺序号',
0fbd01 120                                    `tag_desc` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci default NULL COMMENT '测点描述',
121                                    `enabled` tinyint(1) DEFAULT NULL COMMENT '是否启用',
122                                    `device` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '关联设备',
123                                    `sampling_rate` smallint unsigned DEFAULT NULL COMMENT '采集频率',
124                                    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
125                                    `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
126                                    PRIMARY KEY (`id`) USING BTREE,
127                                    KEY `idx_device` (`device`) USING BTREE,
128                                    KEY `idx_tag_name` (`tag_name`) USING BTREE
129 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='KIO测点表';
130
131 CREATE TABLE t_da_point(
132                          `id` VARCHAR(255) NOT NULL  COMMENT 'ID' ,
133                          `point_no` VARCHAR(36)   COMMENT '测点编码' ,
134                          `point_name` VARCHAR(36)   COMMENT '测点名称' ,
135                          `point_type` VARCHAR(10)   COMMENT '测点类型' ,
136                          `data_type` VARCHAR(10)   COMMENT '数据类型' ,
137                          `value_type` VARCHAR(10)   COMMENT '值类型' ,
138                          `store_type` VARCHAR(10)   COMMENT '存储类型' ,
139                          `unit` VARCHAR(36)   COMMENT '测量单位' ,
140                          `unittransfactor` DECIMAL(24,6)   COMMENT '单位转换' ,
141                          `default_value` DECIMAL(24,6)   COMMENT '默认值' ,
142                          `max_value` DECIMAL(24,6)   COMMENT '最大值' ,
143                          `min_value` DECIMAL(24,6)   COMMENT '最小值' ,
144                          `offset_value` DECIMAL(24,6)   COMMENT '偏移量' ,
145                          `minfreqid` VARCHAR(10)   COMMENT '采集频率' ,
146                          `remark` VARCHAR(255)   COMMENT '备注' ,
147                          `is_enable` tinyint(1)   COMMENT '是否启用' ,
148                          `creator` VARCHAR(255)   COMMENT '创建人' ,
149                          `create_time` DATETIME   COMMENT '创建时间' ,
150                          `updater` VARCHAR(255)   COMMENT '修改人' ,
151                          `update_time` DATETIME   COMMENT '修改时间' ,
152                          PRIMARY KEY (id) USING BTREE,
153                          UNIQUE KEY `uk_point_no` (`point_no`) USING BTREE
154 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '测点表;';
155
156 CREATE TABLE t_da_measure_point(
157                                  `id` VARCHAR(36) NOT NULL  COMMENT 'ID' ,
158                                  `point_id` VARCHAR(36)   COMMENT '测点ID' ,
159                                  `source_type` VARCHAR(10)   COMMENT '数据源类型',
160                                  `source_id` VARCHAR(36)   COMMENT '数据源ID',
161                                  `tag_no` VARCHAR(64)   COMMENT '测点Tag',
162                                  `dimension` INT   COMMENT '尺度' ,
163                                  PRIMARY KEY (id) USING BTREE,
164                                  UNIQUE KEY `uk_point_id` (`point_id`) USING BTREE,
165                                  KEY `uk_tag_no` (`tag_no`) USING BTREE
166 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '测量点表';
167
168 CREATE TABLE t_da_math_point(
169                               `id` VARCHAR(36) NOT NULL  COMMENT 'ID' ,
170                               `point_id` VARCHAR(36)   COMMENT '测点ID' ,
171                               `expression` VARCHAR(500)   COMMENT '计算公式' ,
172                               PRIMARY KEY (id) USING BTREE,
173                               UNIQUE KEY `uk_point_id` (`point_id`) USING BTREE
174 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '计算点表';
175
176 CREATE TABLE `t_da_point_value` (
177                                   `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'id',
178                                   `point_no` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '测点编码',
179                                   `data_time` datetime DEFAULT NULL COMMENT '数据时间',
180                                   `data_value` decimal(24,6) DEFAULT NULL COMMENT '数据值',
181                                   PRIMARY KEY (`id`) USING BTREE,
182                                   KEY `idx_point_no` (`point_no`) USING BTREE,
183                                   KEY `idx_data_time` (`data_time`) USING BTREE
184 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='点值表';
185
186 CREATE TABLE t_da_sequence_num(
187                                 `id` VARCHAR(36) NOT NULL  COMMENT 'ID' ,
188                                 `code` VARCHAR(36)   COMMENT '编号',
189                                 `name` VARCHAR(36)   COMMENT '名称',
190                                 `sequence_num` int   COMMENT '序号' ,
191                                 `prefix` VARCHAR(10) COMMENT '前缀' ,
192                                 PRIMARY KEY (id) USING BTREE,
193                                 UNIQUE KEY `uk_code` (`code`) USING BTREE
194 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '序号表';
195 INSERT INTO `t_da_sequence_num` (`id`, `code`, `name`, `sequence_num`, `prefix`) VALUES ('1', 'POINT_M', '测量点编码', 100000, 'M');
196 INSERT INTO `t_da_sequence_num` (`id`, `code`, `name`, `sequence_num`, `prefix`) VALUES ('2', 'POINT_C', '计算点编码', 100000, 'C');
197
198 CREATE TABLE `t_http_api`  (
be2308 199                              `id` varchar(36) NOT NULL COMMENT 'ID',
200                              `code` varchar(32) NOT NULL COMMENT '编码',
201                              `name` varchar(32) NOT NULL COMMENT '名称',
202                              `url` varchar(100) NOT NULL COMMENT 'URL',
203                              `method` varchar(10) NULL DEFAULT NULL COMMENT '请求方法',
204                              `param` varchar(200) NULL DEFAULT NULL COMMENT '参数',
205                              `descp` varchar(100) NULL DEFAULT NULL COMMENT '描述',
206                              `is_auth` tinyint(1) NOT NULL COMMENT '是否认证',
207                              `auth_url` varchar(36) NULL DEFAULT NULL COMMENT '认证地址',
7c3e09 208                              `creator` VARCHAR(64)   COMMENT '创建人' ,
0fbd01 209                              `create_time` DATETIME   COMMENT '创建时间' ,
7c3e09 210                              `updater` VARCHAR(64)   COMMENT '修改人' ,
0fbd01 211                              `update_time` DATETIME   COMMENT '修改时间' ,
212                              PRIMARY KEY (`ID`) USING BTREE,
213                              UNIQUE KEY `uk_code` (`code`) USING BTREE
214 ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = 'Api接口' ROW_FORMAT = DYNAMIC;
215
216 CREATE TABLE `t_http_token` (
336bd2 217                               `id` varchar(36)  NOT NULL COMMENT 'ID',
218                               `login_url` varchar(200) DEFAULT NULL COMMENT '登录地址',
219                               `client_id` varchar(100) DEFAULT NULL COMMENT 'ClientId',
220                               `client_secret` varchar(100) DEFAULT NULL COMMENT 'ClientSecret',
221                               `username` varchar(50) DEFAULT NULL COMMENT '用户名',
222                               `password` varchar(50) DEFAULT NULL COMMENT '密码',
be2308 223                               `refresh_freq` varchar(10) DEFAULT NULL COMMENT '刷新频率',
336bd2 224                               `token` varchar(2000) DEFAULT NULL COMMENT 'token',
0fbd01 225                               `expire_time` datetime DEFAULT NULL COMMENT '过期时间',
226                               `update_time` datetime DEFAULT NULL COMMENT '更新时间',
227                               PRIMARY KEY (`id`) USING BTREE,
be2308 228                               UNIQUE KEY `uk_login_url` (`login_url`) USING BTREE
0fbd01 229 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='接口Token';
230
231 CREATE TABLE `t_http_tag` (
336bd2 232                             `id` varchar(36) NOT NULL COMMENT 'ID',
233                             `api_id` varchar(36) NOT NULL COMMENT '接口ID',
234                             `tag_name` varchar(64) NOT NULL COMMENT '标签名称',
235                             `data_type` varchar(16) NOT NULL COMMENT '数据类型',
236                             `enabled` tinyint(1) DEFAULT NULL COMMENT '是否启用',
237                             `tag_desc` varchar(64) DEFAULT NULL COMMENT '描述',
0fbd01 238                             `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
239                             `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
240                             PRIMARY KEY (`id`) USING BTREE
336bd2 241 ) ENGINE=InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT='接口Tag表';
0fbd01 242
f1162e 243 -- 指标管理------------
244 CREATE TABLE t_ind_data_set(
245                                     `id` VARCHAR(36) NOT NULL  COMMENT 'ID' ,
246                                     `name` VARCHAR(30) NOT NULL   COMMENT '名称' ,
247                                     `data_source` VARCHAR(64) NOT NULL   COMMENT '数据源ID',
248                                     `query_sql` VARCHAR(200) NOT NULL   COMMENT '查询语句',
249                                     `remark` VARCHAR(100)   COMMENT '备注',
250                                     `sort` int   COMMENT '排序',
251                                     `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建者',
252                                     `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
253                                     `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '更新者',
254                                     `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
255                                     PRIMARY KEY (id) USING BTREE
256 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '指标数据集';
257
258 CREATE TABLE t_ind_data_set_field(
259                                `id` VARCHAR(36) NOT NULL  COMMENT 'ID' ,
260                                `data_set_id` VARCHAR(36) NOT NULL  COMMENT '数据集ID' ,
261                                `field_code` VARCHAR(30)   COMMENT '英文名' ,
262                                `field_name` VARCHAR(30)   COMMENT '中文名' ,
263                                `field_type` VARCHAR(30)   COMMENT '数据类型' ,
264                                `sort` int   COMMENT '排序',
a63a4f 265                                PRIMARY KEY (id) USING BTREE,
266                                INDEX `idx_data_set_id` (`data_set_id`) USING BTREE
f1162e 267 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '指标数据集字段';
268
269 CREATE TABLE t_ind_item_category(
270                                 `id` VARCHAR(36) NOT NULL  COMMENT 'ID' ,
271                                 `label` VARCHAR(20)   COMMENT '标签' ,
272                                 `pid` VARCHAR(36)   COMMENT '父ID',
273                                 `sort` int   COMMENT '排序',
274                                 `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建者',
275                                 `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
276                                 `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '更新者',
277                                 `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
278                                 PRIMARY KEY (id) USING BTREE
279 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '指标分类';
0fbd01 280
281 CREATE TABLE t_ind_item(
f1162e 282                            `id` VARCHAR(36) NOT NULL  COMMENT 'ID' ,
283                            `item_no` VARCHAR(36)   COMMENT '指标编码' ,
284                            `item_name` VARCHAR(36)   COMMENT '指标名称' ,
285                            `item_type` VARCHAR(20)   COMMENT '指标类型' ,
218503 286                            `item_category` VARCHAR(36)   COMMENT '指标分类' ,
e41062 287                            `coefficient` decimal(10, 4) COMMENT '转换系数',
f1162e 288                            `precision` int COMMENT '指标精度',
289                            `time_granularity` VARCHAR(10)   COMMENT '时间粒度' ,
e41062 290                            `unit` VARCHAR(10)   COMMENT '数量单位' ,
f1162e 291                            `remark` VARCHAR(255)   COMMENT '备注' ,
292                            `status` tinyint NOT NULL DEFAULT 0 COMMENT '状态(0正常 1停用)',
293                            `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建者',
294                            `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
295                            `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '更新者',
296                            `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
297                            PRIMARY KEY (id) USING BTREE,
a63a4f 298                            UNIQUE INDEX `uk_item_no` (`item_no`) USING BTREE
0fbd01 299 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '指标项表';
300
301 CREATE TABLE t_ind_item_atom(
302                               `id` VARCHAR(36) NOT NULL  COMMENT 'ID' ,
303                               `item_id` VARCHAR(36)   COMMENT '指标ID' ,
f1162e 304                               `data_source` VARCHAR(64)   COMMENT '数据源',
305                               `data_set` VARCHAR(64)   COMMENT '数据集',
306                               `using_field` VARCHAR(64)   COMMENT '使用字段',
307                               `stat_func` VARCHAR(64)   COMMENT '统计方式',
0fbd01 308                               PRIMARY KEY (id) USING BTREE,
f1162e 309                               UNIQUE KEY `uk_item_id` (`item_id`) USING BTREE
0fbd01 310 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '原子指标';
f1162e 311
312 CREATE TABLE t_ind_item_der(
313                                 `id` VARCHAR(36) NOT NULL  COMMENT 'ID' ,
314                                 `item_id` VARCHAR(36)   COMMENT '指标ID' ,
315                                 `atom_item_id` VARCHAR(36)   COMMENT '原子指标ID' ,
316                                 `time_label` VARCHAR(20)   COMMENT '时间标识',
317                                 `time_limit` VARCHAR(20)   COMMENT '时间限定',
318                                 `time_start` DATETIME   COMMENT '开始时间',
319                                 `time_end` DATETIME   COMMENT '结束时间',
320                                 `dimension` VARCHAR(200)   COMMENT '分析维度',
321                                 PRIMARY KEY (id) USING BTREE,
322                                 UNIQUE KEY `uk_item_id` (`item_id`) USING BTREE
323 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '派生指标';
0fbd01 324
325 CREATE TABLE t_ind_item_cal(
326                              `id` VARCHAR(36) NOT NULL  COMMENT 'ID' ,
327                              `item_id` VARCHAR(36)   COMMENT '指标ID' ,
328                              `expression` varchar(100)  DEFAULT NULL COMMENT '计算表达式',
329                              PRIMARY KEY (id) USING BTREE,
330                              UNIQUE KEY `uk_item_id` (`item_id`) USING BTREE
e41062 331 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT = '复合指标';
332
0fbd01 333
334 CREATE TABLE `t_ind_item_value` (
335                                   `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'id',
336                                   `item_no` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '指标编码',
e41062 337                                   `data_time` varchar(20) DEFAULT NULL COMMENT '数据时间',
0fbd01 338                                   `data_value` decimal(24,6) DEFAULT NULL COMMENT '数据值',
339                                   PRIMARY KEY (`id`) USING BTREE,
340                                   KEY `idx_item_no` (`item_no`) USING BTREE,
341                                   KEY `idx_data_time` (`data_time`) USING BTREE
342 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='指标值表';