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