Topic: Parse recent transactions with bitcoin core in pruned mode (Read 468 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;"