liquid_feedback_core

view core.sql @ 4:6133c0a62378

find changesets by author, revision, files, or words in the commit message
Version beta5

Precalculation of certain values to increase performance:
- Count of active members per area
- Count of active members altogether
- Count of people involved in the voting process for an issue

Minor bugfix in function close_voting(...) related to initiatives having no positive or negative votes
author jbe
date Sun Nov 15 12:00:00 2009 +0100 (2009-11-15)
parents 3da35844c874
children 1cbdd3975a61
line source
1
2 CREATE LANGUAGE plpgsql; -- Triggers are implemented in PL/pgSQL
3
4 -- NOTE: In PostgreSQL every UNIQUE constraint implies creation of an index
5
6 BEGIN;
7
8
9
10 -------------------------
11 -- Tables and indicies --
12 -------------------------
13
14
15 CREATE TABLE "member" (
16 "id" SERIAL4 PRIMARY KEY,
17 "login" TEXT NOT NULL UNIQUE,
18 "password" TEXT,
19 "active" BOOLEAN NOT NULL DEFAULT TRUE,
20 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
21 "name" TEXT,
22 "ident_number" TEXT UNIQUE );
23 CREATE INDEX "member_active_idx" ON "member" ("active");
24
25 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
26
27 COMMENT ON COLUMN "member"."login" IS 'Login name';
28 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
29 COMMENT ON COLUMN "member"."active" IS 'Inactive members can not login and their supports/votes are not counted by the system.';
30 COMMENT ON COLUMN "member"."ident_number" IS 'Additional information about the members idenficication number within the organization';
31
32
33 CREATE TABLE "member_count" (
34 "count" INT4 NOT NULL );
35
36 COMMENT ON TABLE "member_count" IS 'Holds a single value (single row, single column) of the total count of active(!) members, as calculated from "member_count_view"';
37
38 COMMENT ON COLUMN "member_count"."count" IS 'Total count of active(!) members';
39
40
41 CREATE TABLE "contact" (
42 PRIMARY KEY ("member_id", "other_member_id"),
43 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
44 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
45 "public" BOOLEAN NOT NULL DEFAULT FALSE );
46
47 COMMENT ON TABLE "contact" IS 'Contact lists';
48
49 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
50 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
51 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
52
53
54 CREATE TABLE "session" (
55 "ident" TEXT PRIMARY KEY,
56 "additional_secret" TEXT,
57 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
58 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
59 "lang" TEXT );
60 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
61
62 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';
63
64 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
65 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
66 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
67 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
68
69
70 CREATE TABLE "policy" (
71 "id" SERIAL4 PRIMARY KEY,
72 "active" BOOLEAN NOT NULL DEFAULT TRUE,
73 "name" TEXT NOT NULL UNIQUE,
74 "description" TEXT NOT NULL DEFAULT '',
75 "admission_time" INTERVAL NOT NULL,
76 "discussion_time" INTERVAL NOT NULL,
77 "verification_time" INTERVAL NOT NULL,
78 "voting_time" INTERVAL NOT NULL,
79 "issue_quorum_num" INT4 NOT NULL,
80 "issue_quorum_den" INT4 NOT NULL,
81 "initiative_quorum_num" INT4 NOT NULL,
82 "initiative_quorum_den" INT4 NOT NULL );
83 CREATE INDEX "policy_active_idx" ON "policy" ("active");
84
85 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
86
87 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
88 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"';
89 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"';
90 COMMENT ON COLUMN "policy"."verification_time" IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
91 COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "fully_frozen" but not "closed"';
92 COMMENT ON COLUMN "policy"."issue_quorum_num" IS 'Numerator of quorum to be reached by one initiative of an issue to be "accepted"';
93 COMMENT ON COLUMN "policy"."issue_quorum_den" IS 'Denominator of quorum to be reached by one initiative of an issue to be "accepted"';
94 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of quorum to be reached by an initiative to be "admitted" for voting';
95 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of quorum to be reached by an initiative to be "admitted" for voting';
96
97
98 CREATE TABLE "area" (
99 "id" SERIAL4 PRIMARY KEY,
100 "active" BOOLEAN NOT NULL DEFAULT TRUE,
101 "name" TEXT NOT NULL,
102 "description" TEXT NOT NULL DEFAULT '',
103 "member_count" INT4 );
104 CREATE INDEX "area_active_idx" ON "area" ("active");
105
106 COMMENT ON TABLE "area" IS 'Subject areas';
107
108 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
109 COMMENT ON COLUMN "area"."member_count" IS 'Number of active members of that area, as calculated from view "area_member_count"';
110
111
112 CREATE TABLE "issue" (
113 "id" SERIAL4 PRIMARY KEY,
114 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
115 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
116 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
117 "accepted" TIMESTAMPTZ,
118 "half_frozen" TIMESTAMPTZ,
119 "fully_frozen" TIMESTAMPTZ,
120 "closed" TIMESTAMPTZ,
121 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
122 "snapshot" TIMESTAMPTZ,
123 "population" INT4,
124 "vote_now" INT4,
125 "vote_later" INT4,
126 "voter_count" INT4,
127 CONSTRAINT "valid_state" CHECK (
128 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
129 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
130 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
131 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
132 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
133 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
134 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ),
135 CONSTRAINT "state_change_order" CHECK (
136 "created" <= "accepted" AND
137 "accepted" <= "half_frozen" AND
138 "half_frozen" <= "fully_frozen" AND
139 "fully_frozen" <= "closed" ),
140 CONSTRAINT "last_snapshot_on_full_freeze" CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
141 CONSTRAINT "freeze_requires_snapshot" CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL) );
142 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
143 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
144 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
145
146 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
147
148 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
149 COMMENT ON COLUMN "issue"."half_frozen" IS 'Point in time, when "discussion_time" has elapsed, or members voted for voting';
150 COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed';
151 COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active';
152 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
153 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population", "vote_now", "vote_later" and *_count values were precalculated';
154 COMMENT ON COLUMN "issue"."population" IS 'Calculated from table "direct_population_snapshot"';
155 COMMENT ON COLUMN "issue"."vote_now" IS 'Calculated from table "direct_interest_snapshot"';
156 COMMENT ON COLUMN "issue"."vote_later" IS 'Calculated from table "direct_interest_snapshot"';
157 COMMENT ON COLUMN "issue"."voter_count" IS 'Total number of direct and delegating voters; This value is related to the final voting, while "population" is related to snapshots before the final voting';
158
159
160 CREATE TABLE "initiative" (
161 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
162 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
163 "id" SERIAL4 PRIMARY KEY,
164 "name" TEXT NOT NULL,
165 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
166 "revoked" TIMESTAMPTZ,
167 "admitted" BOOLEAN,
168 "supporter_count" INT4,
169 "informed_supporter_count" INT4,
170 "satisfied_supporter_count" INT4,
171 "satisfied_informed_supporter_count" INT4,
172 "positive_votes" INT4,
173 "negative_votes" INT4,
174 "rank" INT4,
175 CONSTRAINT "revoked_initiatives_cant_be_admitted"
176 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
177 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"
178 CHECK ("admitted" = TRUE OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "rank" ISNULL)) );
179
180 COMMENT ON TABLE "initiative" IS 'Group of members publishing drafts for resolutions to be passed';
181
182 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
183 COMMENT ON COLUMN "initiative"."admitted" IS 'True, if initiative reaches the "initiative_quorum" when freezing the issue';
184 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
185 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
186 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
187 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
188 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
189 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
190 COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';
191
192
193 CREATE TABLE "draft" (
194 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
195 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
196 "id" SERIAL8 PRIMARY KEY,
197 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
198 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
199 "content" TEXT NOT NULL );
200
201 COMMENT ON TABLE "draft" IS 'Drafts of initiatives to solve issues';
202
203
204 CREATE TABLE "suggestion" (
205 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
206 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
207 "id" SERIAL8 PRIMARY KEY,
208 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
209 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
210 "name" TEXT NOT NULL,
211 "description" TEXT NOT NULL DEFAULT '',
212 "minus2_unfulfilled_count" INT4,
213 "minus2_fulfilled_count" INT4,
214 "minus1_unfulfilled_count" INT4,
215 "minus1_fulfilled_count" INT4,
216 "plus1_unfulfilled_count" INT4,
217 "plus1_fulfilled_count" INT4,
218 "plus2_unfulfilled_count" INT4,
219 "plus2_fulfilled_count" INT4 );
220
221 COMMENT ON TABLE "suggestion" IS 'Suggestions to initiators, to change the current draft';
222
223 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
224 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
225 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
226 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
227 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
228 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
229 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
230 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
231
232
233 CREATE TABLE "membership" (
234 PRIMARY KEY ("area_id", "member_id"),
235 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
236 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
237 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
238 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
239
240 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
241
242 COMMENT ON COLUMN "membership"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure; If there exists an "interest" entry, the interest entry has precedence';
243
244
245 CREATE TABLE "interest" (
246 PRIMARY KEY ("issue_id", "member_id"),
247 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
248 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
249 "autoreject" BOOLEAN NOT NULL,
250 "voting_requested" BOOLEAN );
251 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
252
253 COMMENT ON TABLE "interest" IS 'Interest of members in a particular issue';
254
255 COMMENT ON COLUMN "interest"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure';
256 COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply';
257
258
259 CREATE TABLE "initiator" (
260 PRIMARY KEY ("initiative_id", "member_id"),
261 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
262 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
263 "accepted" BOOLEAN NOT NULL DEFAULT TRUE );
264 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
265
266 COMMENT ON TABLE "initiator" IS 'Members who are allowed to post new drafts';
267
268 COMMENT ON COLUMN "initiator"."accepted" IS 'If "accepted" = FALSE, then the member was invited to be a co-initiator, but has not answered yet.';
269
270
271 CREATE TABLE "supporter" (
272 "issue_id" INT4 NOT NULL,
273 PRIMARY KEY ("initiative_id", "member_id"),
274 "initiative_id" INT4,
275 "member_id" INT4,
276 "draft_id" INT8 NOT NULL,
277 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE RESTRICT ON UPDATE CASCADE,
278 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
279 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
280
281 COMMENT ON TABLE "supporter" IS 'Members who support an initiative (conditionally)';
282
283 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
284
285
286 CREATE TABLE "opinion" (
287 "initiative_id" INT4 NOT NULL,
288 PRIMARY KEY ("suggestion_id", "member_id"),
289 "suggestion_id" INT8,
290 "member_id" INT4,
291 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
292 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
293 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE,
294 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
295 CREATE INDEX "opinion_member_id_idx" ON "opinion" ("member_id");
296
297 COMMENT ON TABLE "opinion" IS 'Opinion on suggestions (criticism related to initiatives)';
298
299 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
300
301
302 CREATE TABLE "delegation" (
303 "id" SERIAL8 PRIMARY KEY,
304 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
305 "trustee_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
306 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
307 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
308 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
309 CONSTRAINT "area_id_or_issue_id_can_be_set_but_not_both" CHECK ("area_id" ISNULL OR "issue_id" ISNULL),
310 UNIQUE ("area_id", "truster_id", "trustee_id"),
311 UNIQUE ("issue_id", "truster_id", "trustee_id") );
312 CREATE UNIQUE INDEX "delegation_default_truster_id_trustee_id_unique_idx"
313 ON "delegation" ("truster_id", "trustee_id")
314 WHERE "area_id" ISNULL AND "issue_id" ISNULL;
315 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
316 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
317
318 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
319
320 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
321 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
322
323
324 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'start_of_voting');
325
326 COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''start_of_voting'' = saved state at end of verification period';
327
328
329 CREATE TABLE "direct_population_snapshot" (
330 PRIMARY KEY ("issue_id", "event", "member_id"),
331 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
332 "event" "snapshot_event",
333 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
334 "weight" INT4,
335 "interest_exists" BOOLEAN NOT NULL );
336 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
337
338 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
339
340 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
341 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
342 COMMENT ON COLUMN "direct_population_snapshot"."interest_exists" IS 'TRUE if entry is due to interest in issue, FALSE if entry is only due to membership in area';
343
344
345 CREATE TABLE "delegating_population_snapshot" (
346 PRIMARY KEY ("issue_id", "event", "member_id"),
347 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
348 "event" "snapshot_event",
349 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
350 "delegate_member_ids" INT4[] NOT NULL );
351 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
352
353 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
354
355 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
356 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
357 COMMENT ON COLUMN "delegating_population_snapshot"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_population_snapshot"';
358
359
360 CREATE TABLE "direct_interest_snapshot" (
361 PRIMARY KEY ("issue_id", "event", "member_id"),
362 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
363 "event" "snapshot_event",
364 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
365 "weight" INT4,
366 "voting_requested" BOOLEAN );
367 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
368
369 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
370
371 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
372 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
373 COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';
374
375
376 CREATE TABLE "delegating_interest_snapshot" (
377 PRIMARY KEY ("issue_id", "event", "member_id"),
378 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
379 "event" "snapshot_event",
380 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
381 "delegate_member_ids" INT4[] NOT NULL );
382 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
383
384 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
385
386 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
387 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
388 COMMENT ON COLUMN "delegating_interest_snapshot"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_interest_snapshot"';
389
390
391 CREATE TABLE "direct_supporter_snapshot" (
392 "issue_id" INT4 NOT NULL,
393 PRIMARY KEY ("initiative_id", "event", "member_id"),
394 "initiative_id" INT4,
395 "event" "snapshot_event",
396 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
397 "informed" BOOLEAN NOT NULL,
398 "satisfied" BOOLEAN NOT NULL,
399 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
400 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
401 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
402
403 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot)';
404
405 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
406 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
407 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
408
409
410 CREATE TABLE "direct_voter" (
411 PRIMARY KEY ("issue_id", "member_id"),
412 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
413 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
414 "weight" INT4,
415 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
416 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
417
418 COMMENT ON TABLE "direct_voter" IS 'Members having directly voted for/against initiatives of an issue';
419
420 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
421 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
422
423
424 CREATE TABLE "delegating_voter" (
425 PRIMARY KEY ("issue_id", "member_id"),
426 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
427 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
428 "delegate_member_ids" INT4[] NOT NULL );
429 CREATE INDEX "delegating_voter_member_id_idx" ON "direct_voter" ("member_id");
430
431 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
432
433 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
434 COMMENT ON COLUMN "delegating_voter"."delegate_member_ids" IS 'Chain of members who act as delegates; last entry referes to "member_id" column of table "direct_voter"';
435
436
437 CREATE TABLE "vote" (
438 "issue_id" INT4 NOT NULL,
439 PRIMARY KEY ("initiative_id", "member_id"),
440 "initiative_id" INT4,
441 "member_id" INT4,
442 "grade" INT4,
443 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
444 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
445 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
446
447 COMMENT ON TABLE "vote" IS 'Manual and delegated votes without abstentions';
448
449 COMMENT ON COLUMN "vote"."grade" IS 'Values smaller than zero mean reject, values greater than zero mean acceptance, zero or missing row means abstention. Preferences are expressed by different positive or negative numbers.';
450
451
452
453 ----------------------------
454 -- Additional constraints --
455 ----------------------------
456
457
458 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
459 RETURNS TRIGGER
460 LANGUAGE 'plpgsql' VOLATILE AS $$
461 BEGIN
462 IF NOT EXISTS (
463 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
464 ) THEN
465 --RAISE 'Cannot create issue without an initial initiative.' USING
466 -- ERRCODE = 'integrity_constraint_violation',
467 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
468 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
469 END IF;
470 RETURN NULL;
471 END;
472 $$;
473
474 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
475 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
476 FOR EACH ROW EXECUTE PROCEDURE
477 "issue_requires_first_initiative_trigger"();
478
479 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
480 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
481
482
483 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
484 RETURNS TRIGGER
485 LANGUAGE 'plpgsql' VOLATILE AS $$
486 DECLARE
487 "reference_lost" BOOLEAN;
488 BEGIN
489 IF TG_OP = 'DELETE' THEN
490 "reference_lost" := TRUE;
491 ELSE
492 "reference_lost" := NEW."issue_id" != OLD."issue_id";
493 END IF;
494 IF
495 "reference_lost" AND NOT EXISTS (
496 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
497 )
498 THEN
499 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
500 END IF;
501 RETURN NULL;
502 END;
503 $$;
504
505 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
506 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
507 FOR EACH ROW EXECUTE PROCEDURE
508 "last_initiative_deletes_issue_trigger"();
509
510 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
511 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
512
513
514 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
515 RETURNS TRIGGER
516 LANGUAGE 'plpgsql' VOLATILE AS $$
517 BEGIN
518 IF NOT EXISTS (
519 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
520 ) THEN
521 --RAISE 'Cannot create initiative without an initial draft.' USING
522 -- ERRCODE = 'integrity_constraint_violation',
523 -- HINT = 'Create issue, initiative and draft within the same transaction.';
524 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
525 END IF;
526 RETURN NULL;
527 END;
528 $$;
529
530 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
531 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
532 FOR EACH ROW EXECUTE PROCEDURE
533 "initiative_requires_first_draft_trigger"();
534
535 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
536 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
537
538
539 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
540 RETURNS TRIGGER
541 LANGUAGE 'plpgsql' VOLATILE AS $$
542 DECLARE
543 "reference_lost" BOOLEAN;
544 BEGIN
545 IF TG_OP = 'DELETE' THEN
546 "reference_lost" := TRUE;
547 ELSE
548 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
549 END IF;
550 IF
551 "reference_lost" AND NOT EXISTS (
552 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
553 )
554 THEN
555 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
556 END IF;
557 RETURN NULL;
558 END;
559 $$;
560
561 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
562 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
563 FOR EACH ROW EXECUTE PROCEDURE
564 "last_draft_deletes_initiative_trigger"();
565
566 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
567 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
568
569
570 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
571 RETURNS TRIGGER
572 LANGUAGE 'plpgsql' VOLATILE AS $$
573 BEGIN
574 IF NOT EXISTS (
575 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
576 ) THEN
577 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
578 END IF;
579 RETURN NULL;
580 END;
581 $$;
582
583 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
584 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
585 FOR EACH ROW EXECUTE PROCEDURE
586 "suggestion_requires_first_opinion_trigger"();
587
588 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
589 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
590
591
592 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
593 RETURNS TRIGGER
594 LANGUAGE 'plpgsql' VOLATILE AS $$
595 DECLARE
596 "reference_lost" BOOLEAN;
597 BEGIN
598 IF TG_OP = 'DELETE' THEN
599 "reference_lost" := TRUE;
600 ELSE
601 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
602 END IF;
603 IF
604 "reference_lost" AND NOT EXISTS (
605 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
606 )
607 THEN
608 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
609 END IF;
610 RETURN NULL;
611 END;
612 $$;
613
614 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
615 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
616 FOR EACH ROW EXECUTE PROCEDURE
617 "last_opinion_deletes_suggestion_trigger"();
618
619 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
620 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
621
622
623
624 --------------------------------------------------------------------
625 -- Auto-retrieval of fields only needed for referential integrity --
626 --------------------------------------------------------------------
627
628 CREATE FUNCTION "autofill_issue_id_trigger"()
629 RETURNS TRIGGER
630 LANGUAGE 'plpgsql' VOLATILE AS $$
631 BEGIN
632 IF NEW."issue_id" ISNULL THEN
633 SELECT "issue_id" INTO NEW."issue_id"
634 FROM "initiative" WHERE "id" = NEW."initiative_id";
635 END IF;
636 RETURN NEW;
637 END;
638 $$;
639
640 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
641 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
642
643 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
644 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
645
646 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
647 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
648 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
649
650
651 CREATE FUNCTION "autofill_initiative_id_trigger"()
652 RETURNS TRIGGER
653 LANGUAGE 'plpgsql' VOLATILE AS $$
654 BEGIN
655 IF NEW."initiative_id" ISNULL THEN
656 SELECT "initiative_id" INTO NEW."initiative_id"
657 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
658 END IF;
659 RETURN NEW;
660 END;
661 $$;
662
663 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
664 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
665
666 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
667 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
668
669
670
671 -----------------------------------------------------
672 -- Automatic calculation of certain default values --
673 -----------------------------------------------------
674
675 CREATE FUNCTION "copy_autoreject_trigger"()
676 RETURNS TRIGGER
677 LANGUAGE 'plpgsql' VOLATILE AS $$
678 BEGIN
679 IF NEW."autoreject" ISNULL THEN
680 SELECT "membership"."autoreject" INTO NEW."autoreject"
681 FROM "issue" JOIN "membership"
682 ON "issue"."area_id" = "membership"."area_id"
683 WHERE "issue"."id" = NEW."issue_id"
684 AND "membership"."member_id" = NEW."member_id";
685 END IF;
686 IF NEW."autoreject" ISNULL THEN
687 NEW."autoreject" := FALSE;
688 END IF;
689 RETURN NEW;
690 END;
691 $$;
692
693 CREATE TRIGGER "copy_autoreject" BEFORE INSERT OR UPDATE ON "interest"
694 FOR EACH ROW EXECUTE PROCEDURE "copy_autoreject_trigger"();
695
696 COMMENT ON FUNCTION "copy_autoreject_trigger"() IS 'Implementation of trigger "copy_autoreject" on table "interest"';
697 COMMENT ON TRIGGER "copy_autoreject" ON "interest" IS 'If "autoreject" is NULL, then copy it from the area setting, or set to FALSE, if no membership existent';
698
699
700 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
701 RETURNS TRIGGER
702 LANGUAGE 'plpgsql' VOLATILE AS $$
703 BEGIN
704 IF NEW."draft_id" ISNULL THEN
705 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
706 WHERE "initiative_id" = NEW."initiative_id";
707 END IF;
708 RETURN NEW;
709 END;
710 $$;
711
712 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
713 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
714
715 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
716 COMMENT ON TRIGGER "default_for_draft_id" ON "supporter" IS 'If "draft_id" is NULL, then use the current draft of the initiative as default';
717
718
719
720 ----------------------------------------
721 -- Automatic creation of dependencies --
722 ----------------------------------------
723
724 CREATE FUNCTION "autocreate_interest_trigger"()
725 RETURNS TRIGGER
726 LANGUAGE 'plpgsql' VOLATILE AS $$
727 BEGIN
728 IF NOT EXISTS (
729 SELECT NULL FROM "initiative" JOIN "interest"
730 ON "initiative"."issue_id" = "interest"."issue_id"
731 WHERE "initiative"."id" = NEW."initiative_id"
732 AND "interest"."member_id" = NEW."member_id"
733 ) THEN
734 BEGIN
735 INSERT INTO "interest" ("issue_id", "member_id")
736 SELECT "issue_id", NEW."member_id"
737 FROM "initiative" WHERE "id" = NEW."initiative_id";
738 EXCEPTION WHEN unique_violation THEN END;
739 END IF;
740 RETURN NEW;
741 END;
742 $$;
743
744 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
745 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
746
747 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
748 COMMENT ON TRIGGER "autocreate_interest" ON "supporter" IS 'Supporting an initiative implies interest in the issue, thus automatically creates an entry in the "interest" table';
749
750
751 CREATE FUNCTION "autocreate_supporter_trigger"()
752 RETURNS TRIGGER
753 LANGUAGE 'plpgsql' VOLATILE AS $$
754 BEGIN
755 IF NOT EXISTS (
756 SELECT NULL FROM "suggestion" JOIN "supporter"
757 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
758 WHERE "suggestion"."id" = NEW."suggestion_id"
759 AND "supporter"."member_id" = NEW."member_id"
760 ) THEN
761 BEGIN
762 INSERT INTO "supporter" ("initiative_id", "member_id")
763 SELECT "initiative_id", NEW."member_id"
764 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
765 EXCEPTION WHEN unique_violation THEN END;
766 END IF;
767 RETURN NEW;
768 END;
769 $$;
770
771 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
772 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
773
774 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
775 COMMENT ON TRIGGER "autocreate_supporter" ON "opinion" IS 'Opinions can only be added for supported initiatives. This trigger automatrically creates an entry in the "supporter" table, if not existent yet.';
776
777
778
779 ------------------------------------------
780 -- Views and helper functions for views --
781 ------------------------------------------
782
783 CREATE VIEW "member_count_view" AS
784 SELECT count(1) FROM "member" WHERE "active";
785
786 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
787
788
789 CREATE VIEW "area_member_count" AS
790 SELECT "area"."id" AS "area_id", count("member"."id")
791 FROM "area"
792 LEFT JOIN "membership"
793 ON "area"."id" = "membership"."area_id"
794 LEFT JOIN "member"
795 ON "membership"."member_id" = "member"."id"
796 AND "member"."active"
797 GROUP BY "area"."id";
798
799 COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"';
800
801
802 CREATE VIEW "issue_delegation_with_overridden_and_inactive" AS
803 SELECT "delegation".*, "issue"."id" AS "resulting_issue_id"
804 FROM "delegation"
805 JOIN "issue" ON
806 ("delegation"."area_id" ISNULL AND "delegation"."issue_id" ISNULL) OR
807 "delegation"."area_id" = "issue"."area_id" OR
808 "delegation"."issue_id" = "issue"."id";
809
810 COMMENT ON VIEW "issue_delegation_with_overridden_and_inactive" IS 'Helper view for "issue_delegation"';
811
812
813 CREATE VIEW "issue_delegation" AS
814 SELECT
815 "entry"."id" AS "id",
816 "entry"."truster_id" AS "truster_id",
817 "entry"."trustee_id" AS "trustee_id",
818 "entry"."resulting_issue_id" AS "issue_id"
819 FROM "issue_delegation_with_overridden_and_inactive" AS "entry"
820 JOIN "member" AS "truster" ON "entry"."truster_id" = "truster"."id"
821 JOIN "member" AS "trustee" ON "entry"."trustee_id" = "trustee"."id"
822 LEFT JOIN "issue_delegation_with_overridden_and_inactive" AS "override"
823 ON "entry"."truster_id" = "override"."truster_id"
824 AND "entry"."id" != "override"."id"
825 AND (
826 ("entry"."area_id" ISNULL AND "entry"."issue_id" ISNULL) OR
827 "override"."issue_id" NOTNULL
828 )
829 WHERE "truster"."active" AND "trustee"."active"
830 AND "override"."truster_id" ISNULL;
831
832 COMMENT ON VIEW "issue_delegation" IS 'Resulting delegations for issues, without those involving inactive members';
833
834
835 CREATE VIEW "current_draft" AS
836 SELECT "draft".* FROM (
837 SELECT
838 "initiative"."id" AS "initiative_id",
839 max("draft"."id") AS "draft_id"
840 FROM "initiative" JOIN "draft"
841 ON "initiative"."id" = "draft"."initiative_id"
842 GROUP BY "initiative"."id"
843 ) AS "subquery"
844 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
845
846 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
847
848
849 CREATE VIEW "critical_opinion" AS
850 SELECT * FROM "opinion"
851 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
852 OR ("degree" = -2 AND "fulfilled" = TRUE);
853
854 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
855
856
857 CREATE VIEW "battle_participant" AS
858 SELECT "issue_id", "id" AS "initiative_id" FROM "initiative"
859 WHERE "admitted"
860 AND "positive_votes" > "negative_votes";
861
862 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle" view';
863
864
865 CREATE VIEW "battle" AS
866 SELECT
867 "issue"."id" AS "issue_id",
868 "winning_initiative"."initiative_id" AS "winning_initiative_id",
869 "losing_initiative"."initiative_id" AS "losing_initiative_id",
870 sum(
871 CASE WHEN
872 coalesce("better_vote"."grade", 0) >
873 coalesce("worse_vote"."grade", 0)
874 THEN "direct_voter"."weight" ELSE 0 END
875 ) AS "count"
876 FROM "issue"
877 LEFT JOIN "direct_voter"
878 ON "issue"."id" = "direct_voter"."issue_id"
879 JOIN "battle_participant" AS "winning_initiative"
880 ON "issue"."id" = "winning_initiative"."issue_id"
881 JOIN "battle_participant" AS "losing_initiative"
882 ON "issue"."id" = "losing_initiative"."issue_id"
883 LEFT JOIN "vote" AS "better_vote"
884 ON "direct_voter"."member_id" = "better_vote"."member_id"
885 AND "winning_initiative"."initiative_id" = "better_vote"."initiative_id"
886 LEFT JOIN "vote" AS "worse_vote"
887 ON "direct_voter"."member_id" = "worse_vote"."member_id"
888 AND "losing_initiative"."initiative_id" = "worse_vote"."initiative_id"
889 WHERE
890 "winning_initiative"."initiative_id" !=
891 "losing_initiative"."initiative_id"
892 GROUP BY
893 "issue"."id",
894 "winning_initiative"."initiative_id",
895 "losing_initiative"."initiative_id";
896
897 COMMENT ON VIEW "battle" IS 'Number of members preferring one initiative over another';
898
899
900 CREATE VIEW "expired_session" AS
901 SELECT * FROM "session" WHERE now() > "expiry";
902
903 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
904 DELETE FROM "session" WHERE "ident" = OLD."ident";
905
906 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
907 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
908
909
910 CREATE VIEW "open_issue" AS
911 SELECT * FROM "issue" WHERE "closed" ISNULL;
912
913 COMMENT ON VIEW "open_issue" IS 'All open issues';
914
915
916 CREATE VIEW "issue_with_ranks_missing" AS
917 SELECT * FROM "issue"
918 WHERE "fully_frozen" NOTNULL
919 AND "closed" NOTNULL
920 AND "ranks_available" = FALSE;
921
922 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
923
924
925
926 ------------------------------
927 -- Comparison by vote count --
928 ------------------------------
929
930 CREATE FUNCTION "vote_ratio"
931 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
932 "negative_votes_p" "initiative"."negative_votes"%TYPE )
933 RETURNS FLOAT8
934 LANGUAGE 'plpgsql' STABLE AS $$
935 DECLARE
936 "total_v" INT4;
937 BEGIN
938 "total_v" := "positive_votes_p" + "negative_votes_p";
939 IF "total_v" > 0 THEN
940 RETURN "positive_votes_p"::FLOAT8 / "total_v"::FLOAT8;
941 ELSE
942 RETURN 0.5;
943 END IF;
944 END;
945 $$;
946
947 COMMENT ON FUNCTION "vote_ratio"
948 ( "initiative"."positive_votes"%TYPE,
949 "initiative"."negative_votes"%TYPE )
950 IS 'Ratio of positive votes to sum of positive and negative votes; 0.5, if there are neither positive nor negative votes';
951
952
953
954 ------------------------------------------------
955 -- Locking for snapshots and voting procedure --
956 ------------------------------------------------
957
958 CREATE FUNCTION "global_lock"() RETURNS VOID
959 LANGUAGE 'plpgsql' VOLATILE AS $$
960 BEGIN
961 -- NOTE: PostgreSQL allows reading, while tables are locked in
962 -- exclusive move. Transactions should be kept short anyway!
963 LOCK TABLE "member" IN EXCLUSIVE MODE;
964 LOCK TABLE "area" IN EXCLUSIVE MODE;
965 LOCK TABLE "membership" IN EXCLUSIVE MODE;
966 -- NOTE: "member", "area" and "membership" are locked first to
967 -- prevent deadlocks in combination with "calculate_member_counts"()
968 LOCK TABLE "policy" IN EXCLUSIVE MODE;
969 LOCK TABLE "issue" IN EXCLUSIVE MODE;
970 LOCK TABLE "initiative" IN EXCLUSIVE MODE;
971 LOCK TABLE "draft" IN EXCLUSIVE MODE;
972 LOCK TABLE "suggestion" IN EXCLUSIVE MODE;
973 LOCK TABLE "interest" IN EXCLUSIVE MODE;
974 LOCK TABLE "initiator" IN EXCLUSIVE MODE;
975 LOCK TABLE "supporter" IN EXCLUSIVE MODE;
976 LOCK TABLE "opinion" IN EXCLUSIVE MODE;
977 LOCK TABLE "delegation" IN EXCLUSIVE MODE;
978 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
979 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
980 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
981 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
982 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
983 LOCK TABLE "direct_voter" IN EXCLUSIVE MODE;
984 LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;
985 LOCK TABLE "vote" IN EXCLUSIVE MODE;
986 RETURN;
987 END;
988 $$;
989
990 COMMENT ON FUNCTION "global_lock"() IS 'Locks all tables related to support/voting until end of transaction; read access is still possible though';
991
992
993
994 -------------------------------
995 -- Materialize member counts --
996 -------------------------------
997
998 CREATE FUNCTION "calculate_member_counts"()
999 RETURNS VOID
1000 LANGUAGE 'plpgsql' VOLATILE AS $$
1001 BEGIN
1002 LOCK TABLE "member" IN EXCLUSIVE MODE;
1003 LOCK TABLE "area" IN EXCLUSIVE MODE;
1004 LOCK TABLE "membership" IN EXCLUSIVE MODE;
1005 DELETE FROM "member_count";
1006 INSERT INTO "member_count" ("count")
1007 SELECT "count" FROM "member_count_view";
1008 UPDATE "area" SET "member_count" = "area_member_count"."count"
1009 FROM "area_member_count"
1010 WHERE "area_member_count"."area_id" = "area"."id";
1011 RETURN;
1012 END;
1013 $$;
1014
1015 COMMENT ON FUNCTION "calculate_member_counts"() IS 'Updates "member_count" table and "member_count" column of table "area" by materializing data from views "member_count_view" and "area_member_count"';
1016
1017
1018
1019 ------------------------------
1020 -- Calculation of snapshots --
1021 ------------------------------
1022
1023 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
1024 ( "issue_id_p" "issue"."id"%TYPE,
1025 "member_id_p" "member"."id"%TYPE,
1026 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
1027 RETURNS "direct_population_snapshot"."weight"%TYPE
1028 LANGUAGE 'plpgsql' VOLATILE AS $$
1029 DECLARE
1030 "issue_delegation_row" "issue_delegation"%ROWTYPE;
1031 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
1032 "weight_v" INT4;
1033 BEGIN
1034 "weight_v" := 0;
1035 FOR "issue_delegation_row" IN
1036 SELECT * FROM "issue_delegation"
1037 WHERE "trustee_id" = "member_id_p"
1038 AND "issue_id" = "issue_id_p"
1039 LOOP
1040 IF NOT EXISTS (
1041 SELECT NULL FROM "direct_population_snapshot"
1042 WHERE "issue_id" = "issue_id_p"
1043 AND "event" = 'periodic'
1044 AND "member_id" = "issue_delegation_row"."truster_id"
1045 ) AND NOT EXISTS (
1046 SELECT NULL FROM "delegating_population_snapshot"
1047 WHERE "issue_id" = "issue_id_p"
1048 AND "event" = 'periodic'
1049 AND "member_id" = "issue_delegation_row"."truster_id"
1050 ) THEN
1051 "delegate_member_ids_v" :=
1052 "member_id_p" || "delegate_member_ids_p";
1053 INSERT INTO "delegating_population_snapshot"
1054 ("issue_id", "event", "member_id", "delegate_member_ids")
1055 VALUES (
1056 "issue_id_p",
1057 'periodic',
1058 "issue_delegation_row"."truster_id",
1059 "delegate_member_ids_v"
1060 );
1061 "weight_v" := "weight_v" + 1 +
1062 "weight_of_added_delegations_for_population_snapshot"(
1063 "issue_id_p",
1064 "issue_delegation_row"."truster_id",
1065 "delegate_member_ids_v"
1066 );
1067 END IF;
1068 END LOOP;
1069 RETURN "weight_v";
1070 END;
1071 $$;
1072
1073 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
1074 ( "issue"."id"%TYPE,
1075 "member"."id"%TYPE,
1076 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
1077 IS 'Helper function for "create_population_snapshot" function';
1078
1079
1080 CREATE FUNCTION "create_population_snapshot"
1081 ( "issue_id_p" "issue"."id"%TYPE )
1082 RETURNS VOID
1083 LANGUAGE 'plpgsql' VOLATILE AS $$
1084 DECLARE
1085 "member_id_v" "member"."id"%TYPE;
1086 BEGIN
1087 DELETE FROM "direct_population_snapshot"
1088 WHERE "issue_id" = "issue_id_p"
1089 AND "event" = 'periodic';
1090 DELETE FROM "delegating_population_snapshot"
1091 WHERE "issue_id" = "issue_id_p"
1092 AND "event" = 'periodic';
1093 INSERT INTO "direct_population_snapshot"
1094 ("issue_id", "event", "member_id", "interest_exists")
1095 SELECT DISTINCT ON ("issue_id", "member_id")
1096 "issue_id_p" AS "issue_id",
1097 'periodic' AS "event",
1098 "subquery"."member_id",
1099 "subquery"."interest_exists"
1100 FROM (
1101 SELECT
1102 "member"."id" AS "member_id",
1103 FALSE AS "interest_exists"
1104 FROM "issue"
1105 JOIN "area" ON "issue"."area_id" = "area"."id"
1106 JOIN "membership" ON "area"."id" = "membership"."area_id"
1107 JOIN "member" ON "membership"."member_id" = "member"."id"
1108 WHERE "issue"."id" = "issue_id_p"
1109 AND "member"."active"
1110 UNION
1111 SELECT
1112 "member"."id" AS "member_id",
1113 TRUE AS "interest_exists"
1114 FROM "interest" JOIN "member"
1115 ON "interest"."member_id" = "member"."id"
1116 WHERE "interest"."issue_id" = "issue_id_p"
1117 AND "member"."active"
1118 ) AS "subquery"
1119 ORDER BY
1120 "issue_id_p",
1121 "subquery"."member_id",
1122 "subquery"."interest_exists" DESC;
1123 FOR "member_id_v" IN
1124 SELECT "member_id" FROM "direct_population_snapshot"
1125 WHERE "issue_id" = "issue_id_p"
1126 AND "event" = 'periodic'
1127 LOOP
1128 UPDATE "direct_population_snapshot" SET
1129 "weight" = 1 +
1130 "weight_of_added_delegations_for_population_snapshot"(
1131 "issue_id_p",
1132 "member_id_v",
1133 '{}'
1134 )
1135 WHERE "issue_id" = "issue_id_p"
1136 AND "event" = 'periodic'
1137 AND "member_id" = "member_id_v";
1138 END LOOP;
1139 RETURN;
1140 END;
1141 $$;
1142
1143 COMMENT ON FUNCTION "create_population_snapshot"
1144 ( "issue_id_p" "issue"."id"%TYPE )
1145 IS 'This function creates a new ''periodic'' population snapshot for the given issue. It does neither lock any tables, nor updates precalculated values in other tables.';
1146
1147
1148 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
1149 ( "issue_id_p" "issue"."id"%TYPE,
1150 "member_id_p" "member"."id"%TYPE,
1151 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
1152 RETURNS "direct_interest_snapshot"."weight"%TYPE
1153 LANGUAGE 'plpgsql' VOLATILE AS $$
1154 DECLARE
1155 "issue_delegation_row" "issue_delegation"%ROWTYPE;
1156 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
1157 "weight_v" INT4;
1158 BEGIN
1159 "weight_v" := 0;
1160 FOR "issue_delegation_row" IN
1161 SELECT * FROM "issue_delegation"
1162 WHERE "trustee_id" = "member_id_p"
1163 AND "issue_id" = "issue_id_p"
1164 LOOP
1165 IF NOT EXISTS (
1166 SELECT NULL FROM "direct_interest_snapshot"
1167 WHERE "issue_id" = "issue_id_p"
1168 AND "event" = 'periodic'
1169 AND "member_id" = "issue_delegation_row"."truster_id"
1170 ) AND NOT EXISTS (
1171 SELECT NULL FROM "delegating_interest_snapshot"
1172 WHERE "issue_id" = "issue_id_p"
1173 AND "event" = 'periodic'
1174 AND "member_id" = "issue_delegation_row"."truster_id"
1175 ) THEN
1176 "delegate_member_ids_v" :=
1177 "member_id_p" || "delegate_member_ids_p";
1178 INSERT INTO "delegating_interest_snapshot"
1179 ("issue_id", "event", "member_id", "delegate_member_ids")
1180 VALUES (
1181 "issue_id_p",
1182 'periodic',
1183 "issue_delegation_row"."truster_id",
1184 "delegate_member_ids_v"
1185 );
1186 "weight_v" := "weight_v" + 1 +
1187 "weight_of_added_delegations_for_interest_snapshot"(
1188 "issue_id_p",
1189 "issue_delegation_row"."truster_id",
1190 "delegate_member_ids_v"
1191 );
1192 END IF;
1193 END LOOP;
1194 RETURN "weight_v";
1195 END;
1196 $$;
1197
1198 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
1199 ( "issue"."id"%TYPE,
1200 "member"."id"%TYPE,
1201 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
1202 IS 'Helper function for "create_interest_snapshot" function';
1203
1204
1205 CREATE FUNCTION "create_interest_snapshot"
1206 ( "issue_id_p" "issue"."id"%TYPE )
1207 RETURNS VOID
1208 LANGUAGE 'plpgsql' VOLATILE AS $$
1209 DECLARE
1210 "member_id_v" "member"."id"%TYPE;
1211 BEGIN
1212 DELETE FROM "direct_interest_snapshot"
1213 WHERE "issue_id" = "issue_id_p"
1214 AND "event" = 'periodic';
1215 DELETE FROM "delegating_interest_snapshot"
1216 WHERE "issue_id" = "issue_id_p"
1217 AND "event" = 'periodic';
1218 DELETE FROM "direct_supporter_snapshot"
1219 WHERE "issue_id" = "issue_id_p"
1220 AND "event" = 'periodic';
1221 INSERT INTO "direct_interest_snapshot"
1222 ("issue_id", "event", "member_id", "voting_requested")
1223 SELECT
1224 "issue_id_p" AS "issue_id",
1225 'periodic' AS "event",
1226 "member"."id" AS "member_id",
1227 "interest"."voting_requested"
1228 FROM "interest" JOIN "member"
1229 ON "interest"."member_id" = "member"."id"
1230 WHERE "interest"."issue_id" = "issue_id_p"
1231 AND "member"."active";
1232 FOR "member_id_v" IN
1233 SELECT "member_id" FROM "direct_interest_snapshot"
1234 WHERE "issue_id" = "issue_id_p"
1235 AND "event" = 'periodic'
1236 LOOP
1237 UPDATE "direct_interest_snapshot" SET
1238 "weight" = 1 +
1239 "weight_of_added_delegations_for_interest_snapshot"(
1240 "issue_id_p",
1241 "member_id_v",
1242 '{}'
1243 )
1244 WHERE "issue_id" = "issue_id_p"
1245 AND "event" = 'periodic'
1246 AND "member_id" = "member_id_v";
1247 END LOOP;
1248 INSERT INTO "direct_supporter_snapshot"
1249 ( "issue_id", "initiative_id", "event", "member_id",
1250 "informed", "satisfied" )
1251 SELECT
1252 "issue_id_p" AS "issue_id",
1253 "initiative"."id" AS "initiative_id",
1254 'periodic' AS "event",
1255 "member"."id" AS "member_id",
1256 "supporter"."draft_id" = "current_draft"."id" AS "informed",
1257 NOT EXISTS (
1258 SELECT NULL FROM "critical_opinion"
1259 WHERE "initiative_id" = "initiative"."id"
1260 AND "member_id" = "member"."id"
1261 ) AS "satisfied"
1262 FROM "supporter"
1263 JOIN "member"
1264 ON "supporter"."member_id" = "member"."id"
1265 JOIN "initiative"
1266 ON "supporter"."initiative_id" = "initiative"."id"
1267 JOIN "current_draft"
1268 ON "initiative"."id" = "current_draft"."initiative_id"
1269 JOIN "direct_interest_snapshot"
1270 ON "member"."id" = "direct_interest_snapshot"."member_id"
1271 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
1272 AND "event" = 'periodic'
1273 WHERE "member"."active"
1274 AND "initiative"."issue_id" = "issue_id_p";
1275 RETURN;
1276 END;
1277 $$;
1278
1279 COMMENT ON FUNCTION "create_interest_snapshot"
1280 ( "issue"."id"%TYPE )
1281 IS 'This function creates a new ''periodic'' interest/supporter snapshot for the given issue. It does neither lock any tables, nor updates precalculated values in other tables.';
1282
1283
1284 CREATE FUNCTION "create_snapshot"
1285 ( "issue_id_p" "issue"."id"%TYPE )
1286 RETURNS VOID
1287 LANGUAGE 'plpgsql' VOLATILE AS $$
1288 DECLARE
1289 "initiative_id_v" "initiative"."id"%TYPE;
1290 "suggestion_id_v" "suggestion"."id"%TYPE;
1291 BEGIN
1292 PERFORM "global_lock"();
1293 PERFORM "create_population_snapshot"("issue_id_p");
1294 PERFORM "create_interest_snapshot"("issue_id_p");
1295 UPDATE "issue" SET
1296 "snapshot" = now(),
1297 "population" = (
1298 SELECT coalesce(sum("weight"), 0)
1299 FROM "direct_population_snapshot"
1300 WHERE "issue_id" = "issue_id_p"
1301 AND "event" = 'periodic'
1302 ),
1303 "vote_now" = (
1304 SELECT coalesce(sum("weight"), 0)
1305 FROM "direct_interest_snapshot"
1306 WHERE "issue_id" = "issue_id_p"
1307 AND "event" = 'periodic'
1308 AND "voting_requested" = TRUE
1309 ),
1310 "vote_later" = (
1311 SELECT coalesce(sum("weight"), 0)
1312 FROM "direct_interest_snapshot"
1313 WHERE "issue_id" = "issue_id_p"
1314 AND "event" = 'periodic'
1315 AND "voting_requested" = FALSE
1316 )
1317 WHERE "id" = "issue_id_p";
1318 FOR "initiative_id_v" IN
1319 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
1320 LOOP
1321 UPDATE "initiative" SET
1322 "supporter_count" = (
1323 SELECT coalesce(sum("di"."weight"), 0)
1324 FROM "direct_interest_snapshot" AS "di"
1325 JOIN "direct_supporter_snapshot" AS "ds"
1326 ON "di"."member_id" = "ds"."member_id"
1327 WHERE "di"."issue_id" = "issue_id_p"
1328 AND "di"."event" = 'periodic'
1329 AND "ds"."initiative_id" = "initiative_id_v"
1330 AND "ds"."event" = 'periodic'
1331 ),
1332 "informed_supporter_count" = (
1333 SELECT coalesce(sum("di"."weight"), 0)
1334 FROM "direct_interest_snapshot" AS "di"
1335 JOIN "direct_supporter_snapshot" AS "ds"
1336 ON "di"."member_id" = "ds"."member_id"
1337 WHERE "di"."issue_id" = "issue_id_p"
1338 AND "di"."event" = 'periodic'
1339 AND "ds"."initiative_id" = "initiative_id_v"
1340 AND "ds"."event" = 'periodic'
1341 AND "ds"."informed"
1342 ),
1343 "satisfied_supporter_count" = (
1344 SELECT coalesce(sum("di"."weight"), 0)
1345 FROM "direct_interest_snapshot" AS "di"
1346 JOIN "direct_supporter_snapshot" AS "ds"
1347 ON "di"."member_id" = "ds"."member_id"
1348 WHERE "di"."issue_id" = "issue_id_p"
1349 AND "di"."event" = 'periodic'
1350 AND "ds"."initiative_id" = "initiative_id_v"
1351 AND "ds"."event" = 'periodic'
1352 AND "ds"."satisfied"
1353 ),
1354 "satisfied_informed_supporter_count" = (
1355 SELECT coalesce(sum("di"."weight"), 0)
1356 FROM "direct_interest_snapshot" AS "di"
1357 JOIN "direct_supporter_snapshot" AS "ds"
1358 ON "di"."member_id" = "ds"."member_id"
1359 WHERE "di"."issue_id" = "issue_id_p"
1360 AND "di"."event" = 'periodic'
1361 AND "ds"."initiative_id" = "initiative_id_v"
1362 AND "ds"."event" = 'periodic'
1363 AND "ds"."informed"
1364 AND "ds"."satisfied"
1365 )
1366 WHERE "id" = "initiative_id_v";
1367 FOR "suggestion_id_v" IN
1368 SELECT "id" FROM "suggestion"
1369 WHERE "initiative_id" = "initiative_id_v"
1370 LOOP
1371 UPDATE "suggestion" SET
1372 "minus2_unfulfilled_count" = (
1373 SELECT coalesce(sum("snapshot"."weight"), 0)
1374 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
1375 ON "opinion"."member_id" = "snapshot"."member_id"
1376 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
1377 AND "snapshot"."issue_id" = "issue_id_p"
1378 AND "opinion"."degree" = -2
1379 AND "opinion"."fulfilled" = FALSE
1380 ),
1381 "minus2_fulfilled_count" = (
1382 SELECT coalesce(sum("snapshot"."weight"), 0)
1383 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
1384 ON "opinion"."member_id" = "snapshot"."member_id"
1385 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
1386 AND "snapshot"."issue_id" = "issue_id_p"
1387 AND "opinion"."degree" = -2
1388 AND "opinion"."fulfilled" = TRUE
1389 ),
1390 "minus1_unfulfilled_count" = (
1391 SELECT coalesce(sum("snapshot"."weight"), 0)
1392 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
1393 ON "opinion"."member_id" = "snapshot"."member_id"
1394 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
1395 AND "snapshot"."issue_id" = "issue_id_p"
1396 AND "opinion"."degree" = -1
1397 AND "opinion"."fulfilled" = FALSE
1398 ),
1399 "minus1_fulfilled_count" = (
1400 SELECT coalesce(sum("snapshot"."weight"), 0)
1401 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
1402 ON "opinion"."member_id" = "snapshot"."member_id"
1403 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
1404 AND "snapshot"."issue_id" = "issue_id_p"
1405 AND "opinion"."degree" = -1
1406 AND "opinion"."fulfilled" = TRUE
1407 ),
1408 "plus1_unfulfilled_count" = (
1409 SELECT coalesce(sum("snapshot"."weight"), 0)
1410 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
1411 ON "opinion"."member_id" = "snapshot"."member_id"
1412 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
1413 AND "snapshot"."issue_id" = "issue_id_p"
1414 AND "opinion"."degree" = 1
1415 AND "opinion"."fulfilled" = FALSE
1416 ),
1417 "plus1_fulfilled_count" = (
1418 SELECT coalesce(sum("snapshot"."weight"), 0)
1419 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
1420 ON "opinion"."member_id" = "snapshot"."member_id"
1421 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
1422 AND "snapshot"."issue_id" = "issue_id_p"
1423 AND "opinion"."degree" = 1
1424 AND "opinion"."fulfilled" = TRUE
1425 ),
1426 "plus2_unfulfilled_count" = (
1427 SELECT coalesce(sum("snapshot"."weight"), 0)
1428 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
1429 ON "opinion"."member_id" = "snapshot"."member_id"
1430 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
1431 AND "snapshot"."issue_id" = "issue_id_p"
1432 AND "opinion"."degree" = 2
1433 AND "opinion"."fulfilled" = FALSE
1434 ),
1435 "plus2_fulfilled_count" = (
1436 SELECT coalesce(sum("snapshot"."weight"), 0)
1437 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
1438 ON "opinion"."member_id" = "snapshot"."member_id"
1439 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
1440 AND "snapshot"."issue_id" = "issue_id_p"
1441 AND "opinion"."degree" = 2
1442 AND "opinion"."fulfilled" = TRUE
1443 )
1444 WHERE "suggestion"."id" = "suggestion_id_v";
1445 END LOOP;
1446 END LOOP;
1447 RETURN;
1448 END;
1449 $$;
1450
1451 COMMENT ON FUNCTION "create_snapshot"
1452 ( "issue"."id"%TYPE )
1453 IS 'This function creates a complete new ''periodic'' snapshot of population, interest and support for the given issue. All involved tables are locked, and after completion precalculated values in the source tables are updated.';
1454
1455
1456 CREATE FUNCTION "set_snapshot_event"
1457 ( "issue_id_p" "issue"."id"%TYPE,
1458 "event_p" "snapshot_event" )
1459 RETURNS VOID
1460 LANGUAGE 'plpgsql' VOLATILE AS $$
1461 BEGIN
1462 UPDATE "direct_population_snapshot" SET "event" = "event_p"
1463 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
1464 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
1465 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
1466 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
1467 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
1468 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
1469 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
1470 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
1471 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
1472 RETURN;
1473 END;
1474 $$;
1475
1476 COMMENT ON FUNCTION "set_snapshot_event"
1477 ( "issue"."id"%TYPE,
1478 "snapshot_event" )
1479 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
1480
1481
1482
1483 ---------------------
1484 -- Freezing issues --
1485 ---------------------
1486
1487 CREATE FUNCTION "freeze_after_snapshot"
1488 ( "issue_id_p" "issue"."id"%TYPE )
1489 RETURNS VOID
1490 LANGUAGE 'plpgsql' VOLATILE AS $$
1491 DECLARE
1492 "issue_row" "issue"%ROWTYPE;
1493 "policy_row" "policy"%ROWTYPE;
1494 "initiative_row" "initiative"%ROWTYPE;
1495 BEGIN
1496 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1497 SELECT * INTO "policy_row"
1498 FROM "policy" WHERE "id" = "issue_row"."policy_id";
1499 PERFORM "set_snapshot_event"("issue_id_p", 'start_of_voting');
1500 UPDATE "issue" SET
1501 "accepted" = coalesce("accepted", now()),
1502 "half_frozen" = coalesce("half_frozen", now()),
1503 "fully_frozen" = now()
1504 WHERE "id" = "issue_id_p";
1505 FOR "initiative_row" IN
1506 SELECT * FROM "initiative" WHERE "issue_id" = "issue_id_p"
1507 LOOP
1508 IF
1509 "initiative_row"."satisfied_supporter_count" > 0 AND
1510 "initiative_row"."satisfied_supporter_count" *
1511 "policy_row"."initiative_quorum_den" >=
1512 "issue_row"."population" * "policy_row"."initiative_quorum_num"
1513 THEN
1514 UPDATE "initiative" SET "admitted" = TRUE
1515 WHERE "id" = "initiative_row"."id";
1516 ELSE
1517 UPDATE "initiative" SET "admitted" = FALSE
1518 WHERE "id" = "initiative_row"."id";
1519 END IF;
1520 END LOOP;
1521 RETURN;
1522 END;
1523 $$;
1524
1525 COMMENT ON FUNCTION "freeze_after_snapshot"
1526 ( "issue"."id"%TYPE )
1527 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction';
1528
1529
1530 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
1531 RETURNS VOID
1532 LANGUAGE 'plpgsql' VOLATILE AS $$
1533 DECLARE
1534 "issue_row" "issue"%ROWTYPE;
1535 BEGIN
1536 PERFORM "create_snapshot"("issue_id_p");
1537 PERFORM "freeze_after_snapshot"("issue_id_p");
1538 RETURN;
1539 END;
1540 $$;
1541
1542 COMMENT ON FUNCTION "freeze_after_snapshot"
1543 ( "issue"."id"%TYPE )
1544 IS 'Freeze an issue manually (fully) and start voting';
1545
1546
1547
1548 -----------------------
1549 -- Counting of votes --
1550 -----------------------
1551
1552
1553 CREATE FUNCTION "weight_of_added_delegations"
1554 ( "issue_id_p" "issue"."id"%TYPE,
1555 "member_id_p" "member"."id"%TYPE,
1556 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
1557 RETURNS "direct_voter"."weight"%TYPE
1558 LANGUAGE 'plpgsql' VOLATILE AS $$
1559 DECLARE
1560 "issue_delegation_row" "issue_delegation"%ROWTYPE;
1561 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
1562 "weight_v" INT4;
1563 BEGIN
1564 "weight_v" := 0;
1565 FOR "issue_delegation_row" IN
1566 SELECT * FROM "issue_delegation"
1567 WHERE "trustee_id" = "member_id_p"
1568 AND "issue_id" = "issue_id_p"
1569 LOOP
1570 IF NOT EXISTS (
1571 SELECT NULL FROM "direct_voter"
1572 WHERE "member_id" = "issue_delegation_row"."truster_id"
1573 AND "issue_id" = "issue_id_p"
1574 ) AND NOT EXISTS (
1575 SELECT NULL FROM "delegating_voter"
1576 WHERE "member_id" = "issue_delegation_row"."truster_id"
1577 AND "issue_id" = "issue_id_p"
1578 ) THEN
1579 "delegate_member_ids_v" :=
1580 "member_id_p" || "delegate_member_ids_p";
1581 INSERT INTO "delegating_voter"
1582 ("member_id", "issue_id", "delegate_member_ids")
1583 VALUES (
1584 "issue_delegation_row"."truster_id",
1585 "issue_id_p",
1586 "delegate_member_ids_v"
1587 );
1588 "weight_v" := "weight_v" + 1 + "weight_of_added_delegations"(
1589 "issue_id_p",
1590 "issue_delegation_row"."truster_id",
1591 "delegate_member_ids_v"
1592 );
1593 END IF;
1594 END LOOP;
1595 RETURN "weight_v";
1596 END;
1597 $$;
1598
1599 COMMENT ON FUNCTION "weight_of_added_delegations"
1600 ( "issue"."id"%TYPE,
1601 "member"."id"%TYPE,
1602 "delegating_voter"."delegate_member_ids"%TYPE )
1603 IS 'Helper function for "add_vote_delegations" function';
1604
1605
1606 CREATE FUNCTION "add_vote_delegations"
1607 ( "issue_id_p" "issue"."id"%TYPE )
1608 RETURNS VOID
1609 LANGUAGE 'plpgsql' VOLATILE AS $$
1610 DECLARE
1611 "member_id_v" "member"."id"%TYPE;
1612 BEGIN
1613 FOR "member_id_v" IN
1614 SELECT "member_id" FROM "direct_voter"
1615 WHERE "issue_id" = "issue_id_p"
1616 LOOP
1617 UPDATE "direct_voter" SET
1618 "weight" = "weight" + "weight_of_added_delegations"(
1619 "issue_id_p",
1620 "member_id_v",
1621 '{}'
1622 )
1623 WHERE "member_id" = "member_id_v"
1624 AND "issue_id" = "issue_id_p";
1625 END LOOP;
1626 RETURN;
1627 END;
1628 $$;
1629
1630 COMMENT ON FUNCTION "add_vote_delegations"
1631 ( "issue_id_p" "issue"."id"%TYPE )
1632 IS 'Helper function for "close_voting" function';
1633
1634
1635 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
1636 RETURNS VOID
1637 LANGUAGE 'plpgsql' VOLATILE AS $$
1638 DECLARE
1639 "issue_row" "issue"%ROWTYPE;
1640 "member_id_v" "member"."id"%TYPE;
1641 BEGIN
1642 PERFORM "global_lock"();
1643 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1644 DELETE FROM "delegating_voter"
1645 WHERE "issue_id" = "issue_id_p";
1646 DELETE FROM "direct_voter"
1647 WHERE "issue_id" = "issue_id_p"
1648 AND "autoreject" = TRUE;
1649 DELETE FROM "direct_voter" USING "member"
1650 WHERE "direct_voter"."member_id" = "member"."id"
1651 AND "direct_voter"."issue_id" = "issue_id_p"
1652 AND "member"."active" = FALSE;
1653 UPDATE "direct_voter" SET "weight" = 1
1654 WHERE "issue_id" = "issue_id_p";
1655 PERFORM "add_vote_delegations"("issue_id_p");
1656 FOR "member_id_v" IN
1657 SELECT "interest"."member_id"
1658 FROM "interest"
1659 LEFT JOIN "direct_voter"
1660 ON "interest"."member_id" = "direct_voter"."member_id"
1661 AND "interest"."issue_id" = "direct_voter"."issue_id"
1662 LEFT JOIN "delegating_voter"
1663 ON "interest"."member_id" = "delegating_voter"."member_id"
1664 AND "interest"."issue_id" = "delegating_voter"."issue_id"
1665 WHERE "interest"."issue_id" = "issue_id_p"
1666 AND "interest"."autoreject" = TRUE
1667 AND "direct_voter"."member_id" ISNULL
1668 AND "delegating_voter"."member_id" ISNULL
1669 UNION SELECT "membership"."member_id"
1670 FROM "membership"
1671 LEFT JOIN "interest"
1672 ON "membership"."member_id" = "interest"."member_id"
1673 AND "interest"."issue_id" = "issue_id_p"
1674 LEFT JOIN "direct_voter"
1675 ON "membership"."member_id" = "direct_voter"."member_id"
1676 AND "direct_voter"."issue_id" = "issue_id_p"
1677 LEFT JOIN "delegating_voter"
1678 ON "membership"."member_id" = "delegating_voter"."member_id"
1679 AND "delegating_voter"."issue_id" = "issue_id_p"
1680 WHERE "membership"."area_id" = "issue_row"."area_id"
1681 AND "membership"."autoreject" = TRUE
1682 AND "interest"."autoreject" ISNULL
1683 AND "direct_voter"."member_id" ISNULL
1684 AND "delegating_voter"."member_id" ISNULL
1685 LOOP
1686 INSERT INTO "direct_voter" ("member_id", "issue_id", "autoreject")
1687 VALUES ("member_id_v", "issue_id_p", TRUE);
1688 INSERT INTO "vote" (
1689 "member_id",
1690 "issue_id",
1691 "initiative_id",
1692 "grade"
1693 ) SELECT
1694 "member_id_v" AS "member_id",
1695 "issue_id_p" AS "issue_id",
1696 "id" AS "initiative_id",
1697 -1 AS "grade"
1698 FROM "initiative" WHERE "issue_id" = "issue_id_p";
1699 END LOOP;
1700 PERFORM "add_vote_delegations"("issue_id_p");
1701 UPDATE "issue" SET
1702 "voter_count" = (
1703 SELECT coalesce(sum("weight"), 0)
1704 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
1705 );
1706 UPDATE "initiative" SET
1707 "positive_votes" = "subquery"."positive_votes",
1708 "negative_votes" = "subquery"."negative_votes"
1709 FROM (
1710 SELECT
1711 "initiative_id",
1712 coalesce(
1713 sum(
1714 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
1715 ),
1716 0
1717 ) AS "positive_votes",
1718 coalesce(
1719 sum(
1720 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
1721 ),
1722 0
1723 ) AS "negative_votes"
1724 FROM "vote" JOIN "direct_voter"
1725 ON "vote"."member_id" = "direct_voter"."member_id"
1726 AND "vote"."issue_id" = "direct_voter"."issue_id"
1727 WHERE "vote"."issue_id" = "issue_id_p"
1728 GROUP BY "initiative_id"
1729 ) AS "subquery"
1730 WHERE "initiative"."admitted"
1731 AND "initiative"."id" = "subquery"."initiative_id";
1732 UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p";
1733 END;
1734 $$;
1735
1736 COMMENT ON FUNCTION "close_voting"
1737 ( "issue"."id"%TYPE )
1738 IS 'Closes the voting on an issue, and calculates positive and negative votes for each initiative; The ranking is not calculated yet, to keep the (locking) transaction short.';
1739
1740
1741 CREATE FUNCTION "init_array"("dim_p" INTEGER)
1742 RETURNS INT4[]
1743 LANGUAGE 'plpgsql' IMMUTABLE AS $$
1744 DECLARE
1745 "i" INTEGER;
1746 "ary_text_v" TEXT;
1747 BEGIN
1748 IF "dim_p" >= 1 THEN
1749 "ary_text_v" := '{NULL';
1750 "i" := "dim_p";
1751 LOOP
1752 "i" := "i" - 1;
1753 EXIT WHEN "i" = 0;
1754 "ary_text_v" := "ary_text_v" || ',NULL';
1755 END LOOP;
1756 "ary_text_v" := "ary_text_v" || '}';
1757 RETURN "ary_text_v"::INT4[][];
1758 ELSE
1759 RAISE EXCEPTION 'Dimension needs to be at least 1.';
1760 END IF;
1761 END;
1762 $$;
1763
1764 COMMENT ON FUNCTION "init_array"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
1765
1766
1767 CREATE FUNCTION "init_square_matrix"("dim_p" INTEGER)
1768 RETURNS INT4[][]
1769 LANGUAGE 'plpgsql' IMMUTABLE AS $$
1770 DECLARE
1771 "i" INTEGER;
1772 "row_text_v" TEXT;
1773 "ary_text_v" TEXT;
1774 BEGIN
1775 IF "dim_p" >= 1 THEN
1776 "row_text_v" := '{NULL';
1777 "i" := "dim_p";
1778 LOOP
1779 "i" := "i" - 1;
1780 EXIT WHEN "i" = 0;
1781 "row_text_v" := "row_text_v" || ',NULL';
1782 END LOOP;
1783 "row_text_v" := "row_text_v" || '}';
1784 "ary_text_v" := '{' || "row_text_v";
1785 "i" := "dim_p";
1786 LOOP
1787 "i" := "i" - 1;
1788 EXIT WHEN "i" = 0;
1789 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
1790 END LOOP;
1791 "ary_text_v" := "ary_text_v" || '}';
1792 RETURN "ary_text_v"::INT4[][];
1793 ELSE
1794 RAISE EXCEPTION 'Dimension needs to be at least 1.';
1795 END IF;
1796 END;
1797 $$;
1798
1799 COMMENT ON FUNCTION "init_square_matrix"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
1800
1801
1802 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
1803 RETURNS VOID
1804 LANGUAGE 'plpgsql' VOLATILE AS $$
1805 DECLARE
1806 "dimension_v" INTEGER;
1807 "matrix" INT4[][];
1808 "i" INTEGER;
1809 "j" INTEGER;
1810 "k" INTEGER;
1811 "battle_row" "battle"%ROWTYPE;
1812 "rank_ary" INT4[];
1813 "rank_v" INT4;
1814 "done_v" INTEGER;
1815 "winners_ary" INTEGER[];
1816 "initiative_id_v" "initiative"."id"%TYPE;
1817 BEGIN
1818 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
1819 -- Prepare matrix for Schulze-Method:
1820 SELECT count(1) INTO "dimension_v"
1821 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
1822 IF "dimension_v" = 1 THEN
1823 UPDATE "initiative" SET
1824 "rank" = 1
1825 FROM "battle_participant"
1826 WHERE "initiative"."issue_id" = "issue_id_p"
1827 AND "initiative"."id" = "battle_participant"."initiative_id";
1828 ELSIF "dimension_v" > 1 THEN
1829 "matrix" := "init_square_matrix"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
1830 "i" := 1;
1831 "j" := 2;
1832 -- Fill matrix with data from "battle" view
1833 FOR "battle_row" IN
1834 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
1835 ORDER BY "winning_initiative_id", "losing_initiative_id"
1836 LOOP
1837 "matrix"["i"]["j"] := "battle_row"."count";
1838 IF "j" = "dimension_v" THEN
1839 "i" := "i" + 1;
1840 "j" := 1;
1841 ELSE
1842 "j" := "j" + 1;
1843 IF "j" = "i" THEN
1844 "j" := "j" + 1;
1845 END IF;
1846 END IF;
1847 END LOOP;
1848 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
1849 RAISE EXCEPTION 'Wrong battle count (should not happen)';
1850 END IF;
1851 -- Delete losers from matrix:
1852 "i" := 1;
1853 LOOP
1854 "j" := "i" + 1;
1855 LOOP
1856 IF "i" != "j" THEN
1857 IF "matrix"["i"]["j"] < "matrix"["j"]["i"] THEN
1858 "matrix"["i"]["j"] := 0;
1859 ELSIF matrix[j][i] < matrix[i][j] THEN
1860 "matrix"["j"]["i"] := 0;
1861 ELSE
1862 "matrix"["i"]["j"] := 0;
1863 "matrix"["j"]["i"] := 0;
1864 END IF;
1865 END IF;
1866 EXIT WHEN "j" = "dimension_v";
1867 "j" := "j" + 1;
1868 END LOOP;
1869 EXIT WHEN "i" = "dimension_v" - 1;
1870 "i" := "i" + 1;
1871 END LOOP;
1872 -- Find best paths:
1873 "i" := 1;
1874 LOOP
1875 "j" := 1;
1876 LOOP
1877 IF "i" != "j" THEN
1878 "k" := 1;
1879 LOOP
1880 IF "i" != "k" AND "j" != "k" THEN
1881 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
1882 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
1883 "matrix"["j"]["k"] := "matrix"["j"]["i"];
1884 END IF;
1885 ELSE
1886 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
1887 "matrix"["j"]["k"] := "matrix"["i"]["k"];
1888 END IF;
1889 END IF;
1890 END IF;
1891 EXIT WHEN "k" = "dimension_v";
1892 "k" := "k" + 1;
1893 END LOOP;
1894 END IF;
1895 EXIT WHEN "j" = "dimension_v";
1896 "j" := "j" + 1;
1897 END LOOP;
1898 EXIT WHEN "i" = "dimension_v";
1899 "i" := "i" + 1;
1900 END LOOP;
1901 -- Determine order of winners:
1902 "rank_ary" := "init_array"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
1903 "rank_v" := 1;
1904 "done_v" := 0;
1905 LOOP
1906 "winners_ary" := '{}';
1907 "i" := 1;
1908 LOOP
1909 IF "rank_ary"["i"] ISNULL THEN
1910 "j" := 1;
1911 LOOP
1912 IF
1913 "i" != "j" AND
1914 "rank_ary"["j"] ISNULL AND
1915 "matrix"["j"]["i"] > "matrix"["i"]["j"]
1916 THEN
1917 -- someone else is better
1918 EXIT;
1919 END IF;
1920 IF "j" = "dimension_v" THEN
1921 -- noone is better
1922 "winners_ary" := "winners_ary" || "i";
1923 EXIT;
1924 END IF;
1925 "j" := "j" + 1;
1926 END LOOP;
1927 END IF;
1928 EXIT WHEN "i" = "dimension_v";
1929 "i" := "i" + 1;
1930 END LOOP;
1931 "i" := 1;
1932 LOOP
1933 "rank_ary"["winners_ary"["i"]] := "rank_v";
1934 "done_v" := "done_v" + 1;
1935 EXIT WHEN "i" = array_upper("winners_ary", 1);
1936 "i" := "i" + 1;
1937 END LOOP;
1938 EXIT WHEN "done_v" = "dimension_v";
1939 "rank_v" := "rank_v" + 1;
1940 END LOOP;
1941 -- write preliminary ranks:
1942 "i" := 1;
1943 FOR "initiative_id_v" IN
1944 SELECT "initiative"."id"
1945 FROM "initiative" JOIN "battle_participant"
1946 ON "initiative"."id" = "battle_participant"."initiative_id"
1947 WHERE "initiative"."issue_id" = "issue_id_p"
1948 ORDER BY "initiative"."id"
1949 LOOP
1950 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
1951 WHERE "id" = "initiative_id_v";
1952 "i" := "i" + 1;
1953 END LOOP;
1954 IF "i" != "dimension_v" + 1 THEN
1955 RAISE EXCEPTION 'Wrong winner count (should not happen)';
1956 END IF;
1957 -- straighten ranks (start counting with 1, no equal ranks):
1958 "rank_v" := 1;
1959 FOR "initiative_id_v" IN
1960 SELECT "id" FROM "initiative"
1961 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
1962 ORDER BY
1963 "rank",
1964 "vote_ratio"("positive_votes", "negative_votes") DESC,
1965 "id"
1966 LOOP
1967 UPDATE "initiative" SET "rank" = "rank_v"
1968 WHERE "id" = "initiative_id_v";
1969 "rank_v" := "rank_v" + 1;
1970 END LOOP;
1971 END IF;
1972 -- mark issue as finished
1973 UPDATE "issue" SET "ranks_available" = TRUE
1974 WHERE "id" = "issue_id_p";
1975 RETURN;
1976 END;
1977 $$;
1978
1979 COMMENT ON FUNCTION "calculate_ranks"
1980 ( "issue"."id"%TYPE )
1981 IS 'Determine ranking (Votes have to be counted first)';
1982
1983
1984
1985 -----------------------------
1986 -- Automatic state changes --
1987 -----------------------------
1988
1989
1990 CREATE FUNCTION "check_issue"
1991 ( "issue_id_p" "issue"."id"%TYPE )
1992 RETURNS VOID
1993 LANGUAGE 'plpgsql' VOLATILE AS $$
1994 DECLARE
1995 "issue_row" "issue"%ROWTYPE;
1996 "policy_row" "policy"%ROWTYPE;
1997 "voting_requested_v" BOOLEAN;
1998 BEGIN
1999 PERFORM "global_lock"();
2000 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2001 IF "issue_row"."closed" ISNULL THEN
2002 SELECT * INTO "policy_row" FROM "policy"
2003 WHERE "id" = "issue_row"."policy_id";
2004 IF "issue_row"."fully_frozen" ISNULL THEN
2005 PERFORM "create_snapshot"("issue_id_p");
2006 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2007 END IF;
2008 IF "issue_row"."accepted" ISNULL THEN
2009 IF EXISTS (
2010 SELECT NULL FROM "initiative"
2011 WHERE "issue_id" = "issue_id_p"
2012 AND "supporter_count" > 0
2013 AND "supporter_count" * "policy_row"."issue_quorum_den"
2014 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
2015 ) THEN
2016 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
2017 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later
2018 UPDATE "issue" SET "accepted" = "issue_row"."accepted"
2019 WHERE "id" = "issue_row"."id";
2020 ELSIF
2021 now() >= "issue_row"."created" + "policy_row"."admission_time"
2022 THEN
2023 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
2024 UPDATE "issue" SET "closed" = now()
2025 WHERE "id" = "issue_row"."id";
2026 END IF;
2027 END IF;
2028 IF
2029 "issue_row"."accepted" NOTNULL AND
2030 "issue_row"."half_frozen" ISNULL
2031 THEN
2032 SELECT
2033 CASE
2034 WHEN "vote_now" * 2 > "issue_row"."population" THEN
2035 TRUE
2036 WHEN "vote_later" * 2 > "issue_row"."population" THEN
2037 FALSE
2038 ELSE NULL
2039 END
2040 INTO "voting_requested_v"
2041 FROM "issue" WHERE "id" = "issue_id_p";
2042 IF
2043 "voting_requested_v" OR (
2044 "voting_requested_v" ISNULL AND
2045 now() >= "issue_row"."accepted" + "policy_row"."discussion_time"
2046 )
2047 THEN
2048 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later
2049 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
2050 WHERE "id" = "issue_row"."id";
2051 END IF;
2052 END IF;
2053 IF
2054 "issue_row"."half_frozen" NOTNULL AND
2055 "issue_row"."fully_frozen" ISNULL AND
2056 now() >= "issue_row"."half_frozen" + "policy_row"."verification_time"
2057 THEN
2058 "issue_row"."fully_frozen" = now(); -- NOTE: "issue_row" used later
2059 PERFORM "freeze_after_snapshot"("issue_id_p");
2060 END IF;
2061 IF
2062 "issue_row"."fully_frozen" NOTNULL AND
2063 now() >= "issue_row"."fully_frozen" + "policy_row"."voting_time"
2064 THEN
2065 PERFORM "close_voting"("issue_id_p");
2066 END IF;
2067 END IF;
2068 RETURN;
2069 END;
2070 $$;
2071
2072 COMMENT ON FUNCTION "check_issue"
2073 ( "issue"."id"%TYPE )
2074 IS 'Precalculate supporter counts etc. for a given issue, and check, if status change is required; At end of voting the ranking is not calculated by this function, but must be calculated in a seperate transaction using the "calculate_ranks" function.';
2075
2076
2077 CREATE FUNCTION "check_everything"()
2078 RETURNS VOID
2079 LANGUAGE 'plpgsql' VOLATILE AS $$
2080 DECLARE
2081 "issue_id_v" "issue"."id"%TYPE;
2082 BEGIN
2083 DELETE FROM "expired_session";
2084 PERFORM "calculate_member_counts"();
2085 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
2086 PERFORM "check_issue"("issue_id_v");
2087 END LOOP;
2088 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
2089 PERFORM "calculate_ranks"("issue_id_v");
2090 END LOOP;
2091 RETURN;
2092 END;
2093 $$;
2094
2095 COMMENT ON FUNCTION "check_everything"() IS 'Perform "check_issue" for every open issue, and if possible, automatically calculate ranks. Use this function only for development and debugging purposes, as long transactions with exclusive locking may result.';
2096
2097
2098
2099 COMMIT;

Impressum / About Us