Skip to content

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

59.0 KB
Raw Blame
package oracle

import (
	"context"
	"database/sql"
	"fmt"
	"log/slog"
	"regexp"
	"strings"
	"unicode/utf8"

	metadatapb "github.com/bytebase/omni/metadata"
	"github.com/pkg/errors"

	"github.com/bytebase/bytebase/backend/common"
	"github.com/bytebase/bytebase/backend/common/log"
	"github.com/bytebase/bytebase/backend/plugin/db"
	"github.com/bytebase/bytebase/backend/plugin/db/util"
	"github.com/bytebase/bytebase/backend/plugin/parser/plsql"
)

const systemSchema = "'ANONYMOUS','APPQOSSYS','AUDSYS','CTXSYS','DBSFWUSER','DBSNMP','DGPDB_INT','DIP','DVF','DVSYS','GGSYS','GSMADMIN_INTERNAL','GSMCATUSER','GSMROOTUSER','GSMUSER','LBACSYS','MDDATA','MDSYS','OPS$ORACLE','ORACLE_OCM','OUTLN','REMOTE_SCHEDULER_AGENT','SYS','SYS$UMF','SYSBACKUP','SYSDG','SYSKM','SYSRAC','SYSTEM','XDB','XS$NULL','XS$$NULL','FLOWS_FILES','HR','MDSYS','EXFSYS','MGMT_VIEW','OLAPSYS','ORDDATA','ORDPLUGINS','ORDSYS','OWBSYS','OWBSYS_AUDIT','SCOTT','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYSMAN','WMSYS','OJVMSYS'"

var (
	semVersionRegex       = regexp.MustCompile(`[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+`)
	canonicalVersionRegex = regexp.MustCompile(`[0-9][0-9][a-z]`)
)

// SyncInstance syncs the instance.
func (d *Driver) SyncInstance(ctx context.Context) (*db.InstanceMetadata, error) {
	var fullVersion string
	queryVersion := "SELECT BANNER FROM v$version WHERE banner LIKE 'Oracle%'"
	if err := d.db.QueryRowContext(ctx, queryVersion).Scan(&fullVersion); err != nil {
		return nil, util.FormatErrorWithQuery(err, queryVersion)
	}
	tokens := strings.Fields(fullVersion)
	var version, canonicalVersion string
	for _, token := range tokens {
		if semVersionRegex.MatchString(token) {
			version = token
			continue
		}
		if canonicalVersionRegex.MatchString(token) {
			canonicalVersion = token
			continue
		}
	}
	if canonicalVersion != "" {
		version = fmt.Sprintf("%s (%s)", version, canonicalVersion)
	}

	txn, err := d.db.BeginTx(ctx, nil)
	if err != nil {
		return nil, err
	}
	defer txn.Rollback()

	schemas, err := getSchemas(txn)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to get schemas from database %q", d.databaseName)
	}
	var databases []*metadatapb.DatabaseSchemaMetadata
	for _, schema := range schemas {
		databases = append(databases, &metadatapb.DatabaseSchemaMetadata{
			// The driver can hand back raw non-UTF-8 bytes for dictionary
			// strings on non-UTF-8 charsets (e.g. ZHS16GBK values ending in a
			// truncated multi-byte character); proto string fields reject them
			// at marshal time, so sanitize at the source.
			Name:        common.SanitizeUTF8String(schema),
			ServiceName: "",
		})
	}

	if err := txn.Commit(); err != nil {
		return nil, err
	}

	return &db.InstanceMetadata{
		Version:   version,
		Databases: databases,
	}, nil
}

// SyncDBSchema syncs a single database schema.
func (d *Driver) SyncDBSchema(ctx context.Context) (*metadatapb.DatabaseSchemaMetadata, error) {
	txn, err := d.db.BeginTx(ctx, nil)
	if err != nil {
		return nil, err
	}
	defer txn.Rollback()

	version, err := d.GetVersion()
	if err != nil {
		return nil, errors.Wrapf(err, "failed to get version")
	}

	columnMap, err := getTableColumns(txn, d.databaseName, version)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to get table columns from database %q", d.databaseName)
	}
	tableTriggerMap, viewTriggerMap, err := getTriggers(txn, d.databaseName)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to get triggers from database %q", d.databaseName)
	}
	indexMap, checkConstraintMap, foreignKeyMap, err := getIndexesAndConstraints(txn, d.databaseName)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to get indices")
	}
	tableMap, err := getTables(txn, d.databaseName, columnMap, indexMap, checkConstraintMap, foreignKeyMap, tableTriggerMap)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to get tables from database %q", d.databaseName)
	}
	viewMap, err := getViews(txn, d.databaseName, columnMap, viewTriggerMap)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to get views from database %q", d.databaseName)
	}
	sequences, err := getSequences(txn, d.databaseName)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to get sequences from database %q", d.databaseName)
	}
	dbLinks, err := getDBLinks(txn)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to get db links from database %q", d.databaseName)
	}
	functions, procedures, packages, err := getRoutines(txn, d.databaseName)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to get routines from database %q", d.databaseName)
	}
	materializedViews, err := getMaterializedViews(txn, d.databaseName, indexMap, tableTriggerMap)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to get materialized views from database %q", d.databaseName)
	}
	synonyms, err := getSynonyms(txn, d.databaseName)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to get synonyms from database %q", d.databaseName)
	}
	publicSynonyms, err := getSynonyms(txn, publicSchema)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to get public synonyms")
	}

	if err := txn.Commit(); err != nil {
		return nil, err
	}

	databaseMetadata := &metadatapb.DatabaseSchemaMetadata{
		Name:            d.databaseName,
		ServiceName:     d.serviceName,
		LinkedDatabases: dbLinks,
		PublicSynonyms:  publicSynonyms,
	}
	databaseMetadata.Schemas = append(databaseMetadata.Schemas, &metadatapb.SchemaMetadata{
		Name:              "",
		Tables:            tableMap[d.databaseName],
		Views:             viewMap[d.databaseName],
		MaterializedViews: materializedViews,
		Sequences:         sequences,
		Functions:         functions,
		Procedures:        procedures,
		Packages:          packages,
		Synonyms:          synonyms,
	})
	// Deep-sanitize every string field (names, definitions, defaults, …), not
	// just comments: the go-ora driver returns wholly-unconverted raw bytes
	// for any GBK/SJIS/Big5 value that ends in a dangling multi-byte lead
	// byte, and one invalid string anywhere fails proto marshaling for the
	// entire database metadata (BYT-9916).
	common.SanitizeUTF8Message(databaseMetadata)
	return databaseMetadata, nil
}

// publicSchema owns the public synonyms. No user can take its name (ORA-01920).
const publicSchema = "PUBLIC"

func getSynonyms(txn *sql.Tx, owner string) ([]*metadatapb.SynonymMetadata, error) {
	query := `
		SELECT SYNONYM_NAME, TABLE_OWNER, TABLE_NAME, DB_LINK
		FROM ALL_SYNONYMS
		WHERE OWNER = :1
		ORDER BY SYNONYM_NAME`
	rows, err := txn.Query(query, owner)
	if err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	defer rows.Close()

	var synonyms []*metadatapb.SynonymMetadata
	for rows.Next() {
		var name string
		var targetSchema, targetName, targetDBLink sql.NullString
		if err := rows.Scan(&name, &targetSchema, &targetName, &targetDBLink); err != nil {
			return nil, err
		}
		synonyms = append(synonyms, &metadatapb.SynonymMetadata{
			Name:         name,
			TargetSchema: targetSchema.String,
			TargetName:   targetName.String,
			TargetDbLink: targetDBLink.String,
		})
	}
	if err := rows.Err(); err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	return synonyms, nil
}

func getDBLinks(txn *sql.Tx) ([]*metadatapb.LinkedDatabaseMetadata, error) {
	query := `
	SELECT DB_LINK, HOST, USERNAME
	FROM all_db_links
	ORDER BY DB_LINK`
	slog.Debug("running get db link query")
	rows, err := txn.Query(query)
	if err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	defer rows.Close()

	var result []*metadatapb.LinkedDatabaseMetadata
	for rows.Next() {
		dbLink := &metadatapb.LinkedDatabaseMetadata{}
		var name, host, username sql.NullString
		if err := rows.Scan(&name, &host, &username); err != nil {
			return nil, err
		}
		if !name.Valid {
			continue
		}
		dbLink.Name = name.String
		if host.Valid {
			dbLink.Host = host.String
		}
		if username.Valid {
			dbLink.Username = username.String
		}
		result = append(result, dbLink)
	}
	if err := rows.Err(); err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	if err := rows.Close(); err != nil {
		return nil, errors.Wrapf(err, "failed to close rows")
	}

	return result, nil
}

