sql/migrate_roles_to_access_levels.sql - master - Riny/course_project

sql/migrate_roles_to_access_levels.sql
106 строк4 KB
Riny
Загрузить файлы в «»
26 июн 2026, 03:52
26 июн 2026, 03:521858d95
-- Миграция старой системы ADMIN/USER на уровни доступа 0-4.
-- Выполнять для уже созданной старой базы данных.
-- Скрипт больше не создаёт английские роли. Если английские роли уже были
-- добавлены прошлой версией приложения, их назначения переносятся на ваши
-- роли с таким же уровнем доступа, после чего английские роли удаляются.

START TRANSACTION;

UPDATE roles
SET access_level = 1
WHERE name = 'ADMIN' AND access_level = 10;

UPDATE roles
SET access_level = 4
WHERE name = 'USER' AND access_level = 1;

UPDATE roles
SET access_level = 1
WHERE access_level < 0 OR access_level > 4;

COMMIT;

SET @constraint_exists := (
    SELECT COUNT(*)
    FROM information_schema.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = DATABASE()
      AND TABLE_NAME = 'roles'
      AND CONSTRAINT_NAME = 'chk_roles_access_level'
      AND CONSTRAINT_TYPE = 'CHECK'
);

SET @drop_sql := IF(
    @constraint_exists > 0,
    'ALTER TABLE roles DROP CHECK chk_roles_access_level',
    'SELECT 1'
);
PREPARE drop_stmt FROM @drop_sql;
EXECUTE drop_stmt;
DEALLOCATE PREPARE drop_stmt;

ALTER TABLE roles
ADD CONSTRAINT chk_roles_access_level CHECK (access_level BETWEEN 0 AND 4);

START TRANSACTION;

-- Русские имена для пустой новой базы. Если у вас уже есть свои роли с такими
-- уровнями доступа, они останутся основными, а эти записи не будут добавляться.
INSERT INTO roles (name, access_level)
SELECT 'Администратор', 1
WHERE NOT EXISTS (SELECT 1 FROM roles WHERE access_level = 1);

INSERT INTO roles (name, access_level)
SELECT 'HR - специалист', 2
WHERE NOT EXISTS (SELECT 1 FROM roles WHERE access_level = 2);

INSERT INTO roles (name, access_level)
SELECT 'Менеджер', 3
WHERE NOT EXISTS (SELECT 1 FROM roles WHERE access_level = 3);

INSERT INTO roles (name, access_level)
SELECT 'Работник', 4
WHERE NOT EXISTS (SELECT 1 FROM roles WHERE access_level = 4);

INSERT INTO roles (name, access_level)
SELECT 'Гость', 0
WHERE NOT EXISTS (SELECT 1 FROM roles WHERE access_level = 0);

-- Переносим назначения со старых английских ролей на первую существующую
-- неанглийскую роль с тем же уровнем доступа.
INSERT IGNORE INTO user_roles (id_role, id_user)
SELECT target.id_role, ur.id_user
FROM user_roles ur
JOIN roles source ON source.id_role = ur.id_role
JOIN roles target ON target.access_level = source.access_level
WHERE source.name IN ('ADMIN', 'USER', 'USER_MANAGER', 'WORK_MANAGER', 'EMPLOYEE', 'NO_ACCESS')
  AND target.name NOT IN ('ADMIN', 'USER', 'USER_MANAGER', 'WORK_MANAGER', 'EMPLOYEE', 'NO_ACCESS')
  AND target.id_role = (
      SELECT MIN(r2.id_role)
      FROM roles r2
      WHERE r2.access_level = source.access_level
        AND r2.name NOT IN ('ADMIN', 'USER', 'USER_MANAGER', 'WORK_MANAGER', 'EMPLOYEE', 'NO_ACCESS')
  );

DELETE ur
FROM user_roles ur
JOIN roles r ON r.id_role = ur.id_role
WHERE r.name IN ('ADMIN', 'USER', 'USER_MANAGER', 'WORK_MANAGER', 'EMPLOYEE', 'NO_ACCESS')
  AND EXISTS (
      SELECT 1
      FROM roles target
      WHERE target.access_level = r.access_level
        AND target.name NOT IN ('ADMIN', 'USER', 'USER_MANAGER', 'WORK_MANAGER', 'EMPLOYEE', 'NO_ACCESS')
  );

DELETE r
FROM roles r
WHERE r.name IN ('ADMIN', 'USER', 'USER_MANAGER', 'WORK_MANAGER', 'EMPLOYEE', 'NO_ACCESS')
  AND NOT EXISTS (SELECT 1 FROM user_roles ur WHERE ur.id_role = r.id_role)
  AND EXISTS (
      SELECT 1
      FROM roles target
      WHERE target.access_level = r.access_level
        AND target.name NOT IN ('ADMIN', 'USER', 'USER_MANAGER', 'WORK_MANAGER', 'EMPLOYEE', 'NO_ACCESS')
  );

COMMIT;