platomix-gmetry-bi/sqls/4.1.5_20240411.sql

CREATE TABLE `mcp_user` (
                            `user_id` bigint NOT NULL,
                            `user_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名',
                            `pass_word` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '密码',
                            `nick_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '昵称',
                            `create_time` datetime NOT NULL COMMENT '创建时间',
                            PRIMARY KEY (`user_id`),
                            UNIQUE KEY `user_name` (`user_name`)
) ENGINE=InnoDB  COMMENT='管理中台用户表';

CREATE TABLE `mcp_visual_component_define` (
                                               `id` int NOT NULL AUTO_INCREMENT,
                                               `component_name` varchar(100) NOT NULL COMMENT '组件名称',
                                               `image_url` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '组件图标',
                                               `description` varchar(500) DEFAULT NULL COMMENT '组件描述',
                                               `component_version` varchar(50) DEFAULT NULL COMMENT '组件版本号',
                                               `code_text` text COMMENT '代码内容',
                                               `enable_tenants` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '0' COMMENT '对某些租户公开，租户id通过逗号分割',
                                               `status` tinyint NOT NULL DEFAULT '0' COMMENT '启用/禁用 0是禁用1是启用',
                                               `create_time` datetime NOT NULL COMMENT '创建时间',
                                               `create_user_id` bigint NOT NULL COMMENT '创建人',
                                               `update_time` datetime NOT NULL COMMENT '修改时间',
                                               `update_by` varchar(50) DEFAULT NULL COMMENT '修改人',
                                               PRIMARY KEY (`id`)
) ENGINE=InnoDB  COMMENT='自定义组件';

