Re: [sqlalchemy] use_ansi inconsistency with relationship's primaryjoin

2017-09-13 Thread Kent 
Never mind that last silly 0.7 question.  (Your patch is compatible it 
seems.)  (And pretend that you didn't hear any mention of 0.7)

Thanks again for your awesome software!
Kent


On Wednesday, September 13, 2017 at 3:42:55 PM UTC-4, Kent wrote:
>
>
>> dude!it is 2017.   get on this client!   :)   I literally have to 
>> maintain this feature for you personally :). 
>>
>>
>>
> Hahaha!  If you could only feel my pain!  Unfortunately, there are 
> multiple clients still on 8i because they are stuck on it due to their 
> legacy application (which we are working hard to replace and get them on 
> PostgreSQL.)  (So, at least you know the 8i stuff is helping multiple 
> companies :)
>  
>
>> > 
>> > The cleanest approach is specifying that the 'primaryjoin' to the 
>> > relationship in the mapper should include an extra join clause.  I hate 
>> > doing this, but after many other approaches, I've found this is by far 
>> the 
>> > cleanest approach due to bad database design (which I can't control -- 
>> > legacy). 
>> > 
>> > Anyway, the attached script shows an simplified, analogous mock-up, 
>> which 
>> > works correctly when joins are ANSI and incorrectly with 
>> use_ansi=False. 
>> > 
>> > The script demonstrates an inconsistency in use_ansi True vs. False on 
>> > sqlalchemy version 1.1.14 (although my sqlalchemy is older). 
>> > 
>> > In the use_ansi=False SQL, the correct "fix" would be changing the 
>> rendered: 
>> > 
>> > AND bugs_1.deathdate IS NULL 
>> > 
>> > into 
>> > 
>> > AND bugs_1.deathdate(+) IS NULL 
>> > 
>> > This then matches the ANSI join and works on 8i (I've tested it). 
>> > 
>> > Is this something we can fix?  Since the column is on the remote table 
>> and 
>> > specified in the join condition, it really needs "(+)" after the column 
>> name 
>> > in SQL.  This accomplishes the same thing as the ANSI version placing 
>> this 
>> > join condition in the "ON ..." clause instead of the "WHERE". 
>> > 
>> > Alternatively, is there a hack I could use to fix the rendered SQL on 
>> > joinedloads for this particular relationship? 
>>
>> the miracle of Docker means that I now have easy to run Oracle, SQL 
>> Server, etc. databases anywhere I need them so I can quickly confirm 
>> that this works with ansi or not: 
>>
>> mapper(Rock, rocks_table, 
>> properties={ 
>> 'livingbugs': relationship(Bug, 
>> primaryjoin=and_( 
>> bugs_table.c.rockid == rocks_table.c.id, 
>> bugs_table.c.deathdate.op("(+)=")(null()), 
>> )), 
>> }) 
>>
>>
> I'll find a shield to hide behind and then dare to ask "Is there a way to 
> hack that fix on SqlAlchemy 0.7?"
>
> Thanks for looking at this!
> Kent
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] use_ansi inconsistency with relationship's primaryjoin

2017-09-13 Thread Kent 

>
>
> dude!it is 2017.   get on this client!   :)   I literally have to 
> maintain this feature for you personally :). 
>
>
>
Hahaha!  If you could only feel my pain!  Unfortunately, there are multiple 
clients still on 8i because they are stuck on it due to their legacy 
application (which we are working hard to replace and get them on 
PostgreSQL.)  (So, at least you know the 8i stuff is helping multiple 
companies :)
 

