liquid_feedback_core
view core.sql @ 159:32887495ca57
Wiki formatting for member statements and suggestions
| author | jbe |
|---|---|
| date | Sat Jun 04 16:50:50 2011 +0200 (2011-06-04) |
| parents | 374cca7355af |
| children | cc520b58e8ec |
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 "formatting_engine" TEXT,
119 "statement" TEXT,
120 "text_search_data" TSVECTOR );
121 CREATE INDEX "member_active_idx" ON "member" ("active");
122 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
123 CREATE TRIGGER "update_text_search_data"
124 BEFORE INSERT OR UPDATE ON "member"
125 FOR EACH ROW EXECUTE PROCEDURE
126 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
127 "name", "identification", "organizational_unit", "internal_posts",
128 "realname", "external_memberships", "external_posts", "statement" );
130 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
132 COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login';
133 COMMENT ON COLUMN "member"."last_login_public" IS 'Date of last login (time stripped for privacy reasons, updated only after day change)';
134 COMMENT ON COLUMN "member"."login" IS 'Login name';
135 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
136 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
137 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.';
138 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
139 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
140 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
141 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
142 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
143 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
144 COMMENT ON COLUMN "member"."notify_level" IS 'Selects which event notifications are to be sent to the "notify_email" mail address';
145 COMMENT ON COLUMN "member"."notify_event_id" IS 'Latest "id" of an "event" the member was notified about';
146 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member';
147 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
148 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
149 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
150 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
151 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
152 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
153 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
154 COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"';
155 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his homepage within the system';
158 CREATE TABLE "member_history" (
159 "id" SERIAL8 PRIMARY KEY,
160 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
161 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
162 "active" BOOLEAN NOT NULL,
163 "name" TEXT NOT NULL );
164 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
166 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
168 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
169 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
172 CREATE TABLE "rendered_member_statement" (
173 PRIMARY KEY ("member_id", "format"),
174 "member_id" INT8 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
175 "format" TEXT,
176 "content" TEXT NOT NULL );
178 COMMENT ON TABLE "rendered_member_statement" IS 'This table may be used by frontends to cache "rendered" member statements (e.g. HTML output generated from wiki text)';
181 CREATE TABLE "invite_code" (
182 "id" SERIAL8 PRIMARY KEY,
183 "code" TEXT NOT NULL UNIQUE,
184 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
185 "used" TIMESTAMPTZ,
186 "member_id" INT4 UNIQUE REFERENCES "member" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
187 "comment" TEXT,
188 CONSTRAINT "only_used_codes_may_refer_to_member" CHECK ("used" NOTNULL OR "member_id" ISNULL) );
190 COMMENT ON TABLE "invite_code" IS 'Invite codes can be used once to create a new member account.';
192 COMMENT ON COLUMN "invite_code"."code" IS 'Secret code';
193 COMMENT ON COLUMN "invite_code"."created" IS 'Time of creation of the secret code';
194 COMMENT ON COLUMN "invite_code"."used" IS 'NULL, if not used yet, otherwise tells when this code was used to create a member account';
195 COMMENT ON COLUMN "invite_code"."member_id" IS 'References the member whose account was created with this code';
196 COMMENT ON COLUMN "invite_code"."comment" IS 'Comment on the code, which is to be used for administrative reasons only';
199 CREATE TABLE "setting" (
200 PRIMARY KEY ("member_id", "key"),
201 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
202 "key" TEXT NOT NULL,
203 "value" TEXT NOT NULL );
204 CREATE INDEX "setting_key_idx" ON "setting" ("key");
206 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
208 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
211 CREATE TABLE "setting_map" (
212 PRIMARY KEY ("member_id", "key", "subkey"),
213 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
214 "key" TEXT NOT NULL,
215 "subkey" TEXT NOT NULL,
216 "value" TEXT NOT NULL );
217 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
219 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
221 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
222 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
223 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
226 CREATE TABLE "member_relation_setting" (
227 PRIMARY KEY ("member_id", "key", "other_member_id"),
228 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
229 "key" TEXT NOT NULL,
230 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
231 "value" TEXT NOT NULL );
233 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
236 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
238 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
241 CREATE TABLE "member_image" (
242 PRIMARY KEY ("member_id", "image_type", "scaled"),
243 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
244 "image_type" "member_image_type",
245 "scaled" BOOLEAN,
246 "content_type" TEXT,
247 "data" BYTEA NOT NULL );
249 COMMENT ON TABLE "member_image" IS 'Images of members';
251 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
254 CREATE TABLE "member_count" (
255 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
256 "total_count" INT4 NOT NULL );
258 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';
260 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
261 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
264 CREATE TABLE "contact" (
265 PRIMARY KEY ("member_id", "other_member_id"),
266 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
267 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
268 "public" BOOLEAN NOT NULL DEFAULT FALSE,
269 CONSTRAINT "cant_save_yourself_as_contact"
270 CHECK ("member_id" != "other_member_id") );
271 CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
273 COMMENT ON TABLE "contact" IS 'Contact lists';
275 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
276 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
277 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
280 CREATE TABLE "ignored_member" (
281 PRIMARY KEY ("member_id", "other_member_id"),
282 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
283 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
284 CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
286 COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
288 COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone';
289 COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
292 CREATE TABLE "session" (
293 "ident" TEXT PRIMARY KEY,
294 "additional_secret" TEXT,
295 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
296 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
297 "lang" TEXT );
298 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
300 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';
302 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
303 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
304 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
305 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
308 CREATE TABLE "policy" (
309 "id" SERIAL4 PRIMARY KEY,
310 "index" INT4 NOT NULL,
311 "active" BOOLEAN NOT NULL DEFAULT TRUE,
312 "name" TEXT NOT NULL UNIQUE,
313 "description" TEXT NOT NULL DEFAULT '',
314 "admission_time" INTERVAL NOT NULL,
315 "discussion_time" INTERVAL NOT NULL,
316 "verification_time" INTERVAL NOT NULL,
317 "voting_time" INTERVAL NOT NULL,
318 "issue_quorum_num" INT4 NOT NULL,
319 "issue_quorum_den" INT4 NOT NULL,
320 "initiative_quorum_num" INT4 NOT NULL,
321 "initiative_quorum_den" INT4 NOT NULL,
322 "majority_num" INT4 NOT NULL DEFAULT 1,
323 "majority_den" INT4 NOT NULL DEFAULT 2,
324 "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
325 "majority_positive" INT4 NOT NULL DEFAULT 0,
326 "majority_non_negative" INT4 NOT NULL DEFAULT 0,
327 "majority_indirect" BOOLEAN NOT NULL DEFAULT TRUE,
328 "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT TRUE,
329 "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE );
330 CREATE INDEX "policy_active_idx" ON "policy" ("active");
332 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
334 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
335 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
336 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"';
337 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"';
338 COMMENT ON COLUMN "policy"."verification_time" IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
339 COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "fully_frozen" but not "closed"';
340 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"';
341 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"';
342 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
343 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
344 COMMENT ON COLUMN "policy"."majority_num" IS 'Numerator of fraction of majority to be reached during voting by an initiative to be an "attainable" winner';
345 COMMENT ON COLUMN "policy"."majority_den" IS 'Denominator of fraction of majority to be reached during voting by an initiative to be an "attainable" winner';
346 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.';
347 COMMENT ON COLUMN "policy"."majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be an "attainable" winner.';
348 COMMENT ON COLUMN "policy"."majority_non_negative" IS 'Absolute number of sum of "positive_votes" and abstentions neccessary for an initiative to be an "attainable" winner.';
349 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" may be indirect through a beat path; if FALSE, then the status quo must be beaten directly with that majority';
350 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". This option ensures both that a winning initiative is never tied in a (weak) condorcet paradox with the status quo and a winning initiative always beats the status quo directly with a simple majority.';
351 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 disqualifies initiatives which could cause an instable result. An instable result in this meaning is a result such that repeating the ballot with same preferences but with the winner of the first ballot as status quo would lead to a different winner in the second ballot. If (a) "majority_indirect" is TRUE, or (b) "no_reverse_beat_path" is TRUE and "majority_num"/"majority_den" is only a simple majority, or (c) "majority_num" is zero, then results are always stable and this flag does not have any effect on the winner (but still affects the "eligible" flag of an "initiative").';
354 CREATE TABLE "unit" (
355 "id" SERIAL4 PRIMARY KEY,
356 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
357 "active" BOOLEAN NOT NULL DEFAULT TRUE,
358 "name" TEXT NOT NULL,
359 "description" TEXT NOT NULL DEFAULT '',
360 "member_count" INT4,
361 "text_search_data" TSVECTOR );
362 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
363 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
364 CREATE INDEX "unit_active_idx" ON "unit" ("active");
365 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
366 CREATE TRIGGER "update_text_search_data"
367 BEFORE INSERT OR UPDATE ON "unit"
368 FOR EACH ROW EXECUTE PROCEDURE
369 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
370 "name", "description" );
372 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
374 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
375 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in units of this area';
376 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
379 CREATE TABLE "area" (
380 "id" SERIAL4 PRIMARY KEY,
381 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
382 "active" BOOLEAN NOT NULL DEFAULT TRUE,
383 "name" TEXT NOT NULL,
384 "description" TEXT NOT NULL DEFAULT '',
385 "direct_member_count" INT4,
386 "member_weight" INT4,
387 "autoreject_weight" INT4,
388 "text_search_data" TSVECTOR );
389 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
390 CREATE INDEX "area_active_idx" ON "area" ("active");
391 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
392 CREATE TRIGGER "update_text_search_data"
393 BEFORE INSERT OR UPDATE ON "area"
394 FOR EACH ROW EXECUTE PROCEDURE
395 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
396 "name", "description" );
398 COMMENT ON TABLE "area" IS 'Subject areas';
400 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
401 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"';
402 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
403 COMMENT ON COLUMN "area"."autoreject_weight" IS 'Sum of weight of members using the autoreject feature';
406 CREATE TABLE "area_setting" (
407 PRIMARY KEY ("member_id", "key", "area_id"),
408 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
409 "key" TEXT NOT NULL,
410 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
411 "value" TEXT NOT NULL );
413 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
416 CREATE TABLE "allowed_policy" (
417 PRIMARY KEY ("area_id", "policy_id"),
418 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
419 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
420 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
421 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
423 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
425 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
428 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
430 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';
433 CREATE TYPE "issue_state" AS ENUM (
434 'admission', 'discussion', 'verification', 'voting',
435 'canceled_revoked_before_accepted',
436 'canceled_issue_not_accepted',
437 'canceled_after_revocation_during_discussion',
438 'canceled_after_revocation_during_verification',
439 'calculation',
440 'canceled_no_initiative_admitted',
441 'finished_without_winner', 'finished_with_winner');
443 COMMENT ON TYPE "issue_state" IS 'State of issues';
446 CREATE TABLE "issue" (
447 "id" SERIAL4 PRIMARY KEY,
448 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
449 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
450 "state" "issue_state" NOT NULL DEFAULT 'admission',
451 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
452 "accepted" TIMESTAMPTZ,
453 "half_frozen" TIMESTAMPTZ,
454 "fully_frozen" TIMESTAMPTZ,
455 "closed" TIMESTAMPTZ,
456 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
457 "cleaned" TIMESTAMPTZ,
458 "admission_time" INTERVAL NOT NULL,
459 "discussion_time" INTERVAL NOT NULL,
460 "verification_time" INTERVAL NOT NULL,
461 "voting_time" INTERVAL NOT NULL,
462 "snapshot" TIMESTAMPTZ,
463 "latest_snapshot_event" "snapshot_event",
464 "population" INT4,
465 "voter_count" INT4,
466 CONSTRAINT "valid_state" CHECK ((
467 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
468 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
469 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
470 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
471 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
472 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
473 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
474 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
475 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)) AND (
476 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
477 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
478 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
479 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
480 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
481 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
482 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
483 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
484 ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
485 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
486 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
487 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
488 )),
489 CONSTRAINT "state_change_order" CHECK (
490 "created" <= "accepted" AND
491 "accepted" <= "half_frozen" AND
492 "half_frozen" <= "fully_frozen" AND
493 "fully_frozen" <= "closed" ),
494 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
495 "cleaned" ISNULL OR "closed" NOTNULL ),
496 CONSTRAINT "last_snapshot_on_full_freeze"
497 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
498 CONSTRAINT "freeze_requires_snapshot"
499 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
500 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
501 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
502 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
503 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
504 CREATE INDEX "issue_created_idx" ON "issue" ("created");
505 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
506 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
507 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
508 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
509 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
510 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
512 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
514 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
515 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.';
516 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.';
517 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.';
518 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
519 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
520 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
521 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
522 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
523 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
524 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
525 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';
526 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
527 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';
530 CREATE TABLE "issue_setting" (
531 PRIMARY KEY ("member_id", "key", "issue_id"),
532 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
533 "key" TEXT NOT NULL,
534 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
535 "value" TEXT NOT NULL );
537 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
540 CREATE TABLE "initiative" (
541 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
542 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
543 "id" SERIAL4 PRIMARY KEY,
544 "name" TEXT NOT NULL,
545 "discussion_url" TEXT,
546 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
547 "revoked" TIMESTAMPTZ,
548 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
549 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
550 "admitted" BOOLEAN,
551 "supporter_count" INT4,
552 "informed_supporter_count" INT4,
553 "satisfied_supporter_count" INT4,
554 "satisfied_informed_supporter_count" INT4,
555 "positive_votes" INT4,
556 "negative_votes" INT4,
557 "attainable" BOOLEAN,
558 "favored_to_status_quo" BOOLEAN,
559 "unfavored_to_status_quo" BOOLEAN,
560 "schulze_rank" INT4,
561 "reverse_beat_path" BOOLEAN,
562 "multistage_majority" BOOLEAN,
563 "eligible" BOOLEAN,
564 "winner" BOOLEAN,
565 "text_search_data" TSVECTOR,
566 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
567 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL),
568 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
569 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
570 CONSTRAINT "revoked_initiatives_cant_be_admitted"
571 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
572 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
573 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
574 ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND "attainable" ISNULL AND
575 "favored_to_status_quo" ISNULL AND "unfavored_to_status_quo" ISNULL AND
576 "schulze_rank" ISNULL AND
577 "multistage_majority" ISNULL AND "reverse_beat_path" ISNULL AND
578 "winner" ISNULL ) ),
579 CONSTRAINT "favored_excludes_unfavored" CHECK (NOT ("favored_to_status_quo" AND "unfavored_to_status_quo")) );
580 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
581 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
582 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
583 CREATE TRIGGER "update_text_search_data"
584 BEFORE INSERT OR UPDATE ON "initiative"
585 FOR EACH ROW EXECUTE PROCEDURE
586 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
587 "name", "discussion_url");
589 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.';
591 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
592 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
593 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoked the initiative';
594 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
595 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
596 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
597 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
598 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
599 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
600 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
601 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"';
602 COMMENT ON COLUMN "initiative"."favored_to_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)';
603 COMMENT ON COLUMN "initiative"."unfavored_to_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)';
604 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking without tie-breaking';
605 COMMENT ON COLUMN "initiative"."reverse_beat_path" IS 'TRUE, if there is a beat path (may include ties), from this initiative to the status quo';
606 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';
607 COMMENT ON COLUMN "initiative"."eligible" IS 'Initiative is "attainable" and depending on selected policy has no "reverse_beat_path" or "multistage_majority"';
608 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"';
611 CREATE TABLE "battle" (
612 "issue_id" INT4 NOT NULL,
613 "winning_initiative_id" INT4,
614 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
615 "losing_initiative_id" INT4,
616 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
617 "count" INT4 NOT NULL,
618 CONSTRAINT "initiative_ids_not_equal" CHECK (
619 "winning_initiative_id" != "losing_initiative_id" OR
620 ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
621 ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) );
622 CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
623 CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
624 CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
626 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';
629 CREATE TABLE "ignored_initiative" (
630 PRIMARY KEY ("initiative_id", "member_id"),
631 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
632 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
633 CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");
635 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
638 CREATE TABLE "initiative_setting" (
639 PRIMARY KEY ("member_id", "key", "initiative_id"),
640 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
641 "key" TEXT NOT NULL,
642 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
643 "value" TEXT NOT NULL );
645 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
648 CREATE TABLE "draft" (
649 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
650 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
651 "id" SERIAL8 PRIMARY KEY,
652 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
653 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
654 "formatting_engine" TEXT,
655 "content" TEXT NOT NULL,
656 "text_search_data" TSVECTOR );
657 CREATE INDEX "draft_created_idx" ON "draft" ("created");
658 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
659 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
660 CREATE TRIGGER "update_text_search_data"
661 BEFORE INSERT OR UPDATE ON "draft"
662 FOR EACH ROW EXECUTE PROCEDURE
663 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
665 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.';
667 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
668 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
671 CREATE TABLE "rendered_draft" (
672 PRIMARY KEY ("draft_id", "format"),
673 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
674 "format" TEXT,
675 "content" TEXT NOT NULL );
677 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)';
680 CREATE TABLE "suggestion" (
681 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
682 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
683 "id" SERIAL8 PRIMARY KEY,
684 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
685 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
686 "name" TEXT NOT NULL,
687 "formatting_engine" TEXT,
688 "content" TEXT NOT NULL DEFAULT '',
689 "text_search_data" TSVECTOR,
690 "minus2_unfulfilled_count" INT4,
691 "minus2_fulfilled_count" INT4,
692 "minus1_unfulfilled_count" INT4,
693 "minus1_fulfilled_count" INT4,
694 "plus1_unfulfilled_count" INT4,
695 "plus1_fulfilled_count" INT4,
696 "plus2_unfulfilled_count" INT4,
697 "plus2_fulfilled_count" INT4 );
698 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
699 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
700 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
701 CREATE TRIGGER "update_text_search_data"
702 BEFORE INSERT OR UPDATE ON "suggestion"
703 FOR EACH ROW EXECUTE PROCEDURE
704 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
705 "name", "content");
707 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';
709 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
710 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
711 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
712 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
713 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
714 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
715 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
716 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
719 CREATE TABLE "rendered_suggestion" (
720 PRIMARY KEY ("suggestion_id", "format"),
721 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
722 "format" TEXT,
723 "content" TEXT NOT NULL );
725 COMMENT ON TABLE "rendered_suggestion" IS 'This table may be used by frontends to cache "rendered" drafts (e.g. HTML output generated from wiki text)';
728 CREATE TABLE "suggestion_setting" (
729 PRIMARY KEY ("member_id", "key", "suggestion_id"),
730 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
731 "key" TEXT NOT NULL,
732 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
733 "value" TEXT NOT NULL );
735 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
738 CREATE TABLE "invite_code_unit" (
739 PRIMARY KEY ("invite_code_id", "unit_id"),
740 "invite_code_id" INT8 REFERENCES "invite_code" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
741 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
743 COMMENT ON TABLE "invite_code_unit" IS 'Units where accounts created with a given invite codes get voting rights';
746 CREATE TABLE "privilege" (
747 PRIMARY KEY ("unit_id", "member_id"),
748 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
749 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
750 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
751 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
752 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
753 "voting_right_manager" BOOLEAN NOT NULL DEFAULT FALSE,
754 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE );
756 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
758 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke admin privileges to/from other users';
759 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create or lock sub units';
760 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create or lock areas and set area parameters';
761 COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote inside the unit';
762 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to discuss and vote';
765 CREATE TABLE "membership" (
766 PRIMARY KEY ("area_id", "member_id"),
767 "area_id" INT4 REFERENCES "area" ("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 DEFAULT FALSE );
770 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
772 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
774 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.';
777 CREATE TABLE "interest" (
778 PRIMARY KEY ("issue_id", "member_id"),
779 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
780 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
781 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
783 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.';
786 CREATE TABLE "issue_autoreject" (
787 PRIMARY KEY ("issue_id", "member_id"),
788 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
789 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
790 "autoreject" BOOLEAN NOT NULL );
791 CREATE INDEX "issue_autoreject_member_id_idx" ON "issue_autoreject" ("member_id");
793 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.';
796 CREATE TABLE "initiator" (
797 PRIMARY KEY ("initiative_id", "member_id"),
798 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
799 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
800 "accepted" BOOLEAN );
801 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
803 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.';
805 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.';
808 CREATE TABLE "supporter" (
809 "issue_id" INT4 NOT NULL,
810 PRIMARY KEY ("initiative_id", "member_id"),
811 "initiative_id" INT4,
812 "member_id" INT4,
813 "draft_id" INT8 NOT NULL,
814 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
815 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
816 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
818 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.';
820 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
823 CREATE TABLE "opinion" (
824 "initiative_id" INT4 NOT NULL,
825 PRIMARY KEY ("suggestion_id", "member_id"),
826 "suggestion_id" INT8,
827 "member_id" INT4,
828 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
829 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
830 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
831 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
832 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
834 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.';
836 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
839 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
841 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
844 CREATE TABLE "delegation" (
845 "id" SERIAL8 PRIMARY KEY,
846 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
847 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
848 "scope" "delegation_scope" NOT NULL,
849 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
850 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
851 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
852 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
853 CONSTRAINT "no_unit_delegation_to_null"
854 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
855 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
856 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
857 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
858 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
859 UNIQUE ("unit_id", "truster_id"),
860 UNIQUE ("area_id", "truster_id"),
861 UNIQUE ("issue_id", "truster_id") );
862 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
863 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
865 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
867 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
868 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
869 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
872 CREATE TABLE "direct_population_snapshot" (
873 PRIMARY KEY ("issue_id", "event", "member_id"),
874 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
875 "event" "snapshot_event",
876 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
877 "weight" INT4 );
878 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
880 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';
882 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
883 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
886 CREATE TABLE "delegating_population_snapshot" (
887 PRIMARY KEY ("issue_id", "event", "member_id"),
888 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
889 "event" "snapshot_event",
890 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
891 "weight" INT4,
892 "scope" "delegation_scope" NOT NULL,
893 "delegate_member_ids" INT4[] NOT NULL );
894 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
896 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
898 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
899 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
900 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
901 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"';
904 CREATE TABLE "direct_interest_snapshot" (
905 PRIMARY KEY ("issue_id", "event", "member_id"),
906 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
907 "event" "snapshot_event",
908 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
909 "weight" INT4 );
910 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
912 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
914 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
915 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
918 CREATE TABLE "delegating_interest_snapshot" (
919 PRIMARY KEY ("issue_id", "event", "member_id"),
920 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
921 "event" "snapshot_event",
922 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
923 "weight" INT4,
924 "scope" "delegation_scope" NOT NULL,
925 "delegate_member_ids" INT4[] NOT NULL );
926 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
928 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
930 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
931 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
932 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
933 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"';
936 CREATE TABLE "direct_supporter_snapshot" (
937 "issue_id" INT4 NOT NULL,
938 PRIMARY KEY ("initiative_id", "event", "member_id"),
939 "initiative_id" INT4,
940 "event" "snapshot_event",
941 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
942 "informed" BOOLEAN NOT NULL,
943 "satisfied" BOOLEAN NOT NULL,
944 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
945 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
946 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
948 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
950 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
951 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
952 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
955 CREATE TABLE "non_voter" (
956 PRIMARY KEY ("issue_id", "member_id"),
957 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
958 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
959 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
961 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
964 CREATE TABLE "direct_voter" (
965 PRIMARY KEY ("issue_id", "member_id"),
966 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
967 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
968 "weight" INT4,
969 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
970 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
972 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.';
974 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
975 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
978 CREATE TABLE "delegating_voter" (
979 PRIMARY KEY ("issue_id", "member_id"),
980 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
981 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
982 "weight" INT4,
983 "scope" "delegation_scope" NOT NULL,
984 "delegate_member_ids" INT4[] NOT NULL );
985 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
987 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
989 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
990 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
991 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"';
994 CREATE TABLE "vote" (
995 "issue_id" INT4 NOT NULL,
996 PRIMARY KEY ("initiative_id", "member_id"),
997 "initiative_id" INT4,
998 "member_id" INT4,
999 "grade" INT4,
1000 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1001 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1002 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
1004 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.';
1006 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.';
1009 CREATE TABLE "issue_comment" (
1010 PRIMARY KEY ("issue_id", "member_id"),
1011 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1012 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1013 "changed" TIMESTAMPTZ NOT NULL DEFAULT now(),
1014 "formatting_engine" TEXT,
1015 "content" TEXT NOT NULL,
1016 "text_search_data" TSVECTOR );
1017 CREATE INDEX "issue_comment_member_id_idx" ON "issue_comment" ("member_id");
1018 CREATE INDEX "issue_comment_text_search_data_idx" ON "issue_comment" USING gin ("text_search_data");
1019 CREATE TRIGGER "update_text_search_data"
1020 BEFORE INSERT OR UPDATE ON "issue_comment"
1021 FOR EACH ROW EXECUTE PROCEDURE
1022 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
1024 COMMENT ON TABLE "issue_comment" IS 'Place to store free comments of members related to issues';
1026 COMMENT ON COLUMN "issue_comment"."changed" IS 'Time the comment was last changed';
1029 CREATE TABLE "rendered_issue_comment" (
1030 PRIMARY KEY ("issue_id", "member_id", "format"),
1031 FOREIGN KEY ("issue_id", "member_id")
1032 REFERENCES "issue_comment" ("issue_id", "member_id")
1033 ON DELETE CASCADE ON UPDATE CASCADE,
1034 "issue_id" INT4,
1035 "member_id" INT4,
1036 "format" TEXT,
1037 "content" TEXT NOT NULL );
1039 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)';
1042 CREATE TABLE "voting_comment" (
1043 PRIMARY KEY ("issue_id", "member_id"),
1044 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1045 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1046 "changed" TIMESTAMPTZ,
1047 "formatting_engine" TEXT,
1048 "content" TEXT NOT NULL,
1049 "text_search_data" TSVECTOR );
1050 CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id");
1051 CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data");
1052 CREATE TRIGGER "update_text_search_data"
1053 BEFORE INSERT OR UPDATE ON "voting_comment"
1054 FOR EACH ROW EXECUTE PROCEDURE
1055 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
1057 COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.';
1059 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.';
1062 CREATE TABLE "rendered_voting_comment" (
1063 PRIMARY KEY ("issue_id", "member_id", "format"),
1064 FOREIGN KEY ("issue_id", "member_id")
1065 REFERENCES "voting_comment" ("issue_id", "member_id")
1066 ON DELETE CASCADE ON UPDATE CASCADE,
1067 "issue_id" INT4,
1068 "member_id" INT4,
1069 "format" TEXT,
1070 "content" TEXT NOT NULL );
1072 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)';
1075 CREATE TYPE "event_type" AS ENUM (
1076 'issue_state_changed',
1077 'initiative_created_in_new_issue',
1078 'initiative_created_in_existing_issue',
1079 'initiative_revoked',
1080 'new_draft_created',
1081 'suggestion_created');
1083 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
1086 CREATE TABLE "event" (
1087 "id" SERIAL8 PRIMARY KEY,
1088 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
1089 "event" "event_type" NOT NULL,
1090 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1091 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1092 "state" "issue_state" CHECK ("state" != 'calculation'),
1093 "initiative_id" INT4,
1094 "draft_id" INT8,
1095 "suggestion_id" INT8,
1096 FOREIGN KEY ("issue_id", "initiative_id")
1097 REFERENCES "initiative" ("issue_id", "id")
1098 ON DELETE CASCADE ON UPDATE CASCADE,
1099 FOREIGN KEY ("initiative_id", "draft_id")
1100 REFERENCES "draft" ("initiative_id", "id")
1101 ON DELETE CASCADE ON UPDATE CASCADE,
1102 FOREIGN KEY ("initiative_id", "suggestion_id")
1103 REFERENCES "suggestion" ("initiative_id", "id")
1104 ON DELETE CASCADE ON UPDATE CASCADE,
1105 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
1106 "event" != 'issue_state_changed' OR (
1107 "member_id" ISNULL AND
1108 "issue_id" NOTNULL AND
1109 "state" NOTNULL AND
1110 "initiative_id" ISNULL AND
1111 "draft_id" ISNULL AND
1112 "suggestion_id" ISNULL )),
1113 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
1114 "event" NOT IN (
1115 'initiative_created_in_new_issue',
1116 'initiative_created_in_existing_issue',
1117 'initiative_revoked',
1118 'new_draft_created'
1119 ) OR (
1120 "member_id" NOTNULL AND
1121 "issue_id" NOTNULL AND
1122 "state" NOTNULL AND
1123 "initiative_id" NOTNULL AND
1124 "draft_id" NOTNULL AND
1125 "suggestion_id" ISNULL )),
1126 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
1127 "event" != 'suggestion_created' OR (
1128 "member_id" NOTNULL AND
1129 "issue_id" NOTNULL AND
1130 "state" NOTNULL AND
1131 "initiative_id" NOTNULL AND
1132 "draft_id" ISNULL AND
1133 "suggestion_id" NOTNULL )) );
1135 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
1137 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
1138 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';
1139 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';
1140 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';
1144 ----------------------------------------------
1145 -- Writing of history entries and event log --
1146 ----------------------------------------------
1148 CREATE FUNCTION "write_member_history_trigger"()
1149 RETURNS TRIGGER
1150 LANGUAGE 'plpgsql' VOLATILE AS $$
1151 BEGIN
1152 IF
1153 NEW."active" != OLD."active" OR
1154 NEW."name" != OLD."name"
1155 THEN
1156 INSERT INTO "member_history"
1157 ("member_id", "active", "name")
1158 VALUES (NEW."id", OLD."active", OLD."name");
1159 END IF;
1160 RETURN NULL;
1161 END;
1162 $$;
1164 CREATE TRIGGER "write_member_history"
1165 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1166 "write_member_history_trigger"();
1168 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
1169 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
1172 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
1173 RETURNS TRIGGER
1174 LANGUAGE 'plpgsql' VOLATILE AS $$
1175 BEGIN
1176 IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN
1177 INSERT INTO "event" ("event", "issue_id", "state")
1178 VALUES ('issue_state_changed', NEW."id", NEW."state");
1179 END IF;
1180 RETURN NULL;
1181 END;
1182 $$;
1184 CREATE TRIGGER "write_event_issue_state_changed"
1185 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
1186 "write_event_issue_state_changed_trigger"();
1188 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
1189 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
1192 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1193 RETURNS TRIGGER
1194 LANGUAGE 'plpgsql' VOLATILE AS $$
1195 DECLARE
1196 "initiative_row" "initiative"%ROWTYPE;
1197 "issue_row" "issue"%ROWTYPE;
1198 "event_v" "event_type";
1199 BEGIN
1200 SELECT * INTO "initiative_row" FROM "initiative"
1201 WHERE "id" = NEW."initiative_id";
1202 SELECT * INTO "issue_row" FROM "issue"
1203 WHERE "id" = "initiative_row"."issue_id";
1204 IF EXISTS (
1205 SELECT NULL FROM "draft"
1206 WHERE "initiative_id" = NEW."initiative_id"
1207 AND "id" != NEW."id"
1208 ) THEN
1209 "event_v" := 'new_draft_created';
1210 ELSE
1211 IF EXISTS (
1212 SELECT NULL FROM "initiative"
1213 WHERE "issue_id" = "initiative_row"."issue_id"
1214 AND "id" != "initiative_row"."id"
1215 ) THEN
1216 "event_v" := 'initiative_created_in_existing_issue';
1217 ELSE
1218 "event_v" := 'initiative_created_in_new_issue';
1219 END IF;
1220 END IF;
1221 INSERT INTO "event" (
1222 "event", "member_id",
1223 "issue_id", "state", "initiative_id", "draft_id"
1224 ) VALUES (
1225 "event_v",
1226 NEW."author_id",
1227 "initiative_row"."issue_id",
1228 "issue_row"."state",
1229 "initiative_row"."id",
1230 NEW."id" );
1231 RETURN NULL;
1232 END;
1233 $$;
1235 CREATE TRIGGER "write_event_initiative_or_draft_created"
1236 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
1237 "write_event_initiative_or_draft_created_trigger"();
1239 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
1240 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
1243 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
1244 RETURNS TRIGGER
1245 LANGUAGE 'plpgsql' VOLATILE AS $$
1246 DECLARE
1247 "issue_row" "issue"%ROWTYPE;
1248 BEGIN
1249 SELECT * INTO "issue_row" FROM "issue"
1250 WHERE "id" = NEW."issue_id";
1251 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1252 INSERT INTO "event" (
1253 "event", "member_id", "issue_id", "state", "initiative_id"
1254 ) VALUES (
1255 'initiative_revoked',
1256 NEW."revoked_by_member_id",
1257 NEW."issue_id",
1258 "issue_row"."state",
1259 NEW."id" );
1260 END IF;
1261 RETURN NULL;
1262 END;
1263 $$;
1265 CREATE TRIGGER "write_event_initiative_revoked"
1266 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
1267 "write_event_initiative_revoked_trigger"();
1269 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
1270 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
1273 CREATE FUNCTION "write_event_suggestion_created_trigger"()
1274 RETURNS TRIGGER
1275 LANGUAGE 'plpgsql' VOLATILE AS $$
1276 DECLARE
1277 "initiative_row" "initiative"%ROWTYPE;
1278 "issue_row" "issue"%ROWTYPE;
1279 BEGIN
1280 SELECT * INTO "initiative_row" FROM "initiative"
1281 WHERE "id" = NEW."initiative_id";
1282 SELECT * INTO "issue_row" FROM "issue"
1283 WHERE "id" = "initiative_row"."issue_id";
1284 INSERT INTO "event" (
1285 "event", "member_id",
1286 "issue_id", "state", "initiative_id", "suggestion_id"
1287 ) VALUES (
1288 'suggestion_created',
1289 NEW."author_id",
1290 "initiative_row"."issue_id",
1291 "issue_row"."state",
1292 "initiative_row"."id",
1293 NEW."id" );
1294 RETURN NULL;
1295 END;
1296 $$;
1298 CREATE TRIGGER "write_event_suggestion_created"
1299 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1300 "write_event_suggestion_created_trigger"();
1302 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
1303 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1307 ----------------------------
1308 -- Additional constraints --
1309 ----------------------------
1312 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
1313 RETURNS TRIGGER
1314 LANGUAGE 'plpgsql' VOLATILE AS $$
1315 BEGIN
1316 IF NOT EXISTS (
1317 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
1318 ) THEN
1319 --RAISE 'Cannot create issue without an initial initiative.' USING
1320 -- ERRCODE = 'integrity_constraint_violation',
1321 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
1322 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
1323 END IF;
1324 RETURN NULL;
1325 END;
1326 $$;
1328 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
1329 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
1330 FOR EACH ROW EXECUTE PROCEDURE
1331 "issue_requires_first_initiative_trigger"();
1333 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
1334 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
1337 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
1338 RETURNS TRIGGER
1339 LANGUAGE 'plpgsql' VOLATILE AS $$
1340 DECLARE
1341 "reference_lost" BOOLEAN;
1342 BEGIN
1343 IF TG_OP = 'DELETE' THEN
1344 "reference_lost" := TRUE;
1345 ELSE
1346 "reference_lost" := NEW."issue_id" != OLD."issue_id";
1347 END IF;
1348 IF
1349 "reference_lost" AND NOT EXISTS (
1350 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
1351 )
1352 THEN
1353 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
1354 END IF;
1355 RETURN NULL;
1356 END;
1357 $$;
1359 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
1360 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1361 FOR EACH ROW EXECUTE PROCEDURE
1362 "last_initiative_deletes_issue_trigger"();
1364 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
1365 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
1368 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
1369 RETURNS TRIGGER
1370 LANGUAGE 'plpgsql' VOLATILE AS $$
1371 BEGIN
1372 IF NOT EXISTS (
1373 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
1374 ) THEN
1375 --RAISE 'Cannot create initiative without an initial draft.' USING
1376 -- ERRCODE = 'integrity_constraint_violation',
1377 -- HINT = 'Create issue, initiative and draft within the same transaction.';
1378 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
1379 END IF;
1380 RETURN NULL;
1381 END;
1382 $$;
1384 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
1385 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1386 FOR EACH ROW EXECUTE PROCEDURE
1387 "initiative_requires_first_draft_trigger"();
1389 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
1390 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
1393 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
1394 RETURNS TRIGGER
1395 LANGUAGE 'plpgsql' VOLATILE AS $$
1396 DECLARE
1397 "reference_lost" BOOLEAN;
1398 BEGIN
1399 IF TG_OP = 'DELETE' THEN
1400 "reference_lost" := TRUE;
1401 ELSE
1402 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
1403 END IF;
1404 IF
1405 "reference_lost" AND NOT EXISTS (
1406 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
1407 )
1408 THEN
1409 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
1410 END IF;
1411 RETURN NULL;
1412 END;
1413 $$;
1415 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
1416 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
1417 FOR EACH ROW EXECUTE PROCEDURE
1418 "last_draft_deletes_initiative_trigger"();
1420 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
1421 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
1424 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
1425 RETURNS TRIGGER
1426 LANGUAGE 'plpgsql' VOLATILE AS $$
1427 BEGIN
1428 IF NOT EXISTS (
1429 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
1430 ) THEN
1431 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
1432 END IF;
1433 RETURN NULL;
1434 END;
1435 $$;
1437 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
1438 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
1439 FOR EACH ROW EXECUTE PROCEDURE
1440 "suggestion_requires_first_opinion_trigger"();
1442 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1443 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1446 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1447 RETURNS TRIGGER
1448 LANGUAGE 'plpgsql' VOLATILE AS $$
1449 DECLARE
1450 "reference_lost" BOOLEAN;
1451 BEGIN
1452 IF TG_OP = 'DELETE' THEN
1453 "reference_lost" := TRUE;
1454 ELSE
1455 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1456 END IF;
1457 IF
1458 "reference_lost" AND NOT EXISTS (
1459 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1460 )
1461 THEN
1462 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1463 END IF;
1464 RETURN NULL;
1465 END;
1466 $$;
1468 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1469 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1470 FOR EACH ROW EXECUTE PROCEDURE
1471 "last_opinion_deletes_suggestion_trigger"();
1473 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1474 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1478 ---------------------------------------------------------------
1479 -- Ensure that votes are not modified when issues are frozen --
1480 ---------------------------------------------------------------
1482 -- NOTE: Frontends should ensure this anyway, but in case of programming
1483 -- errors the following triggers ensure data integrity.
1486 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1487 RETURNS TRIGGER
1488 LANGUAGE 'plpgsql' VOLATILE AS $$
1489 DECLARE
1490 "issue_id_v" "issue"."id"%TYPE;
1491 "issue_row" "issue"%ROWTYPE;
1492 BEGIN
1493 IF TG_OP = 'DELETE' THEN
1494 "issue_id_v" := OLD."issue_id";
1495 ELSE
1496 "issue_id_v" := NEW."issue_id";
1497 END IF;
1498 SELECT INTO "issue_row" * FROM "issue"
1499 WHERE "id" = "issue_id_v" FOR SHARE;
1500 IF "issue_row"."closed" NOTNULL THEN
1501 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
1502 END IF;
1503 RETURN NULL;
1504 END;
1505 $$;
1507 CREATE TRIGGER "forbid_changes_on_closed_issue"
1508 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1509 FOR EACH ROW EXECUTE PROCEDURE
1510 "forbid_changes_on_closed_issue_trigger"();
1512 CREATE TRIGGER "forbid_changes_on_closed_issue"
1513 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1514 FOR EACH ROW EXECUTE PROCEDURE
1515 "forbid_changes_on_closed_issue_trigger"();
1517 CREATE TRIGGER "forbid_changes_on_closed_issue"
1518 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1519 FOR EACH ROW EXECUTE PROCEDURE
1520 "forbid_changes_on_closed_issue_trigger"();
1522 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"';
1523 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';
1524 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';
1525 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';
1529 --------------------------------------------------------------------
1530 -- Auto-retrieval of fields only needed for referential integrity --
1531 --------------------------------------------------------------------
1534 CREATE FUNCTION "autofill_issue_id_trigger"()
1535 RETURNS TRIGGER
1536 LANGUAGE 'plpgsql' VOLATILE AS $$
1537 BEGIN
1538 IF NEW."issue_id" ISNULL THEN
1539 SELECT "issue_id" INTO NEW."issue_id"
1540 FROM "initiative" WHERE "id" = NEW."initiative_id";
1541 END IF;
1542 RETURN NEW;
1543 END;
1544 $$;
1546 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1547 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1549 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1550 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1552 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1553 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1554 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1557 CREATE FUNCTION "autofill_initiative_id_trigger"()
1558 RETURNS TRIGGER
1559 LANGUAGE 'plpgsql' VOLATILE AS $$
1560 BEGIN
1561 IF NEW."initiative_id" ISNULL THEN
1562 SELECT "initiative_id" INTO NEW."initiative_id"
1563 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1564 END IF;
1565 RETURN NEW;
1566 END;
1567 $$;
1569 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1570 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1572 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1573 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1577 -----------------------------------------------------
1578 -- Automatic calculation of certain default values --
1579 -----------------------------------------------------
1582 CREATE FUNCTION "copy_timings_trigger"()
1583 RETURNS TRIGGER
1584 LANGUAGE 'plpgsql' VOLATILE AS $$
1585 DECLARE
1586 "policy_row" "policy"%ROWTYPE;
1587 BEGIN
1588 SELECT * INTO "policy_row" FROM "policy"
1589 WHERE "id" = NEW."policy_id";
1590 IF NEW."admission_time" ISNULL THEN
1591 NEW."admission_time" := "policy_row"."admission_time";
1592 END IF;
1593 IF NEW."discussion_time" ISNULL THEN
1594 NEW."discussion_time" := "policy_row"."discussion_time";
1595 END IF;
1596 IF NEW."verification_time" ISNULL THEN
1597 NEW."verification_time" := "policy_row"."verification_time";
1598 END IF;
1599 IF NEW."voting_time" ISNULL THEN
1600 NEW."voting_time" := "policy_row"."voting_time";
1601 END IF;
1602 RETURN NEW;
1603 END;
1604 $$;
1606 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1607 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1609 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1610 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1613 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
1614 RETURNS TRIGGER
1615 LANGUAGE 'plpgsql' VOLATILE AS $$
1616 BEGIN
1617 IF NEW."draft_id" ISNULL THEN
1618 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1619 WHERE "initiative_id" = NEW."initiative_id";
1620 END IF;
1621 RETURN NEW;
1622 END;
1623 $$;
1625 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1626 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
1628 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
1629 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';
1633 ----------------------------------------
1634 -- Automatic creation of dependencies --
1635 ----------------------------------------
1638 CREATE FUNCTION "autocreate_interest_trigger"()
1639 RETURNS TRIGGER
1640 LANGUAGE 'plpgsql' VOLATILE AS $$
1641 BEGIN
1642 IF NOT EXISTS (
1643 SELECT NULL FROM "initiative" JOIN "interest"
1644 ON "initiative"."issue_id" = "interest"."issue_id"
1645 WHERE "initiative"."id" = NEW."initiative_id"
1646 AND "interest"."member_id" = NEW."member_id"
1647 ) THEN
1648 BEGIN
1649 INSERT INTO "interest" ("issue_id", "member_id")
1650 SELECT "issue_id", NEW."member_id"
1651 FROM "initiative" WHERE "id" = NEW."initiative_id";
1652 EXCEPTION WHEN unique_violation THEN END;
1653 END IF;
1654 RETURN NEW;
1655 END;
1656 $$;
1658 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1659 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1661 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1662 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';
1665 CREATE FUNCTION "autocreate_supporter_trigger"()
1666 RETURNS TRIGGER
1667 LANGUAGE 'plpgsql' VOLATILE AS $$
1668 BEGIN
1669 IF NOT EXISTS (
1670 SELECT NULL FROM "suggestion" JOIN "supporter"
1671 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1672 WHERE "suggestion"."id" = NEW."suggestion_id"
1673 AND "supporter"."member_id" = NEW."member_id"
1674 ) THEN
1675 BEGIN
1676 INSERT INTO "supporter" ("initiative_id", "member_id")
1677 SELECT "initiative_id", NEW."member_id"
1678 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1679 EXCEPTION WHEN unique_violation THEN END;
1680 END IF;
1681 RETURN NEW;
1682 END;
1683 $$;
1685 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1686 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1688 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1689 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.';
1693 ------------------------------------------
1694 -- Views and helper functions for views --
1695 ------------------------------------------
1698 CREATE VIEW "unit_delegation" AS
1699 SELECT
1700 "unit"."id" AS "unit_id",
1701 "delegation"."id",
1702 "delegation"."truster_id",
1703 "delegation"."trustee_id",
1704 "delegation"."scope"
1705 FROM "unit"
1706 JOIN "delegation"
1707 ON "delegation"."unit_id" = "unit"."id"
1708 JOIN "member"
1709 ON "delegation"."truster_id" = "member"."id"
1710 JOIN "privilege"
1711 ON "delegation"."unit_id" = "privilege"."unit_id"
1712 AND "delegation"."truster_id" = "privilege"."member_id"
1713 WHERE "member"."active" AND "privilege"."voting_right";
1715 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
1718 CREATE VIEW "area_delegation" AS
1719 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1720 "area"."id" AS "area_id",
1721 "delegation"."id",
1722 "delegation"."truster_id",
1723 "delegation"."trustee_id",
1724 "delegation"."scope"
1725 FROM "area"
1726 JOIN "delegation"
1727 ON "delegation"."unit_id" = "area"."unit_id"
1728 OR "delegation"."area_id" = "area"."id"
1729 JOIN "member"
1730 ON "delegation"."truster_id" = "member"."id"
1731 JOIN "privilege"
1732 ON "area"."unit_id" = "privilege"."unit_id"
1733 AND "delegation"."truster_id" = "privilege"."member_id"
1734 WHERE "member"."active" AND "privilege"."voting_right"
1735 ORDER BY
1736 "area"."id",
1737 "delegation"."truster_id",
1738 "delegation"."scope" DESC;
1740 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
1743 CREATE VIEW "issue_delegation" AS
1744 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1745 "issue"."id" AS "issue_id",
1746 "delegation"."id",
1747 "delegation"."truster_id",
1748 "delegation"."trustee_id",
1749 "delegation"."scope"
1750 FROM "issue"
1751 JOIN "area"
1752 ON "area"."id" = "issue"."area_id"
1753 JOIN "delegation"
1754 ON "delegation"."unit_id" = "area"."unit_id"
1755 OR "delegation"."area_id" = "area"."id"
1756 OR "delegation"."issue_id" = "issue"."id"
1757 JOIN "member"
1758 ON "delegation"."truster_id" = "member"."id"
1759 JOIN "privilege"
1760 ON "area"."unit_id" = "privilege"."unit_id"
1761 AND "delegation"."truster_id" = "privilege"."member_id"
1762 WHERE "member"."active" AND "privilege"."voting_right"
1763 ORDER BY
1764 "issue"."id",
1765 "delegation"."truster_id",
1766 "delegation"."scope" DESC;
1768 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
1771 CREATE FUNCTION "membership_weight_with_skipping"
1772 ( "area_id_p" "area"."id"%TYPE,
1773 "member_id_p" "member"."id"%TYPE,
1774 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1775 RETURNS INT4
1776 LANGUAGE 'plpgsql' STABLE AS $$
1777 DECLARE
1778 "sum_v" INT4;
1779 "delegation_row" "area_delegation"%ROWTYPE;
1780 BEGIN
1781 "sum_v" := 1;
1782 FOR "delegation_row" IN
1783 SELECT "area_delegation".*
1784 FROM "area_delegation" LEFT JOIN "membership"
1785 ON "membership"."area_id" = "area_id_p"
1786 AND "membership"."member_id" = "area_delegation"."truster_id"
1787 WHERE "area_delegation"."area_id" = "area_id_p"
1788 AND "area_delegation"."trustee_id" = "member_id_p"
1789 AND "membership"."member_id" ISNULL
1790 LOOP
1791 IF NOT
1792 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1793 THEN
1794 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1795 "area_id_p",
1796 "delegation_row"."truster_id",
1797 "skip_member_ids_p" || "delegation_row"."truster_id"
1798 );
1799 END IF;
1800 END LOOP;
1801 RETURN "sum_v";
1802 END;
1803 $$;
1805 COMMENT ON FUNCTION "membership_weight_with_skipping"
1806 ( "area"."id"%TYPE,
1807 "member"."id"%TYPE,
1808 INT4[] )
1809 IS 'Helper function for "membership_weight" function';
1812 CREATE FUNCTION "membership_weight"
1813 ( "area_id_p" "area"."id"%TYPE,
1814 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1815 RETURNS INT4
1816 LANGUAGE 'plpgsql' STABLE AS $$
1817 BEGIN
1818 RETURN "membership_weight_with_skipping"(
1819 "area_id_p",
1820 "member_id_p",
1821 ARRAY["member_id_p"]
1822 );
1823 END;
1824 $$;
1826 COMMENT ON FUNCTION "membership_weight"
1827 ( "area"."id"%TYPE,
1828 "member"."id"%TYPE )
1829 IS 'Calculates the potential voting weight of a member in a given area';
1832 CREATE VIEW "member_count_view" AS
1833 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1835 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1838 CREATE VIEW "unit_member_count" AS
1839 SELECT
1840 "unit"."id" AS "unit_id",
1841 sum("member"."id") AS "member_count"
1842 FROM "unit"
1843 LEFT JOIN "privilege"
1844 ON "privilege"."unit_id" = "unit"."id"
1845 AND "privilege"."voting_right"
1846 LEFT JOIN "member"
1847 ON "member"."id" = "privilege"."member_id"
1848 AND "member"."active"
1849 GROUP BY "unit"."id";
1851 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
1854 CREATE VIEW "area_member_count" AS
1855 SELECT
1856 "area"."id" AS "area_id",
1857 count("member"."id") AS "direct_member_count",
1858 coalesce(
1859 sum(
1860 CASE WHEN "member"."id" NOTNULL THEN
1861 "membership_weight"("area"."id", "member"."id")
1862 ELSE 0 END
1863 )
1864 ) AS "member_weight",
1865 coalesce(
1866 sum(
1867 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
1868 "membership_weight"("area"."id", "member"."id")
1869 ELSE 0 END
1870 )
1871 ) AS "autoreject_weight"
1872 FROM "area"
1873 LEFT JOIN "membership"
1874 ON "area"."id" = "membership"."area_id"
1875 LEFT JOIN "privilege"
1876 ON "privilege"."unit_id" = "area"."unit_id"
1877 AND "privilege"."member_id" = "membership"."member_id"
1878 AND "privilege"."voting_right"
1879 LEFT JOIN "member"
1880 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
1881 AND "member"."active"
1882 GROUP BY "area"."id";
1884 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count", "member_weight" and "autoreject_weight" columns of table "area"';
1887 CREATE VIEW "opening_draft" AS
1888 SELECT "draft".* FROM (
1889 SELECT
1890 "initiative"."id" AS "initiative_id",
1891 min("draft"."id") AS "draft_id"
1892 FROM "initiative" JOIN "draft"
1893 ON "initiative"."id" = "draft"."initiative_id"
1894 GROUP BY "initiative"."id"
1895 ) AS "subquery"
1896 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1898 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1901 CREATE VIEW "current_draft" AS
1902 SELECT "draft".* FROM (
1903 SELECT
1904 "initiative"."id" AS "initiative_id",
1905 max("draft"."id") AS "draft_id"
1906 FROM "initiative" JOIN "draft"
1907 ON "initiative"."id" = "draft"."initiative_id"
1908 GROUP BY "initiative"."id"
1909 ) AS "subquery"
1910 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1912 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1915 CREATE VIEW "critical_opinion" AS
1916 SELECT * FROM "opinion"
1917 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
1918 OR ("degree" = -2 AND "fulfilled" = TRUE);
1920 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
1923 CREATE VIEW "battle_participant" AS
1924 SELECT "initiative"."id", "initiative"."issue_id"
1925 FROM "issue" JOIN "initiative"
1926 ON "issue"."id" = "initiative"."issue_id"
1927 WHERE "initiative"."admitted"
1928 UNION ALL
1929 SELECT NULL, "id" AS "issue_id"
1930 FROM "issue";
1932 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
1935 CREATE VIEW "battle_view" AS
1936 SELECT
1937 "issue"."id" AS "issue_id",
1938 "winning_initiative"."id" AS "winning_initiative_id",
1939 "losing_initiative"."id" AS "losing_initiative_id",
1940 sum(
1941 CASE WHEN
1942 coalesce("better_vote"."grade", 0) >
1943 coalesce("worse_vote"."grade", 0)
1944 THEN "direct_voter"."weight" ELSE 0 END
1945 ) AS "count"
1946 FROM "issue"
1947 LEFT JOIN "direct_voter"
1948 ON "issue"."id" = "direct_voter"."issue_id"
1949 JOIN "battle_participant" AS "winning_initiative"
1950 ON "issue"."id" = "winning_initiative"."issue_id"
1951 JOIN "battle_participant" AS "losing_initiative"
1952 ON "issue"."id" = "losing_initiative"."issue_id"
1953 LEFT JOIN "vote" AS "better_vote"
1954 ON "direct_voter"."member_id" = "better_vote"."member_id"
1955 AND "winning_initiative"."id" = "better_vote"."initiative_id"
1956 LEFT JOIN "vote" AS "worse_vote"
1957 ON "direct_voter"."member_id" = "worse_vote"."member_id"
1958 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
1959 WHERE "issue"."closed" NOTNULL
1960 AND "issue"."cleaned" ISNULL
1961 AND (
1962 "winning_initiative"."id" != "losing_initiative"."id" OR
1963 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
1964 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
1965 GROUP BY
1966 "issue"."id",
1967 "winning_initiative"."id",
1968 "losing_initiative"."id";
1970 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';
1973 CREATE VIEW "expired_session" AS
1974 SELECT * FROM "session" WHERE now() > "expiry";
1976 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
1977 DELETE FROM "session" WHERE "ident" = OLD."ident";
1979 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
1980 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
1983 CREATE VIEW "open_issue" AS
1984 SELECT * FROM "issue" WHERE "closed" ISNULL;
1986 COMMENT ON VIEW "open_issue" IS 'All open issues';
1989 CREATE VIEW "issue_with_ranks_missing" AS
1990 SELECT * FROM "issue"
1991 WHERE "fully_frozen" NOTNULL
1992 AND "closed" NOTNULL
1993 AND "ranks_available" = FALSE;
1995 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
1998 CREATE VIEW "member_contingent" AS
1999 SELECT
2000 "member"."id" AS "member_id",
2001 "contingent"."time_frame",
2002 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
2003 (
2004 SELECT count(1) FROM "draft"
2005 WHERE "draft"."author_id" = "member"."id"
2006 AND "draft"."created" > now() - "contingent"."time_frame"
2007 ) + (
2008 SELECT count(1) FROM "suggestion"
2009 WHERE "suggestion"."author_id" = "member"."id"
2010 AND "suggestion"."created" > now() - "contingent"."time_frame"
2011 )
2012 ELSE NULL END AS "text_entry_count",
2013 "contingent"."text_entry_limit",
2014 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
2015 SELECT count(1) FROM "opening_draft"
2016 WHERE "opening_draft"."author_id" = "member"."id"
2017 AND "opening_draft"."created" > now() - "contingent"."time_frame"
2018 ) ELSE NULL END AS "initiative_count",
2019 "contingent"."initiative_limit"
2020 FROM "member" CROSS JOIN "contingent";
2022 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
2024 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
2025 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
2028 CREATE VIEW "member_contingent_left" AS
2029 SELECT
2030 "member_id",
2031 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
2032 max("initiative_limit" - "initiative_count") AS "initiatives_left"
2033 FROM "member_contingent" GROUP BY "member_id";
2035 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.';
2038 CREATE VIEW "event_seen_by_member" AS
2039 SELECT
2040 "member"."id" AS "seen_by_member_id",
2041 CASE WHEN "event"."state" IN (
2042 'voting',
2043 'finished_without_winner',
2044 'finished_with_winner'
2045 ) THEN
2046 'voting'::"notify_level"
2047 ELSE
2048 CASE WHEN "event"."state" IN (
2049 'verification',
2050 'canceled_after_revocation_during_verification',
2051 'canceled_no_initiative_admitted'
2052 ) THEN
2053 'verification'::"notify_level"
2054 ELSE
2055 CASE WHEN "event"."state" IN (
2056 'discussion',
2057 'canceled_after_revocation_during_discussion'
2058 ) THEN
2059 'discussion'::"notify_level"
2060 ELSE
2061 'all'::"notify_level"
2062 END
2063 END
2064 END AS "notify_level",
2065 "event".*
2066 FROM "member" CROSS JOIN "event"
2067 LEFT JOIN "issue"
2068 ON "event"."issue_id" = "issue"."id"
2069 LEFT JOIN "membership"
2070 ON "member"."id" = "membership"."member_id"
2071 AND "issue"."area_id" = "membership"."area_id"
2072 LEFT JOIN "interest"
2073 ON "member"."id" = "interest"."member_id"
2074 AND "event"."issue_id" = "interest"."issue_id"
2075 LEFT JOIN "supporter"
2076 ON "member"."id" = "supporter"."member_id"
2077 AND "event"."initiative_id" = "supporter"."initiative_id"
2078 LEFT JOIN "ignored_member"
2079 ON "member"."id" = "ignored_member"."member_id"
2080 AND "event"."member_id" = "ignored_member"."other_member_id"
2081 LEFT JOIN "ignored_initiative"
2082 ON "member"."id" = "ignored_initiative"."member_id"
2083 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2084 WHERE (
2085 "supporter"."member_id" NOTNULL OR
2086 "interest"."member_id" NOTNULL OR
2087 ( "membership"."member_id" NOTNULL AND
2088 "event"."event" IN (
2089 'issue_state_changed',
2090 'initiative_created_in_new_issue',
2091 'initiative_created_in_existing_issue',
2092 'initiative_revoked' ) ) )
2093 AND "ignored_member"."member_id" ISNULL
2094 AND "ignored_initiative"."member_id" ISNULL;
2096 COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support';
2099 CREATE VIEW "pending_notification" AS
2100 SELECT
2101 "member"."id" AS "seen_by_member_id",
2102 "event".*
2103 FROM "member" CROSS JOIN "event"
2104 LEFT JOIN "issue"
2105 ON "event"."issue_id" = "issue"."id"
2106 LEFT JOIN "membership"
2107 ON "member"."id" = "membership"."member_id"
2108 AND "issue"."area_id" = "membership"."area_id"
2109 LEFT JOIN "interest"
2110 ON "member"."id" = "interest"."member_id"
2111 AND "event"."issue_id" = "interest"."issue_id"
2112 LEFT JOIN "supporter"
2113 ON "member"."id" = "supporter"."member_id"
2114 AND "event"."initiative_id" = "supporter"."initiative_id"
2115 LEFT JOIN "ignored_member"
2116 ON "member"."id" = "ignored_member"."member_id"
2117 AND "event"."member_id" = "ignored_member"."other_member_id"
2118 LEFT JOIN "ignored_initiative"
2119 ON "member"."id" = "ignored_initiative"."member_id"
2120 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2121 WHERE (
2122 "member"."notify_event_id" ISNULL OR
2123 ( "member"."notify_event_id" NOTNULL AND
2124 "member"."notify_event_id" < "event"."id" ) )
2125 AND (
2126 ( "member"."notify_level" >= 'all' ) OR
2127 ( "member"."notify_level" >= 'voting' AND
2128 "event"."state" IN (
2129 'voting',
2130 'finished_without_winner',
2131 'finished_with_winner' ) ) OR
2132 ( "member"."notify_level" >= 'verification' AND
2133 "event"."state" IN (
2134 'verification',
2135 'canceled_after_revocation_during_verification',
2136 'canceled_no_initiative_admitted' ) ) OR
2137 ( "member"."notify_level" >= 'discussion' AND
2138 "event"."state" IN (
2139 'discussion',
2140 'canceled_after_revocation_during_discussion' ) ) )
2141 AND (
2142 "supporter"."member_id" NOTNULL OR
2143 "interest"."member_id" NOTNULL OR
2144 ( "membership"."member_id" NOTNULL AND
2145 "event"."event" IN (
2146 'issue_state_changed',
2147 'initiative_created_in_new_issue',
2148 'initiative_created_in_existing_issue',
2149 'initiative_revoked' ) ) )
2150 AND "ignored_member"."member_id" ISNULL
2151 AND "ignored_initiative"."member_id" ISNULL;
2153 COMMENT ON VIEW "pending_notification" IS 'Events to be sent to "notify_email" address of member referred to by "seen_by_member_id"';
2156 CREATE TYPE "timeline_event" AS ENUM (
2157 'issue_created',
2158 'issue_canceled',
2159 'issue_accepted',
2160 'issue_half_frozen',
2161 'issue_finished_without_voting',
2162 'issue_voting_started',
2163 'issue_finished_after_voting',
2164 'initiative_created',
2165 'initiative_revoked',
2166 'draft_created',
2167 'suggestion_created');
2169 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)';
2172 CREATE VIEW "timeline_issue" AS
2173 SELECT
2174 "created" AS "occurrence",
2175 'issue_created'::"timeline_event" AS "event",
2176 "id" AS "issue_id"
2177 FROM "issue"
2178 UNION ALL
2179 SELECT
2180 "closed" AS "occurrence",
2181 'issue_canceled'::"timeline_event" AS "event",
2182 "id" AS "issue_id"
2183 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
2184 UNION ALL
2185 SELECT
2186 "accepted" AS "occurrence",
2187 'issue_accepted'::"timeline_event" AS "event",
2188 "id" AS "issue_id"
2189 FROM "issue" WHERE "accepted" NOTNULL
2190 UNION ALL
2191 SELECT
2192 "half_frozen" AS "occurrence",
2193 'issue_half_frozen'::"timeline_event" AS "event",
2194 "id" AS "issue_id"
2195 FROM "issue" WHERE "half_frozen" NOTNULL
2196 UNION ALL
2197 SELECT
2198 "fully_frozen" AS "occurrence",
2199 'issue_voting_started'::"timeline_event" AS "event",
2200 "id" AS "issue_id"
2201 FROM "issue"
2202 WHERE "fully_frozen" NOTNULL
2203 AND ("closed" ISNULL OR "closed" != "fully_frozen")
2204 UNION ALL
2205 SELECT
2206 "closed" AS "occurrence",
2207 CASE WHEN "fully_frozen" = "closed" THEN
2208 'issue_finished_without_voting'::"timeline_event"
2209 ELSE
2210 'issue_finished_after_voting'::"timeline_event"
2211 END AS "event",
2212 "id" AS "issue_id"
2213 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
2215 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)';
2218 CREATE VIEW "timeline_initiative" AS
2219 SELECT
2220 "created" AS "occurrence",
2221 'initiative_created'::"timeline_event" AS "event",
2222 "id" AS "initiative_id"
2223 FROM "initiative"
2224 UNION ALL
2225 SELECT
2226 "revoked" AS "occurrence",
2227 'initiative_revoked'::"timeline_event" AS "event",
2228 "id" AS "initiative_id"
2229 FROM "initiative" WHERE "revoked" NOTNULL;
2231 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)';
2234 CREATE VIEW "timeline_draft" AS
2235 SELECT
2236 "created" AS "occurrence",
2237 'draft_created'::"timeline_event" AS "event",
2238 "id" AS "draft_id"
2239 FROM "draft";
2241 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)';
2244 CREATE VIEW "timeline_suggestion" AS
2245 SELECT
2246 "created" AS "occurrence",
2247 'suggestion_created'::"timeline_event" AS "event",
2248 "id" AS "suggestion_id"
2249 FROM "suggestion";
2251 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)';
2254 CREATE VIEW "timeline" AS
2255 SELECT
2256 "occurrence",
2257 "event",
2258 "issue_id",
2259 NULL AS "initiative_id",
2260 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
2261 NULL::INT8 AS "suggestion_id"
2262 FROM "timeline_issue"
2263 UNION ALL
2264 SELECT
2265 "occurrence",
2266 "event",
2267 NULL AS "issue_id",
2268 "initiative_id",
2269 NULL AS "draft_id",
2270 NULL AS "suggestion_id"
2271 FROM "timeline_initiative"
2272 UNION ALL
2273 SELECT
2274 "occurrence",
2275 "event",
2276 NULL AS "issue_id",
2277 NULL AS "initiative_id",
2278 "draft_id",
2279 NULL AS "suggestion_id"
2280 FROM "timeline_draft"
2281 UNION ALL
2282 SELECT
2283 "occurrence",
2284 "event",
2285 NULL AS "issue_id",
2286 NULL AS "initiative_id",
2287 NULL AS "draft_id",
2288 "suggestion_id"
2289 FROM "timeline_suggestion";
2291 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)';
2295 --------------------------------------------------
2296 -- Set returning function for delegation chains --
2297 --------------------------------------------------
2300 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
2301 ('first', 'intermediate', 'last', 'repetition');
2303 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
2306 CREATE TYPE "delegation_chain_row" AS (
2307 "index" INT4,
2308 "member_id" INT4,
2309 "member_valid" BOOLEAN,
2310 "participation" BOOLEAN,
2311 "overridden" BOOLEAN,
2312 "scope_in" "delegation_scope",
2313 "scope_out" "delegation_scope",
2314 "disabled_out" BOOLEAN,
2315 "loop" "delegation_chain_loop_tag" );
2317 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
2319 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
2320 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';
2321 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
2322 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
2323 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
2324 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
2325 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
2328 CREATE FUNCTION "delegation_chain"
2329 ( "member_id_p" "member"."id"%TYPE,
2330 "unit_id_p" "unit"."id"%TYPE,
2331 "area_id_p" "area"."id"%TYPE,
2332 "issue_id_p" "issue"."id"%TYPE,
2333 "simulate_trustee_id_p" "member"."id"%TYPE )
2334 RETURNS SETOF "delegation_chain_row"
2335 LANGUAGE 'plpgsql' STABLE AS $$
2336 DECLARE
2337 "scope_v" "delegation_scope";
2338 "unit_id_v" "unit"."id"%TYPE;
2339 "area_id_v" "area"."id"%TYPE;
2340 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2341 "loop_member_id_v" "member"."id"%TYPE;
2342 "output_row" "delegation_chain_row";
2343 "output_rows" "delegation_chain_row"[];
2344 "delegation_row" "delegation"%ROWTYPE;
2345 "row_count" INT4;
2346 "i" INT4;
2347 "loop_v" BOOLEAN;
2348 BEGIN
2349 IF
2350 "unit_id_p" NOTNULL AND
2351 "area_id_p" ISNULL AND
2352 "issue_id_p" ISNULL
2353 THEN
2354 "scope_v" := 'unit';
2355 "unit_id_v" := "unit_id_p";
2356 ELSIF
2357 "unit_id_p" ISNULL AND
2358 "area_id_p" NOTNULL AND
2359 "issue_id_p" ISNULL
2360 THEN
2361 "scope_v" := 'area';
2362 "area_id_v" := "area_id_p";
2363 SELECT "unit_id" INTO "unit_id_v"
2364 FROM "area" WHERE "id" = "area_id_v";
2365 ELSIF
2366 "unit_id_p" ISNULL AND
2367 "area_id_p" ISNULL AND
2368 "issue_id_p" NOTNULL
2369 THEN
2370 "scope_v" := 'issue';
2371 SELECT "area_id" INTO "area_id_v"
2372 FROM "issue" WHERE "id" = "issue_id_p";
2373 SELECT "unit_id" INTO "unit_id_v"
2374 FROM "area" WHERE "id" = "area_id_v";
2375 ELSE
2376 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2377 END IF;
2378 "visited_member_ids" := '{}';
2379 "loop_member_id_v" := NULL;
2380 "output_rows" := '{}';
2381 "output_row"."index" := 0;
2382 "output_row"."member_id" := "member_id_p";
2383 "output_row"."member_valid" := TRUE;
2384 "output_row"."participation" := FALSE;
2385 "output_row"."overridden" := FALSE;
2386 "output_row"."disabled_out" := FALSE;
2387 "output_row"."scope_out" := NULL;
2388 LOOP
2389 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2390 "loop_member_id_v" := "output_row"."member_id";
2391 ELSE
2392 "visited_member_ids" :=
2393 "visited_member_ids" || "output_row"."member_id";
2394 END IF;
2395 IF "output_row"."participation" THEN
2396 "output_row"."overridden" := TRUE;
2397 END IF;
2398 "output_row"."scope_in" := "output_row"."scope_out";
2399 IF EXISTS (
2400 SELECT NULL FROM "member" JOIN "privilege"
2401 ON "privilege"."member_id" = "member"."id"
2402 AND "privilege"."unit_id" = "unit_id_v"
2403 WHERE "id" = "output_row"."member_id"
2404 AND "member"."active" AND "privilege"."voting_right"
2405 ) THEN
2406 IF "scope_v" = 'unit' THEN
2407 SELECT * INTO "delegation_row" FROM "delegation"
2408 WHERE "truster_id" = "output_row"."member_id"
2409 AND "unit_id" = "unit_id_v";
2410 ELSIF "scope_v" = 'area' THEN
2411 "output_row"."participation" := EXISTS (
2412 SELECT NULL FROM "membership"
2413 WHERE "area_id" = "area_id_p"
2414 AND "member_id" = "output_row"."member_id"
2415 );
2416 SELECT * INTO "delegation_row" FROM "delegation"
2417 WHERE "truster_id" = "output_row"."member_id"
2418 AND (
2419 "unit_id" = "unit_id_v" OR
2420 "area_id" = "area_id_v"
2421 )
2422 ORDER BY "scope" DESC;
2423 ELSIF "scope_v" = 'issue' THEN
2424 "output_row"."participation" := EXISTS (
2425 SELECT NULL FROM "interest"
2426 WHERE "issue_id" = "issue_id_p"
2427 AND "member_id" = "output_row"."member_id"
2428 );
2429 SELECT * INTO "delegation_row" FROM "delegation"
2430 WHERE "truster_id" = "output_row"."member_id"
2431 AND (
2432 "unit_id" = "unit_id_v" OR
2433 "area_id" = "area_id_v" OR
2434 "issue_id" = "issue_id_p"
2435 )
2436 ORDER BY "scope" DESC;
2437 END IF;
2438 ELSE
2439 "output_row"."member_valid" := FALSE;
2440 "output_row"."participation" := FALSE;
2441 "output_row"."scope_out" := NULL;
2442 "delegation_row" := ROW(NULL);
2443 END IF;
2444 IF
2445 "output_row"."member_id" = "member_id_p" AND
2446 "simulate_trustee_id_p" NOTNULL
2447 THEN
2448 "output_row"."scope_out" := "scope_v";
2449 "output_rows" := "output_rows" || "output_row";
2450 "output_row"."member_id" := "simulate_trustee_id_p";
2451 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2452 "output_row"."scope_out" := "delegation_row"."scope";
2453 "output_rows" := "output_rows" || "output_row";
2454 "output_row"."member_id" := "delegation_row"."trustee_id";
2455 ELSIF "delegation_row"."scope" NOTNULL THEN
2456 "output_row"."scope_out" := "delegation_row"."scope";
2457 "output_row"."disabled_out" := TRUE;
2458 "output_rows" := "output_rows" || "output_row";
2459 EXIT;
2460 ELSE
2461 "output_row"."scope_out" := NULL;
2462 "output_rows" := "output_rows" || "output_row";
2463 EXIT;
2464 END IF;
2465 EXIT WHEN "loop_member_id_v" NOTNULL;
2466 "output_row"."index" := "output_row"."index" + 1;
2467 END LOOP;
2468 "row_count" := array_upper("output_rows", 1);
2469 "i" := 1;
2470 "loop_v" := FALSE;
2471 LOOP
2472 "output_row" := "output_rows"["i"];
2473 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2474 IF "loop_v" THEN
2475 IF "i" + 1 = "row_count" THEN
2476 "output_row"."loop" := 'last';
2477 ELSIF "i" = "row_count" THEN
2478 "output_row"."loop" := 'repetition';
2479 ELSE
2480 "output_row"."loop" := 'intermediate';
2481 END IF;
2482 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2483 "output_row"."loop" := 'first';
2484 "loop_v" := TRUE;
2485 END IF;
2486 IF "scope_v" = 'unit' THEN
2487 "output_row"."participation" := NULL;
2488 END IF;
2489 RETURN NEXT "output_row";
2490 "i" := "i" + 1;
2491 END LOOP;
2492 RETURN;
2493 END;
2494 $$;
2496 COMMENT ON FUNCTION "delegation_chain"
2497 ( "member"."id"%TYPE,
2498 "unit"."id"%TYPE,
2499 "area"."id"%TYPE,
2500 "issue"."id"%TYPE,
2501 "member"."id"%TYPE )
2502 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
2505 CREATE FUNCTION "delegation_chain"
2506 ( "member_id_p" "member"."id"%TYPE,
2507 "unit_id_p" "unit"."id"%TYPE,
2508 "area_id_p" "area"."id"%TYPE,
2509 "issue_id_p" "issue"."id"%TYPE )
2510 RETURNS SETOF "delegation_chain_row"
2511 LANGUAGE 'plpgsql' STABLE AS $$
2512 DECLARE
2513 "result_row" "delegation_chain_row";
2514 BEGIN
2515 FOR "result_row" IN
2516 SELECT * FROM "delegation_chain"(
2517 "member_id_p", "unit_id_p", "area_id_p", "issue_id_p", NULL
2518 )
2519 LOOP
2520 RETURN NEXT "result_row";
2521 END LOOP;
2522 RETURN;
2523 END;
2524 $$;
2526 COMMENT ON FUNCTION "delegation_chain"
2527 ( "member"."id"%TYPE,
2528 "unit"."id"%TYPE,
2529 "area"."id"%TYPE,
2530 "issue"."id"%TYPE )
2531 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
2535 ------------------------------
2536 -- Comparison by vote count --
2537 ------------------------------
2539 CREATE FUNCTION "vote_ratio"
2540 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
2541 "negative_votes_p" "initiative"."negative_votes"%TYPE )
2542 RETURNS FLOAT8
2543 LANGUAGE 'plpgsql' STABLE AS $$
2544 BEGIN
2545 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
2546 RETURN
2547 "positive_votes_p"::FLOAT8 /
2548 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
2549 ELSIF "positive_votes_p" > 0 THEN
2550 RETURN "positive_votes_p";
2551 ELSIF "negative_votes_p" > 0 THEN
2552 RETURN 1 - "negative_votes_p";
2553 ELSE
2554 RETURN 0.5;
2555 END IF;
2556 END;
2557 $$;
2559 COMMENT ON FUNCTION "vote_ratio"
2560 ( "initiative"."positive_votes"%TYPE,
2561 "initiative"."negative_votes"%TYPE )
2562 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.';
2566 ------------------------------------------------
2567 -- Locking for snapshots and voting procedure --
2568 ------------------------------------------------
2571 CREATE FUNCTION "share_row_lock_issue_trigger"()
2572 RETURNS TRIGGER
2573 LANGUAGE 'plpgsql' VOLATILE AS $$
2574 BEGIN
2575 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2576 PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;
2577 END IF;
2578 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2579 PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;
2580 RETURN NEW;
2581 ELSE
2582 RETURN OLD;
2583 END IF;
2584 END;
2585 $$;
2587 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';
2590 CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()
2591 RETURNS TRIGGER
2592 LANGUAGE 'plpgsql' VOLATILE AS $$
2593 BEGIN
2594 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2595 PERFORM NULL FROM "issue"
2596 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2597 WHERE "initiative"."id" = OLD."initiative_id"
2598 FOR SHARE OF "issue";
2599 END IF;
2600 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2601 PERFORM NULL FROM "issue"
2602 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2603 WHERE "initiative"."id" = NEW."initiative_id"
2604 FOR SHARE OF "issue";
2605 RETURN NEW;
2606 ELSE
2607 RETURN OLD;
2608 END IF;
2609 END;
2610 $$;
2612 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';
2615 CREATE TRIGGER "share_row_lock_issue"
2616 BEFORE INSERT OR UPDATE OR DELETE ON "initiative"
2617 FOR EACH ROW EXECUTE PROCEDURE
2618 "share_row_lock_issue_trigger"();
2620 CREATE TRIGGER "share_row_lock_issue"
2621 BEFORE INSERT OR UPDATE OR DELETE ON "interest"
2622 FOR EACH ROW EXECUTE PROCEDURE
2623 "share_row_lock_issue_trigger"();
2625 CREATE TRIGGER "share_row_lock_issue"
2626 BEFORE INSERT OR UPDATE OR DELETE ON "supporter"
2627 FOR EACH ROW EXECUTE PROCEDURE
2628 "share_row_lock_issue_trigger"();
2630 CREATE TRIGGER "share_row_lock_issue_via_initiative"
2631 BEFORE INSERT OR UPDATE OR DELETE ON "opinion"
2632 FOR EACH ROW EXECUTE PROCEDURE
2633 "share_row_lock_issue_via_initiative_trigger"();
2635 CREATE TRIGGER "share_row_lock_issue"
2636 BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter"
2637 FOR EACH ROW EXECUTE PROCEDURE
2638 "share_row_lock_issue_trigger"();
2640 CREATE TRIGGER "share_row_lock_issue"
2641 BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter"
2642 FOR EACH ROW EXECUTE PROCEDURE
2643 "share_row_lock_issue_trigger"();
2645 CREATE TRIGGER "share_row_lock_issue"
2646 BEFORE INSERT OR UPDATE OR DELETE ON "vote"
2647 FOR EACH ROW EXECUTE PROCEDURE
2648 "share_row_lock_issue_trigger"();
2650 COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function';
2651 COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function';
2652 COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function';
2653 COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function';
2654 COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function';
2655 COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function';
2656 COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function';
2659 CREATE FUNCTION "lock_issue"
2660 ( "issue_id_p" "issue"."id"%TYPE )
2661 RETURNS VOID
2662 LANGUAGE 'plpgsql' VOLATILE AS $$
2663 BEGIN
2664 LOCK TABLE "member" IN SHARE MODE;
2665 LOCK TABLE "privilege" IN SHARE MODE;
2666 LOCK TABLE "membership" IN SHARE MODE;
2667 LOCK TABLE "policy" IN SHARE MODE;
2668 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2669 -- NOTE: The row-level exclusive lock in combination with the
2670 -- share_row_lock_issue(_via_initiative)_trigger functions (which
2671 -- acquire a row-level share lock on the issue) ensure that no data
2672 -- is changed, which could affect calculation of snapshots or
2673 -- counting of votes. Table "delegation" must be table-level-locked,
2674 -- as it also contains issue- and global-scope delegations.
2675 LOCK TABLE "delegation" IN SHARE MODE;
2676 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
2677 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
2678 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
2679 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
2680 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
2681 RETURN;
2682 END;
2683 $$;
2685 COMMENT ON FUNCTION "lock_issue"
2686 ( "issue"."id"%TYPE )
2687 IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';
2691 ------------------------------------------------------------------------
2692 -- Regular tasks, except calculcation of snapshots and voting results --
2693 ------------------------------------------------------------------------
2695 CREATE FUNCTION "check_last_login"()
2696 RETURNS VOID
2697 LANGUAGE 'plpgsql' VOLATILE AS $$
2698 DECLARE
2699 "system_setting_row" "system_setting"%ROWTYPE;
2700 BEGIN
2701 SELECT * INTO "system_setting_row" FROM "system_setting";
2702 LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
2703 UPDATE "member" SET "last_login_public" = "last_login"::date
2704 FROM (
2705 SELECT DISTINCT "member"."id"
2706 FROM "member" LEFT JOIN "member_history"
2707 ON "member"."id" = "member_history"."member_id"
2708 WHERE "member"."last_login"::date < 'today' OR (
2709 "member_history"."until"::date >= 'today' AND
2710 "member_history"."active" = FALSE AND "member"."active" = TRUE
2711 )
2712 ) AS "subquery"
2713 WHERE "member"."id" = "subquery"."id";
2714 IF "system_setting_row"."member_ttl" NOTNULL THEN
2715 UPDATE "member" SET "active" = FALSE
2716 WHERE "active" = TRUE
2717 AND "last_login"::date < 'today'
2718 AND "last_login_public" <
2719 (now() - "system_setting_row"."member_ttl")::date;
2720 END IF;
2721 RETURN;
2722 END;
2723 $$;
2725 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).';
2728 CREATE FUNCTION "calculate_member_counts"()
2729 RETURNS VOID
2730 LANGUAGE 'plpgsql' VOLATILE AS $$
2731 BEGIN
2732 LOCK TABLE "member" IN SHARE MODE;
2733 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
2734 LOCK TABLE "unit" IN EXCLUSIVE MODE;
2735 LOCK TABLE "area" IN EXCLUSIVE MODE;
2736 LOCK TABLE "privilege" IN SHARE MODE;
2737 LOCK TABLE "membership" IN SHARE MODE;
2738 DELETE FROM "member_count";
2739 INSERT INTO "member_count" ("total_count")
2740 SELECT "total_count" FROM "member_count_view";
2741 UPDATE "unit" SET "member_count" = "view"."member_count"
2742 FROM "unit_member_count" AS "view"
2743 WHERE "view"."unit_id" = "unit"."id";
2744 UPDATE "area" SET
2745 "direct_member_count" = "view"."direct_member_count",
2746 "member_weight" = "view"."member_weight",
2747 "autoreject_weight" = "view"."autoreject_weight"
2748 FROM "area_member_count" AS "view"
2749 WHERE "view"."area_id" = "area"."id";
2750 RETURN;
2751 END;
2752 $$;
2754 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"';
2758 ------------------------------
2759 -- Calculation of snapshots --
2760 ------------------------------
2762 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
2763 ( "issue_id_p" "issue"."id"%TYPE,
2764 "member_id_p" "member"."id"%TYPE,
2765 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2766 RETURNS "direct_population_snapshot"."weight"%TYPE
2767 LANGUAGE 'plpgsql' VOLATILE AS $$
2768 DECLARE
2769 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2770 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
2771 "weight_v" INT4;
2772 "sub_weight_v" INT4;
2773 BEGIN
2774 "weight_v" := 0;
2775 FOR "issue_delegation_row" IN
2776 SELECT * FROM "issue_delegation"
2777 WHERE "trustee_id" = "member_id_p"
2778 AND "issue_id" = "issue_id_p"
2779 LOOP
2780 IF NOT EXISTS (
2781 SELECT NULL FROM "direct_population_snapshot"
2782 WHERE "issue_id" = "issue_id_p"
2783 AND "event" = 'periodic'
2784 AND "member_id" = "issue_delegation_row"."truster_id"
2785 ) AND NOT EXISTS (
2786 SELECT NULL FROM "delegating_population_snapshot"
2787 WHERE "issue_id" = "issue_id_p"
2788 AND "event" = 'periodic'
2789 AND "member_id" = "issue_delegation_row"."truster_id"
2790 ) THEN
2791 "delegate_member_ids_v" :=
2792 "member_id_p" || "delegate_member_ids_p";
2793 INSERT INTO "delegating_population_snapshot" (
2794 "issue_id",
2795 "event",
2796 "member_id",
2797 "scope",
2798 "delegate_member_ids"
2799 ) VALUES (
2800 "issue_id_p",
2801 'periodic',
2802 "issue_delegation_row"."truster_id",
2803 "issue_delegation_row"."scope",
2804 "delegate_member_ids_v"
2805 );
2806 "sub_weight_v" := 1 +
2807 "weight_of_added_delegations_for_population_snapshot"(
2808 "issue_id_p",
2809 "issue_delegation_row"."truster_id",
2810 "delegate_member_ids_v"
2811 );
2812 UPDATE "delegating_population_snapshot"
2813 SET "weight" = "sub_weight_v"
2814 WHERE "issue_id" = "issue_id_p"
2815 AND "event" = 'periodic'
2816 AND "member_id" = "issue_delegation_row"."truster_id";
2817 "weight_v" := "weight_v" + "sub_weight_v";
2818 END IF;
2819 END LOOP;
2820 RETURN "weight_v";
2821 END;
2822 $$;
2824 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
2825 ( "issue"."id"%TYPE,
2826 "member"."id"%TYPE,
2827 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2828 IS 'Helper function for "create_population_snapshot" function';
2831 CREATE FUNCTION "create_population_snapshot"
2832 ( "issue_id_p" "issue"."id"%TYPE )
2833 RETURNS VOID
2834 LANGUAGE 'plpgsql' VOLATILE AS $$
2835 DECLARE
2836 "member_id_v" "member"."id"%TYPE;
2837 BEGIN
2838 DELETE FROM "direct_population_snapshot"
2839 WHERE "issue_id" = "issue_id_p"
2840 AND "event" = 'periodic';
2841 DELETE FROM "delegating_population_snapshot"
2842 WHERE "issue_id" = "issue_id_p"
2843 AND "event" = 'periodic';
2844 INSERT INTO "direct_population_snapshot"
2845 ("issue_id", "event", "member_id")
2846 SELECT
2847 "issue_id_p" AS "issue_id",
2848 'periodic'::"snapshot_event" AS "event",
2849 "member"."id" AS "member_id"
2850 FROM "issue"
2851 JOIN "area" ON "issue"."area_id" = "area"."id"
2852 JOIN "membership" ON "area"."id" = "membership"."area_id"
2853 JOIN "member" ON "membership"."member_id" = "member"."id"
2854 JOIN "privilege"
2855 ON "privilege"."unit_id" = "area"."unit_id"
2856 AND "privilege"."member_id" = "member"."id"
2857 WHERE "issue"."id" = "issue_id_p"
2858 AND "member"."active" AND "privilege"."voting_right"
2859 UNION
2860 SELECT
2861 "issue_id_p" AS "issue_id",
2862 'periodic'::"snapshot_event" AS "event",
2863 "member"."id" AS "member_id"
2864 FROM "issue"
2865 JOIN "area" ON "issue"."area_id" = "area"."id"
2866 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2867 JOIN "member" ON "interest"."member_id" = "member"."id"
2868 JOIN "privilege"
2869 ON "privilege"."unit_id" = "area"."unit_id"
2870 AND "privilege"."member_id" = "member"."id"
2871 WHERE "issue"."id" = "issue_id_p"
2872 AND "member"."active" AND "privilege"."voting_right"
2873 UNION
2874 SELECT
2875 "issue_id_p" AS "issue_id",
2876 'periodic'::"snapshot_event" AS "event",
2877 "member"."id" AS "member_id"
2878 FROM "issue"
2879 JOIN "area" ON "issue"."area_id" = "area"."id"
2880 JOIN "issue_autoreject" ON "issue"."id" = "issue_autoreject"."issue_id"
2881 JOIN "member" ON "issue_autoreject"."member_id" = "member"."id"
2882 JOIN "privilege"
2883 ON "privilege"."unit_id" = "area"."unit_id"
2884 AND "privilege"."member_id" = "member"."id"
2885 WHERE "issue"."id" = "issue_id_p"
2886 AND "member"."active" AND "privilege"."voting_right";
2887 FOR "member_id_v" IN
2888 SELECT "member_id" FROM "direct_population_snapshot"
2889 WHERE "issue_id" = "issue_id_p"
2890 AND "event" = 'periodic'
2891 LOOP
2892 UPDATE "direct_population_snapshot" SET
2893 "weight" = 1 +
2894 "weight_of_added_delegations_for_population_snapshot"(
2895 "issue_id_p",
2896 "member_id_v",
2897 '{}'
2898 )
2899 WHERE "issue_id" = "issue_id_p"
2900 AND "event" = 'periodic'
2901 AND "member_id" = "member_id_v";
2902 END LOOP;
2903 RETURN;
2904 END;
2905 $$;
2907 COMMENT ON FUNCTION "create_population_snapshot"
2908 ( "issue"."id"%TYPE )
2909 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.';
2912 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2913 ( "issue_id_p" "issue"."id"%TYPE,
2914 "member_id_p" "member"."id"%TYPE,
2915 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2916 RETURNS "direct_interest_snapshot"."weight"%TYPE
2917 LANGUAGE 'plpgsql' VOLATILE AS $$
2918 DECLARE
2919 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2920 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2921 "weight_v" INT4;
2922 "sub_weight_v" INT4;
2923 BEGIN
2924 "weight_v" := 0;
2925 FOR "issue_delegation_row" IN
2926 SELECT * FROM "issue_delegation"
2927 WHERE "trustee_id" = "member_id_p"
2928 AND "issue_id" = "issue_id_p"
2929 LOOP
2930 IF NOT EXISTS (
2931 SELECT NULL FROM "direct_interest_snapshot"
2932 WHERE "issue_id" = "issue_id_p"
2933 AND "event" = 'periodic'
2934 AND "member_id" = "issue_delegation_row"."truster_id"
2935 ) AND NOT EXISTS (
2936 SELECT NULL FROM "delegating_interest_snapshot"
2937 WHERE "issue_id" = "issue_id_p"
2938 AND "event" = 'periodic'
2939 AND "member_id" = "issue_delegation_row"."truster_id"
2940 ) THEN
2941 "delegate_member_ids_v" :=
2942 "member_id_p" || "delegate_member_ids_p";
2943 INSERT INTO "delegating_interest_snapshot" (
2944 "issue_id",
2945 "event",
2946 "member_id",
2947 "scope",
2948 "delegate_member_ids"
2949 ) VALUES (
2950 "issue_id_p",
2951 'periodic',
2952 "issue_delegation_row"."truster_id",
2953 "issue_delegation_row"."scope",
2954 "delegate_member_ids_v"
2955 );
2956 "sub_weight_v" := 1 +
2957 "weight_of_added_delegations_for_interest_snapshot"(
2958 "issue_id_p",
2959 "issue_delegation_row"."truster_id",
2960 "delegate_member_ids_v"
2961 );
2962 UPDATE "delegating_interest_snapshot"
2963 SET "weight" = "sub_weight_v"
2964 WHERE "issue_id" = "issue_id_p"
2965 AND "event" = 'periodic'
2966 AND "member_id" = "issue_delegation_row"."truster_id";
2967 "weight_v" := "weight_v" + "sub_weight_v";
2968 END IF;
2969 END LOOP;
2970 RETURN "weight_v";
2971 END;
2972 $$;
2974 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2975 ( "issue"."id"%TYPE,
2976 "member"."id"%TYPE,
2977 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2978 IS 'Helper function for "create_interest_snapshot" function';
2981 CREATE FUNCTION "create_interest_snapshot"
2982 ( "issue_id_p" "issue"."id"%TYPE )
2983 RETURNS VOID
2984 LANGUAGE 'plpgsql' VOLATILE AS $$
2985 DECLARE
2986 "member_id_v" "member"."id"%TYPE;
2987 BEGIN
2988 DELETE FROM "direct_interest_snapshot"
2989 WHERE "issue_id" = "issue_id_p"
2990 AND "event" = 'periodic';
2991 DELETE FROM "delegating_interest_snapshot"
2992 WHERE "issue_id" = "issue_id_p"
2993 AND "event" = 'periodic';
2994 DELETE FROM "direct_supporter_snapshot"
2995 WHERE "issue_id" = "issue_id_p"
2996 AND "event" = 'periodic';
2997 INSERT INTO "direct_interest_snapshot"
2998 ("issue_id", "event", "member_id")
2999 SELECT
3000 "issue_id_p" AS "issue_id",
3001 'periodic' AS "event",
3002 "member"."id" AS "member_id"
3003 FROM "issue"
3004 JOIN "area" ON "issue"."area_id" = "area"."id"
3005 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3006 JOIN "member" ON "interest"."member_id" = "member"."id"
3007 JOIN "privilege"
3008 ON "privilege"."unit_id" = "area"."unit_id"
3009 AND "privilege"."member_id" = "member"."id"
3010 WHERE "issue"."id" = "issue_id_p"
3011 AND "member"."active" AND "privilege"."voting_right";
3012 FOR "member_id_v" IN
3013 SELECT "member_id" FROM "direct_interest_snapshot"
3014 WHERE "issue_id" = "issue_id_p"
3015 AND "event" = 'periodic'
3016 LOOP
3017 UPDATE "direct_interest_snapshot" SET
3018 "weight" = 1 +
3019 "weight_of_added_delegations_for_interest_snapshot"(
3020 "issue_id_p",
3021 "member_id_v",
3022 '{}'
3023 )
3024 WHERE "issue_id" = "issue_id_p"
3025 AND "event" = 'periodic'
3026 AND "member_id" = "member_id_v";
3027 END LOOP;
3028 INSERT INTO "direct_supporter_snapshot"
3029 ( "issue_id", "initiative_id", "event", "member_id",
3030 "informed", "satisfied" )
3031 SELECT
3032 "issue_id_p" AS "issue_id",
3033 "initiative"."id" AS "initiative_id",
3034 'periodic' AS "event",
3035 "supporter"."member_id" AS "member_id",
3036 "supporter"."draft_id" = "current_draft"."id" AS "informed",
3037 NOT EXISTS (
3038 SELECT NULL FROM "critical_opinion"
3039 WHERE "initiative_id" = "initiative"."id"
3040 AND "member_id" = "supporter"."member_id"
3041 ) AS "satisfied"
3042 FROM "initiative"
3043 JOIN "supporter"
3044 ON "supporter"."initiative_id" = "initiative"."id"
3045 JOIN "current_draft"
3046 ON "initiative"."id" = "current_draft"."initiative_id"
3047 JOIN "direct_interest_snapshot"
3048 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
3049 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
3050 AND "event" = 'periodic'
3051 WHERE "initiative"."issue_id" = "issue_id_p";
3052 RETURN;
3053 END;
3054 $$;
3056 COMMENT ON FUNCTION "create_interest_snapshot"
3057 ( "issue"."id"%TYPE )
3058 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.';
3061 CREATE FUNCTION "create_snapshot"
3062 ( "issue_id_p" "issue"."id"%TYPE )
3063 RETURNS VOID
3064 LANGUAGE 'plpgsql' VOLATILE AS $$
3065 DECLARE
3066 "initiative_id_v" "initiative"."id"%TYPE;
3067 "suggestion_id_v" "suggestion"."id"%TYPE;
3068 BEGIN
3069 PERFORM "lock_issue"("issue_id_p");
3070 PERFORM "create_population_snapshot"("issue_id_p");
3071 PERFORM "create_interest_snapshot"("issue_id_p");
3072 UPDATE "issue" SET
3073 "snapshot" = now(),
3074 "latest_snapshot_event" = 'periodic',
3075 "population" = (
3076 SELECT coalesce(sum("weight"), 0)
3077 FROM "direct_population_snapshot"
3078 WHERE "issue_id" = "issue_id_p"
3079 AND "event" = 'periodic'
3080 )
3081 WHERE "id" = "issue_id_p";
3082 FOR "initiative_id_v" IN
3083 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
3084 LOOP
3085 UPDATE "initiative" SET
3086 "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 ),
3096 "informed_supporter_count" = (
3097 SELECT coalesce(sum("di"."weight"), 0)
3098 FROM "direct_interest_snapshot" AS "di"
3099 JOIN "direct_supporter_snapshot" AS "ds"
3100 ON "di"."member_id" = "ds"."member_id"
3101 WHERE "di"."issue_id" = "issue_id_p"
3102 AND "di"."event" = 'periodic'
3103 AND "ds"."initiative_id" = "initiative_id_v"
3104 AND "ds"."event" = 'periodic'
3105 AND "ds"."informed"
3106 ),
3107 "satisfied_supporter_count" = (
3108 SELECT coalesce(sum("di"."weight"), 0)
3109 FROM "direct_interest_snapshot" AS "di"
3110 JOIN "direct_supporter_snapshot" AS "ds"
3111 ON "di"."member_id" = "ds"."member_id"
3112 WHERE "di"."issue_id" = "issue_id_p"
3113 AND "di"."event" = 'periodic'
3114 AND "ds"."initiative_id" = "initiative_id_v"
3115 AND "ds"."event" = 'periodic'
3116 AND "ds"."satisfied"
3117 ),
3118 "satisfied_informed_supporter_count" = (
3119 SELECT coalesce(sum("di"."weight"), 0)
3120 FROM "direct_interest_snapshot" AS "di"
3121 JOIN "direct_supporter_snapshot" AS "ds"
3122 ON "di"."member_id" = "ds"."member_id"
3123 WHERE "di"."issue_id" = "issue_id_p"
3124 AND "di"."event" = 'periodic'
3125 AND "ds"."initiative_id" = "initiative_id_v"
3126 AND "ds"."event" = 'periodic'
3127 AND "ds"."informed"
3128 AND "ds"."satisfied"
3129 )
3130 WHERE "id" = "initiative_id_v";
3131 FOR "suggestion_id_v" IN
3132 SELECT "id" FROM "suggestion"
3133 WHERE "initiative_id" = "initiative_id_v"
3134 LOOP
3135 UPDATE "suggestion" SET
3136 "minus2_unfulfilled_count" = (
3137 SELECT coalesce(sum("snapshot"."weight"), 0)
3138 FROM "issue" CROSS JOIN "opinion"
3139 JOIN "direct_interest_snapshot" AS "snapshot"
3140 ON "snapshot"."issue_id" = "issue"."id"
3141 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3142 AND "snapshot"."member_id" = "opinion"."member_id"
3143 WHERE "issue"."id" = "issue_id_p"
3144 AND "opinion"."suggestion_id" = "suggestion_id_v"
3145 AND "opinion"."degree" = -2
3146 AND "opinion"."fulfilled" = FALSE
3147 ),
3148 "minus2_fulfilled_count" = (
3149 SELECT coalesce(sum("snapshot"."weight"), 0)
3150 FROM "issue" CROSS JOIN "opinion"
3151 JOIN "direct_interest_snapshot" AS "snapshot"
3152 ON "snapshot"."issue_id" = "issue"."id"
3153 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3154 AND "snapshot"."member_id" = "opinion"."member_id"
3155 WHERE "issue"."id" = "issue_id_p"
3156 AND "opinion"."suggestion_id" = "suggestion_id_v"
3157 AND "opinion"."degree" = -2
3158 AND "opinion"."fulfilled" = TRUE
3159 ),
3160 "minus1_unfulfilled_count" = (
3161 SELECT coalesce(sum("snapshot"."weight"), 0)
3162 FROM "issue" CROSS JOIN "opinion"
3163 JOIN "direct_interest_snapshot" AS "snapshot"
3164 ON "snapshot"."issue_id" = "issue"."id"
3165 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3166 AND "snapshot"."member_id" = "opinion"."member_id"
3167 WHERE "issue"."id" = "issue_id_p"
3168 AND "opinion"."suggestion_id" = "suggestion_id_v"
3169 AND "opinion"."degree" = -1
3170 AND "opinion"."fulfilled" = FALSE
3171 ),
3172 "minus1_fulfilled_count" = (
3173 SELECT coalesce(sum("snapshot"."weight"), 0)
3174 FROM "issue" CROSS JOIN "opinion"
3175 JOIN "direct_interest_snapshot" AS "snapshot"
3176 ON "snapshot"."issue_id" = "issue"."id"
3177 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3178 AND "snapshot"."member_id" = "opinion"."member_id"
3179 WHERE "issue"."id" = "issue_id_p"
3180 AND "opinion"."suggestion_id" = "suggestion_id_v"
3181 AND "opinion"."degree" = -1
3182 AND "opinion"."fulfilled" = TRUE
3183 ),
3184 "plus1_unfulfilled_count" = (
3185 SELECT coalesce(sum("snapshot"."weight"), 0)
3186 FROM "issue" CROSS JOIN "opinion"
3187 JOIN "direct_interest_snapshot" AS "snapshot"
3188 ON "snapshot"."issue_id" = "issue"."id"
3189 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3190 AND "snapshot"."member_id" = "opinion"."member_id"
3191 WHERE "issue"."id" = "issue_id_p"
3192 AND "opinion"."suggestion_id" = "suggestion_id_v"
3193 AND "opinion"."degree" = 1
3194 AND "opinion"."fulfilled" = FALSE
3195 ),
3196 "plus1_fulfilled_count" = (
3197 SELECT coalesce(sum("snapshot"."weight"), 0)
3198 FROM "issue" CROSS JOIN "opinion"
3199 JOIN "direct_interest_snapshot" AS "snapshot"
3200 ON "snapshot"."issue_id" = "issue"."id"
3201 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3202 AND "snapshot"."member_id" = "opinion"."member_id"
3203 WHERE "issue"."id" = "issue_id_p"
3204 AND "opinion"."suggestion_id" = "suggestion_id_v"
3205 AND "opinion"."degree" = 1
3206 AND "opinion"."fulfilled" = TRUE
3207 ),
3208 "plus2_unfulfilled_count" = (
3209 SELECT coalesce(sum("snapshot"."weight"), 0)
3210 FROM "issue" CROSS JOIN "opinion"
3211 JOIN "direct_interest_snapshot" AS "snapshot"
3212 ON "snapshot"."issue_id" = "issue"."id"
3213 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3214 AND "snapshot"."member_id" = "opinion"."member_id"
3215 WHERE "issue"."id" = "issue_id_p"
3216 AND "opinion"."suggestion_id" = "suggestion_id_v"
3217 AND "opinion"."degree" = 2
3218 AND "opinion"."fulfilled" = FALSE
3219 ),
3220 "plus2_fulfilled_count" = (
3221 SELECT coalesce(sum("snapshot"."weight"), 0)
3222 FROM "issue" CROSS JOIN "opinion"
3223 JOIN "direct_interest_snapshot" AS "snapshot"
3224 ON "snapshot"."issue_id" = "issue"."id"
3225 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3226 AND "snapshot"."member_id" = "opinion"."member_id"
3227 WHERE "issue"."id" = "issue_id_p"
3228 AND "opinion"."suggestion_id" = "suggestion_id_v"
3229 AND "opinion"."degree" = 2
3230 AND "opinion"."fulfilled" = TRUE
3231 )
3232 WHERE "suggestion"."id" = "suggestion_id_v";
3233 END LOOP;
3234 END LOOP;
3235 RETURN;
3236 END;
3237 $$;
3239 COMMENT ON FUNCTION "create_snapshot"
3240 ( "issue"."id"%TYPE )
3241 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.';
3244 CREATE FUNCTION "set_snapshot_event"
3245 ( "issue_id_p" "issue"."id"%TYPE,
3246 "event_p" "snapshot_event" )
3247 RETURNS VOID
3248 LANGUAGE 'plpgsql' VOLATILE AS $$
3249 DECLARE
3250 "event_v" "issue"."latest_snapshot_event"%TYPE;
3251 BEGIN
3252 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
3253 WHERE "id" = "issue_id_p" FOR UPDATE;
3254 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
3255 WHERE "id" = "issue_id_p";
3256 UPDATE "direct_population_snapshot" SET "event" = "event_p"
3257 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3258 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
3259 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3260 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
3261 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3262 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
3263 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3264 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
3265 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3266 RETURN;
3267 END;
3268 $$;
3270 COMMENT ON FUNCTION "set_snapshot_event"
3271 ( "issue"."id"%TYPE,
3272 "snapshot_event" )
3273 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
3277 ---------------------
3278 -- Freezing issues --
3279 ---------------------
3281 CREATE FUNCTION "freeze_after_snapshot"
3282 ( "issue_id_p" "issue"."id"%TYPE )
3283 RETURNS VOID
3284 LANGUAGE 'plpgsql' VOLATILE AS $$
3285 DECLARE
3286 "issue_row" "issue"%ROWTYPE;
3287 "policy_row" "policy"%ROWTYPE;
3288 "initiative_row" "initiative"%ROWTYPE;
3289 BEGIN
3290 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3291 SELECT * INTO "policy_row"
3292 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3293 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
3294 FOR "initiative_row" IN
3295 SELECT * FROM "initiative"
3296 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3297 LOOP
3298 IF
3299 "initiative_row"."satisfied_supporter_count" > 0 AND
3300 "initiative_row"."satisfied_supporter_count" *
3301 "policy_row"."initiative_quorum_den" >=
3302 "issue_row"."population" * "policy_row"."initiative_quorum_num"
3303 THEN
3304 UPDATE "initiative" SET "admitted" = TRUE
3305 WHERE "id" = "initiative_row"."id";
3306 ELSE
3307 UPDATE "initiative" SET "admitted" = FALSE
3308 WHERE "id" = "initiative_row"."id";
3309 END IF;
3310 END LOOP;
3311 IF EXISTS (
3312 SELECT NULL FROM "initiative"
3313 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
3314 ) THEN
3315 UPDATE "issue" SET
3316 "state" = 'voting',
3317 "accepted" = coalesce("accepted", now()),
3318 "half_frozen" = coalesce("half_frozen", now()),
3319 "fully_frozen" = now()
3320 WHERE "id" = "issue_id_p";
3321 ELSE
3322 UPDATE "issue" SET
3323 "state" = 'canceled_no_initiative_admitted',
3324 "accepted" = coalesce("accepted", now()),
3325 "half_frozen" = coalesce("half_frozen", now()),
3326 "fully_frozen" = now(),
3327 "closed" = now(),
3328 "ranks_available" = TRUE
3329 WHERE "id" = "issue_id_p";
3330 -- NOTE: The following DELETE statements have effect only when
3331 -- issue state has been manipulated
3332 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
3333 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
3334 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3335 END IF;
3336 RETURN;
3337 END;
3338 $$;
3340 COMMENT ON FUNCTION "freeze_after_snapshot"
3341 ( "issue"."id"%TYPE )
3342 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
3345 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
3346 RETURNS VOID
3347 LANGUAGE 'plpgsql' VOLATILE AS $$
3348 DECLARE
3349 "issue_row" "issue"%ROWTYPE;
3350 BEGIN
3351 PERFORM "create_snapshot"("issue_id_p");
3352 PERFORM "freeze_after_snapshot"("issue_id_p");
3353 RETURN;
3354 END;
3355 $$;
3357 COMMENT ON FUNCTION "manual_freeze"
3358 ( "issue"."id"%TYPE )
3359 IS 'Freeze an issue manually (fully) and start voting';
3363 -----------------------
3364 -- Counting of votes --
3365 -----------------------
3368 CREATE FUNCTION "weight_of_added_vote_delegations"
3369 ( "issue_id_p" "issue"."id"%TYPE,
3370 "member_id_p" "member"."id"%TYPE,
3371 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
3372 RETURNS "direct_voter"."weight"%TYPE
3373 LANGUAGE 'plpgsql' VOLATILE AS $$
3374 DECLARE
3375 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3376 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
3377 "weight_v" INT4;
3378 "sub_weight_v" INT4;
3379 BEGIN
3380 "weight_v" := 0;
3381 FOR "issue_delegation_row" IN
3382 SELECT * FROM "issue_delegation"
3383 WHERE "trustee_id" = "member_id_p"
3384 AND "issue_id" = "issue_id_p"
3385 LOOP
3386 IF NOT EXISTS (
3387 SELECT NULL FROM "direct_voter"
3388 WHERE "member_id" = "issue_delegation_row"."truster_id"
3389 AND "issue_id" = "issue_id_p"
3390 ) AND NOT EXISTS (
3391 SELECT NULL FROM "delegating_voter"
3392 WHERE "member_id" = "issue_delegation_row"."truster_id"
3393 AND "issue_id" = "issue_id_p"
3394 ) THEN
3395 "delegate_member_ids_v" :=
3396 "member_id_p" || "delegate_member_ids_p";
3397 INSERT INTO "delegating_voter" (
3398 "issue_id",
3399 "member_id",
3400 "scope",
3401 "delegate_member_ids"
3402 ) VALUES (
3403 "issue_id_p",
3404 "issue_delegation_row"."truster_id",
3405 "issue_delegation_row"."scope",
3406 "delegate_member_ids_v"
3407 );
3408 "sub_weight_v" := 1 +
3409 "weight_of_added_vote_delegations"(
3410 "issue_id_p",
3411 "issue_delegation_row"."truster_id",
3412 "delegate_member_ids_v"
3413 );
3414 UPDATE "delegating_voter"
3415 SET "weight" = "sub_weight_v"
3416 WHERE "issue_id" = "issue_id_p"
3417 AND "member_id" = "issue_delegation_row"."truster_id";
3418 "weight_v" := "weight_v" + "sub_weight_v";
3419 END IF;
3420 END LOOP;
3421 RETURN "weight_v";
3422 END;
3423 $$;
3425 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
3426 ( "issue"."id"%TYPE,
3427 "member"."id"%TYPE,
3428 "delegating_voter"."delegate_member_ids"%TYPE )
3429 IS 'Helper function for "add_vote_delegations" function';
3432 CREATE FUNCTION "add_vote_delegations"
3433 ( "issue_id_p" "issue"."id"%TYPE )
3434 RETURNS VOID
3435 LANGUAGE 'plpgsql' VOLATILE AS $$
3436 DECLARE
3437 "member_id_v" "member"."id"%TYPE;
3438 BEGIN
3439 FOR "member_id_v" IN
3440 SELECT "member_id" FROM "direct_voter"
3441 WHERE "issue_id" = "issue_id_p"
3442 LOOP
3443 UPDATE "direct_voter" SET
3444 "weight" = "weight" + "weight_of_added_vote_delegations"(
3445 "issue_id_p",
3446 "member_id_v",
3447 '{}'
3448 )
3449 WHERE "member_id" = "member_id_v"
3450 AND "issue_id" = "issue_id_p";
3451 END LOOP;
3452 RETURN;
3453 END;
3454 $$;
3456 COMMENT ON FUNCTION "add_vote_delegations"
3457 ( "issue_id_p" "issue"."id"%TYPE )
3458 IS 'Helper function for "close_voting" function';
3461 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
3462 RETURNS VOID
3463 LANGUAGE 'plpgsql' VOLATILE AS $$
3464 DECLARE
3465 "area_id_v" "area"."id"%TYPE;
3466 "unit_id_v" "unit"."id"%TYPE;
3467 "member_id_v" "member"."id"%TYPE;
3468 BEGIN
3469 PERFORM "lock_issue"("issue_id_p");
3470 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
3471 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
3472 -- consider delegations and auto-reject:
3473 DELETE FROM "delegating_voter"
3474 WHERE "issue_id" = "issue_id_p";
3475 DELETE FROM "direct_voter"
3476 WHERE "issue_id" = "issue_id_p"
3477 AND "autoreject" = TRUE;
3478 DELETE FROM "direct_voter"
3479 USING (
3480 SELECT
3481 "direct_voter"."member_id"
3482 FROM "direct_voter"
3483 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
3484 LEFT JOIN "privilege"
3485 ON "privilege"."unit_id" = "unit_id_v"
3486 AND "privilege"."member_id" = "direct_voter"."member_id"
3487 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
3488 "member"."active" = FALSE OR
3489 "privilege"."voting_right" ISNULL OR
3490 "privilege"."voting_right" = FALSE
3491 )
3492 ) AS "subquery"
3493 WHERE "direct_voter"."issue_id" = "issue_id_p"
3494 AND "direct_voter"."member_id" = "subquery"."member_id";
3495 UPDATE "direct_voter" SET "weight" = 1
3496 WHERE "issue_id" = "issue_id_p";
3497 PERFORM "add_vote_delegations"("issue_id_p");
3498 FOR "member_id_v" IN
3499 SELECT "issue_autoreject"."member_id"
3500 FROM "issue_autoreject"
3501 JOIN "member"
3502 ON "issue_autoreject"."member_id" = "member"."id"
3503 JOIN "privilege"
3504 ON "privilege"."unit_id" = "unit_id_v"
3505 AND "privilege"."member_id" = "member"."id"
3506 LEFT JOIN "direct_voter"
3507 ON "issue_autoreject"."member_id" = "direct_voter"."member_id"
3508 AND "issue_autoreject"."issue_id" = "direct_voter"."issue_id"
3509 LEFT JOIN "delegating_voter"
3510 ON "issue_autoreject"."member_id" = "delegating_voter"."member_id"
3511 AND "issue_autoreject"."issue_id" = "delegating_voter"."issue_id"
3512 WHERE "issue_autoreject"."issue_id" = "issue_id_p"
3513 AND "issue_autoreject"."autoreject" = TRUE
3514 AND "member"."active"
3515 AND "privilege"."voting_right"
3516 AND "direct_voter"."member_id" ISNULL
3517 AND "delegating_voter"."member_id" ISNULL
3518 UNION
3519 SELECT "membership"."member_id"
3520 FROM "membership"
3521 JOIN "member"
3522 ON "membership"."member_id" = "member"."id"
3523 JOIN "privilege"
3524 ON "privilege"."unit_id" = "unit_id_v"
3525 AND "privilege"."member_id" = "member"."id"
3526 LEFT JOIN "issue_autoreject"
3527 ON "membership"."member_id" = "issue_autoreject"."member_id"
3528 AND "issue_autoreject"."issue_id" = "issue_id_p"
3529 LEFT JOIN "direct_voter"
3530 ON "membership"."member_id" = "direct_voter"."member_id"
3531 AND "direct_voter"."issue_id" = "issue_id_p"
3532 LEFT JOIN "delegating_voter"
3533 ON "membership"."member_id" = "delegating_voter"."member_id"
3534 AND "delegating_voter"."issue_id" = "issue_id_p"
3535 WHERE "membership"."area_id" = "area_id_v"
3536 AND "membership"."autoreject" = TRUE
3537 AND "member"."active"
3538 AND "privilege"."voting_right"
3539 AND "issue_autoreject"."autoreject" ISNULL
3540 AND "direct_voter"."member_id" ISNULL
3541 AND "delegating_voter"."member_id" ISNULL
3542 LOOP
3543 INSERT INTO "direct_voter"
3544 ("member_id", "issue_id", "weight", "autoreject") VALUES
3545 ("member_id_v", "issue_id_p", 1, TRUE);
3546 INSERT INTO "vote" (
3547 "member_id",
3548 "issue_id",
3549 "initiative_id",
3550 "grade"
3551 ) SELECT
3552 "member_id_v" AS "member_id",
3553 "issue_id_p" AS "issue_id",
3554 "id" AS "initiative_id",
3555 -1 AS "grade"
3556 FROM "initiative"
3557 WHERE "issue_id" = "issue_id_p" AND "admitted";
3558 END LOOP;
3559 PERFORM "add_vote_delegations"("issue_id_p");
3560 -- set voter count and mark issue as being calculated:
3561 UPDATE "issue" SET
3562 "state" = 'calculation',
3563 "closed" = now(),
3564 "voter_count" = (
3565 SELECT coalesce(sum("weight"), 0)
3566 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
3567 )
3568 WHERE "id" = "issue_id_p";
3569 -- materialize battle_view:
3570 -- NOTE: "closed" column of issue must be set at this point
3571 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3572 INSERT INTO "battle" (
3573 "issue_id",
3574 "winning_initiative_id", "losing_initiative_id",
3575 "count"
3576 ) SELECT
3577 "issue_id",
3578 "winning_initiative_id", "losing_initiative_id",
3579 "count"
3580 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
3581 -- copy "positive_votes" and "negative_votes" from "battle" table:
3582 UPDATE "initiative" SET
3583 "positive_votes" = "battle_win"."count",
3584 "negative_votes" = "battle_lose"."count"
3585 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
3586 WHERE
3587 "battle_win"."issue_id" = "issue_id_p" AND
3588 "battle_win"."winning_initiative_id" = "initiative"."id" AND
3589 "battle_win"."losing_initiative_id" ISNULL AND
3590 "battle_lose"."issue_id" = "issue_id_p" AND
3591 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
3592 "battle_lose"."winning_initiative_id" ISNULL;
3593 END;
3594 $$;
3596 COMMENT ON FUNCTION "close_voting"
3597 ( "issue"."id"%TYPE )
3598 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.';
3601 CREATE FUNCTION "defeat_strength"
3602 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
3603 RETURNS INT8
3604 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3605 BEGIN
3606 IF "positive_votes_p" > "negative_votes_p" THEN
3607 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
3608 ELSIF "positive_votes_p" = "negative_votes_p" THEN
3609 RETURN 0;
3610 ELSE
3611 RETURN -1;
3612 END IF;
3613 END;
3614 $$;
3616 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';
3619 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
3620 RETURNS VOID
3621 LANGUAGE 'plpgsql' VOLATILE AS $$
3622 DECLARE
3623 "issue_row" "issue"%ROWTYPE;
3624 "policy_row" "policy"%ROWTYPE;
3625 "dimension_v" INTEGER;
3626 "vote_matrix" INT4[][]; -- absolute votes
3627 "matrix" INT8[][]; -- defeat strength / best paths
3628 "i" INTEGER;
3629 "j" INTEGER;
3630 "k" INTEGER;
3631 "battle_row" "battle"%ROWTYPE;
3632 "rank_ary" INT4[];
3633 "rank_v" INT4;
3634 "done_v" INTEGER;
3635 "winners_ary" INTEGER[];
3636 "initiative_id_v" "initiative"."id"%TYPE;
3637 BEGIN
3638 SELECT * INTO "issue_row"
3639 FROM "issue" WHERE "id" = "issue_id_p"
3640 FOR UPDATE;
3641 SELECT * INTO "policy_row"
3642 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3643 SELECT count(1) INTO "dimension_v"
3644 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
3645 IF "dimension_v" > 1 THEN
3646 -- Create "vote_matrix" with absolute number of votes in pairwise
3647 -- comparison:
3648 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
3649 "i" := 1;
3650 "j" := 2;
3651 FOR "battle_row" IN
3652 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
3653 ORDER BY
3654 "winning_initiative_id" NULLS LAST,
3655 "losing_initiative_id" NULLS LAST
3656 LOOP
3657 "vote_matrix"["i"]["j"] := "battle_row"."count";
3658 IF "j" = "dimension_v" THEN
3659 "i" := "i" + 1;
3660 "j" := 1;
3661 ELSE
3662 "j" := "j" + 1;
3663 IF "j" = "i" THEN
3664 "j" := "j" + 1;
3665 END IF;
3666 END IF;
3667 END LOOP;
3668 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
3669 RAISE EXCEPTION 'Wrong battle count (should not happen)';
3670 END IF;
3671 -- Store defeat strengths in "matrix" using "defeat_strength"
3672 -- function:
3673 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
3674 "i" := 1;
3675 LOOP
3676 "j" := 1;
3677 LOOP
3678 IF "i" != "j" THEN
3679 "matrix"["i"]["j"] := "defeat_strength"(
3680 "vote_matrix"["i"]["j"],
3681 "vote_matrix"["j"]["i"]
3682 );
3683 END IF;
3684 EXIT WHEN "j" = "dimension_v";
3685 "j" := "j" + 1;
3686 END LOOP;
3687 EXIT WHEN "i" = "dimension_v";
3688 "i" := "i" + 1;
3689 END LOOP;
3690 -- Find best paths:
3691 "i" := 1;
3692 LOOP
3693 "j" := 1;
3694 LOOP
3695 IF "i" != "j" THEN
3696 "k" := 1;
3697 LOOP
3698 IF "i" != "k" AND "j" != "k" THEN
3699 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
3700 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
3701 "matrix"["j"]["k"] := "matrix"["j"]["i"];
3702 END IF;
3703 ELSE
3704 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
3705 "matrix"["j"]["k"] := "matrix"["i"]["k"];
3706 END IF;
3707 END IF;
3708 END IF;
3709 EXIT WHEN "k" = "dimension_v";
3710 "k" := "k" + 1;
3711 END LOOP;
3712 END IF;
3713 EXIT WHEN "j" = "dimension_v";
3714 "j" := "j" + 1;
3715 END LOOP;
3716 EXIT WHEN "i" = "dimension_v";
3717 "i" := "i" + 1;
3718 END LOOP;
3719 -- Determine order of winners:
3720 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
3721 "rank_v" := 1;
3722 "done_v" := 0;
3723 LOOP
3724 "winners_ary" := '{}';
3725 "i" := 1;
3726 LOOP
3727 IF "rank_ary"["i"] ISNULL THEN
3728 "j" := 1;
3729 LOOP
3730 IF
3731 "i" != "j" AND
3732 "rank_ary"["j"] ISNULL AND
3733 "matrix"["j"]["i"] > "matrix"["i"]["j"]
3734 THEN
3735 -- someone else is better
3736 EXIT;
3737 END IF;
3738 IF "j" = "dimension_v" THEN
3739 -- noone is better
3740 "winners_ary" := "winners_ary" || "i";
3741 EXIT;
3742 END IF;
3743 "j" := "j" + 1;
3744 END LOOP;
3745 END IF;
3746 EXIT WHEN "i" = "dimension_v";
3747 "i" := "i" + 1;
3748 END LOOP;
3749 "i" := 1;
3750 LOOP
3751 "rank_ary"["winners_ary"["i"]] := "rank_v";
3752 "done_v" := "done_v" + 1;
3753 EXIT WHEN "i" = array_upper("winners_ary", 1);
3754 "i" := "i" + 1;
3755 END LOOP;
3756 EXIT WHEN "done_v" = "dimension_v";
3757 "rank_v" := "rank_v" + 1;
3758 END LOOP;
3759 -- write preliminary results:
3760 "i" := 1;
3761 FOR "initiative_id_v" IN
3762 SELECT "id" FROM "initiative"
3763 WHERE "issue_id" = "issue_id_p" AND "admitted"
3764 ORDER BY "id"
3765 LOOP
3766 UPDATE "initiative" SET
3767 "attainable" =
3768 CASE WHEN "policy_row"."majority_strict" THEN
3769 "positive_votes" * "policy_row"."majority_den" >
3770 "policy_row"."majority_num" * ("positive_votes"+"negative_votes")
3771 ELSE
3772 "positive_votes" * "policy_row"."majority_den" >=
3773 "policy_row"."majority_num" * ("positive_votes"+"negative_votes")
3774 END
3775 AND "positive_votes" >= "policy_row"."majority_positive"
3776 AND "issue_row"."voter_count"-"negative_votes" >=
3777 "policy_row"."majority_non_negative",
3778 "favored_to_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
3779 "unfavored_to_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
3780 "schulze_rank" = "rank_ary"["i"],
3781 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
3782 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0
3783 WHERE "id" = "initiative_id_v";
3784 "i" := "i" + 1;
3785 END LOOP;
3786 IF "i" != "dimension_v" THEN
3787 RAISE EXCEPTION 'Wrong winner count (should not happen)';
3788 END IF;
3789 if "policy_row"."majority_indirect" THEN
3790 -- take indirect majorities into account:
3791 LOOP
3792 UPDATE "initiative" SET "attainable" = TRUE
3793 FROM (
3794 SELECT "new_initiative"."id" AS "initiative_id"
3795 FROM "initiative" "old_initiative"
3796 JOIN "initiative" "new_initiative"
3797 ON "new_initiative"."issue_id" = "issue_id_p"
3798 AND "new_initiative"."attainable" = FALSE
3799 JOIN "battle" "battle_win"
3800 ON "battle_win"."issue_id" = "issue_id_p"
3801 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
3802 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
3803 JOIN "battle" "battle_lose"
3804 ON "battle_lose"."issue_id" = "issue_id_p"
3805 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
3806 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
3807 WHERE "old_initiative"."issue_id" = "issue_id_p"
3808 AND "old_initiative"."attainable" = TRUE
3809 AND CASE WHEN "policy_row"."majority_strict" THEN
3810 "battle_win"."count" * "policy_row"."majority_den" >
3811 "policy_row"."majority_num" *
3812 ("battle_win"."count"+"battle_lose"."count")
3813 ELSE
3814 "battle_win"."count" * "policy_row"."majority_den" >=
3815 "policy_row"."majority_num" *
3816 ("battle_win"."count"+"battle_lose"."count")
3817 END
3818 AND "battle_win"."count" >= "policy_row"."majority_positive"
3819 AND "issue_row"."voter_count"-"battle_lose"."count" >=
3820 "policy_row"."majority_non_negative"
3821 ) AS "subquery"
3822 WHERE "id" = "subquery"."initiative_id";
3823 EXIT WHEN NOT FOUND;
3824 END LOOP;
3825 END IF;
3826 -- set "multistage_majority" for remaining matching initiatives:
3827 UPDATE "initiative" SET "multistage_majority" = TRUE
3828 FROM (
3829 SELECT "losing_initiative"."id" AS "initiative_id"
3830 FROM "initiative" "losing_initiative"
3831 JOIN "initiative" "winning_initiative"
3832 ON "winning_initiative"."issue_id" = "issue_id_p"
3833 AND "winning_initiative"."admitted"
3834 JOIN "battle" "battle_win"
3835 ON "battle_win"."issue_id" = "issue_id_p"
3836 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
3837 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
3838 JOIN "battle" "battle_lose"
3839 ON "battle_lose"."issue_id" = "issue_id_p"
3840 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
3841 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
3842 WHERE "losing_initiative"."issue_id" = "issue_id_p"
3843 AND "losing_initiative"."admitted"
3844 AND "winning_initiative"."schulze_rank" <
3845 "losing_initiative"."schulze_rank"
3846 AND "battle_win"."count" > "battle_lose"."count"
3847 AND (
3848 "battle_win"."count" > "winning_initiative"."positive_votes" OR
3849 "battle_lose"."count" < "losing_initiative"."negative_votes" )
3850 ) AS "subquery"
3851 WHERE "id" = "subquery"."initiative_id";
3852 -- mark eligible initiatives:
3853 "rank_v" := 1;
3854 UPDATE "initiative" SET "eligible" = TRUE
3855 FROM (
3856 SELECT "initiative"."id" AS "initiative_id"
3857 FROM "issue"
3858 JOIN "policy"
3859 ON "issue"."policy_id" = "policy"."id"
3860 JOIN "initiative"
3861 ON "issue"."id" = "initiative"."issue_id"
3862 WHERE "issue_id" = "issue_id_p"
3863 AND "initiative"."attainable"
3864 AND "initiative"."favored_to_status_quo"
3865 AND (
3866 "policy"."no_multistage_majority" = FALSE OR
3867 "initiative"."multistage_majority" = FALSE )
3868 AND (
3869 "policy"."no_reverse_beat_path" = FALSE OR
3870 "initiative"."reverse_beat_path" = FALSE )
3871 ) AS "subquery"
3872 WHERE "id" = "subquery"."initiative_id";
3873 -- mark final winner:
3874 UPDATE "initiative" SET "winner" = TRUE
3875 FROM (
3876 SELECT "id" AS "initiative_id"
3877 FROM "initiative"
3878 WHERE "issue_id" = "issue_id_p" AND "eligible"
3879 ORDER BY "schulze_rank", "id"
3880 LIMIT 1
3881 ) AS "subquery"
3882 WHERE "id" = "subquery"."initiative_id";
3883 END IF;
3884 -- mark issue as finished:
3885 UPDATE "issue" SET
3886 "state" =
3887 CASE WHEN EXISTS (
3888 SELECT NULL FROM "initiative"
3889 WHERE "issue_id" = "issue_id_p" AND "winner"
3890 ) THEN
3891 'finished_with_winner'::"issue_state"
3892 ELSE
3893 'finished_without_winner'::"issue_state"
3894 END,
3895 "ranks_available" = TRUE
3896 WHERE "id" = "issue_id_p";
3897 RETURN;
3898 END;
3899 $$;
3901 COMMENT ON FUNCTION "calculate_ranks"
3902 ( "issue"."id"%TYPE )
3903 IS 'Determine ranking (Votes have to be counted first)';
3907 -----------------------------
3908 -- Automatic state changes --
3909 -----------------------------
3912 CREATE FUNCTION "check_issue"
3913 ( "issue_id_p" "issue"."id"%TYPE )
3914 RETURNS VOID
3915 LANGUAGE 'plpgsql' VOLATILE AS $$
3916 DECLARE
3917 "issue_row" "issue"%ROWTYPE;
3918 "policy_row" "policy"%ROWTYPE;
3919 BEGIN
3920 PERFORM "lock_issue"("issue_id_p");
3921 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3922 -- only process open issues:
3923 IF "issue_row"."closed" ISNULL THEN
3924 SELECT * INTO "policy_row" FROM "policy"
3925 WHERE "id" = "issue_row"."policy_id";
3926 -- create a snapshot, unless issue is already fully frozen:
3927 IF "issue_row"."fully_frozen" ISNULL THEN
3928 PERFORM "create_snapshot"("issue_id_p");
3929 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3930 END IF;
3931 -- eventually close or accept issues, which have not been accepted:
3932 IF "issue_row"."accepted" ISNULL THEN
3933 IF EXISTS (
3934 SELECT NULL FROM "initiative"
3935 WHERE "issue_id" = "issue_id_p"
3936 AND "supporter_count" > 0
3937 AND "supporter_count" * "policy_row"."issue_quorum_den"
3938 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
3939 ) THEN
3940 -- accept issues, if supporter count is high enough
3941 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3942 -- NOTE: "issue_row" used later
3943 "issue_row"."state" := 'discussion';
3944 "issue_row"."accepted" := now();
3945 UPDATE "issue" SET
3946 "state" = "issue_row"."state",
3947 "accepted" = "issue_row"."accepted"
3948 WHERE "id" = "issue_row"."id";
3949 ELSIF
3950 now() >= "issue_row"."created" + "issue_row"."admission_time"
3951 THEN
3952 -- close issues, if admission time has expired
3953 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3954 UPDATE "issue" SET
3955 "state" = 'canceled_issue_not_accepted',
3956 "closed" = now()
3957 WHERE "id" = "issue_row"."id";
3958 END IF;
3959 END IF;
3960 -- eventually half freeze issues:
3961 IF
3962 -- NOTE: issue can't be closed at this point, if it has been accepted
3963 "issue_row"."accepted" NOTNULL AND
3964 "issue_row"."half_frozen" ISNULL
3965 THEN
3966 IF
3967 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
3968 THEN
3969 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
3970 -- NOTE: "issue_row" used later
3971 "issue_row"."state" := 'verification';
3972 "issue_row"."half_frozen" := now();
3973 UPDATE "issue" SET
3974 "state" = "issue_row"."state",
3975 "half_frozen" = "issue_row"."half_frozen"
3976 WHERE "id" = "issue_row"."id";
3977 END IF;
3978 END IF;
3979 -- close issues after some time, if all initiatives have been revoked:
3980 IF
3981 "issue_row"."closed" ISNULL AND
3982 NOT EXISTS (
3983 -- all initiatives are revoked
3984 SELECT NULL FROM "initiative"
3985 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3986 ) AND (
3987 -- and issue has not been accepted yet
3988 "issue_row"."accepted" ISNULL OR
3989 NOT EXISTS (
3990 -- or no initiatives have been revoked lately
3991 SELECT NULL FROM "initiative"
3992 WHERE "issue_id" = "issue_id_p"
3993 AND now() < "revoked" + "issue_row"."verification_time"
3994 ) OR (
3995 -- or verification time has elapsed
3996 "issue_row"."half_frozen" NOTNULL AND
3997 "issue_row"."fully_frozen" ISNULL AND
3998 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3999 )
4000 )
4001 THEN
4002 -- NOTE: "issue_row" used later
4003 IF "issue_row"."accepted" ISNULL THEN
4004 "issue_row"."state" := 'canceled_revoked_before_accepted';
4005 ELSIF "issue_row"."half_frozen" ISNULL THEN
4006 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
4007 ELSE
4008 "issue_row"."state" := 'canceled_after_revocation_during_verification';
4009 END IF;
4010 "issue_row"."closed" := now();
4011 UPDATE "issue" SET
4012 "state" = "issue_row"."state",
4013 "closed" = "issue_row"."closed"
4014 WHERE "id" = "issue_row"."id";
4015 END IF;
4016 -- fully freeze issue after verification time:
4017 IF
4018 "issue_row"."half_frozen" NOTNULL AND
4019 "issue_row"."fully_frozen" ISNULL AND
4020 "issue_row"."closed" ISNULL AND
4021 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
4022 THEN
4023 PERFORM "freeze_after_snapshot"("issue_id_p");
4024 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
4025 END IF;
4026 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4027 -- close issue by calling close_voting(...) after voting time:
4028 IF
4029 "issue_row"."closed" ISNULL AND
4030 "issue_row"."fully_frozen" NOTNULL AND
4031 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
4032 THEN
4033 PERFORM "close_voting"("issue_id_p");
4034 -- calculate ranks will not consume much time and can be done now
4035 PERFORM "calculate_ranks"("issue_id_p");
4036 END IF;
4037 END IF;
4038 RETURN;
4039 END;
4040 $$;
4042 COMMENT ON FUNCTION "check_issue"
4043 ( "issue"."id"%TYPE )
4044 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.';
4047 CREATE FUNCTION "check_everything"()
4048 RETURNS VOID
4049 LANGUAGE 'plpgsql' VOLATILE AS $$
4050 DECLARE
4051 "issue_id_v" "issue"."id"%TYPE;
4052 BEGIN
4053 DELETE FROM "expired_session";
4054 PERFORM "check_last_login"();
4055 PERFORM "calculate_member_counts"();
4056 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
4057 PERFORM "check_issue"("issue_id_v");
4058 END LOOP;
4059 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
4060 PERFORM "calculate_ranks"("issue_id_v");
4061 END LOOP;
4062 RETURN;
4063 END;
4064 $$;
4066 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.';
4070 ----------------------
4071 -- Deletion of data --
4072 ----------------------
4075 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
4076 RETURNS VOID
4077 LANGUAGE 'plpgsql' VOLATILE AS $$
4078 DECLARE
4079 "issue_row" "issue"%ROWTYPE;
4080 BEGIN
4081 SELECT * INTO "issue_row"
4082 FROM "issue" WHERE "id" = "issue_id_p"
4083 FOR UPDATE;
4084 IF "issue_row"."cleaned" ISNULL THEN
4085 UPDATE "issue" SET
4086 "state" = 'voting',
4087 "closed" = NULL,
4088 "ranks_available" = FALSE
4089 WHERE "id" = "issue_id_p";
4090 DELETE FROM "delegating_voter"
4091 WHERE "issue_id" = "issue_id_p";
4092 DELETE FROM "direct_voter"
4093 WHERE "issue_id" = "issue_id_p";
4094 DELETE FROM "delegating_interest_snapshot"
4095 WHERE "issue_id" = "issue_id_p";
4096 DELETE FROM "direct_interest_snapshot"
4097 WHERE "issue_id" = "issue_id_p";
4098 DELETE FROM "delegating_population_snapshot"
4099 WHERE "issue_id" = "issue_id_p";
4100 DELETE FROM "direct_population_snapshot"
4101 WHERE "issue_id" = "issue_id_p";
4102 DELETE FROM "non_voter"
4103 WHERE "issue_id" = "issue_id_p";
4104 DELETE FROM "delegation"
4105 WHERE "issue_id" = "issue_id_p";
4106 DELETE FROM "supporter"
4107 WHERE "issue_id" = "issue_id_p";
4108 UPDATE "issue" SET
4109 "state" = "issue_row"."state",
4110 "closed" = "issue_row"."closed",
4111 "ranks_available" = "issue_row"."ranks_available",
4112 "cleaned" = now()
4113 WHERE "id" = "issue_id_p";
4114 END IF;
4115 RETURN;
4116 END;
4117 $$;
4119 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
4122 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
4123 RETURNS VOID
4124 LANGUAGE 'plpgsql' VOLATILE AS $$
4125 BEGIN
4126 UPDATE "member" SET
4127 "last_login" = NULL,
4128 "last_login_public" = NULL,
4129 "login" = NULL,
4130 "password" = NULL,
4131 "locked" = TRUE,
4132 "active" = FALSE,
4133 "notify_email" = NULL,
4134 "notify_email_unconfirmed" = NULL,
4135 "notify_email_secret" = NULL,
4136 "notify_email_secret_expiry" = NULL,
4137 "notify_email_lock_expiry" = NULL,
4138 "password_reset_secret" = NULL,
4139 "password_reset_secret_expiry" = NULL,
4140 "organizational_unit" = NULL,
4141 "internal_posts" = NULL,
4142 "realname" = NULL,
4143 "birthday" = NULL,
4144 "address" = NULL,
4145 "email" = NULL,
4146 "xmpp_address" = NULL,
4147 "website" = NULL,
4148 "phone" = NULL,
4149 "mobile_phone" = NULL,
4150 "profession" = NULL,
4151 "external_memberships" = NULL,
4152 "external_posts" = NULL,
4153 "statement" = NULL
4154 WHERE "id" = "member_id_p";
4155 -- "text_search_data" is updated by triggers
4156 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
4157 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
4158 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
4159 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
4160 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
4161 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
4162 DELETE FROM "session" WHERE "member_id" = "member_id_p";
4163 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
4164 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
4165 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
4166 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
4167 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
4168 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
4169 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
4170 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
4171 DELETE FROM "direct_voter" USING "issue"
4172 WHERE "direct_voter"."issue_id" = "issue"."id"
4173 AND "issue"."closed" ISNULL
4174 AND "member_id" = "member_id_p";
4175 RETURN;
4176 END;
4177 $$;
4179 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)';
4182 CREATE FUNCTION "delete_private_data"()
4183 RETURNS VOID
4184 LANGUAGE 'plpgsql' VOLATILE AS $$
4185 BEGIN
4186 UPDATE "member" SET
4187 "last_login" = NULL,
4188 "login" = NULL,
4189 "password" = NULL,
4190 "notify_email" = NULL,
4191 "notify_email_unconfirmed" = NULL,
4192 "notify_email_secret" = NULL,
4193 "notify_email_secret_expiry" = NULL,
4194 "notify_email_lock_expiry" = NULL,
4195 "password_reset_secret" = NULL,
4196 "password_reset_secret_expiry" = NULL,
4197 "organizational_unit" = NULL,
4198 "internal_posts" = NULL,
4199 "realname" = NULL,
4200 "birthday" = NULL,
4201 "address" = NULL,
4202 "email" = NULL,
4203 "xmpp_address" = NULL,
4204 "website" = NULL,
4205 "phone" = NULL,
4206 "mobile_phone" = NULL,
4207 "profession" = NULL,
4208 "external_memberships" = NULL,
4209 "external_posts" = NULL,
4210 "statement" = NULL;
4211 -- "text_search_data" is updated by triggers
4212 DELETE FROM "invite_code";
4213 DELETE FROM "setting";
4214 DELETE FROM "setting_map";
4215 DELETE FROM "member_relation_setting";
4216 DELETE FROM "member_image";
4217 DELETE FROM "contact";
4218 DELETE FROM "ignored_member";
4219 DELETE FROM "session";
4220 DELETE FROM "area_setting";
4221 DELETE FROM "issue_setting";
4222 DELETE FROM "ignored_initiative";
4223 DELETE FROM "initiative_setting";
4224 DELETE FROM "suggestion_setting";
4225 DELETE FROM "non_voter";
4226 DELETE FROM "direct_voter" USING "issue"
4227 WHERE "direct_voter"."issue_id" = "issue"."id"
4228 AND "issue"."closed" ISNULL;
4229 RETURN;
4230 END;
4231 $$;
4233 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.';
4237 COMMIT;