Skip to content

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

17.0 KB
Raw Blame
package oracle

import (
	"os"
	"slices"
	"strings"
	"testing"

	metadatapb "github.com/bytebase/omni/metadata"
	"github.com/stretchr/testify/assert"
	"github.com/stretchr/testify/require"

	"gopkg.in/yaml.v3"

	"github.com/bytebase/bytebase/backend/common"
	"github.com/bytebase/bytebase/backend/common/yamltest"
	storepb "github.com/bytebase/bytebase/backend/generated-go/store"
	"github.com/bytebase/bytebase/backend/plugin/schema"
	"github.com/bytebase/bytebase/backend/store/model"
)

type generateMigrationCase struct {
	Description string `yaml:"description"`
	OldMetadata string `yaml:"oldMetadata"`
	NewMetadata string `yaml:"newMetadata"`
	Expected    string `yaml:"expected"`
}

// TestGenerateMigration pins the DDL that takes oldMetadata to newMetadata. The
// metadata is what Oracle itself reported for each case, captured once; the
// recorder that produced it is gone, so these inputs are frozen and only the
// expected DDL re-records.
func TestGenerateMigration(t *testing.T) {
	const (
		record   = false
		filepath = "testdata/generate_migration.yaml"
	)

	var tests []generateMigrationCase
	content, err := os.ReadFile(filepath)
	require.NoError(t, err)
	require.NoError(t, yaml.Unmarshal(content, &tests))

	for i, tc := range tests {
		t.Run(tc.Description, func(t *testing.T) {
			diff, err := schema.GetDatabaseSchemaDiff(storepb.Engine_ORACLE,
				parseMetadata(t, tc.OldMetadata), parseMetadata(t, tc.NewMetadata))
			require.NoError(t, err)

			migration, err := schema.GenerateMigration(storepb.Engine_ORACLE, diff)
			require.NoError(t, err)

			if record {
				tests[i].Expected = migration
				return
			}
			require.Equal(t, tc.Expected, migration)
		})
	}

	if record {
		yamltest.Record(t, filepath, tests)
	}
}

func parseMetadata(t *testing.T, encoded string) *model.DatabaseMetadata {
	t.Helper()

	metadata := &metadatapb.DatabaseSchemaMetadata{}
	require.NoError(t, common.ProtojsonUnmarshaler.Unmarshal([]byte(encoded), metadata))
	return model.NewDatabaseMetadata(metadata, nil, nil, storepb.Engine_ORACLE, false)
}

