263 lines
9.7 KiB
JavaScript
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,
|
|
}; |