rev |
line source |
jbe@619
|
1 BEGIN;
|
jbe@619
|
2
|
jbe@615
|
3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
|
jbe@619
|
4 SELECT * FROM (VALUES ('4.2.1', 4, 2, 1))
|
jbe@615
|
5 AS "subquery"("string", "major", "minor", "revision");
|
jbe@615
|
6
|
jbe@616
|
7 ALTER TABLE "unit" ADD COLUMN "attr" JSONB NOT NULL DEFAULT '{}' CHECK (jsonb_typeof("attr") = 'object');
|
jbe@616
|
8 COMMENT ON COLUMN "unit"."attr" IS 'Opaque data structure to store any extended attributes used by frontend or middleware';
|
jbe@616
|
9
|
jbe@615
|
10 ALTER TABLE "unit" ADD COLUMN "member_weight" INT4;
|
jbe@615
|
11 COMMENT ON COLUMN "unit"."member_weight" IS 'Sum of active members'' voting weight';
|
jbe@615
|
12
|
jbe@615
|
13 ALTER TABLE "snapshot_population" ADD COLUMN "weight" INT4 NOT NULL DEFAULT 1;
|
jbe@615
|
14 ALTER TABLE "snapshot_population" ALTER COLUMN "weight" DROP DEFAULT;
|
jbe@615
|
15
|
jbe@615
|
16 ALTER TABLE "privilege" ADD COLUMN "weight" INT4 NOT NULL DEFAULT 1 CHECK ("weight" >= 0);
|
jbe@615
|
17 COMMENT ON COLUMN "privilege"."weight" IS 'Voting weight of member in unit';
|
jbe@615
|
18
|
jbe@615
|
19 CREATE TABLE "issue_privilege" (
|
jbe@615
|
20 PRIMARY KEY ("issue_id", "member_id"),
|
jbe@615
|
21 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@615
|
22 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
jbe@615
|
23 "initiative_right" BOOLEAN,
|
jbe@615
|
24 "voting_right" BOOLEAN,
|
jbe@615
|
25 "polling_right" BOOLEAN,
|
jbe@615
|
26 "weight" INT4 CHECK ("weight" >= 0) );
|
jbe@615
|
27 CREATE INDEX "issue_privilege_idx" ON "issue_privilege" ("member_id");
|
jbe@615
|
28 COMMENT ON TABLE "issue_privilege" IS 'Override of "privilege" table for rights of members in certain issues';
|
jbe@615
|
29
|
jbe@615
|
30 ALTER TABLE "direct_interest_snapshot" ADD COLUMN "ownweight" INT4 NOT NULL DEFAULT 1;
|
jbe@615
|
31 ALTER TABLE "direct_interest_snapshot" ALTER COLUMN "ownweight" DROP DEFAULT;
|
jbe@615
|
32 COMMENT ON COLUMN "direct_interest_snapshot"."ownweight" IS 'Own voting weight of member, disregading delegations';
|
jbe@615
|
33 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Voting weight of member according to own weight and "delegating_interest_snapshot"';
|
jbe@615
|
34
|
jbe@615
|
35 ALTER TABLE "delegating_interest_snapshot" ADD COLUMN "ownweight" INT4 NOT NULL DEFAULT 1;
|
jbe@615
|
36 ALTER TABLE "delegating_interest_snapshot" ALTER COLUMN "ownweight" DROP DEFAULT;
|
jbe@615
|
37 COMMENT ON COLUMN "delegating_interest_snapshot"."ownweight" IS 'Own voting weight of member, disregading delegations';
|
jbe@615
|
38 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate voting weight considering incoming delegations';
|
jbe@615
|
39
|
jbe@615
|
40 ALTER TABLE "direct_voter" ADD COLUMN "ownweight" INT4 DEFAULT 1;
|
jbe@615
|
41 ALTER TABLE "direct_voter" ALTER COLUMN "ownweight" DROP DEFAULT;
|
jbe@615
|
42 COMMENT ON COLUMN "direct_voter"."ownweight" IS 'Own voting weight of member, disregarding delegations';
|
jbe@615
|
43 COMMENT ON COLUMN "direct_voter"."weight" IS 'Voting weight of member according to own weight and "delegating_interest_snapshot"';
|
jbe@615
|
44
|
jbe@615
|
45 ALTER TABLE "delegating_voter" ADD COLUMN "ownweight" INT4 NOT NULL DEFAULT 1;
|
jbe@615
|
46 ALTER TABLE "delegating_voter" ALTER COLUMN "ownweight" DROP DEFAULT;
|
jbe@615
|
47 COMMENT ON COLUMN "delegating_voter"."ownweight" IS 'Own voting weight of member, disregarding delegations';
|
jbe@615
|
48 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate voting weight considering incoming delegations';
|
jbe@615
|
49
|
jbe@618
|
50 ALTER TABLE "posting" ADD FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id");
|
jbe@618
|
51
|
jbe@615
|
52 DROP VIEW "issue_delegation";
|
jbe@615
|
53 CREATE VIEW "issue_delegation" AS
|
jbe@615
|
54 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
|
jbe@615
|
55 "issue"."id" AS "issue_id",
|
jbe@615
|
56 "delegation"."id",
|
jbe@615
|
57 "delegation"."truster_id",
|
jbe@615
|
58 "delegation"."trustee_id",
|
jbe@615
|
59 COALESCE("issue_privilege"."weight", "privilege"."weight") AS "weight",
|
jbe@615
|
60 "delegation"."scope"
|
jbe@615
|
61 FROM "issue"
|
jbe@615
|
62 JOIN "area"
|
jbe@615
|
63 ON "area"."id" = "issue"."area_id"
|
jbe@615
|
64 JOIN "delegation"
|
jbe@615
|
65 ON "delegation"."unit_id" = "area"."unit_id"
|
jbe@615
|
66 OR "delegation"."area_id" = "area"."id"
|
jbe@615
|
67 OR "delegation"."issue_id" = "issue"."id"
|
jbe@615
|
68 JOIN "member"
|
jbe@615
|
69 ON "delegation"."truster_id" = "member"."id"
|
jbe@615
|
70 LEFT JOIN "privilege"
|
jbe@615
|
71 ON "area"."unit_id" = "privilege"."unit_id"
|
jbe@615
|
72 AND "delegation"."truster_id" = "privilege"."member_id"
|
jbe@615
|
73 LEFT JOIN "issue_privilege"
|
jbe@615
|
74 ON "issue"."id" = "issue_privilege"."issue_id"
|
jbe@615
|
75 AND "delegation"."truster_id" = "issue_privilege"."member_id"
|
jbe@615
|
76 WHERE "member"."active"
|
jbe@615
|
77 AND COALESCE("issue_privilege"."voting_right", "privilege"."voting_right")
|
jbe@615
|
78 ORDER BY
|
jbe@615
|
79 "issue"."id",
|
jbe@615
|
80 "delegation"."truster_id",
|
jbe@615
|
81 "delegation"."scope" DESC;
|
jbe@615
|
82 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
|
jbe@615
|
83
|
jbe@615
|
84 CREATE OR REPLACE VIEW "unit_member" AS
|
jbe@615
|
85 SELECT
|
jbe@615
|
86 "privilege"."unit_id" AS "unit_id",
|
jbe@615
|
87 "member"."id" AS "member_id",
|
jbe@615
|
88 "privilege"."weight"
|
jbe@615
|
89 FROM "privilege" JOIN "member" ON "member"."id" = "privilege"."member_id"
|
jbe@615
|
90 WHERE "privilege"."voting_right" AND "member"."active";
|
jbe@615
|
91
|
jbe@615
|
92 CREATE OR REPLACE VIEW "unit_member_count" AS
|
jbe@615
|
93 SELECT
|
jbe@615
|
94 "unit"."id" AS "unit_id",
|
jbe@615
|
95 count("unit_member"."member_id") AS "member_count",
|
jbe@615
|
96 sum("unit_member"."weight") AS "member_weight"
|
jbe@615
|
97 FROM "unit" LEFT JOIN "unit_member"
|
jbe@615
|
98 ON "unit"."id" = "unit_member"."unit_id"
|
jbe@615
|
99 GROUP BY "unit"."id";
|
jbe@615
|
100
|
jbe@615
|
101 CREATE OR REPLACE VIEW "event_for_notification" AS
|
jbe@615
|
102 SELECT
|
jbe@615
|
103 "member"."id" AS "recipient_id",
|
jbe@615
|
104 "event".*
|
jbe@615
|
105 FROM "member" CROSS JOIN "event"
|
jbe@615
|
106 JOIN "issue" ON "issue"."id" = "event"."issue_id"
|
jbe@615
|
107 JOIN "area" ON "area"."id" = "issue"."area_id"
|
jbe@615
|
108 LEFT JOIN "privilege" ON
|
jbe@615
|
109 "privilege"."member_id" = "member"."id" AND
|
jbe@615
|
110 "privilege"."unit_id" = "area"."unit_id"
|
jbe@615
|
111 LEFT JOIN "issue_privilege" ON
|
jbe@615
|
112 "issue_privilege"."member_id" = "member"."id" AND
|
jbe@615
|
113 "issue_privilege"."issue_id" = "event"."issue_id"
|
jbe@615
|
114 LEFT JOIN "subscription" ON
|
jbe@615
|
115 "subscription"."member_id" = "member"."id" AND
|
jbe@615
|
116 "subscription"."unit_id" = "area"."unit_id"
|
jbe@615
|
117 LEFT JOIN "ignored_area" ON
|
jbe@615
|
118 "ignored_area"."member_id" = "member"."id" AND
|
jbe@615
|
119 "ignored_area"."area_id" = "issue"."area_id"
|
jbe@615
|
120 LEFT JOIN "interest" ON
|
jbe@615
|
121 "interest"."member_id" = "member"."id" AND
|
jbe@615
|
122 "interest"."issue_id" = "event"."issue_id"
|
jbe@615
|
123 LEFT JOIN "supporter" ON
|
jbe@615
|
124 "supporter"."member_id" = "member"."id" AND
|
jbe@615
|
125 "supporter"."initiative_id" = "event"."initiative_id"
|
jbe@615
|
126 WHERE (
|
jbe@615
|
127 COALESCE("issue_privilege"."voting_right", "privilege"."voting_right") OR
|
jbe@615
|
128 "subscription"."member_id" NOTNULL
|
jbe@615
|
129 ) AND ("ignored_area"."member_id" ISNULL OR "interest"."member_id" NOTNULL)
|
jbe@615
|
130 AND (
|
jbe@615
|
131 "event"."event" = 'issue_state_changed'::"event_type" OR
|
jbe@615
|
132 ( "event"."event" = 'initiative_revoked'::"event_type" AND
|
jbe@615
|
133 "supporter"."member_id" NOTNULL ) );
|
jbe@615
|
134
|
jbe@615
|
135 CREATE OR REPLACE FUNCTION "featured_initiative"
|
jbe@615
|
136 ( "recipient_id_p" "member"."id"%TYPE,
|
jbe@615
|
137 "area_id_p" "area"."id"%TYPE )
|
jbe@615
|
138 RETURNS SETOF "initiative"."id"%TYPE
|
jbe@615
|
139 LANGUAGE 'plpgsql' STABLE AS $$
|
jbe@615
|
140 DECLARE
|
jbe@615
|
141 "counter_v" "member"."notification_counter"%TYPE;
|
jbe@615
|
142 "sample_size_v" "member"."notification_sample_size"%TYPE;
|
jbe@615
|
143 "initiative_id_ary" INT4[]; --"initiative"."id"%TYPE[]
|
jbe@615
|
144 "match_v" BOOLEAN;
|
jbe@615
|
145 "member_id_v" "member"."id"%TYPE;
|
jbe@615
|
146 "seed_v" TEXT;
|
jbe@615
|
147 "initiative_id_v" "initiative"."id"%TYPE;
|
jbe@615
|
148 BEGIN
|
jbe@615
|
149 SELECT "notification_counter", "notification_sample_size"
|
jbe@615
|
150 INTO "counter_v", "sample_size_v"
|
jbe@615
|
151 FROM "member" WHERE "id" = "recipient_id_p";
|
jbe@615
|
152 IF COALESCE("sample_size_v" <= 0, TRUE) THEN
|
jbe@615
|
153 RETURN;
|
jbe@615
|
154 END IF;
|
jbe@615
|
155 "initiative_id_ary" := '{}';
|
jbe@615
|
156 LOOP
|
jbe@615
|
157 "match_v" := FALSE;
|
jbe@615
|
158 FOR "member_id_v", "seed_v" IN
|
jbe@615
|
159 SELECT * FROM (
|
jbe@615
|
160 SELECT DISTINCT
|
jbe@615
|
161 "supporter"."member_id",
|
jbe@615
|
162 md5(
|
jbe@615
|
163 "recipient_id_p" || '-' ||
|
jbe@615
|
164 "counter_v" || '-' ||
|
jbe@615
|
165 "area_id_p" || '-' ||
|
jbe@615
|
166 "supporter"."member_id"
|
jbe@615
|
167 ) AS "seed"
|
jbe@615
|
168 FROM "supporter"
|
jbe@615
|
169 JOIN "initiative" ON "initiative"."id" = "supporter"."initiative_id"
|
jbe@615
|
170 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
|
jbe@615
|
171 WHERE "supporter"."member_id" != "recipient_id_p"
|
jbe@615
|
172 AND "issue"."area_id" = "area_id_p"
|
jbe@615
|
173 AND "issue"."state" IN ('admission', 'discussion', 'verification')
|
jbe@615
|
174 ) AS "subquery"
|
jbe@615
|
175 ORDER BY "seed"
|
jbe@615
|
176 LOOP
|
jbe@615
|
177 SELECT "initiative"."id" INTO "initiative_id_v"
|
jbe@615
|
178 FROM "initiative"
|
jbe@615
|
179 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
|
jbe@615
|
180 JOIN "area" ON "area"."id" = "issue"."area_id"
|
jbe@615
|
181 JOIN "supporter" ON "supporter"."initiative_id" = "initiative"."id"
|
jbe@615
|
182 LEFT JOIN "supporter" AS "self_support" ON
|
jbe@615
|
183 "self_support"."initiative_id" = "initiative"."id" AND
|
jbe@615
|
184 "self_support"."member_id" = "recipient_id_p"
|
jbe@615
|
185 LEFT JOIN "privilege" ON
|
jbe@615
|
186 "privilege"."member_id" = "recipient_id_p" AND
|
jbe@615
|
187 "privilege"."unit_id" = "area"."unit_id"
|
jbe@615
|
188 LEFT JOIN "issue_privilege" ON
|
jbe@617
|
189 "issue_privilege"."member_id" = "recipient_id_p" AND
|
jbe@617
|
190 "issue_privilege"."issue_id" = "initiative"."issue_id"
|
jbe@615
|
191 LEFT JOIN "subscription" ON
|
jbe@615
|
192 "subscription"."member_id" = "recipient_id_p" AND
|
jbe@615
|
193 "subscription"."unit_id" = "area"."unit_id"
|
jbe@615
|
194 LEFT JOIN "ignored_initiative" ON
|
jbe@615
|
195 "ignored_initiative"."member_id" = "recipient_id_p" AND
|
jbe@615
|
196 "ignored_initiative"."initiative_id" = "initiative"."id"
|
jbe@615
|
197 WHERE "supporter"."member_id" = "member_id_v"
|
jbe@615
|
198 AND "issue"."area_id" = "area_id_p"
|
jbe@615
|
199 AND "issue"."state" IN ('admission', 'discussion', 'verification')
|
jbe@615
|
200 AND "initiative"."revoked" ISNULL
|
jbe@615
|
201 AND "self_support"."member_id" ISNULL
|
jbe@615
|
202 AND NOT "initiative_id_ary" @> ARRAY["initiative"."id"]
|
jbe@615
|
203 AND (
|
jbe@615
|
204 COALESCE(
|
jbe@615
|
205 "issue_privilege"."voting_right", "privilege"."voting_right"
|
jbe@615
|
206 ) OR "subscription"."member_id" NOTNULL )
|
jbe@615
|
207 AND "ignored_initiative"."member_id" ISNULL
|
jbe@615
|
208 AND NOT EXISTS (
|
jbe@615
|
209 SELECT NULL FROM "draft"
|
jbe@615
|
210 JOIN "ignored_member" ON
|
jbe@615
|
211 "ignored_member"."member_id" = "recipient_id_p" AND
|
jbe@615
|
212 "ignored_member"."other_member_id" = "draft"."author_id"
|
jbe@615
|
213 WHERE "draft"."initiative_id" = "initiative"."id"
|
jbe@615
|
214 )
|
jbe@615
|
215 ORDER BY md5("seed_v" || '-' || "initiative"."id")
|
jbe@615
|
216 LIMIT 1;
|
jbe@615
|
217 IF FOUND THEN
|
jbe@615
|
218 "match_v" := TRUE;
|
jbe@615
|
219 RETURN NEXT "initiative_id_v";
|
jbe@615
|
220 IF array_length("initiative_id_ary", 1) + 1 >= "sample_size_v" THEN
|
jbe@615
|
221 RETURN;
|
jbe@615
|
222 END IF;
|
jbe@615
|
223 "initiative_id_ary" := "initiative_id_ary" || "initiative_id_v";
|
jbe@615
|
224 END IF;
|
jbe@615
|
225 END LOOP;
|
jbe@615
|
226 EXIT WHEN NOT "match_v";
|
jbe@615
|
227 END LOOP;
|
jbe@615
|
228 RETURN;
|
jbe@615
|
229 END;
|
jbe@615
|
230 $$;
|
jbe@615
|
231
|
jbe@615
|
232 CREATE OR REPLACE FUNCTION "delegation_chain"
|
jbe@615
|
233 ( "member_id_p" "member"."id"%TYPE,
|
jbe@615
|
234 "unit_id_p" "unit"."id"%TYPE,
|
jbe@615
|
235 "area_id_p" "area"."id"%TYPE,
|
jbe@615
|
236 "issue_id_p" "issue"."id"%TYPE,
|
jbe@615
|
237 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
|
jbe@615
|
238 "simulate_default_p" BOOLEAN DEFAULT FALSE )
|
jbe@615
|
239 RETURNS SETOF "delegation_chain_row"
|
jbe@615
|
240 LANGUAGE 'plpgsql' STABLE AS $$
|
jbe@615
|
241 DECLARE
|
jbe@615
|
242 "scope_v" "delegation_scope";
|
jbe@615
|
243 "unit_id_v" "unit"."id"%TYPE;
|
jbe@615
|
244 "area_id_v" "area"."id"%TYPE;
|
jbe@615
|
245 "issue_row" "issue"%ROWTYPE;
|
jbe@615
|
246 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
|
jbe@615
|
247 "loop_member_id_v" "member"."id"%TYPE;
|
jbe@615
|
248 "output_row" "delegation_chain_row";
|
jbe@615
|
249 "output_rows" "delegation_chain_row"[];
|
jbe@615
|
250 "simulate_v" BOOLEAN;
|
jbe@615
|
251 "simulate_here_v" BOOLEAN;
|
jbe@615
|
252 "delegation_row" "delegation"%ROWTYPE;
|
jbe@615
|
253 "row_count" INT4;
|
jbe@615
|
254 "i" INT4;
|
jbe@615
|
255 "loop_v" BOOLEAN;
|
jbe@615
|
256 BEGIN
|
jbe@615
|
257 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
|
jbe@615
|
258 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
|
jbe@615
|
259 END IF;
|
jbe@615
|
260 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
|
jbe@615
|
261 "simulate_v" := TRUE;
|
jbe@615
|
262 ELSE
|
jbe@615
|
263 "simulate_v" := FALSE;
|
jbe@615
|
264 END IF;
|
jbe@615
|
265 IF
|
jbe@615
|
266 "unit_id_p" NOTNULL AND
|
jbe@615
|
267 "area_id_p" ISNULL AND
|
jbe@615
|
268 "issue_id_p" ISNULL
|
jbe@615
|
269 THEN
|
jbe@615
|
270 "scope_v" := 'unit';
|
jbe@615
|
271 "unit_id_v" := "unit_id_p";
|
jbe@615
|
272 ELSIF
|
jbe@615
|
273 "unit_id_p" ISNULL AND
|
jbe@615
|
274 "area_id_p" NOTNULL AND
|
jbe@615
|
275 "issue_id_p" ISNULL
|
jbe@615
|
276 THEN
|
jbe@615
|
277 "scope_v" := 'area';
|
jbe@615
|
278 "area_id_v" := "area_id_p";
|
jbe@615
|
279 SELECT "unit_id" INTO "unit_id_v"
|
jbe@615
|
280 FROM "area" WHERE "id" = "area_id_v";
|
jbe@615
|
281 ELSIF
|
jbe@615
|
282 "unit_id_p" ISNULL AND
|
jbe@615
|
283 "area_id_p" ISNULL AND
|
jbe@615
|
284 "issue_id_p" NOTNULL
|
jbe@615
|
285 THEN
|
jbe@615
|
286 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@615
|
287 IF "issue_row"."id" ISNULL THEN
|
jbe@615
|
288 RETURN;
|
jbe@615
|
289 END IF;
|
jbe@615
|
290 IF "issue_row"."closed" NOTNULL THEN
|
jbe@615
|
291 IF "simulate_v" THEN
|
jbe@615
|
292 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
|
jbe@615
|
293 END IF;
|
jbe@615
|
294 FOR "output_row" IN
|
jbe@615
|
295 SELECT * FROM
|
jbe@615
|
296 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
|
jbe@615
|
297 LOOP
|
jbe@615
|
298 RETURN NEXT "output_row";
|
jbe@615
|
299 END LOOP;
|
jbe@615
|
300 RETURN;
|
jbe@615
|
301 END IF;
|
jbe@615
|
302 "scope_v" := 'issue';
|
jbe@615
|
303 SELECT "area_id" INTO "area_id_v"
|
jbe@615
|
304 FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@615
|
305 SELECT "unit_id" INTO "unit_id_v"
|
jbe@615
|
306 FROM "area" WHERE "id" = "area_id_v";
|
jbe@615
|
307 ELSE
|
jbe@615
|
308 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
|
jbe@615
|
309 END IF;
|
jbe@615
|
310 "visited_member_ids" := '{}';
|
jbe@615
|
311 "loop_member_id_v" := NULL;
|
jbe@615
|
312 "output_rows" := '{}';
|
jbe@615
|
313 "output_row"."index" := 0;
|
jbe@615
|
314 "output_row"."member_id" := "member_id_p";
|
jbe@615
|
315 "output_row"."member_valid" := TRUE;
|
jbe@615
|
316 "output_row"."participation" := FALSE;
|
jbe@615
|
317 "output_row"."overridden" := FALSE;
|
jbe@615
|
318 "output_row"."disabled_out" := FALSE;
|
jbe@615
|
319 "output_row"."scope_out" := NULL;
|
jbe@615
|
320 LOOP
|
jbe@615
|
321 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
|
jbe@615
|
322 "loop_member_id_v" := "output_row"."member_id";
|
jbe@615
|
323 ELSE
|
jbe@615
|
324 "visited_member_ids" :=
|
jbe@615
|
325 "visited_member_ids" || "output_row"."member_id";
|
jbe@615
|
326 END IF;
|
jbe@615
|
327 IF "output_row"."participation" ISNULL THEN
|
jbe@615
|
328 "output_row"."overridden" := NULL;
|
jbe@615
|
329 ELSIF "output_row"."participation" THEN
|
jbe@615
|
330 "output_row"."overridden" := TRUE;
|
jbe@615
|
331 END IF;
|
jbe@615
|
332 "output_row"."scope_in" := "output_row"."scope_out";
|
jbe@615
|
333 "output_row"."member_valid" := EXISTS (
|
jbe@615
|
334 SELECT NULL FROM "member"
|
jbe@615
|
335 LEFT JOIN "privilege"
|
jbe@615
|
336 ON "privilege"."member_id" = "member"."id"
|
jbe@615
|
337 AND "privilege"."unit_id" = "unit_id_v"
|
jbe@615
|
338 LEFT JOIN "issue_privilege"
|
jbe@615
|
339 ON "issue_privilege"."member_id" = "member"."id"
|
jbe@615
|
340 AND "issue_privilege"."issue_id" = "issue_id_p"
|
jbe@615
|
341 WHERE "id" = "output_row"."member_id"
|
jbe@615
|
342 AND "member"."active"
|
jbe@615
|
343 AND COALESCE(
|
jbe@615
|
344 "issue_privilege"."voting_right", "privilege"."voting_right")
|
jbe@615
|
345 );
|
jbe@615
|
346 "simulate_here_v" := (
|
jbe@615
|
347 "simulate_v" AND
|
jbe@615
|
348 "output_row"."member_id" = "member_id_p"
|
jbe@615
|
349 );
|
jbe@615
|
350 "delegation_row" := ROW(NULL);
|
jbe@615
|
351 IF "output_row"."member_valid" OR "simulate_here_v" THEN
|
jbe@615
|
352 IF "scope_v" = 'unit' THEN
|
jbe@615
|
353 IF NOT "simulate_here_v" THEN
|
jbe@615
|
354 SELECT * INTO "delegation_row" FROM "delegation"
|
jbe@615
|
355 WHERE "truster_id" = "output_row"."member_id"
|
jbe@615
|
356 AND "unit_id" = "unit_id_v";
|
jbe@615
|
357 END IF;
|
jbe@615
|
358 ELSIF "scope_v" = 'area' THEN
|
jbe@615
|
359 IF "simulate_here_v" THEN
|
jbe@615
|
360 IF "simulate_trustee_id_p" ISNULL THEN
|
jbe@615
|
361 SELECT * INTO "delegation_row" FROM "delegation"
|
jbe@615
|
362 WHERE "truster_id" = "output_row"."member_id"
|
jbe@615
|
363 AND "unit_id" = "unit_id_v";
|
jbe@615
|
364 END IF;
|
jbe@615
|
365 ELSE
|
jbe@615
|
366 SELECT * INTO "delegation_row" FROM "delegation"
|
jbe@615
|
367 WHERE "truster_id" = "output_row"."member_id"
|
jbe@615
|
368 AND (
|
jbe@615
|
369 "unit_id" = "unit_id_v" OR
|
jbe@615
|
370 "area_id" = "area_id_v"
|
jbe@615
|
371 )
|
jbe@615
|
372 ORDER BY "scope" DESC;
|
jbe@615
|
373 END IF;
|
jbe@615
|
374 ELSIF "scope_v" = 'issue' THEN
|
jbe@615
|
375 IF "issue_row"."fully_frozen" ISNULL THEN
|
jbe@615
|
376 "output_row"."participation" := EXISTS (
|
jbe@615
|
377 SELECT NULL FROM "interest"
|
jbe@615
|
378 WHERE "issue_id" = "issue_id_p"
|
jbe@615
|
379 AND "member_id" = "output_row"."member_id"
|
jbe@615
|
380 );
|
jbe@615
|
381 ELSE
|
jbe@615
|
382 IF "output_row"."member_id" = "member_id_p" THEN
|
jbe@615
|
383 "output_row"."participation" := EXISTS (
|
jbe@615
|
384 SELECT NULL FROM "direct_voter"
|
jbe@615
|
385 WHERE "issue_id" = "issue_id_p"
|
jbe@615
|
386 AND "member_id" = "output_row"."member_id"
|
jbe@615
|
387 );
|
jbe@615
|
388 ELSE
|
jbe@615
|
389 "output_row"."participation" := NULL;
|
jbe@615
|
390 END IF;
|
jbe@615
|
391 END IF;
|
jbe@615
|
392 IF "simulate_here_v" THEN
|
jbe@615
|
393 IF "simulate_trustee_id_p" ISNULL THEN
|
jbe@615
|
394 SELECT * INTO "delegation_row" FROM "delegation"
|
jbe@615
|
395 WHERE "truster_id" = "output_row"."member_id"
|
jbe@615
|
396 AND (
|
jbe@615
|
397 "unit_id" = "unit_id_v" OR
|
jbe@615
|
398 "area_id" = "area_id_v"
|
jbe@615
|
399 )
|
jbe@615
|
400 ORDER BY "scope" DESC;
|
jbe@615
|
401 END IF;
|
jbe@615
|
402 ELSE
|
jbe@615
|
403 SELECT * INTO "delegation_row" FROM "delegation"
|
jbe@615
|
404 WHERE "truster_id" = "output_row"."member_id"
|
jbe@615
|
405 AND (
|
jbe@615
|
406 "unit_id" = "unit_id_v" OR
|
jbe@615
|
407 "area_id" = "area_id_v" OR
|
jbe@615
|
408 "issue_id" = "issue_id_p"
|
jbe@615
|
409 )
|
jbe@615
|
410 ORDER BY "scope" DESC;
|
jbe@615
|
411 END IF;
|
jbe@615
|
412 END IF;
|
jbe@615
|
413 ELSE
|
jbe@615
|
414 "output_row"."participation" := FALSE;
|
jbe@615
|
415 END IF;
|
jbe@615
|
416 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
|
jbe@615
|
417 "output_row"."scope_out" := "scope_v";
|
jbe@615
|
418 "output_rows" := "output_rows" || "output_row";
|
jbe@615
|
419 "output_row"."member_id" := "simulate_trustee_id_p";
|
jbe@615
|
420 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
|
jbe@615
|
421 "output_row"."scope_out" := "delegation_row"."scope";
|
jbe@615
|
422 "output_rows" := "output_rows" || "output_row";
|
jbe@615
|
423 "output_row"."member_id" := "delegation_row"."trustee_id";
|
jbe@615
|
424 ELSIF "delegation_row"."scope" NOTNULL THEN
|
jbe@615
|
425 "output_row"."scope_out" := "delegation_row"."scope";
|
jbe@615
|
426 "output_row"."disabled_out" := TRUE;
|
jbe@615
|
427 "output_rows" := "output_rows" || "output_row";
|
jbe@615
|
428 EXIT;
|
jbe@615
|
429 ELSE
|
jbe@615
|
430 "output_row"."scope_out" := NULL;
|
jbe@615
|
431 "output_rows" := "output_rows" || "output_row";
|
jbe@615
|
432 EXIT;
|
jbe@615
|
433 END IF;
|
jbe@615
|
434 EXIT WHEN "loop_member_id_v" NOTNULL;
|
jbe@615
|
435 "output_row"."index" := "output_row"."index" + 1;
|
jbe@615
|
436 END LOOP;
|
jbe@615
|
437 "row_count" := array_upper("output_rows", 1);
|
jbe@615
|
438 "i" := 1;
|
jbe@615
|
439 "loop_v" := FALSE;
|
jbe@615
|
440 LOOP
|
jbe@615
|
441 "output_row" := "output_rows"["i"];
|
jbe@615
|
442 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
|
jbe@615
|
443 IF "loop_v" THEN
|
jbe@615
|
444 IF "i" + 1 = "row_count" THEN
|
jbe@615
|
445 "output_row"."loop" := 'last';
|
jbe@615
|
446 ELSIF "i" = "row_count" THEN
|
jbe@615
|
447 "output_row"."loop" := 'repetition';
|
jbe@615
|
448 ELSE
|
jbe@615
|
449 "output_row"."loop" := 'intermediate';
|
jbe@615
|
450 END IF;
|
jbe@615
|
451 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
|
jbe@615
|
452 "output_row"."loop" := 'first';
|
jbe@615
|
453 "loop_v" := TRUE;
|
jbe@615
|
454 END IF;
|
jbe@615
|
455 IF "scope_v" = 'unit' THEN
|
jbe@615
|
456 "output_row"."participation" := NULL;
|
jbe@615
|
457 END IF;
|
jbe@615
|
458 RETURN NEXT "output_row";
|
jbe@615
|
459 "i" := "i" + 1;
|
jbe@615
|
460 END LOOP;
|
jbe@615
|
461 RETURN;
|
jbe@615
|
462 END;
|
jbe@615
|
463 $$;
|
jbe@615
|
464
|
jbe@615
|
465 CREATE OR REPLACE FUNCTION "calculate_member_counts"()
|
jbe@615
|
466 RETURNS VOID
|
jbe@615
|
467 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@615
|
468 BEGIN
|
jbe@615
|
469 PERFORM "require_transaction_isolation"();
|
jbe@615
|
470 DELETE FROM "member_count";
|
jbe@615
|
471 INSERT INTO "member_count" ("total_count")
|
jbe@615
|
472 SELECT "total_count" FROM "member_count_view";
|
jbe@615
|
473 UPDATE "unit" SET
|
jbe@615
|
474 "member_count" = "view"."member_count",
|
jbe@615
|
475 "member_weight" = "view"."member_weight"
|
jbe@615
|
476 FROM "unit_member_count" AS "view"
|
jbe@615
|
477 WHERE "view"."unit_id" = "unit"."id";
|
jbe@615
|
478 RETURN;
|
jbe@615
|
479 END;
|
jbe@615
|
480 $$;
|
jbe@615
|
481 COMMENT ON FUNCTION "calculate_member_counts"() IS 'Updates "member_count" table and "member_count" and "member_weight" columns of table "area" by materializing data from views "member_count_view" and "unit_member_count"';
|
jbe@615
|
482
|
jbe@615
|
483 CREATE OR REPLACE FUNCTION "weight_of_added_delegations_for_snapshot"
|
jbe@615
|
484 ( "snapshot_id_p" "snapshot"."id"%TYPE,
|
jbe@615
|
485 "issue_id_p" "issue"."id"%TYPE,
|
jbe@615
|
486 "member_id_p" "member"."id"%TYPE,
|
jbe@615
|
487 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
|
jbe@615
|
488 RETURNS "direct_interest_snapshot"."weight"%TYPE
|
jbe@615
|
489 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@615
|
490 DECLARE
|
jbe@615
|
491 "issue_delegation_row" "issue_delegation"%ROWTYPE;
|
jbe@615
|
492 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
|
jbe@615
|
493 "weight_v" INT4;
|
jbe@615
|
494 "sub_weight_v" INT4;
|
jbe@615
|
495 BEGIN
|
jbe@615
|
496 PERFORM "require_transaction_isolation"();
|
jbe@615
|
497 "weight_v" := 0;
|
jbe@615
|
498 FOR "issue_delegation_row" IN
|
jbe@615
|
499 SELECT * FROM "issue_delegation"
|
jbe@615
|
500 WHERE "trustee_id" = "member_id_p"
|
jbe@615
|
501 AND "issue_id" = "issue_id_p"
|
jbe@615
|
502 LOOP
|
jbe@615
|
503 IF NOT EXISTS (
|
jbe@615
|
504 SELECT NULL FROM "direct_interest_snapshot"
|
jbe@615
|
505 WHERE "snapshot_id" = "snapshot_id_p"
|
jbe@615
|
506 AND "issue_id" = "issue_id_p"
|
jbe@615
|
507 AND "member_id" = "issue_delegation_row"."truster_id"
|
jbe@615
|
508 ) AND NOT EXISTS (
|
jbe@615
|
509 SELECT NULL FROM "delegating_interest_snapshot"
|
jbe@615
|
510 WHERE "snapshot_id" = "snapshot_id_p"
|
jbe@615
|
511 AND "issue_id" = "issue_id_p"
|
jbe@615
|
512 AND "member_id" = "issue_delegation_row"."truster_id"
|
jbe@615
|
513 ) THEN
|
jbe@615
|
514 "delegate_member_ids_v" :=
|
jbe@615
|
515 "member_id_p" || "delegate_member_ids_p";
|
jbe@615
|
516 INSERT INTO "delegating_interest_snapshot" (
|
jbe@615
|
517 "snapshot_id",
|
jbe@615
|
518 "issue_id",
|
jbe@615
|
519 "member_id",
|
jbe@615
|
520 "ownweight",
|
jbe@615
|
521 "scope",
|
jbe@615
|
522 "delegate_member_ids"
|
jbe@615
|
523 ) VALUES (
|
jbe@615
|
524 "snapshot_id_p",
|
jbe@615
|
525 "issue_id_p",
|
jbe@615
|
526 "issue_delegation_row"."truster_id",
|
jbe@615
|
527 "issue_delegation_row"."weight",
|
jbe@615
|
528 "issue_delegation_row"."scope",
|
jbe@615
|
529 "delegate_member_ids_v"
|
jbe@615
|
530 );
|
jbe@615
|
531 "sub_weight_v" := "issue_delegation_row"."weight" +
|
jbe@615
|
532 "weight_of_added_delegations_for_snapshot"(
|
jbe@615
|
533 "snapshot_id_p",
|
jbe@615
|
534 "issue_id_p",
|
jbe@615
|
535 "issue_delegation_row"."truster_id",
|
jbe@615
|
536 "delegate_member_ids_v"
|
jbe@615
|
537 );
|
jbe@615
|
538 UPDATE "delegating_interest_snapshot"
|
jbe@615
|
539 SET "weight" = "sub_weight_v"
|
jbe@615
|
540 WHERE "snapshot_id" = "snapshot_id_p"
|
jbe@615
|
541 AND "issue_id" = "issue_id_p"
|
jbe@615
|
542 AND "member_id" = "issue_delegation_row"."truster_id";
|
jbe@615
|
543 "weight_v" := "weight_v" + "sub_weight_v";
|
jbe@615
|
544 END IF;
|
jbe@615
|
545 END LOOP;
|
jbe@615
|
546 RETURN "weight_v";
|
jbe@615
|
547 END;
|
jbe@615
|
548 $$;
|
jbe@615
|
549
|
jbe@615
|
550 CREATE OR REPLACE FUNCTION "take_snapshot"
|
jbe@615
|
551 ( "issue_id_p" "issue"."id"%TYPE,
|
jbe@615
|
552 "area_id_p" "area"."id"%TYPE = NULL )
|
jbe@615
|
553 RETURNS "snapshot"."id"%TYPE
|
jbe@615
|
554 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@615
|
555 DECLARE
|
jbe@615
|
556 "area_id_v" "area"."id"%TYPE;
|
jbe@615
|
557 "unit_id_v" "unit"."id"%TYPE;
|
jbe@615
|
558 "snapshot_id_v" "snapshot"."id"%TYPE;
|
jbe@615
|
559 "issue_id_v" "issue"."id"%TYPE;
|
jbe@615
|
560 "member_id_v" "member"."id"%TYPE;
|
jbe@615
|
561 BEGIN
|
jbe@615
|
562 IF "issue_id_p" NOTNULL AND "area_id_p" NOTNULL THEN
|
jbe@615
|
563 RAISE EXCEPTION 'One of "issue_id_p" and "area_id_p" must be NULL';
|
jbe@615
|
564 END IF;
|
jbe@615
|
565 PERFORM "require_transaction_isolation"();
|
jbe@615
|
566 IF "issue_id_p" ISNULL THEN
|
jbe@615
|
567 "area_id_v" := "area_id_p";
|
jbe@615
|
568 ELSE
|
jbe@615
|
569 SELECT "area_id" INTO "area_id_v"
|
jbe@615
|
570 FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@615
|
571 END IF;
|
jbe@615
|
572 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
|
jbe@615
|
573 INSERT INTO "snapshot" ("area_id", "issue_id")
|
jbe@615
|
574 VALUES ("area_id_v", "issue_id_p")
|
jbe@615
|
575 RETURNING "id" INTO "snapshot_id_v";
|
jbe@615
|
576 INSERT INTO "snapshot_population" ("snapshot_id", "member_id", "weight")
|
jbe@615
|
577 SELECT
|
jbe@615
|
578 "snapshot_id_v",
|
jbe@615
|
579 "member"."id",
|
jbe@615
|
580 COALESCE("issue_privilege"."weight", "privilege"."weight")
|
jbe@615
|
581 FROM "member"
|
jbe@615
|
582 LEFT JOIN "privilege"
|
jbe@615
|
583 ON "privilege"."unit_id" = "unit_id_v"
|
jbe@615
|
584 AND "privilege"."member_id" = "member"."id"
|
jbe@615
|
585 LEFT JOIN "issue_privilege"
|
jbe@615
|
586 ON "issue_privilege"."issue_id" = "issue_id_p"
|
jbe@615
|
587 AND "issue_privilege"."member_id" = "member"."id"
|
jbe@615
|
588 WHERE "member"."active" AND COALESCE(
|
jbe@615
|
589 "issue_privilege"."voting_right", "privilege"."voting_right");
|
jbe@615
|
590 UPDATE "snapshot" SET
|
jbe@615
|
591 "population" = (
|
jbe@615
|
592 SELECT sum("weight") FROM "snapshot_population"
|
jbe@615
|
593 WHERE "snapshot_id" = "snapshot_id_v"
|
jbe@615
|
594 ) WHERE "id" = "snapshot_id_v";
|
jbe@615
|
595 FOR "issue_id_v" IN
|
jbe@615
|
596 SELECT "id" FROM "issue"
|
jbe@615
|
597 WHERE CASE WHEN "issue_id_p" ISNULL THEN
|
jbe@615
|
598 "area_id" = "area_id_p" AND
|
jbe@615
|
599 "state" = 'admission'
|
jbe@615
|
600 ELSE
|
jbe@615
|
601 "id" = "issue_id_p"
|
jbe@615
|
602 END
|
jbe@615
|
603 LOOP
|
jbe@615
|
604 INSERT INTO "snapshot_issue" ("snapshot_id", "issue_id")
|
jbe@615
|
605 VALUES ("snapshot_id_v", "issue_id_v");
|
jbe@615
|
606 INSERT INTO "direct_interest_snapshot"
|
jbe@615
|
607 ("snapshot_id", "issue_id", "member_id", "ownweight")
|
jbe@615
|
608 SELECT
|
jbe@615
|
609 "snapshot_id_v" AS "snapshot_id",
|
jbe@615
|
610 "issue_id_v" AS "issue_id",
|
jbe@615
|
611 "member"."id" AS "member_id",
|
jbe@615
|
612 COALESCE(
|
jbe@615
|
613 "issue_privilege"."weight", "privilege"."weight"
|
jbe@615
|
614 ) AS "ownweight"
|
jbe@615
|
615 FROM "issue"
|
jbe@615
|
616 JOIN "area" ON "issue"."area_id" = "area"."id"
|
jbe@615
|
617 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
|
jbe@615
|
618 JOIN "member" ON "interest"."member_id" = "member"."id"
|
jbe@615
|
619 LEFT JOIN "privilege"
|
jbe@615
|
620 ON "privilege"."unit_id" = "area"."unit_id"
|
jbe@615
|
621 AND "privilege"."member_id" = "member"."id"
|
jbe@615
|
622 LEFT JOIN "issue_privilege"
|
jbe@615
|
623 ON "issue_privilege"."issue_id" = "issue_id_v"
|
jbe@615
|
624 AND "issue_privilege"."member_id" = "member"."id"
|
jbe@615
|
625 WHERE "issue"."id" = "issue_id_v"
|
jbe@615
|
626 AND "member"."active" AND COALESCE(
|
jbe@615
|
627 "issue_privilege"."voting_right", "privilege"."voting_right");
|
jbe@615
|
628 FOR "member_id_v" IN
|
jbe@615
|
629 SELECT "member_id" FROM "direct_interest_snapshot"
|
jbe@615
|
630 WHERE "snapshot_id" = "snapshot_id_v"
|
jbe@615
|
631 AND "issue_id" = "issue_id_v"
|
jbe@615
|
632 LOOP
|
jbe@615
|
633 UPDATE "direct_interest_snapshot" SET
|
jbe@615
|
634 "weight" = "ownweight" +
|
jbe@615
|
635 "weight_of_added_delegations_for_snapshot"(
|
jbe@615
|
636 "snapshot_id_v",
|
jbe@615
|
637 "issue_id_v",
|
jbe@615
|
638 "member_id_v",
|
jbe@615
|
639 '{}'
|
jbe@615
|
640 )
|
jbe@615
|
641 WHERE "snapshot_id" = "snapshot_id_v"
|
jbe@615
|
642 AND "issue_id" = "issue_id_v"
|
jbe@615
|
643 AND "member_id" = "member_id_v";
|
jbe@615
|
644 END LOOP;
|
jbe@615
|
645 INSERT INTO "direct_supporter_snapshot"
|
jbe@615
|
646 ( "snapshot_id", "issue_id", "initiative_id", "member_id",
|
jbe@615
|
647 "draft_id", "informed", "satisfied" )
|
jbe@615
|
648 SELECT
|
jbe@615
|
649 "snapshot_id_v" AS "snapshot_id",
|
jbe@615
|
650 "issue_id_v" AS "issue_id",
|
jbe@615
|
651 "initiative"."id" AS "initiative_id",
|
jbe@615
|
652 "supporter"."member_id" AS "member_id",
|
jbe@615
|
653 "supporter"."draft_id" AS "draft_id",
|
jbe@615
|
654 "supporter"."draft_id" = "current_draft"."id" AS "informed",
|
jbe@615
|
655 NOT EXISTS (
|
jbe@615
|
656 SELECT NULL FROM "critical_opinion"
|
jbe@615
|
657 WHERE "initiative_id" = "initiative"."id"
|
jbe@615
|
658 AND "member_id" = "supporter"."member_id"
|
jbe@615
|
659 ) AS "satisfied"
|
jbe@615
|
660 FROM "initiative"
|
jbe@615
|
661 JOIN "supporter"
|
jbe@615
|
662 ON "supporter"."initiative_id" = "initiative"."id"
|
jbe@615
|
663 JOIN "current_draft"
|
jbe@615
|
664 ON "initiative"."id" = "current_draft"."initiative_id"
|
jbe@615
|
665 JOIN "direct_interest_snapshot"
|
jbe@615
|
666 ON "snapshot_id_v" = "direct_interest_snapshot"."snapshot_id"
|
jbe@615
|
667 AND "supporter"."member_id" = "direct_interest_snapshot"."member_id"
|
jbe@615
|
668 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
|
jbe@615
|
669 WHERE "initiative"."issue_id" = "issue_id_v";
|
jbe@615
|
670 DELETE FROM "temporary_suggestion_counts";
|
jbe@615
|
671 INSERT INTO "temporary_suggestion_counts"
|
jbe@615
|
672 ( "id",
|
jbe@615
|
673 "minus2_unfulfilled_count", "minus2_fulfilled_count",
|
jbe@615
|
674 "minus1_unfulfilled_count", "minus1_fulfilled_count",
|
jbe@615
|
675 "plus1_unfulfilled_count", "plus1_fulfilled_count",
|
jbe@615
|
676 "plus2_unfulfilled_count", "plus2_fulfilled_count" )
|
jbe@615
|
677 SELECT
|
jbe@615
|
678 "suggestion"."id",
|
jbe@615
|
679 ( SELECT coalesce(sum("di"."weight"), 0)
|
jbe@615
|
680 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
|
jbe@615
|
681 ON "di"."snapshot_id" = "snapshot_id_v"
|
jbe@615
|
682 AND "di"."issue_id" = "issue_id_v"
|
jbe@615
|
683 AND "di"."member_id" = "opinion"."member_id"
|
jbe@615
|
684 WHERE "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@615
|
685 AND "opinion"."degree" = -2
|
jbe@615
|
686 AND "opinion"."fulfilled" = FALSE
|
jbe@615
|
687 ) AS "minus2_unfulfilled_count",
|
jbe@615
|
688 ( SELECT coalesce(sum("di"."weight"), 0)
|
jbe@615
|
689 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
|
jbe@615
|
690 ON "di"."snapshot_id" = "snapshot_id_v"
|
jbe@615
|
691 AND "di"."issue_id" = "issue_id_v"
|
jbe@615
|
692 AND "di"."member_id" = "opinion"."member_id"
|
jbe@615
|
693 WHERE "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@615
|
694 AND "opinion"."degree" = -2
|
jbe@615
|
695 AND "opinion"."fulfilled" = TRUE
|
jbe@615
|
696 ) AS "minus2_fulfilled_count",
|
jbe@615
|
697 ( SELECT coalesce(sum("di"."weight"), 0)
|
jbe@615
|
698 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
|
jbe@615
|
699 ON "di"."snapshot_id" = "snapshot_id_v"
|
jbe@615
|
700 AND "di"."issue_id" = "issue_id_v"
|
jbe@615
|
701 AND "di"."member_id" = "opinion"."member_id"
|
jbe@615
|
702 WHERE "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@615
|
703 AND "opinion"."degree" = -1
|
jbe@615
|
704 AND "opinion"."fulfilled" = FALSE
|
jbe@615
|
705 ) AS "minus1_unfulfilled_count",
|
jbe@615
|
706 ( SELECT coalesce(sum("di"."weight"), 0)
|
jbe@615
|
707 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
|
jbe@615
|
708 ON "di"."snapshot_id" = "snapshot_id_v"
|
jbe@615
|
709 AND "di"."issue_id" = "issue_id_v"
|
jbe@615
|
710 AND "di"."member_id" = "opinion"."member_id"
|
jbe@615
|
711 WHERE "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@615
|
712 AND "opinion"."degree" = -1
|
jbe@615
|
713 AND "opinion"."fulfilled" = TRUE
|
jbe@615
|
714 ) AS "minus1_fulfilled_count",
|
jbe@615
|
715 ( SELECT coalesce(sum("di"."weight"), 0)
|
jbe@615
|
716 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
|
jbe@615
|
717 ON "di"."snapshot_id" = "snapshot_id_v"
|
jbe@615
|
718 AND "di"."issue_id" = "issue_id_v"
|
jbe@615
|
719 AND "di"."member_id" = "opinion"."member_id"
|
jbe@615
|
720 WHERE "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@615
|
721 AND "opinion"."degree" = 1
|
jbe@615
|
722 AND "opinion"."fulfilled" = FALSE
|
jbe@615
|
723 ) AS "plus1_unfulfilled_count",
|
jbe@615
|
724 ( SELECT coalesce(sum("di"."weight"), 0)
|
jbe@615
|
725 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
|
jbe@615
|
726 ON "di"."snapshot_id" = "snapshot_id_v"
|
jbe@615
|
727 AND "di"."issue_id" = "issue_id_v"
|
jbe@615
|
728 AND "di"."member_id" = "opinion"."member_id"
|
jbe@615
|
729 WHERE "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@615
|
730 AND "opinion"."degree" = 1
|
jbe@615
|
731 AND "opinion"."fulfilled" = TRUE
|
jbe@615
|
732 ) AS "plus1_fulfilled_count",
|
jbe@615
|
733 ( SELECT coalesce(sum("di"."weight"), 0)
|
jbe@615
|
734 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
|
jbe@615
|
735 ON "di"."snapshot_id" = "snapshot_id_v"
|
jbe@615
|
736 AND "di"."issue_id" = "issue_id_v"
|
jbe@615
|
737 AND "di"."member_id" = "opinion"."member_id"
|
jbe@615
|
738 WHERE "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@615
|
739 AND "opinion"."degree" = 2
|
jbe@615
|
740 AND "opinion"."fulfilled" = FALSE
|
jbe@615
|
741 ) AS "plus2_unfulfilled_count",
|
jbe@615
|
742 ( SELECT coalesce(sum("di"."weight"), 0)
|
jbe@615
|
743 FROM "opinion" JOIN "direct_interest_snapshot" AS "di"
|
jbe@615
|
744 ON "di"."snapshot_id" = "snapshot_id_v"
|
jbe@615
|
745 AND "di"."issue_id" = "issue_id_v"
|
jbe@615
|
746 AND "di"."member_id" = "opinion"."member_id"
|
jbe@615
|
747 WHERE "opinion"."suggestion_id" = "suggestion"."id"
|
jbe@615
|
748 AND "opinion"."degree" = 2
|
jbe@615
|
749 AND "opinion"."fulfilled" = TRUE
|
jbe@615
|
750 ) AS "plus2_fulfilled_count"
|
jbe@615
|
751 FROM "suggestion" JOIN "initiative"
|
jbe@615
|
752 ON "suggestion"."initiative_id" = "initiative"."id"
|
jbe@615
|
753 WHERE "initiative"."issue_id" = "issue_id_v";
|
jbe@615
|
754 END LOOP;
|
jbe@615
|
755 RETURN "snapshot_id_v";
|
jbe@615
|
756 END;
|
jbe@615
|
757 $$;
|
jbe@615
|
758
|
jbe@615
|
759 CREATE OR REPLACE FUNCTION "weight_of_added_vote_delegations"
|
jbe@615
|
760 ( "issue_id_p" "issue"."id"%TYPE,
|
jbe@615
|
761 "member_id_p" "member"."id"%TYPE,
|
jbe@615
|
762 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
|
jbe@615
|
763 RETURNS "direct_voter"."weight"%TYPE
|
jbe@615
|
764 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@615
|
765 DECLARE
|
jbe@615
|
766 "issue_delegation_row" "issue_delegation"%ROWTYPE;
|
jbe@615
|
767 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
|
jbe@615
|
768 "weight_v" INT4;
|
jbe@615
|
769 "sub_weight_v" INT4;
|
jbe@615
|
770 BEGIN
|
jbe@615
|
771 PERFORM "require_transaction_isolation"();
|
jbe@615
|
772 "weight_v" := 0;
|
jbe@615
|
773 FOR "issue_delegation_row" IN
|
jbe@615
|
774 SELECT * FROM "issue_delegation"
|
jbe@615
|
775 WHERE "trustee_id" = "member_id_p"
|
jbe@615
|
776 AND "issue_id" = "issue_id_p"
|
jbe@615
|
777 LOOP
|
jbe@615
|
778 IF NOT EXISTS (
|
jbe@615
|
779 SELECT NULL FROM "direct_voter"
|
jbe@615
|
780 WHERE "member_id" = "issue_delegation_row"."truster_id"
|
jbe@615
|
781 AND "issue_id" = "issue_id_p"
|
jbe@615
|
782 ) AND NOT EXISTS (
|
jbe@615
|
783 SELECT NULL FROM "delegating_voter"
|
jbe@615
|
784 WHERE "member_id" = "issue_delegation_row"."truster_id"
|
jbe@615
|
785 AND "issue_id" = "issue_id_p"
|
jbe@615
|
786 ) THEN
|
jbe@615
|
787 "delegate_member_ids_v" :=
|
jbe@615
|
788 "member_id_p" || "delegate_member_ids_p";
|
jbe@615
|
789 INSERT INTO "delegating_voter" (
|
jbe@615
|
790 "issue_id",
|
jbe@615
|
791 "member_id",
|
jbe@615
|
792 "ownweight",
|
jbe@615
|
793 "scope",
|
jbe@615
|
794 "delegate_member_ids"
|
jbe@615
|
795 ) VALUES (
|
jbe@615
|
796 "issue_id_p",
|
jbe@615
|
797 "issue_delegation_row"."truster_id",
|
jbe@615
|
798 "issue_delegation_row"."weight",
|
jbe@615
|
799 "issue_delegation_row"."scope",
|
jbe@615
|
800 "delegate_member_ids_v"
|
jbe@615
|
801 );
|
jbe@615
|
802 "sub_weight_v" := "issue_delegation_row"."weight" +
|
jbe@615
|
803 "weight_of_added_vote_delegations"(
|
jbe@615
|
804 "issue_id_p",
|
jbe@615
|
805 "issue_delegation_row"."truster_id",
|
jbe@615
|
806 "delegate_member_ids_v"
|
jbe@615
|
807 );
|
jbe@615
|
808 UPDATE "delegating_voter"
|
jbe@615
|
809 SET "weight" = "sub_weight_v"
|
jbe@615
|
810 WHERE "issue_id" = "issue_id_p"
|
jbe@615
|
811 AND "member_id" = "issue_delegation_row"."truster_id";
|
jbe@615
|
812 "weight_v" := "weight_v" + "sub_weight_v";
|
jbe@615
|
813 END IF;
|
jbe@615
|
814 END LOOP;
|
jbe@615
|
815 RETURN "weight_v";
|
jbe@615
|
816 END;
|
jbe@615
|
817 $$;
|
jbe@615
|
818
|
jbe@615
|
819 CREATE OR REPLACE FUNCTION "add_vote_delegations"
|
jbe@615
|
820 ( "issue_id_p" "issue"."id"%TYPE )
|
jbe@615
|
821 RETURNS VOID
|
jbe@615
|
822 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@615
|
823 DECLARE
|
jbe@615
|
824 "member_id_v" "member"."id"%TYPE;
|
jbe@615
|
825 BEGIN
|
jbe@615
|
826 PERFORM "require_transaction_isolation"();
|
jbe@615
|
827 FOR "member_id_v" IN
|
jbe@615
|
828 SELECT "member_id" FROM "direct_voter"
|
jbe@615
|
829 WHERE "issue_id" = "issue_id_p"
|
jbe@615
|
830 LOOP
|
jbe@615
|
831 UPDATE "direct_voter" SET
|
jbe@615
|
832 "weight" = "ownweight" + "weight_of_added_vote_delegations"(
|
jbe@615
|
833 "issue_id_p",
|
jbe@615
|
834 "member_id_v",
|
jbe@615
|
835 '{}'
|
jbe@615
|
836 )
|
jbe@615
|
837 WHERE "member_id" = "member_id_v"
|
jbe@615
|
838 AND "issue_id" = "issue_id_p";
|
jbe@615
|
839 END LOOP;
|
jbe@615
|
840 RETURN;
|
jbe@615
|
841 END;
|
jbe@615
|
842 $$;
|
jbe@615
|
843
|
jbe@615
|
844 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
|
jbe@615
|
845 RETURNS VOID
|
jbe@615
|
846 LANGUAGE 'plpgsql' VOLATILE AS $$
|
jbe@615
|
847 DECLARE
|
jbe@615
|
848 "area_id_v" "area"."id"%TYPE;
|
jbe@615
|
849 "unit_id_v" "unit"."id"%TYPE;
|
jbe@615
|
850 "member_id_v" "member"."id"%TYPE;
|
jbe@615
|
851 BEGIN
|
jbe@615
|
852 PERFORM "require_transaction_isolation"();
|
jbe@615
|
853 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
|
jbe@615
|
854 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
|
jbe@615
|
855 -- override protection triggers:
|
jbe@615
|
856 INSERT INTO "temporary_transaction_data" ("key", "value")
|
jbe@615
|
857 VALUES ('override_protection_triggers', TRUE::TEXT);
|
jbe@615
|
858 -- delete timestamp of voting comment:
|
jbe@615
|
859 UPDATE "direct_voter" SET "comment_changed" = NULL
|
jbe@615
|
860 WHERE "issue_id" = "issue_id_p";
|
jbe@615
|
861 -- delete delegating votes (in cases of manual reset of issue state):
|
jbe@615
|
862 DELETE FROM "delegating_voter"
|
jbe@615
|
863 WHERE "issue_id" = "issue_id_p";
|
jbe@615
|
864 -- delete votes from non-privileged voters:
|
jbe@615
|
865 DELETE FROM "direct_voter"
|
jbe@615
|
866 USING (
|
jbe@615
|
867 SELECT "direct_voter"."member_id"
|
jbe@615
|
868 FROM "direct_voter"
|
jbe@615
|
869 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
|
jbe@615
|
870 LEFT JOIN "privilege"
|
jbe@615
|
871 ON "privilege"."unit_id" = "unit_id_v"
|
jbe@615
|
872 AND "privilege"."member_id" = "direct_voter"."member_id"
|
jbe@615
|
873 LEFT JOIN "issue_privilege"
|
jbe@615
|
874 ON "issue_privilege"."issue_id" = "issue_id_p"
|
jbe@615
|
875 AND "issue_privilege"."member_id" = "direct_voter"."member_id"
|
jbe@615
|
876 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
|
jbe@615
|
877 "member"."active" = FALSE OR
|
jbe@615
|
878 COALESCE(
|
jbe@615
|
879 "issue_privilege"."voting_right",
|
jbe@615
|
880 "privilege"."voting_right",
|
jbe@615
|
881 FALSE
|
jbe@615
|
882 ) = FALSE
|
jbe@615
|
883 )
|
jbe@615
|
884 ) AS "subquery"
|
jbe@615
|
885 WHERE "direct_voter"."issue_id" = "issue_id_p"
|
jbe@615
|
886 AND "direct_voter"."member_id" = "subquery"."member_id";
|
jbe@615
|
887 -- consider voting weight and delegations:
|
jbe@615
|
888 UPDATE "direct_voter" SET "ownweight" = "privilege"."weight"
|
jbe@615
|
889 FROM "privilege"
|
jbe@615
|
890 WHERE "issue_id" = "issue_id_p"
|
jbe@615
|
891 AND "privilege"."unit_id" = "unit_id_v"
|
jbe@615
|
892 AND "privilege"."member_id" = "direct_voter"."member_id";
|
jbe@615
|
893 UPDATE "direct_voter" SET "ownweight" = "issue_privilege"."weight"
|
jbe@615
|
894 FROM "issue_privilege"
|
jbe@615
|
895 WHERE "direct_voter"."issue_id" = "issue_id_p"
|
jbe@615
|
896 AND "issue_privilege"."issue_id" = "issue_id_p"
|
jbe@615
|
897 AND "issue_privilege"."member_id" = "direct_voter"."member_id";
|
jbe@615
|
898 PERFORM "add_vote_delegations"("issue_id_p");
|
jbe@615
|
899 -- mark first preferences:
|
jbe@615
|
900 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
|
jbe@615
|
901 FROM (
|
jbe@615
|
902 SELECT
|
jbe@615
|
903 "vote"."initiative_id",
|
jbe@615
|
904 "vote"."member_id",
|
jbe@615
|
905 CASE WHEN "vote"."grade" > 0 THEN
|
jbe@615
|
906 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
|
jbe@615
|
907 ELSE NULL
|
jbe@615
|
908 END AS "first_preference"
|
jbe@615
|
909 FROM "vote"
|
jbe@615
|
910 JOIN "initiative" -- NOTE: due to missing index on issue_id
|
jbe@615
|
911 ON "vote"."issue_id" = "initiative"."issue_id"
|
jbe@615
|
912 JOIN "vote" AS "agg"
|
jbe@615
|
913 ON "initiative"."id" = "agg"."initiative_id"
|
jbe@615
|
914 AND "vote"."member_id" = "agg"."member_id"
|
jbe@615
|
915 GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
|
jbe@615
|
916 ) AS "subquery"
|
jbe@615
|
917 WHERE "vote"."issue_id" = "issue_id_p"
|
jbe@615
|
918 AND "vote"."initiative_id" = "subquery"."initiative_id"
|
jbe@615
|
919 AND "vote"."member_id" = "subquery"."member_id";
|
jbe@615
|
920 -- finish overriding protection triggers (avoids garbage):
|
jbe@615
|
921 DELETE FROM "temporary_transaction_data"
|
jbe@615
|
922 WHERE "key" = 'override_protection_triggers';
|
jbe@615
|
923 -- materialize battle_view:
|
jbe@615
|
924 -- NOTE: "closed" column of issue must be set at this point
|
jbe@615
|
925 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
|
jbe@615
|
926 INSERT INTO "battle" (
|
jbe@615
|
927 "issue_id",
|
jbe@615
|
928 "winning_initiative_id", "losing_initiative_id",
|
jbe@615
|
929 "count"
|
jbe@615
|
930 ) SELECT
|
jbe@615
|
931 "issue_id",
|
jbe@615
|
932 "winning_initiative_id", "losing_initiative_id",
|
jbe@615
|
933 "count"
|
jbe@615
|
934 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
|
jbe@615
|
935 -- set voter count:
|
jbe@615
|
936 UPDATE "issue" SET
|
jbe@615
|
937 "voter_count" = (
|
jbe@615
|
938 SELECT coalesce(sum("weight"), 0)
|
jbe@615
|
939 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
|
jbe@615
|
940 )
|
jbe@615
|
941 WHERE "id" = "issue_id_p";
|
jbe@615
|
942 -- copy "positive_votes" and "negative_votes" from "battle" table:
|
jbe@615
|
943 -- NOTE: "first_preference_votes" is set to a default of 0 at this step
|
jbe@615
|
944 UPDATE "initiative" SET
|
jbe@615
|
945 "first_preference_votes" = 0,
|
jbe@615
|
946 "positive_votes" = "battle_win"."count",
|
jbe@615
|
947 "negative_votes" = "battle_lose"."count"
|
jbe@615
|
948 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
|
jbe@615
|
949 WHERE
|
jbe@615
|
950 "battle_win"."issue_id" = "issue_id_p" AND
|
jbe@615
|
951 "battle_win"."winning_initiative_id" = "initiative"."id" AND
|
jbe@615
|
952 "battle_win"."losing_initiative_id" ISNULL AND
|
jbe@615
|
953 "battle_lose"."issue_id" = "issue_id_p" AND
|
jbe@615
|
954 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
|
jbe@615
|
955 "battle_lose"."winning_initiative_id" ISNULL;
|
jbe@615
|
956 -- calculate "first_preference_votes":
|
jbe@615
|
957 -- NOTE: will only set values not equal to zero
|
jbe@615
|
958 UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum"
|
jbe@615
|
959 FROM (
|
jbe@615
|
960 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
|
jbe@615
|
961 FROM "vote" JOIN "direct_voter"
|
jbe@615
|
962 ON "vote"."issue_id" = "direct_voter"."issue_id"
|
jbe@615
|
963 AND "vote"."member_id" = "direct_voter"."member_id"
|
jbe@615
|
964 WHERE "vote"."first_preference"
|
jbe@615
|
965 GROUP BY "vote"."initiative_id"
|
jbe@615
|
966 ) AS "subquery"
|
jbe@615
|
967 WHERE "initiative"."issue_id" = "issue_id_p"
|
jbe@615
|
968 AND "initiative"."admitted"
|
jbe@615
|
969 AND "initiative"."id" = "subquery"."initiative_id";
|
jbe@615
|
970 END;
|
jbe@615
|
971 $$;
|
jbe@615
|
972
|
jbe@619
|
973 DROP INDEX "posting_global_idx";
|
jbe@619
|
974 DROP INDEX "posting_unit_idx";
|
jbe@619
|
975 DROP INDEX "posting_area_idx";
|
jbe@619
|
976 DROP INDEX "posting_policy_idx";
|
jbe@619
|
977 DROP INDEX "posting_issue_idx";
|
jbe@619
|
978 DROP INDEX "posting_initiative_idx";
|
jbe@619
|
979 DROP INDEX "posting_suggestion_idx";
|
jbe@619
|
980
|
jbe@619
|
981 DROP INDEX "posting_lexeme_idx";
|
jbe@619
|
982
|
jbe@619
|
983 DROP INDEX "event_tl_global_idx";
|
jbe@619
|
984 DROP INDEX "event_tl_unit_idx";
|
jbe@619
|
985 DROP INDEX "event_tl_area_idx";
|
jbe@619
|
986 DROP INDEX "event_tl_policy_idx";
|
jbe@619
|
987 DROP INDEX "event_tl_issue_idx";
|
jbe@619
|
988 DROP INDEX "event_tl_initiative_idx";
|
jbe@619
|
989 DROP INDEX "event_tl_suggestion_idx";
|
jbe@619
|
990
|
jbe@619
|
991 DROP EXTENSION IF EXISTS conflux;
|
jbe@619
|
992 DROP EXTENSION IF EXISTS btree_gist;
|
jbe@619
|
993
|
jbe@615
|
994 COMMIT;
|