- [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".

4 files changed, 229 insertions, 27 deletions

diff --git a/test/aaa_profiling/test_compiler.py b/test/aaa_profiling/test_compiler.py
index 129d0bf06..deff49f0f 100644
--- a/test/aaa_profiling/test_compiler.py
+++ b/test/aaa_profiling/test_compiler.py
@@ -34,20 +34,30 @@ class CompileTest(fixtures.TestBase, AssertsExecutionResults):
 
         cls.dialect = default.DefaultDialect()
 
-    @profiling.function_call_count(62)
+    @profiling.function_call_count()
     def test_insert(self):
         t1.insert().compile(dialect=self.dialect)
 
-    @profiling.function_call_count(56)
+    @profiling.function_call_count()
     def test_update(self):
         t1.update().compile(dialect=self.dialect)
 
-    @profiling.function_call_count(110)
     def test_update_whereclause(self):
-        t1.update().where(t1.c.c2==12).compile(dialect=self.dialect)
+        t1.update().where(t1.c.c2 == 12).compile(dialect=self.dialect)
+
+        @profiling.function_call_count()
+        def go():
+            t1.update().where(t1.c.c2 == 12).compile(dialect=self.dialect)
+        go()
 
-    @profiling.function_call_count(139)
     def test_select(self):
-        s = select([t1], t1.c.c2==t2.c.c1)
+        # give some of the cached type values
+        # a chance to warm up
+        s = select([t1], t1.c.c2 == t2.c.c1)
         s.compile(dialect=self.dialect)
 
+        @profiling.function_call_count()
+        def go():
+            s = select([t1], t1.c.c2 == t2.c.c1)
+            s.compile(dialect=self.dialect)
+        go()
\ No newline at end of file
diff --git a/test/lib/profiles.txt b/test/lib/profiles.txt
index edbee75e9..707ecb621 100644
--- a/test/lib/profiles.txt
+++ b/test/lib/profiles.txt
@@ -1,15 +1,15 @@
-# /Users/classic/dev/sqla_comparators/./test/lib/profiles.txt
+# /Users/classic/dev/sqlalchemy/./test/lib/profiles.txt
 # This file is written out on a per-environment basis.
-# For each test in aaa_profiling, the corresponding function and
+# For each test in aaa_profiling, the corresponding function and 
 # environment is located within this file.  If it doesn't exist,
 # the test is skipped.
-# If a callcount does exist, it is compared to what we received.
+# If a callcount does exist, it is compared to what we received. 
 # assertions are raised if the counts do not match.
-#
-# To add a new callcount test, apply the function_call_count
-# decorator and re-run the tests using the --write-profiles option -
+# 
+# To add a new callcount test, apply the function_call_count 
+# decorator and re-run the tests using the --write-profiles option - 
 # this file will be rewritten including the new count.
-#
+# 
 
 # TEST: test.aaa_profiling.test_compiler.CompileTest.test_insert
 
@@ -26,16 +26,12 @@ test.aaa_profiling.test_compiler.CompileTest.test_insert 3.2_sqlite_pysqlite_noc
 
 # TEST: test.aaa_profiling.test_compiler.CompileTest.test_select
 
-test.aaa_profiling.test_compiler.CompileTest.test_select 2.5_sqlite_pysqlite_nocextensions 149
-test.aaa_profiling.test_compiler.CompileTest.test_select 2.6_sqlite_pysqlite_nocextensions 149
-test.aaa_profiling.test_compiler.CompileTest.test_select 2.7_mysql_mysqldb_cextensions 149
-test.aaa_profiling.test_compiler.CompileTest.test_select 2.7_mysql_mysqldb_nocextensions 149
-test.aaa_profiling.test_compiler.CompileTest.test_select 2.7_postgresql_psycopg2_cextensions 149
-test.aaa_profiling.test_compiler.CompileTest.test_select 2.7_postgresql_psycopg2_nocextensions 149
-test.aaa_profiling.test_compiler.CompileTest.test_select 2.7_sqlite_pysqlite_cextensions 149
-test.aaa_profiling.test_compiler.CompileTest.test_select 2.7_sqlite_pysqlite_nocextensions 149
-test.aaa_profiling.test_compiler.CompileTest.test_select 3.2_postgresql_psycopg2_nocextensions 161
-test.aaa_profiling.test_compiler.CompileTest.test_select 3.2_sqlite_pysqlite_nocextensions 161
+test.aaa_profiling.test_compiler.CompileTest.test_select 2.7_postgresql_psycopg2_nocextensions 133
+test.aaa_profiling.test_compiler.CompileTest.test_select 2.7_sqlite_pysqlite_nocextensions 133
+
+# TEST: test.aaa_profiling.test_compiler.CompileTest.test_select_second_time
+
+test.aaa_profiling.test_compiler.CompileTest.test_select_second_time 2.7_sqlite_pysqlite_nocextensions 133
 
 # TEST: test.aaa_profiling.test_compiler.CompileTest.test_update
 
@@ -195,6 +191,8 @@ test.aaa_profiling.test_resultset.ExecutionTest.test_minimal_engine_execute 3.2_
 
 # TEST: test.aaa_profiling.test_resultset.ResultSetTest.test_contains_doesnt_compile
 
+test.aaa_profiling.test_resultset.ResultSetTest.test_contains_doesnt_compile 2.7_postgresql_psycopg2_nocextensions 14
+test.aaa_profiling.test_resultset.ResultSetTest.test_contains_doesnt_compile 2.7_sqlite_pysqlite_nocextensions 14
 
 # TEST: test.aaa_profiling.test_resultset.ResultSetTest.test_string
 
@@ -236,9 +234,7 @@ test.aaa_profiling.test_zoomark.ZooMarkTest.test_profile_2_insert 3.2_postgresql
 
 # TEST: test.aaa_profiling.test_zoomark.ZooMarkTest.test_profile_3_properties
 
-test.aaa_profiling.test_zoomark.ZooMarkTest.test_profile_3_properties 2.7_postgresql_psycopg2_cextensions 3093
-test.aaa_profiling.test_zoomark.ZooMarkTest.test_profile_3_properties 2.7_postgresql_psycopg2_nocextensions 3317
-test.aaa_profiling.test_zoomark.ZooMarkTest.test_profile_3_properties 3.2_postgresql_psycopg2_nocextensions 3094
+test.aaa_profiling.test_zoomark.ZooMarkTest.test_profile_3_properties 2.7_postgresql_psycopg2_nocextensions 3526
 
 # TEST: test.aaa_profiling.test_zoomark.ZooMarkTest.test_profile_4_expressions
 
diff --git a/test/sql/test_returning.py b/test/sql/test_returning.py
index 4be2d74f7..ac5c69e52 100644
--- a/test/sql/test_returning.py
+++ b/test/sql/test_returning.py
@@ -143,7 +143,7 @@ class ReturningTest(fixtures.TestBase, AssertsExecutionResults):
         eq_(result2.fetchall(), [(2,False),])
 
 class SequenceReturningTest(fixtures.TestBase):
-    __requires__ = 'returning',
+    __requires__ = 'returning', 'sequences'
 
     def setup(self):
         meta = MetaData(testing.db)
diff --git a/test/sql/test_type_expressions.py b/test/sql/test_type_expressions.py
new file mode 100644
index 000000000..d64ee7f0e
--- /dev/null
+++ b/test/sql/test_type_expressions.py
@@ -0,0 +1,196 @@
+from sqlalchemy import Table, Column, String, func, MetaData, select
+from test.lib import fixtures, AssertsCompiledSQL, testing
+from test.lib.testing import eq_
+
+class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
+    __dialect__ = 'default'
+
+    def _fixture(self):
+        class MyString(String):
+            def bind_expression(self, bindvalue):
+                return func.lower(bindvalue)
+
+            def column_expression(self, col):
+                return func.lower(col)
+
+        test_table = Table(
+                'test_table',
+                MetaData(), Column('x', String), Column('y', MyString)
+        )
+        return test_table
+
+    def test_select_cols(self):
+        table = self._fixture()
+
+        self.assert_compile(
+            select([table]),
+            "SELECT test_table.x, lower(test_table.y) AS y_1 FROM test_table"
+        )
+
+    def test_select_cols_use_labels(self):
+        table = self._fixture()
+
+        self.assert_compile(
+            select([table]).apply_labels(),
+            "SELECT test_table.x AS test_table_x, "
+            "lower(test_table.y) AS test_table_y FROM test_table"
+        )
+
+    def test_select_cols_use_labels_result_map_targeting(self):
+        table = self._fixture()
+
+        compiled = select([table]).apply_labels().compile()
+        assert table.c.y in compiled.result_map['test_table_y'][1]
+        assert table.c.x in compiled.result_map['test_table_x'][1]
+
+        # the lower() function goes into the result_map, we don't really
+        # need this but it's fine
+        self.assert_compile(
+            compiled.result_map['test_table_y'][1][1],
+            "lower(test_table.y)"
+        )
+        # then the original column gets put in there as well.
+        # it's not important that it's the last value.
+        self.assert_compile(
+            compiled.result_map['test_table_y'][1][-1],
+            "test_table.y"
+        )
+
+    def test_insert_binds(self):
+        table = self._fixture()
+
+        self.assert_compile(
+                table.insert(),
+                "INSERT INTO test_table (x, y) VALUES (:x, lower(:y))"
+        )
+
+        self.assert_compile(
+                table.insert().values(y="hi"),
+                "INSERT INTO test_table (y) VALUES (lower(:y))"
+        )
+
+    def test_select_binds(self):
+        table = self._fixture()
+        self.assert_compile(
+            select([table]).where(table.c.y == "hi"),
+            "SELECT test_table.x, lower(test_table.y) AS y_1 FROM "
+            "test_table WHERE test_table.y = lower(:y_2)"
+        )
+
+class RoundTripTest(fixtures.TablesTest):
+    @classmethod
+    def define_tables(cls, metadata):
+        class MyString(String):
+            def bind_expression(self, bindvalue):
+                return func.lower(bindvalue)
+
+            def column_expression(self, col):
+                return func.upper(col)
+
+        Table(
+                'test_table',
+                metadata,
+                    Column('x', String(50)),
+                    Column('y', MyString(50)
+                )
+        )
+
+    def test_round_trip(self):
+        testing.db.execute(
+            self.tables.test_table.insert(),
+            {"x": "X1", "y": "Y1"},
+            {"x": "X2", "y": "Y2"},
+            {"x": "X3", "y": "Y3"},
+        )
+
+        # test insert coercion alone
+        eq_(
+            testing.db.execute(
+                "select * from test_table order by y").fetchall(),
+            [
+                ("X1", "y1"),
+                ("X2", "y2"),
+                ("X3", "y3"),
+            ]
+        )
+
+        # conversion back to upper
+        eq_(
+            testing.db.execute(
+                select([self.tables.test_table]).\
+                order_by(self.tables.test_table.c.y)
+            ).fetchall(),
+            [
+                ("X1", "Y1"),
+                ("X2", "Y2"),
+                ("X3", "Y3"),
+            ]
+        )
+
+    def test_targeting_no_labels(self):
+        testing.db.execute(
+            self.tables.test_table.insert(),
+            {"x": "X1", "y": "Y1"},
+        )
+        row = testing.db.execute(select([self.tables.test_table])).first()
+        eq_(
+            row[self.tables.test_table.c.y],
+            "Y1"
+        )
+
+    def test_targeting_apply_labels(self):
+        testing.db.execute(
+            self.tables.test_table.insert(),
+            {"x": "X1", "y": "Y1"},
+        )
+        row = testing.db.execute(select([self.tables.test_table]).
+                        apply_labels()).first()
+        eq_(
+            row[self.tables.test_table.c.y],
+            "Y1"
+        )
+
+    @testing.fails_if(lambda: True, "still need to propagate "
+                "result_map more effectively")
+    def test_targeting_individual_labels(self):
+        testing.db.execute(
+            self.tables.test_table.insert(),
+            {"x": "X1", "y": "Y1"},
+        )
+        row = testing.db.execute(select([
+                    self.tables.test_table.c.x.label('xbar'),
+                    self.tables.test_table.c.y.label('ybar')
+                ])).first()
+        eq_(
+            row[self.tables.test_table.c.y],
+            "Y1"
+        )
+
+class ReturningTest(fixtures.TablesTest):
+    __requires__ = 'returning',
+
+    @classmethod
+    def define_tables(cls, metadata):
+        class MyString(String):
+            def column_expression(self, col):
+                return func.lower(col)
+
+        Table(
+                'test_table',
+                metadata, Column('x', String(50)),
+                    Column('y', MyString(50), server_default="YVALUE")
+        )
+
+    @testing.provide_metadata
+    def test_insert_returning(self):
+        table = self.tables.test_table
+        result = testing.db.execute(
+                table.insert().returning(table.c.y),
+                {"x": "xvalue"}
+        )
+        eq_(
+            result.first(),
+            ("yvalue",)
+        )
+
+