schema_postgresql.sql 3.15 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
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);

11
alter table "user" add PASSWORD_RESET_TOKEN varchar(256) default null;
12
13
14

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

15
16
17
18
19
20
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,
21
  accessible_exterieur SMALLINT NOT NULL DEFAULT 1,
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
65
66
67
68
  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);


-- Données
69

70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
INSERT INTO user_role (id, role_id, is_default, parent_id) VALUES
(1, 'Standard', 1, NULL),
(2, 'Gestionnaire', 0, 1),
(3, 'Super-gestionnaire', 0, 2),
(4, 'Administrateur', 0, 3);

INSERT INTO categorie_privilege (id, code, libelle, ordre) VALUES
(1, 'droit', 'Gestion des droits', 1);

INSERT INTO privilege (id, categorie_id, code, libelle, ordre) VALUES
(1, 1, 'role-visualisation', 'Rôles - Visualisation', 1),
(2, 1, 'role-edition', 'Rôles - Édition', 2),
(3, 1, 'privilege-visualisation', 'Privilèges - Visualisation', 3),
(4, 1, 'privilege-edition', 'Privilèges - Édition', 4);

INSERT INTO role_privilege (role_id, privilege_id) VALUES
(4, 1),
(4, 2),
(4, 3),
89
90
91
92
93
94
95
(4, 4);

INSERT INTO "user" (username, email, display_name, password, state) VALUES
  -- utilisateur demo/azerty
  ('demo', 'demo@mail.fr', 'Demo', '$2y$10$PxXnVLYnGEzEnfqPqRKJSe9AabocES2H4bBK5VzzJlzuj1rVt7Lwu', 1);

INSERT INTO user_role_linker(user_id, role_id)
96
SELECT u.id, r.id FROM "user" u, user_role r WHERE u.username = 'demo' and r.role_id = 'Standard';