liquid_feedback_core
view core.sql @ 468:c39ff9540f4d
Work on "issue_for_notification" table
| author | jbe |
|---|---|
| date | Sat Mar 26 22:24:23 2016 +0100 (2016-03-26) |
| parents | 1e7e8b025346 |
| children | c1e283fd6483 |
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 ('3.2.0', 3, 2, 0))
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 "temporary_transaction_data" (
58 PRIMARY KEY ("txid", "key"),
59 "txid" INT8 DEFAULT txid_current(),
60 "key" TEXT,
61 "value" TEXT NOT NULL );
63 COMMENT ON TABLE "temporary_transaction_data" IS 'Table to store temporary transaction data; shall be emptied before a transaction is committed';
65 COMMENT ON COLUMN "temporary_transaction_data"."txid" IS 'Value returned by function txid_current(); should be added to WHERE clause, when doing SELECT on this table, but ignored when doing DELETE on this table';
68 CREATE TABLE "system_setting" (
69 "member_ttl" INTERVAL );
70 CREATE UNIQUE INDEX "system_setting_singleton_idx" ON "system_setting" ((1));
72 COMMENT ON TABLE "system_setting" IS 'This table contains only one row with different settings in each column.';
73 COMMENT ON INDEX "system_setting_singleton_idx" IS 'This index ensures that "system_setting" only contains one row maximum.';
75 COMMENT ON COLUMN "system_setting"."member_ttl" IS 'Time after members get their "active" flag set to FALSE, if they do not show any activity.';
78 CREATE TABLE "contingent" (
79 PRIMARY KEY ("polling", "time_frame"),
80 "polling" BOOLEAN,
81 "time_frame" INTERVAL,
82 "text_entry_limit" INT4,
83 "initiative_limit" INT4 );
85 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.';
87 COMMENT ON COLUMN "contingent"."polling" IS 'Determines if settings are for creating initiatives and new drafts of initiatives with "polling" flag set';
88 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';
89 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
92 CREATE TYPE "notify_level" AS ENUM
93 ('none', 'voting', 'verification', 'discussion', 'all');
95 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';
98 CREATE TABLE "member" (
99 "id" SERIAL4 PRIMARY KEY,
100 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
101 "invite_code" TEXT UNIQUE,
102 "invite_code_expiry" TIMESTAMPTZ,
103 "admin_comment" TEXT,
104 "activated" TIMESTAMPTZ,
105 "last_activity" DATE,
106 "last_login" TIMESTAMPTZ,
107 "last_delegation_check" TIMESTAMPTZ,
108 "login" TEXT UNIQUE,
109 "password" TEXT,
110 "authority" TEXT,
111 "authority_uid" TEXT,
112 "authority_login" TEXT,
113 "locked" BOOLEAN NOT NULL DEFAULT FALSE,
114 "active" BOOLEAN NOT NULL DEFAULT FALSE,
115 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
116 "lang" TEXT,
117 "notify_email" TEXT,
118 "notify_email_unconfirmed" TEXT,
119 "notify_email_secret" TEXT UNIQUE,
120 "notify_email_secret_expiry" TIMESTAMPTZ,
121 "notify_email_lock_expiry" TIMESTAMPTZ,
122 "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE,
123 "login_recovery_expiry" TIMESTAMPTZ,
124 "password_reset_secret" TEXT UNIQUE,
125 "password_reset_secret_expiry" TIMESTAMPTZ,
126 "name" TEXT UNIQUE,
127 "identification" TEXT UNIQUE,
128 "authentication" TEXT,
129 "organizational_unit" TEXT,
130 "internal_posts" TEXT,
131 "realname" TEXT,
132 "birthday" DATE,
133 "address" TEXT,
134 "email" TEXT,
135 "xmpp_address" TEXT,
136 "website" TEXT,
137 "phone" TEXT,
138 "mobile_phone" TEXT,
139 "profession" TEXT,
140 "external_memberships" TEXT,
141 "external_posts" TEXT,
142 "formatting_engine" TEXT,
143 "statement" TEXT,
144 "text_search_data" TSVECTOR,
145 CONSTRAINT "active_requires_activated_and_last_activity"
146 CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)),
147 CONSTRAINT "authority_requires_uid_and_vice_versa"
148 CHECK (("authority" NOTNULL) = ("authority_uid" NOTNULL)),
149 CONSTRAINT "authority_uid_unique_per_authority"
150 UNIQUE ("authority", "authority_uid"),
151 CONSTRAINT "authority_login_requires_authority"
152 CHECK ("authority" NOTNULL OR "authority_login" ISNULL),
153 CONSTRAINT "name_not_null_if_activated"
154 CHECK ("activated" ISNULL OR "name" NOTNULL) );
155 CREATE INDEX "member_authority_login_idx" ON "member" ("authority_login");
156 CREATE INDEX "member_active_idx" ON "member" ("active");
157 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
158 CREATE TRIGGER "update_text_search_data"
159 BEFORE INSERT OR UPDATE ON "member"
160 FOR EACH ROW EXECUTE PROCEDURE
161 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
162 "name", "identification", "organizational_unit", "internal_posts",
163 "realname", "external_memberships", "external_posts", "statement" );
165 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
167 COMMENT ON COLUMN "member"."created" IS 'Creation of member record and/or invite code';
168 COMMENT ON COLUMN "member"."invite_code" IS 'Optional invite code, to allow a member to initialize his/her account the first time';
169 COMMENT ON COLUMN "member"."invite_code_expiry" IS 'Expiry data/time for "invite_code"';
170 COMMENT ON COLUMN "member"."admin_comment" IS 'Hidden comment for administrative purposes';
171 COMMENT ON COLUMN "member"."activated" IS 'Timestamp of first activation of account (i.e. usage of "invite_code"); required to be set for "active" members';
172 COMMENT ON COLUMN "member"."last_activity" IS 'Date of last activity of member; required to be set for "active" members';
173 COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login';
174 COMMENT ON COLUMN "member"."last_delegation_check" IS 'Timestamp of last delegation check (i.e. confirmation of all unit and area delegations)';
175 COMMENT ON COLUMN "member"."login" IS 'Login name';
176 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
177 COMMENT ON COLUMN "member"."authority" IS 'NULL if LiquidFeedback Core is authoritative for the member account; otherwise a string that indicates the source/authority of the external account (e.g. ''LDAP'' for an LDAP account)';
178 COMMENT ON COLUMN "member"."authority_uid" IS 'Unique identifier (unique per "authority") that allows to identify an external account (e.g. even if the login name changes)';
179 COMMENT ON COLUMN "member"."authority_login" IS 'Login name for external accounts (field is not unique!)';
180 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
181 COMMENT ON COLUMN "member"."active" IS 'Memberships, support and votes are taken into account when corresponding members are marked as active. Automatically set to FALSE, if "last_activity" is older than "system_setting"."member_ttl".';
182 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
183 COMMENT ON COLUMN "member"."lang" IS 'Language code of the preferred language of the member';
184 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
185 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
186 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
187 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
188 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
189 COMMENT ON COLUMN "member"."disable_notifications" IS 'TRUE if member does not want to receive notifications';
190 COMMENT ON COLUMN "member"."login_recovery_expiry" IS 'Date/time after which another login recovery attempt is allowed';
191 COMMENT ON COLUMN "member"."password_reset_secret" IS 'Secret string sent via e-mail for password recovery';
192 COMMENT ON COLUMN "member"."password_reset_secret_expiry" IS 'Date/time until the password recovery secret is valid, and date/time after which another password recovery attempt is allowed';
193 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member, may be NULL if account has not been activated yet';
194 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
195 COMMENT ON COLUMN "member"."authentication" IS 'Information about how this member was authenticated';
196 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
197 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
198 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
199 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
200 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
201 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
202 COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"';
203 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his/her profile';
206 CREATE TABLE "member_history" (
207 "id" SERIAL8 PRIMARY KEY,
208 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
209 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
210 "active" BOOLEAN NOT NULL,
211 "name" TEXT NOT NULL );
212 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
214 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
216 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
217 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
220 CREATE TABLE "rendered_member_statement" (
221 PRIMARY KEY ("member_id", "format"),
222 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
223 "format" TEXT,
224 "content" TEXT NOT NULL );
226 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)';
229 CREATE TABLE "subscription_time" (
230 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
231 "day_of_week" INT2 CONSTRAINT "day_of_week_range" CHECK ("day_of_week" BETWEEN 0 AND 6),
232 "time_of_day" TIME NOT NULL );
233 CREATE UNIQUE INDEX "subscription_time_all_days_of_week_time_of_day_idx" ON "subscription_time" ("time_of_day", "member_id") WHERE ("day_of_week" ISNULL);
234 CREATE UNIQUE INDEX "subscription_time_day_of_week_time_of_day_idx" ON "subscription_time" ("day_of_week", "time_of_day", "member_id");
236 COMMENT ON TABLE "subscription_time" IS 'Selects when a member receives digests on new user content in the system';
239 CREATE TABLE "setting" (
240 PRIMARY KEY ("member_id", "key"),
241 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
242 "key" TEXT NOT NULL,
243 "value" TEXT NOT NULL );
244 CREATE INDEX "setting_key_idx" ON "setting" ("key");
246 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
248 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
251 CREATE TABLE "setting_map" (
252 PRIMARY KEY ("member_id", "key", "subkey"),
253 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
254 "key" TEXT NOT NULL,
255 "subkey" TEXT NOT NULL,
256 "value" TEXT NOT NULL );
257 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
259 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
261 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
262 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
263 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
266 CREATE TABLE "member_relation_setting" (
267 PRIMARY KEY ("member_id", "key", "other_member_id"),
268 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
269 "key" TEXT NOT NULL,
270 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
271 "value" TEXT NOT NULL );
273 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
276 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
278 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
281 CREATE TABLE "member_image" (
282 PRIMARY KEY ("member_id", "image_type", "scaled"),
283 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
284 "image_type" "member_image_type",
285 "scaled" BOOLEAN,
286 "content_type" TEXT,
287 "data" BYTEA NOT NULL );
289 COMMENT ON TABLE "member_image" IS 'Images of members';
291 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
294 CREATE TABLE "member_count" (
295 "calculated" TIMESTAMPTZ NOT NULL DEFAULT now(),
296 "total_count" INT4 NOT NULL );
298 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';
300 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
301 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
304 CREATE TABLE "contact" (
305 PRIMARY KEY ("member_id", "other_member_id"),
306 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
307 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
308 "public" BOOLEAN NOT NULL DEFAULT FALSE,
309 CONSTRAINT "cant_save_yourself_as_contact"
310 CHECK ("member_id" != "other_member_id") );
311 CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
313 COMMENT ON TABLE "contact" IS 'Contact lists';
315 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
316 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
317 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
320 CREATE TABLE "ignored_member" (
321 PRIMARY KEY ("member_id", "other_member_id"),
322 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
323 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
324 CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
326 COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
328 COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone';
329 COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
332 CREATE TABLE "session" (
333 "ident" TEXT PRIMARY KEY,
334 "additional_secret" TEXT,
335 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
336 "member_id" INT4 REFERENCES "member" ("id") ON DELETE SET NULL,
337 "authority" TEXT,
338 "authority_uid" TEXT,
339 "authority_login" TEXT,
340 "needs_delegation_check" BOOLEAN NOT NULL DEFAULT FALSE,
341 "lang" TEXT );
342 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
344 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer';
346 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
347 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
348 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
349 COMMENT ON COLUMN "session"."authority" IS 'Temporary store for "member"."authority" during member account creation';
350 COMMENT ON COLUMN "session"."authority_uid" IS 'Temporary store for "member"."authority_uid" during member account creation';
351 COMMENT ON COLUMN "session"."authority_login" IS 'Temporary store for "member"."authority_login" during member account creation';
352 COMMENT ON COLUMN "session"."needs_delegation_check" IS 'Set to TRUE, if member must perform a delegation check to proceed with login; see column "last_delegation_check" in "member" table';
353 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
356 CREATE TYPE "defeat_strength" AS ENUM ('simple', 'tuple');
358 COMMENT ON TYPE "defeat_strength" IS 'How pairwise defeats are measured for the Schulze method: ''simple'' = only the number of winning votes, ''tuple'' = primarily the number of winning votes, secondarily the number of losing votes';
361 CREATE TYPE "tie_breaking" AS ENUM ('simple', 'variant1', 'variant2');
363 COMMENT ON TYPE "tie_breaking" IS 'Tie-breaker for the Schulze method: ''simple'' = only initiative ids are used, ''variant1'' = use initiative ids in variant 1 for tie breaking of the links (TBRL) and sequentially forbid shared links, ''variant2'' = use initiative ids in variant 2 for tie breaking of the links (TBRL) and sequentially forbid shared links';
366 CREATE TABLE "policy" (
367 "id" SERIAL4 PRIMARY KEY,
368 "index" INT4 NOT NULL,
369 "active" BOOLEAN NOT NULL DEFAULT TRUE,
370 "name" TEXT NOT NULL UNIQUE,
371 "description" TEXT NOT NULL DEFAULT '',
372 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
373 "min_admission_time" INTERVAL,
374 "max_admission_time" INTERVAL,
375 "discussion_time" INTERVAL,
376 "verification_time" INTERVAL,
377 "voting_time" INTERVAL,
378 "issue_quorum_num" INT4,
379 "issue_quorum_den" INT4,
380 "initiative_quorum_num" INT4 NOT NULL,
381 "initiative_quorum_den" INT4 NOT NULL,
382 "defeat_strength" "defeat_strength" NOT NULL DEFAULT 'tuple',
383 "tie_breaking" "tie_breaking" NOT NULL DEFAULT 'variant1',
384 "direct_majority_num" INT4 NOT NULL DEFAULT 1,
385 "direct_majority_den" INT4 NOT NULL DEFAULT 2,
386 "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
387 "direct_majority_positive" INT4 NOT NULL DEFAULT 0,
388 "direct_majority_non_negative" INT4 NOT NULL DEFAULT 0,
389 "indirect_majority_num" INT4 NOT NULL DEFAULT 1,
390 "indirect_majority_den" INT4 NOT NULL DEFAULT 2,
391 "indirect_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
392 "indirect_majority_positive" INT4 NOT NULL DEFAULT 0,
393 "indirect_majority_non_negative" INT4 NOT NULL DEFAULT 0,
394 "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT FALSE,
395 "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE,
396 CONSTRAINT "timing" CHECK (
397 ( "polling" = FALSE AND
398 "min_admission_time" NOTNULL AND "max_admission_time" NOTNULL AND
399 "min_admission_time" <= "max_admission_time" AND
400 "discussion_time" NOTNULL AND
401 "verification_time" NOTNULL AND
402 "voting_time" NOTNULL ) OR
403 ( "polling" = TRUE AND
404 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
405 "discussion_time" NOTNULL AND
406 "verification_time" NOTNULL AND
407 "voting_time" NOTNULL ) OR
408 ( "polling" = TRUE AND
409 "min_admission_time" ISNULL AND "max_admission_time" ISNULL AND
410 "discussion_time" ISNULL AND
411 "verification_time" ISNULL AND
412 "voting_time" ISNULL ) ),
413 CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK (
414 "polling" = ("issue_quorum_num" ISNULL) AND
415 "polling" = ("issue_quorum_den" ISNULL) ),
416 CONSTRAINT "no_reverse_beat_path_requires_tuple_defeat_strength" CHECK (
417 "defeat_strength" = 'tuple'::"defeat_strength" OR
418 "no_reverse_beat_path" = FALSE ) );
419 CREATE INDEX "policy_active_idx" ON "policy" ("active");
421 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
423 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
424 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
425 COMMENT ON COLUMN "policy"."polling" IS 'TRUE = special policy for non-user-generated issues without issue quorum, where certain initiatives (those having the "polling" flag set) do not need to pass the initiative quorum; "min_admission_time" and "max_admission_time" MUST be set to NULL, the other timings may be set to NULL altogether, allowing individual timing for those issues';
426 COMMENT ON COLUMN "policy"."min_admission_time" IS 'Minimum duration of issue state ''admission''; Minimum time an issue stays open';
427 COMMENT ON COLUMN "policy"."max_admission_time" IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"';
428 COMMENT ON COLUMN "policy"."discussion_time" IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"';
429 COMMENT ON COLUMN "policy"."verification_time" IS 'Duration of issue state ''verification''; Regular time until an issue is "fully_frozen" (e.g. entering issue state ''voting'') after being "half_frozen"';
430 COMMENT ON COLUMN "policy"."voting_time" IS 'Duration of issue state ''voting''; Time after an issue is "fully_frozen" but not "closed" (duration of issue state ''voting'')';
431 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" and enter issue state ''discussion''';
432 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" and enter issue state ''discussion''';
433 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
434 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
435 COMMENT ON COLUMN "policy"."defeat_strength" IS 'How pairwise defeats are measured for the Schulze method; see type "defeat_strength"; ''tuple'' is the recommended setting';
436 COMMENT ON COLUMN "policy"."tie_breaking" IS 'Tie-breaker for the Schulze method; see type "tie_breaking"; ''variant1'' or ''variant2'' are recommended';
437 COMMENT ON COLUMN "policy"."direct_majority_num" IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner';
438 COMMENT ON COLUMN "policy"."direct_majority_den" IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner';
439 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.';
440 COMMENT ON COLUMN "policy"."direct_majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner';
441 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';
442 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';
443 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';
444 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.';
445 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';
446 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';
447 COMMENT ON COLUMN "policy"."no_reverse_beat_path" IS 'EXPERIMENTAL FEATURE: 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.';
448 COMMENT ON COLUMN "policy"."no_multistage_majority" IS 'EXPERIMENTAL FEATURE: 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").';
451 CREATE TABLE "unit" (
452 "id" SERIAL4 PRIMARY KEY,
453 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
454 "active" BOOLEAN NOT NULL DEFAULT TRUE,
455 "name" TEXT NOT NULL,
456 "description" TEXT NOT NULL DEFAULT '',
457 "external_reference" TEXT,
458 "member_count" INT4,
459 "text_search_data" TSVECTOR );
460 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
461 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
462 CREATE INDEX "unit_active_idx" ON "unit" ("active");
463 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
464 CREATE TRIGGER "update_text_search_data"
465 BEFORE INSERT OR UPDATE ON "unit"
466 FOR EACH ROW EXECUTE PROCEDURE
467 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
468 "name", "description" );
470 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
472 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
473 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in areas of this unit';
474 COMMENT ON COLUMN "unit"."external_reference" IS 'Opaque data field to store an external reference';
475 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
478 CREATE TABLE "subscription" (
479 PRIMARY KEY ("member_id", "unit_id"),
480 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
481 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
482 CREATE INDEX "subscription_unit_id_idx" ON "subscription" ("unit_id");
484 COMMENT ON TABLE "subscription" IS 'An entry in this table denotes that the member wishes to receive notifications regardless of his/her privileges in the given unit';
487 CREATE TABLE "unit_setting" (
488 PRIMARY KEY ("member_id", "key", "unit_id"),
489 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
490 "key" TEXT NOT NULL,
491 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
492 "value" TEXT NOT NULL );
494 COMMENT ON TABLE "unit_setting" IS 'Place for frontend to store unit specific settings of members as strings';
497 CREATE TABLE "area" (
498 "id" SERIAL4 PRIMARY KEY,
499 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
500 "active" BOOLEAN NOT NULL DEFAULT TRUE,
501 "name" TEXT NOT NULL,
502 "description" TEXT NOT NULL DEFAULT '',
503 "external_reference" TEXT,
504 "direct_member_count" INT4,
505 "member_weight" INT4,
506 "text_search_data" TSVECTOR );
507 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
508 CREATE INDEX "area_active_idx" ON "area" ("active");
509 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
510 CREATE TRIGGER "update_text_search_data"
511 BEFORE INSERT OR UPDATE ON "area"
512 FOR EACH ROW EXECUTE PROCEDURE
513 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
514 "name", "description" );
516 COMMENT ON TABLE "area" IS 'Subject areas';
518 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
519 COMMENT ON COLUMN "area"."external_reference" IS 'Opaque data field to store an external reference';
520 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"';
521 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
524 CREATE TABLE "ignored_area" (
525 PRIMARY KEY ("member_id", "area_id"),
526 "member_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
527 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
528 CREATE INDEX "ignored_area_area_id_idx" ON "ignored_area" ("area_id");
530 COMMENT ON TABLE "ignored_area" IS 'An entry in this table denotes that the member does not wish to receive notifications for the given subject area unless he/she declared interested in a particular issue';
533 CREATE TABLE "area_setting" (
534 PRIMARY KEY ("member_id", "key", "area_id"),
535 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
536 "key" TEXT NOT NULL,
537 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
538 "value" TEXT NOT NULL );
540 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
543 CREATE TABLE "allowed_policy" (
544 PRIMARY KEY ("area_id", "policy_id"),
545 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
546 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
547 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
548 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
550 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
552 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
555 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
557 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';
560 CREATE TYPE "issue_state" AS ENUM (
561 'admission', 'discussion', 'verification', 'voting',
562 'canceled_by_admin',
563 'canceled_revoked_before_accepted',
564 'canceled_issue_not_accepted',
565 'canceled_after_revocation_during_discussion',
566 'canceled_after_revocation_during_verification',
567 'canceled_no_initiative_admitted',
568 'finished_without_winner', 'finished_with_winner');
570 COMMENT ON TYPE "issue_state" IS 'State of issues';
573 CREATE TABLE "issue" (
574 "id" SERIAL4 PRIMARY KEY,
575 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
576 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
577 "admin_notice" TEXT,
578 "external_reference" TEXT,
579 "state" "issue_state" NOT NULL DEFAULT 'admission',
580 "phase_finished" TIMESTAMPTZ,
581 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
582 "accepted" TIMESTAMPTZ,
583 "half_frozen" TIMESTAMPTZ,
584 "fully_frozen" TIMESTAMPTZ,
585 "closed" TIMESTAMPTZ,
586 "cleaned" TIMESTAMPTZ,
587 "min_admission_time" INTERVAL,
588 "max_admission_time" INTERVAL,
589 "discussion_time" INTERVAL NOT NULL,
590 "verification_time" INTERVAL NOT NULL,
591 "voting_time" INTERVAL NOT NULL,
592 "snapshot" TIMESTAMPTZ,
593 "latest_snapshot_event" "snapshot_event",
594 "population" INT4,
595 "voter_count" INT4,
596 "status_quo_schulze_rank" INT4,
597 CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK (
598 ("min_admission_time" NOTNULL) = ("max_admission_time" NOTNULL) AND
599 ("min_admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created")) ),
600 CONSTRAINT "valid_state" CHECK (
601 (
602 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR
603 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR
604 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL ) OR
605 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL)
606 ) AND (
607 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
608 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
609 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
610 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
611 ("state" = 'canceled_by_admin' AND "closed" NOTNULL) OR
612 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
613 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
614 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
615 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
616 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" = "fully_frozen") OR
617 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen") OR
618 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen")
619 )),
620 CONSTRAINT "phase_finished_only_when_not_closed" CHECK (
621 "phase_finished" ISNULL OR "closed" ISNULL ),
622 CONSTRAINT "state_change_order" CHECK (
623 "created" <= "accepted" AND
624 "accepted" <= "half_frozen" AND
625 "half_frozen" <= "fully_frozen" AND
626 "fully_frozen" <= "closed" ),
627 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
628 "cleaned" ISNULL OR "closed" NOTNULL ),
629 CONSTRAINT "last_snapshot_on_full_freeze"
630 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
631 CONSTRAINT "freeze_requires_snapshot"
632 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
633 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
634 CHECK (("snapshot" NOTNULL) = ("latest_snapshot_event" NOTNULL)) );
635 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
636 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
637 CREATE INDEX "issue_created_idx" ON "issue" ("created");
638 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
639 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
640 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
641 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
642 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
643 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
645 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
647 COMMENT ON COLUMN "issue"."admin_notice" IS 'Public notice by admin to explain manual interventions, or to announce corrections';
648 COMMENT ON COLUMN "issue"."external_reference" IS 'Opaque data field to store an external reference';
649 COMMENT ON COLUMN "issue"."phase_finished" IS 'Set to a value NOTNULL, if the current phase has finished, but calculations are pending; No changes in this issue shall be made by the frontend or API when this value is set';
650 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
651 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.';
652 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.';
653 COMMENT ON COLUMN "issue"."closed" IS 'Point in time, when "max_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.';
654 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
655 COMMENT ON COLUMN "issue"."min_admission_time" IS 'Copied from "policy" table at creation of issue';
656 COMMENT ON COLUMN "issue"."max_admission_time" IS 'Copied from "policy" table at creation of issue';
657 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
658 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
659 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
660 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
661 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';
662 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
663 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';
664 COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
667 CREATE TABLE "issue_order_in_admission_state" (
668 "id" INT8 PRIMARY KEY, --REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
669 "order_in_area" INT4,
670 "order_in_unit" INT4 );
672 COMMENT ON TABLE "issue_order_in_admission_state" IS 'Ordering information for issues that are not stored in the "issue" table to avoid locking of multiple issues at once; Filled/updated by "lf_update_issue_order"';
674 COMMENT ON COLUMN "issue_order_in_admission_state"."id" IS 'References "issue" ("id") but has no referential integrity trigger associated, due to performance/locking issues';
675 COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_area" IS 'Order of issues in admission state within a single area; NULL values sort last';
676 COMMENT ON COLUMN "issue_order_in_admission_state"."order_in_unit" IS 'Order of issues in admission state within all areas of a unit; NULL values sort last';
679 CREATE TABLE "issue_setting" (
680 PRIMARY KEY ("member_id", "key", "issue_id"),
681 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
682 "key" TEXT NOT NULL,
683 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
684 "value" TEXT NOT NULL );
686 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
689 CREATE TABLE "initiative" (
690 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
691 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
692 "id" SERIAL4 PRIMARY KEY,
693 "name" TEXT NOT NULL,
694 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
695 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
696 "revoked" TIMESTAMPTZ,
697 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
698 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
699 "external_reference" TEXT,
700 "admitted" BOOLEAN,
701 "supporter_count" INT4,
702 "informed_supporter_count" INT4,
703 "satisfied_supporter_count" INT4,
704 "satisfied_informed_supporter_count" INT4,
705 "harmonic_weight" NUMERIC(12, 3),
706 "final_suggestion_order_calculated" BOOLEAN NOT NULL DEFAULT FALSE,
707 "first_preference_votes" INT4,
708 "positive_votes" INT4,
709 "negative_votes" INT4,
710 "direct_majority" BOOLEAN,
711 "indirect_majority" BOOLEAN,
712 "schulze_rank" INT4,
713 "better_than_status_quo" BOOLEAN,
714 "worse_than_status_quo" BOOLEAN,
715 "reverse_beat_path" BOOLEAN,
716 "multistage_majority" BOOLEAN,
717 "eligible" BOOLEAN,
718 "winner" BOOLEAN,
719 "rank" INT4,
720 "text_search_data" TSVECTOR,
721 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
722 CHECK (("revoked" NOTNULL) = ("revoked_by_member_id" NOTNULL)),
723 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
724 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
725 CONSTRAINT "revoked_initiatives_cant_be_admitted"
726 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
727 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
728 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
729 ( "first_preference_votes" ISNULL AND
730 "positive_votes" ISNULL AND "negative_votes" ISNULL AND
731 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
732 "schulze_rank" ISNULL AND
733 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
734 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
735 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ),
736 CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")),
737 CONSTRAINT "minimum_requirement_to_be_eligible" CHECK (
738 "eligible" = FALSE OR
739 ("direct_majority" AND "indirect_majority" AND "better_than_status_quo") ),
740 CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE),
741 CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1),
742 CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE),
743 CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") );
744 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
745 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
746 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
747 CREATE TRIGGER "update_text_search_data"
748 BEFORE INSERT OR UPDATE ON "initiative"
749 FOR EACH ROW EXECUTE PROCEDURE
750 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "name");
752 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.';
754 COMMENT ON COLUMN "initiative"."polling" IS 'Initiative does not need to pass the initiative quorum (see "policy"."polling")';
755 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
756 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoke the initiative';
757 COMMENT ON COLUMN "initiative"."external_reference" IS 'Opaque data field to store an external reference';
758 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
759 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
760 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
761 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
762 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
763 COMMENT ON COLUMN "initiative"."harmonic_weight" IS 'Indicates the relevancy of the initiative, calculated from the potential supporters weighted with the harmonic series to avoid a large number of clones affecting other initiative''s sorting positions too much; shall be used as secondary sorting key after "admitted" as primary sorting key';
764 COMMENT ON COLUMN "initiative"."final_suggestion_order_calculated" IS 'Set to TRUE, when "proportional_order" of suggestions has been calculated the last time';
765 COMMENT ON COLUMN "initiative"."first_preference_votes" IS 'Number of direct and delegating voters who ranked this initiative as their first choice';
766 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Number of direct and delegating voters who ranked this initiative better than the status quo';
767 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Number of direct and delegating voters who ranked this initiative worse than the status quo';
768 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"';
769 COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths';
770 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking';
771 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo';
772 COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (DEPRECATED, since schulze-ranking is unique per issue; use "better_than_status_quo"=FALSE)';
773 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; set to NULL if "policy"."defeat_strength" is set to ''simple''';
774 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';
775 COMMENT ON COLUMN "initiative"."eligible" IS 'Initiative has a "direct_majority" and an "indirect_majority", is "better_than_status_quo" and depending on selected policy the initiative has no "reverse_beat_path" or "multistage_majority"';
776 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank"';
777 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';
780 CREATE TABLE "battle" (
781 "issue_id" INT4 NOT NULL,
782 "winning_initiative_id" INT4,
783 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
784 "losing_initiative_id" INT4,
785 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
786 "count" INT4 NOT NULL,
787 CONSTRAINT "initiative_ids_not_equal" CHECK (
788 "winning_initiative_id" != "losing_initiative_id" OR
789 ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
790 ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) );
791 CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
792 CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
793 CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
795 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';
798 CREATE TABLE "ignored_initiative" (
799 PRIMARY KEY ("member_id", "initiative_id"),
800 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
801 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
802 CREATE INDEX "ignored_initiative_initiative_id_idx" ON "ignored_initiative" ("initiative_id");
804 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
807 CREATE TABLE "initiative_setting" (
808 PRIMARY KEY ("member_id", "key", "initiative_id"),
809 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
810 "key" TEXT NOT NULL,
811 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
812 "value" TEXT NOT NULL );
814 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
817 CREATE TABLE "draft" (
818 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
819 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
820 "id" SERIAL8 PRIMARY KEY,
821 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
822 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
823 "formatting_engine" TEXT,
824 "content" TEXT NOT NULL,
825 "external_reference" TEXT,
826 "text_search_data" TSVECTOR );
827 CREATE INDEX "draft_created_idx" ON "draft" ("created");
828 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
829 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
830 CREATE TRIGGER "update_text_search_data"
831 BEFORE INSERT OR UPDATE ON "draft"
832 FOR EACH ROW EXECUTE PROCEDURE
833 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
835 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.';
837 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
838 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
839 COMMENT ON COLUMN "draft"."external_reference" IS 'Opaque data field to store an external reference';
842 CREATE TABLE "rendered_draft" (
843 PRIMARY KEY ("draft_id", "format"),
844 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
845 "format" TEXT,
846 "content" TEXT NOT NULL );
848 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)';
851 CREATE TABLE "suggestion" (
852 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
853 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
854 "id" SERIAL8 PRIMARY KEY,
855 "draft_id" INT8 NOT NULL,
856 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
857 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
858 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
859 "name" TEXT NOT NULL,
860 "formatting_engine" TEXT,
861 "content" TEXT NOT NULL DEFAULT '',
862 "external_reference" TEXT,
863 "text_search_data" TSVECTOR,
864 "minus2_unfulfilled_count" INT4,
865 "minus2_fulfilled_count" INT4,
866 "minus1_unfulfilled_count" INT4,
867 "minus1_fulfilled_count" INT4,
868 "plus1_unfulfilled_count" INT4,
869 "plus1_fulfilled_count" INT4,
870 "plus2_unfulfilled_count" INT4,
871 "plus2_fulfilled_count" INT4,
872 "proportional_order" INT4 );
873 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
874 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
875 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
876 CREATE TRIGGER "update_text_search_data"
877 BEFORE INSERT OR UPDATE ON "suggestion"
878 FOR EACH ROW EXECUTE PROCEDURE
879 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
880 "name", "content");
882 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';
884 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")';
885 COMMENT ON COLUMN "suggestion"."external_reference" IS 'Opaque data field to store an external reference';
886 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
887 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
888 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
889 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
890 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
891 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
892 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
893 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
894 COMMENT ON COLUMN "suggestion"."proportional_order" IS 'To be used for sorting suggestions within an initiative; NULL values sort last; updated by "lf_update_suggestion_order"';
897 CREATE TABLE "rendered_suggestion" (
898 PRIMARY KEY ("suggestion_id", "format"),
899 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
900 "format" TEXT,
901 "content" TEXT NOT NULL );
903 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)';
906 CREATE TABLE "suggestion_setting" (
907 PRIMARY KEY ("member_id", "key", "suggestion_id"),
908 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
909 "key" TEXT NOT NULL,
910 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
911 "value" TEXT NOT NULL );
913 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
916 CREATE TABLE "privilege" (
917 PRIMARY KEY ("unit_id", "member_id"),
918 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
919 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
920 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
921 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
922 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
923 "member_manager" BOOLEAN NOT NULL DEFAULT FALSE,
924 "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE,
925 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE,
926 "polling_right" BOOLEAN NOT NULL DEFAULT FALSE );
928 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
930 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke any privileges to/from other members';
931 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create and disable sub units';
932 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create and disable areas and set area parameters';
933 COMMENT ON COLUMN "privilege"."member_manager" IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"';
934 COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative';
935 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to support initiatives, create and rate suggestions, and to vote';
936 COMMENT ON COLUMN "privilege"."polling_right" IS 'Right to create issues with policies having the "policy"."polling" flag set, and to add initiatives having the "initiative"."polling" flag set to those issues';
939 CREATE TABLE "membership" (
940 PRIMARY KEY ("area_id", "member_id"),
941 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
942 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
943 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
945 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
948 CREATE TABLE "interest" (
949 PRIMARY KEY ("issue_id", "member_id"),
950 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
951 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
952 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
954 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.';
957 CREATE TABLE "initiator" (
958 PRIMARY KEY ("initiative_id", "member_id"),
959 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
960 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
961 "accepted" BOOLEAN );
962 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
964 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.';
966 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.';
969 CREATE TABLE "supporter" (
970 "issue_id" INT4 NOT NULL,
971 PRIMARY KEY ("initiative_id", "member_id"),
972 "initiative_id" INT4,
973 "member_id" INT4,
974 "draft_id" INT8 NOT NULL,
975 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
976 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE );
977 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
979 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.';
981 COMMENT ON COLUMN "supporter"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
982 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")';
985 CREATE TABLE "opinion" (
986 "initiative_id" INT4 NOT NULL,
987 PRIMARY KEY ("suggestion_id", "member_id"),
988 "suggestion_id" INT8,
989 "member_id" INT4,
990 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
991 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
992 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
993 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
994 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
996 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.';
998 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
1001 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
1003 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
1006 CREATE TABLE "delegation" (
1007 "id" SERIAL8 PRIMARY KEY,
1008 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1009 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1010 "scope" "delegation_scope" NOT NULL,
1011 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1012 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1013 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1014 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
1015 CONSTRAINT "no_unit_delegation_to_null"
1016 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
1017 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
1018 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
1019 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
1020 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
1021 UNIQUE ("unit_id", "truster_id"),
1022 UNIQUE ("area_id", "truster_id"),
1023 UNIQUE ("issue_id", "truster_id") );
1024 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
1025 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
1027 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
1029 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
1030 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
1031 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
1034 CREATE TABLE "direct_population_snapshot" (
1035 PRIMARY KEY ("issue_id", "event", "member_id"),
1036 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1037 "event" "snapshot_event",
1038 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1039 "weight" INT4 );
1040 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
1042 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"; for corrections refer to column "issue_notice" of "issue" table';
1044 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1045 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
1048 CREATE TABLE "delegating_population_snapshot" (
1049 PRIMARY KEY ("issue_id", "event", "member_id"),
1050 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1051 "event" "snapshot_event",
1052 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1053 "weight" INT4,
1054 "scope" "delegation_scope" NOT NULL,
1055 "delegate_member_ids" INT4[] NOT NULL );
1056 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
1058 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table; for corrections refer to column "issue_notice" of "issue" table';
1060 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1061 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
1062 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
1063 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"';
1066 CREATE TABLE "direct_interest_snapshot" (
1067 PRIMARY KEY ("issue_id", "event", "member_id"),
1068 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1069 "event" "snapshot_event",
1070 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1071 "weight" INT4 );
1072 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
1074 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"; for corrections refer to column "issue_notice" of "issue" table';
1076 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1077 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
1080 CREATE TABLE "delegating_interest_snapshot" (
1081 PRIMARY KEY ("issue_id", "event", "member_id"),
1082 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1083 "event" "snapshot_event",
1084 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1085 "weight" INT4,
1086 "scope" "delegation_scope" NOT NULL,
1087 "delegate_member_ids" INT4[] NOT NULL );
1088 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
1090 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table; for corrections refer to column "issue_notice" of "issue" table';
1092 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1093 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
1094 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
1095 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"';
1098 CREATE TABLE "direct_supporter_snapshot" (
1099 "issue_id" INT4 NOT NULL,
1100 PRIMARY KEY ("initiative_id", "event", "member_id"),
1101 "initiative_id" INT4,
1102 "event" "snapshot_event",
1103 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1104 "draft_id" INT8 NOT NULL,
1105 "informed" BOOLEAN NOT NULL,
1106 "satisfied" BOOLEAN NOT NULL,
1107 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1108 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
1109 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1110 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
1112 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot"); for corrections refer to column "issue_notice" of "issue" table';
1114 COMMENT ON COLUMN "direct_supporter_snapshot"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
1115 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1116 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
1117 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
1120 CREATE TABLE "non_voter" (
1121 PRIMARY KEY ("issue_id", "member_id"),
1122 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1123 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
1124 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
1126 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
1129 CREATE TABLE "direct_voter" (
1130 PRIMARY KEY ("issue_id", "member_id"),
1131 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1132 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1133 "weight" INT4,
1134 "comment_changed" TIMESTAMPTZ,
1135 "formatting_engine" TEXT,
1136 "comment" TEXT,
1137 "text_search_data" TSVECTOR );
1138 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
1139 CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data");
1140 CREATE TRIGGER "update_text_search_data"
1141 BEFORE INSERT OR UPDATE ON "direct_voter"
1142 FOR EACH ROW EXECUTE PROCEDURE
1143 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment");
1145 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; for corrections refer to column "issue_notice" of "issue" table';
1147 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
1148 COMMENT ON COLUMN "direct_voter"."comment_changed" IS 'Shall be set on comment change, to indicate a comment being modified after voting has been finished; Automatically set to NULL after voting phase; Automatically set to NULL by trigger, if "comment" is set to NULL';
1149 COMMENT ON COLUMN "direct_voter"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "direct_voter"."comment"; Automatically set to NULL by trigger, if "comment" is set to NULL';
1150 COMMENT ON COLUMN "direct_voter"."comment" 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.';
1153 CREATE TABLE "rendered_voter_comment" (
1154 PRIMARY KEY ("issue_id", "member_id", "format"),
1155 FOREIGN KEY ("issue_id", "member_id")
1156 REFERENCES "direct_voter" ("issue_id", "member_id")
1157 ON DELETE CASCADE ON UPDATE CASCADE,
1158 "issue_id" INT4,
1159 "member_id" INT4,
1160 "format" TEXT,
1161 "content" TEXT NOT NULL );
1163 COMMENT ON TABLE "rendered_voter_comment" IS 'This table may be used by frontends to cache "rendered" voter comments (e.g. HTML output generated from wiki text)';
1166 CREATE TABLE "delegating_voter" (
1167 PRIMARY KEY ("issue_id", "member_id"),
1168 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1169 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1170 "weight" INT4,
1171 "scope" "delegation_scope" NOT NULL,
1172 "delegate_member_ids" INT4[] NOT NULL );
1173 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
1175 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table; for corrections refer to column "issue_notice" of "issue" table';
1177 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
1178 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
1179 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"';
1182 CREATE TABLE "vote" (
1183 "issue_id" INT4 NOT NULL,
1184 PRIMARY KEY ("initiative_id", "member_id"),
1185 "initiative_id" INT4,
1186 "member_id" INT4,
1187 "grade" INT4 NOT NULL,
1188 "first_preference" BOOLEAN,
1189 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1190 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
1191 CONSTRAINT "first_preference_flag_only_set_on_positive_grades"
1192 CHECK ("grade" > 0 OR "first_preference" ISNULL) );
1193 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
1195 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; for corrections refer to column "issue_notice" of "issue" table';
1197 COMMENT ON COLUMN "vote"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
1198 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.';
1199 COMMENT ON COLUMN "vote"."first_preference" IS 'Value is automatically set after voting is finished. For positive grades, this value is set to true for the highest (i.e. best) grade.';
1202 CREATE TYPE "event_type" AS ENUM (
1203 'issue_state_changed',
1204 'initiative_created_in_new_issue',
1205 'initiative_created_in_existing_issue',
1206 'initiative_revoked',
1207 'new_draft_created',
1208 'suggestion_created');
1210 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
1213 CREATE TABLE "event" (
1214 "id" SERIAL8 PRIMARY KEY,
1215 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
1216 "event" "event_type" NOT NULL,
1217 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1218 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1219 "state" "issue_state",
1220 "initiative_id" INT4,
1221 "draft_id" INT8,
1222 "suggestion_id" INT8,
1223 FOREIGN KEY ("issue_id", "initiative_id")
1224 REFERENCES "initiative" ("issue_id", "id")
1225 ON DELETE CASCADE ON UPDATE CASCADE,
1226 FOREIGN KEY ("initiative_id", "draft_id")
1227 REFERENCES "draft" ("initiative_id", "id")
1228 ON DELETE CASCADE ON UPDATE CASCADE,
1229 FOREIGN KEY ("initiative_id", "suggestion_id")
1230 REFERENCES "suggestion" ("initiative_id", "id")
1231 ON DELETE CASCADE ON UPDATE CASCADE,
1232 CONSTRAINT "null_constr_for_issue_state_changed" CHECK (
1233 "event" != 'issue_state_changed' OR (
1234 "member_id" ISNULL AND
1235 "issue_id" NOTNULL AND
1236 "state" NOTNULL AND
1237 "initiative_id" ISNULL AND
1238 "draft_id" ISNULL AND
1239 "suggestion_id" ISNULL )),
1240 CONSTRAINT "null_constr_for_initiative_creation_or_revocation_or_new_draft" CHECK (
1241 "event" NOT IN (
1242 'initiative_created_in_new_issue',
1243 'initiative_created_in_existing_issue',
1244 'initiative_revoked',
1245 'new_draft_created'
1246 ) OR (
1247 "member_id" NOTNULL AND
1248 "issue_id" NOTNULL AND
1249 "state" NOTNULL AND
1250 "initiative_id" NOTNULL AND
1251 "draft_id" NOTNULL AND
1252 "suggestion_id" ISNULL )),
1253 CONSTRAINT "null_constr_for_suggestion_creation" CHECK (
1254 "event" != 'suggestion_created' OR (
1255 "member_id" NOTNULL AND
1256 "issue_id" NOTNULL AND
1257 "state" NOTNULL AND
1258 "initiative_id" NOTNULL AND
1259 "draft_id" ISNULL AND
1260 "suggestion_id" NOTNULL )) );
1261 CREATE INDEX "event_occurrence_idx" ON "event" ("occurrence");
1263 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
1265 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
1266 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';
1267 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';
1268 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';
1271 CREATE TABLE "notification_sent" (
1272 "event_id" INT8 NOT NULL );
1273 CREATE UNIQUE INDEX "notification_sent_singleton_idx" ON "notification_sent" ((1));
1275 COMMENT ON TABLE "notification_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out';
1276 COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.';
1279 CREATE TABLE "advertisement" ( -- TODO: two tables: one for initiatives, one for suggestions
1280 PRIMARY KEY ("time_serial", "initiative_id", "member_id"),
1281 "time_serial" SERIAL8,
1282 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1283 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
1284 CREATE INDEX "advertisement_initiative_id_idx" ON "advertisement" ("initiative_id");
1285 CREATE INDEX "advertisement_member_id_idx" ON "advertisement" ("member_id");
1287 COMMENT ON TABLE "advertisement" IS 'Stores which initiatives have been advertised to a member in a mail digest';
1289 COMMENT ON COLUMN "advertisement"."time_serial" IS 'An increasing integer that may be used to determine which issue or initiative was advertised the longest ago (may be equal or consecutive for those initiatives that are sent in the same notification)';
1293 ----------------------------------------------
1294 -- Writing of history entries and event log --
1295 ----------------------------------------------
1298 CREATE FUNCTION "write_member_history_trigger"()
1299 RETURNS TRIGGER
1300 LANGUAGE 'plpgsql' VOLATILE AS $$
1301 BEGIN
1302 IF
1303 ( NEW."active" != OLD."active" OR
1304 NEW."name" != OLD."name" ) AND
1305 OLD."activated" NOTNULL
1306 THEN
1307 INSERT INTO "member_history"
1308 ("member_id", "active", "name")
1309 VALUES (NEW."id", OLD."active", OLD."name");
1310 END IF;
1311 RETURN NULL;
1312 END;
1313 $$;
1315 CREATE TRIGGER "write_member_history"
1316 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1317 "write_member_history_trigger"();
1319 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
1320 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
1323 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
1324 RETURNS TRIGGER
1325 LANGUAGE 'plpgsql' VOLATILE AS $$
1326 BEGIN
1327 IF NEW."state" != OLD."state" THEN
1328 INSERT INTO "event" ("event", "issue_id", "state")
1329 VALUES ('issue_state_changed', NEW."id", NEW."state");
1330 END IF;
1331 RETURN NULL;
1332 END;
1333 $$;
1335 CREATE TRIGGER "write_event_issue_state_changed"
1336 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
1337 "write_event_issue_state_changed_trigger"();
1339 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
1340 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
1343 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1344 RETURNS TRIGGER
1345 LANGUAGE 'plpgsql' VOLATILE AS $$
1346 DECLARE
1347 "initiative_row" "initiative"%ROWTYPE;
1348 "issue_row" "issue"%ROWTYPE;
1349 "event_v" "event_type";
1350 BEGIN
1351 SELECT * INTO "initiative_row" FROM "initiative"
1352 WHERE "id" = NEW."initiative_id";
1353 SELECT * INTO "issue_row" FROM "issue"
1354 WHERE "id" = "initiative_row"."issue_id";
1355 IF EXISTS (
1356 SELECT NULL FROM "draft"
1357 WHERE "initiative_id" = NEW."initiative_id"
1358 AND "id" != NEW."id"
1359 ) THEN
1360 "event_v" := 'new_draft_created';
1361 ELSE
1362 IF EXISTS (
1363 SELECT NULL FROM "initiative"
1364 WHERE "issue_id" = "initiative_row"."issue_id"
1365 AND "id" != "initiative_row"."id"
1366 ) THEN
1367 "event_v" := 'initiative_created_in_existing_issue';
1368 ELSE
1369 "event_v" := 'initiative_created_in_new_issue';
1370 END IF;
1371 END IF;
1372 INSERT INTO "event" (
1373 "event", "member_id",
1374 "issue_id", "state", "initiative_id", "draft_id"
1375 ) VALUES (
1376 "event_v",
1377 NEW."author_id",
1378 "initiative_row"."issue_id",
1379 "issue_row"."state",
1380 "initiative_row"."id",
1381 NEW."id" );
1382 RETURN NULL;
1383 END;
1384 $$;
1386 CREATE TRIGGER "write_event_initiative_or_draft_created"
1387 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
1388 "write_event_initiative_or_draft_created_trigger"();
1390 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
1391 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
1394 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
1395 RETURNS TRIGGER
1396 LANGUAGE 'plpgsql' VOLATILE AS $$
1397 DECLARE
1398 "issue_row" "issue"%ROWTYPE;
1399 "draft_id_v" "draft"."id"%TYPE;
1400 BEGIN
1401 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1402 SELECT * INTO "issue_row" FROM "issue"
1403 WHERE "id" = NEW."issue_id";
1404 SELECT "id" INTO "draft_id_v" FROM "current_draft"
1405 WHERE "initiative_id" = NEW."id";
1406 INSERT INTO "event" (
1407 "event", "member_id", "issue_id", "state", "initiative_id", "draft_id"
1408 ) VALUES (
1409 'initiative_revoked',
1410 NEW."revoked_by_member_id",
1411 NEW."issue_id",
1412 "issue_row"."state",
1413 NEW."id",
1414 "draft_id_v");
1415 END IF;
1416 RETURN NULL;
1417 END;
1418 $$;
1420 CREATE TRIGGER "write_event_initiative_revoked"
1421 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
1422 "write_event_initiative_revoked_trigger"();
1424 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
1425 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
1428 CREATE FUNCTION "write_event_suggestion_created_trigger"()
1429 RETURNS TRIGGER
1430 LANGUAGE 'plpgsql' VOLATILE AS $$
1431 DECLARE
1432 "initiative_row" "initiative"%ROWTYPE;
1433 "issue_row" "issue"%ROWTYPE;
1434 BEGIN
1435 SELECT * INTO "initiative_row" FROM "initiative"
1436 WHERE "id" = NEW."initiative_id";
1437 SELECT * INTO "issue_row" FROM "issue"
1438 WHERE "id" = "initiative_row"."issue_id";
1439 INSERT INTO "event" (
1440 "event", "member_id",
1441 "issue_id", "state", "initiative_id", "suggestion_id"
1442 ) VALUES (
1443 'suggestion_created',
1444 NEW."author_id",
1445 "initiative_row"."issue_id",
1446 "issue_row"."state",
1447 "initiative_row"."id",
1448 NEW."id" );
1449 RETURN NULL;
1450 END;
1451 $$;
1453 CREATE TRIGGER "write_event_suggestion_created"
1454 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1455 "write_event_suggestion_created_trigger"();
1457 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
1458 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1462 ----------------------------
1463 -- Additional constraints --
1464 ----------------------------
1467 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
1468 RETURNS TRIGGER
1469 LANGUAGE 'plpgsql' VOLATILE AS $$
1470 BEGIN
1471 IF NOT EXISTS (
1472 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
1473 ) THEN
1474 RAISE EXCEPTION 'Cannot create issue without an initial initiative.' USING
1475 ERRCODE = 'integrity_constraint_violation',
1476 HINT = 'Create issue, initiative, and draft within the same transaction.';
1477 END IF;
1478 RETURN NULL;
1479 END;
1480 $$;
1482 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
1483 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
1484 FOR EACH ROW EXECUTE PROCEDURE
1485 "issue_requires_first_initiative_trigger"();
1487 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
1488 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
1491 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
1492 RETURNS TRIGGER
1493 LANGUAGE 'plpgsql' VOLATILE AS $$
1494 DECLARE
1495 "reference_lost" BOOLEAN;
1496 BEGIN
1497 IF TG_OP = 'DELETE' THEN
1498 "reference_lost" := TRUE;
1499 ELSE
1500 "reference_lost" := NEW."issue_id" != OLD."issue_id";
1501 END IF;
1502 IF
1503 "reference_lost" AND NOT EXISTS (
1504 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
1505 )
1506 THEN
1507 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
1508 END IF;
1509 RETURN NULL;
1510 END;
1511 $$;
1513 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
1514 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1515 FOR EACH ROW EXECUTE PROCEDURE
1516 "last_initiative_deletes_issue_trigger"();
1518 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
1519 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
1522 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
1523 RETURNS TRIGGER
1524 LANGUAGE 'plpgsql' VOLATILE AS $$
1525 BEGIN
1526 IF NOT EXISTS (
1527 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
1528 ) THEN
1529 RAISE EXCEPTION 'Cannot create initiative without an initial draft.' USING
1530 ERRCODE = 'integrity_constraint_violation',
1531 HINT = 'Create issue, initiative and draft within the same transaction.';
1532 END IF;
1533 RETURN NULL;
1534 END;
1535 $$;
1537 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
1538 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1539 FOR EACH ROW EXECUTE PROCEDURE
1540 "initiative_requires_first_draft_trigger"();
1542 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
1543 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
1546 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
1547 RETURNS TRIGGER
1548 LANGUAGE 'plpgsql' VOLATILE AS $$
1549 DECLARE
1550 "reference_lost" BOOLEAN;
1551 BEGIN
1552 IF TG_OP = 'DELETE' THEN
1553 "reference_lost" := TRUE;
1554 ELSE
1555 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
1556 END IF;
1557 IF
1558 "reference_lost" AND NOT EXISTS (
1559 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
1560 )
1561 THEN
1562 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
1563 END IF;
1564 RETURN NULL;
1565 END;
1566 $$;
1568 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
1569 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
1570 FOR EACH ROW EXECUTE PROCEDURE
1571 "last_draft_deletes_initiative_trigger"();
1573 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
1574 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
1577 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
1578 RETURNS TRIGGER
1579 LANGUAGE 'plpgsql' VOLATILE AS $$
1580 BEGIN
1581 IF NOT EXISTS (
1582 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
1583 ) THEN
1584 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.' USING
1585 ERRCODE = 'integrity_constraint_violation',
1586 HINT = 'Create suggestion and opinion within the same transaction.';
1587 END IF;
1588 RETURN NULL;
1589 END;
1590 $$;
1592 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
1593 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
1594 FOR EACH ROW EXECUTE PROCEDURE
1595 "suggestion_requires_first_opinion_trigger"();
1597 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1598 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1601 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1602 RETURNS TRIGGER
1603 LANGUAGE 'plpgsql' VOLATILE AS $$
1604 DECLARE
1605 "reference_lost" BOOLEAN;
1606 BEGIN
1607 IF TG_OP = 'DELETE' THEN
1608 "reference_lost" := TRUE;
1609 ELSE
1610 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1611 END IF;
1612 IF
1613 "reference_lost" AND NOT EXISTS (
1614 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1615 )
1616 THEN
1617 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1618 END IF;
1619 RETURN NULL;
1620 END;
1621 $$;
1623 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1624 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1625 FOR EACH ROW EXECUTE PROCEDURE
1626 "last_opinion_deletes_suggestion_trigger"();
1628 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1629 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1632 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()
1633 RETURNS TRIGGER
1634 LANGUAGE 'plpgsql' VOLATILE AS $$
1635 BEGIN
1636 DELETE FROM "direct_voter"
1637 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
1638 RETURN NULL;
1639 END;
1640 $$;
1642 CREATE TRIGGER "non_voter_deletes_direct_voter"
1643 AFTER INSERT OR UPDATE ON "non_voter"
1644 FOR EACH ROW EXECUTE PROCEDURE
1645 "non_voter_deletes_direct_voter_trigger"();
1647 COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"';
1648 COMMENT ON TRIGGER "non_voter_deletes_direct_voter" ON "non_voter" IS 'An entry in the "non_voter" table deletes an entry in the "direct_voter" table (and vice versa due to trigger "direct_voter_deletes_non_voter" on table "direct_voter")';
1651 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()
1652 RETURNS TRIGGER
1653 LANGUAGE 'plpgsql' VOLATILE AS $$
1654 BEGIN
1655 DELETE FROM "non_voter"
1656 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
1657 RETURN NULL;
1658 END;
1659 $$;
1661 CREATE TRIGGER "direct_voter_deletes_non_voter"
1662 AFTER INSERT OR UPDATE ON "direct_voter"
1663 FOR EACH ROW EXECUTE PROCEDURE
1664 "direct_voter_deletes_non_voter_trigger"();
1666 COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"';
1667 COMMENT ON TRIGGER "direct_voter_deletes_non_voter" ON "direct_voter" IS 'An entry in the "direct_voter" table deletes an entry in the "non_voter" table (and vice versa due to trigger "non_voter_deletes_direct_voter" on table "non_voter")';
1670 CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()
1671 RETURNS TRIGGER
1672 LANGUAGE 'plpgsql' VOLATILE AS $$
1673 BEGIN
1674 IF NEW."comment" ISNULL THEN
1675 NEW."comment_changed" := NULL;
1676 NEW."formatting_engine" := NULL;
1677 END IF;
1678 RETURN NEW;
1679 END;
1680 $$;
1682 CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"
1683 BEFORE INSERT OR UPDATE ON "direct_voter"
1684 FOR EACH ROW EXECUTE PROCEDURE
1685 "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();
1687 COMMENT ON FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"() IS 'Implementation of trigger "voter_comment_fields_only_set_when_voter_comment_is_set" ON table "direct_voter"';
1688 COMMENT ON TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set" ON "direct_voter" IS 'If "comment" is set to NULL, then other comment related fields are also set to NULL.';
1691 ---------------------------------------------------------------
1692 -- Ensure that votes are not modified when issues are closed --
1693 ---------------------------------------------------------------
1695 -- NOTE: Frontends should ensure this anyway, but in case of programming
1696 -- errors the following triggers ensure data integrity.
1699 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1700 RETURNS TRIGGER
1701 LANGUAGE 'plpgsql' VOLATILE AS $$
1702 DECLARE
1703 "issue_id_v" "issue"."id"%TYPE;
1704 "issue_row" "issue"%ROWTYPE;
1705 BEGIN
1706 IF EXISTS (
1707 SELECT NULL FROM "temporary_transaction_data"
1708 WHERE "txid" = txid_current()
1709 AND "key" = 'override_protection_triggers'
1710 AND "value" = TRUE::TEXT
1711 ) THEN
1712 RETURN NULL;
1713 END IF;
1714 IF TG_OP = 'DELETE' THEN
1715 "issue_id_v" := OLD."issue_id";
1716 ELSE
1717 "issue_id_v" := NEW."issue_id";
1718 END IF;
1719 SELECT INTO "issue_row" * FROM "issue"
1720 WHERE "id" = "issue_id_v" FOR SHARE;
1721 IF (
1722 "issue_row"."closed" NOTNULL OR (
1723 "issue_row"."state" = 'voting' AND
1724 "issue_row"."phase_finished" NOTNULL
1725 )
1726 ) THEN
1727 IF
1728 TG_RELID = 'direct_voter'::regclass AND
1729 TG_OP = 'UPDATE'
1730 THEN
1731 IF
1732 OLD."issue_id" = NEW."issue_id" AND
1733 OLD."member_id" = NEW."member_id" AND
1734 OLD."weight" = NEW."weight"
1735 THEN
1736 RETURN NULL; -- allows changing of voter comment
1737 END IF;
1738 END IF;
1739 RAISE EXCEPTION 'Tried to modify data after voting has been closed.' USING
1740 ERRCODE = 'integrity_constraint_violation';
1741 END IF;
1742 RETURN NULL;
1743 END;
1744 $$;
1746 CREATE TRIGGER "forbid_changes_on_closed_issue"
1747 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1748 FOR EACH ROW EXECUTE PROCEDURE
1749 "forbid_changes_on_closed_issue_trigger"();
1751 CREATE TRIGGER "forbid_changes_on_closed_issue"
1752 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1753 FOR EACH ROW EXECUTE PROCEDURE
1754 "forbid_changes_on_closed_issue_trigger"();
1756 CREATE TRIGGER "forbid_changes_on_closed_issue"
1757 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1758 FOR EACH ROW EXECUTE PROCEDURE
1759 "forbid_changes_on_closed_issue_trigger"();
1761 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"';
1762 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';
1763 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';
1764 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';
1768 --------------------------------------------------------------------
1769 -- Auto-retrieval of fields only needed for referential integrity --
1770 --------------------------------------------------------------------
1773 CREATE FUNCTION "autofill_issue_id_trigger"()
1774 RETURNS TRIGGER
1775 LANGUAGE 'plpgsql' VOLATILE AS $$
1776 BEGIN
1777 IF NEW."issue_id" ISNULL THEN
1778 SELECT "issue_id" INTO NEW."issue_id"
1779 FROM "initiative" WHERE "id" = NEW."initiative_id";
1780 END IF;
1781 RETURN NEW;
1782 END;
1783 $$;
1785 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1786 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1788 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1789 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1791 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1792 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1793 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1796 CREATE FUNCTION "autofill_initiative_id_trigger"()
1797 RETURNS TRIGGER
1798 LANGUAGE 'plpgsql' VOLATILE AS $$
1799 BEGIN
1800 IF NEW."initiative_id" ISNULL THEN
1801 SELECT "initiative_id" INTO NEW."initiative_id"
1802 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1803 END IF;
1804 RETURN NEW;
1805 END;
1806 $$;
1808 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1809 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1811 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1812 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1816 -----------------------------------------------------
1817 -- Automatic calculation of certain default values --
1818 -----------------------------------------------------
1821 CREATE FUNCTION "copy_timings_trigger"()
1822 RETURNS TRIGGER
1823 LANGUAGE 'plpgsql' VOLATILE AS $$
1824 DECLARE
1825 "policy_row" "policy"%ROWTYPE;
1826 BEGIN
1827 SELECT * INTO "policy_row" FROM "policy"
1828 WHERE "id" = NEW."policy_id";
1829 IF NEW."min_admission_time" ISNULL THEN
1830 NEW."min_admission_time" := "policy_row"."min_admission_time";
1831 END IF;
1832 IF NEW."max_admission_time" ISNULL THEN
1833 NEW."max_admission_time" := "policy_row"."max_admission_time";
1834 END IF;
1835 IF NEW."discussion_time" ISNULL THEN
1836 NEW."discussion_time" := "policy_row"."discussion_time";
1837 END IF;
1838 IF NEW."verification_time" ISNULL THEN
1839 NEW."verification_time" := "policy_row"."verification_time";
1840 END IF;
1841 IF NEW."voting_time" ISNULL THEN
1842 NEW."voting_time" := "policy_row"."voting_time";
1843 END IF;
1844 RETURN NEW;
1845 END;
1846 $$;
1848 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1849 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1851 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1852 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1855 CREATE FUNCTION "default_for_draft_id_trigger"()
1856 RETURNS TRIGGER
1857 LANGUAGE 'plpgsql' VOLATILE AS $$
1858 BEGIN
1859 IF NEW."draft_id" ISNULL THEN
1860 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1861 WHERE "initiative_id" = NEW."initiative_id";
1862 END IF;
1863 RETURN NEW;
1864 END;
1865 $$;
1867 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"
1868 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1869 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1870 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1872 COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';
1873 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';
1874 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';
1878 ----------------------------------------
1879 -- Automatic creation of dependencies --
1880 ----------------------------------------
1883 CREATE FUNCTION "autocreate_interest_trigger"()
1884 RETURNS TRIGGER
1885 LANGUAGE 'plpgsql' VOLATILE AS $$
1886 BEGIN
1887 IF NOT EXISTS (
1888 SELECT NULL FROM "initiative" JOIN "interest"
1889 ON "initiative"."issue_id" = "interest"."issue_id"
1890 WHERE "initiative"."id" = NEW."initiative_id"
1891 AND "interest"."member_id" = NEW."member_id"
1892 ) THEN
1893 BEGIN
1894 INSERT INTO "interest" ("issue_id", "member_id")
1895 SELECT "issue_id", NEW."member_id"
1896 FROM "initiative" WHERE "id" = NEW."initiative_id";
1897 EXCEPTION WHEN unique_violation THEN END;
1898 END IF;
1899 RETURN NEW;
1900 END;
1901 $$;
1903 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1904 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1906 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1907 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';
1910 CREATE FUNCTION "autocreate_supporter_trigger"()
1911 RETURNS TRIGGER
1912 LANGUAGE 'plpgsql' VOLATILE AS $$
1913 BEGIN
1914 IF NOT EXISTS (
1915 SELECT NULL FROM "suggestion" JOIN "supporter"
1916 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1917 WHERE "suggestion"."id" = NEW."suggestion_id"
1918 AND "supporter"."member_id" = NEW."member_id"
1919 ) THEN
1920 BEGIN
1921 INSERT INTO "supporter" ("initiative_id", "member_id")
1922 SELECT "initiative_id", NEW."member_id"
1923 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1924 EXCEPTION WHEN unique_violation THEN END;
1925 END IF;
1926 RETURN NEW;
1927 END;
1928 $$;
1930 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1931 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1933 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1934 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.';
1938 ------------------------------------------
1939 -- Views and helper functions for views --
1940 ------------------------------------------
1943 CREATE VIEW "unit_delegation" AS
1944 SELECT
1945 "unit"."id" AS "unit_id",
1946 "delegation"."id",
1947 "delegation"."truster_id",
1948 "delegation"."trustee_id",
1949 "delegation"."scope"
1950 FROM "unit"
1951 JOIN "delegation"
1952 ON "delegation"."unit_id" = "unit"."id"
1953 JOIN "member"
1954 ON "delegation"."truster_id" = "member"."id"
1955 JOIN "privilege"
1956 ON "delegation"."unit_id" = "privilege"."unit_id"
1957 AND "delegation"."truster_id" = "privilege"."member_id"
1958 WHERE "member"."active" AND "privilege"."voting_right";
1960 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
1963 CREATE VIEW "area_delegation" AS
1964 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1965 "area"."id" AS "area_id",
1966 "delegation"."id",
1967 "delegation"."truster_id",
1968 "delegation"."trustee_id",
1969 "delegation"."scope"
1970 FROM "area"
1971 JOIN "delegation"
1972 ON "delegation"."unit_id" = "area"."unit_id"
1973 OR "delegation"."area_id" = "area"."id"
1974 JOIN "member"
1975 ON "delegation"."truster_id" = "member"."id"
1976 JOIN "privilege"
1977 ON "area"."unit_id" = "privilege"."unit_id"
1978 AND "delegation"."truster_id" = "privilege"."member_id"
1979 WHERE "member"."active" AND "privilege"."voting_right"
1980 ORDER BY
1981 "area"."id",
1982 "delegation"."truster_id",
1983 "delegation"."scope" DESC;
1985 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
1988 CREATE VIEW "issue_delegation" AS
1989 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1990 "issue"."id" AS "issue_id",
1991 "delegation"."id",
1992 "delegation"."truster_id",
1993 "delegation"."trustee_id",
1994 "delegation"."scope"
1995 FROM "issue"
1996 JOIN "area"
1997 ON "area"."id" = "issue"."area_id"
1998 JOIN "delegation"
1999 ON "delegation"."unit_id" = "area"."unit_id"
2000 OR "delegation"."area_id" = "area"."id"
2001 OR "delegation"."issue_id" = "issue"."id"
2002 JOIN "member"
2003 ON "delegation"."truster_id" = "member"."id"
2004 JOIN "privilege"
2005 ON "area"."unit_id" = "privilege"."unit_id"
2006 AND "delegation"."truster_id" = "privilege"."member_id"
2007 WHERE "member"."active" AND "privilege"."voting_right"
2008 ORDER BY
2009 "issue"."id",
2010 "delegation"."truster_id",
2011 "delegation"."scope" DESC;
2013 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
2016 CREATE FUNCTION "membership_weight_with_skipping"
2017 ( "area_id_p" "area"."id"%TYPE,
2018 "member_id_p" "member"."id"%TYPE,
2019 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
2020 RETURNS INT4
2021 LANGUAGE 'plpgsql' STABLE AS $$
2022 DECLARE
2023 "sum_v" INT4;
2024 "delegation_row" "area_delegation"%ROWTYPE;
2025 BEGIN
2026 "sum_v" := 1;
2027 FOR "delegation_row" IN
2028 SELECT "area_delegation".*
2029 FROM "area_delegation" LEFT JOIN "membership"
2030 ON "membership"."area_id" = "area_id_p"
2031 AND "membership"."member_id" = "area_delegation"."truster_id"
2032 WHERE "area_delegation"."area_id" = "area_id_p"
2033 AND "area_delegation"."trustee_id" = "member_id_p"
2034 AND "membership"."member_id" ISNULL
2035 LOOP
2036 IF NOT
2037 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
2038 THEN
2039 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
2040 "area_id_p",
2041 "delegation_row"."truster_id",
2042 "skip_member_ids_p" || "delegation_row"."truster_id"
2043 );
2044 END IF;
2045 END LOOP;
2046 RETURN "sum_v";
2047 END;
2048 $$;
2050 COMMENT ON FUNCTION "membership_weight_with_skipping"
2051 ( "area"."id"%TYPE,
2052 "member"."id"%TYPE,
2053 INT4[] )
2054 IS 'Helper function for "membership_weight" function';
2057 CREATE FUNCTION "membership_weight"
2058 ( "area_id_p" "area"."id"%TYPE,
2059 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
2060 RETURNS INT4
2061 LANGUAGE 'plpgsql' STABLE AS $$
2062 BEGIN
2063 RETURN "membership_weight_with_skipping"(
2064 "area_id_p",
2065 "member_id_p",
2066 ARRAY["member_id_p"]
2067 );
2068 END;
2069 $$;
2071 COMMENT ON FUNCTION "membership_weight"
2072 ( "area"."id"%TYPE,
2073 "member"."id"%TYPE )
2074 IS 'Calculates the potential voting weight of a member in a given area';
2077 CREATE VIEW "member_count_view" AS
2078 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
2080 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
2083 CREATE VIEW "unit_member_count" AS
2084 SELECT
2085 "unit"."id" AS "unit_id",
2086 count("member"."id") AS "member_count"
2087 FROM "unit"
2088 LEFT JOIN "privilege"
2089 ON "privilege"."unit_id" = "unit"."id"
2090 AND "privilege"."voting_right"
2091 LEFT JOIN "member"
2092 ON "member"."id" = "privilege"."member_id"
2093 AND "member"."active"
2094 GROUP BY "unit"."id";
2096 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
2099 CREATE VIEW "area_member_count" AS
2100 SELECT
2101 "area"."id" AS "area_id",
2102 count("member"."id") AS "direct_member_count",
2103 coalesce(
2104 sum(
2105 CASE WHEN "member"."id" NOTNULL THEN
2106 "membership_weight"("area"."id", "member"."id")
2107 ELSE 0 END
2108 )
2109 ) AS "member_weight"
2110 FROM "area"
2111 LEFT JOIN "membership"
2112 ON "area"."id" = "membership"."area_id"
2113 LEFT JOIN "privilege"
2114 ON "privilege"."unit_id" = "area"."unit_id"
2115 AND "privilege"."member_id" = "membership"."member_id"
2116 AND "privilege"."voting_right"
2117 LEFT JOIN "member"
2118 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
2119 AND "member"."active"
2120 GROUP BY "area"."id";
2122 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"';
2125 CREATE VIEW "opening_draft" AS
2126 SELECT "draft".* FROM (
2127 SELECT
2128 "initiative"."id" AS "initiative_id",
2129 min("draft"."id") AS "draft_id"
2130 FROM "initiative" JOIN "draft"
2131 ON "initiative"."id" = "draft"."initiative_id"
2132 GROUP BY "initiative"."id"
2133 ) AS "subquery"
2134 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
2136 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
2139 CREATE VIEW "current_draft" AS
2140 SELECT "draft".* FROM (
2141 SELECT
2142 "initiative"."id" AS "initiative_id",
2143 max("draft"."id") AS "draft_id"
2144 FROM "initiative" JOIN "draft"
2145 ON "initiative"."id" = "draft"."initiative_id"
2146 GROUP BY "initiative"."id"
2147 ) AS "subquery"
2148 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
2150 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
2153 CREATE VIEW "critical_opinion" AS
2154 SELECT * FROM "opinion"
2155 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
2156 OR ("degree" = -2 AND "fulfilled" = TRUE);
2158 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
2161 CREATE VIEW "issue_supporter_in_admission_state" AS
2162 SELECT DISTINCT -- TODO: DISTINCT needed?
2163 "area"."unit_id",
2164 "issue"."area_id",
2165 "issue"."id" AS "issue_id",
2166 "supporter"."member_id",
2167 "direct_interest_snapshot"."weight"
2168 FROM "issue"
2169 JOIN "area" ON "area"."id" = "issue"."area_id"
2170 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
2171 JOIN "direct_interest_snapshot"
2172 ON "direct_interest_snapshot"."issue_id" = "issue"."id"
2173 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
2174 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
2175 WHERE "issue"."state" = 'admission'::"issue_state";
2177 COMMENT ON VIEW "issue_supporter_in_admission_state" IS 'Helper view for "lf_update_issue_order" to allow a (proportional) ordering of issues within an area';
2180 CREATE VIEW "initiative_suggestion_order_calculation" AS
2181 SELECT
2182 "initiative"."id" AS "initiative_id",
2183 ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final"
2184 FROM "initiative" JOIN "issue"
2185 ON "initiative"."issue_id" = "issue"."id"
2186 WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL)
2187 OR ("initiative"."final_suggestion_order_calculated" = FALSE);
2189 COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated';
2191 COMMENT ON COLUMN "initiative_suggestion_order_calculation"."final" IS 'Set to TRUE, if the issue is fully frozen or closed, and the calculation has to be done only once for one last time';
2194 CREATE VIEW "individual_suggestion_ranking" AS
2195 SELECT
2196 "opinion"."initiative_id",
2197 "opinion"."member_id",
2198 "direct_interest_snapshot"."weight",
2199 CASE WHEN
2200 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
2201 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
2202 THEN 1 ELSE
2203 CASE WHEN
2204 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
2205 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
2206 THEN 2 ELSE
2207 CASE WHEN
2208 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
2209 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
2210 THEN 3 ELSE 4 END
2211 END
2212 END AS "preference",
2213 "opinion"."suggestion_id"
2214 FROM "opinion"
2215 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
2216 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2217 JOIN "direct_interest_snapshot"
2218 ON "direct_interest_snapshot"."issue_id" = "issue"."id"
2219 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
2220 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
2222 COMMENT ON VIEW "individual_suggestion_ranking" IS 'Helper view for "lf_update_suggestion_order" to allow a proportional ordering of suggestions within an initiative';
2225 CREATE VIEW "battle_participant" AS
2226 SELECT "initiative"."id", "initiative"."issue_id"
2227 FROM "issue" JOIN "initiative"
2228 ON "issue"."id" = "initiative"."issue_id"
2229 WHERE "initiative"."admitted"
2230 UNION ALL
2231 SELECT NULL, "id" AS "issue_id"
2232 FROM "issue";
2234 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
2237 CREATE VIEW "battle_view" AS
2238 SELECT
2239 "issue"."id" AS "issue_id",
2240 "winning_initiative"."id" AS "winning_initiative_id",
2241 "losing_initiative"."id" AS "losing_initiative_id",
2242 sum(
2243 CASE WHEN
2244 coalesce("better_vote"."grade", 0) >
2245 coalesce("worse_vote"."grade", 0)
2246 THEN "direct_voter"."weight" ELSE 0 END
2247 ) AS "count"
2248 FROM "issue"
2249 LEFT JOIN "direct_voter"
2250 ON "issue"."id" = "direct_voter"."issue_id"
2251 JOIN "battle_participant" AS "winning_initiative"
2252 ON "issue"."id" = "winning_initiative"."issue_id"
2253 JOIN "battle_participant" AS "losing_initiative"
2254 ON "issue"."id" = "losing_initiative"."issue_id"
2255 LEFT JOIN "vote" AS "better_vote"
2256 ON "direct_voter"."member_id" = "better_vote"."member_id"
2257 AND "winning_initiative"."id" = "better_vote"."initiative_id"
2258 LEFT JOIN "vote" AS "worse_vote"
2259 ON "direct_voter"."member_id" = "worse_vote"."member_id"
2260 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
2261 WHERE "issue"."state" = 'voting'
2262 AND "issue"."phase_finished" NOTNULL
2263 AND (
2264 "winning_initiative"."id" != "losing_initiative"."id" OR
2265 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
2266 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
2267 GROUP BY
2268 "issue"."id",
2269 "winning_initiative"."id",
2270 "losing_initiative"."id";
2272 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';
2275 CREATE VIEW "expired_session" AS
2276 SELECT * FROM "session" WHERE now() > "expiry";
2278 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
2279 DELETE FROM "session" WHERE "ident" = OLD."ident";
2281 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
2282 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
2285 CREATE VIEW "open_issue" AS
2286 SELECT * FROM "issue" WHERE "closed" ISNULL;
2288 COMMENT ON VIEW "open_issue" IS 'All open issues';
2291 CREATE VIEW "member_contingent" AS
2292 SELECT
2293 "member"."id" AS "member_id",
2294 "contingent"."polling",
2295 "contingent"."time_frame",
2296 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
2297 (
2298 SELECT count(1) FROM "draft"
2299 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
2300 WHERE "draft"."author_id" = "member"."id"
2301 AND "initiative"."polling" = "contingent"."polling"
2302 AND "draft"."created" > now() - "contingent"."time_frame"
2303 ) + (
2304 SELECT count(1) FROM "suggestion"
2305 JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"
2306 WHERE "suggestion"."author_id" = "member"."id"
2307 AND "contingent"."polling" = FALSE
2308 AND "suggestion"."created" > now() - "contingent"."time_frame"
2309 )
2310 ELSE NULL END AS "text_entry_count",
2311 "contingent"."text_entry_limit",
2312 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
2313 SELECT count(1) FROM "opening_draft" AS "draft"
2314 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
2315 WHERE "draft"."author_id" = "member"."id"
2316 AND "initiative"."polling" = "contingent"."polling"
2317 AND "draft"."created" > now() - "contingent"."time_frame"
2318 ) ELSE NULL END AS "initiative_count",
2319 "contingent"."initiative_limit"
2320 FROM "member" CROSS JOIN "contingent";
2322 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
2324 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
2325 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
2328 CREATE VIEW "member_contingent_left" AS
2329 SELECT
2330 "member_id",
2331 "polling",
2332 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
2333 max("initiative_limit" - "initiative_count") AS "initiatives_left"
2334 FROM "member_contingent" GROUP BY "member_id", "polling";
2336 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.';
2339 CREATE VIEW "event_seen_by_member" AS
2340 SELECT
2341 "member"."id" AS "seen_by_member_id",
2342 CASE WHEN "event"."state" IN (
2343 'voting',
2344 'finished_without_winner',
2345 'finished_with_winner'
2346 ) THEN
2347 'voting'::"notify_level"
2348 ELSE
2349 CASE WHEN "event"."state" IN (
2350 'verification',
2351 'canceled_after_revocation_during_verification',
2352 'canceled_no_initiative_admitted'
2353 ) THEN
2354 'verification'::"notify_level"
2355 ELSE
2356 CASE WHEN "event"."state" IN (
2357 'discussion',
2358 'canceled_after_revocation_during_discussion'
2359 ) THEN
2360 'discussion'::"notify_level"
2361 ELSE
2362 'all'::"notify_level"
2363 END
2364 END
2365 END AS "notify_level",
2366 "event".*
2367 FROM "member" CROSS JOIN "event"
2368 LEFT JOIN "issue"
2369 ON "event"."issue_id" = "issue"."id"
2370 LEFT JOIN "membership"
2371 ON "member"."id" = "membership"."member_id"
2372 AND "issue"."area_id" = "membership"."area_id"
2373 LEFT JOIN "interest"
2374 ON "member"."id" = "interest"."member_id"
2375 AND "event"."issue_id" = "interest"."issue_id"
2376 LEFT JOIN "ignored_member"
2377 ON "member"."id" = "ignored_member"."member_id"
2378 AND "event"."member_id" = "ignored_member"."other_member_id"
2379 LEFT JOIN "ignored_initiative"
2380 ON "member"."id" = "ignored_initiative"."member_id"
2381 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2382 WHERE (
2383 "interest"."member_id" NOTNULL OR
2384 ( "membership"."member_id" NOTNULL AND
2385 "event"."event" IN (
2386 'issue_state_changed',
2387 'initiative_created_in_new_issue',
2388 'initiative_created_in_existing_issue',
2389 'initiative_revoked' ) ) )
2390 AND "ignored_member"."member_id" ISNULL
2391 AND "ignored_initiative"."member_id" ISNULL;
2393 COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support, but ignoring members "notify_level"';
2396 CREATE VIEW "issue_for_notification" AS
2397 SELECT
2398 "member"."id" AS "member_id",
2399 "issue"."id" AS "issue_id"
2400 FROM "member" CROSS JOIN "issue"
2401 JOIN "area" ON "area"."id" = "issue"."area_id"
2402 LEFT JOIN "privilege"
2403 ON "privilege"."member_id" = "member"."id"
2404 AND "privilege"."unit_id" = "area"."unit_id"
2405 LEFT JOIN "subscription"
2406 ON "subscription"."member_id" = "member"."id"
2407 AND "subscription"."unit_id" = "area"."unit_id"
2408 LEFT JOIN "interest"
2409 ON "interest"."member_id" = "member"."id"
2410 AND "interest"."issue_id" = "issue"."id"
2411 LEFT JOIN "ignored_area"
2412 ON "ignored_area"."member_id" = "member"."id"
2413 AND "ignored_area"."area_id" = "issue"."area_id"
2414 WHERE
2415 ( "privilege"."initiative_right" OR "privilege"."voting_right" OR
2416 "subscription"."member_id" NOTNULL ) AND
2417 ( ( "issue"."state" IN ('admission', 'discussion', 'verification') AND
2418 "interest"."member_id" NOTNULL ) OR
2419 ( "issue"."state" IN ('discussion', 'verification') AND
2420 "ignored_area"."member_id" ISNULL ) ); -- TODO: add certain issues in admission phase
2422 COMMENT ON VIEW "issue_for_notification" IS 'Issues that are considered in notifications sent to the member';
2426 ------------------------------------------------------
2427 -- Row set returning function for delegation chains --
2428 ------------------------------------------------------
2431 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
2432 ('first', 'intermediate', 'last', 'repetition');
2434 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
2437 CREATE TYPE "delegation_chain_row" AS (
2438 "index" INT4,
2439 "member_id" INT4,
2440 "member_valid" BOOLEAN,
2441 "participation" BOOLEAN,
2442 "overridden" BOOLEAN,
2443 "scope_in" "delegation_scope",
2444 "scope_out" "delegation_scope",
2445 "disabled_out" BOOLEAN,
2446 "loop" "delegation_chain_loop_tag" );
2448 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';
2450 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
2451 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';
2452 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
2453 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
2454 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
2455 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
2456 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
2459 CREATE FUNCTION "delegation_chain_for_closed_issue"
2460 ( "member_id_p" "member"."id"%TYPE,
2461 "issue_id_p" "issue"."id"%TYPE )
2462 RETURNS SETOF "delegation_chain_row"
2463 LANGUAGE 'plpgsql' STABLE AS $$
2464 DECLARE
2465 "output_row" "delegation_chain_row";
2466 "direct_voter_row" "direct_voter"%ROWTYPE;
2467 "delegating_voter_row" "delegating_voter"%ROWTYPE;
2468 BEGIN
2469 "output_row"."index" := 0;
2470 "output_row"."member_id" := "member_id_p";
2471 "output_row"."member_valid" := TRUE;
2472 "output_row"."participation" := FALSE;
2473 "output_row"."overridden" := FALSE;
2474 "output_row"."disabled_out" := FALSE;
2475 LOOP
2476 SELECT INTO "direct_voter_row" * FROM "direct_voter"
2477 WHERE "issue_id" = "issue_id_p"
2478 AND "member_id" = "output_row"."member_id";
2479 IF "direct_voter_row"."member_id" NOTNULL THEN
2480 "output_row"."participation" := TRUE;
2481 "output_row"."scope_out" := NULL;
2482 "output_row"."disabled_out" := NULL;
2483 RETURN NEXT "output_row";
2484 RETURN;
2485 END IF;
2486 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"
2487 WHERE "issue_id" = "issue_id_p"
2488 AND "member_id" = "output_row"."member_id";
2489 IF "delegating_voter_row"."member_id" ISNULL THEN
2490 RETURN;
2491 END IF;
2492 "output_row"."scope_out" := "delegating_voter_row"."scope";
2493 RETURN NEXT "output_row";
2494 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];
2495 "output_row"."scope_in" := "output_row"."scope_out";
2496 END LOOP;
2497 END;
2498 $$;
2500 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"
2501 ( "member"."id"%TYPE,
2502 "member"."id"%TYPE )
2503 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';
2506 CREATE FUNCTION "delegation_chain"
2507 ( "member_id_p" "member"."id"%TYPE,
2508 "unit_id_p" "unit"."id"%TYPE,
2509 "area_id_p" "area"."id"%TYPE,
2510 "issue_id_p" "issue"."id"%TYPE,
2511 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2512 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2513 RETURNS SETOF "delegation_chain_row"
2514 LANGUAGE 'plpgsql' STABLE AS $$
2515 DECLARE
2516 "scope_v" "delegation_scope";
2517 "unit_id_v" "unit"."id"%TYPE;
2518 "area_id_v" "area"."id"%TYPE;
2519 "issue_row" "issue"%ROWTYPE;
2520 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2521 "loop_member_id_v" "member"."id"%TYPE;
2522 "output_row" "delegation_chain_row";
2523 "output_rows" "delegation_chain_row"[];
2524 "simulate_v" BOOLEAN;
2525 "simulate_here_v" BOOLEAN;
2526 "delegation_row" "delegation"%ROWTYPE;
2527 "row_count" INT4;
2528 "i" INT4;
2529 "loop_v" BOOLEAN;
2530 BEGIN
2531 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
2532 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
2533 END IF;
2534 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
2535 "simulate_v" := TRUE;
2536 ELSE
2537 "simulate_v" := FALSE;
2538 END IF;
2539 IF
2540 "unit_id_p" NOTNULL AND
2541 "area_id_p" ISNULL AND
2542 "issue_id_p" ISNULL
2543 THEN
2544 "scope_v" := 'unit';
2545 "unit_id_v" := "unit_id_p";
2546 ELSIF
2547 "unit_id_p" ISNULL AND
2548 "area_id_p" NOTNULL AND
2549 "issue_id_p" ISNULL
2550 THEN
2551 "scope_v" := 'area';
2552 "area_id_v" := "area_id_p";
2553 SELECT "unit_id" INTO "unit_id_v"
2554 FROM "area" WHERE "id" = "area_id_v";
2555 ELSIF
2556 "unit_id_p" ISNULL AND
2557 "area_id_p" ISNULL AND
2558 "issue_id_p" NOTNULL
2559 THEN
2560 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
2561 IF "issue_row"."id" ISNULL THEN
2562 RETURN;
2563 END IF;
2564 IF "issue_row"."closed" NOTNULL THEN
2565 IF "simulate_v" THEN
2566 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
2567 END IF;
2568 FOR "output_row" IN
2569 SELECT * FROM
2570 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
2571 LOOP
2572 RETURN NEXT "output_row";
2573 END LOOP;
2574 RETURN;
2575 END IF;
2576 "scope_v" := 'issue';
2577 SELECT "area_id" INTO "area_id_v"
2578 FROM "issue" WHERE "id" = "issue_id_p";
2579 SELECT "unit_id" INTO "unit_id_v"
2580 FROM "area" WHERE "id" = "area_id_v";
2581 ELSE
2582 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2583 END IF;
2584 "visited_member_ids" := '{}';
2585 "loop_member_id_v" := NULL;
2586 "output_rows" := '{}';
2587 "output_row"."index" := 0;
2588 "output_row"."member_id" := "member_id_p";
2589 "output_row"."member_valid" := TRUE;
2590 "output_row"."participation" := FALSE;
2591 "output_row"."overridden" := FALSE;
2592 "output_row"."disabled_out" := FALSE;
2593 "output_row"."scope_out" := NULL;
2594 LOOP
2595 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2596 "loop_member_id_v" := "output_row"."member_id";
2597 ELSE
2598 "visited_member_ids" :=
2599 "visited_member_ids" || "output_row"."member_id";
2600 END IF;
2601 IF "output_row"."participation" ISNULL THEN
2602 "output_row"."overridden" := NULL;
2603 ELSIF "output_row"."participation" THEN
2604 "output_row"."overridden" := TRUE;
2605 END IF;
2606 "output_row"."scope_in" := "output_row"."scope_out";
2607 "output_row"."member_valid" := EXISTS (
2608 SELECT NULL FROM "member" JOIN "privilege"
2609 ON "privilege"."member_id" = "member"."id"
2610 AND "privilege"."unit_id" = "unit_id_v"
2611 WHERE "id" = "output_row"."member_id"
2612 AND "member"."active" AND "privilege"."voting_right"
2613 );
2614 "simulate_here_v" := (
2615 "simulate_v" AND
2616 "output_row"."member_id" = "member_id_p"
2617 );
2618 "delegation_row" := ROW(NULL);
2619 IF "output_row"."member_valid" OR "simulate_here_v" THEN
2620 IF "scope_v" = 'unit' THEN
2621 IF NOT "simulate_here_v" THEN
2622 SELECT * INTO "delegation_row" FROM "delegation"
2623 WHERE "truster_id" = "output_row"."member_id"
2624 AND "unit_id" = "unit_id_v";
2625 END IF;
2626 ELSIF "scope_v" = 'area' THEN
2627 "output_row"."participation" := EXISTS (
2628 SELECT NULL FROM "membership"
2629 WHERE "area_id" = "area_id_p"
2630 AND "member_id" = "output_row"."member_id"
2631 );
2632 IF "simulate_here_v" THEN
2633 IF "simulate_trustee_id_p" ISNULL THEN
2634 SELECT * INTO "delegation_row" FROM "delegation"
2635 WHERE "truster_id" = "output_row"."member_id"
2636 AND "unit_id" = "unit_id_v";
2637 END IF;
2638 ELSE
2639 SELECT * INTO "delegation_row" FROM "delegation"
2640 WHERE "truster_id" = "output_row"."member_id"
2641 AND (
2642 "unit_id" = "unit_id_v" OR
2643 "area_id" = "area_id_v"
2644 )
2645 ORDER BY "scope" DESC;
2646 END IF;
2647 ELSIF "scope_v" = 'issue' THEN
2648 IF "issue_row"."fully_frozen" ISNULL THEN
2649 "output_row"."participation" := EXISTS (
2650 SELECT NULL FROM "interest"
2651 WHERE "issue_id" = "issue_id_p"
2652 AND "member_id" = "output_row"."member_id"
2653 );
2654 ELSE
2655 IF "output_row"."member_id" = "member_id_p" THEN
2656 "output_row"."participation" := EXISTS (
2657 SELECT NULL FROM "direct_voter"
2658 WHERE "issue_id" = "issue_id_p"
2659 AND "member_id" = "output_row"."member_id"
2660 );
2661 ELSE
2662 "output_row"."participation" := NULL;
2663 END IF;
2664 END IF;
2665 IF "simulate_here_v" THEN
2666 IF "simulate_trustee_id_p" ISNULL THEN
2667 SELECT * INTO "delegation_row" FROM "delegation"
2668 WHERE "truster_id" = "output_row"."member_id"
2669 AND (
2670 "unit_id" = "unit_id_v" OR
2671 "area_id" = "area_id_v"
2672 )
2673 ORDER BY "scope" DESC;
2674 END IF;
2675 ELSE
2676 SELECT * INTO "delegation_row" FROM "delegation"
2677 WHERE "truster_id" = "output_row"."member_id"
2678 AND (
2679 "unit_id" = "unit_id_v" OR
2680 "area_id" = "area_id_v" OR
2681 "issue_id" = "issue_id_p"
2682 )
2683 ORDER BY "scope" DESC;
2684 END IF;
2685 END IF;
2686 ELSE
2687 "output_row"."participation" := FALSE;
2688 END IF;
2689 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
2690 "output_row"."scope_out" := "scope_v";
2691 "output_rows" := "output_rows" || "output_row";
2692 "output_row"."member_id" := "simulate_trustee_id_p";
2693 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2694 "output_row"."scope_out" := "delegation_row"."scope";
2695 "output_rows" := "output_rows" || "output_row";
2696 "output_row"."member_id" := "delegation_row"."trustee_id";
2697 ELSIF "delegation_row"."scope" NOTNULL THEN
2698 "output_row"."scope_out" := "delegation_row"."scope";
2699 "output_row"."disabled_out" := TRUE;
2700 "output_rows" := "output_rows" || "output_row";
2701 EXIT;
2702 ELSE
2703 "output_row"."scope_out" := NULL;
2704 "output_rows" := "output_rows" || "output_row";
2705 EXIT;
2706 END IF;
2707 EXIT WHEN "loop_member_id_v" NOTNULL;
2708 "output_row"."index" := "output_row"."index" + 1;
2709 END LOOP;
2710 "row_count" := array_upper("output_rows", 1);
2711 "i" := 1;
2712 "loop_v" := FALSE;
2713 LOOP
2714 "output_row" := "output_rows"["i"];
2715 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2716 IF "loop_v" THEN
2717 IF "i" + 1 = "row_count" THEN
2718 "output_row"."loop" := 'last';
2719 ELSIF "i" = "row_count" THEN
2720 "output_row"."loop" := 'repetition';
2721 ELSE
2722 "output_row"."loop" := 'intermediate';
2723 END IF;
2724 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2725 "output_row"."loop" := 'first';
2726 "loop_v" := TRUE;
2727 END IF;
2728 IF "scope_v" = 'unit' THEN
2729 "output_row"."participation" := NULL;
2730 END IF;
2731 RETURN NEXT "output_row";
2732 "i" := "i" + 1;
2733 END LOOP;
2734 RETURN;
2735 END;
2736 $$;
2738 COMMENT ON FUNCTION "delegation_chain"
2739 ( "member"."id"%TYPE,
2740 "unit"."id"%TYPE,
2741 "area"."id"%TYPE,
2742 "issue"."id"%TYPE,
2743 "member"."id"%TYPE,
2744 BOOLEAN )
2745 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
2749 ---------------------------------------------------------
2750 -- Single row returning function for delegation chains --
2751 ---------------------------------------------------------
2754 CREATE TYPE "delegation_info_loop_type" AS ENUM
2755 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');
2757 COMMENT ON TYPE "delegation_info_loop_type" IS 'Type of "delegation_loop" in "delegation_info_type"; ''own'' means loop to self, ''first'' means loop to first trustee, ''first_ellipsis'' means loop to ellipsis after first trustee, ''other'' means loop to other trustee, ''other_ellipsis'' means loop to ellipsis after other trustee''';
2760 CREATE TYPE "delegation_info_type" AS (
2761 "own_participation" BOOLEAN,
2762 "own_delegation_scope" "delegation_scope",
2763 "first_trustee_id" INT4,
2764 "first_trustee_participation" BOOLEAN,
2765 "first_trustee_ellipsis" BOOLEAN,
2766 "other_trustee_id" INT4,
2767 "other_trustee_participation" BOOLEAN,
2768 "other_trustee_ellipsis" BOOLEAN,
2769 "delegation_loop" "delegation_info_loop_type",
2770 "participating_member_id" INT4 );
2772 COMMENT ON TYPE "delegation_info_type" IS 'Type of result returned by "delegation_info" function; For meaning of "participation" check comment on "delegation_chain_row" type';
2774 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';
2775 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';
2776 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';
2777 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
2778 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';
2779 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';
2780 COMMENT ON COLUMN "delegation_info_type"."other_trustee_participation" IS 'Another trustee is participating (redundant field: if "other_trustee_id" is set, then "other_trustee_participation" is always TRUE, else "other_trustee_participation" is NULL)';
2781 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';
2782 COMMENT ON COLUMN "delegation_info_type"."delegation_loop" IS 'Non-NULL value, if delegation chain contains a circle; See comment on "delegation_info_loop_type" for details';
2783 COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain';
2786 CREATE FUNCTION "delegation_info"
2787 ( "member_id_p" "member"."id"%TYPE,
2788 "unit_id_p" "unit"."id"%TYPE,
2789 "area_id_p" "area"."id"%TYPE,
2790 "issue_id_p" "issue"."id"%TYPE,
2791 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2792 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2793 RETURNS "delegation_info_type"
2794 LANGUAGE 'plpgsql' STABLE AS $$
2795 DECLARE
2796 "current_row" "delegation_chain_row";
2797 "result" "delegation_info_type";
2798 BEGIN
2799 "result"."own_participation" := FALSE;
2800 FOR "current_row" IN
2801 SELECT * FROM "delegation_chain"(
2802 "member_id_p",
2803 "unit_id_p", "area_id_p", "issue_id_p",
2804 "simulate_trustee_id_p", "simulate_default_p")
2805 LOOP
2806 IF
2807 "result"."participating_member_id" ISNULL AND
2808 "current_row"."participation"
2809 THEN
2810 "result"."participating_member_id" := "current_row"."member_id";
2811 END IF;
2812 IF "current_row"."member_id" = "member_id_p" THEN
2813 "result"."own_participation" := "current_row"."participation";
2814 "result"."own_delegation_scope" := "current_row"."scope_out";
2815 IF "current_row"."loop" = 'first' THEN
2816 "result"."delegation_loop" := 'own';
2817 END IF;
2818 ELSIF
2819 "current_row"."member_valid" AND
2820 ( "current_row"."loop" ISNULL OR
2821 "current_row"."loop" != 'repetition' )
2822 THEN
2823 IF "result"."first_trustee_id" ISNULL THEN
2824 "result"."first_trustee_id" := "current_row"."member_id";
2825 "result"."first_trustee_participation" := "current_row"."participation";
2826 "result"."first_trustee_ellipsis" := FALSE;
2827 IF "current_row"."loop" = 'first' THEN
2828 "result"."delegation_loop" := 'first';
2829 END IF;
2830 ELSIF "result"."other_trustee_id" ISNULL THEN
2831 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
2832 "result"."other_trustee_id" := "current_row"."member_id";
2833 "result"."other_trustee_participation" := TRUE;
2834 "result"."other_trustee_ellipsis" := FALSE;
2835 IF "current_row"."loop" = 'first' THEN
2836 "result"."delegation_loop" := 'other';
2837 END IF;
2838 ELSE
2839 "result"."first_trustee_ellipsis" := TRUE;
2840 IF "current_row"."loop" = 'first' THEN
2841 "result"."delegation_loop" := 'first_ellipsis';
2842 END IF;
2843 END IF;
2844 ELSE
2845 "result"."other_trustee_ellipsis" := TRUE;
2846 IF "current_row"."loop" = 'first' THEN
2847 "result"."delegation_loop" := 'other_ellipsis';
2848 END IF;
2849 END IF;
2850 END IF;
2851 END LOOP;
2852 RETURN "result";
2853 END;
2854 $$;
2856 COMMENT ON FUNCTION "delegation_info"
2857 ( "member"."id"%TYPE,
2858 "unit"."id"%TYPE,
2859 "area"."id"%TYPE,
2860 "issue"."id"%TYPE,
2861 "member"."id"%TYPE,
2862 BOOLEAN )
2863 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
2867 ---------------------------
2868 -- Transaction isolation --
2869 ---------------------------
2872 CREATE FUNCTION "require_transaction_isolation"()
2873 RETURNS VOID
2874 LANGUAGE 'plpgsql' VOLATILE AS $$
2875 BEGIN
2876 IF
2877 current_setting('transaction_isolation') NOT IN
2878 ('repeatable read', 'serializable')
2879 THEN
2880 RAISE EXCEPTION 'Insufficient transaction isolation level' USING
2881 HINT = 'Consider using SET TRANSACTION ISOLATION LEVEL REPEATABLE READ.';
2882 END IF;
2883 RETURN;
2884 END;
2885 $$;
2887 COMMENT ON FUNCTION "require_transaction_isolation"() IS 'Throws an exception, if transaction isolation level is too low to provide a consistent snapshot';
2890 CREATE FUNCTION "dont_require_transaction_isolation"()
2891 RETURNS VOID
2892 LANGUAGE 'plpgsql' VOLATILE AS $$
2893 BEGIN
2894 IF
2895 current_setting('transaction_isolation') IN
2896 ('repeatable read', 'serializable')
2897 THEN
2898 RAISE WARNING 'Unneccessary transaction isolation level: %',
2899 current_setting('transaction_isolation');
2900 END IF;
2901 RETURN;
2902 END;
2903 $$;
2905 COMMENT ON FUNCTION "dont_require_transaction_isolation"() IS 'Raises a warning, if transaction isolation level is higher than READ COMMITTED';
2909 ------------------------------------------------------------------------
2910 -- Regular tasks, except calculcation of snapshots and voting results --
2911 ------------------------------------------------------------------------
2914 CREATE FUNCTION "check_activity"()
2915 RETURNS VOID
2916 LANGUAGE 'plpgsql' VOLATILE AS $$
2917 DECLARE
2918 "system_setting_row" "system_setting"%ROWTYPE;
2919 BEGIN
2920 PERFORM "dont_require_transaction_isolation"();
2921 SELECT * INTO "system_setting_row" FROM "system_setting";
2922 IF "system_setting_row"."member_ttl" NOTNULL THEN
2923 UPDATE "member" SET "active" = FALSE
2924 WHERE "active" = TRUE
2925 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
2926 END IF;
2927 RETURN;
2928 END;
2929 $$;
2931 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
2934 CREATE FUNCTION "calculate_member_counts"()
2935 RETURNS VOID
2936 LANGUAGE 'plpgsql' VOLATILE AS $$
2937 BEGIN
2938 PERFORM "require_transaction_isolation"();
2939 DELETE FROM "member_count";
2940 INSERT INTO "member_count" ("total_count")
2941 SELECT "total_count" FROM "member_count_view";
2942 UPDATE "unit" SET "member_count" = "view"."member_count"
2943 FROM "unit_member_count" AS "view"
2944 WHERE "view"."unit_id" = "unit"."id";
2945 UPDATE "area" SET
2946 "direct_member_count" = "view"."direct_member_count",
2947 "member_weight" = "view"."member_weight"
2948 FROM "area_member_count" AS "view"
2949 WHERE "view"."area_id" = "area"."id";
2950 RETURN;
2951 END;
2952 $$;
2954 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"';
2958 ------------------------------------
2959 -- Calculation of harmonic weight --
2960 ------------------------------------
2963 CREATE VIEW "remaining_harmonic_supporter_weight" AS
2964 SELECT
2965 "direct_interest_snapshot"."issue_id",
2966 "direct_interest_snapshot"."event",
2967 "direct_interest_snapshot"."member_id",
2968 "direct_interest_snapshot"."weight" AS "weight_num",
2969 count("initiative"."id") AS "weight_den"
2970 FROM "issue"
2971 JOIN "direct_interest_snapshot"
2972 ON "issue"."id" = "direct_interest_snapshot"."issue_id"
2973 AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
2974 JOIN "initiative"
2975 ON "issue"."id" = "initiative"."issue_id"
2976 AND "initiative"."harmonic_weight" ISNULL
2977 JOIN "direct_supporter_snapshot"
2978 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
2979 AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event"
2980 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
2981 AND (
2982 "direct_supporter_snapshot"."satisfied" = TRUE OR
2983 coalesce("initiative"."admitted", FALSE) = FALSE
2984 )
2985 GROUP BY
2986 "direct_interest_snapshot"."issue_id",
2987 "direct_interest_snapshot"."event",
2988 "direct_interest_snapshot"."member_id",
2989 "direct_interest_snapshot"."weight";
2991 COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';
2994 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
2995 SELECT
2996 "initiative"."issue_id",
2997 "initiative"."id" AS "initiative_id",
2998 "initiative"."admitted",
2999 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
3000 "remaining_harmonic_supporter_weight"."weight_den"
3001 FROM "remaining_harmonic_supporter_weight"
3002 JOIN "initiative"
3003 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
3004 AND "initiative"."harmonic_weight" ISNULL
3005 JOIN "direct_supporter_snapshot"
3006 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
3007 AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event"
3008 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
3009 AND (
3010 "direct_supporter_snapshot"."satisfied" = TRUE OR
3011 coalesce("initiative"."admitted", FALSE) = FALSE
3012 )
3013 GROUP BY
3014 "initiative"."issue_id",
3015 "initiative"."id",
3016 "initiative"."admitted",
3017 "remaining_harmonic_supporter_weight"."weight_den";
3019 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
3022 CREATE VIEW "remaining_harmonic_initiative_weight_dummies" AS
3023 SELECT
3024 "issue_id",
3025 "id" AS "initiative_id",
3026 "admitted",
3027 0 AS "weight_num",
3028 1 AS "weight_den"
3029 FROM "initiative"
3030 WHERE "harmonic_weight" ISNULL;
3032 COMMENT ON VIEW "remaining_harmonic_initiative_weight_dummies" IS 'Helper view for function "set_harmonic_initiative_weights" providing dummy weights of zero value, which are needed for corner cases where there are no supporters for an initiative at all';
3035 CREATE FUNCTION "set_harmonic_initiative_weights"
3036 ( "issue_id_p" "issue"."id"%TYPE )
3037 RETURNS VOID
3038 LANGUAGE 'plpgsql' VOLATILE AS $$
3039 DECLARE
3040 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;
3041 "i" INT4;
3042 "count_v" INT4;
3043 "summand_v" FLOAT;
3044 "id_ary" INT4[];
3045 "weight_ary" FLOAT[];
3046 "min_weight_v" FLOAT;
3047 BEGIN
3048 PERFORM "require_transaction_isolation"();
3049 UPDATE "initiative" SET "harmonic_weight" = NULL
3050 WHERE "issue_id" = "issue_id_p";
3051 LOOP
3052 "min_weight_v" := NULL;
3053 "i" := 0;
3054 "count_v" := 0;
3055 FOR "weight_row" IN
3056 SELECT * FROM "remaining_harmonic_initiative_weight_summands"
3057 WHERE "issue_id" = "issue_id_p"
3058 AND (
3059 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
3060 SELECT NULL FROM "initiative"
3061 WHERE "issue_id" = "issue_id_p"
3062 AND "harmonic_weight" ISNULL
3063 AND coalesce("admitted", FALSE) = FALSE
3064 )
3065 )
3066 UNION ALL -- needed for corner cases
3067 SELECT * FROM "remaining_harmonic_initiative_weight_dummies"
3068 WHERE "issue_id" = "issue_id_p"
3069 AND (
3070 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
3071 SELECT NULL FROM "initiative"
3072 WHERE "issue_id" = "issue_id_p"
3073 AND "harmonic_weight" ISNULL
3074 AND coalesce("admitted", FALSE) = FALSE
3075 )
3076 )
3077 ORDER BY "initiative_id" DESC, "weight_den" DESC
3078 -- NOTE: non-admitted initiatives placed first (at last positions),
3079 -- latest initiatives treated worse in case of tie
3080 LOOP
3081 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
3082 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
3083 "i" := "i" + 1;
3084 "count_v" := "i";
3085 "id_ary"["i"] := "weight_row"."initiative_id";
3086 "weight_ary"["i"] := "summand_v";
3087 ELSE
3088 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
3089 END IF;
3090 END LOOP;
3091 EXIT WHEN "count_v" = 0;
3092 "i" := 1;
3093 LOOP
3094 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
3095 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
3096 "min_weight_v" := "weight_ary"["i"];
3097 END IF;
3098 "i" := "i" + 1;
3099 EXIT WHEN "i" > "count_v";
3100 END LOOP;
3101 "i" := 1;
3102 LOOP
3103 IF "weight_ary"["i"] = "min_weight_v" THEN
3104 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"
3105 WHERE "id" = "id_ary"["i"];
3106 EXIT;
3107 END IF;
3108 "i" := "i" + 1;
3109 END LOOP;
3110 END LOOP;
3111 UPDATE "initiative" SET "harmonic_weight" = 0
3112 WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;
3113 END;
3114 $$;
3116 COMMENT ON FUNCTION "set_harmonic_initiative_weights"
3117 ( "issue"."id"%TYPE )
3118 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
3122 ------------------------------
3123 -- Calculation of snapshots --
3124 ------------------------------
3127 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
3128 ( "issue_id_p" "issue"."id"%TYPE,
3129 "member_id_p" "member"."id"%TYPE,
3130 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3131 RETURNS "direct_population_snapshot"."weight"%TYPE
3132 LANGUAGE 'plpgsql' VOLATILE AS $$
3133 DECLARE
3134 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3135 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
3136 "weight_v" INT4;
3137 "sub_weight_v" INT4;
3138 BEGIN
3139 PERFORM "require_transaction_isolation"();
3140 "weight_v" := 0;
3141 FOR "issue_delegation_row" IN
3142 SELECT * FROM "issue_delegation"
3143 WHERE "trustee_id" = "member_id_p"
3144 AND "issue_id" = "issue_id_p"
3145 LOOP
3146 IF NOT EXISTS (
3147 SELECT NULL FROM "direct_population_snapshot"
3148 WHERE "issue_id" = "issue_id_p"
3149 AND "event" = 'periodic'
3150 AND "member_id" = "issue_delegation_row"."truster_id"
3151 ) AND NOT EXISTS (
3152 SELECT NULL FROM "delegating_population_snapshot"
3153 WHERE "issue_id" = "issue_id_p"
3154 AND "event" = 'periodic'
3155 AND "member_id" = "issue_delegation_row"."truster_id"
3156 ) THEN
3157 "delegate_member_ids_v" :=
3158 "member_id_p" || "delegate_member_ids_p";
3159 INSERT INTO "delegating_population_snapshot" (
3160 "issue_id",
3161 "event",
3162 "member_id",
3163 "scope",
3164 "delegate_member_ids"
3165 ) VALUES (
3166 "issue_id_p",
3167 'periodic',
3168 "issue_delegation_row"."truster_id",
3169 "issue_delegation_row"."scope",
3170 "delegate_member_ids_v"
3171 );
3172 "sub_weight_v" := 1 +
3173 "weight_of_added_delegations_for_population_snapshot"(
3174 "issue_id_p",
3175 "issue_delegation_row"."truster_id",
3176 "delegate_member_ids_v"
3177 );
3178 UPDATE "delegating_population_snapshot"
3179 SET "weight" = "sub_weight_v"
3180 WHERE "issue_id" = "issue_id_p"
3181 AND "event" = 'periodic'
3182 AND "member_id" = "issue_delegation_row"."truster_id";
3183 "weight_v" := "weight_v" + "sub_weight_v";
3184 END IF;
3185 END LOOP;
3186 RETURN "weight_v";
3187 END;
3188 $$;
3190 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
3191 ( "issue"."id"%TYPE,
3192 "member"."id"%TYPE,
3193 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3194 IS 'Helper function for "create_population_snapshot" function';
3197 CREATE FUNCTION "create_population_snapshot"
3198 ( "issue_id_p" "issue"."id"%TYPE )
3199 RETURNS VOID
3200 LANGUAGE 'plpgsql' VOLATILE AS $$
3201 DECLARE
3202 "member_id_v" "member"."id"%TYPE;
3203 BEGIN
3204 PERFORM "require_transaction_isolation"();
3205 DELETE FROM "direct_population_snapshot"
3206 WHERE "issue_id" = "issue_id_p"
3207 AND "event" = 'periodic';
3208 DELETE FROM "delegating_population_snapshot"
3209 WHERE "issue_id" = "issue_id_p"
3210 AND "event" = 'periodic';
3211 INSERT INTO "direct_population_snapshot"
3212 ("issue_id", "event", "member_id")
3213 SELECT
3214 "issue_id_p" AS "issue_id",
3215 'periodic'::"snapshot_event" AS "event",
3216 "member"."id" AS "member_id"
3217 FROM "issue"
3218 JOIN "area" ON "issue"."area_id" = "area"."id"
3219 JOIN "membership" ON "area"."id" = "membership"."area_id"
3220 JOIN "member" ON "membership"."member_id" = "member"."id"
3221 JOIN "privilege"
3222 ON "privilege"."unit_id" = "area"."unit_id"
3223 AND "privilege"."member_id" = "member"."id"
3224 WHERE "issue"."id" = "issue_id_p"
3225 AND "member"."active" AND "privilege"."voting_right"
3226 UNION
3227 SELECT
3228 "issue_id_p" AS "issue_id",
3229 'periodic'::"snapshot_event" AS "event",
3230 "member"."id" AS "member_id"
3231 FROM "issue"
3232 JOIN "area" ON "issue"."area_id" = "area"."id"
3233 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3234 JOIN "member" ON "interest"."member_id" = "member"."id"
3235 JOIN "privilege"
3236 ON "privilege"."unit_id" = "area"."unit_id"
3237 AND "privilege"."member_id" = "member"."id"
3238 WHERE "issue"."id" = "issue_id_p"
3239 AND "member"."active" AND "privilege"."voting_right";
3240 FOR "member_id_v" IN
3241 SELECT "member_id" FROM "direct_population_snapshot"
3242 WHERE "issue_id" = "issue_id_p"
3243 AND "event" = 'periodic'
3244 LOOP
3245 UPDATE "direct_population_snapshot" SET
3246 "weight" = 1 +
3247 "weight_of_added_delegations_for_population_snapshot"(
3248 "issue_id_p",
3249 "member_id_v",
3250 '{}'
3251 )
3252 WHERE "issue_id" = "issue_id_p"
3253 AND "event" = 'periodic'
3254 AND "member_id" = "member_id_v";
3255 END LOOP;
3256 RETURN;
3257 END;
3258 $$;
3260 COMMENT ON FUNCTION "create_population_snapshot"
3261 ( "issue"."id"%TYPE )
3262 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.';
3265 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3266 ( "issue_id_p" "issue"."id"%TYPE,
3267 "member_id_p" "member"."id"%TYPE,
3268 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3269 RETURNS "direct_interest_snapshot"."weight"%TYPE
3270 LANGUAGE 'plpgsql' VOLATILE AS $$
3271 DECLARE
3272 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3273 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
3274 "weight_v" INT4;
3275 "sub_weight_v" INT4;
3276 BEGIN
3277 PERFORM "require_transaction_isolation"();
3278 "weight_v" := 0;
3279 FOR "issue_delegation_row" IN
3280 SELECT * FROM "issue_delegation"
3281 WHERE "trustee_id" = "member_id_p"
3282 AND "issue_id" = "issue_id_p"
3283 LOOP
3284 IF NOT EXISTS (
3285 SELECT NULL FROM "direct_interest_snapshot"
3286 WHERE "issue_id" = "issue_id_p"
3287 AND "event" = 'periodic'
3288 AND "member_id" = "issue_delegation_row"."truster_id"
3289 ) AND NOT EXISTS (
3290 SELECT NULL FROM "delegating_interest_snapshot"
3291 WHERE "issue_id" = "issue_id_p"
3292 AND "event" = 'periodic'
3293 AND "member_id" = "issue_delegation_row"."truster_id"
3294 ) THEN
3295 "delegate_member_ids_v" :=
3296 "member_id_p" || "delegate_member_ids_p";
3297 INSERT INTO "delegating_interest_snapshot" (
3298 "issue_id",
3299 "event",
3300 "member_id",
3301 "scope",
3302 "delegate_member_ids"
3303 ) VALUES (
3304 "issue_id_p",
3305 'periodic',
3306 "issue_delegation_row"."truster_id",
3307 "issue_delegation_row"."scope",
3308 "delegate_member_ids_v"
3309 );
3310 "sub_weight_v" := 1 +
3311 "weight_of_added_delegations_for_interest_snapshot"(
3312 "issue_id_p",
3313 "issue_delegation_row"."truster_id",
3314 "delegate_member_ids_v"
3315 );
3316 UPDATE "delegating_interest_snapshot"
3317 SET "weight" = "sub_weight_v"
3318 WHERE "issue_id" = "issue_id_p"
3319 AND "event" = 'periodic'
3320 AND "member_id" = "issue_delegation_row"."truster_id";
3321 "weight_v" := "weight_v" + "sub_weight_v";
3322 END IF;
3323 END LOOP;
3324 RETURN "weight_v";
3325 END;
3326 $$;
3328 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3329 ( "issue"."id"%TYPE,
3330 "member"."id"%TYPE,
3331 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3332 IS 'Helper function for "create_interest_snapshot" function';
3335 CREATE FUNCTION "create_interest_snapshot"
3336 ( "issue_id_p" "issue"."id"%TYPE )
3337 RETURNS VOID
3338 LANGUAGE 'plpgsql' VOLATILE AS $$
3339 DECLARE
3340 "member_id_v" "member"."id"%TYPE;
3341 BEGIN
3342 PERFORM "require_transaction_isolation"();
3343 DELETE FROM "direct_interest_snapshot"
3344 WHERE "issue_id" = "issue_id_p"
3345 AND "event" = 'periodic';
3346 DELETE FROM "delegating_interest_snapshot"
3347 WHERE "issue_id" = "issue_id_p"
3348 AND "event" = 'periodic';
3349 DELETE FROM "direct_supporter_snapshot"
3350 USING "initiative" -- NOTE: due to missing index on issue_id
3351 WHERE "initiative"."issue_id" = "issue_id_p"
3352 AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
3353 AND "direct_supporter_snapshot"."event" = 'periodic';
3354 INSERT INTO "direct_interest_snapshot"
3355 ("issue_id", "event", "member_id")
3356 SELECT
3357 "issue_id_p" AS "issue_id",
3358 'periodic' AS "event",
3359 "member"."id" AS "member_id"
3360 FROM "issue"
3361 JOIN "area" ON "issue"."area_id" = "area"."id"
3362 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3363 JOIN "member" ON "interest"."member_id" = "member"."id"
3364 JOIN "privilege"
3365 ON "privilege"."unit_id" = "area"."unit_id"
3366 AND "privilege"."member_id" = "member"."id"
3367 WHERE "issue"."id" = "issue_id_p"
3368 AND "member"."active" AND "privilege"."voting_right";
3369 FOR "member_id_v" IN
3370 SELECT "member_id" FROM "direct_interest_snapshot"
3371 WHERE "issue_id" = "issue_id_p"
3372 AND "event" = 'periodic'
3373 LOOP
3374 UPDATE "direct_interest_snapshot" SET
3375 "weight" = 1 +
3376 "weight_of_added_delegations_for_interest_snapshot"(
3377 "issue_id_p",
3378 "member_id_v",
3379 '{}'
3380 )
3381 WHERE "issue_id" = "issue_id_p"
3382 AND "event" = 'periodic'
3383 AND "member_id" = "member_id_v";
3384 END LOOP;
3385 INSERT INTO "direct_supporter_snapshot"
3386 ( "issue_id", "initiative_id", "event", "member_id",
3387 "draft_id", "informed", "satisfied" )
3388 SELECT
3389 "issue_id_p" AS "issue_id",
3390 "initiative"."id" AS "initiative_id",
3391 'periodic' AS "event",
3392 "supporter"."member_id" AS "member_id",
3393 "supporter"."draft_id" AS "draft_id",
3394 "supporter"."draft_id" = "current_draft"."id" AS "informed",
3395 NOT EXISTS (
3396 SELECT NULL FROM "critical_opinion"
3397 WHERE "initiative_id" = "initiative"."id"
3398 AND "member_id" = "supporter"."member_id"
3399 ) AS "satisfied"
3400 FROM "initiative"
3401 JOIN "supporter"
3402 ON "supporter"."initiative_id" = "initiative"."id"
3403 JOIN "current_draft"
3404 ON "initiative"."id" = "current_draft"."initiative_id"
3405 JOIN "direct_interest_snapshot"
3406 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
3407 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
3408 AND "event" = 'periodic'
3409 WHERE "initiative"."issue_id" = "issue_id_p";
3410 RETURN;
3411 END;
3412 $$;
3414 COMMENT ON FUNCTION "create_interest_snapshot"
3415 ( "issue"."id"%TYPE )
3416 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.';
3419 CREATE FUNCTION "create_snapshot"
3420 ( "issue_id_p" "issue"."id"%TYPE )
3421 RETURNS VOID
3422 LANGUAGE 'plpgsql' VOLATILE AS $$
3423 DECLARE
3424 "initiative_id_v" "initiative"."id"%TYPE;
3425 "suggestion_id_v" "suggestion"."id"%TYPE;
3426 BEGIN
3427 PERFORM "require_transaction_isolation"();
3428 PERFORM "create_population_snapshot"("issue_id_p");
3429 PERFORM "create_interest_snapshot"("issue_id_p");
3430 UPDATE "issue" SET
3431 "snapshot" = coalesce("phase_finished", now()),
3432 "latest_snapshot_event" = 'periodic',
3433 "population" = (
3434 SELECT coalesce(sum("weight"), 0)
3435 FROM "direct_population_snapshot"
3436 WHERE "issue_id" = "issue_id_p"
3437 AND "event" = 'periodic'
3438 )
3439 WHERE "id" = "issue_id_p";
3440 FOR "initiative_id_v" IN
3441 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
3442 LOOP
3443 UPDATE "initiative" SET
3444 "supporter_count" = (
3445 SELECT coalesce(sum("di"."weight"), 0)
3446 FROM "direct_interest_snapshot" AS "di"
3447 JOIN "direct_supporter_snapshot" AS "ds"
3448 ON "di"."member_id" = "ds"."member_id"
3449 WHERE "di"."issue_id" = "issue_id_p"
3450 AND "di"."event" = 'periodic'
3451 AND "ds"."initiative_id" = "initiative_id_v"
3452 AND "ds"."event" = 'periodic'
3453 ),
3454 "informed_supporter_count" = (
3455 SELECT coalesce(sum("di"."weight"), 0)
3456 FROM "direct_interest_snapshot" AS "di"
3457 JOIN "direct_supporter_snapshot" AS "ds"
3458 ON "di"."member_id" = "ds"."member_id"
3459 WHERE "di"."issue_id" = "issue_id_p"
3460 AND "di"."event" = 'periodic'
3461 AND "ds"."initiative_id" = "initiative_id_v"
3462 AND "ds"."event" = 'periodic'
3463 AND "ds"."informed"
3464 ),
3465 "satisfied_supporter_count" = (
3466 SELECT coalesce(sum("di"."weight"), 0)
3467 FROM "direct_interest_snapshot" AS "di"
3468 JOIN "direct_supporter_snapshot" AS "ds"
3469 ON "di"."member_id" = "ds"."member_id"
3470 WHERE "di"."issue_id" = "issue_id_p"
3471 AND "di"."event" = 'periodic'
3472 AND "ds"."initiative_id" = "initiative_id_v"
3473 AND "ds"."event" = 'periodic'
3474 AND "ds"."satisfied"
3475 ),
3476 "satisfied_informed_supporter_count" = (
3477 SELECT coalesce(sum("di"."weight"), 0)
3478 FROM "direct_interest_snapshot" AS "di"
3479 JOIN "direct_supporter_snapshot" AS "ds"
3480 ON "di"."member_id" = "ds"."member_id"
3481 WHERE "di"."issue_id" = "issue_id_p"
3482 AND "di"."event" = 'periodic'
3483 AND "ds"."initiative_id" = "initiative_id_v"
3484 AND "ds"."event" = 'periodic'
3485 AND "ds"."informed"
3486 AND "ds"."satisfied"
3487 )
3488 WHERE "id" = "initiative_id_v";
3489 FOR "suggestion_id_v" IN
3490 SELECT "id" FROM "suggestion"
3491 WHERE "initiative_id" = "initiative_id_v"
3492 LOOP
3493 UPDATE "suggestion" SET
3494 "minus2_unfulfilled_count" = (
3495 SELECT coalesce(sum("snapshot"."weight"), 0)
3496 FROM "issue" CROSS JOIN "opinion"
3497 JOIN "direct_interest_snapshot" AS "snapshot"
3498 ON "snapshot"."issue_id" = "issue"."id"
3499 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3500 AND "snapshot"."member_id" = "opinion"."member_id"
3501 WHERE "issue"."id" = "issue_id_p"
3502 AND "opinion"."suggestion_id" = "suggestion_id_v"
3503 AND "opinion"."degree" = -2
3504 AND "opinion"."fulfilled" = FALSE
3505 ),
3506 "minus2_fulfilled_count" = (
3507 SELECT coalesce(sum("snapshot"."weight"), 0)
3508 FROM "issue" CROSS JOIN "opinion"
3509 JOIN "direct_interest_snapshot" AS "snapshot"
3510 ON "snapshot"."issue_id" = "issue"."id"
3511 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3512 AND "snapshot"."member_id" = "opinion"."member_id"
3513 WHERE "issue"."id" = "issue_id_p"
3514 AND "opinion"."suggestion_id" = "suggestion_id_v"
3515 AND "opinion"."degree" = -2
3516 AND "opinion"."fulfilled" = TRUE
3517 ),
3518 "minus1_unfulfilled_count" = (
3519 SELECT coalesce(sum("snapshot"."weight"), 0)
3520 FROM "issue" CROSS JOIN "opinion"
3521 JOIN "direct_interest_snapshot" AS "snapshot"
3522 ON "snapshot"."issue_id" = "issue"."id"
3523 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3524 AND "snapshot"."member_id" = "opinion"."member_id"
3525 WHERE "issue"."id" = "issue_id_p"
3526 AND "opinion"."suggestion_id" = "suggestion_id_v"
3527 AND "opinion"."degree" = -1
3528 AND "opinion"."fulfilled" = FALSE
3529 ),
3530 "minus1_fulfilled_count" = (
3531 SELECT coalesce(sum("snapshot"."weight"), 0)
3532 FROM "issue" CROSS JOIN "opinion"
3533 JOIN "direct_interest_snapshot" AS "snapshot"
3534 ON "snapshot"."issue_id" = "issue"."id"
3535 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3536 AND "snapshot"."member_id" = "opinion"."member_id"
3537 WHERE "issue"."id" = "issue_id_p"
3538 AND "opinion"."suggestion_id" = "suggestion_id_v"
3539 AND "opinion"."degree" = -1
3540 AND "opinion"."fulfilled" = TRUE
3541 ),
3542 "plus1_unfulfilled_count" = (
3543 SELECT coalesce(sum("snapshot"."weight"), 0)
3544 FROM "issue" CROSS JOIN "opinion"
3545 JOIN "direct_interest_snapshot" AS "snapshot"
3546 ON "snapshot"."issue_id" = "issue"."id"
3547 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3548 AND "snapshot"."member_id" = "opinion"."member_id"
3549 WHERE "issue"."id" = "issue_id_p"
3550 AND "opinion"."suggestion_id" = "suggestion_id_v"
3551 AND "opinion"."degree" = 1
3552 AND "opinion"."fulfilled" = FALSE
3553 ),
3554 "plus1_fulfilled_count" = (
3555 SELECT coalesce(sum("snapshot"."weight"), 0)
3556 FROM "issue" CROSS JOIN "opinion"
3557 JOIN "direct_interest_snapshot" AS "snapshot"
3558 ON "snapshot"."issue_id" = "issue"."id"
3559 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3560 AND "snapshot"."member_id" = "opinion"."member_id"
3561 WHERE "issue"."id" = "issue_id_p"
3562 AND "opinion"."suggestion_id" = "suggestion_id_v"
3563 AND "opinion"."degree" = 1
3564 AND "opinion"."fulfilled" = TRUE
3565 ),
3566 "plus2_unfulfilled_count" = (
3567 SELECT coalesce(sum("snapshot"."weight"), 0)
3568 FROM "issue" CROSS JOIN "opinion"
3569 JOIN "direct_interest_snapshot" AS "snapshot"
3570 ON "snapshot"."issue_id" = "issue"."id"
3571 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3572 AND "snapshot"."member_id" = "opinion"."member_id"
3573 WHERE "issue"."id" = "issue_id_p"
3574 AND "opinion"."suggestion_id" = "suggestion_id_v"
3575 AND "opinion"."degree" = 2
3576 AND "opinion"."fulfilled" = FALSE
3577 ),
3578 "plus2_fulfilled_count" = (
3579 SELECT coalesce(sum("snapshot"."weight"), 0)
3580 FROM "issue" CROSS JOIN "opinion"
3581 JOIN "direct_interest_snapshot" AS "snapshot"
3582 ON "snapshot"."issue_id" = "issue"."id"
3583 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3584 AND "snapshot"."member_id" = "opinion"."member_id"
3585 WHERE "issue"."id" = "issue_id_p"
3586 AND "opinion"."suggestion_id" = "suggestion_id_v"
3587 AND "opinion"."degree" = 2
3588 AND "opinion"."fulfilled" = TRUE
3589 )
3590 WHERE "suggestion"."id" = "suggestion_id_v";
3591 END LOOP;
3592 END LOOP;
3593 RETURN;
3594 END;
3595 $$;
3597 COMMENT ON FUNCTION "create_snapshot"
3598 ( "issue"."id"%TYPE )
3599 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.';
3602 CREATE FUNCTION "set_snapshot_event"
3603 ( "issue_id_p" "issue"."id"%TYPE,
3604 "event_p" "snapshot_event" )
3605 RETURNS VOID
3606 LANGUAGE 'plpgsql' VOLATILE AS $$
3607 DECLARE
3608 "event_v" "issue"."latest_snapshot_event"%TYPE;
3609 BEGIN
3610 PERFORM "require_transaction_isolation"();
3611 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
3612 WHERE "id" = "issue_id_p" FOR UPDATE;
3613 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
3614 WHERE "id" = "issue_id_p";
3615 UPDATE "direct_population_snapshot" SET "event" = "event_p"
3616 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3617 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
3618 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3619 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
3620 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3621 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
3622 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3623 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
3624 FROM "initiative" -- NOTE: due to missing index on issue_id
3625 WHERE "initiative"."issue_id" = "issue_id_p"
3626 AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
3627 AND "direct_supporter_snapshot"."event" = "event_v";
3628 RETURN;
3629 END;
3630 $$;
3632 COMMENT ON FUNCTION "set_snapshot_event"
3633 ( "issue"."id"%TYPE,
3634 "snapshot_event" )
3635 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
3639 -----------------------
3640 -- Counting of votes --
3641 -----------------------
3644 CREATE FUNCTION "weight_of_added_vote_delegations"
3645 ( "issue_id_p" "issue"."id"%TYPE,
3646 "member_id_p" "member"."id"%TYPE,
3647 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
3648 RETURNS "direct_voter"."weight"%TYPE
3649 LANGUAGE 'plpgsql' VOLATILE AS $$
3650 DECLARE
3651 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3652 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
3653 "weight_v" INT4;
3654 "sub_weight_v" INT4;
3655 BEGIN
3656 PERFORM "require_transaction_isolation"();
3657 "weight_v" := 0;
3658 FOR "issue_delegation_row" IN
3659 SELECT * FROM "issue_delegation"
3660 WHERE "trustee_id" = "member_id_p"
3661 AND "issue_id" = "issue_id_p"
3662 LOOP
3663 IF NOT EXISTS (
3664 SELECT NULL FROM "direct_voter"
3665 WHERE "member_id" = "issue_delegation_row"."truster_id"
3666 AND "issue_id" = "issue_id_p"
3667 ) AND NOT EXISTS (
3668 SELECT NULL FROM "delegating_voter"
3669 WHERE "member_id" = "issue_delegation_row"."truster_id"
3670 AND "issue_id" = "issue_id_p"
3671 ) THEN
3672 "delegate_member_ids_v" :=
3673 "member_id_p" || "delegate_member_ids_p";
3674 INSERT INTO "delegating_voter" (
3675 "issue_id",
3676 "member_id",
3677 "scope",
3678 "delegate_member_ids"
3679 ) VALUES (
3680 "issue_id_p",
3681 "issue_delegation_row"."truster_id",
3682 "issue_delegation_row"."scope",
3683 "delegate_member_ids_v"
3684 );
3685 "sub_weight_v" := 1 +
3686 "weight_of_added_vote_delegations"(
3687 "issue_id_p",
3688 "issue_delegation_row"."truster_id",
3689 "delegate_member_ids_v"
3690 );
3691 UPDATE "delegating_voter"
3692 SET "weight" = "sub_weight_v"
3693 WHERE "issue_id" = "issue_id_p"
3694 AND "member_id" = "issue_delegation_row"."truster_id";
3695 "weight_v" := "weight_v" + "sub_weight_v";
3696 END IF;
3697 END LOOP;
3698 RETURN "weight_v";
3699 END;
3700 $$;
3702 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
3703 ( "issue"."id"%TYPE,
3704 "member"."id"%TYPE,
3705 "delegating_voter"."delegate_member_ids"%TYPE )
3706 IS 'Helper function for "add_vote_delegations" function';
3709 CREATE FUNCTION "add_vote_delegations"
3710 ( "issue_id_p" "issue"."id"%TYPE )
3711 RETURNS VOID
3712 LANGUAGE 'plpgsql' VOLATILE AS $$
3713 DECLARE
3714 "member_id_v" "member"."id"%TYPE;
3715 BEGIN
3716 PERFORM "require_transaction_isolation"();
3717 FOR "member_id_v" IN
3718 SELECT "member_id" FROM "direct_voter"
3719 WHERE "issue_id" = "issue_id_p"
3720 LOOP
3721 UPDATE "direct_voter" SET
3722 "weight" = "weight" + "weight_of_added_vote_delegations"(
3723 "issue_id_p",
3724 "member_id_v",
3725 '{}'
3726 )
3727 WHERE "member_id" = "member_id_v"
3728 AND "issue_id" = "issue_id_p";
3729 END LOOP;
3730 RETURN;
3731 END;
3732 $$;
3734 COMMENT ON FUNCTION "add_vote_delegations"
3735 ( "issue_id_p" "issue"."id"%TYPE )
3736 IS 'Helper function for "close_voting" function';
3739 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
3740 RETURNS VOID
3741 LANGUAGE 'plpgsql' VOLATILE AS $$
3742 DECLARE
3743 "area_id_v" "area"."id"%TYPE;
3744 "unit_id_v" "unit"."id"%TYPE;
3745 "member_id_v" "member"."id"%TYPE;
3746 BEGIN
3747 PERFORM "require_transaction_isolation"();
3748 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
3749 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
3750 -- override protection triggers:
3751 INSERT INTO "temporary_transaction_data" ("key", "value")
3752 VALUES ('override_protection_triggers', TRUE::TEXT);
3753 -- delete timestamp of voting comment:
3754 UPDATE "direct_voter" SET "comment_changed" = NULL
3755 WHERE "issue_id" = "issue_id_p";
3756 -- delete delegating votes (in cases of manual reset of issue state):
3757 DELETE FROM "delegating_voter"
3758 WHERE "issue_id" = "issue_id_p";
3759 -- delete votes from non-privileged voters:
3760 DELETE FROM "direct_voter"
3761 USING (
3762 SELECT
3763 "direct_voter"."member_id"
3764 FROM "direct_voter"
3765 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
3766 LEFT JOIN "privilege"
3767 ON "privilege"."unit_id" = "unit_id_v"
3768 AND "privilege"."member_id" = "direct_voter"."member_id"
3769 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
3770 "member"."active" = FALSE OR
3771 "privilege"."voting_right" ISNULL OR
3772 "privilege"."voting_right" = FALSE
3773 )
3774 ) AS "subquery"
3775 WHERE "direct_voter"."issue_id" = "issue_id_p"
3776 AND "direct_voter"."member_id" = "subquery"."member_id";
3777 -- consider delegations:
3778 UPDATE "direct_voter" SET "weight" = 1
3779 WHERE "issue_id" = "issue_id_p";
3780 PERFORM "add_vote_delegations"("issue_id_p");
3781 -- mark first preferences:
3782 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
3783 FROM (
3784 SELECT
3785 "vote"."initiative_id",
3786 "vote"."member_id",
3787 CASE WHEN "vote"."grade" > 0 THEN
3788 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
3789 ELSE NULL
3790 END AS "first_preference"
3791 FROM "vote"
3792 JOIN "initiative" -- NOTE: due to missing index on issue_id
3793 ON "vote"."issue_id" = "initiative"."issue_id"
3794 JOIN "vote" AS "agg"
3795 ON "initiative"."id" = "agg"."initiative_id"
3796 AND "vote"."member_id" = "agg"."member_id"
3797 GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
3798 ) AS "subquery"
3799 WHERE "vote"."issue_id" = "issue_id_p"
3800 AND "vote"."initiative_id" = "subquery"."initiative_id"
3801 AND "vote"."member_id" = "subquery"."member_id";
3802 -- finish overriding protection triggers (avoids garbage):
3803 DELETE FROM "temporary_transaction_data"
3804 WHERE "key" = 'override_protection_triggers';
3805 -- materialize battle_view:
3806 -- NOTE: "closed" column of issue must be set at this point
3807 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3808 INSERT INTO "battle" (
3809 "issue_id",
3810 "winning_initiative_id", "losing_initiative_id",
3811 "count"
3812 ) SELECT
3813 "issue_id",
3814 "winning_initiative_id", "losing_initiative_id",
3815 "count"
3816 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
3817 -- set voter count:
3818 UPDATE "issue" SET
3819 "voter_count" = (
3820 SELECT coalesce(sum("weight"), 0)
3821 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
3822 )
3823 WHERE "id" = "issue_id_p";
3824 -- copy "positive_votes" and "negative_votes" from "battle" table:
3825 -- NOTE: "first_preference_votes" is set to a default of 0 at this step
3826 UPDATE "initiative" SET
3827 "first_preference_votes" = 0,
3828 "positive_votes" = "battle_win"."count",
3829 "negative_votes" = "battle_lose"."count"
3830 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
3831 WHERE
3832 "battle_win"."issue_id" = "issue_id_p" AND
3833 "battle_win"."winning_initiative_id" = "initiative"."id" AND
3834 "battle_win"."losing_initiative_id" ISNULL AND
3835 "battle_lose"."issue_id" = "issue_id_p" AND
3836 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
3837 "battle_lose"."winning_initiative_id" ISNULL;
3838 -- calculate "first_preference_votes":
3839 -- NOTE: will only set values not equal to zero
3840 UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum"
3841 FROM (
3842 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
3843 FROM "vote" JOIN "direct_voter"
3844 ON "vote"."issue_id" = "direct_voter"."issue_id"
3845 AND "vote"."member_id" = "direct_voter"."member_id"
3846 WHERE "vote"."first_preference"
3847 GROUP BY "vote"."initiative_id"
3848 ) AS "subquery"
3849 WHERE "initiative"."issue_id" = "issue_id_p"
3850 AND "initiative"."admitted"
3851 AND "initiative"."id" = "subquery"."initiative_id";
3852 END;
3853 $$;
3855 COMMENT ON FUNCTION "close_voting"
3856 ( "issue"."id"%TYPE )
3857 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.';
3860 CREATE FUNCTION "defeat_strength"
3861 ( "positive_votes_p" INT4,
3862 "negative_votes_p" INT4,
3863 "defeat_strength_p" "defeat_strength" )
3864 RETURNS INT8
3865 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3866 BEGIN
3867 IF "defeat_strength_p" = 'simple'::"defeat_strength" THEN
3868 IF "positive_votes_p" > "negative_votes_p" THEN
3869 RETURN "positive_votes_p";
3870 ELSE
3871 RETURN 0;
3872 END IF;
3873 ELSE
3874 IF "positive_votes_p" > "negative_votes_p" THEN
3875 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
3876 ELSIF "positive_votes_p" = "negative_votes_p" THEN
3877 RETURN 0;
3878 ELSE
3879 RETURN -1;
3880 END IF;
3881 END IF;
3882 END;
3883 $$;
3885 COMMENT ON FUNCTION "defeat_strength"(INT4, INT4, "defeat_strength") IS 'Calculates defeat strength (INT8!) according to the "defeat_strength" option (see comment on type "defeat_strength")';
3888 CREATE FUNCTION "secondary_link_strength"
3889 ( "initiative1_ord_p" INT4,
3890 "initiative2_ord_p" INT4,
3891 "tie_breaking_p" "tie_breaking" )
3892 RETURNS INT8
3893 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3894 BEGIN
3895 IF "initiative1_ord_p" = "initiative2_ord_p" THEN
3896 RAISE EXCEPTION 'Identical initiative ids passed to "secondary_link_strength" function (should not happen)';
3897 END IF;
3898 RETURN (
3899 CASE WHEN "tie_breaking_p" = 'simple'::"tie_breaking" THEN
3900 0
3901 ELSE
3902 CASE WHEN "initiative1_ord_p" < "initiative2_ord_p" THEN
3903 1::INT8 << 62
3904 ELSE 0 END
3905 +
3906 CASE WHEN "tie_breaking_p" = 'variant2'::"tie_breaking" THEN
3907 ("initiative2_ord_p"::INT8 << 31) - "initiative1_ord_p"::INT8
3908 ELSE
3909 "initiative2_ord_p"::INT8 - ("initiative1_ord_p"::INT8 << 31)
3910 END
3911 END
3912 );
3913 END;
3914 $$;
3916 COMMENT ON FUNCTION "secondary_link_strength"(INT4, INT4, "tie_breaking") IS 'Calculates a secondary criterion for the defeat strength (tie-breaking of the links)';
3919 CREATE TYPE "link_strength" AS (
3920 "primary" INT8,
3921 "secondary" INT8 );
3923 COMMENT ON TYPE "link_strength" IS 'Type to store the defeat strength of a link between two candidates plus a secondary criterion to create unique link strengths between the candidates (needed for tie-breaking ''variant1'' and ''variant2'')';
3926 CREATE FUNCTION "find_best_paths"("matrix_d" "link_strength"[][])
3927 RETURNS "link_strength"[][]
3928 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3929 DECLARE
3930 "dimension_v" INT4;
3931 "matrix_p" "link_strength"[][];
3932 "i" INT4;
3933 "j" INT4;
3934 "k" INT4;
3935 BEGIN
3936 "dimension_v" := array_upper("matrix_d", 1);
3937 "matrix_p" := "matrix_d";
3938 "i" := 1;
3939 LOOP
3940 "j" := 1;
3941 LOOP
3942 IF "i" != "j" THEN
3943 "k" := 1;
3944 LOOP
3945 IF "i" != "k" AND "j" != "k" THEN
3946 IF "matrix_p"["j"]["i"] < "matrix_p"["i"]["k"] THEN
3947 IF "matrix_p"["j"]["i"] > "matrix_p"["j"]["k"] THEN
3948 "matrix_p"["j"]["k"] := "matrix_p"["j"]["i"];
3949 END IF;
3950 ELSE
3951 IF "matrix_p"["i"]["k"] > "matrix_p"["j"]["k"] THEN
3952 "matrix_p"["j"]["k"] := "matrix_p"["i"]["k"];
3953 END IF;
3954 END IF;
3955 END IF;
3956 EXIT WHEN "k" = "dimension_v";
3957 "k" := "k" + 1;
3958 END LOOP;
3959 END IF;
3960 EXIT WHEN "j" = "dimension_v";
3961 "j" := "j" + 1;
3962 END LOOP;
3963 EXIT WHEN "i" = "dimension_v";
3964 "i" := "i" + 1;
3965 END LOOP;
3966 RETURN "matrix_p";
3967 END;
3968 $$;
3970 COMMENT ON FUNCTION "find_best_paths"("link_strength"[][]) IS 'Computes the strengths of the best beat-paths from a square matrix';
3973 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
3974 RETURNS VOID
3975 LANGUAGE 'plpgsql' VOLATILE AS $$
3976 DECLARE
3977 "issue_row" "issue"%ROWTYPE;
3978 "policy_row" "policy"%ROWTYPE;
3979 "dimension_v" INT4;
3980 "matrix_a" INT4[][]; -- absolute votes
3981 "matrix_d" "link_strength"[][]; -- defeat strength (direct)
3982 "matrix_p" "link_strength"[][]; -- defeat strength (best path)
3983 "matrix_t" "link_strength"[][]; -- defeat strength (tie-breaking)
3984 "matrix_f" BOOLEAN[][]; -- forbidden link (tie-breaking)
3985 "matrix_b" BOOLEAN[][]; -- final order (who beats who)
3986 "i" INT4;
3987 "j" INT4;
3988 "m" INT4;
3989 "n" INT4;
3990 "battle_row" "battle"%ROWTYPE;
3991 "rank_ary" INT4[];
3992 "rank_v" INT4;
3993 "initiative_id_v" "initiative"."id"%TYPE;
3994 BEGIN
3995 PERFORM "require_transaction_isolation"();
3996 SELECT * INTO "issue_row"
3997 FROM "issue" WHERE "id" = "issue_id_p";
3998 SELECT * INTO "policy_row"
3999 FROM "policy" WHERE "id" = "issue_row"."policy_id";
4000 SELECT count(1) INTO "dimension_v"
4001 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
4002 -- create "matrix_a" with absolute number of votes in pairwise
4003 -- comparison:
4004 "matrix_a" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
4005 "i" := 1;
4006 "j" := 2;
4007 FOR "battle_row" IN
4008 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
4009 ORDER BY
4010 "winning_initiative_id" NULLS FIRST,
4011 "losing_initiative_id" NULLS FIRST
4012 LOOP
4013 "matrix_a"["i"]["j"] := "battle_row"."count";
4014 IF "j" = "dimension_v" THEN
4015 "i" := "i" + 1;
4016 "j" := 1;
4017 ELSE
4018 "j" := "j" + 1;
4019 IF "j" = "i" THEN
4020 "j" := "j" + 1;
4021 END IF;
4022 END IF;
4023 END LOOP;
4024 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
4025 RAISE EXCEPTION 'Wrong battle count (should not happen)';
4026 END IF;
4027 -- store direct defeat strengths in "matrix_d" using "defeat_strength"
4028 -- and "secondary_link_strength" functions:
4029 "matrix_d" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
4030 "i" := 1;
4031 LOOP
4032 "j" := 1;
4033 LOOP
4034 IF "i" != "j" THEN
4035 "matrix_d"["i"]["j"] := (
4036 "defeat_strength"(
4037 "matrix_a"["i"]["j"],
4038 "matrix_a"["j"]["i"],
4039 "policy_row"."defeat_strength"
4040 ),
4041 "secondary_link_strength"(
4042 "i",
4043 "j",
4044 "policy_row"."tie_breaking"
4045 )
4046 )::"link_strength";
4047 END IF;
4048 EXIT WHEN "j" = "dimension_v";
4049 "j" := "j" + 1;
4050 END LOOP;
4051 EXIT WHEN "i" = "dimension_v";
4052 "i" := "i" + 1;
4053 END LOOP;
4054 -- find best paths:
4055 "matrix_p" := "find_best_paths"("matrix_d");
4056 -- create partial order:
4057 "matrix_b" := array_fill(NULL::BOOLEAN, ARRAY["dimension_v", "dimension_v"]);
4058 "i" := 1;
4059 LOOP
4060 "j" := "i" + 1;
4061 LOOP
4062 IF "i" != "j" THEN
4063 IF "matrix_p"["i"]["j"] > "matrix_p"["j"]["i"] THEN
4064 "matrix_b"["i"]["j"] := TRUE;
4065 "matrix_b"["j"]["i"] := FALSE;
4066 ELSIF "matrix_p"["i"]["j"] < "matrix_p"["j"]["i"] THEN
4067 "matrix_b"["i"]["j"] := FALSE;
4068 "matrix_b"["j"]["i"] := TRUE;
4069 END IF;
4070 END IF;
4071 EXIT WHEN "j" = "dimension_v";
4072 "j" := "j" + 1;
4073 END LOOP;
4074 EXIT WHEN "i" = "dimension_v" - 1;
4075 "i" := "i" + 1;
4076 END LOOP;
4077 -- tie-breaking by forbidding shared weakest links in beat-paths
4078 -- (unless "tie_breaking" is set to 'simple', in which case tie-breaking
4079 -- is performed later by initiative id):
4080 IF "policy_row"."tie_breaking" != 'simple'::"tie_breaking" THEN
4081 "m" := 1;
4082 LOOP
4083 "n" := "m" + 1;
4084 LOOP
4085 -- only process those candidates m and n, which are tied:
4086 IF "matrix_b"["m"]["n"] ISNULL THEN
4087 -- start with beat-paths prior tie-breaking:
4088 "matrix_t" := "matrix_p";
4089 -- start with all links allowed:
4090 "matrix_f" := array_fill(FALSE, ARRAY["dimension_v", "dimension_v"]);
4091 LOOP
4092 -- determine (and forbid) that link that is the weakest link
4093 -- in both the best path from candidate m to candidate n and
4094 -- from candidate n to candidate m:
4095 "i" := 1;
4096 <<forbid_one_link>>
4097 LOOP
4098 "j" := 1;
4099 LOOP
4100 IF "i" != "j" THEN
4101 IF "matrix_d"["i"]["j"] = "matrix_t"["m"]["n"] THEN
4102 "matrix_f"["i"]["j"] := TRUE;
4103 -- exit for performance reasons,
4104 -- as exactly one link will be found:
4105 EXIT forbid_one_link;
4106 END IF;
4107 END IF;
4108 EXIT WHEN "j" = "dimension_v";
4109 "j" := "j" + 1;
4110 END LOOP;
4111 IF "i" = "dimension_v" THEN
4112 RAISE EXCEPTION 'Did not find shared weakest link for tie-breaking (should not happen)';
4113 END IF;
4114 "i" := "i" + 1;
4115 END LOOP;
4116 -- calculate best beat-paths while ignoring forbidden links:
4117 "i" := 1;
4118 LOOP
4119 "j" := 1;
4120 LOOP
4121 IF "i" != "j" THEN
4122 "matrix_t"["i"]["j"] := CASE
4123 WHEN "matrix_f"["i"]["j"]
4124 THEN ((-1::INT8) << 63, 0)::"link_strength" -- worst possible value
4125 ELSE "matrix_d"["i"]["j"] END;
4126 END IF;
4127 EXIT WHEN "j" = "dimension_v";
4128 "j" := "j" + 1;
4129 END LOOP;
4130 EXIT WHEN "i" = "dimension_v";
4131 "i" := "i" + 1;
4132 END LOOP;
4133 "matrix_t" := "find_best_paths"("matrix_t");
4134 -- extend partial order, if tie-breaking was successful:
4135 IF "matrix_t"["m"]["n"] > "matrix_t"["n"]["m"] THEN
4136 "matrix_b"["m"]["n"] := TRUE;
4137 "matrix_b"["n"]["m"] := FALSE;
4138 EXIT;
4139 ELSIF "matrix_t"["m"]["n"] < "matrix_t"["n"]["m"] THEN
4140 "matrix_b"["m"]["n"] := FALSE;
4141 "matrix_b"["n"]["m"] := TRUE;
4142 EXIT;
4143 END IF;
4144 END LOOP;
4145 END IF;
4146 EXIT WHEN "n" = "dimension_v";
4147 "n" := "n" + 1;
4148 END LOOP;
4149 EXIT WHEN "m" = "dimension_v" - 1;
4150 "m" := "m" + 1;
4151 END LOOP;
4152 END IF;
4153 -- store a unique ranking in "rank_ary":
4154 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
4155 "rank_v" := 1;
4156 LOOP
4157 "i" := 1;
4158 <<assign_next_rank>>
4159 LOOP
4160 IF "rank_ary"["i"] ISNULL THEN
4161 "j" := 1;
4162 LOOP
4163 IF
4164 "i" != "j" AND
4165 "rank_ary"["j"] ISNULL AND
4166 ( "matrix_b"["j"]["i"] OR
4167 -- tie-breaking by "id"
4168 ( "matrix_b"["j"]["i"] ISNULL AND
4169 "j" < "i" ) )
4170 THEN
4171 -- someone else is better
4172 EXIT;
4173 END IF;
4174 IF "j" = "dimension_v" THEN
4175 -- noone is better
4176 "rank_ary"["i"] := "rank_v";
4177 EXIT assign_next_rank;
4178 END IF;
4179 "j" := "j" + 1;
4180 END LOOP;
4181 END IF;
4182 "i" := "i" + 1;
4183 IF "i" > "dimension_v" THEN
4184 RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)';
4185 END IF;
4186 END LOOP;
4187 EXIT WHEN "rank_v" = "dimension_v";
4188 "rank_v" := "rank_v" + 1;
4189 END LOOP;
4190 -- write preliminary results:
4191 "i" := 2; -- omit status quo with "i" = 1
4192 FOR "initiative_id_v" IN
4193 SELECT "id" FROM "initiative"
4194 WHERE "issue_id" = "issue_id_p" AND "admitted"
4195 ORDER BY "id"
4196 LOOP
4197 UPDATE "initiative" SET
4198 "direct_majority" =
4199 CASE WHEN "policy_row"."direct_majority_strict" THEN
4200 "positive_votes" * "policy_row"."direct_majority_den" >
4201 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
4202 ELSE
4203 "positive_votes" * "policy_row"."direct_majority_den" >=
4204 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
4205 END
4206 AND "positive_votes" >= "policy_row"."direct_majority_positive"
4207 AND "issue_row"."voter_count"-"negative_votes" >=
4208 "policy_row"."direct_majority_non_negative",
4209 "indirect_majority" =
4210 CASE WHEN "policy_row"."indirect_majority_strict" THEN
4211 "positive_votes" * "policy_row"."indirect_majority_den" >
4212 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
4213 ELSE
4214 "positive_votes" * "policy_row"."indirect_majority_den" >=
4215 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
4216 END
4217 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
4218 AND "issue_row"."voter_count"-"negative_votes" >=
4219 "policy_row"."indirect_majority_non_negative",
4220 "schulze_rank" = "rank_ary"["i"],
4221 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1],
4222 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"[1],
4223 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"[1],
4224 "reverse_beat_path" = CASE WHEN "policy_row"."defeat_strength" = 'simple'::"defeat_strength"
4225 THEN NULL
4226 ELSE "matrix_p"[1]["i"]."primary" >= 0 END,
4227 "eligible" = FALSE,
4228 "winner" = FALSE,
4229 "rank" = NULL -- NOTE: in cases of manual reset of issue state
4230 WHERE "id" = "initiative_id_v";
4231 "i" := "i" + 1;
4232 END LOOP;
4233 IF "i" != "dimension_v" + 1 THEN
4234 RAISE EXCEPTION 'Wrong winner count (should not happen)';
4235 END IF;
4236 -- take indirect majorities into account:
4237 LOOP
4238 UPDATE "initiative" SET "indirect_majority" = TRUE
4239 FROM (
4240 SELECT "new_initiative"."id" AS "initiative_id"
4241 FROM "initiative" "old_initiative"
4242 JOIN "initiative" "new_initiative"
4243 ON "new_initiative"."issue_id" = "issue_id_p"
4244 AND "new_initiative"."indirect_majority" = FALSE
4245 JOIN "battle" "battle_win"
4246 ON "battle_win"."issue_id" = "issue_id_p"
4247 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
4248 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
4249 JOIN "battle" "battle_lose"
4250 ON "battle_lose"."issue_id" = "issue_id_p"
4251 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
4252 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
4253 WHERE "old_initiative"."issue_id" = "issue_id_p"
4254 AND "old_initiative"."indirect_majority" = TRUE
4255 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
4256 "battle_win"."count" * "policy_row"."indirect_majority_den" >
4257 "policy_row"."indirect_majority_num" *
4258 ("battle_win"."count"+"battle_lose"."count")
4259 ELSE
4260 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
4261 "policy_row"."indirect_majority_num" *
4262 ("battle_win"."count"+"battle_lose"."count")
4263 END
4264 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
4265 AND "issue_row"."voter_count"-"battle_lose"."count" >=
4266 "policy_row"."indirect_majority_non_negative"
4267 ) AS "subquery"
4268 WHERE "id" = "subquery"."initiative_id";
4269 EXIT WHEN NOT FOUND;
4270 END LOOP;
4271 -- set "multistage_majority" for remaining matching initiatives:
4272 UPDATE "initiative" SET "multistage_majority" = TRUE
4273 FROM (
4274 SELECT "losing_initiative"."id" AS "initiative_id"
4275 FROM "initiative" "losing_initiative"
4276 JOIN "initiative" "winning_initiative"
4277 ON "winning_initiative"."issue_id" = "issue_id_p"
4278 AND "winning_initiative"."admitted"
4279 JOIN "battle" "battle_win"
4280 ON "battle_win"."issue_id" = "issue_id_p"
4281 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
4282 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
4283 JOIN "battle" "battle_lose"
4284 ON "battle_lose"."issue_id" = "issue_id_p"
4285 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
4286 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
4287 WHERE "losing_initiative"."issue_id" = "issue_id_p"
4288 AND "losing_initiative"."admitted"
4289 AND "winning_initiative"."schulze_rank" <
4290 "losing_initiative"."schulze_rank"
4291 AND "battle_win"."count" > "battle_lose"."count"
4292 AND (
4293 "battle_win"."count" > "winning_initiative"."positive_votes" OR
4294 "battle_lose"."count" < "losing_initiative"."negative_votes" )
4295 ) AS "subquery"
4296 WHERE "id" = "subquery"."initiative_id";
4297 -- mark eligible initiatives:
4298 UPDATE "initiative" SET "eligible" = TRUE
4299 WHERE "issue_id" = "issue_id_p"
4300 AND "initiative"."direct_majority"
4301 AND "initiative"."indirect_majority"
4302 AND "initiative"."better_than_status_quo"
4303 AND (
4304 "policy_row"."no_multistage_majority" = FALSE OR
4305 "initiative"."multistage_majority" = FALSE )
4306 AND (
4307 "policy_row"."no_reverse_beat_path" = FALSE OR
4308 coalesce("initiative"."reverse_beat_path", FALSE) = FALSE );
4309 -- mark final winner:
4310 UPDATE "initiative" SET "winner" = TRUE
4311 FROM (
4312 SELECT "id" AS "initiative_id"
4313 FROM "initiative"
4314 WHERE "issue_id" = "issue_id_p" AND "eligible"
4315 ORDER BY
4316 "schulze_rank",
4317 "id"
4318 LIMIT 1
4319 ) AS "subquery"
4320 WHERE "id" = "subquery"."initiative_id";
4321 -- write (final) ranks:
4322 "rank_v" := 1;
4323 FOR "initiative_id_v" IN
4324 SELECT "id"
4325 FROM "initiative"
4326 WHERE "issue_id" = "issue_id_p" AND "admitted"
4327 ORDER BY
4328 "winner" DESC,
4329 "eligible" DESC,
4330 "schulze_rank",
4331 "id"
4332 LOOP
4333 UPDATE "initiative" SET "rank" = "rank_v"
4334 WHERE "id" = "initiative_id_v";
4335 "rank_v" := "rank_v" + 1;
4336 END LOOP;
4337 -- set schulze rank of status quo and mark issue as finished:
4338 UPDATE "issue" SET
4339 "status_quo_schulze_rank" = "rank_ary"[1],
4340 "state" =
4341 CASE WHEN EXISTS (
4342 SELECT NULL FROM "initiative"
4343 WHERE "issue_id" = "issue_id_p" AND "winner"
4344 ) THEN
4345 'finished_with_winner'::"issue_state"
4346 ELSE
4347 'finished_without_winner'::"issue_state"
4348 END,
4349 "closed" = "phase_finished",
4350 "phase_finished" = NULL
4351 WHERE "id" = "issue_id_p";
4352 RETURN;
4353 END;
4354 $$;
4356 COMMENT ON FUNCTION "calculate_ranks"
4357 ( "issue"."id"%TYPE )
4358 IS 'Determine ranking (Votes have to be counted first)';
4362 -----------------------------
4363 -- Automatic state changes --
4364 -----------------------------
4367 CREATE TYPE "check_issue_persistence" AS (
4368 "state" "issue_state",
4369 "phase_finished" BOOLEAN,
4370 "issue_revoked" BOOLEAN,
4371 "snapshot_created" BOOLEAN,
4372 "harmonic_weights_set" BOOLEAN,
4373 "closed_voting" BOOLEAN );
4375 COMMENT ON TYPE "check_issue_persistence" IS 'Type of data returned by "check_issue" function, to be passed to subsequent calls of the same function';
4378 CREATE FUNCTION "check_issue"
4379 ( "issue_id_p" "issue"."id"%TYPE,
4380 "persist" "check_issue_persistence" )
4381 RETURNS "check_issue_persistence"
4382 LANGUAGE 'plpgsql' VOLATILE AS $$
4383 DECLARE
4384 "issue_row" "issue"%ROWTYPE;
4385 "policy_row" "policy"%ROWTYPE;
4386 "initiative_row" "initiative"%ROWTYPE;
4387 "state_v" "issue_state";
4388 BEGIN
4389 PERFORM "require_transaction_isolation"();
4390 IF "persist" ISNULL THEN
4391 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
4392 FOR UPDATE;
4393 IF "issue_row"."closed" NOTNULL THEN
4394 RETURN NULL;
4395 END IF;
4396 "persist"."state" := "issue_row"."state";
4397 IF
4398 ( "issue_row"."state" = 'admission' AND now() >=
4399 "issue_row"."created" + "issue_row"."max_admission_time" ) OR
4400 ( "issue_row"."state" = 'discussion' AND now() >=
4401 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
4402 ( "issue_row"."state" = 'verification' AND now() >=
4403 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
4404 ( "issue_row"."state" = 'voting' AND now() >=
4405 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
4406 THEN
4407 "persist"."phase_finished" := TRUE;
4408 ELSE
4409 "persist"."phase_finished" := FALSE;
4410 END IF;
4411 IF
4412 NOT EXISTS (
4413 -- all initiatives are revoked
4414 SELECT NULL FROM "initiative"
4415 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
4416 ) AND (
4417 -- and issue has not been accepted yet
4418 "persist"."state" = 'admission' OR
4419 -- or verification time has elapsed
4420 ( "persist"."state" = 'verification' AND
4421 "persist"."phase_finished" ) OR
4422 -- or no initiatives have been revoked lately
4423 NOT EXISTS (
4424 SELECT NULL FROM "initiative"
4425 WHERE "issue_id" = "issue_id_p"
4426 AND now() < "revoked" + "issue_row"."verification_time"
4427 )
4428 )
4429 THEN
4430 "persist"."issue_revoked" := TRUE;
4431 ELSE
4432 "persist"."issue_revoked" := FALSE;
4433 END IF;
4434 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
4435 UPDATE "issue" SET "phase_finished" = now()
4436 WHERE "id" = "issue_row"."id";
4437 RETURN "persist";
4438 ELSIF
4439 "persist"."state" IN ('admission', 'discussion', 'verification')
4440 THEN
4441 RETURN "persist";
4442 ELSE
4443 RETURN NULL;
4444 END IF;
4445 END IF;
4446 IF
4447 "persist"."state" IN ('admission', 'discussion', 'verification') AND
4448 coalesce("persist"."snapshot_created", FALSE) = FALSE
4449 THEN
4450 PERFORM "create_snapshot"("issue_id_p");
4451 "persist"."snapshot_created" = TRUE;
4452 IF "persist"."phase_finished" THEN
4453 IF "persist"."state" = 'admission' THEN
4454 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
4455 ELSIF "persist"."state" = 'discussion' THEN
4456 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
4457 ELSIF "persist"."state" = 'verification' THEN
4458 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
4459 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4460 SELECT * INTO "policy_row" FROM "policy"
4461 WHERE "id" = "issue_row"."policy_id";
4462 FOR "initiative_row" IN
4463 SELECT * FROM "initiative"
4464 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
4465 FOR UPDATE
4466 LOOP
4467 IF
4468 "initiative_row"."polling" OR (
4469 "initiative_row"."satisfied_supporter_count" > 0 AND
4470 "initiative_row"."satisfied_supporter_count" *
4471 "policy_row"."initiative_quorum_den" >=
4472 "issue_row"."population" * "policy_row"."initiative_quorum_num"
4473 )
4474 THEN
4475 UPDATE "initiative" SET "admitted" = TRUE
4476 WHERE "id" = "initiative_row"."id";
4477 ELSE
4478 UPDATE "initiative" SET "admitted" = FALSE
4479 WHERE "id" = "initiative_row"."id";
4480 END IF;
4481 END LOOP;
4482 END IF;
4483 END IF;
4484 RETURN "persist";
4485 END IF;
4486 IF
4487 "persist"."state" IN ('admission', 'discussion', 'verification') AND
4488 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
4489 THEN
4490 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
4491 "persist"."harmonic_weights_set" = TRUE;
4492 IF
4493 "persist"."phase_finished" OR
4494 "persist"."issue_revoked" OR
4495 "persist"."state" = 'admission'
4496 THEN
4497 RETURN "persist";
4498 ELSE
4499 RETURN NULL;
4500 END IF;
4501 END IF;
4502 IF "persist"."issue_revoked" THEN
4503 IF "persist"."state" = 'admission' THEN
4504 "state_v" := 'canceled_revoked_before_accepted';
4505 ELSIF "persist"."state" = 'discussion' THEN
4506 "state_v" := 'canceled_after_revocation_during_discussion';
4507 ELSIF "persist"."state" = 'verification' THEN
4508 "state_v" := 'canceled_after_revocation_during_verification';
4509 END IF;
4510 UPDATE "issue" SET
4511 "state" = "state_v",
4512 "closed" = "phase_finished",
4513 "phase_finished" = NULL
4514 WHERE "id" = "issue_id_p";
4515 RETURN NULL;
4516 END IF;
4517 IF "persist"."state" = 'admission' THEN
4518 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
4519 FOR UPDATE;
4520 SELECT * INTO "policy_row"
4521 FROM "policy" WHERE "id" = "issue_row"."policy_id";
4522 IF
4523 ( now() >=
4524 "issue_row"."created" + "issue_row"."min_admission_time" ) AND
4525 EXISTS (
4526 SELECT NULL FROM "initiative"
4527 WHERE "issue_id" = "issue_id_p"
4528 AND "supporter_count" > 0
4529 AND "supporter_count" * "policy_row"."issue_quorum_den"
4530 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
4531 )
4532 THEN
4533 UPDATE "issue" SET
4534 "state" = 'discussion',
4535 "accepted" = coalesce("phase_finished", now()),
4536 "phase_finished" = NULL
4537 WHERE "id" = "issue_id_p";
4538 ELSIF "issue_row"."phase_finished" NOTNULL THEN
4539 UPDATE "issue" SET
4540 "state" = 'canceled_issue_not_accepted',
4541 "closed" = "phase_finished",
4542 "phase_finished" = NULL
4543 WHERE "id" = "issue_id_p";
4544 END IF;
4545 RETURN NULL;
4546 END IF;
4547 IF "persist"."phase_finished" THEN
4548 IF "persist"."state" = 'discussion' THEN
4549 UPDATE "issue" SET
4550 "state" = 'verification',
4551 "half_frozen" = "phase_finished",
4552 "phase_finished" = NULL
4553 WHERE "id" = "issue_id_p";
4554 RETURN NULL;
4555 END IF;
4556 IF "persist"."state" = 'verification' THEN
4557 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
4558 FOR UPDATE;
4559 SELECT * INTO "policy_row" FROM "policy"
4560 WHERE "id" = "issue_row"."policy_id";
4561 IF EXISTS (
4562 SELECT NULL FROM "initiative"
4563 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
4564 ) THEN
4565 UPDATE "issue" SET
4566 "state" = 'voting',
4567 "fully_frozen" = "phase_finished",
4568 "phase_finished" = NULL
4569 WHERE "id" = "issue_id_p";
4570 ELSE
4571 UPDATE "issue" SET
4572 "state" = 'canceled_no_initiative_admitted',
4573 "fully_frozen" = "phase_finished",
4574 "closed" = "phase_finished",
4575 "phase_finished" = NULL
4576 WHERE "id" = "issue_id_p";
4577 -- NOTE: The following DELETE statements have effect only when
4578 -- issue state has been manipulated
4579 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
4580 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
4581 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
4582 END IF;
4583 RETURN NULL;
4584 END IF;
4585 IF "persist"."state" = 'voting' THEN
4586 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
4587 PERFORM "close_voting"("issue_id_p");
4588 "persist"."closed_voting" = TRUE;
4589 RETURN "persist";
4590 END IF;
4591 PERFORM "calculate_ranks"("issue_id_p");
4592 RETURN NULL;
4593 END IF;
4594 END IF;
4595 RAISE WARNING 'should not happen';
4596 RETURN NULL;
4597 END;
4598 $$;
4600 COMMENT ON FUNCTION "check_issue"
4601 ( "issue"."id"%TYPE,
4602 "check_issue_persistence" )
4603 IS 'Precalculate supporter counts etc. for a given issue, and check, if status change is required, and perform the status change when necessary; Function must be called multiple times with the previous result as second parameter, until the result is NULL (see source code of function "check_everything")';
4606 CREATE FUNCTION "check_everything"()
4607 RETURNS VOID
4608 LANGUAGE 'plpgsql' VOLATILE AS $$
4609 DECLARE
4610 "issue_id_v" "issue"."id"%TYPE;
4611 "persist_v" "check_issue_persistence";
4612 BEGIN
4613 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
4614 DELETE FROM "expired_session";
4615 PERFORM "check_activity"();
4616 PERFORM "calculate_member_counts"();
4617 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
4618 "persist_v" := NULL;
4619 LOOP
4620 "persist_v" := "check_issue"("issue_id_v", "persist_v");
4621 EXIT WHEN "persist_v" ISNULL;
4622 END LOOP;
4623 END LOOP;
4624 RETURN;
4625 END;
4626 $$;
4628 COMMENT ON FUNCTION "check_everything"() IS 'Amongst other regular tasks this function performs "check_issue" for every open issue. Use this function only for development and debugging purposes, as you may run into locking and/or serialization problems in productive environments.';
4632 ----------------------
4633 -- Deletion of data --
4634 ----------------------
4637 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
4638 RETURNS VOID
4639 LANGUAGE 'plpgsql' VOLATILE AS $$
4640 BEGIN
4641 IF EXISTS (
4642 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
4643 ) THEN
4644 -- override protection triggers:
4645 INSERT INTO "temporary_transaction_data" ("key", "value")
4646 VALUES ('override_protection_triggers', TRUE::TEXT);
4647 -- clean data:
4648 DELETE FROM "delegating_voter"
4649 WHERE "issue_id" = "issue_id_p";
4650 DELETE FROM "direct_voter"
4651 WHERE "issue_id" = "issue_id_p";
4652 DELETE FROM "delegating_interest_snapshot"
4653 WHERE "issue_id" = "issue_id_p";
4654 DELETE FROM "direct_interest_snapshot"
4655 WHERE "issue_id" = "issue_id_p";
4656 DELETE FROM "delegating_population_snapshot"
4657 WHERE "issue_id" = "issue_id_p";
4658 DELETE FROM "direct_population_snapshot"
4659 WHERE "issue_id" = "issue_id_p";
4660 DELETE FROM "non_voter"
4661 WHERE "issue_id" = "issue_id_p";
4662 DELETE FROM "delegation"
4663 WHERE "issue_id" = "issue_id_p";
4664 DELETE FROM "supporter"
4665 USING "initiative" -- NOTE: due to missing index on issue_id
4666 WHERE "initiative"."issue_id" = "issue_id_p"
4667 AND "supporter"."initiative_id" = "initiative_id";
4668 -- mark issue as cleaned:
4669 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
4670 -- finish overriding protection triggers (avoids garbage):
4671 DELETE FROM "temporary_transaction_data"
4672 WHERE "key" = 'override_protection_triggers';
4673 END IF;
4674 RETURN;
4675 END;
4676 $$;
4678 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
4681 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
4682 RETURNS VOID
4683 LANGUAGE 'plpgsql' VOLATILE AS $$
4684 BEGIN
4685 UPDATE "member" SET
4686 "last_login" = NULL,
4687 "last_delegation_check" = NULL,
4688 "login" = NULL,
4689 "password" = NULL,
4690 "authority" = NULL,
4691 "authority_uid" = NULL,
4692 "authority_login" = NULL,
4693 "locked" = TRUE,
4694 "active" = FALSE,
4695 "notify_email" = NULL,
4696 "notify_email_unconfirmed" = NULL,
4697 "notify_email_secret" = NULL,
4698 "notify_email_secret_expiry" = NULL,
4699 "notify_email_lock_expiry" = NULL,
4700 "login_recovery_expiry" = NULL,
4701 "password_reset_secret" = NULL,
4702 "password_reset_secret_expiry" = NULL,
4703 "organizational_unit" = NULL,
4704 "internal_posts" = NULL,
4705 "realname" = NULL,
4706 "birthday" = NULL,
4707 "address" = NULL,
4708 "email" = NULL,
4709 "xmpp_address" = NULL,
4710 "website" = NULL,
4711 "phone" = NULL,
4712 "mobile_phone" = NULL,
4713 "profession" = NULL,
4714 "external_memberships" = NULL,
4715 "external_posts" = NULL,
4716 "statement" = NULL
4717 WHERE "id" = "member_id_p";
4718 -- "text_search_data" is updated by triggers
4719 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
4720 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
4721 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
4722 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
4723 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
4724 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
4725 DELETE FROM "session" WHERE "member_id" = "member_id_p";
4726 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
4727 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
4728 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
4729 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
4730 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
4731 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
4732 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
4733 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
4734 DELETE FROM "direct_voter" USING "issue"
4735 WHERE "direct_voter"."issue_id" = "issue"."id"
4736 AND "issue"."closed" ISNULL
4737 AND "member_id" = "member_id_p";
4738 RETURN;
4739 END;
4740 $$;
4742 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)';
4745 CREATE FUNCTION "delete_private_data"()
4746 RETURNS VOID
4747 LANGUAGE 'plpgsql' VOLATILE AS $$
4748 BEGIN
4749 DELETE FROM "temporary_transaction_data";
4750 DELETE FROM "member" WHERE "activated" ISNULL;
4751 UPDATE "member" SET
4752 "invite_code" = NULL,
4753 "invite_code_expiry" = NULL,
4754 "admin_comment" = NULL,
4755 "last_login" = NULL,
4756 "last_delegation_check" = NULL,
4757 "login" = NULL,
4758 "password" = NULL,
4759 "authority" = NULL,
4760 "authority_uid" = NULL,
4761 "authority_login" = NULL,
4762 "lang" = NULL,
4763 "notify_email" = NULL,
4764 "notify_email_unconfirmed" = NULL,
4765 "notify_email_secret" = NULL,
4766 "notify_email_secret_expiry" = NULL,
4767 "notify_email_lock_expiry" = NULL,
4768 "notify_level" = NULL,
4769 "login_recovery_expiry" = NULL,
4770 "password_reset_secret" = NULL,
4771 "password_reset_secret_expiry" = NULL,
4772 "organizational_unit" = NULL,
4773 "internal_posts" = NULL,
4774 "realname" = NULL,
4775 "birthday" = NULL,
4776 "address" = NULL,
4777 "email" = NULL,
4778 "xmpp_address" = NULL,
4779 "website" = NULL,
4780 "phone" = NULL,
4781 "mobile_phone" = NULL,
4782 "profession" = NULL,
4783 "external_memberships" = NULL,
4784 "external_posts" = NULL,
4785 "formatting_engine" = NULL,
4786 "statement" = NULL;
4787 -- "text_search_data" is updated by triggers
4788 DELETE FROM "setting";
4789 DELETE FROM "setting_map";
4790 DELETE FROM "member_relation_setting";
4791 DELETE FROM "member_image";
4792 DELETE FROM "contact";
4793 DELETE FROM "ignored_member";
4794 DELETE FROM "session";
4795 DELETE FROM "area_setting";
4796 DELETE FROM "issue_setting";
4797 DELETE FROM "ignored_initiative";
4798 DELETE FROM "initiative_setting";
4799 DELETE FROM "suggestion_setting";
4800 DELETE FROM "non_voter";
4801 DELETE FROM "direct_voter" USING "issue"
4802 WHERE "direct_voter"."issue_id" = "issue"."id"
4803 AND "issue"."closed" ISNULL;
4804 RETURN;
4805 END;
4806 $$;
4808 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. See source code to see which data is deleted. If you need a different behaviour, copy this function and modify lf_export accordingly, to avoid data-leaks after updating.';
4812 COMMIT;