code.vegaprotocol.io/vega@v0.79.0/datanode/sqlstore/proposals.go

// Copyright (C) 2023 Gobalsky Labs Limited
//
// This program is free software: you can redistribute it and/or modify
// it under the terms of the GNU Affero General Public License as
// published by the Free Software Foundation, either version 3 of the
// License, or (at your option) any later version.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU Affero General Public License for more details.
//
// You should have received a copy of the GNU Affero General Public License
// along with this program.  If not, see <http://www.gnu.org/licenses/>.

package sqlstore

import (
	"context"
	"fmt"
	"sort"
	"strings"

	"code.vegaprotocol.io/vega/datanode/entities"
	"code.vegaprotocol.io/vega/datanode/metrics"
	v2 "code.vegaprotocol.io/vega/protos/data-node/api/v2"

	"github.com/georgysavva/scany/pgxscan"
	"github.com/jackc/pgx/v4"
)

type Proposals struct {
	*ConnectionSource
}

var proposalsOrdering = TableOrdering{
	ColumnOrdering{Name: "vega_time", Sorting: ASC},
	ColumnOrdering{Name: "id", Sorting: ASC},
}

func NewProposals(connectionSource *ConnectionSource) *Proposals {
	p := &Proposals{
		ConnectionSource: connectionSource,
	}
	return p
}

func (ps *Proposals) Add(ctx context.Context, p entities.Proposal) error {
	defer metrics.StartSQLQuery("Proposals", "Add")()
	_, err := ps.Exec(ctx,
		`INSERT INTO proposals(
			id,
			batch_id,
			reference,
			party_id,
			state,
			terms,
			batch_terms,
			rationale,
			reason,
			error_details,
			proposal_time,
			vega_time,
			required_majority,
			required_participation,
			required_lp_majority,
			required_lp_participation,
			tx_hash)
		 VALUES ($1,  $2,  $3,  $4,  $5,  $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17)
		 ON CONFLICT (id, vega_time) DO UPDATE SET
			reference = EXCLUDED.reference,
			party_id = EXCLUDED.party_id,
			state = EXCLUDED.state,
			terms = EXCLUDED.terms,
			rationale = EXCLUDED.rationale,
			reason = EXCLUDED.reason,
			error_details = EXCLUDED.error_details,
			proposal_time = EXCLUDED.proposal_time,
			tx_hash = EXCLUDED.tx_hash
			;
		 `,
		p.ID, p.BatchID, p.Reference, p.PartyID, p.State, p.Terms, p.BatchTerms, p.Rationale, p.Reason,
		p.ErrorDetails, p.ProposalTime, p.VegaTime, p.RequiredMajority, p.RequiredParticipation,
		p.RequiredLPMajority, p.RequiredLPParticipation, p.TxHash)
	return err
}

func (ps *Proposals) getProposalsInBatch(ctx context.Context, batchID string) ([]entities.Proposal, error) {
	var proposals []entities.Proposal
	query := `SELECT * FROM proposals_current WHERE batch_id=$1`

	rows, err := ps.Query(ctx, query, entities.ProposalID(batchID))
	if err != nil {
		return proposals, fmt.Errorf("querying proposals: %w", err)
	}
	defer rows.Close()

	if err = pgxscan.ScanAll(&proposals, rows); err != nil {
		return proposals, fmt.Errorf("parsing proposals: %w", err)
	}

	sort.Slice(proposals, func(i, j int) bool {
		return proposals[i].Terms.EnactmentTimestamp < proposals[j].Terms.EnactmentTimestamp
	})

	return proposals, nil
}

// extendOrGetBatchProposal fetching sub proposals in case of batch proposal
// or fetches the whole batch if sub proposal is requested.
// If none of the above applies then proposal is returned without change.
func (ps *Proposals) extendOrGetBatchProposal(ctx context.Context, p entities.Proposal) (entities.Proposal, error) {
	// if proposal is part of batch fetch to whole batch
	if p.BelongsToBatch() {
		return ps.GetByID(ctx, p.BatchID.String())
	}

	// if it's batch fetch the sub proposals
	if p.IsBatch() {
		pps, err := ps.getProposalsInBatch(ctx, p.ID.String())
		if err != nil {
			return p, ps.wrapE(err)
		}
		p.Proposals = pps
	}

	return p, nil
}

