Select Git revision
schema_postgresql.sql
Florian Joriot authored
Code owners
Assign users and groups as approvers for specific file changes. Learn more.
schema_postgresql.sql 5.58 KiB
-- -----------------------------------------------------
-- TABLE UNICAEN_PRIVILEGE_CATEGORIE
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS UNICAEN_PRIVILEGE_CATEGORIE (
ID SERIAL PRIMARY KEY,
CODE VARCHAR(150) NOT NULL,
LIBELLE VARCHAR(200) NOT NULL,
NAMESPACE VARCHAR(255),
ORDRE INTEGER DEFAULT 0
);
CREATE UNIQUE INDEX IF NOT EXISTS UN_UNICAEN_PRIVILEGE_CATEGORIE_CODE ON UNICAEN_PRIVILEGE_CATEGORIE (CODE);
-- -----------------------------------------------------
-- TABLE UNICAEN_PRIVILEGE
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS UNICAEN_PRIVILEGE_PRIVILEGE (
ID SERIAL PRIMARY KEY,
CATEGORIE_ID INTEGER NOT NULL,
CODE VARCHAR(150) NOT NULL,
LIBELLE VARCHAR(200) NOT NULL,
ORDRE INTEGER DEFAULT 0,
CONSTRAINT FK_UNICAEN_PRIVILEGE_CATEGORIE FOREIGN KEY (CATEGORIE_ID) REFERENCES UNICAEN_PRIVILEGE_CATEGORIE (ID) DEFERRABLE INITIALLY IMMEDIATE
);
CREATE UNIQUE INDEX IF NOT EXISTS UN_UNICAEN_PRIVILEGE_CODE ON UNICAEN_PRIVILEGE_PRIVILEGE (CATEGORIE_ID, CODE);
CREATE INDEX IF NOT EXISTS IX_UNICAEN_PRIVILEGE_CATEGORIE ON UNICAEN_PRIVILEGE_PRIVILEGE(CATEGORIE_ID);
-- -----------------------------------------------------
-- TABLE UNICAEN_ROLE_PRIVILEGE_LINKER
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS UNICAEN_PRIVILEGE_PRIVILEGE_ROLE_LINKER (
ROLE_ID INTEGER NOT NULL,
PRIVILEGE_ID INTEGER NOT NULL,
CONSTRAINT PK_UNICAEN_PRIVILEGE_PRIVILEGE_ROLE_LINKER PRIMARY KEY (ROLE_ID, PRIVILEGE_ID),
CONSTRAINT FK_UNICAEN_PRIVILEGE_PRIVILEGE_ROLE_LINKER_ROLE FOREIGN KEY (ROLE_ID) REFERENCES UNICAEN_UTILISATEUR_ROLE (ID) DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT FK_UNICAEN_PRIVILEGE_PRIVILEGE_ROLE_LINKER_PRIVILEGE FOREIGN KEY (PRIVILEGE_ID) REFERENCES UNICAEN_PRIVILEGE_PRIVILEGE (ID) DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX IF NOT EXISTS IX_UNICAEN_PRIVILEGE_PRIVILEGE_ROLE_LINKER_ROLE ON UNICAEN_PRIVILEGE_PRIVILEGE_ROLE_LINKER (ROLE_ID);
CREATE INDEX IF NOT EXISTS IX_UNICAEN_PRIVILEGE_PRIVILEGE_ROLE_LINKER_PRIVILEGE ON UNICAEN_PRIVILEGE_PRIVILEGE_ROLE_LINKER (PRIVILEGE_ID);
-- DATA
INSERT INTO UNICAEN_PRIVILEGE_CATEGORIE (
CODE,
LIBELLE,
NAMESPACE,
ORDRE)
values
('utilisateur', 'Gestion des utilisateurs', 'UnicaenUtilisateur\Provider\Privilege', 1),
('role', 'Gestion des rôles', 'UnicaenUtilisateur\Provider\Privilege', 2),
('privilege', 'Gestion des privilèges', 'UnicaenPrivilege\Provider\Privilege', 3)
ON CONFLICT (CODE) DO
UPDATE SET
LIBELLE=excluded.LIBELLE,
NAMESPACE=excluded.NAMESPACE,
ORDRE=excluded.ORDRE;
WITH d(code, lib, ordre) AS (
SELECT 'utilisateur_afficher', 'Consulter un utilisateur', 1 UNION
SELECT 'utilisateur_ajouter', 'Ajouter un utilisateur', 2 UNION
SELECT 'utilisateur_changerstatus', 'Changer le statut d''un utilisateur', 3 UNION
SELECT 'utilisateur_modifierrole', 'Modifier les rôles attribués à un utilisateur', 4
)
INSERT INTO unicaen_privilege_privilege(CATEGORIE_ID, CODE, LIBELLE, ORDRE)
SELECT cp.id, d.code, d.lib, d.ordre
FROM d
JOIN unicaen_privilege_categorie cp ON cp.CODE = 'utilisateur'
ON CONFLICT (CATEGORIE_ID, CODE) DO
UPDATE SET
LIBELLE=excluded.LIBELLE,
ORDRE=excluded.ORDRE;
WITH d(code, lib, ordre) AS (
SELECT 'role_afficher', 'Consulter les rôles', 1 UNION
SELECT 'role_modifier', 'Modifier un rôle', 2 UNION
SELECT 'role_effacer', 'Supprimer un rôle', 3
)
INSERT INTO unicaen_privilege_privilege(CATEGORIE_ID, CODE, LIBELLE, ORDRE)
SELECT cp.id, d.code, d.lib, d.ordre
FROM d
JOIN unicaen_privilege_categorie cp ON cp.CODE = 'role'
ON CONFLICT (CATEGORIE_ID, CODE) DO
UPDATE SET
LIBELLE=excluded.LIBELLE,
ORDRE=excluded.ORDRE;
WITH d(code, lib, ordre) AS (
SELECT 'privilege_voir', 'Afficher les privilèges', 1 UNION
SELECT 'privilege_ajouter' , 'Ajouter un privilège', 2 UNION
SELECT 'privilege_modifier' , 'Modifier un privilège', 3 UNION
SELECT 'privilege_supprimer' , 'Supprimer un privilège', 4 UNION
SELECT 'privilege_affecter' , 'Attribuer un privilège', 5
)
INSERT INTO unicaen_privilege_privilege(CATEGORIE_ID, CODE, LIBELLE, ORDRE)
SELECT cp.id, d.code, d.lib, d.ordre
FROM d
JOIN unicaen_privilege_categorie cp ON cp.CODE = 'privilege'
ON CONFLICT (CATEGORIE_ID, CODE) DO
UPDATE SET
LIBELLE=excluded.LIBELLE,
ORDRE=excluded.ORDRE;
INSERT INTO unicaen_privilege_privilege_role_linker
(role_id, privilege_id)
SELECT role.id, privilege.id
FROM
unicaen_utilisateur_role role,
unicaen_privilege_privilege privilege
JOIN unicaen_privilege_categorie cp ON privilege.categorie_id = cp.id
WHERE role.role_id = 'Admin_tech'
AND cp.code IN('utilisateur', 'role', 'privilege')
ON conflict do nothing;