Subversion Repositories eFlore/Projets.eflore-projets

-- -----------------------------------------------------
-- Table `ontologie_type`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ontologie_type` (
  `id_type` INT NOT NULL ,
  `id_type_parent` INT NOT NULL DEFAULT 0 ,
  `type` VARCHAR(45) NULL ,
  `description` TEXT NULL ,
  PRIMARY KEY (`id_type`) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `ontologie_auteur`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ontologie_auteur` (
  `id_auteur` INT NOT NULL ,
  `prenom` VARCHAR(45) NULL ,
  `nom` VARCHAR(45) NULL ,
  `pseudo` VARCHAR(90) NULL ,
  `naissance_date` DATE NULL ,
  `deces_date` DATE NULL ,
  PRIMARY KEY (`id_auteur`) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `ontologie_publication`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ontologie_publication` (
  `id_publication` INT NOT NULL ,
  `ce_auteur_principal` INT NOT NULL ,
  `titre` VARCHAR(255) NULL ,
  `date` DATE NULL ,
  `uri` VARCHAR(255) NULL ,
  PRIMARY KEY (`id_publication`) ,
  INDEX `fk_ontologie_publication_ontologie_auteur1` (`ce_auteur_principal` ASC) ,
  CONSTRAINT `fk_ontologie_publication_ontologie_auteur1`
    FOREIGN KEY (`ce_auteur_principal` )
    REFERENCES `ontologie_auteur` (`id_auteur` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `ontologie_image`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ontologie_image` (
  `id_image` INT NOT NULL ,
  `uri` VARCHAR(255) NULL ,
  `ce_publication` INT NOT NULL ,
  PRIMARY KEY (`id_image`) ,
  INDEX `fk_ontologie_image_ontologie_publication1` (`ce_publication` ASC) ,
  CONSTRAINT `fk_ontologie_image_ontologie_publication1`
    FOREIGN KEY (`ce_publication` )
    REFERENCES `ontologie_publication` (`id_publication` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `ontologie_terme`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ontologie_terme` (
  `id_terme` INT NOT NULL COMMENT 'Identifiant global du terme (GLOBALID)' ,
  `ce_type` INT NOT NULL ,
  `terme` VARCHAR(255) NOT NULL ,
  `definition` TEXT NULL ,
  `terme_fr` VARCHAR(255) NULL ,
  `definition_fr` TEXT NULL ,
  `notes_fr` TEXT NULL ,
  `preference` TINYINT(1)  NULL COMMENT '1 = PREFERED TERM\n0 = DISALLOWED TERM\n' ,
  `ce_auteur` INT NOT NULL ,
  `ce_publication` INT NOT NULL ,
  `ce_image` INT NOT NULL ,
  PRIMARY KEY (`id_terme`) ,
  INDEX `fk_ontologie_terme_ontologie_terme_type` (`ce_type` ASC) ,
  INDEX `fk_ontologie_terme_ontologie_auteur1` (`ce_auteur` ASC) ,
  INDEX `fk_ontologie_terme_ontologie_publication1` (`ce_publication` ASC) ,
  INDEX `fk_ontologie_terme_ontologie_image1` (`ce_image` ASC) ,
  CONSTRAINT `fk_ontologie_terme_ontologie_terme_type`
    FOREIGN KEY (`ce_type` )
    REFERENCES `ontologie_type` (`id_type` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ontologie_terme_ontologie_auteur1`
    FOREIGN KEY (`ce_auteur` )
    REFERENCES `ontologie_auteur` (`id_auteur` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ontologie_terme_ontologie_publication1`
    FOREIGN KEY (`ce_publication` )
    REFERENCES `ontologie_publication` (`id_publication` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ontologie_terme_ontologie_image1`
    FOREIGN KEY (`ce_image` )
    REFERENCES `ontologie_image` (`id_image` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `ontologie_hierarchie`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ontologie_hierarchie` (
  `id_noeud` INT NOT NULL ,
  `id_noeud_parent` INT NOT NULL ,
  `chemin_noms` VARCHAR(500) NOT NULL ,
  `chemin_ids` VARCHAR(100) NOT NULL ,
  `ce_terme` INT NOT NULL ,
  PRIMARY KEY (`id_noeud`) ,
  INDEX `fk_ontologie_hierarchie_ontologie_terme1` (`ce_terme` ASC) ,
  CONSTRAINT `fk_ontologie_hierarchie_ontologie_terme1`
    FOREIGN KEY (`ce_terme` )
    REFERENCES `ontologie_terme` (`id_terme` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


-- -----------------------------------------------------
-- Table `ontologie_relation`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ontologie_relation` (
  `id_terme_01` INT NOT NULL ,
  `id_terme_02` INT NOT NULL ,
  `relation` VARCHAR(45) NULL COMMENT 'Relation entre terme_01 et terme_02.' ,
  PRIMARY KEY (`id_terme_01`, `id_terme_02`) ,
  INDEX `fk_ontologie_relations_ontologie_terme2` (`id_terme_02` ASC) ,
  CONSTRAINT `fk_ontologie_relations_ontologie_terme1`
    FOREIGN KEY (`id_terme_01` )
    REFERENCES `ontologie_terme` (`id_terme` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ontologie_relations_ontologie_terme2`
    FOREIGN KEY (`id_terme_02` )
    REFERENCES `ontologie_terme` (`id_terme` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

-- -----------------------------------------------------
-- Data for table `ontologie_type`
-- -----------------------------------------------------

INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (1, 0, 'Global', NULL);
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (4, 1, 'Modificateur relatif', NULL);
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (5, 1, 'Valeur relative', NULL);
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (6, 1, 'Modificateur spatial', NULL);
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (7, 1, 'Localisateur de region', NULL);
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (8, 1, 'Modificateur temporel', NULL);
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (9, 1, 'Unité', NULL);
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (10, 1, 'Propriété quantitative', NULL);
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (11, 1, 'Propriété quantitative redéfinie', NULL);
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (12, 0, 'Botanique', NULL);
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (13, 12, 'État qualitatif', NULL);
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (14, 12, 'Type de structure', NULL);
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (15, 12, 'Structure', NULL);
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (16, 12, 'Région', NULL);
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (17, 12, 'Structure générique', NULL);
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (18, 12, 'Groupe d\'états', NULL);
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (19, 18, 'Sous-groupe d\'états', NULL);
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (2, 1, 'Modificateur de fréquence', NULL);
INSERT INTO ontologie_type (`id_type`, `id_type_parent`, `type`, `description`) VALUES (3, 1, 'Qualificateur', NULL);