newCashBook/epub_node/db/chapter.js

import connection from "./base.js";

/*
*  `name` VARCHAR(255) 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
    * */
export async function chapter_insert({
                                         name = "",
                                         book_id = "",
                                         author_id = "",
                                         content = "",
                                         level = "",
                                         order_index = "",
                                         order_id = "",
                                         old_path = "",
                                         path = "",
                                         parent_id= ''
                                     }) {
    // 假设最大长度为255，您可能需要根据实际数据库定义调整
    const maxLength = 255;
    
    const sql = `
        INSERT INTO chapter (name, book_id, author_id, content, level, order_index, order_id, old_path, path, parent_id)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
    `;
    const values = [
        name,
        book_id,
        author_id,
        content,
        level,
        order_index,
        order_id,
        old_path,
        path,
        parent_id
    ];
    
    return new Promise((resolve, reject) => {
        connection.execute(sql, values, (error, result) => {
            if (error) {
                console.error("Database error:", error);
                return reject(error); // 使用 reject 处理错误
            }
            resolve(result); // 返回查询结果
        });
    });
}

/*根据文件路径查询章节数据*/

// select * from files where source_id like '%part0042%'
export async function searchChapterInfoForPath(path, book_id) {
    return new Promise((resolve, reject) => {
        const query = `
            SELECT files.file_id
            FROM files
                     INNER JOIN book_link_file ON files.file_id = book_link_file.file_id
            WHERE book_link_file.book_id = ?
              AND files.path LIKE ?;`; // 确保 `source_id` 上有索引
        
        // 调整参数顺序以匹配 SQL 中的占位符顺序
        const queryParams = [book_id, `%${path}%`];
        
        connection.query(query, queryParams, (err, rows) => {
            if (err) {
                return reject(err);
            }
            
            resolve(rows.length > 0 ? rows[0] : false);
        });
    });
}

/*获取当前书籍的所有数据*/
export async function getChaptersByBookId(book_id, level = 0, parent_id = '') {
    return new Promise((resolve, reject) => {
        /*
         SELECT files.*, chapter.*
         FROM files
         INNER JOIN book_link_file ON files.file_id = book_link_file.file_id
         INNER JOIN chapter ON files.path like CONCAT('%', chapter.order_id, '%')
         WHERE book_link_file.book_id = '28b639bf9362ad3b2cd5a24cb6d811c0'
         AND files.mimetype = 'text/html' AND chapter.level = '1';
         */
        /*const query = `
            SELECT files.*, chapter.*
            FROM files
                     INNER JOIN book_link_file ON files.file_id = book_link_file.file_id
                     INNER JOIN chapter ON files.path LIKE CONCAT('%', chapter.order_id, '%')
            WHERE book_link_file.book_id = '${book_id}'
              AND files.mimetype = 'text/html' ${level === 10 ? '' : `AND chapter.level = '${level}'`};
        `;*/
        // SELECT id,name,level,order_id,parent_id,path
        const query = `
            SELECT *
            FROM chapter
              WHERE book_id = '${book_id}'
        `;
        console.log(9292, query)
        connection.query(query, [], (err, rows) => {
            if (err) {
                return reject(err);
            }
            resolve(rows);
        });
    });
}





/* 获取当前书籍下一章的file_id */
export async function getChaptersByBookId2(book_id, level = 0, parent_id = '') {
    return new Promise((resolve, reject) => {
        /*
         SELECT files.*, chapter.*
         FROM files
         INNER JOIN book_link_file ON files.file_id = book_link_file.file_id
         INNER JOIN chapter ON files.path like CONCAT('%', chapter.order_id, '%')
         WHERE book_link_file.book_id = '28b639bf9362ad3b2cd5a24cb6d811c0'
         AND files.mimetype = 'text/html' AND chapter.level = '1';
         */
        /*const query = `
            SELECT files.*, chapter.*
            FROM files
                     INNER JOIN book_link_file ON files.file_id = book_link_file.file_id
                     INNER JOIN chapter ON files.path LIKE CONCAT('%', chapter.order_id, '%')
            WHERE book_link_file.book_id = '${book_id}'
              AND files.mimetype = 'text/html' ${level === 10 ? '' : `AND chapter.level = '${level}'`};
        `;*/
        // SELECT id,name,level,order_id,parent_id,path
        const query = `
            SELECT *
            FROM chapter
              WHERE book_id = '${book_id}'
        `;
        console.log(9292, query)
        connection.query(query, [], (err, rows) => {
            if (err) {
                return reject(err);
            }
            resolve(rows);
        });
    });
}