Skip to content

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

8.3 KB
Raw Blame
package pg

import (
	"context"
	"fmt"
	"slices"

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

	storepb "github.com/bytebase/bytebase/backend/generated-go/store"
	"github.com/bytebase/bytebase/backend/plugin/advisor"
	"github.com/bytebase/bytebase/backend/plugin/advisor/code"
	pgparser "github.com/bytebase/bytebase/backend/plugin/parser/pg"
	"github.com/bytebase/bytebase/backend/store/model"
)

var (
	_ advisor.Advisor = (*TableRequirePKAdvisor)(nil)
)

func init() {
	advisor.Register(storepb.Engine_POSTGRES, storepb.SQLReviewRule_TABLE_REQUIRE_PK, &TableRequirePKAdvisor{})
}

// TableRequirePKAdvisor is the advisor checking table requires PK.
//
// The rule guards the net change of a batch by comparing the metadata before
// and after the walkthrough: a table created in the batch must end with a
// PRIMARY KEY, and a table that had one must still have one. Pre-existing
// tables without a PRIMARY KEY are out of scope, so unrelated changes to them
// are not blocked. Name resolution (search_path, SET ROLE, drops) is left to
// the walkthrough; statements are only tracked to tell created tables from
// pre-existing ones and to attribute the advice.
type TableRequirePKAdvisor struct {
}

// Check parses the given statement and checks for errors.
func (*TableRequirePKAdvisor) Check(_ context.Context, checkCtx advisor.Context) ([]*storepb.Advice, error) {
	level, err := advisor.NewStatusBySQLReviewRuleLevel(checkCtx.Rule.Level)
	if err != nil {
		return nil, err
	}

	rule := &tableRequirePKRule{
		OmniBaseRule: OmniBaseRule{
			Level: level,
			Title: checkCtx.Rule.Type.String(),
		},
		originalMetadata: checkCtx.OriginalMetadata,
		finalMetadata:    checkCtx.FinalMetadata,
		tableMentions:    make(map[string]*tableMention),
	}

	// Manually iterate statements instead of using RunRules because
	// validateFinalState must be called AFTER all statements have been processed.
	for _, stmt := range checkCtx.ParsedStatements {
		if stmt.AST == nil {
			continue
		}
		node, ok := pgparser.GetOmniNode(stmt.AST)
		if !ok {
			continue
		}
		rule.SetStatement(stmt.BaseLine(), stmt.Text)
		rule.OnStatement(node)
	}

	rule.validateFinalState()

	return rule.GetAdviceList(), nil
}

type tableMention struct {
	startLine int
	text      string
	// created is set when the batch contains a CREATE TABLE for this name.
	created bool
	// dropped is set when the batch contains a DROP TABLE for this name.
	dropped bool
	// renamedFrom is the name the table had before the batch renamed it.
	renamedFrom string
}

type tableRequirePKRule struct {
	OmniBaseRule
	originalMetadata *model.DatabaseMetadata
	finalMetadata    *model.DatabaseMetadata

	// tableMentions tracks what the batch did to each table and the last
	// CREATE/ALTER statement for advice attribution. Keys are "schema.table"
	// with the schema as written, so an unqualified name is keyed ".table";
	// validation merges the qualified and unqualified entries of a table.
	tableMentions map[string]*tableMention
}

// Name returns the rule name.
func (*tableRequirePKRule) Name() string {
	return "table.require-pk"
}

// OnStatement is called for each top-level statement AST node.
func (r *tableRequirePKRule) OnStatement(node ast.Node) {
	switch n := node.(type) {
	case *ast.CreateStmt:
		if m := r.recordMention(n.Relation); m != nil {
			m.created = true
		}
	case *ast.AlterTableStmt:
		r.recordMention(n.Relation)
	case *ast.DropStmt:
		if n.RemoveType != int(ast.OBJECT_TABLE) || n.Objects == nil {
			return
		}
		for _, item := range n.Objects.Items {
			if schema, tableName, ok := dropObjectName(item); ok {
				r.mention(schema, tableName).dropped = true
			}
		}
	case *ast.RenameStmt:
		if n.RenameType != ast.OBJECT_TABLE || n.Relation == nil {
			return
		}
		r.renameMention(n.Relation, n.Newname)
	default:
	}
}

// dropObjectName splits a DROP TABLE object into schema (empty when
// unqualified) and table name.
func dropObjectName(item ast.Node) (string, string, bool) {
	list, ok := item.(*ast.List)
	if !ok {
		return "", "", false
	}
	var parts []string
	for _, nameItem := range list.Items {
		if s, ok := nameItem.(*ast.String); ok {
			parts = append(parts, s.Str)
		}
	}
	switch len(parts) {
	case 1:
		return "", parts[0], true
	case 2:
		return parts[0], parts[1], true
	default:
		return "", "", false
	}
}

