· 8 years ago · Apr 18, 2018, 09:44 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 = 5461452
15 val nBlocks = 1
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 CREATE TABLE IF NOT EXISTS balances(
35 address CHARACTER VARYING(100) NOT NULL PRIMARY KEY,
36 sent DOUBLE PRECISION,
37 received DOUBLE PRECISION,
38 final DOUBLE PRECISION,
39 n_sent DOUBLE PRECISION,
40 n_received DOUBLE PRECISION
41 )
42 """,
43 sql"""
44 INSERT INTO balances(address,sent,received, final, n_sent, n_received)
45 VALUES (?, ?, ?, ?, ?, ?)
46 """,
47 pg, nBlocks
48 )
49
50 println("table created")
51
52 // address => sent, received, final, n_sent, n_received
53 var map = Map[String, Array[Double]]()
54
55 var counter = 0
56
57 val rate = PriceHistorical.getRate(Calendar.getInstance().getTime)
58 val weiInEth = 1e18
59 println(Calendar.getInstance().getTime.getTime)
60 println("rate: " + rate.toString)
61
62 println("foreach starting...")
63 blockchain.foreach(block => {
64 counter += 1
65
66 if (block.height % 10 == 0)
67 println(counter.toString + ") n transactions: " + block.txs.length)
68
69 block.txs.foreach(transaction => {
70// if (transaction.hash == "0x9e6ce37f81e72638cbbaeb6cd304fafc76a1b1b17b2cf470545269d2b254e00b") {
71 if (transaction.hash == "0x56d08c077bc9958e03ff3e13413f7aff47ead42668a567a8d3dc2b75fcbd2929") {
72
73 val dollars = transaction.value.toDouble / weiInEth * rate
74
75 println("transaction.value: " + transaction.value.toDouble)
76 println("eth: " + transaction.value.toDouble / weiInEth)
77 println("dollars: " + dollars)
78
79 if (map.contains(transaction.from)) {
80 // if the address "from" is already present in the map,
81 // we can update its balance
82 /** how to update:
83 * the "value" goes from the "from" address to the "to" address
84 * 1. add "value" to the sent ethereums
85 * 2. substract "value" from the final balance
86 * 3. increment the number of transactions that have taken
87 * eth from "from", i.e. increment n_sent
88 */
89
90 // first, save the current balance
91 val balance: Array[Double] = map(transaction.from)
92
93 // update of the quantities
94
95 // update of the quantity of eth sent
96 balance(0) += dollars // sent += value
97
98 // update of the final balance
99 balance(2) -= dollars // final -= value
100
101 // update of the number of transaction that the address has sent to
102 balance(3) += 1 // n_sent ++
103 }
104 else {
105 // the address "from" is not in the map, so it must be added
106 // only if there is a transaction sending money
107 if (dollars > 0)
108
109 /** add an entry to the map which goes from the "from" address to
110 * an array with:
111 * sent: value
112 * received: 0
113 * final: -value
114 * n_sent: 1
115 * n_received: 0
116 */
117 map += transaction.from -> Array(dollars, 0, -dollars, 1, 0)
118 }
119
120 if (map.contains(transaction.to)) {
121 // if the address "to" is already present in the map,
122 // we can update its balance
123
124 /** how to update:
125 * the "value" goes from the "from" address to the "to" address
126 * 1. add "value" to the received ethereums
127 * 2. add "value" to the final balance
128 * 3. increment the number of transactions that have sent
129 * eth to "to", i.e. increment n_receivd
130 */
131
132 // first, save the current balance
133 val balance: Array[Double] = map(transaction.to)
134
135 // update of the quantity of eth sent
136 balance(1) += dollars // received += value
137
138 // update of the final balance
139 balance(2) += dollars // final += value
140
141 // update of the number of transaction that have taken eth from that address
142 balance(4) += 1 // n_received ++
143 }
144 else {
145 // the address "to" is not in the map, so it must be added
146 // only if there is a transaction sending money
147 if (dollars > 0)
148
149 /** add an entry to the map which goes from the "to" address to
150 * an array with:
151 * sent: 0
152 * received: value
153 * final: value
154 * n_sent: 0
155 * n_received: 1
156 */
157 map += transaction.to -> Array(0, dollars, dollars, 0, 1)
158 }
159 }
160 })
161 })
162 println("...foreach ended")
163
164 for(address <- map.keys) {
165 blockTable.insert(address :: map(address).toList) // insert each element of the map in the db
166 println(address::map(address).toList)
167 }
168 println("data inserted")
169
170 blockTable.close
171 println("table closed")
172
173 println("END")
174 }
175}