Emit v2.0 deprecation warning for "implicit autocommit"

"Implicit autocommit", which is the COMMIT that occurs when a DML or DDL
statement is emitted on a connection, is deprecated and won't be part of
SQLAlchemy 2.0.   A 2.0-style warning is emitted when autocommit takes
effect, so that the calling code may be adjusted to use an explicit
transaction.

As part of this change, DDL methods such as
:meth:`_schema.MetaData.create_all` when used against a
:class:`_engine.Engine` or :class:`_engine.Connection` will run the
operation in a BEGIN block if one is not started already.

The MySQL and MariaDB dialects now query from the information_schema.tables
system view in order to determine if a particular table exists or not.
Previously, the "DESCRIBE" command was used with an exception catch to
detect non-existent,  which would have the undesirable effect of emitting a
ROLLBACK on the connection. There appeared to be legacy encoding issues
which prevented the use of "SHOW TABLES", for this, but as MySQL support is
now at 5.0.2  or above due to :ticket:`4189`, the information_schema tables
are now available in all cases.

Fixes: #4846
Change-Id: I733a7e0e17477a63607fb9931c87c393bbd7ac57

5 files changed, 262 insertions, 51 deletions

diff --git a/test/base/test_tutorials.py b/test/base/test_tutorials.py
index 69f9f7e90..4ac3fb981 100644
--- a/test/base/test_tutorials.py
+++ b/test/base/test_tutorials.py
@@ -89,7 +89,7 @@ class DocTest(fixtures.TestBase):
     def test_orm(self):
         self._run_doctest("orm/tutorial.rst")
 
-    @testing.emits_warning("SELECT statement has a cartesian")
+    @testing.emits_warning()
     def test_core(self):
         self._run_doctest("core/tutorial.rst")
 
diff --git a/test/dialect/mssql/test_reflection.py b/test/dialect/mssql/test_reflection.py
index 6e4038eb4..0bd8f7a5a 100644
--- a/test/dialect/mssql/test_reflection.py
+++ b/test/dialect/mssql/test_reflection.py
@@ -12,6 +12,7 @@ from sqlalchemy import schema
 from sqlalchemy import Table
 from sqlalchemy import testing
 from sqlalchemy import types
+from sqlalchemy import types as sqltypes
 from sqlalchemy import util
 from sqlalchemy.dialects import mssql
 from sqlalchemy.dialects.mssql import base
@@ -143,20 +144,38 @@ class ReflectionTest(fixtures.TestBase, ComparesTables, AssertsCompiledSQL):
         eq_(table2.c["col1"].dialect_options["mssql"]["identity_start"], 2)
         eq_(table2.c["col1"].dialect_options["mssql"]["identity_increment"], 3)
 
-    @testing.emits_warning("Did not recognize")
     @testing.provide_metadata
-    def test_skip_types(self):
-        metadata = self.metadata
-        with testing.db.connect() as c:
-            c.exec_driver_sql(
-                "create table foo (id integer primary key, data xml)"
-            )
+    def test_skip_types(self, connection):
+        connection.exec_driver_sql(
+            "create table foo (id integer primary key, data xml)"
+        )
         with mock.patch.object(
-            testing.db.dialect, "ischema_names", {"int": mssql.INTEGER}
+            connection.dialect, "ischema_names", {"int": mssql.INTEGER}
         ):
-            t1 = Table("foo", metadata, autoload=True)
-        assert isinstance(t1.c.id.type, Integer)
-        assert isinstance(t1.c.data.type, types.NullType)
+            with testing.expect_warnings(
+                "Did not recognize type 'xml' of column 'data'"
+            ):
+                eq_(
+                    inspect(connection).get_columns("foo"),
+                    [
+                        {
+                            "name": "id",
+                            "type": testing.eq_type_affinity(sqltypes.INTEGER),
+                            "nullable": False,
+                            "default": None,
+                            "autoincrement": False,
+                        },
+                        {
+                            "name": "data",
+                            "type": testing.eq_type_affinity(
+                                sqltypes.NullType
+                            ),
+                            "nullable": True,
+                            "default": None,
+                            "autoincrement": False,
+                        },
+                    ],
+                )
 
     @testing.provide_metadata
     def test_cross_schema_fk_pk_name_overlaps(self):
