dalgo2oracle

README

dalgo2oracle

Oracle Database adapter for DALgo. It opens the server with the pure-Go driver github.com/sijms/go-ora/v3 (no Oracle client, compiles with CGO_ENABLED=0), wraps github.com/dal-go/dalgo2sql for the dal.DB surface, and adds Oracle-native implementations of:

• dbschema.SchemaReader — schema introspection over Oracle's ALL_ catalog views, scoped to one schema (owner), plus ListViews
• read-only transactions, which go-ora itself refuses
• errors that never carry the connection string, the user, the host, the port or the service name
• dal.ConcurrencyAware — SupportsConcurrentConnections() = true

Constructor usage

import (
    "context"

    "github.com/dal-go/dalgo2oracle"
)

db, err := dalgo2oracle.NewDatabase(ctx, "oracle://user:pass@host:1521/FREEPDB1")
if err != nil {
    return err // a *dalgo2oracle.ConnectionError when the server cannot be reached
}
defer db.Close()

The URL is go-ora's: oracle://user:password@host:port/service, with special characters percent-encoded. NewDatabase parses it, pings the server and reads the session's CURRENT_SCHEMA.

For native SQL, db.SQLDB() returns the *sql.DB pool, and NewConnector(dsn) returns a driver.Connector to open one yourself with sql.OpenDB; both carry the read-only transactions, the fast-login workaround and the error sanitising described below.

Options

Option	Effect
WithSchema(name)	Scopes the schema reader to name, spelled as Oracle stores it (an unquoted name is upper case). Default: the session's CURRENT_SCHEMA.
WithDbOptions(dalgo2sql.DbOptions{...})	The dalgo2sql options of the dal.DB surface (recordsets, exact numeric values, ...). StructuredQueryDialect is always set to "oracle"; IsAlreadyExists defaults to dalgo2oracle.IsAlreadyExists (ORA-00001).

Other exported API: Database.SchemaName(), Database.Adapter() (name dalgo2oracle, Version), Database.BeginReadOnly, Database.QueryReadOnly, IsAlreadyExists, ConnectionError with FailureKind, and the constants Version and StructuredQueryDialect.

Schema reader

ListCollections returns the tables, views and materialized views of the schema in alphabetical order, under the names Oracle stores. It leaves out Oracle-maintained objects, recycle-bin (BIN$) objects, nested, secondary and IOT overflow tables, materialized-view container tables and materialized-view log tables. ListViews returns the views and materialized views among them.

DescribeCollection reports, for a table, view or materialized view:

