repair_lite/database/sql/repair_lite.sql

/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     2019/3/15 16:42:14                           */
/*==============================================================*/


drop table if exists action_bars;

drop table if exists group_users;

drop table if exists groups;

drop table if exists roles;

drop table if exists user_action_bars;

drop table if exists user_roles;

drop table if exists users;

/*==============================================================*/
/* Table: action_bars                                           */
/*==============================================================*/
create table action_bars
(
   id                   int not null auto_increment,
   parent               int default 0 comment '该层菜单的上级菜单,第一层的上级菜单是0',
   name                 varchar(128),
   description          varchar(255),
   icon                 varchar(255),
   link_type            char(32) comment '定义link的含义，可以是跳转，可以是发起请求',
   link                 char(255),
   status               char(32) default 'normal' comment '状态表，可能用户编辑了这个菜单，但是没有编辑完，所以只是存了一份草稿',
   created_at           timestamp default CURRENT_TIMESTAMP,
   updated_at           timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   created_user_id      int,
   updated_user_id      int,
   is_del               bool default false,
   primary key (id)
);

alter table action_bars comment '下拉菜单';

/*==============================================================*/
/* Table: group_users                                           */
/*==============================================================*/
create table group_users
(
   id                   int not null auto_increment,
   group_id             int,
   user_id              int,
   status               char(32) default 'normal',
   created_at           timestamp default CURRENT_TIMESTAMP,
   updated_at           timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   created_user_id      int,
   updated_user_id      int,
   is_del               bool default false,
   primary key (id)
);

alter table group_users comment '用户组和用户的关联表';

/*==============================================================*/
/* Table: groups                                                */
/*==============================================================*/
create table groups
(
   id                   int not null auto_increment,
   name                 varchar(128),
   description          varchar(255),
   icon                 varchar(255),
   status               char(32) default 'normal',
   created_at           timestamp default CURRENT_TIMESTAMP,
   updated_at           timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   created_user_id      int,
   updated_user_id      int,
   is_del               bool default false,
   primary key (id)
);

alter table groups comment '用户组';

/*==============================================================*/
/* Table: roles                                                 */
/*==============================================================*/
create table roles
(
   id                   int not null auto_increment,
   name                 varchar(128) comment '角色的名字',
   description          varchar(255) comment '角色的描述',
   status               char(32) comment '状态信息',
   created_at           timestamp default CURRENT_TIMESTAMP,
   updated_at           timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   created_user_at      int,
   updated_user_at      int,
   is_del               bool default false,
   primary key (id)
);

alter table roles comment '用户角色表';

/*==============================================================*/
/* Table: user_action_bars                                      */
/*==============================================================*/
create table user_action_bars
(
   id                   int not null auto_increment,
   owner_type           char(16) default 'user' comment '拥有者的类型，值: user/group/role',
   owner_id             int,
   bar_id               int comment 'action bar的id',
   status               char(32) default 'normal' comment '状态信息，比如角色是否生效的状态',
   created_at           timestamp default CURRENT_TIMESTAMP comment '创建时间',
   updated_at           timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '最后更新时间',
   created_user_id      int,
   updated_user_id      int,
   is_del               bool default false comment '删除标志',
   primary key (id)
);

alter table user_action_bars comment '下拉菜单权限表';

/*==============================================================*/
/* Table: user_roles                                            */
/*==============================================================*/
create table user_roles
(
   id                   int not null auto_increment comment '用户id',
   user_id              int,
   role_id              int comment '用户角色id',
   status               char(32) default 'normal',
   created_at           timestamp default CURRENT_TIMESTAMP,
   updated_at           timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   created_user_id      int,
   updated_user_id      int,
   is_del               bool default false,
   primary key (id)
);

alter table user_roles comment '用户权限表';

/*==============================================================*/
/* Table: users                                                 */
/*==============================================================*/
create table users
(
   id                   int not null auto_increment,
   username             varchar(128) not null comment '用户名',
   password             varchar(128) not null comment '用户密码',
   nickname             varchar(255) comment '用户昵称',
   icon                 varchar(255),
   tel                  char(16) comment '用户电话',
   email                char(64) comment '用户邮箱',
   created_at           timestamp default CURRENT_TIMESTAMP comment '创建时间',
   updated_at           timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '最后更新时间',
   created_user_id      int,
   updated_user_id      int,
   is_del               bool default false comment '删除标志',
   primary key (id)
);

alter table users comment '用户表';