群控系统服务端开发模式-应用开发-业务架构逻辑开发API建表
废话不多说,首先创建管理员表、角色表、菜单表,其次再添加数据并修改API对应的数据库配置文件、最后再生成秘钥及Base主开发。
首先要创建数据库,我习惯以项目名称命名为数据库名称及用户名称,密码是随机生成的。生成数据库的代码如下:
创建用户、密码及授权数据库
GRANT ALL PRIVILEGES ON cluster_control.* TO 'cluster_control'@'localhost' IDENTIFIED BY '7%Qh3ar@TiA5Rt#gNPT';
flush privileges;
创建数据库
CREATE DATABASE `cluster_control` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
一、创建基础表
1、管理员表
a、创建表语句
CREATE TABLE `cluster_control`.`nc_permission_admin` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',`username` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '账号名称',`password` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '密码',`avatar` varchar(250) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '头像',`realname` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '真实姓名',`email` varchar(250) CHARACTER SET utf8 COLLATE utf8_general_mysql500_ci NOT NULL COMMENT '邮箱',`ip` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT 'ip地址',`department_id` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '部门编号',`grade_id` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '级别编号',`role_id` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '角色编号',`status` tinyint(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态 1:启用 0:禁用',`create_time` datetime(0) NOT NULL COMMENT '创建时间',`update_time` datetime(0) NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',PRIMARY KEY (`id`),UNIQUE INDEX `a_u`(`username`) USING BTREE,INDEX `a_r`(`realname`) USING BTREE,UNIQUE INDEX `a_e`(`email`) USING BTREE,INDEX `a_d`(`department_id`) USING BTREE,INDEX `a_g`(`grade_id`) USING BTREE,INDEX `a_ro`(`role_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '管理员表';
b、表结构
2、角色表
a、创建表语句
CREATE TABLE `cluster_control`.`nc_permission_role` (`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '编号',`rolename` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '角色名称',`menu_id` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '菜单组',`role_key` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '角色键值',`status` tinyint(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态 1:启用 0:禁用',`create_time` datetime(0) NOT NULL COMMENT '创建时间',`update_time` datetime(0) NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',PRIMARY KEY (`id`),UNIQUE INDEX `r_r`(`rolename`) USING BTREE,INDEX `r_ro`(`role_key`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '管理员角色表';
b、表结构
3、菜单表
a、创建表语句
CREATE TABLE `cluster_control`.`nc_permission_menu` (`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '编号',`menuname` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '菜单名称',`pid` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '菜单父类编号',`title` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '路由名称',`path` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '路由地址',`component` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '组件地址',`icon` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '图标',`redirect` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '重定向地址',`always_show` tinyint(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT '是否显示 1:是 0:否',`is_hidden` tinyint(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT '是否隐藏 1:是 0:否',`is_cache` tinyint(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT '是否缓存 1:是 0:否',`is_icon` tinyint(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT '是否显示图标 1:是 0:否',`is_menu` tinyint(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT '是否是菜单 1:是 0:否',`button_type` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '按钮类型 0:无效 1:列表 2:所有 3:添加 4:编辑 5:保存 6:删除 7:详情 8:信息',`sort` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '排序',`status` tinyint(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态 1:启用 0:禁用',`create_time` datetime(0) NOT NULL COMMENT '创建时间',`update_time` datetime(0) NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',PRIMARY KEY (`id`),UNIQUE INDEX `m_m`(`menuname`) USING BTREE,INDEX `m_p`(`pid`) USING BTREE,INDEX `m_b`(`button_type`) USING BTREE,INDEX `m_i`(`is_menu`) USING BTREE,INDEX `m_s`(`sort`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '管理员菜单表';
b、表结构
4、操作记录表
a、创建表语句
CREATE TABLE `cluster_control`.`nc_permission_token` (`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '编号',`token_type` tinyint(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT '操作类型 1:登录 2:列表 3:保存 4:详情 5:启禁用 6:删除 7:退出',
`menu_name` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '菜单名称',`token` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'token =登录人编号+随机数+登录时间',`admin_id` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户编号',`random_number` varchar(20) NOT NULL COMMENT '随机数编号',`create_time` date NOT NULL COMMENT '操作日期',`login_time` datetime(0) NOT NULL COMMENT '操作时间',`expire_time` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '过期时间',PRIMARY KEY (`id`),INDEX `t_t`(`token`) USING BTREE,INDEX `t_c`(`create_time`) USING BTREE,INDEX `t_e`(`expire_time`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '管理员操作记录表';
b、表结构
二、修改数据库配置文件
将根目录下面的.env.development文件内容替换成一下代码
APP_DEBUG = true
[APP]
DEFAULT_TIMEZONE = Asia/Shanghai
[DATABASE]
TYPE = mysql
HOSTNAME = 172.20.36.143
DATABASE = cluster_control
USERNAME = cluster_control
PASSWORD = 7%Qh3ar@TiA5Rt#gNPT
HOSTPORT = 3306
CHARSET = utf8
DEBUG = true
PREFIX = nc_
[LANG]
default_lang = zh-cn
[REDIS]
HOST=172.20.36.144
SCHEME=tcp
PORT=6379
PASSWORD=QXtr@@PxjoLenGon
DATA_DB_API=124
DATA_DB_ADMIN=125
OTHER_DB=125
CACHE_DB=125
TOKEN_DB=126
DEFAULT_DB=0
三、提前说明
下一节开始生成秘钥及开发Base.php控制逻辑。