luhaojie/agricultural-monitoring-api
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
agricultural-monitoring-api/sql/其他/fastbee2.2-upgrade-v2.3.sql
wyw c202e2b63d 第一次提交
2024-08-08 00:31:26 +08:00

980 lines
54 KiB
SQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

-- ----------------------------
-- 注意注意：以下部分sql重构了表结构，会清空部分表的数据，需要根据自己的系统进行调整
-- 更新前建议备份数据，数据丢失蜂信物联概不负责
-- ----------------------------
-- 机构表新加字段
ALTER TABLE `sys_dept`
ADD COLUMN `dept_user_id` bigint(20) NULL DEFAULT NULL COMMENT '机构系统账号ID',
ADD COLUMN `dept_type` tinyint(11) NULL DEFAULT NULL COMMENT '机构类型';
-- 删除 iot_device表 user_id 、user_name
ALTER TABLE `iot_device`
DROP COLUMN `user_id`,
DROP COLUMN `user_name`;
-- 新建设备分享表
CREATE TABLE `iot_device_share` (
`device_id` bigint NOT NULL COMMENT '设备id',
`user_id` bigint NOT NULL COMMENT '用户id',
`phonenumber` varchar(20) NULL COMMENT '手机',
`perms` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户物模型权限，多个以英文逗号分隔',
`del_flag` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
`create_by` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '创建者',
`create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
`update_by` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '更新者',
`update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
`remark` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`device_id`, `user_id`),
INDEX `PRIMARK_DEVICE_USER`(`device_id`, `user_id`) USING BTREE COMMENT '设备与用户索引'
) COMMENT = '设备分享表';
-- 设备用户表删除字段
ALTER TABLE `iot_device_user`
DROP COLUMN `device_name`,
DROP COLUMN `user_name`,
DROP COLUMN `perms`,
DROP COLUMN `tenant_id`,
DROP COLUMN `tenant_name`,
DROP COLUMN `is_owner`;
-- 设备分享权限
INSERT INTO `sys_menu`(`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('设备分享', 2007, 1, 'share', 'iot/share/index', NULL, 1, 0, 'C', '0', '0', 'iot:share:list', '#', 'admin', '2024-04-17 14:20:33', '', NULL, '设备分享菜单');
SELECT @parentId := LAST_INSERT_ID();
INSERT INTO `sys_menu`(`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('设备分享用户', @parentId, 1, '', NULL, NULL, 1, 0, 'F', '0', '0', 'iot:share:user', '#', 'admin', '2024-05-09 09:38:07', '', NULL, '');
INSERT INTO `sys_menu`(`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('设备分享查询', @parentId, 1, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:share:query', '#', 'admin', '2024-04-17 14:20:33', '', NULL, '');
INSERT INTO `sys_menu`(`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('设备分享新增', @parentId, 2, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:share:add', '#', 'admin', '2024-04-17 14:20:33', '', NULL, '');
INSERT INTO `sys_menu`(`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('设备分享修改', @parentId, 3, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:share:edit', '#', 'admin', '2024-04-17 14:20:33', '', NULL, '');
INSERT INTO `sys_menu`(`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('设备分享删除', @parentId, 4, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:share:remove', '#', 'admin', '2024-04-17 14:20:33', '', NULL, '');
INSERT INTO `sys_menu`(`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('设备分享导出', @parentId, 5, '#', '', NULL, 1, 0, 'F', '0', '0', 'iot:share:export', '#', 'admin', '2024-04-17 14:20:33', '', NULL, '');
-- 产品表增加是否是私有产品
ALTER TABLE `iot_product`
ADD COLUMN `is_owner` tinyint(255) NULL DEFAULT NULL COMMENT '是否是私有产品(0-否，1-是)';
-- 告警表增加租户字段
ALTER TABLE iot_alert ADD COLUMN tenant_id bigint(20) NULL DEFAULT NULL COMMENT '租户id', ADD COLUMN tenant_name varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '租户名称';
-- 菜单表新增权限
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark) VALUES ('设备分配', 2007, 10, '', NULL, NULL, 1, 0, 'F', '0', '0', 'iot:device:assignment', '#', 'admin', '2024-04-10 09:40:59', '', NULL, '');
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, query, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark) VALUES ('设备回收', 2007, 11, '', NULL, NULL, 1, 0, 'F', '0', '0', 'iot:device:recovery', '#', 'admin', '2024-04-10 09:41:16', '', NULL, '');
-- 云云对接表增加租户字段
ALTER TABLE oauth_client_details ADD COLUMN tenant_id bigint(20) NOT NULL COMMENT '租户id',
ADD COLUMN tenant_name varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '租户名称';
-- 场景表增加区分终端用户表
ALTER TABLE `iot_scene` ADD COLUMN `terminal_user` tinyint(1) NULL DEFAULT 0 COMMENT '是否终端用户（1-是，0-不是)';
-- 大屏加租户字段
ALTER TABLE iot_goview_project ADD COLUMN tenant_id bigint(20) NULL DEFAULT NULL COMMENT '租户id', ADD COLUMN tenant_name varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '租户名称';
-- 机构类型枚举
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, update_by, update_time, remark) VALUES ('机构类型', 'department_type', '0', 'admin', '2024-02-29 11:38:14', '', NULL, NULL);
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark) VALUES (1, '厂商', '1', 'department_type', NULL, 'success', 'N', '0', 'admin', '2024-02-29 14:07:54', 'admin', '2024-03-20 11:55:36', '2,3,4');
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark) VALUES (2, '生产厂商', '2', 'department_type', NULL, 'primary', 'N', '0', 'admin', '2024-02-29 14:09:06', 'admin', '2024-03-20 11:55:45', '3,4');
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark) VALUES (2, '经销商', '3', 'department_type', NULL, 'warning', 'N', '0', 'admin', '2024-02-29 14:09:25', 'admin', '2024-03-20 11:55:52', '3,4');
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, update_by, update_time, remark) VALUES (4, '服务商', '4', 'department_type', NULL, 'info', 'N', '0', 'admin', '2024-02-29 14:09:34', 'admin', '2024-03-20 11:55:57', '4');
-- 新增企业管理菜单
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES ('企业管理', 0, 5, 'enterprise', NULL, '', 1, 0, 'M', '0', '0', '', 'authenticate', 'admin', '2021-12-15 21:36:18', 'admin', '2024-03-20 15:57:01', '企业管理目录');
SELECT @parentId := LAST_INSERT_ID();
update sys_menu
set parent_id = @parentId
where menu_name in ('用户管理','角色管理','菜单管理','部门管理');
update sys_menu
set menu_name = '机构管理'
where menu_name = '部门管理';
-- 刷新部门表数据
truncate table sys_dept;
INSERT INTO `sys_dept` (`dept_id`, `parent_id`, `ancestors`, `dept_name`, `order_num`, `leader`, `phone`, `email`, `status`, `del_flag`, `create_by`, `create_time`, `update_by`, `update_time`, `dept_user_id`, `dept_type`) VALUES (100, 0, '0', '蜂信物联', 0, 'FastBee', '15888888888', '164770707@qq.com', '0', '0', 'admin', '2021-12-15 21:36:18', 'admin', '2023-02-26 23:06:24', 1, 1);
update sys_user
set dept_id = 100
where user_id = 1;
-- 刷新角色部门表数据
truncate table sys_role_dept;
INSERT INTO `fastbee`.`sys_role_dept` (`role_id`, `dept_id`) VALUES (1, 100);
INSERT INTO `fastbee`.`sys_role_dept` (`role_id`, `dept_id`) VALUES (2, 100);
INSERT INTO `fastbee`.`sys_role_dept` (`role_id`, `dept_id`) VALUES (3, 100);
INSERT INTO `fastbee`.`sys_role_dept` (`role_id`, `dept_id`) VALUES (4, 100);
INSERT INTO `fastbee`.`sys_role_dept` (`role_id`, `dept_id`) VALUES (5, 100);
INSERT INTO `fastbee`.`sys_role_dept` (`role_id`, `dept_id`) VALUES (6, 100);
-- 刷新角色菜单数据
delete from sys_role_menu
where role_id in (3,4);
-- 更新普通用户、游客角色权限
INSERT INTO `sys_role_menu` VALUES (3, 1);
INSERT INTO `sys_role_menu` VALUES (3, 4);
INSERT INTO `sys_role_menu` VALUES (3, 107);
INSERT INTO `sys_role_menu` VALUES (3, 1036);
INSERT INTO `sys_role_menu` VALUES (3, 2000);
INSERT INTO `sys_role_menu` VALUES (3, 2007);
INSERT INTO `sys_role_menu` VALUES (3, 2008);
INSERT INTO `sys_role_menu` VALUES (3, 2009);
INSERT INTO `sys_role_menu` VALUES (3, 2010);
INSERT INTO `sys_role_menu` VALUES (3, 2011);
INSERT INTO `sys_role_menu` VALUES (3, 2012);
INSERT INTO `sys_role_menu` VALUES (3, 2019);
INSERT INTO `sys_role_menu` VALUES (3, 2020);
INSERT INTO `sys_role_menu` VALUES (3, 2021);
INSERT INTO `sys_role_menu` VALUES (3, 2022);
INSERT INTO `sys_role_menu` VALUES (3, 2023);
INSERT INTO `sys_role_menu` VALUES (3, 2024);
INSERT INTO `sys_role_menu` VALUES (3, 2043);
INSERT INTO `sys_role_menu` VALUES (3, 2067);
INSERT INTO `sys_role_menu` VALUES (3, 2068);
INSERT INTO `sys_role_menu` VALUES (3, 2070);
INSERT INTO `sys_role_menu` VALUES (3, 2085);
INSERT INTO `sys_role_menu` VALUES (3, 2086);
INSERT INTO `sys_role_menu` VALUES (3, 2087);
INSERT INTO `sys_role_menu` VALUES (3, 2088);
INSERT INTO `sys_role_menu` VALUES (3, 2089);
INSERT INTO `sys_role_menu` VALUES (3, 2090);
INSERT INTO `sys_role_menu` VALUES (3, 2129);
INSERT INTO `sys_role_menu` VALUES (3, 2130);
INSERT INTO `sys_role_menu` VALUES (3, 2147);
INSERT INTO `sys_role_menu` VALUES (3, 2148);
INSERT INTO `sys_role_menu` VALUES (3, 2168);
INSERT INTO `sys_role_menu` VALUES (3, 2169);
INSERT INTO `sys_role_menu` VALUES (3, 2170);
INSERT INTO `sys_role_menu` VALUES (3, 2171);
INSERT INTO `sys_role_menu` VALUES (3, 2172);
INSERT INTO `sys_role_menu` VALUES (3, 3001);
INSERT INTO `sys_role_menu` VALUES (3, 3002);
INSERT INTO `sys_role_menu` VALUES (3, 3003);
INSERT INTO `sys_role_menu` VALUES (3, 3004);
INSERT INTO `sys_role_menu` VALUES (3, 3005);
INSERT INTO `sys_role_menu` VALUES (3, 3006);
INSERT INTO `sys_role_menu` VALUES (3, 3031);
INSERT INTO `sys_role_menu` VALUES (3, 3032);
INSERT INTO `sys_role_menu` VALUES (3, 3033);
INSERT INTO `sys_role_menu` VALUES (3, 3034);
INSERT INTO `sys_role_menu` VALUES (3, 3035);
INSERT INTO `sys_role_menu` VALUES (3, 3036);
INSERT INTO `sys_role_menu` VALUES (3, 3037);
INSERT INTO `sys_role_menu` VALUES (3, 3038);
INSERT INTO `sys_role_menu` VALUES (3, 3044);
INSERT INTO `sys_role_menu` VALUES (3, 3046);
INSERT INTO `sys_role_menu` VALUES (3, 3051);
INSERT INTO `sys_role_menu` VALUES (3, 3159);
INSERT INTO `sys_role_menu` VALUES (3, 3178);
INSERT INTO `sys_role_menu` VALUES (3, 3206);
INSERT INTO `sys_role_menu` VALUES (3, 3207);
INSERT INTO `sys_role_menu` VALUES (3, 3226);
INSERT INTO `sys_role_menu` VALUES (3, 3238);
INSERT INTO `sys_role_menu` VALUES (3, 3247);
INSERT INTO `sys_role_menu` VALUES (3, 3249);
INSERT INTO `sys_role_menu` VALUES (3, 3250);
INSERT INTO `sys_role_menu` VALUES (3, 3251);
INSERT INTO `sys_role_menu` VALUES (3, 3252);
INSERT INTO `sys_role_menu` VALUES (4, 1);
INSERT INTO `sys_role_menu` VALUES (4, 2);
INSERT INTO `sys_role_menu` VALUES (4, 3);
INSERT INTO `sys_role_menu` VALUES (4, 4);
INSERT INTO `sys_role_menu` VALUES (4, 100);
INSERT INTO `sys_role_menu` VALUES (4, 101);
INSERT INTO `sys_role_menu` VALUES (4, 102);
INSERT INTO `sys_role_menu` VALUES (4, 103);
INSERT INTO `sys_role_menu` VALUES (4, 104);
INSERT INTO `sys_role_menu` VALUES (4, 105);
INSERT INTO `sys_role_menu` VALUES (4, 106);
INSERT INTO `sys_role_menu` VALUES (4, 107);
INSERT INTO `sys_role_menu` VALUES (4, 108);
INSERT INTO `sys_role_menu` VALUES (4, 109);
INSERT INTO `sys_role_menu` VALUES (4, 110);
INSERT INTO `sys_role_menu` VALUES (4, 111);
INSERT INTO `sys_role_menu` VALUES (4, 112);
INSERT INTO `sys_role_menu` VALUES (4, 113);
INSERT INTO `sys_role_menu` VALUES (4, 114);
INSERT INTO `sys_role_menu` VALUES (4, 115);
INSERT INTO `sys_role_menu` VALUES (4, 116);
INSERT INTO `sys_role_menu` VALUES (4, 124);
INSERT INTO `sys_role_menu` VALUES (4, 500);
INSERT INTO `sys_role_menu` VALUES (4, 501);
INSERT INTO `sys_role_menu` VALUES (4, 1001);
INSERT INTO `sys_role_menu` VALUES (4, 1008);
INSERT INTO `sys_role_menu` VALUES (4, 1013);
INSERT INTO `sys_role_menu` VALUES (4, 1017);
INSERT INTO `sys_role_menu` VALUES (4, 1021);
INSERT INTO `sys_role_menu` VALUES (4, 1026);
INSERT INTO `sys_role_menu` VALUES (4, 1031);
INSERT INTO `sys_role_menu` VALUES (4, 1036);
INSERT INTO `sys_role_menu` VALUES (4, 1040);
INSERT INTO `sys_role_menu` VALUES (4, 1043);
INSERT INTO `sys_role_menu` VALUES (4, 1046);
INSERT INTO `sys_role_menu` VALUES (4, 1049);
INSERT INTO `sys_role_menu` VALUES (4, 1055);
INSERT INTO `sys_role_menu` VALUES (4, 2000);
INSERT INTO `sys_role_menu` VALUES (4, 2001);
INSERT INTO `sys_role_menu` VALUES (4, 2002);
INSERT INTO `sys_role_menu` VALUES (4, 2003);
INSERT INTO `sys_role_menu` VALUES (4, 2007);
INSERT INTO `sys_role_menu` VALUES (4, 2008);
INSERT INTO `sys_role_menu` VALUES (4, 2009);
INSERT INTO `sys_role_menu` VALUES (4, 2013);
INSERT INTO `sys_role_menu` VALUES (4, 2014);
INSERT INTO `sys_role_menu` VALUES (4, 2015);
INSERT INTO `sys_role_menu` VALUES (4, 2019);
INSERT INTO `sys_role_menu` VALUES (4, 2020);
INSERT INTO `sys_role_menu` VALUES (4, 2021);
INSERT INTO `sys_role_menu` VALUES (4, 2043);
INSERT INTO `sys_role_menu` VALUES (4, 2044);
INSERT INTO `sys_role_menu` VALUES (4, 2045);
INSERT INTO `sys_role_menu` VALUES (4, 2049);
INSERT INTO `sys_role_menu` VALUES (4, 2050);
INSERT INTO `sys_role_menu` VALUES (4, 2051);
INSERT INTO `sys_role_menu` VALUES (4, 2067);
INSERT INTO `sys_role_menu` VALUES (4, 2068);
INSERT INTO `sys_role_menu` VALUES (4, 2069);
INSERT INTO `sys_role_menu` VALUES (4, 2085);
INSERT INTO `sys_role_menu` VALUES (4, 2086);
INSERT INTO `sys_role_menu` VALUES (4, 2087);
INSERT INTO `sys_role_menu` VALUES (4, 2104);
INSERT INTO `sys_role_menu` VALUES (4, 2123);
INSERT INTO `sys_role_menu` VALUES (4, 2124);
INSERT INTO `sys_role_menu` VALUES (4, 2125);
INSERT INTO `sys_role_menu` VALUES (4, 2129);
INSERT INTO `sys_role_menu` VALUES (4, 2130);
INSERT INTO `sys_role_menu` VALUES (4, 2131);
INSERT INTO `sys_role_menu` VALUES (4, 2136);
INSERT INTO `sys_role_menu` VALUES (4, 2137);
INSERT INTO `sys_role_menu` VALUES (4, 2141);
INSERT INTO `sys_role_menu` VALUES (4, 2147);
INSERT INTO `sys_role_menu` VALUES (4, 2148);
INSERT INTO `sys_role_menu` VALUES (4, 2149);
INSERT INTO `sys_role_menu` VALUES (4, 2167);
INSERT INTO `sys_role_menu` VALUES (4, 2168);
INSERT INTO `sys_role_menu` VALUES (4, 2169);
INSERT INTO `sys_role_menu` VALUES (4, 2170);
INSERT INTO `sys_role_menu` VALUES (4, 2173);
INSERT INTO `sys_role_menu` VALUES (4, 2174);
INSERT INTO `sys_role_menu` VALUES (4, 2175);
INSERT INTO `sys_role_menu` VALUES (4, 2179);
INSERT INTO `sys_role_menu` VALUES (4, 2180);
INSERT INTO `sys_role_menu` VALUES (4, 2181);
INSERT INTO `sys_role_menu` VALUES (4, 3000);
INSERT INTO `sys_role_menu` VALUES (4, 3001);
INSERT INTO `sys_role_menu` VALUES (4, 3002);
INSERT INTO `sys_role_menu` VALUES (4, 3003);
INSERT INTO `sys_role_menu` VALUES (4, 3007);
INSERT INTO `sys_role_menu` VALUES (4, 3008);
INSERT INTO `sys_role_menu` VALUES (4, 3009);
INSERT INTO `sys_role_menu` VALUES (4, 3013);
INSERT INTO `sys_role_menu` VALUES (4, 3014);
INSERT INTO `sys_role_menu` VALUES (4, 3015);
INSERT INTO `sys_role_menu` VALUES (4, 3019);
INSERT INTO `sys_role_menu` VALUES (4, 3020);
INSERT INTO `sys_role_menu` VALUES (4, 3021);
INSERT INTO `sys_role_menu` VALUES (4, 3025);
INSERT INTO `sys_role_menu` VALUES (4, 3026);
INSERT INTO `sys_role_menu` VALUES (4, 3027);
INSERT INTO `sys_role_menu` VALUES (4, 3031);
INSERT INTO `sys_role_menu` VALUES (4, 3032);
INSERT INTO `sys_role_menu` VALUES (4, 3033);
INSERT INTO `sys_role_menu` VALUES (4, 3034);
INSERT INTO `sys_role_menu` VALUES (4, 3035);
INSERT INTO `sys_role_menu` VALUES (4, 3039);
INSERT INTO `sys_role_menu` VALUES (4, 3040);
INSERT INTO `sys_role_menu` VALUES (4, 3041);
INSERT INTO `sys_role_menu` VALUES (4, 3044);
INSERT INTO `sys_role_menu` VALUES (4, 3045);
INSERT INTO `sys_role_menu` VALUES (4, 3046);
INSERT INTO `sys_role_menu` VALUES (4, 3047);
INSERT INTO `sys_role_menu` VALUES (4, 3048);
INSERT INTO `sys_role_menu` VALUES (4, 3049);
INSERT INTO `sys_role_menu` VALUES (4, 3051);
INSERT INTO `sys_role_menu` VALUES (4, 3055);
INSERT INTO `sys_role_menu` VALUES (4, 3147);
INSERT INTO `sys_role_menu` VALUES (4, 3157);
INSERT INTO `sys_role_menu` VALUES (4, 3159);
INSERT INTO `sys_role_menu` VALUES (4, 3166);
INSERT INTO `sys_role_menu` VALUES (4, 3167);
INSERT INTO `sys_role_menu` VALUES (4, 3168);
INSERT INTO `sys_role_menu` VALUES (4, 3169);
INSERT INTO `sys_role_menu` VALUES (4, 3172);
INSERT INTO `sys_role_menu` VALUES (4, 3173);
INSERT INTO `sys_role_menu` VALUES (4, 3174);
INSERT INTO `sys_role_menu` VALUES (4, 3175);
INSERT INTO `sys_role_menu` VALUES (4, 3176);
INSERT INTO `sys_role_menu` VALUES (4, 3178);
INSERT INTO `sys_role_menu` VALUES (4, 3179);
INSERT INTO `sys_role_menu` VALUES (4, 3180);
INSERT INTO `sys_role_menu` VALUES (4, 3181);
INSERT INTO `sys_role_menu` VALUES (4, 3184);
INSERT INTO `sys_role_menu` VALUES (4, 3185);
INSERT INTO `sys_role_menu` VALUES (4, 3186);
INSERT INTO `sys_role_menu` VALUES (4, 3187);
INSERT INTO `sys_role_menu` VALUES (4, 3189);
INSERT INTO `sys_role_menu` VALUES (4, 3190);
INSERT INTO `sys_role_menu` VALUES (4, 3191);
INSERT INTO `sys_role_menu` VALUES (4, 3192);
INSERT INTO `sys_role_menu` VALUES (4, 3196);
INSERT INTO `sys_role_menu` VALUES (4, 3197);
INSERT INTO `sys_role_menu` VALUES (4, 3198);
INSERT INTO `sys_role_menu` VALUES (4, 3199);
INSERT INTO `sys_role_menu` VALUES (4, 3200);
INSERT INTO `sys_role_menu` VALUES (4, 3202);
INSERT INTO `sys_role_menu` VALUES (4, 3203);
INSERT INTO `sys_role_menu` VALUES (4, 3204);
INSERT INTO `sys_role_menu` VALUES (4, 3205);
INSERT INTO `sys_role_menu` VALUES (4, 3206);
INSERT INTO `sys_role_menu` VALUES (4, 3207);
INSERT INTO `sys_role_menu` VALUES (4, 3208);
INSERT INTO `sys_role_menu` VALUES (4, 3210);
INSERT INTO `sys_role_menu` VALUES (4, 3211);
INSERT INTO `sys_role_menu` VALUES (4, 3212);
INSERT INTO `sys_role_menu` VALUES (4, 3213);
INSERT INTO `sys_role_menu` VALUES (4, 3214);
INSERT INTO `sys_role_menu` VALUES (4, 3217);
INSERT INTO `sys_role_menu` VALUES (4, 3218);
INSERT INTO `sys_role_menu` VALUES (4, 3219);
INSERT INTO `sys_role_menu` VALUES (4, 3220);
INSERT INTO `sys_role_menu` VALUES (4, 3223);
INSERT INTO `sys_role_menu` VALUES (4, 3224);
INSERT INTO `sys_role_menu` VALUES (4, 3225);
INSERT INTO `sys_role_menu` VALUES (4, 3226);
INSERT INTO `sys_role_menu` VALUES (4, 3229);
INSERT INTO `sys_role_menu` VALUES (4, 3230);
INSERT INTO `sys_role_menu` VALUES (4, 3231);
INSERT INTO `sys_role_menu` VALUES (4, 3232);
INSERT INTO `sys_role_menu` VALUES (4, 3235);
INSERT INTO `sys_role_menu` VALUES (4, 3236);
INSERT INTO `sys_role_menu` VALUES (4, 3237);
INSERT INTO `sys_role_menu` VALUES (4, 3238);
INSERT INTO `sys_role_menu` VALUES (4, 3239);
INSERT INTO `sys_role_menu` VALUES (4, 3240);
INSERT INTO `sys_role_menu` VALUES (4, 3243);
INSERT INTO `sys_role_menu` VALUES (4, 3244);
INSERT INTO `sys_role_menu` VALUES (4, 3249);
INSERT INTO `sys_role_menu` VALUES (4, 3250);
INSERT INTO `sys_role_menu` VALUES (4, 3251);
INSERT INTO `sys_role_menu` VALUES (4, 3252);
INSERT INTO `sys_role_menu` VALUES (4, 3253);
INSERT INTO `sys_role_menu` VALUES (4, 3256);
INSERT INTO `sys_role_menu` VALUES (4, 3269);
INSERT INTO `sys_role_menu` VALUES (4, 3270);
INSERT INTO `sys_role_menu` VALUES (4, 3271);
INSERT INTO `sys_role_menu` VALUES (4, 3277);
-- 删除视频配置表字段
ALTER TABLE `sip_config`
DROP COLUMN `tenant_id`,
DROP COLUMN `tenant_name`;
-- 更新老数据绑定到管理员账户
update iot_goview_project
set tenant_id = 1,
tenant_name = 'admin';
update iot_scene
set user_id = 1,
user_name = 'admin';
update iot_script
set user_id = 1,
user_name = 'admin';
update iot_alert
set tenant_id = 1,
tenant_name = 'admin';
update iot_alert_log
set user_id = 1;
update oauth_client_details
set tenant_id = 1,
tenant_name = 'admin';
update sys_user
set dept_id = 100;
-- 此机构为web端注册用户绑定机构，其中dept_id必须为101，后端写死的
INSERT INTO `sys_dept` (`dept_id`, `dept_user_id`, `parent_id`, `ancestors`, `dept_name`, `order_num`, `leader`, `phone`, `status`, `del_flag`, `create_by`, `create_time`, `update_by`, `update_time`, `email`, `dept_type`)
VALUES (101, 0, 100, '0,100', 'web端注册用户归属机构', 0, 'fastbee-web', '15888888885', '0', '0', 'admin', '2024-05-08 16:35:48', 'admin', '2024-05-08 16:36:15', NULL, 2);
-- 新增web端注册用户绑定机构
INSERT INTO `sys_role` (`role_name`, `role_key`, `role_sort`, `data_scope`, `menu_check_strictly`, `dept_check_strictly`, `status`, `del_flag`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
VALUES ('管理员', 'manager', 1, '1', 0, 0, '0', '0', '', '2024-05-08 16:35:49', '', NULL, NULL);
SELECT @parentId := LAST_INSERT_ID();
INSERT INTO `sys_role_menu` VALUES (@parentId, 3);
INSERT INTO `sys_role_menu` VALUES (@parentId, 4);
INSERT INTO `sys_role_menu` VALUES (@parentId, 5);
INSERT INTO `sys_role_menu` VALUES (@parentId, 100);
INSERT INTO `sys_role_menu` VALUES (@parentId, 101);
INSERT INTO `sys_role_menu` VALUES (@parentId, 103);
INSERT INTO `sys_role_menu` VALUES (@parentId, 114);
INSERT INTO `sys_role_menu` VALUES (@parentId, 115);
INSERT INTO `sys_role_menu` VALUES (@parentId, 116);
INSERT INTO `sys_role_menu` VALUES (@parentId, 1001);
INSERT INTO `sys_role_menu` VALUES (@parentId, 1002);
INSERT INTO `sys_role_menu` VALUES (@parentId, 1003);
INSERT INTO `sys_role_menu` VALUES (@parentId, 1004);
INSERT INTO `sys_role_menu` VALUES (@parentId, 1005);
INSERT INTO `sys_role_menu` VALUES (@parentId, 1006);
INSERT INTO `sys_role_menu` VALUES (@parentId, 1007);
INSERT INTO `sys_role_menu` VALUES (@parentId, 1008);
INSERT INTO `sys_role_menu` VALUES (@parentId, 1009);
INSERT INTO `sys_role_menu` VALUES (@parentId, 1010);
INSERT INTO `sys_role_menu` VALUES (@parentId, 1011);
INSERT INTO `sys_role_menu` VALUES (@parentId, 1012);
INSERT INTO `sys_role_menu` VALUES (@parentId, 1017);
INSERT INTO `sys_role_menu` VALUES (@parentId, 1018);
INSERT INTO `sys_role_menu` VALUES (@parentId, 1019);
INSERT INTO `sys_role_menu` VALUES (@parentId, 1020);
INSERT INTO `sys_role_menu` VALUES (@parentId, 1055);
INSERT INTO `sys_role_menu` VALUES (@parentId, 1056);
INSERT INTO `sys_role_menu` VALUES (@parentId, 1057);
INSERT INTO `sys_role_menu` VALUES (@parentId, 1058);
INSERT INTO `sys_role_menu` VALUES (@parentId, 1059);
INSERT INTO `sys_role_menu` VALUES (@parentId, 1060);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2000);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2001);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2002);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2003);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2006);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2007);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2008);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2009);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2010);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2011);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2012);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2013);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2014);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2015);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2016);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2017);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2018);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2019);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2020);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2021);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2022);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2023);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2024);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2043);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2044);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2045);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2046);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2047);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2048);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2049);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2050);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2051);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2054);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2067);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2068);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2069);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2070);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2071);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2072);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2085);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2086);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2087);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2088);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2089);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2090);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2099);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2100);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2101);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2102);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2103);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2104);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2105);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2106);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2107);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2108);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2109);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2111);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2112);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2136);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2137);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2138);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2139);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2140);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2143);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2144);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2145);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2146);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2147);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2148);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2149);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2167);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2168);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2169);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2170);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2171);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2172);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2173);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2174);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2175);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2176);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2177);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2178);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2179);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2180);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2181);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2182);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2183);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2184);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3000);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3001);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3002);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3003);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3004);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3005);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3006);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3007);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3008);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3009);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3010);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3011);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3012);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3013);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3014);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3015);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3016);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3017);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3018);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3019);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3020);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3021);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3022);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3023);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3024);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3025);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3026);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3027);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3028);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3029);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3030);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3031);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3032);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3033);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3034);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3035);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3036);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3037);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3038);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3039);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3040);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3041);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3042);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3043);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3044);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3046);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3047);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3048);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3049);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3051);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3052);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3055);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3099);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3100);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3102);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3103);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3104);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3105);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3106);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3107);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3108);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3109);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3110);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3111);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3112);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3113);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3114);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3115);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3116);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3117);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3147);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3148);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3149);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3150);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3151);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3152);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3153);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3154);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3155);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3156);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3157);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3158);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3159);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3160);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3161);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3162);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3163);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3164);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3165);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3166);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3167);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3168);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3169);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3170);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3171);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3172);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3173);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3174);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3175);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3176);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3177);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3178);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3179);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3180);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3181);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3182);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3183);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3184);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3185);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3186);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3187);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3188);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3189);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3190);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3191);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3197);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3198);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3199);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3200);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3201);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3202);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3203);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3204);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3205);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3206);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3207);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3208);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3209);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3210);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3211);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3214);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3215);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3216);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3217);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3218);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3219);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3220);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3221);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3222);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3223);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3224);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3225);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3226);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3237);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3238);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3239);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3240);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3247);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3248);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3249);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3250);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3251);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3252);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3253);
INSERT INTO `sys_role_dept` (`role_id`, `dept_id`) VALUES (@parentId, 101);
INSERT INTO `sys_user` (`dept_id`, `user_name`, `nick_name`, `user_type`, `email`, `phonenumber`, `sex`, `avatar`, `password`, `status`, `del_flag`, `login_ip`, `login_date`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
VALUES (101, 'fastbee-web', 'fastbee-web', '00', '', '15888888885', '0', '', '$2a$10$nwso5Yvdl6pfntn/wuz0MO6BqKTybKXdtb3f326XR8XRVaoD5OTYm', '0', '0', '', NULL, '', '2024-05-08 16:35:49', '', NULL, NULL);
SELECT @parentId := LAST_INSERT_ID();
update sys_dept
set dept_user_id = @parentId
where dept_id = 101;
INSERT INTO `sys_user_role`(`user_id`, `role_id`)
VALUES (@parentId, (select rd.role_id from sys_dept d left join sys_role_dept rd on d.dept_id = rd.dept_id where d.dept_id = 101));
-- web端注册用户绑定角色，其中role_key必须为general，后端代码里写死的
INSERT INTO `sys_role` (`role_name`, `role_key`, `role_sort`, `data_scope`, `menu_check_strictly`, `dept_check_strictly`, `status`, `del_flag`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
VALUES ('web端注册用户', 'general', 0, '1', 1, 1, '0', '0', 'fastbee-web', '2024-05-08 16:55:16', '', NULL, NULL);
SELECT @parentId := LAST_INSERT_ID();
INSERT INTO `sys_role_menu` VALUES (@parentId, 2000);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2007);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2008);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2009);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2010);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2011);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2012);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2013);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2014);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2015);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2016);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2017);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2018);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2019);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2020);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2021);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2043);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2044);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2147);
INSERT INTO `sys_role_menu` VALUES (@parentId, 2148);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3000);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3001);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3002);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3003);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3007);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3008);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3013);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3014);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3015);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3016);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3017);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3018);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3019);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3020);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3021);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3022);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3023);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3024);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3025);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3026);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3027);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3028);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3029);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3030);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3033);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3034);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3035);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3039);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3040);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3041);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3042);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3043);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3044);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3206);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3207);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3208);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3209);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3210);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3214);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3215);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3216);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3217);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3218);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3219);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3220);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3221);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3222);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3247);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3248);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3249);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3250);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3251);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3252);
INSERT INTO `sys_role_menu` VALUES (@parentId, 3253);
INSERT INTO `sys_role_dept` (`role_id`, `dept_id`) VALUES (@parentId, 101);
-- 新增设备联动、终端用户菜单，需自行修改parent_id
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
VALUES ('设备联动', 2000, 7, 'linkage', 'iot/device/device-linkage', NULL, 1, 0, 'C', '0', '0', NULL, 'tree-table', 'admin', '2024-05-10 17:54:12', '', NULL, '');
INSERT INTO `sys_menu` (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
VALUES ('终端用户', 5, 5, 'terminal', 'system/terminalUser/index', NULL, 1, 0, 'C', '0', '0', NULL, 'build', 'admin', '2024-05-10 17:55:00', '', NULL, '');
-- 修复小程序头像修改字段超长问题
ALTER TABLE `sys_user`
MODIFY COLUMN `avatar` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '头像地址';
-- 设备新增告警表
-- 新增设备告警用户配置表
DROP TABLE IF EXISTS `iot_device_alert_user`;
CREATE TABLE `iot_device_alert_user` (
`device_id` bigint(20) NOT NULL COMMENT '设备id',
`user_id` bigint(20) NOT NULL COMMENT '用户id',
PRIMARY KEY (`device_id`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='设备告警用户表';
-- 菜单 SQL
insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('告警用户', '2007', '1', 'user', 'iot/device/alert/user', 1, 0, 'C', '0', '0', 'iot:device:alert:user:list', '#', 'admin', sysdate(), '', null, '设备告警用户菜单');
-- 按钮父菜单ID
SELECT @parentId := LAST_INSERT_ID();
-- 按钮 SQL
insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('告警用户查询', @parentId, '1', '#', '', 1, 0, 'F', '0', '0', 'iot:device:alert:user:query', '#', 'admin', sysdate(), '', null, '');
insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('告警用户新增', @parentId, '2', '#', '', 1, 0, 'F', '0', '0', 'iot:device:alert:user:add', '#', 'admin', sysdate(), '', null, '');
insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('告警用户删除', @parentId, '3', '#', '', 1, 0, 'F', '0', '0', 'iot:device:alert:user:remove', '#', 'admin', sysdate(), '', null, '');
-- 设备告警记录更改一下菜单权限标识，menu_id请自行修改，或者直接在菜单改就行了
update sys_menu
set perms = 'iot:alertLog:list'
where menu_id = 2067;
update sys_menu
set perms = 'iot:alertLog:query'
where menu_id = 2068;
update sys_menu
set perms = 'iot:alertLog:add'
where menu_id = 2069;
update sys_menu
set perms = 'iot:alertLog:edit'
where menu_id = 2070;
update sys_menu
set perms = 'iot:alertLog:remove'
where menu_id = 2071;
update sys_menu
set perms = 'iot:alertLog:export'
where menu_id = 2072;
-- ----------------------------
-- Table structure for oss_config
-- ----------------------------
DROP TABLE IF EXISTS `oss_config`;
CREATE TABLE `oss_config`
(
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
`tenant_id` bigint(20) NOT NULL DEFAULT 1 COMMENT '租户ID',
`tenant_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '租户名称',
`config_key` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '配置key',
`access_key` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT 'accessKey',
`secret_key` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '秘钥',
`bucket_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '桶名称',
`prefix` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT'前缀',
`endpoint` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '访问站点',
`domain` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '自定义域名',
`is_https` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N' COMMENT '是否https（Y=是,N=否）',
`region` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '域',
`access_policy` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT '桶权限类型(0=private 1=public 2=custom)',
`status` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '1' COMMENT '是否默认（0=是,1=否）',
`ext1` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '扩展字段',
`del_flag` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
`create_by` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '创建者',
`create_time` datetime(0) NOT NULL COMMENT '创建时间',
`update_by` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '更新者',
`update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
`remark` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
AUTO_INCREMENT = 1
CHARACTER SET = utf8
COLLATE = utf8_general_ci COMMENT = '对象存储配置表'
ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of oss_config
-- ----------------------------
insert into oss_config values (1, 1, 'admin', 'aliyun', 'XXXXXXXXXXXXXXX', 'XXXXXXXXXXXXXXX', 'fastbee','', 'oss-cn-beijing.aliyuncs.com', '','N', '','0' ,'0', '', '0', '', '2023-02-25 23:15:57', '', NULL, NULL);
insert into oss_config values (2, 1, 'admin', 'qiniu', 'XXXXXXXXXXXXXXX', 'XXXXXXXXXXXXXXX', 'fastbee','', 's3-cn-north-1.qiniucs.com', '','N', '','1' ,'1', '', '0', '', '2023-02-25 23:15:57', '', NULL, NULL);
-- ----------------------------
-- Table structure for oss_detail
-- ----------------------------
DROP TABLE IF EXISTS `oss_detail`;
CREATE TABLE `oss_detail`
(
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '文件id',
`tenant_id` bigint(20) NOT NULL DEFAULT 1 COMMENT '租户ID',
`tenant_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '租户名称',
`file_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '文件名',
`original_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '原名',
`file_suffix` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '文件后缀名',
`url` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT 'URL地址',
`service` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'aliyun' COMMENT '服务商',
`del_flag` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
`create_by` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '创建者',
`create_time` datetime(0) NOT NULL COMMENT '创建时间',
`update_by` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '更新者',
`update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
`remark` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
AUTO_INCREMENT = 1
CHARACTER SET = utf8
COLLATE = utf8_general_ci COMMENT = '文件记录表'
ROW_FORMAT = Dynamic;
-- 文件记录菜单SQL
INSERT INTO sys_menu (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
VALUES ('文件记录', 3046, 4, 'detail', 'system/oss/index', NULL, 1, 0, 'C', '0', '0', 'oss:detail:list', 'documentation', 'admin', sysdate(), '', null, '文件记录菜单');
-- 按钮父菜单ID
SELECT @parentId := LAST_INSERT_ID();
-- 文件记录按钮SQL
INSERT INTO sys_menu (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
VALUES ('文件记录查询', @parentId, 1, '#', '', NULL, 1, 0, 'F', '0', '0', 'oss:detail:query', '#', 'admin', sysdate(), '', NULL, '');
INSERT INTO sys_menu (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
VALUES ('文件记录新增', @parentId, 2, '#', '', NULL, 1, 0, 'F', '0', '0', 'oss:detail:add', '#', 'admin', sysdate(), '', NULL, '');
INSERT INTO sys_menu (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
VALUES ('文件记录修改', @parentId, 3, '#', '', NULL, 1, 0, 'F', '0', '0', 'oss:detail:edit', '#', 'admin', sysdate(), '', NULL, '');
INSERT INTO sys_menu (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
VALUES ('文件记录删除', @parentId, 4, '#', '', NULL, 1, 0, 'F', '0', '0', 'oss:detail:remove', '#', 'admin', sysdate(), '', NULL, '');
INSERT INTO sys_menu (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
VALUES ('文件记录导出', @parentId, 5, '#', '', NULL, 1, 0, 'F', '0', '0', 'oss:detail:export', '#', 'admin', sysdate(), '', NULL, '');
INSERT INTO sys_menu (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
VALUES ('文件存储配置新增', @parentId, 6, '', NULL, NULL, 1, 0, 'F', '0', '0', 'oss:config:add', '#', 'admin', sysdate(), 'admin', null, '');
INSERT INTO sys_menu (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
VALUES ('文件存储配置修改', @parentId, 7, '', NULL, NULL, 1, 0, 'F', '0', '0', 'oss:config:edit', '#', 'admin', sysdate(), '', NULL, '');
INSERT INTO sys_menu (`menu_name`, `parent_id`, `order_num`, `path`, `component`, `query`, `is_frame`, `is_cache`, `menu_type`, `visible`, `status`, `perms`, `icon`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
VALUES ('文件存储配置删除', @parentId, 8, '', NULL, NULL, 1, 0, 'F', '0', '0', 'oss:config:remove', '#', 'admin', sysdate(), '', NULL, '');
Reference in New Issue View Git Blame Copy Permalink