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