schema_oracle.sql 4.33 KB
Newer Older
1
CREATE TABLE "USER"
2
(	"ID" NUMBER(*,0) NOT NULL ENABLE,
3
4
5
6
7
	 "USERNAME" VARCHAR2(255 CHAR),
	 "EMAIL" VARCHAR2(255 CHAR),
	 "DISPLAY_NAME" VARCHAR2(64 CHAR),
	 "PASSWORD" VARCHAR2(128 CHAR) NOT NULL ENABLE,
	 "STATE" SMALLINT DEFAULT 1 NOT NULL ENABLE,
8
9
10
	CONSTRAINT "USER_PK" PRIMARY KEY ("ID"),
	CONSTRAINT "USER_USERNAME_UN" UNIQUE ("USERNAME")
);
11
12
CREATE SEQUENCE "USER_ID_SEQ" ;

13
14
15
16
alter table "USER" add PASSWORD_RESET_TOKEN varchar2(256) default null;

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

17

18
CREATE TABLE USER_ROLE
19
(	"ID" NUMBER(*,0) NOT NULL ENABLE,
20
21
22
23
24
25
26
	 "ROLE_ID" VARCHAR2(64 CHAR) NOT NULL ENABLE,
	 "IS_DEFAULT" NUMBER(38,0) NOT NULL ENABLE,
	 "PARENT_ID" NUMBER(*,0),
	 "LDAP_FILTER" VARCHAR2(255) DEFAULT NULL,
	CONSTRAINT "USER_ROLE_PK" PRIMARY KEY ("ID"),
	CONSTRAINT "USER_ROLE_ROLE_UN" UNIQUE ("ROLE_ID"),
	CONSTRAINT "USER_ROLE_USER_ROLE_FK" FOREIGN KEY ("PARENT_ID") REFERENCES USER_ROLE ("ID") ENABLE
27
);
28
29
30
CREATE INDEX "USER_ROLE_PARENT__IDX" ON USER_ROLE ("PARENT_ID");
CREATE SEQUENCE USER_ROLE_ID_SEQ ;

31

32
CREATE TABLE USER_ROLE_LINKER
33
(	"USER_ID" NUMBER(*,0) NOT NULL ENABLE,
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
	 "ROLE_ID" NUMBER(*,0) NOT NULL ENABLE,
	CONSTRAINT "USER_ROLE_LINKER_PK" PRIMARY KEY ("USER_ID", "ROLE_ID"),
	CONSTRAINT "USER_ROLE_LINKER_USER_FK" FOREIGN KEY ("USER_ID") REFERENCES "USER" ("ID")      ON DELETE CASCADE ENABLE,
	CONSTRAINT "USER_ROLE_LINKER_ROLE_FK" FOREIGN KEY ("ROLE_ID") REFERENCES USER_ROLE ("ID") ON DELETE CASCADE ENABLE
);
CREATE INDEX "USER_ROLE_LINKER_USER_IDX" ON USER_ROLE_LINKER ("USER_ID");
CREATE INDEX "USER_ROLE_LINKER_ROLE_IDX" ON USER_ROLE_LINKER ("ROLE_ID");

/** si une autre table utilisateur est utilisée à la place de "user", modifier la containte sur la colonne "user_id", exemple :
ALTER TABLE USER_ROLE_LINKER DROP CONSTRAINT "USER_ROLE_LINKER_USER_FK";
ALTER TABLE USER_ROLE_LINKER ADD CONSTRAINT "USER_ROLE_LINKER_USER_FK" FOREIGN KEY ("USER_ID") REFERENCES "UTILISATEUR" ("ID") ON DELETE CASCADE ENABLE;
*/

/**
 * exemples de données
 */

INSERT INTO USER_ROLE (ID, ROLE_ID, IS_DEFAULT, PARENT_ID) VALUES (USER_ROLE_ID_SEQ.NEXTVAL, 'Standard', 					1, null);
INSERT INTO USER_ROLE (ID, ROLE_ID, IS_DEFAULT, PARENT_ID) VALUES (USER_ROLE_ID_SEQ.NEXTVAL, 'Gestionnaire', 			0, 1);
INSERT INTO USER_ROLE (ID, ROLE_ID, IS_DEFAULT, PARENT_ID) VALUES (USER_ROLE_ID_SEQ.NEXTVAL, 'Super-gestionnaire', 0, 2);
INSERT INTO USER_ROLE (ID, ROLE_ID, IS_DEFAULT, PARENT_ID) VALUES (USER_ROLE_ID_SEQ.NEXTVAL, 'Administrateur', 		0, 3);



/**
 * Privilèges
 */

CREATE TABLE CATEGORIE_PRIVILEGE (
	ID      NUMBER(*,0) PRIMARY KEY,
	CODE    VARCHAR2(150 CHAR) NOT NULL,
	LIBELLE VARCHAR2(200 CHAR) NOT NULL,
	ORDRE   INT
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
93
94
95
96
97
98
99
100
101
102
103
104
105
106
CREATE UNIQUE INDEX CATEGORIE_PRIVILEGE_UNIQUE ON CATEGORIE_PRIVILEGE (CODE);
CREATE SEQUENCE "CATEGORIE_PRIVILEGE_ID_SEQ" ;


CREATE TABLE PRIVILEGE (
	ID            NUMBER(*,0) PRIMARY KEY,
	CATEGORIE_ID  NUMBER(*,0) NOT NULL,
	CODE          VARCHAR2(150 CHAR) NOT NULL,
	LIBELLE       VARCHAR2(200 CHAR) NOT NULL,
	ORDRE         INT,
	FOREIGN KEY (CATEGORIE_ID) REFERENCES CATEGORIE_PRIVILEGE (ID) ON DELETE CASCADE
);
CREATE UNIQUE INDEX PRIVILEGE_UNIQUE ON PRIVILEGE (CODE);
CREATE SEQUENCE "PRIVILEGE_ID_SEQ" ;


CREATE TABLE ROLE_PRIVILEGE (
	ROLE_ID       NUMBER(*,0) NOT NULL,
	PRIVILEGE_ID  NUMBER(*,0) 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
);

/**
 * exemples de données
 */

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);
INSERT INTO PRIVILEGE (ID, CATEGORIE_ID, CODE, LIBELLE, ORDRE) VALUES (2, 1, 'role-edition', 						'rôles - édition', 						2);
INSERT INTO PRIVILEGE (ID, CATEGORIE_ID, CODE, LIBELLE, ORDRE) VALUES (3, 1, 'privilege-visualisation', 'privilèges - visualisation', 3);
INSERT INTO PRIVILEGE (ID, CATEGORIE_ID, CODE, LIBELLE, ORDRE) VALUES (4, 1, 'privilege-edition', 			'privilèges - édition', 			4);

INSERT INTO ROLE_PRIVILEGE (ROLE_ID, PRIVILEGE_ID) VALUES (4, 1);
INSERT INTO ROLE_PRIVILEGE (ROLE_ID, PRIVILEGE_ID) VALUES (4, 2);
INSERT INTO ROLE_PRIVILEGE (ROLE_ID, PRIVILEGE_ID) VALUES (4, 3);
INSERT INTO ROLE_PRIVILEGE (ROLE_ID, PRIVILEGE_ID) VALUES (4, 4);