db.js

const logger = require('./common/logger.js');
var sqlite3  = require('sqlite3').verbose();

/* The kara table is defined as follows:
 *
 * <code>
 * CREATE TABLE IF NOT EXISTS kara
 *   ( id          INTEGER PRIMARY KEY AUTOINCREMENT
 *   , song_name   TEXT    NOT NULL
 *   , source_name TEXT    NOT NULL
 *   , category    INTEGER NOT NULL REFERENCES kara_category
 *   , song_type   INTEGER NOT NULL REFERENCES kara_type
 *   , song_number INTEGER NOT NULL CHECK(song_number > 0)
 *   , language    TEXT    REFERENCES language
 *   , file_path   TEXT    NOT NULL UNIQUE
 *   , is_new      INTEGER NOT NULL
 *   , author_name TEXT
 *   , available   INTEGER CHECK(available = 0 OR available = 1) DEFAULT 1 NOT NULL
 *   , string      TEXT GENERATED ALWAYS AS
 *     ( category || ' - ' || language || ' / ' || source_name || ' - ' || song_type ||
 *       song_number || ' - ' || song_name || ' [ ' || author_name || ' ]' ||
 *       CASE WHEN available = 0 THEN ' (U)' ELSE '' END
 *     ) STORED
 *   );
 * </code>
 */

class KaraDatabase {

    /* Private members
     * - #m_karaPath: String
     * - #m_db: sqlite3 database */
    #m_karaPath;
    #m_db;

    /* The constructor
     * - karaPath: String */
    constructor(karaPath) {
        this.#m_karaPath = karaPath;
        this.#m_db       = new sqlite3.Database(this.m_karaPath);
        logger.log("info", "Create database from file " + karaPath);
    }

    /* Call this as a destructor */
    close() {
        this.#m_db.close();
        logger.log("info", "Close database " + karaPath);
    }

    /* Search from the kara table, a string. Can be anything (query, name, etc).
     * - queryString: String */
    search(queryString) {
        let __sqlQuery =
            `SELECT id, string
             FROM kara
             WHERE string LIKE ? OR author COLLATE nocase = ?`
        ;
        db.all(sql, [queryString, queryString], (err, rows) => {
            if (err) {
                throw err;
            }
            rows.forEach((row) => {
                logger.log("debug", `[${row.id}] -> ${row.string}`);
            });
        });
    }
}