func getSchemas(txn *sql.Tx) ([]string, error) {
	query := fmt.Sprintf(`
		SELECT username FROM all_users
		WHERE username NOT IN (%s) AND username NOT LIKE 'APEX_%%' ORDER BY username`,
		systemSchema)
	slog.Debug("running get schemas query")
	rows, err := txn.Query(query)
	if err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	defer rows.Close()

	var result []string
	for rows.Next() {
		var schemaName string
		if err := rows.Scan(&schemaName); err != nil {
			return nil, err
		}
		result = append(result, schemaName)
	}
	if err := rows.Err(); err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	if err := rows.Close(); err != nil {
		return nil, errors.Wrapf(err, "failed to close rows")
	}

	return result, nil
}

func getTriggers(txn *sql.Tx, schemaName string) (map[db.TableKey][]*metadatapb.TriggerMetadata, map[db.TableKey][]*metadatapb.TriggerMetadata, error) {
	tableTriggerMap := make(map[db.TableKey][]*metadatapb.TriggerMetadata)
	viewTriggerMap := make(map[db.TableKey][]*metadatapb.TriggerMetadata)

	// Get trigger comments
	triggerCommentMap, err := getTriggerComments(txn, schemaName)
	if err != nil {
		return nil, nil, errors.Wrapf(err, "failed to get trigger comments")
	}

	// A trigger the schema owns on another schema's table is left out: keyed by
	// TABLE_NAME alone, it would land on this schema's table of the same name.
	query := fmt.Sprintf(`
		SELECT TRIGGER_NAME, TABLE_NAME, DESCRIPTION, TRIGGER_BODY, BASE_OBJECT_TYPE, TRIGGER_TYPE, TRIGGERING_EVENT
		FROM sys.all_triggers
		WHERE OWNER = '%s' AND TABLE_OWNER = '%s' AND TABLE_NAME is NOT NULL AND (BASE_OBJECT_TYPE = 'TABLE' OR BASE_OBJECT_TYPE = 'VIEW')
		ORDER BY TRIGGER_NAME`, schemaName, schemaName)

	slog.Debug("running get triggers query")
	rows, err := txn.Query(query)
	if err != nil {
		return nil, nil, util.FormatErrorWithQuery(err, query)
	}
	defer rows.Close()

	for rows.Next() {
		var triggerName, tableName, description, triggerBody, baseObjectType, triggerType, triggeringEvent sql.NullString
		if err := rows.Scan(&triggerName, &tableName, &description, &triggerBody, &baseObjectType, &triggerType, &triggeringEvent); err != nil {
			return nil, nil, err
		}
		if !triggerName.Valid || !tableName.Valid || !description.Valid || !triggerBody.Valid || !baseObjectType.Valid {
			continue
		}
		key := db.TableKey{Schema: schemaName, Table: tableName.String}
		trigger := &metadatapb.TriggerMetadata{
			Name: triggerName.String,
			Body: sanitizeOracleMetadataString(
				schemaName,
				"TRIGGER",
				triggerName.String,
				"body",
				constructTriggerBody(description.String, triggerBody.String),
			),
		}
		if triggerType.Valid {
			trigger.Timing = triggerType.String
		}
		if triggeringEvent.Valid {
			trigger.Event = triggeringEvent.String
		}
		// Add trigger comment if available
		triggerKey := db.TableKey{Schema: schemaName, Table: triggerName.String}
		if comment, ok := triggerCommentMap[triggerKey]; ok {
			trigger.Comment = comment
		}

		if baseObjectType.String == "TABLE" {
			tableTriggerMap[key] = append(tableTriggerMap[key], trigger)
		} else {
			viewTriggerMap[key] = append(viewTriggerMap[key], trigger)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, nil, util.FormatErrorWithQuery(err, query)
	}

	return tableTriggerMap, viewTriggerMap, nil
}

func constructTriggerBody(description, triggerBody string) string {
	var buf strings.Builder
	_, _ = buf.WriteString("CREATE OR REPLACE TRIGGER ")
	_, _ = buf.WriteString(description)
	_, _ = buf.WriteString(triggerBody)
	return buf.String()
}

// getTables gets all tables of a database.
func getTables(
	txn *sql.Tx,
	schemaName string,
	columnMap map[db.TableKey][]*metadatapb.ColumnMetadata,
	indexMap map[db.TableKey][]*metadatapb.IndexMetadata,
	checkConstraintMap map[db.TableKey][]*metadatapb.CheckConstraintMetadata,
	foreignKeyMap map[db.TableKey][]*metadatapb.ForeignKeyMetadata,
	triggerMap map[db.TableKey][]*metadatapb.TriggerMetadata,
) (map[string][]*metadatapb.TableMetadata, error) {
	columnCommentMap, err := getTableColumnComments(txn, schemaName)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to get table column comments")
	}
	for key, columns := range columnMap {
		for _, column := range columns {
			comment, ok := columnCommentMap[db.ColumnKey{Schema: key.Schema, Table: key.Table, Column: column.Name}]
			if ok {
				column.Comment = comment
			}
		}
	}
	tableCommentMap, err := getTableComments(txn, schemaName)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to get table comments")
	}
	tableMap := make(map[string][]*metadatapb.TableMetadata)
	appendTables := func(query string, skipDump bool) error {
		rows, err := txn.Query(query)
		if err != nil {
			return util.FormatErrorWithQuery(err, query)
		}
		defer rows.Close()

		for rows.Next() {
			table := &metadatapb.TableMetadata{SkipDump: skipDump}
			var schemaName string
			// https://github.com/rana/ora/issues/57#issuecomment-179909837
			// NUMBER in Oracle can hold 38 decimal digits, so int64 is not enough with its 19 decimal digits.
			// float64 is a little bit better - not precise enough, but won't overflow.
			var count sql.NullFloat64
			if err := rows.Scan(&schemaName, &table.Name, &count); err != nil {
				return err
			}
			table.RowCount = int64(count.Float64)
			key := db.TableKey{Schema: schemaName, Table: table.Name}
			table.Columns = columnMap[key]
			table.Indexes = indexMap[key]
			table.CheckConstraints = checkConstraintMap[key]
			table.ForeignKeys = foreignKeyMap[key]
			table.Triggers = triggerMap[key]
			if comment, ok := tableCommentMap[db.TableKey{Schema: schemaName, Table: table.Name}]; ok {
				table.Comment = comment
			}

			tableMap[schemaName] = append(tableMap[schemaName], table)
		}
		if err := rows.Err(); err != nil {
			return util.FormatErrorWithQuery(err, query)
		}
		return rows.Close()
	}

	query := fmt.Sprintf(`
		SELECT OWNER, TABLE_NAME, NUM_ROWS
		FROM all_tables
		WHERE OWNER = '%s'
		AND TABLE_NAME NOT IN (
			SELECT MVIEW_NAME FROM all_mviews WHERE OWNER = '%s'
		)
		ORDER BY TABLE_NAME`, schemaName, schemaName)
	slog.Debug("running get tables query")
	if err := appendTables(query, false); err != nil {
		return nil, err
	}

	// ALL_TABLES omits object and XMLType tables (CREATE TABLE ... OF), which
	// DROP TABLE drops all the same, so they are listed too, with SkipDump. The
	// ALL_MVIEWS subquery drops an object materialized view's container and keeps
	// the plan on the owner index: without it Oracle full-scans OBJ$, which holds
	// every schema's objects.
	objectTableQuery := fmt.Sprintf(`
		SELECT OWNER, TABLE_NAME, NUM_ROWS
		FROM all_object_tables
		WHERE OWNER = '%s'
		AND TABLE_NAME NOT IN (
			SELECT MVIEW_NAME FROM all_mviews WHERE OWNER = '%s'
		)
		ORDER BY TABLE_NAME`, schemaName, schemaName)
	slog.Debug("running get object tables query")
	if err := appendTables(objectTableQuery, true); err != nil {
		return nil, err
	}

	return tableMap, nil
}

