数据结构

12138

表总览

通用表

• _user
• _user_session
• _role
• _page
• _resource
• _group
• _user_group_role
• _user_group_role_page
• _user_group_role_resource
• _constant
• _constant_ui
• _record
• _record_history
• _cache
• _file
• _view01_user

业务表

_ui

• ui 施工方案

CREATE TABLE `_ui` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pageId` varchar(255)  DEFAULT NULL COMMENT 'page id; E.g: index',
  `uiActionType` varchar(255)  DEFAULT NULL COMMENT 'ui 动作类型，如：fetchData, postData, changeUi',
  `uiActionId` varchar(255)  DEFAULT NULL COMMENT 'action id; E.g: selectXXXByXXX',
  `desc` varchar(255)  DEFAULT NULL COMMENT '描述',
  `uiActionConfig` text  COMMENT 'ui 动作数据',
  `appDataSchema` text  COMMENT 'ui 校验数据',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT '操作者userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT '操作者用户名',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC COMMENT = 'ui 施工方案'

project

• 项目表

CREATE TABLE `project` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idSequence` int(11) DEFAULT NULL COMMENT '自增ID, 1001++',
  `projectId` varchar(255) DEFAULT NULL COMMENT '项目ID',
  `projectManagerId` varchar(255) DEFAULT NULL COMMENT '项目管理员id;',
  `projectAfficheList` text COMMENT '项目公告列表;',
  `projectName` varchar(255) DEFAULT NULL COMMENT '项目名称',
  `projectIcon` varchar(255) DEFAULT NULL COMMENT '项目图标',
  `projectStartAt` varchar(255) DEFAULT NULL COMMENT '项目开始时间',
  `projectEndAt` varchar(255) DEFAULT NULL COMMENT '项目截止时间',
  `projectDesc` varchar(255) DEFAULT NULL COMMENT '项目描述',
  `projectMemberIdList` varchar(255) DEFAULT NULL COMMENT '项目成员列表',
  `projectGroup` varchar(255) DEFAULT '未分组' COMMENT '项目分组',
  `projectArchiveAt` varchar(255) DEFAULT NULL COMMENT '项目归档时间;',
  `projectDeleteAt` varchar(255) DEFAULT NULL COMMENT '项目删除时间;',
  `projectCreateAt` varchar(255) DEFAULT NULL COMMENT '项目创建时间',
  `isDelete` varchar(255) DEFAULT '否' COMMENT '是否删除;是,否',
  `isArchive` varchar(255) DEFAULT '否' COMMENT '是否归档;是,否',
  `isMark` varchar(255) DEFAULT '否' COMMENT '是否星标项目;是,否',
  `operation` varchar(255) DEFAULT 'insert' COMMENT '操作: insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT '操作者userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT '操作者用户名',
  `operationAt` varchar(255) DEFAULT NULL COMMENT '操作时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 118 DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMMENT = '项目表'

student

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `studentId` varchar(255)  DEFAULT NULL COMMENT '学生ID',
  `name` varchar(255)  DEFAULT NULL COMMENT '学生名字',
  `gender` varchar(255)  DEFAULT NULL COMMENT '性别',
  `dateOfBirth` varchar(255)  DEFAULT NULL COMMENT '出生日期',
  `classId` varchar(255)  DEFAULT NULL COMMENT '班级ID',
  `level` varchar(255)  DEFAULT NULL COMMENT '年级',
  `bodyHeight` varchar(255)  DEFAULT NULL COMMENT '身高',
  `studentStatus` varchar(255)  DEFAULT NULL COMMENT '学生状态',
  `remarks` mediumtext  COMMENT '备注',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT '操作者userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT '操作者用户名',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `studentId` (`studentId`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC

task

• 任务表

