• Forum has been upgraded, all links, images, etc are as they were. Please see Official Announcements for more information

Abe MySQL Queries

calnaughtonjnr

calnaughtonjnr

Active member
Foundation Member
Can anyone give me some pointers on using MySQL queries to get data out of Abe please? I've been doing some serious googling and also been looking through Datastore.py, but really struggling.

I'd like to get a list of transactions received for an address and would also like to get the data in the html table on these pages.

http://explorer.darkcoin.io/address/XjDgtN9RTQF6WAmbWC5xGZ94NNzmVEav9R

Would really appreciate any help!
 
calnaughtonjnr said:
Thanks Udjin! What should I be passing as the chain_id and block_height?
Click to expand...
Not sure, block_height is max block count you want to count txes to I guess so you can just skip "cc.block_height <= " part
And about chain_id ... maybe skip it and see what's available? or maybe flare or crowning can help? :wink:
 
Tried this

Code: 
            SELECT COALESCE(value_sum, 0), c.chain_last_block_id
              FROM chain c LEFT JOIN (
              SELECT cc.chain_id, SUM(txout.txout_value) value_sum
              FROM pubkey
              JOIN txout              ON (txout.pubkey_id = pubkey.pubkey_id)
              JOIN block_tx           ON (block_tx.tx_id = txout.tx_id)
              JOIN block b            ON (b.block_id = block_tx.block_id)
              JOIN chain_candidate cc ON (cc.block_id = b.block_id)
              WHERE
                  pubkey.pubkey_hash = lower('5D966EDC803DC19BE39D83D5D0F8AC02E3B3F58F') AND
                  -- cc.chain_id = '%' AND
                  cc.in_longest = 1 AND
                  cc.block_height <= 999999999
              GROUP BY cc.chain_id
              ) a ON (c.chain_id = a.chain_id)
              -- WHERE c.chain_id = ?

And got this

Code: 
COALESCE(value_sum, 0)           c.chain_last_block_id
0  
0  
0  
0  
0  
0  
0  
0  
0  
0  
0  
0  
0  
0  
0                                 225377


:confused:
 
calnaughtonjnr said:
Tried this

Code: 
            SELECT COALESCE(value_sum, 0), c.chain_last_block_id
              FROM chain c LEFT JOIN (
              SELECT cc.chain_id, SUM(txout.txout_value) value_sum
              FROM pubkey
              JOIN txout              ON (txout.pubkey_id = pubkey.pubkey_id)
              JOIN block_tx           ON (block_tx.tx_id = txout.tx_id)
              JOIN block b            ON (b.block_id = block_tx.block_id)
              JOIN chain_candidate cc ON (cc.block_id = b.block_id)
              WHERE
                  pubkey.pubkey_hash = lower('5D966EDC803DC19BE39D83D5D0F8AC02E3B3F58F') AND
                  -- cc.chain_id = '%' AND
                  cc.in_longest = 1 AND
                  cc.block_height <= 999999999
              GROUP BY cc.chain_id
              ) a ON (c.chain_id = a.chain_id)
              -- WHERE c.chain_id = ?

And got this

Code: 
COALESCE(value_sum, 0)           c.chain_last_block_id
0 
0 
0 
0 
0 
0 
0 
0 
0 
0 
0 
0 
0 
0 
0                                 225377


:confused:
Click to expand...
ooops, this means that I was wrong:oops:
that was the part for received part of balance, not for tx listing
how about that one https://github.com/darkcoin/darkcoin-abe/blob/master/Abe/abe.py#L1026-L1048 ?
 
That looks like what I need!.

I tried this

Code: 
 SELECT
                b.block_nTime,
                cc.chain_id,
                b.block_height,
                1,
                b.block_hash,
                tx.tx_hash,
                txin.txin_pos,
                -prevout.txout_value,
                pubkey.pubkey_hash
              FROM chain_candidate cc
              JOIN block b ON (b.block_id = cc.block_id)
              JOIN block_tx ON (block_tx.block_id = b.block_id)
              JOIN tx ON (tx.tx_id = block_tx.tx_id)
              JOIN txin ON (txin.tx_id = tx.tx_id)
              JOIN txout prevout ON (txin.txout_id = prevout.txout_id)
              JOIN pubkey ON (pubkey.pubkey_id = prevout.pubkey_id)
             WHERE pubkey.pubkey_hash = '5D966EDC803DC19BE39D83D5D0F8AC02E3B3F58F'

But got no results. If I take out the WHERE clause, I get results, but all the pubkey.pubkey_hash data comes back as BLOB. Do I need to do any conversion on the '5D966EDC803DC19BE39D83D5D0F8AC02E3B3F58F', before I pass it?
 
calnaughtonjnr said:
That looks like what I need!.

I tried this

Code: 
 SELECT
                b.block_nTime,
                cc.chain_id,
                b.block_height,
                1,
                b.block_hash,
                tx.tx_hash,
                txin.txin_pos,
                -prevout.txout_value,
                pubkey.pubkey_hash
              FROM chain_candidate cc
              JOIN block b ON (b.block_id = cc.block_id)
              JOIN block_tx ON (block_tx.block_id = b.block_id)
              JOIN tx ON (tx.tx_id = block_tx.tx_id)
              JOIN txin ON (txin.tx_id = tx.tx_id)
              JOIN txout prevout ON (txin.txout_id = prevout.txout_id)
              JOIN pubkey ON (pubkey.pubkey_id = prevout.pubkey_id)
             WHERE pubkey.pubkey_hash = '5D966EDC803DC19BE39D83D5D0F8AC02E3B3F58F'

But got no results. If I take out the WHERE clause, I get results, but all the pubkey.pubkey_hash data comes back as BLOB. Do I need to do any conversion on the '5D966EDC803DC19BE39D83D5D0F8AC02E3B3F58F', before I pass it?
Click to expand...
hmm, you're right again https://github.com/darkcoin/darkcoin-abe/blob/master/Abe/abe.py#L999
and it comes from https://github.com/darkcoin/darkcoin-abe/blob/master/Abe/abe.py#L994 but addresstohash gives you hex encoded version https://github.com/darkcoin/darkcoin-abe/blob/master/Abe/abe.py#L1619
 
calnaughtonjnr said:
OK, I'm a bit lost now. Does this mean that I have to pass the wallet address through a function before using it in the SELECT query?
Click to expand...
Well, I guess you need to run smth like .decode('hex') on "5D966EDC803DC19BE39D83D5D0F8AC02E3B3F58F"...

EDIT: cmon flare , show up here, I'm not a python expert :rolleyes:
 
Last edited by a moderator:
You must log in or register to reply here.
Back
Top