report-script.sql 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288
  1. SET NAMES utf8mb4;
  2. SET FOREIGN_KEY_CHECKS = 0;
  3. -- ----------------------------
  4. -- 租户数据集关联表(仅多租户时可用)
  5. -- ----------------------------
  6. DROP TABLE IF EXISTS `zz_report_tenant_dataset`;
  7. CREATE TABLE `zz_report_tenant_dataset` (
  8. `tenant_id` bigint NOT NULL COMMENT '租户Id',
  9. `dataset_id` bigint NOT NULL COMMENT '数据集Id',
  10. PRIMARY KEY (`tenant_id`,`dataset_id`),
  11. KEY `idx_dataset_id` (`dataset_id`) USING BTREE
  12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='租户数据集关联表';
  13. -- ----------------------------
  14. -- 数据集表
  15. -- ----------------------------
  16. DROP TABLE IF EXISTS `zz_report_dataset`;
  17. CREATE TABLE `zz_report_dataset` (
  18. `dataset_id` bigint NOT NULL COMMENT '主键Id',
  19. `app_code` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '应用编码',
  20. `dataset_name` varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '数据集名称',
  21. `group_id` bigint NOT NULL COMMENT '分组Id',
  22. `dblink_id` bigint COMMENT '数据库链接Id',
  23. `dataset_type` int NOT NULL COMMENT '数据集类型',
  24. `table_name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '数据表名',
  25. `dataset_info` text COLLATE utf8mb4_bin COMMENT '表原始信息',
  26. `create_time` datetime NOT NULL COMMENT '创建时间',
  27. `create_user_id` bigint NOT NULL COMMENT '创建者',
  28. `update_time` datetime NOT NULL COMMENT '更新时间',
  29. `update_user_id` bigint NOT NULL COMMENT '更新者',
  30. PRIMARY KEY (`dataset_id`) USING BTREE,
  31. KEY `idx_group_id` (`group_id`) USING BTREE,
  32. KEY `idx_dblink_id` (`dblink_id`) USING BTREE,
  33. KEY `idx_app_code` (`app_code`) USING BTREE,
  34. KEY `idx_dataset_type` (`dataset_type`) USING BTREE
  35. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='数据集表';
  36. -- ----------------------------
  37. -- 数据集字段表
  38. -- ----------------------------
  39. DROP TABLE IF EXISTS `zz_report_dataset_column`;
  40. CREATE TABLE `zz_report_dataset_column` (
  41. `column_id` bigint NOT NULL COMMENT '主键Id',
  42. `column_name` varchar(128) COLLATE utf8mb4_bin NOT NULL COMMENT '字段名',
  43. `dataset_id` bigint NOT NULL COMMENT '数据集Id',
  44. `primary_key` int NOT NULL DEFAULT '0' COMMENT '是否为主键',
  45. `column_type` varchar(128) COLLATE utf8mb4_bin NOT NULL COMMENT '表字段类型',
  46. `column_show_order` int NOT NULL COMMENT '列位置',
  47. `column_comment` varchar(521) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '字段注释',
  48. `field_name` varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT 'Java属性名',
  49. `field_type` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT 'Java属性类型',
  50. `numeric_precision` int DEFAULT '0' COMMENT '数值型字段的精度',
  51. `numeric_scale` int DEFAULT NULL COMMENT '数值型字段的刻度',
  52. `dict_id` bigint DEFAULT NULL COMMENT '字典Id',
  53. `dimension` int DEFAULT NULL COMMENT '是否为维度字段',
  54. `image` int NOT NULL DEFAULT '0' COMMENT '是否为图片字段',
  55. `logic_delete` int NOT NULL DEFAULT '0' COMMENT '是否为逻辑删除字段',
  56. `dept_filter` int NOT NULL DEFAULT '0' COMMENT '是否部门过滤字段',
  57. `user_filter` int NOT NULL DEFAULT '0' COMMENT '是否用户过滤字段',
  58. `tenant_filter` int NOT NULL DEFAULT '0' COMMENT '是否用户过滤字段',
  59. `field_kind` int NOT NULL DEFAULT '0' COMMENT '字段类别',
  60. `function_body` varchar(1024) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '函数体实现',
  61. PRIMARY KEY (`column_id`) USING BTREE,
  62. KEY `idx_dataset_id` (`dataset_id`) USING BTREE
  63. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='数据集字段表';
  64. -- ----------------------------
  65. -- 数据集分组表
  66. -- ----------------------------
  67. DROP TABLE IF EXISTS `zz_report_dataset_group`;
  68. CREATE TABLE `zz_report_dataset_group` (
  69. `group_id` bigint NOT NULL COMMENT '主键Id',
  70. `app_code` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '应用编码',
  71. `group_name` varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '分组名称',
  72. `parent_id` bigint DEFAULT NULL COMMENT '父级Id',
  73. `create_time` datetime NOT NULL COMMENT '创建时间',
  74. `create_user_id` bigint NOT NULL COMMENT '创建者',
  75. `update_time` datetime NOT NULL COMMENT '更新时间',
  76. `update_user_id` bigint NOT NULL COMMENT '更新者',
  77. KEY `idx_app_code` (`app_code`) USING BTREE,
  78. PRIMARY KEY (`group_id`) USING BTREE
  79. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='数据集分组表';
  80. -- ----------------------------
  81. -- 数据集关联表
  82. -- ----------------------------
  83. DROP TABLE IF EXISTS `zz_report_dataset_relation`;
  84. CREATE TABLE `zz_report_dataset_relation` (
  85. `relation_id` bigint NOT NULL COMMENT '主键Id',
  86. `app_code` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '应用编码',
  87. `variable_name` varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '变量名',
  88. `master_dataset_id` bigint NOT NULL COMMENT '主表数据集Id',
  89. `master_column_id` bigint NOT NULL COMMENT '主表关联字段Id',
  90. `slave_dataset_id` bigint NOT NULL COMMENT '从表数据集Id',
  91. `slave_column_id` bigint NOT NULL COMMENT '从表关联字段Id',
  92. `relation_type` int NOT NULL COMMENT '关联类型 (0:一对一 1:一对多)',
  93. `create_time` datetime NOT NULL COMMENT '创建时间',
  94. `create_user_id` bigint NOT NULL COMMENT '创建者',
  95. `update_time` datetime NOT NULL COMMENT '更新时间',
  96. `update_user_id` bigint NOT NULL COMMENT '更新者',
  97. PRIMARY KEY (`relation_id`) USING BTREE,
  98. KEY `idx_app_code` (`app_code`) USING BTREE,
  99. UNIQUE KEY `uk_master_dataset_id` (`master_dataset_id`, `variable_name`) USING BTREE,
  100. KEY `idx_slave_dataset_id` (`slave_dataset_id`) USING BTREE
  101. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='数据集关联表';
  102. -- ----------------------------
  103. -- 数据库链接表
  104. -- ----------------------------
  105. DROP TABLE IF EXISTS `zz_report_dblink`;
  106. CREATE TABLE `zz_report_dblink` (
  107. `dblink_id` bigint NOT NULL COMMENT '主键Id',
  108. `app_code` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '应用编码',
  109. `dblink_name` varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '数据源名称',
  110. `dblink_description` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '数据源描述',
  111. `dblink_type` int NOT NULL COMMENT '数据源类型',
  112. `configuration` varchar(2000) COLLATE utf8mb4_bin NOT NULL COMMENT '配置信息',
  113. `create_time` datetime NOT NULL COMMENT '创建时间',
  114. `create_user_id` bigint NOT NULL COMMENT '创建者',
  115. `update_time` datetime NOT NULL COMMENT '更新时间',
  116. `update_user_id` bigint NOT NULL COMMENT '更新者',
  117. PRIMARY KEY (`dblink_id`) USING BTREE,
  118. KEY `idx_app_code` (`app_code`) USING BTREE,
  119. KEY `idx_dblink_type` (`dblink_type`) USING BTREE
  120. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='数据库链接表';
  121. -- ----------------------------
  122. -- 字典表
  123. -- ----------------------------
  124. DROP TABLE IF EXISTS `zz_report_dict`;
  125. CREATE TABLE `zz_report_dict` (
  126. `dict_id` bigint NOT NULL COMMENT '主键Id',
  127. `app_code` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '应用编码',
  128. `dict_name` varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '字典名称',
  129. `dict_type` int NOT NULL COMMENT '字典类型',
  130. `dblink_id` bigint DEFAULT NULL COMMENT '数据库链接Id',
  131. `table_name` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '字典表名称',
  132. `dict_code` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '全局字典编码',
  133. `key_column_name` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '字典表键字段名称',
  134. `parent_key_column_name` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '字典表父键字段名称',
  135. `value_column_name` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '字典值字段名称',
  136. `deleted_column_name` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '逻辑删除字段',
  137. `tenant_filter_column_name` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '租户过滤字段名称',
  138. `tree_flag` int NOT NULL COMMENT '是否树形标记',
  139. `dict_list_url` varchar(512) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '获取字典列表数据的url',
  140. `dict_ids_url` varchar(512) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '根据主键id批量获取字典数据的url',
  141. `dict_data_json` text COLLATE utf8mb4_bin COMMENT '字典的JSON数据',
  142. `create_time` datetime NOT NULL COMMENT '创建时间',
  143. `create_user_id` bigint NOT NULL COMMENT '创建者',
  144. `update_time` datetime NOT NULL COMMENT '更新时间',
  145. `update_user_id` bigint NOT NULL COMMENT '更新者',
  146. PRIMARY KEY (`dict_id`) USING BTREE,
  147. KEY `idx_app_code` (`app_code`) USING BTREE,
  148. KEY `idx_dict_type` (`dict_type`) USING BTREE,
  149. KEY `idx_dblink_id` (`dblink_id`) USING BTREE
  150. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='字典表';
  151. -- ----------------------------
  152. -- 在线统计表单页面表
  153. -- ----------------------------
  154. DROP TABLE IF EXISTS `zz_report_page`;
  155. CREATE TABLE `zz_report_page` (
  156. `page_id` bigint NOT NULL COMMENT '主键Id',
  157. `tenant_id` bigint COMMENT '租户Id',
  158. `app_code` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '应用编码',
  159. `page_code` varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '页面编码',
  160. `page_name` varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '名称',
  161. `group_id` bigint NOT NULL COMMENT '页面分组Id',
  162. `page_json` longtext COLLATE utf8mb4_bin COMMENT '页面配置的JSON',
  163. `widget_json` text COLLATE utf8mb4_bin COMMENT '表单组件JSON',
  164. `param_json` text COLLATE utf8mb4_bin COMMENT '表单参数JSON',
  165. `create_time` datetime NOT NULL COMMENT '创建时间',
  166. `create_user_id` bigint NOT NULL COMMENT '创建者',
  167. `update_time` datetime NOT NULL COMMENT '更新时间',
  168. `update_user_id` bigint NOT NULL COMMENT '更新者',
  169. PRIMARY KEY (`page_id`) USING BTREE,
  170. KEY `idx_tenant_id` (`tenant_id`) USING BTREE,
  171. KEY `idx_app_code` (`app_code`) USING BTREE,
  172. KEY `idx_page_code` (`page_code`) USING BTREE
  173. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='在线统计表单页面表';
  174. -- ----------------------------
  175. -- 统计页面分组表
  176. -- ----------------------------
  177. DROP TABLE IF EXISTS `zz_report_page_group`;
  178. CREATE TABLE `zz_report_page_group` (
  179. `group_id` bigint NOT NULL COMMENT '主键Id',
  180. `tenant_id` bigint COMMENT '租户Id',
  181. `app_code` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '应用编码',
  182. `group_name` varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT '名称',
  183. `parent_id` bigint DEFAULT NULL COMMENT '父级id',
  184. `create_time` datetime NOT NULL COMMENT '创建时间',
  185. `create_user_id` bigint NOT NULL COMMENT '创建者',
  186. `update_time` datetime NOT NULL COMMENT '更新时间',
  187. `update_user_id` bigint NOT NULL COMMENT '更新者',
  188. KEY `idx_tenant_id` (`tenant_id`) USING BTREE,
  189. KEY `idx_app_code` (`app_code`) USING BTREE,
  190. PRIMARY KEY (`group_id`) USING BTREE
  191. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='统计页面分组表';
  192. -- ----------------------------
  193. -- 打印模板表
  194. -- ----------------------------
  195. DROP TABLE IF EXISTS `zz_report_print`;
  196. CREATE TABLE `zz_report_print` (
  197. `print_id` bigint NOT NULL COMMENT '主键Id',
  198. `tenant_id` bigint COMMENT '租户Id',
  199. `app_code` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '应用编码',
  200. `print_name` varchar(64) COLLATE utf8mb4_bin NOT NULL COMMENT '名称',
  201. `print_variable` varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT '变量名',
  202. `print_type` int DEFAULT '1' COMMENT '打印模板类型',
  203. `word_template` varchar(1024) COLLATE utf8mb4_bin COMMENT 'word打印模板',
  204. `group_id` bigint NOT NULL COMMENT '页面分组Id',
  205. `print_json` text COLLATE utf8mb4_bin COMMENT '打印配置JSON',
  206. `param_json` text COLLATE utf8mb4_bin COMMENT '参数数据JSON',
  207. `fragment_json` text COLLATE utf8mb4_bin COMMENT '打印片段参数JSON',
  208. `sheet_data_json` longtext COLLATE utf8mb4_bin COMMENT 'luckysheet电子表单原始配置JSON',
  209. `template_data_json` longtext COLLATE utf8mb4_bin COMMENT '电子表格解析后的打印模板配置数据JSON',
  210. `create_time` datetime NOT NULL COMMENT '创建时间',
  211. `create_user_id` bigint NOT NULL COMMENT '创建者',
  212. `update_time` datetime NOT NULL COMMENT '更新时间',
  213. `update_user_id` bigint NOT NULL COMMENT '更新者',
  214. PRIMARY KEY (`print_id`) USING BTREE,
  215. KEY `idx_tenant_id` (`tenant_id`) USING BTREE,
  216. KEY `idx_app_code` (`app_code`) USING BTREE,
  217. KEY `idx_print_variable` (`print_variable`) USING BTREE,
  218. KEY `idx_group_id` (`group_id`) USING BTREE
  219. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='打印模板表';
  220. -- ----------------------------
  221. -- 打印模板分组表
  222. -- ----------------------------
  223. DROP TABLE IF EXISTS `zz_report_print_group`;
  224. CREATE TABLE `zz_report_print_group` (
  225. `group_id` bigint NOT NULL COMMENT '主键Id',
  226. `tenant_id` bigint COMMENT '租户Id',
  227. `app_code` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '应用编码',
  228. `group_name` varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT '名称',
  229. `parent_id` bigint DEFAULT NULL COMMENT '父级id',
  230. `create_time` datetime NOT NULL COMMENT '创建时间',
  231. `create_user_id` bigint NOT NULL COMMENT '创建者',
  232. `update_time` datetime NOT NULL COMMENT '更新时间',
  233. `update_user_id` bigint NOT NULL COMMENT '更新者',
  234. KEY `idx_tenant_id` (`tenant_id`) USING BTREE,
  235. KEY `idx_app_code` (`app_code`) USING BTREE,
  236. PRIMARY KEY (`group_id`) USING BTREE
  237. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='打印模板分组表';
  238. -- ----------------------------
  239. -- 大屏可视化数据表
  240. -- ----------------------------
  241. DROP TABLE IF EXISTS `zz_report_visualization`;
  242. CREATE TABLE `zz_report_visualization` (
  243. `visual_id` bigint NOT NULL COMMENT '主键Id',
  244. `visual_name` varchar(512) COLLATE utf8mb4_bin NOT NULL COMMENT '名称',
  245. `config_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '可视化配置',
  246. `cover_img` longtext COLLATE utf8mb4_bin COMMENT '封面图的BASE64',
  247. `code_page` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '代码页',
  248. `publish_status` int NOT NULL COMMENT '发布状态',
  249. `create_time` datetime NOT NULL COMMENT '创建时间',
  250. `create_user_id` bigint NOT NULL COMMENT '创建者',
  251. `update_time` datetime NOT NULL COMMENT '更新时间',
  252. `update_user_id` bigint NOT NULL COMMENT '更新者',
  253. `deleted_flag` int NOT NULL COMMENT '删除标记(1: 正常 -1: 已删除)',
  254. PRIMARY KEY (`visual_id`)
  255. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='大屏可视化数据表';
  256. -- ----------------------------
  257. -- 大屏可视化素材数据表
  258. -- ----------------------------
  259. DROP TABLE IF EXISTS `zz_report_visualization_asset`;
  260. CREATE TABLE `zz_report_visualization_asset` (
  261. `asset_id` bigint NOT NULL COMMENT '主键Id',
  262. `visual_id` bigint COMMENT '可视化项目Id',
  263. `asset_name` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '素材名称',
  264. `thumbnail_img` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '缩略图BASE64',
  265. `asset_img` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin COMMENT '图的BASE64',
  266. `create_time` datetime NOT NULL COMMENT '创建时间',
  267. `create_user_id` bigint NOT NULL COMMENT '创建者',
  268. `update_time` datetime NOT NULL COMMENT '更新时间',
  269. `update_user_id` bigint NOT NULL COMMENT '更新者',
  270. PRIMARY KEY (`asset_id`) USING BTREE,
  271. KEY `idx_visual_id` (`visual_id`)
  272. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='大屏可视化素材数据表';
  273. SET FOREIGN_KEY_CHECKS = 1;