[Sqlalchemy-commits] sqlalchemy: sort of muscling this out, mysql a PITA

[<co...@sq...>]

details:   http://hg.sqlalchemy.org/sqlalchemy/sqlalchemy/rev/c77de8c83228
changeset: 7910:c77de8c83228
user:      zzzeek
date:      Mon Nov 21 20:40:31 2011 -0500
description:
sort of muscling this out, mysql a PITA
Subject: sqlalchemy: passes for all three, includes multi col system with mysql

details:   http://hg.sqlalchemy.org/sqlalchemy/sqlalchemy/rev/18d9c041e55b
changeset: 7911:18d9c041e55b
user:      zzzeek
date:      Mon Nov 21 22:00:50 2011 -0500
description:
passes for all three, includes multi col system with mysql
Subject: sqlalchemy: cleanup

details:   http://hg.sqlalchemy.org/sqlalchemy/sqlalchemy/rev/0aae7303fd7e
changeset: 7912:0aae7303fd7e
user:      zzzeek
date:      Mon Nov 21 22:10:49 2011 -0500
description:
cleanup
Subject: sqlalchemy: - commit the unit tests

details:   http://hg.sqlalchemy.org/sqlalchemy/sqlalchemy/rev/b0c8ec8b2b83
changeset: 7913:b0c8ec8b2b83
user:      zzzeek
date:      Tue Nov 22 17:27:35 2011 -0500
description:
- commit the unit tests
- lots of doc updates to all three of update/insert/delete
Subject: sqlalchemy: fixes to actually get tests to pass

details:   http://hg.sqlalchemy.org/sqlalchemy/sqlalchemy/rev/40d783ff6ad0
changeset: 7914:40d783ff6ad0
user:      zzzeek
date:      Tue Nov 22 18:05:05 2011 -0500
description:
fixes to actually get tests to pass
Subject: sqlalchemy: merge tip

details:   http://hg.sqlalchemy.org/sqlalchemy/sqlalchemy/rev/a75b63c05ef8
changeset: 7915:a75b63c05ef8
user:      zzzeek
date:      Tue Nov 22 18:05:20 2011 -0500
description:
merge tip
Subject: sqlalchemy: changelog

details:   http://hg.sqlalchemy.org/sqlalchemy/sqlalchemy/rev/ca55966ba176
changeset: 7916:ca55966ba176
user:      zzzeek
date:      Tue Nov 22 18:15:51 2011 -0500
description:
changelog

diffstat:

 CHANGES                               |   22 ++-
 doc/build/core/tutorial.rst           |   59 ++++++++-
 lib/sqlalchemy/dialects/mysql/base.py |   32 ++--
 lib/sqlalchemy/sql/compiler.py        |  127 +++++++++++++++---
 lib/sqlalchemy/sql/expression.py      |  217 +++++++++++++++++++++++++-------
 test/aaa_profiling/test_compiler.py   |    4 +-
 test/lib/requires.py                  |    8 +
 test/sql/test_compiler.py             |   12 +
 test/sql/test_update.py               |  224 ++++++++++++++++++++++++++++++++++
 9 files changed, 604 insertions(+), 101 deletions(-)

diffs (truncated from 960 to 300 lines):

diff -r e32024b48e14 -r ca55966ba176 CHANGES
--- a/CHANGES	Sat Nov 19 23:28:01 2011 -0500
+++ b/CHANGES	Tue Nov 22 18:15:51 2011 -0500
@@ -5,12 +5,6 @@
 =======
 0.7.4
 =====
-- examples
-   - [bug] Fixed bug in history_meta.py example where
-     the "unique" flag was not removed from a 
-     single-table-inheritance subclass which 
-     generates columns to put up onto the base.
-
 - orm
    - [bug] Fixed backref behavior when "popping" the 
      value off of a many-to-one in response to 
@@ -82,6 +76,16 @@
      causing failures later on.  [ticket:2312]
 
 - sql
+   - [feature] The update() construct can now accommodate
+     multiple tables in the WHERE clause, which will
+     render an "UPDATE..FROM" construct, recognized by 
+     Postgresql and MSSQL.  When compiled on MySQL,
+     will instead generate "UPDATE t1, t2, ..".  MySQL
+     additionally can render against multiple tables in the
+     SET clause, if Column objects are used as keys
+     in the "values" parameter or generative method.
+     [ticket:2166] [ticket:1944]
+
    - [feature] Added accessor to types called "python_type",
      returns the rudimentary Python type object
      for a particular TypeEngine instance, if known,
@@ -133,6 +137,12 @@
   - [bug] Unicode adjustments allow latest pymysql 
     (post 0.4) to pass 100% on Python 2.
 
+- examples
+   - [bug] Fixed bug in history_meta.py example where
+     the "unique" flag was not removed from a 
+     single-table-inheritance subclass which 
+     generates columns to put up onto the base.
+
 0.7.3
 =====
 - general
