I think this may partly illustrate the problem (or am I doing something
silly?):
CREATE SCHEMA content;
CREATE SEQUENCE content.node_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
CREATE TABLE content.node
(
id int8 NOT NULL DEFAULT nextval('content.node_id_seq'::regclass),
parent_id int8,
next_sibling_id int8,
prev_sibling_id int8,
CONSTRAINT node_pkey PRIMARY KEY (id),
CONSTRAINT node_next_sibling_id_fkey FOREIGN KEY (next_sibling_id)
REFERENCES content.node (id) ON UPDATE CASCADE ON DELETE SET NULL,
CONSTRAINT node_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES
content.node (id) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT node_prev_sibling_id_fkey FOREIGN KEY (prev_sibling_id)
REFERENCES content.node (id) ON UPDATE CASCADE ON DELETE SET NULL
)
WITHOUT OIDS;
CREATE SEQUENCE content.tree_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
CREATE TABLE content.tree
(
id int8 NOT NULL DEFAULT nextval('content.tree_id_seq'::regclass),
root_id int8,
context text,
CONSTRAINT tree_pkey PRIMARY KEY (id),
CONSTRAINT tree_root_id_fkey FOREIGN KEY (root_id) REFERENCES
content.node (id) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT tree_context_key UNIQUE (context)
)
WITHOUT OIDS;
--8<------------------------------------------------------
import sqlalchemy.mods.threadlocal
from sqlalchemy import *
engine = create_engine('postgres://user:[EMAIL PROTECTED]/test', echo=True)
metadata = BoundMetaData(engine)
session = objectstore.session
class Node(object):
pass
class Tree(object):
pass
node_table = Table('node', metadata, schema='content', autoload=True)
node_mapper = mapper(Node, node_table, properties={
'parent': sqlalchemy.relation(
Node,
lazy=True,
primaryjoin=node_table.c.parent_id==node_table.c.id,
foreignkey=node_table.c.id,
uselist=False
),
'_children': sqlalchemy.relation(
Node,
primaryjoin=node_table.c.id==node_table.c.parent_id,
lazy=True,
cascade='all'
),
'previous_sibling': sqlalchemy.relation(
Node,
primaryjoin=node_table.c.prev_sibling_id==node_table.c.id,
foreignkey=node_table.c.id,
lazy=True,
uselist=False
),
'next_sibling': sqlalchemy.relation(
Node,
primaryjoin=node_table.c.next_sibling_id==node_table.c.id,
foreignkey=node_table.c.id,
lazy=True,
uselist=False,
post_update=True
)
})
tree_table = Table('tree', metadata, schema='content', autoload=True)
tree_mapper = mapper(Tree, tree_table)
print session.query(Tree).select_by(context=None)
--8<------------------------------------------------------
Gives this traceback:
Traceback (most recent call last):
File "test.py", line 50, in ?
print session.query(Tree).select_by(context=None)
File "build/bdist.linux-i686/egg/sqlalchemy/orm/session.py", line 180,
in query
File "build/bdist.linux-i686/egg/sqlalchemy/orm/session.py", line 452,
in class_mapper
File "build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py", line 1426,
in class_mapper
File "build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py", line 166,
in compile
File "build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py", line 184,
in _compile_all
File "build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py", line 403,
in _initialize_properties
File "build/bdist.linux-i686/egg/sqlalchemy/orm/mapper.py", line 1169,
in init
File "build/bdist.linux-i686/egg/sqlalchemy/orm/properties.py", line
249, in do_init
File "build/bdist.linux-i686/egg/sqlalchemy/orm/properties.py", line
302, in _get_direction
Working with Postgres 8.1.4, and SA rev 1869.
Michael Bayer wrote:
> I just noticed this comment thats been sitting on the wiki for a few
> months:
>
> If using autoload=True when defining your metadata, be sure your FK
> constraint names are unique to the schema . PostgreSQL scopes FK
> constraint names to the table, but SQLAlchemy loads them from the SQL
> standard information_schema, which is designed for schema-scoped
> constraint names.
>
> which is news to me; the query used to reflect foreign keys takes
> table name into account, and a unit test which reflects anonymously-
> created foreign keys from two different tables works fine (and always
> has...). can someone illustrate this problem for me ?
>
>
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job easier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
