rearchitect reflection for batched performance

Rearchitected the schema reflection API to allow some dialects to make use
of high performing batch queries to reflect the schemas of many tables at
once using much fewer queries. The new performance features are targeted
first at the PostgreSQL and Oracle backends, and may be applied to any
dialect that makes use of SELECT queries against system catalog tables to
reflect tables (currently this omits the MySQL and SQLite dialects which
instead make use of parsing the "CREATE TABLE" statement, however these
dialects do not have a pre-existing performance issue with reflection. MS
SQL Server is still a TODO).

The new API is backwards compatible with the previous system, and should
require no changes to third party dialects to retain compatibility;
third party dialects can also opt into the new system by implementing
batched queries for schema reflection.

Along with this change is an updated reflection API that is fully
:pep:`484` typed, features many new methods and some changes.

Fixes: #4379
Change-Id: I897ec09843543aa7012bcdce758792ed3d415d08

1 files changed, 49 insertions, 5 deletions

diff --git a/lib/sqlalchemy/dialects/oracle/provision.py b/lib/sqlalchemy/dialects/oracle/provision.py
index cba3b5be4..75b7a7aa9 100644
--- a/lib/sqlalchemy/dialects/oracle/provision.py
+++ b/lib/sqlalchemy/dialects/oracle/provision.py
@@ -2,9 +2,12 @@
 
 from ... import create_engine
 from ... import exc
+from ... import inspect
 from ...engine import url as sa_url
 from ...testing.provision import configure_follower
 from ...testing.provision import create_db
+from ...testing.provision import drop_all_schema_objects_post_tables
+from ...testing.provision import drop_all_schema_objects_pre_tables
 from ...testing.provision import drop_db
 from ...testing.provision import follower_url_from_main
 from ...testing.provision import log
@@ -28,6 +31,10 @@ def _oracle_create_db(cfg, eng, ident):
         conn.exec_driver_sql("grant unlimited tablespace to %s" % ident)
         conn.exec_driver_sql("grant unlimited tablespace to %s_ts1" % ident)
         conn.exec_driver_sql("grant unlimited tablespace to %s_ts2" % ident)
+        # these are needed to create materialized views
+        conn.exec_driver_sql("grant create table to %s" % ident)
+        conn.exec_driver_sql("grant create table to %s_ts1" % ident)
+        conn.exec_driver_sql("grant create table to %s_ts2" % ident)
 
 
 @configure_follower.for_db("oracle")
@@ -46,6 +53,30 @@ def _ora_drop_ignore(conn, dbname):
         return False
 
 
+@drop_all_schema_objects_pre_tables.for_db("oracle")
+def _ora_drop_all_schema_objects_pre_tables(cfg, eng):
+    _purge_recyclebin(eng)
+    _purge_recyclebin(eng, cfg.test_schema)
+
+
+@drop_all_schema_objects_post_tables.for_db("oracle")
+def _ora_drop_all_schema_objects_post_tables(cfg, eng):
+
+    with eng.begin() as conn:
+        for syn in conn.dialect._get_synonyms(conn, None, None, None):
+            conn.exec_driver_sql(f"drop synonym {syn['synonym_name']}")
+
+        for syn in conn.dialect._get_synonyms(
+            conn, cfg.test_schema, None, None
+        ):
+            conn.exec_driver_sql(
+                f"drop synonym {cfg.test_schema}.{syn['synonym_name']}"
+            )
+
+        for tmp_table in inspect(conn).get_temp_table_names():
+            conn.exec_driver_sql(f"drop table {tmp_table}")
+
+
 @drop_db.for_db("oracle")
 def _oracle_drop_db(cfg, eng, ident):
     with eng.begin() as conn:
@@ -60,13 +91,10 @@ def _oracle_drop_db(cfg, eng, ident):
 
 
 @stop_test_class_outside_fixtures.for_db("oracle")
-def stop_test_class_outside_fixtures(config, db, cls):
+def _ora_stop_test_class_outside_fixtures(config, db, cls):
 
     try:
-        with db.begin() as conn:
-            # run magic command to get rid of identity sequences
-            # https://floo.bar/2019/11/29/drop-the-underlying-sequence-of-an-identity-column/  # noqa: E501
-            conn.exec_driver_sql("purge recyclebin")
+        _purge_recyclebin(db)
     except exc.DatabaseError as err:
         log.warning("purge recyclebin command failed: %s", err)
 
@@ -85,6 +113,22 @@ def stop_test_class_outside_fixtures(config, db, cls):
     _all_conns.clear()
 
 
+def _purge_recyclebin(eng, schema=None):
+    with eng.begin() as conn:
+        if schema is None:
+            # run magic command to get rid of identity sequences
+            # https://floo.bar/2019/11/29/drop-the-underlying-sequence-of-an-identity-column/  # noqa: E501
+            conn.exec_driver_sql("purge recyclebin")
+        else:
+            # per user: https://community.oracle.com/tech/developers/discussion/2255402/how-to-clear-dba-recyclebin-for-a-particular-user  # noqa: E501
+            for owner, object_name, type_ in conn.exec_driver_sql(
+                "select owner, object_name,type from "
+                "dba_recyclebin where owner=:schema and type='TABLE'",
+                {"schema": conn.dialect.denormalize_name(schema)},
+            ).all():
+                conn.exec_driver_sql(f'purge {type_} {owner}."{object_name}"')
+
+
 _all_conns = set()