1package historydb
  2
  3import (
  4	"database/sql"
  5	"errors"
  6	"fmt"
  7
  8	ethCommon "github.com/ethereum/go-ethereum/common"
  9	"github.com/hermeznetwork/hermez-node/common"
 10	"github.com/hermeznetwork/hermez-node/db"
 11	"github.com/hermeznetwork/hermez-node/log"
 12	"github.com/iden3/go-iden3-crypto/babyjub"
 13	"github.com/jmoiron/sqlx"
 14
 15	//nolint:errcheck // driver for postgres DB
 16	_ "github.com/lib/pq"
 17	"github.com/russross/meddler"
 18)
 19
 20// TODO(Edu): Document here how HistoryDB is kept consistent
 21
 22// HistoryDB persist the historic of the rollup
 23type HistoryDB struct {
 24	db *sqlx.DB
 25}
 26
 27// BlockData contains the information of a Block
 28type BlockData struct {
 29	Block *common.Block
 30	// Rollup
 31	// L1UserTxs that were submitted in the block
 32	L1UserTxs        []common.L1Tx
 33	Batches          []BatchData
 34	RegisteredTokens []common.Token
 35	RollupVars       *common.RollupVars
 36	// Auction
 37	Bids                []common.Bid
 38	Coordinators        []common.Coordinator
 39	AuctionVars         *common.AuctionVars
 40	WithdrawDelayerVars *common.WithdrawDelayerVars
 41	// TODO: enable when common.WithdrawalDelayerVars is Merged from Synchronizer PR
 42	// WithdrawalDelayerVars *common.WithdrawalDelayerVars
 43}
 44
 45// BatchData contains the information of a Batch
 46type BatchData struct {
 47	// L1UserTxs that were forged in the batch
 48	L1Batch          bool // TODO: Remove once Batch.ForgeL1TxsNum is a pointer
 49	L1UserTxs        []common.L1Tx
 50	L1CoordinatorTxs []common.L1Tx
 51	L2Txs            []common.L2Tx
 52	CreatedAccounts  []common.Account
 53	ExitTree         []common.ExitInfo
 54	Batch            *common.Batch
 55}
 56
 57// NewBatchData creates an empty BatchData with the slices initialized.
 58func NewBatchData() *BatchData {
 59	return &BatchData{
 60		L1Batch:          false,
 61		L1UserTxs:        make([]common.L1Tx, 0),
 62		L1CoordinatorTxs: make([]common.L1Tx, 0),
 63		L2Txs:            make([]common.L2Tx, 0),
 64		CreatedAccounts:  make([]common.Account, 0),
 65		ExitTree:         make([]common.ExitInfo, 0),
 66		Batch:            &common.Batch{},
 67	}
 68}
 69
 70// NewHistoryDB initialize the DB
 71func NewHistoryDB(db *sqlx.DB) *HistoryDB {
 72	return &HistoryDB{db: db}
 73}
 74
 75// AddBlock insert a block into the DB
 76func (hdb *HistoryDB) AddBlock(block *common.Block) error { return hdb.addBlock(hdb.db, block) }
 77func (hdb *HistoryDB) addBlock(d meddler.DB, block *common.Block) error {
 78	return meddler.Insert(d, "block", block)
 79}
 80
 81// AddBlocks inserts blocks into the DB
 82func (hdb *HistoryDB) AddBlocks(blocks []common.Block) error {
 83	return hdb.addBlocks(hdb.db, blocks)
 84}
 85
 86func (hdb *HistoryDB) addBlocks(d meddler.DB, blocks []common.Block) error {
 87	return db.BulkInsert(
 88		d,
 89		`INSERT INTO block (
 90			eth_block_num,
 91			timestamp,
 92			hash
 93		) VALUES %s;`,
 94		blocks[:],
 95	)
 96}
 97
 98// GetBlock retrieve a block from the DB, given a block number
 99func (hdb *HistoryDB) GetBlock(blockNum int64) (*common.Block, error) {
100	block := &common.Block{}
101	err := meddler.QueryRow(
102		hdb.db, block,
103		"SELECT * FROM block WHERE eth_block_num = $1;", blockNum,
104	)
105	return block, err
106}
107
108// GetBlocks retrieve blocks from the DB, given a range of block numbers defined by from and to
109func (hdb *HistoryDB) GetBlocks(from, to int64) ([]common.Block, error) {
110	var blocks []*common.Block
111	err := meddler.QueryAll(
112		hdb.db, &blocks,
113		"SELECT * FROM block WHERE $1 <= eth_block_num AND eth_block_num < $2;",
114		from, to,
115	)
116	return db.SlicePtrsToSlice(blocks).([]common.Block), err
117}
118
119// GetLastBlock retrieve the block with the highest block number from the DB
120func (hdb *HistoryDB) GetLastBlock() (*common.Block, error) {
121	block := &common.Block{}
122	err := meddler.QueryRow(
123		hdb.db, block, "SELECT * FROM block ORDER BY eth_block_num DESC LIMIT 1;",
124	)
125	return block, err
126}
127
128// AddBatch insert a Batch into the DB
129func (hdb *HistoryDB) AddBatch(batch *common.Batch) error { return hdb.addBatch(hdb.db, batch) }
130func (hdb *HistoryDB) addBatch(d meddler.DB, batch *common.Batch) error {
131	return meddler.Insert(d, "batch", batch)
132}
133
134// AddBatches insert Bids into the DB
135func (hdb *HistoryDB) AddBatches(batches []common.Batch) error {
136	return hdb.addBatches(hdb.db, batches)
137}
138func (hdb *HistoryDB) addBatches(d meddler.DB, batches []common.Batch) error {
139	return db.BulkInsert(
140		d,
141		`INSERT INTO batch (
142			batch_num,
143			eth_block_num,
144			forger_addr,
145			fees_collected,
146			state_root,
147			num_accounts,
148			exit_root,
149			forge_l1_txs_num,
150			slot_num,
151			total_fees_usd
152		) VALUES %s;`,
153		batches[:],
154	)
155}
156
157// GetBatches retrieve batches from the DB, given a range of batch numbers defined by from and to
158func (hdb *HistoryDB) GetBatches(from, to common.BatchNum) ([]common.Batch, error) {
159	var batches []*common.Batch
160	err := meddler.QueryAll(
161		hdb.db, &batches,
162		"SELECT * FROM batch WHERE $1 <= batch_num AND batch_num < $2;",
163		from, to,
164	)
165	return db.SlicePtrsToSlice(batches).([]common.Batch), err
166}
167
168// GetLastBatchNum returns the BatchNum of the latest forged batch
169func (hdb *HistoryDB) GetLastBatchNum() (common.BatchNum, error) {
170	row := hdb.db.QueryRow("SELECT batch_num FROM batch ORDER BY batch_num DESC LIMIT 1;")
171	var batchNum common.BatchNum
172	return batchNum, row.Scan(&batchNum)
173}
174
175// GetLastL1TxsNum returns the greatest ForgeL1TxsNum in the DB.  If there's no
176// batch in the DB (nil, nil) is returned.
177func (hdb *HistoryDB) GetLastL1TxsNum() (*int64, error) {
178	row := hdb.db.QueryRow("SELECT MAX(forge_l1_txs_num) FROM batch;")
179	lastL1TxsNum := new(int64)
180	return lastL1TxsNum, row.Scan(&lastL1TxsNum)
181}
182
183// Reorg deletes all the information that was added into the DB after the
184// lastValidBlock.  If lastValidBlock is negative, all block information is
185// deleted.
186func (hdb *HistoryDB) Reorg(lastValidBlock int64) error {
187	var err error
188	if lastValidBlock < 0 {
189		_, err = hdb.db.Exec("DELETE FROM block;")
190	} else {
191		_, err = hdb.db.Exec("DELETE FROM block WHERE eth_block_num > $1;", lastValidBlock)
192	}
193	return err
194}
195
196// SyncPoD stores all the data that can be changed / added on a block in the PoD SC
197func (hdb *HistoryDB) SyncPoD(
198	blockNum uint64,
199	bids []common.Bid,
200	coordinators []common.Coordinator,
201	vars *common.AuctionVars,
202) error {
203	return nil
204}
205
206// AddBids insert Bids into the DB
207func (hdb *HistoryDB) AddBids(bids []common.Bid) error { return hdb.addBids(hdb.db, bids) }
208func (hdb *HistoryDB) addBids(d meddler.DB, bids []common.Bid) error {
209	// TODO: check the coordinator info
210	return db.BulkInsert(
211		d,
212		"INSERT INTO bid (slot_num, bid_value, eth_block_num, bidder_addr) VALUES %s;",
213		bids[:],
214	)
215}
216
217// GetBids return the bids
218func (hdb *HistoryDB) GetBids() ([]common.Bid, error) {
219	var bids []*common.Bid
220	err := meddler.QueryAll(
221		hdb.db, &bids,
222		"SELECT * FROM bid;",
223	)
224	return db.SlicePtrsToSlice(bids).([]common.Bid), err
225}
226
227// AddCoordinators insert Coordinators into the DB
228func (hdb *HistoryDB) AddCoordinators(coordinators []common.Coordinator) error {
229	return hdb.addCoordinators(hdb.db, coordinators)
230}
231func (hdb *HistoryDB) addCoordinators(d meddler.DB, coordinators []common.Coordinator) error {
232	return db.BulkInsert(
233		d,
234		"INSERT INTO coordinator (bidder_addr, forger_addr, eth_block_num, url) VALUES %s;",
235		coordinators[:],
236	)
237}
238
239// AddExitTree insert Exit tree into the DB
240func (hdb *HistoryDB) AddExitTree(exitTree []common.ExitInfo) error {
241	return hdb.addExitTree(hdb.db, exitTree)
242}
243func (hdb *HistoryDB) addExitTree(d meddler.DB, exitTree []common.ExitInfo) error {
244	return db.BulkInsert(
245		d,
246		"INSERT INTO exit_tree (batch_num, account_idx, merkle_proof, balance, "+
247			"instant_withdrawn, delayed_withdraw_request, delayed_withdrawn) VALUES %s;",
248		exitTree[:],
249	)
250}
251
252// AddToken insert a token into the DB
253func (hdb *HistoryDB) AddToken(token *common.Token) error {
254	return meddler.Insert(hdb.db, "token", token)
255}
256
257// AddTokens insert tokens into the DB
258func (hdb *HistoryDB) AddTokens(tokens []common.Token) error { return hdb.addTokens(hdb.db, tokens) }
259func (hdb *HistoryDB) addTokens(d meddler.DB, tokens []common.Token) error {
260	return db.BulkInsert(
261		d,
262		`INSERT INTO token (
263			token_id,
264			eth_block_num,
265			eth_addr,
266			name,
267			symbol,
268			decimals,
269			usd,
270			usd_update
271		) VALUES %s;`,
272		tokens[:],
273	)
274}
275
276// UpdateTokenValue updates the USD value of a token
277func (hdb *HistoryDB) UpdateTokenValue(tokenSymbol string, value float64) error {
278	_, err := hdb.db.Exec(
279		"UPDATE token SET usd = $1 WHERE symbol = $2;",
280		value, tokenSymbol,
281	)
282	return err
283}
284
285// GetTokens returns a list of tokens from the DB
286func (hdb *HistoryDB) GetTokens() ([]common.Token, error) {
287	var tokens []*common.Token
288	err := meddler.QueryAll(
289		hdb.db, &tokens,
290		"SELECT * FROM token ORDER BY token_id;",
291	)
292	return db.SlicePtrsToSlice(tokens).([]common.Token), err
293}
294
295// GetTokenSymbols returns all the token symbols from the DB
296func (hdb *HistoryDB) GetTokenSymbols() ([]string, error) {
297	var tokenSymbols []string
298	rows, err := hdb.db.Query("SELECT symbol FROM token;")
299	if err != nil {
300		return nil, err
301	}
302	sym := new(string)
303	for rows.Next() {
304		err = rows.Scan(sym)
305		if err != nil {
306			return nil, err
307		}
308		tokenSymbols = append(tokenSymbols, *sym)
309	}
310	return tokenSymbols, nil
311}
312
313// AddAccounts insert accounts into the DB
314func (hdb *HistoryDB) AddAccounts(accounts []common.Account) error {
315	return hdb.addAccounts(hdb.db, accounts)
316}
317func (hdb *HistoryDB) addAccounts(d meddler.DB, accounts []common.Account) error {
318	return db.BulkInsert(
319		d,
320		`INSERT INTO account (
321			idx,
322			token_id,
323			batch_num,
324			bjj,
325			eth_addr
326		) VALUES %s;`,
327		accounts[:],
328	)
329}
330
331// GetAccounts returns a list of accounts from the DB
332func (hdb *HistoryDB) GetAccounts() ([]common.Account, error) {
333	var accs []*common.Account
334	err := meddler.QueryAll(
335		hdb.db, &accs,
336		"SELECT * FROM account ORDER BY idx;",
337	)
338	return db.SlicePtrsToSlice(accs).([]common.Account), err
339}
340
341// AddL1Txs inserts L1 txs to the DB. USD and LoadAmountUSD will be set automatically before storing the tx.
342// If the tx is originated by a coordinator, BatchNum must be provided. If it's originated by a user,
343// BatchNum should be null, and the value will be setted by a trigger when a batch forges the tx.
344func (hdb *HistoryDB) AddL1Txs(l1txs []common.L1Tx) error { return hdb.addL1Txs(hdb.db, l1txs) }
345
346// addL1Txs inserts L1 txs to the DB. USD and LoadAmountUSD will be set automatically before storing the tx.
347// If the tx is originated by a coordinator, BatchNum must be provided. If it's originated by a user,
348// BatchNum should be null, and the value will be setted by a trigger when a batch forges the tx.
349func (hdb *HistoryDB) addL1Txs(d meddler.DB, l1txs []common.L1Tx) error {
350	txs := []common.Tx{}
351	for _, tx := range l1txs {
352		txs = append(txs, *(tx.Tx()))
353	}
354	return hdb.addTxs(d, txs)
355}
356
357// AddL2Txs inserts L2 txs to the DB. USD and FeeUSD will be set automatically before storing the tx.
358func (hdb *HistoryDB) AddL2Txs(l2txs []common.L2Tx) error { return hdb.addL2Txs(hdb.db, l2txs) }
359
360// addL2Txs inserts L2 txs to the DB. USD and FeeUSD will be set automatically before storing the tx.
361func (hdb *HistoryDB) addL2Txs(d meddler.DB, l2txs []common.L2Tx) error {
362	txs := []common.Tx{}
363	for _, tx := range l2txs {
364		txs = append(txs, *(tx.Tx()))
365	}
366	return hdb.addTxs(d, txs)
367}
368
369func (hdb *HistoryDB) addTxs(d meddler.DB, txs []common.Tx) error {
370	return db.BulkInsert(
371		d,
372		`INSERT INTO tx (
373			is_l1,
374			id,
375			type,
376			position,
377			from_idx,
378			to_idx,
379			amount,
380			amount_f,
381			token_id,
382			amount_usd,
383			batch_num,
384			eth_block_num,
385			to_forge_l1_txs_num,
386			user_origin,
387			from_eth_addr,
388			from_bjj,
389			load_amount,
390			load_amount_f,
391			load_amount_usd,
392			fee,
393			fee_usd,
394			nonce
395		) VALUES %s;`,
396		txs[:],
397	)
398}
399
400// GetTxs returns a list of txs from the DB
401func (hdb *HistoryDB) GetTxs() ([]common.Tx, error) {
402	var txs []*common.Tx
403	err := meddler.QueryAll(
404		hdb.db, &txs,
405		`SELECT * FROM tx 
406		ORDER BY (batch_num, position) ASC`,
407	)
408	return db.SlicePtrsToSlice(txs).([]common.Tx), err
409}
410
411// GetHistoryTxs returns a list of txs from the DB using the HistoryTx struct
412func (hdb *HistoryDB) GetHistoryTxs(
413	ethAddr *ethCommon.Address, bjj *babyjub.PublicKey,
414	tokenID, idx, batchNum *uint, txType *common.TxType,
415	offset, limit *uint, last bool,
416) ([]HistoryTx, int, error) {
417	if ethAddr != nil && bjj != nil {
418		return nil, 0, errors.New("ethAddr and bjj are incompatible")
419	}
420	var query string
421	var args []interface{}
422	queryStr := `SELECT tx.*, token.token_id, token.eth_block_num AS token_block,
423	token.eth_addr, token.name, token.symbol, token.decimals, token.usd,
424	token.usd_update, block.timestamp, count(*) OVER() AS total_items 
425	FROM tx 
426	INNER JOIN token ON tx.token_id = token.token_id 
427	INNER JOIN block ON tx.eth_block_num = block.eth_block_num `
428	// Apply filters
429	nextIsAnd := false
430	// ethAddr filter
431	if ethAddr != nil {
432		queryStr = `WITH acc AS 
433		(select idx from account where eth_addr = ?) ` + queryStr
434		queryStr += ", acc WHERE (tx.from_idx IN(acc.idx) OR tx.to_idx IN(acc.idx)) "
435		nextIsAnd = true
436		args = append(args, ethAddr)
437	} else if bjj != nil { // bjj filter
438		queryStr = `WITH acc AS 
439		(select idx from account where bjj = ?) ` + queryStr
440		queryStr += ", acc WHERE (tx.from_idx IN(acc.idx) OR tx.to_idx IN(acc.idx)) "
441		nextIsAnd = true
442		args = append(args, bjj)
443	}
444	// tokenID filter
445	if tokenID != nil {
446		if nextIsAnd {
447			queryStr += "AND "
448		} else {
449			queryStr += "WHERE "
450		}
451		queryStr += "tx.token_id = ? "
452		args = append(args, tokenID)
453		nextIsAnd = true
454	}
455	// idx filter
456	if idx != nil {
457		if nextIsAnd {
458			queryStr += "AND "
459		} else {
460			queryStr += "WHERE "
461		}
462		queryStr += "(tx.from_idx = ? OR tx.to_idx = ?) "
463		args = append(args, idx, idx)
464		nextIsAnd = true
465	}
466	// batchNum filter
467	if batchNum != nil {
468		if nextIsAnd {
469			queryStr += "AND "
470		} else {
471			queryStr += "WHERE "
472		}
473		queryStr += "tx.batch_num = ? "
474		args = append(args, batchNum)
475		nextIsAnd = true
476	}
477	// txType filter
478	if txType != nil {
479		if nextIsAnd {
480			queryStr += "AND "
481		} else {
482			queryStr += "WHERE "
483		}
484		queryStr += "tx.type = ? "
485		args = append(args, txType)
486		// nextIsAnd = true
487	}
488	// pagination
489	if last {
490		queryStr += "ORDER BY (batch_num, position) DESC NULLS FIRST "
491	} else {
492		queryStr += "ORDER BY (batch_num, position) ASC NULLS LAST "
493		queryStr += fmt.Sprintf("OFFSET %d ", *offset)
494	}
495	queryStr += fmt.Sprintf("LIMIT %d;", *limit)
496	query = hdb.db.Rebind(queryStr)
497	// log.Debug(query)
498	txsPtrs := []*HistoryTx{}
499	if err := meddler.QueryAll(hdb.db, &txsPtrs, query, args...); err != nil {
500		return nil, 0, err
501	}
502	txs := db.SlicePtrsToSlice(txsPtrs).([]HistoryTx)
503	if len(txs) == 0 {
504		return nil, 0, sql.ErrNoRows
505	} else if last {
506		tmp := []HistoryTx{}
507		for i := len(txs) - 1; i >= 0; i-- {
508			tmp = append(tmp, txs[i])
509		}
510		txs = tmp
511	}
512	return txs, txs[0].TotalItems, nil
513}
514
515// GetTx returns a tx from the DB
516func (hdb *HistoryDB) GetTx(txID common.TxID) (*common.Tx, error) {
517	tx := new(common.Tx)
518	return tx, meddler.QueryRow(
519		hdb.db, tx,
520		"SELECT * FROM tx WHERE id = $1;",
521		txID,
522	)
523}
524
525// // GetL1UserTxs gets L1 User Txs to be forged in a batch that will create an account
526// // TODO: This is currently not used.  Figure out if it should be used somewhere or removed.
527// func (hdb *HistoryDB) GetL1UserTxs(toForgeL1TxsNum int64) ([]*common.Tx, error) {
528// 	var txs []*common.Tx
529// 	err := meddler.QueryAll(
530// 		hdb.db, &txs,
531// 		"SELECT * FROM tx WHERE to_forge_l1_txs_num = $1 AND is_l1 = TRUE AND user_origin = TRUE;",
532// 		toForgeL1TxsNum,
533// 	)
534// 	return txs, err
535// }
536
537// TODO: Think about chaning all the queries that return a last value, to queries that return the next valid value.
538
539// GetLastTxsPosition for a given to_forge_l1_txs_num
540func (hdb *HistoryDB) GetLastTxsPosition(toForgeL1TxsNum int64) (int, error) {
541	row := hdb.db.QueryRow("SELECT MAX(position) FROM tx WHERE to_forge_l1_txs_num = $1;", toForgeL1TxsNum)
542	var lastL1TxsPosition int
543	return lastL1TxsPosition, row.Scan(&lastL1TxsPosition)
544}
545
546// AddBlockSCData stores all the information of a block retrieved by the Synchronizer
547func (hdb *HistoryDB) AddBlockSCData(blockData *BlockData) (err error) {
548	txn, err := hdb.db.Begin()
549	if err != nil {
550		return err
551	}
552	defer func() {
553		if err != nil {
554			errRollback := txn.Rollback()
555			if errRollback != nil {
556				log.Errorw("Rollback", "err", errRollback)
557			}
558		}
559	}()
560
561	// Add block
562	err = hdb.addBlock(txn, blockData.Block)
563	if err != nil {
564		return err
565	}
566
567	// Add Coordinators
568	if len(blockData.Coordinators) > 0 {
569		err = hdb.addCoordinators(txn, blockData.Coordinators)
570		if err != nil {
571			return err
572		}
573	}
574
575	// Add Bids
576	if len(blockData.Bids) > 0 {
577		err = hdb.addBids(txn, blockData.Bids)
578		if err != nil {
579			return err
580		}
581	}
582
583	// Add Tokens
584	if len(blockData.RegisteredTokens) > 0 {
585		err = hdb.addTokens(txn, blockData.RegisteredTokens)
586		if err != nil {
587			return err
588		}
589	}
590
591	// Add l1 Txs
592	if len(blockData.L1UserTxs) > 0 {
593		err = hdb.addL1Txs(txn, blockData.L1UserTxs)
594		if err != nil {
595			return err
596		}
597	}
598
599	// Add Batches
600	for _, batch := range blockData.Batches {
601		// Add Batch: this will trigger an update on the DB
602		// that will set the batch num of forged L1 txs in this batch
603		err = hdb.addBatch(txn, batch.Batch)
604		if err != nil {
605			return err
606		}
607
608		// Add unforged l1 Txs
609		if batch.L1Batch {
610			if len(batch.L1CoordinatorTxs) > 0 {
611				err = hdb.addL1Txs(txn, batch.L1CoordinatorTxs)
612				if err != nil {
613					return err
614				}
615			}
616		}
617
618		// Add l2 Txs
619		if len(batch.L2Txs) > 0 {
620			err = hdb.addL2Txs(txn, batch.L2Txs)
621			if err != nil {
622				return err
623			}
624		}
625
626		// Add accounts
627		if len(batch.CreatedAccounts) > 0 {
628			err = hdb.addAccounts(txn, batch.CreatedAccounts)
629			if err != nil {
630				return err
631			}
632		}
633
634		// Add exit tree
635		if len(batch.ExitTree) > 0 {
636			err = hdb.addExitTree(txn, batch.ExitTree)
637			if err != nil {
638				return err
639			}
640		}
641
642		// TODO: INSERT CONTRACTS VARS
643	}
644
645	return txn.Commit()
646}