liquid_feedback_core
view core.sql @ 463:88b47f0dacde
Use ERRCODE and HINT when raising exceptions
| author | jbe |
|---|---|
| date | Fri Mar 18 23:34:45 2016 +0100 (2016-03-18) |
| parents | e9525f069607 |
| children | 913f4d0b1e6e |
line source
2 -- Execute the following command manually for PostgreSQL prior version 9.0:
3 -- CREATE LANGUAGE plpgsql;
5 -- NOTE: In PostgreSQL every UNIQUE constraint implies creation of an index
7 BEGIN;
9 CREATE VIEW "liquid_feedback_version" AS
10 SELECT * FROM (VALUES ('3.2.0', 3, 2, 0))
11 AS "subquery"("string", "major", "minor", "revision");
15 ----------------------
16 -- Full text search --
17 ----------------------
20 CREATE FUNCTION "text_search_query"("query_text_p" TEXT)
21 RETURNS TSQUERY
22 LANGUAGE 'plpgsql' IMMUTABLE AS $$
23 BEGIN
24 RETURN plainto_tsquery('pg_catalog.simple', "query_text_p");
25 END;
26 $$;
28 COMMENT ON FUNCTION "text_search_query"(TEXT) IS 'Usage: WHERE "text_search_data" @@ "text_search_query"(''<user query>'')';
31 CREATE FUNCTION "highlight"
32 ( "body_p" TEXT,
33 "query_text_p" TEXT )
34 RETURNS TEXT
35 LANGUAGE 'plpgsql' IMMUTABLE AS $$
36 BEGIN
37 RETURN ts_headline(
38 'pg_catalog.simple',
39 replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'),
40 "text_search_query"("query_text_p"),
41 'StartSel=* StopSel=* HighlightAll=TRUE' );
42 END;
43 $$;
45 COMMENT ON FUNCTION "highlight"
46 ( "body_p" TEXT,
47 "query_text_p" TEXT )
48 IS 'For a given a user query this function encapsulates all matches with asterisks. Asterisks and backslashes being already present are preceeded with one extra backslash.';
52 -------------------------
53 -- Tables and indicies --
54 -------------------------
57 CREATE TABLE "temporary_transaction_data" (
58 PRIMARY KEY ("txid", "key"),
59 "txid" INT8 DEFAULT txid_current(),
60 "key" TEXT,
61 "value" TEXT NOT NULL );
63 COMMENT ON TABLE "temporary_transaction_data" IS 'Table to store temporary transaction data; shall be emptied before a transaction is committed';
65 COMMENT ON COLUMN "temporary_transaction_data"."txid" IS 'Value returned by function txid_current(); should be added to WHERE clause, when doing SELECT on this table, but ignored when doing DELETE on this table';
68 CREATE TABLE "system_setting" (
69 "member_ttl" INTERVAL );
70 CREATE UNIQUE INDEX "system_setting_singleton_idx" ON "system_setting" ((1));
72 COMMENT ON TABLE "system_setting" IS 'This table contains only one row with different settings in each column.';
73 COMMENT ON INDEX "system_setting_singleton_idx" IS 'This index ensures that "system_setting" only contains one row maximum.';
75 COMMENT ON COLUMN "system_setting"."member_ttl" IS 'Time after members get their "active" flag set to FALSE, if they do not show any activity.';
78 CREATE TABLE "contingent" (
79 PRIMARY KEY ("polling", "time_frame"),
80 "polling" BOOLEAN,
81 "time_frame" INTERVAL,
82 "text_entry_limit" INT4,
83 "initiative_limit" INT4 );
85 COMMENT ON TABLE "contingent" IS 'Amount of text entries or initiatives a user may create within a given time frame. Only one row needs to be fulfilled for a member to be allowed to post. This table must not be empty.';
87 COMMENT ON COLUMN "contingent"."polling" IS 'Determines if settings are for creating initiatives and new drafts of initiatives with "polling" flag set';
88 COMMENT ON COLUMN "contingent"."text_entry_limit" IS 'Number of new drafts or suggestions to be submitted by each member within the given time frame';
89 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
92 CREATE TYPE "notify_level" AS ENUM
93 ('none', 'voting', 'verification', 'discussion', 'all');
95 COMMENT ON TYPE "notify_level" IS 'Level of notification: ''none'' = no notifications, ''voting'' = notifications about finished issues and issues in voting, ''verification'' = notifications about finished issues, issues in voting and verification phase, ''discussion'' = notifications about everything except issues in admission phase, ''all'' = notifications about everything';
98 CREATE TABLE "member" (
99 "id" SERIAL4 PRIMARY KEY,
100 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
101 "invite_code" TEXT UNIQUE,
102 "invite_code_expiry" TIMESTAMPTZ,
103 "admin_comment" TEXT,
104 "activated" TIMESTAMPTZ,
105 "last_activity" DATE,
106 "last_login" TIMESTAMPTZ,
107 "last_delegation_check" TIMESTAMPTZ,
108 "login" TEXT UNIQUE,
109 "password" TEXT,
110 "authority" TEXT,
111 "authority_uid" TEXT,
112 "authority_login" TEXT,
113 "locked" BOOLEAN NOT NULL DEFAULT FALSE,
114 "active" BOOLEAN NOT NULL DEFAULT FALSE,
115 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
116 "lang" TEXT,
117 "notify_email" TEXT,
118 "notify_email_unconfirmed" TEXT,
119 "notify_email_secret" TEXT UNIQUE,
120 "notify_email_secret_expiry" TIMESTAMPTZ,
121 "notify_email_lock_expiry" TIMESTAMPTZ,
122 "disable_notifications" BOOLEAN NOT NULL DEFAULT FALSE,
123 "login_recovery_expiry" TIMESTAMPTZ,
124 "password_reset_secret" TEXT UNIQUE,
125 "password_reset_secret_expiry" TIMESTAMPTZ,
126 "name" TEXT UNIQUE,
127 "identification" TEXT UNIQUE,
128 "authentication" TEXT,
129 "organizational_unit" TEXT,
130 "internal_posts" TEXT,
131 "realname" TEXT,
132 "birthday" DATE,
133 "address" TEXT,
134 "email" TEXT,
135 "xmpp_address" TEXT,
136 "website" TEXT,
137 "phone" TEXT,
138 "mobile_phone" TEXT,
139 "profession" TEXT,
140 "external_memberships" TEXT,
141 "external_posts" TEXT,
142 "formatting_engine" TEXT,
143 "statement" TEXT,
144 "text_search_data" TSVECTOR,
145 CONSTRAINT "active_requires_activated_and_last_activity"
146 CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)),
147 CONSTRAINT "authority_requires_uid_and_vice_versa"
148 CHECK (("authority" NOTNULL) = ("authority_uid" NOTNULL)),
149 CONSTRAINT "authority_uid_unique_per_authority"
150 UNIQUE ("authority", "authority_uid"),
151 CONSTRAINT "authority_login_requires_authority"
152 CHECK ("authority" NOTNULL OR "authority_login" ISNULL),
153 CONSTRAINT "name_not_null_if_activated"
154 CHECK ("activated" ISNULL OR "name" NOTNULL) );
155 CREATE INDEX "member_authority_login_idx" ON "member" ("authority_login");
156 CREATE INDEX "member_active_idx" ON "member" ("active");
157 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
158 CREATE TRIGGER "update_text_search_data"
159 BEFORE INSERT OR UPDATE ON "member"
160 FOR EACH ROW EXECUTE PROCEDURE
161 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
162 "name", "identification", "organizational_unit", "internal_posts",
163 "realname", "external_memberships", "external_posts", "statement" );
165 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
167 COMMENT ON COLUMN "member"."created" IS 'Creation of member record and/or invite code';
168 COMMENT ON COLUMN "member"."invite_code" IS 'Optional invite code, to allow a member to initialize his/her account the first time';
169 COMMENT ON COLUMN "member"."invite_code_expiry" IS 'Expiry data/time for "invite_code"';
170 COMMENT ON COLUMN "member"."admin_comment" IS 'Hidden comment for administrative purposes';
171 COMMENT ON COLUMN "member"."activated" IS 'Timestamp of first activation of account (i.e. usage of "invite_code"); required to be set for "active" members';
172 COMMENT ON COLUMN "member"."last_activity" IS 'Date of last activity of member; required to be set for "active" members';
173 COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login';
174 COMMENT ON COLUMN "member"."last_delegation_check" IS 'Timestamp of last delegation check (i.e. confirmation of all unit and area delegations)';
175 COMMENT ON COLUMN "member"."login" IS 'Login name';
176 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
177 COMMENT ON COLUMN "member"."authority" IS 'NULL if LiquidFeedback Core is authoritative for the member account; otherwise a string that indicates the source/authority of the external account (e.g. ''LDAP'' for an LDAP account)';
178 COMMENT ON COLUMN "member"."authority_uid" IS 'Unique identifier (unique per "authority") that allows to identify an external account (e.g. even if the login name changes)';
179 COMMENT ON COLUMN "member"."authority_login" IS 'Login name for external accounts (field is not unique!)';
180 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
181 COMMENT ON COLUMN "member"."active" IS 'Memberships, support and votes are taken into account when corresponding members are marked as active. Automatically set to FALSE, if "last_activity" is older than "system_setting"."member_ttl".';
182 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
183 COMMENT ON COLUMN "member"."lang" IS 'Language code of the preferred language of the member';
184 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
185 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
186 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
187 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
188 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
189 COMMENT ON COLUMN "member"."disable_notifications" IS 'TRUE if member does not want to receive notifications';
190 COMMENT ON COLUMN "member"."login_recovery_expiry" IS 'Date/time after which another login recovery attempt is allowed';
191 COMMENT ON COLUMN "member"."password_reset_secret" IS 'Secret string sent via e-mail for password recovery';
192 COMMENT ON COLUMN "member"."password_reset_secret_expiry" IS 'Date/time until the password recovery secret is valid, and date/time after which another password recovery attempt is allowed';
193 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member, may be NULL if account has not been activated yet';
194 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
195 COMMENT ON COLUMN "member"."authentication" IS 'Information about how this member was authenticated';
196 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
197 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
198 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
199 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
200 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
201 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
202 COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"';
203 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his/her profile';
206 CREATE TABLE "member_history" (
207 "id" SERIAL8 PRIMARY KEY,
208 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
209 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
210 "active" BOOLEAN NOT NULL,
211 "name" TEXT NOT NULL );
212 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
214 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
216 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
217 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
220 CREATE TABLE "rendered_member_statement" (
221 PRIMARY KEY ("member_id", "format"),
222 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
223 "format" TEXT,
224 "content" TEXT NOT NULL );
226 COMMENT ON TABLE "rendered_member_statement" IS 'This table may be used by frontends to cache "rendered" member statements (e.g. HTML output generated from wiki text)';
229 CREATE TABLE "subscription_time" (
230 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
231 "day_of_week" INT2 CONSTRAINT "day_of_week_range" CHECK ("day_of_week" BETWEEN 0 AND 6),
232 "time_of_day" TIME NOT NULL );
233 CREATE UNIQUE INDEX "subscription_time_all_days_of_week_time_of_day_idx" ON "subscription_time" ("time_of_day", "member_id") WHERE ("day_of_week" ISNULL);
234 CREATE UNIQUE INDEX "subscription_time_day_of_week_time_of_day_idx" ON "subscription_time" ("day_of_week", "time_of_day", "member_id");
236 COMMENT ON TABLE "subscription_time" IS 'Selects when a member receives digests on new user content in the system';
239 CREATE TABLE "setting" (
240 PRIMARY KEY ("member_id", "key"),
241 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
242 "key" TEXT NOT NULL,
243 "value" TEXT NOT NULL );
244 CREATE INDEX "setting_key_idx" ON "setting" ("key");
246 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
248 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
251 CREATE TABLE "setting_map" (
252 PRIMARY KEY ("member_id", "key", "subkey"),
253 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
254 "key" TEXT NOT NULL,
255 "subkey" TEXT NOT NULL,
256 "value" TEXT NOT NULL );
257 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
259 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
261 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
262 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
263 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
266 CREATE TABLE "member_relation_setting" (
267 PRIMARY KEY ("member_id", "key", "other_member_id"),
268 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
269 "key" TEXT NOT NULL,
270 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
271 "value" TEXT NOT NULL );
273 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
276 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
278 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
281 CREATE TABLE "member_image" (
282 PRIMARY KEY ("member_id", "image_type", "scaled"),
283 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
284 "image_type" "member_image_type",
285 "scaled" BOOLEAN,
286 "content_type" TEXT,
287 "data" BYTEA NOT NULL );
289 COMMENT ON TABLE "member_image" IS 'Images of members';
291 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
294 CREATE TABLE "member_count" (
295 "calculated" TIMESTAMPTZ NOT NULL DEFAULT now(),
296 "total_count" INT4 NOT NULL );
298 COMMENT ON TABLE "member_count" IS 'Contains one row which contains the total count of active(!) members and a timestamp indicating when the total member count and area member counts were calculated';
300 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
301 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
304 CREATE TABLE "contact" (
305 PRIMARY KEY ("member_id", "other_member_id"),
306 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
307 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
308 "public" BOOLEAN NOT NULL DEFAULT FALSE,
309 CONSTRAINT "cant_save_yourself_as_contact"
310 CHECK ("member_id" != "other_member_id") );
311 CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
313 COMMENT ON TABLE "contact" IS 'Contact lists';
315 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
316 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
317 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
320 CREATE TABLE "ignored_member" (
321 PRIMARY KEY ("member_id", "other_member_id"),
322 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
323 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
324 CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
326 COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
328 COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone';
329 COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
332 CREATE TABLE "session" (
333 "ident" TEXT PRIMARY KEY,
334 "additional_secret" TEXT,
335 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
336 "member_id" INT4 REFERENCES "member" ("id") ON DELETE SET NULL,
337 "authority" TEXT,
338 "authority_uid" TEXT,
339 "authority_login" TEXT,
340 "needs_delegation_check" BOOLEAN NOT NULL DEFAULT FALSE,
341 "lang" TEXT );
342 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
344 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer';
346 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
347 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
348 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
349 COMMENT ON COLUMN "session"."authority" IS 'Temporary store for "member"."authority" during member account creation';
350 COMMENT ON COLUMN "session"."authority_uid" IS 'Temporary store for "member"."authority_uid" during member account creation';
351 COMMENT ON COLUMN "session"."authority_login" IS 'Temporary store for "member"."authority_login" during member account creation';
352 COMMENT ON COLUMN "session"."needs_delegation_check" IS 'Set to TRUE, if member must perform a delegation check to proceed with login; see column "last_delegation_check" in "member" table';
353 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
356 CREATE TYPE "defeat_strength" AS ENUM ('simple', 'tuple');
358 COMMENT ON TYPE "defeat_strength" IS 'How pairwise defeats are measured for the Schulze method: ''simple'' = only the number of winning votes, ''tuple'' = primarily the number of winning votes, secondarily the number of losing votes';
361 CREATE TYPE "tie_breaking" AS ENUM ('simple', 'variant1', 'variant2');
363 COMMENT ON TYPE "tie_breaking" IS 'Tie-breaker for the Schulze method: ''simple'' = only initiative ids are used, ''variant1'' = use initiative ids in variant 1 for tie breaking of the links (TBRL) and sequentially forbid shared links, ''variant2'' = use initiative ids in variant 2 for tie breaking of the links (TBRL) and sequentially forbid shared links';
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 "unit_setting" (
480 PRIMARY KEY ("member_id", "key", "unit_id"),
481 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
482 "key" TEXT NOT NULL,
483 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
484 "value" TEXT NOT NULL );
486 COMMENT ON TABLE "unit_setting" IS 'Place for frontend to store unit specific settings of members as strings';
489 CREATE TABLE "area" (
490 "id" SERIAL4 PRIMARY KEY,
491 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
492 "active" BOOLEAN NOT NULL DEFAULT TRUE,
493 "name" TEXT NOT NULL,
494 "description" TEXT NOT NULL DEFAULT '',
495 "external_reference" TEXT,
496 "direct_member_count" INT4,
497 "member_weight" INT4,
498 "text_search_data" TSVECTOR );
499 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
500 CREATE INDEX "area_active_idx" ON "area" ("active");
501 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
502 CREATE TRIGGER "update_text_search_data"
503 BEFORE INSERT OR UPDATE ON "area"
504 FOR EACH ROW EXECUTE PROCEDURE
505 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
506 "name", "description" );
508 COMMENT ON TABLE "area" IS 'Subject areas';
510 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
511 COMMENT ON COLUMN "area"."external_reference" IS 'Opaque data field to store an external reference';
512 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"';
513 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
516 CREATE TABLE "area_setting" (
517 PRIMARY KEY ("member_id", "key", "area_id"),
518 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
519 "key" TEXT NOT NULL,
520 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
521 "value" TEXT NOT NULL );
523 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
526 CREATE TABLE "allowed_policy" (
527 PRIMARY KEY ("area_id", "policy_id"),
528 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
529 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
530 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
531 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
533 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
535 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
538 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
540 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';
543 CREATE TYPE "issue_state" AS ENUM (
544 'admission', 'discussion', 'verification', 'voting',
545 'canceled_by_admin',
546 'canceled_revoked_before_accepted',
547 'canceled_issue_not_accepted',
548 'canceled_after_revocation_during_discussion',
549 'canceled_after_revocation_during_verification',
550 'canceled_no_initiative_admitted',
551 'finished_without_winner', 'finished_with_winner');
553 COMMENT ON TYPE "issue_state" IS 'State of issues';
556 CREATE TABLE "issue" (
557 "id" SERIAL4 PRIMARY KEY,
558 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
559 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
560 "admin_notice" TEXT,
561 "external_reference" TEXT,
562 "state" "issue_state" NOT NULL DEFAULT 'admission',
563 "phase_finished" TIMESTAMPTZ,
564 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
565 "accepted" TIMESTAMPTZ,
566 "half_frozen" TIMESTAMPTZ,
567 "fully_frozen" TIMESTAMPTZ,
568 "closed" TIMESTAMPTZ,
569 "cleaned" TIMESTAMPTZ,
570 "min_admission_time" INTERVAL,
571 "max_admission_time" INTERVAL,
572 "discussion_time" INTERVAL NOT NULL,
573 "verification_time" INTERVAL NOT NULL,
574 "voting_time" INTERVAL NOT NULL,
575 "snapshot" TIMESTAMPTZ,
576 "latest_snapshot_event" "snapshot_event",
577 "population" INT4,
578 "voter_count" INT4,
579 "status_quo_schulze_rank" INT4,
580 CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK (
581 ("min_admission_time" NOTNULL) = ("max_admission_time" NOTNULL) AND
582 ("min_admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created")) ),
583 CONSTRAINT "valid_state" CHECK (
584 (
585 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR
586 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL ) OR
587 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL ) OR
588 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL)
589 ) AND (
590 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
591 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
592 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
593 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
594 ("state" = 'canceled_by_admin' AND "closed" NOTNULL) OR
595 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
596 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
597 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
598 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
599 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" = "fully_frozen") OR
600 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen") OR
601 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "closed" != "fully_frozen")
602 )),
603 CONSTRAINT "phase_finished_only_when_not_closed" CHECK (
604 "phase_finished" ISNULL OR "closed" ISNULL ),
605 CONSTRAINT "state_change_order" CHECK (
606 "created" <= "accepted" AND
607 "accepted" <= "half_frozen" AND
608 "half_frozen" <= "fully_frozen" AND
609 "fully_frozen" <= "closed" ),
610 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
611 "cleaned" ISNULL OR "closed" NOTNULL ),
612 CONSTRAINT "last_snapshot_on_full_freeze"
613 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
614 CONSTRAINT "freeze_requires_snapshot"
615 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
616 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
617 CHECK (("snapshot" NOTNULL) = ("latest_snapshot_event" NOTNULL)) );
618 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
619 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
620 CREATE INDEX "issue_created_idx" ON "issue" ("created");
621 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
622 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
623 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
624 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
625 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
626 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
628 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
630 COMMENT ON COLUMN "issue"."admin_notice" IS 'Public notice by admin to explain manual interventions, or to announce corrections';
631 COMMENT ON COLUMN "issue"."external_reference" IS 'Opaque data field to store an external reference';
632 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';
633 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
634 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.';
635 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.';
636 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.';
637 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
638 COMMENT ON COLUMN "issue"."min_admission_time" IS 'Copied from "policy" table at creation of issue';
639 COMMENT ON COLUMN "issue"."max_admission_time" IS 'Copied from "policy" table at creation of issue';
640 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
641 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
642 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
643 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
644 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';
645 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
646 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';
647 COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
650 CREATE TABLE "issue_order_in_admission_state" (
651 "id" INT8 PRIMARY KEY, --REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
652 "order_in_area" INT4,
653 "order_in_unit" INT4 );
655 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"';
657 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';
658 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';
659 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';
662 CREATE TABLE "issue_setting" (
663 PRIMARY KEY ("member_id", "key", "issue_id"),
664 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
665 "key" TEXT NOT NULL,
666 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
667 "value" TEXT NOT NULL );
669 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
672 CREATE TABLE "initiative" (
673 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
674 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
675 "id" SERIAL4 PRIMARY KEY,
676 "name" TEXT NOT NULL,
677 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
678 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
679 "revoked" TIMESTAMPTZ,
680 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
681 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
682 "external_reference" TEXT,
683 "admitted" BOOLEAN,
684 "supporter_count" INT4,
685 "informed_supporter_count" INT4,
686 "satisfied_supporter_count" INT4,
687 "satisfied_informed_supporter_count" INT4,
688 "harmonic_weight" NUMERIC(12, 3),
689 "final_suggestion_order_calculated" BOOLEAN NOT NULL DEFAULT FALSE,
690 "first_preference_votes" INT4,
691 "positive_votes" INT4,
692 "negative_votes" INT4,
693 "direct_majority" BOOLEAN,
694 "indirect_majority" BOOLEAN,
695 "schulze_rank" INT4,
696 "better_than_status_quo" BOOLEAN,
697 "worse_than_status_quo" BOOLEAN,
698 "reverse_beat_path" BOOLEAN,
699 "multistage_majority" BOOLEAN,
700 "eligible" BOOLEAN,
701 "winner" BOOLEAN,
702 "rank" INT4,
703 "text_search_data" TSVECTOR,
704 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
705 CHECK (("revoked" NOTNULL) = ("revoked_by_member_id" NOTNULL)),
706 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
707 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
708 CONSTRAINT "revoked_initiatives_cant_be_admitted"
709 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
710 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
711 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
712 ( "first_preference_votes" ISNULL AND
713 "positive_votes" ISNULL AND "negative_votes" ISNULL AND
714 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
715 "schulze_rank" ISNULL AND
716 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
717 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
718 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ),
719 CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")),
720 CONSTRAINT "minimum_requirement_to_be_eligible" CHECK (
721 "eligible" = FALSE OR
722 ("direct_majority" AND "indirect_majority" AND "better_than_status_quo") ),
723 CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE),
724 CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1),
725 CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE),
726 CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") );
727 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
728 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
729 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
730 CREATE TRIGGER "update_text_search_data"
731 BEFORE INSERT OR UPDATE ON "initiative"
732 FOR EACH ROW EXECUTE PROCEDURE
733 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "name");
735 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.';
737 COMMENT ON COLUMN "initiative"."polling" IS 'Initiative does not need to pass the initiative quorum (see "policy"."polling")';
738 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
739 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoke the initiative';
740 COMMENT ON COLUMN "initiative"."external_reference" IS 'Opaque data field to store an external reference';
741 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
742 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
743 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
744 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
745 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
746 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';
747 COMMENT ON COLUMN "initiative"."final_suggestion_order_calculated" IS 'Set to TRUE, when "proportional_order" of suggestions has been calculated the last time';
748 COMMENT ON COLUMN "initiative"."first_preference_votes" IS 'Number of direct and delegating voters who ranked this initiative as their first choice';
749 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Number of direct and delegating voters who ranked this initiative better than the status quo';
750 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Number of direct and delegating voters who ranked this initiative worse than the status quo';
751 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"';
752 COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths';
753 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking';
754 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo';
755 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)';
756 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''';
757 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';
758 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"';
759 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank"';
760 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';
763 CREATE TABLE "battle" (
764 "issue_id" INT4 NOT NULL,
765 "winning_initiative_id" INT4,
766 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
767 "losing_initiative_id" INT4,
768 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
769 "count" INT4 NOT NULL,
770 CONSTRAINT "initiative_ids_not_equal" CHECK (
771 "winning_initiative_id" != "losing_initiative_id" OR
772 ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
773 ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) );
774 CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
775 CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
776 CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
778 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';
781 CREATE TABLE "ignored_initiative" (
782 PRIMARY KEY ("initiative_id", "member_id"),
783 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
784 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
785 CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");
787 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
790 CREATE TABLE "initiative_setting" (
791 PRIMARY KEY ("member_id", "key", "initiative_id"),
792 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
793 "key" TEXT NOT NULL,
794 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
795 "value" TEXT NOT NULL );
797 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
800 CREATE TABLE "draft" (
801 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
802 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
803 "id" SERIAL8 PRIMARY KEY,
804 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
805 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
806 "formatting_engine" TEXT,
807 "content" TEXT NOT NULL,
808 "external_reference" TEXT,
809 "text_search_data" TSVECTOR );
810 CREATE INDEX "draft_created_idx" ON "draft" ("created");
811 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
812 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
813 CREATE TRIGGER "update_text_search_data"
814 BEFORE INSERT OR UPDATE ON "draft"
815 FOR EACH ROW EXECUTE PROCEDURE
816 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
818 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.';
820 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
821 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
822 COMMENT ON COLUMN "draft"."external_reference" IS 'Opaque data field to store an external reference';
825 CREATE TABLE "rendered_draft" (
826 PRIMARY KEY ("draft_id", "format"),
827 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
828 "format" TEXT,
829 "content" TEXT NOT NULL );
831 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)';
834 CREATE TABLE "suggestion" (
835 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
836 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
837 "id" SERIAL8 PRIMARY KEY,
838 "draft_id" INT8 NOT NULL,
839 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
840 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
841 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
842 "name" TEXT NOT NULL,
843 "formatting_engine" TEXT,
844 "content" TEXT NOT NULL DEFAULT '',
845 "external_reference" TEXT,
846 "text_search_data" TSVECTOR,
847 "minus2_unfulfilled_count" INT4,
848 "minus2_fulfilled_count" INT4,
849 "minus1_unfulfilled_count" INT4,
850 "minus1_fulfilled_count" INT4,
851 "plus1_unfulfilled_count" INT4,
852 "plus1_fulfilled_count" INT4,
853 "plus2_unfulfilled_count" INT4,
854 "plus2_fulfilled_count" INT4,
855 "proportional_order" INT4 );
856 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
857 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
858 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
859 CREATE TRIGGER "update_text_search_data"
860 BEFORE INSERT OR UPDATE ON "suggestion"
861 FOR EACH ROW EXECUTE PROCEDURE
862 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
863 "name", "content");
865 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';
867 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")';
868 COMMENT ON COLUMN "suggestion"."external_reference" IS 'Opaque data field to store an external reference';
869 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
870 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
871 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
872 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
873 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
874 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
875 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
876 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
877 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"';
880 CREATE TABLE "rendered_suggestion" (
881 PRIMARY KEY ("suggestion_id", "format"),
882 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
883 "format" TEXT,
884 "content" TEXT NOT NULL );
886 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)';
889 CREATE TABLE "suggestion_setting" (
890 PRIMARY KEY ("member_id", "key", "suggestion_id"),
891 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
892 "key" TEXT NOT NULL,
893 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
894 "value" TEXT NOT NULL );
896 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
899 CREATE TABLE "privilege" (
900 PRIMARY KEY ("unit_id", "member_id"),
901 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
902 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
903 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
904 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
905 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
906 "member_manager" BOOLEAN NOT NULL DEFAULT FALSE,
907 "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE,
908 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE,
909 "polling_right" BOOLEAN NOT NULL DEFAULT FALSE );
911 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
913 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke any privileges to/from other members';
914 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create and disable sub units';
915 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create and disable areas and set area parameters';
916 COMMENT ON COLUMN "privilege"."member_manager" IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"';
917 COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative';
918 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to support initiatives, create and rate suggestions, and to vote';
919 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';
922 CREATE TABLE "subscription" (
923 PRIMARY KEY ("unit_id", "member_id"),
924 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
925 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
926 CREATE INDEX "subscription_member_id_idx" ON "subscription" ("member_id");
928 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';
931 CREATE TABLE "ignored_area" (
932 PRIMARY KEY ("area_id", "member_id"),
933 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
934 "member_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
935 CREATE INDEX "ignored_area_member_id_idx" ON "ignored_area" ("member_id");
937 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';
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.';
1280 CREATE TABLE "advertisement" (
1281 PRIMARY KEY ("time_serial", "initiative_id", "member_id"),
1282 "time_serial" SERIAL8,
1283 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1284 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
1285 CREATE INDEX "advertisement_initiative_id_idx" ON "advertisement" ("initiative_id");
1286 CREATE INDEX "advertisement_member_id_idx" ON "advertisement" ("member_id");
1288 COMMENT ON TABLE "advertisement" IS 'Stores which initiatives have been advertised to a member in a mail digest';
1290 COMMENT ON COLUMN "advertisement"."time_serial" IS 'An increasing integer that may be used to determine which issue or initiative was advertised the longest ago (may be equal or consecutive for those initiatives that are sent in the same notification)';
1294 ----------------------------------------------
1295 -- Writing of history entries and event log --
1296 ----------------------------------------------
1299 CREATE FUNCTION "write_member_history_trigger"()
1300 RETURNS TRIGGER
1301 LANGUAGE 'plpgsql' VOLATILE AS $$
1302 BEGIN
1303 IF
1304 ( NEW."active" != OLD."active" OR
1305 NEW."name" != OLD."name" ) AND
1306 OLD."activated" NOTNULL
1307 THEN
1308 INSERT INTO "member_history"
1309 ("member_id", "active", "name")
1310 VALUES (NEW."id", OLD."active", OLD."name");
1311 END IF;
1312 RETURN NULL;
1313 END;
1314 $$;
1316 CREATE TRIGGER "write_member_history"
1317 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1318 "write_member_history_trigger"();
1320 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
1321 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
1324 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
1325 RETURNS TRIGGER
1326 LANGUAGE 'plpgsql' VOLATILE AS $$
1327 BEGIN
1328 IF NEW."state" != OLD."state" THEN
1329 INSERT INTO "event" ("event", "issue_id", "state")
1330 VALUES ('issue_state_changed', NEW."id", NEW."state");
1331 END IF;
1332 RETURN NULL;
1333 END;
1334 $$;
1336 CREATE TRIGGER "write_event_issue_state_changed"
1337 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
1338 "write_event_issue_state_changed_trigger"();
1340 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
1341 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
1344 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1345 RETURNS TRIGGER
1346 LANGUAGE 'plpgsql' VOLATILE AS $$
1347 DECLARE
1348 "initiative_row" "initiative"%ROWTYPE;
1349 "issue_row" "issue"%ROWTYPE;
1350 "event_v" "event_type";
1351 BEGIN
1352 SELECT * INTO "initiative_row" FROM "initiative"
1353 WHERE "id" = NEW."initiative_id";
1354 SELECT * INTO "issue_row" FROM "issue"
1355 WHERE "id" = "initiative_row"."issue_id";
1356 IF EXISTS (
1357 SELECT NULL FROM "draft"
1358 WHERE "initiative_id" = NEW."initiative_id"
1359 AND "id" != NEW."id"
1360 ) THEN
1361 "event_v" := 'new_draft_created';
1362 ELSE
1363 IF EXISTS (
1364 SELECT NULL FROM "initiative"
1365 WHERE "issue_id" = "initiative_row"."issue_id"
1366 AND "id" != "initiative_row"."id"
1367 ) THEN
1368 "event_v" := 'initiative_created_in_existing_issue';
1369 ELSE
1370 "event_v" := 'initiative_created_in_new_issue';
1371 END IF;
1372 END IF;
1373 INSERT INTO "event" (
1374 "event", "member_id",
1375 "issue_id", "state", "initiative_id", "draft_id"
1376 ) VALUES (
1377 "event_v",
1378 NEW."author_id",
1379 "initiative_row"."issue_id",
1380 "issue_row"."state",
1381 "initiative_row"."id",
1382 NEW."id" );
1383 RETURN NULL;
1384 END;
1385 $$;
1387 CREATE TRIGGER "write_event_initiative_or_draft_created"
1388 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
1389 "write_event_initiative_or_draft_created_trigger"();
1391 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
1392 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
1395 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
1396 RETURNS TRIGGER
1397 LANGUAGE 'plpgsql' VOLATILE AS $$
1398 DECLARE
1399 "issue_row" "issue"%ROWTYPE;
1400 "draft_id_v" "draft"."id"%TYPE;
1401 BEGIN
1402 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1403 SELECT * INTO "issue_row" FROM "issue"
1404 WHERE "id" = NEW."issue_id";
1405 SELECT "id" INTO "draft_id_v" FROM "current_draft"
1406 WHERE "initiative_id" = NEW."id";
1407 INSERT INTO "event" (
1408 "event", "member_id", "issue_id", "state", "initiative_id", "draft_id"
1409 ) VALUES (
1410 'initiative_revoked',
1411 NEW."revoked_by_member_id",
1412 NEW."issue_id",
1413 "issue_row"."state",
1414 NEW."id",
1415 "draft_id_v");
1416 END IF;
1417 RETURN NULL;
1418 END;
1419 $$;
1421 CREATE TRIGGER "write_event_initiative_revoked"
1422 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
1423 "write_event_initiative_revoked_trigger"();
1425 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
1426 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
1429 CREATE FUNCTION "write_event_suggestion_created_trigger"()
1430 RETURNS TRIGGER
1431 LANGUAGE 'plpgsql' VOLATILE AS $$
1432 DECLARE
1433 "initiative_row" "initiative"%ROWTYPE;
1434 "issue_row" "issue"%ROWTYPE;
1435 BEGIN
1436 SELECT * INTO "initiative_row" FROM "initiative"
1437 WHERE "id" = NEW."initiative_id";
1438 SELECT * INTO "issue_row" FROM "issue"
1439 WHERE "id" = "initiative_row"."issue_id";
1440 INSERT INTO "event" (
1441 "event", "member_id",
1442 "issue_id", "state", "initiative_id", "suggestion_id"
1443 ) VALUES (
1444 'suggestion_created',
1445 NEW."author_id",
1446 "initiative_row"."issue_id",
1447 "issue_row"."state",
1448 "initiative_row"."id",
1449 NEW."id" );
1450 RETURN NULL;
1451 END;
1452 $$;
1454 CREATE TRIGGER "write_event_suggestion_created"
1455 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1456 "write_event_suggestion_created_trigger"();
1458 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
1459 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1463 ----------------------------
1464 -- Additional constraints --
1465 ----------------------------
1468 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
1469 RETURNS TRIGGER
1470 LANGUAGE 'plpgsql' VOLATILE AS $$
1471 BEGIN
1472 IF NOT EXISTS (
1473 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
1474 ) THEN
1475 RAISE EXCEPTION 'Cannot create issue without an initial initiative.' USING
1476 ERRCODE = 'integrity_constraint_violation',
1477 HINT = 'Create issue, initiative, and draft within the same transaction.';
1478 END IF;
1479 RETURN NULL;
1480 END;
1481 $$;
1483 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
1484 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
1485 FOR EACH ROW EXECUTE PROCEDURE
1486 "issue_requires_first_initiative_trigger"();
1488 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
1489 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
1492 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
1493 RETURNS TRIGGER
1494 LANGUAGE 'plpgsql' VOLATILE AS $$
1495 DECLARE
1496 "reference_lost" BOOLEAN;
1497 BEGIN
1498 IF TG_OP = 'DELETE' THEN
1499 "reference_lost" := TRUE;
1500 ELSE
1501 "reference_lost" := NEW."issue_id" != OLD."issue_id";
1502 END IF;
1503 IF
1504 "reference_lost" AND NOT EXISTS (
1505 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
1506 )
1507 THEN
1508 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
1509 END IF;
1510 RETURN NULL;
1511 END;
1512 $$;
1514 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
1515 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1516 FOR EACH ROW EXECUTE PROCEDURE
1517 "last_initiative_deletes_issue_trigger"();
1519 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
1520 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
1523 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
1524 RETURNS TRIGGER
1525 LANGUAGE 'plpgsql' VOLATILE AS $$
1526 BEGIN
1527 IF NOT EXISTS (
1528 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
1529 ) THEN
1530 RAISE EXCEPTION 'Cannot create initiative without an initial draft.' USING
1531 ERRCODE = 'integrity_constraint_violation',
1532 HINT = 'Create issue, initiative and draft within the same transaction.';
1533 END IF;
1534 RETURN NULL;
1535 END;
1536 $$;
1538 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
1539 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1540 FOR EACH ROW EXECUTE PROCEDURE
1541 "initiative_requires_first_draft_trigger"();
1543 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
1544 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
1547 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
1548 RETURNS TRIGGER
1549 LANGUAGE 'plpgsql' VOLATILE AS $$
1550 DECLARE
1551 "reference_lost" BOOLEAN;
1552 BEGIN
1553 IF TG_OP = 'DELETE' THEN
1554 "reference_lost" := TRUE;
1555 ELSE
1556 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
1557 END IF;
1558 IF
1559 "reference_lost" AND NOT EXISTS (
1560 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
1561 )
1562 THEN
1563 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
1564 END IF;
1565 RETURN NULL;
1566 END;
1567 $$;
1569 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
1570 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
1571 FOR EACH ROW EXECUTE PROCEDURE
1572 "last_draft_deletes_initiative_trigger"();
1574 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
1575 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
1578 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
1579 RETURNS TRIGGER
1580 LANGUAGE 'plpgsql' VOLATILE AS $$
1581 BEGIN
1582 IF NOT EXISTS (
1583 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
1584 ) THEN
1585 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.' USING
1586 ERRCODE = 'integrity_constraint_violation',
1587 HINT = 'Create suggestion and opinion within the same transaction.';
1588 END IF;
1589 RETURN NULL;
1590 END;
1591 $$;
1593 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
1594 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
1595 FOR EACH ROW EXECUTE PROCEDURE
1596 "suggestion_requires_first_opinion_trigger"();
1598 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1599 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1602 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1603 RETURNS TRIGGER
1604 LANGUAGE 'plpgsql' VOLATILE AS $$
1605 DECLARE
1606 "reference_lost" BOOLEAN;
1607 BEGIN
1608 IF TG_OP = 'DELETE' THEN
1609 "reference_lost" := TRUE;
1610 ELSE
1611 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1612 END IF;
1613 IF
1614 "reference_lost" AND NOT EXISTS (
1615 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1616 )
1617 THEN
1618 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1619 END IF;
1620 RETURN NULL;
1621 END;
1622 $$;
1624 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1625 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1626 FOR EACH ROW EXECUTE PROCEDURE
1627 "last_opinion_deletes_suggestion_trigger"();
1629 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1630 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1633 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()
1634 RETURNS TRIGGER
1635 LANGUAGE 'plpgsql' VOLATILE AS $$
1636 BEGIN
1637 DELETE FROM "direct_voter"
1638 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
1639 RETURN NULL;
1640 END;
1641 $$;
1643 CREATE TRIGGER "non_voter_deletes_direct_voter"
1644 AFTER INSERT OR UPDATE ON "non_voter"
1645 FOR EACH ROW EXECUTE PROCEDURE
1646 "non_voter_deletes_direct_voter_trigger"();
1648 COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"';
1649 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")';
1652 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()
1653 RETURNS TRIGGER
1654 LANGUAGE 'plpgsql' VOLATILE AS $$
1655 BEGIN
1656 DELETE FROM "non_voter"
1657 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
1658 RETURN NULL;
1659 END;
1660 $$;
1662 CREATE TRIGGER "direct_voter_deletes_non_voter"
1663 AFTER INSERT OR UPDATE ON "direct_voter"
1664 FOR EACH ROW EXECUTE PROCEDURE
1665 "direct_voter_deletes_non_voter_trigger"();
1667 COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"';
1668 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")';
1671 CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()
1672 RETURNS TRIGGER
1673 LANGUAGE 'plpgsql' VOLATILE AS $$
1674 BEGIN
1675 IF NEW."comment" ISNULL THEN
1676 NEW."comment_changed" := NULL;
1677 NEW."formatting_engine" := NULL;
1678 END IF;
1679 RETURN NEW;
1680 END;
1681 $$;
1683 CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"
1684 BEFORE INSERT OR UPDATE ON "direct_voter"
1685 FOR EACH ROW EXECUTE PROCEDURE
1686 "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();
1688 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"';
1689 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.';
1692 ---------------------------------------------------------------
1693 -- Ensure that votes are not modified when issues are closed --
1694 ---------------------------------------------------------------
1696 -- NOTE: Frontends should ensure this anyway, but in case of programming
1697 -- errors the following triggers ensure data integrity.
1700 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1701 RETURNS TRIGGER
1702 LANGUAGE 'plpgsql' VOLATILE AS $$
1703 DECLARE
1704 "issue_id_v" "issue"."id"%TYPE;
1705 "issue_row" "issue"%ROWTYPE;
1706 BEGIN
1707 IF EXISTS (
1708 SELECT NULL FROM "temporary_transaction_data"
1709 WHERE "txid" = txid_current()
1710 AND "key" = 'override_protection_triggers'
1711 AND "value" = TRUE::TEXT
1712 ) THEN
1713 RETURN NULL;
1714 END IF;
1715 IF TG_OP = 'DELETE' THEN
1716 "issue_id_v" := OLD."issue_id";
1717 ELSE
1718 "issue_id_v" := NEW."issue_id";
1719 END IF;
1720 SELECT INTO "issue_row" * FROM "issue"
1721 WHERE "id" = "issue_id_v" FOR SHARE;
1722 IF (
1723 "issue_row"."closed" NOTNULL OR (
1724 "issue_row"."state" = 'voting' AND
1725 "issue_row"."phase_finished" NOTNULL
1726 )
1727 ) THEN
1728 IF
1729 TG_RELID = 'direct_voter'::regclass AND
1730 TG_OP = 'UPDATE'
1731 THEN
1732 IF
1733 OLD."issue_id" = NEW."issue_id" AND
1734 OLD."member_id" = NEW."member_id" AND
1735 OLD."weight" = NEW."weight"
1736 THEN
1737 RETURN NULL; -- allows changing of voter comment
1738 END IF;
1739 END IF;
1740 RAISE EXCEPTION 'Tried to modify data after voting has been closed.' USING
1741 ERRCODE = 'integrity_constraint_violation';
1742 END IF;
1743 RETURN NULL;
1744 END;
1745 $$;
1747 CREATE TRIGGER "forbid_changes_on_closed_issue"
1748 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1749 FOR EACH ROW EXECUTE PROCEDURE
1750 "forbid_changes_on_closed_issue_trigger"();
1752 CREATE TRIGGER "forbid_changes_on_closed_issue"
1753 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1754 FOR EACH ROW EXECUTE PROCEDURE
1755 "forbid_changes_on_closed_issue_trigger"();
1757 CREATE TRIGGER "forbid_changes_on_closed_issue"
1758 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1759 FOR EACH ROW EXECUTE PROCEDURE
1760 "forbid_changes_on_closed_issue_trigger"();
1762 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"';
1763 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';
1764 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';
1765 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';
1769 --------------------------------------------------------------------
1770 -- Auto-retrieval of fields only needed for referential integrity --
1771 --------------------------------------------------------------------
1774 CREATE FUNCTION "autofill_issue_id_trigger"()
1775 RETURNS TRIGGER
1776 LANGUAGE 'plpgsql' VOLATILE AS $$
1777 BEGIN
1778 IF NEW."issue_id" ISNULL THEN
1779 SELECT "issue_id" INTO NEW."issue_id"
1780 FROM "initiative" WHERE "id" = NEW."initiative_id";
1781 END IF;
1782 RETURN NEW;
1783 END;
1784 $$;
1786 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1787 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1789 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1790 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1792 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1793 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1794 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1797 CREATE FUNCTION "autofill_initiative_id_trigger"()
1798 RETURNS TRIGGER
1799 LANGUAGE 'plpgsql' VOLATILE AS $$
1800 BEGIN
1801 IF NEW."initiative_id" ISNULL THEN
1802 SELECT "initiative_id" INTO NEW."initiative_id"
1803 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1804 END IF;
1805 RETURN NEW;
1806 END;
1807 $$;
1809 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1810 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1812 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1813 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1817 -----------------------------------------------------
1818 -- Automatic calculation of certain default values --
1819 -----------------------------------------------------
1822 CREATE FUNCTION "copy_timings_trigger"()
1823 RETURNS TRIGGER
1824 LANGUAGE 'plpgsql' VOLATILE AS $$
1825 DECLARE
1826 "policy_row" "policy"%ROWTYPE;
1827 BEGIN
1828 SELECT * INTO "policy_row" FROM "policy"
1829 WHERE "id" = NEW."policy_id";
1830 IF NEW."min_admission_time" ISNULL THEN
1831 NEW."min_admission_time" := "policy_row"."min_admission_time";
1832 END IF;
1833 IF NEW."max_admission_time" ISNULL THEN
1834 NEW."max_admission_time" := "policy_row"."max_admission_time";
1835 END IF;
1836 IF NEW."discussion_time" ISNULL THEN
1837 NEW."discussion_time" := "policy_row"."discussion_time";
1838 END IF;
1839 IF NEW."verification_time" ISNULL THEN
1840 NEW."verification_time" := "policy_row"."verification_time";
1841 END IF;
1842 IF NEW."voting_time" ISNULL THEN
1843 NEW."voting_time" := "policy_row"."voting_time";
1844 END IF;
1845 RETURN NEW;
1846 END;
1847 $$;
1849 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1850 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1852 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1853 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1856 CREATE FUNCTION "default_for_draft_id_trigger"()
1857 RETURNS TRIGGER
1858 LANGUAGE 'plpgsql' VOLATILE AS $$
1859 BEGIN
1860 IF NEW."draft_id" ISNULL THEN
1861 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1862 WHERE "initiative_id" = NEW."initiative_id";
1863 END IF;
1864 RETURN NEW;
1865 END;
1866 $$;
1868 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"
1869 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1870 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1871 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1873 COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';
1874 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';
1875 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';
1879 ----------------------------------------
1880 -- Automatic creation of dependencies --
1881 ----------------------------------------
1884 CREATE FUNCTION "autocreate_interest_trigger"()
1885 RETURNS TRIGGER
1886 LANGUAGE 'plpgsql' VOLATILE AS $$
1887 BEGIN
1888 IF NOT EXISTS (
1889 SELECT NULL FROM "initiative" JOIN "interest"
1890 ON "initiative"."issue_id" = "interest"."issue_id"
1891 WHERE "initiative"."id" = NEW."initiative_id"
1892 AND "interest"."member_id" = NEW."member_id"
1893 ) THEN
1894 BEGIN
1895 INSERT INTO "interest" ("issue_id", "member_id")
1896 SELECT "issue_id", NEW."member_id"
1897 FROM "initiative" WHERE "id" = NEW."initiative_id";
1898 EXCEPTION WHEN unique_violation THEN END;
1899 END IF;
1900 RETURN NEW;
1901 END;
1902 $$;
1904 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1905 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1907 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1908 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';
1911 CREATE FUNCTION "autocreate_supporter_trigger"()
1912 RETURNS TRIGGER
1913 LANGUAGE 'plpgsql' VOLATILE AS $$
1914 BEGIN
1915 IF NOT EXISTS (
1916 SELECT NULL FROM "suggestion" JOIN "supporter"
1917 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1918 WHERE "suggestion"."id" = NEW."suggestion_id"
1919 AND "supporter"."member_id" = NEW."member_id"
1920 ) THEN
1921 BEGIN
1922 INSERT INTO "supporter" ("initiative_id", "member_id")
1923 SELECT "initiative_id", NEW."member_id"
1924 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1925 EXCEPTION WHEN unique_violation THEN END;
1926 END IF;
1927 RETURN NEW;
1928 END;
1929 $$;
1931 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1932 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1934 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1935 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.';
1939 ------------------------------------------
1940 -- Views and helper functions for views --
1941 ------------------------------------------
1944 CREATE VIEW "unit_delegation" AS
1945 SELECT
1946 "unit"."id" AS "unit_id",
1947 "delegation"."id",
1948 "delegation"."truster_id",
1949 "delegation"."trustee_id",
1950 "delegation"."scope"
1951 FROM "unit"
1952 JOIN "delegation"
1953 ON "delegation"."unit_id" = "unit"."id"
1954 JOIN "member"
1955 ON "delegation"."truster_id" = "member"."id"
1956 JOIN "privilege"
1957 ON "delegation"."unit_id" = "privilege"."unit_id"
1958 AND "delegation"."truster_id" = "privilege"."member_id"
1959 WHERE "member"."active" AND "privilege"."voting_right";
1961 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
1964 CREATE VIEW "area_delegation" AS
1965 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1966 "area"."id" AS "area_id",
1967 "delegation"."id",
1968 "delegation"."truster_id",
1969 "delegation"."trustee_id",
1970 "delegation"."scope"
1971 FROM "area"
1972 JOIN "delegation"
1973 ON "delegation"."unit_id" = "area"."unit_id"
1974 OR "delegation"."area_id" = "area"."id"
1975 JOIN "member"
1976 ON "delegation"."truster_id" = "member"."id"
1977 JOIN "privilege"
1978 ON "area"."unit_id" = "privilege"."unit_id"
1979 AND "delegation"."truster_id" = "privilege"."member_id"
1980 WHERE "member"."active" AND "privilege"."voting_right"
1981 ORDER BY
1982 "area"."id",
1983 "delegation"."truster_id",
1984 "delegation"."scope" DESC;
1986 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
1989 CREATE VIEW "issue_delegation" AS
1990 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1991 "issue"."id" AS "issue_id",
1992 "delegation"."id",
1993 "delegation"."truster_id",
1994 "delegation"."trustee_id",
1995 "delegation"."scope"
1996 FROM "issue"
1997 JOIN "area"
1998 ON "area"."id" = "issue"."area_id"
1999 JOIN "delegation"
2000 ON "delegation"."unit_id" = "area"."unit_id"
2001 OR "delegation"."area_id" = "area"."id"
2002 OR "delegation"."issue_id" = "issue"."id"
2003 JOIN "member"
2004 ON "delegation"."truster_id" = "member"."id"
2005 JOIN "privilege"
2006 ON "area"."unit_id" = "privilege"."unit_id"
2007 AND "delegation"."truster_id" = "privilege"."member_id"
2008 WHERE "member"."active" AND "privilege"."voting_right"
2009 ORDER BY
2010 "issue"."id",
2011 "delegation"."truster_id",
2012 "delegation"."scope" DESC;
2014 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
2017 CREATE FUNCTION "membership_weight_with_skipping"
2018 ( "area_id_p" "area"."id"%TYPE,
2019 "member_id_p" "member"."id"%TYPE,
2020 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
2021 RETURNS INT4
2022 LANGUAGE 'plpgsql' STABLE AS $$
2023 DECLARE
2024 "sum_v" INT4;
2025 "delegation_row" "area_delegation"%ROWTYPE;
2026 BEGIN
2027 "sum_v" := 1;
2028 FOR "delegation_row" IN
2029 SELECT "area_delegation".*
2030 FROM "area_delegation" LEFT JOIN "membership"
2031 ON "membership"."area_id" = "area_id_p"
2032 AND "membership"."member_id" = "area_delegation"."truster_id"
2033 WHERE "area_delegation"."area_id" = "area_id_p"
2034 AND "area_delegation"."trustee_id" = "member_id_p"
2035 AND "membership"."member_id" ISNULL
2036 LOOP
2037 IF NOT
2038 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
2039 THEN
2040 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
2041 "area_id_p",
2042 "delegation_row"."truster_id",
2043 "skip_member_ids_p" || "delegation_row"."truster_id"
2044 );
2045 END IF;
2046 END LOOP;
2047 RETURN "sum_v";
2048 END;
2049 $$;
2051 COMMENT ON FUNCTION "membership_weight_with_skipping"
2052 ( "area"."id"%TYPE,
2053 "member"."id"%TYPE,
2054 INT4[] )
2055 IS 'Helper function for "membership_weight" function';
2058 CREATE FUNCTION "membership_weight"
2059 ( "area_id_p" "area"."id"%TYPE,
2060 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
2061 RETURNS INT4
2062 LANGUAGE 'plpgsql' STABLE AS $$
2063 BEGIN
2064 RETURN "membership_weight_with_skipping"(
2065 "area_id_p",
2066 "member_id_p",
2067 ARRAY["member_id_p"]
2068 );
2069 END;
2070 $$;
2072 COMMENT ON FUNCTION "membership_weight"
2073 ( "area"."id"%TYPE,
2074 "member"."id"%TYPE )
2075 IS 'Calculates the potential voting weight of a member in a given area';
2078 CREATE VIEW "member_count_view" AS
2079 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
2081 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
2084 CREATE VIEW "unit_member_count" AS
2085 SELECT
2086 "unit"."id" AS "unit_id",
2087 count("member"."id") AS "member_count"
2088 FROM "unit"
2089 LEFT JOIN "privilege"
2090 ON "privilege"."unit_id" = "unit"."id"
2091 AND "privilege"."voting_right"
2092 LEFT JOIN "member"
2093 ON "member"."id" = "privilege"."member_id"
2094 AND "member"."active"
2095 GROUP BY "unit"."id";
2097 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
2100 CREATE VIEW "area_member_count" AS
2101 SELECT
2102 "area"."id" AS "area_id",
2103 count("member"."id") AS "direct_member_count",
2104 coalesce(
2105 sum(
2106 CASE WHEN "member"."id" NOTNULL THEN
2107 "membership_weight"("area"."id", "member"."id")
2108 ELSE 0 END
2109 )
2110 ) AS "member_weight"
2111 FROM "area"
2112 LEFT JOIN "membership"
2113 ON "area"."id" = "membership"."area_id"
2114 LEFT JOIN "privilege"
2115 ON "privilege"."unit_id" = "area"."unit_id"
2116 AND "privilege"."member_id" = "membership"."member_id"
2117 AND "privilege"."voting_right"
2118 LEFT JOIN "member"
2119 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
2120 AND "member"."active"
2121 GROUP BY "area"."id";
2123 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"';
2126 CREATE VIEW "opening_draft" AS
2127 SELECT "draft".* FROM (
2128 SELECT
2129 "initiative"."id" AS "initiative_id",
2130 min("draft"."id") AS "draft_id"
2131 FROM "initiative" JOIN "draft"
2132 ON "initiative"."id" = "draft"."initiative_id"
2133 GROUP BY "initiative"."id"
2134 ) AS "subquery"
2135 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
2137 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
2140 CREATE VIEW "current_draft" AS
2141 SELECT "draft".* FROM (
2142 SELECT
2143 "initiative"."id" AS "initiative_id",
2144 max("draft"."id") AS "draft_id"
2145 FROM "initiative" JOIN "draft"
2146 ON "initiative"."id" = "draft"."initiative_id"
2147 GROUP BY "initiative"."id"
2148 ) AS "subquery"
2149 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
2151 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
2154 CREATE VIEW "critical_opinion" AS
2155 SELECT * FROM "opinion"
2156 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
2157 OR ("degree" = -2 AND "fulfilled" = TRUE);
2159 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
2162 CREATE VIEW "issue_supporter_in_admission_state" AS
2163 SELECT DISTINCT
2164 "area"."unit_id",
2165 "issue"."area_id",
2166 "issue"."id" AS "issue_id",
2167 "supporter"."member_id",
2168 "direct_interest_snapshot"."weight"
2169 FROM "issue"
2170 JOIN "area" ON "area"."id" = "issue"."area_id"
2171 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
2172 JOIN "direct_interest_snapshot"
2173 ON "direct_interest_snapshot"."issue_id" = "issue"."id"
2174 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
2175 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
2176 WHERE "issue"."state" = 'admission'::"issue_state";
2178 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';
2181 CREATE VIEW "initiative_suggestion_order_calculation" AS
2182 SELECT
2183 "initiative"."id" AS "initiative_id",
2184 ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final"
2185 FROM "initiative" JOIN "issue"
2186 ON "initiative"."issue_id" = "issue"."id"
2187 WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL)
2188 OR ("initiative"."final_suggestion_order_calculated" = FALSE);
2190 COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated';
2192 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';
2195 CREATE VIEW "individual_suggestion_ranking" AS
2196 SELECT
2197 "opinion"."initiative_id",
2198 "opinion"."member_id",
2199 "direct_interest_snapshot"."weight",
2200 CASE WHEN
2201 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
2202 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
2203 THEN 1 ELSE
2204 CASE WHEN
2205 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
2206 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
2207 THEN 2 ELSE
2208 CASE WHEN
2209 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
2210 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
2211 THEN 3 ELSE 4 END
2212 END
2213 END AS "preference",
2214 "opinion"."suggestion_id"
2215 FROM "opinion"
2216 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
2217 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
2218 JOIN "direct_interest_snapshot"
2219 ON "direct_interest_snapshot"."issue_id" = "issue"."id"
2220 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
2221 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
2223 COMMENT ON VIEW "individual_suggestion_ranking" IS 'Helper view for "lf_update_suggestion_order" to allow a proportional ordering of suggestions within an initiative';
2226 CREATE VIEW "battle_participant" AS
2227 SELECT "initiative"."id", "initiative"."issue_id"
2228 FROM "issue" JOIN "initiative"
2229 ON "issue"."id" = "initiative"."issue_id"
2230 WHERE "initiative"."admitted"
2231 UNION ALL
2232 SELECT NULL, "id" AS "issue_id"
2233 FROM "issue";
2235 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
2238 CREATE VIEW "battle_view" AS
2239 SELECT
2240 "issue"."id" AS "issue_id",
2241 "winning_initiative"."id" AS "winning_initiative_id",
2242 "losing_initiative"."id" AS "losing_initiative_id",
2243 sum(
2244 CASE WHEN
2245 coalesce("better_vote"."grade", 0) >
2246 coalesce("worse_vote"."grade", 0)
2247 THEN "direct_voter"."weight" ELSE 0 END
2248 ) AS "count"
2249 FROM "issue"
2250 LEFT JOIN "direct_voter"
2251 ON "issue"."id" = "direct_voter"."issue_id"
2252 JOIN "battle_participant" AS "winning_initiative"
2253 ON "issue"."id" = "winning_initiative"."issue_id"
2254 JOIN "battle_participant" AS "losing_initiative"
2255 ON "issue"."id" = "losing_initiative"."issue_id"
2256 LEFT JOIN "vote" AS "better_vote"
2257 ON "direct_voter"."member_id" = "better_vote"."member_id"
2258 AND "winning_initiative"."id" = "better_vote"."initiative_id"
2259 LEFT JOIN "vote" AS "worse_vote"
2260 ON "direct_voter"."member_id" = "worse_vote"."member_id"
2261 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
2262 WHERE "issue"."state" = 'voting'
2263 AND "issue"."phase_finished" NOTNULL
2264 AND (
2265 "winning_initiative"."id" != "losing_initiative"."id" OR
2266 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
2267 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
2268 GROUP BY
2269 "issue"."id",
2270 "winning_initiative"."id",
2271 "losing_initiative"."id";
2273 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';
2276 CREATE VIEW "expired_session" AS
2277 SELECT * FROM "session" WHERE now() > "expiry";
2279 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
2280 DELETE FROM "session" WHERE "ident" = OLD."ident";
2282 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
2283 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
2286 CREATE VIEW "open_issue" AS
2287 SELECT * FROM "issue" WHERE "closed" ISNULL;
2289 COMMENT ON VIEW "open_issue" IS 'All open issues';
2292 CREATE VIEW "member_contingent" AS
2293 SELECT
2294 "member"."id" AS "member_id",
2295 "contingent"."polling",
2296 "contingent"."time_frame",
2297 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
2298 (
2299 SELECT count(1) FROM "draft"
2300 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
2301 WHERE "draft"."author_id" = "member"."id"
2302 AND "initiative"."polling" = "contingent"."polling"
2303 AND "draft"."created" > now() - "contingent"."time_frame"
2304 ) + (
2305 SELECT count(1) FROM "suggestion"
2306 JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"
2307 WHERE "suggestion"."author_id" = "member"."id"
2308 AND "contingent"."polling" = FALSE
2309 AND "suggestion"."created" > now() - "contingent"."time_frame"
2310 )
2311 ELSE NULL END AS "text_entry_count",
2312 "contingent"."text_entry_limit",
2313 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
2314 SELECT count(1) FROM "opening_draft" AS "draft"
2315 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
2316 WHERE "draft"."author_id" = "member"."id"
2317 AND "initiative"."polling" = "contingent"."polling"
2318 AND "draft"."created" > now() - "contingent"."time_frame"
2319 ) ELSE NULL END AS "initiative_count",
2320 "contingent"."initiative_limit"
2321 FROM "member" CROSS JOIN "contingent";
2323 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
2325 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
2326 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
2329 CREATE VIEW "member_contingent_left" AS
2330 SELECT
2331 "member_id",
2332 "polling",
2333 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
2334 max("initiative_limit" - "initiative_count") AS "initiatives_left"
2335 FROM "member_contingent" GROUP BY "member_id", "polling";
2337 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.';
2340 CREATE VIEW "event_seen_by_member" AS
2341 SELECT
2342 "member"."id" AS "seen_by_member_id",
2343 CASE WHEN "event"."state" IN (
2344 'voting',
2345 'finished_without_winner',
2346 'finished_with_winner'
2347 ) THEN
2348 'voting'::"notify_level"
2349 ELSE
2350 CASE WHEN "event"."state" IN (
2351 'verification',
2352 'canceled_after_revocation_during_verification',
2353 'canceled_no_initiative_admitted'
2354 ) THEN
2355 'verification'::"notify_level"
2356 ELSE
2357 CASE WHEN "event"."state" IN (
2358 'discussion',
2359 'canceled_after_revocation_during_discussion'
2360 ) THEN
2361 'discussion'::"notify_level"
2362 ELSE
2363 'all'::"notify_level"
2364 END
2365 END
2366 END AS "notify_level",
2367 "event".*
2368 FROM "member" CROSS JOIN "event"
2369 LEFT JOIN "issue"
2370 ON "event"."issue_id" = "issue"."id"
2371 LEFT JOIN "membership"
2372 ON "member"."id" = "membership"."member_id"
2373 AND "issue"."area_id" = "membership"."area_id"
2374 LEFT JOIN "interest"
2375 ON "member"."id" = "interest"."member_id"
2376 AND "event"."issue_id" = "interest"."issue_id"
2377 LEFT JOIN "ignored_member"
2378 ON "member"."id" = "ignored_member"."member_id"
2379 AND "event"."member_id" = "ignored_member"."other_member_id"
2380 LEFT JOIN "ignored_initiative"
2381 ON "member"."id" = "ignored_initiative"."member_id"
2382 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2383 WHERE (
2384 "interest"."member_id" NOTNULL OR
2385 ( "membership"."member_id" NOTNULL AND
2386 "event"."event" IN (
2387 'issue_state_changed',
2388 'initiative_created_in_new_issue',
2389 'initiative_created_in_existing_issue',
2390 'initiative_revoked' ) ) )
2391 AND "ignored_member"."member_id" ISNULL
2392 AND "ignored_initiative"."member_id" ISNULL;
2394 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"';
2398 ------------------------------------------------------
2399 -- Row set returning function for delegation chains --
2400 ------------------------------------------------------
2403 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
2404 ('first', 'intermediate', 'last', 'repetition');
2406 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
2409 CREATE TYPE "delegation_chain_row" AS (
2410 "index" INT4,
2411 "member_id" INT4,
2412 "member_valid" BOOLEAN,
2413 "participation" BOOLEAN,
2414 "overridden" BOOLEAN,
2415 "scope_in" "delegation_scope",
2416 "scope_out" "delegation_scope",
2417 "disabled_out" BOOLEAN,
2418 "loop" "delegation_chain_loop_tag" );
2420 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';
2422 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
2423 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';
2424 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
2425 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
2426 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
2427 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
2428 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
2431 CREATE FUNCTION "delegation_chain_for_closed_issue"
2432 ( "member_id_p" "member"."id"%TYPE,
2433 "issue_id_p" "issue"."id"%TYPE )
2434 RETURNS SETOF "delegation_chain_row"
2435 LANGUAGE 'plpgsql' STABLE AS $$
2436 DECLARE
2437 "output_row" "delegation_chain_row";
2438 "direct_voter_row" "direct_voter"%ROWTYPE;
2439 "delegating_voter_row" "delegating_voter"%ROWTYPE;
2440 BEGIN
2441 "output_row"."index" := 0;
2442 "output_row"."member_id" := "member_id_p";
2443 "output_row"."member_valid" := TRUE;
2444 "output_row"."participation" := FALSE;
2445 "output_row"."overridden" := FALSE;
2446 "output_row"."disabled_out" := FALSE;
2447 LOOP
2448 SELECT INTO "direct_voter_row" * FROM "direct_voter"
2449 WHERE "issue_id" = "issue_id_p"
2450 AND "member_id" = "output_row"."member_id";
2451 IF "direct_voter_row"."member_id" NOTNULL THEN
2452 "output_row"."participation" := TRUE;
2453 "output_row"."scope_out" := NULL;
2454 "output_row"."disabled_out" := NULL;
2455 RETURN NEXT "output_row";
2456 RETURN;
2457 END IF;
2458 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"
2459 WHERE "issue_id" = "issue_id_p"
2460 AND "member_id" = "output_row"."member_id";
2461 IF "delegating_voter_row"."member_id" ISNULL THEN
2462 RETURN;
2463 END IF;
2464 "output_row"."scope_out" := "delegating_voter_row"."scope";
2465 RETURN NEXT "output_row";
2466 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];
2467 "output_row"."scope_in" := "output_row"."scope_out";
2468 END LOOP;
2469 END;
2470 $$;
2472 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"
2473 ( "member"."id"%TYPE,
2474 "member"."id"%TYPE )
2475 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';
2478 CREATE FUNCTION "delegation_chain"
2479 ( "member_id_p" "member"."id"%TYPE,
2480 "unit_id_p" "unit"."id"%TYPE,
2481 "area_id_p" "area"."id"%TYPE,
2482 "issue_id_p" "issue"."id"%TYPE,
2483 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2484 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2485 RETURNS SETOF "delegation_chain_row"
2486 LANGUAGE 'plpgsql' STABLE AS $$
2487 DECLARE
2488 "scope_v" "delegation_scope";
2489 "unit_id_v" "unit"."id"%TYPE;
2490 "area_id_v" "area"."id"%TYPE;
2491 "issue_row" "issue"%ROWTYPE;
2492 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2493 "loop_member_id_v" "member"."id"%TYPE;
2494 "output_row" "delegation_chain_row";
2495 "output_rows" "delegation_chain_row"[];
2496 "simulate_v" BOOLEAN;
2497 "simulate_here_v" BOOLEAN;
2498 "delegation_row" "delegation"%ROWTYPE;
2499 "row_count" INT4;
2500 "i" INT4;
2501 "loop_v" BOOLEAN;
2502 BEGIN
2503 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
2504 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
2505 END IF;
2506 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
2507 "simulate_v" := TRUE;
2508 ELSE
2509 "simulate_v" := FALSE;
2510 END IF;
2511 IF
2512 "unit_id_p" NOTNULL AND
2513 "area_id_p" ISNULL AND
2514 "issue_id_p" ISNULL
2515 THEN
2516 "scope_v" := 'unit';
2517 "unit_id_v" := "unit_id_p";
2518 ELSIF
2519 "unit_id_p" ISNULL AND
2520 "area_id_p" NOTNULL AND
2521 "issue_id_p" ISNULL
2522 THEN
2523 "scope_v" := 'area';
2524 "area_id_v" := "area_id_p";
2525 SELECT "unit_id" INTO "unit_id_v"
2526 FROM "area" WHERE "id" = "area_id_v";
2527 ELSIF
2528 "unit_id_p" ISNULL AND
2529 "area_id_p" ISNULL AND
2530 "issue_id_p" NOTNULL
2531 THEN
2532 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
2533 IF "issue_row"."id" ISNULL THEN
2534 RETURN;
2535 END IF;
2536 IF "issue_row"."closed" NOTNULL THEN
2537 IF "simulate_v" THEN
2538 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
2539 END IF;
2540 FOR "output_row" IN
2541 SELECT * FROM
2542 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
2543 LOOP
2544 RETURN NEXT "output_row";
2545 END LOOP;
2546 RETURN;
2547 END IF;
2548 "scope_v" := 'issue';
2549 SELECT "area_id" INTO "area_id_v"
2550 FROM "issue" WHERE "id" = "issue_id_p";
2551 SELECT "unit_id" INTO "unit_id_v"
2552 FROM "area" WHERE "id" = "area_id_v";
2553 ELSE
2554 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2555 END IF;
2556 "visited_member_ids" := '{}';
2557 "loop_member_id_v" := NULL;
2558 "output_rows" := '{}';
2559 "output_row"."index" := 0;
2560 "output_row"."member_id" := "member_id_p";
2561 "output_row"."member_valid" := TRUE;
2562 "output_row"."participation" := FALSE;
2563 "output_row"."overridden" := FALSE;
2564 "output_row"."disabled_out" := FALSE;
2565 "output_row"."scope_out" := NULL;
2566 LOOP
2567 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2568 "loop_member_id_v" := "output_row"."member_id";
2569 ELSE
2570 "visited_member_ids" :=
2571 "visited_member_ids" || "output_row"."member_id";
2572 END IF;
2573 IF "output_row"."participation" ISNULL THEN
2574 "output_row"."overridden" := NULL;
2575 ELSIF "output_row"."participation" THEN
2576 "output_row"."overridden" := TRUE;
2577 END IF;
2578 "output_row"."scope_in" := "output_row"."scope_out";
2579 "output_row"."member_valid" := EXISTS (
2580 SELECT NULL FROM "member" JOIN "privilege"
2581 ON "privilege"."member_id" = "member"."id"
2582 AND "privilege"."unit_id" = "unit_id_v"
2583 WHERE "id" = "output_row"."member_id"
2584 AND "member"."active" AND "privilege"."voting_right"
2585 );
2586 "simulate_here_v" := (
2587 "simulate_v" AND
2588 "output_row"."member_id" = "member_id_p"
2589 );
2590 "delegation_row" := ROW(NULL);
2591 IF "output_row"."member_valid" OR "simulate_here_v" THEN
2592 IF "scope_v" = 'unit' THEN
2593 IF NOT "simulate_here_v" THEN
2594 SELECT * INTO "delegation_row" FROM "delegation"
2595 WHERE "truster_id" = "output_row"."member_id"
2596 AND "unit_id" = "unit_id_v";
2597 END IF;
2598 ELSIF "scope_v" = 'area' THEN
2599 "output_row"."participation" := EXISTS (
2600 SELECT NULL FROM "membership"
2601 WHERE "area_id" = "area_id_p"
2602 AND "member_id" = "output_row"."member_id"
2603 );
2604 IF "simulate_here_v" THEN
2605 IF "simulate_trustee_id_p" ISNULL THEN
2606 SELECT * INTO "delegation_row" FROM "delegation"
2607 WHERE "truster_id" = "output_row"."member_id"
2608 AND "unit_id" = "unit_id_v";
2609 END IF;
2610 ELSE
2611 SELECT * INTO "delegation_row" FROM "delegation"
2612 WHERE "truster_id" = "output_row"."member_id"
2613 AND (
2614 "unit_id" = "unit_id_v" OR
2615 "area_id" = "area_id_v"
2616 )
2617 ORDER BY "scope" DESC;
2618 END IF;
2619 ELSIF "scope_v" = 'issue' THEN
2620 IF "issue_row"."fully_frozen" ISNULL THEN
2621 "output_row"."participation" := EXISTS (
2622 SELECT NULL FROM "interest"
2623 WHERE "issue_id" = "issue_id_p"
2624 AND "member_id" = "output_row"."member_id"
2625 );
2626 ELSE
2627 IF "output_row"."member_id" = "member_id_p" THEN
2628 "output_row"."participation" := EXISTS (
2629 SELECT NULL FROM "direct_voter"
2630 WHERE "issue_id" = "issue_id_p"
2631 AND "member_id" = "output_row"."member_id"
2632 );
2633 ELSE
2634 "output_row"."participation" := NULL;
2635 END IF;
2636 END IF;
2637 IF "simulate_here_v" THEN
2638 IF "simulate_trustee_id_p" ISNULL THEN
2639 SELECT * INTO "delegation_row" FROM "delegation"
2640 WHERE "truster_id" = "output_row"."member_id"
2641 AND (
2642 "unit_id" = "unit_id_v" OR
2643 "area_id" = "area_id_v"
2644 )
2645 ORDER BY "scope" DESC;
2646 END IF;
2647 ELSE
2648 SELECT * INTO "delegation_row" FROM "delegation"
2649 WHERE "truster_id" = "output_row"."member_id"
2650 AND (
2651 "unit_id" = "unit_id_v" OR
2652 "area_id" = "area_id_v" OR
2653 "issue_id" = "issue_id_p"
2654 )
2655 ORDER BY "scope" DESC;
2656 END IF;
2657 END IF;
2658 ELSE
2659 "output_row"."participation" := FALSE;
2660 END IF;
2661 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
2662 "output_row"."scope_out" := "scope_v";
2663 "output_rows" := "output_rows" || "output_row";
2664 "output_row"."member_id" := "simulate_trustee_id_p";
2665 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2666 "output_row"."scope_out" := "delegation_row"."scope";
2667 "output_rows" := "output_rows" || "output_row";
2668 "output_row"."member_id" := "delegation_row"."trustee_id";
2669 ELSIF "delegation_row"."scope" NOTNULL THEN
2670 "output_row"."scope_out" := "delegation_row"."scope";
2671 "output_row"."disabled_out" := TRUE;
2672 "output_rows" := "output_rows" || "output_row";
2673 EXIT;
2674 ELSE
2675 "output_row"."scope_out" := NULL;
2676 "output_rows" := "output_rows" || "output_row";
2677 EXIT;
2678 END IF;
2679 EXIT WHEN "loop_member_id_v" NOTNULL;
2680 "output_row"."index" := "output_row"."index" + 1;
2681 END LOOP;
2682 "row_count" := array_upper("output_rows", 1);
2683 "i" := 1;
2684 "loop_v" := FALSE;
2685 LOOP
2686 "output_row" := "output_rows"["i"];
2687 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2688 IF "loop_v" THEN
2689 IF "i" + 1 = "row_count" THEN
2690 "output_row"."loop" := 'last';
2691 ELSIF "i" = "row_count" THEN
2692 "output_row"."loop" := 'repetition';
2693 ELSE
2694 "output_row"."loop" := 'intermediate';
2695 END IF;
2696 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2697 "output_row"."loop" := 'first';
2698 "loop_v" := TRUE;
2699 END IF;
2700 IF "scope_v" = 'unit' THEN
2701 "output_row"."participation" := NULL;
2702 END IF;
2703 RETURN NEXT "output_row";
2704 "i" := "i" + 1;
2705 END LOOP;
2706 RETURN;
2707 END;
2708 $$;
2710 COMMENT ON FUNCTION "delegation_chain"
2711 ( "member"."id"%TYPE,
2712 "unit"."id"%TYPE,
2713 "area"."id"%TYPE,
2714 "issue"."id"%TYPE,
2715 "member"."id"%TYPE,
2716 BOOLEAN )
2717 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
2721 ---------------------------------------------------------
2722 -- Single row returning function for delegation chains --
2723 ---------------------------------------------------------
2726 CREATE TYPE "delegation_info_loop_type" AS ENUM
2727 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');
2729 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''';
2732 CREATE TYPE "delegation_info_type" AS (
2733 "own_participation" BOOLEAN,
2734 "own_delegation_scope" "delegation_scope",
2735 "first_trustee_id" INT4,
2736 "first_trustee_participation" BOOLEAN,
2737 "first_trustee_ellipsis" BOOLEAN,
2738 "other_trustee_id" INT4,
2739 "other_trustee_participation" BOOLEAN,
2740 "other_trustee_ellipsis" BOOLEAN,
2741 "delegation_loop" "delegation_info_loop_type",
2742 "participating_member_id" INT4 );
2744 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';
2746 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';
2747 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';
2748 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';
2749 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
2750 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';
2751 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';
2752 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)';
2753 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';
2754 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';
2755 COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain';
2758 CREATE FUNCTION "delegation_info"
2759 ( "member_id_p" "member"."id"%TYPE,
2760 "unit_id_p" "unit"."id"%TYPE,
2761 "area_id_p" "area"."id"%TYPE,
2762 "issue_id_p" "issue"."id"%TYPE,
2763 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2764 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2765 RETURNS "delegation_info_type"
2766 LANGUAGE 'plpgsql' STABLE AS $$
2767 DECLARE
2768 "current_row" "delegation_chain_row";
2769 "result" "delegation_info_type";
2770 BEGIN
2771 "result"."own_participation" := FALSE;
2772 FOR "current_row" IN
2773 SELECT * FROM "delegation_chain"(
2774 "member_id_p",
2775 "unit_id_p", "area_id_p", "issue_id_p",
2776 "simulate_trustee_id_p", "simulate_default_p")
2777 LOOP
2778 IF
2779 "result"."participating_member_id" ISNULL AND
2780 "current_row"."participation"
2781 THEN
2782 "result"."participating_member_id" := "current_row"."member_id";
2783 END IF;
2784 IF "current_row"."member_id" = "member_id_p" THEN
2785 "result"."own_participation" := "current_row"."participation";
2786 "result"."own_delegation_scope" := "current_row"."scope_out";
2787 IF "current_row"."loop" = 'first' THEN
2788 "result"."delegation_loop" := 'own';
2789 END IF;
2790 ELSIF
2791 "current_row"."member_valid" AND
2792 ( "current_row"."loop" ISNULL OR
2793 "current_row"."loop" != 'repetition' )
2794 THEN
2795 IF "result"."first_trustee_id" ISNULL THEN
2796 "result"."first_trustee_id" := "current_row"."member_id";
2797 "result"."first_trustee_participation" := "current_row"."participation";
2798 "result"."first_trustee_ellipsis" := FALSE;
2799 IF "current_row"."loop" = 'first' THEN
2800 "result"."delegation_loop" := 'first';
2801 END IF;
2802 ELSIF "result"."other_trustee_id" ISNULL THEN
2803 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
2804 "result"."other_trustee_id" := "current_row"."member_id";
2805 "result"."other_trustee_participation" := TRUE;
2806 "result"."other_trustee_ellipsis" := FALSE;
2807 IF "current_row"."loop" = 'first' THEN
2808 "result"."delegation_loop" := 'other';
2809 END IF;
2810 ELSE
2811 "result"."first_trustee_ellipsis" := TRUE;
2812 IF "current_row"."loop" = 'first' THEN
2813 "result"."delegation_loop" := 'first_ellipsis';
2814 END IF;
2815 END IF;
2816 ELSE
2817 "result"."other_trustee_ellipsis" := TRUE;
2818 IF "current_row"."loop" = 'first' THEN
2819 "result"."delegation_loop" := 'other_ellipsis';
2820 END IF;
2821 END IF;
2822 END IF;
2823 END LOOP;
2824 RETURN "result";
2825 END;
2826 $$;
2828 COMMENT ON FUNCTION "delegation_info"
2829 ( "member"."id"%TYPE,
2830 "unit"."id"%TYPE,
2831 "area"."id"%TYPE,
2832 "issue"."id"%TYPE,
2833 "member"."id"%TYPE,
2834 BOOLEAN )
2835 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
2839 ---------------------------
2840 -- Transaction isolation --
2841 ---------------------------
2844 CREATE FUNCTION "require_transaction_isolation"()
2845 RETURNS VOID
2846 LANGUAGE 'plpgsql' VOLATILE AS $$
2847 BEGIN
2848 IF
2849 current_setting('transaction_isolation') NOT IN
2850 ('repeatable read', 'serializable')
2851 THEN
2852 RAISE EXCEPTION 'Insufficient transaction isolation level' USING
2853 HINT = 'Consider using SET TRANSACTION ISOLATION LEVEL REPEATABLE READ.';
2854 END IF;
2855 RETURN;
2856 END;
2857 $$;
2859 COMMENT ON FUNCTION "require_transaction_isolation"() IS 'Throws an exception, if transaction isolation level is too low to provide a consistent snapshot';
2862 CREATE FUNCTION "dont_require_transaction_isolation"()
2863 RETURNS VOID
2864 LANGUAGE 'plpgsql' VOLATILE AS $$
2865 BEGIN
2866 IF
2867 current_setting('transaction_isolation') IN
2868 ('repeatable read', 'serializable')
2869 THEN
2870 RAISE WARNING 'Unneccessary transaction isolation level: %',
2871 current_setting('transaction_isolation');
2872 END IF;
2873 RETURN;
2874 END;
2875 $$;
2877 COMMENT ON FUNCTION "dont_require_transaction_isolation"() IS 'Raises a warning, if transaction isolation level is higher than READ COMMITTED';
2881 ------------------------------------------------------------------------
2882 -- Regular tasks, except calculcation of snapshots and voting results --
2883 ------------------------------------------------------------------------
2886 CREATE FUNCTION "check_activity"()
2887 RETURNS VOID
2888 LANGUAGE 'plpgsql' VOLATILE AS $$
2889 DECLARE
2890 "system_setting_row" "system_setting"%ROWTYPE;
2891 BEGIN
2892 PERFORM "dont_require_transaction_isolation"();
2893 SELECT * INTO "system_setting_row" FROM "system_setting";
2894 IF "system_setting_row"."member_ttl" NOTNULL THEN
2895 UPDATE "member" SET "active" = FALSE
2896 WHERE "active" = TRUE
2897 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
2898 END IF;
2899 RETURN;
2900 END;
2901 $$;
2903 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
2906 CREATE FUNCTION "calculate_member_counts"()
2907 RETURNS VOID
2908 LANGUAGE 'plpgsql' VOLATILE AS $$
2909 BEGIN
2910 PERFORM "require_transaction_isolation"();
2911 DELETE FROM "member_count";
2912 INSERT INTO "member_count" ("total_count")
2913 SELECT "total_count" FROM "member_count_view";
2914 UPDATE "unit" SET "member_count" = "view"."member_count"
2915 FROM "unit_member_count" AS "view"
2916 WHERE "view"."unit_id" = "unit"."id";
2917 UPDATE "area" SET
2918 "direct_member_count" = "view"."direct_member_count",
2919 "member_weight" = "view"."member_weight"
2920 FROM "area_member_count" AS "view"
2921 WHERE "view"."area_id" = "area"."id";
2922 RETURN;
2923 END;
2924 $$;
2926 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"';
2930 ------------------------------------
2931 -- Calculation of harmonic weight --
2932 ------------------------------------
2935 CREATE VIEW "remaining_harmonic_supporter_weight" AS
2936 SELECT
2937 "direct_interest_snapshot"."issue_id",
2938 "direct_interest_snapshot"."event",
2939 "direct_interest_snapshot"."member_id",
2940 "direct_interest_snapshot"."weight" AS "weight_num",
2941 count("initiative"."id") AS "weight_den"
2942 FROM "issue"
2943 JOIN "direct_interest_snapshot"
2944 ON "issue"."id" = "direct_interest_snapshot"."issue_id"
2945 AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
2946 JOIN "initiative"
2947 ON "issue"."id" = "initiative"."issue_id"
2948 AND "initiative"."harmonic_weight" ISNULL
2949 JOIN "direct_supporter_snapshot"
2950 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
2951 AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event"
2952 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
2953 AND (
2954 "direct_supporter_snapshot"."satisfied" = TRUE OR
2955 coalesce("initiative"."admitted", FALSE) = FALSE
2956 )
2957 GROUP BY
2958 "direct_interest_snapshot"."issue_id",
2959 "direct_interest_snapshot"."event",
2960 "direct_interest_snapshot"."member_id",
2961 "direct_interest_snapshot"."weight";
2963 COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';
2966 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
2967 SELECT
2968 "initiative"."issue_id",
2969 "initiative"."id" AS "initiative_id",
2970 "initiative"."admitted",
2971 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
2972 "remaining_harmonic_supporter_weight"."weight_den"
2973 FROM "remaining_harmonic_supporter_weight"
2974 JOIN "initiative"
2975 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
2976 AND "initiative"."harmonic_weight" ISNULL
2977 JOIN "direct_supporter_snapshot"
2978 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
2979 AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event"
2980 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
2981 AND (
2982 "direct_supporter_snapshot"."satisfied" = TRUE OR
2983 coalesce("initiative"."admitted", FALSE) = FALSE
2984 )
2985 GROUP BY
2986 "initiative"."issue_id",
2987 "initiative"."id",
2988 "initiative"."admitted",
2989 "remaining_harmonic_supporter_weight"."weight_den";
2991 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
2994 CREATE VIEW "remaining_harmonic_initiative_weight_dummies" AS
2995 SELECT
2996 "issue_id",
2997 "id" AS "initiative_id",
2998 "admitted",
2999 0 AS "weight_num",
3000 1 AS "weight_den"
3001 FROM "initiative"
3002 WHERE "harmonic_weight" ISNULL;
3004 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';
3007 CREATE FUNCTION "set_harmonic_initiative_weights"
3008 ( "issue_id_p" "issue"."id"%TYPE )
3009 RETURNS VOID
3010 LANGUAGE 'plpgsql' VOLATILE AS $$
3011 DECLARE
3012 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;
3013 "i" INT4;
3014 "count_v" INT4;
3015 "summand_v" FLOAT;
3016 "id_ary" INT4[];
3017 "weight_ary" FLOAT[];
3018 "min_weight_v" FLOAT;
3019 BEGIN
3020 PERFORM "require_transaction_isolation"();
3021 UPDATE "initiative" SET "harmonic_weight" = NULL
3022 WHERE "issue_id" = "issue_id_p";
3023 LOOP
3024 "min_weight_v" := NULL;
3025 "i" := 0;
3026 "count_v" := 0;
3027 FOR "weight_row" IN
3028 SELECT * FROM "remaining_harmonic_initiative_weight_summands"
3029 WHERE "issue_id" = "issue_id_p"
3030 AND (
3031 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
3032 SELECT NULL FROM "initiative"
3033 WHERE "issue_id" = "issue_id_p"
3034 AND "harmonic_weight" ISNULL
3035 AND coalesce("admitted", FALSE) = FALSE
3036 )
3037 )
3038 UNION ALL -- needed for corner cases
3039 SELECT * FROM "remaining_harmonic_initiative_weight_dummies"
3040 WHERE "issue_id" = "issue_id_p"
3041 AND (
3042 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
3043 SELECT NULL FROM "initiative"
3044 WHERE "issue_id" = "issue_id_p"
3045 AND "harmonic_weight" ISNULL
3046 AND coalesce("admitted", FALSE) = FALSE
3047 )
3048 )
3049 ORDER BY "initiative_id" DESC, "weight_den" DESC
3050 -- NOTE: non-admitted initiatives placed first (at last positions),
3051 -- latest initiatives treated worse in case of tie
3052 LOOP
3053 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
3054 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
3055 "i" := "i" + 1;
3056 "count_v" := "i";
3057 "id_ary"["i"] := "weight_row"."initiative_id";
3058 "weight_ary"["i"] := "summand_v";
3059 ELSE
3060 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
3061 END IF;
3062 END LOOP;
3063 EXIT WHEN "count_v" = 0;
3064 "i" := 1;
3065 LOOP
3066 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
3067 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
3068 "min_weight_v" := "weight_ary"["i"];
3069 END IF;
3070 "i" := "i" + 1;
3071 EXIT WHEN "i" > "count_v";
3072 END LOOP;
3073 "i" := 1;
3074 LOOP
3075 IF "weight_ary"["i"] = "min_weight_v" THEN
3076 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"
3077 WHERE "id" = "id_ary"["i"];
3078 EXIT;
3079 END IF;
3080 "i" := "i" + 1;
3081 END LOOP;
3082 END LOOP;
3083 UPDATE "initiative" SET "harmonic_weight" = 0
3084 WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;
3085 END;
3086 $$;
3088 COMMENT ON FUNCTION "set_harmonic_initiative_weights"
3089 ( "issue"."id"%TYPE )
3090 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
3094 ------------------------------
3095 -- Calculation of snapshots --
3096 ------------------------------
3099 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
3100 ( "issue_id_p" "issue"."id"%TYPE,
3101 "member_id_p" "member"."id"%TYPE,
3102 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3103 RETURNS "direct_population_snapshot"."weight"%TYPE
3104 LANGUAGE 'plpgsql' VOLATILE AS $$
3105 DECLARE
3106 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3107 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
3108 "weight_v" INT4;
3109 "sub_weight_v" INT4;
3110 BEGIN
3111 PERFORM "require_transaction_isolation"();
3112 "weight_v" := 0;
3113 FOR "issue_delegation_row" IN
3114 SELECT * FROM "issue_delegation"
3115 WHERE "trustee_id" = "member_id_p"
3116 AND "issue_id" = "issue_id_p"
3117 LOOP
3118 IF NOT EXISTS (
3119 SELECT NULL FROM "direct_population_snapshot"
3120 WHERE "issue_id" = "issue_id_p"
3121 AND "event" = 'periodic'
3122 AND "member_id" = "issue_delegation_row"."truster_id"
3123 ) AND NOT EXISTS (
3124 SELECT NULL FROM "delegating_population_snapshot"
3125 WHERE "issue_id" = "issue_id_p"
3126 AND "event" = 'periodic'
3127 AND "member_id" = "issue_delegation_row"."truster_id"
3128 ) THEN
3129 "delegate_member_ids_v" :=
3130 "member_id_p" || "delegate_member_ids_p";
3131 INSERT INTO "delegating_population_snapshot" (
3132 "issue_id",
3133 "event",
3134 "member_id",
3135 "scope",
3136 "delegate_member_ids"
3137 ) VALUES (
3138 "issue_id_p",
3139 'periodic',
3140 "issue_delegation_row"."truster_id",
3141 "issue_delegation_row"."scope",
3142 "delegate_member_ids_v"
3143 );
3144 "sub_weight_v" := 1 +
3145 "weight_of_added_delegations_for_population_snapshot"(
3146 "issue_id_p",
3147 "issue_delegation_row"."truster_id",
3148 "delegate_member_ids_v"
3149 );
3150 UPDATE "delegating_population_snapshot"
3151 SET "weight" = "sub_weight_v"
3152 WHERE "issue_id" = "issue_id_p"
3153 AND "event" = 'periodic'
3154 AND "member_id" = "issue_delegation_row"."truster_id";
3155 "weight_v" := "weight_v" + "sub_weight_v";
3156 END IF;
3157 END LOOP;
3158 RETURN "weight_v";
3159 END;
3160 $$;
3162 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
3163 ( "issue"."id"%TYPE,
3164 "member"."id"%TYPE,
3165 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3166 IS 'Helper function for "create_population_snapshot" function';
3169 CREATE FUNCTION "create_population_snapshot"
3170 ( "issue_id_p" "issue"."id"%TYPE )
3171 RETURNS VOID
3172 LANGUAGE 'plpgsql' VOLATILE AS $$
3173 DECLARE
3174 "member_id_v" "member"."id"%TYPE;
3175 BEGIN
3176 PERFORM "require_transaction_isolation"();
3177 DELETE FROM "direct_population_snapshot"
3178 WHERE "issue_id" = "issue_id_p"
3179 AND "event" = 'periodic';
3180 DELETE FROM "delegating_population_snapshot"
3181 WHERE "issue_id" = "issue_id_p"
3182 AND "event" = 'periodic';
3183 INSERT INTO "direct_population_snapshot"
3184 ("issue_id", "event", "member_id")
3185 SELECT
3186 "issue_id_p" AS "issue_id",
3187 'periodic'::"snapshot_event" AS "event",
3188 "member"."id" AS "member_id"
3189 FROM "issue"
3190 JOIN "area" ON "issue"."area_id" = "area"."id"
3191 JOIN "membership" ON "area"."id" = "membership"."area_id"
3192 JOIN "member" ON "membership"."member_id" = "member"."id"
3193 JOIN "privilege"
3194 ON "privilege"."unit_id" = "area"."unit_id"
3195 AND "privilege"."member_id" = "member"."id"
3196 WHERE "issue"."id" = "issue_id_p"
3197 AND "member"."active" AND "privilege"."voting_right"
3198 UNION
3199 SELECT
3200 "issue_id_p" AS "issue_id",
3201 'periodic'::"snapshot_event" AS "event",
3202 "member"."id" AS "member_id"
3203 FROM "issue"
3204 JOIN "area" ON "issue"."area_id" = "area"."id"
3205 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3206 JOIN "member" ON "interest"."member_id" = "member"."id"
3207 JOIN "privilege"
3208 ON "privilege"."unit_id" = "area"."unit_id"
3209 AND "privilege"."member_id" = "member"."id"
3210 WHERE "issue"."id" = "issue_id_p"
3211 AND "member"."active" AND "privilege"."voting_right";
3212 FOR "member_id_v" IN
3213 SELECT "member_id" FROM "direct_population_snapshot"
3214 WHERE "issue_id" = "issue_id_p"
3215 AND "event" = 'periodic'
3216 LOOP
3217 UPDATE "direct_population_snapshot" SET
3218 "weight" = 1 +
3219 "weight_of_added_delegations_for_population_snapshot"(
3220 "issue_id_p",
3221 "member_id_v",
3222 '{}'
3223 )
3224 WHERE "issue_id" = "issue_id_p"
3225 AND "event" = 'periodic'
3226 AND "member_id" = "member_id_v";
3227 END LOOP;
3228 RETURN;
3229 END;
3230 $$;
3232 COMMENT ON FUNCTION "create_population_snapshot"
3233 ( "issue"."id"%TYPE )
3234 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.';
3237 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3238 ( "issue_id_p" "issue"."id"%TYPE,
3239 "member_id_p" "member"."id"%TYPE,
3240 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3241 RETURNS "direct_interest_snapshot"."weight"%TYPE
3242 LANGUAGE 'plpgsql' VOLATILE AS $$
3243 DECLARE
3244 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3245 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
3246 "weight_v" INT4;
3247 "sub_weight_v" INT4;
3248 BEGIN
3249 PERFORM "require_transaction_isolation"();
3250 "weight_v" := 0;
3251 FOR "issue_delegation_row" IN
3252 SELECT * FROM "issue_delegation"
3253 WHERE "trustee_id" = "member_id_p"
3254 AND "issue_id" = "issue_id_p"
3255 LOOP
3256 IF NOT EXISTS (
3257 SELECT NULL FROM "direct_interest_snapshot"
3258 WHERE "issue_id" = "issue_id_p"
3259 AND "event" = 'periodic'
3260 AND "member_id" = "issue_delegation_row"."truster_id"
3261 ) AND NOT EXISTS (
3262 SELECT NULL FROM "delegating_interest_snapshot"
3263 WHERE "issue_id" = "issue_id_p"
3264 AND "event" = 'periodic'
3265 AND "member_id" = "issue_delegation_row"."truster_id"
3266 ) THEN
3267 "delegate_member_ids_v" :=
3268 "member_id_p" || "delegate_member_ids_p";
3269 INSERT INTO "delegating_interest_snapshot" (
3270 "issue_id",
3271 "event",
3272 "member_id",
3273 "scope",
3274 "delegate_member_ids"
3275 ) VALUES (
3276 "issue_id_p",
3277 'periodic',
3278 "issue_delegation_row"."truster_id",
3279 "issue_delegation_row"."scope",
3280 "delegate_member_ids_v"
3281 );
3282 "sub_weight_v" := 1 +
3283 "weight_of_added_delegations_for_interest_snapshot"(
3284 "issue_id_p",
3285 "issue_delegation_row"."truster_id",
3286 "delegate_member_ids_v"
3287 );
3288 UPDATE "delegating_interest_snapshot"
3289 SET "weight" = "sub_weight_v"
3290 WHERE "issue_id" = "issue_id_p"
3291 AND "event" = 'periodic'
3292 AND "member_id" = "issue_delegation_row"."truster_id";
3293 "weight_v" := "weight_v" + "sub_weight_v";
3294 END IF;
3295 END LOOP;
3296 RETURN "weight_v";
3297 END;
3298 $$;
3300 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3301 ( "issue"."id"%TYPE,
3302 "member"."id"%TYPE,
3303 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3304 IS 'Helper function for "create_interest_snapshot" function';
3307 CREATE FUNCTION "create_interest_snapshot"
3308 ( "issue_id_p" "issue"."id"%TYPE )
3309 RETURNS VOID
3310 LANGUAGE 'plpgsql' VOLATILE AS $$
3311 DECLARE
3312 "member_id_v" "member"."id"%TYPE;
3313 BEGIN
3314 PERFORM "require_transaction_isolation"();
3315 DELETE FROM "direct_interest_snapshot"
3316 WHERE "issue_id" = "issue_id_p"
3317 AND "event" = 'periodic';
3318 DELETE FROM "delegating_interest_snapshot"
3319 WHERE "issue_id" = "issue_id_p"
3320 AND "event" = 'periodic';
3321 DELETE FROM "direct_supporter_snapshot"
3322 USING "initiative" -- NOTE: due to missing index on issue_id
3323 WHERE "initiative"."issue_id" = "issue_id_p"
3324 AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
3325 AND "direct_supporter_snapshot"."event" = 'periodic';
3326 INSERT INTO "direct_interest_snapshot"
3327 ("issue_id", "event", "member_id")
3328 SELECT
3329 "issue_id_p" AS "issue_id",
3330 'periodic' AS "event",
3331 "member"."id" AS "member_id"
3332 FROM "issue"
3333 JOIN "area" ON "issue"."area_id" = "area"."id"
3334 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3335 JOIN "member" ON "interest"."member_id" = "member"."id"
3336 JOIN "privilege"
3337 ON "privilege"."unit_id" = "area"."unit_id"
3338 AND "privilege"."member_id" = "member"."id"
3339 WHERE "issue"."id" = "issue_id_p"
3340 AND "member"."active" AND "privilege"."voting_right";
3341 FOR "member_id_v" IN
3342 SELECT "member_id" FROM "direct_interest_snapshot"
3343 WHERE "issue_id" = "issue_id_p"
3344 AND "event" = 'periodic'
3345 LOOP
3346 UPDATE "direct_interest_snapshot" SET
3347 "weight" = 1 +
3348 "weight_of_added_delegations_for_interest_snapshot"(
3349 "issue_id_p",
3350 "member_id_v",
3351 '{}'
3352 )
3353 WHERE "issue_id" = "issue_id_p"
3354 AND "event" = 'periodic'
3355 AND "member_id" = "member_id_v";
3356 END LOOP;
3357 INSERT INTO "direct_supporter_snapshot"
3358 ( "issue_id", "initiative_id", "event", "member_id",
3359 "draft_id", "informed", "satisfied" )
3360 SELECT
3361 "issue_id_p" AS "issue_id",
3362 "initiative"."id" AS "initiative_id",
3363 'periodic' AS "event",
3364 "supporter"."member_id" AS "member_id",
3365 "supporter"."draft_id" AS "draft_id",
3366 "supporter"."draft_id" = "current_draft"."id" AS "informed",
3367 NOT EXISTS (
3368 SELECT NULL FROM "critical_opinion"
3369 WHERE "initiative_id" = "initiative"."id"
3370 AND "member_id" = "supporter"."member_id"
3371 ) AS "satisfied"
3372 FROM "initiative"
3373 JOIN "supporter"
3374 ON "supporter"."initiative_id" = "initiative"."id"
3375 JOIN "current_draft"
3376 ON "initiative"."id" = "current_draft"."initiative_id"
3377 JOIN "direct_interest_snapshot"
3378 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
3379 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
3380 AND "event" = 'periodic'
3381 WHERE "initiative"."issue_id" = "issue_id_p";
3382 RETURN;
3383 END;
3384 $$;
3386 COMMENT ON FUNCTION "create_interest_snapshot"
3387 ( "issue"."id"%TYPE )
3388 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.';
3391 CREATE FUNCTION "create_snapshot"
3392 ( "issue_id_p" "issue"."id"%TYPE )
3393 RETURNS VOID
3394 LANGUAGE 'plpgsql' VOLATILE AS $$
3395 DECLARE
3396 "initiative_id_v" "initiative"."id"%TYPE;
3397 "suggestion_id_v" "suggestion"."id"%TYPE;
3398 BEGIN
3399 PERFORM "require_transaction_isolation"();
3400 PERFORM "create_population_snapshot"("issue_id_p");
3401 PERFORM "create_interest_snapshot"("issue_id_p");
3402 UPDATE "issue" SET
3403 "snapshot" = coalesce("phase_finished", now()),
3404 "latest_snapshot_event" = 'periodic',
3405 "population" = (
3406 SELECT coalesce(sum("weight"), 0)
3407 FROM "direct_population_snapshot"
3408 WHERE "issue_id" = "issue_id_p"
3409 AND "event" = 'periodic'
3410 )
3411 WHERE "id" = "issue_id_p";
3412 FOR "initiative_id_v" IN
3413 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
3414 LOOP
3415 UPDATE "initiative" SET
3416 "supporter_count" = (
3417 SELECT coalesce(sum("di"."weight"), 0)
3418 FROM "direct_interest_snapshot" AS "di"
3419 JOIN "direct_supporter_snapshot" AS "ds"
3420 ON "di"."member_id" = "ds"."member_id"
3421 WHERE "di"."issue_id" = "issue_id_p"
3422 AND "di"."event" = 'periodic'
3423 AND "ds"."initiative_id" = "initiative_id_v"
3424 AND "ds"."event" = 'periodic'
3425 ),
3426 "informed_supporter_count" = (
3427 SELECT coalesce(sum("di"."weight"), 0)
3428 FROM "direct_interest_snapshot" AS "di"
3429 JOIN "direct_supporter_snapshot" AS "ds"
3430 ON "di"."member_id" = "ds"."member_id"
3431 WHERE "di"."issue_id" = "issue_id_p"
3432 AND "di"."event" = 'periodic'
3433 AND "ds"."initiative_id" = "initiative_id_v"
3434 AND "ds"."event" = 'periodic'
3435 AND "ds"."informed"
3436 ),
3437 "satisfied_supporter_count" = (
3438 SELECT coalesce(sum("di"."weight"), 0)
3439 FROM "direct_interest_snapshot" AS "di"
3440 JOIN "direct_supporter_snapshot" AS "ds"
3441 ON "di"."member_id" = "ds"."member_id"
3442 WHERE "di"."issue_id" = "issue_id_p"
3443 AND "di"."event" = 'periodic'
3444 AND "ds"."initiative_id" = "initiative_id_v"
3445 AND "ds"."event" = 'periodic'
3446 AND "ds"."satisfied"
3447 ),
3448 "satisfied_informed_supporter_count" = (
3449 SELECT coalesce(sum("di"."weight"), 0)
3450 FROM "direct_interest_snapshot" AS "di"
3451 JOIN "direct_supporter_snapshot" AS "ds"
3452 ON "di"."member_id" = "ds"."member_id"
3453 WHERE "di"."issue_id" = "issue_id_p"
3454 AND "di"."event" = 'periodic'
3455 AND "ds"."initiative_id" = "initiative_id_v"
3456 AND "ds"."event" = 'periodic'
3457 AND "ds"."informed"
3458 AND "ds"."satisfied"
3459 )
3460 WHERE "id" = "initiative_id_v";
3461 FOR "suggestion_id_v" IN
3462 SELECT "id" FROM "suggestion"
3463 WHERE "initiative_id" = "initiative_id_v"
3464 LOOP
3465 UPDATE "suggestion" SET
3466 "minus2_unfulfilled_count" = (
3467 SELECT coalesce(sum("snapshot"."weight"), 0)
3468 FROM "issue" CROSS JOIN "opinion"
3469 JOIN "direct_interest_snapshot" AS "snapshot"
3470 ON "snapshot"."issue_id" = "issue"."id"
3471 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3472 AND "snapshot"."member_id" = "opinion"."member_id"
3473 WHERE "issue"."id" = "issue_id_p"
3474 AND "opinion"."suggestion_id" = "suggestion_id_v"
3475 AND "opinion"."degree" = -2
3476 AND "opinion"."fulfilled" = FALSE
3477 ),
3478 "minus2_fulfilled_count" = (
3479 SELECT coalesce(sum("snapshot"."weight"), 0)
3480 FROM "issue" CROSS JOIN "opinion"
3481 JOIN "direct_interest_snapshot" AS "snapshot"
3482 ON "snapshot"."issue_id" = "issue"."id"
3483 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3484 AND "snapshot"."member_id" = "opinion"."member_id"
3485 WHERE "issue"."id" = "issue_id_p"
3486 AND "opinion"."suggestion_id" = "suggestion_id_v"
3487 AND "opinion"."degree" = -2
3488 AND "opinion"."fulfilled" = TRUE
3489 ),
3490 "minus1_unfulfilled_count" = (
3491 SELECT coalesce(sum("snapshot"."weight"), 0)
3492 FROM "issue" CROSS JOIN "opinion"
3493 JOIN "direct_interest_snapshot" AS "snapshot"
3494 ON "snapshot"."issue_id" = "issue"."id"
3495 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3496 AND "snapshot"."member_id" = "opinion"."member_id"
3497 WHERE "issue"."id" = "issue_id_p"
3498 AND "opinion"."suggestion_id" = "suggestion_id_v"
3499 AND "opinion"."degree" = -1
3500 AND "opinion"."fulfilled" = FALSE
3501 ),
3502 "minus1_fulfilled_count" = (
3503 SELECT coalesce(sum("snapshot"."weight"), 0)
3504 FROM "issue" CROSS JOIN "opinion"
3505 JOIN "direct_interest_snapshot" AS "snapshot"
3506 ON "snapshot"."issue_id" = "issue"."id"
3507 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3508 AND "snapshot"."member_id" = "opinion"."member_id"
3509 WHERE "issue"."id" = "issue_id_p"
3510 AND "opinion"."suggestion_id" = "suggestion_id_v"
3511 AND "opinion"."degree" = -1
3512 AND "opinion"."fulfilled" = TRUE
3513 ),
3514 "plus1_unfulfilled_count" = (
3515 SELECT coalesce(sum("snapshot"."weight"), 0)
3516 FROM "issue" CROSS JOIN "opinion"
3517 JOIN "direct_interest_snapshot" AS "snapshot"
3518 ON "snapshot"."issue_id" = "issue"."id"
3519 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3520 AND "snapshot"."member_id" = "opinion"."member_id"
3521 WHERE "issue"."id" = "issue_id_p"
3522 AND "opinion"."suggestion_id" = "suggestion_id_v"
3523 AND "opinion"."degree" = 1
3524 AND "opinion"."fulfilled" = FALSE
3525 ),
3526 "plus1_fulfilled_count" = (
3527 SELECT coalesce(sum("snapshot"."weight"), 0)
3528 FROM "issue" CROSS JOIN "opinion"
3529 JOIN "direct_interest_snapshot" AS "snapshot"
3530 ON "snapshot"."issue_id" = "issue"."id"
3531 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3532 AND "snapshot"."member_id" = "opinion"."member_id"
3533 WHERE "issue"."id" = "issue_id_p"
3534 AND "opinion"."suggestion_id" = "suggestion_id_v"
3535 AND "opinion"."degree" = 1
3536 AND "opinion"."fulfilled" = TRUE
3537 ),
3538 "plus2_unfulfilled_count" = (
3539 SELECT coalesce(sum("snapshot"."weight"), 0)
3540 FROM "issue" CROSS JOIN "opinion"
3541 JOIN "direct_interest_snapshot" AS "snapshot"
3542 ON "snapshot"."issue_id" = "issue"."id"
3543 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3544 AND "snapshot"."member_id" = "opinion"."member_id"
3545 WHERE "issue"."id" = "issue_id_p"
3546 AND "opinion"."suggestion_id" = "suggestion_id_v"
3547 AND "opinion"."degree" = 2
3548 AND "opinion"."fulfilled" = FALSE
3549 ),
3550 "plus2_fulfilled_count" = (
3551 SELECT coalesce(sum("snapshot"."weight"), 0)
3552 FROM "issue" CROSS JOIN "opinion"
3553 JOIN "direct_interest_snapshot" AS "snapshot"
3554 ON "snapshot"."issue_id" = "issue"."id"
3555 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3556 AND "snapshot"."member_id" = "opinion"."member_id"
3557 WHERE "issue"."id" = "issue_id_p"
3558 AND "opinion"."suggestion_id" = "suggestion_id_v"
3559 AND "opinion"."degree" = 2
3560 AND "opinion"."fulfilled" = TRUE
3561 )
3562 WHERE "suggestion"."id" = "suggestion_id_v";
3563 END LOOP;
3564 END LOOP;
3565 RETURN;
3566 END;
3567 $$;
3569 COMMENT ON FUNCTION "create_snapshot"
3570 ( "issue"."id"%TYPE )
3571 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.';
3574 CREATE FUNCTION "set_snapshot_event"
3575 ( "issue_id_p" "issue"."id"%TYPE,
3576 "event_p" "snapshot_event" )
3577 RETURNS VOID
3578 LANGUAGE 'plpgsql' VOLATILE AS $$
3579 DECLARE
3580 "event_v" "issue"."latest_snapshot_event"%TYPE;
3581 BEGIN
3582 PERFORM "require_transaction_isolation"();
3583 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
3584 WHERE "id" = "issue_id_p" FOR UPDATE;
3585 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
3586 WHERE "id" = "issue_id_p";
3587 UPDATE "direct_population_snapshot" SET "event" = "event_p"
3588 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3589 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
3590 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3591 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
3592 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3593 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
3594 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3595 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
3596 FROM "initiative" -- NOTE: due to missing index on issue_id
3597 WHERE "initiative"."issue_id" = "issue_id_p"
3598 AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
3599 AND "direct_supporter_snapshot"."event" = "event_v";
3600 RETURN;
3601 END;
3602 $$;
3604 COMMENT ON FUNCTION "set_snapshot_event"
3605 ( "issue"."id"%TYPE,
3606 "snapshot_event" )
3607 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
3611 -----------------------
3612 -- Counting of votes --
3613 -----------------------
3616 CREATE FUNCTION "weight_of_added_vote_delegations"
3617 ( "issue_id_p" "issue"."id"%TYPE,
3618 "member_id_p" "member"."id"%TYPE,
3619 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
3620 RETURNS "direct_voter"."weight"%TYPE
3621 LANGUAGE 'plpgsql' VOLATILE AS $$
3622 DECLARE
3623 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3624 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
3625 "weight_v" INT4;
3626 "sub_weight_v" INT4;
3627 BEGIN
3628 PERFORM "require_transaction_isolation"();
3629 "weight_v" := 0;
3630 FOR "issue_delegation_row" IN
3631 SELECT * FROM "issue_delegation"
3632 WHERE "trustee_id" = "member_id_p"
3633 AND "issue_id" = "issue_id_p"
3634 LOOP
3635 IF NOT EXISTS (
3636 SELECT NULL FROM "direct_voter"
3637 WHERE "member_id" = "issue_delegation_row"."truster_id"
3638 AND "issue_id" = "issue_id_p"
3639 ) AND NOT EXISTS (
3640 SELECT NULL FROM "delegating_voter"
3641 WHERE "member_id" = "issue_delegation_row"."truster_id"
3642 AND "issue_id" = "issue_id_p"
3643 ) THEN
3644 "delegate_member_ids_v" :=
3645 "member_id_p" || "delegate_member_ids_p";
3646 INSERT INTO "delegating_voter" (
3647 "issue_id",
3648 "member_id",
3649 "scope",
3650 "delegate_member_ids"
3651 ) VALUES (
3652 "issue_id_p",
3653 "issue_delegation_row"."truster_id",
3654 "issue_delegation_row"."scope",
3655 "delegate_member_ids_v"
3656 );
3657 "sub_weight_v" := 1 +
3658 "weight_of_added_vote_delegations"(
3659 "issue_id_p",
3660 "issue_delegation_row"."truster_id",
3661 "delegate_member_ids_v"
3662 );
3663 UPDATE "delegating_voter"
3664 SET "weight" = "sub_weight_v"
3665 WHERE "issue_id" = "issue_id_p"
3666 AND "member_id" = "issue_delegation_row"."truster_id";
3667 "weight_v" := "weight_v" + "sub_weight_v";
3668 END IF;
3669 END LOOP;
3670 RETURN "weight_v";
3671 END;
3672 $$;
3674 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
3675 ( "issue"."id"%TYPE,
3676 "member"."id"%TYPE,
3677 "delegating_voter"."delegate_member_ids"%TYPE )
3678 IS 'Helper function for "add_vote_delegations" function';
3681 CREATE FUNCTION "add_vote_delegations"
3682 ( "issue_id_p" "issue"."id"%TYPE )
3683 RETURNS VOID
3684 LANGUAGE 'plpgsql' VOLATILE AS $$
3685 DECLARE
3686 "member_id_v" "member"."id"%TYPE;
3687 BEGIN
3688 PERFORM "require_transaction_isolation"();
3689 FOR "member_id_v" IN
3690 SELECT "member_id" FROM "direct_voter"
3691 WHERE "issue_id" = "issue_id_p"
3692 LOOP
3693 UPDATE "direct_voter" SET
3694 "weight" = "weight" + "weight_of_added_vote_delegations"(
3695 "issue_id_p",
3696 "member_id_v",
3697 '{}'
3698 )
3699 WHERE "member_id" = "member_id_v"
3700 AND "issue_id" = "issue_id_p";
3701 END LOOP;
3702 RETURN;
3703 END;
3704 $$;
3706 COMMENT ON FUNCTION "add_vote_delegations"
3707 ( "issue_id_p" "issue"."id"%TYPE )
3708 IS 'Helper function for "close_voting" function';
3711 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
3712 RETURNS VOID
3713 LANGUAGE 'plpgsql' VOLATILE AS $$
3714 DECLARE
3715 "area_id_v" "area"."id"%TYPE;
3716 "unit_id_v" "unit"."id"%TYPE;
3717 "member_id_v" "member"."id"%TYPE;
3718 BEGIN
3719 PERFORM "require_transaction_isolation"();
3720 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
3721 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
3722 -- override protection triggers:
3723 INSERT INTO "temporary_transaction_data" ("key", "value")
3724 VALUES ('override_protection_triggers', TRUE::TEXT);
3725 -- delete timestamp of voting comment:
3726 UPDATE "direct_voter" SET "comment_changed" = NULL
3727 WHERE "issue_id" = "issue_id_p";
3728 -- delete delegating votes (in cases of manual reset of issue state):
3729 DELETE FROM "delegating_voter"
3730 WHERE "issue_id" = "issue_id_p";
3731 -- delete votes from non-privileged voters:
3732 DELETE FROM "direct_voter"
3733 USING (
3734 SELECT
3735 "direct_voter"."member_id"
3736 FROM "direct_voter"
3737 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
3738 LEFT JOIN "privilege"
3739 ON "privilege"."unit_id" = "unit_id_v"
3740 AND "privilege"."member_id" = "direct_voter"."member_id"
3741 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
3742 "member"."active" = FALSE OR
3743 "privilege"."voting_right" ISNULL OR
3744 "privilege"."voting_right" = FALSE
3745 )
3746 ) AS "subquery"
3747 WHERE "direct_voter"."issue_id" = "issue_id_p"
3748 AND "direct_voter"."member_id" = "subquery"."member_id";
3749 -- consider delegations:
3750 UPDATE "direct_voter" SET "weight" = 1
3751 WHERE "issue_id" = "issue_id_p";
3752 PERFORM "add_vote_delegations"("issue_id_p");
3753 -- mark first preferences:
3754 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
3755 FROM (
3756 SELECT
3757 "vote"."initiative_id",
3758 "vote"."member_id",
3759 CASE WHEN "vote"."grade" > 0 THEN
3760 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
3761 ELSE NULL
3762 END AS "first_preference"
3763 FROM "vote"
3764 JOIN "initiative" -- NOTE: due to missing index on issue_id
3765 ON "vote"."issue_id" = "initiative"."issue_id"
3766 JOIN "vote" AS "agg"
3767 ON "initiative"."id" = "agg"."initiative_id"
3768 AND "vote"."member_id" = "agg"."member_id"
3769 GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
3770 ) AS "subquery"
3771 WHERE "vote"."issue_id" = "issue_id_p"
3772 AND "vote"."initiative_id" = "subquery"."initiative_id"
3773 AND "vote"."member_id" = "subquery"."member_id";
3774 -- finish overriding protection triggers (avoids garbage):
3775 DELETE FROM "temporary_transaction_data"
3776 WHERE "key" = 'override_protection_triggers';
3777 -- materialize battle_view:
3778 -- NOTE: "closed" column of issue must be set at this point
3779 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3780 INSERT INTO "battle" (
3781 "issue_id",
3782 "winning_initiative_id", "losing_initiative_id",
3783 "count"
3784 ) SELECT
3785 "issue_id",
3786 "winning_initiative_id", "losing_initiative_id",
3787 "count"
3788 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
3789 -- set voter count:
3790 UPDATE "issue" SET
3791 "voter_count" = (
3792 SELECT coalesce(sum("weight"), 0)
3793 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
3794 )
3795 WHERE "id" = "issue_id_p";
3796 -- copy "positive_votes" and "negative_votes" from "battle" table:
3797 -- NOTE: "first_preference_votes" is set to a default of 0 at this step
3798 UPDATE "initiative" SET
3799 "first_preference_votes" = 0,
3800 "positive_votes" = "battle_win"."count",
3801 "negative_votes" = "battle_lose"."count"
3802 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
3803 WHERE
3804 "battle_win"."issue_id" = "issue_id_p" AND
3805 "battle_win"."winning_initiative_id" = "initiative"."id" AND
3806 "battle_win"."losing_initiative_id" ISNULL AND
3807 "battle_lose"."issue_id" = "issue_id_p" AND
3808 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
3809 "battle_lose"."winning_initiative_id" ISNULL;
3810 -- calculate "first_preference_votes":
3811 -- NOTE: will only set values not equal to zero
3812 UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum"
3813 FROM (
3814 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
3815 FROM "vote" JOIN "direct_voter"
3816 ON "vote"."issue_id" = "direct_voter"."issue_id"
3817 AND "vote"."member_id" = "direct_voter"."member_id"
3818 WHERE "vote"."first_preference"
3819 GROUP BY "vote"."initiative_id"
3820 ) AS "subquery"
3821 WHERE "initiative"."issue_id" = "issue_id_p"
3822 AND "initiative"."admitted"
3823 AND "initiative"."id" = "subquery"."initiative_id";
3824 END;
3825 $$;
3827 COMMENT ON FUNCTION "close_voting"
3828 ( "issue"."id"%TYPE )
3829 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.';
3832 CREATE FUNCTION "defeat_strength"
3833 ( "positive_votes_p" INT4,
3834 "negative_votes_p" INT4,
3835 "defeat_strength_p" "defeat_strength" )
3836 RETURNS INT8
3837 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3838 BEGIN
3839 IF "defeat_strength_p" = 'simple'::"defeat_strength" THEN
3840 IF "positive_votes_p" > "negative_votes_p" THEN
3841 RETURN "positive_votes_p";
3842 ELSE
3843 RETURN 0;
3844 END IF;
3845 ELSE
3846 IF "positive_votes_p" > "negative_votes_p" THEN
3847 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
3848 ELSIF "positive_votes_p" = "negative_votes_p" THEN
3849 RETURN 0;
3850 ELSE
3851 RETURN -1;
3852 END IF;
3853 END IF;
3854 END;
3855 $$;
3857 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")';
3860 CREATE FUNCTION "secondary_link_strength"
3861 ( "initiative1_ord_p" INT4,
3862 "initiative2_ord_p" INT4,
3863 "tie_breaking_p" "tie_breaking" )
3864 RETURNS INT8
3865 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3866 BEGIN
3867 IF "initiative1_ord_p" = "initiative2_ord_p" THEN
3868 RAISE EXCEPTION 'Identical initiative ids passed to "secondary_link_strength" function (should not happen)';
3869 END IF;
3870 RETURN (
3871 CASE WHEN "tie_breaking_p" = 'simple'::"tie_breaking" THEN
3872 0
3873 ELSE
3874 CASE WHEN "initiative1_ord_p" < "initiative2_ord_p" THEN
3875 1::INT8 << 62
3876 ELSE 0 END
3877 +
3878 CASE WHEN "tie_breaking_p" = 'variant2'::"tie_breaking" THEN
3879 ("initiative2_ord_p"::INT8 << 31) - "initiative1_ord_p"::INT8
3880 ELSE
3881 "initiative2_ord_p"::INT8 - ("initiative1_ord_p"::INT8 << 31)
3882 END
3883 END
3884 );
3885 END;
3886 $$;
3888 COMMENT ON FUNCTION "secondary_link_strength"(INT4, INT4, "tie_breaking") IS 'Calculates a secondary criterion for the defeat strength (tie-breaking of the links)';
3891 CREATE TYPE "link_strength" AS (
3892 "primary" INT8,
3893 "secondary" INT8 );
3895 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'')';
3898 CREATE FUNCTION "find_best_paths"("matrix_d" "link_strength"[][])
3899 RETURNS "link_strength"[][]
3900 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3901 DECLARE
3902 "dimension_v" INT4;
3903 "matrix_p" "link_strength"[][];
3904 "i" INT4;
3905 "j" INT4;
3906 "k" INT4;
3907 BEGIN
3908 "dimension_v" := array_upper("matrix_d", 1);
3909 "matrix_p" := "matrix_d";
3910 "i" := 1;
3911 LOOP
3912 "j" := 1;
3913 LOOP
3914 IF "i" != "j" THEN
3915 "k" := 1;
3916 LOOP
3917 IF "i" != "k" AND "j" != "k" THEN
3918 IF "matrix_p"["j"]["i"] < "matrix_p"["i"]["k"] THEN
3919 IF "matrix_p"["j"]["i"] > "matrix_p"["j"]["k"] THEN
3920 "matrix_p"["j"]["k"] := "matrix_p"["j"]["i"];
3921 END IF;
3922 ELSE
3923 IF "matrix_p"["i"]["k"] > "matrix_p"["j"]["k"] THEN
3924 "matrix_p"["j"]["k"] := "matrix_p"["i"]["k"];
3925 END IF;
3926 END IF;
3927 END IF;
3928 EXIT WHEN "k" = "dimension_v";
3929 "k" := "k" + 1;
3930 END LOOP;
3931 END IF;
3932 EXIT WHEN "j" = "dimension_v";
3933 "j" := "j" + 1;
3934 END LOOP;
3935 EXIT WHEN "i" = "dimension_v";
3936 "i" := "i" + 1;
3937 END LOOP;
3938 RETURN "matrix_p";
3939 END;
3940 $$;
3942 COMMENT ON FUNCTION "find_best_paths"("link_strength"[][]) IS 'Computes the strengths of the best beat-paths from a square matrix';
3945 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
3946 RETURNS VOID
3947 LANGUAGE 'plpgsql' VOLATILE AS $$
3948 DECLARE
3949 "issue_row" "issue"%ROWTYPE;
3950 "policy_row" "policy"%ROWTYPE;
3951 "dimension_v" INT4;
3952 "matrix_a" INT4[][]; -- absolute votes
3953 "matrix_d" "link_strength"[][]; -- defeat strength (direct)
3954 "matrix_p" "link_strength"[][]; -- defeat strength (best path)
3955 "matrix_t" "link_strength"[][]; -- defeat strength (tie-breaking)
3956 "matrix_f" BOOLEAN[][]; -- forbidden link (tie-breaking)
3957 "matrix_b" BOOLEAN[][]; -- final order (who beats who)
3958 "i" INT4;
3959 "j" INT4;
3960 "m" INT4;
3961 "n" INT4;
3962 "battle_row" "battle"%ROWTYPE;
3963 "rank_ary" INT4[];
3964 "rank_v" INT4;
3965 "initiative_id_v" "initiative"."id"%TYPE;
3966 BEGIN
3967 PERFORM "require_transaction_isolation"();
3968 SELECT * INTO "issue_row"
3969 FROM "issue" WHERE "id" = "issue_id_p";
3970 SELECT * INTO "policy_row"
3971 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3972 SELECT count(1) INTO "dimension_v"
3973 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
3974 -- create "matrix_a" with absolute number of votes in pairwise
3975 -- comparison:
3976 "matrix_a" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
3977 "i" := 1;
3978 "j" := 2;
3979 FOR "battle_row" IN
3980 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
3981 ORDER BY
3982 "winning_initiative_id" NULLS FIRST,
3983 "losing_initiative_id" NULLS FIRST
3984 LOOP
3985 "matrix_a"["i"]["j"] := "battle_row"."count";
3986 IF "j" = "dimension_v" THEN
3987 "i" := "i" + 1;
3988 "j" := 1;
3989 ELSE
3990 "j" := "j" + 1;
3991 IF "j" = "i" THEN
3992 "j" := "j" + 1;
3993 END IF;
3994 END IF;
3995 END LOOP;
3996 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
3997 RAISE EXCEPTION 'Wrong battle count (should not happen)';
3998 END IF;
3999 -- store direct defeat strengths in "matrix_d" using "defeat_strength"
4000 -- and "secondary_link_strength" functions:
4001 "matrix_d" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
4002 "i" := 1;
4003 LOOP
4004 "j" := 1;
4005 LOOP
4006 IF "i" != "j" THEN
4007 "matrix_d"["i"]["j"] := (
4008 "defeat_strength"(
4009 "matrix_a"["i"]["j"],
4010 "matrix_a"["j"]["i"],
4011 "policy_row"."defeat_strength"
4012 ),
4013 "secondary_link_strength"(
4014 "i",
4015 "j",
4016 "policy_row"."tie_breaking"
4017 )
4018 )::"link_strength";
4019 END IF;
4020 EXIT WHEN "j" = "dimension_v";
4021 "j" := "j" + 1;
4022 END LOOP;
4023 EXIT WHEN "i" = "dimension_v";
4024 "i" := "i" + 1;
4025 END LOOP;
4026 -- find best paths:
4027 "matrix_p" := "find_best_paths"("matrix_d");
4028 -- create partial order:
4029 "matrix_b" := array_fill(NULL::BOOLEAN, ARRAY["dimension_v", "dimension_v"]);
4030 "i" := 1;
4031 LOOP
4032 "j" := "i" + 1;
4033 LOOP
4034 IF "i" != "j" THEN
4035 IF "matrix_p"["i"]["j"] > "matrix_p"["j"]["i"] THEN
4036 "matrix_b"["i"]["j"] := TRUE;
4037 "matrix_b"["j"]["i"] := FALSE;
4038 ELSIF "matrix_p"["i"]["j"] < "matrix_p"["j"]["i"] THEN
4039 "matrix_b"["i"]["j"] := FALSE;
4040 "matrix_b"["j"]["i"] := TRUE;
4041 END IF;
4042 END IF;
4043 EXIT WHEN "j" = "dimension_v";
4044 "j" := "j" + 1;
4045 END LOOP;
4046 EXIT WHEN "i" = "dimension_v" - 1;
4047 "i" := "i" + 1;
4048 END LOOP;
4049 -- tie-breaking by forbidding shared weakest links in beat-paths
4050 -- (unless "tie_breaking" is set to 'simple', in which case tie-breaking
4051 -- is performed later by initiative id):
4052 IF "policy_row"."tie_breaking" != 'simple'::"tie_breaking" THEN
4053 "m" := 1;
4054 LOOP
4055 "n" := "m" + 1;
4056 LOOP
4057 -- only process those candidates m and n, which are tied:
4058 IF "matrix_b"["m"]["n"] ISNULL THEN
4059 -- start with beat-paths prior tie-breaking:
4060 "matrix_t" := "matrix_p";
4061 -- start with all links allowed:
4062 "matrix_f" := array_fill(FALSE, ARRAY["dimension_v", "dimension_v"]);
4063 LOOP
4064 -- determine (and forbid) that link that is the weakest link
4065 -- in both the best path from candidate m to candidate n and
4066 -- from candidate n to candidate m:
4067 "i" := 1;
4068 <<forbid_one_link>>
4069 LOOP
4070 "j" := 1;
4071 LOOP
4072 IF "i" != "j" THEN
4073 IF "matrix_d"["i"]["j"] = "matrix_t"["m"]["n"] THEN
4074 "matrix_f"["i"]["j"] := TRUE;
4075 -- exit for performance reasons,
4076 -- as exactly one link will be found:
4077 EXIT forbid_one_link;
4078 END IF;
4079 END IF;
4080 EXIT WHEN "j" = "dimension_v";
4081 "j" := "j" + 1;
4082 END LOOP;
4083 IF "i" = "dimension_v" THEN
4084 RAISE EXCEPTION 'Did not find shared weakest link for tie-breaking (should not happen)';
4085 END IF;
4086 "i" := "i" + 1;
4087 END LOOP;
4088 -- calculate best beat-paths while ignoring forbidden links:
4089 "i" := 1;
4090 LOOP
4091 "j" := 1;
4092 LOOP
4093 IF "i" != "j" THEN
4094 "matrix_t"["i"]["j"] := CASE
4095 WHEN "matrix_f"["i"]["j"]
4096 THEN ((-1::INT8) << 63, 0)::"link_strength" -- worst possible value
4097 ELSE "matrix_d"["i"]["j"] END;
4098 END IF;
4099 EXIT WHEN "j" = "dimension_v";
4100 "j" := "j" + 1;
4101 END LOOP;
4102 EXIT WHEN "i" = "dimension_v";
4103 "i" := "i" + 1;
4104 END LOOP;
4105 "matrix_t" := "find_best_paths"("matrix_t");
4106 -- extend partial order, if tie-breaking was successful:
4107 IF "matrix_t"["m"]["n"] > "matrix_t"["n"]["m"] THEN
4108 "matrix_b"["m"]["n"] := TRUE;
4109 "matrix_b"["n"]["m"] := FALSE;
4110 EXIT;
4111 ELSIF "matrix_t"["m"]["n"] < "matrix_t"["n"]["m"] THEN
4112 "matrix_b"["m"]["n"] := FALSE;
4113 "matrix_b"["n"]["m"] := TRUE;
4114 EXIT;
4115 END IF;
4116 END LOOP;
4117 END IF;
4118 EXIT WHEN "n" = "dimension_v";
4119 "n" := "n" + 1;
4120 END LOOP;
4121 EXIT WHEN "m" = "dimension_v" - 1;
4122 "m" := "m" + 1;
4123 END LOOP;
4124 END IF;
4125 -- store a unique ranking in "rank_ary":
4126 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
4127 "rank_v" := 1;
4128 LOOP
4129 "i" := 1;
4130 <<assign_next_rank>>
4131 LOOP
4132 IF "rank_ary"["i"] ISNULL THEN
4133 "j" := 1;
4134 LOOP
4135 IF
4136 "i" != "j" AND
4137 "rank_ary"["j"] ISNULL AND
4138 ( "matrix_b"["j"]["i"] OR
4139 -- tie-breaking by "id"
4140 ( "matrix_b"["j"]["i"] ISNULL AND
4141 "j" < "i" ) )
4142 THEN
4143 -- someone else is better
4144 EXIT;
4145 END IF;
4146 IF "j" = "dimension_v" THEN
4147 -- noone is better
4148 "rank_ary"["i"] := "rank_v";
4149 EXIT assign_next_rank;
4150 END IF;
4151 "j" := "j" + 1;
4152 END LOOP;
4153 END IF;
4154 "i" := "i" + 1;
4155 IF "i" > "dimension_v" THEN
4156 RAISE EXCEPTION 'Schulze ranking does not compute (should not happen)';
4157 END IF;
4158 END LOOP;
4159 EXIT WHEN "rank_v" = "dimension_v";
4160 "rank_v" := "rank_v" + 1;
4161 END LOOP;
4162 -- write preliminary results:
4163 "i" := 2; -- omit status quo with "i" = 1
4164 FOR "initiative_id_v" IN
4165 SELECT "id" FROM "initiative"
4166 WHERE "issue_id" = "issue_id_p" AND "admitted"
4167 ORDER BY "id"
4168 LOOP
4169 UPDATE "initiative" SET
4170 "direct_majority" =
4171 CASE WHEN "policy_row"."direct_majority_strict" THEN
4172 "positive_votes" * "policy_row"."direct_majority_den" >
4173 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
4174 ELSE
4175 "positive_votes" * "policy_row"."direct_majority_den" >=
4176 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
4177 END
4178 AND "positive_votes" >= "policy_row"."direct_majority_positive"
4179 AND "issue_row"."voter_count"-"negative_votes" >=
4180 "policy_row"."direct_majority_non_negative",
4181 "indirect_majority" =
4182 CASE WHEN "policy_row"."indirect_majority_strict" THEN
4183 "positive_votes" * "policy_row"."indirect_majority_den" >
4184 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
4185 ELSE
4186 "positive_votes" * "policy_row"."indirect_majority_den" >=
4187 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
4188 END
4189 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
4190 AND "issue_row"."voter_count"-"negative_votes" >=
4191 "policy_row"."indirect_majority_non_negative",
4192 "schulze_rank" = "rank_ary"["i"],
4193 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"[1],
4194 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"[1],
4195 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"[1],
4196 "reverse_beat_path" = CASE WHEN "policy_row"."defeat_strength" = 'simple'::"defeat_strength"
4197 THEN NULL
4198 ELSE "matrix_p"[1]["i"]."primary" >= 0 END,
4199 "eligible" = FALSE,
4200 "winner" = FALSE,
4201 "rank" = NULL -- NOTE: in cases of manual reset of issue state
4202 WHERE "id" = "initiative_id_v";
4203 "i" := "i" + 1;
4204 END LOOP;
4205 IF "i" != "dimension_v" + 1 THEN
4206 RAISE EXCEPTION 'Wrong winner count (should not happen)';
4207 END IF;
4208 -- take indirect majorities into account:
4209 LOOP
4210 UPDATE "initiative" SET "indirect_majority" = TRUE
4211 FROM (
4212 SELECT "new_initiative"."id" AS "initiative_id"
4213 FROM "initiative" "old_initiative"
4214 JOIN "initiative" "new_initiative"
4215 ON "new_initiative"."issue_id" = "issue_id_p"
4216 AND "new_initiative"."indirect_majority" = FALSE
4217 JOIN "battle" "battle_win"
4218 ON "battle_win"."issue_id" = "issue_id_p"
4219 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
4220 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
4221 JOIN "battle" "battle_lose"
4222 ON "battle_lose"."issue_id" = "issue_id_p"
4223 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
4224 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
4225 WHERE "old_initiative"."issue_id" = "issue_id_p"
4226 AND "old_initiative"."indirect_majority" = TRUE
4227 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
4228 "battle_win"."count" * "policy_row"."indirect_majority_den" >
4229 "policy_row"."indirect_majority_num" *
4230 ("battle_win"."count"+"battle_lose"."count")
4231 ELSE
4232 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
4233 "policy_row"."indirect_majority_num" *
4234 ("battle_win"."count"+"battle_lose"."count")
4235 END
4236 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
4237 AND "issue_row"."voter_count"-"battle_lose"."count" >=
4238 "policy_row"."indirect_majority_non_negative"
4239 ) AS "subquery"
4240 WHERE "id" = "subquery"."initiative_id";
4241 EXIT WHEN NOT FOUND;
4242 END LOOP;
4243 -- set "multistage_majority" for remaining matching initiatives:
4244 UPDATE "initiative" SET "multistage_majority" = TRUE
4245 FROM (
4246 SELECT "losing_initiative"."id" AS "initiative_id"
4247 FROM "initiative" "losing_initiative"
4248 JOIN "initiative" "winning_initiative"
4249 ON "winning_initiative"."issue_id" = "issue_id_p"
4250 AND "winning_initiative"."admitted"
4251 JOIN "battle" "battle_win"
4252 ON "battle_win"."issue_id" = "issue_id_p"
4253 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
4254 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
4255 JOIN "battle" "battle_lose"
4256 ON "battle_lose"."issue_id" = "issue_id_p"
4257 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
4258 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
4259 WHERE "losing_initiative"."issue_id" = "issue_id_p"
4260 AND "losing_initiative"."admitted"
4261 AND "winning_initiative"."schulze_rank" <
4262 "losing_initiative"."schulze_rank"
4263 AND "battle_win"."count" > "battle_lose"."count"
4264 AND (
4265 "battle_win"."count" > "winning_initiative"."positive_votes" OR
4266 "battle_lose"."count" < "losing_initiative"."negative_votes" )
4267 ) AS "subquery"
4268 WHERE "id" = "subquery"."initiative_id";
4269 -- mark eligible initiatives:
4270 UPDATE "initiative" SET "eligible" = TRUE
4271 WHERE "issue_id" = "issue_id_p"
4272 AND "initiative"."direct_majority"
4273 AND "initiative"."indirect_majority"
4274 AND "initiative"."better_than_status_quo"
4275 AND (
4276 "policy_row"."no_multistage_majority" = FALSE OR
4277 "initiative"."multistage_majority" = FALSE )
4278 AND (
4279 "policy_row"."no_reverse_beat_path" = FALSE OR
4280 coalesce("initiative"."reverse_beat_path", FALSE) = FALSE );
4281 -- mark final winner:
4282 UPDATE "initiative" SET "winner" = TRUE
4283 FROM (
4284 SELECT "id" AS "initiative_id"
4285 FROM "initiative"
4286 WHERE "issue_id" = "issue_id_p" AND "eligible"
4287 ORDER BY
4288 "schulze_rank",
4289 "id"
4290 LIMIT 1
4291 ) AS "subquery"
4292 WHERE "id" = "subquery"."initiative_id";
4293 -- write (final) ranks:
4294 "rank_v" := 1;
4295 FOR "initiative_id_v" IN
4296 SELECT "id"
4297 FROM "initiative"
4298 WHERE "issue_id" = "issue_id_p" AND "admitted"
4299 ORDER BY
4300 "winner" DESC,
4301 "eligible" DESC,
4302 "schulze_rank",
4303 "id"
4304 LOOP
4305 UPDATE "initiative" SET "rank" = "rank_v"
4306 WHERE "id" = "initiative_id_v";
4307 "rank_v" := "rank_v" + 1;
4308 END LOOP;
4309 -- set schulze rank of status quo and mark issue as finished:
4310 UPDATE "issue" SET
4311 "status_quo_schulze_rank" = "rank_ary"[1],
4312 "state" =
4313 CASE WHEN EXISTS (
4314 SELECT NULL FROM "initiative"
4315 WHERE "issue_id" = "issue_id_p" AND "winner"
4316 ) THEN
4317 'finished_with_winner'::"issue_state"
4318 ELSE
4319 'finished_without_winner'::"issue_state"
4320 END,
4321 "closed" = "phase_finished",
4322 "phase_finished" = NULL
4323 WHERE "id" = "issue_id_p";
4324 RETURN;
4325 END;
4326 $$;
4328 COMMENT ON FUNCTION "calculate_ranks"
4329 ( "issue"."id"%TYPE )
4330 IS 'Determine ranking (Votes have to be counted first)';
4334 -----------------------------
4335 -- Automatic state changes --
4336 -----------------------------
4339 CREATE TYPE "check_issue_persistence" AS (
4340 "state" "issue_state",
4341 "phase_finished" BOOLEAN,
4342 "issue_revoked" BOOLEAN,
4343 "snapshot_created" BOOLEAN,
4344 "harmonic_weights_set" BOOLEAN,
4345 "closed_voting" BOOLEAN );
4347 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';
4350 CREATE FUNCTION "check_issue"
4351 ( "issue_id_p" "issue"."id"%TYPE,
4352 "persist" "check_issue_persistence" )
4353 RETURNS "check_issue_persistence"
4354 LANGUAGE 'plpgsql' VOLATILE AS $$
4355 DECLARE
4356 "issue_row" "issue"%ROWTYPE;
4357 "policy_row" "policy"%ROWTYPE;
4358 "initiative_row" "initiative"%ROWTYPE;
4359 "state_v" "issue_state";
4360 BEGIN
4361 PERFORM "require_transaction_isolation"();
4362 IF "persist" ISNULL THEN
4363 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
4364 FOR UPDATE;
4365 IF "issue_row"."closed" NOTNULL THEN
4366 RETURN NULL;
4367 END IF;
4368 "persist"."state" := "issue_row"."state";
4369 IF
4370 ( "issue_row"."state" = 'admission' AND now() >=
4371 "issue_row"."created" + "issue_row"."max_admission_time" ) OR
4372 ( "issue_row"."state" = 'discussion' AND now() >=
4373 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
4374 ( "issue_row"."state" = 'verification' AND now() >=
4375 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
4376 ( "issue_row"."state" = 'voting' AND now() >=
4377 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
4378 THEN
4379 "persist"."phase_finished" := TRUE;
4380 ELSE
4381 "persist"."phase_finished" := FALSE;
4382 END IF;
4383 IF
4384 NOT EXISTS (
4385 -- all initiatives are revoked
4386 SELECT NULL FROM "initiative"
4387 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
4388 ) AND (
4389 -- and issue has not been accepted yet
4390 "persist"."state" = 'admission' OR
4391 -- or verification time has elapsed
4392 ( "persist"."state" = 'verification' AND
4393 "persist"."phase_finished" ) OR
4394 -- or no initiatives have been revoked lately
4395 NOT EXISTS (
4396 SELECT NULL FROM "initiative"
4397 WHERE "issue_id" = "issue_id_p"
4398 AND now() < "revoked" + "issue_row"."verification_time"
4399 )
4400 )
4401 THEN
4402 "persist"."issue_revoked" := TRUE;
4403 ELSE
4404 "persist"."issue_revoked" := FALSE;
4405 END IF;
4406 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
4407 UPDATE "issue" SET "phase_finished" = now()
4408 WHERE "id" = "issue_row"."id";
4409 RETURN "persist";
4410 ELSIF
4411 "persist"."state" IN ('admission', 'discussion', 'verification')
4412 THEN
4413 RETURN "persist";
4414 ELSE
4415 RETURN NULL;
4416 END IF;
4417 END IF;
4418 IF
4419 "persist"."state" IN ('admission', 'discussion', 'verification') AND
4420 coalesce("persist"."snapshot_created", FALSE) = FALSE
4421 THEN
4422 PERFORM "create_snapshot"("issue_id_p");
4423 "persist"."snapshot_created" = TRUE;
4424 IF "persist"."phase_finished" THEN
4425 IF "persist"."state" = 'admission' THEN
4426 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
4427 ELSIF "persist"."state" = 'discussion' THEN
4428 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
4429 ELSIF "persist"."state" = 'verification' THEN
4430 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
4431 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4432 SELECT * INTO "policy_row" FROM "policy"
4433 WHERE "id" = "issue_row"."policy_id";
4434 FOR "initiative_row" IN
4435 SELECT * FROM "initiative"
4436 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
4437 FOR UPDATE
4438 LOOP
4439 IF
4440 "initiative_row"."polling" OR (
4441 "initiative_row"."satisfied_supporter_count" > 0 AND
4442 "initiative_row"."satisfied_supporter_count" *
4443 "policy_row"."initiative_quorum_den" >=
4444 "issue_row"."population" * "policy_row"."initiative_quorum_num"
4445 )
4446 THEN
4447 UPDATE "initiative" SET "admitted" = TRUE
4448 WHERE "id" = "initiative_row"."id";
4449 ELSE
4450 UPDATE "initiative" SET "admitted" = FALSE
4451 WHERE "id" = "initiative_row"."id";
4452 END IF;
4453 END LOOP;
4454 END IF;
4455 END IF;
4456 RETURN "persist";
4457 END IF;
4458 IF
4459 "persist"."state" IN ('admission', 'discussion', 'verification') AND
4460 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
4461 THEN
4462 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
4463 "persist"."harmonic_weights_set" = TRUE;
4464 IF
4465 "persist"."phase_finished" OR
4466 "persist"."issue_revoked" OR
4467 "persist"."state" = 'admission'
4468 THEN
4469 RETURN "persist";
4470 ELSE
4471 RETURN NULL;
4472 END IF;
4473 END IF;
4474 IF "persist"."issue_revoked" THEN
4475 IF "persist"."state" = 'admission' THEN
4476 "state_v" := 'canceled_revoked_before_accepted';
4477 ELSIF "persist"."state" = 'discussion' THEN
4478 "state_v" := 'canceled_after_revocation_during_discussion';
4479 ELSIF "persist"."state" = 'verification' THEN
4480 "state_v" := 'canceled_after_revocation_during_verification';
4481 END IF;
4482 UPDATE "issue" SET
4483 "state" = "state_v",
4484 "closed" = "phase_finished",
4485 "phase_finished" = NULL
4486 WHERE "id" = "issue_id_p";
4487 RETURN NULL;
4488 END IF;
4489 IF "persist"."state" = 'admission' THEN
4490 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
4491 FOR UPDATE;
4492 SELECT * INTO "policy_row"
4493 FROM "policy" WHERE "id" = "issue_row"."policy_id";
4494 IF
4495 ( now() >=
4496 "issue_row"."created" + "issue_row"."min_admission_time" ) AND
4497 EXISTS (
4498 SELECT NULL FROM "initiative"
4499 WHERE "issue_id" = "issue_id_p"
4500 AND "supporter_count" > 0
4501 AND "supporter_count" * "policy_row"."issue_quorum_den"
4502 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
4503 )
4504 THEN
4505 UPDATE "issue" SET
4506 "state" = 'discussion',
4507 "accepted" = coalesce("phase_finished", now()),
4508 "phase_finished" = NULL
4509 WHERE "id" = "issue_id_p";
4510 ELSIF "issue_row"."phase_finished" NOTNULL THEN
4511 UPDATE "issue" SET
4512 "state" = 'canceled_issue_not_accepted',
4513 "closed" = "phase_finished",
4514 "phase_finished" = NULL
4515 WHERE "id" = "issue_id_p";
4516 END IF;
4517 RETURN NULL;
4518 END IF;
4519 IF "persist"."phase_finished" THEN
4520 IF "persist"."state" = 'discussion' THEN
4521 UPDATE "issue" SET
4522 "state" = 'verification',
4523 "half_frozen" = "phase_finished",
4524 "phase_finished" = NULL
4525 WHERE "id" = "issue_id_p";
4526 RETURN NULL;
4527 END IF;
4528 IF "persist"."state" = 'verification' THEN
4529 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
4530 FOR UPDATE;
4531 SELECT * INTO "policy_row" FROM "policy"
4532 WHERE "id" = "issue_row"."policy_id";
4533 IF EXISTS (
4534 SELECT NULL FROM "initiative"
4535 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
4536 ) THEN
4537 UPDATE "issue" SET
4538 "state" = 'voting',
4539 "fully_frozen" = "phase_finished",
4540 "phase_finished" = NULL
4541 WHERE "id" = "issue_id_p";
4542 ELSE
4543 UPDATE "issue" SET
4544 "state" = 'canceled_no_initiative_admitted',
4545 "fully_frozen" = "phase_finished",
4546 "closed" = "phase_finished",
4547 "phase_finished" = NULL
4548 WHERE "id" = "issue_id_p";
4549 -- NOTE: The following DELETE statements have effect only when
4550 -- issue state has been manipulated
4551 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
4552 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
4553 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
4554 END IF;
4555 RETURN NULL;
4556 END IF;
4557 IF "persist"."state" = 'voting' THEN
4558 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
4559 PERFORM "close_voting"("issue_id_p");
4560 "persist"."closed_voting" = TRUE;
4561 RETURN "persist";
4562 END IF;
4563 PERFORM "calculate_ranks"("issue_id_p");
4564 RETURN NULL;
4565 END IF;
4566 END IF;
4567 RAISE WARNING 'should not happen';
4568 RETURN NULL;
4569 END;
4570 $$;
4572 COMMENT ON FUNCTION "check_issue"
4573 ( "issue"."id"%TYPE,
4574 "check_issue_persistence" )
4575 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")';
4578 CREATE FUNCTION "check_everything"()
4579 RETURNS VOID
4580 LANGUAGE 'plpgsql' VOLATILE AS $$
4581 DECLARE
4582 "issue_id_v" "issue"."id"%TYPE;
4583 "persist_v" "check_issue_persistence";
4584 BEGIN
4585 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
4586 DELETE FROM "expired_session";
4587 PERFORM "check_activity"();
4588 PERFORM "calculate_member_counts"();
4589 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
4590 "persist_v" := NULL;
4591 LOOP
4592 "persist_v" := "check_issue"("issue_id_v", "persist_v");
4593 EXIT WHEN "persist_v" ISNULL;
4594 END LOOP;
4595 END LOOP;
4596 RETURN;
4597 END;
4598 $$;
4600 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.';
4604 ----------------------
4605 -- Deletion of data --
4606 ----------------------
4609 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
4610 RETURNS VOID
4611 LANGUAGE 'plpgsql' VOLATILE AS $$
4612 BEGIN
4613 IF EXISTS (
4614 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
4615 ) THEN
4616 -- override protection triggers:
4617 INSERT INTO "temporary_transaction_data" ("key", "value")
4618 VALUES ('override_protection_triggers', TRUE::TEXT);
4619 -- clean data:
4620 DELETE FROM "delegating_voter"
4621 WHERE "issue_id" = "issue_id_p";
4622 DELETE FROM "direct_voter"
4623 WHERE "issue_id" = "issue_id_p";
4624 DELETE FROM "delegating_interest_snapshot"
4625 WHERE "issue_id" = "issue_id_p";
4626 DELETE FROM "direct_interest_snapshot"
4627 WHERE "issue_id" = "issue_id_p";
4628 DELETE FROM "delegating_population_snapshot"
4629 WHERE "issue_id" = "issue_id_p";
4630 DELETE FROM "direct_population_snapshot"
4631 WHERE "issue_id" = "issue_id_p";
4632 DELETE FROM "non_voter"
4633 WHERE "issue_id" = "issue_id_p";
4634 DELETE FROM "delegation"
4635 WHERE "issue_id" = "issue_id_p";
4636 DELETE FROM "supporter"
4637 USING "initiative" -- NOTE: due to missing index on issue_id
4638 WHERE "initiative"."issue_id" = "issue_id_p"
4639 AND "supporter"."initiative_id" = "initiative_id";
4640 -- mark issue as cleaned:
4641 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
4642 -- finish overriding protection triggers (avoids garbage):
4643 DELETE FROM "temporary_transaction_data"
4644 WHERE "key" = 'override_protection_triggers';
4645 END IF;
4646 RETURN;
4647 END;
4648 $$;
4650 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
4653 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
4654 RETURNS VOID
4655 LANGUAGE 'plpgsql' VOLATILE AS $$
4656 BEGIN
4657 UPDATE "member" SET
4658 "last_login" = NULL,
4659 "last_delegation_check" = NULL,
4660 "login" = NULL,
4661 "password" = NULL,
4662 "authority" = NULL,
4663 "authority_uid" = NULL,
4664 "authority_login" = NULL,
4665 "locked" = TRUE,
4666 "active" = FALSE,
4667 "notify_email" = NULL,
4668 "notify_email_unconfirmed" = NULL,
4669 "notify_email_secret" = NULL,
4670 "notify_email_secret_expiry" = NULL,
4671 "notify_email_lock_expiry" = NULL,
4672 "login_recovery_expiry" = NULL,
4673 "password_reset_secret" = NULL,
4674 "password_reset_secret_expiry" = NULL,
4675 "organizational_unit" = NULL,
4676 "internal_posts" = NULL,
4677 "realname" = NULL,
4678 "birthday" = NULL,
4679 "address" = NULL,
4680 "email" = NULL,
4681 "xmpp_address" = NULL,
4682 "website" = NULL,
4683 "phone" = NULL,
4684 "mobile_phone" = NULL,
4685 "profession" = NULL,
4686 "external_memberships" = NULL,
4687 "external_posts" = NULL,
4688 "statement" = NULL
4689 WHERE "id" = "member_id_p";
4690 -- "text_search_data" is updated by triggers
4691 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
4692 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
4693 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
4694 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
4695 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
4696 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
4697 DELETE FROM "session" WHERE "member_id" = "member_id_p";
4698 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
4699 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
4700 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
4701 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
4702 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
4703 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
4704 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
4705 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
4706 DELETE FROM "direct_voter" USING "issue"
4707 WHERE "direct_voter"."issue_id" = "issue"."id"
4708 AND "issue"."closed" ISNULL
4709 AND "member_id" = "member_id_p";
4710 RETURN;
4711 END;
4712 $$;
4714 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)';
4717 CREATE FUNCTION "delete_private_data"()
4718 RETURNS VOID
4719 LANGUAGE 'plpgsql' VOLATILE AS $$
4720 BEGIN
4721 DELETE FROM "temporary_transaction_data";
4722 DELETE FROM "member" WHERE "activated" ISNULL;
4723 UPDATE "member" SET
4724 "invite_code" = NULL,
4725 "invite_code_expiry" = NULL,
4726 "admin_comment" = NULL,
4727 "last_login" = NULL,
4728 "last_delegation_check" = NULL,
4729 "login" = NULL,
4730 "password" = NULL,
4731 "authority" = NULL,
4732 "authority_uid" = NULL,
4733 "authority_login" = NULL,
4734 "lang" = NULL,
4735 "notify_email" = NULL,
4736 "notify_email_unconfirmed" = NULL,
4737 "notify_email_secret" = NULL,
4738 "notify_email_secret_expiry" = NULL,
4739 "notify_email_lock_expiry" = NULL,
4740 "notify_level" = NULL,
4741 "login_recovery_expiry" = NULL,
4742 "password_reset_secret" = NULL,
4743 "password_reset_secret_expiry" = NULL,
4744 "organizational_unit" = NULL,
4745 "internal_posts" = NULL,
4746 "realname" = NULL,
4747 "birthday" = NULL,
4748 "address" = NULL,
4749 "email" = NULL,
4750 "xmpp_address" = NULL,
4751 "website" = NULL,
4752 "phone" = NULL,
4753 "mobile_phone" = NULL,
4754 "profession" = NULL,
4755 "external_memberships" = NULL,
4756 "external_posts" = NULL,
4757 "formatting_engine" = NULL,
4758 "statement" = NULL;
4759 -- "text_search_data" is updated by triggers
4760 DELETE FROM "setting";
4761 DELETE FROM "setting_map";
4762 DELETE FROM "member_relation_setting";
4763 DELETE FROM "member_image";
4764 DELETE FROM "contact";
4765 DELETE FROM "ignored_member";
4766 DELETE FROM "session";
4767 DELETE FROM "area_setting";
4768 DELETE FROM "issue_setting";
4769 DELETE FROM "ignored_initiative";
4770 DELETE FROM "initiative_setting";
4771 DELETE FROM "suggestion_setting";
4772 DELETE FROM "non_voter";
4773 DELETE FROM "direct_voter" USING "issue"
4774 WHERE "direct_voter"."issue_id" = "issue"."id"
4775 AND "issue"."closed" ISNULL;
4776 RETURN;
4777 END;
4778 $$;
4780 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.';
4784 COMMIT;