schema.sql

CREATE TABLE `commander` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `commander_name_uindex` (`name`)
) ENGINE=InnoDB;

CREATE TABLE `incidenttype` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `description` varchar(100) NOT NULL,
  `technical` int(11) DEFAULT '0',
  `hidden` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `incidenttype_description_uindex` (`description`)
) ENGINE=InnoDB;

CREATE TABLE `system` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `x` decimal(10,5) DEFAULT NULL,
  `y` decimal(10,5) DEFAULT NULL,
  `z` decimal(10,5) DEFAULT NULL,
  `notes` varchar(1024) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `system_name_uindex` (`name`)
) ENGINE=InnoDB;

CREATE TABLE `waypoint` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `system` int(11) DEFAULT NULL,
  `trip` int(11) NOT NULL,
  `number` int(11) NOT NULL,
  `commander` int(11) DEFAULT NULL,
  `reached` int(11) NOT NULL DEFAULT '0',
  `sequence` int(11) NOT NULL,
  `special` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `waypoint_system_id_fk` (`system`),
  KEY `waypoint_trip_id_fk` (`trip`),
  KEY `waypoint_commander_id_fk` (`commander`),
  CONSTRAINT `waypoint_commander_id_fk` FOREIGN KEY (`commander`) REFERENCES `commander` (`id`),
  CONSTRAINT `waypoint_system_id_fk` FOREIGN KEY (`system`) REFERENCES `system` (`id`),
  CONSTRAINT `waypoint_trip_id_fk` FOREIGN KEY (`trip`) REFERENCES `trip` (`id`)
) ENGINE=InnoDB;

CREATE TABLE `trip` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `departure` date DEFAULT NULL,
  `from` int(11) DEFAULT NULL,
  `to` int(11) DEFAULT NULL,
  `arrival` date DEFAULT NULL,
  `linkpage` varchar(255) DEFAULT NULL,
  `linktag` varchar(255) DEFAULT NULL,
  `departurestation` varchar(100) DEFAULT NULL,
  `arrivalstation` varchar(100) DEFAULT NULL,
  `jumprange` decimal(10,5) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `trip_system_id_fk` (`from`),
  KEY `trip_system_to_id_fk` (`to`),
  CONSTRAINT `trip_system_id_fk` FOREIGN KEY (`from`) REFERENCES `system` (`id`),
  CONSTRAINT `trip_system_to_id_fk` FOREIGN KEY (`to`) REFERENCES `waypoint` (`id`)
) ENGINE=InnoDB;

CREATE TABLE `session` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `trip` int(11) NOT NULL,
  `nextwaypoint` int(11) DEFAULT NULL,
  `currentsystem` int(11) DEFAULT NULL,
  `jumpsmade` int(11) DEFAULT NULL,
  `madness` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `session_trip_id_fk` (`trip`),
  KEY `session_system_id_fk` (`currentsystem`),
  KEY `session_waypoint_id_fk` (`nextwaypoint`),
  CONSTRAINT `session_system_id_fk` FOREIGN KEY (`currentsystem`) REFERENCES `system` (`id`),
  CONSTRAINT `session_trip_id_fk` FOREIGN KEY (`trip`) REFERENCES `trip` (`id`),
  CONSTRAINT `session_waypoint_id_fk` FOREIGN KEY (`nextwaypoint`) REFERENCES `waypoint` (`id`)
) ENGINE=InnoDB;

CREATE TABLE `incident` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `session` int(11) NOT NULL,
  `type` int(11) NOT NULL,
  `delta` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `incident_session_id_fk` (`session`),
  KEY `incident_incidenttype_id_fk` (`type`),
  CONSTRAINT `incident_incidenttype_id_fk` FOREIGN KEY (`type`) REFERENCES `incidenttype` (`id`),
  CONSTRAINT `incident_session_id_fk` FOREIGN KEY (`session`) REFERENCES `session` (`id`)
) ENGINE=InnoDB;