photochamber-webserver/src/databaseHelper.js

const sqlite3 = require('sqlite3');
const SessionModel = require('../model/session.js').SessionModel;
const MediaFileModel = require('../model/mediaItem.js').MediaFileModel;
const CONFIG = require('./config.js');

let ALL_MODELS = [ SessionModel, MediaFileModel ];

function DatabaseHelper() {
    this.db = null;
}

DatabaseHelper.prototype.init = function() {
    return new Promise((ok, ko) => {
        this.db = new sqlite3.Database(CONFIG.database, sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE | sqlite3.OPEN_FULLMUTEX, async (err) => {
            if (err) {
                ko(err);
                return;
            }
            let types = ALL_MODELS;
            for (let i =0; i < types.length; ++i) {
                let instance = new types[i]();
                await instance.createOrUpdateBase(this);
            }
            console.log("Database is ready");
            ok();
        });
    });
}

DatabaseHelper.prototype.runSql = function(sqlStatement, args) {
    console.info("DatabaseHelper::runSql ", sqlStatement, args);
    return new Promise((ok, ko) => {
        if (sqlStatement.indexOf('--') >= 0) {
            console.error("Rejecting SQL request containing comments");
            ko("SQL Comment Error");
            return;
        }
        try {
            this.db.all(sqlStatement, args, (err, data) => {
                if (err) {
                    console.error(err);
                    ko(err);
                } else {
                    ok(data);
                }
            });
        } catch (err) {
            console.error(err);
            ko(err);
        }
    });
}

DatabaseHelper.prototype.insertOne = async function(object) {
    let columns = [];
    let args = [];

    for (let [key, val] of Object.entries(object.describe())) {
        columns.push("`"+key+"`");
        args.push(val);
    }
    let request = "insert into `" +object.getTableName()
            +"` (" +columns.join(", ")
            +") VALUES (" +columns.map(i => "?").join(",")+")";
    await this.runSql(request, args);
}

DatabaseHelper.prototype.upsertOne = async function(obj) {
    let columns = [];
    let columnsVal = [];
    let args = [];
    let i =0;

    for (let [key, val] of Object.entries(obj.describe())) {
        columns.push("`"+key+"`");
        columnsVal.push("`"+key+"`=:"+(i++));
        args.push(val);
    }
    let request = "insert into `" +obj.getTableName()
            +"` (" +columns.join(", ")
            +") VALUES (" +columns.map((_, i) => `:${i}`).join(",")+")"
            +" ON CONFLICT DO UPDATE SET " +columnsVal.join(", ");
    await this.runSql(request, args);
}

DatabaseHelper.prototype.buildWhere = function(whereObj, columns, args) {
    columns = columns || [];
    args = args || [];
    if (!whereObj) {
        columns.push("1=1");
    } else {
        for (let [key, val] of Object.entries(whereObj)) {
            if (val === null) {
                columns.push("`"+key+"` is null");
            } else if (Array.isArray(val)) {
                columns.push("`"+key+"` in (" +val.map(i => "?").join(",") + ")");
                for (let i of val) args.push(i);
            } else {
                columns.push("`"+key+"`=?");
                args.push(val);
            }
        }
    }
    return {
        columns: columns,
        args: args
    };
}

DatabaseHelper.prototype.maxVersion = async function(instanceName) {
    let queryParts = [];
    for (let i of ALL_MODELS)
        queryParts.push("select max(" +i.prototype.versionColumn.call(null) +") as v from " +i.prototype.getTableName.call(null) +" where instance=:0");
    let version = await this.runSql("select max(v) as v from (" +queryParts.join(" union ") +")", [ instanceName ]);
    return (version?.[0]?.v) || 0;
}

DatabaseHelper.prototype.versionFetch = async function(objectPrototype, minVersion) {
    let query = "select * from `" +objectPrototype.prototype.getTableName.call(null) +"` where `" +objectPrototype.prototype.versionColumn.call(null)+"` > ? and instance=?";
    let result = [];
    for (let i of await this.runSql(query, [minVersion, CONFIG.instanceHostname]))
    {
        let resultObj = new objectPrototype();
        resultObj.fromDb(i);
        result.push(resultObj);
    }
    return result;
}

DatabaseHelper.prototype.fetch = async function(objectPrototype, where, orderBy) {
    let result = await this.fetchRaw("*", objectPrototype.prototype.getTableName.call(null), where, orderBy);
    let resultArr = [];
    for (let i of result)
    {
        let resultObj = new objectPrototype();
        resultObj.fromDb(i);
        resultArr.push(resultObj);
    }
    return resultArr;
}

DatabaseHelper.prototype.fetchRaw = async function(columns, tableName, where, orderBy) {
    let whereArgs = this.buildWhere(where);
    let query = "select " + (Array.isArray(columns) ? columns.join(","): columns) +" from `" +tableName +"` where " +whereArgs.columns.join(" and ");
    if (orderBy)
        query += " ORDER BY " +Object.keys(orderBy || {}).map(i => "`"+i+"` " +(orderBy[i] === 'DESC' ? "DESC":"ASC")).join(",");
    return await this.runSql(query, whereArgs.args);
}

DatabaseHelper.prototype.findOne = async function(objectPrototype, where) {
    let whereArgs = this.buildWhere(where);
    let result = await this.runSql("select * from `" +objectPrototype.prototype.getTableName.call(null) +"` where " +whereArgs.columns.join(" and "), whereArgs.args);
    if (result && result.length)
    {
        let resultObj = new objectPrototype();
        resultObj.fromDb(result[0]);
        return resultObj;
    }
    return null;
}

DatabaseHelper.prototype.update = async function(where, object) {
    if (object.lastUpdated)
        object.lastUpdated = new Date();
    await this.rawUpdate(object, where, object.describe());
}

DatabaseHelper.prototype.rawUpdate = async function(model, where, values) {
    let columns = [];
    let args = [];

    for (let [key, val] of Object.entries(values)) {
        columns.push("`"+key+"`=?");
        args.push(val);
    }
    let whereArgs = this.buildWhere(where, [], args);
    let query = "update `" +(model.prototype?.getTableName ? model.prototype.getTableName() : model.getTableName())
            +"` set " +columns.join(", ")
            +" WHERE " +whereArgs.columns.join(" and ");
    await this.runSql(query, args);
}

module.exports.DatabaseHelper = new DatabaseHelper();