Ethereum Tables : Schema Description - WeeFin/WeeFinProject GitHub Wiki

To analyze the ethereum blockchain we downloaded the ethereum blocks with geth utility. In order to get the data in a much readable version, we used ethereum-etl to get all the informations in CSV files.

We have three different types of CSV files (which can be interpreted as tables): blocks, transactions and erc20_transfers.

Quick overview :

Let's take a look at these tables :

Blocks table schema

Uncles : an orphan block (mined just after a correct block was added to the main chain), that contributes to the security of the main chain.

Gas : Gas is what is used to calculate the amount of fees that need to be paid to the network in order to execute an operation.

Proof of work : Cycling through solutions in order to guess the nonce

Difficulty : a measure of how difficult it is to find a hash below a given target.

log bloom filter : Events in the ethereum system must be easily searched for, so that applications can filter and display events, including historical ones, without undue overhead. At the same time, storage space is expensive, so we don't want to store a lot of duplicate data - such as the list of transactions, and the logs they generate. The logs bloom filter exists to resolve this.

Merkle Tree : Arbre d’hash de hash contenant un résumé d’information d’un volume de données, les feuilles de l’arbre sont les valeurs de hash de chacun des blocks de données initiales. Ces hash sont concaténés deux à deux pour pouvoir calculer un nouveau hash parent. Utilisé dans la vérification de données dans un système distribué car les données existent dans plusieurs localisations. Par exemple, au lieu de checker l’intégralité du fichier, on checke uniquement les hashs des fichiers.

  • block_number
  • block_hash : hash of the block
  • block_parent_hash
  • block_nonce (nonce stands for «number that can only be used once») : random number
  • block_sha3_uncles : SHA3 of the uncles data in the block
  • block_logs_bloom : the bloom filter for the logs of the block
  • block_transactions_root : the root of the transaction trie of the block
  • block_state_root : the root of the final state trie of the block
  • block_miner : the address of the beneficiary to whom the mining rewards were given
  • block_difficulty : integer of the difficulty for this block
  • block_total_difficulty : integer of the total difficulty of the chain until this block
  • block_size : integer the size of this block in bytes
  • block_extra_data : the "extra data" field of this block
  • block_gas_limit : the maximum gas allowed in this block
  • block_gas_used : the total used gas by all transactions in this block
  • block_timestamp : the unix timestamp for when the block was collated
  • block_transaction_count :

Transactions table schema

  • tx_hash : hash of the transaction.
  • tx_nonce : the number of transactions made by the sender prior to this one
  • tx_block_hash: hash of the block where this transaction was in
  • tx_block_number : block number where this transaction was in
  • tx_index : integer of the transactions index position in the block
  • tx_from : address of the sender
  • tx_to : address of the receiver
  • tx_value : value transferred in Wei (1 Wei = 10^18 Ether)
  • tx_gas : gas provided by the sender
  • tx_gas_price : gas price provided by the sender in Wei
  • tx_input : the data send along with the transaction

Real Transaction example :

ERC20_transfers table schema

Transfers of tokens following the ERC20 (Ethereum Request for comments) standard

ERC20 is just a standard for tokens.

Token : a token is nothing more than a smart contract running on top of the ethereum blockchain. As such, it is a set of code (functions) with an associated database. The code describes the behavior of the token, and the database is basically a table with rows and columns tracking who owns how many tokens.

ERC20 specifications (six functions) :

  • totalSupply sends the total number of existing tokens

  • balanceOf allows to consult the number of tokens got by an account

  • allowance returns the number of tokens an address is allowed to remove from the token's contract

  • transfer allows to send token to another account

  • transferFrom allows to transfer tokens from one address to another by guaranteeing that the address which sends the transaction is the one which has the tokens

  • approve allows to the owner of a token's contract to approuve a withdrawal for a given amount by a specific acccount

Ethereum Request for comments : processus par lequel une personne demande à la communauté de revoir et de commenter une proposition pour ethereum.

Coin VS Token :

Coins are just method of payment while tokens may present a company’s share, give access to product or service and perform many other functions. Coins are currencies that can be used for buying and selling things. You can buy a token with a coin, but not vice versa. Coin operates independently, while token has a specific use in the project’s ecosystem.

  • erc20_token : name of the token (EOS,OmiseGO,TRON)

  • erc20_from

  • erc20_to

  • erc20_value : This field is used to calcultate the number of tokens transferred. If T is the name of the ERC-20 token you are looking at, then to find the number of T tokens involved in the transaction, take value and divide by 10^decimals. For example, for the ZRX token, the decimal is the same as for ether, 1 wei (10^18).

  • erc20_tx_hash

  • erc20_log_index

  • erc20_block_number

Real ERC20 transfer :