diff --git a/test/dialect/oracle/test_reflection.py b/test/dialect/oracle/test_reflection.py
index beb24923d..b9975f65e 100644
--- a/test/dialect/oracle/test_reflection.py
+++ b/test/dialect/oracle/test_reflection.py
@@ -5,6 +5,7 @@ from sqlalchemy import exc
 from sqlalchemy import FLOAT
 from sqlalchemy import ForeignKey
 from sqlalchemy import ForeignKeyConstraint
+from sqlalchemy import func
 from sqlalchemy import Index
 from sqlalchemy import inspect
 from sqlalchemy import INTEGER
@@ -13,7 +14,6 @@ from sqlalchemy import MetaData
 from sqlalchemy import Numeric
 from sqlalchemy import PrimaryKeyConstraint
 from sqlalchemy import select
-from sqlalchemy import String
 from sqlalchemy import testing
 from sqlalchemy import text
 from sqlalchemy import Unicode
@@ -437,29 +437,6 @@ class DontReflectIOTTest(fixtures.TestBase):
         eq_(set(t.name for t in m.tables.values()), set(["admin_docindex"]))
 
 
-class UnsupportedIndexReflectTest(fixtures.TestBase):
-    __only_on__ = "oracle"
-    __backend__ = True
-
-    @testing.emits_warning("No column names")
-    @testing.provide_metadata
-    def test_reflect_functional_index(self):
-        metadata = self.metadata
-        Table(
-            "test_index_reflect",
-            metadata,
-            Column("data", String(20), primary_key=True),
-        )
-        metadata.create_all()
-
-        exec_sql(
-            testing.db,
-            "CREATE INDEX DATA_IDX ON " "TEST_INDEX_REFLECT (UPPER(DATA))",
-        )
-        m2 = MetaData(testing.db)
-        Table("test_index_reflect", m2, autoload=True)
-
-
 def all_tables_compression_missing():
     try:
         exec_sql(testing.db, "SELECT compression FROM all_tables")
@@ -610,6 +587,40 @@ class RoundTripIndexTest(fixtures.TestBase):
         )
 
     @testing.provide_metadata
+    def test_reflect_fn_index(self, connection):
+        """test reflection of a functional index.
+
+        it appears this emitted a warning at some point but does not right now.
+        the returned data is not exactly correct, but this is what it's
+        likely been doing for many years.
+
+        """
+
+        metadata = self.metadata
+        s_table = Table(
+            "sometable",
+            metadata,
+            Column("group", Unicode(255), primary_key=True),
+            Column("col", Unicode(255)),
+        )
+
+        Index("data_idx", func.upper(s_table.c.col))
+
+        metadata.create_all(connection)
+
+        eq_(
+            inspect(connection).get_indexes("sometable"),
+            [
+                {
+                    "column_names": [],
+                    "dialect_options": {},
+                    "name": "data_idx",
+                    "unique": False,
+                }
+            ],
+        )
+
+    @testing.provide_metadata
     def test_basic(self):
         metadata = self.metadata
 
diff --git a/test/engine/test_ddlevents.py b/test/engine/test_ddlevents.py
index 8f5834cc9..9f661abf0 100644
--- a/test/engine/test_ddlevents.py
+++ b/test/engine/test_ddlevents.py
@@ -549,6 +549,163 @@ class DDLExecutionTest(fixtures.TestBase):
             )
 
 
