hermez-node/db/historydb/historydb.go
db/historydb/historydb.goRaw
1package historydb
2
3import (
4 "database/sql"
5 "errors"
6 "fmt"
7 "math"
8 "math/big"
9
10 ethCommon "github.com/ethereum/go-ethereum/common"
11 "github.com/hermeznetwork/hermez-node/common"
12 "github.com/hermeznetwork/hermez-node/db"
13 "github.com/hermeznetwork/hermez-node/log"
14 "github.com/iden3/go-iden3-crypto/babyjub"
15 "github.com/jmoiron/sqlx"
16
17 //nolint:errcheck // driver for postgres DB
18 _ "github.com/lib/pq"
19 "github.com/russross/meddler"
20)
21
22const (
23 // OrderAsc indicates ascending order when using pagination
24 OrderAsc = "ASC"
25 // OrderDesc indicates descending order when using pagination
26 OrderDesc = "DESC"
27)
28
29// TODO(Edu): Document here how HistoryDB is kept consistent
30
31// HistoryDB persist the historic of the rollup
32type HistoryDB struct {
33 db *sqlx.DB
34}
35
36// NewHistoryDB initialize the DB
37func NewHistoryDB(db *sqlx.DB) *HistoryDB {
38 return &HistoryDB{db: db}
39}
40
41// AddBlock insert a block into the DB
42func (hdb *HistoryDB) AddBlock(block *common.Block) error { return hdb.addBlock(hdb.db, block) }
43func (hdb *HistoryDB) addBlock(d meddler.DB, block *common.Block) error {
44 return meddler.Insert(d, "block", block)
45}
46
47// AddBlocks inserts blocks into the DB
48func (hdb *HistoryDB) AddBlocks(blocks []common.Block) error {
49 return hdb.addBlocks(hdb.db, blocks)
50}
51
52func (hdb *HistoryDB) addBlocks(d meddler.DB, blocks []common.Block) error {
53 return db.BulkInsert(
54 d,
55 `INSERT INTO block (
56 eth_block_num,
57 timestamp,
58 hash
59 ) VALUES %s;`,
60 blocks[:],
61 )
62}
63
64// GetBlock retrieve a block from the DB, given a block number
65func (hdb *HistoryDB) GetBlock(blockNum int64) (*common.Block, error) {
66 block := &common.Block{}
67 err := meddler.QueryRow(
68 hdb.db, block,
69 "SELECT * FROM block WHERE eth_block_num = $1;", blockNum,
70 )
71 return block, err
72}
73
74// GetBlocks retrieve blocks from the DB, given a range of block numbers defined by from and to
75func (hdb *HistoryDB) GetBlocks(from, to int64) ([]common.Block, error) {
76 var blocks []*common.Block
77 err := meddler.QueryAll(
78 hdb.db, &blocks,
79 "SELECT * FROM block WHERE $1 <= eth_block_num AND eth_block_num < $2;",
80 from, to,
81 )
82 return db.SlicePtrsToSlice(blocks).([]common.Block), err
83}
84
85// GetLastBlock retrieve the block with the highest block number from the DB
86func (hdb *HistoryDB) GetLastBlock() (*common.Block, error) {
87 block := &common.Block{}
88 err := meddler.QueryRow(
89 hdb.db, block, "SELECT * FROM block ORDER BY eth_block_num DESC LIMIT 1;",
90 )
91 return block, err
92}
93
94// AddBatch insert a Batch into the DB
95func (hdb *HistoryDB) AddBatch(batch *common.Batch) error { return hdb.addBatch(hdb.db, batch) }
96func (hdb *HistoryDB) addBatch(d meddler.DB, batch *common.Batch) error {
97 // Calculate total collected fees in USD
98 // Get IDs of collected tokens for fees
99 tokenIDs := []common.TokenID{}
100 for id := range batch.CollectedFees {
101 tokenIDs = append(tokenIDs, id)
102 }
103 // Get USD value of the tokens
104 type tokenPrice struct {
105 ID common.TokenID `meddler:"token_id"`
106 USD *float64 `meddler:"usd"`
107 Decimals int `meddler:"decimals"`
108 }
109 var tokenPrices []*tokenPrice
110 if len(tokenIDs) > 0 {
111 query, args, err := sqlx.In(
112 "SELECT token_id, usd, decimals FROM token WHERE token_id IN (?)",
113 tokenIDs,
114 )
115 if err != nil {
116 return err
117 }
118 query = hdb.db.Rebind(query)
119 if err := meddler.QueryAll(
120 hdb.db, &tokenPrices, query, args...,
121 ); err != nil {
122 return err
123 }
124 }
125 // Calculate total collected
126 var total float64
127 for _, tokenPrice := range tokenPrices {
128 if tokenPrice.USD == nil {
129 continue
130 }
131 f := new(big.Float).SetInt(batch.CollectedFees[tokenPrice.ID])
132 amount, _ := f.Float64()
133 total += *tokenPrice.USD * (amount / math.Pow(10, float64(tokenPrice.Decimals))) //nolint decimals have to be ^10
134 }
135 batch.TotalFeesUSD = &total
136 // Insert to DB
137 return meddler.Insert(d, "batch", batch)
138}
139
140// AddBatches insert Bids into the DB
141func (hdb *HistoryDB) AddBatches(batches []common.Batch) error {
142 return hdb.addBatches(hdb.db, batches)
143}
144func (hdb *HistoryDB) addBatches(d meddler.DB, batches []common.Batch) error {
145 for i := 0; i < len(batches); i++ {
146 if err := hdb.addBatch(d, &batches[i]); err != nil {
147 return err
148 }
149 }
150 return nil
151}
152
153// GetBatches retrieve batches from the DB, given a range of batch numbers defined by from and to
154func (hdb *HistoryDB) GetBatches(from, to common.BatchNum) ([]common.Batch, error) {
155 var batches []*common.Batch
156 err := meddler.QueryAll(
157 hdb.db, &batches,
158 "SELECT * FROM batch WHERE $1 <= batch_num AND batch_num < $2;",
159 from, to,
160 )
161 return db.SlicePtrsToSlice(batches).([]common.Batch), err
162}
163
164// GetLastBatchNum returns the BatchNum of the latest forged batch
165func (hdb *HistoryDB) GetLastBatchNum() (common.BatchNum, error) {
166 row := hdb.db.QueryRow("SELECT batch_num FROM batch ORDER BY batch_num DESC LIMIT 1;")
167 var batchNum common.BatchNum
168 return batchNum, row.Scan(&batchNum)
169}
170
171// GetLastL1TxsNum returns the greatest ForgeL1TxsNum in the DB from forged
172// batches. If there's no batch in the DB (nil, nil) is returned.
173func (hdb *HistoryDB) GetLastL1TxsNum() (*int64, error) {
174 row := hdb.db.QueryRow("SELECT MAX(forge_l1_txs_num) FROM batch;")
175 lastL1TxsNum := new(int64)
176 return lastL1TxsNum, row.Scan(&lastL1TxsNum)
177}
178
179// Reorg deletes all the information that was added into the DB after the
180// lastValidBlock. If lastValidBlock is negative, all block information is
181// deleted.
182func (hdb *HistoryDB) Reorg(lastValidBlock int64) error {
183 var err error
184 if lastValidBlock < 0 {
185 _, err = hdb.db.Exec("DELETE FROM block;")
186 } else {
187 _, err = hdb.db.Exec("DELETE FROM block WHERE eth_block_num > $1;", lastValidBlock)
188 }
189 return err
190}
191
192// SyncPoD stores all the data that can be changed / added on a block in the PoD SC
193func (hdb *HistoryDB) SyncPoD(
194 blockNum uint64,
195 bids []common.Bid,
196 coordinators []common.Coordinator,
197 vars *common.AuctionVars,
198) error {
199 return nil
200}
201
202// AddBids insert Bids into the DB
203func (hdb *HistoryDB) AddBids(bids []common.Bid) error { return hdb.addBids(hdb.db, bids) }
204func (hdb *HistoryDB) addBids(d meddler.DB, bids []common.Bid) error {
205 // TODO: check the coordinator info
206 return db.BulkInsert(
207 d,
208 "INSERT INTO bid (slot_num, bid_value, eth_block_num, bidder_addr) VALUES %s;",
209 bids[:],
210 )
211}
212
213// GetBids return the bids
214func (hdb *HistoryDB) GetBids() ([]common.Bid, error) {
215 var bids []*common.Bid
216 err := meddler.QueryAll(
217 hdb.db, &bids,
218 "SELECT * FROM bid;",
219 )
220 return db.SlicePtrsToSlice(bids).([]common.Bid), err
221}
222
223// AddCoordinators insert Coordinators into the DB
224func (hdb *HistoryDB) AddCoordinators(coordinators []common.Coordinator) error {
225 return hdb.addCoordinators(hdb.db, coordinators)
226}
227func (hdb *HistoryDB) addCoordinators(d meddler.DB, coordinators []common.Coordinator) error {
228 return db.BulkInsert(
229 d,
230 "INSERT INTO coordinator (bidder_addr, forger_addr, eth_block_num, url) VALUES %s;",
231 coordinators[:],
232 )
233}
234
235// AddExitTree insert Exit tree into the DB
236func (hdb *HistoryDB) AddExitTree(exitTree []common.ExitInfo) error {
237 return hdb.addExitTree(hdb.db, exitTree)
238}
239func (hdb *HistoryDB) addExitTree(d meddler.DB, exitTree []common.ExitInfo) error {
240 return db.BulkInsert(
241 d,
242 "INSERT INTO exit_tree (batch_num, account_idx, merkle_proof, balance, "+
243 "instant_withdrawn, delayed_withdraw_request, delayed_withdrawn) VALUES %s;",
244 exitTree[:],
245 )
246}
247
248// AddToken insert a token into the DB
249func (hdb *HistoryDB) AddToken(token *common.Token) error {
250 return meddler.Insert(hdb.db, "token", token)
251}
252
253// AddTokens insert tokens into the DB
254func (hdb *HistoryDB) AddTokens(tokens []common.Token) error { return hdb.addTokens(hdb.db, tokens) }
255func (hdb *HistoryDB) addTokens(d meddler.DB, tokens []common.Token) error {
256 return db.BulkInsert(
257 d,
258 `INSERT INTO token (
259 token_id,
260 eth_block_num,
261 eth_addr,
262 name,
263 symbol,
264 decimals
265 ) VALUES %s;`,
266 tokens[:],
267 )
268}
269
270// UpdateTokenValue updates the USD value of a token
271func (hdb *HistoryDB) UpdateTokenValue(tokenSymbol string, value float64) error {
272 _, err := hdb.db.Exec(
273 "UPDATE token SET usd = $1 WHERE symbol = $2;",
274 value, tokenSymbol,
275 )
276 return err
277}
278
279// GetToken returns a token from the DB given a TokenID
280func (hdb *HistoryDB) GetToken(tokenID common.TokenID) (*TokenRead, error) {
281 token := &TokenRead{}
282 err := meddler.QueryRow(
283 hdb.db, token, `SELECT * FROM token WHERE token_id = $1;`, tokenID,
284 )
285 return token, err
286}
287
288// GetAllTokens returns all tokens from the DB
289func (hdb *HistoryDB) GetAllTokens() ([]TokenRead, error) {
290 var tokens []*TokenRead
291 err := meddler.QueryAll(
292 hdb.db, &tokens,
293 "SELECT * FROM token ORDER BY token_id;",
294 )
295 return db.SlicePtrsToSlice(tokens).([]TokenRead), err
296}
297
298// GetTokens returns a list of tokens from the DB
299func (hdb *HistoryDB) GetTokens(ids []common.TokenID, symbols []string, name string, fromItem, limit *uint, order string) ([]TokenRead, *db.Pagination, error) {
300 var query string
301 var args []interface{}
302 queryStr := `SELECT * , COUNT(*) OVER() AS total_items, MIN(token.item_id) OVER() AS first_item, MAX(token.item_id) OVER() AS last_item FROM token `
303 // Apply filters
304 nextIsAnd := false
305 if len(ids) > 0 {
306 queryStr += "WHERE token_id IN (?) "
307 nextIsAnd = true
308 args = append(args, ids)
309 }
310 if len(symbols) > 0 {
311 if nextIsAnd {
312 queryStr += "AND "
313 } else {
314 queryStr += "WHERE "
315 }
316 queryStr += "symbol IN (?) "
317 args = append(args, symbols)
318 nextIsAnd = true
319 }
320 if name != "" {
321 if nextIsAnd {
322 queryStr += "AND "
323 } else {
324 queryStr += "WHERE "
325 }
326 queryStr += "name ~ ? "
327 args = append(args, name)
328 nextIsAnd = true
329 }
330 if fromItem != nil {
331 if nextIsAnd {
332 queryStr += "AND "
333 } else {
334 queryStr += "WHERE "
335 }
336 if order == OrderAsc {
337 queryStr += "item_id >= ? "
338 } else {
339 queryStr += "item_id <= ? "
340 }
341 args = append(args, fromItem)
342 }
343 // pagination
344 queryStr += "ORDER BY item_id "
345 if order == OrderAsc {
346 queryStr += "ASC "
347 } else {
348 queryStr += "DESC "
349 }
350 queryStr += fmt.Sprintf("LIMIT %d;", *limit)
351 query, argsQ, err := sqlx.In(queryStr, args...)
352 if err != nil {
353 return nil, nil, err
354 }
355 query = hdb.db.Rebind(query)
356 tokens := []*TokenRead{}
357 if err := meddler.QueryAll(hdb.db, &tokens, query, argsQ...); err != nil {
358 return nil, nil, err
359 }
360 if len(tokens) == 0 {
361 return nil, nil, sql.ErrNoRows
362 }
363 return db.SlicePtrsToSlice(tokens).([]TokenRead), &db.Pagination{
364 TotalItems: tokens[0].TotalItems,
365 FirstItem: tokens[0].FirstItem,
366 LastItem: tokens[0].LastItem,
367 }, nil
368}
369
370// GetTokenSymbols returns all the token symbols from the DB
371func (hdb *HistoryDB) GetTokenSymbols() ([]string, error) {
372 var tokenSymbols []string
373 rows, err := hdb.db.Query("SELECT symbol FROM token;")
374 if err != nil {
375 return nil, err
376 }
377 sym := new(string)
378 for rows.Next() {
379 err = rows.Scan(sym)
380 if err != nil {
381 return nil, err
382 }
383 tokenSymbols = append(tokenSymbols, *sym)
384 }
385 return tokenSymbols, nil
386}
387
388// AddAccounts insert accounts into the DB
389func (hdb *HistoryDB) AddAccounts(accounts []common.Account) error {
390 return hdb.addAccounts(hdb.db, accounts)
391}
392func (hdb *HistoryDB) addAccounts(d meddler.DB, accounts []common.Account) error {
393 return db.BulkInsert(
394 d,
395 `INSERT INTO account (
396 idx,
397 token_id,
398 batch_num,
399 bjj,
400 eth_addr
401 ) VALUES %s;`,
402 accounts[:],
403 )
404}
405
406// GetAccounts returns a list of accounts from the DB
407func (hdb *HistoryDB) GetAccounts() ([]common.Account, error) {
408 var accs []*common.Account
409 err := meddler.QueryAll(
410 hdb.db, &accs,
411 "SELECT * FROM account ORDER BY idx;",
412 )
413 return db.SlicePtrsToSlice(accs).([]common.Account), err
414}
415
416// AddL1Txs inserts L1 txs to the DB. USD and LoadAmountUSD will be set automatically before storing the tx.
417// If the tx is originated by a coordinator, BatchNum must be provided. If it's originated by a user,
418// BatchNum should be null, and the value will be setted by a trigger when a batch forges the tx.
419func (hdb *HistoryDB) AddL1Txs(l1txs []common.L1Tx) error { return hdb.addL1Txs(hdb.db, l1txs) }
420
421// addL1Txs inserts L1 txs to the DB. USD and LoadAmountUSD will be set automatically before storing the tx.
422// If the tx is originated by a coordinator, BatchNum must be provided. If it's originated by a user,
423// BatchNum should be null, and the value will be setted by a trigger when a batch forges the tx.
424func (hdb *HistoryDB) addL1Txs(d meddler.DB, l1txs []common.L1Tx) error {
425 txs := []txWrite{}
426 for i := 0; i < len(l1txs); i++ {
427 af := new(big.Float).SetInt(l1txs[i].Amount)
428 amountFloat, _ := af.Float64()
429 laf := new(big.Float).SetInt(l1txs[i].LoadAmount)
430 loadAmountFloat, _ := laf.Float64()
431 txs = append(txs, txWrite{
432 // Generic
433 IsL1: true,
434 TxID: l1txs[i].TxID,
435 Type: l1txs[i].Type,
436 Position: l1txs[i].Position,
437 FromIdx: &l1txs[i].FromIdx,
438 ToIdx: l1txs[i].ToIdx,
439 Amount: l1txs[i].Amount,
440 AmountFloat: amountFloat,
441 TokenID: l1txs[i].TokenID,
442 BatchNum: l1txs[i].BatchNum,
443 EthBlockNum: l1txs[i].EthBlockNum,
444 // L1
445 ToForgeL1TxsNum: l1txs[i].ToForgeL1TxsNum,
446 UserOrigin: &l1txs[i].UserOrigin,
447 FromEthAddr: &l1txs[i].FromEthAddr,
448 FromBJJ: l1txs[i].FromBJJ,
449 LoadAmount: l1txs[i].LoadAmount,
450 LoadAmountFloat: &loadAmountFloat,
451 })
452 }
453 return hdb.addTxs(d, txs)
454}
455
456// AddL2Txs inserts L2 txs to the DB. TokenID, USD and FeeUSD will be set automatically before storing the tx.
457func (hdb *HistoryDB) AddL2Txs(l2txs []common.L2Tx) error { return hdb.addL2Txs(hdb.db, l2txs) }
458
459// addL2Txs inserts L2 txs to the DB. TokenID, USD and FeeUSD will be set automatically before storing the tx.
460func (hdb *HistoryDB) addL2Txs(d meddler.DB, l2txs []common.L2Tx) error {
461 txs := []txWrite{}
462 for i := 0; i < len(l2txs); i++ {
463 f := new(big.Float).SetInt(l2txs[i].Amount)
464 amountFloat, _ := f.Float64()
465 txs = append(txs, txWrite{
466 // Generic
467 IsL1: false,
468 TxID: l2txs[i].TxID,
469 Type: l2txs[i].Type,
470 Position: l2txs[i].Position,
471 FromIdx: &l2txs[i].FromIdx,
472 ToIdx: l2txs[i].ToIdx,
473 Amount: l2txs[i].Amount,
474 AmountFloat: amountFloat,
475 BatchNum: &l2txs[i].BatchNum,
476 EthBlockNum: l2txs[i].EthBlockNum,
477 // L2
478 Fee: &l2txs[i].Fee,
479 Nonce: &l2txs[i].Nonce,
480 })
481 }
482 return hdb.addTxs(d, txs)
483}
484
485func (hdb *HistoryDB) addTxs(d meddler.DB, txs []txWrite) error {
486 return db.BulkInsert(
487 d,
488 `INSERT INTO tx (
489 is_l1,
490 id,
491 type,
492 position,
493 from_idx,
494 to_idx,
495 amount,
496 amount_f,
497 token_id,
498 batch_num,
499 eth_block_num,
500 to_forge_l1_txs_num,
501 user_origin,
502 from_eth_addr,
503 from_bjj,
504 load_amount,
505 load_amount_f,
506 fee,
507 nonce
508 ) VALUES %s;`,
509 txs[:],
510 )
511}
512
513// // GetTxs returns a list of txs from the DB
514// func (hdb *HistoryDB) GetTxs() ([]common.Tx, error) {
515// var txs []*common.Tx
516// err := meddler.QueryAll(
517// hdb.db, &txs,
518// `SELECT * FROM tx
519// ORDER BY (batch_num, position) ASC`,
520// )
521// return db.SlicePtrsToSlice(txs).([]common.Tx), err
522// }
523
524// GetHistoryTx returns a tx from the DB given a TxID
525func (hdb *HistoryDB) GetHistoryTx(txID common.TxID) (*HistoryTx, error) {
526 tx := &HistoryTx{}
527 err := meddler.QueryRow(
528 hdb.db, tx, `SELECT tx.item_id, tx.is_l1, tx.id, tx.type, tx.position,
529 tx.from_idx, tx.to_idx, tx.amount, tx.token_id, tx.amount_usd,
530 tx.batch_num, tx.eth_block_num, tx.to_forge_l1_txs_num, tx.user_origin,
531 tx.from_eth_addr, tx.from_bjj, tx.load_amount,
532 tx.load_amount_usd, tx.fee, tx.fee_usd, tx.nonce,
533 token.token_id, token.eth_block_num AS token_block,
534 token.eth_addr, token.name, token.symbol, token.decimals, token.usd,
535 token.usd_update, block.timestamp
536 FROM tx INNER JOIN token ON tx.token_id = token.token_id
537 INNER JOIN block ON tx.eth_block_num = block.eth_block_num
538 WHERE tx.id = $1;`, txID,
539 )
540 return tx, err
541}
542
543// GetHistoryTxs returns a list of txs from the DB using the HistoryTx struct
544// and pagination info
545func (hdb *HistoryDB) GetHistoryTxs(
546 ethAddr *ethCommon.Address, bjj *babyjub.PublicKey,
547 tokenID *common.TokenID, idx *common.Idx, batchNum *uint, txType *common.TxType,
548 fromItem, limit *uint, order string,
549) ([]HistoryTx, *db.Pagination, error) {
550 if ethAddr != nil && bjj != nil {
551 return nil, nil, errors.New("ethAddr and bjj are incompatible")
552 }
553 var query string
554 var args []interface{}
555 queryStr := `SELECT tx.item_id, tx.is_l1, tx.id, tx.type, tx.position,
556 tx.from_idx, tx.to_idx, tx.amount, tx.token_id, tx.amount_usd,
557 tx.batch_num, tx.eth_block_num, tx.to_forge_l1_txs_num, tx.user_origin,
558 tx.from_eth_addr, tx.from_bjj, tx.load_amount,
559 tx.load_amount_usd, tx.fee, tx.fee_usd, tx.nonce,
560 token.token_id, token.eth_block_num AS token_block,
561 token.eth_addr, token.name, token.symbol, token.decimals, token.usd,
562 token.usd_update, block.timestamp, count(*) OVER() AS total_items,
563 MIN(tx.item_id) OVER() AS first_item, MAX(tx.item_id) OVER() AS last_item
564 FROM tx INNER JOIN token ON tx.token_id = token.token_id
565 INNER JOIN block ON tx.eth_block_num = block.eth_block_num `
566 // Apply filters
567 nextIsAnd := false
568 // ethAddr filter
569 if ethAddr != nil {
570 queryStr = `WITH acc AS
571 (select idx from account where eth_addr = ?) ` + queryStr
572 queryStr += ", acc WHERE (tx.from_idx IN(acc.idx) OR tx.to_idx IN(acc.idx)) "
573 nextIsAnd = true
574 args = append(args, ethAddr)
575 } else if bjj != nil { // bjj filter
576 queryStr = `WITH acc AS
577 (select idx from account where bjj = ?) ` + queryStr
578 queryStr += ", acc WHERE (tx.from_idx IN(acc.idx) OR tx.to_idx IN(acc.idx)) "
579 nextIsAnd = true
580 args = append(args, bjj)
581 }
582 // tokenID filter
583 if tokenID != nil {
584 if nextIsAnd {
585 queryStr += "AND "
586 } else {
587 queryStr += "WHERE "
588 }
589 queryStr += "tx.token_id = ? "
590 args = append(args, tokenID)
591 nextIsAnd = true
592 }
593 // idx filter
594 if idx != nil {
595 if nextIsAnd {
596 queryStr += "AND "
597 } else {
598 queryStr += "WHERE "
599 }
600 queryStr += "(tx.from_idx = ? OR tx.to_idx = ?) "
601 args = append(args, idx, idx)
602 nextIsAnd = true
603 }
604 // batchNum filter
605 if batchNum != nil {
606 if nextIsAnd {
607 queryStr += "AND "
608 } else {
609 queryStr += "WHERE "
610 }
611 queryStr += "tx.batch_num = ? "
612 args = append(args, batchNum)
613 nextIsAnd = true
614 }
615 // txType filter
616 if txType != nil {
617 if nextIsAnd {
618 queryStr += "AND "
619 } else {
620 queryStr += "WHERE "
621 }
622 queryStr += "tx.type = ? "
623 args = append(args, txType)
624 nextIsAnd = true
625 }
626 if fromItem != nil {
627 if nextIsAnd {
628 queryStr += "AND "
629 } else {
630 queryStr += "WHERE "
631 }
632 if order == OrderAsc {
633 queryStr += "tx.item_id >= ? "
634 } else {
635 queryStr += "tx.item_id <= ? "
636 }
637 args = append(args, fromItem)
638 nextIsAnd = true
639 }
640 if nextIsAnd {
641 queryStr += "AND "
642 } else {
643 queryStr += "WHERE "
644 }
645 queryStr += "tx.batch_num IS NOT NULL "
646
647 // pagination
648 queryStr += "ORDER BY tx.item_id "
649 if order == OrderAsc {
650 queryStr += " ASC "
651 } else {
652 queryStr += " DESC "
653 }
654 queryStr += fmt.Sprintf("LIMIT %d;", *limit)
655 query = hdb.db.Rebind(queryStr)
656 log.Debug(query)
657 txsPtrs := []*HistoryTx{}
658 if err := meddler.QueryAll(hdb.db, &txsPtrs, query, args...); err != nil {
659 return nil, nil, err
660 }
661 txs := db.SlicePtrsToSlice(txsPtrs).([]HistoryTx)
662 if len(txs) == 0 {
663 return nil, nil, sql.ErrNoRows
664 }
665 return txs, &db.Pagination{
666 TotalItems: txs[0].TotalItems,
667 FirstItem: txs[0].FirstItem,
668 LastItem: txs[0].LastItem,
669 }, nil
670}
671
672// GetExit returns a exit from the DB
673func (hdb *HistoryDB) GetExit(batchNum *uint, idx *common.Idx) (*HistoryExit, error) {
674 exit := &HistoryExit{}
675 err := meddler.QueryRow(
676 hdb.db, exit, `SELECT exit_tree.*, token.token_id, token.eth_block_num AS token_block,
677 token.eth_addr, token.name, token.symbol, token.decimals, token.usd, token.usd_update
678 FROM exit_tree INNER JOIN account ON exit_tree.account_idx = account.idx
679 INNER JOIN token ON account.token_id = token.token_id
680 WHERE exit_tree.batch_num = $1 AND exit_tree.account_idx = $2;`, batchNum, idx,
681 )
682 return exit, err
683}
684
685// GetExits returns a list of exits from the DB and pagination info
686func (hdb *HistoryDB) GetExits(
687 ethAddr *ethCommon.Address, bjj *babyjub.PublicKey,
688 tokenID *common.TokenID, idx *common.Idx, batchNum *uint,
689 fromItem, limit *uint, order string,
690) ([]HistoryExit, *db.Pagination, error) {
691 if ethAddr != nil && bjj != nil {
692 return nil, nil, errors.New("ethAddr and bjj are incompatible")
693 }
694 var query string
695 var args []interface{}
696 queryStr := `SELECT exit_tree.*, token.token_id, token.eth_block_num AS token_block,
697 token.eth_addr, token.name, token.symbol, token.decimals, token.usd,
698 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
699 FROM exit_tree INNER JOIN account ON exit_tree.account_idx = account.idx
700 INNER JOIN token ON account.token_id = token.token_id `
701 // Apply filters
702 nextIsAnd := false
703 // ethAddr filter
704 if ethAddr != nil {
705 queryStr += "WHERE account.eth_addr = ? "
706 nextIsAnd = true
707 args = append(args, ethAddr)
708 } else if bjj != nil { // bjj filter
709 queryStr += "WHERE account.bjj = ? "
710 nextIsAnd = true
711 args = append(args, bjj)
712 }
713 // tokenID filter
714 if tokenID != nil {
715 if nextIsAnd {
716 queryStr += "AND "
717 } else {
718 queryStr += "WHERE "
719 }
720 queryStr += "account.token_id = ? "
721 args = append(args, tokenID)
722 nextIsAnd = true
723 }
724 // idx filter
725 if idx != nil {
726 if nextIsAnd {
727 queryStr += "AND "
728 } else {
729 queryStr += "WHERE "
730 }
731 queryStr += "exit_tree.account_idx = ? "
732 args = append(args, idx)
733 nextIsAnd = true
734 }
735 // batchNum filter
736 if batchNum != nil {
737 if nextIsAnd {
738 queryStr += "AND "
739 } else {
740 queryStr += "WHERE "
741 }
742 queryStr += "exit_tree.batch_num = ? "
743 args = append(args, batchNum)
744 nextIsAnd = true
745 }
746 if fromItem != nil {
747 if nextIsAnd {
748 queryStr += "AND "
749 } else {
750 queryStr += "WHERE "
751 }
752 if order == OrderAsc {
753 queryStr += "exit_tree.item_id >= ? "
754 } else {
755 queryStr += "exit_tree.item_id <= ? "
756 }
757 args = append(args, fromItem)
758 // nextIsAnd = true
759 }
760 // pagination
761 queryStr += "ORDER BY exit_tree.item_id "
762 if order == OrderAsc {
763 queryStr += " ASC "
764 } else {
765 queryStr += " DESC "
766 }
767 queryStr += fmt.Sprintf("LIMIT %d;", *limit)
768 query = hdb.db.Rebind(queryStr)
769 // log.Debug(query)
770 exits := []*HistoryExit{}
771 if err := meddler.QueryAll(hdb.db, &exits, query, args...); err != nil {
772 return nil, nil, err
773 }
774 if len(exits) == 0 {
775 return nil, nil, sql.ErrNoRows
776 }
777 return db.SlicePtrsToSlice(exits).([]HistoryExit), &db.Pagination{
778 TotalItems: exits[0].TotalItems,
779 FirstItem: exits[0].FirstItem,
780 LastItem: exits[0].LastItem,
781 }, nil
782}
783
784// // GetTx returns a tx from the DB
785// func (hdb *HistoryDB) GetTx(txID common.TxID) (*common.Tx, error) {
786// tx := new(common.Tx)
787// return tx, meddler.QueryRow(
788// hdb.db, tx,
789// "SELECT * FROM tx WHERE id = $1;",
790// txID,
791// )
792// }
793
794// GetL1UserTxs gets L1 User Txs to be forged in the L1Batch with toForgeL1TxsNum.
795func (hdb *HistoryDB) GetL1UserTxs(toForgeL1TxsNum int64) ([]common.L1Tx, error) {
796 var txs []*common.L1Tx
797 err := meddler.QueryAll(
798 hdb.db, &txs,
799 `SELECT tx.id, tx.to_forge_l1_txs_num, tx.position, tx.user_origin,
800 tx.from_idx, tx.from_eth_addr, tx.from_bjj, tx.to_idx, tx.token_id, tx.amount,
801 tx.load_amount, tx.eth_block_num, tx.type, tx.batch_num
802 FROM tx WHERE to_forge_l1_txs_num = $1 AND is_l1 = TRUE AND user_origin = TRUE;`,
803 toForgeL1TxsNum,
804 )
805 return db.SlicePtrsToSlice(txs).([]common.L1Tx), err
806}
807
808// TODO: Think about chaning all the queries that return a last value, to queries that return the next valid value.
809
810// GetLastTxsPosition for a given to_forge_l1_txs_num
811func (hdb *HistoryDB) GetLastTxsPosition(toForgeL1TxsNum int64) (int, error) {
812 row := hdb.db.QueryRow("SELECT MAX(position) FROM tx WHERE to_forge_l1_txs_num = $1;", toForgeL1TxsNum)
813 var lastL1TxsPosition int
814 return lastL1TxsPosition, row.Scan(&lastL1TxsPosition)
815}
816
817// AddBlockSCData stores all the information of a block retrieved by the
818// Synchronizer. Blocks should be inserted in order, leaving no gaps because
819// the pagination system of the API/DB depends on this. Within blocks, all
820// items should also be in the correct order (Accounts, Tokens, Txs, etc.)
821func (hdb *HistoryDB) AddBlockSCData(blockData *common.BlockData) (err error) {
822 txn, err := hdb.db.Begin()
823 if err != nil {
824 return err
825 }
826 defer func() {
827 if err != nil {
828 errRollback := txn.Rollback()
829 if errRollback != nil {
830 log.Errorw("Rollback", "err", errRollback)
831 }
832 }
833 }()
834
835 // Add block
836 err = hdb.addBlock(txn, &blockData.Block)
837 if err != nil {
838 return err
839 }
840
841 // Add Coordinators
842 if len(blockData.Coordinators) > 0 {
843 err = hdb.addCoordinators(txn, blockData.Coordinators)
844 if err != nil {
845 return err
846 }
847 }
848
849 // Add Bids
850 if len(blockData.Bids) > 0 {
851 err = hdb.addBids(txn, blockData.Bids)
852 if err != nil {
853 return err
854 }
855 }
856
857 // Add Tokens
858 if len(blockData.AddedTokens) > 0 {
859 err = hdb.addTokens(txn, blockData.AddedTokens)
860 if err != nil {
861 return err
862 }
863 }
864
865 // Add l1 Txs
866 if len(blockData.L1UserTxs) > 0 {
867 err = hdb.addL1Txs(txn, blockData.L1UserTxs)
868 if err != nil {
869 return err
870 }
871 }
872
873 // Add Batches
874 for i := range blockData.Batches {
875 batch := &blockData.Batches[i]
876 // Add Batch: this will trigger an update on the DB
877 // that will set the batch num of forged L1 txs in this batch
878 err = hdb.addBatch(txn, &batch.Batch)
879 if err != nil {
880 return err
881 }
882
883 // Add unforged l1 Txs
884 if batch.L1Batch {
885 if len(batch.L1CoordinatorTxs) > 0 {
886 err = hdb.addL1Txs(txn, batch.L1CoordinatorTxs)
887 if err != nil {
888 return err
889 }
890 }
891 }
892
893 // Add l2 Txs
894 if len(batch.L2Txs) > 0 {
895 err = hdb.addL2Txs(txn, batch.L2Txs)
896 if err != nil {
897 return err
898 }
899 }
900
901 // Add accounts
902 if len(batch.CreatedAccounts) > 0 {
903 err = hdb.addAccounts(txn, batch.CreatedAccounts)
904 if err != nil {
905 return err
906 }
907 }
908
909 // Add exit tree
910 if len(batch.ExitTree) > 0 {
911 err = hdb.addExitTree(txn, batch.ExitTree)
912 if err != nil {
913 return err
914 }
915 }
916
917 // TODO: INSERT CONTRACTS VARS
918 }
919
920 return txn.Commit()
921}
922
923// GetCoordinator returns a coordinator by its bidderAddr
924func (hdb *HistoryDB) GetCoordinator(bidderAddr ethCommon.Address) (*HistoryCoordinator, error) {
925 coordinator := &HistoryCoordinator{}
926 err := meddler.QueryRow(
927 hdb.db, coordinator, `SELECT * FROM coordinator WHERE bidder_addr = $1;`, bidderAddr,
928 )
929 return coordinator, err
930}
931
932// GetCoordinators returns a list of coordinators from the DB and pagination info
933func (hdb *HistoryDB) GetCoordinators(fromItem, limit *uint, order string) ([]HistoryCoordinator, *db.Pagination, error) {
934 var query string
935 var args []interface{}
936 queryStr := `SELECT coordinator.*,
937 COUNT(*) OVER() AS total_items, MIN(coordinator.item_id) OVER() AS first_item, MAX(coordinator.item_id) OVER() AS last_item
938 FROM coordinator `
939 // Apply filters
940 if fromItem != nil {
941 queryStr += "WHERE "
942 if order == OrderAsc {
943 queryStr += "coordinator.item_id >= ? "
944 } else {
945 queryStr += "coordinator.item_id <= ? "
946 }
947 args = append(args, fromItem)
948 }
949 // pagination
950 queryStr += "ORDER BY coordinator.item_id "
951 if order == OrderAsc {
952 queryStr += " ASC "
953 } else {
954 queryStr += " DESC "
955 }
956 queryStr += fmt.Sprintf("LIMIT %d;", *limit)
957 query = hdb.db.Rebind(queryStr)
958
959 coordinators := []*HistoryCoordinator{}
960 if err := meddler.QueryAll(hdb.db, &coordinators, query, args...); err != nil {
961 return nil, nil, err
962 }
963 if len(coordinators) == 0 {
964 return nil, nil, sql.ErrNoRows
965 }
966 return db.SlicePtrsToSlice(coordinators).([]HistoryCoordinator), &db.Pagination{
967 TotalItems: coordinators[0].TotalItems,
968 FirstItem: coordinators[0].FirstItem,
969 LastItem: coordinators[0].LastItem,
970 }, nil
971}