Commits

Mike Bayer committed c266761

- [feature] The prefix_with() method is now available
on each of select(), insert(), update(), delete(),
all with the same API, accepting multiple
prefix calls, as well as a "dialect name" so that
the prefix can be limited to one kind of dialect.
[ticket:2431]

Comments (0)

Files changed (5)

     method, auto correlates all selectables except those
     passed.
 
+  - [feature] The prefix_with() method is now available
+    on each of select(), insert(), update(), delete(),
+    all with the same API, accepting multiple
+    prefix calls, as well as a "dialect name" so that
+    the prefix can be limited to one kind of dialect.
+    [ticket:2431]
+
   - [feature] Added reduce_columns() method
     to select() construct, replaces columns inline
     using the util.reduce_columns utility function

doc/build/core/expression_api.rst

    :show-inheritance:
 
 .. autoclass:: Delete
-   :members: where
+   :members: where, prefix_with
    :show-inheritance:
 
 .. autoclass:: Executable
    :members:
    :show-inheritance:
 
+   ..automember:: prefix_with
+
 .. autoclass:: Selectable
    :members:
    :show-inheritance:
    :show-inheritance:
 
 .. autoclass:: Update
-  :members:
+  :members: prefix_with, values, where
   :show-inheritance:
 
 .. autoclass:: UpdateBase

lib/sqlalchemy/sql/compiler.py

                 text += hint_text + " "
 
         if select._prefixes:
-            text += " ".join(
-                            x._compiler_dispatch(self, **kwargs)
-                            for x in select._prefixes) + " "
+            text += self._generate_prefixes(select, select._prefixes, **kwargs)
+
         text += self.get_select_precolumns(select)
         text += ', '.join(inner_columns)
 
         else:
             return text
 
+    def _generate_prefixes(self, stmt, prefixes, **kw):
+        clause = " ".join(
+                            prefix._compiler_dispatch(self, **kw)
+                            for prefix, dialect_name in prefixes
+                            if dialect_name is None or
+                                dialect_name == self.dialect.name
+                            )
+        if clause:
+            clause += " "
+        return clause
+
     def _render_cte_clause(self):
         if self.positional:
             self.positiontup = self.cte_positional + self.positiontup
             join.onclause._compiler_dispatch(self, **kwargs)
         )
 
-    def visit_insert(self, insert_stmt):
+    def visit_insert(self, insert_stmt, **kw):
         self.isinsert = True
         colparams = self._get_colparams(insert_stmt)
 
         preparer = self.preparer
         supports_default_values = self.dialect.supports_default_values
 
-        text = "INSERT"
+        text = "INSERT "
 
+        if insert_stmt._prefixes:
+            text += self._generate_prefixes(insert_stmt,
+                                insert_stmt._prefixes, **kw)
 
-        prefixes = [self.process(x) for x in insert_stmt._prefixes]
-        if prefixes:
-            text += " " + " ".join(prefixes)
-
-        text += " INTO "
+        text += "INTO "
         table_text = preparer.format_table(insert_stmt.table)
 
         if insert_stmt._hints:
         colparams = self._get_colparams(update_stmt, extra_froms)
 
         text = "UPDATE "
+
+        if update_stmt._prefixes:
+            text += self._generate_prefixes(update_stmt,
+                                update_stmt._prefixes, **kw)
+
         table_text = self.update_tables_clause(update_stmt, update_stmt.table,
                                                extra_froms, **kw)
 
 
         return values
 
-    def visit_delete(self, delete_stmt):
+    def visit_delete(self, delete_stmt, **kw):
         self.stack.append({'from': set([delete_stmt.table])})
         self.isdelete = True
 
-        text = "DELETE FROM "
+        text = "DELETE "
+
+        if delete_stmt._prefixes:
+            text += self._generate_prefixes(delete_stmt,
+                                delete_stmt._prefixes, **kw)
+
+        text += "FROM "
         table_text = delete_stmt.table._compiler_dispatch(self,
                                 asfrom=True, iscrud=True)
 

