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