[ANNOUNCE] Abe 0.7: Open Source Block Explorer Knockoff

[mcorlett]

I'm very interested in seeing Firstbits support.

[molecular]

I'm very interested in seeing Firstbits support.

what exactly do you have in mind?

an api call "/q/firstbits/15ArtC" that returns "15ArtCgi3wmpQAAfYx4riaFmo4prJA4VsK"?

[mcorlett]

I'm very interested in seeing Firstbits support.

what exactly do you have in mind?

an api call "/q/firstbits/15ArtC" that returns "15ArtCgi3wmpQAAfYx4riaFmo4prJA4VsK"?

Yes. I'd prefer some SQL code, but I know that Abe doesn't store addresses in base58 form for now, so that'd be difficult to implement. What you describe is adequate.

[Xenland]

Is there documentation on the table setup?

How do I query information on a particular address? (total received balance, transaction history,etc)

[John Tobey]

Is there documentation on the table setup?

How do I query information on a particular address? (total received balance, transaction history,etc)

No, sorry, no docs on the tables.  Have you searched this thread for the answer?

[Xenland]

Yeah I don't see how to query mysql for the balance of a Bitcoin address in this thread, I checked every single message. I noticed their is a nice API system but I'm already running a webserver with apache/php so I think it might be cleaner to just query the mysql data straight from php instead of having php call back to the server through python and then to mysql.

[John Tobey]

This seems to work.  Note that the database has no function to translate an address into a public key hash.  I assume you can do this in PHP.  The example uses address 12cbQLTFMXRnSzktFkuoG3eHoMeFtpTu3S, whose hash is 11b366edfc0a8b66feebae5c2e25a7b6a5d1cf31.  The hash appears in two places in the query.

Code:

SELECT SUM(value) / 100000000 AS balance FROM (
    SELECT -txout.txout_value AS value
      FROM pubkey
      JOIN txout ON txout.pubkey_id=pubkey.pubkey_id
      JOIN txin ON txin.txout_id=txout.txout_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('11B366EDFC0A8B66FEEBAE5C2E25A7B6A5D1CF31') AND
          cc.chain_id = 1 AND
          cc.in_longest = 1
    UNION ALL
    SELECT txout.txout_value AS value
      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('11B366EDFC0A8B66FEEBAE5C2E25A7B6A5D1CF31') AND
          cc.chain_id = 1 AND
          cc.in_longest = 1
) a;

[Xenland]

This seems to work.  Note that the database has no function to translate an address into a public key hash.  I assume you can do this in PHP.  The example uses address 12cbQLTFMXRnSzktFkuoG3eHoMeFtpTu3S, whose hash is 11b366edfc0a8b66feebae5c2e25a7b6a5d1cf31.  The hash appears in two places in the query.

Code:

SELECT SUM(value) / 100000000 AS balance FROM (
    SELECT -txout.txout_value AS value
      FROM pubkey
      JOIN txout ON txout.pubkey_id=pubkey.pubkey_id
      JOIN txin ON txin.txout_id=txout.txout_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('11B366EDFC0A8B66FEEBAE5C2E25A7B6A5D1CF31') AND
          cc.chain_id = 1 AND
          cc.in_longest = 1
    UNION ALL
    SELECT txout.txout_value AS value
      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('11B366EDFC0A8B66FEEBAE5C2E25A7B6A5D1CF31') AND
          cc.chain_id = 1 AND
          cc.in_longest = 1
) a;

Dude your awsome!, I would have never figured that query out on my own -- works great too

[John Tobey]

Dude your awsome!, I would have never figured that query out on my own -- works great too

Thanks for confirming.

[Xenland]

This seems to work.  Note that the database has no function to translate an address into a public key hash.  I assume you can do this in PHP.  The example uses address 12cbQLTFMXRnSzktFkuoG3eHoMeFtpTu3S, whose hash is 11b366edfc0a8b66feebae5c2e25a7b6a5d1cf31.  The hash appears in two places in the query.

Code:

