- more updates to text docs, literal_column, column etc. in prep

for ticket 2992.

3 files changed, 177 insertions, 41 deletions

diff --git a/doc/build/core/tutorial.rst b/doc/build/core/tutorial.rst
index 5f265ef14..5a1eb0b7a 100644
--- a/doc/build/core/tutorial.rst
+++ b/doc/build/core/tutorial.rst
@@ -754,11 +754,11 @@ Using Text
 Our last example really became a handful to type. Going from what one
 understands to be a textual SQL expression into a Python construct which
 groups components together in a programmatic style can be hard. That's why
-SQLAlchemy lets you just use strings too. The :func:`~.expression.text` construct represents
-any textual statement, in a backend-agnostic way.
-To use bind parameters with :func:`~.expression.text`, always use the
-named colon format. Such as below, we create a :func:`~.expression.text` and execute it,
-feeding in the bind parameters to the :meth:`~.Connection.execute` method:
+SQLAlchemy lets you just use strings, for those cases when the SQL
+is already known and there isn't a strong need for the statement to support
+dynamic features.  The :func:`~.expression.text` construct is used
+to compose a textual statement that is passed to the database mostly
+unchanged.  Below, we create a :func:`~.expression.text` object and execute it:
 
 .. sourcecode:: pycon+sql
 
@@ -778,29 +778,41 @@ feeding in the bind parameters to the :meth:`~.Connection.execute` method:
     ('m', 'z', '%@aol.com', '%@msn.com')
     {stop}[(u'Wendy Williams, wendy@aol.com',)]
 
-To gain a "hybrid" approach, the :func:`.select` construct accepts
-:func:`~.expression.text` constructs for most of its arguments.
-Below we combine the usage of :func:`~.expression.text` with our constructed
-:func:`.select` object, by using the :func:`.select` object to structure the
-statement, and strings to provide all the content within the structure. For
-this example, SQLAlchemy is not given any :class:`~sqlalchemy.schema.Column`
-or :class:`~sqlalchemy.schema.Table` objects in any of its expressions, so it
-cannot generate a FROM clause. So we also use the :meth:`~.Select.select_from`
-method, which accepts a :class:`.FromClause` or string expression
-to be placed within the FROM clause:
+Above, we can see that bound parameters are specified in
+:func:`~.expression.text` using the named colon format; this format is
+consistent regardless of database backend.  To send values in for the
+parameters, we passed them into the :meth:`~.Connection.execute` method
+as additional arguments.  Depending on how we are working, we can also
+send values to be associated directly with the :func:`~.expression.text`
+construct using the :meth:`~.TextClause.bindparams` method; if we are
+using datatypes that need special handling as they are received in Python,
+or we'd like to compose our :func:`~.expression.text` object into a larger
+expression, we may also wish to use the :meth:`~.TextClause.columns` method
+in order to specify column return types and names.
+
+:func:`~.expression.text` can also be used freely within a
+:func:`~.expression.select` object, which accepts :func:`~.expression.text`
+objects as an argument for most of its builder functions.
+Below, we combine the usage of :func:`~.expression.text` within a
+:func:`.select` object.  The :func:`~.expression.select` construct provides the "geometry"
+of the statement, and the :func:`~.expression.text` construct provides the
+textual content within this form.  We can build a statement without the
+need to refer to any pre-established :class:`.Table` metadata:
 
 .. sourcecode:: pycon+sql
 
     >>> s = select([
-    ...            text("users.fullname || ', ' || addresses.email_address AS title")
-    ...          ]).\
-    ...           where(
-    ...              and_(
+    ...        text("users.fullname || ', ' || addresses.email_address AS title")
+    ...     ]).\
+    ...         where(
+    ...             and_(
     ...                 text("users.id = addresses.user_id"),
     ...                 text("users.name BETWEEN 'm' AND 'z'"),
-    ...                 text("(addresses.email_address LIKE :x OR addresses.email_address LIKE :y)")
+    ...                 text(
+    ...                     "(addresses.email_address LIKE :x "
+    ...                     "OR addresses.email_address LIKE :y)")
     ...             )
-    ...           ).select_from(text('users, addresses'))
+    ...         ).select_from(text('users, addresses'))
     {sql}>>> conn.execute(s, x='%@aol.com', y='%@msn.com').fetchall() #doctest: +NORMALIZE_WHITESPACE
     SELECT users.fullname || ', ' || addresses.email_address AS title
     FROM users, addresses
