Blame | Last modification | View Log | RSS feed
#+---------------------------------------------------------------------------------------------------------+#| Copyright (C) 2005 Tela Botanica (accueil@tela-botanica.org) |#+---------------------------------------------------------------------------------------------------------+#| This file is part of Herbier. |#| |#| Foobar is free software; you can redistribute it and/or modify it under the terms of the GNU General |#| Public License as published by the Free Software Foundation; either version 2 of the License, or |#| (at your option) any later version. |#| |#| Foobar 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 General Public License along with Foobar; if not, write to |#| the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA |#+---------------------------------------------------------------------------------------------------------+# CVS : $Id: herbiers.sql,v 1.1 2005-11-23 10:32:32 jp_milcent Exp $## Base de données Herbier## Script de création de la structure de la base de données Herbier.##@package Herbier#@subpackage Sql# Auteur original :#@author Alexandre GRANIER <alexandre@tela-botanica.org>#Autres auteurs :#@author Aucun#@copyright Tela-Botanica 2000-2005#@version $Revision: 1.1 $ $Date: 2005-11-23 10:32:32 $# +------------------------------------------------------------------------------------------------------+## Structure de la table `EFLORE_DROIT`#CREATE TABLE `EFLORE_DROIT` (`ED_ID_DROIT` int(11) NOT NULL default '0',`ED_INTITULE_DROIT` varchar(50) NOT NULL default '',`ED_DESCRIPTION_DROIT` varchar(255) NOT NULL default '',PRIMARY KEY (`ED_ID_DROIT`),UNIQUE KEY `EFLORE_DROIT_PK` (`ED_ID_DROIT`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;## Contenu de la table `EFLORE_DROIT`#INSERT INTO `EFLORE_DROIT` VALUES (1, 'Super-administrateur', 'Droits : gestion, validation, modification, ajout & consultation. Appliqué par défaut à : tous les projets.');INSERT INTO `EFLORE_DROIT` VALUES (2, 'Administrateur', 'Droits : gestion, validation, modification, ajout & consultation. Appliqué par défaut à : un projet donné.');INSERT INTO `EFLORE_DROIT` VALUES (3, 'Modérateur', 'Droits : validation & consultation. Appliqué par défaut à : l\'ensemble du contenu d\'un projet.');INSERT INTO `EFLORE_DROIT` VALUES (4, 'Contributeur', 'Droits : modification, ajout & consultation. Appliqué par défaut à : l\'ensemble du contenu d\'un projet.');INSERT INTO `EFLORE_DROIT` VALUES (5, 'Utilisateur', 'Droits : consultation. Appliqué par défaut à : l\'ensemble du contenu d\'un projet.');# --------------------------------------------------------## Structure de la table `EFLORE_DROIT_POSSEDER`#CREATE TABLE `EFLORE_DROIT_POSSEDER` (`EDP_ID_UTILISATEUR` int(11) NOT NULL default '0',`EDP_ID_PROJET` int(11) NOT NULL default '0',`EDP_ID_DROIT` int(11) NOT NULL default '0',PRIMARY KEY (`EDP_ID_UTILISATEUR`,`EDP_ID_DROIT`,`EDP_ID_PROJET`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;## Contenu de la table `EFLORE_DROIT_POSSEDER`#INSERT INTO `EFLORE_DROIT_POSSEDER` VALUES (5, 1, 2);INSERT INTO `EFLORE_DROIT_POSSEDER` VALUES (18, 1, 1);INSERT INTO `EFLORE_DROIT_POSSEDER` VALUES (19, 1, 1);INSERT INTO `EFLORE_DROIT_POSSEDER` VALUES (29, 1, 2);INSERT INTO `EFLORE_DROIT_POSSEDER` VALUES (68, 1, 1);INSERT INTO `EFLORE_DROIT_POSSEDER` VALUES (97, 1, 1);INSERT INTO `EFLORE_DROIT_POSSEDER` VALUES (210, 1, 2);INSERT INTO `EFLORE_DROIT_POSSEDER` VALUES (253, 1, 2);INSERT INTO `EFLORE_DROIT_POSSEDER` VALUES (415, 1, 2);INSERT INTO `EFLORE_DROIT_POSSEDER` VALUES (423, 1, 1);INSERT INTO `EFLORE_DROIT_POSSEDER` VALUES (551, 1, 2);INSERT INTO `EFLORE_DROIT_POSSEDER` VALUES (871, 1, 2);INSERT INTO `EFLORE_DROIT_POSSEDER` VALUES (920, 1, 2);INSERT INTO `EFLORE_DROIT_POSSEDER` VALUES (937, 1, 2);INSERT INTO `EFLORE_DROIT_POSSEDER` VALUES (1084, 1, 2);INSERT INTO `EFLORE_DROIT_POSSEDER` VALUES (1237, 1, 2);INSERT INTO `EFLORE_DROIT_POSSEDER` VALUES (1815, 1, 2);INSERT INTO `EFLORE_DROIT_POSSEDER` VALUES (3036, 1, 2);INSERT INTO `EFLORE_DROIT_POSSEDER` VALUES (3567, 1, 1);INSERT INTO `EFLORE_DROIT_POSSEDER` VALUES (4077, 1, 2);# --------------------------------------------------------## Structure de la table `EFLORE_PROJET`#CREATE TABLE `EFLORE_PROJET` (`EPR_ID_PROJET` int(11) NOT NULL default '0',`EPR_CE_TYPE_PROJET_ORIGINE` int(11) NOT NULL default '0',`EPR_CE_OUVRAGE_SOURCE` int(11) default NULL,`EPR_INTITULE_PROJET` varchar(255) NOT NULL default '',`EPR_ABREVIATION_PROJET` varchar(40) default NULL,`EPR_DESCRIPTION_PROJET` varchar(255) default NULL,`EPR_LIEN_WEB` varchar(255) default NULL,`EPR_NOTES_PROJET` text,PRIMARY KEY (`EPR_ID_PROJET`),UNIQUE KEY `EFLORE_PROJET_PK` (`EPR_ID_PROJET`),KEY `POSSEDER_TYPE_PROJET_FK` (`EPR_CE_TYPE_PROJET_ORIGINE`),KEY `ETRE_BASE_SUR_OUVRAGE_FK` (`EPR_CE_OUVRAGE_SOURCE`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;## Contenu de la table `EFLORE_PROJET`#INSERT INTO `EFLORE_PROJET` VALUES (1, 0, NULL, 'Inventaire des herbiers de France', 'IHF', 'Réalisation d\'un inventaire de toutes les collections d\'herbiers présentes sur le territoire.', NULL, NULL);## Structure de la table `HERBIERS_ADMINISTRER`#CREATE TABLE `HERBIERS_ADMINISTRER` (`HA_ID_ANNUAIRE` int(10) unsigned default NULL,`HA_ID_ORG` int(10) unsigned default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;# --------------------------------------------------------## Structure de la table `HERBIERS_A_UN_TYPE`#CREATE TABLE `HERBIERS_A_UN_TYPE` (`ID_INDIC` int(11) NOT NULL default '0',`ID_TYPE` int(11) NOT NULL default '0',PRIMARY KEY (`ID_INDIC`,`ID_TYPE`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;# --------------------------------------------------------## Structure de la table `HERBIERS_COLLECTION`#CREATE TABLE `HERBIERS_COLLECTION` (`ID` int(11) NOT NULL auto_increment,`PARENT_ID` int(11) default NULL,`LEVEL` varchar(255) default NULL,`COLLECTION_CODE` varchar(64) default NULL,`NOM_COLLECTION` varchar(255) NOT NULL default '',`DESCRIPTION` text,`STATUT` tinyint(3) unsigned NOT NULL default '1',`URL` varchar(255) default NULL,`INCLUDED_TYPE` tinyint(4) default '0',`STRENGTH` text,`PURPOSE` text,`NUM_ORGANISATION` int(11) default NULL,`NUM_SPECIMENS` int(11) default NULL,`NUM_SPECIES` int(11) default NULL,`DOC_STATE` text,`PERCENT_DATABASED` smallint(6) default NULL,`BIOCASE_URL` varchar(255) default NULL,`ACCESS_RESTRICTION` text,`USAGE_RESTRICTION` text,`COLLECTION_ACTIVITY` varchar(255) default NULL,`COLLECTION_FOCUS` varchar(255) default NULL,`NOTES` text,`COLLECTION_CLASS` tinyint(3) unsigned NOT NULL default '1',`COLLECTEURS` text,`DATE_DEBUT` date default '0000-00-00',`DATE_FIN` date default '0000-00-00',`DATE_DEBUT_CARAC` tinyint(3) unsigned NOT NULL default '1',`DATE_FIN_CARAC` tinyint(3) unsigned NOT NULL default '1',`PREC_SPECIMENS` tinyint(3) unsigned NOT NULL default '3',`PREC_SPECIES` tinyint(3) unsigned NOT NULL default '3',`ETAT_SPECIMENS` enum('1','2','3','4') NOT NULL default '1',`ETAT_CLASSEMENT` enum('1','2','3','4') NOT NULL default '1',`ETAT_PRESENTATION` enum('1','2','3','4') NOT NULL default '1',`MODE_CLASSEMENT` text,PRIMARY KEY (`ID`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;# --------------------------------------------------------## Structure de la table `HERBIERS_COLLECTIONS`#CREATE TABLE `HERBIERS_COLLECTIONS` (`ID_COLLECTIONS` int(11) NOT NULL default '0',`ID` int(11) NOT NULL default '0',`NAME` varchar(255) default NULL,PRIMARY KEY (`ID_COLLECTIONS`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;# --------------------------------------------------------## Structure de la table `HERBIERS_COLLECTION_CLASS`#CREATE TABLE `HERBIERS_COLLECTION_CLASS` (`HCC_ID` tinyint(3) unsigned NOT NULL default '0',`HCC_LABEL` varchar(255) default NULL,PRIMARY KEY (`HCC_ID`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;# --------------------------------------------------------## Structure de la table `HERBIERS_COLL_STATUT`#CREATE TABLE `HERBIERS_COLL_STATUT` (`ID_COLL_STATUT` tinyint(3) unsigned NOT NULL auto_increment,`LABEL` varchar(255) NOT NULL default '',PRIMARY KEY (`ID_COLL_STATUT`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;# --------------------------------------------------------## Structure de la table `HERBIERS_COMMON_NAME`#CREATE TABLE `HERBIERS_COMMON_NAME` (`ID_COMMON_NAME` int(11) NOT NULL default '0',`ID` int(11) NOT NULL default '0',`NAME` varchar(255) default NULL,`SOURCE` varchar(255) default NULL,PRIMARY KEY (`ID_COMMON_NAME`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;# --------------------------------------------------------## Structure de la table `HERBIERS_COORDONNE`#CREATE TABLE `HERBIERS_COORDONNE` (`HC_ID_COORDINATEUR` int(10) unsigned NOT NULL default '0',`HC_ID_REDACTEUR` int(10) unsigned NOT NULL default '0',PRIMARY KEY (`HC_ID_COORDINATEUR`,`HC_ID_REDACTEUR`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;# --------------------------------------------------------## Structure de la table `HERBIERS_DATE_DEBUT_CARAC`#CREATE TABLE `HERBIERS_DATE_DEBUT_CARAC` (`ID_DATE_CARAC` tinyint(3) unsigned NOT NULL default '0',`LABEL_DATE_DEBUT_CARAC` varchar(255) default NULL,PRIMARY KEY (`ID_DATE_CARAC`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;# --------------------------------------------------------## Structure de la table `HERBIERS_DATE_FIN_CARAC`#CREATE TABLE `HERBIERS_DATE_FIN_CARAC` (`ID_DATE_CARAC` tinyint(3) unsigned NOT NULL default '0',`LABEL_DATE_FIN_CARAC` varchar(255) default NULL,PRIMARY KEY (`ID_DATE_CARAC`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;# --------------------------------------------------------## Structure de la table `HERBIERS_DENOMBREMENT_CARAC_SC`#CREATE TABLE `HERBIERS_DENOMBREMENT_CARAC_SC` (`HDC_ID` tinyint(3) unsigned NOT NULL default '0',`HDC_LABEL` varchar(255) default NULL,PRIMARY KEY (`HDC_ID`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;# --------------------------------------------------------## Structure de la table `HERBIERS_DENOMBREMENT_CARAC_SP`#CREATE TABLE `HERBIERS_DENOMBREMENT_CARAC_SP` (`HDC_ID` tinyint(3) unsigned NOT NULL default '0',`HDC_LABEL` varchar(255) default NULL,PRIMARY KEY (`HDC_ID`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;# --------------------------------------------------------## Structure de la table `HERBIERS_ETAT_LABEL`#CREATE TABLE `HERBIERS_ETAT_LABEL` (`HEL_ID` tinyint(3) unsigned NOT NULL default '0',`HEL_LABEL` varchar(255) default NULL,PRIMARY KEY (`HEL_ID`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;# --------------------------------------------------------## Structure de la table `HERBIERS_INDIC`#CREATE TABLE `HERBIERS_INDIC` (`ID_INDIC` int(11) NOT NULL auto_increment,`ID` int(11) NOT NULL default '0',`REM_INDIC` text,`MAJ_INDIC` int(11) default NULL,`TXT_INDIC` text,PRIMARY KEY (`ID_INDIC`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;# --------------------------------------------------------## Structure de la table `HERBIERS_INDIC_HISTORIQUE`#CREATE TABLE `HERBIERS_INDIC_HISTORIQUE` (`ID_INDIC_HIST` int(10) unsigned NOT NULL auto_increment,`ID_INDIC` int(10) unsigned NOT NULL default '0',`DATE_INDIC` int(10) unsigned NOT NULL default '0',`ID_INDICATEUR` int(10) unsigned NOT NULL default '0',PRIMARY KEY (`ID_INDIC_HIST`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;# --------------------------------------------------------## Structure de la table `HERBIERS_ONT_COLLECTEURS`#CREATE TABLE `HERBIERS_ONT_COLLECTEURS` (`HOC_ID_COLL` int(10) unsigned default NULL,`HOC_ID_IBN` int(10) unsigned default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;# --------------------------------------------------------## Structure de la table `HERBIERS_ORGANISATION`#CREATE TABLE `HERBIERS_ORGANISATION` (`ID_ORG` int(11) NOT NULL auto_increment,`NUM_COLLECTION` int(11) default NULL,`ADRESS_TEXT` text,`INSTITUTION_NAME` text,`ADRESS_LINE` text,`TOWN` varchar(255) default NULL,`REGION` varchar(255) default NULL,`COUNTRY_CODE` char(3) default NULL,`ZIP` varchar(16) default NULL,`TEL` varchar(64) default NULL,`FAX` varchar(64) default NULL,`EMAIL` varchar(255) default NULL,`HO_URL` varchar(255) default 'http://',`LOGO` varchar(255) default NULL,`TIME_ZONE` varchar(255) default NULL,`ORGANISATION_CLASS` enum('Botanic_Garden','Zoo','Hoticultural','Museum','Herbarium','Laboratory','Other') NOT NULL default 'Botanic_Garden',`COLLECTION_CLASS` enum('living','dormant','culture','preserved','observations','photography','fossil','other') NOT NULL default 'living',`INDEX_HERB` varchar(16) default NULL,`ACCESS_RESTRICTION` text,`STATUT_PUBLICATION` tinyint(3) unsigned NOT NULL default '0',`SOURCE_DES_DONNEES` varchar(255) NOT NULL default 'IH - Index Herbariorum',PRIMARY KEY (`ID_ORG`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;# --------------------------------------------------------## Structure de la table `HERBIERS_PERSON_NAME`#CREATE TABLE `HERBIERS_PERSON_NAME` (`ID_PERSON_NAME` int(11) NOT NULL default '0',`ID` int(11) NOT NULL default '0',`PERSON` varchar(255) default NULL,`ROLE` varchar(255) default NULL,PRIMARY KEY (`ID_PERSON_NAME`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;# --------------------------------------------------------## Structure de la table `HERBIERS_PRES`#CREATE TABLE `HERBIERS_PRES` (`ID_PRES` int(10) unsigned NOT NULL auto_increment,`LABEL` varchar(255) NOT NULL default '',`LABEL_ANG` varchar(255) NOT NULL default '',PRIMARY KEY (`ID_PRES`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;# --------------------------------------------------------## Structure de la table `HERBIERS_PUBLICATIONS`#CREATE TABLE `HERBIERS_PUBLICATIONS` (`ID_PUBLICATIONS` int(11) NOT NULL default '0',`ID` int(11) NOT NULL default '0',`PUBLICATIONS` varchar(255) default NULL,PRIMARY KEY (`ID_PUBLICATIONS`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;# --------------------------------------------------------## Structure de la table `HERBIERS_STAFF`#CREATE TABLE `HERBIERS_STAFF` (`ID_STAFF` int(11) NOT NULL auto_increment,`NOM` varchar(255) default NULL,`PRENOM` varchar(255) default NULL,`ADRESSE1` varchar(255) default NULL,`ADRESSE2` varchar(255) default NULL,`CP` varchar(10) default NULL,`VILLE` varchar(255) default NULL,`MAIL` varchar(255) default NULL,`TEL` varchar(64) NOT NULL default '',`FAX` varchar(64) NOT NULL default '',`ID_TELA_BOTANICA` int(11) default NULL,`FONCTION` varchar(255) default NULL,`CONTACT` enum('non','oui') NOT NULL default 'non',PRIMARY KEY (`ID_STAFF`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;# --------------------------------------------------------## Structure de la table `HERBIERS_STATUT_PUBLICATION`#CREATE TABLE `HERBIERS_STATUT_PUBLICATION` (`HSP_ID` tinyint(3) unsigned NOT NULL default '0',`HSP_LABEL` varchar(255) default NULL,PRIMARY KEY (`HSP_ID`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;# --------------------------------------------------------## Structure de la table `HERBIERS_TYPE`#CREATE TABLE `HERBIERS_TYPE` (`ID_TYPE` int(11) NOT NULL auto_increment,`LABEL_TYPE` varchar(255) default NULL,PRIMARY KEY (`ID_TYPE`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;# --------------------------------------------------------## Structure de la table `HERBIERS_WEBSITES`#CREATE TABLE `HERBIERS_WEBSITES` (`ID_WEBSITES` int(11) NOT NULL default '0',`URL` varchar(255) default NULL,PRIMARY KEY (`ID_WEBSITES`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;# --------------------------------------------------------## Structure de la table `HERBIERS_ont_pres`#CREATE TABLE `HERBIERS_ont_pres` (`ID` int(10) unsigned NOT NULL default '0',`ID_PRES` int(10) unsigned NOT NULL default '0',PRIMARY KEY (`ID`,`ID_PRES`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;# --------------------------------------------------------## Structure de la table `HERBIERS_ont_un_staff`#CREATE TABLE `HERBIERS_ont_un_staff` (`ID_ORG` int(11) NOT NULL default '0',`ID_STAFF` int(11) NOT NULL default '0',PRIMARY KEY (`ID_ORG`,`ID_STAFF`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;# --------------------------------------------------------## Structure de la table `gen_COUNTRY`#CREATE TABLE `gen_COUNTRY` (`GC_ID` varchar(5) NOT NULL default '',`GC_LOCALE` varchar(5) NOT NULL default '',`GC_NAME` varchar(80) NOT NULL default '',`GC_CAPITAL` varchar(80) NOT NULL default '',`GC_CONTINENT_ID` int(11) NOT NULL default '0',PRIMARY KEY (`GC_ID`,`GC_LOCALE`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;