Skip to content

SigNoz is an open-source, OpenTelemetry-native observability platform for your team and their AI agents. Get logs, metrics, and traces in one tool with features like APM, distributed tracing, log management, infra monitoring, etc. Combined with SigNoz MCP and a native AI teammate (in SigNoz Cloud) it helps you build more resilient apps.

11.6 KB
Raw Blame
package postgressqlschema

import (
	"context"
	"database/sql"
	"log/slog"

	"github.com/uptrace/bun"

	"github.com/SigNoz/signoz/pkg/errors"
	"github.com/SigNoz/signoz/pkg/factory"
	"github.com/SigNoz/signoz/pkg/sqlschema"
	"github.com/SigNoz/signoz/pkg/sqlstore"
)

type provider struct {
	settings factory.ScopedProviderSettings
	fmter    sqlschema.SQLFormatter
	sqlstore sqlstore.SQLStore
	operator sqlschema.SQLOperator
}

func NewFactory(sqlstore sqlstore.SQLStore) factory.ProviderFactory[sqlschema.SQLSchema, sqlschema.Config] {
	return factory.NewProviderFactory(factory.MustNewName("postgres"), func(ctx context.Context, providerSettings factory.ProviderSettings, config sqlschema.Config) (sqlschema.SQLSchema, error) {
		return New(ctx, providerSettings, config, sqlstore)
	})
}

func New(ctx context.Context, providerSettings factory.ProviderSettings, config sqlschema.Config, sqlstore sqlstore.SQLStore) (sqlschema.SQLSchema, error) {
	settings := factory.NewScopedProviderSettings(providerSettings, "github.com/SigNoz/signoz/pkg/sqlschema/postgressqlschema")
	fmter := Formatter{Formatter: sqlschema.NewFormatter(sqlstore.BunDB().Dialect())}

	return &provider{
		sqlstore: sqlstore,
		fmter:    fmter,
		settings: settings,
		operator: sqlschema.NewOperator(fmter, sqlschema.OperatorSupport{
			SCreateAndDropConstraint:                        true,
			SAlterTableAddAndDropColumnIfNotExistsAndExists: true,
			SAlterTableAlterColumnSetAndDrop:                true,
		}),
	}, nil
}

func (provider *provider) Formatter() sqlschema.SQLFormatter {
	return provider.fmter
}

func (provider *provider) Operator() sqlschema.SQLOperator {
	return provider.operator
}