func (ps *Proposals) GetByID(ctx context.Context, id string) (entities.Proposal, error) {
	defer metrics.StartSQLQuery("Proposals", "GetByID")()
	var p entities.Proposal
	query := `SELECT * FROM proposals_current WHERE id=$1`

	if err := pgxscan.Get(ctx, ps.ConnectionSource, &p, query, entities.ProposalID(id)); err != nil {
		return p, ps.wrapE(pgxscan.Get(ctx, ps.ConnectionSource, &p, query, entities.ProposalID(id)))
	}

	p, err := ps.extendOrGetBatchProposal(ctx, p)
	if err != nil {
		return p, err
	}

	return p, nil
}

// GetByIDWithoutBatch returns a proposal without extending single proposal by fetching batch proposal.
func (ps *Proposals) GetByIDWithoutBatch(ctx context.Context, id string) (entities.Proposal, error) {
	defer metrics.StartSQLQuery("Proposals", "GetByIDWithoutBatch")()
	var p entities.Proposal
	query := `SELECT * FROM proposals_current WHERE id=$1`

	if err := pgxscan.Get(ctx, ps.ConnectionSource, &p, query, entities.ProposalID(id)); err != nil {
		return p, ps.wrapE(pgxscan.Get(ctx, ps.ConnectionSource, &p, query, entities.ProposalID(id)))
	}

	return p, nil
}

func (ps *Proposals) GetByReference(ctx context.Context, ref string) (entities.Proposal, error) {
	defer metrics.StartSQLQuery("Proposals", "GetByReference")()
	var p entities.Proposal
	query := `SELECT * FROM proposals_current WHERE reference=$1 LIMIT 1`

	if err := pgxscan.Get(ctx, ps.ConnectionSource, &p, query, ref); err != nil {
		return p, ps.wrapE(err)
	}

	p, err := ps.extendOrGetBatchProposal(ctx, p)
	if err != nil {
		return p, err
	}

	return p, nil
}

func (ps *Proposals) GetByTxHash(ctx context.Context, txHash entities.TxHash) ([]entities.Proposal, error) {
	defer metrics.StartSQLQuery("Proposals", "GetByTxHash")()

	var proposals []entities.Proposal
	query := `SELECT * FROM proposals WHERE tx_hash=$1`
	err := pgxscan.Select(ctx, ps.ConnectionSource, &proposals, query, txHash)
	if err != nil {
		return nil, ps.wrapE(err)
	}

	for i, p := range proposals {
		p, err := ps.extendOrGetBatchProposal(ctx, p)
		if err != nil {
			return proposals, err
		}

		proposals[i] = p
	}

	return proposals, nil
}

func getOpenStateProposalsQuery(inState *entities.ProposalState, conditions []string, pagination entities.CursorPagination,
	pc *entities.ProposalCursor, pageForward bool, args ...interface{},
) (string, []interface{}, error) {
	// if we're querying for a specific state and it's not the Open state,
	// or if we are paging forward and the current state is not the open state
	// then we do not need to query for any open state proposals
	if (inState != nil && *inState != entities.ProposalStateOpen) ||
		(pageForward && pc.State != entities.ProposalStateUnspecified && pc.State != entities.ProposalStateOpen) {
		// we aren't interested in open proposals so the query should be empty
		return "", args, nil
	}

	if pc.State != entities.ProposalStateOpen {
		if pagination.HasForward() {
			pagination.Forward.Cursor = nil
		} else if pagination.HasBackward() {
			pagination.Backward.Cursor = nil
		}
	}

	conditions = append([]string{
		fmt.Sprintf("state=%s", nextBindVar(&args, entities.ProposalStateOpen)),
	}, conditions...)

	query := `select * from proposals_current`

	if len(conditions) > 0 {
		query = fmt.Sprintf("%s WHERE %s", query, strings.Join(conditions, " AND "))
	}

	var err error
	query, args, err = PaginateQuery[entities.ProposalCursor](query, args, proposalsOrdering, pagination)
	if err != nil {
		return "", args, err
	}

	return query, args, nil
}

