schema_postgresql.sql

CREATE TABLE "user" (
  id BIGSERIAL PRIMARY KEY,
  username VARCHAR(255) DEFAULT NULL,
  email VARCHAR(255) DEFAULT NULL,
  display_name VARCHAR(64) DEFAULT NULL,
  password VARCHAR(128) NOT NULL,
  state SMALLINT default 1,
  last_role_id SMALLINT,
  created_on DATE default current_timestamp not null,
  FOREIGN KEY (last_role_id) REFERENCES user_role (id) ON DELETE SET NULL
) ;
CREATE UNIQUE INDEX user_username_unique ON "user" (username);

alter table "user" add PASSWORD_RESET_TOKEN varchar(256) default null;

create unique index USER_PASSWORD_RESET_TOKEN_UN on "user" (PASSWORD_RESET_TOKEN);

CREATE TABLE user_role (
  id BIGSERIAL PRIMARY KEY,
  role_id VARCHAR(64) NOT NULL,
  is_default SMALLINT NOT NULL DEFAULT 0,
  parent_id BIGINT DEFAULT NULL,
  ldap_filter varchar(255) DEFAULT NULL,
  accessible_exterieur BOOLEAN NOT NULL DEFAULT true,
  FOREIGN KEY (parent_id) REFERENCES user_role (id) ON DELETE SET NULL
);
CREATE UNIQUE INDEX user_role_roleid_unique ON user_role (role_id);


CREATE TABLE user_role_linker (
  user_id BIGINT NOT NULL,
  role_id BIGINT NOT NULL,
  PRIMARY KEY (user_id, role_id),
  FOREIGN KEY (user_id) REFERENCES "user" (id) ON DELETE CASCADE,
  FOREIGN KEY (role_id) REFERENCES user_role (id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX user_role_linker_unique ON user_role_linker (user_id, role_id);




CREATE TABLE  IF NOT EXISTS categorie_privilege (
  id      BIGSERIAL PRIMARY KEY,
  code    VARCHAR(150) NOT NULL,
  libelle VARCHAR(200) NOT NULL,
  ordre   INT
);
CREATE UNIQUE INDEX categorie_privilege_unique ON categorie_privilege (code);


CREATE TABLE IF NOT EXISTS privilege (
  id            BIGSERIAL PRIMARY KEY,
  categorie_id  BIGINT NOT NULL,
  code          VARCHAR(150) NOT NULL,
  libelle       VARCHAR(200) NOT NULL,
  ordre         INT,
  FOREIGN KEY (categorie_id) REFERENCES categorie_privilege (id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX privilege_unique ON privilege (code);

CREATE TABLE IF NOT EXISTS role_privilege (
  role_id       BIGINT NOT NULL,
  privilege_id  BIGINT NOT NULL,
  PRIMARY KEY (role_id, privilege_id),
  FOREIGN KEY (role_id) REFERENCES user_role (id) ON DELETE CASCADE,
  FOREIGN KEY (privilege_id) REFERENCES privilege (id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX role_privilege_unique ON role_privilege (role_id, privilege_id);