Convert remaining ORM APIs to support 2.0 style

This is kind of a mixed bag of all kinds to help get us
to 1.4 betas.    The documentation stuff is a work in
progress.    Lots of other relatively small changes to
APIs and things.    More commits will follow to continue
improving the documentation and transitioning to the
1.4/2.0 hybrid documentation.  In particular some refinements
to Session usage models so that it can match Engine's
scoping / transactional patterns, and a decision to
start moving away from "subtransactions" completely.

* add select().from_statement() to produce FromStatement in an
  ORM context

* begin referring to select() that has "plugins" for the few edge
  cases where select() will have ORM-only behaviors

* convert dynamic.AppenderQuery to its own object that can use
  select(), though at the moment it uses Query to support legacy
  join calling forms.

* custom query classes for AppenderQuery are replaced by
  do_orm_execute() hooks for custom actions, a separate gerrit
  will document this

* add Session.get() to replace query.get()

* Deprecate session.begin->subtransaction.  propose within the
  test suite a hypothetical recipe for apps that rely on this
  pattern

* introduce Session construction level context manager,
  sessionmaker context manager, rewrite the whole top of the
  session_transaction.rst documentation.   Establish context manager
  patterns for Session that are identical to engine

* ensure same begin_nested() / commit() behavior as engine

* devise all new "join into an external transaction" recipe,
  add test support for it, add rules into Session so it
  just works, write new docs.  need to ensure this doesn't
  break anything

* vastly reduce the verbosity of lots of session docs as
  I dont think people read this stuff and it's difficult
  to keep current in any case

* constructs like case(), with_only_columns() really need
  to move to *columns, add a coercion rule to just change
  these.

* docs need changes everywhere I look.  in_() is not in
  the Core tutorial?  how do people even know about it?
  Remove tons of cruft from Select docs, etc.

* build a system for common ORM options like populate_existing
  and autoflush to populate from execution options.

* others?

Change-Id: Ia4bea0f804250e54d90b3884cf8aab8b66b82ecf

5 files changed, 173 insertions, 86 deletions

