-drop sequence SEQ_GEN_SEQUENCE;
-create sequence SEQ_GEN_SEQUENCE INCREMENT BY 50;
+/* Unique Ids sequence generator */
-DROP TABLE attribute_group CASCADE;
+drop sequence if exists SEQ_GEN_SEQUENCE;
+create sequence SEQ_GEN_SEQUENCE START WITH 1000 INCREMENT BY 50;
+
+drop table if exists study_files cascade;
+
+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;
CREATE TABLE attribute_group (
- id int8 PRIMARY KEY
+ id bigint NOT NULL PRIMARY KEY
);
-DROP TABLE attribute CASCADE;
+DROP TABLE IF EXISTS attribute CASCADE;
CREATE TABLE attribute (
- id int8 PRIMARY KEY,
+ id bigint NOT NULL PRIMARY KEY,
name varchar(255),
type varchar(255),
value varchar(255),
- group_id int8 REFERENCES attribute_group (id),
+ attribute_group_id bigint not null REFERENCES attribute_group (id) on delete cascade on update cascade,
mandatory boolean
);
-DROP TABLE file CASCADE;
-CREATE TABLE file (
- id int8 PRIMARY KEY,
+/* 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) on delete cascade on update cascade
+);
+
+/* DATA */
+DROP TABLE IF EXISTS gde_file CASCADE;
+CREATE TABLE gde_file (
+ id bigint NOT NULL PRIMARY KEY,
name varchar(255),
- length int8,
+ length bigint,
checksum varchar(255),
creation_date timestamp,
update_date timestamp,
valid boolean,
deleted boolean,
deletion_date timestamp,
- group_id int8 REFERENCES attribute_group (id)
+ attribute_group_id bigint REFERENCES attribute_group (id),
+ data_profile_id bigint REFERENCES profile (id)
);
-DROP TABLE chunk CASCADE;
+DROP TABLE IF EXISTS chunk CASCADE;
CREATE TABLE chunk (
- id int8 PRIMARY KEY,
- file_id int8 REFERENCES file (id),
- rank int8,
+ id bigint NOT NULL PRIMARY KEY,
+ file_id bigint NOT NULL REFERENCES gde_file (id),
+ rank bigint,
checksum varchar(255),
- size int8,
+ size bigint,
data bytea
);
-/*
-DROP TABLE data CASCADE;
-CREATE TABLE data (
- file_id int8 REFERENCES file (id),
- metadata_id int8 REFERENCES attribute_group (id),
- PRIMARY KEY (file_id, metadata_id)
-);
-*/
-DROP TABLE profile CASCADE;
-CREATE TABLE profile (
- id int8 PRIMARY KEY,
- name varchar(255)
-);
-DROP TABLE profile_attribute CASCADE;
-CREATE TABLE profile_attribute (
- id int8 PRIMARY KEY,
+/* STUDIES */
+DROP TABLE IF EXISTS study CASCADE;
+CREATE TABLE study (
+ id bigint NOT NULL PRIMARY KEY,
name varchar(255),
- type varchar(255),
- mandatory boolean,
- profile_id int8 REFERENCES profile (id)
+ creation_date timestamp,
+ update_date timestamp,
+ valid boolean,
+ deleted boolean,
+ deletion_date timestamp,
+ attribute_group_id bigint REFERENCES attribute_group (id),
+ profile_id bigint REFERENCES profile (id),
+ locked boolean,
+ lock_owner bigint references users(id)
);
+/* PROFILES */
+
+
+-- 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);
+/* User management permissions */
+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
+/* Studies permissions */
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (11, 1, 'StudyService',1); -- Create study
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (12, 1, 'StudyService',2); -- Set study state
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (13, 1, 'StudyService',3); -- Read study
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (14, 1, 'StudyService',4); -- Delete study
+/* Profiles services */
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (15, 1, 'ProfilesService',1); -- Create profile
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (16, 1, 'ProfilesService',2); -- Delete profile
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (17, 1, 'ProfilesService',3); -- Read profile
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (18, 1, 'ProfilesService',4); -- Update profile
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (19, 1, 'ProfilesService',5); -- Create profile attribute
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (20, 1, 'ProfilesService',6); -- Delete profile attribute
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (21, 1, 'ProfilesService',7); -- Read profile attribute
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (22, 1, 'ProfilesService',8); -- Update profile attribute
+/* Attributes services */
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (23, 1, 'AttributesService',1); -- Create attribute
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (24, 1, 'AttributesService',2); -- Delete attribute
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (25, 1, 'AttributesService',3); -- Read attribute
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (26, 1, 'AttributesService',4); -- Create attribute group
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (27, 1, 'AttributesService',5); -- Delete attribute group
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (28, 1, 'AttributesService',6); -- Update attribute group
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (29, 1, 'AttributesService',7); -- Read attribute group
+/* File services */
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (30, 1, 'FileService',1); --
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (31, 1, 'FileService',2); --
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (32, 1, 'FileService',3); --
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (33, 1, 'FileService',4); --
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (34, 1, 'FileService',5); --
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (35, 1, 'FileService',6); --
+INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (36, 1, 'FileService',7); --
