poa-network-monitor/common/dao.js

let sqlite3 = require('sqlite3').verbose();
let db = new sqlite3.Database('./poa_monitor.db');

function SqlDao(networkName) {
    this.missedRoundsTableName = "missed_rounds_" + networkName;
    this.miningRewardTableName = "mining_reward_" + networkName;
    this.missedTxsTableName = "missed_txs_" + networkName;
    this.txsPublicRpcTableName = "txs_public_rpc_" + networkName;
    this.txsInfuraTableName = "txs_Infura_" + networkName;
    this.reorgsTableName = "reorgs_" + networkName;
    this.rewardTransferTableName = "reward_transfer_" + networkName;
    this.rewardByBlockTableName = "reward_by_block_" + networkName;

    // Create

    this.missedRoundsTableCreateSql = " CREATE TABLE IF NOT EXISTS " + this.missedRoundsTableName + " (id INTEGER PRIMARY KEY AUTOINCREMENT," +
        " time TEXT," +
        " passed INTEGER NOT NULL CHECK (passed IN (0,1))," +
        " lastBlock TEXT," +
        " missedValidators TEXT)";
    this.miningRewardTableCreateSql = " CREATE TABLE IF NOT EXISTS " + this.miningRewardTableName + " (id INTEGER PRIMARY KEY AUTOINCREMENT," +
        " time TEXT," +
        " passed INTEGER NOT NULL CHECK (passed IN (0,1))," +
        " error TEXT," +
        " transactions TEXT," +
        " rewardDetails TEXT)";
    this.missedTxsTableCreateSql = " CREATE TABLE IF NOT EXISTS " + this.missedTxsTableName + " (id INTEGER PRIMARY KEY AUTOINCREMENT," +
        " time TEXT," +
        " passed INTEGER NOT NULL CHECK (passed IN (0,1))," +
        " lastBlock TEXT," +
        " validatorsMissedTxs TEXT," +
        " failedTxs TEXT)";

    this.txsPublicRpcTableCreateSql = " CREATE TABLE IF NOT EXISTS " + this.txsPublicRpcTableName + " (id INTEGER PRIMARY KEY AUTOINCREMENT," +
        " time TEXT," +
        " passed INTEGER NOT NULL CHECK (passed IN (0,1))," +
        " errorMessage TEXT," +
        " transactionHash TEXT," +
        " blockNumber TEXT," +
        " miner TEXT)";

    this.txsInfuraTableCreateSql = " CREATE TABLE IF NOT EXISTS " + this.txsInfuraTableName + " (id INTEGER PRIMARY KEY AUTOINCREMENT," +
        " time TEXT," +
        " passed INTEGER NOT NULL CHECK (passed IN (0,1))," +
        " errorMessage TEXT," +
        " transactionHash TEXT," +
        " blockNumber TEXT," +
        " miner TEXT)";

    this.reorgsTableCreateSql = " CREATE TABLE IF NOT EXISTS " + this.reorgsTableName + " (id INTEGER PRIMARY KEY AUTOINCREMENT," +
        " time TEXT," +
        " toBlock TEXT," +
        " changedBlocks TEXT)";

    this.rewardTransferTableCreateSql = " CREATE TABLE IF NOT EXISTS " + this.rewardTransferTableName + " (id INTEGER PRIMARY KEY AUTOINCREMENT," +
        " time TEXT," +
        " passed INTEGER NOT NULL CHECK (passed IN (0,1))," +
        " validator TEXT," +
        " payoutKey TEXT," +
        " error TEXT," +
        " transferTx TEXT," +
        " otherTxs TEXT," +
        " blockNumber TEXT)";

    this.rewardByBlockTableCreateSql = " CREATE TABLE IF NOT EXISTS " + this.rewardByBlockTableName + " (id INTEGER PRIMARY KEY AUTOINCREMENT," +
        " time TEXT," +
        " passed INTEGER NOT NULL CHECK (passed IN (0,1))," +
        " block INTEGER," +
        " validator TEXT," +
        " payoutKey TEXT," +
        " error TEXT)";

    this.createMissingRoundsTable = function () {
        run(this.missedRoundsTableCreateSql);
    };

    this.createRewardTable = function () {
        run(this.miningRewardTableCreateSql);
    };

    this.createTxsTable = function () {
        run(this.missedTxsTableCreateSql);
    };

    this.createTxsPublicRpcTable = function () {
        run(this.txsPublicRpcTableCreateSql);
    };

    this.createTxsInfuraTable = function () {
        run(this.txsInfuraTableCreateSql);
    };

    this.createReorgsTable = function () {
        run(this.reorgsTableCreateSql);
    };

    this.createRewardTransferTable = function () {
        run(this.rewardTransferTableCreateSql);
    };

    this.createRewardByBlockTable = function () {
        run(this.rewardByBlockTableCreateSql);
    };

    // Insert

    this.addToMissingRounds = async function (params) {
        await run("INSERT INTO " + this.missedRoundsTableName + " (time, passed, lastBlock, missedValidators) VALUES ( ?, ?, ?, ?)",
            params);
    };

    this.addToRewardTable = async function (params) {
        await run("INSERT INTO " + this.miningRewardTableName + " (time, passed, error, rewardDetails, transactions) VALUES ( ?, ?, ?, ?, ?)",
            params);
    };

    this.addToTxsTable = async function (params) {
        await run("INSERT INTO " + this.missedTxsTableName + " (time, passed, lastBlock, validatorsMissedTxs, failedTxs) VALUES ( ?, ?, ?, ?, ?)",
            params);
    };

    this.addToTxsPublicRpcTable = async function (params) {
        await run("INSERT INTO " + this.txsPublicRpcTableName + " (time, passed, errorMessage, transactionHash, blockNumber, miner) VALUES ( ?, ?, ?, ?, ?, ?)",
            params);
    };

    this.addToTxsInfuraTable = async function (params) {
        await run("INSERT INTO " + this.txsInfuraTableName + " (time, passed, errorMessage, transactionHash, blockNumber, miner) VALUES ( ?, ?, ?, ?, ?, ?)",
            params);
    };

    this.addToReorgsTable = async function (params) {
        await run("INSERT INTO " + this.reorgsTableName + " (time, toBlock, changedBlocks) VALUES ( ?, ?, ?)",
            params);
    };

    this.addToRewardTransfer = async function (params) {
        await run("INSERT INTO " + this.rewardTransferTableName + " (time, passed, validator, payoutKey, error, blockNumber, transferTx, otherTxs) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?)",
            params);
    };

    this.addToRewardByBlockTable = async function (params) {
        await run("INSERT INTO " + this.rewardByBlockTableName + " (time, passed, block, validator, payoutKey, error) VALUES ( ?, ?, ?, ?, ?, ?)",
            params);
    };

    // Select

    this.getMissedRounds = async function (lastSeconds) {
        console.log("getMissedRounds, lastSeconds: " + lastSeconds);
        return await allWithTime("SELECT * FROM " + this.missedRoundsTableName + " where 1 ", lastSeconds);
    };

    this.getFailedMissedRounds = async function (lastSeconds) {
        return allWithTime("SELECT * FROM " + this.missedRoundsTableName + " where passed = 0 ", lastSeconds);
    };

    this.getReorgs = async function (lastSeconds) {
        return allWithTime("SELECT * FROM " + this.reorgsTableName + " where 1 ", lastSeconds);
    };

    this.getRewards = async function (lastSeconds) {
        return allWithTime("SELECT * FROM " + this.miningRewardTableName + " where 1 ", lastSeconds);
    };

    this.getFailedRewards = async function (lastSeconds) {
        return allWithTime("SELECT * FROM " + this.miningRewardTableName + " where passed = 0 ", lastSeconds);
    };

    this.getMissedTxs = async function (lastSeconds) {
        return allWithTime("SELECT * FROM " + this.missedTxsTableName + " where 1 ", lastSeconds);
    };

    this.getFailedMissedTxs = async function (lastSeconds) {
        return allWithTime("SELECT * FROM " + this.missedTxsTableName + " where passed = 0 ", lastSeconds);
    };

    this.getTxsPublicRpc = async function (lastSeconds) {
        return allWithTime("SELECT * FROM " + this.txsPublicRpcTableName + " where 1 ", lastSeconds);
    };

    this.getTxsInfura = async function (lastSeconds) {
        return allWithTime("SELECT * FROM " + this.txsInfuraTableName + " where 1 ", lastSeconds);
    };

    this.getFailedTxsPublicRpc = async function (lastSeconds) {
        return allWithTime("SELECT * FROM " + this.txsPublicRpcTableName + " where passed = 0 ", lastSeconds);
    };

    this.getFailedTxsInfura = async function (lastSeconds) {
        return allWithTime("SELECT * FROM " + this.txsInfuraTableName + " where passed = 0 ", lastSeconds);
    };

    this.getRewardTransfers = async function (lastSeconds) {
        return await allWithTime("SELECT * FROM " + this.rewardTransferTableName + " where 1 ", lastSeconds);
    };

    this.getFailedRewardTransfers = async function (lastSeconds) {
        return await allWithTime("SELECT * FROM " + this.rewardTransferTableName + " where passed = 0 ", lastSeconds);
    };

    this.getRewardByBlock = async function (lastSeconds) {
        return await allWithTime("SELECT * FROM " + this.rewardByBlockTableName + " where 1 ", lastSeconds);
    };

    this.getFailedRewardByBlock = async function (lastSeconds) {
        return await allWithTime("SELECT * FROM " + this.rewardByBlockTableName + " where passed = 0 ", lastSeconds);
    };

    this.getLatestRewardByBlockRecord = async function () {
        return await all("SELECT * FROM " + this.rewardByBlockTableName + " where block = (SELECT MAX(block)  FROM " + this.rewardByBlockTableName + ") ");
    };


    this.closeDb = function () {
        db.close();
    };

}

function run(sql, params) {
    return new Promise((resolve, reject) => {
        db.run(sql, params, (err, respose) => {
            if (err) {
                console.log('Error running sql: ' + sql);
                console.log(err);
                reject(err);
            } else {
                resolve(respose);
            }
        })
    })

}

function all(sql, params) {
    return new Promise((resolve, reject) => {
        db.all(sql, params, (err, rows) => {
            if (err) {
                console.log('Error running sql: ' + sql);
                console.log(err);
                reject(err);
            } else {
                resolve(rows);
            }
        })
    })
}

function allWithTime(sql, lastSeconds) {
    if (lastSeconds) {
        let fromTime = new Date(Date.now() - lastSeconds * 1000).toISOString();
        console.log("fromTime: " + fromTime);
        return all(sql + " and time >= '" + fromTime + "'");
    }
    else {
        return all(sql);
    }
}

module.exports = {
    SqlDao,
};