企业内部开发经常遇到的一个问题是,bug追踪问题,一些公司使用excel表格追踪问题,虽然可以,但是随着业务的增加,变的难以维护。为了有效地管理bug,我们需要要一个强大的错误跟踪解决方案,比如现代化的缺陷跟踪管理软件,它们可以跟踪每个项目阶段的bug,从开发过程到软件测试和发布阶段。
表结构设计
t_user:用户表,没啥好解释的,需要注意一下code字段。权限码是在增加用户时添加的,与role表的code字段相关。因为系统比较小,涉及到的权限比较少,所以可以使用按位与或操作,判断用户是否拥有相应的权限。具体操作在代码编写时在介绍。
CREATE TABLE `t_user` (
`Id` int unsigned NOT NULL AUTO_INCREMENT,
`user_name` varchar(50) DEFAULT NULL COMMENT '用户名',
`password` varchar(32) DEFAULT NULL COMMENT '密码',
`reg_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态',
`token` varchar(32) DEFAULT NULL COMMENT '临时令牌',
`code` int unsigned DEFAULT '1' COMMENT '权限码',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
INSERT INTO `t_user` (`Id`, `user_name`, `password`, `reg_time`, `update_time`, `status`, `token`, `code`)
VALUES
(1,'root','123456','2021-09-01 11:15:02','2022-08-14 17:07:17',2,'8017d7b9253be3a2e98bb000fb221556',1)
t_role:权限,二进制位移的方法进行code初始化,比如ROOT=1,项目管理员=2,本地测试=4以此类推。
CREATE TABLE `t_role` (
`Id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增',
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '名称',
`code` int unsigned DEFAULT '1' COMMENT '权限码',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`status` tinyint(1) DEFAULT NULL COMMENT '状态',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO `t_role` (`Id`, `name`, `code`, `update_time`, `status`)
VALUES
(1,'ROOT',1,'2021-01-01 00:00:00',2),
(2,'项目管理员',2,'2021-01-01 00:00:00',2),
(3,'本地测试',4,'2021-01-01 00:00:00',2),
(4,'前端工程师',8,'2021-01-01 00:00:00',2),
(5,'后端工程师',16,'2021-01-01 00:00:00',2),
(6,'甲方评测员',32,'2021-09-09 15:53:34',2);
t_res:接口资源,管理后台提供的所有接口,控制用户的访问权限。
CREATE TABLE `t_res` (
`Id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增',
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '接口资源名称',
`url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '地址',
`code` int unsigned DEFAULT NULL COMMENT '权限码',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`status` tinyint(1) DEFAULT '1' COMMENT '状态',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8;
INSERT INTO `t_res` (`Id`, `name`, `url`, `code`, `update_time`, `status`)
VALUES
(1,'获取菜单','com.dguo.web.controller.MangeController#deskTop',63,'2021-09-09 15:14:36',2),
(2,'用户设置','com.dguo.web.controller.MangeController#userSetting',1,'2020-01-01 00:00:00',2),
(3,'角色设置','com.dguo.web.controller.MangeController#roleSetting',1,'2021-11-11 00:00:00',2),
(4,'菜单设置','com.dguo.web.controller.MangeController#menuSetting',1,'2020-02-02 00:00:00',2),
(5,'新增用户','com.dguo.web.controller.MangeController#addUser',1,'2020-02-02 00:00:00',2),
(6,'视图-新增用户','com.dguo.web.controller.MangeController#addUserView',1,'2020-01-01 00:00:00',2),
(7,'视图-编辑用户','com.dguo.web.controller.MangeController#editUserView',1,'2020-01-01 00:00:00',2),
(8,'编辑用户','com.dguo.web.controller.MangeController#editUser',1,'2020-01-01 00:00:00',2),
(9,'视图-新增角色','com.dguo.web.controller.MangeController#addRoleView',1,'2020-01-01 00:00:00',2),
(10,'新增角色','com.dguo.web.controller.MangeController#addRole',1,'2020-01-01 00:00:00',2),
(11,'视图-编辑角色','com.dguo.web.controller.MangeController#editRoleView',1,'2020-02-02 00:00:00',2),
(12,'编辑角色','com.dguo.web.controller.MangeController#editRole',1,'2020-01-01 00:00:00',2),
(13,'视图-新增菜单','com.dguo.web.controller.MangeController#addMenuView',1,'2020-01-01 00:00:00',2),
(14,'新增菜单','com.dguo.web.controller.MangeController#addMenu',1,'2020-01-01 00:00:00',2),
(15,'视图-编辑菜单','com.dguo.web.controller.MangeController#editMenuView',1,'2020-11-11 00:00:00',2),
(16,'编辑菜单','com.dguo.web.controller.MangeController#editMenu',1,'2021-01-01 00:00:00',2),
(17,'资源设置','com.dguo.web.controller.MangeController#resSetting',1,'2021-03-03 00:00:00',2),
(18,'视图-新增资源','com.dguo.web.controller.MangeController#addResView',1,'2021-03-03 00:00:00',2),
(19,'新增资源','com.dguo.web.controller.MangeController#addRes',1,'2021-03-03 00:00:00',2),
(20,'视图-编辑资源','com.dguo.web.controller.MangeController#editResView',1,'2021-03-03 00:00:00',2),
(21,'编辑资源','com.dguo.web.controller.MangeController#editRes',1,'2021-09-09 14:24:12',2),
(22,'登录的用户信息','com.dguo.web.controller.MangeController#userInfo',63,'2021-09-09 14:54:19',2),
(23,'修改用户信息','com.dguo.web.controller.MangeController#editUserInfo',63,'2021-09-09 15:06:20',2),
(24,'退出系统','com.dguo.web.controller.MangeController#logout',63,'2021-09-09 15:08:32',2),
(25,'项目设置','com.dguo.web.controller.ProjectController#projectSetting',3,'2021-09-10 13:09:23',2),
(26,'视图-新增项目','com.dguo.web.controller.ProjectController#addProjectView',3,'2021-09-10 13:23:15',2),
(27,'新增项目','com.dguo.web.controller.ProjectController#addProject',3,'2021-09-10 13:23:30',2),
(28,'视图-编辑项目','com.dguo.web.controller.ProjectController#editProjectView',3,'2021-09-10 13:40:08',2),
(29,'编辑项目','com.dguo.web.controller.ProjectController#editProject',3,'2021-09-10 13:40:24',2),
(30,'项目成员分配','com.dguo.web.controller.ProjectController#memberMap',3,'2021-09-11 00:33:27',2),
(31,'新增-编辑-项目成员','com.dguo.web.controller.ProjectController#addMember',3,'2021-09-11 01:18:33',2),
(32,'项目基本信息','com.dguo.web.controller.ProjectController#projectInfo',63,'2021-09-11 02:11:00',2),
(33,'bug列表','com.dguo.web.controller.ProjectController#bugListView',3,'2021-09-11 19:05:29',2),
(34,'视图-新增bug','com.dguo.web.controller.ProjectController#addBugView',39,'2021-09-11 19:26:12',2),
(35,'新增bug','com.dguo.web.controller.ProjectController#addBug',39,'2020-01-01 00:00:00',2),
(36,'视图-编辑bug','com.dguo.web.controller.ProjectController#editBugView',63,'2021-09-13 13:37:49',2),
(37,'编辑bug','com.dguo.web.controller.ProjectController#editBug',63,'2021-09-13 13:38:11',2),
(38,'视图-图片资源','com.dguo.web.controller.MangeController#bugFileSetting',1,'2021-09-14 14:38:26',2);
t_menu,菜单,管理视图,根据用户权限展示不同的菜单。
CREATE TABLE `t_menu` (
`Id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '菜单名',
`url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '页面地址',
`code` int unsigned DEFAULT '1' COMMENT '权限码',
`icon` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '图标',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`status` tinyint(1) DEFAULT NULL COMMENT '状态',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO `t_menu` (`Id`, `name`, `url`, `code`, `icon`, `update_time`, `status`)
VALUES
(1,'用户设置','/m/userSetting',1,NULL,'2021-01-01 00:00:00',2),
(2,'角色设置','/m/roleSetting',1,'','2021-09-02 13:42:18',2),
(3,'菜单设置','/m/menuSetting',1,NULL,'2021-09-02 13:42:18',2),
(4,'资源设置','/m/resSetting',1,'','2021-09-09 13:05:33',2),
(5,'项目设置','/p/projectSetting',3,'','2021-09-10 13:14:26',2),
(6,'图片','/m/bugFileSetting',1,'','2021-09-14 14:38:45',2);
t_project ,项目,基本信息录入。
CREATE TABLE `t_project` (
`Id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '项目名',
`front_url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '前台地址',
`back_url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '后台地址',
`reg_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`status` tinyint(1) DEFAULT '0' COMMENT '状态',
`author` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '作者',
`user_id` bigint unsigned DEFAULT NULL COMMENT '所有人id',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO `t_project` (`Id`, `name`, `front_url`, `back_url`, `reg_time`, `update_time`, `status`, `author`, `user_id`)
VALUES
(1,'测试项目11','http://localhost:8080/','http://localhost:8080/','2021-09-10 13:27:39','2021-09-11 18:44:31',2,NULL,3)
t_member :项目成员,参与项目的人员,关联表。
CREATE TABLE `t_member` (
`Id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
`project_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '项目名',
`user_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户名',
`project_id` bigint unsigned DEFAULT NULL COMMENT '项目id',
`user_id` bigint unsigned DEFAULT NULL COMMENT '用户id',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=111 DEFAULT CHARSET=utf8;
t_bug ,问题反馈表,记录项目发现的bug。
CREATE TABLE `t_bug` (
`Id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
`user_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户名',
`reg_time` datetime DEFAULT NULL COMMENT '提交时间',
`lv` tinyint unsigned DEFAULT NULL COMMENT 'bug分级',
`description` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '描述',
`image` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '图片等资源',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`status` tinyint(1) DEFAULT NULL COMMENT '状态',
`project_id` bigint unsigned DEFAULT '0' COMMENT '项目id',
`user_id` bigint unsigned DEFAULT NULL COMMENT '用户id',
`origin` tinyint unsigned DEFAULT NULL COMMENT '来源',
`feedback` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '反馈',
`round` int unsigned DEFAULT '0' COMMENT '轮次',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
t_bug_file :bug的资源表,可是图片等。
CREATE TABLE `t_bug_file` (
`Id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
`bug_id` bigint unsigned DEFAULT NULL COMMENT 'bugId',
`path` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '本地路径',
`url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '访问地址',
`project_id` bigint unsigned DEFAULT NULL COMMENT '项目id',
`project_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '项目名',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
说明:表中有很多冗余字段,便于查询展示,减少关联查询。
关于权限判断这里简单的说明一下,比如,数字 1 用二进制表示为 0001,数字 2 是 0010,数字 4 是 0100,将两个数字进行按位与操作,比如,0001 & 0010 = 0000,如果得数为0表示没有权限。在设置权限时,可以按位或操作,比如,1|2 表示为 0001 | 0010 = 0011,此时有,0011 & 0001 = 0001,得数不为0可知有相应的权限。注意:新增权限时可以使用移位操作,int类型在内存中占用了4个字节,也就是32位。对于权限少的系统可以直接使用,如果权限比较多,需要额外处理,比如分组,或者直接使用其他算法。这里大家要注意!
今天到这里,小伙伴们可以理解一下。
明天进行开发环境搭建。