correct for "autocommit" deprecation warning

Ensure no autocommit warnings occur internally or
within tests.

Also includes fixes for SQL Server full text tests
which apparently have not been working at all for a long
time, as it used long removed APIs.  CI has not had
fulltext running for some years and is now installed.

Change-Id: Id806e1856c9da9f0a9eac88cebc7a94ecc95eb96

3 files changed, 17 insertions, 15 deletions

diff --git a/lib/sqlalchemy/dialects/mysql/provision.py b/lib/sqlalchemy/dialects/mysql/provision.py
index c1d83bbb7..50b6e3c85 100644
--- a/lib/sqlalchemy/dialects/mysql/provision.py
+++ b/lib/sqlalchemy/dialects/mysql/provision.py
@@ -41,12 +41,13 @@ def generate_driver_url(url, driver, query_str):
 
 @create_db.for_db("mysql", "mariadb")
 def _mysql_create_db(cfg, eng, ident):
-    with eng.connect() as conn:
+    with eng.begin() as conn:
         try:
             _mysql_drop_db(cfg, conn, ident)
         except Exception:
             pass
 
+    with eng.begin() as conn:
         conn.exec_driver_sql(
             "CREATE DATABASE %s CHARACTER SET utf8mb4" % ident
         )
@@ -66,7 +67,7 @@ def _mysql_configure_follower(config, ident):
 
 @drop_db.for_db("mysql", "mariadb")
 def _mysql_drop_db(cfg, eng, ident):
-    with eng.connect() as conn:
+    with eng.begin() as conn:
         conn.exec_driver_sql("DROP DATABASE %s_test_schema" % ident)
         conn.exec_driver_sql("DROP DATABASE %s_test_schema_2" % ident)
         conn.exec_driver_sql("DROP DATABASE %s" % ident)
diff --git a/lib/sqlalchemy/dialects/oracle/provision.py b/lib/sqlalchemy/dialects/oracle/provision.py
index d19dfc9fe..aadc2c5a9 100644
--- a/lib/sqlalchemy/dialects/oracle/provision.py
+++ b/lib/sqlalchemy/dialects/oracle/provision.py
@@ -17,7 +17,7 @@ def _oracle_create_db(cfg, eng, ident):
     # NOTE: make sure you've run "ALTER DATABASE default tablespace users" or
     # similar, so that the default tablespace is not "system"; reflection will
     # fail otherwise
-    with eng.connect() as conn:
+    with eng.begin() as conn:
         conn.exec_driver_sql("create user %s identified by xe" % ident)
         conn.exec_driver_sql("create user %s_ts1 identified by xe" % ident)
         conn.exec_driver_sql("create user %s_ts2 identified by xe" % ident)
@@ -45,7 +45,7 @@ def _ora_drop_ignore(conn, dbname):
 
 @drop_db.for_db("oracle")
 def _oracle_drop_db(cfg, eng, ident):
-    with eng.connect() as conn:
+    with eng.begin() as conn:
         # cx_Oracle seems to occasionally leak open connections when a large
         # suite it run, even if we confirm we have zero references to
         # connection objects.
@@ -65,7 +65,7 @@ def _oracle_update_db_opts(db_url, db_opts):
 def _reap_oracle_dbs(url, idents):
     log.info("db reaper connecting to %r", url)
     eng = create_engine(url)
-    with eng.connect() as conn:
+    with eng.begin() as conn:
 
         log.info("identifiers in file: %s", ", ".join(idents))
 
diff --git a/lib/sqlalchemy/dialects/postgresql/provision.py b/lib/sqlalchemy/dialects/postgresql/provision.py
index 9433ec458..575316c61 100644
--- a/lib/sqlalchemy/dialects/postgresql/provision.py
+++ b/lib/sqlalchemy/dialects/postgresql/provision.py
@@ -13,7 +13,7 @@ from ...testing.provision import temp_table_keyword_args
 def _pg_create_db(cfg, eng, ident):
     template_db = cfg.options.postgresql_templatedb
 
-    with eng.connect().execution_options(isolation_level="AUTOCOMMIT") as conn:
+    with eng.execution_options(isolation_level="AUTOCOMMIT").begin() as conn:
         try:
             _pg_drop_db(cfg, conn, ident)
         except Exception:
@@ -51,15 +51,16 @@ def _pg_create_db(cfg, eng, ident):
 @drop_db.for_db("postgresql")
 def _pg_drop_db(cfg, eng, ident):
     with eng.connect().execution_options(isolation_level="AUTOCOMMIT") as conn:
-        conn.execute(
-            text(
-                "select pg_terminate_backend(pid) from pg_stat_activity "
-                "where usename=current_user and pid != pg_backend_pid() "
-                "and datname=:dname"
-            ),
-            dname=ident,
-        )
-        conn.exec_driver_sql("DROP DATABASE %s" % ident)
+        with conn.begin():
+            conn.execute(
+                text(
+                    "select pg_terminate_backend(pid) from pg_stat_activity "
+                    "where usename=current_user and pid != pg_backend_pid() "
+                    "and datname=:dname"
+                ),
+                dname=ident,
+            )
+            conn.exec_driver_sql("DROP DATABASE %s" % ident)
 
 
 @temp_table_keyword_args.for_db("postgresql")