[Modeling-users] Bugfix proposal for MySQL-related bug #614261
Status: Abandoned
Brought to you by:
sbigaret
|
From: Sebastien B. <sbi...@us...> - 2003-03-07 15:24:43
|
Hi,
I've just checked-in a bugfix for MySQLSQLExpression to support arbitra=
rily
long qualifiers when selecting objects.
A few words on what happened: a fetch specified that way was failing:
>>> from Modeling.Qualifier import qualifierWithQualifierFormat as =
qWQF
>>> q=3DqWQF('(age<100) AND pygmalion.books.title like "G*"')
>>>
>>> fetchSpec=3DFetchSpecification(entityName=3D'Writer', qualifier=
=3Dq)
>>> objects=3Dec.objectsWithFetchSpecification(fetchSpec)
The default Modeling.SQLExpression produces the following statement:
(extracted from test_11_allQualifierOperators)
SELECT t0.ID, t0.FIRST_NAME, t0.LAST_NAME, t0.AGE, t0.BIRTHDAY,
t0.FK_WRITER_ID
FROM WRITER t0=20
INNER JOIN ( WRITER t1=20
INNER JOIN BOOK t2=20
ON t1.ID=3Dt2.FK_WRITER_ID )
ON t0.FK_WRITER_ID=3Dt1.ID=20
WHERE (t0.AGE < 100 AND t2.title LIKE 'G%')
--> this led to a _mysql_exceptions.ProgrammingError/1064, "You have =
an
error in your SQL syntax near ...", every time a keypath with 2 or
more entities was inserted in a qualifier (such as in:
'pygmalion.books.title')
See: http://www.mysql.com/doc/en/JOIN.html, the first comment in
''User Comments'' (bottom of the page) describes this.
Now MySQLSQLExpression overrides this default behaviour so that element=
s are
produced in the correct order for MySQL, i.e.:
SELECT t0.ID, t0.FIRST_NAME, t0.LAST_NAME, t0.AGE, t0.BIRTHDAY,=20
t0.FK_WRITER_ID
FROM ( BOOK t2=20
INNER JOIN WRITER t1
ON ( t1.ID=3Dt2.FK_WRITER_ID )=20=20
)=20=20
INNER JOIN WRITER t0
ON ( t0.FK_WRITER_ID=3Dt1.ID )
WHERE (t0.AGE < 100 AND t2.title LIKE 'G%')
I did not close the bug-item, however: since I do not use MySQL at al=
l,
all what I can say is that it now passes all the tests. I'd prefer th=
at
users using mysql plays a bit with that and report success or failure.
The corrected MySQLSQLExpression can be found at:
http://cvs.sourceforge.net/cgi-bin/viewcvs.cgi/modeling/ProjectModeling=
/Modeling/DatabaseAdaptors/MySQLAdaptorLayer/MySQLSQLExpression.py?rev=
=3D1.3&content-type=3Dtext/vnd.viewcvs-markup
and the updated test_EditingContext_Global.py at:
http://cvs.sourceforge.net/cgi-bin/viewcvs.cgi/modeling/ProjectModeling=
/Modeling/tests/test_EditingContext_Global.py?rev=3D1.17&content-type=
=3Dtext/vnd.viewcvs-markup
Happy fetching :)
-- S=E9bastien.
|