newCashBook/epub_node/db/files.js

import connection from "./base.js";

/*
`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,

  */
export async function files_insert({
                                       file_id = "",
                                       source_id = "",
                                       md5 = "",
                                       mimetype = "",
                                       size = "",
                                       name = "",
                                       path = "",
                                       content = "",
                                       old_path = "",
                                       book_id = "",
                                   }) {
    return new Promise(async (resolve, reject) => {
        try {
            const sql = `
                INSERT INTO files (file_id, source_id, md5, mimetype, size, name, path, content, old_path, book_id)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY
                UPDATE file_id = file_id;
            `;
            const values = [file_id, source_id, md5, mimetype, size, name, path, content, old_path, book_id];
            // 直接接收 execute 返回的内容
            const result = await connection.execute(sql, values);
            return resolve(result);
        } catch (err) {
            return resolve(false);
        }
    });
}

export async function files_insert_link_epub({
                                                 file_id = "",
                                                 book_id = "",
                                                 author_id = "",
                                             }) {
    // console.log("files_insert_link_epub", {
    //   file_id,
    //   book_id,
    //   author_id,
    // });
    
    return new Promise(async (resolve, reject) => {
        try {
            const sql = `
                INSERT INTO book_link_file (file_id, book_id, author_id)
                VALUES (?, ?, ?);
            `;
            
            const values = [file_id, book_id, author_id];
            // 直接接收 execute 返回的内容
            connection.execute(sql, values, (err, row) => {
                if (err) {
                    console.log(606060, err);
                    return resolve(false);
                }
                return resolve(row);
            });
        } catch (err) {
            return resolve(false);
        }
    });
}

// 查询图片信息
export function getFileBymd5(md5Str) {
    return new Promise((resolve, reject) => {
        connection.query(
            `SELECT files.*, book_link_file.book_id AS book_id
             FROM files
                      JOIN book_link_file ON files.file_id = book_link_file.file_id
             WHERE files.file_id = ?`,
            [md5Str],
            (err, rows) => {
                if (err) {
                    resolve(false); // 如果出现错误，返回 false
                } else {
                    resolve(rows.length > 0 ? rows[0] : false); // 如果存在记录，返回第一条记录，否则返回 false
                }
            }
        );
    });
}

// 查询图片信息
export function searchFileByPath(imgPath, file_md5) {
    return new Promise((resolve, reject) => {
        connection.query(
            `SELECT *
             FROM files
             WHERE path like ? ${file_md5 ? "and path like ?" : ""};`,
            [`%${imgPath}%`, `%${file_md5}%`],
            (err, rows) => {
                if (err) {
                    resolve(false); // 如果存在记录，则返回 true，否则返回 false
                } else {
                    resolve(rows.length > 0 ? rows[0] : false); // 如果存在记录，则返回 true，否则返回 false
                }
            }
        );
    });
}

// 查询图片信息
export function searchFileByName(fileName, fileMd5) {
    return new Promise((resolve, reject) => {
        const query = `
            SELECT *
            FROM files
                     JOIN book_link_file ON files.file_id = book_link_file.file_id
                AND book_link_file.book_id = ?
            WHERE name LIKE ? ${fileMd5 ? "OR path LIKE ?" : ""};
        `;
        
        const queryParams = fileMd5
            ? [fileMd5, `%${fileName}%`, `%${fileMd5}%`]
            : [fileMd5, `%${fileName}%`];
        
        connection.query(query, queryParams, (err, rows) => {
            if (err) {
                return reject(err);
            }
            
            resolve(rows.length > 0 ? rows[0] : false);
        });
    });
}