func getTableComments(txn *sql.Tx, schemaName string) (map[db.TableKey]string, error) {
	tableCommentMap := make(map[db.TableKey]string)

	query := ""
	if schemaName == "" {
		query = fmt.Sprintf(`
		SELECT OWNER, TABLE_NAME, COMMENTS
		FROM all_tab_comments
		WHERE OWNER NOT IN (%s) AND OWNER NOT LIKE 'APEX_%%' AND COMMENTS IS NOT NULL
		ORDER BY OWNER, TABLE_NAME`, systemSchema)
	} else {
		query = fmt.Sprintf(`
		SELECT OWNER, TABLE_NAME, COMMENTS
		FROM all_tab_comments
		WHERE OWNER = '%s' AND COMMENTS IS NOT NULL
		ORDER BY TABLE_NAME`, schemaName)
	}
	slog.Debug("running get table comments query")
	rows, err := txn.Query(query)
	if err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	defer rows.Close()

	for rows.Next() {
		var schemaName, tableName, comment string
		if err := rows.Scan(&schemaName, &tableName, &comment); err != nil {
			return nil, err
		}
		key := db.TableKey{Schema: schemaName, Table: tableName}
		tableCommentMap[key] = common.SanitizeUTF8String(comment)
	}
	if err := rows.Err(); err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	if err := rows.Close(); err != nil {
		return nil, errors.Wrapf(err, "failed to close rows")
	}

	return tableCommentMap, nil
}

func getTableColumnComments(txn *sql.Tx, schemaName string) (map[db.ColumnKey]string, error) {
	columnCommentsMap := make(map[db.ColumnKey]string)

	query := ""
	if schemaName == "" {
		query = fmt.Sprintf(`
		SELECT OWNER, TABLE_NAME, COLUMN_NAME, COMMENTS
		FROM all_col_comments
		WHERE OWNER NOT IN (%s) AND OWNER NOT LIKE 'APEX_%%' AND COMMENTS IS NOT NULL
		ORDER BY OWNER, TABLE_NAME, COLUMN_NAME`, systemSchema)
	} else {
		query = fmt.Sprintf(`
		SELECT OWNER, TABLE_NAME, COLUMN_NAME, COMMENTS
		FROM all_col_comments
		WHERE OWNER = '%s' AND COMMENTS IS NOT NULL
		ORDER BY TABLE_NAME, COLUMN_NAME`, schemaName)
	}
	slog.Debug("running get table column comments query")
	rows, err := txn.Query(query)
	if err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	defer rows.Close()
	for rows.Next() {
		var schemaName, tableName, columnName, comment string
		if err := rows.Scan(&schemaName, &tableName, &columnName, &comment); err != nil {
			return nil, err
		}
		key := db.ColumnKey{Schema: schemaName, Table: tableName, Column: columnName}
		columnCommentsMap[key] = common.SanitizeUTF8String(comment)
	}
	if err := rows.Err(); err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	if err := rows.Close(); err != nil {
		return nil, errors.Wrapf(err, "failed to close rows")
	}

	return columnCommentsMap, nil
}

// getTableColumns gets the columns of a table.
func getTableColumns(txn *sql.Tx, schemaName string, version *plsql.Version) (map[db.TableKey][]*metadatapb.ColumnMetadata, error) {
	columnsMap := make(map[db.TableKey][]*metadatapb.ColumnMetadata)

	// Invisible columns have no COLUMN_ID. They sort after the visible columns
	// and take the positions that follow. ALL_TAB_COLUMNS is ALL_TAB_COLS with
	// USER_GENERATED = 'YES', which leaves out hidden system columns; the newer
	// query reads ALL_TAB_COLS under that filter to get VIRTUAL_COLUMN too.
	query := ""
	var virtualColumns map[db.ColumnKey]bool
	// https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/ALL_TAB_COLS.html#GUID-85036F42-140A-406B-BE11-0AC49A00DBA3
	equalOrHigherThan12c2release := version.First > 12 || (version.First == 12 && version.Second >= 2)
	if equalOrHigherThan12c2release {
		query = fmt.Sprintf(`
		SELECT
			OWNER,
			TABLE_NAME,
			COLUMN_NAME,
			DATA_TYPE,
			DATA_LENGTH,
			DATA_PRECISION,
			DATA_SCALE,
			COLUMN_ID,
			DATA_DEFAULT,
			NULLABLE,
			COLLATION,
			DEFAULT_ON_NULL,
			VIRTUAL_COLUMN
		FROM sys.all_tab_cols
		WHERE OWNER = '%s' AND USER_GENERATED = 'YES'
		ORDER BY TABLE_NAME, COLUMN_ID NULLS LAST, COLUMN_NAME`, schemaName)
	} else {
		// Failing to tell virtual columns apart only keeps their expressions
		// in Default, as before, so it must not fail the sync.
		var err error
		virtualColumns, err = getVirtualColumns(txn, schemaName)
		if err != nil {
			slog.Warn("failed to list Oracle virtual columns", slog.String("schema", schemaName), log.BBError(err))
		}
		query = fmt.Sprintf(`
		SELECT
			OWNER,
			TABLE_NAME,
			COLUMN_NAME,
			DATA_TYPE,
			DATA_LENGTH,
			DATA_PRECISION,
			DATA_SCALE,
			COLUMN_ID,
			DATA_DEFAULT,
			NULLABLE,
			NULL,
			NULL,
			NULL
		FROM sys.all_tab_columns
		WHERE OWNER = '%s'
		ORDER BY TABLE_NAME, COLUMN_ID NULLS LAST, COLUMN_NAME`, schemaName)
	}

	slog.Debug("running get columns query")
	rows, err := txn.Query(query)
	if err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	defer rows.Close()
	for rows.Next() {
		column := &metadatapb.ColumnMetadata{}
		var schemaName, tableName, nullable string
		var defaultStr, collation, defaultOnNull, virtualColumn sql.NullString
		var dataLength, dataPrecision, dataScale sql.NullInt64
		var columnID sql.NullInt32
		if err := rows.Scan(
			&schemaName,
			&tableName,
			&column.Name,
			&column.Type,
			&dataLength,
			&dataPrecision,
			&dataScale,
			&columnID,
			&defaultStr,
			&nullable,
			&collation,
			&defaultOnNull,
			&virtualColumn,
		); err != nil {
			return nil, err
		}
		column.Type = getTypeString(column.Type, dataLength, dataPrecision, dataScale)
		if defaultStr.Valid {
			// Clean up the default expression to remove current schema name and fix NEXTVAL syntax
			cleanedDefault := cleanDefaultExpression(defaultStr.String, schemaName)
			// DATA_DEFAULT holds a virtual column's expression.
			if virtualColumn.String == "YES" || virtualColumns[db.ColumnKey{Schema: schemaName, Table: tableName, Column: column.Name}] {
				column.Generation = &metadatapb.GenerationMetadata{
					Type:       metadatapb.GenerationMetadata_TYPE_VIRTUAL,
					Expression: cleanedDefault,
				}
			} else {
				column.Default = cleanedDefault
			}
		}
		isNullBool, err := util.ConvertYesNo(nullable)
		if err != nil {
			return nil, err
		}
		column.Nullable = isNullBool
		if collation.Valid {
			column.Collation = collation.String
		}
		if defaultOnNull.Valid && defaultOnNull.String == "YES" {
			column.DefaultOnNull = true
		}

		key := db.TableKey{Schema: schemaName, Table: tableName}
		if columnID.Valid {
			column.Position = columnID.Int32
		} else {
			column.IsInvisible = true
			column.Position = 1
			if columns := columnsMap[key]; len(columns) > 0 {
				column.Position = columns[len(columns)-1].Position + 1
			}
		}
		columnsMap[key] = append(columnsMap[key], column)
	}
	if err := rows.Err(); err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	if err := rows.Close(); err != nil {
		return nil, errors.Wrapf(err, "failed to close rows")
	}

	return columnsMap, nil
}

// getVirtualColumns lists the virtual columns for versions whose column query
// reads ALL_TAB_COLUMNS, which has no VIRTUAL_COLUMN.
func getVirtualColumns(txn *sql.Tx, schemaName string) (map[db.ColumnKey]bool, error) {
	query := fmt.Sprintf(`
		SELECT TABLE_NAME, COLUMN_NAME
		FROM sys.all_tab_cols
		WHERE OWNER = '%s' AND VIRTUAL_COLUMN = 'YES'`, schemaName)
	slog.Debug("running get virtual columns query")
	rows, err := txn.Query(query)
	if err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	defer rows.Close()
	virtualColumns := make(map[db.ColumnKey]bool)
	for rows.Next() {
		var tableName, columnName string
		if err := rows.Scan(&tableName, &columnName); err != nil {
			return nil, err
		}
		virtualColumns[db.ColumnKey{Schema: schemaName, Table: tableName, Column: columnName}] = true
	}
	if err := rows.Err(); err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	return virtualColumns, nil
}

