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 (
"strings"
metadatapb "github.com/bytebase/omni/metadata"
storepb "github.com/bytebase/bytebase/backend/generated-go/store"
"github.com/bytebase/bytebase/backend/plugin/parser/base"
"github.com/bytebase/bytebase/backend/plugin/schema"
)
// Two of GetSchemaString's object types are deliberately left unregistered.
//
// SCHEMA: an Oracle schema is a user, one per database, so its definition is the
// database definition GetDatabaseDefinition already renders. Only pg registers a
// schema definition; MSSQL, MySQL and TiDB have real schemas and do not.
//
// SEQUENCE: getSequences in backend/plugin/db/oracle/sync.go selects only
// SEQUENCE_NAME, so synced sequences carry no Start, Increment, MaxValue or
// Cycle. Rendering one would emit a bare CREATE SEQUENCE that silently drops the
// real increment and bounds. Registering it has to wait for the sync to read
// those columns.
func init() {
schema.RegisterGetDatabaseDefinition(storepb.Engine_ORACLE, GetDatabaseDefinition)
schema.RegisterGetTableDefinition(storepb.Engine_ORACLE, GetTableDefinition)
schema.RegisterGetViewDefinition(storepb.Engine_ORACLE, GetViewDefinition)
schema.RegisterGetMaterializedViewDefinition(storepb.Engine_ORACLE, GetMaterializedViewDefinition)
schema.RegisterGetFunctionDefinition(storepb.Engine_ORACLE, GetFunctionDefinition)
schema.RegisterGetProcedureDefinition(storepb.Engine_ORACLE, GetProcedureDefinition)
}
func GetDatabaseDefinition(_ schema.GetDefinitionContext, to *metadatapb.DatabaseSchemaMetadata) (string, error) {
if len(to.Schemas) == 0 {
return "", nil
}
var buf strings.Builder
schema := to.Schemas[0]
// For Oracle, the database name (user/schema name) is stored in to.Name
databaseName := to.Name
// First generate sequences (they need to exist before tables that reference them)
for _, sequence := range schema.Sequences {
// Skip system-generated sequences
if !strings.HasPrefix(sequence.Name, "ISEQ$$_") {
if err := writeSequence(&buf, sequence); err != nil {
return "", err
}
}
}
// Build dependency graph for topological sorting
graph := base.NewGraph()
tableMap := make(map[string]*metadatapb.TableMetadata)
viewMap := make(map[string]*metadatapb.ViewMetadata)
materializedViewMap := make(map[string]*metadatapb.MaterializedViewMetadata)
functionMap := make(map[string]*metadatapb.FunctionMetadata)
procedureMap := make(map[string]*metadatapb.ProcedureMetadata)
// SkipDump marks object and XMLType tables: CREATE TABLE ... OF needs the
// object type, which the metadata does not carry.
var tables []*metadatapb.TableMetadata
for _, table := range schema.Tables {
if !table.SkipDump {
tables = append(tables, table)
}
}
// Add tables to graph
for _, table := range tables {
tableID := getObjectID(databaseName, table.Name)
tableMap[tableID] = table
graph.AddNode(tableID)
}
// Add views to graph
for _, view := range schema.Views {
viewID := getObjectID(databaseName, view.Name)
viewMap[viewID] = view
graph.AddNode(viewID)
// Add dependencies from view to tables/views it references
for _, dependency := range view.DependencyColumns {
dependencyID := getObjectID(dependency.Schema, dependency.Table)
graph.AddEdge(dependencyID, viewID)
}
}
// Add materialized views to graph
for _, view := range schema.MaterializedViews {
viewID := getObjectID(databaseName, view.Name)
materializedViewMap[viewID] = view
graph.AddNode(viewID)
// Add dependencies from materialized view to tables/views it references
for _, dependency := range view.DependencyColumns {
dependencyID := getObjectID(dependency.Schema, dependency.Table)
graph.AddEdge(dependencyID, viewID)
}
}
// Add functions to graph
for _, function := range schema.Functions {
functionID := getObjectID(databaseName, function.Name)
functionMap[functionID] = function
graph.AddNode(functionID)
// Add dependencies from function to tables it references
for _, dependency := range function.DependencyTables {
dependencyID := getObjectID(dependency.Schema, dependency.Table)
graph.AddEdge(dependencyID, functionID)
}
}
// Add procedures to graph
for _, procedure := range schema.Procedures {
procedureID := getObjectID(databaseName, procedure.Name)
procedureMap[procedureID] = procedure
graph.AddNode(procedureID)
// Note: ProcedureMetadata doesn't have DependencyTables field
// Procedures will be created after tables by default order
}
// Add foreign key dependencies between tables
for _, table := range tables {
tableID := getObjectID(databaseName, table.Name)
for _, fk := range table.ForeignKeys {
if fk.ReferencedTable != table.Name { // Avoid self-references
referencedTableID := getObjectID(databaseName, fk.ReferencedTable)
graph.AddEdge(referencedTableID, tableID)
}
}
}
// Perform topological sort
orderedList, err := graph.TopologicalSort()
if err != nil {
// If there are cycles, fall back to original order
for _, table := range tables {
if err := writeTable(&buf, table); err != nil {
return "", err
}
}
for _, view := range schema.Views {
if err := writeView(&buf, view); err != nil {
return "", err
}
}
for _, view := range schema.MaterializedViews {
if err := writeMaterializedView(&buf, view); err != nil {
return "", err
}
}
for _, function := range schema.Functions {
if err := writeFunction(&buf, function); err != nil {
return "", err
}
}
for _, procedure := range schema.Procedures {
if err := writeProcedure(&buf, procedure); err != nil {
return "", err
}
}
return buf.String(), nil
}
// Generate objects in dependency order
for _, objectID := range orderedList {
if table, ok := tableMap[objectID]; ok {
if err := writeTable(&buf, table); err != nil {
return "", err
}
}
if view, ok := viewMap[objectID]; ok {
if err := writeView(&buf, view); err != nil {
return "", err
}
}
if view, ok := materializedViewMap[objectID]; ok {
if err := writeMaterializedView(&buf, view); err != nil {
return "", err
}
}
if function, ok := functionMap[objectID]; ok {
if err := writeFunction(&buf, function); err != nil {
return "", err
}
}
if procedure, ok := procedureMap[objectID]; ok {
if err := writeProcedure(&buf, procedure); err != nil {
return "", err
}
}
}
return buf.String(), nil
}
func GetTableDefinition(_ string, table *metadatapb.TableMetadata, _ []*metadatapb.SequenceMetadata) (string, error) {
if table.SkipDump {
return "", nil
}
var buf strings.Builder
if err := writeTable(&buf, table); err != nil {
return "", err
}
return buf.String(), nil
}
// The single-object definitions below all ignore the schema argument: Oracle has
// one schema per database and the writers emit unqualified names, matching
// GetTableDefinition and the whole-database output.
func GetViewDefinition(_ string, view *metadatapb.ViewMetadata) (string, error) {
var buf strings.Builder
if err := writeView(&buf, view); err != nil {
return "", err
}
return buf.String(), nil
}
func GetMaterializedViewDefinition(_ string, view *metadatapb.MaterializedViewMetadata) (string, error) {
var buf strings.Builder
if err := writeMaterializedView(&buf, view); err != nil {
return "", err
}
return buf.String(), nil
}
func GetFunctionDefinition(_ string, function *metadatapb.FunctionMetadata) (string, error) {
var buf strings.Builder
if err := writeFunction(&buf, function); err != nil {
return "", err
}
return buf.String(), nil
}
func GetProcedureDefinition(_ string, procedure *metadatapb.ProcedureMetadata) (string, error) {
var buf strings.Builder
if err := writeProcedure(&buf, procedure); err != nil {
return "", err
}
return buf.String(), nil
}
func writeTable(buf *strings.Builder, table *metadatapb.TableMetadata) error {
if _, err := buf.WriteString(`CREATE TABLE "`); err != nil {
return err
}
if _, err := buf.WriteString(table.Name); err != nil {
return err
}
if _, err := buf.WriteString("\" (\n"); err != nil {
return err
}
itemCount := 0
writeSeparator := func() error {
if itemCount > 0 {
if _, err := buf.WriteString(",\n"); err != nil {
return err
}
}
itemCount++
_, err := buf.WriteString(` `)
return err
}
for _, column := range table.Columns {
if err := writeSeparator(); err != nil {
return err
}
if err := writeColumn(buf, column); err != nil {
return err
}
}
for _, constraint := range table.Indexes {
if !constraint.IsConstraint {
continue
}
if err := writeSeparator(); err != nil {
return err
}
if err := writeConstraint(buf, constraint); err != nil {
return err
}
}
for _, check := range table.CheckConstraints {
if err := writeSeparator(); err != nil {
return err
}
if err := writeCheckConstraint(buf, check); err != nil {
return err
}
}
for _, fk := range table.ForeignKeys {
if err := writeSeparator(); err != nil {
return err
}
if err := writeForeignKey(buf, fk); err != nil {
return err
}
}
if _, err := buf.WriteString("\n);\n\n"); err != nil {
return err
}
for _, index := range table.Indexes {
if index.IsConstraint {
continue
}
if err := writeIndex(buf, table.Name, index); err != nil {
return err
}
}
return writeTriggers(buf, table.Triggers)
}
func writeIndex(buf *strings.Builder, table string, index *metadatapb.IndexMetadata) error {
if _, err := buf.WriteString(`CREATE`); err != nil {
return err
}
switch index.Type {
case "BITMAP", "FUNCTION-BASED BITMAP":
if _, err := buf.WriteString(` BITMAP`); err != nil {
return err
}
default:
// Other index types don't need special syntax
}
if index.Unique {
if _, err := buf.WriteString(` UNIQUE`); err != nil {
return err
}
}
if _, err := buf.WriteString(` INDEX "`); err != nil {
return err
}
if _, err := buf.WriteString(index.Name); err != nil {
return err
}
if _, err := buf.WriteString(`" ON "`); err != nil {
return err
}
if _, err := buf.WriteString(table); err != nil {
return err
}
if _, err := buf.WriteString(`" (`); err != nil {
return err
}
if strings.Contains(index.Type, "FUNCTION-BASED") {
for i, expression := range index.Expressions {
if i != 0 {
if _, err := buf.WriteString(", "); err != nil {
return err
}
}
if _, err := buf.WriteString(expression); err != nil {
return err
}
if i < len(index.Descending) && index.Descending[i] {
if _, err := buf.WriteString(` DESC`); err != nil {
return err
}
} else {
if _, err := buf.WriteString(` ASC`); err != nil {
return err
}
}
}
} else {
for i, column := range index.Expressions {
if i != 0 {
if _, err := buf.WriteString(", "); err != nil {
return err
}
}
// Check if column is already quoted to avoid double quoting
if strings.HasPrefix(column, `"`) && strings.HasSuffix(column, `"`) {
// Column is already quoted, use as-is
if _, err := buf.WriteString(column); err != nil {
return err
}
} else {
// Column is not quoted, add quotes
if _, err := buf.WriteString(`"`); err != nil {
return err
}
if _, err := buf.WriteString(column); err != nil {
return err
}
if _, err := buf.WriteString(`"`); err != nil {
return err
}
}
if i < len(index.Descending) && index.Descending[i] {
if _, err := buf.WriteString(` DESC`); err != nil {
return err
}
} else {
if _, err := buf.WriteString(` ASC`); err != nil {
return err
}
}
}
}
if _, err := buf.WriteString(`);`); err != nil {
return err
}
if _, err := buf.WriteString("\n\n"); err != nil {
return err
}
return nil
}
func writeForeignKey(buf *strings.Builder, fk *metadatapb.ForeignKeyMetadata) error {
if _, err := buf.WriteString(`CONSTRAINT "`); err != nil {
return err
}
if _, err := buf.WriteString(fk.Name); err != nil {
return err
}
if _, err := buf.WriteString(`"`); err != nil {
return err
}
if _, err := buf.WriteString(` FOREIGN KEY (`); err != nil {
return err
}
for i, column := range fk.Columns {
if i != 0 {
if _, err := buf.WriteString(", "); err != nil {
return err
}
}
if _, err := buf.WriteString(`"`); err != nil {
return err
}
if _, err := buf.WriteString(column); err != nil {
return err
}
if _, err := buf.WriteString(`"`); err != nil {
return err
}
}
if _, err := buf.WriteString(`) REFERENCES "`); err != nil {
return err
}
// Only include schema prefix for cross-schema foreign key references
// Same-schema references don't need prefix for portability
if fk.ReferencedSchema != "" {
if _, err := buf.WriteString(fk.ReferencedSchema); err != nil {
return err
}
if _, err := buf.WriteString(`"."`); err != nil {
return err
}
}
if _, err := buf.WriteString(fk.ReferencedTable); err != nil {
return err
}
if _, err := buf.WriteString(`" (`); err != nil {
return err
}
for i, column := range fk.ReferencedColumns {
if i != 0 {
if _, err := buf.WriteString(", "); err != nil {
return err
}
}
if _, err := buf.WriteString(`"`); err != nil {
return err
}
if _, err := buf.WriteString(column); err != nil {
return err
}
if _, err := buf.WriteString(`"`); err != nil {
return err
}
}
_, err := buf.WriteString(`)`)
return err
}
func writeCheckConstraint(buf *strings.Builder, check *metadatapb.CheckConstraintMetadata) error {
if _, err := buf.WriteString(`CONSTRAINT "`); err != nil {
return err
}
if _, err := buf.WriteString(check.Name); err != nil {
return err
}
if _, err := buf.WriteString(`"`); err != nil {
return err
}
if _, err := buf.WriteString(` CHECK (`); err != nil {
return err
}
if _, err := buf.WriteString(check.Expression); err != nil {
return err
}
_, err := buf.WriteString(`)`)
return err
}
func writeConstraint(buf *strings.Builder, constraint *metadatapb.IndexMetadata) error {
if _, err := buf.WriteString(`CONSTRAINT "`); err != nil {
return err
}
if _, err := buf.WriteString(constraint.Name); err != nil {
return err
}
if _, err := buf.WriteString(`"`); err != nil {
return err
}
switch {
case constraint.Primary:
if _, err := buf.WriteString(` PRIMARY KEY (`); err != nil {
return err
}
for i, column := range constraint.Expressions {
if i != 0 {
if _, err := buf.WriteString(", "); err != nil {
return err
}
}
if _, err := buf.WriteString(`"`); err != nil {
return err
}
if _, err := buf.WriteString(column); err != nil {
return err
}
if _, err := buf.WriteString(`"`); err != nil {
return err
}
}
if _, err := buf.WriteString(`)`); err != nil {
return err
}
case !constraint.Primary && constraint.Unique:
if _, err := buf.WriteString(` UNIQUE (`); err != nil {
return err
}
for i, column := range constraint.Expressions {
if i != 0 {
if _, err := buf.WriteString(", "); err != nil {
return err
}
}
if _, err := buf.WriteString(`"`); err != nil {
return err
}
if _, err := buf.WriteString(column); err != nil {
return err
}
if _, err := buf.WriteString(`"`); err != nil {
return err
}
}
if _, err := buf.WriteString(`)`); err != nil {
return err
}
default:
// Other constraint types (e.g. CHECK, FOREIGN KEY)
}
return nil
}
func writeColumn(buf *strings.Builder, column *metadatapb.ColumnMetadata) error {
if _, err := buf.WriteString(`"`); err != nil {
return err
}
if _, err := buf.WriteString(column.Name); err != nil {
return err
}
if _, err := buf.WriteString(`" `); err != nil {
return err
}
if _, err := buf.WriteString(column.Type); err != nil {
return err
}
if column.IsInvisible {
if _, err := buf.WriteString(` INVISIBLE`); err != nil {
return err
}
}
if column.Generation != nil {
if _, err := buf.WriteString(virtualColumnClause(column.Generation)); err != nil {
return err
}
if !column.Nullable {
if _, err := buf.WriteString(` NOT NULL`); err != nil {
return err
}
}
return nil
}
// Skip collation for Oracle as it causes issues with standard string size settings
// if column.Collation != "" {
// if _, err := buf.WriteString(` COLLATE "`); err != nil {
// return err
// }
// if _, err := buf.WriteString(column.Collation); err != nil {
// return err
// }
// if _, err := buf.WriteString(`"`); err != nil {
// return err
// }
// }
// Handle default values
hasDefault := column.Default != ""
if hasDefault {
defaultExpr := column.Default
// Skip system-generated sequence references as they can't be manually created
if defaultExpr != "" && !strings.Contains(defaultExpr, "ISEQ$$_") {
if _, err := buf.WriteString(` DEFAULT `); err != nil {
return err
}
if column.DefaultOnNull {
if _, err := buf.WriteString(`ON NULL `); err != nil {
return err
}
}
if _, err := buf.WriteString(defaultExpr); err != nil {
return err
}
}
}
if !column.Nullable {
if _, err := buf.WriteString(` NOT NULL`); err != nil {
return err
}
}
return nil
}
func writeSequence(buf *strings.Builder, sequence *metadatapb.SequenceMetadata) error {
if _, err := buf.WriteString("CREATE SEQUENCE \""); err != nil {
return err
}
if _, err := buf.WriteString(sequence.Name); err != nil {
return err
}
if _, err := buf.WriteString("\""); err != nil {
return err
}
if sequence.Start != "" && sequence.Start != "0" {
if _, err := buf.WriteString(" START WITH "); err != nil {
return err
}
if _, err := buf.WriteString(sequence.Start); err != nil {
return err
}
}
if sequence.Increment != "" && sequence.Increment != "0" && sequence.Increment != "1" {
if _, err := buf.WriteString(" INCREMENT BY "); err != nil {
return err
}
if _, err := buf.WriteString(sequence.Increment); err != nil {
return err
}
}
if sequence.MaxValue != "" && sequence.MaxValue != "0" {
if _, err := buf.WriteString(" MAXVALUE "); err != nil {
return err
}
if _, err := buf.WriteString(sequence.MaxValue); err != nil {
return err
}
}
if sequence.Cycle {
if _, err := buf.WriteString(" CYCLE"); err != nil {
return err
}
}
if _, err := buf.WriteString(";\n\n"); err != nil {
return err
}
return nil
}
// getObjectID returns a unique identifier for database objects
func getObjectID(schema, objectName string) string {
if schema == "" {
return objectName
}
return schema + "." + objectName
}
// writeView writes a CREATE VIEW statement
func writeView(buf *strings.Builder, view *metadatapb.ViewMetadata) error {
if _, err := buf.WriteString(`CREATE VIEW "`); err != nil {
return err
}
if _, err := buf.WriteString(view.Name); err != nil {
return err
}
// The synced column names are deliberately not written back as an explicit
// list. They would restore the aliases of CREATE VIEW V (RENAMED) AS SELECT
// BASE ..., which ALL_VIEWS.TEXT does not carry, but getTableColumns drops
// invisible columns (COLUMN_ID IS NULL, sync.go:485) while the query keeps
// their expressions -- so a view with an invisible column would get fewer
// names than output columns and fail with ORA-01730. Losing an alias beats
// emitting DDL that cannot run.
if _, err := buf.WriteString(`" AS `); err != nil {
return err
}
if _, err := buf.WriteString(view.Definition); err != nil {
return err
}
if !strings.HasSuffix(strings.TrimSpace(view.Definition), ";") {
if _, err := buf.WriteString(`;`); err != nil {
return err
}
}
if _, err := buf.WriteString("\n\n"); err != nil {
return err
}
// An INSTEAD OF trigger carries the view's DML behavior, so it belongs with
// the view the way writeTable emits a table's triggers.
return writeTriggers(buf, view.Triggers)
}
// virtualColumnClause writes the clause that follows a virtual column's type
// and visibility.
func virtualColumnClause(generation *metadatapb.GenerationMetadata) string {
return " GENERATED ALWAYS AS (" + generation.Expression + ") VIRTUAL"
}
// writeMaterializedView writes a CREATE MATERIALIZED VIEW statement
func writeMaterializedView(buf *strings.Builder, view *metadatapb.MaterializedViewMetadata) error {
if _, err := buf.WriteString(`CREATE MATERIALIZED VIEW "`); err != nil {
return err
}
if _, err := buf.WriteString(view.Name); err != nil {
return err
}
if _, err := buf.WriteString(`" AS `); err != nil {
return err
}
if _, err := buf.WriteString(view.Definition); err != nil {
return err
}
if !strings.HasSuffix(strings.TrimSpace(view.Definition), ";") {
if _, err := buf.WriteString(`;`); err != nil {
return err
}
}
if _, err := buf.WriteString("\n\n"); err != nil {
return err
}
return writeTriggers(buf, view.Triggers)
}
// writeFunction writes a CREATE FUNCTION statement
func writeFunction(buf *strings.Builder, function *metadatapb.FunctionMetadata) error {
definition := function.Definition
// If the definition doesn't start with CREATE, add the CREATE OR REPLACE prefix
if !strings.HasPrefix(strings.ToUpper(strings.TrimSpace(definition)), "CREATE") {
if _, err := buf.WriteString("CREATE OR REPLACE "); err != nil {
return err
}
}
if _, err := buf.WriteString(definition); err != nil {
return err
}
if !strings.HasSuffix(strings.TrimSpace(definition), ";") {
if _, err := buf.WriteString(";"); err != nil {
return err
}
}
if _, err := buf.WriteString("\n\n"); err != nil {
return err
}
return nil
}
// writeProcedure writes a CREATE PROCEDURE statement
func writeProcedure(buf *strings.Builder, procedure *metadatapb.ProcedureMetadata) error {
definition := procedure.Definition
// If the definition doesn't start with CREATE, add the CREATE OR REPLACE prefix
if !strings.HasPrefix(strings.ToUpper(strings.TrimSpace(definition)), "CREATE") {
if _, err := buf.WriteString("CREATE OR REPLACE "); err != nil {
return err
}
}
if _, err := buf.WriteString(definition); err != nil {
return err
}
if !strings.HasSuffix(strings.TrimSpace(definition), ";") {
if _, err := buf.WriteString(";"); err != nil {
return err
}
}
if _, err := buf.WriteString("\n\n"); err != nil {
return err
}
return nil
}
// writeTriggers writes the CREATE TRIGGER statements owned by a table or view.
func writeTriggers(buf *strings.Builder, triggers []*metadatapb.TriggerMetadata) error {
for _, trigger := range triggers {
if err := writeTrigger(buf, trigger); err != nil {
return err
}
}
return nil
}
// writeTrigger writes a CREATE TRIGGER statement
func writeTrigger(buf *strings.Builder, trigger *metadatapb.TriggerMetadata) error {
// The trigger body should already contain the full CREATE TRIGGER statement
if _, err := buf.WriteString(trigger.Body); err != nil {
return err
}
if !strings.HasSuffix(strings.TrimSpace(trigger.Body), ";") {
if _, err := buf.WriteString(";"); err != nil {
return err
}
}
if _, err := buf.WriteString("\n\n"); err != nil {
return err
}
return nil
}