liquid_feedback_core
view core.sql @ 55:b63515611a60
Added column "notify_email_lock_expiry"; Corrected "manual_freeze" comment
| author | jbe |
|---|---|
| date | Thu Jul 08 12:45:34 2010 +0200 (2010-07-08) |
| parents | 964cab0880ce |
| children | a7ad50614d82 |
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 ('1.1.0', 1, 1, 0))
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 "last_login" TIMESTAMPTZ,
60 "login" TEXT UNIQUE,
61 "password" TEXT,
62 "active" BOOLEAN NOT NULL DEFAULT TRUE,
63 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
64 "notify_email" TEXT,
65 "notify_email_unconfirmed" TEXT,
66 "notify_email_secret" TEXT UNIQUE,
67 "notify_email_secret_expiry" TIMESTAMPTZ,
68 "notify_email_lock_expiry" TIMESTAMPTZ,
69 "password_reset_secret" TEXT UNIQUE,
70 "password_reset_secret_expiry" TIMESTAMPTZ,
71 "name" TEXT NOT NULL UNIQUE,
72 "identification" TEXT UNIQUE,
73 "organizational_unit" TEXT,
74 "internal_posts" TEXT,
75 "realname" TEXT,
76 "birthday" DATE,
77 "address" TEXT,
78 "email" TEXT,
79 "xmpp_address" TEXT,
80 "website" TEXT,
81 "phone" TEXT,
82 "mobile_phone" TEXT,
83 "profession" TEXT,
84 "external_memberships" TEXT,
85 "external_posts" TEXT,
86 "statement" TEXT,
87 "text_search_data" TSVECTOR );
88 CREATE INDEX "member_active_idx" ON "member" ("active");
89 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
90 CREATE TRIGGER "update_text_search_data"
91 BEFORE INSERT OR UPDATE ON "member"
92 FOR EACH ROW EXECUTE PROCEDURE
93 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
94 "name", "identification", "organizational_unit", "internal_posts",
95 "realname", "external_memberships", "external_posts", "statement" );
97 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
99 COMMENT ON COLUMN "member"."login" IS 'Login name';
100 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
101 COMMENT ON COLUMN "member"."active" IS 'Inactive members can not login and their supports/votes are not counted by the system.';
102 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
103 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
104 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
105 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
106 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
107 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
108 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member';
109 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
110 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
111 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
112 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
113 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
114 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
115 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
116 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his homepage within the system';
119 CREATE TABLE "member_history" (
120 "id" SERIAL8 PRIMARY KEY,
121 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
122 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
123 "login" TEXT,
124 "active" BOOLEAN NOT NULL,
125 "name" TEXT NOT NULL );
126 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
128 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names, login names and active flag of members';
130 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
131 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the name and login had been valid';
134 CREATE TABLE "invite_code" (
135 "code" TEXT PRIMARY KEY,
136 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
137 "used" TIMESTAMPTZ,
138 "member_id" INT4 UNIQUE REFERENCES "member" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
139 "comment" TEXT,
140 CONSTRAINT "only_used_codes_may_refer_to_member" CHECK ("used" NOTNULL OR "member_id" ISNULL) );
142 COMMENT ON TABLE "invite_code" IS 'Invite codes can be used once to create a new member account.';
144 COMMENT ON COLUMN "invite_code"."code" IS 'Secret code';
145 COMMENT ON COLUMN "invite_code"."created" IS 'Time of creation of the secret code';
146 COMMENT ON COLUMN "invite_code"."used" IS 'NULL, if not used yet, otherwise tells when this code was used to create a member account';
147 COMMENT ON COLUMN "invite_code"."member_id" IS 'References the member whose account was created with this code';
148 COMMENT ON COLUMN "invite_code"."comment" IS 'Comment on the code, which is to be used for administrative reasons only';
151 CREATE TABLE "setting" (
152 PRIMARY KEY ("member_id", "key"),
153 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
154 "key" TEXT NOT NULL,
155 "value" TEXT NOT NULL );
156 CREATE INDEX "setting_key_idx" ON "setting" ("key");
158 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
160 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
163 CREATE TABLE "setting_map" (
164 PRIMARY KEY ("member_id", "key", "subkey"),
165 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
166 "key" TEXT NOT NULL,
167 "subkey" TEXT NOT NULL,
168 "value" TEXT NOT NULL );
169 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
171 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
173 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
174 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
175 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
178 CREATE TABLE "member_relation_setting" (
179 PRIMARY KEY ("member_id", "key", "other_member_id"),
180 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
181 "key" TEXT NOT NULL,
182 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
183 "value" TEXT NOT NULL );
185 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
188 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
190 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
193 CREATE TABLE "member_image" (
194 PRIMARY KEY ("member_id", "image_type", "scaled"),
195 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
196 "image_type" "member_image_type",
197 "scaled" BOOLEAN,
198 "content_type" TEXT,
199 "data" BYTEA NOT NULL );
201 COMMENT ON TABLE "member_image" IS 'Images of members';
203 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
206 CREATE TABLE "member_count" (
207 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
208 "total_count" INT4 NOT NULL );
210 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';
212 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
213 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
216 CREATE TABLE "contact" (
217 PRIMARY KEY ("member_id", "other_member_id"),
218 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
219 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
220 "public" BOOLEAN NOT NULL DEFAULT FALSE,
221 CONSTRAINT "cant_save_yourself_as_contact"
222 CHECK ("member_id" != "other_member_id") );
224 COMMENT ON TABLE "contact" IS 'Contact lists';
226 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
227 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
228 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
231 CREATE TABLE "session" (
232 "ident" TEXT PRIMARY KEY,
233 "additional_secret" TEXT,
234 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
235 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
236 "lang" TEXT );
237 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
239 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';
241 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
242 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
243 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
244 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
247 CREATE TABLE "policy" (
248 "id" SERIAL4 PRIMARY KEY,
249 "index" INT4 NOT NULL,
250 "active" BOOLEAN NOT NULL DEFAULT TRUE,
251 "name" TEXT NOT NULL UNIQUE,
252 "description" TEXT NOT NULL DEFAULT '',
253 "admission_time" INTERVAL NOT NULL,
254 "discussion_time" INTERVAL NOT NULL,
255 "verification_time" INTERVAL NOT NULL,
256 "voting_time" INTERVAL NOT NULL,
257 "issue_quorum_num" INT4 NOT NULL,
258 "issue_quorum_den" INT4 NOT NULL,
259 "initiative_quorum_num" INT4 NOT NULL,
260 "initiative_quorum_den" INT4 NOT NULL,
261 "majority_num" INT4 NOT NULL DEFAULT 1,
262 "majority_den" INT4 NOT NULL DEFAULT 2,
263 "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE );
264 CREATE INDEX "policy_active_idx" ON "policy" ("active");
266 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
268 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
269 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
270 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"';
271 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"';
272 COMMENT ON COLUMN "policy"."verification_time" IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
273 COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "fully_frozen" but not "closed"';
274 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"';
275 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"';
276 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
277 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
278 COMMENT ON COLUMN "policy"."majority_num" IS 'Numerator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
279 COMMENT ON COLUMN "policy"."majority_den" IS 'Denominator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
280 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.';
283 CREATE TABLE "area" (
284 "id" SERIAL4 PRIMARY KEY,
285 "active" BOOLEAN NOT NULL DEFAULT TRUE,
286 "name" TEXT NOT NULL,
287 "description" TEXT NOT NULL DEFAULT '',
288 "direct_member_count" INT4,
289 "member_weight" INT4,
290 "autoreject_weight" INT4,
291 "text_search_data" TSVECTOR );
292 CREATE INDEX "area_active_idx" ON "area" ("active");
293 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
294 CREATE TRIGGER "update_text_search_data"
295 BEFORE INSERT OR UPDATE ON "area"
296 FOR EACH ROW EXECUTE PROCEDURE
297 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
298 "name", "description" );
300 COMMENT ON TABLE "area" IS 'Subject areas';
302 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
303 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"';
304 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
305 COMMENT ON COLUMN "area"."autoreject_weight" IS 'Sum of weight of members using the autoreject feature';
308 CREATE TABLE "area_setting" (
309 PRIMARY KEY ("member_id", "key", "area_id"),
310 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
311 "key" TEXT NOT NULL,
312 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
313 "value" TEXT NOT NULL );
315 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
318 CREATE TABLE "allowed_policy" (
319 PRIMARY KEY ("area_id", "policy_id"),
320 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
321 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
322 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
323 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
325 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
327 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
330 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
332 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';
335 CREATE TABLE "issue" (
336 "id" SERIAL4 PRIMARY KEY,
337 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
338 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
339 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
340 "accepted" TIMESTAMPTZ,
341 "half_frozen" TIMESTAMPTZ,
342 "fully_frozen" TIMESTAMPTZ,
343 "closed" TIMESTAMPTZ,
344 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
345 "admission_time" INTERVAL NOT NULL,
346 "discussion_time" INTERVAL NOT NULL,
347 "verification_time" INTERVAL NOT NULL,
348 "voting_time" INTERVAL NOT NULL,
349 "snapshot" TIMESTAMPTZ,
350 "latest_snapshot_event" "snapshot_event",
351 "population" INT4,
352 "vote_now" INT4,
353 "vote_later" INT4,
354 "voter_count" INT4,
355 CONSTRAINT "valid_state" CHECK (
356 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
357 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
358 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
359 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
360 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
361 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
362 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
363 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
364 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ),
365 CONSTRAINT "state_change_order" CHECK (
366 "created" <= "accepted" AND
367 "accepted" <= "half_frozen" AND
368 "half_frozen" <= "fully_frozen" AND
369 "fully_frozen" <= "closed" ),
370 CONSTRAINT "last_snapshot_on_full_freeze"
371 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
372 CONSTRAINT "freeze_requires_snapshot"
373 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
374 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
375 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
376 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
377 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
378 CREATE INDEX "issue_created_idx" ON "issue" ("created");
379 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
380 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
381 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
382 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
383 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
384 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
386 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
388 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
389 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.';
390 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.';
391 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.';
392 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
393 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
394 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
395 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
396 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
397 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';
398 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';
399 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
400 COMMENT ON COLUMN "issue"."vote_now" IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"';
401 COMMENT ON COLUMN "issue"."vote_later" IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"';
402 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';
405 CREATE TABLE "issue_setting" (
406 PRIMARY KEY ("member_id", "key", "issue_id"),
407 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
408 "key" TEXT NOT NULL,
409 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
410 "value" TEXT NOT NULL );
412 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
415 CREATE TABLE "initiative" (
416 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
417 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
418 "id" SERIAL4 PRIMARY KEY,
419 "name" TEXT NOT NULL,
420 "discussion_url" TEXT,
421 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
422 "revoked" TIMESTAMPTZ,
423 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
424 "admitted" BOOLEAN,
425 "supporter_count" INT4,
426 "informed_supporter_count" INT4,
427 "satisfied_supporter_count" INT4,
428 "satisfied_informed_supporter_count" INT4,
429 "positive_votes" INT4,
430 "negative_votes" INT4,
431 "agreed" BOOLEAN,
432 "rank" INT4,
433 "text_search_data" TSVECTOR,
434 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
435 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
436 CONSTRAINT "revoked_initiatives_cant_be_admitted"
437 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
438 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"
439 CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "agreed" ISNULL)),
440 CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"
441 CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL),
442 CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"
443 CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) );
444 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
445 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
446 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
447 CREATE TRIGGER "update_text_search_data"
448 BEFORE INSERT OR UPDATE ON "initiative"
449 FOR EACH ROW EXECUTE PROCEDURE
450 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
451 "name", "discussion_url");
453 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.';
455 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
456 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
457 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
458 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
459 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
460 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
461 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
462 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
463 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
464 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"';
465 COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';
468 CREATE TABLE "initiative_setting" (
469 PRIMARY KEY ("member_id", "key", "initiative_id"),
470 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
471 "key" TEXT NOT NULL,
472 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
473 "value" TEXT NOT NULL );
475 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
478 CREATE TABLE "draft" (
479 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
480 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
481 "id" SERIAL8 PRIMARY KEY,
482 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
483 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
484 "formatting_engine" TEXT,
485 "content" TEXT NOT NULL,
486 "text_search_data" TSVECTOR );
487 CREATE INDEX "draft_created_idx" ON "draft" ("created");
488 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
489 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
490 CREATE TRIGGER "update_text_search_data"
491 BEFORE INSERT OR UPDATE ON "draft"
492 FOR EACH ROW EXECUTE PROCEDURE
493 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
495 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.';
497 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
498 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
501 CREATE TABLE "suggestion" (
502 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
503 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
504 "id" SERIAL8 PRIMARY KEY,
505 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
506 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
507 "name" TEXT NOT NULL,
508 "description" TEXT NOT NULL DEFAULT '',
509 "text_search_data" TSVECTOR,
510 "minus2_unfulfilled_count" INT4,
511 "minus2_fulfilled_count" INT4,
512 "minus1_unfulfilled_count" INT4,
513 "minus1_fulfilled_count" INT4,
514 "plus1_unfulfilled_count" INT4,
515 "plus1_fulfilled_count" INT4,
516 "plus2_unfulfilled_count" INT4,
517 "plus2_fulfilled_count" INT4 );
518 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
519 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
520 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
521 CREATE TRIGGER "update_text_search_data"
522 BEFORE INSERT OR UPDATE ON "suggestion"
523 FOR EACH ROW EXECUTE PROCEDURE
524 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
525 "name", "description");
527 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';
529 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
530 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
531 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
532 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
533 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
534 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
535 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
536 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
539 CREATE TABLE "suggestion_setting" (
540 PRIMARY KEY ("member_id", "key", "suggestion_id"),
541 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
542 "key" TEXT NOT NULL,
543 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
544 "value" TEXT NOT NULL );
546 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
549 CREATE TABLE "membership" (
550 PRIMARY KEY ("area_id", "member_id"),
551 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
552 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
553 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
554 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
556 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
558 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';
561 CREATE TABLE "interest" (
562 PRIMARY KEY ("issue_id", "member_id"),
563 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
564 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
565 "autoreject" BOOLEAN NOT NULL,
566 "voting_requested" BOOLEAN );
567 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
569 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.';
571 COMMENT ON COLUMN "interest"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure';
572 COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply';
575 CREATE TABLE "initiator" (
576 PRIMARY KEY ("initiative_id", "member_id"),
577 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
578 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
579 "accepted" BOOLEAN );
580 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
582 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.';
584 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.';
587 CREATE TABLE "supporter" (
588 "issue_id" INT4 NOT NULL,
589 PRIMARY KEY ("initiative_id", "member_id"),
590 "initiative_id" INT4,
591 "member_id" INT4,
592 "draft_id" INT8 NOT NULL,
593 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
594 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
595 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
597 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.';
599 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
602 CREATE TABLE "opinion" (
603 "initiative_id" INT4 NOT NULL,
604 PRIMARY KEY ("suggestion_id", "member_id"),
605 "suggestion_id" INT8,
606 "member_id" INT4,
607 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
608 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
609 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
610 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
611 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
613 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.';
615 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
618 CREATE TYPE "delegation_scope" AS ENUM ('global', 'area', 'issue');
620 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''global'', ''area'', or ''issue'' (order is relevant)';
623 CREATE TABLE "delegation" (
624 "id" SERIAL8 PRIMARY KEY,
625 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
626 "trustee_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
627 "scope" "delegation_scope" NOT NULL,
628 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
629 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
630 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
631 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
632 ("scope" = 'global' AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
633 ("scope" = 'area' AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
634 ("scope" = 'issue' AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
635 UNIQUE ("area_id", "truster_id", "trustee_id"),
636 UNIQUE ("issue_id", "truster_id", "trustee_id") );
637 CREATE UNIQUE INDEX "delegation_global_truster_id_trustee_id_unique_idx"
638 ON "delegation" ("truster_id", "trustee_id") WHERE "scope" = 'global';
639 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
640 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
642 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
644 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
645 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
648 CREATE TABLE "direct_population_snapshot" (
649 PRIMARY KEY ("issue_id", "event", "member_id"),
650 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
651 "event" "snapshot_event",
652 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
653 "weight" INT4 );
654 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
656 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
658 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
659 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
662 CREATE TABLE "delegating_population_snapshot" (
663 PRIMARY KEY ("issue_id", "event", "member_id"),
664 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
665 "event" "snapshot_event",
666 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
667 "weight" INT4,
668 "scope" "delegation_scope" NOT NULL,
669 "delegate_member_ids" INT4[] NOT NULL );
670 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
672 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
674 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
675 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
676 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
677 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"';
680 CREATE TABLE "direct_interest_snapshot" (
681 PRIMARY KEY ("issue_id", "event", "member_id"),
682 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
683 "event" "snapshot_event",
684 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
685 "weight" INT4,
686 "voting_requested" BOOLEAN );
687 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
689 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
691 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
692 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
693 COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';
696 CREATE TABLE "delegating_interest_snapshot" (
697 PRIMARY KEY ("issue_id", "event", "member_id"),
698 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
699 "event" "snapshot_event",
700 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
701 "weight" INT4,
702 "scope" "delegation_scope" NOT NULL,
703 "delegate_member_ids" INT4[] NOT NULL );
704 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
706 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
708 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
709 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
710 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
711 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"';
714 CREATE TABLE "direct_supporter_snapshot" (
715 "issue_id" INT4 NOT NULL,
716 PRIMARY KEY ("initiative_id", "event", "member_id"),
717 "initiative_id" INT4,
718 "event" "snapshot_event",
719 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
720 "informed" BOOLEAN NOT NULL,
721 "satisfied" BOOLEAN NOT NULL,
722 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
723 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
724 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
726 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
728 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
729 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
730 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
733 CREATE TABLE "direct_voter" (
734 PRIMARY KEY ("issue_id", "member_id"),
735 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
736 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
737 "weight" INT4,
738 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
739 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
741 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.';
743 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
744 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
747 CREATE TABLE "delegating_voter" (
748 PRIMARY KEY ("issue_id", "member_id"),
749 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
750 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
751 "weight" INT4,
752 "scope" "delegation_scope" NOT NULL,
753 "delegate_member_ids" INT4[] NOT NULL );
754 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
756 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
758 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
759 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
760 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"';
763 CREATE TABLE "vote" (
764 "issue_id" INT4 NOT NULL,
765 PRIMARY KEY ("initiative_id", "member_id"),
766 "initiative_id" INT4,
767 "member_id" INT4,
768 "grade" INT4,
769 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
770 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
771 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
773 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.';
775 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.';
778 CREATE TABLE "contingent" (
779 "time_frame" INTERVAL PRIMARY KEY,
780 "text_entry_limit" INT4,
781 "initiative_limit" INT4 );
783 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.';
785 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';
786 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
790 --------------------------------
791 -- Writing of history entries --
792 --------------------------------
794 CREATE FUNCTION "write_member_history_trigger"()
795 RETURNS TRIGGER
796 LANGUAGE 'plpgsql' VOLATILE AS $$
797 BEGIN
798 IF
799 ( NEW."login" NOTNULL AND OLD."login" NOTNULL AND
800 NEW."login" != OLD."login" ) OR
801 ( NEW."login" NOTNULL AND OLD."login" ISNULL ) OR
802 ( NEW."login" ISNULL AND OLD."login" NOTNULL ) OR
803 NEW."active" != OLD."active" OR
804 NEW."name" != OLD."name"
805 THEN
806 INSERT INTO "member_history"
807 ("member_id", "login", "active", "name")
808 VALUES (NEW."id", OLD."login", OLD."active", OLD."name");
809 END IF;
810 RETURN NULL;
811 END;
812 $$;
814 CREATE TRIGGER "write_member_history"
815 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
816 "write_member_history_trigger"();
818 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
819 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';
823 ----------------------------
824 -- Additional constraints --
825 ----------------------------
828 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
829 RETURNS TRIGGER
830 LANGUAGE 'plpgsql' VOLATILE AS $$
831 BEGIN
832 IF NOT EXISTS (
833 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
834 ) THEN
835 --RAISE 'Cannot create issue without an initial initiative.' USING
836 -- ERRCODE = 'integrity_constraint_violation',
837 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
838 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
839 END IF;
840 RETURN NULL;
841 END;
842 $$;
844 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
845 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
846 FOR EACH ROW EXECUTE PROCEDURE
847 "issue_requires_first_initiative_trigger"();
849 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
850 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
853 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
854 RETURNS TRIGGER
855 LANGUAGE 'plpgsql' VOLATILE AS $$
856 DECLARE
857 "reference_lost" BOOLEAN;
858 BEGIN
859 IF TG_OP = 'DELETE' THEN
860 "reference_lost" := TRUE;
861 ELSE
862 "reference_lost" := NEW."issue_id" != OLD."issue_id";
863 END IF;
864 IF
865 "reference_lost" AND NOT EXISTS (
866 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
867 )
868 THEN
869 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
870 END IF;
871 RETURN NULL;
872 END;
873 $$;
875 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
876 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
877 FOR EACH ROW EXECUTE PROCEDURE
878 "last_initiative_deletes_issue_trigger"();
880 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
881 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
884 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
885 RETURNS TRIGGER
886 LANGUAGE 'plpgsql' VOLATILE AS $$
887 BEGIN
888 IF NOT EXISTS (
889 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
890 ) THEN
891 --RAISE 'Cannot create initiative without an initial draft.' USING
892 -- ERRCODE = 'integrity_constraint_violation',
893 -- HINT = 'Create issue, initiative and draft within the same transaction.';
894 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
895 END IF;
896 RETURN NULL;
897 END;
898 $$;
900 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
901 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
902 FOR EACH ROW EXECUTE PROCEDURE
903 "initiative_requires_first_draft_trigger"();
905 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
906 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
909 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
910 RETURNS TRIGGER
911 LANGUAGE 'plpgsql' VOLATILE AS $$
912 DECLARE
913 "reference_lost" BOOLEAN;
914 BEGIN
915 IF TG_OP = 'DELETE' THEN
916 "reference_lost" := TRUE;
917 ELSE
918 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
919 END IF;
920 IF
921 "reference_lost" AND NOT EXISTS (
922 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
923 )
924 THEN
925 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
926 END IF;
927 RETURN NULL;
928 END;
929 $$;
931 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
932 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
933 FOR EACH ROW EXECUTE PROCEDURE
934 "last_draft_deletes_initiative_trigger"();
936 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
937 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
940 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
941 RETURNS TRIGGER
942 LANGUAGE 'plpgsql' VOLATILE AS $$
943 BEGIN
944 IF NOT EXISTS (
945 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
946 ) THEN
947 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
948 END IF;
949 RETURN NULL;
950 END;
951 $$;
953 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
954 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
955 FOR EACH ROW EXECUTE PROCEDURE
956 "suggestion_requires_first_opinion_trigger"();
958 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
959 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
962 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
963 RETURNS TRIGGER
964 LANGUAGE 'plpgsql' VOLATILE AS $$
965 DECLARE
966 "reference_lost" BOOLEAN;
967 BEGIN
968 IF TG_OP = 'DELETE' THEN
969 "reference_lost" := TRUE;
970 ELSE
971 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
972 END IF;
973 IF
974 "reference_lost" AND NOT EXISTS (
975 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
976 )
977 THEN
978 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
979 END IF;
980 RETURN NULL;
981 END;
982 $$;
984 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
985 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
986 FOR EACH ROW EXECUTE PROCEDURE
987 "last_opinion_deletes_suggestion_trigger"();
989 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
990 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
994 ---------------------------------------------------------------
995 -- Ensure that votes are not modified when issues are frozen --
996 ---------------------------------------------------------------
998 -- NOTE: Frontends should ensure this anyway, but in case of programming
999 -- errors the following triggers ensure data integrity.
1002 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1003 RETURNS TRIGGER
1004 LANGUAGE 'plpgsql' VOLATILE AS $$
1005 DECLARE
1006 "issue_id_v" "issue"."id"%TYPE;
1007 "issue_row" "issue"%ROWTYPE;
1008 BEGIN
1009 IF TG_OP = 'DELETE' THEN
1010 "issue_id_v" := OLD."issue_id";
1011 ELSE
1012 "issue_id_v" := NEW."issue_id";
1013 END IF;
1014 SELECT INTO "issue_row" * FROM "issue"
1015 WHERE "id" = "issue_id_v" FOR SHARE;
1016 IF "issue_row"."closed" NOTNULL THEN
1017 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
1018 END IF;
1019 RETURN NULL;
1020 END;
1021 $$;
1023 CREATE TRIGGER "forbid_changes_on_closed_issue"
1024 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1025 FOR EACH ROW EXECUTE PROCEDURE
1026 "forbid_changes_on_closed_issue_trigger"();
1028 CREATE TRIGGER "forbid_changes_on_closed_issue"
1029 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1030 FOR EACH ROW EXECUTE PROCEDURE
1031 "forbid_changes_on_closed_issue_trigger"();
1033 CREATE TRIGGER "forbid_changes_on_closed_issue"
1034 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1035 FOR EACH ROW EXECUTE PROCEDURE
1036 "forbid_changes_on_closed_issue_trigger"();
1038 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"';
1039 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';
1040 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';
1041 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';
1045 --------------------------------------------------------------------
1046 -- Auto-retrieval of fields only needed for referential integrity --
1047 --------------------------------------------------------------------
1050 CREATE FUNCTION "autofill_issue_id_trigger"()
1051 RETURNS TRIGGER
1052 LANGUAGE 'plpgsql' VOLATILE AS $$
1053 BEGIN
1054 IF NEW."issue_id" ISNULL THEN
1055 SELECT "issue_id" INTO NEW."issue_id"
1056 FROM "initiative" WHERE "id" = NEW."initiative_id";
1057 END IF;
1058 RETURN NEW;
1059 END;
1060 $$;
1062 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1063 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1065 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1066 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1068 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1069 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1070 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1073 CREATE FUNCTION "autofill_initiative_id_trigger"()
1074 RETURNS TRIGGER
1075 LANGUAGE 'plpgsql' VOLATILE AS $$
1076 BEGIN
1077 IF NEW."initiative_id" ISNULL THEN
1078 SELECT "initiative_id" INTO NEW."initiative_id"
1079 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1080 END IF;
1081 RETURN NEW;
1082 END;
1083 $$;
1085 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1086 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1088 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1089 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1093 -----------------------------------------------------
1094 -- Automatic calculation of certain default values --
1095 -----------------------------------------------------
1098 CREATE FUNCTION "copy_timings_trigger"()
1099 RETURNS TRIGGER
1100 LANGUAGE 'plpgsql' VOLATILE AS $$
1101 DECLARE
1102 "policy_row" "policy"%ROWTYPE;
1103 BEGIN
1104 SELECT * INTO "policy_row" FROM "policy"
1105 WHERE "id" = NEW."policy_id";
1106 IF NEW."admission_time" ISNULL THEN
1107 NEW."admission_time" := "policy_row"."admission_time";
1108 END IF;
1109 IF NEW."discussion_time" ISNULL THEN
1110 NEW."discussion_time" := "policy_row"."discussion_time";
1111 END IF;
1112 IF NEW."verification_time" ISNULL THEN
1113 NEW."verification_time" := "policy_row"."verification_time";
1114 END IF;
1115 IF NEW."voting_time" ISNULL THEN
1116 NEW."voting_time" := "policy_row"."voting_time";
1117 END IF;
1118 RETURN NEW;
1119 END;
1120 $$;
1122 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1123 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1125 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1126 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1129 CREATE FUNCTION "copy_autoreject_trigger"()
1130 RETURNS TRIGGER
1131 LANGUAGE 'plpgsql' VOLATILE AS $$
1132 BEGIN
1133 IF NEW."autoreject" ISNULL THEN
1134 SELECT "membership"."autoreject" INTO NEW."autoreject"
1135 FROM "issue" JOIN "membership"
1136 ON "issue"."area_id" = "membership"."area_id"
1137 WHERE "issue"."id" = NEW."issue_id"
1138 AND "membership"."member_id" = NEW."member_id";
1139 END IF;
1140 IF NEW."autoreject" ISNULL THEN
1141 NEW."autoreject" := FALSE;
1142 END IF;
1143 RETURN NEW;
1144 END;
1145 $$;
1147 CREATE TRIGGER "copy_autoreject" BEFORE INSERT OR UPDATE ON "interest"
1148 FOR EACH ROW EXECUTE PROCEDURE "copy_autoreject_trigger"();
1150 COMMENT ON FUNCTION "copy_autoreject_trigger"() IS 'Implementation of trigger "copy_autoreject" on table "interest"';
1151 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';
1154 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
1155 RETURNS TRIGGER
1156 LANGUAGE 'plpgsql' VOLATILE AS $$
1157 BEGIN
1158 IF NEW."draft_id" ISNULL THEN
1159 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1160 WHERE "initiative_id" = NEW."initiative_id";
1161 END IF;
1162 RETURN NEW;
1163 END;
1164 $$;
1166 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1167 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
1169 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
1170 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';
1174 ----------------------------------------
1175 -- Automatic creation of dependencies --
1176 ----------------------------------------
1179 CREATE FUNCTION "autocreate_interest_trigger"()
1180 RETURNS TRIGGER
1181 LANGUAGE 'plpgsql' VOLATILE AS $$
1182 BEGIN
1183 IF NOT EXISTS (
1184 SELECT NULL FROM "initiative" JOIN "interest"
1185 ON "initiative"."issue_id" = "interest"."issue_id"
1186 WHERE "initiative"."id" = NEW."initiative_id"
1187 AND "interest"."member_id" = NEW."member_id"
1188 ) THEN
1189 BEGIN
1190 INSERT INTO "interest" ("issue_id", "member_id")
1191 SELECT "issue_id", NEW."member_id"
1192 FROM "initiative" WHERE "id" = NEW."initiative_id";
1193 EXCEPTION WHEN unique_violation THEN END;
1194 END IF;
1195 RETURN NEW;
1196 END;
1197 $$;
1199 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1200 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1202 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1203 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';
1206 CREATE FUNCTION "autocreate_supporter_trigger"()
1207 RETURNS TRIGGER
1208 LANGUAGE 'plpgsql' VOLATILE AS $$
1209 BEGIN
1210 IF NOT EXISTS (
1211 SELECT NULL FROM "suggestion" JOIN "supporter"
1212 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1213 WHERE "suggestion"."id" = NEW."suggestion_id"
1214 AND "supporter"."member_id" = NEW."member_id"
1215 ) THEN
1216 BEGIN
1217 INSERT INTO "supporter" ("initiative_id", "member_id")
1218 SELECT "initiative_id", NEW."member_id"
1219 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1220 EXCEPTION WHEN unique_violation THEN END;
1221 END IF;
1222 RETURN NEW;
1223 END;
1224 $$;
1226 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1227 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1229 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1230 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.';
1234 ------------------------------------------
1235 -- Views and helper functions for views --
1236 ------------------------------------------
1239 CREATE VIEW "global_delegation" AS
1240 SELECT
1241 "delegation"."id",
1242 "delegation"."truster_id",
1243 "delegation"."trustee_id"
1244 FROM "delegation" JOIN "member"
1245 ON "delegation"."trustee_id" = "member"."id"
1246 WHERE "delegation"."scope" = 'global' AND "member"."active";
1248 COMMENT ON VIEW "global_delegation" IS 'Global delegations to active members';
1251 CREATE VIEW "area_delegation" AS
1252 SELECT "subquery".* FROM (
1253 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1254 "area"."id" AS "area_id",
1255 "delegation"."id",
1256 "delegation"."truster_id",
1257 "delegation"."trustee_id",
1258 "delegation"."scope"
1259 FROM "area" JOIN "delegation"
1260 ON "delegation"."scope" = 'global'
1261 OR "delegation"."area_id" = "area"."id"
1262 ORDER BY
1263 "area"."id",
1264 "delegation"."truster_id",
1265 "delegation"."scope" DESC
1266 ) AS "subquery"
1267 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
1268 WHERE "member"."active";
1270 COMMENT ON VIEW "area_delegation" IS 'Active delegations for areas';
1273 CREATE VIEW "issue_delegation" AS
1274 SELECT "subquery".* FROM (
1275 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1276 "issue"."id" AS "issue_id",
1277 "delegation"."id",
1278 "delegation"."truster_id",
1279 "delegation"."trustee_id",
1280 "delegation"."scope"
1281 FROM "issue" JOIN "delegation"
1282 ON "delegation"."scope" = 'global'
1283 OR "delegation"."area_id" = "issue"."area_id"
1284 OR "delegation"."issue_id" = "issue"."id"
1285 ORDER BY
1286 "issue"."id",
1287 "delegation"."truster_id",
1288 "delegation"."scope" DESC
1289 ) AS "subquery"
1290 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
1291 WHERE "member"."active";
1293 COMMENT ON VIEW "issue_delegation" IS 'Active delegations for issues';
1296 CREATE FUNCTION "membership_weight_with_skipping"
1297 ( "area_id_p" "area"."id"%TYPE,
1298 "member_id_p" "member"."id"%TYPE,
1299 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1300 RETURNS INT4
1301 LANGUAGE 'plpgsql' STABLE AS $$
1302 DECLARE
1303 "sum_v" INT4;
1304 "delegation_row" "area_delegation"%ROWTYPE;
1305 BEGIN
1306 "sum_v" := 1;
1307 FOR "delegation_row" IN
1308 SELECT "area_delegation".*
1309 FROM "area_delegation" LEFT JOIN "membership"
1310 ON "membership"."area_id" = "area_id_p"
1311 AND "membership"."member_id" = "area_delegation"."truster_id"
1312 WHERE "area_delegation"."area_id" = "area_id_p"
1313 AND "area_delegation"."trustee_id" = "member_id_p"
1314 AND "membership"."member_id" ISNULL
1315 LOOP
1316 IF NOT
1317 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1318 THEN
1319 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1320 "area_id_p",
1321 "delegation_row"."truster_id",
1322 "skip_member_ids_p" || "delegation_row"."truster_id"
1323 );
1324 END IF;
1325 END LOOP;
1326 RETURN "sum_v";
1327 END;
1328 $$;
1330 COMMENT ON FUNCTION "membership_weight_with_skipping"
1331 ( "area"."id"%TYPE,
1332 "member"."id"%TYPE,
1333 INT4[] )
1334 IS 'Helper function for "membership_weight" function';
1337 CREATE FUNCTION "membership_weight"
1338 ( "area_id_p" "area"."id"%TYPE,
1339 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1340 RETURNS INT4
1341 LANGUAGE 'plpgsql' STABLE AS $$
1342 BEGIN
1343 RETURN "membership_weight_with_skipping"(
1344 "area_id_p",
1345 "member_id_p",
1346 ARRAY["member_id_p"]
1347 );
1348 END;
1349 $$;
1351 COMMENT ON FUNCTION "membership_weight"
1352 ( "area"."id"%TYPE,
1353 "member"."id"%TYPE )
1354 IS 'Calculates the potential voting weight of a member in a given area';
1357 CREATE VIEW "member_count_view" AS
1358 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1360 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1363 CREATE VIEW "area_member_count" AS
1364 SELECT
1365 "area"."id" AS "area_id",
1366 count("member"."id") AS "direct_member_count",
1367 coalesce(
1368 sum(
1369 CASE WHEN "member"."id" NOTNULL THEN
1370 "membership_weight"("area"."id", "member"."id")
1371 ELSE 0 END
1372 )
1373 ) AS "member_weight",
1374 coalesce(
1375 sum(
1376 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
1377 "membership_weight"("area"."id", "member"."id")
1378 ELSE 0 END
1379 )
1380 ) AS "autoreject_weight"
1381 FROM "area"
1382 LEFT JOIN "membership"
1383 ON "area"."id" = "membership"."area_id"
1384 LEFT JOIN "member"
1385 ON "membership"."member_id" = "member"."id"
1386 AND "member"."active"
1387 GROUP BY "area"."id";
1389 COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"';
1392 CREATE VIEW "opening_draft" AS
1393 SELECT "draft".* FROM (
1394 SELECT
1395 "initiative"."id" AS "initiative_id",
1396 min("draft"."id") AS "draft_id"
1397 FROM "initiative" JOIN "draft"
1398 ON "initiative"."id" = "draft"."initiative_id"
1399 GROUP BY "initiative"."id"
1400 ) AS "subquery"
1401 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1403 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1406 CREATE VIEW "current_draft" AS
1407 SELECT "draft".* FROM (
1408 SELECT
1409 "initiative"."id" AS "initiative_id",
1410 max("draft"."id") AS "draft_id"
1411 FROM "initiative" JOIN "draft"
1412 ON "initiative"."id" = "draft"."initiative_id"
1413 GROUP BY "initiative"."id"
1414 ) AS "subquery"
1415 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1417 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1420 CREATE VIEW "critical_opinion" AS
1421 SELECT * FROM "opinion"
1422 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
1423 OR ("degree" = -2 AND "fulfilled" = TRUE);
1425 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
1428 CREATE VIEW "battle" AS
1429 SELECT
1430 "issue"."id" AS "issue_id",
1431 "winning_initiative"."id" AS "winning_initiative_id",
1432 "losing_initiative"."id" AS "losing_initiative_id",
1433 sum(
1434 CASE WHEN
1435 coalesce("better_vote"."grade", 0) >
1436 coalesce("worse_vote"."grade", 0)
1437 THEN "direct_voter"."weight" ELSE 0 END
1438 ) AS "count"
1439 FROM "issue"
1440 LEFT JOIN "direct_voter"
1441 ON "issue"."id" = "direct_voter"."issue_id"
1442 JOIN "initiative" AS "winning_initiative"
1443 ON "issue"."id" = "winning_initiative"."issue_id"
1444 AND "winning_initiative"."agreed"
1445 JOIN "initiative" AS "losing_initiative"
1446 ON "issue"."id" = "losing_initiative"."issue_id"
1447 AND "losing_initiative"."agreed"
1448 LEFT JOIN "vote" AS "better_vote"
1449 ON "direct_voter"."member_id" = "better_vote"."member_id"
1450 AND "winning_initiative"."id" = "better_vote"."initiative_id"
1451 LEFT JOIN "vote" AS "worse_vote"
1452 ON "direct_voter"."member_id" = "worse_vote"."member_id"
1453 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
1454 WHERE
1455 "winning_initiative"."id" != "losing_initiative"."id"
1456 GROUP BY
1457 "issue"."id",
1458 "winning_initiative"."id",
1459 "losing_initiative"."id";
1461 COMMENT ON VIEW "battle" IS 'Number of members preferring one initiative over another';
1464 CREATE VIEW "expired_session" AS
1465 SELECT * FROM "session" WHERE now() > "expiry";
1467 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
1468 DELETE FROM "session" WHERE "ident" = OLD."ident";
1470 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
1471 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
1474 CREATE VIEW "open_issue" AS
1475 SELECT * FROM "issue" WHERE "closed" ISNULL;
1477 COMMENT ON VIEW "open_issue" IS 'All open issues';
1480 CREATE VIEW "issue_with_ranks_missing" AS
1481 SELECT * FROM "issue"
1482 WHERE "fully_frozen" NOTNULL
1483 AND "closed" NOTNULL
1484 AND "ranks_available" = FALSE;
1486 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
1489 CREATE VIEW "member_contingent" AS
1490 SELECT
1491 "member"."id" AS "member_id",
1492 "contingent"."time_frame",
1493 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
1494 (
1495 SELECT count(1) FROM "draft"
1496 WHERE "draft"."author_id" = "member"."id"
1497 AND "draft"."created" > now() - "contingent"."time_frame"
1498 ) + (
1499 SELECT count(1) FROM "suggestion"
1500 WHERE "suggestion"."author_id" = "member"."id"
1501 AND "suggestion"."created" > now() - "contingent"."time_frame"
1502 )
1503 ELSE NULL END AS "text_entry_count",
1504 "contingent"."text_entry_limit",
1505 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
1506 SELECT count(1) FROM "opening_draft"
1507 WHERE "opening_draft"."author_id" = "member"."id"
1508 AND "opening_draft"."created" > now() - "contingent"."time_frame"
1509 ) ELSE NULL END AS "initiative_count",
1510 "contingent"."initiative_limit"
1511 FROM "member" CROSS JOIN "contingent";
1513 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
1515 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
1516 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
1519 CREATE VIEW "member_contingent_left" AS
1520 SELECT
1521 "member_id",
1522 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
1523 max("initiative_limit" - "initiative_count") AS "initiatives_left"
1524 FROM "member_contingent" GROUP BY "member_id";
1526 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.';
1529 CREATE TYPE "timeline_event" AS ENUM (
1530 'issue_created',
1531 'issue_canceled',
1532 'issue_accepted',
1533 'issue_half_frozen',
1534 'issue_finished_without_voting',
1535 'issue_voting_started',
1536 'issue_finished_after_voting',
1537 'initiative_created',
1538 'initiative_revoked',
1539 'draft_created',
1540 'suggestion_created');
1542 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables';
1545 CREATE VIEW "timeline_issue" AS
1546 SELECT
1547 "created" AS "occurrence",
1548 'issue_created'::"timeline_event" AS "event",
1549 "id" AS "issue_id"
1550 FROM "issue"
1551 UNION ALL
1552 SELECT
1553 "closed" AS "occurrence",
1554 'issue_canceled'::"timeline_event" AS "event",
1555 "id" AS "issue_id"
1556 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
1557 UNION ALL
1558 SELECT
1559 "accepted" AS "occurrence",
1560 'issue_accepted'::"timeline_event" AS "event",
1561 "id" AS "issue_id"
1562 FROM "issue" WHERE "accepted" NOTNULL
1563 UNION ALL
1564 SELECT
1565 "half_frozen" AS "occurrence",
1566 'issue_half_frozen'::"timeline_event" AS "event",
1567 "id" AS "issue_id"
1568 FROM "issue" WHERE "half_frozen" NOTNULL
1569 UNION ALL
1570 SELECT
1571 "fully_frozen" AS "occurrence",
1572 'issue_voting_started'::"timeline_event" AS "event",
1573 "id" AS "issue_id"
1574 FROM "issue"
1575 WHERE "fully_frozen" NOTNULL
1576 AND ("closed" ISNULL OR "closed" != "fully_frozen")
1577 UNION ALL
1578 SELECT
1579 "closed" AS "occurrence",
1580 CASE WHEN "fully_frozen" = "closed" THEN
1581 'issue_finished_without_voting'::"timeline_event"
1582 ELSE
1583 'issue_finished_after_voting'::"timeline_event"
1584 END AS "event",
1585 "id" AS "issue_id"
1586 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
1588 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view';
1591 CREATE VIEW "timeline_initiative" AS
1592 SELECT
1593 "created" AS "occurrence",
1594 'initiative_created'::"timeline_event" AS "event",
1595 "id" AS "initiative_id"
1596 FROM "initiative"
1597 UNION ALL
1598 SELECT
1599 "revoked" AS "occurrence",
1600 'initiative_revoked'::"timeline_event" AS "event",
1601 "id" AS "initiative_id"
1602 FROM "initiative" WHERE "revoked" NOTNULL;
1604 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view';
1607 CREATE VIEW "timeline_draft" AS
1608 SELECT
1609 "created" AS "occurrence",
1610 'draft_created'::"timeline_event" AS "event",
1611 "id" AS "draft_id"
1612 FROM "draft";
1614 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view';
1617 CREATE VIEW "timeline_suggestion" AS
1618 SELECT
1619 "created" AS "occurrence",
1620 'suggestion_created'::"timeline_event" AS "event",
1621 "id" AS "suggestion_id"
1622 FROM "suggestion";
1624 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view';
1627 CREATE VIEW "timeline" AS
1628 SELECT
1629 "occurrence",
1630 "event",
1631 "issue_id",
1632 NULL AS "initiative_id",
1633 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
1634 NULL::INT8 AS "suggestion_id"
1635 FROM "timeline_issue"
1636 UNION ALL
1637 SELECT
1638 "occurrence",
1639 "event",
1640 NULL AS "issue_id",
1641 "initiative_id",
1642 NULL AS "draft_id",
1643 NULL AS "suggestion_id"
1644 FROM "timeline_initiative"
1645 UNION ALL
1646 SELECT
1647 "occurrence",
1648 "event",
1649 NULL AS "issue_id",
1650 NULL AS "initiative_id",
1651 "draft_id",
1652 NULL AS "suggestion_id"
1653 FROM "timeline_draft"
1654 UNION ALL
1655 SELECT
1656 "occurrence",
1657 "event",
1658 NULL AS "issue_id",
1659 NULL AS "initiative_id",
1660 NULL AS "draft_id",
1661 "suggestion_id"
1662 FROM "timeline_suggestion";
1664 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system';
1668 --------------------------------------------------
1669 -- Set returning function for delegation chains --
1670 --------------------------------------------------
1673 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
1674 ('first', 'intermediate', 'last', 'repetition');
1676 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
1679 CREATE TYPE "delegation_chain_row" AS (
1680 "index" INT4,
1681 "member_id" INT4,
1682 "member_active" BOOLEAN,
1683 "participation" BOOLEAN,
1684 "overridden" BOOLEAN,
1685 "scope_in" "delegation_scope",
1686 "scope_out" "delegation_scope",
1687 "loop" "delegation_chain_loop_tag" );
1689 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
1691 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
1692 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';
1693 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
1694 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
1695 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
1696 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
1699 CREATE FUNCTION "delegation_chain"
1700 ( "member_id_p" "member"."id"%TYPE,
1701 "area_id_p" "area"."id"%TYPE,
1702 "issue_id_p" "issue"."id"%TYPE,
1703 "simulate_trustee_id_p" "member"."id"%TYPE )
1704 RETURNS SETOF "delegation_chain_row"
1705 LANGUAGE 'plpgsql' STABLE AS $$
1706 DECLARE
1707 "issue_row" "issue"%ROWTYPE;
1708 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
1709 "loop_member_id_v" "member"."id"%TYPE;
1710 "output_row" "delegation_chain_row";
1711 "output_rows" "delegation_chain_row"[];
1712 "delegation_row" "delegation"%ROWTYPE;
1713 "row_count" INT4;
1714 "i" INT4;
1715 "loop_v" BOOLEAN;
1716 BEGIN
1717 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1718 "visited_member_ids" := '{}';
1719 "loop_member_id_v" := NULL;
1720 "output_rows" := '{}';
1721 "output_row"."index" := 0;
1722 "output_row"."member_id" := "member_id_p";
1723 "output_row"."member_active" := TRUE;
1724 "output_row"."participation" := FALSE;
1725 "output_row"."overridden" := FALSE;
1726 "output_row"."scope_out" := NULL;
1727 LOOP
1728 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
1729 "loop_member_id_v" := "output_row"."member_id";
1730 ELSE
1731 "visited_member_ids" :=
1732 "visited_member_ids" || "output_row"."member_id";
1733 END IF;
1734 IF "output_row"."participation" THEN
1735 "output_row"."overridden" := TRUE;
1736 END IF;
1737 "output_row"."scope_in" := "output_row"."scope_out";
1738 IF EXISTS (
1739 SELECT NULL FROM "member"
1740 WHERE "id" = "output_row"."member_id" AND "active"
1741 ) THEN
1742 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
1743 SELECT * INTO "delegation_row" FROM "delegation"
1744 WHERE "truster_id" = "output_row"."member_id"
1745 AND "scope" = 'global';
1746 ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN
1747 "output_row"."participation" := EXISTS (
1748 SELECT NULL FROM "membership"
1749 WHERE "area_id" = "area_id_p"
1750 AND "member_id" = "output_row"."member_id"
1751 );
1752 SELECT * INTO "delegation_row" FROM "delegation"
1753 WHERE "truster_id" = "output_row"."member_id"
1754 AND ("scope" = 'global' OR "area_id" = "area_id_p")
1755 ORDER BY "scope" DESC;
1756 ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN
1757 "output_row"."participation" := EXISTS (
1758 SELECT NULL FROM "interest"
1759 WHERE "issue_id" = "issue_id_p"
1760 AND "member_id" = "output_row"."member_id"
1761 );
1762 SELECT * INTO "delegation_row" FROM "delegation"
1763 WHERE "truster_id" = "output_row"."member_id"
1764 AND ("scope" = 'global' OR
1765 "area_id" = "issue_row"."area_id" OR
1766 "issue_id" = "issue_id_p"
1767 )
1768 ORDER BY "scope" DESC;
1769 ELSE
1770 RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.';
1771 END IF;
1772 ELSE
1773 "output_row"."member_active" := FALSE;
1774 "output_row"."participation" := FALSE;
1775 "output_row"."scope_out" := NULL;
1776 "delegation_row" := ROW(NULL);
1777 END IF;
1778 IF
1779 "output_row"."member_id" = "member_id_p" AND
1780 "simulate_trustee_id_p" NOTNULL
1781 THEN
1782 "output_row"."scope_out" := CASE
1783 WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global'
1784 WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area'
1785 WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue'
1786 END;
1787 "output_rows" := "output_rows" || "output_row";
1788 "output_row"."member_id" := "simulate_trustee_id_p";
1789 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
1790 "output_row"."scope_out" := "delegation_row"."scope";
1791 "output_rows" := "output_rows" || "output_row";
1792 "output_row"."member_id" := "delegation_row"."trustee_id";
1793 ELSE
1794 "output_row"."scope_out" := NULL;
1795 "output_rows" := "output_rows" || "output_row";
1796 EXIT;
1797 END IF;
1798 EXIT WHEN "loop_member_id_v" NOTNULL;
1799 "output_row"."index" := "output_row"."index" + 1;
1800 END LOOP;
1801 "row_count" := array_upper("output_rows", 1);
1802 "i" := 1;
1803 "loop_v" := FALSE;
1804 LOOP
1805 "output_row" := "output_rows"["i"];
1806 EXIT WHEN "output_row"."member_id" ISNULL;
1807 IF "loop_v" THEN
1808 IF "i" + 1 = "row_count" THEN
1809 "output_row"."loop" := 'last';
1810 ELSIF "i" = "row_count" THEN
1811 "output_row"."loop" := 'repetition';
1812 ELSE
1813 "output_row"."loop" := 'intermediate';
1814 END IF;
1815 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
1816 "output_row"."loop" := 'first';
1817 "loop_v" := TRUE;
1818 END IF;
1819 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
1820 "output_row"."participation" := NULL;
1821 END IF;
1822 RETURN NEXT "output_row";
1823 "i" := "i" + 1;
1824 END LOOP;
1825 RETURN;
1826 END;
1827 $$;
1829 COMMENT ON FUNCTION "delegation_chain"
1830 ( "member"."id"%TYPE,
1831 "area"."id"%TYPE,
1832 "issue"."id"%TYPE,
1833 "member"."id"%TYPE )
1834 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
1836 CREATE FUNCTION "delegation_chain"
1837 ( "member_id_p" "member"."id"%TYPE,
1838 "area_id_p" "area"."id"%TYPE,
1839 "issue_id_p" "issue"."id"%TYPE )
1840 RETURNS SETOF "delegation_chain_row"
1841 LANGUAGE 'plpgsql' STABLE AS $$
1842 DECLARE
1843 "result_row" "delegation_chain_row";
1844 BEGIN
1845 FOR "result_row" IN
1846 SELECT * FROM "delegation_chain"(
1847 "member_id_p", "area_id_p", "issue_id_p", NULL
1848 )
1849 LOOP
1850 RETURN NEXT "result_row";
1851 END LOOP;
1852 RETURN;
1853 END;
1854 $$;
1856 COMMENT ON FUNCTION "delegation_chain"
1857 ( "member"."id"%TYPE,
1858 "area"."id"%TYPE,
1859 "issue"."id"%TYPE )
1860 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
1864 ------------------------------
1865 -- Comparison by vote count --
1866 ------------------------------
1868 CREATE FUNCTION "vote_ratio"
1869 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
1870 "negative_votes_p" "initiative"."negative_votes"%TYPE )
1871 RETURNS FLOAT8
1872 LANGUAGE 'plpgsql' STABLE AS $$
1873 BEGIN
1874 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
1875 RETURN
1876 "positive_votes_p"::FLOAT8 /
1877 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
1878 ELSIF "positive_votes_p" > 0 THEN
1879 RETURN "positive_votes_p";
1880 ELSIF "negative_votes_p" > 0 THEN
1881 RETURN 1 - "negative_votes_p";
1882 ELSE
1883 RETURN 0.5;
1884 END IF;
1885 END;
1886 $$;
1888 COMMENT ON FUNCTION "vote_ratio"
1889 ( "initiative"."positive_votes"%TYPE,
1890 "initiative"."negative_votes"%TYPE )
1891 IS 'Returns a number, which can be used for comparison of initiatives based on count of approvals and disapprovals. Greater numbers indicate a better result. This function is NOT injective.';
1895 ------------------------------------------------
1896 -- Locking for snapshots and voting procedure --
1897 ------------------------------------------------
1899 CREATE FUNCTION "global_lock"() RETURNS VOID
1900 LANGUAGE 'plpgsql' VOLATILE AS $$
1901 BEGIN
1902 -- NOTE: PostgreSQL allows reading, while tables are locked in
1903 -- exclusive move. Transactions should be kept short anyway!
1904 LOCK TABLE "member" IN EXCLUSIVE MODE;
1905 LOCK TABLE "area" IN EXCLUSIVE MODE;
1906 LOCK TABLE "membership" IN EXCLUSIVE MODE;
1907 -- NOTE: "member", "area" and "membership" are locked first to
1908 -- prevent deadlocks in combination with "calculate_member_counts"()
1909 LOCK TABLE "policy" IN EXCLUSIVE MODE;
1910 LOCK TABLE "issue" IN EXCLUSIVE MODE;
1911 LOCK TABLE "initiative" IN EXCLUSIVE MODE;
1912 LOCK TABLE "draft" IN EXCLUSIVE MODE;
1913 LOCK TABLE "suggestion" IN EXCLUSIVE MODE;
1914 LOCK TABLE "interest" IN EXCLUSIVE MODE;
1915 LOCK TABLE "initiator" IN EXCLUSIVE MODE;
1916 LOCK TABLE "supporter" IN EXCLUSIVE MODE;
1917 LOCK TABLE "opinion" IN EXCLUSIVE MODE;
1918 LOCK TABLE "delegation" IN EXCLUSIVE MODE;
1919 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
1920 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
1921 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
1922 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
1923 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
1924 LOCK TABLE "direct_voter" IN EXCLUSIVE MODE;
1925 LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;
1926 LOCK TABLE "vote" IN EXCLUSIVE MODE;
1927 RETURN;
1928 END;
1929 $$;
1931 COMMENT ON FUNCTION "global_lock"() IS 'Locks all tables related to support/voting until end of transaction; read access is still possible though';
1935 -------------------------------
1936 -- Materialize member counts --
1937 -------------------------------
1939 CREATE FUNCTION "calculate_member_counts"()
1940 RETURNS VOID
1941 LANGUAGE 'plpgsql' VOLATILE AS $$
1942 BEGIN
1943 LOCK TABLE "member" IN EXCLUSIVE MODE;
1944 LOCK TABLE "area" IN EXCLUSIVE MODE;
1945 LOCK TABLE "membership" IN EXCLUSIVE MODE;
1946 DELETE FROM "member_count";
1947 INSERT INTO "member_count" ("total_count")
1948 SELECT "total_count" FROM "member_count_view";
1949 UPDATE "area" SET
1950 "direct_member_count" = "view"."direct_member_count",
1951 "member_weight" = "view"."member_weight",
1952 "autoreject_weight" = "view"."autoreject_weight"
1953 FROM "area_member_count" AS "view"
1954 WHERE "view"."area_id" = "area"."id";
1955 RETURN;
1956 END;
1957 $$;
1959 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"';
1963 ------------------------------
1964 -- Calculation of snapshots --
1965 ------------------------------
1967 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
1968 ( "issue_id_p" "issue"."id"%TYPE,
1969 "member_id_p" "member"."id"%TYPE,
1970 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
1971 RETURNS "direct_population_snapshot"."weight"%TYPE
1972 LANGUAGE 'plpgsql' VOLATILE AS $$
1973 DECLARE
1974 "issue_delegation_row" "issue_delegation"%ROWTYPE;
1975 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
1976 "weight_v" INT4;
1977 "sub_weight_v" INT4;
1978 BEGIN
1979 "weight_v" := 0;
1980 FOR "issue_delegation_row" IN
1981 SELECT * FROM "issue_delegation"
1982 WHERE "trustee_id" = "member_id_p"
1983 AND "issue_id" = "issue_id_p"
1984 LOOP
1985 IF NOT EXISTS (
1986 SELECT NULL FROM "direct_population_snapshot"
1987 WHERE "issue_id" = "issue_id_p"
1988 AND "event" = 'periodic'
1989 AND "member_id" = "issue_delegation_row"."truster_id"
1990 ) AND NOT EXISTS (
1991 SELECT NULL FROM "delegating_population_snapshot"
1992 WHERE "issue_id" = "issue_id_p"
1993 AND "event" = 'periodic'
1994 AND "member_id" = "issue_delegation_row"."truster_id"
1995 ) THEN
1996 "delegate_member_ids_v" :=
1997 "member_id_p" || "delegate_member_ids_p";
1998 INSERT INTO "delegating_population_snapshot" (
1999 "issue_id",
2000 "event",
2001 "member_id",
2002 "scope",
2003 "delegate_member_ids"
2004 ) VALUES (
2005 "issue_id_p",
2006 'periodic',
2007 "issue_delegation_row"."truster_id",
2008 "issue_delegation_row"."scope",
2009 "delegate_member_ids_v"
2010 );
2011 "sub_weight_v" := 1 +
2012 "weight_of_added_delegations_for_population_snapshot"(
2013 "issue_id_p",
2014 "issue_delegation_row"."truster_id",
2015 "delegate_member_ids_v"
2016 );
2017 UPDATE "delegating_population_snapshot"
2018 SET "weight" = "sub_weight_v"
2019 WHERE "issue_id" = "issue_id_p"
2020 AND "event" = 'periodic'
2021 AND "member_id" = "issue_delegation_row"."truster_id";
2022 "weight_v" := "weight_v" + "sub_weight_v";
2023 END IF;
2024 END LOOP;
2025 RETURN "weight_v";
2026 END;
2027 $$;
2029 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
2030 ( "issue"."id"%TYPE,
2031 "member"."id"%TYPE,
2032 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2033 IS 'Helper function for "create_population_snapshot" function';
2036 CREATE FUNCTION "create_population_snapshot"
2037 ( "issue_id_p" "issue"."id"%TYPE )
2038 RETURNS VOID
2039 LANGUAGE 'plpgsql' VOLATILE AS $$
2040 DECLARE
2041 "member_id_v" "member"."id"%TYPE;
2042 BEGIN
2043 DELETE FROM "direct_population_snapshot"
2044 WHERE "issue_id" = "issue_id_p"
2045 AND "event" = 'periodic';
2046 DELETE FROM "delegating_population_snapshot"
2047 WHERE "issue_id" = "issue_id_p"
2048 AND "event" = 'periodic';
2049 INSERT INTO "direct_population_snapshot"
2050 ("issue_id", "event", "member_id")
2051 SELECT
2052 "issue_id_p" AS "issue_id",
2053 'periodic'::"snapshot_event" AS "event",
2054 "member"."id" AS "member_id"
2055 FROM "issue"
2056 JOIN "area" ON "issue"."area_id" = "area"."id"
2057 JOIN "membership" ON "area"."id" = "membership"."area_id"
2058 JOIN "member" ON "membership"."member_id" = "member"."id"
2059 WHERE "issue"."id" = "issue_id_p"
2060 AND "member"."active"
2061 UNION
2062 SELECT
2063 "issue_id_p" AS "issue_id",
2064 'periodic'::"snapshot_event" AS "event",
2065 "member"."id" AS "member_id"
2066 FROM "interest" JOIN "member"
2067 ON "interest"."member_id" = "member"."id"
2068 WHERE "interest"."issue_id" = "issue_id_p"
2069 AND "member"."active";
2070 FOR "member_id_v" IN
2071 SELECT "member_id" FROM "direct_population_snapshot"
2072 WHERE "issue_id" = "issue_id_p"
2073 AND "event" = 'periodic'
2074 LOOP
2075 UPDATE "direct_population_snapshot" SET
2076 "weight" = 1 +
2077 "weight_of_added_delegations_for_population_snapshot"(
2078 "issue_id_p",
2079 "member_id_v",
2080 '{}'
2081 )
2082 WHERE "issue_id" = "issue_id_p"
2083 AND "event" = 'periodic'
2084 AND "member_id" = "member_id_v";
2085 END LOOP;
2086 RETURN;
2087 END;
2088 $$;
2090 COMMENT ON FUNCTION "create_population_snapshot"
2091 ( "issue_id_p" "issue"."id"%TYPE )
2092 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.';
2095 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2096 ( "issue_id_p" "issue"."id"%TYPE,
2097 "member_id_p" "member"."id"%TYPE,
2098 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2099 RETURNS "direct_interest_snapshot"."weight"%TYPE
2100 LANGUAGE 'plpgsql' VOLATILE AS $$
2101 DECLARE
2102 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2103 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2104 "weight_v" INT4;
2105 "sub_weight_v" INT4;
2106 BEGIN
2107 "weight_v" := 0;
2108 FOR "issue_delegation_row" IN
2109 SELECT * FROM "issue_delegation"
2110 WHERE "trustee_id" = "member_id_p"
2111 AND "issue_id" = "issue_id_p"
2112 LOOP
2113 IF NOT EXISTS (
2114 SELECT NULL FROM "direct_interest_snapshot"
2115 WHERE "issue_id" = "issue_id_p"
2116 AND "event" = 'periodic'
2117 AND "member_id" = "issue_delegation_row"."truster_id"
2118 ) AND NOT EXISTS (
2119 SELECT NULL FROM "delegating_interest_snapshot"
2120 WHERE "issue_id" = "issue_id_p"
2121 AND "event" = 'periodic'
2122 AND "member_id" = "issue_delegation_row"."truster_id"
2123 ) THEN
2124 "delegate_member_ids_v" :=
2125 "member_id_p" || "delegate_member_ids_p";
2126 INSERT INTO "delegating_interest_snapshot" (
2127 "issue_id",
2128 "event",
2129 "member_id",
2130 "scope",
2131 "delegate_member_ids"
2132 ) VALUES (
2133 "issue_id_p",
2134 'periodic',
2135 "issue_delegation_row"."truster_id",
2136 "issue_delegation_row"."scope",
2137 "delegate_member_ids_v"
2138 );
2139 "sub_weight_v" := 1 +
2140 "weight_of_added_delegations_for_interest_snapshot"(
2141 "issue_id_p",
2142 "issue_delegation_row"."truster_id",
2143 "delegate_member_ids_v"
2144 );
2145 UPDATE "delegating_interest_snapshot"
2146 SET "weight" = "sub_weight_v"
2147 WHERE "issue_id" = "issue_id_p"
2148 AND "event" = 'periodic'
2149 AND "member_id" = "issue_delegation_row"."truster_id";
2150 "weight_v" := "weight_v" + "sub_weight_v";
2151 END IF;
2152 END LOOP;
2153 RETURN "weight_v";
2154 END;
2155 $$;
2157 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2158 ( "issue"."id"%TYPE,
2159 "member"."id"%TYPE,
2160 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2161 IS 'Helper function for "create_interest_snapshot" function';
2164 CREATE FUNCTION "create_interest_snapshot"
2165 ( "issue_id_p" "issue"."id"%TYPE )
2166 RETURNS VOID
2167 LANGUAGE 'plpgsql' VOLATILE AS $$
2168 DECLARE
2169 "member_id_v" "member"."id"%TYPE;
2170 BEGIN
2171 DELETE FROM "direct_interest_snapshot"
2172 WHERE "issue_id" = "issue_id_p"
2173 AND "event" = 'periodic';
2174 DELETE FROM "delegating_interest_snapshot"
2175 WHERE "issue_id" = "issue_id_p"
2176 AND "event" = 'periodic';
2177 DELETE FROM "direct_supporter_snapshot"
2178 WHERE "issue_id" = "issue_id_p"
2179 AND "event" = 'periodic';
2180 INSERT INTO "direct_interest_snapshot"
2181 ("issue_id", "event", "member_id", "voting_requested")
2182 SELECT
2183 "issue_id_p" AS "issue_id",
2184 'periodic' AS "event",
2185 "member"."id" AS "member_id",
2186 "interest"."voting_requested"
2187 FROM "interest" JOIN "member"
2188 ON "interest"."member_id" = "member"."id"
2189 WHERE "interest"."issue_id" = "issue_id_p"
2190 AND "member"."active";
2191 FOR "member_id_v" IN
2192 SELECT "member_id" FROM "direct_interest_snapshot"
2193 WHERE "issue_id" = "issue_id_p"
2194 AND "event" = 'periodic'
2195 LOOP
2196 UPDATE "direct_interest_snapshot" SET
2197 "weight" = 1 +
2198 "weight_of_added_delegations_for_interest_snapshot"(
2199 "issue_id_p",
2200 "member_id_v",
2201 '{}'
2202 )
2203 WHERE "issue_id" = "issue_id_p"
2204 AND "event" = 'periodic'
2205 AND "member_id" = "member_id_v";
2206 END LOOP;
2207 INSERT INTO "direct_supporter_snapshot"
2208 ( "issue_id", "initiative_id", "event", "member_id",
2209 "informed", "satisfied" )
2210 SELECT
2211 "issue_id_p" AS "issue_id",
2212 "initiative"."id" AS "initiative_id",
2213 'periodic' AS "event",
2214 "member"."id" AS "member_id",
2215 "supporter"."draft_id" = "current_draft"."id" AS "informed",
2216 NOT EXISTS (
2217 SELECT NULL FROM "critical_opinion"
2218 WHERE "initiative_id" = "initiative"."id"
2219 AND "member_id" = "member"."id"
2220 ) AS "satisfied"
2221 FROM "supporter"
2222 JOIN "member"
2223 ON "supporter"."member_id" = "member"."id"
2224 JOIN "initiative"
2225 ON "supporter"."initiative_id" = "initiative"."id"
2226 JOIN "current_draft"
2227 ON "initiative"."id" = "current_draft"."initiative_id"
2228 JOIN "direct_interest_snapshot"
2229 ON "member"."id" = "direct_interest_snapshot"."member_id"
2230 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
2231 AND "event" = 'periodic'
2232 WHERE "member"."active"
2233 AND "initiative"."issue_id" = "issue_id_p";
2234 RETURN;
2235 END;
2236 $$;
2238 COMMENT ON FUNCTION "create_interest_snapshot"
2239 ( "issue"."id"%TYPE )
2240 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.';
2243 CREATE FUNCTION "create_snapshot"
2244 ( "issue_id_p" "issue"."id"%TYPE )
2245 RETURNS VOID
2246 LANGUAGE 'plpgsql' VOLATILE AS $$
2247 DECLARE
2248 "initiative_id_v" "initiative"."id"%TYPE;
2249 "suggestion_id_v" "suggestion"."id"%TYPE;
2250 BEGIN
2251 PERFORM "global_lock"();
2252 PERFORM "create_population_snapshot"("issue_id_p");
2253 PERFORM "create_interest_snapshot"("issue_id_p");
2254 UPDATE "issue" SET
2255 "snapshot" = now(),
2256 "latest_snapshot_event" = 'periodic',
2257 "population" = (
2258 SELECT coalesce(sum("weight"), 0)
2259 FROM "direct_population_snapshot"
2260 WHERE "issue_id" = "issue_id_p"
2261 AND "event" = 'periodic'
2262 ),
2263 "vote_now" = (
2264 SELECT coalesce(sum("weight"), 0)
2265 FROM "direct_interest_snapshot"
2266 WHERE "issue_id" = "issue_id_p"
2267 AND "event" = 'periodic'
2268 AND "voting_requested" = TRUE
2269 ),
2270 "vote_later" = (
2271 SELECT coalesce(sum("weight"), 0)
2272 FROM "direct_interest_snapshot"
2273 WHERE "issue_id" = "issue_id_p"
2274 AND "event" = 'periodic'
2275 AND "voting_requested" = FALSE
2276 )
2277 WHERE "id" = "issue_id_p";
2278 FOR "initiative_id_v" IN
2279 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
2280 LOOP
2281 UPDATE "initiative" SET
2282 "supporter_count" = (
2283 SELECT coalesce(sum("di"."weight"), 0)
2284 FROM "direct_interest_snapshot" AS "di"
2285 JOIN "direct_supporter_snapshot" AS "ds"
2286 ON "di"."member_id" = "ds"."member_id"
2287 WHERE "di"."issue_id" = "issue_id_p"
2288 AND "di"."event" = 'periodic'
2289 AND "ds"."initiative_id" = "initiative_id_v"
2290 AND "ds"."event" = 'periodic'
2291 ),
2292 "informed_supporter_count" = (
2293 SELECT coalesce(sum("di"."weight"), 0)
2294 FROM "direct_interest_snapshot" AS "di"
2295 JOIN "direct_supporter_snapshot" AS "ds"
2296 ON "di"."member_id" = "ds"."member_id"
2297 WHERE "di"."issue_id" = "issue_id_p"
2298 AND "di"."event" = 'periodic'
2299 AND "ds"."initiative_id" = "initiative_id_v"
2300 AND "ds"."event" = 'periodic'
2301 AND "ds"."informed"
2302 ),
2303 "satisfied_supporter_count" = (
2304 SELECT coalesce(sum("di"."weight"), 0)
2305 FROM "direct_interest_snapshot" AS "di"
2306 JOIN "direct_supporter_snapshot" AS "ds"
2307 ON "di"."member_id" = "ds"."member_id"
2308 WHERE "di"."issue_id" = "issue_id_p"
2309 AND "di"."event" = 'periodic'
2310 AND "ds"."initiative_id" = "initiative_id_v"
2311 AND "ds"."event" = 'periodic'
2312 AND "ds"."satisfied"
2313 ),
2314 "satisfied_informed_supporter_count" = (
2315 SELECT coalesce(sum("di"."weight"), 0)
2316 FROM "direct_interest_snapshot" AS "di"
2317 JOIN "direct_supporter_snapshot" AS "ds"
2318 ON "di"."member_id" = "ds"."member_id"
2319 WHERE "di"."issue_id" = "issue_id_p"
2320 AND "di"."event" = 'periodic'
2321 AND "ds"."initiative_id" = "initiative_id_v"
2322 AND "ds"."event" = 'periodic'
2323 AND "ds"."informed"
2324 AND "ds"."satisfied"
2325 )
2326 WHERE "id" = "initiative_id_v";
2327 FOR "suggestion_id_v" IN
2328 SELECT "id" FROM "suggestion"
2329 WHERE "initiative_id" = "initiative_id_v"
2330 LOOP
2331 UPDATE "suggestion" SET
2332 "minus2_unfulfilled_count" = (
2333 SELECT coalesce(sum("snapshot"."weight"), 0)
2334 FROM "issue" CROSS JOIN "opinion"
2335 JOIN "direct_interest_snapshot" AS "snapshot"
2336 ON "snapshot"."issue_id" = "issue"."id"
2337 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2338 AND "snapshot"."member_id" = "opinion"."member_id"
2339 WHERE "issue"."id" = "issue_id_p"
2340 AND "opinion"."suggestion_id" = "suggestion_id_v"
2341 AND "opinion"."degree" = -2
2342 AND "opinion"."fulfilled" = FALSE
2343 ),
2344 "minus2_fulfilled_count" = (
2345 SELECT coalesce(sum("snapshot"."weight"), 0)
2346 FROM "issue" CROSS JOIN "opinion"
2347 JOIN "direct_interest_snapshot" AS "snapshot"
2348 ON "snapshot"."issue_id" = "issue"."id"
2349 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2350 AND "snapshot"."member_id" = "opinion"."member_id"
2351 WHERE "issue"."id" = "issue_id_p"
2352 AND "opinion"."suggestion_id" = "suggestion_id_v"
2353 AND "opinion"."degree" = -2
2354 AND "opinion"."fulfilled" = TRUE
2355 ),
2356 "minus1_unfulfilled_count" = (
2357 SELECT coalesce(sum("snapshot"."weight"), 0)
2358 FROM "issue" CROSS JOIN "opinion"
2359 JOIN "direct_interest_snapshot" AS "snapshot"
2360 ON "snapshot"."issue_id" = "issue"."id"
2361 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2362 AND "snapshot"."member_id" = "opinion"."member_id"
2363 WHERE "issue"."id" = "issue_id_p"
2364 AND "opinion"."suggestion_id" = "suggestion_id_v"
2365 AND "opinion"."degree" = -1
2366 AND "opinion"."fulfilled" = FALSE
2367 ),
2368 "minus1_fulfilled_count" = (
2369 SELECT coalesce(sum("snapshot"."weight"), 0)
2370 FROM "issue" CROSS JOIN "opinion"
2371 JOIN "direct_interest_snapshot" AS "snapshot"
2372 ON "snapshot"."issue_id" = "issue"."id"
2373 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2374 AND "snapshot"."member_id" = "opinion"."member_id"
2375 WHERE "issue"."id" = "issue_id_p"
2376 AND "opinion"."suggestion_id" = "suggestion_id_v"
2377 AND "opinion"."degree" = -1
2378 AND "opinion"."fulfilled" = TRUE
2379 ),
2380 "plus1_unfulfilled_count" = (
2381 SELECT coalesce(sum("snapshot"."weight"), 0)
2382 FROM "issue" CROSS JOIN "opinion"
2383 JOIN "direct_interest_snapshot" AS "snapshot"
2384 ON "snapshot"."issue_id" = "issue"."id"
2385 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2386 AND "snapshot"."member_id" = "opinion"."member_id"
2387 WHERE "issue"."id" = "issue_id_p"
2388 AND "opinion"."suggestion_id" = "suggestion_id_v"
2389 AND "opinion"."degree" = 1
2390 AND "opinion"."fulfilled" = FALSE
2391 ),
2392 "plus1_fulfilled_count" = (
2393 SELECT coalesce(sum("snapshot"."weight"), 0)
2394 FROM "issue" CROSS JOIN "opinion"
2395 JOIN "direct_interest_snapshot" AS "snapshot"
2396 ON "snapshot"."issue_id" = "issue"."id"
2397 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2398 AND "snapshot"."member_id" = "opinion"."member_id"
2399 WHERE "issue"."id" = "issue_id_p"
2400 AND "opinion"."suggestion_id" = "suggestion_id_v"
2401 AND "opinion"."degree" = 1
2402 AND "opinion"."fulfilled" = TRUE
2403 ),
2404 "plus2_unfulfilled_count" = (
2405 SELECT coalesce(sum("snapshot"."weight"), 0)
2406 FROM "issue" CROSS JOIN "opinion"
2407 JOIN "direct_interest_snapshot" AS "snapshot"
2408 ON "snapshot"."issue_id" = "issue"."id"
2409 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2410 AND "snapshot"."member_id" = "opinion"."member_id"
2411 WHERE "issue"."id" = "issue_id_p"
2412 AND "opinion"."suggestion_id" = "suggestion_id_v"
2413 AND "opinion"."degree" = 2
2414 AND "opinion"."fulfilled" = FALSE
2415 ),
2416 "plus2_fulfilled_count" = (
2417 SELECT coalesce(sum("snapshot"."weight"), 0)
2418 FROM "issue" CROSS JOIN "opinion"
2419 JOIN "direct_interest_snapshot" AS "snapshot"
2420 ON "snapshot"."issue_id" = "issue"."id"
2421 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2422 AND "snapshot"."member_id" = "opinion"."member_id"
2423 WHERE "issue"."id" = "issue_id_p"
2424 AND "opinion"."suggestion_id" = "suggestion_id_v"
2425 AND "opinion"."degree" = 2
2426 AND "opinion"."fulfilled" = TRUE
2427 )
2428 WHERE "suggestion"."id" = "suggestion_id_v";
2429 END LOOP;
2430 END LOOP;
2431 RETURN;
2432 END;
2433 $$;
2435 COMMENT ON FUNCTION "create_snapshot"
2436 ( "issue"."id"%TYPE )
2437 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.';
2440 CREATE FUNCTION "set_snapshot_event"
2441 ( "issue_id_p" "issue"."id"%TYPE,
2442 "event_p" "snapshot_event" )
2443 RETURNS VOID
2444 LANGUAGE 'plpgsql' VOLATILE AS $$
2445 DECLARE
2446 "event_v" "issue"."latest_snapshot_event"%TYPE;
2447 BEGIN
2448 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
2449 WHERE "id" = "issue_id_p" FOR UPDATE;
2450 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
2451 WHERE "id" = "issue_id_p";
2452 UPDATE "direct_population_snapshot" SET "event" = "event_p"
2453 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2454 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
2455 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2456 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
2457 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2458 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
2459 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2460 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
2461 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2462 RETURN;
2463 END;
2464 $$;
2466 COMMENT ON FUNCTION "set_snapshot_event"
2467 ( "issue"."id"%TYPE,
2468 "snapshot_event" )
2469 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
2473 ---------------------
2474 -- Freezing issues --
2475 ---------------------
2477 CREATE FUNCTION "freeze_after_snapshot"
2478 ( "issue_id_p" "issue"."id"%TYPE )
2479 RETURNS VOID
2480 LANGUAGE 'plpgsql' VOLATILE AS $$
2481 DECLARE
2482 "issue_row" "issue"%ROWTYPE;
2483 "policy_row" "policy"%ROWTYPE;
2484 "initiative_row" "initiative"%ROWTYPE;
2485 BEGIN
2486 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2487 SELECT * INTO "policy_row"
2488 FROM "policy" WHERE "id" = "issue_row"."policy_id";
2489 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
2490 UPDATE "issue" SET
2491 "accepted" = coalesce("accepted", now()),
2492 "half_frozen" = coalesce("half_frozen", now()),
2493 "fully_frozen" = now()
2494 WHERE "id" = "issue_id_p";
2495 FOR "initiative_row" IN
2496 SELECT * FROM "initiative"
2497 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
2498 LOOP
2499 IF
2500 "initiative_row"."satisfied_supporter_count" > 0 AND
2501 "initiative_row"."satisfied_supporter_count" *
2502 "policy_row"."initiative_quorum_den" >=
2503 "issue_row"."population" * "policy_row"."initiative_quorum_num"
2504 THEN
2505 UPDATE "initiative" SET "admitted" = TRUE
2506 WHERE "id" = "initiative_row"."id";
2507 ELSE
2508 UPDATE "initiative" SET "admitted" = FALSE
2509 WHERE "id" = "initiative_row"."id";
2510 END IF;
2511 END LOOP;
2512 IF NOT EXISTS (
2513 SELECT NULL FROM "initiative"
2514 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
2515 ) THEN
2516 PERFORM "close_voting"("issue_id_p");
2517 END IF;
2518 RETURN;
2519 END;
2520 $$;
2522 COMMENT ON FUNCTION "freeze_after_snapshot"
2523 ( "issue"."id"%TYPE )
2524 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
2527 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
2528 RETURNS VOID
2529 LANGUAGE 'plpgsql' VOLATILE AS $$
2530 DECLARE
2531 "issue_row" "issue"%ROWTYPE;
2532 BEGIN
2533 PERFORM "create_snapshot"("issue_id_p");
2534 PERFORM "freeze_after_snapshot"("issue_id_p");
2535 RETURN;
2536 END;
2537 $$;
2539 COMMENT ON FUNCTION "manual_freeze"
2540 ( "issue"."id"%TYPE )
2541 IS 'Freeze an issue manually (fully) and start voting';
2545 -----------------------
2546 -- Counting of votes --
2547 -----------------------
2550 CREATE FUNCTION "weight_of_added_vote_delegations"
2551 ( "issue_id_p" "issue"."id"%TYPE,
2552 "member_id_p" "member"."id"%TYPE,
2553 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
2554 RETURNS "direct_voter"."weight"%TYPE
2555 LANGUAGE 'plpgsql' VOLATILE AS $$
2556 DECLARE
2557 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2558 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
2559 "weight_v" INT4;
2560 "sub_weight_v" INT4;
2561 BEGIN
2562 "weight_v" := 0;
2563 FOR "issue_delegation_row" IN
2564 SELECT * FROM "issue_delegation"
2565 WHERE "trustee_id" = "member_id_p"
2566 AND "issue_id" = "issue_id_p"
2567 LOOP
2568 IF NOT EXISTS (
2569 SELECT NULL FROM "direct_voter"
2570 WHERE "member_id" = "issue_delegation_row"."truster_id"
2571 AND "issue_id" = "issue_id_p"
2572 ) AND NOT EXISTS (
2573 SELECT NULL FROM "delegating_voter"
2574 WHERE "member_id" = "issue_delegation_row"."truster_id"
2575 AND "issue_id" = "issue_id_p"
2576 ) THEN
2577 "delegate_member_ids_v" :=
2578 "member_id_p" || "delegate_member_ids_p";
2579 INSERT INTO "delegating_voter" (
2580 "issue_id",
2581 "member_id",
2582 "scope",
2583 "delegate_member_ids"
2584 ) VALUES (
2585 "issue_id_p",
2586 "issue_delegation_row"."truster_id",
2587 "issue_delegation_row"."scope",
2588 "delegate_member_ids_v"
2589 );
2590 "sub_weight_v" := 1 +
2591 "weight_of_added_vote_delegations"(
2592 "issue_id_p",
2593 "issue_delegation_row"."truster_id",
2594 "delegate_member_ids_v"
2595 );
2596 UPDATE "delegating_voter"
2597 SET "weight" = "sub_weight_v"
2598 WHERE "issue_id" = "issue_id_p"
2599 AND "member_id" = "issue_delegation_row"."truster_id";
2600 "weight_v" := "weight_v" + "sub_weight_v";
2601 END IF;
2602 END LOOP;
2603 RETURN "weight_v";
2604 END;
2605 $$;
2607 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
2608 ( "issue"."id"%TYPE,
2609 "member"."id"%TYPE,
2610 "delegating_voter"."delegate_member_ids"%TYPE )
2611 IS 'Helper function for "add_vote_delegations" function';
2614 CREATE FUNCTION "add_vote_delegations"
2615 ( "issue_id_p" "issue"."id"%TYPE )
2616 RETURNS VOID
2617 LANGUAGE 'plpgsql' VOLATILE AS $$
2618 DECLARE
2619 "member_id_v" "member"."id"%TYPE;
2620 BEGIN
2621 FOR "member_id_v" IN
2622 SELECT "member_id" FROM "direct_voter"
2623 WHERE "issue_id" = "issue_id_p"
2624 LOOP
2625 UPDATE "direct_voter" SET
2626 "weight" = "weight" + "weight_of_added_vote_delegations"(
2627 "issue_id_p",
2628 "member_id_v",
2629 '{}'
2630 )
2631 WHERE "member_id" = "member_id_v"
2632 AND "issue_id" = "issue_id_p";
2633 END LOOP;
2634 RETURN;
2635 END;
2636 $$;
2638 COMMENT ON FUNCTION "add_vote_delegations"
2639 ( "issue_id_p" "issue"."id"%TYPE )
2640 IS 'Helper function for "close_voting" function';
2643 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
2644 RETURNS VOID
2645 LANGUAGE 'plpgsql' VOLATILE AS $$
2646 DECLARE
2647 "issue_row" "issue"%ROWTYPE;
2648 "member_id_v" "member"."id"%TYPE;
2649 BEGIN
2650 PERFORM "global_lock"();
2651 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2652 DELETE FROM "delegating_voter"
2653 WHERE "issue_id" = "issue_id_p";
2654 DELETE FROM "direct_voter"
2655 WHERE "issue_id" = "issue_id_p"
2656 AND "autoreject" = TRUE;
2657 DELETE FROM "direct_voter" USING "member"
2658 WHERE "direct_voter"."member_id" = "member"."id"
2659 AND "direct_voter"."issue_id" = "issue_id_p"
2660 AND "member"."active" = FALSE;
2661 UPDATE "direct_voter" SET "weight" = 1
2662 WHERE "issue_id" = "issue_id_p";
2663 PERFORM "add_vote_delegations"("issue_id_p");
2664 FOR "member_id_v" IN
2665 SELECT "interest"."member_id"
2666 FROM "interest"
2667 LEFT JOIN "direct_voter"
2668 ON "interest"."member_id" = "direct_voter"."member_id"
2669 AND "interest"."issue_id" = "direct_voter"."issue_id"
2670 LEFT JOIN "delegating_voter"
2671 ON "interest"."member_id" = "delegating_voter"."member_id"
2672 AND "interest"."issue_id" = "delegating_voter"."issue_id"
2673 WHERE "interest"."issue_id" = "issue_id_p"
2674 AND "interest"."autoreject" = TRUE
2675 AND "direct_voter"."member_id" ISNULL
2676 AND "delegating_voter"."member_id" ISNULL
2677 UNION SELECT "membership"."member_id"
2678 FROM "membership"
2679 LEFT JOIN "interest"
2680 ON "membership"."member_id" = "interest"."member_id"
2681 AND "interest"."issue_id" = "issue_id_p"
2682 LEFT JOIN "direct_voter"
2683 ON "membership"."member_id" = "direct_voter"."member_id"
2684 AND "direct_voter"."issue_id" = "issue_id_p"
2685 LEFT JOIN "delegating_voter"
2686 ON "membership"."member_id" = "delegating_voter"."member_id"
2687 AND "delegating_voter"."issue_id" = "issue_id_p"
2688 WHERE "membership"."area_id" = "issue_row"."area_id"
2689 AND "membership"."autoreject" = TRUE
2690 AND "interest"."autoreject" ISNULL
2691 AND "direct_voter"."member_id" ISNULL
2692 AND "delegating_voter"."member_id" ISNULL
2693 LOOP
2694 INSERT INTO "direct_voter"
2695 ("member_id", "issue_id", "weight", "autoreject") VALUES
2696 ("member_id_v", "issue_id_p", 1, TRUE);
2697 INSERT INTO "vote" (
2698 "member_id",
2699 "issue_id",
2700 "initiative_id",
2701 "grade"
2702 ) SELECT
2703 "member_id_v" AS "member_id",
2704 "issue_id_p" AS "issue_id",
2705 "id" AS "initiative_id",
2706 -1 AS "grade"
2707 FROM "initiative" WHERE "issue_id" = "issue_id_p";
2708 END LOOP;
2709 PERFORM "add_vote_delegations"("issue_id_p");
2710 UPDATE "issue" SET
2711 "voter_count" = (
2712 SELECT coalesce(sum("weight"), 0)
2713 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
2714 )
2715 WHERE "id" = "issue_id_p";
2716 UPDATE "initiative" SET
2717 "positive_votes" = "vote_counts"."positive_votes",
2718 "negative_votes" = "vote_counts"."negative_votes",
2719 "agreed" = CASE WHEN "majority_strict" THEN
2720 "vote_counts"."positive_votes" * "majority_den" >
2721 "majority_num" *
2722 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
2723 ELSE
2724 "vote_counts"."positive_votes" * "majority_den" >=
2725 "majority_num" *
2726 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
2727 END
2728 FROM
2729 ( SELECT
2730 "initiative"."id" AS "initiative_id",
2731 coalesce(
2732 sum(
2733 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
2734 ),
2735 0
2736 ) AS "positive_votes",
2737 coalesce(
2738 sum(
2739 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
2740 ),
2741 0
2742 ) AS "negative_votes"
2743 FROM "initiative"
2744 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
2745 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
2746 LEFT JOIN "direct_voter"
2747 ON "direct_voter"."issue_id" = "initiative"."issue_id"
2748 LEFT JOIN "vote"
2749 ON "vote"."initiative_id" = "initiative"."id"
2750 AND "vote"."member_id" = "direct_voter"."member_id"
2751 WHERE "initiative"."issue_id" = "issue_id_p"
2752 AND "initiative"."admitted" -- NOTE: NULL case is handled too
2753 GROUP BY "initiative"."id"
2754 ) AS "vote_counts",
2755 "issue",
2756 "policy"
2757 WHERE "vote_counts"."initiative_id" = "initiative"."id"
2758 AND "issue"."id" = "initiative"."issue_id"
2759 AND "policy"."id" = "issue"."policy_id";
2760 UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p";
2761 END;
2762 $$;
2764 COMMENT ON FUNCTION "close_voting"
2765 ( "issue"."id"%TYPE )
2766 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.';
2769 CREATE FUNCTION "defeat_strength"
2770 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
2771 RETURNS INT8
2772 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2773 BEGIN
2774 IF "positive_votes_p" > "negative_votes_p" THEN
2775 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
2776 ELSIF "positive_votes_p" = "negative_votes_p" THEN
2777 RETURN 0;
2778 ELSE
2779 RETURN -1;
2780 END IF;
2781 END;
2782 $$;
2784 COMMENT ON FUNCTION "defeat_strength"(INT4, INT4) IS 'Calculates defeat strength (INT8!) of a pairwise defeat primarily by the absolute number of votes for the winner and secondarily by the absolute number of votes for the loser';
2787 CREATE FUNCTION "array_init_string"("dim_p" INTEGER)
2788 RETURNS TEXT
2789 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2790 DECLARE
2791 "i" INTEGER;
2792 "ary_text_v" TEXT;
2793 BEGIN
2794 IF "dim_p" >= 1 THEN
2795 "ary_text_v" := '{NULL';
2796 "i" := "dim_p";
2797 LOOP
2798 "i" := "i" - 1;
2799 EXIT WHEN "i" = 0;
2800 "ary_text_v" := "ary_text_v" || ',NULL';
2801 END LOOP;
2802 "ary_text_v" := "ary_text_v" || '}';
2803 RETURN "ary_text_v";
2804 ELSE
2805 RAISE EXCEPTION 'Dimension needs to be at least 1.';
2806 END IF;
2807 END;
2808 $$;
2810 COMMENT ON FUNCTION "array_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
2813 CREATE FUNCTION "square_matrix_init_string"("dim_p" INTEGER)
2814 RETURNS TEXT
2815 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2816 DECLARE
2817 "i" INTEGER;
2818 "row_text_v" TEXT;
2819 "ary_text_v" TEXT;
2820 BEGIN
2821 IF "dim_p" >= 1 THEN
2822 "row_text_v" := '{NULL';
2823 "i" := "dim_p";
2824 LOOP
2825 "i" := "i" - 1;
2826 EXIT WHEN "i" = 0;
2827 "row_text_v" := "row_text_v" || ',NULL';
2828 END LOOP;
2829 "row_text_v" := "row_text_v" || '}';
2830 "ary_text_v" := '{' || "row_text_v";
2831 "i" := "dim_p";
2832 LOOP
2833 "i" := "i" - 1;
2834 EXIT WHEN "i" = 0;
2835 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
2836 END LOOP;
2837 "ary_text_v" := "ary_text_v" || '}';
2838 RETURN "ary_text_v";
2839 ELSE
2840 RAISE EXCEPTION 'Dimension needs to be at least 1.';
2841 END IF;
2842 END;
2843 $$;
2845 COMMENT ON FUNCTION "square_matrix_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
2848 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
2849 RETURNS VOID
2850 LANGUAGE 'plpgsql' VOLATILE AS $$
2851 DECLARE
2852 "dimension_v" INTEGER;
2853 "vote_matrix" INT4[][]; -- absolute votes
2854 "matrix" INT8[][]; -- defeat strength / best paths
2855 "i" INTEGER;
2856 "j" INTEGER;
2857 "k" INTEGER;
2858 "battle_row" "battle"%ROWTYPE;
2859 "rank_ary" INT4[];
2860 "rank_v" INT4;
2861 "done_v" INTEGER;
2862 "winners_ary" INTEGER[];
2863 "initiative_id_v" "initiative"."id"%TYPE;
2864 BEGIN
2865 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2866 SELECT count(1) INTO "dimension_v" FROM "initiative"
2867 WHERE "issue_id" = "issue_id_p" AND "agreed";
2868 IF "dimension_v" = 1 THEN
2869 UPDATE "initiative" SET "rank" = 1
2870 WHERE "issue_id" = "issue_id_p" AND "agreed";
2871 ELSIF "dimension_v" > 1 THEN
2872 -- Create "vote_matrix" with absolute number of votes in pairwise
2873 -- comparison:
2874 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2875 "i" := 1;
2876 "j" := 2;
2877 FOR "battle_row" IN
2878 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
2879 ORDER BY "winning_initiative_id", "losing_initiative_id"
2880 LOOP
2881 "vote_matrix"["i"]["j"] := "battle_row"."count";
2882 IF "j" = "dimension_v" THEN
2883 "i" := "i" + 1;
2884 "j" := 1;
2885 ELSE
2886 "j" := "j" + 1;
2887 IF "j" = "i" THEN
2888 "j" := "j" + 1;
2889 END IF;
2890 END IF;
2891 END LOOP;
2892 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
2893 RAISE EXCEPTION 'Wrong battle count (should not happen)';
2894 END IF;
2895 -- Store defeat strengths in "matrix" using "defeat_strength"
2896 -- function:
2897 "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2898 "i" := 1;
2899 LOOP
2900 "j" := 1;
2901 LOOP
2902 IF "i" != "j" THEN
2903 "matrix"["i"]["j"] := "defeat_strength"(
2904 "vote_matrix"["i"]["j"],
2905 "vote_matrix"["j"]["i"]
2906 );
2907 END IF;
2908 EXIT WHEN "j" = "dimension_v";
2909 "j" := "j" + 1;
2910 END LOOP;
2911 EXIT WHEN "i" = "dimension_v";
2912 "i" := "i" + 1;
2913 END LOOP;
2914 -- Find best paths:
2915 "i" := 1;
2916 LOOP
2917 "j" := 1;
2918 LOOP
2919 IF "i" != "j" THEN
2920 "k" := 1;
2921 LOOP
2922 IF "i" != "k" AND "j" != "k" THEN
2923 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
2924 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
2925 "matrix"["j"]["k"] := "matrix"["j"]["i"];
2926 END IF;
2927 ELSE
2928 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
2929 "matrix"["j"]["k"] := "matrix"["i"]["k"];
2930 END IF;
2931 END IF;
2932 END IF;
2933 EXIT WHEN "k" = "dimension_v";
2934 "k" := "k" + 1;
2935 END LOOP;
2936 END IF;
2937 EXIT WHEN "j" = "dimension_v";
2938 "j" := "j" + 1;
2939 END LOOP;
2940 EXIT WHEN "i" = "dimension_v";
2941 "i" := "i" + 1;
2942 END LOOP;
2943 -- Determine order of winners:
2944 "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2945 "rank_v" := 1;
2946 "done_v" := 0;
2947 LOOP
2948 "winners_ary" := '{}';
2949 "i" := 1;
2950 LOOP
2951 IF "rank_ary"["i"] ISNULL THEN
2952 "j" := 1;
2953 LOOP
2954 IF
2955 "i" != "j" AND
2956 "rank_ary"["j"] ISNULL AND
2957 "matrix"["j"]["i"] > "matrix"["i"]["j"]
2958 THEN
2959 -- someone else is better
2960 EXIT;
2961 END IF;
2962 IF "j" = "dimension_v" THEN
2963 -- noone is better
2964 "winners_ary" := "winners_ary" || "i";
2965 EXIT;
2966 END IF;
2967 "j" := "j" + 1;
2968 END LOOP;
2969 END IF;
2970 EXIT WHEN "i" = "dimension_v";
2971 "i" := "i" + 1;
2972 END LOOP;
2973 "i" := 1;
2974 LOOP
2975 "rank_ary"["winners_ary"["i"]] := "rank_v";
2976 "done_v" := "done_v" + 1;
2977 EXIT WHEN "i" = array_upper("winners_ary", 1);
2978 "i" := "i" + 1;
2979 END LOOP;
2980 EXIT WHEN "done_v" = "dimension_v";
2981 "rank_v" := "rank_v" + 1;
2982 END LOOP;
2983 -- write preliminary ranks:
2984 "i" := 1;
2985 FOR "initiative_id_v" IN
2986 SELECT "id" FROM "initiative"
2987 WHERE "issue_id" = "issue_id_p" AND "agreed"
2988 ORDER BY "id"
2989 LOOP
2990 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
2991 WHERE "id" = "initiative_id_v";
2992 "i" := "i" + 1;
2993 END LOOP;
2994 IF "i" != "dimension_v" + 1 THEN
2995 RAISE EXCEPTION 'Wrong winner count (should not happen)';
2996 END IF;
2997 -- straighten ranks (start counting with 1, no equal ranks):
2998 "rank_v" := 1;
2999 FOR "initiative_id_v" IN
3000 SELECT "id" FROM "initiative"
3001 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
3002 ORDER BY
3003 "rank",
3004 "vote_ratio"("positive_votes", "negative_votes") DESC,
3005 "id"
3006 LOOP
3007 UPDATE "initiative" SET "rank" = "rank_v"
3008 WHERE "id" = "initiative_id_v";
3009 "rank_v" := "rank_v" + 1;
3010 END LOOP;
3011 END IF;
3012 -- mark issue as finished
3013 UPDATE "issue" SET "ranks_available" = TRUE
3014 WHERE "id" = "issue_id_p";
3015 RETURN;
3016 END;
3017 $$;
3019 COMMENT ON FUNCTION "calculate_ranks"
3020 ( "issue"."id"%TYPE )
3021 IS 'Determine ranking (Votes have to be counted first)';
3025 -----------------------------
3026 -- Automatic state changes --
3027 -----------------------------
3030 CREATE FUNCTION "check_issue"
3031 ( "issue_id_p" "issue"."id"%TYPE )
3032 RETURNS VOID
3033 LANGUAGE 'plpgsql' VOLATILE AS $$
3034 DECLARE
3035 "issue_row" "issue"%ROWTYPE;
3036 "policy_row" "policy"%ROWTYPE;
3037 "voting_requested_v" BOOLEAN;
3038 BEGIN
3039 PERFORM "global_lock"();
3040 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3041 -- only process open issues:
3042 IF "issue_row"."closed" ISNULL THEN
3043 SELECT * INTO "policy_row" FROM "policy"
3044 WHERE "id" = "issue_row"."policy_id";
3045 -- create a snapshot, unless issue is already fully frozen:
3046 IF "issue_row"."fully_frozen" ISNULL THEN
3047 PERFORM "create_snapshot"("issue_id_p");
3048 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3049 END IF;
3050 -- eventually close or accept issues, which have not been accepted:
3051 IF "issue_row"."accepted" ISNULL THEN
3052 IF EXISTS (
3053 SELECT NULL FROM "initiative"
3054 WHERE "issue_id" = "issue_id_p"
3055 AND "supporter_count" > 0
3056 AND "supporter_count" * "policy_row"."issue_quorum_den"
3057 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
3058 ) THEN
3059 -- accept issues, if supporter count is high enough
3060 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3061 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later
3062 UPDATE "issue" SET "accepted" = "issue_row"."accepted"
3063 WHERE "id" = "issue_row"."id";
3064 ELSIF
3065 now() >= "issue_row"."created" + "issue_row"."admission_time"
3066 THEN
3067 -- close issues, if admission time has expired
3068 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3069 UPDATE "issue" SET "closed" = now()
3070 WHERE "id" = "issue_row"."id";
3071 END IF;
3072 END IF;
3073 -- eventually half freeze issues:
3074 IF
3075 -- NOTE: issue can't be closed at this point, if it has been accepted
3076 "issue_row"."accepted" NOTNULL AND
3077 "issue_row"."half_frozen" ISNULL
3078 THEN
3079 SELECT
3080 CASE
3081 WHEN "vote_now" * 2 > "issue_row"."population" THEN
3082 TRUE
3083 WHEN "vote_later" * 2 > "issue_row"."population" THEN
3084 FALSE
3085 ELSE NULL
3086 END
3087 INTO "voting_requested_v"
3088 FROM "issue" WHERE "id" = "issue_id_p";
3089 IF
3090 "voting_requested_v" OR (
3091 "voting_requested_v" ISNULL AND
3092 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
3093 )
3094 THEN
3095 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
3096 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later
3097 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
3098 WHERE "id" = "issue_row"."id";
3099 END IF;
3100 END IF;
3101 -- close issues after some time, if all initiatives have been revoked:
3102 IF
3103 "issue_row"."closed" ISNULL AND
3104 NOT EXISTS (
3105 -- all initiatives are revoked
3106 SELECT NULL FROM "initiative"
3107 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3108 ) AND (
3109 NOT EXISTS (
3110 -- and no initiatives have been revoked lately
3111 SELECT NULL FROM "initiative"
3112 WHERE "issue_id" = "issue_id_p"
3113 AND now() < "revoked" + "issue_row"."verification_time"
3114 ) OR (
3115 -- or verification time has elapsed
3116 "issue_row"."half_frozen" NOTNULL AND
3117 "issue_row"."fully_frozen" ISNULL AND
3118 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3119 )
3120 )
3121 THEN
3122 "issue_row"."closed" = now(); -- NOTE: "issue_row" used later
3123 UPDATE "issue" SET "closed" = "issue_row"."closed"
3124 WHERE "id" = "issue_row"."id";
3125 END IF;
3126 -- fully freeze issue after verification time:
3127 IF
3128 "issue_row"."half_frozen" NOTNULL AND
3129 "issue_row"."fully_frozen" ISNULL AND
3130 "issue_row"."closed" ISNULL AND
3131 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3132 THEN
3133 PERFORM "freeze_after_snapshot"("issue_id_p");
3134 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
3135 END IF;
3136 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3137 -- close issue by calling close_voting(...) after voting time:
3138 IF
3139 "issue_row"."closed" ISNULL AND
3140 "issue_row"."fully_frozen" NOTNULL AND
3141 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
3142 THEN
3143 PERFORM "close_voting"("issue_id_p");
3144 END IF;
3145 END IF;
3146 RETURN;
3147 END;
3148 $$;
3150 COMMENT ON FUNCTION "check_issue"
3151 ( "issue"."id"%TYPE )
3152 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.';
3155 CREATE FUNCTION "check_everything"()
3156 RETURNS VOID
3157 LANGUAGE 'plpgsql' VOLATILE AS $$
3158 DECLARE
3159 "issue_id_v" "issue"."id"%TYPE;
3160 BEGIN
3161 DELETE FROM "expired_session";
3162 PERFORM "calculate_member_counts"();
3163 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
3164 PERFORM "check_issue"("issue_id_v");
3165 END LOOP;
3166 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
3167 PERFORM "calculate_ranks"("issue_id_v");
3168 END LOOP;
3169 RETURN;
3170 END;
3171 $$;
3173 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.';
3177 ------------------------------
3178 -- Deletion of private data --
3179 ------------------------------
3182 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
3183 RETURNS VOID
3184 LANGUAGE 'plpgsql' VOLATILE AS $$
3185 BEGIN
3186 UPDATE "member" SET
3187 "login" = NULL,
3188 "password" = NULL,
3189 "active" = FALSE,
3190 "notify_email" = NULL,
3191 "notify_email_unconfirmed" = NULL,
3192 "notify_email_secret" = NULL,
3193 "notify_email_secret_expiry" = NULL,
3194 "password_reset_secret" = NULL,
3195 "password_reset_secret_expiry" = NULL,
3196 "organizational_unit" = NULL,
3197 "internal_posts" = NULL,
3198 "realname" = NULL,
3199 "birthday" = NULL,
3200 "address" = NULL,
3201 "email" = NULL,
3202 "xmpp_address" = NULL,
3203 "website" = NULL,
3204 "phone" = NULL,
3205 "mobile_phone" = NULL,
3206 "profession" = NULL,
3207 "external_memberships" = NULL,
3208 "external_posts" = NULL,
3209 "statement" = NULL
3210 WHERE "id" = "member_id_p";
3211 -- "text_search_data" is updated by triggers
3212 UPDATE "member_history" SET "login" = NULL
3213 WHERE "member_id" = "member_id_p";
3214 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
3215 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
3216 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
3217 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
3218 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
3219 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
3220 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
3221 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
3222 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
3223 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
3224 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
3225 RETURN;
3226 END;
3227 $$;
3229 COMMENT ON FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) IS 'Clear certain settings and data of a particular member (data protection)';
3232 CREATE FUNCTION "delete_private_data"()
3233 RETURNS VOID
3234 LANGUAGE 'plpgsql' VOLATILE AS $$
3235 BEGIN
3236 UPDATE "member" SET
3237 "login" = NULL,
3238 "password" = NULL,
3239 "notify_email" = NULL,
3240 "notify_email_unconfirmed" = NULL,
3241 "notify_email_secret" = NULL,
3242 "notify_email_secret_expiry" = NULL,
3243 "password_reset_secret" = NULL,
3244 "password_reset_secret_expiry" = NULL,
3245 "organizational_unit" = NULL,
3246 "internal_posts" = NULL,
3247 "realname" = NULL,
3248 "birthday" = NULL,
3249 "address" = NULL,
3250 "email" = NULL,
3251 "xmpp_address" = NULL,
3252 "website" = NULL,
3253 "phone" = NULL,
3254 "mobile_phone" = NULL,
3255 "profession" = NULL,
3256 "external_memberships" = NULL,
3257 "external_posts" = NULL,
3258 "statement" = NULL;
3259 -- "text_search_data" is updated by triggers
3260 UPDATE "member_history" SET "login" = NULL;
3261 DELETE FROM "invite_code";
3262 DELETE FROM "setting";
3263 DELETE FROM "setting_map";
3264 DELETE FROM "member_relation_setting";
3265 DELETE FROM "member_image";
3266 DELETE FROM "contact";
3267 DELETE FROM "session";
3268 DELETE FROM "area_setting";
3269 DELETE FROM "issue_setting";
3270 DELETE FROM "initiative_setting";
3271 DELETE FROM "suggestion_setting";
3272 DELETE FROM "direct_voter" USING "issue"
3273 WHERE "direct_voter"."issue_id" = "issue"."id"
3274 AND "issue"."closed" ISNULL;
3275 RETURN;
3276 END;
3277 $$;
3279 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.';
3283 COMMIT;