+class DDLTransactionTest(fixtures.TestBase):
+    """test DDL transactional behavior as of SQLAlchemy 1.4."""
+
+    @testing.fixture
+    def metadata_fixture(self):
+        m = MetaData()
+        Table("t1", m, Column("q", Integer))
+        Table("t2", m, Column("q", Integer))
+
+        try:
+            yield m
+        finally:
+            m.drop_all(testing.db)
+
+    def _listening_engine_fixture(self, future=False):
+        eng = engines.testing_engine(future=future)
+
+        m1 = mock.Mock()
+
+        event.listen(eng, "begin", m1.begin)
+        event.listen(eng, "commit", m1.commit)
+        event.listen(eng, "rollback", m1.rollback)
+
+        @event.listens_for(eng, "before_cursor_execute")
+        def before_cursor_execute(
+            conn, cursor, statement, parameters, context, executemany
+        ):
+            if "CREATE TABLE" in statement:
+                m1.cursor_execute("CREATE TABLE ...")
+
+        eng.connect().close()
+
+        return eng, m1
+
+    @testing.fixture
+    def listening_engine_fixture(self):
+        return self._listening_engine_fixture(future=False)
+
+    @testing.fixture
+    def future_listening_engine_fixture(self):
+        return self._listening_engine_fixture(future=True)
+
+    def test_ddl_legacy_engine(
+        self, metadata_fixture, listening_engine_fixture
+    ):
+        eng, m1 = listening_engine_fixture
+
+        metadata_fixture.create_all(eng)
+
+        eq_(
+            m1.mock_calls,
+            [
+                mock.call.begin(mock.ANY),
+                mock.call.cursor_execute("CREATE TABLE ..."),
+                mock.call.cursor_execute("CREATE TABLE ..."),
+                mock.call.commit(mock.ANY),
+            ],
+        )
+
+    def test_ddl_future_engine(
+        self, metadata_fixture, future_listening_engine_fixture
+    ):
+        eng, m1 = future_listening_engine_fixture
+
+        metadata_fixture.create_all(eng)
+
+        eq_(
+            m1.mock_calls,
+            [
+                mock.call.begin(mock.ANY),
+                mock.call.cursor_execute("CREATE TABLE ..."),
+                mock.call.cursor_execute("CREATE TABLE ..."),
+                mock.call.commit(mock.ANY),
+            ],
+        )
+
+    def test_ddl_legacy_connection_no_transaction(
+        self, metadata_fixture, listening_engine_fixture
+    ):
+        eng, m1 = listening_engine_fixture
+
+        with eng.connect() as conn:
+            with testing.expect_deprecated(
+                "The current statement is being autocommitted using "
+                "implicit autocommit"
+            ):
+                metadata_fixture.create_all(conn)
+
+        eq_(
+            m1.mock_calls,
+            [
+                mock.call.cursor_execute("CREATE TABLE ..."),
+                mock.call.commit(mock.ANY),
+                mock.call.cursor_execute("CREATE TABLE ..."),
+                mock.call.commit(mock.ANY),
+            ],
+        )
+
+    def test_ddl_legacy_connection_transaction(
+        self, metadata_fixture, listening_engine_fixture
+    ):
+        eng, m1 = listening_engine_fixture
+
+        with eng.connect() as conn:
+            with conn.begin():
+                metadata_fixture.create_all(conn)
+
+        eq_(
+            m1.mock_calls,
+            [
+                mock.call.begin(mock.ANY),
+                mock.call.cursor_execute("CREATE TABLE ..."),
+                mock.call.cursor_execute("CREATE TABLE ..."),
+                mock.call.commit(mock.ANY),
+            ],
+        )
+
+    def test_ddl_future_connection_autobegin_transaction(
+        self, metadata_fixture, future_listening_engine_fixture
+    ):
+        eng, m1 = future_listening_engine_fixture
+
+        with eng.connect() as conn:
+            metadata_fixture.create_all(conn)
+
+            conn.commit()
+
+        eq_(
+            m1.mock_calls,
+            [
+                mock.call.begin(mock.ANY),
+                mock.call.cursor_execute("CREATE TABLE ..."),
+                mock.call.cursor_execute("CREATE TABLE ..."),
+                mock.call.commit(mock.ANY),
+            ],
+        )
+
+    def test_ddl_future_connection_explicit_begin_transaction(
+        self, metadata_fixture, future_listening_engine_fixture
+    ):
+        eng, m1 = future_listening_engine_fixture
+
+        with eng.connect() as conn:
+            with conn.begin():
+                metadata_fixture.create_all(conn)
+
+        eq_(
+            m1.mock_calls,
+            [
+                mock.call.begin(mock.ANY),
+                mock.call.cursor_execute("CREATE TABLE ..."),
+                mock.call.cursor_execute("CREATE TABLE ..."),
+                mock.call.commit(mock.ANY),
+            ],
+        )
+
+
 class DDLTest(fixtures.TestBase, AssertsCompiledSQL):
     def mock_engine(self):
         def executor(*a, **kw):
