schema_postgresql.sql

-- -----------------------------------------------------
-- TABLE UNICAEN_PRIVILEGE_CATEGORIE
-- -----------------------------------------------------
CREATE TABLE 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 UN_UNICAEN_PRIVILEGE_CATEGORIE_CODE ON UNICAEN_PRIVILEGE_CATEGORIE (CODE);

-- -----------------------------------------------------
-- TABLE UNICAEN_PRIVILEGE_PRIVILEGE
-- -----------------------------------------------------
CREATE TABLE UNICAEN_PRIVILEGE_PRIVILEGE (
  ID           SERIAL       PRIMARY KEY,
  CATEGORIE_ID INTEGER      NOT NULL,
  CODE         VARCHAR(150) NOT NULL,
  LIBELLE      VARCHAR(200) NOT NULL,
  ORDRE        INTEGER      NOT NULL,
  CONSTRAINT FK_UNICAEN_PRIVILEGE_CATEGORIE FOREIGN KEY (CATEGORIE_ID) REFERENCES UNICAEN_PRIVILEGE_CATEGORIE (ID) DEFERRABLE INITIALLY IMMEDIATE
);

CREATE UNIQUE INDEX UN_UNICAEN_PRIVILEGE_CODE ON UNICAEN_PRIVILEGE_PRIVILEGE(CODE);
CREATE INDEX IX_UNICAEN_PRIVILEGE_CATEGORIE ON UNICAEN_PRIVILEGE_PRIVILEGE(CATEGORIE_ID);


-- -----------------------------------------------------
-- TABLE UNICAEN_PRIVILEGE_PRIVILEGE_ROLE_LINKER
-- -----------------------------------------------------
CREATE TABLE 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 IX_UNICAEN_PRIVILEGE_PRIVILEGE_ROLE_LINKER_ROLE ON UNICAEN_PRIVILEGE_PRIVILEGE_ROLE_LINKER (ROLE_ID);
CREATE INDEX IX_UNICAEN_PRIVILEGE_PRIVILEGE_ROLE_LINKER_PRIVILEGE ON UNICAEN_PRIVILEGE_PRIVILEGE_ROLE_LINKER (PRIVILEGE_ID);


-- DATA

INSERT INTO UNICAEN_PRIVILEGE_CATEGORIE (
  ID,
  CODE,
  LIBELLE,
  ORDRE)
WITH tmp AS (
  SELECT  1, 'utilisateur'  c, 'Gestion des utilisateurs' l, 1  o FROM dual
)
SELECT
  UNICAEN_PRIVILEGE_CATEGORIE_ID_SEQ.nextval,
  tmp.c,
  tmp.l,
  tmp.o
FROM tmp;

INSERT INTO UNICAEN_PRIVILEGE_PRIVILEGE (
  ID,
  CATEGORIE_ID,
  CODE,
  LIBELLE,
  ORDRE)
WITH tmp AS (
  SELECT 1, 1 ca, 'utilisateur_afficher'      c, 'Consulter un utilisateur'                       l, 1  o FROM dual UNION
  SELECT 2, 1 ca, 'utilisateur_ajouter'       c, 'Ajouter un utilisateur'                         l, 2  o FROM dual UNION
  SELECT 3, 1 ca, 'utilisateur_changerstatus' c, 'Changer le statut d''un utilisateur'            l, 3  o FROM dual UNION
  SELECT 4, 1 ca, 'utilisateur_modifierrole'  c, 'Modifier les rôles attribués à un utilisateur'  l, 4  o FROM dual
)
SELECT
  UNICAEN_PRIVILEGE_PRIVILEGE_ID_SEQ.nextval,
  tmp.ca,
  UNICAEN_PRIVILEGE_CATEGORIE_ID_SEQ.currval,
  tmp.l,
  tmp.o
FROM tmp;

INSERT INTO UNICAEN_PRIVILEGE_CATEGORIE (
  ID,
  CODE,
  LIBELLE,
  ORDRE)
WITH tmp AS (
  SELECT  1, 'role'  c, 'Gestion des rôles' l, 1  o FROM dual
)
SELECT
  UNICAEN_PRIVILEGE_CATEGORIE_ID_SEQ.nextval,
  tmp.c,
  tmp.l,
  tmp.o
FROM tmp;

INSERT INTO UNICAEN_PRIVILEGE_PRIVILEGE (
  ID,
  CATEGORIE_ID,
  CODE,
  LIBELLE,
  ORDRE)
WITH tmp AS (
  SELECT 1, 1 ca, 'role_afficher' c, 'Consulter les rôles'  l, 1  o FROM dual UNION
  SELECT 2, 1 ca, 'role_modifier' c, 'Modifier un rôle'     l, 2  o FROM dual UNION
  SELECT 3, 1 ca, 'role_effacer'  c, 'Supprimer un rôle'    l, 3  o FROM dual
)
SELECT
  UNICAEN_PRIVILEGE_PRIVILEGE_ID_SEQ.nextval,
  tmp.ca,
  UNICAEN_PRIVILEGE_CATEGORIE_ID_SEQ.currval,
  tmp.l,
  tmp.o
FROM tmp;

INSERT INTO UNICAEN_PRIVILEGE_CATEGORIE (
  ID,
  CODE,
  LIBELLE,
  ORDRE)
WITH tmp AS (
  SELECT  1, 'privilege'  c, 'Gestion des privilèges' l, 1  o FROM dual
)
SELECT
  UNICAEN_PRIVILEGE_CATEGORIE_ID_SEQ.nextval,
  tmp.c,
  tmp.l,
  tmp.o
FROM tmp;

INSERT INTO UNICAEN_PRIVILEGE_PRIVILEGE (
  ID,
  CATEGORIE_ID,
  CODE,
  LIBELLE,
  ORDRE)
WITH tmp AS (
  SELECT 1, 1 ca, 'privilege_voir'      c, 'Afficher les privilèges'  l, 1  o FROM dual UNION
  SELECT 2, 1 ca, 'privilege_ajouter'   c, 'Ajouter un privilège'     l, 2  o FROM dual UNION
  SELECT 3, 1 ca, 'privilege_modifier'  c, 'Modifier un privilège'    l, 3  o FROM dual UNION
  SELECT 4, 1 ca, 'privilege_supprimer' c, 'Supprimer un privilège'   l, 4  o FROM dual UNION
  SELECT 5, 1 ca, 'privilege_affecter'  c, 'Attribuer un privilège'   l, 5  o FROM dual
)
SELECT
  UNICAEN_PRIVILEGE_PRIVILEGE_ID_SEQ.nextval,
  tmp.ca,
  UNICAEN_PRIVILEGE_CATEGORIE_ID_SEQ.currval,
  tmp.l,
  tmp.o
FROM tmp;