liquid_feedback_core
view core.sql @ 291:86f231bd6906
Issues may have an admission_time set to NULL
| author | jbe |
|---|---|
| date | Mon Aug 20 03:28:45 2012 +0200 (2012-08-20) |
| parents | 2301a1f2acfa |
| children | 3de42ea02dc2 |
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 NOT NULL,
341 "issue_quorum_den" INT4 NOT NULL,
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 CREATE INDEX "policy_active_idx" ON "policy" ("active");
368 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
370 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
371 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
372 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';
373 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"';
374 COMMENT ON COLUMN "policy"."discussion_time" IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"';
375 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"';
376 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'')';
377 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''';
378 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''';
379 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
380 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
381 COMMENT ON COLUMN "policy"."direct_majority_num" IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner';
382 COMMENT ON COLUMN "policy"."direct_majority_den" IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner';
383 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.';
384 COMMENT ON COLUMN "policy"."direct_majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner';
385 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';
386 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';
387 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';
388 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.';
389 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';
390 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';
391 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.';
392 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").';
395 CREATE TABLE "unit" (
396 "id" SERIAL4 PRIMARY KEY,
397 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
398 "active" BOOLEAN NOT NULL DEFAULT TRUE,
399 "name" TEXT NOT NULL,
400 "description" TEXT NOT NULL DEFAULT '',
401 "member_count" INT4,
402 "text_search_data" TSVECTOR );
403 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
404 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
405 CREATE INDEX "unit_active_idx" ON "unit" ("active");
406 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
407 CREATE TRIGGER "update_text_search_data"
408 BEFORE INSERT OR UPDATE ON "unit"
409 FOR EACH ROW EXECUTE PROCEDURE
410 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
411 "name", "description" );
413 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
415 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
416 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in areas of this unit';
417 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
420 CREATE TABLE "unit_setting" (
421 PRIMARY KEY ("member_id", "key", "unit_id"),
422 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
423 "key" TEXT NOT NULL,
424 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
425 "value" TEXT NOT NULL );
427 COMMENT ON TABLE "unit_setting" IS 'Place for frontend to store unit specific settings of members as strings';
430 CREATE TABLE "area" (
431 "id" SERIAL4 PRIMARY KEY,
432 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
433 "active" BOOLEAN NOT NULL DEFAULT TRUE,
434 "name" TEXT NOT NULL,
435 "description" TEXT NOT NULL DEFAULT '',
436 "direct_member_count" INT4,
437 "member_weight" INT4,
438 "text_search_data" TSVECTOR );
439 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
440 CREATE INDEX "area_active_idx" ON "area" ("active");
441 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
442 CREATE TRIGGER "update_text_search_data"
443 BEFORE INSERT OR UPDATE ON "area"
444 FOR EACH ROW EXECUTE PROCEDURE
445 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
446 "name", "description" );
448 COMMENT ON TABLE "area" IS 'Subject areas';
450 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
451 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"';
452 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
455 CREATE TABLE "area_setting" (
456 PRIMARY KEY ("member_id", "key", "area_id"),
457 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
458 "key" TEXT NOT NULL,
459 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
460 "value" TEXT NOT NULL );
462 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
465 CREATE TABLE "allowed_policy" (
466 PRIMARY KEY ("area_id", "policy_id"),
467 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
468 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
469 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
470 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
472 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
474 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
477 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
479 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';
482 CREATE TYPE "issue_state" AS ENUM (
483 'admission', 'discussion', 'verification', 'voting',
484 'canceled_revoked_before_accepted',
485 'canceled_issue_not_accepted',
486 'canceled_after_revocation_during_discussion',
487 'canceled_after_revocation_during_verification',
488 'calculation',
489 'canceled_no_initiative_admitted',
490 'finished_without_winner', 'finished_with_winner');
492 COMMENT ON TYPE "issue_state" IS 'State of issues';
495 CREATE TABLE "issue" (
496 "id" SERIAL4 PRIMARY KEY,
497 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
498 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
499 "state" "issue_state" NOT NULL DEFAULT 'admission',
500 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
501 "accepted" TIMESTAMPTZ,
502 "half_frozen" TIMESTAMPTZ,
503 "fully_frozen" TIMESTAMPTZ,
504 "closed" TIMESTAMPTZ,
505 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
506 "cleaned" TIMESTAMPTZ,
507 "admission_time" INTERVAL,
508 "discussion_time" INTERVAL NOT NULL,
509 "verification_time" INTERVAL NOT NULL,
510 "voting_time" INTERVAL NOT NULL,
511 "snapshot" TIMESTAMPTZ,
512 "latest_snapshot_event" "snapshot_event",
513 "population" INT4,
514 "voter_count" INT4,
515 "status_quo_schulze_rank" INT4,
516 CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK (
517 "admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created") ),
518 CONSTRAINT "valid_state" CHECK ((
519 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
520 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
521 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
522 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
523 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
524 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
525 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
526 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
527 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)) AND (
528 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
529 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
530 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
531 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
532 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
533 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
534 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
535 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
536 ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
537 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
538 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
539 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
540 )),
541 CONSTRAINT "state_change_order" CHECK (
542 "created" <= "accepted" AND
543 "accepted" <= "half_frozen" AND
544 "half_frozen" <= "fully_frozen" AND
545 "fully_frozen" <= "closed" ),
546 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
547 "cleaned" ISNULL OR "closed" NOTNULL ),
548 CONSTRAINT "last_snapshot_on_full_freeze"
549 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
550 CONSTRAINT "freeze_requires_snapshot"
551 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
552 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
553 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
554 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
555 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
556 CREATE INDEX "issue_created_idx" ON "issue" ("created");
557 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
558 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
559 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
560 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
561 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
562 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
564 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
566 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
567 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.';
568 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.';
569 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.';
570 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
571 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
572 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
573 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
574 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
575 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
576 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
577 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';
578 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
579 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';
580 COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
583 CREATE TABLE "issue_setting" (
584 PRIMARY KEY ("member_id", "key", "issue_id"),
585 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
586 "key" TEXT NOT NULL,
587 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
588 "value" TEXT NOT NULL );
590 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
593 CREATE TABLE "initiative" (
594 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
595 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
596 "id" SERIAL4 PRIMARY KEY,
597 "name" TEXT NOT NULL,
598 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
599 "discussion_url" TEXT,
600 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
601 "revoked" TIMESTAMPTZ,
602 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
603 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
604 "admitted" BOOLEAN,
605 "supporter_count" INT4,
606 "informed_supporter_count" INT4,
607 "satisfied_supporter_count" INT4,
608 "satisfied_informed_supporter_count" INT4,
609 "positive_votes" INT4,
610 "negative_votes" INT4,
611 "direct_majority" BOOLEAN,
612 "indirect_majority" BOOLEAN,
613 "schulze_rank" INT4,
614 "better_than_status_quo" BOOLEAN,
615 "worse_than_status_quo" BOOLEAN,
616 "reverse_beat_path" BOOLEAN,
617 "multistage_majority" BOOLEAN,
618 "eligible" BOOLEAN,
619 "winner" BOOLEAN,
620 "rank" INT4,
621 "text_search_data" TSVECTOR,
622 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
623 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL),
624 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
625 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
626 CONSTRAINT "revoked_initiatives_cant_be_admitted"
627 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
628 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
629 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
630 ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND
631 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
632 "schulze_rank" ISNULL AND
633 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
634 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
635 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ),
636 CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")),
637 CONSTRAINT "minimum_requirement_to_be_eligible" CHECK (
638 "eligible" = FALSE OR
639 ("direct_majority" AND "indirect_majority" AND "better_than_status_quo") ),
640 CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE),
641 CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1),
642 CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE),
643 CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") );
644 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
645 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
646 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
647 CREATE TRIGGER "update_text_search_data"
648 BEFORE INSERT OR UPDATE ON "initiative"
649 FOR EACH ROW EXECUTE PROCEDURE
650 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
651 "name", "discussion_url");
653 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.';
655 COMMENT ON COLUMN "initiative"."polling" IS 'Initiative does not need to pass the initiative quorum (see "policy"."polling")';
656 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
657 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
658 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoke the initiative';
659 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
660 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
661 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
662 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
663 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
664 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
665 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
666 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"';
667 COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths';
668 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking without tie-breaking';
669 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)';
670 COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)';
671 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';
672 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';
673 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"';
674 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"';
675 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';
678 CREATE TABLE "battle" (
679 "issue_id" INT4 NOT NULL,
680 "winning_initiative_id" INT4,
681 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
682 "losing_initiative_id" INT4,
683 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
684 "count" INT4 NOT NULL,
685 CONSTRAINT "initiative_ids_not_equal" CHECK (
686 "winning_initiative_id" != "losing_initiative_id" OR
687 ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
688 ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) );
689 CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
690 CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
691 CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
693 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';
696 CREATE TABLE "ignored_initiative" (
697 PRIMARY KEY ("initiative_id", "member_id"),
698 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
699 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
700 CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");
702 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
705 CREATE TABLE "initiative_setting" (
706 PRIMARY KEY ("member_id", "key", "initiative_id"),
707 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
708 "key" TEXT NOT NULL,
709 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
710 "value" TEXT NOT NULL );
712 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
715 CREATE TABLE "draft" (
716 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
717 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
718 "id" SERIAL8 PRIMARY KEY,
719 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
720 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
721 "formatting_engine" TEXT,
722 "content" TEXT NOT NULL,
723 "text_search_data" TSVECTOR );
724 CREATE INDEX "draft_created_idx" ON "draft" ("created");
725 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
726 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
727 CREATE TRIGGER "update_text_search_data"
728 BEFORE INSERT OR UPDATE ON "draft"
729 FOR EACH ROW EXECUTE PROCEDURE
730 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
732 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.';
734 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
735 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
738 CREATE TABLE "rendered_draft" (
739 PRIMARY KEY ("draft_id", "format"),
740 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
741 "format" TEXT,
742 "content" TEXT NOT NULL );
744 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)';
747 CREATE TABLE "suggestion" (
748 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
749 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
750 "id" SERIAL8 PRIMARY KEY,
751 "draft_id" INT8 NOT NULL,
752 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
753 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
754 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
755 "name" TEXT NOT NULL,
756 "formatting_engine" TEXT,
757 "content" TEXT NOT NULL DEFAULT '',
758 "text_search_data" TSVECTOR,
759 "minus2_unfulfilled_count" INT4,
760 "minus2_fulfilled_count" INT4,
761 "minus1_unfulfilled_count" INT4,
762 "minus1_fulfilled_count" INT4,
763 "plus1_unfulfilled_count" INT4,
764 "plus1_fulfilled_count" INT4,
765 "plus2_unfulfilled_count" INT4,
766 "plus2_fulfilled_count" INT4 );
767 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
768 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
769 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
770 CREATE TRIGGER "update_text_search_data"
771 BEFORE INSERT OR UPDATE ON "suggestion"
772 FOR EACH ROW EXECUTE PROCEDURE
773 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
774 "name", "content");
776 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';
778 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")';
779 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
780 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
781 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
782 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
783 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
784 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
785 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
786 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
789 CREATE TABLE "rendered_suggestion" (
790 PRIMARY KEY ("suggestion_id", "format"),
791 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
792 "format" TEXT,
793 "content" TEXT NOT NULL );
795 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)';
798 CREATE TABLE "suggestion_setting" (
799 PRIMARY KEY ("member_id", "key", "suggestion_id"),
800 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
801 "key" TEXT NOT NULL,
802 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
803 "value" TEXT NOT NULL );
805 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
808 CREATE TABLE "privilege" (
809 PRIMARY KEY ("unit_id", "member_id"),
810 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
811 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
812 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
813 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
814 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
815 "member_manager" BOOLEAN NOT NULL DEFAULT FALSE,
816 "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE,
817 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE,
818 "polling_right" BOOLEAN NOT NULL DEFAULT FALSE );
820 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
822 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke any privileges to/from other members';
823 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create and disable sub units';
824 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create and disable areas and set area parameters';
825 COMMENT ON COLUMN "privilege"."member_manager" IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"';
826 COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative';
827 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to support initiatives, create and rate suggestions, and to vote';
828 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';
831 CREATE TABLE "membership" (
832 PRIMARY KEY ("area_id", "member_id"),
833 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
834 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
835 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
837 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
840 CREATE TABLE "interest" (
841 PRIMARY KEY ("issue_id", "member_id"),
842 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
843 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
844 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
846 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.';
849 CREATE TABLE "initiator" (
850 PRIMARY KEY ("initiative_id", "member_id"),
851 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
852 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
853 "accepted" BOOLEAN );
854 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
856 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.';
858 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.';
861 CREATE TABLE "supporter" (
862 "issue_id" INT4 NOT NULL,
863 PRIMARY KEY ("initiative_id", "member_id"),
864 "initiative_id" INT4,
865 "member_id" INT4,
866 "draft_id" INT8 NOT NULL,
867 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
868 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE );
869 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
871 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.';
873 COMMENT ON COLUMN "supporter"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
874 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")';
877 CREATE TABLE "opinion" (
878 "initiative_id" INT4 NOT NULL,
879 PRIMARY KEY ("suggestion_id", "member_id"),
880 "suggestion_id" INT8,
881 "member_id" INT4,
882 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
883 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
884 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
885 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
886 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
888 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.';
890 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
893 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
895 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
898 CREATE TABLE "delegation" (
899 "id" SERIAL8 PRIMARY KEY,
900 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
901 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
902 "scope" "delegation_scope" NOT NULL,
903 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
904 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
905 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
906 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
907 CONSTRAINT "no_unit_delegation_to_null"
908 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
909 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
910 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
911 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
912 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
913 UNIQUE ("unit_id", "truster_id"),
914 UNIQUE ("area_id", "truster_id"),
915 UNIQUE ("issue_id", "truster_id") );
916 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
917 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
919 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
921 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
922 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
923 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
926 CREATE TABLE "direct_population_snapshot" (
927 PRIMARY KEY ("issue_id", "event", "member_id"),
928 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
929 "event" "snapshot_event",
930 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
931 "weight" INT4 );
932 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
934 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
936 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
937 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
940 CREATE TABLE "delegating_population_snapshot" (
941 PRIMARY KEY ("issue_id", "event", "member_id"),
942 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
943 "event" "snapshot_event",
944 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
945 "weight" INT4,
946 "scope" "delegation_scope" NOT NULL,
947 "delegate_member_ids" INT4[] NOT NULL );
948 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
950 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
952 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
953 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
954 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
955 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"';
958 CREATE TABLE "direct_interest_snapshot" (
959 PRIMARY KEY ("issue_id", "event", "member_id"),
960 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
961 "event" "snapshot_event",
962 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
963 "weight" INT4 );
964 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
966 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
968 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
969 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
972 CREATE TABLE "delegating_interest_snapshot" (
973 PRIMARY KEY ("issue_id", "event", "member_id"),
974 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
975 "event" "snapshot_event",
976 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
977 "weight" INT4,
978 "scope" "delegation_scope" NOT NULL,
979 "delegate_member_ids" INT4[] NOT NULL );
980 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
982 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
984 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
985 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
986 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
987 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"';
990 CREATE TABLE "direct_supporter_snapshot" (
991 "issue_id" INT4 NOT NULL,
992 PRIMARY KEY ("initiative_id", "event", "member_id"),
993 "initiative_id" INT4,
994 "event" "snapshot_event",
995 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
996 "draft_id" INT8 NOT NULL,
997 "informed" BOOLEAN NOT NULL,
998 "satisfied" BOOLEAN NOT NULL,
999 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1000 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
1001 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1002 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
1004 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
1006 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';
1007 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1008 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
1009 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
1012 CREATE TABLE "non_voter" (
1013 PRIMARY KEY ("issue_id", "member_id"),
1014 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1015 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
1016 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
1018 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
1021 CREATE TABLE "direct_voter" (
1022 PRIMARY KEY ("issue_id", "member_id"),
1023 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1024 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1025 "weight" INT4,
1026 "comment_changed" TIMESTAMPTZ,
1027 "formatting_engine" TEXT,
1028 "comment" TEXT,
1029 "text_search_data" TSVECTOR );
1030 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
1031 CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data");
1032 CREATE TRIGGER "update_text_search_data"
1033 BEFORE INSERT OR UPDATE ON "direct_voter"
1034 FOR EACH ROW EXECUTE PROCEDURE
1035 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment");
1037 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.';
1039 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
1040 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';
1041 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';
1042 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.';
1045 CREATE TABLE "rendered_voter_comment" (
1046 PRIMARY KEY ("issue_id", "member_id", "format"),
1047 FOREIGN KEY ("issue_id", "member_id")
1048 REFERENCES "direct_voter" ("issue_id", "member_id")
1049 ON DELETE CASCADE ON UPDATE CASCADE,
1050 "issue_id" INT4,
1051 "member_id" INT4,
1052 "format" TEXT,
1053 "content" TEXT NOT NULL );
1055 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)';
1058 CREATE TABLE "delegating_voter" (
1059 PRIMARY KEY ("issue_id", "member_id"),
1060 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1061 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1062 "weight" INT4,
1063 "scope" "delegation_scope" NOT NULL,
1064 "delegate_member_ids" INT4[] NOT NULL );
1065 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
1067 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
1069 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
1070 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
1071 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"';
1074 CREATE TABLE "vote" (
1075 "issue_id" INT4 NOT NULL,
1076 PRIMARY KEY ("initiative_id", "member_id"),
1077 "initiative_id" INT4,
1078 "member_id" INT4,
1079 "grade" INT4,
1080 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1081 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1082 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
1084 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.';
1086 COMMENT ON COLUMN "vote"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
1087 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.';
1090 CREATE TYPE "event_type" AS ENUM (
1091 'issue_state_changed',
1092 'initiative_created_in_new_issue',
1093 'initiative_created_in_existing_issue',
1094 'initiative_revoked',
1095 'new_draft_created',
1096 'suggestion_created');
1098 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
1101 CREATE TABLE "event" (
1102 "id" SERIAL8 PRIMARY KEY,
1103 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
1104 "event" "event_type" NOT NULL,
1105 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1106 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1107 "state" "issue_state" CHECK ("state" != 'calculation'),
1108 "initiative_id" INT4,
1109 "draft_id" INT8,
1110 "suggestion_id" INT8,
1111 FOREIGN KEY ("issue_id", "initiative_id")
1112 REFERENCES "initiative" ("issue_id", "id")
1113 ON DELETE CASCADE ON UPDATE CASCADE,
1114 FOREIGN KEY ("initiative_id", "draft_id")
1115 REFERENCES "draft" ("initiative_id", "id")
1116 ON DELETE CASCADE ON UPDATE CASCADE,
1117 FOREIGN KEY ("initiative_id", "suggestion_id")
1118 REFERENCES "suggestion" ("initiative_id", "id")
1119 ON DELETE CASCADE ON UPDATE CASCADE,
1120 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
1121 "event" != 'issue_state_changed' OR (
1122 "member_id" ISNULL AND
1123 "issue_id" NOTNULL AND
1124 "state" NOTNULL AND
1125 "initiative_id" ISNULL AND
1126 "draft_id" ISNULL AND
1127 "suggestion_id" ISNULL )),
1128 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
1129 "event" NOT IN (
1130 'initiative_created_in_new_issue',
1131 'initiative_created_in_existing_issue',
1132 'initiative_revoked',
1133 'new_draft_created'
1134 ) OR (
1135 "member_id" NOTNULL AND
1136 "issue_id" NOTNULL AND
1137 "state" NOTNULL AND
1138 "initiative_id" NOTNULL AND
1139 "draft_id" NOTNULL AND
1140 "suggestion_id" ISNULL )),
1141 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
1142 "event" != 'suggestion_created' OR (
1143 "member_id" NOTNULL AND
1144 "issue_id" NOTNULL AND
1145 "state" NOTNULL AND
1146 "initiative_id" NOTNULL AND
1147 "draft_id" ISNULL AND
1148 "suggestion_id" NOTNULL )) );
1149 CREATE INDEX "event_occurrence_idx" ON "event" ("occurrence");
1151 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
1153 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
1154 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';
1155 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';
1156 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';
1159 CREATE TABLE "notification_sent" (
1160 "event_id" INT8 NOT NULL );
1161 CREATE UNIQUE INDEX "notification_sent_singleton_idx" ON "notification_sent" ((1));
1163 COMMENT ON TABLE "notification_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out';
1164 COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.';
1168 ----------------------------------------------
1169 -- Writing of history entries and event log --
1170 ----------------------------------------------
1173 CREATE FUNCTION "write_member_history_trigger"()
1174 RETURNS TRIGGER
1175 LANGUAGE 'plpgsql' VOLATILE AS $$
1176 BEGIN
1177 IF
1178 ( NEW."active" != OLD."active" OR
1179 NEW."name" != OLD."name" ) AND
1180 OLD."activated" NOTNULL
1181 THEN
1182 INSERT INTO "member_history"
1183 ("member_id", "active", "name")
1184 VALUES (NEW."id", OLD."active", OLD."name");
1185 END IF;
1186 RETURN NULL;
1187 END;
1188 $$;
1190 CREATE TRIGGER "write_member_history"
1191 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1192 "write_member_history_trigger"();
1194 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
1195 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
1198 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
1199 RETURNS TRIGGER
1200 LANGUAGE 'plpgsql' VOLATILE AS $$
1201 BEGIN
1202 IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN
1203 INSERT INTO "event" ("event", "issue_id", "state")
1204 VALUES ('issue_state_changed', NEW."id", NEW."state");
1205 END IF;
1206 RETURN NULL;
1207 END;
1208 $$;
1210 CREATE TRIGGER "write_event_issue_state_changed"
1211 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
1212 "write_event_issue_state_changed_trigger"();
1214 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
1215 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
1218 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1219 RETURNS TRIGGER
1220 LANGUAGE 'plpgsql' VOLATILE AS $$
1221 DECLARE
1222 "initiative_row" "initiative"%ROWTYPE;
1223 "issue_row" "issue"%ROWTYPE;
1224 "event_v" "event_type";
1225 BEGIN
1226 SELECT * INTO "initiative_row" FROM "initiative"
1227 WHERE "id" = NEW."initiative_id";
1228 SELECT * INTO "issue_row" FROM "issue"
1229 WHERE "id" = "initiative_row"."issue_id";
1230 IF EXISTS (
1231 SELECT NULL FROM "draft"
1232 WHERE "initiative_id" = NEW."initiative_id"
1233 AND "id" != NEW."id"
1234 ) THEN
1235 "event_v" := 'new_draft_created';
1236 ELSE
1237 IF EXISTS (
1238 SELECT NULL FROM "initiative"
1239 WHERE "issue_id" = "initiative_row"."issue_id"
1240 AND "id" != "initiative_row"."id"
1241 ) THEN
1242 "event_v" := 'initiative_created_in_existing_issue';
1243 ELSE
1244 "event_v" := 'initiative_created_in_new_issue';
1245 END IF;
1246 END IF;
1247 INSERT INTO "event" (
1248 "event", "member_id",
1249 "issue_id", "state", "initiative_id", "draft_id"
1250 ) VALUES (
1251 "event_v",
1252 NEW."author_id",
1253 "initiative_row"."issue_id",
1254 "issue_row"."state",
1255 "initiative_row"."id",
1256 NEW."id" );
1257 RETURN NULL;
1258 END;
1259 $$;
1261 CREATE TRIGGER "write_event_initiative_or_draft_created"
1262 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
1263 "write_event_initiative_or_draft_created_trigger"();
1265 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
1266 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
1269 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
1270 RETURNS TRIGGER
1271 LANGUAGE 'plpgsql' VOLATILE AS $$
1272 DECLARE
1273 "issue_row" "issue"%ROWTYPE;
1274 "draft_id_v" "draft"."id"%TYPE;
1275 BEGIN
1276 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1277 SELECT * INTO "issue_row" FROM "issue"
1278 WHERE "id" = NEW."issue_id";
1279 SELECT "id" INTO "draft_id_v" FROM "current_draft"
1280 WHERE "initiative_id" = NEW."id";
1281 INSERT INTO "event" (
1282 "event", "member_id", "issue_id", "state", "initiative_id", "draft_id"
1283 ) VALUES (
1284 'initiative_revoked',
1285 NEW."revoked_by_member_id",
1286 NEW."issue_id",
1287 "issue_row"."state",
1288 NEW."id",
1289 "draft_id_v");
1290 END IF;
1291 RETURN NULL;
1292 END;
1293 $$;
1295 CREATE TRIGGER "write_event_initiative_revoked"
1296 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
1297 "write_event_initiative_revoked_trigger"();
1299 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
1300 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
1303 CREATE FUNCTION "write_event_suggestion_created_trigger"()
1304 RETURNS TRIGGER
1305 LANGUAGE 'plpgsql' VOLATILE AS $$
1306 DECLARE
1307 "initiative_row" "initiative"%ROWTYPE;
1308 "issue_row" "issue"%ROWTYPE;
1309 BEGIN
1310 SELECT * INTO "initiative_row" FROM "initiative"
1311 WHERE "id" = NEW."initiative_id";
1312 SELECT * INTO "issue_row" FROM "issue"
1313 WHERE "id" = "initiative_row"."issue_id";
1314 INSERT INTO "event" (
1315 "event", "member_id",
1316 "issue_id", "state", "initiative_id", "suggestion_id"
1317 ) VALUES (
1318 'suggestion_created',
1319 NEW."author_id",
1320 "initiative_row"."issue_id",
1321 "issue_row"."state",
1322 "initiative_row"."id",
1323 NEW."id" );
1324 RETURN NULL;
1325 END;
1326 $$;
1328 CREATE TRIGGER "write_event_suggestion_created"
1329 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1330 "write_event_suggestion_created_trigger"();
1332 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
1333 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1337 ----------------------------
1338 -- Additional constraints --
1339 ----------------------------
1342 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
1343 RETURNS TRIGGER
1344 LANGUAGE 'plpgsql' VOLATILE AS $$
1345 BEGIN
1346 IF NOT EXISTS (
1347 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
1348 ) THEN
1349 --RAISE 'Cannot create issue without an initial initiative.' USING
1350 -- ERRCODE = 'integrity_constraint_violation',
1351 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
1352 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
1353 END IF;
1354 RETURN NULL;
1355 END;
1356 $$;
1358 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
1359 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
1360 FOR EACH ROW EXECUTE PROCEDURE
1361 "issue_requires_first_initiative_trigger"();
1363 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
1364 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
1367 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
1368 RETURNS TRIGGER
1369 LANGUAGE 'plpgsql' VOLATILE AS $$
1370 DECLARE
1371 "reference_lost" BOOLEAN;
1372 BEGIN
1373 IF TG_OP = 'DELETE' THEN
1374 "reference_lost" := TRUE;
1375 ELSE
1376 "reference_lost" := NEW."issue_id" != OLD."issue_id";
1377 END IF;
1378 IF
1379 "reference_lost" AND NOT EXISTS (
1380 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
1381 )
1382 THEN
1383 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
1384 END IF;
1385 RETURN NULL;
1386 END;
1387 $$;
1389 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
1390 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1391 FOR EACH ROW EXECUTE PROCEDURE
1392 "last_initiative_deletes_issue_trigger"();
1394 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
1395 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
1398 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
1399 RETURNS TRIGGER
1400 LANGUAGE 'plpgsql' VOLATILE AS $$
1401 BEGIN
1402 IF NOT EXISTS (
1403 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
1404 ) THEN
1405 --RAISE 'Cannot create initiative without an initial draft.' USING
1406 -- ERRCODE = 'integrity_constraint_violation',
1407 -- HINT = 'Create issue, initiative and draft within the same transaction.';
1408 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
1409 END IF;
1410 RETURN NULL;
1411 END;
1412 $$;
1414 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
1415 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1416 FOR EACH ROW EXECUTE PROCEDURE
1417 "initiative_requires_first_draft_trigger"();
1419 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
1420 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
1423 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
1424 RETURNS TRIGGER
1425 LANGUAGE 'plpgsql' VOLATILE AS $$
1426 DECLARE
1427 "reference_lost" BOOLEAN;
1428 BEGIN
1429 IF TG_OP = 'DELETE' THEN
1430 "reference_lost" := TRUE;
1431 ELSE
1432 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
1433 END IF;
1434 IF
1435 "reference_lost" AND NOT EXISTS (
1436 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
1437 )
1438 THEN
1439 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
1440 END IF;
1441 RETURN NULL;
1442 END;
1443 $$;
1445 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
1446 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
1447 FOR EACH ROW EXECUTE PROCEDURE
1448 "last_draft_deletes_initiative_trigger"();
1450 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
1451 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
1454 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
1455 RETURNS TRIGGER
1456 LANGUAGE 'plpgsql' VOLATILE AS $$
1457 BEGIN
1458 IF NOT EXISTS (
1459 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
1460 ) THEN
1461 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
1462 END IF;
1463 RETURN NULL;
1464 END;
1465 $$;
1467 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
1468 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
1469 FOR EACH ROW EXECUTE PROCEDURE
1470 "suggestion_requires_first_opinion_trigger"();
1472 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1473 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1476 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1477 RETURNS TRIGGER
1478 LANGUAGE 'plpgsql' VOLATILE AS $$
1479 DECLARE
1480 "reference_lost" BOOLEAN;
1481 BEGIN
1482 IF TG_OP = 'DELETE' THEN
1483 "reference_lost" := TRUE;
1484 ELSE
1485 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1486 END IF;
1487 IF
1488 "reference_lost" AND NOT EXISTS (
1489 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1490 )
1491 THEN
1492 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1493 END IF;
1494 RETURN NULL;
1495 END;
1496 $$;
1498 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1499 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1500 FOR EACH ROW EXECUTE PROCEDURE
1501 "last_opinion_deletes_suggestion_trigger"();
1503 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1504 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1507 CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"()
1508 RETURNS TRIGGER
1509 LANGUAGE 'plpgsql' VOLATILE AS $$
1510 BEGIN
1511 DELETE FROM "direct_voter"
1512 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
1513 RETURN NULL;
1514 END;
1515 $$;
1517 CREATE TRIGGER "non_voter_deletes_direct_voter"
1518 AFTER INSERT OR UPDATE ON "non_voter"
1519 FOR EACH ROW EXECUTE PROCEDURE
1520 "non_voter_deletes_direct_voter_trigger"();
1522 COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"';
1523 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")';
1526 CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"()
1527 RETURNS TRIGGER
1528 LANGUAGE 'plpgsql' VOLATILE AS $$
1529 BEGIN
1530 DELETE FROM "non_voter"
1531 WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id";
1532 RETURN NULL;
1533 END;
1534 $$;
1536 CREATE TRIGGER "direct_voter_deletes_non_voter"
1537 AFTER INSERT OR UPDATE ON "direct_voter"
1538 FOR EACH ROW EXECUTE PROCEDURE
1539 "direct_voter_deletes_non_voter_trigger"();
1541 COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"';
1542 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")';
1545 CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"()
1546 RETURNS TRIGGER
1547 LANGUAGE 'plpgsql' VOLATILE AS $$
1548 BEGIN
1549 IF NEW."comment" ISNULL THEN
1550 NEW."comment_changed" := NULL;
1551 NEW."formatting_engine" := NULL;
1552 END IF;
1553 RETURN NEW;
1554 END;
1555 $$;
1557 CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set"
1558 BEFORE INSERT OR UPDATE ON "direct_voter"
1559 FOR EACH ROW EXECUTE PROCEDURE
1560 "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"();
1562 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"';
1563 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.';
1566 ---------------------------------------------------------------
1567 -- Ensure that votes are not modified when issues are frozen --
1568 ---------------------------------------------------------------
1570 -- NOTE: Frontends should ensure this anyway, but in case of programming
1571 -- errors the following triggers ensure data integrity.
1574 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1575 RETURNS TRIGGER
1576 LANGUAGE 'plpgsql' VOLATILE AS $$
1577 DECLARE
1578 "issue_id_v" "issue"."id"%TYPE;
1579 "issue_row" "issue"%ROWTYPE;
1580 BEGIN
1581 IF TG_RELID = 'direct_voter'::regclass AND TG_OP = 'UPDATE' THEN
1582 IF
1583 OLD."issue_id" = NEW."issue_id" AND
1584 OLD."member_id" = NEW."member_id" AND
1585 OLD."weight" = NEW."weight"
1586 THEN
1587 RETURN NULL; -- allows changing of voter comment
1588 END IF;
1589 END IF;
1590 IF TG_OP = 'DELETE' THEN
1591 "issue_id_v" := OLD."issue_id";
1592 ELSE
1593 "issue_id_v" := NEW."issue_id";
1594 END IF;
1595 SELECT INTO "issue_row" * FROM "issue"
1596 WHERE "id" = "issue_id_v" FOR SHARE;
1597 IF "issue_row"."closed" NOTNULL THEN
1598 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
1599 END IF;
1600 RETURN NULL;
1601 END;
1602 $$;
1604 CREATE TRIGGER "forbid_changes_on_closed_issue"
1605 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1606 FOR EACH ROW EXECUTE PROCEDURE
1607 "forbid_changes_on_closed_issue_trigger"();
1609 CREATE TRIGGER "forbid_changes_on_closed_issue"
1610 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1611 FOR EACH ROW EXECUTE PROCEDURE
1612 "forbid_changes_on_closed_issue_trigger"();
1614 CREATE TRIGGER "forbid_changes_on_closed_issue"
1615 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1616 FOR EACH ROW EXECUTE PROCEDURE
1617 "forbid_changes_on_closed_issue_trigger"();
1619 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"';
1620 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';
1621 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';
1622 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';
1626 --------------------------------------------------------------------
1627 -- Auto-retrieval of fields only needed for referential integrity --
1628 --------------------------------------------------------------------
1631 CREATE FUNCTION "autofill_issue_id_trigger"()
1632 RETURNS TRIGGER
1633 LANGUAGE 'plpgsql' VOLATILE AS $$
1634 BEGIN
1635 IF NEW."issue_id" ISNULL THEN
1636 SELECT "issue_id" INTO NEW."issue_id"
1637 FROM "initiative" WHERE "id" = NEW."initiative_id";
1638 END IF;
1639 RETURN NEW;
1640 END;
1641 $$;
1643 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1644 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1646 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1647 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1649 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1650 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1651 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1654 CREATE FUNCTION "autofill_initiative_id_trigger"()
1655 RETURNS TRIGGER
1656 LANGUAGE 'plpgsql' VOLATILE AS $$
1657 BEGIN
1658 IF NEW."initiative_id" ISNULL THEN
1659 SELECT "initiative_id" INTO NEW."initiative_id"
1660 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1661 END IF;
1662 RETURN NEW;
1663 END;
1664 $$;
1666 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1667 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1669 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1670 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1674 -----------------------------------------------------
1675 -- Automatic calculation of certain default values --
1676 -----------------------------------------------------
1679 CREATE FUNCTION "copy_timings_trigger"()
1680 RETURNS TRIGGER
1681 LANGUAGE 'plpgsql' VOLATILE AS $$
1682 DECLARE
1683 "policy_row" "policy"%ROWTYPE;
1684 BEGIN
1685 SELECT * INTO "policy_row" FROM "policy"
1686 WHERE "id" = NEW."policy_id";
1687 IF NEW."admission_time" ISNULL THEN
1688 NEW."admission_time" := "policy_row"."admission_time";
1689 END IF;
1690 IF NEW."discussion_time" ISNULL THEN
1691 NEW."discussion_time" := "policy_row"."discussion_time";
1692 END IF;
1693 IF NEW."verification_time" ISNULL THEN
1694 NEW."verification_time" := "policy_row"."verification_time";
1695 END IF;
1696 IF NEW."voting_time" ISNULL THEN
1697 NEW."voting_time" := "policy_row"."voting_time";
1698 END IF;
1699 RETURN NEW;
1700 END;
1701 $$;
1703 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1704 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1706 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1707 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1710 CREATE FUNCTION "default_for_draft_id_trigger"()
1711 RETURNS TRIGGER
1712 LANGUAGE 'plpgsql' VOLATILE AS $$
1713 BEGIN
1714 IF NEW."draft_id" ISNULL THEN
1715 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1716 WHERE "initiative_id" = NEW."initiative_id";
1717 END IF;
1718 RETURN NEW;
1719 END;
1720 $$;
1722 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"
1723 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1724 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1725 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1727 COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';
1728 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';
1729 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';
1733 ----------------------------------------
1734 -- Automatic creation of dependencies --
1735 ----------------------------------------
1738 CREATE FUNCTION "autocreate_interest_trigger"()
1739 RETURNS TRIGGER
1740 LANGUAGE 'plpgsql' VOLATILE AS $$
1741 BEGIN
1742 IF NOT EXISTS (
1743 SELECT NULL FROM "initiative" JOIN "interest"
1744 ON "initiative"."issue_id" = "interest"."issue_id"
1745 WHERE "initiative"."id" = NEW."initiative_id"
1746 AND "interest"."member_id" = NEW."member_id"
1747 ) THEN
1748 BEGIN
1749 INSERT INTO "interest" ("issue_id", "member_id")
1750 SELECT "issue_id", NEW."member_id"
1751 FROM "initiative" WHERE "id" = NEW."initiative_id";
1752 EXCEPTION WHEN unique_violation THEN END;
1753 END IF;
1754 RETURN NEW;
1755 END;
1756 $$;
1758 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1759 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1761 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1762 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';
1765 CREATE FUNCTION "autocreate_supporter_trigger"()
1766 RETURNS TRIGGER
1767 LANGUAGE 'plpgsql' VOLATILE AS $$
1768 BEGIN
1769 IF NOT EXISTS (
1770 SELECT NULL FROM "suggestion" JOIN "supporter"
1771 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1772 WHERE "suggestion"."id" = NEW."suggestion_id"
1773 AND "supporter"."member_id" = NEW."member_id"
1774 ) THEN
1775 BEGIN
1776 INSERT INTO "supporter" ("initiative_id", "member_id")
1777 SELECT "initiative_id", NEW."member_id"
1778 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1779 EXCEPTION WHEN unique_violation THEN END;
1780 END IF;
1781 RETURN NEW;
1782 END;
1783 $$;
1785 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1786 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1788 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1789 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.';
1793 ------------------------------------------
1794 -- Views and helper functions for views --
1795 ------------------------------------------
1798 CREATE VIEW "unit_delegation" AS
1799 SELECT
1800 "unit"."id" AS "unit_id",
1801 "delegation"."id",
1802 "delegation"."truster_id",
1803 "delegation"."trustee_id",
1804 "delegation"."scope"
1805 FROM "unit"
1806 JOIN "delegation"
1807 ON "delegation"."unit_id" = "unit"."id"
1808 JOIN "member"
1809 ON "delegation"."truster_id" = "member"."id"
1810 JOIN "privilege"
1811 ON "delegation"."unit_id" = "privilege"."unit_id"
1812 AND "delegation"."truster_id" = "privilege"."member_id"
1813 WHERE "member"."active" AND "privilege"."voting_right";
1815 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
1818 CREATE VIEW "area_delegation" AS
1819 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1820 "area"."id" AS "area_id",
1821 "delegation"."id",
1822 "delegation"."truster_id",
1823 "delegation"."trustee_id",
1824 "delegation"."scope"
1825 FROM "area"
1826 JOIN "delegation"
1827 ON "delegation"."unit_id" = "area"."unit_id"
1828 OR "delegation"."area_id" = "area"."id"
1829 JOIN "member"
1830 ON "delegation"."truster_id" = "member"."id"
1831 JOIN "privilege"
1832 ON "area"."unit_id" = "privilege"."unit_id"
1833 AND "delegation"."truster_id" = "privilege"."member_id"
1834 WHERE "member"."active" AND "privilege"."voting_right"
1835 ORDER BY
1836 "area"."id",
1837 "delegation"."truster_id",
1838 "delegation"."scope" DESC;
1840 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
1843 CREATE VIEW "issue_delegation" AS
1844 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1845 "issue"."id" AS "issue_id",
1846 "delegation"."id",
1847 "delegation"."truster_id",
1848 "delegation"."trustee_id",
1849 "delegation"."scope"
1850 FROM "issue"
1851 JOIN "area"
1852 ON "area"."id" = "issue"."area_id"
1853 JOIN "delegation"
1854 ON "delegation"."unit_id" = "area"."unit_id"
1855 OR "delegation"."area_id" = "area"."id"
1856 OR "delegation"."issue_id" = "issue"."id"
1857 JOIN "member"
1858 ON "delegation"."truster_id" = "member"."id"
1859 JOIN "privilege"
1860 ON "area"."unit_id" = "privilege"."unit_id"
1861 AND "delegation"."truster_id" = "privilege"."member_id"
1862 WHERE "member"."active" AND "privilege"."voting_right"
1863 ORDER BY
1864 "issue"."id",
1865 "delegation"."truster_id",
1866 "delegation"."scope" DESC;
1868 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
1871 CREATE FUNCTION "membership_weight_with_skipping"
1872 ( "area_id_p" "area"."id"%TYPE,
1873 "member_id_p" "member"."id"%TYPE,
1874 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1875 RETURNS INT4
1876 LANGUAGE 'plpgsql' STABLE AS $$
1877 DECLARE
1878 "sum_v" INT4;
1879 "delegation_row" "area_delegation"%ROWTYPE;
1880 BEGIN
1881 "sum_v" := 1;
1882 FOR "delegation_row" IN
1883 SELECT "area_delegation".*
1884 FROM "area_delegation" LEFT JOIN "membership"
1885 ON "membership"."area_id" = "area_id_p"
1886 AND "membership"."member_id" = "area_delegation"."truster_id"
1887 WHERE "area_delegation"."area_id" = "area_id_p"
1888 AND "area_delegation"."trustee_id" = "member_id_p"
1889 AND "membership"."member_id" ISNULL
1890 LOOP
1891 IF NOT
1892 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1893 THEN
1894 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1895 "area_id_p",
1896 "delegation_row"."truster_id",
1897 "skip_member_ids_p" || "delegation_row"."truster_id"
1898 );
1899 END IF;
1900 END LOOP;
1901 RETURN "sum_v";
1902 END;
1903 $$;
1905 COMMENT ON FUNCTION "membership_weight_with_skipping"
1906 ( "area"."id"%TYPE,
1907 "member"."id"%TYPE,
1908 INT4[] )
1909 IS 'Helper function for "membership_weight" function';
1912 CREATE FUNCTION "membership_weight"
1913 ( "area_id_p" "area"."id"%TYPE,
1914 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1915 RETURNS INT4
1916 LANGUAGE 'plpgsql' STABLE AS $$
1917 BEGIN
1918 RETURN "membership_weight_with_skipping"(
1919 "area_id_p",
1920 "member_id_p",
1921 ARRAY["member_id_p"]
1922 );
1923 END;
1924 $$;
1926 COMMENT ON FUNCTION "membership_weight"
1927 ( "area"."id"%TYPE,
1928 "member"."id"%TYPE )
1929 IS 'Calculates the potential voting weight of a member in a given area';
1932 CREATE VIEW "member_count_view" AS
1933 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1935 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1938 CREATE VIEW "unit_member_count" AS
1939 SELECT
1940 "unit"."id" AS "unit_id",
1941 count("member"."id") AS "member_count"
1942 FROM "unit"
1943 LEFT JOIN "privilege"
1944 ON "privilege"."unit_id" = "unit"."id"
1945 AND "privilege"."voting_right"
1946 LEFT JOIN "member"
1947 ON "member"."id" = "privilege"."member_id"
1948 AND "member"."active"
1949 GROUP BY "unit"."id";
1951 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
1954 CREATE VIEW "area_member_count" AS
1955 SELECT
1956 "area"."id" AS "area_id",
1957 count("member"."id") AS "direct_member_count",
1958 coalesce(
1959 sum(
1960 CASE WHEN "member"."id" NOTNULL THEN
1961 "membership_weight"("area"."id", "member"."id")
1962 ELSE 0 END
1963 )
1964 ) AS "member_weight"
1965 FROM "area"
1966 LEFT JOIN "membership"
1967 ON "area"."id" = "membership"."area_id"
1968 LEFT JOIN "privilege"
1969 ON "privilege"."unit_id" = "area"."unit_id"
1970 AND "privilege"."member_id" = "membership"."member_id"
1971 AND "privilege"."voting_right"
1972 LEFT JOIN "member"
1973 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
1974 AND "member"."active"
1975 GROUP BY "area"."id";
1977 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"';
1980 CREATE VIEW "opening_draft" AS
1981 SELECT "draft".* FROM (
1982 SELECT
1983 "initiative"."id" AS "initiative_id",
1984 min("draft"."id") AS "draft_id"
1985 FROM "initiative" JOIN "draft"
1986 ON "initiative"."id" = "draft"."initiative_id"
1987 GROUP BY "initiative"."id"
1988 ) AS "subquery"
1989 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1991 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1994 CREATE VIEW "current_draft" AS
1995 SELECT "draft".* FROM (
1996 SELECT
1997 "initiative"."id" AS "initiative_id",
1998 max("draft"."id") AS "draft_id"
1999 FROM "initiative" JOIN "draft"
2000 ON "initiative"."id" = "draft"."initiative_id"
2001 GROUP BY "initiative"."id"
2002 ) AS "subquery"
2003 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
2005 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
2008 CREATE VIEW "critical_opinion" AS
2009 SELECT * FROM "opinion"
2010 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
2011 OR ("degree" = -2 AND "fulfilled" = TRUE);
2013 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
2016 CREATE VIEW "battle_participant" AS
2017 SELECT "initiative"."id", "initiative"."issue_id"
2018 FROM "issue" JOIN "initiative"
2019 ON "issue"."id" = "initiative"."issue_id"
2020 WHERE "initiative"."admitted"
2021 UNION ALL
2022 SELECT NULL, "id" AS "issue_id"
2023 FROM "issue";
2025 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
2028 CREATE VIEW "battle_view" AS
2029 SELECT
2030 "issue"."id" AS "issue_id",
2031 "winning_initiative"."id" AS "winning_initiative_id",
2032 "losing_initiative"."id" AS "losing_initiative_id",
2033 sum(
2034 CASE WHEN
2035 coalesce("better_vote"."grade", 0) >
2036 coalesce("worse_vote"."grade", 0)
2037 THEN "direct_voter"."weight" ELSE 0 END
2038 ) AS "count"
2039 FROM "issue"
2040 LEFT JOIN "direct_voter"
2041 ON "issue"."id" = "direct_voter"."issue_id"
2042 JOIN "battle_participant" AS "winning_initiative"
2043 ON "issue"."id" = "winning_initiative"."issue_id"
2044 JOIN "battle_participant" AS "losing_initiative"
2045 ON "issue"."id" = "losing_initiative"."issue_id"
2046 LEFT JOIN "vote" AS "better_vote"
2047 ON "direct_voter"."member_id" = "better_vote"."member_id"
2048 AND "winning_initiative"."id" = "better_vote"."initiative_id"
2049 LEFT JOIN "vote" AS "worse_vote"
2050 ON "direct_voter"."member_id" = "worse_vote"."member_id"
2051 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
2052 WHERE "issue"."closed" NOTNULL
2053 AND "issue"."cleaned" ISNULL
2054 AND (
2055 "winning_initiative"."id" != "losing_initiative"."id" OR
2056 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
2057 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
2058 GROUP BY
2059 "issue"."id",
2060 "winning_initiative"."id",
2061 "losing_initiative"."id";
2063 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';
2066 CREATE VIEW "expired_session" AS
2067 SELECT * FROM "session" WHERE now() > "expiry";
2069 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
2070 DELETE FROM "session" WHERE "ident" = OLD."ident";
2072 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
2073 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
2076 CREATE VIEW "open_issue" AS
2077 SELECT * FROM "issue" WHERE "closed" ISNULL;
2079 COMMENT ON VIEW "open_issue" IS 'All open issues';
2082 CREATE VIEW "issue_with_ranks_missing" AS
2083 SELECT * FROM "issue"
2084 WHERE "fully_frozen" NOTNULL
2085 AND "closed" NOTNULL
2086 AND "ranks_available" = FALSE;
2088 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
2091 CREATE VIEW "member_contingent" AS
2092 SELECT
2093 "member"."id" AS "member_id",
2094 "contingent"."time_frame",
2095 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
2096 (
2097 SELECT count(1) FROM "draft"
2098 WHERE "draft"."author_id" = "member"."id"
2099 AND "draft"."created" > now() - "contingent"."time_frame"
2100 ) + (
2101 SELECT count(1) FROM "suggestion"
2102 WHERE "suggestion"."author_id" = "member"."id"
2103 AND "suggestion"."created" > now() - "contingent"."time_frame"
2104 )
2105 ELSE NULL END AS "text_entry_count",
2106 "contingent"."text_entry_limit",
2107 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
2108 SELECT count(1) FROM "opening_draft"
2109 WHERE "opening_draft"."author_id" = "member"."id"
2110 AND "opening_draft"."created" > now() - "contingent"."time_frame"
2111 ) ELSE NULL END AS "initiative_count",
2112 "contingent"."initiative_limit"
2113 FROM "member" CROSS JOIN "contingent";
2115 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
2117 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
2118 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
2121 CREATE VIEW "member_contingent_left" AS
2122 SELECT
2123 "member_id",
2124 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
2125 max("initiative_limit" - "initiative_count") AS "initiatives_left"
2126 FROM "member_contingent" GROUP BY "member_id";
2128 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.';
2131 CREATE VIEW "event_seen_by_member" AS
2132 SELECT
2133 "member"."id" AS "seen_by_member_id",
2134 CASE WHEN "event"."state" IN (
2135 'voting',
2136 'finished_without_winner',
2137 'finished_with_winner'
2138 ) THEN
2139 'voting'::"notify_level"
2140 ELSE
2141 CASE WHEN "event"."state" IN (
2142 'verification',
2143 'canceled_after_revocation_during_verification',
2144 'canceled_no_initiative_admitted'
2145 ) THEN
2146 'verification'::"notify_level"
2147 ELSE
2148 CASE WHEN "event"."state" IN (
2149 'discussion',
2150 'canceled_after_revocation_during_discussion'
2151 ) THEN
2152 'discussion'::"notify_level"
2153 ELSE
2154 'all'::"notify_level"
2155 END
2156 END
2157 END AS "notify_level",
2158 "event".*
2159 FROM "member" CROSS JOIN "event"
2160 LEFT JOIN "issue"
2161 ON "event"."issue_id" = "issue"."id"
2162 LEFT JOIN "membership"
2163 ON "member"."id" = "membership"."member_id"
2164 AND "issue"."area_id" = "membership"."area_id"
2165 LEFT JOIN "interest"
2166 ON "member"."id" = "interest"."member_id"
2167 AND "event"."issue_id" = "interest"."issue_id"
2168 LEFT JOIN "supporter"
2169 ON "member"."id" = "supporter"."member_id"
2170 AND "event"."initiative_id" = "supporter"."initiative_id"
2171 LEFT JOIN "ignored_member"
2172 ON "member"."id" = "ignored_member"."member_id"
2173 AND "event"."member_id" = "ignored_member"."other_member_id"
2174 LEFT JOIN "ignored_initiative"
2175 ON "member"."id" = "ignored_initiative"."member_id"
2176 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2177 WHERE (
2178 "supporter"."member_id" NOTNULL OR
2179 "interest"."member_id" NOTNULL OR
2180 ( "membership"."member_id" NOTNULL AND
2181 "event"."event" IN (
2182 'issue_state_changed',
2183 'initiative_created_in_new_issue',
2184 'initiative_created_in_existing_issue',
2185 'initiative_revoked' ) ) )
2186 AND "ignored_member"."member_id" ISNULL
2187 AND "ignored_initiative"."member_id" ISNULL;
2189 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"';
2192 CREATE VIEW "selected_event_seen_by_member" AS
2193 SELECT
2194 "member"."id" AS "seen_by_member_id",
2195 CASE WHEN "event"."state" IN (
2196 'voting',
2197 'finished_without_winner',
2198 'finished_with_winner'
2199 ) THEN
2200 'voting'::"notify_level"
2201 ELSE
2202 CASE WHEN "event"."state" IN (
2203 'verification',
2204 'canceled_after_revocation_during_verification',
2205 'canceled_no_initiative_admitted'
2206 ) THEN
2207 'verification'::"notify_level"
2208 ELSE
2209 CASE WHEN "event"."state" IN (
2210 'discussion',
2211 'canceled_after_revocation_during_discussion'
2212 ) THEN
2213 'discussion'::"notify_level"
2214 ELSE
2215 'all'::"notify_level"
2216 END
2217 END
2218 END AS "notify_level",
2219 "event".*
2220 FROM "member" CROSS JOIN "event"
2221 LEFT JOIN "issue"
2222 ON "event"."issue_id" = "issue"."id"
2223 LEFT JOIN "membership"
2224 ON "member"."id" = "membership"."member_id"
2225 AND "issue"."area_id" = "membership"."area_id"
2226 LEFT JOIN "interest"
2227 ON "member"."id" = "interest"."member_id"
2228 AND "event"."issue_id" = "interest"."issue_id"
2229 LEFT JOIN "supporter"
2230 ON "member"."id" = "supporter"."member_id"
2231 AND "event"."initiative_id" = "supporter"."initiative_id"
2232 LEFT JOIN "ignored_member"
2233 ON "member"."id" = "ignored_member"."member_id"
2234 AND "event"."member_id" = "ignored_member"."other_member_id"
2235 LEFT JOIN "ignored_initiative"
2236 ON "member"."id" = "ignored_initiative"."member_id"
2237 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2238 WHERE (
2239 ( "member"."notify_level" >= 'all' ) OR
2240 ( "member"."notify_level" >= 'voting' AND
2241 "event"."state" IN (
2242 'voting',
2243 'finished_without_winner',
2244 'finished_with_winner' ) ) OR
2245 ( "member"."notify_level" >= 'verification' AND
2246 "event"."state" IN (
2247 'verification',
2248 'canceled_after_revocation_during_verification',
2249 'canceled_no_initiative_admitted' ) ) OR
2250 ( "member"."notify_level" >= 'discussion' AND
2251 "event"."state" IN (
2252 'discussion',
2253 'canceled_after_revocation_during_discussion' ) ) )
2254 AND (
2255 "supporter"."member_id" NOTNULL OR
2256 "interest"."member_id" NOTNULL OR
2257 ( "membership"."member_id" NOTNULL AND
2258 "event"."event" IN (
2259 'issue_state_changed',
2260 'initiative_created_in_new_issue',
2261 'initiative_created_in_existing_issue',
2262 'initiative_revoked' ) ) )
2263 AND "ignored_member"."member_id" ISNULL
2264 AND "ignored_initiative"."member_id" ISNULL;
2266 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"';
2269 CREATE TYPE "timeline_event" AS ENUM (
2270 'issue_created',
2271 'issue_canceled',
2272 'issue_accepted',
2273 'issue_half_frozen',
2274 'issue_finished_without_voting',
2275 'issue_voting_started',
2276 'issue_finished_after_voting',
2277 'initiative_created',
2278 'initiative_revoked',
2279 'draft_created',
2280 'suggestion_created');
2282 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)';
2285 CREATE VIEW "timeline_issue" AS
2286 SELECT
2287 "created" AS "occurrence",
2288 'issue_created'::"timeline_event" AS "event",
2289 "id" AS "issue_id"
2290 FROM "issue"
2291 UNION ALL
2292 SELECT
2293 "closed" AS "occurrence",
2294 'issue_canceled'::"timeline_event" AS "event",
2295 "id" AS "issue_id"
2296 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
2297 UNION ALL
2298 SELECT
2299 "accepted" AS "occurrence",
2300 'issue_accepted'::"timeline_event" AS "event",
2301 "id" AS "issue_id"
2302 FROM "issue" WHERE "accepted" NOTNULL
2303 UNION ALL
2304 SELECT
2305 "half_frozen" AS "occurrence",
2306 'issue_half_frozen'::"timeline_event" AS "event",
2307 "id" AS "issue_id"
2308 FROM "issue" WHERE "half_frozen" NOTNULL
2309 UNION ALL
2310 SELECT
2311 "fully_frozen" AS "occurrence",
2312 'issue_voting_started'::"timeline_event" AS "event",
2313 "id" AS "issue_id"
2314 FROM "issue"
2315 WHERE "fully_frozen" NOTNULL
2316 AND ("closed" ISNULL OR "closed" != "fully_frozen")
2317 UNION ALL
2318 SELECT
2319 "closed" AS "occurrence",
2320 CASE WHEN "fully_frozen" = "closed" THEN
2321 'issue_finished_without_voting'::"timeline_event"
2322 ELSE
2323 'issue_finished_after_voting'::"timeline_event"
2324 END AS "event",
2325 "id" AS "issue_id"
2326 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
2328 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)';
2331 CREATE VIEW "timeline_initiative" AS
2332 SELECT
2333 "created" AS "occurrence",
2334 'initiative_created'::"timeline_event" AS "event",
2335 "id" AS "initiative_id"
2336 FROM "initiative"
2337 UNION ALL
2338 SELECT
2339 "revoked" AS "occurrence",
2340 'initiative_revoked'::"timeline_event" AS "event",
2341 "id" AS "initiative_id"
2342 FROM "initiative" WHERE "revoked" NOTNULL;
2344 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)';
2347 CREATE VIEW "timeline_draft" AS
2348 SELECT
2349 "created" AS "occurrence",
2350 'draft_created'::"timeline_event" AS "event",
2351 "id" AS "draft_id"
2352 FROM "draft";
2354 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)';
2357 CREATE VIEW "timeline_suggestion" AS
2358 SELECT
2359 "created" AS "occurrence",
2360 'suggestion_created'::"timeline_event" AS "event",
2361 "id" AS "suggestion_id"
2362 FROM "suggestion";
2364 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)';
2367 CREATE VIEW "timeline" AS
2368 SELECT
2369 "occurrence",
2370 "event",
2371 "issue_id",
2372 NULL AS "initiative_id",
2373 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
2374 NULL::INT8 AS "suggestion_id"
2375 FROM "timeline_issue"
2376 UNION ALL
2377 SELECT
2378 "occurrence",
2379 "event",
2380 NULL AS "issue_id",
2381 "initiative_id",
2382 NULL AS "draft_id",
2383 NULL AS "suggestion_id"
2384 FROM "timeline_initiative"
2385 UNION ALL
2386 SELECT
2387 "occurrence",
2388 "event",
2389 NULL AS "issue_id",
2390 NULL AS "initiative_id",
2391 "draft_id",
2392 NULL AS "suggestion_id"
2393 FROM "timeline_draft"
2394 UNION ALL
2395 SELECT
2396 "occurrence",
2397 "event",
2398 NULL AS "issue_id",
2399 NULL AS "initiative_id",
2400 NULL AS "draft_id",
2401 "suggestion_id"
2402 FROM "timeline_suggestion";
2404 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)';
2408 ------------------------------------------------------
2409 -- Row set returning function for delegation chains --
2410 ------------------------------------------------------
2413 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
2414 ('first', 'intermediate', 'last', 'repetition');
2416 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
2419 CREATE TYPE "delegation_chain_row" AS (
2420 "index" INT4,
2421 "member_id" INT4,
2422 "member_valid" BOOLEAN,
2423 "participation" BOOLEAN,
2424 "overridden" BOOLEAN,
2425 "scope_in" "delegation_scope",
2426 "scope_out" "delegation_scope",
2427 "disabled_out" BOOLEAN,
2428 "loop" "delegation_chain_loop_tag" );
2430 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';
2432 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
2433 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';
2434 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
2435 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
2436 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
2437 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
2438 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
2441 CREATE FUNCTION "delegation_chain_for_closed_issue"
2442 ( "member_id_p" "member"."id"%TYPE,
2443 "issue_id_p" "issue"."id"%TYPE )
2444 RETURNS SETOF "delegation_chain_row"
2445 LANGUAGE 'plpgsql' STABLE AS $$
2446 DECLARE
2447 "output_row" "delegation_chain_row";
2448 "direct_voter_row" "direct_voter"%ROWTYPE;
2449 "delegating_voter_row" "delegating_voter"%ROWTYPE;
2450 BEGIN
2451 "output_row"."index" := 0;
2452 "output_row"."member_id" := "member_id_p";
2453 "output_row"."member_valid" := TRUE;
2454 "output_row"."participation" := FALSE;
2455 "output_row"."overridden" := FALSE;
2456 "output_row"."disabled_out" := FALSE;
2457 LOOP
2458 SELECT INTO "direct_voter_row" * FROM "direct_voter"
2459 WHERE "issue_id" = "issue_id_p"
2460 AND "member_id" = "output_row"."member_id";
2461 IF "direct_voter_row"."member_id" NOTNULL THEN
2462 "output_row"."participation" := TRUE;
2463 "output_row"."scope_out" := NULL;
2464 "output_row"."disabled_out" := NULL;
2465 RETURN NEXT "output_row";
2466 RETURN;
2467 END IF;
2468 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"
2469 WHERE "issue_id" = "issue_id_p"
2470 AND "member_id" = "output_row"."member_id";
2471 IF "delegating_voter_row"."member_id" ISNULL THEN
2472 RETURN;
2473 END IF;
2474 "output_row"."scope_out" := "delegating_voter_row"."scope";
2475 RETURN NEXT "output_row";
2476 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];
2477 "output_row"."scope_in" := "output_row"."scope_out";
2478 END LOOP;
2479 END;
2480 $$;
2482 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"
2483 ( "member"."id"%TYPE,
2484 "member"."id"%TYPE )
2485 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';
2488 CREATE FUNCTION "delegation_chain"
2489 ( "member_id_p" "member"."id"%TYPE,
2490 "unit_id_p" "unit"."id"%TYPE,
2491 "area_id_p" "area"."id"%TYPE,
2492 "issue_id_p" "issue"."id"%TYPE,
2493 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2494 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2495 RETURNS SETOF "delegation_chain_row"
2496 LANGUAGE 'plpgsql' STABLE AS $$
2497 DECLARE
2498 "scope_v" "delegation_scope";
2499 "unit_id_v" "unit"."id"%TYPE;
2500 "area_id_v" "area"."id"%TYPE;
2501 "issue_row" "issue"%ROWTYPE;
2502 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2503 "loop_member_id_v" "member"."id"%TYPE;
2504 "output_row" "delegation_chain_row";
2505 "output_rows" "delegation_chain_row"[];
2506 "simulate_v" BOOLEAN;
2507 "simulate_here_v" BOOLEAN;
2508 "delegation_row" "delegation"%ROWTYPE;
2509 "row_count" INT4;
2510 "i" INT4;
2511 "loop_v" BOOLEAN;
2512 BEGIN
2513 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
2514 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
2515 END IF;
2516 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
2517 "simulate_v" := TRUE;
2518 ELSE
2519 "simulate_v" := FALSE;
2520 END IF;
2521 IF
2522 "unit_id_p" NOTNULL AND
2523 "area_id_p" ISNULL AND
2524 "issue_id_p" ISNULL
2525 THEN
2526 "scope_v" := 'unit';
2527 "unit_id_v" := "unit_id_p";
2528 ELSIF
2529 "unit_id_p" ISNULL AND
2530 "area_id_p" NOTNULL AND
2531 "issue_id_p" ISNULL
2532 THEN
2533 "scope_v" := 'area';
2534 "area_id_v" := "area_id_p";
2535 SELECT "unit_id" INTO "unit_id_v"
2536 FROM "area" WHERE "id" = "area_id_v";
2537 ELSIF
2538 "unit_id_p" ISNULL AND
2539 "area_id_p" ISNULL AND
2540 "issue_id_p" NOTNULL
2541 THEN
2542 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
2543 IF "issue_row"."id" ISNULL THEN
2544 RETURN;
2545 END IF;
2546 IF "issue_row"."closed" NOTNULL THEN
2547 IF "simulate_v" THEN
2548 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
2549 END IF;
2550 FOR "output_row" IN
2551 SELECT * FROM
2552 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
2553 LOOP
2554 RETURN NEXT "output_row";
2555 END LOOP;
2556 RETURN;
2557 END IF;
2558 "scope_v" := 'issue';
2559 SELECT "area_id" INTO "area_id_v"
2560 FROM "issue" WHERE "id" = "issue_id_p";
2561 SELECT "unit_id" INTO "unit_id_v"
2562 FROM "area" WHERE "id" = "area_id_v";
2563 ELSE
2564 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2565 END IF;
2566 "visited_member_ids" := '{}';
2567 "loop_member_id_v" := NULL;
2568 "output_rows" := '{}';
2569 "output_row"."index" := 0;
2570 "output_row"."member_id" := "member_id_p";
2571 "output_row"."member_valid" := TRUE;
2572 "output_row"."participation" := FALSE;
2573 "output_row"."overridden" := FALSE;
2574 "output_row"."disabled_out" := FALSE;
2575 "output_row"."scope_out" := NULL;
2576 LOOP
2577 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2578 "loop_member_id_v" := "output_row"."member_id";
2579 ELSE
2580 "visited_member_ids" :=
2581 "visited_member_ids" || "output_row"."member_id";
2582 END IF;
2583 IF "output_row"."participation" ISNULL THEN
2584 "output_row"."overridden" := NULL;
2585 ELSIF "output_row"."participation" THEN
2586 "output_row"."overridden" := TRUE;
2587 END IF;
2588 "output_row"."scope_in" := "output_row"."scope_out";
2589 "output_row"."member_valid" := EXISTS (
2590 SELECT NULL FROM "member" JOIN "privilege"
2591 ON "privilege"."member_id" = "member"."id"
2592 AND "privilege"."unit_id" = "unit_id_v"
2593 WHERE "id" = "output_row"."member_id"
2594 AND "member"."active" AND "privilege"."voting_right"
2595 );
2596 "simulate_here_v" := (
2597 "simulate_v" AND
2598 "output_row"."member_id" = "member_id_p"
2599 );
2600 "delegation_row" := ROW(NULL);
2601 IF "output_row"."member_valid" OR "simulate_here_v" THEN
2602 IF "scope_v" = 'unit' THEN
2603 IF NOT "simulate_here_v" THEN
2604 SELECT * INTO "delegation_row" FROM "delegation"
2605 WHERE "truster_id" = "output_row"."member_id"
2606 AND "unit_id" = "unit_id_v";
2607 END IF;
2608 ELSIF "scope_v" = 'area' THEN
2609 "output_row"."participation" := EXISTS (
2610 SELECT NULL FROM "membership"
2611 WHERE "area_id" = "area_id_p"
2612 AND "member_id" = "output_row"."member_id"
2613 );
2614 IF "simulate_here_v" THEN
2615 IF "simulate_trustee_id_p" ISNULL THEN
2616 SELECT * INTO "delegation_row" FROM "delegation"
2617 WHERE "truster_id" = "output_row"."member_id"
2618 AND "unit_id" = "unit_id_v";
2619 END IF;
2620 ELSE
2621 SELECT * INTO "delegation_row" FROM "delegation"
2622 WHERE "truster_id" = "output_row"."member_id"
2623 AND (
2624 "unit_id" = "unit_id_v" OR
2625 "area_id" = "area_id_v"
2626 )
2627 ORDER BY "scope" DESC;
2628 END IF;
2629 ELSIF "scope_v" = 'issue' THEN
2630 IF "issue_row"."fully_frozen" ISNULL THEN
2631 "output_row"."participation" := EXISTS (
2632 SELECT NULL FROM "interest"
2633 WHERE "issue_id" = "issue_id_p"
2634 AND "member_id" = "output_row"."member_id"
2635 );
2636 ELSE
2637 IF "output_row"."member_id" = "member_id_p" THEN
2638 "output_row"."participation" := EXISTS (
2639 SELECT NULL FROM "direct_voter"
2640 WHERE "issue_id" = "issue_id_p"
2641 AND "member_id" = "output_row"."member_id"
2642 );
2643 ELSE
2644 "output_row"."participation" := NULL;
2645 END IF;
2646 END IF;
2647 IF "simulate_here_v" THEN
2648 IF "simulate_trustee_id_p" ISNULL THEN
2649 SELECT * INTO "delegation_row" FROM "delegation"
2650 WHERE "truster_id" = "output_row"."member_id"
2651 AND (
2652 "unit_id" = "unit_id_v" OR
2653 "area_id" = "area_id_v"
2654 )
2655 ORDER BY "scope" DESC;
2656 END IF;
2657 ELSE
2658 SELECT * INTO "delegation_row" FROM "delegation"
2659 WHERE "truster_id" = "output_row"."member_id"
2660 AND (
2661 "unit_id" = "unit_id_v" OR
2662 "area_id" = "area_id_v" OR
2663 "issue_id" = "issue_id_p"
2664 )
2665 ORDER BY "scope" DESC;
2666 END IF;
2667 END IF;
2668 ELSE
2669 "output_row"."participation" := FALSE;
2670 END IF;
2671 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
2672 "output_row"."scope_out" := "scope_v";
2673 "output_rows" := "output_rows" || "output_row";
2674 "output_row"."member_id" := "simulate_trustee_id_p";
2675 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2676 "output_row"."scope_out" := "delegation_row"."scope";
2677 "output_rows" := "output_rows" || "output_row";
2678 "output_row"."member_id" := "delegation_row"."trustee_id";
2679 ELSIF "delegation_row"."scope" NOTNULL THEN
2680 "output_row"."scope_out" := "delegation_row"."scope";
2681 "output_row"."disabled_out" := TRUE;
2682 "output_rows" := "output_rows" || "output_row";
2683 EXIT;
2684 ELSE
2685 "output_row"."scope_out" := NULL;
2686 "output_rows" := "output_rows" || "output_row";
2687 EXIT;
2688 END IF;
2689 EXIT WHEN "loop_member_id_v" NOTNULL;
2690 "output_row"."index" := "output_row"."index" + 1;
2691 END LOOP;
2692 "row_count" := array_upper("output_rows", 1);
2693 "i" := 1;
2694 "loop_v" := FALSE;
2695 LOOP
2696 "output_row" := "output_rows"["i"];
2697 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2698 IF "loop_v" THEN
2699 IF "i" + 1 = "row_count" THEN
2700 "output_row"."loop" := 'last';
2701 ELSIF "i" = "row_count" THEN
2702 "output_row"."loop" := 'repetition';
2703 ELSE
2704 "output_row"."loop" := 'intermediate';
2705 END IF;
2706 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2707 "output_row"."loop" := 'first';
2708 "loop_v" := TRUE;
2709 END IF;
2710 IF "scope_v" = 'unit' THEN
2711 "output_row"."participation" := NULL;
2712 END IF;
2713 RETURN NEXT "output_row";
2714 "i" := "i" + 1;
2715 END LOOP;
2716 RETURN;
2717 END;
2718 $$;
2720 COMMENT ON FUNCTION "delegation_chain"
2721 ( "member"."id"%TYPE,
2722 "unit"."id"%TYPE,
2723 "area"."id"%TYPE,
2724 "issue"."id"%TYPE,
2725 "member"."id"%TYPE,
2726 BOOLEAN )
2727 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
2731 ---------------------------------------------------------
2732 -- Single row returning function for delegation chains --
2733 ---------------------------------------------------------
2736 CREATE TYPE "delegation_info_loop_type" AS ENUM
2737 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');
2739 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''';
2742 CREATE TYPE "delegation_info_type" AS (
2743 "own_participation" BOOLEAN,
2744 "own_delegation_scope" "delegation_scope",
2745 "first_trustee_id" INT4,
2746 "first_trustee_participation" BOOLEAN,
2747 "first_trustee_ellipsis" BOOLEAN,
2748 "other_trustee_id" INT4,
2749 "other_trustee_participation" BOOLEAN,
2750 "other_trustee_ellipsis" BOOLEAN,
2751 "delegation_loop" "delegation_info_loop_type",
2752 "participating_member_id" INT4 );
2754 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';
2756 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';
2757 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';
2758 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';
2759 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
2760 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';
2761 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';
2762 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)';
2763 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';
2764 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';
2765 COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain';
2768 CREATE FUNCTION "delegation_info"
2769 ( "member_id_p" "member"."id"%TYPE,
2770 "unit_id_p" "unit"."id"%TYPE,
2771 "area_id_p" "area"."id"%TYPE,
2772 "issue_id_p" "issue"."id"%TYPE,
2773 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2774 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2775 RETURNS "delegation_info_type"
2776 LANGUAGE 'plpgsql' STABLE AS $$
2777 DECLARE
2778 "current_row" "delegation_chain_row";
2779 "result" "delegation_info_type";
2780 BEGIN
2781 "result"."own_participation" := FALSE;
2782 FOR "current_row" IN
2783 SELECT * FROM "delegation_chain"(
2784 "member_id_p",
2785 "unit_id_p", "area_id_p", "issue_id_p",
2786 "simulate_trustee_id_p", "simulate_default_p")
2787 LOOP
2788 IF
2789 "result"."participating_member_id" ISNULL AND
2790 "current_row"."participation"
2791 THEN
2792 "result"."participating_member_id" := "current_row"."member_id";
2793 END IF;
2794 IF "current_row"."member_id" = "member_id_p" THEN
2795 "result"."own_participation" := "current_row"."participation";
2796 "result"."own_delegation_scope" := "current_row"."scope_out";
2797 IF "current_row"."loop" = 'first' THEN
2798 "result"."delegation_loop" := 'own';
2799 END IF;
2800 ELSIF
2801 "current_row"."member_valid" AND
2802 ( "current_row"."loop" ISNULL OR
2803 "current_row"."loop" != 'repetition' )
2804 THEN
2805 IF "result"."first_trustee_id" ISNULL THEN
2806 "result"."first_trustee_id" := "current_row"."member_id";
2807 "result"."first_trustee_participation" := "current_row"."participation";
2808 "result"."first_trustee_ellipsis" := FALSE;
2809 IF "current_row"."loop" = 'first' THEN
2810 "result"."delegation_loop" := 'first';
2811 END IF;
2812 ELSIF "result"."other_trustee_id" ISNULL THEN
2813 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
2814 "result"."other_trustee_id" := "current_row"."member_id";
2815 "result"."other_trustee_participation" := TRUE;
2816 "result"."other_trustee_ellipsis" := FALSE;
2817 IF "current_row"."loop" = 'first' THEN
2818 "result"."delegation_loop" := 'other';
2819 END IF;
2820 ELSE
2821 "result"."first_trustee_ellipsis" := TRUE;
2822 IF "current_row"."loop" = 'first' THEN
2823 "result"."delegation_loop" := 'first_ellipsis';
2824 END IF;
2825 END IF;
2826 ELSE
2827 "result"."other_trustee_ellipsis" := TRUE;
2828 IF "current_row"."loop" = 'first' THEN
2829 "result"."delegation_loop" := 'other_ellipsis';
2830 END IF;
2831 END IF;
2832 END IF;
2833 END LOOP;
2834 RETURN "result";
2835 END;
2836 $$;
2838 COMMENT ON FUNCTION "delegation_info"
2839 ( "member"."id"%TYPE,
2840 "unit"."id"%TYPE,
2841 "area"."id"%TYPE,
2842 "issue"."id"%TYPE,
2843 "member"."id"%TYPE,
2844 BOOLEAN )
2845 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
2849 ------------------------------
2850 -- Comparison by vote count --
2851 ------------------------------
2853 CREATE FUNCTION "vote_ratio"
2854 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
2855 "negative_votes_p" "initiative"."negative_votes"%TYPE )
2856 RETURNS FLOAT8
2857 LANGUAGE 'plpgsql' STABLE AS $$
2858 BEGIN
2859 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
2860 RETURN
2861 "positive_votes_p"::FLOAT8 /
2862 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
2863 ELSIF "positive_votes_p" > 0 THEN
2864 RETURN "positive_votes_p";
2865 ELSIF "negative_votes_p" > 0 THEN
2866 RETURN 1 - "negative_votes_p";
2867 ELSE
2868 RETURN 0.5;
2869 END IF;
2870 END;
2871 $$;
2873 COMMENT ON FUNCTION "vote_ratio"
2874 ( "initiative"."positive_votes"%TYPE,
2875 "initiative"."negative_votes"%TYPE )
2876 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.';
2880 ------------------------------------------------
2881 -- Locking for snapshots and voting procedure --
2882 ------------------------------------------------
2885 CREATE FUNCTION "share_row_lock_issue_trigger"()
2886 RETURNS TRIGGER
2887 LANGUAGE 'plpgsql' VOLATILE AS $$
2888 BEGIN
2889 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2890 PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;
2891 END IF;
2892 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2893 PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;
2894 RETURN NEW;
2895 ELSE
2896 RETURN OLD;
2897 END IF;
2898 END;
2899 $$;
2901 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';
2904 CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()
2905 RETURNS TRIGGER
2906 LANGUAGE 'plpgsql' VOLATILE AS $$
2907 BEGIN
2908 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2909 PERFORM NULL FROM "issue"
2910 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2911 WHERE "initiative"."id" = OLD."initiative_id"
2912 FOR SHARE OF "issue";
2913 END IF;
2914 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2915 PERFORM NULL FROM "issue"
2916 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2917 WHERE "initiative"."id" = NEW."initiative_id"
2918 FOR SHARE OF "issue";
2919 RETURN NEW;
2920 ELSE
2921 RETURN OLD;
2922 END IF;
2923 END;
2924 $$;
2926 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';
2929 CREATE TRIGGER "share_row_lock_issue"
2930 BEFORE INSERT OR UPDATE OR DELETE ON "initiative"
2931 FOR EACH ROW EXECUTE PROCEDURE
2932 "share_row_lock_issue_trigger"();
2934 CREATE TRIGGER "share_row_lock_issue"
2935 BEFORE INSERT OR UPDATE OR DELETE ON "interest"
2936 FOR EACH ROW EXECUTE PROCEDURE
2937 "share_row_lock_issue_trigger"();
2939 CREATE TRIGGER "share_row_lock_issue"
2940 BEFORE INSERT OR UPDATE OR DELETE ON "supporter"
2941 FOR EACH ROW EXECUTE PROCEDURE
2942 "share_row_lock_issue_trigger"();
2944 CREATE TRIGGER "share_row_lock_issue_via_initiative"
2945 BEFORE INSERT OR UPDATE OR DELETE ON "opinion"
2946 FOR EACH ROW EXECUTE PROCEDURE
2947 "share_row_lock_issue_via_initiative_trigger"();
2949 CREATE TRIGGER "share_row_lock_issue"
2950 BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter"
2951 FOR EACH ROW EXECUTE PROCEDURE
2952 "share_row_lock_issue_trigger"();
2954 CREATE TRIGGER "share_row_lock_issue"
2955 BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter"
2956 FOR EACH ROW EXECUTE PROCEDURE
2957 "share_row_lock_issue_trigger"();
2959 CREATE TRIGGER "share_row_lock_issue"
2960 BEFORE INSERT OR UPDATE OR DELETE ON "vote"
2961 FOR EACH ROW EXECUTE PROCEDURE
2962 "share_row_lock_issue_trigger"();
2964 COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function';
2965 COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function';
2966 COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function';
2967 COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function';
2968 COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function';
2969 COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function';
2970 COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function';
2973 CREATE FUNCTION "lock_issue"
2974 ( "issue_id_p" "issue"."id"%TYPE )
2975 RETURNS VOID
2976 LANGUAGE 'plpgsql' VOLATILE AS $$
2977 BEGIN
2978 LOCK TABLE "member" IN SHARE MODE;
2979 LOCK TABLE "privilege" IN SHARE MODE;
2980 LOCK TABLE "membership" IN SHARE MODE;
2981 LOCK TABLE "policy" IN SHARE MODE;
2982 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2983 -- NOTE: The row-level exclusive lock in combination with the
2984 -- share_row_lock_issue(_via_initiative)_trigger functions (which
2985 -- acquire a row-level share lock on the issue) ensure that no data
2986 -- is changed, which could affect calculation of snapshots or
2987 -- counting of votes. Table "delegation" must be table-level-locked,
2988 -- as it also contains issue- and global-scope delegations.
2989 LOCK TABLE "delegation" IN SHARE MODE;
2990 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
2991 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
2992 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
2993 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
2994 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
2995 RETURN;
2996 END;
2997 $$;
2999 COMMENT ON FUNCTION "lock_issue"
3000 ( "issue"."id"%TYPE )
3001 IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';
3005 ------------------------------------------------------------------------
3006 -- Regular tasks, except calculcation of snapshots and voting results --
3007 ------------------------------------------------------------------------
3009 CREATE FUNCTION "check_activity"()
3010 RETURNS VOID
3011 LANGUAGE 'plpgsql' VOLATILE AS $$
3012 DECLARE
3013 "system_setting_row" "system_setting"%ROWTYPE;
3014 BEGIN
3015 SELECT * INTO "system_setting_row" FROM "system_setting";
3016 LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
3017 IF "system_setting_row"."member_ttl" NOTNULL THEN
3018 UPDATE "member" SET "active" = FALSE
3019 WHERE "active" = TRUE
3020 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
3021 END IF;
3022 RETURN;
3023 END;
3024 $$;
3026 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
3029 CREATE FUNCTION "calculate_member_counts"()
3030 RETURNS VOID
3031 LANGUAGE 'plpgsql' VOLATILE AS $$
3032 BEGIN
3033 LOCK TABLE "member" IN SHARE MODE;
3034 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
3035 LOCK TABLE "unit" IN EXCLUSIVE MODE;
3036 LOCK TABLE "area" IN EXCLUSIVE MODE;
3037 LOCK TABLE "privilege" IN SHARE MODE;
3038 LOCK TABLE "membership" IN SHARE MODE;
3039 DELETE FROM "member_count";
3040 INSERT INTO "member_count" ("total_count")
3041 SELECT "total_count" FROM "member_count_view";
3042 UPDATE "unit" SET "member_count" = "view"."member_count"
3043 FROM "unit_member_count" AS "view"
3044 WHERE "view"."unit_id" = "unit"."id";
3045 UPDATE "area" SET
3046 "direct_member_count" = "view"."direct_member_count",
3047 "member_weight" = "view"."member_weight"
3048 FROM "area_member_count" AS "view"
3049 WHERE "view"."area_id" = "area"."id";
3050 RETURN;
3051 END;
3052 $$;
3054 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"';
3058 ------------------------------
3059 -- Calculation of snapshots --
3060 ------------------------------
3062 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
3063 ( "issue_id_p" "issue"."id"%TYPE,
3064 "member_id_p" "member"."id"%TYPE,
3065 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3066 RETURNS "direct_population_snapshot"."weight"%TYPE
3067 LANGUAGE 'plpgsql' VOLATILE AS $$
3068 DECLARE
3069 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3070 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
3071 "weight_v" INT4;
3072 "sub_weight_v" INT4;
3073 BEGIN
3074 "weight_v" := 0;
3075 FOR "issue_delegation_row" IN
3076 SELECT * FROM "issue_delegation"
3077 WHERE "trustee_id" = "member_id_p"
3078 AND "issue_id" = "issue_id_p"
3079 LOOP
3080 IF NOT EXISTS (
3081 SELECT NULL FROM "direct_population_snapshot"
3082 WHERE "issue_id" = "issue_id_p"
3083 AND "event" = 'periodic'
3084 AND "member_id" = "issue_delegation_row"."truster_id"
3085 ) AND NOT EXISTS (
3086 SELECT NULL FROM "delegating_population_snapshot"
3087 WHERE "issue_id" = "issue_id_p"
3088 AND "event" = 'periodic'
3089 AND "member_id" = "issue_delegation_row"."truster_id"
3090 ) THEN
3091 "delegate_member_ids_v" :=
3092 "member_id_p" || "delegate_member_ids_p";
3093 INSERT INTO "delegating_population_snapshot" (
3094 "issue_id",
3095 "event",
3096 "member_id",
3097 "scope",
3098 "delegate_member_ids"
3099 ) VALUES (
3100 "issue_id_p",
3101 'periodic',
3102 "issue_delegation_row"."truster_id",
3103 "issue_delegation_row"."scope",
3104 "delegate_member_ids_v"
3105 );
3106 "sub_weight_v" := 1 +
3107 "weight_of_added_delegations_for_population_snapshot"(
3108 "issue_id_p",
3109 "issue_delegation_row"."truster_id",
3110 "delegate_member_ids_v"
3111 );
3112 UPDATE "delegating_population_snapshot"
3113 SET "weight" = "sub_weight_v"
3114 WHERE "issue_id" = "issue_id_p"
3115 AND "event" = 'periodic'
3116 AND "member_id" = "issue_delegation_row"."truster_id";
3117 "weight_v" := "weight_v" + "sub_weight_v";
3118 END IF;
3119 END LOOP;
3120 RETURN "weight_v";
3121 END;
3122 $$;
3124 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
3125 ( "issue"."id"%TYPE,
3126 "member"."id"%TYPE,
3127 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3128 IS 'Helper function for "create_population_snapshot" function';
3131 CREATE FUNCTION "create_population_snapshot"
3132 ( "issue_id_p" "issue"."id"%TYPE )
3133 RETURNS VOID
3134 LANGUAGE 'plpgsql' VOLATILE AS $$
3135 DECLARE
3136 "member_id_v" "member"."id"%TYPE;
3137 BEGIN
3138 DELETE FROM "direct_population_snapshot"
3139 WHERE "issue_id" = "issue_id_p"
3140 AND "event" = 'periodic';
3141 DELETE FROM "delegating_population_snapshot"
3142 WHERE "issue_id" = "issue_id_p"
3143 AND "event" = 'periodic';
3144 INSERT INTO "direct_population_snapshot"
3145 ("issue_id", "event", "member_id")
3146 SELECT
3147 "issue_id_p" AS "issue_id",
3148 'periodic'::"snapshot_event" AS "event",
3149 "member"."id" AS "member_id"
3150 FROM "issue"
3151 JOIN "area" ON "issue"."area_id" = "area"."id"
3152 JOIN "membership" ON "area"."id" = "membership"."area_id"
3153 JOIN "member" ON "membership"."member_id" = "member"."id"
3154 JOIN "privilege"
3155 ON "privilege"."unit_id" = "area"."unit_id"
3156 AND "privilege"."member_id" = "member"."id"
3157 WHERE "issue"."id" = "issue_id_p"
3158 AND "member"."active" AND "privilege"."voting_right"
3159 UNION
3160 SELECT
3161 "issue_id_p" AS "issue_id",
3162 'periodic'::"snapshot_event" AS "event",
3163 "member"."id" AS "member_id"
3164 FROM "issue"
3165 JOIN "area" ON "issue"."area_id" = "area"."id"
3166 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3167 JOIN "member" ON "interest"."member_id" = "member"."id"
3168 JOIN "privilege"
3169 ON "privilege"."unit_id" = "area"."unit_id"
3170 AND "privilege"."member_id" = "member"."id"
3171 WHERE "issue"."id" = "issue_id_p"
3172 AND "member"."active" AND "privilege"."voting_right";
3173 FOR "member_id_v" IN
3174 SELECT "member_id" FROM "direct_population_snapshot"
3175 WHERE "issue_id" = "issue_id_p"
3176 AND "event" = 'periodic'
3177 LOOP
3178 UPDATE "direct_population_snapshot" SET
3179 "weight" = 1 +
3180 "weight_of_added_delegations_for_population_snapshot"(
3181 "issue_id_p",
3182 "member_id_v",
3183 '{}'
3184 )
3185 WHERE "issue_id" = "issue_id_p"
3186 AND "event" = 'periodic'
3187 AND "member_id" = "member_id_v";
3188 END LOOP;
3189 RETURN;
3190 END;
3191 $$;
3193 COMMENT ON FUNCTION "create_population_snapshot"
3194 ( "issue"."id"%TYPE )
3195 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.';
3198 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3199 ( "issue_id_p" "issue"."id"%TYPE,
3200 "member_id_p" "member"."id"%TYPE,
3201 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3202 RETURNS "direct_interest_snapshot"."weight"%TYPE
3203 LANGUAGE 'plpgsql' VOLATILE AS $$
3204 DECLARE
3205 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3206 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
3207 "weight_v" INT4;
3208 "sub_weight_v" INT4;
3209 BEGIN
3210 "weight_v" := 0;
3211 FOR "issue_delegation_row" IN
3212 SELECT * FROM "issue_delegation"
3213 WHERE "trustee_id" = "member_id_p"
3214 AND "issue_id" = "issue_id_p"
3215 LOOP
3216 IF NOT EXISTS (
3217 SELECT NULL FROM "direct_interest_snapshot"
3218 WHERE "issue_id" = "issue_id_p"
3219 AND "event" = 'periodic'
3220 AND "member_id" = "issue_delegation_row"."truster_id"
3221 ) AND NOT EXISTS (
3222 SELECT NULL FROM "delegating_interest_snapshot"
3223 WHERE "issue_id" = "issue_id_p"
3224 AND "event" = 'periodic'
3225 AND "member_id" = "issue_delegation_row"."truster_id"
3226 ) THEN
3227 "delegate_member_ids_v" :=
3228 "member_id_p" || "delegate_member_ids_p";
3229 INSERT INTO "delegating_interest_snapshot" (
3230 "issue_id",
3231 "event",
3232 "member_id",
3233 "scope",
3234 "delegate_member_ids"
3235 ) VALUES (
3236 "issue_id_p",
3237 'periodic',
3238 "issue_delegation_row"."truster_id",
3239 "issue_delegation_row"."scope",
3240 "delegate_member_ids_v"
3241 );
3242 "sub_weight_v" := 1 +
3243 "weight_of_added_delegations_for_interest_snapshot"(
3244 "issue_id_p",
3245 "issue_delegation_row"."truster_id",
3246 "delegate_member_ids_v"
3247 );
3248 UPDATE "delegating_interest_snapshot"
3249 SET "weight" = "sub_weight_v"
3250 WHERE "issue_id" = "issue_id_p"
3251 AND "event" = 'periodic'
3252 AND "member_id" = "issue_delegation_row"."truster_id";
3253 "weight_v" := "weight_v" + "sub_weight_v";
3254 END IF;
3255 END LOOP;
3256 RETURN "weight_v";
3257 END;
3258 $$;
3260 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3261 ( "issue"."id"%TYPE,
3262 "member"."id"%TYPE,
3263 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3264 IS 'Helper function for "create_interest_snapshot" function';
3267 CREATE FUNCTION "create_interest_snapshot"
3268 ( "issue_id_p" "issue"."id"%TYPE )
3269 RETURNS VOID
3270 LANGUAGE 'plpgsql' VOLATILE AS $$
3271 DECLARE
3272 "member_id_v" "member"."id"%TYPE;
3273 BEGIN
3274 DELETE FROM "direct_interest_snapshot"
3275 WHERE "issue_id" = "issue_id_p"
3276 AND "event" = 'periodic';
3277 DELETE FROM "delegating_interest_snapshot"
3278 WHERE "issue_id" = "issue_id_p"
3279 AND "event" = 'periodic';
3280 DELETE FROM "direct_supporter_snapshot"
3281 WHERE "issue_id" = "issue_id_p"
3282 AND "event" = 'periodic';
3283 INSERT INTO "direct_interest_snapshot"
3284 ("issue_id", "event", "member_id")
3285 SELECT
3286 "issue_id_p" AS "issue_id",
3287 'periodic' AS "event",
3288 "member"."id" AS "member_id"
3289 FROM "issue"
3290 JOIN "area" ON "issue"."area_id" = "area"."id"
3291 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3292 JOIN "member" ON "interest"."member_id" = "member"."id"
3293 JOIN "privilege"
3294 ON "privilege"."unit_id" = "area"."unit_id"
3295 AND "privilege"."member_id" = "member"."id"
3296 WHERE "issue"."id" = "issue_id_p"
3297 AND "member"."active" AND "privilege"."voting_right";
3298 FOR "member_id_v" IN
3299 SELECT "member_id" FROM "direct_interest_snapshot"
3300 WHERE "issue_id" = "issue_id_p"
3301 AND "event" = 'periodic'
3302 LOOP
3303 UPDATE "direct_interest_snapshot" SET
3304 "weight" = 1 +
3305 "weight_of_added_delegations_for_interest_snapshot"(
3306 "issue_id_p",
3307 "member_id_v",
3308 '{}'
3309 )
3310 WHERE "issue_id" = "issue_id_p"
3311 AND "event" = 'periodic'
3312 AND "member_id" = "member_id_v";
3313 END LOOP;
3314 INSERT INTO "direct_supporter_snapshot"
3315 ( "issue_id", "initiative_id", "event", "member_id",
3316 "draft_id", "informed", "satisfied" )
3317 SELECT
3318 "issue_id_p" AS "issue_id",
3319 "initiative"."id" AS "initiative_id",
3320 'periodic' AS "event",
3321 "supporter"."member_id" AS "member_id",
3322 "supporter"."draft_id" AS "draft_id",
3323 "supporter"."draft_id" = "current_draft"."id" AS "informed",
3324 NOT EXISTS (
3325 SELECT NULL FROM "critical_opinion"
3326 WHERE "initiative_id" = "initiative"."id"
3327 AND "member_id" = "supporter"."member_id"
3328 ) AS "satisfied"
3329 FROM "initiative"
3330 JOIN "supporter"
3331 ON "supporter"."initiative_id" = "initiative"."id"
3332 JOIN "current_draft"
3333 ON "initiative"."id" = "current_draft"."initiative_id"
3334 JOIN "direct_interest_snapshot"
3335 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
3336 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
3337 AND "event" = 'periodic'
3338 WHERE "initiative"."issue_id" = "issue_id_p";
3339 RETURN;
3340 END;
3341 $$;
3343 COMMENT ON FUNCTION "create_interest_snapshot"
3344 ( "issue"."id"%TYPE )
3345 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.';
3348 CREATE FUNCTION "create_snapshot"
3349 ( "issue_id_p" "issue"."id"%TYPE )
3350 RETURNS VOID
3351 LANGUAGE 'plpgsql' VOLATILE AS $$
3352 DECLARE
3353 "initiative_id_v" "initiative"."id"%TYPE;
3354 "suggestion_id_v" "suggestion"."id"%TYPE;
3355 BEGIN
3356 PERFORM "lock_issue"("issue_id_p");
3357 PERFORM "create_population_snapshot"("issue_id_p");
3358 PERFORM "create_interest_snapshot"("issue_id_p");
3359 UPDATE "issue" SET
3360 "snapshot" = now(),
3361 "latest_snapshot_event" = 'periodic',
3362 "population" = (
3363 SELECT coalesce(sum("weight"), 0)
3364 FROM "direct_population_snapshot"
3365 WHERE "issue_id" = "issue_id_p"
3366 AND "event" = 'periodic'
3367 )
3368 WHERE "id" = "issue_id_p";
3369 FOR "initiative_id_v" IN
3370 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
3371 LOOP
3372 UPDATE "initiative" SET
3373 "supporter_count" = (
3374 SELECT coalesce(sum("di"."weight"), 0)
3375 FROM "direct_interest_snapshot" AS "di"
3376 JOIN "direct_supporter_snapshot" AS "ds"
3377 ON "di"."member_id" = "ds"."member_id"
3378 WHERE "di"."issue_id" = "issue_id_p"
3379 AND "di"."event" = 'periodic'
3380 AND "ds"."initiative_id" = "initiative_id_v"
3381 AND "ds"."event" = 'periodic'
3382 ),
3383 "informed_supporter_count" = (
3384 SELECT coalesce(sum("di"."weight"), 0)
3385 FROM "direct_interest_snapshot" AS "di"
3386 JOIN "direct_supporter_snapshot" AS "ds"
3387 ON "di"."member_id" = "ds"."member_id"
3388 WHERE "di"."issue_id" = "issue_id_p"
3389 AND "di"."event" = 'periodic'
3390 AND "ds"."initiative_id" = "initiative_id_v"
3391 AND "ds"."event" = 'periodic'
3392 AND "ds"."informed"
3393 ),
3394 "satisfied_supporter_count" = (
3395 SELECT coalesce(sum("di"."weight"), 0)
3396 FROM "direct_interest_snapshot" AS "di"
3397 JOIN "direct_supporter_snapshot" AS "ds"
3398 ON "di"."member_id" = "ds"."member_id"
3399 WHERE "di"."issue_id" = "issue_id_p"
3400 AND "di"."event" = 'periodic'
3401 AND "ds"."initiative_id" = "initiative_id_v"
3402 AND "ds"."event" = 'periodic'
3403 AND "ds"."satisfied"
3404 ),
3405 "satisfied_informed_supporter_count" = (
3406 SELECT coalesce(sum("di"."weight"), 0)
3407 FROM "direct_interest_snapshot" AS "di"
3408 JOIN "direct_supporter_snapshot" AS "ds"
3409 ON "di"."member_id" = "ds"."member_id"
3410 WHERE "di"."issue_id" = "issue_id_p"
3411 AND "di"."event" = 'periodic'
3412 AND "ds"."initiative_id" = "initiative_id_v"
3413 AND "ds"."event" = 'periodic'
3414 AND "ds"."informed"
3415 AND "ds"."satisfied"
3416 )
3417 WHERE "id" = "initiative_id_v";
3418 FOR "suggestion_id_v" IN
3419 SELECT "id" FROM "suggestion"
3420 WHERE "initiative_id" = "initiative_id_v"
3421 LOOP
3422 UPDATE "suggestion" SET
3423 "minus2_unfulfilled_count" = (
3424 SELECT coalesce(sum("snapshot"."weight"), 0)
3425 FROM "issue" CROSS JOIN "opinion"
3426 JOIN "direct_interest_snapshot" AS "snapshot"
3427 ON "snapshot"."issue_id" = "issue"."id"
3428 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3429 AND "snapshot"."member_id" = "opinion"."member_id"
3430 WHERE "issue"."id" = "issue_id_p"
3431 AND "opinion"."suggestion_id" = "suggestion_id_v"
3432 AND "opinion"."degree" = -2
3433 AND "opinion"."fulfilled" = FALSE
3434 ),
3435 "minus2_fulfilled_count" = (
3436 SELECT coalesce(sum("snapshot"."weight"), 0)
3437 FROM "issue" CROSS JOIN "opinion"
3438 JOIN "direct_interest_snapshot" AS "snapshot"
3439 ON "snapshot"."issue_id" = "issue"."id"
3440 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3441 AND "snapshot"."member_id" = "opinion"."member_id"
3442 WHERE "issue"."id" = "issue_id_p"
3443 AND "opinion"."suggestion_id" = "suggestion_id_v"
3444 AND "opinion"."degree" = -2
3445 AND "opinion"."fulfilled" = TRUE
3446 ),
3447 "minus1_unfulfilled_count" = (
3448 SELECT coalesce(sum("snapshot"."weight"), 0)
3449 FROM "issue" CROSS JOIN "opinion"
3450 JOIN "direct_interest_snapshot" AS "snapshot"
3451 ON "snapshot"."issue_id" = "issue"."id"
3452 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3453 AND "snapshot"."member_id" = "opinion"."member_id"
3454 WHERE "issue"."id" = "issue_id_p"
3455 AND "opinion"."suggestion_id" = "suggestion_id_v"
3456 AND "opinion"."degree" = -1
3457 AND "opinion"."fulfilled" = FALSE
3458 ),
3459 "minus1_fulfilled_count" = (
3460 SELECT coalesce(sum("snapshot"."weight"), 0)
3461 FROM "issue" CROSS JOIN "opinion"
3462 JOIN "direct_interest_snapshot" AS "snapshot"
3463 ON "snapshot"."issue_id" = "issue"."id"
3464 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3465 AND "snapshot"."member_id" = "opinion"."member_id"
3466 WHERE "issue"."id" = "issue_id_p"
3467 AND "opinion"."suggestion_id" = "suggestion_id_v"
3468 AND "opinion"."degree" = -1
3469 AND "opinion"."fulfilled" = TRUE
3470 ),
3471 "plus1_unfulfilled_count" = (
3472 SELECT coalesce(sum("snapshot"."weight"), 0)
3473 FROM "issue" CROSS JOIN "opinion"
3474 JOIN "direct_interest_snapshot" AS "snapshot"
3475 ON "snapshot"."issue_id" = "issue"."id"
3476 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3477 AND "snapshot"."member_id" = "opinion"."member_id"
3478 WHERE "issue"."id" = "issue_id_p"
3479 AND "opinion"."suggestion_id" = "suggestion_id_v"
3480 AND "opinion"."degree" = 1
3481 AND "opinion"."fulfilled" = FALSE
3482 ),
3483 "plus1_fulfilled_count" = (
3484 SELECT coalesce(sum("snapshot"."weight"), 0)
3485 FROM "issue" CROSS JOIN "opinion"
3486 JOIN "direct_interest_snapshot" AS "snapshot"
3487 ON "snapshot"."issue_id" = "issue"."id"
3488 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3489 AND "snapshot"."member_id" = "opinion"."member_id"
3490 WHERE "issue"."id" = "issue_id_p"
3491 AND "opinion"."suggestion_id" = "suggestion_id_v"
3492 AND "opinion"."degree" = 1
3493 AND "opinion"."fulfilled" = TRUE
3494 ),
3495 "plus2_unfulfilled_count" = (
3496 SELECT coalesce(sum("snapshot"."weight"), 0)
3497 FROM "issue" CROSS JOIN "opinion"
3498 JOIN "direct_interest_snapshot" AS "snapshot"
3499 ON "snapshot"."issue_id" = "issue"."id"
3500 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3501 AND "snapshot"."member_id" = "opinion"."member_id"
3502 WHERE "issue"."id" = "issue_id_p"
3503 AND "opinion"."suggestion_id" = "suggestion_id_v"
3504 AND "opinion"."degree" = 2
3505 AND "opinion"."fulfilled" = FALSE
3506 ),
3507 "plus2_fulfilled_count" = (
3508 SELECT coalesce(sum("snapshot"."weight"), 0)
3509 FROM "issue" CROSS JOIN "opinion"
3510 JOIN "direct_interest_snapshot" AS "snapshot"
3511 ON "snapshot"."issue_id" = "issue"."id"
3512 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3513 AND "snapshot"."member_id" = "opinion"."member_id"
3514 WHERE "issue"."id" = "issue_id_p"
3515 AND "opinion"."suggestion_id" = "suggestion_id_v"
3516 AND "opinion"."degree" = 2
3517 AND "opinion"."fulfilled" = TRUE
3518 )
3519 WHERE "suggestion"."id" = "suggestion_id_v";
3520 END LOOP;
3521 END LOOP;
3522 RETURN;
3523 END;
3524 $$;
3526 COMMENT ON FUNCTION "create_snapshot"
3527 ( "issue"."id"%TYPE )
3528 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.';
3531 CREATE FUNCTION "set_snapshot_event"
3532 ( "issue_id_p" "issue"."id"%TYPE,
3533 "event_p" "snapshot_event" )
3534 RETURNS VOID
3535 LANGUAGE 'plpgsql' VOLATILE AS $$
3536 DECLARE
3537 "event_v" "issue"."latest_snapshot_event"%TYPE;
3538 BEGIN
3539 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
3540 WHERE "id" = "issue_id_p" FOR UPDATE;
3541 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
3542 WHERE "id" = "issue_id_p";
3543 UPDATE "direct_population_snapshot" SET "event" = "event_p"
3544 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3545 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
3546 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3547 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
3548 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3549 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
3550 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3551 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
3552 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3553 RETURN;
3554 END;
3555 $$;
3557 COMMENT ON FUNCTION "set_snapshot_event"
3558 ( "issue"."id"%TYPE,
3559 "snapshot_event" )
3560 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
3564 ---------------------
3565 -- Freezing issues --
3566 ---------------------
3568 CREATE FUNCTION "freeze_after_snapshot"
3569 ( "issue_id_p" "issue"."id"%TYPE )
3570 RETURNS VOID
3571 LANGUAGE 'plpgsql' VOLATILE AS $$
3572 DECLARE
3573 "issue_row" "issue"%ROWTYPE;
3574 "policy_row" "policy"%ROWTYPE;
3575 "initiative_row" "initiative"%ROWTYPE;
3576 BEGIN
3577 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3578 SELECT * INTO "policy_row"
3579 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3580 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
3581 FOR "initiative_row" IN
3582 SELECT * FROM "initiative"
3583 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3584 LOOP
3585 IF
3586 "initiative_row"."polling" OR (
3587 "initiative_row"."satisfied_supporter_count" > 0 AND
3588 "initiative_row"."satisfied_supporter_count" *
3589 "policy_row"."initiative_quorum_den" >=
3590 "issue_row"."population" * "policy_row"."initiative_quorum_num"
3591 )
3592 THEN
3593 UPDATE "initiative" SET "admitted" = TRUE
3594 WHERE "id" = "initiative_row"."id";
3595 ELSE
3596 UPDATE "initiative" SET "admitted" = FALSE
3597 WHERE "id" = "initiative_row"."id";
3598 END IF;
3599 END LOOP;
3600 IF EXISTS (
3601 SELECT NULL FROM "initiative"
3602 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
3603 ) THEN
3604 UPDATE "issue" SET
3605 "state" = 'voting',
3606 "accepted" = coalesce("accepted", now()),
3607 "half_frozen" = coalesce("half_frozen", now()),
3608 "fully_frozen" = now()
3609 WHERE "id" = "issue_id_p";
3610 ELSE
3611 UPDATE "issue" SET
3612 "state" = 'canceled_no_initiative_admitted',
3613 "accepted" = coalesce("accepted", now()),
3614 "half_frozen" = coalesce("half_frozen", now()),
3615 "fully_frozen" = now(),
3616 "closed" = now(),
3617 "ranks_available" = TRUE
3618 WHERE "id" = "issue_id_p";
3619 -- NOTE: The following DELETE statements have effect only when
3620 -- issue state has been manipulated
3621 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
3622 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
3623 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3624 END IF;
3625 RETURN;
3626 END;
3627 $$;
3629 COMMENT ON FUNCTION "freeze_after_snapshot"
3630 ( "issue"."id"%TYPE )
3631 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
3634 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
3635 RETURNS VOID
3636 LANGUAGE 'plpgsql' VOLATILE AS $$
3637 DECLARE
3638 "issue_row" "issue"%ROWTYPE;
3639 BEGIN
3640 PERFORM "create_snapshot"("issue_id_p");
3641 PERFORM "freeze_after_snapshot"("issue_id_p");
3642 RETURN;
3643 END;
3644 $$;
3646 COMMENT ON FUNCTION "manual_freeze"
3647 ( "issue"."id"%TYPE )
3648 IS 'Freeze an issue manually (fully) and start voting';
3652 -----------------------
3653 -- Counting of votes --
3654 -----------------------
3657 CREATE FUNCTION "weight_of_added_vote_delegations"
3658 ( "issue_id_p" "issue"."id"%TYPE,
3659 "member_id_p" "member"."id"%TYPE,
3660 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
3661 RETURNS "direct_voter"."weight"%TYPE
3662 LANGUAGE 'plpgsql' VOLATILE AS $$
3663 DECLARE
3664 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3665 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
3666 "weight_v" INT4;
3667 "sub_weight_v" INT4;
3668 BEGIN
3669 "weight_v" := 0;
3670 FOR "issue_delegation_row" IN
3671 SELECT * FROM "issue_delegation"
3672 WHERE "trustee_id" = "member_id_p"
3673 AND "issue_id" = "issue_id_p"
3674 LOOP
3675 IF NOT EXISTS (
3676 SELECT NULL FROM "direct_voter"
3677 WHERE "member_id" = "issue_delegation_row"."truster_id"
3678 AND "issue_id" = "issue_id_p"
3679 ) AND NOT EXISTS (
3680 SELECT NULL FROM "delegating_voter"
3681 WHERE "member_id" = "issue_delegation_row"."truster_id"
3682 AND "issue_id" = "issue_id_p"
3683 ) THEN
3684 "delegate_member_ids_v" :=
3685 "member_id_p" || "delegate_member_ids_p";
3686 INSERT INTO "delegating_voter" (
3687 "issue_id",
3688 "member_id",
3689 "scope",
3690 "delegate_member_ids"
3691 ) VALUES (
3692 "issue_id_p",
3693 "issue_delegation_row"."truster_id",
3694 "issue_delegation_row"."scope",
3695 "delegate_member_ids_v"
3696 );
3697 "sub_weight_v" := 1 +
3698 "weight_of_added_vote_delegations"(
3699 "issue_id_p",
3700 "issue_delegation_row"."truster_id",
3701 "delegate_member_ids_v"
3702 );
3703 UPDATE "delegating_voter"
3704 SET "weight" = "sub_weight_v"
3705 WHERE "issue_id" = "issue_id_p"
3706 AND "member_id" = "issue_delegation_row"."truster_id";
3707 "weight_v" := "weight_v" + "sub_weight_v";
3708 END IF;
3709 END LOOP;
3710 RETURN "weight_v";
3711 END;
3712 $$;
3714 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
3715 ( "issue"."id"%TYPE,
3716 "member"."id"%TYPE,
3717 "delegating_voter"."delegate_member_ids"%TYPE )
3718 IS 'Helper function for "add_vote_delegations" function';
3721 CREATE FUNCTION "add_vote_delegations"
3722 ( "issue_id_p" "issue"."id"%TYPE )
3723 RETURNS VOID
3724 LANGUAGE 'plpgsql' VOLATILE AS $$
3725 DECLARE
3726 "member_id_v" "member"."id"%TYPE;
3727 BEGIN
3728 FOR "member_id_v" IN
3729 SELECT "member_id" FROM "direct_voter"
3730 WHERE "issue_id" = "issue_id_p"
3731 LOOP
3732 UPDATE "direct_voter" SET
3733 "weight" = "weight" + "weight_of_added_vote_delegations"(
3734 "issue_id_p",
3735 "member_id_v",
3736 '{}'
3737 )
3738 WHERE "member_id" = "member_id_v"
3739 AND "issue_id" = "issue_id_p";
3740 END LOOP;
3741 RETURN;
3742 END;
3743 $$;
3745 COMMENT ON FUNCTION "add_vote_delegations"
3746 ( "issue_id_p" "issue"."id"%TYPE )
3747 IS 'Helper function for "close_voting" function';
3750 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
3751 RETURNS VOID
3752 LANGUAGE 'plpgsql' VOLATILE AS $$
3753 DECLARE
3754 "area_id_v" "area"."id"%TYPE;
3755 "unit_id_v" "unit"."id"%TYPE;
3756 "member_id_v" "member"."id"%TYPE;
3757 BEGIN
3758 PERFORM "lock_issue"("issue_id_p");
3759 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
3760 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
3761 -- delete timestamp of voting comment:
3762 UPDATE "direct_voter" SET "comment_changed" = NULL
3763 WHERE "issue_id" = "issue_id_p";
3764 -- delete delegating votes (in cases of manual reset of issue state):
3765 DELETE FROM "delegating_voter"
3766 WHERE "issue_id" = "issue_id_p";
3767 -- delete votes from non-privileged voters:
3768 DELETE FROM "direct_voter"
3769 USING (
3770 SELECT
3771 "direct_voter"."member_id"
3772 FROM "direct_voter"
3773 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
3774 LEFT JOIN "privilege"
3775 ON "privilege"."unit_id" = "unit_id_v"
3776 AND "privilege"."member_id" = "direct_voter"."member_id"
3777 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
3778 "member"."active" = FALSE OR
3779 "privilege"."voting_right" ISNULL OR
3780 "privilege"."voting_right" = FALSE
3781 )
3782 ) AS "subquery"
3783 WHERE "direct_voter"."issue_id" = "issue_id_p"
3784 AND "direct_voter"."member_id" = "subquery"."member_id";
3785 -- consider delegations:
3786 UPDATE "direct_voter" SET "weight" = 1
3787 WHERE "issue_id" = "issue_id_p";
3788 PERFORM "add_vote_delegations"("issue_id_p");
3789 -- set voter count and mark issue as being calculated:
3790 UPDATE "issue" SET
3791 "state" = 'calculation',
3792 "closed" = now(),
3793 "voter_count" = (
3794 SELECT coalesce(sum("weight"), 0)
3795 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
3796 )
3797 WHERE "id" = "issue_id_p";
3798 -- materialize battle_view:
3799 -- NOTE: "closed" column of issue must be set at this point
3800 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3801 INSERT INTO "battle" (
3802 "issue_id",
3803 "winning_initiative_id", "losing_initiative_id",
3804 "count"
3805 ) SELECT
3806 "issue_id",
3807 "winning_initiative_id", "losing_initiative_id",
3808 "count"
3809 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
3810 -- copy "positive_votes" and "negative_votes" from "battle" table:
3811 UPDATE "initiative" SET
3812 "positive_votes" = "battle_win"."count",
3813 "negative_votes" = "battle_lose"."count"
3814 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
3815 WHERE
3816 "battle_win"."issue_id" = "issue_id_p" AND
3817 "battle_win"."winning_initiative_id" = "initiative"."id" AND
3818 "battle_win"."losing_initiative_id" ISNULL AND
3819 "battle_lose"."issue_id" = "issue_id_p" AND
3820 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
3821 "battle_lose"."winning_initiative_id" ISNULL;
3822 END;
3823 $$;
3825 COMMENT ON FUNCTION "close_voting"
3826 ( "issue"."id"%TYPE )
3827 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.';
3830 CREATE FUNCTION "defeat_strength"
3831 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
3832 RETURNS INT8
3833 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3834 BEGIN
3835 IF "positive_votes_p" > "negative_votes_p" THEN
3836 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
3837 ELSIF "positive_votes_p" = "negative_votes_p" THEN
3838 RETURN 0;
3839 ELSE
3840 RETURN -1;
3841 END IF;
3842 END;
3843 $$;
3845 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';
3848 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
3849 RETURNS VOID
3850 LANGUAGE 'plpgsql' VOLATILE AS $$
3851 DECLARE
3852 "issue_row" "issue"%ROWTYPE;
3853 "policy_row" "policy"%ROWTYPE;
3854 "dimension_v" INTEGER;
3855 "vote_matrix" INT4[][]; -- absolute votes
3856 "matrix" INT8[][]; -- defeat strength / best paths
3857 "i" INTEGER;
3858 "j" INTEGER;
3859 "k" INTEGER;
3860 "battle_row" "battle"%ROWTYPE;
3861 "rank_ary" INT4[];
3862 "rank_v" INT4;
3863 "done_v" INTEGER;
3864 "winners_ary" INTEGER[];
3865 "initiative_id_v" "initiative"."id"%TYPE;
3866 BEGIN
3867 SELECT * INTO "issue_row"
3868 FROM "issue" WHERE "id" = "issue_id_p"
3869 FOR UPDATE;
3870 SELECT * INTO "policy_row"
3871 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3872 SELECT count(1) INTO "dimension_v"
3873 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
3874 -- Create "vote_matrix" with absolute number of votes in pairwise
3875 -- comparison:
3876 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
3877 "i" := 1;
3878 "j" := 2;
3879 FOR "battle_row" IN
3880 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
3881 ORDER BY
3882 "winning_initiative_id" NULLS LAST,
3883 "losing_initiative_id" NULLS LAST
3884 LOOP
3885 "vote_matrix"["i"]["j"] := "battle_row"."count";
3886 IF "j" = "dimension_v" THEN
3887 "i" := "i" + 1;
3888 "j" := 1;
3889 ELSE
3890 "j" := "j" + 1;
3891 IF "j" = "i" THEN
3892 "j" := "j" + 1;
3893 END IF;
3894 END IF;
3895 END LOOP;
3896 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
3897 RAISE EXCEPTION 'Wrong battle count (should not happen)';
3898 END IF;
3899 -- Store defeat strengths in "matrix" using "defeat_strength"
3900 -- function:
3901 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
3902 "i" := 1;
3903 LOOP
3904 "j" := 1;
3905 LOOP
3906 IF "i" != "j" THEN
3907 "matrix"["i"]["j"] := "defeat_strength"(
3908 "vote_matrix"["i"]["j"],
3909 "vote_matrix"["j"]["i"]
3910 );
3911 END IF;
3912 EXIT WHEN "j" = "dimension_v";
3913 "j" := "j" + 1;
3914 END LOOP;
3915 EXIT WHEN "i" = "dimension_v";
3916 "i" := "i" + 1;
3917 END LOOP;
3918 -- Find best paths:
3919 "i" := 1;
3920 LOOP
3921 "j" := 1;
3922 LOOP
3923 IF "i" != "j" THEN
3924 "k" := 1;
3925 LOOP
3926 IF "i" != "k" AND "j" != "k" THEN
3927 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
3928 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
3929 "matrix"["j"]["k"] := "matrix"["j"]["i"];
3930 END IF;
3931 ELSE
3932 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
3933 "matrix"["j"]["k"] := "matrix"["i"]["k"];
3934 END IF;
3935 END IF;
3936 END IF;
3937 EXIT WHEN "k" = "dimension_v";
3938 "k" := "k" + 1;
3939 END LOOP;
3940 END IF;
3941 EXIT WHEN "j" = "dimension_v";
3942 "j" := "j" + 1;
3943 END LOOP;
3944 EXIT WHEN "i" = "dimension_v";
3945 "i" := "i" + 1;
3946 END LOOP;
3947 -- Determine order of winners:
3948 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
3949 "rank_v" := 1;
3950 "done_v" := 0;
3951 LOOP
3952 "winners_ary" := '{}';
3953 "i" := 1;
3954 LOOP
3955 IF "rank_ary"["i"] ISNULL THEN
3956 "j" := 1;
3957 LOOP
3958 IF
3959 "i" != "j" AND
3960 "rank_ary"["j"] ISNULL AND
3961 "matrix"["j"]["i"] > "matrix"["i"]["j"]
3962 THEN
3963 -- someone else is better
3964 EXIT;
3965 END IF;
3966 IF "j" = "dimension_v" THEN
3967 -- noone is better
3968 "winners_ary" := "winners_ary" || "i";
3969 EXIT;
3970 END IF;
3971 "j" := "j" + 1;
3972 END LOOP;
3973 END IF;
3974 EXIT WHEN "i" = "dimension_v";
3975 "i" := "i" + 1;
3976 END LOOP;
3977 "i" := 1;
3978 LOOP
3979 "rank_ary"["winners_ary"["i"]] := "rank_v";
3980 "done_v" := "done_v" + 1;
3981 EXIT WHEN "i" = array_upper("winners_ary", 1);
3982 "i" := "i" + 1;
3983 END LOOP;
3984 EXIT WHEN "done_v" = "dimension_v";
3985 "rank_v" := "rank_v" + 1;
3986 END LOOP;
3987 -- write preliminary results:
3988 "i" := 1;
3989 FOR "initiative_id_v" IN
3990 SELECT "id" FROM "initiative"
3991 WHERE "issue_id" = "issue_id_p" AND "admitted"
3992 ORDER BY "id"
3993 LOOP
3994 UPDATE "initiative" SET
3995 "direct_majority" =
3996 CASE WHEN "policy_row"."direct_majority_strict" THEN
3997 "positive_votes" * "policy_row"."direct_majority_den" >
3998 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
3999 ELSE
4000 "positive_votes" * "policy_row"."direct_majority_den" >=
4001 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
4002 END
4003 AND "positive_votes" >= "policy_row"."direct_majority_positive"
4004 AND "issue_row"."voter_count"-"negative_votes" >=
4005 "policy_row"."direct_majority_non_negative",
4006 "indirect_majority" =
4007 CASE WHEN "policy_row"."indirect_majority_strict" THEN
4008 "positive_votes" * "policy_row"."indirect_majority_den" >
4009 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
4010 ELSE
4011 "positive_votes" * "policy_row"."indirect_majority_den" >=
4012 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
4013 END
4014 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
4015 AND "issue_row"."voter_count"-"negative_votes" >=
4016 "policy_row"."indirect_majority_non_negative",
4017 "schulze_rank" = "rank_ary"["i"],
4018 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
4019 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
4020 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
4021 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0,
4022 "eligible" = FALSE,
4023 "winner" = FALSE,
4024 "rank" = NULL -- NOTE: in cases of manual reset of issue state
4025 WHERE "id" = "initiative_id_v";
4026 "i" := "i" + 1;
4027 END LOOP;
4028 IF "i" != "dimension_v" THEN
4029 RAISE EXCEPTION 'Wrong winner count (should not happen)';
4030 END IF;
4031 -- take indirect majorities into account:
4032 LOOP
4033 UPDATE "initiative" SET "indirect_majority" = TRUE
4034 FROM (
4035 SELECT "new_initiative"."id" AS "initiative_id"
4036 FROM "initiative" "old_initiative"
4037 JOIN "initiative" "new_initiative"
4038 ON "new_initiative"."issue_id" = "issue_id_p"
4039 AND "new_initiative"."indirect_majority" = FALSE
4040 JOIN "battle" "battle_win"
4041 ON "battle_win"."issue_id" = "issue_id_p"
4042 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
4043 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
4044 JOIN "battle" "battle_lose"
4045 ON "battle_lose"."issue_id" = "issue_id_p"
4046 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
4047 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
4048 WHERE "old_initiative"."issue_id" = "issue_id_p"
4049 AND "old_initiative"."indirect_majority" = TRUE
4050 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
4051 "battle_win"."count" * "policy_row"."indirect_majority_den" >
4052 "policy_row"."indirect_majority_num" *
4053 ("battle_win"."count"+"battle_lose"."count")
4054 ELSE
4055 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
4056 "policy_row"."indirect_majority_num" *
4057 ("battle_win"."count"+"battle_lose"."count")
4058 END
4059 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
4060 AND "issue_row"."voter_count"-"battle_lose"."count" >=
4061 "policy_row"."indirect_majority_non_negative"
4062 ) AS "subquery"
4063 WHERE "id" = "subquery"."initiative_id";
4064 EXIT WHEN NOT FOUND;
4065 END LOOP;
4066 -- set "multistage_majority" for remaining matching initiatives:
4067 UPDATE "initiative" SET "multistage_majority" = TRUE
4068 FROM (
4069 SELECT "losing_initiative"."id" AS "initiative_id"
4070 FROM "initiative" "losing_initiative"
4071 JOIN "initiative" "winning_initiative"
4072 ON "winning_initiative"."issue_id" = "issue_id_p"
4073 AND "winning_initiative"."admitted"
4074 JOIN "battle" "battle_win"
4075 ON "battle_win"."issue_id" = "issue_id_p"
4076 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
4077 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
4078 JOIN "battle" "battle_lose"
4079 ON "battle_lose"."issue_id" = "issue_id_p"
4080 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
4081 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
4082 WHERE "losing_initiative"."issue_id" = "issue_id_p"
4083 AND "losing_initiative"."admitted"
4084 AND "winning_initiative"."schulze_rank" <
4085 "losing_initiative"."schulze_rank"
4086 AND "battle_win"."count" > "battle_lose"."count"
4087 AND (
4088 "battle_win"."count" > "winning_initiative"."positive_votes" OR
4089 "battle_lose"."count" < "losing_initiative"."negative_votes" )
4090 ) AS "subquery"
4091 WHERE "id" = "subquery"."initiative_id";
4092 -- mark eligible initiatives:
4093 UPDATE "initiative" SET "eligible" = TRUE
4094 WHERE "issue_id" = "issue_id_p"
4095 AND "initiative"."direct_majority"
4096 AND "initiative"."indirect_majority"
4097 AND "initiative"."better_than_status_quo"
4098 AND (
4099 "policy_row"."no_multistage_majority" = FALSE OR
4100 "initiative"."multistage_majority" = FALSE )
4101 AND (
4102 "policy_row"."no_reverse_beat_path" = FALSE OR
4103 "initiative"."reverse_beat_path" = FALSE );
4104 -- mark final winner:
4105 UPDATE "initiative" SET "winner" = TRUE
4106 FROM (
4107 SELECT "id" AS "initiative_id"
4108 FROM "initiative"
4109 WHERE "issue_id" = "issue_id_p" AND "eligible"
4110 ORDER BY
4111 "schulze_rank",
4112 "vote_ratio"("positive_votes", "negative_votes"),
4113 "id"
4114 LIMIT 1
4115 ) AS "subquery"
4116 WHERE "id" = "subquery"."initiative_id";
4117 -- write (final) ranks:
4118 "rank_v" := 1;
4119 FOR "initiative_id_v" IN
4120 SELECT "id"
4121 FROM "initiative"
4122 WHERE "issue_id" = "issue_id_p" AND "admitted"
4123 ORDER BY
4124 "winner" DESC,
4125 "eligible" DESC,
4126 "schulze_rank",
4127 "vote_ratio"("positive_votes", "negative_votes"),
4128 "id"
4129 LOOP
4130 UPDATE "initiative" SET "rank" = "rank_v"
4131 WHERE "id" = "initiative_id_v";
4132 "rank_v" := "rank_v" + 1;
4133 END LOOP;
4134 -- set schulze rank of status quo and mark issue as finished:
4135 UPDATE "issue" SET
4136 "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
4137 "state" =
4138 CASE WHEN EXISTS (
4139 SELECT NULL FROM "initiative"
4140 WHERE "issue_id" = "issue_id_p" AND "winner"
4141 ) THEN
4142 'finished_with_winner'::"issue_state"
4143 ELSE
4144 'finished_without_winner'::"issue_state"
4145 END,
4146 "ranks_available" = TRUE
4147 WHERE "id" = "issue_id_p";
4148 RETURN;
4149 END;
4150 $$;
4152 COMMENT ON FUNCTION "calculate_ranks"
4153 ( "issue"."id"%TYPE )
4154 IS 'Determine ranking (Votes have to be counted first)';
4158 -----------------------------
4159 -- Automatic state changes --
4160 -----------------------------
4163 CREATE FUNCTION "check_issue"
4164 ( "issue_id_p" "issue"."id"%TYPE )
4165 RETURNS VOID
4166 LANGUAGE 'plpgsql' VOLATILE AS $$
4167 DECLARE
4168 "issue_row" "issue"%ROWTYPE;
4169 "policy_row" "policy"%ROWTYPE;
4170 BEGIN
4171 PERFORM "lock_issue"("issue_id_p");
4172 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4173 -- only process open issues:
4174 IF "issue_row"."closed" ISNULL THEN
4175 SELECT * INTO "policy_row" FROM "policy"
4176 WHERE "id" = "issue_row"."policy_id";
4177 -- create a snapshot, unless issue is already fully frozen:
4178 IF "issue_row"."fully_frozen" ISNULL THEN
4179 PERFORM "create_snapshot"("issue_id_p");
4180 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4181 END IF;
4182 -- eventually close or accept issues, which have not been accepted:
4183 IF "issue_row"."accepted" ISNULL THEN
4184 IF EXISTS (
4185 SELECT NULL FROM "initiative"
4186 WHERE "issue_id" = "issue_id_p"
4187 AND "supporter_count" > 0
4188 AND "supporter_count" * "policy_row"."issue_quorum_den"
4189 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
4190 ) THEN
4191 -- accept issues, if supporter count is high enough
4192 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
4193 -- NOTE: "issue_row" used later
4194 "issue_row"."state" := 'discussion';
4195 "issue_row"."accepted" := now();
4196 UPDATE "issue" SET
4197 "state" = "issue_row"."state",
4198 "accepted" = "issue_row"."accepted"
4199 WHERE "id" = "issue_row"."id";
4200 ELSIF
4201 now() >= "issue_row"."created" + "issue_row"."admission_time"
4202 THEN
4203 -- close issues, if admission time has expired
4204 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
4205 UPDATE "issue" SET
4206 "state" = 'canceled_issue_not_accepted',
4207 "closed" = now()
4208 WHERE "id" = "issue_row"."id";
4209 END IF;
4210 END IF;
4211 -- eventually half freeze issues:
4212 IF
4213 -- NOTE: issue can't be closed at this point, if it has been accepted
4214 "issue_row"."accepted" NOTNULL AND
4215 "issue_row"."half_frozen" ISNULL
4216 THEN
4217 IF
4218 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
4219 THEN
4220 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
4221 -- NOTE: "issue_row" used later
4222 "issue_row"."state" := 'verification';
4223 "issue_row"."half_frozen" := now();
4224 UPDATE "issue" SET
4225 "state" = "issue_row"."state",
4226 "half_frozen" = "issue_row"."half_frozen"
4227 WHERE "id" = "issue_row"."id";
4228 END IF;
4229 END IF;
4230 -- close issues after some time, if all initiatives have been revoked:
4231 IF
4232 "issue_row"."closed" ISNULL AND
4233 NOT EXISTS (
4234 -- all initiatives are revoked
4235 SELECT NULL FROM "initiative"
4236 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
4237 ) AND (
4238 -- and issue has not been accepted yet
4239 "issue_row"."accepted" ISNULL OR
4240 NOT EXISTS (
4241 -- or no initiatives have been revoked lately
4242 SELECT NULL FROM "initiative"
4243 WHERE "issue_id" = "issue_id_p"
4244 AND now() < "revoked" + "issue_row"."verification_time"
4245 ) OR (
4246 -- or verification time has elapsed
4247 "issue_row"."half_frozen" NOTNULL AND
4248 "issue_row"."fully_frozen" ISNULL AND
4249 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
4250 )
4251 )
4252 THEN
4253 -- NOTE: "issue_row" used later
4254 IF "issue_row"."accepted" ISNULL THEN
4255 "issue_row"."state" := 'canceled_revoked_before_accepted';
4256 ELSIF "issue_row"."half_frozen" ISNULL THEN
4257 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
4258 ELSE
4259 "issue_row"."state" := 'canceled_after_revocation_during_verification';
4260 END IF;
4261 "issue_row"."closed" := now();
4262 UPDATE "issue" SET
4263 "state" = "issue_row"."state",
4264 "closed" = "issue_row"."closed"
4265 WHERE "id" = "issue_row"."id";
4266 END IF;
4267 -- fully freeze issue after verification time:
4268 IF
4269 "issue_row"."half_frozen" NOTNULL AND
4270 "issue_row"."fully_frozen" ISNULL AND
4271 "issue_row"."closed" ISNULL AND
4272 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
4273 THEN
4274 PERFORM "freeze_after_snapshot"("issue_id_p");
4275 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
4276 END IF;
4277 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4278 -- close issue by calling close_voting(...) after voting time:
4279 IF
4280 "issue_row"."closed" ISNULL AND
4281 "issue_row"."fully_frozen" NOTNULL AND
4282 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
4283 THEN
4284 PERFORM "close_voting"("issue_id_p");
4285 -- calculate ranks will not consume much time and can be done now
4286 PERFORM "calculate_ranks"("issue_id_p");
4287 END IF;
4288 END IF;
4289 RETURN;
4290 END;
4291 $$;
4293 COMMENT ON FUNCTION "check_issue"
4294 ( "issue"."id"%TYPE )
4295 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.';
4298 CREATE FUNCTION "check_everything"()
4299 RETURNS VOID
4300 LANGUAGE 'plpgsql' VOLATILE AS $$
4301 DECLARE
4302 "issue_id_v" "issue"."id"%TYPE;
4303 BEGIN
4304 DELETE FROM "expired_session";
4305 PERFORM "check_activity"();
4306 PERFORM "calculate_member_counts"();
4307 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
4308 PERFORM "check_issue"("issue_id_v");
4309 END LOOP;
4310 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
4311 PERFORM "calculate_ranks"("issue_id_v");
4312 END LOOP;
4313 RETURN;
4314 END;
4315 $$;
4317 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.';
4321 ----------------------
4322 -- Deletion of data --
4323 ----------------------
4326 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
4327 RETURNS VOID
4328 LANGUAGE 'plpgsql' VOLATILE AS $$
4329 DECLARE
4330 "issue_row" "issue"%ROWTYPE;
4331 BEGIN
4332 SELECT * INTO "issue_row"
4333 FROM "issue" WHERE "id" = "issue_id_p"
4334 FOR UPDATE;
4335 IF "issue_row"."cleaned" ISNULL THEN
4336 UPDATE "issue" SET
4337 "state" = 'voting',
4338 "closed" = NULL,
4339 "ranks_available" = FALSE
4340 WHERE "id" = "issue_id_p";
4341 DELETE FROM "delegating_voter"
4342 WHERE "issue_id" = "issue_id_p";
4343 DELETE FROM "direct_voter"
4344 WHERE "issue_id" = "issue_id_p";
4345 DELETE FROM "delegating_interest_snapshot"
4346 WHERE "issue_id" = "issue_id_p";
4347 DELETE FROM "direct_interest_snapshot"
4348 WHERE "issue_id" = "issue_id_p";
4349 DELETE FROM "delegating_population_snapshot"
4350 WHERE "issue_id" = "issue_id_p";
4351 DELETE FROM "direct_population_snapshot"
4352 WHERE "issue_id" = "issue_id_p";
4353 DELETE FROM "non_voter"
4354 WHERE "issue_id" = "issue_id_p";
4355 DELETE FROM "delegation"
4356 WHERE "issue_id" = "issue_id_p";
4357 DELETE FROM "supporter"
4358 WHERE "issue_id" = "issue_id_p";
4359 UPDATE "issue" SET
4360 "state" = "issue_row"."state",
4361 "closed" = "issue_row"."closed",
4362 "ranks_available" = "issue_row"."ranks_available",
4363 "cleaned" = now()
4364 WHERE "id" = "issue_id_p";
4365 END IF;
4366 RETURN;
4367 END;
4368 $$;
4370 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
4373 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
4374 RETURNS VOID
4375 LANGUAGE 'plpgsql' VOLATILE AS $$
4376 BEGIN
4377 UPDATE "member" SET
4378 "last_login" = NULL,
4379 "login" = NULL,
4380 "password" = NULL,
4381 "locked" = TRUE,
4382 "active" = FALSE,
4383 "notify_email" = NULL,
4384 "notify_email_unconfirmed" = NULL,
4385 "notify_email_secret" = NULL,
4386 "notify_email_secret_expiry" = NULL,
4387 "notify_email_lock_expiry" = NULL,
4388 "password_reset_secret" = NULL,
4389 "password_reset_secret_expiry" = NULL,
4390 "organizational_unit" = NULL,
4391 "internal_posts" = NULL,
4392 "realname" = NULL,
4393 "birthday" = NULL,
4394 "address" = NULL,
4395 "email" = NULL,
4396 "xmpp_address" = NULL,
4397 "website" = NULL,
4398 "phone" = NULL,
4399 "mobile_phone" = NULL,
4400 "profession" = NULL,
4401 "external_memberships" = NULL,
4402 "external_posts" = NULL,
4403 "statement" = NULL
4404 WHERE "id" = "member_id_p";
4405 -- "text_search_data" is updated by triggers
4406 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
4407 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
4408 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
4409 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
4410 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
4411 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
4412 DELETE FROM "session" WHERE "member_id" = "member_id_p";
4413 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
4414 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
4415 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
4416 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
4417 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
4418 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
4419 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
4420 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
4421 DELETE FROM "direct_voter" USING "issue"
4422 WHERE "direct_voter"."issue_id" = "issue"."id"
4423 AND "issue"."closed" ISNULL
4424 AND "member_id" = "member_id_p";
4425 RETURN;
4426 END;
4427 $$;
4429 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)';
4432 CREATE FUNCTION "delete_private_data"()
4433 RETURNS VOID
4434 LANGUAGE 'plpgsql' VOLATILE AS $$
4435 BEGIN
4436 DELETE FROM "member" WHERE "activated" ISNULL;
4437 UPDATE "member" SET
4438 "invite_code" = NULL,
4439 "invite_code_expiry" = NULL,
4440 "admin_comment" = NULL,
4441 "last_login" = NULL,
4442 "login" = NULL,
4443 "password" = NULL,
4444 "lang" = NULL,
4445 "notify_email" = NULL,
4446 "notify_email_unconfirmed" = NULL,
4447 "notify_email_secret" = NULL,
4448 "notify_email_secret_expiry" = NULL,
4449 "notify_email_lock_expiry" = NULL,
4450 "notify_level" = NULL,
4451 "password_reset_secret" = NULL,
4452 "password_reset_secret_expiry" = NULL,
4453 "organizational_unit" = NULL,
4454 "internal_posts" = NULL,
4455 "realname" = NULL,
4456 "birthday" = NULL,
4457 "address" = NULL,
4458 "email" = NULL,
4459 "xmpp_address" = NULL,
4460 "website" = NULL,
4461 "phone" = NULL,
4462 "mobile_phone" = NULL,
4463 "profession" = NULL,
4464 "external_memberships" = NULL,
4465 "external_posts" = NULL,
4466 "formatting_engine" = NULL,
4467 "statement" = NULL;
4468 -- "text_search_data" is updated by triggers
4469 DELETE FROM "setting";
4470 DELETE FROM "setting_map";
4471 DELETE FROM "member_relation_setting";
4472 DELETE FROM "member_image";
4473 DELETE FROM "contact";
4474 DELETE FROM "ignored_member";
4475 DELETE FROM "session";
4476 DELETE FROM "area_setting";
4477 DELETE FROM "issue_setting";
4478 DELETE FROM "ignored_initiative";
4479 DELETE FROM "initiative_setting";
4480 DELETE FROM "suggestion_setting";
4481 DELETE FROM "non_voter";
4482 DELETE FROM "direct_voter" USING "issue"
4483 WHERE "direct_voter"."issue_id" = "issue"."id"
4484 AND "issue"."closed" ISNULL;
4485 RETURN;
4486 END;
4487 $$;
4489 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.';
4493 COMMIT;