New file |
0,0 → 1,515 |
#+---------------------------------------------------------------------------------------------------------+ |
#| 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; |