[Modeling-users] Bugfix proposal for MySQL-related bug #614261

[Sebastien B. <sbi...@us...>]

        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.