Schema Vulcain
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
CREATE SCHEMA IF NOT EXISTS `Vulcain` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `Vulcain` ;
Table `Vulcain`.`MDB_SGBD`
Ici, je crée la table "SGBD" qui contiendra les différents Serveurs de Gestion de Bases de Données.
CREATE TABLE IF NOT EXISTS `Vulcain`.`MDB_SGBD` (
`sgbd_id` INT NOT NULL AUTO_INCREMENT,
`sgbd_nom` VARCHAR(45) NULL,
PRIMARY KEY (`sgbd_id`))
ENGINE = InnoDB;
Table `Vulcain`.`MDB_LOCALISATION`
Je crée la table qui contiendra les différents serveurs physiques du Génoscope.
CREATE TABLE IF NOT EXISTS `Vulcain`.`MDB_LOCALISATION` (
`local_id` INT NOT NULL AUTO_INCREMENT,
`local_serveur` VARCHAR(45) NULL,
`local_alias` VARCHAR(45) NULL,
`local_adresse_ip` VARCHAR(16) NULL,
`local_domaine` VARCHAR(45) NULL,
`local_type` VARCHAR(45) NULL,
`local_os` VARCHAR(45) NULL,
PRIMARY KEY (`local_id`))
ENGINE = InnoDB
COMMENT = ' ';
Table `Vulcain`.`MDB_SERVEUR_DB`
Ici, je crée la table qui va répertorier tous les serveurs de bases de données.
CREATE TABLE IF NOT EXISTS `Vulcain`.`MDB_SERVEUR_DB` (
`srv_id` INT NOT NULL AUTO_INCREMENT,
`srv_nom` VARCHAR(45) NULL,
`srv_sgbd_id` INT NOT NULL,
`srv_localisation_id` INT NOT NULL,
`srv_port` DECIMAL(10) NULL,
`srv_version` DECIMAL(50) NULL,
`srv_date_maj` DATETIME NULL,
`srv_serveur_db` VARCHAR(45) NULL,
PRIMARY KEY (`srv_id`),
INDEX `fk_SERVEUR_DB_SGBD1_idx` (`srv_sgbd_id` ASC),
INDEX `fk_SERVEUR_DB_LOCALISATION1_idx` (`srv_localisation_id` ASC),
CONSTRAINT `fk_SERVEUR_DB_SGBD1`
FOREIGN KEY (`srv_sgbd_id`)
REFERENCES `Vulcain`.`MDB_SGBD` (`sgbd_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_SERVEUR_DB_LOCALISATION1`
FOREIGN KEY (`srv_localisation_id`)
REFERENCES `Vulcain`.`MDB_LOCALISATION` (`local_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
Table `Vulcain`.`MDB_DATABASE`
Cette table listera toutes les différentes bases de données du génoscope (environ 800 bases).
CREATE TABLE IF NOT EXISTS `Vulcain`.`MDB_DATABASE` (
`dat_id` INT NOT NULL AUTO_INCREMENT,
`dat_nom` VARCHAR(100) NULL,
`dat_serveur_db_id` INT NOT NULL,
`dat_owner` VARCHAR(120) NULL,
`dat_taille` BIGINT NULL,
`dat_espace_libre` INT NULL,
`dat_date_creation` DATETIME NULL,
`dat_date_fermeture` DATETIME NULL,
`dat_date_maj` DATETIME NULL,
`dat_date_backup` DATETIME NULL,
PRIMARY KEY (`dat_id`, `dat_serveur_db_id`),
INDEX `fk_DATABASE_SERVEUR_DB1_idx` (`dat_serveur_db_id` ASC),
CONSTRAINT `fk_DATABASE_SERVEUR_DB1`
FOREIGN KEY (`dat_serveur_db_id`)
REFERENCES `Vulcain`.`MDB_SERVEUR_DB` (`srv_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = ' ';
Table `Vulcain`.`MDB_APPLICATION`
Quelles applications y a-t-il sur ce serveur ? Cette table nous le dira !
CREATE TABLE IF NOT EXISTS `Vulcain`.`MDB_APPLICATION` (
`appli_id` INT NOT NULL AUTO_INCREMENT,
`appli_nom` VARCHAR(45) NULL,
PRIMARY KEY (`appli_id`))
ENGINE = InnoDB;
Table `Vulcain`.`MDB_TYPE_APPLICATION`
Celle-ci précisera le type d'application !
CREATE TABLE IF NOT EXISTS `Vulcain`.`MDB_TYPE_APPLICATION` (
`type_id` INT NOT NULL AUTO_INCREMENT,
`type_nature` VARCHAR(45) NULL,
`type_type` VARCHAR(45) NULL,
PRIMARY KEY (`type_id`))
ENGINE = InnoDB;
Table `Vulcain`.`REL_TYPE_APPLICATION`
Cette table fera la relation entre "MDB_APPLICATION et MDB_TYPE_APPLICATION".
CREATE TABLE IF NOT EXISTS `Vulcain`.`REL_TYPE_APPLICATION` (
`rel_typ_application_id` INT NOT NULL,
`rel_type_appli_id_type_application` INT NOT NULL,
PRIMARY KEY (`rel_typ_application_id`, `rel_type_appli_id_type_application`),
INDEX `fk_APPLICATION_has_TYPE_APPLICATION_TYPE_APPLICATION1_idx` (`rel_type_appli_id_type_application` ASC),
INDEX `fk_APPLICATION_has_TYPE_APPLICATION_APPLICATION1_idx` (`rel_typ_application_id` ASC),
CONSTRAINT `fk_APPLICATION_has_TYPE_APPLICATION_APPLICATION1`
FOREIGN KEY (`rel_typ_application_id`)
REFERENCES `Vulcain`.`MDB_APPLICATION` (`appli_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_APPLICATION_has_TYPE_APPLICATION_TYPE_APPLICATION1`
FOREIGN KEY (`rel_type_appli_id_type_application`)
REFERENCES `Vulcain`.`MDB_TYPE_APPLICATION` (`type_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
Table `Vulcain`.`REL_DATABASE_APPLICATION`
Si des applicatons utilisent des bases de données, nous pourrons faire une relation entre la base et l'application en question ici.
CREATE TABLE IF NOT EXISTS `Vulcain`.`REL_DATABASE_APPLICATION` (
`rel_dat_database_id` INT NOT NULL,
`rel_dati_application_id` INT NOT NULL,
`rel_dat_version` DECIMAL(50) NULL,
`rel_dat_date_maj` DATETIME NULL,
PRIMARY KEY (`rel_dat_database_id`, `rel_dati_application_id`),
INDEX `fk_DATABASE_has_APPLICATION_APPLICATION1_idx` (`rel_dati_application_id` ASC),
INDEX `fk_DATABASE_has_APPLICATION_DATABASE1_idx` (`rel_dat_database_id` ASC),
CONSTRAINT `fk_DATABASE_has_APPLICATION_DATABASE1`
FOREIGN KEY (`rel_dat_database_id`)
REFERENCES `Vulcain`.`MDB_DATABASE` (`dat_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_DATABASE_has_APPLICATION_APPLICATION1`
FOREIGN KEY (`rel_dati_application_id`)
REFERENCES `Vulcain`.`MDB_APPLICATION` (`appli_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
Table `Vulcain`.`MDB_HISTORIQUE`
L'historique de la taille de chaque base, daté et repertorié.
CREATE TABLE IF NOT EXISTS `Vulcain`.`MDB_HISTORIQUE` (
`hist_id` INT NOT NULL AUTO_INCREMENT,
`hist_DATABASE_id` INT NOT NULL,
`hist_DATABASE_APPLICATION_APPLICATION_id` INT NOT NULL,
`hist_DATABASE_APPLICATION_DATABASE_id` INT NOT NULL,
`hist_DATABASE_SGBD_SGBD_id` INT NOT NULL,
`hist_DATABASE_SGBD_DATABASE_id` INT NOT NULL,
`hist_date_maj_sgbd` DATETIME NULL,
`hist_date_maj_application` DATETIME NULL,
`hist_date_taille` DATETIME NULL,
PRIMARY KEY (`hist_id`),
INDEX `fk_HISTORIQUE_DATABASE_APPLICATION1_idx` (`hist_DATABASE_APPLICATION_DATABASE_id` ASC, `hist_DATABASE_APPLICATION_APPLICATION_id` ASC),
INDEX `fk_HISTORIQUE_DATABASE1_idx` (`hist_DATABASE_id` ASC),
CONSTRAINT `fk_HISTORIQUE_DATABASE_APPLICATION1`
FOREIGN KEY (`hist_DATABASE_APPLICATION_DATABASE_id` , `hist_DATABASE_APPLICATION_APPLICATION_id`)
REFERENCES `Vulcain`.`REL_DATABASE_APPLICATION` (`rel_dat_database_id` , `rel_dati_application_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_HISTORIQUE_DATABASE1`
FOREIGN KEY (`hist_DATABASE_id`)
REFERENCES `Vulcain`.`MDB_DATABASE` (`dat_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
Table `Vulcain`.`MDB_FILE_SYSTEM`
Liste des différents file system du Génoscope.
CREATE TABLE IF NOT EXISTS `Vulcain`.`MDB_FILE_SYSTEM` (
`file_id` INT NOT NULL AUTO_INCREMENT,
`file_nom` VARCHAR(45) NULL,
`file_taille_giga` DECIMAL(50) NULL,
`file_espace_libre` DECIMAL(50) NULL,
PRIMARY KEY (`file_id`))
ENGINE = InnoDB;
Table `Vulcain`.`REL_DATABASE_FILE_SYSTEM`
Grâce à cette table, nous saurons quelle file system heberge telle ou telle base.
CREATE TABLE IF NOT EXISTS `Vulcain`.`REL_DATABASE_FILE_SYSTEM` (
`rel_file_database_id` INT NOT NULL,
`rel_file_database_serveur_db_id` INT NOT NULL,
`rel_file_system_id` INT NOT NULL,
`rel_type` VARCHAR(15) NOT NULL,
PRIMARY KEY (`rel_file_database_id`, `rel_file_database_serveur_db_id`, `rel_file_system_id`, `rel_type`),
INDEX `fk_DATABASE_has_FILE_SYSTEM_FILE_SYSTEM1_idx` (`rel_file_system_id` ASC),
INDEX `fk_DATABASE_has_FILE_SYSTEM_DATABASE1_idx` (`rel_file_database_id` ASC, `rel_file_database_serveur_db_id` ASC),
CONSTRAINT `fk_DATABASE_has_FILE_SYSTEM_DATABASE1`
FOREIGN KEY (`rel_file_database_id` , `rel_file_database_serveur_db_id`)
REFERENCES `Vulcain`.`MDB_DATABASE` (`dat_id` , `dat_serveur_db_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_DATABASE_has_FILE_SYSTEM_FILE_SYSTEM1`
FOREIGN KEY (`rel_file_system_id`)
REFERENCES `Vulcain`.`MDB_FILE_SYSTEM` (`file_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
Table `Vulcain`.`MDB_LABO`
Liste les différents laboratoires du Génoscope. Cette table servira à localiser les utilisateurs dans la table MDB_USERS.
CREATE TABLE IF NOT EXISTS `Vulcain`.`MDB_LABO` (
`lab_id` INT NULL AUTO_INCREMENT,
`lab_nom` VARCHAR(150) NULL,
`lab_tag` VARCHAR(10) NULL,
`lab_service_tag` VARCHAR(15) NULL,
`lab_activite` VARCHAR(5) NULL,
`lab_serlabgmidlet` VARCHAR(10) NULL,
PRIMARY KEY (`lab_id`))
ENGINE = InnoDB
COMMENT = ' ';
Table `Vulcain`.`MDB_USERS`
Annuaire des employés du Génoscope.
CREATE TABLE IF NOT EXISTS `Vulcain`.`MDB_USERS` (
`u_id` INT NOT NULL,
`u_labo` INT NULL,
`u_log` VARCHAR(45) NULL,
`u_nom` VARCHAR(45) NULL,
`u_prenom` VARCHAR(45) NULL,
`u_presence` TINYINT(1) NULL,
`u_serco` INT NULL,
`u_tel` VARCHAR(45) NULL,
`u_matricule` VARCHAR(10) NULL,
PRIMARY KEY (`u_id`),
INDEX `fk_MDB_USERS_MDB_LABO1_idx` (`u_labo` ASC),
CONSTRAINT `fk_MDB_USERS_MDB_LABO1`
FOREIGN KEY (`u_labo`)
REFERENCES `Vulcain`.`MDB_LABO` (`lab_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
Table `Vulcain`.`REL_USERS_DATABASE`
La relation entre les utilisateurs et leur appartenance à une base.
CREATE TABLE IF NOT EXISTS `Vulcain`.`REL_USERS_DATABASE` (
`rel_u_users_id` INT NOT NULL,
`rel_u_dat_id` INT NOT NULL,
PRIMARY KEY (`rel_u_dat_id`, `rel_u_users_id`),
INDEX `fk_MDB_USERS_has_MDB_DATABASE_MDB_DATABASE1_idx` (`rel_u_dat_id` ASC),
INDEX `fk_MDB_USERS_has_MDB_DATABASE_MDB_USERS1_idx` (`rel_u_users_id` ASC),
CONSTRAINT `fk_MDB_USERS_has_MDB_DATABASE_MDB_USERS1`
FOREIGN KEY (`rel_u_users_id`)
REFERENCES `Vulcain`.`MDB_USERS` (`u_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_MDB_USERS_has_MDB_DATABASE_MDB_DATABASE1`
FOREIGN KEY (`rel_u_dat_id`)
REFERENCES `Vulcain`.`MDB_DATABASE` (`dat_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
Table `Vulcain`.`MDB_SOURCE`
Liste les propriétaires de chaque base.
CREATE TABLE IF NOT EXISTS `Vulcain`.`MDB_SOURCE` (
`src_nom_serveur` VARCHAR(45) NOT NULL,
`src_nom_dat` VARCHAR(45) NOT NULL,
`src_nom_pro` VARCHAR(45) NULL DEFAULT NULL,
PRIMARY KEY (`src_nom_serveur`, `src_nom_dat`, `src_nom_pro`))
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;