Database governance built for humans and agents — controlling changes and access across every major database.
Go cicddata-governancedata-maskingdata-securitydatabase-accessdatabase-governancedevopsdevsecopsflywaygitopsliquibasemongodbmysqloraclepostgresqlschema-migrationssecuritysql-clientsqlservertidb
package pg
import (
"context"
"fmt"
"slices"
"strings"
"github.com/pkg/errors"
"github.com/bytebase/omni/pg/ast"
"github.com/bytebase/bytebase/backend/common"
storepb "github.com/bytebase/bytebase/backend/generated-go/store"
"github.com/bytebase/bytebase/backend/plugin/parser/base"
"github.com/bytebase/bytebase/backend/store/model"
)
const (
maxTableNameLength = 63
)
type StatementType int
const (
StatementTypeUnknown StatementType = iota
StatementTypeUpdate
StatementTypeInsert
StatementTypeDelete
)
type TableReference struct {
Database string
Schema string
Table string
Alias string
StatementType StatementType
}
func (t *TableReference) String() string {
if t.Database != "" {
return fmt.Sprintf(`"%s"."%s"."%s"`, t.Database, t.Schema, t.Table)
}
if t.Schema != "" {
return fmt.Sprintf(`"%s"."%s"`, t.Schema, t.Table)
}
return fmt.Sprintf(`"%s"`, t.Table)
}
type statementInfo struct {
offset int
statement string
node ast.Node
table *TableReference
startPos *storepb.Position
endPos *storepb.Position
fullSQL string
}
func init() {
base.RegisterTransformDMLToSelect(storepb.Engine_POSTGRES, TransformDMLToSelect)
}
func TransformDMLToSelect(ctx context.Context, tCtx base.TransformContext, statement string, _ string, targetSchema string, tablePrefix string) ([]base.BackupStatement, error) {
statementInfoList, err := prepareTransformation(ctx, tCtx, statement)
if err != nil {
return nil, errors.Wrapf(err, "failed to prepare transformation")
}
return generateSQL(statementInfoList, targetSchema, tablePrefix)
}
func generateSQL(statementInfoList []statementInfo, targetSchema string, tablePrefix string) ([]base.BackupStatement, error) {
groupByTable := make(map[string][]statementInfo)
for _, item := range statementInfoList {
key := fmt.Sprintf("%s.%s", item.table.Schema, item.table.Table)
groupByTable[key] = append(groupByTable[key], item)
}
// Check if the statement type is the same for all statements on the same table.
for key, list := range groupByTable {
statementType := StatementTypeUnknown
for _, item := range list {
if statementType == StatementTypeUnknown {
statementType = item.table.StatementType
}
if statementType != item.table.StatementType {
return nil, errors.Errorf("The statement type is not the same for all statements on the same table %q", key)
}
}
}
var result []base.BackupStatement
for key, list := range groupByTable {
backupStatement, err := generateSQLForTable(list, targetSchema, tablePrefix)
if err != nil {
return nil, errors.Wrapf(err, "failed to generate SQL for table %q", key)
}
result = append(result, *backupStatement)
}
slices.SortFunc(result, func(a, b base.BackupStatement) int {
if a.StartPosition.Line != b.StartPosition.Line {
if a.StartPosition.Line < b.StartPosition.Line {
return -1
}
return 1
}
if a.StartPosition.Column != b.StartPosition.Column {
if a.StartPosition.Column < b.StartPosition.Column {
return -1
}
return 1
}
if a.SourceTableName < b.SourceTableName {
return -1
}
if a.SourceTableName > b.SourceTableName {
return 1
}
return 0
})
return result, nil
}
func generateSQLForTable(statementInfoList []statementInfo, targetSchema string, tablePrefix string) (*base.BackupStatement, error) {
table := statementInfoList[0].table
targetTable := fmt.Sprintf("%s_%s_%s", tablePrefix, table.Table, table.Schema)
targetTable, _ = common.TruncateString(targetTable, maxTableNameLength)
var buf strings.Builder
if _, err := fmt.Fprintf(&buf, `CREATE TABLE "%s"."%s" AS`+"\n", targetSchema, targetTable); err != nil {
return nil, errors.Wrap(err, "failed to write to buffer")
}
// In PostgreSQL, WITH clause must appear once before the first SELECT.
cteClause := extractCTE(statementInfoList[0].node, statementInfoList[0].fullSQL)
if cteClause != "" {
if _, err := fmt.Fprintf(&buf, "%s\n", cteClause); err != nil {
return nil, errors.Wrap(err, "failed to write to buffer")
}
}
for i, item := range statementInfoList {
if i != 0 {
if _, err := buf.WriteString("\n UNION\n"); err != nil {
return nil, errors.Wrap(err, "failed to write to buffer")
}
}
if table.Alias != "" {
if _, err := fmt.Fprintf(&buf, ` SELECT "%s".* `, table.Alias); err != nil {
return nil, errors.Wrap(err, "failed to write to buffer")
}
} else {
if _, err := fmt.Fprintf(&buf, ` SELECT %s.* `, table.String()); err != nil {
return nil, errors.Wrap(err, "failed to write to buffer")
}
}
if err := writeSuffixSelectClause(&buf, item.node, item.fullSQL); err != nil {
return nil, errors.Wrap(err, "failed to write string with new line")
}
}
if _, err := buf.WriteString(";"); err != nil {
return nil, errors.Wrap(err, "failed to write to buffer")
}
return &base.BackupStatement{
Statement: buf.String(),
SourceSchema: table.Schema,
SourceTableName: table.Table,
TargetTableName: targetTable,
StartPosition: statementInfoList[0].startPos,
EndPosition: statementInfoList[len(statementInfoList)-1].endPos,
}, nil
}
// DataModifyingCTENames returns the names of the CTEs in a statement's WITH
// clause that run INSERT, UPDATE, DELETE, or MERGE. PostgreSQL only allows
// such CTEs in the top-level WITH clause, so nested queries are not searched.
func DataModifyingCTENames(node ast.Node) []string {
var wc *ast.WithClause
switch n := node.(type) {
case *ast.SelectStmt:
wc = n.WithClause
case *ast.InsertStmt:
wc = n.WithClause
case *ast.UpdateStmt:
wc = n.WithClause
case *ast.DeleteStmt:
wc = n.WithClause
case *ast.MergeStmt:
wc = n.WithClause
default:
}
if wc == nil || wc.Ctes == nil {
return nil
}
var names []string
for _, item := range wc.Ctes.Items {
cte, ok := item.(*ast.CommonTableExpr)
if !ok {
continue
}
if _, isSelect := cte.Ctequery.(*ast.SelectStmt); !isSelect {
names = append(names, cte.Ctename)
}
}
return names
}
// rejectDataModifyingCTE fails a backup whose statement has a data-modifying
// CTE: the backup copies the WITH clause, so running it would apply that
// change once before the statement itself runs.
func rejectDataModifyingCTE(node ast.Node) error {
names := DataModifyingCTENames(node)
if len(names) == 0 {
return nil
}
quoted := make([]string, 0, len(names))
for _, name := range names {
quoted = append(quoted, fmt.Sprintf("%q", name))
}
return errors.Errorf("prior backup does not support data-modifying WITH query %s; rewrite the statement without it, or disable or skip prior backup", strings.Join(quoted, ", "))
}
func extractCTE(node ast.Node, fullSQL string) string {
var wc *ast.WithClause
switch n := node.(type) {
case *ast.UpdateStmt:
wc = n.WithClause
case *ast.DeleteStmt:
wc = n.WithClause
default:
}
if wc == nil {
return ""
}
return extractNodeText(wc.Loc, fullSQL)
}
func writeSuffixSelectClause(buf *strings.Builder, node ast.Node, fullSQL string) error {
switch n := node.(type) {
case *ast.UpdateStmt:
return writeUpdateSuffix(buf, n, fullSQL)
case *ast.DeleteStmt:
return writeDeleteSuffix(buf, n, fullSQL)
}
return nil
}
func writeUpdateSuffix(buf *strings.Builder, n *ast.UpdateStmt, sql string) error {
if n.Relation == nil {
return nil
}
relText := extractNodeText(n.Relation.Loc, sql)
if _, err := fmt.Fprintf(buf, "FROM %s", relText); err != nil {
return errors.Wrap(err, "failed to write to buffer")
}
if n.FromClause != nil && n.FromClause.Len() > 0 {
fromText := extractNodeText(ast.ListSpan(n.FromClause), sql)
if fromText != "" {
if _, err := fmt.Fprintf(buf, ", %s", fromText); err != nil {
return errors.Wrap(err, "failed to write to buffer")
}
}
}
if n.WhereClause != nil {
whereText := extractNodeText(ast.NodeLoc(n.WhereClause), sql)
if whereText != "" {
if _, err := fmt.Fprintf(buf, " WHERE %s", whereText); err != nil {
return errors.Wrap(err, "failed to write to buffer")
}
}
}
return nil
}
func writeDeleteSuffix(buf *strings.Builder, n *ast.DeleteStmt, sql string) error {
if n.Relation == nil {
return nil
}
relText := extractNodeText(n.Relation.Loc, sql)
if _, err := fmt.Fprintf(buf, "FROM %s", relText); err != nil {
return errors.Wrap(err, "failed to write to buffer")
}
if n.UsingClause != nil && n.UsingClause.Len() > 0 {
usingText := extractNodeText(ast.ListSpan(n.UsingClause), sql)
if usingText != "" {
if _, err := fmt.Fprintf(buf, ", %s", usingText); err != nil {
return errors.Wrap(err, "failed to write to buffer")
}
}
}
if n.WhereClause != nil {
whereText := extractNodeText(ast.NodeLoc(n.WhereClause), sql)
if whereText != "" {
if _, err := fmt.Fprintf(buf, " WHERE %s", whereText); err != nil {
return errors.Wrap(err, "failed to write to buffer")
}
}
}
return nil
}
// extractNodeText extracts trimmed text from SQL using a Loc range.
func extractNodeText(loc ast.Loc, sql string) string {
if loc.Start < 0 || loc.End < 0 || loc.Start >= loc.End || loc.End > len(sql) {
return ""
}
return strings.TrimSpace(sql[loc.Start:loc.End])
}
func prepareTransformation(ctx context.Context, tCtx base.TransformContext, statement string) ([]statementInfo, error) {
stmts, err := ParsePg(statement)
if err != nil {
return nil, errors.Wrap(err, "failed to parse statement")
}
if tCtx.GetDatabaseMetadataFunc == nil {
return nil, errors.New("GetDatabaseMetadataFunc is not set in TransformContext")
}
_, metadata, err := tCtx.GetDatabaseMetadataFunc(ctx, tCtx.InstanceID, tCtx.DatabaseName)
if err != nil {
return nil, errors.Wrap(err, "failed to get database metadata")
}
if metadata == nil {
return nil, errors.Errorf("database metadata for database %q not found (database not synced)", tCtx.DatabaseName)
}
searchPath := metadata.GetSearchPath()
var dmls []statementInfo
// Node locations ascend across statements, so a single incremental mapper
// converts all offsets in one pass instead of rescanning from byte 0.
posMapper := base.NewByteOffsetPositionMapper(statement)
for i, stmt := range stmts {
if stmt.Empty() {
continue
}
switch n := stmt.AST.(type) {
case *ast.VariableSetStmt:
if strings.EqualFold(n.Name, "search_path") && n.Args != nil {
var newSearchPath []string
for _, arg := range n.Args.Items {
if ac, ok := arg.(*ast.A_Const); ok {
if s, ok := ac.Val.(*ast.String); ok {
newSearchPath = append(newSearchPath, s.Str)
}
}
}
if len(newSearchPath) > 0 {
searchPath = newSearchPath
}
}
case *ast.UpdateStmt:
if err := rejectDataModifyingCTE(n); err != nil {
return nil, err
}
table, err := extractTableReferenceFromRangeVar(n.Relation, metadata, searchPath)
if err != nil {
return nil, errors.Wrapf(err, "failed to extract table reference from update statement at offset %d", i)
}
if table == nil {
continue
}
table.StatementType = StatementTypeUpdate
nodeLoc := n.Loc
startPos := posMapper.Position(nodeLoc.Start)
startPos.Column-- // 0-based column to match existing convention
endPos := posMapper.Position(nodeLoc.End)
dmls = append(dmls, statementInfo{
offset: i,
statement: stmt.Text,
node: n,
table: table,
startPos: startPos,
endPos: endPos,
fullSQL: statement,
})
case *ast.DeleteStmt:
if err := rejectDataModifyingCTE(n); err != nil {
return nil, err
}
table, err := extractTableReferenceFromRangeVar(n.Relation, metadata, searchPath)
if err != nil {
return nil, errors.Wrapf(err, "failed to extract table reference from delete statement at offset %d", i)
}
if table == nil {
continue
}
table.StatementType = StatementTypeDelete
nodeLoc := n.Loc
startPos := posMapper.Position(nodeLoc.Start)
startPos.Column--
endPos := posMapper.Position(nodeLoc.End)
dmls = append(dmls, statementInfo{
offset: i,
statement: stmt.Text,
node: n,
table: table,
startPos: startPos,
endPos: endPos,
fullSQL: statement,
})
default:
}
}
return dmls, nil
}
func extractTableReferenceFromRangeVar(rv *ast.RangeVar, metadata *model.DatabaseMetadata, searchPath []string) (*TableReference, error) {
if rv == nil {
return nil, nil
}
table := TableReference{}
switch {
case rv.Catalogname != "":
table.Database = rv.Catalogname
table.Schema = rv.Schemaname
table.Table = rv.Relname
case rv.Schemaname != "":
table.Schema = rv.Schemaname
table.Table = rv.Relname
default:
// TODO: remove it in the future.
// Handle the case where the search path is not synchronized with the metadata.
if len(searchPath) == 0 {
searchPath = []string{"public"}
}
schemaName, _ := metadata.SearchRelation(searchPath, rv.Relname)
if schemaName == "" {
return nil, errors.Errorf("Table %q not found in metadata with search path %v", rv.Relname, searchPath)
}
table.Schema = schemaName
table.Table = rv.Relname
}
if rv.Alias != nil && rv.Alias.Aliasname != "" {
table.Alias = rv.Alias.Aliasname
}
return &table, nil
}