func getTypeString(dataType string, dataLength, dataPrecision, dataScale sql.NullInt64) string {
	switch dataType {
	case "VARCHAR2", "CHAR":
		return fmt.Sprintf("%s(%d BYTE)", dataType, dataLength.Int64)
	case "NVARCHAR2", "RAW", "UROWID", "NCHAR":
		return fmt.Sprintf("%s(%d)", dataType, dataLength.Int64)
	case "NUMBER":
		switch {
		case !dataPrecision.Valid || dataPrecision.Int64 == 0:
		// do nothing
		case dataPrecision.Valid && dataPrecision.Int64 > 0 && (!dataScale.Valid || dataScale.Int64 == 0):
			return fmt.Sprintf("%s(%d)", dataType, dataPrecision.Int64)
		case dataPrecision.Valid && dataPrecision.Int64 > 0 && dataScale.Valid && dataScale.Int64 > 0:
			return fmt.Sprintf("%s(%d,%d)", dataType, dataPrecision.Int64, dataScale.Int64)
		default:
			// do nothing
		}
	case "FLOAT":
		switch {
		case !dataPrecision.Valid || dataPrecision.Int64 == 0:
		// do nothing
		case dataPrecision.Valid && dataPrecision.Int64 > 0:
			return fmt.Sprintf("%s(%d)", dataType, dataPrecision.Int64)
		default:
			// do nothing
		}
	default:
		// return dataType as-is for unhandled types
	}
	return dataType
}

func getOuterSchemaRColumns(txn *sql.Tx, outerRTableMap map[db.ConstraintKey]string, outerRColumnMap map[db.ConstraintKey][]string, schemaName, constraintName string) (string, []string, error) {
	queryColumns := fmt.Sprintf(`
		SELECT
			TABLE_NAME,
			CONSTRAINT_NAME,
			COLUMN_NAME
		FROM
			SYS.ALL_CONS_COLUMNS
		WHERE
			OWNER = '%s'
		ORDER BY TABLE_NAME, CONSTRAINT_NAME, POSITION`, schemaName)

	slog.Debug("running get outer schema reference columns query")
	rows, err := txn.Query(queryColumns)
	if err != nil {
		return "", nil, util.FormatErrorWithQuery(err, queryColumns)
	}
	defer rows.Close()

	for rows.Next() {
		var tableName, constraintName string
		var columnName sql.NullString
		if err := rows.Scan(&tableName, &constraintName, &columnName); err != nil {
			return "", nil, err
		}
		if !columnName.Valid {
			continue
		}
		key := db.ConstraintKey{Schema: schemaName, Constraint: constraintName}
		outerRTableMap[key] = tableName
		outerRColumnMap[key] = append(outerRColumnMap[key], columnName.String)
	}
	if err := rows.Err(); err != nil {
		return "", nil, util.FormatErrorWithQuery(err, queryColumns)
	}

	constraintKey := db.ConstraintKey{Schema: schemaName, Constraint: constraintName}
	return outerRTableMap[constraintKey], outerRColumnMap[constraintKey], nil
}

func getConstraints(txn *sql.Tx, schemaName string) (
	map[db.TableKey][]*metadatapb.IndexMetadata,
	map[db.TableKey][]*metadatapb.CheckConstraintMetadata,
	map[db.TableKey][]*metadatapb.ForeignKeyMetadata,
	map[db.IndexKey]bool,
	error,
) {
	queryConstraintColumns := fmt.Sprintf(`
		SELECT
			TABLE_NAME,
			CONSTRAINT_NAME,
			COLUMN_NAME
		FROM SYS.ALL_CONS_COLUMNS
		WHERE OWNER = '%s'
		ORDER BY TABLE_NAME, CONSTRAINT_NAME, POSITION`, schemaName)

	slog.Debug("running get constraint columns query")
	constraintColumnRows, err := txn.Query(queryConstraintColumns)
	if err != nil {
		return nil, nil, nil, nil, util.FormatErrorWithQuery(err, queryConstraintColumns)
	}
	defer constraintColumnRows.Close()
	constraintColumnMap := make(map[db.ConstraintKey][]string)
	constraintTableMap := make(map[db.ConstraintKey]string)
	for constraintColumnRows.Next() {
		var tableName, constraintName string
		var columnName sql.NullString
		if err := constraintColumnRows.Scan(&tableName, &constraintName, &columnName); err != nil {
			return nil, nil, nil, nil, err
		}
		key := db.ConstraintKey{Schema: schemaName, Constraint: constraintName}
		if columnName.Valid {
			constraintColumnMap[key] = append(constraintColumnMap[key], columnName.String)
		}
		constraintTableMap[key] = tableName
	}
	if err := constraintColumnRows.Err(); err != nil {
		return nil, nil, nil, nil, util.FormatErrorWithQuery(err, queryConstraintColumns)
	}

	queryConstraints := fmt.Sprintf(`
		SELECT
			TABLE_NAME,
			CONSTRAINT_NAME,
			CONSTRAINT_TYPE,
			SEARCH_CONDITION,
			R_OWNER,
			R_CONSTRAINT_NAME,
			DELETE_RULE
		FROM SYS.ALL_CONSTRAINTS
		WHERE OWNER = '%s'
		ORDER BY TABLE_NAME, CONSTRAINT_NAME`, schemaName)

	slog.Debug("running get constraints query")
	constraintRows, err := txn.Query(queryConstraints)
	if err != nil {
		return nil, nil, nil, nil, util.FormatErrorWithQuery(err, queryConstraints)
	}
	defer constraintRows.Close()
	indexMap := make(map[db.TableKey][]*metadatapb.IndexMetadata)
	checkConstraintMap := make(map[db.TableKey][]*metadatapb.CheckConstraintMetadata)
	foreignKeyMap := make(map[db.TableKey][]*metadatapb.ForeignKeyMetadata)
	isConstraint := make(map[db.IndexKey]bool)
	outerRColumnMap := make(map[db.ConstraintKey][]string)
	outerRTableMap := make(map[db.ConstraintKey]string)
	for constraintRows.Next() {
		var tableName, constraintName, constraintType string
		var searchCondition, rOwner, rConstraintName, deleteRule sql.NullString
		if err := constraintRows.Scan(&tableName, &constraintName, &constraintType, &searchCondition, &rOwner, &rConstraintName, &deleteRule); err != nil {
			return nil, nil, nil, nil, err
		}
		key := db.TableKey{Schema: schemaName, Table: tableName}
		constraintKey := db.ConstraintKey{Schema: schemaName, Constraint: constraintName}
		switch constraintType {
		case "P":
			index := &metadatapb.IndexMetadata{
				Name:         constraintName,
				Primary:      true,
				Unique:       true,
				IsConstraint: true,
			}
			if columns, ok := constraintColumnMap[constraintKey]; ok {
				index.Expressions = columns
			}
			indexMap[key] = append(indexMap[key], index)
			isConstraint[db.IndexKey{Schema: schemaName, Table: tableName, Index: constraintName}] = true
		case "U":
			index := &metadatapb.IndexMetadata{
				Name:         constraintName,
				Unique:       true,
				IsConstraint: true,
			}
			if columns, ok := constraintColumnMap[constraintKey]; ok {
				index.Expressions = columns
			}
			indexMap[key] = append(indexMap[key], index)
			isConstraint[db.IndexKey{Schema: schemaName, Table: tableName, Index: constraintName}] = true
		case "C":
			// Skip system-generated check constraints (e.g., SYS_C*)
			if strings.HasPrefix(constraintName, "SYS_C") {
				continue
			}
			constraint := &metadatapb.CheckConstraintMetadata{
				Name: constraintName,
			}
			if searchCondition.Valid {
				constraint.Expression = searchCondition.String
			}
			checkConstraintMap[key] = append(checkConstraintMap[key], constraint)
		case "R":
			if rOwner.Valid && rConstraintName.Valid {
				foreignKey := &metadatapb.ForeignKeyMetadata{
					Name:    constraintName,
					Columns: constraintColumnMap[constraintKey],
				}
				if deleteRule.Valid && deleteRule.String != "NO ACTION" {
					foreignKey.OnDelete = deleteRule.String
				}
				if rOwner.String == schemaName {
					// Same-schema reference - don't set ReferencedSchema for portability
					rConstraintKey := db.ConstraintKey{Schema: rOwner.String, Constraint: rConstraintName.String}
					foreignKey.ReferencedTable = constraintTableMap[rConstraintKey]
					foreignKey.ReferencedColumns = constraintColumnMap[rConstraintKey]
				} else {
					// Cross-schema reference - set ReferencedSchema
					foreignKey.ReferencedSchema = rOwner.String
					foreignKey.ReferencedTable, foreignKey.ReferencedColumns, err = getOuterSchemaRColumns(txn, outerRTableMap, outerRColumnMap, rOwner.String, rConstraintName.String)
					if err != nil {
						return nil, nil, nil, nil, errors.Wrapf(err, "failed to get outer schema reference columns")
					}
				}
				foreignKeyMap[key] = append(foreignKeyMap[key], foreignKey)
			}
		default:
			// Ignore other constraint types
		}
	}
	if err := constraintRows.Err(); err != nil {
		return nil, nil, nil, nil, util.FormatErrorWithQuery(err, queryConstraints)
	}

	return indexMap, checkConstraintMap, foreignKeyMap, isConstraint, nil
}

