1 /* Unique Ids sequence generator */
3 drop sequence if exists SEQ_GEN_SEQUENCE;
4 create sequence SEQ_GEN_SEQUENCE START WITH 1000 INCREMENT BY 50;
6 drop table if exists study_files cascade;
8 DROP TABLE IF EXISTS GROUP_ CASCADE;
10 id bigint NOT NULL PRIMARY KEY,
11 groupName varchar(255) NOT NULL
14 DROP TABLE IF EXISTS USERS CASCADE;
16 id bigint NOT NULL PRIMARY KEY,
17 userName varchar(255) not null,
18 userpassword varchar(255) not null
20 CREATE INDEX users_username_index ON USERS(userName);
22 DROP TABLE IF EXISTS USERGROUP CASCADE;
23 CREATE TABLE USERGROUP (
24 id bigint NOT NULL PRIMARY KEY,
25 groupId bigint references GROUP_(id) on delete cascade,
26 userId bigint references USERS(id) on delete cascade
28 create index usergroup_groupId_idx ON USERGROUP(groupId);
30 DROP TABLE IF EXISTS GROUPPERMISSIONS CASCADE;
31 CREATE TABLE GROUPPERMISSIONS (
32 id bigint NOT NULL PRIMARY KEY,
33 groupId bigint references GROUP_(id),
34 serviceName varchar(255) not null,
35 methodIndex int not null
40 DROP TABLE IF EXISTS attribute_group CASCADE;
41 CREATE TABLE attribute_group (
42 id bigint NOT NULL PRIMARY KEY
45 DROP TABLE IF EXISTS attribute CASCADE;
46 CREATE TABLE attribute (
47 id bigint NOT NULL PRIMARY KEY,
51 attribute_group_id bigint not null REFERENCES attribute_group (id) on delete cascade on update cascade,
57 DROP TABLE IF EXISTS profile CASCADE;
58 CREATE TABLE profile (
59 id bigint NOT NULL PRIMARY KEY,
63 DROP TABLE IF EXISTS profile_attribute CASCADE;
64 CREATE TABLE profile_attribute (
65 id bigint NOT NULL PRIMARY KEY,
69 profile_id bigint not null REFERENCES profile (id) on delete cascade on update cascade
73 DROP TABLE IF EXISTS gde_file CASCADE;
74 CREATE TABLE gde_file (
75 id bigint NOT NULL PRIMARY KEY,
78 checksum varchar(255),
79 creation_date timestamp,
80 update_date timestamp,
83 deletion_date timestamp,
84 attribute_group_id bigint REFERENCES attribute_group (id),
85 data_profile_id bigint REFERENCES profile (id)
88 DROP TABLE IF EXISTS chunk CASCADE;
90 id bigint NOT NULL PRIMARY KEY,
91 file_id bigint NOT NULL REFERENCES gde_file (id),
93 checksum varchar(255),
99 DROP TABLE IF EXISTS study CASCADE;
101 id bigint NOT NULL PRIMARY KEY,
103 creation_date timestamp,
104 update_date timestamp,
107 deletion_date timestamp,
108 attribute_group_id bigint REFERENCES attribute_group (id),
109 profile_id bigint REFERENCES profile (id),
111 lock_owner bigint references users(id)
116 -- The 1000 first ids are reserved for initial data
117 INSERT INTO users (id,username,userpassword) VALUES (1,'admin','edf123');
118 INSERT INTO group_ (id,groupname) VALUES (1,'admins');
119 INSERT into usergroup(id,groupid,userid) VALUES (2,1,1);
120 /* User management permissions */
121 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (3, 1, 'UserService',1); -- Create user
122 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (4, 1, 'UserService',2); -- Delete user
123 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (5, 1, 'UserService',3); -- Add to group
124 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (6, 1, 'UserService',4); -- Remove from group
125 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (7, 1, 'UserService',5); -- Create group
126 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (8, 1, 'UserService',6); -- Delete group
127 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (9, 1, 'UserService',7); -- Find user
128 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (10, 1, 'UserService',8); -- Find group
129 /* Studies permissions */
130 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (11, 1, 'StudyService',1); -- Create study
131 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (12, 1, 'StudyService',2); -- Set study state
132 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (13, 1, 'StudyService',3); -- Read study
133 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (14, 1, 'StudyService',4); -- Delete study
134 /* Profiles services */
135 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (15, 1, 'ProfilesService',1); -- Create profile
136 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (16, 1, 'ProfilesService',2); -- Delete profile
137 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (17, 1, 'ProfilesService',3); -- Read profile
138 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (18, 1, 'ProfilesService',4); -- Update profile
139 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (19, 1, 'ProfilesService',5); -- Create profile attribute
140 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (20, 1, 'ProfilesService',6); -- Delete profile attribute
141 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (21, 1, 'ProfilesService',7); -- Read profile attribute
142 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (22, 1, 'ProfilesService',8); -- Update profile attribute
143 /* Attributes services */
144 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (23, 1, 'AttributesService',1); -- Create attribute
145 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (24, 1, 'AttributesService',2); -- Delete attribute
146 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (25, 1, 'AttributesService',3); -- Read attribute
147 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (26, 1, 'AttributesService',4); -- Create attribute group
148 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (27, 1, 'AttributesService',5); -- Delete attribute group
149 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (28, 1, 'AttributesService',6); -- Update attribute group
150 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (29, 1, 'AttributesService',7); -- Read attribute group
152 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (30, 1, 'FileService',1); --
153 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (31, 1, 'FileService',2); --
154 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (32, 1, 'FileService',3); --
155 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (33, 1, 'FileService',4); --
156 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (34, 1, 'FileService',5); --
157 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (35, 1, 'FileService',6); --
158 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (36, 1, 'FileService',7); --