pep484: schema API

implement strict typing for schema.py

this module has lots of public API, lots of old decisions
and very hard to follow construction sequences in many
cases, and is also where we get a lot of new feature requests,
so strict typing should help keep things clean.

among improvements here, fixed the pool .info getters
and also figured out how to get ColumnCollection and
related to be covariant so that we may set them up
as returning Column or ColumnClause without any conflicts.

DDL was affected, noting that superclasses of DDLElement
(_DDLCompiles, added recently) can now be passed into
"ddl_if" callables; reorganized ddl into ExecutableDDLElement
as a new name for DDLElement and _DDLCompiles renamed to
BaseDDLElement.

setting up strict also located an API use case that
is completely broken, which is connection.execute(some_default)
returns a scalar value.   This case has been deprecated
and new paths have been set up so that connection.scalar()
may be used.  This likely wasn't possible in previous
versions because scalar() would assume a CursorResult.

The scalar() change also impacts Session as we have explicit
support (since someone had reported it as a regression)
for session.execute(Sequence()) to work.  They will get the
same deprecation message (which omits the word "Connection",
just uses ".execute()" and ".scalar()") and they can then
use Session.scalar() as well.  Getting this to type
correctly while still supporting ORM use cases required
some refactoring, and I also set up a keyword only delimeter
for Session.execute() and related as execution_options /
bind_arguments should always be keyword only, applied these
changes to AsyncSession as well.

Additionally simpify Table __init__ now that we are Python
3 only, we can have positional plus explicit kwargs finally.
Simplify Column.__init__ as well again taking advantage
of kw only arguments.

Fill in most/all __init__ methods in sqltypes.py as
the constructor for types is most of the API.   should
likely do this for dialect-specific types as well.

Apply _InfoType for all info attributes as should have been
done originally and update descriptor decorators.

Change-Id: I3f9f8ff3f1c8858471ff4545ac83d68c88107527

12 files changed, 181 insertions, 28 deletions

diff --git a/test/dialect/oracle/test_dialect.py b/test/dialect/oracle/test_dialect.py
index 60cdb2577..ad361b879 100644
--- a/test/dialect/oracle/test_dialect.py
+++ b/test/dialect/oracle/test_dialect.py
@@ -778,7 +778,7 @@ class ExecuteTest(fixtures.TestBase):
         seq = Sequence("foo_seq")
         seq.create(connection)
         try:
-            val = connection.execute(seq)
+            val = connection.scalar(seq)
             eq_(val, 1)
             assert type(val) is int
         finally:
diff --git a/test/engine/test_execute.py b/test/engine/test_execute.py
index b561db99e..bd9ac0e3d 100644
--- a/test/engine/test_execute.py
+++ b/test/engine/test_execute.py
@@ -53,7 +53,6 @@ from sqlalchemy.testing import is_false
 from sqlalchemy.testing import is_not
 from sqlalchemy.testing import is_true
 from sqlalchemy.testing import mock
-from sqlalchemy.testing.assertions import expect_deprecated
 from sqlalchemy.testing.assertsql import CompiledSQL
 from sqlalchemy.testing.schema import Column
 from sqlalchemy.testing.schema import Table
@@ -490,15 +489,6 @@ class ExecuteTest(fixtures.TablesTest):
                     obj,
                 )
 
-    def test_subquery_exec_warning(self):
-        for obj in (select(1).alias(), select(1).subquery()):
-            with testing.db.connect() as conn:
-                with expect_deprecated(
-                    "Executing a subquery object is deprecated and will "
-                    "raise ObjectNotExecutableError"
-                ):
-                    eq_(conn.execute(obj).scalar(), 1)
-
     def test_stmt_exception_bytestring_raised(self):
         name = "méil"
         users = self.tables.users
