Database

The data is going to be collected and stored in mysql database. Below is a representation of the tables and relations.

These are the mysql statements :

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 `BGPstorage` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `BGPstorage` ;

-- -----------------------------------------------------
-- Table `BGPstorage`.`as_detail`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `BGPstorage`.`as_detail` (
  `AS_No` INT NOT NULL ,
  `AS_Name` VARCHAR(45) NULL ,
  `AS_Location` VARCHAR(45) NULL ,
  PRIMARY KEY (`AS_No`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `BGPstorage`.`peer_detail`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `BGPstorage`.`peer_detail` (
  `Peer_IP` VARCHAR(45) NOT NULL ,
  `Peer_Location` VARCHAR(45) NULL ,
  `Peer_IP_Name` VARCHAR(45) NULL ,
  `Peer_AS` INT NULL ,
  PRIMARY KEY (`Peer_IP`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `BGPstorage`.`update_detail`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `BGPstorage`.`update_detail` (
  `Update_ID` INT NOT NULL ,
  `Time` VARCHAR(45) NOT NULL ,
  `Type` VARCHAR(45) NULL ,
  `Peer_IP` VARCHAR(45) NULL ,
  PRIMARY KEY (`Update_ID`, `Time`) ,
  INDEX `Peer_IP_PD_idx` (`Peer_IP` ASC) ,
  CONSTRAINT `Peer_IP_fk`
    FOREIGN KEY (`Peer_IP` )
    REFERENCES `BGPstorage`.`peer_detail` (`Peer_IP` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `BGPstorage`.`announce_update`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `BGPstorage`.`announce_update` (
  `Announce_UpdateID` INT NOT NULL AUTO_INCREMENT ,
  `Prefix` VARCHAR(45) NULL ,
  `Update_ID` INT NULL ,
  PRIMARY KEY (`Announce_UpdateID`) ,
  INDEX `Update_ID_fk_idx` (`Update_ID` ASC) ,
  CONSTRAINT `Update_ID_fk_announce`
    FOREIGN KEY (`Update_ID` )
    REFERENCES `BGPstorage`.`update_detail` (`Update_ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `BGPstorage`.`as_path`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `BGPstorage`.`as_path` (
  `AS_Path_ID` INT NOT NULL AUTO_INCREMENT ,
  `Path_Index` INT NULL ,
  `AS_No` INT NULL ,
  `Update_ID` INT NULL ,
  PRIMARY KEY (`AS_Path_ID`) ,
  INDEX `AS_No_fk_idx` (`AS_No` ASC) ,
  INDEX `Update_ID_fk_as_path_idx` (`Update_ID` ASC) ,
  CONSTRAINT `AS_No_fk`
    FOREIGN KEY (`AS_No` )
    REFERENCES `BGPstorage`.`as_detail` (`AS_No` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Update_ID_fk_as_path`
    FOREIGN KEY (`Update_ID` )
    REFERENCES `BGPstorage`.`update_detail` (`Update_ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `BGPstorage`.`withdraw_update`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `BGPstorage`.`withdraw_update` (
  `Withdraw_UpdateID` INT NOT NULL AUTO_INCREMENT ,
  `Prefix` VARCHAR(45) NULL ,
  `Update_ID` INT NULL ,
  PRIMARY KEY (`Withdraw_UpdateID`) ,
  INDEX `Update_ID_fk_idx` (`Update_ID` ASC) ,
  CONSTRAINT `Update_ID_fk_withdraw`
    FOREIGN KEY (`Update_ID` )
    REFERENCES `BGPstorage`.`update_detail` (`Update_ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

USE `BGPstorage` ;


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

=======================================================================

This table may still need some changes but for now, this is what i will use if errors are not encountered during the migration of the dumps.