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 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
}