liquid_feedback_core
view core.sql @ 321:48a5036d5eb1
Require "satisfied" support for "admitted" initiatives when calculating "harmonic_weight"
| author | jbe |
|---|---|
| date | Fri Feb 08 18:36:22 2013 +0100 (2013-02-08) |
| parents | 3a7f06a88ecb |
| children | fd58f487e1d0 |
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 ('2.1.1', 2, 1, 1))
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 "system_setting" (
58 "member_ttl" INTERVAL );
59 CREATE UNIQUE INDEX "system_setting_singleton_idx" ON "system_setting" ((1));
61 COMMENT ON TABLE "system_setting" IS 'This table contains only one row with different settings in each column.';
62 COMMENT ON INDEX "system_setting_singleton_idx" IS 'This index ensures that "system_setting" only contains one row maximum.';
64 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.';
67 CREATE TABLE "contingent" (
68 PRIMARY KEY ("polling", "time_frame"),
69 "polling" BOOLEAN,
70 "time_frame" INTERVAL,
71 "text_entry_limit" INT4,
72 "initiative_limit" INT4 );
74 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.';
76 COMMENT ON COLUMN "contingent"."polling" IS 'Determines if settings are for creating initiatives and new drafts of initiatives with "polling" flag set';
77 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';
78 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
81 CREATE TYPE "notify_level" AS ENUM
82 ('none', 'voting', 'verification', 'discussion', 'all');
84 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';
87 CREATE TABLE "member" (
88 "id" SERIAL4 PRIMARY KEY,
89 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
90 "invite_code" TEXT UNIQUE,
91 "invite_code_expiry" TIMESTAMPTZ,
92 "admin_comment" TEXT,
93 "activated" TIMESTAMPTZ,
94 "last_activity" DATE,
95 "last_login" TIMESTAMPTZ,
96 "login" TEXT UNIQUE,
97 "password" TEXT,
98 "locked" BOOLEAN NOT NULL DEFAULT FALSE,
99 "active" BOOLEAN NOT NULL DEFAULT FALSE,
100 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
101 "lang" TEXT,
102 "notify_email" TEXT,
103 "notify_email_unconfirmed" TEXT,
104 "notify_email_secret" TEXT UNIQUE,
105 "notify_email_secret_expiry" TIMESTAMPTZ,
106 "notify_email_lock_expiry" TIMESTAMPTZ,
107 "notify_level" "notify_level",
108 "password_reset_secret" TEXT UNIQUE,
109 "password_reset_secret_expiry" TIMESTAMPTZ,
110 "name" TEXT UNIQUE,
111 "identification" TEXT UNIQUE,
112 "authentication" TEXT,
113 "organizational_unit" TEXT,
114 "internal_posts" TEXT,
115 "realname" TEXT,
116 "birthday" DATE,
117 "address" TEXT,
118 "email" TEXT,
119 "xmpp_address" TEXT,
120 "website" TEXT,
121 "phone" TEXT,
122 "mobile_phone" TEXT,
123 "profession" TEXT,
124 "external_memberships" TEXT,
125 "external_posts" TEXT,
126 "formatting_engine" TEXT,
127 "statement" TEXT,
128 "text_search_data" TSVECTOR,
129 CONSTRAINT "active_requires_activated_and_last_activity"
130 CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)),
131 CONSTRAINT "name_not_null_if_activated"
132 CHECK ("activated" ISNULL OR "name" NOTNULL) );
133 CREATE INDEX "member_active_idx" ON "member" ("active");
134 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
135 CREATE TRIGGER "update_text_search_data"
136 BEFORE INSERT OR UPDATE ON "member"
137 FOR EACH ROW EXECUTE PROCEDURE
138 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
139 "name", "identification", "organizational_unit", "internal_posts",
140 "realname", "external_memberships", "external_posts", "statement" );
142 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
144 COMMENT ON COLUMN "member"."created" IS 'Creation of member record and/or invite code';
145 COMMENT ON COLUMN "member"."invite_code" IS 'Optional invite code, to allow a member to initialize his/her account the first time';
146 COMMENT ON COLUMN "member"."invite_code_expiry" IS 'Expiry data/time for "invite_code"';
147 COMMENT ON COLUMN "member"."admin_comment" IS 'Hidden comment for administrative purposes';
148 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';
149 COMMENT ON COLUMN "member"."last_activity" IS 'Date of last activity of member; required to be set for "active" members';
150 COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login';
151 COMMENT ON COLUMN "member"."login" IS 'Login name';
152 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
153 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
154 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".';
155 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
156 COMMENT ON COLUMN "member"."lang" IS 'Language code of the preferred language of the member';
157 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
158 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
159 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
160 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
161 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
162 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';
163 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member, may be NULL if account has not been activated yet';
164 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
165 COMMENT ON COLUMN "member"."authentication" IS 'Information about how this member was authenticated';
166 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
167 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
168 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
169 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
170 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
171 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
172 COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"';
173 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his/her profile';
176 -- DEPRECATED API TABLES --
178 CREATE TYPE "application_access_level" AS ENUM
179 ('member', 'full', 'pseudonymous', 'anonymous');
181 COMMENT ON TYPE "application_access_level" IS 'DEPRECATED, WILL BE REMOVED! Access privileges for applications using the API';
184 CREATE TABLE "member_application" (
185 "id" SERIAL8 PRIMARY KEY,
186 UNIQUE ("member_id", "name"),
187 "member_id" INT4 NOT NULL REFERENCES "member" ("id")
188 ON DELETE CASCADE ON UPDATE CASCADE,
189 "name" TEXT NOT NULL,
190 "comment" TEXT,
191 "access_level" "application_access_level" NOT NULL,
192 "key" TEXT NOT NULL UNIQUE,
193 "last_usage" TIMESTAMPTZ );
195 COMMENT ON TABLE "member_application" IS 'DEPRECATED, WILL BE REMOVED! Registered application being allowed to use the API';
197 -- END OF DEPRECARED API TABLES --
200 CREATE TABLE "member_history" (
201 "id" SERIAL8 PRIMARY KEY,
202 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
203 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
204 "active" BOOLEAN NOT NULL,
205 "name" TEXT NOT NULL );
206 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
208 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
210 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
211 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
214 CREATE TABLE "rendered_member_statement" (
215 PRIMARY KEY ("member_id", "format"),
216 "member_id" INT8 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
217 "format" TEXT,
218 "content" TEXT NOT NULL );
220 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)';
223 CREATE TABLE "setting" (
224 PRIMARY KEY ("member_id", "key"),
225 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
226 "key" TEXT NOT NULL,
227 "value" TEXT NOT NULL );
228 CREATE INDEX "setting_key_idx" ON "setting" ("key");
230 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
232 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
235 CREATE TABLE "setting_map" (
236 PRIMARY KEY ("member_id", "key", "subkey"),
237 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
238 "key" TEXT NOT NULL,
239 "subkey" TEXT NOT NULL,
240 "value" TEXT NOT NULL );
241 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
243 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
245 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
246 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
247 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
250 CREATE TABLE "member_relation_setting" (
251 PRIMARY KEY ("member_id", "key", "other_member_id"),
252 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
253 "key" TEXT NOT NULL,
254 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
255 "value" TEXT NOT NULL );
257 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
260 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
262 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
265 CREATE TABLE "member_image" (
266 PRIMARY KEY ("member_id", "image_type", "scaled"),
267 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
268 "image_type" "member_image_type",
269 "scaled" BOOLEAN,
270 "content_type" TEXT,
271 "data" BYTEA NOT NULL );
273 COMMENT ON TABLE "member_image" IS 'Images of members';
275 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
278 CREATE TABLE "member_count" (
279 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
280 "total_count" INT4 NOT NULL );
282 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';
284 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
285 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
288 CREATE TABLE "contact" (
289 PRIMARY KEY ("member_id", "other_member_id"),
290 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
291 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
292 "public" BOOLEAN NOT NULL DEFAULT FALSE,
293 CONSTRAINT "cant_save_yourself_as_contact"
294 CHECK ("member_id" != "other_member_id") );
295 CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
297 COMMENT ON TABLE "contact" IS 'Contact lists';
299 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
300 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
301 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
304 CREATE TABLE "ignored_member" (
305 PRIMARY KEY ("member_id", "other_member_id"),
306 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
307 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
308 CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
310 COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
312 COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone';
313 COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
316 CREATE TABLE "session" (
317 "ident" TEXT PRIMARY KEY,
318 "additional_secret" TEXT,
319 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
320 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
321 "lang" TEXT );
322 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
324 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer';
326 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
327 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
328 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
329 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
332 CREATE TABLE "policy" (
333 "id" SERIAL4 PRIMARY KEY,
334 "index" INT4 NOT NULL,
335 "active" BOOLEAN NOT NULL DEFAULT TRUE,
336 "name" TEXT NOT NULL UNIQUE,
337 "description" TEXT NOT NULL DEFAULT '',
338 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
339 "admission_time" INTERVAL,
340 "discussion_time" INTERVAL,
341 "verification_time" INTERVAL,
342 "voting_time" INTERVAL,
343 "issue_quorum_num" INT4,
344 "issue_quorum_den" INT4,
345 "initiative_quorum_num" INT4 NOT NULL,
346 "initiative_quorum_den" INT4 NOT NULL,
347 "direct_majority_num" INT4 NOT NULL DEFAULT 1,
348 "direct_majority_den" INT4 NOT NULL DEFAULT 2,
349 "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
350 "direct_majority_positive" INT4 NOT NULL DEFAULT 0,
351 "direct_majority_non_negative" INT4 NOT NULL DEFAULT 0,
352 "indirect_majority_num" INT4 NOT NULL DEFAULT 1,
353 "indirect_majority_den" INT4 NOT NULL DEFAULT 2,
354 "indirect_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
355 "indirect_majority_positive" INT4 NOT NULL DEFAULT 0,
356 "indirect_majority_non_negative" INT4 NOT NULL DEFAULT 0,
357 "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT TRUE,
358 "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE,
359 CONSTRAINT "timing" CHECK (
360 ( "polling" = FALSE AND
361 "admission_time" NOTNULL AND "discussion_time" NOTNULL AND
362 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
363 ( "polling" = TRUE AND
364 "admission_time" ISNULL AND "discussion_time" NOTNULL AND
365 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
366 ( "polling" = TRUE AND
367 "admission_time" ISNULL AND "discussion_time" ISNULL AND
368 "verification_time" ISNULL AND "voting_time" ISNULL ) ),
369 CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK (
370 "polling" = "issue_quorum_num" ISNULL AND
371 "polling" = "issue_quorum_den" ISNULL ) );
372 CREATE INDEX "policy_active_idx" ON "policy" ("active");
374 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
376 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
377 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
378 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';
379 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"';
380 COMMENT ON COLUMN "policy"."discussion_time" IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"';
381 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"';
382 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'')';
383 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''';
384 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''';
385 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
386 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
387 COMMENT ON COLUMN "policy"."direct_majority_num" IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner';
388 COMMENT ON COLUMN "policy"."direct_majority_den" IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner';
389 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.';
390 COMMENT ON COLUMN "policy"."direct_majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner';
391 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';
392 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';
393 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';
394 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.';
395 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';
396 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';
397 COMMENT ON COLUMN "policy"."no_reverse_beat_path" IS '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.';
398 COMMENT ON COLUMN "policy"."no_multistage_majority" IS '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").';
401 CREATE TABLE "unit" (
402 "id" SERIAL4 PRIMARY KEY,
403 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
404 "active" BOOLEAN NOT NULL DEFAULT TRUE,
405 "name" TEXT NOT NULL,
406 "description" TEXT NOT NULL DEFAULT '',
407 "member_count" INT4,
408 "text_search_data" TSVECTOR );
409 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
410 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
411 CREATE INDEX "unit_active_idx" ON "unit" ("active");
412 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
413 CREATE TRIGGER "update_text_search_data"
414 BEFORE INSERT OR UPDATE ON "unit"
415 FOR EACH ROW EXECUTE PROCEDURE
416 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
417 "name", "description" );
419 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
421 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
422 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in areas of this unit';
423 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
426 CREATE TABLE "unit_setting" (
427 PRIMARY KEY ("member_id", "key", "unit_id"),
428 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
429 "key" TEXT NOT NULL,
430 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
431 "value" TEXT NOT NULL );
433 COMMENT ON TABLE "unit_setting" IS 'Place for frontend to store unit specific settings of members as strings';
436 CREATE TABLE "area" (
437 "id" SERIAL4 PRIMARY KEY,
438 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
439 "active" BOOLEAN NOT NULL DEFAULT TRUE,
440 "name" TEXT NOT NULL,
441 "description" TEXT NOT NULL DEFAULT '',
442 "direct_member_count" INT4,
443 "member_weight" INT4,
444 "text_search_data" TSVECTOR );
445 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
446 CREATE INDEX "area_active_idx" ON "area" ("active");
447 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
448 CREATE TRIGGER "update_text_search_data"
449 BEFORE INSERT OR UPDATE ON "area"
450 FOR EACH ROW EXECUTE PROCEDURE
451 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
452 "name", "description" );
454 COMMENT ON TABLE "area" IS 'Subject areas';
456 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
457 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"';
458 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
461 CREATE TABLE "area_setting" (
462 PRIMARY KEY ("member_id", "key", "area_id"),
463 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
464 "key" TEXT NOT NULL,
465 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
466 "value" TEXT NOT NULL );
468 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
471 CREATE TABLE "allowed_policy" (
472 PRIMARY KEY ("area_id", "policy_id"),
473 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
474 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
475 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
476 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
478 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
480 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
483 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
485 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';
488 CREATE TYPE "issue_state" AS ENUM (
489 'admission', 'discussion', 'verification', 'voting',
490 'canceled_revoked_before_accepted',
491 'canceled_issue_not_accepted',
492 'canceled_after_revocation_during_discussion',
493 'canceled_after_revocation_during_verification',
494 'calculation',
495 'canceled_no_initiative_admitted',
496 'finished_without_winner', 'finished_with_winner');
498 COMMENT ON TYPE "issue_state" IS 'State of issues';
501 CREATE TABLE "issue" (
502 "id" SERIAL4 PRIMARY KEY,
503 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
504 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
505 "state" "issue_state" NOT NULL DEFAULT 'admission',
506 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
507 "accepted" TIMESTAMPTZ,
508 "half_frozen" TIMESTAMPTZ,
509 "fully_frozen" TIMESTAMPTZ,
510 "closed" TIMESTAMPTZ,
511 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
512 "cleaned" TIMESTAMPTZ,
513 "admission_time" INTERVAL,
514 "discussion_time" INTERVAL NOT NULL,
515 "verification_time" INTERVAL NOT NULL,
516 "voting_time" INTERVAL NOT NULL,
517 "snapshot" TIMESTAMPTZ,
518 "latest_snapshot_event" "snapshot_event",
519 "population" INT4,
520 "voter_count" INT4,
521 "status_quo_schulze_rank" INT4,
522 CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK (
523 "admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created") ),
524 CONSTRAINT "valid_state" CHECK ((
525 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
526 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
527 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
528 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
529 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
530 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
531 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
532 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
533 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)) AND (
534 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
535 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
536 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
537 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
538 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
539 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
540 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
541 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
542 ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
543 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
544 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
545 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
546 )),
547 CONSTRAINT "state_change_order" CHECK (
548 "created" <= "accepted" AND
549 "accepted" <= "half_frozen" AND
550 "half_frozen" <= "fully_frozen" AND
551 "fully_frozen" <= "closed" ),
552 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
553 "cleaned" ISNULL OR "closed" NOTNULL ),
554 CONSTRAINT "last_snapshot_on_full_freeze"
555 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
556 CONSTRAINT "freeze_requires_snapshot"
557 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
558 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
559 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
560 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
561 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
562 CREATE INDEX "issue_created_idx" ON "issue" ("created");
563 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
564 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
565 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
566 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
567 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
568 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
570 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
572 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
573 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.';
574 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.';
575 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.';
576 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
577 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
578 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
579 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
580 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
581 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
582 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
583 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';
584 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
585 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';
586 COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
589 CREATE TABLE "issue_setting" (
590 PRIMARY KEY ("member_id", "key", "issue_id"),
591 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
592 "key" TEXT NOT NULL,
593 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
594 "value" TEXT NOT NULL );
596 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
599 CREATE TABLE "initiative" (
600 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
601 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
602 "id" SERIAL4 PRIMARY KEY,
603 "name" TEXT NOT NULL,
604 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
605 "discussion_url" TEXT,
606 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
607 "revoked" TIMESTAMPTZ,
608 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
609 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
610 "admitted" BOOLEAN,
611 "supporter_count" INT4,
612 "informed_supporter_count" INT4,
613 "satisfied_supporter_count" INT4,
614 "satisfied_informed_supporter_count" INT4,
615 "harmonic_weight" NUMERIC(12, 3),
616 "positive_votes" INT4,
617 "negative_votes" INT4,
618 "direct_majority" BOOLEAN,
619 "indirect_majority" BOOLEAN,
620 "schulze_rank" INT4,
621 "better_than_status_quo" BOOLEAN,
622 "worse_than_status_quo" BOOLEAN,
623 "reverse_beat_path" BOOLEAN,
624 "multistage_majority" BOOLEAN,
625 "eligible" BOOLEAN,
626 "winner" BOOLEAN,
627 "rank" INT4,
628 "text_search_data" TSVECTOR,
629 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
630 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL),
631 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
632 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
633 CONSTRAINT "revoked_initiatives_cant_be_admitted"
634 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
635 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
636 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
637 ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND
638 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
639 "schulze_rank" ISNULL AND
640 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
641 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
642 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ),
643 CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")),
644 CONSTRAINT "minimum_requirement_to_be_eligible" CHECK (
645 "eligible" = FALSE OR
646 ("direct_majority" AND "indirect_majority" AND "better_than_status_quo") ),
647 CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE),
648 CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1),
649 CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE),
650 CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") );
651 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
652 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
653 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
654 CREATE TRIGGER "update_text_search_data"
655 BEFORE INSERT OR UPDATE ON "initiative"
656 FOR EACH ROW EXECUTE PROCEDURE
657 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
658 "name", "discussion_url");
660 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.';
662 COMMENT ON COLUMN "initiative"."polling" IS 'Initiative does not need to pass the initiative quorum (see "policy"."polling")';
663 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
664 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
665 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoke the initiative';
666 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
667 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
668 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
669 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
670 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
671 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';
672 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
673 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
674 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"';
675 COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths';
676 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking without tie-breaking';
677 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)';
678 COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)';
679 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';
680 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';
681 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"';
682 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"';
683 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';
686 CREATE TABLE "battle" (
687 "issue_id" INT4 NOT NULL,
688 "winning_initiative_id" INT4,
689 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
690 "losing_initiative_id" INT4,
691 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
692 "count" INT4 NOT NULL,
693 CONSTRAINT "initiative_ids_not_equal" CHECK (
694 "winning_initiative_id" != "losing_initiative_id" OR
695 ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
696 ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) );
697 CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
698 CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
699 CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
701 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';
704 CREATE TABLE "ignored_initiative" (
705 PRIMARY KEY ("initiative_id", "member_id"),
706 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
707 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
708 CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");
710 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
713 CREATE TABLE "initiative_setting" (
714 PRIMARY KEY ("member_id", "key", "initiative_id"),
715 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
716 "key" TEXT NOT NULL,
717 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
718 "value" TEXT NOT NULL );
720 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
723 CREATE TABLE "draft" (
724 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
725 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
726 "id" SERIAL8 PRIMARY KEY,
727 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
728 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
729 "formatting_engine" TEXT,
730 "content" TEXT NOT NULL,
731 "text_search_data" TSVECTOR );
732 CREATE INDEX "draft_created_idx" ON "draft" ("created");
733 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
734 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
735 CREATE TRIGGER "update_text_search_data"
736 BEFORE INSERT OR UPDATE ON "draft"
737 FOR EACH ROW EXECUTE PROCEDURE
738 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
740 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.';
742 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
743 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
746 CREATE TABLE "rendered_draft" (
747 PRIMARY KEY ("draft_id", "format"),
748 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
749 "format" TEXT,
750 "content" TEXT NOT NULL );
752 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)';
755 CREATE TABLE "suggestion" (
756 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
757 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
758 "id" SERIAL8 PRIMARY KEY,
759 "draft_id" INT8 NOT NULL,
760 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
761 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
762 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
763 "name" TEXT NOT NULL,
764 "formatting_engine" TEXT,
765 "content" TEXT NOT NULL DEFAULT '',
766 "text_search_data" TSVECTOR,
767 "minus2_unfulfilled_count" INT4,
768 "minus2_fulfilled_count" INT4,
769 "minus1_unfulfilled_count" INT4,
770 "minus1_fulfilled_count" INT4,
771 "plus1_unfulfilled_count" INT4,
772 "plus1_fulfilled_count" INT4,
773 "plus2_unfulfilled_count" INT4,
774 "plus2_fulfilled_count" INT4,
775 "harmonic_weight" NUMERIC(12, 3) );
776 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
777 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
778 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
779 CREATE TRIGGER "update_text_search_data"
780 BEFORE INSERT OR UPDATE ON "suggestion"
781 FOR EACH ROW EXECUTE PROCEDURE
782 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
783 "name", "content");
785 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';
787 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")';
788 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
789 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
790 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
791 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
792 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
793 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
794 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
795 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
796 COMMENT ON COLUMN "suggestion"."harmonic_weight" IS 'Indicates the relevancy of the suggestion, calculated from the supporters (positive "degree") of the suggestion weighted with the harmonic series to avoid a large number of clones affecting other suggestion''s sorting positions too much';
799 CREATE TABLE "rendered_suggestion" (
800 PRIMARY KEY ("suggestion_id", "format"),
801 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
802 "format" TEXT,
803 "content" TEXT NOT NULL );
805 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)';
808 CREATE TABLE "suggestion_setting" (
809 PRIMARY KEY ("member_id", "key", "suggestion_id"),
810 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
811 "key" TEXT NOT NULL,
812 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
813 "value" TEXT NOT NULL );
815 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
818 CREATE TABLE "privilege" (
819 PRIMARY KEY ("unit_id", "member_id"),
820 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
821 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
822 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
823 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
824 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
825 "member_manager" BOOLEAN NOT NULL DEFAULT FALSE,
826 "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE,
827 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE,
828 "polling_right" BOOLEAN NOT NULL DEFAULT FALSE );
830 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
832 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke any privileges to/from other members';
833 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create and disable sub units';
834 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create and disable areas and set area parameters';
835 COMMENT ON COLUMN "privilege"."member_manager" IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"';
836 COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative';
837 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to support initiatives, create and rate suggestions, and to vote';
838 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';
841 CREATE TABLE "membership" (
842 PRIMARY KEY ("area_id", "member_id"),
843 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
844 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
845 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
847 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
850 CREATE TABLE "interest" (
851 PRIMARY KEY ("issue_id", "member_id"),
852 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
853 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
854 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
856 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.';
859 CREATE TABLE "initiator" (
860 PRIMARY KEY ("initiative_id", "member_id"),
861 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
862 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
863 "accepted" BOOLEAN );
864 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
866 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.';
868 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.';
871 CREATE TABLE "supporter" (
872 "issue_id" INT4 NOT NULL,
873 PRIMARY KEY ("initiative_id", "member_id"),
874 "initiative_id" INT4,
875 "member_id" INT4,
876 "draft_id" INT8 NOT NULL,
877 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
878 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE );
879 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
881 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.';
883 COMMENT ON COLUMN "supporter"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
884 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")';
887 CREATE TABLE "opinion" (
888 "initiative_id" INT4 NOT NULL,
889 PRIMARY KEY ("suggestion_id", "member_id"),
890 "suggestion_id" INT8,
891 "member_id" INT4,
892 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
893 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
894 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
895 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
896 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
898 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.';
900 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
903 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
905 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
908 CREATE TABLE "delegation" (
909 "id" SERIAL8 PRIMARY KEY,
910 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
911 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
912 "scope" "delegation_scope" NOT NULL,
913 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
914 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
915 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
916 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
917 CONSTRAINT "no_unit_delegation_to_null"
918 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
919 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
920 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
921 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
922 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
923 UNIQUE ("unit_id", "truster_id"),
924 UNIQUE ("area_id", "truster_id"),
925 UNIQUE ("issue_id", "truster_id") );
926 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
927 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
929 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
931 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
932 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
933 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
936 CREATE TABLE "direct_population_snapshot" (
937 PRIMARY KEY ("issue_id", "event", "member_id"),
938 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
939 "event" "snapshot_event",
940 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
941 "weight" INT4 );
942 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
944 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
946 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
947 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
950 CREATE TABLE "delegating_population_snapshot" (
951 PRIMARY KEY ("issue_id", "event", "member_id"),
952 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
953 "event" "snapshot_event",
954 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
955 "weight" INT4,
956 "scope" "delegation_scope" NOT NULL,
957 "delegate_member_ids" INT4[] NOT NULL );
958 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
960 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
962 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
963 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
964 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
965 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"';
968 CREATE TABLE "direct_interest_snapshot" (
969 PRIMARY KEY ("issue_id", "event", "member_id"),
970 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
971 "event" "snapshot_event",
972 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
973 "weight" INT4 );
974 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
976 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
978 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
979 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
982 CREATE TABLE "delegating_interest_snapshot" (
983 PRIMARY KEY ("issue_id", "event", "member_id"),
984 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
985 "event" "snapshot_event",
986 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
987 "weight" INT4,
988 "scope" "delegation_scope" NOT NULL,
989 "delegate_member_ids" INT4[] NOT NULL );
990 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
992 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
994 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
995 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
996 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
997 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"';
1000 CREATE TABLE "direct_supporter_snapshot" (
1001 "issue_id" INT4 NOT NULL,
1002 PRIMARY KEY ("initiative_id", "event", "member_id"),
1003 "initiative_id" INT4,
1004 "event" "snapshot_event",
1005 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1006 "draft_id" INT8 NOT NULL,
1007 "informed" BOOLEAN NOT NULL,
1008 "satisfied" BOOLEAN NOT NULL,
1009 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1010 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
1011 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1012 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
1014 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
1016 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';
1017 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1018 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
1019 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
1022 CREATE TABLE "non_voter" (
1023 PRIMARY KEY ("issue_id", "member_id"),
1024 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1025 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
1026 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
1028 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
1031 CREATE TABLE "direct_voter" (
1032 PRIMARY KEY ("issue_id", "member_id"),
1033 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1034 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1035 "weight" INT4,
1036 "comment_changed" TIMESTAMPTZ,
1037 "formatting_engine" TEXT,
1038 "comment" TEXT,
1039 "text_search_data" TSVECTOR );
1040 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
1041 CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data");
1042 CREATE TRIGGER "update_text_search_data"
1043 BEFORE INSERT OR UPDATE ON "direct_voter"
1044 FOR EACH ROW EXECUTE PROCEDURE
1045 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment");
1047 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.';
1049 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
1050 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';
1051 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';
1052 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.';
1055 CREATE TABLE "rendered_voter_comment" (
1056 PRIMARY KEY ("issue_id", "member_id", "format"),
1057 FOREIGN KEY ("issue_id", "member_id")
1058 REFERENCES "direct_voter" ("issue_id", "member_id")
1059 ON DELETE CASCADE ON UPDATE CASCADE,
1060 "issue_id" INT4,
1061 "member_id" INT4,
1062 "format" TEXT,
1063 "content" TEXT NOT NULL );
1065 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)';
1068 CREATE TABLE "delegating_voter" (
1069 PRIMARY KEY ("issue_id", "member_id"),
1070 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1071 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1072 "weight" INT4,
1073 "scope" "delegation_scope" NOT NULL,
1074 "delegate_member_ids" INT4[] NOT NULL );
1075 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
1077 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
1079 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
1080 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
1081 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"';
1084 CREATE TABLE "vote" (
1085 "issue_id" INT4 NOT NULL,
1086 PRIMARY KEY ("initiative_id", "member_id"),
1087 "initiative_id" INT4,
1088 "member_id" INT4,
1089 "grade" INT4,
1090 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1091 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1092 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
1094 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.';
1096 COMMENT ON COLUMN "vote"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
1097 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.';
1100 CREATE TYPE "event_type" AS ENUM (
1101 'issue_state_changed',
1102 'initiative_created_in_new_issue',
1103 'initiative_created_in_existing_issue',
1104 'initiative_revoked',
1105 'new_draft_created',
1106 'suggestion_created');
1108 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
1111 CREATE TABLE "event" (
1112 "id" SERIAL8 PRIMARY KEY,
1113 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
1114 "event" "event_type" NOT NULL,
1115 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1116 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1117 "state" "issue_state" CHECK ("state" != 'calculation'),
1118 "initiative_id" INT4,
1119 "draft_id" INT8,
1120 "suggestion_id" INT8,
1121 FOREIGN KEY ("issue_id", "initiative_id")
1122 REFERENCES "initiative" ("issue_id", "id")
1123 ON DELETE CASCADE ON UPDATE CASCADE,
1124 FOREIGN KEY ("initiative_id", "draft_id")
1125 REFERENCES "draft" ("initiative_id", "id")
1126 ON DELETE CASCADE ON UPDATE CASCADE,
1127 FOREIGN KEY ("initiative_id", "suggestion_id")
1128 REFERENCES "suggestion" ("initiative_id", "id")
1129 ON DELETE CASCADE ON UPDATE CASCADE,
1130 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
1131 "event" != 'issue_state_changed' OR (
1132 "member_id" ISNULL AND
1133 "issue_id" NOTNULL AND
1134 "state" NOTNULL AND
1135 "initiative_id" ISNULL AND
1136 "draft_id" ISNULL AND
1137 "suggestion_id" ISNULL )),
1138 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
1139 "event" NOT IN (
1140 'initiative_created_in_new_issue',
1141 'initiative_created_in_existing_issue',
1142 'initiative_revoked',
1143 'new_draft_created'
1144 ) OR (
1145 "member_id" NOTNULL AND
1146 "issue_id" NOTNULL AND
1147 "state" NOTNULL AND
1148 "initiative_id" NOTNULL AND
1149 "draft_id" NOTNULL AND
1150 "suggestion_id" ISNULL )),
1151 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
1152 "event" != 'suggestion_created' OR (
1153 "member_id" NOTNULL AND
1154 "issue_id" NOTNULL AND
1155 "state" NOTNULL AND
1156 "initiative_id" NOTNULL AND
1157 "draft_id" ISNULL AND
1158 "suggestion_id" NOTNULL )) );
1159 CREATE INDEX "event_occurrence_idx" ON "event" ("occurrence");
1161 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
1163 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
1164 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';
1165 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';
1166 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';
1169 CREATE TABLE "notification_sent" (
1170 "event_id" INT8 NOT NULL );
1171 CREATE UNIQUE INDEX "notification_sent_singleton_idx" ON "notification_sent" ((1));
1173 COMMENT ON TABLE "notification_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out';
1174 COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.';
1178 ----------------------------------------------
1179 -- Writing of history entries and event log --
1180 ----------------------------------------------
1183 CREATE FUNCTION "write_member_history_trigger"()
1184 RETURNS TRIGGER
1185 LANGUAGE 'plpgsql' VOLATILE AS $$
1186 BEGIN
1187 IF
1188 ( NEW."active" != OLD."active" OR
1189 NEW."name" != OLD."name" ) AND
1190 OLD."activated" NOTNULL
1191 THEN
1192 INSERT INTO "member_history"
1193 ("member_id", "active", "name")
1194 VALUES (NEW."id", OLD."active", OLD."name");
1195 END IF;
1196 RETURN NULL;
1197 END;
1198 $$;
1200 CREATE TRIGGER "write_member_history"
1201 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1202 "write_member_history_trigger"();
1204 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
1205 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
1208 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
1209 RETURNS TRIGGER
1210 LANGUAGE 'plpgsql' VOLATILE AS $$
1211 BEGIN
1212 IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN
1213 INSERT INTO "event" ("event", "issue_id", "state")
1214 VALUES ('issue_state_changed', NEW."id", NEW."state");
1215 END IF;
1216 RETURN NULL;
1217 END;
1218 $$;
1220 CREATE TRIGGER "write_event_issue_state_changed"
1221 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
1222 "write_event_issue_state_changed_trigger"();
1224 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
1225 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
1228 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1229 RETURNS TRIGGER
1230 LANGUAGE 'plpgsql' VOLATILE AS $$
1231 DECLARE
1232 "initiative_row" "initiative"%ROWTYPE;
1233 "issue_row" "issue"%ROWTYPE;
1234 "event_v" "event_type";
1235 BEGIN
1236 SELECT * INTO "initiative_row" FROM "initiative"
1237 WHERE "id" = NEW."initiative_id";
1238 SELECT * INTO "issue_row" FROM "issue"
1239 WHERE "id" = "initiative_row"."issue_id";
1240 IF EXISTS (
1241 SELECT NULL FROM "draft"
1242 WHERE "initiative_id" = NEW."initiative_id"
1243 AND "id" != NEW."id"
1244 ) THEN
1245 "event_v" := 'new_draft_created';
1246 ELSE
1247 IF EXISTS (
1248 SELECT NULL FROM "initiative"
1249 WHERE "issue_id" = "initiative_row"."issue_id"
1250 AND "id" != "initiative_row"."id"
1251 ) THEN
1252 "event_v" := 'initiative_created_in_existing_issue';
1253 ELSE
1254 "event_v" := 'initiative_created_in_new_issue';
1255 END IF;
1256 END IF;
1257 INSERT INTO "event" (
1258 "event", "member_id",
1259 "issue_id", "state", "initiative_id", "draft_id"
1260 ) VALUES (
1261 "event_v",
1262 NEW."author_id",
1263 "initiative_row"."issue_id",
1264 "issue_row"."state",
1265 "initiative_row"."id",
1266 NEW."id" );
1267 RETURN NULL;
1268 END;
1269 $$;
1271 CREATE TRIGGER "write_event_initiative_or_draft_created"
1272 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
1273 "write_event_initiative_or_draft_created_trigger"();
1275 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
1276 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
1279 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
1280 RETURNS TRIGGER
1281 LANGUAGE 'plpgsql' VOLATILE AS $$
1282 DECLARE
1283 "issue_row" "issue"%ROWTYPE;
1284 "draft_id_v" "draft"."id"%TYPE;
1285 BEGIN
1286 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1287 SELECT * INTO "issue_row" FROM "issue"
1288 WHERE "id" = NEW."issue_id";
1289 SELECT "id" INTO "draft_id_v" FROM "current_draft"
1290 WHERE "initiative_id" = NEW."id";
1291 INSERT INTO "event" (
1292 "event", "member_id", "issue_id", "state", "initiative_id", "draft_id"
1293 ) VALUES (
1294 'initiative_revoked',
1295 NEW."revoked_by_member_id",
1296 NEW."issue_id",
1297 "issue_row"."state",
1298 NEW."id",
1299 "draft_id_v");
1300 END IF;
1301 RETURN NULL;
1302 END;
1303 $$;
1305 CREATE TRIGGER "write_event_initiative_revoked"
1306 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
1307 "write_event_initiative_revoked_trigger"();
1309 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
1310 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
1313 CREATE FUNCTION "write_event_suggestion_created_trigger"()
1314 RETURNS TRIGGER
1315 LANGUAGE 'plpgsql' VOLATILE AS $$
1316 DECLARE
1317 "initiative_row" "initiative"%ROWTYPE;
1318 "issue_row" "issue"%ROWTYPE;
1319 BEGIN
1320 SELECT * INTO "initiative_row" FROM "initiative"
1321 WHERE "id" = NEW."initiative_id";
1322 SELECT * INTO "issue_row" FROM "issue"
1323 WHERE "id" = "initiative_row"."issue_id";
1324 INSERT INTO "event" (
1325 "event", "member_id",
1326 "issue_id", "state", "initiative_id", "suggestion_id"
1327 ) VALUES (
1328 'suggestion_created',
1329 NEW."author_id",
1330 "initiative_row"."issue_id",
1331 "issue_row"."state",
1332 "initiative_row"."id",
1333 NEW."id" );
1334 RETURN NULL;
1335 END;
1336 $$;
1338 CREATE TRIGGER "write_event_suggestion_created"
1339 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1340 "write_event_suggestion_created_trigger"();
1342 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
1343 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1347 ----------------------------
1348 -- Additional constraints --
1349 ----------------------------
1352 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
1353 RETURNS TRIGGER
1354 LANGUAGE 'plpgsql' VOLATILE AS $$
1355 BEGIN
1356 IF NOT EXISTS (
1357 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
1358 ) THEN
1359 --RAISE 'Cannot create issue without an initial initiative.' USING
1360 -- ERRCODE = 'integrity_constraint_violation',
1361 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
1362 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
1363 END IF;
1364 RETURN NULL;
1365 END;
1366 $$;
1368 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
1369 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
1370 FOR EACH ROW EXECUTE PROCEDURE
1371 "issue_requires_first_initiative_trigger"();
1373 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
1374 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
1377 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
1378 RETURNS TRIGGER
1379 LANGUAGE 'plpgsql' VOLATILE AS $$
1380 DECLARE
1381 "reference_lost" BOOLEAN;
1382 BEGIN
1383 IF TG_OP = 'DELETE' THEN
1384 "reference_lost" := TRUE;
1385 ELSE
1386 "reference_lost" := NEW."issue_id" != OLD."issue_id";
1387 END IF;
1388 IF
1389 "reference_lost" AND NOT EXISTS (
1390 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
1391 )
1392 THEN
1393 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
1394 END IF;
1395 RETURN NULL;
1396 END;
1397 $$;
1399 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
1400 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1401 FOR EACH ROW EXECUTE PROCEDURE
1402 "last_initiative_deletes_issue_trigger"();
1404 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
1405 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
1408 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
1409 RETURNS TRIGGER
1410 LANGUAGE 'plpgsql' VOLATILE AS $$
1411 BEGIN
1412 IF NOT EXISTS (
1413 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
1414 ) THEN
1415 --RAISE 'Cannot create initiative without an initial draft.' USING
1416 -- ERRCODE = 'integrity_constraint_violation',
1417 -- HINT = 'Create issue, initiative and draft within the same transaction.';
1418 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
1419 END IF;
1420 RETURN NULL;
1421 END;
1422 $$;
1424 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
1425 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1426 FOR EACH ROW EXECUTE PROCEDURE
1427 "initiative_requires_first_draft_trigger"();
1429 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
1430 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
1433 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
1434 RETURNS TRIGGER
1435 LANGUAGE 'plpgsql' VOLATILE AS $$
1436 DECLARE
1437 "reference_lost" BOOLEAN;
1438 BEGIN
1439 IF TG_OP = 'DELETE' THEN
1440 "reference_lost" := TRUE;
1441 ELSE
1442 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
1443 END IF;
1444 IF
1445 "reference_lost" AND NOT EXISTS (
1446 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
1447 )
1448 THEN
1449 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
1450 END IF;
1451 RETURN NULL;
1452 END;
1453 $$;
1455 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
1456 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
1457 FOR EACH ROW EXECUTE PROCEDURE
1458 "last_draft_deletes_initiative_trigger"();
1460 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
1461 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
1464 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
1465 RETURNS TRIGGER
1466 LANGUAGE 'plpgsql' VOLATILE AS $$
1467 BEGIN
1468 IF NOT EXISTS (
1469 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
1470 ) THEN
1471 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
1472 END IF;
1473 RETURN NULL;
1474 END;
1475 $$;
1477 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
1478 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
1479 FOR EACH ROW EXECUTE PROCEDURE
1480 "suggestion_requires_first_opinion_trigger"();
1482 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1483 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1486 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1487 RETURNS TRIGGER
1488 LANGUAGE 'plpgsql' VOLATILE AS $$
1489 DECLARE
1490 "reference_lost" BOOLEAN;
1491 BEGIN
1492 IF TG_OP = 'DELETE' THEN
1493 "reference_lost" := TRUE;
1494 ELSE
1495 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1496 END IF;
1497 IF
1498 "reference_lost" AND NOT EXISTS (
1499 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1500 )
1501 THEN
1502 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1503 END IF;
1504 RETURN NULL;
1505 END;
1506 $$;
1508 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1509 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1510 FOR EACH ROW EXECUTE PROCEDURE
1511 "last_opinion_deletes_suggestion_trigger"();
1513 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1514 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1517 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()
1518 RETURNS TRIGGER
1519 LANGUAGE 'plpgsql' VOLATILE AS $$
1520 BEGIN
1521 DELETE FROM "direct_voter"
1522 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
1523 RETURN NULL;
1524 END;
1525 $$;
1527 CREATE TRIGGER "non_voter_deletes_direct_voter"
1528 AFTER INSERT OR UPDATE ON "non_voter"
1529 FOR EACH ROW EXECUTE PROCEDURE
1530 "non_voter_deletes_direct_voter_trigger"();
1532 COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"';
1533 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")';
1536 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()
1537 RETURNS TRIGGER
1538 LANGUAGE 'plpgsql' VOLATILE AS $$
1539 BEGIN
1540 DELETE FROM "non_voter"
1541 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
1542 RETURN NULL;
1543 END;
1544 $$;
1546 CREATE TRIGGER "direct_voter_deletes_non_voter"
1547 AFTER INSERT OR UPDATE ON "direct_voter"
1548 FOR EACH ROW EXECUTE PROCEDURE
1549 "direct_voter_deletes_non_voter_trigger"();
1551 COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"';
1552 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")';
1555 CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()
1556 RETURNS TRIGGER
1557 LANGUAGE 'plpgsql' VOLATILE AS $$
1558 BEGIN
1559 IF NEW."comment" ISNULL THEN
1560 NEW."comment_changed" := NULL;
1561 NEW."formatting_engine" := NULL;
1562 END IF;
1563 RETURN NEW;
1564 END;
1565 $$;
1567 CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"
1568 BEFORE INSERT OR UPDATE ON "direct_voter"
1569 FOR EACH ROW EXECUTE PROCEDURE
1570 "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();
1572 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"';
1573 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.';
1576 ---------------------------------------------------------------
1577 -- Ensure that votes are not modified when issues are frozen --
1578 ---------------------------------------------------------------
1580 -- NOTE: Frontends should ensure this anyway, but in case of programming
1581 -- errors the following triggers ensure data integrity.
1584 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1585 RETURNS TRIGGER
1586 LANGUAGE 'plpgsql' VOLATILE AS $$
1587 DECLARE
1588 "issue_id_v" "issue"."id"%TYPE;
1589 "issue_row" "issue"%ROWTYPE;
1590 BEGIN
1591 IF TG_RELID = 'direct_voter'::regclass AND TG_OP = 'UPDATE' THEN
1592 IF
1593 OLD."issue_id" = NEW."issue_id" AND
1594 OLD."member_id" = NEW."member_id" AND
1595 OLD."weight" = NEW."weight"
1596 THEN
1597 RETURN NULL; -- allows changing of voter comment
1598 END IF;
1599 END IF;
1600 IF TG_OP = 'DELETE' THEN
1601 "issue_id_v" := OLD."issue_id";
1602 ELSE
1603 "issue_id_v" := NEW."issue_id";
1604 END IF;
1605 SELECT INTO "issue_row" * FROM "issue"
1606 WHERE "id" = "issue_id_v" FOR SHARE;
1607 IF "issue_row"."closed" NOTNULL THEN
1608 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
1609 END IF;
1610 RETURN NULL;
1611 END;
1612 $$;
1614 CREATE TRIGGER "forbid_changes_on_closed_issue"
1615 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1616 FOR EACH ROW EXECUTE PROCEDURE
1617 "forbid_changes_on_closed_issue_trigger"();
1619 CREATE TRIGGER "forbid_changes_on_closed_issue"
1620 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1621 FOR EACH ROW EXECUTE PROCEDURE
1622 "forbid_changes_on_closed_issue_trigger"();
1624 CREATE TRIGGER "forbid_changes_on_closed_issue"
1625 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1626 FOR EACH ROW EXECUTE PROCEDURE
1627 "forbid_changes_on_closed_issue_trigger"();
1629 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"';
1630 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';
1631 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';
1632 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';
1636 --------------------------------------------------------------------
1637 -- Auto-retrieval of fields only needed for referential integrity --
1638 --------------------------------------------------------------------
1641 CREATE FUNCTION "autofill_issue_id_trigger"()
1642 RETURNS TRIGGER
1643 LANGUAGE 'plpgsql' VOLATILE AS $$
1644 BEGIN
1645 IF NEW."issue_id" ISNULL THEN
1646 SELECT "issue_id" INTO NEW."issue_id"
1647 FROM "initiative" WHERE "id" = NEW."initiative_id";
1648 END IF;
1649 RETURN NEW;
1650 END;
1651 $$;
1653 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1654 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1656 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1657 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1659 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1660 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1661 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1664 CREATE FUNCTION "autofill_initiative_id_trigger"()
1665 RETURNS TRIGGER
1666 LANGUAGE 'plpgsql' VOLATILE AS $$
1667 BEGIN
1668 IF NEW."initiative_id" ISNULL THEN
1669 SELECT "initiative_id" INTO NEW."initiative_id"
1670 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1671 END IF;
1672 RETURN NEW;
1673 END;
1674 $$;
1676 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1677 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1679 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1680 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1684 -----------------------------------------------------
1685 -- Automatic calculation of certain default values --
1686 -----------------------------------------------------
1689 CREATE FUNCTION "copy_timings_trigger"()
1690 RETURNS TRIGGER
1691 LANGUAGE 'plpgsql' VOLATILE AS $$
1692 DECLARE
1693 "policy_row" "policy"%ROWTYPE;
1694 BEGIN
1695 SELECT * INTO "policy_row" FROM "policy"
1696 WHERE "id" = NEW."policy_id";
1697 IF NEW."admission_time" ISNULL THEN
1698 NEW."admission_time" := "policy_row"."admission_time";
1699 END IF;
1700 IF NEW."discussion_time" ISNULL THEN
1701 NEW."discussion_time" := "policy_row"."discussion_time";
1702 END IF;
1703 IF NEW."verification_time" ISNULL THEN
1704 NEW."verification_time" := "policy_row"."verification_time";
1705 END IF;
1706 IF NEW."voting_time" ISNULL THEN
1707 NEW."voting_time" := "policy_row"."voting_time";
1708 END IF;
1709 RETURN NEW;
1710 END;
1711 $$;
1713 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1714 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1716 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1717 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1720 CREATE FUNCTION "default_for_draft_id_trigger"()
1721 RETURNS TRIGGER
1722 LANGUAGE 'plpgsql' VOLATILE AS $$
1723 BEGIN
1724 IF NEW."draft_id" ISNULL THEN
1725 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1726 WHERE "initiative_id" = NEW."initiative_id";
1727 END IF;
1728 RETURN NEW;
1729 END;
1730 $$;
1732 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"
1733 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1734 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1735 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1737 COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';
1738 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';
1739 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';
1743 ----------------------------------------
1744 -- Automatic creation of dependencies --
1745 ----------------------------------------
1748 CREATE FUNCTION "autocreate_interest_trigger"()
1749 RETURNS TRIGGER
1750 LANGUAGE 'plpgsql' VOLATILE AS $$
1751 BEGIN
1752 IF NOT EXISTS (
1753 SELECT NULL FROM "initiative" JOIN "interest"
1754 ON "initiative"."issue_id" = "interest"."issue_id"
1755 WHERE "initiative"."id" = NEW."initiative_id"
1756 AND "interest"."member_id" = NEW."member_id"
1757 ) THEN
1758 BEGIN
1759 INSERT INTO "interest" ("issue_id", "member_id")
1760 SELECT "issue_id", NEW."member_id"
1761 FROM "initiative" WHERE "id" = NEW."initiative_id";
1762 EXCEPTION WHEN unique_violation THEN END;
1763 END IF;
1764 RETURN NEW;
1765 END;
1766 $$;
1768 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1769 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1771 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1772 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';
1775 CREATE FUNCTION "autocreate_supporter_trigger"()
1776 RETURNS TRIGGER
1777 LANGUAGE 'plpgsql' VOLATILE AS $$
1778 BEGIN
1779 IF NOT EXISTS (
1780 SELECT NULL FROM "suggestion" JOIN "supporter"
1781 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1782 WHERE "suggestion"."id" = NEW."suggestion_id"
1783 AND "supporter"."member_id" = NEW."member_id"
1784 ) THEN
1785 BEGIN
1786 INSERT INTO "supporter" ("initiative_id", "member_id")
1787 SELECT "initiative_id", NEW."member_id"
1788 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1789 EXCEPTION WHEN unique_violation THEN END;
1790 END IF;
1791 RETURN NEW;
1792 END;
1793 $$;
1795 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1796 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1798 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1799 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.';
1803 ------------------------------------------
1804 -- Views and helper functions for views --
1805 ------------------------------------------
1808 CREATE VIEW "unit_delegation" AS
1809 SELECT
1810 "unit"."id" AS "unit_id",
1811 "delegation"."id",
1812 "delegation"."truster_id",
1813 "delegation"."trustee_id",
1814 "delegation"."scope"
1815 FROM "unit"
1816 JOIN "delegation"
1817 ON "delegation"."unit_id" = "unit"."id"
1818 JOIN "member"
1819 ON "delegation"."truster_id" = "member"."id"
1820 JOIN "privilege"
1821 ON "delegation"."unit_id" = "privilege"."unit_id"
1822 AND "delegation"."truster_id" = "privilege"."member_id"
1823 WHERE "member"."active" AND "privilege"."voting_right";
1825 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
1828 CREATE VIEW "area_delegation" AS
1829 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1830 "area"."id" AS "area_id",
1831 "delegation"."id",
1832 "delegation"."truster_id",
1833 "delegation"."trustee_id",
1834 "delegation"."scope"
1835 FROM "area"
1836 JOIN "delegation"
1837 ON "delegation"."unit_id" = "area"."unit_id"
1838 OR "delegation"."area_id" = "area"."id"
1839 JOIN "member"
1840 ON "delegation"."truster_id" = "member"."id"
1841 JOIN "privilege"
1842 ON "area"."unit_id" = "privilege"."unit_id"
1843 AND "delegation"."truster_id" = "privilege"."member_id"
1844 WHERE "member"."active" AND "privilege"."voting_right"
1845 ORDER BY
1846 "area"."id",
1847 "delegation"."truster_id",
1848 "delegation"."scope" DESC;
1850 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
1853 CREATE VIEW "issue_delegation" AS
1854 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1855 "issue"."id" AS "issue_id",
1856 "delegation"."id",
1857 "delegation"."truster_id",
1858 "delegation"."trustee_id",
1859 "delegation"."scope"
1860 FROM "issue"
1861 JOIN "area"
1862 ON "area"."id" = "issue"."area_id"
1863 JOIN "delegation"
1864 ON "delegation"."unit_id" = "area"."unit_id"
1865 OR "delegation"."area_id" = "area"."id"
1866 OR "delegation"."issue_id" = "issue"."id"
1867 JOIN "member"
1868 ON "delegation"."truster_id" = "member"."id"
1869 JOIN "privilege"
1870 ON "area"."unit_id" = "privilege"."unit_id"
1871 AND "delegation"."truster_id" = "privilege"."member_id"
1872 WHERE "member"."active" AND "privilege"."voting_right"
1873 ORDER BY
1874 "issue"."id",
1875 "delegation"."truster_id",
1876 "delegation"."scope" DESC;
1878 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
1881 CREATE FUNCTION "membership_weight_with_skipping"
1882 ( "area_id_p" "area"."id"%TYPE,
1883 "member_id_p" "member"."id"%TYPE,
1884 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1885 RETURNS INT4
1886 LANGUAGE 'plpgsql' STABLE AS $$
1887 DECLARE
1888 "sum_v" INT4;
1889 "delegation_row" "area_delegation"%ROWTYPE;
1890 BEGIN
1891 "sum_v" := 1;
1892 FOR "delegation_row" IN
1893 SELECT "area_delegation".*
1894 FROM "area_delegation" LEFT JOIN "membership"
1895 ON "membership"."area_id" = "area_id_p"
1896 AND "membership"."member_id" = "area_delegation"."truster_id"
1897 WHERE "area_delegation"."area_id" = "area_id_p"
1898 AND "area_delegation"."trustee_id" = "member_id_p"
1899 AND "membership"."member_id" ISNULL
1900 LOOP
1901 IF NOT
1902 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1903 THEN
1904 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1905 "area_id_p",
1906 "delegation_row"."truster_id",
1907 "skip_member_ids_p" || "delegation_row"."truster_id"
1908 );
1909 END IF;
1910 END LOOP;
1911 RETURN "sum_v";
1912 END;
1913 $$;
1915 COMMENT ON FUNCTION "membership_weight_with_skipping"
1916 ( "area"."id"%TYPE,
1917 "member"."id"%TYPE,
1918 INT4[] )
1919 IS 'Helper function for "membership_weight" function';
1922 CREATE FUNCTION "membership_weight"
1923 ( "area_id_p" "area"."id"%TYPE,
1924 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1925 RETURNS INT4
1926 LANGUAGE 'plpgsql' STABLE AS $$
1927 BEGIN
1928 RETURN "membership_weight_with_skipping"(
1929 "area_id_p",
1930 "member_id_p",
1931 ARRAY["member_id_p"]
1932 );
1933 END;
1934 $$;
1936 COMMENT ON FUNCTION "membership_weight"
1937 ( "area"."id"%TYPE,
1938 "member"."id"%TYPE )
1939 IS 'Calculates the potential voting weight of a member in a given area';
1942 CREATE VIEW "member_count_view" AS
1943 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1945 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1948 CREATE VIEW "unit_member_count" AS
1949 SELECT
1950 "unit"."id" AS "unit_id",
1951 count("member"."id") AS "member_count"
1952 FROM "unit"
1953 LEFT JOIN "privilege"
1954 ON "privilege"."unit_id" = "unit"."id"
1955 AND "privilege"."voting_right"
1956 LEFT JOIN "member"
1957 ON "member"."id" = "privilege"."member_id"
1958 AND "member"."active"
1959 GROUP BY "unit"."id";
1961 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
1964 CREATE VIEW "area_member_count" AS
1965 SELECT
1966 "area"."id" AS "area_id",
1967 count("member"."id") AS "direct_member_count",
1968 coalesce(
1969 sum(
1970 CASE WHEN "member"."id" NOTNULL THEN
1971 "membership_weight"("area"."id", "member"."id")
1972 ELSE 0 END
1973 )
1974 ) AS "member_weight"
1975 FROM "area"
1976 LEFT JOIN "membership"
1977 ON "area"."id" = "membership"."area_id"
1978 LEFT JOIN "privilege"
1979 ON "privilege"."unit_id" = "area"."unit_id"
1980 AND "privilege"."member_id" = "membership"."member_id"
1981 AND "privilege"."voting_right"
1982 LEFT JOIN "member"
1983 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
1984 AND "member"."active"
1985 GROUP BY "area"."id";
1987 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"';
1990 CREATE VIEW "opening_draft" AS
1991 SELECT "draft".* FROM (
1992 SELECT
1993 "initiative"."id" AS "initiative_id",
1994 min("draft"."id") AS "draft_id"
1995 FROM "initiative" JOIN "draft"
1996 ON "initiative"."id" = "draft"."initiative_id"
1997 GROUP BY "initiative"."id"
1998 ) AS "subquery"
1999 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
2001 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
2004 CREATE VIEW "current_draft" AS
2005 SELECT "draft".* FROM (
2006 SELECT
2007 "initiative"."id" AS "initiative_id",
2008 max("draft"."id") AS "draft_id"
2009 FROM "initiative" JOIN "draft"
2010 ON "initiative"."id" = "draft"."initiative_id"
2011 GROUP BY "initiative"."id"
2012 ) AS "subquery"
2013 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
2015 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
2018 CREATE VIEW "critical_opinion" AS
2019 SELECT * FROM "opinion"
2020 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
2021 OR ("degree" = -2 AND "fulfilled" = TRUE);
2023 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
2026 CREATE VIEW "battle_participant" AS
2027 SELECT "initiative"."id", "initiative"."issue_id"
2028 FROM "issue" JOIN "initiative"
2029 ON "issue"."id" = "initiative"."issue_id"
2030 WHERE "initiative"."admitted"
2031 UNION ALL
2032 SELECT NULL, "id" AS "issue_id"
2033 FROM "issue";
2035 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
2038 CREATE VIEW "battle_view" AS
2039 SELECT
2040 "issue"."id" AS "issue_id",
2041 "winning_initiative"."id" AS "winning_initiative_id",
2042 "losing_initiative"."id" AS "losing_initiative_id",
2043 sum(
2044 CASE WHEN
2045 coalesce("better_vote"."grade", 0) >
2046 coalesce("worse_vote"."grade", 0)
2047 THEN "direct_voter"."weight" ELSE 0 END
2048 ) AS "count"
2049 FROM "issue"
2050 LEFT JOIN "direct_voter"
2051 ON "issue"."id" = "direct_voter"."issue_id"
2052 JOIN "battle_participant" AS "winning_initiative"
2053 ON "issue"."id" = "winning_initiative"."issue_id"
2054 JOIN "battle_participant" AS "losing_initiative"
2055 ON "issue"."id" = "losing_initiative"."issue_id"
2056 LEFT JOIN "vote" AS "better_vote"
2057 ON "direct_voter"."member_id" = "better_vote"."member_id"
2058 AND "winning_initiative"."id" = "better_vote"."initiative_id"
2059 LEFT JOIN "vote" AS "worse_vote"
2060 ON "direct_voter"."member_id" = "worse_vote"."member_id"
2061 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
2062 WHERE "issue"."closed" NOTNULL
2063 AND "issue"."cleaned" ISNULL
2064 AND (
2065 "winning_initiative"."id" != "losing_initiative"."id" OR
2066 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
2067 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
2068 GROUP BY
2069 "issue"."id",
2070 "winning_initiative"."id",
2071 "losing_initiative"."id";
2073 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';
2076 CREATE VIEW "expired_session" AS
2077 SELECT * FROM "session" WHERE now() > "expiry";
2079 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
2080 DELETE FROM "session" WHERE "ident" = OLD."ident";
2082 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
2083 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
2086 CREATE VIEW "open_issue" AS
2087 SELECT * FROM "issue" WHERE "closed" ISNULL;
2089 COMMENT ON VIEW "open_issue" IS 'All open issues';
2092 CREATE VIEW "issue_with_ranks_missing" AS
2093 SELECT * FROM "issue"
2094 WHERE "fully_frozen" NOTNULL
2095 AND "closed" NOTNULL
2096 AND "ranks_available" = FALSE;
2098 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
2101 CREATE VIEW "member_contingent" AS
2102 SELECT
2103 "member"."id" AS "member_id",
2104 "contingent"."polling",
2105 "contingent"."time_frame",
2106 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
2107 (
2108 SELECT count(1) FROM "draft"
2109 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
2110 WHERE "draft"."author_id" = "member"."id"
2111 AND "initiative"."polling" = "contingent"."polling"
2112 AND "draft"."created" > now() - "contingent"."time_frame"
2113 ) + (
2114 SELECT count(1) FROM "suggestion"
2115 JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"
2116 WHERE "suggestion"."author_id" = "member"."id"
2117 AND "contingent"."polling" = FALSE
2118 AND "suggestion"."created" > now() - "contingent"."time_frame"
2119 )
2120 ELSE NULL END AS "text_entry_count",
2121 "contingent"."text_entry_limit",
2122 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
2123 SELECT count(1) FROM "opening_draft" AS "draft"
2124 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
2125 WHERE "draft"."author_id" = "member"."id"
2126 AND "initiative"."polling" = "contingent"."polling"
2127 AND "draft"."created" > now() - "contingent"."time_frame"
2128 ) ELSE NULL END AS "initiative_count",
2129 "contingent"."initiative_limit"
2130 FROM "member" CROSS JOIN "contingent";
2132 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
2134 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
2135 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
2138 CREATE VIEW "member_contingent_left" AS
2139 SELECT
2140 "member_id",
2141 "polling",
2142 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
2143 max("initiative_limit" - "initiative_count") AS "initiatives_left"
2144 FROM "member_contingent" GROUP BY "member_id", "polling";
2146 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.';
2149 CREATE VIEW "event_seen_by_member" AS
2150 SELECT
2151 "member"."id" AS "seen_by_member_id",
2152 CASE WHEN "event"."state" IN (
2153 'voting',
2154 'finished_without_winner',
2155 'finished_with_winner'
2156 ) THEN
2157 'voting'::"notify_level"
2158 ELSE
2159 CASE WHEN "event"."state" IN (
2160 'verification',
2161 'canceled_after_revocation_during_verification',
2162 'canceled_no_initiative_admitted'
2163 ) THEN
2164 'verification'::"notify_level"
2165 ELSE
2166 CASE WHEN "event"."state" IN (
2167 'discussion',
2168 'canceled_after_revocation_during_discussion'
2169 ) THEN
2170 'discussion'::"notify_level"
2171 ELSE
2172 'all'::"notify_level"
2173 END
2174 END
2175 END AS "notify_level",
2176 "event".*
2177 FROM "member" CROSS JOIN "event"
2178 LEFT JOIN "issue"
2179 ON "event"."issue_id" = "issue"."id"
2180 LEFT JOIN "membership"
2181 ON "member"."id" = "membership"."member_id"
2182 AND "issue"."area_id" = "membership"."area_id"
2183 LEFT JOIN "interest"
2184 ON "member"."id" = "interest"."member_id"
2185 AND "event"."issue_id" = "interest"."issue_id"
2186 LEFT JOIN "supporter"
2187 ON "member"."id" = "supporter"."member_id"
2188 AND "event"."initiative_id" = "supporter"."initiative_id"
2189 LEFT JOIN "ignored_member"
2190 ON "member"."id" = "ignored_member"."member_id"
2191 AND "event"."member_id" = "ignored_member"."other_member_id"
2192 LEFT JOIN "ignored_initiative"
2193 ON "member"."id" = "ignored_initiative"."member_id"
2194 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2195 WHERE (
2196 "supporter"."member_id" NOTNULL OR
2197 "interest"."member_id" NOTNULL OR
2198 ( "membership"."member_id" NOTNULL AND
2199 "event"."event" IN (
2200 'issue_state_changed',
2201 'initiative_created_in_new_issue',
2202 'initiative_created_in_existing_issue',
2203 'initiative_revoked' ) ) )
2204 AND "ignored_member"."member_id" ISNULL
2205 AND "ignored_initiative"."member_id" ISNULL;
2207 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"';
2210 CREATE VIEW "selected_event_seen_by_member" AS
2211 SELECT
2212 "member"."id" AS "seen_by_member_id",
2213 CASE WHEN "event"."state" IN (
2214 'voting',
2215 'finished_without_winner',
2216 'finished_with_winner'
2217 ) THEN
2218 'voting'::"notify_level"
2219 ELSE
2220 CASE WHEN "event"."state" IN (
2221 'verification',
2222 'canceled_after_revocation_during_verification',
2223 'canceled_no_initiative_admitted'
2224 ) THEN
2225 'verification'::"notify_level"
2226 ELSE
2227 CASE WHEN "event"."state" IN (
2228 'discussion',
2229 'canceled_after_revocation_during_discussion'
2230 ) THEN
2231 'discussion'::"notify_level"
2232 ELSE
2233 'all'::"notify_level"
2234 END
2235 END
2236 END AS "notify_level",
2237 "event".*
2238 FROM "member" CROSS JOIN "event"
2239 LEFT JOIN "issue"
2240 ON "event"."issue_id" = "issue"."id"
2241 LEFT JOIN "membership"
2242 ON "member"."id" = "membership"."member_id"
2243 AND "issue"."area_id" = "membership"."area_id"
2244 LEFT JOIN "interest"
2245 ON "member"."id" = "interest"."member_id"
2246 AND "event"."issue_id" = "interest"."issue_id"
2247 LEFT JOIN "supporter"
2248 ON "member"."id" = "supporter"."member_id"
2249 AND "event"."initiative_id" = "supporter"."initiative_id"
2250 LEFT JOIN "ignored_member"
2251 ON "member"."id" = "ignored_member"."member_id"
2252 AND "event"."member_id" = "ignored_member"."other_member_id"
2253 LEFT JOIN "ignored_initiative"
2254 ON "member"."id" = "ignored_initiative"."member_id"
2255 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2256 WHERE (
2257 ( "member"."notify_level" >= 'all' ) OR
2258 ( "member"."notify_level" >= 'voting' AND
2259 "event"."state" IN (
2260 'voting',
2261 'finished_without_winner',
2262 'finished_with_winner' ) ) OR
2263 ( "member"."notify_level" >= 'verification' AND
2264 "event"."state" IN (
2265 'verification',
2266 'canceled_after_revocation_during_verification',
2267 'canceled_no_initiative_admitted' ) ) OR
2268 ( "member"."notify_level" >= 'discussion' AND
2269 "event"."state" IN (
2270 'discussion',
2271 'canceled_after_revocation_during_discussion' ) ) )
2272 AND (
2273 "supporter"."member_id" NOTNULL OR
2274 "interest"."member_id" NOTNULL OR
2275 ( "membership"."member_id" NOTNULL AND
2276 "event"."event" IN (
2277 'issue_state_changed',
2278 'initiative_created_in_new_issue',
2279 'initiative_created_in_existing_issue',
2280 'initiative_revoked' ) ) )
2281 AND "ignored_member"."member_id" ISNULL
2282 AND "ignored_initiative"."member_id" ISNULL;
2284 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"';
2287 CREATE TYPE "timeline_event" AS ENUM (
2288 'issue_created',
2289 'issue_canceled',
2290 'issue_accepted',
2291 'issue_half_frozen',
2292 'issue_finished_without_voting',
2293 'issue_voting_started',
2294 'issue_finished_after_voting',
2295 'initiative_created',
2296 'initiative_revoked',
2297 'draft_created',
2298 'suggestion_created');
2300 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)';
2303 CREATE VIEW "timeline_issue" AS
2304 SELECT
2305 "created" AS "occurrence",
2306 'issue_created'::"timeline_event" AS "event",
2307 "id" AS "issue_id"
2308 FROM "issue"
2309 UNION ALL
2310 SELECT
2311 "closed" AS "occurrence",
2312 'issue_canceled'::"timeline_event" AS "event",
2313 "id" AS "issue_id"
2314 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
2315 UNION ALL
2316 SELECT
2317 "accepted" AS "occurrence",
2318 'issue_accepted'::"timeline_event" AS "event",
2319 "id" AS "issue_id"
2320 FROM "issue" WHERE "accepted" NOTNULL
2321 UNION ALL
2322 SELECT
2323 "half_frozen" AS "occurrence",
2324 'issue_half_frozen'::"timeline_event" AS "event",
2325 "id" AS "issue_id"
2326 FROM "issue" WHERE "half_frozen" NOTNULL
2327 UNION ALL
2328 SELECT
2329 "fully_frozen" AS "occurrence",
2330 'issue_voting_started'::"timeline_event" AS "event",
2331 "id" AS "issue_id"
2332 FROM "issue"
2333 WHERE "fully_frozen" NOTNULL
2334 AND ("closed" ISNULL OR "closed" != "fully_frozen")
2335 UNION ALL
2336 SELECT
2337 "closed" AS "occurrence",
2338 CASE WHEN "fully_frozen" = "closed" THEN
2339 'issue_finished_without_voting'::"timeline_event"
2340 ELSE
2341 'issue_finished_after_voting'::"timeline_event"
2342 END AS "event",
2343 "id" AS "issue_id"
2344 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
2346 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)';
2349 CREATE VIEW "timeline_initiative" AS
2350 SELECT
2351 "created" AS "occurrence",
2352 'initiative_created'::"timeline_event" AS "event",
2353 "id" AS "initiative_id"
2354 FROM "initiative"
2355 UNION ALL
2356 SELECT
2357 "revoked" AS "occurrence",
2358 'initiative_revoked'::"timeline_event" AS "event",
2359 "id" AS "initiative_id"
2360 FROM "initiative" WHERE "revoked" NOTNULL;
2362 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)';
2365 CREATE VIEW "timeline_draft" AS
2366 SELECT
2367 "created" AS "occurrence",
2368 'draft_created'::"timeline_event" AS "event",
2369 "id" AS "draft_id"
2370 FROM "draft";
2372 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)';
2375 CREATE VIEW "timeline_suggestion" AS
2376 SELECT
2377 "created" AS "occurrence",
2378 'suggestion_created'::"timeline_event" AS "event",
2379 "id" AS "suggestion_id"
2380 FROM "suggestion";
2382 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)';
2385 CREATE VIEW "timeline" AS
2386 SELECT
2387 "occurrence",
2388 "event",
2389 "issue_id",
2390 NULL AS "initiative_id",
2391 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
2392 NULL::INT8 AS "suggestion_id"
2393 FROM "timeline_issue"
2394 UNION ALL
2395 SELECT
2396 "occurrence",
2397 "event",
2398 NULL AS "issue_id",
2399 "initiative_id",
2400 NULL AS "draft_id",
2401 NULL AS "suggestion_id"
2402 FROM "timeline_initiative"
2403 UNION ALL
2404 SELECT
2405 "occurrence",
2406 "event",
2407 NULL AS "issue_id",
2408 NULL AS "initiative_id",
2409 "draft_id",
2410 NULL AS "suggestion_id"
2411 FROM "timeline_draft"
2412 UNION ALL
2413 SELECT
2414 "occurrence",
2415 "event",
2416 NULL AS "issue_id",
2417 NULL AS "initiative_id",
2418 NULL AS "draft_id",
2419 "suggestion_id"
2420 FROM "timeline_suggestion";
2422 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)';
2426 ------------------------------------------------------
2427 -- Row set returning function for delegation chains --
2428 ------------------------------------------------------
2431 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
2432 ('first', 'intermediate', 'last', 'repetition');
2434 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
2437 CREATE TYPE "delegation_chain_row" AS (
2438 "index" INT4,
2439 "member_id" INT4,
2440 "member_valid" BOOLEAN,
2441 "participation" BOOLEAN,
2442 "overridden" BOOLEAN,
2443 "scope_in" "delegation_scope",
2444 "scope_out" "delegation_scope",
2445 "disabled_out" BOOLEAN,
2446 "loop" "delegation_chain_loop_tag" );
2448 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';
2450 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
2451 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';
2452 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
2453 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
2454 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
2455 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
2456 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
2459 CREATE FUNCTION "delegation_chain_for_closed_issue"
2460 ( "member_id_p" "member"."id"%TYPE,
2461 "issue_id_p" "issue"."id"%TYPE )
2462 RETURNS SETOF "delegation_chain_row"
2463 LANGUAGE 'plpgsql' STABLE AS $$
2464 DECLARE
2465 "output_row" "delegation_chain_row";
2466 "direct_voter_row" "direct_voter"%ROWTYPE;
2467 "delegating_voter_row" "delegating_voter"%ROWTYPE;
2468 BEGIN
2469 "output_row"."index" := 0;
2470 "output_row"."member_id" := "member_id_p";
2471 "output_row"."member_valid" := TRUE;
2472 "output_row"."participation" := FALSE;
2473 "output_row"."overridden" := FALSE;
2474 "output_row"."disabled_out" := FALSE;
2475 LOOP
2476 SELECT INTO "direct_voter_row" * FROM "direct_voter"
2477 WHERE "issue_id" = "issue_id_p"
2478 AND "member_id" = "output_row"."member_id";
2479 IF "direct_voter_row"."member_id" NOTNULL THEN
2480 "output_row"."participation" := TRUE;
2481 "output_row"."scope_out" := NULL;
2482 "output_row"."disabled_out" := NULL;
2483 RETURN NEXT "output_row";
2484 RETURN;
2485 END IF;
2486 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"
2487 WHERE "issue_id" = "issue_id_p"
2488 AND "member_id" = "output_row"."member_id";
2489 IF "delegating_voter_row"."member_id" ISNULL THEN
2490 RETURN;
2491 END IF;
2492 "output_row"."scope_out" := "delegating_voter_row"."scope";
2493 RETURN NEXT "output_row";
2494 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];
2495 "output_row"."scope_in" := "output_row"."scope_out";
2496 END LOOP;
2497 END;
2498 $$;
2500 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"
2501 ( "member"."id"%TYPE,
2502 "member"."id"%TYPE )
2503 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';
2506 CREATE FUNCTION "delegation_chain"
2507 ( "member_id_p" "member"."id"%TYPE,
2508 "unit_id_p" "unit"."id"%TYPE,
2509 "area_id_p" "area"."id"%TYPE,
2510 "issue_id_p" "issue"."id"%TYPE,
2511 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2512 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2513 RETURNS SETOF "delegation_chain_row"
2514 LANGUAGE 'plpgsql' STABLE AS $$
2515 DECLARE
2516 "scope_v" "delegation_scope";
2517 "unit_id_v" "unit"."id"%TYPE;
2518 "area_id_v" "area"."id"%TYPE;
2519 "issue_row" "issue"%ROWTYPE;
2520 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2521 "loop_member_id_v" "member"."id"%TYPE;
2522 "output_row" "delegation_chain_row";
2523 "output_rows" "delegation_chain_row"[];
2524 "simulate_v" BOOLEAN;
2525 "simulate_here_v" BOOLEAN;
2526 "delegation_row" "delegation"%ROWTYPE;
2527 "row_count" INT4;
2528 "i" INT4;
2529 "loop_v" BOOLEAN;
2530 BEGIN
2531 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
2532 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
2533 END IF;
2534 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
2535 "simulate_v" := TRUE;
2536 ELSE
2537 "simulate_v" := FALSE;
2538 END IF;
2539 IF
2540 "unit_id_p" NOTNULL AND
2541 "area_id_p" ISNULL AND
2542 "issue_id_p" ISNULL
2543 THEN
2544 "scope_v" := 'unit';
2545 "unit_id_v" := "unit_id_p";
2546 ELSIF
2547 "unit_id_p" ISNULL AND
2548 "area_id_p" NOTNULL AND
2549 "issue_id_p" ISNULL
2550 THEN
2551 "scope_v" := 'area';
2552 "area_id_v" := "area_id_p";
2553 SELECT "unit_id" INTO "unit_id_v"
2554 FROM "area" WHERE "id" = "area_id_v";
2555 ELSIF
2556 "unit_id_p" ISNULL AND
2557 "area_id_p" ISNULL AND
2558 "issue_id_p" NOTNULL
2559 THEN
2560 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
2561 IF "issue_row"."id" ISNULL THEN
2562 RETURN;
2563 END IF;
2564 IF "issue_row"."closed" NOTNULL THEN
2565 IF "simulate_v" THEN
2566 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
2567 END IF;
2568 FOR "output_row" IN
2569 SELECT * FROM
2570 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
2571 LOOP
2572 RETURN NEXT "output_row";
2573 END LOOP;
2574 RETURN;
2575 END IF;
2576 "scope_v" := 'issue';
2577 SELECT "area_id" INTO "area_id_v"
2578 FROM "issue" WHERE "id" = "issue_id_p";
2579 SELECT "unit_id" INTO "unit_id_v"
2580 FROM "area" WHERE "id" = "area_id_v";
2581 ELSE
2582 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2583 END IF;
2584 "visited_member_ids" := '{}';
2585 "loop_member_id_v" := NULL;
2586 "output_rows" := '{}';
2587 "output_row"."index" := 0;
2588 "output_row"."member_id" := "member_id_p";
2589 "output_row"."member_valid" := TRUE;
2590 "output_row"."participation" := FALSE;
2591 "output_row"."overridden" := FALSE;
2592 "output_row"."disabled_out" := FALSE;
2593 "output_row"."scope_out" := NULL;
2594 LOOP
2595 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2596 "loop_member_id_v" := "output_row"."member_id";
2597 ELSE
2598 "visited_member_ids" :=
2599 "visited_member_ids" || "output_row"."member_id";
2600 END IF;
2601 IF "output_row"."participation" ISNULL THEN
2602 "output_row"."overridden" := NULL;
2603 ELSIF "output_row"."participation" THEN
2604 "output_row"."overridden" := TRUE;
2605 END IF;
2606 "output_row"."scope_in" := "output_row"."scope_out";
2607 "output_row"."member_valid" := EXISTS (
2608 SELECT NULL FROM "member" JOIN "privilege"
2609 ON "privilege"."member_id" = "member"."id"
2610 AND "privilege"."unit_id" = "unit_id_v"
2611 WHERE "id" = "output_row"."member_id"
2612 AND "member"."active" AND "privilege"."voting_right"
2613 );
2614 "simulate_here_v" := (
2615 "simulate_v" AND
2616 "output_row"."member_id" = "member_id_p"
2617 );
2618 "delegation_row" := ROW(NULL);
2619 IF "output_row"."member_valid" OR "simulate_here_v" THEN
2620 IF "scope_v" = 'unit' THEN
2621 IF NOT "simulate_here_v" THEN
2622 SELECT * INTO "delegation_row" FROM "delegation"
2623 WHERE "truster_id" = "output_row"."member_id"
2624 AND "unit_id" = "unit_id_v";
2625 END IF;
2626 ELSIF "scope_v" = 'area' THEN
2627 "output_row"."participation" := EXISTS (
2628 SELECT NULL FROM "membership"
2629 WHERE "area_id" = "area_id_p"
2630 AND "member_id" = "output_row"."member_id"
2631 );
2632 IF "simulate_here_v" THEN
2633 IF "simulate_trustee_id_p" ISNULL THEN
2634 SELECT * INTO "delegation_row" FROM "delegation"
2635 WHERE "truster_id" = "output_row"."member_id"
2636 AND "unit_id" = "unit_id_v";
2637 END IF;
2638 ELSE
2639 SELECT * INTO "delegation_row" FROM "delegation"
2640 WHERE "truster_id" = "output_row"."member_id"
2641 AND (
2642 "unit_id" = "unit_id_v" OR
2643 "area_id" = "area_id_v"
2644 )
2645 ORDER BY "scope" DESC;
2646 END IF;
2647 ELSIF "scope_v" = 'issue' THEN
2648 IF "issue_row"."fully_frozen" ISNULL THEN
2649 "output_row"."participation" := EXISTS (
2650 SELECT NULL FROM "interest"
2651 WHERE "issue_id" = "issue_id_p"
2652 AND "member_id" = "output_row"."member_id"
2653 );
2654 ELSE
2655 IF "output_row"."member_id" = "member_id_p" THEN
2656 "output_row"."participation" := EXISTS (
2657 SELECT NULL FROM "direct_voter"
2658 WHERE "issue_id" = "issue_id_p"
2659 AND "member_id" = "output_row"."member_id"
2660 );
2661 ELSE
2662 "output_row"."participation" := NULL;
2663 END IF;
2664 END IF;
2665 IF "simulate_here_v" THEN
2666 IF "simulate_trustee_id_p" ISNULL THEN
2667 SELECT * INTO "delegation_row" FROM "delegation"
2668 WHERE "truster_id" = "output_row"."member_id"
2669 AND (
2670 "unit_id" = "unit_id_v" OR
2671 "area_id" = "area_id_v"
2672 )
2673 ORDER BY "scope" DESC;
2674 END IF;
2675 ELSE
2676 SELECT * INTO "delegation_row" FROM "delegation"
2677 WHERE "truster_id" = "output_row"."member_id"
2678 AND (
2679 "unit_id" = "unit_id_v" OR
2680 "area_id" = "area_id_v" OR
2681 "issue_id" = "issue_id_p"
2682 )
2683 ORDER BY "scope" DESC;
2684 END IF;
2685 END IF;
2686 ELSE
2687 "output_row"."participation" := FALSE;
2688 END IF;
2689 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
2690 "output_row"."scope_out" := "scope_v";
2691 "output_rows" := "output_rows" || "output_row";
2692 "output_row"."member_id" := "simulate_trustee_id_p";
2693 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2694 "output_row"."scope_out" := "delegation_row"."scope";
2695 "output_rows" := "output_rows" || "output_row";
2696 "output_row"."member_id" := "delegation_row"."trustee_id";
2697 ELSIF "delegation_row"."scope" NOTNULL THEN
2698 "output_row"."scope_out" := "delegation_row"."scope";
2699 "output_row"."disabled_out" := TRUE;
2700 "output_rows" := "output_rows" || "output_row";
2701 EXIT;
2702 ELSE
2703 "output_row"."scope_out" := NULL;
2704 "output_rows" := "output_rows" || "output_row";
2705 EXIT;
2706 END IF;
2707 EXIT WHEN "loop_member_id_v" NOTNULL;
2708 "output_row"."index" := "output_row"."index" + 1;
2709 END LOOP;
2710 "row_count" := array_upper("output_rows", 1);
2711 "i" := 1;
2712 "loop_v" := FALSE;
2713 LOOP
2714 "output_row" := "output_rows"["i"];
2715 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2716 IF "loop_v" THEN
2717 IF "i" + 1 = "row_count" THEN
2718 "output_row"."loop" := 'last';
2719 ELSIF "i" = "row_count" THEN
2720 "output_row"."loop" := 'repetition';
2721 ELSE
2722 "output_row"."loop" := 'intermediate';
2723 END IF;
2724 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2725 "output_row"."loop" := 'first';
2726 "loop_v" := TRUE;
2727 END IF;
2728 IF "scope_v" = 'unit' THEN
2729 "output_row"."participation" := NULL;
2730 END IF;
2731 RETURN NEXT "output_row";
2732 "i" := "i" + 1;
2733 END LOOP;
2734 RETURN;
2735 END;
2736 $$;
2738 COMMENT ON FUNCTION "delegation_chain"
2739 ( "member"."id"%TYPE,
2740 "unit"."id"%TYPE,
2741 "area"."id"%TYPE,
2742 "issue"."id"%TYPE,
2743 "member"."id"%TYPE,
2744 BOOLEAN )
2745 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
2749 ---------------------------------------------------------
2750 -- Single row returning function for delegation chains --
2751 ---------------------------------------------------------
2754 CREATE TYPE "delegation_info_loop_type" AS ENUM
2755 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');
2757 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''';
2760 CREATE TYPE "delegation_info_type" AS (
2761 "own_participation" BOOLEAN,
2762 "own_delegation_scope" "delegation_scope",
2763 "first_trustee_id" INT4,
2764 "first_trustee_participation" BOOLEAN,
2765 "first_trustee_ellipsis" BOOLEAN,
2766 "other_trustee_id" INT4,
2767 "other_trustee_participation" BOOLEAN,
2768 "other_trustee_ellipsis" BOOLEAN,
2769 "delegation_loop" "delegation_info_loop_type",
2770 "participating_member_id" INT4 );
2772 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';
2774 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';
2775 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';
2776 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';
2777 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
2778 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';
2779 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';
2780 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)';
2781 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';
2782 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';
2783 COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain';
2786 CREATE FUNCTION "delegation_info"
2787 ( "member_id_p" "member"."id"%TYPE,
2788 "unit_id_p" "unit"."id"%TYPE,
2789 "area_id_p" "area"."id"%TYPE,
2790 "issue_id_p" "issue"."id"%TYPE,
2791 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2792 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2793 RETURNS "delegation_info_type"
2794 LANGUAGE 'plpgsql' STABLE AS $$
2795 DECLARE
2796 "current_row" "delegation_chain_row";
2797 "result" "delegation_info_type";
2798 BEGIN
2799 "result"."own_participation" := FALSE;
2800 FOR "current_row" IN
2801 SELECT * FROM "delegation_chain"(
2802 "member_id_p",
2803 "unit_id_p", "area_id_p", "issue_id_p",
2804 "simulate_trustee_id_p", "simulate_default_p")
2805 LOOP
2806 IF
2807 "result"."participating_member_id" ISNULL AND
2808 "current_row"."participation"
2809 THEN
2810 "result"."participating_member_id" := "current_row"."member_id";
2811 END IF;
2812 IF "current_row"."member_id" = "member_id_p" THEN
2813 "result"."own_participation" := "current_row"."participation";
2814 "result"."own_delegation_scope" := "current_row"."scope_out";
2815 IF "current_row"."loop" = 'first' THEN
2816 "result"."delegation_loop" := 'own';
2817 END IF;
2818 ELSIF
2819 "current_row"."member_valid" AND
2820 ( "current_row"."loop" ISNULL OR
2821 "current_row"."loop" != 'repetition' )
2822 THEN
2823 IF "result"."first_trustee_id" ISNULL THEN
2824 "result"."first_trustee_id" := "current_row"."member_id";
2825 "result"."first_trustee_participation" := "current_row"."participation";
2826 "result"."first_trustee_ellipsis" := FALSE;
2827 IF "current_row"."loop" = 'first' THEN
2828 "result"."delegation_loop" := 'first';
2829 END IF;
2830 ELSIF "result"."other_trustee_id" ISNULL THEN
2831 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
2832 "result"."other_trustee_id" := "current_row"."member_id";
2833 "result"."other_trustee_participation" := TRUE;
2834 "result"."other_trustee_ellipsis" := FALSE;
2835 IF "current_row"."loop" = 'first' THEN
2836 "result"."delegation_loop" := 'other';
2837 END IF;
2838 ELSE
2839 "result"."first_trustee_ellipsis" := TRUE;
2840 IF "current_row"."loop" = 'first' THEN
2841 "result"."delegation_loop" := 'first_ellipsis';
2842 END IF;
2843 END IF;
2844 ELSE
2845 "result"."other_trustee_ellipsis" := TRUE;
2846 IF "current_row"."loop" = 'first' THEN
2847 "result"."delegation_loop" := 'other_ellipsis';
2848 END IF;
2849 END IF;
2850 END IF;
2851 END LOOP;
2852 RETURN "result";
2853 END;
2854 $$;
2856 COMMENT ON FUNCTION "delegation_info"
2857 ( "member"."id"%TYPE,
2858 "unit"."id"%TYPE,
2859 "area"."id"%TYPE,
2860 "issue"."id"%TYPE,
2861 "member"."id"%TYPE,
2862 BOOLEAN )
2863 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
2867 ------------------------------------------------
2868 -- Locking for snapshots and voting procedure --
2869 ------------------------------------------------
2872 CREATE FUNCTION "share_row_lock_issue_trigger"()
2873 RETURNS TRIGGER
2874 LANGUAGE 'plpgsql' VOLATILE AS $$
2875 BEGIN
2876 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2877 PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;
2878 END IF;
2879 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2880 PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;
2881 RETURN NEW;
2882 ELSE
2883 RETURN OLD;
2884 END IF;
2885 END;
2886 $$;
2888 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';
2891 CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()
2892 RETURNS TRIGGER
2893 LANGUAGE 'plpgsql' VOLATILE AS $$
2894 BEGIN
2895 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2896 PERFORM NULL FROM "issue"
2897 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2898 WHERE "initiative"."id" = OLD."initiative_id"
2899 FOR SHARE OF "issue";
2900 END IF;
2901 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2902 PERFORM NULL FROM "issue"
2903 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2904 WHERE "initiative"."id" = NEW."initiative_id"
2905 FOR SHARE OF "issue";
2906 RETURN NEW;
2907 ELSE
2908 RETURN OLD;
2909 END IF;
2910 END;
2911 $$;
2913 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';
2916 CREATE TRIGGER "share_row_lock_issue"
2917 BEFORE INSERT OR UPDATE OR DELETE ON "initiative"
2918 FOR EACH ROW EXECUTE PROCEDURE
2919 "share_row_lock_issue_trigger"();
2921 CREATE TRIGGER "share_row_lock_issue"
2922 BEFORE INSERT OR UPDATE OR DELETE ON "interest"
2923 FOR EACH ROW EXECUTE PROCEDURE
2924 "share_row_lock_issue_trigger"();
2926 CREATE TRIGGER "share_row_lock_issue"
2927 BEFORE INSERT OR UPDATE OR DELETE ON "supporter"
2928 FOR EACH ROW EXECUTE PROCEDURE
2929 "share_row_lock_issue_trigger"();
2931 CREATE TRIGGER "share_row_lock_issue_via_initiative"
2932 BEFORE INSERT OR UPDATE OR DELETE ON "opinion"
2933 FOR EACH ROW EXECUTE PROCEDURE
2934 "share_row_lock_issue_via_initiative_trigger"();
2936 CREATE TRIGGER "share_row_lock_issue"
2937 BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter"
2938 FOR EACH ROW EXECUTE PROCEDURE
2939 "share_row_lock_issue_trigger"();
2941 CREATE TRIGGER "share_row_lock_issue"
2942 BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter"
2943 FOR EACH ROW EXECUTE PROCEDURE
2944 "share_row_lock_issue_trigger"();
2946 CREATE TRIGGER "share_row_lock_issue"
2947 BEFORE INSERT OR UPDATE OR DELETE ON "vote"
2948 FOR EACH ROW EXECUTE PROCEDURE
2949 "share_row_lock_issue_trigger"();
2951 COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function';
2952 COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function';
2953 COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function';
2954 COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function';
2955 COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function';
2956 COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function';
2957 COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function';
2960 CREATE FUNCTION "lock_issue"
2961 ( "issue_id_p" "issue"."id"%TYPE )
2962 RETURNS VOID
2963 LANGUAGE 'plpgsql' VOLATILE AS $$
2964 BEGIN
2965 -- The following locking order is used:
2966 -- 1st) row-level lock on the issue
2967 -- 2nd) table-level locks in order of occurrence in the core.sql file
2968 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2969 -- NOTE: The row-level exclusive lock in combination with the
2970 -- share_row_lock_issue(_via_initiative)_trigger functions (which
2971 -- acquire a row-level share lock on the issue) ensure that no data
2972 -- is changed, which could affect calculation of snapshots or
2973 -- counting of votes. Table "delegation" must be table-level-locked,
2974 -- as it also contains issue- and global-scope delegations.
2975 PERFORM NULL FROM "member" WHERE "active" FOR SHARE;
2976 -- NOTE: As we later cause implicit row-level share locks on many
2977 -- active members, we lock them before locking any other table
2978 -- to avoid deadlocks
2979 LOCK TABLE "member" IN SHARE MODE;
2980 LOCK TABLE "privilege" IN SHARE MODE;
2981 LOCK TABLE "membership" IN SHARE MODE;
2982 LOCK TABLE "policy" IN SHARE MODE;
2983 LOCK TABLE "delegation" IN SHARE MODE;
2984 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
2985 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
2986 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
2987 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
2988 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
2989 RETURN;
2990 END;
2991 $$;
2993 COMMENT ON FUNCTION "lock_issue"
2994 ( "issue"."id"%TYPE )
2995 IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';
2999 ------------------------------------------------------------------------
3000 -- Regular tasks, except calculcation of snapshots and voting results --
3001 ------------------------------------------------------------------------
3003 CREATE FUNCTION "check_activity"()
3004 RETURNS VOID
3005 LANGUAGE 'plpgsql' VOLATILE AS $$
3006 DECLARE
3007 "system_setting_row" "system_setting"%ROWTYPE;
3008 BEGIN
3009 SELECT * INTO "system_setting_row" FROM "system_setting";
3010 LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
3011 IF "system_setting_row"."member_ttl" NOTNULL THEN
3012 UPDATE "member" SET "active" = FALSE
3013 WHERE "active" = TRUE
3014 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
3015 END IF;
3016 RETURN;
3017 END;
3018 $$;
3020 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
3023 CREATE FUNCTION "calculate_member_counts"()
3024 RETURNS VOID
3025 LANGUAGE 'plpgsql' VOLATILE AS $$
3026 BEGIN
3027 LOCK TABLE "member" IN SHARE MODE;
3028 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
3029 LOCK TABLE "unit" IN EXCLUSIVE MODE;
3030 LOCK TABLE "area" IN EXCLUSIVE MODE;
3031 LOCK TABLE "privilege" IN SHARE MODE;
3032 LOCK TABLE "membership" IN SHARE MODE;
3033 DELETE FROM "member_count";
3034 INSERT INTO "member_count" ("total_count")
3035 SELECT "total_count" FROM "member_count_view";
3036 UPDATE "unit" SET "member_count" = "view"."member_count"
3037 FROM "unit_member_count" AS "view"
3038 WHERE "view"."unit_id" = "unit"."id";
3039 UPDATE "area" SET
3040 "direct_member_count" = "view"."direct_member_count",
3041 "member_weight" = "view"."member_weight"
3042 FROM "area_member_count" AS "view"
3043 WHERE "view"."area_id" = "area"."id";
3044 RETURN;
3045 END;
3046 $$;
3048 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"';
3052 -------------------------------------
3053 -- Calculation of harmonic weights --
3054 -------------------------------------
3057 CREATE VIEW "remaining_harmonic_supporter_weight" AS
3058 SELECT
3059 "direct_interest_snapshot"."issue_id",
3060 "direct_interest_snapshot"."event",
3061 "direct_interest_snapshot"."member_id",
3062 "direct_interest_snapshot"."weight" AS "weight_num",
3063 count("initiative"."id") AS "weight_den"
3064 FROM "issue"
3065 JOIN "direct_interest_snapshot"
3066 ON "issue"."id" = "direct_interest_snapshot"."issue_id"
3067 AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
3068 JOIN "direct_supporter_snapshot"
3069 ON "direct_interest_snapshot"."issue_id" = "direct_supporter_snapshot"."issue_id"
3070 AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event"
3071 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
3072 JOIN "initiative"
3073 ON "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
3074 AND (
3075 "direct_supporter_snapshot"."satisfied" = TRUE OR
3076 coalesce("initiative"."admitted", FALSE) = FALSE
3077 )
3078 AND "initiative"."harmonic_weight" ISNULL
3079 GROUP BY
3080 "direct_interest_snapshot"."issue_id",
3081 "direct_interest_snapshot"."event",
3082 "direct_interest_snapshot"."member_id",
3083 "direct_interest_snapshot"."weight";
3085 COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';
3088 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
3089 SELECT
3090 "initiative"."issue_id",
3091 "initiative"."id" AS "initiative_id",
3092 "initiative"."admitted",
3093 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
3094 "remaining_harmonic_supporter_weight"."weight_den"
3095 FROM "remaining_harmonic_supporter_weight"
3096 JOIN "direct_supporter_snapshot"
3097 ON "remaining_harmonic_supporter_weight"."issue_id" = "direct_supporter_snapshot"."issue_id"
3098 AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event"
3099 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
3100 JOIN "initiative"
3101 ON "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
3102 AND (
3103 "direct_supporter_snapshot"."satisfied" = TRUE OR
3104 coalesce("initiative"."admitted", FALSE) = FALSE
3105 )
3106 AND "initiative"."harmonic_weight" ISNULL
3107 GROUP BY
3108 "initiative"."issue_id",
3109 "initiative"."id",
3110 "initiative"."admitted",
3111 "remaining_harmonic_supporter_weight"."weight_den";
3113 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
3116 CREATE FUNCTION "set_harmonic_initiative_weights"
3117 ( "issue_id_p" "issue"."id"%TYPE )
3118 RETURNS VOID
3119 LANGUAGE 'plpgsql' VOLATILE AS $$
3120 DECLARE
3121 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;
3122 "i" INT4;
3123 "count_v" INT4;
3124 "summand_v" FLOAT;
3125 "id_ary" INT4[];
3126 "weight_ary" FLOAT[];
3127 "min_weight_v" FLOAT;
3128 BEGIN
3129 UPDATE "initiative" SET "harmonic_weight" = NULL
3130 WHERE "issue_id" = "issue_id_p";
3131 LOOP
3132 "min_weight_v" := NULL;
3133 "i" := 0;
3134 "count_v" := 0;
3135 FOR "weight_row" IN
3136 SELECT * FROM "remaining_harmonic_initiative_weight_summands"
3137 WHERE "issue_id" = "issue_id_p"
3138 AND (
3139 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
3140 SELECT NULL FROM "initiative"
3141 WHERE "issue_id" = "issue_id_p"
3142 AND "harmonic_weight" ISNULL
3143 AND coalesce("admitted", FALSE) = FALSE
3144 )
3145 )
3146 ORDER BY "initiative_id" DESC, "weight_den" DESC
3147 -- NOTE: non-admitted initiatives placed first (at last positions),
3148 -- latest initiatives treated worse in case of tie
3149 LOOP
3150 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
3151 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
3152 "i" := "i" + 1;
3153 "count_v" := "i";
3154 "id_ary"["i"] := "weight_row"."initiative_id";
3155 "weight_ary"["i"] := "summand_v";
3156 ELSE
3157 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
3158 END IF;
3159 END LOOP;
3160 EXIT WHEN "count_v" = 0;
3161 "i" := 1;
3162 LOOP
3163 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
3164 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
3165 "min_weight_v" := "weight_ary"["i"];
3166 END IF;
3167 "i" := "i" + 1;
3168 EXIT WHEN "i" > "count_v";
3169 END LOOP;
3170 "i" := 1;
3171 LOOP
3172 IF "weight_ary"["i"] = "min_weight_v" THEN
3173 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"
3174 WHERE "id" = "id_ary"["i"];
3175 EXIT;
3176 END IF;
3177 "i" := "i" + 1;
3178 END LOOP;
3179 END LOOP;
3180 UPDATE "initiative" SET "harmonic_weight" = 0
3181 WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;
3182 END;
3183 $$;
3185 COMMENT ON FUNCTION "set_harmonic_initiative_weights"
3186 ( "issue"."id"%TYPE )
3187 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
3190 CREATE VIEW "remaining_harmonic_opinion_weight" AS
3191 SELECT
3192 "initiative"."issue_id",
3193 "opinion"."initiative_id",
3194 "direct_interest_snapshot"."member_id",
3195 "direct_interest_snapshot"."weight" AS "weight_num",
3196 count("opinion"."suggestion_id") AS "weight_den"
3197 FROM "issue"
3198 JOIN "direct_interest_snapshot"
3199 ON "issue"."id" = "direct_interest_snapshot"."issue_id"
3200 AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
3201 JOIN "initiative"
3202 ON "direct_interest_snapshot"."issue_id" = "initiative"."issue_id"
3203 JOIN "opinion"
3204 ON "direct_interest_snapshot"."member_id" = "opinion"."member_id"
3205 AND "initiative"."id" = "opinion"."initiative_id"
3206 AND (
3207 ("opinion"."degree" > 0 AND "opinion"."fulfilled" = FALSE) OR
3208 ("opinion"."degree" < 0 AND "opinion"."fulfilled" = TRUE)
3209 )
3210 JOIN "suggestion"
3211 ON "opinion"."suggestion_id" = "suggestion"."id"
3212 AND "suggestion"."harmonic_weight" ISNULL
3213 GROUP BY
3214 "initiative"."issue_id",
3215 "opinion"."initiative_id",
3216 "direct_interest_snapshot"."member_id",
3217 "direct_interest_snapshot"."weight";
3219 COMMENT ON VIEW "remaining_harmonic_opinion_weight" IS 'Helper view for function "set_harmonic_suggestion_weights"';
3222 CREATE VIEW "remaining_harmonic_suggestion_weight_summands" AS
3223 SELECT
3224 "suggestion"."initiative_id",
3225 "opinion"."suggestion_id",
3226 sum("remaining_harmonic_opinion_weight"."weight_num") AS "weight_num",
3227 "remaining_harmonic_opinion_weight"."weight_den"
3228 FROM "remaining_harmonic_opinion_weight"
3229 JOIN "opinion"
3230 ON "remaining_harmonic_opinion_weight"."initiative_id" = "opinion"."initiative_id"
3231 AND "remaining_harmonic_opinion_weight"."member_id" = "opinion"."member_id"
3232 AND (
3233 ("opinion"."degree" > 0 AND "opinion"."fulfilled" = FALSE) OR
3234 ("opinion"."degree" < 0 AND "opinion"."fulfilled" = TRUE)
3235 )
3236 JOIN "suggestion"
3237 ON "opinion"."suggestion_id" = "suggestion"."id"
3238 AND "suggestion"."harmonic_weight" ISNULL
3239 GROUP BY
3240 "suggestion"."initiative_id",
3241 "opinion"."suggestion_id",
3242 "remaining_harmonic_opinion_weight"."weight_den";
3244 COMMENT ON VIEW "remaining_harmonic_suggestion_weight_summands" IS 'Helper view for function "set_harmonic_suggestion_weights"';
3247 CREATE FUNCTION "set_harmonic_suggestion_weights"
3248 ( "initiative_id_p" "initiative"."id"%TYPE )
3249 RETURNS VOID
3250 LANGUAGE 'plpgsql' VOLATILE AS $$
3251 DECLARE
3252 "weight_row" "remaining_harmonic_suggestion_weight_summands"%ROWTYPE;
3253 "i" INT4;
3254 "count_v" INT4;
3255 "summand_v" FLOAT;
3256 "id_ary" INT4[];
3257 "weight_ary" FLOAT[];
3258 "min_weight_v" FLOAT;
3259 BEGIN
3260 UPDATE "suggestion" SET "harmonic_weight" = NULL
3261 WHERE "initiative_id" = "initiative_id_p";
3262 LOOP
3263 "min_weight_v" := NULL;
3264 "i" := 0;
3265 "count_v" := 0;
3266 FOR "weight_row" IN
3267 SELECT * FROM "remaining_harmonic_suggestion_weight_summands"
3268 WHERE "initiative_id" = "initiative_id_p"
3269 ORDER BY "suggestion_id" DESC, "weight_den" DESC
3270 -- NOTE: latest suggestions treated worse in case of tie
3271 LOOP
3272 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
3273 IF "i" = 0 OR "weight_row"."suggestion_id" != "id_ary"["i"] THEN
3274 "i" := "i" + 1;
3275 "count_v" := "i";
3276 "id_ary"["i"] := "weight_row"."suggestion_id";
3277 "weight_ary"["i"] := "summand_v";
3278 ELSE
3279 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
3280 END IF;
3281 END LOOP;
3282 EXIT WHEN "count_v" = 0;
3283 "i" := 1;
3284 LOOP
3285 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
3286 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
3287 "min_weight_v" := "weight_ary"["i"];
3288 END IF;
3289 "i" := "i" + 1;
3290 EXIT WHEN "i" > "count_v";
3291 END LOOP;
3292 "i" := 1;
3293 LOOP
3294 IF "weight_ary"["i"] = "min_weight_v" THEN
3295 UPDATE "suggestion" SET "harmonic_weight" = "min_weight_v"
3296 WHERE "id" = "id_ary"["i"];
3297 EXIT;
3298 END IF;
3299 "i" := "i" + 1;
3300 END LOOP;
3301 END LOOP;
3302 UPDATE "suggestion" SET "harmonic_weight" = 0
3303 WHERE "initiative_id" = "initiative_id_p" AND "harmonic_weight" ISNULL;
3304 END;
3305 $$;
3307 COMMENT ON FUNCTION "set_harmonic_suggestion_weights"
3308 ( "issue"."id"%TYPE )
3309 IS 'Calculates and sets "harmonic_weight" of suggestions in a given initiative';
3312 ------------------------------
3313 -- Calculation of snapshots --
3314 ------------------------------
3317 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
3318 ( "issue_id_p" "issue"."id"%TYPE,
3319 "member_id_p" "member"."id"%TYPE,
3320 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3321 RETURNS "direct_population_snapshot"."weight"%TYPE
3322 LANGUAGE 'plpgsql' VOLATILE AS $$
3323 DECLARE
3324 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3325 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
3326 "weight_v" INT4;
3327 "sub_weight_v" INT4;
3328 BEGIN
3329 "weight_v" := 0;
3330 FOR "issue_delegation_row" IN
3331 SELECT * FROM "issue_delegation"
3332 WHERE "trustee_id" = "member_id_p"
3333 AND "issue_id" = "issue_id_p"
3334 LOOP
3335 IF NOT EXISTS (
3336 SELECT NULL FROM "direct_population_snapshot"
3337 WHERE "issue_id" = "issue_id_p"
3338 AND "event" = 'periodic'
3339 AND "member_id" = "issue_delegation_row"."truster_id"
3340 ) AND NOT EXISTS (
3341 SELECT NULL FROM "delegating_population_snapshot"
3342 WHERE "issue_id" = "issue_id_p"
3343 AND "event" = 'periodic'
3344 AND "member_id" = "issue_delegation_row"."truster_id"
3345 ) THEN
3346 "delegate_member_ids_v" :=
3347 "member_id_p" || "delegate_member_ids_p";
3348 INSERT INTO "delegating_population_snapshot" (
3349 "issue_id",
3350 "event",
3351 "member_id",
3352 "scope",
3353 "delegate_member_ids"
3354 ) VALUES (
3355 "issue_id_p",
3356 'periodic',
3357 "issue_delegation_row"."truster_id",
3358 "issue_delegation_row"."scope",
3359 "delegate_member_ids_v"
3360 );
3361 "sub_weight_v" := 1 +
3362 "weight_of_added_delegations_for_population_snapshot"(
3363 "issue_id_p",
3364 "issue_delegation_row"."truster_id",
3365 "delegate_member_ids_v"
3366 );
3367 UPDATE "delegating_population_snapshot"
3368 SET "weight" = "sub_weight_v"
3369 WHERE "issue_id" = "issue_id_p"
3370 AND "event" = 'periodic'
3371 AND "member_id" = "issue_delegation_row"."truster_id";
3372 "weight_v" := "weight_v" + "sub_weight_v";
3373 END IF;
3374 END LOOP;
3375 RETURN "weight_v";
3376 END;
3377 $$;
3379 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
3380 ( "issue"."id"%TYPE,
3381 "member"."id"%TYPE,
3382 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3383 IS 'Helper function for "create_population_snapshot" function';
3386 CREATE FUNCTION "create_population_snapshot"
3387 ( "issue_id_p" "issue"."id"%TYPE )
3388 RETURNS VOID
3389 LANGUAGE 'plpgsql' VOLATILE AS $$
3390 DECLARE
3391 "member_id_v" "member"."id"%TYPE;
3392 BEGIN
3393 DELETE FROM "direct_population_snapshot"
3394 WHERE "issue_id" = "issue_id_p"
3395 AND "event" = 'periodic';
3396 DELETE FROM "delegating_population_snapshot"
3397 WHERE "issue_id" = "issue_id_p"
3398 AND "event" = 'periodic';
3399 INSERT INTO "direct_population_snapshot"
3400 ("issue_id", "event", "member_id")
3401 SELECT
3402 "issue_id_p" AS "issue_id",
3403 'periodic'::"snapshot_event" AS "event",
3404 "member"."id" AS "member_id"
3405 FROM "issue"
3406 JOIN "area" ON "issue"."area_id" = "area"."id"
3407 JOIN "membership" ON "area"."id" = "membership"."area_id"
3408 JOIN "member" ON "membership"."member_id" = "member"."id"
3409 JOIN "privilege"
3410 ON "privilege"."unit_id" = "area"."unit_id"
3411 AND "privilege"."member_id" = "member"."id"
3412 WHERE "issue"."id" = "issue_id_p"
3413 AND "member"."active" AND "privilege"."voting_right"
3414 UNION
3415 SELECT
3416 "issue_id_p" AS "issue_id",
3417 'periodic'::"snapshot_event" AS "event",
3418 "member"."id" AS "member_id"
3419 FROM "issue"
3420 JOIN "area" ON "issue"."area_id" = "area"."id"
3421 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3422 JOIN "member" ON "interest"."member_id" = "member"."id"
3423 JOIN "privilege"
3424 ON "privilege"."unit_id" = "area"."unit_id"
3425 AND "privilege"."member_id" = "member"."id"
3426 WHERE "issue"."id" = "issue_id_p"
3427 AND "member"."active" AND "privilege"."voting_right";
3428 FOR "member_id_v" IN
3429 SELECT "member_id" FROM "direct_population_snapshot"
3430 WHERE "issue_id" = "issue_id_p"
3431 AND "event" = 'periodic'
3432 LOOP
3433 UPDATE "direct_population_snapshot" SET
3434 "weight" = 1 +
3435 "weight_of_added_delegations_for_population_snapshot"(
3436 "issue_id_p",
3437 "member_id_v",
3438 '{}'
3439 )
3440 WHERE "issue_id" = "issue_id_p"
3441 AND "event" = 'periodic'
3442 AND "member_id" = "member_id_v";
3443 END LOOP;
3444 RETURN;
3445 END;
3446 $$;
3448 COMMENT ON FUNCTION "create_population_snapshot"
3449 ( "issue"."id"%TYPE )
3450 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.';
3453 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3454 ( "issue_id_p" "issue"."id"%TYPE,
3455 "member_id_p" "member"."id"%TYPE,
3456 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3457 RETURNS "direct_interest_snapshot"."weight"%TYPE
3458 LANGUAGE 'plpgsql' VOLATILE AS $$
3459 DECLARE
3460 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3461 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
3462 "weight_v" INT4;
3463 "sub_weight_v" INT4;
3464 BEGIN
3465 "weight_v" := 0;
3466 FOR "issue_delegation_row" IN
3467 SELECT * FROM "issue_delegation"
3468 WHERE "trustee_id" = "member_id_p"
3469 AND "issue_id" = "issue_id_p"
3470 LOOP
3471 IF NOT EXISTS (
3472 SELECT NULL FROM "direct_interest_snapshot"
3473 WHERE "issue_id" = "issue_id_p"
3474 AND "event" = 'periodic'
3475 AND "member_id" = "issue_delegation_row"."truster_id"
3476 ) AND NOT EXISTS (
3477 SELECT NULL FROM "delegating_interest_snapshot"
3478 WHERE "issue_id" = "issue_id_p"
3479 AND "event" = 'periodic'
3480 AND "member_id" = "issue_delegation_row"."truster_id"
3481 ) THEN
3482 "delegate_member_ids_v" :=
3483 "member_id_p" || "delegate_member_ids_p";
3484 INSERT INTO "delegating_interest_snapshot" (
3485 "issue_id",
3486 "event",
3487 "member_id",
3488 "scope",
3489 "delegate_member_ids"
3490 ) VALUES (
3491 "issue_id_p",
3492 'periodic',
3493 "issue_delegation_row"."truster_id",
3494 "issue_delegation_row"."scope",
3495 "delegate_member_ids_v"
3496 );
3497 "sub_weight_v" := 1 +
3498 "weight_of_added_delegations_for_interest_snapshot"(
3499 "issue_id_p",
3500 "issue_delegation_row"."truster_id",
3501 "delegate_member_ids_v"
3502 );
3503 UPDATE "delegating_interest_snapshot"
3504 SET "weight" = "sub_weight_v"
3505 WHERE "issue_id" = "issue_id_p"
3506 AND "event" = 'periodic'
3507 AND "member_id" = "issue_delegation_row"."truster_id";
3508 "weight_v" := "weight_v" + "sub_weight_v";
3509 END IF;
3510 END LOOP;
3511 RETURN "weight_v";
3512 END;
3513 $$;
3515 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3516 ( "issue"."id"%TYPE,
3517 "member"."id"%TYPE,
3518 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3519 IS 'Helper function for "create_interest_snapshot" function';
3522 CREATE FUNCTION "create_interest_snapshot"
3523 ( "issue_id_p" "issue"."id"%TYPE )
3524 RETURNS VOID
3525 LANGUAGE 'plpgsql' VOLATILE AS $$
3526 DECLARE
3527 "member_id_v" "member"."id"%TYPE;
3528 BEGIN
3529 DELETE FROM "direct_interest_snapshot"
3530 WHERE "issue_id" = "issue_id_p"
3531 AND "event" = 'periodic';
3532 DELETE FROM "delegating_interest_snapshot"
3533 WHERE "issue_id" = "issue_id_p"
3534 AND "event" = 'periodic';
3535 DELETE FROM "direct_supporter_snapshot"
3536 WHERE "issue_id" = "issue_id_p"
3537 AND "event" = 'periodic';
3538 INSERT INTO "direct_interest_snapshot"
3539 ("issue_id", "event", "member_id")
3540 SELECT
3541 "issue_id_p" AS "issue_id",
3542 'periodic' AS "event",
3543 "member"."id" AS "member_id"
3544 FROM "issue"
3545 JOIN "area" ON "issue"."area_id" = "area"."id"
3546 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3547 JOIN "member" ON "interest"."member_id" = "member"."id"
3548 JOIN "privilege"
3549 ON "privilege"."unit_id" = "area"."unit_id"
3550 AND "privilege"."member_id" = "member"."id"
3551 WHERE "issue"."id" = "issue_id_p"
3552 AND "member"."active" AND "privilege"."voting_right";
3553 FOR "member_id_v" IN
3554 SELECT "member_id" FROM "direct_interest_snapshot"
3555 WHERE "issue_id" = "issue_id_p"
3556 AND "event" = 'periodic'
3557 LOOP
3558 UPDATE "direct_interest_snapshot" SET
3559 "weight" = 1 +
3560 "weight_of_added_delegations_for_interest_snapshot"(
3561 "issue_id_p",
3562 "member_id_v",
3563 '{}'
3564 )
3565 WHERE "issue_id" = "issue_id_p"
3566 AND "event" = 'periodic'
3567 AND "member_id" = "member_id_v";
3568 END LOOP;
3569 INSERT INTO "direct_supporter_snapshot"
3570 ( "issue_id", "initiative_id", "event", "member_id",
3571 "draft_id", "informed", "satisfied" )
3572 SELECT
3573 "issue_id_p" AS "issue_id",
3574 "initiative"."id" AS "initiative_id",
3575 'periodic' AS "event",
3576 "supporter"."member_id" AS "member_id",
3577 "supporter"."draft_id" AS "draft_id",
3578 "supporter"."draft_id" = "current_draft"."id" AS "informed",
3579 NOT EXISTS (
3580 SELECT NULL FROM "critical_opinion"
3581 WHERE "initiative_id" = "initiative"."id"
3582 AND "member_id" = "supporter"."member_id"
3583 ) AS "satisfied"
3584 FROM "initiative"
3585 JOIN "supporter"
3586 ON "supporter"."initiative_id" = "initiative"."id"
3587 JOIN "current_draft"
3588 ON "initiative"."id" = "current_draft"."initiative_id"
3589 JOIN "direct_interest_snapshot"
3590 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
3591 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
3592 AND "event" = 'periodic'
3593 WHERE "initiative"."issue_id" = "issue_id_p";
3594 RETURN;
3595 END;
3596 $$;
3598 COMMENT ON FUNCTION "create_interest_snapshot"
3599 ( "issue"."id"%TYPE )
3600 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.';
3603 CREATE FUNCTION "create_snapshot"
3604 ( "issue_id_p" "issue"."id"%TYPE )
3605 RETURNS VOID
3606 LANGUAGE 'plpgsql' VOLATILE AS $$
3607 DECLARE
3608 "initiative_id_v" "initiative"."id"%TYPE;
3609 "suggestion_id_v" "suggestion"."id"%TYPE;
3610 BEGIN
3611 PERFORM "lock_issue"("issue_id_p");
3612 PERFORM "create_population_snapshot"("issue_id_p");
3613 PERFORM "create_interest_snapshot"("issue_id_p");
3614 UPDATE "issue" SET
3615 "snapshot" = now(),
3616 "latest_snapshot_event" = 'periodic',
3617 "population" = (
3618 SELECT coalesce(sum("weight"), 0)
3619 FROM "direct_population_snapshot"
3620 WHERE "issue_id" = "issue_id_p"
3621 AND "event" = 'periodic'
3622 )
3623 WHERE "id" = "issue_id_p";
3624 FOR "initiative_id_v" IN
3625 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
3626 LOOP
3627 UPDATE "initiative" SET
3628 "supporter_count" = (
3629 SELECT coalesce(sum("di"."weight"), 0)
3630 FROM "direct_interest_snapshot" AS "di"
3631 JOIN "direct_supporter_snapshot" AS "ds"
3632 ON "di"."member_id" = "ds"."member_id"
3633 WHERE "di"."issue_id" = "issue_id_p"
3634 AND "di"."event" = 'periodic'
3635 AND "ds"."initiative_id" = "initiative_id_v"
3636 AND "ds"."event" = 'periodic'
3637 ),
3638 "informed_supporter_count" = (
3639 SELECT coalesce(sum("di"."weight"), 0)
3640 FROM "direct_interest_snapshot" AS "di"
3641 JOIN "direct_supporter_snapshot" AS "ds"
3642 ON "di"."member_id" = "ds"."member_id"
3643 WHERE "di"."issue_id" = "issue_id_p"
3644 AND "di"."event" = 'periodic'
3645 AND "ds"."initiative_id" = "initiative_id_v"
3646 AND "ds"."event" = 'periodic'
3647 AND "ds"."informed"
3648 ),
3649 "satisfied_supporter_count" = (
3650 SELECT coalesce(sum("di"."weight"), 0)
3651 FROM "direct_interest_snapshot" AS "di"
3652 JOIN "direct_supporter_snapshot" AS "ds"
3653 ON "di"."member_id" = "ds"."member_id"
3654 WHERE "di"."issue_id" = "issue_id_p"
3655 AND "di"."event" = 'periodic'
3656 AND "ds"."initiative_id" = "initiative_id_v"
3657 AND "ds"."event" = 'periodic'
3658 AND "ds"."satisfied"
3659 ),
3660 "satisfied_informed_supporter_count" = (
3661 SELECT coalesce(sum("di"."weight"), 0)
3662 FROM "direct_interest_snapshot" AS "di"
3663 JOIN "direct_supporter_snapshot" AS "ds"
3664 ON "di"."member_id" = "ds"."member_id"
3665 WHERE "di"."issue_id" = "issue_id_p"
3666 AND "di"."event" = 'periodic'
3667 AND "ds"."initiative_id" = "initiative_id_v"
3668 AND "ds"."event" = 'periodic'
3669 AND "ds"."informed"
3670 AND "ds"."satisfied"
3671 )
3672 WHERE "id" = "initiative_id_v";
3673 FOR "suggestion_id_v" IN
3674 SELECT "id" FROM "suggestion"
3675 WHERE "initiative_id" = "initiative_id_v"
3676 LOOP
3677 UPDATE "suggestion" SET
3678 "minus2_unfulfilled_count" = (
3679 SELECT coalesce(sum("snapshot"."weight"), 0)
3680 FROM "issue" CROSS JOIN "opinion"
3681 JOIN "direct_interest_snapshot" AS "snapshot"
3682 ON "snapshot"."issue_id" = "issue"."id"
3683 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3684 AND "snapshot"."member_id" = "opinion"."member_id"
3685 WHERE "issue"."id" = "issue_id_p"
3686 AND "opinion"."suggestion_id" = "suggestion_id_v"
3687 AND "opinion"."degree" = -2
3688 AND "opinion"."fulfilled" = FALSE
3689 ),
3690 "minus2_fulfilled_count" = (
3691 SELECT coalesce(sum("snapshot"."weight"), 0)
3692 FROM "issue" CROSS JOIN "opinion"
3693 JOIN "direct_interest_snapshot" AS "snapshot"
3694 ON "snapshot"."issue_id" = "issue"."id"
3695 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3696 AND "snapshot"."member_id" = "opinion"."member_id"
3697 WHERE "issue"."id" = "issue_id_p"
3698 AND "opinion"."suggestion_id" = "suggestion_id_v"
3699 AND "opinion"."degree" = -2
3700 AND "opinion"."fulfilled" = TRUE
3701 ),
3702 "minus1_unfulfilled_count" = (
3703 SELECT coalesce(sum("snapshot"."weight"), 0)
3704 FROM "issue" CROSS JOIN "opinion"
3705 JOIN "direct_interest_snapshot" AS "snapshot"
3706 ON "snapshot"."issue_id" = "issue"."id"
3707 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3708 AND "snapshot"."member_id" = "opinion"."member_id"
3709 WHERE "issue"."id" = "issue_id_p"
3710 AND "opinion"."suggestion_id" = "suggestion_id_v"
3711 AND "opinion"."degree" = -1
3712 AND "opinion"."fulfilled" = FALSE
3713 ),
3714 "minus1_fulfilled_count" = (
3715 SELECT coalesce(sum("snapshot"."weight"), 0)
3716 FROM "issue" CROSS JOIN "opinion"
3717 JOIN "direct_interest_snapshot" AS "snapshot"
3718 ON "snapshot"."issue_id" = "issue"."id"
3719 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3720 AND "snapshot"."member_id" = "opinion"."member_id"
3721 WHERE "issue"."id" = "issue_id_p"
3722 AND "opinion"."suggestion_id" = "suggestion_id_v"
3723 AND "opinion"."degree" = -1
3724 AND "opinion"."fulfilled" = TRUE
3725 ),
3726 "plus1_unfulfilled_count" = (
3727 SELECT coalesce(sum("snapshot"."weight"), 0)
3728 FROM "issue" CROSS JOIN "opinion"
3729 JOIN "direct_interest_snapshot" AS "snapshot"
3730 ON "snapshot"."issue_id" = "issue"."id"
3731 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3732 AND "snapshot"."member_id" = "opinion"."member_id"
3733 WHERE "issue"."id" = "issue_id_p"
3734 AND "opinion"."suggestion_id" = "suggestion_id_v"
3735 AND "opinion"."degree" = 1
3736 AND "opinion"."fulfilled" = FALSE
3737 ),
3738 "plus1_fulfilled_count" = (
3739 SELECT coalesce(sum("snapshot"."weight"), 0)
3740 FROM "issue" CROSS JOIN "opinion"
3741 JOIN "direct_interest_snapshot" AS "snapshot"
3742 ON "snapshot"."issue_id" = "issue"."id"
3743 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3744 AND "snapshot"."member_id" = "opinion"."member_id"
3745 WHERE "issue"."id" = "issue_id_p"
3746 AND "opinion"."suggestion_id" = "suggestion_id_v"
3747 AND "opinion"."degree" = 1
3748 AND "opinion"."fulfilled" = TRUE
3749 ),
3750 "plus2_unfulfilled_count" = (
3751 SELECT coalesce(sum("snapshot"."weight"), 0)
3752 FROM "issue" CROSS JOIN "opinion"
3753 JOIN "direct_interest_snapshot" AS "snapshot"
3754 ON "snapshot"."issue_id" = "issue"."id"
3755 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3756 AND "snapshot"."member_id" = "opinion"."member_id"
3757 WHERE "issue"."id" = "issue_id_p"
3758 AND "opinion"."suggestion_id" = "suggestion_id_v"
3759 AND "opinion"."degree" = 2
3760 AND "opinion"."fulfilled" = FALSE
3761 ),
3762 "plus2_fulfilled_count" = (
3763 SELECT coalesce(sum("snapshot"."weight"), 0)
3764 FROM "issue" CROSS JOIN "opinion"
3765 JOIN "direct_interest_snapshot" AS "snapshot"
3766 ON "snapshot"."issue_id" = "issue"."id"
3767 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3768 AND "snapshot"."member_id" = "opinion"."member_id"
3769 WHERE "issue"."id" = "issue_id_p"
3770 AND "opinion"."suggestion_id" = "suggestion_id_v"
3771 AND "opinion"."degree" = 2
3772 AND "opinion"."fulfilled" = TRUE
3773 )
3774 WHERE "suggestion"."id" = "suggestion_id_v";
3775 END LOOP;
3776 PERFORM "set_harmonic_suggestion_weights"("initiative_id_v");
3777 END LOOP;
3778 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
3779 RETURN;
3780 END;
3781 $$;
3783 COMMENT ON FUNCTION "create_snapshot"
3784 ( "issue"."id"%TYPE )
3785 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.';
3788 CREATE FUNCTION "set_snapshot_event"
3789 ( "issue_id_p" "issue"."id"%TYPE,
3790 "event_p" "snapshot_event" )
3791 RETURNS VOID
3792 LANGUAGE 'plpgsql' VOLATILE AS $$
3793 DECLARE
3794 "event_v" "issue"."latest_snapshot_event"%TYPE;
3795 BEGIN
3796 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
3797 WHERE "id" = "issue_id_p" FOR UPDATE;
3798 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
3799 WHERE "id" = "issue_id_p";
3800 UPDATE "direct_population_snapshot" SET "event" = "event_p"
3801 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3802 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
3803 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3804 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
3805 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3806 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
3807 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3808 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
3809 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3810 RETURN;
3811 END;
3812 $$;
3814 COMMENT ON FUNCTION "set_snapshot_event"
3815 ( "issue"."id"%TYPE,
3816 "snapshot_event" )
3817 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
3821 ---------------------
3822 -- Freezing issues --
3823 ---------------------
3825 CREATE FUNCTION "freeze_after_snapshot"
3826 ( "issue_id_p" "issue"."id"%TYPE )
3827 RETURNS VOID
3828 LANGUAGE 'plpgsql' VOLATILE AS $$
3829 DECLARE
3830 "issue_row" "issue"%ROWTYPE;
3831 "policy_row" "policy"%ROWTYPE;
3832 "initiative_row" "initiative"%ROWTYPE;
3833 BEGIN
3834 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3835 SELECT * INTO "policy_row"
3836 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3837 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
3838 FOR "initiative_row" IN
3839 SELECT * FROM "initiative"
3840 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3841 LOOP
3842 IF
3843 "initiative_row"."polling" OR (
3844 "initiative_row"."satisfied_supporter_count" > 0 AND
3845 "initiative_row"."satisfied_supporter_count" *
3846 "policy_row"."initiative_quorum_den" >=
3847 "issue_row"."population" * "policy_row"."initiative_quorum_num"
3848 )
3849 THEN
3850 UPDATE "initiative" SET "admitted" = TRUE
3851 WHERE "id" = "initiative_row"."id";
3852 ELSE
3853 UPDATE "initiative" SET "admitted" = FALSE
3854 WHERE "id" = "initiative_row"."id";
3855 END IF;
3856 END LOOP;
3857 IF EXISTS (
3858 SELECT NULL FROM "initiative"
3859 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
3860 ) THEN
3861 UPDATE "issue" SET
3862 "state" = 'voting',
3863 "accepted" = coalesce("accepted", now()),
3864 "half_frozen" = coalesce("half_frozen", now()),
3865 "fully_frozen" = now()
3866 WHERE "id" = "issue_id_p";
3867 ELSE
3868 UPDATE "issue" SET
3869 "state" = 'canceled_no_initiative_admitted',
3870 "accepted" = coalesce("accepted", now()),
3871 "half_frozen" = coalesce("half_frozen", now()),
3872 "fully_frozen" = now(),
3873 "closed" = now(),
3874 "ranks_available" = TRUE
3875 WHERE "id" = "issue_id_p";
3876 -- NOTE: The following DELETE statements have effect only when
3877 -- issue state has been manipulated
3878 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
3879 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
3880 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3881 END IF;
3882 RETURN;
3883 END;
3884 $$;
3886 COMMENT ON FUNCTION "freeze_after_snapshot"
3887 ( "issue"."id"%TYPE )
3888 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
3891 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
3892 RETURNS VOID
3893 LANGUAGE 'plpgsql' VOLATILE AS $$
3894 DECLARE
3895 "issue_row" "issue"%ROWTYPE;
3896 BEGIN
3897 PERFORM "create_snapshot"("issue_id_p");
3898 PERFORM "freeze_after_snapshot"("issue_id_p");
3899 RETURN;
3900 END;
3901 $$;
3903 COMMENT ON FUNCTION "manual_freeze"
3904 ( "issue"."id"%TYPE )
3905 IS 'Freeze an issue manually (fully) and start voting';
3909 -----------------------
3910 -- Counting of votes --
3911 -----------------------
3914 CREATE FUNCTION "weight_of_added_vote_delegations"
3915 ( "issue_id_p" "issue"."id"%TYPE,
3916 "member_id_p" "member"."id"%TYPE,
3917 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
3918 RETURNS "direct_voter"."weight"%TYPE
3919 LANGUAGE 'plpgsql' VOLATILE AS $$
3920 DECLARE
3921 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3922 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
3923 "weight_v" INT4;
3924 "sub_weight_v" INT4;
3925 BEGIN
3926 "weight_v" := 0;
3927 FOR "issue_delegation_row" IN
3928 SELECT * FROM "issue_delegation"
3929 WHERE "trustee_id" = "member_id_p"
3930 AND "issue_id" = "issue_id_p"
3931 LOOP
3932 IF NOT EXISTS (
3933 SELECT NULL FROM "direct_voter"
3934 WHERE "member_id" = "issue_delegation_row"."truster_id"
3935 AND "issue_id" = "issue_id_p"
3936 ) AND NOT EXISTS (
3937 SELECT NULL FROM "delegating_voter"
3938 WHERE "member_id" = "issue_delegation_row"."truster_id"
3939 AND "issue_id" = "issue_id_p"
3940 ) THEN
3941 "delegate_member_ids_v" :=
3942 "member_id_p" || "delegate_member_ids_p";
3943 INSERT INTO "delegating_voter" (
3944 "issue_id",
3945 "member_id",
3946 "scope",
3947 "delegate_member_ids"
3948 ) VALUES (
3949 "issue_id_p",
3950 "issue_delegation_row"."truster_id",
3951 "issue_delegation_row"."scope",
3952 "delegate_member_ids_v"
3953 );
3954 "sub_weight_v" := 1 +
3955 "weight_of_added_vote_delegations"(
3956 "issue_id_p",
3957 "issue_delegation_row"."truster_id",
3958 "delegate_member_ids_v"
3959 );
3960 UPDATE "delegating_voter"
3961 SET "weight" = "sub_weight_v"
3962 WHERE "issue_id" = "issue_id_p"
3963 AND "member_id" = "issue_delegation_row"."truster_id";
3964 "weight_v" := "weight_v" + "sub_weight_v";
3965 END IF;
3966 END LOOP;
3967 RETURN "weight_v";
3968 END;
3969 $$;
3971 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
3972 ( "issue"."id"%TYPE,
3973 "member"."id"%TYPE,
3974 "delegating_voter"."delegate_member_ids"%TYPE )
3975 IS 'Helper function for "add_vote_delegations" function';
3978 CREATE FUNCTION "add_vote_delegations"
3979 ( "issue_id_p" "issue"."id"%TYPE )
3980 RETURNS VOID
3981 LANGUAGE 'plpgsql' VOLATILE AS $$
3982 DECLARE
3983 "member_id_v" "member"."id"%TYPE;
3984 BEGIN
3985 FOR "member_id_v" IN
3986 SELECT "member_id" FROM "direct_voter"
3987 WHERE "issue_id" = "issue_id_p"
3988 LOOP
3989 UPDATE "direct_voter" SET
3990 "weight" = "weight" + "weight_of_added_vote_delegations"(
3991 "issue_id_p",
3992 "member_id_v",
3993 '{}'
3994 )
3995 WHERE "member_id" = "member_id_v"
3996 AND "issue_id" = "issue_id_p";
3997 END LOOP;
3998 RETURN;
3999 END;
4000 $$;
4002 COMMENT ON FUNCTION "add_vote_delegations"
4003 ( "issue_id_p" "issue"."id"%TYPE )
4004 IS 'Helper function for "close_voting" function';
4007 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
4008 RETURNS VOID
4009 LANGUAGE 'plpgsql' VOLATILE AS $$
4010 DECLARE
4011 "area_id_v" "area"."id"%TYPE;
4012 "unit_id_v" "unit"."id"%TYPE;
4013 "member_id_v" "member"."id"%TYPE;
4014 BEGIN
4015 PERFORM "lock_issue"("issue_id_p");
4016 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
4017 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
4018 -- delete timestamp of voting comment:
4019 UPDATE "direct_voter" SET "comment_changed" = NULL
4020 WHERE "issue_id" = "issue_id_p";
4021 -- delete delegating votes (in cases of manual reset of issue state):
4022 DELETE FROM "delegating_voter"
4023 WHERE "issue_id" = "issue_id_p";
4024 -- delete votes from non-privileged voters:
4025 DELETE FROM "direct_voter"
4026 USING (
4027 SELECT
4028 "direct_voter"."member_id"
4029 FROM "direct_voter"
4030 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
4031 LEFT JOIN "privilege"
4032 ON "privilege"."unit_id" = "unit_id_v"
4033 AND "privilege"."member_id" = "direct_voter"."member_id"
4034 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
4035 "member"."active" = FALSE OR
4036 "privilege"."voting_right" ISNULL OR
4037 "privilege"."voting_right" = FALSE
4038 )
4039 ) AS "subquery"
4040 WHERE "direct_voter"."issue_id" = "issue_id_p"
4041 AND "direct_voter"."member_id" = "subquery"."member_id";
4042 -- consider delegations:
4043 UPDATE "direct_voter" SET "weight" = 1
4044 WHERE "issue_id" = "issue_id_p";
4045 PERFORM "add_vote_delegations"("issue_id_p");
4046 -- set voter count and mark issue as being calculated:
4047 UPDATE "issue" SET
4048 "state" = 'calculation',
4049 "closed" = now(),
4050 "voter_count" = (
4051 SELECT coalesce(sum("weight"), 0)
4052 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
4053 )
4054 WHERE "id" = "issue_id_p";
4055 -- materialize battle_view:
4056 -- NOTE: "closed" column of issue must be set at this point
4057 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
4058 INSERT INTO "battle" (
4059 "issue_id",
4060 "winning_initiative_id", "losing_initiative_id",
4061 "count"
4062 ) SELECT
4063 "issue_id",
4064 "winning_initiative_id", "losing_initiative_id",
4065 "count"
4066 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
4067 -- copy "positive_votes" and "negative_votes" from "battle" table:
4068 UPDATE "initiative" SET
4069 "positive_votes" = "battle_win"."count",
4070 "negative_votes" = "battle_lose"."count"
4071 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
4072 WHERE
4073 "battle_win"."issue_id" = "issue_id_p" AND
4074 "battle_win"."winning_initiative_id" = "initiative"."id" AND
4075 "battle_win"."losing_initiative_id" ISNULL AND
4076 "battle_lose"."issue_id" = "issue_id_p" AND
4077 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
4078 "battle_lose"."winning_initiative_id" ISNULL;
4079 END;
4080 $$;
4082 COMMENT ON FUNCTION "close_voting"
4083 ( "issue"."id"%TYPE )
4084 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.';
4087 CREATE FUNCTION "defeat_strength"
4088 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
4089 RETURNS INT8
4090 LANGUAGE 'plpgsql' IMMUTABLE AS $$
4091 BEGIN
4092 IF "positive_votes_p" > "negative_votes_p" THEN
4093 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
4094 ELSIF "positive_votes_p" = "negative_votes_p" THEN
4095 RETURN 0;
4096 ELSE
4097 RETURN -1;
4098 END IF;
4099 END;
4100 $$;
4102 COMMENT ON FUNCTION "defeat_strength"(INT4, INT4) IS 'Calculates defeat strength (INT8!) of a pairwise defeat primarily by the absolute number of votes for the winner and secondarily by the absolute number of votes for the loser';
4105 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
4106 RETURNS VOID
4107 LANGUAGE 'plpgsql' VOLATILE AS $$
4108 DECLARE
4109 "issue_row" "issue"%ROWTYPE;
4110 "policy_row" "policy"%ROWTYPE;
4111 "dimension_v" INTEGER;
4112 "vote_matrix" INT4[][]; -- absolute votes
4113 "matrix" INT8[][]; -- defeat strength / best paths
4114 "i" INTEGER;
4115 "j" INTEGER;
4116 "k" INTEGER;
4117 "battle_row" "battle"%ROWTYPE;
4118 "rank_ary" INT4[];
4119 "rank_v" INT4;
4120 "done_v" INTEGER;
4121 "winners_ary" INTEGER[];
4122 "initiative_id_v" "initiative"."id"%TYPE;
4123 BEGIN
4124 SELECT * INTO "issue_row"
4125 FROM "issue" WHERE "id" = "issue_id_p"
4126 FOR UPDATE;
4127 SELECT * INTO "policy_row"
4128 FROM "policy" WHERE "id" = "issue_row"."policy_id";
4129 SELECT count(1) INTO "dimension_v"
4130 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
4131 -- Create "vote_matrix" with absolute number of votes in pairwise
4132 -- comparison:
4133 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
4134 "i" := 1;
4135 "j" := 2;
4136 FOR "battle_row" IN
4137 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
4138 ORDER BY
4139 "winning_initiative_id" NULLS LAST,
4140 "losing_initiative_id" NULLS LAST
4141 LOOP
4142 "vote_matrix"["i"]["j"] := "battle_row"."count";
4143 IF "j" = "dimension_v" THEN
4144 "i" := "i" + 1;
4145 "j" := 1;
4146 ELSE
4147 "j" := "j" + 1;
4148 IF "j" = "i" THEN
4149 "j" := "j" + 1;
4150 END IF;
4151 END IF;
4152 END LOOP;
4153 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
4154 RAISE EXCEPTION 'Wrong battle count (should not happen)';
4155 END IF;
4156 -- Store defeat strengths in "matrix" using "defeat_strength"
4157 -- function:
4158 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
4159 "i" := 1;
4160 LOOP
4161 "j" := 1;
4162 LOOP
4163 IF "i" != "j" THEN
4164 "matrix"["i"]["j"] := "defeat_strength"(
4165 "vote_matrix"["i"]["j"],
4166 "vote_matrix"["j"]["i"]
4167 );
4168 END IF;
4169 EXIT WHEN "j" = "dimension_v";
4170 "j" := "j" + 1;
4171 END LOOP;
4172 EXIT WHEN "i" = "dimension_v";
4173 "i" := "i" + 1;
4174 END LOOP;
4175 -- Find best paths:
4176 "i" := 1;
4177 LOOP
4178 "j" := 1;
4179 LOOP
4180 IF "i" != "j" THEN
4181 "k" := 1;
4182 LOOP
4183 IF "i" != "k" AND "j" != "k" THEN
4184 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
4185 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
4186 "matrix"["j"]["k"] := "matrix"["j"]["i"];
4187 END IF;
4188 ELSE
4189 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
4190 "matrix"["j"]["k"] := "matrix"["i"]["k"];
4191 END IF;
4192 END IF;
4193 END IF;
4194 EXIT WHEN "k" = "dimension_v";
4195 "k" := "k" + 1;
4196 END LOOP;
4197 END IF;
4198 EXIT WHEN "j" = "dimension_v";
4199 "j" := "j" + 1;
4200 END LOOP;
4201 EXIT WHEN "i" = "dimension_v";
4202 "i" := "i" + 1;
4203 END LOOP;
4204 -- Determine order of winners:
4205 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
4206 "rank_v" := 1;
4207 "done_v" := 0;
4208 LOOP
4209 "winners_ary" := '{}';
4210 "i" := 1;
4211 LOOP
4212 IF "rank_ary"["i"] ISNULL THEN
4213 "j" := 1;
4214 LOOP
4215 IF
4216 "i" != "j" AND
4217 "rank_ary"["j"] ISNULL AND
4218 "matrix"["j"]["i"] > "matrix"["i"]["j"]
4219 THEN
4220 -- someone else is better
4221 EXIT;
4222 END IF;
4223 IF "j" = "dimension_v" THEN
4224 -- noone is better
4225 "winners_ary" := "winners_ary" || "i";
4226 EXIT;
4227 END IF;
4228 "j" := "j" + 1;
4229 END LOOP;
4230 END IF;
4231 EXIT WHEN "i" = "dimension_v";
4232 "i" := "i" + 1;
4233 END LOOP;
4234 "i" := 1;
4235 LOOP
4236 "rank_ary"["winners_ary"["i"]] := "rank_v";
4237 "done_v" := "done_v" + 1;
4238 EXIT WHEN "i" = array_upper("winners_ary", 1);
4239 "i" := "i" + 1;
4240 END LOOP;
4241 EXIT WHEN "done_v" = "dimension_v";
4242 "rank_v" := "rank_v" + 1;
4243 END LOOP;
4244 -- write preliminary results:
4245 "i" := 1;
4246 FOR "initiative_id_v" IN
4247 SELECT "id" FROM "initiative"
4248 WHERE "issue_id" = "issue_id_p" AND "admitted"
4249 ORDER BY "id"
4250 LOOP
4251 UPDATE "initiative" SET
4252 "direct_majority" =
4253 CASE WHEN "policy_row"."direct_majority_strict" THEN
4254 "positive_votes" * "policy_row"."direct_majority_den" >
4255 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
4256 ELSE
4257 "positive_votes" * "policy_row"."direct_majority_den" >=
4258 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
4259 END
4260 AND "positive_votes" >= "policy_row"."direct_majority_positive"
4261 AND "issue_row"."voter_count"-"negative_votes" >=
4262 "policy_row"."direct_majority_non_negative",
4263 "indirect_majority" =
4264 CASE WHEN "policy_row"."indirect_majority_strict" THEN
4265 "positive_votes" * "policy_row"."indirect_majority_den" >
4266 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
4267 ELSE
4268 "positive_votes" * "policy_row"."indirect_majority_den" >=
4269 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
4270 END
4271 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
4272 AND "issue_row"."voter_count"-"negative_votes" >=
4273 "policy_row"."indirect_majority_non_negative",
4274 "schulze_rank" = "rank_ary"["i"],
4275 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
4276 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
4277 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
4278 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0,
4279 "eligible" = FALSE,
4280 "winner" = FALSE,
4281 "rank" = NULL -- NOTE: in cases of manual reset of issue state
4282 WHERE "id" = "initiative_id_v";
4283 "i" := "i" + 1;
4284 END LOOP;
4285 IF "i" != "dimension_v" THEN
4286 RAISE EXCEPTION 'Wrong winner count (should not happen)';
4287 END IF;
4288 -- take indirect majorities into account:
4289 LOOP
4290 UPDATE "initiative" SET "indirect_majority" = TRUE
4291 FROM (
4292 SELECT "new_initiative"."id" AS "initiative_id"
4293 FROM "initiative" "old_initiative"
4294 JOIN "initiative" "new_initiative"
4295 ON "new_initiative"."issue_id" = "issue_id_p"
4296 AND "new_initiative"."indirect_majority" = FALSE
4297 JOIN "battle" "battle_win"
4298 ON "battle_win"."issue_id" = "issue_id_p"
4299 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
4300 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
4301 JOIN "battle" "battle_lose"
4302 ON "battle_lose"."issue_id" = "issue_id_p"
4303 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
4304 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
4305 WHERE "old_initiative"."issue_id" = "issue_id_p"
4306 AND "old_initiative"."indirect_majority" = TRUE
4307 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
4308 "battle_win"."count" * "policy_row"."indirect_majority_den" >
4309 "policy_row"."indirect_majority_num" *
4310 ("battle_win"."count"+"battle_lose"."count")
4311 ELSE
4312 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
4313 "policy_row"."indirect_majority_num" *
4314 ("battle_win"."count"+"battle_lose"."count")
4315 END
4316 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
4317 AND "issue_row"."voter_count"-"battle_lose"."count" >=
4318 "policy_row"."indirect_majority_non_negative"
4319 ) AS "subquery"
4320 WHERE "id" = "subquery"."initiative_id";
4321 EXIT WHEN NOT FOUND;
4322 END LOOP;
4323 -- set "multistage_majority" for remaining matching initiatives:
4324 UPDATE "initiative" SET "multistage_majority" = TRUE
4325 FROM (
4326 SELECT "losing_initiative"."id" AS "initiative_id"
4327 FROM "initiative" "losing_initiative"
4328 JOIN "initiative" "winning_initiative"
4329 ON "winning_initiative"."issue_id" = "issue_id_p"
4330 AND "winning_initiative"."admitted"
4331 JOIN "battle" "battle_win"
4332 ON "battle_win"."issue_id" = "issue_id_p"
4333 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
4334 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
4335 JOIN "battle" "battle_lose"
4336 ON "battle_lose"."issue_id" = "issue_id_p"
4337 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
4338 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
4339 WHERE "losing_initiative"."issue_id" = "issue_id_p"
4340 AND "losing_initiative"."admitted"
4341 AND "winning_initiative"."schulze_rank" <
4342 "losing_initiative"."schulze_rank"
4343 AND "battle_win"."count" > "battle_lose"."count"
4344 AND (
4345 "battle_win"."count" > "winning_initiative"."positive_votes" OR
4346 "battle_lose"."count" < "losing_initiative"."negative_votes" )
4347 ) AS "subquery"
4348 WHERE "id" = "subquery"."initiative_id";
4349 -- mark eligible initiatives:
4350 UPDATE "initiative" SET "eligible" = TRUE
4351 WHERE "issue_id" = "issue_id_p"
4352 AND "initiative"."direct_majority"
4353 AND "initiative"."indirect_majority"
4354 AND "initiative"."better_than_status_quo"
4355 AND (
4356 "policy_row"."no_multistage_majority" = FALSE OR
4357 "initiative"."multistage_majority" = FALSE )
4358 AND (
4359 "policy_row"."no_reverse_beat_path" = FALSE OR
4360 "initiative"."reverse_beat_path" = FALSE );
4361 -- mark final winner:
4362 UPDATE "initiative" SET "winner" = TRUE
4363 FROM (
4364 SELECT "id" AS "initiative_id"
4365 FROM "initiative"
4366 WHERE "issue_id" = "issue_id_p" AND "eligible"
4367 ORDER BY
4368 "schulze_rank",
4369 "id"
4370 LIMIT 1
4371 ) AS "subquery"
4372 WHERE "id" = "subquery"."initiative_id";
4373 -- write (final) ranks:
4374 "rank_v" := 1;
4375 FOR "initiative_id_v" IN
4376 SELECT "id"
4377 FROM "initiative"
4378 WHERE "issue_id" = "issue_id_p" AND "admitted"
4379 ORDER BY
4380 "winner" DESC,
4381 "eligible" DESC,
4382 "schulze_rank",
4383 "id"
4384 LOOP
4385 UPDATE "initiative" SET "rank" = "rank_v"
4386 WHERE "id" = "initiative_id_v";
4387 "rank_v" := "rank_v" + 1;
4388 END LOOP;
4389 -- set schulze rank of status quo and mark issue as finished:
4390 UPDATE "issue" SET
4391 "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
4392 "state" =
4393 CASE WHEN EXISTS (
4394 SELECT NULL FROM "initiative"
4395 WHERE "issue_id" = "issue_id_p" AND "winner"
4396 ) THEN
4397 'finished_with_winner'::"issue_state"
4398 ELSE
4399 'finished_without_winner'::"issue_state"
4400 END,
4401 "ranks_available" = TRUE
4402 WHERE "id" = "issue_id_p";
4403 RETURN;
4404 END;
4405 $$;
4407 COMMENT ON FUNCTION "calculate_ranks"
4408 ( "issue"."id"%TYPE )
4409 IS 'Determine ranking (Votes have to be counted first)';
4413 -----------------------------
4414 -- Automatic state changes --
4415 -----------------------------
4418 CREATE FUNCTION "check_issue"
4419 ( "issue_id_p" "issue"."id"%TYPE )
4420 RETURNS VOID
4421 LANGUAGE 'plpgsql' VOLATILE AS $$
4422 DECLARE
4423 "issue_row" "issue"%ROWTYPE;
4424 "policy_row" "policy"%ROWTYPE;
4425 BEGIN
4426 PERFORM "lock_issue"("issue_id_p");
4427 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4428 -- only process open issues:
4429 IF "issue_row"."closed" ISNULL THEN
4430 SELECT * INTO "policy_row" FROM "policy"
4431 WHERE "id" = "issue_row"."policy_id";
4432 -- create a snapshot, unless issue is already fully frozen:
4433 IF "issue_row"."fully_frozen" ISNULL THEN
4434 PERFORM "create_snapshot"("issue_id_p");
4435 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4436 END IF;
4437 -- eventually close or accept issues, which have not been accepted:
4438 IF "issue_row"."accepted" ISNULL THEN
4439 IF EXISTS (
4440 SELECT NULL FROM "initiative"
4441 WHERE "issue_id" = "issue_id_p"
4442 AND "supporter_count" > 0
4443 AND "supporter_count" * "policy_row"."issue_quorum_den"
4444 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
4445 ) THEN
4446 -- accept issues, if supporter count is high enough
4447 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
4448 -- NOTE: "issue_row" used later
4449 "issue_row"."state" := 'discussion';
4450 "issue_row"."accepted" := now();
4451 UPDATE "issue" SET
4452 "state" = "issue_row"."state",
4453 "accepted" = "issue_row"."accepted"
4454 WHERE "id" = "issue_row"."id";
4455 ELSIF
4456 now() >= "issue_row"."created" + "issue_row"."admission_time"
4457 THEN
4458 -- close issues, if admission time has expired
4459 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
4460 UPDATE "issue" SET
4461 "state" = 'canceled_issue_not_accepted',
4462 "closed" = now()
4463 WHERE "id" = "issue_row"."id";
4464 END IF;
4465 END IF;
4466 -- eventually half freeze issues:
4467 IF
4468 -- NOTE: issue can't be closed at this point, if it has been accepted
4469 "issue_row"."accepted" NOTNULL AND
4470 "issue_row"."half_frozen" ISNULL
4471 THEN
4472 IF
4473 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
4474 THEN
4475 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
4476 -- NOTE: "issue_row" used later
4477 "issue_row"."state" := 'verification';
4478 "issue_row"."half_frozen" := now();
4479 UPDATE "issue" SET
4480 "state" = "issue_row"."state",
4481 "half_frozen" = "issue_row"."half_frozen"
4482 WHERE "id" = "issue_row"."id";
4483 END IF;
4484 END IF;
4485 -- close issues after some time, if all initiatives have been revoked:
4486 IF
4487 "issue_row"."closed" ISNULL AND
4488 NOT EXISTS (
4489 -- all initiatives are revoked
4490 SELECT NULL FROM "initiative"
4491 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
4492 ) AND (
4493 -- and issue has not been accepted yet
4494 "issue_row"."accepted" ISNULL OR
4495 NOT EXISTS (
4496 -- or no initiatives have been revoked lately
4497 SELECT NULL FROM "initiative"
4498 WHERE "issue_id" = "issue_id_p"
4499 AND now() < "revoked" + "issue_row"."verification_time"
4500 ) OR (
4501 -- or verification time has elapsed
4502 "issue_row"."half_frozen" NOTNULL AND
4503 "issue_row"."fully_frozen" ISNULL AND
4504 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
4505 )
4506 )
4507 THEN
4508 -- NOTE: "issue_row" used later
4509 IF "issue_row"."accepted" ISNULL THEN
4510 "issue_row"."state" := 'canceled_revoked_before_accepted';
4511 ELSIF "issue_row"."half_frozen" ISNULL THEN
4512 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
4513 ELSE
4514 "issue_row"."state" := 'canceled_after_revocation_during_verification';
4515 END IF;
4516 "issue_row"."closed" := now();
4517 UPDATE "issue" SET
4518 "state" = "issue_row"."state",
4519 "closed" = "issue_row"."closed"
4520 WHERE "id" = "issue_row"."id";
4521 END IF;
4522 -- fully freeze issue after verification time:
4523 IF
4524 "issue_row"."half_frozen" NOTNULL AND
4525 "issue_row"."fully_frozen" ISNULL AND
4526 "issue_row"."closed" ISNULL AND
4527 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
4528 THEN
4529 PERFORM "freeze_after_snapshot"("issue_id_p");
4530 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
4531 END IF;
4532 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4533 -- close issue by calling close_voting(...) after voting time:
4534 IF
4535 "issue_row"."closed" ISNULL AND
4536 "issue_row"."fully_frozen" NOTNULL AND
4537 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
4538 THEN
4539 PERFORM "close_voting"("issue_id_p");
4540 -- calculate ranks will not consume much time and can be done now
4541 PERFORM "calculate_ranks"("issue_id_p");
4542 END IF;
4543 END IF;
4544 RETURN;
4545 END;
4546 $$;
4548 COMMENT ON FUNCTION "check_issue"
4549 ( "issue"."id"%TYPE )
4550 IS 'Precalculate supporter counts etc. for a given issue, and check, if status change is required; At end of voting the ranking is not calculated by this function, but must be calculated in a seperate transaction using the "calculate_ranks" function.';
4553 CREATE FUNCTION "check_everything"()
4554 RETURNS VOID
4555 LANGUAGE 'plpgsql' VOLATILE AS $$
4556 DECLARE
4557 "issue_id_v" "issue"."id"%TYPE;
4558 BEGIN
4559 DELETE FROM "expired_session";
4560 PERFORM "check_activity"();
4561 PERFORM "calculate_member_counts"();
4562 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
4563 PERFORM "check_issue"("issue_id_v");
4564 END LOOP;
4565 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
4566 PERFORM "calculate_ranks"("issue_id_v");
4567 END LOOP;
4568 RETURN;
4569 END;
4570 $$;
4572 COMMENT ON FUNCTION "check_everything"() IS 'Amongst other regular tasks this function performs "check_issue" for every open issue, and if possible, automatically calculates ranks. Use this function only for development and debugging purposes, as long transactions with exclusive locking may result. In productive environments you should call the lf_update program instead.';
4576 ----------------------
4577 -- Deletion of data --
4578 ----------------------
4581 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
4582 RETURNS VOID
4583 LANGUAGE 'plpgsql' VOLATILE AS $$
4584 DECLARE
4585 "issue_row" "issue"%ROWTYPE;
4586 BEGIN
4587 SELECT * INTO "issue_row"
4588 FROM "issue" WHERE "id" = "issue_id_p"
4589 FOR UPDATE;
4590 IF "issue_row"."cleaned" ISNULL THEN
4591 UPDATE "issue" SET
4592 "state" = 'voting',
4593 "closed" = NULL,
4594 "ranks_available" = FALSE
4595 WHERE "id" = "issue_id_p";
4596 DELETE FROM "delegating_voter"
4597 WHERE "issue_id" = "issue_id_p";
4598 DELETE FROM "direct_voter"
4599 WHERE "issue_id" = "issue_id_p";
4600 DELETE FROM "delegating_interest_snapshot"
4601 WHERE "issue_id" = "issue_id_p";
4602 DELETE FROM "direct_interest_snapshot"
4603 WHERE "issue_id" = "issue_id_p";
4604 DELETE FROM "delegating_population_snapshot"
4605 WHERE "issue_id" = "issue_id_p";
4606 DELETE FROM "direct_population_snapshot"
4607 WHERE "issue_id" = "issue_id_p";
4608 DELETE FROM "non_voter"
4609 WHERE "issue_id" = "issue_id_p";
4610 DELETE FROM "delegation"
4611 WHERE "issue_id" = "issue_id_p";
4612 DELETE FROM "supporter"
4613 WHERE "issue_id" = "issue_id_p";
4614 UPDATE "issue" SET
4615 "state" = "issue_row"."state",
4616 "closed" = "issue_row"."closed",
4617 "ranks_available" = "issue_row"."ranks_available",
4618 "cleaned" = now()
4619 WHERE "id" = "issue_id_p";
4620 END IF;
4621 RETURN;
4622 END;
4623 $$;
4625 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
4628 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
4629 RETURNS VOID
4630 LANGUAGE 'plpgsql' VOLATILE AS $$
4631 BEGIN
4632 UPDATE "member" SET
4633 "last_login" = NULL,
4634 "login" = NULL,
4635 "password" = NULL,
4636 "locked" = TRUE,
4637 "active" = FALSE,
4638 "notify_email" = NULL,
4639 "notify_email_unconfirmed" = NULL,
4640 "notify_email_secret" = NULL,
4641 "notify_email_secret_expiry" = NULL,
4642 "notify_email_lock_expiry" = NULL,
4643 "password_reset_secret" = NULL,
4644 "password_reset_secret_expiry" = NULL,
4645 "organizational_unit" = NULL,
4646 "internal_posts" = NULL,
4647 "realname" = NULL,
4648 "birthday" = NULL,
4649 "address" = NULL,
4650 "email" = NULL,
4651 "xmpp_address" = NULL,
4652 "website" = NULL,
4653 "phone" = NULL,
4654 "mobile_phone" = NULL,
4655 "profession" = NULL,
4656 "external_memberships" = NULL,
4657 "external_posts" = NULL,
4658 "statement" = NULL
4659 WHERE "id" = "member_id_p";
4660 -- "text_search_data" is updated by triggers
4661 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
4662 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
4663 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
4664 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
4665 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
4666 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
4667 DELETE FROM "session" WHERE "member_id" = "member_id_p";
4668 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
4669 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
4670 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
4671 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
4672 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
4673 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
4674 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
4675 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
4676 DELETE FROM "direct_voter" USING "issue"
4677 WHERE "direct_voter"."issue_id" = "issue"."id"
4678 AND "issue"."closed" ISNULL
4679 AND "member_id" = "member_id_p";
4680 RETURN;
4681 END;
4682 $$;
4684 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)';
4687 CREATE FUNCTION "delete_private_data"()
4688 RETURNS VOID
4689 LANGUAGE 'plpgsql' VOLATILE AS $$
4690 BEGIN
4691 DELETE FROM "member" WHERE "activated" ISNULL;
4692 UPDATE "member" SET
4693 "invite_code" = NULL,
4694 "invite_code_expiry" = NULL,
4695 "admin_comment" = NULL,
4696 "last_login" = NULL,
4697 "login" = NULL,
4698 "password" = NULL,
4699 "lang" = NULL,
4700 "notify_email" = NULL,
4701 "notify_email_unconfirmed" = NULL,
4702 "notify_email_secret" = NULL,
4703 "notify_email_secret_expiry" = NULL,
4704 "notify_email_lock_expiry" = NULL,
4705 "notify_level" = NULL,
4706 "password_reset_secret" = NULL,
4707 "password_reset_secret_expiry" = NULL,
4708 "organizational_unit" = NULL,
4709 "internal_posts" = NULL,
4710 "realname" = NULL,
4711 "birthday" = NULL,
4712 "address" = NULL,
4713 "email" = NULL,
4714 "xmpp_address" = NULL,
4715 "website" = NULL,
4716 "phone" = NULL,
4717 "mobile_phone" = NULL,
4718 "profession" = NULL,
4719 "external_memberships" = NULL,
4720 "external_posts" = NULL,
4721 "formatting_engine" = NULL,
4722 "statement" = NULL;
4723 -- "text_search_data" is updated by triggers
4724 DELETE FROM "setting";
4725 DELETE FROM "setting_map";
4726 DELETE FROM "member_relation_setting";
4727 DELETE FROM "member_image";
4728 DELETE FROM "contact";
4729 DELETE FROM "ignored_member";
4730 DELETE FROM "session";
4731 DELETE FROM "area_setting";
4732 DELETE FROM "issue_setting";
4733 DELETE FROM "ignored_initiative";
4734 DELETE FROM "initiative_setting";
4735 DELETE FROM "suggestion_setting";
4736 DELETE FROM "non_voter";
4737 DELETE FROM "direct_voter" USING "issue"
4738 WHERE "direct_voter"."issue_id" = "issue"."id"
4739 AND "issue"."closed" ISNULL;
4740 RETURN;
4741 END;
4742 $$;
4744 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.';
4748 COMMIT;