sqlite3PlayNodeJs/database.js

const config = require('./config');
const sqlite3 = require('sqlite3').verbose();

let db = new sqlite3.Database(config.dbFileName, sqlite3.OPEN_READWRITE, (err) => {
    if (err) {
        console.error("ERROR: opening DB failed: ", err.message);
    }
    console.log('Connected.');
});


let query = (SQL) => {
    return new Promise((resolve, reject) => {
        // this if assumes only SELECT has a resultset
        if (SQL.toUpperCase().startsWith("SELECT")) {
            db.all(SQL, [], (err, rows) => {
                if (err) {
                    reject("ERROR: fetching data: " + err + err.stack);
                }
                resolve(rows);
            });
        } else {
            db.run(SQL, function (err) {
                if (err) {
                    reject("ERROR: running statement: (" + SQL + ") " + err + err.stack);
                }
                if (SQL.toUpperCase().startsWith("INSERT")) {
                    // the lastID is just the rowID - need to resolve the actual primary-Key ID by rowid.
                    // Note: this expects that there is an ID column in every table!
                    let table = SQL.replace(/^insert inot /i,'').replace(/ .*$/,'');
                    let sqlGetID = "select ID from " + table + " where rowid=" + this.lastID;
                    console.log(sqlGetID);

                    db.run(sqlGetID, function (err, rows) {
                        if (err) {
                            reject("ERROR: getting Record-ID after insert: " + err + err.stack);
                        }
                        resolve(rows[0].ID);
                    });

                }
                resolve(true);
            });
        }
    });
};

let close = () => {
    return new Promise((resolve, reject) => {
        db.close((err) => {
            if (err) {
                reject("ERROR: closing DB: ", err.message);
            }
            resolve(true);
        });
    });
}

let exitHandler = (eventType) => {
    console.log("DB-Cleaning up (" + eventType + ")");
    close().then(process.exit);
}

// make sure we close the DB in case the process ends.
[`exit`, `SIGINT`, `SIGUSR1`, `SIGUSR2`, `uncaughtException`, `SIGTERM`].forEach((eventType) => {
    process.on(eventType, exitHandler.bind(null, eventType));
})

let main = async () => {
    query('insert into log (message) values (\'Outside\')');
    let rows = null;
    try {
        let newId = await query('insert into log (message) values (\'start\')');
        console.log("ID created for start was " + newId);
        rows = await query('select * from Option');
        rows.forEach((row) => {
            console.log(row.ID + "\t" + row.PutCall);
        })
        await query('insert into log (message) values (\'end\')');
    } catch (ex) {
        console.error('Error', ex);
    }
    console.re
    process.stdin.resume();//so the program will not close instantly
}

main();


module.exports = {
    query
};