func mentionKey(schema, tableName string) string {
	return schema + "." + tableName
}

func (r *tableRequirePKRule) mention(schema, tableName string) *tableMention {
	key := mentionKey(schema, tableName)
	m := r.tableMentions[key]
	if m == nil {
		m = &tableMention{}
		r.tableMentions[key] = m
	}
	return m
}

// recordMention points the table's advice attribution at the current statement.
func (r *tableRequirePKRule) recordMention(rv *ast.RangeVar) *tableMention {
	tableName := omniTableName(rv)
	if tableName == "" {
		return nil
	}
	m := r.mention(rv.Schemaname, tableName)
	m.startLine = int(r.ContentStartLine()) + r.BaseLine
	m.text = r.TrimmedStmtText()
	return m
}

// renameMention carries the batch state of rv over to newName and remembers
// the pre-batch name so the original metadata can still be found.
func (r *tableRequirePKRule) renameMention(rv *ast.RangeVar, newName string) {
	oldName := omniTableName(rv)
	if oldName == "" || newName == "" {
		return
	}
	oldKey := mentionKey(rv.Schemaname, oldName)
	old := r.tableMentions[oldKey]
	delete(r.tableMentions, oldKey)

	m := r.mention(rv.Schemaname, newName)
	m.startLine = int(r.ContentStartLine()) + r.BaseLine
	m.text = r.TrimmedStmtText()
	m.renamedFrom = oldName
	if old != nil {
		m.created = m.created || old.created
		m.dropped = m.dropped || old.dropped
		if old.renamedFrom != "" {
			m.renamedFrom = old.renamedFrom
		}
	}
}

// mentionFor merges the qualified and unqualified entries of a table, taking
// the attribution from the later statement.
func (r *tableRequirePKRule) mentionFor(schema, tableName string) *tableMention {
	q := r.tableMentions[mentionKey(schema, tableName)]
	u := r.tableMentions[mentionKey("", tableName)]
	switch {
	case q == nil:
		return u
	case u == nil:
		return q
	default:
	}
	m := *q
	if u.startLine > q.startLine {
		m.startLine, m.text = u.startLine, u.text
	}
	m.created = q.created || u.created
	m.dropped = q.dropped || u.dropped
	if m.renamedFrom == "" {
		m.renamedFrom = u.renamedFrom
	}
	return &m
}

// originalTable returns the table from the metadata before this batch, or nil.
func (r *tableRequirePKRule) originalTable(schemaName, tableName string) *model.TableMetadata {
	if r.originalMetadata == nil {
		return nil
	}
	return r.originalMetadata.GetSchemaMetadata(schemaName).GetTable(tableName)
}

// validateFinalState flags tables that end the batch without a PRIMARY KEY and
// either were created by the batch or had a PRIMARY KEY before it.
func (r *tableRequirePKRule) validateFinalState() {
	if r.finalMetadata == nil {
		return
	}
	schemaNames := r.finalMetadata.ListSchemaNames()
	slices.Sort(schemaNames)
	for _, schemaName := range schemaNames {
		schema := r.finalMetadata.GetSchemaMetadata(schemaName)
		tableNames := schema.ListTableNames()
		slices.Sort(tableNames)
		for _, tableName := range tableNames {
			if schema.GetTable(tableName).GetPrimaryKey() != nil {
				continue
			}
			mention := r.mentionFor(schemaName, tableName)
			original := r.originalTable(schemaName, tableName)
			if original == nil && mention != nil && mention.renamedFrom != "" {
				original = r.originalTable(schemaName, mention.renamedFrom)
			}
			switch {
			case original == nil:
				// New in this batch: only tables the batch created are in scope.
				if mention == nil || !mention.created {
					continue
				}
			case original.GetPrimaryKey() == nil:
				// A pre-existing table without a PRIMARY KEY is out of scope
				// unless the batch dropped and recreated it.
				if mention == nil || !mention.created || !mention.dropped {
					continue
				}
			default:
			}
			r.addAdvice(schemaName, tableName, mention)
		}
	}
}

func (r *tableRequirePKRule) addAdvice(schemaName, tableName string, mention *tableMention) {
	content := fmt.Sprintf("Table %q.%q requires PRIMARY KEY", schemaName, tableName)
	line := 0
	if mention != nil {
		line = mention.startLine
		if mention.text != "" {
			content = fmt.Sprintf("%s, related statement: %q", content, mention.text)
		}
	}
	r.AddAdviceAbsolute(&storepb.Advice{
		Status:  r.Level,
		Code:    code.TableNoPK.Int32(),
		Title:   r.Title,
		Content: content,
		StartPosition: &storepb.Position{
			Line:   int32(line),
			Column: 0,
		},
	})
}