liquid_feedback_core

annotate update/core-update.v2.0.9-v2.0.10.sql @ 605:f50171274974

find changesets by author, revision, files, or words in the commit message
Included hint in README on solving apparently missing data types from extensions
author jbe
date Tue Feb 11 17:10:40 2020 +0100 (2020-02-11)
parents 389200fd973d
children
rev   line source
jbe@253 1 BEGIN;
jbe@253 2
jbe@253 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@253 4 SELECT * FROM (VALUES ('2.0.10', 2, 0, 10))
jbe@253 5 AS "subquery"("string", "major", "minor", "revision");
jbe@253 6
jbe@253 7 DROP FUNCTION "delegation_info"
jbe@253 8 ( "member"."id"%TYPE,
jbe@253 9 "unit"."id"%TYPE,
jbe@253 10 "area"."id"%TYPE,
jbe@253 11 "issue"."id"%TYPE,
jbe@253 12 "member"."id"%TYPE );
jbe@253 13
jbe@253 14 DROP TYPE "delegation_info_type";
jbe@253 15
jbe@253 16
jbe@253 17 CREATE TYPE "delegation_info_type" AS (
jbe@253 18 "own_participation" BOOLEAN,
jbe@253 19 "own_delegation_scope" "delegation_scope",
jbe@253 20 "first_trustee_id" INT4,
jbe@253 21 "first_trustee_participation" BOOLEAN,
jbe@253 22 "first_trustee_ellipsis" BOOLEAN,
jbe@253 23 "other_trustee_id" INT4,
jbe@253 24 "other_trustee_participation" BOOLEAN,
jbe@253 25 "other_trustee_ellipsis" BOOLEAN,
jbe@253 26 "delegation_loop" "delegation_info_loop_type",
jbe@253 27 "participating_member_id" INT4 );
jbe@253 28
jbe@253 29 COMMENT ON TYPE "delegation_info_type" IS 'Type of result returned by "delegation_info" function; For meaning of "participation" check comment on "delegation_chain_row" type';
jbe@253 30
jbe@253 31 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';
jbe@253 32 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';
jbe@253 33 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';
jbe@253 34 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
jbe@253 35 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';
jbe@253 36 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';
jbe@253 37 COMMENT ON COLUMN "delegation_info_type"."other_trustee_participation" IS 'Another trustee is participating (redundant field: if "other_trustee_id" is set, then "other_trustee_participation" is always TRUE, else "other_trustee_participation" is NULL)';
jbe@253 38 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';
jbe@253 39 COMMENT ON COLUMN "delegation_info_type"."delegation_loop" IS 'Non-NULL value, if delegation chain contains a circle; See comment on "delegation_info_loop_type" for details';
jbe@253 40 COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain';
jbe@253 41
jbe@253 42
jbe@253 43 CREATE FUNCTION "delegation_info"
jbe@253 44 ( "member_id_p" "member"."id"%TYPE,
jbe@253 45 "unit_id_p" "unit"."id"%TYPE,
jbe@253 46 "area_id_p" "area"."id"%TYPE,
jbe@253 47 "issue_id_p" "issue"."id"%TYPE,
jbe@253 48 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL )
jbe@253 49 RETURNS "delegation_info_type"
jbe@253 50 LANGUAGE 'plpgsql' STABLE AS $$
jbe@253 51 DECLARE
jbe@253 52 "current_row" "delegation_chain_row";
jbe@253 53 "result" "delegation_info_type";
jbe@253 54 BEGIN
jbe@253 55 "result"."own_participation" := FALSE;
jbe@253 56 FOR "current_row" IN
jbe@253 57 SELECT * FROM "delegation_chain"(
jbe@253 58 "member_id_p",
jbe@253 59 "unit_id_p", "area_id_p", "issue_id_p",
jbe@253 60 "simulate_trustee_id_p")
jbe@253 61 LOOP
jbe@253 62 IF
jbe@253 63 "result"."participating_member_id" ISNULL AND
jbe@253 64 "current_row"."participation"
jbe@253 65 THEN
jbe@253 66 "result"."participating_member_id" := "current_row"."member_id";
jbe@253 67 END IF;
jbe@253 68 IF "current_row"."member_id" = "member_id_p" THEN
jbe@253 69 "result"."own_participation" := "current_row"."participation";
jbe@253 70 "result"."own_delegation_scope" := "current_row"."scope_out";
jbe@253 71 IF "current_row"."loop" = 'first' THEN
jbe@253 72 "result"."delegation_loop" := 'own';
jbe@253 73 END IF;
jbe@253 74 ELSIF
jbe@253 75 "current_row"."member_valid" AND
jbe@253 76 ( "current_row"."loop" ISNULL OR
jbe@253 77 "current_row"."loop" != 'repetition' )
jbe@253 78 THEN
jbe@253 79 IF "result"."first_trustee_id" ISNULL THEN
jbe@253 80 "result"."first_trustee_id" := "current_row"."member_id";
jbe@253 81 "result"."first_trustee_participation" := "current_row"."participation";
jbe@253 82 "result"."first_trustee_ellipsis" := FALSE;
jbe@253 83 IF "current_row"."loop" = 'first' THEN
jbe@253 84 "result"."delegation_loop" := 'first';
jbe@253 85 END IF;
jbe@253 86 ELSIF "result"."other_trustee_id" ISNULL THEN
jbe@253 87 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
jbe@253 88 "result"."other_trustee_id" := "current_row"."member_id";
jbe@253 89 "result"."other_trustee_participation" := TRUE;
jbe@253 90 "result"."other_trustee_ellipsis" := FALSE;
jbe@253 91 IF "current_row"."loop" = 'first' THEN
jbe@253 92 "result"."delegation_loop" := 'other';
jbe@253 93 END IF;
jbe@253 94 ELSE
jbe@253 95 "result"."first_trustee_ellipsis" := TRUE;
jbe@253 96 IF "current_row"."loop" = 'first' THEN
jbe@253 97 "result"."delegation_loop" := 'first_ellipsis';
jbe@253 98 END IF;
jbe@253 99 END IF;
jbe@253 100 ELSE
jbe@253 101 "result"."other_trustee_ellipsis" := TRUE;
jbe@253 102 IF "current_row"."loop" = 'first' THEN
jbe@253 103 "result"."delegation_loop" := 'other_ellipsis';
jbe@253 104 END IF;
jbe@253 105 END IF;
jbe@253 106 END IF;
jbe@253 107 END LOOP;
jbe@253 108 RETURN "result";
jbe@253 109 END;
jbe@253 110 $$;
jbe@253 111
jbe@253 112 COMMENT ON FUNCTION "delegation_info"
jbe@253 113 ( "member"."id"%TYPE,
jbe@253 114 "unit"."id"%TYPE,
jbe@253 115 "area"."id"%TYPE,
jbe@253 116 "issue"."id"%TYPE,
jbe@253 117 "member"."id"%TYPE )
jbe@253 118 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
jbe@253 119
jbe@253 120
jbe@253 121 COMMIT;

Impressum / About Us