Work on Profile

[modules/gde.git] / projects / GDE_App / src / GDE_DB_Init.sql

diff --git a/projects/GDE_App/src/GDE_DB_Init.sql b/projects/GDE_App/src/GDE_DB_Init.sql
index 19c986d8fe426e6447e084d4eb9284192bc131ee..870b5f2febb595da45374c1f30fe115f59affa8d 100644 (file)
--- a/projects/GDE_App/src/GDE_DB_Init.sql
+++ b/projects/GDE_App/src/GDE_DB_Init.sql
@@ -1,7 +1,7 @@
 /* Unique Ids sequence generator */
 
-drop sequence SEQ_GEN_SEQUENCE;
-create sequence SEQ_GEN_SEQUENCE START WITH 50 INCREMENT BY 50;
+drop sequence if exists SEQ_GEN_SEQUENCE;
+create sequence SEQ_GEN_SEQUENCE START WITH 1000 INCREMENT BY 50;
 
 
 DROP TABLE IF EXISTS GROUP_ CASCADE;
@@ -26,12 +26,14 @@ CREATE TABLE USERGROUP (
 );
 create index usergroup_groupId_idx ON USERGROUP(groupId);
 
+
+
 DROP TABLE IF EXISTS GROUPPERMISSIONS CASCADE;
 CREATE TABLE GROUPPERMISSIONS (
     id bigint NOT NULL PRIMARY KEY,
     groupId bigint references GROUP_(id),
     serviceName varchar(255) not null,
-    methodName varchar(255) not null
+    methodIndex int not null
 );
 
 /* METADATA */
@@ -47,10 +49,27 @@ CREATE TABLE attribute (
     name varchar(255),
     type varchar(255),
     value varchar(255),
-    attribute_group_id bigint REFERENCES attribute_group (id),
+    attribute_group_id bigint not null REFERENCES attribute_group (id),
     mandatory boolean
 );
 
+/* PROFILE */
+
+DROP TABLE IF EXISTS profile CASCADE;
+CREATE TABLE profile (
+    id bigint NOT NULL PRIMARY KEY,
+    name varchar(255)
+);
+
+DROP TABLE IF EXISTS profile_attribute CASCADE;
+CREATE TABLE profile_attribute (
+    id bigint NOT NULL PRIMARY KEY,
+    name varchar(255),
+    type varchar(255),
+    mandatory boolean,
+    profile_id bigint not null REFERENCES profile (id)
+);
+
 /* DATA */
 
 DROP TABLE IF EXISTS gde_file CASCADE;
@@ -90,22 +109,23 @@ CREATE TABLE study (
     deleted boolean,
     deletion_date timestamp,
     attribute_group_id bigint REFERENCES attribute_group (id),
-    profile_id bigint REFERENCES profile (id)
+    profile_id bigint REFERENCES profile (id),
+    locked boolean
 );
-
 /* PROFILES */
 
-DROP TABLE IF EXISTS profile CASCADE;
-CREATE TABLE profile (
-    id bigint NOT NULL PRIMARY KEY,
-    name varchar(255)
-);
 
-DROP TABLE IF EXISTS profile_attribute CASCADE;
-CREATE TABLE profile_attribute (
-    id bigint NOT NULL PRIMARY KEY,
-    name varchar(255),
-    type varchar(255),
-    mandatory boolean,
-    profile_id bigint REFERENCES profile (id)
-);
+-- The 1000 first ids are reserved for initial data
+INSERT INTO users (id,username,userpassword) VALUES (1,'admin','edf123');
+INSERT INTO group_ (id,groupname) VALUES (1,'admins');
+INSERT into usergroup(id,groupid,userid) VALUES (2,1,1);
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (3, 1, 'UserService',1); -- Create user 
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (4, 1, 'UserService',2); -- Delete user
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (5, 1, 'UserService',3); -- Add to group
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (6, 1, 'UserService',4); -- Remove from group
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (7, 1, 'UserService',5); -- Create group
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (8, 1, 'UserService',6); -- Delete group
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (9, 1, 'UserService',7); -- Find user
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (10, 1, 'UserService',8); -- Find group
+
+