框架内置表

12003

• 内置表/View
  • 用户: _user, _user_session, _view01_user
  • 页面&接口: _page, _resource
  • 用户权限:
    • _group, _user_group_role,
    • _user_group_role_page, _user_group_role_resource
  • 工具表: constant_ui, constant, _record_history, _file, _cache

用户

_user

CREATE TABLE `_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idSequence` varchar(255)  DEFAULT NULL COMMENT '自增id; 用于生成userId',
  `userId` varchar(255)  DEFAULT NULL COMMENT '主键id',
  `username` varchar(255)  DEFAULT NULL COMMENT '用户名(登陆)',
  `clearTextPassword` varchar(255)  DEFAULT NULL COMMENT '明文密码',
  `password` varchar(255)  DEFAULT NULL COMMENT '密码',
  `md5Salt` varchar(255)  DEFAULT NULL COMMENT 'md5Salt',
  `userStatus` varchar(255)  DEFAULT 'active' COMMENT '用户账号状态：活跃或关闭',
  `userType` varchar(255)  DEFAULT NULL COMMENT '用户类型; staff, student.',
  `config` 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,
  UNIQUE KEY `username_index` (`username`) USING BTREE,
  UNIQUE KEY `userId_index` (`userId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 50 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '用户表'

_user_session

CREATE TABLE `_user_session` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(255)  DEFAULT NULL COMMENT '用户id',
  `userIp` varchar(255)  DEFAULT NULL COMMENT '用户ip',
  `userIpRegion` varchar(255)  DEFAULT NULL COMMENT '用户Ip区域',
  `userAgent` text  COMMENT '请求的 agent',
  `deviceId` varchar(255)  DEFAULT NULL COMMENT '设备id',
  `deviceType` varchar(255)  DEFAULT 'web' COMMENT '设备类型; flutter, web, bot_databot, bot_chatbot, bot_xiaochengxu',
  `socketStatus` varchar(255)  DEFAULT 'offline' COMMENT 'socket状态',
  `authToken` varchar(255)  DEFAULT NULL COMMENT 'auth token',
  `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 `userId_index` (`userId`) USING BTREE,
  KEY `userId_deviceId_index` (`userId`, `deviceId`) USING BTREE,
  KEY `authToken_index` (`authToken`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 83 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '用户session表; deviceId 维度;软删除未启用;'

_view01_user

CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `_view01_user` AS select `_user`.`id` AS `id`,`_user`.`idSequence` AS `idSequence`,`_user`.`userId` AS `userId`,`_user`.`username` AS `username`,`_user`.`phoneNumber` AS `phoneNumber`,`_user`.`email` AS `email`,`_user`.`userStatus` AS `userStatus`,`_user`.`hrOrgRoleList` AS `hrOrgRoleList`,`_user`.`qiweiId` AS `qiweiId`,`_user`.`wechatId` AS `wechatId`,`_user`.`userConfig` AS `userConfig`,`_user`.`password` AS `password`,`_user`.`md5Salt` AS `md5Salt`,`_user`.`clearTextPassword` AS `clearTextPassword`,`_user`.`operation` AS `operation`,`_user`.`operationByUserId` AS `operationByUserId`,`_user`.`operationByUser` AS `operationByUser`,`_user`.`operationAt` AS `operationAt` from `_user`

页面&接口

_page

CREATE TABLE `_page` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pageId` varchar(255)  DEFAULT NULL COMMENT 'pageId',
  `pageFile` varchar(255)  DEFAULT NULL COMMENT 'page文件指定; 默认使用pageId.html',
  `pageHook` varchar(255)  DEFAULT NULL COMMENT 'pageHook',
  `pageName` varchar(255)  DEFAULT NULL COMMENT 'page name',
  `pageType` varchar(255)  DEFAULT NULL COMMENT '页面类型; showInMenu, dynamicInMenu',
  `pageIcon` text ,
  `sort` varchar(255)  DEFAULT NULL,
  `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 = 174 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '页面表; 软删除未启用;'

_resource

CREATE TABLE `_resource` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `accessControlTable` varchar(255)  DEFAULT NULL COMMENT '数据规则控制表',
  `resourceHook` text  COMMENT '[ "before": {"service": "xx", "serviceFunction": "xxx"}, "after": [] }',
  `pageId` varchar(255)  DEFAULT NULL COMMENT 'page id; E.g: index',
  `actionId` varchar(255)  DEFAULT NULL COMMENT 'action id; E.g: selectXXXByXXX',
  `desc` varchar(255)  DEFAULT NULL COMMENT '描述',
  `resourceType` varchar(255)  DEFAULT NULL COMMENT 'resource 类型; E.g: auth service sql',
  `appDataSchema` text  COMMENT 'appData 参数校验',
  `resourceData` text  COMMENT 'resource 数据; { "service": "auth", "serviceFunction": "passwordLogin" } or  { "table": "${tableName}", "action": "select", "whereCondition": ".where(function() {this.whereNot( { recordStatus: \\"active\\" })})" }',
  `requestDemo` text  COMMENT '请求Demo',
  `responseDemo` text  COMMENT '响应Demo',
  `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 = 798 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '请求资源表; 软删除未启用; resourceId=`${appId}.${pageId}.${actionId}`'

页面&接口

_group

CREATE TABLE `_group` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `groupId` varchar(511) CHARACTER SET utf8mb4 DEFAULT NULL,
  `groupLastId` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT 'groupId',
  `groupPath` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  `groupName` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '群组简称',
  `groupDeptName` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '群组全称',
  `groupAllName` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '路径全名',
  `principalId` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '组织负责人',
  `headId` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '部门主管',
  `leadId` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '分管领导; 未启用',
  `groupDesc` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '群组描述',
  `operation` varchar(255) CHARACTER SET utf8mb4 DEFAULT 'insert' COMMENT '操作; insert, update, jhInsert, jhUpdate, jhDelete jhRestore',
  `operationByUserId` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '操作者userId',
  `operationByUser` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '操作者用户名',
  `operationAt` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '操作时间; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

_user_group_role

CREATE TABLE `_user_group_role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(255) NOT NULL COMMENT '用户id',
  `groupId` varchar(255) NOT NULL COMMENT '群组Id',
  `roleId` varchar(255) DEFAULT NULL COMMENT '角色Id',
  `roleDeadline` varchar(255) DEFAULT NULL 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 `groupId_index` (`groupId`) USING BTREE,
  KEY `userId_index` (`userId`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=691 DEFAULT CHARSET=utf8mb4 COMMENT='用户群组角色关联表; 软删除未启用;';

_user_group_role_page

_user_group_role_resource

工具表

constant_ui

constant

CREATE TABLE `_constant` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `constantKey` varchar(255)  DEFAULT NULL,
  `constantType` varchar(255)  DEFAULT NULL COMMENT '常量类型; object, array',
  `desc` varchar(255)  DEFAULT NULL COMMENT '描述',
  `constantValue` text  COMMENT '常量内容; object, array',
  `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 = 12 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '常量表; 软删除未启用;'

_record_history

CREATE TABLE `_record_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `table` varchar(255)  DEFAULT NULL COMMENT '表',
  `recordId` int(11) DEFAULT NULL COMMENT '数据在table中的主键id; recordContent.id',
  `recordContent` text  NOT NULL COMMENT '数据',
  `packageContent` text  NOT NULL COMMENT '当时请求的 package JSON',
  `operation` varchar(255)  DEFAULT NULL COMMENT '操作; jhInsert, jhUpdate, jhDelete jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT '操作者userId; recordContent.operationByUserId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT '操作者用户名; recordContent.operationByUser',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT '操作时间; recordContent.operationAt; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 40812 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin

_file

_cache

CREATE TABLE `_cache` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(255)  NOT NULL COMMENT '用户Id',
  `content` longtext  COMMENT '缓存数据',
  `recordStatus` varchar(255)  DEFAULT 'active',
  `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 COLLATE = utf8mb4_bin COMMENT = '缓存表