· 8 years ago · Apr 18, 2018, 10:04 AM
1package tcs.examples.ethereum.sql
2
3import java.util.Calendar
4
5import tcs.blockchain.BlockchainLib
6import tcs.db.sql.Table
7import tcs.db.{DatabaseSettings, PostgreSQL}
8import scalikejdbc._
9import tcs.blockchain.ethereum.EthereumSettings
10import tcs.custom.ethereum.PriceHistorical
11
12object EthereumPools {
13 def main(args: Array[String]): Unit = {
14 val start = 5461000
15 val nBlocks = 100
16 val end = start + nBlocks
17 val blockchain = BlockchainLib.getEthereumBlockchain(new EthereumSettings("https://mainnet.infura.io/lGhdnAJw7n56K0xXGP3i:8545")).start(start).end(end)
18 val pg = new DatabaseSettings("ethereum", PostgreSQL, "postgres", "0")
19
20 /**
21 * Table:
22 * address: ~
23 * sent: amount of ethereum that the address has sent
24 * received: amount of ethereum that the address has received
25 * total: ~
26 * n_sent: number of transactions which the address has sent etheruem,
27 * or number of transactions that have withdrawn from that address
28 * n_received: number of transactions which the address has received
29 * etheruem from, or number of transactions that have sent
30 * ethereum to that address
31 */
32 val blockTable = new Table(
33 sql"""
34 DROP TABLE IF EXISTS balances;
35 CREATE TABLE IF NOT EXISTS balances(
36 address CHARACTER VARYING(100) NOT NULL PRIMARY KEY,
37 sent DOUBLE PRECISION,
38 received DOUBLE PRECISION,
39 final DOUBLE PRECISION,
40 n_sent DOUBLE PRECISION,
41 n_received DOUBLE PRECISION
42 )
43 """,
44 sql"""
45 INSERT INTO balances(address,sent,received, final, n_sent, n_received)
46 VALUES (?, ?, ?, ?, ?, ?)
47 """,
48 pg, nBlocks
49 )
50
51 println("table created")
52
53 // map from the address to the array of data
54 // address => sent, received, final, n_sent, n_received
55 var map = Map[String, Array[Double]]()
56
57
58 val rate = PriceHistorical.getRate(Calendar.getInstance().getTime)
59 val weiInEth = 1e18
60 println("rate ETH->USD: " + rate.toString)
61
62 println("foreach starting...")
63 blockchain.foreach(block => {
64 if (block.height % 10 == 0)
65 println(block.height + ") n transactions inside: " + block.txs.length)
66
67 block.txs.foreach(transaction => {
68 val dollars = transaction.value.toDouble / weiInEth * rate
69
70 if (map.contains(transaction.from)) {
71 // if the address "from" is already present in the map,
72 // we can update its balance
73 /** how to update:
74 * the "value" goes from the "from" address to the "to" address
75 * 1. add "value" to the sent ethereums
76 * 2. substract "value" from the final balance
77 * 3. increment the number of transactions that have taken
78 * eth from "from", i.e. increment n_sent
79 */
80
81 // current balance
82 val balance: Array[Double] = map(transaction.from)
83
84 // update of the quantities
85
86 // update of the quantity of eth sent
87 balance(0) += dollars // sent += value
88
89 // update of the final balance
90 balance(2) -= dollars // final -= value
91
92 // update of the number of transaction that the address has sent to
93 balance(3) += 1 // n_sent ++
94 }
95 else {
96 // the address "from" is not in the map, so it must be added
97 // only if there is a transaction sending money
98 if (dollars > 0)
99
100 /** add an entry to the map which goes from the "from" address to
101 * an array with:
102 * sent: value
103 * received: 0
104 * final: -value
105 * n_sent: 1
106 * n_received: 0
107 */
108 map += transaction.from -> Array(dollars, 0, -dollars, 1, 0)
109 }
110
111 if (map.contains(transaction.to)) {
112 // if the address "to" is already present in the map,
113 // we can update its balance
114
115 /** how to update:
116 * the "value" goes from the "from" address to the "to" address
117 * 1. add "value" to the received ethereums
118 * 2. add "value" to the final balance
119 * 3. increment the number of transactions that have sent
120 * eth to "to", i.e. increment n_receivd
121 */
122
123 // current balance
124 val balance: Array[Double] = map(transaction.to)
125
126 // update of the quantity of eth sent
127 balance(1) += dollars // received += value
128
129 // update of the final balance
130 balance(2) += dollars // final += value
131
132 // update of the number of transaction that have taken eth from that address
133 balance(4) += 1 // n_received ++
134 }
135 else {
136 // the address "to" is not in the map, so it must be added
137 // only if there is a transaction sending money
138 if (dollars > 0)
139
140 /** add an entry to the map which goes from the "to" address to
141 * an array with:
142 * sent: 0
143 * received: value
144 * final: value
145 * n_sent: 0
146 * n_received: 1
147 */
148 map += transaction.to -> Array(0, dollars, dollars, 0, 1)
149 }
150 })
151 })
152 println("...foreach ended")
153
154 for(address <- map.keys) {
155 blockTable.insert(address :: map(address).toList) // insert each element of the map in the db
156 println(address::map(address).toList)
157 }
158 println("data inserted")
159
160 blockTable.close
161 println("table closed")
162
163 println("END")
164 }
165}