diff -r e32024b48e14 -r ca55966ba176 doc/build/core/tutorial.rst
--- a/doc/build/core/tutorial.rst	Sat Nov 19 23:28:01 2011 -0500
+++ b/doc/build/core/tutorial.rst	Tue Nov 22 18:15:51 2011 -0500
@@ -1408,11 +1408,13 @@
     ()
     {stop}[(2, 1, u'ja...@ms...')]
 
+.. _inserts_and_updates:
+
 Inserts and Updates
 ===================
 
 Finally, we're back to INSERT for some more detail. The
-:func:`~sqlalchemy.sql.expression.insert` construct provides a ``values()``
+:func:`~sqlalchemy.sql.expression.insert` construct provides a :meth:`~.ValuesBase.values`
 method which can be used to send any value or clause expression to the VALUES
 portion of the INSERT::
 
@@ -1445,7 +1447,8 @@
         ]
     )
 
-Updates work a lot like INSERTS, except there is an additional WHERE clause
+An UPDATE statement is emitted using the :func:`.update` construct.  These
+work much like an INSERT, except there is an additional WHERE clause
 that can be specified:
 
 .. sourcecode:: pycon+sql
@@ -1508,10 +1511,60 @@
     COMMIT
     {stop}<sqlalchemy.engine.base.ResultProxy object at 0x...>
 
+Multiple Table Updates
+----------------------
+
+.. note:: This feature is new as of version 0.7.4.
+
+The Postgresql, Microsoft SQL Server, and MySQL backends all support UPDATE statements
+that refer to multiple tables.   For PG and MSSQL, this is the "UPDATE FROM" syntax,
+which updates one table at a time, but can reference additional tables in an additional
+"FROM" clause that can then be referenced in the WHERE clause directly.   On MySQL,
+multiple tables can be embedded into a single UPDATE statement separated by a comma.
+The SQLAlchemy :func:`.update` construct supports both of these modes 
+implicitly, simply by specifying multiple tables in the WHERE clause::
+
+    stmt = users.update().\
+            values(name='ed wood').\
+            where(users.c.id==addresses.c.id).\
+            where(addresses.c.email_address.startswith('ed%'))
+    conn.execute(stmt)
+
+The resulting SQL from the above statement would render as::
+
+    UPDATE users SET name=:name FROM addresses 
+    WHERE users.id = addresses.id AND 
+    addresses.email_address LIKE :email_address_1 || '%%'
+
+When using MySQL, columns from each table can be assigned to in the
+SET clause directly, using the dictionary form passed to :meth:`.Update.values`::
+
+    stmt = users.update().\
+            values({
+                users.c.name:'ed wood', 
+                addresses.c.email_address:'ed...@fo...'
+            }).\
+            where(users.c.id==addresses.c.id).\
+            where(addresses.c.email_address.startswith('ed%'))
+
+The tables are referenced explicitly in the SET clause::
+
+    UPDATE users, addresses SET addresses.email_address=%s, 
+            users.name=%s WHERE users.id = addresses.id 
+            AND addresses.email_address LIKE concat(%s, '%%')
+
+SQLAlchemy doesn't do anything special when these constructs are used on 
+a non-supporting database.  The ``UPDATE FROM`` syntax generates by default
+when multiple tables are present, and the statement will simply be rejected
+by the database if this syntax is not supported.
+
+.. _deletes:
+
 Deletes
 ========
 
-Finally, a delete.  Easy enough:
+Finally, a delete.  This is accomplished easily enough using the
+:func:`~.expression.delete` construct:
 
 .. sourcecode:: pycon+sql
 
diff -r e32024b48e14 -r ca55966ba176 lib/sqlalchemy/dialects/mysql/base.py
--- a/lib/sqlalchemy/dialects/mysql/base.py	Sat Nov 19 23:28:01 2011 -0500
+++ b/lib/sqlalchemy/dialects/mysql/base.py	Tue Nov 22 18:15:51 2011 -0500
@@ -1180,6 +1180,9 @@
 
 class MySQLCompiler(compiler.SQLCompiler):
 