CREATE TABLE `mcp_visual_component_sys` (
                                            `id` int NOT NULL AUTO_INCREMENT,
                                            `component_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '组件code',
                                            `component_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '组件名称',
                                            `image_url` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '组件图标',
                                            `description` varchar(500) DEFAULT NULL COMMENT '组件描述',
                                            `component_version` varchar(50) DEFAULT NULL COMMENT '组件版本号',
                                            `status` tinyint DEFAULT '0' COMMENT '是否禁用(0禁用 1 启用)',
                                            `create_time` datetime DEFAULT NULL COMMENT '创建时间',
                                            `create_user_id` bigint DEFAULT NULL COMMENT '创建人',
                                            `update_time` datetime DEFAULT NULL COMMENT '修改时间',
                                            `update_by` varchar(50) DEFAULT NULL COMMENT '修改人',
                                            PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='系统组件';

CREATE TABLE `mcp_visual_component_sys_category` (
                                                     `id` int NOT NULL AUTO_INCREMENT,
                                                     `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '分类名称',
                                                     `icon` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '图标',
                                                     `sort` int DEFAULT NULL COMMENT '排序从0开始',
                                                     `create_time` datetime DEFAULT NULL COMMENT '创建时间',
                                                     `create_user_id` bigint DEFAULT NULL COMMENT '创建人',
                                                     `update_time` datetime DEFAULT NULL COMMENT '修改时间',
                                                     `update_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '修改人',
                                                     PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='系统组件分类表';

CREATE TABLE `mcp_visual_component_sys_relation` (
                                                     `id` int NOT NULL AUTO_INCREMENT,
                                                     `category_id` int DEFAULT NULL COMMENT '分类id',
                                                     `sys_component_id` int DEFAULT NULL COMMENT '系统组件id',
                                                     `create_time` datetime DEFAULT NULL COMMENT '创建时间',
                                                     `create_user_id` bigint DEFAULT NULL COMMENT '创建人',
                                                     `update_time` datetime DEFAULT NULL COMMENT '修改时间',
                                                     `update_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '修改人',
                                                     PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='系统组件分类关系表';

CREATE TABLE `visual_data_template` (
                                        `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
                                        `name` varchar(255) DEFAULT NULL COMMENT '模板名字',
                                        `category` int DEFAULT NULL COMMENT '模板分类',
                                        `mcp_category` int DEFAULT NULL COMMENT '中台模板分类',
                                        `review_status` int DEFAULT '0' COMMENT '0未发起审核，1审核中，2审核通过 3审核未通过',
                                        `status` int DEFAULT '0' COMMENT '状态',
                                        `order_index` int DEFAULT NULL COMMENT '排序值 从小到大排序',
                                        `visual_id` int DEFAULT NULL COMMENT '大屏ID',
                                        `disable_reason` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '禁用原因',
                                        `preview_img_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '大屏预览图片地址',
                                        `create_user` bigint DEFAULT NULL COMMENT '创建人',
                                        `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                        `update_user` bigint DEFAULT NULL COMMENT '修改人',
                                        `update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
                                        `tenant_id` varchar(12) DEFAULT NULL COMMENT '租户id',
                                        `is_deleted` int DEFAULT '0' COMMENT '是否已删除',
                                        PRIMARY KEY (`id`),
                                        UNIQUE KEY `idx_visual_id` (`visual_id`)
) ENGINE=InnoDB  COMMENT='大屏模板表';

CREATE TABLE `visual_data_template_category` (
                                                 `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
                                                 `name` varchar(255) DEFAULT NULL COMMENT '分类名字',
                                                 `parent_id` int DEFAULT '0' COMMENT 'parent_id',
                                                 `order_index` int DEFAULT NULL COMMENT '排序值 从小到大排序',
                                                 `create_user` bigint DEFAULT NULL COMMENT '创建人',
                                                 `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                                 `update_user` bigint DEFAULT NULL COMMENT '修改人',
                                                 `update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
                                                 `tenant_id` varchar(12) DEFAULT NULL COMMENT '租户id',
                                                 `is_deleted` int DEFAULT '0' COMMENT '是否已删除',
                                                 PRIMARY KEY (`id`)
) ENGINE=InnoDB  COMMENT='大屏模板分类表';

CREATE TABLE `visual_review_log` (
                                     `id` int NOT NULL AUTO_INCREMENT,
                                     `relation_id` int DEFAULT NULL COMMENT '关联id',
                                     `type` int DEFAULT NULL COMMENT '类型 0大屏模板/1大屏',
                                     `review_status` int DEFAULT NULL COMMENT '审核状态(0发起审核，1审核通过，2审核驳回)',
                                     `review_reason` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '审核理由',
                                     `Initiate_reason` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '理由',
                                     `review_time` datetime DEFAULT NULL COMMENT '审核时间',
                                     `create_time` datetime DEFAULT NULL COMMENT '创建时间',
                                     `create_user_id` bigint DEFAULT NULL COMMENT '创建人',
                                     `review_user_id` bigint DEFAULT NULL COMMENT '审核人',
                                     `update_time` datetime DEFAULT NULL COMMENT '修改时间',
                                     `update_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '修改人',
                                     `tenant_id` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '租户id',
                                     `is_deleted` int DEFAULT '0' COMMENT '是否已删除',
                                     PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='审核记录';

CREATE TABLE `visual_component_template` (
                                             `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
                                             `name` varchar(255) DEFAULT NULL COMMENT '模板名字',
                                             `category` int DEFAULT NULL COMMENT '模板分类',
                                             `mcp_category` int DEFAULT NULL COMMENT '中台模板分类',
                                             `review_status` int DEFAULT '0' COMMENT '0未发起审核，1审核中，2审核通过 3审核未通过',
                                             `status` int DEFAULT '0' COMMENT '状态',
                                             `order_index` int DEFAULT NULL COMMENT '排序值 从小到大排序',
                                             `component_id` int DEFAULT NULL COMMENT '大屏ID',
                                             `preview_url` varchar(255) DEFAULT NULL COMMENT '预览图片地址',
                                             `disable_reason` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '禁用原因',
                                             `create_user` bigint DEFAULT NULL COMMENT '创建人',
                                             `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                             `update_user` bigint DEFAULT NULL COMMENT '修改人',
                                             `update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
                                             `tenant_id` varchar(12) DEFAULT NULL COMMENT '租户id',
                                             `is_deleted` int DEFAULT '0' COMMENT '是否已删除',
                                             PRIMARY KEY (`id`),
                                             UNIQUE KEY `idx_component_id` (`component_id`)
) ENGINE=InnoDB COMMENT='大屏组件模板表';
CREATE TABLE `visual_component_template_category` (
                                                      `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
                                                      `name` varchar(255) DEFAULT NULL COMMENT '分类名字',
                                                      `parent_id` int DEFAULT '0' COMMENT 'parent_id',
                                                      `order_index` int DEFAULT NULL COMMENT '排序值 从小到大排序',
                                                      `create_user` bigint DEFAULT NULL COMMENT '创建人',
                                                      `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                                      `update_user` bigint DEFAULT NULL COMMENT '修改人',
                                                      `update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
                                                      `tenant_id` varchar(12) DEFAULT NULL COMMENT '租户id',
                                                      `is_deleted` int DEFAULT '0' COMMENT '是否已删除',
                                                      PRIMARY KEY (`id`)
) ENGINE=InnoDB  COMMENT='大屏组件模板分类表';

ALTER TABLE digital_operation.visual_component ADD COLUMN static_data_path VARCHAR(255) NULL COMMENT '静态数据地址';
ALTER TABLE digital_operation.visual_component ADD COLUMN `whether_static` tinyint DEFAULT '0' COMMENT '是否是静态，默认0不是';
ALTER TABLE digital_operation.visual_component ADD COLUMN component_source INT COMMENT '组件来源 0是系统组件 1是超管自定义组件';
ALTER TABLE digital_operation.visual_component ADD COLUMN component_source_id INT COMMENT '组件来源id';
ALTER TABLE digital_operation.visual_component ADD COLUMN `tenant_id` varchar(12) DEFAULT NULL COMMENT '租户id';
ALTER TABLE digital_operation.visual_data ADD COLUMN `disable_reason` varchar(255) DEFAULT '1' COMMENT '禁用原因';
ALTER TABLE digital_operation.visual_data ADD COLUMN `review_status` int DEFAULT '1' COMMENT '审核状态（0未审核，1审核通过2审核未通过）';
ALTER TABLE digital_operation.visual_data ADD COLUMN `template_id` int DEFAULT NULL COMMENT '模板id';
ALTER TABLE digital_operation.visual_component_template ADD COLUMN `create_user_name` varchar(255) DEFAULT NULL COMMENT '创建人名字';
ALTER TABLE digital_operation.mcp_visual_component_define ADD COLUMN `create_user_name` varchar(255) DEFAULT NULL COMMENT '创建人名字';
ALTER TABLE `digital_operation`.`mcp_visual_component_define` ADD COLUMN `disable_reason` varchar(500)  DEFAULT NULL COMMENT '禁用原因' AFTER `status`;
ALTER TABLE `digital_operation`.`sql_backup_record` ADD COLUMN `table_names` varchar(500)  DEFAULT NULL COMMENT '表名字逗号分割' AFTER `remarks`;
ALTER TABLE `digital_operation`.`sql_backup_record` ADD COLUMN `create_user` bigint  DEFAULT NULL COMMENT '创建人id' AFTER `restore_time`;
ALTER TABLE `digital_operation`.`sql_backup_record` ADD COLUMN `create_user_name` varchar(255)  DEFAULT NULL COMMENT '创建人名字' AFTER `create_user`;
ALTER TABLE digital_operation.visual_review_log ADD COLUMN `create_user_name` varchar(50) DEFAULT NULL COMMENT '创建人名字';
ALTER TABLE digital_operation.visual_review_log ADD COLUMN `review_user_name` varchar(50) DEFAULT NULL COMMENT '审核人名字';
ALTER TABLE digital_operation.`visual_warning` ADD COLUMN `level` VARCHAR(50) NOT NULL COMMENT '预警等级';
ALTER TABLE digital_operation.`visual_component_define` MODIFY COLUMN `image_url` varchar(255)  DEFAULT NULL COMMENT '组件图标';
ALTER TABLE digital_operation.`visual_data_template` ADD COLUMN `use_times` int DEFAULT '0' COMMENT '使用次数';
ALTER TABLE digital_operation.`report_table_category` ADD COLUMN `parent_id` int DEFAULT '0' COMMENT 'parent_id';
ALTER TABLE digital_operation.`visual_power_classify_favourite` ADD COLUMN `parent_id` int DEFAULT '0' COMMENT 'parent_id';
ALTER TABLE digital_operation.`visual_power_classify_archiving` ADD COLUMN `parent_id` int DEFAULT '0' COMMENT 'parent_id';
ALTER TABLE digital_operation.`visual_report_category_favourite` ADD COLUMN `parent_id` int DEFAULT '0' COMMENT 'parent_id';
ALTER TABLE digital_operation.`visual_classify_favourite` ADD COLUMN `parent_id` int DEFAULT '0' COMMENT 'parent_id';
ALTER TABLE digital_operation.`data_permission_type` ADD COLUMN `parent_id` int DEFAULT '0' COMMENT 'parent_id';
UPDATE digital_operation.mcp_visual_component_sys set image_url = REPLACE(image_url,'http://static.yanxiang.platomix.net/platomix','https://osstest.9n1m.com/9n1m-bireport-test');
ALTER TABLE platomix_visualdev.base_visualdev ADD `F_CreatorUserName` varchar(255) DEFAULT NULL COMMENT '创建人名称';
ALTER TABLE platomix_visualdev.base_visualdev ADD `F_LastModifyUserName` varchar(255) DEFAULT NULL COMMENT '最后修改人名称';
ALTER TABLE mcp_visual_component_define MODIFY COLUMN `enable_tenants` varchar(500) DEFAULT NULL COMMENT '对某些租户公开，租户id通过逗号分割';
update digital_operation.mcp_visual_component_define  set enable_tenants = null;
CREATE TABLE `digital_operation`.`custom_platform`  (
                                    `id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增id',
                                    `platform_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '平台类型',
                                    `platform_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '平台名称',
                                    `url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'url地址',
                                    `token` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'token',
                                    `keys` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'key',
                                    `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '平台描述',
                                    `create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                    `creator` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '创建人',
                                    `tenant_id` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '000000' COMMENT '租户ID',
                                    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB  COMMENT = '平台信息表' ROW_FORMAT = Dynamic;
ALTER TABLE digital_operation.`visual_data` ADD COLUMN `create_user_name` varchar(50) DEFAULT NULL COMMENT '创建人名字';

drop table IF EXISTS digital_operation.`visual_review_log`;

CREATE TABLE digital_operation.`visual_review_log` (
                                     `id` int NOT NULL AUTO_INCREMENT,
                                     `relation_id` int DEFAULT NULL COMMENT '关联id',
                                     `type` int DEFAULT NULL COMMENT '类型 0大屏模板/1大屏/2是组件模板',
                                     `status` int DEFAULT NULL COMMENT '0是发起审核 1通过 2驳回 3禁用',
                                     `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
                                     `create_time` datetime DEFAULT NULL COMMENT '创建时间',
                                     `create_user_id` bigint DEFAULT NULL COMMENT '创建人',
                                     `create_user_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建人名字',
                                     `tenant_id` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '租户id',
                                     PRIMARY KEY (`id`)
) ENGINE=InnoDB  COMMENT='审核记录';
ALTER TABLE digital_operation.`visual_data_template` ADD COLUMN `create_user_name` varchar(50) DEFAULT NULL COMMENT '创建人名字';

CREATE TABLE digital_operation.`visual_data_share` (
                                     `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
                                     `type` int DEFAULT NULL COMMENT '0可视化大屏，1自助报表',
                                     `relation_id` int DEFAULT NULL COMMENT '关联id',
                                     `relation_name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '关联名字',
                                     `shared_user` bigint DEFAULT NULL COMMENT '被分享人',
                                     `shared_user_name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '创建人名字',
                                     `shared_user_tenant` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '租户id',
                                     `create_user` bigint DEFAULT NULL COMMENT '创建人',
                                     `create_user_name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '创建人名字',
                                     `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                     `update_user` bigint DEFAULT NULL COMMENT '修改人',
                                     `update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
                                     `is_deleted` int DEFAULT '0' COMMENT '是否已删除',
                                     PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB;
INSERT INTO `digital_operation`.`profile_event` (`name`, `code`, `platform`, `desc_message`, `create_time`, `tenant_id`) VALUES ('大屏预览', 'visualPreview', 'web/h5', '', '2023-11-15 12:50:08', '111111');



ALTER TABLE digital_operation.`visual_warning` ADD COLUMN `custom_ids` varchar(100) DEFAULT NULL COMMENT '自定义平台ids 逗号分割';