Sélectionner une révision Git
picture_displayer.js
-
Tanguy CHARLES a rédigéTanguy CHARLES a rédigé
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;