From bec49179785cb820716cee28e60a323a56ea3e4d Mon Sep 17 00:00:00 2001 From: miker Date: Mon, 25 Oct 2010 18:01:25 +0000 Subject: [PATCH] In 2.0+ we require PG 8.4+ which means we have WITH RECURSIVE support, so use that instead of tablefunc connect_by. git-svn-id: svn://svn.open-ils.org/ILS/trunk@18462 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/020.schema.functions.sql | 108 +++++++++++++++------ .../Pg/upgrade/0445.schema.replace-connect_by.sql | 85 ++++++++++++++++ 3 files changed, 164 insertions(+), 31 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0445.schema.replace-connect_by.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 51488080f..9e2a7126c 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -70,7 +70,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0444'); -- gmc +INSERT INTO config.upgrade_log (version) VALUES ('0445'); -- miker CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/020.schema.functions.sql b/Open-ILS/src/sql/Pg/020.schema.functions.sql index 1f6b39cb3..ec191dc02 100644 --- a/Open-ILS/src/sql/Pg/020.schema.functions.sql +++ b/Open-ILS/src/sql/Pg/020.schema.functions.sql @@ -156,36 +156,84 @@ CREATE OR REPLACE FUNCTION tableoid2name ( oid ) RETURNS TEXT AS $$ END; $$ language 'plpgsql'; - -CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT ) RETURNS SETOF actor.org_unit AS $$ - SELECT a.* - FROM connectby('actor.org_unit'::text,'id'::text,'parent_ou'::text,'name'::text,$1::text,100,'.'::text) - AS t(keyid text, parent_keyid text, level int, branch text,pos int) - JOIN actor.org_unit a ON a.id::text = t.keyid::text - ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name; -$$ LANGUAGE SQL STABLE; - -CREATE OR REPLACE FUNCTION actor.org_unit_ancestors ( INT ) RETURNS SETOF actor.org_unit AS $$ - SELECT a.* - FROM connectby('actor.org_unit'::text,'parent_ou'::text,'id'::text,'name'::text,$1::text,100,'.'::text) - AS t(keyid text, parent_keyid text, level int, branch text,pos int) - JOIN actor.org_unit a ON a.id::text = t.keyid::text - JOIN actor.org_unit_type tp ON tp.id = a.ou_type - ORDER BY tp.depth, a.name; -$$ LANGUAGE SQL STABLE; - -CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT,INT ) RETURNS SETOF actor.org_unit AS $$ - SELECT a.* - FROM connectby('actor.org_unit'::text,'id'::text,'parent_ou'::text,'name'::text, - (SELECT x.id - FROM actor.org_unit_ancestors($1) x - JOIN actor.org_unit_type y ON x.ou_type = y.id - WHERE y.depth = $2)::text - ,100,'.'::text) - AS t(keyid text, parent_keyid text, level int, branch text,pos int) - JOIN actor.org_unit a ON a.id::text = t.keyid::text - ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name; -$$ LANGUAGE SQL STABLE; +CREATE OR REPLACE FUNCTION actor.org_unit_descendants( INT, INT ) RETURNS SETOF actor.org_unit AS $$ + WITH RECURSIVE descendant_depth AS ( + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + JOIN anscestor_depth ad ON (ad.id = ou.id) + WHERE ad.depth = $2 + UNION ALL + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + JOIN descendant_depth ot ON (ot.id = ou.parent_ou) + ), anscestor_depth AS ( + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + WHERE ou.id = $1 + UNION ALL + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + JOIN anscestor_depth ot ON (ot.parent_ou = ou.id) + ) SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id); +$$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION actor.org_unit_descendants( INT ) RETURNS SETOF actor.org_unit AS $$ + WITH RECURSIVE descendant_depth AS ( + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + WHERE ou.id = $1 + UNION ALL + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + JOIN descendant_depth ot ON (ot.id = ou.parent_ou) + ), anscestor_depth AS ( + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + WHERE ou.id = $1 + UNION ALL + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + JOIN anscestor_depth ot ON (ot.parent_ou = ou.id) + ) SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id); +$$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION actor.org_unit_ancestors( INT ) RETURNS SETOF actor.org_unit AS $$ + WITH RECURSIVE anscestor_depth AS ( + SELECT ou.id, + ou.parent_ou + FROM actor.org_unit ou + WHERE ou.id = $1 + UNION ALL + SELECT ou.id, + ou.parent_ou + FROM actor.org_unit ou + JOIN anscestor_depth ot ON (ot.parent_ou = ou.id) + ) SELECT ou.* FROM actor.org_unit ou JOIN anscestor_depth USING (id); +$$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_at_depth ( INT,INT ) RETURNS actor.org_unit AS $$ SELECT a.* diff --git a/Open-ILS/src/sql/Pg/upgrade/0445.schema.replace-connect_by.sql b/Open-ILS/src/sql/Pg/upgrade/0445.schema.replace-connect_by.sql new file mode 100644 index 000000000..54bda1f82 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0445.schema.replace-connect_by.sql @@ -0,0 +1,85 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0445'); -- miker + +CREATE OR REPLACE FUNCTION actor.org_unit_descendants( INT, INT ) RETURNS SETOF actor.org_unit AS $$ + WITH RECURSIVE descendant_depth AS ( + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + JOIN anscestor_depth ad ON (ad.id = ou.id) + WHERE ad.depth = $2 + UNION ALL + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + JOIN descendant_depth ot ON (ot.id = ou.parent_ou) + ), anscestor_depth AS ( + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + WHERE ou.id = $1 + UNION ALL + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + JOIN anscestor_depth ot ON (ot.parent_ou = ou.id) + ) SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id); +$$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION actor.org_unit_descendants( INT ) RETURNS SETOF actor.org_unit AS $$ + WITH RECURSIVE descendant_depth AS ( + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + WHERE ou.id = $1 + UNION ALL + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + JOIN descendant_depth ot ON (ot.id = ou.parent_ou) + ), anscestor_depth AS ( + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + WHERE ou.id = $1 + UNION ALL + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + JOIN anscestor_depth ot ON (ot.parent_ou = ou.id) + ) SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id); +$$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION actor.org_unit_ancestors( INT ) RETURNS SETOF actor.org_unit AS $$ + WITH RECURSIVE anscestor_depth AS ( + SELECT ou.id, + ou.parent_ou + FROM actor.org_unit ou + WHERE ou.id = $1 + UNION ALL + SELECT ou.id, + ou.parent_ou + FROM actor.org_unit ou + JOIN anscestor_depth ot ON (ot.parent_ou = ou.id) + ) SELECT ou.* FROM actor.org_unit ou JOIN anscestor_depth USING (id); +$$ LANGUAGE SQL; + +COMMIT; + -- 2.11.0