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 plsql
import (
"context"
"fmt"
"reflect"
"slices"
"strings"
oracleast "github.com/bytebase/omni/oracle/ast"
"github.com/pkg/errors"
"github.com/bytebase/bytebase/backend/common"
"github.com/bytebase/bytebase/backend/generated-go/store"
"github.com/bytebase/bytebase/backend/plugin/parser/base"
)
const (
maxTableNameLengthAfter12_2 = 128
maxTableNameLengthBefore12_2 = 30
)
var errNoBackupableDML = errors.New("no parse results")
func init() {
base.RegisterTransformDMLToSelect(store.Engine_ORACLE, TransformDMLToSelect)
}
type StatementType int
const (
StatementTypeUnknown StatementType = iota
StatementTypeUpdate
StatementTypeInsert
StatementTypeDelete
)
type TableReference struct {
Database string
HasSchema bool
Schema string
Table string
Alias string
DBLink string
StatementType StatementType
}
type statementInfo struct {
offset int
statement string
node oracleast.StmtNode
table *TableReference
startPosition *store.Position
endPosition *store.Position
fullSQL string
}
// TransformDMLToSelect transforms DML statement to SELECT statement.
// For Oracle, we only consider the managed on schema mode.
func TransformDMLToSelect(_ context.Context, tCtx base.TransformContext, statement string, sourceDatabase string, targetDatabase string, tablePrefix string) ([]base.BackupStatement, error) {
statementInfoList, err := prepareTransformation(sourceDatabase, statement)
if err != nil {
return nil, errors.Wrap(err, "failed to prepare transformation")
}
return generateSQL(tCtx, statementInfoList, targetDatabase, tablePrefix)
}
func generateSQL(ctx base.TransformContext, statementInfoList []statementInfo, targetDatabase string, tablePrefix string) ([]base.BackupStatement, error) {
groupByTable := make(map[string][]statementInfo)
for _, item := range statementInfoList {
key := fmt.Sprintf("%s.%s@%s", item.table.Schema, item.table.Table, item.table.DBLink)
groupByTable[key] = append(groupByTable[key], item)
}
// Check if the statement type is the same for all statements in the group.
for key, list := range groupByTable {
statementType := StatementTypeUnknown
for _, item := range list {
if statementType == StatementTypeUnknown {
statementType = item.table.StatementType
}
if statementType != item.table.StatementType {
return nil, errors.Errorf("prior backup cannot handle statements with different types on the same table: %s", key)
}
}
}
var result []base.BackupStatement
for key, list := range groupByTable {
backupStatement, err := generateSQLForTable(ctx, list, targetDatabase, tablePrefix)
if err != nil {
return nil, errors.Wrapf(err, "failed to generate SQL for table: %s", key)
}
result = append(result, *backupStatement)
}
slices.SortFunc(result, func(i, j base.BackupStatement) int {
if i.StartPosition.Line != j.StartPosition.Line {
if i.StartPosition.Line < j.StartPosition.Line {
return -1
}
return 1
}
if i.StartPosition.Column != j.StartPosition.Column {
if i.StartPosition.Column < j.StartPosition.Column {
return -1
}
return 1
}
if i.SourceTableName < j.SourceTableName {
return -1
}
if i.SourceTableName > j.SourceTableName {
return 1
}
return 0
})
return result, nil
}
func generateSQLForTable(ctx base.TransformContext, statementInfoList []statementInfo, targetDatabase string, tablePrefix string) (*base.BackupStatement, error) {
table := statementInfoList[0].table
// Prior backup cannot round-trip a table referenced through a database
// link: the backup CTAS could read the remote rows, but the restore path
// only records schema+table and would write the rows back into a LOCAL
// table of the same name — silent cross-database corruption on rollback.
// That was already true before the ROWID rework (the link was dropped
// from the reconstructed source), so fail loudly instead of producing a
// backup that cannot be restored correctly.
if table.DBLink != "" {
return nil, errors.Errorf("prior backup does not support tables referenced through a database link: %s.%s@%s; disable prior backup for this issue", table.Schema, table.Table, table.DBLink)
}
version, ok := ctx.Version.(*Version)
if !ok {
version = &Version{
First: 11,
Second: 0,
}
}
targetTable := fmt.Sprintf("%s_%s_%s", tablePrefix, table.Table, table.Schema)
if version.GTE(&Version{First: 12, Second: 2}) {
targetTable, _ = common.TruncateString(targetTable, maxTableNameLengthAfter12_2)
} else {
targetTable, _ = common.TruncateString(targetTable, maxTableNameLengthBefore12_2)
}
var buf strings.Builder
if _, err := fmt.Fprintf(&buf, `CREATE TABLE "%s"."%s" AS`+"\n", targetDatabase, targetTable); err != nil {
return nil, errors.Wrap(err, "failed to write to buffer")
}
if len(statementInfoList) == 1 {
// Single DML: a plain CTAS needs no deduplication.
if err := writeBranchSelect(&buf, statementInfoList[0], "*", " "); err != nil {
return nil, err
}
} else {
// Multiple DMLs on the same table: deduplicate by ROWID instead of a
// value-based UNION. UNION's implicit DISTINCT compares every column,
// which fails on LOB columns (ORA-22848) and silently collapses
// value-identical physical rows into one backup row. ROWID is always
// comparable, keeps LOB columns out of the comparison key, and
// deduplicates by physical row — the semantics a backup wants.
if _, err := fmt.Fprintf(&buf, ` SELECT "%s"."%s".* FROM "%s"."%s" WHERE ROWID IN (`+"\n",
table.Schema, table.Table, table.Schema, table.Table); err != nil {
return nil, errors.Wrap(err, "failed to write to buffer")
}
for i, info := range statementInfoList {
if i != 0 {
if _, err := buf.WriteString("\n UNION\n"); err != nil {
return nil, errors.Wrap(err, "failed to write to buffer")
}
}
if err := writeBranchSelect(&buf, info, "ROWID", " "); err != nil {
return nil, err
}
}
if _, err := buf.WriteString("\n )"); err != nil {
return nil, errors.Wrap(err, "failed to write to buffer")
}
}
if _, err := buf.WriteString(";"); err != nil {
return nil, errors.Wrap(err, "failed to write to buffer")
}
return &base.BackupStatement{
Statement: buf.String(),
SourceSchema: table.Schema,
SourceTableName: table.Table,
TargetTableName: targetTable,
StartPosition: zeroBasedColumnPosition(statementInfoList[0].startPosition),
EndPosition: zeroBasedColumnPosition(statementInfoList[len(statementInfoList)-1].endPosition),
}, nil
}
func prepareTransformation(databaseName, statement string) ([]statementInfo, error) {
statements, err := SplitSQL(statement)
if err != nil {
return nil, errors.Wrap(err, "failed to split PLSQL")
}
var result []statementInfo
positionMapper := base.NewByteOffsetPositionMapper(statement)
for i, stmt := range statements {
if stmt.Empty {
continue
}
list, err := ParsePLSQL(stmt.Text)
if err != nil {
return nil, errors.Wrap(err, "failed to parse PLSQL")
}
for _, item := range list.Items {
raw, ok := item.(*oracleast.RawStmt)
if !ok || raw.Stmt == nil {
continue
}
info := extractOmniDML(databaseName, raw.Stmt, stmt.Text)
if info == nil {
continue
}
info.offset = i
if stmt.Range != nil {
info.startPosition = positionMapper.Position(int(stmt.Range.Start) + raw.Loc.Start)
info.endPosition = positionMapper.Position(int(stmt.Range.Start) + max(raw.Loc.End-1, raw.Loc.Start))
} else {
info.startPosition = stmt.Start
info.endPosition = stmt.End
}
info.fullSQL = stmt.Text
result = append(result, *info)
}
}
if len(result) == 0 {
return nil, errNoBackupableDML
}
return result, nil
}
func extractOmniDML(databaseName string, node oracleast.StmtNode, fullSQL string) *statementInfo {
switch n := node.(type) {
case *oracleast.DeleteStmt:
table := omniDMLTableReference(databaseName, n.Table, n.Alias, StatementTypeDelete)
if table == nil {
return nil
}
return &statementInfo{
statement: extractOmniStatementText(fullSQL, n.Loc),
node: n,
table: table,
}
case *oracleast.UpdateStmt:
table := omniDMLTableReference(databaseName, n.Table, n.Alias, StatementTypeUpdate)
if table == nil {
return nil
}
return &statementInfo{
statement: extractOmniStatementText(fullSQL, n.Loc),
node: n,
table: table,
}
default:
return nil
}
}
func omniDMLTableReference(databaseName string, name *oracleast.ObjectName, alias *oracleast.Alias, statementType StatementType) *TableReference {
if name == nil || name.Name == "" {
return nil
}
schema := name.Schema
hasSchema := schema != ""
if schema == "" {
schema = databaseName
}
table := &TableReference{
Database: schema,
HasSchema: hasSchema,
Schema: schema,
Table: name.Name,
DBLink: name.DBLink,
StatementType: statementType,
}
if alias != nil {
table.Alias = alias.Name
}
return table
}
// writeBranchSelect writes one `SELECT <qualifier>.<projection> FROM <dml
// target and predicate>` branch. projection is "*" for a plain CTAS body and
// "ROWID" for the deduplication subquery branches.
func writeBranchSelect(buf *strings.Builder, info statementInfo, projection string, indent string) error {
t := info.table
switch {
case t.Alias != "":
if _, err := fmt.Fprintf(buf, `%sSELECT "%s".%s FROM `, indent, t.Alias, projection); err != nil {
return errors.Wrap(err, "failed to write to buffer")
}
case t.HasSchema:
if _, err := fmt.Fprintf(buf, `%sSELECT "%s"."%s".%s FROM `, indent, t.Schema, t.Table, projection); err != nil {
return errors.Wrap(err, "failed to write to buffer")
}
default:
if _, err := fmt.Fprintf(buf, `%sSELECT "%s".%s FROM `, indent, t.Table, projection); err != nil {
return errors.Wrap(err, "failed to write to buffer")
}
}
return errors.Wrap(writeSuffixSelectClause(buf, info.node, info.fullSQL), "failed to write suffix select clause")
}
func writeSuffixSelectClause(buf *strings.Builder, node oracleast.StmtNode, fullSQL string) error {
suffix := ""
switch n := node.(type) {
case *oracleast.UpdateStmt:
suffix = oracleDMLTargetText(fullSQL, n.Table, n.PartitionExt, n.Alias)
if n.WhereClause != nil && n.SetClauses != nil && n.SetClauses.Len() > 0 {
if setClause, ok := n.SetClauses.Items[n.SetClauses.Len()-1].(*oracleast.SetClause); ok {
whereStart := setClause.Loc.End
if fromText, fromEnd, ok := oracleListText(fullSQL, n.FromClause); ok {
suffix += ", " + fromText
whereStart = fromEnd
}
suffix = joinOracleSuffix(suffix, oracleWhereSuffix(fullSQL, whereStart, n.WhereClause))
}
}
case *oracleast.DeleteStmt:
targetEnd := n.Table.Loc.End
if n.PartitionExt != nil {
targetEnd = n.PartitionExt.Loc.End
}
if n.Alias != nil {
targetEnd = n.Alias.Loc.End
}
suffix = joinOracleSuffix(oracleDMLTargetText(fullSQL, n.Table, n.PartitionExt, n.Alias), oracleWhereSuffix(fullSQL, targetEnd, n.WhereClause))
default:
}
_, err := buf.WriteString(suffix)
return err
}
func oracleDMLTargetText(sql string, name *oracleast.ObjectName, partitionExt *oracleast.PartitionExtClause, alias *oracleast.Alias) string {
if name == nil {
return ""
}
end := name.Loc.End
if partitionExt != nil {
end = partitionExt.Loc.End
}
if alias != nil {
end = alias.Loc.End
}
return strings.TrimSpace(oracleLocText(sql, name.Loc.Start, end))
}
func oracleDMLTrailingText(sql string, start, end int) string {
text := strings.TrimSpace(oracleLocText(sql, start, end))
return strings.TrimSpace(strings.TrimSuffix(text, ";"))
}
func oracleListText(sql string, list *oracleast.List) (string, int, bool) {
if list == nil || list.Len() == 0 {
return "", 0, false
}
start, ok := oracleNodeLoc(list.Items[0])
if !ok {
return "", 0, false
}
end, ok := oracleNodeLoc(list.Items[list.Len()-1])
if !ok {
return "", 0, false
}
text := strings.TrimSpace(oracleLocText(sql, start.Start, end.End))
if text == "" {
return "", 0, false
}
return text, end.End, true
}
func oracleWhereSuffix(sql string, start int, where oracleast.ExprNode) string {
loc, ok := oracleNodeLoc(where)
if !ok {
return ""
}
return oracleDMLTrailingText(sql, start, loc.End)
}
func oracleNodeLoc(node oracleast.Node) (oracleast.Loc, bool) {
if node == nil {
return oracleast.Loc{}, false
}
value := reflect.ValueOf(node)
if value.Kind() != reflect.Pointer || value.IsNil() {
return oracleast.Loc{}, false
}
elem := value.Elem()
if elem.Kind() != reflect.Struct {
return oracleast.Loc{}, false
}
field := elem.FieldByName("Loc")
if !field.IsValid() || field.Type() != reflect.TypeOf(oracleast.Loc{}) {
return oracleast.Loc{}, false
}
loc, ok := field.Interface().(oracleast.Loc)
if !ok || loc.End <= loc.Start {
return oracleast.Loc{}, false
}
return loc, true
}
func oracleLocText(sql string, start, end int) string {
if start < 0 {
start = 0
}
if end <= 0 || end > len(sql) {
end = len(sql)
}
if start >= end || start >= len(sql) {
return ""
}
return sql[start:end]
}
func joinOracleSuffix(parts ...string) string {
var nonEmpty []string
for _, part := range parts {
if part != "" {
nonEmpty = append(nonEmpty, part)
}
}
return strings.Join(nonEmpty, " ")
}
func extractOmniStatementText(sql string, loc oracleast.Loc) string {
return strings.TrimSpace(strings.TrimSuffix(oracleLocText(sql, loc.Start, loc.End), ";"))
}
func zeroBasedColumnPosition(pos *store.Position) *store.Position {
if pos == nil {
return nil
}
column := pos.Column
if column > 0 {
column--
}
return &store.Position{
Line: pos.Line,
Column: column,
}
}