// getIndexes gets all indices and constraints of a database.
func getIndexesAndConstraints(txn *sql.Tx, schemaName string) (map[db.TableKey][]*metadatapb.IndexMetadata, map[db.TableKey][]*metadatapb.CheckConstraintMetadata, map[db.TableKey][]*metadatapb.ForeignKeyMetadata, error) {
	indexMap, checkConstraintMap, foreignKeyMap, isConstraint, err := getConstraints(txn, schemaName)
	if err != nil {
		return nil, nil, nil, errors.Wrapf(err, "failed to get constraints")
	}

	// Get index comments
	indexCommentMap, err := getIndexComments(txn, schemaName)
	if err != nil {
		return nil, nil, nil, errors.Wrapf(err, "failed to get index comments")
	}

	queryColumn := fmt.Sprintf(`
		SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME, COLUMN_NAME, DESCEND
		FROM sys.all_ind_columns
		WHERE TABLE_OWNER = '%s'
		ORDER BY TABLE_NAME, INDEX_NAME, COLUMN_POSITION`, schemaName)

	slog.Debug("running get index column query")
	indexExpressionMap := make(map[db.IndexKey][]string)
	indexColumnMap := make(map[db.IndexKey][]string)
	descendingMap := make(map[db.IndexKey][]bool)
	colRows, err := txn.Query(queryColumn)
	if err != nil {
		return nil, nil, nil, util.FormatErrorWithQuery(err, queryColumn)
	}
	defer colRows.Close()
	for colRows.Next() {
		var schemaName, tableName, indexName, columnName string
		var descend sql.NullString
		if err := colRows.Scan(&schemaName, &tableName, &indexName, &columnName, &descend); err != nil {
			return nil, nil, nil, err
		}
		key := db.IndexKey{Schema: schemaName, Table: tableName, Index: indexName}

		// Clean up column name but preserve the original if it's already clean
		cleanColumnName := columnName
		if strings.HasPrefix(columnName, "\"") && strings.HasSuffix(columnName, "\"") {
			cleanColumnName = columnName[1 : len(columnName)-1]
		}

		indexColumnMap[key] = append(indexColumnMap[key], cleanColumnName)
		descendingMap[key] = append(descendingMap[key], descend.String == "DESC")
	}
	if err := colRows.Err(); err != nil {
		return nil, nil, nil, util.FormatErrorWithQuery(err, queryColumn)
	}
	if err := colRows.Close(); err != nil {
		return nil, nil, nil, errors.Wrapf(err, "failed to close rows")
	}

	queryExpression := fmt.Sprintf(`
		SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME, COLUMN_EXPRESSION
		FROM sys.all_ind_expressions
		WHERE TABLE_OWNER = '%s'
		ORDER BY TABLE_NAME, INDEX_NAME, COLUMN_POSITION`, schemaName)

	slog.Debug("running get index expression query")
	expRows, err := txn.Query(queryExpression)
	if err != nil {
		return nil, nil, nil, util.FormatErrorWithQuery(err, queryExpression)
	}
	defer expRows.Close()
	for expRows.Next() {
		var schemaName, tableName, indexName, columnExpression string
		if err := expRows.Scan(&schemaName, &tableName, &indexName, &columnExpression); err != nil {
			return nil, nil, nil, err
		}
		key := db.IndexKey{Schema: schemaName, Table: tableName, Index: indexName}
		indexExpressionMap[key] = append(indexExpressionMap[key], columnExpression)
	}
	if err := expRows.Err(); err != nil {
		return nil, nil, nil, util.FormatErrorWithQuery(err, queryExpression)
	}
	if err := expRows.Close(); err != nil {
		return nil, nil, nil, errors.Wrapf(err, "failed to close rows")
	}

	query := fmt.Sprintf(`
		SELECT OWNER, TABLE_NAME, INDEX_NAME, UNIQUENESS, INDEX_TYPE, VISIBILITY
		FROM sys.all_indexes
		WHERE OWNER = '%s'
		ORDER BY TABLE_NAME, INDEX_NAME`, schemaName)

	slog.Debug("running get index query")
	rows, err := txn.Query(query)
	if err != nil {
		return nil, nil, nil, util.FormatErrorWithQuery(err, query)
	}
	defer rows.Close()
	for rows.Next() {
		index := &metadatapb.IndexMetadata{}
		var schemaName, tableName, unique string
		var visibility sql.NullString
		// INDEX_TYPE is NORMAL, or FUNCTION-BASED NORMAL.
		if err := rows.Scan(&schemaName, &tableName, &index.Name, &unique, &index.Type, &visibility); err != nil {
			return nil, nil, nil, err
		}
		if isConstraint[db.IndexKey{Schema: schemaName, Table: tableName, Index: index.Name}] {
			continue
		}

		// Skip system-generated indexes, such as those of LOBs, IOTs, and nested tables.
		if strings.HasPrefix(index.Name, "SYS_") {
			continue
		}

		index.Unique = unique == "UNIQUE"
		indexKey := db.IndexKey{Schema: schemaName, Table: tableName, Index: index.Name}
		// Handle index expressions - preserve Oracle's classification
		columns := indexColumnMap[indexKey]
		expressions := indexExpressionMap[indexKey]

		// For function-based indexes (including those with DESC columns)
		if len(expressions) > 0 {
			// Combine regular columns with expressions
			// Oracle reports ASC columns in ALL_IND_COLUMNS and DESC columns as expressions
			var combinedExpressions []string
			exprIdx := 0

			for i := 0; i < len(columns); i++ {
				if strings.HasPrefix(columns[i], "SYS_NC") {
					// This is a virtual column for a DESC column, use the expression
					if exprIdx < len(expressions) {
						combinedExpressions = append(combinedExpressions, expressions[exprIdx])
						exprIdx++
					}
				} else {
					// Regular column
					combinedExpressions = append(combinedExpressions, fmt.Sprintf(`"%s"`, columns[i]))
				}
			}

			index.Expressions = combinedExpressions
		} else {
			// Simple column index without expressions
			// Skip indexes that reference only system-generated virtual columns
			hasOnlyVirtualColumns := true
			for _, col := range columns {
				if !strings.HasPrefix(col, "SYS_NC") {
					hasOnlyVirtualColumns = false
					break
				}
			}
			if hasOnlyVirtualColumns {
				continue
			}

			// For column-based indexes, quote the column names to match expected format
			quotedColumns := make([]string, len(columns))
			for i, col := range columns {
				quotedColumns[i] = fmt.Sprintf(`"%s"`, col)
			}
			index.Expressions = quotedColumns
		}
		// Note: Keep index.Type as reported by Oracle (don't modify it)

		// Set descending flags for all indexes (both function-based and normal)
		if desc, ok := descendingMap[indexKey]; ok && len(desc) > 0 {
			// Always set descending array if we have column information,
			// even if all columns are ascending (to maintain consistency)
			index.Descending = desc
		}
		index.Visible = true
		if visibility.Valid && visibility.String == "INVISIBLE" {
			index.Visible = false
		}

		// Add index comment if available
		if comment, ok := indexCommentMap[indexKey]; ok {
			index.Comment = comment
		}

		key := db.TableKey{Schema: schemaName, Table: tableName}
		indexMap[key] = append(indexMap[key], index)
	}
	if err := rows.Err(); err != nil {
		return nil, nil, nil, util.FormatErrorWithQuery(err, query)
	}
	if err := rows.Close(); err != nil {
		return nil, nil, nil, errors.Wrapf(err, "failed to close rows")
	}

	return indexMap, checkConstraintMap, foreignKeyMap, nil
}

