db.js

const logger = require.main.require('./common/logger.js');
const config = require.main.require('./common/config.js');
const MiniSearch = require("minisearch")
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);
        this.m_db.configure('busyTimeout', 50000);
        this.m_search_engine = new MiniSearch({
            fields: [
                "id",
                "cat",
                "type",
                "language",
                "author",
                "title",
                "source"
            ], // fields to index for full-text search
            storeFields: [
                "id",
                "cat",
                "type",
                "language",
                "author",
                "title",
                "source",
		"available"
            ] // fields to return with search results
          })
        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 for karas with the search engine
     *  - queryString: String
     * => A list of search results [(id: Int, string, cat, type, language, author, title, source)] */
    search(queryString) {
        let searchResult = this.m_search_engine.search(queryString, {
            prefix: term => term.length,
            fuzzy: term => term.length > 3 ? 0.2 : null,
            combineWith: 'AND'
        }).slice(0,200);
        return searchResult;
    }

    /* Initiate the search engine with the db
     * => a promize */
    setup_search_engine() {
        var __ret = [];
        var __sqlQuery = `SELECT id,
                            category AS cat,
                            (song_type || song_number) AS type,
                            language, author_name AS author,
                            song_name AS title, source_name AS source, available
                          FROM kara`;
        function __getRecords(db) {
            return new Promise(resolv => {
                db.all(__sqlQuery, [], (err, rows) => {
                    if (err) {
                        logger.error('db', err);
                        throw err;
                    }
                    rows.forEach(row => {
                        __ret.push(row);
                    });
                    resolv(__ret);
                });
            });
        }

        function __SetupSE(karas, search_engine = db.m_search_engine){
            search_engine.removeAll()
            logger.info("db", karas.length)
            search_engine.addAll(karas)
            logger.info("db", search_engine.documentCount)
        }

        return __getRecords(this.m_db).then(__SetupSE);
    }

    /* List all the next karas in the queue.
     * => a promize, callback with [(id: Int, string, cat, type, language,
     *    author, title, source, position: Int)] */
    queueAll() {
        var __ret = [];
        let __sqlQuery = `WITH content AS (
             SELECT kara.id AS id, string,
                    category AS cat,
                    (song_type || song_number) AS type,
                    language, author_name AS author,
                    song_name AS title, source_name AS source,
                    position
              FROM queue
              JOIN kara ON kara_id = kara.id
              GROUP BY position ORDER BY position ASC, priority DESC)
        SELECT id, string, position, cat, type, language, author, source, title
        FROM content;`;

        function __getRecords(db) {
            return new Promise(resolv => {
                db.all(__sqlQuery, [], (err, rows) => {
                    if (err) {
                        logger.error('db', err);
                        throw err;
                    }
                    rows.forEach(row => {
                        __ret.push(row);
                    });
                    resolv(__ret);
                });
            });
        }

        return __getRecords(this.m_db);
    }

    /* List all the next karas in the queue.
     * -  first: Integer => the first kara to consider being in the queue (the
     *    current one, starts ad 0)
     * -  count: Integer => the number of karas of the queue to load.
     * => a promize, callback with [(id: Int, string, cat, type, language,
     *    author, title, source, position: Int)] */
    queue(first, count) {
        var __ret = [];
        let __sqlQuery = `WITH content AS (
             SELECT kara.id AS id, string,
                    category AS cat,
                    (song_type || song_number) AS type,
                    language, author_name AS author,
                    song_name AS title, source_name AS source,
                    position
              FROM queue
              JOIN kara ON kara_id = kara.id
              WHERE position >= ${first} AND position <= ${count + first}
              GROUP BY position ORDER BY position ASC, priority DESC)
        SELECT id, string, position, cat, type, language, author, source, title
        FROM content;`;

        function __getRecords(db) {
            return new Promise(resolv => {
                db.all(__sqlQuery, [], (err, rows) => {
                    if (err) {
                        logger.error('db', err);
                        throw err;
                    }
                    rows.forEach(row => {
                        __ret.push(row);
                    });
                    resolv(__ret);
                });
            });
        }

        return __getRecords(this.m_db);
    }
}

var db = new KaraDatabase();
module.exports = db;