EthereumDB/sql_helper.py at master · validitylabs/EthereumDB · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
"""
Author: Aleksandra Sokolowska
for Validity Labs AG
"""
def create_database(cur):
    """ create the schema for the database"""
    quick = """
    CREATE TABLE IF NOT EXISTS Quick (
     balanceFrom TEXT,
     balanceTo TEXT,
     blockNumber INTEGER,
     sender TEXT,
     nonce INTEGER,
     recipient TEXT,
     txHash TEXT PRIMARY KEY,
     value TEXT);"""

    tx = """
    CREATE TABLE IF NOT EXISTS TX (
     blockNumber INTEGER,
     contractAddress TEXT,
     cumulativeGasUsed INTEGER,
     gas INTEGER,
     gasPrice INTEGER,
     gasUsed INTEGER,
     input TEXT,
     logs TEXT,
     logsBloom TEXT,
     r TEXT,
     s TEXT,
     status INTEGER,
     txHash TEXT PRIMARY KEY,
     transactionIndex INTEGER,
     v INTEGER);"""

    blck = """
    CREATE TABLE IF NOT EXISTS block (
     blockGasUsed INTEGER,
     blockHash TEXT,
     blockLogsBloom TEXT,
     blockNonce TEXT,
     blockNumber INTEGER PRIMARY KEY,
     difficulty TEXT,
     extraData TEXT,
     gasLimit INTEGER,
     miner TEXT,
     mixHash TEXT,
     parentHash TEXT,
     receiptsRoot TEXT,
     sha3Uncles TEXT,
     size INTEGER,
     stateRoot TEXT,
     timestamp INTEGER,
     totalDifficulty TEXT,
     transactions TEXT,
     transactionsRoot TEXT,
     uncles TEXT); """

    cur.execute(quick)
    cur.execute(blck)
    cur.execute(tx)

def create_index(cur):
    quick = "CREATE INDEX index_quick ON Quick(value, sender, recipient);"
    tx = "CREATE INDEX index_TX ON TX(blockNumber, status);"
    blck = "CREATE INDEX index_block ON block(timestamp);"

    cur.execute(quick)
    cur.execute(blck)
    cur.execute(tx)

def update_database(cur, table_quick, table_tx, table_block):
    """ write lists of dictionaries into the database"""
    quick = """INSERT INTO Quick VALUES (:balanceFrom, :balanceTo, :blockNumber, :from, :nonce, :to, :txHash, :value); """
    tx = """ INSERT INTO TX VALUES (:blockNumber, :contractAddress, :cumulativeGasUsed, :gas, :gasPrice, :gasUsed, :input, :logs, :logsBloom, :r, :s, :status, :txHash, :transactionIndex, :v); """
    blck = """ INSERT INTO block VALUES (:blockGasUsed, :blockHash, :blockLogsBloom, :blockNonce, :blockNumber,  :difficulty, :extraData, :gasLimit, :miner, :mixHash, :parentHash, :receiptsRoot, :sha3Uncles, :size, :stateRoot, :timestamp, :totalDifficulty, :transactions, :transactionsRoot, :uncles); """
    cur.executemany(quick, table_quick)
    cur.executemany(tx, table_tx)
    cur.executemany(blck, table_block)