--
-- Host: 127.0.1    Database: difxdb
-- ------------------------------------------------------


--
-- Current Database: `difxdb`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `difxdb` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `difxdb`;

--
-- Table structure for table `Experiment`
--

CREATE TABLE `Experiment` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(20) NOT NULL,
  `number` int(4) unsigned zerofill NOT NULL,
  `statusID` bigint(20) unsigned NOT NULL DEFAULT '7',
  `directory` varchar(255) DEFAULT NULL,
  `vexfile` varchar(100) DEFAULT NULL,
  `dateCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`),
  KEY `statusID` (`statusID`),
  CONSTRAINT `Experiment_ibfk_1` FOREIGN KEY (`statusID`) REFERENCES `ExperimentStatus` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=180 DEFAULT CHARSET=utf8;

--
-- Table structure for table `ExperimentAndModule`
--

CREATE TABLE `ExperimentAndModule` (
  `experimentID` bigint(20) unsigned NOT NULL,
  `moduleID` bigint(20) unsigned NOT NULL,
  KEY `experimentID` (`experimentID`),
  KEY `moduleID` (`moduleID`),
  CONSTRAINT `ExperimentAndModule_ibfk_1` FOREIGN KEY (`experimentID`) REFERENCES `Experiment` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `ExperimentAndModule_ibfk_2` FOREIGN KEY (`moduleID`) REFERENCES `Module` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `ExperimentStatus`
--

CREATE TABLE `ExperimentStatus` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `statuscode` int(11) NOT NULL,
  `experimentstatus` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `statuscode` (`statuscode`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

--
-- Table structure for table `Job`
--

CREATE TABLE `Job` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `passID` bigint(20) unsigned NOT NULL,
  `jobNumber` int(11) NOT NULL,
  `queueTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `priority` int(11) NOT NULL DEFAULT '0',
  `correlationStart` timestamp NULL DEFAULT NULL,
  `correlationEnd` timestamp NULL DEFAULT NULL,
  `jobStart` float NOT NULL,
  `jobDuration` float NOT NULL,
  `inputFile` varchar(255) NOT NULL,
  `outputFile` varchar(255) DEFAULT NULL,
  `outputSize` bigint(20) DEFAULT NULL,
  `difxVersion` varchar(30) NOT NULL,
  `speedupFactor` float DEFAULT NULL,
  `numAntennas` int(11) NOT NULL,
  `numForeign` int(11) NOT NULL,
  `dutyCycle` float DEFAULT NULL,
  `statusID` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `statusID` (`statusID`),
  KEY `passID` (`passID`),
  CONSTRAINT `Job_ibfk_1` FOREIGN KEY (`passID`) REFERENCES `Pass` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `Job_ibfk_2` FOREIGN KEY (`statusID`) REFERENCES `JobStatus` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `JobStatus`
--

CREATE TABLE `JobStatus` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `status` varchar(100) NOT NULL,
  `active` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

--
-- Table structure for table `Module`
--

CREATE TABLE `Module` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `vsn` varchar(8) NOT NULL,
  `capacity` int(11) NOT NULL,
  `datarate` int(11) NOT NULL,
  `numScans` int(11) DEFAULT NULL,
  `stationCode` varchar(2) DEFAULT NULL,
  `comment` text,
  `received` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `shipped` timestamp NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `vsn` (`vsn`)
) ENGINE=InnoDB AUTO_INCREMENT=394 DEFAULT CHARSET=utf8;

--
-- Table structure for table `Pass`
--

CREATE TABLE `Pass` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `experimentID` bigint(20) unsigned NOT NULL,
  `passName` varchar(30) NOT NULL,
  `passTypeID` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `passTypeID` (`passTypeID`),
  KEY `experimentID` (`experimentID`),
  CONSTRAINT `Pass_ibfk_1` FOREIGN KEY (`experimentID`) REFERENCES `Experiment` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `Pass_ibfk_2` FOREIGN KEY (`passTypeID`) REFERENCES `PassType` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Table structure for table `PassType`
--

CREATE TABLE `PassType` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Table structure for table `Slot`
--

CREATE TABLE `Slot` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `moduleID` bigint(20) unsigned DEFAULT NULL,
  `location` varchar(30) NOT NULL,
  `isActive` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `location` (`location`),
  UNIQUE KEY `moduleID` (`moduleID`),
  CONSTRAINT `Slot_ibfk_1` FOREIGN KEY (`moduleID`) REFERENCES `Module` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=563 DEFAULT CHARSET=utf8;

--
-- Table structure for table `VersionHistory`
--

CREATE TABLE `VersionHistory` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `major` int(11) NOT NULL,
  `minor` int(11) NOT NULL,
  `dateCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


--
-- Host: 127.0.1    Database: difxdb
-- ------------------------------------------------------


--
-- Dumping data for table `ExperimentStatus`
--

INSERT INTO `ExperimentStatus` (`id`, `statuscode`, `experimentstatus`) VALUES (1,10,'scheduled'),(2,20,'waiting for correlation'),(3,30,'started correlation'),(4,40,'finished correlation'),(5,100,'released'),(6,110,'correlated elsewhere'),(7,0,'unknown'),(8,200,'aborted'),(9,25,'waiting for data');

--
-- Dumping data for table `JobStatus`
--

INSERT INTO `JobStatus` (`id`, `status`, `active`) VALUES (1,'unknown',0),(2,'queued',1),(3,'running',1),(4,'complete',0),(5,'killed',0),(6,'failed',0);

--
-- Dumping data for table `PassType`
--

INSERT INTO `PassType` (`id`, `type`) VALUES (1,'production'),(2,'clock'),(3,'test');



INSERT INTO `difxdb`.`VersionHistory` (
`major` ,
`minor`
)
VALUES (
'1', '0'
);

