liquid_feedback_core
view core.sql @ 147:37a264fb5eef
Merged bugfix in function "close_voting": Create autoreject ballots only for members with "voting_right" in the unit
| author | jbe |
|---|---|
| date | Thu Jun 02 01:25:08 2011 +0200 (2011-06-02) |
| parents | 0fa3c4739ca7 259dd3e127c0 |
| children | ec1fdf2fc8c9 |
line source
2 -- Execute the following command manually for PostgreSQL prior version 9.0:
3 -- CREATE LANGUAGE plpgsql;
5 -- NOTE: In PostgreSQL every UNIQUE constraint implies creation of an index
7 BEGIN;
9 CREATE VIEW "liquid_feedback_version" AS
10 SELECT * FROM (VALUES ('1.4.0_rc1', 1, 4, -1))
11 AS "subquery"("string", "major", "minor", "revision");
15 ----------------------
16 -- Full text search --
17 ----------------------
20 CREATE FUNCTION "text_search_query"("query_text_p" TEXT)
21 RETURNS TSQUERY
22 LANGUAGE 'plpgsql' IMMUTABLE AS $$
23 BEGIN
24 RETURN plainto_tsquery('pg_catalog.simple', "query_text_p");
25 END;
26 $$;
28 COMMENT ON FUNCTION "text_search_query"(TEXT) IS 'Usage: WHERE "text_search_data" @@ "text_search_query"(''<user query>'')';
31 CREATE FUNCTION "highlight"
32 ( "body_p" TEXT,
33 "query_text_p" TEXT )
34 RETURNS TEXT
35 LANGUAGE 'plpgsql' IMMUTABLE AS $$
36 BEGIN
37 RETURN ts_headline(
38 'pg_catalog.simple',
39 replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'),
40 "text_search_query"("query_text_p"),
41 'StartSel=* StopSel=* HighlightAll=TRUE' );
42 END;
43 $$;
45 COMMENT ON FUNCTION "highlight"
46 ( "body_p" TEXT,
47 "query_text_p" TEXT )
48 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.';
52 -------------------------
53 -- Tables and indicies --
54 -------------------------
57 CREATE TABLE "system_setting" (
58 "member_ttl" INTERVAL );
59 CREATE UNIQUE INDEX "system_setting_singleton_idx" ON "system_setting" ((1));
61 COMMENT ON TABLE "system_setting" IS 'This table contains only one row with different settings in each column.';
62 COMMENT ON INDEX "system_setting_singleton_idx" IS 'This index ensures that "system_setting" only contains one row maximum.';
64 COMMENT ON COLUMN "system_setting"."member_ttl" IS 'Time after members get their "active" flag set to FALSE, if they do not login anymore.';
67 CREATE TABLE "contingent" (
68 "time_frame" INTERVAL PRIMARY KEY,
69 "text_entry_limit" INT4,
70 "initiative_limit" INT4 );
72 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.';
74 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';
75 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
78 CREATE TYPE "notify_level" AS ENUM
79 ('none', 'voting', 'verification', 'discussion', 'all');
81 COMMENT ON TYPE "notify_level" IS 'Level of notification: ''none'' = no notifications, ''voting'' = notifications about finished issues and issues in voting, ''verification'' = notifications about finished issues, issues in voting and verification phase, ''discussion'' = notifications about everything except issues in admission phase, ''all'' = notifications about everything';
84 CREATE TABLE "member" (
85 "id" SERIAL4 PRIMARY KEY,
86 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
87 "last_login" TIMESTAMPTZ,
88 "last_login_public" DATE,
89 "login" TEXT UNIQUE,
90 "password" TEXT,
91 "locked" BOOLEAN NOT NULL DEFAULT FALSE,
92 "active" BOOLEAN NOT NULL DEFAULT TRUE,
93 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
94 "notify_email" TEXT,
95 "notify_email_unconfirmed" TEXT,
96 "notify_email_secret" TEXT UNIQUE,
97 "notify_email_secret_expiry" TIMESTAMPTZ,
98 "notify_email_lock_expiry" TIMESTAMPTZ,
99 "notify_level" "notify_level" NOT NULL DEFAULT 'none',
100 "notify_event_id" INT8,
101 "password_reset_secret" TEXT UNIQUE,
102 "password_reset_secret_expiry" TIMESTAMPTZ,
103 "name" TEXT NOT NULL UNIQUE,
104 "identification" TEXT UNIQUE,
105 "organizational_unit" TEXT,
106 "internal_posts" TEXT,
107 "realname" TEXT,
108 "birthday" DATE,
109 "address" TEXT,
110 "email" TEXT,
111 "xmpp_address" TEXT,
112 "website" TEXT,
113 "phone" TEXT,
114 "mobile_phone" TEXT,
115 "profession" TEXT,
116 "external_memberships" TEXT,
117 "external_posts" TEXT,
118 "statement" TEXT,
119 "text_search_data" TSVECTOR );
120 CREATE INDEX "member_active_idx" ON "member" ("active");
121 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
122 CREATE TRIGGER "update_text_search_data"
123 BEFORE INSERT OR UPDATE ON "member"
124 FOR EACH ROW EXECUTE PROCEDURE
125 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
126 "name", "identification", "organizational_unit", "internal_posts",
127 "realname", "external_memberships", "external_posts", "statement" );
129 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
131 COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login';
132 COMMENT ON COLUMN "member"."last_login_public" IS 'Date of last login (time stripped for privacy reasons, updated only after day change)';
133 COMMENT ON COLUMN "member"."login" IS 'Login name';
134 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
135 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
136 COMMENT ON COLUMN "member"."active" IS 'Memberships, support and votes are taken into account when corresponding members are marked as active. When the user does not log in for an extended period of time, this flag may be set to FALSE. If the user is not locked, he/she may reset the active flag by logging in.';
137 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
138 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
139 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
140 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
141 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
142 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
143 COMMENT ON COLUMN "member"."notify_level" IS 'Selects which event notifications are to be sent to the "notify_email" mail address';
144 COMMENT ON COLUMN "member"."notify_event_id" IS 'Latest "id" of an "event" the member was notified about';
145 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member';
146 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
147 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
148 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
149 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
150 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
151 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
152 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
153 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his homepage within the system';
156 CREATE TABLE "member_history" (
157 "id" SERIAL8 PRIMARY KEY,
158 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
159 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
160 "active" BOOLEAN NOT NULL,
161 "name" TEXT NOT NULL );
162 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
164 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
166 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
167 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
170 CREATE TABLE "invite_code" (
171 "id" SERIAL8 PRIMARY KEY,
172 "code" TEXT NOT NULL UNIQUE,
173 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
174 "used" TIMESTAMPTZ,
175 "member_id" INT4 UNIQUE REFERENCES "member" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
176 "comment" TEXT,
177 CONSTRAINT "only_used_codes_may_refer_to_member" CHECK ("used" NOTNULL OR "member_id" ISNULL) );
179 COMMENT ON TABLE "invite_code" IS 'Invite codes can be used once to create a new member account.';
181 COMMENT ON COLUMN "invite_code"."code" IS 'Secret code';
182 COMMENT ON COLUMN "invite_code"."created" IS 'Time of creation of the secret code';
183 COMMENT ON COLUMN "invite_code"."used" IS 'NULL, if not used yet, otherwise tells when this code was used to create a member account';
184 COMMENT ON COLUMN "invite_code"."member_id" IS 'References the member whose account was created with this code';
185 COMMENT ON COLUMN "invite_code"."comment" IS 'Comment on the code, which is to be used for administrative reasons only';
188 CREATE TABLE "setting" (
189 PRIMARY KEY ("member_id", "key"),
190 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
191 "key" TEXT NOT NULL,
192 "value" TEXT NOT NULL );
193 CREATE INDEX "setting_key_idx" ON "setting" ("key");
195 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
197 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
200 CREATE TABLE "setting_map" (
201 PRIMARY KEY ("member_id", "key", "subkey"),
202 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
203 "key" TEXT NOT NULL,
204 "subkey" TEXT NOT NULL,
205 "value" TEXT NOT NULL );
206 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
208 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
210 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
211 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
212 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
215 CREATE TABLE "member_relation_setting" (
216 PRIMARY KEY ("member_id", "key", "other_member_id"),
217 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
218 "key" TEXT NOT NULL,
219 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
220 "value" TEXT NOT NULL );
222 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
225 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
227 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
230 CREATE TABLE "member_image" (
231 PRIMARY KEY ("member_id", "image_type", "scaled"),
232 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
233 "image_type" "member_image_type",
234 "scaled" BOOLEAN,
235 "content_type" TEXT,
236 "data" BYTEA NOT NULL );
238 COMMENT ON TABLE "member_image" IS 'Images of members';
240 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
243 CREATE TABLE "member_count" (
244 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
245 "total_count" INT4 NOT NULL );
247 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';
249 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
250 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
253 CREATE TABLE "contact" (
254 PRIMARY KEY ("member_id", "other_member_id"),
255 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
256 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
257 "public" BOOLEAN NOT NULL DEFAULT FALSE,
258 CONSTRAINT "cant_save_yourself_as_contact"
259 CHECK ("member_id" != "other_member_id") );
260 CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
262 COMMENT ON TABLE "contact" IS 'Contact lists';
264 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
265 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
266 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
269 CREATE TABLE "ignored_member" (
270 PRIMARY KEY ("member_id", "other_member_id"),
271 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
272 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
273 CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
275 COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
277 COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone';
278 COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
281 CREATE TABLE "session" (
282 "ident" TEXT PRIMARY KEY,
283 "additional_secret" TEXT,
284 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
285 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
286 "lang" TEXT );
287 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
289 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';
291 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
292 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
293 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
294 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
297 CREATE TABLE "policy" (
298 "id" SERIAL4 PRIMARY KEY,
299 "index" INT4 NOT NULL,
300 "active" BOOLEAN NOT NULL DEFAULT TRUE,
301 "name" TEXT NOT NULL UNIQUE,
302 "description" TEXT NOT NULL DEFAULT '',
303 "admission_time" INTERVAL NOT NULL,
304 "discussion_time" INTERVAL NOT NULL,
305 "verification_time" INTERVAL NOT NULL,
306 "voting_time" INTERVAL NOT NULL,
307 "issue_quorum_num" INT4 NOT NULL,
308 "issue_quorum_den" INT4 NOT NULL,
309 "initiative_quorum_num" INT4 NOT NULL,
310 "initiative_quorum_den" INT4 NOT NULL,
311 "majority_num" INT4 NOT NULL DEFAULT 1,
312 "majority_den" INT4 NOT NULL DEFAULT 2,
313 "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
314 "majority_positive" INT4 NOT NULL DEFAULT 0,
315 "majority_non_negative" INT4 NOT NULL DEFAULT 0 );
316 CREATE INDEX "policy_active_idx" ON "policy" ("active");
318 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
320 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
321 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
322 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"';
323 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"';
324 COMMENT ON COLUMN "policy"."verification_time" IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
325 COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "fully_frozen" but not "closed"';
326 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"';
327 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"';
328 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
329 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
330 COMMENT ON COLUMN "policy"."majority_num" IS 'Numerator of fraction of majority to be reached during voting by an initiative to be "attainable"';
331 COMMENT ON COLUMN "policy"."majority_den" IS 'Denominator of fraction of majority to be reached during voting by an initiative to be "attainable"';
332 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.';
333 COMMENT ON COLUMN "policy"."majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be "attainable".';
334 COMMENT ON COLUMN "policy"."majority_non_negative" IS 'Absolute number of sum of "positive_votes" and abstentions neccessary for an initiative to be "attainable".';
337 CREATE TABLE "unit" (
338 "id" SERIAL4 PRIMARY KEY,
339 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
340 "active" BOOLEAN NOT NULL DEFAULT TRUE,
341 "name" TEXT NOT NULL,
342 "description" TEXT NOT NULL DEFAULT '',
343 "member_count" INT4,
344 "text_search_data" TSVECTOR );
345 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
346 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
347 CREATE INDEX "unit_active_idx" ON "unit" ("active");
348 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
349 CREATE TRIGGER "update_text_search_data"
350 BEFORE INSERT OR UPDATE ON "unit"
351 FOR EACH ROW EXECUTE PROCEDURE
352 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
353 "name", "description" );
355 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
357 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
358 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in units of this area';
359 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
362 CREATE TABLE "area" (
363 "id" SERIAL4 PRIMARY KEY,
364 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
365 "active" BOOLEAN NOT NULL DEFAULT TRUE,
366 "name" TEXT NOT NULL,
367 "description" TEXT NOT NULL DEFAULT '',
368 "direct_member_count" INT4,
369 "member_weight" INT4,
370 "autoreject_weight" INT4,
371 "text_search_data" TSVECTOR );
372 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
373 CREATE INDEX "area_active_idx" ON "area" ("active");
374 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
375 CREATE TRIGGER "update_text_search_data"
376 BEFORE INSERT OR UPDATE ON "area"
377 FOR EACH ROW EXECUTE PROCEDURE
378 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
379 "name", "description" );
381 COMMENT ON TABLE "area" IS 'Subject areas';
383 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
384 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"';
385 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
386 COMMENT ON COLUMN "area"."autoreject_weight" IS 'Sum of weight of members using the autoreject feature';
389 CREATE TABLE "area_setting" (
390 PRIMARY KEY ("member_id", "key", "area_id"),
391 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
392 "key" TEXT NOT NULL,
393 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
394 "value" TEXT NOT NULL );
396 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
399 CREATE TABLE "allowed_policy" (
400 PRIMARY KEY ("area_id", "policy_id"),
401 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
402 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
403 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
404 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
406 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
408 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
411 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
413 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';
416 CREATE TYPE "issue_state" AS ENUM (
417 'admission', 'discussion', 'verification', 'voting',
418 'canceled_revoked_before_accepted',
419 'canceled_issue_not_accepted',
420 'canceled_after_revocation_during_discussion',
421 'canceled_after_revocation_during_verification',
422 'calculation',
423 'canceled_no_initiative_admitted',
424 'finished_without_winner', 'finished_with_winner');
426 COMMENT ON TYPE "issue_state" IS 'State of issues';
429 CREATE TABLE "issue" (
430 "id" SERIAL4 PRIMARY KEY,
431 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
432 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
433 "state" "issue_state" NOT NULL DEFAULT 'admission',
434 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
435 "accepted" TIMESTAMPTZ,
436 "half_frozen" TIMESTAMPTZ,
437 "fully_frozen" TIMESTAMPTZ,
438 "closed" TIMESTAMPTZ,
439 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
440 "cleaned" TIMESTAMPTZ,
441 "admission_time" INTERVAL NOT NULL,
442 "discussion_time" INTERVAL NOT NULL,
443 "verification_time" INTERVAL NOT NULL,
444 "voting_time" INTERVAL NOT NULL,
445 "snapshot" TIMESTAMPTZ,
446 "latest_snapshot_event" "snapshot_event",
447 "population" INT4,
448 "voter_count" INT4,
449 CONSTRAINT "valid_state" CHECK ((
450 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
451 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
452 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
453 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
454 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
455 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
456 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
457 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
458 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)) AND (
459 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
460 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
461 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
462 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
463 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
464 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
465 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
466 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
467 ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
468 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
469 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
470 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
471 )),
472 CONSTRAINT "state_change_order" CHECK (
473 "created" <= "accepted" AND
474 "accepted" <= "half_frozen" AND
475 "half_frozen" <= "fully_frozen" AND
476 "fully_frozen" <= "closed" ),
477 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
478 "cleaned" ISNULL OR "closed" NOTNULL ),
479 CONSTRAINT "last_snapshot_on_full_freeze"
480 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
481 CONSTRAINT "freeze_requires_snapshot"
482 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
483 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
484 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
485 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
486 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
487 CREATE INDEX "issue_created_idx" ON "issue" ("created");
488 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
489 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
490 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
491 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
492 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
493 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
495 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
497 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
498 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.';
499 COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed and voting has started; 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.';
500 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.';
501 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
502 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
503 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
504 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
505 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
506 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
507 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
508 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';
509 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
510 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';
513 CREATE TABLE "issue_setting" (
514 PRIMARY KEY ("member_id", "key", "issue_id"),
515 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
516 "key" TEXT NOT NULL,
517 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
518 "value" TEXT NOT NULL );
520 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
523 CREATE TABLE "initiative" (
524 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
525 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
526 "id" SERIAL4 PRIMARY KEY,
527 "name" TEXT NOT NULL,
528 "discussion_url" TEXT,
529 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
530 "revoked" TIMESTAMPTZ,
531 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
532 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
533 "admitted" BOOLEAN,
534 "supporter_count" INT4,
535 "informed_supporter_count" INT4,
536 "satisfied_supporter_count" INT4,
537 "satisfied_informed_supporter_count" INT4,
538 "positive_votes" INT4,
539 "negative_votes" INT4,
540 "attainable" BOOLEAN,
541 "favored" BOOLEAN,
542 "unfavored" BOOLEAN,
543 "preliminary_rank" INT4,
544 "final_rank" INT4,
545 "disqualified" BOOLEAN,
546 "winner" BOOLEAN,
547 "text_search_data" TSVECTOR,
548 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
549 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL),
550 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
551 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
552 CONSTRAINT "revoked_initiatives_cant_be_admitted"
553 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
554 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
555 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
556 ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND
557 "attainable" ISNULL AND "favored" ISNULL AND "unfavored" ISNULL AND
558 "disqualified" ISNULL AND "preliminary_rank" ISNULL AND
559 "final_rank" ISNULL AND "winner" ISNULL ) ),
560 CONSTRAINT "favored_excludes_unfavored" CHECK (NOT ("favored" AND "unfavored")) );
561 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
562 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
563 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
564 CREATE TRIGGER "update_text_search_data"
565 BEFORE INSERT OR UPDATE ON "initiative"
566 FOR EACH ROW EXECUTE PROCEDURE
567 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
568 "name", "discussion_url");
570 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.';
572 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
573 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
574 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoked the initiative';
575 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
576 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
577 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
578 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
579 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
580 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
581 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
582 COMMENT ON COLUMN "initiative"."attainable" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "majority_num"/"majority_den", and "positive_votes" is greater-equal than "majority_positive", and ("positive_votes"+abstentions) is greater-equal than "majority_non_negative"';
583 COMMENT ON COLUMN "initiative"."favored" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)';
584 COMMENT ON COLUMN "initiative"."unfavored" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)';
585 COMMENT ON COLUMN "initiative"."preliminary_rank" IS 'Schulze-Ranking without tie-breaking';
586 COMMENT ON COLUMN "initiative"."final_rank" IS 'Schulze-Ranking after tie-breaking';
587 COMMENT ON COLUMN "initiative"."disqualified" IS 'TRUE, if initiative may not win, because it either (a) has no better rank than the status quo, or (b) because there exists a better ranked initiative X, which directly beats this initiative, and either more voters prefer X to this initiative than voters preferring X to the status quo or less voters prefer this initiative to X than voters preferring the status quo to X';
588 COMMENT ON COLUMN "initiative"."winner" IS 'TRUE, if initiative is final winner (best ranked initiative being "attainable" and not "disqualified")';
591 CREATE TABLE "battle" (
592 "issue_id" INT4 NOT NULL,
593 "winning_initiative_id" INT4,
594 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
595 "losing_initiative_id" INT4,
596 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
597 "count" INT4 NOT NULL,
598 CONSTRAINT "initiative_ids_not_equal" CHECK (
599 "winning_initiative_id" != "losing_initiative_id" OR
600 ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
601 ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) );
602 CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
603 CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
604 CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
606 COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue; NULL as initiative_id denotes virtual "status-quo" initiative';
609 CREATE TABLE "ignored_initiative" (
610 PRIMARY KEY ("initiative_id", "member_id"),
611 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
612 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
613 CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");
615 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
618 CREATE TABLE "initiative_setting" (
619 PRIMARY KEY ("member_id", "key", "initiative_id"),
620 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
621 "key" TEXT NOT NULL,
622 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
623 "value" TEXT NOT NULL );
625 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
628 CREATE TABLE "draft" (
629 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
630 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
631 "id" SERIAL8 PRIMARY KEY,
632 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
633 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
634 "formatting_engine" TEXT,
635 "content" TEXT NOT NULL,
636 "text_search_data" TSVECTOR );
637 CREATE INDEX "draft_created_idx" ON "draft" ("created");
638 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
639 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
640 CREATE TRIGGER "update_text_search_data"
641 BEFORE INSERT OR UPDATE ON "draft"
642 FOR EACH ROW EXECUTE PROCEDURE
643 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
645 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.';
647 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
648 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
651 CREATE TABLE "rendered_draft" (
652 PRIMARY KEY ("draft_id", "format"),
653 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
654 "format" TEXT,
655 "content" TEXT NOT NULL );
657 COMMENT ON TABLE "rendered_draft" IS 'This table may be used by frontends to cache "rendered" drafts (e.g. HTML output generated from wiki text)';
660 CREATE TABLE "suggestion" (
661 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
662 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
663 "id" SERIAL8 PRIMARY KEY,
664 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
665 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
666 "name" TEXT NOT NULL,
667 "description" TEXT NOT NULL DEFAULT '',
668 "text_search_data" TSVECTOR,
669 "minus2_unfulfilled_count" INT4,
670 "minus2_fulfilled_count" INT4,
671 "minus1_unfulfilled_count" INT4,
672 "minus1_fulfilled_count" INT4,
673 "plus1_unfulfilled_count" INT4,
674 "plus1_fulfilled_count" INT4,
675 "plus2_unfulfilled_count" INT4,
676 "plus2_fulfilled_count" INT4 );
677 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
678 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
679 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
680 CREATE TRIGGER "update_text_search_data"
681 BEFORE INSERT OR UPDATE ON "suggestion"
682 FOR EACH ROW EXECUTE PROCEDURE
683 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
684 "name", "description");
686 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';
688 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
689 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
690 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
691 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
692 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
693 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
694 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
695 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
698 CREATE TABLE "suggestion_setting" (
699 PRIMARY KEY ("member_id", "key", "suggestion_id"),
700 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
701 "key" TEXT NOT NULL,
702 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
703 "value" TEXT NOT NULL );
705 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
708 CREATE TABLE "invite_code_unit" (
709 PRIMARY KEY ("invite_code_id", "unit_id"),
710 "invite_code_id" INT8 REFERENCES "invite_code" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
711 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
713 COMMENT ON TABLE "invite_code_unit" IS 'Units where accounts created with a given invite codes get voting rights';
716 CREATE TABLE "privilege" (
717 PRIMARY KEY ("unit_id", "member_id"),
718 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
719 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
720 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
721 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
722 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
723 "voting_right_manager" BOOLEAN NOT NULL DEFAULT FALSE,
724 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE );
726 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
728 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke admin privileges to/from other users';
729 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create or lock sub units';
730 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create or lock areas and set area parameters';
731 COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote inside the unit';
732 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to discuss and vote';
735 CREATE TABLE "membership" (
736 PRIMARY KEY ("area_id", "member_id"),
737 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
738 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
739 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
740 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
742 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
744 COMMENT ON COLUMN "membership"."autoreject" IS 'TRUE = member votes against all initiatives, if he is neither direct_ or delegating_voter; Entries in the "interest" table can override this setting.';
747 CREATE TABLE "interest" (
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 CASCADE ON UPDATE CASCADE,
751 "autoreject" BOOLEAN );
752 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
754 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.';
756 COMMENT ON COLUMN "interest"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure';
759 CREATE TABLE "initiator" (
760 PRIMARY KEY ("initiative_id", "member_id"),
761 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
762 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
763 "accepted" BOOLEAN );
764 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
766 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.';
768 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.';
771 CREATE TABLE "supporter" (
772 "issue_id" INT4 NOT NULL,
773 PRIMARY KEY ("initiative_id", "member_id"),
774 "initiative_id" INT4,
775 "member_id" INT4,
776 "draft_id" INT8 NOT NULL,
777 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
778 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
779 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
781 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.';
783 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
786 CREATE TABLE "opinion" (
787 "initiative_id" INT4 NOT NULL,
788 PRIMARY KEY ("suggestion_id", "member_id"),
789 "suggestion_id" INT8,
790 "member_id" INT4,
791 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
792 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
793 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
794 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
795 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
797 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.';
799 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
802 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
804 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
807 CREATE TABLE "delegation" (
808 "id" SERIAL8 PRIMARY KEY,
809 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
810 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
811 "scope" "delegation_scope" NOT NULL,
812 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
813 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
814 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
815 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
816 CONSTRAINT "no_unit_delegation_to_null"
817 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
818 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
819 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
820 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
821 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
822 UNIQUE ("unit_id", "truster_id"),
823 UNIQUE ("area_id", "truster_id"),
824 UNIQUE ("issue_id", "truster_id") );
825 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
826 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
828 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
830 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
831 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
832 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
835 CREATE TABLE "direct_population_snapshot" (
836 PRIMARY KEY ("issue_id", "event", "member_id"),
837 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
838 "event" "snapshot_event",
839 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
840 "weight" INT4 );
841 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
843 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
845 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
846 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
849 CREATE TABLE "delegating_population_snapshot" (
850 PRIMARY KEY ("issue_id", "event", "member_id"),
851 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
852 "event" "snapshot_event",
853 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
854 "weight" INT4,
855 "scope" "delegation_scope" NOT NULL,
856 "delegate_member_ids" INT4[] NOT NULL );
857 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
859 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
861 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
862 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
863 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
864 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"';
867 CREATE TABLE "direct_interest_snapshot" (
868 PRIMARY KEY ("issue_id", "event", "member_id"),
869 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
870 "event" "snapshot_event",
871 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
872 "weight" INT4 );
873 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
875 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
877 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
878 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
881 CREATE TABLE "delegating_interest_snapshot" (
882 PRIMARY KEY ("issue_id", "event", "member_id"),
883 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
884 "event" "snapshot_event",
885 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
886 "weight" INT4,
887 "scope" "delegation_scope" NOT NULL,
888 "delegate_member_ids" INT4[] NOT NULL );
889 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
891 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
893 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
894 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
895 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
896 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"';
899 CREATE TABLE "direct_supporter_snapshot" (
900 "issue_id" INT4 NOT NULL,
901 PRIMARY KEY ("initiative_id", "event", "member_id"),
902 "initiative_id" INT4,
903 "event" "snapshot_event",
904 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
905 "informed" BOOLEAN NOT NULL,
906 "satisfied" BOOLEAN NOT NULL,
907 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
908 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
909 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
911 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
913 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
914 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
915 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
918 CREATE TABLE "non_voter" (
919 PRIMARY KEY ("issue_id", "member_id"),
920 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
921 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
922 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
924 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
927 CREATE TABLE "direct_voter" (
928 PRIMARY KEY ("issue_id", "member_id"),
929 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
930 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
931 "weight" INT4,
932 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
933 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
935 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.';
937 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
938 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
941 CREATE TABLE "delegating_voter" (
942 PRIMARY KEY ("issue_id", "member_id"),
943 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
944 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
945 "weight" INT4,
946 "scope" "delegation_scope" NOT NULL,
947 "delegate_member_ids" INT4[] NOT NULL );
948 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
950 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
952 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
953 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
954 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"';
957 CREATE TABLE "vote" (
958 "issue_id" INT4 NOT NULL,
959 PRIMARY KEY ("initiative_id", "member_id"),
960 "initiative_id" INT4,
961 "member_id" INT4,
962 "grade" INT4,
963 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
964 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
965 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
967 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.';
969 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.';
972 CREATE TABLE "issue_comment" (
973 PRIMARY KEY ("issue_id", "member_id"),
974 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
975 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
976 "changed" TIMESTAMPTZ NOT NULL DEFAULT now(),
977 "formatting_engine" TEXT,
978 "content" TEXT NOT NULL,
979 "text_search_data" TSVECTOR );
980 CREATE INDEX "issue_comment_member_id_idx" ON "issue_comment" ("member_id");
981 CREATE INDEX "issue_comment_text_search_data_idx" ON "issue_comment" USING gin ("text_search_data");
982 CREATE TRIGGER "update_text_search_data"
983 BEFORE INSERT OR UPDATE ON "issue_comment"
984 FOR EACH ROW EXECUTE PROCEDURE
985 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
987 COMMENT ON TABLE "issue_comment" IS 'Place to store free comments of members related to issues';
989 COMMENT ON COLUMN "issue_comment"."changed" IS 'Time the comment was last changed';
992 CREATE TABLE "rendered_issue_comment" (
993 PRIMARY KEY ("issue_id", "member_id", "format"),
994 FOREIGN KEY ("issue_id", "member_id")
995 REFERENCES "issue_comment" ("issue_id", "member_id")
996 ON DELETE CASCADE ON UPDATE CASCADE,
997 "issue_id" INT4,
998 "member_id" INT4,
999 "format" TEXT,
1000 "content" TEXT NOT NULL );
1002 COMMENT ON TABLE "rendered_issue_comment" IS 'This table may be used by frontends to cache "rendered" issue comments (e.g. HTML output generated from wiki text)';
1005 CREATE TABLE "voting_comment" (
1006 PRIMARY KEY ("issue_id", "member_id"),
1007 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1008 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1009 "changed" TIMESTAMPTZ,
1010 "formatting_engine" TEXT,
1011 "content" TEXT NOT NULL,
1012 "text_search_data" TSVECTOR );
1013 CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id");
1014 CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data");
1015 CREATE TRIGGER "update_text_search_data"
1016 BEFORE INSERT OR UPDATE ON "voting_comment"
1017 FOR EACH ROW EXECUTE PROCEDURE
1018 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
1020 COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.';
1022 COMMENT ON COLUMN "voting_comment"."changed" IS 'Is to be set or updated by the frontend, if comment was inserted or updated AFTER the issue has been closed. Otherwise it shall be set to NULL.';
1025 CREATE TABLE "rendered_voting_comment" (
1026 PRIMARY KEY ("issue_id", "member_id", "format"),
1027 FOREIGN KEY ("issue_id", "member_id")
1028 REFERENCES "voting_comment" ("issue_id", "member_id")
1029 ON DELETE CASCADE ON UPDATE CASCADE,
1030 "issue_id" INT4,
1031 "member_id" INT4,
1032 "format" TEXT,
1033 "content" TEXT NOT NULL );
1035 COMMENT ON TABLE "rendered_voting_comment" IS 'This table may be used by frontends to cache "rendered" voting comments (e.g. HTML output generated from wiki text)';
1038 CREATE TYPE "event_type" AS ENUM (
1039 'issue_state_changed',
1040 'initiative_created_in_new_issue',
1041 'initiative_created_in_existing_issue',
1042 'initiative_revoked',
1043 'new_draft_created',
1044 'suggestion_created');
1046 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
1049 CREATE TABLE "event" (
1050 "id" SERIAL8 PRIMARY KEY,
1051 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
1052 "event" "event_type" NOT NULL,
1053 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1054 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1055 "state" "issue_state" CHECK ("state" != 'calculation'),
1056 "initiative_id" INT4,
1057 "draft_id" INT8,
1058 "suggestion_id" INT8,
1059 FOREIGN KEY ("issue_id", "initiative_id")
1060 REFERENCES "initiative" ("issue_id", "id")
1061 ON DELETE CASCADE ON UPDATE CASCADE,
1062 FOREIGN KEY ("initiative_id", "draft_id")
1063 REFERENCES "draft" ("initiative_id", "id")
1064 ON DELETE CASCADE ON UPDATE CASCADE,
1065 FOREIGN KEY ("initiative_id", "suggestion_id")
1066 REFERENCES "suggestion" ("initiative_id", "id")
1067 ON DELETE CASCADE ON UPDATE CASCADE,
1068 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
1069 "event" != 'issue_state_changed' OR (
1070 "member_id" ISNULL AND
1071 "issue_id" NOTNULL AND
1072 "state" NOTNULL AND
1073 "initiative_id" ISNULL AND
1074 "draft_id" ISNULL AND
1075 "suggestion_id" ISNULL )),
1076 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
1077 "event" NOT IN (
1078 'initiative_created_in_new_issue',
1079 'initiative_created_in_existing_issue',
1080 'initiative_revoked',
1081 'new_draft_created'
1082 ) OR (
1083 "member_id" NOTNULL AND
1084 "issue_id" NOTNULL AND
1085 "state" NOTNULL AND
1086 "initiative_id" NOTNULL AND
1087 "draft_id" NOTNULL AND
1088 "suggestion_id" ISNULL )),
1089 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
1090 "event" != 'suggestion_created' OR (
1091 "member_id" NOTNULL AND
1092 "issue_id" NOTNULL AND
1093 "state" NOTNULL AND
1094 "initiative_id" NOTNULL AND
1095 "draft_id" ISNULL AND
1096 "suggestion_id" NOTNULL )) );
1098 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
1100 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
1101 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';
1102 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';
1103 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';
1107 ----------------------------------------------
1108 -- Writing of history entries and event log --
1109 ----------------------------------------------
1111 CREATE FUNCTION "write_member_history_trigger"()
1112 RETURNS TRIGGER
1113 LANGUAGE 'plpgsql' VOLATILE AS $$
1114 BEGIN
1115 IF
1116 NEW."active" != OLD."active" OR
1117 NEW."name" != OLD."name"
1118 THEN
1119 INSERT INTO "member_history"
1120 ("member_id", "active", "name")
1121 VALUES (NEW."id", OLD."active", OLD."name");
1122 END IF;
1123 RETURN NULL;
1124 END;
1125 $$;
1127 CREATE TRIGGER "write_member_history"
1128 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1129 "write_member_history_trigger"();
1131 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
1132 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
1135 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
1136 RETURNS TRIGGER
1137 LANGUAGE 'plpgsql' VOLATILE AS $$
1138 BEGIN
1139 IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN
1140 INSERT INTO "event" ("event", "issue_id", "state")
1141 VALUES ('issue_state_changed', NEW."id", NEW."state");
1142 END IF;
1143 RETURN NULL;
1144 END;
1145 $$;
1147 CREATE TRIGGER "write_event_issue_state_changed"
1148 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
1149 "write_event_issue_state_changed_trigger"();
1151 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
1152 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
1155 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1156 RETURNS TRIGGER
1157 LANGUAGE 'plpgsql' VOLATILE AS $$
1158 DECLARE
1159 "initiative_row" "initiative"%ROWTYPE;
1160 "issue_row" "issue"%ROWTYPE;
1161 "event_v" "event_type";
1162 BEGIN
1163 SELECT * INTO "initiative_row" FROM "initiative"
1164 WHERE "id" = NEW."initiative_id";
1165 SELECT * INTO "issue_row" FROM "issue"
1166 WHERE "id" = "initiative_row"."issue_id";
1167 IF EXISTS (
1168 SELECT NULL FROM "draft"
1169 WHERE "initiative_id" = NEW."initiative_id"
1170 AND "id" != NEW."id"
1171 ) THEN
1172 "event_v" := 'new_draft_created';
1173 ELSE
1174 IF EXISTS (
1175 SELECT NULL FROM "initiative"
1176 WHERE "issue_id" = "initiative_row"."issue_id"
1177 AND "id" != "initiative_row"."id"
1178 ) THEN
1179 "event_v" := 'initiative_created_in_existing_issue';
1180 ELSE
1181 "event_v" := 'initiative_created_in_new_issue';
1182 END IF;
1183 END IF;
1184 INSERT INTO "event" (
1185 "event", "member_id",
1186 "issue_id", "state", "initiative_id", "draft_id"
1187 ) VALUES (
1188 "event_v",
1189 NEW."author_id",
1190 "initiative_row"."issue_id",
1191 "issue_row"."state",
1192 "initiative_row"."id",
1193 NEW."id" );
1194 RETURN NULL;
1195 END;
1196 $$;
1198 CREATE TRIGGER "write_event_initiative_or_draft_created"
1199 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
1200 "write_event_initiative_or_draft_created_trigger"();
1202 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
1203 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
1206 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
1207 RETURNS TRIGGER
1208 LANGUAGE 'plpgsql' VOLATILE AS $$
1209 DECLARE
1210 "issue_row" "issue"%ROWTYPE;
1211 BEGIN
1212 SELECT * INTO "issue_row" FROM "issue"
1213 WHERE "id" = NEW."issue_id";
1214 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1215 INSERT INTO "event" (
1216 "event", "member_id", "issue_id", "state", "initiative_id"
1217 ) VALUES (
1218 'initiative_revoked',
1219 NEW."revoked_by_member_id",
1220 NEW."issue_id",
1221 "issue_row"."state",
1222 NEW."id" );
1223 END IF;
1224 RETURN NULL;
1225 END;
1226 $$;
1228 CREATE TRIGGER "write_event_initiative_revoked"
1229 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
1230 "write_event_initiative_revoked_trigger"();
1232 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
1233 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
1236 CREATE FUNCTION "write_event_suggestion_created_trigger"()
1237 RETURNS TRIGGER
1238 LANGUAGE 'plpgsql' VOLATILE AS $$
1239 DECLARE
1240 "initiative_row" "initiative"%ROWTYPE;
1241 "issue_row" "issue"%ROWTYPE;
1242 BEGIN
1243 SELECT * INTO "initiative_row" FROM "initiative"
1244 WHERE "id" = NEW."initiative_id";
1245 SELECT * INTO "issue_row" FROM "issue"
1246 WHERE "id" = "initiative_row"."issue_id";
1247 INSERT INTO "event" (
1248 "event", "member_id",
1249 "issue_id", "state", "initiative_id", "suggestion_id"
1250 ) VALUES (
1251 'suggestion_created',
1252 NEW."author_id",
1253 "initiative_row"."issue_id",
1254 "issue_row"."state",
1255 "initiative_row"."id",
1256 NEW."id" );
1257 RETURN NULL;
1258 END;
1259 $$;
1261 CREATE TRIGGER "write_event_suggestion_created"
1262 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1263 "write_event_suggestion_created_trigger"();
1265 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
1266 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1270 ----------------------------
1271 -- Additional constraints --
1272 ----------------------------
1275 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
1276 RETURNS TRIGGER
1277 LANGUAGE 'plpgsql' VOLATILE AS $$
1278 BEGIN
1279 IF NOT EXISTS (
1280 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
1281 ) THEN
1282 --RAISE 'Cannot create issue without an initial initiative.' USING
1283 -- ERRCODE = 'integrity_constraint_violation',
1284 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
1285 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
1286 END IF;
1287 RETURN NULL;
1288 END;
1289 $$;
1291 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
1292 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
1293 FOR EACH ROW EXECUTE PROCEDURE
1294 "issue_requires_first_initiative_trigger"();
1296 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
1297 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
1300 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
1301 RETURNS TRIGGER
1302 LANGUAGE 'plpgsql' VOLATILE AS $$
1303 DECLARE
1304 "reference_lost" BOOLEAN;
1305 BEGIN
1306 IF TG_OP = 'DELETE' THEN
1307 "reference_lost" := TRUE;
1308 ELSE
1309 "reference_lost" := NEW."issue_id" != OLD."issue_id";
1310 END IF;
1311 IF
1312 "reference_lost" AND NOT EXISTS (
1313 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
1314 )
1315 THEN
1316 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
1317 END IF;
1318 RETURN NULL;
1319 END;
1320 $$;
1322 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
1323 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1324 FOR EACH ROW EXECUTE PROCEDURE
1325 "last_initiative_deletes_issue_trigger"();
1327 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
1328 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
1331 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
1332 RETURNS TRIGGER
1333 LANGUAGE 'plpgsql' VOLATILE AS $$
1334 BEGIN
1335 IF NOT EXISTS (
1336 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
1337 ) THEN
1338 --RAISE 'Cannot create initiative without an initial draft.' USING
1339 -- ERRCODE = 'integrity_constraint_violation',
1340 -- HINT = 'Create issue, initiative and draft within the same transaction.';
1341 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
1342 END IF;
1343 RETURN NULL;
1344 END;
1345 $$;
1347 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
1348 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1349 FOR EACH ROW EXECUTE PROCEDURE
1350 "initiative_requires_first_draft_trigger"();
1352 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
1353 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
1356 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
1357 RETURNS TRIGGER
1358 LANGUAGE 'plpgsql' VOLATILE AS $$
1359 DECLARE
1360 "reference_lost" BOOLEAN;
1361 BEGIN
1362 IF TG_OP = 'DELETE' THEN
1363 "reference_lost" := TRUE;
1364 ELSE
1365 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
1366 END IF;
1367 IF
1368 "reference_lost" AND NOT EXISTS (
1369 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
1370 )
1371 THEN
1372 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
1373 END IF;
1374 RETURN NULL;
1375 END;
1376 $$;
1378 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
1379 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
1380 FOR EACH ROW EXECUTE PROCEDURE
1381 "last_draft_deletes_initiative_trigger"();
1383 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
1384 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
1387 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
1388 RETURNS TRIGGER
1389 LANGUAGE 'plpgsql' VOLATILE AS $$
1390 BEGIN
1391 IF NOT EXISTS (
1392 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
1393 ) THEN
1394 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
1395 END IF;
1396 RETURN NULL;
1397 END;
1398 $$;
1400 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
1401 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
1402 FOR EACH ROW EXECUTE PROCEDURE
1403 "suggestion_requires_first_opinion_trigger"();
1405 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1406 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1409 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1410 RETURNS TRIGGER
1411 LANGUAGE 'plpgsql' VOLATILE AS $$
1412 DECLARE
1413 "reference_lost" BOOLEAN;
1414 BEGIN
1415 IF TG_OP = 'DELETE' THEN
1416 "reference_lost" := TRUE;
1417 ELSE
1418 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1419 END IF;
1420 IF
1421 "reference_lost" AND NOT EXISTS (
1422 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1423 )
1424 THEN
1425 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1426 END IF;
1427 RETURN NULL;
1428 END;
1429 $$;
1431 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1432 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1433 FOR EACH ROW EXECUTE PROCEDURE
1434 "last_opinion_deletes_suggestion_trigger"();
1436 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1437 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1441 ---------------------------------------------------------------
1442 -- Ensure that votes are not modified when issues are frozen --
1443 ---------------------------------------------------------------
1445 -- NOTE: Frontends should ensure this anyway, but in case of programming
1446 -- errors the following triggers ensure data integrity.
1449 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1450 RETURNS TRIGGER
1451 LANGUAGE 'plpgsql' VOLATILE AS $$
1452 DECLARE
1453 "issue_id_v" "issue"."id"%TYPE;
1454 "issue_row" "issue"%ROWTYPE;
1455 BEGIN
1456 IF TG_OP = 'DELETE' THEN
1457 "issue_id_v" := OLD."issue_id";
1458 ELSE
1459 "issue_id_v" := NEW."issue_id";
1460 END IF;
1461 SELECT INTO "issue_row" * FROM "issue"
1462 WHERE "id" = "issue_id_v" FOR SHARE;
1463 IF "issue_row"."closed" NOTNULL THEN
1464 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
1465 END IF;
1466 RETURN NULL;
1467 END;
1468 $$;
1470 CREATE TRIGGER "forbid_changes_on_closed_issue"
1471 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1472 FOR EACH ROW EXECUTE PROCEDURE
1473 "forbid_changes_on_closed_issue_trigger"();
1475 CREATE TRIGGER "forbid_changes_on_closed_issue"
1476 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1477 FOR EACH ROW EXECUTE PROCEDURE
1478 "forbid_changes_on_closed_issue_trigger"();
1480 CREATE TRIGGER "forbid_changes_on_closed_issue"
1481 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1482 FOR EACH ROW EXECUTE PROCEDURE
1483 "forbid_changes_on_closed_issue_trigger"();
1485 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"';
1486 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';
1487 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';
1488 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';
1492 --------------------------------------------------------------------
1493 -- Auto-retrieval of fields only needed for referential integrity --
1494 --------------------------------------------------------------------
1497 CREATE FUNCTION "autofill_issue_id_trigger"()
1498 RETURNS TRIGGER
1499 LANGUAGE 'plpgsql' VOLATILE AS $$
1500 BEGIN
1501 IF NEW."issue_id" ISNULL THEN
1502 SELECT "issue_id" INTO NEW."issue_id"
1503 FROM "initiative" WHERE "id" = NEW."initiative_id";
1504 END IF;
1505 RETURN NEW;
1506 END;
1507 $$;
1509 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1510 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1512 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1513 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1515 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1516 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1517 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1520 CREATE FUNCTION "autofill_initiative_id_trigger"()
1521 RETURNS TRIGGER
1522 LANGUAGE 'plpgsql' VOLATILE AS $$
1523 BEGIN
1524 IF NEW."initiative_id" ISNULL THEN
1525 SELECT "initiative_id" INTO NEW."initiative_id"
1526 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1527 END IF;
1528 RETURN NEW;
1529 END;
1530 $$;
1532 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1533 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1535 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1536 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1540 -----------------------------------------------------
1541 -- Automatic calculation of certain default values --
1542 -----------------------------------------------------
1545 CREATE FUNCTION "copy_timings_trigger"()
1546 RETURNS TRIGGER
1547 LANGUAGE 'plpgsql' VOLATILE AS $$
1548 DECLARE
1549 "policy_row" "policy"%ROWTYPE;
1550 BEGIN
1551 SELECT * INTO "policy_row" FROM "policy"
1552 WHERE "id" = NEW."policy_id";
1553 IF NEW."admission_time" ISNULL THEN
1554 NEW."admission_time" := "policy_row"."admission_time";
1555 END IF;
1556 IF NEW."discussion_time" ISNULL THEN
1557 NEW."discussion_time" := "policy_row"."discussion_time";
1558 END IF;
1559 IF NEW."verification_time" ISNULL THEN
1560 NEW."verification_time" := "policy_row"."verification_time";
1561 END IF;
1562 IF NEW."voting_time" ISNULL THEN
1563 NEW."voting_time" := "policy_row"."voting_time";
1564 END IF;
1565 RETURN NEW;
1566 END;
1567 $$;
1569 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1570 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1572 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1573 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1576 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
1577 RETURNS TRIGGER
1578 LANGUAGE 'plpgsql' VOLATILE AS $$
1579 BEGIN
1580 IF NEW."draft_id" ISNULL THEN
1581 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1582 WHERE "initiative_id" = NEW."initiative_id";
1583 END IF;
1584 RETURN NEW;
1585 END;
1586 $$;
1588 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1589 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
1591 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
1592 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';
1596 ----------------------------------------
1597 -- Automatic creation of dependencies --
1598 ----------------------------------------
1601 CREATE FUNCTION "autocreate_interest_trigger"()
1602 RETURNS TRIGGER
1603 LANGUAGE 'plpgsql' VOLATILE AS $$
1604 BEGIN
1605 IF NOT EXISTS (
1606 SELECT NULL FROM "initiative" JOIN "interest"
1607 ON "initiative"."issue_id" = "interest"."issue_id"
1608 WHERE "initiative"."id" = NEW."initiative_id"
1609 AND "interest"."member_id" = NEW."member_id"
1610 ) THEN
1611 BEGIN
1612 INSERT INTO "interest" ("issue_id", "member_id")
1613 SELECT "issue_id", NEW."member_id"
1614 FROM "initiative" WHERE "id" = NEW."initiative_id";
1615 EXCEPTION WHEN unique_violation THEN END;
1616 END IF;
1617 RETURN NEW;
1618 END;
1619 $$;
1621 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1622 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1624 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1625 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';
1628 CREATE FUNCTION "autocreate_supporter_trigger"()
1629 RETURNS TRIGGER
1630 LANGUAGE 'plpgsql' VOLATILE AS $$
1631 BEGIN
1632 IF NOT EXISTS (
1633 SELECT NULL FROM "suggestion" JOIN "supporter"
1634 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1635 WHERE "suggestion"."id" = NEW."suggestion_id"
1636 AND "supporter"."member_id" = NEW."member_id"
1637 ) THEN
1638 BEGIN
1639 INSERT INTO "supporter" ("initiative_id", "member_id")
1640 SELECT "initiative_id", NEW."member_id"
1641 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1642 EXCEPTION WHEN unique_violation THEN END;
1643 END IF;
1644 RETURN NEW;
1645 END;
1646 $$;
1648 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1649 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1651 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1652 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.';
1656 ------------------------------------------
1657 -- Views and helper functions for views --
1658 ------------------------------------------
1661 CREATE VIEW "unit_delegation" AS
1662 SELECT
1663 "unit"."id" AS "unit_id",
1664 "delegation"."id",
1665 "delegation"."truster_id",
1666 "delegation"."trustee_id",
1667 "delegation"."scope"
1668 FROM "unit"
1669 JOIN "delegation"
1670 ON "delegation"."unit_id" = "unit"."id"
1671 JOIN "member"
1672 ON "delegation"."truster_id" = "member"."id"
1673 JOIN "privilege"
1674 ON "delegation"."unit_id" = "privilege"."unit_id"
1675 AND "delegation"."truster_id" = "privilege"."member_id"
1676 WHERE "member"."active" AND "privilege"."voting_right";
1678 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
1681 CREATE VIEW "area_delegation" AS
1682 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1683 "area"."id" AS "area_id",
1684 "delegation"."id",
1685 "delegation"."truster_id",
1686 "delegation"."trustee_id",
1687 "delegation"."scope"
1688 FROM "area"
1689 JOIN "delegation"
1690 ON "delegation"."unit_id" = "area"."unit_id"
1691 OR "delegation"."area_id" = "area"."id"
1692 JOIN "member"
1693 ON "delegation"."truster_id" = "member"."id"
1694 JOIN "privilege"
1695 ON "area"."unit_id" = "privilege"."unit_id"
1696 AND "delegation"."truster_id" = "privilege"."member_id"
1697 WHERE "member"."active" AND "privilege"."voting_right"
1698 ORDER BY
1699 "area"."id",
1700 "delegation"."truster_id",
1701 "delegation"."scope" DESC;
1703 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
1706 CREATE VIEW "issue_delegation" AS
1707 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1708 "issue"."id" AS "issue_id",
1709 "delegation"."id",
1710 "delegation"."truster_id",
1711 "delegation"."trustee_id",
1712 "delegation"."scope"
1713 FROM "issue"
1714 JOIN "area"
1715 ON "area"."id" = "issue"."area_id"
1716 JOIN "delegation"
1717 ON "delegation"."unit_id" = "area"."unit_id"
1718 OR "delegation"."area_id" = "area"."id"
1719 OR "delegation"."issue_id" = "issue"."id"
1720 JOIN "member"
1721 ON "delegation"."truster_id" = "member"."id"
1722 JOIN "privilege"
1723 ON "area"."unit_id" = "privilege"."unit_id"
1724 AND "delegation"."truster_id" = "privilege"."member_id"
1725 WHERE "member"."active" AND "privilege"."voting_right"
1726 ORDER BY
1727 "issue"."id",
1728 "delegation"."truster_id",
1729 "delegation"."scope" DESC;
1731 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
1734 CREATE FUNCTION "membership_weight_with_skipping"
1735 ( "area_id_p" "area"."id"%TYPE,
1736 "member_id_p" "member"."id"%TYPE,
1737 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1738 RETURNS INT4
1739 LANGUAGE 'plpgsql' STABLE AS $$
1740 DECLARE
1741 "sum_v" INT4;
1742 "delegation_row" "area_delegation"%ROWTYPE;
1743 BEGIN
1744 "sum_v" := 1;
1745 FOR "delegation_row" IN
1746 SELECT "area_delegation".*
1747 FROM "area_delegation" LEFT JOIN "membership"
1748 ON "membership"."area_id" = "area_id_p"
1749 AND "membership"."member_id" = "area_delegation"."truster_id"
1750 WHERE "area_delegation"."area_id" = "area_id_p"
1751 AND "area_delegation"."trustee_id" = "member_id_p"
1752 AND "membership"."member_id" ISNULL
1753 LOOP
1754 IF NOT
1755 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1756 THEN
1757 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1758 "area_id_p",
1759 "delegation_row"."truster_id",
1760 "skip_member_ids_p" || "delegation_row"."truster_id"
1761 );
1762 END IF;
1763 END LOOP;
1764 RETURN "sum_v";
1765 END;
1766 $$;
1768 COMMENT ON FUNCTION "membership_weight_with_skipping"
1769 ( "area"."id"%TYPE,
1770 "member"."id"%TYPE,
1771 INT4[] )
1772 IS 'Helper function for "membership_weight" function';
1775 CREATE FUNCTION "membership_weight"
1776 ( "area_id_p" "area"."id"%TYPE,
1777 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1778 RETURNS INT4
1779 LANGUAGE 'plpgsql' STABLE AS $$
1780 BEGIN
1781 RETURN "membership_weight_with_skipping"(
1782 "area_id_p",
1783 "member_id_p",
1784 ARRAY["member_id_p"]
1785 );
1786 END;
1787 $$;
1789 COMMENT ON FUNCTION "membership_weight"
1790 ( "area"."id"%TYPE,
1791 "member"."id"%TYPE )
1792 IS 'Calculates the potential voting weight of a member in a given area';
1795 CREATE VIEW "member_count_view" AS
1796 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1798 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1801 CREATE VIEW "unit_member_count" AS
1802 SELECT
1803 "unit"."id" AS "unit_id",
1804 sum("member"."id") AS "member_count"
1805 FROM "unit"
1806 LEFT JOIN "privilege"
1807 ON "privilege"."unit_id" = "unit"."id"
1808 AND "privilege"."voting_right"
1809 LEFT JOIN "member"
1810 ON "member"."id" = "privilege"."member_id"
1811 AND "member"."active"
1812 GROUP BY "unit"."id";
1814 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
1817 CREATE VIEW "area_member_count" AS
1818 SELECT
1819 "area"."id" AS "area_id",
1820 count("member"."id") AS "direct_member_count",
1821 coalesce(
1822 sum(
1823 CASE WHEN "member"."id" NOTNULL THEN
1824 "membership_weight"("area"."id", "member"."id")
1825 ELSE 0 END
1826 )
1827 ) AS "member_weight",
1828 coalesce(
1829 sum(
1830 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
1831 "membership_weight"("area"."id", "member"."id")
1832 ELSE 0 END
1833 )
1834 ) AS "autoreject_weight"
1835 FROM "area"
1836 LEFT JOIN "membership"
1837 ON "area"."id" = "membership"."area_id"
1838 LEFT JOIN "privilege"
1839 ON "privilege"."unit_id" = "area"."unit_id"
1840 AND "privilege"."member_id" = "membership"."member_id"
1841 AND "privilege"."voting_right"
1842 LEFT JOIN "member"
1843 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
1844 AND "member"."active"
1845 GROUP BY "area"."id";
1847 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count", "member_weight" and "autoreject_weight" columns of table "area"';
1850 CREATE VIEW "opening_draft" AS
1851 SELECT "draft".* FROM (
1852 SELECT
1853 "initiative"."id" AS "initiative_id",
1854 min("draft"."id") AS "draft_id"
1855 FROM "initiative" JOIN "draft"
1856 ON "initiative"."id" = "draft"."initiative_id"
1857 GROUP BY "initiative"."id"
1858 ) AS "subquery"
1859 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1861 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1864 CREATE VIEW "current_draft" AS
1865 SELECT "draft".* FROM (
1866 SELECT
1867 "initiative"."id" AS "initiative_id",
1868 max("draft"."id") AS "draft_id"
1869 FROM "initiative" JOIN "draft"
1870 ON "initiative"."id" = "draft"."initiative_id"
1871 GROUP BY "initiative"."id"
1872 ) AS "subquery"
1873 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1875 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1878 CREATE VIEW "critical_opinion" AS
1879 SELECT * FROM "opinion"
1880 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
1881 OR ("degree" = -2 AND "fulfilled" = TRUE);
1883 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
1886 CREATE VIEW "battle_participant" AS
1887 SELECT "initiative"."id", "initiative"."issue_id"
1888 FROM "issue" JOIN "initiative"
1889 ON "issue"."id" = "initiative"."issue_id"
1890 WHERE "initiative"."admitted"
1891 UNION ALL
1892 SELECT NULL, "id" AS "issue_id"
1893 FROM "issue";
1895 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
1898 CREATE VIEW "battle_view" AS
1899 SELECT
1900 "issue"."id" AS "issue_id",
1901 "winning_initiative"."id" AS "winning_initiative_id",
1902 "losing_initiative"."id" AS "losing_initiative_id",
1903 sum(
1904 CASE WHEN
1905 coalesce("better_vote"."grade", 0) >
1906 coalesce("worse_vote"."grade", 0)
1907 THEN "direct_voter"."weight" ELSE 0 END
1908 ) AS "count"
1909 FROM "issue"
1910 LEFT JOIN "direct_voter"
1911 ON "issue"."id" = "direct_voter"."issue_id"
1912 JOIN "battle_participant" AS "winning_initiative"
1913 ON "issue"."id" = "winning_initiative"."issue_id"
1914 JOIN "battle_participant" AS "losing_initiative"
1915 ON "issue"."id" = "losing_initiative"."issue_id"
1916 LEFT JOIN "vote" AS "better_vote"
1917 ON "direct_voter"."member_id" = "better_vote"."member_id"
1918 AND "winning_initiative"."id" = "better_vote"."initiative_id"
1919 LEFT JOIN "vote" AS "worse_vote"
1920 ON "direct_voter"."member_id" = "worse_vote"."member_id"
1921 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
1922 WHERE "issue"."closed" NOTNULL
1923 AND "issue"."cleaned" ISNULL
1924 AND (
1925 "winning_initiative"."id" != "losing_initiative"."id" OR
1926 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
1927 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
1928 GROUP BY
1929 "issue"."id",
1930 "winning_initiative"."id",
1931 "losing_initiative"."id";
1933 COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative (or status-quo) to another initiative (or status-quo); Used to fill "battle" table';
1936 CREATE VIEW "expired_session" AS
1937 SELECT * FROM "session" WHERE now() > "expiry";
1939 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
1940 DELETE FROM "session" WHERE "ident" = OLD."ident";
1942 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
1943 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
1946 CREATE VIEW "open_issue" AS
1947 SELECT * FROM "issue" WHERE "closed" ISNULL;
1949 COMMENT ON VIEW "open_issue" IS 'All open issues';
1952 CREATE VIEW "issue_with_ranks_missing" AS
1953 SELECT * FROM "issue"
1954 WHERE "fully_frozen" NOTNULL
1955 AND "closed" NOTNULL
1956 AND "ranks_available" = FALSE;
1958 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
1961 CREATE VIEW "member_contingent" AS
1962 SELECT
1963 "member"."id" AS "member_id",
1964 "contingent"."time_frame",
1965 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
1966 (
1967 SELECT count(1) FROM "draft"
1968 WHERE "draft"."author_id" = "member"."id"
1969 AND "draft"."created" > now() - "contingent"."time_frame"
1970 ) + (
1971 SELECT count(1) FROM "suggestion"
1972 WHERE "suggestion"."author_id" = "member"."id"
1973 AND "suggestion"."created" > now() - "contingent"."time_frame"
1974 )
1975 ELSE NULL END AS "text_entry_count",
1976 "contingent"."text_entry_limit",
1977 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
1978 SELECT count(1) FROM "opening_draft"
1979 WHERE "opening_draft"."author_id" = "member"."id"
1980 AND "opening_draft"."created" > now() - "contingent"."time_frame"
1981 ) ELSE NULL END AS "initiative_count",
1982 "contingent"."initiative_limit"
1983 FROM "member" CROSS JOIN "contingent";
1985 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
1987 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
1988 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
1991 CREATE VIEW "member_contingent_left" AS
1992 SELECT
1993 "member_id",
1994 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
1995 max("initiative_limit" - "initiative_count") AS "initiatives_left"
1996 FROM "member_contingent" GROUP BY "member_id";
1998 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.';
2001 CREATE VIEW "event_seen_by_member" AS
2002 SELECT
2003 "member"."id" AS "seen_by_member_id",
2004 CASE WHEN "event"."state" IN (
2005 'voting',
2006 'finished_without_winner',
2007 'finished_with_winner'
2008 ) THEN
2009 'voting'::"notify_level"
2010 ELSE
2011 CASE WHEN "event"."state" IN (
2012 'verification',
2013 'canceled_after_revocation_during_verification',
2014 'canceled_no_initiative_admitted'
2015 ) THEN
2016 'verification'::"notify_level"
2017 ELSE
2018 CASE WHEN "event"."state" IN (
2019 'discussion',
2020 'canceled_after_revocation_during_discussion'
2021 ) THEN
2022 'discussion'::"notify_level"
2023 ELSE
2024 'all'::"notify_level"
2025 END
2026 END
2027 END AS "notify_level",
2028 "event".*
2029 FROM "member" CROSS JOIN "event"
2030 LEFT JOIN "issue"
2031 ON "event"."issue_id" = "issue"."id"
2032 LEFT JOIN "membership"
2033 ON "member"."id" = "membership"."member_id"
2034 AND "issue"."area_id" = "membership"."area_id"
2035 LEFT JOIN "interest"
2036 ON "member"."id" = "interest"."member_id"
2037 AND "event"."issue_id" = "interest"."issue_id"
2038 LEFT JOIN "supporter"
2039 ON "member"."id" = "supporter"."member_id"
2040 AND "event"."initiative_id" = "supporter"."initiative_id"
2041 LEFT JOIN "ignored_member"
2042 ON "member"."id" = "ignored_member"."member_id"
2043 AND "event"."member_id" = "ignored_member"."other_member_id"
2044 LEFT JOIN "ignored_initiative"
2045 ON "member"."id" = "ignored_initiative"."member_id"
2046 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2047 WHERE (
2048 "supporter"."member_id" NOTNULL OR
2049 "interest"."member_id" NOTNULL OR
2050 ( "membership"."member_id" NOTNULL AND
2051 "event"."event" IN (
2052 'issue_state_changed',
2053 'initiative_created_in_new_issue',
2054 'initiative_created_in_existing_issue',
2055 'initiative_revoked' ) ) )
2056 AND "ignored_member"."member_id" ISNULL
2057 AND "ignored_initiative"."member_id" ISNULL;
2059 COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support';
2062 CREATE VIEW "pending_notification" AS
2063 SELECT
2064 "member"."id" AS "seen_by_member_id",
2065 "event".*
2066 FROM "member" CROSS JOIN "event"
2067 LEFT JOIN "issue"
2068 ON "event"."issue_id" = "issue"."id"
2069 LEFT JOIN "membership"
2070 ON "member"."id" = "membership"."member_id"
2071 AND "issue"."area_id" = "membership"."area_id"
2072 LEFT JOIN "interest"
2073 ON "member"."id" = "interest"."member_id"
2074 AND "event"."issue_id" = "interest"."issue_id"
2075 LEFT JOIN "supporter"
2076 ON "member"."id" = "supporter"."member_id"
2077 AND "event"."initiative_id" = "supporter"."initiative_id"
2078 LEFT JOIN "ignored_member"
2079 ON "member"."id" = "ignored_member"."member_id"
2080 AND "event"."member_id" = "ignored_member"."other_member_id"
2081 LEFT JOIN "ignored_initiative"
2082 ON "member"."id" = "ignored_initiative"."member_id"
2083 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2084 WHERE (
2085 "member"."notify_event_id" ISNULL OR
2086 ( "member"."notify_event_id" NOTNULL AND
2087 "member"."notify_event_id" < "event"."id" ) )
2088 AND (
2089 ( "member"."notify_level" >= 'all' ) OR
2090 ( "member"."notify_level" >= 'voting' AND
2091 "event"."state" IN (
2092 'voting',
2093 'finished_without_winner',
2094 'finished_with_winner' ) ) OR
2095 ( "member"."notify_level" >= 'verification' AND
2096 "event"."state" IN (
2097 'verification',
2098 'canceled_after_revocation_during_verification',
2099 'canceled_no_initiative_admitted' ) ) OR
2100 ( "member"."notify_level" >= 'discussion' AND
2101 "event"."state" IN (
2102 'discussion',
2103 'canceled_after_revocation_during_discussion' ) ) )
2104 AND (
2105 "supporter"."member_id" NOTNULL OR
2106 "interest"."member_id" NOTNULL OR
2107 ( "membership"."member_id" NOTNULL AND
2108 "event"."event" IN (
2109 'issue_state_changed',
2110 'initiative_created_in_new_issue',
2111 'initiative_created_in_existing_issue',
2112 'initiative_revoked' ) ) )
2113 AND "ignored_member"."member_id" ISNULL
2114 AND "ignored_initiative"."member_id" ISNULL;
2116 COMMENT ON VIEW "pending_notification" IS 'Events to be sent to "notify_email" address of member referred to by "seen_by_member_id"';
2119 CREATE TYPE "timeline_event" AS ENUM (
2120 'issue_created',
2121 'issue_canceled',
2122 'issue_accepted',
2123 'issue_half_frozen',
2124 'issue_finished_without_voting',
2125 'issue_voting_started',
2126 'issue_finished_after_voting',
2127 'initiative_created',
2128 'initiative_revoked',
2129 'draft_created',
2130 'suggestion_created');
2132 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)';
2135 CREATE VIEW "timeline_issue" AS
2136 SELECT
2137 "created" AS "occurrence",
2138 'issue_created'::"timeline_event" AS "event",
2139 "id" AS "issue_id"
2140 FROM "issue"
2141 UNION ALL
2142 SELECT
2143 "closed" AS "occurrence",
2144 'issue_canceled'::"timeline_event" AS "event",
2145 "id" AS "issue_id"
2146 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
2147 UNION ALL
2148 SELECT
2149 "accepted" AS "occurrence",
2150 'issue_accepted'::"timeline_event" AS "event",
2151 "id" AS "issue_id"
2152 FROM "issue" WHERE "accepted" NOTNULL
2153 UNION ALL
2154 SELECT
2155 "half_frozen" AS "occurrence",
2156 'issue_half_frozen'::"timeline_event" AS "event",
2157 "id" AS "issue_id"
2158 FROM "issue" WHERE "half_frozen" NOTNULL
2159 UNION ALL
2160 SELECT
2161 "fully_frozen" AS "occurrence",
2162 'issue_voting_started'::"timeline_event" AS "event",
2163 "id" AS "issue_id"
2164 FROM "issue"
2165 WHERE "fully_frozen" NOTNULL
2166 AND ("closed" ISNULL OR "closed" != "fully_frozen")
2167 UNION ALL
2168 SELECT
2169 "closed" AS "occurrence",
2170 CASE WHEN "fully_frozen" = "closed" THEN
2171 'issue_finished_without_voting'::"timeline_event"
2172 ELSE
2173 'issue_finished_after_voting'::"timeline_event"
2174 END AS "event",
2175 "id" AS "issue_id"
2176 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
2178 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)';
2181 CREATE VIEW "timeline_initiative" AS
2182 SELECT
2183 "created" AS "occurrence",
2184 'initiative_created'::"timeline_event" AS "event",
2185 "id" AS "initiative_id"
2186 FROM "initiative"
2187 UNION ALL
2188 SELECT
2189 "revoked" AS "occurrence",
2190 'initiative_revoked'::"timeline_event" AS "event",
2191 "id" AS "initiative_id"
2192 FROM "initiative" WHERE "revoked" NOTNULL;
2194 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)';
2197 CREATE VIEW "timeline_draft" AS
2198 SELECT
2199 "created" AS "occurrence",
2200 'draft_created'::"timeline_event" AS "event",
2201 "id" AS "draft_id"
2202 FROM "draft";
2204 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)';
2207 CREATE VIEW "timeline_suggestion" AS
2208 SELECT
2209 "created" AS "occurrence",
2210 'suggestion_created'::"timeline_event" AS "event",
2211 "id" AS "suggestion_id"
2212 FROM "suggestion";
2214 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)';
2217 CREATE VIEW "timeline" AS
2218 SELECT
2219 "occurrence",
2220 "event",
2221 "issue_id",
2222 NULL AS "initiative_id",
2223 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
2224 NULL::INT8 AS "suggestion_id"
2225 FROM "timeline_issue"
2226 UNION ALL
2227 SELECT
2228 "occurrence",
2229 "event",
2230 NULL AS "issue_id",
2231 "initiative_id",
2232 NULL AS "draft_id",
2233 NULL AS "suggestion_id"
2234 FROM "timeline_initiative"
2235 UNION ALL
2236 SELECT
2237 "occurrence",
2238 "event",
2239 NULL AS "issue_id",
2240 NULL AS "initiative_id",
2241 "draft_id",
2242 NULL AS "suggestion_id"
2243 FROM "timeline_draft"
2244 UNION ALL
2245 SELECT
2246 "occurrence",
2247 "event",
2248 NULL AS "issue_id",
2249 NULL AS "initiative_id",
2250 NULL AS "draft_id",
2251 "suggestion_id"
2252 FROM "timeline_suggestion";
2254 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)';
2258 --------------------------------------------------
2259 -- Set returning function for delegation chains --
2260 --------------------------------------------------
2263 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
2264 ('first', 'intermediate', 'last', 'repetition');
2266 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
2269 CREATE TYPE "delegation_chain_row" AS (
2270 "index" INT4,
2271 "member_id" INT4,
2272 "member_valid" BOOLEAN,
2273 "participation" BOOLEAN,
2274 "overridden" BOOLEAN,
2275 "scope_in" "delegation_scope",
2276 "scope_out" "delegation_scope",
2277 "disabled_out" BOOLEAN,
2278 "loop" "delegation_chain_loop_tag" );
2280 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
2282 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
2283 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';
2284 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
2285 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
2286 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
2287 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
2288 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
2291 CREATE FUNCTION "delegation_chain"
2292 ( "member_id_p" "member"."id"%TYPE,
2293 "unit_id_p" "unit"."id"%TYPE,
2294 "area_id_p" "area"."id"%TYPE,
2295 "issue_id_p" "issue"."id"%TYPE,
2296 "simulate_trustee_id_p" "member"."id"%TYPE )
2297 RETURNS SETOF "delegation_chain_row"
2298 LANGUAGE 'plpgsql' STABLE AS $$
2299 DECLARE
2300 "scope_v" "delegation_scope";
2301 "unit_id_v" "unit"."id"%TYPE;
2302 "area_id_v" "area"."id"%TYPE;
2303 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2304 "loop_member_id_v" "member"."id"%TYPE;
2305 "output_row" "delegation_chain_row";
2306 "output_rows" "delegation_chain_row"[];
2307 "delegation_row" "delegation"%ROWTYPE;
2308 "row_count" INT4;
2309 "i" INT4;
2310 "loop_v" BOOLEAN;
2311 BEGIN
2312 IF
2313 "unit_id_p" NOTNULL AND
2314 "area_id_p" ISNULL AND
2315 "issue_id_p" ISNULL
2316 THEN
2317 "scope_v" := 'unit';
2318 "unit_id_v" := "unit_id_p";
2319 ELSIF
2320 "unit_id_p" ISNULL AND
2321 "area_id_p" NOTNULL AND
2322 "issue_id_p" ISNULL
2323 THEN
2324 "scope_v" := 'area';
2325 "area_id_v" := "area_id_p";
2326 SELECT "unit_id" INTO "unit_id_v"
2327 FROM "area" WHERE "id" = "area_id_v";
2328 ELSIF
2329 "unit_id_p" ISNULL AND
2330 "area_id_p" ISNULL AND
2331 "issue_id_p" NOTNULL
2332 THEN
2333 "scope_v" := 'issue';
2334 SELECT "area_id" INTO "area_id_v"
2335 FROM "issue" WHERE "id" = "issue_id_p";
2336 SELECT "unit_id" INTO "unit_id_v"
2337 FROM "area" WHERE "id" = "area_id_v";
2338 ELSE
2339 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2340 END IF;
2341 "visited_member_ids" := '{}';
2342 "loop_member_id_v" := NULL;
2343 "output_rows" := '{}';
2344 "output_row"."index" := 0;
2345 "output_row"."member_id" := "member_id_p";
2346 "output_row"."member_valid" := TRUE;
2347 "output_row"."participation" := FALSE;
2348 "output_row"."overridden" := FALSE;
2349 "output_row"."disabled_out" := FALSE;
2350 "output_row"."scope_out" := NULL;
2351 LOOP
2352 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2353 "loop_member_id_v" := "output_row"."member_id";
2354 ELSE
2355 "visited_member_ids" :=
2356 "visited_member_ids" || "output_row"."member_id";
2357 END IF;
2358 IF "output_row"."participation" THEN
2359 "output_row"."overridden" := TRUE;
2360 END IF;
2361 "output_row"."scope_in" := "output_row"."scope_out";
2362 IF EXISTS (
2363 SELECT NULL FROM "member" JOIN "privilege"
2364 ON "privilege"."member_id" = "member"."id"
2365 AND "privilege"."unit_id" = "unit_id_v"
2366 WHERE "id" = "output_row"."member_id"
2367 AND "member"."active" AND "privilege"."voting_right"
2368 ) THEN
2369 IF "scope_v" = 'unit' THEN
2370 SELECT * INTO "delegation_row" FROM "delegation"
2371 WHERE "truster_id" = "output_row"."member_id"
2372 AND "unit_id" = "unit_id_v";
2373 ELSIF "scope_v" = 'area' THEN
2374 "output_row"."participation" := EXISTS (
2375 SELECT NULL FROM "membership"
2376 WHERE "area_id" = "area_id_p"
2377 AND "member_id" = "output_row"."member_id"
2378 );
2379 SELECT * INTO "delegation_row" FROM "delegation"
2380 WHERE "truster_id" = "output_row"."member_id"
2381 AND (
2382 "unit_id" = "unit_id_v" OR
2383 "area_id" = "area_id_v"
2384 )
2385 ORDER BY "scope" DESC;
2386 ELSIF "scope_v" = 'issue' THEN
2387 "output_row"."participation" := EXISTS (
2388 SELECT NULL FROM "interest"
2389 WHERE "issue_id" = "issue_id_p"
2390 AND "member_id" = "output_row"."member_id"
2391 );
2392 SELECT * INTO "delegation_row" FROM "delegation"
2393 WHERE "truster_id" = "output_row"."member_id"
2394 AND (
2395 "unit_id" = "unit_id_v" OR
2396 "area_id" = "area_id_v" OR
2397 "issue_id" = "issue_id_p"
2398 )
2399 ORDER BY "scope" DESC;
2400 END IF;
2401 ELSE
2402 "output_row"."member_valid" := FALSE;
2403 "output_row"."participation" := FALSE;
2404 "output_row"."scope_out" := NULL;
2405 "delegation_row" := ROW(NULL);
2406 END IF;
2407 IF
2408 "output_row"."member_id" = "member_id_p" AND
2409 "simulate_trustee_id_p" NOTNULL
2410 THEN
2411 "output_row"."scope_out" := "scope_v";
2412 "output_rows" := "output_rows" || "output_row";
2413 "output_row"."member_id" := "simulate_trustee_id_p";
2414 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2415 "output_row"."scope_out" := "delegation_row"."scope";
2416 "output_rows" := "output_rows" || "output_row";
2417 "output_row"."member_id" := "delegation_row"."trustee_id";
2418 ELSIF "delegation_row"."scope" NOTNULL THEN
2419 "output_row"."scope_out" := "delegation_row"."scope";
2420 "output_row"."disabled_out" := TRUE;
2421 "output_rows" := "output_rows" || "output_row";
2422 EXIT;
2423 ELSE
2424 "output_row"."scope_out" := NULL;
2425 "output_rows" := "output_rows" || "output_row";
2426 EXIT;
2427 END IF;
2428 EXIT WHEN "loop_member_id_v" NOTNULL;
2429 "output_row"."index" := "output_row"."index" + 1;
2430 END LOOP;
2431 "row_count" := array_upper("output_rows", 1);
2432 "i" := 1;
2433 "loop_v" := FALSE;
2434 LOOP
2435 "output_row" := "output_rows"["i"];
2436 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2437 IF "loop_v" THEN
2438 IF "i" + 1 = "row_count" THEN
2439 "output_row"."loop" := 'last';
2440 ELSIF "i" = "row_count" THEN
2441 "output_row"."loop" := 'repetition';
2442 ELSE
2443 "output_row"."loop" := 'intermediate';
2444 END IF;
2445 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2446 "output_row"."loop" := 'first';
2447 "loop_v" := TRUE;
2448 END IF;
2449 IF "scope_v" = 'unit' THEN
2450 "output_row"."participation" := NULL;
2451 END IF;
2452 RETURN NEXT "output_row";
2453 "i" := "i" + 1;
2454 END LOOP;
2455 RETURN;
2456 END;
2457 $$;
2459 COMMENT ON FUNCTION "delegation_chain"
2460 ( "member"."id"%TYPE,
2461 "unit"."id"%TYPE,
2462 "area"."id"%TYPE,
2463 "issue"."id"%TYPE,
2464 "member"."id"%TYPE )
2465 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
2468 CREATE FUNCTION "delegation_chain"
2469 ( "member_id_p" "member"."id"%TYPE,
2470 "unit_id_p" "unit"."id"%TYPE,
2471 "area_id_p" "area"."id"%TYPE,
2472 "issue_id_p" "issue"."id"%TYPE )
2473 RETURNS SETOF "delegation_chain_row"
2474 LANGUAGE 'plpgsql' STABLE AS $$
2475 DECLARE
2476 "result_row" "delegation_chain_row";
2477 BEGIN
2478 FOR "result_row" IN
2479 SELECT * FROM "delegation_chain"(
2480 "member_id_p", "unit_id_p", "area_id_p", "issue_id_p", NULL
2481 )
2482 LOOP
2483 RETURN NEXT "result_row";
2484 END LOOP;
2485 RETURN;
2486 END;
2487 $$;
2489 COMMENT ON FUNCTION "delegation_chain"
2490 ( "member"."id"%TYPE,
2491 "unit"."id"%TYPE,
2492 "area"."id"%TYPE,
2493 "issue"."id"%TYPE )
2494 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
2498 ------------------------------
2499 -- Comparison by vote count --
2500 ------------------------------
2502 CREATE FUNCTION "vote_ratio"
2503 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
2504 "negative_votes_p" "initiative"."negative_votes"%TYPE )
2505 RETURNS FLOAT8
2506 LANGUAGE 'plpgsql' STABLE AS $$
2507 BEGIN
2508 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
2509 RETURN
2510 "positive_votes_p"::FLOAT8 /
2511 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
2512 ELSIF "positive_votes_p" > 0 THEN
2513 RETURN "positive_votes_p";
2514 ELSIF "negative_votes_p" > 0 THEN
2515 RETURN 1 - "negative_votes_p";
2516 ELSE
2517 RETURN 0.5;
2518 END IF;
2519 END;
2520 $$;
2522 COMMENT ON FUNCTION "vote_ratio"
2523 ( "initiative"."positive_votes"%TYPE,
2524 "initiative"."negative_votes"%TYPE )
2525 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.';
2529 ------------------------------------------------
2530 -- Locking for snapshots and voting procedure --
2531 ------------------------------------------------
2534 CREATE FUNCTION "share_row_lock_issue_trigger"()
2535 RETURNS TRIGGER
2536 LANGUAGE 'plpgsql' VOLATILE AS $$
2537 BEGIN
2538 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2539 PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;
2540 END IF;
2541 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2542 PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;
2543 RETURN NEW;
2544 ELSE
2545 RETURN OLD;
2546 END IF;
2547 END;
2548 $$;
2550 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';
2553 CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()
2554 RETURNS TRIGGER
2555 LANGUAGE 'plpgsql' VOLATILE AS $$
2556 BEGIN
2557 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2558 PERFORM NULL FROM "issue"
2559 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2560 WHERE "initiative"."id" = OLD."initiative_id"
2561 FOR SHARE OF "issue";
2562 END IF;
2563 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2564 PERFORM NULL FROM "issue"
2565 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2566 WHERE "initiative"."id" = NEW."initiative_id"
2567 FOR SHARE OF "issue";
2568 RETURN NEW;
2569 ELSE
2570 RETURN OLD;
2571 END IF;
2572 END;
2573 $$;
2575 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';
2578 CREATE TRIGGER "share_row_lock_issue"
2579 BEFORE INSERT OR UPDATE OR DELETE ON "initiative"
2580 FOR EACH ROW EXECUTE PROCEDURE
2581 "share_row_lock_issue_trigger"();
2583 CREATE TRIGGER "share_row_lock_issue"
2584 BEFORE INSERT OR UPDATE OR DELETE ON "interest"
2585 FOR EACH ROW EXECUTE PROCEDURE
2586 "share_row_lock_issue_trigger"();
2588 CREATE TRIGGER "share_row_lock_issue"
2589 BEFORE INSERT OR UPDATE OR DELETE ON "supporter"
2590 FOR EACH ROW EXECUTE PROCEDURE
2591 "share_row_lock_issue_trigger"();
2593 CREATE TRIGGER "share_row_lock_issue_via_initiative"
2594 BEFORE INSERT OR UPDATE OR DELETE ON "opinion"
2595 FOR EACH ROW EXECUTE PROCEDURE
2596 "share_row_lock_issue_via_initiative_trigger"();
2598 CREATE TRIGGER "share_row_lock_issue"
2599 BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter"
2600 FOR EACH ROW EXECUTE PROCEDURE
2601 "share_row_lock_issue_trigger"();
2603 CREATE TRIGGER "share_row_lock_issue"
2604 BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter"
2605 FOR EACH ROW EXECUTE PROCEDURE
2606 "share_row_lock_issue_trigger"();
2608 CREATE TRIGGER "share_row_lock_issue"
2609 BEFORE INSERT OR UPDATE OR DELETE ON "vote"
2610 FOR EACH ROW EXECUTE PROCEDURE
2611 "share_row_lock_issue_trigger"();
2613 COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function';
2614 COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function';
2615 COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function';
2616 COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function';
2617 COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function';
2618 COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function';
2619 COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function';
2622 CREATE FUNCTION "lock_issue"
2623 ( "issue_id_p" "issue"."id"%TYPE )
2624 RETURNS VOID
2625 LANGUAGE 'plpgsql' VOLATILE AS $$
2626 BEGIN
2627 LOCK TABLE "member" IN SHARE MODE;
2628 LOCK TABLE "privilege" IN SHARE MODE;
2629 LOCK TABLE "membership" IN SHARE MODE;
2630 LOCK TABLE "policy" IN SHARE MODE;
2631 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2632 -- NOTE: The row-level exclusive lock in combination with the
2633 -- share_row_lock_issue(_via_initiative)_trigger functions (which
2634 -- acquire a row-level share lock on the issue) ensure that no data
2635 -- is changed, which could affect calculation of snapshots or
2636 -- counting of votes. Table "delegation" must be table-level-locked,
2637 -- as it also contains issue- and global-scope delegations.
2638 LOCK TABLE "delegation" IN SHARE MODE;
2639 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
2640 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
2641 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
2642 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
2643 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
2644 RETURN;
2645 END;
2646 $$;
2648 COMMENT ON FUNCTION "lock_issue"
2649 ( "issue"."id"%TYPE )
2650 IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';
2654 ------------------------------------------------------------------------
2655 -- Regular tasks, except calculcation of snapshots and voting results --
2656 ------------------------------------------------------------------------
2658 CREATE FUNCTION "check_last_login"()
2659 RETURNS VOID
2660 LANGUAGE 'plpgsql' VOLATILE AS $$
2661 DECLARE
2662 "system_setting_row" "system_setting"%ROWTYPE;
2663 BEGIN
2664 SELECT * INTO "system_setting_row" FROM "system_setting";
2665 LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
2666 UPDATE "member" SET "last_login_public" = "last_login"::date
2667 FROM (
2668 SELECT DISTINCT "member"."id"
2669 FROM "member" LEFT JOIN "member_history"
2670 ON "member"."id" = "member_history"."member_id"
2671 WHERE "member"."last_login"::date < 'today' OR (
2672 "member_history"."until"::date >= 'today' AND
2673 "member_history"."active" = FALSE AND "member"."active" = TRUE
2674 )
2675 ) AS "subquery"
2676 WHERE "member"."id" = "subquery"."id";
2677 IF "system_setting_row"."member_ttl" NOTNULL THEN
2678 UPDATE "member" SET "active" = FALSE
2679 WHERE "active" = TRUE
2680 AND "last_login"::date < 'today'
2681 AND "last_login_public" <
2682 (now() - "system_setting_row"."member_ttl")::date;
2683 END IF;
2684 RETURN;
2685 END;
2686 $$;
2688 COMMENT ON FUNCTION "check_last_login"() IS 'Updates "last_login_public" field, which contains the date but not the time of the last login, and deactivates members who do not login for the time specified in "system_setting"."member_ttl". For privacy reasons this function does not update "last_login_public", if the last login of a member has been today (except when member was reactivated today).';
2691 CREATE FUNCTION "calculate_member_counts"()
2692 RETURNS VOID
2693 LANGUAGE 'plpgsql' VOLATILE AS $$
2694 BEGIN
2695 LOCK TABLE "member" IN SHARE MODE;
2696 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
2697 LOCK TABLE "unit" IN EXCLUSIVE MODE;
2698 LOCK TABLE "area" IN EXCLUSIVE MODE;
2699 LOCK TABLE "privilege" IN SHARE MODE;
2700 LOCK TABLE "membership" IN SHARE MODE;
2701 DELETE FROM "member_count";
2702 INSERT INTO "member_count" ("total_count")
2703 SELECT "total_count" FROM "member_count_view";
2704 UPDATE "unit" SET "member_count" = "view"."member_count"
2705 FROM "unit_member_count" AS "view"
2706 WHERE "view"."unit_id" = "unit"."id";
2707 UPDATE "area" SET
2708 "direct_member_count" = "view"."direct_member_count",
2709 "member_weight" = "view"."member_weight",
2710 "autoreject_weight" = "view"."autoreject_weight"
2711 FROM "area_member_count" AS "view"
2712 WHERE "view"."area_id" = "area"."id";
2713 RETURN;
2714 END;
2715 $$;
2717 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"';
2721 ------------------------------
2722 -- Calculation of snapshots --
2723 ------------------------------
2725 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
2726 ( "issue_id_p" "issue"."id"%TYPE,
2727 "member_id_p" "member"."id"%TYPE,
2728 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2729 RETURNS "direct_population_snapshot"."weight"%TYPE
2730 LANGUAGE 'plpgsql' VOLATILE AS $$
2731 DECLARE
2732 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2733 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
2734 "weight_v" INT4;
2735 "sub_weight_v" INT4;
2736 BEGIN
2737 "weight_v" := 0;
2738 FOR "issue_delegation_row" IN
2739 SELECT * FROM "issue_delegation"
2740 WHERE "trustee_id" = "member_id_p"
2741 AND "issue_id" = "issue_id_p"
2742 LOOP
2743 IF NOT EXISTS (
2744 SELECT NULL FROM "direct_population_snapshot"
2745 WHERE "issue_id" = "issue_id_p"
2746 AND "event" = 'periodic'
2747 AND "member_id" = "issue_delegation_row"."truster_id"
2748 ) AND NOT EXISTS (
2749 SELECT NULL FROM "delegating_population_snapshot"
2750 WHERE "issue_id" = "issue_id_p"
2751 AND "event" = 'periodic'
2752 AND "member_id" = "issue_delegation_row"."truster_id"
2753 ) THEN
2754 "delegate_member_ids_v" :=
2755 "member_id_p" || "delegate_member_ids_p";
2756 INSERT INTO "delegating_population_snapshot" (
2757 "issue_id",
2758 "event",
2759 "member_id",
2760 "scope",
2761 "delegate_member_ids"
2762 ) VALUES (
2763 "issue_id_p",
2764 'periodic',
2765 "issue_delegation_row"."truster_id",
2766 "issue_delegation_row"."scope",
2767 "delegate_member_ids_v"
2768 );
2769 "sub_weight_v" := 1 +
2770 "weight_of_added_delegations_for_population_snapshot"(
2771 "issue_id_p",
2772 "issue_delegation_row"."truster_id",
2773 "delegate_member_ids_v"
2774 );
2775 UPDATE "delegating_population_snapshot"
2776 SET "weight" = "sub_weight_v"
2777 WHERE "issue_id" = "issue_id_p"
2778 AND "event" = 'periodic'
2779 AND "member_id" = "issue_delegation_row"."truster_id";
2780 "weight_v" := "weight_v" + "sub_weight_v";
2781 END IF;
2782 END LOOP;
2783 RETURN "weight_v";
2784 END;
2785 $$;
2787 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
2788 ( "issue"."id"%TYPE,
2789 "member"."id"%TYPE,
2790 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2791 IS 'Helper function for "create_population_snapshot" function';
2794 CREATE FUNCTION "create_population_snapshot"
2795 ( "issue_id_p" "issue"."id"%TYPE )
2796 RETURNS VOID
2797 LANGUAGE 'plpgsql' VOLATILE AS $$
2798 DECLARE
2799 "member_id_v" "member"."id"%TYPE;
2800 BEGIN
2801 DELETE FROM "direct_population_snapshot"
2802 WHERE "issue_id" = "issue_id_p"
2803 AND "event" = 'periodic';
2804 DELETE FROM "delegating_population_snapshot"
2805 WHERE "issue_id" = "issue_id_p"
2806 AND "event" = 'periodic';
2807 INSERT INTO "direct_population_snapshot"
2808 ("issue_id", "event", "member_id")
2809 SELECT
2810 "issue_id_p" AS "issue_id",
2811 'periodic'::"snapshot_event" AS "event",
2812 "member"."id" AS "member_id"
2813 FROM "issue"
2814 JOIN "area" ON "issue"."area_id" = "area"."id"
2815 JOIN "membership" ON "area"."id" = "membership"."area_id"
2816 JOIN "member" ON "membership"."member_id" = "member"."id"
2817 JOIN "privilege"
2818 ON "privilege"."unit_id" = "area"."unit_id"
2819 AND "privilege"."member_id" = "member"."id"
2820 WHERE "issue"."id" = "issue_id_p"
2821 AND "member"."active" AND "privilege"."voting_right"
2822 UNION
2823 SELECT
2824 "issue_id_p" AS "issue_id",
2825 'periodic'::"snapshot_event" AS "event",
2826 "member"."id" AS "member_id"
2827 FROM "issue"
2828 JOIN "area" ON "issue"."area_id" = "area"."id"
2829 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2830 JOIN "member" ON "interest"."member_id" = "member"."id"
2831 JOIN "privilege"
2832 ON "privilege"."unit_id" = "area"."unit_id"
2833 AND "privilege"."member_id" = "member"."id"
2834 WHERE "issue"."id" = "issue_id_p"
2835 AND "member"."active" AND "privilege"."voting_right";
2836 FOR "member_id_v" IN
2837 SELECT "member_id" FROM "direct_population_snapshot"
2838 WHERE "issue_id" = "issue_id_p"
2839 AND "event" = 'periodic'
2840 LOOP
2841 UPDATE "direct_population_snapshot" SET
2842 "weight" = 1 +
2843 "weight_of_added_delegations_for_population_snapshot"(
2844 "issue_id_p",
2845 "member_id_v",
2846 '{}'
2847 )
2848 WHERE "issue_id" = "issue_id_p"
2849 AND "event" = 'periodic'
2850 AND "member_id" = "member_id_v";
2851 END LOOP;
2852 RETURN;
2853 END;
2854 $$;
2856 COMMENT ON FUNCTION "create_population_snapshot"
2857 ( "issue"."id"%TYPE )
2858 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.';
2861 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2862 ( "issue_id_p" "issue"."id"%TYPE,
2863 "member_id_p" "member"."id"%TYPE,
2864 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2865 RETURNS "direct_interest_snapshot"."weight"%TYPE
2866 LANGUAGE 'plpgsql' VOLATILE AS $$
2867 DECLARE
2868 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2869 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2870 "weight_v" INT4;
2871 "sub_weight_v" INT4;
2872 BEGIN
2873 "weight_v" := 0;
2874 FOR "issue_delegation_row" IN
2875 SELECT * FROM "issue_delegation"
2876 WHERE "trustee_id" = "member_id_p"
2877 AND "issue_id" = "issue_id_p"
2878 LOOP
2879 IF NOT EXISTS (
2880 SELECT NULL FROM "direct_interest_snapshot"
2881 WHERE "issue_id" = "issue_id_p"
2882 AND "event" = 'periodic'
2883 AND "member_id" = "issue_delegation_row"."truster_id"
2884 ) AND NOT EXISTS (
2885 SELECT NULL FROM "delegating_interest_snapshot"
2886 WHERE "issue_id" = "issue_id_p"
2887 AND "event" = 'periodic'
2888 AND "member_id" = "issue_delegation_row"."truster_id"
2889 ) THEN
2890 "delegate_member_ids_v" :=
2891 "member_id_p" || "delegate_member_ids_p";
2892 INSERT INTO "delegating_interest_snapshot" (
2893 "issue_id",
2894 "event",
2895 "member_id",
2896 "scope",
2897 "delegate_member_ids"
2898 ) VALUES (
2899 "issue_id_p",
2900 'periodic',
2901 "issue_delegation_row"."truster_id",
2902 "issue_delegation_row"."scope",
2903 "delegate_member_ids_v"
2904 );
2905 "sub_weight_v" := 1 +
2906 "weight_of_added_delegations_for_interest_snapshot"(
2907 "issue_id_p",
2908 "issue_delegation_row"."truster_id",
2909 "delegate_member_ids_v"
2910 );
2911 UPDATE "delegating_interest_snapshot"
2912 SET "weight" = "sub_weight_v"
2913 WHERE "issue_id" = "issue_id_p"
2914 AND "event" = 'periodic'
2915 AND "member_id" = "issue_delegation_row"."truster_id";
2916 "weight_v" := "weight_v" + "sub_weight_v";
2917 END IF;
2918 END LOOP;
2919 RETURN "weight_v";
2920 END;
2921 $$;
2923 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2924 ( "issue"."id"%TYPE,
2925 "member"."id"%TYPE,
2926 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2927 IS 'Helper function for "create_interest_snapshot" function';
2930 CREATE FUNCTION "create_interest_snapshot"
2931 ( "issue_id_p" "issue"."id"%TYPE )
2932 RETURNS VOID
2933 LANGUAGE 'plpgsql' VOLATILE AS $$
2934 DECLARE
2935 "member_id_v" "member"."id"%TYPE;
2936 BEGIN
2937 DELETE FROM "direct_interest_snapshot"
2938 WHERE "issue_id" = "issue_id_p"
2939 AND "event" = 'periodic';
2940 DELETE FROM "delegating_interest_snapshot"
2941 WHERE "issue_id" = "issue_id_p"
2942 AND "event" = 'periodic';
2943 DELETE FROM "direct_supporter_snapshot"
2944 WHERE "issue_id" = "issue_id_p"
2945 AND "event" = 'periodic';
2946 INSERT INTO "direct_interest_snapshot"
2947 ("issue_id", "event", "member_id")
2948 SELECT
2949 "issue_id_p" AS "issue_id",
2950 'periodic' AS "event",
2951 "member"."id" AS "member_id"
2952 FROM "issue"
2953 JOIN "area" ON "issue"."area_id" = "area"."id"
2954 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2955 JOIN "member" ON "interest"."member_id" = "member"."id"
2956 JOIN "privilege"
2957 ON "privilege"."unit_id" = "area"."unit_id"
2958 AND "privilege"."member_id" = "member"."id"
2959 WHERE "issue"."id" = "issue_id_p"
2960 AND "member"."active" AND "privilege"."voting_right";
2961 FOR "member_id_v" IN
2962 SELECT "member_id" FROM "direct_interest_snapshot"
2963 WHERE "issue_id" = "issue_id_p"
2964 AND "event" = 'periodic'
2965 LOOP
2966 UPDATE "direct_interest_snapshot" SET
2967 "weight" = 1 +
2968 "weight_of_added_delegations_for_interest_snapshot"(
2969 "issue_id_p",
2970 "member_id_v",
2971 '{}'
2972 )
2973 WHERE "issue_id" = "issue_id_p"
2974 AND "event" = 'periodic'
2975 AND "member_id" = "member_id_v";
2976 END LOOP;
2977 INSERT INTO "direct_supporter_snapshot"
2978 ( "issue_id", "initiative_id", "event", "member_id",
2979 "informed", "satisfied" )
2980 SELECT
2981 "issue_id_p" AS "issue_id",
2982 "initiative"."id" AS "initiative_id",
2983 'periodic' AS "event",
2984 "supporter"."member_id" AS "member_id",
2985 "supporter"."draft_id" = "current_draft"."id" AS "informed",
2986 NOT EXISTS (
2987 SELECT NULL FROM "critical_opinion"
2988 WHERE "initiative_id" = "initiative"."id"
2989 AND "member_id" = "supporter"."member_id"
2990 ) AS "satisfied"
2991 FROM "initiative"
2992 JOIN "supporter"
2993 ON "supporter"."initiative_id" = "initiative"."id"
2994 JOIN "current_draft"
2995 ON "initiative"."id" = "current_draft"."initiative_id"
2996 JOIN "direct_interest_snapshot"
2997 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
2998 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
2999 AND "event" = 'periodic'
3000 WHERE "initiative"."issue_id" = "issue_id_p";
3001 RETURN;
3002 END;
3003 $$;
3005 COMMENT ON FUNCTION "create_interest_snapshot"
3006 ( "issue"."id"%TYPE )
3007 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.';
3010 CREATE FUNCTION "create_snapshot"
3011 ( "issue_id_p" "issue"."id"%TYPE )
3012 RETURNS VOID
3013 LANGUAGE 'plpgsql' VOLATILE AS $$
3014 DECLARE
3015 "initiative_id_v" "initiative"."id"%TYPE;
3016 "suggestion_id_v" "suggestion"."id"%TYPE;
3017 BEGIN
3018 PERFORM "lock_issue"("issue_id_p");
3019 PERFORM "create_population_snapshot"("issue_id_p");
3020 PERFORM "create_interest_snapshot"("issue_id_p");
3021 UPDATE "issue" SET
3022 "snapshot" = now(),
3023 "latest_snapshot_event" = 'periodic',
3024 "population" = (
3025 SELECT coalesce(sum("weight"), 0)
3026 FROM "direct_population_snapshot"
3027 WHERE "issue_id" = "issue_id_p"
3028 AND "event" = 'periodic'
3029 )
3030 WHERE "id" = "issue_id_p";
3031 FOR "initiative_id_v" IN
3032 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
3033 LOOP
3034 UPDATE "initiative" SET
3035 "supporter_count" = (
3036 SELECT coalesce(sum("di"."weight"), 0)
3037 FROM "direct_interest_snapshot" AS "di"
3038 JOIN "direct_supporter_snapshot" AS "ds"
3039 ON "di"."member_id" = "ds"."member_id"
3040 WHERE "di"."issue_id" = "issue_id_p"
3041 AND "di"."event" = 'periodic'
3042 AND "ds"."initiative_id" = "initiative_id_v"
3043 AND "ds"."event" = 'periodic'
3044 ),
3045 "informed_supporter_count" = (
3046 SELECT coalesce(sum("di"."weight"), 0)
3047 FROM "direct_interest_snapshot" AS "di"
3048 JOIN "direct_supporter_snapshot" AS "ds"
3049 ON "di"."member_id" = "ds"."member_id"
3050 WHERE "di"."issue_id" = "issue_id_p"
3051 AND "di"."event" = 'periodic'
3052 AND "ds"."initiative_id" = "initiative_id_v"
3053 AND "ds"."event" = 'periodic'
3054 AND "ds"."informed"
3055 ),
3056 "satisfied_supporter_count" = (
3057 SELECT coalesce(sum("di"."weight"), 0)
3058 FROM "direct_interest_snapshot" AS "di"
3059 JOIN "direct_supporter_snapshot" AS "ds"
3060 ON "di"."member_id" = "ds"."member_id"
3061 WHERE "di"."issue_id" = "issue_id_p"
3062 AND "di"."event" = 'periodic'
3063 AND "ds"."initiative_id" = "initiative_id_v"
3064 AND "ds"."event" = 'periodic'
3065 AND "ds"."satisfied"
3066 ),
3067 "satisfied_informed_supporter_count" = (
3068 SELECT coalesce(sum("di"."weight"), 0)
3069 FROM "direct_interest_snapshot" AS "di"
3070 JOIN "direct_supporter_snapshot" AS "ds"
3071 ON "di"."member_id" = "ds"."member_id"
3072 WHERE "di"."issue_id" = "issue_id_p"
3073 AND "di"."event" = 'periodic'
3074 AND "ds"."initiative_id" = "initiative_id_v"
3075 AND "ds"."event" = 'periodic'
3076 AND "ds"."informed"
3077 AND "ds"."satisfied"
3078 )
3079 WHERE "id" = "initiative_id_v";
3080 FOR "suggestion_id_v" IN
3081 SELECT "id" FROM "suggestion"
3082 WHERE "initiative_id" = "initiative_id_v"
3083 LOOP
3084 UPDATE "suggestion" SET
3085 "minus2_unfulfilled_count" = (
3086 SELECT coalesce(sum("snapshot"."weight"), 0)
3087 FROM "issue" CROSS JOIN "opinion"
3088 JOIN "direct_interest_snapshot" AS "snapshot"
3089 ON "snapshot"."issue_id" = "issue"."id"
3090 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3091 AND "snapshot"."member_id" = "opinion"."member_id"
3092 WHERE "issue"."id" = "issue_id_p"
3093 AND "opinion"."suggestion_id" = "suggestion_id_v"
3094 AND "opinion"."degree" = -2
3095 AND "opinion"."fulfilled" = FALSE
3096 ),
3097 "minus2_fulfilled_count" = (
3098 SELECT coalesce(sum("snapshot"."weight"), 0)
3099 FROM "issue" CROSS JOIN "opinion"
3100 JOIN "direct_interest_snapshot" AS "snapshot"
3101 ON "snapshot"."issue_id" = "issue"."id"
3102 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3103 AND "snapshot"."member_id" = "opinion"."member_id"
3104 WHERE "issue"."id" = "issue_id_p"
3105 AND "opinion"."suggestion_id" = "suggestion_id_v"
3106 AND "opinion"."degree" = -2
3107 AND "opinion"."fulfilled" = TRUE
3108 ),
3109 "minus1_unfulfilled_count" = (
3110 SELECT coalesce(sum("snapshot"."weight"), 0)
3111 FROM "issue" CROSS JOIN "opinion"
3112 JOIN "direct_interest_snapshot" AS "snapshot"
3113 ON "snapshot"."issue_id" = "issue"."id"
3114 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3115 AND "snapshot"."member_id" = "opinion"."member_id"
3116 WHERE "issue"."id" = "issue_id_p"
3117 AND "opinion"."suggestion_id" = "suggestion_id_v"
3118 AND "opinion"."degree" = -1
3119 AND "opinion"."fulfilled" = FALSE
3120 ),
3121 "minus1_fulfilled_count" = (
3122 SELECT coalesce(sum("snapshot"."weight"), 0)
3123 FROM "issue" CROSS JOIN "opinion"
3124 JOIN "direct_interest_snapshot" AS "snapshot"
3125 ON "snapshot"."issue_id" = "issue"."id"
3126 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3127 AND "snapshot"."member_id" = "opinion"."member_id"
3128 WHERE "issue"."id" = "issue_id_p"
3129 AND "opinion"."suggestion_id" = "suggestion_id_v"
3130 AND "opinion"."degree" = -1
3131 AND "opinion"."fulfilled" = TRUE
3132 ),
3133 "plus1_unfulfilled_count" = (
3134 SELECT coalesce(sum("snapshot"."weight"), 0)
3135 FROM "issue" CROSS JOIN "opinion"
3136 JOIN "direct_interest_snapshot" AS "snapshot"
3137 ON "snapshot"."issue_id" = "issue"."id"
3138 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3139 AND "snapshot"."member_id" = "opinion"."member_id"
3140 WHERE "issue"."id" = "issue_id_p"
3141 AND "opinion"."suggestion_id" = "suggestion_id_v"
3142 AND "opinion"."degree" = 1
3143 AND "opinion"."fulfilled" = FALSE
3144 ),
3145 "plus1_fulfilled_count" = (
3146 SELECT coalesce(sum("snapshot"."weight"), 0)
3147 FROM "issue" CROSS JOIN "opinion"
3148 JOIN "direct_interest_snapshot" AS "snapshot"
3149 ON "snapshot"."issue_id" = "issue"."id"
3150 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3151 AND "snapshot"."member_id" = "opinion"."member_id"
3152 WHERE "issue"."id" = "issue_id_p"
3153 AND "opinion"."suggestion_id" = "suggestion_id_v"
3154 AND "opinion"."degree" = 1
3155 AND "opinion"."fulfilled" = TRUE
3156 ),
3157 "plus2_unfulfilled_count" = (
3158 SELECT coalesce(sum("snapshot"."weight"), 0)
3159 FROM "issue" CROSS JOIN "opinion"
3160 JOIN "direct_interest_snapshot" AS "snapshot"
3161 ON "snapshot"."issue_id" = "issue"."id"
3162 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3163 AND "snapshot"."member_id" = "opinion"."member_id"
3164 WHERE "issue"."id" = "issue_id_p"
3165 AND "opinion"."suggestion_id" = "suggestion_id_v"
3166 AND "opinion"."degree" = 2
3167 AND "opinion"."fulfilled" = FALSE
3168 ),
3169 "plus2_fulfilled_count" = (
3170 SELECT coalesce(sum("snapshot"."weight"), 0)
3171 FROM "issue" CROSS JOIN "opinion"
3172 JOIN "direct_interest_snapshot" AS "snapshot"
3173 ON "snapshot"."issue_id" = "issue"."id"
3174 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3175 AND "snapshot"."member_id" = "opinion"."member_id"
3176 WHERE "issue"."id" = "issue_id_p"
3177 AND "opinion"."suggestion_id" = "suggestion_id_v"
3178 AND "opinion"."degree" = 2
3179 AND "opinion"."fulfilled" = TRUE
3180 )
3181 WHERE "suggestion"."id" = "suggestion_id_v";
3182 END LOOP;
3183 END LOOP;
3184 RETURN;
3185 END;
3186 $$;
3188 COMMENT ON FUNCTION "create_snapshot"
3189 ( "issue"."id"%TYPE )
3190 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.';
3193 CREATE FUNCTION "set_snapshot_event"
3194 ( "issue_id_p" "issue"."id"%TYPE,
3195 "event_p" "snapshot_event" )
3196 RETURNS VOID
3197 LANGUAGE 'plpgsql' VOLATILE AS $$
3198 DECLARE
3199 "event_v" "issue"."latest_snapshot_event"%TYPE;
3200 BEGIN
3201 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
3202 WHERE "id" = "issue_id_p" FOR UPDATE;
3203 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
3204 WHERE "id" = "issue_id_p";
3205 UPDATE "direct_population_snapshot" SET "event" = "event_p"
3206 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3207 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
3208 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3209 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
3210 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3211 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
3212 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3213 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
3214 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3215 RETURN;
3216 END;
3217 $$;
3219 COMMENT ON FUNCTION "set_snapshot_event"
3220 ( "issue"."id"%TYPE,
3221 "snapshot_event" )
3222 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
3226 ---------------------
3227 -- Freezing issues --
3228 ---------------------
3230 CREATE FUNCTION "freeze_after_snapshot"
3231 ( "issue_id_p" "issue"."id"%TYPE )
3232 RETURNS VOID
3233 LANGUAGE 'plpgsql' VOLATILE AS $$
3234 DECLARE
3235 "issue_row" "issue"%ROWTYPE;
3236 "policy_row" "policy"%ROWTYPE;
3237 "initiative_row" "initiative"%ROWTYPE;
3238 BEGIN
3239 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3240 SELECT * INTO "policy_row"
3241 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3242 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
3243 FOR "initiative_row" IN
3244 SELECT * FROM "initiative"
3245 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3246 LOOP
3247 IF
3248 "initiative_row"."satisfied_supporter_count" > 0 AND
3249 "initiative_row"."satisfied_supporter_count" *
3250 "policy_row"."initiative_quorum_den" >=
3251 "issue_row"."population" * "policy_row"."initiative_quorum_num"
3252 THEN
3253 UPDATE "initiative" SET "admitted" = TRUE
3254 WHERE "id" = "initiative_row"."id";
3255 ELSE
3256 UPDATE "initiative" SET "admitted" = FALSE
3257 WHERE "id" = "initiative_row"."id";
3258 END IF;
3259 END LOOP;
3260 IF EXISTS (
3261 SELECT NULL FROM "initiative"
3262 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
3263 ) THEN
3264 UPDATE "issue" SET
3265 "state" = 'voting',
3266 "accepted" = coalesce("accepted", now()),
3267 "half_frozen" = coalesce("half_frozen", now()),
3268 "fully_frozen" = now()
3269 WHERE "id" = "issue_id_p";
3270 ELSE
3271 UPDATE "issue" SET
3272 "state" = 'canceled_no_initiative_admitted',
3273 "accepted" = coalesce("accepted", now()),
3274 "half_frozen" = coalesce("half_frozen", now()),
3275 "fully_frozen" = now(),
3276 "closed" = now(),
3277 "ranks_available" = TRUE
3278 WHERE "id" = "issue_id_p";
3279 -- NOTE: The following DELETE statements have effect only when
3280 -- issue state has been manipulated
3281 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
3282 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
3283 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3284 END IF;
3285 RETURN;
3286 END;
3287 $$;
3289 COMMENT ON FUNCTION "freeze_after_snapshot"
3290 ( "issue"."id"%TYPE )
3291 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
3294 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
3295 RETURNS VOID
3296 LANGUAGE 'plpgsql' VOLATILE AS $$
3297 DECLARE
3298 "issue_row" "issue"%ROWTYPE;
3299 BEGIN
3300 PERFORM "create_snapshot"("issue_id_p");
3301 PERFORM "freeze_after_snapshot"("issue_id_p");
3302 RETURN;
3303 END;
3304 $$;
3306 COMMENT ON FUNCTION "manual_freeze"
3307 ( "issue"."id"%TYPE )
3308 IS 'Freeze an issue manually (fully) and start voting';
3312 -----------------------
3313 -- Counting of votes --
3314 -----------------------
3317 CREATE FUNCTION "weight_of_added_vote_delegations"
3318 ( "issue_id_p" "issue"."id"%TYPE,
3319 "member_id_p" "member"."id"%TYPE,
3320 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
3321 RETURNS "direct_voter"."weight"%TYPE
3322 LANGUAGE 'plpgsql' VOLATILE AS $$
3323 DECLARE
3324 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3325 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
3326 "weight_v" INT4;
3327 "sub_weight_v" INT4;
3328 BEGIN
3329 "weight_v" := 0;
3330 FOR "issue_delegation_row" IN
3331 SELECT * FROM "issue_delegation"
3332 WHERE "trustee_id" = "member_id_p"
3333 AND "issue_id" = "issue_id_p"
3334 LOOP
3335 IF NOT EXISTS (
3336 SELECT NULL FROM "direct_voter"
3337 WHERE "member_id" = "issue_delegation_row"."truster_id"
3338 AND "issue_id" = "issue_id_p"
3339 ) AND NOT EXISTS (
3340 SELECT NULL FROM "delegating_voter"
3341 WHERE "member_id" = "issue_delegation_row"."truster_id"
3342 AND "issue_id" = "issue_id_p"
3343 ) THEN
3344 "delegate_member_ids_v" :=
3345 "member_id_p" || "delegate_member_ids_p";
3346 INSERT INTO "delegating_voter" (
3347 "issue_id",
3348 "member_id",
3349 "scope",
3350 "delegate_member_ids"
3351 ) VALUES (
3352 "issue_id_p",
3353 "issue_delegation_row"."truster_id",
3354 "issue_delegation_row"."scope",
3355 "delegate_member_ids_v"
3356 );
3357 "sub_weight_v" := 1 +
3358 "weight_of_added_vote_delegations"(
3359 "issue_id_p",
3360 "issue_delegation_row"."truster_id",
3361 "delegate_member_ids_v"
3362 );
3363 UPDATE "delegating_voter"
3364 SET "weight" = "sub_weight_v"
3365 WHERE "issue_id" = "issue_id_p"
3366 AND "member_id" = "issue_delegation_row"."truster_id";
3367 "weight_v" := "weight_v" + "sub_weight_v";
3368 END IF;
3369 END LOOP;
3370 RETURN "weight_v";
3371 END;
3372 $$;
3374 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
3375 ( "issue"."id"%TYPE,
3376 "member"."id"%TYPE,
3377 "delegating_voter"."delegate_member_ids"%TYPE )
3378 IS 'Helper function for "add_vote_delegations" function';
3381 CREATE FUNCTION "add_vote_delegations"
3382 ( "issue_id_p" "issue"."id"%TYPE )
3383 RETURNS VOID
3384 LANGUAGE 'plpgsql' VOLATILE AS $$
3385 DECLARE
3386 "member_id_v" "member"."id"%TYPE;
3387 BEGIN
3388 FOR "member_id_v" IN
3389 SELECT "member_id" FROM "direct_voter"
3390 WHERE "issue_id" = "issue_id_p"
3391 LOOP
3392 UPDATE "direct_voter" SET
3393 "weight" = "weight" + "weight_of_added_vote_delegations"(
3394 "issue_id_p",
3395 "member_id_v",
3396 '{}'
3397 )
3398 WHERE "member_id" = "member_id_v"
3399 AND "issue_id" = "issue_id_p";
3400 END LOOP;
3401 RETURN;
3402 END;
3403 $$;
3405 COMMENT ON FUNCTION "add_vote_delegations"
3406 ( "issue_id_p" "issue"."id"%TYPE )
3407 IS 'Helper function for "close_voting" function';
3410 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
3411 RETURNS VOID
3412 LANGUAGE 'plpgsql' VOLATILE AS $$
3413 DECLARE
3414 "area_id_v" "area"."id"%TYPE;
3415 "unit_id_v" "unit"."id"%TYPE;
3416 "member_id_v" "member"."id"%TYPE;
3417 BEGIN
3418 PERFORM "lock_issue"("issue_id_p");
3419 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
3420 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
3421 -- consider delegations and auto-reject:
3422 DELETE FROM "delegating_voter"
3423 WHERE "issue_id" = "issue_id_p";
3424 DELETE FROM "direct_voter"
3425 WHERE "issue_id" = "issue_id_p"
3426 AND "autoreject" = TRUE;
3427 DELETE FROM "direct_voter"
3428 USING (
3429 SELECT
3430 "direct_voter"."member_id"
3431 FROM "direct_voter"
3432 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
3433 LEFT JOIN "privilege"
3434 ON "privilege"."unit_id" = "unit_id_v"
3435 AND "privilege"."member_id" = "direct_voter"."member_id"
3436 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
3437 "member"."active" = FALSE OR
3438 "privilege"."voting_right" ISNULL OR
3439 "privilege"."voting_right" = FALSE
3440 )
3441 ) AS "subquery"
3442 WHERE "direct_voter"."issue_id" = "issue_id_p"
3443 AND "direct_voter"."member_id" = "subquery"."member_id";
3444 UPDATE "direct_voter" SET "weight" = 1
3445 WHERE "issue_id" = "issue_id_p";
3446 PERFORM "add_vote_delegations"("issue_id_p");
3447 FOR "member_id_v" IN
3448 SELECT "interest"."member_id"
3449 FROM "interest"
3450 JOIN "member"
3451 ON "interest"."member_id" = "member"."id"
3452 JOIN "privilege"
3453 ON "privilege"."unit_id" = "unit_id_v"
3454 AND "privilege"."member_id" = "member"."id"
3455 LEFT JOIN "direct_voter"
3456 ON "interest"."member_id" = "direct_voter"."member_id"
3457 AND "interest"."issue_id" = "direct_voter"."issue_id"
3458 LEFT JOIN "delegating_voter"
3459 ON "interest"."member_id" = "delegating_voter"."member_id"
3460 AND "interest"."issue_id" = "delegating_voter"."issue_id"
3461 WHERE "interest"."issue_id" = "issue_id_p"
3462 AND "interest"."autoreject" = TRUE
3463 AND "member"."active"
3464 AND "privilege"."voting_right"
3465 AND "direct_voter"."member_id" ISNULL
3466 AND "delegating_voter"."member_id" ISNULL
3467 UNION SELECT "membership"."member_id"
3468 FROM "membership"
3469 JOIN "member"
3470 ON "membership"."member_id" = "member"."id"
3471 JOIN "privilege"
3472 ON "privilege"."unit_id" = "unit_id_v"
3473 AND "privilege"."member_id" = "member"."id"
3474 LEFT JOIN "interest"
3475 ON "membership"."member_id" = "interest"."member_id"
3476 AND "interest"."issue_id" = "issue_id_p"
3477 LEFT JOIN "direct_voter"
3478 ON "membership"."member_id" = "direct_voter"."member_id"
3479 AND "direct_voter"."issue_id" = "issue_id_p"
3480 LEFT JOIN "delegating_voter"
3481 ON "membership"."member_id" = "delegating_voter"."member_id"
3482 AND "delegating_voter"."issue_id" = "issue_id_p"
3483 WHERE "membership"."area_id" = "area_id_v"
3484 AND "membership"."autoreject" = TRUE
3485 AND "member"."active"
3486 AND "privilege"."voting_right"
3487 AND "interest"."autoreject" ISNULL
3488 AND "direct_voter"."member_id" ISNULL
3489 AND "delegating_voter"."member_id" ISNULL
3490 LOOP
3491 INSERT INTO "direct_voter"
3492 ("member_id", "issue_id", "weight", "autoreject") VALUES
3493 ("member_id_v", "issue_id_p", 1, TRUE);
3494 INSERT INTO "vote" (
3495 "member_id",
3496 "issue_id",
3497 "initiative_id",
3498 "grade"
3499 ) SELECT
3500 "member_id_v" AS "member_id",
3501 "issue_id_p" AS "issue_id",
3502 "id" AS "initiative_id",
3503 -1 AS "grade"
3504 FROM "initiative" WHERE "issue_id" = "issue_id_p";
3505 END LOOP;
3506 PERFORM "add_vote_delegations"("issue_id_p");
3507 -- set voter count and mark issue as being calculated:
3508 UPDATE "issue" SET
3509 "state" = 'calculation',
3510 "closed" = now(),
3511 "voter_count" = (
3512 SELECT coalesce(sum("weight"), 0)
3513 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
3514 )
3515 WHERE "id" = "issue_id_p";
3516 -- calculate "positive_votes", "negative_votes" and "attainable":
3517 UPDATE "initiative" SET
3518 "positive_votes" = "vote_counts"."positive_votes",
3519 "negative_votes" = "vote_counts"."negative_votes",
3520 "attainable" =
3521 CASE WHEN "majority_strict" THEN
3522 "vote_counts"."positive_votes" * "majority_den" >
3523 "majority_num" *
3524 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
3525 ELSE
3526 "vote_counts"."positive_votes" * "majority_den" >=
3527 "majority_num" *
3528 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
3529 END
3530 AND "vote_counts"."positive_votes" >= "policy"."majority_positive"
3531 AND "issue"."voter_count"-"vote_counts"."negative_votes" >=
3532 "policy"."majority_non_negative"
3533 FROM
3534 ( SELECT
3535 "initiative"."id" AS "initiative_id",
3536 coalesce(
3537 sum(
3538 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
3539 ),
3540 0
3541 ) AS "positive_votes",
3542 coalesce(
3543 sum(
3544 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
3545 ),
3546 0
3547 ) AS "negative_votes"
3548 FROM "initiative"
3549 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
3550 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
3551 LEFT JOIN "direct_voter"
3552 ON "direct_voter"."issue_id" = "initiative"."issue_id"
3553 LEFT JOIN "vote"
3554 ON "vote"."initiative_id" = "initiative"."id"
3555 AND "vote"."member_id" = "direct_voter"."member_id"
3556 WHERE "initiative"."issue_id" = "issue_id_p"
3557 AND "initiative"."admitted" -- NOTE: NULL case is handled too
3558 GROUP BY "initiative"."id"
3559 ) AS "vote_counts",
3560 "issue",
3561 "policy"
3562 WHERE "vote_counts"."initiative_id" = "initiative"."id"
3563 AND "issue"."id" = "initiative"."issue_id"
3564 AND "policy"."id" = "issue"."policy_id";
3565 -- materialize battle_view:
3566 -- NOTE: "closed" column of issue must be set at this point
3567 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3568 INSERT INTO "battle" (
3569 "issue_id",
3570 "winning_initiative_id", "losing_initiative_id",
3571 "count"
3572 ) SELECT
3573 "issue_id",
3574 "winning_initiative_id", "losing_initiative_id",
3575 "count"
3576 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
3577 END;
3578 $$;
3580 COMMENT ON FUNCTION "close_voting"
3581 ( "issue"."id"%TYPE )
3582 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.';
3585 CREATE FUNCTION "defeat_strength"
3586 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
3587 RETURNS INT8
3588 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3589 BEGIN
3590 IF "positive_votes_p" > "negative_votes_p" THEN
3591 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
3592 ELSIF "positive_votes_p" = "negative_votes_p" THEN
3593 RETURN 0;
3594 ELSE
3595 RETURN -1;
3596 END IF;
3597 END;
3598 $$;
3600 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';
3603 CREATE FUNCTION "array_init_string"("dim_p" INTEGER)
3604 RETURNS TEXT
3605 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3606 DECLARE
3607 "i" INTEGER;
3608 "ary_text_v" TEXT;
3609 BEGIN
3610 IF "dim_p" >= 1 THEN
3611 "ary_text_v" := '{NULL';
3612 "i" := "dim_p";
3613 LOOP
3614 "i" := "i" - 1;
3615 EXIT WHEN "i" = 0;
3616 "ary_text_v" := "ary_text_v" || ',NULL';
3617 END LOOP;
3618 "ary_text_v" := "ary_text_v" || '}';
3619 RETURN "ary_text_v";
3620 ELSE
3621 RAISE EXCEPTION 'Dimension needs to be at least 1.';
3622 END IF;
3623 END;
3624 $$;
3626 COMMENT ON FUNCTION "array_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
3629 CREATE FUNCTION "square_matrix_init_string"("dim_p" INTEGER)
3630 RETURNS TEXT
3631 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3632 DECLARE
3633 "i" INTEGER;
3634 "row_text_v" TEXT;
3635 "ary_text_v" TEXT;
3636 BEGIN
3637 IF "dim_p" >= 1 THEN
3638 "row_text_v" := '{NULL';
3639 "i" := "dim_p";
3640 LOOP
3641 "i" := "i" - 1;
3642 EXIT WHEN "i" = 0;
3643 "row_text_v" := "row_text_v" || ',NULL';
3644 END LOOP;
3645 "row_text_v" := "row_text_v" || '}';
3646 "ary_text_v" := '{' || "row_text_v";
3647 "i" := "dim_p";
3648 LOOP
3649 "i" := "i" - 1;
3650 EXIT WHEN "i" = 0;
3651 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
3652 END LOOP;
3653 "ary_text_v" := "ary_text_v" || '}';
3654 RETURN "ary_text_v";
3655 ELSE
3656 RAISE EXCEPTION 'Dimension needs to be at least 1.';
3657 END IF;
3658 END;
3659 $$;
3661 COMMENT ON FUNCTION "square_matrix_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
3664 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
3665 RETURNS VOID
3666 LANGUAGE 'plpgsql' VOLATILE AS $$
3667 DECLARE
3668 "dimension_v" INTEGER;
3669 "vote_matrix" INT4[][]; -- absolute votes
3670 "matrix" INT8[][]; -- defeat strength / best paths
3671 "i" INTEGER;
3672 "j" INTEGER;
3673 "k" INTEGER;
3674 "battle_row" "battle"%ROWTYPE;
3675 "rank_ary" INT4[];
3676 "rank_v" INT4;
3677 "done_v" INTEGER;
3678 "winners_ary" INTEGER[];
3679 "initiative_id_v" "initiative"."id"%TYPE;
3680 BEGIN
3681 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
3682 SELECT count(1) INTO "dimension_v"
3683 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
3684 IF "dimension_v" > 1 THEN
3685 -- Create "vote_matrix" with absolute number of votes in pairwise
3686 -- comparison:
3687 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
3688 "i" := 1;
3689 "j" := 2;
3690 FOR "battle_row" IN
3691 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
3692 ORDER BY
3693 "winning_initiative_id" NULLS LAST,
3694 "losing_initiative_id" NULLS LAST
3695 LOOP
3696 "vote_matrix"["i"]["j"] := "battle_row"."count";
3697 IF "j" = "dimension_v" THEN
3698 "i" := "i" + 1;
3699 "j" := 1;
3700 ELSE
3701 "j" := "j" + 1;
3702 IF "j" = "i" THEN
3703 "j" := "j" + 1;
3704 END IF;
3705 END IF;
3706 END LOOP;
3707 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
3708 RAISE EXCEPTION 'Wrong battle count (should not happen)';
3709 END IF;
3710 -- Store defeat strengths in "matrix" using "defeat_strength"
3711 -- function:
3712 "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
3713 "i" := 1;
3714 LOOP
3715 "j" := 1;
3716 LOOP
3717 IF "i" != "j" THEN
3718 "matrix"["i"]["j"] := "defeat_strength"(
3719 "vote_matrix"["i"]["j"],
3720 "vote_matrix"["j"]["i"]
3721 );
3722 END IF;
3723 EXIT WHEN "j" = "dimension_v";
3724 "j" := "j" + 1;
3725 END LOOP;
3726 EXIT WHEN "i" = "dimension_v";
3727 "i" := "i" + 1;
3728 END LOOP;
3729 -- Find best paths:
3730 "i" := 1;
3731 LOOP
3732 "j" := 1;
3733 LOOP
3734 IF "i" != "j" THEN
3735 "k" := 1;
3736 LOOP
3737 IF "i" != "k" AND "j" != "k" THEN
3738 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
3739 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
3740 "matrix"["j"]["k"] := "matrix"["j"]["i"];
3741 END IF;
3742 ELSE
3743 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
3744 "matrix"["j"]["k"] := "matrix"["i"]["k"];
3745 END IF;
3746 END IF;
3747 END IF;
3748 EXIT WHEN "k" = "dimension_v";
3749 "k" := "k" + 1;
3750 END LOOP;
3751 END IF;
3752 EXIT WHEN "j" = "dimension_v";
3753 "j" := "j" + 1;
3754 END LOOP;
3755 EXIT WHEN "i" = "dimension_v";
3756 "i" := "i" + 1;
3757 END LOOP;
3758 -- Determine order of winners:
3759 "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
3760 "rank_v" := 1;
3761 "done_v" := 0;
3762 LOOP
3763 "winners_ary" := '{}';
3764 "i" := 1;
3765 LOOP
3766 IF "rank_ary"["i"] ISNULL THEN
3767 "j" := 1;
3768 LOOP
3769 IF
3770 "i" != "j" AND
3771 "rank_ary"["j"] ISNULL AND
3772 "matrix"["j"]["i"] > "matrix"["i"]["j"]
3773 THEN
3774 -- someone else is better
3775 EXIT;
3776 END IF;
3777 IF "j" = "dimension_v" THEN
3778 -- noone is better
3779 "winners_ary" := "winners_ary" || "i";
3780 EXIT;
3781 END IF;
3782 "j" := "j" + 1;
3783 END LOOP;
3784 END IF;
3785 EXIT WHEN "i" = "dimension_v";
3786 "i" := "i" + 1;
3787 END LOOP;
3788 "i" := 1;
3789 LOOP
3790 "rank_ary"["winners_ary"["i"]] := "rank_v";
3791 "done_v" := "done_v" + 1;
3792 EXIT WHEN "i" = array_upper("winners_ary", 1);
3793 "i" := "i" + 1;
3794 END LOOP;
3795 EXIT WHEN "done_v" = "dimension_v";
3796 "rank_v" := "rank_v" + 1;
3797 END LOOP;
3798 -- write preliminary results:
3799 "i" := 1;
3800 FOR "initiative_id_v" IN
3801 SELECT "id" FROM "initiative"
3802 WHERE "issue_id" = "issue_id_p" AND "admitted"
3803 ORDER BY "id"
3804 LOOP
3805 UPDATE "initiative" SET
3806 "favored" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
3807 "unfavored" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
3808 "preliminary_rank" = "rank_ary"["i"],
3809 "disqualified" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
3810 "winner" = FALSE
3811 WHERE "id" = "initiative_id_v";
3812 "i" := "i" + 1;
3813 END LOOP;
3814 IF "i" != "dimension_v" THEN
3815 RAISE EXCEPTION 'Wrong winner count (should not happen)';
3816 END IF;
3817 -- remove possible gap in preliminary ranks:
3818 IF NOT EXISTS (
3819 SELECT NULL FROM "initiative"
3820 WHERE "issue_id" = "issue_id_p"
3821 AND "favored"=FALSE AND "unfavored"=FALSE
3822 ) THEN
3823 UPDATE "initiative" SET "preliminary_rank" = "preliminary_rank" - 1
3824 WHERE "issue_id" = "issue_id_p" AND "unfavored";
3825 END IF;
3826 -- disqualify certain initiatives to enforce a stable result:
3827 UPDATE "initiative" SET "disqualified" = TRUE
3828 FROM (
3829 SELECT "losing_initiative"."id" AS "initiative_id"
3830 FROM "initiative" "losing_initiative"
3831 JOIN "initiative" "winning_initiative"
3832 ON "winning_initiative"."issue_id" = "issue_id_p"
3833 AND "winning_initiative"."admitted"
3834 JOIN "battle" "battle_win"
3835 ON "battle_win"."issue_id" = "issue_id_p"
3836 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
3837 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
3838 JOIN "battle" "battle_lose"
3839 ON "battle_lose"."issue_id" = "issue_id_p"
3840 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
3841 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
3842 WHERE "losing_initiative"."issue_id" = "issue_id_p"
3843 AND "losing_initiative"."admitted"
3844 AND "winning_initiative"."preliminary_rank" <
3845 "losing_initiative"."preliminary_rank"
3846 AND "battle_win"."count" > "battle_lose"."count"
3847 AND (
3848 "battle_win"."count" > "winning_initiative"."positive_votes" OR
3849 "battle_lose"."count" < "losing_initiative"."negative_votes" )
3850 ) AS "subquery"
3851 WHERE "id" = "subquery"."initiative_id";
3852 -- calculate final ranks (start counting with 1, no equal ranks):
3853 "rank_v" := 1;
3854 FOR "initiative_id_v" IN
3855 SELECT "id" FROM "initiative"
3856 WHERE "issue_id" = "issue_id_p" AND "admitted"
3857 ORDER BY "preliminary_rank", "id"
3858 LOOP
3859 UPDATE "initiative" SET "final_rank" = "rank_v"
3860 WHERE "id" = "initiative_id_v";
3861 "rank_v" := "rank_v" + 1;
3862 END LOOP;
3863 -- mark final winner:
3864 UPDATE "initiative" SET "winner" = TRUE
3865 FROM (
3866 SELECT "id" AS "initiative_id"
3867 FROM "initiative"
3868 WHERE "issue_id" = "issue_id_p"
3869 AND "attainable" AND NOT "disqualified"
3870 ORDER BY "final_rank"
3871 LIMIT 1
3872 ) AS "subquery"
3873 WHERE "id" = "subquery"."initiative_id";
3874 END IF;
3875 -- mark issue as finished:
3876 UPDATE "issue" SET
3877 "state" =
3878 CASE WHEN EXISTS (
3879 SELECT NULL FROM "initiative"
3880 WHERE "issue_id" = "issue_id_p" AND "winner"
3881 ) THEN
3882 'finished_with_winner'::"issue_state"
3883 ELSE
3884 'finished_without_winner'::"issue_state"
3885 END,
3886 "ranks_available" = TRUE
3887 WHERE "id" = "issue_id_p";
3888 RETURN;
3889 END;
3890 $$;
3892 COMMENT ON FUNCTION "calculate_ranks"
3893 ( "issue"."id"%TYPE )
3894 IS 'Determine ranking (Votes have to be counted first)';
3898 -----------------------------
3899 -- Automatic state changes --
3900 -----------------------------
3903 CREATE FUNCTION "check_issue"
3904 ( "issue_id_p" "issue"."id"%TYPE )
3905 RETURNS VOID
3906 LANGUAGE 'plpgsql' VOLATILE AS $$
3907 DECLARE
3908 "issue_row" "issue"%ROWTYPE;
3909 "policy_row" "policy"%ROWTYPE;
3910 BEGIN
3911 PERFORM "lock_issue"("issue_id_p");
3912 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3913 -- only process open issues:
3914 IF "issue_row"."closed" ISNULL THEN
3915 SELECT * INTO "policy_row" FROM "policy"
3916 WHERE "id" = "issue_row"."policy_id";
3917 -- create a snapshot, unless issue is already fully frozen:
3918 IF "issue_row"."fully_frozen" ISNULL THEN
3919 PERFORM "create_snapshot"("issue_id_p");
3920 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3921 END IF;
3922 -- eventually close or accept issues, which have not been accepted:
3923 IF "issue_row"."accepted" ISNULL THEN
3924 IF EXISTS (
3925 SELECT NULL FROM "initiative"
3926 WHERE "issue_id" = "issue_id_p"
3927 AND "supporter_count" > 0
3928 AND "supporter_count" * "policy_row"."issue_quorum_den"
3929 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
3930 ) THEN
3931 -- accept issues, if supporter count is high enough
3932 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3933 -- NOTE: "issue_row" used later
3934 "issue_row"."state" := 'discussion';
3935 "issue_row"."accepted" := now();
3936 UPDATE "issue" SET
3937 "state" = "issue_row"."state",
3938 "accepted" = "issue_row"."accepted"
3939 WHERE "id" = "issue_row"."id";
3940 ELSIF
3941 now() >= "issue_row"."created" + "issue_row"."admission_time"
3942 THEN
3943 -- close issues, if admission time has expired
3944 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3945 UPDATE "issue" SET
3946 "state" = 'canceled_issue_not_accepted',
3947 "closed" = now()
3948 WHERE "id" = "issue_row"."id";
3949 END IF;
3950 END IF;
3951 -- eventually half freeze issues:
3952 IF
3953 -- NOTE: issue can't be closed at this point, if it has been accepted
3954 "issue_row"."accepted" NOTNULL AND
3955 "issue_row"."half_frozen" ISNULL
3956 THEN
3957 IF
3958 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
3959 THEN
3960 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
3961 -- NOTE: "issue_row" used later
3962 "issue_row"."state" := 'verification';
3963 "issue_row"."half_frozen" := now();
3964 UPDATE "issue" SET
3965 "state" = "issue_row"."state",
3966 "half_frozen" = "issue_row"."half_frozen"
3967 WHERE "id" = "issue_row"."id";
3968 END IF;
3969 END IF;
3970 -- close issues after some time, if all initiatives have been revoked:
3971 IF
3972 "issue_row"."closed" ISNULL AND
3973 NOT EXISTS (
3974 -- all initiatives are revoked
3975 SELECT NULL FROM "initiative"
3976 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3977 ) AND (
3978 -- and issue has not been accepted yet
3979 "issue_row"."accepted" ISNULL OR
3980 NOT EXISTS (
3981 -- or no initiatives have been revoked lately
3982 SELECT NULL FROM "initiative"
3983 WHERE "issue_id" = "issue_id_p"
3984 AND now() < "revoked" + "issue_row"."verification_time"
3985 ) OR (
3986 -- or verification time has elapsed
3987 "issue_row"."half_frozen" NOTNULL AND
3988 "issue_row"."fully_frozen" ISNULL AND
3989 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3990 )
3991 )
3992 THEN
3993 -- NOTE: "issue_row" used later
3994 IF "issue_row"."accepted" ISNULL THEN
3995 "issue_row"."state" := 'canceled_revoked_before_accepted';
3996 ELSIF "issue_row"."half_frozen" ISNULL THEN
3997 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
3998 ELSE
3999 "issue_row"."state" := 'canceled_after_revocation_during_verification';
4000 END IF;
4001 "issue_row"."closed" := now();
4002 UPDATE "issue" SET
4003 "state" = "issue_row"."state",
4004 "closed" = "issue_row"."closed"
4005 WHERE "id" = "issue_row"."id";
4006 END IF;
4007 -- fully freeze issue after verification time:
4008 IF
4009 "issue_row"."half_frozen" NOTNULL AND
4010 "issue_row"."fully_frozen" ISNULL AND
4011 "issue_row"."closed" ISNULL AND
4012 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
4013 THEN
4014 PERFORM "freeze_after_snapshot"("issue_id_p");
4015 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
4016 END IF;
4017 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4018 -- close issue by calling close_voting(...) after voting time:
4019 IF
4020 "issue_row"."closed" ISNULL AND
4021 "issue_row"."fully_frozen" NOTNULL AND
4022 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
4023 THEN
4024 PERFORM "close_voting"("issue_id_p");
4025 -- calculate ranks will not consume much time and can be done now
4026 PERFORM "calculate_ranks"("issue_id_p");
4027 END IF;
4028 END IF;
4029 RETURN;
4030 END;
4031 $$;
4033 COMMENT ON FUNCTION "check_issue"
4034 ( "issue"."id"%TYPE )
4035 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.';
4038 CREATE FUNCTION "check_everything"()
4039 RETURNS VOID
4040 LANGUAGE 'plpgsql' VOLATILE AS $$
4041 DECLARE
4042 "issue_id_v" "issue"."id"%TYPE;
4043 BEGIN
4044 DELETE FROM "expired_session";
4045 PERFORM "check_last_login"();
4046 PERFORM "calculate_member_counts"();
4047 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
4048 PERFORM "check_issue"("issue_id_v");
4049 END LOOP;
4050 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
4051 PERFORM "calculate_ranks"("issue_id_v");
4052 END LOOP;
4053 RETURN;
4054 END;
4055 $$;
4057 COMMENT ON FUNCTION "check_everything"() IS 'Amongst other regular tasks this function performs "check_issue" for every open issue, and if possible, automatically calculates ranks. Use this function only for development and debugging purposes, as long transactions with exclusive locking may result. In productive environments you should call the lf_update program instead.';
4061 ----------------------
4062 -- Deletion of data --
4063 ----------------------
4066 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
4067 RETURNS VOID
4068 LANGUAGE 'plpgsql' VOLATILE AS $$
4069 DECLARE
4070 "issue_row" "issue"%ROWTYPE;
4071 BEGIN
4072 SELECT * INTO "issue_row"
4073 FROM "issue" WHERE "id" = "issue_id_p"
4074 FOR UPDATE;
4075 IF "issue_row"."cleaned" ISNULL THEN
4076 -- TODO: might be broken due to new constraints!
4077 UPDATE "issue" SET
4078 "closed" = NULL,
4079 "ranks_available" = FALSE
4080 WHERE "id" = "issue_id_p";
4081 DELETE FROM "delegating_voter"
4082 WHERE "issue_id" = "issue_id_p";
4083 DELETE FROM "direct_voter"
4084 WHERE "issue_id" = "issue_id_p";
4085 DELETE FROM "delegating_interest_snapshot"
4086 WHERE "issue_id" = "issue_id_p";
4087 DELETE FROM "direct_interest_snapshot"
4088 WHERE "issue_id" = "issue_id_p";
4089 DELETE FROM "delegating_population_snapshot"
4090 WHERE "issue_id" = "issue_id_p";
4091 DELETE FROM "direct_population_snapshot"
4092 WHERE "issue_id" = "issue_id_p";
4093 DELETE FROM "non_voter"
4094 WHERE "issue_id" = "issue_id_p";
4095 DELETE FROM "delegation"
4096 WHERE "issue_id" = "issue_id_p";
4097 DELETE FROM "supporter"
4098 WHERE "issue_id" = "issue_id_p";
4099 UPDATE "issue" SET
4100 "closed" = "issue_row"."closed",
4101 "ranks_available" = "issue_row"."ranks_available",
4102 "cleaned" = now()
4103 WHERE "id" = "issue_id_p";
4104 END IF;
4105 RETURN;
4106 END;
4107 $$;
4109 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
4112 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
4113 RETURNS VOID
4114 LANGUAGE 'plpgsql' VOLATILE AS $$
4115 BEGIN
4116 UPDATE "member" SET
4117 "last_login" = NULL,
4118 "last_login_public" = NULL,
4119 "login" = NULL,
4120 "password" = NULL,
4121 "locked" = TRUE,
4122 "active" = FALSE,
4123 "notify_email" = NULL,
4124 "notify_email_unconfirmed" = NULL,
4125 "notify_email_secret" = NULL,
4126 "notify_email_secret_expiry" = NULL,
4127 "notify_email_lock_expiry" = NULL,
4128 "password_reset_secret" = NULL,
4129 "password_reset_secret_expiry" = NULL,
4130 "organizational_unit" = NULL,
4131 "internal_posts" = NULL,
4132 "realname" = NULL,
4133 "birthday" = NULL,
4134 "address" = NULL,
4135 "email" = NULL,
4136 "xmpp_address" = NULL,
4137 "website" = NULL,
4138 "phone" = NULL,
4139 "mobile_phone" = NULL,
4140 "profession" = NULL,
4141 "external_memberships" = NULL,
4142 "external_posts" = NULL,
4143 "statement" = NULL
4144 WHERE "id" = "member_id_p";
4145 -- "text_search_data" is updated by triggers
4146 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
4147 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
4148 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
4149 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
4150 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
4151 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
4152 DELETE FROM "session" WHERE "member_id" = "member_id_p";
4153 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
4154 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
4155 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
4156 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
4157 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
4158 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
4159 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
4160 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
4161 DELETE FROM "direct_voter" USING "issue"
4162 WHERE "direct_voter"."issue_id" = "issue"."id"
4163 AND "issue"."closed" ISNULL
4164 AND "member_id" = "member_id_p";
4165 RETURN;
4166 END;
4167 $$;
4169 COMMENT ON FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) IS 'Deactivate member and clear certain settings and data of this member (data protection)';
4172 CREATE FUNCTION "delete_private_data"()
4173 RETURNS VOID
4174 LANGUAGE 'plpgsql' VOLATILE AS $$
4175 BEGIN
4176 UPDATE "member" SET
4177 "last_login" = NULL,
4178 "login" = NULL,
4179 "password" = NULL,
4180 "notify_email" = NULL,
4181 "notify_email_unconfirmed" = NULL,
4182 "notify_email_secret" = NULL,
4183 "notify_email_secret_expiry" = NULL,
4184 "notify_email_lock_expiry" = NULL,
4185 "password_reset_secret" = NULL,
4186 "password_reset_secret_expiry" = NULL,
4187 "organizational_unit" = NULL,
4188 "internal_posts" = NULL,
4189 "realname" = NULL,
4190 "birthday" = NULL,
4191 "address" = NULL,
4192 "email" = NULL,
4193 "xmpp_address" = NULL,
4194 "website" = NULL,
4195 "phone" = NULL,
4196 "mobile_phone" = NULL,
4197 "profession" = NULL,
4198 "external_memberships" = NULL,
4199 "external_posts" = NULL,
4200 "statement" = NULL;
4201 -- "text_search_data" is updated by triggers
4202 DELETE FROM "invite_code";
4203 DELETE FROM "setting";
4204 DELETE FROM "setting_map";
4205 DELETE FROM "member_relation_setting";
4206 DELETE FROM "member_image";
4207 DELETE FROM "contact";
4208 DELETE FROM "ignored_member";
4209 DELETE FROM "session";
4210 DELETE FROM "area_setting";
4211 DELETE FROM "issue_setting";
4212 DELETE FROM "ignored_initiative";
4213 DELETE FROM "initiative_setting";
4214 DELETE FROM "suggestion_setting";
4215 DELETE FROM "non_voter";
4216 DELETE FROM "direct_voter" USING "issue"
4217 WHERE "direct_voter"."issue_id" = "issue"."id"
4218 AND "issue"."closed" ISNULL;
4219 RETURN;
4220 END;
4221 $$;
4223 COMMENT ON FUNCTION "delete_private_data"() IS 'Used by lf_export script. 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.';
4227 COMMIT;