NAME
DBIO::Oracle::Deploy - Deploy, diff and upgrade a schema on Oracle, against a temporary user
VERSION
version 0.900007
DESCRIPTION
DBIO::Oracle::Deploy deploys a schema to Oracle. install runs the install DDL (DBIO::Oracle::DDL) against the connected database -- it is what $schema->deploy calls -- and apply runs the SQL of a diff it is handed. Both are inherited from DBIO::Deploy::Base unchanged; this class supplies the three class-name hooks they work through and the check of "_check_before_apply". A diff that drops something or may alter stored values (a lossy column change, "lossy" in DBIO::Oracle::Diff::Column) runs only with allow_destructive => 1.
diff and upgrade are core's as well. The desired state they compare the live schema with is built in a temporary Oracle user, the counterpart of the temporary database of DBIO::Deploy::Base::TempDatabase (see "The desired state: a temporary user"). A table only the desired schema has is created whole by the diff (DBIO::Oracle::Diff): its columns and keys, sequence and trigger, then its indexes, then its foreign keys; an upgrade over an empty schema makes what install makes, and the next one finds nothing to do.
my $deploy = DBIO::Oracle::Deploy->new(
schema => MyApp::DB->connect($dsn),
);
$deploy->install; # fresh, into an empty schema
my $diff = $deploy->diff; # live schema against the desired one
$deploy->upgrade; # diff, then apply
The desired state: a temporary user
An Oracle schema is a user account, and Oracle commits implicitly around every DDL statement. A test deploy on the live connection therefore cannot be taken back (the first CREATE TABLE commits the transaction and erases the savepoint, ORA-01086; ADR 0002, overtaken by ADR 0010). So "_build_target_model" in DBIO::Deploy::Base::TempDatabase deploys the schema into a user made for the purpose:
The privileges are checked first: the connected user needs
CREATE USERandDROP USER(read fromsession_privs). If one is missing a DBIO exception names it, and nothing has been created.CREATE USER DBIO_TMP_<pid>_<n>(ncounts per process, the password is random) with an unlimited quota on the default tablespace of the connected user, and the grants the install DDL needs:CREATE SESSION,TABLE,SEQUENCE,TRIGGER,VIEW. The connected user has to be able to grant them (WITH ADMIN OPTION).The install DDL runs on a connection of the temp user (same DSN), the result is introspected there -- the introspection reads the user it is connected as -- and the connection is closed.
DROP USER ... CASCADE, whatever became of the deploy or the introspection. A drop that fails does not change the outcome, but is warned about with the name of the user left on the server; passwords in the message are shown as***.
CREATE USER, GRANT and DROP USER commit implicitly, so they never run on the connection of the schema: they run on a connection of their own (same credentials, AutoCommit), opened for the call and closed after it. The live connection is read from (the introspection of the live schema) and nothing else is done to it; a transaction the caller has open stays open and uncommitted.
Transactional DDL -- the bracket this engine cannot carry
DBIO::Oracle::Storage declares _use_transactional_ddl(0), so "_execute_ddl" in DBIO::Deploy::Base runs the statements of an "install" in DBIO::Deploy::Base or an "apply" in DBIO::Deploy::Base one at a time and core emits its one-time non-transactional DDL notice naming this class. The notice is a true statement about this engine, not a defect to be silenced.
Oracle commits around every DDL statement -- before it "even if the statement results in an error", and again after it when it completes without one (SQL Language Reference 19c, COMMIT). A $storage->txn_do around the loop would therefore be worse than no bracket at all: the first CREATE TABLE commits the transaction txn_do just opened, and the ROLLBACK at the end of a failed block has nothing left to reach. The full reasoning sits at the declaration in DBIO::Oracle::Storage.
What a rollback still recovers after a half-run deploy
Nothing. When apply has run n of m statements and the n+1st throws, all n are committed and permanent: each was committed by the statement following it, and the last one by the failing statement's own leading COMMIT, which runs whether or not that statement then succeeds. Nothing on the connection or in the session takes any of it back -- txn_rollback, an enclosing DBIO::Storage::TxnScopeGuard, an explicit ROLLBACK TO SAVEPOINT: each of them acts on a transaction the first DDL statement had already ended.
The recycle bin is a recovery tool, not a rollback
Oracle's Flashback Drop is sometimes offered as the missing undo. It is worth being exact about what it is: a manual, after-the-fact recovery path an operator drives per object and by name, working from "dropped data in the recycle bin rather than undo data" (SQL Language Reference 19c, FLASHBACK TABLE). It is not transaction semantics, and nothing in this class invokes it on a caller's behalf.
For this deploy path it also mostly does not apply. Three statements in DBIO::Oracle::Diff remove anything: DROP TABLE ... CASCADE CONSTRAINTS (DBIO::Oracle::Diff::Table), ALTER TABLE ... DROP COLUMN (DBIO::Oracle::Diff::Column) and DROP INDEX (DBIO::Oracle::Diff::Index). Only the first leaves anything behind: "Only the DROP TABLE SQL statement places objects in the recycle bin" (Administrator's Guide 19c, What Is the Recycle Bin?). The dropped column and the dropped index are gone outright, and FLASHBACK TABLE ... TO SCN is no way back to them either, because "Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table" -- with "modifying or dropping a column" named in the restrictions.
Even for the table drop the retrieval is partial and conditional. The recycle bin has to be enabled (the recyclebin initialization parameter; on by default), the table "must have resided in a locally managed tablespace other than the SYSTEM tablespace", and nothing may have purged it since. What comes back carries recycle-bin names for its indexes, triggers and constraints -- and "FLASHBACK TABLE TO BEFORE DROP does not recover referential constraints", the retrieved constraints excluding "referential integrity constraints that reference other tables". Those are exactly the ones this driver's CASCADE CONSTRAINTS had just dropped. (Quotations: SQL Language Reference 19c, FLASHBACK TABLE.)
Recovery is a new diff
The recovery path after a half-applied upgrade is to run it again: it is diff followed by apply, and diff introspects the live database on every call, so the second run's diff is computed against the state the first run actually left behind and contains only the remainder. That is the forward-progress story core's notice points at.
What of this is verified
The temp-user orchestration is pinned offline in t/58-deploy-temp-user.t with doubles for the handles: the order (create user, grants, install, introspect, drop), the drop on a failed deploy, the warning for a drop that failed, the refusal before anything is created when CREATE USER or DROP USER is missing, the DBIO_TMP_ name, and that the connection of the schema is not written to. Section 8 of that file pins a schema with tables in other schemas: a temp user for each, the qualifiers rewritten, the models merged back under the real names, every user dropped on every path. t/84-ddl-cross-schema-live.t runs it against a server with a second user.
The behaviour is measured against a server, Oracle Database 23ai Free (23.26.3) over DBD::Oracle 1.95. t/54-deploy-live.t: what install leaves in a schema, that it commits a transaction the caller has open; that diff and upgrade leave a transaction of the caller open and uncommitted, leave no DBIO_TMP_ user behind, also when the deploy fails; that a diff of a schema against the state it already has is empty and that a missing column is added by upgrade; and, kept as the measured reason for ADR 0010, the statements of the old savepoint strategy replayed by hand (ORA-01086). t/55-diff-index-live.t: what DBIO::Oracle::Diff makes of a new table: A to B applied gives B, and upgrade of a DBIO schema (two tables, a foreign key, an index, a unique constraint, a sequence) over an empty schema and over a schema that has one of the tables gives what install gives.
The core seam is verified against core's source: which branch of "_execute_ddl" in DBIO::Deploy::Base the declared capability selects. t/52-deploy-transactional-ddl.t pins that half offline.
What is said about the recycle bin further up is not measured. It is documented behaviour with its source named (SQL Language Reference 19c COMMIT and FLASHBACK TABLE; 2 Day Developer's Guide 19c, About Data Definition Language (DDL) Statements; Administrator's Guide 19c, Using Flashback Drop and Managing the Recycle Bin), and no test of this driver has run a Flashback statement.
METHODS
_install_ddl
The install DDL that core's "install" in DBIO::Deploy::Base (what $schema->deploy runs) executes, after a check: a table in another schema (table('OWNX.item'), see "TABLES IN ANOTHER SCHEMA" in DBIO::Oracle::DDL) is created in that schema, which is a user, and the driver creates no user. Every schema the install names is looked up in all_users (by the exact name, like the quoted statements); when one is missing a DBIO exception names the user and a table of the declaration that wants it, says it refuses, and nothing has been sent to the server. When Oracle holds a user of the upper-case spelling (a user made unquoted), the message says so.
_check_before_apply
$self->_check_before_apply($diff);
Seam of "apply" in DBIO::Deploy::Base, run once the diff is released ("allow_destructive" in DBIO::Deploy::Base) and before the first statement: looks at the rows of the live tables ("_refuse_unfillable"), then goes on as the base does. apply and upgrade themselves are core's (ADR 0080).
_refuse_unfillable
$self->_refuse_unfillable($diff, $dbh);
Refuses -- a DBIO::Exception naming table and column, nothing sent -- what Oracle would refuse for the rows a table holds half way through the diff, since Oracle commits every DDL statement and the ones before it cannot be taken back:
a
NOT NULLcolumn without a default that the diff adds to a table that holds rows (ORA-01758);a column that becomes
NOT NULLin a table that has aNULLin it (ORA-02296);a column that holds values and changes to a datatype Oracle does not convert (ORA-01439; ORA-22858 and ORA-22859 for the LOB types). Oracle converts, with values in the column,
CHARandVARCHAR2into each other and intoNCHARandNVARCHAR2, those two into each other,DATEandTIMESTAMPinto each other andNUMBERintoFLOAT; every other change between the types the driver writes is refused. A type that is not among them is left to the server.
An empty table, a table with no NULL in the column and a column that holds only NULL pass.
_new_introspect
my $introspect = $self->_new_introspect($dbh, %args);
Overrides "_new_introspect" in DBIO::Deploy::Base to hand further arguments (the schemas of "schemas" in DBIO::Oracle::Introspect) to the introspection.
_introspect_current
The model of the live schema. When the declaration has tables in other schemas (a source OWNX.item) those schemas are read as well, so that their tables are in the model as OWNX.item, the keys of the model of the desired state (see "_build_target_model"). The connected user reads them through the all_* views, as far as it may see them.
_build_target_model
Builds the desired state in a temporary user (see "The desired state: a temporary user"). A schema with tables in other schemas (a source OWNX.item) gets one more temporary user for each of those schemas, DBIO_TMP_<pid>_<n>_<k> (k counts them in the order of their names), because a temporary user cannot hold a table of another user and the driver makes no user of the real name. See "Tables in other schemas".
_deploy_and_introspect_temp_schemas
my $model = $self->_deploy_and_introspect_temp_schemas($main, { OWNX => $temp_user });
The install DDL of a schema with foreign schemas, built with the temp users as qualifiers, run statement by statement as the temp user it writes to, and the merged model of all of them (see "_build_target_model"). All connections are closed before it returns or raises.
temp_db_prefix
DBIO_TMP_: the prefix of the temporary users, and what a leftover one is found by (SELECT username FROM all_users WHERE username LIKE 'DBIO\_TMP\_%' ESCAPE '\'). Overrides "temp_db_prefix" in DBIO::Deploy::Base::TempDatabase.
_temp_connect_info
my ($dsn, $user, $pass) = $self->_temp_connect_info($temp_user);
The DSN of the storage, with the name and the password of the temporary user. Overrides "_temp_connect_info" in DBIO::Deploy::Base::TempDatabase; with the argument probe it answers the connect info of the storage itself, which is how the admin connection gets its credentials.
_create_temp_db
my $temp_user = $self->_create_temp_db($dbh);
$self->_create_temp_db($dbh, $name); # a user of that name
Makes the temporary user ("The desired state: a temporary user") on a connection of its own and returns its name. $dbh, the connection of the schema, is not used. Throws a DBIO exception naming CREATE USER or DROP USER before anything is created when the connected user lacks the privilege; when a step after CREATE USER fails the user is dropped again before the error is raised.
_drop_temp_db
$self->_drop_temp_db($dbh, $temp_user);
DROP USER ... CASCADE on the connection of its own, which is closed afterwards in any case. A failure is raised (with the passwords masked) for "_build_target_model" in DBIO::Deploy::Base::TempDatabase to warn about.
Tables in other schemas
The install DDL is written with each foreign schema replaced by its temporary user ("%SCHEMA_MAP" in DBIO::Oracle::Identifier), and every statement runs on the connection of the user whose schema it writes to: the temporary users need none of the
ANYprivileges the real install needs.A temporary user sees only its own tables, so each is introspected on its own connection. The models are merged under the keys the live introspection uses (
tablefor the connected schema,OWNX.tablefor a foreign one) and the temporary names are written back as the real schemas, in table keys, theschemaof a table, foreign keys and sequences: a diff of the schema against itself is empty.Every temporary user is dropped, also when the deploy fails; a drop that fails is warned about with the name of that user, the others are still dropped.
Foreign keys between schemas. The install DDL and
applywrite noGRANT: a foreign key from a table of one user to a table of another needsREFERENCESon the parent for the owner of the child, and without it the server's error (ORA-01031) reaches the caller. That grant is the schema owner's to give. Only among its own temporary users does this class issueGRANT REFERENCES ON parent TO child_user, by the owner of the parent and before the foreign key, so that the desired state can be built at all; the diff then compares the declaration with the live schema and does not know whether the live owner has granted the right.A schema named like the connected user is its own schema, not a foreign one: it gets no temporary user of its own, its tables are made in the main temporary user, and the model holds them bare, as the live introspection of the connected user reads its own tables.
SEE ALSO
DBIO::Oracle - schema component
DBIO::Oracle::DDL - generates DDL
DBIO::Oracle::Introspect - reads live database state
DBIO::Oracle::Diff - compares two introspected models
DBIO::Deploy::Base - inherited orchestration
AUTHOR
DBIO & DBIx::Class Authors
COPYRIGHT AND LICENSE
Copyright (C) 2026 DBIO Authors Portions Copyright (C) 2005-2025 DBIx::Class Authors Based on DBIx::Class, heavily modified.
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.