dis-check-menu.sql
3.29 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
-- =====================================================
-- dis核对 完整部署脚本
-- 数据库: urban_ops_agent
-- 包含: 表结构变更 + 菜单创建 + 角色授权
-- 执行方式: 在对应环境的数据库中执行此 SQL
-- =====================================================
-- ==========================================
-- Part 0: garden_gis_road 表结构变更
-- ==========================================
-- 新增多边形围栏边界坐标字段（MySQL 8.x 兼容写法）
SET @col_exists = (
SELECT COUNT(*) FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'garden_gis_road'
AND COLUMN_NAME = 'gis_polygon_coords'
);
SET @sql = IF(@col_exists = 0,
'ALTER TABLE garden_gis_road ADD COLUMN gis_polygon_coords LONGTEXT NOT NULL COMMENT ''多边形/矩形围栏边界坐标，格式：POLYGON((x1 y1, x2 y2, ..., xn yn, x1 y1)) 存储的是WGS84坐标系''',
'SELECT ''COLUMN gis_polygon_coords already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- ==========================================
-- Part 1: 菜单创建
-- ==========================================
-- 1. 新增「dis核对」子菜单（位于电子围栏下）
INSERT INTO system_menu
(name, permission, type, sort, parent_id, path, icon, component, component_name, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
VALUES
('gis数据核对', '', 2, 1, 5698, 'disCheck', 'ep:view', 'fence/dis-check/index', 'DisCheck', 0, 1, 0, 1, '1', NOW(), '1', NOW(), 0);
-- 2. 获取上一步插入的菜单ID，用于后续按钮权限插入
SET @dis_check_menu_id = LAST_INSERT_ID();
-- 3. 新增「导出」按钮权限（挂在新菜单下）
INSERT INTO system_menu
(name, permission, type, sort, parent_id, path, icon, component, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
VALUES
('导出', 'garden:gis-road:export', 3, 0, @dis_check_menu_id, '', '', '', 0, 1, 0, 1, '1', NOW(), '1', NOW(), 0);
-- 4. 新增「查询」按钮权限
INSERT INTO system_menu
(name, permission, type, sort, parent_id, path, icon, component, status, visible, keep_alive, always_show, creator, create_time, updater, update_time, deleted)
VALUES
('查询', 'garden:gis-road:query', 3, 1, @dis_check_menu_id, '', '', '', 0, 1, 0, 1, '1', NOW(), '1', NOW(), 0);
-- 5. 给超级管理员(role_id=1)分配菜单权限
INSERT INTO system_role_menu (role_id, menu_id, creator, create_time, updater, update_time)
VALUES
(1, @dis_check_menu_id, '1', NOW(), '1', NOW());
INSERT INTO system_role_menu (role_id, menu_id, creator, create_time, updater, update_time)
SELECT 1, id, '1', NOW(), '1', NOW()
FROM system_menu
WHERE parent_id = @dis_check_menu_id AND deleted = 0;
-- =====================================================
-- 验证插入结果
-- =====================================================
SELECT id, name, type, parent_id, path, component, permission
FROM system_menu
WHERE parent_id = 5698 OR id = @dis_check_menu_id
ORDER BY parent_id, sort;
SELECT rm.role_id, rm.menu_id, m.name, m.permission
FROM system_role_menu rm
JOIN system_menu m ON rm.menu_id = m.id
WHERE rm.menu_id = @dis_check_menu_id
OR rm.menu_id IN (SELECT id FROM system_menu WHERE parent_id = @dis_check_menu_id);