lib/sqlalchemy/sql/expression.py

       ``distinct`` is also available via the :meth:`~.Select.distinct`
       generative method.
 
-      .. note::
-
-         The ``distinct`` keyword's acceptance of a string
-         argument for usage with MySQL is deprecated.  Use
-         the ``prefixes`` argument or :meth:`~.Select.prefix_with`.
-
     :param for_update=False:
       when ``True``, applies ``FOR UPDATE`` to the end of the
       resulting statement.
       a scalar or list of :class:`.ClauseElement` objects which will
       comprise the ``ORDER BY`` clause of the resulting select.
 
-    :param prefixes:
-      a list of strings or :class:`.ClauseElement` objects to include
-      directly after the SELECT keyword in the generated statement,
-      for dialect-specific query features.  ``prefixes`` is
-      also available via the :meth:`~.Select.prefix_with`
-      generative method.
-
     :param use_labels=False:
       when ``True``, the statement will be generated using labels
       for each column in the columns clause, which qualify each
      generated from the full list of table columns. Note that the
      :meth:`~Insert.values()` generative method may also be used for this.
 
-    :param prefixes: A list of modifier keywords to be inserted between INSERT
-      and INTO. Alternatively, the :meth:`~Insert.prefix_with` generative
-      method may be used.
-
     :param inline: if True, SQL defaults will be compiled 'inline' into the
       statement and not pre-executed.
 
         self._bind = bind
     bind = property(bind, _set_bind)
 
-class Select(SelectBase):
+class HasPrefixes(object):
+    _prefixes = ()
+
+    @_generative
+    def prefix_with(self, *expr, **kw):
+        """Add one or more expressions following the statement keyword, i.e.
+        SELECT, INSERT, UPDATE, or DELETE. Generative.
+
+        This is used to support backend-specific prefix keywords such as those
+        provided by MySQL.
+
+        E.g.::
+
+            stmt = table.insert().prefix_with("LOW_PRIORITY", dialect="mysql")
+
+        Multiple prefixes can be specified by multiple calls
+        to :meth:`.prefix_with`.
+
+        :param \*expr: textual or :class:`.ClauseElement` construct which
+         will be rendered following the INSERT, UPDATE, or DELETE
+         keyword.
+        :param \**kw: A single keyword 'dialect' is accepted.  This is an
+         optional string dialect name which will
+         limit rendering of this prefix to only that dialect.
+
+        """
+        dialect = kw.pop('dialect', None)
+        if kw:
+            raise exc.ArgumentError("Unsupported argument(s): %s" %
+                            ",".join(kw))
+        self._setup_prefixes(expr, dialect)
+
+    def _setup_prefixes(self, prefixes, dialect=None):
+        self._prefixes = self._prefixes + tuple(
+                            [(_literal_as_text(p), dialect) for p in prefixes])
+
+class Select(HasPrefixes, SelectBase):
     """Represents a ``SELECT`` statement.
 
     See also:
         """
         self._should_correlate = correlate
         if distinct is not False:
