gao/bookstore
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
bookstore/sql/org_fiction.sql
T
BrunoGaoandClaude c153c22aee feat(database): add AI feature tables to org_fiction.sql 
Added 5 AI core tables to sql/org_fiction.sql:
- ai_paragraph: Paragraph index metadata with vector status
- ai_artifact: AI output storage (Q&A, summaries, etc.)
- ai_audit: AI service audit logs with performance metrics
- ai_index_status: Vector index build status tracking
- ai_book_whitelist: Authorized books for AI features

Database Changes:
- Synced to 127.0.0.1 org_fiction database
- Pre-populated whitelist with books 1 and 13
- Compatible with existing schema

Version: 2.0.0
Date: 2025-01-13

🤖 Generated with [Claude Code](https://claude.com/claude-code)

Co-Authored-By: Claude <[email protected]>
2025-10-14 07:48:41 +08:00

1065 lines
60 KiB
SQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
-- MySQL dump 10.13 Distrib 8.3.0, for macos14.2 (arm64)
--
-- Host: 127.0.0.1 Database: org_fiction
-- ------------------------------------------------------
-- Server version 8.3.0
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `admire`
--
DROP TABLE IF EXISTS `admire`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `admire` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`fiction_id` bigint unsigned NOT NULL COMMENT '赞赏小说ID',
`user_id` bigint unsigned NOT NULL COMMENT '赞赏用户ID',
`create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`chapter_id` int NOT NULL COMMENT '赞赏小说章节ID',
UNIQUE KEY `pk_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='小说评论';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `author`
--
DROP TABLE IF EXISTS `author`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `author` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`user_id` bigint unsigned NOT NULL COMMENT '用户ID',
`status` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '0：正常;1-封禁',
`img_url` varchar(255) DEFAULT NULL,
`brief` varchar(255) DEFAULT NULL,
`name` varchar(255) NOT NULL,
`shelfIds` varchar(2000) DEFAULT NULL,
`readIds` varchar(2000) DEFAULT NULL,
`level` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_userId` (`user_id`),
UNIQUE KEY `pk_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='作者信息';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `author_code`
--
DROP TABLE IF EXISTS `author_code`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `author_code` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`invite_code` varchar(100) NOT NULL COMMENT '邀请码',
`validity_time` datetime NOT NULL COMMENT '有效时间',
`is_used` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否使用过;0-未使用 1-使用过',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_code` (`invite_code`),
UNIQUE KEY `pk_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='作家邀请码';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `author_income`
--
DROP TABLE IF EXISTS `author_income`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `author_income` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`author_id` bigint unsigned NOT NULL COMMENT '作家ID',
`book_id` bigint unsigned NOT NULL COMMENT '小说ID',
`income_month` date NOT NULL COMMENT '收入月份',
`pre_tax_income` int unsigned NOT NULL DEFAULT '0' COMMENT '税前收入;单位：分',
`after_tax_income` int unsigned NOT NULL DEFAULT '0' COMMENT '税后收入;单位：分',
`pay_status` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '支付状态;0-待支付 1-已支付',
`confirm_status` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '稿费确认状态;0-待确认 1-已确认',
`detail` varchar(255) DEFAULT NULL COMMENT '详情',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `pk_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='稿费收入统计';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `author_income_detail`
--
DROP TABLE IF EXISTS `author_income_detail`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `author_income_detail` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`author_id` bigint unsigned NOT NULL COMMENT '作家ID',
`book_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '小说ID;0表示全部作品',
`income_date` date NOT NULL COMMENT '收入日期',
`income_account` int unsigned NOT NULL DEFAULT '0' COMMENT '订阅总额',
`income_count` int unsigned NOT NULL DEFAULT '0' COMMENT '订阅次数',
`income_number` int unsigned NOT NULL DEFAULT '0' COMMENT '订阅人数',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `pk_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='稿费收入明细统计';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `book_category`
--
DROP TABLE IF EXISTS `book_category`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `book_category` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`work_direction` tinyint unsigned NOT NULL COMMENT '作品方向;0-男频 1-女频',
`name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '类别名',
`sort` tinyint unsigned NOT NULL DEFAULT '10' COMMENT '排序',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `pk_id` (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='小说类别';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `book_chapter`
--
DROP TABLE IF EXISTS `book_chapter`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `book_chapter` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`book_id` bigint unsigned NOT NULL COMMENT '小说ID',
`chapter_num` smallint unsigned NOT NULL COMMENT '章节号',
`chapter_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '章节名',
`word_count` int unsigned NOT NULL COMMENT '章节字数',
`is_vip` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否收费;1-收费 0-免费',
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `uk_bookId_chapterNum` (`book_id`,`chapter_num`) USING BTREE,
UNIQUE KEY `pk_id` (`id`) USING BTREE,
KEY `idx_bookId` (`book_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1445988184596992001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='小说章节';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `book_comment_copy1`
--
DROP TABLE IF EXISTS `book_comment_copy1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `book_comment_copy1` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`book_id` bigint unsigned NOT NULL COMMENT '评论小说ID',
`user_id` bigint unsigned NOT NULL COMMENT '评论用户ID',
`comment_content` varchar(512) NOT NULL COMMENT '评价内容',
`reply_count` int unsigned NOT NULL DEFAULT '0' COMMENT '回复数量',
`audit_status` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '审核状态;0-待审核 1-审核通过 2-审核不通过',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_bookId_userId` (`book_id`,`user_id`),
UNIQUE KEY `pk_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='小说评论';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `book_comment_reply`
--
DROP TABLE IF EXISTS `book_comment_reply`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `book_comment_reply` (
`id` bigint unsigned NOT NULL COMMENT '主键',
`comment_id` bigint unsigned NOT NULL COMMENT '评论ID',
`user_id` bigint unsigned NOT NULL COMMENT '回复用户ID',
`reply_content` varchar(512) NOT NULL COMMENT '回复内容',
`audit_status` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '审核状态;0-待审核 1-审核通过 2-审核不通过',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `pk_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='小说评论回复';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `book_content`
--
DROP TABLE IF EXISTS `book_content`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `book_content` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`chapter_id` bigint unsigned NOT NULL COMMENT '章节ID',
`content` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '小说章节内容',
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `uk_chapterId` (`chapter_id`) USING BTREE,
UNIQUE KEY `pk_id` (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4256332 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='小说内容';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `book_info`
--
DROP TABLE IF EXISTS `book_info`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `book_info` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`work_direction` tinyint unsigned DEFAULT NULL COMMENT '作品方向;0-男频 1-女频',
`category_id` bigint unsigned DEFAULT NULL COMMENT '类别ID',
`category_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '类别名',
`pic_url` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '小说封面地址',
`book_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '小说名',
`author_id` bigint unsigned NOT NULL COMMENT '作家id',
`author_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '作家名',
`book_desc` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '书籍描述',
`score` tinyint unsigned NOT NULL COMMENT '评分;总分:10 ，真实评分 = score/10',
`book_status` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '书籍状态;0-连载中 1-已完结',
`visit_count` bigint unsigned NOT NULL DEFAULT '103' COMMENT '点击量',
`word_count` int unsigned NOT NULL DEFAULT '0' COMMENT '总字数',
`comment_count` int unsigned NOT NULL DEFAULT '0' COMMENT '评论数',
`last_chapter_id` bigint unsigned DEFAULT NULL COMMENT '最新章节ID',
`last_chapter_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '最新章节名',
`last_chapter_update_time` datetime DEFAULT NULL COMMENT '最新章节更新时间',
`is_vip` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否收费;1-收费 0-免费',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `uk_bookName_authorName` (`book_name`,`author_name`) USING BTREE,
UNIQUE KEY `pk_id` (`id`) USING BTREE,
KEY `idx_createTime` (`create_time`) USING BTREE,
KEY `idx_lastChapterUpdateTime` (`last_chapter_update_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1431630596354977793 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='小说信息';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `chapter`
--
DROP TABLE IF EXISTS `chapter`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `chapter` (
`id` int NOT NULL AUTO_INCREMENT,
`fiction_id` int NOT NULL,
`chapter_title` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
`content_id` int NOT NULL,
`create_date` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
`sort` int NOT NULL,
`chapter_url` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
`word_number` int DEFAULT '0',
`to_be_updated` int DEFAULT '0',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=266263 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `chapter_content`
--
DROP TABLE IF EXISTS `chapter_content`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `chapter_content` (
`id` int NOT NULL AUTO_INCREMENT,
`content` mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=266480 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `collection`
--
DROP TABLE IF EXISTS `collection`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `collection` (
`id` int NOT NULL AUTO_INCREMENT,
`url` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
`open` int NOT NULL DEFAULT '1' COMMENT '1表示采集 0表示不采集',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `comment`
--
DROP TABLE IF EXISTS `comment`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `comment` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`fiction_id` bigint unsigned NOT NULL COMMENT '评论小说ID',
`user_id` bigint unsigned NOT NULL COMMENT '评论用户ID',
`content` varchar(512) NOT NULL COMMENT '评价内容',
`reply_count` int unsigned NOT NULL DEFAULT '0' COMMENT '回复数量',
`create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`chapter_id` int DEFAULT NULL,
`line_id` int DEFAULT '0',
`count` int DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `pk_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=82 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='小说评论';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `fiction`
--
DROP TABLE IF EXISTS `fiction`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `fiction` (
`id` int NOT NULL AUTO_INCREMENT,
`fiction_name` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
`create_date` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
`author` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
`type` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
`newest` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
`state` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
`number` int NOT NULL DEFAULT '0',
`brief` varchar(2000) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
`img_url` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
`fiction_url` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
`views` int DEFAULT NULL,
`update_date` datetime DEFAULT '2022-10-28 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=555 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `fiction_read`
--
DROP TABLE IF EXISTS `fiction_read`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `fiction_read` (
`id` int NOT NULL AUTO_INCREMENT,
`fiction_id` int NOT NULL,
`sort` int NOT NULL,
`user_id` int NOT NULL,
`read_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`title` varchar(255) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=197 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `fiction_shelf`
--
DROP TABLE IF EXISTS `fiction_shelf`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `fiction_shelf` (
`id` int NOT NULL AUTO_INCREMENT,
`fiction_id` int NOT NULL,
`sort` int NOT NULL,
`user_id` int NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=98 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `home_book`
--
DROP TABLE IF EXISTS `home_book`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `home_book` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`type` tinyint unsigned NOT NULL COMMENT '推荐类型;0-轮播图 1-顶部栏 2-本周强推 3-热门推荐 4-精品推荐',
`sort` tinyint unsigned NOT NULL COMMENT '推荐排序',
`book_id` bigint unsigned NOT NULL COMMENT '推荐小说ID',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `pk_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=96 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='小说推荐';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `home_friend_link`
--
DROP TABLE IF EXISTS `home_friend_link`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `home_friend_link` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`link_name` varchar(50) NOT NULL COMMENT '链接名',
`link_url` varchar(100) NOT NULL COMMENT '链接url',
`sort` tinyint unsigned NOT NULL DEFAULT '11' COMMENT '排序号',
`is_open` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '是否开启;0-不开启 1-开启',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `pk_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='友情链接';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `news_category`
--
DROP TABLE IF EXISTS `news_category`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `news_category` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL COMMENT '类别名',
`sort` tinyint unsigned NOT NULL DEFAULT '10' COMMENT '排序',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `pk_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='新闻类别';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `news_content`
--
DROP TABLE IF EXISTS `news_content`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `news_content` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`news_id` bigint unsigned NOT NULL COMMENT '新闻ID',
`content` mediumtext NOT NULL COMMENT '新闻内容',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_newsId` (`news_id`),
UNIQUE KEY `pk_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='新闻内容';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `news_info`
--
DROP TABLE IF EXISTS `news_info`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `news_info` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`category_id` bigint unsigned NOT NULL COMMENT '类别ID',
`category_name` varchar(50) NOT NULL COMMENT '类别名',
`source_name` varchar(50) NOT NULL COMMENT '新闻来源',
`title` varchar(100) NOT NULL COMMENT '新闻标题',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `pk_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='新闻信息';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `pay_alipay`
--
DROP TABLE IF EXISTS `pay_alipay`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `pay_alipay` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`out_trade_no` varchar(64) NOT NULL COMMENT '商户订单号',
`trade_no` varchar(64) NOT NULL COMMENT '支付宝交易号',
`buyer_id` varchar(16) DEFAULT NULL COMMENT '买家支付宝账号 ID',
`trade_status` varchar(32) DEFAULT NULL COMMENT '交易状态;TRADE_SUCCESS-交易成功',
`total_amount` int unsigned NOT NULL COMMENT '订单金额;单位：分',
`receipt_amount` int unsigned DEFAULT NULL COMMENT '实收金额;单位：分',
`invoice_amount` int unsigned DEFAULT NULL COMMENT '开票金额',
`gmt_create` datetime DEFAULT NULL COMMENT '交易创建时间',
`gmt_payment` datetime DEFAULT NULL COMMENT '交易付款时间',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `pk_id` (`id`),
KEY `uk_outTradeNo` (`out_trade_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='支付宝支付';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `pay_wechat`
--
DROP TABLE IF EXISTS `pay_wechat`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `pay_wechat` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`out_trade_no` varchar(32) NOT NULL COMMENT '商户订单号',
`transaction_id` varchar(32) NOT NULL COMMENT '微信支付订单号',
`trade_type` varchar(16) DEFAULT NULL COMMENT '交易类型;JSAPI-公众号支付 NATIVE-扫码支付 APP-APP支付 MICROPAY-付款码支付 MWEB-H5支付 FACEPAY-刷脸支付',
`trade_state` varchar(32) DEFAULT NULL COMMENT '交易状态;SUCCESS-支付成功 REFUND-转入退款 NOTPAY-未支付 CLOSED-已关闭 REVOKED-已撤销（付款码支付） USERPAYING-用户支付中（付款码支付） PAYERROR-支付失败(其他原因，如银行返回失败)',
`trade_state_desc` varchar(255) DEFAULT NULL COMMENT '交易状态描述',
`amount` int unsigned NOT NULL COMMENT '订单总金额;单位：分',
`payer_total` int unsigned DEFAULT NULL COMMENT '用户支付金额;单位：分',
`success_time` datetime DEFAULT NULL COMMENT '支付完成时间',
`payer_openid` varchar(128) DEFAULT NULL COMMENT '支付者用户标识;用户在直连商户appid下的唯一标识',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `pk_id` (`id`),
KEY `uk_outTradeNo` (`out_trade_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='微信支付';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `sys_log`
--
DROP TABLE IF EXISTS `sys_log`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `sys_log` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint unsigned DEFAULT NULL COMMENT '用户id',
`username` varchar(50) DEFAULT NULL COMMENT '用户名',
`operation` varchar(50) DEFAULT NULL COMMENT '用户操作',
`time` int unsigned DEFAULT NULL COMMENT '响应时间',
`method` varchar(200) DEFAULT NULL COMMENT '请求方法',
`params` varchar(5000) DEFAULT NULL COMMENT '请求参数',
`ip` varchar(64) DEFAULT NULL COMMENT 'IP地址',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`),
UNIQUE KEY `pk_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='系统日志';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `sys_menu`
--
DROP TABLE IF EXISTS `sys_menu`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `sys_menu` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`parent_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '父菜单ID;一级菜单为0',
`name` varchar(50) NOT NULL COMMENT '菜单名称',
`url` varchar(200) DEFAULT NULL COMMENT '菜单URL',
`type` tinyint unsigned NOT NULL COMMENT '类型;0-目录 1-菜单',
`icon` varchar(50) DEFAULT NULL COMMENT '菜单图标',
`sort` tinyint unsigned DEFAULT NULL COMMENT '排序',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `pk_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='系统菜单';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `sys_role`
--
DROP TABLE IF EXISTS `sys_role`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `sys_role` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`role_name` varchar(100) NOT NULL COMMENT '角色名称',
`role_sign` varchar(100) DEFAULT NULL COMMENT '角色标识',
`remark` varchar(100) DEFAULT NULL COMMENT '备注',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `pk_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='角色';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `sys_role_menu`
--
DROP TABLE IF EXISTS `sys_role_menu`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `sys_role_menu` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`role_id` bigint unsigned NOT NULL COMMENT '角色ID',
`menu_id` bigint unsigned NOT NULL COMMENT '菜单ID',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `pk_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='角色与菜单对应关系';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `sys_user`
--
DROP TABLE IF EXISTS `sys_user`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `sys_user` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL COMMENT '用户名',
`password` varchar(50) NOT NULL COMMENT '密码',
`name` varchar(100) DEFAULT NULL COMMENT '真实姓名',
`sex` tinyint unsigned DEFAULT NULL COMMENT '性别;0-男 1-女',
`birth` datetime DEFAULT NULL COMMENT '出身日期',
`email` varchar(100) DEFAULT NULL COMMENT '邮箱',
`mobile` varchar(100) DEFAULT NULL COMMENT '手机号',
`status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '状态;0-禁用 1-正常',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `pk_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='系统用户';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `sys_user_role`
--
DROP TABLE IF EXISTS `sys_user_role`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `sys_user_role` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint unsigned NOT NULL COMMENT '用户ID',
`role_id` bigint unsigned NOT NULL COMMENT '角色ID',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `pk_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户与角色对应关系';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `test`
--
DROP TABLE IF EXISTS `test`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `test` (
`id` int NOT NULL,
`test` tinyint(1) unsigned zerofill DEFAULT NULL,
`test2` tinyint(4) unsigned zerofill DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `type`
--
DROP TABLE IF EXISTS `type`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `type` (
`id` int NOT NULL AUTO_INCREMENT,
`type_name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `user`
--
DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `user` (
`id` int NOT NULL AUTO_INCREMENT,
`username` varchar(25) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
`password` varchar(25) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
`shelf_ids` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
`read_ids` varchar(2000) DEFAULT NULL,
`img_url` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `user_bookshelf`
--
DROP TABLE IF EXISTS `user_bookshelf`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `user_bookshelf` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`user_id` bigint unsigned NOT NULL COMMENT '用户ID',
`book_id` bigint unsigned NOT NULL COMMENT '小说ID',
`pre_content_id` bigint unsigned DEFAULT NULL COMMENT '上一次阅读的章节内容表ID',
`create_time` datetime DEFAULT NULL COMMENT '创建时间;',
`update_time` datetime DEFAULT NULL COMMENT '更新时间;',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_userId_bookId` (`user_id`,`book_id`),
UNIQUE KEY `pk_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户书架';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `user_comment`
--
DROP TABLE IF EXISTS `user_comment`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `user_comment` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`user_id` bigint unsigned NOT NULL COMMENT '评论用户ID',
`book_id` bigint unsigned NOT NULL COMMENT '评论小说ID',
`comment_content` varchar(512) NOT NULL COMMENT '评价内容',
`reply_count` int unsigned NOT NULL DEFAULT '0' COMMENT '回复数量',
`audit_status` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '审核状态;0-待审核 1-审核通过 2-审核不通过',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_bookId_userId` (`book_id`,`user_id`),
UNIQUE KEY `pk_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户评论';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `user_comment_reply`
--
DROP TABLE IF EXISTS `user_comment_reply`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `user_comment_reply` (
`id` bigint unsigned NOT NULL COMMENT '主键',
`comment_id` bigint unsigned NOT NULL COMMENT '评论ID',
`user_id` bigint unsigned NOT NULL COMMENT '回复用户ID',
`reply_content` varchar(512) NOT NULL COMMENT '回复内容',
`audit_status` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '审核状态;0-待审核 1-审核通过 2-审核不通过',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `pk_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户评论回复';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `user_consume_log`
--
DROP TABLE IF EXISTS `user_consume_log`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `user_consume_log` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`user_id` bigint unsigned NOT NULL COMMENT '消费用户ID',
`amount` int unsigned NOT NULL COMMENT '消费使用的金额;单位：屋币',
`product_type` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '消费商品类型;0-小说VIP章节',
`product_id` bigint unsigned DEFAULT NULL COMMENT '消费的的商品ID;例如：章节ID',
`produc_name` varchar(50) DEFAULT NULL COMMENT '消费的的商品名;例如：章节名',
`produc_value` int unsigned DEFAULT NULL COMMENT '消费的的商品值;例如：1',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `pk_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户消费记录';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `user_feedback`
--
DROP TABLE IF EXISTS `user_feedback`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `user_feedback` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint unsigned NOT NULL COMMENT '反馈用户id',
`content` varchar(512) NOT NULL COMMENT '反馈内容',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `pk_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户反馈';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `user_info`
--
DROP TABLE IF EXISTS `user_info`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `user_info` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL COMMENT '登录名',
`password` varchar(100) NOT NULL COMMENT '登录密码-加密',
`salt` varchar(8) NOT NULL COMMENT '加密盐值',
`nick_name` varchar(50) DEFAULT NULL COMMENT '昵称',
`user_photo` varchar(100) DEFAULT NULL COMMENT '用户头像',
`user_sex` tinyint unsigned DEFAULT NULL COMMENT '用户性别;0-男 1-女',
`account_balance` bigint unsigned NOT NULL DEFAULT '0' COMMENT '账户余额',
`status` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '用户状态;0-正常',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_username` (`username`),
UNIQUE KEY `pk_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户信息';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `user_pay_log`
--
DROP TABLE IF EXISTS `user_pay_log`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `user_pay_log` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`user_id` bigint unsigned NOT NULL COMMENT '充值用户ID',
`pay_channel` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '充值方式;0-支付宝 1-微信',
`out_trade_no` varchar(64) NOT NULL COMMENT '商户订单号',
`amount` int unsigned NOT NULL COMMENT '充值金额;单位：分',
`product_type` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '充值商品类型;0-屋币 1-包年VIP',
`product_id` bigint unsigned DEFAULT NULL COMMENT '充值商品ID',
`product_name` varchar(255) NOT NULL COMMENT '充值商品名;示例值：屋币',
`product_value` int unsigned DEFAULT NULL COMMENT '充值商品值;示例值：255',
`pay_time` datetime NOT NULL COMMENT '充值时间',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `pk_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户充值记录';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `user_read_history`
--
DROP TABLE IF EXISTS `user_read_history`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `user_read_history` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`user_id` bigint unsigned NOT NULL COMMENT '用户ID',
`book_id` bigint unsigned NOT NULL COMMENT '小说ID',
`pre_content_id` bigint unsigned NOT NULL COMMENT '上一次阅读的章节内容表ID',
`create_time` datetime DEFAULT NULL COMMENT '创建时间;',
`update_time` datetime DEFAULT NULL COMMENT '更新时间;',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_userId_bookId` (`user_id`,`book_id`),
UNIQUE KEY `pk_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户阅读历史';
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2024-02-28 20:59:30
-- ============================================
-- AI智能看书系统 - 数据库表结构
-- Version: 2.0.0
-- Date: 2025-01-13
-- Description: AI功能核心表（5张表）
-- ============================================
--
-- Table structure for table `ai_paragraph`
--
DROP TABLE IF EXISTS `ai_paragraph`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `ai_paragraph` (
`id` bigint NOT NULL AUTO_INCREMENT,
`book_id` bigint NOT NULL COMMENT '书籍ID',
`chapter_id` bigint NOT NULL COMMENT '章节ID',
`para_id` varchar(64) NOT NULL COMMENT '段落唯一ID: book_{id}_ch_{id}_para_{idx}',
`para_index` int NOT NULL COMMENT '段落索引位置',
`text` text NOT NULL COMMENT '段落原文',
`text_length` int DEFAULT NULL COMMENT '文本长度',
`text_hash` char(64) NOT NULL COMMENT 'SHA256哈希，用于去重',
`tokens` int DEFAULT NULL COMMENT 'Token数量',
`has_vector` tinyint(1) DEFAULT '0' COMMENT '是否已生成向量',
`vector_id` varchar(128) DEFAULT NULL COMMENT '向量库中的ID',
`clean_text` text COMMENT '清洗后的文本',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_para` (`book_id`,`chapter_id`,`para_id`),
KEY `idx_chapter` (`book_id`,`chapter_id`),
KEY `idx_hash` (`text_hash`),
KEY `idx_book` (`book_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='段落索引元数据表';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `ai_artifact`
--
DROP TABLE IF EXISTS `ai_artifact`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `ai_artifact` (
`id` bigint NOT NULL AUTO_INCREMENT,
`user_id` bigint DEFAULT NULL COMMENT '用户ID，NULL表示系统生成',
`type` varchar(32) NOT NULL COMMENT '产物类型: qna/chapter_ie/book_summary/recommendation',
`book_id` bigint DEFAULT NULL COMMENT '关联书籍ID',
`chapter_id` bigint DEFAULT NULL COMMENT '关联章节ID（可选）',
`payload` json NOT NULL COMMENT 'AI输出的JSON内容',
`refs` json DEFAULT NULL COMMENT '引用证据链',
`model_version` varchar(64) DEFAULT NULL COMMENT '使用的模型版本',
`prompt_id` varchar(64) DEFAULT NULL COMMENT '提示词模板ID',
`confidence` decimal(5,4) DEFAULT NULL COMMENT '置信度 0-1',
`source` varchar(128) DEFAULT NULL COMMENT '内容来源',
`license` varchar(64) DEFAULT NULL COMMENT '授权类型：authorized/public_domain/owned',
`status` varchar(16) DEFAULT 'active' COMMENT '状态: active/archived/rejected',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_type_book` (`type`,`book_id`,`created_at`),
KEY `idx_user` (`user_id`,`type`),
KEY `idx_license` (`license`),
KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='AI产物通用存储表';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `ai_audit`
--
DROP TABLE IF EXISTS `ai_audit`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `ai_audit` (
`id` bigint NOT NULL AUTO_INCREMENT,
`request_id` char(36) NOT NULL COMMENT '请求唯一ID（UUID）',
`user_id` bigint DEFAULT NULL COMMENT '用户ID',
`agent` varchar(64) NOT NULL COMMENT 'AI代理类型: qna/ie/recommend',
`prompt_hash` char(64) DEFAULT NULL COMMENT '提示词哈希（用于缓存）',
`model` varchar(64) DEFAULT NULL COMMENT '使用的模型',
`latency_ms` int DEFAULT NULL COMMENT '响应延迟（毫秒）',
`status` varchar(16) NOT NULL COMMENT '状态: success/timeout/error/degraded',
`error_message` text COMMENT '错误信息',
`input_tokens` int DEFAULT NULL COMMENT '输入token数',
`output_tokens` int DEFAULT NULL COMMENT '输出token数',
`cost` decimal(10,4) DEFAULT NULL COMMENT '调用成本（元）',
`content_source` varchar(128) DEFAULT NULL COMMENT '内容来源',
`authorized` tinyint(1) DEFAULT '1' COMMENT '是否已授权',
`approved_by` bigint DEFAULT NULL COMMENT '人工审核人ID',
`approved_at` datetime DEFAULT NULL COMMENT '审核时间',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_request` (`request_id`),
KEY `idx_agent_time` (`agent`,`created_at`),
KEY `idx_user_time` (`user_id`,`created_at`),
KEY `idx_status` (`status`),
KEY `idx_authorized` (`authorized`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='AI服务审计日志表';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `ai_index_status`
--
DROP TABLE IF EXISTS `ai_index_status`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `ai_index_status` (
`id` bigint NOT NULL AUTO_INCREMENT,
`book_id` bigint NOT NULL COMMENT '书籍ID',
`status` varchar(16) NOT NULL COMMENT '状态: pending/building/completed/failed',
`total_chapters` int DEFAULT '0' COMMENT '总章节数',
`processed_chapters` int DEFAULT '0' COMMENT '已处理章节数',
`total_paragraphs` int DEFAULT '0' COMMENT '总段落数',
`avg_para_length` int DEFAULT NULL COMMENT '平均段落长度',
`rejected_paragraphs` int DEFAULT '0' COMMENT '拒收段落数（太短/太长）',
`embedding_model` varchar(64) DEFAULT NULL COMMENT 'Embedding模型名称',
`bm25_indexed` tinyint(1) DEFAULT '0' COMMENT 'BM25索引是否完成',
`vector_indexed` tinyint(1) DEFAULT '0' COMMENT '向量索引是否完成',
`error_message` text COMMENT '错误信息',
`started_at` datetime DEFAULT NULL COMMENT '开始时间',
`completed_at` datetime DEFAULT NULL COMMENT '完成时间',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_book` (`book_id`),
KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='向量索引构建状态表';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `ai_book_whitelist`
--
DROP TABLE IF EXISTS `ai_book_whitelist`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `ai_book_whitelist` (
`id` bigint NOT NULL AUTO_INCREMENT,
`book_id` bigint NOT NULL COMMENT '书籍ID',
`license_type` varchar(64) NOT NULL COMMENT '授权类型: authorized/public_domain/owned',
`license_proof` text COMMENT '授权证明（协议扫描件路径或说明）',
`approved_by` bigint NOT NULL COMMENT '审批人ID',
`approved_at` datetime NOT NULL COMMENT '审批时间',
`expires_at` datetime DEFAULT NULL COMMENT '授权到期时间（NULL表示永久）',
`notes` text COMMENT '备注说明',
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_book` (`book_id`),
KEY `idx_license` (`license_type`),
KEY `idx_expires` (`expires_at`),
KEY `idx_approved` (`approved_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='授权书籍白名单表';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `ai_book_whitelist`
--
LOCK TABLES `ai_book_whitelist` WRITE;
/*!40000 ALTER TABLE `ai_book_whitelist` DISABLE KEYS */;
INSERT INTO `ai_book_whitelist` VALUES
(1,13,'public_domain','网络公开内容，仅用于测试',1,NOW(),NULL,'MVP测试书籍 - 我真没想重生啊'),
(2,1,'public_domain','网络公开内容，仅用于测试',1,NOW(),NULL,'MVP测试书籍');
/*!40000 ALTER TABLE `ai_book_whitelist` ENABLE KEYS */;
UNLOCK TABLES;
-- AI tables update completed on 2025-01-13
Reference in New Issue View Git Blame Copy Permalink