func TestTopologicalOrderCreateObjects(t *testing.T) {
	tests := []struct {
		name        string
		diff        *schema.MetadataDiff
		description string
	}{
		{
			name: "table_depends_on_table_via_foreign_key",
			diff: &schema.MetadataDiff{
				TableChanges: []*schema.TableDiff{
					// Create orders table that depends on customers table via FK
					{
						Action:     schema.MetadataDiffActionCreate,
						SchemaName: "TESTUSER",
						TableName:  "ORDERS",
						NewTable: &metadatapb.TableMetadata{
							Name: "ORDERS",
							Columns: []*metadatapb.ColumnMetadata{
								{Name: "ID", Type: "NUMBER", Nullable: false},
								{Name: "CUSTOMER_ID", Type: "NUMBER", Nullable: false},
							},
							ForeignKeys: []*metadatapb.ForeignKeyMetadata{
								{
									Name:              "FK_ORDERS_CUSTOMER",
									Columns:           []string{"CUSTOMER_ID"},
									ReferencedSchema:  "TESTUSER",
									ReferencedTable:   "CUSTOMERS",
									ReferencedColumns: []string{"ID"},
								},
							},
						},
					},
					// Create customers table (should be created first)
					{
						Action:     schema.MetadataDiffActionCreate,
						SchemaName: "TESTUSER",
						TableName:  "CUSTOMERS",
						NewTable: &metadatapb.TableMetadata{
							Name: "CUSTOMERS",
							Columns: []*metadatapb.ColumnMetadata{
								{Name: "ID", Type: "NUMBER", Nullable: false},
								{Name: "NAME", Type: "VARCHAR2(100)", Nullable: false},
							},
						},
					},
				},
			},
			description: "Customers table should be created before orders table due to FK dependency",
		},
		{
			name: "view_depends_on_tables",
			diff: &schema.MetadataDiff{
				TableChanges: []*schema.TableDiff{
					{
						Action:     schema.MetadataDiffActionCreate,
						SchemaName: "TESTUSER",
						TableName:  "USERS",
						NewTable: &metadatapb.TableMetadata{
							Name: "USERS",
							Columns: []*metadatapb.ColumnMetadata{
								{Name: "ID", Type: "NUMBER", Nullable: false},
								{Name: "NAME", Type: "VARCHAR2(100)", Nullable: false},
							},
						},
					},
					{
						Action:     schema.MetadataDiffActionCreate,
						SchemaName: "TESTUSER",
						TableName:  "ORDERS",
						NewTable: &metadatapb.TableMetadata{
							Name: "ORDERS",
							Columns: []*metadatapb.ColumnMetadata{
								{Name: "ID", Type: "NUMBER", Nullable: false},
								{Name: "USER_ID", Type: "NUMBER", Nullable: false},
							},
						},
					},
				},
				ViewChanges: []*schema.ViewDiff{
					{
						Action:     schema.MetadataDiffActionCreate,
						SchemaName: "TESTUSER",
						ViewName:   "USER_ORDERS",
						NewView: &metadatapb.ViewMetadata{
							Name:       "USER_ORDERS",
							Definition: "SELECT u.NAME, o.ID FROM USERS u JOIN ORDERS o ON u.ID = o.USER_ID",
							DependencyColumns: []*metadatapb.DependencyColumn{
								{Schema: "TESTUSER", Table: "USERS", Column: "ID"},
								{Schema: "TESTUSER", Table: "USERS", Column: "NAME"},
								{Schema: "TESTUSER", Table: "ORDERS", Column: "ID"},
								{Schema: "TESTUSER", Table: "ORDERS", Column: "USER_ID"},
							},
						},
					},
				},
			},
			description: "View should be created after tables it depends on",
		},
		{
			name: "column_addition_follows_table_dependency",
			diff: &schema.MetadataDiff{
				TableChanges: []*schema.TableDiff{
					// Add column to orders table
					{
						Action:     schema.MetadataDiffActionAlter,
						SchemaName: "TESTUSER",
						TableName:  "ORDERS",
						ColumnChanges: []*schema.ColumnDiff{
							{
								Action: schema.MetadataDiffActionCreate,
								NewColumn: &metadatapb.ColumnMetadata{
									Name:     "CUSTOMER_REF",
									Type:     "NUMBER",
									Nullable: true,
								},
							},
						},
					},
					// Add column to customers table (should be added first in dependency order)
					{
						Action:     schema.MetadataDiffActionAlter,
						SchemaName: "TESTUSER",
						TableName:  "CUSTOMERS",
						ColumnChanges: []*schema.ColumnDiff{
							{
								Action: schema.MetadataDiffActionCreate,
								NewColumn: &metadatapb.ColumnMetadata{
									Name:     "STATUS",
									Type:     "VARCHAR2(20)",
									Nullable: true,
								},
							},
						},
					},
				},
			},
			description: "Column additions should follow table topological order",
		},
		{
			name: "materialized_view_depends_on_view",
			diff: &schema.MetadataDiff{
				TableChanges: []*schema.TableDiff{
					{
						Action:     schema.MetadataDiffActionCreate,
						SchemaName: "TESTUSER",
						TableName:  "PRODUCTS",
						NewTable: &metadatapb.TableMetadata{
							Name: "PRODUCTS",
							Columns: []*metadatapb.ColumnMetadata{
								{Name: "ID", Type: "NUMBER", Nullable: false},
								{Name: "NAME", Type: "VARCHAR2(100)", Nullable: false},
								{Name: "PRICE", Type: "NUMBER(10,2)", Nullable: false},
							},
						},
					},
				},
				ViewChanges: []*schema.ViewDiff{
					{
						Action:     schema.MetadataDiffActionCreate,
						SchemaName: "TESTUSER",
						ViewName:   "EXPENSIVE_PRODUCTS",
						NewView: &metadatapb.ViewMetadata{
							Name:       "EXPENSIVE_PRODUCTS",
							Definition: "SELECT * FROM PRODUCTS WHERE PRICE > 100",
							DependencyColumns: []*metadatapb.DependencyColumn{
								{Schema: "TESTUSER", Table: "PRODUCTS", Column: "ID"},
								{Schema: "TESTUSER", Table: "PRODUCTS", Column: "NAME"},
								{Schema: "TESTUSER", Table: "PRODUCTS", Column: "PRICE"},
							},
						},
					},
				},
				MaterializedViewChanges: []*schema.MaterializedViewDiff{
					{
						Action:               schema.MetadataDiffActionCreate,
						SchemaName:           "TESTUSER",
						MaterializedViewName: "EXPENSIVE_PRODUCTS_MV",
						NewMaterializedView: &metadatapb.MaterializedViewMetadata{
							Name:       "EXPENSIVE_PRODUCTS_MV",
							Definition: "SELECT NAME FROM EXPENSIVE_PRODUCTS",
							DependencyColumns: []*metadatapb.DependencyColumn{
								{Schema: "TESTUSER", Table: "EXPENSIVE_PRODUCTS", Column: "NAME"},
							},
						},
					},
				},
			},
			description: "Materialized view should be created after view it depends on",
		},
		{
			name: "function_depends_on_table",
			diff: &schema.MetadataDiff{
				TableChanges: []*schema.TableDiff{
					{
						Action:     schema.MetadataDiffActionCreate,
						SchemaName: "TESTUSER",
						TableName:  "EMPLOYEES",
						NewTable: &metadatapb.TableMetadata{
							Name: "EMPLOYEES",
							Columns: []*metadatapb.ColumnMetadata{
								{Name: "ID", Type: "NUMBER", Nullable: false},
								{Name: "SALARY", Type: "NUMBER(10,2)", Nullable: false},
							},
						},
					},
				},
				FunctionChanges: []*schema.FunctionDiff{
					{
						Action:       schema.MetadataDiffActionCreate,
						SchemaName:   "TESTUSER",
						FunctionName: "GET_AVG_SALARY",
						NewFunction: &metadatapb.FunctionMetadata{
							Name:       "GET_AVG_SALARY",
							Definition: "CREATE OR REPLACE FUNCTION GET_AVG_SALARY RETURN NUMBER IS avg_sal NUMBER; BEGIN SELECT AVG(SALARY) INTO avg_sal FROM EMPLOYEES; RETURN avg_sal; END;",
							DependencyTables: []*metadatapb.DependencyTable{
								{Schema: "TESTUSER", Table: "EMPLOYEES"},
							},
						},
					},
				},
			},
			description: "Function should be created after table it depends on",
		},
	}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			result, err := generateMigration(tt.diff)
			require.NoError(t, err, "Generate migration should not error")

			// Verify the SQL is not empty
			assert.NotEmpty(t, result, "Generated migration should not be empty")

			// Parse the generated SQL to check order
			statements := parseStatements(result)

			// Verify topological order based on test case
			switch tt.name {
			case "table_depends_on_table_via_foreign_key":
				// customers table should be created before orders table
				customersIndex := findStatementIndex(statements, "CREATE TABLE", "CUSTOMERS")
				ordersIndex := findStatementIndex(statements, "CREATE TABLE", "ORDERS")
				assert.True(t, customersIndex < ordersIndex,
					"Customers table should be created before orders table. Got statements: %v", statements)

			case "view_depends_on_tables":
				// Both tables should be created before the view
				usersIndex := findStatementIndex(statements, "CREATE TABLE", "USERS")
				ordersIndex := findStatementIndex(statements, "CREATE TABLE", "ORDERS")
				viewIndex := findStatementIndex(statements, "CREATE VIEW", "USER_ORDERS")
				assert.True(t, usersIndex < viewIndex && ordersIndex < viewIndex,
					"Tables should be created before view. Got statements: %v", statements)

			case "column_addition_follows_table_dependency":
				// Verify column additions are in the topological order
				customersAlterIndex := findStatementIndex(statements, "ALTER TABLE", "CUSTOMERS")
				ordersAlterIndex := findStatementIndex(statements, "ALTER TABLE", "ORDERS")
				// Both should exist
				assert.True(t, customersAlterIndex >= 0, "Customers ALTER should exist")
				assert.True(t, ordersAlterIndex >= 0, "Orders ALTER should exist")

			case "materialized_view_depends_on_view":
				// Table, then view, then materialized view
				tableIndex := findStatementIndex(statements, "CREATE TABLE", "PRODUCTS")
				viewIndex := findStatementIndex(statements, "CREATE VIEW", "EXPENSIVE_PRODUCTS")
				mvIndex := findStatementIndex(statements, "CREATE MATERIALIZED VIEW", "EXPENSIVE_PRODUCTS_MV")
				assert.True(t, tableIndex < viewIndex && viewIndex < mvIndex,
					"Objects should be created in dependency order: table -> view -> materialized view. Got statements: %v", statements)

			case "function_depends_on_table":
				// Table should be created before function
				tableIndex := findStatementIndex(statements, "CREATE TABLE", "EMPLOYEES")
				funcIndex := findStatementIndex(statements, "CREATE OR REPLACE FUNCTION", "GET_AVG_SALARY")
				if funcIndex == -1 {
					// Try alternative function creation pattern
					funcIndex = findStatementIndex(statements, "CREATE FUNCTION", "GET_AVG_SALARY")
				}
				assert.True(t, tableIndex < funcIndex,
					"Table should be created before function. Got statements: %v", statements)
			default:
				// No specific verification for this test case
			}
		})
	}
}

