schema_oracle.sql

-- -----------------------------------------------------
-- TABLE UNICAEN_PRIVILEGE_CATEGORIE
-- -----------------------------------------------------
CREATE SEQUENCE UNICAEN_PRIVILEGE_CATEGORIE_ID_SEQ NOCYCLE START WITH 1 INCREMENT BY 1 MINVALUE 0;

CREATE TABLE UNICAEN_PRIVILEGE_CATEGORIE (
  ID        NUMBER(*,0)         NOT NULL,
  CODE      VARCHAR2(150 CHAR)  NOT NULL,
  LIBELLE   VARCHAR2(200 CHAR)  NOT NULL,
  NAMESPACE VARCHAR(255),
  ORDRE     INT                 DEFAULT 0,
  CONSTRAINT PK_UNICAEN_PRIVILEGE_CATEGORIE PRIMARY KEY (ID),
  CONSTRAINT UN_UNICAEN_PRIVILEGE_CATEGORIE_CODE UNIQUE (CODE)
);


-- -----------------------------------------------------
-- TABLE UNICAEN_PRIVILEGE
-- -----------------------------------------------------
CREATE SEQUENCE UNICAEN_PRIVILEGE_ID_SEQ NOCYCLE START WITH 1 INCREMENT BY 1 MINVALUE 0;

CREATE TABLE UNICAEN_PRIVILEGE (
  ID           NUMBER(*, 0)       NOT NULL,
  CATEGORIE_ID NUMBER(*, 0)       NOT NULL,
  CODE         VARCHAR2(150 CHAR) NOT NULL,
  LIBELLE      VARCHAR2(200 CHAR) NOT NULL,
  ORDRE        INT                DEFAULT 0,
  CONSTRAINT PK_UNICAEN_PRIVILEGE PRIMARY KEY (ID),
  CONSTRAINT UN_UNICAEN_PRIVILEGE_CODE UNIQUE (CATEGORIE_ID, CODE),
  CONSTRAINT FK_UNICAEN_PRIVILEGE_CATEGORIE FOREIGN KEY (CATEGORIE_ID) REFERENCES UNICAEN_PRIVILEGE_CATEGORIE (ID) DEFERRABLE INITIALLY IMMEDIATE
);

CREATE INDEX IX_UNICAEN_PRIVILEGE_CATEGORIE ON UNICAEN_PRIVILEGE(CATEGORIE_ID);


-- -----------------------------------------------------
-- TABLE UNICAEN_ROLE_PRIVILEGE_LINKER
-- -----------------------------------------------------
CREATE TABLE UNICAEN_ROLE_PRIVILEGE_LINKER (
  ROLE_ID      NUMBER(*, 0) NOT NULL,
  PRIVILEGE_ID NUMBER(*, 0) NOT NULL,
  CONSTRAINT PK_UNICAEN_ROLE_PRIVILEGE_LINKER PRIMARY KEY (ROLE_ID, PRIVILEGE_ID),
  CONSTRAINT FK_UNICAEN_ROLE_PRIVILEGE_LINKER_ROLE FOREIGN KEY (ROLE_ID) REFERENCES UNICAEN_ROLE (ID) DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT FK_UNICAEN_ROLE_PRIVILEGE_LINKER_PRIVILEGE FOREIGN KEY (PRIVILEGE_ID) REFERENCES UNICAEN_PRIVILEGE (ID) DEFERRABLE INITIALLY IMMEDIATE
);

CREATE INDEX IX_UNICAEN_ROLE_PRIVILEGE_LINKER_ROLE ON UNICAEN_ROLE_PRIVILEGE_LINKER (ROLE_ID);
CREATE INDEX IX_UNICAEN_ROLE_PRIVILEGE_LINKER_PRIVILEGE ON UNICAEN_ROLE_PRIVILEGE_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 (
  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_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 (
  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_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 (
  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_ID_SEQ.nextval,
  tmp.ca,
  UNICAEN_PRIVILEGE_CATEGORIE_ID_SEQ.currval,
  tmp.l,
  tmp.o
FROM tmp;