326 lines
11 KiB
SQL
326 lines
11 KiB
SQL
/*
|
||
Source Server : 127.0.0.1
|
||
Source Host : localhost:3317
|
||
Source Database : micro
|
||
|
||
Target Server Type : MYSQL
|
||
Target Server Version : 5.7
|
||
File Encoding : utf-8
|
||
Author ： lishouyu
|
||
Email : 18332763730@163.com
|
||
*/
|
||
# 外键检查，不能有外键
|
||
SET FOREIGN_KEY_CHECKS = 0;
|
||
|
||
-- -------------------
|
||
-- 创建micro数据库
|
||
-- -------------------
|
||
CREATE DATABASE IF NOT EXISTS micro
|
||
CHARACTER SET utf8;
|
||
USE micro;
|
||
-- upms用户权限管理系统
|
||
|
||
-- -------------------------
|
||
-- upms_log upms操作日志
|
||
-- -------------------------
|
||
DROP TABLE IF EXISTS upms_log;
|
||
CREATE TABLE `upms_log` (
|
||
`id` INT(10) UNSIGNED AUTO_INCREMENT NOT NULL
|
||
COMMENT '日志编号',
|
||
`descirption` VARCHAR(100) DEFAULT NULL
|
||
COMMENT '操作描述',
|
||
`username` VARCHAR(20) DEFAULT NULL
|
||
COMMENT '操作用户',
|
||
`start_time` BIGINT(20) UNSIGNED DEFAULT NULL
|
||
COMMENT '操作时间',
|
||
`spend_time` INT UNSIGNED DEFAULT NULL
|
||
COMMENT '消耗时间',
|
||
`base_path` VARCHAR(100) DEFAULT NULL
|
||
COMMENT '根路径',
|
||
`uri` VARCHAR(200) DEFAULT NULL
|
||
COMMENT 'URI',
|
||
`url` VARCHAR(200) DEFAULT NULL
|
||
COMMENT 'URL',
|
||
`method` VARCHAR(10) DEFAULT NULL
|
||
COMMENT '请求的类型',
|
||
`parameter` MEDIUMTEXT DEFAULT NULL
|
||
COMMENT '请求参数',
|
||
`user_agent` VARCHAR(200) DEFAULT NULL
|
||
COMMENT '用户标识',
|
||
`ip` VARCHAR(30) DEFAULT NULL
|
||
COMMENT 'ip地址',
|
||
`result` MEDIUMTEXT DEFAULT NULL
|
||
COMMENT '响应结果',
|
||
`permissions` VARCHAR(100) DEFAULT NULL
|
||
COMMENT '权限值',
|
||
`ctime` BIGINT(20) UNSIGNED DEFAULT NULL
|
||
COMMENT '创建时间',
|
||
`uptime` BIGINT(20) UNSIGNED DEFAULT NULL
|
||
COMMENT '修改时间',
|
||
`version` INT(10) UNSIGNED DEFAULT 0
|
||
COMMENT '数据版本',
|
||
PRIMARY KEY (`id`)
|
||
)
|
||
ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8;
|
||
|
||
-- ------------------
|
||
-- upms_user upms用户表
|
||
-- ------------------
|
||
DROP TABLE IF EXISTS upms_user;
|
||
CREATE TABLE upms_user (
|
||
`id` INT(10) UNSIGNED AUTO_INCREMENT NOT NULL
|
||
COMMENT '用户的编号',
|
||
`username` VARCHAR(20) NOT NULL
|
||
COMMENT '用户名',
|
||
`password` VARCHAR(200) NOT NULL
|
||
COMMENT '密码加密后的md5值(密码+盐值）',
|
||
`salt` VARCHAR(32) DEFAULT NULL
|
||
COMMENT '盐值',
|
||
`realname` VARCHAR(20) DEFAULT NULL
|
||
COMMENT '真实姓名',
|
||
`avatar` VARCHAR(150) DEFAULT NULL
|
||
COMMENT '头像',
|
||
`phone` VARCHAR(20) DEFAULT NULL
|
||
COMMENT '电话',
|
||
`email` VARCHAR(50) DEFAULT NULL
|
||
COMMENT '电子邮箱',
|
||
`sex` TINYINT(4) DEFAULT NULL
|
||
COMMENT '性别',
|
||
`locked` TINYINT(4) DEFAULT NULL
|
||
COMMENT '账号状态',
|
||
`ctime` BIGINT(20) UNSIGNED DEFAULT NULL
|
||
COMMENT '创建时间',
|
||
`uptime` BIGINT(20) UNSIGNED DEFAULT NULL
|
||
COMMENT '修改时间',
|
||
`version` INT(10) UNSIGNED DEFAULT 0
|
||
COMMENT '数据版本',
|
||
PRIMARY KEY (`id`)
|
||
)
|
||
ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8;
|
||
|
||
-- --------------------------
|
||
-- upms_organization upms组织表
|
||
-- ---------------------------
|
||
DROP TABLE IF EXISTS upms_organization;
|
||
CREATE TABLE upms_organization (
|
||
`id` INT(10) UNSIGNED AUTO_INCREMENT NOT NULL
|
||
COMMENT '组织的编号',
|
||
`pid` INT(10) UNSIGNED DEFAULT NULL
|
||
COMMENT '所属上级',
|
||
`name` VARCHAR(20) DEFAULT NULL
|
||
COMMENT '组织名称',
|
||
`description` VARCHAR(1000) DEFAULT NULL
|
||
COMMENT '组织描述',
|
||
`ctime` BIGINT(20) UNSIGNED DEFAULT NULL
|
||
COMMENT '创建时间',
|
||
`uptime` BIGINT(20) UNSIGNED DEFAULT NULL
|
||
COMMENT '修改时间',
|
||
`version` INT(10) UNSIGNED DEFAULT 0
|
||
COMMENT '数据版本',
|
||
PRIMARY KEY (`id`)
|
||
)
|
||
ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8;
|
||
|
||
-- ---------------------
|
||
-- upms_role upms角色表
|
||
-- ---------------------
|
||
DROP TABLE IF EXISTS upms_role;
|
||
CREATE TABLE upms_role (
|
||
`id` INT(10) UNSIGNED AUTO_INCREMENT NOT NULL
|
||
COMMENT '角色编号',
|
||
`name` VARCHAR(20) DEFAULT NULL
|
||
COMMENT '角色名称',
|
||
`description` VARCHAR(1000) DEFAULT NULL
|
||
COMMENT '角色描述',
|
||
`orders` BIGINT(20) UNSIGNED DEFAULT NULL
|
||
COMMENT '排序',
|
||
`ctime` BIGINT(20) UNSIGNED DEFAULT NULL
|
||
COMMENT '创建时间',
|
||
`uptime` BIGINT(20) UNSIGNED DEFAULT NULL
|
||
COMMENT '修改时间',
|
||
`version` INT(10) UNSIGNED DEFAULT 0
|
||
COMMENT '数据版本',
|
||
PRIMARY KEY (`id`)
|
||
)
|
||
ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8;
|
||
|
||
-- ---------------------------
|
||
-- upms_permission upms权限
|
||
-- ---------------------------
|
||
DROP TABLE IF EXISTS upms_permission;
|
||
CREATE TABLE upms_permission (
|
||
`id` INT(10) UNSIGNED AUTO_INCREMENT NOT NULL
|
||
COMMENT '权限编号',
|
||
`system_id` INT(10) UNSIGNED DEFAULT NULL
|
||
COMMENT '所属系统',
|
||
`pid` INT(10) UNSIGNED DEFAULT NULL
|
||
COMMENT '所属上级',
|
||
`name` VARCHAR(20) DEFAULT NULL
|
||
COMMENT '名称',
|
||
`description` VARCHAR(200) DEFAULT NULL
|
||
COMMENT '权限描述',
|
||
`type` TINYINT(4) DEFAULT NULL
|
||
COMMENT '类型 1.视图授权2.数据授权',
|
||
`permission_value` VARCHAR(50) DEFAULT NULL
|
||
COMMENT '权限值',
|
||
`uri` VARCHAR(100) DEFAULT NULL
|
||
COMMENT '路径',
|
||
`icon` VARCHAR(50) DEFAULT NULL
|
||
COMMENT '图标',
|
||
`status` TINYINT(4) DEFAULT NULL
|
||
COMMENT '状态 0 禁止 1 正常',
|
||
`orders` BIGINT(20) UNSIGNED DEFAULT NULL
|
||
COMMENT '排序',
|
||
`ctime` BIGINT(20) UNSIGNED DEFAULT NULL
|
||
COMMENT '创建时间',
|
||
`uptime` BIGINT(20) UNSIGNED DEFAULT NULL
|
||
COMMENT '修改时间',
|
||
`version` INT(10) UNSIGNED DEFAULT 0
|
||
COMMENT '数据版本',
|
||
PRIMARY KEY (`id`),
|
||
KEY `upms_permission_system_id_index` (`system_id`) USING BTREE COMMENT 'B+树索引'
|
||
)
|
||
ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8;
|
||
|
||
-- -------------------------
|
||
-- upms_system upms系统表
|
||
-- -------------------------
|
||
DROP TABLE IF EXISTS upms_system;
|
||
CREATE TABLE upms_system (
|
||
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
|
||
COMMENT '系统编号',
|
||
`icon` VARCHAR(50) DEFAULT NULL
|
||
COMMENT '图标',
|
||
`banner` VARCHAR(150) DEFAULT NULL
|
||
COMMENT '背景',
|
||
`theme` VARCHAR(50) DEFAULT NULL
|
||
COMMENT '主题',
|
||
`basepath` VARCHAR(100) DEFAULT NULL
|
||
COMMENT '根目录',
|
||
`status` TINYINT(4) DEFAULT NULL
|
||
COMMENT '状态 0 黑名单 1 正常',
|
||
`name` VARCHAR(20) DEFAULT NULL
|
||
COMMENT '系统名称',
|
||
`route` VARCHAR(20) DEFAULT NULL
|
||
COMMENT '系统路由',
|
||
`title` VARCHAR(20) DEFAULT NULL
|
||
COMMENT '系统标题',
|
||
`description` VARCHAR(300) DEFAULT NULL
|
||
COMMENT '系统描述',
|
||
`orders` BIGINT(20) UNSIGNED DEFAULT NULL
|
||
COMMENT '排序',
|
||
`ctime` BIGINT(20) UNSIGNED DEFAULT NULL
|
||
COMMENT '创建时间',
|
||
`uptime` BIGINT(20) UNSIGNED DEFAULT NULL
|
||
COMMENT '修改时间',
|
||
`version` INT(10) UNSIGNED DEFAULT 0
|
||
COMMENT '数据版本',
|
||
PRIMARY KEY (`id`)
|
||
)
|
||
ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8;
|
||
# 关联关系表
|
||
-- --------------------------
|
||
-- upms_user_organization 用户_组织关联表
|
||
-- --------------------------
|
||
DROP TABLE IF EXISTS upms_user_organization;
|
||
CREATE TABLE upms_user_organization (
|
||
`id` INT(10) UNSIGNED AUTO_INCREMENT NOT NULL
|
||
COMMENT '编号',
|
||
`user_id` INT(10) UNSIGNED NOT NULL
|
||
COMMENT '用户编号',
|
||
`organization_id` INT(10) UNSIGNED NOT NULL
|
||
COMMENT '组织编号',
|
||
`ctime` BIGINT(20) UNSIGNED DEFAULT NULL
|
||
COMMENT '创建时间',
|
||
`uptime` BIGINT(20) UNSIGNED DEFAULT NULL
|
||
COMMENT '修改时间',
|
||
`version` INT(10) UNSIGNED DEFAULT 0
|
||
COMMENT '数据版本',
|
||
PRIMARY KEY (`id`),
|
||
KEY `upms_user_organization_user_id_index` (`user_id`) USING BTREE COMMENT 'B+树索引',
|
||
KEY `upms_user_organization_organization_id_index` (`organization_id`) USING BTREE COMMENT 'B+树索引'
|
||
)
|
||
ENGINE = InnoDB
|
||
DEFAULT CHARACTER SET = utf8;
|
||
|
||
-- ----------------------------
|
||
-- upms_user_role 用户_角色关联表
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS upms_user_role;
|
||
CREATE TABLE upms_user_role (
|
||
`id` INT(10) UNSIGNED AUTO_INCREMENT NOT NULL
|
||
COMMENT '编号',
|
||
`user_id` INT(10) UNSIGNED NOT NULL
|
||
COMMENT '用户编号',
|
||
`role_id` INT(10) UNSIGNED NOT NULL
|
||
COMMENT '角色编号',
|
||
`ctime` BIGINT(20) UNSIGNED DEFAULT NULL
|
||
COMMENT '创建时间',
|
||
`uptime` BIGINT(20) UNSIGNED DEFAULT NULL
|
||
COMMENT '修改时间',
|
||
`version` INT(10) UNSIGNED DEFAULT 0
|
||
COMMENT '数据版本',
|
||
PRIMARY KEY (`id`),
|
||
KEY ` upms_user_role_user_id_index` (`user_id`) USING BTREE COMMENT 'B+树索引',
|
||
KEY ` upms_user_role_role_id_index` (`role_id`) USING BTREE COMMENT 'B+树索引'
|
||
)
|
||
ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8;
|
||
|
||
# -- ----------------------------------
|
||
# -- upms_user_permission 用户_权限关联表
|
||
# -- ----------------------------------
|
||
# DROP TABLE IF EXISTS upms_user_permission;
|
||
# DROP TABLE IF EXISTS upms_user_permissioon;
|
||
# CREATE TABLE upms_user_permission (
|
||
# `id` INT(10) UNSIGNED AUTO_INCREMENT NOT NULL
|
||
# COMMENT '编号',
|
||
# `user_id` INT(10) UNSIGNED NOT NULL
|
||
# COMMENT '用户编号',
|
||
# `permission_id` INT(10) UNSIGNED NOT NULL
|
||
# COMMENT '权限编号',
|
||
# `type` TINYINT(4) NOT NULL
|
||
# COMMENT '权限类型 -1 减权限',
|
||
# `ctime` BIGINT(20) UNSIGNED DEFAULT NULL
|
||
# COMMENT '创建时间',
|
||
# `uptime` BIGINT(20) UNSIGNED DEFAULT NULL
|
||
# COMMENT '修改时间',
|
||
# `version` INT(10) UNSIGNED DEFAULT 0
|
||
# COMMENT '数据版本',
|
||
# PRIMARY KEY (`id`),
|
||
# KEY `upms_user_permission_user_id_index` (`user_id`) USING BTREE COMMENT 'B+树索引',
|
||
# KEY `upms_user_permission_permission_id_index` (`permission_id`) USING BTREE COMMENT 'B+树索引'
|
||
# )
|
||
# ENGINE = InnoDB
|
||
# DEFAULT CHARSET utf8;
|
||
|
||
-- ----------------------------------
|
||
-- upms_role_permission 角色_权限关联表
|
||
-- ----------------------------------
|
||
DROP TABLE IF EXISTS upms_role_permission;
|
||
CREATE TABLE upms_role_permission (
|
||
`id` INT(10) UNSIGNED AUTO_INCREMENT NOT NULL
|
||
COMMENT '编号',
|
||
`role_id` INT(10) UNSIGNED NOT NULL
|
||
COMMENT '角色编号',
|
||
`permission_id` INT(10) UNSIGNED NOT NULL
|
||
COMMENT '权限编号',
|
||
`ctime` BIGINT(20) UNSIGNED DEFAULT NULL
|
||
COMMENT '创建时间',
|
||
`uptime` BIGINT(20) UNSIGNED DEFAULT NULL
|
||
COMMENT '修改时间',
|
||
`version` INT(10) UNSIGNED DEFAULT 0
|
||
COMMENT '数据版本',
|
||
PRIMARY KEY (`id`),
|
||
KEY `upms_role_permission_role_id_index` (`role_id`) USING BTREE COMMENT 'B+树索引',
|
||
KEY `upms_role_permission_permission_id_index` (`permission_id`) USING BTREE COMMENT 'B+树索引'
|
||
)
|
||
ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8;
|