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 = "",
                                     }) {
    const sql = `
        INSERT INTO chapter (name, book_id, author_id, content, level, order_index, order_id, old_path, path)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);
    `;
    const values = [
        name, book_id, author_id, content, level, order_index, order_id, old_path, path,
    ];
    
    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.source_id 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);
        });
    });
}