liquid_feedback_core
view core.sql @ 7:69d84040fb93
Version beta8
More attibutes in member table
Renamed column ident_number of member table to identification
Images of members are now stored in extra table member_image
Minor bugfix in init.sql: Added missing verification_time column
Full text index search support using PostgreSQL's TSVECTOR and TSQUERY datatypes
New function highlight(...), which helps to highlight matching words in search results
More attibutes in member table
Renamed column ident_number of member table to identification
Images of members are now stored in extra table member_image
Minor bugfix in init.sql: Added missing verification_time column
Full text index search support using PostgreSQL's TSVECTOR and TSQUERY datatypes
New function highlight(...), which helps to highlight matching words in search results
author | jbe |
---|---|
date | Mon Nov 23 12:00:00 2009 +0100 (2009-11-23) |
parents | 3ea7a72ed7e7 |
children | e6faf5ff83af |
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;
8 CREATE VIEW "liquid_feedback_version" AS
9 SELECT * FROM (VALUES ('beta8', NULL, NULL, NULL))
10 AS "subquery"("string", "major", "minor", "revision");
14 ----------------------
15 -- Full text search --
16 ----------------------
19 CREATE FUNCTION "text_search_query"("query_text_p" TEXT)
20 RETURNS TSQUERY
21 LANGUAGE 'plpgsql' IMMUTABLE AS $$
22 BEGIN
23 RETURN plainto_tsquery('pg_catalog.simple', "query_text_p");
24 END;
25 $$;
27 COMMENT ON FUNCTION "text_search_query"(TEXT) IS 'Usage: WHERE "text_search_data" @@ "text_search_query"(''<user query>'')';
30 CREATE FUNCTION "highlight"
31 ( "body_p" TEXT,
32 "query_text_p" TEXT )
33 RETURNS TEXT
34 LANGUAGE 'plpgsql' IMMUTABLE AS $$
35 BEGIN
36 RETURN ts_headline(
37 'pg_catalog.simple',
38 replace(replace("body_p", '\\', '\\\\'), '*', '\\*'),
39 "text_search_query"("query_text_p"),
40 'StartSel=* StopSel=* HighlightAll=TRUE' );
41 END;
42 $$;
44 COMMENT ON FUNCTION "highlight"
45 ( "body_p" TEXT,
46 "query_text_p" TEXT )
47 IS 'For a given a user query this function encapsulates all matches with asterisks. Asterisks and backslashes being already present are preceeded with one extra backslash.';
51 -------------------------
52 -- Tables and indicies --
53 -------------------------
55 CREATE TABLE "member" (
56 "id" SERIAL4 PRIMARY KEY,
57 "login" TEXT NOT NULL UNIQUE,
58 "password" TEXT,
59 "active" BOOLEAN NOT NULL DEFAULT TRUE,
60 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
61 "notify_email" TEXT,
62 "notify_email_confirmed" BOOLEAN,
63 "name" TEXT NOT NULL UNIQUE,
64 "identification" TEXT UNIQUE,
65 "organizational_unit" TEXT,
66 "internal_posts" TEXT,
67 "realname" TEXT,
68 "birthday" DATE,
69 "address" TEXT,
70 "email" TEXT,
71 "xmpp_address" TEXT,
72 "website" TEXT,
73 "phone" TEXT,
74 "mobile_phone" TEXT,
75 "profession" TEXT,
76 "external_memberships" TEXT,
77 "external_posts" TEXT,
78 "statement" TEXT,
79 "text_search_data" TSVECTOR,
80 CONSTRAINT "notify_email_null_check"
81 CHECK ("notify_email" NOTNULL = "notify_email_confirmed" NOTNULL) );
82 CREATE INDEX "member_active_idx" ON "member" ("active");
83 CREATE TRIGGER "update_text_search_data"
84 BEFORE INSERT OR UPDATE ON "member"
85 FOR EACH ROW EXECUTE PROCEDURE
86 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
87 "name", "identification", "organizational_unit", "internal_posts",
88 "realname", "external_memberships", "external_posts", "statement" );
90 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
92 COMMENT ON COLUMN "member"."login" IS 'Login name';
93 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
94 COMMENT ON COLUMN "member"."active" IS 'Inactive members can not login and their supports/votes are not counted by the system.';
95 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
96 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
97 COMMENT ON COLUMN "member"."notify_email_confirmed" IS 'TRUE, if "notify_email" has been confirmed';
98 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member';
99 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
100 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
101 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
102 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
103 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
104 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
105 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
106 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his homepage within the system';
109 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
111 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
115 CREATE TABLE "member_image" (
116 PRIMARY KEY ("member_id", "image_type", "scaled"),
117 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
118 "image_type" "member_image_type",
119 "scaled" BOOLEAN,
120 "content_type" TEXT,
121 "data" BYTEA NOT NULL );
123 COMMENT ON TABLE "member_image" IS 'Images of members';
125 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
128 CREATE TABLE "member_count" (
129 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
130 "total_count" INT4 NOT NULL );
132 COMMENT ON TABLE "member_count" IS 'Contains one row which contains the total count of active(!) members and a timestamp indicating when the total member count and area member counts were calculated';
134 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
135 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
138 CREATE TABLE "contact" (
139 PRIMARY KEY ("member_id", "other_member_id"),
140 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
141 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
142 "public" BOOLEAN NOT NULL DEFAULT FALSE );
144 COMMENT ON TABLE "contact" IS 'Contact lists';
146 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
147 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
148 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
151 CREATE TABLE "session" (
152 "ident" TEXT PRIMARY KEY,
153 "additional_secret" TEXT,
154 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
155 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
156 "lang" TEXT );
157 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
159 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';
161 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
162 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
163 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
164 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
167 CREATE TABLE "policy" (
168 "id" SERIAL4 PRIMARY KEY,
169 "active" BOOLEAN NOT NULL DEFAULT TRUE,
170 "name" TEXT NOT NULL UNIQUE,
171 "description" TEXT NOT NULL DEFAULT '',
172 "admission_time" INTERVAL NOT NULL,
173 "discussion_time" INTERVAL NOT NULL,
174 "verification_time" INTERVAL NOT NULL,
175 "voting_time" INTERVAL NOT NULL,
176 "issue_quorum_num" INT4 NOT NULL,
177 "issue_quorum_den" INT4 NOT NULL,
178 "initiative_quorum_num" INT4 NOT NULL,
179 "initiative_quorum_den" INT4 NOT NULL );
180 CREATE INDEX "policy_active_idx" ON "policy" ("active");
182 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
184 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
185 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"';
186 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"';
187 COMMENT ON COLUMN "policy"."verification_time" IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
188 COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "fully_frozen" but not "closed"';
189 COMMENT ON COLUMN "policy"."issue_quorum_num" IS 'Numerator of quorum to be reached by one initiative of an issue to be "accepted"';
190 COMMENT ON COLUMN "policy"."issue_quorum_den" IS 'Denominator of quorum to be reached by one initiative of an issue to be "accepted"';
191 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of quorum to be reached by an initiative to be "admitted" for voting';
192 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of quorum to be reached by an initiative to be "admitted" for voting';
195 CREATE TABLE "area" (
196 "id" SERIAL4 PRIMARY KEY,
197 "active" BOOLEAN NOT NULL DEFAULT TRUE,
198 "name" TEXT NOT NULL,
199 "description" TEXT NOT NULL DEFAULT '',
200 "direct_member_count" INT4,
201 "member_weight" INT4,
202 "autoreject_weight" INT4,
203 "text_search_data" TSVECTOR );
204 CREATE INDEX "area_active_idx" ON "area" ("active");
205 CREATE TRIGGER "update_text_search_data"
206 BEFORE INSERT OR UPDATE ON "area"
207 FOR EACH ROW EXECUTE PROCEDURE
208 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
209 "name", "description" );
211 COMMENT ON TABLE "area" IS 'Subject areas';
213 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
214 COMMENT ON COLUMN "area"."direct_member_count" IS 'Number of active members of that area (ignoring their weight), as calculated from view "area_member_count"';
215 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
216 COMMENT ON COLUMN "area"."autoreject_weight" IS 'Sum of weight of members using the autoreject feature';
219 CREATE TABLE "issue" (
220 "id" SERIAL4 PRIMARY KEY,
221 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
222 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
223 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
224 "accepted" TIMESTAMPTZ,
225 "half_frozen" TIMESTAMPTZ,
226 "fully_frozen" TIMESTAMPTZ,
227 "closed" TIMESTAMPTZ,
228 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
229 "snapshot" TIMESTAMPTZ,
230 "population" INT4,
231 "vote_now" INT4,
232 "vote_later" INT4,
233 "voter_count" INT4,
234 CONSTRAINT "valid_state" CHECK (
235 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
236 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
237 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
238 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
239 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
240 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
241 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ),
242 CONSTRAINT "state_change_order" CHECK (
243 "created" <= "accepted" AND
244 "accepted" <= "half_frozen" AND
245 "half_frozen" <= "fully_frozen" AND
246 "fully_frozen" <= "closed" ),
247 CONSTRAINT "last_snapshot_on_full_freeze" CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
248 CONSTRAINT "freeze_requires_snapshot" CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL) );
249 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
250 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
251 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
253 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
255 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
256 COMMENT ON COLUMN "issue"."half_frozen" IS 'Point in time, when "discussion_time" has elapsed, or members voted for voting';
257 COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed';
258 COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active';
259 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
260 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';
261 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
262 COMMENT ON COLUMN "issue"."vote_now" IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"';
263 COMMENT ON COLUMN "issue"."vote_later" IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"';
264 COMMENT ON COLUMN "issue"."voter_count" IS 'Total number of direct and delegating voters; This value is related to the final voting, while "population" is related to snapshots before the final voting';
267 CREATE TABLE "initiative" (
268 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
269 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
270 "id" SERIAL4 PRIMARY KEY,
271 "name" TEXT NOT NULL,
272 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
273 "revoked" TIMESTAMPTZ,
274 "admitted" BOOLEAN,
275 "supporter_count" INT4,
276 "informed_supporter_count" INT4,
277 "satisfied_supporter_count" INT4,
278 "satisfied_informed_supporter_count" INT4,
279 "positive_votes" INT4,
280 "negative_votes" INT4,
281 "rank" INT4,
282 "text_search_data" TSVECTOR,
283 CONSTRAINT "revoked_initiatives_cant_be_admitted"
284 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
285 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"
286 CHECK ("admitted" = TRUE OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "rank" ISNULL)) );
287 CREATE TRIGGER "update_text_search_data"
288 BEFORE INSERT OR UPDATE ON "initiative"
289 FOR EACH ROW EXECUTE PROCEDURE
290 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "name");
292 COMMENT ON TABLE "initiative" IS 'Group of members publishing drafts for resolutions to be passed';
294 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
295 COMMENT ON COLUMN "initiative"."admitted" IS 'True, if initiative reaches the "initiative_quorum" when freezing the issue';
296 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
297 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
298 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
299 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
300 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
301 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
302 COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';
305 CREATE TABLE "draft" (
306 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
307 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
308 "id" SERIAL8 PRIMARY KEY,
309 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
310 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
311 "content" TEXT NOT NULL,
312 "text_search_data" TSVECTOR );
313 CREATE TRIGGER "update_text_search_data"
314 BEFORE INSERT OR UPDATE ON "draft"
315 FOR EACH ROW EXECUTE PROCEDURE
316 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
318 COMMENT ON TABLE "draft" IS 'Drafts of initiatives to solve issues';
321 CREATE TABLE "suggestion" (
322 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
323 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
324 "id" SERIAL8 PRIMARY KEY,
325 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
326 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
327 "name" TEXT NOT NULL,
328 "description" TEXT NOT NULL DEFAULT '',
329 "text_search_data" TSVECTOR,
330 "minus2_unfulfilled_count" INT4,
331 "minus2_fulfilled_count" INT4,
332 "minus1_unfulfilled_count" INT4,
333 "minus1_fulfilled_count" INT4,
334 "plus1_unfulfilled_count" INT4,
335 "plus1_fulfilled_count" INT4,
336 "plus2_unfulfilled_count" INT4,
337 "plus2_fulfilled_count" INT4 );
338 CREATE TRIGGER "update_text_search_data"
339 BEFORE INSERT OR UPDATE ON "suggestion"
340 FOR EACH ROW EXECUTE PROCEDURE
341 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
342 "name", "description");
344 COMMENT ON TABLE "suggestion" IS 'Suggestions to initiators, to change the current draft';
346 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
347 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
348 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
349 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
350 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
351 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
352 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
353 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
356 CREATE TABLE "membership" (
357 PRIMARY KEY ("area_id", "member_id"),
358 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
359 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
360 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
361 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
363 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
365 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';
368 CREATE TABLE "interest" (
369 PRIMARY KEY ("issue_id", "member_id"),
370 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
371 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
372 "autoreject" BOOLEAN NOT NULL,
373 "voting_requested" BOOLEAN );
374 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
376 COMMENT ON TABLE "interest" IS 'Interest of members in a particular issue';
378 COMMENT ON COLUMN "interest"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure';
379 COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply';
382 CREATE TABLE "initiator" (
383 PRIMARY KEY ("initiative_id", "member_id"),
384 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
385 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
386 "accepted" BOOLEAN NOT NULL DEFAULT TRUE );
387 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
389 COMMENT ON TABLE "initiator" IS 'Members who are allowed to post new drafts';
391 COMMENT ON COLUMN "initiator"."accepted" IS 'If "accepted" = FALSE, then the member was invited to be a co-initiator, but has not answered yet.';
394 CREATE TABLE "supporter" (
395 "issue_id" INT4 NOT NULL,
396 PRIMARY KEY ("initiative_id", "member_id"),
397 "initiative_id" INT4,
398 "member_id" INT4,
399 "draft_id" INT8 NOT NULL,
400 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE RESTRICT ON UPDATE CASCADE,
401 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
402 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
404 COMMENT ON TABLE "supporter" IS 'Members who support an initiative (conditionally)';
406 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
409 CREATE TABLE "opinion" (
410 "initiative_id" INT4 NOT NULL,
411 PRIMARY KEY ("suggestion_id", "member_id"),
412 "suggestion_id" INT8,
413 "member_id" INT4,
414 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
415 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
416 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE,
417 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
418 CREATE INDEX "opinion_member_id_idx" ON "opinion" ("member_id");
420 COMMENT ON TABLE "opinion" IS 'Opinion on suggestions (criticism related to initiatives)';
422 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
425 CREATE TABLE "delegation" (
426 "id" SERIAL8 PRIMARY KEY,
427 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
428 "trustee_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
429 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
430 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
431 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
432 CONSTRAINT "area_id_or_issue_id_can_be_set_but_not_both" CHECK ("area_id" ISNULL OR "issue_id" ISNULL),
433 UNIQUE ("area_id", "truster_id", "trustee_id"),
434 UNIQUE ("issue_id", "truster_id", "trustee_id") );
435 CREATE UNIQUE INDEX "delegation_default_truster_id_trustee_id_unique_idx"
436 ON "delegation" ("truster_id", "trustee_id")
437 WHERE "area_id" ISNULL AND "issue_id" ISNULL;
438 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
439 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
441 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
443 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
444 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
447 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'start_of_voting');
449 COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''start_of_voting'' = saved state at end of verification period';
452 CREATE TABLE "direct_population_snapshot" (
453 PRIMARY KEY ("issue_id", "event", "member_id"),
454 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
455 "event" "snapshot_event",
456 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
457 "weight" INT4,
458 "interest_exists" BOOLEAN NOT NULL );
459 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
461 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
463 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
464 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
465 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';
468 CREATE TABLE "delegating_population_snapshot" (
469 PRIMARY KEY ("issue_id", "event", "member_id"),
470 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
471 "event" "snapshot_event",
472 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
473 "delegate_member_ids" INT4[] NOT NULL );
474 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
476 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
478 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
479 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
480 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"';
483 CREATE TABLE "direct_interest_snapshot" (
484 PRIMARY KEY ("issue_id", "event", "member_id"),
485 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
486 "event" "snapshot_event",
487 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
488 "weight" INT4,
489 "voting_requested" BOOLEAN );
490 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
492 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
494 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
495 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
496 COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';
499 CREATE TABLE "delegating_interest_snapshot" (
500 PRIMARY KEY ("issue_id", "event", "member_id"),
501 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
502 "event" "snapshot_event",
503 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
504 "delegate_member_ids" INT4[] NOT NULL );
505 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
507 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
509 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
510 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
511 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"';
514 CREATE TABLE "direct_supporter_snapshot" (
515 "issue_id" INT4 NOT NULL,
516 PRIMARY KEY ("initiative_id", "event", "member_id"),
517 "initiative_id" INT4,
518 "event" "snapshot_event",
519 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
520 "informed" BOOLEAN NOT NULL,
521 "satisfied" BOOLEAN NOT NULL,
522 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
523 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
524 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
526 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot)';
528 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
529 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
530 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
533 CREATE TABLE "direct_voter" (
534 PRIMARY KEY ("issue_id", "member_id"),
535 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
536 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
537 "weight" INT4,
538 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
539 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
541 COMMENT ON TABLE "direct_voter" IS 'Members having directly voted for/against initiatives of an issue';
543 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
544 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
547 CREATE TABLE "delegating_voter" (
548 PRIMARY KEY ("issue_id", "member_id"),
549 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
550 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
551 "delegate_member_ids" INT4[] NOT NULL );
552 CREATE INDEX "delegating_voter_member_id_idx" ON "direct_voter" ("member_id");
554 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
556 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
557 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"';
560 CREATE TABLE "vote" (
561 "issue_id" INT4 NOT NULL,
562 PRIMARY KEY ("initiative_id", "member_id"),
563 "initiative_id" INT4,
564 "member_id" INT4,
565 "grade" INT4,
566 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
567 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
568 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
570 COMMENT ON TABLE "vote" IS 'Manual and delegated votes without abstentions';
572 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.';
576 ----------------------------
577 -- Additional constraints --
578 ----------------------------
581 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
582 RETURNS TRIGGER
583 LANGUAGE 'plpgsql' VOLATILE AS $$
584 BEGIN
585 IF NOT EXISTS (
586 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
587 ) THEN
588 --RAISE 'Cannot create issue without an initial initiative.' USING
589 -- ERRCODE = 'integrity_constraint_violation',
590 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
591 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
592 END IF;
593 RETURN NULL;
594 END;
595 $$;
597 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
598 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
599 FOR EACH ROW EXECUTE PROCEDURE
600 "issue_requires_first_initiative_trigger"();
602 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
603 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
606 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
607 RETURNS TRIGGER
608 LANGUAGE 'plpgsql' VOLATILE AS $$
609 DECLARE
610 "reference_lost" BOOLEAN;
611 BEGIN
612 IF TG_OP = 'DELETE' THEN
613 "reference_lost" := TRUE;
614 ELSE
615 "reference_lost" := NEW."issue_id" != OLD."issue_id";
616 END IF;
617 IF
618 "reference_lost" AND NOT EXISTS (
619 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
620 )
621 THEN
622 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
623 END IF;
624 RETURN NULL;
625 END;
626 $$;
628 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
629 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
630 FOR EACH ROW EXECUTE PROCEDURE
631 "last_initiative_deletes_issue_trigger"();
633 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
634 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
637 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
638 RETURNS TRIGGER
639 LANGUAGE 'plpgsql' VOLATILE AS $$
640 BEGIN
641 IF NOT EXISTS (
642 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
643 ) THEN
644 --RAISE 'Cannot create initiative without an initial draft.' USING
645 -- ERRCODE = 'integrity_constraint_violation',
646 -- HINT = 'Create issue, initiative and draft within the same transaction.';
647 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
648 END IF;
649 RETURN NULL;
650 END;
651 $$;
653 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
654 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
655 FOR EACH ROW EXECUTE PROCEDURE
656 "initiative_requires_first_draft_trigger"();
658 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
659 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
662 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
663 RETURNS TRIGGER
664 LANGUAGE 'plpgsql' VOLATILE AS $$
665 DECLARE
666 "reference_lost" BOOLEAN;
667 BEGIN
668 IF TG_OP = 'DELETE' THEN
669 "reference_lost" := TRUE;
670 ELSE
671 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
672 END IF;
673 IF
674 "reference_lost" AND NOT EXISTS (
675 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
676 )
677 THEN
678 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
679 END IF;
680 RETURN NULL;
681 END;
682 $$;
684 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
685 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
686 FOR EACH ROW EXECUTE PROCEDURE
687 "last_draft_deletes_initiative_trigger"();
689 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
690 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
693 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
694 RETURNS TRIGGER
695 LANGUAGE 'plpgsql' VOLATILE AS $$
696 BEGIN
697 IF NOT EXISTS (
698 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
699 ) THEN
700 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
701 END IF;
702 RETURN NULL;
703 END;
704 $$;
706 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
707 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
708 FOR EACH ROW EXECUTE PROCEDURE
709 "suggestion_requires_first_opinion_trigger"();
711 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
712 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
715 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
716 RETURNS TRIGGER
717 LANGUAGE 'plpgsql' VOLATILE AS $$
718 DECLARE
719 "reference_lost" BOOLEAN;
720 BEGIN
721 IF TG_OP = 'DELETE' THEN
722 "reference_lost" := TRUE;
723 ELSE
724 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
725 END IF;
726 IF
727 "reference_lost" AND NOT EXISTS (
728 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
729 )
730 THEN
731 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
732 END IF;
733 RETURN NULL;
734 END;
735 $$;
737 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
738 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
739 FOR EACH ROW EXECUTE PROCEDURE
740 "last_opinion_deletes_suggestion_trigger"();
742 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
743 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
747 --------------------------------------------------------------------
748 -- Auto-retrieval of fields only needed for referential integrity --
749 --------------------------------------------------------------------
751 CREATE FUNCTION "autofill_issue_id_trigger"()
752 RETURNS TRIGGER
753 LANGUAGE 'plpgsql' VOLATILE AS $$
754 BEGIN
755 IF NEW."issue_id" ISNULL THEN
756 SELECT "issue_id" INTO NEW."issue_id"
757 FROM "initiative" WHERE "id" = NEW."initiative_id";
758 END IF;
759 RETURN NEW;
760 END;
761 $$;
763 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
764 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
766 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
767 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
769 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
770 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
771 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
774 CREATE FUNCTION "autofill_initiative_id_trigger"()
775 RETURNS TRIGGER
776 LANGUAGE 'plpgsql' VOLATILE AS $$
777 BEGIN
778 IF NEW."initiative_id" ISNULL THEN
779 SELECT "initiative_id" INTO NEW."initiative_id"
780 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
781 END IF;
782 RETURN NEW;
783 END;
784 $$;
786 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
787 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
789 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
790 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
794 -----------------------------------------------------
795 -- Automatic calculation of certain default values --
796 -----------------------------------------------------
798 CREATE FUNCTION "copy_autoreject_trigger"()
799 RETURNS TRIGGER
800 LANGUAGE 'plpgsql' VOLATILE AS $$
801 BEGIN
802 IF NEW."autoreject" ISNULL THEN
803 SELECT "membership"."autoreject" INTO NEW."autoreject"
804 FROM "issue" JOIN "membership"
805 ON "issue"."area_id" = "membership"."area_id"
806 WHERE "issue"."id" = NEW."issue_id"
807 AND "membership"."member_id" = NEW."member_id";
808 END IF;
809 IF NEW."autoreject" ISNULL THEN
810 NEW."autoreject" := FALSE;
811 END IF;
812 RETURN NEW;
813 END;
814 $$;
816 CREATE TRIGGER "copy_autoreject" BEFORE INSERT OR UPDATE ON "interest"
817 FOR EACH ROW EXECUTE PROCEDURE "copy_autoreject_trigger"();
819 COMMENT ON FUNCTION "copy_autoreject_trigger"() IS 'Implementation of trigger "copy_autoreject" on table "interest"';
820 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';
823 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
824 RETURNS TRIGGER
825 LANGUAGE 'plpgsql' VOLATILE AS $$
826 BEGIN
827 IF NEW."draft_id" ISNULL THEN
828 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
829 WHERE "initiative_id" = NEW."initiative_id";
830 END IF;
831 RETURN NEW;
832 END;
833 $$;
835 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
836 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
838 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
839 COMMENT ON TRIGGER "default_for_draft_id" ON "supporter" IS 'If "draft_id" is NULL, then use the current draft of the initiative as default';
843 ----------------------------------------
844 -- Automatic creation of dependencies --
845 ----------------------------------------
847 CREATE FUNCTION "autocreate_interest_trigger"()
848 RETURNS TRIGGER
849 LANGUAGE 'plpgsql' VOLATILE AS $$
850 BEGIN
851 IF NOT EXISTS (
852 SELECT NULL FROM "initiative" JOIN "interest"
853 ON "initiative"."issue_id" = "interest"."issue_id"
854 WHERE "initiative"."id" = NEW."initiative_id"
855 AND "interest"."member_id" = NEW."member_id"
856 ) THEN
857 BEGIN
858 INSERT INTO "interest" ("issue_id", "member_id")
859 SELECT "issue_id", NEW."member_id"
860 FROM "initiative" WHERE "id" = NEW."initiative_id";
861 EXCEPTION WHEN unique_violation THEN END;
862 END IF;
863 RETURN NEW;
864 END;
865 $$;
867 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
868 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
870 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
871 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';
874 CREATE FUNCTION "autocreate_supporter_trigger"()
875 RETURNS TRIGGER
876 LANGUAGE 'plpgsql' VOLATILE AS $$
877 BEGIN
878 IF NOT EXISTS (
879 SELECT NULL FROM "suggestion" JOIN "supporter"
880 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
881 WHERE "suggestion"."id" = NEW."suggestion_id"
882 AND "supporter"."member_id" = NEW."member_id"
883 ) THEN
884 BEGIN
885 INSERT INTO "supporter" ("initiative_id", "member_id")
886 SELECT "initiative_id", NEW."member_id"
887 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
888 EXCEPTION WHEN unique_violation THEN END;
889 END IF;
890 RETURN NEW;
891 END;
892 $$;
894 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
895 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
897 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
898 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.';
902 ------------------------------------------
903 -- Views and helper functions for views --
904 ------------------------------------------
907 CREATE TYPE "delegation_scope" AS ENUM
908 ('global', 'area', 'issue');
910 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''global'', ''area'', or ''issue''';
913 CREATE VIEW "global_delegation" AS
914 SELECT
915 "delegation"."id",
916 "delegation"."truster_id",
917 "delegation"."trustee_id"
918 FROM "delegation" JOIN "member"
919 ON "delegation"."trustee_id" = "member"."id"
920 WHERE "delegation"."area_id" ISNULL
921 AND "delegation"."issue_id" ISNULL
922 AND "member"."active";
924 COMMENT ON VIEW "global_delegation" IS 'Global delegations to active members';
927 CREATE VIEW "area_delegation" AS
928 SELECT "subquery".* FROM (
929 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
930 "area"."id" AS "area_id",
931 "delegation"."id" AS "id",
932 "delegation"."truster_id" AS "truster_id",
933 "delegation"."trustee_id" AS "trustee_id",
934 CASE WHEN "delegation"."area_id" ISNULL THEN
935 'global'::"delegation_scope"
936 ELSE
937 'area'::"delegation_scope"
938 END AS "scope"
939 FROM "area" JOIN "delegation"
940 ON ("delegation"."area_id" ISNULL OR "delegation"."area_id" = "area"."id")
941 AND "delegation"."issue_id" ISNULL
942 ORDER BY
943 "area"."id",
944 "delegation"."truster_id",
945 "delegation"."area_id" NULLS LAST
946 ) AS "subquery"
947 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
948 WHERE "member"."active";
950 COMMENT ON VIEW "area_delegation" IS 'Active delegations for areas';
953 CREATE VIEW "issue_delegation" AS
954 SELECT "subquery".* FROM (
955 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
956 "issue"."id" AS "issue_id",
957 "delegation"."id" AS "id",
958 "delegation"."truster_id" AS "truster_id",
959 "delegation"."trustee_id" AS "trustee_id",
960 CASE
961 WHEN
962 "delegation"."area_id" ISNULL AND
963 "delegation"."issue_id" ISNULL
964 THEN 'global'::"delegation_scope"
965 WHEN
966 "delegation"."area_id" NOTNULL
967 THEN 'area'::"delegation_scope"
968 ELSE 'issue'::"delegation_scope"
969 END AS "scope"
970 FROM "issue" JOIN "delegation"
971 ON (
972 "delegation"."area_id" ISNULL OR
973 "delegation"."area_id" = "issue"."area_id"
974 ) AND (
975 "delegation"."issue_id" ISNULL OR
976 "delegation"."issue_id" = "issue"."id"
977 )
978 ORDER BY
979 "issue"."id",
980 "delegation"."truster_id",
981 "delegation"."issue_id" NULLS LAST,
982 "delegation"."area_id" NULLS LAST
983 ) AS "subquery"
984 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
985 WHERE "member"."active";
987 COMMENT ON VIEW "issue_delegation" IS 'Active delegations for issues';
990 CREATE FUNCTION "membership_weight_with_skipping"
991 ( "area_id_p" "area"."id"%TYPE,
992 "member_id_p" "member"."id"%TYPE,
993 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
994 RETURNS INT4
995 LANGUAGE 'plpgsql' STABLE AS $$
996 DECLARE
997 "sum_v" INT4;
998 "delegation_row" "area_delegation"%ROWTYPE;
999 BEGIN
1000 "sum_v" := 1;
1001 FOR "delegation_row" IN
1002 SELECT "area_delegation".*
1003 FROM "area_delegation" LEFT JOIN "membership"
1004 ON "membership"."area_id" = "area_id_p"
1005 AND "membership"."member_id" = "area_delegation"."truster_id"
1006 WHERE "area_delegation"."area_id" = "area_id_p"
1007 AND "area_delegation"."trustee_id" = "member_id_p"
1008 AND "membership"."member_id" ISNULL
1009 LOOP
1010 IF NOT
1011 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1012 THEN
1013 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1014 "area_id_p",
1015 "delegation_row"."truster_id",
1016 "skip_member_ids_p" || "delegation_row"."truster_id"
1017 );
1018 END IF;
1019 END LOOP;
1020 RETURN "sum_v";
1021 END;
1022 $$;
1024 CREATE FUNCTION "membership_weight"
1025 ( "area_id_p" "area"."id"%TYPE,
1026 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1027 RETURNS INT4
1028 LANGUAGE 'plpgsql' STABLE AS $$
1029 BEGIN
1030 RETURN "membership_weight_with_skipping"(
1031 "area_id_p",
1032 "member_id_p",
1033 ARRAY["member_id_p"]
1034 );
1035 END;
1036 $$;
1039 CREATE VIEW "member_count_view" AS
1040 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1042 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1045 CREATE VIEW "area_member_count" AS
1046 SELECT
1047 "area"."id" AS "area_id",
1048 count("member"."id") AS "direct_member_count",
1049 coalesce(
1050 sum(
1051 CASE WHEN "member"."id" NOTNULL THEN
1052 "membership_weight"("area"."id", "member"."id")
1053 ELSE 0 END
1054 )
1055 ) AS "member_weight",
1056 coalesce(
1057 sum(
1058 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
1059 "membership_weight"("area"."id", "member"."id")
1060 ELSE 0 END
1061 )
1062 ) AS "autoreject_weight"
1063 FROM "area"
1064 LEFT JOIN "membership"
1065 ON "area"."id" = "membership"."area_id"
1066 LEFT JOIN "member"
1067 ON "membership"."member_id" = "member"."id"
1068 AND "member"."active"
1069 GROUP BY "area"."id";
1070 -- TODO: count delegations
1072 COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"';
1075 CREATE VIEW "current_draft" AS
1076 SELECT "draft".* FROM (
1077 SELECT
1078 "initiative"."id" AS "initiative_id",
1079 max("draft"."id") AS "draft_id"
1080 FROM "initiative" JOIN "draft"
1081 ON "initiative"."id" = "draft"."initiative_id"
1082 GROUP BY "initiative"."id"
1083 ) AS "subquery"
1084 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1086 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1089 CREATE VIEW "critical_opinion" AS
1090 SELECT * FROM "opinion"
1091 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
1092 OR ("degree" = -2 AND "fulfilled" = TRUE);
1094 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
1097 CREATE VIEW "battle_participant" AS
1098 SELECT "issue_id", "id" AS "initiative_id" FROM "initiative"
1099 WHERE "admitted"
1100 AND "positive_votes" > "negative_votes";
1102 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle" view';
1105 CREATE VIEW "battle" AS
1106 SELECT
1107 "issue"."id" AS "issue_id",
1108 "winning_initiative"."initiative_id" AS "winning_initiative_id",
1109 "losing_initiative"."initiative_id" AS "losing_initiative_id",
1110 sum(
1111 CASE WHEN
1112 coalesce("better_vote"."grade", 0) >
1113 coalesce("worse_vote"."grade", 0)
1114 THEN "direct_voter"."weight" ELSE 0 END
1115 ) AS "count"
1116 FROM "issue"
1117 LEFT JOIN "direct_voter"
1118 ON "issue"."id" = "direct_voter"."issue_id"
1119 JOIN "battle_participant" AS "winning_initiative"
1120 ON "issue"."id" = "winning_initiative"."issue_id"
1121 JOIN "battle_participant" AS "losing_initiative"
1122 ON "issue"."id" = "losing_initiative"."issue_id"
1123 LEFT JOIN "vote" AS "better_vote"
1124 ON "direct_voter"."member_id" = "better_vote"."member_id"
1125 AND "winning_initiative"."initiative_id" = "better_vote"."initiative_id"
1126 LEFT JOIN "vote" AS "worse_vote"
1127 ON "direct_voter"."member_id" = "worse_vote"."member_id"
1128 AND "losing_initiative"."initiative_id" = "worse_vote"."initiative_id"
1129 WHERE
1130 "winning_initiative"."initiative_id" !=
1131 "losing_initiative"."initiative_id"
1132 GROUP BY
1133 "issue"."id",
1134 "winning_initiative"."initiative_id",
1135 "losing_initiative"."initiative_id";
1137 COMMENT ON VIEW "battle" IS 'Number of members preferring one initiative over another';
1140 CREATE VIEW "expired_session" AS
1141 SELECT * FROM "session" WHERE now() > "expiry";
1143 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
1144 DELETE FROM "session" WHERE "ident" = OLD."ident";
1146 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
1147 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
1150 CREATE VIEW "open_issue" AS
1151 SELECT * FROM "issue" WHERE "closed" ISNULL;
1153 COMMENT ON VIEW "open_issue" IS 'All open issues';
1156 CREATE VIEW "issue_with_ranks_missing" AS
1157 SELECT * FROM "issue"
1158 WHERE "fully_frozen" NOTNULL
1159 AND "closed" NOTNULL
1160 AND "ranks_available" = FALSE;
1162 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
1166 --------------------------------------------------
1167 -- Set returning function for delegation chains --
1168 --------------------------------------------------
1171 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
1172 ('first', 'intermediate', 'last', 'repetition');
1174 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
1177 CREATE TYPE "delegation_chain_row" AS (
1178 "index" INT4,
1179 "member_id" INT4,
1180 "member_active" BOOLEAN,
1181 "participation" BOOLEAN,
1182 "overridden" BOOLEAN,
1183 "scope_in" "delegation_scope",
1184 "scope_out" "delegation_scope",
1185 "loop" "delegation_chain_loop_tag" );
1187 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
1189 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
1190 COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest, for areas: membership, for global delegation chains: always null';
1191 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
1192 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
1193 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
1194 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
1197 CREATE FUNCTION "delegation_chain"
1198 ( "member_id_p" "member"."id"%TYPE,
1199 "area_id_p" "area"."id"%TYPE,
1200 "issue_id_p" "issue"."id"%TYPE,
1201 "simulate_trustee_id_p" "member"."id"%TYPE )
1202 RETURNS SETOF "delegation_chain_row"
1203 LANGUAGE 'plpgsql' STABLE AS $$
1204 DECLARE
1205 "issue_row" "issue"%ROWTYPE;
1206 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
1207 "loop_member_id_v" "member"."id"%TYPE;
1208 "output_row" "delegation_chain_row";
1209 "output_rows" "delegation_chain_row"[];
1210 "delegation_row" "delegation"%ROWTYPE;
1211 "row_count" INT4;
1212 "i" INT4;
1213 "loop_v" BOOLEAN;
1214 BEGIN
1215 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1216 "visited_member_ids" := '{}';
1217 "loop_member_id_v" := NULL;
1218 "output_rows" := '{}';
1219 "output_row"."index" := 0;
1220 "output_row"."member_id" := "member_id_p";
1221 "output_row"."member_active" := TRUE;
1222 "output_row"."participation" := FALSE;
1223 "output_row"."overridden" := FALSE;
1224 "output_row"."scope_out" := NULL;
1225 LOOP
1226 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
1227 "loop_member_id_v" := "output_row"."member_id";
1228 ELSE
1229 "visited_member_ids" :=
1230 "visited_member_ids" || "output_row"."member_id";
1231 END IF;
1232 IF "output_row"."participation" THEN
1233 "output_row"."overridden" := TRUE;
1234 END IF;
1235 "output_row"."scope_in" := "output_row"."scope_out";
1236 IF EXISTS (
1237 SELECT NULL FROM "member"
1238 WHERE "id" = "output_row"."member_id" AND "active"
1239 ) THEN
1240 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
1241 SELECT * INTO "delegation_row" FROM "delegation"
1242 WHERE "truster_id" = "output_row"."member_id"
1243 AND "area_id" ISNULL AND "issue_id" ISNULL;
1244 ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN
1245 "output_row"."participation" := EXISTS (
1246 SELECT NULL FROM "membership"
1247 WHERE "area_id" = "area_id_p"
1248 AND "member_id" = "output_row"."member_id"
1249 );
1250 SELECT * INTO "delegation_row" FROM "delegation"
1251 WHERE "truster_id" = "output_row"."member_id"
1252 AND ("area_id" ISNULL OR "area_id" = "area_id_p")
1253 AND "issue_id" ISNULL
1254 ORDER BY "area_id" NULLS LAST;
1255 ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN
1256 "output_row"."participation" := EXISTS (
1257 SELECT NULL FROM "interest"
1258 WHERE "issue_id" = "issue_id_p"
1259 AND "member_id" = "output_row"."member_id"
1260 );
1261 SELECT * INTO "delegation_row" FROM "delegation"
1262 WHERE "truster_id" = "output_row"."member_id"
1263 AND ("area_id" ISNULL OR "area_id" = "issue_row"."area_id")
1264 AND ("issue_id" ISNULL OR "issue_id" = "issue_id_p")
1265 ORDER BY "issue_id" NULLS LAST, "area_id" NULLS LAST;
1266 ELSE
1267 RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.';
1268 END IF;
1269 ELSE
1270 "output_row"."member_active" := FALSE;
1271 "output_row"."participation" := FALSE;
1272 "output_row"."scope_out" := NULL;
1273 "delegation_row" := ROW(NULL);
1274 END IF;
1275 IF
1276 "output_row"."member_id" = "member_id_p" AND
1277 "simulate_trustee_id_p" NOTNULL
1278 THEN
1279 "output_row"."scope_out" := CASE
1280 WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global'
1281 WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area'
1282 WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue'
1283 END;
1284 "output_rows" := "output_rows" || "output_row";
1285 "output_row"."member_id" := "simulate_trustee_id_p";
1286 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
1287 "output_row"."scope_out" := CASE
1288 WHEN
1289 "delegation_row"."area_id" ISNULL AND
1290 "delegation_row"."issue_id" ISNULL
1291 THEN 'global'
1292 WHEN
1293 "delegation_row"."area_id" NOTNULL AND
1294 "delegation_row"."issue_id" ISNULL
1295 THEN 'area'
1296 WHEN
1297 "delegation_row"."area_id" ISNULL AND
1298 "delegation_row"."issue_id" NOTNULL
1299 THEN 'issue'
1300 END;
1301 "output_rows" := "output_rows" || "output_row";
1302 "output_row"."member_id" := "delegation_row"."trustee_id";
1303 ELSE
1304 "output_row"."scope_out" := NULL;
1305 "output_rows" := "output_rows" || "output_row";
1306 EXIT;
1307 END IF;
1308 EXIT WHEN "loop_member_id_v" NOTNULL;
1309 "output_row"."index" := "output_row"."index" + 1;
1310 END LOOP;
1311 "row_count" := array_upper("output_rows", 1);
1312 "i" := 1;
1313 "loop_v" := FALSE;
1314 LOOP
1315 "output_row" := "output_rows"["i"];
1316 EXIT WHEN "output_row"."member_id" ISNULL;
1317 IF "loop_v" THEN
1318 IF "i" + 1 = "row_count" THEN
1319 "output_row"."loop" := 'last';
1320 ELSIF "i" = "row_count" THEN
1321 "output_row"."loop" := 'repetition';
1322 ELSE
1323 "output_row"."loop" := 'intermediate';
1324 END IF;
1325 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
1326 "output_row"."loop" := 'first';
1327 "loop_v" := TRUE;
1328 END IF;
1329 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
1330 "output_row"."participation" := NULL;
1331 END IF;
1332 RETURN NEXT "output_row";
1333 "i" := "i" + 1;
1334 END LOOP;
1335 RETURN;
1336 END;
1337 $$;
1339 COMMENT ON FUNCTION "delegation_chain"
1340 ( "member"."id"%TYPE,
1341 "area"."id"%TYPE,
1342 "issue"."id"%TYPE,
1343 "member"."id"%TYPE )
1344 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
1346 CREATE FUNCTION "delegation_chain"
1347 ( "member_id_p" "member"."id"%TYPE,
1348 "area_id_p" "area"."id"%TYPE,
1349 "issue_id_p" "issue"."id"%TYPE )
1350 RETURNS SETOF "delegation_chain_row"
1351 LANGUAGE 'plpgsql' STABLE AS $$
1352 DECLARE
1353 "result_row" "delegation_chain_row";
1354 BEGIN
1355 FOR "result_row" IN
1356 SELECT * FROM "delegation_chain"(
1357 "member_id_p", "area_id_p", "issue_id_p", NULL
1358 )
1359 LOOP
1360 RETURN NEXT "result_row";
1361 END LOOP;
1362 RETURN;
1363 END;
1364 $$;
1366 COMMENT ON FUNCTION "delegation_chain"
1367 ( "member"."id"%TYPE,
1368 "area"."id"%TYPE,
1369 "issue"."id"%TYPE )
1370 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
1374 ------------------------------
1375 -- Comparison by vote count --
1376 ------------------------------
1378 CREATE FUNCTION "vote_ratio"
1379 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
1380 "negative_votes_p" "initiative"."negative_votes"%TYPE )
1381 RETURNS FLOAT8
1382 LANGUAGE 'plpgsql' STABLE AS $$
1383 DECLARE
1384 "total_v" INT4;
1385 BEGIN
1386 "total_v" := "positive_votes_p" + "negative_votes_p";
1387 IF "total_v" > 0 THEN
1388 RETURN "positive_votes_p"::FLOAT8 / "total_v"::FLOAT8;
1389 ELSE
1390 RETURN 0.5;
1391 END IF;
1392 END;
1393 $$;
1395 COMMENT ON FUNCTION "vote_ratio"
1396 ( "initiative"."positive_votes"%TYPE,
1397 "initiative"."negative_votes"%TYPE )
1398 IS 'Ratio of positive votes to sum of positive and negative votes; 0.5, if there are neither positive nor negative votes';
1402 ------------------------------------------------
1403 -- Locking for snapshots and voting procedure --
1404 ------------------------------------------------
1406 CREATE FUNCTION "global_lock"() RETURNS VOID
1407 LANGUAGE 'plpgsql' VOLATILE AS $$
1408 BEGIN
1409 -- NOTE: PostgreSQL allows reading, while tables are locked in
1410 -- exclusive move. Transactions should be kept short anyway!
1411 LOCK TABLE "member" IN EXCLUSIVE MODE;
1412 LOCK TABLE "area" IN EXCLUSIVE MODE;
1413 LOCK TABLE "membership" IN EXCLUSIVE MODE;
1414 -- NOTE: "member", "area" and "membership" are locked first to
1415 -- prevent deadlocks in combination with "calculate_member_counts"()
1416 LOCK TABLE "policy" IN EXCLUSIVE MODE;
1417 LOCK TABLE "issue" IN EXCLUSIVE MODE;
1418 LOCK TABLE "initiative" IN EXCLUSIVE MODE;
1419 LOCK TABLE "draft" IN EXCLUSIVE MODE;
1420 LOCK TABLE "suggestion" IN EXCLUSIVE MODE;
1421 LOCK TABLE "interest" IN EXCLUSIVE MODE;
1422 LOCK TABLE "initiator" IN EXCLUSIVE MODE;
1423 LOCK TABLE "supporter" IN EXCLUSIVE MODE;
1424 LOCK TABLE "opinion" IN EXCLUSIVE MODE;
1425 LOCK TABLE "delegation" IN EXCLUSIVE MODE;
1426 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
1427 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
1428 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
1429 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
1430 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
1431 LOCK TABLE "direct_voter" IN EXCLUSIVE MODE;
1432 LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;
1433 LOCK TABLE "vote" IN EXCLUSIVE MODE;
1434 RETURN;
1435 END;
1436 $$;
1438 COMMENT ON FUNCTION "global_lock"() IS 'Locks all tables related to support/voting until end of transaction; read access is still possible though';
1442 -------------------------------
1443 -- Materialize member counts --
1444 -------------------------------
1446 CREATE FUNCTION "calculate_member_counts"()
1447 RETURNS VOID
1448 LANGUAGE 'plpgsql' VOLATILE AS $$
1449 BEGIN
1450 LOCK TABLE "member" IN EXCLUSIVE MODE;
1451 LOCK TABLE "area" IN EXCLUSIVE MODE;
1452 LOCK TABLE "membership" IN EXCLUSIVE MODE;
1453 DELETE FROM "member_count";
1454 INSERT INTO "member_count" ("total_count")
1455 SELECT "total_count" FROM "member_count_view";
1456 UPDATE "area" SET
1457 "direct_member_count" = "view"."direct_member_count",
1458 "member_weight" = "view"."member_weight",
1459 "autoreject_weight" = "view"."autoreject_weight"
1460 FROM "area_member_count" AS "view"
1461 WHERE "view"."area_id" = "area"."id";
1462 RETURN;
1463 END;
1464 $$;
1466 COMMENT ON FUNCTION "calculate_member_counts"() IS 'Updates "member_count" table and "member_count" column of table "area" by materializing data from views "member_count_view" and "area_member_count"';
1470 ------------------------------
1471 -- Calculation of snapshots --
1472 ------------------------------
1474 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
1475 ( "issue_id_p" "issue"."id"%TYPE,
1476 "member_id_p" "member"."id"%TYPE,
1477 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
1478 RETURNS "direct_population_snapshot"."weight"%TYPE
1479 LANGUAGE 'plpgsql' VOLATILE AS $$
1480 DECLARE
1481 "issue_delegation_row" "issue_delegation"%ROWTYPE;
1482 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
1483 "weight_v" INT4;
1484 BEGIN
1485 "weight_v" := 0;
1486 FOR "issue_delegation_row" IN
1487 SELECT * FROM "issue_delegation"
1488 WHERE "trustee_id" = "member_id_p"
1489 AND "issue_id" = "issue_id_p"
1490 LOOP
1491 IF NOT EXISTS (
1492 SELECT NULL FROM "direct_population_snapshot"
1493 WHERE "issue_id" = "issue_id_p"
1494 AND "event" = 'periodic'
1495 AND "member_id" = "issue_delegation_row"."truster_id"
1496 ) AND NOT EXISTS (
1497 SELECT NULL FROM "delegating_population_snapshot"
1498 WHERE "issue_id" = "issue_id_p"
1499 AND "event" = 'periodic'
1500 AND "member_id" = "issue_delegation_row"."truster_id"
1501 ) THEN
1502 "delegate_member_ids_v" :=
1503 "member_id_p" || "delegate_member_ids_p";
1504 INSERT INTO "delegating_population_snapshot"
1505 ("issue_id", "event", "member_id", "delegate_member_ids")
1506 VALUES (
1507 "issue_id_p",
1508 'periodic',
1509 "issue_delegation_row"."truster_id",
1510 "delegate_member_ids_v"
1511 );
1512 "weight_v" := "weight_v" + 1 +
1513 "weight_of_added_delegations_for_population_snapshot"(
1514 "issue_id_p",
1515 "issue_delegation_row"."truster_id",
1516 "delegate_member_ids_v"
1517 );
1518 END IF;
1519 END LOOP;
1520 RETURN "weight_v";
1521 END;
1522 $$;
1524 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
1525 ( "issue"."id"%TYPE,
1526 "member"."id"%TYPE,
1527 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
1528 IS 'Helper function for "create_population_snapshot" function';
1531 CREATE FUNCTION "create_population_snapshot"
1532 ( "issue_id_p" "issue"."id"%TYPE )
1533 RETURNS VOID
1534 LANGUAGE 'plpgsql' VOLATILE AS $$
1535 DECLARE
1536 "member_id_v" "member"."id"%TYPE;
1537 BEGIN
1538 DELETE FROM "direct_population_snapshot"
1539 WHERE "issue_id" = "issue_id_p"
1540 AND "event" = 'periodic';
1541 DELETE FROM "delegating_population_snapshot"
1542 WHERE "issue_id" = "issue_id_p"
1543 AND "event" = 'periodic';
1544 INSERT INTO "direct_population_snapshot"
1545 ("issue_id", "event", "member_id", "interest_exists")
1546 SELECT DISTINCT ON ("issue_id", "member_id")
1547 "issue_id_p" AS "issue_id",
1548 'periodic' AS "event",
1549 "subquery"."member_id",
1550 "subquery"."interest_exists"
1551 FROM (
1552 SELECT
1553 "member"."id" AS "member_id",
1554 FALSE AS "interest_exists"
1555 FROM "issue"
1556 JOIN "area" ON "issue"."area_id" = "area"."id"
1557 JOIN "membership" ON "area"."id" = "membership"."area_id"
1558 JOIN "member" ON "membership"."member_id" = "member"."id"
1559 WHERE "issue"."id" = "issue_id_p"
1560 AND "member"."active"
1561 UNION
1562 SELECT
1563 "member"."id" AS "member_id",
1564 TRUE AS "interest_exists"
1565 FROM "interest" JOIN "member"
1566 ON "interest"."member_id" = "member"."id"
1567 WHERE "interest"."issue_id" = "issue_id_p"
1568 AND "member"."active"
1569 ) AS "subquery"
1570 ORDER BY
1571 "issue_id_p",
1572 "subquery"."member_id",
1573 "subquery"."interest_exists" DESC;
1574 FOR "member_id_v" IN
1575 SELECT "member_id" FROM "direct_population_snapshot"
1576 WHERE "issue_id" = "issue_id_p"
1577 AND "event" = 'periodic'
1578 LOOP
1579 UPDATE "direct_population_snapshot" SET
1580 "weight" = 1 +
1581 "weight_of_added_delegations_for_population_snapshot"(
1582 "issue_id_p",
1583 "member_id_v",
1584 '{}'
1585 )
1586 WHERE "issue_id" = "issue_id_p"
1587 AND "event" = 'periodic'
1588 AND "member_id" = "member_id_v";
1589 END LOOP;
1590 RETURN;
1591 END;
1592 $$;
1594 COMMENT ON FUNCTION "create_population_snapshot"
1595 ( "issue_id_p" "issue"."id"%TYPE )
1596 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.';
1599 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
1600 ( "issue_id_p" "issue"."id"%TYPE,
1601 "member_id_p" "member"."id"%TYPE,
1602 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
1603 RETURNS "direct_interest_snapshot"."weight"%TYPE
1604 LANGUAGE 'plpgsql' VOLATILE AS $$
1605 DECLARE
1606 "issue_delegation_row" "issue_delegation"%ROWTYPE;
1607 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
1608 "weight_v" INT4;
1609 BEGIN
1610 "weight_v" := 0;
1611 FOR "issue_delegation_row" IN
1612 SELECT * FROM "issue_delegation"
1613 WHERE "trustee_id" = "member_id_p"
1614 AND "issue_id" = "issue_id_p"
1615 LOOP
1616 IF NOT EXISTS (
1617 SELECT NULL FROM "direct_interest_snapshot"
1618 WHERE "issue_id" = "issue_id_p"
1619 AND "event" = 'periodic'
1620 AND "member_id" = "issue_delegation_row"."truster_id"
1621 ) AND NOT EXISTS (
1622 SELECT NULL FROM "delegating_interest_snapshot"
1623 WHERE "issue_id" = "issue_id_p"
1624 AND "event" = 'periodic'
1625 AND "member_id" = "issue_delegation_row"."truster_id"
1626 ) THEN
1627 "delegate_member_ids_v" :=
1628 "member_id_p" || "delegate_member_ids_p";
1629 INSERT INTO "delegating_interest_snapshot"
1630 ("issue_id", "event", "member_id", "delegate_member_ids")
1631 VALUES (
1632 "issue_id_p",
1633 'periodic',
1634 "issue_delegation_row"."truster_id",
1635 "delegate_member_ids_v"
1636 );
1637 "weight_v" := "weight_v" + 1 +
1638 "weight_of_added_delegations_for_interest_snapshot"(
1639 "issue_id_p",
1640 "issue_delegation_row"."truster_id",
1641 "delegate_member_ids_v"
1642 );
1643 END IF;
1644 END LOOP;
1645 RETURN "weight_v";
1646 END;
1647 $$;
1649 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
1650 ( "issue"."id"%TYPE,
1651 "member"."id"%TYPE,
1652 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
1653 IS 'Helper function for "create_interest_snapshot" function';
1656 CREATE FUNCTION "create_interest_snapshot"
1657 ( "issue_id_p" "issue"."id"%TYPE )
1658 RETURNS VOID
1659 LANGUAGE 'plpgsql' VOLATILE AS $$
1660 DECLARE
1661 "member_id_v" "member"."id"%TYPE;
1662 BEGIN
1663 DELETE FROM "direct_interest_snapshot"
1664 WHERE "issue_id" = "issue_id_p"
1665 AND "event" = 'periodic';
1666 DELETE FROM "delegating_interest_snapshot"
1667 WHERE "issue_id" = "issue_id_p"
1668 AND "event" = 'periodic';
1669 DELETE FROM "direct_supporter_snapshot"
1670 WHERE "issue_id" = "issue_id_p"
1671 AND "event" = 'periodic';
1672 INSERT INTO "direct_interest_snapshot"
1673 ("issue_id", "event", "member_id", "voting_requested")
1674 SELECT
1675 "issue_id_p" AS "issue_id",
1676 'periodic' AS "event",
1677 "member"."id" AS "member_id",
1678 "interest"."voting_requested"
1679 FROM "interest" JOIN "member"
1680 ON "interest"."member_id" = "member"."id"
1681 WHERE "interest"."issue_id" = "issue_id_p"
1682 AND "member"."active";
1683 FOR "member_id_v" IN
1684 SELECT "member_id" FROM "direct_interest_snapshot"
1685 WHERE "issue_id" = "issue_id_p"
1686 AND "event" = 'periodic'
1687 LOOP
1688 UPDATE "direct_interest_snapshot" SET
1689 "weight" = 1 +
1690 "weight_of_added_delegations_for_interest_snapshot"(
1691 "issue_id_p",
1692 "member_id_v",
1693 '{}'
1694 )
1695 WHERE "issue_id" = "issue_id_p"
1696 AND "event" = 'periodic'
1697 AND "member_id" = "member_id_v";
1698 END LOOP;
1699 INSERT INTO "direct_supporter_snapshot"
1700 ( "issue_id", "initiative_id", "event", "member_id",
1701 "informed", "satisfied" )
1702 SELECT
1703 "issue_id_p" AS "issue_id",
1704 "initiative"."id" AS "initiative_id",
1705 'periodic' AS "event",
1706 "member"."id" AS "member_id",
1707 "supporter"."draft_id" = "current_draft"."id" AS "informed",
1708 NOT EXISTS (
1709 SELECT NULL FROM "critical_opinion"
1710 WHERE "initiative_id" = "initiative"."id"
1711 AND "member_id" = "member"."id"
1712 ) AS "satisfied"
1713 FROM "supporter"
1714 JOIN "member"
1715 ON "supporter"."member_id" = "member"."id"
1716 JOIN "initiative"
1717 ON "supporter"."initiative_id" = "initiative"."id"
1718 JOIN "current_draft"
1719 ON "initiative"."id" = "current_draft"."initiative_id"
1720 JOIN "direct_interest_snapshot"
1721 ON "member"."id" = "direct_interest_snapshot"."member_id"
1722 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
1723 AND "event" = 'periodic'
1724 WHERE "member"."active"
1725 AND "initiative"."issue_id" = "issue_id_p";
1726 RETURN;
1727 END;
1728 $$;
1730 COMMENT ON FUNCTION "create_interest_snapshot"
1731 ( "issue"."id"%TYPE )
1732 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.';
1735 CREATE FUNCTION "create_snapshot"
1736 ( "issue_id_p" "issue"."id"%TYPE )
1737 RETURNS VOID
1738 LANGUAGE 'plpgsql' VOLATILE AS $$
1739 DECLARE
1740 "initiative_id_v" "initiative"."id"%TYPE;
1741 "suggestion_id_v" "suggestion"."id"%TYPE;
1742 BEGIN
1743 PERFORM "global_lock"();
1744 PERFORM "create_population_snapshot"("issue_id_p");
1745 PERFORM "create_interest_snapshot"("issue_id_p");
1746 UPDATE "issue" SET
1747 "snapshot" = now(),
1748 "population" = (
1749 SELECT coalesce(sum("weight"), 0)
1750 FROM "direct_population_snapshot"
1751 WHERE "issue_id" = "issue_id_p"
1752 AND "event" = 'periodic'
1753 ),
1754 "vote_now" = (
1755 SELECT coalesce(sum("weight"), 0)
1756 FROM "direct_interest_snapshot"
1757 WHERE "issue_id" = "issue_id_p"
1758 AND "event" = 'periodic'
1759 AND "voting_requested" = TRUE
1760 ),
1761 "vote_later" = (
1762 SELECT coalesce(sum("weight"), 0)
1763 FROM "direct_interest_snapshot"
1764 WHERE "issue_id" = "issue_id_p"
1765 AND "event" = 'periodic'
1766 AND "voting_requested" = FALSE
1767 )
1768 WHERE "id" = "issue_id_p";
1769 FOR "initiative_id_v" IN
1770 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
1771 LOOP
1772 UPDATE "initiative" SET
1773 "supporter_count" = (
1774 SELECT coalesce(sum("di"."weight"), 0)
1775 FROM "direct_interest_snapshot" AS "di"
1776 JOIN "direct_supporter_snapshot" AS "ds"
1777 ON "di"."member_id" = "ds"."member_id"
1778 WHERE "di"."issue_id" = "issue_id_p"
1779 AND "di"."event" = 'periodic'
1780 AND "ds"."initiative_id" = "initiative_id_v"
1781 AND "ds"."event" = 'periodic'
1782 ),
1783 "informed_supporter_count" = (
1784 SELECT coalesce(sum("di"."weight"), 0)
1785 FROM "direct_interest_snapshot" AS "di"
1786 JOIN "direct_supporter_snapshot" AS "ds"
1787 ON "di"."member_id" = "ds"."member_id"
1788 WHERE "di"."issue_id" = "issue_id_p"
1789 AND "di"."event" = 'periodic'
1790 AND "ds"."initiative_id" = "initiative_id_v"
1791 AND "ds"."event" = 'periodic'
1792 AND "ds"."informed"
1793 ),
1794 "satisfied_supporter_count" = (
1795 SELECT coalesce(sum("di"."weight"), 0)
1796 FROM "direct_interest_snapshot" AS "di"
1797 JOIN "direct_supporter_snapshot" AS "ds"
1798 ON "di"."member_id" = "ds"."member_id"
1799 WHERE "di"."issue_id" = "issue_id_p"
1800 AND "di"."event" = 'periodic'
1801 AND "ds"."initiative_id" = "initiative_id_v"
1802 AND "ds"."event" = 'periodic'
1803 AND "ds"."satisfied"
1804 ),
1805 "satisfied_informed_supporter_count" = (
1806 SELECT coalesce(sum("di"."weight"), 0)
1807 FROM "direct_interest_snapshot" AS "di"
1808 JOIN "direct_supporter_snapshot" AS "ds"
1809 ON "di"."member_id" = "ds"."member_id"
1810 WHERE "di"."issue_id" = "issue_id_p"
1811 AND "di"."event" = 'periodic'
1812 AND "ds"."initiative_id" = "initiative_id_v"
1813 AND "ds"."event" = 'periodic'
1814 AND "ds"."informed"
1815 AND "ds"."satisfied"
1816 )
1817 WHERE "id" = "initiative_id_v";
1818 FOR "suggestion_id_v" IN
1819 SELECT "id" FROM "suggestion"
1820 WHERE "initiative_id" = "initiative_id_v"
1821 LOOP
1822 UPDATE "suggestion" SET
1823 "minus2_unfulfilled_count" = (
1824 SELECT coalesce(sum("snapshot"."weight"), 0)
1825 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
1826 ON "opinion"."member_id" = "snapshot"."member_id"
1827 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
1828 AND "snapshot"."issue_id" = "issue_id_p"
1829 AND "opinion"."degree" = -2
1830 AND "opinion"."fulfilled" = FALSE
1831 ),
1832 "minus2_fulfilled_count" = (
1833 SELECT coalesce(sum("snapshot"."weight"), 0)
1834 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
1835 ON "opinion"."member_id" = "snapshot"."member_id"
1836 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
1837 AND "snapshot"."issue_id" = "issue_id_p"
1838 AND "opinion"."degree" = -2
1839 AND "opinion"."fulfilled" = TRUE
1840 ),
1841 "minus1_unfulfilled_count" = (
1842 SELECT coalesce(sum("snapshot"."weight"), 0)
1843 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
1844 ON "opinion"."member_id" = "snapshot"."member_id"
1845 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
1846 AND "snapshot"."issue_id" = "issue_id_p"
1847 AND "opinion"."degree" = -1
1848 AND "opinion"."fulfilled" = FALSE
1849 ),
1850 "minus1_fulfilled_count" = (
1851 SELECT coalesce(sum("snapshot"."weight"), 0)
1852 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
1853 ON "opinion"."member_id" = "snapshot"."member_id"
1854 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
1855 AND "snapshot"."issue_id" = "issue_id_p"
1856 AND "opinion"."degree" = -1
1857 AND "opinion"."fulfilled" = TRUE
1858 ),
1859 "plus1_unfulfilled_count" = (
1860 SELECT coalesce(sum("snapshot"."weight"), 0)
1861 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
1862 ON "opinion"."member_id" = "snapshot"."member_id"
1863 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
1864 AND "snapshot"."issue_id" = "issue_id_p"
1865 AND "opinion"."degree" = 1
1866 AND "opinion"."fulfilled" = FALSE
1867 ),
1868 "plus1_fulfilled_count" = (
1869 SELECT coalesce(sum("snapshot"."weight"), 0)
1870 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
1871 ON "opinion"."member_id" = "snapshot"."member_id"
1872 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
1873 AND "snapshot"."issue_id" = "issue_id_p"
1874 AND "opinion"."degree" = 1
1875 AND "opinion"."fulfilled" = TRUE
1876 ),
1877 "plus2_unfulfilled_count" = (
1878 SELECT coalesce(sum("snapshot"."weight"), 0)
1879 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
1880 ON "opinion"."member_id" = "snapshot"."member_id"
1881 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
1882 AND "snapshot"."issue_id" = "issue_id_p"
1883 AND "opinion"."degree" = 2
1884 AND "opinion"."fulfilled" = FALSE
1885 ),
1886 "plus2_fulfilled_count" = (
1887 SELECT coalesce(sum("snapshot"."weight"), 0)
1888 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
1889 ON "opinion"."member_id" = "snapshot"."member_id"
1890 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
1891 AND "snapshot"."issue_id" = "issue_id_p"
1892 AND "opinion"."degree" = 2
1893 AND "opinion"."fulfilled" = TRUE
1894 )
1895 WHERE "suggestion"."id" = "suggestion_id_v";
1896 END LOOP;
1897 END LOOP;
1898 RETURN;
1899 END;
1900 $$;
1902 COMMENT ON FUNCTION "create_snapshot"
1903 ( "issue"."id"%TYPE )
1904 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.';
1907 CREATE FUNCTION "set_snapshot_event"
1908 ( "issue_id_p" "issue"."id"%TYPE,
1909 "event_p" "snapshot_event" )
1910 RETURNS VOID
1911 LANGUAGE 'plpgsql' VOLATILE AS $$
1912 BEGIN
1913 UPDATE "direct_population_snapshot" SET "event" = "event_p"
1914 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
1915 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
1916 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
1917 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
1918 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
1919 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
1920 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
1921 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
1922 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
1923 RETURN;
1924 END;
1925 $$;
1927 COMMENT ON FUNCTION "set_snapshot_event"
1928 ( "issue"."id"%TYPE,
1929 "snapshot_event" )
1930 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
1934 ---------------------
1935 -- Freezing issues --
1936 ---------------------
1938 CREATE FUNCTION "freeze_after_snapshot"
1939 ( "issue_id_p" "issue"."id"%TYPE )
1940 RETURNS VOID
1941 LANGUAGE 'plpgsql' VOLATILE AS $$
1942 DECLARE
1943 "issue_row" "issue"%ROWTYPE;
1944 "policy_row" "policy"%ROWTYPE;
1945 "initiative_row" "initiative"%ROWTYPE;
1946 BEGIN
1947 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1948 SELECT * INTO "policy_row"
1949 FROM "policy" WHERE "id" = "issue_row"."policy_id";
1950 PERFORM "set_snapshot_event"("issue_id_p", 'start_of_voting');
1951 UPDATE "issue" SET
1952 "accepted" = coalesce("accepted", now()),
1953 "half_frozen" = coalesce("half_frozen", now()),
1954 "fully_frozen" = now()
1955 WHERE "id" = "issue_id_p";
1956 FOR "initiative_row" IN
1957 SELECT * FROM "initiative" WHERE "issue_id" = "issue_id_p"
1958 LOOP
1959 IF
1960 "initiative_row"."satisfied_supporter_count" > 0 AND
1961 "initiative_row"."satisfied_supporter_count" *
1962 "policy_row"."initiative_quorum_den" >=
1963 "issue_row"."population" * "policy_row"."initiative_quorum_num"
1964 THEN
1965 UPDATE "initiative" SET "admitted" = TRUE
1966 WHERE "id" = "initiative_row"."id";
1967 ELSE
1968 UPDATE "initiative" SET "admitted" = FALSE
1969 WHERE "id" = "initiative_row"."id";
1970 END IF;
1971 END LOOP;
1972 RETURN;
1973 END;
1974 $$;
1976 COMMENT ON FUNCTION "freeze_after_snapshot"
1977 ( "issue"."id"%TYPE )
1978 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction';
1981 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
1982 RETURNS VOID
1983 LANGUAGE 'plpgsql' VOLATILE AS $$
1984 DECLARE
1985 "issue_row" "issue"%ROWTYPE;
1986 BEGIN
1987 PERFORM "create_snapshot"("issue_id_p");
1988 PERFORM "freeze_after_snapshot"("issue_id_p");
1989 RETURN;
1990 END;
1991 $$;
1993 COMMENT ON FUNCTION "freeze_after_snapshot"
1994 ( "issue"."id"%TYPE )
1995 IS 'Freeze an issue manually (fully) and start voting';
1999 -----------------------
2000 -- Counting of votes --
2001 -----------------------
2004 CREATE FUNCTION "weight_of_added_vote_delegations"
2005 ( "issue_id_p" "issue"."id"%TYPE,
2006 "member_id_p" "member"."id"%TYPE,
2007 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
2008 RETURNS "direct_voter"."weight"%TYPE
2009 LANGUAGE 'plpgsql' VOLATILE AS $$
2010 DECLARE
2011 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2012 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
2013 "weight_v" INT4;
2014 BEGIN
2015 "weight_v" := 0;
2016 FOR "issue_delegation_row" IN
2017 SELECT * FROM "issue_delegation"
2018 WHERE "trustee_id" = "member_id_p"
2019 AND "issue_id" = "issue_id_p"
2020 LOOP
2021 IF NOT EXISTS (
2022 SELECT NULL FROM "direct_voter"
2023 WHERE "member_id" = "issue_delegation_row"."truster_id"
2024 AND "issue_id" = "issue_id_p"
2025 ) AND NOT EXISTS (
2026 SELECT NULL FROM "delegating_voter"
2027 WHERE "member_id" = "issue_delegation_row"."truster_id"
2028 AND "issue_id" = "issue_id_p"
2029 ) THEN
2030 "delegate_member_ids_v" :=
2031 "member_id_p" || "delegate_member_ids_p";
2032 INSERT INTO "delegating_voter"
2033 ("member_id", "issue_id", "delegate_member_ids")
2034 VALUES (
2035 "issue_delegation_row"."truster_id",
2036 "issue_id_p",
2037 "delegate_member_ids_v"
2038 );
2039 "weight_v" := "weight_v" +
2040 1 + "weight_of_added_vote_delegations"(
2041 "issue_id_p",
2042 "issue_delegation_row"."truster_id",
2043 "delegate_member_ids_v"
2044 );
2045 END IF;
2046 END LOOP;
2047 RETURN "weight_v";
2048 END;
2049 $$;
2051 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
2052 ( "issue"."id"%TYPE,
2053 "member"."id"%TYPE,
2054 "delegating_voter"."delegate_member_ids"%TYPE )
2055 IS 'Helper function for "add_vote_delegations" function';
2058 CREATE FUNCTION "add_vote_delegations"
2059 ( "issue_id_p" "issue"."id"%TYPE )
2060 RETURNS VOID
2061 LANGUAGE 'plpgsql' VOLATILE AS $$
2062 DECLARE
2063 "member_id_v" "member"."id"%TYPE;
2064 BEGIN
2065 FOR "member_id_v" IN
2066 SELECT "member_id" FROM "direct_voter"
2067 WHERE "issue_id" = "issue_id_p"
2068 LOOP
2069 UPDATE "direct_voter" SET
2070 "weight" = "weight" + "weight_of_added_vote_delegations"(
2071 "issue_id_p",
2072 "member_id_v",
2073 '{}'
2074 )
2075 WHERE "member_id" = "member_id_v"
2076 AND "issue_id" = "issue_id_p";
2077 END LOOP;
2078 RETURN;
2079 END;
2080 $$;
2082 COMMENT ON FUNCTION "add_vote_delegations"
2083 ( "issue_id_p" "issue"."id"%TYPE )
2084 IS 'Helper function for "close_voting" function';
2087 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
2088 RETURNS VOID
2089 LANGUAGE 'plpgsql' VOLATILE AS $$
2090 DECLARE
2091 "issue_row" "issue"%ROWTYPE;
2092 "member_id_v" "member"."id"%TYPE;
2093 BEGIN
2094 PERFORM "global_lock"();
2095 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2096 DELETE FROM "delegating_voter"
2097 WHERE "issue_id" = "issue_id_p";
2098 DELETE FROM "direct_voter"
2099 WHERE "issue_id" = "issue_id_p"
2100 AND "autoreject" = TRUE;
2101 DELETE FROM "direct_voter" USING "member"
2102 WHERE "direct_voter"."member_id" = "member"."id"
2103 AND "direct_voter"."issue_id" = "issue_id_p"
2104 AND "member"."active" = FALSE;
2105 UPDATE "direct_voter" SET "weight" = 1
2106 WHERE "issue_id" = "issue_id_p";
2107 PERFORM "add_vote_delegations"("issue_id_p");
2108 FOR "member_id_v" IN
2109 SELECT "interest"."member_id"
2110 FROM "interest"
2111 LEFT JOIN "direct_voter"
2112 ON "interest"."member_id" = "direct_voter"."member_id"
2113 AND "interest"."issue_id" = "direct_voter"."issue_id"
2114 LEFT JOIN "delegating_voter"
2115 ON "interest"."member_id" = "delegating_voter"."member_id"
2116 AND "interest"."issue_id" = "delegating_voter"."issue_id"
2117 WHERE "interest"."issue_id" = "issue_id_p"
2118 AND "interest"."autoreject" = TRUE
2119 AND "direct_voter"."member_id" ISNULL
2120 AND "delegating_voter"."member_id" ISNULL
2121 UNION SELECT "membership"."member_id"
2122 FROM "membership"
2123 LEFT JOIN "interest"
2124 ON "membership"."member_id" = "interest"."member_id"
2125 AND "interest"."issue_id" = "issue_id_p"
2126 LEFT JOIN "direct_voter"
2127 ON "membership"."member_id" = "direct_voter"."member_id"
2128 AND "direct_voter"."issue_id" = "issue_id_p"
2129 LEFT JOIN "delegating_voter"
2130 ON "membership"."member_id" = "delegating_voter"."member_id"
2131 AND "delegating_voter"."issue_id" = "issue_id_p"
2132 WHERE "membership"."area_id" = "issue_row"."area_id"
2133 AND "membership"."autoreject" = TRUE
2134 AND "interest"."autoreject" ISNULL
2135 AND "direct_voter"."member_id" ISNULL
2136 AND "delegating_voter"."member_id" ISNULL
2137 LOOP
2138 INSERT INTO "direct_voter" ("member_id", "issue_id", "autoreject")
2139 VALUES ("member_id_v", "issue_id_p", TRUE);
2140 INSERT INTO "vote" (
2141 "member_id",
2142 "issue_id",
2143 "initiative_id",
2144 "grade"
2145 ) SELECT
2146 "member_id_v" AS "member_id",
2147 "issue_id_p" AS "issue_id",
2148 "id" AS "initiative_id",
2149 -1 AS "grade"
2150 FROM "initiative" WHERE "issue_id" = "issue_id_p";
2151 END LOOP;
2152 PERFORM "add_vote_delegations"("issue_id_p");
2153 UPDATE "issue" SET
2154 "voter_count" = (
2155 SELECT coalesce(sum("weight"), 0)
2156 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
2157 )
2158 WHERE "id" = "issue_id_p";
2159 UPDATE "initiative" SET
2160 "positive_votes" = "subquery"."positive_votes",
2161 "negative_votes" = "subquery"."negative_votes"
2162 FROM (
2163 SELECT
2164 "initiative_id",
2165 coalesce(
2166 sum(
2167 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
2168 ),
2169 0
2170 ) AS "positive_votes",
2171 coalesce(
2172 sum(
2173 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
2174 ),
2175 0
2176 ) AS "negative_votes"
2177 FROM "vote" JOIN "direct_voter"
2178 ON "vote"."member_id" = "direct_voter"."member_id"
2179 AND "vote"."issue_id" = "direct_voter"."issue_id"
2180 WHERE "vote"."issue_id" = "issue_id_p"
2181 GROUP BY "initiative_id"
2182 ) AS "subquery"
2183 WHERE "initiative"."admitted"
2184 AND "initiative"."id" = "subquery"."initiative_id";
2185 UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p";
2186 END;
2187 $$;
2189 COMMENT ON FUNCTION "close_voting"
2190 ( "issue"."id"%TYPE )
2191 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.';
2194 CREATE FUNCTION "init_array"("dim_p" INTEGER)
2195 RETURNS INT4[]
2196 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2197 DECLARE
2198 "i" INTEGER;
2199 "ary_text_v" TEXT;
2200 BEGIN
2201 IF "dim_p" >= 1 THEN
2202 "ary_text_v" := '{NULL';
2203 "i" := "dim_p";
2204 LOOP
2205 "i" := "i" - 1;
2206 EXIT WHEN "i" = 0;
2207 "ary_text_v" := "ary_text_v" || ',NULL';
2208 END LOOP;
2209 "ary_text_v" := "ary_text_v" || '}';
2210 RETURN "ary_text_v"::INT4[][];
2211 ELSE
2212 RAISE EXCEPTION 'Dimension needs to be at least 1.';
2213 END IF;
2214 END;
2215 $$;
2217 COMMENT ON FUNCTION "init_array"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
2220 CREATE FUNCTION "init_square_matrix"("dim_p" INTEGER)
2221 RETURNS INT4[][]
2222 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2223 DECLARE
2224 "i" INTEGER;
2225 "row_text_v" TEXT;
2226 "ary_text_v" TEXT;
2227 BEGIN
2228 IF "dim_p" >= 1 THEN
2229 "row_text_v" := '{NULL';
2230 "i" := "dim_p";
2231 LOOP
2232 "i" := "i" - 1;
2233 EXIT WHEN "i" = 0;
2234 "row_text_v" := "row_text_v" || ',NULL';
2235 END LOOP;
2236 "row_text_v" := "row_text_v" || '}';
2237 "ary_text_v" := '{' || "row_text_v";
2238 "i" := "dim_p";
2239 LOOP
2240 "i" := "i" - 1;
2241 EXIT WHEN "i" = 0;
2242 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
2243 END LOOP;
2244 "ary_text_v" := "ary_text_v" || '}';
2245 RETURN "ary_text_v"::INT4[][];
2246 ELSE
2247 RAISE EXCEPTION 'Dimension needs to be at least 1.';
2248 END IF;
2249 END;
2250 $$;
2252 COMMENT ON FUNCTION "init_square_matrix"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
2255 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
2256 RETURNS VOID
2257 LANGUAGE 'plpgsql' VOLATILE AS $$
2258 DECLARE
2259 "dimension_v" INTEGER;
2260 "matrix" INT4[][];
2261 "i" INTEGER;
2262 "j" INTEGER;
2263 "k" INTEGER;
2264 "battle_row" "battle"%ROWTYPE;
2265 "rank_ary" INT4[];
2266 "rank_v" INT4;
2267 "done_v" INTEGER;
2268 "winners_ary" INTEGER[];
2269 "initiative_id_v" "initiative"."id"%TYPE;
2270 BEGIN
2271 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2272 -- Prepare matrix for Schulze-Method:
2273 SELECT count(1) INTO "dimension_v"
2274 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
2275 IF "dimension_v" = 1 THEN
2276 UPDATE "initiative" SET
2277 "rank" = 1
2278 FROM "battle_participant"
2279 WHERE "initiative"."issue_id" = "issue_id_p"
2280 AND "initiative"."id" = "battle_participant"."initiative_id";
2281 ELSIF "dimension_v" > 1 THEN
2282 "matrix" := "init_square_matrix"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2283 "i" := 1;
2284 "j" := 2;
2285 -- Fill matrix with data from "battle" view
2286 FOR "battle_row" IN
2287 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
2288 ORDER BY "winning_initiative_id", "losing_initiative_id"
2289 LOOP
2290 "matrix"["i"]["j"] := "battle_row"."count";
2291 IF "j" = "dimension_v" THEN
2292 "i" := "i" + 1;
2293 "j" := 1;
2294 ELSE
2295 "j" := "j" + 1;
2296 IF "j" = "i" THEN
2297 "j" := "j" + 1;
2298 END IF;
2299 END IF;
2300 END LOOP;
2301 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
2302 RAISE EXCEPTION 'Wrong battle count (should not happen)';
2303 END IF;
2304 -- Delete losers from matrix:
2305 "i" := 1;
2306 LOOP
2307 "j" := "i" + 1;
2308 LOOP
2309 IF "i" != "j" THEN
2310 IF "matrix"["i"]["j"] < "matrix"["j"]["i"] THEN
2311 "matrix"["i"]["j"] := 0;
2312 ELSIF matrix[j][i] < matrix[i][j] THEN
2313 "matrix"["j"]["i"] := 0;
2314 ELSE
2315 "matrix"["i"]["j"] := 0;
2316 "matrix"["j"]["i"] := 0;
2317 END IF;
2318 END IF;
2319 EXIT WHEN "j" = "dimension_v";
2320 "j" := "j" + 1;
2321 END LOOP;
2322 EXIT WHEN "i" = "dimension_v" - 1;
2323 "i" := "i" + 1;
2324 END LOOP;
2325 -- Find best paths:
2326 "i" := 1;
2327 LOOP
2328 "j" := 1;
2329 LOOP
2330 IF "i" != "j" THEN
2331 "k" := 1;
2332 LOOP
2333 IF "i" != "k" AND "j" != "k" THEN
2334 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
2335 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
2336 "matrix"["j"]["k"] := "matrix"["j"]["i"];
2337 END IF;
2338 ELSE
2339 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
2340 "matrix"["j"]["k"] := "matrix"["i"]["k"];
2341 END IF;
2342 END IF;
2343 END IF;
2344 EXIT WHEN "k" = "dimension_v";
2345 "k" := "k" + 1;
2346 END LOOP;
2347 END IF;
2348 EXIT WHEN "j" = "dimension_v";
2349 "j" := "j" + 1;
2350 END LOOP;
2351 EXIT WHEN "i" = "dimension_v";
2352 "i" := "i" + 1;
2353 END LOOP;
2354 -- Determine order of winners:
2355 "rank_ary" := "init_array"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2356 "rank_v" := 1;
2357 "done_v" := 0;
2358 LOOP
2359 "winners_ary" := '{}';
2360 "i" := 1;
2361 LOOP
2362 IF "rank_ary"["i"] ISNULL THEN
2363 "j" := 1;
2364 LOOP
2365 IF
2366 "i" != "j" AND
2367 "rank_ary"["j"] ISNULL AND
2368 "matrix"["j"]["i"] > "matrix"["i"]["j"]
2369 THEN
2370 -- someone else is better
2371 EXIT;
2372 END IF;
2373 IF "j" = "dimension_v" THEN
2374 -- noone is better
2375 "winners_ary" := "winners_ary" || "i";
2376 EXIT;
2377 END IF;
2378 "j" := "j" + 1;
2379 END LOOP;
2380 END IF;
2381 EXIT WHEN "i" = "dimension_v";
2382 "i" := "i" + 1;
2383 END LOOP;
2384 "i" := 1;
2385 LOOP
2386 "rank_ary"["winners_ary"["i"]] := "rank_v";
2387 "done_v" := "done_v" + 1;
2388 EXIT WHEN "i" = array_upper("winners_ary", 1);
2389 "i" := "i" + 1;
2390 END LOOP;
2391 EXIT WHEN "done_v" = "dimension_v";
2392 "rank_v" := "rank_v" + 1;
2393 END LOOP;
2394 -- write preliminary ranks:
2395 "i" := 1;
2396 FOR "initiative_id_v" IN
2397 SELECT "initiative"."id"
2398 FROM "initiative" JOIN "battle_participant"
2399 ON "initiative"."id" = "battle_participant"."initiative_id"
2400 WHERE "initiative"."issue_id" = "issue_id_p"
2401 ORDER BY "initiative"."id"
2402 LOOP
2403 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
2404 WHERE "id" = "initiative_id_v";
2405 "i" := "i" + 1;
2406 END LOOP;
2407 IF "i" != "dimension_v" + 1 THEN
2408 RAISE EXCEPTION 'Wrong winner count (should not happen)';
2409 END IF;
2410 -- straighten ranks (start counting with 1, no equal ranks):
2411 "rank_v" := 1;
2412 FOR "initiative_id_v" IN
2413 SELECT "id" FROM "initiative"
2414 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
2415 ORDER BY
2416 "rank",
2417 "vote_ratio"("positive_votes", "negative_votes") DESC,
2418 "id"
2419 LOOP
2420 UPDATE "initiative" SET "rank" = "rank_v"
2421 WHERE "id" = "initiative_id_v";
2422 "rank_v" := "rank_v" + 1;
2423 END LOOP;
2424 END IF;
2425 -- mark issue as finished
2426 UPDATE "issue" SET "ranks_available" = TRUE
2427 WHERE "id" = "issue_id_p";
2428 RETURN;
2429 END;
2430 $$;
2432 COMMENT ON FUNCTION "calculate_ranks"
2433 ( "issue"."id"%TYPE )
2434 IS 'Determine ranking (Votes have to be counted first)';
2438 -----------------------------
2439 -- Automatic state changes --
2440 -----------------------------
2443 CREATE FUNCTION "check_issue"
2444 ( "issue_id_p" "issue"."id"%TYPE )
2445 RETURNS VOID
2446 LANGUAGE 'plpgsql' VOLATILE AS $$
2447 DECLARE
2448 "issue_row" "issue"%ROWTYPE;
2449 "policy_row" "policy"%ROWTYPE;
2450 "voting_requested_v" BOOLEAN;
2451 BEGIN
2452 PERFORM "global_lock"();
2453 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2454 IF "issue_row"."closed" ISNULL THEN
2455 SELECT * INTO "policy_row" FROM "policy"
2456 WHERE "id" = "issue_row"."policy_id";
2457 IF "issue_row"."fully_frozen" ISNULL THEN
2458 PERFORM "create_snapshot"("issue_id_p");
2459 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2460 END IF;
2461 IF "issue_row"."accepted" ISNULL THEN
2462 IF EXISTS (
2463 SELECT NULL FROM "initiative"
2464 WHERE "issue_id" = "issue_id_p"
2465 AND "supporter_count" > 0
2466 AND "supporter_count" * "policy_row"."issue_quorum_den"
2467 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
2468 ) THEN
2469 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
2470 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later
2471 UPDATE "issue" SET "accepted" = "issue_row"."accepted"
2472 WHERE "id" = "issue_row"."id";
2473 ELSIF
2474 now() >= "issue_row"."created" + "policy_row"."admission_time"
2475 THEN
2476 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
2477 UPDATE "issue" SET "closed" = now()
2478 WHERE "id" = "issue_row"."id";
2479 END IF;
2480 END IF;
2481 IF
2482 "issue_row"."accepted" NOTNULL AND
2483 "issue_row"."half_frozen" ISNULL
2484 THEN
2485 SELECT
2486 CASE
2487 WHEN "vote_now" * 2 > "issue_row"."population" THEN
2488 TRUE
2489 WHEN "vote_later" * 2 > "issue_row"."population" THEN
2490 FALSE
2491 ELSE NULL
2492 END
2493 INTO "voting_requested_v"
2494 FROM "issue" WHERE "id" = "issue_id_p";
2495 IF
2496 "voting_requested_v" OR (
2497 "voting_requested_v" ISNULL AND
2498 now() >= "issue_row"."accepted" + "policy_row"."discussion_time"
2499 )
2500 THEN
2501 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later
2502 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
2503 WHERE "id" = "issue_row"."id";
2504 END IF;
2505 END IF;
2506 IF
2507 "issue_row"."half_frozen" NOTNULL AND
2508 "issue_row"."fully_frozen" ISNULL AND
2509 now() >= "issue_row"."half_frozen" + "policy_row"."verification_time"
2510 THEN
2511 "issue_row"."fully_frozen" = now(); -- NOTE: "issue_row" used later
2512 PERFORM "freeze_after_snapshot"("issue_id_p");
2513 END IF;
2514 IF
2515 "issue_row"."fully_frozen" NOTNULL AND
2516 now() >= "issue_row"."fully_frozen" + "policy_row"."voting_time"
2517 THEN
2518 PERFORM "close_voting"("issue_id_p");
2519 END IF;
2520 END IF;
2521 RETURN;
2522 END;
2523 $$;
2525 COMMENT ON FUNCTION "check_issue"
2526 ( "issue"."id"%TYPE )
2527 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.';
2530 CREATE FUNCTION "check_everything"()
2531 RETURNS VOID
2532 LANGUAGE 'plpgsql' VOLATILE AS $$
2533 DECLARE
2534 "issue_id_v" "issue"."id"%TYPE;
2535 BEGIN
2536 DELETE FROM "expired_session";
2537 PERFORM "calculate_member_counts"();
2538 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
2539 PERFORM "check_issue"("issue_id_v");
2540 END LOOP;
2541 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
2542 PERFORM "calculate_ranks"("issue_id_v");
2543 END LOOP;
2544 RETURN;
2545 END;
2546 $$;
2548 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.';
2552 COMMIT;