projects/GDE_App/src/GDE_DB_Init.sql

   1 /* Unique Ids sequence generator */
   2
   3 drop sequence SEQ_GEN_SEQUENCE;
   4 create sequence SEQ_GEN_SEQUENCE START WITH 1000 INCREMENT BY 50;
   5
   6
   7 DROP TABLE IF EXISTS GROUP_ CASCADE;
   8 CREATE TABLE GROUP_ (
   9     id bigint NOT NULL PRIMARY KEY,
  10     groupName varchar(255) NOT NULL
  11 );
  12
  13 DROP TABLE IF EXISTS USERS CASCADE;
  14 CREATE TABLE USERS (
  15     id bigint NOT NULL PRIMARY KEY,
  16     userName varchar(255) not null,
  17     userpassword varchar(255) not null
  18 );
  19 CREATE INDEX users_username_index ON USERS(userName);
  20
  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
  26 );
  27 create index usergroup_groupId_idx ON USERGROUP(groupId);
  28
  29
  30
  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
  37 );
  38
  39 /* METADATA */
  40
  41 DROP TABLE IF EXISTS attribute_group CASCADE;
  42 CREATE TABLE attribute_group (
  43     id bigint NOT NULL PRIMARY KEY
  44 );
  45
  46 DROP TABLE IF EXISTS attribute CASCADE;
  47 CREATE TABLE attribute (
  48     id bigint NOT NULL PRIMARY KEY,
  49     name varchar(255),
  50     type varchar(255),
  51     value varchar(255),
  52     attribute_group_id bigint REFERENCES attribute_group (id),
  53     mandatory boolean
  54 );
  55
  56 DROP TABLE IF EXISTS attribute_group_attribute CASCADE;
  57 CREATE TABLE attribute_group_attribute
  58 (
  59   attributegroup_id bigint NOT NULL,
  60   attributecollection_id bigint NOT NULL,
  61   CONSTRAINT attribute_group_attribute_pkey PRIMARY KEY (attributegroup_id, attributecollection_id),
  62   CONSTRAINT fk_attribute_group_attribute_attributecollection_id FOREIGN KEY (attributecollection_id)
  63       REFERENCES attribute (id) MATCH SIMPLE
  64       ON UPDATE NO ACTION ON DELETE NO ACTION,
  65   CONSTRAINT fk_attribute_group_attribute_attributegroup_id FOREIGN KEY (attributegroup_id)
  66       REFERENCES attribute_group (id) MATCH SIMPLE
  67       ON UPDATE NO ACTION ON DELETE NO ACTION
  68 );
  69 create index attribute_group_attribute_idx1 on attribute_group_attribute(attributegroup_id);
  70 create index attribute_group_attribute_idx2 on attribute_group_attribute(attributecollection_id);
  71
  72 DROP TABLE IF EXISTS profile CASCADE;
  73 CREATE TABLE profile (
  74     id bigint NOT NULL PRIMARY KEY,
  75     name varchar(255)
  76 );
  77
  78 DROP TABLE IF EXISTS profile_attribute CASCADE;
  79 CREATE TABLE profile_attribute (
  80     id bigint NOT NULL PRIMARY KEY,
  81     name varchar(255),
  82     type varchar(255),
  83     mandatory boolean,
  84     profile_id bigint REFERENCES profile (id)
  85 );
  86
  87 /* DATA */
  88
  89 DROP TABLE IF EXISTS gde_file CASCADE;
  90 CREATE TABLE gde_file (
  91     id bigint NOT NULL PRIMARY KEY,
  92     name varchar(255),
  93     length bigint,
  94     checksum varchar(255),
  95     creation_date timestamp,
  96     update_date timestamp,
  97     valid boolean,
  98     deleted boolean,
  99     deletion_date timestamp,
 100     attribute_group_id bigint REFERENCES attribute_group (id),
 101     data_profile_id bigint REFERENCES profile (id)
 102 );
 103
 104 DROP TABLE IF EXISTS chunk CASCADE;
 105 CREATE TABLE chunk (
 106     id bigint NOT NULL PRIMARY KEY,
 107     file_id bigint NOT NULL REFERENCES gde_file (id),
 108     rank bigint,
 109     checksum varchar(255),
 110     size bigint,
 111     data bytea
 112 );
 113
 114 /* STUDIES */
 115
 116 DROP TABLE IF EXISTS study CASCADE;
 117 CREATE TABLE study (
 118     id bigint NOT NULL PRIMARY KEY,
 119     name varchar(255),
 120     creation_date timestamp,
 121     update_date timestamp,
 122     valid boolean,
 123     deleted boolean,
 124     deletion_date timestamp,
 125     attribute_group_id bigint REFERENCES attribute_group (id),
 126     profile_id bigint REFERENCES profile (id),
 127     locked boolean
 128 );
 129 /* PROFILES */
 130
 131
 132 -- The 1000 first ids are reserved for initial data
 133 INSERT INTO users (id,username,userpassword) VALUES (1,'admin','edf123');
 134 INSERT INTO group_ (id,groupname) VALUES (1,'admins');
 135 INSERT into usergroup(id,groupid,userid) VALUES (2,1,1);
 136 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (3, 1, 'UserService',1); -- Create user
 137 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (4, 1, 'UserService',2); -- Delete user
 138 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (5, 1, 'UserService',3); -- Add to group
 139 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (6, 1, 'UserService',4); -- Remove from group
 140 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (7, 1, 'UserService',5); -- Create group
 141 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (8, 1, 'UserService',6); -- Delete group
 142 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (9, 1, 'UserService',7); -- Find user
 143 INSERT INTO GROUPPERMISSIONS (id,groupid,servicename,methodindex) VALUES (10, 1, 'UserService',8); -- Find group
 144
 145