poa-network-monitor/common/dao.js

263 lines
9.7 KiB
JavaScript

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,
};