Skip to content

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

17.5 KB
Raw Blame
// Package oracle is the plugin for Oracle driver.
package oracle

import (
	"context"
	"crypto/rand"
	"crypto/tls"
	"database/sql"
	"database/sql/driver"
	"encoding/json"
	"fmt"
	"log/slog"
	"math/big"
	"strconv"
	"strings"
	"time"

	// Import go-ora Oracle driver.

	"github.com/pkg/errors"
	goora "github.com/sijms/go-ora/v2"
	"google.golang.org/protobuf/types/known/durationpb"

	"github.com/bytebase/bytebase/backend/common/log"
	storepb "github.com/bytebase/bytebase/backend/generated-go/store"
	v1pb "github.com/bytebase/bytebase/backend/generated-go/v1"
	"github.com/bytebase/bytebase/backend/plugin/db"
	"github.com/bytebase/bytebase/backend/plugin/db/transaction"
	"github.com/bytebase/bytebase/backend/plugin/db/util"
	"github.com/bytebase/bytebase/backend/plugin/parser/base"
	plsqlparser "github.com/bytebase/bytebase/backend/plugin/parser/plsql"
)

var (
	_ db.Driver = (*Driver)(nil)
)

func init() {
	db.Register(storepb.Engine_ORACLE, newDriver)
}

// Driver is the Oracle driver.
type Driver struct {
	db            *sql.DB
	databaseName  string
	serviceName   string
	connectionCtx db.ConnectionContext
}

func newDriver() db.Driver {
	return &Driver{}
}

// GetVersion gets the Oracle version.
func (d *Driver) GetVersion() (*plsqlparser.Version, error) {
	return plsqlparser.ParseVersion(d.connectionCtx.EngineVersion)
}

// Open opens a Oracle driver.
func (d *Driver) Open(ctx context.Context, _ storepb.Engine, config db.ConnectionConfig) (db.Driver, error) {
	port, err := strconv.Atoi(config.DataSource.Port)
	if err != nil {
		return nil, errors.Errorf("invalid port %q", config.DataSource.Port)
	}
	tlsConfig, err := getTLSConfig(config.DataSource)
	if err != nil {
		return nil, errors.Wrap(err, "failed to build TLS configuration")
	}
	options := connectionOptions(config.DataSource.GetSid(), config.DataSource.GetExtraConnectionParameters(), tlsConfig != nil)
	dsn := goora.BuildUrl(config.DataSource.Host, port, config.DataSource.GetServiceName(), config.DataSource.Username, config.Password, options)
	db := sql.OpenDB(&connector{dsn: dsn, tlsConfig: tlsConfig, currentSchema: config.ConnectionContext.DatabaseName})
	if config.ConnectionContext.DatabaseName != "" {
		// Connect now so that a schema the session can't switch to fails Open, not the first statement.
		if err := db.PingContext(ctx); err != nil {
			_ = db.Close()
			return nil, err
		}
	}
	d.db = db
	d.databaseName = config.ConnectionContext.DatabaseName
	d.serviceName = config.DataSource.GetServiceName()
	d.connectionCtx = config.ConnectionContext
	return d, nil
}

// getTLSConfig returns the TLS configuration for the data source's TLS section, or nil when TLS
// is off. It leaves the name check to crypto/tls instead of util.GetTLSConfig's verifier, which
// checks Hostname: go-ora sets ServerName to each server it dials, so a SERVER address or a
// listener redirect is checked against its own name.
func getTLSConfig(dataSource *storepb.DataSource) (*tls.Config, error) {
	config, err := util.GetTLSConfig(dataSource)
	if err != nil || config == nil {
		return config, err
	}
	config.InsecureSkipVerify = !dataSource.GetVerifyTlsCertificate()
	config.VerifyPeerCertificate = nil
	return config, nil
}