// getViews gets all views of a database.
func getViews(txn *sql.Tx, schemaName string, columnMap map[db.TableKey][]*metadatapb.ColumnMetadata, triggerMap map[db.TableKey][]*metadatapb.TriggerMetadata) (map[string][]*metadatapb.ViewMetadata, error) {
	viewMap := make(map[string][]*metadatapb.ViewMetadata)

	// Get view comments
	viewCommentMap, err := getViewComments(txn, schemaName)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to get view comments")
	}

	query := fmt.Sprintf(`
		SELECT OWNER, VIEW_NAME, TEXT
		FROM sys.all_views
		WHERE OWNER = '%s'
		ORDER BY view_name
	`, schemaName)

	slog.Debug("running get view query")
	rows, err := txn.Query(query)
	if err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	defer rows.Close()
	for rows.Next() {
		view := &metadatapb.ViewMetadata{}
		var schemaName string
		if err := rows.Scan(&schemaName, &view.Name, &view.Definition); err != nil {
			return nil, err
		}
		view.Definition = sanitizeOracleDefinition(schemaName, "VIEW", view.Name, view.Definition)
		key := db.TableKey{Schema: schemaName, Table: view.Name}
		view.Columns = columnMap[key]
		view.Triggers = triggerMap[key]
		if comment, ok := viewCommentMap[key]; ok {
			view.Comment = comment
		}

		viewMap[schemaName] = append(viewMap[schemaName], view)
	}
	if err := rows.Err(); err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	if err := rows.Close(); err != nil {
		return nil, errors.Wrapf(err, "failed to close rows")
	}

	// Fetch all view dependencies in a single query to avoid N+1 problem
	viewDependenciesMap, err := getAllViewDependencies(txn, schemaName)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to get view dependencies")
	}

	// Populate view dependencies from the map
	for schemaName, list := range viewMap {
		for _, view := range list {
			key := db.TableKey{Schema: schemaName, Table: view.Name}
			if dependencies, ok := viewDependenciesMap[key]; ok {
				view.DependencyColumns = dependencies
			}
		}
	}

	return viewMap, nil
}

// getAllViewDependencies gets all view dependencies for a schema in a single query.
func getAllViewDependencies(txn *sql.Tx, schemaName string) (map[db.TableKey][]*metadatapb.DependencyColumn, error) {
	dependenciesMap := make(map[db.TableKey][]*metadatapb.DependencyColumn)

	query := fmt.Sprintf(`
		SELECT 
			NAME as view_name,
			REFERENCED_OWNER as source_schema,
			REFERENCED_NAME as source_table,
			'*' as column_name
		FROM ALL_DEPENDENCIES
		WHERE OWNER = '%s'
			AND TYPE = 'VIEW'
			AND REFERENCED_TYPE IN ('TABLE', 'VIEW')
		ORDER BY NAME, REFERENCED_OWNER, REFERENCED_NAME
	`, schemaName)

	rows, err := txn.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		var viewName string
		dependencyColumn := &metadatapb.DependencyColumn{}
		if err := rows.Scan(&viewName, &dependencyColumn.Schema, &dependencyColumn.Table, &dependencyColumn.Column); err != nil {
			return nil, err
		}

		key := db.TableKey{Schema: schemaName, Table: viewName}
		dependenciesMap[key] = append(dependenciesMap[key], dependencyColumn)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}

	return dependenciesMap, nil
}

// getAllMaterializedViewDependencies gets all materialized view dependencies for a schema in a single query.
func getAllMaterializedViewDependencies(txn *sql.Tx, schemaName string) (map[db.TableKey][]*metadatapb.DependencyColumn, error) {
	dependenciesMap := make(map[db.TableKey][]*metadatapb.DependencyColumn)

	query := fmt.Sprintf(`
		SELECT 
			NAME as mview_name,
			REFERENCED_OWNER as source_schema,
			REFERENCED_NAME as source_table,
			'*' as column_name
		FROM ALL_DEPENDENCIES
		WHERE OWNER = '%s'
			AND TYPE = 'MATERIALIZED VIEW'
			AND REFERENCED_TYPE IN ('TABLE', 'VIEW')
		ORDER BY NAME, REFERENCED_OWNER, REFERENCED_NAME
	`, schemaName)

	rows, err := txn.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		var mviewName string
		dependencyColumn := &metadatapb.DependencyColumn{}
		if err := rows.Scan(&mviewName, &dependencyColumn.Schema, &dependencyColumn.Table, &dependencyColumn.Column); err != nil {
			return nil, err
		}

		key := db.TableKey{Schema: schemaName, Table: mviewName}
		dependenciesMap[key] = append(dependenciesMap[key], dependencyColumn)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}

	return dependenciesMap, nil
}

// getMaterializedViews gets all materialized views of a database.
func getMaterializedViews(txn *sql.Tx, schemaName string, indexMap map[db.TableKey][]*metadatapb.IndexMetadata, triggerMap map[db.TableKey][]*metadatapb.TriggerMetadata) ([]*metadatapb.MaterializedViewMetadata, error) {
	var materializedViews []*metadatapb.MaterializedViewMetadata

	// Get materialized view comments
	materializedViewCommentMap, err := getMaterializedViewComments(txn, schemaName)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to get materialized view comments")
	}

	query := fmt.Sprintf(`
		SELECT OWNER, MVIEW_NAME, QUERY
		FROM sys.all_mviews
		WHERE OWNER = '%s'
		ORDER BY MVIEW_NAME
	`, schemaName)

	slog.Debug("running get materialized views query")
	rows, err := txn.Query(query)
	if err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	defer rows.Close()

	for rows.Next() {
		materializedView := &metadatapb.MaterializedViewMetadata{}
		var schemaName string
		if err := rows.Scan(&schemaName, &materializedView.Name, &materializedView.Definition); err != nil {
			return nil, err
		}
		materializedView.Definition = sanitizeOracleDefinition(
			schemaName,
			"MATERIALIZED VIEW",
			materializedView.Name,
			materializedView.Definition,
		)

		// Ensure the definition ends with a newline to match expected format
		if materializedView.Definition != "" && !strings.HasSuffix(materializedView.Definition, "\n") {
			materializedView.Definition += "\n"
		}

		// Add comment if available
		key := db.TableKey{Schema: schemaName, Table: materializedView.Name}
		if comment, ok := materializedViewCommentMap[key]; ok {
			materializedView.Comment = comment
		}
		// Indexes and triggers are on the container table, which has the view's name.
		materializedView.Indexes = indexMap[key]
		materializedView.Triggers = triggerMap[key]

		materializedViews = append(materializedViews, materializedView)
	}
	if err := rows.Err(); err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	if err := rows.Close(); err != nil {
		return nil, errors.Wrapf(err, "failed to close rows")
	}

	// Fetch all materialized view dependencies in a single query to avoid N+1 problem
	materializedViewDependenciesMap, err := getAllMaterializedViewDependencies(txn, schemaName)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to get materialized view dependencies")
	}

	// Populate dependencies from the map
	for _, materializedView := range materializedViews {
		key := db.TableKey{Schema: schemaName, Table: materializedView.Name}
		if dependencies, ok := materializedViewDependenciesMap[key]; ok {
			materializedView.DependencyColumns = dependencies
		}
	}

	return materializedViews, nil
}

// getSequences gets all sequences of a database.
func getSequences(txn *sql.Tx, schemaName string) ([]*metadatapb.SequenceMetadata, error) {
	var sequences []*metadatapb.SequenceMetadata

	// Get sequence comments
	sequenceCommentMap, err := getSequenceComments(txn, schemaName)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to get sequence comments")
	}

	query := fmt.Sprintf(`
		SELECT SEQUENCE_NAME FROM ALL_SEQUENCES
		WHERE SEQUENCE_OWNER = '%s'
		ORDER BY SEQUENCE_NAME`, schemaName)

	slog.Debug("running get sequences query")
	rows, err := txn.Query(query)
	if err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	defer rows.Close()

	for rows.Next() {
		seq := &metadatapb.SequenceMetadata{}
		if err := rows.Scan(&seq.Name); err != nil {
			return nil, err
		}
		// Skip system-generated sequences (e.g., ISEQ$$_* for IDENTITY columns)
		if strings.HasPrefix(seq.Name, "ISEQ$$_") {
			continue
		}
		key := db.TableKey{Schema: schemaName, Table: seq.Name}
		if comment, ok := sequenceCommentMap[key]; ok {
			seq.Comment = comment
		}
		sequences = append(sequences, seq)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}

	return sequences, nil
}

