getDataForDatabase.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Creating Database Tables"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that the execution of this script requires running an Ethereum node, Arbitrum node and Optimism node. We ran an Erigon node (https://github.com/ledgerwatch/erigon) for Ethereum, as well as a Geth node for Arbitrum (https://docs.arbitrum.io/run-arbitrum-node/overview) and Optimism (https://docs.optimism.io/builders/node-operators/tutorials/node-from-docker). Note that some of our other scripts rely on our database. The database, however, only stores unaltered blockchain infomration and is simply used to allow quicker access."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following version were used:\n",
    "- Python version: 3.10.12\n",
    "- pip version: 22.3.1\n",
    "- web3 version: 5.23.1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "powershell"
    }
   },
   "outputs": [],
   "source": [
    "#install packages\n",
    "!pip install web3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#import libaries\n",
    "from web3 import Web3, HTTPProvider"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#url of Ethereum node\n",
    "urlETH = \"\"\n",
    "#connect to Ethereum client \n",
    "w3ETH = Web3(HTTPProvider(urlETH))\n",
    "\n",
    "#url of Arbitrum node\n",
    "urlARB = \"\"\n",
    "#connect to Arbitrum client \n",
    "w3ARB = Web3(HTTPProvider(urlARB))\n",
    "\n",
    "#url of Optimism node\n",
    "urlOP = \"\"\n",
    "#connect to Arbitrum client \n",
    "w3OP = Web3(HTTPProvider(urlOP))"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## blocks Table"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Column descriptions in blocks table\n",
    "\n",
    "\n",
    "|      column      |            Type             |            Entry in block object returned by web3.eth API             |\n",
    "|------------------|-----------------------------|-----------------------------------------------------------------------|\n",
    "| height           | bigint                      | number                                                               |\n",
    "| hash             | bytea                       | hash                                            |\n",
    "| miner            | bytea                       | miner                                            |\n",
    "| state_root       | bytea                       | stateRoot                                            |\n",
    "| txs_root         | bytea                       | transactionsRoot                                            |\n",
    "| receipts_root    | bytea                       | receiptsRoot                                            |\n",
    "| gas_used         | bigint                      | gasUsed                                            |\n",
    "| gas_limit        | bigint                      | gasLimit                                            |\n",
    "| base_fee_per_gas | bigint                      | baseFeePerGas                                            |\n",
    "| extra_data       | bytea                       | extraData                                            |\n",
    "| logs_bloom       | bytea                       | logsBloom                                                                   |\n",
    "| time_mined       | timestamp without time zone | timestamp                                                                   |\n",
    "| difficulty       | bytea                       | difficulty                                                                   |\n",
    "| total_difficulty | bytea                       | totalDifficulty                                                                   |\n",
    "| block_size       | bigint                      | size                                                                   |\n",
    "| mix_hash         | bytea                       | number                                                                  |\n",
    "| nonce            | bytea                       | nonce                                                                   |\n",
    "\n",
    "We query every block information for every block and write the unaltered information into the psql table shown above. Above we indicate, which field in the blockObject returned by the web3.eth API corresponds to each column. Below we show the core for obtaining the blockObject."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "lastBlock =w3ETH.eth.get_block_number()\n",
    "blocks = range(lastBlock)\n",
    "for block in blocks: \n",
    "    blockObject = w3ETH.eth.getBlock(block)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "lastBlock =w3ARB.eth.get_block_number()\n",
    "blocks = range(lastBlock)\n",
    "for block in blocks: \n",
    "    blockObject = w3ARB.eth.getBlock(block)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "lastBlock =w3OP.eth.get_block_number()\n",
    "blocks = range(lastBlock)\n",
    "for block in blocks: \n",
    "    blockObject = w3OP.eth.getBlock(block)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## logs Table"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Column descriptions in logs table\n",
    "\n",
    "|    column    |  Type   |          Entry in log object returned by web3.eth API             | \n",
    "|--------------|---------|-----------------------------------------------------------------------|\n",
    "| address      | bytea   | address |\n",
    "| topic0       | bytea   | topics[0]|\n",
    "| topic1       | bytea   | topics[1]|\n",
    "| topic2       | bytea   | topics[2] |\n",
    "| topic3       | bytea   | topics[3]|\n",
    "| data         | bytea   | data|\n",
    "| block_number | bigint  | blockNumber|\n",
    "| tx_index     | bigint  | transactionIndex|\n",
    "| log_index    | bigint  | logIndex|\n",
    "| removed      | boolean | removed|\n",
    "\n",
    "We query the logs for every block and write the unaltered information into the psql table shown above. Above we indicate, which field in the logObject returned by the web3.eth API corresponds to each column. Below we show the core for obtaining the logObject."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "lastBlock =w3ETH.eth.get_block_number()\n",
    "blocks = range(lastBlock)\n",
    "for block in blocks: \n",
    "    logs = w3ETH.eth.getLogs({\"fromBlock\":block, \"toBlock\":block},)\n",
    "    for object in logs: \n",
    "        logObject=object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "lastBlock =w3ARB.eth.get_block_number()\n",
    "blocks = range(lastBlock)\n",
    "for block in blocks: \n",
    "    logs = w3ARB.eth.getLogs({\"fromBlock\":block, \"toBlock\":block},)\n",
    "    for object in logs: \n",
    "        logObject=object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "lastBlock =w3OP.eth.get_block_number()\n",
    "blocks = range(lastBlock)\n",
    "for block in blocks: \n",
    "    logs = w3OP.eth.getLogs({\"fromBlock\":block, \"toBlock\":block},)\n",
    "    for object in logs: \n",
    "        logObject=object"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## bytea2numeric Function"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We implement the function below on our database to convert bytea to numeric. The function is taken from https://stackoverflow.com/questions/37248518/sql-function-to-convert-numeric-to-bytea-and-bytea-to-numeric."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "vscode": {
     "languageId": "sql"
    }
   },
   "outputs": [],
   "source": [
    "\n",
    "CREATE OR REPLACE FUNCTION bytea2numeric(_b BYTEA) RETURNS NUMERIC AS $$\n",
    "DECLARE\n",
    "    _n NUMERIC := 0;\n",
    "BEGIN\n",
    "    FOR _i IN 0 .. LENGTH(_b)-1 LOOP\n",
    "        _n := _n*256+GET_BYTE(_b,_i);\n",
    "    END LOOP;\n",
    "    RETURN _n;\n",
    "END;\n",
    "$$ LANGUAGE PLPGSQL IMMUTABLE STRICT;"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7 (tags/v3.9.7:1016ef3, Aug 30 2021, 20:19:38) [MSC v.1929 64 bit (AMD64)]"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "82ed002fa2d4956f5c6aec99bcefe0f73a9f79882f3c9e2319b14958a5896ac5"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}