From 15386852bbc3642bc615a783e4e716461ef4748e Mon Sep 17 00:00:00 2001
From: Kavoos Bojnourdi <kavoos.bojnourdi@edf.fr>
Date: Thu, 13 Aug 2015 23:03:09 +0200
Subject: [PATCH] Fix SQL

---
 .../GDE_App/GDE-ejb/src/conf/persistence.xml  |  1 -
 projects/GDE_App/src/GDE_DB_Init.sql          | 52 +++++++++++--------
 2 files changed, 30 insertions(+), 23 deletions(-)

diff --git a/projects/GDE_App/GDE-ejb/src/conf/persistence.xml b/projects/GDE_App/GDE-ejb/src/conf/persistence.xml
index b2b1d79..775e749 100644
--- a/projects/GDE_App/GDE-ejb/src/conf/persistence.xml
+++ b/projects/GDE_App/GDE-ejb/src/conf/persistence.xml
@@ -6,7 +6,6 @@
     <exclude-unlisted-classes>false</exclude-unlisted-classes>
     <properties>
       <property name="eclipselink.target-database" value="PostgreSQL"/>
-      <property name="javax.persistence.schema-generation.database.action" value="create"/>
     </properties>
   </persistence-unit>
 </persistence>
diff --git a/projects/GDE_App/src/GDE_DB_Init.sql b/projects/GDE_App/src/GDE_DB_Init.sql
index fee15ea..76ebb4c 100644
--- a/projects/GDE_App/src/GDE_DB_Init.sql
+++ b/projects/GDE_App/src/GDE_DB_Init.sql
@@ -26,14 +26,6 @@ CREATE TABLE USERGROUP (
 );
 create index usergroup_groupId_idx ON USERGROUP(groupId);
 
-DROP TABLE IF EXISTS attribute_group_attribute CASCADE;
-CREATE TABLE attribute_group_attribute
-(
-  attributegroup_id bigint NOT NULL REFERENCES attribute (id) ON UPDATE NO ACTION ON DELETE NO ACTION,
-  attributecollection_id bigint NOT NULL REFERENCES attribute_group (id)ON UPDATE NO ACTION ON DELETE NO ACTION
-);
-create index attribute_group_attribute_idx1 on attribute_group_attribute(attributegroup_id);
-create index attribute_group_attribute_idx2 on attribute_group_attribute(attributecollection_id);
 
 
 DROP TABLE IF EXISTS GROUPPERMISSIONS CASCADE;
@@ -61,6 +53,36 @@ CREATE TABLE attribute (
     mandatory boolean
 );
 
+DROP TABLE IF EXISTS attribute_group_attribute CASCADE;
+CREATE TABLE attribute_group_attribute
+(
+  attributegroup_id bigint NOT NULL,
+  attributecollection_id bigint NOT NULL,
+  CONSTRAINT attribute_group_attribute_pkey PRIMARY KEY (attributegroup_id, attributecollection_id),
+  CONSTRAINT fk_attribute_group_attribute_attributecollection_id FOREIGN KEY (attributecollection_id)
+      REFERENCES attribute (id) MATCH SIMPLE
+      ON UPDATE NO ACTION ON DELETE NO ACTION,
+  CONSTRAINT fk_attribute_group_attribute_attributegroup_id FOREIGN KEY (attributegroup_id)
+      REFERENCES attribute_group (id) MATCH SIMPLE
+      ON UPDATE NO ACTION ON DELETE NO ACTION
+);
+create index attribute_group_attribute_idx1 on attribute_group_attribute(attributegroup_id);
+create index attribute_group_attribute_idx2 on attribute_group_attribute(attributecollection_id);
+
+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 REFERENCES profile (id)
+);
 
 /* DATA */
 
@@ -106,20 +128,6 @@ CREATE TABLE study (
 );
 /* PROFILES */
 
-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 REFERENCES profile (id)
-);
 
 -- The 1000 first ids are reserved for initial data
 INSERT INTO users (id,username,userpassword) VALUES (1,'admin','edf123');
-- 
2.39.2