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// BlockData contains the information of a Block
30type BlockData struct {
31 block *common.Block
32 // Rollup
33 // L1UserTxs that were submitted in the block
34 L1UserTxs []common.L1Tx
35 Batches []BatchData
36 RegisteredTokens []common.Token
37 RollupVars *common.RollupVars
38 // Auction
39 Bids []common.Bid
40 Coordinators []common.Coordinator
41 AuctionVars *common.AuctionVars
42 // WithdrawalDelayer
43 // TODO: enable when common.WithdrawalDelayerVars is Merged from Synchronizer PR
44 // WithdrawalDelayerVars *common.WithdrawalDelayerVars
45}
46
47// BatchData contains the information of a Batch
48type BatchData struct {
49 // L1UserTxs that were forged in the batch
50 L1Batch bool // TODO: Remove once Batch.ForgeL1TxsNum is a pointer
51 L1UserTxs []common.L1Tx
52 L1CoordinatorTxs []common.L1Tx
53 L2Txs []common.L2Tx
54 CreatedAccounts []common.Account
55 ExitTree []common.ExitInfo
56 Batch *common.Batch
57}
58
59// NewHistoryDB initialize the DB
60func NewHistoryDB(port int, host, user, password, dbname string) (*HistoryDB, error) {
61 // Connect to DB
62 psqlconn := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", host, port, user, password, dbname)
63 hdb, err := sqlx.Connect("postgres", psqlconn)
64 if err != nil {
65 return nil, err
66 }
67 // Init meddler
68 db.InitMeddler()
69 meddler.Default = meddler.PostgreSQL
70
71 // Run DB migrations
72 migrations := &migrate.PackrMigrationSource{
73 Box: packr.New("history-migrations", "./migrations"),
74 }
75 nMigrations, err := migrate.Exec(hdb.DB, "postgres", migrations, migrate.Up)
76 if err != nil {
77 return nil, err
78 }
79 log.Debug("HistoryDB applied ", nMigrations, " migrations for ", dbname, " database")
80
81 return &HistoryDB{hdb}, nil
82}
83
84// AddBlock insert a block into the DB
85func (hdb *HistoryDB) AddBlock(block *common.Block) error { return hdb.addBlock(hdb.db, block) }
86func (hdb *HistoryDB) addBlock(d meddler.DB, block *common.Block) error {
87 return meddler.Insert(d, "block", block)
88}
89
90// AddBlocks inserts blocks into the DB
91func (hdb *HistoryDB) AddBlocks(blocks []common.Block) error {
92 return hdb.addBlocks(hdb.db, blocks)
93}
94
95func (hdb *HistoryDB) addBlocks(d meddler.DB, blocks []common.Block) error {
96 return db.BulkInsert(
97 d,
98 `INSERT INTO block (
99 eth_block_num,
100 timestamp,
101 hash
102 ) VALUES %s;`,
103 blocks[:],
104 )
105}
106
107// GetBlock retrieve a block from the DB, given a block number
108func (hdb *HistoryDB) GetBlock(blockNum int64) (*common.Block, error) {
109 block := &common.Block{}
110 err := meddler.QueryRow(
111 hdb.db, block,
112 "SELECT * FROM block WHERE eth_block_num = $1;", blockNum,
113 )
114 return block, err
115}
116
117// GetBlocks retrieve blocks from the DB, given a range of block numbers defined by from and to
118func (hdb *HistoryDB) GetBlocks(from, to int64) ([]*common.Block, error) {
119 var blocks []*common.Block
120 err := meddler.QueryAll(
121 hdb.db, &blocks,
122 "SELECT * FROM block WHERE $1 <= eth_block_num AND eth_block_num < $2;",
123 from, to,
124 )
125 return blocks, err
126}
127
128// GetLastBlock retrieve the block with the highest block number from the DB
129func (hdb *HistoryDB) GetLastBlock() (*common.Block, error) {
130 block := &common.Block{}
131 err := meddler.QueryRow(
132 hdb.db, block, "SELECT * FROM block ORDER BY eth_block_num DESC LIMIT 1;",
133 )
134 return block, err
135}
136
137// AddBatch insert a Batch into the DB
138func (hdb *HistoryDB) AddBatch(batch *common.Batch) error { return hdb.addBatch(hdb.db, batch) }
139func (hdb *HistoryDB) addBatch(d meddler.DB, batch *common.Batch) error {
140 return meddler.Insert(d, "batch", batch)
141}
142
143// AddBatches insert Bids into the DB
144func (hdb *HistoryDB) AddBatches(batches []common.Batch) error {
145 return hdb.addBatches(hdb.db, batches)
146}
147func (hdb *HistoryDB) addBatches(d meddler.DB, batches []common.Batch) error {
148 return db.BulkInsert(
149 d,
150 `INSERT INTO batch (
151 batch_num,
152 eth_block_num,
153 forger_addr,
154 fees_collected,
155 state_root,
156 num_accounts,
157 exit_root,
158 forge_l1_txs_num,
159 slot_num
160 ) VALUES %s;`,
161 batches[:],
162 )
163}
164
165// GetBatches retrieve batches from the DB, given a range of batch numbers defined by from and to
166func (hdb *HistoryDB) GetBatches(from, to common.BatchNum) ([]*common.Batch, error) {
167 var batches []*common.Batch
168 err := meddler.QueryAll(
169 hdb.db, &batches,
170 "SELECT * FROM batch WHERE $1 <= batch_num AND batch_num < $2;",
171 from, to,
172 )
173 return batches, err
174}
175
176// GetLastBatchNum returns the BatchNum of the latest forged batch
177func (hdb *HistoryDB) GetLastBatchNum() (common.BatchNum, error) {
178 row := hdb.db.QueryRow("SELECT batch_num FROM batch ORDER BY batch_num DESC LIMIT 1;")
179 var batchNum common.BatchNum
180 return batchNum, row.Scan(&batchNum)
181}
182
183// GetLastL1TxsNum returns the greatest ForgeL1TxsNum in the DB. If there's no
184// batch in the DB (nil, nil) is returned.
185func (hdb *HistoryDB) GetLastL1TxsNum() (*int64, error) {
186 row := hdb.db.QueryRow("SELECT MAX(forge_l1_txs_num) FROM batch;")
187 lastL1TxsNum := new(int64)
188 return lastL1TxsNum, row.Scan(&lastL1TxsNum)
189}
190
191// Reorg deletes all the information that was added into the DB after the
192// lastValidBlock. If lastValidBlock is negative, all block information is
193// deleted.
194func (hdb *HistoryDB) Reorg(lastValidBlock int64) error {
195 var err error
196 if lastValidBlock < 0 {
197 _, err = hdb.db.Exec("DELETE FROM block;")
198 } else {
199 _, err = hdb.db.Exec("DELETE FROM block WHERE eth_block_num > $1;", lastValidBlock)
200 }
201 return err
202}
203
204// SyncPoD stores all the data that can be changed / added on a block in the PoD SC
205func (hdb *HistoryDB) SyncPoD(
206 blockNum uint64,
207 bids []common.Bid,
208 coordinators []common.Coordinator,
209 vars *common.AuctionVars,
210) error {
211 return nil
212}
213
214// AddBids insert Bids into the DB
215func (hdb *HistoryDB) AddBids(bids []common.Bid) error { return hdb.addBids(hdb.db, bids) }
216func (hdb *HistoryDB) addBids(d meddler.DB, bids []common.Bid) error {
217 // TODO: check the coordinator info
218 return db.BulkInsert(
219 d,
220 "INSERT INTO bid (slot_num, forger_addr, bid_value, eth_block_num) VALUES %s;",
221 bids[:],
222 )
223}
224
225// GetBids return the bids
226func (hdb *HistoryDB) GetBids() ([]*common.Bid, error) {
227 var bids []*common.Bid
228 err := meddler.QueryAll(
229 hdb.db, &bids,
230 "SELECT * FROM bid;",
231 )
232 return bids, err
233}
234
235// AddCoordinators insert Coordinators into the DB
236func (hdb *HistoryDB) AddCoordinators(coordinators []common.Coordinator) error {
237 return hdb.addCoordinators(hdb.db, coordinators)
238}
239func (hdb *HistoryDB) addCoordinators(d meddler.DB, coordinators []common.Coordinator) error {
240 return db.BulkInsert(
241 d,
242 "INSERT INTO coordinator (forger_addr, eth_block_num, withdraw_addr, url) VALUES %s;",
243 coordinators[:],
244 )
245}
246
247// AddExitTree insert Exit tree into the DB
248func (hdb *HistoryDB) AddExitTree(exitTree []common.ExitInfo) error {
249 return hdb.addExitTree(hdb.db, exitTree)
250}
251func (hdb *HistoryDB) addExitTree(d meddler.DB, exitTree []common.ExitInfo) error {
252 return db.BulkInsert(
253 d,
254 "INSERT INTO exit_tree (batch_num, account_idx, merkle_proof, balance, "+
255 "instant_withdrawn, delayed_withdraw_request, delayed_withdrawn) VALUES %s;",
256 exitTree[:],
257 )
258}
259
260// AddToken insert a token into the DB
261func (hdb *HistoryDB) AddToken(token *common.Token) error {
262 return meddler.Insert(hdb.db, "token", token)
263}
264
265// AddTokens insert tokens into the DB
266func (hdb *HistoryDB) AddTokens(tokens []common.Token) error { return hdb.addTokens(hdb.db, tokens) }
267func (hdb *HistoryDB) addTokens(d meddler.DB, tokens []common.Token) error {
268 return db.BulkInsert(
269 d,
270 `INSERT INTO token (
271 token_id,
272 eth_block_num,
273 eth_addr,
274 name,
275 symbol,
276 decimals,
277 usd,
278 usd_update
279 ) VALUES %s;`,
280 tokens[:],
281 )
282}
283
284// UpdateTokenValue updates the USD value of a token
285func (hdb *HistoryDB) UpdateTokenValue(tokenID common.TokenID, value float64) error {
286 _, err := hdb.db.Exec(
287 "UPDATE token SET usd = $1 WHERE token_id = $2;",
288 value, tokenID,
289 )
290 return err
291}
292
293// GetTokens returns a list of tokens from the DB
294func (hdb *HistoryDB) GetTokens() ([]*common.Token, error) {
295 var tokens []*common.Token
296 err := meddler.QueryAll(
297 hdb.db, &tokens,
298 "SELECT * FROM token ORDER BY token_id;",
299 )
300 return tokens, err
301}
302
303// AddAccounts insert accounts into the DB
304func (hdb *HistoryDB) AddAccounts(accounts []common.Account) error {
305 return hdb.addAccounts(hdb.db, accounts)
306}
307func (hdb *HistoryDB) addAccounts(d meddler.DB, accounts []common.Account) error {
308 return db.BulkInsert(
309 d,
310 `INSERT INTO account (
311 idx,
312 token_id,
313 batch_num,
314 bjj,
315 eth_addr
316 ) VALUES %s;`,
317 accounts[:],
318 )
319}
320
321// GetAccounts returns a list of accounts from the DB
322func (hdb *HistoryDB) GetAccounts() ([]*common.Account, error) {
323 var accs []*common.Account
324 err := meddler.QueryAll(
325 hdb.db, &accs,
326 "SELECT * FROM account ORDER BY idx;",
327 )
328 return accs, err
329}
330
331// AddL1Txs inserts L1 txs to the DB
332func (hdb *HistoryDB) AddL1Txs(l1txs []common.L1Tx) error { return hdb.addL1Txs(hdb.db, l1txs) }
333func (hdb *HistoryDB) addL1Txs(d meddler.DB, l1txs []common.L1Tx) error {
334 txs := []common.Tx{}
335 for _, tx := range l1txs {
336 txs = append(txs, *(tx.Tx()))
337 }
338 return hdb.addTxs(d, txs)
339}
340
341// AddL2Txs inserts L2 txs to the DB
342func (hdb *HistoryDB) AddL2Txs(l2txs []common.L2Tx) error { return hdb.addL2Txs(hdb.db, l2txs) }
343func (hdb *HistoryDB) addL2Txs(d meddler.DB, l2txs []common.L2Tx) error {
344 txs := []common.Tx{}
345 for _, tx := range l2txs {
346 txs = append(txs, *(tx.Tx()))
347 }
348 return hdb.addTxs(d, txs)
349}
350
351// AddTxs insert L1 txs into the DB
352func (hdb *HistoryDB) AddTxs(txs []common.Tx) error { return hdb.addTxs(hdb.db, txs) }
353func (hdb *HistoryDB) addTxs(d meddler.DB, txs []common.Tx) error {
354 return db.BulkInsert(
355 d,
356 `INSERT INTO tx (
357 is_l1,
358 id,
359 type,
360 position,
361 from_idx,
362 to_idx,
363 amount,
364 amount_f,
365 token_id,
366 amount_usd,
367 batch_num,
368 eth_block_num,
369 to_forge_l1_txs_num,
370 user_origin,
371 from_eth_addr,
372 from_bjj,
373 load_amount,
374 load_amount_f,
375 load_amount_usd,
376 fee,
377 fee_usd,
378 nonce
379 ) VALUES %s;`,
380 txs[:],
381 )
382}
383
384// SetBatchNumL1UserTxs sets the batchNum in all the L1UserTxs with toForgeL1TxsNum.
385func (hdb *HistoryDB) SetBatchNumL1UserTxs(toForgeL1TxsNum, batchNum int64) error {
386 return hdb.setBatchNumL1UserTxs(hdb.db, toForgeL1TxsNum, batchNum)
387}
388func (hdb *HistoryDB) setBatchNumL1UserTxs(d meddler.DB, toForgeL1TxsNum, batchNum int64) error {
389 _, err := d.Exec("UPDATE tx SET batch_num = $1 WHERE to_forge_l1_txs_num = $2 AND is_l1 = TRUE AND user_origin = TRUE;",
390 batchNum, toForgeL1TxsNum)
391 return err
392}
393
394// GetTxs returns a list of txs from the DB
395func (hdb *HistoryDB) GetTxs() ([]*common.Tx, error) {
396 var txs []*common.Tx
397 err := meddler.QueryAll(
398 hdb.db, &txs,
399 `SELECT * FROM tx
400 ORDER BY (batch_num, position) ASC`,
401 )
402 return txs, err
403}
404
405// GetHistoryTxs returns a list of txs from the DB using the HistoryTx struct
406func (hdb *HistoryDB) GetHistoryTxs(
407 ethAddr *ethCommon.Address, bjj *babyjub.PublicKey,
408 tokenID, idx, batchNum *uint, txType *common.TxType,
409 offset, limit *uint, last bool,
410) ([]*HistoryTx, int, error) {
411 if ethAddr != nil && bjj != nil {
412 return nil, 0, errors.New("ethAddr and bjj are incompatible")
413 }
414 var query string
415 var args []interface{}
416 queryStr := `SELECT tx.*, tx.amount_f * token.usd AS current_usd,
417 token.symbol, token.usd_update, block.timestamp, count(*) OVER() AS total_items FROM tx
418 INNER JOIN token ON tx.token_id = token.token_id
419 INNER JOIN block ON tx.eth_block_num = block.eth_block_num `
420 // Apply filters
421 nextIsAnd := false
422 // ethAddr filter
423 if ethAddr != nil {
424 queryStr = `WITH acc AS
425 (select idx from account where eth_addr = ?) ` + queryStr
426 queryStr += ", acc WHERE (tx.from_idx IN(acc.idx) OR tx.to_idx IN(acc.idx)) "
427 nextIsAnd = true
428 args = append(args, ethAddr)
429 } else if bjj != nil { // bjj filter
430 queryStr = `WITH acc AS
431 (select idx from account where bjj = ?) ` + queryStr
432 queryStr += ", acc WHERE (tx.from_idx IN(acc.idx) OR tx.to_idx IN(acc.idx)) "
433 nextIsAnd = true
434 args = append(args, bjj)
435 }
436 // tokenID filter
437 if tokenID != nil {
438 if nextIsAnd {
439 queryStr += "AND "
440 } else {
441 queryStr += "WHERE "
442 }
443 queryStr += "tx.token_id = ? "
444 args = append(args, tokenID)
445 nextIsAnd = true
446 }
447 // idx filter
448 if idx != nil {
449 if nextIsAnd {
450 queryStr += "AND "
451 } else {
452 queryStr += "WHERE "
453 }
454 queryStr += "(tx.from_idx = ? OR tx.to_idx = ?) "
455 args = append(args, idx, idx)
456 nextIsAnd = true
457 }
458 // batchNum filter
459 if batchNum != nil {
460 if nextIsAnd {
461 queryStr += "AND "
462 } else {
463 queryStr += "WHERE "
464 }
465 queryStr += "tx.batch_num = ? "
466 args = append(args, batchNum)
467 nextIsAnd = true
468 }
469 // txType filter
470 if txType != nil {
471 if nextIsAnd {
472 queryStr += "AND "
473 } else {
474 queryStr += "WHERE "
475 }
476 queryStr += "tx.type = ? "
477 args = append(args, txType)
478 // nextIsAnd = true
479 }
480 // pagination
481 if last {
482 queryStr += "ORDER BY (batch_num, position) DESC NULLS FIRST "
483 } else {
484 queryStr += "ORDER BY (batch_num, position) ASC NULLS LAST "
485 queryStr += fmt.Sprintf("OFFSET %d ", *offset)
486 }
487 queryStr += fmt.Sprintf("LIMIT %d;", *limit)
488 query = hdb.db.Rebind(queryStr)
489 // log.Debug(query)
490 txs := []*HistoryTx{}
491 if err := meddler.QueryAll(hdb.db, &txs, query, args...); err != nil {
492 return nil, 0, err
493 }
494 if len(txs) == 0 {
495 return nil, 0, sql.ErrNoRows
496 } else if last {
497 tmp := []*HistoryTx{}
498 for i := len(txs) - 1; i >= 0; i-- {
499 tmp = append(tmp, txs[i])
500 }
501 txs = tmp
502 }
503 return txs, txs[0].TotalItems, nil
504}
505
506// GetTx returns a tx from the DB
507func (hdb *HistoryDB) GetTx(txID common.TxID) (*common.Tx, error) {
508 tx := new(common.Tx)
509 return tx, meddler.QueryRow(
510 hdb.db, tx,
511 "SELECT * FROM tx WHERE id = $1;",
512 txID,
513 )
514}
515
516// GetL1UserTxs gets L1 User Txs to be forged in a batch that will create an account
517// TODO: This is currently not used. Figure out if it should be used somewhere or removed.
518func (hdb *HistoryDB) GetL1UserTxs(toForgeL1TxsNum int64) ([]*common.Tx, error) {
519 var txs []*common.Tx
520 err := meddler.QueryAll(
521 hdb.db, &txs,
522 "SELECT * FROM tx WHERE to_forge_l1_txs_num = $1 AND is_l1 = TRUE AND user_origin = TRUE;",
523 toForgeL1TxsNum,
524 )
525 return txs, err
526}
527
528// TODO: Think about chaning all the queries that return a last value, to queries that return the next valid value.
529
530// GetLastTxsPosition for a given to_forge_l1_txs_num
531func (hdb *HistoryDB) GetLastTxsPosition(toForgeL1TxsNum int64) (int, error) {
532 row := hdb.db.QueryRow("SELECT MAX(position) FROM tx WHERE to_forge_l1_txs_num = $1;", toForgeL1TxsNum)
533 var lastL1TxsPosition int
534 return lastL1TxsPosition, row.Scan(&lastL1TxsPosition)
535}
536
537// AddBlockSCData stores all the information of a block retrieved by the Synchronizer
538func (hdb *HistoryDB) AddBlockSCData(blockData *BlockData) (err error) {
539 txn, err := hdb.db.Begin()
540 if err != nil {
541 return err
542 }
543 defer func() {
544 if err != nil {
545 err = txn.Rollback()
546 }
547 }()
548
549 // Add block
550 err = hdb.addBlock(txn, blockData.block)
551 if err != nil {
552 return err
553 }
554
555 // Add l1 Txs
556 if len(blockData.L1UserTxs) > 0 {
557 err = hdb.addL1Txs(txn, blockData.L1UserTxs)
558 if err != nil {
559 return err
560 }
561 }
562
563 // Add Tokens
564 if len(blockData.RegisteredTokens) > 0 {
565 err = hdb.addTokens(txn, blockData.RegisteredTokens)
566 if err != nil {
567 return err
568 }
569 }
570
571 // Add Bids
572 if len(blockData.Bids) > 0 {
573 err = hdb.addBids(txn, blockData.Bids)
574 if err != nil {
575 return err
576 }
577 }
578
579 // Add Coordinators
580 if len(blockData.Coordinators) > 0 {
581 err = hdb.addCoordinators(txn, blockData.Coordinators)
582 if err != nil {
583 return err
584 }
585 }
586
587 // Add Batches
588 for _, batch := range blockData.Batches {
589 if batch.L1Batch {
590 err = hdb.setBatchNumL1UserTxs(txn, batch.Batch.ForgeL1TxsNum, int64(batch.Batch.BatchNum))
591 if err != nil {
592 return err
593 }
594 if len(batch.L1CoordinatorTxs) > 0 {
595 err = hdb.addL1Txs(txn, batch.L1CoordinatorTxs)
596 if err != nil {
597 return err
598 }
599 }
600 }
601
602 // Add l2 Txs
603 if len(batch.L2Txs) > 0 {
604 err = hdb.addL2Txs(txn, batch.L2Txs)
605 if err != nil {
606 return err
607 }
608 }
609
610 // Add accounts
611 if len(batch.CreatedAccounts) > 0 {
612 err = hdb.addAccounts(txn, batch.CreatedAccounts)
613 if err != nil {
614 return err
615 }
616 }
617
618 // Add exit tree
619 if len(batch.ExitTree) > 0 {
620 err = hdb.addExitTree(txn, batch.ExitTree)
621 if err != nil {
622 return err
623 }
624 }
625
626 // Add Batch
627 err = hdb.addBatch(txn, batch.Batch)
628 if err != nil {
629 return err
630 }
631
632 // TODO: INSERT CONTRACTS VARS
633 }
634
635 return txn.Commit()
636}
637
638// Close frees the resources used by HistoryDB
639func (hdb *HistoryDB) Close() error {
640 return hdb.db.Close()
641}