func getRoutines(txn *sql.Tx, schemaName string) ([]*metadatapb.FunctionMetadata, []*metadatapb.ProcedureMetadata, []*metadatapb.PackageMetadata, error) {
	var functions []*metadatapb.FunctionMetadata
	var procedures []*metadatapb.ProcedureMetadata
	var packages []*metadatapb.PackageMetadata

	// Get function comments
	functionCommentMap, err := getFunctionComments(txn, schemaName)
	if err != nil {
		return nil, nil, nil, errors.Wrapf(err, "failed to get function comments")
	}

	query := fmt.Sprintf(`
		SELECT
			NAME,
			TYPE,
			TEXT
		FROM ALL_SOURCE
		WHERE
			TYPE IN ('FUNCTION', 'PROCEDURE', 'PACKAGE')
			AND
			OWNER = '%s'
		ORDER BY NAME, TYPE, LINE`, schemaName)

	slog.Debug("running get routines query")
	rows, err := txn.Query(query)
	if err != nil {
		return nil, nil, nil, util.FormatErrorWithQuery(err, query)
	}
	defer rows.Close()

	var currentName, currentType string
	var defText []string
	for rows.Next() {
		var name, t, def string
		if err := rows.Scan(&name, &t, &def); err != nil {
			return nil, nil, nil, err
		}
		if name == currentName && t == currentType {
			defText = append(defText, def)
		} else {
			// Oracle may include a trailing C-string null terminator via the go-ora driver.
			switch currentType {
			case "FUNCTION":
				function := &metadatapb.FunctionMetadata{
					Name:       currentName,
					Definition: sanitizeOracleDefinition(schemaName, "FUNCTION", currentName, strings.Join(defText, "")),
				}
				key := db.TableKey{Schema: schemaName, Table: currentName}
				if comment, ok := functionCommentMap[key]; ok {
					function.Comment = comment
				}
				functions = append(functions, function)
			case "PROCEDURE":
				procedures = append(procedures, &metadatapb.ProcedureMetadata{
					Name:       currentName,
					Definition: sanitizeOracleDefinition(schemaName, "PROCEDURE", currentName, strings.Join(defText, "")),
				})
			case "PACKAGE":
				packages = append(packages, &metadatapb.PackageMetadata{
					Name:       currentName,
					Definition: sanitizeOracleDefinition(schemaName, "PACKAGE", currentName, strings.Join(defText, "")),
				})
			default:
				// Ignore other types
			}
			currentName = name
			currentType = t
			defText = []string{def}
		}
	}
	// Oracle may include a trailing C-string null terminator via the go-ora driver.
	switch currentType {
	case "FUNCTION":
		function := &metadatapb.FunctionMetadata{
			Name:       currentName,
			Definition: sanitizeOracleDefinition(schemaName, "FUNCTION", currentName, strings.Join(defText, "")),
		}
		key := db.TableKey{Schema: schemaName, Table: currentName}
		if comment, ok := functionCommentMap[key]; ok {
			function.Comment = comment
		}
		functions = append(functions, function)
	case "PROCEDURE":
		procedures = append(procedures, &metadatapb.ProcedureMetadata{
			Name:       currentName,
			Definition: sanitizeOracleDefinition(schemaName, "PROCEDURE", currentName, strings.Join(defText, "")),
		})
	case "PACKAGE":
		packages = append(packages, &metadatapb.PackageMetadata{
			Name:       currentName,
			Definition: sanitizeOracleDefinition(schemaName, "PACKAGE", currentName, strings.Join(defText, "")),
		})
	default:
		// Ignore other types
	}
	if err := rows.Err(); err != nil {
		return nil, nil, nil, err
	}
	if err := rows.Close(); err != nil {
		return nil, nil, nil, err
	}

	return functions, procedures, packages, nil
}

func sanitizeOracleDefinition(schemaName, objectType, objectName, definition string) string {
	return sanitizeOracleMetadataString(schemaName, objectType, objectName, "definition", definition)
}

func sanitizeOracleMetadataString(schemaName, objectType, objectName, field, value string) string {
	// Oracle may include a trailing C-string null terminator via the go-ora driver.
	value = strings.TrimRight(value, "\x00")
	if utf8.ValidString(value) {
		return value
	}
	slog.Warn("sanitized invalid UTF-8 in Oracle metadata",
		slog.String("schema", schemaName),
		slog.String("object_type", objectType),
		slog.String("object_name", objectName),
		slog.String("field", field),
	)

	var sanitized strings.Builder
	sanitized.Grow(len(value))
	for i := 0; i < len(value); {
		r, width := utf8.DecodeRuneInString(value[i:])
		if r == utf8.RuneError && width == 1 {
			_, _ = fmt.Fprintf(&sanitized, "\\x%02x", value[i])
			i++
			continue
		}
		_, _ = sanitized.WriteString(value[i : i+width])
		i += width
	}
	return sanitized.String()
}

// cleanDefaultExpression cleans up Oracle default expressions to make them more portable
// and fix common syntax issues like schema-qualified sequence NEXTVAL calls
func cleanDefaultExpression(defaultExpr, currentSchema string) string {
	if defaultExpr == "" {
		return defaultExpr
	}

	// Remove leading/trailing whitespace
	cleaned := strings.TrimSpace(defaultExpr)

	// Fix schema-qualified sequence NEXTVAL calls
	// Pattern: "SCHEMA"."SEQUENCE_NAME"."NEXTVAL" -> "SEQUENCE_NAME".NEXTVAL
	// Keep sequence name quoted for compatibility
	if strings.Contains(cleaned, ".\"NEXTVAL\"") {
		// Use regex to match quoted schema and sequence names followed by quoted NEXTVAL
		re := regexp.MustCompile(`"` + regexp.QuoteMeta(currentSchema) + `"\."([^"]+)"\."NEXTVAL"`)
		cleaned = re.ReplaceAllString(cleaned, `"$1".NEXTVAL`)
	}

	// Also handle unquoted NEXTVAL (less common)
	if strings.Contains(cleaned, ".NEXTVAL") {
		re := regexp.MustCompile(`"` + regexp.QuoteMeta(currentSchema) + `"\."([^"]+)"\.NEXTVAL`)
		cleaned = re.ReplaceAllString(cleaned, `"$1".NEXTVAL`)
	}

	return cleaned
}

// getViewComments gets comments for views from Oracle system views
func getViewComments(txn *sql.Tx, schemaName string) (map[db.TableKey]string, error) {
	viewCommentMap := make(map[db.TableKey]string)

	// Oracle stores view comments in ALL_TAB_COMMENTS where TABLE_TYPE = 'VIEW'
	query := ""
	if schemaName == "" {
		query = fmt.Sprintf(`
		SELECT OWNER, TABLE_NAME, COMMENTS
		FROM all_tab_comments
		WHERE OWNER NOT IN (%s) AND OWNER NOT LIKE 'APEX_%%' AND COMMENTS IS NOT NULL
		AND TABLE_TYPE = 'VIEW'
		ORDER BY OWNER, TABLE_NAME`, systemSchema)
	} else {
		query = fmt.Sprintf(`
		SELECT OWNER, TABLE_NAME, COMMENTS
		FROM all_tab_comments
		WHERE OWNER = '%s' AND COMMENTS IS NOT NULL
		AND TABLE_TYPE = 'VIEW'
		ORDER BY TABLE_NAME`, schemaName)
	}

	slog.Debug("running get view comments query")
	rows, err := txn.Query(query)
	if err != nil {
		// If the query fails (e.g., TABLE_TYPE = 'VIEW' not supported), return empty map
		slog.Debug("view comments query failed, returning empty map", slog.String("error", err.Error()))
		return viewCommentMap, nil
	}
	defer rows.Close()

	for rows.Next() {
		var schemaName, viewName, comment string
		if err := rows.Scan(&schemaName, &viewName, &comment); err != nil {
			return nil, err
		}
		key := db.TableKey{Schema: schemaName, Table: viewName}
		viewCommentMap[key] = common.SanitizeUTF8String(comment)
	}
	if err := rows.Err(); err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	if err := rows.Close(); err != nil {
		return nil, errors.Wrapf(err, "failed to close rows")
	}

	return viewCommentMap, nil
}

