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;
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);
31 DROP TABLE IF EXISTS GROUPPERMISSIONS CASCADE;
32 CREATE TABLE GROUPPERMISSIONS (
33 id bigint NOT NULL PRIMARY KEY,
34 groupId bigint references GROUP_(id),
35 serviceName varchar(255) not null,
36 methodIndex int not null
41 DROP TABLE IF EXISTS attribute_group CASCADE;
42 CREATE TABLE attribute_group (
43 id bigint NOT NULL PRIMARY KEY
46 DROP TABLE IF EXISTS attribute CASCADE;
47 CREATE TABLE attribute (
48 id bigint NOT NULL PRIMARY KEY,
52 attribute_group_id bigint REFERENCES attribute_group (id),
56 DROP TABLE IF EXISTS attribute_group_attribute CASCADE;
57 CREATE TABLE attribute_group_attribute
59 attributegroup_id bigint REFERENCES attribute_group (id) ON UPDATE NO ACTION ON DELETE NO ACTION,
60 attribute_id bigint REFERENCES attribute (id) ON UPDATE NO ACTION ON DELETE NO ACTION
62 alter table attribute_group_attribute add primary key (attributegroup_id, attribute_id);
66 DROP TABLE IF EXISTS profile CASCADE;
67 CREATE TABLE profile (
68 id bigint NOT NULL PRIMARY KEY,
72 DROP TABLE IF EXISTS profile_attribute CASCADE;
73 CREATE TABLE profile_attribute (
74 id bigint NOT NULL PRIMARY KEY,
78 profile_id bigint REFERENCES profile (id)
81 DROP TABLE IF EXISTS PROFILE_ATTRIBUTE_PROFILE CASCADE;
82 CREATE TABLE PROFILE_ATTRIBUTE_PROFILE (
83 profile_id bigint references profile(id) on update no action on delete no action,
84 profile_attribute_id bigint references profile_attribute on update no action on delete no action
86 alter table PROFILE_ATTRIBUTE_PROFILE add primary key (profile_id, profile_attribute_id);
90 DROP TABLE IF EXISTS gde_file CASCADE;
91 CREATE TABLE gde_file (
92 id bigint NOT NULL PRIMARY KEY,
95 checksum varchar(255),
96 creation_date timestamp,
97 update_date timestamp,
100 deletion_date timestamp,
101 attribute_group_id bigint REFERENCES attribute_group (id),
102 data_profile_id bigint REFERENCES profile (id)
105 DROP TABLE IF EXISTS chunk CASCADE;
107 id bigint NOT NULL PRIMARY KEY,
108 file_id bigint NOT NULL REFERENCES gde_file (id),
110 checksum varchar(255),
117 DROP TABLE IF EXISTS study CASCADE;
119 id bigint NOT NULL PRIMARY KEY,
121 creation_date timestamp,
122 update_date timestamp,
125 deletion_date timestamp,
126 attribute_group_id bigint REFERENCES attribute_group (id),
127 profile_id bigint REFERENCES profile (id),
133 -- The 1000 first ids are reserved for initial data
134 INSERT INTO users (id,username,userpassword) VALUES (1,'admin','edf123');
135 INSERT INTO group_ (id,groupname) VALUES (1,'admins');
136 INSERT into usergroup(id,groupid,userid) VALUES (2,1,1);
137 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (3, 1, 'UserService',1); -- Create user
138 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (4, 1, 'UserService',2); -- Delete user
139 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (5, 1, 'UserService',3); -- Add to group
140 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (6, 1, 'UserService',4); -- Remove from group
141 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (7, 1, 'UserService',5); -- Create group
142 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (8, 1, 'UserService',6); -- Delete group
143 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (9, 1, 'UserService',7); -- Find user
144 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (10, 1, 'UserService',8); -- Find group