def payouts_to_address(self, address): resultset = self._cursor.execute_and_fetchall(""" SELECT DISTINCT trs."{transactions[id]}", trs."{transactions[amount]}", trs."{transactions[timestamp]}", trs."{transactions[recipient_id]}", trs."{transactions[sender_id]}", trs."{transactions[type]}", trs."{transactions[fee]}", mem."{mem_accounts[username]}", ENCODE(mem."{mem_accounts[public_key]}"::BYTEA, 'hex'), blocks."{blocks[height]}", trs."{transactions[vendor_field]}" FROM {mem_accounts[table]} mem INNER JOIN {transactions[table]} trs ON (trs."{transactions[sender_id]}"=mem."{mem_accounts[address]}") INNER JOIN {blocks[table]} blocks ON (blocks."{blocks[id]}" = trs."{transactions[block_id]}") WHERE trs."{transactions[recipient_id]}" = '{address}' AND mem."{mem_accounts[is_delegate]}" = 1 ORDER BY blocks."{blocks[height]}" ASC """.format(transactions=self.scheme['transactions'], mem_accounts=self.scheme['mem_accounts'], address=address, blocks=self.scheme['blocks'])) labelset = [ 'tx_id', 'amount', 'timestamp', 'recipient_id', 'sender_id', 'type', 'fee', 'username', 'public_key', 'height', 'vendor_field' ] return dictionify(resultset, labelset)
def transactions_from_address(self, address): resultset = self._cursor.execute_and_fetchall(""" SELECT trs."{transactions[id]}" AS tx_id, trs."{transactions[amount]}", trs."{transactions[timestamp]}", trs."{transactions[recipient_id]}", trs."{transactions[sender_id]}", trs."{transactions[type]}", trs."{transactions[fee]}", trs."{transactions[rawasset]}", blocks."{blocks[height]}", trs."{transactions[vendor_field]}" FROM {transactions[table]} AS trs INNER JOIN {blocks[table]} AS blocks ON (blocks."{blocks[id]}" = trs."{transactions[block_id]}") WHERE trs."{transactions[sender_id]}" = '{address}' OR trs."{transactions[recipient_id]}" = '{address}' ORDER BY blocks."{blocks[height]}" ASC """.format(transactions=self.scheme['transactions'], address=address, blocks=self.scheme['blocks'])) labelset = [ 'tx_id', 'amount', 'timestamp', 'recipient_id', 'sender_id', 'type', 'fee', 'rawasset', 'height', 'vendor_field' ] return dictionify(resultset, labelset)
def get_historic_voters(self, address): delegate_public_key = self.account_details( address=address)['public_key'] plusvote = '{{"votes":["+{0}"]}}'.format(delegate_public_key) resultset = self._cursor.execute_and_fetchall(""" SELECT trs."{transactions[sender_id]}", trs."{transactions[timestamp]}", trs."{transactions[id]}", blocks."{blocks[height]}" FROM {transactions[table]} AS trs INNER JOIN {blocks[table]} AS blocks ON (blocks."{blocks[id]}" = trs."{transactions[block_id]}") WHERE trs."{transactions[rawasset]}" = '{plusvote}' ORDER BY {blocks[table]}."{blocks[height]}" ASC; """.format(transactions=self.scheme['transactions'], mem_accounts=self.scheme['mem_accounts'], address=address, plusvote=plusvote, blocks=self.scheme['blocks'])) labelset = ['address', 'timestamp', 'id', 'height'] return dictionify(resultset, labelset)
def get_last_out_transactions(self, address): resultset = self._cursor.execute_and_fetchall(""" SELECT trs."{transactions[recipient_id]}", trs."{transactions[timestamp]}", blocks."{blocks[height]}", trs."{transactions[id]}", trs."{transactions[amount]}", trs."{transactions[fee]}" FROM {transactions[table]} trs INNER JOIN {blocks[table]} blocks ON (blocks."{blocks[id]}" = trs."{transactions[block_id]}"), (SELECT MAX(ts."{transactions[timestamp]}") AS max_timestamp, ts."{transactions[recipient_id]}" FROM {transactions[table]} AS ts WHERE ts."{transactions[sender_id]}" = '{address}' GROUP BY ts."{transactions[recipient_id]}") AS maxresults WHERE trs."recipientId" = maxresults."recipientId" AND trs."timestamp"= maxresults.max_timestamp; """.format(transactions=self.scheme['transactions'], address=address, blocks=self.scheme['blocks'])) labelset = [ 'recipient_id', 'timestamp', 'height', 'tx_id', 'amount', 'fee' ] return dictionify(resultset, labelset)
def all_votes_by_address(self, address): resultset = self._cursor.execute_and_fetchall(""" SELECT trs."{transactions[timestamp]}", votes."{votes[votes]}", mem."{mem_accounts[username]}", mem."{mem_accounts[address]}", ENCODE(mem."{mem_accounts[public_key]}"::BYTEA, 'hex'), blocks."{blocks[height]}" FROM {transactions[table]} AS trs INNER JOIN {blocks[table]} blocks ON (blocks."{blocks[id]}" = trs."{transactions[block_id]}") INNER JOIN {votes[table]} AS votes ON (trs."{transactions[id]}" = votes."{votes[transaction_id]}") INNER JOIN {mem_accounts[table]} mem ON (TRIM(LEADING '+-' FROM votes."{votes[votes]}") = ENCODE(mem."{mem_accounts[public_key]}"::BYTEA, 'hex')) WHERE trs."{transactions[sender_id]}" = '{address}' ORDER BY blocks."{blocks[height]}" ASC; """.format(transactions=self.scheme['transactions'], votes=self.scheme['votes'], mem_accounts=self.scheme['mem_accounts'], address=address, blocks=self.scheme['blocks'])) labelset = [ 'timestamp', 'vote', 'username', 'address', 'public_key', 'height' ] return dictionify(resultset, labelset)
def current_delegates(self): resultset = self._cursor.execute_and_fetchall(""" SELECT mem."{mem_accounts[username]}", mem."{mem_accounts[is_delegate]}", mem."{mem_accounts[second_signature]}", mem."{mem_accounts[address]}", ENCODE(mem."{mem_accounts[public_key]}"::BYTEA, 'hex'), ENCODE(mem."{mem_accounts[second_public_key]}"::BYTEA, 'hex'), mem."{mem_accounts[balance]}", mem."{mem_accounts[vote]}", mem."{mem_accounts[rate]}", mem."{mem_accounts[multi_signatures]}" FROM {mem_accounts[table]} AS mem WHERE mem."{mem_accounts[is_delegate]}" = 1 ORDER BY mem."{mem_accounts[vote]}" DESC LIMIT {num_delegates} """.format(mem_accounts=self.scheme['mem_accounts'], num_delegates=self.num_delegates)) labelset = [ 'username', 'is_delegate', 'second_signature', 'address', 'public_key', 'second_public_key', 'balance', 'vote', 'rate', 'multisignatures', ] return dictionify(resultset, labelset)
def node_height_details(self): resultset = self._cursor.execute_and_fetchone(""" SELECT blocks."{blocks[id]}", blocks."{blocks[timestamp]}", blocks."{blocks[height]}", ENCODE(blocks."{blocks[generator_public_key]}"::BYTEA, 'hex') FROM {blocks[table]} AS blocks ORDER BY blocks."{blocks[height]}" DESC LIMIT 1; """.format(blocks=self.scheme['blocks'])) labelset = ['block_id', 'timestamp', 'height', 'generator_public_key'] return dictionify(resultset, labelset, single=True)
def account_details(self, address): resultset = self._cursor.execute_and_fetchone(""" SELECT mem."{mem_accounts[address]}", mem."{mem_accounts[username]}", mem."{mem_accounts[is_delegate]}", mem."{mem_accounts[second_signature]}", ENCODE(mem."{mem_accounts[public_key]}"::BYTEA, 'hex'), ENCODE(mem."{mem_accounts[second_public_key]}"::BYTEA, 'hex'), mem."{mem_accounts[balance]}", mem."{mem_accounts[vote]}", mem."{mem_accounts[rate]}", mem."{mem_accounts[multi_signatures]}" FROM {mem_accounts[table]} as mem WHERE mem."{mem_accounts[address]}" = '{address}'; """.format(mem_accounts=self.scheme['mem_accounts'], address=address)) labelset = [ 'address', 'username', 'is_delegate', 'second_signature', 'public_key', 'second_public_key', 'balance', 'vote', 'rate', 'multisignatures' ] return dictionify( resultset, labelset, single=True, )
def get_current_voters(self, address): resultset = self._cursor.execute_and_fetchall(""" SELECT trs."{transactions[sender_id]}", trs."{transactions[timestamp]}", trs."{transactions[id]}", blocks."{blocks[height]}" AS block, trs."{transactions[id]}" FROM {transactions[table]} AS trs INNER JOIN {blocks[table]} blocks ON (blocks."{blocks[id]}" = trs."{transactions[block_id]}") WHERE trs."{transactions[type]}" = 3 AND trs."{transactions[sender_id]}" IN ( SELECT mem."{mem_accounts2delegates[account_id]}" FROM {mem_accounts2delegates[table]} AS mem WHERE mem."{mem_accounts2delegates[dependent_id]}" = ( SELECT ENCODE(mem2."{mem_accounts[public_key]}"::BYTEA, 'hex') FROM {mem_accounts[table]} AS mem2 WHERE mem2."{mem_accounts[address]}" = '{address}' )) ORDER BY block DESC """.format( transactions=self.scheme['transactions'], blocks=self.scheme['blocks'], mem_accounts2delegates=self.scheme['mem_accounts2delegates'], mem_accounts=self.scheme['mem_accounts'], address=address)) labelset = ['address', 'timestamp', 'id', 'height'] return dictionify(resultset, labelset)
def get_blocks(self, delegate_address): resultset = self._cursor.execute_and_fetchall(""" SELECT blocks."{blocks[height]}", blocks."{blocks[timestamp]}", blocks."{blocks[id]}", blocks."{blocks[total_fee]}", blocks."{blocks[reward]}" FROM {blocks[table]} AS blocks WHERE blocks."{blocks[generator_public_key]}" = ( SELECT mem."{mem_accounts[public_key]}" FROM {mem_accounts[table]} AS mem WHERE mem."{mem_accounts[address]}" = '{address}') ORDER BY blocks."{blocks[height]}" ASC """.format(blocks=self.scheme['blocks'], mem_accounts=self.scheme['mem_accounts'], address=delegate_address)) labelset = ['height', 'timestamp', 'id', 'total_fee', 'reward'] return dictionify(resultset, labelset)