liquid_feedback_core
view core.sql @ 292:3de42ea02dc2
No "issue_quorum" for polling policies
| author | jbe |
|---|---|
| date | Mon Aug 20 13:22:33 2012 +0200 (2012-08-20) |
| parents | 86f231bd6906 |
| children | 9ab561a91035 |
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.0', 2, 1, 0))
11 AS "subquery"("string", "major", "minor", "revision");
15 ----------------------
16 -- Full text search --
17 ----------------------
20 CREATE FUNCTION "text_search_query"("query_text_p" TEXT)
21 RETURNS TSQUERY
22 LANGUAGE 'plpgsql' IMMUTABLE AS $$
23 BEGIN
24 RETURN plainto_tsquery('pg_catalog.simple', "query_text_p");
25 END;
26 $$;
28 COMMENT ON FUNCTION "text_search_query"(TEXT) IS 'Usage: WHERE "text_search_data" @@ "text_search_query"(''<user query>'')';
31 CREATE FUNCTION "highlight"
32 ( "body_p" TEXT,
33 "query_text_p" TEXT )
34 RETURNS TEXT
35 LANGUAGE 'plpgsql' IMMUTABLE AS $$
36 BEGIN
37 RETURN ts_headline(
38 'pg_catalog.simple',
39 replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'),
40 "text_search_query"("query_text_p"),
41 'StartSel=* StopSel=* HighlightAll=TRUE' );
42 END;
43 $$;
45 COMMENT ON FUNCTION "highlight"
46 ( "body_p" TEXT,
47 "query_text_p" TEXT )
48 IS 'For a given a user query this function encapsulates all matches with asterisks. Asterisks and backslashes being already present are preceeded with one extra backslash.';
52 -------------------------
53 -- Tables and indicies --
54 -------------------------
57 CREATE TABLE "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 "time_frame" INTERVAL PRIMARY KEY,
69 "text_entry_limit" INT4,
70 "initiative_limit" INT4 );
72 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.';
74 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';
75 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
78 CREATE TYPE "notify_level" AS ENUM
79 ('none', 'voting', 'verification', 'discussion', 'all');
81 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';
84 CREATE TABLE "member" (
85 "id" SERIAL4 PRIMARY KEY,
86 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
87 "invite_code" TEXT UNIQUE,
88 "invite_code_expiry" TIMESTAMPTZ,
89 "admin_comment" TEXT,
90 "activated" TIMESTAMPTZ,
91 "last_activity" DATE,
92 "last_login" TIMESTAMPTZ,
93 "login" TEXT UNIQUE,
94 "password" TEXT,
95 "locked" BOOLEAN NOT NULL DEFAULT FALSE,
96 "active" BOOLEAN NOT NULL DEFAULT FALSE,
97 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
98 "lang" TEXT,
99 "notify_email" TEXT,
100 "notify_email_unconfirmed" TEXT,
101 "notify_email_secret" TEXT UNIQUE,
102 "notify_email_secret_expiry" TIMESTAMPTZ,
103 "notify_email_lock_expiry" TIMESTAMPTZ,
104 "notify_level" "notify_level",
105 "password_reset_secret" TEXT UNIQUE,
106 "password_reset_secret_expiry" TIMESTAMPTZ,
107 "name" TEXT UNIQUE,
108 "identification" TEXT UNIQUE,
109 "authentication" TEXT,
110 "organizational_unit" TEXT,
111 "internal_posts" TEXT,
112 "realname" TEXT,
113 "birthday" DATE,
114 "address" TEXT,
115 "email" TEXT,
116 "xmpp_address" TEXT,
117 "website" TEXT,
118 "phone" TEXT,
119 "mobile_phone" TEXT,
120 "profession" TEXT,
121 "external_memberships" TEXT,
122 "external_posts" TEXT,
123 "formatting_engine" TEXT,
124 "statement" TEXT,
125 "text_search_data" TSVECTOR,
126 CONSTRAINT "active_requires_activated_and_last_activity"
127 CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)),
128 CONSTRAINT "name_not_null_if_activated"
129 CHECK ("activated" ISNULL OR "name" NOTNULL) );
130 CREATE INDEX "member_active_idx" ON "member" ("active");
131 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
132 CREATE TRIGGER "update_text_search_data"
133 BEFORE INSERT OR UPDATE ON "member"
134 FOR EACH ROW EXECUTE PROCEDURE
135 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
136 "name", "identification", "organizational_unit", "internal_posts",
137 "realname", "external_memberships", "external_posts", "statement" );
139 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
141 COMMENT ON COLUMN "member"."created" IS 'Creation of member record and/or invite code';
142 COMMENT ON COLUMN "member"."invite_code" IS 'Optional invite code, to allow a member to initialize his/her account the first time';
143 COMMENT ON COLUMN "member"."invite_code_expiry" IS 'Expiry data/time for "invite_code"';
144 COMMENT ON COLUMN "member"."admin_comment" IS 'Hidden comment for administrative purposes';
145 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';
146 COMMENT ON COLUMN "member"."last_activity" IS 'Date of last activity of member; required to be set for "active" members';
147 COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login';
148 COMMENT ON COLUMN "member"."login" IS 'Login name';
149 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
150 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
151 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".';
152 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
153 COMMENT ON COLUMN "member"."lang" IS 'Language code of the preferred language of the member';
154 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
155 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
156 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
157 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
158 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
159 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';
160 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member, may be NULL if account has not been activated yet';
161 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
162 COMMENT ON COLUMN "member"."authentication" IS 'Information about how this member was authenticated';
163 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
164 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
165 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
166 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
167 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
168 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
169 COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"';
170 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his/her profile';
173 -- DEPRECATED API TABLES --
175 CREATE TYPE "application_access_level" AS ENUM
176 ('member', 'full', 'pseudonymous', 'anonymous');
178 COMMENT ON TYPE "application_access_level" IS 'DEPRECATED, WILL BE REMOVED! Access privileges for applications using the API';
181 CREATE TABLE "member_application" (
182 "id" SERIAL8 PRIMARY KEY,
183 UNIQUE ("member_id", "name"),
184 "member_id" INT4 NOT NULL REFERENCES "member" ("id")
185 ON DELETE CASCADE ON UPDATE CASCADE,
186 "name" TEXT NOT NULL,
187 "comment" TEXT,
188 "access_level" "application_access_level" NOT NULL,
189 "key" TEXT NOT NULL UNIQUE,
190 "last_usage" TIMESTAMPTZ );
192 COMMENT ON TABLE "member_application" IS 'DEPRECATED, WILL BE REMOVED! Registered application being allowed to use the API';
194 -- END OF DEPRECARED API TABLES --
197 CREATE TABLE "member_history" (
198 "id" SERIAL8 PRIMARY KEY,
199 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
200 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
201 "active" BOOLEAN NOT NULL,
202 "name" TEXT NOT NULL );
203 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
205 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
207 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
208 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
211 CREATE TABLE "rendered_member_statement" (
212 PRIMARY KEY ("member_id", "format"),
213 "member_id" INT8 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
214 "format" TEXT,
215 "content" TEXT NOT NULL );
217 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)';
220 CREATE TABLE "setting" (
221 PRIMARY KEY ("member_id", "key"),
222 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
223 "key" TEXT NOT NULL,
224 "value" TEXT NOT NULL );
225 CREATE INDEX "setting_key_idx" ON "setting" ("key");
227 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
229 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
232 CREATE TABLE "setting_map" (
233 PRIMARY KEY ("member_id", "key", "subkey"),
234 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
235 "key" TEXT NOT NULL,
236 "subkey" TEXT NOT NULL,
237 "value" TEXT NOT NULL );
238 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
240 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
242 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
243 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
244 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
247 CREATE TABLE "member_relation_setting" (
248 PRIMARY KEY ("member_id", "key", "other_member_id"),
249 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
250 "key" TEXT NOT NULL,
251 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
252 "value" TEXT NOT NULL );
254 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
257 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
259 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
262 CREATE TABLE "member_image" (
263 PRIMARY KEY ("member_id", "image_type", "scaled"),
264 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
265 "image_type" "member_image_type",
266 "scaled" BOOLEAN,
267 "content_type" TEXT,
268 "data" BYTEA NOT NULL );
270 COMMENT ON TABLE "member_image" IS 'Images of members';
272 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
275 CREATE TABLE "member_count" (
276 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
277 "total_count" INT4 NOT NULL );
279 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';
281 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
282 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
285 CREATE TABLE "contact" (
286 PRIMARY KEY ("member_id", "other_member_id"),
287 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
288 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
289 "public" BOOLEAN NOT NULL DEFAULT FALSE,
290 CONSTRAINT "cant_save_yourself_as_contact"
291 CHECK ("member_id" != "other_member_id") );
292 CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
294 COMMENT ON TABLE "contact" IS 'Contact lists';
296 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
297 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
298 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
301 CREATE TABLE "ignored_member" (
302 PRIMARY KEY ("member_id", "other_member_id"),
303 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
304 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
305 CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
307 COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
309 COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone';
310 COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
313 CREATE TABLE "session" (
314 "ident" TEXT PRIMARY KEY,
315 "additional_secret" TEXT,
316 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
317 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
318 "lang" TEXT );
319 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
321 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer';
323 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
324 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
325 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
326 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
329 CREATE TABLE "policy" (
330 "id" SERIAL4 PRIMARY KEY,
331 "index" INT4 NOT NULL,
332 "active" BOOLEAN NOT NULL DEFAULT TRUE,
333 "name" TEXT NOT NULL UNIQUE,
334 "description" TEXT NOT NULL DEFAULT '',
335 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
336 "admission_time" INTERVAL,
337 "discussion_time" INTERVAL,
338 "verification_time" INTERVAL,
339 "voting_time" INTERVAL,
340 "issue_quorum_num" INT4,
341 "issue_quorum_den" INT4,
342 "initiative_quorum_num" INT4 NOT NULL,
343 "initiative_quorum_den" INT4 NOT NULL,
344 "direct_majority_num" INT4 NOT NULL DEFAULT 1,
345 "direct_majority_den" INT4 NOT NULL DEFAULT 2,
346 "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
347 "direct_majority_positive" INT4 NOT NULL DEFAULT 0,
348 "direct_majority_non_negative" INT4 NOT NULL DEFAULT 0,
349 "indirect_majority_num" INT4 NOT NULL DEFAULT 1,
350 "indirect_majority_den" INT4 NOT NULL DEFAULT 2,
351 "indirect_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
352 "indirect_majority_positive" INT4 NOT NULL DEFAULT 0,
353 "indirect_majority_non_negative" INT4 NOT NULL DEFAULT 0,
354 "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT TRUE,
355 "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE,
356 CONSTRAINT "timing" CHECK (
357 ( "polling" = FALSE AND
358 "admission_time" NOTNULL AND "discussion_time" NOTNULL AND
359 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
360 ( "polling" = TRUE AND
361 "admission_time" ISNULL 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" ISNULL AND
365 "verification_time" ISNULL AND "voting_time" ISNULL ) ),
366 CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK (
367 "polling" = "issue_quorum_num" ISNULL AND
368 "polling" = "issue_quorum_den" ISNULL ) );
369 CREATE INDEX "policy_active_idx" ON "policy" ("active");
371 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
373 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
374 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
375 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';
376 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"';
377 COMMENT ON COLUMN "policy"."discussion_time" IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"';
378 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"';
379 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'')';
380 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''';
381 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''';
382 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
383 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
384 COMMENT ON COLUMN "policy"."direct_majority_num" IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner';
385 COMMENT ON COLUMN "policy"."direct_majority_den" IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner';
386 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.';
387 COMMENT ON COLUMN "policy"."direct_majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner';
388 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';
389 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';
390 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';
391 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.';
392 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';
393 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';
394 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.';
395 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").';
398 CREATE TABLE "unit" (
399 "id" SERIAL4 PRIMARY KEY,
400 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
401 "active" BOOLEAN NOT NULL DEFAULT TRUE,
402 "name" TEXT NOT NULL,
403 "description" TEXT NOT NULL DEFAULT '',
404 "member_count" INT4,
405 "text_search_data" TSVECTOR );
406 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
407 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
408 CREATE INDEX "unit_active_idx" ON "unit" ("active");
409 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
410 CREATE TRIGGER "update_text_search_data"
411 BEFORE INSERT OR UPDATE ON "unit"
412 FOR EACH ROW EXECUTE PROCEDURE
413 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
414 "name", "description" );
416 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
418 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
419 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in areas of this unit';
420 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
423 CREATE TABLE "unit_setting" (
424 PRIMARY KEY ("member_id", "key", "unit_id"),
425 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
426 "key" TEXT NOT NULL,
427 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
428 "value" TEXT NOT NULL );
430 COMMENT ON TABLE "unit_setting" IS 'Place for frontend to store unit specific settings of members as strings';
433 CREATE TABLE "area" (
434 "id" SERIAL4 PRIMARY KEY,
435 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
436 "active" BOOLEAN NOT NULL DEFAULT TRUE,
437 "name" TEXT NOT NULL,
438 "description" TEXT NOT NULL DEFAULT '',
439 "direct_member_count" INT4,
440 "member_weight" INT4,
441 "text_search_data" TSVECTOR );
442 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
443 CREATE INDEX "area_active_idx" ON "area" ("active");
444 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
445 CREATE TRIGGER "update_text_search_data"
446 BEFORE INSERT OR UPDATE ON "area"
447 FOR EACH ROW EXECUTE PROCEDURE
448 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
449 "name", "description" );
451 COMMENT ON TABLE "area" IS 'Subject areas';
453 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
454 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"';
455 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
458 CREATE TABLE "area_setting" (
459 PRIMARY KEY ("member_id", "key", "area_id"),
460 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
461 "key" TEXT NOT NULL,
462 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
463 "value" TEXT NOT NULL );
465 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
468 CREATE TABLE "allowed_policy" (
469 PRIMARY KEY ("area_id", "policy_id"),
470 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
471 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
472 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
473 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
475 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
477 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
480 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
482 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';
485 CREATE TYPE "issue_state" AS ENUM (
486 'admission', 'discussion', 'verification', 'voting',
487 'canceled_revoked_before_accepted',
488 'canceled_issue_not_accepted',
489 'canceled_after_revocation_during_discussion',
490 'canceled_after_revocation_during_verification',
491 'calculation',
492 'canceled_no_initiative_admitted',
493 'finished_without_winner', 'finished_with_winner');
495 COMMENT ON TYPE "issue_state" IS 'State of issues';
498 CREATE TABLE "issue" (
499 "id" SERIAL4 PRIMARY KEY,
500 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
501 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
502 "state" "issue_state" NOT NULL DEFAULT 'admission',
503 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
504 "accepted" TIMESTAMPTZ,
505 "half_frozen" TIMESTAMPTZ,
506 "fully_frozen" TIMESTAMPTZ,
507 "closed" TIMESTAMPTZ,
508 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
509 "cleaned" TIMESTAMPTZ,
510 "admission_time" INTERVAL,
511 "discussion_time" INTERVAL NOT NULL,
512 "verification_time" INTERVAL NOT NULL,
513 "voting_time" INTERVAL NOT NULL,
514 "snapshot" TIMESTAMPTZ,
515 "latest_snapshot_event" "snapshot_event",
516 "population" INT4,
517 "voter_count" INT4,
518 "status_quo_schulze_rank" INT4,
519 CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK (
520 "admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created") ),
521 CONSTRAINT "valid_state" CHECK ((
522 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
523 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
524 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
525 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
526 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
527 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
528 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
529 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
530 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)) AND (
531 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
532 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
533 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
534 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
535 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
536 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
537 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
538 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
539 ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
540 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
541 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
542 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
543 )),
544 CONSTRAINT "state_change_order" CHECK (
545 "created" <= "accepted" AND
546 "accepted" <= "half_frozen" AND
547 "half_frozen" <= "fully_frozen" AND
548 "fully_frozen" <= "closed" ),
549 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
550 "cleaned" ISNULL OR "closed" NOTNULL ),
551 CONSTRAINT "last_snapshot_on_full_freeze"
552 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
553 CONSTRAINT "freeze_requires_snapshot"
554 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
555 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
556 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
557 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
558 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
559 CREATE INDEX "issue_created_idx" ON "issue" ("created");
560 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
561 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
562 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
563 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
564 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
565 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
567 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
569 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
570 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.';
571 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.';
572 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.';
573 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
574 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
575 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
576 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
577 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
578 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
579 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
580 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';
581 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
582 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';
583 COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
586 CREATE TABLE "issue_setting" (
587 PRIMARY KEY ("member_id", "key", "issue_id"),
588 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
589 "key" TEXT NOT NULL,
590 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
591 "value" TEXT NOT NULL );
593 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
596 CREATE TABLE "initiative" (
597 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
598 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
599 "id" SERIAL4 PRIMARY KEY,
600 "name" TEXT NOT NULL,
601 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
602 "discussion_url" TEXT,
603 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
604 "revoked" TIMESTAMPTZ,
605 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
606 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
607 "admitted" BOOLEAN,
608 "supporter_count" INT4,
609 "informed_supporter_count" INT4,
610 "satisfied_supporter_count" INT4,
611 "satisfied_informed_supporter_count" INT4,
612 "positive_votes" INT4,
613 "negative_votes" INT4,
614 "direct_majority" BOOLEAN,
615 "indirect_majority" BOOLEAN,
616 "schulze_rank" INT4,
617 "better_than_status_quo" BOOLEAN,
618 "worse_than_status_quo" BOOLEAN,
619 "reverse_beat_path" BOOLEAN,
620 "multistage_majority" BOOLEAN,
621 "eligible" BOOLEAN,
622 "winner" BOOLEAN,
623 "rank" INT4,
624 "text_search_data" TSVECTOR,
625 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
626 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL),
627 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
628 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
629 CONSTRAINT "revoked_initiatives_cant_be_admitted"
630 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
631 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
632 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
633 ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND
634 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
635 "schulze_rank" ISNULL AND
636 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
637 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
638 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ),
639 CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")),
640 CONSTRAINT "minimum_requirement_to_be_eligible" CHECK (
641 "eligible" = FALSE OR
642 ("direct_majority" AND "indirect_majority" AND "better_than_status_quo") ),
643 CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE),
644 CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1),
645 CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE),
646 CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") );
647 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
648 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
649 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
650 CREATE TRIGGER "update_text_search_data"
651 BEFORE INSERT OR UPDATE ON "initiative"
652 FOR EACH ROW EXECUTE PROCEDURE
653 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
654 "name", "discussion_url");
656 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.';
658 COMMENT ON COLUMN "initiative"."polling" IS 'Initiative does not need to pass the initiative quorum (see "policy"."polling")';
659 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
660 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
661 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoke the initiative';
662 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
663 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
664 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
665 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
666 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
667 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
668 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
669 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"';
670 COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths';
671 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking without tie-breaking';
672 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)';
673 COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)';
674 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';
675 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';
676 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"';
677 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"';
678 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';
681 CREATE TABLE "battle" (
682 "issue_id" INT4 NOT NULL,
683 "winning_initiative_id" INT4,
684 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
685 "losing_initiative_id" INT4,
686 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
687 "count" INT4 NOT NULL,
688 CONSTRAINT "initiative_ids_not_equal" CHECK (
689 "winning_initiative_id" != "losing_initiative_id" OR
690 ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
691 ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) );
692 CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
693 CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
694 CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
696 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';
699 CREATE TABLE "ignored_initiative" (
700 PRIMARY KEY ("initiative_id", "member_id"),
701 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
702 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
703 CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");
705 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
708 CREATE TABLE "initiative_setting" (
709 PRIMARY KEY ("member_id", "key", "initiative_id"),
710 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
711 "key" TEXT NOT NULL,
712 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
713 "value" TEXT NOT NULL );
715 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
718 CREATE TABLE "draft" (
719 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
720 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
721 "id" SERIAL8 PRIMARY KEY,
722 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
723 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
724 "formatting_engine" TEXT,
725 "content" TEXT NOT NULL,
726 "text_search_data" TSVECTOR );
727 CREATE INDEX "draft_created_idx" ON "draft" ("created");
728 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
729 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
730 CREATE TRIGGER "update_text_search_data"
731 BEFORE INSERT OR UPDATE ON "draft"
732 FOR EACH ROW EXECUTE PROCEDURE
733 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
735 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.';
737 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
738 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
741 CREATE TABLE "rendered_draft" (
742 PRIMARY KEY ("draft_id", "format"),
743 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
744 "format" TEXT,
745 "content" TEXT NOT NULL );
747 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)';
750 CREATE TABLE "suggestion" (
751 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
752 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
753 "id" SERIAL8 PRIMARY KEY,
754 "draft_id" INT8 NOT NULL,
755 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
756 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
757 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
758 "name" TEXT NOT NULL,
759 "formatting_engine" TEXT,
760 "content" TEXT NOT NULL DEFAULT '',
761 "text_search_data" TSVECTOR,
762 "minus2_unfulfilled_count" INT4,
763 "minus2_fulfilled_count" INT4,
764 "minus1_unfulfilled_count" INT4,
765 "minus1_fulfilled_count" INT4,
766 "plus1_unfulfilled_count" INT4,
767 "plus1_fulfilled_count" INT4,
768 "plus2_unfulfilled_count" INT4,
769 "plus2_fulfilled_count" INT4 );
770 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
771 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
772 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
773 CREATE TRIGGER "update_text_search_data"
774 BEFORE INSERT OR UPDATE ON "suggestion"
775 FOR EACH ROW EXECUTE PROCEDURE
776 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
777 "name", "content");
779 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';
781 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")';
782 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
783 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
784 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
785 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
786 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
787 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
788 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
789 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
792 CREATE TABLE "rendered_suggestion" (
793 PRIMARY KEY ("suggestion_id", "format"),
794 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
795 "format" TEXT,
796 "content" TEXT NOT NULL );
798 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)';
801 CREATE TABLE "suggestion_setting" (
802 PRIMARY KEY ("member_id", "key", "suggestion_id"),
803 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
804 "key" TEXT NOT NULL,
805 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
806 "value" TEXT NOT NULL );
808 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
811 CREATE TABLE "privilege" (
812 PRIMARY KEY ("unit_id", "member_id"),
813 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
814 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
815 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
816 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
817 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
818 "member_manager" BOOLEAN NOT NULL DEFAULT FALSE,
819 "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE,
820 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE,
821 "polling_right" BOOLEAN NOT NULL DEFAULT FALSE );
823 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
825 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke any privileges to/from other members';
826 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create and disable sub units';
827 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create and disable areas and set area parameters';
828 COMMENT ON COLUMN "privilege"."member_manager" IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"';
829 COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative';
830 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to support initiatives, create and rate suggestions, and to vote';
831 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';
834 CREATE TABLE "membership" (
835 PRIMARY KEY ("area_id", "member_id"),
836 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
837 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
838 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
840 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
843 CREATE TABLE "interest" (
844 PRIMARY KEY ("issue_id", "member_id"),
845 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
846 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
847 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
849 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.';
852 CREATE TABLE "initiator" (
853 PRIMARY KEY ("initiative_id", "member_id"),
854 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
855 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
856 "accepted" BOOLEAN );
857 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
859 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.';
861 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.';
864 CREATE TABLE "supporter" (
865 "issue_id" INT4 NOT NULL,
866 PRIMARY KEY ("initiative_id", "member_id"),
867 "initiative_id" INT4,
868 "member_id" INT4,
869 "draft_id" INT8 NOT NULL,
870 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
871 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE );
872 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
874 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.';
876 COMMENT ON COLUMN "supporter"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
877 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")';
880 CREATE TABLE "opinion" (
881 "initiative_id" INT4 NOT NULL,
882 PRIMARY KEY ("suggestion_id", "member_id"),
883 "suggestion_id" INT8,
884 "member_id" INT4,
885 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
886 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
887 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
888 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
889 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
891 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.';
893 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
896 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
898 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
901 CREATE TABLE "delegation" (
902 "id" SERIAL8 PRIMARY KEY,
903 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
904 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
905 "scope" "delegation_scope" NOT NULL,
906 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
907 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
908 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
909 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
910 CONSTRAINT "no_unit_delegation_to_null"
911 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
912 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
913 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
914 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
915 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
916 UNIQUE ("unit_id", "truster_id"),
917 UNIQUE ("area_id", "truster_id"),
918 UNIQUE ("issue_id", "truster_id") );
919 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
920 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
922 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
924 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
925 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
926 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
929 CREATE TABLE "direct_population_snapshot" (
930 PRIMARY KEY ("issue_id", "event", "member_id"),
931 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
932 "event" "snapshot_event",
933 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
934 "weight" INT4 );
935 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
937 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
939 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
940 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
943 CREATE TABLE "delegating_population_snapshot" (
944 PRIMARY KEY ("issue_id", "event", "member_id"),
945 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
946 "event" "snapshot_event",
947 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
948 "weight" INT4,
949 "scope" "delegation_scope" NOT NULL,
950 "delegate_member_ids" INT4[] NOT NULL );
951 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
953 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
955 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
956 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
957 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
958 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"';
961 CREATE TABLE "direct_interest_snapshot" (
962 PRIMARY KEY ("issue_id", "event", "member_id"),
963 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
964 "event" "snapshot_event",
965 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
966 "weight" INT4 );
967 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
969 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
971 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
972 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
975 CREATE TABLE "delegating_interest_snapshot" (
976 PRIMARY KEY ("issue_id", "event", "member_id"),
977 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
978 "event" "snapshot_event",
979 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
980 "weight" INT4,
981 "scope" "delegation_scope" NOT NULL,
982 "delegate_member_ids" INT4[] NOT NULL );
983 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
985 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
987 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
988 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
989 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
990 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"';
993 CREATE TABLE "direct_supporter_snapshot" (
994 "issue_id" INT4 NOT NULL,
995 PRIMARY KEY ("initiative_id", "event", "member_id"),
996 "initiative_id" INT4,
997 "event" "snapshot_event",
998 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
999 "draft_id" INT8 NOT NULL,
1000 "informed" BOOLEAN NOT NULL,
1001 "satisfied" BOOLEAN NOT NULL,
1002 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1003 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
1004 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1005 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
1007 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
1009 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';
1010 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1011 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
1012 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
1015 CREATE TABLE "non_voter" (
1016 PRIMARY KEY ("issue_id", "member_id"),
1017 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1018 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
1019 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
1021 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
1024 CREATE TABLE "direct_voter" (
1025 PRIMARY KEY ("issue_id", "member_id"),
1026 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1027 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1028 "weight" INT4,
1029 "comment_changed" TIMESTAMPTZ,
1030 "formatting_engine" TEXT,
1031 "comment" TEXT,
1032 "text_search_data" TSVECTOR );
1033 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
1034 CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data");
1035 CREATE TRIGGER "update_text_search_data"
1036 BEFORE INSERT OR UPDATE ON "direct_voter"
1037 FOR EACH ROW EXECUTE PROCEDURE
1038 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment");
1040 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.';
1042 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
1043 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';
1044 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';
1045 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.';
1048 CREATE TABLE "rendered_voter_comment" (
1049 PRIMARY KEY ("issue_id", "member_id", "format"),
1050 FOREIGN KEY ("issue_id", "member_id")
1051 REFERENCES "direct_voter" ("issue_id", "member_id")
1052 ON DELETE CASCADE ON UPDATE CASCADE,
1053 "issue_id" INT4,
1054 "member_id" INT4,
1055 "format" TEXT,
1056 "content" TEXT NOT NULL );
1058 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)';
1061 CREATE TABLE "delegating_voter" (
1062 PRIMARY KEY ("issue_id", "member_id"),
1063 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1064 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1065 "weight" INT4,
1066 "scope" "delegation_scope" NOT NULL,
1067 "delegate_member_ids" INT4[] NOT NULL );
1068 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
1070 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
1072 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
1073 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
1074 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"';
1077 CREATE TABLE "vote" (
1078 "issue_id" INT4 NOT NULL,
1079 PRIMARY KEY ("initiative_id", "member_id"),
1080 "initiative_id" INT4,
1081 "member_id" INT4,
1082 "grade" INT4,
1083 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1084 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1085 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
1087 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.';
1089 COMMENT ON COLUMN "vote"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
1090 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.';
1093 CREATE TYPE "event_type" AS ENUM (
1094 'issue_state_changed',
1095 'initiative_created_in_new_issue',
1096 'initiative_created_in_existing_issue',
1097 'initiative_revoked',
1098 'new_draft_created',
1099 'suggestion_created');
1101 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
1104 CREATE TABLE "event" (
1105 "id" SERIAL8 PRIMARY KEY,
1106 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
1107 "event" "event_type" NOT NULL,
1108 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1109 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1110 "state" "issue_state" CHECK ("state" != 'calculation'),
1111 "initiative_id" INT4,
1112 "draft_id" INT8,
1113 "suggestion_id" INT8,
1114 FOREIGN KEY ("issue_id", "initiative_id")
1115 REFERENCES "initiative" ("issue_id", "id")
1116 ON DELETE CASCADE ON UPDATE CASCADE,
1117 FOREIGN KEY ("initiative_id", "draft_id")
1118 REFERENCES "draft" ("initiative_id", "id")
1119 ON DELETE CASCADE ON UPDATE CASCADE,
1120 FOREIGN KEY ("initiative_id", "suggestion_id")
1121 REFERENCES "suggestion" ("initiative_id", "id")
1122 ON DELETE CASCADE ON UPDATE CASCADE,
1123 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
1124 "event" != 'issue_state_changed' OR (
1125 "member_id" ISNULL AND
1126 "issue_id" NOTNULL AND
1127 "state" NOTNULL AND
1128 "initiative_id" ISNULL AND
1129 "draft_id" ISNULL AND
1130 "suggestion_id" ISNULL )),
1131 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
1132 "event" NOT IN (
1133 'initiative_created_in_new_issue',
1134 'initiative_created_in_existing_issue',
1135 'initiative_revoked',
1136 'new_draft_created'
1137 ) OR (
1138 "member_id" NOTNULL AND
1139 "issue_id" NOTNULL AND
1140 "state" NOTNULL AND
1141 "initiative_id" NOTNULL AND
1142 "draft_id" NOTNULL AND
1143 "suggestion_id" ISNULL )),
1144 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
1145 "event" != 'suggestion_created' OR (
1146 "member_id" NOTNULL AND
1147 "issue_id" NOTNULL AND
1148 "state" NOTNULL AND
1149 "initiative_id" NOTNULL AND
1150 "draft_id" ISNULL AND
1151 "suggestion_id" NOTNULL )) );
1152 CREATE INDEX "event_occurrence_idx" ON "event" ("occurrence");
1154 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
1156 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
1157 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';
1158 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';
1159 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';
1162 CREATE TABLE "notification_sent" (
1163 "event_id" INT8 NOT NULL );
1164 CREATE UNIQUE INDEX "notification_sent_singleton_idx" ON "notification_sent" ((1));
1166 COMMENT ON TABLE "notification_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out';
1167 COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.';
1171 ----------------------------------------------
1172 -- Writing of history entries and event log --
1173 ----------------------------------------------
1176 CREATE FUNCTION "write_member_history_trigger"()
1177 RETURNS TRIGGER
1178 LANGUAGE 'plpgsql' VOLATILE AS $$
1179 BEGIN
1180 IF
1181 ( NEW."active" != OLD."active" OR
1182 NEW."name" != OLD."name" ) AND
1183 OLD."activated" NOTNULL
1184 THEN
1185 INSERT INTO "member_history"
1186 ("member_id", "active", "name")
1187 VALUES (NEW."id", OLD."active", OLD."name");
1188 END IF;
1189 RETURN NULL;
1190 END;
1191 $$;
1193 CREATE TRIGGER "write_member_history"
1194 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1195 "write_member_history_trigger"();
1197 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
1198 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
1201 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
1202 RETURNS TRIGGER
1203 LANGUAGE 'plpgsql' VOLATILE AS $$
1204 BEGIN
1205 IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN
1206 INSERT INTO "event" ("event", "issue_id", "state")
1207 VALUES ('issue_state_changed', NEW."id", NEW."state");
1208 END IF;
1209 RETURN NULL;
1210 END;
1211 $$;
1213 CREATE TRIGGER "write_event_issue_state_changed"
1214 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
1215 "write_event_issue_state_changed_trigger"();
1217 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
1218 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
1221 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1222 RETURNS TRIGGER
1223 LANGUAGE 'plpgsql' VOLATILE AS $$
1224 DECLARE
1225 "initiative_row" "initiative"%ROWTYPE;
1226 "issue_row" "issue"%ROWTYPE;
1227 "event_v" "event_type";
1228 BEGIN
1229 SELECT * INTO "initiative_row" FROM "initiative"
1230 WHERE "id" = NEW."initiative_id";
1231 SELECT * INTO "issue_row" FROM "issue"
1232 WHERE "id" = "initiative_row"."issue_id";
1233 IF EXISTS (
1234 SELECT NULL FROM "draft"
1235 WHERE "initiative_id" = NEW."initiative_id"
1236 AND "id" != NEW."id"
1237 ) THEN
1238 "event_v" := 'new_draft_created';
1239 ELSE
1240 IF EXISTS (
1241 SELECT NULL FROM "initiative"
1242 WHERE "issue_id" = "initiative_row"."issue_id"
1243 AND "id" != "initiative_row"."id"
1244 ) THEN
1245 "event_v" := 'initiative_created_in_existing_issue';
1246 ELSE
1247 "event_v" := 'initiative_created_in_new_issue';
1248 END IF;
1249 END IF;
1250 INSERT INTO "event" (
1251 "event", "member_id",
1252 "issue_id", "state", "initiative_id", "draft_id"
1253 ) VALUES (
1254 "event_v",
1255 NEW."author_id",
1256 "initiative_row"."issue_id",
1257 "issue_row"."state",
1258 "initiative_row"."id",
1259 NEW."id" );
1260 RETURN NULL;
1261 END;
1262 $$;
1264 CREATE TRIGGER "write_event_initiative_or_draft_created"
1265 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
1266 "write_event_initiative_or_draft_created_trigger"();
1268 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
1269 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
1272 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
1273 RETURNS TRIGGER
1274 LANGUAGE 'plpgsql' VOLATILE AS $$
1275 DECLARE
1276 "issue_row" "issue"%ROWTYPE;
1277 "draft_id_v" "draft"."id"%TYPE;
1278 BEGIN
1279 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1280 SELECT * INTO "issue_row" FROM "issue"
1281 WHERE "id" = NEW."issue_id";
1282 SELECT "id" INTO "draft_id_v" FROM "current_draft"
1283 WHERE "initiative_id" = NEW."id";
1284 INSERT INTO "event" (
1285 "event", "member_id", "issue_id", "state", "initiative_id", "draft_id"
1286 ) VALUES (
1287 'initiative_revoked',
1288 NEW."revoked_by_member_id",
1289 NEW."issue_id",
1290 "issue_row"."state",
1291 NEW."id",
1292 "draft_id_v");
1293 END IF;
1294 RETURN NULL;
1295 END;
1296 $$;
1298 CREATE TRIGGER "write_event_initiative_revoked"
1299 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
1300 "write_event_initiative_revoked_trigger"();
1302 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
1303 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
1306 CREATE FUNCTION "write_event_suggestion_created_trigger"()
1307 RETURNS TRIGGER
1308 LANGUAGE 'plpgsql' VOLATILE AS $$
1309 DECLARE
1310 "initiative_row" "initiative"%ROWTYPE;
1311 "issue_row" "issue"%ROWTYPE;
1312 BEGIN
1313 SELECT * INTO "initiative_row" FROM "initiative"
1314 WHERE "id" = NEW."initiative_id";
1315 SELECT * INTO "issue_row" FROM "issue"
1316 WHERE "id" = "initiative_row"."issue_id";
1317 INSERT INTO "event" (
1318 "event", "member_id",
1319 "issue_id", "state", "initiative_id", "suggestion_id"
1320 ) VALUES (
1321 'suggestion_created',
1322 NEW."author_id",
1323 "initiative_row"."issue_id",
1324 "issue_row"."state",
1325 "initiative_row"."id",
1326 NEW."id" );
1327 RETURN NULL;
1328 END;
1329 $$;
1331 CREATE TRIGGER "write_event_suggestion_created"
1332 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1333 "write_event_suggestion_created_trigger"();
1335 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
1336 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1340 ----------------------------
1341 -- Additional constraints --
1342 ----------------------------
1345 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
1346 RETURNS TRIGGER
1347 LANGUAGE 'plpgsql' VOLATILE AS $$
1348 BEGIN
1349 IF NOT EXISTS (
1350 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
1351 ) THEN
1352 --RAISE 'Cannot create issue without an initial initiative.' USING
1353 -- ERRCODE = 'integrity_constraint_violation',
1354 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
1355 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
1356 END IF;
1357 RETURN NULL;
1358 END;
1359 $$;
1361 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
1362 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
1363 FOR EACH ROW EXECUTE PROCEDURE
1364 "issue_requires_first_initiative_trigger"();
1366 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
1367 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
1370 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
1371 RETURNS TRIGGER
1372 LANGUAGE 'plpgsql' VOLATILE AS $$
1373 DECLARE
1374 "reference_lost" BOOLEAN;
1375 BEGIN
1376 IF TG_OP = 'DELETE' THEN
1377 "reference_lost" := TRUE;
1378 ELSE
1379 "reference_lost" := NEW."issue_id" != OLD."issue_id";
1380 END IF;
1381 IF
1382 "reference_lost" AND NOT EXISTS (
1383 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
1384 )
1385 THEN
1386 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
1387 END IF;
1388 RETURN NULL;
1389 END;
1390 $$;
1392 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
1393 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1394 FOR EACH ROW EXECUTE PROCEDURE
1395 "last_initiative_deletes_issue_trigger"();
1397 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
1398 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
1401 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
1402 RETURNS TRIGGER
1403 LANGUAGE 'plpgsql' VOLATILE AS $$
1404 BEGIN
1405 IF NOT EXISTS (
1406 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
1407 ) THEN
1408 --RAISE 'Cannot create initiative without an initial draft.' USING
1409 -- ERRCODE = 'integrity_constraint_violation',
1410 -- HINT = 'Create issue, initiative and draft within the same transaction.';
1411 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
1412 END IF;
1413 RETURN NULL;
1414 END;
1415 $$;
1417 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
1418 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1419 FOR EACH ROW EXECUTE PROCEDURE
1420 "initiative_requires_first_draft_trigger"();
1422 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
1423 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
1426 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
1427 RETURNS TRIGGER
1428 LANGUAGE 'plpgsql' VOLATILE AS $$
1429 DECLARE
1430 "reference_lost" BOOLEAN;
1431 BEGIN
1432 IF TG_OP = 'DELETE' THEN
1433 "reference_lost" := TRUE;
1434 ELSE
1435 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
1436 END IF;
1437 IF
1438 "reference_lost" AND NOT EXISTS (
1439 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
1440 )
1441 THEN
1442 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
1443 END IF;
1444 RETURN NULL;
1445 END;
1446 $$;
1448 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
1449 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
1450 FOR EACH ROW EXECUTE PROCEDURE
1451 "last_draft_deletes_initiative_trigger"();
1453 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
1454 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
1457 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
1458 RETURNS TRIGGER
1459 LANGUAGE 'plpgsql' VOLATILE AS $$
1460 BEGIN
1461 IF NOT EXISTS (
1462 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
1463 ) THEN
1464 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
1465 END IF;
1466 RETURN NULL;
1467 END;
1468 $$;
1470 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
1471 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
1472 FOR EACH ROW EXECUTE PROCEDURE
1473 "suggestion_requires_first_opinion_trigger"();
1475 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1476 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1479 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1480 RETURNS TRIGGER
1481 LANGUAGE 'plpgsql' VOLATILE AS $$
1482 DECLARE
1483 "reference_lost" BOOLEAN;
1484 BEGIN
1485 IF TG_OP = 'DELETE' THEN
1486 "reference_lost" := TRUE;
1487 ELSE
1488 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1489 END IF;
1490 IF
1491 "reference_lost" AND NOT EXISTS (
1492 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1493 )
1494 THEN
1495 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1496 END IF;
1497 RETURN NULL;
1498 END;
1499 $$;
1501 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1502 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1503 FOR EACH ROW EXECUTE PROCEDURE
1504 "last_opinion_deletes_suggestion_trigger"();
1506 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1507 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1510 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()
1511 RETURNS TRIGGER
1512 LANGUAGE 'plpgsql' VOLATILE AS $$
1513 BEGIN
1514 DELETE FROM "direct_voter"
1515 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
1516 RETURN NULL;
1517 END;
1518 $$;
1520 CREATE TRIGGER "non_voter_deletes_direct_voter"
1521 AFTER INSERT OR UPDATE ON "non_voter"
1522 FOR EACH ROW EXECUTE PROCEDURE
1523 "non_voter_deletes_direct_voter_trigger"();
1525 COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"';
1526 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")';
1529 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()
1530 RETURNS TRIGGER
1531 LANGUAGE 'plpgsql' VOLATILE AS $$
1532 BEGIN
1533 DELETE FROM "non_voter"
1534 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
1535 RETURN NULL;
1536 END;
1537 $$;
1539 CREATE TRIGGER "direct_voter_deletes_non_voter"
1540 AFTER INSERT OR UPDATE ON "direct_voter"
1541 FOR EACH ROW EXECUTE PROCEDURE
1542 "direct_voter_deletes_non_voter_trigger"();
1544 COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"';
1545 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")';
1548 CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()
1549 RETURNS TRIGGER
1550 LANGUAGE 'plpgsql' VOLATILE AS $$
1551 BEGIN
1552 IF NEW."comment" ISNULL THEN
1553 NEW."comment_changed" := NULL;
1554 NEW."formatting_engine" := NULL;
1555 END IF;
1556 RETURN NEW;
1557 END;
1558 $$;
1560 CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"
1561 BEFORE INSERT OR UPDATE ON "direct_voter"
1562 FOR EACH ROW EXECUTE PROCEDURE
1563 "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();
1565 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"';
1566 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.';
1569 ---------------------------------------------------------------
1570 -- Ensure that votes are not modified when issues are frozen --
1571 ---------------------------------------------------------------
1573 -- NOTE: Frontends should ensure this anyway, but in case of programming
1574 -- errors the following triggers ensure data integrity.
1577 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1578 RETURNS TRIGGER
1579 LANGUAGE 'plpgsql' VOLATILE AS $$
1580 DECLARE
1581 "issue_id_v" "issue"."id"%TYPE;
1582 "issue_row" "issue"%ROWTYPE;
1583 BEGIN
1584 IF TG_RELID = 'direct_voter'::regclass AND TG_OP = 'UPDATE' THEN
1585 IF
1586 OLD."issue_id" = NEW."issue_id" AND
1587 OLD."member_id" = NEW."member_id" AND
1588 OLD."weight" = NEW."weight"
1589 THEN
1590 RETURN NULL; -- allows changing of voter comment
1591 END IF;
1592 END IF;
1593 IF TG_OP = 'DELETE' THEN
1594 "issue_id_v" := OLD."issue_id";
1595 ELSE
1596 "issue_id_v" := NEW."issue_id";
1597 END IF;
1598 SELECT INTO "issue_row" * FROM "issue"
1599 WHERE "id" = "issue_id_v" FOR SHARE;
1600 IF "issue_row"."closed" NOTNULL THEN
1601 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
1602 END IF;
1603 RETURN NULL;
1604 END;
1605 $$;
1607 CREATE TRIGGER "forbid_changes_on_closed_issue"
1608 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1609 FOR EACH ROW EXECUTE PROCEDURE
1610 "forbid_changes_on_closed_issue_trigger"();
1612 CREATE TRIGGER "forbid_changes_on_closed_issue"
1613 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1614 FOR EACH ROW EXECUTE PROCEDURE
1615 "forbid_changes_on_closed_issue_trigger"();
1617 CREATE TRIGGER "forbid_changes_on_closed_issue"
1618 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1619 FOR EACH ROW EXECUTE PROCEDURE
1620 "forbid_changes_on_closed_issue_trigger"();
1622 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"';
1623 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';
1624 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';
1625 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';
1629 --------------------------------------------------------------------
1630 -- Auto-retrieval of fields only needed for referential integrity --
1631 --------------------------------------------------------------------
1634 CREATE FUNCTION "autofill_issue_id_trigger"()
1635 RETURNS TRIGGER
1636 LANGUAGE 'plpgsql' VOLATILE AS $$
1637 BEGIN
1638 IF NEW."issue_id" ISNULL THEN
1639 SELECT "issue_id" INTO NEW."issue_id"
1640 FROM "initiative" WHERE "id" = NEW."initiative_id";
1641 END IF;
1642 RETURN NEW;
1643 END;
1644 $$;
1646 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1647 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1649 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1650 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1652 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1653 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1654 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1657 CREATE FUNCTION "autofill_initiative_id_trigger"()
1658 RETURNS TRIGGER
1659 LANGUAGE 'plpgsql' VOLATILE AS $$
1660 BEGIN
1661 IF NEW."initiative_id" ISNULL THEN
1662 SELECT "initiative_id" INTO NEW."initiative_id"
1663 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1664 END IF;
1665 RETURN NEW;
1666 END;
1667 $$;
1669 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1670 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1672 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1673 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1677 -----------------------------------------------------
1678 -- Automatic calculation of certain default values --
1679 -----------------------------------------------------
1682 CREATE FUNCTION "copy_timings_trigger"()
1683 RETURNS TRIGGER
1684 LANGUAGE 'plpgsql' VOLATILE AS $$
1685 DECLARE
1686 "policy_row" "policy"%ROWTYPE;
1687 BEGIN
1688 SELECT * INTO "policy_row" FROM "policy"
1689 WHERE "id" = NEW."policy_id";
1690 IF NEW."admission_time" ISNULL THEN
1691 NEW."admission_time" := "policy_row"."admission_time";
1692 END IF;
1693 IF NEW."discussion_time" ISNULL THEN
1694 NEW."discussion_time" := "policy_row"."discussion_time";
1695 END IF;
1696 IF NEW."verification_time" ISNULL THEN
1697 NEW."verification_time" := "policy_row"."verification_time";
1698 END IF;
1699 IF NEW."voting_time" ISNULL THEN
1700 NEW."voting_time" := "policy_row"."voting_time";
1701 END IF;
1702 RETURN NEW;
1703 END;
1704 $$;
1706 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1707 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1709 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1710 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1713 CREATE FUNCTION "default_for_draft_id_trigger"()
1714 RETURNS TRIGGER
1715 LANGUAGE 'plpgsql' VOLATILE AS $$
1716 BEGIN
1717 IF NEW."draft_id" ISNULL THEN
1718 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1719 WHERE "initiative_id" = NEW."initiative_id";
1720 END IF;
1721 RETURN NEW;
1722 END;
1723 $$;
1725 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"
1726 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1727 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1728 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1730 COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';
1731 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';
1732 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';
1736 ----------------------------------------
1737 -- Automatic creation of dependencies --
1738 ----------------------------------------
1741 CREATE FUNCTION "autocreate_interest_trigger"()
1742 RETURNS TRIGGER
1743 LANGUAGE 'plpgsql' VOLATILE AS $$
1744 BEGIN
1745 IF NOT EXISTS (
1746 SELECT NULL FROM "initiative" JOIN "interest"
1747 ON "initiative"."issue_id" = "interest"."issue_id"
1748 WHERE "initiative"."id" = NEW."initiative_id"
1749 AND "interest"."member_id" = NEW."member_id"
1750 ) THEN
1751 BEGIN
1752 INSERT INTO "interest" ("issue_id", "member_id")
1753 SELECT "issue_id", NEW."member_id"
1754 FROM "initiative" WHERE "id" = NEW."initiative_id";
1755 EXCEPTION WHEN unique_violation THEN END;
1756 END IF;
1757 RETURN NEW;
1758 END;
1759 $$;
1761 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1762 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1764 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1765 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';
1768 CREATE FUNCTION "autocreate_supporter_trigger"()
1769 RETURNS TRIGGER
1770 LANGUAGE 'plpgsql' VOLATILE AS $$
1771 BEGIN
1772 IF NOT EXISTS (
1773 SELECT NULL FROM "suggestion" JOIN "supporter"
1774 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1775 WHERE "suggestion"."id" = NEW."suggestion_id"
1776 AND "supporter"."member_id" = NEW."member_id"
1777 ) THEN
1778 BEGIN
1779 INSERT INTO "supporter" ("initiative_id", "member_id")
1780 SELECT "initiative_id", NEW."member_id"
1781 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1782 EXCEPTION WHEN unique_violation THEN END;
1783 END IF;
1784 RETURN NEW;
1785 END;
1786 $$;
1788 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1789 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1791 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1792 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.';
1796 ------------------------------------------
1797 -- Views and helper functions for views --
1798 ------------------------------------------
1801 CREATE VIEW "unit_delegation" AS
1802 SELECT
1803 "unit"."id" AS "unit_id",
1804 "delegation"."id",
1805 "delegation"."truster_id",
1806 "delegation"."trustee_id",
1807 "delegation"."scope"
1808 FROM "unit"
1809 JOIN "delegation"
1810 ON "delegation"."unit_id" = "unit"."id"
1811 JOIN "member"
1812 ON "delegation"."truster_id" = "member"."id"
1813 JOIN "privilege"
1814 ON "delegation"."unit_id" = "privilege"."unit_id"
1815 AND "delegation"."truster_id" = "privilege"."member_id"
1816 WHERE "member"."active" AND "privilege"."voting_right";
1818 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
1821 CREATE VIEW "area_delegation" AS
1822 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1823 "area"."id" AS "area_id",
1824 "delegation"."id",
1825 "delegation"."truster_id",
1826 "delegation"."trustee_id",
1827 "delegation"."scope"
1828 FROM "area"
1829 JOIN "delegation"
1830 ON "delegation"."unit_id" = "area"."unit_id"
1831 OR "delegation"."area_id" = "area"."id"
1832 JOIN "member"
1833 ON "delegation"."truster_id" = "member"."id"
1834 JOIN "privilege"
1835 ON "area"."unit_id" = "privilege"."unit_id"
1836 AND "delegation"."truster_id" = "privilege"."member_id"
1837 WHERE "member"."active" AND "privilege"."voting_right"
1838 ORDER BY
1839 "area"."id",
1840 "delegation"."truster_id",
1841 "delegation"."scope" DESC;
1843 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
1846 CREATE VIEW "issue_delegation" AS
1847 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1848 "issue"."id" AS "issue_id",
1849 "delegation"."id",
1850 "delegation"."truster_id",
1851 "delegation"."trustee_id",
1852 "delegation"."scope"
1853 FROM "issue"
1854 JOIN "area"
1855 ON "area"."id" = "issue"."area_id"
1856 JOIN "delegation"
1857 ON "delegation"."unit_id" = "area"."unit_id"
1858 OR "delegation"."area_id" = "area"."id"
1859 OR "delegation"."issue_id" = "issue"."id"
1860 JOIN "member"
1861 ON "delegation"."truster_id" = "member"."id"
1862 JOIN "privilege"
1863 ON "area"."unit_id" = "privilege"."unit_id"
1864 AND "delegation"."truster_id" = "privilege"."member_id"
1865 WHERE "member"."active" AND "privilege"."voting_right"
1866 ORDER BY
1867 "issue"."id",
1868 "delegation"."truster_id",
1869 "delegation"."scope" DESC;
1871 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
1874 CREATE FUNCTION "membership_weight_with_skipping"
1875 ( "area_id_p" "area"."id"%TYPE,
1876 "member_id_p" "member"."id"%TYPE,
1877 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1878 RETURNS INT4
1879 LANGUAGE 'plpgsql' STABLE AS $$
1880 DECLARE
1881 "sum_v" INT4;
1882 "delegation_row" "area_delegation"%ROWTYPE;
1883 BEGIN
1884 "sum_v" := 1;
1885 FOR "delegation_row" IN
1886 SELECT "area_delegation".*
1887 FROM "area_delegation" LEFT JOIN "membership"
1888 ON "membership"."area_id" = "area_id_p"
1889 AND "membership"."member_id" = "area_delegation"."truster_id"
1890 WHERE "area_delegation"."area_id" = "area_id_p"
1891 AND "area_delegation"."trustee_id" = "member_id_p"
1892 AND "membership"."member_id" ISNULL
1893 LOOP
1894 IF NOT
1895 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1896 THEN
1897 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1898 "area_id_p",
1899 "delegation_row"."truster_id",
1900 "skip_member_ids_p" || "delegation_row"."truster_id"
1901 );
1902 END IF;
1903 END LOOP;
1904 RETURN "sum_v";
1905 END;
1906 $$;
1908 COMMENT ON FUNCTION "membership_weight_with_skipping"
1909 ( "area"."id"%TYPE,
1910 "member"."id"%TYPE,
1911 INT4[] )
1912 IS 'Helper function for "membership_weight" function';
1915 CREATE FUNCTION "membership_weight"
1916 ( "area_id_p" "area"."id"%TYPE,
1917 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1918 RETURNS INT4
1919 LANGUAGE 'plpgsql' STABLE AS $$
1920 BEGIN
1921 RETURN "membership_weight_with_skipping"(
1922 "area_id_p",
1923 "member_id_p",
1924 ARRAY["member_id_p"]
1925 );
1926 END;
1927 $$;
1929 COMMENT ON FUNCTION "membership_weight"
1930 ( "area"."id"%TYPE,
1931 "member"."id"%TYPE )
1932 IS 'Calculates the potential voting weight of a member in a given area';
1935 CREATE VIEW "member_count_view" AS
1936 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1938 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1941 CREATE VIEW "unit_member_count" AS
1942 SELECT
1943 "unit"."id" AS "unit_id",
1944 count("member"."id") AS "member_count"
1945 FROM "unit"
1946 LEFT JOIN "privilege"
1947 ON "privilege"."unit_id" = "unit"."id"
1948 AND "privilege"."voting_right"
1949 LEFT JOIN "member"
1950 ON "member"."id" = "privilege"."member_id"
1951 AND "member"."active"
1952 GROUP BY "unit"."id";
1954 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
1957 CREATE VIEW "area_member_count" AS
1958 SELECT
1959 "area"."id" AS "area_id",
1960 count("member"."id") AS "direct_member_count",
1961 coalesce(
1962 sum(
1963 CASE WHEN "member"."id" NOTNULL THEN
1964 "membership_weight"("area"."id", "member"."id")
1965 ELSE 0 END
1966 )
1967 ) AS "member_weight"
1968 FROM "area"
1969 LEFT JOIN "membership"
1970 ON "area"."id" = "membership"."area_id"
1971 LEFT JOIN "privilege"
1972 ON "privilege"."unit_id" = "area"."unit_id"
1973 AND "privilege"."member_id" = "membership"."member_id"
1974 AND "privilege"."voting_right"
1975 LEFT JOIN "member"
1976 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
1977 AND "member"."active"
1978 GROUP BY "area"."id";
1980 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"';
1983 CREATE VIEW "opening_draft" AS
1984 SELECT "draft".* FROM (
1985 SELECT
1986 "initiative"."id" AS "initiative_id",
1987 min("draft"."id") AS "draft_id"
1988 FROM "initiative" JOIN "draft"
1989 ON "initiative"."id" = "draft"."initiative_id"
1990 GROUP BY "initiative"."id"
1991 ) AS "subquery"
1992 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1994 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1997 CREATE VIEW "current_draft" AS
1998 SELECT "draft".* FROM (
1999 SELECT
2000 "initiative"."id" AS "initiative_id",
2001 max("draft"."id") AS "draft_id"
2002 FROM "initiative" JOIN "draft"
2003 ON "initiative"."id" = "draft"."initiative_id"
2004 GROUP BY "initiative"."id"
2005 ) AS "subquery"
2006 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
2008 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
2011 CREATE VIEW "critical_opinion" AS
2012 SELECT * FROM "opinion"
2013 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
2014 OR ("degree" = -2 AND "fulfilled" = TRUE);
2016 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
2019 CREATE VIEW "battle_participant" AS
2020 SELECT "initiative"."id", "initiative"."issue_id"
2021 FROM "issue" JOIN "initiative"
2022 ON "issue"."id" = "initiative"."issue_id"
2023 WHERE "initiative"."admitted"
2024 UNION ALL
2025 SELECT NULL, "id" AS "issue_id"
2026 FROM "issue";
2028 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
2031 CREATE VIEW "battle_view" AS
2032 SELECT
2033 "issue"."id" AS "issue_id",
2034 "winning_initiative"."id" AS "winning_initiative_id",
2035 "losing_initiative"."id" AS "losing_initiative_id",
2036 sum(
2037 CASE WHEN
2038 coalesce("better_vote"."grade", 0) >
2039 coalesce("worse_vote"."grade", 0)
2040 THEN "direct_voter"."weight" ELSE 0 END
2041 ) AS "count"
2042 FROM "issue"
2043 LEFT JOIN "direct_voter"
2044 ON "issue"."id" = "direct_voter"."issue_id"
2045 JOIN "battle_participant" AS "winning_initiative"
2046 ON "issue"."id" = "winning_initiative"."issue_id"
2047 JOIN "battle_participant" AS "losing_initiative"
2048 ON "issue"."id" = "losing_initiative"."issue_id"
2049 LEFT JOIN "vote" AS "better_vote"
2050 ON "direct_voter"."member_id" = "better_vote"."member_id"
2051 AND "winning_initiative"."id" = "better_vote"."initiative_id"
2052 LEFT JOIN "vote" AS "worse_vote"
2053 ON "direct_voter"."member_id" = "worse_vote"."member_id"
2054 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
2055 WHERE "issue"."closed" NOTNULL
2056 AND "issue"."cleaned" ISNULL
2057 AND (
2058 "winning_initiative"."id" != "losing_initiative"."id" OR
2059 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
2060 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
2061 GROUP BY
2062 "issue"."id",
2063 "winning_initiative"."id",
2064 "losing_initiative"."id";
2066 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';
2069 CREATE VIEW "expired_session" AS
2070 SELECT * FROM "session" WHERE now() > "expiry";
2072 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
2073 DELETE FROM "session" WHERE "ident" = OLD."ident";
2075 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
2076 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
2079 CREATE VIEW "open_issue" AS
2080 SELECT * FROM "issue" WHERE "closed" ISNULL;
2082 COMMENT ON VIEW "open_issue" IS 'All open issues';
2085 CREATE VIEW "issue_with_ranks_missing" AS
2086 SELECT * FROM "issue"
2087 WHERE "fully_frozen" NOTNULL
2088 AND "closed" NOTNULL
2089 AND "ranks_available" = FALSE;
2091 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
2094 CREATE VIEW "member_contingent" AS
2095 SELECT
2096 "member"."id" AS "member_id",
2097 "contingent"."time_frame",
2098 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
2099 (
2100 SELECT count(1) FROM "draft"
2101 WHERE "draft"."author_id" = "member"."id"
2102 AND "draft"."created" > now() - "contingent"."time_frame"
2103 ) + (
2104 SELECT count(1) FROM "suggestion"
2105 WHERE "suggestion"."author_id" = "member"."id"
2106 AND "suggestion"."created" > now() - "contingent"."time_frame"
2107 )
2108 ELSE NULL END AS "text_entry_count",
2109 "contingent"."text_entry_limit",
2110 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
2111 SELECT count(1) FROM "opening_draft"
2112 WHERE "opening_draft"."author_id" = "member"."id"
2113 AND "opening_draft"."created" > now() - "contingent"."time_frame"
2114 ) ELSE NULL END AS "initiative_count",
2115 "contingent"."initiative_limit"
2116 FROM "member" CROSS JOIN "contingent";
2118 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
2120 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
2121 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
2124 CREATE VIEW "member_contingent_left" AS
2125 SELECT
2126 "member_id",
2127 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
2128 max("initiative_limit" - "initiative_count") AS "initiatives_left"
2129 FROM "member_contingent" GROUP BY "member_id";
2131 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.';
2134 CREATE VIEW "event_seen_by_member" AS
2135 SELECT
2136 "member"."id" AS "seen_by_member_id",
2137 CASE WHEN "event"."state" IN (
2138 'voting',
2139 'finished_without_winner',
2140 'finished_with_winner'
2141 ) THEN
2142 'voting'::"notify_level"
2143 ELSE
2144 CASE WHEN "event"."state" IN (
2145 'verification',
2146 'canceled_after_revocation_during_verification',
2147 'canceled_no_initiative_admitted'
2148 ) THEN
2149 'verification'::"notify_level"
2150 ELSE
2151 CASE WHEN "event"."state" IN (
2152 'discussion',
2153 'canceled_after_revocation_during_discussion'
2154 ) THEN
2155 'discussion'::"notify_level"
2156 ELSE
2157 'all'::"notify_level"
2158 END
2159 END
2160 END AS "notify_level",
2161 "event".*
2162 FROM "member" CROSS JOIN "event"
2163 LEFT JOIN "issue"
2164 ON "event"."issue_id" = "issue"."id"
2165 LEFT JOIN "membership"
2166 ON "member"."id" = "membership"."member_id"
2167 AND "issue"."area_id" = "membership"."area_id"
2168 LEFT JOIN "interest"
2169 ON "member"."id" = "interest"."member_id"
2170 AND "event"."issue_id" = "interest"."issue_id"
2171 LEFT JOIN "supporter"
2172 ON "member"."id" = "supporter"."member_id"
2173 AND "event"."initiative_id" = "supporter"."initiative_id"
2174 LEFT JOIN "ignored_member"
2175 ON "member"."id" = "ignored_member"."member_id"
2176 AND "event"."member_id" = "ignored_member"."other_member_id"
2177 LEFT JOIN "ignored_initiative"
2178 ON "member"."id" = "ignored_initiative"."member_id"
2179 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2180 WHERE (
2181 "supporter"."member_id" NOTNULL OR
2182 "interest"."member_id" NOTNULL OR
2183 ( "membership"."member_id" NOTNULL AND
2184 "event"."event" IN (
2185 'issue_state_changed',
2186 'initiative_created_in_new_issue',
2187 'initiative_created_in_existing_issue',
2188 'initiative_revoked' ) ) )
2189 AND "ignored_member"."member_id" ISNULL
2190 AND "ignored_initiative"."member_id" ISNULL;
2192 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"';
2195 CREATE VIEW "selected_event_seen_by_member" AS
2196 SELECT
2197 "member"."id" AS "seen_by_member_id",
2198 CASE WHEN "event"."state" IN (
2199 'voting',
2200 'finished_without_winner',
2201 'finished_with_winner'
2202 ) THEN
2203 'voting'::"notify_level"
2204 ELSE
2205 CASE WHEN "event"."state" IN (
2206 'verification',
2207 'canceled_after_revocation_during_verification',
2208 'canceled_no_initiative_admitted'
2209 ) THEN
2210 'verification'::"notify_level"
2211 ELSE
2212 CASE WHEN "event"."state" IN (
2213 'discussion',
2214 'canceled_after_revocation_during_discussion'
2215 ) THEN
2216 'discussion'::"notify_level"
2217 ELSE
2218 'all'::"notify_level"
2219 END
2220 END
2221 END AS "notify_level",
2222 "event".*
2223 FROM "member" CROSS JOIN "event"
2224 LEFT JOIN "issue"
2225 ON "event"."issue_id" = "issue"."id"
2226 LEFT JOIN "membership"
2227 ON "member"."id" = "membership"."member_id"
2228 AND "issue"."area_id" = "membership"."area_id"
2229 LEFT JOIN "interest"
2230 ON "member"."id" = "interest"."member_id"
2231 AND "event"."issue_id" = "interest"."issue_id"
2232 LEFT JOIN "supporter"
2233 ON "member"."id" = "supporter"."member_id"
2234 AND "event"."initiative_id" = "supporter"."initiative_id"
2235 LEFT JOIN "ignored_member"
2236 ON "member"."id" = "ignored_member"."member_id"
2237 AND "event"."member_id" = "ignored_member"."other_member_id"
2238 LEFT JOIN "ignored_initiative"
2239 ON "member"."id" = "ignored_initiative"."member_id"
2240 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2241 WHERE (
2242 ( "member"."notify_level" >= 'all' ) OR
2243 ( "member"."notify_level" >= 'voting' AND
2244 "event"."state" IN (
2245 'voting',
2246 'finished_without_winner',
2247 'finished_with_winner' ) ) OR
2248 ( "member"."notify_level" >= 'verification' AND
2249 "event"."state" IN (
2250 'verification',
2251 'canceled_after_revocation_during_verification',
2252 'canceled_no_initiative_admitted' ) ) OR
2253 ( "member"."notify_level" >= 'discussion' AND
2254 "event"."state" IN (
2255 'discussion',
2256 'canceled_after_revocation_during_discussion' ) ) )
2257 AND (
2258 "supporter"."member_id" NOTNULL OR
2259 "interest"."member_id" NOTNULL OR
2260 ( "membership"."member_id" NOTNULL AND
2261 "event"."event" IN (
2262 'issue_state_changed',
2263 'initiative_created_in_new_issue',
2264 'initiative_created_in_existing_issue',
2265 'initiative_revoked' ) ) )
2266 AND "ignored_member"."member_id" ISNULL
2267 AND "ignored_initiative"."member_id" ISNULL;
2269 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"';
2272 CREATE TYPE "timeline_event" AS ENUM (
2273 'issue_created',
2274 'issue_canceled',
2275 'issue_accepted',
2276 'issue_half_frozen',
2277 'issue_finished_without_voting',
2278 'issue_voting_started',
2279 'issue_finished_after_voting',
2280 'initiative_created',
2281 'initiative_revoked',
2282 'draft_created',
2283 'suggestion_created');
2285 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)';
2288 CREATE VIEW "timeline_issue" AS
2289 SELECT
2290 "created" AS "occurrence",
2291 'issue_created'::"timeline_event" AS "event",
2292 "id" AS "issue_id"
2293 FROM "issue"
2294 UNION ALL
2295 SELECT
2296 "closed" AS "occurrence",
2297 'issue_canceled'::"timeline_event" AS "event",
2298 "id" AS "issue_id"
2299 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
2300 UNION ALL
2301 SELECT
2302 "accepted" AS "occurrence",
2303 'issue_accepted'::"timeline_event" AS "event",
2304 "id" AS "issue_id"
2305 FROM "issue" WHERE "accepted" NOTNULL
2306 UNION ALL
2307 SELECT
2308 "half_frozen" AS "occurrence",
2309 'issue_half_frozen'::"timeline_event" AS "event",
2310 "id" AS "issue_id"
2311 FROM "issue" WHERE "half_frozen" NOTNULL
2312 UNION ALL
2313 SELECT
2314 "fully_frozen" AS "occurrence",
2315 'issue_voting_started'::"timeline_event" AS "event",
2316 "id" AS "issue_id"
2317 FROM "issue"
2318 WHERE "fully_frozen" NOTNULL
2319 AND ("closed" ISNULL OR "closed" != "fully_frozen")
2320 UNION ALL
2321 SELECT
2322 "closed" AS "occurrence",
2323 CASE WHEN "fully_frozen" = "closed" THEN
2324 'issue_finished_without_voting'::"timeline_event"
2325 ELSE
2326 'issue_finished_after_voting'::"timeline_event"
2327 END AS "event",
2328 "id" AS "issue_id"
2329 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
2331 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)';
2334 CREATE VIEW "timeline_initiative" AS
2335 SELECT
2336 "created" AS "occurrence",
2337 'initiative_created'::"timeline_event" AS "event",
2338 "id" AS "initiative_id"
2339 FROM "initiative"
2340 UNION ALL
2341 SELECT
2342 "revoked" AS "occurrence",
2343 'initiative_revoked'::"timeline_event" AS "event",
2344 "id" AS "initiative_id"
2345 FROM "initiative" WHERE "revoked" NOTNULL;
2347 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)';
2350 CREATE VIEW "timeline_draft" AS
2351 SELECT
2352 "created" AS "occurrence",
2353 'draft_created'::"timeline_event" AS "event",
2354 "id" AS "draft_id"
2355 FROM "draft";
2357 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)';
2360 CREATE VIEW "timeline_suggestion" AS
2361 SELECT
2362 "created" AS "occurrence",
2363 'suggestion_created'::"timeline_event" AS "event",
2364 "id" AS "suggestion_id"
2365 FROM "suggestion";
2367 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)';
2370 CREATE VIEW "timeline" AS
2371 SELECT
2372 "occurrence",
2373 "event",
2374 "issue_id",
2375 NULL AS "initiative_id",
2376 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
2377 NULL::INT8 AS "suggestion_id"
2378 FROM "timeline_issue"
2379 UNION ALL
2380 SELECT
2381 "occurrence",
2382 "event",
2383 NULL AS "issue_id",
2384 "initiative_id",
2385 NULL AS "draft_id",
2386 NULL AS "suggestion_id"
2387 FROM "timeline_initiative"
2388 UNION ALL
2389 SELECT
2390 "occurrence",
2391 "event",
2392 NULL AS "issue_id",
2393 NULL AS "initiative_id",
2394 "draft_id",
2395 NULL AS "suggestion_id"
2396 FROM "timeline_draft"
2397 UNION ALL
2398 SELECT
2399 "occurrence",
2400 "event",
2401 NULL AS "issue_id",
2402 NULL AS "initiative_id",
2403 NULL AS "draft_id",
2404 "suggestion_id"
2405 FROM "timeline_suggestion";
2407 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)';
2411 ------------------------------------------------------
2412 -- Row set returning function for delegation chains --
2413 ------------------------------------------------------
2416 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
2417 ('first', 'intermediate', 'last', 'repetition');
2419 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
2422 CREATE TYPE "delegation_chain_row" AS (
2423 "index" INT4,
2424 "member_id" INT4,
2425 "member_valid" BOOLEAN,
2426 "participation" BOOLEAN,
2427 "overridden" BOOLEAN,
2428 "scope_in" "delegation_scope",
2429 "scope_out" "delegation_scope",
2430 "disabled_out" BOOLEAN,
2431 "loop" "delegation_chain_loop_tag" );
2433 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';
2435 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
2436 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';
2437 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
2438 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
2439 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
2440 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
2441 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
2444 CREATE FUNCTION "delegation_chain_for_closed_issue"
2445 ( "member_id_p" "member"."id"%TYPE,
2446 "issue_id_p" "issue"."id"%TYPE )
2447 RETURNS SETOF "delegation_chain_row"
2448 LANGUAGE 'plpgsql' STABLE AS $$
2449 DECLARE
2450 "output_row" "delegation_chain_row";
2451 "direct_voter_row" "direct_voter"%ROWTYPE;
2452 "delegating_voter_row" "delegating_voter"%ROWTYPE;
2453 BEGIN
2454 "output_row"."index" := 0;
2455 "output_row"."member_id" := "member_id_p";
2456 "output_row"."member_valid" := TRUE;
2457 "output_row"."participation" := FALSE;
2458 "output_row"."overridden" := FALSE;
2459 "output_row"."disabled_out" := FALSE;
2460 LOOP
2461 SELECT INTO "direct_voter_row" * FROM "direct_voter"
2462 WHERE "issue_id" = "issue_id_p"
2463 AND "member_id" = "output_row"."member_id";
2464 IF "direct_voter_row"."member_id" NOTNULL THEN
2465 "output_row"."participation" := TRUE;
2466 "output_row"."scope_out" := NULL;
2467 "output_row"."disabled_out" := NULL;
2468 RETURN NEXT "output_row";
2469 RETURN;
2470 END IF;
2471 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"
2472 WHERE "issue_id" = "issue_id_p"
2473 AND "member_id" = "output_row"."member_id";
2474 IF "delegating_voter_row"."member_id" ISNULL THEN
2475 RETURN;
2476 END IF;
2477 "output_row"."scope_out" := "delegating_voter_row"."scope";
2478 RETURN NEXT "output_row";
2479 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];
2480 "output_row"."scope_in" := "output_row"."scope_out";
2481 END LOOP;
2482 END;
2483 $$;
2485 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"
2486 ( "member"."id"%TYPE,
2487 "member"."id"%TYPE )
2488 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';
2491 CREATE FUNCTION "delegation_chain"
2492 ( "member_id_p" "member"."id"%TYPE,
2493 "unit_id_p" "unit"."id"%TYPE,
2494 "area_id_p" "area"."id"%TYPE,
2495 "issue_id_p" "issue"."id"%TYPE,
2496 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2497 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2498 RETURNS SETOF "delegation_chain_row"
2499 LANGUAGE 'plpgsql' STABLE AS $$
2500 DECLARE
2501 "scope_v" "delegation_scope";
2502 "unit_id_v" "unit"."id"%TYPE;
2503 "area_id_v" "area"."id"%TYPE;
2504 "issue_row" "issue"%ROWTYPE;
2505 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2506 "loop_member_id_v" "member"."id"%TYPE;
2507 "output_row" "delegation_chain_row";
2508 "output_rows" "delegation_chain_row"[];
2509 "simulate_v" BOOLEAN;
2510 "simulate_here_v" BOOLEAN;
2511 "delegation_row" "delegation"%ROWTYPE;
2512 "row_count" INT4;
2513 "i" INT4;
2514 "loop_v" BOOLEAN;
2515 BEGIN
2516 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
2517 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
2518 END IF;
2519 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
2520 "simulate_v" := TRUE;
2521 ELSE
2522 "simulate_v" := FALSE;
2523 END IF;
2524 IF
2525 "unit_id_p" NOTNULL AND
2526 "area_id_p" ISNULL AND
2527 "issue_id_p" ISNULL
2528 THEN
2529 "scope_v" := 'unit';
2530 "unit_id_v" := "unit_id_p";
2531 ELSIF
2532 "unit_id_p" ISNULL AND
2533 "area_id_p" NOTNULL AND
2534 "issue_id_p" ISNULL
2535 THEN
2536 "scope_v" := 'area';
2537 "area_id_v" := "area_id_p";
2538 SELECT "unit_id" INTO "unit_id_v"
2539 FROM "area" WHERE "id" = "area_id_v";
2540 ELSIF
2541 "unit_id_p" ISNULL AND
2542 "area_id_p" ISNULL AND
2543 "issue_id_p" NOTNULL
2544 THEN
2545 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
2546 IF "issue_row"."id" ISNULL THEN
2547 RETURN;
2548 END IF;
2549 IF "issue_row"."closed" NOTNULL THEN
2550 IF "simulate_v" THEN
2551 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
2552 END IF;
2553 FOR "output_row" IN
2554 SELECT * FROM
2555 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
2556 LOOP
2557 RETURN NEXT "output_row";
2558 END LOOP;
2559 RETURN;
2560 END IF;
2561 "scope_v" := 'issue';
2562 SELECT "area_id" INTO "area_id_v"
2563 FROM "issue" WHERE "id" = "issue_id_p";
2564 SELECT "unit_id" INTO "unit_id_v"
2565 FROM "area" WHERE "id" = "area_id_v";
2566 ELSE
2567 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2568 END IF;
2569 "visited_member_ids" := '{}';
2570 "loop_member_id_v" := NULL;
2571 "output_rows" := '{}';
2572 "output_row"."index" := 0;
2573 "output_row"."member_id" := "member_id_p";
2574 "output_row"."member_valid" := TRUE;
2575 "output_row"."participation" := FALSE;
2576 "output_row"."overridden" := FALSE;
2577 "output_row"."disabled_out" := FALSE;
2578 "output_row"."scope_out" := NULL;
2579 LOOP
2580 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2581 "loop_member_id_v" := "output_row"."member_id";
2582 ELSE
2583 "visited_member_ids" :=
2584 "visited_member_ids" || "output_row"."member_id";
2585 END IF;
2586 IF "output_row"."participation" ISNULL THEN
2587 "output_row"."overridden" := NULL;
2588 ELSIF "output_row"."participation" THEN
2589 "output_row"."overridden" := TRUE;
2590 END IF;
2591 "output_row"."scope_in" := "output_row"."scope_out";
2592 "output_row"."member_valid" := EXISTS (
2593 SELECT NULL FROM "member" JOIN "privilege"
2594 ON "privilege"."member_id" = "member"."id"
2595 AND "privilege"."unit_id" = "unit_id_v"
2596 WHERE "id" = "output_row"."member_id"
2597 AND "member"."active" AND "privilege"."voting_right"
2598 );
2599 "simulate_here_v" := (
2600 "simulate_v" AND
2601 "output_row"."member_id" = "member_id_p"
2602 );
2603 "delegation_row" := ROW(NULL);
2604 IF "output_row"."member_valid" OR "simulate_here_v" THEN
2605 IF "scope_v" = 'unit' THEN
2606 IF NOT "simulate_here_v" THEN
2607 SELECT * INTO "delegation_row" FROM "delegation"
2608 WHERE "truster_id" = "output_row"."member_id"
2609 AND "unit_id" = "unit_id_v";
2610 END IF;
2611 ELSIF "scope_v" = 'area' THEN
2612 "output_row"."participation" := EXISTS (
2613 SELECT NULL FROM "membership"
2614 WHERE "area_id" = "area_id_p"
2615 AND "member_id" = "output_row"."member_id"
2616 );
2617 IF "simulate_here_v" THEN
2618 IF "simulate_trustee_id_p" ISNULL THEN
2619 SELECT * INTO "delegation_row" FROM "delegation"
2620 WHERE "truster_id" = "output_row"."member_id"
2621 AND "unit_id" = "unit_id_v";
2622 END IF;
2623 ELSE
2624 SELECT * INTO "delegation_row" FROM "delegation"
2625 WHERE "truster_id" = "output_row"."member_id"
2626 AND (
2627 "unit_id" = "unit_id_v" OR
2628 "area_id" = "area_id_v"
2629 )
2630 ORDER BY "scope" DESC;
2631 END IF;
2632 ELSIF "scope_v" = 'issue' THEN
2633 IF "issue_row"."fully_frozen" ISNULL THEN
2634 "output_row"."participation" := EXISTS (
2635 SELECT NULL FROM "interest"
2636 WHERE "issue_id" = "issue_id_p"
2637 AND "member_id" = "output_row"."member_id"
2638 );
2639 ELSE
2640 IF "output_row"."member_id" = "member_id_p" THEN
2641 "output_row"."participation" := EXISTS (
2642 SELECT NULL FROM "direct_voter"
2643 WHERE "issue_id" = "issue_id_p"
2644 AND "member_id" = "output_row"."member_id"
2645 );
2646 ELSE
2647 "output_row"."participation" := NULL;
2648 END IF;
2649 END IF;
2650 IF "simulate_here_v" THEN
2651 IF "simulate_trustee_id_p" ISNULL THEN
2652 SELECT * INTO "delegation_row" FROM "delegation"
2653 WHERE "truster_id" = "output_row"."member_id"
2654 AND (
2655 "unit_id" = "unit_id_v" OR
2656 "area_id" = "area_id_v"
2657 )
2658 ORDER BY "scope" DESC;
2659 END IF;
2660 ELSE
2661 SELECT * INTO "delegation_row" FROM "delegation"
2662 WHERE "truster_id" = "output_row"."member_id"
2663 AND (
2664 "unit_id" = "unit_id_v" OR
2665 "area_id" = "area_id_v" OR
2666 "issue_id" = "issue_id_p"
2667 )
2668 ORDER BY "scope" DESC;
2669 END IF;
2670 END IF;
2671 ELSE
2672 "output_row"."participation" := FALSE;
2673 END IF;
2674 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
2675 "output_row"."scope_out" := "scope_v";
2676 "output_rows" := "output_rows" || "output_row";
2677 "output_row"."member_id" := "simulate_trustee_id_p";
2678 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2679 "output_row"."scope_out" := "delegation_row"."scope";
2680 "output_rows" := "output_rows" || "output_row";
2681 "output_row"."member_id" := "delegation_row"."trustee_id";
2682 ELSIF "delegation_row"."scope" NOTNULL THEN
2683 "output_row"."scope_out" := "delegation_row"."scope";
2684 "output_row"."disabled_out" := TRUE;
2685 "output_rows" := "output_rows" || "output_row";
2686 EXIT;
2687 ELSE
2688 "output_row"."scope_out" := NULL;
2689 "output_rows" := "output_rows" || "output_row";
2690 EXIT;
2691 END IF;
2692 EXIT WHEN "loop_member_id_v" NOTNULL;
2693 "output_row"."index" := "output_row"."index" + 1;
2694 END LOOP;
2695 "row_count" := array_upper("output_rows", 1);
2696 "i" := 1;
2697 "loop_v" := FALSE;
2698 LOOP
2699 "output_row" := "output_rows"["i"];
2700 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2701 IF "loop_v" THEN
2702 IF "i" + 1 = "row_count" THEN
2703 "output_row"."loop" := 'last';
2704 ELSIF "i" = "row_count" THEN
2705 "output_row"."loop" := 'repetition';
2706 ELSE
2707 "output_row"."loop" := 'intermediate';
2708 END IF;
2709 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2710 "output_row"."loop" := 'first';
2711 "loop_v" := TRUE;
2712 END IF;
2713 IF "scope_v" = 'unit' THEN
2714 "output_row"."participation" := NULL;
2715 END IF;
2716 RETURN NEXT "output_row";
2717 "i" := "i" + 1;
2718 END LOOP;
2719 RETURN;
2720 END;
2721 $$;
2723 COMMENT ON FUNCTION "delegation_chain"
2724 ( "member"."id"%TYPE,
2725 "unit"."id"%TYPE,
2726 "area"."id"%TYPE,
2727 "issue"."id"%TYPE,
2728 "member"."id"%TYPE,
2729 BOOLEAN )
2730 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
2734 ---------------------------------------------------------
2735 -- Single row returning function for delegation chains --
2736 ---------------------------------------------------------
2739 CREATE TYPE "delegation_info_loop_type" AS ENUM
2740 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');
2742 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''';
2745 CREATE TYPE "delegation_info_type" AS (
2746 "own_participation" BOOLEAN,
2747 "own_delegation_scope" "delegation_scope",
2748 "first_trustee_id" INT4,
2749 "first_trustee_participation" BOOLEAN,
2750 "first_trustee_ellipsis" BOOLEAN,
2751 "other_trustee_id" INT4,
2752 "other_trustee_participation" BOOLEAN,
2753 "other_trustee_ellipsis" BOOLEAN,
2754 "delegation_loop" "delegation_info_loop_type",
2755 "participating_member_id" INT4 );
2757 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';
2759 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';
2760 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';
2761 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';
2762 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
2763 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';
2764 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';
2765 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)';
2766 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';
2767 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';
2768 COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain';
2771 CREATE FUNCTION "delegation_info"
2772 ( "member_id_p" "member"."id"%TYPE,
2773 "unit_id_p" "unit"."id"%TYPE,
2774 "area_id_p" "area"."id"%TYPE,
2775 "issue_id_p" "issue"."id"%TYPE,
2776 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2777 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2778 RETURNS "delegation_info_type"
2779 LANGUAGE 'plpgsql' STABLE AS $$
2780 DECLARE
2781 "current_row" "delegation_chain_row";
2782 "result" "delegation_info_type";
2783 BEGIN
2784 "result"."own_participation" := FALSE;
2785 FOR "current_row" IN
2786 SELECT * FROM "delegation_chain"(
2787 "member_id_p",
2788 "unit_id_p", "area_id_p", "issue_id_p",
2789 "simulate_trustee_id_p", "simulate_default_p")
2790 LOOP
2791 IF
2792 "result"."participating_member_id" ISNULL AND
2793 "current_row"."participation"
2794 THEN
2795 "result"."participating_member_id" := "current_row"."member_id";
2796 END IF;
2797 IF "current_row"."member_id" = "member_id_p" THEN
2798 "result"."own_participation" := "current_row"."participation";
2799 "result"."own_delegation_scope" := "current_row"."scope_out";
2800 IF "current_row"."loop" = 'first' THEN
2801 "result"."delegation_loop" := 'own';
2802 END IF;
2803 ELSIF
2804 "current_row"."member_valid" AND
2805 ( "current_row"."loop" ISNULL OR
2806 "current_row"."loop" != 'repetition' )
2807 THEN
2808 IF "result"."first_trustee_id" ISNULL THEN
2809 "result"."first_trustee_id" := "current_row"."member_id";
2810 "result"."first_trustee_participation" := "current_row"."participation";
2811 "result"."first_trustee_ellipsis" := FALSE;
2812 IF "current_row"."loop" = 'first' THEN
2813 "result"."delegation_loop" := 'first';
2814 END IF;
2815 ELSIF "result"."other_trustee_id" ISNULL THEN
2816 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
2817 "result"."other_trustee_id" := "current_row"."member_id";
2818 "result"."other_trustee_participation" := TRUE;
2819 "result"."other_trustee_ellipsis" := FALSE;
2820 IF "current_row"."loop" = 'first' THEN
2821 "result"."delegation_loop" := 'other';
2822 END IF;
2823 ELSE
2824 "result"."first_trustee_ellipsis" := TRUE;
2825 IF "current_row"."loop" = 'first' THEN
2826 "result"."delegation_loop" := 'first_ellipsis';
2827 END IF;
2828 END IF;
2829 ELSE
2830 "result"."other_trustee_ellipsis" := TRUE;
2831 IF "current_row"."loop" = 'first' THEN
2832 "result"."delegation_loop" := 'other_ellipsis';
2833 END IF;
2834 END IF;
2835 END IF;
2836 END LOOP;
2837 RETURN "result";
2838 END;
2839 $$;
2841 COMMENT ON FUNCTION "delegation_info"
2842 ( "member"."id"%TYPE,
2843 "unit"."id"%TYPE,
2844 "area"."id"%TYPE,
2845 "issue"."id"%TYPE,
2846 "member"."id"%TYPE,
2847 BOOLEAN )
2848 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
2852 ------------------------------
2853 -- Comparison by vote count --
2854 ------------------------------
2856 CREATE FUNCTION "vote_ratio"
2857 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
2858 "negative_votes_p" "initiative"."negative_votes"%TYPE )
2859 RETURNS FLOAT8
2860 LANGUAGE 'plpgsql' STABLE AS $$
2861 BEGIN
2862 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
2863 RETURN
2864 "positive_votes_p"::FLOAT8 /
2865 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
2866 ELSIF "positive_votes_p" > 0 THEN
2867 RETURN "positive_votes_p";
2868 ELSIF "negative_votes_p" > 0 THEN
2869 RETURN 1 - "negative_votes_p";
2870 ELSE
2871 RETURN 0.5;
2872 END IF;
2873 END;
2874 $$;
2876 COMMENT ON FUNCTION "vote_ratio"
2877 ( "initiative"."positive_votes"%TYPE,
2878 "initiative"."negative_votes"%TYPE )
2879 IS 'Returns a number, which can be used for comparison of initiatives based on count of approvals and disapprovals. Greater numbers indicate a better result. This function is NOT injective.';
2883 ------------------------------------------------
2884 -- Locking for snapshots and voting procedure --
2885 ------------------------------------------------
2888 CREATE FUNCTION "share_row_lock_issue_trigger"()
2889 RETURNS TRIGGER
2890 LANGUAGE 'plpgsql' VOLATILE AS $$
2891 BEGIN
2892 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2893 PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;
2894 END IF;
2895 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2896 PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;
2897 RETURN NEW;
2898 ELSE
2899 RETURN OLD;
2900 END IF;
2901 END;
2902 $$;
2904 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';
2907 CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()
2908 RETURNS TRIGGER
2909 LANGUAGE 'plpgsql' VOLATILE AS $$
2910 BEGIN
2911 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2912 PERFORM NULL FROM "issue"
2913 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2914 WHERE "initiative"."id" = OLD."initiative_id"
2915 FOR SHARE OF "issue";
2916 END IF;
2917 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2918 PERFORM NULL FROM "issue"
2919 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2920 WHERE "initiative"."id" = NEW."initiative_id"
2921 FOR SHARE OF "issue";
2922 RETURN NEW;
2923 ELSE
2924 RETURN OLD;
2925 END IF;
2926 END;
2927 $$;
2929 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';
2932 CREATE TRIGGER "share_row_lock_issue"
2933 BEFORE INSERT OR UPDATE OR DELETE ON "initiative"
2934 FOR EACH ROW EXECUTE PROCEDURE
2935 "share_row_lock_issue_trigger"();
2937 CREATE TRIGGER "share_row_lock_issue"
2938 BEFORE INSERT OR UPDATE OR DELETE ON "interest"
2939 FOR EACH ROW EXECUTE PROCEDURE
2940 "share_row_lock_issue_trigger"();
2942 CREATE TRIGGER "share_row_lock_issue"
2943 BEFORE INSERT OR UPDATE OR DELETE ON "supporter"
2944 FOR EACH ROW EXECUTE PROCEDURE
2945 "share_row_lock_issue_trigger"();
2947 CREATE TRIGGER "share_row_lock_issue_via_initiative"
2948 BEFORE INSERT OR UPDATE OR DELETE ON "opinion"
2949 FOR EACH ROW EXECUTE PROCEDURE
2950 "share_row_lock_issue_via_initiative_trigger"();
2952 CREATE TRIGGER "share_row_lock_issue"
2953 BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter"
2954 FOR EACH ROW EXECUTE PROCEDURE
2955 "share_row_lock_issue_trigger"();
2957 CREATE TRIGGER "share_row_lock_issue"
2958 BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter"
2959 FOR EACH ROW EXECUTE PROCEDURE
2960 "share_row_lock_issue_trigger"();
2962 CREATE TRIGGER "share_row_lock_issue"
2963 BEFORE INSERT OR UPDATE OR DELETE ON "vote"
2964 FOR EACH ROW EXECUTE PROCEDURE
2965 "share_row_lock_issue_trigger"();
2967 COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function';
2968 COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function';
2969 COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function';
2970 COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function';
2971 COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function';
2972 COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function';
2973 COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function';
2976 CREATE FUNCTION "lock_issue"
2977 ( "issue_id_p" "issue"."id"%TYPE )
2978 RETURNS VOID
2979 LANGUAGE 'plpgsql' VOLATILE AS $$
2980 BEGIN
2981 LOCK TABLE "member" IN SHARE MODE;
2982 LOCK TABLE "privilege" IN SHARE MODE;
2983 LOCK TABLE "membership" IN SHARE MODE;
2984 LOCK TABLE "policy" IN SHARE MODE;
2985 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2986 -- NOTE: The row-level exclusive lock in combination with the
2987 -- share_row_lock_issue(_via_initiative)_trigger functions (which
2988 -- acquire a row-level share lock on the issue) ensure that no data
2989 -- is changed, which could affect calculation of snapshots or
2990 -- counting of votes. Table "delegation" must be table-level-locked,
2991 -- as it also contains issue- and global-scope delegations.
2992 LOCK TABLE "delegation" IN SHARE MODE;
2993 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
2994 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
2995 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
2996 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
2997 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
2998 RETURN;
2999 END;
3000 $$;
3002 COMMENT ON FUNCTION "lock_issue"
3003 ( "issue"."id"%TYPE )
3004 IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';
3008 ------------------------------------------------------------------------
3009 -- Regular tasks, except calculcation of snapshots and voting results --
3010 ------------------------------------------------------------------------
3012 CREATE FUNCTION "check_activity"()
3013 RETURNS VOID
3014 LANGUAGE 'plpgsql' VOLATILE AS $$
3015 DECLARE
3016 "system_setting_row" "system_setting"%ROWTYPE;
3017 BEGIN
3018 SELECT * INTO "system_setting_row" FROM "system_setting";
3019 LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
3020 IF "system_setting_row"."member_ttl" NOTNULL THEN
3021 UPDATE "member" SET "active" = FALSE
3022 WHERE "active" = TRUE
3023 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
3024 END IF;
3025 RETURN;
3026 END;
3027 $$;
3029 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
3032 CREATE FUNCTION "calculate_member_counts"()
3033 RETURNS VOID
3034 LANGUAGE 'plpgsql' VOLATILE AS $$
3035 BEGIN
3036 LOCK TABLE "member" IN SHARE MODE;
3037 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
3038 LOCK TABLE "unit" IN EXCLUSIVE MODE;
3039 LOCK TABLE "area" IN EXCLUSIVE MODE;
3040 LOCK TABLE "privilege" IN SHARE MODE;
3041 LOCK TABLE "membership" IN SHARE MODE;
3042 DELETE FROM "member_count";
3043 INSERT INTO "member_count" ("total_count")
3044 SELECT "total_count" FROM "member_count_view";
3045 UPDATE "unit" SET "member_count" = "view"."member_count"
3046 FROM "unit_member_count" AS "view"
3047 WHERE "view"."unit_id" = "unit"."id";
3048 UPDATE "area" SET
3049 "direct_member_count" = "view"."direct_member_count",
3050 "member_weight" = "view"."member_weight"
3051 FROM "area_member_count" AS "view"
3052 WHERE "view"."area_id" = "area"."id";
3053 RETURN;
3054 END;
3055 $$;
3057 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"';
3061 ------------------------------
3062 -- Calculation of snapshots --
3063 ------------------------------
3065 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
3066 ( "issue_id_p" "issue"."id"%TYPE,
3067 "member_id_p" "member"."id"%TYPE,
3068 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3069 RETURNS "direct_population_snapshot"."weight"%TYPE
3070 LANGUAGE 'plpgsql' VOLATILE AS $$
3071 DECLARE
3072 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3073 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
3074 "weight_v" INT4;
3075 "sub_weight_v" INT4;
3076 BEGIN
3077 "weight_v" := 0;
3078 FOR "issue_delegation_row" IN
3079 SELECT * FROM "issue_delegation"
3080 WHERE "trustee_id" = "member_id_p"
3081 AND "issue_id" = "issue_id_p"
3082 LOOP
3083 IF NOT EXISTS (
3084 SELECT NULL FROM "direct_population_snapshot"
3085 WHERE "issue_id" = "issue_id_p"
3086 AND "event" = 'periodic'
3087 AND "member_id" = "issue_delegation_row"."truster_id"
3088 ) AND NOT EXISTS (
3089 SELECT NULL FROM "delegating_population_snapshot"
3090 WHERE "issue_id" = "issue_id_p"
3091 AND "event" = 'periodic'
3092 AND "member_id" = "issue_delegation_row"."truster_id"
3093 ) THEN
3094 "delegate_member_ids_v" :=
3095 "member_id_p" || "delegate_member_ids_p";
3096 INSERT INTO "delegating_population_snapshot" (
3097 "issue_id",
3098 "event",
3099 "member_id",
3100 "scope",
3101 "delegate_member_ids"
3102 ) VALUES (
3103 "issue_id_p",
3104 'periodic',
3105 "issue_delegation_row"."truster_id",
3106 "issue_delegation_row"."scope",
3107 "delegate_member_ids_v"
3108 );
3109 "sub_weight_v" := 1 +
3110 "weight_of_added_delegations_for_population_snapshot"(
3111 "issue_id_p",
3112 "issue_delegation_row"."truster_id",
3113 "delegate_member_ids_v"
3114 );
3115 UPDATE "delegating_population_snapshot"
3116 SET "weight" = "sub_weight_v"
3117 WHERE "issue_id" = "issue_id_p"
3118 AND "event" = 'periodic'
3119 AND "member_id" = "issue_delegation_row"."truster_id";
3120 "weight_v" := "weight_v" + "sub_weight_v";
3121 END IF;
3122 END LOOP;
3123 RETURN "weight_v";
3124 END;
3125 $$;
3127 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
3128 ( "issue"."id"%TYPE,
3129 "member"."id"%TYPE,
3130 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3131 IS 'Helper function for "create_population_snapshot" function';
3134 CREATE FUNCTION "create_population_snapshot"
3135 ( "issue_id_p" "issue"."id"%TYPE )
3136 RETURNS VOID
3137 LANGUAGE 'plpgsql' VOLATILE AS $$
3138 DECLARE
3139 "member_id_v" "member"."id"%TYPE;
3140 BEGIN
3141 DELETE FROM "direct_population_snapshot"
3142 WHERE "issue_id" = "issue_id_p"
3143 AND "event" = 'periodic';
3144 DELETE FROM "delegating_population_snapshot"
3145 WHERE "issue_id" = "issue_id_p"
3146 AND "event" = 'periodic';
3147 INSERT INTO "direct_population_snapshot"
3148 ("issue_id", "event", "member_id")
3149 SELECT
3150 "issue_id_p" AS "issue_id",
3151 'periodic'::"snapshot_event" AS "event",
3152 "member"."id" AS "member_id"
3153 FROM "issue"
3154 JOIN "area" ON "issue"."area_id" = "area"."id"
3155 JOIN "membership" ON "area"."id" = "membership"."area_id"
3156 JOIN "member" ON "membership"."member_id" = "member"."id"
3157 JOIN "privilege"
3158 ON "privilege"."unit_id" = "area"."unit_id"
3159 AND "privilege"."member_id" = "member"."id"
3160 WHERE "issue"."id" = "issue_id_p"
3161 AND "member"."active" AND "privilege"."voting_right"
3162 UNION
3163 SELECT
3164 "issue_id_p" AS "issue_id",
3165 'periodic'::"snapshot_event" AS "event",
3166 "member"."id" AS "member_id"
3167 FROM "issue"
3168 JOIN "area" ON "issue"."area_id" = "area"."id"
3169 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3170 JOIN "member" ON "interest"."member_id" = "member"."id"
3171 JOIN "privilege"
3172 ON "privilege"."unit_id" = "area"."unit_id"
3173 AND "privilege"."member_id" = "member"."id"
3174 WHERE "issue"."id" = "issue_id_p"
3175 AND "member"."active" AND "privilege"."voting_right";
3176 FOR "member_id_v" IN
3177 SELECT "member_id" FROM "direct_population_snapshot"
3178 WHERE "issue_id" = "issue_id_p"
3179 AND "event" = 'periodic'
3180 LOOP
3181 UPDATE "direct_population_snapshot" SET
3182 "weight" = 1 +
3183 "weight_of_added_delegations_for_population_snapshot"(
3184 "issue_id_p",
3185 "member_id_v",
3186 '{}'
3187 )
3188 WHERE "issue_id" = "issue_id_p"
3189 AND "event" = 'periodic'
3190 AND "member_id" = "member_id_v";
3191 END LOOP;
3192 RETURN;
3193 END;
3194 $$;
3196 COMMENT ON FUNCTION "create_population_snapshot"
3197 ( "issue"."id"%TYPE )
3198 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.';
3201 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3202 ( "issue_id_p" "issue"."id"%TYPE,
3203 "member_id_p" "member"."id"%TYPE,
3204 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3205 RETURNS "direct_interest_snapshot"."weight"%TYPE
3206 LANGUAGE 'plpgsql' VOLATILE AS $$
3207 DECLARE
3208 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3209 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
3210 "weight_v" INT4;
3211 "sub_weight_v" INT4;
3212 BEGIN
3213 "weight_v" := 0;
3214 FOR "issue_delegation_row" IN
3215 SELECT * FROM "issue_delegation"
3216 WHERE "trustee_id" = "member_id_p"
3217 AND "issue_id" = "issue_id_p"
3218 LOOP
3219 IF NOT EXISTS (
3220 SELECT NULL FROM "direct_interest_snapshot"
3221 WHERE "issue_id" = "issue_id_p"
3222 AND "event" = 'periodic'
3223 AND "member_id" = "issue_delegation_row"."truster_id"
3224 ) AND NOT EXISTS (
3225 SELECT NULL FROM "delegating_interest_snapshot"
3226 WHERE "issue_id" = "issue_id_p"
3227 AND "event" = 'periodic'
3228 AND "member_id" = "issue_delegation_row"."truster_id"
3229 ) THEN
3230 "delegate_member_ids_v" :=
3231 "member_id_p" || "delegate_member_ids_p";
3232 INSERT INTO "delegating_interest_snapshot" (
3233 "issue_id",
3234 "event",
3235 "member_id",
3236 "scope",
3237 "delegate_member_ids"
3238 ) VALUES (
3239 "issue_id_p",
3240 'periodic',
3241 "issue_delegation_row"."truster_id",
3242 "issue_delegation_row"."scope",
3243 "delegate_member_ids_v"
3244 );
3245 "sub_weight_v" := 1 +
3246 "weight_of_added_delegations_for_interest_snapshot"(
3247 "issue_id_p",
3248 "issue_delegation_row"."truster_id",
3249 "delegate_member_ids_v"
3250 );
3251 UPDATE "delegating_interest_snapshot"
3252 SET "weight" = "sub_weight_v"
3253 WHERE "issue_id" = "issue_id_p"
3254 AND "event" = 'periodic'
3255 AND "member_id" = "issue_delegation_row"."truster_id";
3256 "weight_v" := "weight_v" + "sub_weight_v";
3257 END IF;
3258 END LOOP;
3259 RETURN "weight_v";
3260 END;
3261 $$;
3263 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3264 ( "issue"."id"%TYPE,
3265 "member"."id"%TYPE,
3266 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3267 IS 'Helper function for "create_interest_snapshot" function';
3270 CREATE FUNCTION "create_interest_snapshot"
3271 ( "issue_id_p" "issue"."id"%TYPE )
3272 RETURNS VOID
3273 LANGUAGE 'plpgsql' VOLATILE AS $$
3274 DECLARE
3275 "member_id_v" "member"."id"%TYPE;
3276 BEGIN
3277 DELETE FROM "direct_interest_snapshot"
3278 WHERE "issue_id" = "issue_id_p"
3279 AND "event" = 'periodic';
3280 DELETE FROM "delegating_interest_snapshot"
3281 WHERE "issue_id" = "issue_id_p"
3282 AND "event" = 'periodic';
3283 DELETE FROM "direct_supporter_snapshot"
3284 WHERE "issue_id" = "issue_id_p"
3285 AND "event" = 'periodic';
3286 INSERT INTO "direct_interest_snapshot"
3287 ("issue_id", "event", "member_id")
3288 SELECT
3289 "issue_id_p" AS "issue_id",
3290 'periodic' AS "event",
3291 "member"."id" AS "member_id"
3292 FROM "issue"
3293 JOIN "area" ON "issue"."area_id" = "area"."id"
3294 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3295 JOIN "member" ON "interest"."member_id" = "member"."id"
3296 JOIN "privilege"
3297 ON "privilege"."unit_id" = "area"."unit_id"
3298 AND "privilege"."member_id" = "member"."id"
3299 WHERE "issue"."id" = "issue_id_p"
3300 AND "member"."active" AND "privilege"."voting_right";
3301 FOR "member_id_v" IN
3302 SELECT "member_id" FROM "direct_interest_snapshot"
3303 WHERE "issue_id" = "issue_id_p"
3304 AND "event" = 'periodic'
3305 LOOP
3306 UPDATE "direct_interest_snapshot" SET
3307 "weight" = 1 +
3308 "weight_of_added_delegations_for_interest_snapshot"(
3309 "issue_id_p",
3310 "member_id_v",
3311 '{}'
3312 )
3313 WHERE "issue_id" = "issue_id_p"
3314 AND "event" = 'periodic'
3315 AND "member_id" = "member_id_v";
3316 END LOOP;
3317 INSERT INTO "direct_supporter_snapshot"
3318 ( "issue_id", "initiative_id", "event", "member_id",
3319 "draft_id", "informed", "satisfied" )
3320 SELECT
3321 "issue_id_p" AS "issue_id",
3322 "initiative"."id" AS "initiative_id",
3323 'periodic' AS "event",
3324 "supporter"."member_id" AS "member_id",
3325 "supporter"."draft_id" AS "draft_id",
3326 "supporter"."draft_id" = "current_draft"."id" AS "informed",
3327 NOT EXISTS (
3328 SELECT NULL FROM "critical_opinion"
3329 WHERE "initiative_id" = "initiative"."id"
3330 AND "member_id" = "supporter"."member_id"
3331 ) AS "satisfied"
3332 FROM "initiative"
3333 JOIN "supporter"
3334 ON "supporter"."initiative_id" = "initiative"."id"
3335 JOIN "current_draft"
3336 ON "initiative"."id" = "current_draft"."initiative_id"
3337 JOIN "direct_interest_snapshot"
3338 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
3339 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
3340 AND "event" = 'periodic'
3341 WHERE "initiative"."issue_id" = "issue_id_p";
3342 RETURN;
3343 END;
3344 $$;
3346 COMMENT ON FUNCTION "create_interest_snapshot"
3347 ( "issue"."id"%TYPE )
3348 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.';
3351 CREATE FUNCTION "create_snapshot"
3352 ( "issue_id_p" "issue"."id"%TYPE )
3353 RETURNS VOID
3354 LANGUAGE 'plpgsql' VOLATILE AS $$
3355 DECLARE
3356 "initiative_id_v" "initiative"."id"%TYPE;
3357 "suggestion_id_v" "suggestion"."id"%TYPE;
3358 BEGIN
3359 PERFORM "lock_issue"("issue_id_p");
3360 PERFORM "create_population_snapshot"("issue_id_p");
3361 PERFORM "create_interest_snapshot"("issue_id_p");
3362 UPDATE "issue" SET
3363 "snapshot" = now(),
3364 "latest_snapshot_event" = 'periodic',
3365 "population" = (
3366 SELECT coalesce(sum("weight"), 0)
3367 FROM "direct_population_snapshot"
3368 WHERE "issue_id" = "issue_id_p"
3369 AND "event" = 'periodic'
3370 )
3371 WHERE "id" = "issue_id_p";
3372 FOR "initiative_id_v" IN
3373 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
3374 LOOP
3375 UPDATE "initiative" SET
3376 "supporter_count" = (
3377 SELECT coalesce(sum("di"."weight"), 0)
3378 FROM "direct_interest_snapshot" AS "di"
3379 JOIN "direct_supporter_snapshot" AS "ds"
3380 ON "di"."member_id" = "ds"."member_id"
3381 WHERE "di"."issue_id" = "issue_id_p"
3382 AND "di"."event" = 'periodic'
3383 AND "ds"."initiative_id" = "initiative_id_v"
3384 AND "ds"."event" = 'periodic'
3385 ),
3386 "informed_supporter_count" = (
3387 SELECT coalesce(sum("di"."weight"), 0)
3388 FROM "direct_interest_snapshot" AS "di"
3389 JOIN "direct_supporter_snapshot" AS "ds"
3390 ON "di"."member_id" = "ds"."member_id"
3391 WHERE "di"."issue_id" = "issue_id_p"
3392 AND "di"."event" = 'periodic'
3393 AND "ds"."initiative_id" = "initiative_id_v"
3394 AND "ds"."event" = 'periodic'
3395 AND "ds"."informed"
3396 ),
3397 "satisfied_supporter_count" = (
3398 SELECT coalesce(sum("di"."weight"), 0)
3399 FROM "direct_interest_snapshot" AS "di"
3400 JOIN "direct_supporter_snapshot" AS "ds"
3401 ON "di"."member_id" = "ds"."member_id"
3402 WHERE "di"."issue_id" = "issue_id_p"
3403 AND "di"."event" = 'periodic'
3404 AND "ds"."initiative_id" = "initiative_id_v"
3405 AND "ds"."event" = 'periodic'
3406 AND "ds"."satisfied"
3407 ),
3408 "satisfied_informed_supporter_count" = (
3409 SELECT coalesce(sum("di"."weight"), 0)
3410 FROM "direct_interest_snapshot" AS "di"
3411 JOIN "direct_supporter_snapshot" AS "ds"
3412 ON "di"."member_id" = "ds"."member_id"
3413 WHERE "di"."issue_id" = "issue_id_p"
3414 AND "di"."event" = 'periodic'
3415 AND "ds"."initiative_id" = "initiative_id_v"
3416 AND "ds"."event" = 'periodic'
3417 AND "ds"."informed"
3418 AND "ds"."satisfied"
3419 )
3420 WHERE "id" = "initiative_id_v";
3421 FOR "suggestion_id_v" IN
3422 SELECT "id" FROM "suggestion"
3423 WHERE "initiative_id" = "initiative_id_v"
3424 LOOP
3425 UPDATE "suggestion" SET
3426 "minus2_unfulfilled_count" = (
3427 SELECT coalesce(sum("snapshot"."weight"), 0)
3428 FROM "issue" CROSS JOIN "opinion"
3429 JOIN "direct_interest_snapshot" AS "snapshot"
3430 ON "snapshot"."issue_id" = "issue"."id"
3431 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3432 AND "snapshot"."member_id" = "opinion"."member_id"
3433 WHERE "issue"."id" = "issue_id_p"
3434 AND "opinion"."suggestion_id" = "suggestion_id_v"
3435 AND "opinion"."degree" = -2
3436 AND "opinion"."fulfilled" = FALSE
3437 ),
3438 "minus2_fulfilled_count" = (
3439 SELECT coalesce(sum("snapshot"."weight"), 0)
3440 FROM "issue" CROSS JOIN "opinion"
3441 JOIN "direct_interest_snapshot" AS "snapshot"
3442 ON "snapshot"."issue_id" = "issue"."id"
3443 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3444 AND "snapshot"."member_id" = "opinion"."member_id"
3445 WHERE "issue"."id" = "issue_id_p"
3446 AND "opinion"."suggestion_id" = "suggestion_id_v"
3447 AND "opinion"."degree" = -2
3448 AND "opinion"."fulfilled" = TRUE
3449 ),
3450 "minus1_unfulfilled_count" = (
3451 SELECT coalesce(sum("snapshot"."weight"), 0)
3452 FROM "issue" CROSS JOIN "opinion"
3453 JOIN "direct_interest_snapshot" AS "snapshot"
3454 ON "snapshot"."issue_id" = "issue"."id"
3455 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3456 AND "snapshot"."member_id" = "opinion"."member_id"
3457 WHERE "issue"."id" = "issue_id_p"
3458 AND "opinion"."suggestion_id" = "suggestion_id_v"
3459 AND "opinion"."degree" = -1
3460 AND "opinion"."fulfilled" = FALSE
3461 ),
3462 "minus1_fulfilled_count" = (
3463 SELECT coalesce(sum("snapshot"."weight"), 0)
3464 FROM "issue" CROSS JOIN "opinion"
3465 JOIN "direct_interest_snapshot" AS "snapshot"
3466 ON "snapshot"."issue_id" = "issue"."id"
3467 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3468 AND "snapshot"."member_id" = "opinion"."member_id"
3469 WHERE "issue"."id" = "issue_id_p"
3470 AND "opinion"."suggestion_id" = "suggestion_id_v"
3471 AND "opinion"."degree" = -1
3472 AND "opinion"."fulfilled" = TRUE
3473 ),
3474 "plus1_unfulfilled_count" = (
3475 SELECT coalesce(sum("snapshot"."weight"), 0)
3476 FROM "issue" CROSS JOIN "opinion"
3477 JOIN "direct_interest_snapshot" AS "snapshot"
3478 ON "snapshot"."issue_id" = "issue"."id"
3479 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3480 AND "snapshot"."member_id" = "opinion"."member_id"
3481 WHERE "issue"."id" = "issue_id_p"
3482 AND "opinion"."suggestion_id" = "suggestion_id_v"
3483 AND "opinion"."degree" = 1
3484 AND "opinion"."fulfilled" = FALSE
3485 ),
3486 "plus1_fulfilled_count" = (
3487 SELECT coalesce(sum("snapshot"."weight"), 0)
3488 FROM "issue" CROSS JOIN "opinion"
3489 JOIN "direct_interest_snapshot" AS "snapshot"
3490 ON "snapshot"."issue_id" = "issue"."id"
3491 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3492 AND "snapshot"."member_id" = "opinion"."member_id"
3493 WHERE "issue"."id" = "issue_id_p"
3494 AND "opinion"."suggestion_id" = "suggestion_id_v"
3495 AND "opinion"."degree" = 1
3496 AND "opinion"."fulfilled" = TRUE
3497 ),
3498 "plus2_unfulfilled_count" = (
3499 SELECT coalesce(sum("snapshot"."weight"), 0)
3500 FROM "issue" CROSS JOIN "opinion"
3501 JOIN "direct_interest_snapshot" AS "snapshot"
3502 ON "snapshot"."issue_id" = "issue"."id"
3503 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3504 AND "snapshot"."member_id" = "opinion"."member_id"
3505 WHERE "issue"."id" = "issue_id_p"
3506 AND "opinion"."suggestion_id" = "suggestion_id_v"
3507 AND "opinion"."degree" = 2
3508 AND "opinion"."fulfilled" = FALSE
3509 ),
3510 "plus2_fulfilled_count" = (
3511 SELECT coalesce(sum("snapshot"."weight"), 0)
3512 FROM "issue" CROSS JOIN "opinion"
3513 JOIN "direct_interest_snapshot" AS "snapshot"
3514 ON "snapshot"."issue_id" = "issue"."id"
3515 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3516 AND "snapshot"."member_id" = "opinion"."member_id"
3517 WHERE "issue"."id" = "issue_id_p"
3518 AND "opinion"."suggestion_id" = "suggestion_id_v"
3519 AND "opinion"."degree" = 2
3520 AND "opinion"."fulfilled" = TRUE
3521 )
3522 WHERE "suggestion"."id" = "suggestion_id_v";
3523 END LOOP;
3524 END LOOP;
3525 RETURN;
3526 END;
3527 $$;
3529 COMMENT ON FUNCTION "create_snapshot"
3530 ( "issue"."id"%TYPE )
3531 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.';
3534 CREATE FUNCTION "set_snapshot_event"
3535 ( "issue_id_p" "issue"."id"%TYPE,
3536 "event_p" "snapshot_event" )
3537 RETURNS VOID
3538 LANGUAGE 'plpgsql' VOLATILE AS $$
3539 DECLARE
3540 "event_v" "issue"."latest_snapshot_event"%TYPE;
3541 BEGIN
3542 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
3543 WHERE "id" = "issue_id_p" FOR UPDATE;
3544 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
3545 WHERE "id" = "issue_id_p";
3546 UPDATE "direct_population_snapshot" SET "event" = "event_p"
3547 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3548 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
3549 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3550 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
3551 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3552 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
3553 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3554 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
3555 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3556 RETURN;
3557 END;
3558 $$;
3560 COMMENT ON FUNCTION "set_snapshot_event"
3561 ( "issue"."id"%TYPE,
3562 "snapshot_event" )
3563 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
3567 ---------------------
3568 -- Freezing issues --
3569 ---------------------
3571 CREATE FUNCTION "freeze_after_snapshot"
3572 ( "issue_id_p" "issue"."id"%TYPE )
3573 RETURNS VOID
3574 LANGUAGE 'plpgsql' VOLATILE AS $$
3575 DECLARE
3576 "issue_row" "issue"%ROWTYPE;
3577 "policy_row" "policy"%ROWTYPE;
3578 "initiative_row" "initiative"%ROWTYPE;
3579 BEGIN
3580 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3581 SELECT * INTO "policy_row"
3582 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3583 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
3584 FOR "initiative_row" IN
3585 SELECT * FROM "initiative"
3586 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3587 LOOP
3588 IF
3589 "initiative_row"."polling" OR (
3590 "initiative_row"."satisfied_supporter_count" > 0 AND
3591 "initiative_row"."satisfied_supporter_count" *
3592 "policy_row"."initiative_quorum_den" >=
3593 "issue_row"."population" * "policy_row"."initiative_quorum_num"
3594 )
3595 THEN
3596 UPDATE "initiative" SET "admitted" = TRUE
3597 WHERE "id" = "initiative_row"."id";
3598 ELSE
3599 UPDATE "initiative" SET "admitted" = FALSE
3600 WHERE "id" = "initiative_row"."id";
3601 END IF;
3602 END LOOP;
3603 IF EXISTS (
3604 SELECT NULL FROM "initiative"
3605 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
3606 ) THEN
3607 UPDATE "issue" SET
3608 "state" = 'voting',
3609 "accepted" = coalesce("accepted", now()),
3610 "half_frozen" = coalesce("half_frozen", now()),
3611 "fully_frozen" = now()
3612 WHERE "id" = "issue_id_p";
3613 ELSE
3614 UPDATE "issue" SET
3615 "state" = 'canceled_no_initiative_admitted',
3616 "accepted" = coalesce("accepted", now()),
3617 "half_frozen" = coalesce("half_frozen", now()),
3618 "fully_frozen" = now(),
3619 "closed" = now(),
3620 "ranks_available" = TRUE
3621 WHERE "id" = "issue_id_p";
3622 -- NOTE: The following DELETE statements have effect only when
3623 -- issue state has been manipulated
3624 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
3625 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
3626 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3627 END IF;
3628 RETURN;
3629 END;
3630 $$;
3632 COMMENT ON FUNCTION "freeze_after_snapshot"
3633 ( "issue"."id"%TYPE )
3634 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
3637 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
3638 RETURNS VOID
3639 LANGUAGE 'plpgsql' VOLATILE AS $$
3640 DECLARE
3641 "issue_row" "issue"%ROWTYPE;
3642 BEGIN
3643 PERFORM "create_snapshot"("issue_id_p");
3644 PERFORM "freeze_after_snapshot"("issue_id_p");
3645 RETURN;
3646 END;
3647 $$;
3649 COMMENT ON FUNCTION "manual_freeze"
3650 ( "issue"."id"%TYPE )
3651 IS 'Freeze an issue manually (fully) and start voting';
3655 -----------------------
3656 -- Counting of votes --
3657 -----------------------
3660 CREATE FUNCTION "weight_of_added_vote_delegations"
3661 ( "issue_id_p" "issue"."id"%TYPE,
3662 "member_id_p" "member"."id"%TYPE,
3663 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
3664 RETURNS "direct_voter"."weight"%TYPE
3665 LANGUAGE 'plpgsql' VOLATILE AS $$
3666 DECLARE
3667 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3668 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
3669 "weight_v" INT4;
3670 "sub_weight_v" INT4;
3671 BEGIN
3672 "weight_v" := 0;
3673 FOR "issue_delegation_row" IN
3674 SELECT * FROM "issue_delegation"
3675 WHERE "trustee_id" = "member_id_p"
3676 AND "issue_id" = "issue_id_p"
3677 LOOP
3678 IF NOT EXISTS (
3679 SELECT NULL FROM "direct_voter"
3680 WHERE "member_id" = "issue_delegation_row"."truster_id"
3681 AND "issue_id" = "issue_id_p"
3682 ) AND NOT EXISTS (
3683 SELECT NULL FROM "delegating_voter"
3684 WHERE "member_id" = "issue_delegation_row"."truster_id"
3685 AND "issue_id" = "issue_id_p"
3686 ) THEN
3687 "delegate_member_ids_v" :=
3688 "member_id_p" || "delegate_member_ids_p";
3689 INSERT INTO "delegating_voter" (
3690 "issue_id",
3691 "member_id",
3692 "scope",
3693 "delegate_member_ids"
3694 ) VALUES (
3695 "issue_id_p",
3696 "issue_delegation_row"."truster_id",
3697 "issue_delegation_row"."scope",
3698 "delegate_member_ids_v"
3699 );
3700 "sub_weight_v" := 1 +
3701 "weight_of_added_vote_delegations"(
3702 "issue_id_p",
3703 "issue_delegation_row"."truster_id",
3704 "delegate_member_ids_v"
3705 );
3706 UPDATE "delegating_voter"
3707 SET "weight" = "sub_weight_v"
3708 WHERE "issue_id" = "issue_id_p"
3709 AND "member_id" = "issue_delegation_row"."truster_id";
3710 "weight_v" := "weight_v" + "sub_weight_v";
3711 END IF;
3712 END LOOP;
3713 RETURN "weight_v";
3714 END;
3715 $$;
3717 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
3718 ( "issue"."id"%TYPE,
3719 "member"."id"%TYPE,
3720 "delegating_voter"."delegate_member_ids"%TYPE )
3721 IS 'Helper function for "add_vote_delegations" function';
3724 CREATE FUNCTION "add_vote_delegations"
3725 ( "issue_id_p" "issue"."id"%TYPE )
3726 RETURNS VOID
3727 LANGUAGE 'plpgsql' VOLATILE AS $$
3728 DECLARE
3729 "member_id_v" "member"."id"%TYPE;
3730 BEGIN
3731 FOR "member_id_v" IN
3732 SELECT "member_id" FROM "direct_voter"
3733 WHERE "issue_id" = "issue_id_p"
3734 LOOP
3735 UPDATE "direct_voter" SET
3736 "weight" = "weight" + "weight_of_added_vote_delegations"(
3737 "issue_id_p",
3738 "member_id_v",
3739 '{}'
3740 )
3741 WHERE "member_id" = "member_id_v"
3742 AND "issue_id" = "issue_id_p";
3743 END LOOP;
3744 RETURN;
3745 END;
3746 $$;
3748 COMMENT ON FUNCTION "add_vote_delegations"
3749 ( "issue_id_p" "issue"."id"%TYPE )
3750 IS 'Helper function for "close_voting" function';
3753 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
3754 RETURNS VOID
3755 LANGUAGE 'plpgsql' VOLATILE AS $$
3756 DECLARE
3757 "area_id_v" "area"."id"%TYPE;
3758 "unit_id_v" "unit"."id"%TYPE;
3759 "member_id_v" "member"."id"%TYPE;
3760 BEGIN
3761 PERFORM "lock_issue"("issue_id_p");
3762 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
3763 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
3764 -- delete timestamp of voting comment:
3765 UPDATE "direct_voter" SET "comment_changed" = NULL
3766 WHERE "issue_id" = "issue_id_p";
3767 -- delete delegating votes (in cases of manual reset of issue state):
3768 DELETE FROM "delegating_voter"
3769 WHERE "issue_id" = "issue_id_p";
3770 -- delete votes from non-privileged voters:
3771 DELETE FROM "direct_voter"
3772 USING (
3773 SELECT
3774 "direct_voter"."member_id"
3775 FROM "direct_voter"
3776 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
3777 LEFT JOIN "privilege"
3778 ON "privilege"."unit_id" = "unit_id_v"
3779 AND "privilege"."member_id" = "direct_voter"."member_id"
3780 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
3781 "member"."active" = FALSE OR
3782 "privilege"."voting_right" ISNULL OR
3783 "privilege"."voting_right" = FALSE
3784 )
3785 ) AS "subquery"
3786 WHERE "direct_voter"."issue_id" = "issue_id_p"
3787 AND "direct_voter"."member_id" = "subquery"."member_id";
3788 -- consider delegations:
3789 UPDATE "direct_voter" SET "weight" = 1
3790 WHERE "issue_id" = "issue_id_p";
3791 PERFORM "add_vote_delegations"("issue_id_p");
3792 -- set voter count and mark issue as being calculated:
3793 UPDATE "issue" SET
3794 "state" = 'calculation',
3795 "closed" = now(),
3796 "voter_count" = (
3797 SELECT coalesce(sum("weight"), 0)
3798 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
3799 )
3800 WHERE "id" = "issue_id_p";
3801 -- materialize battle_view:
3802 -- NOTE: "closed" column of issue must be set at this point
3803 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3804 INSERT INTO "battle" (
3805 "issue_id",
3806 "winning_initiative_id", "losing_initiative_id",
3807 "count"
3808 ) SELECT
3809 "issue_id",
3810 "winning_initiative_id", "losing_initiative_id",
3811 "count"
3812 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
3813 -- copy "positive_votes" and "negative_votes" from "battle" table:
3814 UPDATE "initiative" SET
3815 "positive_votes" = "battle_win"."count",
3816 "negative_votes" = "battle_lose"."count"
3817 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
3818 WHERE
3819 "battle_win"."issue_id" = "issue_id_p" AND
3820 "battle_win"."winning_initiative_id" = "initiative"."id" AND
3821 "battle_win"."losing_initiative_id" ISNULL AND
3822 "battle_lose"."issue_id" = "issue_id_p" AND
3823 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
3824 "battle_lose"."winning_initiative_id" ISNULL;
3825 END;
3826 $$;
3828 COMMENT ON FUNCTION "close_voting"
3829 ( "issue"."id"%TYPE )
3830 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.';
3833 CREATE FUNCTION "defeat_strength"
3834 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
3835 RETURNS INT8
3836 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3837 BEGIN
3838 IF "positive_votes_p" > "negative_votes_p" THEN
3839 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
3840 ELSIF "positive_votes_p" = "negative_votes_p" THEN
3841 RETURN 0;
3842 ELSE
3843 RETURN -1;
3844 END IF;
3845 END;
3846 $$;
3848 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';
3851 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
3852 RETURNS VOID
3853 LANGUAGE 'plpgsql' VOLATILE AS $$
3854 DECLARE
3855 "issue_row" "issue"%ROWTYPE;
3856 "policy_row" "policy"%ROWTYPE;
3857 "dimension_v" INTEGER;
3858 "vote_matrix" INT4[][]; -- absolute votes
3859 "matrix" INT8[][]; -- defeat strength / best paths
3860 "i" INTEGER;
3861 "j" INTEGER;
3862 "k" INTEGER;
3863 "battle_row" "battle"%ROWTYPE;
3864 "rank_ary" INT4[];
3865 "rank_v" INT4;
3866 "done_v" INTEGER;
3867 "winners_ary" INTEGER[];
3868 "initiative_id_v" "initiative"."id"%TYPE;
3869 BEGIN
3870 SELECT * INTO "issue_row"
3871 FROM "issue" WHERE "id" = "issue_id_p"
3872 FOR UPDATE;
3873 SELECT * INTO "policy_row"
3874 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3875 SELECT count(1) INTO "dimension_v"
3876 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
3877 -- Create "vote_matrix" with absolute number of votes in pairwise
3878 -- comparison:
3879 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
3880 "i" := 1;
3881 "j" := 2;
3882 FOR "battle_row" IN
3883 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
3884 ORDER BY
3885 "winning_initiative_id" NULLS LAST,
3886 "losing_initiative_id" NULLS LAST
3887 LOOP
3888 "vote_matrix"["i"]["j"] := "battle_row"."count";
3889 IF "j" = "dimension_v" THEN
3890 "i" := "i" + 1;
3891 "j" := 1;
3892 ELSE
3893 "j" := "j" + 1;
3894 IF "j" = "i" THEN
3895 "j" := "j" + 1;
3896 END IF;
3897 END IF;
3898 END LOOP;
3899 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
3900 RAISE EXCEPTION 'Wrong battle count (should not happen)';
3901 END IF;
3902 -- Store defeat strengths in "matrix" using "defeat_strength"
3903 -- function:
3904 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
3905 "i" := 1;
3906 LOOP
3907 "j" := 1;
3908 LOOP
3909 IF "i" != "j" THEN
3910 "matrix"["i"]["j"] := "defeat_strength"(
3911 "vote_matrix"["i"]["j"],
3912 "vote_matrix"["j"]["i"]
3913 );
3914 END IF;
3915 EXIT WHEN "j" = "dimension_v";
3916 "j" := "j" + 1;
3917 END LOOP;
3918 EXIT WHEN "i" = "dimension_v";
3919 "i" := "i" + 1;
3920 END LOOP;
3921 -- Find best paths:
3922 "i" := 1;
3923 LOOP
3924 "j" := 1;
3925 LOOP
3926 IF "i" != "j" THEN
3927 "k" := 1;
3928 LOOP
3929 IF "i" != "k" AND "j" != "k" THEN
3930 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
3931 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
3932 "matrix"["j"]["k"] := "matrix"["j"]["i"];
3933 END IF;
3934 ELSE
3935 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
3936 "matrix"["j"]["k"] := "matrix"["i"]["k"];
3937 END IF;
3938 END IF;
3939 END IF;
3940 EXIT WHEN "k" = "dimension_v";
3941 "k" := "k" + 1;
3942 END LOOP;
3943 END IF;
3944 EXIT WHEN "j" = "dimension_v";
3945 "j" := "j" + 1;
3946 END LOOP;
3947 EXIT WHEN "i" = "dimension_v";
3948 "i" := "i" + 1;
3949 END LOOP;
3950 -- Determine order of winners:
3951 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
3952 "rank_v" := 1;
3953 "done_v" := 0;
3954 LOOP
3955 "winners_ary" := '{}';
3956 "i" := 1;
3957 LOOP
3958 IF "rank_ary"["i"] ISNULL THEN
3959 "j" := 1;
3960 LOOP
3961 IF
3962 "i" != "j" AND
3963 "rank_ary"["j"] ISNULL AND
3964 "matrix"["j"]["i"] > "matrix"["i"]["j"]
3965 THEN
3966 -- someone else is better
3967 EXIT;
3968 END IF;
3969 IF "j" = "dimension_v" THEN
3970 -- noone is better
3971 "winners_ary" := "winners_ary" || "i";
3972 EXIT;
3973 END IF;
3974 "j" := "j" + 1;
3975 END LOOP;
3976 END IF;
3977 EXIT WHEN "i" = "dimension_v";
3978 "i" := "i" + 1;
3979 END LOOP;
3980 "i" := 1;
3981 LOOP
3982 "rank_ary"["winners_ary"["i"]] := "rank_v";
3983 "done_v" := "done_v" + 1;
3984 EXIT WHEN "i" = array_upper("winners_ary", 1);
3985 "i" := "i" + 1;
3986 END LOOP;
3987 EXIT WHEN "done_v" = "dimension_v";
3988 "rank_v" := "rank_v" + 1;
3989 END LOOP;
3990 -- write preliminary results:
3991 "i" := 1;
3992 FOR "initiative_id_v" IN
3993 SELECT "id" FROM "initiative"
3994 WHERE "issue_id" = "issue_id_p" AND "admitted"
3995 ORDER BY "id"
3996 LOOP
3997 UPDATE "initiative" SET
3998 "direct_majority" =
3999 CASE WHEN "policy_row"."direct_majority_strict" THEN
4000 "positive_votes" * "policy_row"."direct_majority_den" >
4001 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
4002 ELSE
4003 "positive_votes" * "policy_row"."direct_majority_den" >=
4004 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
4005 END
4006 AND "positive_votes" >= "policy_row"."direct_majority_positive"
4007 AND "issue_row"."voter_count"-"negative_votes" >=
4008 "policy_row"."direct_majority_non_negative",
4009 "indirect_majority" =
4010 CASE WHEN "policy_row"."indirect_majority_strict" THEN
4011 "positive_votes" * "policy_row"."indirect_majority_den" >
4012 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
4013 ELSE
4014 "positive_votes" * "policy_row"."indirect_majority_den" >=
4015 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
4016 END
4017 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
4018 AND "issue_row"."voter_count"-"negative_votes" >=
4019 "policy_row"."indirect_majority_non_negative",
4020 "schulze_rank" = "rank_ary"["i"],
4021 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
4022 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
4023 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
4024 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0,
4025 "eligible" = FALSE,
4026 "winner" = FALSE,
4027 "rank" = NULL -- NOTE: in cases of manual reset of issue state
4028 WHERE "id" = "initiative_id_v";
4029 "i" := "i" + 1;
4030 END LOOP;
4031 IF "i" != "dimension_v" THEN
4032 RAISE EXCEPTION 'Wrong winner count (should not happen)';
4033 END IF;
4034 -- take indirect majorities into account:
4035 LOOP
4036 UPDATE "initiative" SET "indirect_majority" = TRUE
4037 FROM (
4038 SELECT "new_initiative"."id" AS "initiative_id"
4039 FROM "initiative" "old_initiative"
4040 JOIN "initiative" "new_initiative"
4041 ON "new_initiative"."issue_id" = "issue_id_p"
4042 AND "new_initiative"."indirect_majority" = FALSE
4043 JOIN "battle" "battle_win"
4044 ON "battle_win"."issue_id" = "issue_id_p"
4045 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
4046 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
4047 JOIN "battle" "battle_lose"
4048 ON "battle_lose"."issue_id" = "issue_id_p"
4049 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
4050 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
4051 WHERE "old_initiative"."issue_id" = "issue_id_p"
4052 AND "old_initiative"."indirect_majority" = TRUE
4053 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
4054 "battle_win"."count" * "policy_row"."indirect_majority_den" >
4055 "policy_row"."indirect_majority_num" *
4056 ("battle_win"."count"+"battle_lose"."count")
4057 ELSE
4058 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
4059 "policy_row"."indirect_majority_num" *
4060 ("battle_win"."count"+"battle_lose"."count")
4061 END
4062 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
4063 AND "issue_row"."voter_count"-"battle_lose"."count" >=
4064 "policy_row"."indirect_majority_non_negative"
4065 ) AS "subquery"
4066 WHERE "id" = "subquery"."initiative_id";
4067 EXIT WHEN NOT FOUND;
4068 END LOOP;
4069 -- set "multistage_majority" for remaining matching initiatives:
4070 UPDATE "initiative" SET "multistage_majority" = TRUE
4071 FROM (
4072 SELECT "losing_initiative"."id" AS "initiative_id"
4073 FROM "initiative" "losing_initiative"
4074 JOIN "initiative" "winning_initiative"
4075 ON "winning_initiative"."issue_id" = "issue_id_p"
4076 AND "winning_initiative"."admitted"
4077 JOIN "battle" "battle_win"
4078 ON "battle_win"."issue_id" = "issue_id_p"
4079 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
4080 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
4081 JOIN "battle" "battle_lose"
4082 ON "battle_lose"."issue_id" = "issue_id_p"
4083 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
4084 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
4085 WHERE "losing_initiative"."issue_id" = "issue_id_p"
4086 AND "losing_initiative"."admitted"
4087 AND "winning_initiative"."schulze_rank" <
4088 "losing_initiative"."schulze_rank"
4089 AND "battle_win"."count" > "battle_lose"."count"
4090 AND (
4091 "battle_win"."count" > "winning_initiative"."positive_votes" OR
4092 "battle_lose"."count" < "losing_initiative"."negative_votes" )
4093 ) AS "subquery"
4094 WHERE "id" = "subquery"."initiative_id";
4095 -- mark eligible initiatives:
4096 UPDATE "initiative" SET "eligible" = TRUE
4097 WHERE "issue_id" = "issue_id_p"
4098 AND "initiative"."direct_majority"
4099 AND "initiative"."indirect_majority"
4100 AND "initiative"."better_than_status_quo"
4101 AND (
4102 "policy_row"."no_multistage_majority" = FALSE OR
4103 "initiative"."multistage_majority" = FALSE )
4104 AND (
4105 "policy_row"."no_reverse_beat_path" = FALSE OR
4106 "initiative"."reverse_beat_path" = FALSE );
4107 -- mark final winner:
4108 UPDATE "initiative" SET "winner" = TRUE
4109 FROM (
4110 SELECT "id" AS "initiative_id"
4111 FROM "initiative"
4112 WHERE "issue_id" = "issue_id_p" AND "eligible"
4113 ORDER BY
4114 "schulze_rank",
4115 "vote_ratio"("positive_votes", "negative_votes"),
4116 "id"
4117 LIMIT 1
4118 ) AS "subquery"
4119 WHERE "id" = "subquery"."initiative_id";
4120 -- write (final) ranks:
4121 "rank_v" := 1;
4122 FOR "initiative_id_v" IN
4123 SELECT "id"
4124 FROM "initiative"
4125 WHERE "issue_id" = "issue_id_p" AND "admitted"
4126 ORDER BY
4127 "winner" DESC,
4128 "eligible" DESC,
4129 "schulze_rank",
4130 "vote_ratio"("positive_votes", "negative_votes"),
4131 "id"
4132 LOOP
4133 UPDATE "initiative" SET "rank" = "rank_v"
4134 WHERE "id" = "initiative_id_v";
4135 "rank_v" := "rank_v" + 1;
4136 END LOOP;
4137 -- set schulze rank of status quo and mark issue as finished:
4138 UPDATE "issue" SET
4139 "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
4140 "state" =
4141 CASE WHEN EXISTS (
4142 SELECT NULL FROM "initiative"
4143 WHERE "issue_id" = "issue_id_p" AND "winner"
4144 ) THEN
4145 'finished_with_winner'::"issue_state"
4146 ELSE
4147 'finished_without_winner'::"issue_state"
4148 END,
4149 "ranks_available" = TRUE
4150 WHERE "id" = "issue_id_p";
4151 RETURN;
4152 END;
4153 $$;
4155 COMMENT ON FUNCTION "calculate_ranks"
4156 ( "issue"."id"%TYPE )
4157 IS 'Determine ranking (Votes have to be counted first)';
4161 -----------------------------
4162 -- Automatic state changes --
4163 -----------------------------
4166 CREATE FUNCTION "check_issue"
4167 ( "issue_id_p" "issue"."id"%TYPE )
4168 RETURNS VOID
4169 LANGUAGE 'plpgsql' VOLATILE AS $$
4170 DECLARE
4171 "issue_row" "issue"%ROWTYPE;
4172 "policy_row" "policy"%ROWTYPE;
4173 BEGIN
4174 PERFORM "lock_issue"("issue_id_p");
4175 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4176 -- only process open issues:
4177 IF "issue_row"."closed" ISNULL THEN
4178 SELECT * INTO "policy_row" FROM "policy"
4179 WHERE "id" = "issue_row"."policy_id";
4180 -- create a snapshot, unless issue is already fully frozen:
4181 IF "issue_row"."fully_frozen" ISNULL THEN
4182 PERFORM "create_snapshot"("issue_id_p");
4183 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4184 END IF;
4185 -- eventually close or accept issues, which have not been accepted:
4186 IF "issue_row"."accepted" ISNULL THEN
4187 IF EXISTS (
4188 SELECT NULL FROM "initiative"
4189 WHERE "issue_id" = "issue_id_p"
4190 AND "supporter_count" > 0
4191 AND "supporter_count" * "policy_row"."issue_quorum_den"
4192 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
4193 ) THEN
4194 -- accept issues, if supporter count is high enough
4195 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
4196 -- NOTE: "issue_row" used later
4197 "issue_row"."state" := 'discussion';
4198 "issue_row"."accepted" := now();
4199 UPDATE "issue" SET
4200 "state" = "issue_row"."state",
4201 "accepted" = "issue_row"."accepted"
4202 WHERE "id" = "issue_row"."id";
4203 ELSIF
4204 now() >= "issue_row"."created" + "issue_row"."admission_time"
4205 THEN
4206 -- close issues, if admission time has expired
4207 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
4208 UPDATE "issue" SET
4209 "state" = 'canceled_issue_not_accepted',
4210 "closed" = now()
4211 WHERE "id" = "issue_row"."id";
4212 END IF;
4213 END IF;
4214 -- eventually half freeze issues:
4215 IF
4216 -- NOTE: issue can't be closed at this point, if it has been accepted
4217 "issue_row"."accepted" NOTNULL AND
4218 "issue_row"."half_frozen" ISNULL
4219 THEN
4220 IF
4221 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
4222 THEN
4223 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
4224 -- NOTE: "issue_row" used later
4225 "issue_row"."state" := 'verification';
4226 "issue_row"."half_frozen" := now();
4227 UPDATE "issue" SET
4228 "state" = "issue_row"."state",
4229 "half_frozen" = "issue_row"."half_frozen"
4230 WHERE "id" = "issue_row"."id";
4231 END IF;
4232 END IF;
4233 -- close issues after some time, if all initiatives have been revoked:
4234 IF
4235 "issue_row"."closed" ISNULL AND
4236 NOT EXISTS (
4237 -- all initiatives are revoked
4238 SELECT NULL FROM "initiative"
4239 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
4240 ) AND (
4241 -- and issue has not been accepted yet
4242 "issue_row"."accepted" ISNULL OR
4243 NOT EXISTS (
4244 -- or no initiatives have been revoked lately
4245 SELECT NULL FROM "initiative"
4246 WHERE "issue_id" = "issue_id_p"
4247 AND now() < "revoked" + "issue_row"."verification_time"
4248 ) OR (
4249 -- or verification time has elapsed
4250 "issue_row"."half_frozen" NOTNULL AND
4251 "issue_row"."fully_frozen" ISNULL AND
4252 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
4253 )
4254 )
4255 THEN
4256 -- NOTE: "issue_row" used later
4257 IF "issue_row"."accepted" ISNULL THEN
4258 "issue_row"."state" := 'canceled_revoked_before_accepted';
4259 ELSIF "issue_row"."half_frozen" ISNULL THEN
4260 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
4261 ELSE
4262 "issue_row"."state" := 'canceled_after_revocation_during_verification';
4263 END IF;
4264 "issue_row"."closed" := now();
4265 UPDATE "issue" SET
4266 "state" = "issue_row"."state",
4267 "closed" = "issue_row"."closed"
4268 WHERE "id" = "issue_row"."id";
4269 END IF;
4270 -- fully freeze issue after verification time:
4271 IF
4272 "issue_row"."half_frozen" NOTNULL AND
4273 "issue_row"."fully_frozen" ISNULL AND
4274 "issue_row"."closed" ISNULL AND
4275 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
4276 THEN
4277 PERFORM "freeze_after_snapshot"("issue_id_p");
4278 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
4279 END IF;
4280 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4281 -- close issue by calling close_voting(...) after voting time:
4282 IF
4283 "issue_row"."closed" ISNULL AND
4284 "issue_row"."fully_frozen" NOTNULL AND
4285 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
4286 THEN
4287 PERFORM "close_voting"("issue_id_p");
4288 -- calculate ranks will not consume much time and can be done now
4289 PERFORM "calculate_ranks"("issue_id_p");
4290 END IF;
4291 END IF;
4292 RETURN;
4293 END;
4294 $$;
4296 COMMENT ON FUNCTION "check_issue"
4297 ( "issue"."id"%TYPE )
4298 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.';
4301 CREATE FUNCTION "check_everything"()
4302 RETURNS VOID
4303 LANGUAGE 'plpgsql' VOLATILE AS $$
4304 DECLARE
4305 "issue_id_v" "issue"."id"%TYPE;
4306 BEGIN
4307 DELETE FROM "expired_session";
4308 PERFORM "check_activity"();
4309 PERFORM "calculate_member_counts"();
4310 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
4311 PERFORM "check_issue"("issue_id_v");
4312 END LOOP;
4313 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
4314 PERFORM "calculate_ranks"("issue_id_v");
4315 END LOOP;
4316 RETURN;
4317 END;
4318 $$;
4320 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.';
4324 ----------------------
4325 -- Deletion of data --
4326 ----------------------
4329 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
4330 RETURNS VOID
4331 LANGUAGE 'plpgsql' VOLATILE AS $$
4332 DECLARE
4333 "issue_row" "issue"%ROWTYPE;
4334 BEGIN
4335 SELECT * INTO "issue_row"
4336 FROM "issue" WHERE "id" = "issue_id_p"
4337 FOR UPDATE;
4338 IF "issue_row"."cleaned" ISNULL THEN
4339 UPDATE "issue" SET
4340 "state" = 'voting',
4341 "closed" = NULL,
4342 "ranks_available" = FALSE
4343 WHERE "id" = "issue_id_p";
4344 DELETE FROM "delegating_voter"
4345 WHERE "issue_id" = "issue_id_p";
4346 DELETE FROM "direct_voter"
4347 WHERE "issue_id" = "issue_id_p";
4348 DELETE FROM "delegating_interest_snapshot"
4349 WHERE "issue_id" = "issue_id_p";
4350 DELETE FROM "direct_interest_snapshot"
4351 WHERE "issue_id" = "issue_id_p";
4352 DELETE FROM "delegating_population_snapshot"
4353 WHERE "issue_id" = "issue_id_p";
4354 DELETE FROM "direct_population_snapshot"
4355 WHERE "issue_id" = "issue_id_p";
4356 DELETE FROM "non_voter"
4357 WHERE "issue_id" = "issue_id_p";
4358 DELETE FROM "delegation"
4359 WHERE "issue_id" = "issue_id_p";
4360 DELETE FROM "supporter"
4361 WHERE "issue_id" = "issue_id_p";
4362 UPDATE "issue" SET
4363 "state" = "issue_row"."state",
4364 "closed" = "issue_row"."closed",
4365 "ranks_available" = "issue_row"."ranks_available",
4366 "cleaned" = now()
4367 WHERE "id" = "issue_id_p";
4368 END IF;
4369 RETURN;
4370 END;
4371 $$;
4373 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
4376 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
4377 RETURNS VOID
4378 LANGUAGE 'plpgsql' VOLATILE AS $$
4379 BEGIN
4380 UPDATE "member" SET
4381 "last_login" = NULL,
4382 "login" = NULL,
4383 "password" = NULL,
4384 "locked" = TRUE,
4385 "active" = FALSE,
4386 "notify_email" = NULL,
4387 "notify_email_unconfirmed" = NULL,
4388 "notify_email_secret" = NULL,
4389 "notify_email_secret_expiry" = NULL,
4390 "notify_email_lock_expiry" = NULL,
4391 "password_reset_secret" = NULL,
4392 "password_reset_secret_expiry" = NULL,
4393 "organizational_unit" = NULL,
4394 "internal_posts" = NULL,
4395 "realname" = NULL,
4396 "birthday" = NULL,
4397 "address" = NULL,
4398 "email" = NULL,
4399 "xmpp_address" = NULL,
4400 "website" = NULL,
4401 "phone" = NULL,
4402 "mobile_phone" = NULL,
4403 "profession" = NULL,
4404 "external_memberships" = NULL,
4405 "external_posts" = NULL,
4406 "statement" = NULL
4407 WHERE "id" = "member_id_p";
4408 -- "text_search_data" is updated by triggers
4409 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
4410 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
4411 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
4412 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
4413 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
4414 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
4415 DELETE FROM "session" WHERE "member_id" = "member_id_p";
4416 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
4417 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
4418 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
4419 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
4420 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
4421 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
4422 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
4423 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
4424 DELETE FROM "direct_voter" USING "issue"
4425 WHERE "direct_voter"."issue_id" = "issue"."id"
4426 AND "issue"."closed" ISNULL
4427 AND "member_id" = "member_id_p";
4428 RETURN;
4429 END;
4430 $$;
4432 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)';
4435 CREATE FUNCTION "delete_private_data"()
4436 RETURNS VOID
4437 LANGUAGE 'plpgsql' VOLATILE AS $$
4438 BEGIN
4439 DELETE FROM "member" WHERE "activated" ISNULL;
4440 UPDATE "member" SET
4441 "invite_code" = NULL,
4442 "invite_code_expiry" = NULL,
4443 "admin_comment" = NULL,
4444 "last_login" = NULL,
4445 "login" = NULL,
4446 "password" = NULL,
4447 "lang" = NULL,
4448 "notify_email" = NULL,
4449 "notify_email_unconfirmed" = NULL,
4450 "notify_email_secret" = NULL,
4451 "notify_email_secret_expiry" = NULL,
4452 "notify_email_lock_expiry" = NULL,
4453 "notify_level" = NULL,
4454 "password_reset_secret" = NULL,
4455 "password_reset_secret_expiry" = NULL,
4456 "organizational_unit" = NULL,
4457 "internal_posts" = NULL,
4458 "realname" = NULL,
4459 "birthday" = NULL,
4460 "address" = NULL,
4461 "email" = NULL,
4462 "xmpp_address" = NULL,
4463 "website" = NULL,
4464 "phone" = NULL,
4465 "mobile_phone" = NULL,
4466 "profession" = NULL,
4467 "external_memberships" = NULL,
4468 "external_posts" = NULL,
4469 "formatting_engine" = NULL,
4470 "statement" = NULL;
4471 -- "text_search_data" is updated by triggers
4472 DELETE FROM "setting";
4473 DELETE FROM "setting_map";
4474 DELETE FROM "member_relation_setting";
4475 DELETE FROM "member_image";
4476 DELETE FROM "contact";
4477 DELETE FROM "ignored_member";
4478 DELETE FROM "session";
4479 DELETE FROM "area_setting";
4480 DELETE FROM "issue_setting";
4481 DELETE FROM "ignored_initiative";
4482 DELETE FROM "initiative_setting";
4483 DELETE FROM "suggestion_setting";
4484 DELETE FROM "non_voter";
4485 DELETE FROM "direct_voter" USING "issue"
4486 WHERE "direct_voter"."issue_id" = "issue"."id"
4487 AND "issue"."closed" ISNULL;
4488 RETURN;
4489 END;
4490 $$;
4492 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.';
4496 COMMIT;