Skip to content
Extraits de code Groupes Projets
Sélectionner une révision Git
  • 5054f202391b86aeb99899589bebb444187c4390
  • master par défaut protégée
  • dev-deurstann-3
  • dev-deurstann-2
  • dev-kubat
  • dev-deurstann
  • dev-sting
7 résultats

db.js

Blame
  • db.js 5,66 Kio
    const logger = require.main.require('./common/logger.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(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 ${this.m_karaPath}`);
        }
    
        /* Search from the kara table, a string. Can be anything (query, name,
         * language, source, etc).
         * - queryString: String => a promize, callback with [(id: Int, string,
         *   cat, type, language, author, title, source)] */
        search(queryString) {
            var __ret = [];
            let __sqlQuery = `SELECT id, string,
                                category AS cat,
                                (song_type || song_number) AS type,
                                language, author_name AS author,
                                song_name AS title, source_name AS source
                              FROM kara
                              WHERE string LIKE ? OR author_name COLLATE nocase = ?`;
    
            function __getRecords(db) {
                return new Promise(resolv => {
                    db.all(
                        __sqlQuery,
                        [`%${queryString}%`, queryString],
                        (err, rows) => {
                            if (err) {
                                logger.error(err);
                                throw err;
                            }
                            rows.forEach(row => {
                                __ret.push(row);
                            });
                            resolv(__ret);
                        }
                    );
                });
            }
    
            return __getRecords(this.m_db);
        }
    
        /* List all the kara in the db.
         * => a promize, callback with [(id: Int, string, cat, type, language,
         *    author, title, source)] */
        all() {
            var __ret = [];
            let __sqlQuery = `SELECT id, string,
                                category AS cat,
                                (song_type || song_number) AS type,
                                language, author_name AS author,
                                song_name AS title, source_name AS source
                              FROM kara;`;
    
            function __getRecords(db) {
                return new Promise(resolv => {
                    db.all(__sqlQuery, [], (err, rows) => {
                        if (err) {
                            logger.error(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(err);
                            throw err;
                        }
                        rows.forEach(row => {
                            __ret.push(row);
                        });
                        resolv(__ret);
                    });
                });
            }
    
            return __getRecords(this.m_db);
        }
    }
    
    module.exports = KaraDatabase;