From 1a08d1aade046e9516d0527ffd2ac8bb43906171 Mon Sep 17 00:00:00 2001
From: Gord Thompson <gord@gordthompson.com>
Date: Tue, 1 Sep 2020 14:36:40 -0600
Subject: Improve handling of covering indexes

Improved support for covering indexes (with INCLUDE columns). Added the
ability for postgresql to render CREATE INDEX statements with an INCLUDE
clause from Core. Index reflection also report INCLUDE columns separately
for both mssql and postgresql (11+).

Fixes: #4458
Change-Id: If0b82103fbc898cdaeaf6a6d2d421c732744acd6
---
 lib/sqlalchemy/testing/requirements.py          |  4 ++
 lib/sqlalchemy/testing/suite/test_reflection.py | 73 +++++++++++++++++--------
 2 files changed, 54 insertions(+), 23 deletions(-)

(limited to 'lib/sqlalchemy/testing')

diff --git a/lib/sqlalchemy/testing/requirements.py b/lib/sqlalchemy/testing/requirements.py
index 4114137d4..304f4475f 100644
--- a/lib/sqlalchemy/testing/requirements.py
+++ b/lib/sqlalchemy/testing/requirements.py
@@ -594,6 +594,10 @@ class SuiteRequirements(Requirements):
     def index_reflection(self):
         return exclusions.open()
 
+    @property
+    def index_reflects_included_columns(self):
+        return exclusions.closed()
+
     @property
     def indexes_with_ascdesc(self):
         """target database supports CREATE INDEX with per-column ASC/DESC."""
diff --git a/lib/sqlalchemy/testing/suite/test_reflection.py b/lib/sqlalchemy/testing/suite/test_reflection.py
index 94ec22c1e..3c10a45f6 100644
--- a/lib/sqlalchemy/testing/suite/test_reflection.py
+++ b/lib/sqlalchemy/testing/suite/test_reflection.py
@@ -2,6 +2,7 @@ import operator
 import re
 
 import sqlalchemy as sa
+from sqlalchemy import func
 from .. import config
 from .. import engines
 from .. import eq_
@@ -1013,32 +1014,62 @@ class ComponentReflectionTest(fixtures.TablesTest):
     @testing.requires.indexes_with_expressions
     @testing.provide_metadata
     def test_reflect_expression_based_indexes(self):
-        Table(
+        t = Table(
             "t",
             self.metadata,
             Column("x", String(30)),
             Column("y", String(30)),
         )
-        event.listen(
-            self.metadata,
-            "after_create",
-            DDL("CREATE INDEX t_idx ON t(lower(x), lower(y))"),
-        )
-        event.listen(
-            self.metadata, "after_create", DDL("CREATE INDEX t_idx_2 ON t(x)")
-        )
-        self.metadata.create_all()
 
-        insp = inspect(self.metadata.bind)
+        Index("t_idx", func.lower(t.c.x), func.lower(t.c.y))
+
+        Index("t_idx_2", t.c.x)
+
+        self.metadata.create_all(testing.db)
+
+        insp = inspect(testing.db)
+
+        expected = [
+            {"name": "t_idx_2", "column_names": ["x"], "unique": False}
+        ]
+        if testing.requires.index_reflects_included_columns.enabled:
+            expected[0]["include_columns"] = []
 
         with expect_warnings(
             "Skipped unsupported reflection of expression-based index t_idx"
         ):
             eq_(
-                insp.get_indexes("t"),
-                [{"name": "t_idx_2", "column_names": ["x"], "unique": 0}],
+                insp.get_indexes("t"), expected,
             )
 
+    @testing.requires.index_reflects_included_columns
+    @testing.provide_metadata
+    def test_reflect_covering_index(self):
+        t = Table(
+            "t",
+            self.metadata,
+            Column("x", String(30)),
+            Column("y", String(30)),
+        )
+        idx = Index("t_idx", t.c.x)
+        idx.dialect_options[testing.db.name]["include"] = ["y"]
+
+        self.metadata.create_all(testing.db)
+
+        insp = inspect(testing.db)
+
+        eq_(
+            insp.get_indexes("t"),
+            [
+                {
+                    "name": "t_idx",
+                    "column_names": ["x"],
+                    "include_columns": ["y"],
+                    "unique": False,
+                }
+            ],
+        )
+
     @testing.requires.unique_constraint_reflection
     def test_get_unique_constraints(self):
         self._test_get_unique_constraints()
@@ -1061,17 +1092,13 @@ class ComponentReflectionTest(fixtures.TablesTest):
         indexes = insp.get_indexes(table_name)
         for ind in indexes:
             ind.pop("dialect_options", None)
+        expected = [
+            {"unique": False, "column_names": ["foo"], "name": "user_tmp_ix"}
+        ]
+        if testing.requires.index_reflects_included_columns.enabled:
+            expected[0]["include_columns"] = []
         eq_(
-            # TODO: we need to add better filtering for indexes/uq constraints
-            # that are doubled up
-            [idx for idx in indexes if idx["name"] == "user_tmp_ix"],
-            [
-                {
-                    "unique": False,
-                    "column_names": ["foo"],
-                    "name": "user_tmp_ix",
-                }
-            ],
+            [idx for idx in indexes if idx["name"] == "user_tmp_ix"], expected,
         )
 
     @testing.requires.unique_constraint_reflection
-- 
cgit v1.2.1