> > 
> > The cleanest approach is specifying that the 'primaryjoin' to the 
> > relationship in the mapper should include an extra join clause.  I hate 
> > doing this, but after many other approaches, I've found this is by far 
> the 
> > cleanest approach due to bad database design (which I can't control -- 
> > legacy). 
> > 
> > Anyway, the attached script shows an simplified, analogous mock-up, 
> which 
> > works correctly when joins are ANSI and incorrectly with use_ansi=False. 
> > 
> > The script demonstrates an inconsistency in use_ansi True vs. False on 
> > sqlalchemy version 1.1.14 (although my sqlalchemy is older). 
> > 
> > In the use_ansi=False SQL, the correct "fix" would be changing the 
> rendered: 
> > 
> > AND bugs_1.deathdate IS NULL 
> > 
> > into 
> > 
> > AND bugs_1.deathdate(+) IS NULL 
> > 
> > This then matches the ANSI join and works on 8i (I've tested it). 
> > 
> > Is this something we can fix?  Since the column is on the remote table 
> and 
> > specified in the join condition, it really needs "(+)" after the column 
> name 
> > in SQL.  This accomplishes the same thing as the ANSI version placing 
> this 
> > join condition in the "ON ..." clause instead of the "WHERE". 
> > 
> > Alternatively, is there a hack I could use to fix the rendered SQL on 
> > joinedloads for this particular relationship? 
>
> the miracle of Docker means that I now have easy to run Oracle, SQL 
> Server, etc. databases anywhere I need them so I can quickly confirm 
> that this works with ansi or not: 
>
> mapper(Rock, rocks_table, 
> properties={ 
> 'livingbugs': relationship(Bug, 
> primaryjoin=and_( 
> bugs_table.c.rockid == rocks_table.c.id, 
> bugs_table.c.deathdate.op("(+)=")(null()), 
> )), 
> }) 
>
>
I'll find a shield to hide behind and then dare to ask "Is there a way to 
hack that fix on SqlAlchemy 0.7?"

Thanks for looking at this!
Kent

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] use_ansi inconsistency with relationship's primaryjoin

2017-09-13 Thread Mike Bayer 
https://bitbucket.org/zzzeek/sqlalchemy/issues/4076/oracle-8-non-ansi-joins-needs-to-apply-to

