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