diff options
Diffstat (limited to 'mysql/efo.sql')
| -rw-r--r-- | mysql/efo.sql | 188 |
1 files changed, 188 insertions, 0 deletions
diff --git a/mysql/efo.sql b/mysql/efo.sql new file mode 100644 index 0000000..7bed513 --- /dev/null +++ b/mysql/efo.sql @@ -0,0 +1,188 @@ +-- MySQL Script generated by MySQL Workbench +-- Wed Jan 24 16:19:01 2018 +-- 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 efo +-- ----------------------------------------------------- +-- 线上文件管理系统 +DROP SCHEMA IF EXISTS `efo` ; + +-- ----------------------------------------------------- +-- Schema efo +-- +-- 线上文件管理系统 +-- ----------------------------------------------------- +CREATE SCHEMA IF NOT EXISTS `efo` DEFAULT CHARACTER SET utf8 ; +USE `efo` ; + +-- ----------------------------------------------------- +-- Table `efo`.`user` +-- ----------------------------------------------------- +DROP TABLE IF EXISTS `efo`.`user` ; + +CREATE TABLE IF NOT EXISTS `efo`.`user` ( + `id` INT NOT NULL AUTO_INCREMENT COMMENT '用户编号', + `username` VARCHAR(16) NOT NULL DEFAULT '' COMMENT '用户名', + `real_name` VARCHAR(45) NULL DEFAULT '' COMMENT '真实姓名', + `email` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '邮箱地址', + `password` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '登录密码', + `permission` INT NOT NULL DEFAULT 1 COMMENT '0（禁止登录），1（正常，普通用户），2（正常，管理员），3（正常，超级管理员）', + `create_time` DATETIME NOT NULL DEFAULT current_timestamp COMMENT '注册时间', + `last_login_time` DATETIME NOT NULL DEFAULT current_timestamp COMMENT '最后一次登录时间', + `is_downloadable` INT NOT NULL DEFAULT 1 COMMENT '（全局权限）用户是否可以下载，0不可以，1可以', + `is_uploadable` INT NOT NULL DEFAULT 1 COMMENT '（全局权限）用户是否可以上传，0不可以，1可以', + `is_visible` INT NOT NULL DEFAULT 1 COMMENT '（全局权限）用户是否可以查看文件，0不可以，1可以', + `is_deletable` INT NOT NULL DEFAULT 0 COMMENT '（全局权限）用户可以删除文件，0不可以，1可以', + `is_updatable` INT NOT NULL DEFAULT 0 COMMENT '（全局权限）用户是否可以更新文件，0不可以，1可以', + `avatar` VARCHAR(255) NULL DEFAULT '' COMMENT '头像', + PRIMARY KEY (`id`), + UNIQUE INDEX `id_UNIQUE` (`id` ASC), + UNIQUE INDEX `username_UNIQUE` (`username` ASC), + UNIQUE INDEX `create_time_UNIQUE` (`create_time` ASC), + UNIQUE INDEX `email_UNIQUE` (`email` ASC)) +ENGINE = InnoDB +COMMENT = '用户表'; + + +-- ----------------------------------------------------- +-- Table `efo`.`category` +-- ----------------------------------------------------- +DROP TABLE IF EXISTS `efo`.`category` ; + +CREATE TABLE IF NOT EXISTS `efo`.`category` ( + `id` INT NOT NULL AUTO_INCREMENT, + `name` VARCHAR(45) NOT NULL DEFAULT '', + `create_time` DATETIME NOT NULL DEFAULT current_timestamp, + PRIMARY KEY (`id`), + UNIQUE INDEX `name_UNIQUE` (`name` ASC), + UNIQUE INDEX `cat_id_UNIQUE` (`id` ASC)) +ENGINE = InnoDB +COMMENT = '文件分类'; + + +-- ----------------------------------------------------- +-- Table `efo`.`file` +-- ----------------------------------------------------- +DROP TABLE IF EXISTS `efo`.`file` ; + +CREATE TABLE IF NOT EXISTS `efo`.`file` ( + `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '编号', + `name` VARCHAR(256) NULL DEFAULT '' COMMENT '文件名', + `suffix` VARCHAR(16) NOT NULL DEFAULT '' COMMENT '文件后缀', + `local_url` VARCHAR(1024) NOT NULL DEFAULT '' COMMENT '本地路径', + `visit_url` VARCHAR(1024) NOT NULL DEFAULT '' COMMENT '客户端访问路径', + `size` BIGINT NOT NULL DEFAULT 0 COMMENT '文件大小，单位bit', + `create_time` DATETIME NOT NULL DEFAULT current_timestamp COMMENT '创建时间', + `description` VARCHAR(1024) NULL DEFAULT '' COMMENT '文件描述', + `check_times` INT NOT NULL DEFAULT 0 COMMENT '查看次数', + `download_times` INT NOT NULL DEFAULT 0 COMMENT '下载次数', + `tag` VARCHAR(45) NULL DEFAULT '' COMMENT '文件标签', + `user_id` INT NOT NULL, + `category_id` INT NOT NULL, + `is_downloadable` INT NOT NULL DEFAULT 1 COMMENT '（全局权限）文件是否可以下载，0不可以，1可以', + `is_uploadable` INT NOT NULL DEFAULT 1 COMMENT '（全局权限）文件夹是否允许上传（需要判断文件是否是文件夹），0不可以，1可以', + `is_visible` INT NOT NULL DEFAULT 1 COMMENT '（全局权限）文件是否可见，0不可以，1可以', + `is_deletable` INT NOT NULL DEFAULT 1 COMMENT '（全局权限）文件是否可以删除，0不可以，1可以', + `is_updatable` INT NOT NULL DEFAULT 1 COMMENT '（全局权限）文件是否可以更新，0不可以，1可以', + `last_modify_time` DATETIME NOT NULL DEFAULT current_timestamp COMMENT '最近一次修改时间', + PRIMARY KEY (`id`), + UNIQUE INDEX `file_id_UNIQUE` (`id` ASC), + INDEX `fk_file_user_idx` (`user_id` ASC), + INDEX `fk_file_category1_idx` (`category_id` ASC), + UNIQUE INDEX `local_url_UNIQUE` (`local_url` ASC), + UNIQUE INDEX `visit_url_UNIQUE` (`visit_url` ASC), + CONSTRAINT `fk_file_user` + FOREIGN KEY (`user_id`) + REFERENCES `efo`.`user` (`id`) + ON DELETE NO ACTION + ON UPDATE NO ACTION, + CONSTRAINT `fk_file_category1` + FOREIGN KEY (`category_id`) + REFERENCES `efo`.`category` (`id`) + ON DELETE NO ACTION + ON UPDATE NO ACTION) +ENGINE = InnoDB +COMMENT = '文件列表'; + + +-- ----------------------------------------------------- +-- Table `efo`.`download` +-- ----------------------------------------------------- +DROP TABLE IF EXISTS `efo`.`download` ; + +CREATE TABLE IF NOT EXISTS `efo`.`download` ( + `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '编号', + `create_time` DATETIME NOT NULL DEFAULT current_timestamp COMMENT '下载时间', + `user_id` INT NOT NULL, + `file_id` BIGINT NOT NULL, + PRIMARY KEY (`id`), + UNIQUE INDEX `id_UNIQUE` (`id` ASC), + INDEX `fk_download_user1_idx` (`user_id` ASC), + INDEX `fk_download_file1_idx` (`file_id` ASC), + CONSTRAINT `fk_download_user1` + FOREIGN KEY (`user_id`) + REFERENCES `efo`.`user` (`id`) + ON DELETE NO ACTION + ON UPDATE NO ACTION, + CONSTRAINT `fk_download_file1` + FOREIGN KEY (`file_id`) + REFERENCES `efo`.`file` (`id`) + ON DELETE NO ACTION + ON UPDATE NO ACTION) +ENGINE = InnoDB +COMMENT = '下载历史表'; + + +-- ----------------------------------------------------- +-- Table `efo`.`auth` +-- ----------------------------------------------------- +DROP TABLE IF EXISTS `efo`.`auth` ; + +CREATE TABLE IF NOT EXISTS `efo`.`auth` ( + `id` BIGINT NOT NULL AUTO_INCREMENT, + `is_uploadable` INT NOT NULL DEFAULT 1 COMMENT '是否可以上传（需要判断对应的文件是否是文件夹），0不可以，1可以', + `is_deletable` INT NOT NULL DEFAULT 1 COMMENT '是否可以删除，0不可以，1可以', + `is_updatable` INT NOT NULL DEFAULT 1 COMMENT '是否可以更新，0不可以，1可以', + `user_id` INT NOT NULL, + `file_id` BIGINT NOT NULL, + `is_visible` INT NOT NULL DEFAULT 1 COMMENT '是否可以查看，0不可以，1可以', + `is_downloadable` INT NOT NULL DEFAULT 1 COMMENT '用户是否可以下载，0不可以，1可以', + `create_time` DATETIME NOT NULL DEFAULT current_timestamp COMMENT '创建时间', + PRIMARY KEY (`id`), + UNIQUE INDEX `id_UNIQUE` (`id` ASC), + INDEX `fk_auth_user1_idx` (`user_id` ASC), + INDEX `fk_auth_file1_idx` (`file_id` ASC), + CONSTRAINT `fk_auth_user1` + FOREIGN KEY (`user_id`) + REFERENCES `efo`.`user` (`id`) + ON DELETE NO ACTION + ON UPDATE NO ACTION, + CONSTRAINT `fk_auth_file1` + FOREIGN KEY (`file_id`) + REFERENCES `efo`.`file` (`id`) + ON DELETE NO ACTION + ON UPDATE NO ACTION) +ENGINE = InnoDB +COMMENT = '用户对应指定文件的权限表，覆盖用户表的权限'; + + +SET SQL_MODE=@OLD_SQL_MODE; +SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; +SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; + +INSERT INTO user(username,real_name,email,password,permission,is_deletable,is_updatable) VALUES("system","系统","[email protected]",sha2("123456",256),3,1,1); + +#请确保数据库中始终有“未分类”这个分类，否则系统运行时有可能出错 +INSERT INTO category(name) VALUES("未分类"); + +DROP USER IF EXISTS 'zhazhapan'@'localhost'; + +CREATE USER 'zhazhapan'@'localhost' IDENTIFIED BY 'zhazhapan'; + +GRANT INSERT, DELETE, UPDATE, SELECT ON efo.* TO 'zhazhapan'@'localhost';
\ No newline at end of file |