liquid_feedback_core
view update/core-update.v2.0.9-v2.0.10.sql @ 378:e88d0606891f
Bugfix regarding "proportional_order" of suggestions:
Use NULL values explicitly to be sorted last
(includes new suggestions as well as suggestions without any individual rankings)
Use NULL values explicitly to be sorted last
(includes new suggestions as well as suggestions without any individual rankings)
author | jbe |
---|---|
date | Mon Mar 18 09:36:21 2013 +0100 (2013-03-18) |
parents | 389200fd973d |
children |
line source
1 BEGIN;
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
4 SELECT * FROM (VALUES ('2.0.10', 2, 0, 10))
5 AS "subquery"("string", "major", "minor", "revision");
7 DROP FUNCTION "delegation_info"
8 ( "member"."id"%TYPE,
9 "unit"."id"%TYPE,
10 "area"."id"%TYPE,
11 "issue"."id"%TYPE,
12 "member"."id"%TYPE );
14 DROP TYPE "delegation_info_type";
17 CREATE TYPE "delegation_info_type" AS (
18 "own_participation" BOOLEAN,
19 "own_delegation_scope" "delegation_scope",
20 "first_trustee_id" INT4,
21 "first_trustee_participation" BOOLEAN,
22 "first_trustee_ellipsis" BOOLEAN,
23 "other_trustee_id" INT4,
24 "other_trustee_participation" BOOLEAN,
25 "other_trustee_ellipsis" BOOLEAN,
26 "delegation_loop" "delegation_info_loop_type",
27 "participating_member_id" INT4 );
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';
31 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';
32 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';
33 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';
34 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
35 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';
36 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';
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)';
38 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';
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';
40 COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain';
43 CREATE FUNCTION "delegation_info"
44 ( "member_id_p" "member"."id"%TYPE,
45 "unit_id_p" "unit"."id"%TYPE,
46 "area_id_p" "area"."id"%TYPE,
47 "issue_id_p" "issue"."id"%TYPE,
48 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL )
49 RETURNS "delegation_info_type"
50 LANGUAGE 'plpgsql' STABLE AS $$
51 DECLARE
52 "current_row" "delegation_chain_row";
53 "result" "delegation_info_type";
54 BEGIN
55 "result"."own_participation" := FALSE;
56 FOR "current_row" IN
57 SELECT * FROM "delegation_chain"(
58 "member_id_p",
59 "unit_id_p", "area_id_p", "issue_id_p",
60 "simulate_trustee_id_p")
61 LOOP
62 IF
63 "result"."participating_member_id" ISNULL AND
64 "current_row"."participation"
65 THEN
66 "result"."participating_member_id" := "current_row"."member_id";
67 END IF;
68 IF "current_row"."member_id" = "member_id_p" THEN
69 "result"."own_participation" := "current_row"."participation";
70 "result"."own_delegation_scope" := "current_row"."scope_out";
71 IF "current_row"."loop" = 'first' THEN
72 "result"."delegation_loop" := 'own';
73 END IF;
74 ELSIF
75 "current_row"."member_valid" AND
76 ( "current_row"."loop" ISNULL OR
77 "current_row"."loop" != 'repetition' )
78 THEN
79 IF "result"."first_trustee_id" ISNULL THEN
80 "result"."first_trustee_id" := "current_row"."member_id";
81 "result"."first_trustee_participation" := "current_row"."participation";
82 "result"."first_trustee_ellipsis" := FALSE;
83 IF "current_row"."loop" = 'first' THEN
84 "result"."delegation_loop" := 'first';
85 END IF;
86 ELSIF "result"."other_trustee_id" ISNULL THEN
87 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
88 "result"."other_trustee_id" := "current_row"."member_id";
89 "result"."other_trustee_participation" := TRUE;
90 "result"."other_trustee_ellipsis" := FALSE;
91 IF "current_row"."loop" = 'first' THEN
92 "result"."delegation_loop" := 'other';
93 END IF;
94 ELSE
95 "result"."first_trustee_ellipsis" := TRUE;
96 IF "current_row"."loop" = 'first' THEN
97 "result"."delegation_loop" := 'first_ellipsis';
98 END IF;
99 END IF;
100 ELSE
101 "result"."other_trustee_ellipsis" := TRUE;
102 IF "current_row"."loop" = 'first' THEN
103 "result"."delegation_loop" := 'other_ellipsis';
104 END IF;
105 END IF;
106 END IF;
107 END LOOP;
108 RETURN "result";
109 END;
110 $$;
112 COMMENT ON FUNCTION "delegation_info"
113 ( "member"."id"%TYPE,
114 "unit"."id"%TYPE,
115 "area"."id"%TYPE,
116 "issue"."id"%TYPE,
117 "member"."id"%TYPE )
118 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
121 COMMIT;