Attribute DAO

[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 25d0435d9c65ed8f14990ee19e3587b0bae7685f..369f5a9e35d165c78ee8adbfa49e96a7791874dd 100644 (file)
--- a/projects/GDE_App/src/GDE_DB_Init.sql
+++ b/projects/GDE_App/src/GDE_DB_Init.sql
@@ -1,8 +1,39 @@
+/* Unique Ids sequence generator */
 
 drop sequence SEQ_GEN_SEQUENCE;
-create sequence SEQ_GEN_SEQUENCE INCREMENT BY 50;
+create sequence SEQ_GEN_SEQUENCE START WITH 1000 INCREMENT BY 50;
+
+
+DROP TABLE IF EXISTS GROUP_ CASCADE;
+CREATE TABLE GROUP_ (
+    id bigint NOT NULL PRIMARY KEY,
+    groupName varchar(255) NOT NULL
+);
+
+DROP TABLE IF EXISTS USERS CASCADE;
+CREATE TABLE USERS (
+    id bigint NOT NULL PRIMARY KEY,
+    userName varchar(255) not null,
+    userpassword varchar(255) not null
+);
+CREATE INDEX users_username_index ON USERS(userName);
+
+DROP TABLE IF EXISTS USERGROUP CASCADE;
+CREATE TABLE USERGROUP (
+    id bigint NOT NULL PRIMARY KEY,
+    groupId bigint references GROUP_(id) on delete cascade,
+    userId bigint references USERS(id)  on delete cascade
+);
+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,
+    methodIndex int not null
+);
 
-    
 /* METADATA */
 
 DROP TABLE IF EXISTS attribute_group CASCADE;
@@ -20,6 +51,13 @@ CREATE TABLE attribute (
     mandatory boolean
 );
 
+DROP TABLE IF EXISTS attribute_group_attribute CASCADE;
+CREATE TABLE attribute_group_attribute (
+    attributegroup_id bigint REFERENCES attribute_group (id),
+    attribute_id bigint REFERENCES attribute(id)
+);
+CREATE INDEX attribute_group_attribute_idx1 on attribute_group_attribute(attributegroup_id);
+CREATE INDEX attribute_group_attribute_idx2 on attribute_group_attribute(attribute_id);
 
 /* DATA */
 
@@ -34,7 +72,8 @@ CREATE TABLE gde_file (
     valid boolean,
     deleted boolean,
     deletion_date timestamp,
-    attribute_group_id bigint REFERENCES attribute_group (id)
+    attribute_group_id bigint REFERENCES attribute_group (id),
+    data_profile_id bigint REFERENCES profile (id)
 );
 
 DROP TABLE IF EXISTS chunk CASCADE;
@@ -58,9 +97,10 @@ CREATE TABLE study (
     valid boolean,
     deleted boolean,
     deletion_date timestamp,
-    attribute_group_id bigint REFERENCES attribute_group (id)
+    attribute_group_id bigint REFERENCES attribute_group (id),
+    profile_id bigint REFERENCES profile (id),
+    locked boolean
 );
-
 /* PROFILES */
 
 DROP TABLE IF EXISTS profile CASCADE;
@@ -77,3 +117,18 @@ CREATE TABLE profile_attribute (
     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
+
+