source: xssim/trunk/db/example/schema.sql @ 104

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';

DROP SCHEMA IF EXISTS `gssim_results` ;
CREATE SCHEMA IF NOT EXISTS `gssim_results` DEFAULT CHARACTER SET latin1 ;
USE `gssim_results` ;

-- -----------------------------------------------------
-- Table `gssim_results`.`user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `gssim_results`.`user` ;

CREATE  TABLE IF NOT EXISTS `gssim_results`.`user` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `dn` VARCHAR(60) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `gssim_results`.`configuration`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `gssim_results`.`configuration` ;

CREATE  TABLE IF NOT EXISTS `gssim_results`.`configuration` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `gssim_results`.`metaexperiment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `gssim_results`.`metaexperiment` ;

CREATE  TABLE IF NOT EXISTS `gssim_results`.`metaexperiment` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `description` VARCHAR(511) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `gssim_results`.`experiment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `gssim_results`.`experiment` ;

CREATE  TABLE IF NOT EXISTS `gssim_results`.`experiment` (
  `PK` INT NOT NULL AUTO_INCREMENT ,
  `user_id` INT NOT NULL ,
  `config_id` INT NOT NULL ,
  `commission_date` DATETIME NULL COMMENT 'decide whether experiment results can be available to the public or should remain private.' ,
  `name` VARCHAR(45) NULL COMMENT 'Current experiment status in text and/or percent of performed job.' ,
  `description` VARCHAR(45) NULL ,
  `status` VARCHAR(45) NULL ,
  `metaexp_id` INT NULL ,
  PRIMARY KEY (`PK`) ,
  CONSTRAINT `fk_exp_user`
    FOREIGN KEY (`user_id` )
    REFERENCES `gssim_results`.`user` (`id` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_exp_config`
    FOREIGN KEY (`config_id` )
    REFERENCES `gssim_results`.`configuration` (`id` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_metaexp`
    FOREIGN KEY (`metaexp_id` )
    REFERENCES `gssim_results`.`metaexperiment` (`id` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_exp_user` ON `gssim_results`.`experiment` (`user_id` ASC) ;

CREATE INDEX `fk_exp_config` ON `gssim_results`.`experiment` (`config_id` ASC) ;

CREATE INDEX `fk_metaexp` ON `gssim_results`.`experiment` (`metaexp_id` ASC) ;


-- -----------------------------------------------------
-- Table `gssim_results`.`experiment_stats`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `gssim_results`.`experiment_stats` ;

CREATE  TABLE IF NOT EXISTS `gssim_results`.`experiment_stats` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(60) NOT NULL ,
  `value` DOUBLE NOT NULL ,
  CONSTRAINT `fk_exp_stats_exp`
    FOREIGN KEY (`id` )
    REFERENCES `gssim_results`.`experiment` (`PK` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_exp_stats_exp` ON `gssim_results`.`experiment_stats` (`id` ASC) ;


-- -----------------------------------------------------
-- Table `gssim_results`.`resource`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `gssim_results`.`resource` ;

CREATE  TABLE IF NOT EXISTS `gssim_results`.`resource` (
  `PK` INT NOT NULL AUTO_INCREMENT ,
  `exp_id` INT NOT NULL ,
  `name` VARCHAR(45) NOT NULL ,
  `type` VARCHAR(45) NOT NULL ,
  `allocation_load` DOUBLE NULL COMMENT 'resource load generated by executed tasks - used allocations' ,
  `reservation_load` DOUBLE NULL COMMENT 'resource load generated by reservations' ,
  PRIMARY KEY (`PK`) ,
  CONSTRAINT `fk_resource_exp`
    FOREIGN KEY (`exp_id` )
    REFERENCES `gssim_results`.`experiment` (`PK` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_resource_exp` ON `gssim_results`.`resource` (`exp_id` ASC) ;


-- -----------------------------------------------------
-- Table `gssim_results`.`unit`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `gssim_results`.`unit` ;

CREATE  TABLE IF NOT EXISTS `gssim_results`.`unit` (
  `PK` INT NOT NULL AUTO_INCREMENT ,
  `resource_id` INT NOT NULL ,
  `type` VARCHAR(45) NOT NULL ,
  `name` VARCHAR(45) NOT NULL ,
  `amount` INT NOT NULL ,
  `allocation_load` DOUBLE NOT NULL ,
  `reservation_load` DOUBLE NOT NULL ,
  PRIMARY KEY (`PK`) ,
  CONSTRAINT `fk_unit_resource`
    FOREIGN KEY (`resource_id` )
    REFERENCES `gssim_results`.`resource` (`PK` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_unit_resource` ON `gssim_results`.`unit` (`resource_id` ASC) ;


-- -----------------------------------------------------
-- Table `gssim_results`.`unit_energy_timeline`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `gssim_results`.`unit_energy_timeline` ;

CREATE  TABLE IF NOT EXISTS `gssim_results`.`unit_energy_timeline` (
  `fk_unit_id` INT NOT NULL ,
  `timestamp` DATETIME NOT NULL ,
  `usage` FLOAT NOT NULL ,
  CONSTRAINT `fk_en_timeline_unit`
    FOREIGN KEY (`fk_unit_id` )
    REFERENCES `gssim_results`.`unit` (`PK` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_en_timeline_unit` ON `gssim_results`.`unit_energy_timeline` (`fk_unit_id` ASC) ;


-- -----------------------------------------------------
-- Table `gssim_results`.`unit_allocation_timeline`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `gssim_results`.`unit_allocation_timeline` ;

CREATE  TABLE IF NOT EXISTS `gssim_results`.`unit_allocation_timeline` (
  `fk_unit_id` INT NOT NULL ,
  `timestamp` DATETIME NOT NULL ,
  `usage` FLOAT NOT NULL ,
  CONSTRAINT `fk_al_timeline_unit`
    FOREIGN KEY (`fk_unit_id` )
    REFERENCES `gssim_results`.`unit` (`PK` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_al_timeline_unit` ON `gssim_results`.`unit_allocation_timeline` (`fk_unit_id` ASC) ;


-- -----------------------------------------------------
-- Table `gssim_results`.`task`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `gssim_results`.`task` ;

CREATE  TABLE IF NOT EXISTS `gssim_results`.`task` (
  `PK` INT NOT NULL AUTO_INCREMENT ,
  `exp_id` INT NOT NULL ,
  `job_id` INT NOT NULL ,
  `task_id` INT NULL ,
  `proc_set_id` INT NULL ,
  `parent_id` INT NULL ,
  `exec_start_date` DATETIME NOT NULL ,
  `exec_finish_date` DATETIME NOT NULL COMMENT 'time when task execution is finshed' ,
  `exec_end_date` DATETIME NOT NULL COMMENT 'The latest date when the execution of task must be ended. If the value is not defined in the task description it equals exec_finish_date' ,
  `gb_submit_date` DATETIME NULL COMMENT 'grid/global broker submission date' ,
  `lb_submit_date` DATETIME NULL COMMENT 'the submission or arrival date of task from the latest GridResource (local broker)' ,
  `completion_time` INT NOT NULL COMMENT 'exec_finish_date - simulationStartDate' ,
  `exec_start_time` INT NULL COMMENT 'number of sconds between simulation start time and the beginning of task execution;\n\nexec_start_date -\nsimulationStartDate' ,
  `execution_time` INT NOT NULL COMMENT 'number of seconds between exec_finish_date and exec_start_date;\n\ncompletion_time - exec_start_time' ,
  `start_time` INT NOT NULL COMMENT 'difference in seconds between time in which task execution starts and time when task is delivered to grid/global broker;\n\nexec_start_time - (gb_submit_date - simulationStartDate)' ,
  `ready_time` INT NULL COMMENT 'the earliest time when the execution of task can be started (if ready time is not specified it is assumed to be equal to submission time) ' ,
  `flow_time` INT NOT NULL COMMENT 'completion_time - ready_time' ,
  `waiting_time` INT NOT NULL COMMENT 'exec_start_time - (lb_submit_date - simulationStartDate)' ,
  `gq_waiting_time` INT NOT NULL COMMENT 'lb_submit_date - gb_submit_date' ,
  `lateness` INT NOT NULL COMMENT 'completion_time - (exec_end_date - simulationStartDate)      ' ,
  `tardiness` INT NOT NULL COMMENT 'max(lateness)' ,
  `makespan` INT NULL ,
  PRIMARY KEY (`PK`) ,
  CONSTRAINT `fk_task_exp`
    FOREIGN KEY (`exp_id` )
    REFERENCES `gssim_results`.`experiment` (`PK` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_child_parent`
    FOREIGN KEY (`parent_id` )
    REFERENCES `gssim_results`.`task` (`PK` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_task_exp` ON `gssim_results`.`task` (`exp_id` ASC) ;

CREATE INDEX `idx_job_id` ON `gssim_results`.`task` (`job_id` ASC) ;

CREATE INDEX `idx_task_id` ON `gssim_results`.`task` (`task_id` ASC) ;

CREATE INDEX `idx_proc_set_id` ON `gssim_results`.`task` (`proc_set_id` ASC) ;

CREATE INDEX `fk_child_parent` ON `gssim_results`.`task` (`parent_id` ASC) ;


-- -----------------------------------------------------
-- Table `gssim_results`.`reservation`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `gssim_results`.`reservation` ;

CREATE  TABLE IF NOT EXISTS `gssim_results`.`reservation` (
  `PK` INT NOT NULL AUTO_INCREMENT ,
  `task_id` INT NULL ,
  `unit_id` INT NOT NULL ,
  `begin` DATETIME NOT NULL ,
  `end` DATETIME NOT NULL ,
  PRIMARY KEY (`PK`) ,
  CONSTRAINT `fk_reserv_task`
    FOREIGN KEY (`task_id` )
    REFERENCES `gssim_results`.`task` (`PK` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_reserv_unit`
    FOREIGN KEY (`unit_id` )
    REFERENCES `gssim_results`.`unit` (`PK` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_reserv_task` ON `gssim_results`.`reservation` (`task_id` ASC) ;

CREATE INDEX `fk_reserv_unit` ON `gssim_results`.`reservation` (`unit_id` ASC) ;


-- -----------------------------------------------------
-- Table `gssim_results`.`allocation`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `gssim_results`.`allocation` ;

CREATE  TABLE IF NOT EXISTS `gssim_results`.`allocation` (
  `PK` INT NOT NULL AUTO_INCREMENT ,
  `task_id` INT NOT NULL ,
  `unit_id` INT NOT NULL ,
  `begin` DATETIME NOT NULL ,
  `end` DATETIME NOT NULL ,
  PRIMARY KEY (`PK`) ,
  CONSTRAINT `fk_alloc_task`
    FOREIGN KEY (`task_id` )
    REFERENCES `gssim_results`.`task` (`PK` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_alloc_unit`
    FOREIGN KEY (`unit_id` )
    REFERENCES `gssim_results`.`unit` (`PK` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_alloc_task` ON `gssim_results`.`allocation` (`task_id` ASC) ;

CREATE INDEX `fk_alloc_unit` ON `gssim_results`.`allocation` (`unit_id` ASC) ;


-- -----------------------------------------------------
-- Table `gssim_results`.`queues`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `gssim_results`.`queues` ;

CREATE  TABLE IF NOT EXISTS `gssim_results`.`queues` (
  `fk_resource_id` INT NOT NULL ,
  `name` VARCHAR(45) NOT NULL ,
  `avg_length` FLOAT NULL ,
  CONSTRAINT `fk_queues_resource`
    FOREIGN KEY (`fk_resource_id` )
    REFERENCES `gssim_results`.`resource` (`PK` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_queues_resource` ON `gssim_results`.`queues` (`fk_resource_id` ASC) ;


-- -----------------------------------------------------
-- Table `gssim_results`.`unit_details`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `gssim_results`.`unit_details` ;

CREATE  TABLE IF NOT EXISTS `gssim_results`.`unit_details` (
  `fk_unit_id` INT NOT NULL ,
  `name` VARCHAR(45) NOT NULL ,
  `value` VARCHAR(45) NOT NULL ,
  CONSTRAINT `fk_udetails_unit`
    FOREIGN KEY (`fk_unit_id` )
    REFERENCES `gssim_results`.`unit` (`PK` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_udetails_unit` ON `gssim_results`.`unit_details` (`fk_unit_id` ASC) ;


-- -----------------------------------------------------
-- Table `gssim_results`.`resource_allocation_timeline`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `gssim_results`.`resource_allocation_timeline` ;

CREATE  TABLE IF NOT EXISTS `gssim_results`.`resource_allocation_timeline` (
  `fk_resource_id` INT NOT NULL ,
  `timestamp` DATETIME NOT NULL ,
  `usage` FLOAT NOT NULL ,
  CONSTRAINT `fk_al_timeline_resource`
    FOREIGN KEY (`fk_resource_id` )
    REFERENCES `gssim_results`.`resource` (`PK` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_al_timeline_resource` ON `gssim_results`.`resource_allocation_timeline` (`fk_resource_id` ASC) ;


-- -----------------------------------------------------
-- Table `gssim_results`.`resource_energy_timeline`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `gssim_results`.`resource_energy_timeline` ;

CREATE  TABLE IF NOT EXISTS `gssim_results`.`resource_energy_timeline` (
  `fk_resource_id` INT NOT NULL ,
  `timestamp` DATETIME NOT NULL ,
  `usage` FLOAT NOT NULL ,
  CONSTRAINT `fk_en_timeline_resource`
    FOREIGN KEY (`fk_resource_id` )
    REFERENCES `gssim_results`.`resource` (`PK` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_en_timeline_resource` ON `gssim_results`.`resource_energy_timeline` (`fk_resource_id` ASC) ;


-- -----------------------------------------------------
-- Table `gssim_results`.`text_property`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `gssim_results`.`text_property` ;

CREATE  TABLE IF NOT EXISTS `gssim_results`.`text_property` (
  `config_id` INT NOT NULL ,
  `name` VARCHAR(90) NOT NULL ,
  `value` VARCHAR(90) NOT NULL ,
  CONSTRAINT `fk_text_property_configuration1`
    FOREIGN KEY (`config_id` )
    REFERENCES `gssim_results`.`configuration` (`id` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_text_property_configuration1` ON `gssim_results`.`text_property` (`config_id` ASC) ;


-- -----------------------------------------------------
-- Table `gssim_results`.`blob_property`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `gssim_results`.`blob_property` ;

CREATE  TABLE IF NOT EXISTS `gssim_results`.`blob_property` (
  `config_id` INT NOT NULL ,
  `name` VARCHAR(45) NOT NULL ,
  `value` BLOB NOT NULL ,
  CONSTRAINT `fk_text_property_configuration10`
    FOREIGN KEY (`config_id` )
    REFERENCES `gssim_results`.`configuration` (`id` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_text_property_configuration1` ON `gssim_results`.`blob_property` (`config_id` ASC) ;


-- -----------------------------------------------------
-- Table `gssim_results`.`reservation_details`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `gssim_results`.`reservation_details` ;

CREATE  TABLE IF NOT EXISTS `gssim_results`.`reservation_details` (
  `fk_reservation_id` INT NOT NULL ,
  `name` VARCHAR(45) NULL ,
  `value` VARCHAR(45) NULL ,
  CONSTRAINT `fk_reservation_details_1`
    FOREIGN KEY (`fk_reservation_id` )
    REFERENCES `gssim_results`.`reservation` (`PK` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_reservation_details_1` ON `gssim_results`.`reservation_details` (`fk_reservation_id` ASC) ;


-- -----------------------------------------------------
-- Table `gssim_results`.`allocation_details`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `gssim_results`.`allocation_details` ;

CREATE  TABLE IF NOT EXISTS `gssim_results`.`allocation_details` (
  `fk_allocation_id` INT NOT NULL ,
  `name` VARCHAR(45) NULL ,
  `value` VARCHAR(45) NULL ,
  CONSTRAINT `fk_allocation_details_1`
    FOREIGN KEY (`fk_allocation_id` )
    REFERENCES `gssim_results`.`allocation` (`PK` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_allocation_details_1` ON `gssim_results`.`allocation_details` (`fk_allocation_id` ASC) ;


-- -----------------------------------------------------
-- Table `gssim_results`.`plugin`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `gssim_results`.`plugin` ;

CREATE  TABLE IF NOT EXISTS `gssim_results`.`plugin` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `is_global` TINYINT(1)  NOT NULL ,
  `description` VARCHAR(2000) NULL ,
  `input` VARCHAR(500) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `gssim_results`.`plugin_compatibility`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `gssim_results`.`plugin_compatibility` ;

CREATE  TABLE IF NOT EXISTS `gssim_results`.`plugin_compatibility` (
  `parent_id` INT NOT NULL ,
  `child_id` INT NOT NULL ,
  PRIMARY KEY (`parent_id`, `child_id`) ,
  CONSTRAINT `fk_plugin_compatibility_1`
    FOREIGN KEY (`parent_id` )
    REFERENCES `gssim_results`.`plugin` (`id` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_plugin_compatibility_2`
    FOREIGN KEY (`child_id` )
    REFERENCES `gssim_results`.`plugin` (`id` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `fk_plugin_compatibility_1` ON `gssim_results`.`plugin_compatibility` (`parent_id` ASC) ;

CREATE INDEX `fk_plugin_compatibility_2` ON `gssim_results`.`plugin_compatibility` (`child_id` ASC) ;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;