From 3cbb067bd46776fdb125553ba0ac192cb45d060c Mon Sep 17 00:00:00 2001 From: Eli Collins Date: Wed, 12 Jun 2019 14:46:46 -0400 Subject: [PATCH] PostgreSQL now reflects per-column sort order on indexes. * Dialect.get_indexes() / Inspector._reflect_indexes() expanded to support per-column sort order via "column_sorting" keyword. * PGDialect.get_indexes() now reflects per-column sort order from "pg_index.indoption" in system schema; unittest added. Fixes: 4717 --- lib/sqlalchemy/dialects/postgresql/base.py | 36 +++++++++- lib/sqlalchemy/engine/reflection.py | 29 +++++++- test/dialect/postgresql/test_reflection.py | 77 ++++++++++++++++++++++ 3 files changed, 138 insertions(+), 4 deletions(-) diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 1363e81af7..c28ff97e31 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -3196,7 +3196,7 @@ class PGDialect(default.DefaultDialect): i.relname as relname, ix.indisunique, ix.indexprs, ix.indpred, a.attname, a.attnum, NULL, ix.indkey%s, - %s, am.amname + %s, %s, am.amname FROM pg_class t join pg_index ix on t.oid = ix.indrelid @@ -3219,6 +3219,9 @@ class PGDialect(default.DefaultDialect): # cast does not work in PG 8.2.4, does work in 8.3.0. # nothing in PG changelogs regarding this. "::varchar" if self.server_version_info >= (8, 3) else "", + "ix.indoption::varchar" + if self.server_version_info >= (8, 3) + else "NULL", "i.reloptions" if self.server_version_info >= (8, 2) else "NULL", @@ -3230,7 +3233,7 @@ class PGDialect(default.DefaultDialect): i.relname as relname, ix.indisunique, ix.indexprs, ix.indpred, a.attname, a.attnum, c.conrelid, ix.indkey::varchar, - i.reloptions, am.amname + ix.indoption::varchar, i.reloptions, am.amname FROM pg_class t join pg_index ix on t.oid = ix.indrelid @@ -3273,6 +3276,7 @@ class PGDialect(default.DefaultDialect): col_num, conrelid, idx_key, + idx_option, options, amname, ) = row @@ -3299,6 +3303,29 @@ class PGDialect(default.DefaultDialect): index["cols"][col_num] = col if not has_idx: index["key"] = [int(k.strip()) for k in idx_key.split()] + + # (new in pg 8.3) + # "pg_index.indoption" is list of ints, one per column/expr. + # int acts as bitmask: 0x01=DESC, 0x02=NULLSFIRST + sorting = {} + for col_idx, col_flags in enumerate( + (idx_option or "").split() + ): + col_flags = int(col_flags.strip()) + col_sorting = {} + # try to set flags only if they differ from PG defaults... + if col_flags & 0x01: + col_sorting["desc"] = True + if not (col_flags & 0x02): + col_sorting["nullslast"] = True + else: + if col_flags & 0x02: + col_sorting["nullsfirst"] = True + if col_sorting: + sorting[col_idx] = col_sorting + if sorting: + index["sorting"] = sorting + index["unique"] = unique if conrelid is not None: index["duplicates_constraint"] = idx_name @@ -3323,6 +3350,11 @@ class PGDialect(default.DefaultDialect): } if "duplicates_constraint" in idx: entry["duplicates_constraint"] = idx["duplicates_constraint"] + if "sorting" in idx: + entry["column_sorting"] = dict( + (idx["cols"][idx["key"][i]], value) + for i, value in idx["sorting"].items() + ) if "options" in idx: entry.setdefault("dialect_options", {})[ "postgresql_with" diff --git a/lib/sqlalchemy/engine/reflection.py b/lib/sqlalchemy/engine/reflection.py index 8881492261..6f05cd41a0 100644 --- a/lib/sqlalchemy/engine/reflection.py +++ b/lib/sqlalchemy/engine/reflection.py @@ -469,6 +469,13 @@ class Inspector(object): unique boolean + column_sorting + optional dict mapping column names to dict of sorting options. + each sorting options dict may set any of the following keywords + to True: ``asc``, ``desc``, ``nullsfirst``, ``nullslast``. + + .. versionadded:: 1.3.5 + dialect_options dict of dialect-specific index options. May not be present for all dialects. @@ -869,6 +876,7 @@ class Inspector(object): for index_d in indexes: name = index_d["name"] columns = index_d["column_names"] + column_sorting = index_d.get("column_sorting", {}) unique = index_d["unique"] flavor = index_d.get("type", "index") dialect_options = index_d.get("dialect_options", {}) @@ -897,8 +905,11 @@ class Inspector(object): "%s key '%s' was not located in " "columns for table '%s'" % (flavor, c, table_name) ) - else: - idx_cols.append(idx_col) + continue + c_sorting = column_sorting.get(c) + if c_sorting: + idx_col = self._create_sorted_expr(idx_col, **c_sorting) + idx_cols.append(idx_col) sa_schema.Index( name, @@ -907,6 +918,20 @@ class Inspector(object): **dict(list(dialect_options.items()) + [("unique", unique)]) ) + @classmethod + def _create_sorted_expr( + cls, expr, asc=False, desc=False, nullsfirst=False, nullslast=False + ): + if asc: + expr = expr.asc() + if desc: + expr = expr.desc() + if nullsfirst: + expr = expr.nullsfirst() + if nullslast: + expr = expr.nullslast() + return expr + def _reflect_unique_constraints( self, table_name, diff --git a/test/dialect/postgresql/test_reflection.py b/test/dialect/postgresql/test_reflection.py index f2e4911679..9580018be2 100644 --- a/test/dialect/postgresql/test_reflection.py +++ b/test/dialect/postgresql/test_reflection.py @@ -907,6 +907,83 @@ class ReflectionTest(fixtures.TestBase): ], ) + @testing.fails_if("postgresql < 8.3", "index ordering not supported") + @testing.provide_metadata + def test_index_reflection_with_sorting(self): + """reflect indexes with sorting options set""" + + t1 = Table( + "party", + self.metadata, + Column("id", String(10), nullable=False), + Column("name", String(20)), + Column("aname", String(20)), + ) + + with testing.db.connect() as conn: + + t1.create(conn) + + # check ASC, DESC options alone + conn.execute( + """ + create index idx1 on party + (id, name ASC, aname DESC) + """ + ) + + # check DESC w/ NULLS options + conn.execute( + """ + create index idx2 on party + (name DESC NULLS FIRST, aname DESC NULLS LAST) + """ + ) + + # check ASC w/ NULLS options + conn.execute( + """ + create index idx3 on party + (name ASC NULLS FIRST, aname ASC NULLS LAST) + """ + ) + + # reflect data + with testing.db.connect() as conn: + m2 = MetaData(conn) + t2 = Table("party", m2, autoload=True) + + eq_(len(t2.indexes), 3) + + # Make sure indexes are in the order we expect them in + r1, r2, r3 = sorted(t2.indexes, key=lambda idx: idx.name) + + eq_(r1.name, "idx1") + eq_(r2.name, "idx2") + eq_(r3.name, "idx3") + + # "ASC NULLS LAST" is implicit default for indexes, + # and "NULLS FIRST" is implicit default for "DESC". + # (https://www.postgresql.org/docs/11/indexes-ordering.html) + + def compile_exprs(exprs): + return list(map(str, exprs)) + + eq_( + compile_exprs([t2.c.id, t2.c.name, t2.c.aname.desc()]), + compile_exprs(r1.expressions), + ) + + eq_( + compile_exprs([t2.c.name.desc(), t2.c.aname.desc().nullslast()]), + compile_exprs(r2.expressions), + ) + + eq_( + compile_exprs([t2.c.name.nullsfirst(), t2.c.aname]), + compile_exprs(r3.expressions), + ) + @testing.provide_metadata def test_index_reflection_modified(self): """reflect indexes when a column name has changed - PG 9 -- 2.47.3