webchat-ugc.sql 9.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131
  1. -- 消息持久化数据表
  2. CREATE TABLE webchat_ugc.`web_chat_message` (
  3. `ID` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  4. `sender` char(100) NOT NULL COMMENT '发送人',
  5. `receiver` char(100) NOT NULL COMMENT '接收人',
  6. `proxy_sender` char(100) DEFAULT NULL COMMENT '消息代理发送人(应用在群聊场景)',
  7. `message` text DEFAULT NULL COMMENT '消息内容',
  8. `image` varchar(300) DEFAULT NULL COMMENT '图片',
  9. `type` tinyint(1) DEFAULT 0 COMMENT '消息类型',
  10. `IS_READ` tinyint(1) DEFAULT 0 COMMENT '是否已读',
  11. `SEND_DATE` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '消息时间',
  12. `UPDATE_DATE` datetime DEFAULT NULL COMMENT '更新时间',
  13. `VERSION` int DEFAULT '0' COMMENT '版本',
  14. PRIMARY KEY (`ID`),
  15. KEY `INDEX_SENDER_PROXY_SENDER` (`sender`, `proxy_sender`),
  16. KEY `INDEX_RECEIVER` (`receiver`)
  17. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='消息持久化数据表';
  18. -- 红包信息表
  19. CREATE TABLE webchat_ugc.`web_chat_red_packet` (
  20. `ID` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  21. `order_id` char(64) NOT NULL COMMENT '支付平台交易订单id',
  22. `sender` char(100) NOT NULL COMMENT '红包发送人',
  23. `receiver` char(100) NOT NULL COMMENT '接受账户(人、群、企业账户)',
  24. `type` int(4) NOT NULL COMMENT '红包类型 1 固定红包、2 拼手气',
  25. `count` int(4) NOT NULL COMMENT '红包个数',
  26. `cover` varchar(100) DEFAULT NULL COMMENT '红包封面',
  27. `blessing` varchar(50) DEFAULT NULL COMMENT '祝福语',
  28. `status` int(4) NOT NULL COMMENT '状态',
  29. `total_money` DECIMAL(10, 2) default '0.00' COMMENT '金额',
  30. `CREATE_BY` char(100) DEFAULT NULL COMMENT '创建人',
  31. `CREATE_DATE` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  32. `expire_date` datetime NOT NULL COMMENT '过期时间',
  33. `UPDATE_BY` char(100) DEFAULT NULL COMMENT '更新人',
  34. `UPDATE_DATE` datetime DEFAULT NULL COMMENT '更新时间',
  35. `VERSION` int DEFAULT '0' COMMENT '版本',
  36. PRIMARY KEY (`ID`),
  37. KEY `INDEX_ORDER_ID` (`order_id`),
  38. KEY `INDEX_SENDER` (`sender`),
  39. KEY `INDEX_STATUS_EXPIRE_DATE` (`status`, `expire_date`)
  40. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='红包信息表';
  41. -- 红包拆分记录明细表
  42. CREATE TABLE webchat_ugc.`web_chat_red_packet_record` (
  43. `ID` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  44. `red_packet_id` bigint NOT NULL COMMENT '红包id',
  45. `user_id` char(100) NOT NULL COMMENT '领取人',
  46. `money` DECIMAL(10, 2) default '0.00' COMMENT '领取金额',
  47. `CREATE_DATE` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  48. PRIMARY KEY (`ID`),
  49. KEY `INDEX_RED_PACKET_ID` (`red_packet_id`),
  50. KEY `INDEX_USER_ID` (`user_id`)
  51. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='红包拆分记录明细表';
  52. -- webchat朋友圈动态核心数据表
  53. CREATE TABLE webchat_ugc.`web_chat_moment` (
  54. `ID` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  55. `author` char(100) NOT NULL COMMENT '动态作者 ',
  56. `content` varchar(300) DEFAULT NULL COMMENT '正文(纯文本)',
  57. `status` int(4) NOT NULL DEFAULT 1 COMMENT '状态',
  58. `include_images` tinyint(1) DEFAULT 0 COMMENT '是否包含图片,冗余字段',
  59. `include_video` tinyint(1) DEFAULT 0 COMMENT '是否包含图片,冗余字段',
  60. `include_link` tinyint(1) DEFAULT 0 COMMENT '是否包含连接,冗余字段',
  61. `ip` char(30) DEFAULT NULL COMMENT 'IP',
  62. `ip_address` varchar(100) DEFAULT NULL COMMENT 'IP归属地',
  63. `review_score` int(4) DEFAULT NULL COMMENT '大模型机审内容质量分',
  64. `CREATE_DATE` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  65. `UPDATE_BY` char(100) DEFAULT NULL COMMENT '更新人',
  66. `UPDATE_DATE` datetime DEFAULT NULL COMMENT '更新时间',
  67. PRIMARY KEY (`ID`),
  68. KEY `INDEX_AUTHOR_STATUS` (`author`, `status`)
  69. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='webchat朋友圈动态核心数据表';
  70. -- webchat朋友圈动态媒体资源表
  71. CREATE TABLE webchat_ugc.`web_chat_moment_media` (
  72. `ID` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  73. `moment_id` bigint unsigned NOT NULL COMMENT '动态ID',
  74. `type` int(4) NOT NULL COMMENT '资源类型 1图片 2视频',
  75. `resource` varchar(300) NOT NULL COMMENT '资源地址,对应OS存储资源URL',
  76. `size` bigint unsigned DEFAULT 0 COMMENT '资源大小',
  77. `width` int(6) DEFAULT 0 COMMENT '资源宽度',
  78. `height` int(6) DEFAULT 0 COMMENT '资源高度',
  79. PRIMARY KEY (`ID`),
  80. KEY `INDEX_MOMENT_ID_TYPE` (`moment_id`, `type`)
  81. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='webchat朋友圈动态媒体资源表';
  82. -- webchat朋友圈动态分享链接表
  83. CREATE TABLE webchat_ugc.`web_chat_moment_link` (
  84. `ID` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  85. `moment_id` bigint unsigned NOT NULL COMMENT '动态ID',
  86. `resource` varchar(300) NOT NULL COMMENT '分享到朋友圈的url网络资源链接',
  87. `title` varchar(100) DEFAULT NULL COMMENT '链接解析标题',
  88. `cover` varchar(300) DEFAULT NULL COMMENT '链接解析封面图,webchat OS地址',
  89. PRIMARY KEY (`ID`),
  90. UNIQUE KEY `INDEX_MOMENT_ID` (`moment_id`)
  91. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='webchat朋友圈动态分享链接表';
  92. -- 朋友圈动态时间线
  93. CREATE TABLE webchat_ugc.`web_chat_moment_timeline_0`(
  94. `ID` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  95. `user_id` char(100) NOT NULL COMMENT '谁的时间线',
  96. `moment_id` bigint unsigned NOT NULL COMMENT '动态id',
  97. `time_line` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '时间线时间点',
  98. PRIMARY KEY (`ID`),
  99. KEY `INDEX_USER_ID_TIME_LINE` (`user_id`, `time_line`),
  100. KEY `INDEX_MOMENT_ID` (`moment_id`)
  101. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='webchat朋友圈时间线';
  102. CREATE TABLE webchat_ugc.`web_chat_moment_timeline_1`(
  103. `ID` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  104. `user_id` char(100) NOT NULL COMMENT '谁的时间线',
  105. `moment_id` bigint unsigned NOT NULL COMMENT '动态id',
  106. `time_line` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '时间线时间点',
  107. PRIMARY KEY (`ID`),
  108. KEY `INDEX_USER_ID_TIME_LINE` (`user_id`, `time_line`),
  109. KEY `INDEX_MOMENT_ID` (`moment_id`)
  110. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='webchat朋友圈时间线';
  111. CREATE TABLE webchat_ugc.`web_chat_moment_timeline_2`(
  112. `ID` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  113. `user_id` char(100) NOT NULL COMMENT '谁的时间线',
  114. `moment_id` bigint unsigned NOT NULL COMMENT '动态id',
  115. `time_line` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '时间线时间点',
  116. PRIMARY KEY (`ID`),
  117. KEY `INDEX_USER_ID_TIME_LINE` (`user_id`, `time_line`),
  118. KEY `INDEX_MOMENT_ID` (`moment_id`)
  119. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='webchat朋友圈时间线';