// TestTopologicalOrderWithCycles tests behavior when there are circular dependencies
func TestTopologicalOrderWithCycles(t *testing.T) {
	// Create a diff with circular dependency (should fall back to safe order)
	diff := &schema.MetadataDiff{
		TableChanges: []*schema.TableDiff{
			{
				Action:     schema.MetadataDiffActionCreate,
				SchemaName: "TESTUSER",
				TableName:  "TABLE_A",
				NewTable: &metadatapb.TableMetadata{
					Name: "TABLE_A",
					Columns: []*metadatapb.ColumnMetadata{
						{Name: "ID", Type: "NUMBER", Nullable: false},
						{Name: "B_ID", Type: "NUMBER", Nullable: true},
					},
					ForeignKeys: []*metadatapb.ForeignKeyMetadata{
						{
							Name:              "FK_A_TO_B",
							Columns:           []string{"B_ID"},
							ReferencedSchema:  "TESTUSER",
							ReferencedTable:   "TABLE_B",
							ReferencedColumns: []string{"ID"},
						},
					},
				},
			},
			{
				Action:     schema.MetadataDiffActionCreate,
				SchemaName: "TESTUSER",
				TableName:  "TABLE_B",
				NewTable: &metadatapb.TableMetadata{
					Name: "TABLE_B",
					Columns: []*metadatapb.ColumnMetadata{
						{Name: "ID", Type: "NUMBER", Nullable: false},
						{Name: "A_ID", Type: "NUMBER", Nullable: true},
					},
					ForeignKeys: []*metadatapb.ForeignKeyMetadata{
						{
							Name:              "FK_B_TO_A",
							Columns:           []string{"A_ID"},
							ReferencedSchema:  "TESTUSER",
							ReferencedTable:   "TABLE_A",
							ReferencedColumns: []string{"ID"},
						},
					},
				},
			},
		},
	}

	result, err := generateMigration(diff)
	require.NoError(t, err, "Generate migration should handle cycles gracefully")
	assert.NotEmpty(t, result, "Generated migration should not be empty even with cycles")

	// With cycles, it should fall back to creating tables without foreign keys first
	statements := parseStatements(result)

	// Both CREATE TABLE statements should exist
	tableAIndex := findStatementIndex(statements, "CREATE TABLE", "TABLE_A")
	tableBIndex := findStatementIndex(statements, "CREATE TABLE", "TABLE_B")
	assert.True(t, tableAIndex >= 0, "Table A should be created")
	assert.True(t, tableBIndex >= 0, "Table B should be created")

	// Foreign key constraints should be added later as separate ALTER TABLE statements
	fkCount := 0
	for _, stmt := range statements {
		if strings.Contains(strings.ToUpper(stmt), "ADD CONSTRAINT") && strings.Contains(strings.ToUpper(stmt), "FOREIGN KEY") {
			fkCount++
		}
	}
	assert.True(t, fkCount >= 2, "Both foreign keys should be added separately, found %d FK statements", fkCount)
}

