From cbef8a4e04d7424da0b6f241f6b2ad35841d63e9 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Tue, 5 Nov 2019 12:30:10 -0500 Subject: [PATCH] LP#1851413: Restore assumed order of full_path Some code, including UI rendering code in the reporting interfaces, assumes that the order of the full_path stored procedure will be the same as for the ancestors and descendants procedures, which is tree order from top to bottom. However, because the full_path procedure simply UNIONs the other two together without an explicit ORDER BY, that may not be -- and for org hierarchies that have been modified heavily, often is not -- the case in practice. This is due to internals of query planning in Postgres. The easiest place to see this issues is in the report interface. Select a template folder that is not currently shared, choose Manage Folder, select Share Folder, click Go, and see the dropdown of options. Under some circumstances, the list of org units in the dropdown there are incorrectly ordered (should be from top of the tree down), and some that should be available are disabled. This commit uses the org unit type depth to order the output as assumed. Signed-off-by: Mike Rylander Signed-off-by: Rogan Hamby Signed-off-by: Galen Charlton --- Open-ILS/src/sql/Pg/020.schema.functions.sql | 13 ++++++++----- .../upgrade/XXXX.function.restore_full_path_order.sql | 17 +++++++++++++++++ 2 files changed, 25 insertions(+), 5 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.function.restore_full_path_order.sql diff --git a/Open-ILS/src/sql/Pg/020.schema.functions.sql b/Open-ILS/src/sql/Pg/020.schema.functions.sql index b2560877e8..9e82ac794f 100644 --- a/Open-ILS/src/sql/Pg/020.schema.functions.sql +++ b/Open-ILS/src/sql/Pg/020.schema.functions.sql @@ -205,11 +205,14 @@ CREATE OR REPLACE FUNCTION actor.org_unit_ancestors_distance( INT ) RETURNS TABL $$ LANGUAGE SQL STABLE ROWS 1; CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor.org_unit AS $$ - SELECT * - FROM actor.org_unit_ancestors($1) - UNION - SELECT * - FROM actor.org_unit_descendants($1); + SELECT aou.* + FROM actor.org_unit AS aou + JOIN ( + (SELECT au.id, t.depth FROM actor.org_unit_ancestors($1) AS au JOIN actor.org_unit_type t ON (au.ou_type = t.id)) + UNION + (SELECT au.id, t.depth FROM actor.org_unit_descendants($1) AS au JOIN actor.org_unit_type t ON (au.ou_type = t.id)) + ) AS ad ON (aou.id=ad.id) + ORDER BY ad.depth; $$ LANGUAGE SQL STABLE ROWS 1; CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT, INT ) RETURNS SETOF actor.org_unit AS $$ diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.restore_full_path_order.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.restore_full_path_order.sql new file mode 100644 index 0000000000..6eb4cc3680 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.restore_full_path_order.sql @@ -0,0 +1,17 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor.org_unit AS $$ + SELECT aou.* + FROM actor.org_unit AS aou + JOIN ( + (SELECT au.id, t.depth FROM actor.org_unit_ancestors($1) AS au JOIN actor.org_unit_type t ON (au.ou_type = t.id)) + UNION + (SELECT au.id, t.depth FROM actor.org_unit_descendants($1) AS au JOIN actor.org_unit_type t ON (au.ou_type = t.id)) + ) AS ad ON (aou.id=ad.id) + ORDER BY ad.depth; +$$ LANGUAGE SQL STABLE; + +COMMIT; + -- 2.11.0