Reflect included columns as dialect_options

Fixed reflection of covering indexes to report ``include_columns`` as part
of the ``dialect_options`` entry in the reflected index dictionary, thereby
enabling round trips from reflection->create to be complete. Included
columns continue to also be present under the ``include_columns`` key for
backwards compatibility.

Fixes: #7382
Change-Id: I4f16b65caed3a36d405481690a3a92432b5efd62

3 files changed, 26 insertions, 0 deletions

diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 5b38c4bb5..03ab9e85e 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -3050,6 +3050,12 @@ class MSDialect(default.DefaultDialect):
                     indexes[row["index_id"]]["column_names"].append(
                         row["name"]
                     )
+        for index_info in indexes.values():
+            # NOTE: "root level" include_columns is legacy, now part of
+            #       dialect_options (issue #7382)
+            index_info.setdefault("dialect_options", {})[
+                "mssql_include"
+            ] = index_info["include_columns"]
 
         return list(indexes.values())
 
diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 614c84b56..869e48bd4 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -4242,6 +4242,8 @@ class PGDialect(default.DefaultDialect):
                 "column_names": [idx["cols"][i] for i in idx["key"]],
             }
             if self.server_version_info >= (11, 0):
+                # NOTE: this is legacy, this is part of dialect_options now
+                # as of #7382
                 entry["include_columns"] = [idx["cols"][i] for i in idx["inc"]]
             if "duplicates_constraint" in idx:
                 entry["duplicates_constraint"] = idx["duplicates_constraint"]
@@ -4250,6 +4252,10 @@ class PGDialect(default.DefaultDialect):
                     (idx["cols"][idx["key"][i]], value)
                     for i, value in idx["sorting"].items()
                 )
+            if "include_columns" in entry:
+                entry.setdefault("dialect_options", {})[
+                    "postgresql_include"
+                ] = entry["include_columns"]
             if "options" in idx:
                 entry.setdefault("dialect_options", {})[
                     "postgresql_with"
diff --git a/lib/sqlalchemy/testing/suite/test_reflection.py b/lib/sqlalchemy/testing/suite/test_reflection.py
index ba176bcd9..9287f76de 100644
--- a/lib/sqlalchemy/testing/suite/test_reflection.py
+++ b/lib/sqlalchemy/testing/suite/test_reflection.py
@@ -1232,6 +1232,9 @@ class ComponentReflectionTestExtra(fixtures.TestBase):
         ]
         if testing.requires.index_reflects_included_columns.enabled:
             expected[0]["include_columns"] = []
+            expected[0]["dialect_options"] = {
+                "%s_include" % connection.engine.name: []
+            }
 
         with expect_warnings(
             "Skipped unsupported reflection of expression-based index t_idx"
@@ -1264,10 +1267,21 @@ class ComponentReflectionTestExtra(fixtures.TestBase):
                     "column_names": ["x"],
                     "include_columns": ["y"],
                     "unique": False,
+                    "dialect_options": {
+                        "%s_include" % connection.engine.name: ["y"]
+                    },
                 }
             ],
         )
 
+        t2 = Table("t", MetaData(), autoload_with=connection)
+        eq_(
+            list(t2.indexes)[0].dialect_options[connection.engine.name][
+                "include"
+            ],
+            ["y"],
+        )
+
     def _type_round_trip(self, connection, metadata, *types):
         t = Table(
             "t",