func getOtherStateProposalsQuery(inState *entities.ProposalState, conditions []string, pagination entities.CursorPagination,
	pc *entities.ProposalCursor, pageForward bool, args ...interface{},
) (string, []interface{}, error) {
	// if we're filtering for state and the state is open,
	// or we're paging forward, and the cursor has reached the open proposals
	// then we don't need to return any non-open proposal results
	if (inState != nil && *inState == entities.ProposalStateOpen) || (!pageForward && pc.State == entities.ProposalStateOpen) {
		// the open state query should already be providing the correct query for this
		return "", args, nil
	}

	if pagination.HasForward() {
		if pc.State == entities.ProposalStateOpen || pc.State == entities.ProposalStateUnspecified {
			pagination.Forward.Cursor = nil
		}
	} else if pagination.HasBackward() {
		if pc.State == entities.ProposalStateOpen || pc.State == entities.ProposalStateUnspecified {
			pagination.Backward.Cursor = nil
		}
	}

	if inState == nil {
		conditions = append([]string{
			fmt.Sprintf("state!=%s", nextBindVar(&args, entities.ProposalStateOpen)),
		}, conditions...)
	} else {
		conditions = append([]string{
			fmt.Sprintf("state=%s", nextBindVar(&args, *inState)),
		}, conditions...)
	}
	query := `select * from proposals_current`

	if len(conditions) > 0 {
		query = fmt.Sprintf("%s WHERE %s", query, strings.Join(conditions, " AND "))
	}

	var err error
	query, args, err = PaginateQuery[entities.ProposalCursor](query, args, proposalsOrdering, pagination)
	if err != nil {
		return "", args, err
	}
	return query, args, nil
}

func clonePagination(p entities.CursorPagination) (entities.CursorPagination, error) {
	var first, last int32
	var after, before string

	var pFirst, pLast *int32
	var pAfter, pBefore *string

	if p.HasForward() {
		first = *p.Forward.Limit
		pFirst = &first
		if p.Forward.HasCursor() {
			after = p.Forward.Cursor.Encode()
			pAfter = &after
		}
	}

	if p.HasBackward() {
		last = *p.Backward.Limit
		pLast = &last
		if p.Backward.HasCursor() {
			before = p.Backward.Cursor.Encode()
			pBefore = &before
		}
	}

	return entities.NewCursorPagination(pFirst, pAfter, pLast, pBefore, p.NewestFirst)
}

