InfestMySQLCreator.sql

-- MySQL Script generated by MySQL Workbench
-- Mon Apr 10 19:32:14 2017
-- 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 infest
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `infest` ;

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

-- -----------------------------------------------------
-- Table `infest`.`tables`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `infest`.`tables` ;

CREATE TABLE IF NOT EXISTS `infest`.`tables` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  `status` INT(2) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  INDEX `idx_table` (`id` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `infest`.`carts`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `infest`.`carts` ;

CREATE TABLE IF NOT EXISTS `infest`.`carts` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `idtable` INT(10) UNSIGNED NOT NULL,
  `note` VARCHAR(255) NOT NULL,
  `date` DATE NOT NULL,
  `time` TIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  INDEX `idx_cart` (`id` ASC),
  INDEX `foreign_idtable_idx` (`idtable` ASC),
  CONSTRAINT `foreign_idtable`
    FOREIGN KEY (`idtable`)
    REFERENCES `infest`.`tables` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `infest`.`features`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `infest`.`features` ;

CREATE TABLE IF NOT EXISTS `infest`.`features` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  `status` INT(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  INDEX `idx_feature` (`id` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Inserting data for table `infest`.`features`
-- -----------------------------------------------------

INSERT INTO `features` VALUES (1,'MAINTENANCE_MODE','',0);
INSERT INTO `features` VALUES (2,'CURRENCY','GBP',0);
INSERT INTO `features` VALUES (3,'OPERATOR_GENERATE_REPORT','',0);
INSERT INTO `features` VALUES (4,'CUSTOMER_PRINT_BILL','',0);
INSERT INTO `features` VALUES (5,'CUSTOMER_SHOW_DURATION','',0);
INSERT INTO `features` VALUES (6,'CUSTOMER_SHOW_IMAGE','',0);


-- -----------------------------------------------------
-- Table `infest`.`menus`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `infest`.`menus` ;

CREATE TABLE IF NOT EXISTS `infest`.`menus` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  `status` INT(1) NOT NULL,
  `status_date` DATE NOT NULL,
  `type` INT(1) NOT NULL,
  `price` INT(10) NOT NULL,
  `stock` INT(10) NOT NULL,
  `duration` TIME NOT NULL,
  `image` MEDIUMBLOB,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  INDEX `idx_menu` (`id` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `infest`.`orders`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `infest`.`orders` ;

CREATE TABLE IF NOT EXISTS `infest`.`orders` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `idcart` INT(10) UNSIGNED NOT NULL,
  `idmenu` INT(10) UNSIGNED NOT NULL,
  `total` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  INDEX `idx_order` (`id` ASC),
  INDEX `foreign_idmenu_idx` (`idcart` ASC),
  INDEX `foreign_idmenu_idx1` (`idmenu` ASC),
  CONSTRAINT `foreign_idcart`
    FOREIGN KEY (`idcart`)
    REFERENCES `infest`.`carts` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `foreign_idmenu`
    FOREIGN KEY (`idmenu`)
    REFERENCES `infest`.`menus` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

SET SQL_MODE = '';
GRANT USAGE ON *.* TO infest;
 DROP USER infest;
SET SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
CREATE USER 'infest' IDENTIFIED BY 'Yogyakarta*1949#';

GRANT ALL ON `infest`.* TO 'infest';

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