schema_postgresql.sql

-- -----------------------------------------------------
-- 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);