- [feature] To complement [ticket:2547], types

can now provide "bind expressions" and
"column expressions" which allow compile-time
injection of SQL expressions into statements
on a per-column or per-bind level.   This is
to suit the use case of a type which needs
to augment bind- and result- behavior at the
SQL level, as opposed to in the Python level.
Allows for schemes like transparent encryption/
decryption, usage of Postgis functions, etc.
[ticket:1534]
- update postgis example fully.
- still need to repair the result map propagation
here to be transparent for cases like "labeled column".

6 files changed, 137 insertions, 86 deletions

diff --git a/lib/sqlalchemy/dialects/access/base.py b/lib/sqlalchemy/dialects/access/base.py
index f107c9c8c..1f119098b 100644
--- a/lib/sqlalchemy/dialects/access/base.py
+++ b/lib/sqlalchemy/dialects/access/base.py
@@ -361,13 +361,6 @@ class AccessCompiler(compiler.SQLCompiler):
         """Access uses "mod" instead of "%" """
         return binary.operator == '%' and 'mod' or binary.operator
 
-    def label_select_column(self, select, column, asfrom):
-        if isinstance(column, expression.Function):
-            return column.label()
-        else:
-            return super(AccessCompiler, self).\
-                        label_select_column(select, column, asfrom)
-
     function_rewrites =  {'current_date':       'now',
                           'current_timestamp':  'now',
                           'length':             'len',
diff --git a/lib/sqlalchemy/dialects/firebird/base.py b/lib/sqlalchemy/dialects/firebird/base.py
index ad6dcee54..f7877a901 100644
--- a/lib/sqlalchemy/dialects/firebird/base.py
+++ b/lib/sqlalchemy/dialects/firebird/base.py
@@ -278,15 +278,11 @@ class FBCompiler(sql.compiler.SQLCompiler):
         return ""
 
     def returning_clause(self, stmt, returning_cols):
-
         columns = [
-                self.process(
-                    self.label_select_column(None, c, asfrom=False),
-                    within_columns_clause=True,
-                    result_map=self.result_map
-                )
+                self._label_select_column(None, c, True, False, {})
                 for c in expression._select_iterables(returning_cols)
             ]
+
         return 'RETURNING ' + ', '.join(columns)
 
 
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 83f6346a7..0dd610788 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -849,7 +849,7 @@ class MSSQLCompiler(compiler.SQLCompiler):
         return ("ROLLBACK TRANSACTION %s"
                 % self.preparer.format_savepoint(savepoint_stmt))
 
-    def visit_column(self, column, result_map=None, **kwargs):
+    def visit_column(self, column, add_to_result_map=None, **kwargs):
         if column.table is not None and \
             (not self.isupdate and not self.isdelete) or self.is_subquery():
             # translate for schema-qualified table aliases
@@ -858,20 +858,19 @@ class MSSQLCompiler(compiler.SQLCompiler):
                 converted = expression._corresponding_column_or_error(
                                         t, column)
 
-                if result_map is not None:
-                    result_map[column.name
+                if add_to_result_map is not None:
+                    self.result_map[column.name
                                 if self.dialect.case_sensitive
                                 else column.name.lower()] = \
-                                    (column.name, (column, ),
+                                    (column.name, (column, ) + add_to_result_map,
                                                     column.type)
 
                 return super(MSSQLCompiler, self).\
                                 visit_column(converted,
                                             result_map=None, **kwargs)
 
-        return super(MSSQLCompiler, self).visit_column(column,
-                                                       result_map=result_map,
-                                                       **kwargs)
+        return super(MSSQLCompiler, self).visit_column(
+                        column, add_to_result_map=add_to_result_map, **kwargs)
 
     def visit_binary(self, binary, **kwargs):
         """Move bind parameters to the right-hand side of an operator, where
@@ -898,21 +897,13 @@ class MSSQLCompiler(compiler.SQLCompiler):
             target = stmt.table.alias("deleted")
 
         adapter = sql_util.ClauseAdapter(target)
-        def col_label(col):
-            adapted = adapter.traverse(col)
-            if isinstance(col, expression.Label):
-                return adapted.label(c.key)
-            else:
-                return self.label_select_column(None, adapted, asfrom=False)
 
         columns = [
-            self.process(
-                col_label(c),
-                within_columns_clause=True,
-                result_map=self.result_map
-            )
-            for c in expression._select_iterables(returning_cols)
-        ]
+                self._label_select_column(None, adapter.traverse(c),
+                                    True, False, {})
+                for c in expression._select_iterables(returning_cols)
+            ]
+
         return 'OUTPUT ' + ', '.join(columns)
 
     def get_cte_preamble(self, recursive):
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 36da14d33..d159649e0 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -691,10 +691,7 @@ class PGCompiler(compiler.SQLCompiler):
     def returning_clause(self, stmt, returning_cols):
 
         columns = [
-                self.process(
-                    self.label_select_column(None, c, asfrom=False),
-                    within_columns_clause=True,
-                    result_map=self.result_map)
+                self._label_select_column(None, c, True, False, {})
                 for c in expression._select_iterables(returning_cols)
             ]
 
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 300cdb6b4..f975225d6 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -172,6 +172,7 @@ class _CompileLabel(visitors.Visitable):
     def quote(self):
         return self.element.quote
 
+
 class SQLCompiler(engine.Compiled):
     """Default implementation of Compiled.
 
@@ -373,7 +374,8 @@ class SQLCompiler(engine.Compiled):
     def visit_grouping(self, grouping, asfrom=False, **kwargs):
         return "(" + grouping.element._compiler_dispatch(self, **kwargs) + ")"
 
-    def visit_label(self, label, result_map=None,
+    def visit_label(self, label,
+                            add_to_result_map = None,
                             within_label_clause=False,
                             within_columns_clause=False, **kw):
         # only render labels within the columns clause
@@ -385,14 +387,18 @@ class SQLCompiler(engine.Compiled):
             else:
                 labelname = label.name
 
-            if result_map is not None:
-                result_map[labelname
+            if add_to_result_map is not None:
+                self.result_map[
+                        labelname
                             if self.dialect.case_sensitive
-                            else labelname.lower()] = (
-                        label.name,
-                        (label, label.element, labelname, ) +
-                            label._alt_names,
-                        label.type)
+                            else labelname.lower()
+                        ] = (
+                            label.name,
+                            (label, label.element, labelname, ) +
+                                label._alt_names +
+                                add_to_result_map,
+                            label.type,
+                        )
 
             return label.element._compiler_dispatch(self,
                                     within_columns_clause=True,
@@ -405,7 +411,7 @@ class SQLCompiler(engine.Compiled):
                                     within_columns_clause=False,
                                     **kw)
 
-    def visit_column(self, column, result_map=None, **kwargs):
+    def visit_column(self, column, add_to_result_map=None, **kwargs):
         name = orig_name = column.name
         if name is None:
             raise exc.CompileError("Cannot compile Column object until "
@@ -415,12 +421,16 @@ class SQLCompiler(engine.Compiled):
         if not is_literal and isinstance(name, sql._truncated_label):
             name = self._truncated_identifier("colident", name)
 
-        if result_map is not None:
-            result_map[name
+        if add_to_result_map is not None:
+            self.result_map[
+                        name
                         if self.dialect.case_sensitive
-                        else name.lower()] = (orig_name,
-                                        (column, name, column.key),
-                                        column.type)
+                        else name.lower()
+                    ] = (
+                        orig_name,
+                        (column, name, column.key) + add_to_result_map,
+                        column.type
+                    )
 
         if is_literal:
             name = self.escape_literal_column(name)
@@ -527,7 +537,7 @@ class SQLCompiler(engine.Compiled):
                     cast.typeclause._compiler_dispatch(self, **kwargs))
 
     def visit_over(self, over, **kwargs):
-        x ="%s OVER (" % over.func._compiler_dispatch(self, **kwargs)
+        x = "%s OVER (" % over.func._compiler_dispatch(self, **kwargs)
         if over.partition_by is not None:
             x += "PARTITION BY %s" % \
                 over.partition_by._compiler_dispatch(self, **kwargs)
@@ -544,12 +554,13 @@ class SQLCompiler(engine.Compiled):
         return "EXTRACT(%s FROM %s)" % (field,
                             extract.expr._compiler_dispatch(self, **kwargs))
 
-    def visit_function(self, func, result_map=None, **kwargs):
-        if result_map is not None:
-            result_map[func.name
+    def visit_function(self, func, add_to_result_map=None, **kwargs):
+        if add_to_result_map is not None:
+            self.result_map[
+                        func.name
                         if self.dialect.case_sensitive
-                        else func.name.lower()] = \
-                        (func.name, None, func.type)
+                        else func.name.lower()
+                    ] = (func.name, add_to_result_map, func.type)
 
         disp = getattr(self, "visit_%s_func" % func.name.lower(), None)
         if disp:
@@ -557,14 +568,15 @@ class SQLCompiler(engine.Compiled):
         else:
             name = FUNCTIONS.get(func.__class__, func.name + "%(expr)s")
             return ".".join(list(func.packagenames) + [name]) % \
-                            {'expr':self.function_argspec(func, **kwargs)}
+                            {'expr': self.function_argspec(func, **kwargs)}
 
     def visit_next_value_func(self, next_value, **kw):
         return self.visit_sequence(next_value.sequence)
 
     def visit_sequence(self, sequence):
         raise NotImplementedError(
-            "Dialect '%s' does not support sequence increments." % self.dialect.name
+            "Dialect '%s' does not support sequence increments." %
+            self.dialect.name
         )
 
     def function_argspec(self, func, **kwargs):
@@ -704,7 +716,14 @@ class SQLCompiler(engine.Compiled):
 
 
     def visit_bindparam(self, bindparam, within_columns_clause=False,
-                                            literal_binds=False, **kwargs):
+                                            literal_binds=False,
+                                            skip_bind_expression=False,
+                                            **kwargs):
+
+        if not skip_bind_expression and bindparam.type._has_bind_expression:
+            bind_expression = bindparam.type.bind_expression(bindparam)
+            return self.process(bind_expression,
+                                skip_bind_expression=True)
 
         if literal_binds or \
             (within_columns_clause and \
@@ -912,17 +931,31 @@ class SQLCompiler(engine.Compiled):
         else:
             return alias.original._compiler_dispatch(self, **kwargs)
 
-    def label_select_column(self, select, column, asfrom):
-        """label columns present in a select()."""
+    def _label_select_column(self, select, column, populate_result_map,
+                                    asfrom, column_clause_args):
+        """produce labeled columns present in a select()."""
+
+        if column.type._has_column_expression:
+            col_expr = column.type.column_expression(column)
+            if populate_result_map:
+                add_to_result_map = (column, )
+            else:
+                add_to_result_map = None
+        else:
+            col_expr = column
+            if populate_result_map:
+                add_to_result_map = ()
+            else:
+                add_to_result_map = None
 
-        if isinstance(column, sql.Label):
-            return column
+        if isinstance(col_expr, sql.Label):
+            result_expr = col_expr
 
         elif select is not None and \
                 select.use_labels and \
                 column._label:
-            return _CompileLabel(
-                    column,
+            result_expr = _CompileLabel(
+                    col_expr,
                     column._label,
                     alt_names=(column._key_label, )
                 )
@@ -933,15 +966,25 @@ class SQLCompiler(engine.Compiled):
             not column.is_literal and \
             column.table is not None and \
             not isinstance(column.table, sql.Select):
-            return _CompileLabel(column, sql._as_truncated(column.name),
-                                        alt_names=(column.key,))
+            result_expr = _CompileLabel(col_expr,
+                                    sql._as_truncated(column.name),
+                                    alt_names=(column.key,))
         elif not isinstance(column,
                     (sql.UnaryExpression, sql.TextClause)) \
                 and (not hasattr(column, 'name') or \
                         isinstance(column, sql.Function)):
-            return _CompileLabel(column, column.anon_label)
+            result_expr = _CompileLabel(col_expr, column.anon_label)
+        elif col_expr is not column:
+            result_expr = _CompileLabel(col_expr, column.anon_label)
         else:
-            return column
+            result_expr = col_expr
+
+        return result_expr._compiler_dispatch(
+                       self, within_columns_clause=True,
+                        add_to_result_map=add_to_result_map,
+                        **column_clause_args
+                    )
+
 
     def format_from_hint_text(self, sqltext, table, hint, iscrud):
         hinttext = self.get_from_hint_text(table, hint)
@@ -976,24 +1019,21 @@ class SQLCompiler(engine.Compiled):
         # to outermost if existingfroms: correlate_froms =
         # correlate_froms.union(existingfroms)
 
-        self.stack.append({'from': correlate_froms, 'iswrapper'
-                          : iswrapper})
+        self.stack.append({'from': correlate_froms,
+                            'iswrapper': iswrapper})
 
-        if compound_index==1 and not entry or entry.get('iswrapper', False):
-            column_clause_args = {'result_map':self.result_map,
-                                    'positional_names':positional_names}
-        else:
-            column_clause_args = {'positional_names':positional_names}
+        populate_result_map = compound_index == 1 and not entry or \
+                                entry.get('iswrapper', False)
+        column_clause_args = {'positional_names': positional_names}
 
         # the actual list of columns to print in the SELECT column list.
         inner_columns = [
             c for c in [
-                self.label_select_column(select, co, asfrom=asfrom).\
-                    _compiler_dispatch(self,
-                        within_columns_clause=True,
-                        **column_clause_args)
-                for co in util.unique_list(select.inner_columns)
-            ]
+                self._label_select_column(select, column,
+                                    populate_result_map, asfrom,
+                                    column_clause_args)
+                for column in util.unique_list(select.inner_columns)
+                ]
             if c is not None
         ]
 
@@ -1059,8 +1099,8 @@ class SQLCompiler(engine.Compiled):
             text += self.for_update_clause(select)
 
         if self.ctes and \
-            compound_index==1 and not entry:
-            text  = self._render_cte_clause() + text
+            compound_index == 1 and not entry:
+            text = self._render_cte_clause() + text
 
         self.stack.pop(-1)
 
diff --git a/lib/sqlalchemy/types.py b/lib/sqlalchemy/types.py
index bbeebf5d3..ee262b56b 100644
--- a/lib/sqlalchemy/types.py
+++ b/lib/sqlalchemy/types.py
@@ -25,7 +25,7 @@ import codecs
 
 from . import exc, schema, util, processors, events, event
 from .sql import operators
-from .sql.expression import _DefaultColumnComparator
+from .sql.expression import _DefaultColumnComparator, column, bindparam
 from .util import pickle
 from .util.compat import decimal
 from .sql.visitors import Visitable
@@ -163,6 +163,40 @@ class TypeEngine(AbstractType):
         """
         return None
 
+    def column_expression(self, colexpr):
+        """Given a SELECT column expression, return a wrapping SQL expression."""
+
+        return None
+
+    @util.memoized_property
+    def _has_column_expression(self):
+        """memoized boolean, check if column_expression is implemented."""
+        return self.column_expression(column('x')) is not None
+
+    def bind_expression(self, bindvalue):
+        """"Given a bind value (i.e. a :class:`.BindParameter` instance),
+        return a SQL expression in its place.
+
+        This is typically a SQL function that wraps the existing value
+        in a bind.   It is used for special data types that require
+        literals being wrapped in some special database function in all
+        cases, such as Postgis GEOMETRY types.
+
+        The method is evaluated at statement compile time, as opposed
+        to statement construction time.
+
+        Note that this method, when implemented, should always return
+        the exact same structure, without any conditional logic, as it
+        will be used during executemany() calls as well.
+
+        """
+        return None
+
+    @util.memoized_property
+    def _has_bind_expression(self):
+        """memoized boolean, check if bind_expression is implemented."""
+        return self.bind_expression(bindparam('x')) is not None
+
     def compare_values(self, x, y):
         """Compare two values for equality."""