// getSequenceComments gets comments for sequences from Oracle system views
func getSequenceComments(txn *sql.Tx, schemaName string) (map[db.TableKey]string, error) {
	sequenceCommentMap := make(map[db.TableKey]string)

	// Oracle doesn't have a direct sequence comments view, but we can try user_tab_comments
	// where TABLE_TYPE might be 'SEQUENCE' in some Oracle versions
	query := ""
	if schemaName == "" {
		query = fmt.Sprintf(`
		SELECT OWNER, TABLE_NAME, COMMENTS
		FROM all_tab_comments
		WHERE OWNER NOT IN (%s) AND OWNER NOT LIKE 'APEX_%%' AND COMMENTS IS NOT NULL
		AND TABLE_TYPE = 'SEQUENCE'
		ORDER BY OWNER, TABLE_NAME`, systemSchema)
	} else {
		query = fmt.Sprintf(`
		SELECT OWNER, TABLE_NAME, COMMENTS
		FROM all_tab_comments
		WHERE OWNER = '%s' AND COMMENTS IS NOT NULL
		AND TABLE_TYPE = 'SEQUENCE'
		ORDER BY TABLE_NAME`, schemaName)
	}

	slog.Debug("running get sequence comments query")
	rows, err := txn.Query(query)
	if err != nil {
		// If the query fails (e.g., TABLE_TYPE = 'SEQUENCE' not supported), return empty map
		slog.Debug("sequence comments query failed, returning empty map", slog.String("error", err.Error()))
		return sequenceCommentMap, nil
	}
	defer rows.Close()

	for rows.Next() {
		var schemaName, sequenceName, comment string
		if err := rows.Scan(&schemaName, &sequenceName, &comment); err != nil {
			return nil, err
		}
		key := db.TableKey{Schema: schemaName, Table: sequenceName}
		sequenceCommentMap[key] = common.SanitizeUTF8String(comment)
	}
	if err := rows.Err(); err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	if err := rows.Close(); err != nil {
		return nil, errors.Wrapf(err, "failed to close rows")
	}

	return sequenceCommentMap, nil
}

// getFunctionComments gets comments for functions and procedures from Oracle system views
func getFunctionComments(txn *sql.Tx, schemaName string) (map[db.TableKey]string, error) {
	functionCommentMap := make(map[db.TableKey]string)

	// Oracle stores function/procedure comments in ALL_OBJECTS or USER_OBJECTS
	// However, Oracle doesn't have a standard system view for function/procedure comments
	// We can try to get them from ALL_TAB_COMMENTS where TABLE_TYPE might be 'FUNCTION' or 'PROCEDURE'
	query := ""
	if schemaName == "" {
		query = fmt.Sprintf(`
		SELECT OWNER, TABLE_NAME, COMMENTS
		FROM all_tab_comments
		WHERE OWNER NOT IN (%s) AND OWNER NOT LIKE 'APEX_%%' AND COMMENTS IS NOT NULL
		AND TABLE_TYPE IN ('FUNCTION', 'PROCEDURE')
		ORDER BY OWNER, TABLE_NAME`, systemSchema)
	} else {
		query = fmt.Sprintf(`
		SELECT OWNER, TABLE_NAME, COMMENTS
		FROM all_tab_comments
		WHERE OWNER = '%s' AND COMMENTS IS NOT NULL
		AND TABLE_TYPE IN ('FUNCTION', 'PROCEDURE')
		ORDER BY TABLE_NAME`, schemaName)
	}

	slog.Debug("running get function comments query")
	rows, err := txn.Query(query)
	if err != nil {
		// If the query fails (e.g., TABLE_TYPE for functions not supported), return empty map
		slog.Debug("function comments query failed, returning empty map", slog.String("error", err.Error()))
		return functionCommentMap, nil
	}
	defer rows.Close()

	for rows.Next() {
		var schemaName, functionName, comment string
		if err := rows.Scan(&schemaName, &functionName, &comment); err != nil {
			return nil, err
		}
		key := db.TableKey{Schema: schemaName, Table: functionName}
		functionCommentMap[key] = common.SanitizeUTF8String(comment)
	}
	if err := rows.Err(); err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	if err := rows.Close(); err != nil {
		return nil, errors.Wrapf(err, "failed to close rows")
	}

	return functionCommentMap, nil
}

// getIndexComments gets comments for indexes from Oracle system views
func getIndexComments(_ *sql.Tx, schemaName string) (map[db.IndexKey]string, error) {
	indexCommentMap := make(map[db.IndexKey]string)

	// Oracle doesn't have a dedicated index comments view in all versions
	// We need to join ALL_INDEXES with ALL_TAB_COMMENTS to get index comments
	// However, Oracle stores index comments differently depending on the version
	// For now, return empty map since Oracle index comments are not consistently available
	slog.Debug("Oracle index comments not implemented - returning empty map", "schema", schemaName)
	return indexCommentMap, nil
}

// getMaterializedViewComments gets comments for materialized views from Oracle system views
func getMaterializedViewComments(txn *sql.Tx, schemaName string) (map[db.TableKey]string, error) {
	materializedViewCommentMap := make(map[db.TableKey]string)

	query := ""
	if schemaName == "" {
		query = fmt.Sprintf(`
		SELECT OWNER, MVIEW_NAME, COMMENTS
		FROM all_mview_comments
		WHERE OWNER NOT IN (%s) AND OWNER NOT LIKE 'APEX_%%' AND COMMENTS IS NOT NULL
		ORDER BY OWNER, MVIEW_NAME`, systemSchema)
	} else {
		query = fmt.Sprintf(`
		SELECT OWNER, MVIEW_NAME, COMMENTS
		FROM all_mview_comments
		WHERE OWNER = '%s' AND COMMENTS IS NOT NULL
		ORDER BY MVIEW_NAME`, schemaName)
	}

	slog.Debug("running get materialized view comments query")
	rows, err := txn.Query(query)
	if err != nil {
		// If the view doesn't exist, return empty map
		slog.Debug("materialized view comments query failed, returning empty map", slog.String("error", err.Error()))
		return materializedViewCommentMap, nil
	}
	defer rows.Close()

	for rows.Next() {
		var schemaName, mvName, comment string
		if err := rows.Scan(&schemaName, &mvName, &comment); err != nil {
			return nil, err
		}
		key := db.TableKey{Schema: schemaName, Table: mvName}
		materializedViewCommentMap[key] = common.SanitizeUTF8String(comment)
	}
	if err := rows.Err(); err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	if err := rows.Close(); err != nil {
		return nil, errors.Wrapf(err, "failed to close rows")
	}

	return materializedViewCommentMap, nil
}

// getTriggerComments gets comments for triggers from Oracle system views
func getTriggerComments(txn *sql.Tx, schemaName string) (map[db.TableKey]string, error) {
	triggerCommentMap := make(map[db.TableKey]string)

	// Oracle doesn't have a standard trigger comments view
	// Trigger comments might be stored in ALL_TAB_COMMENTS with TABLE_TYPE = 'TRIGGER'
	// or in some Oracle versions, they might not be available at all
	query := ""
	if schemaName == "" {
		query = fmt.Sprintf(`
		SELECT OWNER, TABLE_NAME, COMMENTS
		FROM all_tab_comments
		WHERE OWNER NOT IN (%s) AND OWNER NOT LIKE 'APEX_%%' AND COMMENTS IS NOT NULL
		AND TABLE_TYPE = 'TRIGGER'
		ORDER BY OWNER, TABLE_NAME`, systemSchema)
	} else {
		query = fmt.Sprintf(`
		SELECT OWNER, TABLE_NAME, COMMENTS
		FROM all_tab_comments
		WHERE OWNER = '%s' AND COMMENTS IS NOT NULL
		AND TABLE_TYPE = 'TRIGGER'
		ORDER BY TABLE_NAME`, schemaName)
	}

	slog.Debug("running get trigger comments query")
	rows, err := txn.Query(query)
	if err != nil {
		// If the query fails (e.g., TABLE_TYPE = 'TRIGGER' not supported), return empty map
		slog.Debug("trigger comments query failed, returning empty map", slog.String("error", err.Error()))
		return triggerCommentMap, nil
	}
	defer rows.Close()

	for rows.Next() {
		var schemaName, triggerName, comment string
		if err := rows.Scan(&schemaName, &triggerName, &comment); err != nil {
			return nil, err
		}
		key := db.TableKey{Schema: schemaName, Table: triggerName}
		triggerCommentMap[key] = common.SanitizeUTF8String(comment)
	}
	if err := rows.Err(); err != nil {
		return nil, util.FormatErrorWithQuery(err, query)
	}
	if err := rows.Close(); err != nil {
		return nil, errors.Wrapf(err, "failed to close rows")
	}

	return triggerCommentMap, nil
}