// connector opens each connection through go-ora's default driver, as sql.Open("oracle") does,
// with its own copy of the TLS configuration: go-ora sets ServerName on the configuration it is
// given, so connections opened in parallel can't share one.
//
// It also sets currentSchema on every session it opens, because the pool opens a new session
// whenever none is idle or one goes bad. go-ora's AddSessionParam can't do this: it stores the
// parameter on the default driver, which every Bytebase Oracle connection shares.
type connector struct {
	dsn           string
	tlsConfig     *tls.Config
	currentSchema string
}

func (c *connector) Connect(ctx context.Context) (driver.Conn, error) {
	oracleConnector, err := goora.GetDefaultDriver().OpenConnector(c.dsn)
	if err != nil {
		return nil, err
	}
	oracleConnector.(*goora.OracleConnector).WithTLSConfig(c.tlsConfig.Clone())
	conn, err := oracleConnector.Connect(ctx)
	if err != nil {
		return nil, err
	}
	if c.currentSchema == "" {
		return conn, nil
	}
	if _, err := conn.(driver.ExecerContext).ExecContext(ctx, fmt.Sprintf("ALTER SESSION SET CURRENT_SCHEMA = \"%s\"", c.currentSchema), nil); err != nil {
		_ = conn.Close()
		return nil, errors.Wrapf(err, "failed to set current schema to %q", c.currentSchema)
	}
	return conn, nil
}

func (*connector) Driver() driver.Driver {
	return goora.GetDefaultDriver()
}

const (
	sidOption              = "SID"
	sslOption              = "SSL"
	dialTimeoutOption      = "CONNECTION TIMEOUT"
	dialTimeoutOptionAlias = "CONNECT TIMEOUT"
	// defaultDialTimeoutSeconds bounds the wait for one address to accept a connection, DNS
	// lookup included, so an unreachable address fails over in seconds. It does not limit
	// how long a statement runs.
	defaultDialTimeoutSeconds = "10"
)

// connectionOptions returns the go-ora URL options for a data source: the extra connection
// parameters as typed, the SID field and the default dial timeout unless an extra parameter
// sets the same option, and SSL=TRUE when TLS is on.
//
// go-ora upper-cases option keys and reads CONNECT TIMEOUT as CONNECTION TIMEOUT. A Bytebase
// key sent next to a user key for the same option would leave the winner to go-ora's map
// iteration order, so only one is sent: the user's key for SID and the dial timeout, and
// Bytebase's SSL, which the TLS section decides.
func connectionOptions(sid string, extraParameters map[string]string, useTLS bool) map[string]string {
	options := make(map[string]string, len(extraParameters)+3)
	userOptions := make(map[string]bool, len(extraParameters))
	for key, value := range extraParameters {
		option := strings.ToUpper(key)
		if useTLS && option == sslOption {
			continue
		}
		options[key] = value
		userOptions[option] = true
	}
	if sid != "" && !userOptions[sidOption] {
		options[sidOption] = sid
	}
	if !userOptions[dialTimeoutOption] && !userOptions[dialTimeoutOptionAlias] {
		options[dialTimeoutOption] = defaultDialTimeoutSeconds
	}
	if useTLS {
		options[sslOption] = "TRUE"
	}
	return options
}

// Close closes the driver.
func (d *Driver) Close(_ context.Context) error {
	return d.db.Close()
}

// Ping pings the database.
func (d *Driver) Ping(ctx context.Context) error {
	return d.db.PingContext(ctx)
}

// GetDB gets the database.
func (d *Driver) GetDB() *sql.DB {
	return d.db
}

