liquid_feedback_core

annotate update/core-update.v2.0.9-v2.0.10.sql @ 484:d264e48cffbf

find changesets by author, revision, files, or words in the commit message
Sort harmonic_weight of NULL after zero in updated_or_featured_initiative
author jbe
date Fri Apr 01 17:24:32 2016 +0200 (2016-04-01)
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