db.js

const logger = require.main.require('./common/logger.js');
const config = require.main.require('./common/config.js');
var sqlite3 = require.main.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>
 */

/* The queue table is defined as follows:
 *
 * <code>
 * CREATE TABLE IF NOT EXISTS queue
 *  ( position INTEGER PRIMARY KEY AUTOINCREMENT CHECK(position > 0)
 *  , kara_id  INTEGER REFERENCES kara
 *  , priority INTEGER NOT NULL DEFAULT 1 CHECK(priority > 0 AND priority < 6)
 *  );
 * </code>
 */

class KaraDatabase {
    /* The constructor
     * - karaPath: String
     *
     * Private members
     * - m_karaPath: String
     * - m_db: sqlite3 database */
    constructor() {
        this.m_karaPath = config.content.database.path;
        this.m_db = new sqlite3.Database(this.m_karaPath);
        logger.info('db', 'Create database from file ' + config.content.database.path);
    }

    /* Call this as a destructor */
    close() {
        this.m_db.close();
        logger.info('db', `Close database ${this.m_karaPath}`);
    }

    /* Search from the kara table, a string. Can be anything (query, name,
     * language, source, etc).
     *  - queryString: String
     *  - limit: Int | null -> maximal number of results
     *  - offset: Int | null -> offset in the search
     * => a promize, callback with [(id: Int, string, cat, type, language, author, title, source)] */
    search(queryString, offset = null, limit = null) {
        var __ret = [];
        var __sqlQuery = `SELECT id, string,
                            category AS cat,
                            (song_type || song_number) AS type,
                            language, author_name AS author,