// Execute executes the migration, `beforeCommitTxFunc` will be called before transaction commit and after executing `statement`.
//
// Callers can use `beforeCommitTx` to do some extra work before transaction commit, like get the transaction id.
// Any error returned by `beforeCommitTx` will rollback the transaction, so it is the callers' responsibility to return nil if the error occurs in `beforeCommitTx` is not fatal.
func (d *Driver) Execute(ctx context.Context, statement string, opts db.ExecuteOptions) (int64, error) {
	if opts.CreateDatabase {
		return 0, errors.New("create database is not supported for Oracle")
	}

	// Parse transaction mode from the script
	config, cleanedStatement := base.ParseTransactionConfig(statement)
	statement = cleanedStatement
	transactionMode := config.Mode

	// Apply default when transaction mode is not specified
	if transactionMode == transaction.ModeUnspecified {
		transactionMode = transaction.DefaultMode()
	}

	commands, err := buildExecuteCommands(statement)
	if err != nil {
		return 0, err
	}
	if len(commands) == 0 {
		return 0, nil
	}

	conn, err := d.db.Conn(ctx)
	if err != nil {
		return 0, errors.Wrapf(err, "failed to get connection")
	}
	defer conn.Close()

	// Execute based on transaction mode
	if transactionMode == transaction.ModeOff {
		return d.executeInAutoCommitMode(ctx, conn, commands, opts)
	}
	return d.executeInTransactionMode(ctx, conn, commands, opts)
}

func buildExecuteCommands(statement string) ([]base.Statement, error) {
	commands, err := plsqlparser.SplitSQL(statement)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to split sql")
	}
	return base.FilterEmptyStatements(commands), nil
}

// executeInTransactionMode executes statements within a single transaction
func (*Driver) executeInTransactionMode(ctx context.Context, conn *sql.Conn, commands []base.Statement, opts db.ExecuteOptions) (int64, error) {
	tx, err := conn.BeginTx(ctx, nil)
	if err != nil {
		opts.LogTransactionControl(storepb.TaskRunLog_TransactionControl_BEGIN, err.Error())
		return 0, errors.Wrapf(err, "failed to begin transaction")
	}
	opts.LogTransactionControl(storepb.TaskRunLog_TransactionControl_BEGIN, "")

	committed := false
	defer func() {
		err := tx.Rollback()
		if committed {
			return
		}
		var rerr string
		if err != nil {
			rerr = err.Error()
		}
		opts.LogTransactionControl(storepb.TaskRunLog_TransactionControl_ROLLBACK, rerr)
	}()

	totalRowsAffected := int64(0)
	for _, command := range commands {
		opts.LogCommandExecute(command.Range, command.Text)

		sqlResult, err := tx.ExecContext(ctx, command.Text)
		if err != nil {
			opts.LogCommandResponse(0, nil, err.Error())
			return 0, err
		}
		rowsAffected, err := sqlResult.RowsAffected()
		if err != nil {
			// Since we cannot differentiate DDL and DML yet, we have to ignore the error.
			slog.Debug("rowsAffected returns error", log.BBError(err))
			rowsAffected = 0
		}
		opts.LogCommandResponse(rowsAffected, []int64{rowsAffected}, "")
		totalRowsAffected += rowsAffected
	}

	if err := tx.Commit(); err != nil {
		opts.LogTransactionControl(storepb.TaskRunLog_TransactionControl_COMMIT, err.Error())
		return 0, errors.Wrapf(err, "failed to commit transaction")
	}
	opts.LogTransactionControl(storepb.TaskRunLog_TransactionControl_COMMIT, "")
	committed = true
	return totalRowsAffected, nil
}

// executeInAutoCommitMode executes statements sequentially in auto-commit mode
func (*Driver) executeInAutoCommitMode(ctx context.Context, conn *sql.Conn, commands []base.Statement, opts db.ExecuteOptions) (int64, error) {
	totalRowsAffected := int64(0)
	for _, command := range commands {
		opts.LogCommandExecute(command.Range, command.Text)

		sqlResult, err := conn.ExecContext(ctx, command.Text)
		if err != nil {
			opts.LogCommandResponse(0, nil, err.Error())
			// In auto-commit mode, we stop at the first error
			// The database is left in a partially migrated state
			return totalRowsAffected, err
		}
		rowsAffected, err := sqlResult.RowsAffected()
		if err != nil {
			// Since we cannot differentiate DDL and DML yet, we have to ignore the error.
			slog.Debug("rowsAffected returns error", log.BBError(err))
			rowsAffected = 0
		}
		opts.LogCommandResponse(rowsAffected, []int64{rowsAffected}, "")
		totalRowsAffected += rowsAffected
	}
	return totalRowsAffected, nil
}