@@ -809,7 +821,7 @@ to be placed within the FROM clause:
     ('%@aol.com', '%@msn.com')
     {stop}[(u'Wendy Williams, wendy@aol.com',)]
 
-.. sidebar:: Why not use strings everywhere?
+.. topic:: Why not use strings everywhere?
 
     When we use literal strings, the Core can't adapt our SQL to work
     on different database backends.  Above, our expression won't work
@@ -823,6 +835,123 @@ to be placed within the FROM clause:
     the less flexibility and ability for manipulation/transformation
     the statement will have.
 
+.. _sqlexpression_literal_column:
+
+Using More Specific Text with :func:`.table`, :func:`.literal_column`, and :func:`.column`
+-------------------------------------------------------------------------------------------
+
+We can move our level of structure back in the other direction too,
+by using :func:`~.expression.column`, :func:`~.expression.literal_column`,
+and :func:`~.expression.table` for some of the
+key elements of our statement.   Using these constructs, we can get
+some more expression capabilities than if we used :func:`~.expression.text`
+directly, as they provide to the Core more information about how the strings
+they store are to be used, but still without the need to get into full
+:class:`.Table` based metadata.  Below, we also specify the :class:`.String`
+datatype for two of the key :func:`~.expression.literal_column` objects,
+so that the string-specific concatenation operator becomes available.
+We also use :func:`~.expression.literal_column` in order to use table-qualified
+expressions, e.g. ``users.fullname``, that will be rendered as is;
+using :func:`~.expression.column` implies an individual column name that may
+be quoted:
+
+.. sourcecode:: pycon+sql
+
+    >>> from sqlalchemy import select, and_, text, String
+    >>> from sqlalchemy.sql import table, literal_column
+    >>> s = select([
+    ...    literal_column("users.fullname", String) +
+    ...    ' , ' +
+    ...    literal_column("addresses.email_address").label("title")
+    ... ]).\
+    ...    where(
+    ...        and_(
+    ...            literal_column("users.id") == literal_column("addresses.user_id"),
+    ...            text("users.name BETWEEN 'm' AND 'z'"),
+    ...            text(
+    ...                "(addresses.email_address LIKE :x OR "
+    ...                "addresses.email_address LIKE :y)")
+    ...        )
+    ...    ).select_from(table('users')).select_from(table('addresses'))
+
+    {sql}>>> conn.execute(s, x='%@aol.com', y='%@msn.com').fetchall() #doctest: +NORMALIZE_WHITESPACE
+    SELECT "users.fullname" || ? || "addresses.email_address" AS anon_1
+    FROM users, addresses
+    WHERE "users.id" = "addresses.user_id"
+    AND users.name BETWEEN 'm' AND 'z'
+    AND (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
+    (' , ', '%@aol.com', '%@msn.com')
+    {stop}[(u'Wendy Williams, wendy@aol.com',)]
+
+Ordering or Grouping by a Label
+-------------------------------
+
+One place where we sometimes want to use a string as a shortcut is when
+our statement has some labeled column element that we want to refer to in
+a place such as the "ORDER BY" or "GROUP BY" clause; other candidates include
+fields within an "OVER" or "DISTINCT" clause.  If we have such a label
+in our :func:`.select` construct, we can refer to it directly by passing the
+string straight into :meth:`.select.order_by` or :meth:`.select.group_by`,
+among others.  This will refer to the named label and also prevent the
+expression from being rendered twice:
+
+.. sourcecode:: pycon+sql
+
+    >>> from sqlalchemy import func
+    >>> stmt = select([
+    ...         addresses.c.user_id,
+    ...         func.count(addresses.c.id).label('num_addresses')]).\
+    ...         order_by("num_addresses")
+
+    {sql}>>> conn.execute(stmt).fetchall() # doctest: +NORMALIZE_WHITESPACE
+    SELECT addresses.user_id, count(addresses.id) AS num_addresses
+    FROM addresses ORDER BY num_addresses
+    ()
+    {stop}[(2, 4)]
+
+We can use modifiers like :func:`.asc` or :func:`.desc` by passing the string
+name:
+
+.. sourcecode:: pycon+sql
+
+    >>> from sqlalchemy import func, desc
+    >>> stmt = select([
+    ...         addresses.c.user_id,
+    ...         func.count(addresses.c.id).label('num_addresses')]).\
+    ...         order_by(desc("num_addresses"))
+
+    {sql}>>> conn.execute(stmt).fetchall() # doctest: +NORMALIZE_WHITESPACE
+    SELECT addresses.user_id, count(addresses.id) AS num_addresses
+    FROM addresses ORDER BY num_addresses DESC
+    ()
+    {stop}[(2, 4)]
+
+Note that the string feature here is very much tailored to when we have
+already used the :meth:`~.ColumnElement.label` method to create a
+specifically-named label.  In other cases, we always want to refer to the
+:class:`.ColumnElement` object directly so that the expression system can
+make the most effective choices for rendering.  Below, we illustrate how using
+the :class:`.ColumnElement` eliminates ambiguity when we want to order
+by a column name that appears more than once:
+
+.. sourcecode:: pycon+sql
+
+    >>> u1a, u1b = users.alias(), users.alias()
+    >>> stmt = select([u1a, u1b]).\
+    ...             where(u1a.c.name > u1b.c.name).\
+    ...             order_by(u1a.c.name)  # using "name" here would be ambiguous
+
+    {sql}>>> conn.execute(stmt).fetchall() # doctest: +NORMALIZE_WHITESPACE
+    SELECT users_1.id, users_1.name, users_1.fullname, users_2.id,
+    users_2.name, users_2.fullname
+    FROM users AS users_1, users AS users_2
+    WHERE users_1.name > users_2.name ORDER BY users_1.name
+    ()
+    {stop}[(2, u'wendy', u'Wendy Williams', 1, u'jack', u'Jack Jones')]
+
+
+
+
 Using Aliases
 ==============
 
diff --git a/doc/build/orm/tutorial.rst b/doc/build/orm/tutorial.rst
index d758c92b7..6514ec800 100644
--- a/doc/build/orm/tutorial.rst
+++ b/doc/build/orm/tutorial.rst
@@ -982,16 +982,12 @@ completely "raw", using string names to identify desired columns:
     ('ed',)
     {stop}[(1, u'ed', 12)]
 
-.. sidebar:: Why not use strings everywhere?
-
-    When we use literal strings, the Core can't adapt our SQL to work
-    on different database backends.  We also lose typing information about
-    result columns and bound parameters.  Depending on how much of our
-    SQL is specified as fixed text, the :class:`.Query` object
-    has less ability to perform transformations and adaptations of the
-    SQL statement as a whole, which can impact features such as eager loading,
-    generation of joins and subqueries, and working with compound
-    mappings such as inheritance mappings.
+.. seealso::
+
+    :ref:`sqlexpression_text` - Core description of textual segments.  The
+    behavior of the ORM :class:`.Query` object with regards to
+    :func:`.text` and related constructs is very similar to that of the
+    Core :func:`.select` object.
 
 Counting
 --------
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 6cbf583cc..8cae83169 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -2133,14 +2133,15 @@ class Case(ColumnElement):
 
 
 def literal_column(text, type_=None):
-    """Return a textual column expression, as would be in the columns
-    clause of a ``SELECT`` statement.
-
-    The object returned supports further expressions in the same way as any
-    other column object, including comparison, math and string operations.
-    The type\_ parameter is important to determine proper expression behavior
-    (such as, '+' means string concatenation or numerical addition based on
-    the type).
+    """Produce a :class:`.ColumnClause` object that has the
+    :paramref:`.column.is_literal` flag set to True.
+
+    :func:`.literal_column` is similar to :func:`.column`, except that
+    it is more often used as a "standalone" column expression that renders
+    exactly as stated; while :func:`.column` stores a string name that
+    will be assumed to be part of a table and may be quoted as such,
+    :func:`.literal_column` can be that, or any other arbitrary column-oriented
+    expression.
 
     :param text: the text of the expression; can be any SQL expression.
       Quoting rules will not be applied. To specify a column-name expression
@@ -2152,6 +2153,14 @@ def literal_column(text, type_=None):
       provide result-set translation and additional expression semantics for
       this column. If left as None the type will be NullType.
 
+    .. seealso::
+
+        :func:`.column`
+
+        :func:`.text`
+
+        :ref:`sqlexpression_literal_column`
+
     """
     return ColumnClause(text, type_=type_, is_literal=True)
 
@@ -2965,9 +2974,11 @@ class ColumnClause(Immutable, ColumnElement):
 
             :func:`.literal_column`
 
+            :func:`.table`
+
             :func:`.text`
 
-            :ref:`metadata_toplevel`
+            :ref:`sqlexpression_literal_column`
 
         """