schema_oracle.sql 4.74 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
	 "ROLE_ID" VARCHAR2(64 CHAR) NOT NULL ENABLE,
21
	 "IS_DEFAULT" NUMBER(1) NOT NULL ENABLE,
22 23
	 "PARENT_ID" NUMBER(*,0),
	 "LDAP_FILTER" VARCHAR2(255) DEFAULT NULL,
24
	 "ACCESSIBLE_EXTERIEUR" NUMBER(1) DEFAULT 1 NOT NULL ENABLE,
25 26 27
	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
28
);
29 30 31
CREATE INDEX "USER_ROLE_PARENT__IDX" ON USER_ROLE ("PARENT_ID");
CREATE SEQUENCE USER_ROLE_ID_SEQ ;

32

33
CREATE TABLE USER_ROLE_LINKER
34
(	"USER_ID" NUMBER(*,0) NOT NULL ENABLE,
35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56
	 "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);

57 58 59 60 61
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)
62
SELECT u.id, r.id FROM "USER" u, user_role r WHERE u.username = 'demo' and r.role_id = 'Standard';
63

64 65 66 67 68 69 70 71 72 73 74


/**
 * 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
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 107 108 109 110 111 112 113 114
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);