d81s.sql

-- MySQL Script generated by MySQL Workbench
-- 11/12/15 11:52:26
-- Model: One Step    Version: 1.0
-- MySQL Workbench Forward Engineering

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

-- -----------------------------------------------------
-- Schema d81s
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema d81s
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `d81s` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `d81s` ;

-- -----------------------------------------------------
-- Table `d81s`.`sessions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`sessions` (
  `session_id` INT NOT NULL AUTO_INCREMENT,
  `time_start` TIMESTAMP NULL,
  `time_end` TIMESTAMP NULL,
  `state` TINYINT(1) NULL,
  PRIMARY KEY (`session_id`),
  UNIQUE INDEX `session_id_UNIQUE` (`session_id` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`sources`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`sources` (
  `source_id` INT NOT NULL AUTO_INCREMENT,
  `session_id` INT NOT NULL,
  `explorer` VARCHAR(16) NOT NULL,
  `host` VARCHAR(64) NOT NULL,
  `login` VARCHAR(32) NULL,
  `password` VARCHAR(32) NULL,
  `proto` VARCHAR(32) NULL,
  `port` INT NULL,
  `url` VARCHAR(256) NULL,
  PRIMARY KEY (`source_id`, `session_id`),
  INDEX `fk_sources_sessions_idx` (`session_id` ASC),
  CONSTRAINT `fk_sources_sessions`
    FOREIGN KEY (`session_id`)
    REFERENCES `d81s`.`sessions` (`session_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`bfcf`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`bfcf` (
  `bfcf_id` INT NOT NULL AUTO_INCREMENT,
  `session_id` INT NOT NULL,
  `source_id` INT NOT NULL,
  `principal_wwn` VARCHAR(23) NULL,
  `principal_domain` INT NULL,
  `principal_ip` VARCHAR(15) NULL,
  `principal_name` VARCHAR(64) NULL,
  `fabric_name` VARCHAR(64) NULL,
  PRIMARY KEY (`bfcf_id`, `session_id`, `source_id`),
  INDEX `fk_bfcf_sessions1_idx` (`session_id` ASC),
  UNIQUE INDEX `bfcf_id_UNIQUE` (`bfcf_id` ASC),
  INDEX `fk_bfcf_sources1_idx` (`source_id` ASC),
  CONSTRAINT `fk_bfcf_sessions1`
    FOREIGN KEY (`session_id`)
    REFERENCES `d81s`.`sessions` (`session_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_bfcf_sources1`
    FOREIGN KEY (`source_id`)
    REFERENCES `d81s`.`sources` (`source_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`bfcf_members`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`bfcf_members` (
  `member_id` INT NOT NULL AUTO_INCREMENT,
  `bfcf_id` INT NOT NULL,
  `domain` INT NULL,
  `switchid` VARCHAR(6) NULL,
  `wwn` VARCHAR(23) NULL,
  `ip` VARCHAR(15) NULL,
  `name` VARCHAR(64) NULL,
  `principal` TINYINT(1) NULL DEFAULT 0,
  PRIMARY KEY (`member_id`, `bfcf_id`),
  INDEX `fk_bfcf_members_bfcf1_idx` (`bfcf_id` ASC),
  UNIQUE INDEX `member_id_UNIQUE` (`member_id` ASC),
  INDEX `domain` (`domain` ASC),
  CONSTRAINT `fk_bfcf_members_bfcf1`
    FOREIGN KEY (`bfcf_id`)
    REFERENCES `d81s`.`bfcf` (`bfcf_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`bfcf_zoning`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`bfcf_zoning` (
  `record_id` INT NOT NULL AUTO_INCREMENT,
  `bfcf_id` INT NOT NULL,
  `record_count` INT NULL,
  `record_name` VARCHAR(64) NULL,
  `record_member` VARCHAR(64) NULL,
  PRIMARY KEY (`record_id`, `bfcf_id`),
  INDEX `fk_bfcf_zoning_bfcf1_idx` (`bfcf_id` ASC),
  INDEX `bfcf_zoning-idx` (`record_count` ASC),
  UNIQUE INDEX `record_id_UNIQUE` (`record_id` ASC),
  INDEX `record_member` (`record_member` ASC),
  INDEX `record_name` (`record_name` ASC),
  CONSTRAINT `fk_bfcf_zoning_bfcf1`
    FOREIGN KEY (`bfcf_id`)
    REFERENCES `d81s`.`bfcf` (`bfcf_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`hdvm_arrays`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`hdvm_arrays` (
  `array_id` INT NOT NULL AUTO_INCREMENT,
  `session_id` INT NOT NULL,
  `source_id` INT NOT NULL,
  `name` VARCHAR(64) NOT NULL,
  `serial` VARCHAR(32) NULL,
  `array_type` VARCHAR(32) NULL,
  `display_array_type` VARCHAR(32) NULL,
  `capacity` BIGINT NULL,
  `allocated_capacity` BIGINT NULL,
  `free_capacity` BIGINT NULL,
  `total_free_space` BIGINT NULL,
  `number_of_controllers` SMALLINT NULL,
  `cache` INT NULL,
  `hardware_revision` VARCHAR(16) NULL,
  `controller_version` VARCHAR(16) NULL,
  PRIMARY KEY (`array_id`, `session_id`, `source_id`),
  INDEX `fk_arrays_sessions1_idx` (`session_id` ASC),
  UNIQUE INDEX `array_id_UNIQUE` (`array_id` ASC),
  INDEX `fk_hdvm_arrays_sources1_idx` (`source_id` ASC),
  CONSTRAINT `fk_arrays_sessions1`
    FOREIGN KEY (`session_id`)
    REFERENCES `d81s`.`sessions` (`session_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_hdvm_arrays_sources1`
    FOREIGN KEY (`source_id`)
    REFERENCES `d81s`.`sources` (`source_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`hdvm_lun`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`hdvm_lun` (
  `lun_id` INT NOT NULL AUTO_INCREMENT,
  `array_id` INT NOT NULL,
  `dev_num` INT NULL,
  `dev_num_display` VARCHAR(16) NULL,
  `capacity` BIGINT NULL,
  `emulation` VARCHAR(8) NULL,
  `device_count` INT NULL,
  `raid_type` VARCHAR(16) NULL,
  `consumed_capacity` BIGINT NULL,
  `command_device` TINYINT(1) NULL,
  `dp_pool_id` INT NULL,
  `dp_type` INT NULL,
  `rg_number` INT NULL,
  `port_id` INT NULL,
  `domain_id` INT NULL,
  `lun` INT NULL,
  `wwn` VARCHAR(23) NULL,
  `nickname` VARCHAR(64) NULL,
  PRIMARY KEY (`lun_id`, `array_id`),
  INDEX `fk_hdvm_lun_hdvm_arrays1_idx` (`array_id` ASC),
  INDEX `hdvm_lun-idx` (`wwn` ASC),
  UNIQUE INDEX `lun_id_UNIQUE` (`lun_id` ASC),
  INDEX `hdvm_lun_port_id` (`port_id` ASC),
  INDEX `hdvm_lun_domain_id` (`domain_id` ASC),
  INDEX `rg_number` (`rg_number` ASC),
  CONSTRAINT `fk_hdvm_lun_hdvm_arrays1`
    FOREIGN KEY (`array_id`)
    REFERENCES `d81s`.`hdvm_arrays` (`array_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`bfcf_ns`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`bfcf_ns` (
  `ns_id` INT NOT NULL AUTO_INCREMENT,
  `bfcf_id` INT NOT NULL,
  `port_type` VARCHAR(2) NULL,
  `address` VARCHAR(6) NULL,
  `cos` VARCHAR(8) NULL,
  `port_name` VARCHAR(23) NULL,
  `node_name` VARCHAR(23) NULL,
  `did` SMALLINT NULL,
  `aid` SMALLINT NULL,
  `alpa` SMALLINT NULL,
  `fabric_port_name` VARCHAR(23) NULL,
  `device_type` VARCHAR(64) NULL,
  `port_index` INT NULL,
  `port_symb` VARCHAR(128) NULL,
  `node_symb` VARCHAR(128) NULL,
  PRIMARY KEY (`ns_id`, `bfcf_id`),
  INDEX `fk_bfcf_ns_bfcf1_idx` (`bfcf_id` ASC),
  INDEX `bfcf_ns-idx` (`port_name` ASC),
  UNIQUE INDEX `ns_id_UNIQUE` (`ns_id` ASC),
  INDEX `did` (`did` ASC),
  INDEX `device_type` (`device_type` ASC),
  CONSTRAINT `fk_bfcf_ns_bfcf1`
    FOREIGN KEY (`bfcf_id`)
    REFERENCES `d81s`.`bfcf` (`bfcf_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`hdvm_port`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`hdvm_port` (
  `hdvm_port_id` INT NOT NULL AUTO_INCREMENT,
  `array_id` INT NOT NULL,
  `port_id` INT NULL,
  `port_type` VARCHAR(16) NULL,
  `port_role` VARCHAR(16) NULL,
  `topology` VARCHAR(32) NULL,
  `port_display_name` VARCHAR(16) NULL,
  `lun_security` TINYINT(1) NULL,
  `controller_id` INT NULL,
  `pwwn` VARCHAR(23) NULL,
  `channel_speed` INT NULL,
  `port_option` VARCHAR(64) NULL,
  `domain_id` VARCHAR(45) NULL,
  `host_mode` VARCHAR(32) NULL,
  `host_mode2` VARCHAR(240) NULL,
  `hg_display_name` VARCHAR(32) NULL,
  `domain_type` INT NULL,
  `nickname` VARCHAR(32) NULL,
  PRIMARY KEY (`hdvm_port_id`, `array_id`),
  INDEX `fk_hdvm_port_hdvm_arrays1_idx` (`array_id` ASC),
  UNIQUE INDEX `hdvm_port_id_UNIQUE` (`hdvm_port_id` ASC),
  FULLTEXT INDEX `hdvm_port_pwwn` (`pwwn` ASC),
  INDEX `hdvm_port_hg` (`domain_type` ASC),
  CONSTRAINT `fk_hdvm_port_hdvm_arrays1`
    FOREIGN KEY (`array_id`)
    REFERENCES `d81s`.`hdvm_arrays` (`array_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`hdvm_rg`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`hdvm_rg` (
  `rg_id` INT NOT NULL AUTO_INCREMENT,
  `array_id` INT NOT NULL,
  `number` INT NULL,
  `display_name` VARCHAR(16) NOT NULL,
  `disk_size` INT NULL,
  `disk_type` VARCHAR(32) NULL,
  `total_capacity` BIGINT NULL,
  `allocated_capacity` BIGINT NULL,
  `free_capacity` BIGINT NULL,
  `total_free_space` BIGINT NULL,
  `dp_pool_id` INT NULL,
  `emulation` VARCHAR(8) NULL,
  `chassis` INT NULL,
  `controller_id` INT NULL,
  `raid_type` VARCHAR(16) NULL,
  `rg_type` INT NULL,
  `volume_type` INT NULL,
  `encrypted` INT NULL,
  `protection_level` INT NULL,
  `form_factor` INT NULL,
  PRIMARY KEY (`rg_id`, `array_id`),
  INDEX `fk_hdvm_rg_hdvm_arrays1_idx` (`array_id` ASC),
  UNIQUE INDEX `rg_id_UNIQUE` (`rg_id` ASC),
  INDEX `number` (`number` ASC),
  INDEX `emulation` (`emulation` ASC),
  CONSTRAINT `fk_hdvm_rg_hdvm_arrays1`
    FOREIGN KEY (`array_id`)
    REFERENCES `d81s`.`hdvm_arrays` (`array_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`hdvm_pool`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`hdvm_pool` (
  `pool_id` INT NOT NULL AUTO_INCREMENT,
  `array_id` INT NOT NULL,
  `pool_function` INT NULL,
  `dp_pool_id` INT NULL,
  `controller_id` INT NULL,
  `pool_type` INT NULL,
  `status` INT NULL,
  `threshold` INT NULL,
  `threshold2` INT NULL,
  `capacity` BIGINT NULL,
  `free_capacity` BIGINT NULL,
  `usage_rate` INT NULL,
  `number_of_vvols` INT NULL,
  `capacity_of_vvols` BIGINT NULL,
  `raid_level` VARCHAR(16) NULL,
  `combination` VARCHAR(16) NULL,
  `disk_type` VARCHAR(16) NULL,
  `rpm` INT NULL,
  PRIMARY KEY (`pool_id`, `array_id`),
  INDEX `fk_hdvm_pool_hdvm_arrays1_idx` (`array_id` ASC),
  UNIQUE INDEX `pool_id_UNIQUE` (`pool_id` ASC),
  CONSTRAINT `fk_hdvm_pool_hdvm_arrays1`
    FOREIGN KEY (`array_id`)
    REFERENCES `d81s`.`hdvm_arrays` (`array_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`hdvm_ldev`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`hdvm_ldev` (
  `ldev_id` INT NOT NULL AUTO_INCREMENT,
  `array_id` INT NOT NULL,
  `array_group_number` INT NOT NULL,
  `dev_num` INT NOT NULL,
  `dev_num_display` VARCHAR(16) NOT NULL,
  `raid_type` VARCHAR(16) NULL,
  `dp_pool_id` INT NOT NULL,
  `dp_type` INT NULL,
  `chassis` INT NULL,
  `consumed_size` BIGINT NULL,
  `ldev_status` INT NULL,
  `quorum_disk` INT NULL,
  `encrypted` INT NULL,
  `threshold` INT NULL,
  `volume_kind` INT NULL,
  `emulation` VARCHAR(8) NULL,
  `size` BIGINT NULL,
  PRIMARY KEY (`ldev_id`, `array_id`),
  UNIQUE INDEX `ldev_id_UNIQUE` (`ldev_id` ASC),
  INDEX `fk_hdvm_ldev_hdvm_arrays1_idx` (`array_id` ASC),
  CONSTRAINT `fk_hdvm_ldev_hdvm_arrays1`
    FOREIGN KEY (`array_id`)
    REFERENCES `d81s`.`hdvm_arrays` (`array_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`lun_map`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`lun_map` (
  `lun_map_id` INT NOT NULL AUTO_INCREMENT,
  `session_id` INT NOT NULL,
  `Fabric Zone` VARCHAR(64) NOT NULL,
  `Host-WWN` VARCHAR(23) NOT NULL,
  `Array Host-alias` VARCHAR(64) NULL,
  `Host OS / Mode` VARCHAR(32) NULL,
  `LDEV / VVOL WWN` VARCHAR(39) NULL,
  `VVOL Name` VARCHAR(32) NULL,
  `RAID` VARCHAR(16) NULL,
  `LUN Capacity` BIGINT NULL,
  `LUN Consumed Capacity` BIGINT NULL,
  `Pool ID` INT NULL,
  `Raid-Group` VARCHAR(16) NULL,
  `Raid-Group type` INT NULL,
  `VVOL Provisioning` VARCHAR(4) NULL,
  `VVOL Type` VARCHAR(5) NULL,
  `VVOL User CPG` VARCHAR(32) NULL,
  `SAN Switch Host-side` VARCHAR(64) NULL,
  `SAN Switch Host-side Port` INT NULL,
  `SAN Switch Array-side` VARCHAR(64) NULL,
  `SAN Switch Array-side Port` INT NULL,
  `Array-WWN` VARCHAR(23) NULL,
  `Array Port` VARCHAR(16) NULL,
  `Array Host-group` VARCHAR(32) NULL,
  `Array Name` VARCHAR(64) NULL,
  `Array Serial Number / Array ID` VARCHAR(64) NULL,
  `Array Model` VARCHAR(32) NULL,
  PRIMARY KEY (`lun_map_id`, `session_id`),
  INDEX `fk_lun_map_sessions1_idx` (`session_id` ASC),
  CONSTRAINT `fk_lun_map_sessions1`
    FOREIGN KEY (`session_id`)
    REFERENCES `d81s`.`sessions` (`session_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`3par_arrays`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`3par_arrays` (
  `array_id` INT NOT NULL AUTO_INCREMENT,
  `session_id` INT NOT NULL,
  `source_id` INT NOT NULL,
  `system_name` VARCHAR(64) NULL,
  `system_model` VARCHAR(32) NULL,
  `serial_number` VARCHAR(32) NULL,
  `system_id` VARCHAR(32) NULL,
  `number_of_nodes` INT NULL,
  `master_node` SMALLINT NULL,
  `total_capacity` BIGINT NULL,
  `allocated_capacity` BIGINT NULL,
  `free_capacity` BIGINT NULL,
  `failed_capacity` BIGINT NULL,
  `location` VARCHAR(128) NULL,
  `owner` VARCHAR(128) NULL,
  `contact` VARCHAR(256) NULL,
  `comment` VARCHAR(512) NULL,
  PRIMARY KEY (`array_id`, `session_id`, `source_id`),
  INDEX `fk_3par_arrays_sessions1_idx` (`session_id` ASC),
  INDEX `fk_3par_arrays_sources1_idx` (`source_id` ASC),
  CONSTRAINT `fk_3par_arrays_sessions1`
    FOREIGN KEY (`session_id`)
    REFERENCES `d81s`.`sessions` (`session_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_3par_arrays_sources1`
    FOREIGN KEY (`source_id`)
    REFERENCES `d81s`.`sources` (`source_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`3par_port`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`3par_port` (
  `3par_port_id` INT NOT NULL AUTO_INCREMENT,
  `array_id` INT NOT NULL,
  `nsp` VARCHAR(8) NULL,
  `mode` VARCHAR(16) NULL,
  `state` VARCHAR(16) NULL,
  `nwwn` VARCHAR(23) NULL,
  `pwwn` VARCHAR(23) NULL,
  `port_type` VARCHAR(8) NULL,
  `protocol` VARCHAR(8) NULL,
  `label` VARCHAR(16) NULL,
  `partner` VARCHAR(8) NULL,
  PRIMARY KEY (`3par_port_id`, `array_id`),
  INDEX `fk_3par_port_3par_arrays1_idx` (`array_id` ASC),
  INDEX `nsp` (`nsp` ASC),
  INDEX `pwwn` (`pwwn` ASC),
  CONSTRAINT `fk_3par_port_3par_arrays1`
    FOREIGN KEY (`array_id`)
    REFERENCES `d81s`.`3par_arrays` (`array_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`3par_vlun`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`3par_vlun` (
  `3par_vlun_id` INT NOT NULL AUTO_INCREMENT,
  `array_id` INT NOT NULL,
  `lun` INT NOT NULL,
  `vvname` VARCHAR(64) NULL,
  `vv_wwn` VARCHAR(32) NULL,
  `hostname` VARCHAR(64) NULL,
  `host_wwniscsi` VARCHAR(23) NULL,
  `port` VARCHAR(8) NULL,
  PRIMARY KEY (`3par_vlun_id`, `array_id`),
  INDEX `fk_3par_vlun_3par_arrays1_idx` (`array_id` ASC),
  INDEX `nsp` (`port` ASC),
  INDEX `hwwn` (`host_wwniscsi` ASC),
  INDEX `vv_wwn` (`vv_wwn` ASC),
  INDEX `port` (`port` ASC),
  CONSTRAINT `fk_3par_vlun_3par_arrays1`
    FOREIGN KEY (`array_id`)
    REFERENCES `d81s`.`3par_arrays` (`array_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`3par_vvol`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`3par_vvol` (
  `3par_vvol_id` INT NOT NULL AUTO_INCREMENT,
  `array_id` INT NOT NULL,
  `vv_id` INT NOT NULL,
  `vvname` VARCHAR(64) NULL,
  `rd` VARCHAR(2) NULL,
  `mstr` VARCHAR(8) NULL,
  `vv_wwn` VARCHAR(32) NULL,
  `prov` VARCHAR(4) NULL,
  `vv_type` VARCHAR(5) NULL,
  `usrcpg` VARCHAR(32) NULL,
  `snpcpg` VARCHAR(32) NULL,
  `tot_rsvr_kb` BIGINT NULL,
  `vsize_kb` BIGINT NULL,
  PRIMARY KEY (`3par_vvol_id`, `array_id`),
  INDEX `fk_3par_vvol_3par_arrays1_idx` (`array_id` ASC),
  INDEX `vv_wwn` (`vv_type` ASC),
  INDEX `usrcpg` (`usrcpg` ASC),
  CONSTRAINT `fk_3par_vvol_3par_arrays1`
    FOREIGN KEY (`array_id`)
    REFERENCES `d81s`.`3par_arrays` (`array_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`3par_cpg`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`3par_cpg` (
  `3par_cpg_id` INT NOT NULL AUTO_INCREMENT,
  `array_id` INT NOT NULL,
  `cpg_id` INT NULL,
  `cpgname` VARCHAR(64) NULL,
  `warn` VARCHAR(3) NULL,
  `lim` VARCHAR(3) NULL,
  `grow` VARCHAR(3) NULL,
  `raid` VARCHAR(16) NULL,
  `ssz` INT NULL,
  `rs` INT NULL,
  `ss` INT NULL,
  `ha` VARCHAR(4) NULL,
  `nd` VARCHAR(45) NULL,
  `devtype` VARCHAR(8) NULL,
  `rpm` VARCHAR(8) NULL,
  PRIMARY KEY (`3par_cpg_id`, `array_id`),
  INDEX `fk_3par_cpg_3par_arrays1_idx` (`array_id` ASC),
  INDEX `cpgname` (`cpgname` ASC),
  CONSTRAINT `fk_3par_cpg_3par_arrays1`
    FOREIGN KEY (`array_id`)
    REFERENCES `d81s`.`3par_arrays` (`array_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`ibts_libraries`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`ibts_libraries` (
  `library_id` INT NOT NULL AUTO_INCREMENT,
  `session_id` INT NOT NULL,
  `source_id` INT NOT NULL,
  `name` VARCHAR(64) NULL,
  `model` VARCHAR(32) NULL,
  `comment` VARCHAR(512) NULL,
  PRIMARY KEY (`library_id`, `session_id`, `source_id`),
  INDEX `fk_ibts_libraries_sessions1_idx` (`session_id` ASC),
  INDEX `fk_ibts_libraries_sources1_idx` (`source_id` ASC),
  CONSTRAINT `fk_ibts_libraries_sessions1`
    FOREIGN KEY (`session_id`)
    REFERENCES `d81s`.`sessions` (`session_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ibts_libraries_sources1`
    FOREIGN KEY (`source_id`)
    REFERENCES `d81s`.`sources` (`source_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`ibts_drives`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`ibts_drives` (
  `ibts_drives_id` INT NOT NULL AUTO_INCREMENT,
  `library_id` INT NOT NULL,
  `wwn` VARCHAR(23) NOT NULL,
  `model` VARCHAR(24) NULL,
  `serial` VARCHAR(16) NULL,
  `valid` INT NULL,
  `error` BIGINT NULL,
  `abort` BIGINT NULL,
  `reset` BIGINT NULL,
  `recovery` BIGINT NULL,
  PRIMARY KEY (`ibts_drives_id`, `library_id`),
  INDEX `fk_ibts_drives_ibts_libraries1_idx` (`library_id` ASC),
  CONSTRAINT `fk_ibts_drives_ibts_libraries1`
    FOREIGN KEY (`library_id`)
    REFERENCES `d81s`.`ibts_libraries` (`library_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`heva_arrays`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`heva_arrays` (
  `array_id` INT NOT NULL AUTO_INCREMENT,
  `session_id` INT NOT NULL,
  `source_id` INT NOT NULL,
  `name` VARCHAR(64) NOT NULL,
  `wwn` VARCHAR(19) NOT NULL,
  `operationalstate` VARCHAR(16) NULL,
  `operationalstatedetail` VARCHAR(32) NULL,
  `licensestate` VARCHAR(32) NULL,
  `comments` VARCHAR(512) NULL,
  `uid` VARCHAR(40) NOT NULL,
  `hexuid` VARCHAR(39) NOT NULL,
  `systemtype` VARCHAR(16) NULL,
  `totalstoragespace` BIGINT NULL,
  `usedstoragespace` BIGINT NULL,
  `availablestoragespace` BIGINT NULL,
  `firmwareversion` VARCHAR(16) NULL,
  `nscfwversion` VARCHAR(32) NULL,
  `storagesystemcontrollermemory` INT NULL,
  `storagesystemcontrollercachememory` INT NULL,
  PRIMARY KEY (`array_id`, `session_id`, `source_id`),
  INDEX `fk_eva_arrays_sessions1_idx` (`session_id` ASC),
  INDEX `fk_eva_arrays_sources1_idx` (`source_id` ASC),
  INDEX `uid` (`uid` ASC),
  CONSTRAINT `fk_eva_arrays_sessions1`
    FOREIGN KEY (`session_id`)
    REFERENCES `d81s`.`sessions` (`session_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_eva_arrays_sources1`
    FOREIGN KEY (`source_id`)
    REFERENCES `d81s`.`sources` (`source_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`heva_port`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`heva_port` (
  `port_id` INT NOT NULL AUTO_INCREMENT,
  `array_id` INT NOT NULL,
  `portname` VARCHAR(32) NULL,
  `wwid` VARCHAR(23) NULL,
  `nodeid` VARCHAR(23) NULL,
  `hostportaddress` VARCHAR(8) NULL,
  `operationalstate` VARCHAR(16) NULL,
  `speed` INT NULL,
  `portcondition` VARCHAR(16) NULL,
  `topology` VARCHAR(16) NULL,
  `controller_name` VARCHAR(32) NULL,
  `controller_uid` VARCHAR(40) NULL,
  PRIMARY KEY (`port_id`, `array_id`),
  INDEX `fk_heva_port_heva_arrays1_idx` (`array_id` ASC),
  INDEX `cuid` (`controller_uid` ASC),
  INDEX `wwn` (`wwid` ASC),
  CONSTRAINT `fk_heva_port_heva_arrays1`
    FOREIGN KEY (`array_id`)
    REFERENCES `d81s`.`heva_arrays` (`array_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`heva_dg`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`heva_dg` (
  `dg_id` INT NOT NULL AUTO_INCREMENT,
  `array_id` INT NOT NULL,
  `uid` VARCHAR(40) NULL,
  `operationalstate` VARCHAR(16) NULL,
  `operationalstatedetail` VARCHAR(32) NULL,
  `diskgroupname` VARCHAR(64) NULL,
  `totaldisks` INT NULL,
  `levelingstate` VARCHAR(16) NULL,
  `levelingprogress` VARCHAR(5) NULL,
  `diskdrivetype` VARCHAR(16) NULL,
  `requestedsparepolicy` VARCHAR(16) NULL,
  `currentsparepolicy` VARCHAR(16) NULL,
  `totalstoragespace_raw` BIGINT NULL,
  `totalstoragespace` BIGINT NULL,
  `usedstoragespace_raw` BIGINT NULL,
  `usedstoragespace` BIGINT NULL,
  `occupancyalarmlevel` INT NULL,
  `diskgrouptype` VARCHAR(16) NULL,
  `dgwarningalarmlevel` INT NULL,
  PRIMARY KEY (`dg_id`, `array_id`),
  INDEX `fk_heva_dg_heva_arrays1_idx` (`array_id` ASC),
  INDEX `uid` (`uid` ASC),
  CONSTRAINT `fk_heva_dg_heva_arrays1`
    FOREIGN KEY (`array_id`)
    REFERENCES `d81s`.`heva_arrays` (`array_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`heva_host`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`heva_host` (
  `host_id` INT NOT NULL AUTO_INCREMENT,
  `array_id` INT NOT NULL,
  `uid` VARCHAR(40) NULL,
  `storagecellid` VARCHAR(40) NULL,
  `hostname` VARCHAR(64) NULL,
  `virtualdiskid` VARCHAR(40) NULL,
  `portwwn` VARCHAR(23) NULL,
  `operationalstate` VARCHAR(16) NULL,
  `operationalstatedetail` VARCHAR(32) NULL,
  `osmode` VARCHAR(16) NULL,
  `osmodebitmask` VARCHAR(16) NULL,
  `hosttype` VARCHAR(16) NULL,
  `osmodeindex` INT NULL,
  PRIMARY KEY (`host_id`, `array_id`),
  INDEX `fk_heva_host_heva_arrays1_idx` (`array_id` ASC),
  INDEX `uid` (`uid` ASC),
  INDEX `hpwwn` (`portwwn` ASC),
  INDEX `storageuid` (`storagecellid` ASC),
  CONSTRAINT `fk_heva_host_heva_arrays1`
    FOREIGN KEY (`array_id`)
    REFERENCES `d81s`.`heva_arrays` (`array_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`heva_vdisk`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`heva_vdisk` (
  `vdisk_id` INT NOT NULL AUTO_INCREMENT,
  `array_id` INT NOT NULL,
  `uid` VARCHAR(40) NULL,
  `familyname` VARCHAR(64) NULL,
  `creationdatetime` DATETIME NULL,
  `timestampmodify` TIMESTAMP NULL,
  `istpvdisk` TINYINT(1) NULL,
  `wwlunid` VARCHAR(39) NULL,
  `dirtyblockcount` INT NULL,
  `controllerid` VARCHAR(40) NULL,
  `migrationinprogress` FLOAT NULL,
  `operationalstate` VARCHAR(16) NULL,
  `operationalstatedetail` VARCHAR(32) NULL,
  `allocatedcapacity` BIGINT NULL,
  `requestedcapacity` BIGINT NULL,
  `virtualdisktype` VARCHAR(16) NULL,
  `redundancy` VARCHAR(8) NULL,
  `writecacheactual` VARCHAR(16) NULL,
  `writecache` VARCHAR(16) NULL,
  `mirrorcache` VARCHAR(16) NULL,
  `readcache` VARCHAR(8) NULL,
  `virtualdiskpresented` VARCHAR(8) NULL,
  `hostid` VARCHAR(40) NULL,
  `lunnumber` INT NULL,
  `writeprotect` VARCHAR(8) NULL,
  `diskgroupid` VARCHAR(40) NULL,
  `preferredpath` VARCHAR(32) NULL,
  `restoreprogress` VARCHAR(5) NULL,
  `hostaccess` VARCHAR(16) NULL,
  PRIMARY KEY (`vdisk_id`, `array_id`),
  INDEX `fk_heva_vdisk_heva_arrays1_idx` (`array_id` ASC),
  INDEX `uid` (`uid` ASC),
  INDEX `cuid` (`controllerid` ASC),
  INDEX `huid` (`hostid` ASC),
  INDEX `dguid` (`diskgroupid` ASC),
  CONSTRAINT `fk_heva_vdisk_heva_arrays1`
    FOREIGN KEY (`array_id`)
    REFERENCES `d81s`.`heva_arrays` (`array_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`lun_map_hdvm`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`lun_map_hdvm` (
  `lun_map_id` INT NOT NULL AUTO_INCREMENT,
  `session_id` INT NOT NULL,
  `Fabric Zone` VARCHAR(128) NOT NULL,
  `Host-WWN` VARCHAR(23) NOT NULL,
  `Array Host-alias` VARCHAR(64) NULL,
  `Host OS / Mode` VARCHAR(32) NULL,
  `LDEV / VVOL WWN` VARCHAR(32) NULL,
  `RAID` VARCHAR(16) NULL,
  `LUN Capacity` BIGINT NULL,
  `LUN Consumed Capacity` BIGINT NULL,
  `Pool ID` INT NULL,
  `Raid-Group` VARCHAR(16) NULL,
  `Raid-Group type` INT NULL,
  `SAN Switch Host-side` VARCHAR(64) NULL,
  `SAN Switch Host-side Port` INT NULL,
  `SAN Switch Array-side` VARCHAR(64) NULL,
  `SAN Switch Array-side Port` INT NULL,
  `Array-WWN` VARCHAR(23) NULL,
  `Array Port` VARCHAR(16) NULL,
  `Array Host-group` VARCHAR(32) NULL,
  `Array Name` VARCHAR(64) NULL,
  `Array Serial Number / Array ID` VARCHAR(64) NULL,
  `Array Model` VARCHAR(32) NULL,
  PRIMARY KEY (`lun_map_id`, `session_id`),
  INDEX `fk_lun_map_hdvm_sessions1_idx` (`session_id` ASC),
  CONSTRAINT `fk_lun_map_hdvm_sessions1`
    FOREIGN KEY (`session_id`)
    REFERENCES `d81s`.`sessions` (`session_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`lun_map_3par`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`lun_map_3par` (
  `lun_map_id` INT NOT NULL AUTO_INCREMENT,
  `session_id` INT NOT NULL,
  `Fabric Zone` VARCHAR(128) NOT NULL,
  `Host-WWN` VARCHAR(23) NOT NULL,
  `Array Host-alias` VARCHAR(64) NULL,
  `Host OS / Mode` VARCHAR(32) NULL,
  `LDEV / VVOL WWN` VARCHAR(32) NULL,
  `VVOL Name` VARCHAR(32) NULL,
  `RAID` VARCHAR(16) NULL,
  `LUN Capacity` BIGINT NULL,
  `LUN Consumed Capacity` BIGINT NULL,
  `VVOL Provisioning` VARCHAR(4) NULL,
  `VVOL Type` VARCHAR(5) NULL,
  `VVOL User CPG` VARCHAR(32) NULL,
  `SAN Switch Host-side` VARCHAR(64) NULL,
  `SAN Switch Host-side Port` INT NULL,
  `SAN Switch Array-side` VARCHAR(64) NULL,
  `SAN Switch Array-side Port` INT NULL,
  `Array-WWN` VARCHAR(23) NULL,
  `Array Port` VARCHAR(16) NULL,
  `Array Name` VARCHAR(64) NULL,
  `Array Serial Number / Array ID` VARCHAR(64) NULL,
  `Array Model` VARCHAR(32) NULL,
  PRIMARY KEY (`lun_map_id`, `session_id`),
  INDEX `fk_lun_map_3par_sessions1_idx` (`session_id` ASC),
  CONSTRAINT `fk_lun_map_3par_sessions1`
    FOREIGN KEY (`session_id`)
    REFERENCES `d81s`.`sessions` (`session_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`lun_map_ibts`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`lun_map_ibts` (
  `lun_map_id` INT NOT NULL AUTO_INCREMENT,
  `session_id` INT NOT NULL,
  `Fabric Zone` VARCHAR(128) NOT NULL,
  `Host-WWN` VARCHAR(23) NOT NULL,
  `SAN Switch Host-side` VARCHAR(64) NULL,
  `SAN Switch Host-side Port` INT NULL,
  `SAN Switch Array-side` VARCHAR(64) NULL,
  `SAN Switch Array-side Port` INT NULL,
  `Array-WWN` VARCHAR(23) NULL,
  `Array Name` VARCHAR(64) NULL,
  `Array Model` VARCHAR(32) NULL,
  PRIMARY KEY (`lun_map_id`, `session_id`),
  INDEX `fk_lun_map_ibts_sessions1_idx` (`session_id` ASC),
  CONSTRAINT `fk_lun_map_ibts_sessions1`
    FOREIGN KEY (`session_id`)
    REFERENCES `d81s`.`sessions` (`session_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`lun_map_heva`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`lun_map_heva` (
  `lun_map_id` INT NOT NULL AUTO_INCREMENT,
  `session_id` INT NOT NULL,
  `Fabric Zone` VARCHAR(128) NOT NULL,
  `Host-WWN` VARCHAR(23) NOT NULL,
  `Array Host-alias` VARCHAR(64) NULL,
  `Host OS / Mode` VARCHAR(32) NULL,
  `VDISK WWLUNID` VARCHAR(39) NULL,
  `VDISK Name` VARCHAR(32) NULL,
  `RAID` VARCHAR(16) NULL,
  `LUN Capacity` BIGINT NULL,
  `LUN Consumed Capacity` BIGINT NULL,
  `Disk-Group` VARCHAR(32) NULL,
  `SAN Switch Host-side` VARCHAR(64) NULL,
  `SAN Switch Host-side Port` INT NULL,
  `SAN Switch Array-side` VARCHAR(64) NULL,
  `SAN Switch Array-side Port` INT NULL,
  `Array-WWN` VARCHAR(23) NULL,
  `Array Port` VARCHAR(16) NULL,
  `Array Name` VARCHAR(64) NULL,
  `Array SN WWN` VARCHAR(23) NULL,
  `Array Model` VARCHAR(32) NULL,
  PRIMARY KEY (`lun_map_id`, `session_id`),
  INDEX `fk_lun_map_heva_sessions1_idx` (`session_id` ASC),
  CONSTRAINT `fk_lun_map_heva_sessions1`
    FOREIGN KEY (`session_id`)
    REFERENCES `d81s`.`sessions` (`session_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`3par_host`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`3par_host` (
  `3par_host_id` INT NOT NULL AUTO_INCREMENT,
  `array_id` INT NOT NULL,
  `host_id` INT NULL,
  `host_name` VARCHAR(64) NULL,
  `persona` VARCHAR(16) NULL,
  `host_wwniscsi` VARCHAR(23) NULL,
  `port` VARCHAR(8) NULL,
  `ip_addr` VARCHAR(15) NULL,
  PRIMARY KEY (`3par_host_id`, `array_id`),
  INDEX `fk_3par_host_3par_arrays1_idx` (`array_id` ASC),
  INDEX `hwwn` (`host_wwniscsi` ASC),
  CONSTRAINT `fk_3par_host_3par_arrays1`
    FOREIGN KEY (`array_id`)
    REFERENCES `d81s`.`3par_arrays` (`array_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `d81s`.`lun_map_unknown`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `d81s`.`lun_map_unknown` (
  `lun_map_id` INT NOT NULL AUTO_INCREMENT,
  `session_id` INT NOT NULL,
  `Fabric Zone` VARCHAR(128) NOT NULL,
  `Initiatort-WWN` VARCHAR(23) NOT NULL,
  `Initiator Device Type` VARCHAR(64) NULL,
  `Initiator Port Symb` VARCHAR(128) NULL,
  `Initiator Node Symb` VARCHAR(128) NULL,
  `SAN Switch Initiator-side` VARCHAR(64) NULL,
  `SAN Switch Initiator-side Port` INT NULL,
  `SAN Switch Target-side Port` INT NULL,
  `SAN Switch Target-side` VARCHAR(64) NULL,
  `Target Port Symb` VARCHAR(128) NULL,
  `Target Node Symb` VARCHAR(128) NULL,
  `Target Device Type` VARCHAR(64) NULL,
  `Target-WWN` VARCHAR(23) NULL,
  PRIMARY KEY (`lun_map_id`, `session_id`),
  INDEX `fk_lun_map_unknown_sessions1_idx` (`session_id` ASC),
  CONSTRAINT `fk_lun_map_unknown_sessions1`
    FOREIGN KEY (`session_id`)
    REFERENCES `d81s`.`sessions` (`session_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

USE `d81s` ;

-- -----------------------------------------------------
-- procedure get_volumes_totals
-- -----------------------------------------------------

DELIMITER $$
USE `d81s`$$
CREATE PROCEDURE `get_volumes_totals`()
BEGIN
	DECLARE s_id INT;
	SELECT max(lun_map.session_id) INTO s_id from lun_map join sessions on sessions.state = 0;
	SELECT DISTINCT
		`Fabric Zone`,
        `RAID`,
        round(sum(`LUN Capacity`)/1024/1024, 2) as `LUN Capacity, GB`,
        round(sum(`LUN Consumed Capacity`)/1024/1024, 2) as `LUN Used, GB`,
        round(sum(`LUN Capacity` - `LUN Consumed Capacity`)/1024/1024, 2) as `LUN Free Capacity, GB`,
        round((sum(`LUN Consumed Capacity`))/(sum(`LUN Capacity`))*100, 2) as `Used %`,
        `Array Name`
--  FROM d81s.lun_map WHERE session_id = s_id and `LUN Consumed Capacity` != -1 GROUP BY `Fabric Zone`, `LDEV`, `RAID`;
	FROM d81s.lun_map WHERE session_id = s_id and `LUN Consumed Capacity` != -1 GROUP BY `Fabric Zone`, `RAID`;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure get_thp_volumes_capacity_vs_consumed_percents
-- -----------------------------------------------------

DELIMITER $$
USE `d81s`$$
CREATE PROCEDURE `get_thp_volumes_capacity_vs_consumed_percents`(IN s_id INT)
BEGIN
	SELECT DISTINCT
		`Fabric Zone`, 
        `LDEV / VVOL WWN`,
        `RAID`,
        `LUN Capacity`,
        `LUN Consumed Capacity`,
        round((`LUN Capacity` - `LUN Consumed Capacity`)/1024/1024, 2) as 'LUN Free Capacity, GB',
        round((`LUN Consumed Capacity`/`LUN Capacity`)*100, 2) as 'Used %',
        `Array Name`,
        `Array Serial Number / Array ID` 
	FROM d81s.lun_map WHERE session_id = s_id and `LUN Consumed Capacity` != -1;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure get_fresh_lun_map_unknown
-- -----------------------------------------------------

DELIMITER $$
USE `d81s`$$
CREATE PROCEDURE `get_fresh_lun_map_unknown`()
BEGIN
	DECLARE s_id INT;
	SELECT max(lun_map_unknown.session_id) INTO s_id from lun_map_unknown join sessions on sessions.state = 0;
	SELECT * FROM d81s.lun_map_unknown WHERE lun_map_unknown.session_id = s_id;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure get_fresh_session
-- -----------------------------------------------------

DELIMITER $$
USE `d81s`$$
CREATE PROCEDURE `get_fresh_session`()
BEGIN
	SELECT max(session_id) FROM sessions WHERE sessions.state = 0;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure get_lun_map_unknown
-- -----------------------------------------------------

DELIMITER $$
USE `d81s`$$
CREATE PROCEDURE `get_lun_map_unknown`(IN s_id INT)
BEGIN
	SELECT * FROM d81s.lun_map_unknown WHERE lun_map_unknown.session_id = s_id;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure get_lun_map
-- -----------------------------------------------------

DELIMITER $$
USE `d81s`$$
CREATE PROCEDURE `get_lun_map`(IN s_id INT)
BEGIN
	
	SELECT * FROM d81s.lun_map WHERE lun_map.session_id = s_id;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure get_hdvm_arrays
-- -----------------------------------------------------

DELIMITER $$
USE `d81s`$$
CREATE DEFINER=`d81s`@`%` PROCEDURE `get_hdvm_arrays`(IN s_id INT)
BEGIN
	SELECT * FROM d81s.hdvm_arrays WHERE hdvm_arrays.session_id = s_id;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure get_3par_arrays
-- -----------------------------------------------------

DELIMITER $$
USE `d81s`$$
CREATE DEFINER=`d81s`@`%` PROCEDURE `get_3par_arrays`(IN s_id INT)
BEGIN
	SELECT * FROM `d81s`.`3par_arrays` WHERE `3par_arrays`.session_id = s_id;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure get_heva_arrays
-- -----------------------------------------------------

DELIMITER $$
USE `d81s`$$
CREATE DEFINER=`d81s`@`%` PROCEDURE `get_heva_arrays`(IN s_id INT)
BEGIN
	SELECT * FROM `d81s`.`heva_arrays` WHERE heva_arrays.session_id = s_id;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure get_fresh_lun_map
-- -----------------------------------------------------

DELIMITER $$
USE `d81s`$$
CREATE PROCEDURE `get_fresh_lun_map`(IN s_id INT)
BEGIN
	DECLARE s_id INT;
	SELECT max(lun_map_unknown.session_id) INTO s_id from lun_map_unknown join sessions on sessions.state = 0;
	SELECT * FROM d81s.lun_map WHERE lun_map.session_id = s_id;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure get_ibts_libraries
-- -----------------------------------------------------

DELIMITER $$
USE `d81s`$$
CREATE DEFINER=`d81s`@`%` PROCEDURE `get_ibts_libraries`(IN s_id INT)
BEGIN
	SELECT * FROM `d81s`.`ibts_libraries` WHERE ibts_libraries.session_id = s_id;
END$$

DELIMITER ;

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