数据结构

12135

表总览

通用表

• _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 DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMMENT = 'ui 施工方案'

task

• 任务表

CREATE TABLE `task` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idSequence` int(11) DEFAULT NULL COMMENT '自增ID, 1001++',
  `taskId` varchar(255) DEFAULT NULL COMMENT '任务ID',
  `taskTemplateId` varchar(255) DEFAULT NULL COMMENT '任务模板id',
  `taskBizId` varchar(255) DEFAULT NULL COMMENT '任务业务ID;绑定的业务或办公应用rowId',
  `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 '任务创建时间;',
  `taskReadStatus` varchar(255) DEFAULT '否' 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 '任务评论列表',
  `taskRelationList` text CHARACTER SET utf8mb4  COMMENT '任务关联的信息列表',
  `taskFileList` text CHARACTER SET utf8mb4  COMMENT '任务关联的附件列表',
  `taskChildList` text CHARACTER SET utf8mb4  COMMENT '子任务列表todo',
  `taskNoticeConfig` text COMMENT '抄送人配置;',
  `taskAuditUserIdList` varchar(255) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT '审核用户ID列表; 多个用英文逗号分隔',
  `taskAuditedUserIdList` varchar(255) CHARACTER SET utf8mb4  DEFAULT NULL COMMENT '已审核用户ID列表; 多个用英文逗号分隔',
  `taskAuditConfig` text CHARACTER SET utf8mb4  COMMENT '审核配置; { "ticketAuditUserIdList": ["xxx", "xxx"], "ticketAuditedUserIdList": ["xxx", "xxx"] }',
  `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 = 270 DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMMENT = '任务表'

业务视图

_view01_task

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `_view01_task` AS
select
  `jianghu_oa`.`task`.`id` AS `id`,
  `jianghu_oa`.`task`.`idSequence` AS `idSequence`,
  `jianghu_oa`.`task`.`taskId` AS `taskId`,
  `jianghu_oa`.`task`.`taskTemplateId` AS `taskTemplateId`,
  `jianghu_oa`.`task`.`taskBizId` AS `taskBizId`,
  `jianghu_oa`.`task`.`taskTitle` AS `taskTitle`,
  `jianghu_oa`.`task`.`taskDesc` AS `taskDesc`,
  `jianghu_oa`.`task`.`taskLevel` AS `taskLevel`,
  `jianghu_oa`.`task`.`taskTag` AS `taskTag`,
  `jianghu_oa`.`task`.`taskStatus` AS `taskStatus`,
  `jianghu_oa`.`task`.`taskType` AS `taskType`,
  `jianghu_oa`.`task`.`taskContent` AS `taskContent`,
  `jianghu_oa`.`task`.`taskCreateAt` AS `taskCreateAt`,
  `jianghu_oa`.`task`.`taskReadStatus` AS `taskReadStatus`,
  `jianghu_oa`.`task`.`taskStartAt` AS `taskStartAt`,
  `jianghu_oa`.`task`.`taskEndAt` AS `taskEndAt`,
  `jianghu_oa`.`task`.`taskManagerId` AS `taskManagerId`,
  `jianghu_oa`.`task`.`taskMemberIdList` AS `taskMemberIdList`,
  `jianghu_oa`.`task`.`taskCommentList` AS `taskCommentList`,
  `jianghu_oa`.`task`.`taskRelationList` AS `taskRelationList`,
  `jianghu_oa`.`task`.`taskFileList` AS `taskFileList`,
  `jianghu_oa`.`task`.`taskChildList` AS `taskChildList`,
  `jianghu_oa`.`task`.`taskNoticeConfig` AS `taskNoticeConfig`,
  `jianghu_oa`.`task`.`taskAuditUserIdList` AS `taskAuditUserIdList`,
  `jianghu_oa`.`task`.`taskAuditedUserIdList` AS `taskAuditedUserIdList`,
  `jianghu_oa`.`task`.`taskAuditConfig` AS `taskAuditConfig`,
  `jianghu_oa`.`task`.`operation` AS `operation`,
  `jianghu_oa`.`task`.`operationByUserId` AS `operationByUserId`,
  `jianghu_oa`.`task`.`operationByUser` AS `operationByUser`,
  `jianghu_oa`.`task`.`operationAt` AS `operationAt`,
  `jianghu_oa`.`task_template`.`taskTemplateName` AS `taskTemplateName`,
  `_view01_user`.`username` AS `username`
from
  (
    (
      `jianghu_oa`.`task`
      join `jianghu_oa`.`task_template` on (
        (
          `jianghu_oa`.`task`.`taskTemplateId` = `jianghu_oa`.`task_template`.`taskTemplateId`
        )
      )
    )
    join `jianghu_oa`.`_view01_user` on (
      (
        `jianghu_oa`.`task`.`taskManagerId` = `_view01_user`.`userId`
      )
    )
  )

_view01_user_group

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `_view01_user_group` AS
select
  `_view01_user`.`id` AS `id`,
  `_view01_user`.`idSequence` AS `idSequence`,
  `_view01_user`.`userId` AS `userId`,
  `_view01_user`.`username` AS `username`,
  `_view01_user`.`phoneNumber` AS `phoneNumber`,
  `_view01_user`.`email` AS `email`,
  `_view01_user`.`userStatus` AS `userStatus`,
  `_view01_user`.`hrOrgRoleList` AS `hrOrgRoleList`,
  `_view01_user`.`qiweiId` AS `qiweiId`,
  `_view01_user`.`wechatId` AS `wechatId`,
  `_view01_user`.`userConfig` AS `userConfig`,
  `_view01_user`.`password` AS `password`,
  `_view01_user`.`md5Salt` AS `md5Salt`,
  `_view01_user`.`clearTextPassword` AS `clearTextPassword`,
  `_view01_user`.`operation` AS `operation`,
  `_view01_user`.`operationByUserId` AS `operationByUserId`,
  `_view01_user`.`operationByUser` AS `operationByUser`,
  `_view01_user`.`operationAt` AS `operationAt`,
  `_group`.`groupName` AS `groupName`
from
  (
    (
      `jianghu_oa`.`_view01_user`
      join `jianghu_oa`.`_user_group_role` on (
        (
          `_view01_user`.`userId` = `_user_group_role`.`userId`
        )
      )
    )
    join `jianghu_oa`.`_group` on (
      (`_user_group_role`.`groupId` = `_group`.`groupId`)
    )
  )

_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-oa'
  )