Topic: Parse recent transactions with bitcoin core in pruned mode (Read 476 times)

# Get hashes of transactions in the mempool
#
sql="START TRANSACTION;"
Tx=$(bitcoin-cli -rpccookiefile=$cookie getrawmempool | jq -r .[])
sql=${sql}$(echo "$Tx" | sed -e "s/^/INSERT IGNORE INTO exp_mempool(hash) VALUES('/" | sed -e "s/$/');/");
sql="${sql}COMMIT;"

mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_password" "$mysql_db" -BNs <<-EOF
  $sql
EOF

# Cleanup
#
mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_password" "$mysql_db" -BNs <<-EOF
  DELETE FROM exp_mempool WHERE tstamp < NOW() - INTERVAL 3 HOUR;
  DELETE FROM exp_mempool_tx WHERE tstamp < NOW() - INTERVAL 3 HOUR;
EOF

# Select only unprocessed transactions
#
Tx=$(mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_password" "$mysql_db" -BNs <<-EOF
  SELECT hash FROM exp_mempool WHERE is_processed = FALSE ORDER BY tstamp ASC LIMIT 500;
EOF
)

# Process mempool transactions (populate exp_mempool_tx table)
#
_header "$(date --rfc-3339=seconds)"
sql="START TRANSACTION;"
IFS=$'\n'
aTx=($(echo "$Tx"))
nTx=$(echo "$Tx" | wc -l)
echo "transactions:$nTx"
if (( $nTx == 0 )); then
  exit 2
fi

tsstart=$(date +%s%N | cut -b1-13)
i=0
for txhash in $Tx; do
  if (( $(($i%100==42)) )); then
    tsnow=$(date +%s%N | cut -b1-13)
    opsec=$(echo "scale=2;$i/(($tsnow-$tsstart)/1000)" | bc)
    p=$(echo "$i*100/$nTx" | bc)
    printf "$p%% ($opsec tx/sec)\r"
  fi

set +e
  txjson=$(bitcoin-cli -rpccookiefile=$cookie getrawtransaction $txhash 1 $bhash 2>/dev/null)
  if (( $? != 0 )); then
set -e
    # Gone from the mempool already?
    #
    #echo "Deleting tx:$txhash"
    sql=${sql}"DELETE FROM exp_mempool WHERE hash = '$txhash' LIMIT 1;"$'\n'
    i=$((i+1))
    continue
  fi
set -e

  # Make sure the transaction doesn't support RBF
  #
  rbf=$(echo "$txjson" | jq -r '.vin[] | select(.sequence < 4294967294)' | wc -l)
  if (( $rbf > 0 )); then
    #echo "$txjson" > /tmp/tx/$txhash
    sql="${sql}UPDATE exp_mempool SET is_processed = TRUE WHERE hash = '$txhash' LIMIT 1;"$'\n'
    i=$((i+1))
    continue
  fi

  r=$(echo "$txjson" | jq -r '. as $tx | .vout[] | select(.scriptPubKey.type == "pubkeyhash") | $tx.txid, .value, .scriptPubKey.addresses[]' | grep . || true )
  if [ ! -z "$r" ]; then
    r=$(echo "$r" | awk 'NR%3==2 {printf("%d\n", sprintf("%.08f", $0*100000000)); next} {print $0}' | awk '{printf "'\''%s'\''," (NR%3==0?RS:FS),$1}')
    sql=${sql}$(echo "$r" | sed -e "s/^/INSERT INTO exp_mempool_tx(hash, amount, address_to) VALUES(/" | sed -e 's/,$/);/')
  fi
  sql="${sql}UPDATE exp_mempool SET is_processed = TRUE WHERE hash = '$txhash' LIMIT 1;"$'\n'
  i=$((i+1))
done
tsnow=$(date +%s%N | cut -b1-13)
opsec=$(echo "scale=2;$i/(($tsnow-$tsstart)/1000)" | bc)
p=100
printf "$p%% ($opsec tx/sec)\r"
echo
sql="${sql}COMMIT;"

#!/usr/bin/env bash
set -e
set -o errexit

#if tty -s; then
export TERM=screen-256color
_bold=$(tput bold)
_underline=$(tput sgr 0 1)
_reset=$(tput sgr0)
_purple=$(tput setaf 171)
_red=$(tput setaf 1)
_green=$(tput setaf 82)
_tan=$(tput setaf 3)
_blue=$(tput setaf 39)
#fi
function _success() { printf "${_green}[ok]${_reset}: %s\n" "$@"; }
function _error() { printf "${_red}[error]${_reset}: %s\n" "$@"; }
function _warning() { printf "${_tan}[warning]${_reset}: %s\n" "$@"; }
function _info() { printf "${_blue}[info]${_reset}: %s\n" "$@" ; }
function _header() { printf '\n%s%s==========  %s  ==========%s\n' "$_bold" "$_purple" "$@" "$_reset" ; }

DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd)"
cd "$DIR"

