Autoload and ManyToMany

[esvist]

Spent several hours reading available documentation and searching
samples, but no result...

Having schema (Oracle):

CREATE TABLE user_ (
id NUMBER(16)
CONSTRAINT pk_user PRIMARY KEY,
name VARCHAR2(32) NOT NULL
);

CREATE TABLE case (
id NUMBER(16)
CONSTRAINT pk_case PRIMARY KEY,
name VARCHAR2(32) NOT NULL
);

CREATE TABLE case2user (
case_id NUMBER(16) NOT NULL
CONSTRAINT fk_c2u_case REFERENCES case (id),
user_id NUMBER(16) NOT NULL
CONSTRAINT fk_c2u_user REFERENCES user_ (id),

CONSTRAINT pk_case2user PRIMARY KEY (case_id, user_id)
);

and mapping it as:

class User(Entity):
using_options(
tablename='USER_',
autoload=True
)
cases = ManyToMany(
'Case',
tablename='CASE2USER',
remote_side='case_id',
local_side='user_id'
)


class Case(Entity):
using_options(
tablename='CASE',
autoload=True
)
users = ManyToMany(
'User',
tablename='CASE2USER',
remote_side='user_id',
local_side='case_id'
)

setup_all() passes ok

but

User.query.all()

results in

<class 'sqlalchemy.exc.ArgumentError'>: Could not determine join
condition between parent/child tables on relation Case.users. Specify
a 'primaryjoin' expression. If this is a many-to-many relation,
'secondaryjoin' is needed as well.

According to Elixir API documentation primaryjoin and secondaryjoin
should be determined automatically in this case. Any idea where I'm
wrong?

Many thanks

[Gaetan de Menten]

You are not wrong anywhere I think. This code works fine here on
another DB. This is probably a bug somewhere due to the strange casing
used by Oracle. My guess is that it's a problem somewhere in SA, since
on non self-referential ManyToMany relationships, Elixir doesn't
generate any join conditions because SA can (usually) detect them
automatically in that case.

The next step would be to try to produce an SA-only test case, and if
the problem is present there too, add a ticket on SA's trac. If you
have any trouble producing the SA test-case, don't hesitate to ask for
help. Can't test it myself though since I don't have access to an
Oracle DB at the moment.

--
Gaëtan de Menten
http://openhex.org

[esvist]

Is this what you ment?

from sqlalchemy import *
from sqlalchemy.orm import *

db = create_engine('CONNECT STRING HERE')
db.echo = True

meta = MetaData()
meta.bind = db

user = Table('USER_', meta, autoload=True)
case = Table('CASE', meta, autoload=True)
case2user = Table('CASE2USER', meta, autoload=True)

class Case(object):
pass
class User(object):
pass

mapper(Case, case, properties={
'users':relation(User, secondary=case2user)
})

mapper(User, user)

Here

Case.users.select()

gives me the same exception

<class 'sqlalchemy.exc.ArgumentError'>: Could not determine join
condition between parent/child tables on relation Case.users. Specify
a 'primaryjoin' expression. If this is a many-to-many relation,
'secondaryjoin' is needed as well.

, but I'm not sure if the sample is equivalent and correct...

Thanks for your help

On Oct 1, 9:23 am, "Gaetan de Menten" <gdemen...@gmail.com> wrote:

[Gaetan de Menten]

Yes, this is what I meant. It would help if you provided a way to
create those tables, so that the test case can be run as-is. It seems
correct to me, except the last line:

> Case.users.select()

seem incorrect (though that might be a new usage of SA I'm not aware
of). In any case, it certainly not the equivalent of
"User.query.all()"

The correct equivalent would be:

session = sessionmaker()()
session.query(User).all()

The last step is to create a ticket on SA's trac with your script attached.

[esvist]

On Oct 2, 9:45 am, "Gaetan de Menten" <gdemen...@gmail.com> wrote:

I changed the query statement - same result...

Before posting a ticket, could you verify, this would run on any other
database (not Oracle) correctly?

Thanks for your help

The complete test case is:

CREATE TABLE user_ (
id NUMBER(16)
CONSTRAINT pk_user PRIMARY KEY,
name VARCHAR2(32) NOT NULL
);

CREATE TABLE case (
id NUMBER(16)
CONSTRAINT pk_case PRIMARY KEY,
name VARCHAR2(32) NOT NULL
);

CREATE TABLE case2user (
case_id NUMBER(16) NOT NULL
CONSTRAINT fk_c2u_case REFERENCES case (id),
user_id NUMBER(16) NOT NULL
CONSTRAINT fk_c2u_user REFERENCES user_ (id),

CONSTRAINT pk_case2user PRIMARY KEY (case_id, user_id)
);

from sqlalchemy import *
from sqlalchemy.orm import *

db = create_engine('oracle://sf:sf.@svistcenter.svistnet/xe')

db.echo = True

meta = MetaData()
meta.bind = db

user = Table('USER_', meta, autoload=True)
case = Table('CASE', meta, autoload=True)
case2user = Table('CASE2USER', meta, autoload=True)

class Case(object):
pass
class User(object):
pass

mapper(User, user)

mapper(Case, case, properties={
'users':relation(User, secondary=case2user)
})

session = sessionmaker()()

session.query(Case).all()

[Gaetan de Menten]

On Thu, Oct 2, 2008 at 10:26 AM, esvist <jan.k...@gmail.com> wrote:

> I changed the query statement - same result...
>
> Before posting a ticket, could you verify, this would run on any other
> database (not Oracle) correctly?

Seems like the issue is not specific to Oracle but rather a case
issue, as the attached test case fails with the same error you have.

[esvist]

On Oct 2, 11:44 am, "Gaetan de Menten" <gdemen...@gmail.com> wrote:

> On Thu, Oct 2, 2008 at 10:26 AM, esvist <jan.kabr...@gmail.com> wrote:
> > I changed the query statement - same result...
>
> > Before posting a ticket, could you verify, this would run on any other
> > database (not Oracle) correctly?
>
> Seems like the issue is not specific to Oracle but rather a case
> issue, as the attached test case fails with the same error you have.
>
> --
> Gaëtan de Mentenhttp://openhex.org
>

>  test_m2m_case.py
> 1KViewDownload

I prepared a ticket for SA trac, but before clicking 'submit' I
decided to verify everything once again... and solved the problem:

1) your test case using sqllite failed, since you used
'USER_' (uppercase) in

user = Table('USER_', meta, autoload=True)

but table was created with lowercase name 'user_'

correcting it, test case works fine

2) Oracle allows me to create table named 'case' (no matter there is a
keyword 'case')

- mapping it as

case = Table('CASE', meta, autoload=True)

works fine

- mapping it as
case = Table('case', meta, autoload=True)
doesn't work - exception
<type 'exceptions.AssertionError'>: Couldn't find any column
information for table case
occurs - since Oracle saves table names uppercased and there is not
table 'case' so

Consider that for 'user_' columnt there is no problem - I can map it
as 'user_' or 'USER_' and SA correctly makes it uppercase for
underlying queries to system tables
in both case

It seems to me that there must be some special handling of keywords in
SA.

Using a table named 'case_' instead of 'case' solves the problem.

3) As mentioned in 2) uppercase table names works fine for mapping

user = Table('USER_', meta, autoload=True)

passes ok

but using it with manytomany relations

mapper(Case, case, properties={'users': relation(User,
secondary=case2user)})

causes the exception (mentioned in my original post) for

session.query(Case).all()

The workaround is to use lowercase table names only.


Thanks for assistance