• the fields in column order (hidden columns left out), with their type, length, precision, nullability (a primary-key column is never nullable) and AutoIncrement for an identity column;
• defaults: a dbschema.DefaultLiteral holding the expression as Oracle stores it, except SYSDATE, SYSTIMESTAMP, CURRENT_DATE, CURRENT_TIMESTAMP and LOCALTIMESTAMP, which are dbschema.DefaultCurrentTimestamp; an identity column has no default, and a virtual column's is GENERATED ALWAYS AS (<expression>);
• the primary key, the indexes (not the one backing the primary key, not LOB indexes; a function-based column is named by its expression) and the outgoing foreign keys (a key to another schema names it in ReferencedNamespace; OnUpdate is always NO ACTION, which is all Oracle has; Enforcement follows the constraint's status);
• SourceDefinition with dialect oracle and each column's declared type (varchar2(100 char), number(10,2), raw(16), ...), NOT NULL, default SQL and primary-key position.

ListIndexes, ListConstraints (typed primary-key, unique, foreign-key and check; the check constraints Oracle records for NOT NULL columns are left out) and ListReferrers read the same catalog.

Which schema is read: ListCollections and ListViews list only the configured schema (WithSchema, else CURRENT_SCHEMA). DescribeCollection, ListIndexes, ListConstraints and ListReferrers read the schema the collection reference names (dal.CollectionRef.Schema()), and the configured one only when the reference names none. Either way they see only what the user's ALL_* views show, that is, objects the user owns or has been granted access to.

Type mapping

Oracle column type	dbschema.Type
VARCHAR2, NVARCHAR2, CHAR, NCHAR	String, Length = length in characters
NUMBER(p,0) with p ≤ 18, INTEGER	Int
NUMBER(p,s) otherwise	Decimal, Precision = (p, s)
NUMBER	Decimal
FLOAT, BINARY_FLOAT, BINARY_DOUBLE	Float
DATE, TIMESTAMP*	Time
RAW(n)	Bytes, Length = n
LONG RAW, BLOB, BFILE	Bytes
BOOLEAN (23ai)	Bool
anything else (CLOB, INTERVAL, XMLTYPE, JSON, ROWID, ...)	String; SourceDefinition keeps the declared type

Read-only transactions

go-ora refuses sql.TxOptions{ReadOnly: true}. The adapter's connector accepts it: it begins a plain transaction whose first statement is SET TRANSACTION READ ONLY, after which Oracle refuses every write in it with ORA-01456. Database.BeginReadOnly and Database.QueryReadOnly (one query, rolled back afterwards) use it, and so does any BeginTx with ReadOnly on db.SQLDB(). Only the default isolation level is supported.

Fast login is turned off

With go-ora's "fast login" on, go-ora v3.0.1 caches a negotiation cookie per service, and against Oracle Database 23ai Free every later connection of the process ends in EOF, so a pool cannot open a second connection. The connector therefore adds fast login=false (fast+login=false) to the URL's query, before any #fragment, unless the URL sets the option itself under a key go-ora reads (fast login in any case, nothing trimmed).

Error sanitising

go-ora and the Oracle listener put the user, host, port and service name into their error messages, and callers print errors. So:

• A failure to connect — from NewDatabase, or from the pool connecting again later — is a *ConnectionError whose text is only the step, a fixed sentence chosen by Kind (FailureInvalidDSN, FailureNetwork, FailureTimeout, FailureAuthentication, FailureService, FailureServer, FailureOther) and the ORA number in Code. The driver's error is not reachable through errors.Unwrap/Is/As; branch on Kind and Code. errors.Is still finds context.Canceled and context.DeadlineExceeded.
• A transport failure on an open connection (a reset socket, an EOF) becomes a *ConnectionError too.
• A server error about a statement (*network.OracleError, for example ORA-00942) passes unchanged, as do driver.ErrBadConn and context errors.

Current limitations

• Structured queries (dal.DB query execution) need dalgo2sql's "oracle" structured-query dialect, which dalgo2sql does not have yet; it is being added in dal-go/dalgo2sql. Until then the schema reader and native SQL through SQLDB() are the working surface.
• Key reads and writes (Get, Set, Insert, Delete, ...) need Oracle's :1, :2, ... bind placeholders. dalgo2sql emits only ? (PlaceholderQuestion) and $1 (PlaceholderDollar), so these do not work against Oracle yet.
• No ddl.Applier: the adapter does not create or alter tables.

Running the tests

go test ./... runs the unit tests (sqlmock and fake drivers) and covers every statement without a server.

TestOracleServer runs against a real server and skips unless DALGO2ORACLE_TEST_URL names one. It creates D2O_* fixture objects in the user's own schema and drops them again. With Docker:

docker run -d --name oracle-free -p 1521:1521 \
  -e ORACLE_PASSWORD=sys_pw_9 -e APP_USER=scott -e APP_USER_PASSWORD=tiger9 \
  gvenzl/oracle-free:23-slim

DALGO2ORACLE_TEST_URL='oracle://scott:tiger9@localhost:1521/FREEPDB1' \
  go test ./... -count=1 -v -run TestOracleServer

The user and password must not be words of the adapter's error text (such as dalgo or oracle): the test asserts that a refused login names nothing of the URL.

CI runs the same test against gvenzl/oracle-free:23-slim pinned by digest and fails on a skip or a missing pass line. Oracle Database Free is used there under the Oracle Free Use Terms and Conditions, for automated testing only; the image is never redistributed.

License

MIT — see LICENSE.

Documentation

Overview

Package dalgo2oracle is the Oracle Database adapter of DALgo.

It opens the server with github.com/sijms/go-ora/v3 (pure Go), composes github.com/dal-go/dalgo2sql for the dal.DB surface with the structured-query dialect "oracle", and adds:

• dbschema.SchemaReader over Oracle's ALL_ catalog views, scoped to one schema (the session's CURRENT_SCHEMA unless WithSchema names another), plus ListViews, which tells a caller which collections are views;
• read-only transactions, which go-ora refuses: see NewConnector;
• errors that never carry the connection string, the user, the host or the service: see ConnectionError.

Index

• Constants
• func IsAlreadyExists(err error) bool
• func NewConnector(dsn string) driver.Connector
• type ConnectionError
  • func (e *ConnectionError) Error() string
  • func (e *ConnectionError) Is(target error) bool
