liquid_feedback_core
view core.sql @ 0:8d021cb5eaf4
Version beta1
| author | jbe |
|---|---|
| date | Tue Oct 27 12:00:00 2009 +0100 (2009-10-27) |
| parents | |
| children | 23092eb00e16 |
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 "open_issue" AS
842 SELECT * FROM "issue" WHERE "closed" ISNULL;
844 COMMENT ON VIEW "open_issue" IS 'All open issues';
847 CREATE VIEW "issue_with_ranks_missing" AS
848 SELECT * FROM "issue"
849 WHERE "frozen" NOTNULL
850 AND "closed" NOTNULL
851 AND "ranks_available" = FALSE;
853 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
857 ------------------------------
858 -- Comparison by vote count --
859 ------------------------------
861 CREATE FUNCTION "vote_ratio"
862 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
863 "negative_votes_p" "initiative"."negative_votes"%TYPE )
864 RETURNS FLOAT8
865 LANGUAGE 'plpgsql' STABLE AS $$
866 DECLARE
867 "total_v" INT4;
868 BEGIN
869 "total_v" := "positive_votes_p" + "negative_votes_p";
870 IF "total_v" > 0 THEN
871 RETURN "positive_votes_p"::FLOAT8 / "total_v"::FLOAT8;
872 ELSE
873 RETURN 0.5;
874 END IF;
875 END;
876 $$;
878 COMMENT ON FUNCTION "vote_ratio"
879 ( "initiative"."positive_votes"%TYPE,
880 "initiative"."negative_votes"%TYPE )
881 IS 'Ratio of positive votes to sum of positive and negative votes; 0.5, if there are neither positive nor negative votes';
885 ------------------------------------------------
886 -- Locking for snapshots and voting procedure --
887 ------------------------------------------------
889 CREATE FUNCTION "global_lock"() RETURNS VOID
890 LANGUAGE 'plpgsql' VOLATILE AS $$
891 BEGIN
892 -- NOTE: PostgreSQL allows reading, while tables are locked in
893 -- exclusive move. Transactions should be kept short anyway!
894 LOCK TABLE "member" IN EXCLUSIVE MODE;
895 LOCK TABLE "policy" IN EXCLUSIVE MODE;
896 LOCK TABLE "area" IN EXCLUSIVE MODE;
897 LOCK TABLE "issue" IN EXCLUSIVE MODE;
898 LOCK TABLE "initiative" IN EXCLUSIVE MODE;
899 LOCK TABLE "draft" IN EXCLUSIVE MODE;
900 LOCK TABLE "suggestion" IN EXCLUSIVE MODE;
901 LOCK TABLE "membership" IN EXCLUSIVE MODE;
902 LOCK TABLE "interest" IN EXCLUSIVE MODE;
903 LOCK TABLE "initiator" IN EXCLUSIVE MODE;
904 LOCK TABLE "supporter" IN EXCLUSIVE MODE;
905 LOCK TABLE "opinion" IN EXCLUSIVE MODE;
906 LOCK TABLE "delegation" IN EXCLUSIVE MODE;
907 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
908 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
909 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
910 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
911 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
912 LOCK TABLE "direct_voter" IN EXCLUSIVE MODE;
913 LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;
914 LOCK TABLE "vote" IN EXCLUSIVE MODE;
915 RETURN;
916 END;
917 $$;
919 COMMENT ON FUNCTION "global_lock"() IS 'Locks all tables related to support/voting until end of transaction; read access is still possible though';
923 ------------------------------
924 -- Calculation of snapshots --
925 ------------------------------
927 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
928 ( "issue_id_p" "issue"."id"%TYPE,
929 "member_id_p" "member"."id"%TYPE,
930 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
931 RETURNS "direct_population_snapshot"."weight"%TYPE
932 LANGUAGE 'plpgsql' VOLATILE AS $$
933 DECLARE
934 "issue_delegation_row" "issue_delegation"%ROWTYPE;
935 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
936 "weight_v" INT4;
937 BEGIN
938 "weight_v" := 0;
939 FOR "issue_delegation_row" IN
940 SELECT * FROM "issue_delegation"
941 WHERE "trustee_id" = "member_id_p"
942 AND "issue_id" = "issue_id_p"
943 LOOP
944 IF NOT EXISTS (
945 SELECT NULL FROM "direct_population_snapshot"
946 WHERE "issue_id" = "issue_id_p"
947 AND "event" = 'periodic'
948 AND "member_id" = "issue_delegation_row"."truster_id"
949 ) AND NOT EXISTS (
950 SELECT NULL FROM "delegating_population_snapshot"
951 WHERE "issue_id" = "issue_id_p"
952 AND "event" = 'periodic'
953 AND "member_id" = "issue_delegation_row"."truster_id"
954 ) THEN
955 "delegate_member_ids_v" :=
956 "member_id_p" || "delegate_member_ids_p";
957 INSERT INTO "delegating_population_snapshot"
958 ("issue_id", "event", "member_id", "delegate_member_ids")
959 VALUES (
960 "issue_id_p",
961 'periodic',
962 "issue_delegation_row"."truster_id",
963 "delegate_member_ids_v"
964 );
965 "weight_v" := "weight_v" + 1 +
966 "weight_of_added_delegations_for_population_snapshot"(
967 "issue_id_p",
968 "issue_delegation_row"."truster_id",
969 "delegate_member_ids_v"
970 );
971 END IF;
972 END LOOP;
973 RETURN "weight_v";
974 END;
975 $$;
977 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
978 ( "issue"."id"%TYPE,
979 "member"."id"%TYPE,
980 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
981 IS 'Helper function for "create_population_snapshot" function';
984 CREATE FUNCTION "create_population_snapshot"
985 ( "issue_id_p" "issue"."id"%TYPE )
986 RETURNS VOID
987 LANGUAGE 'plpgsql' VOLATILE AS $$
988 DECLARE
989 "member_id_v" "member"."id"%TYPE;
990 BEGIN
991 DELETE FROM "direct_population_snapshot"
992 WHERE "issue_id" = "issue_id_p"
993 AND "event" = 'periodic';
994 DELETE FROM "delegating_population_snapshot"
995 WHERE "issue_id" = "issue_id_p"
996 AND "event" = 'periodic';
997 INSERT INTO "direct_population_snapshot"
998 ("issue_id", "event", "member_id", "interest_exists")
999 SELECT DISTINCT ON ("issue_id", "member_id")
1000 "issue_id_p" AS "issue_id",
1001 'periodic' AS "event",
1002 "subquery"."member_id",
1003 "subquery"."interest_exists"
1004 FROM (
1005 SELECT
1006 "member"."id" AS "member_id",
1007 FALSE AS "interest_exists"
1008 FROM "issue"
1009 JOIN "area" ON "issue"."area_id" = "area"."id"
1010 JOIN "membership" ON "area"."id" = "membership"."area_id"
1011 JOIN "member" ON "membership"."member_id" = "member"."id"
1012 WHERE "issue"."id" = "issue_id_p"
1013 AND "member"."active"
1014 UNION
1015 SELECT
1016 "member"."id" AS "member_id",
1017 TRUE AS "interest_exists"
1018 FROM "interest" JOIN "member"
1019 ON "interest"."member_id" = "member"."id"
1020 WHERE "interest"."issue_id" = "issue_id_p"
1021 AND "member"."active"
1022 ) AS "subquery"
1023 ORDER BY
1024 "issue_id_p",
1025 "subquery"."member_id",
1026 "subquery"."interest_exists" DESC;
1027 FOR "member_id_v" IN
1028 SELECT "member_id" FROM "direct_population_snapshot"
1029 WHERE "issue_id" = "issue_id_p"
1030 AND "event" = 'periodic'
1031 LOOP
1032 UPDATE "direct_population_snapshot" SET
1033 "weight" = 1 +
1034 "weight_of_added_delegations_for_population_snapshot"(
1035 "issue_id_p",
1036 "member_id_v",
1037 '{}'
1038 )
1039 WHERE "issue_id" = "issue_id_p"
1040 AND "event" = 'periodic'
1041 AND "member_id" = "member_id_v";
1042 END LOOP;
1043 RETURN;
1044 END;
1045 $$;
1047 COMMENT ON FUNCTION "create_population_snapshot"
1048 ( "issue_id_p" "issue"."id"%TYPE )
1049 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.';
1052 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
1053 ( "issue_id_p" "issue"."id"%TYPE,
1054 "member_id_p" "member"."id"%TYPE,
1055 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
1056 RETURNS "direct_interest_snapshot"."weight"%TYPE
1057 LANGUAGE 'plpgsql' VOLATILE AS $$
1058 DECLARE
1059 "issue_delegation_row" "issue_delegation"%ROWTYPE;
1060 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
1061 "weight_v" INT4;
1062 BEGIN
1063 "weight_v" := 0;
1064 FOR "issue_delegation_row" IN
1065 SELECT * FROM "issue_delegation"
1066 WHERE "trustee_id" = "member_id_p"
1067 AND "issue_id" = "issue_id_p"
1068 LOOP
1069 IF NOT EXISTS (
1070 SELECT NULL FROM "direct_interest_snapshot"
1071 WHERE "issue_id" = "issue_id_p"
1072 AND "event" = 'periodic'
1073 AND "member_id" = "issue_delegation_row"."truster_id"
1074 ) AND NOT EXISTS (
1075 SELECT NULL FROM "delegating_interest_snapshot"
1076 WHERE "issue_id" = "issue_id_p"
1077 AND "event" = 'periodic'
1078 AND "member_id" = "issue_delegation_row"."truster_id"
1079 ) THEN
1080 "delegate_member_ids_v" :=
1081 "member_id_p" || "delegate_member_ids_p";
1082 INSERT INTO "delegating_interest_snapshot"
1083 ("issue_id", "event", "member_id", "delegate_member_ids")
1084 VALUES (
1085 "issue_id_p",
1086 'periodic',
1087 "issue_delegation_row"."truster_id",
1088 "delegate_member_ids_v"
1089 );
1090 "weight_v" := "weight_v" + 1 +
1091 "weight_of_added_delegations_for_interest_snapshot"(
1092 "issue_id_p",
1093 "issue_delegation_row"."truster_id",
1094 "delegate_member_ids_v"
1095 );
1096 END IF;
1097 END LOOP;
1098 RETURN "weight_v";
1099 END;
1100 $$;
1102 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
1103 ( "issue"."id"%TYPE,
1104 "member"."id"%TYPE,
1105 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
1106 IS 'Helper function for "create_interest_snapshot" function';
1109 CREATE FUNCTION "create_interest_snapshot"
1110 ( "issue_id_p" "issue"."id"%TYPE )
1111 RETURNS VOID
1112 LANGUAGE 'plpgsql' VOLATILE AS $$
1113 DECLARE
1114 "member_id_v" "member"."id"%TYPE;
1115 BEGIN
1116 DELETE FROM "direct_interest_snapshot"
1117 WHERE "issue_id" = "issue_id_p"
1118 AND "event" = 'periodic';
1119 DELETE FROM "delegating_interest_snapshot"
1120 WHERE "issue_id" = "issue_id_p"
1121 AND "event" = 'periodic';
1122 DELETE FROM "direct_supporter_snapshot"
1123 WHERE "issue_id" = "issue_id_p"
1124 AND "event" = 'periodic';
1125 INSERT INTO "direct_interest_snapshot"
1126 ("issue_id", "event", "member_id", "voting_requested")
1127 SELECT
1128 "issue_id_p" AS "issue_id",
1129 'periodic' AS "event",
1130 "member"."id" AS "member_id",
1131 "interest"."voting_requested"
1132 FROM "interest" JOIN "member"
1133 ON "interest"."member_id" = "member"."id"
1134 WHERE "interest"."issue_id" = "issue_id_p"
1135 AND "member"."active";
1136 FOR "member_id_v" IN
1137 SELECT "member_id" FROM "direct_interest_snapshot"
1138 WHERE "issue_id" = "issue_id_p"
1139 AND "event" = 'periodic'
1140 LOOP
1141 UPDATE "direct_interest_snapshot" SET
1142 "weight" = 1 +
1143 "weight_of_added_delegations_for_interest_snapshot"(
1144 "issue_id_p",
1145 "member_id_v",
1146 '{}'
1147 )
1148 WHERE "issue_id" = "issue_id_p"
1149 AND "event" = 'periodic'
1150 AND "member_id" = "member_id_v";
1151 END LOOP;
1152 INSERT INTO "direct_supporter_snapshot"
1153 ( "issue_id", "initiative_id", "event", "member_id",
1154 "informed", "satisfied" )
1155 SELECT
1156 "issue_id_p" AS "issue_id",
1157 "initiative"."id" AS "initiative_id",
1158 'periodic' AS "event",
1159 "member"."id" AS "member_id",
1160 "supporter"."draft_id" = "current_draft"."id" AS "informed",
1161 NOT EXISTS (
1162 SELECT NULL FROM "critical_opinion"
1163 WHERE "initiative_id" = "initiative"."id"
1164 AND "member_id" = "member"."id"
1165 ) AS "satisfied"
1166 FROM "supporter"
1167 JOIN "member"
1168 ON "supporter"."member_id" = "member"."id"
1169 JOIN "initiative"
1170 ON "supporter"."initiative_id" = "initiative"."id"
1171 JOIN "current_draft"
1172 ON "initiative"."id" = "current_draft"."initiative_id"
1173 JOIN "direct_interest_snapshot"
1174 ON "member"."id" = "direct_interest_snapshot"."member_id"
1175 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
1176 WHERE "member"."active"
1177 AND "initiative"."issue_id" = "issue_id_p";
1178 RETURN;
1179 END;
1180 $$;
1182 COMMENT ON FUNCTION "create_interest_snapshot"
1183 ( "issue"."id"%TYPE )
1184 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.';
1187 CREATE FUNCTION "create_snapshot"
1188 ( "issue_id_p" "issue"."id"%TYPE )
1189 RETURNS VOID
1190 LANGUAGE 'plpgsql' VOLATILE AS $$
1191 DECLARE
1192 "initiative_id_v" "initiative"."id"%TYPE;
1193 "suggestion_id_v" "suggestion"."id"%TYPE;
1194 BEGIN
1195 PERFORM "global_lock"();
1196 PERFORM "create_population_snapshot"("issue_id_p");
1197 PERFORM "create_interest_snapshot"("issue_id_p");
1198 UPDATE "issue" SET
1199 "snapshot" = now(),
1200 "population" = (
1201 SELECT coalesce(sum("weight"), 0)
1202 FROM "direct_population_snapshot"
1203 WHERE "issue_id" = "issue_id_p"
1204 AND "event" = 'periodic'
1205 ),
1206 "vote_now" = (
1207 SELECT coalesce(sum("weight"), 0)
1208 FROM "direct_interest_snapshot"
1209 WHERE "issue_id" = "issue_id_p"
1210 AND "event" = 'periodic'
1211 AND "voting_requested" = TRUE
1212 ),
1213 "vote_later" = (
1214 SELECT coalesce(sum("weight"), 0)
1215 FROM "direct_interest_snapshot"
1216 WHERE "issue_id" = "issue_id_p"
1217 AND "event" = 'periodic'
1218 AND "voting_requested" = FALSE
1219 )
1220 WHERE "id" = "issue_id_p";
1221 FOR "initiative_id_v" IN
1222 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
1223 LOOP
1224 UPDATE "initiative" SET
1225 "supporter_count" = (
1226 SELECT coalesce(sum("di"."weight"), 0)
1227 FROM "direct_interest_snapshot" AS "di"
1228 JOIN "direct_supporter_snapshot" AS "ds"
1229 ON "di"."member_id" = "ds"."member_id"
1230 WHERE "di"."issue_id" = "issue_id_p"
1231 AND "di"."event" = 'periodic'
1232 AND "ds"."initiative_id" = "initiative_id_v"
1233 AND "ds"."event" = 'periodic'
1234 ),
1235 "informed_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 AND "ds"."informed"
1245 ),
1246 "satisfied_supporter_count" = (
1247 SELECT coalesce(sum("di"."weight"), 0)
1248 FROM "direct_interest_snapshot" AS "di"
1249 JOIN "direct_supporter_snapshot" AS "ds"
1250 ON "di"."member_id" = "ds"."member_id"
1251 WHERE "di"."issue_id" = "issue_id_p"
1252 AND "di"."event" = 'periodic'
1253 AND "ds"."initiative_id" = "initiative_id_v"
1254 AND "ds"."event" = 'periodic'
1255 AND "ds"."satisfied"
1256 ),
1257 "satisfied_informed_supporter_count" = (
1258 SELECT coalesce(sum("di"."weight"), 0)
1259 FROM "direct_interest_snapshot" AS "di"
1260 JOIN "direct_supporter_snapshot" AS "ds"
1261 ON "di"."member_id" = "ds"."member_id"
1262 WHERE "di"."issue_id" = "issue_id_p"
1263 AND "di"."event" = 'periodic'
1264 AND "ds"."initiative_id" = "initiative_id_v"
1265 AND "ds"."event" = 'periodic'
1266 AND "ds"."informed"
1267 AND "ds"."satisfied"
1268 )
1269 WHERE "id" = "initiative_id_v";
1270 FOR "suggestion_id_v" IN
1271 SELECT "id" FROM "suggestion"
1272 WHERE "initiative_id" = "initiative_id_v"
1273 LOOP
1274 UPDATE "suggestion" SET
1275 "minus2_unfulfilled_count" = (
1276 SELECT coalesce(sum("snapshot"."weight"), 0)
1277 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
1278 ON "opinion"."member_id" = "snapshot"."member_id"
1279 WHERE "opinion"."initiative_id" = "initiative_id_v"
1280 AND "snapshot"."issue_id" = "issue_id_p"
1281 AND "opinion"."degree" = -2
1282 AND "opinion"."fulfilled" = FALSE
1283 ),
1284 "minus2_fulfilled_count" = (
1285 SELECT coalesce(sum("snapshot"."weight"), 0)
1286 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
1287 ON "opinion"."member_id" = "snapshot"."member_id"
1288 WHERE "opinion"."initiative_id" = "initiative_id_v"
1289 AND "snapshot"."issue_id" = "issue_id_p"
1290 AND "opinion"."degree" = -2
1291 AND "opinion"."fulfilled" = TRUE
1292 ),
1293 "minus1_unfulfilled_count" = (
1294 SELECT coalesce(sum("snapshot"."weight"), 0)
1295 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
1296 ON "opinion"."member_id" = "snapshot"."member_id"
1297 WHERE "opinion"."initiative_id" = "initiative_id_v"
1298 AND "snapshot"."issue_id" = "issue_id_p"
1299 AND "opinion"."degree" = -1
1300 AND "opinion"."fulfilled" = FALSE
1301 ),
1302 "minus1_fulfilled_count" = (
1303 SELECT coalesce(sum("snapshot"."weight"), 0)
1304 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
1305 ON "opinion"."member_id" = "snapshot"."member_id"
1306 WHERE "opinion"."initiative_id" = "initiative_id_v"
1307 AND "snapshot"."issue_id" = "issue_id_p"
1308 AND "opinion"."degree" = -1
1309 AND "opinion"."fulfilled" = TRUE
1310 ),
1311 "plus1_unfulfilled_count" = (
1312 SELECT coalesce(sum("snapshot"."weight"), 0)
1313 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
1314 ON "opinion"."member_id" = "snapshot"."member_id"
1315 WHERE "opinion"."initiative_id" = "initiative_id_v"
1316 AND "snapshot"."issue_id" = "issue_id_p"
1317 AND "opinion"."degree" = 1
1318 AND "opinion"."fulfilled" = FALSE
1319 ),
1320 "plus1_fulfilled_count" = (
1321 SELECT coalesce(sum("snapshot"."weight"), 0)
1322 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
1323 ON "opinion"."member_id" = "snapshot"."member_id"
1324 WHERE "opinion"."initiative_id" = "initiative_id_v"
1325 AND "snapshot"."issue_id" = "issue_id_p"
1326 AND "opinion"."degree" = 1
1327 AND "opinion"."fulfilled" = TRUE
1328 ),
1329 "plus2_unfulfilled_count" = (
1330 SELECT coalesce(sum("snapshot"."weight"), 0)
1331 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
1332 ON "opinion"."member_id" = "snapshot"."member_id"
1333 WHERE "opinion"."initiative_id" = "initiative_id_v"
1334 AND "snapshot"."issue_id" = "issue_id_p"
1335 AND "opinion"."degree" = 2
1336 AND "opinion"."fulfilled" = FALSE
1337 ),
1338 "plus2_fulfilled_count" = (
1339 SELECT coalesce(sum("snapshot"."weight"), 0)
1340 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
1341 ON "opinion"."member_id" = "snapshot"."member_id"
1342 WHERE "opinion"."initiative_id" = "initiative_id_v"
1343 AND "snapshot"."issue_id" = "issue_id_p"
1344 AND "opinion"."degree" = 2
1345 AND "opinion"."fulfilled" = TRUE
1346 )
1347 WHERE "suggestion"."id" = "suggestion_id_v";
1348 END LOOP;
1349 END LOOP;
1350 RETURN;
1351 END;
1352 $$;
1354 COMMENT ON FUNCTION "create_snapshot"
1355 ( "issue"."id"%TYPE )
1356 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.';
1359 CREATE FUNCTION "set_snapshot_event"
1360 ( "issue_id_p" "issue"."id"%TYPE,
1361 "event_p" "snapshot_event" )
1362 RETURNS VOID
1363 LANGUAGE 'plpgsql' VOLATILE AS $$
1364 BEGIN
1365 UPDATE "direct_population_snapshot"
1366 SET "event" = 'end_of_discussion'
1367 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
1368 UPDATE "delegating_population_snapshot"
1369 SET "event" = 'end_of_discussion'
1370 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
1371 UPDATE "direct_interest_snapshot"
1372 SET "event" = 'end_of_discussion'
1373 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
1374 UPDATE "delegating_interest_snapshot"
1375 SET "event" = 'end_of_discussion'
1376 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
1377 UPDATE "direct_supporter_snapshot"
1378 SET "event" = 'end_of_discussion'
1379 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
1380 RETURN;
1381 END;
1382 $$;
1384 COMMENT ON FUNCTION "set_snapshot_event"
1385 ( "issue"."id"%TYPE,
1386 "snapshot_event" )
1387 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
1391 ---------------------
1392 -- Freezing issues --
1393 ---------------------
1395 CREATE FUNCTION "freeze_after_snapshot"
1396 ( "issue_id_p" "issue"."id"%TYPE )
1397 RETURNS VOID
1398 LANGUAGE 'plpgsql' VOLATILE AS $$
1399 DECLARE
1400 "issue_row" "issue"%ROWTYPE;
1401 "policy_row" "policy"%ROWTYPE;
1402 "initiative_row" "initiative"%ROWTYPE;
1403 BEGIN
1404 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1405 SELECT * INTO "policy_row"
1406 FROM "policy" WHERE "id" = "issue_row"."policy_id";
1407 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_discussion');
1408 UPDATE "issue" SET "frozen" = now() WHERE "id" = "issue_id_p";
1409 FOR "initiative_row" IN
1410 SELECT * FROM "initiative" WHERE "issue_id" = "issue_id_p"
1411 LOOP
1412 IF
1413 "initiative_row"."satisfied_supporter_count" > 0 AND
1414 "initiative_row"."satisfied_supporter_count" *
1415 "policy_row"."initiative_quorum_den" >=
1416 "issue_row"."population" * "policy_row"."initiative_quorum_num"
1417 THEN
1418 UPDATE "initiative" SET "admitted" = TRUE
1419 WHERE "id" = "initiative_row"."id";
1420 ELSE
1421 UPDATE "initiative" SET "admitted" = FALSE
1422 WHERE "id" = "initiative_row"."id";
1423 END IF;
1424 END LOOP;
1425 RETURN;
1426 END;
1427 $$;
1429 COMMENT ON FUNCTION "freeze_after_snapshot"
1430 ( "issue"."id"%TYPE )
1431 IS 'This function freezes an issue, but must only be called when "create_snapshot" was called in the same transaction';
1434 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
1435 RETURNS VOID
1436 LANGUAGE 'plpgsql' VOLATILE AS $$
1437 DECLARE
1438 "issue_row" "issue"%ROWTYPE;
1439 BEGIN
1440 PERFORM "create_snapshot"("issue_id_p");
1441 PERFORM "freeze_after_snapshot"("issue_id_p");
1442 RETURN;
1443 END;
1444 $$;
1446 COMMENT ON FUNCTION "freeze_after_snapshot"
1447 ( "issue"."id"%TYPE )
1448 IS 'Freeze an issue manually';
1452 -----------------------
1453 -- Counting of votes --
1454 -----------------------
1457 CREATE FUNCTION "weight_of_added_delegations"
1458 ( "issue_id_p" "issue"."id"%TYPE,
1459 "member_id_p" "member"."id"%TYPE,
1460 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
1461 RETURNS "direct_voter"."weight"%TYPE
1462 LANGUAGE 'plpgsql' VOLATILE AS $$
1463 DECLARE
1464 "issue_delegation_row" "issue_delegation"%ROWTYPE;
1465 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
1466 "weight_v" INT4;
1467 BEGIN
1468 "weight_v" := 0;
1469 FOR "issue_delegation_row" IN
1470 SELECT * FROM "issue_delegation"
1471 WHERE "trustee_id" = "member_id_p"
1472 AND "issue_id" = "issue_id_p"
1473 LOOP
1474 IF NOT EXISTS (
1475 SELECT NULL FROM "direct_voter"
1476 WHERE "member_id" = "issue_delegation_row"."truster_id"
1477 AND "issue_id" = "issue_id_p"
1478 ) AND NOT EXISTS (
1479 SELECT NULL FROM "delegating_voter"
1480 WHERE "member_id" = "issue_delegation_row"."truster_id"
1481 AND "issue_id" = "issue_id_p"
1482 ) THEN
1483 "delegate_member_ids_v" :=
1484 "member_id_p" || "delegate_member_ids_p";
1485 INSERT INTO "delegating_voter"
1486 ("member_id", "issue_id", "delegate_member_ids")
1487 VALUES (
1488 "issue_delegation_row"."truster_id",
1489 "issue_id_p",
1490 "delegate_member_ids_v"
1491 );
1492 "weight_v" := "weight_v" + 1 + "weight_of_added_delegations"(
1493 "issue_id_p",
1494 "issue_delegation_row"."truster_id",
1495 "delegate_member_ids_v"
1496 );
1497 END IF;
1498 END LOOP;
1499 RETURN "weight_v";
1500 END;
1501 $$;
1503 COMMENT ON FUNCTION "weight_of_added_delegations"
1504 ( "issue"."id"%TYPE,
1505 "member"."id"%TYPE,
1506 "delegating_voter"."delegate_member_ids"%TYPE )
1507 IS 'Helper function for "add_vote_delegations" function';
1510 CREATE FUNCTION "add_vote_delegations"
1511 ( "issue_id_p" "issue"."id"%TYPE )
1512 RETURNS VOID
1513 LANGUAGE 'plpgsql' VOLATILE AS $$
1514 DECLARE
1515 "member_id_v" "member"."id"%TYPE;
1516 BEGIN
1517 FOR "member_id_v" IN
1518 SELECT "member_id" FROM "direct_voter"
1519 WHERE "issue_id" = "issue_id_p"
1520 LOOP
1521 UPDATE "direct_voter" SET
1522 "weight" = "weight" + "weight_of_added_delegations"(
1523 "issue_id_p",
1524 "member_id_v",
1525 '{}'
1526 )
1527 WHERE "member_id" = "member_id_v"
1528 AND "issue_id" = "issue_id_p";
1529 END LOOP;
1530 RETURN;
1531 END;
1532 $$;
1534 COMMENT ON FUNCTION "add_vote_delegations"
1535 ( "issue_id_p" "issue"."id"%TYPE )
1536 IS 'Helper function for "close_voting" function';
1539 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
1540 RETURNS VOID
1541 LANGUAGE 'plpgsql' VOLATILE AS $$
1542 DECLARE
1543 "issue_row" "issue"%ROWTYPE;
1544 "member_id_v" "member"."id"%TYPE;
1545 BEGIN
1546 PERFORM "global_lock"();
1547 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1548 DELETE FROM "delegating_voter"
1549 WHERE "issue_id" = "issue_id_p";
1550 DELETE FROM "direct_voter"
1551 WHERE "issue_id" = "issue_id_p"
1552 AND "autoreject" = TRUE;
1553 DELETE FROM "direct_voter" USING "member"
1554 WHERE "direct_voter"."member_id" = "member"."id"
1555 AND "direct_voter"."issue_id" = "issue_id_p"
1556 AND "member"."active" = FALSE;
1557 UPDATE "direct_voter" SET "weight" = 1
1558 WHERE "issue_id" = "issue_id_p";
1559 PERFORM "add_vote_delegations"("issue_id_p");
1560 FOR "member_id_v" IN
1561 SELECT "interest"."member_id"
1562 FROM "interest"
1563 LEFT JOIN "direct_voter"
1564 ON "interest"."member_id" = "direct_voter"."member_id"
1565 AND "interest"."issue_id" = "direct_voter"."issue_id"
1566 LEFT JOIN "delegating_voter"
1567 ON "interest"."member_id" = "delegating_voter"."member_id"
1568 AND "interest"."issue_id" = "delegating_voter"."issue_id"
1569 WHERE "interest"."issue_id" = "issue_id_p"
1570 AND "interest"."autoreject" = TRUE
1571 AND "direct_voter"."member_id" ISNULL
1572 AND "delegating_voter"."member_id" ISNULL
1573 UNION SELECT "membership"."member_id"
1574 FROM "membership"
1575 LEFT JOIN "interest"
1576 ON "membership"."member_id" = "interest"."member_id"
1577 AND "interest"."issue_id" = "issue_id_p"
1578 LEFT JOIN "direct_voter"
1579 ON "membership"."member_id" = "direct_voter"."member_id"
1580 AND "direct_voter"."issue_id" = "issue_id_p"
1581 LEFT JOIN "delegating_voter"
1582 ON "membership"."member_id" = "delegating_voter"."member_id"
1583 AND "delegating_voter"."issue_id" = "issue_id_p"
1584 WHERE "membership"."area_id" = "issue_row"."area_id"
1585 AND "membership"."autoreject" = TRUE
1586 AND "interest"."autoreject" ISNULL
1587 AND "direct_voter"."member_id" ISNULL
1588 AND "delegating_voter"."member_id" ISNULL
1589 LOOP
1590 INSERT INTO "direct_voter" ("member_id", "issue_id", "autoreject")
1591 VALUES ("member_id_v", "issue_id_p", TRUE);
1592 INSERT INTO "vote" (
1593 "member_id",
1594 "issue_id",
1595 "initiative_id",
1596 "grade"
1597 ) SELECT
1598 "member_id_v" AS "member_id",
1599 "issue_id_p" AS "issue_id",
1600 "id" AS "initiative_id",
1601 -1 AS "grade"
1602 FROM "initiative" WHERE "issue_id" = "issue_id_p";
1603 END LOOP;
1604 PERFORM "add_vote_delegations"("issue_id_p");
1605 UPDATE "initiative" SET
1606 "positive_votes" = "subquery"."positive_votes",
1607 "negative_votes" = "subquery"."negative_votes"
1608 FROM (
1609 SELECT
1610 "initiative_id",
1611 sum(
1612 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
1613 ) AS "positive_votes",
1614 sum (
1615 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
1616 ) AS "negative_votes"
1617 FROM "vote" JOIN "direct_voter"
1618 ON "vote"."member_id" = "direct_voter"."member_id"
1619 AND "vote"."issue_id" = "direct_voter"."issue_id"
1620 WHERE "vote"."issue_id" = "issue_id_p"
1621 GROUP BY "initiative_id"
1622 ) AS "subquery"
1623 WHERE "initiative"."admitted"
1624 AND "initiative"."id" = "subquery"."initiative_id";
1625 UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p";
1626 END;
1627 $$;
1629 COMMENT ON FUNCTION "close_voting"
1630 ( "issue"."id"%TYPE )
1631 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.';
1634 CREATE FUNCTION "init_array"("dim_p" INTEGER)
1635 RETURNS INT4[]
1636 LANGUAGE 'plpgsql' IMMUTABLE AS $$
1637 DECLARE
1638 "i" INTEGER;
1639 "ary_text_v" TEXT;
1640 BEGIN
1641 IF "dim_p" >= 1 THEN
1642 "ary_text_v" := '{NULL';
1643 "i" := "dim_p";
1644 LOOP
1645 "i" := "i" - 1;
1646 EXIT WHEN "i" = 0;
1647 "ary_text_v" := "ary_text_v" || ',NULL';
1648 END LOOP;
1649 "ary_text_v" := "ary_text_v" || '}';
1650 RETURN "ary_text_v"::INT4[][];
1651 ELSE
1652 RAISE EXCEPTION 'Dimension needs to be at least 1.';
1653 END IF;
1654 END;
1655 $$;
1657 COMMENT ON FUNCTION "init_array"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
1660 CREATE FUNCTION "init_square_matrix"("dim_p" INTEGER)
1661 RETURNS INT4[][]
1662 LANGUAGE 'plpgsql' IMMUTABLE AS $$
1663 DECLARE
1664 "i" INTEGER;
1665 "row_text_v" TEXT;
1666 "ary_text_v" TEXT;
1667 BEGIN
1668 IF "dim_p" >= 1 THEN
1669 "row_text_v" := '{NULL';
1670 "i" := "dim_p";
1671 LOOP
1672 "i" := "i" - 1;
1673 EXIT WHEN "i" = 0;
1674 "row_text_v" := "row_text_v" || ',NULL';
1675 END LOOP;
1676 "row_text_v" := "row_text_v" || '}';
1677 "ary_text_v" := '{' || "row_text_v";
1678 "i" := "dim_p";
1679 LOOP
1680 "i" := "i" - 1;
1681 EXIT WHEN "i" = 0;
1682 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
1683 END LOOP;
1684 "ary_text_v" := "ary_text_v" || '}';
1685 RETURN "ary_text_v"::INT4[][];
1686 ELSE
1687 RAISE EXCEPTION 'Dimension needs to be at least 1.';
1688 END IF;
1689 END;
1690 $$;
1692 COMMENT ON FUNCTION "init_square_matrix"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
1695 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
1696 RETURNS VOID
1697 LANGUAGE 'plpgsql' VOLATILE AS $$
1698 DECLARE
1699 "dimension_v" INTEGER;
1700 "matrix" INT4[][];
1701 "i" INTEGER;
1702 "j" INTEGER;
1703 "k" INTEGER;
1704 "battle_row" "battle"%ROWTYPE;
1705 "rank_ary" INT4[];
1706 "rank_v" INT4;
1707 "done_v" INTEGER;
1708 "winners_ary" INTEGER[];
1709 "initiative_id_v" "initiative"."id"%TYPE;
1710 BEGIN
1711 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
1712 -- Prepare matrix for Schulze-Method:
1713 SELECT count(1) INTO "dimension_v"
1714 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
1715 IF "dimension_v" = 1 THEN
1716 UPDATE "initiative" SET
1717 "rank" = 1
1718 FROM "battle_participant"
1719 WHERE "initiative"."issue_id" = "issue_id_p"
1720 AND "initiative"."id" = "battle_participant"."initiative_id";
1721 ELSIF "dimension_v" > 1 THEN
1722 "matrix" := "init_square_matrix"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
1723 "i" := 1;
1724 "j" := 2;
1725 -- Fill matrix with data from "battle" view
1726 FOR "battle_row" IN
1727 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
1728 ORDER BY "winning_initiative_id", "losing_initiative_id"
1729 LOOP
1730 "matrix"["i"]["j"] := "battle_row"."count";
1731 IF "j" = "dimension_v" THEN
1732 "i" := "i" + 1;
1733 "j" := 1;
1734 ELSE
1735 "j" := "j" + 1;
1736 IF "j" = "i" THEN
1737 "j" := "j" + 1;
1738 END IF;
1739 END IF;
1740 END LOOP;
1741 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
1742 RAISE EXCEPTION 'Wrong battle count (should not happen)';
1743 END IF;
1744 -- Delete losers from matrix:
1745 "i" := 1;
1746 LOOP
1747 "j" := "i" + 1;
1748 LOOP
1749 IF "i" != "j" THEN
1750 IF "matrix"["i"]["j"] < "matrix"["j"]["i"] THEN
1751 "matrix"["i"]["j"] := 0;
1752 ELSIF matrix[j][i] < matrix[i][j] THEN
1753 "matrix"["j"]["i"] := 0;
1754 ELSE
1755 "matrix"["i"]["j"] := 0;
1756 "matrix"["j"]["i"] := 0;
1757 END IF;
1758 END IF;
1759 EXIT WHEN "j" = "dimension_v";
1760 "j" := "j" + 1;
1761 END LOOP;
1762 EXIT WHEN "i" = "dimension_v" - 1;
1763 "i" := "i" + 1;
1764 END LOOP;
1765 -- Find best paths:
1766 "i" := 1;
1767 LOOP
1768 "j" := 1;
1769 LOOP
1770 IF "i" != "j" THEN
1771 "k" := 1;
1772 LOOP
1773 IF "i" != "k" AND "j" != "k" THEN
1774 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
1775 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
1776 "matrix"["j"]["k"] := "matrix"["j"]["i"];
1777 END IF;
1778 ELSE
1779 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
1780 "matrix"["j"]["k"] := "matrix"["i"]["k"];
1781 END IF;
1782 END IF;
1783 END IF;
1784 EXIT WHEN "k" = "dimension_v";
1785 "k" := "k" + 1;
1786 END LOOP;
1787 END IF;
1788 EXIT WHEN "j" = "dimension_v";
1789 "j" := "j" + 1;
1790 END LOOP;
1791 EXIT WHEN "i" = "dimension_v";
1792 "i" := "i" + 1;
1793 END LOOP;
1794 -- Determine order of winners:
1795 "rank_ary" := "init_array"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
1796 "rank_v" := 1;
1797 "done_v" := 0;
1798 LOOP
1799 "winners_ary" := '{}';
1800 "i" := 1;
1801 LOOP
1802 IF "rank_ary"["i"] ISNULL THEN
1803 "j" := 1;
1804 LOOP
1805 IF
1806 "i" != "j" AND
1807 "rank_ary"["j"] ISNULL AND
1808 "matrix"["j"]["i"] > "matrix"["i"]["j"]
1809 THEN
1810 -- someone else is better
1811 EXIT;
1812 END IF;
1813 IF "j" = "dimension_v" THEN
1814 -- noone is better
1815 "winners_ary" := "winners_ary" || "i";
1816 EXIT;
1817 END IF;
1818 "j" := "j" + 1;
1819 END LOOP;
1820 END IF;
1821 EXIT WHEN "i" = "dimension_v";
1822 "i" := "i" + 1;
1823 END LOOP;
1824 "i" := 1;
1825 LOOP
1826 "rank_ary"["winners_ary"["i"]] := "rank_v";
1827 "done_v" := "done_v" + 1;
1828 EXIT WHEN "i" = array_upper("winners_ary", 1);
1829 "i" := "i" + 1;
1830 END LOOP;
1831 EXIT WHEN "done_v" = "dimension_v";
1832 "rank_v" := "rank_v" + 1;
1833 END LOOP;
1834 -- write preliminary ranks:
1835 "i" := 1;
1836 FOR "initiative_id_v" IN
1837 SELECT "initiative"."id"
1838 FROM "initiative" JOIN "battle_participant"
1839 ON "initiative"."id" = "battle_participant"."initiative_id"
1840 WHERE "initiative"."issue_id" = "issue_id_p"
1841 ORDER BY "initiative"."id"
1842 LOOP
1843 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
1844 WHERE "id" = "initiative_id_v";
1845 "i" := "i" + 1;
1846 END LOOP;
1847 IF "i" != "dimension_v" + 1 THEN
1848 RAISE EXCEPTION 'Wrong winner count (should not happen)';
1849 END IF;
1850 -- straighten ranks (start counting with 1, no equal ranks):
1851 "rank_v" := 1;
1852 FOR "initiative_id_v" IN
1853 SELECT "id" FROM "initiative"
1854 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
1855 ORDER BY
1856 "rank",
1857 "vote_ratio"("positive_votes", "negative_votes") DESC,
1858 "id"
1859 LOOP
1860 UPDATE "initiative" SET "rank" = "rank_v"
1861 WHERE "id" = "initiative_id_v";
1862 "rank_v" := "rank_v" + 1;
1863 END LOOP;
1864 END IF;
1865 -- mark issue as finished
1866 UPDATE "issue" SET "ranks_available" = TRUE
1867 WHERE "id" = "issue_id_p";
1868 RETURN;
1869 END;
1870 $$;
1872 COMMENT ON FUNCTION "calculate_ranks"
1873 ( "issue"."id"%TYPE )
1874 IS 'Determine ranking (Votes have to be counted first)';
1878 -----------------------------
1879 -- Automatic state changes --
1880 -----------------------------
1883 CREATE FUNCTION "check_issue"
1884 ( "issue_id_p" "issue"."id"%TYPE )
1885 RETURNS VOID
1886 LANGUAGE 'plpgsql' VOLATILE AS $$
1887 DECLARE
1888 "issue_row" "issue"%ROWTYPE;
1889 "policy_row" "policy"%ROWTYPE;
1890 "voting_requested_v" BOOLEAN;
1891 BEGIN
1892 PERFORM "global_lock"();
1893 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1894 IF "issue_row"."closed" ISNULL THEN
1895 SELECT * INTO "policy_row" FROM "policy"
1896 WHERE "id" = "issue_row"."policy_id";
1897 IF "issue_row"."frozen" ISNULL THEN
1898 PERFORM "create_snapshot"("issue_id_p");
1899 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1900 END IF;
1901 IF "issue_row"."accepted" ISNULL THEN
1902 IF EXISTS (
1903 SELECT NULL FROM "initiative"
1904 WHERE "issue_id" = "issue_id_p"
1905 AND "supporter_count" > 0
1906 AND "supporter_count" * "policy_row"."issue_quorum_den"
1907 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
1908 ) THEN
1909 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later
1910 UPDATE "issue" SET "accepted" = "issue_row"."accepted"
1911 WHERE "id" = "issue_row"."id";
1912 ELSIF
1913 now() > "issue_row"."created" + "policy_row"."admission_time"
1914 THEN
1915 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
1916 UPDATE "issue" SET "closed" = now()
1917 WHERE "id" = "issue_row"."id";
1918 END IF;
1919 END IF;
1920 IF
1921 "issue_row"."accepted" NOTNULL AND
1922 "issue_row"."frozen" ISNULL
1923 THEN
1924 SELECT
1925 CASE
1926 WHEN "vote_now" * 2 > "issue_row"."population" THEN
1927 TRUE
1928 WHEN "vote_later" * 2 > "issue_row"."population" THEN
1929 FALSE
1930 ELSE NULL
1931 END
1932 INTO "voting_requested_v"
1933 FROM "issue" WHERE "id" = "issue_id_p";
1934 IF
1935 "voting_requested_v" OR (
1936 "voting_requested_v" ISNULL AND now() >
1937 "issue_row"."accepted" + "policy_row"."discussion_time"
1938 )
1939 THEN
1940 PERFORM "freeze_after_snapshot"("issue_id_p");
1941 END IF;
1942 END IF;
1943 IF
1944 "issue_row"."frozen" NOTNULL AND
1945 now() > "issue_row"."frozen" + "policy_row"."voting_time"
1946 THEN
1947 PERFORM "close_voting"("issue_id_p");
1948 END IF;
1949 END IF;
1950 RETURN;
1951 END;
1952 $$;
1954 COMMENT ON FUNCTION "check_issue"
1955 ( "issue"."id"%TYPE )
1956 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.';
1959 CREATE FUNCTION "check_everything"()
1960 RETURNS VOID
1961 LANGUAGE 'plpgsql' VOLATILE AS $$
1962 DECLARE
1963 "issue_id_v" "issue"."id"%TYPE;
1964 BEGIN
1965 FOR "issue_id_v" IN
1966 SELECT "id" FROM "issue" WHERE "closed" ISNULL
1967 LOOP
1968 PERFORM "check_issue"("issue_id_v");
1969 END LOOP;
1970 FOR "issue_id_v" IN
1971 SELECT "id" FROM "issue"
1972 WHERE "frozen" NOTNULL
1973 AND "closed" NOTNULL
1974 AND "ranks_available" = FALSE FOR UPDATE
1975 LOOP
1976 PERFORM "calculate_ranks"("issue_id_v");
1977 END LOOP;
1978 RETURN;
1979 END;
1980 $$;
1982 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.';
1986 COMMIT;