Author: acheype Date: 2017-08-03 03:32:51 +0200 (Thu, 03 Aug 2017) New Revision: 277 Url: http://forge.codelutin.com/projects/cantharella/repository/revisions/277 Log: - IRD Logo changed - Chairman of IRD changed for the imprint - Dockerization of the app Added: trunk/README.md trunk/docker/ trunk/docker/cantharella-db/ trunk/docker/cantharella-db/Dockerfile trunk/docker/cantharella-db/create_db.sql trunk/docker/cantharella-db/docker-entrypoint-initdb.d/ trunk/docker/cantharella-db/docker-entrypoint-initdb.d/init-cantharella-db.sh trunk/docker/cantharella-db/docker-entrypoint-initdb.d/sql/ trunk/docker/cantharella-db/docker-entrypoint-initdb.d/sql/cantharella_schema_1.0.sql trunk/docker/cantharella-db/docker-entrypoint-initdb.d/sql/cantharella_schema_1.1.sql trunk/docker/cantharella-db/docker-entrypoint-initdb.d/sql/cantharella_schema_1.2.sql trunk/docker/cantharella-db/docker-entrypoint-initdb.d/sql/create_db.sql trunk/docker/cantharella-db/docker-entrypoint-initdb.d/sql/dev_update_1.0_to_1.1.sql trunk/docker/cantharella-web/ trunk/docker/cantharella-web/Dockerfile trunk/docker/docker-compose.yml trunk/sql/ Removed: trunk/cantharella.data/src/main/sql/ trunk/sql/init.sql Modified: trunk/cantharella.data/pom.xml trunk/cantharella.data/src/main/resources/cantharella.conf trunk/cantharella.service/pom.xml trunk/cantharella.utils/pom.xml trunk/cantharella.web/ trunk/cantharella.web/pom.xml trunk/cantharella.web/src/main/java/nc/ird/cantharella/web/pages/ImprintPage.html trunk/cantharella.web/src/main/java/nc/ird/cantharella/web/pages/TemplatePage.html trunk/cantharella.web/src/main/resources/cantharella.conf trunk/cantharella.web/src/main/resources/log4j.xml trunk/cantharella.web/src/main/resources/log4j_prod.xml trunk/cantharella.web/src/main/resources/web_en.properties trunk/cantharella.web/src/main/resources/web_fr.properties trunk/cantharella.web/src/main/webapp/images/ird.png trunk/pom.xml trunk/sql/cantharella_schema_1.2.sql trunk/src/site/rst/index.rst Copied: trunk/README.md (from rev 276, trunk/README.txt) =================================================================== --- trunk/README.md (rev 0) +++ trunk/README.md 2017-08-03 01:32:51 UTC (rev 277) @@ -0,0 +1,95 @@ +# Cantharella + +*The GitHub repository in only a mirror of the one hosted by Code Lutin [here]()* + + +The information system (IS) Cantharella: Pharmacochemical database of natural substances, designed and developed by +IRD (www.ird.fr), share and sustain pharmacochemical data of all organisms collected for the study of their natural +substances, with a controlled access via internet. +The IS provides access to harvest and taxonomic data, monitor various chemical processes of extraction and +purification, and finally centralize all biological activities. The database is progressive according to the extraction +and purification methods needed and the biological tests performed. + +Cantharella is an open source information system based on Java components and a PostgreSQL database. + +***News from 2017-08:***The application have been dockerized. See [Deployment with Docker](#deployment-with-docker) +section. + + + +More info on the project, [here](src/site/rst/index.rst). + +### License + +Cantharella is distributed under the terms of the under the terms of the GNU General Public License, version 3.0. The +text is included in the file LICENSE in the root of the project. + +### Java and database servers requirements + +Cantharella requires at least Java 1.6. The application server for running your web application should adhere to the +servlet specification version 2.5 or newer. Tomcat 6 or newer is recommended. +For the database, the IS requires a PostgreSQL database (version 8.0 or newer). + +### Dependencies + +Cantharella is dependent to some librairies from another open source projects. This is the list of the dependencies and +their licenses. + - Apache Commons (http://commons.apache.org), under the Apache License 2.0 + - JUnit (www.junit.org), under the Common Public License 1.0 + - ICU4J (http://www.icu-project.org), under the ICU License (compatible with GNU GPL) + - Log4j (http://logging.apache.org/log4j), under the Apache License 2.0 + - SFL4J (http://www.slf4j.org), under the MIT license + - AspectJ (http://www.eclipse.org/aspectj), under the Common Public License 1.0 + - Spring (http://www.springsource.org), under the Apache License 2.0 + - Javassist (http://www.jboss.org/javassist), under the GNU Lesser General Public License 2.1 + - Cglib (http://cglib.sourceforge.net), under the Apache License 1.1 + - Hibernate (http://www.hibernate.org), under the GNU Lesser General Public License 2.1 + - POI (http://poi.apache.org), under the Apache License 2.0 + - Wicket (http://wicket.apache.org), under the Apache License 2.0 + - PostgreSQL JDBC Driver (http://jdbc.postgresql.org), under the BSD License (same as the SGBD) + - ChemDoodle Web Components (http://web.chemdoodle.com), under GNU General Public License, version 3.0 + +You can refer to the licenses folder of the project to see each license conditions. +The dependency libraries are managed by the the Maven build tool (http://maven.apache.org). The Maven's dependency +mechanism will download all the necessary libraries automatically. You can see the dependencies declared in the +pom.xml files to known each library version. + +## Deployment with Docker + +*You can find the detailled instruction for a classic installation [here](src/site/rst/installation.rst).* + +### Requirement + +Please first install Docker CE (Community Edition) and Docker Compose by following the official instructions : + - https://docs.docker.com/engine/installation/ + - https://docs.docker.com/compose/install/ + +### Building + +First construct the war from the project sources with mvn (from the project root) + + mvn clean package + +Then copy this war and the sql scripts (if needed, replace the version number) + + cp cantharella.web/target/cantharella-1.2.2.war docker/cantharella-web/cantharella.war + cp -R sql docker/cantharella-db/docker-entrypoint-initdb.d + +Then build the images (cantharella.db and cantharella.web) + + cd docker + docker-compose build + +### Starting the application + +Launch the application with the docker-compose command : + + docker-compose up + +Then, you can verify the status of the two containers (cantharella.db and cantharella.web) : + + docker ps + +To stop properly the application (with the delete of the containers), execute this command : + + docker-compose down \ No newline at end of file Modified: trunk/cantharella.data/pom.xml =================================================================== --- trunk/cantharella.data/pom.xml 2014-05-07 08:30:33 UTC (rev 276) +++ trunk/cantharella.data/pom.xml 2017-08-03 01:32:51 UTC (rev 277) @@ -5,7 +5,7 @@ <parent> <groupId>nc.ird</groupId> <artifactId>cantharella</artifactId> - <version>1.2.1-SNAPSHOT</version> + <version>1.2.2</version> </parent> <artifactId>cantharella.data</artifactId> Modified: trunk/cantharella.data/src/main/resources/cantharella.conf =================================================================== --- trunk/cantharella.data/src/main/resources/cantharella.conf 2014-05-07 08:30:33 UTC (rev 276) +++ trunk/cantharella.data/src/main/resources/cantharella.conf 2017-08-03 01:32:51 UTC (rev 277) @@ -24,9 +24,9 @@ # # DATABASE CONFIGURATION # -db.url=jdbc:postgresql:cantharella +db.url=jdbc:postgresql://postgres:5432/cantharella db.user=cantharella -db.password=cantharella +db.password=ctrl4 db.debug=true # value for production #db.debug=false Modified: trunk/cantharella.service/pom.xml =================================================================== --- trunk/cantharella.service/pom.xml 2014-05-07 08:30:33 UTC (rev 276) +++ trunk/cantharella.service/pom.xml 2017-08-03 01:32:51 UTC (rev 277) @@ -5,7 +5,7 @@ <parent> <artifactId>cantharella</artifactId> <groupId>nc.ird</groupId> - <version>1.2.1-SNAPSHOT</version> + <version>1.2.2</version> </parent> <artifactId>cantharella.service</artifactId> Modified: trunk/cantharella.utils/pom.xml =================================================================== --- trunk/cantharella.utils/pom.xml 2014-05-07 08:30:33 UTC (rev 276) +++ trunk/cantharella.utils/pom.xml 2017-08-03 01:32:51 UTC (rev 277) @@ -5,7 +5,7 @@ <parent> <groupId>nc.ird</groupId> <artifactId>cantharella</artifactId> - <version>1.2.1-SNAPSHOT</version> + <version>1.2.2</version> </parent> <artifactId>cantharella.utils</artifactId> Index: trunk/cantharella.web =================================================================== --- trunk/cantharella.web 2014-05-07 08:30:33 UTC (rev 276) +++ trunk/cantharella.web 2017-08-03 01:32:51 UTC (rev 277) Property changes on: trunk/cantharella.web ___________________________________________________________________ Modified: svn:ignore ## -2,7 +2,6 ## nb-configuration.xml *.iws .classpath -*.iml .idea .project target Modified: trunk/cantharella.web/pom.xml =================================================================== --- trunk/cantharella.web/pom.xml 2014-05-07 08:30:33 UTC (rev 276) +++ trunk/cantharella.web/pom.xml 2017-08-03 01:32:51 UTC (rev 277) @@ -5,7 +5,7 @@ <parent> <artifactId>cantharella</artifactId> <groupId>nc.ird</groupId> - <version>1.2.1-SNAPSHOT</version> + <version>1.2.2</version> </parent> <artifactId>cantharella.web</artifactId> Modified: trunk/cantharella.web/src/main/java/nc/ird/cantharella/web/pages/ImprintPage.html =================================================================== --- trunk/cantharella.web/src/main/java/nc/ird/cantharella/web/pages/ImprintPage.html 2014-05-07 08:30:33 UTC (rev 276) +++ trunk/cantharella.web/src/main/java/nc/ird/cantharella/web/pages/ImprintPage.html 2017-08-03 01:32:51 UTC (rev 277) @@ -30,7 +30,10 @@ <p><wicket:message key="ImprintPage.AccessRights.Address"/></p> <h3><wicket:message key="ImprintPage.Editor"/></h3> - <p><wicket:message key="ImprintPage.Editor.Director"/></p> + <p><wicket:message key="ImprintPage.Editor.Director.1"/> + <a href="https://www.ird.fr/l-ird/l-organigramme/presidence/president-directeur-general"> + <wicket:message key="ImprintPage.Editor.Director.2"/></a> + </p> <p><wicket:message key="ImprintPage.Editor.Administrator"/></p> <h3><wicket:message key="ImprintPage.Hosting"/></h3> Modified: trunk/cantharella.web/src/main/java/nc/ird/cantharella/web/pages/TemplatePage.html =================================================================== --- trunk/cantharella.web/src/main/java/nc/ird/cantharella/web/pages/TemplatePage.html 2014-05-07 08:30:33 UTC (rev 276) +++ trunk/cantharella.web/src/main/java/nc/ird/cantharella/web/pages/TemplatePage.html 2017-08-03 01:32:51 UTC (rev 277) @@ -171,12 +171,16 @@ </div></div> <div id="footer"> - <div class="f_base"> - <p class="titre">Cantharella - <wicket:message key="TemplatePage.Subtitle"/></p> - <p><a wicket:id="ContactPage"><wicket:message key="ContactPage" /></a> - - <a wicket:id="ImprintPage"><wicket:message key="ImprintPage" /></a> - - <wicket:message key="TemplatePage.Copyright" /> - <span wicket:id="Year" /> <a href="http://www.ird.fr/"><img src="images/ird.png" alt="IRD" height="41" width="75"/></a></p> - </div> + <div class="f_base" style="height: 160px;"> + <p class="titre">Cantharella - <wicket:message key="TemplatePage.Subtitle"/></p> + <p> + <a href="http://www.ird.fr/"><img style="float: right; margin-left:1.5rem;" src="images/ird.png" alt="IRD"></a> + <p style="margin-top:45px"> + <a wicket:id="ContactPage"><wicket:message key="ContactPage" /></a> - <a wicket:id="ImprintPage"><wicket:message key="ImprintPage" /></a><br> + <wicket:message key="TemplatePage.Copyright" /> - <span wicket:id="Year" /> + </p> + </p> + </div> </div> <div id="AjaxIndicator" style="display: none;"></div> Modified: trunk/cantharella.web/src/main/resources/cantharella.conf =================================================================== --- trunk/cantharella.web/src/main/resources/cantharella.conf 2014-05-07 08:30:33 UTC (rev 276) +++ trunk/cantharella.web/src/main/resources/cantharella.conf 2017-08-03 01:32:51 UTC (rev 277) @@ -36,8 +36,6 @@ wicket.configuration=deployment log4j.config=classpath:log4j.xml -# value for production -#log4j.config=/home/tomcat/.config/log4j_prod.xml #document file max upload size (Mb) document.maxUploadSize=1 Modified: trunk/cantharella.web/src/main/resources/log4j.xml =================================================================== --- trunk/cantharella.web/src/main/resources/log4j.xml 2014-05-07 08:30:33 UTC (rev 276) +++ trunk/cantharella.web/src/main/resources/log4j.xml 2017-08-03 01:32:51 UTC (rev 277) @@ -25,41 +25,54 @@ "http://logging.apache.org/log4j/1.2/apidocs/org/apache/log4j/xml/doc-files/log4j.dtd"> <log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/"> - <!-- Console IRD: detailed logs --> + <!--Console IRD: detailed logs --> <appender name="consoleIRD" class="org.apache.log4j.ConsoleAppender"> <param name="threshold" value="debug" /> <layout class="org.apache.log4j.PatternLayout"> - <param name="ConversionPattern" value="%d{HH:mm:ss.SSS} %5p (%F:%M:%L) %m%n" /> + <param name="ConversionPattern" value="%d{HH:mm:ss} %5p (%F:%M:%L) %m%n" /> </layout> </appender> - <!-- Console root: simple logs --> + <!--<!– Console root: simple logs –>--> <appender name="consoleRoot" class="org.apache.log4j.ConsoleAppender"> <param name="threshold" value="debug" /> <layout class="org.apache.log4j.PatternLayout"> - <param name="ConversionPattern" value="%d{HH:mm:ss.SSS} %5p (%c) %m%n" /> + <param name="ConversionPattern" value="%d{HH:mm:ss} %5p (%c) %m%n" /> </layout> </appender> + <!--<appender name="console" class="org.apache.log4j.ConsoleAppender">--> + <!--<param name="Target" value="System.out"/>--> + <!--<layout class="org.apache.log4j.PatternLayout">--> + <!--<param name="ConversionPattern" value="%d{MM-dd/HH:mm:ss} %5p (%F:%M:%L) %m%n"/>--> + <!--</layout>--> + <!--</appender>--> + <!-- Logs IRD --> <logger name="nc.ird" additivity="false"> - <level value="debug" /> + <level value="info" /> <appender-ref ref="consoleIRD" /> </logger> - <!-- Logs Hibernate --> + <!-- Logs Hibernate (you can activate it on dev mode) --> <logger name="org.hibernate.SQL" additivity="false"> - <level value="info" /> - <appender-ref ref="consoleRoot" /> - </logger> + <level value="OFF" /> + <appender-ref ref="consoleRoot" /> + </logger> <logger name="org.hibernate.pretty" additivity="false"> - <level value="info" /> + <level value="OFF" /> <appender-ref ref="consoleRoot" /> </logger> - + + <!--<logger name="org.apache.wicket" additivity="false">--> + <!--<level value="INFO" />--> + <!--<appender-ref ref="consoleRoot" />--> + <!--</logger>--> + <!-- Logs default --> <root> - <level value="warn" /> + <level value="WARN" /> <appender-ref ref="consoleRoot" /> </root> + </log4j:configuration> Modified: trunk/cantharella.web/src/main/resources/log4j_prod.xml =================================================================== --- trunk/cantharella.web/src/main/resources/log4j_prod.xml 2014-05-07 08:30:33 UTC (rev 276) +++ trunk/cantharella.web/src/main/resources/log4j_prod.xml 2017-08-03 01:32:51 UTC (rev 277) @@ -26,7 +26,7 @@ <log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/"> <!-- Asynchronous daily rolling file logs --> - <appender name="file" class="org.apache.log4j.DailyRollingFileAppender"> + <!-- <appender name="file" class="org.apache.log4j.DailyRollingFileAppender"> <param name="file" value="/opt/tomcat/logs/cantharella.log" /> <param name="threshold" value="info" /> <param name="datePattern" value="'.'yyyy-MM-dd" /> @@ -37,10 +37,10 @@ <appender name="async" class="org.apache.log4j.AsyncAppender"> <param name="bufferSize" value="128" /> <appender-ref ref="file" /> - </appender> + </appender> --> <!-- E-mail logs --> - <appender name="mail" class="org.apache.log4j.net.SMTPAppender"> + <!-- <appender name="mail" class="org.apache.log4j.net.SMTPAppender"> <param name="SMTPHost" value="smtp.ird.nc" /> <param name="threshold" value="error" /> <param name="bufferSize" value="512" /> @@ -50,29 +50,44 @@ <layout class="org.apache.log4j.PatternLayout"> <param name="ConversionPattern" value="%d{HH:mm:ss.SSS} %5p (%c) %m%n" /> </layout> + </appender> --> + + <!--Console IRD: detailed logs --> + <appender name="consoleIRD" class="org.apache.log4j.ConsoleAppender"> + <param name="threshold" value="debug" /> + <layout class="org.apache.log4j.PatternLayout"> + <param name="ConversionPattern" value="%d{MM-dd/HH:mm:ss} %5p (%F:%M:%L) %m%n" /> + </layout> </appender> + <!--<!– Console root: simple logs –>--> + <appender name="consoleRoot" class="org.apache.log4j.ConsoleAppender"> + <param name="threshold" value="debug" /> + <layout class="org.apache.log4j.PatternLayout"> + <param name="ConversionPattern" value="%d{MM-dd/HH:mm:ss} %5p (%c) %m%n" /> + </layout> + </appender> + <!-- Logs IRD --> <logger name="nc.ird" additivity="false"> <level value="info" /> - <appender-ref ref="async" /> + <appender-ref ref="consoleIRD" /> </logger> - <!-- Logs Hibernate --> - <logger name="org.hibernate" additivity="false"> - <!-- Disabled --> - <level value="off"/> + <!-- Logs Hibernate (you can activate it on dev mode) --> + <logger name="org.hibernate.SQL" additivity="false"> + <level value="OFF" /> + <appender-ref ref="consoleRoot" /> </logger> - - <logger name="org.hibernate.validator" additivity="false"> - <level value="error" /> - <appender-ref ref="async" /> + <logger name="org.hibernate.pretty" additivity="false"> + <level value="OFF" /> + <appender-ref ref="consoleRoot" /> </logger> - <!-- Logs default --> + <!-- Logs default --> <root> - <level value="warn" /> - <appender-ref ref="async" /> + <level value="WARN" /> + <appender-ref ref="consoleRoot" /> </root> </log4j:configuration> \ No newline at end of file Modified: trunk/cantharella.web/src/main/resources/web_en.properties =================================================================== --- trunk/cantharella.web/src/main/resources/web_en.properties 2014-05-07 08:30:33 UTC (rev 276) +++ trunk/cantharella.web/src/main/resources/web_en.properties 2017-08-03 01:32:51 UTC (rev 277) @@ -85,7 +85,8 @@ ImprintPage.AccessRights.Info=According to the law n° 78-17 of 6 January 1978 on Data Processing, Data Files and Individual Liberties, you have a right of opposition (art. 26) of access (art. 34 - 38), of rectification (art. 36) and of deletion of any data which concerns you. You can practice this right by e-mail at <a href="mailto:dsi@ird.fr">dsi@ird.fr</a> or mail: ImprintPage.AccessRights.Address=Institut de recherche pour le développement<br />DSI<br />44, boulevard de Dunkerque, CS 90009<br />13752 Marseille cedex 02 ImprintPage.Editor=Publisher -ImprintPage.Editor.Director=Publisher : Michel Laurent, Président +ImprintPage.Editor.Director1=Publisher : +ImprintPage.Editor.Director2=Chairman - general director of the IRD ImprintPage.Editor.Administrator=IS Administrator : Sylvain PETEK and Adrien CHEYPE ImprintPage.Hosting=Hosting ImprintPage.Hosting.Info=The website <a href="http://cantharella.ird.nc/">http://cantharella.ird.nc</a> is hosted by the IRD. Modified: trunk/cantharella.web/src/main/resources/web_fr.properties =================================================================== --- trunk/cantharella.web/src/main/resources/web_fr.properties 2014-05-07 08:30:33 UTC (rev 276) +++ trunk/cantharella.web/src/main/resources/web_fr.properties 2017-08-03 01:32:51 UTC (rev 277) @@ -84,7 +84,8 @@ ImprintPage.AccessRights.Info=Conformément à la loi n° 78-17 du 6 janvier 1978 relative à l'informatique, aux fichiers et aux libertés, vous disposez d'un droit d'opposition (art. 26) d'accès (art. 34 à 38), de rectification (art. 36) et de suppression des données qui vous concernent. Vous pouvez exercer ce droit par courrier électronique à <a href="mailto:dsi@ird.fr">dsi@ird.fr</a> ou postal : ImprintPage.AccessRights.Address=Institut de recherche pour le développement<br />DSI<br />44, boulevard de Dunkerque, CS 90009<br />13752 Marseille cedex 02 ImprintPage.Editor=Editeur -ImprintPage.Editor.Director=Directeur de la publication : Michel Laurent, Président +ImprintPage.Editor.Director.1=Directeur de la publication : +ImprintPage.Editor.Director.2=Président-directeur général de l'IRD ImprintPage.Editor.Administrator=Administrateurs du SI : Sylvain PETEK et Adrien CHEYPE ImprintPage.Hosting=Hébergement ImprintPage.Hosting.Info=Le site <a href="http://cantharella.ird.nc/">http://cantharella.ird.nc</a> est hébergé par l'IRD. Modified: trunk/cantharella.web/src/main/webapp/images/ird.png =================================================================== (Binary files differ) Added: trunk/docker/cantharella-db/Dockerfile =================================================================== --- trunk/docker/cantharella-db/Dockerfile (rev 0) +++ trunk/docker/cantharella-db/Dockerfile 2017-08-03 01:32:51 UTC (rev 277) @@ -0,0 +1,5 @@ +FROM postgres:9.6.3 +MAINTAINER Adrien Cheype <adrien.cheype@ird.fr> + +COPY ./docker-entrypoint-initdb.d/ /docker-entrypoint-initdb.d/ +RUN chmod 0755 /docker-entrypoint-initdb.d/init-cantharella-db.sh \ No newline at end of file Added: trunk/docker/cantharella-db/create_db.sql =================================================================== --- trunk/docker/cantharella-db/create_db.sql (rev 0) +++ trunk/docker/cantharella-db/create_db.sql 2017-08-03 01:32:51 UTC (rev 277) @@ -0,0 +1,29 @@ +--- +-- #%L +-- Cantharella :: Data +-- $Id: create_db.sql 133 2013-02-19 11:02:25Z acheype $ +-- $HeadURL: https://svn.codelutin.com/cantharella/trunk/cantharella.data/src/main/sql/cr... $ +-- %% +-- Copyright (C) 2009 - 2012 IRD (Institut de Recherche pour le Developpement) and by respective authors (see below) +-- %% +-- This program is free software: you can redistribute it and/or modify +-- it under the terms of the GNU Affero General Public License as published by +-- the Free Software Foundation, either version 3 of the License, or +-- (at your option) any later version. +-- +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +-- GNU General Public License for more details. +-- +-- You should have received a copy of the GNU Affero General Public License +-- along with this program. If not, see <http://www.gnu.org/licenses/>. +-- #L% +--- +CREATE ROLE cantharella LOGIN PASSWORD 'ctrl4'; +CREATE DATABASE cantharella WITH ENCODING='UTF8' OWNER=cantharella; +GRANT ALL ON DATABASE cantharella TO cantharella; +\connect cantharella +ALTER SCHEMA public OWNER TO cantharella; +GRANT ALL ON SCHEMA public TO cantharella; +\q Added: trunk/docker/cantharella-db/docker-entrypoint-initdb.d/init-cantharella-db.sh =================================================================== --- trunk/docker/cantharella-db/docker-entrypoint-initdb.d/init-cantharella-db.sh (rev 0) +++ trunk/docker/cantharella-db/docker-entrypoint-initdb.d/init-cantharella-db.sh 2017-08-03 01:32:51 UTC (rev 277) @@ -0,0 +1,26 @@ +#!/bin/bash + +function onexit(){ + echo "An error occured - init_cantharella_db script exiting !" +} + +SCRIPT_DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )" + +set -e +trap onexit EXIT + +echo "--- Init Cantharella DB ---" +echo "$SCRIPT_DIR" +echo "No 'cantharella' database found. The database is going to be created." +psql --username "$POSTGRES_USER" -f "$SCRIPT_DIR/sql/create_db.sql" +echo "* create_db.sql imported *" +psql --username cantharella -d cantharella -f $SCRIPT_DIR/sql/cantharella_schema_1.2.sql +echo "* cantharella_schema_1.2.sql imported *" +echo "--- End of Init Cantharella DB ---" +echo -e "\n" +echo "**** You can login with an admin account with : ****" +echo "**** - email : islog@ird.fr ****" +echo "**** - password : password ****" +echo -e "\n" + + Added: trunk/docker/cantharella-db/docker-entrypoint-initdb.d/sql/cantharella_schema_1.0.sql =================================================================== --- trunk/docker/cantharella-db/docker-entrypoint-initdb.d/sql/cantharella_schema_1.0.sql (rev 0) +++ trunk/docker/cantharella-db/docker-entrypoint-initdb.d/sql/cantharella_schema_1.0.sql 2017-08-03 01:32:51 UTC (rev 277) @@ -0,0 +1,593 @@ +--- +-- #%L +-- Cantharella :: Data +-- $Id: cantharella_schema_1.0.sql 211 2013-04-30 07:31:44Z acheype $ +-- $HeadURL: https://svn.codelutin.com/cantharella/trunk/cantharella.data/src/main/sql/ca... $ +-- %% +-- Copyright (C) 2009 - 2012 IRD (Institut de Recherche pour le Developpement) and by respective authors (see below) +-- %% +-- This program is free software: you can redistribute it and/or modify +-- it under the terms of the GNU Affero General Public License as published by +-- the Free Software Foundation, either version 3 of the License, or +-- (at your option) any later version. +-- +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +-- GNU General Public License for more details. +-- +-- You should have received a copy of the GNU Affero General Public License +-- along with this program. If not, see <http://www.gnu.org/licenses/>. +-- #L% +--- + +-- insert once connected to cantharella database with the user cantharella +BEGIN; + + create table Campagne ( + idCampagne int4 not null, + codePays varchar(255) not null, + complement text, + dateDeb date not null, + dateFin date not null, + mentionLegale text, + nom varchar(255) unique not null, + programme varchar(255), + createur_idPersonne int4 not null, + primary key (idCampagne) + ); + + create table CampagneGroupeDroits ( + droitExtrait bool, + droitPuri bool, + droitRecolte bool, + droitTestBio bool, + pk1_idCampagne int4 not null, + pk2_idGroupe int4 not null, + primary key (pk1_idCampagne, pk2_idGroupe) + ); + + create table CampagnePersonneDroits ( + droitExtrait bool, + droitPuri bool, + droitRecolte bool, + droitTestBio bool, + pk1_idCampagne int4 not null, + pk2_idPersonne int4 not null, + primary key (pk1_idCampagne, pk2_idPersonne) + ); + + create table CampagnePersonneParticipant ( + complement text, + pk1_idCampagne int4 not null, + pk2_idPersonne int4 not null, + primary key (pk1_idCampagne, pk2_idPersonne) + ); + + create table Campagne_Station ( + campagnes_idCampagne int4 not null, + stations_idStation int4 not null + ); + + create table ErreurTestBio ( + idErreurTest int4 not null, + description text not null, + nom varchar(255) not null, + primary key (idErreurTest), + unique (nom) + ); + + create table Extraction ( + idExtraction int4 not null, + complement text, + date date not null, + masseDepart numeric(9, 4), + ref varchar(255) unique not null, + createur_idPersonne int4 not null, + lot_idLot int4 not null, + manipulateur_idPersonne int4 not null, + methode_idMethodeExtraction int4 not null, + primary key (idExtraction) + ); + + create table Extrait ( + id int4 not null, + extraction_idExtraction int4 not null, + typeExtrait_idTypeExtrait int4 not null, + primary key (id) + ); + + create table Fraction ( + indice varchar(255) not null, + id int4 not null, + purification_idPurification int4 not null, + primary key (id) + ); + + create table Groupe ( + idGroupe int4 not null, + description text not null, + nom varchar(255) unique not null, + primary key (idGroupe) + ); + + create table Lot ( + idLot int4 not null, + complement text, + dateRecolte date not null, + echantillonColl bool not null, + echantillonIdent bool not null, + echantillonPhylo bool not null, + masseFraiche numeric(9, 4), + masseSeche numeric(9, 4), + ref varchar(255) unique not null, + campagne_idCampagne int4 not null, + createur_idPersonne int4 not null, + partie_idPartie int4, + specimenRef_idSpecimen int4 not null, + station_idStation int4 not null, + primary key (idLot) + ); + + create table LotGroupeDroits ( + droitExtrait bool, + droitPuri bool, + droitRecolte bool, + droitTestBio bool, + pk1_idLot int4 not null, + pk2_idGroupe int4 not null, + primary key (pk1_idLot, pk2_idGroupe) + ); + + create table LotPersonneDroits ( + droitExtrait bool, + droitPuri bool, + droitRecolte bool, + droitTestBio bool, + pk2_idPersonne int4 not null, + pk1_idLot int4 not null, + primary key (pk1_idLot, pk2_idPersonne) + ); + + create table MethodeExtraction ( + idMethodeExtraction int4 not null, + description text not null, + nom varchar(255) unique not null, + primary key (idMethodeExtraction) + ); + + create table MethodePurification ( + idMethodePurification int4 not null, + description text not null, + nom varchar(255) unique not null, + primary key (idMethodePurification) + ); + + create table MethodeTestBio ( + idMethodeTest int4 not null, + cible varchar(255) unique not null, + critereActivite varchar(255) not null, + description text not null, + domaine varchar(255) not null, + nom varchar(255) unique not null, + uniteResultat varchar(255) not null, + valeurMesuree varchar(255) not null, + primary key (idMethodeTest) + ); + + create table ParamMethoPuri ( + idParamMethoPuri int4 not null, + description text not null, + index int4 not null, + nom varchar(255) not null, + methodePurification_idMethodePurification int4 not null, + primary key (idParamMethoPuri) + ); + + create table ParamMethoPuriEffectif ( + idParamMethoPuriEffectif int4 not null, + valeur varchar(255), + param_idParamMethoPuri int4 not null, + purification_idPurification int4 not null, + primary key (idParamMethoPuriEffectif) + ); + + create table Partie ( + idPartie int4 not null, + nom varchar(255) unique not null, + primary key (idPartie) + ); + + create table Personne ( + idPersonne int4 not null, + adressePostale text not null, + codePays varchar(255) not null, + codePostal varchar(255) not null, + courriel varchar(255) unique not null, + fax varchar(255), + fonction varchar(255), + nom varchar(255) not null, + organisme varchar(255) not null, + prenom varchar(255) not null, + tel varchar(255), + ville varchar(255) not null, + primary key (idPersonne), + unique (nom, prenom) + ); + + create table Produit ( + id int4 not null, + masseObtenue numeric(9, 4), + ref varchar(255) unique not null, + primary key (id) + ); + + create table Purification ( + idPurification int4 not null, + complement text, + confidentiel bool not null, + date date not null, + dateConfidentialite date, + masseDepart numeric(9, 4), + ref varchar(255) unique not null, + createur_idPersonne int4 not null, + lotSource_idLot int4 not null, + manipulateur_idPersonne int4 not null, + methode_idMethodePurification int4 not null, + produit_id int4 not null, + primary key (idPurification) + ); + + create table ResultatTestBio ( + id int4 not null, + concMasse numeric(9, 4), + estActif bool, + produitTemoin varchar(255), + repere varchar(255) not null, + stade int4, + typeResultat int4 not null, + uniteConcMasse int4, + valeur numeric(9, 4), + erreur_idErreurTest int4, + produit_id int4, + testBio_idTestBio int4 not null, + typeExtraitSource_idTypeExtrait int4, + primary key (id) + ); + + create table Specimen ( + idSpecimen int4 not null, + complement text, + dateDepot date, + embranchement varchar(255) not null, + espece varchar(255), + famille varchar(255), + genre varchar(255), + lieuDepot varchar(255), + numDepot varchar(255), + ref varchar(255) unique not null, + sousEspece varchar(255), + typeOrganisme int4, + variete varchar(255), + createur_idPersonne int4 not null, + identificateur_idPersonne int4, + station_idStation int4, + primary key (idSpecimen) + ); + + create table Station ( + idStation int4 not null, + codePays varchar(255) not null, + complement text, + latitude varchar(255), + localite varchar(255), + longitude varchar(255), + nom varchar(255) unique not null, + referentiel int4, + createur_idPersonne int4 not null, + primary key (idStation) + ); + + create table TestBio ( + idTestBio int4 not null, + complement text, + concMasseDefaut numeric(9, 4), + confidentiel bool not null, + date date not null, + dateConfidentialite date, + organismeTesteur varchar(255) not null, + ref varchar(255) unique not null, + stadeDefaut int4, + uniteConcMasseDefaut int4, + createur_idPersonne int4 not null, + manipulateur_idPersonne int4 not null, + methode_idMethodeTest int4 not null, + primary key (idTestBio) + ); + + create table TypeDocument ( + idTypeDocument int4 not null, + description text not null, + domaine varchar(255), + estImage bool, + nom varchar(255) unique, + primary key (idTypeDocument), + unique (nom) + ); + + create table TypeExtrait ( + idTypeExtrait int4 not null, + description text not null, + initiales varchar(255) unique not null, + methodeExtraction_idMethodeExtraction int4 not null, + primary key (idTypeExtrait) + ); + + create table Utilisateur ( + dateValiditeCompte date, + estValide bool not null, + passwordHash varchar(255) not null, + typeDroit int4 not null, + idPersonne int4 not null, + groupe_idGroupe int4, + primary key (idPersonne) + ); + + alter table Campagne + add constraint FKFB835E7E822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table CampagneGroupeDroits + add constraint FK3AFBE26B905CBAAF + foreign key (pk2_idGroupe) + references Groupe; + + alter table CampagneGroupeDroits + add constraint FK3AFBE26B975C19C0 + foreign key (pk1_idCampagne) + references Campagne; + + alter table CampagnePersonneDroits + add constraint FK81782471975C19C0 + foreign key (pk1_idCampagne) + references Campagne; + + alter table CampagnePersonneDroits + add constraint FK81782471E49019FB + foreign key (pk2_idPersonne) + references Personne; + + alter table CampagnePersonneParticipant + add constraint FK72EE0469975C19C0 + foreign key (pk1_idCampagne) + references Campagne; + + alter table CampagnePersonneParticipant + add constraint FK72EE0469E49019FB + foreign key (pk2_idPersonne) + references Personne; + + alter table Campagne_Station + add constraint FK61A84053557B2C21 + foreign key (campagnes_idCampagne) + references Campagne; + + alter table Campagne_Station + add constraint FK61A840531AE541CD + foreign key (stations_idStation) + references Station; + + alter table Extraction + add constraint FKA39DBC077106663B + foreign key (manipulateur_idPersonne) + references Personne; + + alter table Extraction + add constraint FKA39DBC07822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table Extraction + add constraint FKA39DBC07F7C7FFC0 + foreign key (methode_idMethodeExtraction) + references MethodeExtraction; + + alter table Extraction + add constraint FKA39DBC07AE638899 + foreign key (lot_idLot) + references Lot; + + alter table Extrait + add constraint FK156B751B9547CE24 + foreign key (id) + references Produit; + + alter table Extrait + add constraint FK156B751B252FD5C9 + foreign key (typeExtrait_idTypeExtrait) + references TypeExtrait; + + alter table Extrait + add constraint FK156B751BE48A1121 + foreign key (extraction_idExtraction) + references Extraction; + + alter table Fraction + add constraint FKA14826229547CE24 + foreign key (id) + references Produit; + + alter table Fraction + add constraint FKA1482622859CB52B + foreign key (purification_idPurification) + references Purification; + + alter table Lot + add constraint FK12B311AA103F8 + foreign key (campagne_idCampagne) + references Campagne; + + alter table Lot + add constraint FK12B3168370809 + foreign key (partie_idPartie) + references Partie; + + alter table Lot + add constraint FK12B31822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table Lot + add constraint FK12B31DDF1E8FF + foreign key (specimenRef_idSpecimen) + references Specimen; + + alter table Lot + add constraint FK12B31CBED0F02 + foreign key (station_idStation) + references Station; + + alter table LotGroupeDroits + add constraint FK9AC53A9E905CBAAF + foreign key (pk2_idGroupe) + references Groupe; + + alter table LotGroupeDroits + add constraint FK9AC53A9E87AF0F1E + foreign key (pk1_idLot) + references Lot; + + alter table LotPersonneDroits + add constraint FK144C3BE4E49019FB + foreign key (pk2_idPersonne) + references Personne; + + alter table LotPersonneDroits + add constraint FK144C3BE487AF0F1E + foreign key (pk1_idLot) + references Lot; + + alter table ParamMethoPuri + add constraint FK1476479225A42085 + foreign key (methodePurification_idMethodePurification) + references MethodePurification; + + alter table ParamMethoPuriEffectif + add constraint FK8F7FA020B4F23AD1 + foreign key (param_idParamMethoPuri) + references ParamMethoPuri; + + alter table ParamMethoPuriEffectif + add constraint FK8F7FA020859CB52B + foreign key (purification_idPurification) + references Purification; + + create index courriel on Personne (courriel); + + alter table Purification + add constraint FKD1727611FE19187A + foreign key (produit_id) + references Produit; + + alter table Purification + add constraint FKD17276117106663B + foreign key (manipulateur_idPersonne) + references Personne; + + alter table Purification + add constraint FKD1727611822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table Purification + add constraint FKD1727611626E4F54 + foreign key (methode_idMethodePurification) + references MethodePurification; + + alter table Purification + add constraint FKD1727611C4943FB4 + foreign key (lotSource_idLot) + references Lot; + + create index typeResultat on ResultatTestBio (typeResultat); + + alter table ResultatTestBio + add constraint FK5DFD5BC6FE19187A + foreign key (produit_id) + references Produit; + + alter table ResultatTestBio + add constraint FK5DFD5BC65033BE68 + foreign key (testBio_idTestBio) + references TestBio; + + alter table ResultatTestBio + add constraint FK5DFD5BC64B1883E5 + foreign key (erreur_idErreurTest) + references ErreurTestBio; + + alter table ResultatTestBio + add constraint FK5DFD5BC6ABE26DE4 + foreign key (typeExtraitSource_idTypeExtrait) + references TypeExtrait; + + alter table Specimen + add constraint FK84B96728822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table Specimen + add constraint FK84B96728D2CE02CA + foreign key (identificateur_idPersonne) + references Personne; + + alter table Specimen + add constraint FK84B96728CBED0F02 + foreign key (station_idStation) + references Station; + + alter table Station + add constraint FKF2249914822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table TestBio + add constraint FKE6F36F67106663B + foreign key (manipulateur_idPersonne) + references Personne; + + alter table TestBio + add constraint FKE6F36F6822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table TestBio + add constraint FKE6F36F67DBA1C52 + foreign key (methode_idMethodeTest) + references MethodeTestBio; + + alter table TypeExtrait + add constraint FK40F3D8811D52927 + foreign key (methodeExtraction_idMethodeExtraction) + references MethodeExtraction; + + create index estValide on Utilisateur (estValide); + + create index typeDroit on Utilisateur (typeDroit); + + alter table Utilisateur + add constraint FK407FDB6370C95760 + foreign key (groupe_idGroupe) + references Groupe; + + alter table Utilisateur + add constraint FK407FDB63A1338A53 + foreign key (idPersonne) + references Personne; + + create sequence hibernate_sequence; + + + +COMMIT; Added: trunk/docker/cantharella-db/docker-entrypoint-initdb.d/sql/cantharella_schema_1.1.sql =================================================================== --- trunk/docker/cantharella-db/docker-entrypoint-initdb.d/sql/cantharella_schema_1.1.sql (rev 0) +++ trunk/docker/cantharella-db/docker-entrypoint-initdb.d/sql/cantharella_schema_1.1.sql 2017-08-03 01:32:51 UTC (rev 277) @@ -0,0 +1,727 @@ +--- +-- #%L +-- Cantharella :: Data +-- $Id: +-- $HeadURL: +-- %% +-- Copyright (C) 2009 - 2012 IRD (Institut de Recherche pour le Developpement) and by respective authors (see below) +-- %% +-- This program is free software: you can redistribute it and/or modify +-- it under the terms of the GNU Affero General Public License as published by +-- the Free Software Foundation, either version 3 of the License, or +-- (at your option) any later version. +-- +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +-- GNU General Public License for more details. +-- +-- You should have received a copy of the GNU Affero General Public License +-- along with this program. If not, see <http://www.gnu.org/licenses/>. +-- #L% +--- + +-- insert once connected to cantharella database with the user cantharella +BEGIN; + + create table Campagne ( + idCampagne int4 not null, + codePays varchar(255) not null, + complement text, + dateDeb date not null, + dateFin date not null, + mentionLegale text, + nom varchar(255) unique not null, + programme varchar(255), + createur_idPersonne int4 not null, + primary key (idCampagne) + ); + + create table CampagneGroupeDroits ( + droitExtrait bool, + droitPuri bool, + droitRecolte bool, + droitTestBio bool, + pk1_idCampagne int4 not null, + pk2_idGroupe int4 not null, + primary key (pk1_idCampagne, pk2_idGroupe) + ); + + create table CampagnePersonneDroits ( + droitExtrait bool, + droitPuri bool, + droitRecolte bool, + droitTestBio bool, + pk1_idCampagne int4 not null, + pk2_idPersonne int4 not null, + primary key (pk1_idCampagne, pk2_idPersonne) + ); + + create table CampagnePersonneParticipant ( + complement text, + pk1_idCampagne int4 not null, + pk2_idPersonne int4 not null, + primary key (pk1_idCampagne, pk2_idPersonne) + ); + + create table Campagne_Station ( + campagnes_idCampagne int4 not null, + stations_idStation int4 not null + ); + + create table ErreurTestBio ( + idErreurTest int4 not null, + description text not null, + nom varchar(255) not null, + primary key (idErreurTest), + unique (nom) + ); + + create table Extraction ( + idExtraction int4 not null, + complement text, + date date not null, + masseDepart numeric(9, 4), + ref varchar(255) unique not null, + createur_idPersonne int4 not null, + lot_idLot int4 not null, + manipulateur_idPersonne int4 not null, + methode_idMethodeExtraction int4 not null, + primary key (idExtraction) + ); + + create table Extrait ( + id int4 not null, + extraction_idExtraction int4 not null, + typeExtrait_idTypeExtrait int4 not null, + primary key (id) + ); + + create table Fraction ( + indice varchar(255) not null, + id int4 not null, + purification_idPurification int4 not null, + primary key (id) + ); + + create table Groupe ( + idGroupe int4 not null, + description text not null, + nom varchar(255) unique not null, + primary key (idGroupe) + ); + + create table Lot ( + idLot int4 not null, + complement text, + dateRecolte date not null, + echantillonColl bool not null, + echantillonIdent bool not null, + echantillonPhylo bool not null, + masseFraiche numeric(9, 4), + masseSeche numeric(9, 4), + ref varchar(255) unique not null, + campagne_idCampagne int4 not null, + createur_idPersonne int4 not null, + partie_idPartie int4, + specimenRef_idSpecimen int4 not null, + station_idStation int4 not null, + primary key (idLot) + ); + + create table LotGroupeDroits ( + droitExtrait bool, + droitPuri bool, + droitRecolte bool, + droitTestBio bool, + pk1_idLot int4 not null, + pk2_idGroupe int4 not null, + primary key (pk1_idLot, pk2_idGroupe) + ); + + create table LotPersonneDroits ( + droitExtrait bool, + droitPuri bool, + droitRecolte bool, + droitTestBio bool, + pk2_idPersonne int4 not null, + pk1_idLot int4 not null, + primary key (pk1_idLot, pk2_idPersonne) + ); + + create table MethodeExtraction ( + idMethodeExtraction int4 not null, + description text not null, + nom varchar(255) unique not null, + primary key (idMethodeExtraction) + ); + + create table MethodePurification ( + idMethodePurification int4 not null, + description text not null, + nom varchar(255) unique not null, + primary key (idMethodePurification) + ); + + create table MethodeTestBio ( + idMethodeTest int4 not null, + cible varchar(255) unique not null, + critereActivite varchar(255) not null, + description text not null, + domaine varchar(255) not null, + nom varchar(255) unique not null, + uniteResultat varchar(255) not null, + valeurMesuree varchar(255) not null, + primary key (idMethodeTest) + ); + + create table ParamMethoPuri ( + idParamMethoPuri int4 not null, + description text not null, + index int4 not null, + nom varchar(255) not null, + methodePurification_idMethodePurification int4 not null, + primary key (idParamMethoPuri) + ); + + create table ParamMethoPuriEffectif ( + idParamMethoPuriEffectif int4 not null, + valeur varchar(255), + param_idParamMethoPuri int4 not null, + purification_idPurification int4 not null, + primary key (idParamMethoPuriEffectif) + ); + + create table Partie ( + idPartie int4 not null, + nom varchar(255) unique not null, + primary key (idPartie) + ); + + create table Personne ( + idPersonne int4 not null, + adressePostale text not null, + codePays varchar(255) not null, + codePostal varchar(255) not null, + courriel varchar(255) unique not null, + fax varchar(255), + fonction varchar(255), + nom varchar(255) not null, + organisme varchar(255) not null, + prenom varchar(255) not null, + tel varchar(255), + ville varchar(255) not null, + primary key (idPersonne), + unique (nom, prenom) + ); + + create table Produit ( + id int4 not null, + masseObtenue numeric(9, 4), + ref varchar(255) unique not null, + primary key (id) + ); + + create table Purification ( + idPurification int4 not null, + complement text, + confidentiel bool not null, + date date not null, + dateConfidentialite date, + masseDepart numeric(9, 4), + ref varchar(255) unique not null, + createur_idPersonne int4 not null, + lotSource_idLot int4 not null, + manipulateur_idPersonne int4 not null, + methode_idMethodePurification int4 not null, + produit_id int4 not null, + primary key (idPurification) + ); + + create table ResultatTestBio ( + id int4 not null, + concMasse numeric(9, 4), + estActif bool, + produitTemoin varchar(255), + repere varchar(255) not null, + stade int4, + typeResultat int4 not null, + uniteConcMasse int4, + valeur numeric(9, 4), + erreur_idErreurTest int4, + produit_id int4, + testBio_idTestBio int4 not null, + typeExtraitSource_idTypeExtrait int4, + primary key (id) + ); + + create table Specimen ( + idSpecimen int4 not null, + complement text, + dateDepot date, + embranchement varchar(255) not null, + espece varchar(255), + famille varchar(255), + genre varchar(255), + lieuDepot varchar(255), + numDepot varchar(255), + ref varchar(255) unique not null, + sousEspece varchar(255), + typeOrganisme int4, + variete varchar(255), + createur_idPersonne int4 not null, + identificateur_idPersonne int4, + station_idStation int4, + primary key (idSpecimen) + ); + + create table Station ( + idStation int4 not null, + codePays varchar(255) not null, + complement text, + latitude varchar(255), + localite varchar(255), + longitude varchar(255), + nom varchar(255) unique not null, + referentiel int4, + createur_idPersonne int4 not null, + primary key (idStation) + ); + + create table TestBio ( + idTestBio int4 not null, + complement text, + concMasseDefaut numeric(9, 4), + confidentiel bool not null, + date date not null, + dateConfidentialite date, + organismeTesteur varchar(255) not null, + ref varchar(255) unique not null, + stadeDefaut int4, + uniteConcMasseDefaut int4, + createur_idPersonne int4 not null, + manipulateur_idPersonne int4 not null, + methode_idMethodeTest int4 not null, + primary key (idTestBio) + ); + + create table TypeExtrait ( + idTypeExtrait int4 not null, + description text not null, + initiales varchar(255) unique not null, + methodeExtraction_idMethodeExtraction int4 not null, + primary key (idTypeExtrait) + ); + + create table Utilisateur ( + dateValiditeCompte date, + estValide bool not null, + passwordHash varchar(255) not null, + typeDroit int4 not null, + idPersonne int4 not null, + groupe_idGroupe int4, + primary key (idPersonne) + ); + + alter table Campagne + add constraint FKFB835E7E822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table CampagneGroupeDroits + add constraint FK3AFBE26B905CBAAF + foreign key (pk2_idGroupe) + references Groupe; + + alter table CampagneGroupeDroits + add constraint FK3AFBE26B975C19C0 + foreign key (pk1_idCampagne) + references Campagne; + + alter table CampagnePersonneDroits + add constraint FK81782471975C19C0 + foreign key (pk1_idCampagne) + references Campagne; + + alter table CampagnePersonneDroits + add constraint FK81782471E49019FB + foreign key (pk2_idPersonne) + references Personne; + + alter table CampagnePersonneParticipant + add constraint FK72EE0469975C19C0 + foreign key (pk1_idCampagne) + references Campagne; + + alter table CampagnePersonneParticipant + add constraint FK72EE0469E49019FB + foreign key (pk2_idPersonne) + references Personne; + + alter table Campagne_Station + add constraint FK61A84053557B2C21 + foreign key (campagnes_idCampagne) + references Campagne; + + alter table Campagne_Station + add constraint FK61A840531AE541CD + foreign key (stations_idStation) + references Station; + + alter table Extraction + add constraint FKA39DBC077106663B + foreign key (manipulateur_idPersonne) + references Personne; + + alter table Extraction + add constraint FKA39DBC07822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table Extraction + add constraint FKA39DBC07F7C7FFC0 + foreign key (methode_idMethodeExtraction) + references MethodeExtraction; + + alter table Extraction + add constraint FKA39DBC07AE638899 + foreign key (lot_idLot) + references Lot; + + alter table Extrait + add constraint FK156B751B9547CE24 + foreign key (id) + references Produit; + + alter table Extrait + add constraint FK156B751B252FD5C9 + foreign key (typeExtrait_idTypeExtrait) + references TypeExtrait; + + alter table Extrait + add constraint FK156B751BE48A1121 + foreign key (extraction_idExtraction) + references Extraction; + + alter table Fraction + add constraint FKA14826229547CE24 + foreign key (id) + references Produit; + + alter table Fraction + add constraint FKA1482622859CB52B + foreign key (purification_idPurification) + references Purification; + + alter table Lot + add constraint FK12B311AA103F8 + foreign key (campagne_idCampagne) + references Campagne; + + alter table Lot + add constraint FK12B3168370809 + foreign key (partie_idPartie) + references Partie; + + alter table Lot + add constraint FK12B31822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table Lot + add constraint FK12B31DDF1E8FF + foreign key (specimenRef_idSpecimen) + references Specimen; + + alter table Lot + add constraint FK12B31CBED0F02 + foreign key (station_idStation) + references Station; + + alter table LotGroupeDroits + add constraint FK9AC53A9E905CBAAF + foreign key (pk2_idGroupe) + references Groupe; + + alter table LotGroupeDroits + add constraint FK9AC53A9E87AF0F1E + foreign key (pk1_idLot) + references Lot; + + alter table LotPersonneDroits + add constraint FK144C3BE4E49019FB + foreign key (pk2_idPersonne) + references Personne; + + alter table LotPersonneDroits + add constraint FK144C3BE487AF0F1E + foreign key (pk1_idLot) + references Lot; + + alter table ParamMethoPuri + add constraint FK1476479225A42085 + foreign key (methodePurification_idMethodePurification) + references MethodePurification; + + alter table ParamMethoPuriEffectif + add constraint FK8F7FA020B4F23AD1 + foreign key (param_idParamMethoPuri) + references ParamMethoPuri; + + alter table ParamMethoPuriEffectif + add constraint FK8F7FA020859CB52B + foreign key (purification_idPurification) + references Purification; + + create index courriel on Personne (courriel); + + alter table Purification + add constraint FKD1727611FE19187A + foreign key (produit_id) + references Produit; + + alter table Purification + add constraint FKD17276117106663B + foreign key (manipulateur_idPersonne) + references Personne; + + alter table Purification + add constraint FKD1727611822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table Purification + add constraint FKD1727611626E4F54 + foreign key (methode_idMethodePurification) + references MethodePurification; + + alter table Purification + add constraint FKD1727611C4943FB4 + foreign key (lotSource_idLot) + references Lot; + + create index typeResultat on ResultatTestBio (typeResultat); + + alter table ResultatTestBio + add constraint FK5DFD5BC6FE19187A + foreign key (produit_id) + references Produit; + + alter table ResultatTestBio + add constraint FK5DFD5BC65033BE68 + foreign key (testBio_idTestBio) + references TestBio; + + alter table ResultatTestBio + add constraint FK5DFD5BC64B1883E5 + foreign key (erreur_idErreurTest) + references ErreurTestBio; + + alter table ResultatTestBio + add constraint FK5DFD5BC6ABE26DE4 + foreign key (typeExtraitSource_idTypeExtrait) + references TypeExtrait; + + alter table Specimen + add constraint FK84B96728822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table Specimen + add constraint FK84B96728D2CE02CA + foreign key (identificateur_idPersonne) + references Personne; + + alter table Specimen + add constraint FK84B96728CBED0F02 + foreign key (station_idStation) + references Station; + + alter table Station + add constraint FKF2249914822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table TestBio + add constraint FKE6F36F67106663B + foreign key (manipulateur_idPersonne) + references Personne; + + alter table TestBio + add constraint FKE6F36F6822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table TestBio + add constraint FKE6F36F67DBA1C52 + foreign key (methode_idMethodeTest) + references MethodeTestBio; + + alter table TypeExtrait + add constraint FK40F3D8811D52927 + foreign key (methodeExtraction_idMethodeExtraction) + references MethodeExtraction; + + create index estValide on Utilisateur (estValide); + + create index typeDroit on Utilisateur (typeDroit); + + alter table Utilisateur + add constraint FK407FDB6370C95760 + foreign key (groupe_idGroupe) + references Groupe; + + alter table Utilisateur + add constraint FK407FDB63A1338A53 + foreign key (idPersonne) + references Personne; + + create sequence hibernate_sequence; + + -- Documents (16/11/2012) - update (25/03/2013) + create table TypeDocument ( + idTypeDocument int4 not null, + description text not null, + domaine varchar(60) not null, + nom varchar(60) not null unique, + primary key (idTypeDocument), + unique (nom) + ); + + -- Molecules (07/01/2013) + create table Molecule ( + idMolecule int4 not null, + complement text, + familleChimique varchar(60), + formuleBrute varchar(60) not null, + formuleDevMol text, + identifieePar varchar(60), + masseMolaire numeric(9, 4) check (masseMolaire<=99999 AND masseMolaire>=0), + nomCommun varchar(100), + nomIupca varchar(255), + nouvMolecul boolean not null, + publiOrigine text, + campagne_idCampagne int4, + createur_idPersonne int4 not null, + primary key (idMolecule) + ); + + create table MoleculeProvenance ( + id int4 not null, + pourcentage numeric(9, 4) check (pourcentage>=0 AND pourcentage<=100), + molecule_idMolecule int4 not null, + produit_id int4 not null, + primary key (id) + ); + + alter table Molecule + add constraint FKEC979EA61AA103F8 + foreign key (campagne_idCampagne) + references Campagne; + + alter table Molecule + add constraint FKEC979EA6822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table MoleculeProvenance + add constraint FK8B39E567F44F1B20 + foreign key (molecule_idMolecule) + references Molecule; + + alter table MoleculeProvenance + add constraint FK8B39E567FE19187A + foreign key (produit_id) + references Produit; + + create sequence molecule_sequence; + + -- Document (12/02/2013) + create table Document ( + idDocument int4 not null, + contrainteLegale varchar(100), + dateCreation date not null, + description text, + editeur varchar(100) not null, + fileContent bytea, + fileContentThumb bytea, + fileMimetype varchar(60), + fileName varchar(60) not null, + langue varchar(2), + titre varchar(100) not null, + ajoutePar_idPersonne int4 not null, + createur_idPersonne int4 not null, + typeDocument_idTypeDocument int4 not null, + specimen int4, + campagne int4, + personne int4, + extraction int4, + station int4, + purification int4, + lot int4, + resultatTestBio int4, + molecule int4, + primary key (idDocument) + ); + + alter table Document + add constraint FK3737353B2F46DB31 + foreign key (ajoutePar_idPersonne) + references Personne; + + alter table Document + add constraint FK3737353B822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table Document + add constraint FK3737353BBECBA92F + foreign key (typeDocument_idTypeDocument) + references TypeDocument; + + alter table Document + add constraint FK3737353B51427EB0 + foreign key (specimen) + references Specimen; + + alter table Document + add constraint FK3737353B3ED66D5C + foreign key (campagne) + references Campagne; + + alter table Document + add constraint FK3737353B8458C838 + foreign key (personne) + references Personne; + + alter table Document + add constraint FK3737353BD9E0906E + foreign key (extraction) + references Extraction; + + alter table Document + add constraint FK3737353BC591F5C8 + foreign key (station) + references Station; + + alter table Document + add constraint FK3737353B20A56C82 + foreign key (purification) + references Purification; + + alter table Document + add constraint FK3737353B767DEA02 + foreign key (lot) + references Lot; + + alter table Document + add constraint FK3737353B8923169C + foreign key (resultatTestBio) + references TestBio; + + alter table Document + add constraint FK3737353B20FEEDAC + foreign key (molecule) + references Molecule; + +COMMIT; Added: trunk/docker/cantharella-db/docker-entrypoint-initdb.d/sql/cantharella_schema_1.2.sql =================================================================== --- trunk/docker/cantharella-db/docker-entrypoint-initdb.d/sql/cantharella_schema_1.2.sql (rev 0) +++ trunk/docker/cantharella-db/docker-entrypoint-initdb.d/sql/cantharella_schema_1.2.sql 2017-08-03 01:32:51 UTC (rev 277) @@ -0,0 +1,780 @@ +--- +-- #%L +-- Cantharella :: Data +-- $Id: dev_update_1.1_to_1.2.sql 211 2013-04-30 07:31:44Z acheype $ +-- $HeadURL: http://svn.forge.codelutin.com/svn/cantharella/trunk/cantharella.data/src/ma... $ +-- %% +-- Copyright (C) 2014 IRD (Institut de Recherche pour le Developpement) and by respective authors (see below) +-- %% +-- This program is free software: you can redistribute it and/or modify +-- it under the terms of the GNU Affero General Public License as published by +-- the Free Software Foundation, either version 3 of the License, or +-- (at your option) any later version. +-- +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +-- GNU General Public License for more details. +-- +-- You should have received a copy of the GNU Affero General Public License +-- along with this program. If not, see <http://www.gnu.org/licenses/>. +-- #L% +--- + +-- insert once connected to cantharella database with the user cantharella +BEGIN; + + -- SCHEMA 1.1 + + create table Campagne ( + idCampagne int4 not null, + codePays varchar(255) not null, + complement text, + dateDeb date not null, + dateFin date not null, + mentionLegale text, + nom varchar(255) unique not null, + programme varchar(255), + createur_idPersonne int4 not null, + primary key (idCampagne) + ); + + create table CampagneGroupeDroits ( + droitExtrait bool, + droitPuri bool, + droitRecolte bool, + droitTestBio bool, + pk1_idCampagne int4 not null, + pk2_idGroupe int4 not null, + primary key (pk1_idCampagne, pk2_idGroupe) + ); + + create table CampagnePersonneDroits ( + droitExtrait bool, + droitPuri bool, + droitRecolte bool, + droitTestBio bool, + pk1_idCampagne int4 not null, + pk2_idPersonne int4 not null, + primary key (pk1_idCampagne, pk2_idPersonne) + ); + + create table CampagnePersonneParticipant ( + complement text, + pk1_idCampagne int4 not null, + pk2_idPersonne int4 not null, + primary key (pk1_idCampagne, pk2_idPersonne) + ); + + create table Campagne_Station ( + campagnes_idCampagne int4 not null, + stations_idStation int4 not null + ); + + create table ErreurTestBio ( + idErreurTest int4 not null, + description text not null, + nom varchar(255) not null, + primary key (idErreurTest), + unique (nom) + ); + + create table Extraction ( + idExtraction int4 not null, + complement text, + date date not null, + masseDepart numeric(9, 4), + ref varchar(255) unique not null, + createur_idPersonne int4 not null, + lot_idLot int4 not null, + manipulateur_idPersonne int4 not null, + methode_idMethodeExtraction int4 not null, + primary key (idExtraction) + ); + + create table Extrait ( + id int4 not null, + extraction_idExtraction int4 not null, + typeExtrait_idTypeExtrait int4 not null, + primary key (id) + ); + + create table Fraction ( + indice varchar(255) not null, + id int4 not null, + purification_idPurification int4 not null, + primary key (id) + ); + + create table Groupe ( + idGroupe int4 not null, + description text not null, + nom varchar(255) unique not null, + primary key (idGroupe) + ); + + create table Lot ( + idLot int4 not null, + complement text, + dateRecolte date not null, + echantillonColl bool not null, + echantillonIdent bool not null, + echantillonPhylo bool not null, + masseFraiche numeric(9, 4), + masseSeche numeric(9, 4), + ref varchar(255) unique not null, + campagne_idCampagne int4 not null, + createur_idPersonne int4 not null, + partie_idPartie int4, + specimenRef_idSpecimen int4 not null, + station_idStation int4 not null, + primary key (idLot) + ); + + create table LotGroupeDroits ( + droitExtrait bool, + droitPuri bool, + droitRecolte bool, + droitTestBio bool, + pk1_idLot int4 not null, + pk2_idGroupe int4 not null, + primary key (pk1_idLot, pk2_idGroupe) + ); + + create table LotPersonneDroits ( + droitExtrait bool, + droitPuri bool, + droitRecolte bool, + droitTestBio bool, + pk2_idPersonne int4 not null, + pk1_idLot int4 not null, + primary key (pk1_idLot, pk2_idPersonne) + ); + + create table MethodeExtraction ( + idMethodeExtraction int4 not null, + description text not null, + nom varchar(255) unique not null, + primary key (idMethodeExtraction) + ); + + create table MethodePurification ( + idMethodePurification int4 not null, + description text not null, + nom varchar(255) unique not null, + primary key (idMethodePurification) + ); + + create table MethodeTestBio ( + idMethodeTest int4 not null, + cible varchar(255) unique not null, + critereActivite varchar(255) not null, + description text not null, + domaine varchar(255) not null, + nom varchar(255) unique not null, + uniteResultat varchar(255) not null, + valeurMesuree varchar(255) not null, + primary key (idMethodeTest) + ); + + create table ParamMethoPuri ( + idParamMethoPuri int4 not null, + description text not null, + index int4 not null, + nom varchar(255) not null, + methodePurification_idMethodePurification int4 not null, + primary key (idParamMethoPuri) + ); + + create table ParamMethoPuriEffectif ( + idParamMethoPuriEffectif int4 not null, + valeur varchar(255), + param_idParamMethoPuri int4 not null, + purification_idPurification int4 not null, + primary key (idParamMethoPuriEffectif) + ); + + create table Partie ( + idPartie int4 not null, + nom varchar(255) unique not null, + primary key (idPartie) + ); + + create table Personne ( + idPersonne int4 not null, + adressePostale text not null, + codePays varchar(255) not null, + codePostal varchar(255) not null, + courriel varchar(255) unique not null, + fax varchar(255), + fonction varchar(255), + nom varchar(255) not null, + organisme varchar(255) not null, + prenom varchar(255) not null, + tel varchar(255), + ville varchar(255) not null, + primary key (idPersonne), + unique (nom, prenom) + ); + + create table Produit ( + id int4 not null, + masseObtenue numeric(9, 4), + ref varchar(255) unique not null, + primary key (id) + ); + + create table Purification ( + idPurification int4 not null, + complement text, + confidentiel bool not null, + date date not null, + dateConfidentialite date, + masseDepart numeric(9, 4), + ref varchar(255) unique not null, + createur_idPersonne int4 not null, + lotSource_idLot int4 not null, + manipulateur_idPersonne int4 not null, + methode_idMethodePurification int4 not null, + produit_id int4 not null, + primary key (idPurification) + ); + + create table ResultatTestBio ( + id int4 not null, + concMasse numeric(9, 4), + estActif bool, + produitTemoin varchar(255), + repere varchar(255) not null, + stade int4, + typeResultat int4 not null, + uniteConcMasse int4, + valeur numeric(9, 4), + erreur_idErreurTest int4, + produit_id int4, + testBio_idTestBio int4 not null, + typeExtraitSource_idTypeExtrait int4, + primary key (id) + ); + + create table Specimen ( + idSpecimen int4 not null, + complement text, + dateDepot date, + embranchement varchar(255) not null, + espece varchar(255), + famille varchar(255), + genre varchar(255), + lieuDepot varchar(255), + numDepot varchar(255), + ref varchar(255) unique not null, + sousEspece varchar(255), + typeOrganisme int4, + variete varchar(255), + createur_idPersonne int4 not null, + identificateur_idPersonne int4, + station_idStation int4, + primary key (idSpecimen) + ); + + create table Station ( + idStation int4 not null, + codePays varchar(255) not null, + complement text, + latitude varchar(255), + localite varchar(255), + longitude varchar(255), + nom varchar(255) unique not null, + referentiel int4, + createur_idPersonne int4 not null, + primary key (idStation) + ); + + create table TestBio ( + idTestBio int4 not null, + complement text, + concMasseDefaut numeric(9, 4), + confidentiel bool not null, + date date not null, + dateConfidentialite date, + organismeTesteur varchar(255) not null, + ref varchar(255) unique not null, + stadeDefaut int4, + uniteConcMasseDefaut int4, + createur_idPersonne int4 not null, + manipulateur_idPersonne int4 not null, + methode_idMethodeTest int4 not null, + primary key (idTestBio) + ); + + create table TypeExtrait ( + idTypeExtrait int4 not null, + description text not null, + initiales varchar(255) unique not null, + methodeExtraction_idMethodeExtraction int4 not null, + primary key (idTypeExtrait) + ); + + create table Utilisateur ( + dateValiditeCompte date, + estValide bool not null, + passwordHash varchar(255) not null, + typeDroit int4 not null, + idPersonne int4 not null, + groupe_idGroupe int4, + primary key (idPersonne) + ); + + alter table Campagne + add constraint FKFB835E7E822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table CampagneGroupeDroits + add constraint FK3AFBE26B905CBAAF + foreign key (pk2_idGroupe) + references Groupe; + + alter table CampagneGroupeDroits + add constraint FK3AFBE26B975C19C0 + foreign key (pk1_idCampagne) + references Campagne; + + alter table CampagnePersonneDroits + add constraint FK81782471975C19C0 + foreign key (pk1_idCampagne) + references Campagne; + + alter table CampagnePersonneDroits + add constraint FK81782471E49019FB + foreign key (pk2_idPersonne) + references Personne; + + alter table CampagnePersonneParticipant + add constraint FK72EE0469975C19C0 + foreign key (pk1_idCampagne) + references Campagne; + + alter table CampagnePersonneParticipant + add constraint FK72EE0469E49019FB + foreign key (pk2_idPersonne) + references Personne; + + alter table Campagne_Station + add constraint FK61A84053557B2C21 + foreign key (campagnes_idCampagne) + references Campagne; + + alter table Campagne_Station + add constraint FK61A840531AE541CD + foreign key (stations_idStation) + references Station; + + alter table Extraction + add constraint FKA39DBC077106663B + foreign key (manipulateur_idPersonne) + references Personne; + + alter table Extraction + add constraint FKA39DBC07822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table Extraction + add constraint FKA39DBC07F7C7FFC0 + foreign key (methode_idMethodeExtraction) + references MethodeExtraction; + + alter table Extraction + add constraint FKA39DBC07AE638899 + foreign key (lot_idLot) + references Lot; + + alter table Extrait + add constraint FK156B751B9547CE24 + foreign key (id) + references Produit; + + alter table Extrait + add constraint FK156B751B252FD5C9 + foreign key (typeExtrait_idTypeExtrait) + references TypeExtrait; + + alter table Extrait + add constraint FK156B751BE48A1121 + foreign key (extraction_idExtraction) + references Extraction; + + alter table Fraction + add constraint FKA14826229547CE24 + foreign key (id) + references Produit; + + alter table Fraction + add constraint FKA1482622859CB52B + foreign key (purification_idPurification) + references Purification; + + alter table Lot + add constraint FK12B311AA103F8 + foreign key (campagne_idCampagne) + references Campagne; + + alter table Lot + add constraint FK12B3168370809 + foreign key (partie_idPartie) + references Partie; + + alter table Lot + add constraint FK12B31822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table Lot + add constraint FK12B31DDF1E8FF + foreign key (specimenRef_idSpecimen) + references Specimen; + + alter table Lot + add constraint FK12B31CBED0F02 + foreign key (station_idStation) + references Station; + + alter table LotGroupeDroits + add constraint FK9AC53A9E905CBAAF + foreign key (pk2_idGroupe) + references Groupe; + + alter table LotGroupeDroits + add constraint FK9AC53A9E87AF0F1E + foreign key (pk1_idLot) + references Lot; + + alter table LotPersonneDroits + add constraint FK144C3BE4E49019FB + foreign key (pk2_idPersonne) + references Personne; + + alter table LotPersonneDroits + add constraint FK144C3BE487AF0F1E + foreign key (pk1_idLot) + references Lot; + + alter table ParamMethoPuri + add constraint FK1476479225A42085 + foreign key (methodePurification_idMethodePurification) + references MethodePurification; + + alter table ParamMethoPuriEffectif + add constraint FK8F7FA020B4F23AD1 + foreign key (param_idParamMethoPuri) + references ParamMethoPuri; + + alter table ParamMethoPuriEffectif + add constraint FK8F7FA020859CB52B + foreign key (purification_idPurification) + references Purification; + + create index courriel on Personne (courriel); + + alter table Purification + add constraint FKD1727611FE19187A + foreign key (produit_id) + references Produit; + + alter table Purification + add constraint FKD17276117106663B + foreign key (manipulateur_idPersonne) + references Personne; + + alter table Purification + add constraint FKD1727611822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table Purification + add constraint FKD1727611626E4F54 + foreign key (methode_idMethodePurification) + references MethodePurification; + + alter table Purification + add constraint FKD1727611C4943FB4 + foreign key (lotSource_idLot) + references Lot; + + create index typeResultat on ResultatTestBio (typeResultat); + + alter table ResultatTestBio + add constraint FK5DFD5BC6FE19187A + foreign key (produit_id) + references Produit; + + alter table ResultatTestBio + add constraint FK5DFD5BC65033BE68 + foreign key (testBio_idTestBio) + references TestBio; + + alter table ResultatTestBio + add constraint FK5DFD5BC64B1883E5 + foreign key (erreur_idErreurTest) + references ErreurTestBio; + + alter table ResultatTestBio + add constraint FK5DFD5BC6ABE26DE4 + foreign key (typeExtraitSource_idTypeExtrait) + references TypeExtrait; + + alter table Specimen + add constraint FK84B96728822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table Specimen + add constraint FK84B96728D2CE02CA + foreign key (identificateur_idPersonne) + references Personne; + + alter table Specimen + add constraint FK84B96728CBED0F02 + foreign key (station_idStation) + references Station; + + alter table Station + add constraint FKF2249914822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table TestBio + add constraint FKE6F36F67106663B + foreign key (manipulateur_idPersonne) + references Personne; + + alter table TestBio + add constraint FKE6F36F6822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table TestBio + add constraint FKE6F36F67DBA1C52 + foreign key (methode_idMethodeTest) + references MethodeTestBio; + + alter table TypeExtrait + add constraint FK40F3D8811D52927 + foreign key (methodeExtraction_idMethodeExtraction) + references MethodeExtraction; + + create index estValide on Utilisateur (estValide); + + create index typeDroit on Utilisateur (typeDroit); + + alter table Utilisateur + add constraint FK407FDB6370C95760 + foreign key (groupe_idGroupe) + references Groupe; + + alter table Utilisateur + add constraint FK407FDB63A1338A53 + foreign key (idPersonne) + references Personne; + + create sequence hibernate_sequence; + + -- Documents (16/11/2012) - update (25/03/2013) + create table TypeDocument ( + idTypeDocument int4 not null, + description text not null, + domaine varchar(60) not null, + nom varchar(60) not null unique, + primary key (idTypeDocument), + unique (nom) + ); + + -- Molecules (07/01/2013) + create table Molecule ( + idMolecule int4 not null, + complement text, + familleChimique varchar(60), + formuleBrute varchar(60) not null, + formuleDevMol text, + identifieePar varchar(60), + masseMolaire numeric(9, 4) check (masseMolaire<=99999 AND masseMolaire>=0), + nomCommun varchar(100), + nomIupca varchar(255), + nouvMolecul boolean not null, + publiOrigine text, + campagne_idCampagne int4, + createur_idPersonne int4 not null, + primary key (idMolecule) + ); + + create table MoleculeProvenance ( + id int4 not null, + pourcentage numeric(9, 4) check (pourcentage>=0 AND pourcentage<=100), + molecule_idMolecule int4 not null, + produit_id int4 not null, + primary key (id) + ); + + alter table Molecule + add constraint FKEC979EA61AA103F8 + foreign key (campagne_idCampagne) + references Campagne; + + alter table Molecule + add constraint FKEC979EA6822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table MoleculeProvenance + add constraint FK8B39E567F44F1B20 + foreign key (molecule_idMolecule) + references Molecule; + + alter table MoleculeProvenance + add constraint FK8B39E567FE19187A + foreign key (produit_id) + references Produit; + + create sequence molecule_sequence; + + -- Document (12/02/2013) + create table Document ( + idDocument int4 not null, + contrainteLegale varchar(100), + dateCreation date not null, + description text, + editeur varchar(100) not null, + fileContent bytea, + fileContentThumb bytea, + fileMimetype varchar(60), + fileName varchar(60) not null, + langue varchar(2), + titre varchar(100) not null, + ajoutePar_idPersonne int4 not null, + createur_idPersonne int4 not null, + typeDocument_idTypeDocument int4 not null, + specimen int4, + campagne int4, + personne int4, + extraction int4, + station int4, + purification int4, + lot int4, + resultatTestBio int4, + molecule int4, + primary key (idDocument) + ); + + alter table Document + add constraint FK3737353B2F46DB31 + foreign key (ajoutePar_idPersonne) + references Personne; + + alter table Document + add constraint FK3737353B822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table Document + add constraint FK3737353BBECBA92F + foreign key (typeDocument_idTypeDocument) + references TypeDocument; + + alter table Document + add constraint FK3737353B51427EB0 + foreign key (specimen) + references Specimen; + + alter table Document + add constraint FK3737353B3ED66D5C + foreign key (campagne) + references Campagne; + + alter table Document + add constraint FK3737353B8458C838 + foreign key (personne) + references Personne; + + alter table Document + add constraint FK3737353BD9E0906E + foreign key (extraction) + references Extraction; + + alter table Document + add constraint FK3737353BC591F5C8 + foreign key (station) + references Station; + + alter table Document + add constraint FK3737353B20A56C82 + foreign key (purification) + references Purification; + + alter table Document + add constraint FK3737353B767DEA02 + foreign key (lot) + references Lot; + + alter table Document + add constraint FK3737353B8923169C + foreign key (resultatTestBio) + references TestBio; + + alter table Document + add constraint FK3737353B20FEEDAC + foreign key (molecule) + references Molecule; + + -- END OF SCHEMA 1.1 + + -- MIGRATION FROM SCHEMA 1.1 TO 1.2 + + -- DocumentContent (05/05/2014) + create table DocumentContent ( + idDocumentContent int4 not null, + fileContent bytea not null, + tmpDocument int4, + tmpDocumentThumb int4, + primary key (idDocumentContent) + ); + + -- move all file content to new table + insert into DocumentContent (idDocumentContent, fileContent, tmpDocument) + SELECT nextval('hibernate_sequence'), fileContent, idDocument from document; + -- move all thumbnail content to new table + insert into DocumentContent (idDocumentContent, fileContent, tmpDocumentThumb) + SELECT nextval('hibernate_sequence'), fileContentThumb, idDocument FROM document where fileContentThumb is not NULL; + + -- link documentcontent to document + alter table Document add column fileContent_idDocumentContent int4; + alter table Document add column fileContentThumb_idDocumentContent int4; + + update Document SET fileContent_idDocumentContent = subquery.idDocumentContent + FROM (SELECT idDocumentContent, tmpDocument FROM DocumentContent) AS subquery + WHERE subquery.tmpDocument = Document.idDocument; + update Document SET fileContentThumb_idDocumentContent = subquery.idDocumentContent + FROM (SELECT idDocumentContent, tmpDocumentThumb FROM DocumentContent) AS subquery + WHERE subquery.tmpDocumentThumb = Document.idDocument; + + alter table Document + alter column fileContent_idDocumentContent set not null; + + alter table Document + add constraint FK_3l4n85rsmw88bewfx57o7gg9g + foreign key (fileContent_idDocumentContent) + references DocumentContent; + + alter table Document + add constraint FK_mphot3xs24tg4u5na1bgo1h85 + foreign key (fileContentThumb_idDocumentContent) + references DocumentContent; + + -- clean + alter table Document drop column fileContent; + alter table Document drop column fileContentThumb; + alter table DocumentContent drop column tmpDocument; + alter table DocumentContent drop column tmpDocumentThumb; + + -- END OF MIGRATION FROM SCHEMA 1.1 TO 1.2 +COMMIT; \ No newline at end of file Added: trunk/docker/cantharella-db/docker-entrypoint-initdb.d/sql/create_db.sql =================================================================== --- trunk/docker/cantharella-db/docker-entrypoint-initdb.d/sql/create_db.sql (rev 0) +++ trunk/docker/cantharella-db/docker-entrypoint-initdb.d/sql/create_db.sql 2017-08-03 01:32:51 UTC (rev 277) @@ -0,0 +1,29 @@ +--- +-- #%L +-- Cantharella :: Data +-- $Id: create_db.sql 133 2013-02-19 11:02:25Z acheype $ +-- $HeadURL: https://svn.codelutin.com/cantharella/trunk/cantharella.data/src/main/sql/cr... $ +-- %% +-- Copyright (C) 2009 - 2012 IRD (Institut de Recherche pour le Developpement) and by respective authors (see below) +-- %% +-- This program is free software: you can redistribute it and/or modify +-- it under the terms of the GNU Affero General Public License as published by +-- the Free Software Foundation, either version 3 of the License, or +-- (at your option) any later version. +-- +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +-- GNU General Public License for more details. +-- +-- You should have received a copy of the GNU Affero General Public License +-- along with this program. If not, see <http://www.gnu.org/licenses/>. +-- #L% +--- +CREATE ROLE cantharella LOGIN PASSWORD 'ctrl4'; +CREATE DATABASE cantharella WITH ENCODING='UTF8' OWNER=cantharella; +GRANT ALL ON DATABASE cantharella TO cantharella; +\connect cantharella +ALTER SCHEMA public OWNER TO cantharella; +GRANT ALL ON SCHEMA public TO cantharella; +\q Added: trunk/docker/cantharella-db/docker-entrypoint-initdb.d/sql/dev_update_1.0_to_1.1.sql =================================================================== --- trunk/docker/cantharella-db/docker-entrypoint-initdb.d/sql/dev_update_1.0_to_1.1.sql (rev 0) +++ trunk/docker/cantharella-db/docker-entrypoint-initdb.d/sql/dev_update_1.0_to_1.1.sql 2017-08-03 01:32:51 UTC (rev 277) @@ -0,0 +1,174 @@ +--- +-- #%L +-- Cantharella :: Data +-- $Id: dev_update_1.0_to_1.1.sql 211 2013-04-30 07:31:44Z acheype $ +-- $HeadURL: https://svn.codelutin.com/cantharella/trunk/cantharella.data/src/main/sql/de... $ +-- %% +-- Copyright (C) 2013 IRD (Institut de Recherche pour le Developpement) and by respective authors (see below) +-- %% +-- This program is free software: you can redistribute it and/or modify +-- it under the terms of the GNU Affero General Public License as published by +-- the Free Software Foundation, either version 3 of the License, or +-- (at your option) any later version. +-- +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +-- GNU General Public License for more details. +-- +-- You should have received a copy of the GNU Affero General Public License +-- along with this program. If not, see <http://www.gnu.org/licenses/>. +-- #L% +--- +-- Mise à jour à appliquer à la base de données +-- pour les modifications effectuées pendant le développement + + +BEGIN; +-- Documents (16/11/2012) - update (25/03/2013) + create table TypeDocument ( + idTypeDocument int4 not null, + description text not null, + domaine varchar(60) not null, + nom varchar(60) not null unique, + primary key (idTypeDocument), + unique (nom) + ); + +-- Molecules (07/01/2013) + create table Molecule ( + idMolecule int4 not null, + complement text, + familleChimique varchar(60), + formuleBrute varchar(60) not null, + formuleDevMol text, + identifieePar varchar(60), + masseMolaire numeric(9, 4) check (masseMolaire<=99999 AND masseMolaire>=0), + nomCommun varchar(100), + nomIupca varchar(255), + nouvMolecul boolean not null, + publiOrigine text, + campagne_idCampagne int4, + createur_idPersonne int4 not null, + primary key (idMolecule) + ); + + create table MoleculeProvenance ( + id int4 not null, + pourcentage numeric(9, 4) check (pourcentage>=0 AND pourcentage<=100), + molecule_idMolecule int4 not null, + produit_id int4 not null, + primary key (id) + ); + + alter table Molecule + add constraint FKEC979EA61AA103F8 + foreign key (campagne_idCampagne) + references Campagne; + + alter table Molecule + add constraint FKEC979EA6822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table MoleculeProvenance + add constraint FK8B39E567F44F1B20 + foreign key (molecule_idMolecule) + references Molecule; + + alter table MoleculeProvenance + add constraint FK8B39E567FE19187A + foreign key (produit_id) + references Produit; + + create sequence molecule_sequence; + +-- Document (12/02/2013) + create table Document ( + idDocument int4 not null, + contrainteLegale varchar(100), + dateCreation date not null, + description text, + editeur varchar(100) not null, + fileContent bytea, + fileContentThumb bytea, + fileMimetype varchar(60), + fileName varchar(60) not null, + langue varchar(2), + titre varchar(100) not null, + ajoutePar_idPersonne int4 not null, + createur_idPersonne int4 not null, + typeDocument_idTypeDocument int4 not null, + specimen int4, + campagne int4, + personne int4, + extraction int4, + station int4, + purification int4, + lot int4, + resultatTestBio int4, + molecule int4, + primary key (idDocument) + ); + + alter table Document + add constraint FK3737353B2F46DB31 + foreign key (ajoutePar_idPersonne) + references Personne; + + alter table Document + add constraint FK3737353B822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table Document + add constraint FK3737353BBECBA92F + foreign key (typeDocument_idTypeDocument) + references TypeDocument; + + alter table Document + add constraint FK3737353B51427EB0 + foreign key (specimen) + references Specimen; + + alter table Document + add constraint FK3737353B3ED66D5C + foreign key (campagne) + references Campagne; + + alter table Document + add constraint FK3737353B8458C838 + foreign key (personne) + references Personne; + + alter table Document + add constraint FK3737353BD9E0906E + foreign key (extraction) + references Extraction; + + alter table Document + add constraint FK3737353BC591F5C8 + foreign key (station) + references Station; + + alter table Document + add constraint FK3737353B20A56C82 + foreign key (purification) + references Purification; + + alter table Document + add constraint FK3737353B767DEA02 + foreign key (lot) + references Lot; + + alter table Document + add constraint FK3737353B8923169C + foreign key (resultatTestBio) + references TestBio; + + alter table Document + add constraint FK3737353B20FEEDAC + foreign key (molecule) + references Molecule; + +COMMIT; \ No newline at end of file Added: trunk/docker/cantharella-web/Dockerfile =================================================================== --- trunk/docker/cantharella-web/Dockerfile (rev 0) +++ trunk/docker/cantharella-web/Dockerfile 2017-08-03 01:32:51 UTC (rev 277) @@ -0,0 +1,5 @@ +FROM tomcat:6.0.53-jre7 +MAINTAINER Adrien Cheype <adrien.cheype@ird.fr> + +RUN rm -rf /usr/local/tomcat/webapps/* +COPY ./cantharella.war /usr/local/tomcat/webapps/ROOT.war \ No newline at end of file Added: trunk/docker/docker-compose.yml =================================================================== --- trunk/docker/docker-compose.yml (rev 0) +++ trunk/docker/docker-compose.yml 2017-08-03 01:32:51 UTC (rev 277) @@ -0,0 +1,21 @@ +version: '3' +services: + tomcat: + container_name: cantharella-web + build: ./cantharella-web + image: acheype/cantharella-web + ports: + - "80:8080" + links: + - "postgres" + + postgres: + container_name: cantharella-db + build: ./cantharella-db + image: acheype/cantharella-db + volumes: + - /data/postgresql:/var/lib/postgresql/data + ports: + - "5432:5432" + environment: + - POSTGRES_PASSWORD=CHANGE_PASSWORD \ No newline at end of file Modified: trunk/pom.xml =================================================================== --- trunk/pom.xml 2014-05-07 08:30:33 UTC (rev 276) +++ trunk/pom.xml 2017-08-03 01:32:51 UTC (rev 277) @@ -10,7 +10,7 @@ <groupId>nc.ird</groupId> <artifactId>cantharella</artifactId> - <version>1.2.1-SNAPSHOT</version> + <version>1.2.2</version> <packaging>pom</packaging> <name>Cantharella</name> <description>Pharmacochemical database of natural substances</description> Modified: trunk/sql/cantharella_schema_1.2.sql =================================================================== --- trunk/cantharella.data/src/main/sql/cantharella_schema_1.2.sql 2014-05-07 08:30:33 UTC (rev 276) +++ trunk/sql/cantharella_schema_1.2.sql 2017-08-03 01:32:51 UTC (rev 277) @@ -23,6 +23,713 @@ -- insert once connected to cantharella database with the user cantharella BEGIN; + + -- SCHEMA 1.1 + + create table Campagne ( + idCampagne int4 not null, + codePays varchar(255) not null, + complement text, + dateDeb date not null, + dateFin date not null, + mentionLegale text, + nom varchar(255) unique not null, + programme varchar(255), + createur_idPersonne int4 not null, + primary key (idCampagne) + ); + + create table CampagneGroupeDroits ( + droitExtrait bool, + droitPuri bool, + droitRecolte bool, + droitTestBio bool, + pk1_idCampagne int4 not null, + pk2_idGroupe int4 not null, + primary key (pk1_idCampagne, pk2_idGroupe) + ); + + create table CampagnePersonneDroits ( + droitExtrait bool, + droitPuri bool, + droitRecolte bool, + droitTestBio bool, + pk1_idCampagne int4 not null, + pk2_idPersonne int4 not null, + primary key (pk1_idCampagne, pk2_idPersonne) + ); + + create table CampagnePersonneParticipant ( + complement text, + pk1_idCampagne int4 not null, + pk2_idPersonne int4 not null, + primary key (pk1_idCampagne, pk2_idPersonne) + ); + + create table Campagne_Station ( + campagnes_idCampagne int4 not null, + stations_idStation int4 not null + ); + + create table ErreurTestBio ( + idErreurTest int4 not null, + description text not null, + nom varchar(255) not null, + primary key (idErreurTest), + unique (nom) + ); + + create table Extraction ( + idExtraction int4 not null, + complement text, + date date not null, + masseDepart numeric(9, 4), + ref varchar(255) unique not null, + createur_idPersonne int4 not null, + lot_idLot int4 not null, + manipulateur_idPersonne int4 not null, + methode_idMethodeExtraction int4 not null, + primary key (idExtraction) + ); + + create table Extrait ( + id int4 not null, + extraction_idExtraction int4 not null, + typeExtrait_idTypeExtrait int4 not null, + primary key (id) + ); + + create table Fraction ( + indice varchar(255) not null, + id int4 not null, + purification_idPurification int4 not null, + primary key (id) + ); + + create table Groupe ( + idGroupe int4 not null, + description text not null, + nom varchar(255) unique not null, + primary key (idGroupe) + ); + + create table Lot ( + idLot int4 not null, + complement text, + dateRecolte date not null, + echantillonColl bool not null, + echantillonIdent bool not null, + echantillonPhylo bool not null, + masseFraiche numeric(9, 4), + masseSeche numeric(9, 4), + ref varchar(255) unique not null, + campagne_idCampagne int4 not null, + createur_idPersonne int4 not null, + partie_idPartie int4, + specimenRef_idSpecimen int4 not null, + station_idStation int4 not null, + primary key (idLot) + ); + + create table LotGroupeDroits ( + droitExtrait bool, + droitPuri bool, + droitRecolte bool, + droitTestBio bool, + pk1_idLot int4 not null, + pk2_idGroupe int4 not null, + primary key (pk1_idLot, pk2_idGroupe) + ); + + create table LotPersonneDroits ( + droitExtrait bool, + droitPuri bool, + droitRecolte bool, + droitTestBio bool, + pk2_idPersonne int4 not null, + pk1_idLot int4 not null, + primary key (pk1_idLot, pk2_idPersonne) + ); + + create table MethodeExtraction ( + idMethodeExtraction int4 not null, + description text not null, + nom varchar(255) unique not null, + primary key (idMethodeExtraction) + ); + + create table MethodePurification ( + idMethodePurification int4 not null, + description text not null, + nom varchar(255) unique not null, + primary key (idMethodePurification) + ); + + create table MethodeTestBio ( + idMethodeTest int4 not null, + cible varchar(255) unique not null, + critereActivite varchar(255) not null, + description text not null, + domaine varchar(255) not null, + nom varchar(255) unique not null, + uniteResultat varchar(255) not null, + valeurMesuree varchar(255) not null, + primary key (idMethodeTest) + ); + + create table ParamMethoPuri ( + idParamMethoPuri int4 not null, + description text not null, + index int4 not null, + nom varchar(255) not null, + methodePurification_idMethodePurification int4 not null, + primary key (idParamMethoPuri) + ); + + create table ParamMethoPuriEffectif ( + idParamMethoPuriEffectif int4 not null, + valeur varchar(255), + param_idParamMethoPuri int4 not null, + purification_idPurification int4 not null, + primary key (idParamMethoPuriEffectif) + ); + + create table Partie ( + idPartie int4 not null, + nom varchar(255) unique not null, + primary key (idPartie) + ); + + create table Personne ( + idPersonne int4 not null, + adressePostale text not null, + codePays varchar(255) not null, + codePostal varchar(255) not null, + courriel varchar(255) unique not null, + fax varchar(255), + fonction varchar(255), + nom varchar(255) not null, + organisme varchar(255) not null, + prenom varchar(255) not null, + tel varchar(255), + ville varchar(255) not null, + primary key (idPersonne), + unique (nom, prenom) + ); + + create table Produit ( + id int4 not null, + masseObtenue numeric(9, 4), + ref varchar(255) unique not null, + primary key (id) + ); + + create table Purification ( + idPurification int4 not null, + complement text, + confidentiel bool not null, + date date not null, + dateConfidentialite date, + masseDepart numeric(9, 4), + ref varchar(255) unique not null, + createur_idPersonne int4 not null, + lotSource_idLot int4 not null, + manipulateur_idPersonne int4 not null, + methode_idMethodePurification int4 not null, + produit_id int4 not null, + primary key (idPurification) + ); + + create table ResultatTestBio ( + id int4 not null, + concMasse numeric(9, 4), + estActif bool, + produitTemoin varchar(255), + repere varchar(255) not null, + stade int4, + typeResultat int4 not null, + uniteConcMasse int4, + valeur numeric(9, 4), + erreur_idErreurTest int4, + produit_id int4, + testBio_idTestBio int4 not null, + typeExtraitSource_idTypeExtrait int4, + primary key (id) + ); + + create table Specimen ( + idSpecimen int4 not null, + complement text, + dateDepot date, + embranchement varchar(255) not null, + espece varchar(255), + famille varchar(255), + genre varchar(255), + lieuDepot varchar(255), + numDepot varchar(255), + ref varchar(255) unique not null, + sousEspece varchar(255), + typeOrganisme int4, + variete varchar(255), + createur_idPersonne int4 not null, + identificateur_idPersonne int4, + station_idStation int4, + primary key (idSpecimen) + ); + + create table Station ( + idStation int4 not null, + codePays varchar(255) not null, + complement text, + latitude varchar(255), + localite varchar(255), + longitude varchar(255), + nom varchar(255) unique not null, + referentiel int4, + createur_idPersonne int4 not null, + primary key (idStation) + ); + + create table TestBio ( + idTestBio int4 not null, + complement text, + concMasseDefaut numeric(9, 4), + confidentiel bool not null, + date date not null, + dateConfidentialite date, + organismeTesteur varchar(255) not null, + ref varchar(255) unique not null, + stadeDefaut int4, + uniteConcMasseDefaut int4, + createur_idPersonne int4 not null, + manipulateur_idPersonne int4 not null, + methode_idMethodeTest int4 not null, + primary key (idTestBio) + ); + + create table TypeExtrait ( + idTypeExtrait int4 not null, + description text not null, + initiales varchar(255) unique not null, + methodeExtraction_idMethodeExtraction int4 not null, + primary key (idTypeExtrait) + ); + + create table Utilisateur ( + dateValiditeCompte date, + estValide bool not null, + passwordHash varchar(255) not null, + typeDroit int4 not null, + idPersonne int4 not null, + groupe_idGroupe int4, + primary key (idPersonne) + ); + + alter table Campagne + add constraint FKFB835E7E822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table CampagneGroupeDroits + add constraint FK3AFBE26B905CBAAF + foreign key (pk2_idGroupe) + references Groupe; + + alter table CampagneGroupeDroits + add constraint FK3AFBE26B975C19C0 + foreign key (pk1_idCampagne) + references Campagne; + + alter table CampagnePersonneDroits + add constraint FK81782471975C19C0 + foreign key (pk1_idCampagne) + references Campagne; + + alter table CampagnePersonneDroits + add constraint FK81782471E49019FB + foreign key (pk2_idPersonne) + references Personne; + + alter table CampagnePersonneParticipant + add constraint FK72EE0469975C19C0 + foreign key (pk1_idCampagne) + references Campagne; + + alter table CampagnePersonneParticipant + add constraint FK72EE0469E49019FB + foreign key (pk2_idPersonne) + references Personne; + + alter table Campagne_Station + add constraint FK61A84053557B2C21 + foreign key (campagnes_idCampagne) + references Campagne; + + alter table Campagne_Station + add constraint FK61A840531AE541CD + foreign key (stations_idStation) + references Station; + + alter table Extraction + add constraint FKA39DBC077106663B + foreign key (manipulateur_idPersonne) + references Personne; + + alter table Extraction + add constraint FKA39DBC07822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table Extraction + add constraint FKA39DBC07F7C7FFC0 + foreign key (methode_idMethodeExtraction) + references MethodeExtraction; + + alter table Extraction + add constraint FKA39DBC07AE638899 + foreign key (lot_idLot) + references Lot; + + alter table Extrait + add constraint FK156B751B9547CE24 + foreign key (id) + references Produit; + + alter table Extrait + add constraint FK156B751B252FD5C9 + foreign key (typeExtrait_idTypeExtrait) + references TypeExtrait; + + alter table Extrait + add constraint FK156B751BE48A1121 + foreign key (extraction_idExtraction) + references Extraction; + + alter table Fraction + add constraint FKA14826229547CE24 + foreign key (id) + references Produit; + + alter table Fraction + add constraint FKA1482622859CB52B + foreign key (purification_idPurification) + references Purification; + + alter table Lot + add constraint FK12B311AA103F8 + foreign key (campagne_idCampagne) + references Campagne; + + alter table Lot + add constraint FK12B3168370809 + foreign key (partie_idPartie) + references Partie; + + alter table Lot + add constraint FK12B31822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table Lot + add constraint FK12B31DDF1E8FF + foreign key (specimenRef_idSpecimen) + references Specimen; + + alter table Lot + add constraint FK12B31CBED0F02 + foreign key (station_idStation) + references Station; + + alter table LotGroupeDroits + add constraint FK9AC53A9E905CBAAF + foreign key (pk2_idGroupe) + references Groupe; + + alter table LotGroupeDroits + add constraint FK9AC53A9E87AF0F1E + foreign key (pk1_idLot) + references Lot; + + alter table LotPersonneDroits + add constraint FK144C3BE4E49019FB + foreign key (pk2_idPersonne) + references Personne; + + alter table LotPersonneDroits + add constraint FK144C3BE487AF0F1E + foreign key (pk1_idLot) + references Lot; + + alter table ParamMethoPuri + add constraint FK1476479225A42085 + foreign key (methodePurification_idMethodePurification) + references MethodePurification; + + alter table ParamMethoPuriEffectif + add constraint FK8F7FA020B4F23AD1 + foreign key (param_idParamMethoPuri) + references ParamMethoPuri; + + alter table ParamMethoPuriEffectif + add constraint FK8F7FA020859CB52B + foreign key (purification_idPurification) + references Purification; + + create index courriel on Personne (courriel); + + alter table Purification + add constraint FKD1727611FE19187A + foreign key (produit_id) + references Produit; + + alter table Purification + add constraint FKD17276117106663B + foreign key (manipulateur_idPersonne) + references Personne; + + alter table Purification + add constraint FKD1727611822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table Purification + add constraint FKD1727611626E4F54 + foreign key (methode_idMethodePurification) + references MethodePurification; + + alter table Purification + add constraint FKD1727611C4943FB4 + foreign key (lotSource_idLot) + references Lot; + + create index typeResultat on ResultatTestBio (typeResultat); + + alter table ResultatTestBio + add constraint FK5DFD5BC6FE19187A + foreign key (produit_id) + references Produit; + + alter table ResultatTestBio + add constraint FK5DFD5BC65033BE68 + foreign key (testBio_idTestBio) + references TestBio; + + alter table ResultatTestBio + add constraint FK5DFD5BC64B1883E5 + foreign key (erreur_idErreurTest) + references ErreurTestBio; + + alter table ResultatTestBio + add constraint FK5DFD5BC6ABE26DE4 + foreign key (typeExtraitSource_idTypeExtrait) + references TypeExtrait; + + alter table Specimen + add constraint FK84B96728822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table Specimen + add constraint FK84B96728D2CE02CA + foreign key (identificateur_idPersonne) + references Personne; + + alter table Specimen + add constraint FK84B96728CBED0F02 + foreign key (station_idStation) + references Station; + + alter table Station + add constraint FKF2249914822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table TestBio + add constraint FKE6F36F67106663B + foreign key (manipulateur_idPersonne) + references Personne; + + alter table TestBio + add constraint FKE6F36F6822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table TestBio + add constraint FKE6F36F67DBA1C52 + foreign key (methode_idMethodeTest) + references MethodeTestBio; + + alter table TypeExtrait + add constraint FK40F3D8811D52927 + foreign key (methodeExtraction_idMethodeExtraction) + references MethodeExtraction; + + create index estValide on Utilisateur (estValide); + + create index typeDroit on Utilisateur (typeDroit); + + alter table Utilisateur + add constraint FK407FDB6370C95760 + foreign key (groupe_idGroupe) + references Groupe; + + alter table Utilisateur + add constraint FK407FDB63A1338A53 + foreign key (idPersonne) + references Personne; + + create sequence hibernate_sequence; + + -- Documents (16/11/2012) - update (25/03/2013) + create table TypeDocument ( + idTypeDocument int4 not null, + description text not null, + domaine varchar(60) not null, + nom varchar(60) not null unique, + primary key (idTypeDocument), + unique (nom) + ); + + -- Molecules (07/01/2013) + create table Molecule ( + idMolecule int4 not null, + complement text, + familleChimique varchar(60), + formuleBrute varchar(60) not null, + formuleDevMol text, + identifieePar varchar(60), + masseMolaire numeric(9, 4) check (masseMolaire<=99999 AND masseMolaire>=0), + nomCommun varchar(100), + nomIupca varchar(255), + nouvMolecul boolean not null, + publiOrigine text, + campagne_idCampagne int4, + createur_idPersonne int4 not null, + primary key (idMolecule) + ); + + create table MoleculeProvenance ( + id int4 not null, + pourcentage numeric(9, 4) check (pourcentage>=0 AND pourcentage<=100), + molecule_idMolecule int4 not null, + produit_id int4 not null, + primary key (id) + ); + + alter table Molecule + add constraint FKEC979EA61AA103F8 + foreign key (campagne_idCampagne) + references Campagne; + + alter table Molecule + add constraint FKEC979EA6822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table MoleculeProvenance + add constraint FK8B39E567F44F1B20 + foreign key (molecule_idMolecule) + references Molecule; + + alter table MoleculeProvenance + add constraint FK8B39E567FE19187A + foreign key (produit_id) + references Produit; + + create sequence molecule_sequence; + + -- Document (12/02/2013) + create table Document ( + idDocument int4 not null, + contrainteLegale varchar(100), + dateCreation date not null, + description text, + editeur varchar(100) not null, + fileContent bytea, + fileContentThumb bytea, + fileMimetype varchar(60), + fileName varchar(60) not null, + langue varchar(2), + titre varchar(100) not null, + ajoutePar_idPersonne int4 not null, + createur_idPersonne int4 not null, + typeDocument_idTypeDocument int4 not null, + specimen int4, + campagne int4, + personne int4, + extraction int4, + station int4, + purification int4, + lot int4, + resultatTestBio int4, + molecule int4, + primary key (idDocument) + ); + + alter table Document + add constraint FK3737353B2F46DB31 + foreign key (ajoutePar_idPersonne) + references Personne; + + alter table Document + add constraint FK3737353B822055B9 + foreign key (createur_idPersonne) + references Personne; + + alter table Document + add constraint FK3737353BBECBA92F + foreign key (typeDocument_idTypeDocument) + references TypeDocument; + + alter table Document + add constraint FK3737353B51427EB0 + foreign key (specimen) + references Specimen; + + alter table Document + add constraint FK3737353B3ED66D5C + foreign key (campagne) + references Campagne; + + alter table Document + add constraint FK3737353B8458C838 + foreign key (personne) + references Personne; + + alter table Document + add constraint FK3737353BD9E0906E + foreign key (extraction) + references Extraction; + + alter table Document + add constraint FK3737353BC591F5C8 + foreign key (station) + references Station; + + alter table Document + add constraint FK3737353B20A56C82 + foreign key (purification) + references Purification; + + alter table Document + add constraint FK3737353B767DEA02 + foreign key (lot) + references Lot; + + alter table Document + add constraint FK3737353B8923169C + foreign key (resultatTestBio) + references TestBio; + + alter table Document + add constraint FK3737353B20FEEDAC + foreign key (molecule) + references Molecule; + + -- END OF SCHEMA 1.1 + + -- MIGRATION FROM SCHEMA 1.1 TO 1.2 + -- DocumentContent (05/05/2014) create table DocumentContent ( idDocumentContent int4 not null, @@ -68,4 +775,6 @@ alter table Document drop column fileContentThumb; alter table DocumentContent drop column tmpDocument; alter table DocumentContent drop column tmpDocumentThumb; + + -- END OF MIGRATION FROM SCHEMA 1.1 TO 1.2 COMMIT; \ No newline at end of file Deleted: trunk/sql/init.sql =================================================================== --- trunk/cantharella.data/src/main/sql/init.sql 2014-05-07 08:30:33 UTC (rev 276) +++ trunk/sql/init.sql 2017-08-03 01:32:51 UTC (rev 277) @@ -1,29 +0,0 @@ ---- --- #%L --- Cantharella :: Data --- $Id$ --- $HeadURL$ --- %% --- Copyright (C) 2009 - 2012 IRD (Institut de Recherche pour le Developpement) and by respective authors (see below) --- %% --- This program is free software: you can redistribute it and/or modify --- it under the terms of the GNU Affero General Public License as published by --- the Free Software Foundation, either version 3 of the License, or --- (at your option) any later version. --- --- This program is distributed in the hope that it will be useful, --- but WITHOUT ANY WARRANTY; without even the implied warranty of --- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the --- GNU General Public License for more details. --- --- You should have received a copy of the GNU Affero General Public License --- along with this program. If not, see <http://www.gnu.org/licenses/>. --- #L% ---- -CREATE ROLE cantharella LOGIN PASSWORD 'cantharella'; -CREATE DATABASE cantharella WITH ENCODING='UTF8' OWNER=cantharella; -GRANT ALL ON DATABASE cantharella TO cantharella; -\connect cantharella -ALTER SCHEMA public OWNER TO cantharella; -GRANT ALL ON SCHEMA public TO cantharella; -\q \ No newline at end of file Modified: trunk/src/site/rst/index.rst =================================================================== --- trunk/src/site/rst/index.rst 2014-05-07 08:30:33 UTC (rev 276) +++ trunk/src/site/rst/index.rst 2017-08-03 01:32:51 UTC (rev 277) @@ -47,15 +47,15 @@ Screenshots ----------- -.. figure:: images/Home.png +.. figure:: images/Home.jpg Home -.. figure:: images/Station.png +.. figure:: images/Station.jpg Location form -.. figure:: images/Extraction.png +.. figure:: images/Extraction.jpg Extraction form