On Wed, Sep 13, 2017 at 2:41 PM, Mike Bayer  wrote:
> this is also a bug.   the operator here shouldn't matter for the "(+)"
> operator to be added, that's easy to fix.  (doing it for 1.2 for now).
>
> On Wed, Sep 13, 2017 at 2:26 PM, Mike Bayer  wrote:
>> On Wed, Sep 13, 2017 at 12:01 PM, Kent  wrote:
>>> I've got a strange relationship on a legacy Oracle 8i database which I need
>>> to support (whether I like it or not).
>>
>> dude!it is 2017.   get on this client!   :)   I literally have to
>> maintain this feature for you personally :).
>>
>>
>>>
>>> The cleanest approach is specifying that the 'primaryjoin' to the
>>> relationship in the mapper should include an extra join clause.  I hate
>>> doing this, but after many other approaches, I've found this is by far the
>>> cleanest approach due to bad database design (which I can't control --
>>> legacy).
>>>
>>> Anyway, the attached script shows an simplified, analogous mock-up, which
>>> works correctly when joins are ANSI and incorrectly with use_ansi=False.
>>>
>>> The script demonstrates an inconsistency in use_ansi True vs. False on
>>> sqlalchemy version 1.1.14 (although my sqlalchemy is older).
>>>
>>> In the use_ansi=False SQL, the correct "fix" would be changing the rendered:
>>>
>>> AND bugs_1.deathdate IS NULL
>>>
>>> into
>>>
>>> AND bugs_1.deathdate(+) IS NULL
>>>
>>> This then matches the ANSI join and works on 8i (I've tested it).
>>>
>>> Is this something we can fix?  Since the column is on the remote table and
>>> specified in the join condition, it really needs "(+)" after the column name
>>> in SQL.  This accomplishes the same thing as the ANSI version placing this
>>> join condition in the "ON ..." clause instead of the "WHERE".
>>>
>>> Alternatively, is there a hack I could use to fix the rendered SQL on
>>> joinedloads for this particular relationship?
>>
>> the miracle of Docker means that I now have easy to run Oracle, SQL
>> Server, etc. databases anywhere I need them so I can quickly confirm
>> that this works with ansi or not:
>>
>> mapper(Rock, rocks_table,
>> properties={
>> 'livingbugs': relationship(Bug,
>> primaryjoin=and_(
>> bugs_table.c.rockid == rocks_table.c.id,
>> bugs_table.c.deathdate.op("(+)=")(null()),
>> )),
>> })
>>
>>
>>
>>
>>
>>>
>>> Thanks very much in advance!
>>> Kent
>>>
>>> --
>>> SQLAlchemy -
>>> The Python SQL Toolkit and Object Relational Mapper
>>>
>>> http://www.sqlalchemy.org/
>>>
>>> To post example code, please provide an MCVE: Minimal, Complete, and
>>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>>> description.
>>> ---
>>> You received this message because you are subscribed to the Google Groups
>>> "sqlalchemy" group.
>>> To unsubscribe from this group and stop receiving emails from it, send an
>>> email to sqlalchemy+unsubscr...@googlegroups.com.
>>> To post to this group, send email to sqlalchemy@googlegroups.com.
>>> Visit this group at https://groups.google.com/group/sqlalchemy.
>>> For more options, visit https://groups.google.com/d/optout.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] use_ansi inconsistency with relationship's primaryjoin

2017-09-13 Thread Mike Bayer 
this is also a bug.   the operator here shouldn't matter for the "(+)"
operator to be added, that's easy to fix.  (doing it for 1.2 for now).

On Wed, Sep 13, 2017 at 2:26 PM, Mike Bayer  wrote:
> On Wed, Sep 13, 2017 at 12:01 PM, Kent  wrote:
>> I've got a strange relationship on a legacy Oracle 8i database which I need
>> to support (whether I like it or not).
>
> dude!it is 2017.   get on this client!   :)   I literally have to
> maintain this feature for you personally :).
>
>
>>
>> The cleanest approach is specifying that the 'primaryjoin' to the
>> relationship in the mapper should include an extra join clause.  I hate
>> doing this, but after many other approaches, I've found this is by far the
>> cleanest approach due to bad database design (which I can't control --
>> legacy).
>>
>> Anyway, the attached script shows an simplified, analogous mock-up, which
>> works correctly when joins are ANSI and incorrectly with use_ansi=False.
>>
>> The script demonstrates an inconsistency in use_ansi True vs. False on
>> sqlalchemy version 1.1.14 (although my sqlalchemy is older).
>>
>> In the use_ansi=False SQL, the correct "fix" would be changing the rendered:
>>
>> AND bugs_1.deathdate IS NULL
>>
>> into
>>
>> AND bugs_1.deathdate(+) IS NULL
>>
>> This then matches the ANSI join and works on 8i (I've tested it).
>>
>> Is this something we can fix?  Since the column is on the remote table and
>> specified in the join condition, it really needs "(+)" after the column name
>> in SQL.  This accomplishes the same thing as the ANSI version placing this
>> join condition in the "ON ..." clause instead of the "WHERE".
>>
>> Alternatively, is there a hack I could use to fix the rendered SQL on
>> joinedloads for this particular relationship?
>
> the miracle of Docker means that I now have easy to run Oracle, SQL
> Server, etc. databases anywhere I need them so I can quickly confirm
> that this works with ansi or not:
>
> mapper(Rock, rocks_table,
> properties={
> 'livingbugs': relationship(Bug,
> primaryjoin=and_(
> bugs_table.c.rockid == rocks_table.c.id,
> bugs_table.c.deathdate.op("(+)=")(null()),
> )),
> })
>
>
>
>
>
>>
>> Thanks very much in advance!
>> Kent
>>
>> --
>> SQLAlchemy -
>> The Python SQL Toolkit and Object Relational Mapper
>>
>> http://www.sqlalchemy.org/
>>
>> To post example code, please provide an MCVE: Minimal, Complete, and
>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>> description.
>> ---
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to sqlalchemy+unsubscr...@googlegroups.com.
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> Visit this group at https://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] use_ansi inconsistency with relationship's primaryjoin

2017-09-13 Thread Mike Bayer 
On Wed, Sep 13, 2017 at 12:01 PM, Kent  wrote:
> I've got a strange relationship on a legacy Oracle 8i database which I need
> to support (whether I like it or not).

dude!it is 2017.   get on this client!   :)   I literally have to
maintain this feature for you personally :).


