82 lines
3.5 KiB
PL/PgSQL
82 lines
3.5 KiB
PL/PgSQL
-- ============================================================
|
|
-- V4__project_staff_multi_role.sql
|
|
-- 项目员工多角色支持
|
|
-- 修改 project_staff 表支持多角色，添加 project_staff_role 关联表
|
|
-- ============================================================
|
|
|
|
BEGIN;
|
|
|
|
-- ============================================================
|
|
-- 1. 修改 project_staff 表 - 添加 UUID 主键，添加唯一约束
|
|
-- ============================================================
|
|
|
|
-- 如果存在旧主键约束则删除
|
|
ALTER TABLE project_staff DROP CONSTRAINT IF EXISTS project_staff_pkey;
|
|
|
|
-- 添加 UUID 主键列
|
|
ALTER TABLE project_staff ADD COLUMN IF NOT EXISTS id UUID PRIMARY KEY DEFAULT gen_random_uuid();
|
|
|
|
-- 添加 user_id + project_id 唯一约束（如果尚未存在）
|
|
ALTER TABLE project_staff ADD CONSTRAINT uk_project_staff_user_project UNIQUE (user_id, project_id);
|
|
|
|
-- ============================================================
|
|
-- 2. 创建 project_staff_role 关联表
|
|
-- ============================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS project_staff_role (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
staff_id UUID NOT NULL,
|
|
role_id UUID NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT uk_project_staff_role_staff_role UNIQUE (staff_id, role_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_project_staff_role_staff ON project_staff_role(staff_id);
|
|
CREATE INDEX IF NOT EXISTS idx_project_staff_role_role ON project_staff_role(role_id);
|
|
|
|
-- ============================================================
|
|
-- 3. 添加外键约束
|
|
-- ============================================================
|
|
|
|
ALTER TABLE project_staff_role ADD CONSTRAINT fk_project_staff_role_staff
|
|
FOREIGN KEY (staff_id) REFERENCES project_staff(id) ON DELETE CASCADE;
|
|
ALTER TABLE project_staff_role ADD CONSTRAINT fk_project_staff_role_role
|
|
FOREIGN KEY (role_id) REFERENCES auth_role(id);
|
|
|
|
-- ============================================================
|
|
-- 4. 迁移现有数据（如果有 role_code 字段）
|
|
-- ============================================================
|
|
|
|
-- 检查 role_code 字段是否存在，如果存在则迁移
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'project_staff' AND column_name = 'role_code') THEN
|
|
-- 迁移数据：基于 role_code 关联 auth_role
|
|
INSERT INTO project_staff_role (staff_id, role_id, created_at)
|
|
SELECT ps.id, r.id, COALESCE(ps.created_at, CURRENT_TIMESTAMP)
|
|
FROM project_staff ps
|
|
INNER JOIN auth_role r ON r.code = ps.role_code
|
|
WHERE ps.role_code IS NOT NULL
|
|
ON CONFLICT (staff_id, role_id) DO NOTHING;
|
|
END IF;
|
|
END $$;
|
|
|
|
-- ============================================================
|
|
-- 5. 添加注释
|
|
-- ============================================================
|
|
|
|
COMMENT ON TABLE project_staff_role IS '项目员工角色关联表';
|
|
COMMENT ON COLUMN project_staff_role.staff_id IS '项目员工ID';
|
|
COMMENT ON COLUMN project_staff_role.role_id IS '角色ID';
|
|
|
|
COMMIT;
|
|
|
|
-- ============================================================
|
|
-- 回滚脚本（如果需要回滚）
|
|
-- ============================================================
|
|
|
|
-- DROP TABLE IF EXISTS project_staff_role;
|
|
-- ALTER TABLE project_staff DROP COLUMN IF EXISTS id;
|
|
-- ALTER TABLE project_staff ADD CONSTRAINT project_staff_pkey PRIMARY KEY (user_id);
|
|
-- ALTER TABLE project_staff DROP CONSTRAINT IF EXISTS uk_project_staff_user_project;
|