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