restful.sql

-- MySQL Script generated by MySQL Workbench
-- 09/16/16 22:07:46
-- 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 restful
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema restful
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `restful` DEFAULT CHARACTER SET utf8mb4 ;
USE `restful` ;

-- -----------------------------------------------------
-- Table `restful`.`user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `restful`.`user` ;

CREATE TABLE IF NOT EXISTS `restful`.`user` (
  `userId` INT NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `username` VARCHAR(20) NOT NULL COMMENT '用户名',
  `password` CHAR(32) NOT NULL COMMENT '密码',
  `createdAt` INT NOT NULL COMMENT '注册时间',
  PRIMARY KEY (`userId`),
  UNIQUE INDEX `username` (`username` ASC),
  INDEX `createdAt` (`createdAt` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `restful`.`article`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `restful`.`article` ;

CREATE TABLE IF NOT EXISTS `restful`.`article` (
  `articleId` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '文章ID',
  `title` VARCHAR(40) NOT NULL COMMENT '标题',
  `createdAt` INT NOT NULL COMMENT '发表时间',
  `content` TEXT NOT NULL COMMENT '文章内容',
  `userId` INT NOT NULL COMMENT '用户ID',
  PRIMARY KEY (`articleId`),
  INDEX `title` (`title` ASC),
  INDEX `createdAt` (`createdAt` ASC),
  INDEX `fk_article_user_idx` (`userId` ASC),
  CONSTRAINT `fk_article_user`
    FOREIGN KEY (`userId`)
    REFERENCES `restful`.`user` (`userId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `restful`.`session`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `restful`.`session` ;

CREATE TABLE IF NOT EXISTS `restful`.`session` (
  `token` CHAR(8) NOT NULL COMMENT '会话token',
  `createdAt` INT NOT NULL COMMENT '登录时间',
  `expiresAt` INT NOT NULL COMMENT '过期时间',
  `userId` INT NOT NULL COMMENT '用户ID',
  PRIMARY KEY (`token`),
  INDEX `fk_session_user1_idx` (`userId` ASC),
  INDEX `createdAt` (`createdAt` ASC),
  INDEX `expires` (`expiresAt` ASC),
  CONSTRAINT `fk_session_user1`
    FOREIGN KEY (`userId`)
    REFERENCES `restful`.`user` (`userId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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