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 (
"fmt"
"strings"
omnipg "github.com/bytebase/omni/pg"
"github.com/bytebase/omni/pg/ast"
storepb "github.com/bytebase/bytebase/backend/generated-go/store"
"github.com/bytebase/bytebase/backend/plugin/advisor/code"
pgparser "github.com/bytebase/bytebase/backend/plugin/parser/pg"
)
// CheckSDLIntegrity performs comprehensive integrity checks across SDL files.
// This function always handles multiple files to properly validate cross-file references
// (foreign keys, views) and detect duplicate definitions across files.
//
// Parameters:
// - files: map[filePath]sqlContent - All SDL files to check together.
// For single-file checking, use a map with one entry.
//
// Returns:
// - map[filePath][]*storepb.Advice - Per-file advice list
// - error - Parse or system errors
func CheckSDLIntegrity(files map[string]string) (map[string][]*storepb.Advice, error) {
if len(files) == 0 {
return make(map[string][]*storepb.Advice), nil
}
// Parse each file and build individual symbol tables
fileCheckers := make([]*fileSymbolTable, 0, len(files))
for filePath, statement := range files {
if err := validateParenBalance(statement); err != nil {
return map[string][]*storepb.Advice{
filePath: {{
Status: storepb.Advice_ERROR,
Code: code.StatementSyntaxError.Int32(),
Title: "SQL syntax error",
Content: fmt.Sprintf("Failed to parse SQL in file '%s': %v", filePath, err),
}},
}, nil
}
stmts, err := pgparser.ParsePg(statement)
if err != nil {
return map[string][]*storepb.Advice{
filePath: {{
Status: storepb.Advice_ERROR,
Code: code.StatementSyntaxError.Int32(),
Title: "SQL syntax error",
Content: fmt.Sprintf("Failed to parse SQL in file '%s': %v", filePath, err),
}},
}, nil
}
st, advices := buildFileSymbolTable(stmts, statement)
checker := &sdlIntegrityChecker{
symbolTable: st,
adviceList: advices,
}
fileCheckers = append(fileCheckers, &fileSymbolTable{
filePath: filePath,
checker: checker,
adviceList: make([]*storepb.Advice, 0),
})
}
// Merge all symbol tables
merged := mergeSymbolTables(fileCheckers)
// Detect cross-file duplicates
crossFileDuplicates := detectCrossFileDuplicates(fileCheckers)
for filePath, advices := range crossFileDuplicates {
for _, fc := range fileCheckers {
if fc.filePath == filePath {
fc.adviceList = append(fc.adviceList, advices...)
break
}
}
}
// Validate references with merged symbol table
for _, fc := range fileCheckers {
fc.checker.validateReferencesWithMergedTable(merged)
fc.adviceList = append(fc.adviceList, fc.checker.adviceList...)
}
// Aggregate results per file
results := make(map[string][]*storepb.Advice)
for _, fc := range fileCheckers {
results[fc.filePath] = fc.adviceList
}
return results, nil
}
// checkSingleStatement is a helper for backward compatibility with single-statement checks.
// It wraps the statement in a single-file map and extracts the results.
func checkSingleStatement(statement string) ([]*storepb.Advice, error) {
const defaultFileName = "statement.sql"
results, err := CheckSDLIntegrity(map[string]string{
defaultFileName: statement,
})
if err != nil {
return nil, err
}
return results[defaultFileName], nil
}
// fileSymbolTable tracks the symbol table and advices for a single file
type fileSymbolTable struct {
filePath string
checker *sdlIntegrityChecker
adviceList []*storepb.Advice
}
// mergedSymbolTable provides a global view of all objects across all files
type mergedSymbolTable struct {
allTables map[string]*tableDef
allIndexes map[string]*indexDef
allViews map[string]*viewDef
allSchemaConstraintNames map[string]map[string]*constraintDef // schema -> constraint_name (for PK/UK)
allTableConstraintNames map[string]map[string]*constraintDef // schema.table -> constraint_name (for CHECK/FK)
objectSources map[string]string // qualifiedName -> filePath
}
// mergeSymbolTables merges symbol tables from all files into a unified view
func mergeSymbolTables(fileCheckers []*fileSymbolTable) *mergedSymbolTable {
merged := &mergedSymbolTable{
allTables: make(map[string]*tableDef),
allIndexes: make(map[string]*indexDef),
allViews: make(map[string]*viewDef),
allSchemaConstraintNames: make(map[string]map[string]*constraintDef),
allTableConstraintNames: make(map[string]map[string]*constraintDef),
objectSources: make(map[string]string),
}
for _, fc := range fileCheckers {
// Merge tables
for key, table := range fc.checker.symbolTable.tables {
if _, exists := merged.allTables[key]; !exists {
merged.allTables[key] = table
merged.objectSources[key] = fc.filePath
}
}
// Merge indexes
for key, index := range fc.checker.symbolTable.indexes {
if _, exists := merged.allIndexes[key]; !exists {
merged.allIndexes[key] = index
merged.objectSources["index:"+key] = fc.filePath
}
}
// Merge views
for key, view := range fc.checker.symbolTable.views {
if _, exists := merged.allViews[key]; !exists {
merged.allViews[key] = view
merged.objectSources["view:"+key] = fc.filePath
}
}
// Merge schema-level constraint names (PRIMARY KEY and UNIQUE)
for schema, constraints := range fc.checker.symbolTable.schemaConstraintNames {
if merged.allSchemaConstraintNames[schema] == nil {
merged.allSchemaConstraintNames[schema] = make(map[string]*constraintDef)
}
for name, constraint := range constraints {
if _, exists := merged.allSchemaConstraintNames[schema][name]; !exists {
merged.allSchemaConstraintNames[schema][name] = constraint
merged.objectSources["constraint:"+schema+"."+name] = fc.filePath
}
}
}
// Merge table-level constraint names (CHECK and FOREIGN KEY)
for tableKey, constraints := range fc.checker.symbolTable.tableConstraintNames {
if merged.allTableConstraintNames[tableKey] == nil {
merged.allTableConstraintNames[tableKey] = make(map[string]*constraintDef)
}
for name, constraint := range constraints {
if _, exists := merged.allTableConstraintNames[tableKey][name]; !exists {
merged.allTableConstraintNames[tableKey][name] = constraint
merged.objectSources["constraint:"+tableKey+"."+name] = fc.filePath
}
}
}
}
return merged
}
// objectFirstSeen tracks where an object was first defined
type objectFirstSeen struct {
filePath string
line int
obj any // *tableDef, *indexDef, *viewDef, or *constraintDef
}
// detectCrossFileDuplicates finds objects defined in multiple files
func detectCrossFileDuplicates(fileCheckers []*fileSymbolTable) map[string][]*storepb.Advice {
advices := make(map[string][]*storepb.Advice)
// Track first occurrence of each object
seenTables := make(map[string]*objectFirstSeen)
seenIndexes := make(map[string]*objectFirstSeen)
seenViews := make(map[string]*objectFirstSeen)
seenConstraints := make(map[string]map[string]*objectFirstSeen)
for _, fc := range fileCheckers {
// Check tables
for key, table := range fc.checker.symbolTable.tables {
if first, exists := seenTables[key]; exists {
advice := &storepb.Advice{
Status: storepb.Advice_ERROR,
Code: code.SDLDuplicateTableName.Int32(),
Title: "Duplicate table name across files",
Content: fmt.Sprintf(
"Table '%s.%s' is defined in multiple SDL files.\n\n"+
"First definition: %s (line %d)\n"+
"Duplicate definition: %s (line %d)\n\n"+
"Each table must be defined in exactly one file in the SDL project.",
table.schemaName, table.tableName,
first.filePath, first.line,
fc.filePath, table.line,
),
StartPosition: &storepb.Position{Line: int32(table.line)},
}
advices[fc.filePath] = append(advices[fc.filePath], advice)
} else {
seenTables[key] = &objectFirstSeen{
filePath: fc.filePath,
line: table.line,
obj: table,
}
}
}
// Check indexes
for key, index := range fc.checker.symbolTable.indexes {
if first, exists := seenIndexes[key]; exists {
advice := &storepb.Advice{
Status: storepb.Advice_ERROR,
Code: code.SDLDuplicateIndexName.Int32(),
Title: "Duplicate index name across files",
Content: fmt.Sprintf(
"Index '%s.%s' is defined in multiple SDL files.\n\n"+
"First definition: %s (line %d)\n"+
"Duplicate definition: %s (line %d)\n\n"+
"Each index must be defined in exactly one file in the SDL project.",
index.schemaName, index.indexName,
first.filePath, first.line,
fc.filePath, index.line,
),
StartPosition: &storepb.Position{Line: int32(index.line)},
}
advices[fc.filePath] = append(advices[fc.filePath], advice)
} else {
seenIndexes[key] = &objectFirstSeen{
filePath: fc.filePath,
line: index.line,
obj: index,
}
}
}
// Check views
for key, view := range fc.checker.symbolTable.views {
if first, exists := seenViews[key]; exists {
advice := &storepb.Advice{
Status: storepb.Advice_ERROR,
Code: code.SDLDuplicateTableName.Int32(),
Title: "Duplicate view name across files",
Content: fmt.Sprintf(
"View '%s.%s' is defined in multiple SDL files.\n\n"+
"First definition: %s (line %d)\n"+
"Duplicate definition: %s (line %d)\n\n"+
"Each view must be defined in exactly one file in the SDL project.",
view.schemaName, view.viewName,
first.filePath, first.line,
fc.filePath, view.line,
),
StartPosition: &storepb.Position{Line: int32(view.line)},
}
advices[fc.filePath] = append(advices[fc.filePath], advice)
} else {
seenViews[key] = &objectFirstSeen{
filePath: fc.filePath,
line: view.line,
obj: view,
}
}
}
// Check schema-level constraint names (PRIMARY KEY and UNIQUE)
for schema, constraints := range fc.checker.symbolTable.schemaConstraintNames {
if seenConstraints[schema] == nil {
seenConstraints[schema] = make(map[string]*objectFirstSeen)
}
for name, constraint := range constraints {
if first, exists := seenConstraints[schema][name]; exists {
advice := &storepb.Advice{
Status: storepb.Advice_ERROR,
Code: code.SDLDuplicateConstraintName.Int32(),
Title: "Duplicate constraint name across files",
Content: fmt.Sprintf(
"Constraint '%s' in schema '%s' is defined in multiple SDL files.\n\n"+
"First definition: %s on table '%s' in %s (line %d)\n"+
"Duplicate definition: %s on table '%s' in %s (line %d)\n\n"+
"PostgreSQL requires PRIMARY KEY and UNIQUE constraint names to be unique within a schema.\n"+
"Each constraint must be defined in exactly one file in the SDL project.",
name, schema,
first.obj.(*constraintDef).constraintType, first.obj.(*constraintDef).tableName, first.filePath, first.line,
constraint.constraintType, constraint.tableName, fc.filePath, constraint.line,
),
StartPosition: &storepb.Position{Line: int32(constraint.line)},
}
advices[fc.filePath] = append(advices[fc.filePath], advice)
} else {
seenConstraints[schema][name] = &objectFirstSeen{
filePath: fc.filePath,
line: constraint.line,
obj: constraint,
}
}
}
}
// Check table-level constraint names (CHECK and FOREIGN KEY)
// These are already scoped to table, so duplicates across files are allowed if on different tables
// No cross-file check needed for table-level constraints
}
return advices
}
// validateReferencesWithMergedTable validates references using the merged symbol table
func (c *sdlIntegrityChecker) validateReferencesWithMergedTable(merged *mergedSymbolTable) {
// Check for multiple primary keys
c.checkMultiplePrimaryKeys()
// Validate foreign key references with merged table
c.validateForeignKeysWithMergedTable(merged)
// Validate view dependencies with merged table
c.validateViewDependenciesWithMergedTable(merged)
}
// validateForeignKeysWithMergedTable validates FK references using merged symbol table
func (c *sdlIntegrityChecker) validateForeignKeysWithMergedTable(merged *mergedSymbolTable) {
for _, table := range c.symbolTable.tables {
for _, constraint := range table.constraints {
if constraint.constraintType != "FOREIGN KEY" {
continue
}
// Look up referenced table in merged symbol table
refKey := qualifiedName(constraint.fkReferencedSchema, constraint.fkReferencedTable)
refTable := merged.allTables[refKey]
if refTable == nil {
c.adviceList = append(c.adviceList, &storepb.Advice{
Status: storepb.Advice_ERROR,
Code: code.SDLForeignKeyTableNotFound.Int32(),
Title: "Foreign key references non-existent table",
Content: fmt.Sprintf(
"Foreign key constraint '%s' on table '%s.%s' references table '%s.%s' which does not exist in any SDL file.\n\n"+
"Foreign key columns: %s\n\n"+
"Make sure the referenced table is defined in one of the SDL files.",
getConstraintName(constraint),
table.schemaName, table.tableName,
constraint.fkReferencedSchema, constraint.fkReferencedTable,
formatColumnList(constraint.fkColumns),
),
StartPosition: &storepb.Position{Line: int32(constraint.line)},
})
continue
}
// Validate columns and types
if len(constraint.fkColumns) != len(constraint.fkReferencedColumns) {
continue
}
for i, fkCol := range constraint.fkColumns {
refCol := constraint.fkReferencedColumns[i]
sourceCol := table.columns[fkCol]
if sourceCol == nil {
continue
}
targetCol := refTable.columns[refCol]
if targetCol == nil {
c.adviceList = append(c.adviceList, &storepb.Advice{
Status: storepb.Advice_ERROR,
Code: code.SDLForeignKeyColumnNotFound.Int32(),
Title: "Foreign key references non-existent column",
Content: fmt.Sprintf(
"Foreign key constraint '%s' on table '%s.%s' references column '%s' in table '%s.%s', but this column does not exist.\n\n"+
"Foreign key definition:\n"+
" Source column: %s.%s.%s\n"+
" Referenced column: %s.%s.%s (DOES NOT EXIST)\n\n"+
"Available columns in '%s.%s': %s",
getConstraintName(constraint),
table.schemaName, table.tableName,
refCol,
constraint.fkReferencedSchema, constraint.fkReferencedTable,
table.schemaName, table.tableName, fkCol,
constraint.fkReferencedSchema, constraint.fkReferencedTable, refCol,
constraint.fkReferencedSchema, constraint.fkReferencedTable,
getColumnList(refTable),
),
StartPosition: &storepb.Position{Line: int32(constraint.line)},
})
}
}
}
}
}
// validateViewDependenciesWithMergedTable validates view dependencies using merged symbol table
func (c *sdlIntegrityChecker) validateViewDependenciesWithMergedTable(merged *mergedSymbolTable) {
for _, view := range c.symbolTable.views {
if view.selectQuery == nil {
continue
}
dependencies := getViewDependencies(view.selectQuery, view.schemaName)
for _, dep := range dependencies {
// Skip system schemas
if pgparser.IsSystemSchema(dep.schemaName) {
continue
}
// Skip system tables/views by name
if pgparser.IsSystemTable(dep.tableName) || pgparser.IsSystemView(dep.tableName) {
continue
}
refKey := qualifiedName(dep.schemaName, dep.tableName)
// Check if the reference exists in either tables or views
if merged.allTables[refKey] == nil && merged.allViews[refKey] == nil {
objectType := "table or view"
c.adviceList = append(c.adviceList, &storepb.Advice{
Status: storepb.Advice_ERROR,
Code: code.SDLViewDependencyNotFound.Int32(),
Title: "View references non-existent table or view",
Content: fmt.Sprintf(
"View '%s.%s' (line %d) references %s '%s.%s' which does not exist in any SDL file.\n\n"+
"Views must reference tables or views that are defined in the SDL project.\n\n"+
"Fix: Define %s '%s.%s' in one of the SDL files, or remove the view if the object is external.",
view.schemaName, view.viewName, view.line,
objectType, dep.schemaName, dep.tableName,
objectType, dep.schemaName, dep.tableName,
),
StartPosition: &storepb.Position{Line: int32(view.line)},
})
}
}
}
}
// formatColumnList formats a list of columns for display
func formatColumnList(columns []string) string {
if len(columns) == 0 {
return "<none>"
}
result := ""
for i, col := range columns {
if i > 0 {
result += ", "
}
result += col
}
return result
}
// sdlIntegrityChecker holds the symbol table and advices for integrity checking
type sdlIntegrityChecker struct {
symbolTable *symbolTable
adviceList []*storepb.Advice
}
// symbolTable stores all defined objects for cross-referencing
type symbolTable struct {
// schema.table -> TableDef
tables map[string]*tableDef
// schema.index -> IndexDef
indexes map[string]*indexDef
// schema.view -> ViewDef
views map[string]*viewDef
// Schema-level constraint names (for PRIMARY KEY and UNIQUE constraints)
// schema -> constraint_name -> constraintDef
schemaConstraintNames map[string]map[string]*constraintDef
// Table-level constraint names (for CHECK and FOREIGN KEY constraints)
// schema.table -> constraint_name -> constraintDef
tableConstraintNames map[string]map[string]*constraintDef
}
type tableDef struct {
schemaName string
tableName string
columns map[string]*columnDef // column_name -> ColumnDef
// Track primary keys to detect duplicates
primaryKeys []*constraintDef
// Track all constraints for validation
constraints []*constraintDef
line int
}
type columnDef struct {
name string
dataType *dataType
notNull bool
position int
line int
}
type dataType struct {
// Base type name: INTEGER, TEXT, VARCHAR, NUMERIC, TIMESTAMP, etc.
baseType string
// For VARCHAR(n), CHAR(n)
length int
// For NUMERIC(p,s)
precision int
scale int
}
type indexDef struct {
schemaName string
indexName string
tableName string
line int
}
type viewDef struct {
schemaName string
viewName string
selectQuery ast.Node // the SELECT query AST node
line int
}
type constraintDef struct {
name string
constraintType string // "PRIMARY KEY", "UNIQUE", "CHECK", "FOREIGN KEY"
schemaName string
tableName string
line int
// For FK constraints
fkReferencedSchema string
fkReferencedTable string
fkColumns []string
fkReferencedColumns []string
}
func newSymbolTable() *symbolTable {
return &symbolTable{
tables: make(map[string]*tableDef),
indexes: make(map[string]*indexDef),
views: make(map[string]*viewDef),
schemaConstraintNames: make(map[string]map[string]*constraintDef),
tableConstraintNames: make(map[string]map[string]*constraintDef),
}
}
func (st *symbolTable) addTable(schema, table string, line int) *tableDef {
key := qualifiedName(schema, table)
if _, exists := st.tables[key]; !exists {
st.tables[key] = &tableDef{
schemaName: schema,
tableName: table,
columns: make(map[string]*columnDef),
primaryKeys: make([]*constraintDef, 0),
constraints: make([]*constraintDef, 0),
line: line,
}
}
return st.tables[key]
}
func (st *symbolTable) getTable(schema, table string) *tableDef {
return st.tables[qualifiedName(schema, table)]
}
func (st *symbolTable) addIndex(schema, index, table string, line int) {
key := qualifiedName(schema, index)
st.indexes[key] = &indexDef{
schemaName: schema,
indexName: index,
tableName: table,
line: line,
}
}
func (st *symbolTable) addView(schema, view string, selectQuery ast.Node, line int) {
key := qualifiedName(schema, view)
st.views[key] = &viewDef{
schemaName: schema,
viewName: view,
selectQuery: selectQuery,
line: line,
}
}
func (st *symbolTable) addConstraintName(schema, table, constraintName string, constraint *constraintDef) bool {
// PRIMARY KEY and UNIQUE constraints must be unique within schema
// CHECK and FOREIGN KEY constraints must be unique within table
isPKOrUK := constraint.constraintType == "PRIMARY KEY" || constraint.constraintType == "UNIQUE"
if isPKOrUK {
// Schema-level uniqueness for PK/UK
if st.schemaConstraintNames[schema] == nil {
st.schemaConstraintNames[schema] = make(map[string]*constraintDef)
}
if _, exists := st.schemaConstraintNames[schema][constraintName]; exists {
return false // Duplicate
}
st.schemaConstraintNames[schema][constraintName] = constraint
} else {
// Table-level uniqueness for CHECK/FK
tableKey := qualifiedName(schema, table)
if st.tableConstraintNames[tableKey] == nil {
st.tableConstraintNames[tableKey] = make(map[string]*constraintDef)
}
if _, exists := st.tableConstraintNames[tableKey][constraintName]; exists {
return false // Duplicate
}
st.tableConstraintNames[tableKey][constraintName] = constraint
}
return true
}
func qualifiedName(schema, name string) string {
if schema == "" {
schema = "public"
}
return schema + "." + name
}
// buildFileSymbolTable iterates omni statements and builds the symbol table.
func buildFileSymbolTable(stmts []omnipg.Statement, fileText string) (*symbolTable, []*storepb.Advice) {
st := newSymbolTable()
var advices []*storepb.Advice
for _, stmt := range stmts {
switch n := stmt.AST.(type) {
case *ast.CreateStmt:
processCreateStmt(n, fileText, st, &advices)
case *ast.IndexStmt:
processIndexStmt(n, fileText, st, &advices)
case *ast.ViewStmt:
processViewStmt(n, fileText, st)
default:
}
}
return st, advices
}
// locToLine converts a byte offset to a 1-based line number.
func locToLine(fileText string, loc ast.Loc) int {
if loc.Start < 0 || fileText == "" {
return 1
}
pos := pgparser.ByteOffsetToRunePosition(fileText, loc.Start)
return int(pos.Line)
}
func processCreateStmt(n *ast.CreateStmt, fileText string, st *symbolTable, advices *[]*storepb.Advice) {
if n.Relation == nil {
return
}
tableName := n.Relation.Relname
schemaName := n.Relation.Schemaname
if schemaName == "" {
schemaName = "public"
}
line := locToLine(fileText, n.Loc)
// Check for duplicate table name
if existing := st.getTable(schemaName, tableName); existing != nil {
*advices = append(*advices, &storepb.Advice{
Status: storepb.Advice_ERROR,
Code: code.SDLDuplicateTableName.Int32(),
Title: "Duplicate table name",
Content: fmt.Sprintf(
"Table '%s.%s' is defined multiple times in the SDL.\n\n"+
"First definition at line %d\n"+
"Duplicate definition at line %d\n\n"+
"Each table can only be defined once per schema.",
schemaName, tableName, existing.line, line,
),
StartPosition: &storepb.Position{
Line: int32(line),
},
})
return
}
table := st.addTable(schemaName, tableName, line)
// Process columns from TableElts
position := 0
if n.TableElts != nil {
for _, item := range n.TableElts.Items {
switch elem := item.(type) {
case *ast.ColumnDef:
processColumnDef(elem, fileText, schemaName, tableName, table, &position, advices)
case *ast.Constraint:
processTableConstraint(elem, fileText, table, st, advices)
default:
}
}
}
// Process Constraints list
if n.Constraints != nil {
for _, item := range n.Constraints.Items {
if c, ok := item.(*ast.Constraint); ok {
processTableConstraint(c, fileText, table, st, advices)
}
}
}
}
func processColumnDef(col *ast.ColumnDef, fileText, schemaName, tableName string, table *tableDef, position *int, advices *[]*storepb.Advice) {
colName := col.Colname
colLine := locToLine(fileText, col.Loc)
// Check for duplicate column name
if _, exists := table.columns[colName]; exists {
*advices = append(*advices, &storepb.Advice{
Status: storepb.Advice_ERROR,
Code: code.SDLDuplicateColumnName.Int32(),
Title: "Duplicate column name",
Content: fmt.Sprintf(
"Column '%s' is defined multiple times in table '%s.%s'.\n\n"+
"Each column can only be defined once per table.",
colName, schemaName, tableName,
),
StartPosition: &storepb.Position{
Line: int32(colLine),
},
})
return
}
dt := extractOmniDataType(col.TypeName)
// Check for NOT NULL: either via IsNotNull flag or via column constraints
notNull := col.IsNotNull
if !notNull && col.Constraints != nil {
for _, cItem := range col.Constraints.Items {
if c, ok := cItem.(*ast.Constraint); ok && c.Contype == ast.CONSTR_NOTNULL {
notNull = true
break
}
}
}
table.columns[colName] = &columnDef{
name: colName,
dataType: dt,
notNull: notNull,
position: *position,
line: colLine,
}
*position++
}
func processTableConstraint(c *ast.Constraint, fileText string, table *tableDef, st *symbolTable, advices *[]*storepb.Advice) {
constraint := &constraintDef{
schemaName: table.schemaName,
tableName: table.tableName,
line: locToLine(fileText, c.Loc),
name: c.Conname,
}
switch c.Contype {
case ast.CONSTR_PRIMARY:
constraint.constraintType = "PRIMARY KEY"
table.primaryKeys = append(table.primaryKeys, constraint)
case ast.CONSTR_UNIQUE:
constraint.constraintType = "UNIQUE"
case ast.CONSTR_CHECK:
constraint.constraintType = "CHECK"
case ast.CONSTR_FOREIGN:
constraint.constraintType = "FOREIGN KEY"
// Extract FK columns
if c.FkAttrs != nil {
for _, item := range c.FkAttrs.Items {
if s, ok := item.(*ast.String); ok {
constraint.fkColumns = append(constraint.fkColumns, s.Str)
}
}
}
// Extract referenced table
if c.Pktable != nil {
constraint.fkReferencedTable = c.Pktable.Relname
constraint.fkReferencedSchema = c.Pktable.Schemaname
if constraint.fkReferencedSchema == "" {
constraint.fkReferencedSchema = "public"
}
}
// Extract referenced columns
if c.PkAttrs != nil {
for _, item := range c.PkAttrs.Items {
if s, ok := item.(*ast.String); ok {
constraint.fkReferencedColumns = append(constraint.fkReferencedColumns, s.Str)
}
}
}
default:
return
}
// Check for duplicate constraint name
if constraint.name != "" {
if !st.addConstraintName(table.schemaName, table.tableName, constraint.name, constraint) {
isPKOrUK := constraint.constraintType == "PRIMARY KEY" || constraint.constraintType == "UNIQUE"
var existing *constraintDef
var scope string
var scopeDetail string
if isPKOrUK {
existing = st.schemaConstraintNames[table.schemaName][constraint.name]
scope = "schema"
scopeDetail = "PostgreSQL requires PRIMARY KEY and UNIQUE constraint names to be unique within a schema."
} else {
tableKey := qualifiedName(table.schemaName, table.tableName)
existing = st.tableConstraintNames[tableKey][constraint.name]
scope = "table"
scopeDetail = "PostgreSQL requires CHECK and FOREIGN KEY constraint names to be unique within a table."
}
*advices = append(*advices, &storepb.Advice{
Status: storepb.Advice_ERROR,
Code: code.SDLDuplicateConstraintName.Int32(),
Title: "Duplicate constraint name",
Content: fmt.Sprintf(
"Constraint '%s' is defined multiple times in %s '%s'.\n\n"+
"First definition: %s on table '%s' (line %d)\n"+
"Duplicate definition: %s on table '%s' (line %d)\n\n"+
"%s\n"+
"Use different names or let PostgreSQL generate unique names automatically.",
constraint.name, scope, table.schemaName,
existing.constraintType, existing.tableName, existing.line,
constraint.constraintType, table.tableName, constraint.line,
scopeDetail,
),
StartPosition: &storepb.Position{
Line: int32(constraint.line),
},
})
}
}
table.constraints = append(table.constraints, constraint)
}
func processIndexStmt(n *ast.IndexStmt, fileText string, st *symbolTable, advices *[]*storepb.Advice) {
indexName := n.Idxname
var schemaName, tableName string
if n.Relation != nil {
schemaName = n.Relation.Schemaname
tableName = n.Relation.Relname
}
if schemaName == "" {
schemaName = "public"
}
if indexName == "" {
return // Unnamed index check is handled by sdl_check.go
}
line := locToLine(fileText, n.Loc)
// Check for duplicate index name
key := qualifiedName(schemaName, indexName)
if existing, exists := st.indexes[key]; exists {
*advices = append(*advices, &storepb.Advice{
Status: storepb.Advice_ERROR,
Code: code.SDLDuplicateIndexName.Int32(),
Title: "Duplicate index name",
Content: fmt.Sprintf(
"Index '%s.%s' is defined multiple times in the SDL.\n\n"+
"First definition at line %d\n"+
"Duplicate definition at line %d\n\n"+
"Each index name must be unique within a schema.",
schemaName, indexName, existing.line, line,
),
StartPosition: &storepb.Position{
Line: int32(line),
},
})
return
}
st.addIndex(schemaName, indexName, tableName, line)
}
func processViewStmt(n *ast.ViewStmt, fileText string, st *symbolTable) {
if n.View == nil {
return
}
viewName := n.View.Relname
schemaName := n.View.Schemaname
if schemaName == "" {
schemaName = "public"
}
line := locToLine(fileText, n.Loc)
st.addView(schemaName, viewName, n.Query, line)
}
// extractOmniDataType extracts data type information from an omni TypeName.
func extractOmniDataType(tn *ast.TypeName) *dataType {
if tn == nil {
return &dataType{baseType: "UNKNOWN"}
}
dt := &dataType{}
// Build the type name from Names list
typeName := omniTypeNameStr(tn)
dt.baseType = strings.ToUpper(typeName)
// Extract length/precision from Typmods
if tn.Typmods != nil {
for i, item := range tn.Typmods.Items {
val := extractIntValue(item)
if val < 0 {
continue
}
if i == 0 {
dt.length = val
dt.precision = val
}
if i == 1 {
dt.scale = val
}
}
}
// Check for array type
if tn.ArrayBounds != nil && len(tn.ArrayBounds.Items) > 0 {
dt.baseType = strings.TrimSuffix(dt.baseType, "[]")
}
return dt
}
// extractIntValue extracts an integer value from an AST node.
func extractIntValue(item ast.Node) int {
switch v := item.(type) {
case *ast.Integer:
return int(v.Ival)
case *ast.A_Const:
if iv, ok := v.Val.(*ast.Integer); ok {
return int(iv.Ival)
}
default:
}
return -1
}
// omniTypeNameStr builds a type name string from a TypeName node.
func omniTypeNameStr(tn *ast.TypeName) string {
if tn == nil || tn.Names == nil {
return "UNKNOWN"
}
var parts []string
for _, item := range tn.Names.Items {
if s, ok := item.(*ast.String); ok {
parts = append(parts, s.Str)
}
}
if len(parts) == 0 {
return "UNKNOWN"
}
// The Names list typically contains [catalog, typename] e.g. ["pg_catalog", "int4"]
// We want just the last part (the actual type name).
typeName := parts[len(parts)-1]
// Map common internal type names to SQL type names
typeMap := map[string]string{
"int2": "SMALLINT",
"int4": "INTEGER",
"int8": "BIGINT",
"float4": "REAL",
"float8": "DOUBLE PRECISION",
"bool": "BOOLEAN",
"varchar": "VARCHAR",
"bpchar": "CHARACTER",
"numeric": "NUMERIC",
}
if mapped, ok := typeMap[typeName]; ok {
return mapped
}
return typeName
}
// Pass 2: Validate references
func (c *sdlIntegrityChecker) checkMultiplePrimaryKeys() {
for _, table := range c.symbolTable.tables {
if len(table.primaryKeys) > 1 {
var pkDescriptions []string
for i, pk := range table.primaryKeys {
name := pk.name
if name == "" {
name = "<unnamed>"
}
pkDescriptions = append(pkDescriptions, fmt.Sprintf(" %d. %s at line %d", i+1, name, pk.line))
}
c.adviceList = append(c.adviceList, &storepb.Advice{
Status: storepb.Advice_ERROR,
Code: code.SDLMultiplePrimaryKey.Int32(),
Title: "Multiple primary keys defined",
Content: fmt.Sprintf(
"Table '%s.%s' has multiple PRIMARY KEY constraints defined.\n\n"+
"Found %d primary key constraints:\n%s\n\n"+
"A table can only have one PRIMARY KEY constraint.\n"+
"If you need to enforce uniqueness on multiple column combinations, use UNIQUE constraints instead.",
table.schemaName, table.tableName,
len(table.primaryKeys),
strings.Join(pkDescriptions, "\n"),
),
StartPosition: &storepb.Position{
Line: int32(table.primaryKeys[1].line), // Point to second PK
},
})
}
}
}
type tableReference struct {
schemaName string
tableName string
}
// getViewDependencies extracts view dependencies by walking the AST for RangeVar references.
func getViewDependencies(query ast.Node, defaultSchema string) []tableReference {
if query == nil {
return nil
}
cteNames := extractCTENames(query)
var refs []tableReference
seen := make(map[string]bool)
ast.Inspect(query, func(n ast.Node) bool {
rv, ok := n.(*ast.RangeVar)
if !ok {
return true
}
if rv.Relname == "" {
return true
}
// Skip CTE references
if cteNames[rv.Relname] {
return true
}
schema := rv.Schemaname
if schema == "" {
schema = defaultSchema
}
key := schema + "." + rv.Relname
if !seen[key] {
seen[key] = true
refs = append(refs, tableReference{schemaName: schema, tableName: rv.Relname})
}
return true
})
return refs
}
// extractCTENames extracts all CTE names from a query's WITH clause.
func extractCTENames(query ast.Node) map[string]bool {
cteNames := make(map[string]bool)
sel, ok := query.(*ast.SelectStmt)
if !ok || sel == nil || sel.WithClause == nil || sel.WithClause.Ctes == nil {
return cteNames
}
for _, item := range sel.WithClause.Ctes.Items {
if cte, ok := item.(*ast.CommonTableExpr); ok {
cteNames[cte.Ctename] = true
}
}
return cteNames
}
// Helper functions
func getConstraintName(constraint *constraintDef) string {
if constraint.name != "" {
return constraint.name
}
return "<unnamed>"
}
func getColumnList(table *tableDef) string {
cols := make([]string, 0, len(table.columns))
for colName := range table.columns {
cols = append(cols, colName)
}
if len(cols) == 0 {
return "<no columns>"
}
return strings.Join(cols, ", ")
}