+    render_table_with_column_in_update_from = True
+    """Overridden from base SQLCompiler value"""
+
     extract_map = compiler.SQLCompiler.extract_map.copy()
     extract_map.update ({
         'milliseconds': 'millisecond',
@@ -1315,25 +1318,20 @@
             # No offset provided, so just use the limit
             return ' \n LIMIT %s' % (self.process(sql.literal(limit)),)
 
-    def visit_update(self, update_stmt):
-        self.stack.append({'from': set([update_stmt.table])})
-
-        self.isupdate = True
-        colparams = self._get_colparams(update_stmt)
-
-        text = "UPDATE " + self.preparer.format_table(update_stmt.table) + \
-                " SET " + ', '.join(["%s=%s" % (self.preparer.format_column(c[0]), c[1]) for c in colparams])
-
-        if update_stmt._whereclause is not None:
-            text += " WHERE " + self.process(update_stmt._whereclause)
-
+    def update_limit_clause(self, update_stmt):
         limit = update_stmt.kwargs.get('%s_limit' % self.dialect.name, None)
         if limit:
-            text += " LIMIT %s" % limit
-
-        self.stack.pop(-1)
-
-        return text
+            return "LIMIT %s" % limit
+        else:
+            return None
+
+    def update_tables_clause(self, update_stmt, from_table, extra_froms, **kw):
+        return ', '.join(t._compiler_dispatch(self, asfrom=True, **kw) 
+                    for t in [from_table] + list(extra_froms))
+
+    def update_from_clause(self, update_stmt, from_table, extra_froms, **kw):
+        return None
+
 
 # ug.  "InnoDB needs indexes on foreign keys and referenced keys [...].
 #       Starting with MySQL 4.1.2, these indexes are created automatically.
diff -r e32024b48e14 -r ca55966ba176 lib/sqlalchemy/sql/compiler.py
--- a/lib/sqlalchemy/sql/compiler.py	Sat Nov 19 23:28:01 2011 -0500
+++ b/lib/sqlalchemy/sql/compiler.py	Tue Nov 22 18:15:51 2011 -0500
@@ -177,26 +177,36 @@
 
     compound_keywords = COMPOUND_KEYWORDS
 
-    # class-level defaults which can be set at the instance
-    # level to define if this Compiled instance represents
-    # INSERT/UPDATE/DELETE
     isdelete = isinsert = isupdate = False
+    """class-level defaults which can be set at the instance
+    level to define if this Compiled instance represents
+    INSERT/UPDATE/DELETE
+    """
 
-    # holds the "returning" collection of columns if
-    # the statement is CRUD and defines returning columns
-    # either implicitly or explicitly
     returning = None
+    """holds the "returning" collection of columns if
+    the statement is CRUD and defines returning columns
+    either implicitly or explicitly
+    """
 
-    # set to True classwide to generate RETURNING
-    # clauses before the VALUES or WHERE clause (i.e. MSSQL)
     returning_precedes_values = False
+    """set to True classwide to generate RETURNING
+    clauses before the VALUES or WHERE clause (i.e. MSSQL)
+    """
 
-    # SQL 92 doesn't allow bind parameters to be used
-    # in the columns clause of a SELECT, nor does it allow
-    # ambiguous expressions like "? = ?".  A compiler
-    # subclass can set this flag to False if the target
-    # driver/DB enforces this
+    render_table_with_column_in_update_from = False
+    """set to True classwide to indicate the SET clause
+    in a multi-table UPDATE statement should qualify
+    columns with the table name (i.e. MySQL only)
+    """
+
     ansi_bind_rules = False
+    """SQL 92 doesn't allow bind parameters to be used
+    in the columns clause of a SELECT, nor does it allow
+    ambiguous expressions like "? = ?".  A compiler
+    subclass can set this flag to False if the target
+    driver/DB enforces this
+    """
 
     def __init__(self, dialect, statement, column_keys=None, 
                     inline=False, **kwargs):
@@ -985,20 +995,56 @@
 
         return text
 
-    def visit_update(self, update_stmt):
+    def update_limit_clause(self, update_stmt):
+        """Provide a hook for MySQL to add LIMIT to the UPDATE"""
+        return None
+
+    def update_tables_clause(self, update_stmt, from_table, 
+                                            extra_froms, **kw):
+        """Provide a hook to override the initial table clause
+        in an UPDATE statement.
+        
+        MySQL overrides this.
+
+        """
+        return self.preparer.format_table(from_table)
+
+    def update_from_clause(self, update_stmt, from_table, extra_froms, **kw):
+        """Provide a hook to override the generation of an 
+        UPDATE..FROM clause.
+        
+        MySQL overrides this.
+
+        """
+        return "FROM " + ', '.join(
+                    t._compiler_dispatch(self, asfrom=True, **kw) 
+                    for t in extra_froms)
+
+    def visit_update(self, update_stmt, **kw):
         self.stack.append({'from': set([update_stmt.table])})
 
         self.isupdate = True
-        colparams = self._get_colparams(update_stmt)
 
-        text = "UPDATE " + self.preparer.format_table(update_stmt.table)
+        extra_froms = update_stmt._extra_froms
 
-        text += ' SET ' + \
-                ', '.join(
+        colparams = self._get_colparams(update_stmt, extra_froms)
+
+        text = "UPDATE " + self.update_tables_clause(
+                                        update_stmt, 
+                                        update_stmt.table, 
+                                        extra_froms, **kw)
+
+        text += ' SET '
+        if extra_froms and self.render_table_with_column_in_update_from:
+            text += ', '.join(
+                            self.visit_column(c[0]) + 
+                            '=' + c[1] for c in colparams
+                            )
+        else:
+            text += ', '.join(
                         self.preparer.quote(c[0].name, c[0].quote) + 
-                        '=' + c[1]
-                      for c in colparams
-                )