newCashBook/epub_node/db/DB.sql

-- epub_manage.book definition
CREATE TABLE `book` (
  `id` INT NOT NULL AUTO_INCREMENT,                          -- 主键使用自动递增的整数类型
  `book_name` VARCHAR(255) NOT NULL,                          -- 合理缩短长度
  `book_id` VARCHAR(100) NOT NULL,                            -- 修改长度为 100
  `book_md5` VARCHAR(32) NOT NULL,                            -- MD5 长度是固定的 32 个字符
  `language` VARCHAR(50) DEFAULT NULL COMMENT '语言',         -- 合理缩短字段长度
  `date` DATE DEFAULT NULL COMMENT '创建时间',                -- 使用 DATE 类型来存储日期
  `creatorFileAs` VARCHAR(255) DEFAULT NULL COMMENT '电子书创建人',
  `UUID` VARCHAR(36) DEFAULT NULL COMMENT '电子书唯一编号',   -- UUID 长度设置为 36
  `ISBN` VARCHAR(20) DEFAULT NULL COMMENT '电子书出版编号',   -- ISBN 长度设置为 20
  `author_id` VARCHAR(100) NOT NULL COMMENT '作者id',         -- 合理缩短字段长度
  `category_id` VARCHAR(255) DEFAULT NULL COMMENT '书籍类别编号',
  `Introduction` TEXT DEFAULT NULL COMMENT '简介',            -- TEXT 类型更合适
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,         -- 默认当前时间戳
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  -- 自动更新时间
  PRIMARY KEY (`id`),                                        -- 使用 `id` 作为主键
  UNIQUE KEY `book_id_unique` (`book_id`)                     -- 为 `book_id` 设置唯一约束
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='书籍信息';

-- epub_manage.author definition
CREATE TABLE `author` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL, -- 将 name 长度调整为 255
  `author_id` VARCHAR(100) NOT NULL,  -- 调整为更合理的长度
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `author_id_unique` (`author_id`)  -- 添加唯一约束，确保 author_id 唯一
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- 书籍的类别 category 一本书有且只有一个类型,但是类别可以有多本书
-- epub_manage.category definition
CREATE TABLE `category` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL, -- 将 name 长度调整为 255
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- 书籍的风格 style 一本书可以有多种风格, 同时 风格 也可以对应多种书籍
-- epub_manage.types definition
CREATE TABLE `style` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,  -- 将 name 长度调整为 255
  `style_id` VARCHAR(100) NOT NULL, -- 将 style_id 长度调整为 100
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `style_id_unique` (`style_id`) -- 为 style_id 添加唯一约束
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `style_link_book` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `style_id` VARCHAR(100) NOT NULL,  -- style_id 长度为 100
  `book_id` VARCHAR(100) NOT NULL,  -- book_id 长度为 100
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `style_book_unique` (`style_id`, `book_id`),  -- 添加组合唯一索引
  CONSTRAINT `fk_style` FOREIGN KEY (`style_id`) REFERENCES `style`(`style_id`) ON DELETE CASCADE,  -- 添加外键约束
  CONSTRAINT `fk_book` FOREIGN KEY (`book_id`) REFERENCES `book`(`book_id`) ON DELETE CASCADE -- 添加外键约束
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- epub_manage.chapter definition
CREATE TABLE `chapter` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` text NOT NULL,  -- Chapter name with a maximum length of 255 characters
    `book_id` VARCHAR(100) NOT NULL,  -- Book ID with a maximum length of 100 characters
    `author_id` VARCHAR(100) NOT NULL,  -- Author ID with a maximum length of 100 characters
    `content` LONGTEXT DEFAULT NULL,  -- Chapter content
    `level` INT NULL,  -- Level of the chapter, can be NULL
    `order_index` INT NULL,  -- Order index for sorting chapters, can be NULL
    `order_id` VARCHAR(255) NULL,  -- Order ID, can be NULL
    `old_path` VARCHAR(255) NULL,  -- Old path, can be NULL
    `path` VARCHAR(255) NULL,  -- Current path, can be NULL
    `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- Creation timestamp
    `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  -- Update timestamp
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_chapter_book` FOREIGN KEY (`book_id`) REFERENCES `book`(`book_id`) ON DELETE CASCADE,  -- Foreign key constraint for book
    CONSTRAINT `fk_chapter_author` FOREIGN KEY (`author_id`) REFERENCES `author`(`author_id`) ON DELETE CASCADE  -- Foreign key constraint for author
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- epub_manage.files table definition
CREATE TABLE `files` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `file_id` VARCHAR(100) NOT NULL,  -- file_id 长度为 100
  `md5` VARCHAR(32) NOT NULL,  -- MD5 长度为 32
  `mimetype` VARCHAR(255) NOT NULL,  -- mimetype 长度为 255
  `size` INT NOT NULL,
  `name` VARCHAR(255) DEFAULT NULL,
  `path` VARCHAR(255) DEFAULT NULL,
  `source_id` VARCHAR(100) DEFAULT NULL,
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `file_id_unique` (`file_id`)  -- 为 file_id 添加唯一约束
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- epub_manage.book_link_file table definition
CREATE TABLE `book_link_file` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `file_id` VARCHAR(100) NOT NULL,  -- file_id 长度为 100
  `book_id` VARCHAR(100) NOT NULL,  -- book_id 长度为 100
  `author_id` VARCHAR(100) NOT NULL,  -- author_id 长度为 100
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_book_file` FOREIGN KEY (`file_id`) REFERENCES `files`(`file_id`) ON DELETE CASCADE,  -- 外键约束
  CONSTRAINT `fk_link_book` FOREIGN KEY (`book_id`) REFERENCES `book`(`book_id`) ON DELETE CASCADE,  -- 外键约束
  CONSTRAINT `fk_link_author` FOREIGN KEY (`author_id`) REFERENCES `author`(`author_id`) ON DELETE CASCADE -- 外键约束
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;