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 (
"testing"
metadatapb "github.com/bytebase/omni/metadata"
"github.com/stretchr/testify/require"
"os"
"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"
)
func TestGetTableDefinition(t *testing.T) {
tests := []struct {
name string
table *metadatapb.TableMetadata
want string
}{
{
// A table whose last column has DEFAULT + NOT NULL, followed by a
// table-level FOREIGN KEY with no other indexes or constraints.
// The FK must be separated from the column by a comma (ORA-02253).
name: "foreign key after default not null column without other constraints",
table: &metadatapb.TableMetadata{
Name: "T1",
Columns: []*metadatapb.ColumnMetadata{
{Name: "C1", Type: "NUMBER", Nullable: false},
{Name: "C2", Type: "NUMBER(1)", Default: "0", Nullable: false},
},
ForeignKeys: []*metadatapb.ForeignKeyMetadata{
{
Name: "T1_FK",
Columns: []string{"C1"},
ReferencedTable: "T2",
ReferencedColumns: []string{"C1"},
},
},
},
want: `CREATE TABLE "T1" (
"C1" NUMBER NOT NULL,
"C2" NUMBER(1) DEFAULT 0 NOT NULL,
CONSTRAINT "T1_FK" FOREIGN KEY ("C1") REFERENCES "T2" ("C1")
);
`,
},
{
name: "check constraint after column without other constraints",
table: &metadatapb.TableMetadata{
Name: "T1",
Columns: []*metadatapb.ColumnMetadata{
{Name: "C1", Type: "NUMBER", Nullable: false},
},
CheckConstraints: []*metadatapb.CheckConstraintMetadata{
{Name: "T1_CK", Expression: `"C1" > 0`},
},
},
want: `CREATE TABLE "T1" (
"C1" NUMBER NOT NULL,
CONSTRAINT "T1_CK" CHECK ("C1" > 0)
);
`,
},
{
name: "primary key, check, and foreign key",
table: &metadatapb.TableMetadata{
Name: "T1",
Columns: []*metadatapb.ColumnMetadata{
{Name: "C1", Type: "NUMBER", Nullable: false},
{Name: "C2", Type: "NUMBER(1)", Default: "0", Nullable: false},
},
Indexes: []*metadatapb.IndexMetadata{
{
Name: "T1_PK",
Expressions: []string{"C1"},
Primary: true,
Unique: true,
IsConstraint: true,
},
},
CheckConstraints: []*metadatapb.CheckConstraintMetadata{
{Name: "T1_CK", Expression: `"C2" IN (0, 1)`},
},
ForeignKeys: []*metadatapb.ForeignKeyMetadata{
{
Name: "T1_FK",
Columns: []string{"C1"},
ReferencedTable: "T2",
ReferencedColumns: []string{"C1"},
},
},
},
want: `CREATE TABLE "T1" (
"C1" NUMBER NOT NULL,
"C2" NUMBER(1) DEFAULT 0 NOT NULL,
CONSTRAINT "T1_PK" PRIMARY KEY ("C1"),
CONSTRAINT "T1_CK" CHECK ("C2" IN (0, 1)),
CONSTRAINT "T1_FK" FOREIGN KEY ("C1") REFERENCES "T2" ("C1")
);
`,
},
{
name: "object table has no definition",
table: &metadatapb.TableMetadata{
Name: "OT",
SkipDump: true,
Columns: []*metadatapb.ColumnMetadata{{Name: "X", Type: "NUMBER", Nullable: true}},
},
want: "",
},
}
for _, tt := range tests {
t.Run(tt.name, func(t *testing.T) {
got, err := GetTableDefinition("", tt.table, nil)
require.NoError(t, err)
require.Equal(t, tt.want, got)
})
}
}
// TestGetObjectDefinition covers the single-object definitions GetSchemaString
// serves. The metadata is shaped the way Oracle reports it: view and
// materialized-view definitions are a bare SELECT, and routine definitions
// start at FUNCTION/PROCEDURE with no CREATE of their own.
func TestGetObjectDefinition(t *testing.T) {
tests := []struct {
name string
get func() (string, error)
want string
}{
{
name: "view",
get: func() (string, error) {
return GetViewDefinition("", &metadatapb.ViewMetadata{
Name: "DEPT_EMPLOYEE_COUNT",
Definition: "SELECT D.ID AS DEPT_ID, COUNT(E.ID) AS EMP_COUNT\nFROM DEPARTMENTS D",
})
},
want: `CREATE VIEW "DEPT_EMPLOYEE_COUNT" AS SELECT D.ID AS DEPT_ID, COUNT(E.ID) AS EMP_COUNT
FROM DEPARTMENTS D;
`,
},
{
name: "materialized view",
get: func() (string, error) {
return GetMaterializedViewDefinition("", &metadatapb.MaterializedViewMetadata{
Name: "PRODUCT_STATS",
Definition: "SELECT PRODUCT_ID, COUNT(*) AS ORDER_COUNT FROM ORDERS GROUP BY PRODUCT_ID",
})
},
want: `CREATE MATERIALIZED VIEW "PRODUCT_STATS" AS SELECT PRODUCT_ID, COUNT(*) AS ORDER_COUNT FROM ORDERS GROUP BY PRODUCT_ID;
`,
},
{
// ALL_SOURCE hands back the body starting at FUNCTION, so the
// CREATE OR REPLACE prefix has to be supplied here.
name: "function gains the CREATE OR REPLACE prefix",
get: func() (string, error) {
return GetFunctionDefinition("", &metadatapb.FunctionMetadata{
Name: "CALCULATE_DISCOUNT",
Definition: "FUNCTION CALCULATE_DISCOUNT(AMOUNT NUMBER)\nRETURN NUMBER\nIS\nBEGIN\n RETURN AMOUNT * 0.1;\nEND;",
})
},
want: `CREATE OR REPLACE FUNCTION CALCULATE_DISCOUNT(AMOUNT NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN AMOUNT * 0.1;
END;
`,
},
{
name: "procedure gains the CREATE OR REPLACE prefix",
get: func() (string, error) {
return GetProcedureDefinition("", &metadatapb.ProcedureMetadata{
Name: "LOG_AUDIT",
Definition: "PROCEDURE LOG_AUDIT(P_TABLE_NAME VARCHAR2)\nIS\nBEGIN\n NULL;\nEND;",
})
},
want: `CREATE OR REPLACE PROCEDURE LOG_AUDIT(P_TABLE_NAME VARCHAR2)
IS
BEGIN
NULL;
END;
`,
},
{
// An INSTEAD OF trigger carries the view's DML behavior. Dropping it
// would hand back schema text that recreates the view read-only.
// The body shape is constructTriggerBody's in the Oracle sync: it
// prepends CREATE OR REPLACE TRIGGER to ALL_TRIGGERS.DESCRIPTION.
name: "view keeps its INSTEAD OF trigger",
get: func() (string, error) {
return GetViewDefinition("", &metadatapb.ViewMetadata{
Name: "EMPLOYEE_VIEW",
Definition: "SELECT EMP_ID, EMAIL FROM EMPLOYEES",
Triggers: []*metadatapb.TriggerMetadata{
{
Name: "EMPLOYEE_VIEW_INSERT_TRG",
Body: "CREATE OR REPLACE TRIGGER EMPLOYEE_VIEW_INSERT_TRG\nINSTEAD OF INSERT ON EMPLOYEE_VIEW\nFOR EACH ROW\nBEGIN\n NULL;\nEND;",
},
},
})
},
want: `CREATE VIEW "EMPLOYEE_VIEW" AS SELECT EMP_ID, EMAIL FROM EMPLOYEES;
CREATE OR REPLACE TRIGGER EMPLOYEE_VIEW_INSERT_TRG
INSTEAD OF INSERT ON EMPLOYEE_VIEW
FOR EACH ROW
BEGIN
NULL;
END;
`,
},
}
for _, tt := range tests {
t.Run(tt.name, func(t *testing.T) {
got, err := tt.get()
require.NoError(t, err)
require.Equal(t, tt.want, got)
})
}
}
// TestObjectDefinitionsRegisteredForOracle goes through the schema registry
// rather than calling the functions directly, because the registry lookup is
// what was missing: GetSchemaString served only DATABASE and TABLE for Oracle
// and returned "engine ORACLE is not supported" for the rest, while
// supportGetStringSchema on the frontend lists ORACLE and offers "view schema
// text" on views.
func TestObjectDefinitionsRegisteredForOracle(t *testing.T) {
tests := []struct {
name string
get func() (string, error)
}{
{
name: "view",
get: func() (string, error) {
return schema.GetViewDefinition(storepb.Engine_ORACLE, "", &metadatapb.ViewMetadata{Name: "V", Definition: "SELECT 1 FROM DUAL"})
},
},
{
name: "materialized view",
get: func() (string, error) {
return schema.GetMaterializedViewDefinition(storepb.Engine_ORACLE, "", &metadatapb.MaterializedViewMetadata{Name: "MV", Definition: "SELECT 1 FROM DUAL"})
},
},
{
name: "function",
get: func() (string, error) {
return schema.GetFunctionDefinition(storepb.Engine_ORACLE, "", &metadatapb.FunctionMetadata{Name: "F", Definition: "FUNCTION F RETURN NUMBER IS BEGIN RETURN 1; END;"})
},
},
{
name: "procedure",
get: func() (string, error) {
return schema.GetProcedureDefinition(storepb.Engine_ORACLE, "", &metadatapb.ProcedureMetadata{Name: "P", Definition: "PROCEDURE P IS BEGIN NULL; END;"})
},
},
}
for _, tt := range tests {
t.Run(tt.name, func(t *testing.T) {
got, err := tt.get()
require.NoError(t, err)
require.NotEmpty(t, got)
})
}
}
type getDatabaseDefinitionCase struct {
Description string `yaml:"description"`
Metadata string `yaml:"metadata"`
Expected string `yaml:"expected"`
}
// TestGetDatabaseDefinition pins the DDL generated for each metadata input, and
// re-parses that DDL to prove it is valid Oracle syntax.
//
// The input is metadata rather than a schema text on purpose: deriving it by
// parsing would cap this test's reach at whatever GetDatabaseMetadata happens to
// extract, so any generator branch the parser cannot feed would go untested.
func TestGetDatabaseDefinition(t *testing.T) {
const (
record = false
filepath = "testdata/get_database_definition.yaml"
)
var tests []getDatabaseDefinitionCase
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) {
var metadata metadatapb.DatabaseSchemaMetadata
require.NoError(t, common.ProtojsonUnmarshaler.Unmarshal([]byte(tc.Metadata), &metadata))
definition, err := GetDatabaseDefinition(schema.GetDefinitionContext{}, &metadata)
require.NoError(t, err)
require.NotEmpty(t, definition)
_, err = GetDatabaseMetadata(definition)
require.NoError(t, err, "generated definition should parse")
if record {
tests[i].Expected = definition
return
}
require.Equal(t, tc.Expected, definition)
})
}
if record {
yamltest.Record(t, filepath, tests)
}
}