liquid_feedback_core
view core.sql @ 157:725133783c8a
Do not ignore the "policy"."no_multistage_majority" flag: It does not affect the winner but affects the "initiative"."eligible" flag
| author | jbe |
|---|---|
| date | Sat Jun 04 11:50:44 2011 +0200 (2011-06-04) |
| parents | fb5dc1bf921f |
| children | 374cca7355af |
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.5.0_devel', 1, 5, -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 "majority_indirect" BOOLEAN NOT NULL DEFAULT TRUE,
317 "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT TRUE,
318 "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE );
319 CREATE INDEX "policy_active_idx" ON "policy" ("active");
321 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
323 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
324 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
325 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"';
326 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"';
327 COMMENT ON COLUMN "policy"."verification_time" IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
328 COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "fully_frozen" but not "closed"';
329 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"';
330 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"';
331 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
332 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
333 COMMENT ON COLUMN "policy"."majority_num" IS 'Numerator of fraction of majority to be reached during voting by an initiative to be "attainable"';
334 COMMENT ON COLUMN "policy"."majority_den" IS 'Denominator of fraction of majority to be reached during voting by an initiative to be "attainable"';
335 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.';
336 COMMENT ON COLUMN "policy"."majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be "attainable".';
337 COMMENT ON COLUMN "policy"."majority_non_negative" IS 'Absolute number of sum of "positive_votes" and abstentions neccessary for an initiative to be "attainable".';
338 COMMENT ON COLUMN "policy"."majority_indirect" IS 'If TRUE, then the majority specified by "majority_num", "majority_den", "majority_strict", "majority_positive" and "majority_non_negative" might be indirect; if FALSE, then the status quo must be beaten directly with that majority';
339 COMMENT ON COLUMN "policy"."no_reverse_beat_path" IS 'Causes initiatives with "reverse_beat_path" flag to not be "eligible", thus disallowing them to be winner. See comment on column "initiative"."reverse_beat_path".';
340 COMMENT ON COLUMN "policy"."no_multistage_majority" IS 'Causes initiatives with "multistage_majority" flag to not be "eligible", thus disallowing them to be winner. See comment on column "initiative"."multistage_majority". This flag does not affect the winner, if "policy"."majority_indirect" is set to TRUE or "policy"."majority_num" is set to zero.';
343 CREATE TABLE "unit" (
344 "id" SERIAL4 PRIMARY KEY,
345 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
346 "active" BOOLEAN NOT NULL DEFAULT TRUE,
347 "name" TEXT NOT NULL,
348 "description" TEXT NOT NULL DEFAULT '',
349 "member_count" INT4,
350 "text_search_data" TSVECTOR );
351 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
352 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
353 CREATE INDEX "unit_active_idx" ON "unit" ("active");
354 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
355 CREATE TRIGGER "update_text_search_data"
356 BEFORE INSERT OR UPDATE ON "unit"
357 FOR EACH ROW EXECUTE PROCEDURE
358 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
359 "name", "description" );
361 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
363 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
364 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in units of this area';
365 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
368 CREATE TABLE "area" (
369 "id" SERIAL4 PRIMARY KEY,
370 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
371 "active" BOOLEAN NOT NULL DEFAULT TRUE,
372 "name" TEXT NOT NULL,
373 "description" TEXT NOT NULL DEFAULT '',
374 "direct_member_count" INT4,
375 "member_weight" INT4,
376 "autoreject_weight" INT4,
377 "text_search_data" TSVECTOR );
378 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
379 CREATE INDEX "area_active_idx" ON "area" ("active");
380 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
381 CREATE TRIGGER "update_text_search_data"
382 BEFORE INSERT OR UPDATE ON "area"
383 FOR EACH ROW EXECUTE PROCEDURE
384 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
385 "name", "description" );
387 COMMENT ON TABLE "area" IS 'Subject areas';
389 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
390 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"';
391 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
392 COMMENT ON COLUMN "area"."autoreject_weight" IS 'Sum of weight of members using the autoreject feature';
395 CREATE TABLE "area_setting" (
396 PRIMARY KEY ("member_id", "key", "area_id"),
397 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
398 "key" TEXT NOT NULL,
399 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
400 "value" TEXT NOT NULL );
402 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
405 CREATE TABLE "allowed_policy" (
406 PRIMARY KEY ("area_id", "policy_id"),
407 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
408 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
409 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
410 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
412 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
414 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
417 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
419 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';
422 CREATE TYPE "issue_state" AS ENUM (
423 'admission', 'discussion', 'verification', 'voting',
424 'canceled_revoked_before_accepted',
425 'canceled_issue_not_accepted',
426 'canceled_after_revocation_during_discussion',
427 'canceled_after_revocation_during_verification',
428 'calculation',
429 'canceled_no_initiative_admitted',
430 'finished_without_winner', 'finished_with_winner');
432 COMMENT ON TYPE "issue_state" IS 'State of issues';
435 CREATE TABLE "issue" (
436 "id" SERIAL4 PRIMARY KEY,
437 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
438 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
439 "state" "issue_state" NOT NULL DEFAULT 'admission',
440 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
441 "accepted" TIMESTAMPTZ,
442 "half_frozen" TIMESTAMPTZ,
443 "fully_frozen" TIMESTAMPTZ,
444 "closed" TIMESTAMPTZ,
445 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
446 "cleaned" TIMESTAMPTZ,
447 "admission_time" INTERVAL NOT NULL,
448 "discussion_time" INTERVAL NOT NULL,
449 "verification_time" INTERVAL NOT NULL,
450 "voting_time" INTERVAL NOT NULL,
451 "snapshot" TIMESTAMPTZ,
452 "latest_snapshot_event" "snapshot_event",
453 "population" INT4,
454 "voter_count" INT4,
455 CONSTRAINT "valid_state" CHECK ((
456 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
457 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
458 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
459 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
460 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
461 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
462 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
463 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
464 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)) AND (
465 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
466 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
467 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
468 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
469 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
470 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
471 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
472 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
473 ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
474 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
475 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
476 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
477 )),
478 CONSTRAINT "state_change_order" CHECK (
479 "created" <= "accepted" AND
480 "accepted" <= "half_frozen" AND
481 "half_frozen" <= "fully_frozen" AND
482 "fully_frozen" <= "closed" ),
483 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
484 "cleaned" ISNULL OR "closed" NOTNULL ),
485 CONSTRAINT "last_snapshot_on_full_freeze"
486 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
487 CONSTRAINT "freeze_requires_snapshot"
488 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
489 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
490 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
491 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
492 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
493 CREATE INDEX "issue_created_idx" ON "issue" ("created");
494 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
495 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
496 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
497 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
498 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
499 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
501 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
503 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
504 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.';
505 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.';
506 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.';
507 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
508 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
509 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
510 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
511 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
512 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
513 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
514 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';
515 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
516 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';
519 CREATE TABLE "issue_setting" (
520 PRIMARY KEY ("member_id", "key", "issue_id"),
521 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
522 "key" TEXT NOT NULL,
523 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
524 "value" TEXT NOT NULL );
526 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
529 CREATE TABLE "initiative" (
530 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
531 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
532 "id" SERIAL4 PRIMARY KEY,
533 "name" TEXT NOT NULL,
534 "discussion_url" TEXT,
535 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
536 "revoked" TIMESTAMPTZ,
537 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
538 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
539 "admitted" BOOLEAN,
540 "supporter_count" INT4,
541 "informed_supporter_count" INT4,
542 "satisfied_supporter_count" INT4,
543 "satisfied_informed_supporter_count" INT4,
544 "positive_votes" INT4,
545 "negative_votes" INT4,
546 "attainable" BOOLEAN,
547 "favored_to_status_quo" BOOLEAN,
548 "unfavored_to_status_quo" BOOLEAN,
549 "schulze_rank" INT4,
550 "multistage_majority" BOOLEAN,
551 "reverse_beat_path" BOOLEAN,
552 "eligible" BOOLEAN,
553 "winner" BOOLEAN,
554 "text_search_data" TSVECTOR,
555 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
556 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL),
557 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
558 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
559 CONSTRAINT "revoked_initiatives_cant_be_admitted"
560 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
561 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
562 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
563 ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND "attainable" ISNULL AND
564 "favored_to_status_quo" ISNULL AND "unfavored_to_status_quo" ISNULL AND
565 "schulze_rank" ISNULL AND
566 "multistage_majority" ISNULL AND "reverse_beat_path" ISNULL AND
567 "winner" ISNULL ) ),
568 CONSTRAINT "favored_excludes_unfavored" CHECK (NOT ("favored_to_status_quo" AND "unfavored_to_status_quo")) );
569 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
570 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
571 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
572 CREATE TRIGGER "update_text_search_data"
573 BEFORE INSERT OR UPDATE ON "initiative"
574 FOR EACH ROW EXECUTE PROCEDURE
575 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
576 "name", "discussion_url");
578 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.';
580 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
581 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
582 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoked the initiative';
583 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
584 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
585 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
586 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
587 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
588 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
589 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
590 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"';
591 COMMENT ON COLUMN "initiative"."favored_to_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)';
592 COMMENT ON COLUMN "initiative"."unfavored_to_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)';
593 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking without tie-breaking';
594 COMMENT ON COLUMN "initiative"."multistage_majority" IS 'TRUE, if either (a) this initiative has no better rank than the status quo, or (b) 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';
595 COMMENT ON COLUMN "initiative"."reverse_beat_path" IS 'TRUE, if there a beat path (may include ties), from this initiative to the status quo';
596 COMMENT ON COLUMN "initiative"."eligible" IS 'Initiative is "attainable" and depending on selected policy has no "multistage_majority" or "reverse_beat_path"';
597 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"';
600 CREATE TABLE "battle" (
601 "issue_id" INT4 NOT NULL,
602 "winning_initiative_id" INT4,
603 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
604 "losing_initiative_id" INT4,
605 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
606 "count" INT4 NOT NULL,
607 CONSTRAINT "initiative_ids_not_equal" CHECK (
608 "winning_initiative_id" != "losing_initiative_id" OR
609 ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
610 ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) );
611 CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
612 CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
613 CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
615 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';
618 CREATE TABLE "ignored_initiative" (
619 PRIMARY KEY ("initiative_id", "member_id"),
620 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
621 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
622 CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");
624 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
627 CREATE TABLE "initiative_setting" (
628 PRIMARY KEY ("member_id", "key", "initiative_id"),
629 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
630 "key" TEXT NOT NULL,
631 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
632 "value" TEXT NOT NULL );
634 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
637 CREATE TABLE "draft" (
638 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
639 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
640 "id" SERIAL8 PRIMARY KEY,
641 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
642 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
643 "formatting_engine" TEXT,
644 "content" TEXT NOT NULL,
645 "text_search_data" TSVECTOR );
646 CREATE INDEX "draft_created_idx" ON "draft" ("created");
647 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
648 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
649 CREATE TRIGGER "update_text_search_data"
650 BEFORE INSERT OR UPDATE ON "draft"
651 FOR EACH ROW EXECUTE PROCEDURE
652 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
654 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.';
656 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
657 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
660 CREATE TABLE "rendered_draft" (
661 PRIMARY KEY ("draft_id", "format"),
662 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
663 "format" TEXT,
664 "content" TEXT NOT NULL );
666 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)';
669 CREATE TABLE "suggestion" (
670 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
671 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
672 "id" SERIAL8 PRIMARY KEY,
673 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
674 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
675 "name" TEXT NOT NULL,
676 "description" TEXT NOT NULL DEFAULT '',
677 "text_search_data" TSVECTOR,
678 "minus2_unfulfilled_count" INT4,
679 "minus2_fulfilled_count" INT4,
680 "minus1_unfulfilled_count" INT4,
681 "minus1_fulfilled_count" INT4,
682 "plus1_unfulfilled_count" INT4,
683 "plus1_fulfilled_count" INT4,
684 "plus2_unfulfilled_count" INT4,
685 "plus2_fulfilled_count" INT4 );
686 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
687 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
688 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
689 CREATE TRIGGER "update_text_search_data"
690 BEFORE INSERT OR UPDATE ON "suggestion"
691 FOR EACH ROW EXECUTE PROCEDURE
692 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
693 "name", "description");
695 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';
697 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
698 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
699 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
700 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
701 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
702 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
703 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
704 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
707 CREATE TABLE "suggestion_setting" (
708 PRIMARY KEY ("member_id", "key", "suggestion_id"),
709 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
710 "key" TEXT NOT NULL,
711 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
712 "value" TEXT NOT NULL );
714 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
717 CREATE TABLE "invite_code_unit" (
718 PRIMARY KEY ("invite_code_id", "unit_id"),
719 "invite_code_id" INT8 REFERENCES "invite_code" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
720 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
722 COMMENT ON TABLE "invite_code_unit" IS 'Units where accounts created with a given invite codes get voting rights';
725 CREATE TABLE "privilege" (
726 PRIMARY KEY ("unit_id", "member_id"),
727 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
728 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
729 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
730 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
731 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
732 "voting_right_manager" BOOLEAN NOT NULL DEFAULT FALSE,
733 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE );
735 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
737 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke admin privileges to/from other users';
738 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create or lock sub units';
739 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create or lock areas and set area parameters';
740 COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote inside the unit';
741 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to discuss and vote';
744 CREATE TABLE "membership" (
745 PRIMARY KEY ("area_id", "member_id"),
746 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
747 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
748 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
749 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
751 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
753 COMMENT ON COLUMN "membership"."autoreject" IS 'TRUE = member votes against all initiatives, if he/she is neither direct_ or delegating_voter; Entries in the "issue_autoreject" table can override this setting.';
756 CREATE TABLE "interest" (
757 PRIMARY KEY ("issue_id", "member_id"),
758 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
759 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
760 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
762 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.';
765 CREATE TABLE "issue_autoreject" (
766 PRIMARY KEY ("issue_id", "member_id"),
767 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
768 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
769 "autoreject" BOOLEAN NOT NULL );
770 CREATE INDEX "issue_autoreject_member_id_idx" ON "issue_autoreject" ("member_id");
772 COMMENT ON TABLE "issue_autoreject" IS 'If autoreject=TRUE, then member votes against all initiatives, if he/she is neither direct_ or delegating_voter; Values of either TRUE or FALSE override settings in "membership" table.';
775 CREATE TABLE "initiator" (
776 PRIMARY KEY ("initiative_id", "member_id"),
777 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
778 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
779 "accepted" BOOLEAN );
780 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
782 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.';
784 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.';
787 CREATE TABLE "supporter" (
788 "issue_id" INT4 NOT NULL,
789 PRIMARY KEY ("initiative_id", "member_id"),
790 "initiative_id" INT4,
791 "member_id" INT4,
792 "draft_id" INT8 NOT NULL,
793 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
794 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
795 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
797 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.';
799 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
802 CREATE TABLE "opinion" (
803 "initiative_id" INT4 NOT NULL,
804 PRIMARY KEY ("suggestion_id", "member_id"),
805 "suggestion_id" INT8,
806 "member_id" INT4,
807 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
808 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
809 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
810 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
811 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
813 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.';
815 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
818 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
820 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
823 CREATE TABLE "delegation" (
824 "id" SERIAL8 PRIMARY KEY,
825 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
826 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
827 "scope" "delegation_scope" NOT NULL,
828 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
829 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
830 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
831 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
832 CONSTRAINT "no_unit_delegation_to_null"
833 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
834 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
835 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
836 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
837 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
838 UNIQUE ("unit_id", "truster_id"),
839 UNIQUE ("area_id", "truster_id"),
840 UNIQUE ("issue_id", "truster_id") );
841 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
842 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
844 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
846 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
847 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
848 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
851 CREATE TABLE "direct_population_snapshot" (
852 PRIMARY KEY ("issue_id", "event", "member_id"),
853 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
854 "event" "snapshot_event",
855 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
856 "weight" INT4 );
857 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
859 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area", an "interest" in the "issue", or "issue_autoreject"."autoreject" set to TRUE';
861 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
862 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
865 CREATE TABLE "delegating_population_snapshot" (
866 PRIMARY KEY ("issue_id", "event", "member_id"),
867 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
868 "event" "snapshot_event",
869 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
870 "weight" INT4,
871 "scope" "delegation_scope" NOT NULL,
872 "delegate_member_ids" INT4[] NOT NULL );
873 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
875 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
877 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
878 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
879 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
880 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"';
883 CREATE TABLE "direct_interest_snapshot" (
884 PRIMARY KEY ("issue_id", "event", "member_id"),
885 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
886 "event" "snapshot_event",
887 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
888 "weight" INT4 );
889 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
891 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
893 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
894 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
897 CREATE TABLE "delegating_interest_snapshot" (
898 PRIMARY KEY ("issue_id", "event", "member_id"),
899 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
900 "event" "snapshot_event",
901 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
902 "weight" INT4,
903 "scope" "delegation_scope" NOT NULL,
904 "delegate_member_ids" INT4[] NOT NULL );
905 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
907 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
909 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
910 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
911 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
912 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"';
915 CREATE TABLE "direct_supporter_snapshot" (
916 "issue_id" INT4 NOT NULL,
917 PRIMARY KEY ("initiative_id", "event", "member_id"),
918 "initiative_id" INT4,
919 "event" "snapshot_event",
920 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
921 "informed" BOOLEAN NOT NULL,
922 "satisfied" BOOLEAN NOT NULL,
923 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
924 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
925 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
927 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
929 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
930 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
931 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
934 CREATE TABLE "non_voter" (
935 PRIMARY KEY ("issue_id", "member_id"),
936 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
937 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
938 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
940 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
943 CREATE TABLE "direct_voter" (
944 PRIMARY KEY ("issue_id", "member_id"),
945 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
946 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
947 "weight" INT4,
948 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
949 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
951 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.';
953 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
954 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
957 CREATE TABLE "delegating_voter" (
958 PRIMARY KEY ("issue_id", "member_id"),
959 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
960 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
961 "weight" INT4,
962 "scope" "delegation_scope" NOT NULL,
963 "delegate_member_ids" INT4[] NOT NULL );
964 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
966 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
968 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
969 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
970 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"';
973 CREATE TABLE "vote" (
974 "issue_id" INT4 NOT NULL,
975 PRIMARY KEY ("initiative_id", "member_id"),
976 "initiative_id" INT4,
977 "member_id" INT4,
978 "grade" INT4,
979 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
980 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
981 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
983 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.';
985 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.';
988 CREATE TABLE "issue_comment" (
989 PRIMARY KEY ("issue_id", "member_id"),
990 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
991 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
992 "changed" TIMESTAMPTZ NOT NULL DEFAULT now(),
993 "formatting_engine" TEXT,
994 "content" TEXT NOT NULL,
995 "text_search_data" TSVECTOR );
996 CREATE INDEX "issue_comment_member_id_idx" ON "issue_comment" ("member_id");
997 CREATE INDEX "issue_comment_text_search_data_idx" ON "issue_comment" USING gin ("text_search_data");
998 CREATE TRIGGER "update_text_search_data"
999 BEFORE INSERT OR UPDATE ON "issue_comment"
1000 FOR EACH ROW EXECUTE PROCEDURE
1001 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
1003 COMMENT ON TABLE "issue_comment" IS 'Place to store free comments of members related to issues';
1005 COMMENT ON COLUMN "issue_comment"."changed" IS 'Time the comment was last changed';
1008 CREATE TABLE "rendered_issue_comment" (
1009 PRIMARY KEY ("issue_id", "member_id", "format"),
1010 FOREIGN KEY ("issue_id", "member_id")
1011 REFERENCES "issue_comment" ("issue_id", "member_id")
1012 ON DELETE CASCADE ON UPDATE CASCADE,
1013 "issue_id" INT4,
1014 "member_id" INT4,
1015 "format" TEXT,
1016 "content" TEXT NOT NULL );
1018 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)';
1021 CREATE TABLE "voting_comment" (
1022 PRIMARY KEY ("issue_id", "member_id"),
1023 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1024 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1025 "changed" TIMESTAMPTZ,
1026 "formatting_engine" TEXT,
1027 "content" TEXT NOT NULL,
1028 "text_search_data" TSVECTOR );
1029 CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id");
1030 CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data");
1031 CREATE TRIGGER "update_text_search_data"
1032 BEFORE INSERT OR UPDATE ON "voting_comment"
1033 FOR EACH ROW EXECUTE PROCEDURE
1034 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
1036 COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.';
1038 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.';
1041 CREATE TABLE "rendered_voting_comment" (
1042 PRIMARY KEY ("issue_id", "member_id", "format"),
1043 FOREIGN KEY ("issue_id", "member_id")
1044 REFERENCES "voting_comment" ("issue_id", "member_id")
1045 ON DELETE CASCADE ON UPDATE CASCADE,
1046 "issue_id" INT4,
1047 "member_id" INT4,
1048 "format" TEXT,
1049 "content" TEXT NOT NULL );
1051 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)';
1054 CREATE TYPE "event_type" AS ENUM (
1055 'issue_state_changed',
1056 'initiative_created_in_new_issue',
1057 'initiative_created_in_existing_issue',
1058 'initiative_revoked',
1059 'new_draft_created',
1060 'suggestion_created');
1062 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
1065 CREATE TABLE "event" (
1066 "id" SERIAL8 PRIMARY KEY,
1067 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
1068 "event" "event_type" NOT NULL,
1069 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1070 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1071 "state" "issue_state" CHECK ("state" != 'calculation'),
1072 "initiative_id" INT4,
1073 "draft_id" INT8,
1074 "suggestion_id" INT8,
1075 FOREIGN KEY ("issue_id", "initiative_id")
1076 REFERENCES "initiative" ("issue_id", "id")
1077 ON DELETE CASCADE ON UPDATE CASCADE,
1078 FOREIGN KEY ("initiative_id", "draft_id")
1079 REFERENCES "draft" ("initiative_id", "id")
1080 ON DELETE CASCADE ON UPDATE CASCADE,
1081 FOREIGN KEY ("initiative_id", "suggestion_id")
1082 REFERENCES "suggestion" ("initiative_id", "id")
1083 ON DELETE CASCADE ON UPDATE CASCADE,
1084 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
1085 "event" != 'issue_state_changed' OR (
1086 "member_id" ISNULL AND
1087 "issue_id" NOTNULL AND
1088 "state" NOTNULL AND
1089 "initiative_id" ISNULL AND
1090 "draft_id" ISNULL AND
1091 "suggestion_id" ISNULL )),
1092 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
1093 "event" NOT IN (
1094 'initiative_created_in_new_issue',
1095 'initiative_created_in_existing_issue',
1096 'initiative_revoked',
1097 'new_draft_created'
1098 ) OR (
1099 "member_id" NOTNULL AND
1100 "issue_id" NOTNULL AND
1101 "state" NOTNULL AND
1102 "initiative_id" NOTNULL AND
1103 "draft_id" NOTNULL AND
1104 "suggestion_id" ISNULL )),
1105 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
1106 "event" != 'suggestion_created' OR (
1107 "member_id" NOTNULL AND
1108 "issue_id" NOTNULL AND
1109 "state" NOTNULL AND
1110 "initiative_id" NOTNULL AND
1111 "draft_id" ISNULL AND
1112 "suggestion_id" NOTNULL )) );
1114 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
1116 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
1117 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';
1118 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';
1119 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';
1123 ----------------------------------------------
1124 -- Writing of history entries and event log --
1125 ----------------------------------------------
1127 CREATE FUNCTION "write_member_history_trigger"()
1128 RETURNS TRIGGER
1129 LANGUAGE 'plpgsql' VOLATILE AS $$
1130 BEGIN
1131 IF
1132 NEW."active" != OLD."active" OR
1133 NEW."name" != OLD."name"
1134 THEN
1135 INSERT INTO "member_history"
1136 ("member_id", "active", "name")
1137 VALUES (NEW."id", OLD."active", OLD."name");
1138 END IF;
1139 RETURN NULL;
1140 END;
1141 $$;
1143 CREATE TRIGGER "write_member_history"
1144 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1145 "write_member_history_trigger"();
1147 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
1148 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
1151 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
1152 RETURNS TRIGGER
1153 LANGUAGE 'plpgsql' VOLATILE AS $$
1154 BEGIN
1155 IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN
1156 INSERT INTO "event" ("event", "issue_id", "state")
1157 VALUES ('issue_state_changed', NEW."id", NEW."state");
1158 END IF;
1159 RETURN NULL;
1160 END;
1161 $$;
1163 CREATE TRIGGER "write_event_issue_state_changed"
1164 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
1165 "write_event_issue_state_changed_trigger"();
1167 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
1168 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
1171 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1172 RETURNS TRIGGER
1173 LANGUAGE 'plpgsql' VOLATILE AS $$
1174 DECLARE
1175 "initiative_row" "initiative"%ROWTYPE;
1176 "issue_row" "issue"%ROWTYPE;
1177 "event_v" "event_type";
1178 BEGIN
1179 SELECT * INTO "initiative_row" FROM "initiative"
1180 WHERE "id" = NEW."initiative_id";
1181 SELECT * INTO "issue_row" FROM "issue"
1182 WHERE "id" = "initiative_row"."issue_id";
1183 IF EXISTS (
1184 SELECT NULL FROM "draft"
1185 WHERE "initiative_id" = NEW."initiative_id"
1186 AND "id" != NEW."id"
1187 ) THEN
1188 "event_v" := 'new_draft_created';
1189 ELSE
1190 IF EXISTS (
1191 SELECT NULL FROM "initiative"
1192 WHERE "issue_id" = "initiative_row"."issue_id"
1193 AND "id" != "initiative_row"."id"
1194 ) THEN
1195 "event_v" := 'initiative_created_in_existing_issue';
1196 ELSE
1197 "event_v" := 'initiative_created_in_new_issue';
1198 END IF;
1199 END IF;
1200 INSERT INTO "event" (
1201 "event", "member_id",
1202 "issue_id", "state", "initiative_id", "draft_id"
1203 ) VALUES (
1204 "event_v",
1205 NEW."author_id",
1206 "initiative_row"."issue_id",
1207 "issue_row"."state",
1208 "initiative_row"."id",
1209 NEW."id" );
1210 RETURN NULL;
1211 END;
1212 $$;
1214 CREATE TRIGGER "write_event_initiative_or_draft_created"
1215 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
1216 "write_event_initiative_or_draft_created_trigger"();
1218 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
1219 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
1222 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
1223 RETURNS TRIGGER
1224 LANGUAGE 'plpgsql' VOLATILE AS $$
1225 DECLARE
1226 "issue_row" "issue"%ROWTYPE;
1227 BEGIN
1228 SELECT * INTO "issue_row" FROM "issue"
1229 WHERE "id" = NEW."issue_id";
1230 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1231 INSERT INTO "event" (
1232 "event", "member_id", "issue_id", "state", "initiative_id"
1233 ) VALUES (
1234 'initiative_revoked',
1235 NEW."revoked_by_member_id",
1236 NEW."issue_id",
1237 "issue_row"."state",
1238 NEW."id" );
1239 END IF;
1240 RETURN NULL;
1241 END;
1242 $$;
1244 CREATE TRIGGER "write_event_initiative_revoked"
1245 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
1246 "write_event_initiative_revoked_trigger"();
1248 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
1249 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
1252 CREATE FUNCTION "write_event_suggestion_created_trigger"()
1253 RETURNS TRIGGER
1254 LANGUAGE 'plpgsql' VOLATILE AS $$
1255 DECLARE
1256 "initiative_row" "initiative"%ROWTYPE;
1257 "issue_row" "issue"%ROWTYPE;
1258 BEGIN
1259 SELECT * INTO "initiative_row" FROM "initiative"
1260 WHERE "id" = NEW."initiative_id";
1261 SELECT * INTO "issue_row" FROM "issue"
1262 WHERE "id" = "initiative_row"."issue_id";
1263 INSERT INTO "event" (
1264 "event", "member_id",
1265 "issue_id", "state", "initiative_id", "suggestion_id"
1266 ) VALUES (
1267 'suggestion_created',
1268 NEW."author_id",
1269 "initiative_row"."issue_id",
1270 "issue_row"."state",
1271 "initiative_row"."id",
1272 NEW."id" );
1273 RETURN NULL;
1274 END;
1275 $$;
1277 CREATE TRIGGER "write_event_suggestion_created"
1278 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1279 "write_event_suggestion_created_trigger"();
1281 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
1282 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1286 ----------------------------
1287 -- Additional constraints --
1288 ----------------------------
1291 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
1292 RETURNS TRIGGER
1293 LANGUAGE 'plpgsql' VOLATILE AS $$
1294 BEGIN
1295 IF NOT EXISTS (
1296 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
1297 ) THEN
1298 --RAISE 'Cannot create issue without an initial initiative.' USING
1299 -- ERRCODE = 'integrity_constraint_violation',
1300 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
1301 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
1302 END IF;
1303 RETURN NULL;
1304 END;
1305 $$;
1307 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
1308 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
1309 FOR EACH ROW EXECUTE PROCEDURE
1310 "issue_requires_first_initiative_trigger"();
1312 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
1313 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
1316 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
1317 RETURNS TRIGGER
1318 LANGUAGE 'plpgsql' VOLATILE AS $$
1319 DECLARE
1320 "reference_lost" BOOLEAN;
1321 BEGIN
1322 IF TG_OP = 'DELETE' THEN
1323 "reference_lost" := TRUE;
1324 ELSE
1325 "reference_lost" := NEW."issue_id" != OLD."issue_id";
1326 END IF;
1327 IF
1328 "reference_lost" AND NOT EXISTS (
1329 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
1330 )
1331 THEN
1332 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
1333 END IF;
1334 RETURN NULL;
1335 END;
1336 $$;
1338 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
1339 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1340 FOR EACH ROW EXECUTE PROCEDURE
1341 "last_initiative_deletes_issue_trigger"();
1343 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
1344 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
1347 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
1348 RETURNS TRIGGER
1349 LANGUAGE 'plpgsql' VOLATILE AS $$
1350 BEGIN
1351 IF NOT EXISTS (
1352 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
1353 ) THEN
1354 --RAISE 'Cannot create initiative without an initial draft.' USING
1355 -- ERRCODE = 'integrity_constraint_violation',
1356 -- HINT = 'Create issue, initiative and draft within the same transaction.';
1357 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
1358 END IF;
1359 RETURN NULL;
1360 END;
1361 $$;
1363 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
1364 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1365 FOR EACH ROW EXECUTE PROCEDURE
1366 "initiative_requires_first_draft_trigger"();
1368 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
1369 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
1372 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
1373 RETURNS TRIGGER
1374 LANGUAGE 'plpgsql' VOLATILE AS $$
1375 DECLARE
1376 "reference_lost" BOOLEAN;
1377 BEGIN
1378 IF TG_OP = 'DELETE' THEN
1379 "reference_lost" := TRUE;
1380 ELSE
1381 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
1382 END IF;
1383 IF
1384 "reference_lost" AND NOT EXISTS (
1385 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
1386 )
1387 THEN
1388 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
1389 END IF;
1390 RETURN NULL;
1391 END;
1392 $$;
1394 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
1395 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
1396 FOR EACH ROW EXECUTE PROCEDURE
1397 "last_draft_deletes_initiative_trigger"();
1399 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
1400 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
1403 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
1404 RETURNS TRIGGER
1405 LANGUAGE 'plpgsql' VOLATILE AS $$
1406 BEGIN
1407 IF NOT EXISTS (
1408 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
1409 ) THEN
1410 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
1411 END IF;
1412 RETURN NULL;
1413 END;
1414 $$;
1416 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
1417 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
1418 FOR EACH ROW EXECUTE PROCEDURE
1419 "suggestion_requires_first_opinion_trigger"();
1421 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1422 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1425 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1426 RETURNS TRIGGER
1427 LANGUAGE 'plpgsql' VOLATILE AS $$
1428 DECLARE
1429 "reference_lost" BOOLEAN;
1430 BEGIN
1431 IF TG_OP = 'DELETE' THEN
1432 "reference_lost" := TRUE;
1433 ELSE
1434 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1435 END IF;
1436 IF
1437 "reference_lost" AND NOT EXISTS (
1438 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1439 )
1440 THEN
1441 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1442 END IF;
1443 RETURN NULL;
1444 END;
1445 $$;
1447 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1448 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1449 FOR EACH ROW EXECUTE PROCEDURE
1450 "last_opinion_deletes_suggestion_trigger"();
1452 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1453 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1457 ---------------------------------------------------------------
1458 -- Ensure that votes are not modified when issues are frozen --
1459 ---------------------------------------------------------------
1461 -- NOTE: Frontends should ensure this anyway, but in case of programming
1462 -- errors the following triggers ensure data integrity.
1465 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1466 RETURNS TRIGGER
1467 LANGUAGE 'plpgsql' VOLATILE AS $$
1468 DECLARE
1469 "issue_id_v" "issue"."id"%TYPE;
1470 "issue_row" "issue"%ROWTYPE;
1471 BEGIN
1472 IF TG_OP = 'DELETE' THEN
1473 "issue_id_v" := OLD."issue_id";
1474 ELSE
1475 "issue_id_v" := NEW."issue_id";
1476 END IF;
1477 SELECT INTO "issue_row" * FROM "issue"
1478 WHERE "id" = "issue_id_v" FOR SHARE;
1479 IF "issue_row"."closed" NOTNULL THEN
1480 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
1481 END IF;
1482 RETURN NULL;
1483 END;
1484 $$;
1486 CREATE TRIGGER "forbid_changes_on_closed_issue"
1487 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1488 FOR EACH ROW EXECUTE PROCEDURE
1489 "forbid_changes_on_closed_issue_trigger"();
1491 CREATE TRIGGER "forbid_changes_on_closed_issue"
1492 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1493 FOR EACH ROW EXECUTE PROCEDURE
1494 "forbid_changes_on_closed_issue_trigger"();
1496 CREATE TRIGGER "forbid_changes_on_closed_issue"
1497 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1498 FOR EACH ROW EXECUTE PROCEDURE
1499 "forbid_changes_on_closed_issue_trigger"();
1501 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"';
1502 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';
1503 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';
1504 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';
1508 --------------------------------------------------------------------
1509 -- Auto-retrieval of fields only needed for referential integrity --
1510 --------------------------------------------------------------------
1513 CREATE FUNCTION "autofill_issue_id_trigger"()
1514 RETURNS TRIGGER
1515 LANGUAGE 'plpgsql' VOLATILE AS $$
1516 BEGIN
1517 IF NEW."issue_id" ISNULL THEN
1518 SELECT "issue_id" INTO NEW."issue_id"
1519 FROM "initiative" WHERE "id" = NEW."initiative_id";
1520 END IF;
1521 RETURN NEW;
1522 END;
1523 $$;
1525 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1526 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1528 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1529 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1531 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1532 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1533 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1536 CREATE FUNCTION "autofill_initiative_id_trigger"()
1537 RETURNS TRIGGER
1538 LANGUAGE 'plpgsql' VOLATILE AS $$
1539 BEGIN
1540 IF NEW."initiative_id" ISNULL THEN
1541 SELECT "initiative_id" INTO NEW."initiative_id"
1542 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1543 END IF;
1544 RETURN NEW;
1545 END;
1546 $$;
1548 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1549 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1551 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1552 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1556 -----------------------------------------------------
1557 -- Automatic calculation of certain default values --
1558 -----------------------------------------------------
1561 CREATE FUNCTION "copy_timings_trigger"()
1562 RETURNS TRIGGER
1563 LANGUAGE 'plpgsql' VOLATILE AS $$
1564 DECLARE
1565 "policy_row" "policy"%ROWTYPE;
1566 BEGIN
1567 SELECT * INTO "policy_row" FROM "policy"
1568 WHERE "id" = NEW."policy_id";
1569 IF NEW."admission_time" ISNULL THEN
1570 NEW."admission_time" := "policy_row"."admission_time";
1571 END IF;
1572 IF NEW."discussion_time" ISNULL THEN
1573 NEW."discussion_time" := "policy_row"."discussion_time";
1574 END IF;
1575 IF NEW."verification_time" ISNULL THEN
1576 NEW."verification_time" := "policy_row"."verification_time";
1577 END IF;
1578 IF NEW."voting_time" ISNULL THEN
1579 NEW."voting_time" := "policy_row"."voting_time";
1580 END IF;
1581 RETURN NEW;
1582 END;
1583 $$;
1585 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1586 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1588 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1589 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1592 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
1593 RETURNS TRIGGER
1594 LANGUAGE 'plpgsql' VOLATILE AS $$
1595 BEGIN
1596 IF NEW."draft_id" ISNULL THEN
1597 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1598 WHERE "initiative_id" = NEW."initiative_id";
1599 END IF;
1600 RETURN NEW;
1601 END;
1602 $$;
1604 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1605 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
1607 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
1608 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';
1612 ----------------------------------------
1613 -- Automatic creation of dependencies --
1614 ----------------------------------------
1617 CREATE FUNCTION "autocreate_interest_trigger"()
1618 RETURNS TRIGGER
1619 LANGUAGE 'plpgsql' VOLATILE AS $$
1620 BEGIN
1621 IF NOT EXISTS (
1622 SELECT NULL FROM "initiative" JOIN "interest"
1623 ON "initiative"."issue_id" = "interest"."issue_id"
1624 WHERE "initiative"."id" = NEW."initiative_id"
1625 AND "interest"."member_id" = NEW."member_id"
1626 ) THEN
1627 BEGIN
1628 INSERT INTO "interest" ("issue_id", "member_id")
1629 SELECT "issue_id", NEW."member_id"
1630 FROM "initiative" WHERE "id" = NEW."initiative_id";
1631 EXCEPTION WHEN unique_violation THEN END;
1632 END IF;
1633 RETURN NEW;
1634 END;
1635 $$;
1637 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1638 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1640 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1641 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';
1644 CREATE FUNCTION "autocreate_supporter_trigger"()
1645 RETURNS TRIGGER
1646 LANGUAGE 'plpgsql' VOLATILE AS $$
1647 BEGIN
1648 IF NOT EXISTS (
1649 SELECT NULL FROM "suggestion" JOIN "supporter"
1650 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1651 WHERE "suggestion"."id" = NEW."suggestion_id"
1652 AND "supporter"."member_id" = NEW."member_id"
1653 ) THEN
1654 BEGIN
1655 INSERT INTO "supporter" ("initiative_id", "member_id")
1656 SELECT "initiative_id", NEW."member_id"
1657 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1658 EXCEPTION WHEN unique_violation THEN END;
1659 END IF;
1660 RETURN NEW;
1661 END;
1662 $$;
1664 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1665 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1667 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1668 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.';
1672 ------------------------------------------
1673 -- Views and helper functions for views --
1674 ------------------------------------------
1677 CREATE VIEW "unit_delegation" AS
1678 SELECT
1679 "unit"."id" AS "unit_id",
1680 "delegation"."id",
1681 "delegation"."truster_id",
1682 "delegation"."trustee_id",
1683 "delegation"."scope"
1684 FROM "unit"
1685 JOIN "delegation"
1686 ON "delegation"."unit_id" = "unit"."id"
1687 JOIN "member"
1688 ON "delegation"."truster_id" = "member"."id"
1689 JOIN "privilege"
1690 ON "delegation"."unit_id" = "privilege"."unit_id"
1691 AND "delegation"."truster_id" = "privilege"."member_id"
1692 WHERE "member"."active" AND "privilege"."voting_right";
1694 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
1697 CREATE VIEW "area_delegation" AS
1698 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1699 "area"."id" AS "area_id",
1700 "delegation"."id",
1701 "delegation"."truster_id",
1702 "delegation"."trustee_id",
1703 "delegation"."scope"
1704 FROM "area"
1705 JOIN "delegation"
1706 ON "delegation"."unit_id" = "area"."unit_id"
1707 OR "delegation"."area_id" = "area"."id"
1708 JOIN "member"
1709 ON "delegation"."truster_id" = "member"."id"
1710 JOIN "privilege"
1711 ON "area"."unit_id" = "privilege"."unit_id"
1712 AND "delegation"."truster_id" = "privilege"."member_id"
1713 WHERE "member"."active" AND "privilege"."voting_right"
1714 ORDER BY
1715 "area"."id",
1716 "delegation"."truster_id",
1717 "delegation"."scope" DESC;
1719 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
1722 CREATE VIEW "issue_delegation" AS
1723 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1724 "issue"."id" AS "issue_id",
1725 "delegation"."id",
1726 "delegation"."truster_id",
1727 "delegation"."trustee_id",
1728 "delegation"."scope"
1729 FROM "issue"
1730 JOIN "area"
1731 ON "area"."id" = "issue"."area_id"
1732 JOIN "delegation"
1733 ON "delegation"."unit_id" = "area"."unit_id"
1734 OR "delegation"."area_id" = "area"."id"
1735 OR "delegation"."issue_id" = "issue"."id"
1736 JOIN "member"
1737 ON "delegation"."truster_id" = "member"."id"
1738 JOIN "privilege"
1739 ON "area"."unit_id" = "privilege"."unit_id"
1740 AND "delegation"."truster_id" = "privilege"."member_id"
1741 WHERE "member"."active" AND "privilege"."voting_right"
1742 ORDER BY
1743 "issue"."id",
1744 "delegation"."truster_id",
1745 "delegation"."scope" DESC;
1747 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
1750 CREATE FUNCTION "membership_weight_with_skipping"
1751 ( "area_id_p" "area"."id"%TYPE,
1752 "member_id_p" "member"."id"%TYPE,
1753 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1754 RETURNS INT4
1755 LANGUAGE 'plpgsql' STABLE AS $$
1756 DECLARE
1757 "sum_v" INT4;
1758 "delegation_row" "area_delegation"%ROWTYPE;
1759 BEGIN
1760 "sum_v" := 1;
1761 FOR "delegation_row" IN
1762 SELECT "area_delegation".*
1763 FROM "area_delegation" LEFT JOIN "membership"
1764 ON "membership"."area_id" = "area_id_p"
1765 AND "membership"."member_id" = "area_delegation"."truster_id"
1766 WHERE "area_delegation"."area_id" = "area_id_p"
1767 AND "area_delegation"."trustee_id" = "member_id_p"
1768 AND "membership"."member_id" ISNULL
1769 LOOP
1770 IF NOT
1771 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1772 THEN
1773 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1774 "area_id_p",
1775 "delegation_row"."truster_id",
1776 "skip_member_ids_p" || "delegation_row"."truster_id"
1777 );
1778 END IF;
1779 END LOOP;
1780 RETURN "sum_v";
1781 END;
1782 $$;
1784 COMMENT ON FUNCTION "membership_weight_with_skipping"
1785 ( "area"."id"%TYPE,
1786 "member"."id"%TYPE,
1787 INT4[] )
1788 IS 'Helper function for "membership_weight" function';
1791 CREATE FUNCTION "membership_weight"
1792 ( "area_id_p" "area"."id"%TYPE,
1793 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1794 RETURNS INT4
1795 LANGUAGE 'plpgsql' STABLE AS $$
1796 BEGIN
1797 RETURN "membership_weight_with_skipping"(
1798 "area_id_p",
1799 "member_id_p",
1800 ARRAY["member_id_p"]
1801 );
1802 END;
1803 $$;
1805 COMMENT ON FUNCTION "membership_weight"
1806 ( "area"."id"%TYPE,
1807 "member"."id"%TYPE )
1808 IS 'Calculates the potential voting weight of a member in a given area';
1811 CREATE VIEW "member_count_view" AS
1812 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1814 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1817 CREATE VIEW "unit_member_count" AS
1818 SELECT
1819 "unit"."id" AS "unit_id",
1820 sum("member"."id") AS "member_count"
1821 FROM "unit"
1822 LEFT JOIN "privilege"
1823 ON "privilege"."unit_id" = "unit"."id"
1824 AND "privilege"."voting_right"
1825 LEFT JOIN "member"
1826 ON "member"."id" = "privilege"."member_id"
1827 AND "member"."active"
1828 GROUP BY "unit"."id";
1830 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
1833 CREATE VIEW "area_member_count" AS
1834 SELECT
1835 "area"."id" AS "area_id",
1836 count("member"."id") AS "direct_member_count",
1837 coalesce(
1838 sum(
1839 CASE WHEN "member"."id" NOTNULL THEN
1840 "membership_weight"("area"."id", "member"."id")
1841 ELSE 0 END
1842 )
1843 ) AS "member_weight",
1844 coalesce(
1845 sum(
1846 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
1847 "membership_weight"("area"."id", "member"."id")
1848 ELSE 0 END
1849 )
1850 ) AS "autoreject_weight"
1851 FROM "area"
1852 LEFT JOIN "membership"
1853 ON "area"."id" = "membership"."area_id"
1854 LEFT JOIN "privilege"
1855 ON "privilege"."unit_id" = "area"."unit_id"
1856 AND "privilege"."member_id" = "membership"."member_id"
1857 AND "privilege"."voting_right"
1858 LEFT JOIN "member"
1859 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
1860 AND "member"."active"
1861 GROUP BY "area"."id";
1863 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count", "member_weight" and "autoreject_weight" columns of table "area"';
1866 CREATE VIEW "opening_draft" AS
1867 SELECT "draft".* FROM (
1868 SELECT
1869 "initiative"."id" AS "initiative_id",
1870 min("draft"."id") AS "draft_id"
1871 FROM "initiative" JOIN "draft"
1872 ON "initiative"."id" = "draft"."initiative_id"
1873 GROUP BY "initiative"."id"
1874 ) AS "subquery"
1875 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1877 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1880 CREATE VIEW "current_draft" AS
1881 SELECT "draft".* FROM (
1882 SELECT
1883 "initiative"."id" AS "initiative_id",
1884 max("draft"."id") AS "draft_id"
1885 FROM "initiative" JOIN "draft"
1886 ON "initiative"."id" = "draft"."initiative_id"
1887 GROUP BY "initiative"."id"
1888 ) AS "subquery"
1889 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1891 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1894 CREATE VIEW "critical_opinion" AS
1895 SELECT * FROM "opinion"
1896 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
1897 OR ("degree" = -2 AND "fulfilled" = TRUE);
1899 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
1902 CREATE VIEW "battle_participant" AS
1903 SELECT "initiative"."id", "initiative"."issue_id"
1904 FROM "issue" JOIN "initiative"
1905 ON "issue"."id" = "initiative"."issue_id"
1906 WHERE "initiative"."admitted"
1907 UNION ALL
1908 SELECT NULL, "id" AS "issue_id"
1909 FROM "issue";
1911 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
1914 CREATE VIEW "battle_view" AS
1915 SELECT
1916 "issue"."id" AS "issue_id",
1917 "winning_initiative"."id" AS "winning_initiative_id",
1918 "losing_initiative"."id" AS "losing_initiative_id",
1919 sum(
1920 CASE WHEN
1921 coalesce("better_vote"."grade", 0) >
1922 coalesce("worse_vote"."grade", 0)
1923 THEN "direct_voter"."weight" ELSE 0 END
1924 ) AS "count"
1925 FROM "issue"
1926 LEFT JOIN "direct_voter"
1927 ON "issue"."id" = "direct_voter"."issue_id"
1928 JOIN "battle_participant" AS "winning_initiative"
1929 ON "issue"."id" = "winning_initiative"."issue_id"
1930 JOIN "battle_participant" AS "losing_initiative"
1931 ON "issue"."id" = "losing_initiative"."issue_id"
1932 LEFT JOIN "vote" AS "better_vote"
1933 ON "direct_voter"."member_id" = "better_vote"."member_id"
1934 AND "winning_initiative"."id" = "better_vote"."initiative_id"
1935 LEFT JOIN "vote" AS "worse_vote"
1936 ON "direct_voter"."member_id" = "worse_vote"."member_id"
1937 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
1938 WHERE "issue"."closed" NOTNULL
1939 AND "issue"."cleaned" ISNULL
1940 AND (
1941 "winning_initiative"."id" != "losing_initiative"."id" OR
1942 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
1943 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
1944 GROUP BY
1945 "issue"."id",
1946 "winning_initiative"."id",
1947 "losing_initiative"."id";
1949 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';
1952 CREATE VIEW "expired_session" AS
1953 SELECT * FROM "session" WHERE now() > "expiry";
1955 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
1956 DELETE FROM "session" WHERE "ident" = OLD."ident";
1958 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
1959 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
1962 CREATE VIEW "open_issue" AS
1963 SELECT * FROM "issue" WHERE "closed" ISNULL;
1965 COMMENT ON VIEW "open_issue" IS 'All open issues';
1968 CREATE VIEW "issue_with_ranks_missing" AS
1969 SELECT * FROM "issue"
1970 WHERE "fully_frozen" NOTNULL
1971 AND "closed" NOTNULL
1972 AND "ranks_available" = FALSE;
1974 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
1977 CREATE VIEW "member_contingent" AS
1978 SELECT
1979 "member"."id" AS "member_id",
1980 "contingent"."time_frame",
1981 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
1982 (
1983 SELECT count(1) FROM "draft"
1984 WHERE "draft"."author_id" = "member"."id"
1985 AND "draft"."created" > now() - "contingent"."time_frame"
1986 ) + (
1987 SELECT count(1) FROM "suggestion"
1988 WHERE "suggestion"."author_id" = "member"."id"
1989 AND "suggestion"."created" > now() - "contingent"."time_frame"
1990 )
1991 ELSE NULL END AS "text_entry_count",
1992 "contingent"."text_entry_limit",
1993 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
1994 SELECT count(1) FROM "opening_draft"
1995 WHERE "opening_draft"."author_id" = "member"."id"
1996 AND "opening_draft"."created" > now() - "contingent"."time_frame"
1997 ) ELSE NULL END AS "initiative_count",
1998 "contingent"."initiative_limit"
1999 FROM "member" CROSS JOIN "contingent";
2001 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
2003 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
2004 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
2007 CREATE VIEW "member_contingent_left" AS
2008 SELECT
2009 "member_id",
2010 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
2011 max("initiative_limit" - "initiative_count") AS "initiatives_left"
2012 FROM "member_contingent" GROUP BY "member_id";
2014 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.';
2017 CREATE VIEW "event_seen_by_member" AS
2018 SELECT
2019 "member"."id" AS "seen_by_member_id",
2020 CASE WHEN "event"."state" IN (
2021 'voting',
2022 'finished_without_winner',
2023 'finished_with_winner'
2024 ) THEN
2025 'voting'::"notify_level"
2026 ELSE
2027 CASE WHEN "event"."state" IN (
2028 'verification',
2029 'canceled_after_revocation_during_verification',
2030 'canceled_no_initiative_admitted'
2031 ) THEN
2032 'verification'::"notify_level"
2033 ELSE
2034 CASE WHEN "event"."state" IN (
2035 'discussion',
2036 'canceled_after_revocation_during_discussion'
2037 ) THEN
2038 'discussion'::"notify_level"
2039 ELSE
2040 'all'::"notify_level"
2041 END
2042 END
2043 END AS "notify_level",
2044 "event".*
2045 FROM "member" CROSS JOIN "event"
2046 LEFT JOIN "issue"
2047 ON "event"."issue_id" = "issue"."id"
2048 LEFT JOIN "membership"
2049 ON "member"."id" = "membership"."member_id"
2050 AND "issue"."area_id" = "membership"."area_id"
2051 LEFT JOIN "interest"
2052 ON "member"."id" = "interest"."member_id"
2053 AND "event"."issue_id" = "interest"."issue_id"
2054 LEFT JOIN "supporter"
2055 ON "member"."id" = "supporter"."member_id"
2056 AND "event"."initiative_id" = "supporter"."initiative_id"
2057 LEFT JOIN "ignored_member"
2058 ON "member"."id" = "ignored_member"."member_id"
2059 AND "event"."member_id" = "ignored_member"."other_member_id"
2060 LEFT JOIN "ignored_initiative"
2061 ON "member"."id" = "ignored_initiative"."member_id"
2062 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2063 WHERE (
2064 "supporter"."member_id" NOTNULL OR
2065 "interest"."member_id" NOTNULL OR
2066 ( "membership"."member_id" NOTNULL AND
2067 "event"."event" IN (
2068 'issue_state_changed',
2069 'initiative_created_in_new_issue',
2070 'initiative_created_in_existing_issue',
2071 'initiative_revoked' ) ) )
2072 AND "ignored_member"."member_id" ISNULL
2073 AND "ignored_initiative"."member_id" ISNULL;
2075 COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support';
2078 CREATE VIEW "pending_notification" AS
2079 SELECT
2080 "member"."id" AS "seen_by_member_id",
2081 "event".*
2082 FROM "member" CROSS JOIN "event"
2083 LEFT JOIN "issue"
2084 ON "event"."issue_id" = "issue"."id"
2085 LEFT JOIN "membership"
2086 ON "member"."id" = "membership"."member_id"
2087 AND "issue"."area_id" = "membership"."area_id"
2088 LEFT JOIN "interest"
2089 ON "member"."id" = "interest"."member_id"
2090 AND "event"."issue_id" = "interest"."issue_id"
2091 LEFT JOIN "supporter"
2092 ON "member"."id" = "supporter"."member_id"
2093 AND "event"."initiative_id" = "supporter"."initiative_id"
2094 LEFT JOIN "ignored_member"
2095 ON "member"."id" = "ignored_member"."member_id"
2096 AND "event"."member_id" = "ignored_member"."other_member_id"
2097 LEFT JOIN "ignored_initiative"
2098 ON "member"."id" = "ignored_initiative"."member_id"
2099 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2100 WHERE (
2101 "member"."notify_event_id" ISNULL OR
2102 ( "member"."notify_event_id" NOTNULL AND
2103 "member"."notify_event_id" < "event"."id" ) )
2104 AND (
2105 ( "member"."notify_level" >= 'all' ) OR
2106 ( "member"."notify_level" >= 'voting' AND
2107 "event"."state" IN (
2108 'voting',
2109 'finished_without_winner',
2110 'finished_with_winner' ) ) OR
2111 ( "member"."notify_level" >= 'verification' AND
2112 "event"."state" IN (
2113 'verification',
2114 'canceled_after_revocation_during_verification',
2115 'canceled_no_initiative_admitted' ) ) OR
2116 ( "member"."notify_level" >= 'discussion' AND
2117 "event"."state" IN (
2118 'discussion',
2119 'canceled_after_revocation_during_discussion' ) ) )
2120 AND (
2121 "supporter"."member_id" NOTNULL OR
2122 "interest"."member_id" NOTNULL OR
2123 ( "membership"."member_id" NOTNULL AND
2124 "event"."event" IN (
2125 'issue_state_changed',
2126 'initiative_created_in_new_issue',
2127 'initiative_created_in_existing_issue',
2128 'initiative_revoked' ) ) )
2129 AND "ignored_member"."member_id" ISNULL
2130 AND "ignored_initiative"."member_id" ISNULL;
2132 COMMENT ON VIEW "pending_notification" IS 'Events to be sent to "notify_email" address of member referred to by "seen_by_member_id"';
2135 CREATE TYPE "timeline_event" AS ENUM (
2136 'issue_created',
2137 'issue_canceled',
2138 'issue_accepted',
2139 'issue_half_frozen',
2140 'issue_finished_without_voting',
2141 'issue_voting_started',
2142 'issue_finished_after_voting',
2143 'initiative_created',
2144 'initiative_revoked',
2145 'draft_created',
2146 'suggestion_created');
2148 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)';
2151 CREATE VIEW "timeline_issue" AS
2152 SELECT
2153 "created" AS "occurrence",
2154 'issue_created'::"timeline_event" AS "event",
2155 "id" AS "issue_id"
2156 FROM "issue"
2157 UNION ALL
2158 SELECT
2159 "closed" AS "occurrence",
2160 'issue_canceled'::"timeline_event" AS "event",
2161 "id" AS "issue_id"
2162 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
2163 UNION ALL
2164 SELECT
2165 "accepted" AS "occurrence",
2166 'issue_accepted'::"timeline_event" AS "event",
2167 "id" AS "issue_id"
2168 FROM "issue" WHERE "accepted" NOTNULL
2169 UNION ALL
2170 SELECT
2171 "half_frozen" AS "occurrence",
2172 'issue_half_frozen'::"timeline_event" AS "event",
2173 "id" AS "issue_id"
2174 FROM "issue" WHERE "half_frozen" NOTNULL
2175 UNION ALL
2176 SELECT
2177 "fully_frozen" AS "occurrence",
2178 'issue_voting_started'::"timeline_event" AS "event",
2179 "id" AS "issue_id"
2180 FROM "issue"
2181 WHERE "fully_frozen" NOTNULL
2182 AND ("closed" ISNULL OR "closed" != "fully_frozen")
2183 UNION ALL
2184 SELECT
2185 "closed" AS "occurrence",
2186 CASE WHEN "fully_frozen" = "closed" THEN
2187 'issue_finished_without_voting'::"timeline_event"
2188 ELSE
2189 'issue_finished_after_voting'::"timeline_event"
2190 END AS "event",
2191 "id" AS "issue_id"
2192 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
2194 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)';
2197 CREATE VIEW "timeline_initiative" AS
2198 SELECT
2199 "created" AS "occurrence",
2200 'initiative_created'::"timeline_event" AS "event",
2201 "id" AS "initiative_id"
2202 FROM "initiative"
2203 UNION ALL
2204 SELECT
2205 "revoked" AS "occurrence",
2206 'initiative_revoked'::"timeline_event" AS "event",
2207 "id" AS "initiative_id"
2208 FROM "initiative" WHERE "revoked" NOTNULL;
2210 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)';
2213 CREATE VIEW "timeline_draft" AS
2214 SELECT
2215 "created" AS "occurrence",
2216 'draft_created'::"timeline_event" AS "event",
2217 "id" AS "draft_id"
2218 FROM "draft";
2220 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)';
2223 CREATE VIEW "timeline_suggestion" AS
2224 SELECT
2225 "created" AS "occurrence",
2226 'suggestion_created'::"timeline_event" AS "event",
2227 "id" AS "suggestion_id"
2228 FROM "suggestion";
2230 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)';
2233 CREATE VIEW "timeline" AS
2234 SELECT
2235 "occurrence",
2236 "event",
2237 "issue_id",
2238 NULL AS "initiative_id",
2239 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
2240 NULL::INT8 AS "suggestion_id"
2241 FROM "timeline_issue"
2242 UNION ALL
2243 SELECT
2244 "occurrence",
2245 "event",
2246 NULL AS "issue_id",
2247 "initiative_id",
2248 NULL AS "draft_id",
2249 NULL AS "suggestion_id"
2250 FROM "timeline_initiative"
2251 UNION ALL
2252 SELECT
2253 "occurrence",
2254 "event",
2255 NULL AS "issue_id",
2256 NULL AS "initiative_id",
2257 "draft_id",
2258 NULL AS "suggestion_id"
2259 FROM "timeline_draft"
2260 UNION ALL
2261 SELECT
2262 "occurrence",
2263 "event",
2264 NULL AS "issue_id",
2265 NULL AS "initiative_id",
2266 NULL AS "draft_id",
2267 "suggestion_id"
2268 FROM "timeline_suggestion";
2270 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)';
2274 --------------------------------------------------
2275 -- Set returning function for delegation chains --
2276 --------------------------------------------------
2279 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
2280 ('first', 'intermediate', 'last', 'repetition');
2282 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
2285 CREATE TYPE "delegation_chain_row" AS (
2286 "index" INT4,
2287 "member_id" INT4,
2288 "member_valid" BOOLEAN,
2289 "participation" BOOLEAN,
2290 "overridden" BOOLEAN,
2291 "scope_in" "delegation_scope",
2292 "scope_out" "delegation_scope",
2293 "disabled_out" BOOLEAN,
2294 "loop" "delegation_chain_loop_tag" );
2296 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
2298 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
2299 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';
2300 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
2301 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
2302 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
2303 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
2304 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
2307 CREATE FUNCTION "delegation_chain"
2308 ( "member_id_p" "member"."id"%TYPE,
2309 "unit_id_p" "unit"."id"%TYPE,
2310 "area_id_p" "area"."id"%TYPE,
2311 "issue_id_p" "issue"."id"%TYPE,
2312 "simulate_trustee_id_p" "member"."id"%TYPE )
2313 RETURNS SETOF "delegation_chain_row"
2314 LANGUAGE 'plpgsql' STABLE AS $$
2315 DECLARE
2316 "scope_v" "delegation_scope";
2317 "unit_id_v" "unit"."id"%TYPE;
2318 "area_id_v" "area"."id"%TYPE;
2319 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2320 "loop_member_id_v" "member"."id"%TYPE;
2321 "output_row" "delegation_chain_row";
2322 "output_rows" "delegation_chain_row"[];
2323 "delegation_row" "delegation"%ROWTYPE;
2324 "row_count" INT4;
2325 "i" INT4;
2326 "loop_v" BOOLEAN;
2327 BEGIN
2328 IF
2329 "unit_id_p" NOTNULL AND
2330 "area_id_p" ISNULL AND
2331 "issue_id_p" ISNULL
2332 THEN
2333 "scope_v" := 'unit';
2334 "unit_id_v" := "unit_id_p";
2335 ELSIF
2336 "unit_id_p" ISNULL AND
2337 "area_id_p" NOTNULL AND
2338 "issue_id_p" ISNULL
2339 THEN
2340 "scope_v" := 'area';
2341 "area_id_v" := "area_id_p";
2342 SELECT "unit_id" INTO "unit_id_v"
2343 FROM "area" WHERE "id" = "area_id_v";
2344 ELSIF
2345 "unit_id_p" ISNULL AND
2346 "area_id_p" ISNULL AND
2347 "issue_id_p" NOTNULL
2348 THEN
2349 "scope_v" := 'issue';
2350 SELECT "area_id" INTO "area_id_v"
2351 FROM "issue" WHERE "id" = "issue_id_p";
2352 SELECT "unit_id" INTO "unit_id_v"
2353 FROM "area" WHERE "id" = "area_id_v";
2354 ELSE
2355 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2356 END IF;
2357 "visited_member_ids" := '{}';
2358 "loop_member_id_v" := NULL;
2359 "output_rows" := '{}';
2360 "output_row"."index" := 0;
2361 "output_row"."member_id" := "member_id_p";
2362 "output_row"."member_valid" := TRUE;
2363 "output_row"."participation" := FALSE;
2364 "output_row"."overridden" := FALSE;
2365 "output_row"."disabled_out" := FALSE;
2366 "output_row"."scope_out" := NULL;
2367 LOOP
2368 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2369 "loop_member_id_v" := "output_row"."member_id";
2370 ELSE
2371 "visited_member_ids" :=
2372 "visited_member_ids" || "output_row"."member_id";
2373 END IF;
2374 IF "output_row"."participation" THEN
2375 "output_row"."overridden" := TRUE;
2376 END IF;
2377 "output_row"."scope_in" := "output_row"."scope_out";
2378 IF EXISTS (
2379 SELECT NULL FROM "member" JOIN "privilege"
2380 ON "privilege"."member_id" = "member"."id"
2381 AND "privilege"."unit_id" = "unit_id_v"
2382 WHERE "id" = "output_row"."member_id"
2383 AND "member"."active" AND "privilege"."voting_right"
2384 ) THEN
2385 IF "scope_v" = 'unit' THEN
2386 SELECT * INTO "delegation_row" FROM "delegation"
2387 WHERE "truster_id" = "output_row"."member_id"
2388 AND "unit_id" = "unit_id_v";
2389 ELSIF "scope_v" = 'area' THEN
2390 "output_row"."participation" := EXISTS (
2391 SELECT NULL FROM "membership"
2392 WHERE "area_id" = "area_id_p"
2393 AND "member_id" = "output_row"."member_id"
2394 );
2395 SELECT * INTO "delegation_row" FROM "delegation"
2396 WHERE "truster_id" = "output_row"."member_id"
2397 AND (
2398 "unit_id" = "unit_id_v" OR
2399 "area_id" = "area_id_v"
2400 )
2401 ORDER BY "scope" DESC;
2402 ELSIF "scope_v" = 'issue' THEN
2403 "output_row"."participation" := EXISTS (
2404 SELECT NULL FROM "interest"
2405 WHERE "issue_id" = "issue_id_p"
2406 AND "member_id" = "output_row"."member_id"
2407 );
2408 SELECT * INTO "delegation_row" FROM "delegation"
2409 WHERE "truster_id" = "output_row"."member_id"
2410 AND (
2411 "unit_id" = "unit_id_v" OR
2412 "area_id" = "area_id_v" OR
2413 "issue_id" = "issue_id_p"
2414 )
2415 ORDER BY "scope" DESC;
2416 END IF;
2417 ELSE
2418 "output_row"."member_valid" := FALSE;
2419 "output_row"."participation" := FALSE;
2420 "output_row"."scope_out" := NULL;
2421 "delegation_row" := ROW(NULL);
2422 END IF;
2423 IF
2424 "output_row"."member_id" = "member_id_p" AND
2425 "simulate_trustee_id_p" NOTNULL
2426 THEN
2427 "output_row"."scope_out" := "scope_v";
2428 "output_rows" := "output_rows" || "output_row";
2429 "output_row"."member_id" := "simulate_trustee_id_p";
2430 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2431 "output_row"."scope_out" := "delegation_row"."scope";
2432 "output_rows" := "output_rows" || "output_row";
2433 "output_row"."member_id" := "delegation_row"."trustee_id";
2434 ELSIF "delegation_row"."scope" NOTNULL THEN
2435 "output_row"."scope_out" := "delegation_row"."scope";
2436 "output_row"."disabled_out" := TRUE;
2437 "output_rows" := "output_rows" || "output_row";
2438 EXIT;
2439 ELSE
2440 "output_row"."scope_out" := NULL;
2441 "output_rows" := "output_rows" || "output_row";
2442 EXIT;
2443 END IF;
2444 EXIT WHEN "loop_member_id_v" NOTNULL;
2445 "output_row"."index" := "output_row"."index" + 1;
2446 END LOOP;
2447 "row_count" := array_upper("output_rows", 1);
2448 "i" := 1;
2449 "loop_v" := FALSE;
2450 LOOP
2451 "output_row" := "output_rows"["i"];
2452 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2453 IF "loop_v" THEN
2454 IF "i" + 1 = "row_count" THEN
2455 "output_row"."loop" := 'last';
2456 ELSIF "i" = "row_count" THEN
2457 "output_row"."loop" := 'repetition';
2458 ELSE
2459 "output_row"."loop" := 'intermediate';
2460 END IF;
2461 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2462 "output_row"."loop" := 'first';
2463 "loop_v" := TRUE;
2464 END IF;
2465 IF "scope_v" = 'unit' THEN
2466 "output_row"."participation" := NULL;
2467 END IF;
2468 RETURN NEXT "output_row";
2469 "i" := "i" + 1;
2470 END LOOP;
2471 RETURN;
2472 END;
2473 $$;
2475 COMMENT ON FUNCTION "delegation_chain"
2476 ( "member"."id"%TYPE,
2477 "unit"."id"%TYPE,
2478 "area"."id"%TYPE,
2479 "issue"."id"%TYPE,
2480 "member"."id"%TYPE )
2481 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
2484 CREATE FUNCTION "delegation_chain"
2485 ( "member_id_p" "member"."id"%TYPE,
2486 "unit_id_p" "unit"."id"%TYPE,
2487 "area_id_p" "area"."id"%TYPE,
2488 "issue_id_p" "issue"."id"%TYPE )
2489 RETURNS SETOF "delegation_chain_row"
2490 LANGUAGE 'plpgsql' STABLE AS $$
2491 DECLARE
2492 "result_row" "delegation_chain_row";
2493 BEGIN
2494 FOR "result_row" IN
2495 SELECT * FROM "delegation_chain"(
2496 "member_id_p", "unit_id_p", "area_id_p", "issue_id_p", NULL
2497 )
2498 LOOP
2499 RETURN NEXT "result_row";
2500 END LOOP;
2501 RETURN;
2502 END;
2503 $$;
2505 COMMENT ON FUNCTION "delegation_chain"
2506 ( "member"."id"%TYPE,
2507 "unit"."id"%TYPE,
2508 "area"."id"%TYPE,
2509 "issue"."id"%TYPE )
2510 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
2514 ------------------------------
2515 -- Comparison by vote count --
2516 ------------------------------
2518 CREATE FUNCTION "vote_ratio"
2519 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
2520 "negative_votes_p" "initiative"."negative_votes"%TYPE )
2521 RETURNS FLOAT8
2522 LANGUAGE 'plpgsql' STABLE AS $$
2523 BEGIN
2524 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
2525 RETURN
2526 "positive_votes_p"::FLOAT8 /
2527 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
2528 ELSIF "positive_votes_p" > 0 THEN
2529 RETURN "positive_votes_p";
2530 ELSIF "negative_votes_p" > 0 THEN
2531 RETURN 1 - "negative_votes_p";
2532 ELSE
2533 RETURN 0.5;
2534 END IF;
2535 END;
2536 $$;
2538 COMMENT ON FUNCTION "vote_ratio"
2539 ( "initiative"."positive_votes"%TYPE,
2540 "initiative"."negative_votes"%TYPE )
2541 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.';
2545 ------------------------------------------------
2546 -- Locking for snapshots and voting procedure --
2547 ------------------------------------------------
2550 CREATE FUNCTION "share_row_lock_issue_trigger"()
2551 RETURNS TRIGGER
2552 LANGUAGE 'plpgsql' VOLATILE AS $$
2553 BEGIN
2554 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2555 PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;
2556 END IF;
2557 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2558 PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;
2559 RETURN NEW;
2560 ELSE
2561 RETURN OLD;
2562 END IF;
2563 END;
2564 $$;
2566 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';
2569 CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()
2570 RETURNS TRIGGER
2571 LANGUAGE 'plpgsql' VOLATILE AS $$
2572 BEGIN
2573 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2574 PERFORM NULL FROM "issue"
2575 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2576 WHERE "initiative"."id" = OLD."initiative_id"
2577 FOR SHARE OF "issue";
2578 END IF;
2579 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2580 PERFORM NULL FROM "issue"
2581 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2582 WHERE "initiative"."id" = NEW."initiative_id"
2583 FOR SHARE OF "issue";
2584 RETURN NEW;
2585 ELSE
2586 RETURN OLD;
2587 END IF;
2588 END;
2589 $$;
2591 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';
2594 CREATE TRIGGER "share_row_lock_issue"
2595 BEFORE INSERT OR UPDATE OR DELETE ON "initiative"
2596 FOR EACH ROW EXECUTE PROCEDURE
2597 "share_row_lock_issue_trigger"();
2599 CREATE TRIGGER "share_row_lock_issue"
2600 BEFORE INSERT OR UPDATE OR DELETE ON "interest"
2601 FOR EACH ROW EXECUTE PROCEDURE
2602 "share_row_lock_issue_trigger"();
2604 CREATE TRIGGER "share_row_lock_issue"
2605 BEFORE INSERT OR UPDATE OR DELETE ON "supporter"
2606 FOR EACH ROW EXECUTE PROCEDURE
2607 "share_row_lock_issue_trigger"();
2609 CREATE TRIGGER "share_row_lock_issue_via_initiative"
2610 BEFORE INSERT OR UPDATE OR DELETE ON "opinion"
2611 FOR EACH ROW EXECUTE PROCEDURE
2612 "share_row_lock_issue_via_initiative_trigger"();
2614 CREATE TRIGGER "share_row_lock_issue"
2615 BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter"
2616 FOR EACH ROW EXECUTE PROCEDURE
2617 "share_row_lock_issue_trigger"();
2619 CREATE TRIGGER "share_row_lock_issue"
2620 BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter"
2621 FOR EACH ROW EXECUTE PROCEDURE
2622 "share_row_lock_issue_trigger"();
2624 CREATE TRIGGER "share_row_lock_issue"
2625 BEFORE INSERT OR UPDATE OR DELETE ON "vote"
2626 FOR EACH ROW EXECUTE PROCEDURE
2627 "share_row_lock_issue_trigger"();
2629 COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function';
2630 COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function';
2631 COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function';
2632 COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function';
2633 COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function';
2634 COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function';
2635 COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function';
2638 CREATE FUNCTION "lock_issue"
2639 ( "issue_id_p" "issue"."id"%TYPE )
2640 RETURNS VOID
2641 LANGUAGE 'plpgsql' VOLATILE AS $$
2642 BEGIN
2643 LOCK TABLE "member" IN SHARE MODE;
2644 LOCK TABLE "privilege" IN SHARE MODE;
2645 LOCK TABLE "membership" IN SHARE MODE;
2646 LOCK TABLE "policy" IN SHARE MODE;
2647 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2648 -- NOTE: The row-level exclusive lock in combination with the
2649 -- share_row_lock_issue(_via_initiative)_trigger functions (which
2650 -- acquire a row-level share lock on the issue) ensure that no data
2651 -- is changed, which could affect calculation of snapshots or
2652 -- counting of votes. Table "delegation" must be table-level-locked,
2653 -- as it also contains issue- and global-scope delegations.
2654 LOCK TABLE "delegation" IN SHARE MODE;
2655 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
2656 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
2657 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
2658 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
2659 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
2660 RETURN;
2661 END;
2662 $$;
2664 COMMENT ON FUNCTION "lock_issue"
2665 ( "issue"."id"%TYPE )
2666 IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';
2670 ------------------------------------------------------------------------
2671 -- Regular tasks, except calculcation of snapshots and voting results --
2672 ------------------------------------------------------------------------
2674 CREATE FUNCTION "check_last_login"()
2675 RETURNS VOID
2676 LANGUAGE 'plpgsql' VOLATILE AS $$
2677 DECLARE
2678 "system_setting_row" "system_setting"%ROWTYPE;
2679 BEGIN
2680 SELECT * INTO "system_setting_row" FROM "system_setting";
2681 LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
2682 UPDATE "member" SET "last_login_public" = "last_login"::date
2683 FROM (
2684 SELECT DISTINCT "member"."id"
2685 FROM "member" LEFT JOIN "member_history"
2686 ON "member"."id" = "member_history"."member_id"
2687 WHERE "member"."last_login"::date < 'today' OR (
2688 "member_history"."until"::date >= 'today' AND
2689 "member_history"."active" = FALSE AND "member"."active" = TRUE
2690 )
2691 ) AS "subquery"
2692 WHERE "member"."id" = "subquery"."id";
2693 IF "system_setting_row"."member_ttl" NOTNULL THEN
2694 UPDATE "member" SET "active" = FALSE
2695 WHERE "active" = TRUE
2696 AND "last_login"::date < 'today'
2697 AND "last_login_public" <
2698 (now() - "system_setting_row"."member_ttl")::date;
2699 END IF;
2700 RETURN;
2701 END;
2702 $$;
2704 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).';
2707 CREATE FUNCTION "calculate_member_counts"()
2708 RETURNS VOID
2709 LANGUAGE 'plpgsql' VOLATILE AS $$
2710 BEGIN
2711 LOCK TABLE "member" IN SHARE MODE;
2712 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
2713 LOCK TABLE "unit" IN EXCLUSIVE MODE;
2714 LOCK TABLE "area" IN EXCLUSIVE MODE;
2715 LOCK TABLE "privilege" IN SHARE MODE;
2716 LOCK TABLE "membership" IN SHARE MODE;
2717 DELETE FROM "member_count";
2718 INSERT INTO "member_count" ("total_count")
2719 SELECT "total_count" FROM "member_count_view";
2720 UPDATE "unit" SET "member_count" = "view"."member_count"
2721 FROM "unit_member_count" AS "view"
2722 WHERE "view"."unit_id" = "unit"."id";
2723 UPDATE "area" SET
2724 "direct_member_count" = "view"."direct_member_count",
2725 "member_weight" = "view"."member_weight",
2726 "autoreject_weight" = "view"."autoreject_weight"
2727 FROM "area_member_count" AS "view"
2728 WHERE "view"."area_id" = "area"."id";
2729 RETURN;
2730 END;
2731 $$;
2733 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"';
2737 ------------------------------
2738 -- Calculation of snapshots --
2739 ------------------------------
2741 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
2742 ( "issue_id_p" "issue"."id"%TYPE,
2743 "member_id_p" "member"."id"%TYPE,
2744 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2745 RETURNS "direct_population_snapshot"."weight"%TYPE
2746 LANGUAGE 'plpgsql' VOLATILE AS $$
2747 DECLARE
2748 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2749 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
2750 "weight_v" INT4;
2751 "sub_weight_v" INT4;
2752 BEGIN
2753 "weight_v" := 0;
2754 FOR "issue_delegation_row" IN
2755 SELECT * FROM "issue_delegation"
2756 WHERE "trustee_id" = "member_id_p"
2757 AND "issue_id" = "issue_id_p"
2758 LOOP
2759 IF NOT EXISTS (
2760 SELECT NULL FROM "direct_population_snapshot"
2761 WHERE "issue_id" = "issue_id_p"
2762 AND "event" = 'periodic'
2763 AND "member_id" = "issue_delegation_row"."truster_id"
2764 ) AND NOT EXISTS (
2765 SELECT NULL FROM "delegating_population_snapshot"
2766 WHERE "issue_id" = "issue_id_p"
2767 AND "event" = 'periodic'
2768 AND "member_id" = "issue_delegation_row"."truster_id"
2769 ) THEN
2770 "delegate_member_ids_v" :=
2771 "member_id_p" || "delegate_member_ids_p";
2772 INSERT INTO "delegating_population_snapshot" (
2773 "issue_id",
2774 "event",
2775 "member_id",
2776 "scope",
2777 "delegate_member_ids"
2778 ) VALUES (
2779 "issue_id_p",
2780 'periodic',
2781 "issue_delegation_row"."truster_id",
2782 "issue_delegation_row"."scope",
2783 "delegate_member_ids_v"
2784 );
2785 "sub_weight_v" := 1 +
2786 "weight_of_added_delegations_for_population_snapshot"(
2787 "issue_id_p",
2788 "issue_delegation_row"."truster_id",
2789 "delegate_member_ids_v"
2790 );
2791 UPDATE "delegating_population_snapshot"
2792 SET "weight" = "sub_weight_v"
2793 WHERE "issue_id" = "issue_id_p"
2794 AND "event" = 'periodic'
2795 AND "member_id" = "issue_delegation_row"."truster_id";
2796 "weight_v" := "weight_v" + "sub_weight_v";
2797 END IF;
2798 END LOOP;
2799 RETURN "weight_v";
2800 END;
2801 $$;
2803 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
2804 ( "issue"."id"%TYPE,
2805 "member"."id"%TYPE,
2806 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2807 IS 'Helper function for "create_population_snapshot" function';
2810 CREATE FUNCTION "create_population_snapshot"
2811 ( "issue_id_p" "issue"."id"%TYPE )
2812 RETURNS VOID
2813 LANGUAGE 'plpgsql' VOLATILE AS $$
2814 DECLARE
2815 "member_id_v" "member"."id"%TYPE;
2816 BEGIN
2817 DELETE FROM "direct_population_snapshot"
2818 WHERE "issue_id" = "issue_id_p"
2819 AND "event" = 'periodic';
2820 DELETE FROM "delegating_population_snapshot"
2821 WHERE "issue_id" = "issue_id_p"
2822 AND "event" = 'periodic';
2823 INSERT INTO "direct_population_snapshot"
2824 ("issue_id", "event", "member_id")
2825 SELECT
2826 "issue_id_p" AS "issue_id",
2827 'periodic'::"snapshot_event" AS "event",
2828 "member"."id" AS "member_id"
2829 FROM "issue"
2830 JOIN "area" ON "issue"."area_id" = "area"."id"
2831 JOIN "membership" ON "area"."id" = "membership"."area_id"
2832 JOIN "member" ON "membership"."member_id" = "member"."id"
2833 JOIN "privilege"
2834 ON "privilege"."unit_id" = "area"."unit_id"
2835 AND "privilege"."member_id" = "member"."id"
2836 WHERE "issue"."id" = "issue_id_p"
2837 AND "member"."active" AND "privilege"."voting_right"
2838 UNION
2839 SELECT
2840 "issue_id_p" AS "issue_id",
2841 'periodic'::"snapshot_event" AS "event",
2842 "member"."id" AS "member_id"
2843 FROM "issue"
2844 JOIN "area" ON "issue"."area_id" = "area"."id"
2845 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2846 JOIN "member" ON "interest"."member_id" = "member"."id"
2847 JOIN "privilege"
2848 ON "privilege"."unit_id" = "area"."unit_id"
2849 AND "privilege"."member_id" = "member"."id"
2850 WHERE "issue"."id" = "issue_id_p"
2851 AND "member"."active" AND "privilege"."voting_right"
2852 UNION
2853 SELECT
2854 "issue_id_p" AS "issue_id",
2855 'periodic'::"snapshot_event" AS "event",
2856 "member"."id" AS "member_id"
2857 FROM "issue"
2858 JOIN "area" ON "issue"."area_id" = "area"."id"
2859 JOIN "issue_autoreject" ON "issue"."id" = "issue_autoreject"."issue_id"
2860 JOIN "member" ON "issue_autoreject"."member_id" = "member"."id"
2861 JOIN "privilege"
2862 ON "privilege"."unit_id" = "area"."unit_id"
2863 AND "privilege"."member_id" = "member"."id"
2864 WHERE "issue"."id" = "issue_id_p"
2865 AND "member"."active" AND "privilege"."voting_right";
2866 FOR "member_id_v" IN
2867 SELECT "member_id" FROM "direct_population_snapshot"
2868 WHERE "issue_id" = "issue_id_p"
2869 AND "event" = 'periodic'
2870 LOOP
2871 UPDATE "direct_population_snapshot" SET
2872 "weight" = 1 +
2873 "weight_of_added_delegations_for_population_snapshot"(
2874 "issue_id_p",
2875 "member_id_v",
2876 '{}'
2877 )
2878 WHERE "issue_id" = "issue_id_p"
2879 AND "event" = 'periodic'
2880 AND "member_id" = "member_id_v";
2881 END LOOP;
2882 RETURN;
2883 END;
2884 $$;
2886 COMMENT ON FUNCTION "create_population_snapshot"
2887 ( "issue"."id"%TYPE )
2888 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.';
2891 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2892 ( "issue_id_p" "issue"."id"%TYPE,
2893 "member_id_p" "member"."id"%TYPE,
2894 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2895 RETURNS "direct_interest_snapshot"."weight"%TYPE
2896 LANGUAGE 'plpgsql' VOLATILE AS $$
2897 DECLARE
2898 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2899 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2900 "weight_v" INT4;
2901 "sub_weight_v" INT4;
2902 BEGIN
2903 "weight_v" := 0;
2904 FOR "issue_delegation_row" IN
2905 SELECT * FROM "issue_delegation"
2906 WHERE "trustee_id" = "member_id_p"
2907 AND "issue_id" = "issue_id_p"
2908 LOOP
2909 IF NOT EXISTS (
2910 SELECT NULL FROM "direct_interest_snapshot"
2911 WHERE "issue_id" = "issue_id_p"
2912 AND "event" = 'periodic'
2913 AND "member_id" = "issue_delegation_row"."truster_id"
2914 ) AND NOT EXISTS (
2915 SELECT NULL FROM "delegating_interest_snapshot"
2916 WHERE "issue_id" = "issue_id_p"
2917 AND "event" = 'periodic'
2918 AND "member_id" = "issue_delegation_row"."truster_id"
2919 ) THEN
2920 "delegate_member_ids_v" :=
2921 "member_id_p" || "delegate_member_ids_p";
2922 INSERT INTO "delegating_interest_snapshot" (
2923 "issue_id",
2924 "event",
2925 "member_id",
2926 "scope",
2927 "delegate_member_ids"
2928 ) VALUES (
2929 "issue_id_p",
2930 'periodic',
2931 "issue_delegation_row"."truster_id",
2932 "issue_delegation_row"."scope",
2933 "delegate_member_ids_v"
2934 );
2935 "sub_weight_v" := 1 +
2936 "weight_of_added_delegations_for_interest_snapshot"(
2937 "issue_id_p",
2938 "issue_delegation_row"."truster_id",
2939 "delegate_member_ids_v"
2940 );
2941 UPDATE "delegating_interest_snapshot"
2942 SET "weight" = "sub_weight_v"
2943 WHERE "issue_id" = "issue_id_p"
2944 AND "event" = 'periodic'
2945 AND "member_id" = "issue_delegation_row"."truster_id";
2946 "weight_v" := "weight_v" + "sub_weight_v";
2947 END IF;
2948 END LOOP;
2949 RETURN "weight_v";
2950 END;
2951 $$;
2953 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2954 ( "issue"."id"%TYPE,
2955 "member"."id"%TYPE,
2956 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2957 IS 'Helper function for "create_interest_snapshot" function';
2960 CREATE FUNCTION "create_interest_snapshot"
2961 ( "issue_id_p" "issue"."id"%TYPE )
2962 RETURNS VOID
2963 LANGUAGE 'plpgsql' VOLATILE AS $$
2964 DECLARE
2965 "member_id_v" "member"."id"%TYPE;
2966 BEGIN
2967 DELETE FROM "direct_interest_snapshot"
2968 WHERE "issue_id" = "issue_id_p"
2969 AND "event" = 'periodic';
2970 DELETE FROM "delegating_interest_snapshot"
2971 WHERE "issue_id" = "issue_id_p"
2972 AND "event" = 'periodic';
2973 DELETE FROM "direct_supporter_snapshot"
2974 WHERE "issue_id" = "issue_id_p"
2975 AND "event" = 'periodic';
2976 INSERT INTO "direct_interest_snapshot"
2977 ("issue_id", "event", "member_id")
2978 SELECT
2979 "issue_id_p" AS "issue_id",
2980 'periodic' AS "event",
2981 "member"."id" AS "member_id"
2982 FROM "issue"
2983 JOIN "area" ON "issue"."area_id" = "area"."id"
2984 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2985 JOIN "member" ON "interest"."member_id" = "member"."id"
2986 JOIN "privilege"
2987 ON "privilege"."unit_id" = "area"."unit_id"
2988 AND "privilege"."member_id" = "member"."id"
2989 WHERE "issue"."id" = "issue_id_p"
2990 AND "member"."active" AND "privilege"."voting_right";
2991 FOR "member_id_v" IN
2992 SELECT "member_id" FROM "direct_interest_snapshot"
2993 WHERE "issue_id" = "issue_id_p"
2994 AND "event" = 'periodic'
2995 LOOP
2996 UPDATE "direct_interest_snapshot" SET
2997 "weight" = 1 +
2998 "weight_of_added_delegations_for_interest_snapshot"(
2999 "issue_id_p",
3000 "member_id_v",
3001 '{}'
3002 )
3003 WHERE "issue_id" = "issue_id_p"
3004 AND "event" = 'periodic'
3005 AND "member_id" = "member_id_v";
3006 END LOOP;
3007 INSERT INTO "direct_supporter_snapshot"
3008 ( "issue_id", "initiative_id", "event", "member_id",
3009 "informed", "satisfied" )
3010 SELECT
3011 "issue_id_p" AS "issue_id",
3012 "initiative"."id" AS "initiative_id",
3013 'periodic' AS "event",
3014 "supporter"."member_id" AS "member_id",
3015 "supporter"."draft_id" = "current_draft"."id" AS "informed",
3016 NOT EXISTS (
3017 SELECT NULL FROM "critical_opinion"
3018 WHERE "initiative_id" = "initiative"."id"
3019 AND "member_id" = "supporter"."member_id"
3020 ) AS "satisfied"
3021 FROM "initiative"
3022 JOIN "supporter"
3023 ON "supporter"."initiative_id" = "initiative"."id"
3024 JOIN "current_draft"
3025 ON "initiative"."id" = "current_draft"."initiative_id"
3026 JOIN "direct_interest_snapshot"
3027 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
3028 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
3029 AND "event" = 'periodic'
3030 WHERE "initiative"."issue_id" = "issue_id_p";
3031 RETURN;
3032 END;
3033 $$;
3035 COMMENT ON FUNCTION "create_interest_snapshot"
3036 ( "issue"."id"%TYPE )
3037 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.';
3040 CREATE FUNCTION "create_snapshot"
3041 ( "issue_id_p" "issue"."id"%TYPE )
3042 RETURNS VOID
3043 LANGUAGE 'plpgsql' VOLATILE AS $$
3044 DECLARE
3045 "initiative_id_v" "initiative"."id"%TYPE;
3046 "suggestion_id_v" "suggestion"."id"%TYPE;
3047 BEGIN
3048 PERFORM "lock_issue"("issue_id_p");
3049 PERFORM "create_population_snapshot"("issue_id_p");
3050 PERFORM "create_interest_snapshot"("issue_id_p");
3051 UPDATE "issue" SET
3052 "snapshot" = now(),
3053 "latest_snapshot_event" = 'periodic',
3054 "population" = (
3055 SELECT coalesce(sum("weight"), 0)
3056 FROM "direct_population_snapshot"
3057 WHERE "issue_id" = "issue_id_p"
3058 AND "event" = 'periodic'
3059 )
3060 WHERE "id" = "issue_id_p";
3061 FOR "initiative_id_v" IN
3062 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
3063 LOOP
3064 UPDATE "initiative" SET
3065 "supporter_count" = (
3066 SELECT coalesce(sum("di"."weight"), 0)
3067 FROM "direct_interest_snapshot" AS "di"
3068 JOIN "direct_supporter_snapshot" AS "ds"
3069 ON "di"."member_id" = "ds"."member_id"
3070 WHERE "di"."issue_id" = "issue_id_p"
3071 AND "di"."event" = 'periodic'
3072 AND "ds"."initiative_id" = "initiative_id_v"
3073 AND "ds"."event" = 'periodic'
3074 ),
3075 "informed_supporter_count" = (
3076 SELECT coalesce(sum("di"."weight"), 0)
3077 FROM "direct_interest_snapshot" AS "di"
3078 JOIN "direct_supporter_snapshot" AS "ds"
3079 ON "di"."member_id" = "ds"."member_id"
3080 WHERE "di"."issue_id" = "issue_id_p"
3081 AND "di"."event" = 'periodic'
3082 AND "ds"."initiative_id" = "initiative_id_v"
3083 AND "ds"."event" = 'periodic'
3084 AND "ds"."informed"
3085 ),
3086 "satisfied_supporter_count" = (
3087 SELECT coalesce(sum("di"."weight"), 0)
3088 FROM "direct_interest_snapshot" AS "di"
3089 JOIN "direct_supporter_snapshot" AS "ds"
3090 ON "di"."member_id" = "ds"."member_id"
3091 WHERE "di"."issue_id" = "issue_id_p"
3092 AND "di"."event" = 'periodic'
3093 AND "ds"."initiative_id" = "initiative_id_v"
3094 AND "ds"."event" = 'periodic'
3095 AND "ds"."satisfied"
3096 ),
3097 "satisfied_informed_supporter_count" = (
3098 SELECT coalesce(sum("di"."weight"), 0)
3099 FROM "direct_interest_snapshot" AS "di"
3100 JOIN "direct_supporter_snapshot" AS "ds"
3101 ON "di"."member_id" = "ds"."member_id"
3102 WHERE "di"."issue_id" = "issue_id_p"
3103 AND "di"."event" = 'periodic'
3104 AND "ds"."initiative_id" = "initiative_id_v"
3105 AND "ds"."event" = 'periodic'
3106 AND "ds"."informed"
3107 AND "ds"."satisfied"
3108 )
3109 WHERE "id" = "initiative_id_v";
3110 FOR "suggestion_id_v" IN
3111 SELECT "id" FROM "suggestion"
3112 WHERE "initiative_id" = "initiative_id_v"
3113 LOOP
3114 UPDATE "suggestion" SET
3115 "minus2_unfulfilled_count" = (
3116 SELECT coalesce(sum("snapshot"."weight"), 0)
3117 FROM "issue" CROSS JOIN "opinion"
3118 JOIN "direct_interest_snapshot" AS "snapshot"
3119 ON "snapshot"."issue_id" = "issue"."id"
3120 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3121 AND "snapshot"."member_id" = "opinion"."member_id"
3122 WHERE "issue"."id" = "issue_id_p"
3123 AND "opinion"."suggestion_id" = "suggestion_id_v"
3124 AND "opinion"."degree" = -2
3125 AND "opinion"."fulfilled" = FALSE
3126 ),
3127 "minus2_fulfilled_count" = (
3128 SELECT coalesce(sum("snapshot"."weight"), 0)
3129 FROM "issue" CROSS JOIN "opinion"
3130 JOIN "direct_interest_snapshot" AS "snapshot"
3131 ON "snapshot"."issue_id" = "issue"."id"
3132 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3133 AND "snapshot"."member_id" = "opinion"."member_id"
3134 WHERE "issue"."id" = "issue_id_p"
3135 AND "opinion"."suggestion_id" = "suggestion_id_v"
3136 AND "opinion"."degree" = -2
3137 AND "opinion"."fulfilled" = TRUE
3138 ),
3139 "minus1_unfulfilled_count" = (
3140 SELECT coalesce(sum("snapshot"."weight"), 0)
3141 FROM "issue" CROSS JOIN "opinion"
3142 JOIN "direct_interest_snapshot" AS "snapshot"
3143 ON "snapshot"."issue_id" = "issue"."id"
3144 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3145 AND "snapshot"."member_id" = "opinion"."member_id"
3146 WHERE "issue"."id" = "issue_id_p"
3147 AND "opinion"."suggestion_id" = "suggestion_id_v"
3148 AND "opinion"."degree" = -1
3149 AND "opinion"."fulfilled" = FALSE
3150 ),
3151 "minus1_fulfilled_count" = (
3152 SELECT coalesce(sum("snapshot"."weight"), 0)
3153 FROM "issue" CROSS JOIN "opinion"
3154 JOIN "direct_interest_snapshot" AS "snapshot"
3155 ON "snapshot"."issue_id" = "issue"."id"
3156 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3157 AND "snapshot"."member_id" = "opinion"."member_id"
3158 WHERE "issue"."id" = "issue_id_p"
3159 AND "opinion"."suggestion_id" = "suggestion_id_v"
3160 AND "opinion"."degree" = -1
3161 AND "opinion"."fulfilled" = TRUE
3162 ),
3163 "plus1_unfulfilled_count" = (
3164 SELECT coalesce(sum("snapshot"."weight"), 0)
3165 FROM "issue" CROSS JOIN "opinion"
3166 JOIN "direct_interest_snapshot" AS "snapshot"
3167 ON "snapshot"."issue_id" = "issue"."id"
3168 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3169 AND "snapshot"."member_id" = "opinion"."member_id"
3170 WHERE "issue"."id" = "issue_id_p"
3171 AND "opinion"."suggestion_id" = "suggestion_id_v"
3172 AND "opinion"."degree" = 1
3173 AND "opinion"."fulfilled" = FALSE
3174 ),
3175 "plus1_fulfilled_count" = (
3176 SELECT coalesce(sum("snapshot"."weight"), 0)
3177 FROM "issue" CROSS JOIN "opinion"
3178 JOIN "direct_interest_snapshot" AS "snapshot"
3179 ON "snapshot"."issue_id" = "issue"."id"
3180 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3181 AND "snapshot"."member_id" = "opinion"."member_id"
3182 WHERE "issue"."id" = "issue_id_p"
3183 AND "opinion"."suggestion_id" = "suggestion_id_v"
3184 AND "opinion"."degree" = 1
3185 AND "opinion"."fulfilled" = TRUE
3186 ),
3187 "plus2_unfulfilled_count" = (
3188 SELECT coalesce(sum("snapshot"."weight"), 0)
3189 FROM "issue" CROSS JOIN "opinion"
3190 JOIN "direct_interest_snapshot" AS "snapshot"
3191 ON "snapshot"."issue_id" = "issue"."id"
3192 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3193 AND "snapshot"."member_id" = "opinion"."member_id"
3194 WHERE "issue"."id" = "issue_id_p"
3195 AND "opinion"."suggestion_id" = "suggestion_id_v"
3196 AND "opinion"."degree" = 2
3197 AND "opinion"."fulfilled" = FALSE
3198 ),
3199 "plus2_fulfilled_count" = (
3200 SELECT coalesce(sum("snapshot"."weight"), 0)
3201 FROM "issue" CROSS JOIN "opinion"
3202 JOIN "direct_interest_snapshot" AS "snapshot"
3203 ON "snapshot"."issue_id" = "issue"."id"
3204 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3205 AND "snapshot"."member_id" = "opinion"."member_id"
3206 WHERE "issue"."id" = "issue_id_p"
3207 AND "opinion"."suggestion_id" = "suggestion_id_v"
3208 AND "opinion"."degree" = 2
3209 AND "opinion"."fulfilled" = TRUE
3210 )
3211 WHERE "suggestion"."id" = "suggestion_id_v";
3212 END LOOP;
3213 END LOOP;
3214 RETURN;
3215 END;
3216 $$;
3218 COMMENT ON FUNCTION "create_snapshot"
3219 ( "issue"."id"%TYPE )
3220 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.';
3223 CREATE FUNCTION "set_snapshot_event"
3224 ( "issue_id_p" "issue"."id"%TYPE,
3225 "event_p" "snapshot_event" )
3226 RETURNS VOID
3227 LANGUAGE 'plpgsql' VOLATILE AS $$
3228 DECLARE
3229 "event_v" "issue"."latest_snapshot_event"%TYPE;
3230 BEGIN
3231 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
3232 WHERE "id" = "issue_id_p" FOR UPDATE;
3233 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
3234 WHERE "id" = "issue_id_p";
3235 UPDATE "direct_population_snapshot" SET "event" = "event_p"
3236 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3237 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
3238 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3239 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
3240 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3241 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
3242 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3243 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
3244 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3245 RETURN;
3246 END;
3247 $$;
3249 COMMENT ON FUNCTION "set_snapshot_event"
3250 ( "issue"."id"%TYPE,
3251 "snapshot_event" )
3252 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
3256 ---------------------
3257 -- Freezing issues --
3258 ---------------------
3260 CREATE FUNCTION "freeze_after_snapshot"
3261 ( "issue_id_p" "issue"."id"%TYPE )
3262 RETURNS VOID
3263 LANGUAGE 'plpgsql' VOLATILE AS $$
3264 DECLARE
3265 "issue_row" "issue"%ROWTYPE;
3266 "policy_row" "policy"%ROWTYPE;
3267 "initiative_row" "initiative"%ROWTYPE;
3268 BEGIN
3269 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3270 SELECT * INTO "policy_row"
3271 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3272 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
3273 FOR "initiative_row" IN
3274 SELECT * FROM "initiative"
3275 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3276 LOOP
3277 IF
3278 "initiative_row"."satisfied_supporter_count" > 0 AND
3279 "initiative_row"."satisfied_supporter_count" *
3280 "policy_row"."initiative_quorum_den" >=
3281 "issue_row"."population" * "policy_row"."initiative_quorum_num"
3282 THEN
3283 UPDATE "initiative" SET "admitted" = TRUE
3284 WHERE "id" = "initiative_row"."id";
3285 ELSE
3286 UPDATE "initiative" SET "admitted" = FALSE
3287 WHERE "id" = "initiative_row"."id";
3288 END IF;
3289 END LOOP;
3290 IF EXISTS (
3291 SELECT NULL FROM "initiative"
3292 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
3293 ) THEN
3294 UPDATE "issue" SET
3295 "state" = 'voting',
3296 "accepted" = coalesce("accepted", now()),
3297 "half_frozen" = coalesce("half_frozen", now()),
3298 "fully_frozen" = now()
3299 WHERE "id" = "issue_id_p";
3300 ELSE
3301 UPDATE "issue" SET
3302 "state" = 'canceled_no_initiative_admitted',
3303 "accepted" = coalesce("accepted", now()),
3304 "half_frozen" = coalesce("half_frozen", now()),
3305 "fully_frozen" = now(),
3306 "closed" = now(),
3307 "ranks_available" = TRUE
3308 WHERE "id" = "issue_id_p";
3309 -- NOTE: The following DELETE statements have effect only when
3310 -- issue state has been manipulated
3311 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
3312 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
3313 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3314 END IF;
3315 RETURN;
3316 END;
3317 $$;
3319 COMMENT ON FUNCTION "freeze_after_snapshot"
3320 ( "issue"."id"%TYPE )
3321 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
3324 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
3325 RETURNS VOID
3326 LANGUAGE 'plpgsql' VOLATILE AS $$
3327 DECLARE
3328 "issue_row" "issue"%ROWTYPE;
3329 BEGIN
3330 PERFORM "create_snapshot"("issue_id_p");
3331 PERFORM "freeze_after_snapshot"("issue_id_p");
3332 RETURN;
3333 END;
3334 $$;
3336 COMMENT ON FUNCTION "manual_freeze"
3337 ( "issue"."id"%TYPE )
3338 IS 'Freeze an issue manually (fully) and start voting';
3342 -----------------------
3343 -- Counting of votes --
3344 -----------------------
3347 CREATE FUNCTION "weight_of_added_vote_delegations"
3348 ( "issue_id_p" "issue"."id"%TYPE,
3349 "member_id_p" "member"."id"%TYPE,
3350 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
3351 RETURNS "direct_voter"."weight"%TYPE
3352 LANGUAGE 'plpgsql' VOLATILE AS $$
3353 DECLARE
3354 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3355 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
3356 "weight_v" INT4;
3357 "sub_weight_v" INT4;
3358 BEGIN
3359 "weight_v" := 0;
3360 FOR "issue_delegation_row" IN
3361 SELECT * FROM "issue_delegation"
3362 WHERE "trustee_id" = "member_id_p"
3363 AND "issue_id" = "issue_id_p"
3364 LOOP
3365 IF NOT EXISTS (
3366 SELECT NULL FROM "direct_voter"
3367 WHERE "member_id" = "issue_delegation_row"."truster_id"
3368 AND "issue_id" = "issue_id_p"
3369 ) AND NOT EXISTS (
3370 SELECT NULL FROM "delegating_voter"
3371 WHERE "member_id" = "issue_delegation_row"."truster_id"
3372 AND "issue_id" = "issue_id_p"
3373 ) THEN
3374 "delegate_member_ids_v" :=
3375 "member_id_p" || "delegate_member_ids_p";
3376 INSERT INTO "delegating_voter" (
3377 "issue_id",
3378 "member_id",
3379 "scope",
3380 "delegate_member_ids"
3381 ) VALUES (
3382 "issue_id_p",
3383 "issue_delegation_row"."truster_id",
3384 "issue_delegation_row"."scope",
3385 "delegate_member_ids_v"
3386 );
3387 "sub_weight_v" := 1 +
3388 "weight_of_added_vote_delegations"(
3389 "issue_id_p",
3390 "issue_delegation_row"."truster_id",
3391 "delegate_member_ids_v"
3392 );
3393 UPDATE "delegating_voter"
3394 SET "weight" = "sub_weight_v"
3395 WHERE "issue_id" = "issue_id_p"
3396 AND "member_id" = "issue_delegation_row"."truster_id";
3397 "weight_v" := "weight_v" + "sub_weight_v";
3398 END IF;
3399 END LOOP;
3400 RETURN "weight_v";
3401 END;
3402 $$;
3404 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
3405 ( "issue"."id"%TYPE,
3406 "member"."id"%TYPE,
3407 "delegating_voter"."delegate_member_ids"%TYPE )
3408 IS 'Helper function for "add_vote_delegations" function';
3411 CREATE FUNCTION "add_vote_delegations"
3412 ( "issue_id_p" "issue"."id"%TYPE )
3413 RETURNS VOID
3414 LANGUAGE 'plpgsql' VOLATILE AS $$
3415 DECLARE
3416 "member_id_v" "member"."id"%TYPE;
3417 BEGIN
3418 FOR "member_id_v" IN
3419 SELECT "member_id" FROM "direct_voter"
3420 WHERE "issue_id" = "issue_id_p"
3421 LOOP
3422 UPDATE "direct_voter" SET
3423 "weight" = "weight" + "weight_of_added_vote_delegations"(
3424 "issue_id_p",
3425 "member_id_v",
3426 '{}'
3427 )
3428 WHERE "member_id" = "member_id_v"
3429 AND "issue_id" = "issue_id_p";
3430 END LOOP;
3431 RETURN;
3432 END;
3433 $$;
3435 COMMENT ON FUNCTION "add_vote_delegations"
3436 ( "issue_id_p" "issue"."id"%TYPE )
3437 IS 'Helper function for "close_voting" function';
3440 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
3441 RETURNS VOID
3442 LANGUAGE 'plpgsql' VOLATILE AS $$
3443 DECLARE
3444 "area_id_v" "area"."id"%TYPE;
3445 "unit_id_v" "unit"."id"%TYPE;
3446 "member_id_v" "member"."id"%TYPE;
3447 BEGIN
3448 PERFORM "lock_issue"("issue_id_p");
3449 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
3450 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
3451 -- consider delegations and auto-reject:
3452 DELETE FROM "delegating_voter"
3453 WHERE "issue_id" = "issue_id_p";
3454 DELETE FROM "direct_voter"
3455 WHERE "issue_id" = "issue_id_p"
3456 AND "autoreject" = TRUE;
3457 DELETE FROM "direct_voter"
3458 USING (
3459 SELECT
3460 "direct_voter"."member_id"
3461 FROM "direct_voter"
3462 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
3463 LEFT JOIN "privilege"
3464 ON "privilege"."unit_id" = "unit_id_v"
3465 AND "privilege"."member_id" = "direct_voter"."member_id"
3466 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
3467 "member"."active" = FALSE OR
3468 "privilege"."voting_right" ISNULL OR
3469 "privilege"."voting_right" = FALSE
3470 )
3471 ) AS "subquery"
3472 WHERE "direct_voter"."issue_id" = "issue_id_p"
3473 AND "direct_voter"."member_id" = "subquery"."member_id";
3474 UPDATE "direct_voter" SET "weight" = 1
3475 WHERE "issue_id" = "issue_id_p";
3476 PERFORM "add_vote_delegations"("issue_id_p");
3477 FOR "member_id_v" IN
3478 SELECT "issue_autoreject"."member_id"
3479 FROM "issue_autoreject"
3480 JOIN "member"
3481 ON "issue_autoreject"."member_id" = "member"."id"
3482 JOIN "privilege"
3483 ON "privilege"."unit_id" = "unit_id_v"
3484 AND "privilege"."member_id" = "member"."id"
3485 LEFT JOIN "direct_voter"
3486 ON "issue_autoreject"."member_id" = "direct_voter"."member_id"
3487 AND "issue_autoreject"."issue_id" = "direct_voter"."issue_id"
3488 LEFT JOIN "delegating_voter"
3489 ON "issue_autoreject"."member_id" = "delegating_voter"."member_id"
3490 AND "issue_autoreject"."issue_id" = "delegating_voter"."issue_id"
3491 WHERE "issue_autoreject"."issue_id" = "issue_id_p"
3492 AND "issue_autoreject"."autoreject" = TRUE
3493 AND "member"."active"
3494 AND "privilege"."voting_right"
3495 AND "direct_voter"."member_id" ISNULL
3496 AND "delegating_voter"."member_id" ISNULL
3497 UNION
3498 SELECT "membership"."member_id"
3499 FROM "membership"
3500 JOIN "member"
3501 ON "membership"."member_id" = "member"."id"
3502 JOIN "privilege"
3503 ON "privilege"."unit_id" = "unit_id_v"
3504 AND "privilege"."member_id" = "member"."id"
3505 LEFT JOIN "issue_autoreject"
3506 ON "membership"."member_id" = "issue_autoreject"."member_id"
3507 AND "issue_autoreject"."issue_id" = "issue_id_p"
3508 LEFT JOIN "direct_voter"
3509 ON "membership"."member_id" = "direct_voter"."member_id"
3510 AND "direct_voter"."issue_id" = "issue_id_p"
3511 LEFT JOIN "delegating_voter"
3512 ON "membership"."member_id" = "delegating_voter"."member_id"
3513 AND "delegating_voter"."issue_id" = "issue_id_p"
3514 WHERE "membership"."area_id" = "area_id_v"
3515 AND "membership"."autoreject" = TRUE
3516 AND "member"."active"
3517 AND "privilege"."voting_right"
3518 AND "issue_autoreject"."autoreject" ISNULL
3519 AND "direct_voter"."member_id" ISNULL
3520 AND "delegating_voter"."member_id" ISNULL
3521 LOOP
3522 INSERT INTO "direct_voter"
3523 ("member_id", "issue_id", "weight", "autoreject") VALUES
3524 ("member_id_v", "issue_id_p", 1, TRUE);
3525 INSERT INTO "vote" (
3526 "member_id",
3527 "issue_id",
3528 "initiative_id",
3529 "grade"
3530 ) SELECT
3531 "member_id_v" AS "member_id",
3532 "issue_id_p" AS "issue_id",
3533 "id" AS "initiative_id",
3534 -1 AS "grade"
3535 FROM "initiative"
3536 WHERE "issue_id" = "issue_id_p" AND "admitted";
3537 END LOOP;
3538 PERFORM "add_vote_delegations"("issue_id_p");
3539 -- set voter count and mark issue as being calculated:
3540 UPDATE "issue" SET
3541 "state" = 'calculation',
3542 "closed" = now(),
3543 "voter_count" = (
3544 SELECT coalesce(sum("weight"), 0)
3545 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
3546 )
3547 WHERE "id" = "issue_id_p";
3548 -- materialize battle_view:
3549 -- NOTE: "closed" column of issue must be set at this point
3550 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3551 INSERT INTO "battle" (
3552 "issue_id",
3553 "winning_initiative_id", "losing_initiative_id",
3554 "count"
3555 ) SELECT
3556 "issue_id",
3557 "winning_initiative_id", "losing_initiative_id",
3558 "count"
3559 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
3560 -- copy "positive_votes" and "negative_votes" from "battle" table:
3561 UPDATE "initiative" SET
3562 "positive_votes" = "battle_win"."count",
3563 "negative_votes" = "battle_lose"."count"
3564 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
3565 WHERE
3566 "battle_win"."issue_id" = "issue_id_p" AND
3567 "battle_win"."winning_initiative_id" = "initiative"."id" AND
3568 "battle_win"."losing_initiative_id" ISNULL AND
3569 "battle_lose"."issue_id" = "issue_id_p" AND
3570 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
3571 "battle_lose"."winning_initiative_id" ISNULL;
3572 END;
3573 $$;
3575 COMMENT ON FUNCTION "close_voting"
3576 ( "issue"."id"%TYPE )
3577 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.';
3580 CREATE FUNCTION "defeat_strength"
3581 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
3582 RETURNS INT8
3583 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3584 BEGIN
3585 IF "positive_votes_p" > "negative_votes_p" THEN
3586 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
3587 ELSIF "positive_votes_p" = "negative_votes_p" THEN
3588 RETURN 0;
3589 ELSE
3590 RETURN -1;
3591 END IF;
3592 END;
3593 $$;
3595 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';
3598 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
3599 RETURNS VOID
3600 LANGUAGE 'plpgsql' VOLATILE AS $$
3601 DECLARE
3602 "issue_row" "issue"%ROWTYPE;
3603 "policy_row" "policy"%ROWTYPE;
3604 "dimension_v" INTEGER;
3605 "vote_matrix" INT4[][]; -- absolute votes
3606 "matrix" INT8[][]; -- defeat strength / best paths
3607 "i" INTEGER;
3608 "j" INTEGER;
3609 "k" INTEGER;
3610 "battle_row" "battle"%ROWTYPE;
3611 "rank_ary" INT4[];
3612 "rank_v" INT4;
3613 "done_v" INTEGER;
3614 "winners_ary" INTEGER[];
3615 "initiative_id_v" "initiative"."id"%TYPE;
3616 BEGIN
3617 SELECT * INTO "issue_row"
3618 FROM "issue" WHERE "id" = "issue_id_p"
3619 FOR UPDATE;
3620 SELECT * INTO "policy_row"
3621 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3622 SELECT count(1) INTO "dimension_v"
3623 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
3624 IF "dimension_v" > 1 THEN
3625 -- Create "vote_matrix" with absolute number of votes in pairwise
3626 -- comparison:
3627 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
3628 "i" := 1;
3629 "j" := 2;
3630 FOR "battle_row" IN
3631 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
3632 ORDER BY
3633 "winning_initiative_id" NULLS LAST,
3634 "losing_initiative_id" NULLS LAST
3635 LOOP
3636 "vote_matrix"["i"]["j"] := "battle_row"."count";
3637 IF "j" = "dimension_v" THEN
3638 "i" := "i" + 1;
3639 "j" := 1;
3640 ELSE
3641 "j" := "j" + 1;
3642 IF "j" = "i" THEN
3643 "j" := "j" + 1;
3644 END IF;
3645 END IF;
3646 END LOOP;
3647 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
3648 RAISE EXCEPTION 'Wrong battle count (should not happen)';
3649 END IF;
3650 -- Store defeat strengths in "matrix" using "defeat_strength"
3651 -- function:
3652 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
3653 "i" := 1;
3654 LOOP
3655 "j" := 1;
3656 LOOP
3657 IF "i" != "j" THEN
3658 "matrix"["i"]["j"] := "defeat_strength"(
3659 "vote_matrix"["i"]["j"],
3660 "vote_matrix"["j"]["i"]
3661 );
3662 END IF;
3663 EXIT WHEN "j" = "dimension_v";
3664 "j" := "j" + 1;
3665 END LOOP;
3666 EXIT WHEN "i" = "dimension_v";
3667 "i" := "i" + 1;
3668 END LOOP;
3669 -- Find best paths:
3670 "i" := 1;
3671 LOOP
3672 "j" := 1;
3673 LOOP
3674 IF "i" != "j" THEN
3675 "k" := 1;
3676 LOOP
3677 IF "i" != "k" AND "j" != "k" THEN
3678 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
3679 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
3680 "matrix"["j"]["k"] := "matrix"["j"]["i"];
3681 END IF;
3682 ELSE
3683 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
3684 "matrix"["j"]["k"] := "matrix"["i"]["k"];
3685 END IF;
3686 END IF;
3687 END IF;
3688 EXIT WHEN "k" = "dimension_v";
3689 "k" := "k" + 1;
3690 END LOOP;
3691 END IF;
3692 EXIT WHEN "j" = "dimension_v";
3693 "j" := "j" + 1;
3694 END LOOP;
3695 EXIT WHEN "i" = "dimension_v";
3696 "i" := "i" + 1;
3697 END LOOP;
3698 -- Determine order of winners:
3699 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
3700 "rank_v" := 1;
3701 "done_v" := 0;
3702 LOOP
3703 "winners_ary" := '{}';
3704 "i" := 1;
3705 LOOP
3706 IF "rank_ary"["i"] ISNULL THEN
3707 "j" := 1;
3708 LOOP
3709 IF
3710 "i" != "j" AND
3711 "rank_ary"["j"] ISNULL AND
3712 "matrix"["j"]["i"] > "matrix"["i"]["j"]
3713 THEN
3714 -- someone else is better
3715 EXIT;
3716 END IF;
3717 IF "j" = "dimension_v" THEN
3718 -- noone is better
3719 "winners_ary" := "winners_ary" || "i";
3720 EXIT;
3721 END IF;
3722 "j" := "j" + 1;
3723 END LOOP;
3724 END IF;
3725 EXIT WHEN "i" = "dimension_v";
3726 "i" := "i" + 1;
3727 END LOOP;
3728 "i" := 1;
3729 LOOP
3730 "rank_ary"["winners_ary"["i"]] := "rank_v";
3731 "done_v" := "done_v" + 1;
3732 EXIT WHEN "i" = array_upper("winners_ary", 1);
3733 "i" := "i" + 1;
3734 END LOOP;
3735 EXIT WHEN "done_v" = "dimension_v";
3736 "rank_v" := "rank_v" + 1;
3737 END LOOP;
3738 -- write preliminary results:
3739 "i" := 1;
3740 FOR "initiative_id_v" IN
3741 SELECT "id" FROM "initiative"
3742 WHERE "issue_id" = "issue_id_p" AND "admitted"
3743 ORDER BY "id"
3744 LOOP
3745 UPDATE "initiative" SET
3746 "attainable" =
3747 CASE WHEN "policy_row"."majority_strict" THEN
3748 "positive_votes" * "policy_row"."majority_den" >
3749 "policy_row"."majority_num" * ("positive_votes"+"negative_votes")
3750 ELSE
3751 "positive_votes" * "policy_row"."majority_den" >=
3752 "policy_row"."majority_num" * ("positive_votes"+"negative_votes")
3753 END
3754 AND "positive_votes" >= "policy_row"."majority_positive"
3755 AND "issue_row"."voter_count"-"negative_votes" >=
3756 "policy_row"."majority_non_negative",
3757 "favored_to_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
3758 "unfavored_to_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
3759 "schulze_rank" = "rank_ary"["i"],
3760 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
3761 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0
3762 WHERE "id" = "initiative_id_v";
3763 "i" := "i" + 1;
3764 END LOOP;
3765 IF "i" != "dimension_v" THEN
3766 RAISE EXCEPTION 'Wrong winner count (should not happen)';
3767 END IF;
3768 if "policy_row"."majority_indirect" THEN
3769 -- take indirect majorities into account:
3770 LOOP
3771 UPDATE "initiative" SET "attainable" = TRUE
3772 FROM (
3773 SELECT "new_initiative"."id" AS "initiative_id"
3774 FROM "initiative" "old_initiative"
3775 JOIN "initiative" "new_initiative"
3776 ON "new_initiative"."issue_id" = "issue_id_p"
3777 AND "new_initiative"."attainable" = FALSE
3778 JOIN "battle" "battle_win"
3779 ON "battle_win"."issue_id" = "issue_id_p"
3780 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
3781 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
3782 JOIN "battle" "battle_lose"
3783 ON "battle_lose"."issue_id" = "issue_id_p"
3784 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
3785 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
3786 WHERE "old_initiative"."issue_id" = "issue_id_p"
3787 AND "old_initiative"."attainable" = TRUE
3788 AND CASE WHEN "policy_row"."majority_strict" THEN
3789 "battle_win"."count" * "policy_row"."majority_den" >
3790 "policy_row"."majority_num" *
3791 ("battle_win"."count"+"battle_lose"."count")
3792 ELSE
3793 "battle_win"."count" * "policy_row"."majority_den" >=
3794 "policy_row"."majority_num" *
3795 ("battle_win"."count"+"battle_lose"."count")
3796 END
3797 AND "battle_win"."count" >= "policy_row"."majority_positive"
3798 AND "issue_row"."voter_count"-"battle_lose"."count" >=
3799 "policy_row"."majority_non_negative"
3800 ) AS "subquery"
3801 WHERE "id" = "subquery"."initiative_id";
3802 EXIT WHEN NOT FOUND;
3803 END LOOP;
3804 END IF;
3805 -- set "multistage_majority" for remaining matching initiatives:
3806 UPDATE "initiative" SET "multistage_majority" = TRUE
3807 FROM (
3808 SELECT "losing_initiative"."id" AS "initiative_id"
3809 FROM "initiative" "losing_initiative"
3810 JOIN "initiative" "winning_initiative"
3811 ON "winning_initiative"."issue_id" = "issue_id_p"
3812 AND "winning_initiative"."admitted"
3813 JOIN "battle" "battle_win"
3814 ON "battle_win"."issue_id" = "issue_id_p"
3815 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
3816 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
3817 JOIN "battle" "battle_lose"
3818 ON "battle_lose"."issue_id" = "issue_id_p"
3819 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
3820 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
3821 WHERE "losing_initiative"."issue_id" = "issue_id_p"
3822 AND "losing_initiative"."admitted"
3823 AND "winning_initiative"."schulze_rank" <
3824 "losing_initiative"."schulze_rank"
3825 AND "battle_win"."count" > "battle_lose"."count"
3826 AND (
3827 "battle_win"."count" > "winning_initiative"."positive_votes" OR
3828 "battle_lose"."count" < "losing_initiative"."negative_votes" )
3829 ) AS "subquery"
3830 WHERE "id" = "subquery"."initiative_id";
3831 -- mark eligible initiatives:
3832 "rank_v" := 1;
3833 UPDATE "initiative" SET "eligible" = TRUE
3834 FROM (
3835 SELECT "initiative"."id" AS "initiative_id"
3836 FROM "issue"
3837 JOIN "policy"
3838 ON "issue"."policy_id" = "policy"."id"
3839 JOIN "initiative"
3840 ON "issue"."id" = "initiative"."issue_id"
3841 WHERE "issue_id" = "issue_id_p"
3842 AND "initiative"."attainable"
3843 AND "initiative"."favored_to_status_quo"
3844 AND (
3845 "policy"."no_multistage_majority" = FALSE OR
3846 "initiative"."multistage_majority" = FALSE )
3847 AND (
3848 "policy"."no_reverse_beat_path" = FALSE OR
3849 "initiative"."reverse_beat_path" = FALSE )
3850 ) AS "subquery"
3851 WHERE "id" = "subquery"."initiative_id";
3852 -- mark final winner:
3853 UPDATE "initiative" SET "winner" = TRUE
3854 FROM (
3855 SELECT "id" AS "initiative_id"
3856 FROM "initiative"
3857 WHERE "issue_id" = "issue_id_p" AND "eligible"
3858 ORDER BY "schulze_rank", "id"
3859 LIMIT 1
3860 ) AS "subquery"
3861 WHERE "id" = "subquery"."initiative_id";
3862 END IF;
3863 -- mark issue as finished:
3864 UPDATE "issue" SET
3865 "state" =
3866 CASE WHEN EXISTS (
3867 SELECT NULL FROM "initiative"
3868 WHERE "issue_id" = "issue_id_p" AND "winner"
3869 ) THEN
3870 'finished_with_winner'::"issue_state"
3871 ELSE
3872 'finished_without_winner'::"issue_state"
3873 END,
3874 "ranks_available" = TRUE
3875 WHERE "id" = "issue_id_p";
3876 RETURN;
3877 END;
3878 $$;
3880 COMMENT ON FUNCTION "calculate_ranks"
3881 ( "issue"."id"%TYPE )
3882 IS 'Determine ranking (Votes have to be counted first)';
3886 -----------------------------
3887 -- Automatic state changes --
3888 -----------------------------
3891 CREATE FUNCTION "check_issue"
3892 ( "issue_id_p" "issue"."id"%TYPE )
3893 RETURNS VOID
3894 LANGUAGE 'plpgsql' VOLATILE AS $$
3895 DECLARE
3896 "issue_row" "issue"%ROWTYPE;
3897 "policy_row" "policy"%ROWTYPE;
3898 BEGIN
3899 PERFORM "lock_issue"("issue_id_p");
3900 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3901 -- only process open issues:
3902 IF "issue_row"."closed" ISNULL THEN
3903 SELECT * INTO "policy_row" FROM "policy"
3904 WHERE "id" = "issue_row"."policy_id";
3905 -- create a snapshot, unless issue is already fully frozen:
3906 IF "issue_row"."fully_frozen" ISNULL THEN
3907 PERFORM "create_snapshot"("issue_id_p");
3908 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3909 END IF;
3910 -- eventually close or accept issues, which have not been accepted:
3911 IF "issue_row"."accepted" ISNULL THEN
3912 IF EXISTS (
3913 SELECT NULL FROM "initiative"
3914 WHERE "issue_id" = "issue_id_p"
3915 AND "supporter_count" > 0
3916 AND "supporter_count" * "policy_row"."issue_quorum_den"
3917 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
3918 ) THEN
3919 -- accept issues, if supporter count is high enough
3920 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3921 -- NOTE: "issue_row" used later
3922 "issue_row"."state" := 'discussion';
3923 "issue_row"."accepted" := now();
3924 UPDATE "issue" SET
3925 "state" = "issue_row"."state",
3926 "accepted" = "issue_row"."accepted"
3927 WHERE "id" = "issue_row"."id";
3928 ELSIF
3929 now() >= "issue_row"."created" + "issue_row"."admission_time"
3930 THEN
3931 -- close issues, if admission time has expired
3932 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3933 UPDATE "issue" SET
3934 "state" = 'canceled_issue_not_accepted',
3935 "closed" = now()
3936 WHERE "id" = "issue_row"."id";
3937 END IF;
3938 END IF;
3939 -- eventually half freeze issues:
3940 IF
3941 -- NOTE: issue can't be closed at this point, if it has been accepted
3942 "issue_row"."accepted" NOTNULL AND
3943 "issue_row"."half_frozen" ISNULL
3944 THEN
3945 IF
3946 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
3947 THEN
3948 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
3949 -- NOTE: "issue_row" used later
3950 "issue_row"."state" := 'verification';
3951 "issue_row"."half_frozen" := now();
3952 UPDATE "issue" SET
3953 "state" = "issue_row"."state",
3954 "half_frozen" = "issue_row"."half_frozen"
3955 WHERE "id" = "issue_row"."id";
3956 END IF;
3957 END IF;
3958 -- close issues after some time, if all initiatives have been revoked:
3959 IF
3960 "issue_row"."closed" ISNULL AND
3961 NOT EXISTS (
3962 -- all initiatives are revoked
3963 SELECT NULL FROM "initiative"
3964 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3965 ) AND (
3966 -- and issue has not been accepted yet
3967 "issue_row"."accepted" ISNULL OR
3968 NOT EXISTS (
3969 -- or no initiatives have been revoked lately
3970 SELECT NULL FROM "initiative"
3971 WHERE "issue_id" = "issue_id_p"
3972 AND now() < "revoked" + "issue_row"."verification_time"
3973 ) OR (
3974 -- or verification time has elapsed
3975 "issue_row"."half_frozen" NOTNULL AND
3976 "issue_row"."fully_frozen" ISNULL AND
3977 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3978 )
3979 )
3980 THEN
3981 -- NOTE: "issue_row" used later
3982 IF "issue_row"."accepted" ISNULL THEN
3983 "issue_row"."state" := 'canceled_revoked_before_accepted';
3984 ELSIF "issue_row"."half_frozen" ISNULL THEN
3985 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
3986 ELSE
3987 "issue_row"."state" := 'canceled_after_revocation_during_verification';
3988 END IF;
3989 "issue_row"."closed" := now();
3990 UPDATE "issue" SET
3991 "state" = "issue_row"."state",
3992 "closed" = "issue_row"."closed"
3993 WHERE "id" = "issue_row"."id";
3994 END IF;
3995 -- fully freeze issue after verification time:
3996 IF
3997 "issue_row"."half_frozen" NOTNULL AND
3998 "issue_row"."fully_frozen" ISNULL AND
3999 "issue_row"."closed" ISNULL AND
4000 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
4001 THEN
4002 PERFORM "freeze_after_snapshot"("issue_id_p");
4003 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
4004 END IF;
4005 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4006 -- close issue by calling close_voting(...) after voting time:
4007 IF
4008 "issue_row"."closed" ISNULL AND
4009 "issue_row"."fully_frozen" NOTNULL AND
4010 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
4011 THEN
4012 PERFORM "close_voting"("issue_id_p");
4013 -- calculate ranks will not consume much time and can be done now
4014 PERFORM "calculate_ranks"("issue_id_p");
4015 END IF;
4016 END IF;
4017 RETURN;
4018 END;
4019 $$;
4021 COMMENT ON FUNCTION "check_issue"
4022 ( "issue"."id"%TYPE )
4023 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.';
4026 CREATE FUNCTION "check_everything"()
4027 RETURNS VOID
4028 LANGUAGE 'plpgsql' VOLATILE AS $$
4029 DECLARE
4030 "issue_id_v" "issue"."id"%TYPE;
4031 BEGIN
4032 DELETE FROM "expired_session";
4033 PERFORM "check_last_login"();
4034 PERFORM "calculate_member_counts"();
4035 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
4036 PERFORM "check_issue"("issue_id_v");
4037 END LOOP;
4038 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
4039 PERFORM "calculate_ranks"("issue_id_v");
4040 END LOOP;
4041 RETURN;
4042 END;
4043 $$;
4045 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.';
4049 ----------------------
4050 -- Deletion of data --
4051 ----------------------
4054 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
4055 RETURNS VOID
4056 LANGUAGE 'plpgsql' VOLATILE AS $$
4057 DECLARE
4058 "issue_row" "issue"%ROWTYPE;
4059 BEGIN
4060 SELECT * INTO "issue_row"
4061 FROM "issue" WHERE "id" = "issue_id_p"
4062 FOR UPDATE;
4063 IF "issue_row"."cleaned" ISNULL THEN
4064 UPDATE "issue" SET
4065 "state" = 'voting',
4066 "closed" = NULL,
4067 "ranks_available" = FALSE
4068 WHERE "id" = "issue_id_p";
4069 DELETE FROM "delegating_voter"
4070 WHERE "issue_id" = "issue_id_p";
4071 DELETE FROM "direct_voter"
4072 WHERE "issue_id" = "issue_id_p";
4073 DELETE FROM "delegating_interest_snapshot"
4074 WHERE "issue_id" = "issue_id_p";
4075 DELETE FROM "direct_interest_snapshot"
4076 WHERE "issue_id" = "issue_id_p";
4077 DELETE FROM "delegating_population_snapshot"
4078 WHERE "issue_id" = "issue_id_p";
4079 DELETE FROM "direct_population_snapshot"
4080 WHERE "issue_id" = "issue_id_p";
4081 DELETE FROM "non_voter"
4082 WHERE "issue_id" = "issue_id_p";
4083 DELETE FROM "delegation"
4084 WHERE "issue_id" = "issue_id_p";
4085 DELETE FROM "supporter"
4086 WHERE "issue_id" = "issue_id_p";
4087 UPDATE "issue" SET
4088 "state" = "issue_row"."state",
4089 "closed" = "issue_row"."closed",
4090 "ranks_available" = "issue_row"."ranks_available",
4091 "cleaned" = now()
4092 WHERE "id" = "issue_id_p";
4093 END IF;
4094 RETURN;
4095 END;
4096 $$;
4098 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
4101 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
4102 RETURNS VOID
4103 LANGUAGE 'plpgsql' VOLATILE AS $$
4104 BEGIN
4105 UPDATE "member" SET
4106 "last_login" = NULL,
4107 "last_login_public" = NULL,
4108 "login" = NULL,
4109 "password" = NULL,
4110 "locked" = TRUE,
4111 "active" = FALSE,
4112 "notify_email" = NULL,
4113 "notify_email_unconfirmed" = NULL,
4114 "notify_email_secret" = NULL,
4115 "notify_email_secret_expiry" = NULL,
4116 "notify_email_lock_expiry" = NULL,
4117 "password_reset_secret" = NULL,
4118 "password_reset_secret_expiry" = NULL,
4119 "organizational_unit" = NULL,
4120 "internal_posts" = NULL,
4121 "realname" = NULL,
4122 "birthday" = NULL,
4123 "address" = NULL,
4124 "email" = NULL,
4125 "xmpp_address" = NULL,
4126 "website" = NULL,
4127 "phone" = NULL,
4128 "mobile_phone" = NULL,
4129 "profession" = NULL,
4130 "external_memberships" = NULL,
4131 "external_posts" = NULL,
4132 "statement" = NULL
4133 WHERE "id" = "member_id_p";
4134 -- "text_search_data" is updated by triggers
4135 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
4136 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
4137 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
4138 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
4139 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
4140 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
4141 DELETE FROM "session" WHERE "member_id" = "member_id_p";
4142 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
4143 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
4144 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
4145 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
4146 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
4147 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
4148 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
4149 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
4150 DELETE FROM "direct_voter" USING "issue"
4151 WHERE "direct_voter"."issue_id" = "issue"."id"
4152 AND "issue"."closed" ISNULL
4153 AND "member_id" = "member_id_p";
4154 RETURN;
4155 END;
4156 $$;
4158 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)';
4161 CREATE FUNCTION "delete_private_data"()
4162 RETURNS VOID
4163 LANGUAGE 'plpgsql' VOLATILE AS $$
4164 BEGIN
4165 UPDATE "member" SET
4166 "last_login" = NULL,
4167 "login" = NULL,
4168 "password" = NULL,
4169 "notify_email" = NULL,
4170 "notify_email_unconfirmed" = NULL,
4171 "notify_email_secret" = NULL,
4172 "notify_email_secret_expiry" = NULL,
4173 "notify_email_lock_expiry" = NULL,
4174 "password_reset_secret" = NULL,
4175 "password_reset_secret_expiry" = NULL,
4176 "organizational_unit" = NULL,
4177 "internal_posts" = NULL,
4178 "realname" = NULL,
4179 "birthday" = NULL,
4180 "address" = NULL,
4181 "email" = NULL,
4182 "xmpp_address" = NULL,
4183 "website" = NULL,
4184 "phone" = NULL,
4185 "mobile_phone" = NULL,
4186 "profession" = NULL,
4187 "external_memberships" = NULL,
4188 "external_posts" = NULL,
4189 "statement" = NULL;
4190 -- "text_search_data" is updated by triggers
4191 DELETE FROM "invite_code";
4192 DELETE FROM "setting";
4193 DELETE FROM "setting_map";
4194 DELETE FROM "member_relation_setting";
4195 DELETE FROM "member_image";
4196 DELETE FROM "contact";
4197 DELETE FROM "ignored_member";
4198 DELETE FROM "session";
4199 DELETE FROM "area_setting";
4200 DELETE FROM "issue_setting";
4201 DELETE FROM "ignored_initiative";
4202 DELETE FROM "initiative_setting";
4203 DELETE FROM "suggestion_setting";
4204 DELETE FROM "non_voter";
4205 DELETE FROM "direct_voter" USING "issue"
4206 WHERE "direct_voter"."issue_id" = "issue"."id"
4207 AND "issue"."closed" ISNULL;
4208 RETURN;
4209 END;
4210 $$;
4212 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.';
4216 COMMIT;