# Make sure that only one copy of the script is currently running
#
src=$(realpath ${BASH_SOURCE[0]})
filename=${src##*/}
ps=$(o=""; for p in $(pgrep -f "$filename"); do o="${o}$(pstree -lp | grep -vF \($$\) | grep -nF \($p\))\n"; done; echo -e "$o" | cut -f1 -d: | sort -n | grep -vE ^$ | uniq -c | awk '$1>1' | wc -l)
if (( $ps > 0 )); then
  _warning "$(date --rfc-3339=seconds) :: $ps extra processes are running; exiting now"
  #for p in $(pgrep -f "$filename"); do pstree -lp | grep -vF \($$\) | grep -nF \($p\); done;
        exit 1
fi

file="db.config.xml"
mysql_host=$(xmlstarlet sel -t -v config/host "$file")
mysql_db=$(xmlstarlet sel -t -v config/db "$file")
mysql_user=$(xmlstarlet sel -t -v config/user "$file")
mysql_password=$(xmlstarlet sel -t -v config/password "$file")

cookie=~/.bitcoin/.cookie

function process_block() {
  _header "$(date --rfc-3339=seconds)"
  bhash=${1:-$(bitcoin-cli -rpccookiefile=$cookie getbestblockhash)}
  block=$(bitcoin-cli -rpccookiefile=$cookie getblock $bhash)
  pbhash=$(echo "$block" | jq -r .previousblockhash)
  height=$(echo "$block" | jq -r .height)
  echo "height:$height"
  nTx=$(echo "$block" | jq -r .nTx)
  echo "transactions:$nTx"
  mediantime=$(date -d @$(echo "$block" | jq -r .mediantime) --rfc-3339=seconds)
  echo "mediantime:$mediantime"


  # Make sure not to process unrelevant history
  #
  r=$(mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_password" "$mysql_db" -BNs <<-EOF
  SELECT MIN(height) FROM explorer_poc_blocks_processed;
EOF
)
  if (( $height < $r )); then
    exit 1
  fi

  # Make sure such block wasn't already processed
  #
  r=$(mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_password" "$mysql_db" -BNs <<-EOF
  SELECT COUNT(*) FROM explorer_poc_blocks_processed WHERE height = '$height';
EOF
)
  if (( $r == 0 )); then

  block=$(bitcoin-cli -rpccookiefile=$cookie getblock $bhash 2)
  Tx=$(echo "$block" | jq -r .tx[])
  Tx=$(echo "$Tx" | jq . | jq . -c)

  sql="START TRANSACTION;"
  IFS=$'\n'
  aTx=($(echo "$Tx"))
  tsstart=$(date +%s%N | cut -b1-13)
  #i=0
  #for txhash in $Tx; do
  for (( i=0;i    if (( $(($i%100==42)) )); then
      p=$(echo "$i*100/$nTx" | bc)
      tsnow=$(date +%s%N | cut -b1-13)
      opsec=$(echo "scale=2;$i/(($tsnow-$tsstart)/1000)" | bc)
      printf "$p%% ($opsec tx/sec)\r"
    fi

    #txjson=$(bitcoin-cli -rpccookiefile=$cookie getrawtransaction $txhash 1 $bhash)
    txjson="${aTx[$i]}"

    r=$(echo "$txjson" | jq -r '. as $tx | .vout[] | select(.scriptPubKey.type == "pubkeyhash") | $tx.txid, .value, .scriptPubKey.addresses[]' | grep . || true )
    if [ -z "$r" ]; then continue; fi
    r=$(echo "$r" | awk 'NR%3==2 {printf("%d\n", sprintf("%.08f", $0*100000000)); next} {print $0}' | awk '{printf "'\''%s'\''," (NR%3==0?RS:FS),$1}')
    sql=${sql}$(echo "$r" | sed -e "s/^/INSERT INTO explorer_poc_transactions(block_height, hash, amount, address_to) VALUES('$height',/" | sed -e 's/,$/);/')
    #i=$((i+1))
  done
  echo
  sql="${sql}INSERT INTO explorer_poc_blocks_processed(height) VALUES('$height');"
  sql="${sql}COMMIT;"
  mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_password" "$mysql_db" -BNs <<-EOF
  $sql
EOF

  # Remove the transactions to the addresses that are not found in the 'addresses_to_check' table
  #
  mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_password" "$mysql_db" -BNs <<-EOF
  DELETE explorer_poc_transactions FROM explorer_poc_transactions LEFT JOIN addresses_to_check ON explorer_poc_transactions.address_to = addresses_to_check.address WHERE addresses_to_check.address IS NULL;
EOF

  # Update the 'addresses_to_check'.balance by the information received from the parsed block
  #
  # Get the list of addresses that were updated
  #
  r=$(mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_password" "$mysql_db" -BNs <<-EOF
  SELECT DISTINCT address_to FROM explorer_poc_transactions, addresses_to_check WHERE address_to = addresses_to_check.address AND block_height = '$height';
EOF
)
  for a in $r; do
    _success "  -> address:$a"
    # Get the sum of the amounts of all transaction to the address
    #
    b=$(mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_password" "$mysql_db" -BNs <<-EOF
    SELECT SUM(amount) FROM explorer_poc_transactions WHERE address_to = '$a';
EOF
)
    _success "  -> amount:$b"
    # Update the 'addresses_to_check'.balance with the calculated amount
    #

    mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_password" "$mysql_db" -BNs <<-EOF
                UPDATE addresses_to_check SET last_changes = NOW(), balance = '$b' WHERE address = '$a' AND balance <> '$b';
EOF
  done

  else
    echo "Skipping..."
    # No point of going any further?
    #
    r=$(mysql -h"$mysql_host" -u"$mysql_user" -p"$mysql_password" "$mysql_db" -BNs <<-EOF
    SELECT MAX(height)-MIN(height)=COUNT(*)-1 FROM explorer_poc_blocks_processed;
EOF
)
    if (( $r == 1 )); then
      echo "No point of going any further."
      exit 2
    fi
  fi
  process_block $pbhash
}

process_block ""

#!/usr/bin/env bash
set -e
set -o errexit

function process_block() {
  date --rfc-3339=ns
  bhash=${1:-$(bitcoin-cli getbestblockhash)}
  block=$(bitcoin-cli getblock $bhash)
  pbhash=$(echo "$block" | jq -r .previousblockhash)
  height=$(echo "$block" | jq -r .height)
  echo "height: $height"
  nTx=$(echo "$block" | jq -r .nTx)
  echo "$nTx transactions"
  Tx=$(echo "$block" | jq -r .tx[])
  IFS=$'\n'
  for txhash in $Tx; do
    txjson=$(bitcoin-cli getrawtransaction $txhash 1 $bhash)
    addresses=$(echo "$txjson" | jq -r '.vout[].scriptPubKey | select(.type == "pubkeyhash") | .addresses | .[]')
    for a in $addresses; do
      if [[ $a == ${2} ]]; then
        echo "$txjson"
      fi
    done
  done
  process_block $pbhash ${2}
}

process_block "" ${1}