r211 - trunk/cantharella.data/src/main/sql - Cantharella-commits

30 Apr 2013

Author: acheype
Date: 2013-04-30 09:31:44 +0200 (Tue, 30 Apr 2013)
New Revision: 211

Url: http://forge.codelutin.com/projects/cantharella/repository/revisions/211

Log:
updates sql scripts for 1.1 release

Added:
   trunk/cantharella.data/src/main/sql/cantharella_schema_1.1.sql
   trunk/cantharella.data/src/main/sql/dev_update_1.0_to_1.1.sql
Removed:
   trunk/cantharella.data/src/main/sql/dev_update.sql
Modified:
   trunk/cantharella.data/src/main/sql/cantharella_schema_1.0.sql
   trunk/cantharella.data/src/main/sql/init.sql

Modified: trunk/cantharella.data/src/main/sql/cantharella_schema_1.0.sql
===================================================================

--- trunk/cantharella.data/src/main/sql/cantharella_schema_1.0.sql	2013-04-24 07:50:29 UTC (rev 210)
+++ trunk/cantharella.data/src/main/sql/cantharella_schema_1.0.sql	2013-04-30 07:31:44 UTC (rev 211)
@@ -20,8 +20,8 @@
 -- 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 (
@@ -587,5 +587,7 @@
         references Personne;
 
     create sequence hibernate_sequence;
+
     
+    
 COMMIT;

Copied: trunk/cantharella.data/src/main/sql/cantharella_schema_1.1.sql (from rev 210, trunk/cantharella.data/src/main/sql/cantharella_schema_1.0.sql)
===================================================================
--- trunk/cantharella.data/src/main/sql/cantharella_schema_1.1.sql	                        (rev 0)
+++ trunk/cantharella.data/src/main/sql/cantharella_schema_1.1.sql	2013-04-30 07:31:44 UTC (rev 211)
@@ -0,0 +1,737 @@
+---
+-- #%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 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;
+    
+    -- 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;

Deleted: trunk/cantharella.data/src/main/sql/dev_update.sql
===================================================================
--- trunk/cantharella.data/src/main/sql/dev_update.sql	2013-04-24 07:50:29 UTC (rev 210)
+++ trunk/cantharella.data/src/main/sql/dev_update.sql	2013-04-30 07:31:44 UTC (rev 211)
@@ -1,174 +0,0 @@
----
--- #%L
--- Cantharella :: Data
--- $Id$
--- $HeadURL$
--- %%
--- 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

Copied: trunk/cantharella.data/src/main/sql/dev_update_1.0_to_1.1.sql (from rev 210, trunk/cantharella.data/src/main/sql/dev_update.sql)
===================================================================
--- trunk/cantharella.data/src/main/sql/dev_update_1.0_to_1.1.sql	                        (rev 0)
+++ trunk/cantharella.data/src/main/sql/dev_update_1.0_to_1.1.sql	2013-04-30 07:31:44 UTC (rev 211)
@@ -0,0 +1,174 @@
+---
+-- #%L
+-- Cantharella :: Data
+-- $Id$
+-- $HeadURL$
+-- %%
+-- 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

Modified: trunk/cantharella.data/src/main/sql/init.sql
===================================================================
--- trunk/cantharella.data/src/main/sql/init.sql	2013-04-24 07:50:29 UTC (rev 210)
+++ trunk/cantharella.data/src/main/sql/init.sql	2013-04-30 07:31:44 UTC (rev 211)
@@ -20,16 +20,10 @@
 -- along with this program.  If not, see <http://www.gnu.org/licenses/>.
 -- #L%
 ---
-CREATE ROLE cantharella LOGIN PASSWORD 'ctrl4';
+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
-
--- To generate the DB schema, run the Maven command:
--- mvn hibernate3:hbm2ddl
-
--- Caution : the NOT NULL constraints are not generated
--- Go to src/main/resources/commons/sql for production schema scripts
+\q
\ No newline at end of file

    

r211 - trunk/cantharella.data/src/main/sql

acheype＠users.forge.codelutin.com

tags

participants (1)