EthBot: Ethereum blockchain to SQL converter

EthBot is the backend for AfterEther blockchain Explorer (AEX).

It will attach to the network and export incoming data (in real time) to a SQL database for later use by other applications. With EthBot you can run your own explorer services like the ones offered by Etherscan or Ethplorer.
EthBot was created for people who don't have time to learn Ethereum API but wants to extract required data from the blockchain quickly. Just install & run and get the data you want.

Status

In development, not ready for production yet.

Demo

Features

ToDo Features

New feature request

If you need some feature that is not currently present in EthBot you may sponsor it by buying our coins (AET) directly from us. We will assign a developer to attend your request. This way you will not just paying for a development job, but instead, staying invested in our currency, and later get your money back, potentially, with a profit, if you bought the currency at a convenient price.

Installation

Download the sources from our repository 

bash$ go get -v -u github.com/afterether/ethbot

Make sure your GOPATH is pointing to the correct directory.

Build EthBot:

bash$ go build github.com/afterether/ethbot

During compilation, errors may show if you do not have all the dependencies. Install them using this command: 

bash$ go get -v -u github.com/package_name_here

After compilation, the EthBot executable will be located in the current directory

Configuring AfterEther network

By default, Ethbot is configured for Ethereum Main Net, but if you want to configure it for AfterEther Main Net , execute this command: 

bash$ ./misc/setup-afterether.sh

To switch back to Ethereum , execute: 

bash$ ./misc/setup-ethereum.sh

To find out what kind of network is currently enabled, check the symbolic link in ./vendor/github.com/ethereum directory: 

bash$ ls -l
total 36
-rw-rw-r--  1 ethbot ethbot 17689 May 15 14:05 ethbot-patch4geth-172.diff
drwxrwxr-x 37 ethbot ethbot  4096 May 15 14:12 go-afterether-1.7.2
lrwxrwxrwx  1 ethbot ethbot    17 May 15 14:14 go-ethereum -> go-ethereum-1.7.2
drwxrwxr-x 36 ethbot ethbot  4096 May 15 14:13 go-ethereum-1.7.2

The symbolic link is pointing to the network's source code, Ethereum in this example

Configuring EthBot

Create Postgres database

Install and configure Postgres as usual. Once Postgres is installed , create a new Postgres user and a new database , owned by this user. Here is an example of doing it : 

bash$ su - postgres
bash$ createuser ethbot
bash$ createdb ethbot
bash$ psql
# now you have entered Postgres SQL console
postgres# alter user ethbot with encrypted password '123456';
postgres# grant all privileges on database ethbot to ethbot ;

Edit the script initdb.sh and modify the environment variables :

Run the script to create tables and PLSQL functions: 

bash$ ./initdb.sh

If you want to modify the database initialization script, all you have to do is to alter the init_database.sql file or the functions.sql file, which are located within the same directory as the init_database executable file. You can also run init_database.sql directly in Postgres

Configure EthBot to use the newly created database

Set the environment variables of database connection exactly as during the creation process:

Example: 

bash$ export ETHBOT_HOSTNAME=localhost
bash$ export ETHBOT_USERNAME=ethbot
bash$ export ETHBOT_PASSWORD=123456
bash$ export ETHBOT_DATABASE=ethbot

You may want to hide these variables in a bash_profile script and set appropriate permissions so it is not accessible to everybody

Running EthBot (Exporting data to SQL)

To run EthBot and start exporting the blockchain data immediately type bitcoin evolution review in the console: 

bash$ ./ethbot --gcmode archive --syncmode full

You will achieve the best performance if the node is already synchronized with the network, otherwise blocks will be inserted into SQL database as they are being downloaded.

Note that EthBot is a modified Ethereum geth , so, some geth parameters can be used. For example, to specify another data directory use: 

bash$ ./ethbot --datadir [path to data directory]

Type ./ethbot --help to see the list of all parameters EthBot supports.

Disabling export process at startup

Sometimes you don't want to start the export process automatically at startup, to disable it run EthBot with this flag: 

bash$ ./ethbot --noexport

The following JavaScript functions are provided to operate EthBot from geth console:

Managing EthBot

The following JavaScript functions are provided to operate EthBot from geth console:


ethbot.blockchainExportStart(starting_block,ending_block)

Description:

Parameters:

Return value:

Examples:

1) The following invocation will start the export process from the Genesis block and after exporting all the blocks, will enter into listening mode 

geth> ethbot.startBlockchainExportStart(0,-1)

2) The following invocation will start the export process from the last block you have exported in previous session and after exporting all the blocks , will enter into listening mode 

geth> ethbot.startBlockchainExportStart(-1,-1)

3) The following invocation will export blocks from 1,000,000 to 2,000,000 