diff --git a/test/ext/asyncio/test_session_py3k.py b/test/ext/asyncio/test_session_py3k.py
index ce38de511..48c5e60ab 100644
--- a/test/ext/asyncio/test_session_py3k.py
+++ b/test/ext/asyncio/test_session_py3k.py
@@ -6,6 +6,7 @@ from sqlalchemy import func
 from sqlalchemy import inspect
 from sqlalchemy import Integer
 from sqlalchemy import select
+from sqlalchemy import Sequence
 from sqlalchemy import Table
 from sqlalchemy import testing
 from sqlalchemy import update
@@ -25,6 +26,7 @@ from sqlalchemy.testing import expect_raises_message
 from sqlalchemy.testing import is_
 from sqlalchemy.testing import is_true
 from sqlalchemy.testing import mock
+from sqlalchemy.testing.assertions import expect_deprecated
 from sqlalchemy.testing.assertions import is_false
 from .test_engine_py3k import AsyncFixture as _AsyncFixture
 from ...orm import _fixtures
@@ -68,6 +70,34 @@ class AsyncSessionTest(AsyncFixture):
         ss = AsyncSession(binds=binds)
         is_(ss.binds, binds)
 
+    @async_test
+    @testing.combinations((True,), (False,), argnames="use_scalar")
+    @testing.requires.sequences
+    async def test_sequence_execute(
+        self, async_session: AsyncSession, metadata, use_scalar
+    ):
+        seq = Sequence("some_sequence", metadata=metadata)
+
+        sync_connection = (await async_session.connection()).sync_connection
+
+        await (await async_session.connection()).run_sync(metadata.create_all)
+
+        if use_scalar:
+            eq_(
+                await async_session.scalar(seq),
+                sync_connection.dialect.default_sequence_base,
+            )
+        else:
+            with expect_deprecated(
+                r"Using the .execute\(\) method to invoke a "
+                r"DefaultGenerator object is deprecated; please use "
+                r"the .scalar\(\) method."
+            ):
+                eq_(
+                    await async_session.execute(seq),
+                    sync_connection.dialect.default_sequence_base,
+                )
+
 
 class AsyncSessionQueryTest(AsyncFixture):
     @async_test
diff --git a/test/ext/mypy/plain_files/core_ddl.py b/test/ext/mypy/plain_files/core_ddl.py
new file mode 100644
index 000000000..673a90e94
--- /dev/null
+++ b/test/ext/mypy/plain_files/core_ddl.py
@@ -0,0 +1,43 @@
+from sqlalchemy import CheckConstraint
+from sqlalchemy import Column
+from sqlalchemy import DateTime
+from sqlalchemy import ForeignKey
+from sqlalchemy import Index
+from sqlalchemy import Integer
+from sqlalchemy import MetaData
+from sqlalchemy import PrimaryKeyConstraint
+from sqlalchemy import String
+from sqlalchemy import Table
+
+
+m = MetaData()
+
+
+t1 = Table(
+    "t1",
+    m,
+    Column("id", Integer, primary_key=True),
+    Column("data", String),
+    Column("data2", String(50)),
+    Column("timestamp", DateTime()),
+    Index(None, "data2"),
+)
+
+t2 = Table(
+    "t2",
+    m,
+    Column("t1id", ForeignKey("t1.id")),
+    Column("q", Integer, CheckConstraint("q > 5")),
+)
+
+t3 = Table(
+    "t3",
+    m,
+    Column("x", Integer),
+    Column("y", Integer),
+    Column("t1id", ForeignKey(t1.c.id)),
+    PrimaryKeyConstraint("x", "y"),
+)
+
+# cols w/ no name or type, used by declarative
+c1: Column[int] = Column(ForeignKey(t3.c.x))
diff --git a/test/ext/test_compiler.py b/test/ext/test_compiler.py
index 996797122..7067d24c1 100644
--- a/test/ext/test_compiler.py
+++ b/test/ext/test_compiler.py
@@ -16,7 +16,7 @@ from sqlalchemy.ext.compiler import deregister
 from sqlalchemy.orm import Session
 from sqlalchemy.schema import CreateColumn
 from sqlalchemy.schema import CreateTable
