liquid_feedback_core
view core.sql @ 24:85ee75f90ecd
Close issues when verification time elapsed after revoking last initiative
and more comments in check_issue(...) function
and more comments in check_issue(...) function
| author | jbe |
|---|---|
| date | Sat Feb 06 03:34:09 2010 +0100 (2010-02-06) |
| parents | 137c98fa0b4f |
| children | a73ccca7557a |
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 ('beta19-dev', 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", e'\\', e'\\\\'), '*', e'\\*'),
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 -------------------------
56 CREATE TABLE "member" (
57 "id" SERIAL4 PRIMARY KEY,
58 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
59 "login" TEXT NOT NULL UNIQUE,
60 "password" TEXT,
61 "active" BOOLEAN NOT NULL DEFAULT TRUE,
62 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
63 "notify_email" TEXT,
64 "notify_email_unconfirmed" TEXT,
65 "notify_email_secret" TEXT UNIQUE,
66 "notify_email_secret_expiry" TIMESTAMPTZ,
67 "password_reset_secret" TEXT UNIQUE,
68 "password_reset_secret_expiry" TIMESTAMPTZ,
69 "name" TEXT NOT NULL UNIQUE,
70 "identification" TEXT UNIQUE,
71 "organizational_unit" TEXT,
72 "internal_posts" TEXT,
73 "realname" TEXT,
74 "birthday" DATE,
75 "address" TEXT,
76 "email" TEXT,
77 "xmpp_address" TEXT,
78 "website" TEXT,
79 "phone" TEXT,
80 "mobile_phone" TEXT,
81 "profession" TEXT,
82 "external_memberships" TEXT,
83 "external_posts" TEXT,
84 "statement" TEXT,
85 "text_search_data" TSVECTOR );
86 CREATE INDEX "member_active_idx" ON "member" ("active");
87 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
88 CREATE TRIGGER "update_text_search_data"
89 BEFORE INSERT OR UPDATE ON "member"
90 FOR EACH ROW EXECUTE PROCEDURE
91 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
92 "name", "identification", "organizational_unit", "internal_posts",
93 "realname", "external_memberships", "external_posts", "statement" );
95 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
97 COMMENT ON COLUMN "member"."login" IS 'Login name';
98 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
99 COMMENT ON COLUMN "member"."active" IS 'Inactive members can not login and their supports/votes are not counted by the system.';
100 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
101 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
102 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
103 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
104 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
105 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member';
106 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
107 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
108 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
109 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
110 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
111 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
112 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
113 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his homepage within the system';
116 CREATE TABLE "member_history" (
117 "id" SERIAL8 PRIMARY KEY,
118 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
119 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
120 "login" TEXT NOT NULL,
121 "name" TEXT NOT NULL );
123 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and login names of members';
125 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
126 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the name and login had been valid';
129 CREATE TABLE "invite_code" (
130 "code" TEXT PRIMARY KEY,
131 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
132 "used" TIMESTAMPTZ,
133 "member_id" INT4 UNIQUE REFERENCES "member" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
134 "comment" TEXT,
135 CONSTRAINT "only_used_codes_may_refer_to_member" CHECK ("used" NOTNULL OR "member_id" ISNULL) );
137 COMMENT ON TABLE "invite_code" IS 'Invite codes can be used once to create a new member account.';
139 COMMENT ON COLUMN "invite_code"."code" IS 'Secret code';
140 COMMENT ON COLUMN "invite_code"."created" IS 'Time of creation of the secret code';
141 COMMENT ON COLUMN "invite_code"."used" IS 'NULL, if not used yet, otherwise tells when this code was used to create a member account';
142 COMMENT ON COLUMN "invite_code"."member_id" IS 'References the member whose account was created with this code';
143 COMMENT ON COLUMN "invite_code"."comment" IS 'Comment on the code, which is to be used for administrative reasons only';
146 CREATE TABLE "setting" (
147 PRIMARY KEY ("member_id", "key"),
148 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
149 "key" TEXT NOT NULL,
150 "value" TEXT NOT NULL );
151 CREATE INDEX "setting_key_idx" ON "setting" ("key");
153 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific settings for members as a string';
155 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
158 CREATE TABLE "setting_map" (
159 PRIMARY KEY ("member_id", "key", "subkey"),
160 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
161 "key" TEXT NOT NULL,
162 "subkey" TEXT NOT NULL,
163 "value" TEXT NOT NULL );
164 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
166 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
168 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
169 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
170 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
173 CREATE TABLE "member_relation_setting" (
174 PRIMARY KEY ("member_id", "key", "other_member_id"),
175 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
176 "key" TEXT NOT NULL,
177 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
178 "value" TEXT NOT NULL );
180 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific settings related to relations between members as a string';
183 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
185 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
188 CREATE TABLE "member_image" (
189 PRIMARY KEY ("member_id", "image_type", "scaled"),
190 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
191 "image_type" "member_image_type",
192 "scaled" BOOLEAN,
193 "content_type" TEXT,
194 "data" BYTEA NOT NULL );
196 COMMENT ON TABLE "member_image" IS 'Images of members';
198 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
201 CREATE TABLE "member_count" (
202 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
203 "total_count" INT4 NOT NULL );
205 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';
207 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
208 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
211 CREATE TABLE "contact" (
212 PRIMARY KEY ("member_id", "other_member_id"),
213 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
214 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
215 "public" BOOLEAN NOT NULL DEFAULT FALSE,
216 CONSTRAINT "cant_save_yourself_as_contact"
217 CHECK ("member_id" != "other_member_id") );
219 COMMENT ON TABLE "contact" IS 'Contact lists';
221 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
222 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
223 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
226 CREATE TABLE "session" (
227 "ident" TEXT PRIMARY KEY,
228 "additional_secret" TEXT,
229 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
230 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
231 "lang" TEXT );
232 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
234 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';
236 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
237 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
238 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
239 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
242 CREATE TABLE "policy" (
243 "id" SERIAL4 PRIMARY KEY,
244 "index" INT4 NOT NULL,
245 "active" BOOLEAN NOT NULL DEFAULT TRUE,
246 "name" TEXT NOT NULL UNIQUE,
247 "description" TEXT NOT NULL DEFAULT '',
248 "admission_time" INTERVAL NOT NULL,
249 "discussion_time" INTERVAL NOT NULL,
250 "verification_time" INTERVAL NOT NULL,
251 "voting_time" INTERVAL NOT NULL,
252 "issue_quorum_num" INT4 NOT NULL,
253 "issue_quorum_den" INT4 NOT NULL,
254 "initiative_quorum_num" INT4 NOT NULL,
255 "initiative_quorum_den" INT4 NOT NULL,
256 "majority_num" INT4 NOT NULL DEFAULT 1,
257 "majority_den" INT4 NOT NULL DEFAULT 2,
258 "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE );
259 CREATE INDEX "policy_active_idx" ON "policy" ("active");
261 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
263 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
264 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
265 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"';
266 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"';
267 COMMENT ON COLUMN "policy"."verification_time" IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
268 COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "fully_frozen" but not "closed"';
269 COMMENT ON COLUMN "policy"."issue_quorum_num" IS 'Numerator of potential supporter quorum to be reached by one initiative of an issue to be "accepted"';
270 COMMENT ON COLUMN "policy"."issue_quorum_den" IS 'Denominator of potential supporter quorum to be reached by one initiative of an issue to be "accepted"';
271 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
272 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
273 COMMENT ON COLUMN "policy"."majority_num" IS 'Numerator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
274 COMMENT ON COLUMN "policy"."majority_den" IS 'Denominator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
275 COMMENT ON COLUMN "policy"."majority_strict" IS 'If TRUE, then the majority must be strictly greater than "majority_num"/"majority_den", otherwise it may also be equal.';
278 CREATE TABLE "area" (
279 "id" SERIAL4 PRIMARY KEY,
280 "active" BOOLEAN NOT NULL DEFAULT TRUE,
281 "name" TEXT NOT NULL,
282 "description" TEXT NOT NULL DEFAULT '',
283 "direct_member_count" INT4,
284 "member_weight" INT4,
285 "autoreject_weight" INT4,
286 "text_search_data" TSVECTOR );
287 CREATE INDEX "area_active_idx" ON "area" ("active");
288 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
289 CREATE TRIGGER "update_text_search_data"
290 BEFORE INSERT OR UPDATE ON "area"
291 FOR EACH ROW EXECUTE PROCEDURE
292 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
293 "name", "description" );
295 COMMENT ON TABLE "area" IS 'Subject areas';
297 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
298 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"';
299 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
300 COMMENT ON COLUMN "area"."autoreject_weight" IS 'Sum of weight of members using the autoreject feature';
303 CREATE TABLE "area_setting" (
304 PRIMARY KEY ("member_id", "key", "area_id"),
305 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
306 "key" TEXT NOT NULL,
307 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
308 "value" TEXT NOT NULL );
310 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
313 CREATE TABLE "allowed_policy" (
314 PRIMARY KEY ("area_id", "policy_id"),
315 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
316 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
317 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
318 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
320 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
322 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
325 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
327 COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''half_freeze'' = saved state at end of discussion period, ''full_freeze'' = saved state at end of verification period';
330 CREATE TABLE "issue" (
331 "id" SERIAL4 PRIMARY KEY,
332 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
333 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
334 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
335 "accepted" TIMESTAMPTZ,
336 "half_frozen" TIMESTAMPTZ,
337 "fully_frozen" TIMESTAMPTZ,
338 "closed" TIMESTAMPTZ,
339 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
340 "admission_time" INTERVAL NOT NULL,
341 "discussion_time" INTERVAL NOT NULL,
342 "verification_time" INTERVAL NOT NULL,
343 "voting_time" INTERVAL NOT NULL,
344 "snapshot" TIMESTAMPTZ,
345 "latest_snapshot_event" "snapshot_event",
346 "population" INT4,
347 "vote_now" INT4,
348 "vote_later" INT4,
349 "voter_count" INT4,
350 CONSTRAINT "valid_state" CHECK (
351 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
352 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
353 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
354 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
355 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
356 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
357 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ),
358 CONSTRAINT "state_change_order" CHECK (
359 "created" <= "accepted" AND
360 "accepted" <= "half_frozen" AND
361 "half_frozen" <= "fully_frozen" AND
362 "fully_frozen" <= "closed" ),
363 CONSTRAINT "last_snapshot_on_full_freeze"
364 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
365 CONSTRAINT "freeze_requires_snapshot"
366 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
367 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
368 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
369 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
370 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
371 CREATE INDEX "issue_created_idx" ON "issue" ("created");
372 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
373 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
374 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
375 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
376 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
377 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
379 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
381 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
382 COMMENT ON COLUMN "issue"."half_frozen" IS 'Point in time, when "discussion_time" has elapsed, or members voted for voting; Frontends must ensure that for half_frozen issues a) initiatives are not revoked, b) no new drafts are created, c) no initiators are added or removed.';
383 COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed; Frontends must ensure that for fully_frozen issues additionally to the restrictions for half_frozen issues a) initiatives are not created, b) no interest is created or removed, c) no supporters are added or removed, d) no opinions are created, changed or deleted.';
384 COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "admission_time" or "voting_time" have elapsed, and issue is no longer active; Frontends must ensure that for closed issues additionally to the restrictions for half_frozen and fully_frozen issues a) no voter is added or removed to/from the direct_voter table, b) no votes are added, modified or removed.';
385 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
386 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
387 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
388 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
389 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
390 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';
391 COMMENT ON COLUMN "issue"."latest_snapshot_event" IS 'Event type of latest snapshot for issue; Can be used to select the latest snapshot data in the snapshot tables';
392 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
393 COMMENT ON COLUMN "issue"."vote_now" IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"';
394 COMMENT ON COLUMN "issue"."vote_later" IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"';
395 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';
398 CREATE TABLE "issue_setting" (
399 PRIMARY KEY ("member_id", "key", "issue_id"),
400 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
401 "key" TEXT NOT NULL,
402 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
403 "value" TEXT NOT NULL );
405 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
408 CREATE TABLE "initiative" (
409 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
410 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
411 "id" SERIAL4 PRIMARY KEY,
412 "name" TEXT NOT NULL,
413 "discussion_url" TEXT,
414 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
415 "revoked" TIMESTAMPTZ,
416 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
417 "admitted" BOOLEAN,
418 "supporter_count" INT4,
419 "informed_supporter_count" INT4,
420 "satisfied_supporter_count" INT4,
421 "satisfied_informed_supporter_count" INT4,
422 "positive_votes" INT4,
423 "negative_votes" INT4,
424 "agreed" BOOLEAN,
425 "rank" INT4,
426 "text_search_data" TSVECTOR,
427 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
428 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
429 CONSTRAINT "revoked_initiatives_cant_be_admitted"
430 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
431 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"
432 CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "agreed" ISNULL)),
433 CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"
434 CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL),
435 CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"
436 CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) );
437 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
438 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
439 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
440 CREATE TRIGGER "update_text_search_data"
441 BEFORE INSERT OR UPDATE ON "initiative"
442 FOR EACH ROW EXECUTE PROCEDURE
443 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
444 "name", "discussion_url");
446 COMMENT ON TABLE "initiative" IS 'Group of members publishing drafts for resolutions to be passed; Frontends must ensure that initiatives of half_frozen issues are not revoked, and that initiatives of fully_frozen or closed issues are neither revoked nor created.';
448 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
449 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
450 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
451 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
452 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
453 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
454 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
455 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
456 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
457 COMMENT ON COLUMN "initiative"."agreed" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "majority_num"/"majority_den"';
458 COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';
461 CREATE TABLE "initiative_setting" (
462 PRIMARY KEY ("member_id", "key", "initiative_id"),
463 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
464 "key" TEXT NOT NULL,
465 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
466 "value" TEXT NOT NULL );
468 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
471 CREATE TABLE "draft" (
472 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
473 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
474 "id" SERIAL8 PRIMARY KEY,
475 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
476 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
477 "formatting_engine" TEXT,
478 "content" TEXT NOT NULL,
479 "text_search_data" TSVECTOR );
480 CREATE INDEX "draft_created_idx" ON "draft" ("created");
481 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
482 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
483 CREATE TRIGGER "update_text_search_data"
484 BEFORE INSERT OR UPDATE ON "draft"
485 FOR EACH ROW EXECUTE PROCEDURE
486 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
488 COMMENT ON TABLE "draft" IS 'Drafts of initiatives to solve issues; Frontends must ensure that new drafts for initiatives of half_frozen, fully_frozen or closed issues can''t be created.';
490 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
491 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
494 CREATE TABLE "suggestion" (
495 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
496 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
497 "id" SERIAL8 PRIMARY KEY,
498 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
499 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
500 "name" TEXT NOT NULL,
501 "description" TEXT NOT NULL DEFAULT '',
502 "text_search_data" TSVECTOR,
503 "minus2_unfulfilled_count" INT4,
504 "minus2_fulfilled_count" INT4,
505 "minus1_unfulfilled_count" INT4,
506 "minus1_fulfilled_count" INT4,
507 "plus1_unfulfilled_count" INT4,
508 "plus1_fulfilled_count" INT4,
509 "plus2_unfulfilled_count" INT4,
510 "plus2_fulfilled_count" INT4 );
511 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
512 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
513 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
514 CREATE TRIGGER "update_text_search_data"
515 BEFORE INSERT OR UPDATE ON "suggestion"
516 FOR EACH ROW EXECUTE PROCEDURE
517 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
518 "name", "description");
520 COMMENT ON TABLE "suggestion" IS 'Suggestions to initiators, to change the current draft; must not be deleted explicitly, as they vanish automatically if the last opinion is deleted';
522 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
523 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
524 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
525 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
526 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
527 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
528 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
529 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
532 CREATE TABLE "suggestion_setting" (
533 PRIMARY KEY ("member_id", "key", "suggestion_id"),
534 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
535 "key" TEXT NOT NULL,
536 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
537 "value" TEXT NOT NULL );
539 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
542 CREATE TABLE "membership" (
543 PRIMARY KEY ("area_id", "member_id"),
544 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
545 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
546 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
547 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
549 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
551 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';
554 CREATE TABLE "interest" (
555 PRIMARY KEY ("issue_id", "member_id"),
556 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
557 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
558 "autoreject" BOOLEAN NOT NULL,
559 "voting_requested" BOOLEAN );
560 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
562 COMMENT ON TABLE "interest" IS 'Interest of members in a particular issue; Frontends must ensure that interest for fully_frozen or closed issues is not added or removed.';
564 COMMENT ON COLUMN "interest"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure';
565 COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply';
568 CREATE TABLE "initiator" (
569 PRIMARY KEY ("initiative_id", "member_id"),
570 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
571 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
572 "accepted" BOOLEAN );
573 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
575 COMMENT ON TABLE "initiator" IS 'Members who are allowed to post new drafts; Frontends must ensure that initiators are not added or removed from half_frozen, fully_frozen or closed initiatives.';
577 COMMENT ON COLUMN "initiator"."accepted" IS 'If "accepted" is NULL, then the member was invited to be a co-initiator, but has not answered yet. If it is TRUE, the member has accepted the invitation, if it is FALSE, the member has rejected the invitation.';
580 CREATE TABLE "supporter" (
581 "issue_id" INT4 NOT NULL,
582 PRIMARY KEY ("initiative_id", "member_id"),
583 "initiative_id" INT4,
584 "member_id" INT4,
585 "draft_id" INT8 NOT NULL,
586 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
587 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
588 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
590 COMMENT ON TABLE "supporter" IS 'Members who support an initiative (conditionally); Frontends must ensure that supporters are not added or removed from fully_frozen or closed initiatives.';
592 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
595 CREATE TABLE "opinion" (
596 "initiative_id" INT4 NOT NULL,
597 PRIMARY KEY ("suggestion_id", "member_id"),
598 "suggestion_id" INT8,
599 "member_id" INT4,
600 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
601 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
602 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE,
603 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
604 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
606 COMMENT ON TABLE "opinion" IS 'Opinion on suggestions (criticism related to initiatives); Frontends must ensure that opinions are not created modified or deleted when related to fully_frozen or closed issues.';
608 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
611 CREATE TYPE "delegation_scope" AS ENUM ('global', 'area', 'issue');
613 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''global'', ''area'', or ''issue'' (order is relevant)';
616 CREATE TABLE "delegation" (
617 "id" SERIAL8 PRIMARY KEY,
618 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
619 "trustee_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
620 "scope" "delegation_scope" NOT NULL,
621 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
622 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
623 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
624 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
625 ("scope" = 'global' AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
626 ("scope" = 'area' AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
627 ("scope" = 'issue' AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
628 UNIQUE ("area_id", "truster_id", "trustee_id"),
629 UNIQUE ("issue_id", "truster_id", "trustee_id") );
630 CREATE UNIQUE INDEX "delegation_global_truster_id_trustee_id_unique_idx"
631 ON "delegation" ("truster_id", "trustee_id") WHERE "scope" = 'global';
632 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
633 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
635 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
637 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
638 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
641 CREATE TABLE "direct_population_snapshot" (
642 PRIMARY KEY ("issue_id", "event", "member_id"),
643 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
644 "event" "snapshot_event",
645 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
646 "weight" INT4,
647 "interest_exists" BOOLEAN NOT NULL );
648 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
650 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
652 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
653 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
654 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';
657 CREATE TABLE "delegating_population_snapshot" (
658 PRIMARY KEY ("issue_id", "event", "member_id"),
659 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
660 "event" "snapshot_event",
661 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
662 "weight" INT4,
663 "scope" "delegation_scope" NOT NULL,
664 "delegate_member_ids" INT4[] NOT NULL );
665 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
667 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
669 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
670 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
671 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
672 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"';
675 CREATE TABLE "direct_interest_snapshot" (
676 PRIMARY KEY ("issue_id", "event", "member_id"),
677 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
678 "event" "snapshot_event",
679 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
680 "weight" INT4,
681 "voting_requested" BOOLEAN );
682 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
684 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
686 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
687 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
688 COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';
691 CREATE TABLE "delegating_interest_snapshot" (
692 PRIMARY KEY ("issue_id", "event", "member_id"),
693 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
694 "event" "snapshot_event",
695 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
696 "weight" INT4,
697 "scope" "delegation_scope" NOT NULL,
698 "delegate_member_ids" INT4[] NOT NULL );
699 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
701 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
703 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
704 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
705 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
706 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"';
709 CREATE TABLE "direct_supporter_snapshot" (
710 "issue_id" INT4 NOT NULL,
711 PRIMARY KEY ("initiative_id", "event", "member_id"),
712 "initiative_id" INT4,
713 "event" "snapshot_event",
714 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
715 "informed" BOOLEAN NOT NULL,
716 "satisfied" BOOLEAN NOT NULL,
717 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
718 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
719 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
721 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
723 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
724 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
725 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
728 CREATE TABLE "direct_voter" (
729 PRIMARY KEY ("issue_id", "member_id"),
730 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
731 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
732 "weight" INT4,
733 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
734 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
736 COMMENT ON TABLE "direct_voter" IS 'Members having directly voted for/against initiatives of an issue; Frontends must ensure that no voters are added or removed to/from this table when the issue has been closed.';
738 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
739 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
742 CREATE TABLE "delegating_voter" (
743 PRIMARY KEY ("issue_id", "member_id"),
744 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
745 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
746 "weight" INT4,
747 "scope" "delegation_scope" NOT NULL,
748 "delegate_member_ids" INT4[] NOT NULL );
749 CREATE INDEX "delegating_voter_member_id_idx" ON "direct_voter" ("member_id");
751 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
753 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
754 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
755 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"';
758 CREATE TABLE "vote" (
759 "issue_id" INT4 NOT NULL,
760 PRIMARY KEY ("initiative_id", "member_id"),
761 "initiative_id" INT4,
762 "member_id" INT4,
763 "grade" INT4,
764 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
765 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
766 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
768 COMMENT ON TABLE "vote" IS 'Manual and delegated votes without abstentions; Frontends must ensure that no votes are added modified or removed when the issue has been closed.';
770 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.';
773 CREATE TABLE "contingent" (
774 "time_frame" INTERVAL PRIMARY KEY,
775 "text_entry_limit" INT4,
776 "initiative_limit" INT4 );
778 COMMENT ON TABLE "contingent" IS 'Amount of text entries or initiatives a user may create within a given time frame. Only one row needs to be fulfilled for a member to be allowed to post. This table must not be empty.';
780 COMMENT ON COLUMN "contingent"."text_entry_limit" IS 'Number of new drafts or suggestions to be submitted by each member within the given time frame';
781 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
785 --------------------------------
786 -- Writing of history entries --
787 --------------------------------
789 CREATE FUNCTION "write_member_history_trigger"()
790 RETURNS TRIGGER
791 LANGUAGE 'plpgsql' VOLATILE AS $$
792 BEGIN
793 IF NEW."login" != OLD."login" OR NEW."name" != OLD."name" THEN
794 INSERT INTO "member_history" ("member_id", "login", "name")
795 VALUES (NEW."id", OLD."login", OLD."name");
796 END IF;
797 RETURN NULL;
798 END;
799 $$;
801 CREATE TRIGGER "write_member_history"
802 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
803 "write_member_history_trigger"();
805 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
806 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing name or login of a member, create a history entry in "member_history" table';
810 ----------------------------
811 -- Additional constraints --
812 ----------------------------
815 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
816 RETURNS TRIGGER
817 LANGUAGE 'plpgsql' VOLATILE AS $$
818 BEGIN
819 IF NOT EXISTS (
820 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
821 ) THEN
822 --RAISE 'Cannot create issue without an initial initiative.' USING
823 -- ERRCODE = 'integrity_constraint_violation',
824 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
825 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
826 END IF;
827 RETURN NULL;
828 END;
829 $$;
831 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
832 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
833 FOR EACH ROW EXECUTE PROCEDURE
834 "issue_requires_first_initiative_trigger"();
836 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
837 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
840 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
841 RETURNS TRIGGER
842 LANGUAGE 'plpgsql' VOLATILE AS $$
843 DECLARE
844 "reference_lost" BOOLEAN;
845 BEGIN
846 IF TG_OP = 'DELETE' THEN
847 "reference_lost" := TRUE;
848 ELSE
849 "reference_lost" := NEW."issue_id" != OLD."issue_id";
850 END IF;
851 IF
852 "reference_lost" AND NOT EXISTS (
853 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
854 )
855 THEN
856 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
857 END IF;
858 RETURN NULL;
859 END;
860 $$;
862 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
863 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
864 FOR EACH ROW EXECUTE PROCEDURE
865 "last_initiative_deletes_issue_trigger"();
867 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
868 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
871 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
872 RETURNS TRIGGER
873 LANGUAGE 'plpgsql' VOLATILE AS $$
874 BEGIN
875 IF NOT EXISTS (
876 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
877 ) THEN
878 --RAISE 'Cannot create initiative without an initial draft.' USING
879 -- ERRCODE = 'integrity_constraint_violation',
880 -- HINT = 'Create issue, initiative and draft within the same transaction.';
881 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
882 END IF;
883 RETURN NULL;
884 END;
885 $$;
887 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
888 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
889 FOR EACH ROW EXECUTE PROCEDURE
890 "initiative_requires_first_draft_trigger"();
892 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
893 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
896 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
897 RETURNS TRIGGER
898 LANGUAGE 'plpgsql' VOLATILE AS $$
899 DECLARE
900 "reference_lost" BOOLEAN;
901 BEGIN
902 IF TG_OP = 'DELETE' THEN
903 "reference_lost" := TRUE;
904 ELSE
905 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
906 END IF;
907 IF
908 "reference_lost" AND NOT EXISTS (
909 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
910 )
911 THEN
912 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
913 END IF;
914 RETURN NULL;
915 END;
916 $$;
918 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
919 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
920 FOR EACH ROW EXECUTE PROCEDURE
921 "last_draft_deletes_initiative_trigger"();
923 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
924 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
927 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
928 RETURNS TRIGGER
929 LANGUAGE 'plpgsql' VOLATILE AS $$
930 BEGIN
931 IF NOT EXISTS (
932 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
933 ) THEN
934 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
935 END IF;
936 RETURN NULL;
937 END;
938 $$;
940 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
941 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
942 FOR EACH ROW EXECUTE PROCEDURE
943 "suggestion_requires_first_opinion_trigger"();
945 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
946 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
949 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
950 RETURNS TRIGGER
951 LANGUAGE 'plpgsql' VOLATILE AS $$
952 DECLARE
953 "reference_lost" BOOLEAN;
954 BEGIN
955 IF TG_OP = 'DELETE' THEN
956 "reference_lost" := TRUE;
957 ELSE
958 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
959 END IF;
960 IF
961 "reference_lost" AND NOT EXISTS (
962 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
963 )
964 THEN
965 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
966 END IF;
967 RETURN NULL;
968 END;
969 $$;
971 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
972 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
973 FOR EACH ROW EXECUTE PROCEDURE
974 "last_opinion_deletes_suggestion_trigger"();
976 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
977 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
981 ---------------------------------------------------------------
982 -- Ensure that votes are not modified when issues are frozen --
983 ---------------------------------------------------------------
985 -- NOTE: Frontends should ensure this anyway, but in case of programming
986 -- errors the following triggers ensure data integrity.
989 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
990 RETURNS TRIGGER
991 LANGUAGE 'plpgsql' VOLATILE AS $$
992 DECLARE
993 "issue_row" "issue"%ROWTYPE;
994 BEGIN
995 SELECT INTO "issue_row" * FROM "issue"
996 WHERE "id" = NEW."issue_id" FOR SHARE;
997 IF "issue_row"."closed" NOTNULL THEN
998 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
999 END IF;
1000 RETURN NULL;
1001 END;
1002 $$;
1004 CREATE TRIGGER "forbid_changes_on_closed_issue"
1005 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1006 FOR EACH ROW EXECUTE PROCEDURE
1007 "forbid_changes_on_closed_issue_trigger"();
1009 CREATE TRIGGER "forbid_changes_on_closed_issue"
1010 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1011 FOR EACH ROW EXECUTE PROCEDURE
1012 "forbid_changes_on_closed_issue_trigger"();
1014 CREATE TRIGGER "forbid_changes_on_closed_issue"
1015 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1016 FOR EACH ROW EXECUTE PROCEDURE
1017 "forbid_changes_on_closed_issue_trigger"();
1019 COMMENT ON FUNCTION "forbid_changes_on_closed_issue_trigger"() IS 'Implementation of triggers "forbid_changes_on_closed_issue" on tables "direct_voter", "delegating_voter" and "vote"';
1020 COMMENT ON TRIGGER "forbid_changes_on_closed_issue" ON "direct_voter" IS 'Ensures that frontends can''t tamper with votings of closed issues, in case of programming errors';
1021 COMMENT ON TRIGGER "forbid_changes_on_closed_issue" ON "delegating_voter" IS 'Ensures that frontends can''t tamper with votings of closed issues, in case of programming errors';
1022 COMMENT ON TRIGGER "forbid_changes_on_closed_issue" ON "vote" IS 'Ensures that frontends can''t tamper with votings of closed issues, in case of programming errors';
1026 --------------------------------------------------------------------
1027 -- Auto-retrieval of fields only needed for referential integrity --
1028 --------------------------------------------------------------------
1031 CREATE FUNCTION "autofill_issue_id_trigger"()
1032 RETURNS TRIGGER
1033 LANGUAGE 'plpgsql' VOLATILE AS $$
1034 BEGIN
1035 IF NEW."issue_id" ISNULL THEN
1036 SELECT "issue_id" INTO NEW."issue_id"
1037 FROM "initiative" WHERE "id" = NEW."initiative_id";
1038 END IF;
1039 RETURN NEW;
1040 END;
1041 $$;
1043 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1044 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1046 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1047 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1049 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1050 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1051 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1054 CREATE FUNCTION "autofill_initiative_id_trigger"()
1055 RETURNS TRIGGER
1056 LANGUAGE 'plpgsql' VOLATILE AS $$
1057 BEGIN
1058 IF NEW."initiative_id" ISNULL THEN
1059 SELECT "initiative_id" INTO NEW."initiative_id"
1060 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1061 END IF;
1062 RETURN NEW;
1063 END;
1064 $$;
1066 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1067 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1069 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1070 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1074 -----------------------------------------------------
1075 -- Automatic calculation of certain default values --
1076 -----------------------------------------------------
1079 CREATE FUNCTION "copy_timings_trigger"()
1080 RETURNS TRIGGER
1081 LANGUAGE 'plpgsql' VOLATILE AS $$
1082 DECLARE
1083 "policy_row" "policy"%ROWTYPE;
1084 BEGIN
1085 SELECT * INTO "policy_row" FROM "policy"
1086 WHERE "id" = NEW."policy_id";
1087 IF NEW."admission_time" ISNULL THEN
1088 NEW."admission_time" := "policy_row"."admission_time";
1089 END IF;
1090 IF NEW."discussion_time" ISNULL THEN
1091 NEW."discussion_time" := "policy_row"."discussion_time";
1092 END IF;
1093 IF NEW."verification_time" ISNULL THEN
1094 NEW."verification_time" := "policy_row"."verification_time";
1095 END IF;
1096 IF NEW."voting_time" ISNULL THEN
1097 NEW."voting_time" := "policy_row"."voting_time";
1098 END IF;
1099 RETURN NEW;
1100 END;
1101 $$;
1103 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1104 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1106 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1107 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1110 CREATE FUNCTION "copy_autoreject_trigger"()
1111 RETURNS TRIGGER
1112 LANGUAGE 'plpgsql' VOLATILE AS $$
1113 BEGIN
1114 IF NEW."autoreject" ISNULL THEN
1115 SELECT "membership"."autoreject" INTO NEW."autoreject"
1116 FROM "issue" JOIN "membership"
1117 ON "issue"."area_id" = "membership"."area_id"
1118 WHERE "issue"."id" = NEW."issue_id"
1119 AND "membership"."member_id" = NEW."member_id";
1120 END IF;
1121 IF NEW."autoreject" ISNULL THEN
1122 NEW."autoreject" := FALSE;
1123 END IF;
1124 RETURN NEW;
1125 END;
1126 $$;
1128 CREATE TRIGGER "copy_autoreject" BEFORE INSERT OR UPDATE ON "interest"
1129 FOR EACH ROW EXECUTE PROCEDURE "copy_autoreject_trigger"();
1131 COMMENT ON FUNCTION "copy_autoreject_trigger"() IS 'Implementation of trigger "copy_autoreject" on table "interest"';
1132 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';
1135 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
1136 RETURNS TRIGGER
1137 LANGUAGE 'plpgsql' VOLATILE AS $$
1138 BEGIN
1139 IF NEW."draft_id" ISNULL THEN
1140 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1141 WHERE "initiative_id" = NEW."initiative_id";
1142 END IF;
1143 RETURN NEW;
1144 END;
1145 $$;
1147 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1148 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
1150 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
1151 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';
1155 ----------------------------------------
1156 -- Automatic creation of dependencies --
1157 ----------------------------------------
1160 CREATE FUNCTION "autocreate_interest_trigger"()
1161 RETURNS TRIGGER
1162 LANGUAGE 'plpgsql' VOLATILE AS $$
1163 BEGIN
1164 IF NOT EXISTS (
1165 SELECT NULL FROM "initiative" JOIN "interest"
1166 ON "initiative"."issue_id" = "interest"."issue_id"
1167 WHERE "initiative"."id" = NEW."initiative_id"
1168 AND "interest"."member_id" = NEW."member_id"
1169 ) THEN
1170 BEGIN
1171 INSERT INTO "interest" ("issue_id", "member_id")
1172 SELECT "issue_id", NEW."member_id"
1173 FROM "initiative" WHERE "id" = NEW."initiative_id";
1174 EXCEPTION WHEN unique_violation THEN END;
1175 END IF;
1176 RETURN NEW;
1177 END;
1178 $$;
1180 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1181 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1183 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1184 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';
1187 CREATE FUNCTION "autocreate_supporter_trigger"()
1188 RETURNS TRIGGER
1189 LANGUAGE 'plpgsql' VOLATILE AS $$
1190 BEGIN
1191 IF NOT EXISTS (
1192 SELECT NULL FROM "suggestion" JOIN "supporter"
1193 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1194 WHERE "suggestion"."id" = NEW."suggestion_id"
1195 AND "supporter"."member_id" = NEW."member_id"
1196 ) THEN
1197 BEGIN
1198 INSERT INTO "supporter" ("initiative_id", "member_id")
1199 SELECT "initiative_id", NEW."member_id"
1200 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1201 EXCEPTION WHEN unique_violation THEN END;
1202 END IF;
1203 RETURN NEW;
1204 END;
1205 $$;
1207 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1208 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1210 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1211 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.';
1215 ------------------------------------------
1216 -- Views and helper functions for views --
1217 ------------------------------------------
1220 CREATE VIEW "global_delegation" AS
1221 SELECT
1222 "delegation"."id",
1223 "delegation"."truster_id",
1224 "delegation"."trustee_id"
1225 FROM "delegation" JOIN "member"
1226 ON "delegation"."trustee_id" = "member"."id"
1227 WHERE "delegation"."scope" = 'global' AND "member"."active";
1229 COMMENT ON VIEW "global_delegation" IS 'Global delegations to active members';
1232 CREATE VIEW "area_delegation" AS
1233 SELECT "subquery".* FROM (
1234 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1235 "area"."id" AS "area_id",
1236 "delegation"."id",
1237 "delegation"."truster_id",
1238 "delegation"."trustee_id",
1239 "delegation"."scope"
1240 FROM "area" JOIN "delegation"
1241 ON "delegation"."scope" = 'global'
1242 OR "delegation"."area_id" = "area"."id"
1243 ORDER BY
1244 "area"."id",
1245 "delegation"."truster_id",
1246 "delegation"."scope" DESC
1247 ) AS "subquery"
1248 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
1249 WHERE "member"."active";
1251 COMMENT ON VIEW "area_delegation" IS 'Active delegations for areas';
1254 CREATE VIEW "issue_delegation" AS
1255 SELECT "subquery".* FROM (
1256 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1257 "issue"."id" AS "issue_id",
1258 "delegation"."id",
1259 "delegation"."truster_id",
1260 "delegation"."trustee_id",
1261 "delegation"."scope"
1262 FROM "issue" JOIN "delegation"
1263 ON "delegation"."scope" = 'global'
1264 OR "delegation"."area_id" = "issue"."area_id"
1265 OR "delegation"."issue_id" = "issue"."id"
1266 ORDER BY
1267 "issue"."id",
1268 "delegation"."truster_id",
1269 "delegation"."scope" DESC
1270 ) AS "subquery"
1271 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
1272 WHERE "member"."active";
1274 COMMENT ON VIEW "issue_delegation" IS 'Active delegations for issues';
1277 CREATE FUNCTION "membership_weight_with_skipping"
1278 ( "area_id_p" "area"."id"%TYPE,
1279 "member_id_p" "member"."id"%TYPE,
1280 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1281 RETURNS INT4
1282 LANGUAGE 'plpgsql' STABLE AS $$
1283 DECLARE
1284 "sum_v" INT4;
1285 "delegation_row" "area_delegation"%ROWTYPE;
1286 BEGIN
1287 "sum_v" := 1;
1288 FOR "delegation_row" IN
1289 SELECT "area_delegation".*
1290 FROM "area_delegation" LEFT JOIN "membership"
1291 ON "membership"."area_id" = "area_id_p"
1292 AND "membership"."member_id" = "area_delegation"."truster_id"
1293 WHERE "area_delegation"."area_id" = "area_id_p"
1294 AND "area_delegation"."trustee_id" = "member_id_p"
1295 AND "membership"."member_id" ISNULL
1296 LOOP
1297 IF NOT
1298 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1299 THEN
1300 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1301 "area_id_p",
1302 "delegation_row"."truster_id",
1303 "skip_member_ids_p" || "delegation_row"."truster_id"
1304 );
1305 END IF;
1306 END LOOP;
1307 RETURN "sum_v";
1308 END;
1309 $$;
1311 COMMENT ON FUNCTION "membership_weight_with_skipping"
1312 ( "area"."id"%TYPE,
1313 "member"."id"%TYPE,
1314 INT4[] )
1315 IS 'Helper function for "membership_weight" function';
1318 CREATE FUNCTION "membership_weight"
1319 ( "area_id_p" "area"."id"%TYPE,
1320 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1321 RETURNS INT4
1322 LANGUAGE 'plpgsql' STABLE AS $$
1323 BEGIN
1324 RETURN "membership_weight_with_skipping"(
1325 "area_id_p",
1326 "member_id_p",
1327 ARRAY["member_id_p"]
1328 );
1329 END;
1330 $$;
1332 COMMENT ON FUNCTION "membership_weight"
1333 ( "area"."id"%TYPE,
1334 "member"."id"%TYPE )
1335 IS 'Calculates the potential voting weight of a member in a given area';
1338 CREATE VIEW "member_count_view" AS
1339 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1341 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1344 CREATE VIEW "area_member_count" AS
1345 SELECT
1346 "area"."id" AS "area_id",
1347 count("member"."id") AS "direct_member_count",
1348 coalesce(
1349 sum(
1350 CASE WHEN "member"."id" NOTNULL THEN
1351 "membership_weight"("area"."id", "member"."id")
1352 ELSE 0 END
1353 )
1354 ) AS "member_weight",
1355 coalesce(
1356 sum(
1357 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
1358 "membership_weight"("area"."id", "member"."id")
1359 ELSE 0 END
1360 )
1361 ) AS "autoreject_weight"
1362 FROM "area"
1363 LEFT JOIN "membership"
1364 ON "area"."id" = "membership"."area_id"
1365 LEFT JOIN "member"
1366 ON "membership"."member_id" = "member"."id"
1367 AND "member"."active"
1368 GROUP BY "area"."id";
1370 COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"';
1373 CREATE VIEW "opening_draft" AS
1374 SELECT "draft".* FROM (
1375 SELECT
1376 "initiative"."id" AS "initiative_id",
1377 min("draft"."id") AS "draft_id"
1378 FROM "initiative" JOIN "draft"
1379 ON "initiative"."id" = "draft"."initiative_id"
1380 GROUP BY "initiative"."id"
1381 ) AS "subquery"
1382 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1384 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1387 CREATE VIEW "current_draft" AS
1388 SELECT "draft".* FROM (
1389 SELECT
1390 "initiative"."id" AS "initiative_id",
1391 max("draft"."id") AS "draft_id"
1392 FROM "initiative" JOIN "draft"
1393 ON "initiative"."id" = "draft"."initiative_id"
1394 GROUP BY "initiative"."id"
1395 ) AS "subquery"
1396 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1398 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1401 CREATE VIEW "critical_opinion" AS
1402 SELECT * FROM "opinion"
1403 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
1404 OR ("degree" = -2 AND "fulfilled" = TRUE);
1406 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
1409 CREATE VIEW "battle" AS
1410 SELECT
1411 "issue"."id" AS "issue_id",
1412 "winning_initiative"."id" AS "winning_initiative_id",
1413 "losing_initiative"."id" AS "losing_initiative_id",
1414 sum(
1415 CASE WHEN
1416 coalesce("better_vote"."grade", 0) >
1417 coalesce("worse_vote"."grade", 0)
1418 THEN "direct_voter"."weight" ELSE 0 END
1419 ) AS "count"
1420 FROM "issue"
1421 LEFT JOIN "direct_voter"
1422 ON "issue"."id" = "direct_voter"."issue_id"
1423 JOIN "initiative" AS "winning_initiative"
1424 ON "issue"."id" = "winning_initiative"."issue_id"
1425 AND "winning_initiative"."agreed"
1426 JOIN "initiative" AS "losing_initiative"
1427 ON "issue"."id" = "losing_initiative"."issue_id"
1428 AND "losing_initiative"."agreed"
1429 LEFT JOIN "vote" AS "better_vote"
1430 ON "direct_voter"."member_id" = "better_vote"."member_id"
1431 AND "winning_initiative"."id" = "better_vote"."initiative_id"
1432 LEFT JOIN "vote" AS "worse_vote"
1433 ON "direct_voter"."member_id" = "worse_vote"."member_id"
1434 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
1435 WHERE
1436 "winning_initiative"."id" != "losing_initiative"."id"
1437 GROUP BY
1438 "issue"."id",
1439 "winning_initiative"."id",
1440 "losing_initiative"."id";
1442 COMMENT ON VIEW "battle" IS 'Number of members preferring one initiative over another';
1445 CREATE VIEW "expired_session" AS
1446 SELECT * FROM "session" WHERE now() > "expiry";
1448 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
1449 DELETE FROM "session" WHERE "ident" = OLD."ident";
1451 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
1452 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
1455 CREATE VIEW "open_issue" AS
1456 SELECT * FROM "issue" WHERE "closed" ISNULL;
1458 COMMENT ON VIEW "open_issue" IS 'All open issues';
1461 CREATE VIEW "issue_with_ranks_missing" AS
1462 SELECT * FROM "issue"
1463 WHERE "fully_frozen" NOTNULL
1464 AND "closed" NOTNULL
1465 AND "ranks_available" = FALSE;
1467 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
1470 CREATE VIEW "member_contingent" AS
1471 SELECT
1472 "member"."id" AS "member_id",
1473 "contingent"."time_frame",
1474 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
1475 (
1476 SELECT count(1) FROM "draft"
1477 WHERE "draft"."author_id" = "member"."id"
1478 AND "draft"."created" > now() - "contingent"."time_frame"
1479 ) + (
1480 SELECT count(1) FROM "suggestion"
1481 WHERE "suggestion"."author_id" = "member"."id"
1482 AND "suggestion"."created" > now() - "contingent"."time_frame"
1483 )
1484 ELSE NULL END AS "text_entry_count",
1485 "contingent"."text_entry_limit",
1486 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
1487 SELECT count(1) FROM "opening_draft"
1488 WHERE "opening_draft"."author_id" = "member"."id"
1489 AND "opening_draft"."created" > now() - "contingent"."time_frame"
1490 ) ELSE NULL END AS "initiative_count",
1491 "contingent"."initiative_limit"
1492 FROM "member" CROSS JOIN "contingent";
1494 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
1496 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
1497 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
1500 CREATE VIEW "member_contingent_left" AS
1501 SELECT
1502 "member_id",
1503 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
1504 max("initiative_limit" - "initiative_count") AS "initiatives_left"
1505 FROM "member_contingent" GROUP BY "member_id";
1507 COMMENT ON VIEW "member_contingent_left" IS 'Amount of text entries or initiatives which can be posted now instantly by a member. This view should be used by a frontend to determine, if the contingent for posting is exhausted.';
1510 CREATE TYPE "timeline_event" AS ENUM (
1511 'issue_created',
1512 'issue_canceled',
1513 'issue_accepted',
1514 'issue_half_frozen',
1515 'issue_finished_without_voting',
1516 'issue_voting_started',
1517 'issue_finished_after_voting',
1518 'initiative_created',
1519 'initiative_revoked',
1520 'draft_created',
1521 'suggestion_created');
1523 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables';
1526 CREATE VIEW "timeline_issue" AS
1527 SELECT
1528 "created" AS "occurrence",
1529 'issue_created'::"timeline_event" AS "event",
1530 "id" AS "issue_id"
1531 FROM "issue"
1532 UNION ALL
1533 SELECT
1534 "closed" AS "occurrence",
1535 'issue_canceled'::"timeline_event" AS "event",
1536 "id" AS "issue_id"
1537 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
1538 UNION ALL
1539 SELECT
1540 "accepted" AS "occurrence",
1541 'issue_accepted'::"timeline_event" AS "event",
1542 "id" AS "issue_id"
1543 FROM "issue" WHERE "accepted" NOTNULL
1544 UNION ALL
1545 SELECT
1546 "half_frozen" AS "occurrence",
1547 'issue_half_frozen'::"timeline_event" AS "event",
1548 "id" AS "issue_id"
1549 FROM "issue" WHERE "half_frozen" NOTNULL
1550 UNION ALL
1551 SELECT
1552 "fully_frozen" AS "occurrence",
1553 'issue_voting_started'::"timeline_event" AS "event",
1554 "id" AS "issue_id"
1555 FROM "issue"
1556 WHERE "fully_frozen" NOTNULL
1557 AND ("closed" ISNULL OR "closed" != "fully_frozen")
1558 UNION ALL
1559 SELECT
1560 "closed" AS "occurrence",
1561 CASE WHEN "fully_frozen" = "closed" THEN
1562 'issue_finished_without_voting'::"timeline_event"
1563 ELSE
1564 'issue_finished_after_voting'::"timeline_event"
1565 END AS "event",
1566 "id" AS "issue_id"
1567 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
1569 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view';
1572 CREATE VIEW "timeline_initiative" AS
1573 SELECT
1574 "created" AS "occurrence",
1575 'initiative_created'::"timeline_event" AS "event",
1576 "id" AS "initiative_id"
1577 FROM "initiative"
1578 UNION ALL
1579 SELECT
1580 "revoked" AS "occurrence",
1581 'initiative_revoked'::"timeline_event" AS "event",
1582 "id" AS "initiative_id"
1583 FROM "initiative" WHERE "revoked" NOTNULL;
1585 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view';
1588 CREATE VIEW "timeline_draft" AS
1589 SELECT
1590 "created" AS "occurrence",
1591 'draft_created'::"timeline_event" AS "event",
1592 "id" AS "draft_id"
1593 FROM "draft";
1595 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view';
1598 CREATE VIEW "timeline_suggestion" AS
1599 SELECT
1600 "created" AS "occurrence",
1601 'suggestion_created'::"timeline_event" AS "event",
1602 "id" AS "suggestion_id"
1603 FROM "suggestion";
1605 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view';
1608 CREATE VIEW "timeline" AS
1609 SELECT
1610 "occurrence",
1611 "event",
1612 "issue_id",
1613 NULL AS "initiative_id",
1614 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
1615 NULL::INT8 AS "suggestion_id"
1616 FROM "timeline_issue"
1617 UNION ALL
1618 SELECT
1619 "occurrence",
1620 "event",
1621 NULL AS "issue_id",
1622 "initiative_id",
1623 NULL AS "draft_id",
1624 NULL AS "suggestion_id"
1625 FROM "timeline_initiative"
1626 UNION ALL
1627 SELECT
1628 "occurrence",
1629 "event",
1630 NULL AS "issue_id",
1631 NULL AS "initiative_id",
1632 "draft_id",
1633 NULL AS "suggestion_id"
1634 FROM "timeline_draft"
1635 UNION ALL
1636 SELECT
1637 "occurrence",
1638 "event",
1639 NULL AS "issue_id",
1640 NULL AS "initiative_id",
1641 NULL AS "draft_id",
1642 "suggestion_id"
1643 FROM "timeline_suggestion";
1645 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system';
1649 --------------------------------------------------
1650 -- Set returning function for delegation chains --
1651 --------------------------------------------------
1654 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
1655 ('first', 'intermediate', 'last', 'repetition');
1657 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
1660 CREATE TYPE "delegation_chain_row" AS (
1661 "index" INT4,
1662 "member_id" INT4,
1663 "member_active" BOOLEAN,
1664 "participation" BOOLEAN,
1665 "overridden" BOOLEAN,
1666 "scope_in" "delegation_scope",
1667 "scope_out" "delegation_scope",
1668 "loop" "delegation_chain_loop_tag" );
1670 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
1672 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
1673 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';
1674 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
1675 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
1676 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
1677 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
1680 CREATE FUNCTION "delegation_chain"
1681 ( "member_id_p" "member"."id"%TYPE,
1682 "area_id_p" "area"."id"%TYPE,
1683 "issue_id_p" "issue"."id"%TYPE,
1684 "simulate_trustee_id_p" "member"."id"%TYPE )
1685 RETURNS SETOF "delegation_chain_row"
1686 LANGUAGE 'plpgsql' STABLE AS $$
1687 DECLARE
1688 "issue_row" "issue"%ROWTYPE;
1689 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
1690 "loop_member_id_v" "member"."id"%TYPE;
1691 "output_row" "delegation_chain_row";
1692 "output_rows" "delegation_chain_row"[];
1693 "delegation_row" "delegation"%ROWTYPE;
1694 "row_count" INT4;
1695 "i" INT4;
1696 "loop_v" BOOLEAN;
1697 BEGIN
1698 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1699 "visited_member_ids" := '{}';
1700 "loop_member_id_v" := NULL;
1701 "output_rows" := '{}';
1702 "output_row"."index" := 0;
1703 "output_row"."member_id" := "member_id_p";
1704 "output_row"."member_active" := TRUE;
1705 "output_row"."participation" := FALSE;
1706 "output_row"."overridden" := FALSE;
1707 "output_row"."scope_out" := NULL;
1708 LOOP
1709 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
1710 "loop_member_id_v" := "output_row"."member_id";
1711 ELSE
1712 "visited_member_ids" :=
1713 "visited_member_ids" || "output_row"."member_id";
1714 END IF;
1715 IF "output_row"."participation" THEN
1716 "output_row"."overridden" := TRUE;
1717 END IF;
1718 "output_row"."scope_in" := "output_row"."scope_out";
1719 IF EXISTS (
1720 SELECT NULL FROM "member"
1721 WHERE "id" = "output_row"."member_id" AND "active"
1722 ) THEN
1723 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
1724 SELECT * INTO "delegation_row" FROM "delegation"
1725 WHERE "truster_id" = "output_row"."member_id"
1726 AND "scope" = 'global';
1727 ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN
1728 "output_row"."participation" := EXISTS (
1729 SELECT NULL FROM "membership"
1730 WHERE "area_id" = "area_id_p"
1731 AND "member_id" = "output_row"."member_id"
1732 );
1733 SELECT * INTO "delegation_row" FROM "delegation"
1734 WHERE "truster_id" = "output_row"."member_id"
1735 AND ("scope" = 'global' OR "area_id" = "area_id_p")
1736 ORDER BY "scope" DESC;
1737 ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN
1738 "output_row"."participation" := EXISTS (
1739 SELECT NULL FROM "interest"
1740 WHERE "issue_id" = "issue_id_p"
1741 AND "member_id" = "output_row"."member_id"
1742 );
1743 SELECT * INTO "delegation_row" FROM "delegation"
1744 WHERE "truster_id" = "output_row"."member_id"
1745 AND ("scope" = 'global' OR
1746 "area_id" = "issue_row"."area_id" OR
1747 "issue_id" = "issue_id_p"
1748 )
1749 ORDER BY "scope" DESC;
1750 ELSE
1751 RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.';
1752 END IF;
1753 ELSE
1754 "output_row"."member_active" := FALSE;
1755 "output_row"."participation" := FALSE;
1756 "output_row"."scope_out" := NULL;
1757 "delegation_row" := ROW(NULL);
1758 END IF;
1759 IF
1760 "output_row"."member_id" = "member_id_p" AND
1761 "simulate_trustee_id_p" NOTNULL
1762 THEN
1763 "output_row"."scope_out" := CASE
1764 WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global'
1765 WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area'
1766 WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue'
1767 END;
1768 "output_rows" := "output_rows" || "output_row";
1769 "output_row"."member_id" := "simulate_trustee_id_p";
1770 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
1771 "output_row"."scope_out" := "delegation_row"."scope";
1772 "output_rows" := "output_rows" || "output_row";
1773 "output_row"."member_id" := "delegation_row"."trustee_id";
1774 ELSE
1775 "output_row"."scope_out" := NULL;
1776 "output_rows" := "output_rows" || "output_row";
1777 EXIT;
1778 END IF;
1779 EXIT WHEN "loop_member_id_v" NOTNULL;
1780 "output_row"."index" := "output_row"."index" + 1;
1781 END LOOP;
1782 "row_count" := array_upper("output_rows", 1);
1783 "i" := 1;
1784 "loop_v" := FALSE;
1785 LOOP
1786 "output_row" := "output_rows"["i"];
1787 EXIT WHEN "output_row"."member_id" ISNULL;
1788 IF "loop_v" THEN
1789 IF "i" + 1 = "row_count" THEN
1790 "output_row"."loop" := 'last';
1791 ELSIF "i" = "row_count" THEN
1792 "output_row"."loop" := 'repetition';
1793 ELSE
1794 "output_row"."loop" := 'intermediate';
1795 END IF;
1796 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
1797 "output_row"."loop" := 'first';
1798 "loop_v" := TRUE;
1799 END IF;
1800 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
1801 "output_row"."participation" := NULL;
1802 END IF;
1803 RETURN NEXT "output_row";
1804 "i" := "i" + 1;
1805 END LOOP;
1806 RETURN;
1807 END;
1808 $$;
1810 COMMENT ON FUNCTION "delegation_chain"
1811 ( "member"."id"%TYPE,
1812 "area"."id"%TYPE,
1813 "issue"."id"%TYPE,
1814 "member"."id"%TYPE )
1815 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
1817 CREATE FUNCTION "delegation_chain"
1818 ( "member_id_p" "member"."id"%TYPE,
1819 "area_id_p" "area"."id"%TYPE,
1820 "issue_id_p" "issue"."id"%TYPE )
1821 RETURNS SETOF "delegation_chain_row"
1822 LANGUAGE 'plpgsql' STABLE AS $$
1823 DECLARE
1824 "result_row" "delegation_chain_row";
1825 BEGIN
1826 FOR "result_row" IN
1827 SELECT * FROM "delegation_chain"(
1828 "member_id_p", "area_id_p", "issue_id_p", NULL
1829 )
1830 LOOP
1831 RETURN NEXT "result_row";
1832 END LOOP;
1833 RETURN;
1834 END;
1835 $$;
1837 COMMENT ON FUNCTION "delegation_chain"
1838 ( "member"."id"%TYPE,
1839 "area"."id"%TYPE,
1840 "issue"."id"%TYPE )
1841 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
1845 ------------------------------
1846 -- Comparison by vote count --
1847 ------------------------------
1849 CREATE FUNCTION "vote_ratio"
1850 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
1851 "negative_votes_p" "initiative"."negative_votes"%TYPE )
1852 RETURNS FLOAT8
1853 LANGUAGE 'plpgsql' STABLE AS $$
1854 DECLARE
1855 "total_v" INT4;
1856 BEGIN
1857 "total_v" := "positive_votes_p" + "negative_votes_p";
1858 IF "total_v" > 0 THEN
1859 RETURN "positive_votes_p"::FLOAT8 / "total_v"::FLOAT8;
1860 ELSE
1861 RETURN 0.5;
1862 END IF;
1863 END;
1864 $$;
1866 COMMENT ON FUNCTION "vote_ratio"
1867 ( "initiative"."positive_votes"%TYPE,
1868 "initiative"."negative_votes"%TYPE )
1869 IS 'Ratio of positive votes to sum of positive and negative votes; 0.5, if there are neither positive nor negative votes';
1873 ------------------------------------------------
1874 -- Locking for snapshots and voting procedure --
1875 ------------------------------------------------
1877 CREATE FUNCTION "global_lock"() RETURNS VOID
1878 LANGUAGE 'plpgsql' VOLATILE AS $$
1879 BEGIN
1880 -- NOTE: PostgreSQL allows reading, while tables are locked in
1881 -- exclusive move. Transactions should be kept short anyway!
1882 LOCK TABLE "member" IN EXCLUSIVE MODE;
1883 LOCK TABLE "area" IN EXCLUSIVE MODE;
1884 LOCK TABLE "membership" IN EXCLUSIVE MODE;
1885 -- NOTE: "member", "area" and "membership" are locked first to
1886 -- prevent deadlocks in combination with "calculate_member_counts"()
1887 LOCK TABLE "policy" IN EXCLUSIVE MODE;
1888 LOCK TABLE "issue" IN EXCLUSIVE MODE;
1889 LOCK TABLE "initiative" IN EXCLUSIVE MODE;
1890 LOCK TABLE "draft" IN EXCLUSIVE MODE;
1891 LOCK TABLE "suggestion" IN EXCLUSIVE MODE;
1892 LOCK TABLE "interest" IN EXCLUSIVE MODE;
1893 LOCK TABLE "initiator" IN EXCLUSIVE MODE;
1894 LOCK TABLE "supporter" IN EXCLUSIVE MODE;
1895 LOCK TABLE "opinion" IN EXCLUSIVE MODE;
1896 LOCK TABLE "delegation" IN EXCLUSIVE MODE;
1897 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
1898 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
1899 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
1900 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
1901 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
1902 LOCK TABLE "direct_voter" IN EXCLUSIVE MODE;
1903 LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;
1904 LOCK TABLE "vote" IN EXCLUSIVE MODE;
1905 RETURN;
1906 END;
1907 $$;
1909 COMMENT ON FUNCTION "global_lock"() IS 'Locks all tables related to support/voting until end of transaction; read access is still possible though';
1913 -------------------------------
1914 -- Materialize member counts --
1915 -------------------------------
1917 CREATE FUNCTION "calculate_member_counts"()
1918 RETURNS VOID
1919 LANGUAGE 'plpgsql' VOLATILE AS $$
1920 BEGIN
1921 LOCK TABLE "member" IN EXCLUSIVE MODE;
1922 LOCK TABLE "area" IN EXCLUSIVE MODE;
1923 LOCK TABLE "membership" IN EXCLUSIVE MODE;
1924 DELETE FROM "member_count";
1925 INSERT INTO "member_count" ("total_count")
1926 SELECT "total_count" FROM "member_count_view";
1927 UPDATE "area" SET
1928 "direct_member_count" = "view"."direct_member_count",
1929 "member_weight" = "view"."member_weight",
1930 "autoreject_weight" = "view"."autoreject_weight"
1931 FROM "area_member_count" AS "view"
1932 WHERE "view"."area_id" = "area"."id";
1933 RETURN;
1934 END;
1935 $$;
1937 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"';
1941 ------------------------------
1942 -- Calculation of snapshots --
1943 ------------------------------
1945 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
1946 ( "issue_id_p" "issue"."id"%TYPE,
1947 "member_id_p" "member"."id"%TYPE,
1948 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
1949 RETURNS "direct_population_snapshot"."weight"%TYPE
1950 LANGUAGE 'plpgsql' VOLATILE AS $$
1951 DECLARE
1952 "issue_delegation_row" "issue_delegation"%ROWTYPE;
1953 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
1954 "weight_v" INT4;
1955 "sub_weight_v" INT4;
1956 BEGIN
1957 "weight_v" := 0;
1958 FOR "issue_delegation_row" IN
1959 SELECT * FROM "issue_delegation"
1960 WHERE "trustee_id" = "member_id_p"
1961 AND "issue_id" = "issue_id_p"
1962 LOOP
1963 IF NOT EXISTS (
1964 SELECT NULL FROM "direct_population_snapshot"
1965 WHERE "issue_id" = "issue_id_p"
1966 AND "event" = 'periodic'
1967 AND "member_id" = "issue_delegation_row"."truster_id"
1968 ) AND NOT EXISTS (
1969 SELECT NULL FROM "delegating_population_snapshot"
1970 WHERE "issue_id" = "issue_id_p"
1971 AND "event" = 'periodic'
1972 AND "member_id" = "issue_delegation_row"."truster_id"
1973 ) THEN
1974 "delegate_member_ids_v" :=
1975 "member_id_p" || "delegate_member_ids_p";
1976 INSERT INTO "delegating_population_snapshot" (
1977 "issue_id",
1978 "event",
1979 "member_id",
1980 "scope",
1981 "delegate_member_ids"
1982 ) VALUES (
1983 "issue_id_p",
1984 'periodic',
1985 "issue_delegation_row"."truster_id",
1986 "issue_delegation_row"."scope",
1987 "delegate_member_ids_v"
1988 );
1989 "sub_weight_v" := 1 +
1990 "weight_of_added_delegations_for_population_snapshot"(
1991 "issue_id_p",
1992 "issue_delegation_row"."truster_id",
1993 "delegate_member_ids_v"
1994 );
1995 UPDATE "delegating_population_snapshot"
1996 SET "weight" = "sub_weight_v"
1997 WHERE "issue_id" = "issue_id_p"
1998 AND "event" = 'periodic'
1999 AND "member_id" = "issue_delegation_row"."truster_id";
2000 "weight_v" := "weight_v" + "sub_weight_v";
2001 END IF;
2002 END LOOP;
2003 RETURN "weight_v";
2004 END;
2005 $$;
2007 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
2008 ( "issue"."id"%TYPE,
2009 "member"."id"%TYPE,
2010 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2011 IS 'Helper function for "create_population_snapshot" function';
2014 CREATE FUNCTION "create_population_snapshot"
2015 ( "issue_id_p" "issue"."id"%TYPE )
2016 RETURNS VOID
2017 LANGUAGE 'plpgsql' VOLATILE AS $$
2018 DECLARE
2019 "member_id_v" "member"."id"%TYPE;
2020 BEGIN
2021 DELETE FROM "direct_population_snapshot"
2022 WHERE "issue_id" = "issue_id_p"
2023 AND "event" = 'periodic';
2024 DELETE FROM "delegating_population_snapshot"
2025 WHERE "issue_id" = "issue_id_p"
2026 AND "event" = 'periodic';
2027 INSERT INTO "direct_population_snapshot"
2028 ("issue_id", "event", "member_id", "interest_exists")
2029 SELECT DISTINCT ON ("issue_id", "member_id")
2030 "issue_id_p" AS "issue_id",
2031 'periodic' AS "event",
2032 "subquery"."member_id",
2033 "subquery"."interest_exists"
2034 FROM (
2035 SELECT
2036 "member"."id" AS "member_id",
2037 FALSE AS "interest_exists"
2038 FROM "issue"
2039 JOIN "area" ON "issue"."area_id" = "area"."id"
2040 JOIN "membership" ON "area"."id" = "membership"."area_id"
2041 JOIN "member" ON "membership"."member_id" = "member"."id"
2042 WHERE "issue"."id" = "issue_id_p"
2043 AND "member"."active"
2044 UNION
2045 SELECT
2046 "member"."id" AS "member_id",
2047 TRUE AS "interest_exists"
2048 FROM "interest" JOIN "member"
2049 ON "interest"."member_id" = "member"."id"
2050 WHERE "interest"."issue_id" = "issue_id_p"
2051 AND "member"."active"
2052 ) AS "subquery"
2053 ORDER BY
2054 "issue_id_p",
2055 "subquery"."member_id",
2056 "subquery"."interest_exists" DESC;
2057 FOR "member_id_v" IN
2058 SELECT "member_id" FROM "direct_population_snapshot"
2059 WHERE "issue_id" = "issue_id_p"
2060 AND "event" = 'periodic'
2061 LOOP
2062 UPDATE "direct_population_snapshot" SET
2063 "weight" = 1 +
2064 "weight_of_added_delegations_for_population_snapshot"(
2065 "issue_id_p",
2066 "member_id_v",
2067 '{}'
2068 )
2069 WHERE "issue_id" = "issue_id_p"
2070 AND "event" = 'periodic'
2071 AND "member_id" = "member_id_v";
2072 END LOOP;
2073 RETURN;
2074 END;
2075 $$;
2077 COMMENT ON FUNCTION "create_population_snapshot"
2078 ( "issue_id_p" "issue"."id"%TYPE )
2079 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.';
2082 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2083 ( "issue_id_p" "issue"."id"%TYPE,
2084 "member_id_p" "member"."id"%TYPE,
2085 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2086 RETURNS "direct_interest_snapshot"."weight"%TYPE
2087 LANGUAGE 'plpgsql' VOLATILE AS $$
2088 DECLARE
2089 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2090 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2091 "weight_v" INT4;
2092 "sub_weight_v" INT4;
2093 BEGIN
2094 "weight_v" := 0;
2095 FOR "issue_delegation_row" IN
2096 SELECT * FROM "issue_delegation"
2097 WHERE "trustee_id" = "member_id_p"
2098 AND "issue_id" = "issue_id_p"
2099 LOOP
2100 IF NOT EXISTS (
2101 SELECT NULL FROM "direct_interest_snapshot"
2102 WHERE "issue_id" = "issue_id_p"
2103 AND "event" = 'periodic'
2104 AND "member_id" = "issue_delegation_row"."truster_id"
2105 ) AND NOT EXISTS (
2106 SELECT NULL FROM "delegating_interest_snapshot"
2107 WHERE "issue_id" = "issue_id_p"
2108 AND "event" = 'periodic'
2109 AND "member_id" = "issue_delegation_row"."truster_id"
2110 ) THEN
2111 "delegate_member_ids_v" :=
2112 "member_id_p" || "delegate_member_ids_p";
2113 INSERT INTO "delegating_interest_snapshot" (
2114 "issue_id",
2115 "event",
2116 "member_id",
2117 "scope",
2118 "delegate_member_ids"
2119 ) VALUES (
2120 "issue_id_p",
2121 'periodic',
2122 "issue_delegation_row"."truster_id",
2123 "issue_delegation_row"."scope",
2124 "delegate_member_ids_v"
2125 );
2126 "sub_weight_v" := 1 +
2127 "weight_of_added_delegations_for_interest_snapshot"(
2128 "issue_id_p",
2129 "issue_delegation_row"."truster_id",
2130 "delegate_member_ids_v"
2131 );
2132 UPDATE "delegating_interest_snapshot"
2133 SET "weight" = "sub_weight_v"
2134 WHERE "issue_id" = "issue_id_p"
2135 AND "event" = 'periodic'
2136 AND "member_id" = "issue_delegation_row"."truster_id";
2137 "weight_v" := "weight_v" + "sub_weight_v";
2138 END IF;
2139 END LOOP;
2140 RETURN "weight_v";
2141 END;
2142 $$;
2144 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2145 ( "issue"."id"%TYPE,
2146 "member"."id"%TYPE,
2147 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2148 IS 'Helper function for "create_interest_snapshot" function';
2151 CREATE FUNCTION "create_interest_snapshot"
2152 ( "issue_id_p" "issue"."id"%TYPE )
2153 RETURNS VOID
2154 LANGUAGE 'plpgsql' VOLATILE AS $$
2155 DECLARE
2156 "member_id_v" "member"."id"%TYPE;
2157 BEGIN
2158 DELETE FROM "direct_interest_snapshot"
2159 WHERE "issue_id" = "issue_id_p"
2160 AND "event" = 'periodic';
2161 DELETE FROM "delegating_interest_snapshot"
2162 WHERE "issue_id" = "issue_id_p"
2163 AND "event" = 'periodic';
2164 DELETE FROM "direct_supporter_snapshot"
2165 WHERE "issue_id" = "issue_id_p"
2166 AND "event" = 'periodic';
2167 INSERT INTO "direct_interest_snapshot"
2168 ("issue_id", "event", "member_id", "voting_requested")
2169 SELECT
2170 "issue_id_p" AS "issue_id",
2171 'periodic' AS "event",
2172 "member"."id" AS "member_id",
2173 "interest"."voting_requested"
2174 FROM "interest" JOIN "member"
2175 ON "interest"."member_id" = "member"."id"
2176 WHERE "interest"."issue_id" = "issue_id_p"
2177 AND "member"."active";
2178 FOR "member_id_v" IN
2179 SELECT "member_id" FROM "direct_interest_snapshot"
2180 WHERE "issue_id" = "issue_id_p"
2181 AND "event" = 'periodic'
2182 LOOP
2183 UPDATE "direct_interest_snapshot" SET
2184 "weight" = 1 +
2185 "weight_of_added_delegations_for_interest_snapshot"(
2186 "issue_id_p",
2187 "member_id_v",
2188 '{}'
2189 )
2190 WHERE "issue_id" = "issue_id_p"
2191 AND "event" = 'periodic'
2192 AND "member_id" = "member_id_v";
2193 END LOOP;
2194 INSERT INTO "direct_supporter_snapshot"
2195 ( "issue_id", "initiative_id", "event", "member_id",
2196 "informed", "satisfied" )
2197 SELECT
2198 "issue_id_p" AS "issue_id",
2199 "initiative"."id" AS "initiative_id",
2200 'periodic' AS "event",
2201 "member"."id" AS "member_id",
2202 "supporter"."draft_id" = "current_draft"."id" AS "informed",
2203 NOT EXISTS (
2204 SELECT NULL FROM "critical_opinion"
2205 WHERE "initiative_id" = "initiative"."id"
2206 AND "member_id" = "member"."id"
2207 ) AS "satisfied"
2208 FROM "supporter"
2209 JOIN "member"
2210 ON "supporter"."member_id" = "member"."id"
2211 JOIN "initiative"
2212 ON "supporter"."initiative_id" = "initiative"."id"
2213 JOIN "current_draft"
2214 ON "initiative"."id" = "current_draft"."initiative_id"
2215 JOIN "direct_interest_snapshot"
2216 ON "member"."id" = "direct_interest_snapshot"."member_id"
2217 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
2218 AND "event" = 'periodic'
2219 WHERE "member"."active"
2220 AND "initiative"."issue_id" = "issue_id_p";
2221 RETURN;
2222 END;
2223 $$;
2225 COMMENT ON FUNCTION "create_interest_snapshot"
2226 ( "issue"."id"%TYPE )
2227 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.';
2230 CREATE FUNCTION "create_snapshot"
2231 ( "issue_id_p" "issue"."id"%TYPE )
2232 RETURNS VOID
2233 LANGUAGE 'plpgsql' VOLATILE AS $$
2234 DECLARE
2235 "initiative_id_v" "initiative"."id"%TYPE;
2236 "suggestion_id_v" "suggestion"."id"%TYPE;
2237 BEGIN
2238 PERFORM "global_lock"();
2239 PERFORM "create_population_snapshot"("issue_id_p");
2240 PERFORM "create_interest_snapshot"("issue_id_p");
2241 UPDATE "issue" SET
2242 "snapshot" = now(),
2243 "latest_snapshot_event" = 'periodic',
2244 "population" = (
2245 SELECT coalesce(sum("weight"), 0)
2246 FROM "direct_population_snapshot"
2247 WHERE "issue_id" = "issue_id_p"
2248 AND "event" = 'periodic'
2249 ),
2250 "vote_now" = (
2251 SELECT coalesce(sum("weight"), 0)
2252 FROM "direct_interest_snapshot"
2253 WHERE "issue_id" = "issue_id_p"
2254 AND "event" = 'periodic'
2255 AND "voting_requested" = TRUE
2256 ),
2257 "vote_later" = (
2258 SELECT coalesce(sum("weight"), 0)
2259 FROM "direct_interest_snapshot"
2260 WHERE "issue_id" = "issue_id_p"
2261 AND "event" = 'periodic'
2262 AND "voting_requested" = FALSE
2263 )
2264 WHERE "id" = "issue_id_p";
2265 FOR "initiative_id_v" IN
2266 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
2267 LOOP
2268 UPDATE "initiative" SET
2269 "supporter_count" = (
2270 SELECT coalesce(sum("di"."weight"), 0)
2271 FROM "direct_interest_snapshot" AS "di"
2272 JOIN "direct_supporter_snapshot" AS "ds"
2273 ON "di"."member_id" = "ds"."member_id"
2274 WHERE "di"."issue_id" = "issue_id_p"
2275 AND "di"."event" = 'periodic'
2276 AND "ds"."initiative_id" = "initiative_id_v"
2277 AND "ds"."event" = 'periodic'
2278 ),
2279 "informed_supporter_count" = (
2280 SELECT coalesce(sum("di"."weight"), 0)
2281 FROM "direct_interest_snapshot" AS "di"
2282 JOIN "direct_supporter_snapshot" AS "ds"
2283 ON "di"."member_id" = "ds"."member_id"
2284 WHERE "di"."issue_id" = "issue_id_p"
2285 AND "di"."event" = 'periodic'
2286 AND "ds"."initiative_id" = "initiative_id_v"
2287 AND "ds"."event" = 'periodic'
2288 AND "ds"."informed"
2289 ),
2290 "satisfied_supporter_count" = (
2291 SELECT coalesce(sum("di"."weight"), 0)
2292 FROM "direct_interest_snapshot" AS "di"
2293 JOIN "direct_supporter_snapshot" AS "ds"
2294 ON "di"."member_id" = "ds"."member_id"
2295 WHERE "di"."issue_id" = "issue_id_p"
2296 AND "di"."event" = 'periodic'
2297 AND "ds"."initiative_id" = "initiative_id_v"
2298 AND "ds"."event" = 'periodic'
2299 AND "ds"."satisfied"
2300 ),
2301 "satisfied_informed_supporter_count" = (
2302 SELECT coalesce(sum("di"."weight"), 0)
2303 FROM "direct_interest_snapshot" AS "di"
2304 JOIN "direct_supporter_snapshot" AS "ds"
2305 ON "di"."member_id" = "ds"."member_id"
2306 WHERE "di"."issue_id" = "issue_id_p"
2307 AND "di"."event" = 'periodic'
2308 AND "ds"."initiative_id" = "initiative_id_v"
2309 AND "ds"."event" = 'periodic'
2310 AND "ds"."informed"
2311 AND "ds"."satisfied"
2312 )
2313 WHERE "id" = "initiative_id_v";
2314 FOR "suggestion_id_v" IN
2315 SELECT "id" FROM "suggestion"
2316 WHERE "initiative_id" = "initiative_id_v"
2317 LOOP
2318 UPDATE "suggestion" SET
2319 "minus2_unfulfilled_count" = (
2320 SELECT coalesce(sum("snapshot"."weight"), 0)
2321 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2322 ON "opinion"."member_id" = "snapshot"."member_id"
2323 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2324 AND "snapshot"."issue_id" = "issue_id_p"
2325 AND "opinion"."degree" = -2
2326 AND "opinion"."fulfilled" = FALSE
2327 ),
2328 "minus2_fulfilled_count" = (
2329 SELECT coalesce(sum("snapshot"."weight"), 0)
2330 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2331 ON "opinion"."member_id" = "snapshot"."member_id"
2332 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2333 AND "snapshot"."issue_id" = "issue_id_p"
2334 AND "opinion"."degree" = -2
2335 AND "opinion"."fulfilled" = TRUE
2336 ),
2337 "minus1_unfulfilled_count" = (
2338 SELECT coalesce(sum("snapshot"."weight"), 0)
2339 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2340 ON "opinion"."member_id" = "snapshot"."member_id"
2341 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2342 AND "snapshot"."issue_id" = "issue_id_p"
2343 AND "opinion"."degree" = -1
2344 AND "opinion"."fulfilled" = FALSE
2345 ),
2346 "minus1_fulfilled_count" = (
2347 SELECT coalesce(sum("snapshot"."weight"), 0)
2348 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2349 ON "opinion"."member_id" = "snapshot"."member_id"
2350 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2351 AND "snapshot"."issue_id" = "issue_id_p"
2352 AND "opinion"."degree" = -1
2353 AND "opinion"."fulfilled" = TRUE
2354 ),
2355 "plus1_unfulfilled_count" = (
2356 SELECT coalesce(sum("snapshot"."weight"), 0)
2357 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2358 ON "opinion"."member_id" = "snapshot"."member_id"
2359 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2360 AND "snapshot"."issue_id" = "issue_id_p"
2361 AND "opinion"."degree" = 1
2362 AND "opinion"."fulfilled" = FALSE
2363 ),
2364 "plus1_fulfilled_count" = (
2365 SELECT coalesce(sum("snapshot"."weight"), 0)
2366 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2367 ON "opinion"."member_id" = "snapshot"."member_id"
2368 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2369 AND "snapshot"."issue_id" = "issue_id_p"
2370 AND "opinion"."degree" = 1
2371 AND "opinion"."fulfilled" = TRUE
2372 ),
2373 "plus2_unfulfilled_count" = (
2374 SELECT coalesce(sum("snapshot"."weight"), 0)
2375 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2376 ON "opinion"."member_id" = "snapshot"."member_id"
2377 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2378 AND "snapshot"."issue_id" = "issue_id_p"
2379 AND "opinion"."degree" = 2
2380 AND "opinion"."fulfilled" = FALSE
2381 ),
2382 "plus2_fulfilled_count" = (
2383 SELECT coalesce(sum("snapshot"."weight"), 0)
2384 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2385 ON "opinion"."member_id" = "snapshot"."member_id"
2386 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2387 AND "snapshot"."issue_id" = "issue_id_p"
2388 AND "opinion"."degree" = 2
2389 AND "opinion"."fulfilled" = TRUE
2390 )
2391 WHERE "suggestion"."id" = "suggestion_id_v";
2392 END LOOP;
2393 END LOOP;
2394 RETURN;
2395 END;
2396 $$;
2398 COMMENT ON FUNCTION "create_snapshot"
2399 ( "issue"."id"%TYPE )
2400 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.';
2403 CREATE FUNCTION "set_snapshot_event"
2404 ( "issue_id_p" "issue"."id"%TYPE,
2405 "event_p" "snapshot_event" )
2406 RETURNS VOID
2407 LANGUAGE 'plpgsql' VOLATILE AS $$
2408 DECLARE
2409 "event_v" "issue"."latest_snapshot_event"%TYPE;
2410 BEGIN
2411 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
2412 WHERE "id" = "issue_id_p" FOR UPDATE;
2413 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
2414 WHERE "id" = "issue_id_p";
2415 UPDATE "direct_population_snapshot" SET "event" = "event_p"
2416 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2417 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
2418 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2419 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
2420 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2421 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
2422 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2423 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
2424 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2425 RETURN;
2426 END;
2427 $$;
2429 COMMENT ON FUNCTION "set_snapshot_event"
2430 ( "issue"."id"%TYPE,
2431 "snapshot_event" )
2432 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
2436 ---------------------
2437 -- Freezing issues --
2438 ---------------------
2440 CREATE FUNCTION "freeze_after_snapshot"
2441 ( "issue_id_p" "issue"."id"%TYPE )
2442 RETURNS VOID
2443 LANGUAGE 'plpgsql' VOLATILE AS $$
2444 DECLARE
2445 "issue_row" "issue"%ROWTYPE;
2446 "policy_row" "policy"%ROWTYPE;
2447 "initiative_row" "initiative"%ROWTYPE;
2448 BEGIN
2449 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2450 SELECT * INTO "policy_row"
2451 FROM "policy" WHERE "id" = "issue_row"."policy_id";
2452 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
2453 UPDATE "issue" SET
2454 "accepted" = coalesce("accepted", now()),
2455 "half_frozen" = coalesce("half_frozen", now()),
2456 "fully_frozen" = now()
2457 WHERE "id" = "issue_id_p";
2458 FOR "initiative_row" IN
2459 SELECT * FROM "initiative"
2460 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
2461 LOOP
2462 IF
2463 "initiative_row"."satisfied_supporter_count" > 0 AND
2464 "initiative_row"."satisfied_supporter_count" *
2465 "policy_row"."initiative_quorum_den" >=
2466 "issue_row"."population" * "policy_row"."initiative_quorum_num"
2467 THEN
2468 UPDATE "initiative" SET "admitted" = TRUE
2469 WHERE "id" = "initiative_row"."id";
2470 ELSE
2471 UPDATE "initiative" SET "admitted" = FALSE
2472 WHERE "id" = "initiative_row"."id";
2473 END IF;
2474 END LOOP;
2475 IF NOT EXISTS (
2476 SELECT NULL FROM "initiative"
2477 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
2478 ) THEN
2479 PERFORM "close_voting"("issue_id_p");
2480 END IF;
2481 RETURN;
2482 END;
2483 $$;
2485 COMMENT ON FUNCTION "freeze_after_snapshot"
2486 ( "issue"."id"%TYPE )
2487 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
2490 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
2491 RETURNS VOID
2492 LANGUAGE 'plpgsql' VOLATILE AS $$
2493 DECLARE
2494 "issue_row" "issue"%ROWTYPE;
2495 BEGIN
2496 PERFORM "create_snapshot"("issue_id_p");
2497 PERFORM "freeze_after_snapshot"("issue_id_p");
2498 RETURN;
2499 END;
2500 $$;
2502 COMMENT ON FUNCTION "freeze_after_snapshot"
2503 ( "issue"."id"%TYPE )
2504 IS 'Freeze an issue manually (fully) and start voting';
2508 -----------------------
2509 -- Counting of votes --
2510 -----------------------
2513 CREATE FUNCTION "weight_of_added_vote_delegations"
2514 ( "issue_id_p" "issue"."id"%TYPE,
2515 "member_id_p" "member"."id"%TYPE,
2516 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
2517 RETURNS "direct_voter"."weight"%TYPE
2518 LANGUAGE 'plpgsql' VOLATILE AS $$
2519 DECLARE
2520 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2521 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
2522 "weight_v" INT4;
2523 "sub_weight_v" INT4;
2524 BEGIN
2525 "weight_v" := 0;
2526 FOR "issue_delegation_row" IN
2527 SELECT * FROM "issue_delegation"
2528 WHERE "trustee_id" = "member_id_p"
2529 AND "issue_id" = "issue_id_p"
2530 LOOP
2531 IF NOT EXISTS (
2532 SELECT NULL FROM "direct_voter"
2533 WHERE "member_id" = "issue_delegation_row"."truster_id"
2534 AND "issue_id" = "issue_id_p"
2535 ) AND NOT EXISTS (
2536 SELECT NULL FROM "delegating_voter"
2537 WHERE "member_id" = "issue_delegation_row"."truster_id"
2538 AND "issue_id" = "issue_id_p"
2539 ) THEN
2540 "delegate_member_ids_v" :=
2541 "member_id_p" || "delegate_member_ids_p";
2542 INSERT INTO "delegating_voter" (
2543 "issue_id",
2544 "member_id",
2545 "scope",
2546 "delegate_member_ids"
2547 ) VALUES (
2548 "issue_id_p",
2549 "issue_delegation_row"."truster_id",
2550 "issue_delegation_row"."scope",
2551 "delegate_member_ids_v"
2552 );
2553 "sub_weight_v" := 1 +
2554 "weight_of_added_vote_delegations"(
2555 "issue_id_p",
2556 "issue_delegation_row"."truster_id",
2557 "delegate_member_ids_v"
2558 );
2559 UPDATE "delegating_voter"
2560 SET "weight" = "sub_weight_v"
2561 WHERE "issue_id" = "issue_id_p"
2562 AND "member_id" = "issue_delegation_row"."truster_id";
2563 "weight_v" := "weight_v" + "sub_weight_v";
2564 END IF;
2565 END LOOP;
2566 RETURN "weight_v";
2567 END;
2568 $$;
2570 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
2571 ( "issue"."id"%TYPE,
2572 "member"."id"%TYPE,
2573 "delegating_voter"."delegate_member_ids"%TYPE )
2574 IS 'Helper function for "add_vote_delegations" function';
2577 CREATE FUNCTION "add_vote_delegations"
2578 ( "issue_id_p" "issue"."id"%TYPE )
2579 RETURNS VOID
2580 LANGUAGE 'plpgsql' VOLATILE AS $$
2581 DECLARE
2582 "member_id_v" "member"."id"%TYPE;
2583 BEGIN
2584 FOR "member_id_v" IN
2585 SELECT "member_id" FROM "direct_voter"
2586 WHERE "issue_id" = "issue_id_p"
2587 LOOP
2588 UPDATE "direct_voter" SET
2589 "weight" = "weight" + "weight_of_added_vote_delegations"(
2590 "issue_id_p",
2591 "member_id_v",
2592 '{}'
2593 )
2594 WHERE "member_id" = "member_id_v"
2595 AND "issue_id" = "issue_id_p";
2596 END LOOP;
2597 RETURN;
2598 END;
2599 $$;
2601 COMMENT ON FUNCTION "add_vote_delegations"
2602 ( "issue_id_p" "issue"."id"%TYPE )
2603 IS 'Helper function for "close_voting" function';
2606 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
2607 RETURNS VOID
2608 LANGUAGE 'plpgsql' VOLATILE AS $$
2609 DECLARE
2610 "issue_row" "issue"%ROWTYPE;
2611 "member_id_v" "member"."id"%TYPE;
2612 BEGIN
2613 PERFORM "global_lock"();
2614 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2615 DELETE FROM "delegating_voter"
2616 WHERE "issue_id" = "issue_id_p";
2617 DELETE FROM "direct_voter"
2618 WHERE "issue_id" = "issue_id_p"
2619 AND "autoreject" = TRUE;
2620 DELETE FROM "direct_voter" USING "member"
2621 WHERE "direct_voter"."member_id" = "member"."id"
2622 AND "direct_voter"."issue_id" = "issue_id_p"
2623 AND "member"."active" = FALSE;
2624 UPDATE "direct_voter" SET "weight" = 1
2625 WHERE "issue_id" = "issue_id_p";
2626 PERFORM "add_vote_delegations"("issue_id_p");
2627 FOR "member_id_v" IN
2628 SELECT "interest"."member_id"
2629 FROM "interest"
2630 LEFT JOIN "direct_voter"
2631 ON "interest"."member_id" = "direct_voter"."member_id"
2632 AND "interest"."issue_id" = "direct_voter"."issue_id"
2633 LEFT JOIN "delegating_voter"
2634 ON "interest"."member_id" = "delegating_voter"."member_id"
2635 AND "interest"."issue_id" = "delegating_voter"."issue_id"
2636 WHERE "interest"."issue_id" = "issue_id_p"
2637 AND "interest"."autoreject" = TRUE
2638 AND "direct_voter"."member_id" ISNULL
2639 AND "delegating_voter"."member_id" ISNULL
2640 UNION SELECT "membership"."member_id"
2641 FROM "membership"
2642 LEFT JOIN "interest"
2643 ON "membership"."member_id" = "interest"."member_id"
2644 AND "interest"."issue_id" = "issue_id_p"
2645 LEFT JOIN "direct_voter"
2646 ON "membership"."member_id" = "direct_voter"."member_id"
2647 AND "direct_voter"."issue_id" = "issue_id_p"
2648 LEFT JOIN "delegating_voter"
2649 ON "membership"."member_id" = "delegating_voter"."member_id"
2650 AND "delegating_voter"."issue_id" = "issue_id_p"
2651 WHERE "membership"."area_id" = "issue_row"."area_id"
2652 AND "membership"."autoreject" = TRUE
2653 AND "interest"."autoreject" ISNULL
2654 AND "direct_voter"."member_id" ISNULL
2655 AND "delegating_voter"."member_id" ISNULL
2656 LOOP
2657 INSERT INTO "direct_voter"
2658 ("member_id", "issue_id", "weight", "autoreject") VALUES
2659 ("member_id_v", "issue_id_p", 1, TRUE);
2660 INSERT INTO "vote" (
2661 "member_id",
2662 "issue_id",
2663 "initiative_id",
2664 "grade"
2665 ) SELECT
2666 "member_id_v" AS "member_id",
2667 "issue_id_p" AS "issue_id",
2668 "id" AS "initiative_id",
2669 -1 AS "grade"
2670 FROM "initiative" WHERE "issue_id" = "issue_id_p";
2671 END LOOP;
2672 PERFORM "add_vote_delegations"("issue_id_p");
2673 UPDATE "issue" SET
2674 "voter_count" = (
2675 SELECT coalesce(sum("weight"), 0)
2676 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
2677 )
2678 WHERE "id" = "issue_id_p";
2679 UPDATE "initiative" SET
2680 "positive_votes" = "vote_counts"."positive_votes",
2681 "negative_votes" = "vote_counts"."negative_votes",
2682 "agreed" = CASE WHEN "majority_strict" THEN
2683 "vote_counts"."positive_votes" * "majority_den" >
2684 "majority_num" *
2685 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
2686 ELSE
2687 "vote_counts"."positive_votes" * "majority_den" >=
2688 "majority_num" *
2689 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
2690 END
2691 FROM
2692 ( SELECT
2693 "initiative"."id" AS "initiative_id",
2694 coalesce(
2695 sum(
2696 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
2697 ),
2698 0
2699 ) AS "positive_votes",
2700 coalesce(
2701 sum(
2702 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
2703 ),
2704 0
2705 ) AS "negative_votes"
2706 FROM "initiative"
2707 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
2708 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
2709 LEFT JOIN "direct_voter"
2710 ON "direct_voter"."issue_id" = "initiative"."issue_id"
2711 LEFT JOIN "vote"
2712 ON "vote"."initiative_id" = "initiative"."id"
2713 AND "vote"."member_id" = "direct_voter"."member_id"
2714 WHERE "initiative"."issue_id" = "issue_id_p"
2715 AND "initiative"."admitted" -- NOTE: NULL case is handled too
2716 GROUP BY "initiative"."id"
2717 ) AS "vote_counts",
2718 "issue",
2719 "policy"
2720 WHERE "vote_counts"."initiative_id" = "initiative"."id"
2721 AND "issue"."id" = "initiative"."issue_id"
2722 AND "policy"."id" = "issue"."policy_id";
2723 UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p";
2724 END;
2725 $$;
2727 COMMENT ON FUNCTION "close_voting"
2728 ( "issue"."id"%TYPE )
2729 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.';
2732 CREATE FUNCTION "init_array"("dim_p" INTEGER)
2733 RETURNS INT4[]
2734 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2735 DECLARE
2736 "i" INTEGER;
2737 "ary_text_v" TEXT;
2738 BEGIN
2739 IF "dim_p" >= 1 THEN
2740 "ary_text_v" := '{NULL';
2741 "i" := "dim_p";
2742 LOOP
2743 "i" := "i" - 1;
2744 EXIT WHEN "i" = 0;
2745 "ary_text_v" := "ary_text_v" || ',NULL';
2746 END LOOP;
2747 "ary_text_v" := "ary_text_v" || '}';
2748 RETURN "ary_text_v"::INT4[][];
2749 ELSE
2750 RAISE EXCEPTION 'Dimension needs to be at least 1.';
2751 END IF;
2752 END;
2753 $$;
2755 COMMENT ON FUNCTION "init_array"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
2758 CREATE FUNCTION "init_square_matrix"("dim_p" INTEGER)
2759 RETURNS INT4[][]
2760 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2761 DECLARE
2762 "i" INTEGER;
2763 "row_text_v" TEXT;
2764 "ary_text_v" TEXT;
2765 BEGIN
2766 IF "dim_p" >= 1 THEN
2767 "row_text_v" := '{NULL';
2768 "i" := "dim_p";
2769 LOOP
2770 "i" := "i" - 1;
2771 EXIT WHEN "i" = 0;
2772 "row_text_v" := "row_text_v" || ',NULL';
2773 END LOOP;
2774 "row_text_v" := "row_text_v" || '}';
2775 "ary_text_v" := '{' || "row_text_v";
2776 "i" := "dim_p";
2777 LOOP
2778 "i" := "i" - 1;
2779 EXIT WHEN "i" = 0;
2780 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
2781 END LOOP;
2782 "ary_text_v" := "ary_text_v" || '}';
2783 RETURN "ary_text_v"::INT4[][];
2784 ELSE
2785 RAISE EXCEPTION 'Dimension needs to be at least 1.';
2786 END IF;
2787 END;
2788 $$;
2790 COMMENT ON FUNCTION "init_square_matrix"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
2793 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
2794 RETURNS VOID
2795 LANGUAGE 'plpgsql' VOLATILE AS $$
2796 DECLARE
2797 "dimension_v" INTEGER;
2798 "matrix" INT4[][];
2799 "i" INTEGER;
2800 "j" INTEGER;
2801 "k" INTEGER;
2802 "battle_row" "battle"%ROWTYPE;
2803 "rank_ary" INT4[];
2804 "rank_v" INT4;
2805 "done_v" INTEGER;
2806 "winners_ary" INTEGER[];
2807 "initiative_id_v" "initiative"."id"%TYPE;
2808 BEGIN
2809 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2810 -- Prepare matrix for Schulze-Method:
2811 SELECT count(1) INTO "dimension_v" FROM "initiative"
2812 WHERE "issue_id" = "issue_id_p" AND "agreed";
2813 IF "dimension_v" = 1 THEN
2814 UPDATE "initiative" SET "rank" = 1
2815 WHERE "issue_id" = "issue_id_p" AND "agreed";
2816 ELSIF "dimension_v" > 1 THEN
2817 "matrix" := "init_square_matrix"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2818 "i" := 1;
2819 "j" := 2;
2820 -- Fill matrix with data from "battle" view
2821 FOR "battle_row" IN
2822 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
2823 ORDER BY "winning_initiative_id", "losing_initiative_id"
2824 LOOP
2825 "matrix"["i"]["j"] := "battle_row"."count";
2826 IF "j" = "dimension_v" THEN
2827 "i" := "i" + 1;
2828 "j" := 1;
2829 ELSE
2830 "j" := "j" + 1;
2831 IF "j" = "i" THEN
2832 "j" := "j" + 1;
2833 END IF;
2834 END IF;
2835 END LOOP;
2836 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
2837 RAISE EXCEPTION 'Wrong battle count (should not happen)';
2838 END IF;
2839 -- Delete losers from matrix:
2840 "i" := 1;
2841 LOOP
2842 "j" := "i" + 1;
2843 LOOP
2844 IF "i" != "j" THEN
2845 IF "matrix"["i"]["j"] < "matrix"["j"]["i"] THEN
2846 "matrix"["i"]["j"] := 0;
2847 ELSIF matrix[j][i] < matrix[i][j] THEN
2848 "matrix"["j"]["i"] := 0;
2849 ELSE
2850 "matrix"["i"]["j"] := 0;
2851 "matrix"["j"]["i"] := 0;
2852 END IF;
2853 END IF;
2854 EXIT WHEN "j" = "dimension_v";
2855 "j" := "j" + 1;
2856 END LOOP;
2857 EXIT WHEN "i" = "dimension_v" - 1;
2858 "i" := "i" + 1;
2859 END LOOP;
2860 -- Find best paths:
2861 "i" := 1;
2862 LOOP
2863 "j" := 1;
2864 LOOP
2865 IF "i" != "j" THEN
2866 "k" := 1;
2867 LOOP
2868 IF "i" != "k" AND "j" != "k" THEN
2869 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
2870 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
2871 "matrix"["j"]["k"] := "matrix"["j"]["i"];
2872 END IF;
2873 ELSE
2874 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
2875 "matrix"["j"]["k"] := "matrix"["i"]["k"];
2876 END IF;
2877 END IF;
2878 END IF;
2879 EXIT WHEN "k" = "dimension_v";
2880 "k" := "k" + 1;
2881 END LOOP;
2882 END IF;
2883 EXIT WHEN "j" = "dimension_v";
2884 "j" := "j" + 1;
2885 END LOOP;
2886 EXIT WHEN "i" = "dimension_v";
2887 "i" := "i" + 1;
2888 END LOOP;
2889 -- Determine order of winners:
2890 "rank_ary" := "init_array"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2891 "rank_v" := 1;
2892 "done_v" := 0;
2893 LOOP
2894 "winners_ary" := '{}';
2895 "i" := 1;
2896 LOOP
2897 IF "rank_ary"["i"] ISNULL THEN
2898 "j" := 1;
2899 LOOP
2900 IF
2901 "i" != "j" AND
2902 "rank_ary"["j"] ISNULL AND
2903 "matrix"["j"]["i"] > "matrix"["i"]["j"]
2904 THEN
2905 -- someone else is better
2906 EXIT;
2907 END IF;
2908 IF "j" = "dimension_v" THEN
2909 -- noone is better
2910 "winners_ary" := "winners_ary" || "i";
2911 EXIT;
2912 END IF;
2913 "j" := "j" + 1;
2914 END LOOP;
2915 END IF;
2916 EXIT WHEN "i" = "dimension_v";
2917 "i" := "i" + 1;
2918 END LOOP;
2919 "i" := 1;
2920 LOOP
2921 "rank_ary"["winners_ary"["i"]] := "rank_v";
2922 "done_v" := "done_v" + 1;
2923 EXIT WHEN "i" = array_upper("winners_ary", 1);
2924 "i" := "i" + 1;
2925 END LOOP;
2926 EXIT WHEN "done_v" = "dimension_v";
2927 "rank_v" := "rank_v" + 1;
2928 END LOOP;
2929 -- write preliminary ranks:
2930 "i" := 1;
2931 FOR "initiative_id_v" IN
2932 SELECT "id" FROM "initiative"
2933 WHERE "issue_id" = "issue_id_p" AND "agreed"
2934 ORDER BY "id"
2935 LOOP
2936 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
2937 WHERE "id" = "initiative_id_v";
2938 "i" := "i" + 1;
2939 END LOOP;
2940 IF "i" != "dimension_v" + 1 THEN
2941 RAISE EXCEPTION 'Wrong winner count (should not happen)';
2942 END IF;
2943 -- straighten ranks (start counting with 1, no equal ranks):
2944 "rank_v" := 1;
2945 FOR "initiative_id_v" IN
2946 SELECT "id" FROM "initiative"
2947 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
2948 ORDER BY
2949 "rank",
2950 "vote_ratio"("positive_votes", "negative_votes") DESC,
2951 "id"
2952 LOOP
2953 UPDATE "initiative" SET "rank" = "rank_v"
2954 WHERE "id" = "initiative_id_v";
2955 "rank_v" := "rank_v" + 1;
2956 END LOOP;
2957 END IF;
2958 -- mark issue as finished
2959 UPDATE "issue" SET "ranks_available" = TRUE
2960 WHERE "id" = "issue_id_p";
2961 RETURN;
2962 END;
2963 $$;
2965 COMMENT ON FUNCTION "calculate_ranks"
2966 ( "issue"."id"%TYPE )
2967 IS 'Determine ranking (Votes have to be counted first)';
2971 -----------------------------
2972 -- Automatic state changes --
2973 -----------------------------
2976 CREATE FUNCTION "check_issue"
2977 ( "issue_id_p" "issue"."id"%TYPE )
2978 RETURNS VOID
2979 LANGUAGE 'plpgsql' VOLATILE AS $$
2980 DECLARE
2981 "issue_row" "issue"%ROWTYPE;
2982 "policy_row" "policy"%ROWTYPE;
2983 "voting_requested_v" BOOLEAN;
2984 BEGIN
2985 PERFORM "global_lock"();
2986 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2987 -- only process open issues:
2988 IF "issue_row"."closed" ISNULL THEN
2989 SELECT * INTO "policy_row" FROM "policy"
2990 WHERE "id" = "issue_row"."policy_id";
2991 -- create a snapshot, unless issue is already fully frozen:
2992 IF "issue_row"."fully_frozen" ISNULL THEN
2993 PERFORM "create_snapshot"("issue_id_p");
2994 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2995 END IF;
2996 -- eventually close or accept issues, which have not been accepted:
2997 IF "issue_row"."accepted" ISNULL THEN
2998 IF EXISTS (
2999 SELECT NULL FROM "initiative"
3000 WHERE "issue_id" = "issue_id_p"
3001 AND "supporter_count" > 0
3002 AND "supporter_count" * "policy_row"."issue_quorum_den"
3003 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
3004 ) THEN
3005 -- accept issues, if supporter count is high enough
3006 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3007 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later
3008 UPDATE "issue" SET "accepted" = "issue_row"."accepted"
3009 WHERE "id" = "issue_row"."id";
3010 ELSIF
3011 now() >= "issue_row"."created" + "issue_row"."admission_time"
3012 THEN
3013 -- close issues, if admission time has expired
3014 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3015 UPDATE "issue" SET "closed" = now()
3016 WHERE "id" = "issue_row"."id";
3017 END IF;
3018 END IF;
3019 -- eventually half freeze issues:
3020 IF
3021 -- NOTE: issue can't be closed at this point, if it has been accepted
3022 "issue_row"."accepted" NOTNULL AND
3023 "issue_row"."half_frozen" ISNULL
3024 THEN
3025 SELECT
3026 CASE
3027 WHEN "vote_now" * 2 > "issue_row"."population" THEN
3028 TRUE
3029 WHEN "vote_later" * 2 > "issue_row"."population" THEN
3030 FALSE
3031 ELSE NULL
3032 END
3033 INTO "voting_requested_v"
3034 FROM "issue" WHERE "id" = "issue_id_p";
3035 IF
3036 "voting_requested_v" OR (
3037 "voting_requested_v" ISNULL AND
3038 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
3039 )
3040 THEN
3041 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
3042 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later
3043 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
3044 WHERE "id" = "issue_row"."id";
3045 END IF;
3046 END IF;
3047 -- close issues after some time, if all initiatives have been revoked:
3048 IF
3049 "issue_row"."closed" ISNULL AND
3050 NOT EXISTS (
3051 -- all initiatives are revoked
3052 SELECT NULL FROM "initiative"
3053 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3054 ) AND (
3055 NOT EXISTS (
3056 -- and no initiatives have been revoked lately
3057 SELECT NULL FROM "initiative"
3058 WHERE "issue_id" = "issue_id_p"
3059 AND now() < "revoked" + "issue_row"."verification_time"
3060 ) OR (
3061 -- or verification time has elapsed
3062 "issue_row"."half_frozen" NOTNULL AND
3063 "issue_row"."fully_frozen" ISNULL AND
3064 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3065 )
3066 )
3067 THEN
3068 "issue_row"."closed" = now(); -- NOTE: "issue_row" used later
3069 UPDATE "issue" SET "closed" = "issue_row"."closed"
3070 WHERE "id" = "issue_row"."id";
3071 END IF;
3072 -- fully freeze issue after verification time:
3073 IF
3074 "issue_row"."half_frozen" NOTNULL AND
3075 "issue_row"."fully_frozen" ISNULL AND
3076 "issue_row"."closed" ISNULL AND
3077 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3078 THEN
3079 PERFORM "freeze_after_snapshot"("issue_id_p");
3080 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
3081 END IF;
3082 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3083 -- close issue by calling close_voting(...) after voting time:
3084 IF
3085 "issue_row"."closed" ISNULL AND
3086 "issue_row"."fully_frozen" NOTNULL AND
3087 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
3088 THEN
3089 PERFORM "close_voting"("issue_id_p");
3090 END IF;
3091 END IF;
3092 RETURN;
3093 END;
3094 $$;
3096 COMMENT ON FUNCTION "check_issue"
3097 ( "issue"."id"%TYPE )
3098 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.';
3101 CREATE FUNCTION "check_everything"()
3102 RETURNS VOID
3103 LANGUAGE 'plpgsql' VOLATILE AS $$
3104 DECLARE
3105 "issue_id_v" "issue"."id"%TYPE;
3106 BEGIN
3107 DELETE FROM "expired_session";
3108 PERFORM "calculate_member_counts"();
3109 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
3110 PERFORM "check_issue"("issue_id_v");
3111 END LOOP;
3112 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
3113 PERFORM "calculate_ranks"("issue_id_v");
3114 END LOOP;
3115 RETURN;
3116 END;
3117 $$;
3119 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.';
3123 ------------------------------
3124 -- Deletion of private data --
3125 ------------------------------
3128 CREATE FUNCTION "delete_private_data"()
3129 RETURNS VOID
3130 LANGUAGE 'plpgsql' VOLATILE AS $$
3131 DECLARE
3132 "issue_id_v" "issue"."id"%TYPE;
3133 BEGIN
3134 UPDATE "member" SET
3135 "login" = 'login' || "id"::text,
3136 "password" = NULL,
3137 "notify_email" = NULL,
3138 "notify_email_unconfirmed" = NULL,
3139 "notify_email_secret" = NULL,
3140 "notify_email_secret_expiry" = NULL,
3141 "password_reset_secret" = NULL,
3142 "password_reset_secret_expiry" = NULL,
3143 "organizational_unit" = NULL,
3144 "internal_posts" = NULL,
3145 "realname" = NULL,
3146 "birthday" = NULL,
3147 "address" = NULL,
3148 "email" = NULL,
3149 "xmpp_address" = NULL,
3150 "website" = NULL,
3151 "phone" = NULL,
3152 "mobile_phone" = NULL,
3153 "profession" = NULL,
3154 "external_memberships" = NULL,
3155 "external_posts" = NULL,
3156 "statement" = NULL;
3157 -- "text_search_data" is updated by triggers
3158 DELETE FROM "session";
3159 DELETE FROM "invite_code";
3160 DELETE FROM "contact";
3161 DELETE FROM "setting";
3162 DELETE FROM "member_image";
3163 DELETE FROM "direct_voter" USING "issue"
3164 WHERE "direct_voter"."issue_id" = "issue"."id"
3165 AND "issue"."closed" ISNULL;
3166 RETURN;
3167 END;
3168 $$;
3170 COMMENT ON FUNCTION "delete_private_data"() IS 'DO NOT USE on productive database, but only on a copy! This function deletes all data which should not be publicly available, and can be used to create a database dump for publication.';
3174 COMMIT;