Data2Sql

import org.ethereum.core.Block;
import org.ethereum.core.Genesis;
import org.ethereum.core.Transaction;
import org.ethereum.core.TransactionReceipt;
import org.ethereum.crypto.ECKey;
import org.ethereum.crypto.HashUtil;
import org.ethereum.facade.Ethereum;
import org.ethereum.facade.EthereumFactory;
import org.ethereum.listener.EthereumListenerAdapter;
import org.ethereum.net.p2p.HelloMessage;
import org.ethereum.util.ByteUtil;
import org.spongycastle.util.BigIntegers;
import org.spongycastle.util.encoders.Hex;

import javax.swing.*;
import java.io.*;
import java.math.BigInteger;
import java.util.*;
import java.util.Timer;

import static org.ethereum.config.SystemProperties.CONFIG;

/**
 *
 * @author: Roman Mandeleil
 * Created on: 14/10/2014 14:39
 */
public class Main6 extends EthereumListenerAdapter{

    static Ethereum ethereum;
    private Timer timer = new java.util.Timer("TransactingTimer");
    
    List<byte[]> transactions = new ArrayList<>();
    private boolean sendingTx = false;
 
    public static void main(String[] args) throws IOException {
        
        Main6 listener = new Main6();

        ethereum =  EthereumFactory.createEthereum();
        ethereum.addListener(listener);

        SwingUtilities.invokeLater(new Runnable() {
            public void run() {
                ethereum.connect(CONFIG.activePeerIP(),
                        CONFIG.activePeerPort());
            }
        });

    }

    @Override
    public void onHandShakePeer(HelloMessage helloMessage) {
        
        System.out.println("!!! handshake from: " +  helloMessage);
        parseBlockSql(Genesis.getInstance());
    }

    
    @Override
    public void onNoConnections() {
        System.out.println("I am blind (~) ");

        SwingUtilities.invokeLater(new Runnable() {
            public void run() {
                ethereum.connect(CONFIG.activePeerIP(),
                        CONFIG.activePeerPort());
            }
        });

    }

    @Override
    public void onBlock(Block block) {

        parseBlockSql(block); 
        parseTransactionsSql(block);

        if (block.getNumber() == 110) System.exit(-1);

    }

    private void parseBlockSql(Block block) {

        String sql = String.format("INSERT INTO block (\n  number,\n  hash,\n  uncle_hash,\n  nonce,\n  state_root,\n" +
                        "  tx_trie_root,\n  time_stamp,\n  gas_limit,\n  gas_used,\n  miner,\n  difficulty,\n" +
                        "  extra_data,\n  bloom_filter,\n  rlp\n)\nVALUES  (\n" +
                        "  %d,\n  '%s',\n  '%s',\n  '%s',\n  '%s',\n  '%s',\n  %d,\n  %d,\n  %d,\n  '%s',\n  '%s',\n  '%s',\n  '%s',\n  '%s'\n);",
                block.getNumber(),
                Hex.toHexString(block.getHash()),
                Hex.toHexString(block.getUnclesHash()),
                Hex.toHexString(block.getNonce()),
                Hex.toHexString(block.getStateRoot()),
                Hex.toHexString(block.getTxTrieRoot()),
                block.getTimestamp(),
                block.getGasLimit(),
                block.getGasUsed(),
                Hex.toHexString(block.getCoinbase()),
                Hex.toHexString(block.getDifficulty()),
                "NULL",
                Hex.toHexString(block.getLogBloom()),
                Hex.toHexString(block.getEncoded()));
        
        System.out.println(sql);
        
        
    }

    public void parseTransactionsSql(Block block){
        if (block.getTransactionsList().isEmpty()) return ;
        
        
        for (Transaction tx : block.getTransactionsList()){

            boolean invoke = false;
            if (tx.isContractCreation() )
                invoke = false;
            else
                invoke = ethereum.getRepository().getContractDetails(tx.getReceiveAddress()).getCode().length > 0;

            String txType = tx.isContractCreation() ? "1" : (invoke ? "2" : "0");
            String txHash = Hex.toHexString(tx.getHash());
            String  value = ByteUtil.toHexString(tx.getValue());
            long   gasPrice = ByteUtil.byteArrayToLong(tx.getGasPrice());
            long   gasLimit = ByteUtil.byteArrayToLong( tx.getGasLimit() );

            String fromAddress = Hex.toHexString( tx.getSender());
            String toAddress   =  tx.getReceiveAddress() == null ? "" :  Hex.toHexString( tx.getReceiveAddress());
            String contractAddress = ByteUtil.toHexString(tx.getContractAddress());

            long   nonce = ByteUtil.byteArrayToLong( tx.getNonce() );

            String data = ByteUtil.toHexString(tx.getData());

            String blockHash = Hex.toHexString( block.getHash() );
            String rlp = Hex.toHexString(tx.getEncoded());

            String sql = String.format("INSERT INTO transaction (\n" +
                            "  block_hash,\n  type,\n  hash,\n  value,\n  gas_price,\n  gas_limit,\n" +
                            "  from_address,\n  to_address,\n  contract_address,\n  nonce,\n  data,\n  rlp\n)\n" +
                            "VALUES (\n" +
                            "  '%s',\n  '%s',\n  '%s',\n  '%s',\n  %d,\n  %d,\n  '%s',\n  '%s',\n  '%s',\n  %d,\n  '%s',\n  '%s'\n);",
                    blockHash,
                    txType,
                    txHash,
                    value,
                    gasPrice,
                    gasLimit,
                    fromAddress,
                    toAddress,
                    contractAddress,
                    nonce,
                    data ,
                    rlp);


            System.out.println();
            System.out.println(sql);
            System.out.println();


            parseTxReceiptSql(tx);
        }

    }



    public void parseTxReceiptSql(Transaction transaction){

        String txHash = Hex.toHexString(transaction.getHash());
        
        TransactionReceipt tr = 
        ethereum.getBlockchain().getTransactionReceiptByHash(transaction.getHash());

        String postTxState = Hex.toHexString( tr.getPostTxState() );
        long cumulativeGas = ByteUtil.byteArrayToLong(tr.getCumulativeGas());
        String bloomFilter = Hex.toHexString(tr.getBloomFilter().getData());
        String rlp =  Hex.toHexString(tr.getEncoded());
        
        String sql = String.format("INSERT INTO receipt (\n  transaction_hash,\n  post_state,\n  cumulative_gas,\n  bloom_filter,\n  rlp\n)\n " +
                           "VALUES(\n  '%s',\n  '%s',\n  %d,\n  '%s',\n  '%s'\n);",
                            txHash,
                            postTxState,
                            cumulativeGas,
                            bloomFilter, 
                            rlp);
        
        System.out.println(sql);

    }
    

}