-from sqlalchemy.schema import DDLElement
+from sqlalchemy.schema import ExecutableDDLElement
 from sqlalchemy.sql.elements import ColumnElement
 from sqlalchemy.sql.expression import BindParameter
 from sqlalchemy.sql.expression import ClauseElement
@@ -275,10 +275,10 @@ class UserDefinedTest(fixtures.TestBase, AssertsCompiledSQL):
                 del Select._compiler_dispatcher
 
     def test_dialect_specific(self):
-        class AddThingy(DDLElement):
+        class AddThingy(ExecutableDDLElement):
             __visit_name__ = "add_thingy"
 
-        class DropThingy(DDLElement):
+        class DropThingy(ExecutableDDLElement):
             __visit_name__ = "drop_thingy"
 
         @compiles(AddThingy, "sqlite")
diff --git a/test/orm/test_events.py b/test/orm/test_events.py
index 4cecac0de..be1919614 100644
--- a/test/orm/test_events.py
+++ b/test/orm/test_events.py
@@ -3,6 +3,7 @@ from unittest.mock import call
 from unittest.mock import Mock
 
 import sqlalchemy as sa
+from sqlalchemy import bindparam
 from sqlalchemy import delete
 from sqlalchemy import event
 from sqlalchemy import exc as sa_exc
@@ -239,6 +240,37 @@ class ORMExecuteTest(_RemoveListeners, _fixtures.FixtureTest):
             ),
         )
 
+    def test_override_parameters_scalar(self):
+        """test that session.scalar() maintains the 'scalar-ness' of the
+        result when using re-execute events.
+
+        This got more complicated when the session.scalar(Sequence("my_seq"))
+        use case needed to keep working and returning a scalar result.
+
+        """
+        User = self.classes.User
+
+        sess = Session(testing.db, future=True)
+
+        @event.listens_for(sess, "do_orm_execute")
+        def one(ctx):
+            return ctx.invoke_statement(params={"id": 7})
+
+        orig_params = {"id": 18}
+        with self.sql_execution_asserter() as asserter:
+            result = sess.scalar(
+                select(User).where(User.id == bindparam("id")), orig_params
+            )
+        asserter.assert_(
+            CompiledSQL(
+                "SELECT users.id, users.name FROM users WHERE users.id = :id",
+                [{"id": 7}],
+            )
+        )
+        eq_(result, User(id=7))
+        # orig params weren't mutated
+        eq_(orig_params, {"id": 18})
+
     def test_override_parameters_executesingle(self):
         User = self.classes.User
 
diff --git a/test/orm/test_session.py b/test/orm/test_session.py
index 8e568aef0..49f769f81 100644
--- a/test/orm/test_session.py
+++ b/test/orm/test_session.py
@@ -56,9 +56,10 @@ class ExecutionTest(_fixtures.FixtureTest):
     @testing.combinations(
         (True,), (False,), argnames="add_do_orm_execute_event"
     )
+    @testing.combinations((True,), (False,), argnames="use_scalar")
     @testing.requires.sequences
     def test_sequence_execute(
-        self, connection, metadata, add_do_orm_execute_event
+        self, connection, metadata, add_do_orm_execute_event, use_scalar
     ):
         seq = Sequence("some_sequence", metadata=metadata)
         metadata.create_all(connection)
@@ -69,7 +70,18 @@ class ExecutionTest(_fixtures.FixtureTest):
             event.listen(
                 sess, "do_orm_execute", lambda ctx: evt(ctx.statement)
             )
-        eq_(sess.execute(seq), connection.dialect.default_sequence_base)
+
+        if use_scalar:
+            eq_(sess.scalar(seq), connection.dialect.default_sequence_base)
+        else:
+            with assertions.expect_deprecated(
+                r"Using the .execute\(\) method to invoke a "
+                r"DefaultGenerator object is deprecated; please use "
+                r"the .scalar\(\) method."
+            ):
+                eq_(
+                    sess.execute(seq), connection.dialect.default_sequence_base
+                )
         if add_do_orm_execute_event:
             eq_(evt.mock_calls, [mock.call(seq)])
 
