newCashBook/epub_node/db/update.sql

create table author
(
    id int auto_increment primary key,
    name        varchar(255) not null,
    author_id   varchar(100) not null,
    create_time timestamp default CURRENT_TIMESTAMP null,
    update_time timestamp default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP,
    constraint author_id_unique unique (author_id)
);

create table book
(
    id int auto_increment primary key,
    book_name     varchar(255) not null,
    book_id       varchar(100) not null,
    book_md5      varchar(32)  not null,
    language      varchar(50) null comment '语言',
    date          date null comment '创建时间',
    creatorFileAs varchar(255) null comment '电子书创建人',
    UUID          varchar(36) null comment '电子书唯一编号',
    ISBN          varchar(20) null comment '电子书出版编号',
    author_id     varchar(100) not null comment '作者id',
    category_id   varchar(255) null comment '书籍类别编号',
    Introduction  text null comment '简介',
    create_time   timestamp default CURRENT_TIMESTAMP null,
    update_time   timestamp default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP,
    constraint book_id_unique unique (book_id)
) comment '书籍信息';

create table category
(
    id int auto_increment primary key,
    name        varchar(255) not null,
    create_time timestamp default CURRENT_TIMESTAMP null,
    update_time timestamp default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP
);

create table chapter
(
    id int auto_increment primary key,
    name        varchar(255) not null,
    book_id     varchar(100) not null,
    author_id   varchar(100) not null,
    content     longtext null,
    level       int null,
    order_index int null,
    order_id    varchar(255) null,
    old_path    varchar(255) null,
    path        varchar(255) null,
    create_time timestamp default CURRENT_TIMESTAMP null,
    update_time timestamp default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP,
    constraint fk_chapter_author foreign key (author_id) references author (author_id) on delete cascade,
    constraint fk_chapter_book foreign key (book_id) references book (book_id) on delete cascade
);

create table files
(
    id int auto_increment primary key,
    file_id     varchar(100) not null,
    md5         varchar(32)  not null,
    mimetype    varchar(255) not null,
    size        int          not null,
    name        varchar(255) null,
    path        varchar(255) null,
    create_time timestamp default CURRENT_TIMESTAMP null,
    update_time timestamp default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP,
    constraint file_id_unique unique (file_id)
);

create table book_link_file
(
    id int auto_increment primary key,
    file_id     varchar(100) not null,
    book_id     varchar(100) not null,
    author_id   varchar(100) not null,
    create_time timestamp default CURRENT_TIMESTAMP null,
    update_time timestamp default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP,
    constraint fk_book_file foreign key (file_id) references files (file_id) on delete cascade,
    constraint fk_link_author foreign key (author_id) references author (author_id) on delete cascade,
    constraint fk_link_book foreign key (book_id) references book (book_id) on delete cascade
);

create table style
(
    id int auto_increment primary key,
    name        varchar(255) not null,
    style_id    varchar(100) not null,
    create_time timestamp default CURRENT_TIMESTAMP null,
    update_time timestamp default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP,
    constraint style_id_unique unique (style_id)
);

create table style_link_book
(
    id int auto_increment primary key,
    style_id    varchar(100) not null,
    book_id     varchar(100) not null,
    create_time timestamp default CURRENT_TIMESTAMP null,
    update_time timestamp default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP,
    constraint style_book_unique unique (style_id, book_id),
    constraint fk_book foreign key (book_id) references book (book_id) on delete cascade,
    constraint fk_style foreign key (style_id) references style (style_id) on delete cascade
);



ALTER TABLE epub_manage.chapter ADD level INT NULL;
ALTER TABLE epub_manage.chapter ADD order_index INT NULL;
ALTER TABLE epub_manage.chapter ADD order_id VARCHAR(255) NULL;
ALTER TABLE epub_manage.chapter ADD old_path VARCHAR(255) NULL;
ALTER TABLE epub_manage.chapter ADD path VARCHAR(255) NULL;




alter table chapter
    modify content varchar(255) null;



CREATE INDEX idx_files_source_id ON files (source_id);





SELECT f.*, chapter.*
FROM files f
         JOIN epub_manage.book_link_file blf ON f.file_id = blf.file_id
         JOIN epub_manage.chapter chapter ON chapter.order_id = blf.file_id
WHERE blf.book_id = '1a7c3ccc61a0c00998d588971ee4e567'
  AND f.id > (
    SELECT f2.id
    FROM files f2
             JOIN epub_manage.book_link_file blf2 ON f2.file_id = blf2.file_id
    WHERE f2.file_id = '65403824ba1ebed1576f950d4fb495c0'
      AND blf2.book_id = '1a7c3ccc61a0c00998d588971ee4e567'
    LIMIT 1
    )
ORDER BY f.id ASC
    LIMIT 1;


select * from files f JOIN epub_manage.book_link_file blf ON f.file_id = blf.file_id and blf.book_id = '1a7c3ccc61a0c00998d588971ee4e567' where f.file_id = '65403824ba1ebed1576f950d4fb495c0' ORDER BY f.id ASC
    LIMIT 2;


SELECT *
FROM files f
         JOIN epub_manage.book_link_file blf ON f.file_id = blf.file_id
WHERE blf.book_id = '1a7c3ccc61a0c00998d588971ee4e567' -- 需要根据实际情况填写
  AND f.id > (
    SELECT MAX(f2.id)
    FROM files f2
             JOIN epub_manage.book_link_file blf2 ON f2.file_id = blf2.file_id
    WHERE f2.file_id = '65403824ba1ebed1576f950d4fb495c0'
)
ORDER BY f.id ASC
    LIMIT 1;


SELECT f.*
FROM files f
         JOIN epub_manage.book_link_file blf ON f.file_id = blf.file_id
WHERE blf.book_id = '1a7c3ccc61a0c00998d588971ee4e567'
  AND f.id > (
    SELECT f2.id
    FROM files f2
    WHERE f2.file_id = '65403824ba1ebed1576f950d4fb495c0'
    ORDER BY f2.id DESC
    LIMIT 1
    )
ORDER BY f.id ASC
    LIMIT 1;