diff --git a/test/sql/test_case_statement.py b/test/sql/test_case_statement.py
index 491ff42bc..f2a88bd73 100644
--- a/test/sql/test_case_statement.py
+++ b/test/sql/test_case_statement.py
@@ -57,13 +57,8 @@ class CaseTest(fixtures.TestBase, AssertsCompiledSQL):
         inner = select(
             [
                 case(
-                    [
-                        [info_table.c.pk < 3, "lessthan3"],
-                        [
-                            and_(info_table.c.pk >= 3, info_table.c.pk < 7),
-                            "gt3",
-                        ],
-                    ]
+                    (info_table.c.pk < 3, "lessthan3"),
+                    (and_(info_table.c.pk >= 3, info_table.c.pk < 7), "gt3"),
                 ).label("x"),
                 info_table.c.pk,
                 info_table.c.info,
@@ -80,14 +75,17 @@ class CaseTest(fixtures.TestBase, AssertsCompiledSQL):
         # gt3 4 pk_4_data
         # gt3 5 pk_5_data
         # gt3 6 pk_6_data
-        assert inner_result == [
-            ("lessthan3", 1, "pk_1_data"),
-            ("lessthan3", 2, "pk_2_data"),
-            ("gt3", 3, "pk_3_data"),
-            ("gt3", 4, "pk_4_data"),
-            ("gt3", 5, "pk_5_data"),
-            ("gt3", 6, "pk_6_data"),
-        ]
+        eq_(
+            inner_result,
+            [
+                ("lessthan3", 1, "pk_1_data"),
+                ("lessthan3", 2, "pk_2_data"),
+                ("gt3", 3, "pk_3_data"),
+                ("gt3", 4, "pk_4_data"),
+                ("gt3", 5, "pk_5_data"),
+                ("gt3", 6, "pk_6_data"),
+            ],
+        )
 
         outer = select([inner.alias("q_inner")])
 
@@ -105,10 +103,8 @@ class CaseTest(fixtures.TestBase, AssertsCompiledSQL):
         w_else = select(
             [
                 case(
-                    [
-                        [info_table.c.pk < 3, cast(3, Integer)],
-                        [and_(info_table.c.pk >= 3, info_table.c.pk < 6), 6],
-                    ],
+                    [info_table.c.pk < 3, cast(3, Integer)],
+                    [and_(info_table.c.pk >= 3, info_table.c.pk < 6), 6],
                     else_=0,
                 ).label("x"),
                 info_table.c.pk,
@@ -119,21 +115,24 @@ class CaseTest(fixtures.TestBase, AssertsCompiledSQL):
 
         else_result = w_else.execute().fetchall()
 
-        assert else_result == [
-            (3, 1, "pk_1_data"),
-            (3, 2, "pk_2_data"),
-            (6, 3, "pk_3_data"),
-            (6, 4, "pk_4_data"),
-            (6, 5, "pk_5_data"),
-            (0, 6, "pk_6_data"),
-        ]
+        eq_(
+            else_result,
+            [
+                (3, 1, "pk_1_data"),
+                (3, 2, "pk_2_data"),
+                (6, 3, "pk_3_data"),
+                (6, 4, "pk_4_data"),
+                (6, 5, "pk_5_data"),
+                (0, 6, "pk_6_data"),
+            ],
+        )
 
     def test_literal_interpretation_ambiguous(self):
         assert_raises_message(
             exc.ArgumentError,
             r"Column expression expected, got 'x'",
             case,
-            [("x", "y")],
+            ("x", "y"),
         )
 
     def test_literal_interpretation_ambiguous_tuple(self):
@@ -141,18 +140,18 @@ class CaseTest(fixtures.TestBase, AssertsCompiledSQL):
             exc.ArgumentError,
             r"Column expression expected, got \('x', 'y'\)",
             case,
-            [(("x", "y"), "z")],
+            (("x", "y"), "z"),
         )
 
     def test_literal_interpretation(self):
         t = table("test", column("col1"))
 
         self.assert_compile(
-            case([("x", "y")], value=t.c.col1),
+            case(("x", "y"), value=t.c.col1),
             "CASE test.col1 WHEN :param_1 THEN :param_2 END",
         )
         self.assert_compile(
-            case([(t.c.col1 == 7, "y")], else_="z"),
+            case((t.c.col1 == 7, "y"), else_="z"),
             "CASE WHEN (test.col1 = :col1_1) THEN :param_1 ELSE :param_2 END",
         )
 
@@ -162,7 +161,7 @@ class CaseTest(fixtures.TestBase, AssertsCompiledSQL):
             select(
                 [
                     case(
-                        [(info_table.c.info == "pk_4_data", text("'yes'"))],
+                        (info_table.c.info == "pk_4_data", text("'yes'")),
                         else_=text("'no'"),
                     )
                 ]
@@ -170,36 +169,20 @@ class CaseTest(fixtures.TestBase, AssertsCompiledSQL):
             select(
                 [
                     case(
-                        [
-                            (
-                                info_table.c.info == "pk_4_data",
-                                literal_column("'yes'"),
-                            )
-                        ],
+                        (
+                            info_table.c.info == "pk_4_data",
+                            literal_column("'yes'"),
+                        ),
                         else_=literal_column("'no'"),
                     )
                 ]
             ).order_by(info_table.c.info),
         ]:
-            if testing.against("firebird"):
-                eq_(
-                    s.execute().fetchall(),
-                    [
-                        ("no ",),
-                        ("no ",),
-                        ("no ",),
-                        ("yes",),
-                        ("no ",),
-                        ("no ",),
-                    ],
-                )
-            else:
-                eq_(
-                    s.execute().fetchall(),
-                    [("no",), ("no",), ("no",), ("yes",), ("no",), ("no",)],
-                )
+            eq_(
+                s.execute().fetchall(),
+                [("no",), ("no",), ("no",), ("yes",), ("no",), ("no",)],
+            )
 
-    @testing.fails_on("firebird", "FIXME: unknown")
     def testcase_with_dict(self):
         query = select(
             [
@@ -215,24 +198,27 @@ class CaseTest(fixtures.TestBase, AssertsCompiledSQL):
             ],
             from_obj=[info_table],
         )
-        assert query.execute().fetchall() == [
-            ("lessthan3", 1, "pk_1_data"),
-            ("lessthan3", 2, "pk_2_data"),
-            ("gt3", 3, "pk_3_data"),
-            ("gt3", 4, "pk_4_data"),
-            ("gt3", 5, "pk_5_data"),
-            ("gt3", 6, "pk_6_data"),
-        ]
-
-        simple_query = select(
+        eq_(
+            query.execute().fetchall(),
             [
+                ("lessthan3", 1, "pk_1_data"),
+                ("lessthan3", 2, "pk_2_data"),
+                ("gt3", 3, "pk_3_data"),
+                ("gt3", 4, "pk_4_data"),
+                ("gt3", 5, "pk_5_data"),
+                ("gt3", 6, "pk_6_data"),
+            ],
+        )
+
+        simple_query = (
+            select(
                 case(
                     {1: "one", 2: "two"}, value=info_table.c.pk, else_="other"
                 ),
                 info_table.c.pk,
-            ],
-            whereclause=info_table.c.pk < 4,
-            from_obj=[info_table],
+            )
+            .where(info_table.c.pk < 4)
+            .select_from(info_table)
         )
 
         assert simple_query.execute().fetchall() == [
diff --git a/test/sql/test_compare.py b/test/sql/test_compare.py
index fff5171ef..7ac716dbe 100644
--- a/test/sql/test_compare.py
+++ b/test/sql/test_compare.py
@@ -320,20 +320,15 @@ class CoreFixtures(object):
             ClauseList(table_a.c.a == 5, table_a.c.b == table_a.c.a),
         ),
         lambda: (
-            case(whens=[(table_a.c.a == 5, 10), (table_a.c.a == 10, 20)]),
-            case(whens=[(table_a.c.a == 18, 10), (table_a.c.a == 10, 20)]),
-            case(whens=[(table_a.c.a == 5, 10), (table_a.c.b == 10, 20)]),
+            case((table_a.c.a == 5, 10), (table_a.c.a == 10, 20)),
+            case((table_a.c.a == 18, 10), (table_a.c.a == 10, 20)),
+            case((table_a.c.a == 5, 10), (table_a.c.b == 10, 20)),
             case(
-                whens=[
-                    (table_a.c.a == 5, 10),
-                    (table_a.c.b == 10, 20),
-                    (table_a.c.a == 9, 12),
-                ]
-            ),
-            case(
-                whens=[(table_a.c.a == 5, 10), (table_a.c.a == 10, 20)],
-                else_=30,
+                (table_a.c.a == 5, 10),
+                (table_a.c.b == 10, 20),
+                (table_a.c.a == 9, 12),
             ),
+            case((table_a.c.a == 5, 10), (table_a.c.a == 10, 20), else_=30,),
             case({"wendy": "W", "jack": "J"}, value=table_a.c.a, else_="E"),
             case({"wendy": "W", "jack": "J"}, value=table_a.c.b, else_="E"),
             case({"wendy_w": "W", "jack": "J"}, value=table_a.c.a, else_="E"),
diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index 7f06aa0d1..d79d00555 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -4050,7 +4050,7 @@ class KwargPropagationTest(fixtures.TestBase):
         self._do_test(s)
 
     def test_case(self):
-        c = case([(self.criterion, self.column)], else_=self.column)
+        c = case((self.criterion, self.column), else_=self.column)
         self._do_test(c)
 
     def test_cast(self):
diff --git a/test/sql/test_deprecations.py b/test/sql/test_deprecations.py
index e68b1398b..871d09e04 100644
--- a/test/sql/test_deprecations.py
+++ b/test/sql/test_deprecations.py
@@ -3,6 +3,7 @@
 from sqlalchemy import alias
 from sqlalchemy import and_
 from sqlalchemy import bindparam
+from sqlalchemy import case
 from sqlalchemy import CHAR
 from sqlalchemy import column
 from sqlalchemy import create_engine
@@ -488,6 +489,111 @@ class SelectableTest(fixtures.TestBase, AssertsCompiledSQL):
             "SELECT anon_1.a FROM (SELECT 1 AS a ORDER BY 1) AS anon_1",
         )
 
+    def test_case_list_legacy(self):
+        t1 = table("t", column("q"))
+
+        with testing.expect_deprecated(
+            r"The \"whens\" argument to case\(\) is now passed"
+        ):
+            stmt = select(t1).where(
+                case(
+                    [(t1.c.q == 5, "foo"), (t1.c.q == 10, "bar")], else_="bat"
+                )
+                != "bat"
+            )
+
+        self.assert_compile(
+            stmt,
+            "SELECT t.q FROM t WHERE CASE WHEN (t.q = :q_1) "
+            "THEN :param_1 WHEN (t.q = :q_2) THEN :param_2 "
+            "ELSE :param_3 END != :param_4",
+        )
+
+    def test_case_whens_kw(self):
+        t1 = table("t", column("q"))
+
+        with testing.expect_deprecated(
+            r"The \"whens\" argument to case\(\) is now passed"
+        ):
+            stmt = select(t1).where(
+                case(
+                    whens=[(t1.c.q == 5, "foo"), (t1.c.q == 10, "bar")],
+                    else_="bat",
+                )
+                != "bat"
+            )
+
+        self.assert_compile(
+            stmt,
+            "SELECT t.q FROM t WHERE CASE WHEN (t.q = :q_1) "
+            "THEN :param_1 WHEN (t.q = :q_2) THEN :param_2 "
+            "ELSE :param_3 END != :param_4",
+        )
+
+    def test_case_whens_dict_kw(self):
+        t1 = table("t", column("q"))
+
+        with testing.expect_deprecated(
+            r"The \"whens\" argument to case\(\) is now passed"
+        ):
+            stmt = select(t1).where(
+                case(whens={t1.c.q == 5: "foo"}, else_="bat",) != "bat"
+            )
+
+        self.assert_compile(
+            stmt,
+            "SELECT t.q FROM t WHERE CASE WHEN (t.q = :q_1) THEN "
+            ":param_1 ELSE :param_2 END != :param_3",
+        )
+
+    def test_case_kw_arg_detection(self):
+        # because we support py2k, case() has to parse **kw for now
+
+        assert_raises_message(
+            TypeError,
+            "unknown arguments: bat, foo",
+            case,
+            (column("x") == 10, 5),
+            else_=15,
+            foo="bar",
+            bat="hoho",
+        )
+
+    def test_with_only_generative(self):
+        table1 = table(
+            "table1",
+            column("col1"),
+            column("col2"),
+            column("col3"),
+            column("colx"),
+        )
+        s1 = table1.select().scalar_subquery()
+
+        with testing.expect_deprecated(
+            r"The \"columns\" argument to "
+            r"Select.with_only_columns\(\) is now passed"
+        ):
+            stmt = s1.with_only_columns([s1])
+        self.assert_compile(
+            stmt,
+            "SELECT (SELECT table1.col1, table1.col2, "
+            "table1.col3, table1.colx FROM table1) AS anon_1",
+        )
+
+    def test_from_list_with_columns(self):
+        table1 = table("t1", column("a"))
+        table2 = table("t2", column("b"))
+        s1 = select(table1.c.a, table2.c.b)
+        self.assert_compile(s1, "SELECT t1.a, t2.b FROM t1, t2")
+
+        with testing.expect_deprecated(
+            r"The \"columns\" argument to "
+            r"Select.with_only_columns\(\) is now passed"
+        ):
+            s2 = s1.with_only_columns([table2.c.b])
+
+        self.assert_compile(s2, "SELECT t2.b FROM t2")
+
     def test_column(self):
         stmt = select(column("x"))
         with testing.expect_deprecated(
@@ -815,7 +921,7 @@ class DeprecatedAppendMethTest(fixtures.TestBase, AssertsCompiledSQL):
     def test_append_column(self):
         t1 = table("t1", column("q"), column("p"))
         stmt = select(t1.c.q)
-        with self._expect_deprecated("Select", "column", "column"):
+        with self._expect_deprecated("Select", "column", "add_columns"):
             stmt.append_column(t1.c.p)
         self.assert_compile(stmt, "SELECT t1.q, t1.p FROM t1")
 
diff --git a/test/sql/test_selectable.py b/test/sql/test_selectable.py
index 55875632a..01c8d7ca6 100644
--- a/test/sql/test_selectable.py
+++ b/test/sql/test_selectable.py
@@ -479,7 +479,7 @@ class SelectableTest(
     def test_with_only_generative(self):
         s1 = table1.select().scalar_subquery()
         self.assert_compile(
-            s1.with_only_columns([s1]),
+            s1.with_only_columns(s1),
             "SELECT (SELECT table1.col1, table1.col2, "
             "table1.col3, table1.colx FROM table1) AS anon_1",
         )
@@ -1165,12 +1165,12 @@ class SelectableTest(
         table2 = table("t2", column("b"))
         s1 = select(table1.c.a, table2.c.b)
         self.assert_compile(s1, "SELECT t1.a, t2.b FROM t1, t2")
-        s2 = s1.with_only_columns([table2.c.b])
+        s2 = s1.with_only_columns(table2.c.b)
         self.assert_compile(s2, "SELECT t2.b FROM t2")
 
         s3 = sql_util.ClauseAdapter(table1).traverse(s1)
         self.assert_compile(s3, "SELECT t1.a, t2.b FROM t1, t2")
-        s4 = s3.with_only_columns([table2.c.b])
+        s4 = s3.with_only_columns(table2.c.b)
         self.assert_compile(s4, "SELECT t2.b FROM t2")
 
     def test_from_list_against_existing_one(self):