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