func (provider *provider) GetTable(ctx context.Context, tableName sqlschema.TableName) (*sqlschema.Table, []*sqlschema.UniqueConstraint, error) {
	columns := []struct {
		ColumnName  string  `bun:"column_name"`
		Nullable    bool    `bun:"nullable"`
		SQLDataType string  `bun:"udt_name"`
		DefaultVal  *string `bun:"column_default"`
	}{}

	err := provider.
		sqlstore.
		BunDB().
		NewRaw(`
SELECT 
    c.column_name, 
	c.is_nullable = 'YES' as nullable, 
	c.udt_name, 
	c.column_default 
FROM 
    information_schema.columns AS c
WHERE
    c.table_name = ?`, string(tableName)).
		Scan(ctx, &columns)
	if err != nil {
		return nil, nil, err
	}

	if len(columns) == 0 {
		return nil, nil, provider.sqlstore.WrapNotFoundErrf(sql.ErrNoRows, errors.CodeNotFound, "table (%s) not found", tableName)
	}

	sqlschemaColumns := make([]*sqlschema.Column, 0)
	for _, column := range columns {
		columnDefault := ""
		if column.DefaultVal != nil {
			columnDefault = *column.DefaultVal
		}

		sqlschemaColumns = append(sqlschemaColumns, &sqlschema.Column{
			Name:     sqlschema.ColumnName(column.ColumnName),
			Nullable: column.Nullable,
			DataType: provider.fmter.DataTypeOf(column.SQLDataType),
			Default:  columnDefault,
		})
	}

	constraintsRows, err := provider.
		sqlstore.
		BunDB().
		QueryContext(ctx, `
SELECT 
    c.column_name, 
	constraint_name, 
	constraint_type 
FROM 
    information_schema.table_constraints tc
	JOIN information_schema.constraint_column_usage AS ccu USING (constraint_schema, constraint_catalog, table_name, constraint_name) 
	JOIN information_schema.columns AS c ON c.table_schema = tc.constraint_schema AND tc.table_name = c.table_name AND ccu.column_name = c.column_name 
WHERE
    c.table_name = ?`, string(tableName))
	if err != nil {
		return nil, nil, err
	}

	defer func() {
		if err := constraintsRows.Close(); err != nil {
			provider.settings.Logger().ErrorContext(ctx, "error closing rows", errors.Attr(err))
		}
	}()

	var primaryKeyConstraint *sqlschema.PrimaryKeyConstraint
	uniqueConstraintsMap := make(map[string]*sqlschema.UniqueConstraint)
	for constraintsRows.Next() {
		var (
			name           string
			constraintName string
			constraintType string
		)

		if err := constraintsRows.Scan(&name, &constraintName, &constraintType); err != nil {
			return nil, nil, err
		}

		if constraintType == "PRIMARY KEY" {
			if primaryKeyConstraint == nil {
				primaryKeyConstraint = (&sqlschema.PrimaryKeyConstraint{
					ColumnNames: []sqlschema.ColumnName{sqlschema.ColumnName(name)},
				}).Named(constraintName).(*sqlschema.PrimaryKeyConstraint)
			} else {
				primaryKeyConstraint.ColumnNames = append(primaryKeyConstraint.ColumnNames, sqlschema.ColumnName(name))
			}
		}

		if constraintType == "UNIQUE" {
			if _, ok := uniqueConstraintsMap[constraintName]; !ok {
				uniqueConstraintsMap[constraintName] = (&sqlschema.UniqueConstraint{
					ColumnNames: []sqlschema.ColumnName{sqlschema.ColumnName(name)},
				}).Named(constraintName).(*sqlschema.UniqueConstraint)
			} else {
				uniqueConstraintsMap[constraintName].ColumnNames = append(uniqueConstraintsMap[constraintName].ColumnNames, sqlschema.ColumnName(name))
			}
		}
	}

	foreignKeyConstraintsRows, err := provider.
		sqlstore.
		BunDB().
		QueryContext(ctx, `
SELECT 
    tc.constraint_name, 
	kcu.table_name AS referencing_table, 
	kcu.column_name AS referencing_column, 
	ccu.table_name AS referenced_table, 
	ccu.column_name AS referenced_column 
FROM
    information_schema.key_column_usage kcu 
    JOIN information_schema.table_constraints tc ON kcu.constraint_name = tc.constraint_name AND kcu.table_schema = tc.table_schema 
    JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema
WHERE 
    tc.constraint_type = ?
	AND kcu.table_name = ?`, "FOREIGN KEY", string(tableName))
	if err != nil {
		return nil, nil, err
	}

	defer func() {
		if err := foreignKeyConstraintsRows.Close(); err != nil {
			provider.settings.Logger().ErrorContext(ctx, "error closing rows", errors.Attr(err))
		}
	}()

	foreignKeyConstraints := make([]*sqlschema.ForeignKeyConstraint, 0)
	for foreignKeyConstraintsRows.Next() {
		var (
			constraintName    string
			referencingTable  string
			referencingColumn string
			referencedTable   string
			referencedColumn  string
		)

		if err := foreignKeyConstraintsRows.Scan(&constraintName, &referencingTable, &referencingColumn, &referencedTable, &referencedColumn); err != nil {
			return nil, nil, err
		}

		foreignKeyConstraints = append(foreignKeyConstraints, (&sqlschema.ForeignKeyConstraint{
			ReferencingColumnName: sqlschema.ColumnName(referencingColumn),
			ReferencedTableName:   sqlschema.TableName(referencedTable),
			ReferencedColumnName:  sqlschema.ColumnName(referencedColumn),
		}).Named(constraintName).(*sqlschema.ForeignKeyConstraint))
	}

	uniqueConstraints := make([]*sqlschema.UniqueConstraint, 0)
	for _, uniqueConstraint := range uniqueConstraintsMap {
		uniqueConstraints = append(uniqueConstraints, uniqueConstraint)
	}

	return &sqlschema.Table{
		Name:                  tableName,
		Columns:               sqlschemaColumns,
		PrimaryKeyConstraint:  primaryKeyConstraint,
		ForeignKeyConstraints: foreignKeyConstraints,
	}, uniqueConstraints, nil
}