geth> ethbot.startBlockchainExportStart(1000000,2000000)

Notes:

ethbot.blockchainExportStop()

Description:

Return value:

ethbot.blockchainExportStatus()

Description:

Return value:

Examples:

geth> ethbot.startBlockchainExportStatus()
{
    current_block: 2901328,
    direction: 1,
    ending_block: 3000000,
    listening_mode: true,
    starting_block: 2000000
}

ethbot.verifyLastBalances()

Description:

ethbot.fixLastBalances()

Description:

ethbot.exportBlockRange(starting_block,ending_block)

Description:

Parameters:

Return value:

Example:

To export blocks from 3,100,000 to 3,200,000 invoke the function like this: 

geth> ethbot.exportBlockRange(3100000,3200000)

ethbot.verifySQLdata1(block_num)

Description:

Parameters:

Return value:

Example:

Verify account balances for the block number 3,451,967 

geth> ethbot.verifySQLdata1(3451967)

ethbot.verifySQLdata2(block_num)

Description:

Parameters:

Return value:

Example:

Verify account balances for the block number 3,451,967 

geth> ethbot.verifySQLdata1(3451967)

ethbot.verificationStauts()

Description:

Return value:

Example:

geth> ethbot.verificationStatus()
    {
      block_num: 2001,
      cancelled_by_user: false,
      error_str: "",
      failed: false,
      finished_threads: 0,
      in_progress: false,
      num_accounts: 10212,
      num_processed: 562,
      total_threads: 89,
      valtr_id: 0
    }
    >

ethbot.stopVerification()

Description:

Return value:

ethbot.verifyAccount(address,block_num)

Description:

Parameters:

Return value:

ethbot.verifyAllAccounts()

Description:

Parameters:

Return value:

ethbot.updateMainStats()

Description:

Return value:

Additional Javascript console

To open a geth in another terminal use this command: 

bash$ ./ethbot attach ipc:/your_data_dir/geth.ipc

Now you can control the main EthBot process through another terminal.

Note: The error log and info log will be printed on the console of the main EthBot process, not the console connected through IPC.

Database schema

Database schema can be found in the file init_database.sql

Lookup table for value_transfer.kind is the following:

PLSQL Functions

EthBot provides auxiliary functions to query SQL database so you can avoid writing complex queries. The list of all functions can be found in functions.sql file

Get account balance

psql> SELECT get_balance(account_id,block_num) AS balance;

This function will query the latest balance for the account_id. Not that it queries per block, so only using the latest block will give you the latest balance. Use -1 to specify the latest block.

For example:

psql> SELECT get_balance4block(36313,-1)

To get account transactions

psql> SELECT get_TXs(account_id,starting_block,ending_block)

This function will return all the transactions for an account for a range of blocks Use -1 in ending_block to specify the latest block available

To get account value transfers

Value transfers are transfers of Ether. Value transfers compose a transaction , so if you need low level exploring (transfers between contracts), this function will help you. Note that value transfer can occur without a transaction, like for example during mining a block. 

psql> SELECT get_VTs(account_id,starting_block,ending_block)

Use -1 in ending_block to specify the latest block available

SQL query examples

Getting transactions by account address:

SELECT
        t.tx_id,t.from_id,t.to_id,t.tx_value,t.block_num 
FROM
		transaction AS t,account AS a 
WHERE
		(a.account_id=t.to_id AND a.address='b794f5ea0ba39494ce839613fffba74279579268') OR
		(a.account_id=t.from_id AND a.address='b794f5ea0ba39494ce839613fffba74279579268');

Get value transfers by account address:

SELECT
         vt.block_num,vt.from_id,vt.to_id,vt.value
FROM
         value_transfer AS vt,account AS a 
WHERE
        (a.account_id=vt.to_id AND a.address='b794f5ea0ba39494ce839613fffba74279579268') OR
        (a.account_id=vt.from_id AND a.address='b794f5ea0ba39494ce839613fffba74279579268');

Note, this is not an optimized query, optimized queries can be seen in AEX's source code

Get miner profits by account address:

SELECT 
        vt.block_num,vt.from_id,vt.to_id,vt.kind,vt.value 
FROM
        value_transfer AS vt,account AS a
WHERE 
        (vt.kind='4' AND a.account_id=vt.to_id AND a.address='e8b61c4a9a4143a64cec3a4d4edc76192007d79e') OR
        (vt.kind='4' AND a.account_id=vt.from_id AND a.address='e8b61c4a9a4143a64cec3a4d4edc76192007d79e ');

Get accounts with the highest balance:

SELECT * FROM account ORDER BY last_balance DESC LIMIT 20;




Copyright 2018 AfterEther Foundation, Belize. All Rights Reserved.