liquid_feedback_core
view core.sql @ 316:727926e290e7
Set "harmonic_weight" to zero instead of null, where there are no supporters
| author | jbe |
|---|---|
| date | Sat Feb 02 20:21:16 2013 +0100 (2013-02-02) |
| parents | 5289d62c028c |
| children | 09e2805decd3 |
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';
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 "initiative"."harmonic_weight" ISNULL
3075 GROUP BY
3076 "direct_interest_snapshot"."issue_id",
3077 "direct_interest_snapshot"."event",
3078 "direct_interest_snapshot"."member_id",
3079 "direct_interest_snapshot"."weight";
3081 COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';
3084 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
3085 SELECT
3086 "initiative"."issue_id",
3087 "initiative"."id" AS "initiative_id",
3088 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
3089 "remaining_harmonic_supporter_weight"."weight_den"
3090 FROM "remaining_harmonic_supporter_weight"
3091 JOIN "direct_supporter_snapshot"
3092 ON "remaining_harmonic_supporter_weight"."issue_id" = "direct_supporter_snapshot"."issue_id"
3093 AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event"
3094 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
3095 JOIN "initiative"
3096 ON "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
3097 AND "initiative"."harmonic_weight" ISNULL
3098 GROUP BY
3099 "initiative"."issue_id",
3100 "initiative"."id",
3101 "remaining_harmonic_supporter_weight"."weight_den";
3103 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
3106 CREATE FUNCTION "set_harmonic_initiative_weights"
3107 ( "issue_id_p" "issue"."id"%TYPE )
3108 RETURNS VOID
3109 LANGUAGE 'plpgsql' VOLATILE AS $$
3110 DECLARE
3111 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;
3112 "i" INT4;
3113 "count_v" INT4;
3114 "summand_v" FLOAT;
3115 "id_ary" INT4[];
3116 "weight_ary" FLOAT[];
3117 "min_weight_v" FLOAT;
3118 BEGIN
3119 UPDATE "initiative" SET "harmonic_weight" = NULL
3120 WHERE "issue_id" = "issue_id_p";
3121 LOOP
3122 "min_weight_v" := NULL;
3123 "i" := 0;
3124 "count_v" := 0;
3125 FOR "weight_row" IN
3126 SELECT * FROM "remaining_harmonic_initiative_weight_summands"
3127 WHERE "issue_id" = "issue_id_p"
3128 ORDER BY "initiative_id" DESC, "weight_den" DESC
3129 -- NOTE: latest initiatives treated worse
3130 LOOP
3131 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
3132 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
3133 "i" := "i" + 1;
3134 "count_v" := "i";
3135 "id_ary"["i"] := "weight_row"."initiative_id";
3136 "weight_ary"["i"] := "summand_v";
3137 ELSE
3138 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
3139 END IF;
3140 END LOOP;
3141 EXIT WHEN "count_v" = 0;
3142 "i" := 1;
3143 LOOP
3144 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
3145 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
3146 "min_weight_v" := "weight_ary"["i"];
3147 END IF;
3148 "i" := "i" + 1;
3149 EXIT WHEN "i" > "count_v";
3150 END LOOP;
3151 "i" := 1;
3152 LOOP
3153 IF "weight_ary"["i"] = "min_weight_v" THEN
3154 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"
3155 WHERE "id" = "id_ary"["i"];
3156 EXIT;
3157 END IF;
3158 "i" := "i" + 1;
3159 END LOOP;
3160 END LOOP;
3161 UPDATE "initiative" SET "harmonic_weight" = 0
3162 WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;
3163 END;
3164 $$;
3166 COMMENT ON FUNCTION "set_harmonic_initiative_weights"
3167 ( "issue"."id"%TYPE )
3168 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
3171 CREATE VIEW "remaining_harmonic_opinion_weight" AS
3172 SELECT
3173 "initiative"."issue_id",
3174 "opinion"."initiative_id",
3175 "direct_interest_snapshot"."member_id",
3176 "direct_interest_snapshot"."weight" AS "weight_num",
3177 count("opinion"."suggestion_id") AS "weight_den"
3178 FROM "issue"
3179 JOIN "direct_interest_snapshot"
3180 ON "issue"."id" = "direct_interest_snapshot"."issue_id"
3181 AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
3182 JOIN "initiative"
3183 ON "direct_interest_snapshot"."issue_id" = "initiative"."issue_id"
3184 JOIN "opinion"
3185 ON "direct_interest_snapshot"."member_id" = "opinion"."member_id"
3186 AND "initiative"."id" = "opinion"."initiative_id"
3187 AND "opinion"."degree" > 0
3188 GROUP BY
3189 "initiative"."issue_id",
3190 "opinion"."initiative_id",
3191 "direct_interest_snapshot"."member_id",
3192 "direct_interest_snapshot"."weight";
3194 COMMENT ON VIEW "remaining_harmonic_opinion_weight" IS 'Helper view for function "set_harmonic_suggestion_weights"';
3197 CREATE VIEW "remaining_harmonic_suggestion_weight_summands" AS
3198 SELECT
3199 "suggestion"."initiative_id",
3200 "opinion"."suggestion_id",
3201 sum("remaining_harmonic_opinion_weight"."weight_num") AS "weight_num",
3202 "remaining_harmonic_opinion_weight"."weight_den"
3203 FROM "remaining_harmonic_opinion_weight"
3204 JOIN "opinion"
3205 ON "remaining_harmonic_opinion_weight"."initiative_id" = "opinion"."initiative_id"
3206 AND "remaining_harmonic_opinion_weight"."member_id" = "opinion"."member_id"
3207 JOIN "suggestion"
3208 ON "opinion"."suggestion_id" = "suggestion"."id"
3209 AND "suggestion"."harmonic_weight" ISNULL
3210 GROUP BY
3211 "suggestion"."initiative_id",
3212 "opinion"."suggestion_id",
3213 "remaining_harmonic_opinion_weight"."weight_den";
3215 COMMENT ON VIEW "remaining_harmonic_suggestion_weight_summands" IS 'Helper view for function "set_harmonic_suggestion_weights"';
3218 CREATE FUNCTION "set_harmonic_suggestion_weights"
3219 ( "initiative_id_p" "initiative"."id"%TYPE )
3220 RETURNS VOID
3221 LANGUAGE 'plpgsql' VOLATILE AS $$
3222 DECLARE
3223 "weight_row" "remaining_harmonic_suggestion_weight_summands"%ROWTYPE;
3224 "i" INT4;
3225 "count_v" INT4;
3226 "summand_v" FLOAT;
3227 "id_ary" INT4[];
3228 "weight_ary" FLOAT[];
3229 "min_weight_v" FLOAT;
3230 BEGIN
3231 UPDATE "suggestion" SET "harmonic_weight" = NULL
3232 WHERE "initiative_id" = "initiative_id_p";
3233 LOOP
3234 "min_weight_v" := NULL;
3235 "i" := 0;
3236 "count_v" := 0;
3237 FOR "weight_row" IN
3238 SELECT * FROM "remaining_harmonic_suggestion_weight_summands"
3239 WHERE "initiative_id" = "initiative_id_p"
3240 ORDER BY "suggestion_id" DESC, "weight_den" DESC
3241 -- NOTE: latest suggestions treated worse
3242 LOOP
3243 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
3244 IF "i" = 0 OR "weight_row"."suggestion_id" != "id_ary"["i"] THEN
3245 "i" := "i" + 1;
3246 "count_v" := "i";
3247 "id_ary"["i"] := "weight_row"."suggestion_id";
3248 "weight_ary"["i"] := "summand_v";
3249 ELSE
3250 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
3251 END IF;
3252 END LOOP;
3253 EXIT WHEN "count_v" = 0;
3254 "i" := 1;
3255 LOOP
3256 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
3257 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
3258 "min_weight_v" := "weight_ary"["i"];
3259 END IF;
3260 "i" := "i" + 1;
3261 EXIT WHEN "i" > "count_v";
3262 END LOOP;
3263 "i" := 1;
3264 LOOP
3265 IF "weight_ary"["i"] = "min_weight_v" THEN
3266 UPDATE "suggestion" SET "harmonic_weight" = "min_weight_v"
3267 WHERE "id" = "id_ary"["i"];
3268 EXIT;
3269 END IF;
3270 "i" := "i" + 1;
3271 END LOOP;
3272 END LOOP;
3273 UPDATE "suggestion" SET "harmonic_weight" = 0
3274 WHERE "initiative_id" = "initiative_id_p" AND "harmonic_weight" ISNULL;
3275 END;
3276 $$;
3278 COMMENT ON FUNCTION "set_harmonic_suggestion_weights"
3279 ( "issue"."id"%TYPE )
3280 IS 'Calculates and sets "harmonic_weight" of suggestions in a given initiative';
3283 ------------------------------
3284 -- Calculation of snapshots --
3285 ------------------------------
3288 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
3289 ( "issue_id_p" "issue"."id"%TYPE,
3290 "member_id_p" "member"."id"%TYPE,
3291 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3292 RETURNS "direct_population_snapshot"."weight"%TYPE
3293 LANGUAGE 'plpgsql' VOLATILE AS $$
3294 DECLARE
3295 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3296 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
3297 "weight_v" INT4;
3298 "sub_weight_v" INT4;
3299 BEGIN
3300 "weight_v" := 0;
3301 FOR "issue_delegation_row" IN
3302 SELECT * FROM "issue_delegation"
3303 WHERE "trustee_id" = "member_id_p"
3304 AND "issue_id" = "issue_id_p"
3305 LOOP
3306 IF NOT EXISTS (
3307 SELECT NULL FROM "direct_population_snapshot"
3308 WHERE "issue_id" = "issue_id_p"
3309 AND "event" = 'periodic'
3310 AND "member_id" = "issue_delegation_row"."truster_id"
3311 ) AND NOT EXISTS (
3312 SELECT NULL FROM "delegating_population_snapshot"
3313 WHERE "issue_id" = "issue_id_p"
3314 AND "event" = 'periodic'
3315 AND "member_id" = "issue_delegation_row"."truster_id"
3316 ) THEN
3317 "delegate_member_ids_v" :=
3318 "member_id_p" || "delegate_member_ids_p";
3319 INSERT INTO "delegating_population_snapshot" (
3320 "issue_id",
3321 "event",
3322 "member_id",
3323 "scope",
3324 "delegate_member_ids"
3325 ) VALUES (
3326 "issue_id_p",
3327 'periodic',
3328 "issue_delegation_row"."truster_id",
3329 "issue_delegation_row"."scope",
3330 "delegate_member_ids_v"
3331 );
3332 "sub_weight_v" := 1 +
3333 "weight_of_added_delegations_for_population_snapshot"(
3334 "issue_id_p",
3335 "issue_delegation_row"."truster_id",
3336 "delegate_member_ids_v"
3337 );
3338 UPDATE "delegating_population_snapshot"
3339 SET "weight" = "sub_weight_v"
3340 WHERE "issue_id" = "issue_id_p"
3341 AND "event" = 'periodic'
3342 AND "member_id" = "issue_delegation_row"."truster_id";
3343 "weight_v" := "weight_v" + "sub_weight_v";
3344 END IF;
3345 END LOOP;
3346 RETURN "weight_v";
3347 END;
3348 $$;
3350 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
3351 ( "issue"."id"%TYPE,
3352 "member"."id"%TYPE,
3353 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3354 IS 'Helper function for "create_population_snapshot" function';
3357 CREATE FUNCTION "create_population_snapshot"
3358 ( "issue_id_p" "issue"."id"%TYPE )
3359 RETURNS VOID
3360 LANGUAGE 'plpgsql' VOLATILE AS $$
3361 DECLARE
3362 "member_id_v" "member"."id"%TYPE;
3363 BEGIN
3364 DELETE FROM "direct_population_snapshot"
3365 WHERE "issue_id" = "issue_id_p"
3366 AND "event" = 'periodic';
3367 DELETE FROM "delegating_population_snapshot"
3368 WHERE "issue_id" = "issue_id_p"
3369 AND "event" = 'periodic';
3370 INSERT INTO "direct_population_snapshot"
3371 ("issue_id", "event", "member_id")
3372 SELECT
3373 "issue_id_p" AS "issue_id",
3374 'periodic'::"snapshot_event" AS "event",
3375 "member"."id" AS "member_id"
3376 FROM "issue"
3377 JOIN "area" ON "issue"."area_id" = "area"."id"
3378 JOIN "membership" ON "area"."id" = "membership"."area_id"
3379 JOIN "member" ON "membership"."member_id" = "member"."id"
3380 JOIN "privilege"
3381 ON "privilege"."unit_id" = "area"."unit_id"
3382 AND "privilege"."member_id" = "member"."id"
3383 WHERE "issue"."id" = "issue_id_p"
3384 AND "member"."active" AND "privilege"."voting_right"
3385 UNION
3386 SELECT
3387 "issue_id_p" AS "issue_id",
3388 'periodic'::"snapshot_event" AS "event",
3389 "member"."id" AS "member_id"
3390 FROM "issue"
3391 JOIN "area" ON "issue"."area_id" = "area"."id"
3392 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3393 JOIN "member" ON "interest"."member_id" = "member"."id"
3394 JOIN "privilege"
3395 ON "privilege"."unit_id" = "area"."unit_id"
3396 AND "privilege"."member_id" = "member"."id"
3397 WHERE "issue"."id" = "issue_id_p"
3398 AND "member"."active" AND "privilege"."voting_right";
3399 FOR "member_id_v" IN
3400 SELECT "member_id" FROM "direct_population_snapshot"
3401 WHERE "issue_id" = "issue_id_p"
3402 AND "event" = 'periodic'
3403 LOOP
3404 UPDATE "direct_population_snapshot" SET
3405 "weight" = 1 +
3406 "weight_of_added_delegations_for_population_snapshot"(
3407 "issue_id_p",
3408 "member_id_v",
3409 '{}'
3410 )
3411 WHERE "issue_id" = "issue_id_p"
3412 AND "event" = 'periodic'
3413 AND "member_id" = "member_id_v";
3414 END LOOP;
3415 RETURN;
3416 END;
3417 $$;
3419 COMMENT ON FUNCTION "create_population_snapshot"
3420 ( "issue"."id"%TYPE )
3421 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.';
3424 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3425 ( "issue_id_p" "issue"."id"%TYPE,
3426 "member_id_p" "member"."id"%TYPE,
3427 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3428 RETURNS "direct_interest_snapshot"."weight"%TYPE
3429 LANGUAGE 'plpgsql' VOLATILE AS $$
3430 DECLARE
3431 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3432 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
3433 "weight_v" INT4;
3434 "sub_weight_v" INT4;
3435 BEGIN
3436 "weight_v" := 0;
3437 FOR "issue_delegation_row" IN
3438 SELECT * FROM "issue_delegation"
3439 WHERE "trustee_id" = "member_id_p"
3440 AND "issue_id" = "issue_id_p"
3441 LOOP
3442 IF NOT EXISTS (
3443 SELECT NULL FROM "direct_interest_snapshot"
3444 WHERE "issue_id" = "issue_id_p"
3445 AND "event" = 'periodic'
3446 AND "member_id" = "issue_delegation_row"."truster_id"
3447 ) AND NOT EXISTS (
3448 SELECT NULL FROM "delegating_interest_snapshot"
3449 WHERE "issue_id" = "issue_id_p"
3450 AND "event" = 'periodic'
3451 AND "member_id" = "issue_delegation_row"."truster_id"
3452 ) THEN
3453 "delegate_member_ids_v" :=
3454 "member_id_p" || "delegate_member_ids_p";
3455 INSERT INTO "delegating_interest_snapshot" (
3456 "issue_id",
3457 "event",
3458 "member_id",
3459 "scope",
3460 "delegate_member_ids"
3461 ) VALUES (
3462 "issue_id_p",
3463 'periodic',
3464 "issue_delegation_row"."truster_id",
3465 "issue_delegation_row"."scope",
3466 "delegate_member_ids_v"
3467 );
3468 "sub_weight_v" := 1 +
3469 "weight_of_added_delegations_for_interest_snapshot"(
3470 "issue_id_p",
3471 "issue_delegation_row"."truster_id",
3472 "delegate_member_ids_v"
3473 );
3474 UPDATE "delegating_interest_snapshot"
3475 SET "weight" = "sub_weight_v"
3476 WHERE "issue_id" = "issue_id_p"
3477 AND "event" = 'periodic'
3478 AND "member_id" = "issue_delegation_row"."truster_id";
3479 "weight_v" := "weight_v" + "sub_weight_v";
3480 END IF;
3481 END LOOP;
3482 RETURN "weight_v";
3483 END;
3484 $$;
3486 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3487 ( "issue"."id"%TYPE,
3488 "member"."id"%TYPE,
3489 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3490 IS 'Helper function for "create_interest_snapshot" function';
3493 CREATE FUNCTION "create_interest_snapshot"
3494 ( "issue_id_p" "issue"."id"%TYPE )
3495 RETURNS VOID
3496 LANGUAGE 'plpgsql' VOLATILE AS $$
3497 DECLARE
3498 "member_id_v" "member"."id"%TYPE;
3499 BEGIN
3500 DELETE FROM "direct_interest_snapshot"
3501 WHERE "issue_id" = "issue_id_p"
3502 AND "event" = 'periodic';
3503 DELETE FROM "delegating_interest_snapshot"
3504 WHERE "issue_id" = "issue_id_p"
3505 AND "event" = 'periodic';
3506 DELETE FROM "direct_supporter_snapshot"
3507 WHERE "issue_id" = "issue_id_p"
3508 AND "event" = 'periodic';
3509 INSERT INTO "direct_interest_snapshot"
3510 ("issue_id", "event", "member_id")
3511 SELECT
3512 "issue_id_p" AS "issue_id",
3513 'periodic' AS "event",
3514 "member"."id" AS "member_id"
3515 FROM "issue"
3516 JOIN "area" ON "issue"."area_id" = "area"."id"
3517 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3518 JOIN "member" ON "interest"."member_id" = "member"."id"
3519 JOIN "privilege"
3520 ON "privilege"."unit_id" = "area"."unit_id"
3521 AND "privilege"."member_id" = "member"."id"
3522 WHERE "issue"."id" = "issue_id_p"
3523 AND "member"."active" AND "privilege"."voting_right";
3524 FOR "member_id_v" IN
3525 SELECT "member_id" FROM "direct_interest_snapshot"
3526 WHERE "issue_id" = "issue_id_p"
3527 AND "event" = 'periodic'
3528 LOOP
3529 UPDATE "direct_interest_snapshot" SET
3530 "weight" = 1 +
3531 "weight_of_added_delegations_for_interest_snapshot"(
3532 "issue_id_p",
3533 "member_id_v",
3534 '{}'
3535 )
3536 WHERE "issue_id" = "issue_id_p"
3537 AND "event" = 'periodic'
3538 AND "member_id" = "member_id_v";
3539 END LOOP;
3540 INSERT INTO "direct_supporter_snapshot"
3541 ( "issue_id", "initiative_id", "event", "member_id",
3542 "draft_id", "informed", "satisfied" )
3543 SELECT
3544 "issue_id_p" AS "issue_id",
3545 "initiative"."id" AS "initiative_id",
3546 'periodic' AS "event",
3547 "supporter"."member_id" AS "member_id",
3548 "supporter"."draft_id" AS "draft_id",
3549 "supporter"."draft_id" = "current_draft"."id" AS "informed",
3550 NOT EXISTS (
3551 SELECT NULL FROM "critical_opinion"
3552 WHERE "initiative_id" = "initiative"."id"
3553 AND "member_id" = "supporter"."member_id"
3554 ) AS "satisfied"
3555 FROM "initiative"
3556 JOIN "supporter"
3557 ON "supporter"."initiative_id" = "initiative"."id"
3558 JOIN "current_draft"
3559 ON "initiative"."id" = "current_draft"."initiative_id"
3560 JOIN "direct_interest_snapshot"
3561 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
3562 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
3563 AND "event" = 'periodic'
3564 WHERE "initiative"."issue_id" = "issue_id_p";
3565 RETURN;
3566 END;
3567 $$;
3569 COMMENT ON FUNCTION "create_interest_snapshot"
3570 ( "issue"."id"%TYPE )
3571 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.';
3574 CREATE FUNCTION "create_snapshot"
3575 ( "issue_id_p" "issue"."id"%TYPE )
3576 RETURNS VOID
3577 LANGUAGE 'plpgsql' VOLATILE AS $$
3578 DECLARE
3579 "initiative_id_v" "initiative"."id"%TYPE;
3580 "suggestion_id_v" "suggestion"."id"%TYPE;
3581 BEGIN
3582 PERFORM "lock_issue"("issue_id_p");
3583 PERFORM "create_population_snapshot"("issue_id_p");
3584 PERFORM "create_interest_snapshot"("issue_id_p");
3585 UPDATE "issue" SET
3586 "snapshot" = now(),
3587 "latest_snapshot_event" = 'periodic',
3588 "population" = (
3589 SELECT coalesce(sum("weight"), 0)
3590 FROM "direct_population_snapshot"
3591 WHERE "issue_id" = "issue_id_p"
3592 AND "event" = 'periodic'
3593 )
3594 WHERE "id" = "issue_id_p";
3595 FOR "initiative_id_v" IN
3596 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
3597 LOOP
3598 UPDATE "initiative" SET
3599 "supporter_count" = (
3600 SELECT coalesce(sum("di"."weight"), 0)
3601 FROM "direct_interest_snapshot" AS "di"
3602 JOIN "direct_supporter_snapshot" AS "ds"
3603 ON "di"."member_id" = "ds"."member_id"
3604 WHERE "di"."issue_id" = "issue_id_p"
3605 AND "di"."event" = 'periodic'
3606 AND "ds"."initiative_id" = "initiative_id_v"
3607 AND "ds"."event" = 'periodic'
3608 ),
3609 "informed_supporter_count" = (
3610 SELECT coalesce(sum("di"."weight"), 0)
3611 FROM "direct_interest_snapshot" AS "di"
3612 JOIN "direct_supporter_snapshot" AS "ds"
3613 ON "di"."member_id" = "ds"."member_id"
3614 WHERE "di"."issue_id" = "issue_id_p"
3615 AND "di"."event" = 'periodic'
3616 AND "ds"."initiative_id" = "initiative_id_v"
3617 AND "ds"."event" = 'periodic'
3618 AND "ds"."informed"
3619 ),
3620 "satisfied_supporter_count" = (
3621 SELECT coalesce(sum("di"."weight"), 0)
3622 FROM "direct_interest_snapshot" AS "di"
3623 JOIN "direct_supporter_snapshot" AS "ds"
3624 ON "di"."member_id" = "ds"."member_id"
3625 WHERE "di"."issue_id" = "issue_id_p"
3626 AND "di"."event" = 'periodic'
3627 AND "ds"."initiative_id" = "initiative_id_v"
3628 AND "ds"."event" = 'periodic'
3629 AND "ds"."satisfied"
3630 ),
3631 "satisfied_informed_supporter_count" = (
3632 SELECT coalesce(sum("di"."weight"), 0)
3633 FROM "direct_interest_snapshot" AS "di"
3634 JOIN "direct_supporter_snapshot" AS "ds"
3635 ON "di"."member_id" = "ds"."member_id"
3636 WHERE "di"."issue_id" = "issue_id_p"
3637 AND "di"."event" = 'periodic'
3638 AND "ds"."initiative_id" = "initiative_id_v"
3639 AND "ds"."event" = 'periodic'
3640 AND "ds"."informed"
3641 AND "ds"."satisfied"
3642 )
3643 WHERE "id" = "initiative_id_v";
3644 FOR "suggestion_id_v" IN
3645 SELECT "id" FROM "suggestion"
3646 WHERE "initiative_id" = "initiative_id_v"
3647 LOOP
3648 UPDATE "suggestion" SET
3649 "minus2_unfulfilled_count" = (
3650 SELECT coalesce(sum("snapshot"."weight"), 0)
3651 FROM "issue" CROSS JOIN "opinion"
3652 JOIN "direct_interest_snapshot" AS "snapshot"
3653 ON "snapshot"."issue_id" = "issue"."id"
3654 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3655 AND "snapshot"."member_id" = "opinion"."member_id"
3656 WHERE "issue"."id" = "issue_id_p"
3657 AND "opinion"."suggestion_id" = "suggestion_id_v"
3658 AND "opinion"."degree" = -2
3659 AND "opinion"."fulfilled" = FALSE
3660 ),
3661 "minus2_fulfilled_count" = (
3662 SELECT coalesce(sum("snapshot"."weight"), 0)
3663 FROM "issue" CROSS JOIN "opinion"
3664 JOIN "direct_interest_snapshot" AS "snapshot"
3665 ON "snapshot"."issue_id" = "issue"."id"
3666 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3667 AND "snapshot"."member_id" = "opinion"."member_id"
3668 WHERE "issue"."id" = "issue_id_p"
3669 AND "opinion"."suggestion_id" = "suggestion_id_v"
3670 AND "opinion"."degree" = -2
3671 AND "opinion"."fulfilled" = TRUE
3672 ),
3673 "minus1_unfulfilled_count" = (
3674 SELECT coalesce(sum("snapshot"."weight"), 0)
3675 FROM "issue" CROSS JOIN "opinion"
3676 JOIN "direct_interest_snapshot" AS "snapshot"
3677 ON "snapshot"."issue_id" = "issue"."id"
3678 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3679 AND "snapshot"."member_id" = "opinion"."member_id"
3680 WHERE "issue"."id" = "issue_id_p"
3681 AND "opinion"."suggestion_id" = "suggestion_id_v"
3682 AND "opinion"."degree" = -1
3683 AND "opinion"."fulfilled" = FALSE
3684 ),
3685 "minus1_fulfilled_count" = (
3686 SELECT coalesce(sum("snapshot"."weight"), 0)
3687 FROM "issue" CROSS JOIN "opinion"
3688 JOIN "direct_interest_snapshot" AS "snapshot"
3689 ON "snapshot"."issue_id" = "issue"."id"
3690 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3691 AND "snapshot"."member_id" = "opinion"."member_id"
3692 WHERE "issue"."id" = "issue_id_p"
3693 AND "opinion"."suggestion_id" = "suggestion_id_v"
3694 AND "opinion"."degree" = -1
3695 AND "opinion"."fulfilled" = TRUE
3696 ),
3697 "plus1_unfulfilled_count" = (
3698 SELECT coalesce(sum("snapshot"."weight"), 0)
3699 FROM "issue" CROSS JOIN "opinion"
3700 JOIN "direct_interest_snapshot" AS "snapshot"
3701 ON "snapshot"."issue_id" = "issue"."id"
3702 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3703 AND "snapshot"."member_id" = "opinion"."member_id"
3704 WHERE "issue"."id" = "issue_id_p"
3705 AND "opinion"."suggestion_id" = "suggestion_id_v"
3706 AND "opinion"."degree" = 1
3707 AND "opinion"."fulfilled" = FALSE
3708 ),
3709 "plus1_fulfilled_count" = (
3710 SELECT coalesce(sum("snapshot"."weight"), 0)
3711 FROM "issue" CROSS JOIN "opinion"
3712 JOIN "direct_interest_snapshot" AS "snapshot"
3713 ON "snapshot"."issue_id" = "issue"."id"
3714 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3715 AND "snapshot"."member_id" = "opinion"."member_id"
3716 WHERE "issue"."id" = "issue_id_p"
3717 AND "opinion"."suggestion_id" = "suggestion_id_v"
3718 AND "opinion"."degree" = 1
3719 AND "opinion"."fulfilled" = TRUE
3720 ),
3721 "plus2_unfulfilled_count" = (
3722 SELECT coalesce(sum("snapshot"."weight"), 0)
3723 FROM "issue" CROSS JOIN "opinion"
3724 JOIN "direct_interest_snapshot" AS "snapshot"
3725 ON "snapshot"."issue_id" = "issue"."id"
3726 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3727 AND "snapshot"."member_id" = "opinion"."member_id"
3728 WHERE "issue"."id" = "issue_id_p"
3729 AND "opinion"."suggestion_id" = "suggestion_id_v"
3730 AND "opinion"."degree" = 2
3731 AND "opinion"."fulfilled" = FALSE
3732 ),
3733 "plus2_fulfilled_count" = (
3734 SELECT coalesce(sum("snapshot"."weight"), 0)
3735 FROM "issue" CROSS JOIN "opinion"
3736 JOIN "direct_interest_snapshot" AS "snapshot"
3737 ON "snapshot"."issue_id" = "issue"."id"
3738 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3739 AND "snapshot"."member_id" = "opinion"."member_id"
3740 WHERE "issue"."id" = "issue_id_p"
3741 AND "opinion"."suggestion_id" = "suggestion_id_v"
3742 AND "opinion"."degree" = 2
3743 AND "opinion"."fulfilled" = TRUE
3744 )
3745 WHERE "suggestion"."id" = "suggestion_id_v";
3746 END LOOP;
3747 PERFORM "set_harmonic_suggestion_weights"("initiative_id_v");
3748 END LOOP;
3749 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
3750 RETURN;
3751 END;
3752 $$;
3754 COMMENT ON FUNCTION "create_snapshot"
3755 ( "issue"."id"%TYPE )
3756 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.';
3759 CREATE FUNCTION "set_snapshot_event"
3760 ( "issue_id_p" "issue"."id"%TYPE,
3761 "event_p" "snapshot_event" )
3762 RETURNS VOID
3763 LANGUAGE 'plpgsql' VOLATILE AS $$
3764 DECLARE
3765 "event_v" "issue"."latest_snapshot_event"%TYPE;
3766 BEGIN
3767 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
3768 WHERE "id" = "issue_id_p" FOR UPDATE;
3769 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
3770 WHERE "id" = "issue_id_p";
3771 UPDATE "direct_population_snapshot" SET "event" = "event_p"
3772 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3773 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
3774 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3775 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
3776 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3777 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
3778 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3779 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
3780 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3781 RETURN;
3782 END;
3783 $$;
3785 COMMENT ON FUNCTION "set_snapshot_event"
3786 ( "issue"."id"%TYPE,
3787 "snapshot_event" )
3788 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
3792 ---------------------
3793 -- Freezing issues --
3794 ---------------------
3796 CREATE FUNCTION "freeze_after_snapshot"
3797 ( "issue_id_p" "issue"."id"%TYPE )
3798 RETURNS VOID
3799 LANGUAGE 'plpgsql' VOLATILE AS $$
3800 DECLARE
3801 "issue_row" "issue"%ROWTYPE;
3802 "policy_row" "policy"%ROWTYPE;
3803 "initiative_row" "initiative"%ROWTYPE;
3804 BEGIN
3805 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3806 SELECT * INTO "policy_row"
3807 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3808 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
3809 FOR "initiative_row" IN
3810 SELECT * FROM "initiative"
3811 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3812 LOOP
3813 IF
3814 "initiative_row"."polling" OR (
3815 "initiative_row"."satisfied_supporter_count" > 0 AND
3816 "initiative_row"."satisfied_supporter_count" *
3817 "policy_row"."initiative_quorum_den" >=
3818 "issue_row"."population" * "policy_row"."initiative_quorum_num"
3819 )
3820 THEN
3821 UPDATE "initiative" SET "admitted" = TRUE
3822 WHERE "id" = "initiative_row"."id";
3823 ELSE
3824 UPDATE "initiative" SET "admitted" = FALSE
3825 WHERE "id" = "initiative_row"."id";
3826 END IF;
3827 END LOOP;
3828 IF EXISTS (
3829 SELECT NULL FROM "initiative"
3830 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
3831 ) THEN
3832 UPDATE "issue" SET
3833 "state" = 'voting',
3834 "accepted" = coalesce("accepted", now()),
3835 "half_frozen" = coalesce("half_frozen", now()),
3836 "fully_frozen" = now()
3837 WHERE "id" = "issue_id_p";
3838 ELSE
3839 UPDATE "issue" SET
3840 "state" = 'canceled_no_initiative_admitted',
3841 "accepted" = coalesce("accepted", now()),
3842 "half_frozen" = coalesce("half_frozen", now()),
3843 "fully_frozen" = now(),
3844 "closed" = now(),
3845 "ranks_available" = TRUE
3846 WHERE "id" = "issue_id_p";
3847 -- NOTE: The following DELETE statements have effect only when
3848 -- issue state has been manipulated
3849 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
3850 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
3851 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3852 END IF;
3853 RETURN;
3854 END;
3855 $$;
3857 COMMENT ON FUNCTION "freeze_after_snapshot"
3858 ( "issue"."id"%TYPE )
3859 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
3862 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
3863 RETURNS VOID
3864 LANGUAGE 'plpgsql' VOLATILE AS $$
3865 DECLARE
3866 "issue_row" "issue"%ROWTYPE;
3867 BEGIN
3868 PERFORM "create_snapshot"("issue_id_p");
3869 PERFORM "freeze_after_snapshot"("issue_id_p");
3870 RETURN;
3871 END;
3872 $$;
3874 COMMENT ON FUNCTION "manual_freeze"
3875 ( "issue"."id"%TYPE )
3876 IS 'Freeze an issue manually (fully) and start voting';
3880 -----------------------
3881 -- Counting of votes --
3882 -----------------------
3885 CREATE FUNCTION "weight_of_added_vote_delegations"
3886 ( "issue_id_p" "issue"."id"%TYPE,
3887 "member_id_p" "member"."id"%TYPE,
3888 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
3889 RETURNS "direct_voter"."weight"%TYPE
3890 LANGUAGE 'plpgsql' VOLATILE AS $$
3891 DECLARE
3892 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3893 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
3894 "weight_v" INT4;
3895 "sub_weight_v" INT4;
3896 BEGIN
3897 "weight_v" := 0;
3898 FOR "issue_delegation_row" IN
3899 SELECT * FROM "issue_delegation"
3900 WHERE "trustee_id" = "member_id_p"
3901 AND "issue_id" = "issue_id_p"
3902 LOOP
3903 IF NOT EXISTS (
3904 SELECT NULL FROM "direct_voter"
3905 WHERE "member_id" = "issue_delegation_row"."truster_id"
3906 AND "issue_id" = "issue_id_p"
3907 ) AND NOT EXISTS (
3908 SELECT NULL FROM "delegating_voter"
3909 WHERE "member_id" = "issue_delegation_row"."truster_id"
3910 AND "issue_id" = "issue_id_p"
3911 ) THEN
3912 "delegate_member_ids_v" :=
3913 "member_id_p" || "delegate_member_ids_p";
3914 INSERT INTO "delegating_voter" (
3915 "issue_id",
3916 "member_id",
3917 "scope",
3918 "delegate_member_ids"
3919 ) VALUES (
3920 "issue_id_p",
3921 "issue_delegation_row"."truster_id",
3922 "issue_delegation_row"."scope",
3923 "delegate_member_ids_v"
3924 );
3925 "sub_weight_v" := 1 +
3926 "weight_of_added_vote_delegations"(
3927 "issue_id_p",
3928 "issue_delegation_row"."truster_id",
3929 "delegate_member_ids_v"
3930 );
3931 UPDATE "delegating_voter"
3932 SET "weight" = "sub_weight_v"
3933 WHERE "issue_id" = "issue_id_p"
3934 AND "member_id" = "issue_delegation_row"."truster_id";
3935 "weight_v" := "weight_v" + "sub_weight_v";
3936 END IF;
3937 END LOOP;
3938 RETURN "weight_v";
3939 END;
3940 $$;
3942 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
3943 ( "issue"."id"%TYPE,
3944 "member"."id"%TYPE,
3945 "delegating_voter"."delegate_member_ids"%TYPE )
3946 IS 'Helper function for "add_vote_delegations" function';
3949 CREATE FUNCTION "add_vote_delegations"
3950 ( "issue_id_p" "issue"."id"%TYPE )
3951 RETURNS VOID
3952 LANGUAGE 'plpgsql' VOLATILE AS $$
3953 DECLARE
3954 "member_id_v" "member"."id"%TYPE;
3955 BEGIN
3956 FOR "member_id_v" IN
3957 SELECT "member_id" FROM "direct_voter"
3958 WHERE "issue_id" = "issue_id_p"
3959 LOOP
3960 UPDATE "direct_voter" SET
3961 "weight" = "weight" + "weight_of_added_vote_delegations"(
3962 "issue_id_p",
3963 "member_id_v",
3964 '{}'
3965 )
3966 WHERE "member_id" = "member_id_v"
3967 AND "issue_id" = "issue_id_p";
3968 END LOOP;
3969 RETURN;
3970 END;
3971 $$;
3973 COMMENT ON FUNCTION "add_vote_delegations"
3974 ( "issue_id_p" "issue"."id"%TYPE )
3975 IS 'Helper function for "close_voting" function';
3978 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
3979 RETURNS VOID
3980 LANGUAGE 'plpgsql' VOLATILE AS $$
3981 DECLARE
3982 "area_id_v" "area"."id"%TYPE;
3983 "unit_id_v" "unit"."id"%TYPE;
3984 "member_id_v" "member"."id"%TYPE;
3985 BEGIN
3986 PERFORM "lock_issue"("issue_id_p");
3987 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
3988 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
3989 -- delete timestamp of voting comment:
3990 UPDATE "direct_voter" SET "comment_changed" = NULL
3991 WHERE "issue_id" = "issue_id_p";
3992 -- delete delegating votes (in cases of manual reset of issue state):
3993 DELETE FROM "delegating_voter"
3994 WHERE "issue_id" = "issue_id_p";
3995 -- delete votes from non-privileged voters:
3996 DELETE FROM "direct_voter"
3997 USING (
3998 SELECT
3999 "direct_voter"."member_id"
4000 FROM "direct_voter"
4001 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
4002 LEFT JOIN "privilege"
4003 ON "privilege"."unit_id" = "unit_id_v"
4004 AND "privilege"."member_id" = "direct_voter"."member_id"
4005 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
4006 "member"."active" = FALSE OR
4007 "privilege"."voting_right" ISNULL OR
4008 "privilege"."voting_right" = FALSE
4009 )
4010 ) AS "subquery"
4011 WHERE "direct_voter"."issue_id" = "issue_id_p"
4012 AND "direct_voter"."member_id" = "subquery"."member_id";
4013 -- consider delegations:
4014 UPDATE "direct_voter" SET "weight" = 1
4015 WHERE "issue_id" = "issue_id_p";
4016 PERFORM "add_vote_delegations"("issue_id_p");
4017 -- set voter count and mark issue as being calculated:
4018 UPDATE "issue" SET
4019 "state" = 'calculation',
4020 "closed" = now(),
4021 "voter_count" = (
4022 SELECT coalesce(sum("weight"), 0)
4023 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
4024 )
4025 WHERE "id" = "issue_id_p";
4026 -- materialize battle_view:
4027 -- NOTE: "closed" column of issue must be set at this point
4028 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
4029 INSERT INTO "battle" (
4030 "issue_id",
4031 "winning_initiative_id", "losing_initiative_id",
4032 "count"
4033 ) SELECT
4034 "issue_id",
4035 "winning_initiative_id", "losing_initiative_id",
4036 "count"
4037 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
4038 -- copy "positive_votes" and "negative_votes" from "battle" table:
4039 UPDATE "initiative" SET
4040 "positive_votes" = "battle_win"."count",
4041 "negative_votes" = "battle_lose"."count"
4042 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
4043 WHERE
4044 "battle_win"."issue_id" = "issue_id_p" AND
4045 "battle_win"."winning_initiative_id" = "initiative"."id" AND
4046 "battle_win"."losing_initiative_id" ISNULL AND
4047 "battle_lose"."issue_id" = "issue_id_p" AND
4048 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
4049 "battle_lose"."winning_initiative_id" ISNULL;
4050 END;
4051 $$;
4053 COMMENT ON FUNCTION "close_voting"
4054 ( "issue"."id"%TYPE )
4055 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.';
4058 CREATE FUNCTION "defeat_strength"
4059 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
4060 RETURNS INT8
4061 LANGUAGE 'plpgsql' IMMUTABLE AS $$
4062 BEGIN
4063 IF "positive_votes_p" > "negative_votes_p" THEN
4064 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
4065 ELSIF "positive_votes_p" = "negative_votes_p" THEN
4066 RETURN 0;
4067 ELSE
4068 RETURN -1;
4069 END IF;
4070 END;
4071 $$;
4073 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';
4076 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
4077 RETURNS VOID
4078 LANGUAGE 'plpgsql' VOLATILE AS $$
4079 DECLARE
4080 "issue_row" "issue"%ROWTYPE;
4081 "policy_row" "policy"%ROWTYPE;
4082 "dimension_v" INTEGER;
4083 "vote_matrix" INT4[][]; -- absolute votes
4084 "matrix" INT8[][]; -- defeat strength / best paths
4085 "i" INTEGER;
4086 "j" INTEGER;
4087 "k" INTEGER;
4088 "battle_row" "battle"%ROWTYPE;
4089 "rank_ary" INT4[];
4090 "rank_v" INT4;
4091 "done_v" INTEGER;
4092 "winners_ary" INTEGER[];
4093 "initiative_id_v" "initiative"."id"%TYPE;
4094 BEGIN
4095 SELECT * INTO "issue_row"
4096 FROM "issue" WHERE "id" = "issue_id_p"
4097 FOR UPDATE;
4098 SELECT * INTO "policy_row"
4099 FROM "policy" WHERE "id" = "issue_row"."policy_id";
4100 SELECT count(1) INTO "dimension_v"
4101 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
4102 -- Create "vote_matrix" with absolute number of votes in pairwise
4103 -- comparison:
4104 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
4105 "i" := 1;
4106 "j" := 2;
4107 FOR "battle_row" IN
4108 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
4109 ORDER BY
4110 "winning_initiative_id" NULLS LAST,
4111 "losing_initiative_id" NULLS LAST
4112 LOOP
4113 "vote_matrix"["i"]["j"] := "battle_row"."count";
4114 IF "j" = "dimension_v" THEN
4115 "i" := "i" + 1;
4116 "j" := 1;
4117 ELSE
4118 "j" := "j" + 1;
4119 IF "j" = "i" THEN
4120 "j" := "j" + 1;
4121 END IF;
4122 END IF;
4123 END LOOP;
4124 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
4125 RAISE EXCEPTION 'Wrong battle count (should not happen)';
4126 END IF;
4127 -- Store defeat strengths in "matrix" using "defeat_strength"
4128 -- function:
4129 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
4130 "i" := 1;
4131 LOOP
4132 "j" := 1;
4133 LOOP
4134 IF "i" != "j" THEN
4135 "matrix"["i"]["j"] := "defeat_strength"(
4136 "vote_matrix"["i"]["j"],
4137 "vote_matrix"["j"]["i"]
4138 );
4139 END IF;
4140 EXIT WHEN "j" = "dimension_v";
4141 "j" := "j" + 1;
4142 END LOOP;
4143 EXIT WHEN "i" = "dimension_v";
4144 "i" := "i" + 1;
4145 END LOOP;
4146 -- Find best paths:
4147 "i" := 1;
4148 LOOP
4149 "j" := 1;
4150 LOOP
4151 IF "i" != "j" THEN
4152 "k" := 1;
4153 LOOP
4154 IF "i" != "k" AND "j" != "k" THEN
4155 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
4156 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
4157 "matrix"["j"]["k"] := "matrix"["j"]["i"];
4158 END IF;
4159 ELSE
4160 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
4161 "matrix"["j"]["k"] := "matrix"["i"]["k"];
4162 END IF;
4163 END IF;
4164 END IF;
4165 EXIT WHEN "k" = "dimension_v";
4166 "k" := "k" + 1;
4167 END LOOP;
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 -- Determine order of winners:
4176 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
4177 "rank_v" := 1;
4178 "done_v" := 0;
4179 LOOP
4180 "winners_ary" := '{}';
4181 "i" := 1;
4182 LOOP
4183 IF "rank_ary"["i"] ISNULL THEN
4184 "j" := 1;
4185 LOOP
4186 IF
4187 "i" != "j" AND
4188 "rank_ary"["j"] ISNULL AND
4189 "matrix"["j"]["i"] > "matrix"["i"]["j"]
4190 THEN
4191 -- someone else is better
4192 EXIT;
4193 END IF;
4194 IF "j" = "dimension_v" THEN
4195 -- noone is better
4196 "winners_ary" := "winners_ary" || "i";
4197 EXIT;
4198 END IF;
4199 "j" := "j" + 1;
4200 END LOOP;
4201 END IF;
4202 EXIT WHEN "i" = "dimension_v";
4203 "i" := "i" + 1;
4204 END LOOP;
4205 "i" := 1;
4206 LOOP
4207 "rank_ary"["winners_ary"["i"]] := "rank_v";
4208 "done_v" := "done_v" + 1;
4209 EXIT WHEN "i" = array_upper("winners_ary", 1);
4210 "i" := "i" + 1;
4211 END LOOP;
4212 EXIT WHEN "done_v" = "dimension_v";
4213 "rank_v" := "rank_v" + 1;
4214 END LOOP;
4215 -- write preliminary results:
4216 "i" := 1;
4217 FOR "initiative_id_v" IN
4218 SELECT "id" FROM "initiative"
4219 WHERE "issue_id" = "issue_id_p" AND "admitted"
4220 ORDER BY "id"
4221 LOOP
4222 UPDATE "initiative" SET
4223 "direct_majority" =
4224 CASE WHEN "policy_row"."direct_majority_strict" THEN
4225 "positive_votes" * "policy_row"."direct_majority_den" >
4226 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
4227 ELSE
4228 "positive_votes" * "policy_row"."direct_majority_den" >=
4229 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
4230 END
4231 AND "positive_votes" >= "policy_row"."direct_majority_positive"
4232 AND "issue_row"."voter_count"-"negative_votes" >=
4233 "policy_row"."direct_majority_non_negative",
4234 "indirect_majority" =
4235 CASE WHEN "policy_row"."indirect_majority_strict" THEN
4236 "positive_votes" * "policy_row"."indirect_majority_den" >
4237 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
4238 ELSE
4239 "positive_votes" * "policy_row"."indirect_majority_den" >=
4240 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
4241 END
4242 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
4243 AND "issue_row"."voter_count"-"negative_votes" >=
4244 "policy_row"."indirect_majority_non_negative",
4245 "schulze_rank" = "rank_ary"["i"],
4246 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
4247 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
4248 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
4249 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0,
4250 "eligible" = FALSE,
4251 "winner" = FALSE,
4252 "rank" = NULL -- NOTE: in cases of manual reset of issue state
4253 WHERE "id" = "initiative_id_v";
4254 "i" := "i" + 1;
4255 END LOOP;
4256 IF "i" != "dimension_v" THEN
4257 RAISE EXCEPTION 'Wrong winner count (should not happen)';
4258 END IF;
4259 -- take indirect majorities into account:
4260 LOOP
4261 UPDATE "initiative" SET "indirect_majority" = TRUE
4262 FROM (
4263 SELECT "new_initiative"."id" AS "initiative_id"
4264 FROM "initiative" "old_initiative"
4265 JOIN "initiative" "new_initiative"
4266 ON "new_initiative"."issue_id" = "issue_id_p"
4267 AND "new_initiative"."indirect_majority" = FALSE
4268 JOIN "battle" "battle_win"
4269 ON "battle_win"."issue_id" = "issue_id_p"
4270 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
4271 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
4272 JOIN "battle" "battle_lose"
4273 ON "battle_lose"."issue_id" = "issue_id_p"
4274 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
4275 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
4276 WHERE "old_initiative"."issue_id" = "issue_id_p"
4277 AND "old_initiative"."indirect_majority" = TRUE
4278 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
4279 "battle_win"."count" * "policy_row"."indirect_majority_den" >
4280 "policy_row"."indirect_majority_num" *
4281 ("battle_win"."count"+"battle_lose"."count")
4282 ELSE
4283 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
4284 "policy_row"."indirect_majority_num" *
4285 ("battle_win"."count"+"battle_lose"."count")
4286 END
4287 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
4288 AND "issue_row"."voter_count"-"battle_lose"."count" >=
4289 "policy_row"."indirect_majority_non_negative"
4290 ) AS "subquery"
4291 WHERE "id" = "subquery"."initiative_id";
4292 EXIT WHEN NOT FOUND;
4293 END LOOP;
4294 -- set "multistage_majority" for remaining matching initiatives:
4295 UPDATE "initiative" SET "multistage_majority" = TRUE
4296 FROM (
4297 SELECT "losing_initiative"."id" AS "initiative_id"
4298 FROM "initiative" "losing_initiative"
4299 JOIN "initiative" "winning_initiative"
4300 ON "winning_initiative"."issue_id" = "issue_id_p"
4301 AND "winning_initiative"."admitted"
4302 JOIN "battle" "battle_win"
4303 ON "battle_win"."issue_id" = "issue_id_p"
4304 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
4305 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
4306 JOIN "battle" "battle_lose"
4307 ON "battle_lose"."issue_id" = "issue_id_p"
4308 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
4309 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
4310 WHERE "losing_initiative"."issue_id" = "issue_id_p"
4311 AND "losing_initiative"."admitted"
4312 AND "winning_initiative"."schulze_rank" <
4313 "losing_initiative"."schulze_rank"
4314 AND "battle_win"."count" > "battle_lose"."count"
4315 AND (
4316 "battle_win"."count" > "winning_initiative"."positive_votes" OR
4317 "battle_lose"."count" < "losing_initiative"."negative_votes" )
4318 ) AS "subquery"
4319 WHERE "id" = "subquery"."initiative_id";
4320 -- mark eligible initiatives:
4321 UPDATE "initiative" SET "eligible" = TRUE
4322 WHERE "issue_id" = "issue_id_p"
4323 AND "initiative"."direct_majority"
4324 AND "initiative"."indirect_majority"
4325 AND "initiative"."better_than_status_quo"
4326 AND (
4327 "policy_row"."no_multistage_majority" = FALSE OR
4328 "initiative"."multistage_majority" = FALSE )
4329 AND (
4330 "policy_row"."no_reverse_beat_path" = FALSE OR
4331 "initiative"."reverse_beat_path" = FALSE );
4332 -- mark final winner:
4333 UPDATE "initiative" SET "winner" = TRUE
4334 FROM (
4335 SELECT "id" AS "initiative_id"
4336 FROM "initiative"
4337 WHERE "issue_id" = "issue_id_p" AND "eligible"
4338 ORDER BY
4339 "schulze_rank",
4340 "id"
4341 LIMIT 1
4342 ) AS "subquery"
4343 WHERE "id" = "subquery"."initiative_id";
4344 -- write (final) ranks:
4345 "rank_v" := 1;
4346 FOR "initiative_id_v" IN
4347 SELECT "id"
4348 FROM "initiative"
4349 WHERE "issue_id" = "issue_id_p" AND "admitted"
4350 ORDER BY
4351 "winner" DESC,
4352 "eligible" DESC,
4353 "schulze_rank",
4354 "id"
4355 LOOP
4356 UPDATE "initiative" SET "rank" = "rank_v"
4357 WHERE "id" = "initiative_id_v";
4358 "rank_v" := "rank_v" + 1;
4359 END LOOP;
4360 -- set schulze rank of status quo and mark issue as finished:
4361 UPDATE "issue" SET
4362 "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
4363 "state" =
4364 CASE WHEN EXISTS (
4365 SELECT NULL FROM "initiative"
4366 WHERE "issue_id" = "issue_id_p" AND "winner"
4367 ) THEN
4368 'finished_with_winner'::"issue_state"
4369 ELSE
4370 'finished_without_winner'::"issue_state"
4371 END,
4372 "ranks_available" = TRUE
4373 WHERE "id" = "issue_id_p";
4374 RETURN;
4375 END;
4376 $$;
4378 COMMENT ON FUNCTION "calculate_ranks"
4379 ( "issue"."id"%TYPE )
4380 IS 'Determine ranking (Votes have to be counted first)';
4384 -----------------------------
4385 -- Automatic state changes --
4386 -----------------------------
4389 CREATE FUNCTION "check_issue"
4390 ( "issue_id_p" "issue"."id"%TYPE )
4391 RETURNS VOID
4392 LANGUAGE 'plpgsql' VOLATILE AS $$
4393 DECLARE
4394 "issue_row" "issue"%ROWTYPE;
4395 "policy_row" "policy"%ROWTYPE;
4396 BEGIN
4397 PERFORM "lock_issue"("issue_id_p");
4398 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4399 -- only process open issues:
4400 IF "issue_row"."closed" ISNULL THEN
4401 SELECT * INTO "policy_row" FROM "policy"
4402 WHERE "id" = "issue_row"."policy_id";
4403 -- create a snapshot, unless issue is already fully frozen:
4404 IF "issue_row"."fully_frozen" ISNULL THEN
4405 PERFORM "create_snapshot"("issue_id_p");
4406 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4407 END IF;
4408 -- eventually close or accept issues, which have not been accepted:
4409 IF "issue_row"."accepted" ISNULL THEN
4410 IF EXISTS (
4411 SELECT NULL FROM "initiative"
4412 WHERE "issue_id" = "issue_id_p"
4413 AND "supporter_count" > 0
4414 AND "supporter_count" * "policy_row"."issue_quorum_den"
4415 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
4416 ) THEN
4417 -- accept issues, if supporter count is high enough
4418 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
4419 -- NOTE: "issue_row" used later
4420 "issue_row"."state" := 'discussion';
4421 "issue_row"."accepted" := now();
4422 UPDATE "issue" SET
4423 "state" = "issue_row"."state",
4424 "accepted" = "issue_row"."accepted"
4425 WHERE "id" = "issue_row"."id";
4426 ELSIF
4427 now() >= "issue_row"."created" + "issue_row"."admission_time"
4428 THEN
4429 -- close issues, if admission time has expired
4430 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
4431 UPDATE "issue" SET
4432 "state" = 'canceled_issue_not_accepted',
4433 "closed" = now()
4434 WHERE "id" = "issue_row"."id";
4435 END IF;
4436 END IF;
4437 -- eventually half freeze issues:
4438 IF
4439 -- NOTE: issue can't be closed at this point, if it has been accepted
4440 "issue_row"."accepted" NOTNULL AND
4441 "issue_row"."half_frozen" ISNULL
4442 THEN
4443 IF
4444 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
4445 THEN
4446 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
4447 -- NOTE: "issue_row" used later
4448 "issue_row"."state" := 'verification';
4449 "issue_row"."half_frozen" := now();
4450 UPDATE "issue" SET
4451 "state" = "issue_row"."state",
4452 "half_frozen" = "issue_row"."half_frozen"
4453 WHERE "id" = "issue_row"."id";
4454 END IF;
4455 END IF;
4456 -- close issues after some time, if all initiatives have been revoked:
4457 IF
4458 "issue_row"."closed" ISNULL AND
4459 NOT EXISTS (
4460 -- all initiatives are revoked
4461 SELECT NULL FROM "initiative"
4462 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
4463 ) AND (
4464 -- and issue has not been accepted yet
4465 "issue_row"."accepted" ISNULL OR
4466 NOT EXISTS (
4467 -- or no initiatives have been revoked lately
4468 SELECT NULL FROM "initiative"
4469 WHERE "issue_id" = "issue_id_p"
4470 AND now() < "revoked" + "issue_row"."verification_time"
4471 ) OR (
4472 -- or verification time has elapsed
4473 "issue_row"."half_frozen" NOTNULL AND
4474 "issue_row"."fully_frozen" ISNULL AND
4475 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
4476 )
4477 )
4478 THEN
4479 -- NOTE: "issue_row" used later
4480 IF "issue_row"."accepted" ISNULL THEN
4481 "issue_row"."state" := 'canceled_revoked_before_accepted';
4482 ELSIF "issue_row"."half_frozen" ISNULL THEN
4483 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
4484 ELSE
4485 "issue_row"."state" := 'canceled_after_revocation_during_verification';
4486 END IF;
4487 "issue_row"."closed" := now();
4488 UPDATE "issue" SET
4489 "state" = "issue_row"."state",
4490 "closed" = "issue_row"."closed"
4491 WHERE "id" = "issue_row"."id";
4492 END IF;
4493 -- fully freeze issue after verification time:
4494 IF
4495 "issue_row"."half_frozen" NOTNULL AND
4496 "issue_row"."fully_frozen" ISNULL AND
4497 "issue_row"."closed" ISNULL AND
4498 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
4499 THEN
4500 PERFORM "freeze_after_snapshot"("issue_id_p");
4501 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
4502 END IF;
4503 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4504 -- close issue by calling close_voting(...) after voting time:
4505 IF
4506 "issue_row"."closed" ISNULL AND
4507 "issue_row"."fully_frozen" NOTNULL AND
4508 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
4509 THEN
4510 PERFORM "close_voting"("issue_id_p");
4511 -- calculate ranks will not consume much time and can be done now
4512 PERFORM "calculate_ranks"("issue_id_p");
4513 END IF;
4514 END IF;
4515 RETURN;
4516 END;
4517 $$;
4519 COMMENT ON FUNCTION "check_issue"
4520 ( "issue"."id"%TYPE )
4521 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.';
4524 CREATE FUNCTION "check_everything"()
4525 RETURNS VOID
4526 LANGUAGE 'plpgsql' VOLATILE AS $$
4527 DECLARE
4528 "issue_id_v" "issue"."id"%TYPE;
4529 BEGIN
4530 DELETE FROM "expired_session";
4531 PERFORM "check_activity"();
4532 PERFORM "calculate_member_counts"();
4533 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
4534 PERFORM "check_issue"("issue_id_v");
4535 END LOOP;
4536 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
4537 PERFORM "calculate_ranks"("issue_id_v");
4538 END LOOP;
4539 RETURN;
4540 END;
4541 $$;
4543 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.';
4547 ----------------------
4548 -- Deletion of data --
4549 ----------------------
4552 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
4553 RETURNS VOID
4554 LANGUAGE 'plpgsql' VOLATILE AS $$
4555 DECLARE
4556 "issue_row" "issue"%ROWTYPE;
4557 BEGIN
4558 SELECT * INTO "issue_row"
4559 FROM "issue" WHERE "id" = "issue_id_p"
4560 FOR UPDATE;
4561 IF "issue_row"."cleaned" ISNULL THEN
4562 UPDATE "issue" SET
4563 "state" = 'voting',
4564 "closed" = NULL,
4565 "ranks_available" = FALSE
4566 WHERE "id" = "issue_id_p";
4567 DELETE FROM "delegating_voter"
4568 WHERE "issue_id" = "issue_id_p";
4569 DELETE FROM "direct_voter"
4570 WHERE "issue_id" = "issue_id_p";
4571 DELETE FROM "delegating_interest_snapshot"
4572 WHERE "issue_id" = "issue_id_p";
4573 DELETE FROM "direct_interest_snapshot"
4574 WHERE "issue_id" = "issue_id_p";
4575 DELETE FROM "delegating_population_snapshot"
4576 WHERE "issue_id" = "issue_id_p";
4577 DELETE FROM "direct_population_snapshot"
4578 WHERE "issue_id" = "issue_id_p";
4579 DELETE FROM "non_voter"
4580 WHERE "issue_id" = "issue_id_p";
4581 DELETE FROM "delegation"
4582 WHERE "issue_id" = "issue_id_p";
4583 DELETE FROM "supporter"
4584 WHERE "issue_id" = "issue_id_p";
4585 UPDATE "issue" SET
4586 "state" = "issue_row"."state",
4587 "closed" = "issue_row"."closed",
4588 "ranks_available" = "issue_row"."ranks_available",
4589 "cleaned" = now()
4590 WHERE "id" = "issue_id_p";
4591 END IF;
4592 RETURN;
4593 END;
4594 $$;
4596 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
4599 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
4600 RETURNS VOID
4601 LANGUAGE 'plpgsql' VOLATILE AS $$
4602 BEGIN
4603 UPDATE "member" SET
4604 "last_login" = NULL,
4605 "login" = NULL,
4606 "password" = NULL,
4607 "locked" = TRUE,
4608 "active" = FALSE,
4609 "notify_email" = NULL,
4610 "notify_email_unconfirmed" = NULL,
4611 "notify_email_secret" = NULL,
4612 "notify_email_secret_expiry" = NULL,
4613 "notify_email_lock_expiry" = NULL,
4614 "password_reset_secret" = NULL,
4615 "password_reset_secret_expiry" = NULL,
4616 "organizational_unit" = NULL,
4617 "internal_posts" = NULL,
4618 "realname" = NULL,
4619 "birthday" = NULL,
4620 "address" = NULL,
4621 "email" = NULL,
4622 "xmpp_address" = NULL,
4623 "website" = NULL,
4624 "phone" = NULL,
4625 "mobile_phone" = NULL,
4626 "profession" = NULL,
4627 "external_memberships" = NULL,
4628 "external_posts" = NULL,
4629 "statement" = NULL
4630 WHERE "id" = "member_id_p";
4631 -- "text_search_data" is updated by triggers
4632 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
4633 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
4634 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
4635 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
4636 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
4637 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
4638 DELETE FROM "session" WHERE "member_id" = "member_id_p";
4639 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
4640 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
4641 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
4642 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
4643 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
4644 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
4645 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
4646 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
4647 DELETE FROM "direct_voter" USING "issue"
4648 WHERE "direct_voter"."issue_id" = "issue"."id"
4649 AND "issue"."closed" ISNULL
4650 AND "member_id" = "member_id_p";
4651 RETURN;
4652 END;
4653 $$;
4655 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)';
4658 CREATE FUNCTION "delete_private_data"()
4659 RETURNS VOID
4660 LANGUAGE 'plpgsql' VOLATILE AS $$
4661 BEGIN
4662 DELETE FROM "member" WHERE "activated" ISNULL;
4663 UPDATE "member" SET
4664 "invite_code" = NULL,
4665 "invite_code_expiry" = NULL,
4666 "admin_comment" = NULL,
4667 "last_login" = NULL,
4668 "login" = NULL,
4669 "password" = NULL,
4670 "lang" = NULL,
4671 "notify_email" = NULL,
4672 "notify_email_unconfirmed" = NULL,
4673 "notify_email_secret" = NULL,
4674 "notify_email_secret_expiry" = NULL,
4675 "notify_email_lock_expiry" = NULL,
4676 "notify_level" = NULL,
4677 "password_reset_secret" = NULL,
4678 "password_reset_secret_expiry" = NULL,
4679 "organizational_unit" = NULL,
4680 "internal_posts" = NULL,
4681 "realname" = NULL,
4682 "birthday" = NULL,
4683 "address" = NULL,
4684 "email" = NULL,
4685 "xmpp_address" = NULL,
4686 "website" = NULL,
4687 "phone" = NULL,
4688 "mobile_phone" = NULL,
4689 "profession" = NULL,
4690 "external_memberships" = NULL,
4691 "external_posts" = NULL,
4692 "formatting_engine" = NULL,
4693 "statement" = NULL;
4694 -- "text_search_data" is updated by triggers
4695 DELETE FROM "setting";
4696 DELETE FROM "setting_map";
4697 DELETE FROM "member_relation_setting";
4698 DELETE FROM "member_image";
4699 DELETE FROM "contact";
4700 DELETE FROM "ignored_member";
4701 DELETE FROM "session";
4702 DELETE FROM "area_setting";
4703 DELETE FROM "issue_setting";
4704 DELETE FROM "ignored_initiative";
4705 DELETE FROM "initiative_setting";
4706 DELETE FROM "suggestion_setting";
4707 DELETE FROM "non_voter";
4708 DELETE FROM "direct_voter" USING "issue"
4709 WHERE "direct_voter"."issue_id" = "issue"."id"
4710 AND "issue"."closed" ISNULL;
4711 RETURN;
4712 END;
4713 $$;
4715 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.';
4719 COMMIT;