Skip to content

Database governance built for humans and agents — controlling changes and access across every major database.

5.1 KB
Raw Blame
package pg

import (
	"fmt"
	"log/slog"
	"strings"

	"github.com/bytebase/omni/pg/ast"
	"github.com/pkg/errors"

	"github.com/bytebase/bytebase/backend/common/log"
	storepb "github.com/bytebase/bytebase/backend/generated-go/store"
	"github.com/bytebase/bytebase/backend/plugin/parser/base"
)

func init() {
	base.RegisterResultLimitFunc(storepb.Engine_POSTGRES, statementWithResultLimit)
	// CockroachDB parses through the PostgreSQL grammar here as it does for the
	// read-only gate and EXPLAIN (see query.go, explain.go).
	base.RegisterResultLimitFunc(storepb.Engine_COCKROACHDB, statementWithResultLimit)
}

// statementWithResultLimit implements base.ResultLimitFunc for PostgreSQL and
// CockroachDB, which share this grammar. Neither takes an engineVersion.
func statementWithResultLimit(statement string, limit int, _ string) string {
	stmt, err := statementWithResultLimitInline(statement, limit)
	if err != nil {
		slog.Error("fail to add limit clause", slog.String("statement", statement), log.BBError(err))
		// Fallback to CTE approach for problematic queries
		return fmt.Sprintf("WITH result AS (\n%s\n) SELECT * FROM result LIMIT %d;", base.TrimStatement(statement), limit)
	}
	return stmt
}

func statementWithResultLimitInline(statement string, limitCount int) (string, error) {
	if strings.TrimSpace(statement) == "" {
		return "", errors.New("empty statement")
	}

	stmts, err := ParsePg(statement)
	if err != nil {
		return "", errors.Wrap(err, "failed to parse statement")
	}

	if len(stmts) != 1 {
		return "", errors.Errorf("expected exactly one statement, got %d", len(stmts))
	}

	sel, ok := stmts[0].AST.(*ast.SelectStmt)
	if !ok {
		// Non-SELECT statement, return as-is.
		return statement, nil
	}

	return rewriteSelectLimit(statement, sel, limitCount)
}

// rewriteSelectLimit adds or adjusts the LIMIT clause of a SELECT statement
// using byte-offset positions from the omni AST to surgically edit the original SQL text.
func rewriteSelectLimit(sql string, sel *ast.SelectStmt, limitCount int) (string, error) {
	if sel.LimitCount != nil {
		existingLimit, isInteger := extractIntFromNode(sel.LimitCount)
		loc := nodeLocOf(sel.LimitCount)
		if isInteger && existingLimit >= 0 && existingLimit <= limitCount {
			return sql, nil // existing limit is already lower or equal, keep it
		}
		if isInteger {
			if end, ok := integerLiteralEnd(sql, loc); ok {
				return sql[:loc.Start] + fmt.Sprintf("%d", limitCount) + sql[end:], nil
			}
		}
		// ALL/NULL and non-constant limits lack reliable replacement spans.
		// Cap them with the outer query instead of rewriting their text.
		return "", errors.Errorf("cannot rewrite LIMIT expression in place")
	}

	// No LIMIT clause — find the right insertion point.
	// PostgreSQL grammar order: ... ORDER BY ... LIMIT ... FOR UPDATE ...
	// LIMIT goes BEFORE FOR UPDATE but AFTER everything else.
	insertPos, beforeLocking := findLimitInsertPosition(sel)
	if beforeLocking {
		// Inserting at the start of FOR UPDATE/SHARE. The original whitespace
		// before FOR becomes the separator before LIMIT; we add a trailing
		// space to separate the limit value from FOR.
		return sql[:insertPos] + fmt.Sprintf("LIMIT %d ", limitCount) + sql[insertPos:], nil
	}
	return sql[:insertPos] + fmt.Sprintf(" LIMIT %d", limitCount) + sql[insertPos:], nil
}

// findLimitInsertPosition returns the byte offset where " LIMIT N" should be inserted,
// and whether the insertion is before a locking clause (FOR UPDATE/SHARE).
func findLimitInsertPosition(sel *ast.SelectStmt) (int, bool) {
	// LIMIT must appear before FOR UPDATE/SHARE.
	if sel.LockingClause != nil {
		span := ast.ListSpan(sel.LockingClause)
		if span.Start > 0 {
			return span.Start, true
		}
	}

	// Otherwise insert at SelectStmt.Loc.End (after everything, including outer parens).
	// For CTE queries, omni may report SelectStmt.Loc.End before the outer ORDER BY,
	// so account for the sort clause explicitly.
	end := sel.Loc.End
	if span := ast.ListSpan(sel.SortClause); span.End > end {
		end = span.End
	}
	if end <= 0 {
		return 0, false
	}
	return end, false
}

// extractIntFromNode distinguishes integer limits, including zero, from unlimited
// ALL/NULL constants and expressions that require the fallback wrapper.
func extractIntFromNode(node ast.Node) (int, bool) {
	switch n := node.(type) {
	case *ast.Integer:
		return int(n.Ival), true
	case *ast.A_Const:
		if iv, ok := n.Val.(*ast.Integer); ok {
			return int(iv.Ival), true
		}
		return 0, false
	default:
		return 0, false
	}
}

// nodeLocOf returns the Loc of a node, handling common wrapper types.
func nodeLocOf(node ast.Node) ast.Loc {
	switch n := node.(type) {
	case *ast.A_Const:
		return n.Loc
	default:
		return ast.Loc{Start: -1, End: -1}
	}
}

// integerLiteralEnd returns the exclusive end byte offset of the integer
// literal. The AST span may include whitespace before the next token, which
// must remain when replacing the literal.
func integerLiteralEnd(sql string, loc ast.Loc) (int, bool) {
	if loc.Start < 0 || loc.End <= loc.Start || loc.End > len(sql) {
		return 0, false
	}
	literal := strings.TrimRight(sql[loc.Start:loc.End], " \t\r\n\f\v")
	return loc.Start + len(literal), true
}