From 452c4de9ed109717b216154fccd031aeb6e02007 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Fri, 8 Apr 2016 14:49:10 -0400 Subject: [PATCH] LP#1568046: Use CTE instead of connectby for group tree queries Signed-off-by: Mike Rylander Signed-off-by: Jason Stephenson Signed-off-by: Ben Shum --- Open-ILS/src/sql/Pg/006.schema.permissions.sql | 59 +++++++++++++++++---- .../XXXX.function.grp-tree-functions-via-cte.sql | 60 ++++++++++++++++++++++ 2 files changed, 108 insertions(+), 11 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.function.grp-tree-functions-via-cte.sql diff --git a/Open-ILS/src/sql/Pg/006.schema.permissions.sql b/Open-ILS/src/sql/Pg/006.schema.permissions.sql index 27d5a428c3..9c310fe8ac 100644 --- a/Open-ILS/src/sql/Pg/006.schema.permissions.sql +++ b/Open-ILS/src/sql/Pg/006.schema.permissions.sql @@ -89,17 +89,16 @@ CREATE TABLE permission.usr_grp_map ( CONSTRAINT usr_grp_once UNIQUE (usr,grp) ); -CREATE OR REPLACE FUNCTION permission.grp_ancestors ( INT ) RETURNS SETOF permission.grp_tree AS $$ - SELECT a.* - FROM connectby('permission.grp_tree'::text,'parent'::text,'id'::text,'name'::text,$1::text,100,'.'::text) - AS t(keyid text, parent_keyid text, level int, branch text,pos int) - JOIN permission.grp_tree a ON a.id::text = t.keyid::text - ORDER BY - CASE WHEN a.parent IS NULL - THEN 0 - ELSE 1 - END, a.name; -$$ LANGUAGE SQL STABLE ROWS 1; +CREATE OR REPLACE FUNCTION permission.grp_ancestors( INT ) RETURNS SETOF permission.grp_tree AS $$ + WITH RECURSIVE grp_ancestors_distance(id, distance) AS ( + SELECT $1, 0 + UNION + SELECT ou.parent, ouad.distance+1 + FROM permission.grp_tree ou JOIN grp_ancestors_distance ouad ON (ou.id = ouad.id) + WHERE ou.parent IS NOT NULL + ) + SELECT ou.* FROM permission.grp_tree ou JOIN grp_ancestors_distance ouad USING (id) ORDER BY ouad.distance DESC; +$$ LANGUAGE SQL ROWS 1; CREATE OR REPLACE FUNCTION permission.grp_ancestors_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$ WITH RECURSIVE grp_ancestors_distance(id, distance) AS ( @@ -122,6 +121,44 @@ CREATE OR REPLACE FUNCTION permission.grp_descendants_distance( INT ) RETURNS TA SELECT * FROM grp_descendants_distance; $$ LANGUAGE SQL STABLE ROWS 1; +CREATE OR REPLACE FUNCTION permission.grp_descendants( INT ) RETURNS SETOF permission.grp_tree AS $$ + WITH RECURSIVE descendant_depth AS ( + SELECT gr.id, + gr.parent, + FROM permission.grp_tree gr + WHERE gr.id = $1 + UNION ALL + SELECT gr.id, + gr.parent, + FROM permission.grp_tree gr + JOIN descendant_depth dd ON (dd.id = gr.parent) + ) SELECT gr.* FROM permission.grp_tree gr JOIN descendant_depth USING (id); +$$ LANGUAGE SQL ROWS 1; + +CREATE OR REPLACE FUNCTION permission.grp_tree_full_path ( INT ) RETURNS SETOF permission.grp_tree AS $$ + SELECT * + FROM permission.grp_tree_ancestors($1) + UNION + SELECT * + FROM permission.grp_tree_descendants($1); +$$ LANGUAGE SQL STABLE ROWS 1; + +CREATE OR REPLACE FUNCTION permission.grp_tree_combined_ancestors ( INT, INT ) RETURNS SETOF permission.grp_tree AS $$ + SELECT * + FROM permission.grp_tree_ancestors($1) + UNION + SELECT * + FROM permission.grp_tree_ancestors($2); +$$ LANGUAGE SQL STABLE ROWS 1; + +CREATE OR REPLACE FUNCTION permission.grp_tree_common_ancestors ( INT, INT ) RETURNS SETOF permission.grp_tree AS $$ + SELECT * + FROM permission.grp_tree_ancestors($1) + INTERSECT + SELECT * + FROM permission.grp_tree_ancestors($2); +$$ LANGUAGE SQL STABLE ROWS 1; + CREATE OR REPLACE FUNCTION permission.usr_perms ( INT ) RETURNS SETOF permission.usr_perm_map AS $$ SELECT DISTINCT ON (usr,perm) * FROM ( diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.grp-tree-functions-via-cte.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.grp-tree-functions-via-cte.sql new file mode 100644 index 0000000000..7646e99166 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.grp-tree-functions-via-cte.sql @@ -0,0 +1,60 @@ +BEGIN; + +-- Replace connectby from the tablefunc extension with CTEs + + +CREATE OR REPLACE FUNCTION permission.grp_ancestors( INT ) RETURNS SETOF permission.grp_tree AS $$ + WITH RECURSIVE grp_ancestors_distance(id, distance) AS ( + SELECT $1, 0 + UNION + SELECT ou.parent, ouad.distance+1 + FROM permission.grp_tree ou JOIN grp_ancestors_distance ouad ON (ou.id = ouad.id) + WHERE ou.parent IS NOT NULL + ) + SELECT ou.* FROM permission.grp_tree ou JOIN grp_ancestors_distance ouad USING (id) ORDER BY ouad.distance DESC; +$$ LANGUAGE SQL ROWS 1; + +-- Add a utility function to find descendant groups. + +CREATE OR REPLACE FUNCTION permission.grp_descendants( INT ) RETURNS SETOF permission.grp_tree AS $$ + WITH RECURSIVE descendant_depth AS ( + SELECT gr.id, + gr.parent, + FROM permission.grp_tree gr + WHERE gr.id = $1 + UNION ALL + SELECT gr.id, + gr.parent, + FROM permission.grp_tree gr + JOIN descendant_depth dd ON (dd.id = gr.parent) + ) SELECT gr.* FROM permission.grp_tree gr JOIN descendant_depth USING (id); +$$ LANGUAGE SQL ROWS 1; + +-- Add utility functions to work with permission groups as general tree-ish sets. + +CREATE OR REPLACE FUNCTION permission.grp_tree_full_path ( INT ) RETURNS SETOF permission.grp_tree AS $$ + SELECT * + FROM permission.grp_tree_ancestors($1) + UNION + SELECT * + FROM permission.grp_tree_descendants($1); +$$ LANGUAGE SQL STABLE ROWS 1; + +CREATE OR REPLACE FUNCTION permission.grp_tree_combined_ancestors ( INT, INT ) RETURNS SETOF permission.grp_tree AS $$ + SELECT * + FROM permission.grp_tree_ancestors($1) + UNION + SELECT * + FROM permission.grp_tree_ancestors($2); +$$ LANGUAGE SQL STABLE ROWS 1; + +CREATE OR REPLACE FUNCTION permission.grp_tree_common_ancestors ( INT, INT ) RETURNS SETOF permission.grp_tree AS $$ + SELECT * + FROM permission.grp_tree_ancestors($1) + INTERSECT + SELECT * + FROM permission.grp_tree_ancestors($2); +$$ LANGUAGE SQL STABLE ROWS 1; + +COMMIT; + -- 2.11.0