liquid_feedback_core

view update/core-update.v2.0.9-v2.0.10.sql @ 593:e7f772ca0621

Removed an index on table "initiative"
author jbe
date Sat Dec 08 01:37:23 2018 +0100 (20 months ago)
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;

Impressum / About Us