hermez-node/db/historydb/historydb.go
db/historydb/historydb.goRaw
1package historydb
2
3import (
4 "database/sql"
5 "errors"
6 "fmt"
7
8 ethCommon "github.com/ethereum/go-ethereum/common"
9 "github.com/gobuffalo/packr/v2"
10 "github.com/hermeznetwork/hermez-node/common"
11 "github.com/hermeznetwork/hermez-node/db"
12 "github.com/hermeznetwork/hermez-node/log"
13 "github.com/iden3/go-iden3-crypto/babyjub"
14 "github.com/jmoiron/sqlx"
15
16 //nolint:errcheck // driver for postgres DB
17 _ "github.com/lib/pq"
18 migrate "github.com/rubenv/sql-migrate"
19 "github.com/russross/meddler"
20)
21
22// TODO(Edu): Document here how HistoryDB is kept consistent
23
24// HistoryDB persist the historic of the rollup
25type HistoryDB struct {
26 db *sqlx.DB
27}
28
29// NewHistoryDB initialize the DB
30func NewHistoryDB(port int, host, user, password, dbname string) (*HistoryDB, error) {
31 // Connect to DB
32 psqlconn := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", host, port, user, password, dbname)
33 hdb, err := sqlx.Connect("postgres", psqlconn)
34 if err != nil {
35 return nil, err
36 }
37 // Init meddler
38 db.InitMeddler()
39 meddler.Default = meddler.PostgreSQL
40
41 // Run DB migrations
42 migrations := &migrate.PackrMigrationSource{
43 Box: packr.New("history-migrations", "./migrations"),
44 }
45 nMigrations, err := migrate.Exec(hdb.DB, "postgres", migrations, migrate.Up)
46 if err != nil {
47 return nil, err
48 }
49 log.Debug("HistoryDB applied ", nMigrations, " migrations for ", dbname, " database")
50
51 return &HistoryDB{hdb}, nil
52}
53
54// AddBlock insert a block into the DB
55func (hdb *HistoryDB) AddBlock(block *common.Block) error {
56 return meddler.Insert(hdb.db, "block", block)
57}
58
59// AddBlocks inserts blocks into the DB
60func (hdb *HistoryDB) AddBlocks(blocks []common.Block) error {
61 return db.BulkInsert(
62 hdb.db,
63 `INSERT INTO block (
64 eth_block_num,
65 timestamp,
66 hash
67 ) VALUES %s;`,
68 blocks[:],
69 )
70}
71
72// GetBlock retrieve a block from the DB, given a block number
73func (hdb *HistoryDB) GetBlock(blockNum int64) (*common.Block, error) {
74 block := &common.Block{}
75 err := meddler.QueryRow(
76 hdb.db, block,
77 "SELECT * FROM block WHERE eth_block_num = $1;", blockNum,
78 )
79 return block, err
80}
81
82// GetBlocks retrieve blocks from the DB, given a range of block numbers defined by from and to
83func (hdb *HistoryDB) GetBlocks(from, to int64) ([]*common.Block, error) {
84 var blocks []*common.Block
85 err := meddler.QueryAll(
86 hdb.db, &blocks,
87 "SELECT * FROM block WHERE $1 <= eth_block_num AND eth_block_num < $2",
88 from, to,
89 )
90 return blocks, err
91}
92
93// GetLastBlock retrieve the block with the highest block number from the DB
94func (hdb *HistoryDB) GetLastBlock() (*common.Block, error) {
95 block := &common.Block{}
96 err := meddler.QueryRow(
97 hdb.db, block, "SELECT * FROM block ORDER BY eth_block_num DESC LIMIT 1;",
98 )
99 return block, err
100}
101
102// AddBatches insert Bids into the DB
103func (hdb *HistoryDB) AddBatches(batches []common.Batch) error {
104 return db.BulkInsert(
105 hdb.db,
106 `INSERT INTO batch (
107 batch_num,
108 eth_block_num,
109 forger_addr,
110 fees_collected,
111 state_root,
112 num_accounts,
113 exit_root,
114 forge_l1_txs_num,
115 slot_num
116 ) VALUES %s;`,
117 batches[:],
118 )
119}
120
121// GetBatches retrieve batches from the DB, given a range of batch numbers defined by from and to
122func (hdb *HistoryDB) GetBatches(from, to common.BatchNum) ([]*common.Batch, error) {
123 var batches []*common.Batch
124 err := meddler.QueryAll(
125 hdb.db, &batches,
126 "SELECT * FROM batch WHERE $1 <= batch_num AND batch_num < $2",
127 from, to,
128 )
129 return batches, err
130}
131
132// GetLastBatchNum returns the BatchNum of the latest forged batch
133func (hdb *HistoryDB) GetLastBatchNum() (common.BatchNum, error) {
134 row := hdb.db.QueryRow("SELECT batch_num FROM batch ORDER BY batch_num DESC LIMIT 1;")
135 var batchNum common.BatchNum
136 return batchNum, row.Scan(&batchNum)
137}
138
139// GetLastL1TxsNum returns the greatest ForgeL1TxsNum in the DB. If there's no
140// batch in the DB (nil, nil) is returned.
141func (hdb *HistoryDB) GetLastL1TxsNum() (*int64, error) {
142 row := hdb.db.QueryRow("SELECT MAX(forge_l1_txs_num) FROM batch;")
143 lastL1TxsNum := new(int64)
144 return lastL1TxsNum, row.Scan(&lastL1TxsNum)
145}
146
147// Reorg deletes all the information that was added into the DB after the lastValidBlock
148func (hdb *HistoryDB) Reorg(lastValidBlock int64) error {
149 _, err := hdb.db.Exec("DELETE FROM block WHERE eth_block_num > $1;", lastValidBlock)
150 return err
151}
152
153// SyncRollup stores all the data that can be changed / added on a block in the Rollup SC
154func (hdb *HistoryDB) SyncRollup(
155 blockNum uint64,
156 l1txs []common.L1Tx,
157 l2txs []common.L2Tx,
158 registeredAccounts []common.Account,
159 exitTree common.ExitInfo,
160 withdrawals common.ExitInfo,
161 registeredTokens []common.Token,
162 batches []common.Batch,
163 vars *common.RollupVars,
164) error {
165 // TODO: make all in a single DB commit
166 if err := hdb.AddBatches(batches); err != nil {
167 return err
168 }
169 return nil
170}
171
172// SyncPoD stores all the data that can be changed / added on a block in the PoD SC
173func (hdb *HistoryDB) SyncPoD(
174 blockNum uint64,
175 bids []common.Bid,
176 coordinators []common.Coordinator,
177 vars *common.AuctionVars,
178) error {
179 return nil
180}
181
182// addBids insert Bids into the DB
183func (hdb *HistoryDB) addBids(bids []common.Bid) error {
184 // TODO: check the coordinator info
185 return db.BulkInsert(
186 hdb.db,
187 "INSERT INTO bid (slot_num, forger_addr, bid_value, eth_block_num) VALUES %s",
188 bids[:],
189 )
190}
191
192// GetBids return the bids
193func (hdb *HistoryDB) GetBids() ([]*common.Bid, error) {
194 var bids []*common.Bid
195 err := meddler.QueryAll(
196 hdb.db, &bids,
197 "SELECT * FROM bid;",
198 )
199 return bids, err
200}
201
202// AddToken insert a token into the DB
203func (hdb *HistoryDB) AddToken(token *common.Token) error {
204 return meddler.Insert(hdb.db, "token", token)
205}
206
207// AddTokens insert tokens into the DB
208func (hdb *HistoryDB) AddTokens(tokens []common.Token) error {
209 return db.BulkInsert(
210 hdb.db,
211 `INSERT INTO token (
212 token_id,
213 eth_block_num,
214 eth_addr,
215 name,
216 symbol,
217 decimals,
218 usd,
219 usd_update
220 ) VALUES %s;`,
221 tokens[:],
222 )
223}
224
225// UpdateTokenValue updates the USD value of a token
226func (hdb *HistoryDB) UpdateTokenValue(tokenID common.TokenID, value float64) error {
227 _, err := hdb.db.Exec(
228 "UPDATE token SET usd = $1 WHERE token_id = $2;",
229 value, tokenID,
230 )
231 return err
232}
233
234// GetTokens returns a list of tokens from the DB
235func (hdb *HistoryDB) GetTokens() ([]*common.Token, error) {
236 var tokens []*common.Token
237 err := meddler.QueryAll(
238 hdb.db, &tokens,
239 "SELECT * FROM token ORDER BY token_id;",
240 )
241 return tokens, err
242}
243
244// AddAccounts insert accounts into the DB
245func (hdb *HistoryDB) AddAccounts(accounts []common.Account) error {
246 return db.BulkInsert(
247 hdb.db,
248 `INSERT INTO account (
249 idx,
250 token_id,
251 batch_num,
252 bjj,
253 eth_addr
254 ) VALUES %s;`,
255 accounts[:],
256 )
257}
258
259// GetAccounts returns a list of accounts from the DB
260func (hdb *HistoryDB) GetAccounts() ([]*common.Account, error) {
261 var accs []*common.Account
262 err := meddler.QueryAll(
263 hdb.db, &accs,
264 "SELECT * FROM account ORDER BY idx;",
265 )
266 return accs, err
267}
268
269// AddL1Txs inserts L1 txs to the DB
270func (hdb *HistoryDB) AddL1Txs(l1txs []common.L1Tx) error {
271 txs := []common.Tx{}
272 for _, tx := range l1txs {
273 txs = append(txs, *tx.Tx())
274 }
275 return hdb.AddTxs(txs)
276}
277
278// AddL2Txs inserts L2 txs to the DB
279func (hdb *HistoryDB) AddL2Txs(l2txs []common.L2Tx) error {
280 txs := []common.Tx{}
281 for _, tx := range l2txs {
282 txs = append(txs, *tx.Tx())
283 }
284 return hdb.AddTxs(txs)
285}
286
287// AddTxs insert L1 txs into the DB
288func (hdb *HistoryDB) AddTxs(txs []common.Tx) error {
289 return db.BulkInsert(
290 hdb.db,
291 `INSERT INTO tx (
292 is_l1,
293 id,
294 type,
295 position,
296 from_idx,
297 to_idx,
298 amount,
299 amount_f,
300 token_id,
301 amount_usd,
302 batch_num,
303 eth_block_num,
304 to_forge_l1_txs_num,
305 user_origin,
306 from_eth_addr,
307 from_bjj,
308 load_amount,
309 load_amount_f,
310 load_amount_usd,
311 fee,
312 fee_usd,
313 nonce
314 ) VALUES %s;`,
315 txs[:],
316 )
317}
318
319// GetTxs returns a list of txs from the DB
320func (hdb *HistoryDB) GetTxs() ([]*common.Tx, error) {
321 var txs []*common.Tx
322 err := meddler.QueryAll(
323 hdb.db, &txs,
324 `SELECT * FROM tx
325 ORDER BY (batch_num, position) ASC`,
326 )
327 return txs, err
328}
329
330// GetHistoryTxs returns a list of txs from the DB using the HistoryTx struct
331func (hdb *HistoryDB) GetHistoryTxs(
332 ethAddr *ethCommon.Address, bjj *babyjub.PublicKey,
333 tokenID, idx, batchNum *uint, txType *common.TxType,
334 offset, limit *uint, last bool,
335) ([]*HistoryTx, int, error) {
336 if ethAddr != nil && bjj != nil {
337 return nil, 0, errors.New("ethAddr and bjj are incompatible")
338 }
339 var query string
340 var args []interface{}
341 queryStr := `SELECT tx.*, tx.amount_f * token.usd AS current_usd,
342 token.symbol, token.usd_update, block.timestamp, count(*) OVER() AS total_items FROM tx
343 INNER JOIN token ON tx.token_id = token.token_id
344 INNER JOIN block ON tx.eth_block_num = block.eth_block_num `
345 // Apply filters
346 nextIsAnd := false
347 // ethAddr filter
348 if ethAddr != nil {
349 queryStr = `WITH acc AS
350 (select idx from account where eth_addr = ?) ` + queryStr
351 queryStr += ", acc WHERE (tx.from_idx IN(acc.idx) OR tx.to_idx IN(acc.idx)) "
352 nextIsAnd = true
353 args = append(args, ethAddr)
354 } else if bjj != nil { // bjj filter
355 queryStr = `WITH acc AS
356 (select idx from account where bjj = ?) ` + queryStr
357 queryStr += ", acc WHERE (tx.from_idx IN(acc.idx) OR tx.to_idx IN(acc.idx)) "
358 nextIsAnd = true
359 args = append(args, bjj)
360 }
361 // tokenID filter
362 if tokenID != nil {
363 if nextIsAnd {
364 queryStr += "AND "
365 } else {
366 queryStr += "WHERE "
367 }
368 queryStr += "tx.token_id = ? "
369 args = append(args, tokenID)
370 nextIsAnd = true
371 }
372 // idx filter
373 if idx != nil {
374 if nextIsAnd {
375 queryStr += "AND "
376 } else {
377 queryStr += "WHERE "
378 }
379 queryStr += "(tx.from_idx = ? OR tx.to_idx = ?) "
380 args = append(args, idx, idx)
381 nextIsAnd = true
382 }
383 // batchNum filter
384 if batchNum != nil {
385 if nextIsAnd {
386 queryStr += "AND "
387 } else {
388 queryStr += "WHERE "
389 }
390 queryStr += "tx.batch_num = ? "
391 args = append(args, batchNum)
392 nextIsAnd = true
393 }
394 // txType filter
395 if txType != nil {
396 if nextIsAnd {
397 queryStr += "AND "
398 } else {
399 queryStr += "WHERE "
400 }
401 queryStr += "tx.type = ? "
402 args = append(args, txType)
403 // nextIsAnd = true
404 }
405 // pagination
406 if last {
407 queryStr += "ORDER BY (batch_num, position) DESC NULLS FIRST "
408 } else {
409 queryStr += "ORDER BY (batch_num, position) ASC NULLS LAST "
410 queryStr += fmt.Sprintf("OFFSET %d ", *offset)
411 }
412 queryStr += fmt.Sprintf("LIMIT %d ", *limit)
413 query = hdb.db.Rebind(queryStr)
414 // log.Debug(query)
415 txs := []*HistoryTx{}
416 if err := meddler.QueryAll(hdb.db, &txs, query, args...); err != nil {
417 return nil, 0, err
418 }
419 if len(txs) == 0 {
420 return nil, 0, sql.ErrNoRows
421 } else if last {
422 tmp := []*HistoryTx{}
423 for i := len(txs) - 1; i >= 0; i-- {
424 tmp = append(tmp, txs[i])
425 }
426 txs = tmp
427 }
428 return txs, txs[0].TotalItems, nil
429}
430
431// GetTx returns a tx from the DB
432func (hdb *HistoryDB) GetTx(txID common.TxID) (*common.Tx, error) {
433 tx := new(common.Tx)
434 return tx, meddler.QueryRow(
435 hdb.db, tx,
436 "SELECT * FROM tx WHERE id = $1;",
437 txID,
438 )
439}
440
441// Close frees the resources used by HistoryDB
442func (hdb *HistoryDB) Close() error {
443 return hdb.db.Close()
444}