186 lines
9.2 KiB
SQL
186 lines
9.2 KiB
SQL
-- =====================================================
|
||
-- 项目管理 - 数据表 + 菜单权限配置 SQL
|
||
-- 在管理后台新增"项目管理"独立主菜单
|
||
--
|
||
-- 菜单结构：
|
||
-- 项目管理(2130, M-type独立目录) ← 与"招聘会管理"平级
|
||
-- └ 项目列表(2131, C-type页面)
|
||
-- ├ 项目查询(2132, F-type按钮)
|
||
-- ├ 项目新增(2133, F-type按钮)
|
||
-- ├ 项目修改(2134, F-type按钮)
|
||
-- └ 项目删除(2135, F-type按钮)
|
||
--
|
||
-- 使用固定ID 2130-2135，避免与已有数据冲突
|
||
-- =====================================================
|
||
|
||
-- =====================================================
|
||
-- Step 1: 创建项目表
|
||
-- =====================================================
|
||
CREATE SEQUENCE IF NOT EXISTS shz.cms_project_id_seq
|
||
START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
|
||
|
||
CREATE TABLE IF NOT EXISTS shz.cms_project (
|
||
project_id BIGINT NOT NULL DEFAULT nextval('shz.cms_project_id_seq'::regclass) PRIMARY KEY,
|
||
title VARCHAR(255) NOT NULL,
|
||
description TEXT,
|
||
required_personnel TEXT,
|
||
cooperation_mode VARCHAR(100),
|
||
industry VARCHAR(100),
|
||
region VARCHAR(100),
|
||
contact_name VARCHAR(100),
|
||
contact_phone VARCHAR(50),
|
||
contact_email VARCHAR(100),
|
||
status CHAR(1) DEFAULT '0',
|
||
create_by VARCHAR(64),
|
||
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
update_by VARCHAR(64),
|
||
update_time TIMESTAMP,
|
||
del_flag CHAR(1) DEFAULT '0',
|
||
remark VARCHAR(500)
|
||
);
|
||
|
||
COMMENT ON TABLE shz.cms_project IS '项目管理表';
|
||
COMMENT ON COLUMN shz.cms_project.project_id IS '项目ID';
|
||
COMMENT ON COLUMN shz.cms_project.title IS '项目名称';
|
||
COMMENT ON COLUMN shz.cms_project.description IS '项目描述';
|
||
COMMENT ON COLUMN shz.cms_project.required_personnel IS '项目需求人员';
|
||
COMMENT ON COLUMN shz.cms_project.cooperation_mode IS '合作方式';
|
||
COMMENT ON COLUMN shz.cms_project.industry IS '行业';
|
||
COMMENT ON COLUMN shz.cms_project.region IS '地区';
|
||
COMMENT ON COLUMN shz.cms_project.contact_name IS '联系人';
|
||
COMMENT ON COLUMN shz.cms_project.contact_phone IS '联系电话';
|
||
COMMENT ON COLUMN shz.cms_project.contact_email IS '联系邮箱';
|
||
COMMENT ON COLUMN shz.cms_project.status IS '状态：0=草稿, 1=发布';
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_cms_project_title ON shz.cms_project(title);
|
||
CREATE INDEX IF NOT EXISTS idx_cms_project_industry ON shz.cms_project(industry);
|
||
CREATE INDEX IF NOT EXISTS idx_cms_project_region ON shz.cms_project(region);
|
||
CREATE INDEX IF NOT EXISTS idx_cms_project_cooperation_mode ON shz.cms_project(cooperation_mode);
|
||
CREATE INDEX IF NOT EXISTS idx_cms_project_del_flag ON shz.cms_project(del_flag);
|
||
|
||
-- =====================================================
|
||
-- Step 2: 创建项目评论表
|
||
-- =====================================================
|
||
CREATE SEQUENCE IF NOT EXISTS shz.cms_project_comment_id_seq
|
||
START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
|
||
|
||
CREATE TABLE IF NOT EXISTS shz.cms_project_comment (
|
||
comment_id BIGINT NOT NULL DEFAULT nextval('shz.cms_project_comment_id_seq'::regclass) PRIMARY KEY,
|
||
project_id BIGINT NOT NULL,
|
||
user_id BIGINT,
|
||
user_name VARCHAR(100),
|
||
content TEXT NOT NULL,
|
||
create_by VARCHAR(64),
|
||
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
del_flag CHAR(1) DEFAULT '0'
|
||
);
|
||
|
||
COMMENT ON TABLE shz.cms_project_comment IS '项目评论表';
|
||
COMMENT ON COLUMN shz.cms_project_comment.comment_id IS '评论ID';
|
||
COMMENT ON COLUMN shz.cms_project_comment.project_id IS '关联项目ID';
|
||
COMMENT ON COLUMN shz.cms_project_comment.user_id IS '用户ID';
|
||
COMMENT ON COLUMN shz.cms_project_comment.user_name IS '用户名';
|
||
COMMENT ON COLUMN shz.cms_project_comment.content IS '评论内容';
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_cms_project_comment_project_id ON shz.cms_project_comment(project_id);
|
||
|
||
-- =====================================================
|
||
-- Step 3: 新增菜单权限
|
||
-- 结构：项目管理(2130, M-type目录) → 项目列表(2131, C-type) → 按钮(2132-2135, F-type)
|
||
-- =====================================================
|
||
|
||
-- Step 3.1: 创建主菜单目录（M=目录, parent_id=0 表示顶级菜单）
|
||
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query, route_name, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
SELECT 2130, '项目管理', 0,
|
||
5, 'project', '', '', '', 1, 0, 'M', '0', '0',
|
||
'', 'project', 'admin', CURRENT_TIMESTAMP, '', null, '项目管理目录'
|
||
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 2130);
|
||
|
||
-- Step 3.2: 创建菜单页面（C=菜单页面）
|
||
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query, route_name, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
SELECT 2131, '项目列表', 2130,
|
||
1, 'project-mgmt', 'Management/ProjectMgmt', '', 'ProjectMgmt', 1, 0, 'C', '0', '0',
|
||
'cms:project:list', '#', 'admin', CURRENT_TIMESTAMP, '', null, '项目列表菜单'
|
||
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 2131);
|
||
|
||
-- Step 3.3: 新增按钮权限（F=按钮, parent_id=2131 归属到项目列表下）
|
||
-- 项目查询
|
||
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query, route_name, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
SELECT 2132, '项目查询', 2131, 1, '', '', '', '', 1, 0, 'F', '0', '0',
|
||
'cms:project:query', '#', 'admin', CURRENT_TIMESTAMP, '', null, ''
|
||
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 2132);
|
||
|
||
-- 项目新增
|
||
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query, route_name, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
SELECT 2133, '项目新增', 2131, 2, '', '', '', '', 1, 0, 'F', '0', '0',
|
||
'cms:project:add', '#', 'admin', CURRENT_TIMESTAMP, '', null, ''
|
||
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 2133);
|
||
|
||
-- 项目修改
|
||
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query, route_name, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
SELECT 2134, '项目修改', 2131, 3, '', '', '', '', 1, 0, 'F', '0', '0',
|
||
'cms:project:edit', '#', 'admin', CURRENT_TIMESTAMP, '', null, ''
|
||
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 2134);
|
||
|
||
-- 项目删除
|
||
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query, route_name, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
|
||
SELECT 2135, '项目删除', 2131, 4, '', '', '', '', 1, 0, 'F', '0', '0',
|
||
'cms:project:remove', '#', 'admin', CURRENT_TIMESTAMP, '', null, ''
|
||
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 2135);
|
||
|
||
-- =====================================================
|
||
-- Step 3.4: 将新菜单分配给角色
|
||
-- 管理员角色（role_key='admin'）通配符 *:*:* 自动拥有全部权限，无需分配
|
||
-- =====================================================
|
||
INSERT INTO sys_role_menu (role_id, menu_id)
|
||
SELECT r.role_id, 2130
|
||
FROM sys_role r
|
||
WHERE r.role_key != 'admin'
|
||
AND NOT EXISTS (SELECT 1 FROM sys_role_menu rm WHERE rm.role_id = r.role_id AND rm.menu_id = 2130);
|
||
|
||
INSERT INTO sys_role_menu (role_id, menu_id)
|
||
SELECT r.role_id, 2131
|
||
FROM sys_role r
|
||
WHERE r.role_key != 'admin'
|
||
AND NOT EXISTS (SELECT 1 FROM sys_role_menu rm WHERE rm.role_id = r.role_id AND rm.menu_id = 2131);
|
||
|
||
INSERT INTO sys_role_menu (role_id, menu_id)
|
||
SELECT r.role_id, 2132
|
||
FROM sys_role r
|
||
WHERE r.role_key != 'admin'
|
||
AND NOT EXISTS (SELECT 1 FROM sys_role_menu rm WHERE rm.role_id = r.role_id AND rm.menu_id = 2132);
|
||
|
||
INSERT INTO sys_role_menu (role_id, menu_id)
|
||
SELECT r.role_id, 2133
|
||
FROM sys_role r
|
||
WHERE r.role_key != 'admin'
|
||
AND NOT EXISTS (SELECT 1 FROM sys_role_menu rm WHERE rm.role_id = r.role_id AND rm.menu_id = 2133);
|
||
|
||
INSERT INTO sys_role_menu (role_id, menu_id)
|
||
SELECT r.role_id, 2134
|
||
FROM sys_role r
|
||
WHERE r.role_key != 'admin'
|
||
AND NOT EXISTS (SELECT 1 FROM sys_role_menu rm WHERE rm.role_id = r.role_id AND rm.menu_id = 2134);
|
||
|
||
INSERT INTO sys_role_menu (role_id, menu_id)
|
||
SELECT r.role_id, 2135
|
||
FROM sys_role r
|
||
WHERE r.role_key != 'admin'
|
||
AND NOT EXISTS (SELECT 1 FROM sys_role_menu rm WHERE rm.role_id = r.role_id AND rm.menu_id = 2135);
|
||
|
||
-- =====================================================
|
||
-- 验证 SQL（可选执行）
|
||
-- =====================================================
|
||
-- 检查表是否创建成功
|
||
-- SELECT * FROM shz.cms_project LIMIT 1;
|
||
-- SELECT * FROM shz.cms_project_comment LIMIT 1;
|
||
|
||
-- 检查菜单是否创建成功
|
||
-- SELECT * FROM sys_menu WHERE menu_id BETWEEN 2130 AND 2135;
|
||
|
||
-- 检查角色菜单关联
|
||
-- SELECT r.role_name, m.menu_name FROM sys_role_menu rm
|
||
-- JOIN sys_role r ON r.role_id = rm.role_id
|
||
-- JOIN sys_menu m ON m.menu_id = rm.menu_id
|
||
-- WHERE rm.menu_id BETWEEN 2130 AND 2135;
|