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