def getEpochChartData(conn, hPoolId, more, currentEpoch):
maxEpochs = 10
if more == 'true':
maxEpochs = 90
sql = "select epochNumber as title, signed, askedToSign, "
sql += " (askedToSign - signed) as notSigned, signPer, rewards "
sql += " from " + tables.hpoolperf
sql += " where hPoolId=%s "
sql += " and mode = %s "
dt = datetime.datetime.now().replace(hour=0,
minute=0,
second=0,
microsecond=0)
# oldestTime = (dt - datetime.timedelta(days=maxEpochs))
# sql += " and syncTime >= '" + str(oldestTime) + "'"
sql += " and epochNumber > %s "
sql += " order by 1 "
# logger.info(sql)
return dbUtil.listResultsWithConn(sql, conn,
(hPoolId, constants.H_EPOCH_MODE,
(currentEpoch - maxEpochs)))
def listPoolsSummary(conn, app):
sql, args = listHPoolsSql(None)
# logger.info(args)
data = dbUtil.listResultsWithConn(sql, conn, args)
# logger.info(data)
coinStat = commonUtils.getCoinStat(conn, app)
return dbUtil.combineResults2("data", data, "coinStat", coinStat)
def listHPoolsAsMap(conn):
sql = " select * "
sql += " from " + tables.hpool + " p "
# logger.info(sql)
data = dbUtil.listResultsWithConn(sql, conn)
# logger.info(data)
return commonUtils.getMapFromList(data, "address")
def listEvents(conn, app, event, poolId, startTime):
# logger.info("in listevents method")
size = 500
subType = None
if "size" in event["queryStringParameters"]:
size = int(event["queryStringParameters"]["size"])
if "subType" in event["queryStringParameters"]:
subType = event["queryStringParameters"]["subType"]
# logger.info("params are: {}, {}".format(poolId, subType))
sql = listEventsSql(poolId, None, size, subType)
params = ()
if poolId:
if subType and subType != "others" and subType != "large" and subType != "val" and subType != "fee":
params = (poolId, subType)
else:
params = poolId
data = dbUtil.listResultsWithConn(sql, conn, params)
val = harmonyData.getHPoolById(conn, poolId)
return dbUtil.combineResults2("val", val, "data", data)
else:
if subType and subType != "others" and subType != "large" and subType != "val" and subType != "fee":
params = subType
return dbUtil.listResultsWithResponseWithConn(sql, conn, params)
def listAllUnelectedValidators(conn):
sql = " select p.* "
sql += " from " + tables.hpool + " p "
sql += " where isEverElected='True' and status != 'Elected' "
# logger.info(args)
data = dbUtil.listResultsWithConn(sql, conn)
return data
def getHealth(conn, chatId, userId):
setting = tgBotUtils.prepUserSettings(conn, chatId, userId, app)
if setting["favPools"] is None or setting["favPools"] == "":
message = "Favorite validators are not configured. Check /settings command"
else:
sql = harmonyData.listPoolsSqlWithIds(app, setting["favPools"])
# logger.info(sql)
validators = dbUtil.listResultsWithConn(sql, conn)
lastUpdatedGap = commonUtils.getEventLastUpdated(
conn, eventName.syncHarmonyValidators)
i = 1
message = "List of favorite validators: \n"
message += "Last updated: <b>" + str(int(
lastUpdatedGap / 60)) + "</b> minutes ago.\n\n"
for validator in validators:
message += "#" + str(i) + " - " + getTgValDetails(
conn, validator, None)
i += 1
message += "\n"
message += getToolsLink("") + " /health"
# logger.info(message)
return message
def getNotMapForElectionCheck(conn, epoch, next):
# logger.info("in getNotMapForElectionCheck(epoch: {}, next: {})".format(epoch, next))
sql = "select * from "
sql += tables.hnotification + " hn "
sql += " where hn.epochNumber = %s "
sql += " and hn.notificationType in ( "
if next:
sql += "'" + hConstants.NOTIFICATION_TYPE_RISK_UNELECTED + "',"
sql += "'" + hConstants.NOTIFICATION_TYPE_RISK_PARTIALLY_ELECTED + "',"
sql += "'" + hConstants.NOTIFICATION_TYPE_RISK_HIGH_BID + "'"
else:
sql += "'" + hConstants.NOTIFICATION_TYPE_UNELECTED + "',"
sql += "'" + hConstants.NOTIFICATION_TYPE_PARTIALLY_ELECTED + "',"
sql += "'" + hConstants.NOTIFICATION_TYPE_UNELECTED_FOR_LONG + "'"
sql += " ) "
sql += " and (hn.watchInd = '1' or hn.notifiedInd='0') "
args = (epoch)
# logger.info("sql: {}, args: {}".format(sql, args))
listData = dbUtil.listResultsWithConn(sql, conn, args)
# logger.info(listData)
badKeyMap = commonUtils.getMapFromList2Keys(listData, "notificationType",
"hPoolId")
# logger.info("badKeyMap: {}".format(badKeyMap))
return badKeyMap
def getAllSlots(conn, chatId, userId, current):
sql, args = harmonyData.listHPoolsSql(None)
# logger.info(sql)
validators = dbUtil.listResultsWithConn(sql, conn)
# logger.info(validators)
message2 = None
message3 = None
if current:
header = "Current Epoch"
else:
header = "Next Epoch"
header += " - all validators bid ranking details: \n"
response, response2, response3 = getValidatorsSlotMessage(
validators, current)
# logger.info(len(response))
# logger.info(len(response2))
footer = "\n" + getEpochSummary(conn) + "\n"
if response3:
message3 = "..contd - " + header + response3 + footer + command
message2 = "..contd - " + header + response2 + footer + command
message = header + response
elif response2:
message2 = "..contd - " + header + response2 + footer + command
message = header + response
else:
message = header + response + footer + command
return message, message2, message3
def getEpochSlots(conn, chatId, userId, current):
setting = tgBotUtils.prepUserSettings(conn, chatId, userId, hTgBot.app)
message2 = None
message3 = None
if setting["favPools"] is None or setting["favPools"] == "":
message = "Favorite validators are not configured. Check /settings command"
else:
sql = harmonyData.listPoolsSqlWithIds(hTgBot.app, setting["favPools"])
# logger.info(sql)
validators = dbUtil.listResultsWithConn(sql, conn)
# logger.info(bidDetails)
if current:
header = "Current Epoch - "
else:
header = "Next Epoch - "
header += "favorite validators bid slot details: \n"
response, response2, response3 = getValidatorsSlotMessage(
validators, current)
footer = "\n" + getEpochSummary(conn) + "\n"
if response3:
message3 = "..contd - " + header + response3 + footer + command
message2 = "..contd - " + header + response2 + footer + command
message = header + response
elif response2:
message2 = "..contd - " + header + response2 + footer + command
message = header + response
else:
message = header + response + footer + command
return message, message2, message3
def getConfiguredPools(conn, setting):
configuredPools = setting["favPools"]
if configuredPools is None or configuredPools == "":
return ""
configuredPoolList = configuredPools.split(",")
sql, args = harmonyData.listHPoolsSql(None, " avgEri desc")
validators = dbUtil.listResultsWithConn(sql, conn, args)
message = ""
# logger.info("all validators: {}".format(validators))
# logger.info("processing configured pools")
for cp in configuredPoolList:
# logger.info("processing {}".format(cp))
for validator in validators:
# logger.info("comparing cp {} with validator {} ".format(cp, validator["hPoolId"]))
if cp == str(validator["hPoolId"]):
# logger.info("found validator")
message += "\t - " + validator["name"] + " - " + validator[
"address"] + "\n"
break
# logger.info(message)
return message
def getPerfChartData(conn, hPoolId, hourly, mode, more):
if hourly:
dateColumn = "date_format(syncTime,'%%Y-%%m-%%d %%H')"
else:
dateColumn = "date_format(syncTime,'%%Y-%%m-%%d')"
sql = "select " + dateColumn + " as title, epochNumber, signed, askedToSign, "
sql += " (askedToSign - signed) as notSigned, signPer, rewards "
sql += " from " + tables.hpoolperf
sql += " where hPoolId=%s "
sql += " and mode = %s "
dt = datetime.datetime.now().replace(minute=0, second=0, microsecond=0)
if hourly:
maxHours = 10
if more == 'true':
maxHours = 240
oldestTime = (dt - datetime.timedelta(hours=maxHours))
sql += " and syncTime >= '" + str(oldestTime) + "'"
else:
dt = datetime.datetime.now().replace(hour=0,
minute=0,
second=0,
microsecond=0)
oldestTime = (dt - datetime.timedelta(days=14))
sql += " and syncTime >= '" + str(oldestTime) + "'"
# sql += " group by " + dateColumn
sql += " order by 1 "
# logger.info(sql)
return dbUtil.listResultsWithConn(sql, conn, (hPoolId, mode))
def listValidators(conn):
sql = "select hPoolId, name "
sql += " from " + tables.hpool
# logger.info(sql)
listData = dbUtil.listResultsWithConn(sql, conn)
return commonUtils.getMapWithValueFromList(listData, "hPoolId", "name")
def listEvents(conn):
sql = "select e.*, round((UNIX_TIMESTAMP() - e.epochBlockTime) / 60,0) as elapsedTime "
sql += " from " + eventTable + " e "
sql += " where e.notifiedInd = '0' "
sql += " order by e.blockNumber limit 1000 "
# logger.info(sql)
return dbUtil.listResultsWithConn(sql, conn)
def listPendingNotifications(conn):
sql = "select * "
sql += " from " + notificationTable + " e "
sql += " where e.notifiedInd = '0' "
sql += " order by e.creationDate limit 1000 "
# logger.info(sql)
return dbUtil.listResultsWithConn(sql, conn)
def getAllPropsAsMap(conn, app):
sql = "select * "
sql += " from " + tables.propertytable
sql += " where app = %s "
list = dbUtil.listResultsWithConn(sql, conn, app)
return commonUtils.getMapWithValueFromList(list, "name", "value")
def listLatestShardDelay(conn):
sql = " select * "
sql += " from " + tables.hsharddelay
sql += " order by syncTime desc limit 4 "
# logger.info(sql)
data = dbUtil.listResultsWithConn(sql, conn)
return data
def listBlsKeySyncDetails(conn, app):
sql, args = harmonyData.listHPoolsSql(None)
validators = dbUtil.listResultsWithConn(sql, conn)
keys = listBlsKeys(conn)
# shards = listLatestShardDelay(conn)
# return dbUtil.combineResults3("validators", validators, "keys", keys, "shards", shards)
return dbUtil.combineResults2("validators", validators, "keys", keys)
def listDataForEpochSign(conn, app):
sql, args = listHPoolsSql(None)
# logger.info(args)
data = dbUtil.listResultsWithConn(sql, conn)
syncStatus = commonUtils.getSyncStatus(
conn, app, constants.SYNC_STATUS_EPOCH_FOR_SIGN)
epoch = syncStatus["syncedTillEpoch"]
return dbUtil.combineResults2("validators", data, "epoch", epoch)
def listNotificationUsersForPerf(conn):
sql = "select * "
sql += " from " + tables.tgsetting
sql += " where app = %s and (notifyDelPerf = 'True' "
sql += " or notifyValPerf = 'True' "
sql += " ) "
# logger.info(sql)
return dbUtil.listResultsWithConn(sql, conn, constants.APP)
def listNodeVersions(conn):
sql = "select hp.hPoolId, name, blskey, "
sql += "(case when nodeVersion is null then 'Unknown' else nodeVersion end) as nodeVersion "
sql += "from " + tables.hblskey + " bk "
sql += "inner join " + tables.hpool + " hp on bk.hPoolId=hp.hPoolId "
sql += "where status='Elected' "
logger.info(sql)
return dbUtil.listResultsWithConn(sql, conn)
def getRewardsHistorySummary(conn, address, dateFormat):
sql = "select DATE_FORMAT(from_unixtime(epochBlockTime), '" + dateFormat + "') as withdrawalTime, "
sql += " sum(amount) as amount from " + tables.hevent
sql += " where address = %s and eventType=%s"
sql += " group by DATE_FORMAT(from_unixtime(epochBlockTime), '" + dateFormat + "') "
sql += " order by 1 desc"
# logger.info(sql)
return dbUtil.listResultsWithConn(
sql, conn, (address, hConstants.H_EVENT_COLLECT_REWARDS))
def getValsWithLowerVersions(conn, version):
sql = "select bk.hPoolId, min(nodeVersion) as nodeVersion "
sql += "from " + tables.hblskey + " bk "
sql += "inner join " + tables.hpool + " hp on bk.hPoolId=hp.hPoolId "
sql += "where status='Elected' "
sql += "and nodeVersion < %s "
sql += "group by bk.hPoolId "
# logger.info(sql)
return dbUtil.listResultsWithConn(sql, conn, version)
def getBlockRateHistory(conn): sql = "SELECT epochNumber as title, " sql += " round(avg(case when shardId=0 then blockRate else null end),3) as shard0BlockRate, " sql += " round(avg(case when shardId=1 then blockRate else null end),3) as shard1BlockRate, " sql += " round(avg(case when shardId=2 then blockRate else null end),3) as shard2BlockRate, " sql += " round(avg(case when shardId=3 then blockRate else null end),3) as shard3BlockRate " sql += " from " + tables.hblockrate sql += " group by epochNumber order by epochNumber " # logger.info(sql) return dbUtil.listResultsWithConn(sql, conn)
def getRewardsSummary(conn, address):
sql = "select date(from_unixtime(epochBlockTime)) as procDate, sum(amount) as rewards from "
sql += tables.hevent
sql += " where address = %s and eventType=%s"
sql += " group by date(from_unixtime(epochBlockTime)) "
sql += " order by 1 "
# logger.info(sql)
data = dbUtil.listResultsWithConn(
sql, conn, (address, hConstants.H_EVENT_COLLECT_REWARDS))
return data
def getHStatusMap(conn):
statusList = dbUtil.listResultsWithConn(listHStatusSummarySql(), conn)
# logger.info(statusList)
statusMap = {
constants.H_ELECTED: 0,
constants.H_ELIGIBLE: 0,
constants.H_NOT_ELIGIBLE: 0
}
return commonUtils.getStatusSummaryMap(statusList, {})
def listHPoolPerfByEpoch(conn, app, epochNumber, mode):
sql = " select * "
sql += " from " + tables.hpoolperf + " p "
sql += " where p.epochNumber =%s and mode=%s "
# logger.info(sql)
startTime = commonUtils.getCurrentTime()
data = dbUtil.listResultsWithConn(sql, conn, (epochNumber, mode))
commonUtils.logTimeDiff(startTime, "after processing listHPoolPerfByEpoch")
return data
def getRewardsDetails(conn, address):
sql = "select epochBlockTime as withdrawalTime, amount "
sql += " from " + tables.hevent
sql += " where address = %s and eventType=%s"
# sql += " group by date(from_unixtime(epochBlockTime)) "
sql += " order by 1 desc"
# logger.info(sql)
data = dbUtil.listResultsWithConn(
sql, conn, (address, hConstants.H_EVENT_COLLECT_REWARDS))
return data
def listKeyDataByEpoch(conn, blsKey, epoch):
dateColumn = "date_format(from_unixtime(syncTime),'%%Y-%%m-%%d %%H:%%i')"
sql = "select " + dateColumn + " as title, kp.* "
sql += " from " + tables.hkeyperf + " kp "
sql += " where blsKey = %s "
sql += " and epochNumber=%s"
sql += " order by syncTime "
# logger.info(sql)
data = dbUtil.listResultsWithConn(sql, conn, (blsKey, epoch))
return data
def listEventsByPoolId(conn, poolId, days=None):
# logger.info("in listEventsByPoolId method")
sql = listEventsSql(poolId, None, 200, None, days)
# logger.info(sql)
# , (address)
args = (poolId)
if days:
currentTime = int(time.time())
useTime = currentTime - days * 86400
args = (poolId, useTime)
# logger.info(args)
return dbUtil.listResultsWithConn(sql, conn, args)
def listNodeVersionSummary(conn):
sql = "select "
sql += "(case when nodeVersion is null then 'Unknown' else nodeVersion end) "
sql += " as nodeVersion, count(*) as total "
sql += "from " + tables.hblskey + " bk "
sql += "inner join " + tables.hpool + " hp on bk.hPoolId=hp.hPoolId "
sql += "where status='Elected' "
sql += "group by (case when nodeVersion is null then 'Unknown' else nodeVersion end) "
sql += "order by 2 desc"
logger.info(sql)
return dbUtil.listResultsWithConn(sql, conn)