schema.sqlite.sql 3.83 KB
Newer Older
1
2
3
4
5
6
7
8
9
CREATE TABLE IF NOT EXISTS user
(
    id           INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    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
);
10
ALTER TABLE user ADD PASSWORD_RESET_TOKEN varchar(256) DEFAULT NULL;
11
12
13
14
15
16
17
18
19
20
CREATE UNIQUE INDEX user_unique_username ON user(username);
CREATE UNIQUE INDEX USER_PASSWORD_RESET_TOKEN_UN ON user (PASSWORD_RESET_TOKEN);


CREATE TABLE IF NOT EXISTS user_role (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    role_id VARCHAR(64) NOT NULL,
    is_default TINYINT(1) NOT NULL DEFAULT 0,
    parent_id INTEGER NULL DEFAULT NULL,
    ldap_filter varchar(255) DEFAULT NULL,
21
    accessible_exterieur TINYINT(1) 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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

    FOREIGN KEY (parent_id) REFERENCES user_role (id) ON DELETE SET NULL
);
CREATE UNIQUE INDEX role_unique_role_id ON user_role(role_id);
CREATE INDEX role_idx_parent_id ON user_role(parent_id);


CREATE TABLE IF NOT EXISTS user_role_linker (
    user_id INTEGER NOT NULL,
    role_id INTEGER NOT NULL,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (role_id) REFERENCES user_role (id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE
);
CREATE INDEX user_role_linker_idx_role_id ON user_role_linker(role_id);
CREATE INDEX user_role_linker_idx_user_id ON user_role_linker(user_id);


CREATE TABLE IF NOT EXISTS categorie_privilege (
    id            INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    code          VARCHAR(150) NOT NULL,
    libelle       VARCHAR(200) NOT NULL,
    ordre         INTEGER
);
CREATE UNIQUE INDEX categorie_unique_code ON categorie_privilege(code);


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


CREATE TABLE IF NOT EXISTS role_privilege (
    role_id       INTEGER NOT NULL,
    privilege_id  INTEGER 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 INDEX idx_role_id on role_privilege(role_id);
CREATE INDEX idx_privilege_id on role_privilege(privilege_id);


-- Données

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),
    (4, 4);
93

94
95
96
INSERT INTO user (username, email, display_name, password, state) VALUES
    -- utilisateur admin/azerty
    ('admin', 'admin@mail.fr', 'Administrateur', '$2y$10$QPbyqusyGOOuLlnRCmvZpuKDlAKorxtJJsjeW78bXnIKCFEE.bQiK', 1);
97
98
99
100
101
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)
102
SELECT u.id, r.id FROM user u, user_role r WHERE u.username = 'demo' and r.role_id = 'Standard';