diff --git a/test/engine/test_deprecations.py b/test/engine/test_deprecations.py
index 62bac312b..d733bd6a7 100644
--- a/test/engine/test_deprecations.py
+++ b/test/engine/test_deprecations.py
@@ -13,6 +13,7 @@ from sqlalchemy import pool
 from sqlalchemy import select
 from sqlalchemy import String
 from sqlalchemy import testing
+from sqlalchemy import text
 from sqlalchemy import VARCHAR
 from sqlalchemy.engine import reflection
 from sqlalchemy.engine.base import Connection
@@ -176,31 +177,22 @@ class CreateEngineTest(fixtures.TestBase):
             )
 
 
-class TransactionTest(fixtures.TestBase):
+class TransactionTest(fixtures.TablesTest):
     __backend__ = True
 
     @classmethod
-    def setup_class(cls):
-        metadata = MetaData()
-        cls.users = Table(
-            "query_users",
+    def define_tables(cls, metadata):
+        Table(
+            "users",
             metadata,
             Column("user_id", Integer, primary_key=True),
             Column("user_name", String(20)),
             test_needs_acid=True,
         )
-        cls.users.create(testing.db)
-
-    def teardown(self):
-        with testing.db.connect() as conn:
-            conn.execute(self.users.delete())
-
-    @classmethod
-    def teardown_class(cls):
-        cls.users.drop(testing.db)
+        Table("inserttable", metadata, Column("data", String(20)))
 
     def test_transaction_container(self):
-        users = self.users
+        users = self.tables.users
 
         def go(conn, table, data):
             for d in data:
@@ -231,6 +223,38 @@ class TransactionTest(fixtures.TestBase):
         with testing.db.connect() as conn:
             eq_(conn.execute(users.select()).fetchall(), [(1, "user1")])
 
+    def test_implicit_autocommit_compiled(self):
+        users = self.tables.users
+
+        with testing.db.connect() as conn:
+            with testing.expect_deprecated_20(
+                "The current statement is being autocommitted "
+                "using implicit autocommit."
+            ):
+                conn.execute(
+                    users.insert(), {"user_id": 1, "user_name": "user3"}
+                )
+
+    def test_implicit_autocommit_text(self):
+        with testing.db.connect() as conn:
+            with testing.expect_deprecated_20(
+                "The current statement is being autocommitted "
+                "using implicit autocommit."
+            ):
+                conn.execute(
+                    text("insert into inserttable (data) values ('thedata')")
+                )
+
+    def test_implicit_autocommit_driversql(self):
+        with testing.db.connect() as conn:
+            with testing.expect_deprecated_20(
+                "The current statement is being autocommitted "
+                "using implicit autocommit."
+            ):
+                conn.exec_driver_sql(
+                    "insert into inserttable (data) values ('thedata')"
+                )
+
 
 class HandleInvalidatedOnConnectTest(fixtures.TestBase):
     __requires__ = ("sqlite",)