Data Structures

12126

Table Overview

General Tables

Business Tables

_ui

UI Construction Plan

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 action type, e.g.: fetchData, postData, changeUi',
  `uiActionId` varchar(255)  DEFAULT NULL COMMENT 'action id; E.g: selectXXXByXXX',
  `desc` varchar(255)  DEFAULT NULL COMMENT 'description',
  `uiActionHook` text  COMMENT '[{"service": "xx", "serviceFunction": "xxx"}]',
  `uiActionConfig` text  COMMENT 'UI action data',
  `appDataSchema` text  COMMENT 'UI validation data',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 433 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'UI construction plan'

duoxing_chat_session

Chat Session

CREATE TABLE `duoxing_chat_session` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(255) DEFAULT NULL COMMENT 'user id',
  `type` varchar(255) DEFAULT NULL COMMENT 'type',
  `chatId` varchar(255) DEFAULT NULL COMMENT 'chat ID',
  `lastMessageHistoryId` int(11) DEFAULT NULL COMMENT 'most recent historical message id',
  `topChatOrder` varchar(255) NOT NULL DEFAULT '' COMMENT 'pinned order',
  `muted` int(11) DEFAULT '0' COMMENT 'do not disturb',
  `unreadCount` int(11) NOT NULL DEFAULT '0' COMMENT 'unread count',
  `operation` varchar(255) DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `_chat_session_chatId_type_userId_index` (`chatId`, `type`, `userId`) USING BTREE,
  KEY `_userId_topChatOrder_lastMessageHistoryId_index` (`userId`, `topChatOrder`, `lastMessageHistoryId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1139 DEFAULT CHARSET = utf8mb4 COMMENT = 'Chat session'

duoxing_message_history

Message History Table;

CREATE TABLE `duoxing_message_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `messageFingerprint` varchar(255) DEFAULT NULL COMMENT 'message fingerprint, multiple message ids correspond to one fingerprint',
  `fromUserId` varchar(255) DEFAULT NULL COMMENT 'sender userId',
  `toUserId` varchar(255) DEFAULT NULL COMMENT 'receiver userId',
  `toRoomId` varchar(255) DEFAULT NULL COMMENT 'receiver groupId',
  `messageType` varchar(255) DEFAULT NULL COMMENT 'message type; user: user chat, group: group chat',
  `noticeType` varchar(255) DEFAULT NULL COMMENT 'notification type; E.g: delGroupUser, addGroupUser ',
  `messageContent` text COMMENT 'message content',
  `messageContentType` varchar(255) DEFAULT NULL COMMENT 'message content type; text, atText, image, file, notice, audio, video, userCard, revoke',
  `messageTimeString` varchar(255) DEFAULT NULL COMMENT 'time of message sent; format: 2021-11-16 14:45:34',
  `messageStatus` varchar(255) DEFAULT 'active' COMMENT 'message status active revoke',
  `operation` varchar(255) DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `messageFingerprint_unique_index` (`messageFingerprint`) USING BTREE,
  KEY `messageType_index` (`messageType`) USING BTREE,
  KEY `fromUser_index` (`fromUserId`) USING BTREE,
  KEY `toUserId_index` (`toUserId`) USING BTREE,
  KEY `toGroupId_index` (`toRoomId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2123 DEFAULT CHARSET = utf8mb4 COMMENT = 'Message history table;'

duoxing_user_friend

Friend Table;

CREATE TABLE `duoxing_user_friend` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(255) DEFAULT NULL COMMENT 'user id;',
  `friendId` varchar(255) DEFAULT NULL COMMENT 'friend id',
  `friendStatus` varchar(255) DEFAULT NULL COMMENT 'friend request status; isFriend, notFriend, waitingApprove, rejectApply',
  `requestTime` varchar(255) DEFAULT NULL COMMENT 'request time; ''2022-03-05T14:23:59.077+08:00''',
  `requestRemark` varchar(255) DEFAULT NULL COMMENT 'remark',
  `responseTime` varchar(255) DEFAULT NULL COMMENT 'response time; ''2022-03-05T14:23:59.077+08:00''',
  `rejectTimes` varchar(255) DEFAULT NULL COMMENT 'number of rejections',
  `operation` varchar(255) DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `userId_index` (`userId`) USING BTREE,
  KEY `friend_index` (`friendId`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = 'Friend table;'

room

Group Table;

CREATE TABLE `room` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `roomId` varchar(255) NOT NULL COMMENT 'roomId',
  `roomName` varchar(255) DEFAULT NULL COMMENT 'group name',
  `roomType` varchar(255) DEFAULT NULL COMMENT 'group type; system, cgg, course',
  `roomDesc` varchar(255) DEFAULT NULL COMMENT 'group description',
  `roomAvatar` varchar(255) DEFAULT NULL COMMENT 'group logo',
  `roomExtend` text COMMENT 'extended fields; { groupNotice: ''xx'' }',
  `operation` varchar(255) DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `groupId_index` (`roomId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 242 DEFAULT CHARSET = utf8mb4 COMMENT = 'Group table;'

user_room_role

User Group Role Association Table;

CREATE TABLE `user_room_role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(255) NOT NULL COMMENT 'user id',
  `roomId` varchar(255) NOT NULL COMMENT 'group Id',
  `roleId` varchar(255) DEFAULT NULL COMMENT 'role Id',
  `operation` varchar(255) DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `groupId_index` (`roomId`) USING BTREE,
  KEY `userId_index` (`userId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3550 DEFAULT CHARSET = utf8mb4 COMMENT = 'User group role association table;'

visitor

CREATE TABLE `visitor` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `visitorId` varchar(255) NOT NULL,
  `visitorName` varchar(255) DEFAULT NULL COMMENT 'visitor name',
  `visitorSource` varchar(255) DEFAULT NULL COMMENT 'visitor source',
  `agentId` varchar(255) DEFAULT NULL COMMENT 'customer service id of the receptionist',
  `visitorAvatar` varchar(255) DEFAULT NULL COMMENT 'avatar',
  `visitorGender` varchar(255) DEFAULT 'male' COMMENT 'gender; male, female',
  `lastLoginTime` varchar(255) DEFAULT '0' COMMENT 'last login time',
  `lastIp` varchar(255) DEFAULT '' COMMENT 'last login ip',
  `ipZone` varchar(255) DEFAULT '' COMMENT 'last login ip location',
  `browser` varchar(255) DEFAULT '' COMMENT 'browser information',
  `fromUrl` varchar(255) DEFAULT '' COMMENT 'page url',
  `fullName` varchar(255) DEFAULT '' COMMENT 'full name',
  `address` varchar(128) DEFAULT '' COMMENT 'address',
  `phone` varchar(128) DEFAULT '' COMMENT 'phone number',
  `email` varchar(128) DEFAULT '' COMMENT 'email',
  `wechatId` varchar(255) DEFAULT NULL,
  `remark` text COMMENT 'remark',
  `operation` varchar(255) DEFAULT 'insert',
  `operationByUserId` varchar(255) DEFAULT NULL,
  `operationByUser` varchar(255) DEFAULT NULL,
  `operationAt` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `visitor_agentId_index` (`agentId`) USING BTREE,
  KEY `visitor_lastLoginTime_index` (`lastLoginTime`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 77 DEFAULT CHARSET = utf8mb4

visitor_history

CREATE TABLE `visitor_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `visitorId` varchar(255) NOT NULL,
  `visitorName` varchar(255) DEFAULT NULL COMMENT 'visitor name',
  `visitorSource` varchar(255) DEFAULT NULL COMMENT 'visitor source',
  `agentId` varchar(255) DEFAULT NULL COMMENT 'customer service id of the receptionist',
  `visitorAvatar` varchar(255) DEFAULT NULL COMMENT 'avatar',
  `visitorGender` varchar(255) DEFAULT 'male' COMMENT 'gender; male, female',
  `lastLoginTime` varchar(255) DEFAULT '0' COMMENT 'last login time',
  `lastIp` varchar(255) DEFAULT '' COMMENT 'last login ip',
  `ipZone` varchar(255) DEFAULT '' COMMENT 'last login ip location',
  `lastReferer` varchar(1000) DEFAULT '' COMMENT 'last opened page',
  `browser` varchar(255) DEFAULT '' COMMENT 'browser information',
  `fromUrl` varchar(255) DEFAULT '' COMMENT 'page url',
  `fullName` varchar(255) DEFAULT '' COMMENT 'full name',
  `address` varchar(128) DEFAULT '' COMMENT 'address',
  `phone` varchar(128) DEFAULT '' COMMENT 'phone number',
  `email` varchar(255) DEFAULT '' COMMENT 'email',
  `remark` text COMMENT 'remark',
  `operation` varchar(255) DEFAULT 'insert',
  `operationByUserId` varchar(255) DEFAULT NULL,
  `operationByUser` varchar(255) DEFAULT NULL,
  `operationAt` varchar(255) DEFAULT NULL,
  `wechatId` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `visitor_history_agentId_index` (`agentId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 487 DEFAULT CHARSET = utf8mb4

visitor_ticket

CREATE TABLE `visitor_ticket` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ticketStatus` varchar(255) NOT NULL DEFAULT 'unread' COMMENT 'message status: pending, processed',
  `visitorId` varchar(255) NOT NULL DEFAULT '0' COMMENT 'visitor id',
  `ticketFullName` varchar(255) NOT NULL DEFAULT '' COMMENT 'full name',
  `ticketIp` varchar(255) NOT NULL DEFAULT '' COMMENT 'ip',
  `ticketIpZone` varchar(255) NOT NULL DEFAULT '' COMMENT 'ip area',
  `ticketPhone` varchar(255) NOT NULL DEFAULT '' COMMENT 'phone',
  `ticketEmail` varchar(255) NOT NULL DEFAULT '' COMMENT 'email',
  `ticketWechatId` varchar(255) DEFAULT NULL COMMENT 'WeChat id',
  `ticketContent` text COMMENT 'message content',
  `ticketCreateTime` varchar(255) NOT NULL DEFAULT '0' COMMENT 'order creation time',
  `operation` varchar(255) DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8mb4

web_constant_ui

Constant Table;

CREATE TABLE `web_constant_ui` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `constantKey` varchar(255) DEFAULT NULL,
  `constantType` varchar(255) DEFAULT NULL COMMENT 'constant type; object, array',
  `pageId` varchar(255) DEFAULT 'all' COMMENT 'page id',
  `desc` varchar(255) DEFAULT NULL COMMENT 'description',
  `en` text COMMENT 'constant content; object, array',
  `zh` text COMMENT 'constant content; object, array',
  `operation` varchar(255) DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `pageId_constantKey_unique` (`constantKey`, `pageId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 36 DEFAULT CHARSET = utf8mb4 COMMENT = 'Constant table;'

Business Views

_view01_user_room_role

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `_view01_user_room_role` AS
select
  `jianghu_feige`.`user_room_role`.`id` AS `id`,
  `jianghu_feige`.`user_room_role`.`userId` AS `userId`,
  `jianghu_feige`.`user_room_role`.`roomId` AS `roomId`,
  `jianghu_feige`.`user_room_role`.`roleId` AS `roleId`,
  `_view01_user`.`username` AS `username`,
  'default' AS `userAvatar`,
  `_role`.`roleName` AS `roleName`,
  `jianghu_feige`.`room`.`roomName` AS `roomName`,
  `jianghu_feige`.`room`.`roomAvatar` AS `roomAvatar`,
  `jianghu_feige`.`user_room_role`.`operation` AS `operation`,
  `jianghu_feige`.`user_room_role`.`operationByUserId` AS `operationByUserId`,
  `jianghu_feige`.`user_room_role`.`operationByUser` AS `operationByUser`,
  `jianghu_feige`.`user_room_role`.`operationAt` AS `operationAt`,
  `jianghu_feige`.`room`.`roomExtend` AS `roomExtend`,
  'Normal' AS `userType`
from
  (
    (
      (
        `jianghu_feige`.`user_room_role`
        join `jianghu_feige`.`_view01_user` on (
          (
            `jianghu_feige`.`user_room_role`.`userId` = `_view01_user`.`userId`
          )
        )
      )
      join `jianghu_feige`.`room` on (
        (
          `jianghu_feige`.`user_room_role`.`roomId` = `jianghu_feige`.`room`.`roomId`
        )
      )
    )
    join `jianghu_feige`.`_role` on (
      (
        `jianghu_feige`.`user_room_role`.`roleId` = `_role`.`roleId`
      )
    )
  )

_view02_user

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `_view02_user` AS
select
  `_user`.`id` AS `id`,
  `_user`.`userId` AS `userId`,
  `_user`.`username` AS `username`,
  `_user`.`userAvatar` AS `userAvatar`,
  `_user`.`contactNumber` AS `contactNumber`,
  `_user`.`gender` AS `gender`,
  `_user`.`birthday` AS `birthday`,
  `_user`.`signature` AS `signature`,
  `_user`.`email` AS `email`,
  `_user`.`password` AS `password`,
  `_user`.`md5Salt` AS `md5Salt`,
  `_user`.`userType` AS `userType`,
  `_user`.`userStatus` AS `userStatus`,
  `_user`.`config` AS `config`
from
  `_user`

_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` = 'feige'
  )

view01_duoxing_chat_session

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_duoxing_chat_session` AS
select
  `duoxing_chat_session`.`id` AS `id`,
  `duoxing_chat_session`.`userId` AS `userId`,
  `duoxing_chat_session`.`type` AS `type`,
  `duoxing_chat_session`.`chatId` AS `chatId`,
  `duoxing_chat_session`.`lastMessageHistoryId` AS `lastMessageHistoryId`,
  `duoxing_chat_session`.`topChatOrder` AS `topChatOrder`,
  `duoxing_chat_session`.`muted` AS `muted`,
  `duoxing_chat_session`.`unreadCount` AS `unreadCount`,
  `room`.`roomName` AS `chatRoomName`,
  `room`.`roomAvatar` AS `chatRoomAvatar`,
  `room`.`roomExtend` AS `chatRoomExtend`,
  `view01_visitor_room_info`.`lastLoginTime` AS `lastLoginTime`,
  `view01_visitor_room_info`.`visitorId` AS `visitorId`,
  `view01_visitor_room_info`.`visitorSource` AS `visitorSource`,
  `view01_visitor_room_info`.`agentId` AS `agentId`,
  `view01_visitor_room_info`.`agentName` AS `agentName`
from
  (
    (
      `duoxing_chat_session`
      left join `room` on (
        (
          (`duoxing_chat_session`.`chatId` = `room`.`roomId`)
          and (`duoxing_chat_session`.`type` = 'room')
        )
      )
    )
    join `view01_visitor_room_info` on (
      (
        `duoxing_chat_session`.`chatId` = `view01_visitor_room_info`.`roomId`
      )
    )
  )

view01_duoxing_message_history

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_duoxing_message_history` AS
select
  `dmh`.`id` AS `id`,
  `dmh`.`messageType` AS `messageType`,
  `dmh`.`fromUserId` AS `fromUserId`,
  `fromuser`.`username` AS `fromUsername`,
  'Normal' AS `fromUserType`,
  'default' AS `fromUserAvatar`,
  `dmh`.`toUserId` AS `toUserId`,
  `touser`.`username` AS `toUsername`,
  'default' AS `toUserAvatar`,
  `dmh`.`toRoomId` AS `toRoomId`,
  `dmh`.`messageContent` AS `messageContent`,
  `dmh`.`messageFingerprint` AS `messageFingerprint`,
  `dmh`.`messageTimeString` AS `messageTimeString`,
  `dmh`.`messageContentType` AS `messageContentType`,
  `dmh`.`noticeType` AS `noticeType`,
  `dmh`.`messageStatus` AS `messageStatus`
from
  (
    (
      `jianghu_feige`.`duoxing_message_history` `dmh`
      left join `jianghu_feige`.`_view01_user` `fromuser` on ((`fromuser`.`userId` = `dmh`.`fromUserId`))
    )
    left join `jianghu_feige`.`_view01_user` `touser` on ((`touser`.`userId` = `dmh`.`toUserId`))
  )

view01_visitor_group_info

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_visitor_group_info` AS
select
  `jianghu_feige`.`visitor`.`visitorName` AS `username`,
  `jianghu_feige`.`visitor`.`visitorId` AS `visitorId`,
  `_user_group_role`.`userId` AS `userId`,
  `jianghu_feige`.`visitor`.`visitorSource` AS `visitorSource`,
  `jianghu_feige`.`visitor`.`agentId` AS `agentId`,
  `jianghu_feige`.`_user`.`username` AS `agentName`,
  `jianghu_feige`.`visitor`.`visitorAvatar` AS `userAvatar`,
  `jianghu_feige`.`visitor`.`visitorGender` AS `visitorGender`,
  `jianghu_feige`.`visitor`.`lastLoginTime` AS `lastLoginTime`,
  `jianghu_feige`.`visitor`.`lastIp` AS `lastIp`,
  `jianghu_feige`.`visitor`.`ipZone` AS `ipZone`,
  `jianghu_feige`.`visitor`.`browser` AS `browser`,
  `jianghu_feige`.`visitor`.`fromUrl` AS `fromUrl`,
  `jianghu_feige`.`visitor`.`fullName` AS `fullName`,
  `jianghu_feige`.`visitor`.`address` AS `address`,
  `jianghu_feige`.`visitor`.`phone` AS `phone`,
  `jianghu_feige`.`visitor`.`email` AS `email`,
  `_group`.`groupId` AS `groupId`,
  `_group`.`groupName` AS `groupName`
from
  (
    (
      (
        `jianghu_feige`.`_user_group_role`
        join `jianghu_feige`.`visitor` on (
          (
            `_user_group_role`.`userId` = convert(
              `jianghu_feige`.`visitor`.`visitorId` using utf8mb4
            )
          )
        )
      )
      join `jianghu_feige`.`_group` on (
        (`_user_group_role`.`groupId` = `_group`.`groupId`)
      )
    )
    left join `jianghu_feige`.`_user` on (
      (
        `jianghu_feige`.`visitor`.`agentId` = `jianghu_feige`.`_user`.`userId`
      )
    )
  )

view01_visitor_room_info

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_visitor_room_info` AS
select
  `visitor`.`visitorName` AS `username`,
  `visitor`.`visitorId` AS `visitorId`,
  `user_room_role`.`userId` AS `userId`,
  `visitor`.`visitorSource` AS `visitorSource`,
  `visitor`.`agentId` AS `agentId`,
  `_user`.`username` AS `agentName`,
  `visitor`.`visitorAvatar` AS `userAvatar`,
  `visitor`.`visitorGender` AS `visitorGender`,
  `visitor`.`lastLoginTime` AS `lastLoginTime`,
  `visitor`.`lastIp` AS `lastIp`,
  `visitor`.`ipZone` AS `ipZone`,
  `visitor`.`browser` AS `browser`,
  `visitor`.`fromUrl` AS `fromUrl`,
  `visitor`.`fullName` AS `fullName`,
  `visitor`.`address` AS `address`,
  `visitor`.`phone` AS `phone`,
  `visitor`.`email` AS `email`,
  `visitor`.`wechatId` AS `wechatId`,
  `visitor`.`remark` AS `remark`,
  `room`.`roomId` AS `roomId`,
  `room`.`roomName` AS `roomName`
from
  (
    (
      (
        `user_room_role`
        join `visitor` on (
          (
            `user_room_role`.`userId` = convert(`visitor`.`visitorId` using utf8mb4)
          )
        )
      )
      join `room` on ((`user_room_role`.`roomId` = `room`.`roomId`))
    )
    left join `_user` on ((`visitor`.`agentId` = `_user`.`userId`))
  )

view01_visitor_ticket

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_visitor_ticket` AS
select
  `visitor`.`visitorName` AS `visitorName`,
  `visitor_ticket`.`visitorId` AS `visitorId`,
  `visitor_ticket`.`ticketStatus` AS `ticketStatus`,
  `visitor_ticket`.`id` AS `id`,
  `visitor_ticket`.`ticketFullName` AS `ticketFullName`,
  `visitor_ticket`.`ticketIp` AS `ticketIp`,
  `visitor_ticket`.`ticketIpZone` AS `ticketIpZone`,
  `visitor_ticket`.`ticketPhone` AS `ticketPhone`,
  `visitor_ticket`.`ticketEmail` AS `ticketEmail`,
  `visitor_ticket`.`ticketWechatId` AS `ticketWechatId`,
  `visitor_ticket`.`ticketContent` AS `ticketContent`,
  `visitor_ticket`.`ticketCreateTime` AS `ticketCreateTime`,
  `visitor_ticket`.`operation` AS `operation`,
  `visitor_ticket`.`operationByUserId` AS `operationByUserId`,
  `visitor_ticket`.`operationByUser` AS `operationByUser`,
  `visitor_ticket`.`operationAt` AS `operationAt`,
  `visitor`.`visitorSource` AS `visitorSource`,
  `visitor`.`agentId` AS `agentId`,
  `visitor`.`visitorAvatar` AS `visitorAvatar`,
  `visitor`.`visitorGender` AS `visitorGender`,
  `visitor`.`lastLoginTime` AS `lastLoginTime`,
  `visitor`.`lastIp` AS `lastIp`,
  `visitor`.`ipZone` AS `ipZone`,
  `visitor`.`browser` AS `browser`,
  `visitor`.`fromUrl` AS `fromUrl`,
  `visitor`.`fullName` AS `fullName`,
  `visitor`.`address` AS `address`,
  `visitor`.`phone` AS `phone`,
  `visitor`.`email` AS `email`,
  `visitor`.`remark` AS `remark`
from
  (
    `visitor`
    join `visitor_ticket` on (
      (
        `visitor`.`visitorId` = `visitor_ticket`.`visitorId`
      )
    )
  )

# Table Overview

## General Tables

- [_user](/__appId__/page/article/12324#_user)
- [_user_session](/__appId__/page/article/12324#_user_session)
- [_role](/__appId__/page/article/12324#_role)
- [_page](/__appId__/page/article/12324#_page)
- [_resource](/__appId__/page/article/12324#_resource)
- [_group](/__appId__/page/article/12324#_group)
- [_user_group_role](/__appId__/page/article/12324#_user_group_role)
- [_user_group_role_page](/__appId__/page/article/12324#_user_group_role_page)
- [_user_group_role_resource](/__appId__/page/article/12324#_user_group_role_resource)
- [_constant](/__appId__/page/article/12324#_constant)
- [_constant_ui](/__appId__/page/article/12324#_constant_ui)
- [_record](/__appId__/page/article/12324#_record)
- [_record_history](/__appId__/page/article/12324#_record_history)
- [_cache](/__appId__/page/article/12324#_cache)
- [_file](/__appId__/page/article/12324#_file)
- [_view01_user](/__appId__/page/article/12324#_view01_user)

## Business Tables

### _ui

- UI Construction Plan

```sql
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 action type, e.g.: fetchData, postData, changeUi',
  `uiActionId` varchar(255)  DEFAULT NULL COMMENT 'action id; E.g: selectXXXByXXX',
  `desc` varchar(255)  DEFAULT NULL COMMENT 'description',
  `uiActionHook` text  COMMENT '[{"service": "xx", "serviceFunction": "xxx"}]',
  `uiActionConfig` text  COMMENT 'UI action data',
  `appDataSchema` text  COMMENT 'UI validation data',
  `operation` varchar(255)  DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255)  DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255)  DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255)  DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 433 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = 'UI construction plan'
```

### duoxing_chat_session

- Chat Session

```sql
CREATE TABLE `duoxing_chat_session` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(255) DEFAULT NULL COMMENT 'user id',
  `type` varchar(255) DEFAULT NULL COMMENT 'type',
  `chatId` varchar(255) DEFAULT NULL COMMENT 'chat ID',
  `lastMessageHistoryId` int(11) DEFAULT NULL COMMENT 'most recent historical message id',
  `topChatOrder` varchar(255) NOT NULL DEFAULT '' COMMENT 'pinned order',
  `muted` int(11) DEFAULT '0' COMMENT 'do not disturb',
  `unreadCount` int(11) NOT NULL DEFAULT '0' COMMENT 'unread count',
  `operation` varchar(255) DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `_chat_session_chatId_type_userId_index` (`chatId`, `type`, `userId`) USING BTREE,
  KEY `_userId_topChatOrder_lastMessageHistoryId_index` (`userId`, `topChatOrder`, `lastMessageHistoryId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1139 DEFAULT CHARSET = utf8mb4 COMMENT = 'Chat session'
```

### duoxing_message_history

- Message History Table;

```sql
CREATE TABLE `duoxing_message_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `messageFingerprint` varchar(255) DEFAULT NULL COMMENT 'message fingerprint, multiple message ids correspond to one fingerprint',
  `fromUserId` varchar(255) DEFAULT NULL COMMENT 'sender userId',
  `toUserId` varchar(255) DEFAULT NULL COMMENT 'receiver userId',
  `toRoomId` varchar(255) DEFAULT NULL COMMENT 'receiver groupId',
  `messageType` varchar(255) DEFAULT NULL COMMENT 'message type; user: user chat, group: group chat',
  `noticeType` varchar(255) DEFAULT NULL COMMENT 'notification type; E.g: delGroupUser, addGroupUser ',
  `messageContent` text COMMENT 'message content',
  `messageContentType` varchar(255) DEFAULT NULL COMMENT 'message content type; text, atText, image, file, notice, audio, video, userCard, revoke',
  `messageTimeString` varchar(255) DEFAULT NULL COMMENT 'time of message sent; format: 2021-11-16 14:45:34',
  `messageStatus` varchar(255) DEFAULT 'active' COMMENT 'message status active revoke',
  `operation` varchar(255) DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `messageFingerprint_unique_index` (`messageFingerprint`) USING BTREE,
  KEY `messageType_index` (`messageType`) USING BTREE,
  KEY `fromUser_index` (`fromUserId`) USING BTREE,
  KEY `toUserId_index` (`toUserId`) USING BTREE,
  KEY `toGroupId_index` (`toRoomId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2123 DEFAULT CHARSET = utf8mb4 COMMENT = 'Message history table;'
```

### duoxing_user_friend

- Friend Table;

```sql
CREATE TABLE `duoxing_user_friend` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(255) DEFAULT NULL COMMENT 'user id;',
  `friendId` varchar(255) DEFAULT NULL COMMENT 'friend id',
  `friendStatus` varchar(255) DEFAULT NULL COMMENT 'friend request status; isFriend, notFriend, waitingApprove, rejectApply',
  `requestTime` varchar(255) DEFAULT NULL COMMENT 'request time; ''2022-03-05T14:23:59.077+08:00''',
  `requestRemark` varchar(255) DEFAULT NULL COMMENT 'remark',
  `responseTime` varchar(255) DEFAULT NULL COMMENT 'response time; ''2022-03-05T14:23:59.077+08:00''',
  `rejectTimes` varchar(255) DEFAULT NULL COMMENT 'number of rejections',
  `operation` varchar(255) DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `userId_index` (`userId`) USING BTREE,
  KEY `friend_index` (`friendId`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = 'Friend table;'
```

### room

- Group Table;

```sql
CREATE TABLE `room` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `roomId` varchar(255) NOT NULL COMMENT 'roomId',
  `roomName` varchar(255) DEFAULT NULL COMMENT 'group name',
  `roomType` varchar(255) DEFAULT NULL COMMENT 'group type; system, cgg, course',
  `roomDesc` varchar(255) DEFAULT NULL COMMENT 'group description',
  `roomAvatar` varchar(255) DEFAULT NULL COMMENT 'group logo',
  `roomExtend` text COMMENT 'extended fields; { groupNotice: ''xx'' }',
  `operation` varchar(255) DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `groupId_index` (`roomId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 242 DEFAULT CHARSET = utf8mb4 COMMENT = 'Group table;'
```

### user_room_role

- User Group Role Association Table;

```sql
CREATE TABLE `user_room_role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(255) NOT NULL COMMENT 'user id',
  `roomId` varchar(255) NOT NULL COMMENT 'group Id',
  `roleId` varchar(255) DEFAULT NULL COMMENT 'role Id',
  `operation` varchar(255) DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `groupId_index` (`roomId`) USING BTREE,
  KEY `userId_index` (`userId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3550 DEFAULT CHARSET = utf8mb4 COMMENT = 'User group role association table;'
```

### visitor

```sql
CREATE TABLE `visitor` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `visitorId` varchar(255) NOT NULL,
  `visitorName` varchar(255) DEFAULT NULL COMMENT 'visitor name',
  `visitorSource` varchar(255) DEFAULT NULL COMMENT 'visitor source',
  `agentId` varchar(255) DEFAULT NULL COMMENT 'customer service id of the receptionist',
  `visitorAvatar` varchar(255) DEFAULT NULL COMMENT 'avatar',
  `visitorGender` varchar(255) DEFAULT 'male' COMMENT 'gender; male, female',
  `lastLoginTime` varchar(255) DEFAULT '0' COMMENT 'last login time',
  `lastIp` varchar(255) DEFAULT '' COMMENT 'last login ip',
  `ipZone` varchar(255) DEFAULT '' COMMENT 'last login ip location',
  `browser` varchar(255) DEFAULT '' COMMENT 'browser information',
  `fromUrl` varchar(255) DEFAULT '' COMMENT 'page url',
  `fullName` varchar(255) DEFAULT '' COMMENT 'full name',
  `address` varchar(128) DEFAULT '' COMMENT 'address',
  `phone` varchar(128) DEFAULT '' COMMENT 'phone number',
  `email` varchar(128) DEFAULT '' COMMENT 'email',
  `wechatId` varchar(255) DEFAULT NULL,
  `remark` text COMMENT 'remark',
  `operation` varchar(255) DEFAULT 'insert',
  `operationByUserId` varchar(255) DEFAULT NULL,
  `operationByUser` varchar(255) DEFAULT NULL,
  `operationAt` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `visitor_agentId_index` (`agentId`) USING BTREE,
  KEY `visitor_lastLoginTime_index` (`lastLoginTime`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 77 DEFAULT CHARSET = utf8mb4
```

### visitor_history

```sql
CREATE TABLE `visitor_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `visitorId` varchar(255) NOT NULL,
  `visitorName` varchar(255) DEFAULT NULL COMMENT 'visitor name',
  `visitorSource` varchar(255) DEFAULT NULL COMMENT 'visitor source',
  `agentId` varchar(255) DEFAULT NULL COMMENT 'customer service id of the receptionist',
  `visitorAvatar` varchar(255) DEFAULT NULL COMMENT 'avatar',
  `visitorGender` varchar(255) DEFAULT 'male' COMMENT 'gender; male, female',
  `lastLoginTime` varchar(255) DEFAULT '0' COMMENT 'last login time',
  `lastIp` varchar(255) DEFAULT '' COMMENT 'last login ip',
  `ipZone` varchar(255) DEFAULT '' COMMENT 'last login ip location',
  `lastReferer` varchar(1000) DEFAULT '' COMMENT 'last opened page',
  `browser` varchar(255) DEFAULT '' COMMENT 'browser information',
  `fromUrl` varchar(255) DEFAULT '' COMMENT 'page url',
  `fullName` varchar(255) DEFAULT '' COMMENT 'full name',
  `address` varchar(128) DEFAULT '' COMMENT 'address',
  `phone` varchar(128) DEFAULT '' COMMENT 'phone number',
  `email` varchar(255) DEFAULT '' COMMENT 'email',
  `remark` text COMMENT 'remark',
  `operation` varchar(255) DEFAULT 'insert',
  `operationByUserId` varchar(255) DEFAULT NULL,
  `operationByUser` varchar(255) DEFAULT NULL,
  `operationAt` varchar(255) DEFAULT NULL,
  `wechatId` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `visitor_history_agentId_index` (`agentId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 487 DEFAULT CHARSET = utf8mb4
```

### visitor_ticket

```sql
CREATE TABLE `visitor_ticket` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ticketStatus` varchar(255) NOT NULL DEFAULT 'unread' COMMENT 'message status: pending, processed',
  `visitorId` varchar(255) NOT NULL DEFAULT '0' COMMENT 'visitor id',
  `ticketFullName` varchar(255) NOT NULL DEFAULT '' COMMENT 'full name',
  `ticketIp` varchar(255) NOT NULL DEFAULT '' COMMENT 'ip',
  `ticketIpZone` varchar(255) NOT NULL DEFAULT '' COMMENT 'ip area',
  `ticketPhone` varchar(255) NOT NULL DEFAULT '' COMMENT 'phone',
  `ticketEmail` varchar(255) NOT NULL DEFAULT '' COMMENT 'email',
  `ticketWechatId` varchar(255) DEFAULT NULL COMMENT 'WeChat id',
  `ticketContent` text COMMENT 'message content',
  `ticketCreateTime` varchar(255) NOT NULL DEFAULT '0' COMMENT 'order creation time',
  `operation` varchar(255) DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8mb4
```

### web_constant_ui

- Constant Table;

```sql
CREATE TABLE `web_constant_ui` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `constantKey` varchar(255) DEFAULT NULL,
  `constantType` varchar(255) DEFAULT NULL COMMENT 'constant type; object, array',
  `pageId` varchar(255) DEFAULT 'all' COMMENT 'page id',
  `desc` varchar(255) DEFAULT NULL COMMENT 'description',
  `en` text COMMENT 'constant content; object, array',
  `zh` text COMMENT 'constant content; object, array',
  `operation` varchar(255) DEFAULT 'insert' COMMENT 'operation; insert, update, jhInsert, jhUpdate, jhDelete, jhRestore',
  `operationByUserId` varchar(255) DEFAULT NULL COMMENT 'operator userId',
  `operationByUser` varchar(255) DEFAULT NULL COMMENT 'operator username',
  `operationAt` varchar(255) DEFAULT NULL COMMENT 'operation time; E.g: 2021-05-28T10:24:54+08:00 ',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `pageId_constantKey_unique` (`constantKey`, `pageId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 36 DEFAULT CHARSET = utf8mb4 COMMENT = 'Constant table;'
```

## Business Views

### _view01_user_room_role

```sql
CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `_view01_user_room_role` AS
select
  `jianghu_feige`.`user_room_role`.`id` AS `id`,
  `jianghu_feige`.`user_room_role`.`userId` AS `userId`,
  `jianghu_feige`.`user_room_role`.`roomId` AS `roomId`,
  `jianghu_feige`.`user_room_role`.`roleId` AS `roleId`,
  `_view01_user`.`username` AS `username`,
  'default' AS `userAvatar`,
  `_role`.`roleName` AS `roleName`,
  `jianghu_feige`.`room`.`roomName` AS `roomName`,
  `jianghu_feige`.`room`.`roomAvatar` AS `roomAvatar`,
  `jianghu_feige`.`user_room_role`.`operation` AS `operation`,
  `jianghu_feige`.`user_room_role`.`operationByUserId` AS `operationByUserId`,
  `jianghu_feige`.`user_room_role`.`operationByUser` AS `operationByUser`,
  `jianghu_feige`.`user_room_role`.`operationAt` AS `operationAt`,
  `jianghu_feige`.`room`.`roomExtend` AS `roomExtend`,
  'Normal' AS `userType`
from
  (
    (
      (
        `jianghu_feige`.`user_room_role`
        join `jianghu_feige`.`_view01_user` on (
          (
            `jianghu_feige`.`user_room_role`.`userId` = `_view01_user`.`userId`
          )
        )
      )
      join `jianghu_feige`.`room` on (
        (
          `jianghu_feige`.`user_room_role`.`roomId` = `jianghu_feige`.`room`.`roomId`
        )
      )
    )
    join `jianghu_feige`.`_role` on (
      (
        `jianghu_feige`.`user_room_role`.`roleId` = `_role`.`roleId`
      )
    )
  )
```

### _view02_user

```sql
CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `_view02_user` AS
select
  `_user`.`id` AS `id`,
  `_user`.`userId` AS `userId`,
  `_user`.`username` AS `username`,
  `_user`.`userAvatar` AS `userAvatar`,
  `_user`.`contactNumber` AS `contactNumber`,
  `_user`.`gender` AS `gender`,
  `_user`.`birthday` AS `birthday`,
  `_user`.`signature` AS `signature`,
  `_user`.`email` AS `email`,
  `_user`.`password` AS `password`,
  `_user`.`md5Salt` AS `md5Salt`,
  `_user`.`userType` AS `userType`,
  `_user`.`userStatus` AS `userStatus`,
  `_user`.`config` AS `config`
from
  `_user`
```

### _view02_user_app

```sql
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` = 'feige'
  )
```

### view01_duoxing_chat_session

```sql
CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_duoxing_chat_session` AS
select
  `duoxing_chat_session`.`id` AS `id`,
  `duoxing_chat_session`.`userId` AS `userId`,
  `duoxing_chat_session`.`type` AS `type`,
  `duoxing_chat_session`.`chatId` AS `chatId`,
  `duoxing_chat_session`.`lastMessageHistoryId` AS `lastMessageHistoryId`,
  `duoxing_chat_session`.`topChatOrder` AS `topChatOrder`,
  `duoxing_chat_session`.`muted` AS `muted`,
  `duoxing_chat_session`.`unreadCount` AS `unreadCount`,
  `room`.`roomName` AS `chatRoomName`,
  `room`.`roomAvatar` AS `chatRoomAvatar`,
  `room`.`roomExtend` AS `chatRoomExtend`,
  `view01_visitor_room_info`.`lastLoginTime` AS `lastLoginTime`,
  `view01_visitor_room_info`.`visitorId` AS `visitorId`,
  `view01_visitor_room_info`.`visitorSource` AS `visitorSource`,
  `view01_visitor_room_info`.`agentId` AS `agentId`,
  `view01_visitor_room_info`.`agentName` AS `agentName`
from
  (
    (
      `duoxing_chat_session`
      left join `room` on (
        (
          (`duoxing_chat_session`.`chatId` = `room`.`roomId`)
          and (`duoxing_chat_session`.`type` = 'room')
        )
      )
    )
    join `view01_visitor_room_info` on (
      (
        `duoxing_chat_session`.`chatId` = `view01_visitor_room_info`.`roomId`
      )
    )
  )
```

### view01_duoxing_message_history

```sql
CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_duoxing_message_history` AS
select
  `dmh`.`id` AS `id`,
  `dmh`.`messageType` AS `messageType`,
  `dmh`.`fromUserId` AS `fromUserId`,
  `fromuser`.`username` AS `fromUsername`,
  'Normal' AS `fromUserType`,
  'default' AS `fromUserAvatar`,
  `dmh`.`toUserId` AS `toUserId`,
  `touser`.`username` AS `toUsername`,
  'default' AS `toUserAvatar`,
  `dmh`.`toRoomId` AS `toRoomId`,
  `dmh`.`messageContent` AS `messageContent`,
  `dmh`.`messageFingerprint` AS `messageFingerprint`,
  `dmh`.`messageTimeString` AS `messageTimeString`,
  `dmh`.`messageContentType` AS `messageContentType`,
  `dmh`.`noticeType` AS `noticeType`,
  `dmh`.`messageStatus` AS `messageStatus`
from
  (
    (
      `jianghu_feige`.`duoxing_message_history` `dmh`
      left join `jianghu_feige`.`_view01_user` `fromuser` on ((`fromuser`.`userId` = `dmh`.`fromUserId`))
    )
    left join `jianghu_feige`.`_view01_user` `touser` on ((`touser`.`userId` = `dmh`.`toUserId`))
  )
```

### view01_visitor_group_info

```sql
CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_visitor_group_info` AS
select
  `jianghu_feige`.`visitor`.`visitorName` AS `username`,
  `jianghu_feige`.`visitor`.`visitorId` AS `visitorId`,
  `_user_group_role`.`userId` AS `userId`,
  `jianghu_feige`.`visitor`.`visitorSource` AS `visitorSource`,
  `jianghu_feige`.`visitor`.`agentId` AS `agentId`,
  `jianghu_feige`.`_user`.`username` AS `agentName`,
  `jianghu_feige`.`visitor`.`visitorAvatar` AS `userAvatar`,
  `jianghu_feige`.`visitor`.`visitorGender` AS `visitorGender`,
  `jianghu_feige`.`visitor`.`lastLoginTime` AS `lastLoginTime`,
  `jianghu_feige`.`visitor`.`lastIp` AS `lastIp`,
  `jianghu_feige`.`visitor`.`ipZone` AS `ipZone`,
  `jianghu_feige`.`visitor`.`browser` AS `browser`,
  `jianghu_feige`.`visitor`.`fromUrl` AS `fromUrl`,
  `jianghu_feige`.`visitor`.`fullName` AS `fullName`,
  `jianghu_feige`.`visitor`.`address` AS `address`,
  `jianghu_feige`.`visitor`.`phone` AS `phone`,
  `jianghu_feige`.`visitor`.`email` AS `email`,
  `_group`.`groupId` AS `groupId`,
  `_group`.`groupName` AS `groupName`
from
  (
    (
      (
        `jianghu_feige`.`_user_group_role`
        join `jianghu_feige`.`visitor` on (
          (
            `_user_group_role`.`userId` = convert(
              `jianghu_feige`.`visitor`.`visitorId` using utf8mb4
            )
          )
        )
      )
      join `jianghu_feige`.`_group` on (
        (`_user_group_role`.`groupId` = `_group`.`groupId`)
      )
    )
    left join `jianghu_feige`.`_user` on (
      (
        `jianghu_feige`.`visitor`.`agentId` = `jianghu_feige`.`_user`.`userId`
      )
    )
  )
```

### view01_visitor_room_info

```sql
CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_visitor_room_info` AS
select
  `visitor`.`visitorName` AS `username`,
  `visitor`.`visitorId` AS `visitorId`,
  `user_room_role`.`userId` AS `userId`,
  `visitor`.`visitorSource` AS `visitorSource`,
  `visitor`.`agentId` AS `agentId`,
  `_user`.`username` AS `agentName`,
  `visitor`.`visitorAvatar` AS `userAvatar`,
  `visitor`.`visitorGender` AS `visitorGender`,
  `visitor`.`lastLoginTime` AS `lastLoginTime`,
  `visitor`.`lastIp` AS `lastIp`,
  `visitor`.`ipZone` AS `ipZone`,
  `visitor`.`browser` AS `browser`,
  `visitor`.`fromUrl` AS `fromUrl`,
  `visitor`.`fullName` AS `fullName`,
  `visitor`.`address` AS `address`,
  `visitor`.`phone` AS `phone`,
  `visitor`.`email` AS `email`,
  `visitor`.`wechatId` AS `wechatId`,
  `visitor`.`remark` AS `remark`,
  `room`.`roomId` AS `roomId`,
  `room`.`roomName` AS `roomName`
from
  (
    (
      (
        `user_room_role`
        join `visitor` on (
          (
            `user_room_role`.`userId` = convert(`visitor`.`visitorId` using utf8mb4)
          )
        )
      )
      join `room` on ((`user_room_role`.`roomId` = `room`.`roomId`))
    )
    left join `_user` on ((`visitor`.`agentId` = `_user`.`userId`))
  )
```

### view01_visitor_ticket

```sql
CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view01_visitor_ticket` AS
select
  `visitor`.`visitorName` AS `visitorName`,
  `visitor_ticket`.`visitorId` AS `visitorId`,
  `visitor_ticket`.`ticketStatus` AS `ticketStatus`,
  `visitor_ticket`.`id` AS `id`,
  `visitor_ticket`.`ticketFullName` AS `ticketFullName`,
  `visitor_ticket`.`ticketIp` AS `ticketIp`,
  `visitor_ticket`.`ticketIpZone` AS `ticketIpZone`,
  `visitor_ticket`.`ticketPhone` AS `ticketPhone`,
  `visitor_ticket`.`ticketEmail` AS `ticketEmail`,
  `visitor_ticket`.`ticketWechatId` AS `ticketWechatId`,
  `visitor_ticket`.`ticketContent` AS `ticketContent`,
  `visitor_ticket`.`ticketCreateTime` AS `ticketCreateTime`,
  `visitor_ticket`.`operation` AS `operation`,
  `visitor_ticket`.`operationByUserId` AS `operationByUserId`,
  `visitor_ticket`.`operationByUser` AS `operationByUser`,
  `visitor_ticket`.`operationAt` AS `operationAt`,
  `visitor`.`visitorSource` AS `visitorSource`,
  `visitor`.`agentId` AS `agentId`,
  `visitor`.`visitorAvatar` AS `visitorAvatar`,
  `visitor`.`visitorGender` AS `visitorGender`,
  `visitor`.`lastLoginTime` AS `lastLoginTime`,
  `visitor`.`lastIp` AS `lastIp`,
  `visitor`.`ipZone` AS `ipZone`,
  `visitor`.`browser` AS `browser`,
  `visitor`.`fromUrl` AS `fromUrl`,
  `visitor`.`fullName` AS `fullName`,
  `visitor`.`address` AS `address`,
  `visitor`.`phone` AS `phone`,
  `visitor`.`email` AS `email`,
  `visitor`.`remark` AS `remark`
from
  (
    `visitor`
    join `visitor_ticket` on (
      (
        `visitor`.`visitorId` = `visitor_ticket`.`visitorId`
      )
    )
  )
```