张家训
77 lines
4.5 KiB
SQL
77 lines
4.5 KiB
SQL
-- ============================================================
|
||
-- 三角洲行动 · 改枪码模块（delta_gun_code）
|
||
-- 数据库: freedom_db
|
||
-- 用途: 后台维护改枪码 + 无需鉴权的公开复制页面数据源
|
||
-- 说明: 可重复执行（CREATE TABLE IF NOT EXISTS / 幂等 INSERT 判断）
|
||
-- 执行后需重新登录系统（或退出登录）刷新菜单缓存方可看到新菜单
|
||
-- ============================================================
|
||
|
||
USE `freedom_db`;
|
||
|
||
-- 1. 改枪码数据表
|
||
CREATE TABLE IF NOT EXISTS `delta_gun_code` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键',
|
||
`name` VARCHAR(100) NOT NULL COMMENT '配装名称',
|
||
`gun_name` VARCHAR(100) DEFAULT NULL COMMENT '枪械/载具名称',
|
||
`category` VARCHAR(50) DEFAULT NULL COMMENT '分类标签（主武器/副武器/载具配置等）',
|
||
`code` TEXT NOT NULL COMMENT '改枪码内容',
|
||
`remark` VARCHAR(500) DEFAULT NULL COMMENT '备注说明',
|
||
`sort` INT DEFAULT 0 COMMENT '排序',
|
||
`status` TINYINT DEFAULT 1 COMMENT '状态：1显示 0隐藏',
|
||
`copy_count` INT DEFAULT 0 COMMENT '被复制次数',
|
||
`create_by` VARCHAR(50) DEFAULT NULL COMMENT '创建者',
|
||
`create_date` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_by` VARCHAR(50) DEFAULT NULL COMMENT '更新者',
|
||
`update_date` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`del_flag` TINYINT DEFAULT 0 COMMENT '删除标记',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_delta_code_status` (`status`, `del_flag`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8mb4 COMMENT='三角洲改枪码表';
|
||
|
||
-- 2. 菜单权限：一级菜单「三角洲」
|
||
INSERT INTO `t_sys_permission`
|
||
(`parent_id`, `permission_code`, `permission_name`, `permission_desc`, `level`, `icon`, `url`, `component`, `sort`, `create_by`, `create_date`, `del_flag`)
|
||
SELECT 0, 'delta', '三角洲', '三角洲行动相关功能', 1, 'md-game-controller-a', '', '', 9, 'admin', NOW(), 0
|
||
FROM DUAL
|
||
WHERE NOT EXISTS (SELECT 1 FROM `t_sys_permission` WHERE `permission_code` = 'delta' AND `del_flag` = 0);
|
||
|
||
-- 3. 菜单权限：二级菜单「改枪码管理」
|
||
SET @delta_parent_id = (SELECT `id` FROM `t_sys_permission` WHERE `permission_code` = 'delta' AND `del_flag` = 0 ORDER BY `id` LIMIT 1);
|
||
|
||
INSERT INTO `t_sys_permission`
|
||
(`parent_id`, `permission_code`, `permission_name`, `permission_desc`, `level`, `icon`, `url`, `component`, `sort`, `create_by`, `create_date`, `del_flag`)
|
||
SELECT @delta_parent_id, 'delta:gun-code', '改枪码管理', '三角洲改枪码维护与分享', 2, 'md-code-working', '/delta/gun-code', '/delta/delta-gun-code', 1, 'admin', NOW(), 0
|
||
FROM DUAL
|
||
WHERE NOT EXISTS (SELECT 1 FROM `t_sys_permission` WHERE `permission_code` = 'delta:gun-code' AND `del_flag` = 0);
|
||
|
||
-- 4. 角色授权：给所有现有角色挂上新菜单（幂等）
|
||
INSERT INTO `t_sys_role_permission` (`role_id`, `permission_id`, `create_by`, `create_date`)
|
||
SELECT r.`id`, p.`id`, 'admin', NOW()
|
||
FROM `t_sys_role` r
|
||
JOIN `t_sys_permission` p ON p.`permission_code` IN ('delta', 'delta:gun-code') AND p.`del_flag` = 0
|
||
WHERE r.`del_flag` = 0
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM `t_sys_role_permission` rp
|
||
WHERE rp.`role_id` = r.`id` AND rp.`permission_id` = p.`id`
|
||
);
|
||
|
||
-- 5. 菜单权限：二级菜单「改枪码录入」
|
||
SET @delta_parent_id = (SELECT `id` FROM `t_sys_permission` WHERE `permission_code` = 'delta' AND `del_flag` = 0 ORDER BY `id` LIMIT 1);
|
||
|
||
INSERT INTO `t_sys_permission`
|
||
(`parent_id`, `permission_code`, `permission_name`, `permission_desc`, `level`, `icon`, `url`, `component`, `sort`, `create_by`, `create_date`, `del_flag`)
|
||
SELECT @delta_parent_id, 'delta:gun-code-entry', '改枪码录入', '快速录入三角洲改枪码', 2, 'md-create', '/delta/gun-code-entry', '/delta/delta-gun-code-entry', 0, 'admin', NOW(), 0
|
||
FROM DUAL
|
||
WHERE NOT EXISTS (SELECT 1 FROM `t_sys_permission` WHERE `permission_code` = 'delta:gun-code-entry' AND `del_flag` = 0);
|
||
|
||
-- 6. 角色授权：给所有现有角色挂上「改枪码录入」菜单（幂等）
|
||
INSERT INTO `t_sys_role_permission` (`role_id`, `permission_id`, `create_by`, `create_date`)
|
||
SELECT r.`id`, p.`id`, 'admin', NOW()
|
||
FROM `t_sys_role` r
|
||
JOIN `t_sys_permission` p ON p.`permission_code` = 'delta:gun-code-entry' AND p.`del_flag` = 0
|
||
WHERE r.`del_flag` = 0
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM `t_sys_role_permission` rp
|
||
WHERE rp.`role_id` = r.`id` AND rp.`permission_id` = p.`id`
|
||
);
|