func (provider *provider) GetIndices(ctx context.Context, name sqlschema.TableName) ([]sqlschema.Index, error) {
	// A key is one entry an index is built on: a column (plain) or an expression
	// like lower(x) (functional). Returns one row per key of each non-constraint-
	// backed index on the table, ordered by position. Keys are enumerated with
	// generate_series over indnkeyatts (count of key columns, excluding non-key
	// INCLUDE columns), so expression keys are covered too. Reconstruction uses:
	//
	//   index_name    | index the key belongs to
	//   is_expression | true if the key is an expression, not a plain column
	//   key_def       | rendered key text, e.g. org_id or lower(key)
	//   column_name   | column name for plain keys; NULL for expression keys
	//   predicate     | partial-index WHERE; NULL if not partial (same every row)
	//
	// table_name/unique/primary/column_position are carried over from the prior
	// query (unique gates the loop, column_position orders the keys).
	rows, err := provider.
		sqlstore.
		BunDB().
		QueryContext(ctx, `
SELECT
    ct.relname AS table_name,
    ci.relname AS index_name,
    i.indisunique AS unique,
    i.indisprimary AS primary,
    a.attname AS column_name,
    n AS column_position,
	(i.indkey[n - 1] = 0) AS is_expression,
    pg_get_indexdef(i.indexrelid, n, true) AS key_def,
    pg_get_expr(i.indpred, i.indrelid) AS predicate
FROM
    pg_index i
    LEFT JOIN pg_class ct ON ct.oid = i.indrelid
    LEFT JOIN pg_class ci ON ci.oid = i.indexrelid
    LEFT JOIN pg_constraint con ON con.conindid = i.indexrelid
    CROSS JOIN generate_series(1, i.indnkeyatts) AS n
    LEFT JOIN pg_attribute a ON a.attrelid = ct.oid AND a.attnum = i.indkey[n - 1]
WHERE
    ct.relname = ?
    AND ct.relkind = 'r'
    AND con.oid IS NULL
ORDER BY index_name, column_position`, string(name))
	if err != nil {
		return nil, provider.sqlstore.WrapNotFoundErrf(err, errors.CodeNotFound, "no indices for table (%s) found", name)
	}

	defer func() {
		if err := rows.Close(); err != nil {
			provider.settings.Logger().ErrorContext(ctx, "error closing rows", errors.Attr(err))
		}
	}()

	type indexEntry struct {
		columns   []sqlschema.ColumnName
		predicate *string
		// keyDefs holds every key rendered by pg_get_indexdef, in key order; used
		// for functional indexes where a key may be an expression like lower(x).
		keyDefs       []string
		hasExpression bool
	}

	uniqueIndicesMap := make(map[string]*indexEntry)
	for rows.Next() {
		var (
			tableName  string
			indexName  string
			unique     bool
			primary    bool
			columnName *string
			// starts from 1 and is unused in this function, this is to ensure that the column names are in the correct order
			columnPosition int
			isExpression   bool
			keyDef         string
			predicate      *string
		)

		if err := rows.Scan(&tableName, &indexName, &unique, &primary, &columnName, &columnPosition, &isExpression, &keyDef, &predicate); err != nil {
			return nil, err
		}

		if !unique {
			continue
		}

		entry, ok := uniqueIndicesMap[indexName]
		if !ok {
			entry = &indexEntry{predicate: predicate}
			uniqueIndicesMap[indexName] = entry
		}

		entry.keyDefs = append(entry.keyDefs, keyDef)
		if isExpression {
			entry.hasExpression = true
		} else if columnName != nil {
			entry.columns = append(entry.columns, sqlschema.ColumnName(*columnName))
		}
	}

	indices := make([]sqlschema.Index, 0)
	for indexName, entry := range uniqueIndicesMap {
		// functional partial indexes aren't representable (PartialUniqueIndex has no
		// expressions); skip rather than misrepresent.
		if entry.hasExpression && entry.predicate != nil {
			provider.settings.Logger().WarnContext(ctx, "skipping functional partial unique index; not representable by sqlschema", slog.String("index", indexName), slog.String("table", string(name)))
			continue
		}

		if entry.predicate != nil {
			index := &sqlschema.PartialUniqueIndex{
				TableName:   name,
				ColumnNames: entry.columns,
				Where:       *entry.predicate,
			}

			if index.Name() == indexName {
				indices = append(indices, index)
			} else {
				indices = append(indices, index.Named(indexName))
			}
		} else if entry.hasExpression {
			index := &sqlschema.UniqueIndexWithExpressions{
				TableName:   name,
				Expressions: entry.keyDefs,
			}

			if index.Name() == indexName {
				indices = append(indices, index)
			} else {
				indices = append(indices, index.Named(indexName))
			}
		} else {
			index := &sqlschema.UniqueIndex{
				TableName:   name,
				ColumnNames: entry.columns,
			}

			if index.Name() == indexName {
				indices = append(indices, index)
			} else {
				indices = append(indices, index.Named(indexName))
			}
		}
	}

	return indices, nil
}

func (provider *provider) ToggleFKEnforcement(_ context.Context, _ bun.IDB, _ bool) error {
	return nil
}