Attached is a possible implementation for DBD::Oracle.
It (currently) supports the extended behavior of SQL/CLI, i.e.
- the UNIQUE_OR_PRIMARY field is present and
- the result set contains all candidate keys
Of course, it is easy to reduce that to pure ODBC behavior, but
- if only primary keys are referenced, the method already behaves
in accordance with ODBC
- if alternate keys are referenced, the extended behavior
becomes all-important
So I think, we have a good compromise.
The method parameters are directly mapped to restriction conditions
of the query, no special tests are (currently) implemented.
That means, for example, the result set contains all accessible FK
information of the database, if no parameters are given.
However, this straightforward way is not exactly what the standard
specs say. Especially, some parameter constellations with undefined
values are not allowed in ODBC:
HY009: Invalid use of null pointer
and raise the exception 'invalid use of null pointer' in SQL/CLI.
Of course, it is easy to add these assertions.
Other minor differences between DBD::Oracle and Oracle's ODBC driver
are:
- DBD::Oracle does not emulate Bug #2008554.
- The UK_TABLE_CAT field is NULL, not ''.
Currently, the empty string is equivalent to NULL, but this is a
deprecated feature.
- The UPDATE_RULE is 3 ('NO ACTION'), not NULL.
Even though this action is not allowed in a DDL statement, it is
enforced by the DBMS.
- The DELETE_RULE is prepared for future extensions, see decode().
Steffen
*** DBD-Oracle-1.12/Oracle.pm Fri Aug 31 18:27:18 2001
--- Oracle.pm Mon Oct 08 21:54:59 2001
***************
*** 442,447 ****
--- 442,502 ----
$sth;
}
+ sub foreign_key_info {
+ my $dbh = shift;
+ my $attr = ( ref $_[0] eq 'HASH') ? $_[0] : {
+ 'UK_TABLE_SCHEM' => $_[1],'UK_TABLE_NAME ' => $_[2]
+ ,'FK_TABLE_SCHEM' => $_[4],'FK_TABLE_NAME ' => $_[5] };
+ my $Sql = <<'SQL'; # XXX: DEFERABILITY
+ SELECT *
+ FROM
+ (
+ SELECT to_char( NULL ) UK_TABLE_CAT
+ , uk.OWNER UK_TABLE_SCHEM
+ , uk.TABLE_NAME UK_TABLE_NAME
+ , uc.COLUMN_NAME UK_COLUMN_NAME
+ , to_char( NULL ) FK_TABLE_CAT
+ , fk.OWNER FK_TABLE_SCHEM
+ , fk.TABLE_NAME FK_TABLE_NAME
+ , fc.COLUMN_NAME FK_COLUMN_NAME
+ , uc.POSITION ORDINAL_POSITION
+ , 3 UPDATE_RULE
+ , decode( fk.DELETE_RULE, 'CASCADE', 0, 'RESTRICT', 1, 'SET NULL', 2, 'NO
+ACTION', 3, 'SET DEFAULT', 4 )
+ DELETE_RULE
+ , fk.CONSTRAINT_NAME FK_NAME
+ , uk.CONSTRAINT_NAME UK_NAME
+ , to_char( NULL ) DEFERABILITY
+ , decode( uk.CONSTRAINT_TYPE, 'P', 'PRIMARY', 'U', 'UNIQUE')
+ UNIQUE_OR_PRIMARY
+ FROM ALL_CONSTRAINTS uk
+ , ALL_CONS_COLUMNS uc
+ , ALL_CONSTRAINTS fk
+ , ALL_CONS_COLUMNS fc
+ WHERE uk.OWNER = uc.OWNER
+ AND uk.CONSTRAINT_NAME = uc.CONSTRAINT_NAME
+ AND fk.OWNER = fc.OWNER
+ AND fk.CONSTRAINT_NAME = fc.CONSTRAINT_NAME
+ AND uk.CONSTRAINT_TYPE IN ('P','U')
+ AND fk.CONSTRAINT_TYPE = 'R'
+ AND uk.CONSTRAINT_NAME = fk.R_CONSTRAINT_NAME
+ AND uk.OWNER = fk.R_OWNER
+ AND uc.POSITION = fc.POSITION
+ )
+ WHERE 1 = 1
+ SQL
+ my @BindVals = ();
+ while ( my ( $k, $v ) = each %$attr ) {
+ if ( $v ) {
+ $Sql .= " AND $k = ?\n";
+ push @BindVals, $v;
+ }
+ }
+ $Sql .= " ORDER BY UK_TABLE_SCHEM, UK_TABLE_NAME, FK_TABLE_SCHEM,
+FK_TABLE_NAME, ORDINAL_POSITION\n";
+ my $sth = $dbh->prepare( $Sql ) or return undef;
+ $sth->execute( @BindVals ) or return undef;
+ $sth;
+ }
+
sub type_info_all {
my ($dbh) = @_;
my $names = {
***************
*** 898,903 ****
--- 953,991 ----
PK_NAME contains a system generated name with the form SYS_Cn.
The result set is ordered by TABLE_SCHEM, TABLE_NAME, KEY_SEQ.
+
+ An identifier is passed I<as is>, i.e. as the user provides or
+ Oracle returns it.
+ See L</table_info()> for more detailed information.
+
+ =head2 C<foreign_key_info()>
+
+ This method (currently) supports the extended behavior of SQL/CLI, i.e. the
+ result set contains foreign keys that refer to primary B<and> alternate keys.
+ The field UNIQUE_OR_PRIMARY distinguishes these keys.
+
+ Oracle does not support catalogs, so C<$pk_catalog> and C<$fk_catalog> are
+ ignored as selection criteria (in the new style interface).
+ The UK_TABLE_CAT and FK_TABLE_CAT fields of a fetched row are always
+ NULL (undef).
+ See L</table_info()> for more detailed information.
+
+ If the primary or foreign key constraints were created without an identifier,
+ UK_NAME or FK_NAME contains a system generated name with the form SYS_Cn.
+
+ The UPDATE_RULE field is always 3 ('NO ACTION'), because Oracle (currently)
+ does not support other actions.
+
+ The DELETE_RULE field may contain wrong values. This is a known Bug (#1271663)
+ in Oracle's data dictionary views. Currently (as of 8.1.7), 'RESTRICT' and
+ 'SET DEFAULT' are not supported, 'CASCADE' is mapped correctly and all other
+ actions (incl. 'SET NULL') appear as 'NO ACTION'.
+
+ The DEFERABILITY field is always NULL, because this columns is
+ not present in the ALL_CONSTRAINTS view of older Oracle releases.
+
+ The result set is ordered by UK_TABLE_SCHEM, UK_TABLE_NAME, FK_TABLE_SCHEM,
+ FK_TABLE_NAME, ORDINAL_POSITION.
An identifier is passed I<as is>, i.e. as the user provides or
Oracle returns it.
