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