@@ -1994,7 +2006,7 @@ class SessionInterface(fixtures.MappedTest):
                 if name in blocklist:
                     continue
                 spec = inspect_getfullargspec(getattr(Session, name))
-                if len(spec[0]) > 1 or spec[1]:
+                if len(spec.args) > 1 or spec.varargs or spec.kwonlyargs:
                     ok.add(name)
         return ok
 
@@ -2051,7 +2063,7 @@ class SessionInterface(fixtures.MappedTest):
 
             s = fixture_session()
             s.add(OK())
-            x_raises_(s, "flush", (user_arg,))
+            x_raises_(s, "flush", objects=(user_arg,))
 
         _()
 
diff --git a/test/profiles.txt b/test/profiles.txt
index 7b4f37734..38b7290d6 100644
--- a/test/profiles.txt
+++ b/test/profiles.txt
@@ -204,7 +204,8 @@ test.aaa_profiling.test_orm.JoinedEagerLoadTest.test_fetch_results x86_64_linux_
 
 # TEST: test.aaa_profiling.test_orm.JoinedEagerLoadTest.test_fetch_results_integrated
 
-test.aaa_profiling.test_orm.JoinedEagerLoadTest.test_fetch_results_integrated x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 30373,1014,96450
+# wow first time ever decreasing a value, woop.  not sure why though
+test.aaa_profiling.test_orm.JoinedEagerLoadTest.test_fetch_results_integrated x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 28587,1014,96450
 
 # TEST: test.aaa_profiling.test_orm.LoadManyToOneFromIdentityTest.test_many_to_one_load_identity
 
diff --git a/test/sql/test_constraints.py b/test/sql/test_constraints.py
index 2661e6c8f..c417255af 100644
--- a/test/sql/test_constraints.py
+++ b/test/sql/test_constraints.py
@@ -863,6 +863,8 @@ class ConstraintCompilationTest(fixtures.TestBase, AssertsCompiledSQL):
             x.append_constraint(idx)
             self.assert_compile(schema.CreateIndex(idx), ddl)
 
+            x.to_metadata(MetaData())
+
     def test_index_against_text_separate(self):
         metadata = MetaData()
         idx = Index("y", text("some_function(q)"))
diff --git a/test/sql/test_defaults.py b/test/sql/test_defaults.py
index 52c779969..3742aa174 100644
--- a/test/sql/test_defaults.py
+++ b/test/sql/test_defaults.py
@@ -23,6 +23,7 @@ from sqlalchemy.testing import eq_
 from sqlalchemy.testing import expect_warnings
 from sqlalchemy.testing import fixtures
 from sqlalchemy.testing import mock
+from sqlalchemy.testing.assertions import expect_deprecated
 from sqlalchemy.testing.schema import Column
 from sqlalchemy.testing.schema import Table
 from sqlalchemy.types import TypeDecorator
@@ -503,15 +504,45 @@ class DefaultRoundTripTest(fixtures.TablesTest):
     def teardown_test(self):
         self.default_generator["x"] = 50
 
-    def test_standalone(self, connection):
+    def test_standalone_via_exec_removed(self, connection):
         t = self.tables.default_test
