Commits

jason kirtland committed c6d55d4

- Added support for UPDATE with LIMIT on mysql.
- Added mysql dialect tests for SET columns and the in_ operator.

Comments (0)

Files changed (3)

   
 - fixed sqlite reflection of BOOL/BOOLEAN [ticket:808]
 
+- Added support for UPDATE with LIMIT on mysql.
+
 - null foreign key on a m2o doesn't trigger a lazyload [ticket:803]
 
 - oracle does not implicitly convert to unicode for non-typed result

lib/sqlalchemy/databases/mysql.py

 about how OpenGIS can be smartly integrated into SQLAlchemy please join
 the mailing list!
 
+Many of the MySQL SQL extensions are handled through SQLAlchemy's generic
+function and operator support::
+
+  table.select(table.c.password==func.md5('plaintext'))
+  table.select(table.c.username.op('regexp')('^[a-d]'))
+
+And of course any valid statement can be executed as a string rather than
+through the SQL expression language.
+
+Some limited support for MySQL extensions to SQL expressions is currently
+available.
+
+  * SELECT pragma::
+
+      select(..., prefixes=['HIGH_PRIORITY', 'SQL_SMALL_RESULT'])
+
+  * UPDATE with LIMIT::
+
+      update(..., mysql_limit=10)
+
 If you have problems that seem server related, first check that you are
 using the most recent stable MySQL-Python package available.  The Database
-Notes page on the wiki at http://sqlalchemy.org is a good resource for timely
-information affecting MySQL in SQLAlchemy.
+Notes page on the wiki at http://www.sqlalchemy.org is a good resource for
+timely information affecting MySQL in SQLAlchemy.
 """
 
 import re, datetime, inspect, warnings, sys
             # No offset provided, so just use the limit
             return ' \n LIMIT %s' % (limit,)
 
+    def visit_update(self, update_stmt):
+        self.stack.append({'from':util.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:
+            text += " WHERE " + self.process(update_stmt._whereclause)
+
+        limit = update_stmt.kwargs.get('mysql_limit', None)
+        if limit:
+            text += " LIMIT %s" % limit
+        
+        self.stack.pop(-1)
+        
+        return text
 
 # ug.  "InnoDB needs indexes on foreign keys and referenced keys [...].
 #       Starting with MySQL 4.1.2, these indexes are created automatically.

test/dialect/mysql.py

                 roundtrip([1, 1, 1],
                           [set(['dq']), set(['a']), set(['5'])])
                 roundtrip([set(['dq', 'sq']), None, set(['9', '5', '7'])])
+
+            set_table.insert().execute({'s3':set(['5'])},
+                                       {'s3':set(['5', '7'])},
+                                       {'s3':set(['5', '7', '9'])},
+                                       {'s3':set(['7', '9'])})
+            rows = list(select(
+                [set_table.c.s3],
+                set_table.c.s3.in_([set(['5']), set(['5', '7'])])).execute())
+            found = set([frozenset(row[0]) for row in rows])
+            self.assertEquals(found,
+                              set([frozenset(['5']), frozenset(['5', '7'])]))
         finally:
             meta.drop_all()
 
             "SELECT t.col1, t.col2 FROM t  LIMIT 10, 18446744073709551615"
             )
 
+    @testing.supported('mysql')
+    def test_update_limit(self):
+        t = sql.table('t', sql.column('col1'), sql.column('col2'))
+
+        self.assert_compile(
+            t.update(values={'col1':123}),
+            "UPDATE t SET col1=%s"
+            )
+        self.assert_compile(
+            t.update(values={'col1':123}, mysql_limit=5),
+            "UPDATE t SET col1=%s LIMIT 5"
+            )
+        self.assert_compile(
+            t.update(values={'col1':123}, mysql_limit=None),
+            "UPDATE t SET col1=%s"
+            )
+        self.assert_compile(
+            t.update(t.c.col2==456, values={'col1':123}, mysql_limit=1),
+            "UPDATE t SET col1=%s WHERE t.col2 = %s LIMIT 1"
+            )
 
 def colspec(c):
     return testbase.db.dialect.schemagenerator(testbase.db.dialect,