1-schema.sql 2.5 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
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
) ;
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,
                           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);