-        x = connection.execute(t.c.col1.default)
-        y = connection.execute(t.c.col2.default)
-        z = connection.execute(t.c.col3.default)
+
+        with expect_deprecated(
+            r"Using the .execute\(\) method to invoke a "
+            r"DefaultGenerator object is deprecated; please use "
+            r"the .scalar\(\) method."
+        ):
+            x = connection.execute(t.c.col1.default)
+        with expect_deprecated(
+            r"Using the .execute\(\) method to invoke a "
+            r"DefaultGenerator object is deprecated; please use "
+            r"the .scalar\(\) method."
+        ):
+            y = connection.execute(t.c.col2.default)
+        with expect_deprecated(
+            r"Using the .execute\(\) method to invoke a "
+            r"DefaultGenerator object is deprecated; please use "
+            r"the .scalar\(\) method."
+        ):
+            z = connection.execute(t.c.col3.default)
+
+    def test_standalone_default_scalar(self, connection):
+        t = self.tables.default_test
+        x = connection.scalar(t.c.col1.default)
+        y = connection.scalar(t.c.col2.default)
+        z = connection.scalar(t.c.col3.default)
         assert 50 <= x <= 57
         eq_(y, "imthedefault")
         eq_(z, self.f)
 
+    def test_standalone_function_execute(self, connection):
+        ctexec = connection.execute(self.currenttime)
+        assert isinstance(ctexec.scalar(), datetime.date)
+
+    def test_standalone_function_scalar(self, connection):
+        ctexec = connection.scalar(self.currenttime)
+        assert isinstance(ctexec, datetime.date)
+
     def test_insert(self, connection):
         t = self.tables.default_test
 
diff --git a/test/sql/test_returning.py b/test/sql/test_returning.py
index ffbab3223..bacdbaf3f 100644
--- a/test/sql/test_returning.py
+++ b/test/sql/test_returning.py
@@ -543,7 +543,7 @@ class SequenceReturningTest(fixtures.TablesTest):
         )
         eq_(r.first(), tuple([testing.db.dialect.default_sequence_base]))
         eq_(
-            connection.execute(self.sequences.tid_seq),
+            connection.scalar(self.sequences.tid_seq),
             testing.db.dialect.default_sequence_base + 1,
         )
 
diff --git a/test/sql/test_sequences.py b/test/sql/test_sequences.py
index d11961862..be74153ce 100644
--- a/test/sql/test_sequences.py
+++ b/test/sql/test_sequences.py
@@ -14,6 +14,7 @@ from sqlalchemy.testing import eq_
 from sqlalchemy.testing import fixtures
 from sqlalchemy.testing import is_false
 from sqlalchemy.testing import is_true
+from sqlalchemy.testing.assertions import expect_deprecated
 from sqlalchemy.testing.assertsql import AllOf
 from sqlalchemy.testing.assertsql import CompiledSQL
 from sqlalchemy.testing.assertsql import EachOf
@@ -117,14 +118,25 @@ class SequenceExecTest(fixtures.TestBase):
 
     def test_execute(self, connection):
         s = Sequence("my_sequence")
-        self._assert_seq_result(connection.execute(s))
+        self._assert_seq_result(connection.scalar(s))
 
-    def test_execute_optional(self, connection):
+    def test_execute_deprecated(self, connection):
+
+        s = Sequence("my_sequence", optional=True)
+
+        with expect_deprecated(
+            r"Using the .execute\(\) method to invoke a "
+            r"DefaultGenerator object is deprecated; please use "
+            r"the .scalar\(\) method."
+        ):
+            self._assert_seq_result(connection.execute(s))
+
+    def test_scalar_optional(self, connection):
         """test dialect executes a Sequence, returns nextval, whether
         or not "optional" is set"""
 
         s = Sequence("my_sequence", optional=True)
-        self._assert_seq_result(connection.execute(s))
+        self._assert_seq_result(connection.scalar(s))
 
     def test_execute_next_value(self, connection):
         """test func.next_value().execute()/.scalar() works
@@ -341,7 +353,7 @@ class SequenceTest(fixtures.TestBase, testing.AssertsCompiledSQL):
         seq.create(testing.db)
         try:
             with testing.db.connect() as conn:
-                values = [conn.execute(seq) for i in range(3)]
+                values = [conn.scalar(seq) for i in range(3)]
                 start = seq.start or testing.db.dialect.default_sequence_base
                 inc = seq.increment or 1
                 eq_(values, list(range(start, start + inc * 3, inc)))