// OwnsPrivateDatabaseLink reports whether the session's account owns a private database link.
// SYS.USER_DB_LINKS lists the links owned by the current user; the SYS qualifier keeps a
// same-named local object from standing in for the dictionary view.
func (*Driver) OwnsPrivateDatabaseLink(ctx context.Context, conn *sql.Conn) (bool, error) {
	var count int
	if err := conn.QueryRowContext(ctx, "SELECT COUNT(*) FROM SYS.USER_DB_LINKS").Scan(&count); err != nil {
		return false, errors.Wrap(err, "failed to count private database links")
	}
	return count > 0, nil
}

// QueryConn queries a SQL statement in a given connection.
func (d *Driver) QueryConn(ctx context.Context, conn *sql.Conn, statement string, queryContext db.QueryContext) ([]*v1pb.QueryResult, error) {
	singleSQLs, err := plsqlparser.SplitSQL(statement)
	if err != nil {
		return nil, err
	}
	singleSQLs = base.FilterEmptyStatements(singleSQLs)
	if len(singleSQLs) == 0 {
		return nil, nil
	}

	var results []*v1pb.QueryResult
	for _, singleSQL := range singleSQLs {
		statement := singleSQL.Text
		planAsJSON := false
		if queryContext.Explain {
			startTime := time.Now()
			randNum, err := rand.Int(rand.Reader, big.NewInt(999))
			if err != nil {
				return nil, errors.Wrapf(err, "failed to generate random statement ID")
			}
			randomID := fmt.Sprintf("%d%d", startTime.UnixMilli(), randNum.Int64())

			if _, err := conn.ExecContext(ctx, fmt.Sprintf("EXPLAIN PLAN SET STATEMENT_ID = '%s' FOR %s", randomID, statement)); err != nil {
				return nil, err
			}
			planAsJSON = db.ExplainFormat(queryContext.Option.GetExplainFormat()) == base.ExplainFormatJSON
			if planAsJSON {
				statement = fmt.Sprintf(planStepsQuery, randomID)
			} else {
				statement = fmt.Sprintf(`SELECT LPAD(' ', LEVEL-1) || OPERATION || ' (' || OPTIONS || ')' "Operation", OBJECT_NAME "Object", OPTIMIZER "Optimizer", COST "Cost", CARDINALITY "Cardinality", BYTES "Bytes", PARTITION_START "Partition Start", PARTITION_ID "Partition ID", ACCESS_PREDICATES "Access Predicates" FROM PLAN_TABLE START WITH ID = 0 AND statement_id = '%s' CONNECT BY PRIOR ID=PARENT_ID AND statement_id = '%s' ORDER BY id`, randomID, randomID)
			}
		}

		if !queryContext.Explain && queryContext.Limit > 0 {
			statement = base.StatementWithResultLimit(storepb.Engine_ORACLE, statement, queryContext.Limit, d.connectionCtx.EngineVersion)
		}

		_, allQuery, err := base.ValidateSQLForEditor(storepb.Engine_ORACLE, statement)
		if err != nil {
			return nil, err
		}
		startTime := time.Now()
		queryResult, err := func() (*v1pb.QueryResult, error) {
			if planAsJSON {
				return queryPlanSteps(ctx, conn, statement)
			}
			if allQuery {
				rows, err := conn.QueryContext(ctx, statement)
				if err != nil {
					return nil, err
				}
				defer rows.Close()
				r, err := util.RowsToQueryResult(rows, makeValueByTypeName, convertValue, queryContext.MaximumSQLResultSize)
				if err != nil {
					return nil, err
				}
				if err := rows.Err(); err != nil {
					return nil, err
				}
				return r, nil
			}

			sqlResult, err := conn.ExecContext(ctx, statement)
			if err != nil {
				return nil, err
			}
			affectedRows, err := sqlResult.RowsAffected()
			if err != nil {
				slog.Info("rowsAffected returns error", log.BBError(err))
			}
			return util.BuildAffectedRowsResult(affectedRows, nil), nil
		}()
		stop := false
		if err != nil {
			queryResult = &v1pb.QueryResult{
				Error: err.Error(),
			}
			stop = true
		}
		queryResult.Statement = statement
		queryResult.Latency = durationpb.New(time.Since(startTime))
		queryResult.RowsCount = int64(len(queryResult.Rows))
		results = append(results, queryResult)
		if stop {
			break
		}
	}

	return results, nil
}

