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