hermez-node/db/historydb/apiqueries.go
db/historydb/apiqueries.goRaw
1package historydb
2
3import (
4 "database/sql"
5 "errors"
6 "fmt"
7 "math/big"
8 "time"
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/tracerr"
14 "github.com/iden3/go-iden3-crypto/babyjub"
15 "github.com/jmoiron/sqlx"
16 "github.com/russross/meddler"
17)
18
19// GetLastBlockAPI retrieve the block with the highest block number from the DB
20func (hdb *HistoryDB) GetLastBlockAPI() (*common.Block, error) {
21 cancel, err := hdb.apiConnCon.Acquire()
22 defer cancel()
23 if err != nil {
24 return nil, tracerr.Wrap(err)
25 }
26 defer hdb.apiConnCon.Release()
27 return hdb.GetLastBlock()
28}
29
30// GetBatchAPI return the batch with the given batchNum
31func (hdb *HistoryDB) GetBatchAPI(batchNum common.BatchNum) (*BatchAPI, error) {
32 cancel, err := hdb.apiConnCon.Acquire()
33 defer cancel()
34 if err != nil {
35 return nil, tracerr.Wrap(err)
36 }
37 defer hdb.apiConnCon.Release()
38 return hdb.getBatchAPI(hdb.dbRead, batchNum)
39}
40
41// GetBatchInternalAPI return the batch with the given batchNum
42func (hdb *HistoryDB) GetBatchInternalAPI(batchNum common.BatchNum) (*BatchAPI, error) {
43 return hdb.getBatchAPI(hdb.dbRead, batchNum)
44}
45
46func (hdb *HistoryDB) getBatchAPI(d meddler.DB, batchNum common.BatchNum) (*BatchAPI, error) {
47 batch := &BatchAPI{}
48 return batch, tracerr.Wrap(meddler.QueryRow(
49 d, batch,
50 `SELECT batch.item_id, batch.batch_num, batch.eth_block_num,
51 batch.forger_addr, batch.fees_collected, batch.total_fees_usd, batch.state_root,
52 batch.num_accounts, batch.exit_root, batch.forge_l1_txs_num, batch.slot_num,
53 block.timestamp, block.hash,
54 COALESCE ((SELECT COUNT(*) FROM tx WHERE batch_num = batch.batch_num), 0) AS forged_txs
55 FROM batch INNER JOIN block ON batch.eth_block_num = block.eth_block_num
56 WHERE batch_num = $1;`, batchNum,
57 ))
58}
59
60// GetBatchesAPI return the batches applying the given filters
61func (hdb *HistoryDB) GetBatchesAPI(
62 minBatchNum, maxBatchNum, slotNum *uint,
63 forgerAddr *ethCommon.Address,
64 fromItem, limit *uint, order string,
65) ([]BatchAPI, uint64, error) {
66 cancel, err := hdb.apiConnCon.Acquire()
67 defer cancel()
68 if err != nil {
69 return nil, 0, tracerr.Wrap(err)
70 }
71 defer hdb.apiConnCon.Release()
72 var query string
73 var args []interface{}
74 queryStr := `SELECT batch.item_id, batch.batch_num, batch.eth_block_num,
75 batch.forger_addr, batch.fees_collected, batch.total_fees_usd, batch.state_root,
76 batch.num_accounts, batch.exit_root, batch.forge_l1_txs_num, batch.slot_num,
77 block.timestamp, block.hash,
78 COALESCE ((SELECT COUNT(*) FROM tx WHERE batch_num = batch.batch_num), 0) AS forged_txs,
79 count(*) OVER() AS total_items
80 FROM batch INNER JOIN block ON batch.eth_block_num = block.eth_block_num `
81 // Apply filters
82 nextIsAnd := false
83 // minBatchNum filter
84 if minBatchNum != nil {
85 if nextIsAnd {
86 queryStr += "AND "
87 } else {
88 queryStr += "WHERE "
89 }
90 queryStr += "batch.batch_num > ? "
91 args = append(args, minBatchNum)
92 nextIsAnd = true
93 }
94 // maxBatchNum filter
95 if maxBatchNum != nil {
96 if nextIsAnd {
97 queryStr += "AND "
98 } else {
99 queryStr += "WHERE "
100 }
101 queryStr += "batch.batch_num < ? "
102 args = append(args, maxBatchNum)
103 nextIsAnd = true
104 }
105 // slotNum filter
106 if slotNum != nil {
107 if nextIsAnd {
108 queryStr += "AND "
109 } else {
110 queryStr += "WHERE "
111 }
112 queryStr += "batch.slot_num = ? "
113 args = append(args, slotNum)
114 nextIsAnd = true
115 }
116 // forgerAddr filter
117 if forgerAddr != nil {
118 if nextIsAnd {
119 queryStr += "AND "
120 } else {
121 queryStr += "WHERE "
122 }
123 queryStr += "batch.forger_addr = ? "
124 args = append(args, forgerAddr)
125 nextIsAnd = true
126 }
127 // pagination
128 if fromItem != nil {
129 if nextIsAnd {
130 queryStr += "AND "
131 } else {
132 queryStr += "WHERE "
133 }
134 if order == OrderAsc {
135 queryStr += "batch.item_id >= ? "
136 } else {
137 queryStr += "batch.item_id <= ? "
138 }
139 args = append(args, fromItem)
140 }
141 queryStr += "ORDER BY batch.item_id "
142 if order == OrderAsc {
143 queryStr += " ASC "
144 } else {
145 queryStr += " DESC "
146 }
147 queryStr += fmt.Sprintf("LIMIT %d;", *limit)
148 query = hdb.dbRead.Rebind(queryStr)
149 // log.Debug(query)
150 batchPtrs := []*BatchAPI{}
151 if err := meddler.QueryAll(hdb.dbRead, &batchPtrs, query, args...); err != nil {
152 return nil, 0, tracerr.Wrap(err)
153 }
154 batches := db.SlicePtrsToSlice(batchPtrs).([]BatchAPI)
155 if len(batches) == 0 {
156 return batches, 0, nil
157 }
158 return batches, batches[0].TotalItems - uint64(len(batches)), nil
159}
160
161// GetBestBidAPI returns the best bid in specific slot by slotNum
162func (hdb *HistoryDB) GetBestBidAPI(slotNum *int64) (BidAPI, error) {
163 bid := &BidAPI{}
164 cancel, err := hdb.apiConnCon.Acquire()
165 defer cancel()
166 if err != nil {
167 return *bid, tracerr.Wrap(err)
168 }
169 defer hdb.apiConnCon.Release()
170 err = meddler.QueryRow(
171 hdb.dbRead, bid, `SELECT bid.*, block.timestamp, coordinator.forger_addr, coordinator.url
172 FROM bid INNER JOIN block ON bid.eth_block_num = block.eth_block_num
173 INNER JOIN (
174 SELECT bidder_addr, MAX(item_id) AS item_id FROM coordinator
175 GROUP BY bidder_addr
176 ) c ON bid.bidder_addr = c.bidder_addr
177 INNER JOIN coordinator ON c.item_id = coordinator.item_id
178 WHERE slot_num = $1 ORDER BY item_id DESC LIMIT 1;`, slotNum,
179 )
180 return *bid, tracerr.Wrap(err)
181}
182
183// GetBestBidsAPI returns the best bid in specific slot by slotNum
184func (hdb *HistoryDB) GetBestBidsAPI(
185 minSlotNum, maxSlotNum *int64,
186 bidderAddr *ethCommon.Address,
187 limit *uint, order string,
188) ([]BidAPI, uint64, error) {
189 cancel, err := hdb.apiConnCon.Acquire()
190 defer cancel()
191 if err != nil {
192 return nil, 0, tracerr.Wrap(err)
193 }
194 defer hdb.apiConnCon.Release()
195 return hdb.getBestBidsAPI(hdb.dbRead, minSlotNum, maxSlotNum, bidderAddr, limit, order)
196}
197func (hdb *HistoryDB) getBestBidsAPI(
198 d meddler.DB,
199 minSlotNum, maxSlotNum *int64,
200 bidderAddr *ethCommon.Address,
201 limit *uint, order string,
202) ([]BidAPI, uint64, error) {
203 var query string
204 var args []interface{}
205 // JOIN the best bid of each slot with the latest update of each coordinator
206 queryStr := `SELECT b.*, block.timestamp, coordinator.forger_addr, coordinator.url,
207 COUNT(*) OVER() AS total_items FROM (
208 SELECT slot_num, MAX(item_id) as maxitem
209 FROM bid GROUP BY slot_num
210 )
211 AS x INNER JOIN bid AS b ON b.item_id = x.maxitem
212 INNER JOIN block ON b.eth_block_num = block.eth_block_num
213 INNER JOIN (
214 SELECT bidder_addr, MAX(item_id) AS item_id FROM coordinator
215 GROUP BY bidder_addr
216 ) c ON b.bidder_addr = c.bidder_addr
217 INNER JOIN coordinator ON c.item_id = coordinator.item_id
218 WHERE (b.slot_num >= ? AND b.slot_num <= ?)`
219 args = append(args, minSlotNum)
220 args = append(args, maxSlotNum)
221 // Apply filters
222 if bidderAddr != nil {
223 queryStr += " AND b.bidder_addr = ? "
224 args = append(args, bidderAddr)
225 }
226 queryStr += " ORDER BY b.slot_num "
227 if order == OrderAsc {
228 queryStr += "ASC "
229 } else {
230 queryStr += "DESC "
231 }
232 if limit != nil {
233 queryStr += fmt.Sprintf("LIMIT %d;", *limit)
234 }
235 query = hdb.dbRead.Rebind(queryStr)
236 bidPtrs := []*BidAPI{}
237 if err := meddler.QueryAll(d, &bidPtrs, query, args...); err != nil {
238 return nil, 0, tracerr.Wrap(err)
239 }
240 // log.Debug(query)
241 bids := db.SlicePtrsToSlice(bidPtrs).([]BidAPI)
242 if len(bids) == 0 {
243 return bids, 0, nil
244 }
245 return bids, bids[0].TotalItems - uint64(len(bids)), nil
246}
247
248// GetBidsAPI return the bids applying the given filters
249func (hdb *HistoryDB) GetBidsAPI(
250 slotNum *int64, bidderAddr *ethCommon.Address,
251 fromItem, limit *uint, order string,
252) ([]BidAPI, uint64, error) {
253 cancel, err := hdb.apiConnCon.Acquire()
254 defer cancel()
255 if err != nil {
256 return nil, 0, tracerr.Wrap(err)
257 }
258 defer hdb.apiConnCon.Release()
259 var query string
260 var args []interface{}
261 // JOIN each bid with the latest update of each coordinator
262 queryStr := `SELECT bid.*, block.timestamp, coord.forger_addr, coord.url,
263 COUNT(*) OVER() AS total_items
264 FROM bid INNER JOIN block ON bid.eth_block_num = block.eth_block_num
265 INNER JOIN (
266 SELECT bidder_addr, MAX(item_id) AS item_id FROM coordinator
267 GROUP BY bidder_addr
268 ) c ON bid.bidder_addr = c.bidder_addr
269 INNER JOIN coordinator coord ON c.item_id = coord.item_id `
270 // Apply filters
271 nextIsAnd := false
272 // slotNum filter
273 if slotNum != nil {
274 if nextIsAnd {
275 queryStr += "AND "
276 } else {
277 queryStr += "WHERE "
278 }
279 queryStr += "bid.slot_num = ? "
280 args = append(args, slotNum)
281 nextIsAnd = true
282 }
283 // bidder filter
284 if bidderAddr != nil {
285 if nextIsAnd {
286 queryStr += "AND "
287 } else {
288 queryStr += "WHERE "
289 }
290 queryStr += "bid.bidder_addr = ? "
291 args = append(args, bidderAddr)
292 nextIsAnd = true
293 }
294 if fromItem != nil {
295 if nextIsAnd {
296 queryStr += "AND "
297 } else {
298 queryStr += "WHERE "
299 }
300 if order == OrderAsc {
301 queryStr += "bid.item_id >= ? "
302 } else {
303 queryStr += "bid.item_id <= ? "
304 }
305 args = append(args, fromItem)
306 }
307 // pagination
308 queryStr += "ORDER BY bid.item_id "
309 if order == OrderAsc {
310 queryStr += "ASC "
311 } else {
312 queryStr += "DESC "
313 }
314 queryStr += fmt.Sprintf("LIMIT %d;", *limit)
315 query, argsQ, err := sqlx.In(queryStr, args...)
316 if err != nil {
317 return nil, 0, tracerr.Wrap(err)
318 }
319 query = hdb.dbRead.Rebind(query)
320 bids := []*BidAPI{}
321 if err := meddler.QueryAll(hdb.dbRead, &bids, query, argsQ...); err != nil {
322 return nil, 0, tracerr.Wrap(err)
323 }
324 if len(bids) == 0 {
325 return []BidAPI{}, 0, nil
326 }
327 return db.SlicePtrsToSlice(bids).([]BidAPI), bids[0].TotalItems - uint64(len(bids)), nil
328}
329
330// GetTokenAPI returns a token from the DB given a TokenID
331func (hdb *HistoryDB) GetTokenAPI(tokenID common.TokenID) (*TokenWithUSD, error) {
332 cancel, err := hdb.apiConnCon.Acquire()
333 defer cancel()
334 if err != nil {
335 return nil, tracerr.Wrap(err)
336 }
337 defer hdb.apiConnCon.Release()
338 return hdb.GetToken(tokenID)
339}
340
341// GetTokensAPI returns a list of tokens from the DB
342func (hdb *HistoryDB) GetTokensAPI(
343 ids []common.TokenID, symbols []string, name string, fromItem,
344 limit *uint, order string,
345) ([]TokenWithUSD, uint64, error) {
346 cancel, err := hdb.apiConnCon.Acquire()
347 defer cancel()
348 if err != nil {
349 return nil, 0, tracerr.Wrap(err)
350 }
351 defer hdb.apiConnCon.Release()
352 var query string
353 var args []interface{}
354 queryStr := `SELECT * , COUNT(*) OVER() AS total_items FROM token `
355 // Apply filters
356 nextIsAnd := false
357 if len(ids) > 0 {
358 queryStr += "WHERE token_id IN (?) "
359 nextIsAnd = true
360 args = append(args, ids)
361 }
362 if len(symbols) > 0 {
363 if nextIsAnd {
364 queryStr += "AND "
365 } else {
366 queryStr += "WHERE "
367 }
368 queryStr += "symbol IN (?) "
369 args = append(args, symbols)
370 nextIsAnd = true
371 }
372 if name != "" {
373 if nextIsAnd {
374 queryStr += "AND "
375 } else {
376 queryStr += "WHERE "
377 }
378 queryStr += "name ~ ? "
379 args = append(args, name)
380 nextIsAnd = true
381 }
382 if fromItem != nil {
383 if nextIsAnd {
384 queryStr += "AND "
385 } else {
386 queryStr += "WHERE "
387 }
388 if order == OrderAsc {
389 queryStr += "item_id >= ? "
390 } else {
391 queryStr += "item_id <= ? "
392 }
393 args = append(args, fromItem)
394 }
395 // pagination
396 queryStr += "ORDER BY item_id "
397 if order == OrderAsc {
398 queryStr += "ASC "
399 } else {
400 queryStr += "DESC "
401 }
402 queryStr += fmt.Sprintf("LIMIT %d;", *limit)
403 query, argsQ, err := sqlx.In(queryStr, args...)
404 if err != nil {
405 return nil, 0, tracerr.Wrap(err)
406 }
407 query = hdb.dbRead.Rebind(query)
408 tokens := []*TokenWithUSD{}
409 if err := meddler.QueryAll(hdb.dbRead, &tokens, query, argsQ...); err != nil {
410 return nil, 0, tracerr.Wrap(err)
411 }
412 if len(tokens) == 0 {
413 return []TokenWithUSD{}, 0, nil
414 }
415 return db.SlicePtrsToSlice(tokens).([]TokenWithUSD), uint64(len(tokens)) - tokens[0].TotalItems, nil
416}
417
418// GetTxAPI returns a tx from the DB given a TxID
419func (hdb *HistoryDB) GetTxAPI(txID common.TxID) (*TxAPI, error) {
420 // Warning: amount_success and deposit_amount_success have true as default for
421 // performance reasons. The expected default value is false (when txs are unforged)
422 // this case is handled at the function func (tx TxAPI) MarshalJSON() ([]byte, error)
423 cancel, err := hdb.apiConnCon.Acquire()
424 defer cancel()
425 if err != nil {
426 return nil, tracerr.Wrap(err)
427 }
428 defer hdb.apiConnCon.Release()
429 tx := &TxAPI{}
430 err = meddler.QueryRow(
431 hdb.dbRead, tx, `SELECT tx.item_id, tx.is_l1, tx.id, tx.type, tx.position,
432 hez_idx(tx.effective_from_idx, token.symbol) AS from_idx, tx.from_eth_addr, tx.from_bjj,
433 hez_idx(tx.to_idx, token.symbol) AS to_idx, tx.to_eth_addr, tx.to_bjj,
434 tx.amount, tx.amount_success, tx.token_id, tx.amount_usd,
435 tx.batch_num, tx.eth_block_num, tx.to_forge_l1_txs_num, tx.user_origin,
436 tx.deposit_amount, tx.deposit_amount_usd, tx.deposit_amount_success, tx.fee, tx.fee_usd, tx.nonce,
437 token.token_id, token.item_id AS token_item_id, token.eth_block_num AS token_block,
438 token.eth_addr, token.name, token.symbol, token.decimals, token.usd,
439 token.usd_update, block.timestamp
440 FROM tx INNER JOIN token ON tx.token_id = token.token_id
441 INNER JOIN block ON tx.eth_block_num = block.eth_block_num
442 WHERE tx.id = $1;`, txID,
443 )
444 return tx, tracerr.Wrap(err)
445}
446
447// GetTxsAPI returns a list of txs from the DB using the HistoryTx struct
448// and pagination info
449func (hdb *HistoryDB) GetTxsAPI(
450 ethAddr *ethCommon.Address, bjj *babyjub.PublicKeyComp,
451 tokenID *common.TokenID, idx *common.Idx, batchNum *uint, txType *common.TxType,
452 fromItem, limit *uint, order string,
453) ([]TxAPI, uint64, error) {
454 // Warning: amount_success and deposit_amount_success have true as default for
455 // performance reasons. The expected default value is false (when txs are unforged)
456 // this case is handled at the function func (tx TxAPI) MarshalJSON() ([]byte, error)
457 cancel, err := hdb.apiConnCon.Acquire()
458 defer cancel()
459 if err != nil {
460 return nil, 0, tracerr.Wrap(err)
461 }
462 defer hdb.apiConnCon.Release()
463 if ethAddr != nil && bjj != nil {
464 return nil, 0, tracerr.Wrap(errors.New("ethAddr and bjj are incompatible"))
465 }
466 var query string
467 var args []interface{}
468 queryStr := `SELECT tx.item_id, tx.is_l1, tx.id, tx.type, tx.position,
469 hez_idx(tx.effective_from_idx, token.symbol) AS from_idx, tx.from_eth_addr, tx.from_bjj,
470 hez_idx(tx.to_idx, token.symbol) AS to_idx, tx.to_eth_addr, tx.to_bjj,
471 tx.amount, tx.amount_success, tx.token_id, tx.amount_usd,
472 tx.batch_num, tx.eth_block_num, tx.to_forge_l1_txs_num, tx.user_origin,
473 tx.deposit_amount, tx.deposit_amount_usd, tx.deposit_amount_success, tx.fee, tx.fee_usd, tx.nonce,
474 token.token_id, token.item_id AS token_item_id, token.eth_block_num AS token_block,
475 token.eth_addr, token.name, token.symbol, token.decimals, token.usd,
476 token.usd_update, block.timestamp, count(*) OVER() AS total_items
477 FROM tx INNER JOIN token ON tx.token_id = token.token_id
478 INNER JOIN block ON tx.eth_block_num = block.eth_block_num `
479 // Apply filters
480 nextIsAnd := false
481 // ethAddr filter
482 if ethAddr != nil {
483 queryStr += "WHERE (tx.from_eth_addr = ? OR tx.to_eth_addr = ?) "
484 nextIsAnd = true
485 args = append(args, ethAddr, ethAddr)
486 } else if bjj != nil { // bjj filter
487 queryStr += "WHERE (tx.from_bjj = ? OR tx.to_bjj = ?) "
488 nextIsAnd = true
489 args = append(args, bjj, bjj)
490 }
491 // tokenID filter
492 if tokenID != nil {
493 if nextIsAnd {
494 queryStr += "AND "
495 } else {
496 queryStr += "WHERE "
497 }
498 queryStr += "tx.token_id = ? "
499 args = append(args, tokenID)
500 nextIsAnd = true
501 }
502 // idx filter
503 if idx != nil {
504 if nextIsAnd {
505 queryStr += "AND "
506 } else {
507 queryStr += "WHERE "
508 }
509 queryStr += "(tx.effective_from_idx = ? OR tx.to_idx = ?) "
510 args = append(args, idx, idx)
511 nextIsAnd = true
512 }
513 // batchNum filter
514 if batchNum != nil {
515 if nextIsAnd {
516 queryStr += "AND "
517 } else {
518 queryStr += "WHERE "
519 }
520 queryStr += "tx.batch_num = ? "
521 args = append(args, batchNum)
522 nextIsAnd = true
523 }
524 // txType filter
525 if txType != nil {
526 if nextIsAnd {
527 queryStr += "AND "
528 } else {
529 queryStr += "WHERE "
530 }
531 queryStr += "tx.type = ? "
532 args = append(args, txType)
533 nextIsAnd = true
534 }
535 if fromItem != nil {
536 if nextIsAnd {
537 queryStr += "AND "
538 } else {
539 queryStr += "WHERE "
540 }
541 if order == OrderAsc {
542 queryStr += "tx.item_id >= ? "
543 } else {
544 queryStr += "tx.item_id <= ? "
545 }
546 args = append(args, fromItem)
547 nextIsAnd = true
548 }
549 if nextIsAnd {
550 queryStr += "AND "
551 } else {
552 queryStr += "WHERE "
553 }
554 queryStr += "tx.batch_num IS NOT NULL "
555
556 // pagination
557 queryStr += "ORDER BY tx.item_id "
558 if order == OrderAsc {
559 queryStr += " ASC "
560 } else {
561 queryStr += " DESC "
562 }
563 queryStr += fmt.Sprintf("LIMIT %d;", *limit)
564 query = hdb.dbRead.Rebind(queryStr)
565 // log.Debug(query)
566 txsPtrs := []*TxAPI{}
567 if err := meddler.QueryAll(hdb.dbRead, &txsPtrs, query, args...); err != nil {
568 return nil, 0, tracerr.Wrap(err)
569 }
570 txs := db.SlicePtrsToSlice(txsPtrs).([]TxAPI)
571 if len(txs) == 0 {
572 return txs, 0, nil
573 }
574 return txs, txs[0].TotalItems - uint64(len(txs)), nil
575}
576
577// GetExitAPI returns a exit from the DB
578func (hdb *HistoryDB) GetExitAPI(batchNum *uint, idx *common.Idx) (*ExitAPI, error) {
579 cancel, err := hdb.apiConnCon.Acquire()
580 defer cancel()
581 if err != nil {
582 return nil, tracerr.Wrap(err)
583 }
584 defer hdb.apiConnCon.Release()
585 exit := &ExitAPI{}
586 err = meddler.QueryRow(
587 hdb.dbRead, exit, `SELECT exit_tree.item_id, exit_tree.batch_num,
588 hez_idx(exit_tree.account_idx, token.symbol) AS account_idx,
589 account.bjj, account.eth_addr,
590 exit_tree.merkle_proof, exit_tree.balance, exit_tree.instant_withdrawn,
591 exit_tree.delayed_withdraw_request, exit_tree.delayed_withdrawn,
592 token.token_id, token.item_id AS token_item_id,
593 token.eth_block_num AS token_block, token.eth_addr AS token_eth_addr, token.name, token.symbol,
594 token.decimals, token.usd, token.usd_update
595 FROM exit_tree INNER JOIN account ON exit_tree.account_idx = account.idx
596 INNER JOIN token ON account.token_id = token.token_id
597 WHERE exit_tree.batch_num = $1 AND exit_tree.account_idx = $2;`, batchNum, idx,
598 )
599 return exit, tracerr.Wrap(err)
600}
601
602// GetExitsAPI returns a list of exits from the DB and pagination info
603func (hdb *HistoryDB) GetExitsAPI(
604 ethAddr *ethCommon.Address, bjj *babyjub.PublicKeyComp, tokenID *common.TokenID,
605 idx *common.Idx, batchNum *uint, onlyPendingWithdraws *bool,
606 fromItem, limit *uint, order string,
607) ([]ExitAPI, uint64, error) {
608 if ethAddr != nil && bjj != nil {
609 return nil, 0, tracerr.Wrap(errors.New("ethAddr and bjj are incompatible"))
610 }
611 cancel, err := hdb.apiConnCon.Acquire()
612 defer cancel()
613 if err != nil {
614 return nil, 0, tracerr.Wrap(err)
615 }
616 defer hdb.apiConnCon.Release()
617 var query string
618 var args []interface{}
619 queryStr := `SELECT exit_tree.item_id, exit_tree.batch_num,
620 hez_idx(exit_tree.account_idx, token.symbol) AS account_idx,
621 account.bjj, account.eth_addr,
622 exit_tree.merkle_proof, exit_tree.balance, exit_tree.instant_withdrawn,
623 exit_tree.delayed_withdraw_request, exit_tree.delayed_withdrawn,
624 token.token_id, token.item_id AS token_item_id,
625 token.eth_block_num AS token_block, token.eth_addr AS token_eth_addr, token.name, token.symbol,
626 token.decimals, token.usd, token.usd_update, COUNT(*) OVER() AS total_items
627 FROM exit_tree INNER JOIN account ON exit_tree.account_idx = account.idx
628 INNER JOIN token ON account.token_id = token.token_id `
629 // Apply filters
630 nextIsAnd := false
631 // ethAddr filter
632 if ethAddr != nil {
633 queryStr += "WHERE account.eth_addr = ? "
634 nextIsAnd = true
635 args = append(args, ethAddr)
636 } else if bjj != nil { // bjj filter
637 queryStr += "WHERE account.bjj = ? "
638 nextIsAnd = true
639 args = append(args, bjj)
640 }
641 // tokenID filter
642 if tokenID != nil {
643 if nextIsAnd {
644 queryStr += "AND "
645 } else {
646 queryStr += "WHERE "
647 }
648 queryStr += "account.token_id = ? "
649 args = append(args, tokenID)
650 nextIsAnd = true
651 }
652 // idx filter
653 if idx != nil {
654 if nextIsAnd {
655 queryStr += "AND "
656 } else {
657 queryStr += "WHERE "
658 }
659 queryStr += "exit_tree.account_idx = ? "
660 args = append(args, idx)
661 nextIsAnd = true
662 }
663 // batchNum filter
664 if batchNum != nil {
665 if nextIsAnd {
666 queryStr += "AND "
667 } else {
668 queryStr += "WHERE "
669 }
670 queryStr += "exit_tree.batch_num = ? "
671 args = append(args, batchNum)
672 nextIsAnd = true
673 }
674 // onlyPendingWithdraws
675 if onlyPendingWithdraws != nil {
676 if *onlyPendingWithdraws {
677 if nextIsAnd {
678 queryStr += "AND "
679 } else {
680 queryStr += "WHERE "
681 }
682 queryStr += "(exit_tree.instant_withdrawn IS NULL AND exit_tree.delayed_withdrawn IS NULL) "
683 nextIsAnd = true
684 }
685 }
686 if fromItem != nil {
687 if nextIsAnd {
688 queryStr += "AND "
689 } else {
690 queryStr += "WHERE "
691 }
692 if order == OrderAsc {
693 queryStr += "exit_tree.item_id >= ? "
694 } else {
695 queryStr += "exit_tree.item_id <= ? "
696 }
697 args = append(args, fromItem)
698 // nextIsAnd = true
699 }
700 // pagination
701 queryStr += "ORDER BY exit_tree.item_id "
702 if order == OrderAsc {
703 queryStr += " ASC "
704 } else {
705 queryStr += " DESC "
706 }
707 queryStr += fmt.Sprintf("LIMIT %d;", *limit)
708 query = hdb.dbRead.Rebind(queryStr)
709 // log.Debug(query)
710 exits := []*ExitAPI{}
711 if err := meddler.QueryAll(hdb.dbRead, &exits, query, args...); err != nil {
712 return nil, 0, tracerr.Wrap(err)
713 }
714 if len(exits) == 0 {
715 return []ExitAPI{}, 0, nil
716 }
717 return db.SlicePtrsToSlice(exits).([]ExitAPI), exits[0].TotalItems - uint64(len(exits)), nil
718}
719
720// GetCoordinatorsAPI returns a list of coordinators from the DB and pagination info
721func (hdb *HistoryDB) GetCoordinatorsAPI(
722 bidderAddr, forgerAddr *ethCommon.Address,
723 fromItem, limit *uint, order string,
724) ([]CoordinatorAPI, uint64, error) {
725 cancel, err := hdb.apiConnCon.Acquire()
726 defer cancel()
727 if err != nil {
728 return nil, 0, tracerr.Wrap(err)
729 }
730 defer hdb.apiConnCon.Release()
731 var query string
732 var args []interface{}
733 queryStr := `SELECT coordinator.*, COUNT(*) OVER() AS total_items
734 FROM coordinator INNER JOIN (
735 SELECT MAX(item_id) AS item_id FROM coordinator
736 GROUP BY bidder_addr
737 ) c ON coordinator.item_id = c.item_id `
738 // Apply filters
739 nextIsAnd := false
740 if bidderAddr != nil {
741 queryStr += "WHERE bidder_addr = ? "
742 nextIsAnd = true
743 args = append(args, bidderAddr)
744 }
745 if forgerAddr != nil {
746 if nextIsAnd {
747 queryStr += "AND "
748 } else {
749 queryStr += "WHERE "
750 }
751 queryStr += "forger_addr = ? "
752 nextIsAnd = true
753 args = append(args, forgerAddr)
754 }
755 if fromItem != nil {
756 if nextIsAnd {
757 queryStr += "AND "
758 } else {
759 queryStr += "WHERE "
760 }
761 if order == OrderAsc {
762 queryStr += "coordinator.item_id >= ? "
763 } else {
764 queryStr += "coordinator.item_id <= ? "
765 }
766 args = append(args, fromItem)
767 }
768 // pagination
769 queryStr += "ORDER BY coordinator.item_id "
770 if order == OrderAsc {
771 queryStr += " ASC "
772 } else {
773 queryStr += " DESC "
774 }
775 queryStr += fmt.Sprintf("LIMIT %d;", *limit)
776 query = hdb.dbRead.Rebind(queryStr)
777
778 coordinators := []*CoordinatorAPI{}
779 if err := meddler.QueryAll(hdb.dbRead, &coordinators, query, args...); err != nil {
780 return nil, 0, tracerr.Wrap(err)
781 }
782 if len(coordinators) == 0 {
783 return []CoordinatorAPI{}, 0, nil
784 }
785 return db.SlicePtrsToSlice(coordinators).([]CoordinatorAPI),
786 coordinators[0].TotalItems - uint64(len(coordinators)), nil
787}
788
789// GetAuctionVarsAPI returns auction variables
790func (hdb *HistoryDB) GetAuctionVarsAPI() (*common.AuctionVariables, error) {
791 cancel, err := hdb.apiConnCon.Acquire()
792 defer cancel()
793 if err != nil {
794 return nil, tracerr.Wrap(err)
795 }
796 defer hdb.apiConnCon.Release()
797 auctionVars := &common.AuctionVariables{}
798 err = meddler.QueryRow(
799 hdb.dbRead, auctionVars, `SELECT * FROM auction_vars;`,
800 )
801 return auctionVars, tracerr.Wrap(err)
802}
803
804// GetAccountAPI returns an account by its index
805func (hdb *HistoryDB) GetAccountAPI(idx common.Idx) (*AccountAPI, error) {
806 cancel, err := hdb.apiConnCon.Acquire()
807 defer cancel()
808 if err != nil {
809 return nil, tracerr.Wrap(err)
810 }
811 defer hdb.apiConnCon.Release()
812 account := &AccountAPI{}
813 err = meddler.QueryRow(hdb.dbRead, account, `SELECT account.item_id, hez_idx(account.idx,
814 token.symbol) as idx, account.batch_num, account.bjj, account.eth_addr,
815 token.token_id, token.item_id AS token_item_id, token.eth_block_num AS token_block,
816 token.eth_addr as token_eth_addr, token.name, token.symbol, token.decimals, token.usd,
817 token.usd_update, account_update.nonce, account_update.balance
818 FROM account inner JOIN (
819 SELECT idx, nonce, balance
820 FROM account_update
821 WHERE idx = $1
822 ORDER BY item_id DESC LIMIT 1
823 ) AS account_update ON account_update.idx = account.idx
824 INNER JOIN token ON account.token_id = token.token_id
825 WHERE account.idx = $1;`, idx)
826
827 if err != nil {
828 return nil, tracerr.Wrap(err)
829 }
830
831 return account, nil
832}
833
834// GetAccountsAPI returns a list of accounts from the DB and pagination info
835func (hdb *HistoryDB) GetAccountsAPI(
836 tokenIDs []common.TokenID, ethAddr *ethCommon.Address,
837 bjj *babyjub.PublicKeyComp, fromItem, limit *uint, order string,
838) ([]AccountAPI, uint64, error) {
839 if ethAddr != nil && bjj != nil {
840 return nil, 0, tracerr.Wrap(errors.New("ethAddr and bjj are incompatible"))
841 }
842 cancel, err := hdb.apiConnCon.Acquire()
843 defer cancel()
844 if err != nil {
845 return nil, 0, tracerr.Wrap(err)
846 }
847 defer hdb.apiConnCon.Release()
848 var query string
849 var args []interface{}
850 queryStr := `SELECT account.item_id, hez_idx(account.idx, token.symbol) as idx, account.batch_num,
851 account.bjj, account.eth_addr, token.token_id, token.item_id AS token_item_id, token.eth_block_num AS token_block,
852 token.eth_addr as token_eth_addr, token.name, token.symbol, token.decimals, token.usd, token.usd_update,
853 account_update.nonce, account_update.balance, COUNT(*) OVER() AS total_items
854 FROM account inner JOIN (
855 SELECT DISTINCT idx,
856 first_value(nonce) over(partition by idx ORDER BY item_id DESC) as nonce,
857 first_value(balance) over(partition by idx ORDER BY item_id DESC) as balance
858 FROM account_update
859 ) AS account_update ON account_update.idx = account.idx INNER JOIN token ON account.token_id = token.token_id `
860 // Apply filters
861 nextIsAnd := false
862 // ethAddr filter
863 if ethAddr != nil {
864 queryStr += "WHERE account.eth_addr = ? "
865 nextIsAnd = true
866 args = append(args, ethAddr)
867 } else if bjj != nil { // bjj filter
868 queryStr += "WHERE account.bjj = ? "
869 nextIsAnd = true
870 args = append(args, bjj)
871 }
872 // tokenID filter
873 if len(tokenIDs) > 0 {
874 if nextIsAnd {
875 queryStr += "AND "
876 } else {
877 queryStr += "WHERE "
878 }
879 queryStr += "account.token_id IN (?) "
880 args = append(args, tokenIDs)
881 nextIsAnd = true
882 }
883 if fromItem != nil {
884 if nextIsAnd {
885 queryStr += "AND "
886 } else {
887 queryStr += "WHERE "
888 }
889 if order == OrderAsc {
890 queryStr += "account.item_id >= ? "
891 } else {
892 queryStr += "account.item_id <= ? "
893 }
894 args = append(args, fromItem)
895 }
896 // pagination
897 queryStr += "ORDER BY account.item_id "
898 if order == OrderAsc {
899 queryStr += " ASC "
900 } else {
901 queryStr += " DESC "
902 }
903 queryStr += fmt.Sprintf("LIMIT %d;", *limit)
904 query, argsQ, err := sqlx.In(queryStr, args...)
905 if err != nil {
906 return nil, 0, tracerr.Wrap(err)
907 }
908 query = hdb.dbRead.Rebind(query)
909
910 accounts := []*AccountAPI{}
911 if err := meddler.QueryAll(hdb.dbRead, &accounts, query, argsQ...); err != nil {
912 return nil, 0, tracerr.Wrap(err)
913 }
914 if len(accounts) == 0 {
915 return []AccountAPI{}, 0, nil
916 }
917
918 return db.SlicePtrsToSlice(accounts).([]AccountAPI),
919 accounts[0].TotalItems - uint64(len(accounts)), nil
920}
921
922// GetCommonAccountAPI returns the account associated to an account idx
923func (hdb *HistoryDB) GetCommonAccountAPI(idx common.Idx) (*common.Account, error) {
924 cancel, err := hdb.apiConnCon.Acquire()
925 defer cancel()
926 if err != nil {
927 return nil, tracerr.Wrap(err)
928 }
929 defer hdb.apiConnCon.Release()
930 account := &common.Account{}
931 err = meddler.QueryRow(
932 hdb.dbRead, account, `SELECT idx, token_id, batch_num, bjj, eth_addr
933 FROM account WHERE idx = $1;`, idx,
934 )
935 return account, tracerr.Wrap(err)
936}
937
938// GetCoordinatorAPI returns a coordinator by its bidderAddr
939func (hdb *HistoryDB) GetCoordinatorAPI(bidderAddr ethCommon.Address) (*CoordinatorAPI, error) {
940 cancel, err := hdb.apiConnCon.Acquire()
941 defer cancel()
942 if err != nil {
943 return nil, tracerr.Wrap(err)
944 }
945 defer hdb.apiConnCon.Release()
946 return hdb.getCoordinatorAPI(hdb.dbRead, bidderAddr)
947}
948
949func (hdb *HistoryDB) getCoordinatorAPI(d meddler.DB, bidderAddr ethCommon.Address) (*CoordinatorAPI, error) {
950 coordinator := &CoordinatorAPI{}
951 err := meddler.QueryRow(
952 d, coordinator,
953 "SELECT * FROM coordinator WHERE bidder_addr = $1 ORDER BY item_id DESC LIMIT 1;",
954 bidderAddr,
955 )
956 return coordinator, tracerr.Wrap(err)
957}
958
959// GetNodeInfoAPI retusnt he NodeInfo
960func (hdb *HistoryDB) GetNodeInfoAPI() (*NodeInfo, error) {
961 cancel, err := hdb.apiConnCon.Acquire()
962 defer cancel()
963 if err != nil {
964 return nil, tracerr.Wrap(err)
965 }
966 defer hdb.apiConnCon.Release()
967 return hdb.GetNodeInfo()
968}
969
970// GetBucketUpdatesInternalAPI returns the latest bucket updates
971func (hdb *HistoryDB) GetBucketUpdatesInternalAPI() ([]BucketUpdateAPI, error) {
972 var bucketUpdates []*BucketUpdateAPI
973 err := meddler.QueryAll(
974 hdb.dbRead, &bucketUpdates,
975 `SELECT num_bucket, withdrawals FROM bucket_update
976 WHERE item_id in(SELECT max(item_id) FROM bucket_update
977 group by num_bucket)
978 ORDER BY num_bucket ASC;`,
979 )
980 return db.SlicePtrsToSlice(bucketUpdates).([]BucketUpdateAPI), tracerr.Wrap(err)
981}
982
983// GetNextForgersInternalAPI returns next forgers
984func (hdb *HistoryDB) GetNextForgersInternalAPI(auctionVars *common.AuctionVariables,
985 auctionConsts *common.AuctionConstants,
986 lastBlock common.Block, currentSlot, lastClosedSlot int64) ([]NextForgerAPI, error) {
987 secondsPerBlock := int64(15) //nolint:gomnd
988 // currentSlot and lastClosedSlot included
989 limit := uint(lastClosedSlot - currentSlot + 1)
990 bids, _, err := hdb.getBestBidsAPI(hdb.dbRead, ¤tSlot, &lastClosedSlot, nil, &limit, "ASC")
991 if err != nil && tracerr.Unwrap(err) != sql.ErrNoRows {
992 return nil, tracerr.Wrap(err)
993 }
994 nextForgers := []NextForgerAPI{}
995 // Get min bid info
996 var minBidInfo []MinBidInfo
997 if currentSlot >= auctionVars.DefaultSlotSetBidSlotNum {
998 // All min bids can be calculated with the last update of AuctionVariables
999
1000 minBidInfo = []MinBidInfo{{
1001 DefaultSlotSetBid: auctionVars.DefaultSlotSetBid,
1002 DefaultSlotSetBidSlotNum: auctionVars.DefaultSlotSetBidSlotNum,
1003 }}
1004 } else {
1005 // Get all the relevant updates from the DB
1006 minBidInfo, err = hdb.getMinBidInfo(hdb.dbRead, currentSlot, lastClosedSlot)
1007 if err != nil {
1008 return nil, tracerr.Wrap(err)
1009 }
1010 }
1011 // Create nextForger for each slot
1012 for i := currentSlot; i <= lastClosedSlot; i++ {
1013 fromBlock := i*int64(auctionConsts.BlocksPerSlot) +
1014 auctionConsts.GenesisBlockNum
1015 toBlock := (i+1)*int64(auctionConsts.BlocksPerSlot) +
1016 auctionConsts.GenesisBlockNum - 1
1017 nextForger := NextForgerAPI{
1018 Period: Period{
1019 SlotNum: i,
1020 FromBlock: fromBlock,
1021 ToBlock: toBlock,
1022 FromTimestamp: lastBlock.Timestamp.Add(time.Second *
1023 time.Duration(secondsPerBlock*(fromBlock-lastBlock.Num))),
1024 ToTimestamp: lastBlock.Timestamp.Add(time.Second *
1025 time.Duration(secondsPerBlock*(toBlock-lastBlock.Num))),
1026 },
1027 }
1028 foundForger := false
1029 // If there is a bid for a slot, get forger (coordinator)
1030 for j := range bids {
1031 slotNum := bids[j].SlotNum
1032 if slotNum == i {
1033 // There's a bid for the slot
1034 // Check if the bid is greater than the minimum required
1035 for i := 0; i < len(minBidInfo); i++ {
1036 // Find the most recent update
1037 if slotNum >= minBidInfo[i].DefaultSlotSetBidSlotNum {
1038 // Get min bid
1039 minBidSelector := slotNum % int64(len(auctionVars.DefaultSlotSetBid))
1040 minBid := minBidInfo[i].DefaultSlotSetBid[minBidSelector]
1041 // Check if the bid has beaten the minimum
1042 bid, ok := new(big.Int).SetString(string(bids[j].BidValue), 10)
1043 if !ok {
1044 return nil, tracerr.New("Wrong bid value, error parsing it as big.Int")
1045 }
1046 if minBid.Cmp(bid) == 1 {
1047 // Min bid is greater than bid, the slot will be forged by boot coordinator
1048 break
1049 }
1050 foundForger = true
1051 break
1052 }
1053 }
1054 if !foundForger { // There is no bid or it's smaller than the minimum
1055 break
1056 }
1057 coordinator, err := hdb.getCoordinatorAPI(hdb.dbRead, bids[j].Bidder)
1058 if err != nil {
1059 return nil, tracerr.Wrap(err)
1060 }
1061 nextForger.Coordinator = *coordinator
1062 break
1063 }
1064 }
1065 // If there is no bid, the coordinator that will forge is boot coordinator
1066 if !foundForger {
1067 nextForger.Coordinator = CoordinatorAPI{
1068 Forger: auctionVars.BootCoordinator,
1069 URL: auctionVars.BootCoordinatorURL,
1070 }
1071 }
1072 nextForgers = append(nextForgers, nextForger)
1073 }
1074 return nextForgers, nil
1075}
1076
1077// GetMetricsInternalAPI returns the MetricsAPI
1078func (hdb *HistoryDB) GetMetricsInternalAPI(lastBatchNum common.BatchNum) (*MetricsAPI, error) {
1079 var metrics MetricsAPI
1080 // Get the first and last batch of the last 24h and their timestamps
1081 // if u.state.Network.LastBatch == nil {
1082 // return &metrics, nil
1083 // }
1084 type period struct {
1085 FromBatchNum common.BatchNum `meddler:"from_batch_num"`
1086 FromTimestamp time.Time `meddler:"from_timestamp"`
1087 ToBatchNum common.BatchNum `meddler:"-"`
1088 ToTimestamp time.Time `meddler:"to_timestamp"`
1089 }
1090 p := &period{
1091 ToBatchNum: lastBatchNum,
1092 }
1093 if err := meddler.QueryRow(
1094 hdb.dbRead, p, `SELECT
1095 COALESCE (MIN(batch.batch_num), 0) as from_batch_num,
1096 COALESCE (MIN(block.timestamp), NOW()) AS from_timestamp,
1097 COALESCE (MAX(block.timestamp), NOW()) AS to_timestamp
1098 FROM batch INNER JOIN block ON batch.eth_block_num = block.eth_block_num
1099 WHERE block.timestamp >= NOW() - INTERVAL '24 HOURS';`,
1100 ); err != nil {
1101 return nil, tracerr.Wrap(err)
1102 }
1103 // Get the amount of txs of that period
1104 row := hdb.dbRead.QueryRow(
1105 `SELECT COUNT(*) as total_txs FROM tx WHERE tx.batch_num between $1 AND $2;`,
1106 p.FromBatchNum, p.ToBatchNum,
1107 )
1108 var nTxs int
1109 if err := row.Scan(&nTxs); err != nil {
1110 return nil, tracerr.Wrap(err)
1111 }
1112 // Set txs/s
1113 seconds := p.ToTimestamp.Sub(p.FromTimestamp).Seconds()
1114 if seconds == 0 { // Avoid dividing by 0
1115 seconds++
1116 }
1117 metrics.TransactionsPerSecond = float64(nTxs) / seconds
1118 // Set txs/batch
1119 nBatches := p.ToBatchNum - p.FromBatchNum + 1
1120 if nBatches == 0 { // Avoid dividing by 0
1121 nBatches++
1122 }
1123 if (p.ToBatchNum - p.FromBatchNum) > 0 {
1124 fmt.Printf("DBG ntxs: %v, nBatches: %v\n", nTxs, nBatches)
1125 metrics.TransactionsPerBatch = float64(nTxs) /
1126 float64(nBatches)
1127 } else {
1128 metrics.TransactionsPerBatch = 0
1129 }
1130 // Get total fee of that period
1131 row = hdb.dbRead.QueryRow(
1132 `SELECT COALESCE (SUM(total_fees_usd), 0) FROM batch WHERE batch_num between $1 AND $2;`,
1133 p.FromBatchNum, p.ToBatchNum,
1134 )
1135 var totalFee float64
1136 if err := row.Scan(&totalFee); err != nil {
1137 return nil, tracerr.Wrap(err)
1138 }
1139 // Set batch frequency
1140 metrics.BatchFrequency = seconds / float64(nBatches)
1141 if nTxs > 0 {
1142 metrics.AvgTransactionFee = totalFee / float64(nTxs)
1143 } else {
1144 metrics.AvgTransactionFee = 0
1145 }
1146 // Get and set amount of registered accounts
1147 type registeredAccounts struct {
1148 TotalIdx int64 `meddler:"total_idx"`
1149 TotalBJJ int64 `meddler:"total_bjj"`
1150 }
1151 ra := ®isteredAccounts{}
1152 if err := meddler.QueryRow(
1153 hdb.dbRead, ra,
1154 `SELECT COUNT(*) AS total_bjj, COUNT(DISTINCT(bjj)) AS total_idx FROM account;`,
1155 ); err != nil {
1156 return nil, tracerr.Wrap(err)
1157 }
1158 metrics.TotalAccounts = ra.TotalIdx
1159 metrics.TotalBJJs = ra.TotalBJJ
1160 // Get and set estimated time to forge L1 tx
1161 row = hdb.dbRead.QueryRow(
1162 `SELECT COALESCE (AVG(EXTRACT(EPOCH FROM (forged.timestamp - added.timestamp))), 0) FROM tx
1163 INNER JOIN block AS added ON tx.eth_block_num = added.eth_block_num
1164 INNER JOIN batch AS forged_batch ON tx.batch_num = forged_batch.batch_num
1165 INNER JOIN block AS forged ON forged_batch.eth_block_num = forged.eth_block_num
1166 WHERE tx.batch_num between $1 and $2 AND tx.is_l1 AND tx.user_origin;`,
1167 p.FromBatchNum, p.ToBatchNum,
1168 )
1169 var timeToForgeL1 float64
1170 if err := row.Scan(&timeToForgeL1); err != nil {
1171 return nil, tracerr.Wrap(err)
1172 }
1173 metrics.EstimatedTimeToForgeL1 = timeToForgeL1
1174 return &metrics, nil
1175}
1176
1177// GetStateAPI returns the StateAPI
1178func (hdb *HistoryDB) GetStateAPI() (*StateAPI, error) {
1179 cancel, err := hdb.apiConnCon.Acquire()
1180 defer cancel()
1181 if err != nil {
1182 return nil, tracerr.Wrap(err)
1183 }
1184 defer hdb.apiConnCon.Release()
1185 return hdb.getStateAPI(hdb.dbRead)
1186}