• type Database
  • func NewDatabase(ctx context.Context, dsn string, opts ...Option) (*Database, error)
  • func (d *Database) Adapter() dal.Adapter
  • func (d *Database) BeginReadOnly(ctx context.Context) (*sql.Tx, error)
  • func (d *Database) Close() error
  • func (d *Database) DescribeCollection(ctx context.Context, ref *dal.CollectionRef) (*dbschema.CollectionDef, error)
  • func (d *Database) ListCollections(ctx context.Context, parent *dalrecord.Key) ([]dal.CollectionRef, error)
  • func (d *Database) ListConstraints(ctx context.Context, ref *dal.CollectionRef) ([]dbschema.ConstraintDef, error)
  • func (d *Database) ListIndexes(ctx context.Context, ref *dal.CollectionRef) ([]dbschema.IndexDef, error)
  • func (d *Database) ListReferrers(ctx context.Context, ref *dal.CollectionRef) ([]dbschema.Referrer, error)
  • func (d *Database) ListViews(ctx context.Context) ([]dal.CollectionRef, error)
  • func (d *Database) QueryReadOnly(ctx context.Context, query string, args []any, read func(*sql.Rows) error) error
  • func (d *Database) SQLDB() *sql.DB
  • func (d *Database) SchemaName() string
  • func (d *Database) SupportsConcurrentConnections() bool
• type FailureKind
  • func (k FailureKind) String() string
• type Option
  • func WithDbOptions(dbOptions dalgo2sql.DbOptions) Option
  • func WithSchema(schema string) Option

Constants

const StructuredQueryDialect = "oracle"

StructuredQueryDialect is the dalgo2sql dialect the adapter sets.

const Version = "0.1.0"

Version is the adapter version reported by Database.Adapter.

Functions

func IsAlreadyExists

func IsAlreadyExists(err error) bool

IsAlreadyExists reports whether err is a unique-constraint violation (ORA-00001).

func NewConnector

func NewConnector(dsn string) driver.Connector

