From 652a2cba2ef8cded15ba72c909f4f14b4b8ca56d Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Mon, 20 Mar 2017 12:09:07 -0400 Subject: [PATCH] JBAS-1767 PG 9.6 remove connect-by Signed-off-by: Bill Erickson --- KCLS/sql/schema/deploy/pg-9.6-prep.sql | 32 ++++++++++++++++++++++++++++++++ KCLS/sql/schema/revert/pg-9.6-prep.sql | 33 +++++++++++++++++++++++++++++++++ KCLS/sql/schema/sqitch.plan | 1 + KCLS/sql/schema/verify/pg-9.6-prep.sql | 7 +++++++ 4 files changed, 73 insertions(+) create mode 100644 KCLS/sql/schema/deploy/pg-9.6-prep.sql create mode 100644 KCLS/sql/schema/revert/pg-9.6-prep.sql create mode 100644 KCLS/sql/schema/verify/pg-9.6-prep.sql diff --git a/KCLS/sql/schema/deploy/pg-9.6-prep.sql b/KCLS/sql/schema/deploy/pg-9.6-prep.sql new file mode 100644 index 0000000000..6899ed4f25 --- /dev/null +++ b/KCLS/sql/schema/deploy/pg-9.6-prep.sql @@ -0,0 +1,32 @@ +-- Deploy kcls-evergreen:pg-9.6-prep to pg +-- requires: 2.7-to-2.9-upgrade-part-2 + +BEGIN; + +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_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; + + +COMMIT; diff --git a/KCLS/sql/schema/revert/pg-9.6-prep.sql b/KCLS/sql/schema/revert/pg-9.6-prep.sql new file mode 100644 index 0000000000..e028713282 --- /dev/null +++ b/KCLS/sql/schema/revert/pg-9.6-prep.sql @@ -0,0 +1,33 @@ +-- Revert kcls-evergreen:pg-9.6-prep from pg + +BEGIN; + +CREATE OR REPLACE FUNCTION permission.grp_ancestors(integer) + RETURNS SETOF permission.grp_tree + LANGUAGE sql STABLE ROWS 1 + 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; +$_$; + + +CREATE OR REPLACE FUNCTION permission.grp_descendants_distance(integer) + RETURNS TABLE(id integer, distance integer) + LANGUAGE sql STABLE ROWS 1 + AS $_$ + WITH RECURSIVE permission.grp_descendants_distance(id, distance) AS ( + SELECT $1, 0 + UNION + SELECT pgt.id, gdd.distance+1 + FROM permission.grp_tree pgt JOIN permission.grp_descendants_distance gdd ON pgt.parent = gdd.id + ) + SELECT * FROM permission.grp_descendants_distance; +$_$; +COMMIT; diff --git a/KCLS/sql/schema/sqitch.plan b/KCLS/sql/schema/sqitch.plan index bf93f16b95..34cfffc2e3 100644 --- a/KCLS/sql/schema/sqitch.plan +++ b/KCLS/sql/schema/sqitch.plan @@ -41,3 +41,4 @@ backstage-exports-continued [ingram-edi-mods] 2016-06-20T20:28:38Z Bill Erickson purge-holds [audit-table-maint] 2016-09-21T16:01:57Z Bill Erickson # Holds purge settings 2.7-to-2.9-upgrade [backstage-exports-continued] 2016-12-09T20:27:17Z Bill Erickson # 2.7 to 2.9 Upgrade 2.7-to-2.9-upgrade-part-2 [2.7-to-2.9-upgrade] 2016-12-12T15:19:07Z Bill Erickson # 2.7 to 2.9 Upgrade Secondary Updates +pg-9.6-prep [2.7-to-2.9-upgrade-part-2] 2017-03-20T15:59:10Z Bill Erickson,,, # Prep for PG 9.6 diff --git a/KCLS/sql/schema/verify/pg-9.6-prep.sql b/KCLS/sql/schema/verify/pg-9.6-prep.sql new file mode 100644 index 0000000000..1e182f2532 --- /dev/null +++ b/KCLS/sql/schema/verify/pg-9.6-prep.sql @@ -0,0 +1,7 @@ +-- Verify kcls-evergreen:pg-9.6-prep on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; -- 2.11.0