unrepeatable.sql

-- MySQL Script generated by MySQL Workbench
-- 02/19/17 14:54:28
-- 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 unrepeatable
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `m1397_unrepeatable` ;

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

-- -----------------------------------------------------
-- Table `unrepeatable`.`keys`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `m1397_unrepeatable`.`keys` ;

CREATE TABLE IF NOT EXISTS `m1397_unrepeatable`.`keys` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `secret` VARCHAR(80) NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `created` INT UNSIGNED NOT NULL DEFAULT 0,
  `disabled` TINYINT(1) NOT NULL DEFAULT 0,
  `distance` DOUBLE NOT NULL DEFAULT 0,
  `seed_latitude` DOUBLE NOT NULL DEFAULT 0,
  `seed_longitude` DOUBLE NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

CREATE UNIQUE INDEX `id_UNIQUE` ON `m1397_unrepeatable`.`keys` (`id` ASC);

CREATE UNIQUE INDEX `identifier_UNIQUE` ON `m1397_unrepeatable`.`keys` (`secret` ASC);

CREATE UNIQUE INDEX `name_UNIQUE` ON `m1397_unrepeatable`.`keys` (`name` ASC);


-- -----------------------------------------------------
-- Table `unrepeatable`.`providers`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `m1397_unrepeatable`.`providers` ;

CREATE TABLE IF NOT EXISTS `m1397_unrepeatable`.`providers` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `identifier` VARCHAR(80) NOT NULL,
  `name` VARCHAR(80) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

CREATE UNIQUE INDEX `id_UNIQUE` ON `m1397_unrepeatable`.`providers` (`id` ASC);


-- -----------------------------------------------------
-- Table `unrepeatable`.`users`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `m1397_unrepeatable`.`users` ;

CREATE TABLE IF NOT EXISTS `m1397_unrepeatable`.`users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `fd_id` INT UNSIGNED NOT NULL,
  `profile_picture` TINYTEXT NULL DEFAULT NULL,
  `name` VARCHAR(120) NULL,
  `anonymous` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

CREATE UNIQUE INDEX `user_fb_id_UNIQUE` ON `m1397_unrepeatable`.`users` (`fd_id` ASC);


-- -----------------------------------------------------
-- Table `unrepeatable`.`posts`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `m1397_unrepeatable`.`posts` ;

CREATE TABLE IF NOT EXISTS `m1397_unrepeatable`.`posts` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `key_id` INT UNSIGNED NOT NULL,
  `created_by` INT UNSIGNED NOT NULL,
  `created` INT UNSIGNED NOT NULL DEFAULT 0,
  `latitude` DOUBLE NOT NULL DEFAULT 0,
  `longitude` DOUBLE NOT NULL DEFAULT 0,
  `location_name` VARCHAR(120) NOT NULL,
  `provider_id` INT UNSIGNED NOT NULL DEFAULT 0,
  `provider_source` TEXT NOT NULL,
  `description` TEXT NULL,
  PRIMARY KEY (`id`, `key_id`, `provider_id`, `created_by`),
  CONSTRAINT `FK_KEY_POSTS`
    FOREIGN KEY (`key_id`)
    REFERENCES `m1397_unrepeatable`.`keys` (`id`)
    ON DELETE CASCADE
    ON UPDATE RESTRICT,
  CONSTRAINT `FK_PROVIDER_POSTS`
    FOREIGN KEY (`provider_id`)
    REFERENCES `m1397_unrepeatable`.`providers` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fK_USER_POSTS`
    FOREIGN KEY (`created_by`)
    REFERENCES `m1397_unrepeatable`.`users` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE UNIQUE INDEX `id_UNIQUE` ON `m1397_unrepeatable`.`posts` (`id` ASC);

CREATE UNIQUE INDEX `created_by_UNIQUE` ON `m1397_unrepeatable`.`posts` (`created_by` ASC);

CREATE INDEX `FK_KEY_POSTS_idx` ON `m1397_unrepeatable`.`posts` (`key_id` ASC);

CREATE INDEX `FK_PROVIDER_POSTS_idx` ON `m1397_unrepeatable`.`posts` (`provider_id` ASC);