NAME
DBIO::Oracle::Diff - Compare two introspected Oracle models
VERSION
version 0.900007
DESCRIPTION
DBIO::Oracle::Diff compares two introspected Oracle database models (as produced by DBIO::Oracle::Introspect) and produces a list of structured diff operations. These operations can then be rendered to SQL or a human-readable summary.
my $diff = DBIO::Oracle::Diff->new(
source => $current_model,
target => $desired_model,
);
if ($diff->has_changes) {
print $diff->as_sql;
print $diff->summary;
}
Diff operations are ordered: first the foreign keys that are gone, then the unique constraints that are gone, then the views that are gone, then the tables, the columns and the indexes, then the unique constraints that are new, the foreign keys that are new, and the views that are new or changed last (a foreign key to a new unique constraint needs it first, a key must be gone before the table or column it points to, and a view rests on tables and columns that must exist when it is written). Within the tables, creates come before drops (a DROP TABLE is therefore emitted before any column or index operation).
A table that only the target model has is created whole, the way DBIO::Oracle::DDL writes the install: CREATE TABLE with its columns, primary key and unique constraints, then its sequence and trigger (DBIO::Oracle::Diff::Table); its indexes follow with the others (DBIO::Oracle::Diff::Index), and its foreign keys last, after every CREATE TABLE (DBIO::Oracle::Diff::ForeignKey). Applied to a database that holds the source model, the diff gives a database whose introspected model compares equal to the target: a second diff is empty. A table that only the source has is dropped (DROP TABLE ... CASCADE CONSTRAINTS, then DROP SEQUENCE for the sequences the install made for it), and no DROP INDEX follows it: the indexes went with the table, and Oracle refuses the statement (ORA-01418). A dropped column is dropped with CASCADE CONSTRAINTS (a column of a multi-column key cannot be dropped without it, ORA-12991), and the indexes on a dropped column are not dropped by the diff: Oracle drops them with the column, and a DROP INDEX after it is ORA-01418. What the model does not carry is not invented; see "DESCRIPTION" in DBIO::Oracle::Diff::Table for the trigger.
A table key schema.table (what "schemas" in DBIO::Oracle::Introspect gives for a table of another schema) is written qualified by every operation: "OWNX"."t", with the index, sequence and trigger names in the same schema. A foreign key's to_table of that form is qualified likewise.
The foreign keys and the unique constraints of a table both models have are compared and written: ALTER TABLE ... DROP CONSTRAINT by the name of the source model, ADD CONSTRAINT ... FOREIGN KEY or UNIQUE by the name of the target model, and both for one that changed, the drop first (an action is such a change). A foreign key is compared by its columns, the referenced table and columns, on_delete, on_update and is_deferrable, a unique constraint by its columns; the names of the constraints do not count, and a missing section counts as empty. A constraint over a column the target drops goes with the column (CASCADE CONSTRAINTS). Oracle has ON DELETE CASCADE and SET NULL only: a foreign key with an ON UPDATE action or SET DEFAULT is refused with a DBIO::Exception naming the table and the column (DBIO::Oracle::Diff::ForeignKey, DBIO::Oracle::Diff::Unique). on_delete RESTRICT is written as NO ACTION (no clause), as DBIO::Oracle::DDL does, and is no difference from it.
Oracle refuses to drop a unique constraint a foreign key rests on (ORA-02273). A foreign key that stays as it is over a unique constraint the target drops or changes is therefore dropped first and created again, unchanged, after the constraint: the key gone, the constraint gone, the constraint new, the key new. If the target has neither a unique constraint nor the primary key over the columns such a key points to, the diff refuses with a DBIO::Exception naming the table and the columns, before it returns a single operation.
The model carries the definition of a view (the views section, DBIO::Oracle::Introspect::Views), and the diff writes CREATE OR REPLACE VIEW for a view that is new or whose definition changed and DROP VIEW for one that is gone. Definitions are compared after whitespace and a trailing semicolon are normalized; what Oracle rewrites when it stores a view is described in "DESCRIPTION" in DBIO::Oracle::Diff::View.
SEE ALSO
DBIO::Oracle::Deploy - install, and diff and upgrade against a temporary user
DBIO::Oracle::Introspect - produces the models being compared
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.