def _get_balance(self, ledger_db, address, minconf=1): """ Queries the db to get the balance of a single address :param address: :param minconf: :return: """ try: dbhandler.execute(self.app_log, ledger_db, "SELECT MAX(block_height) FROM transactions") # what is the max block height to consider ? max_block_height = ledger_db.fetchone()[0] - minconf # calc balance up to this block_height dbhandler.execute_param( self.app_log, ledger_db, "SELECT sum(amount)+sum(reward) FROM transactions WHERE recipient = ? and block_height <= ?;", (address, max_block_height)) credit = ledger_db.fetchone()[0] if not credit: credit = 0 # debits + fee - reward dbhandler.execute_param( self.app_log, ledger_db, "SELECT sum(amount)+sum(fee) FROM transactions WHERE address = ? and block_height <= ?;", (address, max_block_height)) debit = ledger_db.fetchone()[0] if not debit: debit = 0 # keep as float #balance = '{:.8f}'.format(credit - debit) balance = credit - debit except Exception as e: print(e) raise return balance
def _get_received(self, ledger_db, address, minconf=1): """ Queries the db to get the total received amount of a single address :param address: :param minconf: :return: """ try: # TODO : for this one and _get_balance, request max block height out of the loop and pass it as a param to alleviate db load dbhandler.execute(self.app_log, ledger_db, "SELECT MAX(block_height) FROM transactions") # what is the max block height to consider ? max_block_height = ledger_db.fetchone()[0] - minconf # calc received up to this block_height dbhandler.execute_param( self.app_log, ledger_db, "SELECT sum(amount) FROM transactions WHERE recipient = ? and block_height <= ?;", (address, max_block_height)) credit = ledger_db.fetchone()[0] if not credit: credit = 0 except Exception as e: print(e) raise return credit
def api_gettransaction(self, socket_handler, ledger_db, peers): """ returns total balance for a list of addresses and minconf BEWARE: this is NOT the json rpc getbalance (that get balance for an account, not an address) :param socket_handler: :param ledger_db: :param peers: :return: """ transaction = {} try: # get the txid transaction_id = connections.receive(socket_handler) # and format format = connections.receive(socket_handler) # raw tx details dbhandler.execute_param( self.app_log, ledger_db, "SELECT * FROM transactions WHERE signature like ?", (transaction_id + '%', )) raw = ledger_db.fetchone() if not format: connections.send(socket_handler, raw) print('api_gettransaction', format, raw) return # current block height, needed for confirmations # dbhandler.execute(self.app_log, ledger_db, "SELECT MAX(block_height) FROM transactions") block_height = ledger_db.fetchone()[0] transaction['txid'] = transaction_id transaction['time'] = raw[1] transaction['hash'] = raw[5] transaction['address'] = raw[2] transaction['recipient'] = raw[3] transaction['amount'] = raw[4] transaction['fee'] = raw[8] transaction['reward'] = raw[9] transaction['keep'] = raw[10] transaction['openfield'] = raw[11] transaction['pubkey'] = base64.b64decode(raw[6]).decode('utf-8') transaction['blockhash'] = raw[7] transaction['blockheight'] = raw[0] transaction['confirmations'] = block_height - raw[0] # Get more info on the block the tx is in. dbhandler.execute_param( self.app_log, ledger_db, "SELECT timestamp, recipient FROM transactions WHERE block_height= ? AND reward > 0", (raw[0], )) block_data = ledger_db.fetchone() transaction['blocktime'] = block_data[0] transaction['blockminer'] = block_data[1] print('api_gettransaction', format, transaction) connections.send(socket_handler, transaction) except Exception as e: raise
def api_getblocksincewhere(self, socket_handler, ledger_db, peers): """ Returns the full transactions following a given block_height and with specific conditions Returns at most transactions from 720 blocks at a time (the most *older* ones if it truncates) so about 12 hours worth of data. Maybe huge, use with caution and restrictive queries only. :param socket_handler: :param ledger_db: :param peers: :return: """ info = [] # get the last known block since_height = connections.receive(socket_handler) where_conditions = connections.receive(socket_handler) print('api_getblocksincewhere', since_height, where_conditions) # TODO: feed as array to have a real control and avoid sql injection !important # Do *NOT* use in production until it's done. raise ValueError("Unsafe, do not use yet") """ [ ['','openfield','like','egg%'] ] [ ['', '('], ['','reward','>','0'] ['and','recipient','in',['','','']] ['', ')'], ] """ where_assembled = where_conditions conditions_assembled = () try: try: dbhandler.execute( self.app_log, ledger_db, "SELECT MAX(block_height) FROM transactions") # what is the max block height to consider ? block_height = min(ledger_db.fetchone()[0], since_height + 720) #print("block_height",block_height) dbhandler.execute_param(self.app_log, ledger_db, ( 'SELECT * FROM transactions WHERE block_height > ? and block_height <= ? and ( ' + where_assembled + ')'), (since_height, block_height) + conditions_assembled) info = ledger_db.fetchall() # it's a list of tuples, send as is. #print(all) except Exception as e: print(e) raise # print("info", info) connections.send(socket_handler, info) except Exception as e: print(e) raise
def testCreateUser(self): rs=dbhandler.execute(("SELECT * FROM users WHERE uname='shlomi'",)) assert len(rs)==0, "User is in DB before action" cua=usersaction.CreateUser('shlomi') cua.execute() rs=dbhandler.execute(("SELECT * FROM users WHERE uname='shlomi'",)) assert len(rs)>0, "Creation failed" result=rs[0] assert type(result[0]) is long, "uid is not integer. value: %s, type:%s" % result[0] assert result[1]=='shlomi', "uname is wrong. value: %s" % result[1]
def api_getblockswhereoflike(self, socket_handler, ledger_db, peers): """ Returns the full transactions following a given block_height and with openfield begining by the given string Returns at most transactions from 1440 blocks at a time (the most *older* ones if it truncates) so about 1 day worth of data. Maybe huge, use with caution and on restrictive queries only. :param socket_handler: :param ledger_db: :param peers: :return: """ info = [] # get the last known block since_height = int(connections.receive(socket_handler)) where_openfield_like = connections.receive(socket_handler) + '%' #print('api_getblockswhereoflike', since_height, where_openfield_like) try: try: dbhandler.execute( self.app_log, ledger_db, "SELECT MAX(block_height) FROM transactions") # what is the max block height to consider ? block_height = min(ledger_db.fetchone()[0], since_height + 1440) #print("block_height", since_height, block_height) dbhandler.execute_param( self.app_log, ledger_db, 'SELECT * FROM transactions WHERE block_height > ? and block_height <= ? and openfield like ?', (since_height, block_height, where_openfield_like)) info = ledger_db.fetchall() # it's a list of tuples, send as is. #print("info", info) except Exception as e: print("error", e) raise # Add the last fetched block so the client will be able to fetch the next block info.append([block_height]) connections.send(socket_handler, info) except Exception as e: print(e) exc_type, exc_obj, exc_tb = sys.exc_info() fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1] print(exc_type, fname, exc_tb.tb_lineno) raise
def api_getaddresssince(self, socket_handler, ledger_db, peers): """ Returns the full transactions following a given block_height (will not include the given height) for the given address, with at least min_confirmations confirmations, as well as last considered block. Returns at most transactions from 720 blocks at a time (the most *older* ones if it truncates) so about 12 hours worth of data. :param socket_handler: :param ledger_db: :param peers: :return: """ info = [] # get the last known block since_height = int(connections.receive(socket_handler)) min_confirmations = int(connections.receive(socket_handler)) address = str(connections.receive(socket_handler)) print('api_getaddresssince', since_height, min_confirmations, address) try: try: dbhandler.execute( self.app_log, ledger_db, "SELECT MAX(block_height) FROM transactions") # what is the max block height to consider ? block_height = min(ledger_db.fetchone()[0] - min_confirmations, since_height + 720) dbhandler.execute_param(self.app_log, ledger_db, ( 'SELECT * FROM transactions WHERE block_height > ? AND block_height <= ? ' 'AND ((address = ?) OR (recipient = ?)) ORDER BY block_height ASC' ), (since_height, block_height, address, address)) info = ledger_db.fetchall() except Exception as e: print("Exception api_getaddresssince:".format(e)) raise connections.send( socket_handler, { 'last': block_height, 'minconf': min_confirmations, 'transactions': info }) except Exception as e: print(e) raise
def api_getblocksince(self, socket_handler, ledger_db, peers): """ Returns the full blocks and transactions following a given block_height Returns at most transactions from 10 blocks (the most recent ones if it truncates) Used by the json-rpc server to poll and be notified of tx and new blocks. :param socket_handler: :param ledger_db: :param peers: :return: """ info = [] # get the last known block since_height = connections.receive(socket_handler) #print('api_getblocksince', since_height) try: try: dbhandler.execute( self.app_log, ledger_db, "SELECT MAX(block_height) FROM transactions") # what is the min block height to consider ? block_height = max(ledger_db.fetchone()[0] - 11, since_height) #print("block_height",block_height) dbhandler.execute_param( self.app_log, ledger_db, ('SELECT * FROM transactions WHERE block_height > ?;'), (block_height, )) info = ledger_db.fetchall() # it's a list of tuples, send as is. #print(all) except Exception as e: print(e) raise # print("info", info) connections.send(socket_handler, info) except Exception as e: print(e) raise
def sqlitewrapper(*args, **kwargs): cmd=getSQLQuery(*args,**kwargs) rs=dbhandler.execute(cmd) return rs