// planStepsQuery reads the plan Oracle recorded for one EXPLAIN PLAN: a row per
// operator, each naming its parent, which is how Oracle states a plan. The
// columns are what the query-plan viewer reads — the tree's shape, what each
// step does, the object it reads, and what the optimizer expects it to cost.
const planStepsQuery = `SELECT id, parent_id, position, operation, options, object_owner, object_name, object_type, optimizer, cost, cardinality, bytes, partition_start, partition_stop, access_predicates, filter_predicates FROM plan_table WHERE statement_id = '%s' ORDER BY id`

// queryPlanSteps returns those rows as one JSON array, which is the form the
// query-plan viewer parses; the viewer rebuilds the tree from the parent links.
//
// Oracle renders a plan as rows rather than as a document, and no EXPLAIN option
// renders it as one, so this is where the rendering happens. It is built here
// rather than with Oracle's own JSON_ARRAYAGG so that it works on releases older
// than 12.2, which do not have those functions. A tab-separated grid would not
// do: a predicate can hold a tab or a newline, and nothing downstream could tell
// that apart from the next column or the next row.
func queryPlanSteps(ctx context.Context, conn *sql.Conn, query string) (*v1pb.QueryResult, error) {
	rows, err := conn.QueryContext(ctx, query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	columns, err := rows.Columns()
	if err != nil {
		return nil, err
	}
	var steps [][]any
	for rows.Next() {
		values := make([]any, len(columns))
		scan := make([]any, len(columns))
		for i := range values {
			scan[i] = &values[i]
		}
		if err := rows.Scan(scan...); err != nil {
			return nil, err
		}
		steps = append(steps, values)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}

	payload, err := renderPlanSteps(columns, steps)
	if err != nil {
		return nil, err
	}
	return &v1pb.QueryResult{
		ColumnNames:     []string{"PLAN"},
		ColumnTypeNames: []string{"CLOB"},
		Rows: []*v1pb.QueryRow{{Values: []*v1pb.RowValue{
			{Kind: &v1pb.RowValue_StringValue{StringValue: payload}},
		}}},
	}, nil
}

// renderPlanSteps writes the scanned plan rows as a JSON array, one object per
// operator keyed by the lower-cased column names.
//
// A column the optimizer had nothing to say about is left out rather than
// carried as a null, so a reader of the plan sees only what Oracle stated.
func renderPlanSteps(columns []string, rows [][]any) (string, error) {
	steps := make([]map[string]any, 0, len(rows))
	for _, row := range rows {
		step := make(map[string]any, len(columns))
		for i, column := range columns {
			if i >= len(row) {
				break
			}
			if value := jsonValue(row[i]); value != nil {
				step[strings.ToLower(column)] = value
			}
		}
		steps = append(steps, step)
	}
	payload, err := json.Marshal(steps)
	if err != nil {
		return "", errors.Wrap(err, "failed to render the query plan")
	}
	return string(payload), nil
}

// jsonValue renders one scanned column, or nil where the row has no value for
// it. The driver hands back text as bytes, which JSON would otherwise encode as
// base64.
func jsonValue(value any) any {
	switch typed := value.(type) {
	case nil:
		return nil
	case []byte:
		return string(typed)
	default:
		return typed
	}
}