[Modeling-users] [patch] Switch to generic nested join for recent versions of MySQL

[John G. <ig...@do...>]

Hi Sebastian,

I get a syntax error from MySQL-4.0.11 when using nested JOINS created by
MySQLAdaptorLayer.MySQLExpression._addTableJoinsForAlias(). If I switch to
Modeling.SQLExpression._addTableJoinsForAlias(), the problem is gone.

The following patch does this for mysql version >= 4.0.11. If anyone can
confirm this for an older version of MySQL, then feel free to lower the
version numbers in the tuple.


diff -urN --exclude=Python_bricks Modeling.orig/DatabaseAdaptors/MySQLAdaptorLayer/MySQLSQLExpression.py Modeling/DatabaseAdaptors/MySQLAdaptorLayer/MySQLSQLExpression.py
--- Modeling.orig/DatabaseAdaptors/MySQLAdaptorLayer/MySQLSQLExpression.py	2003-08-10 14:59:13.000000000 +0300
+++ Modeling/DatabaseAdaptors/MySQLAdaptorLayer/MySQLSQLExpression.py	2003-12-07 18:14:53.000000000 +0200
@@ -33,6 +33,7 @@

 __version__='$Revision: 1.7 $'[11:-2]

+from Modeling.DatabaseAdaptors.MySQLAdaptorLayer.mysql_utils import *
 from Modeling.SQLExpression import SQLExpression, DateType, CharacterType
 from Modeling.logging import trace
 import string
@@ -141,6 +142,10 @@
     https://sourceforge.net/tracker/index.php?func=detail&aid=614261&group_id=58935&atid=489335

     """
+    version = mysql_server_version()
+    if version[:3] >= (4,0,11):
+      return SQLExpression._addTableJoinsForAlias(self, alias, str)
+
     trace('alias: %s / str: %s'%(alias, str))
     relPaths=self._internals.relPathsComingFromAlias(alias)
     aliases=map(lambda rp, self=self: self._internals.aliasForRelPath(rp),
diff -urN --exclude=Python_bricks Modeling.orig/DatabaseAdaptors/MySQLAdaptorLayer/mysql_utils.py Modeling/DatabaseAdaptors/MySQLAdaptorLayer/mysql_utils.py
--- Modeling.orig/DatabaseAdaptors/MySQLAdaptorLayer/mysql_utils.py	1970-01-01 02:00:00.000000000 +0200
+++ Modeling/DatabaseAdaptors/MySQLAdaptorLayer/mysql_utils.py	2003-12-07 18:07:52.000000000 +0200
@@ -0,0 +1,67 @@
+#-----------------------------------------------------------------------------
+#
+# Modeling Framework: an Object-Relational Bridge for python
+# (c) 2001, 2002, 2003 Sebastien Bigaret
+#
+# This file is part of the Modeling Framework.
+#
+# The Modeling Framework is free software; you can redistribute it and/or
+# modify it under the terms of the GNU General Public License as published
+# by the Free Software Foundation; either version 2 of the License, or (at
+# your option) any later version.
+#
+# The Modeling Framework is distributed in the hope that it will be
+# useful, but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+# General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License along
+# with the Modeling Framework; if not, write to the Free Software
+# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
+#
+#-----------------------------------------------------------------------------
+
+import MySQLdb;
+
+"""
+MySQL Adaptor Layer's utils
+
+  Central point for determining MySQL specifics, like the version of the
+  mysql server in use.
+
+  CVS information
+
+    $Id$
+
+"""
+
+__version__='$Revision$'[11:-2]
+
+import os
+
+def mysql_server_version():
+  """
+  Returns the version of the mysql server currently in use. It calls
+  MySQLdb.get_client_info().
+
+  Return value:
+    A 4-values tuple: (VERSION, PATCH_LEVEL, SUBLEVEL, EXTRA_VERSION)
+    E.g. (4,0,11,a-gamma)
+
+
+  """
+  s = MySQLdb.get_client_info()
+  v_list = s.split('.', 3)
+  version = int(v_list[0])
+  patch_level = int(v_list[1])
+  s = v_list[2]
+  for i in range(len(s)):
+    if not s[i].isdigit():
+      break
+  if i == 0:
+    sublevel = 0
+  else:
+    sublevel = int(s[:i])
+  extra_version = s[i:]
+
+  return (version, patch_level, sublevel, extra_version)