-            if isinstance(distinct, basestring):
-                util.warn_deprecated(
-                    "A string argument passed to the 'distinct' "
-                    "keyword argument of 'select()' is deprecated "
-                    "- please use 'prefixes' or 'prefix_with()' "
-                    "to specify additional prefixes")
-                if prefixes:
-                    prefixes = util.to_list(prefixes) + [distinct]
-                else:
-                    prefixes = [distinct]
-            elif distinct is True:
+            if distinct is True:
                 self._distinct = True
             else:
                 self._distinct = [
             self._having = None
 
         if prefixes:
-            self._prefixes = tuple([_literal_as_text(p) for p in prefixes])
+            self._setup_prefixes(prefixes)
 
         SelectBase.__init__(self, **kwargs)
 
         else:
             self._distinct = True
 
-    @_generative
-    def prefix_with(self, *expr):
-        """return a new select() construct which will apply the given
-        expressions, typically strings, to the start of its columns clause,
-        not using any commas.   In particular is useful for MySQL
-        keywords.
-
-        e.g.::
-
-             select(['a', 'b']).prefix_with('HIGH_PRIORITY',
-                                    'SQL_SMALL_RESULT',
-                                    'ALL')
-
-        Would render::
-
-            SELECT HIGH_PRIORITY SQL_SMALL_RESULT ALL a, b
-
-         """
-        expr = tuple(_literal_as_text(e) for e in expr)
-        self._prefixes = self._prefixes + expr
 
     @_generative
     def select_from(self, fromclause):
         self._bind = bind
     bind = property(bind, _set_bind)
 
-class UpdateBase(Executable, ClauseElement):
+class UpdateBase(HasPrefixes, Executable, ClauseElement):
     """Form the base for ``INSERT``, ``UPDATE``, and ``DELETE`` statements.
 
     """
         Executable._execution_options.union({'autocommit': True})
     kwargs = util.immutabledict()
     _hints = util.immutabledict()
+    _prefixes = ()
 
     def _process_colparams(self, parameters):
         if isinstance(parameters, (list, tuple)):
         self._bind = bind
     bind = property(bind, _set_bind)
 
+
     _returning_re = re.compile(r'(?:firebird|postgres(?:ql)?)_returning')
 
     def _process_deprecated_kw(self, kwargs):
         .. note::
 
          :meth:`.UpdateBase.with_hint` currently applies only to
-         Microsoft SQL Server.  For MySQL INSERT hints, use
-         :meth:`.Insert.prefix_with`.   UPDATE/DELETE hints for
-         MySQL will be added in a future release.
+         Microsoft SQL Server.  For MySQL INSERT/UPDATE/DELETE hints, use
+         :meth:`.UpdateBase.prefix_with`.
 
         The text of the hint is rendered in the appropriate
         location for the database backend in use, relative
 
     __visit_name__ = 'values_base'
 
-    def __init__(self, table, values):
+    def __init__(self, table, values, prefixes):
         self.table = table
         self.parameters = self._process_colparams(values)
+        if prefixes:
+            self._setup_prefixes(prefixes)
 
     @_generative
     def values(self, *args, **kwargs):
     """
     __visit_name__ = 'insert'
 
-    _prefixes = ()
 
     def __init__(self,
                 table,
                 prefixes=None,
                 returning=None,
                 **kwargs):
-        ValuesBase.__init__(self, table, values)
+        ValuesBase.__init__(self, table, values, prefixes)
         self._bind = bind
         self.select = None
         self.inline = inline
         self._returning = returning
-        if prefixes:
-            self._prefixes = tuple([_literal_as_text(p) for p in prefixes])
 
         if kwargs:
             self.kwargs = self._process_deprecated_kw(kwargs)
         # TODO: coverage
         self.parameters = self.parameters.copy()
 
-    @_generative
-    def prefix_with(self, clause):
-        """Add a word or expression between INSERT and INTO. Generative.
-
-        If multiple prefixes are supplied, they will be separated with
-        spaces.
-
-        """
-        clause = _literal_as_text(clause)
-        self._prefixes = self._prefixes + (clause,)
 
 class Update(ValuesBase):
     """Represent an Update construct.
                 values=None,
                 inline=False,
                 bind=None,
+                prefixes=None,
                 returning=None,
                 **kwargs):
-        ValuesBase.__init__(self, table, values)
+        ValuesBase.__init__(self, table, values, prefixes)
         self._bind = bind
         self._returning = returning
         if whereclause is not None:
             whereclause,
             bind=None,
             returning=None,
+            prefixes=None,
             **kwargs):
         self._bind = bind
         self.table = table
         self._returning = returning
 
+        if prefixes:
+            self._setup_prefixes(prefixes)
+
         if whereclause is not None:
             self._whereclause = _literal_as_text(whereclause)
         else:

