linuxconf.sql

-- MySQL Script generated by MySQL Workbench
-- Mon 25 Jun 2018 16:11:14 BST
-- Model: New Model    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 linuxconf
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema linuxconf
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `linuxconf` DEFAULT CHARACTER SET utf8 ;
USE `linuxconf` ;

-- -----------------------------------------------------
-- Table `linuxconf`.`contributor`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `linuxconf`.`contributor` (
  `email` (255) NOT NULL,
  `url` (32) NOT NULL,
  `description` VARCHAR(512) NULL DEFAULT CURRENT_TIMESTAMP,
  `owner_git_id` VARCHAR(45) NULL,
  PRIMARY KEY (`email`));


-- -----------------------------------------------------
-- Table `linuxconf`.`devices`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `linuxconf`.`devices` (
  `device_id` MEDIUMTEXT NOT NULL,
  `owner_git_id` INT(11) NOT NULL,
  `distribution` VARCHAR(45) NOT NULL,
  `linux_version` VARCHAR(45) NOT NULL,
  `kernel_version` VARCHAR(45) NOT NULL,
  `git_url` VARCHAR(200) NULL,
  `git_commit` VARCHAR(45) NULL,
  `contributor_email` (255) NOT NULL,
  `upvotes` INT(11) NULL,
  `downvotes` INT(11) NULL,
  PRIMARY KEY (`device_id`, `owner_git_id`, `contributor_email`, `linux_version`, `kernel_version`, `distribution`),
  UNIQUE INDEX `device_id_UNIQUE` (`device_id` ASC),
  INDEX `fk_devices_contributor_idx` (`contributor_email` ASC),
  CONSTRAINT `fk_devices_contributor`
    FOREIGN KEY (`contributor_email`)
    REFERENCES `linuxconf`.`contributor` (`email`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


-- -----------------------------------------------------
-- Table `linuxconf`.`success_code`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `linuxconf`.`success_code` (
  `success_code` VARCHAR(64) NOT NULL,
  `devices_device_id` MEDIUMTEXT NOT NULL,
  `devices_owner_git_id` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`success_code`, `devices_device_id`, `devices_owner_git_id`),
  INDEX `fk_success_code_devices1_idx` (`devices_device_id` ASC, `devices_owner_git_id` ASC),
  CONSTRAINT `fk_success_code_devices1`
    FOREIGN KEY (`devices_device_id` , `devices_owner_git_id`)
    REFERENCES `linuxconf`.`devices` (`device_id` , `owner_git_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


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