// Helper function to parse SQL statements
func parseStatements(sql string) []string {
	var statements []string
	lines := strings.Split(sql, "\n")

	for _, line := range lines {
		line = strings.TrimSpace(line)
		if line != "" && !strings.HasPrefix(line, "--") {
			statements = append(statements, line)
		}
	}
	return statements
}

// Helper function to find the index of a statement containing specific keywords
func findStatementIndex(statements []string, stmtType, objectName string) int {
	for i, stmt := range statements {
		upperStmt := strings.ToUpper(stmt)
		upperType := strings.ToUpper(stmtType)
		upperName := strings.ToUpper(objectName)

		if strings.Contains(upperStmt, upperType) && strings.Contains(upperStmt, upperName) {
			return i
		}
	}
	return -1
}

func TestExpressionReferencesColumn(t *testing.T) {
	tests := []struct {
		expression string
		column     string
		want       bool
	}{
		{expression: `"S"`, column: "S", want: true},
		{expression: `UPPER("S")`, column: "S", want: true},
		{expression: `S+1`, column: "S", want: true},
		{expression: `s > 0`, column: "S", want: true},
		{expression: `"S2"`, column: "S", want: false},
		{expression: `S2`, column: "S", want: false},
		{expression: `'S'`, column: "S", want: false},
		{expression: `"s"`, column: "S", want: false},
		{expression: `1E5`, column: "E5", want: false},
	}
	for _, tc := range tests {
		require.Equal(t, tc.want, expressionReferencesColumn(tc.expression, tc.column), tc.expression)
	}
}

func TestFindColumnDependentsMatchesKeyColumnsByName(t *testing.T) {
	table := &metadatapb.TableMetadata{
		Indexes: []*metadatapb.IndexMetadata{
			{Name: "LOWER_PK", Expressions: []string{"v"}, Primary: true, Unique: true, IsConstraint: true},
			{Name: "MIXED_UK", Expressions: []string{"A", "Vv"}, Unique: true, IsConstraint: true},
			{Name: "UPPER_UK", Expressions: []string{"V"}, Unique: true, IsConstraint: true},
			{Name: "LOWER_IX", Expressions: []string{`"v"`}},
		},
	}
	require.Equal(t, []string{"LOWER_IX", "LOWER_PK"}, sortedIndexNames(findColumnDependents(table, []string{"v"}, nil)))
	require.Equal(t, []string{"MIXED_UK"}, sortedIndexNames(findColumnDependents(table, []string{"Vv"}, nil)))
	require.Equal(t, []string{"UPPER_UK"}, sortedIndexNames(findColumnDependents(table, []string{"V"}, nil)))
}

func sortedIndexNames(dependents columnDependents) []string {
	var names []string
	for _, index := range dependents.indexes {
		names = append(names, index.Name)
	}
	slices.Sort(names)
	return names
}