EasyChat.sql

-- MySQL Script generated by MySQL Workbench
-- 10/19/15 22:54:27
-- 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 EasyChat
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema EasyChat
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `EasyChat` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `EasyChat` ;

-- -----------------------------------------------------
-- Table `EasyChat`.`User`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `EasyChat`.`User` ;

CREATE TABLE IF NOT EXISTS `EasyChat`.`User` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '',
  `sex` INT(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '0未知，1男，2女',
  `name` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '用户名',
  `nick` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '昵称',
  `password` VARCHAR(45) NOT NULL DEFAULT '' COMMENT '密码',
  `salt` VARCHAR(4) NOT NULL DEFAULT '' COMMENT '混淆码',
  `phone` VARCHAR(11) NOT NULL DEFAULT '' COMMENT '',
  `email` VARCHAR(45) NOT NULL DEFAULT '' COMMENT '',
  `avatar` VARCHAR(256) NOT NULL DEFAULT '' COMMENT '头像路径',
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',
  `updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '',
  `sign_info` VARCHAR(140) NOT NULL DEFAULT '' COMMENT '个性签名',
  PRIMARY KEY (`id`)  COMMENT '',
  INDEX `idx` (`name` ASC, `phone` ASC)  COMMENT '',
  UNIQUE INDEX `name_UNIQUE` (`name` ASC)  COMMENT '')
ENGINE = InnoDB
COMMENT = '用户信息表';


-- -----------------------------------------------------
-- Table `EasyChat`.`FriendRelationship`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `EasyChat`.`FriendRelationship` ;

CREATE TABLE IF NOT EXISTS `EasyChat`.`FriendRelationship` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '',
  `aid` INT UNSIGNED NOT NULL COMMENT '用户a的ID',
  `bid` INT UNSIGNED NOT NULL COMMENT '用户B的ID',
  PRIMARY KEY (`id`)  COMMENT '',
  INDEX `fk_aid_idx` (`aid` ASC)  COMMENT '',
  INDEX `fk_bid_idx` (`bid` ASC)  COMMENT '',
  CONSTRAINT `fk_aid`
    FOREIGN KEY (`aid`)
    REFERENCES `EasyChat`.`User` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_bid`
    FOREIGN KEY (`bid`)
    REFERENCES `EasyChat`.`User` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = '好友关系表';


-- -----------------------------------------------------
-- Table `EasyChat`.`Group`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `EasyChat`.`Group` ;

CREATE TABLE IF NOT EXISTS `EasyChat`.`Group` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '',
  `name` VARCHAR(45) NOT NULL DEFAULT '' COMMENT '',
  `avatar` VARCHAR(256) NOT NULL DEFAULT '' COMMENT '群头像',
  `creator` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '创建者',
  `userCnt` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '成员人数',
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `announcement` VARCHAR(140) NULL DEFAULT '' COMMENT '公告',
  PRIMARY KEY (`id`)  COMMENT '')
ENGINE = InnoDB
COMMENT = '群组信息表';


-- -----------------------------------------------------
-- Table `EasyChat`.`GroupRelationship`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `EasyChat`.`GroupRelationship` ;

CREATE TABLE IF NOT EXISTS `EasyChat`.`GroupRelationship` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '',
  `groupId` INT UNSIGNED NOT NULL COMMENT '群组ID',
  `userId` INT UNSIGNED NOT NULL COMMENT '用户ID',
  PRIMARY KEY (`id`)  COMMENT '',
  INDEX `fk_user_idx` (`userId` ASC)  COMMENT '',
  INDEX `fk_group_idx` (`groupId` ASC)  COMMENT '',
  CONSTRAINT `fk_user_r`
    FOREIGN KEY (`userId`)
    REFERENCES `EasyChat`.`User` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_group_r`
    FOREIGN KEY (`groupId`)
    REFERENCES `EasyChat`.`Group` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = '群组关系表';


-- -----------------------------------------------------
-- Table `EasyChat`.`GroupMessage`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `EasyChat`.`GroupMessage` ;

CREATE TABLE IF NOT EXISTS `EasyChat`.`GroupMessage` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '',
  `groupId` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '群组ID',
  `userId` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户ID',
  `msgId` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '消息ID',
  `content` VARCHAR(4096) NOT NULL DEFAULT '' COMMENT '消息内容',
  `type` INT(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '消息类型',
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '消息创建时间',
  PRIMARY KEY (`id`)  COMMENT '',
  INDEX `fk_user_idx` (`userId` ASC)  COMMENT '',
  INDEX `fk_group_idx` (`groupId` ASC)  COMMENT '',
  CONSTRAINT `fk_user_m`
    FOREIGN KEY (`userId`)
    REFERENCES `EasyChat`.`User` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_group_m`
    FOREIGN KEY (`groupId`)
    REFERENCES `EasyChat`.`Group` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = '群聊消息表';


-- -----------------------------------------------------
-- Table `EasyChat`.`Message`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `EasyChat`.`Message` ;

CREATE TABLE IF NOT EXISTS `EasyChat`.`Message` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '',
  `fromId` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '发送方ID',
  `toId` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '接收方ID',
  `msgId` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '消息ID',
  `content` VARCHAR(4096) NOT NULL DEFAULT '' COMMENT '消息内容',
  `type` INT(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '消息类型',
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '消息创建时间',
  PRIMARY KEY (`id`)  COMMENT '',
  INDEX `fk_user_idx` (`fromId` ASC)  COMMENT '',
  INDEX `fk_to_idx` (`toId` ASC)  COMMENT '',
  CONSTRAINT `fk_from`
    FOREIGN KEY (`fromId`)
    REFERENCES `EasyChat`.`User` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_to`
    FOREIGN KEY (`toId`)
    REFERENCES `EasyChat`.`User` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = '单聊消息表';


-- -----------------------------------------------------
-- Table `EasyChat`.`Admin`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `EasyChat`.`Admin` ;

CREATE TABLE IF NOT EXISTS `EasyChat`.`Admin` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '',
  `name` VARCHAR(45) NOT NULL DEFAULT '' COMMENT '',
  `password` VARCHAR(45) NOT NULL DEFAULT '' COMMENT '',
  `updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '',
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',
  PRIMARY KEY (`id`)  COMMENT '',
  UNIQUE INDEX `name_UNIQUE` (`name` ASC)  COMMENT '')
ENGINE = InnoDB
COMMENT = '管理员表';


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