CREATE TABLE `task` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idSequence` int(11) DEFAULT NULL COMMENT '自增ID, 1001++',
  `projectId` varchar(255) DEFAULT NULL COMMENT '项目ID;',
  `taskId` varchar(255) DEFAULT NULL COMMENT '任务ID;',
  `taskTitle` varchar(255) DEFAULT NULL COMMENT '任务名称;',
  `taskDesc` varchar(255) DEFAULT NULL COMMENT '任务描述;',
  `taskLevel` varchar(255) DEFAULT NULL COMMENT '任务优先级;无,低,中,高',
  `taskTag` varchar(255) DEFAULT NULL COMMENT '任务标签;',
  `taskStatus` varchar(255) DEFAULT '未开始' COMMENT '任务状态;未开始,进行中,已完成',
  `taskType` varchar(255) DEFAULT NULL COMMENT '任务类型:;公告,任务,审批,日志',
  `taskContent` text CHARACTER SET utf8mb4  COMMENT '任务内容;富文本',
  `taskCreateAt` varchar(255) DEFAULT NULL COMMENT '任务创建时间;',
  `taskStartAt` varchar(255) DEFAULT NULL COMMENT '任务开始时间;',
  `taskEndAt` varchar(255) DEFAULT NULL COMMENT '任务结束时间;',
  `taskManagerId` varchar(255) DEFAULT NULL COMMENT '负责人id;只能一个',
  `taskMemberIdList` varchar(255) DEFAULT NULL COMMENT '参与人id;可能多个',
  `taskCommentList` text CHARACTER SET utf8mb4  COMMENT '任务评论列表',
  `taskChildList` text CHARACTER SET utf8mb4  COMMENT '子任务列表',
  `taskRelationList` text CHARACTER SET utf8mb4  COMMENT '任务关联的信息列表',
  `taskFileList` text CHARACTER SET utf8mb4  COMMENT '任务关联的附件列表',
  `operation` varchar(255) DEFAULT 'insert' COMMENT '操作: insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT '操作者userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT '操作者用户名',
  `operationAt` varchar(255) DEFAULT NULL COMMENT '操作时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 125 DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMMENT = '任务表'

业务视图

_view02_user_app

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `_view02_user_app` AS
select
  `jh_enterprise_v2_data_repository`.`enterprise_view02_user_app`.`id` AS `id`,
  `jh_enterprise_v2_data_repository`.`enterprise_view02_user_app`.`userId` AS `userId`,
  `jh_enterprise_v2_data_repository`.`enterprise_view02_user_app`.`appId` AS `appId`
from
  `jh_enterprise_v2_data_repository`.`enterprise_view02_user_app`
where
  (
    `jh_enterprise_v2_data_repository`.`enterprise_view02_user_app`.`appId` = 'jianghu-pm'
  )

view01_project

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_project` AS
select
  `project`.`id` AS `id`,
  `project`.`idSequence` AS `idSequence`,
  `project`.`projectId` AS `projectId`,
  `project`.`projectManagerId` AS `projectManagerId`,
  `project`.`projectName` AS `projectName`,
  `project`.`projectIcon` AS `projectIcon`,
  `project`.`projectStartAt` AS `projectStartAt`,
  `project`.`projectEndAt` AS `projectEndAt`,
  `project`.`projectDesc` AS `projectDesc`,
  `project`.`projectMemberIdList` AS `projectMemberIdList`,
  `project`.`projectGroup` AS `projectGroup`,
  `project`.`projectArchiveAt` AS `projectArchiveAt`,
  `project`.`projectDeleteAt` AS `projectDeleteAt`,
  `project`.`projectCreateAt` AS `projectCreateAt`,
  `project`.`isDelete` AS `isDelete`,
  `project`.`isArchive` AS `isArchive`,
  `project`.`isMark` AS `isMark`,
  `project`.`operation` AS `operation`,
  `project`.`operationByUserId` AS `operationByUserId`,
  `project`.`operationByUser` AS `operationByUser`,
  `project`.`operationAt` AS `operationAt`,
  `project`.`projectAfficheList` AS `projectAfficheList`,
  count(`t`.`taskId`) AS `totalTasks`,
  count(
    (
      case
        when (`t`.`taskStatus` = '未开始') then 1
      end
    )
  ) AS `notStartedTasks`,
  count(
    (
      case
        when (`t`.`taskStatus` = '进行中') then 1
      end
    )
  ) AS `inProgressTasks`,
  count(
    (
      case
        when (`t`.`taskStatus` = '已完成') then 1
      end
    )
  ) AS `completedTasks`
from
  (
    `project`
    left join `task` `t` on ((`project`.`projectId` = `t`.`projectId`))
  )
group by
  `project`.`id`,
  `project`.`operationAt`