1 /* Unique Ids sequence generator */
3 drop sequence SEQ_GEN_SEQUENCE;
4 create sequence SEQ_GEN_SEQUENCE START WITH 1000 INCREMENT BY 50;
7 DROP TABLE IF EXISTS GROUP_ CASCADE;
9 id bigint NOT NULL PRIMARY KEY,
10 groupName varchar(255) NOT NULL
13 DROP TABLE IF EXISTS USERS CASCADE;
15 id bigint NOT NULL PRIMARY KEY,
16 userName varchar(255) not null,
17 userpassword varchar(255) not null
19 CREATE INDEX users_username_index ON USERS(userName);
21 DROP TABLE IF EXISTS USERGROUP CASCADE;
22 CREATE TABLE USERGROUP (
23 id bigint NOT NULL PRIMARY KEY,
24 groupId bigint references GROUP_(id) on delete cascade,
25 userId bigint references USERS(id) on delete cascade
27 create index usergroup_groupId_idx ON USERGROUP(groupId);
29 DROP TABLE IF EXISTS attribute_group_attribute CASCADE;
30 CREATE TABLE attribute_group_attribute
32 attributegroup_id bigint NOT NULL REFERENCES attribute (id) ON UPDATE NO ACTION ON DELETE NO ACTION,
33 attributecollection_id bigint NOT NULL REFERENCES attribute_group (id)ON UPDATE NO ACTION ON DELETE NO ACTION
35 create index attribute_group_attribute_idx1 on attribute_group_attribute(attributegroup_id);
36 create index attribute_group_attribute_idx2 on attribute_group_attribute(attributecollection_id);
39 DROP TABLE IF EXISTS GROUPPERMISSIONS CASCADE;
40 CREATE TABLE GROUPPERMISSIONS (
41 id bigint NOT NULL PRIMARY KEY,
42 groupId bigint references GROUP_(id),
43 serviceName varchar(255) not null,
44 methodIndex int not null
49 DROP TABLE IF EXISTS attribute_group CASCADE;
50 CREATE TABLE attribute_group (
51 id bigint NOT NULL PRIMARY KEY
54 DROP TABLE IF EXISTS attribute CASCADE;
55 CREATE TABLE attribute (
56 id bigint NOT NULL PRIMARY KEY,
60 attribute_group_id bigint REFERENCES attribute_group (id),
67 DROP TABLE IF EXISTS gde_file CASCADE;
68 CREATE TABLE gde_file (
69 id bigint NOT NULL PRIMARY KEY,
72 checksum varchar(255),
73 creation_date timestamp,
74 update_date timestamp,
77 deletion_date timestamp,
78 attribute_group_id bigint REFERENCES attribute_group (id),
79 data_profile_id bigint REFERENCES profile (id)
82 DROP TABLE IF EXISTS chunk CASCADE;
84 id bigint NOT NULL PRIMARY KEY,
85 file_id bigint NOT NULL REFERENCES gde_file (id),
87 checksum varchar(255),
94 DROP TABLE IF EXISTS study CASCADE;
96 id bigint NOT NULL PRIMARY KEY,
98 creation_date timestamp,
99 update_date timestamp,
102 deletion_date timestamp,
103 attribute_group_id bigint REFERENCES attribute_group (id),
104 profile_id bigint REFERENCES profile (id),
109 DROP TABLE IF EXISTS profile CASCADE;
110 CREATE TABLE profile (
111 id bigint NOT NULL PRIMARY KEY,
115 DROP TABLE IF EXISTS profile_attribute CASCADE;
116 CREATE TABLE profile_attribute (
117 id bigint NOT NULL PRIMARY KEY,
121 profile_id bigint REFERENCES profile (id)
124 -- The 1000 first ids are reserved for initial data
125 INSERT INTO users (id,username,userpassword) VALUES (1,'admin','edf123');
126 INSERT INTO group_ (id,groupname) VALUES (1,'admins');
127 INSERT into usergroup(id,groupid,userid) VALUES (2,1,1);
128 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (3, 1, 'UserService',1); -- Create user
129 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (4, 1, 'UserService',2); -- Delete user
130 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (5, 1, 'UserService',3); -- Add to group
131 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (6, 1, 'UserService',4); -- Remove from group
132 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (7, 1, 'UserService',5); -- Create group
133 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (8, 1, 'UserService',6); -- Delete group
134 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (9, 1, 'UserService',7); -- Find user
135 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (10, 1, 'UserService',8); -- Find group