[sqlalchemy] Re: Convert JOIN to WHERE for old MySQLs
King Simon-NFHD78 wrote: > Jason kirtland wrote: >> King Simon-NFHD78 wrote: >>> Hi, >>> >>> I'm connecting to an ancient version of MySQL (3.23.58) >>> that (as far as >>> I can tell) doesn't support basic "JOIN ON >>> " syntax >> >> This is in r3916. It's a simple change that can be easily monkey >> patched into any version of SA- these just need to be emitted >> as INNER >> JOIN .. ON instead. > > Thanks - that looks much easier than the oracle version. I _almost_ > understand this one! Monkeypatching now... > > (I also hadn't spotted that adding 'INNER' was enough to satisfy > MySQL...) For hand-patching older SA versions, the recipe is: find the base implementation of visit_join (in compiler.py or wherever, not in databases/), copy it into the MySQL dialect and change 'JOIN' to 'INNER JOIN'. Cheers, Jason --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Convert JOIN to WHERE for old MySQLs
King Simon-NFHD78 wrote: > Hi, > > I'm connecting to an ancient version of MySQL (3.23.58) that (as far as > I can tell) doesn't support basic "JOIN ON " syntax > > Ie. this gives a syntax error: > > SELECT * FROM productversions pv > JOIN producttypes pt ON pv.producttype = pt.producttype > > Whereas this is fine: > > SELECT * FROM productversions pv > JOIN producttypes pt > WHERE pv.producttype = pt.producttype > > Can anyone think of anything I can do (perhaps by subclassing or > monkeypatching MySQLDialect) to automatically convert queries like the > former into the latter? SQLAlchemy seems to be infitely flexible in > dealing with syntax quirks of various databases, so I've got my fingers > crossed... This is in r3916. It's a simple change that can be easily monkey patched into any version of SA- these just need to be emitted as INNER JOIN .. ON instead. -j --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Convert JOIN to WHERE for old MySQLs
Michael Bayer wrote > On Dec 12, 2007, at 9:04 AM, King Simon-NFHD78 wrote: > > > > > Hi, > > > > I'm connecting to an ancient version of MySQL (3.23.58) > that (as far > > as > > I can tell) doesn't support basic "JOIN ON > " syntax > > > > Ie. this gives a syntax error: > > > > SELECT * FROM productversions pv > > JOIN producttypes pt ON pv.producttype = pt.producttype > > > > Whereas this is fine: > > > > SELECT * FROM productversions pv > > JOIN producttypes pt > > WHERE pv.producttype = pt.producttype > > > > Can anyone think of anything I can do (perhaps by subclassing or > > monkeypatching MySQLDialect) to automatically convert > queries like the > > former into the latter? SQLAlchemy seems to be infitely flexible in > > dealing with syntax quirks of various databases, so I've got my > > fingers > > crossed... > > > > this is possible, we do it in the oracle module when the ansi=False > flag is set. if you look at the visit_join code in oracle.py, you > could probably lift that up and stick it right in mysql.py, have it > activated by a dialect flag, and it would work. > > however, if the database doesnt support JOIN..ON, that > indicates that > its impossible to do an OUTER JOIN.oracle used a special > operator > (+) to indicate the "outerness" of a join..does this old mysql have > anything for that ? otherwise it doesnt seem much worth it to > support JOIN at all when you could just select from two tables. > I knew SA could do it! Thanks! This version of MySQL does support LEFT JOIN and RIGHT JOIN, so things like eager loads work correctly (as far as I can tell). Does that mean that in visit_join, I should defer to the normal implementation if join.isouter is True? This came up because I was using query.join to filter a query based on a child's attributes, and suddenly the query stopped working on MySQL. I was about to try and find all occurrences of query.join and change them to explicit filter conditions, but if I can make the visit_join method work, I would much prefer to do it that way. I'll let you know if it works - it may be worth adding as an optional behaviour to MySQLDialect (although if I'm the only one that's come across the problem, maybe no-one else is using a version of MySQL that's this old) Thanks, Simon --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Convert JOIN to WHERE for old MySQLs
Jason kirtland wrote: > > King Simon-NFHD78 wrote: > > Hi, > > > > I'm connecting to an ancient version of MySQL (3.23.58) > that (as far as > > I can tell) doesn't support basic "JOIN ON > " syntax > > > > Ie. this gives a syntax error: > > > > SELECT * FROM productversions pv > > JOIN producttypes pt ON pv.producttype = pt.producttype > > > > Whereas this is fine: > > > > SELECT * FROM productversions pv > > JOIN producttypes pt > > WHERE pv.producttype = pt.producttype > > > > Can anyone think of anything I can do (perhaps by subclassing or > > monkeypatching MySQLDialect) to automatically convert > queries like the > > former into the latter? SQLAlchemy seems to be infitely flexible in > > dealing with syntax quirks of various databases, so I've > got my fingers > > crossed... > > This is in r3916. It's a simple change that can be easily monkey > patched into any version of SA- these just need to be emitted > as INNER > JOIN .. ON instead. > > -j Thanks - that looks much easier than the oracle version. I _almost_ understand this one! Monkeypatching now... (I also hadn't spotted that adding 'INNER' was enough to satisfy MySQL...) Cheers, Simon --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Convert JOIN to WHERE for old MySQLs
On Dec 12, 2007, at 9:04 AM, King Simon-NFHD78 wrote: > > Hi, > > I'm connecting to an ancient version of MySQL (3.23.58) that (as far > as > I can tell) doesn't support basic "JOIN ON " syntax > > Ie. this gives a syntax error: > > SELECT * FROM productversions pv > JOIN producttypes pt ON pv.producttype = pt.producttype > > Whereas this is fine: > > SELECT * FROM productversions pv > JOIN producttypes pt > WHERE pv.producttype = pt.producttype > > Can anyone think of anything I can do (perhaps by subclassing or > monkeypatching MySQLDialect) to automatically convert queries like the > former into the latter? SQLAlchemy seems to be infitely flexible in > dealing with syntax quirks of various databases, so I've got my > fingers > crossed... > this is possible, we do it in the oracle module when the ansi=False flag is set. if you look at the visit_join code in oracle.py, you could probably lift that up and stick it right in mysql.py, have it activated by a dialect flag, and it would work. however, if the database doesnt support JOIN..ON, that indicates that its impossible to do an OUTER JOIN.oracle used a special operator (+) to indicate the "outerness" of a join..does this old mysql have anything for that ? otherwise it doesnt seem much worth it to support JOIN at all when you could just select from two tables. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~--~~~~--~~--~--~---