func (ps *Proposals) Get(ctx context.Context,
	inState *entities.ProposalState,
	partyIDStr *string,
	proposalType *entities.ProposalType,
	pagination entities.CursorPagination,
) ([]entities.Proposal, entities.PageInfo, error) {
	// This one is a bit tricky because we want all the open proposals listed at the top, sorted by date
	// then other proposals in date order regardless of state.

	// In order to do this, we need to construct a union of proposals where state = open, order by vega_time
	// and state != open, order by vega_time
	// If the cursor has been set, and we're traversing forward (newest-oldest), then we need to check if the
	// state of the cursor is = open. If it is then we should append the open state proposals with the non-open state
	// proposals.
	// If the cursor state is != open, we have navigated passed the open state proposals and only need the non-open state proposals.

	// If the cursor has been set and we're traversing backward (newest-oldest), then we need to check if the
	// state of the cursor is = open. If it is then we should only return the proposals where state = open as we've already navigated
	// passed all the non-open proposals.
	// if the state of the cursor is != open, then we need to append all the proposals where the state = open after the proposals where
	// state != open.

	// This combined results of both queries is then wrapped with another select which should return the appropriate number of rows that
	// are required for the pagination to determine whether or not there are any next/previous rows for the pageInfo.
	var (
		pageInfo        entities.PageInfo
		stateOpenQuery  string
		stateOtherQuery string
		stateOpenArgs   []interface{}
		stateOtherArgs  []interface{}
	)
	args := make([]interface{}, 0)
	cursor := extractCursorFromPagination(pagination)

	pc := &entities.ProposalCursor{}

	if cursor != "" {
		err := pc.Parse(cursor)
		if err != nil {
			return nil, pageInfo, err
		}
	}

	pageForward := pagination.HasForward() || (!pagination.HasForward() && !pagination.HasBackward())
	var conditions []string

	if partyIDStr != nil {
		partyID := entities.PartyID(*partyIDStr)
		conditions = append(conditions, fmt.Sprintf("party_id=%s", nextBindVar(&args, partyID)))
	}

	if proposalType != nil && *proposalType != entities.ProposalTypeAll {
		conditions = append(conditions, fmt.Sprintf("terms ? %s", nextBindVar(&args, proposalType.String())))
	}

	var err error
	var openPagination, otherPagination entities.CursorPagination
	// we need to clone the pagination objects because we need to alter the pagination data for the different states
	// to support the required ordering of the data
	openPagination, err = clonePagination(pagination)
	if err != nil {
		return nil, pageInfo, fmt.Errorf("invalid pagination: %w", err)
	}
	otherPagination, err = clonePagination(pagination)
	if err != nil {
		return nil, pageInfo, fmt.Errorf("invalid pagination: %w", err)
	}

	stateOpenQuery, stateOpenArgs, err = getOpenStateProposalsQuery(inState, conditions, openPagination, pc, pageForward, args...)
	if err != nil {
		return nil, pageInfo, err
	}
	stateOtherQuery, stateOtherArgs, err = getOtherStateProposalsQuery(inState, conditions, otherPagination, pc, pageForward, args...)
	if err != nil {
		return nil, pageInfo, err
	}

	batch := &pgx.Batch{}

	if stateOpenQuery != "" {
		batch.Queue(stateOpenQuery, stateOpenArgs...)
	}

	if stateOtherQuery != "" {
		batch.Queue(stateOtherQuery, stateOtherArgs...)
	}

	defer metrics.StartSQLQuery("Proposals", "Get")()
	// copy the store connection because we may need to make recursive calls when processing the from the batch
	// causing the underlying connection to be busy and unusable
	batchConn := ps.ConnectionSource
	results := batchConn.SendBatch(ctx, batch)
	defer results.Close()

	proposals := make([]entities.Proposal, 0)
	fetchedBatches := map[entities.ProposalID]struct{}{}

	for {
		rows, err := results.Query()
		if err != nil {
			break
		}

		var matchedProps []entities.Proposal
		if err := pgxscan.ScanAll(&matchedProps, rows); err != nil {
			return nil, pageInfo, fmt.Errorf("querying proposals: %w", err)
		}

		rows.Close()

		var props []entities.Proposal
		for _, p := range matchedProps {
			var batchID entities.ProposalID
			if p.BelongsToBatch() {
				batchID = p.BatchID
			} else if p.IsBatch() {
				batchID = p.ID
			}

			if _, ok := fetchedBatches[batchID]; ok {
				continue
			}

			p, err := ps.extendOrGetBatchProposal(ctx, p)
			if err != nil {
				return nil, pageInfo, err
			}
			props = append(props, p)
			fetchedBatches[p.ID] = struct{}{}
		}

		if pageForward {
			proposals = append(proposals, props...)
		} else {
			proposals = append(props, proposals...)
		}
	}

	if limit := calculateLimit(pagination); limit > 0 && limit < len(proposals) {
		proposals = proposals[:limit]
	}

	proposals, pageInfo = entities.PageEntities[*v2.GovernanceDataEdge](proposals, pagination)
	return proposals, pageInfo, nil
}