SELECT SUM(value) / 100000000 AS balance FROM (
    SELECT -txout.txout_value AS value
      FROM pubkey
      JOIN txout ON txout.pubkey_id=pubkey.pubkey_id
      JOIN txin ON txin.txout_id=txout.txout_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('11B366EDFC0A8B66FEEBAE5C2E25A7B6A5D1CF31') AND
          cc.chain_id = 1 AND
          cc.in_longest = 1
    UNION ALL
    SELECT txout.txout_value AS value
      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('11B366EDFC0A8B66FEEBAE5C2E25A7B6A5D1CF31') AND
          cc.chain_id = 1 AND
          cc.in_longest = 1
) a;

Oh yeah I forgot to ask, what needs to be changed to this query to get the "total over all received" balance?

[John Tobey]

Oh yeah I forgot to ask, what needs to be changed to this query to get the "total over all received" balance?

Take out the first sub-select and simplify, leaving this:

Code:

    SELECT SUM(txout.txout_value) / 100000000
      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('11B366EDFC0A8B66FEEBAE5C2E25A7B6A5D1CF31') AND
          cc.chain_id = 1 AND
          cc.in_longest = 1

[Xenland]

Oh yeah I forgot to ask, what needs to be changed to this query to get the "total over all received" balance?

Take out the first sub-select and simplify, leaving this:

Code:

    SELECT SUM(txout.txout_value) / 100000000
      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('11B366EDFC0A8B66FEEBAE5C2E25A7B6A5D1CF31') AND
          cc.chain_id = 1 AND
          cc.in_longest = 1

Excellent worked again!

Is this donation address still valid?: 1PWC7PNHL1SgvZaN7xEtygenKjWobWsCuf

[John Tobey]

Is this donation address still valid?: 1PWC7PNHL1SgvZaN7xEtygenKjWobWsCuf

Yes, it is.  Thanks.

[Xenland]

For anyone who was curious. I ended up using bitcoin-php library to convert address to hash160 using the (almost obvious name) addressToHash160() function. It worked like a charm.

[Xenland]

Donation sent!


Hope everyone will appreciate Bitcoinchipin when its released this weekend

Hey wheres the "Powered by Bitcoin-Abe" logo at? 

[hamdi]

who is running ABE and at which url´s??

wanna see how it looks now, but can´t find a working node.

thanks

[Tittiez]

who is running ABE and at which url´s??

wanna see how it looks now, but can´t find a working node.

thanks

I have an Abe running, its in my sig. I edited the main page, but you still get the idea on what it looks like.

[Xenland]

what is wrong here? When i run the following SQL command on Bitcoin-abe database:

SELECT SUM(txout.txout_value) 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('04E116F6F1236ED1D0E40F03A20DE85E81D6C6DF') AND
          cc.chain_id = 1 AND
          cc.in_longest = 1

I get the value of: 1953408036 ­

So to convert that with the following formula 1953408036 ­/ 100000000 = 19.53408036 BTC

But when I look at the Blockchain it says there is a slightly higher total received balance: https://blockchain.info/address/1SoMGuYknDgyYypJPVVKE2teHBN4HDAh3

[John Tobey]

So to convert that with the following formula 1953408036 ­/ 100000000 = 19.53408036 BTC

But when I look at the Blockchain it says there is a slightly higher total received balance: https://blockchain.info/address/1SoMGuYknDgyYypJPVVKE2teHBN4HDAh3

Apparently 19.53408036 was the total received as of Block 180750 and until Block 180779 (8 days ago).  You would see that number if your database were not up to date.  You can check it through the browser interface or with SQL:

Code:

SELECT MAX(block_height) FROM chain_candidate
 WHERE chain_id = 1 AND in_longest = 1;

The output should be the current block number, 181904 as of right now.  Check bitcoind and Abe's log if you get a lower number.

[Xenland]

So to convert that with the following formula 1953408036 ­/ 100000000 = 19.53408036 BTC

But when I look at the Blockchain it says there is a slightly higher total received balance: https://blockchain.info/address/1SoMGuYknDgyYypJPVVKE2teHBN4HDAh3

Apparently 19.53408036 was the total received as of Block 180750 and until Block 180779 (8 days ago).  You would see that number if your database were not up to date.  You can check it through the browser interface or with SQL:

Code:

SELECT MAX(block_height) FROM chain_candidate
 WHERE chain_id = 1 AND in_longest = 1;

The output should be the current block number, 181904 as of right now.  Check bitcoind and Abe's log if you get a lower number.

Oh.... yep that was i the issue my database is only at block: 180769

Thanks mate!