>
> The cleanest approach is specifying that the 'primaryjoin' to the
> relationship in the mapper should include an extra join clause.  I hate
> doing this, but after many other approaches, I've found this is by far the
> cleanest approach due to bad database design (which I can't control --
> legacy).
>
> Anyway, the attached script shows an simplified, analogous mock-up, which
> works correctly when joins are ANSI and incorrectly with use_ansi=False.
>
> The script demonstrates an inconsistency in use_ansi True vs. False on
> sqlalchemy version 1.1.14 (although my sqlalchemy is older).
>
> In the use_ansi=False SQL, the correct "fix" would be changing the rendered:
>
> AND bugs_1.deathdate IS NULL
>
> into
>
> AND bugs_1.deathdate(+) IS NULL
>
> This then matches the ANSI join and works on 8i (I've tested it).
>
> Is this something we can fix?  Since the column is on the remote table and
> specified in the join condition, it really needs "(+)" after the column name
> in SQL.  This accomplishes the same thing as the ANSI version placing this
> join condition in the "ON ..." clause instead of the "WHERE".
>
> Alternatively, is there a hack I could use to fix the rendered SQL on
> joinedloads for this particular relationship?

the miracle of Docker means that I now have easy to run Oracle, SQL
Server, etc. databases anywhere I need them so I can quickly confirm
that this works with ansi or not:

mapper(Rock, rocks_table,
properties={
'livingbugs': relationship(Bug,
primaryjoin=and_(
bugs_table.c.rockid == rocks_table.c.id,
bugs_table.c.deathdate.op("(+)=")(null()),
)),
})





>
> Thanks very much in advance!
> Kent
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] use_ansi inconsistency with relationship's primaryjoin

2017-09-13 Thread Kent 
I've got a strange relationship on a legacy Oracle 8i database which I need 
to support (whether I like it or not).

The cleanest approach is specifying that the 'primaryjoin' to the 
relationship in the mapper should include an extra join clause.  I hate 
doing this, but after many other approaches, I've found this is by far the 
cleanest approach due to bad database design (which I can't control -- 
legacy).

Anyway, the attached script shows an simplified, analogous mock-up, which 
works *correctly* when joins are ANSI and *incorrectly* with use_ansi=False.

The script demonstrates an inconsistency in use_ansi True vs. False on 
sqlalchemy version 1.1.14 (although my sqlalchemy is older). 

In the use_ansi=False SQL, the correct "fix" would be changing the rendered:

AND bugs_1.deathdate IS NULL

into

AND bugs_1.deathdate(+) IS NULL

This then matches the ANSI join and works on 8i (I've tested it).

Is this something we can fix?  Since the column is on the* remote table *and 
*specified in the join condition*, it really needs "(+)" after the column 
name in SQL.  This accomplishes the same thing as the ANSI version placing 
this join condition in the "ON ..." clause instead of the "WHERE".

Alternatively, is there a hack I could use to fix the rendered SQL on 
joinedloads for this particular relationship?

Thanks very much in advance!
Kent

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
from sqlalchemy import *
from sqlalchemy.orm import *
from datetime import date


# if use_ansi=True, this script succeeds
# if False, this script fails

use_ansi = False
#use_ansi = True

engine = create_engine('oracle://kent:kent@localhost:1521/xe', use_ansi=use_ansi, echo=True) 

metadata = MetaData(engine)
Session = sessionmaker(bind=engine)

# a rock has many bugs

rocks_table = Table("rocks", metadata,
Column("id", Integer, primary_key=True),
)

bugs_table = Table("bugs", metadata,
Column("id", Integer, primary_key=True),
Column("rockid", Integer, ForeignKey('rocks.id')),
Column("deathdate", Date),
)

class Rock(object):
pass

class Bug(object):
pass

mapper(Rock, rocks_table, 
properties={
'livingbugs': relationship(Bug,
primaryjoin=and_(
bugs_table.c.rockid == rocks_table.c.id,
bugs_table.c.deathdate == None,
)),
})

mapper(Bug, bugs_table)

metadata.create_all()
try:
s = Session()

r=Rock()
r.id = 55

b=Bug()
b.id = 1
b.rockid = 55
b.deathdate = date.today()

s.add(r)
s.add(b)
s.commit()

s = Session()

rocks = s.query(Rock).options(joinedload('livingbugs')).all()
if not rocks:
# When not using ANSI, if 
# AND bugs_1.deathdate IS NULL
# is changed to:
# AND bugs_1.deathdate(+) IS NULL
# then the join is consistent with ANSI join and doesn't fail
raise Exception("Rock not selected")

finally:
metadata.drop_all()