Oracle foreign key reflection is broken

[Former user]

The reflection code assumes that the remote column of a referential integrity constraint is always the first primary key of the remote table. This seems dubious.

I get an IndexError on primary_key when I try to autoload tables with ref. int. contstraints. Maybe the primary keys aren't loaded when the code needs them?

Anyway it seemed better to just remove the assumption and specify the remote column by name.

This works for me on Oracle 9.2.0.1

constraintSQL = """SELECT
  ac.constraint_name,
  ac.constraint_type,
  LOWER(loc.column_name) AS local_column,
  rem.table_name as remote_table,
  LOWER(rem.column_name) AS remote_column
FROM all_constraints ac,
  all_cons_columns loc,
  all_cons_columns rem
WHERE ac.table_name = :table_name
and ac.constraint_type in ('R','P')
and ac.owner = loc.owner
and ac.constraint_name = loc.constraint_name
and ac.r_owner = rem.owner(+)
and ac.r_constraint_name = rem.constraint_name(+)
-- order multiple primary keys correctly
order by ac.constraint_name, loc.position"""

. . .

class OracleSQLEngine(ansisql.ANSISQLEngine):
    . . .
    def reflecttable(self, table):
        . . .
        c = self.execute(constraintSQL, {'table_name': table.name})
        while True:
            row = c.fetchone()
            if row is None:
                break
            (cons_name, cons_type, local_column, remote_table, remote_column) = row
            if cons_type == 'P': 
                table.c[local_column](local_column)._set_primary_key()
            elif cons_type == 'R':
                table.c[local_column](local_column).append_item(
                    schema.ForeignKey(Table(remote_table,
                                            self,
                                            autoload=True).c[remote_column](remote_column)
                                      )
                    )

[Mike Bayer]

hi there -

in the interests of time and accuracy, please provide me with this change in patch format and ill commit.

[Former user]

unified diff for #105 also fixes #103

[Mike Bayer]

applied in changeset:1150, unit tests pass, thanks !

[Former user]

What about the case of two tables with same name but different columns:

connect a/a

create table ble (text_A varchar2(20)) ;
grant select on ble to b ;

connect b/b

create table ble (numB integer) ;

ble = Table('ble', autoload=True) will have two columns: text_a and numb!

It's trivial to restrict query to owner==logged_user, but ticket #103 is against that. So what now?

[Mike Bayer]

we'll probably have to add an 'owner' attribute to Table to support databases where multiple tables exist in one schema for different owners. possibly modify oracle reflection to detect two different owner names and raise an exception if "owner" is not specified in such a case. want to take a crack at this ?

[Former user]

adds owner keyword and fix Oracle reflection in situation of multiple tables with same name

[Former user]

I didn't get much into how Table keywords work, and exceptions raised are just first thing that seemed suitable (not to mention lame exception messages) -- but it passes all the tests (that Oracle XE pass). Once I figure out how to add unit tests, I will add them too.

[Mike Bayer]

committed the latest patch in changeset:1726 which works, plus a modification to support the latest composite foreign key functionality via #76.

[Former user]

The 81f6188b400b7b65819b738b3621378874dfef35 have broken some of my code. For example:

role = Table('role', meta, autoload=True)
print role.c.contact_id.foreign_key.column.table.name

fails with:

Traceback (most recent call last):
  File "C:\test\mfis\profiles\test_model.py", line 125, in ?
    test_meta()
  File "C:\test\mfis\profiles\test_model.py", line 13, in test_meta
    print role.c.contact_id.foreign_key.column.table.name
  File "c:\gnu\src\sqlalchemy\lib\sqlalchemy\schema.py", line 505, in <lambda>
    column = property(lambda s: s._init_column())
  File "c:\gnu\src\sqlalchemy\lib\sqlalchemy\schema.py", line 494, in _init_column
    table = Table(tname, parenttable.metadata, mustexist=True, schema=schema)
  File "c:\gnu\src\sqlalchemy\lib\sqlalchemy\schema.py", line 87, in __call__
    raise exceptions.ArgumentError("Table '%s.%s' not defined" % (schema, name))

sqlalchemy.exceptions.ArgumentError: Table 'None.contact' not defined

if the 'contact' table was not loaded ahead of time.

The reason seems to be that the ForeignKeyConstraint does automatically loads related table. Following fixes it for me but is it a good fix?

Index: oracle.py

===================================================================

--- oracle.py   (revision 1729)

+++ oracle.py   (working copy)

@@ -237,6 +237,7 @@

                    fk = ([[](],))
                    fks[cons_name](cons_name) = fk
                 refspec = ".".join([remote_column](remote_table,))
+                schema.Table(remote_table, table.metadata, autoload=True)
                 if local_column not in fk[0](0):
                     fk[0](0).append(local_column)
                 if refspec not in fk[1](1):

[Mike Bayer]

youre absolutely right, this was newly introduced bugginess since i did away with the autoload of the foreign key's table when i added the composite foreign key stuff. i modified the reflection test to simulate this condition (i.e. reference the foreign key before youve explicitly loaded the remote table), revealed the issue with postgres, mysql and oracle and applied similar versions of your fix to all three, in changeset:1737. nice job !

[Mike Bayer]

Removing milestone: 0.3.0 (automated comment)