(Finally continuing this old thread.)
The suggested fix does not seem to work. Here is an example:
1. To build the tables, create a schema test and run the following
code:
from sqlalchemy import *
metadata = BoundMetaData("...")
class Subject(object):
def __str__(self):
return "Subject(%s)" % str(self.id)
subject = Table("subject", metadata,
Column("id", Integer, primary_key=True),
schema="public")
mapper(Subject, subject)
class Referer(object):
def __str__(self):
return "Referer(%s)" % str(self.id)
ForeignKey("subject.id")),
referer = Table("referer", metadata,
Column("id", Integer, primary_key=True),
Column("ref", Integer,
schema="test")
mapper(Referer, referer,
properties={"subject": relation(Subject),
"_ref": referer.c.ref,})
#metadata.engine.echo = True
metadata.create_all()
This fails with the following exception:
Traceback (most recent call last):
File "cross_schema1.py", line 33, in ?
metadata.create_all()
[...]
sqlalchemy.exceptions.ArgumentError: Table 'None.subject' not defined
The problem can be resolved by writing the ForeignKey more explicitly:
ForeignKey("public.subject.id")
2. Now try to use the tables with the following similar code: (The
main difference is autoload=True instead of the column definitions.)
from sqlalchemy import *
metadata = BoundMetaData("...")
class Subject(object):
def __str__(self):
return "Subject(%s)" % str(self.id)
subject = Table("subject", metadata,
schema="public", autoload=True)
mapper(Subject, subject)
class Referer(object):
def __str__(self):
return "Referer(%s)" % str(self.id)
referer = Table("referer", metadata,
schema="test", autoload=True)
mapper(Referer, referer,
properties={"subject": relation(Subject),
"_ref": referer.c.ref,})
#metadata.engine.echo = True
session = create_session()
query = session.query(Subject)
print query.list()
query = session.query(Referer)
print query.list()
This fails with the following exception:
Traceback (most recent call last):
File "cross_schema2.py", line 31, in ?
query = session.query(Subject)
[...]
sqlalchemy.exceptions.ArgumentError: Error determining primary and/or
secondary join for relationship 'Referer.subject (Subject)'. If the
underlying error cannot be corrected, you should specify the
'primaryjoin' (and 'secondaryjoin', if there is an association table
present) keyword arguments to the relation() function (or for
backrefs, by specifying the backref using the backref() function with
keyword arguments) to explicitly specify the join conditions. Nested
error is "Can't find any foreign key relationships between 'referer'
and 'subject'"
Best regards
Klaus
On Jun 19, 9:44 am, [EMAIL PROTECTED] wrote:
> This looks like a good solution. I'll need some time to provide a test
> case, however.
>
> If the change breaks existing code, how are cross-schema references
> supposed to be handled?
>
> Best regards
> Klaus
>
> On 18 Jun., 21:54, Michael Bayer <[EMAIL PROTECTED]> wrote:
>
> > On Jun 18, 2007, at 4:25 AM,[EMAIL PROTECTED] wrote:
>
> > > In my first experiments with elixir I noticed that sqlalchemy doesn't
> > > handle foreign keys correctly on autoloaded tables. This has to to
> > > with schema handling in the postgresql driver. A foreign key
> > > referencing a table in the public schema from "outside" gets the table
> > > name without the schema part and thus locates the table in its own
> > > schema. I've appended a trivial fix below.
>
> > im afraid this patch breaks several postgres reflection unit tests,
> > since the "schema" attribute of Table is assumed to be of the default
> > schema if not present. by making it present here, it fails to locate
> > tables within its own metadata. i found when trying to create the
> > test case for this issue it required explicitly stating the default
> > schema name as the "schema" of the "inside" table. that is also a bug.
>
> > the patch that fixes the all issues for the test I was able to create
> > is:
>
> > Index: lib/sqlalchemy/schema.py
> > ===================================================================
> > --- lib/sqlalchemy/schema.py (revision 2742)
> > +++ lib/sqlalchemy/schema.py (working copy)
> > @@ -701,7 +701,7 @@
> > raise exceptions.ArgumentError("Invalid foreign
> > key column specification: " + self._colspec)
> > if m.group(3) is None:
> > (tname, colname) = m.group(1, 2)
> > - schema = parenttable.schema
> > + schema = None
> > else:
> > (schema,tname,colname) = m.group(1,2,3)
> > table = Table(tname, parenttable.metadata,
> > mustexist=True, schema=schema)
>
> > meaning, if you say ForeignKey("sometable.somecolumn") for a
> > particular Column, the "schema" is assumed to be the "default"
> > schema, not the "schema" specified for the Table which contains the
> > ForeignKey object. this means creating a ForeignKey between two
> > tables A and B, both with schema="myschema", would have to look like
> > ForeignKey("myschema.a.somecol"), even though both tables are in the
> > same "myschema" schema. Im OK with that but not sure how disruptive
> > this change would be.
>
> > if you can provide a test case illustrating your scenario (using
> > Table/MetaData/Engine objects only; no elixir classes please), that
> > would help greatly.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