test/sql/test_compiler.py

 from sqlalchemy import *
 from sqlalchemy import exc, sql, util, types, schema
 from sqlalchemy.sql import table, column, label, compiler
-from sqlalchemy.sql.expression import ClauseList, _literal_as_text
+from sqlalchemy.sql.expression import ClauseList, _literal_as_text, HasPrefixes
 from sqlalchemy.engine import default
 from sqlalchemy.databases import *
 from test.lib import *
             assert not hasattr(select([table1.c.myid]).as_scalar().self_group(), 'columns')
             assert not hasattr(select([table1.c.myid]).as_scalar(), 'columns')
 
+    def test_prefix_constructor(self):
+        class Pref(HasPrefixes):
+            def _generate(self):
+                return self
+        assert_raises(exc.ArgumentError,
+                Pref().prefix_with,
+                "some prefix", not_a_dialect=True
+        )
+
     def test_table_select(self):
         self.assert_compile(table1.select(),
                             "SELECT mytable.myid, mytable.name, "
         )
 
 
-    def test_prefixes(self):
+    def test_prefix(self):
         self.assert_compile(
             table1.select().prefix_with("SQL_CALC_FOUND_ROWS").\
                                 prefix_with("SQL_SOME_WEIRD_MYSQL_THING"),
             "mytable.myid, mytable.name, mytable.description FROM mytable"
         )
 
+    def test_prefix_dialect_specific(self):
+        self.assert_compile(
+            table1.select().prefix_with("SQL_CALC_FOUND_ROWS", dialect='sqlite').\
+                                prefix_with("SQL_SOME_WEIRD_MYSQL_THING",
+                                        dialect='mysql'),
+            "SELECT SQL_SOME_WEIRD_MYSQL_THING "
+            "mytable.myid, mytable.name, mytable.description FROM mytable",
+            dialect=mysql.dialect()
+        )
+
     def test_text(self):
         self.assert_compile(
             text("select * from foo where lala = bar") ,
                     insert(table1, values=dict(myid=func.lala())),
                     "INSERT INTO mytable (myid) VALUES (lala())")
 
+    def test_insert_prefix(self):
+        stmt = table1.insert().prefix_with("A", "B", dialect="mysql").\
+                prefix_with("C", "D")
+        self.assert_compile(stmt,
+            "INSERT A B C D INTO mytable (myid, name, description) "
+            "VALUES (%s, %s, %s)", dialect=mysql.dialect()
+        )
+        self.assert_compile(stmt,
+            "INSERT C D INTO mytable (myid, name, description) "
+            "VALUES (:myid, :name, :description)")
+
     def test_inline_insert(self):
         metadata = MetaData()
         table = Table('sometable', metadata,
             "WHERE mytable.myid = hoho(:hoho_1) AND mytable.name = :param_2 || "
             "mytable.name || :param_3")
 
+    def test_update_prefix(self):
+        stmt = table1.update().prefix_with("A", "B", dialect="mysql").\
+                prefix_with("C", "D")
+        self.assert_compile(stmt,
+            "UPDATE A B C D mytable SET myid=%s, name=%s, description=%s",
+            dialect=mysql.dialect()
+        )
+        self.assert_compile(stmt,
+            "UPDATE C D mytable SET myid=:myid, name=:name, "
+            "description=:description")
+
     def test_aliased_update(self):
         talias1 = table1.alias('t1')
         self.assert_compile(
                         "DELETE FROM mytable WHERE mytable.myid = :myid_1 "
                         "AND mytable.name = :name_1")
 
+    def test_delete_prefix(self):
+        stmt = table1.delete().prefix_with("A", "B", dialect="mysql").\
+                prefix_with("C", "D")
+        self.assert_compile(stmt,
+            "DELETE A B C D FROM mytable",
+            dialect=mysql.dialect()
+        )
+        self.assert_compile(stmt,
+            "DELETE C D FROM mytable")
+
     def test_aliased_delete(self):
         talias1 = table1.alias('t1')
         self.assert_compile(