NewConnector returns a driver.Connector for dsn (oracle://user:pass@host:port/service) that a caller can open with sql.OpenDB for native SQL. It differs from go-ora's own connector in two ways:

• A transaction begun with ReadOnly (sql.TxOptions{ReadOnly: true}) is accepted: go-ora refuses one, so it is begun as a plain transaction whose first statement is SET TRANSACTION READ ONLY. Oracle then refuses every write in it (ORA-01456).
• Errors never carry the connection string or the server's address: a failure to connect is a *ConnectionError, and so is a transport failure later on (see ConnectionError).

It also turns go-ora's "fast login" off unless dsn sets it: with it on, go-ora v3.0.1 caches a negotiation cookie per service, and against Oracle 23ai Free every later connection of the process then ends in EOF, so a pool cannot open a second connection.

Types

type ConnectionError

type ConnectionError struct {
	// Kind is what failed.
	Kind FailureKind
	// Code is the ORA number of a server error, for example 1017 for ORA-01017;
	// zero when the server sent none.
	Code int
	// contains filtered or unexported fields
}

ConnectionError is the error a connection that fails is reported as: by NewDatabase when the connection to Oracle cannot be opened or verified, and by any later call whose connection fails (the pool connecting again, a socket that is reset or times out).

Its text is built only from a fixed sentence chosen by Kind and the ORA number in Code. Nothing of the connection string, and nothing of the driver's or the server's own message, is ever copied into it: go-ora and the listener put the user, host, port and service name into theirs, and callers print errors. For the same reason the driver's error is not reachable through errors.Unwrap, errors.Is or errors.As; branch on Kind and Code instead. The one thing kept of it is whether the attempt ended because its context was canceled or its deadline passed: errors.Is finds context.Canceled and context.DeadlineExceeded in such an error.

func (*ConnectionError) Error

func (e *ConnectionError) Error() string

Error returns the text: the step, the fixed sentence and the ORA number only.

func (*ConnectionError) Is

func (e *ConnectionError) Is(target error) bool

Is reports the two context errors of the attempt that failed, and nothing else.

type Database

type Database struct {
	dal.ConcurrencyAvailable
	dal.DB
	// contains filtered or unexported fields
}

Database is an Oracle database opened by NewDatabase. It is a dal.DB (the embedded dalgo2sql database), a dbschema.SchemaReader and an io.Closer.

func NewDatabase

func NewDatabase(ctx context.Context, dsn string, opts ...Option) (*Database, error)

NewDatabase opens the Oracle database dsn names (oracle://user:pass@host:port/service), pings it, and reads the session's CURRENT_SCHEMA unless WithSchema names a schema. A failure to connect is a *ConnectionError, whose text holds nothing of dsn.

func (*Database) Adapter

func (d *Database) Adapter() dal.Adapter

Adapter returns the adapter name and version.

func (*Database) BeginReadOnly

func (d *Database) BeginReadOnly(ctx context.Context) (*sql.Tx, error)

BeginReadOnly begins a transaction whose first statement is SET TRANSACTION READ ONLY: Oracle refuses every write in it. The caller ends it; a rollback is the usual end of a read.

func (*Database) Close

func (d *Database) Close() error

Close closes the connection pool.

func (*Database) DescribeCollection

func (d *Database) DescribeCollection(ctx context.Context, ref *dal.CollectionRef) (*dbschema.CollectionDef, error)

DescribeCollection returns the fields, primary key, indexes and outgoing foreign keys of a table, view or materialized view. A column's default is a dbschema.DefaultLiteral holding the expression as Oracle stores it, except SYSDATE, SYSTIMESTAMP, CURRENT_DATE, CURRENT_TIMESTAMP and LOCALTIMESTAMP, which are dbschema.DefaultCurrentTimestamp; an identity column has AutoIncrement and no default; a virtual column's default is "GENERATED ALWAYS AS (<expression>)". A type with no dbschema counterpart (INTERVAL, XMLTYPE, JSON, ROWID, ...) is reported as a String; SourceDefinition keeps the declared Oracle types.

func (*Database) ListCollections

func (d *Database) ListCollections(ctx context.Context, parent *dalrecord.Key) ([]dal.CollectionRef, error)

ListCollections returns the tables, views and materialized views of the schema in alphabetical order, under the names Oracle stores. The parent key is ignored.

func (*Database) ListConstraints

func (d *Database) ListConstraints(ctx context.Context, ref *dal.CollectionRef) ([]dbschema.ConstraintDef, error)

ListConstraints returns the primary-key, unique, foreign-key and check constraints of a table, typed "primary-key", "unique", "foreign-key" and "check". The check constraints Oracle records for NOT NULL columns are left out: DescribeCollection reports them as non-nullable fields.

func (*Database) ListIndexes

func (d *Database) ListIndexes(ctx context.Context, ref *dal.CollectionRef) ([]dbschema.IndexDef, error)

ListIndexes returns the indexes of a table but the one that backs its primary key: the indexes of unique constraints are unique indexes. A column of a function-based index is named by its expression; a descending column, which Oracle stores as the expression "NAME", by its name.

func (*Database) ListReferrers

func (d *Database) ListReferrers(ctx context.Context, ref *dal.CollectionRef) ([]dbschema.Referrer, error)

ListReferrers returns the tables of the schema whose foreign keys reference the table, each with its referencing columns.

func (*Database) ListViews

func (d *Database) ListViews(ctx context.Context) ([]dal.CollectionRef, error)

ListViews returns the collections of the schema that are views or materialized views, by the references ListCollections gives them. It is the method a schema consumer can look for to tell a view from a table.

func (*Database) QueryReadOnly

func (d *Database) QueryReadOnly(ctx context.Context, query string, args []any, read func(*sql.Rows) error) error

QueryReadOnly runs one native SQL query in a read-only transaction, hands its rows to read, and rolls the transaction back. The error is the first of the begin, the query, read and the rows' own.

func (*Database) SQLDB

func (d *Database) SQLDB() *sql.DB

SQLDB returns the connection pool, for native SQL. A transaction begun on it with sql.TxOptions{ReadOnly: true} is read-only (see NewConnector).

func (*Database) SchemaName

func (d *Database) SchemaName() string

SchemaName returns the schema (owner) the schema reader inspects.

func (*Database) SupportsConcurrentConnections

func (d *Database) SupportsConcurrentConnections() bool

SupportsConcurrentConnections reports true: Oracle serves many sessions. The explicit method resolves the ambiguity between the two embedded ones.

type FailureKind

type FailureKind int

FailureKind says what kind of failure a ConnectionError reports. It decides the fixed sentence the error's text uses.

const (
	// FailureOther is any failure the kinds below do not describe. It is the zero
	// value.
	FailureOther FailureKind = iota
	// FailureInvalidDSN: the driver cannot parse the connection string.
	FailureInvalidDSN
	// FailureNetwork: the server could not be reached, or the connection to it
	// was lost (name resolution, a refused or reset connection, the end of the
	// stream).
	FailureNetwork
	// FailureTimeout: the attempt timed out or was canceled.
	FailureTimeout
	// FailureAuthentication: the server refused the credentials or the account
	// (ORA-01017, ORA-28000, ORA-28001, ORA-01045).
	FailureAuthentication
	// FailureService: the listener does not know the requested service or SID
	// (ORA-12514, ORA-12505).
	FailureService
	// FailureServer: the server answered with another error. [ConnectionError.Code]
	// holds its ORA number.
	FailureServer
)

func (FailureKind) String

func (k FailureKind) String() string

String returns the fixed sentence of the kind.

type Option

type Option func(*options)

Option configures NewDatabase.

func WithDbOptions

func WithDbOptions(dbOptions dalgo2sql.DbOptions) Option

WithDbOptions sets the dalgo2sql options of the dal.DB surface (recordsets, exact numeric values, ...). StructuredQueryDialect is always "oracle", and IsAlreadyExists defaults to IsAlreadyExists.

func WithSchema

func WithSchema(schema string) Option

WithSchema scopes the schema reader to the named schema (owner), spelled as Oracle stores it (an unquoted name is stored in upper case). Without it the session's CURRENT_SCHEMA is read.