hermez-node/db/historydb/historydb.go
db/historydb/historydb.goRaw
1package historydb
2
3import (
4 "database/sql"
5 "errors"
6 "fmt"
7 "math/big"
8
9 ethCommon "github.com/ethereum/go-ethereum/common"
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 "github.com/russross/meddler"
19)
20
21const (
22 // OrderAsc indicates ascending order when using pagination
23 OrderAsc = "ASC"
24 // OrderDesc indicates descending order when using pagination
25 OrderDesc = "DESC"
26)
27
28// TODO(Edu): Document here how HistoryDB is kept consistent
29
30// HistoryDB persist the historic of the rollup
31type HistoryDB struct {
32 db *sqlx.DB
33}
34
35// BlockData contains the information of a Block
36type BlockData struct {
37 Block *common.Block
38 // Rollup
39 // L1UserTxs that were submitted in the block
40 L1UserTxs []common.L1Tx
41 Batches []BatchData
42 RegisteredTokens []common.Token
43 RollupVars *common.RollupVars
44 // Auction
45 Bids []common.Bid
46 Coordinators []common.Coordinator
47 AuctionVars *common.AuctionVars
48 WithdrawDelayerVars *common.WithdrawDelayerVars
49 // TODO: enable when common.WithdrawalDelayerVars is Merged from Synchronizer PR
50 // WithdrawalDelayerVars *common.WithdrawalDelayerVars
51}
52
53// BatchData contains the information of a Batch
54type BatchData struct {
55 // L1UserTxs that were forged in the batch
56 L1Batch bool // TODO: Remove once Batch.ForgeL1TxsNum is a pointer
57 L1UserTxs []common.L1Tx
58 L1CoordinatorTxs []common.L1Tx
59 L2Txs []common.L2Tx
60 CreatedAccounts []common.Account
61 ExitTree []common.ExitInfo
62 Batch *common.Batch
63}
64
65// NewBatchData creates an empty BatchData with the slices initialized.
66func NewBatchData() *BatchData {
67 return &BatchData{
68 L1Batch: false,
69 L1UserTxs: make([]common.L1Tx, 0),
70 L1CoordinatorTxs: make([]common.L1Tx, 0),
71 L2Txs: make([]common.L2Tx, 0),
72 CreatedAccounts: make([]common.Account, 0),
73 ExitTree: make([]common.ExitInfo, 0),
74 Batch: &common.Batch{},
75 }
76}
77
78// NewHistoryDB initialize the DB
79func NewHistoryDB(db *sqlx.DB) *HistoryDB {
80 return &HistoryDB{db: db}
81}
82
83// AddBlock insert a block into the DB
84func (hdb *HistoryDB) AddBlock(block *common.Block) error { return hdb.addBlock(hdb.db, block) }
85func (hdb *HistoryDB) addBlock(d meddler.DB, block *common.Block) error {
86 return meddler.Insert(d, "block", block)
87}
88
89// AddBlocks inserts blocks into the DB
90func (hdb *HistoryDB) AddBlocks(blocks []common.Block) error {
91 return hdb.addBlocks(hdb.db, blocks)
92}
93
94func (hdb *HistoryDB) addBlocks(d meddler.DB, blocks []common.Block) error {
95 return db.BulkInsert(
96 d,
97 `INSERT INTO block (
98 eth_block_num,
99 timestamp,
100 hash
101 ) VALUES %s;`,
102 blocks[:],
103 )
104}
105
106// GetBlock retrieve a block from the DB, given a block number
107func (hdb *HistoryDB) GetBlock(blockNum int64) (*common.Block, error) {
108 block := &common.Block{}
109 err := meddler.QueryRow(
110 hdb.db, block,
111 "SELECT * FROM block WHERE eth_block_num = $1;", blockNum,
112 )
113 return block, err
114}
115
116// GetBlocks retrieve blocks from the DB, given a range of block numbers defined by from and to
117func (hdb *HistoryDB) GetBlocks(from, to int64) ([]common.Block, error) {
118 var blocks []*common.Block
119 err := meddler.QueryAll(
120 hdb.db, &blocks,
121 "SELECT * FROM block WHERE $1 <= eth_block_num AND eth_block_num < $2;",
122 from, to,
123 )
124 return db.SlicePtrsToSlice(blocks).([]common.Block), err
125}
126
127// GetLastBlock retrieve the block with the highest block number from the DB
128func (hdb *HistoryDB) GetLastBlock() (*common.Block, error) {
129 block := &common.Block{}
130 err := meddler.QueryRow(
131 hdb.db, block, "SELECT * FROM block ORDER BY eth_block_num DESC LIMIT 1;",
132 )
133 return block, err
134}
135
136// AddBatch insert a Batch into the DB
137func (hdb *HistoryDB) AddBatch(batch *common.Batch) error { return hdb.addBatch(hdb.db, batch) }
138func (hdb *HistoryDB) addBatch(d meddler.DB, batch *common.Batch) error {
139 return meddler.Insert(d, "batch", batch)
140}
141
142// AddBatches insert Bids into the DB
143func (hdb *HistoryDB) AddBatches(batches []common.Batch) error {
144 return hdb.addBatches(hdb.db, batches)
145}
146func (hdb *HistoryDB) addBatches(d meddler.DB, batches []common.Batch) error {
147 // TODO: Calculate and insert total_fees_usd
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 db.SlicePtrsToSlice(batches).([]common.Batch), 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, bid_value, eth_block_num, bidder_addr) 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 db.SlicePtrsToSlice(bids).([]common.Bid), 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 (bidder_addr, forger_addr, eth_block_num, 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 ) VALUES %s;`,
278 tokens[:],
279 )
280}
281
282// UpdateTokenValue updates the USD value of a token
283func (hdb *HistoryDB) UpdateTokenValue(tokenSymbol string, value float64) error {
284 _, err := hdb.db.Exec(
285 "UPDATE token SET usd = $1 WHERE symbol = $2;",
286 value, tokenSymbol,
287 )
288 return err
289}
290
291// GetTokens returns a list of tokens from the DB
292func (hdb *HistoryDB) GetTokens() ([]TokenRead, error) {
293 var tokens []*TokenRead
294 err := meddler.QueryAll(
295 hdb.db, &tokens,
296 "SELECT * FROM token ORDER BY token_id;",
297 )
298 return db.SlicePtrsToSlice(tokens).([]TokenRead), err
299}
300
301// GetTokenSymbols returns all the token symbols from the DB
302func (hdb *HistoryDB) GetTokenSymbols() ([]string, error) {
303 var tokenSymbols []string
304 rows, err := hdb.db.Query("SELECT symbol FROM token;")
305 if err != nil {
306 return nil, err
307 }
308 sym := new(string)
309 for rows.Next() {
310 err = rows.Scan(sym)
311 if err != nil {
312 return nil, err
313 }
314 tokenSymbols = append(tokenSymbols, *sym)
315 }
316 return tokenSymbols, nil
317}
318
319// AddAccounts insert accounts into the DB
320func (hdb *HistoryDB) AddAccounts(accounts []common.Account) error {
321 return hdb.addAccounts(hdb.db, accounts)
322}
323func (hdb *HistoryDB) addAccounts(d meddler.DB, accounts []common.Account) error {
324 return db.BulkInsert(
325 d,
326 `INSERT INTO account (
327 idx,
328 token_id,
329 batch_num,
330 bjj,
331 eth_addr
332 ) VALUES %s;`,
333 accounts[:],
334 )
335}
336
337// GetAccounts returns a list of accounts from the DB
338func (hdb *HistoryDB) GetAccounts() ([]common.Account, error) {
339 var accs []*common.Account
340 err := meddler.QueryAll(
341 hdb.db, &accs,
342 "SELECT * FROM account ORDER BY idx;",
343 )
344 return db.SlicePtrsToSlice(accs).([]common.Account), err
345}
346
347// AddL1Txs inserts L1 txs to the DB. USD and LoadAmountUSD will be set automatically before storing the tx.
348// If the tx is originated by a coordinator, BatchNum must be provided. If it's originated by a user,
349// BatchNum should be null, and the value will be setted by a trigger when a batch forges the tx.
350func (hdb *HistoryDB) AddL1Txs(l1txs []common.L1Tx) error { return hdb.addL1Txs(hdb.db, l1txs) }
351
352// addL1Txs inserts L1 txs to the DB. USD and LoadAmountUSD will be set automatically before storing the tx.
353// If the tx is originated by a coordinator, BatchNum must be provided. If it's originated by a user,
354// BatchNum should be null, and the value will be setted by a trigger when a batch forges the tx.
355func (hdb *HistoryDB) addL1Txs(d meddler.DB, l1txs []common.L1Tx) error {
356 txs := []txWrite{}
357 for i := 0; i < len(l1txs); i++ {
358 af := new(big.Float).SetInt(l1txs[i].Amount)
359 amountFloat, _ := af.Float64()
360 laf := new(big.Float).SetInt(l1txs[i].LoadAmount)
361 loadAmountFloat, _ := laf.Float64()
362 txs = append(txs, txWrite{
363 // Generic
364 IsL1: true,
365 TxID: l1txs[i].TxID,
366 Type: l1txs[i].Type,
367 Position: l1txs[i].Position,
368 FromIdx: &l1txs[i].FromIdx,
369 ToIdx: l1txs[i].ToIdx,
370 Amount: l1txs[i].Amount,
371 AmountFloat: amountFloat,
372 TokenID: l1txs[i].TokenID,
373 BatchNum: l1txs[i].BatchNum,
374 EthBlockNum: l1txs[i].EthBlockNum,
375 // L1
376 ToForgeL1TxsNum: l1txs[i].ToForgeL1TxsNum,
377 UserOrigin: &l1txs[i].UserOrigin,
378 FromEthAddr: &l1txs[i].FromEthAddr,
379 FromBJJ: l1txs[i].FromBJJ,
380 LoadAmount: l1txs[i].LoadAmount,
381 LoadAmountFloat: &loadAmountFloat,
382 })
383 }
384 return hdb.addTxs(d, txs)
385}
386
387// AddL2Txs inserts L2 txs to the DB. TokenID, USD and FeeUSD will be set automatically before storing the tx.
388func (hdb *HistoryDB) AddL2Txs(l2txs []common.L2Tx) error { return hdb.addL2Txs(hdb.db, l2txs) }
389
390// addL2Txs inserts L2 txs to the DB. TokenID, USD and FeeUSD will be set automatically before storing the tx.
391func (hdb *HistoryDB) addL2Txs(d meddler.DB, l2txs []common.L2Tx) error {
392 txs := []txWrite{}
393 for i := 0; i < len(l2txs); i++ {
394 f := new(big.Float).SetInt(l2txs[i].Amount)
395 amountFloat, _ := f.Float64()
396 txs = append(txs, txWrite{
397 // Generic
398 IsL1: false,
399 TxID: l2txs[i].TxID,
400 Type: l2txs[i].Type,
401 Position: l2txs[i].Position,
402 FromIdx: &l2txs[i].FromIdx,
403 ToIdx: l2txs[i].ToIdx,
404 Amount: l2txs[i].Amount,
405 AmountFloat: amountFloat,
406 BatchNum: &l2txs[i].BatchNum,
407 EthBlockNum: l2txs[i].EthBlockNum,
408 // L2
409 Fee: &l2txs[i].Fee,
410 Nonce: &l2txs[i].Nonce,
411 })
412 }
413 return hdb.addTxs(d, txs)
414}
415
416func (hdb *HistoryDB) addTxs(d meddler.DB, txs []txWrite) error {
417 return db.BulkInsert(
418 d,
419 `INSERT INTO tx (
420 is_l1,
421 id,
422 type,
423 position,
424 from_idx,
425 to_idx,
426 amount,
427 amount_f,
428 token_id,
429 batch_num,
430 eth_block_num,
431 to_forge_l1_txs_num,
432 user_origin,
433 from_eth_addr,
434 from_bjj,
435 load_amount,
436 load_amount_f,
437 fee,
438 nonce
439 ) VALUES %s;`,
440 txs[:],
441 )
442}
443
444// // GetTxs returns a list of txs from the DB
445// func (hdb *HistoryDB) GetTxs() ([]common.Tx, error) {
446// var txs []*common.Tx
447// err := meddler.QueryAll(
448// hdb.db, &txs,
449// `SELECT * FROM tx
450// ORDER BY (batch_num, position) ASC`,
451// )
452// return db.SlicePtrsToSlice(txs).([]common.Tx), err
453// }
454
455// GetHistoryTx returns a tx from the DB given a TxID
456func (hdb *HistoryDB) GetHistoryTx(txID common.TxID) (*HistoryTx, error) {
457 tx := &HistoryTx{}
458 err := meddler.QueryRow(
459 hdb.db, tx, `SELECT tx.item_id, tx.is_l1, tx.id, tx.type, tx.position,
460 tx.from_idx, tx.to_idx, tx.amount, tx.token_id, tx.amount_usd,
461 tx.batch_num, tx.eth_block_num, tx.to_forge_l1_txs_num, tx.user_origin,
462 tx.from_eth_addr, tx.from_bjj, tx.load_amount,
463 tx.load_amount_usd, tx.fee, tx.fee_usd, tx.nonce,
464 token.token_id, token.eth_block_num AS token_block,
465 token.eth_addr, token.name, token.symbol, token.decimals, token.usd,
466 token.usd_update, block.timestamp
467 FROM tx INNER JOIN token ON tx.token_id = token.token_id
468 INNER JOIN block ON tx.eth_block_num = block.eth_block_num
469 WHERE tx.id = $1;`, txID,
470 )
471 return tx, err
472}
473
474// GetHistoryTxs returns a list of txs from the DB using the HistoryTx struct
475// and pagination info
476func (hdb *HistoryDB) GetHistoryTxs(
477 ethAddr *ethCommon.Address, bjj *babyjub.PublicKey,
478 tokenID *common.TokenID, idx *common.Idx, batchNum *uint, txType *common.TxType,
479 fromItem, limit *uint, order string,
480) ([]HistoryTx, *db.Pagination, error) {
481 if ethAddr != nil && bjj != nil {
482 return nil, nil, errors.New("ethAddr and bjj are incompatible")
483 }
484 var query string
485 var args []interface{}
486 queryStr := `SELECT tx.item_id, tx.is_l1, tx.id, tx.type, tx.position,
487 tx.from_idx, tx.to_idx, tx.amount, tx.token_id, tx.amount_usd,
488 tx.batch_num, tx.eth_block_num, tx.to_forge_l1_txs_num, tx.user_origin,
489 tx.from_eth_addr, tx.from_bjj, tx.load_amount,
490 tx.load_amount_usd, tx.fee, tx.fee_usd, tx.nonce,
491 token.token_id, token.eth_block_num AS token_block,
492 token.eth_addr, token.name, token.symbol, token.decimals, token.usd,
493 token.usd_update, block.timestamp, count(*) OVER() AS total_items,
494 MIN(tx.item_id) OVER() AS first_item, MAX(tx.item_id) OVER() AS last_item
495 FROM tx INNER JOIN token ON tx.token_id = token.token_id
496 INNER JOIN block ON tx.eth_block_num = block.eth_block_num `
497 // Apply filters
498 nextIsAnd := false
499 // ethAddr filter
500 if ethAddr != nil {
501 queryStr = `WITH acc AS
502 (select idx from account where eth_addr = ?) ` + queryStr
503 queryStr += ", acc WHERE (tx.from_idx IN(acc.idx) OR tx.to_idx IN(acc.idx)) "
504 nextIsAnd = true
505 args = append(args, ethAddr)
506 } else if bjj != nil { // bjj filter
507 queryStr = `WITH acc AS
508 (select idx from account where bjj = ?) ` + queryStr
509 queryStr += ", acc WHERE (tx.from_idx IN(acc.idx) OR tx.to_idx IN(acc.idx)) "
510 nextIsAnd = true
511 args = append(args, bjj)
512 }
513 // tokenID filter
514 if tokenID != nil {
515 if nextIsAnd {
516 queryStr += "AND "
517 } else {
518 queryStr += "WHERE "
519 }
520 queryStr += "tx.token_id = ? "
521 args = append(args, tokenID)
522 nextIsAnd = true
523 }
524 // idx filter
525 if idx != nil {
526 if nextIsAnd {
527 queryStr += "AND "
528 } else {
529 queryStr += "WHERE "
530 }
531 queryStr += "(tx.from_idx = ? OR tx.to_idx = ?) "
532 args = append(args, idx, idx)
533 nextIsAnd = true
534 }
535 // batchNum filter
536 if batchNum != nil {
537 if nextIsAnd {
538 queryStr += "AND "
539 } else {
540 queryStr += "WHERE "
541 }
542 queryStr += "tx.batch_num = ? "
543 args = append(args, batchNum)
544 nextIsAnd = true
545 }
546 // txType filter
547 if txType != nil {
548 if nextIsAnd {
549 queryStr += "AND "
550 } else {
551 queryStr += "WHERE "
552 }
553 queryStr += "tx.type = ? "
554 args = append(args, txType)
555 nextIsAnd = true
556 }
557 if fromItem != nil {
558 if nextIsAnd {
559 queryStr += "AND "
560 } else {
561 queryStr += "WHERE "
562 }
563 if order == OrderAsc {
564 queryStr += "tx.item_id >= ? "
565 } else {
566 queryStr += "tx.item_id <= ? "
567 }
568 args = append(args, fromItem)
569 nextIsAnd = true
570 }
571 if nextIsAnd {
572 queryStr += "AND "
573 } else {
574 queryStr += "WHERE "
575 }
576 queryStr += "tx.batch_num IS NOT NULL "
577
578 // pagination
579 queryStr += "ORDER BY tx.item_id "
580 if order == OrderAsc {
581 queryStr += " ASC "
582 } else {
583 queryStr += " DESC "
584 }
585 queryStr += fmt.Sprintf("LIMIT %d;", *limit)
586 query = hdb.db.Rebind(queryStr)
587 log.Debug(query)
588 txsPtrs := []*HistoryTx{}
589 if err := meddler.QueryAll(hdb.db, &txsPtrs, query, args...); err != nil {
590 return nil, nil, err
591 }
592 txs := db.SlicePtrsToSlice(txsPtrs).([]HistoryTx)
593 if len(txs) == 0 {
594 return nil, nil, sql.ErrNoRows
595 }
596 return txs, &db.Pagination{
597 TotalItems: txs[0].TotalItems,
598 FirstItem: txs[0].FirstItem,
599 LastItem: txs[0].LastItem,
600 }, nil
601}
602
603// GetExit returns a exit from the DB
604func (hdb *HistoryDB) GetExit(batchNum *uint, idx *common.Idx) (*HistoryExit, error) {
605 exit := &HistoryExit{}
606 err := meddler.QueryRow(
607 hdb.db, exit, `SELECT exit_tree.*, token.token_id, token.eth_block_num AS token_block,
608 token.eth_addr, token.name, token.symbol, token.decimals, token.usd, token.usd_update
609 FROM exit_tree INNER JOIN account ON exit_tree.account_idx = account.idx
610 INNER JOIN token ON account.token_id = token.token_id
611 WHERE exit_tree.batch_num = $1 AND exit_tree.account_idx = $2;`, batchNum, idx,
612 )
613 return exit, err
614}
615
616// GetExits returns a list of exits from the DB and pagination info
617func (hdb *HistoryDB) GetExits(
618 ethAddr *ethCommon.Address, bjj *babyjub.PublicKey,
619 tokenID *common.TokenID, idx *common.Idx, batchNum *uint,
620 fromItem, limit *uint, order string,
621) ([]HistoryExit, *db.Pagination, error) {
622 if ethAddr != nil && bjj != nil {
623 return nil, nil, errors.New("ethAddr and bjj are incompatible")
624 }
625 var query string
626 var args []interface{}
627 queryStr := `SELECT exit_tree.*, token.token_id, token.eth_block_num AS token_block,
628 token.eth_addr, token.name, token.symbol, token.decimals, token.usd,
629 token.usd_update, COUNT(*) OVER() AS total_items, MIN(exit_tree.item_id) OVER() AS first_item, MAX(exit_tree.item_id) OVER() AS last_item
630 FROM exit_tree INNER JOIN account ON exit_tree.account_idx = account.idx
631 INNER JOIN token ON account.token_id = token.token_id `
632 // Apply filters
633 nextIsAnd := false
634 // ethAddr filter
635 if ethAddr != nil {
636 queryStr += "WHERE account.eth_addr = ? "
637 nextIsAnd = true
638 args = append(args, ethAddr)
639 } else if bjj != nil { // bjj filter
640 queryStr += "WHERE account.bjj = ? "
641 nextIsAnd = true
642 args = append(args, bjj)
643 }
644 // tokenID filter
645 if tokenID != nil {
646 if nextIsAnd {
647 queryStr += "AND "
648 } else {
649 queryStr += "WHERE "
650 }
651 queryStr += "account.token_id = ? "
652 args = append(args, tokenID)
653 nextIsAnd = true
654 }
655 // idx filter
656 if idx != nil {
657 if nextIsAnd {
658 queryStr += "AND "
659 } else {
660 queryStr += "WHERE "
661 }
662 queryStr += "exit_tree.account_idx = ? "
663 args = append(args, idx)
664 nextIsAnd = true
665 }
666 // batchNum filter
667 if batchNum != nil {
668 if nextIsAnd {
669 queryStr += "AND "
670 } else {
671 queryStr += "WHERE "
672 }
673 queryStr += "exit_tree.batch_num = ? "
674 args = append(args, batchNum)
675 nextIsAnd = true
676 }
677 if fromItem != nil {
678 if nextIsAnd {
679 queryStr += "AND "
680 } else {
681 queryStr += "WHERE "
682 }
683 if order == OrderAsc {
684 queryStr += "exit_tree.item_id >= ? "
685 } else {
686 queryStr += "exit_tree.item_id <= ? "
687 }
688 args = append(args, fromItem)
689 // nextIsAnd = true
690 }
691 // pagination
692 queryStr += "ORDER BY exit_tree.item_id "
693 if order == OrderAsc {
694 queryStr += " ASC "
695 } else {
696 queryStr += " DESC "
697 }
698 queryStr += fmt.Sprintf("LIMIT %d;", *limit)
699 query = hdb.db.Rebind(queryStr)
700 // log.Debug(query)
701 exits := []*HistoryExit{}
702 if err := meddler.QueryAll(hdb.db, &exits, query, args...); err != nil {
703 return nil, nil, err
704 }
705 if len(exits) == 0 {
706 return nil, nil, sql.ErrNoRows
707 }
708 return db.SlicePtrsToSlice(exits).([]HistoryExit), &db.Pagination{
709 TotalItems: exits[0].TotalItems,
710 FirstItem: exits[0].FirstItem,
711 LastItem: exits[0].LastItem,
712 }, nil
713}
714
715// // GetTx returns a tx from the DB
716// func (hdb *HistoryDB) GetTx(txID common.TxID) (*common.Tx, error) {
717// tx := new(common.Tx)
718// return tx, meddler.QueryRow(
719// hdb.db, tx,
720// "SELECT * FROM tx WHERE id = $1;",
721// txID,
722// )
723// }
724
725// // GetL1UserTxs gets L1 User Txs to be forged in a batch that will create an account
726// // TODO: This is currently not used. Figure out if it should be used somewhere or removed.
727// func (hdb *HistoryDB) GetL1UserTxs(toForgeL1TxsNum int64) ([]*common.Tx, error) {
728// var txs []*common.Tx
729// err := meddler.QueryAll(
730// hdb.db, &txs,
731// "SELECT * FROM tx WHERE to_forge_l1_txs_num = $1 AND is_l1 = TRUE AND user_origin = TRUE;",
732// toForgeL1TxsNum,
733// )
734// return txs, err
735// }
736
737// TODO: Think about chaning all the queries that return a last value, to queries that return the next valid value.
738
739// GetLastTxsPosition for a given to_forge_l1_txs_num
740func (hdb *HistoryDB) GetLastTxsPosition(toForgeL1TxsNum int64) (int, error) {
741 row := hdb.db.QueryRow("SELECT MAX(position) FROM tx WHERE to_forge_l1_txs_num = $1;", toForgeL1TxsNum)
742 var lastL1TxsPosition int
743 return lastL1TxsPosition, row.Scan(&lastL1TxsPosition)
744}
745
746// AddBlockSCData stores all the information of a block retrieved by the Synchronizer
747func (hdb *HistoryDB) AddBlockSCData(blockData *BlockData) (err error) {
748 txn, err := hdb.db.Begin()
749 if err != nil {
750 return err
751 }
752 defer func() {
753 if err != nil {
754 errRollback := txn.Rollback()
755 if errRollback != nil {
756 log.Errorw("Rollback", "err", errRollback)
757 }
758 }
759 }()
760
761 // Add block
762 err = hdb.addBlock(txn, blockData.Block)
763 if err != nil {
764 return err
765 }
766
767 // Add Coordinators
768 if len(blockData.Coordinators) > 0 {
769 err = hdb.addCoordinators(txn, blockData.Coordinators)
770 if err != nil {
771 return err
772 }
773 }
774
775 // Add Bids
776 if len(blockData.Bids) > 0 {
777 err = hdb.addBids(txn, blockData.Bids)
778 if err != nil {
779 return err
780 }
781 }
782
783 // Add Tokens
784 if len(blockData.RegisteredTokens) > 0 {
785 err = hdb.addTokens(txn, blockData.RegisteredTokens)
786 if err != nil {
787 return err
788 }
789 }
790
791 // Add l1 Txs
792 if len(blockData.L1UserTxs) > 0 {
793 err = hdb.addL1Txs(txn, blockData.L1UserTxs)
794 if err != nil {
795 return err
796 }
797 }
798
799 // Add Batches
800 for _, batch := range blockData.Batches {
801 // Add Batch: this will trigger an update on the DB
802 // that will set the batch num of forged L1 txs in this batch
803 err = hdb.addBatch(txn, batch.Batch)
804 if err != nil {
805 return err
806 }
807
808 // Add unforged l1 Txs
809 if batch.L1Batch {
810 if len(batch.L1CoordinatorTxs) > 0 {
811 err = hdb.addL1Txs(txn, batch.L1CoordinatorTxs)
812 if err != nil {
813 return err
814 }
815 }
816 }
817
818 // Add l2 Txs
819 if len(batch.L2Txs) > 0 {
820 err = hdb.addL2Txs(txn, batch.L2Txs)
821 if err != nil {
822 return err
823 }
824 }
825
826 // Add accounts
827 if len(batch.CreatedAccounts) > 0 {
828 err = hdb.addAccounts(txn, batch.CreatedAccounts)
829 if err != nil {
830 return err
831 }
832 }
833
834 // Add exit tree
835 if len(batch.ExitTree) > 0 {
836 err = hdb.addExitTree(txn, batch.ExitTree)
837 if err != nil {
838 return err
839 }
840 }
841
842 // TODO: INSERT CONTRACTS VARS
843 }
844
845 return txn.Commit()
846}