liquid_feedback_core
view core.sql @ 259:620ec5751a1b
Removed "issue_comment"
| author | jbe |
|---|---|
| date | Sun Jul 15 19:14:39 2012 +0200 (2012-07-15) |
| parents | a1db85ce10ea |
| children | 878f84407290 |
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 CREATE TYPE "application_access_level" AS ENUM
174 ('member', 'full', 'pseudonymous', 'anonymous');
176 COMMENT ON TYPE "application_access_level" IS 'Access privileges for applications using the API';
179 CREATE TABLE "member_application" (
180 "id" SERIAL8 PRIMARY KEY,
181 UNIQUE ("member_id", "name"),
182 "member_id" INT4 NOT NULL REFERENCES "member" ("id")
183 ON DELETE CASCADE ON UPDATE CASCADE,
184 "name" TEXT NOT NULL,
185 "comment" TEXT,
186 "access_level" "application_access_level" NOT NULL,
187 "key" TEXT NOT NULL UNIQUE,
188 "last_usage" TIMESTAMPTZ );
190 COMMENT ON TABLE "member_application" IS 'Registered application being allowed to use the API';
193 CREATE TABLE "member_history" (
194 "id" SERIAL8 PRIMARY KEY,
195 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
196 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
197 "active" BOOLEAN NOT NULL,
198 "name" TEXT NOT NULL );
199 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
201 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
203 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
204 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
207 CREATE TABLE "rendered_member_statement" (
208 PRIMARY KEY ("member_id", "format"),
209 "member_id" INT8 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
210 "format" TEXT,
211 "content" TEXT NOT NULL );
213 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)';
216 CREATE TABLE "setting" (
217 PRIMARY KEY ("member_id", "key"),
218 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
219 "key" TEXT NOT NULL,
220 "value" TEXT NOT NULL );
221 CREATE INDEX "setting_key_idx" ON "setting" ("key");
223 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
225 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
228 CREATE TABLE "setting_map" (
229 PRIMARY KEY ("member_id", "key", "subkey"),
230 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
231 "key" TEXT NOT NULL,
232 "subkey" TEXT NOT NULL,
233 "value" TEXT NOT NULL );
234 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
236 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
238 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
239 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
240 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
243 CREATE TABLE "member_relation_setting" (
244 PRIMARY KEY ("member_id", "key", "other_member_id"),
245 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
246 "key" TEXT NOT NULL,
247 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
248 "value" TEXT NOT NULL );
250 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
253 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
255 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
258 CREATE TABLE "member_image" (
259 PRIMARY KEY ("member_id", "image_type", "scaled"),
260 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
261 "image_type" "member_image_type",
262 "scaled" BOOLEAN,
263 "content_type" TEXT,
264 "data" BYTEA NOT NULL );
266 COMMENT ON TABLE "member_image" IS 'Images of members';
268 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
271 CREATE TABLE "member_count" (
272 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
273 "total_count" INT4 NOT NULL );
275 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';
277 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
278 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
281 CREATE TABLE "contact" (
282 PRIMARY KEY ("member_id", "other_member_id"),
283 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
284 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
285 "public" BOOLEAN NOT NULL DEFAULT FALSE,
286 CONSTRAINT "cant_save_yourself_as_contact"
287 CHECK ("member_id" != "other_member_id") );
288 CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
290 COMMENT ON TABLE "contact" IS 'Contact lists';
292 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
293 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
294 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
297 CREATE TABLE "ignored_member" (
298 PRIMARY KEY ("member_id", "other_member_id"),
299 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
300 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
301 CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
303 COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
305 COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone';
306 COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
309 CREATE TABLE "session" (
310 "ident" TEXT PRIMARY KEY,
311 "additional_secret" TEXT,
312 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
313 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
314 "lang" TEXT );
315 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
317 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer';
319 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
320 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
321 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
322 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
325 CREATE TABLE "policy" (
326 "id" SERIAL4 PRIMARY KEY,
327 "index" INT4 NOT NULL,
328 "active" BOOLEAN NOT NULL DEFAULT TRUE,
329 "name" TEXT NOT NULL UNIQUE,
330 "description" TEXT NOT NULL DEFAULT '',
331 "admission_time" INTERVAL NOT NULL,
332 "discussion_time" INTERVAL NOT NULL,
333 "verification_time" INTERVAL NOT NULL,
334 "voting_time" INTERVAL NOT NULL,
335 "issue_quorum_num" INT4 NOT NULL,
336 "issue_quorum_den" INT4 NOT NULL,
337 "initiative_quorum_num" INT4 NOT NULL,
338 "initiative_quorum_den" INT4 NOT NULL,
339 "direct_majority_num" INT4 NOT NULL DEFAULT 1,
340 "direct_majority_den" INT4 NOT NULL DEFAULT 2,
341 "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
342 "direct_majority_positive" INT4 NOT NULL DEFAULT 0,
343 "direct_majority_non_negative" INT4 NOT NULL DEFAULT 0,
344 "indirect_majority_num" INT4 NOT NULL DEFAULT 1,
345 "indirect_majority_den" INT4 NOT NULL DEFAULT 2,
346 "indirect_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
347 "indirect_majority_positive" INT4 NOT NULL DEFAULT 0,
348 "indirect_majority_non_negative" INT4 NOT NULL DEFAULT 0,
349 "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT TRUE,
350 "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE );
351 CREATE INDEX "policy_active_idx" ON "policy" ("active");
353 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
355 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
356 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
357 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"';
358 COMMENT ON COLUMN "policy"."discussion_time" IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"';
359 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"';
360 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'')';
361 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''';
362 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''';
363 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
364 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
365 COMMENT ON COLUMN "policy"."direct_majority_num" IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner';
366 COMMENT ON COLUMN "policy"."direct_majority_den" IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner';
367 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.';
368 COMMENT ON COLUMN "policy"."direct_majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner';
369 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';
370 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';
371 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';
372 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.';
373 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';
374 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';
375 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.';
376 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").';
379 CREATE TABLE "unit" (
380 "id" SERIAL4 PRIMARY KEY,
381 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
382 "active" BOOLEAN NOT NULL DEFAULT TRUE,
383 "name" TEXT NOT NULL,
384 "description" TEXT NOT NULL DEFAULT '',
385 "member_count" INT4,
386 "text_search_data" TSVECTOR );
387 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
388 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
389 CREATE INDEX "unit_active_idx" ON "unit" ("active");
390 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
391 CREATE TRIGGER "update_text_search_data"
392 BEFORE INSERT OR UPDATE ON "unit"
393 FOR EACH ROW EXECUTE PROCEDURE
394 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
395 "name", "description" );
397 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
399 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
400 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in areas of this unit';
401 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
404 CREATE TABLE "unit_setting" (
405 PRIMARY KEY ("member_id", "key", "unit_id"),
406 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
407 "key" TEXT NOT NULL,
408 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
409 "value" TEXT NOT NULL );
411 COMMENT ON TABLE "unit_setting" IS 'Place for frontend to store unit specific settings of members as strings';
414 CREATE TABLE "area" (
415 "id" SERIAL4 PRIMARY KEY,
416 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
417 "active" BOOLEAN NOT NULL DEFAULT TRUE,
418 "name" TEXT NOT NULL,
419 "description" TEXT NOT NULL DEFAULT '',
420 "direct_member_count" INT4,
421 "member_weight" INT4,
422 "text_search_data" TSVECTOR );
423 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
424 CREATE INDEX "area_active_idx" ON "area" ("active");
425 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
426 CREATE TRIGGER "update_text_search_data"
427 BEFORE INSERT OR UPDATE ON "area"
428 FOR EACH ROW EXECUTE PROCEDURE
429 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
430 "name", "description" );
432 COMMENT ON TABLE "area" IS 'Subject areas';
434 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
435 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"';
436 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
439 CREATE TABLE "area_setting" (
440 PRIMARY KEY ("member_id", "key", "area_id"),
441 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
442 "key" TEXT NOT NULL,
443 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
444 "value" TEXT NOT NULL );
446 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
449 CREATE TABLE "allowed_policy" (
450 PRIMARY KEY ("area_id", "policy_id"),
451 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
452 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
453 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
454 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
456 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
458 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
461 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
463 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';
466 CREATE TYPE "issue_state" AS ENUM (
467 'admission', 'discussion', 'verification', 'voting',
468 'canceled_revoked_before_accepted',
469 'canceled_issue_not_accepted',
470 'canceled_after_revocation_during_discussion',
471 'canceled_after_revocation_during_verification',
472 'calculation',
473 'canceled_no_initiative_admitted',
474 'finished_without_winner', 'finished_with_winner');
476 COMMENT ON TYPE "issue_state" IS 'State of issues';
479 CREATE TABLE "issue" (
480 "id" SERIAL4 PRIMARY KEY,
481 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
482 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
483 "state" "issue_state" NOT NULL DEFAULT 'admission',
484 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
485 "accepted" TIMESTAMPTZ,
486 "half_frozen" TIMESTAMPTZ,
487 "fully_frozen" TIMESTAMPTZ,
488 "closed" TIMESTAMPTZ,
489 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
490 "cleaned" TIMESTAMPTZ,
491 "admission_time" INTERVAL NOT NULL,
492 "discussion_time" INTERVAL NOT NULL,
493 "verification_time" INTERVAL NOT NULL,
494 "voting_time" INTERVAL NOT NULL,
495 "snapshot" TIMESTAMPTZ,
496 "latest_snapshot_event" "snapshot_event",
497 "population" INT4,
498 "voter_count" INT4,
499 "status_quo_schulze_rank" INT4,
500 CONSTRAINT "valid_state" CHECK ((
501 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
502 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
503 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
504 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
505 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
506 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
507 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
508 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
509 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)) AND (
510 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
511 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
512 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
513 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
514 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
515 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
516 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
517 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
518 ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
519 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
520 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
521 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
522 )),
523 CONSTRAINT "state_change_order" CHECK (
524 "created" <= "accepted" AND
525 "accepted" <= "half_frozen" AND
526 "half_frozen" <= "fully_frozen" AND
527 "fully_frozen" <= "closed" ),
528 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
529 "cleaned" ISNULL OR "closed" NOTNULL ),
530 CONSTRAINT "last_snapshot_on_full_freeze"
531 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
532 CONSTRAINT "freeze_requires_snapshot"
533 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
534 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
535 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
536 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
537 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
538 CREATE INDEX "issue_created_idx" ON "issue" ("created");
539 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
540 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
541 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
542 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
543 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
544 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
546 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
548 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
549 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.';
550 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.';
551 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.';
552 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
553 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
554 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
555 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
556 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
557 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
558 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
559 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';
560 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
561 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';
562 COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
565 CREATE TABLE "issue_setting" (
566 PRIMARY KEY ("member_id", "key", "issue_id"),
567 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
568 "key" TEXT NOT NULL,
569 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
570 "value" TEXT NOT NULL );
572 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
575 CREATE TABLE "initiative" (
576 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
577 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
578 "id" SERIAL4 PRIMARY KEY,
579 "name" TEXT NOT NULL,
580 "discussion_url" TEXT,
581 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
582 "revoked" TIMESTAMPTZ,
583 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
584 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
585 "admitted" BOOLEAN,
586 "supporter_count" INT4,
587 "informed_supporter_count" INT4,
588 "satisfied_supporter_count" INT4,
589 "satisfied_informed_supporter_count" INT4,
590 "positive_votes" INT4,
591 "negative_votes" INT4,
592 "direct_majority" BOOLEAN,
593 "indirect_majority" BOOLEAN,
594 "schulze_rank" INT4,
595 "better_than_status_quo" BOOLEAN,
596 "worse_than_status_quo" BOOLEAN,
597 "reverse_beat_path" BOOLEAN,
598 "multistage_majority" BOOLEAN,
599 "eligible" BOOLEAN,
600 "winner" BOOLEAN,
601 "rank" INT4,
602 "text_search_data" TSVECTOR,
603 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
604 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL),
605 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
606 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
607 CONSTRAINT "revoked_initiatives_cant_be_admitted"
608 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
609 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
610 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
611 ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND
612 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
613 "schulze_rank" ISNULL AND
614 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
615 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
616 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ),
617 CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")),
618 CONSTRAINT "minimum_requirement_to_be_eligible" CHECK (
619 "eligible" = FALSE OR
620 ("direct_majority" AND "indirect_majority" AND "better_than_status_quo") ),
621 CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE),
622 CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1),
623 CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE),
624 CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") );
625 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
626 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
627 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
628 CREATE TRIGGER "update_text_search_data"
629 BEFORE INSERT OR UPDATE ON "initiative"
630 FOR EACH ROW EXECUTE PROCEDURE
631 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
632 "name", "discussion_url");
634 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.';
636 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
637 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
638 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoke the initiative';
639 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
640 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
641 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
642 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
643 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
644 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
645 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
646 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"';
647 COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths';
648 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking without tie-breaking';
649 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)';
650 COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)';
651 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';
652 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';
653 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"';
654 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"';
655 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';
658 CREATE TABLE "battle" (
659 "issue_id" INT4 NOT NULL,
660 "winning_initiative_id" INT4,
661 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
662 "losing_initiative_id" INT4,
663 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
664 "count" INT4 NOT NULL,
665 CONSTRAINT "initiative_ids_not_equal" CHECK (
666 "winning_initiative_id" != "losing_initiative_id" OR
667 ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
668 ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) );
669 CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
670 CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
671 CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
673 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';
676 CREATE TABLE "ignored_initiative" (
677 PRIMARY KEY ("initiative_id", "member_id"),
678 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
679 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
680 CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");
682 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
685 CREATE TABLE "initiative_setting" (
686 PRIMARY KEY ("member_id", "key", "initiative_id"),
687 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
688 "key" TEXT NOT NULL,
689 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
690 "value" TEXT NOT NULL );
692 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
695 CREATE TABLE "draft" (
696 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
697 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
698 "id" SERIAL8 PRIMARY KEY,
699 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
700 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
701 "formatting_engine" TEXT,
702 "content" TEXT NOT NULL,
703 "text_search_data" TSVECTOR );
704 CREATE INDEX "draft_created_idx" ON "draft" ("created");
705 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
706 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
707 CREATE TRIGGER "update_text_search_data"
708 BEFORE INSERT OR UPDATE ON "draft"
709 FOR EACH ROW EXECUTE PROCEDURE
710 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
712 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.';
714 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
715 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
718 CREATE TABLE "rendered_draft" (
719 PRIMARY KEY ("draft_id", "format"),
720 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
721 "format" TEXT,
722 "content" TEXT NOT NULL );
724 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)';
727 CREATE TABLE "suggestion" (
728 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
729 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
730 "id" SERIAL8 PRIMARY KEY,
731 "draft_id" INT8 NOT NULL,
732 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
733 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
734 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
735 "name" TEXT NOT NULL,
736 "formatting_engine" TEXT,
737 "content" TEXT NOT NULL DEFAULT '',
738 "text_search_data" TSVECTOR,
739 "minus2_unfulfilled_count" INT4,
740 "minus2_fulfilled_count" INT4,
741 "minus1_unfulfilled_count" INT4,
742 "minus1_fulfilled_count" INT4,
743 "plus1_unfulfilled_count" INT4,
744 "plus1_fulfilled_count" INT4,
745 "plus2_unfulfilled_count" INT4,
746 "plus2_fulfilled_count" INT4 );
747 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
748 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
749 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
750 CREATE TRIGGER "update_text_search_data"
751 BEFORE INSERT OR UPDATE ON "suggestion"
752 FOR EACH ROW EXECUTE PROCEDURE
753 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
754 "name", "content");
756 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';
758 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")';
759 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
760 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
761 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
762 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
763 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
764 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
765 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
766 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
769 CREATE TABLE "rendered_suggestion" (
770 PRIMARY KEY ("suggestion_id", "format"),
771 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
772 "format" TEXT,
773 "content" TEXT NOT NULL );
775 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)';
778 CREATE TABLE "suggestion_setting" (
779 PRIMARY KEY ("member_id", "key", "suggestion_id"),
780 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
781 "key" TEXT NOT NULL,
782 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
783 "value" TEXT NOT NULL );
785 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
788 CREATE TABLE "privilege" (
789 PRIMARY KEY ("unit_id", "member_id"),
790 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
791 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
792 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
793 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
794 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
795 "voting_right_manager" BOOLEAN NOT NULL DEFAULT FALSE,
796 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE );
798 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
800 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke admin privileges to/from other members';
801 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create and disable sub units';
802 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create and disable areas and set area parameters';
803 COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote within the unit';
804 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to discuss and vote';
807 CREATE TABLE "membership" (
808 PRIMARY KEY ("area_id", "member_id"),
809 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
810 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
811 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
813 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
816 CREATE TABLE "interest" (
817 PRIMARY KEY ("issue_id", "member_id"),
818 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
819 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
820 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
822 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.';
825 CREATE TABLE "initiator" (
826 PRIMARY KEY ("initiative_id", "member_id"),
827 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
828 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
829 "accepted" BOOLEAN );
830 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
832 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.';
834 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.';
837 CREATE TABLE "supporter" (
838 "issue_id" INT4 NOT NULL,
839 PRIMARY KEY ("initiative_id", "member_id"),
840 "initiative_id" INT4,
841 "member_id" INT4,
842 "draft_id" INT8 NOT NULL,
843 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
844 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE );
845 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
847 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.';
849 COMMENT ON COLUMN "supporter"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
850 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")';
853 CREATE TABLE "opinion" (
854 "initiative_id" INT4 NOT NULL,
855 PRIMARY KEY ("suggestion_id", "member_id"),
856 "suggestion_id" INT8,
857 "member_id" INT4,
858 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
859 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
860 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
861 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
862 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
864 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.';
866 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
869 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
871 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
874 CREATE TABLE "delegation" (
875 "id" SERIAL8 PRIMARY KEY,
876 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
877 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
878 "scope" "delegation_scope" NOT NULL,
879 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
880 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
881 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
882 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
883 CONSTRAINT "no_unit_delegation_to_null"
884 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
885 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
886 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
887 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
888 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
889 UNIQUE ("unit_id", "truster_id"),
890 UNIQUE ("area_id", "truster_id"),
891 UNIQUE ("issue_id", "truster_id") );
892 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
893 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
895 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
897 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
898 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
899 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
902 CREATE TABLE "direct_population_snapshot" (
903 PRIMARY KEY ("issue_id", "event", "member_id"),
904 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
905 "event" "snapshot_event",
906 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
907 "weight" INT4 );
908 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
910 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
912 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
913 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
916 CREATE TABLE "delegating_population_snapshot" (
917 PRIMARY KEY ("issue_id", "event", "member_id"),
918 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
919 "event" "snapshot_event",
920 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
921 "weight" INT4,
922 "scope" "delegation_scope" NOT NULL,
923 "delegate_member_ids" INT4[] NOT NULL );
924 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
926 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
928 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
929 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
930 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
931 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"';
934 CREATE TABLE "direct_interest_snapshot" (
935 PRIMARY KEY ("issue_id", "event", "member_id"),
936 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
937 "event" "snapshot_event",
938 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
939 "weight" INT4 );
940 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
942 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
944 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
945 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
948 CREATE TABLE "delegating_interest_snapshot" (
949 PRIMARY KEY ("issue_id", "event", "member_id"),
950 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
951 "event" "snapshot_event",
952 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
953 "weight" INT4,
954 "scope" "delegation_scope" NOT NULL,
955 "delegate_member_ids" INT4[] NOT NULL );
956 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
958 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
960 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
961 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
962 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
963 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"';
966 CREATE TABLE "direct_supporter_snapshot" (
967 "issue_id" INT4 NOT NULL,
968 PRIMARY KEY ("initiative_id", "event", "member_id"),
969 "initiative_id" INT4,
970 "event" "snapshot_event",
971 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
972 "draft_id" INT8 NOT NULL,
973 "informed" BOOLEAN NOT NULL,
974 "satisfied" BOOLEAN NOT NULL,
975 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
976 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
977 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
978 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
980 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
982 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';
983 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
984 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
985 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
988 CREATE TABLE "non_voter" (
989 PRIMARY KEY ("issue_id", "member_id"),
990 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
991 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
992 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
994 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
997 CREATE TABLE "direct_voter" (
998 PRIMARY KEY ("issue_id", "member_id"),
999 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1000 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1001 "weight" INT4 );
1002 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
1004 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.';
1006 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
1009 CREATE TABLE "delegating_voter" (
1010 PRIMARY KEY ("issue_id", "member_id"),
1011 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1012 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1013 "weight" INT4,
1014 "scope" "delegation_scope" NOT NULL,
1015 "delegate_member_ids" INT4[] NOT NULL );
1016 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
1018 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
1020 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
1021 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
1022 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"';
1025 CREATE TABLE "vote" (
1026 "issue_id" INT4 NOT NULL,
1027 PRIMARY KEY ("initiative_id", "member_id"),
1028 "initiative_id" INT4,
1029 "member_id" INT4,
1030 "grade" INT4,
1031 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1032 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1033 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
1035 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.';
1037 COMMENT ON COLUMN "vote"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
1038 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.';
1041 CREATE TABLE "voting_comment" (
1042 PRIMARY KEY ("issue_id", "member_id"),
1043 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1044 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1045 "changed" TIMESTAMPTZ,
1046 "formatting_engine" TEXT,
1047 "content" TEXT NOT NULL,
1048 "text_search_data" TSVECTOR );
1049 CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id");
1050 CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data");
1051 CREATE TRIGGER "update_text_search_data"
1052 BEFORE INSERT OR UPDATE ON "voting_comment"
1053 FOR EACH ROW EXECUTE PROCEDURE
1054 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
1056 COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.';
1058 COMMENT ON COLUMN "voting_comment"."changed" 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.';
1061 CREATE TABLE "rendered_voting_comment" (
1062 PRIMARY KEY ("issue_id", "member_id", "format"),
1063 FOREIGN KEY ("issue_id", "member_id")
1064 REFERENCES "voting_comment" ("issue_id", "member_id")
1065 ON DELETE CASCADE ON UPDATE CASCADE,
1066 "issue_id" INT4,
1067 "member_id" INT4,
1068 "format" TEXT,
1069 "content" TEXT NOT NULL );
1071 COMMENT ON TABLE "rendered_voting_comment" IS 'This table may be used by frontends to cache "rendered" voting comments (e.g. HTML output generated from wiki text)';
1074 CREATE TYPE "event_type" AS ENUM (
1075 'issue_state_changed',
1076 'initiative_created_in_new_issue',
1077 'initiative_created_in_existing_issue',
1078 'initiative_revoked',
1079 'new_draft_created',
1080 'suggestion_created');
1082 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
1085 CREATE TABLE "event" (
1086 "id" SERIAL8 PRIMARY KEY,
1087 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
1088 "event" "event_type" NOT NULL,
1089 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1090 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1091 "state" "issue_state" CHECK ("state" != 'calculation'),
1092 "initiative_id" INT4,
1093 "draft_id" INT8,
1094 "suggestion_id" INT8,
1095 FOREIGN KEY ("issue_id", "initiative_id")
1096 REFERENCES "initiative" ("issue_id", "id")
1097 ON DELETE CASCADE ON UPDATE CASCADE,
1098 FOREIGN KEY ("initiative_id", "draft_id")
1099 REFERENCES "draft" ("initiative_id", "id")
1100 ON DELETE CASCADE ON UPDATE CASCADE,
1101 FOREIGN KEY ("initiative_id", "suggestion_id")
1102 REFERENCES "suggestion" ("initiative_id", "id")
1103 ON DELETE CASCADE ON UPDATE CASCADE,
1104 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
1105 "event" != 'issue_state_changed' OR (
1106 "member_id" ISNULL AND
1107 "issue_id" NOTNULL AND
1108 "state" NOTNULL AND
1109 "initiative_id" ISNULL AND
1110 "draft_id" ISNULL AND
1111 "suggestion_id" ISNULL )),
1112 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
1113 "event" NOT IN (
1114 'initiative_created_in_new_issue',
1115 'initiative_created_in_existing_issue',
1116 'initiative_revoked',
1117 'new_draft_created'
1118 ) OR (
1119 "member_id" NOTNULL AND
1120 "issue_id" NOTNULL AND
1121 "state" NOTNULL AND
1122 "initiative_id" NOTNULL AND
1123 "draft_id" NOTNULL AND
1124 "suggestion_id" ISNULL )),
1125 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
1126 "event" != 'suggestion_created' OR (
1127 "member_id" NOTNULL AND
1128 "issue_id" NOTNULL AND
1129 "state" NOTNULL AND
1130 "initiative_id" NOTNULL AND
1131 "draft_id" ISNULL AND
1132 "suggestion_id" NOTNULL )) );
1133 CREATE INDEX "event_occurrence_idx" ON "event" ("occurrence");
1135 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
1137 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
1138 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';
1139 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';
1140 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';
1143 CREATE TABLE "notification_sent" (
1144 "event_id" INT8 NOT NULL );
1145 CREATE UNIQUE INDEX "notification_sent_singleton_idx" ON "notification_sent" ((1));
1147 COMMENT ON TABLE "notification_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out';
1148 COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.';
1152 ----------------------------------------------
1153 -- Writing of history entries and event log --
1154 ----------------------------------------------
1157 CREATE FUNCTION "write_member_history_trigger"()
1158 RETURNS TRIGGER
1159 LANGUAGE 'plpgsql' VOLATILE AS $$
1160 BEGIN
1161 IF
1162 ( NEW."active" != OLD."active" OR
1163 NEW."name" != OLD."name" ) AND
1164 OLD."activated" NOTNULL
1165 THEN
1166 INSERT INTO "member_history"
1167 ("member_id", "active", "name")
1168 VALUES (NEW."id", OLD."active", OLD."name");
1169 END IF;
1170 RETURN NULL;
1171 END;
1172 $$;
1174 CREATE TRIGGER "write_member_history"
1175 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1176 "write_member_history_trigger"();
1178 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
1179 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
1182 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
1183 RETURNS TRIGGER
1184 LANGUAGE 'plpgsql' VOLATILE AS $$
1185 BEGIN
1186 IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN
1187 INSERT INTO "event" ("event", "issue_id", "state")
1188 VALUES ('issue_state_changed', NEW."id", NEW."state");
1189 END IF;
1190 RETURN NULL;
1191 END;
1192 $$;
1194 CREATE TRIGGER "write_event_issue_state_changed"
1195 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
1196 "write_event_issue_state_changed_trigger"();
1198 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
1199 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
1202 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1203 RETURNS TRIGGER
1204 LANGUAGE 'plpgsql' VOLATILE AS $$
1205 DECLARE
1206 "initiative_row" "initiative"%ROWTYPE;
1207 "issue_row" "issue"%ROWTYPE;
1208 "event_v" "event_type";
1209 BEGIN
1210 SELECT * INTO "initiative_row" FROM "initiative"
1211 WHERE "id" = NEW."initiative_id";
1212 SELECT * INTO "issue_row" FROM "issue"
1213 WHERE "id" = "initiative_row"."issue_id";
1214 IF EXISTS (
1215 SELECT NULL FROM "draft"
1216 WHERE "initiative_id" = NEW."initiative_id"
1217 AND "id" != NEW."id"
1218 ) THEN
1219 "event_v" := 'new_draft_created';
1220 ELSE
1221 IF EXISTS (
1222 SELECT NULL FROM "initiative"
1223 WHERE "issue_id" = "initiative_row"."issue_id"
1224 AND "id" != "initiative_row"."id"
1225 ) THEN
1226 "event_v" := 'initiative_created_in_existing_issue';
1227 ELSE
1228 "event_v" := 'initiative_created_in_new_issue';
1229 END IF;
1230 END IF;
1231 INSERT INTO "event" (
1232 "event", "member_id",
1233 "issue_id", "state", "initiative_id", "draft_id"
1234 ) VALUES (
1235 "event_v",
1236 NEW."author_id",
1237 "initiative_row"."issue_id",
1238 "issue_row"."state",
1239 "initiative_row"."id",
1240 NEW."id" );
1241 RETURN NULL;
1242 END;
1243 $$;
1245 CREATE TRIGGER "write_event_initiative_or_draft_created"
1246 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
1247 "write_event_initiative_or_draft_created_trigger"();
1249 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
1250 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
1253 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
1254 RETURNS TRIGGER
1255 LANGUAGE 'plpgsql' VOLATILE AS $$
1256 DECLARE
1257 "issue_row" "issue"%ROWTYPE;
1258 "draft_id_v" "draft"."id"%TYPE;
1259 BEGIN
1260 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1261 SELECT * INTO "issue_row" FROM "issue"
1262 WHERE "id" = NEW."issue_id";
1263 SELECT "id" INTO "draft_id_v" FROM "current_draft"
1264 WHERE "initiative_id" = NEW."id";
1265 INSERT INTO "event" (
1266 "event", "member_id", "issue_id", "state", "initiative_id", "draft_id"
1267 ) VALUES (
1268 'initiative_revoked',
1269 NEW."revoked_by_member_id",
1270 NEW."issue_id",
1271 "issue_row"."state",
1272 NEW."id",
1273 "draft_id_v");
1274 END IF;
1275 RETURN NULL;
1276 END;
1277 $$;
1279 CREATE TRIGGER "write_event_initiative_revoked"
1280 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
1281 "write_event_initiative_revoked_trigger"();
1283 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
1284 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
1287 CREATE FUNCTION "write_event_suggestion_created_trigger"()
1288 RETURNS TRIGGER
1289 LANGUAGE 'plpgsql' VOLATILE AS $$
1290 DECLARE
1291 "initiative_row" "initiative"%ROWTYPE;
1292 "issue_row" "issue"%ROWTYPE;
1293 BEGIN
1294 SELECT * INTO "initiative_row" FROM "initiative"
1295 WHERE "id" = NEW."initiative_id";
1296 SELECT * INTO "issue_row" FROM "issue"
1297 WHERE "id" = "initiative_row"."issue_id";
1298 INSERT INTO "event" (
1299 "event", "member_id",
1300 "issue_id", "state", "initiative_id", "suggestion_id"
1301 ) VALUES (
1302 'suggestion_created',
1303 NEW."author_id",
1304 "initiative_row"."issue_id",
1305 "issue_row"."state",
1306 "initiative_row"."id",
1307 NEW."id" );
1308 RETURN NULL;
1309 END;
1310 $$;
1312 CREATE TRIGGER "write_event_suggestion_created"
1313 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1314 "write_event_suggestion_created_trigger"();
1316 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
1317 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1321 ----------------------------
1322 -- Additional constraints --
1323 ----------------------------
1326 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
1327 RETURNS TRIGGER
1328 LANGUAGE 'plpgsql' VOLATILE AS $$
1329 BEGIN
1330 IF NOT EXISTS (
1331 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
1332 ) THEN
1333 --RAISE 'Cannot create issue without an initial initiative.' USING
1334 -- ERRCODE = 'integrity_constraint_violation',
1335 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
1336 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
1337 END IF;
1338 RETURN NULL;
1339 END;
1340 $$;
1342 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
1343 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
1344 FOR EACH ROW EXECUTE PROCEDURE
1345 "issue_requires_first_initiative_trigger"();
1347 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
1348 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
1351 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
1352 RETURNS TRIGGER
1353 LANGUAGE 'plpgsql' VOLATILE AS $$
1354 DECLARE
1355 "reference_lost" BOOLEAN;
1356 BEGIN
1357 IF TG_OP = 'DELETE' THEN
1358 "reference_lost" := TRUE;
1359 ELSE
1360 "reference_lost" := NEW."issue_id" != OLD."issue_id";
1361 END IF;
1362 IF
1363 "reference_lost" AND NOT EXISTS (
1364 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
1365 )
1366 THEN
1367 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
1368 END IF;
1369 RETURN NULL;
1370 END;
1371 $$;
1373 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
1374 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1375 FOR EACH ROW EXECUTE PROCEDURE
1376 "last_initiative_deletes_issue_trigger"();
1378 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
1379 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
1382 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
1383 RETURNS TRIGGER
1384 LANGUAGE 'plpgsql' VOLATILE AS $$
1385 BEGIN
1386 IF NOT EXISTS (
1387 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
1388 ) THEN
1389 --RAISE 'Cannot create initiative without an initial draft.' USING
1390 -- ERRCODE = 'integrity_constraint_violation',
1391 -- HINT = 'Create issue, initiative and draft within the same transaction.';
1392 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
1393 END IF;
1394 RETURN NULL;
1395 END;
1396 $$;
1398 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
1399 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1400 FOR EACH ROW EXECUTE PROCEDURE
1401 "initiative_requires_first_draft_trigger"();
1403 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
1404 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
1407 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
1408 RETURNS TRIGGER
1409 LANGUAGE 'plpgsql' VOLATILE AS $$
1410 DECLARE
1411 "reference_lost" BOOLEAN;
1412 BEGIN
1413 IF TG_OP = 'DELETE' THEN
1414 "reference_lost" := TRUE;
1415 ELSE
1416 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
1417 END IF;
1418 IF
1419 "reference_lost" AND NOT EXISTS (
1420 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
1421 )
1422 THEN
1423 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
1424 END IF;
1425 RETURN NULL;
1426 END;
1427 $$;
1429 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
1430 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
1431 FOR EACH ROW EXECUTE PROCEDURE
1432 "last_draft_deletes_initiative_trigger"();
1434 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
1435 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
1438 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
1439 RETURNS TRIGGER
1440 LANGUAGE 'plpgsql' VOLATILE AS $$
1441 BEGIN
1442 IF NOT EXISTS (
1443 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
1444 ) THEN
1445 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
1446 END IF;
1447 RETURN NULL;
1448 END;
1449 $$;
1451 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
1452 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
1453 FOR EACH ROW EXECUTE PROCEDURE
1454 "suggestion_requires_first_opinion_trigger"();
1456 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1457 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1460 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1461 RETURNS TRIGGER
1462 LANGUAGE 'plpgsql' VOLATILE AS $$
1463 DECLARE
1464 "reference_lost" BOOLEAN;
1465 BEGIN
1466 IF TG_OP = 'DELETE' THEN
1467 "reference_lost" := TRUE;
1468 ELSE
1469 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1470 END IF;
1471 IF
1472 "reference_lost" AND NOT EXISTS (
1473 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1474 )
1475 THEN
1476 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1477 END IF;
1478 RETURN NULL;
1479 END;
1480 $$;
1482 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1483 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1484 FOR EACH ROW EXECUTE PROCEDURE
1485 "last_opinion_deletes_suggestion_trigger"();
1487 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1488 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1492 ---------------------------------------------------------------
1493 -- Ensure that votes are not modified when issues are frozen --
1494 ---------------------------------------------------------------
1496 -- NOTE: Frontends should ensure this anyway, but in case of programming
1497 -- errors the following triggers ensure data integrity.
1500 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1501 RETURNS TRIGGER
1502 LANGUAGE 'plpgsql' VOLATILE AS $$
1503 DECLARE
1504 "issue_id_v" "issue"."id"%TYPE;
1505 "issue_row" "issue"%ROWTYPE;
1506 BEGIN
1507 IF TG_OP = 'DELETE' THEN
1508 "issue_id_v" := OLD."issue_id";
1509 ELSE
1510 "issue_id_v" := NEW."issue_id";
1511 END IF;
1512 SELECT INTO "issue_row" * FROM "issue"
1513 WHERE "id" = "issue_id_v" FOR SHARE;
1514 IF "issue_row"."closed" NOTNULL THEN
1515 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
1516 END IF;
1517 RETURN NULL;
1518 END;
1519 $$;
1521 CREATE TRIGGER "forbid_changes_on_closed_issue"
1522 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1523 FOR EACH ROW EXECUTE PROCEDURE
1524 "forbid_changes_on_closed_issue_trigger"();
1526 CREATE TRIGGER "forbid_changes_on_closed_issue"
1527 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1528 FOR EACH ROW EXECUTE PROCEDURE
1529 "forbid_changes_on_closed_issue_trigger"();
1531 CREATE TRIGGER "forbid_changes_on_closed_issue"
1532 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1533 FOR EACH ROW EXECUTE PROCEDURE
1534 "forbid_changes_on_closed_issue_trigger"();
1536 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"';
1537 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';
1538 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';
1539 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';
1543 --------------------------------------------------------------------
1544 -- Auto-retrieval of fields only needed for referential integrity --
1545 --------------------------------------------------------------------
1548 CREATE FUNCTION "autofill_issue_id_trigger"()
1549 RETURNS TRIGGER
1550 LANGUAGE 'plpgsql' VOLATILE AS $$
1551 BEGIN
1552 IF NEW."issue_id" ISNULL THEN
1553 SELECT "issue_id" INTO NEW."issue_id"
1554 FROM "initiative" WHERE "id" = NEW."initiative_id";
1555 END IF;
1556 RETURN NEW;
1557 END;
1558 $$;
1560 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1561 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1563 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1564 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1566 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1567 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1568 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1571 CREATE FUNCTION "autofill_initiative_id_trigger"()
1572 RETURNS TRIGGER
1573 LANGUAGE 'plpgsql' VOLATILE AS $$
1574 BEGIN
1575 IF NEW."initiative_id" ISNULL THEN
1576 SELECT "initiative_id" INTO NEW."initiative_id"
1577 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1578 END IF;
1579 RETURN NEW;
1580 END;
1581 $$;
1583 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1584 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1586 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1587 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1591 -----------------------------------------------------
1592 -- Automatic calculation of certain default values --
1593 -----------------------------------------------------
1596 CREATE FUNCTION "copy_timings_trigger"()
1597 RETURNS TRIGGER
1598 LANGUAGE 'plpgsql' VOLATILE AS $$
1599 DECLARE
1600 "policy_row" "policy"%ROWTYPE;
1601 BEGIN
1602 SELECT * INTO "policy_row" FROM "policy"
1603 WHERE "id" = NEW."policy_id";
1604 IF NEW."admission_time" ISNULL THEN
1605 NEW."admission_time" := "policy_row"."admission_time";
1606 END IF;
1607 IF NEW."discussion_time" ISNULL THEN
1608 NEW."discussion_time" := "policy_row"."discussion_time";
1609 END IF;
1610 IF NEW."verification_time" ISNULL THEN
1611 NEW."verification_time" := "policy_row"."verification_time";
1612 END IF;
1613 IF NEW."voting_time" ISNULL THEN
1614 NEW."voting_time" := "policy_row"."voting_time";
1615 END IF;
1616 RETURN NEW;
1617 END;
1618 $$;
1620 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1621 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1623 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1624 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1627 CREATE FUNCTION "default_for_draft_id_trigger"()
1628 RETURNS TRIGGER
1629 LANGUAGE 'plpgsql' VOLATILE AS $$
1630 BEGIN
1631 IF NEW."draft_id" ISNULL THEN
1632 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1633 WHERE "initiative_id" = NEW."initiative_id";
1634 END IF;
1635 RETURN NEW;
1636 END;
1637 $$;
1639 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"
1640 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1641 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1642 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1644 COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';
1645 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';
1646 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';
1650 ----------------------------------------
1651 -- Automatic creation of dependencies --
1652 ----------------------------------------
1655 CREATE FUNCTION "autocreate_interest_trigger"()
1656 RETURNS TRIGGER
1657 LANGUAGE 'plpgsql' VOLATILE AS $$
1658 BEGIN
1659 IF NOT EXISTS (
1660 SELECT NULL FROM "initiative" JOIN "interest"
1661 ON "initiative"."issue_id" = "interest"."issue_id"
1662 WHERE "initiative"."id" = NEW."initiative_id"
1663 AND "interest"."member_id" = NEW."member_id"
1664 ) THEN
1665 BEGIN
1666 INSERT INTO "interest" ("issue_id", "member_id")
1667 SELECT "issue_id", NEW."member_id"
1668 FROM "initiative" WHERE "id" = NEW."initiative_id";
1669 EXCEPTION WHEN unique_violation THEN END;
1670 END IF;
1671 RETURN NEW;
1672 END;
1673 $$;
1675 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1676 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1678 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1679 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';
1682 CREATE FUNCTION "autocreate_supporter_trigger"()
1683 RETURNS TRIGGER
1684 LANGUAGE 'plpgsql' VOLATILE AS $$
1685 BEGIN
1686 IF NOT EXISTS (
1687 SELECT NULL FROM "suggestion" JOIN "supporter"
1688 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1689 WHERE "suggestion"."id" = NEW."suggestion_id"
1690 AND "supporter"."member_id" = NEW."member_id"
1691 ) THEN
1692 BEGIN
1693 INSERT INTO "supporter" ("initiative_id", "member_id")
1694 SELECT "initiative_id", NEW."member_id"
1695 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1696 EXCEPTION WHEN unique_violation THEN END;
1697 END IF;
1698 RETURN NEW;
1699 END;
1700 $$;
1702 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1703 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1705 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1706 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.';
1710 ------------------------------------------
1711 -- Views and helper functions for views --
1712 ------------------------------------------
1715 CREATE VIEW "unit_delegation" AS
1716 SELECT
1717 "unit"."id" AS "unit_id",
1718 "delegation"."id",
1719 "delegation"."truster_id",
1720 "delegation"."trustee_id",
1721 "delegation"."scope"
1722 FROM "unit"
1723 JOIN "delegation"
1724 ON "delegation"."unit_id" = "unit"."id"
1725 JOIN "member"
1726 ON "delegation"."truster_id" = "member"."id"
1727 JOIN "privilege"
1728 ON "delegation"."unit_id" = "privilege"."unit_id"
1729 AND "delegation"."truster_id" = "privilege"."member_id"
1730 WHERE "member"."active" AND "privilege"."voting_right";
1732 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
1735 CREATE VIEW "area_delegation" AS
1736 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1737 "area"."id" AS "area_id",
1738 "delegation"."id",
1739 "delegation"."truster_id",
1740 "delegation"."trustee_id",
1741 "delegation"."scope"
1742 FROM "area"
1743 JOIN "delegation"
1744 ON "delegation"."unit_id" = "area"."unit_id"
1745 OR "delegation"."area_id" = "area"."id"
1746 JOIN "member"
1747 ON "delegation"."truster_id" = "member"."id"
1748 JOIN "privilege"
1749 ON "area"."unit_id" = "privilege"."unit_id"
1750 AND "delegation"."truster_id" = "privilege"."member_id"
1751 WHERE "member"."active" AND "privilege"."voting_right"
1752 ORDER BY
1753 "area"."id",
1754 "delegation"."truster_id",
1755 "delegation"."scope" DESC;
1757 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
1760 CREATE VIEW "issue_delegation" AS
1761 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1762 "issue"."id" AS "issue_id",
1763 "delegation"."id",
1764 "delegation"."truster_id",
1765 "delegation"."trustee_id",
1766 "delegation"."scope"
1767 FROM "issue"
1768 JOIN "area"
1769 ON "area"."id" = "issue"."area_id"
1770 JOIN "delegation"
1771 ON "delegation"."unit_id" = "area"."unit_id"
1772 OR "delegation"."area_id" = "area"."id"
1773 OR "delegation"."issue_id" = "issue"."id"
1774 JOIN "member"
1775 ON "delegation"."truster_id" = "member"."id"
1776 JOIN "privilege"
1777 ON "area"."unit_id" = "privilege"."unit_id"
1778 AND "delegation"."truster_id" = "privilege"."member_id"
1779 WHERE "member"."active" AND "privilege"."voting_right"
1780 ORDER BY
1781 "issue"."id",
1782 "delegation"."truster_id",
1783 "delegation"."scope" DESC;
1785 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
1788 CREATE FUNCTION "membership_weight_with_skipping"
1789 ( "area_id_p" "area"."id"%TYPE,
1790 "member_id_p" "member"."id"%TYPE,
1791 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1792 RETURNS INT4
1793 LANGUAGE 'plpgsql' STABLE AS $$
1794 DECLARE
1795 "sum_v" INT4;
1796 "delegation_row" "area_delegation"%ROWTYPE;
1797 BEGIN
1798 "sum_v" := 1;
1799 FOR "delegation_row" IN
1800 SELECT "area_delegation".*
1801 FROM "area_delegation" LEFT JOIN "membership"
1802 ON "membership"."area_id" = "area_id_p"
1803 AND "membership"."member_id" = "area_delegation"."truster_id"
1804 WHERE "area_delegation"."area_id" = "area_id_p"
1805 AND "area_delegation"."trustee_id" = "member_id_p"
1806 AND "membership"."member_id" ISNULL
1807 LOOP
1808 IF NOT
1809 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1810 THEN
1811 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1812 "area_id_p",
1813 "delegation_row"."truster_id",
1814 "skip_member_ids_p" || "delegation_row"."truster_id"
1815 );
1816 END IF;
1817 END LOOP;
1818 RETURN "sum_v";
1819 END;
1820 $$;
1822 COMMENT ON FUNCTION "membership_weight_with_skipping"
1823 ( "area"."id"%TYPE,
1824 "member"."id"%TYPE,
1825 INT4[] )
1826 IS 'Helper function for "membership_weight" function';
1829 CREATE FUNCTION "membership_weight"
1830 ( "area_id_p" "area"."id"%TYPE,
1831 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1832 RETURNS INT4
1833 LANGUAGE 'plpgsql' STABLE AS $$
1834 BEGIN
1835 RETURN "membership_weight_with_skipping"(
1836 "area_id_p",
1837 "member_id_p",
1838 ARRAY["member_id_p"]
1839 );
1840 END;
1841 $$;
1843 COMMENT ON FUNCTION "membership_weight"
1844 ( "area"."id"%TYPE,
1845 "member"."id"%TYPE )
1846 IS 'Calculates the potential voting weight of a member in a given area';
1849 CREATE VIEW "member_count_view" AS
1850 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1852 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1855 CREATE VIEW "unit_member_count" AS
1856 SELECT
1857 "unit"."id" AS "unit_id",
1858 count("member"."id") AS "member_count"
1859 FROM "unit"
1860 LEFT JOIN "privilege"
1861 ON "privilege"."unit_id" = "unit"."id"
1862 AND "privilege"."voting_right"
1863 LEFT JOIN "member"
1864 ON "member"."id" = "privilege"."member_id"
1865 AND "member"."active"
1866 GROUP BY "unit"."id";
1868 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
1871 CREATE VIEW "area_member_count" AS
1872 SELECT
1873 "area"."id" AS "area_id",
1874 count("member"."id") AS "direct_member_count",
1875 coalesce(
1876 sum(
1877 CASE WHEN "member"."id" NOTNULL THEN
1878 "membership_weight"("area"."id", "member"."id")
1879 ELSE 0 END
1880 )
1881 ) AS "member_weight"
1882 FROM "area"
1883 LEFT JOIN "membership"
1884 ON "area"."id" = "membership"."area_id"
1885 LEFT JOIN "privilege"
1886 ON "privilege"."unit_id" = "area"."unit_id"
1887 AND "privilege"."member_id" = "membership"."member_id"
1888 AND "privilege"."voting_right"
1889 LEFT JOIN "member"
1890 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
1891 AND "member"."active"
1892 GROUP BY "area"."id";
1894 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"';
1897 CREATE VIEW "opening_draft" AS
1898 SELECT "draft".* FROM (
1899 SELECT
1900 "initiative"."id" AS "initiative_id",
1901 min("draft"."id") AS "draft_id"
1902 FROM "initiative" JOIN "draft"
1903 ON "initiative"."id" = "draft"."initiative_id"
1904 GROUP BY "initiative"."id"
1905 ) AS "subquery"
1906 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1908 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1911 CREATE VIEW "current_draft" AS
1912 SELECT "draft".* FROM (
1913 SELECT
1914 "initiative"."id" AS "initiative_id",
1915 max("draft"."id") AS "draft_id"
1916 FROM "initiative" JOIN "draft"
1917 ON "initiative"."id" = "draft"."initiative_id"
1918 GROUP BY "initiative"."id"
1919 ) AS "subquery"
1920 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1922 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1925 CREATE VIEW "critical_opinion" AS
1926 SELECT * FROM "opinion"
1927 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
1928 OR ("degree" = -2 AND "fulfilled" = TRUE);
1930 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
1933 CREATE VIEW "battle_participant" AS
1934 SELECT "initiative"."id", "initiative"."issue_id"
1935 FROM "issue" JOIN "initiative"
1936 ON "issue"."id" = "initiative"."issue_id"
1937 WHERE "initiative"."admitted"
1938 UNION ALL
1939 SELECT NULL, "id" AS "issue_id"
1940 FROM "issue";
1942 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
1945 CREATE VIEW "battle_view" AS
1946 SELECT
1947 "issue"."id" AS "issue_id",
1948 "winning_initiative"."id" AS "winning_initiative_id",
1949 "losing_initiative"."id" AS "losing_initiative_id",
1950 sum(
1951 CASE WHEN
1952 coalesce("better_vote"."grade", 0) >
1953 coalesce("worse_vote"."grade", 0)
1954 THEN "direct_voter"."weight" ELSE 0 END
1955 ) AS "count"
1956 FROM "issue"
1957 LEFT JOIN "direct_voter"
1958 ON "issue"."id" = "direct_voter"."issue_id"
1959 JOIN "battle_participant" AS "winning_initiative"
1960 ON "issue"."id" = "winning_initiative"."issue_id"
1961 JOIN "battle_participant" AS "losing_initiative"
1962 ON "issue"."id" = "losing_initiative"."issue_id"
1963 LEFT JOIN "vote" AS "better_vote"
1964 ON "direct_voter"."member_id" = "better_vote"."member_id"
1965 AND "winning_initiative"."id" = "better_vote"."initiative_id"
1966 LEFT JOIN "vote" AS "worse_vote"
1967 ON "direct_voter"."member_id" = "worse_vote"."member_id"
1968 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
1969 WHERE "issue"."closed" NOTNULL
1970 AND "issue"."cleaned" ISNULL
1971 AND (
1972 "winning_initiative"."id" != "losing_initiative"."id" OR
1973 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
1974 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
1975 GROUP BY
1976 "issue"."id",
1977 "winning_initiative"."id",
1978 "losing_initiative"."id";
1980 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';
1983 CREATE VIEW "expired_session" AS
1984 SELECT * FROM "session" WHERE now() > "expiry";
1986 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
1987 DELETE FROM "session" WHERE "ident" = OLD."ident";
1989 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
1990 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
1993 CREATE VIEW "open_issue" AS
1994 SELECT * FROM "issue" WHERE "closed" ISNULL;
1996 COMMENT ON VIEW "open_issue" IS 'All open issues';
1999 CREATE VIEW "issue_with_ranks_missing" AS
2000 SELECT * FROM "issue"
2001 WHERE "fully_frozen" NOTNULL
2002 AND "closed" NOTNULL
2003 AND "ranks_available" = FALSE;
2005 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
2008 CREATE VIEW "member_contingent" AS
2009 SELECT
2010 "member"."id" AS "member_id",
2011 "contingent"."time_frame",
2012 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
2013 (
2014 SELECT count(1) FROM "draft"
2015 WHERE "draft"."author_id" = "member"."id"
2016 AND "draft"."created" > now() - "contingent"."time_frame"
2017 ) + (
2018 SELECT count(1) FROM "suggestion"
2019 WHERE "suggestion"."author_id" = "member"."id"
2020 AND "suggestion"."created" > now() - "contingent"."time_frame"
2021 )
2022 ELSE NULL END AS "text_entry_count",
2023 "contingent"."text_entry_limit",
2024 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
2025 SELECT count(1) FROM "opening_draft"
2026 WHERE "opening_draft"."author_id" = "member"."id"
2027 AND "opening_draft"."created" > now() - "contingent"."time_frame"
2028 ) ELSE NULL END AS "initiative_count",
2029 "contingent"."initiative_limit"
2030 FROM "member" CROSS JOIN "contingent";
2032 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
2034 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
2035 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
2038 CREATE VIEW "member_contingent_left" AS
2039 SELECT
2040 "member_id",
2041 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
2042 max("initiative_limit" - "initiative_count") AS "initiatives_left"
2043 FROM "member_contingent" GROUP BY "member_id";
2045 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.';
2048 CREATE VIEW "event_seen_by_member" AS
2049 SELECT
2050 "member"."id" AS "seen_by_member_id",
2051 CASE WHEN "event"."state" IN (
2052 'voting',
2053 'finished_without_winner',
2054 'finished_with_winner'
2055 ) THEN
2056 'voting'::"notify_level"
2057 ELSE
2058 CASE WHEN "event"."state" IN (
2059 'verification',
2060 'canceled_after_revocation_during_verification',
2061 'canceled_no_initiative_admitted'
2062 ) THEN
2063 'verification'::"notify_level"
2064 ELSE
2065 CASE WHEN "event"."state" IN (
2066 'discussion',
2067 'canceled_after_revocation_during_discussion'
2068 ) THEN
2069 'discussion'::"notify_level"
2070 ELSE
2071 'all'::"notify_level"
2072 END
2073 END
2074 END AS "notify_level",
2075 "event".*
2076 FROM "member" CROSS JOIN "event"
2077 LEFT JOIN "issue"
2078 ON "event"."issue_id" = "issue"."id"
2079 LEFT JOIN "membership"
2080 ON "member"."id" = "membership"."member_id"
2081 AND "issue"."area_id" = "membership"."area_id"
2082 LEFT JOIN "interest"
2083 ON "member"."id" = "interest"."member_id"
2084 AND "event"."issue_id" = "interest"."issue_id"
2085 LEFT JOIN "supporter"
2086 ON "member"."id" = "supporter"."member_id"
2087 AND "event"."initiative_id" = "supporter"."initiative_id"
2088 LEFT JOIN "ignored_member"
2089 ON "member"."id" = "ignored_member"."member_id"
2090 AND "event"."member_id" = "ignored_member"."other_member_id"
2091 LEFT JOIN "ignored_initiative"
2092 ON "member"."id" = "ignored_initiative"."member_id"
2093 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2094 WHERE (
2095 "supporter"."member_id" NOTNULL OR
2096 "interest"."member_id" NOTNULL OR
2097 ( "membership"."member_id" NOTNULL AND
2098 "event"."event" IN (
2099 'issue_state_changed',
2100 'initiative_created_in_new_issue',
2101 'initiative_created_in_existing_issue',
2102 'initiative_revoked' ) ) )
2103 AND "ignored_member"."member_id" ISNULL
2104 AND "ignored_initiative"."member_id" ISNULL;
2106 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"';
2109 CREATE VIEW "selected_event_seen_by_member" AS
2110 SELECT
2111 "member"."id" AS "seen_by_member_id",
2112 CASE WHEN "event"."state" IN (
2113 'voting',
2114 'finished_without_winner',
2115 'finished_with_winner'
2116 ) THEN
2117 'voting'::"notify_level"
2118 ELSE
2119 CASE WHEN "event"."state" IN (
2120 'verification',
2121 'canceled_after_revocation_during_verification',
2122 'canceled_no_initiative_admitted'
2123 ) THEN
2124 'verification'::"notify_level"
2125 ELSE
2126 CASE WHEN "event"."state" IN (
2127 'discussion',
2128 'canceled_after_revocation_during_discussion'
2129 ) THEN
2130 'discussion'::"notify_level"
2131 ELSE
2132 'all'::"notify_level"
2133 END
2134 END
2135 END AS "notify_level",
2136 "event".*
2137 FROM "member" CROSS JOIN "event"
2138 LEFT JOIN "issue"
2139 ON "event"."issue_id" = "issue"."id"
2140 LEFT JOIN "membership"
2141 ON "member"."id" = "membership"."member_id"
2142 AND "issue"."area_id" = "membership"."area_id"
2143 LEFT JOIN "interest"
2144 ON "member"."id" = "interest"."member_id"
2145 AND "event"."issue_id" = "interest"."issue_id"
2146 LEFT JOIN "supporter"
2147 ON "member"."id" = "supporter"."member_id"
2148 AND "event"."initiative_id" = "supporter"."initiative_id"
2149 LEFT JOIN "ignored_member"
2150 ON "member"."id" = "ignored_member"."member_id"
2151 AND "event"."member_id" = "ignored_member"."other_member_id"
2152 LEFT JOIN "ignored_initiative"
2153 ON "member"."id" = "ignored_initiative"."member_id"
2154 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2155 WHERE (
2156 ( "member"."notify_level" >= 'all' ) OR
2157 ( "member"."notify_level" >= 'voting' AND
2158 "event"."state" IN (
2159 'voting',
2160 'finished_without_winner',
2161 'finished_with_winner' ) ) OR
2162 ( "member"."notify_level" >= 'verification' AND
2163 "event"."state" IN (
2164 'verification',
2165 'canceled_after_revocation_during_verification',
2166 'canceled_no_initiative_admitted' ) ) OR
2167 ( "member"."notify_level" >= 'discussion' AND
2168 "event"."state" IN (
2169 'discussion',
2170 'canceled_after_revocation_during_discussion' ) ) )
2171 AND (
2172 "supporter"."member_id" NOTNULL OR
2173 "interest"."member_id" NOTNULL OR
2174 ( "membership"."member_id" NOTNULL AND
2175 "event"."event" IN (
2176 'issue_state_changed',
2177 'initiative_created_in_new_issue',
2178 'initiative_created_in_existing_issue',
2179 'initiative_revoked' ) ) )
2180 AND "ignored_member"."member_id" ISNULL
2181 AND "ignored_initiative"."member_id" ISNULL;
2183 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"';
2186 CREATE TYPE "timeline_event" AS ENUM (
2187 'issue_created',
2188 'issue_canceled',
2189 'issue_accepted',
2190 'issue_half_frozen',
2191 'issue_finished_without_voting',
2192 'issue_voting_started',
2193 'issue_finished_after_voting',
2194 'initiative_created',
2195 'initiative_revoked',
2196 'draft_created',
2197 'suggestion_created');
2199 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)';
2202 CREATE VIEW "timeline_issue" AS
2203 SELECT
2204 "created" AS "occurrence",
2205 'issue_created'::"timeline_event" AS "event",
2206 "id" AS "issue_id"
2207 FROM "issue"
2208 UNION ALL
2209 SELECT
2210 "closed" AS "occurrence",
2211 'issue_canceled'::"timeline_event" AS "event",
2212 "id" AS "issue_id"
2213 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
2214 UNION ALL
2215 SELECT
2216 "accepted" AS "occurrence",
2217 'issue_accepted'::"timeline_event" AS "event",
2218 "id" AS "issue_id"
2219 FROM "issue" WHERE "accepted" NOTNULL
2220 UNION ALL
2221 SELECT
2222 "half_frozen" AS "occurrence",
2223 'issue_half_frozen'::"timeline_event" AS "event",
2224 "id" AS "issue_id"
2225 FROM "issue" WHERE "half_frozen" NOTNULL
2226 UNION ALL
2227 SELECT
2228 "fully_frozen" AS "occurrence",
2229 'issue_voting_started'::"timeline_event" AS "event",
2230 "id" AS "issue_id"
2231 FROM "issue"
2232 WHERE "fully_frozen" NOTNULL
2233 AND ("closed" ISNULL OR "closed" != "fully_frozen")
2234 UNION ALL
2235 SELECT
2236 "closed" AS "occurrence",
2237 CASE WHEN "fully_frozen" = "closed" THEN
2238 'issue_finished_without_voting'::"timeline_event"
2239 ELSE
2240 'issue_finished_after_voting'::"timeline_event"
2241 END AS "event",
2242 "id" AS "issue_id"
2243 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
2245 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)';
2248 CREATE VIEW "timeline_initiative" AS
2249 SELECT
2250 "created" AS "occurrence",
2251 'initiative_created'::"timeline_event" AS "event",
2252 "id" AS "initiative_id"
2253 FROM "initiative"
2254 UNION ALL
2255 SELECT
2256 "revoked" AS "occurrence",
2257 'initiative_revoked'::"timeline_event" AS "event",
2258 "id" AS "initiative_id"
2259 FROM "initiative" WHERE "revoked" NOTNULL;
2261 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)';
2264 CREATE VIEW "timeline_draft" AS
2265 SELECT
2266 "created" AS "occurrence",
2267 'draft_created'::"timeline_event" AS "event",
2268 "id" AS "draft_id"
2269 FROM "draft";
2271 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)';
2274 CREATE VIEW "timeline_suggestion" AS
2275 SELECT
2276 "created" AS "occurrence",
2277 'suggestion_created'::"timeline_event" AS "event",
2278 "id" AS "suggestion_id"
2279 FROM "suggestion";
2281 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)';
2284 CREATE VIEW "timeline" AS
2285 SELECT
2286 "occurrence",
2287 "event",
2288 "issue_id",
2289 NULL AS "initiative_id",
2290 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
2291 NULL::INT8 AS "suggestion_id"
2292 FROM "timeline_issue"
2293 UNION ALL
2294 SELECT
2295 "occurrence",
2296 "event",
2297 NULL AS "issue_id",
2298 "initiative_id",
2299 NULL AS "draft_id",
2300 NULL AS "suggestion_id"
2301 FROM "timeline_initiative"
2302 UNION ALL
2303 SELECT
2304 "occurrence",
2305 "event",
2306 NULL AS "issue_id",
2307 NULL AS "initiative_id",
2308 "draft_id",
2309 NULL AS "suggestion_id"
2310 FROM "timeline_draft"
2311 UNION ALL
2312 SELECT
2313 "occurrence",
2314 "event",
2315 NULL AS "issue_id",
2316 NULL AS "initiative_id",
2317 NULL AS "draft_id",
2318 "suggestion_id"
2319 FROM "timeline_suggestion";
2321 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)';
2325 ------------------------------------------------------
2326 -- Row set returning function for delegation chains --
2327 ------------------------------------------------------
2330 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
2331 ('first', 'intermediate', 'last', 'repetition');
2333 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
2336 CREATE TYPE "delegation_chain_row" AS (
2337 "index" INT4,
2338 "member_id" INT4,
2339 "member_valid" BOOLEAN,
2340 "participation" BOOLEAN,
2341 "overridden" BOOLEAN,
2342 "scope_in" "delegation_scope",
2343 "scope_out" "delegation_scope",
2344 "disabled_out" BOOLEAN,
2345 "loop" "delegation_chain_loop_tag" );
2347 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';
2349 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
2350 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';
2351 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
2352 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
2353 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
2354 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
2355 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
2358 CREATE FUNCTION "delegation_chain_for_closed_issue"
2359 ( "member_id_p" "member"."id"%TYPE,
2360 "issue_id_p" "issue"."id"%TYPE )
2361 RETURNS SETOF "delegation_chain_row"
2362 LANGUAGE 'plpgsql' STABLE AS $$
2363 DECLARE
2364 "output_row" "delegation_chain_row";
2365 "direct_voter_row" "direct_voter"%ROWTYPE;
2366 "delegating_voter_row" "delegating_voter"%ROWTYPE;
2367 BEGIN
2368 "output_row"."index" := 0;
2369 "output_row"."member_id" := "member_id_p";
2370 "output_row"."member_valid" := TRUE;
2371 "output_row"."participation" := FALSE;
2372 "output_row"."overridden" := FALSE;
2373 "output_row"."disabled_out" := FALSE;
2374 LOOP
2375 SELECT INTO "direct_voter_row" * FROM "direct_voter"
2376 WHERE "issue_id" = "issue_id_p"
2377 AND "member_id" = "output_row"."member_id";
2378 IF "direct_voter_row"."member_id" NOTNULL THEN
2379 "output_row"."participation" := TRUE;
2380 "output_row"."scope_out" := NULL;
2381 "output_row"."disabled_out" := NULL;
2382 RETURN NEXT "output_row";
2383 RETURN;
2384 END IF;
2385 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"
2386 WHERE "issue_id" = "issue_id_p"
2387 AND "member_id" = "output_row"."member_id";
2388 IF "delegating_voter_row"."member_id" ISNULL THEN
2389 RETURN;
2390 END IF;
2391 "output_row"."scope_out" := "delegating_voter_row"."scope";
2392 RETURN NEXT "output_row";
2393 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];
2394 "output_row"."scope_in" := "output_row"."scope_out";
2395 END LOOP;
2396 END;
2397 $$;
2399 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"
2400 ( "member"."id"%TYPE,
2401 "member"."id"%TYPE )
2402 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';
2405 CREATE FUNCTION "delegation_chain"
2406 ( "member_id_p" "member"."id"%TYPE,
2407 "unit_id_p" "unit"."id"%TYPE,
2408 "area_id_p" "area"."id"%TYPE,
2409 "issue_id_p" "issue"."id"%TYPE,
2410 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2411 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2412 RETURNS SETOF "delegation_chain_row"
2413 LANGUAGE 'plpgsql' STABLE AS $$
2414 DECLARE
2415 "scope_v" "delegation_scope";
2416 "unit_id_v" "unit"."id"%TYPE;
2417 "area_id_v" "area"."id"%TYPE;
2418 "issue_row" "issue"%ROWTYPE;
2419 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2420 "loop_member_id_v" "member"."id"%TYPE;
2421 "output_row" "delegation_chain_row";
2422 "output_rows" "delegation_chain_row"[];
2423 "simulate_v" BOOLEAN;
2424 "simulate_here_v" BOOLEAN;
2425 "delegation_row" "delegation"%ROWTYPE;
2426 "row_count" INT4;
2427 "i" INT4;
2428 "loop_v" BOOLEAN;
2429 BEGIN
2430 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
2431 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
2432 END IF;
2433 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
2434 "simulate_v" := TRUE;
2435 ELSE
2436 "simulate_v" := FALSE;
2437 END IF;
2438 IF
2439 "unit_id_p" NOTNULL AND
2440 "area_id_p" ISNULL AND
2441 "issue_id_p" ISNULL
2442 THEN
2443 "scope_v" := 'unit';
2444 "unit_id_v" := "unit_id_p";
2445 ELSIF
2446 "unit_id_p" ISNULL AND
2447 "area_id_p" NOTNULL AND
2448 "issue_id_p" ISNULL
2449 THEN
2450 "scope_v" := 'area';
2451 "area_id_v" := "area_id_p";
2452 SELECT "unit_id" INTO "unit_id_v"
2453 FROM "area" WHERE "id" = "area_id_v";
2454 ELSIF
2455 "unit_id_p" ISNULL AND
2456 "area_id_p" ISNULL AND
2457 "issue_id_p" NOTNULL
2458 THEN
2459 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
2460 IF "issue_row"."id" ISNULL THEN
2461 RETURN;
2462 END IF;
2463 IF "issue_row"."closed" NOTNULL THEN
2464 IF "simulate_v" THEN
2465 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
2466 END IF;
2467 FOR "output_row" IN
2468 SELECT * FROM
2469 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
2470 LOOP
2471 RETURN NEXT "output_row";
2472 END LOOP;
2473 RETURN;
2474 END IF;
2475 "scope_v" := 'issue';
2476 SELECT "area_id" INTO "area_id_v"
2477 FROM "issue" WHERE "id" = "issue_id_p";
2478 SELECT "unit_id" INTO "unit_id_v"
2479 FROM "area" WHERE "id" = "area_id_v";
2480 ELSE
2481 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2482 END IF;
2483 "visited_member_ids" := '{}';
2484 "loop_member_id_v" := NULL;
2485 "output_rows" := '{}';
2486 "output_row"."index" := 0;
2487 "output_row"."member_id" := "member_id_p";
2488 "output_row"."member_valid" := TRUE;
2489 "output_row"."participation" := FALSE;
2490 "output_row"."overridden" := FALSE;
2491 "output_row"."disabled_out" := FALSE;
2492 "output_row"."scope_out" := NULL;
2493 LOOP
2494 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2495 "loop_member_id_v" := "output_row"."member_id";
2496 ELSE
2497 "visited_member_ids" :=
2498 "visited_member_ids" || "output_row"."member_id";
2499 END IF;
2500 IF "output_row"."participation" ISNULL THEN
2501 "output_row"."overridden" := NULL;
2502 ELSIF "output_row"."participation" THEN
2503 "output_row"."overridden" := TRUE;
2504 END IF;
2505 "output_row"."scope_in" := "output_row"."scope_out";
2506 "output_row"."member_valid" := EXISTS (
2507 SELECT NULL FROM "member" JOIN "privilege"
2508 ON "privilege"."member_id" = "member"."id"
2509 AND "privilege"."unit_id" = "unit_id_v"
2510 WHERE "id" = "output_row"."member_id"
2511 AND "member"."active" AND "privilege"."voting_right"
2512 );
2513 "simulate_here_v" := (
2514 "simulate_v" AND
2515 "output_row"."member_id" = "member_id_p"
2516 );
2517 "delegation_row" := ROW(NULL);
2518 IF "output_row"."member_valid" OR "simulate_here_v" THEN
2519 IF "scope_v" = 'unit' THEN
2520 IF NOT "simulate_here_v" THEN
2521 SELECT * INTO "delegation_row" FROM "delegation"
2522 WHERE "truster_id" = "output_row"."member_id"
2523 AND "unit_id" = "unit_id_v";
2524 END IF;
2525 ELSIF "scope_v" = 'area' THEN
2526 "output_row"."participation" := EXISTS (
2527 SELECT NULL FROM "membership"
2528 WHERE "area_id" = "area_id_p"
2529 AND "member_id" = "output_row"."member_id"
2530 );
2531 IF "simulate_here_v" THEN
2532 IF "simulate_trustee_id_p" ISNULL THEN
2533 SELECT * INTO "delegation_row" FROM "delegation"
2534 WHERE "truster_id" = "output_row"."member_id"
2535 AND "unit_id" = "unit_id_v";
2536 END IF;
2537 ELSE
2538 SELECT * INTO "delegation_row" FROM "delegation"
2539 WHERE "truster_id" = "output_row"."member_id"
2540 AND (
2541 "unit_id" = "unit_id_v" OR
2542 "area_id" = "area_id_v"
2543 )
2544 ORDER BY "scope" DESC;
2545 END IF;
2546 ELSIF "scope_v" = 'issue' THEN
2547 IF "issue_row"."fully_frozen" ISNULL THEN
2548 "output_row"."participation" := EXISTS (
2549 SELECT NULL FROM "interest"
2550 WHERE "issue_id" = "issue_id_p"
2551 AND "member_id" = "output_row"."member_id"
2552 );
2553 ELSE
2554 IF "output_row"."member_id" = "member_id_p" THEN
2555 "output_row"."participation" := EXISTS (
2556 SELECT NULL FROM "direct_voter"
2557 WHERE "issue_id" = "issue_id_p"
2558 AND "member_id" = "output_row"."member_id"
2559 );
2560 ELSE
2561 "output_row"."participation" := NULL;
2562 END IF;
2563 END IF;
2564 IF "simulate_here_v" THEN
2565 IF "simulate_trustee_id_p" ISNULL THEN
2566 SELECT * INTO "delegation_row" FROM "delegation"
2567 WHERE "truster_id" = "output_row"."member_id"
2568 AND (
2569 "unit_id" = "unit_id_v" OR
2570 "area_id" = "area_id_v"
2571 )
2572 ORDER BY "scope" DESC;
2573 END IF;
2574 ELSE
2575 SELECT * INTO "delegation_row" FROM "delegation"
2576 WHERE "truster_id" = "output_row"."member_id"
2577 AND (
2578 "unit_id" = "unit_id_v" OR
2579 "area_id" = "area_id_v" OR
2580 "issue_id" = "issue_id_p"
2581 )
2582 ORDER BY "scope" DESC;
2583 END IF;
2584 END IF;
2585 ELSE
2586 "output_row"."participation" := FALSE;
2587 END IF;
2588 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
2589 "output_row"."scope_out" := "scope_v";
2590 "output_rows" := "output_rows" || "output_row";
2591 "output_row"."member_id" := "simulate_trustee_id_p";
2592 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2593 "output_row"."scope_out" := "delegation_row"."scope";
2594 "output_rows" := "output_rows" || "output_row";
2595 "output_row"."member_id" := "delegation_row"."trustee_id";
2596 ELSIF "delegation_row"."scope" NOTNULL THEN
2597 "output_row"."scope_out" := "delegation_row"."scope";
2598 "output_row"."disabled_out" := TRUE;
2599 "output_rows" := "output_rows" || "output_row";
2600 EXIT;
2601 ELSE
2602 "output_row"."scope_out" := NULL;
2603 "output_rows" := "output_rows" || "output_row";
2604 EXIT;
2605 END IF;
2606 EXIT WHEN "loop_member_id_v" NOTNULL;
2607 "output_row"."index" := "output_row"."index" + 1;
2608 END LOOP;
2609 "row_count" := array_upper("output_rows", 1);
2610 "i" := 1;
2611 "loop_v" := FALSE;
2612 LOOP
2613 "output_row" := "output_rows"["i"];
2614 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2615 IF "loop_v" THEN
2616 IF "i" + 1 = "row_count" THEN
2617 "output_row"."loop" := 'last';
2618 ELSIF "i" = "row_count" THEN
2619 "output_row"."loop" := 'repetition';
2620 ELSE
2621 "output_row"."loop" := 'intermediate';
2622 END IF;
2623 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2624 "output_row"."loop" := 'first';
2625 "loop_v" := TRUE;
2626 END IF;
2627 IF "scope_v" = 'unit' THEN
2628 "output_row"."participation" := NULL;
2629 END IF;
2630 RETURN NEXT "output_row";
2631 "i" := "i" + 1;
2632 END LOOP;
2633 RETURN;
2634 END;
2635 $$;
2637 COMMENT ON FUNCTION "delegation_chain"
2638 ( "member"."id"%TYPE,
2639 "unit"."id"%TYPE,
2640 "area"."id"%TYPE,
2641 "issue"."id"%TYPE,
2642 "member"."id"%TYPE,
2643 BOOLEAN )
2644 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
2648 ---------------------------------------------------------
2649 -- Single row returning function for delegation chains --
2650 ---------------------------------------------------------
2653 CREATE TYPE "delegation_info_loop_type" AS ENUM
2654 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');
2656 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''';
2659 CREATE TYPE "delegation_info_type" AS (
2660 "own_participation" BOOLEAN,
2661 "own_delegation_scope" "delegation_scope",
2662 "first_trustee_id" INT4,
2663 "first_trustee_participation" BOOLEAN,
2664 "first_trustee_ellipsis" BOOLEAN,
2665 "other_trustee_id" INT4,
2666 "other_trustee_participation" BOOLEAN,
2667 "other_trustee_ellipsis" BOOLEAN,
2668 "delegation_loop" "delegation_info_loop_type",
2669 "participating_member_id" INT4 );
2671 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';
2673 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';
2674 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';
2675 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';
2676 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
2677 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';
2678 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';
2679 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)';
2680 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';
2681 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';
2682 COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain';
2685 CREATE FUNCTION "delegation_info"
2686 ( "member_id_p" "member"."id"%TYPE,
2687 "unit_id_p" "unit"."id"%TYPE,
2688 "area_id_p" "area"."id"%TYPE,
2689 "issue_id_p" "issue"."id"%TYPE,
2690 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2691 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2692 RETURNS "delegation_info_type"
2693 LANGUAGE 'plpgsql' STABLE AS $$
2694 DECLARE
2695 "current_row" "delegation_chain_row";
2696 "result" "delegation_info_type";
2697 BEGIN
2698 "result"."own_participation" := FALSE;
2699 FOR "current_row" IN
2700 SELECT * FROM "delegation_chain"(
2701 "member_id_p",
2702 "unit_id_p", "area_id_p", "issue_id_p",
2703 "simulate_trustee_id_p", "simulate_default_p")
2704 LOOP
2705 IF
2706 "result"."participating_member_id" ISNULL AND
2707 "current_row"."participation"
2708 THEN
2709 "result"."participating_member_id" := "current_row"."member_id";
2710 END IF;
2711 IF "current_row"."member_id" = "member_id_p" THEN
2712 "result"."own_participation" := "current_row"."participation";
2713 "result"."own_delegation_scope" := "current_row"."scope_out";
2714 IF "current_row"."loop" = 'first' THEN
2715 "result"."delegation_loop" := 'own';
2716 END IF;
2717 ELSIF
2718 "current_row"."member_valid" AND
2719 ( "current_row"."loop" ISNULL OR
2720 "current_row"."loop" != 'repetition' )
2721 THEN
2722 IF "result"."first_trustee_id" ISNULL THEN
2723 "result"."first_trustee_id" := "current_row"."member_id";
2724 "result"."first_trustee_participation" := "current_row"."participation";
2725 "result"."first_trustee_ellipsis" := FALSE;
2726 IF "current_row"."loop" = 'first' THEN
2727 "result"."delegation_loop" := 'first';
2728 END IF;
2729 ELSIF "result"."other_trustee_id" ISNULL THEN
2730 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
2731 "result"."other_trustee_id" := "current_row"."member_id";
2732 "result"."other_trustee_participation" := TRUE;
2733 "result"."other_trustee_ellipsis" := FALSE;
2734 IF "current_row"."loop" = 'first' THEN
2735 "result"."delegation_loop" := 'other';
2736 END IF;
2737 ELSE
2738 "result"."first_trustee_ellipsis" := TRUE;
2739 IF "current_row"."loop" = 'first' THEN
2740 "result"."delegation_loop" := 'first_ellipsis';
2741 END IF;
2742 END IF;
2743 ELSE
2744 "result"."other_trustee_ellipsis" := TRUE;
2745 IF "current_row"."loop" = 'first' THEN
2746 "result"."delegation_loop" := 'other_ellipsis';
2747 END IF;
2748 END IF;
2749 END IF;
2750 END LOOP;
2751 RETURN "result";
2752 END;
2753 $$;
2755 COMMENT ON FUNCTION "delegation_info"
2756 ( "member"."id"%TYPE,
2757 "unit"."id"%TYPE,
2758 "area"."id"%TYPE,
2759 "issue"."id"%TYPE,
2760 "member"."id"%TYPE,
2761 BOOLEAN )
2762 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
2766 ------------------------------
2767 -- Comparison by vote count --
2768 ------------------------------
2770 CREATE FUNCTION "vote_ratio"
2771 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
2772 "negative_votes_p" "initiative"."negative_votes"%TYPE )
2773 RETURNS FLOAT8
2774 LANGUAGE 'plpgsql' STABLE AS $$
2775 BEGIN
2776 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
2777 RETURN
2778 "positive_votes_p"::FLOAT8 /
2779 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
2780 ELSIF "positive_votes_p" > 0 THEN
2781 RETURN "positive_votes_p";
2782 ELSIF "negative_votes_p" > 0 THEN
2783 RETURN 1 - "negative_votes_p";
2784 ELSE
2785 RETURN 0.5;
2786 END IF;
2787 END;
2788 $$;
2790 COMMENT ON FUNCTION "vote_ratio"
2791 ( "initiative"."positive_votes"%TYPE,
2792 "initiative"."negative_votes"%TYPE )
2793 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.';
2797 ------------------------------------------------
2798 -- Locking for snapshots and voting procedure --
2799 ------------------------------------------------
2802 CREATE FUNCTION "share_row_lock_issue_trigger"()
2803 RETURNS TRIGGER
2804 LANGUAGE 'plpgsql' VOLATILE AS $$
2805 BEGIN
2806 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2807 PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;
2808 END IF;
2809 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2810 PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;
2811 RETURN NEW;
2812 ELSE
2813 RETURN OLD;
2814 END IF;
2815 END;
2816 $$;
2818 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';
2821 CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()
2822 RETURNS TRIGGER
2823 LANGUAGE 'plpgsql' VOLATILE AS $$
2824 BEGIN
2825 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2826 PERFORM NULL FROM "issue"
2827 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2828 WHERE "initiative"."id" = OLD."initiative_id"
2829 FOR SHARE OF "issue";
2830 END IF;
2831 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2832 PERFORM NULL FROM "issue"
2833 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2834 WHERE "initiative"."id" = NEW."initiative_id"
2835 FOR SHARE OF "issue";
2836 RETURN NEW;
2837 ELSE
2838 RETURN OLD;
2839 END IF;
2840 END;
2841 $$;
2843 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';
2846 CREATE TRIGGER "share_row_lock_issue"
2847 BEFORE INSERT OR UPDATE OR DELETE ON "initiative"
2848 FOR EACH ROW EXECUTE PROCEDURE
2849 "share_row_lock_issue_trigger"();
2851 CREATE TRIGGER "share_row_lock_issue"
2852 BEFORE INSERT OR UPDATE OR DELETE ON "interest"
2853 FOR EACH ROW EXECUTE PROCEDURE
2854 "share_row_lock_issue_trigger"();
2856 CREATE TRIGGER "share_row_lock_issue"
2857 BEFORE INSERT OR UPDATE OR DELETE ON "supporter"
2858 FOR EACH ROW EXECUTE PROCEDURE
2859 "share_row_lock_issue_trigger"();
2861 CREATE TRIGGER "share_row_lock_issue_via_initiative"
2862 BEFORE INSERT OR UPDATE OR DELETE ON "opinion"
2863 FOR EACH ROW EXECUTE PROCEDURE
2864 "share_row_lock_issue_via_initiative_trigger"();
2866 CREATE TRIGGER "share_row_lock_issue"
2867 BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter"
2868 FOR EACH ROW EXECUTE PROCEDURE
2869 "share_row_lock_issue_trigger"();
2871 CREATE TRIGGER "share_row_lock_issue"
2872 BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter"
2873 FOR EACH ROW EXECUTE PROCEDURE
2874 "share_row_lock_issue_trigger"();
2876 CREATE TRIGGER "share_row_lock_issue"
2877 BEFORE INSERT OR UPDATE OR DELETE ON "vote"
2878 FOR EACH ROW EXECUTE PROCEDURE
2879 "share_row_lock_issue_trigger"();
2881 COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function';
2882 COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function';
2883 COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function';
2884 COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function';
2885 COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function';
2886 COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function';
2887 COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function';
2890 CREATE FUNCTION "lock_issue"
2891 ( "issue_id_p" "issue"."id"%TYPE )
2892 RETURNS VOID
2893 LANGUAGE 'plpgsql' VOLATILE AS $$
2894 BEGIN
2895 LOCK TABLE "member" IN SHARE MODE;
2896 LOCK TABLE "privilege" IN SHARE MODE;
2897 LOCK TABLE "membership" IN SHARE MODE;
2898 LOCK TABLE "policy" IN SHARE MODE;
2899 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2900 -- NOTE: The row-level exclusive lock in combination with the
2901 -- share_row_lock_issue(_via_initiative)_trigger functions (which
2902 -- acquire a row-level share lock on the issue) ensure that no data
2903 -- is changed, which could affect calculation of snapshots or
2904 -- counting of votes. Table "delegation" must be table-level-locked,
2905 -- as it also contains issue- and global-scope delegations.
2906 LOCK TABLE "delegation" IN SHARE MODE;
2907 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
2908 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
2909 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
2910 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
2911 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
2912 RETURN;
2913 END;
2914 $$;
2916 COMMENT ON FUNCTION "lock_issue"
2917 ( "issue"."id"%TYPE )
2918 IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';
2922 ------------------------------------------------------------------------
2923 -- Regular tasks, except calculcation of snapshots and voting results --
2924 ------------------------------------------------------------------------
2926 CREATE FUNCTION "check_activity"()
2927 RETURNS VOID
2928 LANGUAGE 'plpgsql' VOLATILE AS $$
2929 DECLARE
2930 "system_setting_row" "system_setting"%ROWTYPE;
2931 BEGIN
2932 SELECT * INTO "system_setting_row" FROM "system_setting";
2933 LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
2934 IF "system_setting_row"."member_ttl" NOTNULL THEN
2935 UPDATE "member" SET "active" = FALSE
2936 WHERE "active" = TRUE
2937 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
2938 END IF;
2939 RETURN;
2940 END;
2941 $$;
2943 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
2946 CREATE FUNCTION "calculate_member_counts"()
2947 RETURNS VOID
2948 LANGUAGE 'plpgsql' VOLATILE AS $$
2949 BEGIN
2950 LOCK TABLE "member" IN SHARE MODE;
2951 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
2952 LOCK TABLE "unit" IN EXCLUSIVE MODE;
2953 LOCK TABLE "area" IN EXCLUSIVE MODE;
2954 LOCK TABLE "privilege" IN SHARE MODE;
2955 LOCK TABLE "membership" IN SHARE MODE;
2956 DELETE FROM "member_count";
2957 INSERT INTO "member_count" ("total_count")
2958 SELECT "total_count" FROM "member_count_view";
2959 UPDATE "unit" SET "member_count" = "view"."member_count"
2960 FROM "unit_member_count" AS "view"
2961 WHERE "view"."unit_id" = "unit"."id";
2962 UPDATE "area" SET
2963 "direct_member_count" = "view"."direct_member_count",
2964 "member_weight" = "view"."member_weight"
2965 FROM "area_member_count" AS "view"
2966 WHERE "view"."area_id" = "area"."id";
2967 RETURN;
2968 END;
2969 $$;
2971 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"';
2975 ------------------------------
2976 -- Calculation of snapshots --
2977 ------------------------------
2979 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
2980 ( "issue_id_p" "issue"."id"%TYPE,
2981 "member_id_p" "member"."id"%TYPE,
2982 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2983 RETURNS "direct_population_snapshot"."weight"%TYPE
2984 LANGUAGE 'plpgsql' VOLATILE AS $$
2985 DECLARE
2986 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2987 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
2988 "weight_v" INT4;
2989 "sub_weight_v" INT4;
2990 BEGIN
2991 "weight_v" := 0;
2992 FOR "issue_delegation_row" IN
2993 SELECT * FROM "issue_delegation"
2994 WHERE "trustee_id" = "member_id_p"
2995 AND "issue_id" = "issue_id_p"
2996 LOOP
2997 IF NOT EXISTS (
2998 SELECT NULL FROM "direct_population_snapshot"
2999 WHERE "issue_id" = "issue_id_p"
3000 AND "event" = 'periodic'
3001 AND "member_id" = "issue_delegation_row"."truster_id"
3002 ) AND NOT EXISTS (
3003 SELECT NULL FROM "delegating_population_snapshot"
3004 WHERE "issue_id" = "issue_id_p"
3005 AND "event" = 'periodic'
3006 AND "member_id" = "issue_delegation_row"."truster_id"
3007 ) THEN
3008 "delegate_member_ids_v" :=
3009 "member_id_p" || "delegate_member_ids_p";
3010 INSERT INTO "delegating_population_snapshot" (
3011 "issue_id",
3012 "event",
3013 "member_id",
3014 "scope",
3015 "delegate_member_ids"
3016 ) VALUES (
3017 "issue_id_p",
3018 'periodic',
3019 "issue_delegation_row"."truster_id",
3020 "issue_delegation_row"."scope",
3021 "delegate_member_ids_v"
3022 );
3023 "sub_weight_v" := 1 +
3024 "weight_of_added_delegations_for_population_snapshot"(
3025 "issue_id_p",
3026 "issue_delegation_row"."truster_id",
3027 "delegate_member_ids_v"
3028 );
3029 UPDATE "delegating_population_snapshot"
3030 SET "weight" = "sub_weight_v"
3031 WHERE "issue_id" = "issue_id_p"
3032 AND "event" = 'periodic'
3033 AND "member_id" = "issue_delegation_row"."truster_id";
3034 "weight_v" := "weight_v" + "sub_weight_v";
3035 END IF;
3036 END LOOP;
3037 RETURN "weight_v";
3038 END;
3039 $$;
3041 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
3042 ( "issue"."id"%TYPE,
3043 "member"."id"%TYPE,
3044 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3045 IS 'Helper function for "create_population_snapshot" function';
3048 CREATE FUNCTION "create_population_snapshot"
3049 ( "issue_id_p" "issue"."id"%TYPE )
3050 RETURNS VOID
3051 LANGUAGE 'plpgsql' VOLATILE AS $$
3052 DECLARE
3053 "member_id_v" "member"."id"%TYPE;
3054 BEGIN
3055 DELETE FROM "direct_population_snapshot"
3056 WHERE "issue_id" = "issue_id_p"
3057 AND "event" = 'periodic';
3058 DELETE FROM "delegating_population_snapshot"
3059 WHERE "issue_id" = "issue_id_p"
3060 AND "event" = 'periodic';
3061 INSERT INTO "direct_population_snapshot"
3062 ("issue_id", "event", "member_id")
3063 SELECT
3064 "issue_id_p" AS "issue_id",
3065 'periodic'::"snapshot_event" AS "event",
3066 "member"."id" AS "member_id"
3067 FROM "issue"
3068 JOIN "area" ON "issue"."area_id" = "area"."id"
3069 JOIN "membership" ON "area"."id" = "membership"."area_id"
3070 JOIN "member" ON "membership"."member_id" = "member"."id"
3071 JOIN "privilege"
3072 ON "privilege"."unit_id" = "area"."unit_id"
3073 AND "privilege"."member_id" = "member"."id"
3074 WHERE "issue"."id" = "issue_id_p"
3075 AND "member"."active" AND "privilege"."voting_right"
3076 UNION
3077 SELECT
3078 "issue_id_p" AS "issue_id",
3079 'periodic'::"snapshot_event" AS "event",
3080 "member"."id" AS "member_id"
3081 FROM "issue"
3082 JOIN "area" ON "issue"."area_id" = "area"."id"
3083 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3084 JOIN "member" ON "interest"."member_id" = "member"."id"
3085 JOIN "privilege"
3086 ON "privilege"."unit_id" = "area"."unit_id"
3087 AND "privilege"."member_id" = "member"."id"
3088 WHERE "issue"."id" = "issue_id_p"
3089 AND "member"."active" AND "privilege"."voting_right";
3090 FOR "member_id_v" IN
3091 SELECT "member_id" FROM "direct_population_snapshot"
3092 WHERE "issue_id" = "issue_id_p"
3093 AND "event" = 'periodic'
3094 LOOP
3095 UPDATE "direct_population_snapshot" SET
3096 "weight" = 1 +
3097 "weight_of_added_delegations_for_population_snapshot"(
3098 "issue_id_p",
3099 "member_id_v",
3100 '{}'
3101 )
3102 WHERE "issue_id" = "issue_id_p"
3103 AND "event" = 'periodic'
3104 AND "member_id" = "member_id_v";
3105 END LOOP;
3106 RETURN;
3107 END;
3108 $$;
3110 COMMENT ON FUNCTION "create_population_snapshot"
3111 ( "issue"."id"%TYPE )
3112 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.';
3115 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3116 ( "issue_id_p" "issue"."id"%TYPE,
3117 "member_id_p" "member"."id"%TYPE,
3118 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3119 RETURNS "direct_interest_snapshot"."weight"%TYPE
3120 LANGUAGE 'plpgsql' VOLATILE AS $$
3121 DECLARE
3122 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3123 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
3124 "weight_v" INT4;
3125 "sub_weight_v" INT4;
3126 BEGIN
3127 "weight_v" := 0;
3128 FOR "issue_delegation_row" IN
3129 SELECT * FROM "issue_delegation"
3130 WHERE "trustee_id" = "member_id_p"
3131 AND "issue_id" = "issue_id_p"
3132 LOOP
3133 IF NOT EXISTS (
3134 SELECT NULL FROM "direct_interest_snapshot"
3135 WHERE "issue_id" = "issue_id_p"
3136 AND "event" = 'periodic'
3137 AND "member_id" = "issue_delegation_row"."truster_id"
3138 ) AND NOT EXISTS (
3139 SELECT NULL FROM "delegating_interest_snapshot"
3140 WHERE "issue_id" = "issue_id_p"
3141 AND "event" = 'periodic'
3142 AND "member_id" = "issue_delegation_row"."truster_id"
3143 ) THEN
3144 "delegate_member_ids_v" :=
3145 "member_id_p" || "delegate_member_ids_p";
3146 INSERT INTO "delegating_interest_snapshot" (
3147 "issue_id",
3148 "event",
3149 "member_id",
3150 "scope",
3151 "delegate_member_ids"
3152 ) VALUES (
3153 "issue_id_p",
3154 'periodic',
3155 "issue_delegation_row"."truster_id",
3156 "issue_delegation_row"."scope",
3157 "delegate_member_ids_v"
3158 );
3159 "sub_weight_v" := 1 +
3160 "weight_of_added_delegations_for_interest_snapshot"(
3161 "issue_id_p",
3162 "issue_delegation_row"."truster_id",
3163 "delegate_member_ids_v"
3164 );
3165 UPDATE "delegating_interest_snapshot"
3166 SET "weight" = "sub_weight_v"
3167 WHERE "issue_id" = "issue_id_p"
3168 AND "event" = 'periodic'
3169 AND "member_id" = "issue_delegation_row"."truster_id";
3170 "weight_v" := "weight_v" + "sub_weight_v";
3171 END IF;
3172 END LOOP;
3173 RETURN "weight_v";
3174 END;
3175 $$;
3177 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3178 ( "issue"."id"%TYPE,
3179 "member"."id"%TYPE,
3180 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3181 IS 'Helper function for "create_interest_snapshot" function';
3184 CREATE FUNCTION "create_interest_snapshot"
3185 ( "issue_id_p" "issue"."id"%TYPE )
3186 RETURNS VOID
3187 LANGUAGE 'plpgsql' VOLATILE AS $$
3188 DECLARE
3189 "member_id_v" "member"."id"%TYPE;
3190 BEGIN
3191 DELETE FROM "direct_interest_snapshot"
3192 WHERE "issue_id" = "issue_id_p"
3193 AND "event" = 'periodic';
3194 DELETE FROM "delegating_interest_snapshot"
3195 WHERE "issue_id" = "issue_id_p"
3196 AND "event" = 'periodic';
3197 DELETE FROM "direct_supporter_snapshot"
3198 WHERE "issue_id" = "issue_id_p"
3199 AND "event" = 'periodic';
3200 INSERT INTO "direct_interest_snapshot"
3201 ("issue_id", "event", "member_id")
3202 SELECT
3203 "issue_id_p" AS "issue_id",
3204 'periodic' AS "event",
3205 "member"."id" AS "member_id"
3206 FROM "issue"
3207 JOIN "area" ON "issue"."area_id" = "area"."id"
3208 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3209 JOIN "member" ON "interest"."member_id" = "member"."id"
3210 JOIN "privilege"
3211 ON "privilege"."unit_id" = "area"."unit_id"
3212 AND "privilege"."member_id" = "member"."id"
3213 WHERE "issue"."id" = "issue_id_p"
3214 AND "member"."active" AND "privilege"."voting_right";
3215 FOR "member_id_v" IN
3216 SELECT "member_id" FROM "direct_interest_snapshot"
3217 WHERE "issue_id" = "issue_id_p"
3218 AND "event" = 'periodic'
3219 LOOP
3220 UPDATE "direct_interest_snapshot" SET
3221 "weight" = 1 +
3222 "weight_of_added_delegations_for_interest_snapshot"(
3223 "issue_id_p",
3224 "member_id_v",
3225 '{}'
3226 )
3227 WHERE "issue_id" = "issue_id_p"
3228 AND "event" = 'periodic'
3229 AND "member_id" = "member_id_v";
3230 END LOOP;
3231 INSERT INTO "direct_supporter_snapshot"
3232 ( "issue_id", "initiative_id", "event", "member_id",
3233 "draft_id", "informed", "satisfied" )
3234 SELECT
3235 "issue_id_p" AS "issue_id",
3236 "initiative"."id" AS "initiative_id",
3237 'periodic' AS "event",
3238 "supporter"."member_id" AS "member_id",
3239 "supporter"."draft_id" AS "draft_id",
3240 "supporter"."draft_id" = "current_draft"."id" AS "informed",
3241 NOT EXISTS (
3242 SELECT NULL FROM "critical_opinion"
3243 WHERE "initiative_id" = "initiative"."id"
3244 AND "member_id" = "supporter"."member_id"
3245 ) AS "satisfied"
3246 FROM "initiative"
3247 JOIN "supporter"
3248 ON "supporter"."initiative_id" = "initiative"."id"
3249 JOIN "current_draft"
3250 ON "initiative"."id" = "current_draft"."initiative_id"
3251 JOIN "direct_interest_snapshot"
3252 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
3253 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
3254 AND "event" = 'periodic'
3255 WHERE "initiative"."issue_id" = "issue_id_p";
3256 RETURN;
3257 END;
3258 $$;
3260 COMMENT ON FUNCTION "create_interest_snapshot"
3261 ( "issue"."id"%TYPE )
3262 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.';
3265 CREATE FUNCTION "create_snapshot"
3266 ( "issue_id_p" "issue"."id"%TYPE )
3267 RETURNS VOID
3268 LANGUAGE 'plpgsql' VOLATILE AS $$
3269 DECLARE
3270 "initiative_id_v" "initiative"."id"%TYPE;
3271 "suggestion_id_v" "suggestion"."id"%TYPE;
3272 BEGIN
3273 PERFORM "lock_issue"("issue_id_p");
3274 PERFORM "create_population_snapshot"("issue_id_p");
3275 PERFORM "create_interest_snapshot"("issue_id_p");
3276 UPDATE "issue" SET
3277 "snapshot" = now(),
3278 "latest_snapshot_event" = 'periodic',
3279 "population" = (
3280 SELECT coalesce(sum("weight"), 0)
3281 FROM "direct_population_snapshot"
3282 WHERE "issue_id" = "issue_id_p"
3283 AND "event" = 'periodic'
3284 )
3285 WHERE "id" = "issue_id_p";
3286 FOR "initiative_id_v" IN
3287 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
3288 LOOP
3289 UPDATE "initiative" SET
3290 "supporter_count" = (
3291 SELECT coalesce(sum("di"."weight"), 0)
3292 FROM "direct_interest_snapshot" AS "di"
3293 JOIN "direct_supporter_snapshot" AS "ds"
3294 ON "di"."member_id" = "ds"."member_id"
3295 WHERE "di"."issue_id" = "issue_id_p"
3296 AND "di"."event" = 'periodic'
3297 AND "ds"."initiative_id" = "initiative_id_v"
3298 AND "ds"."event" = 'periodic'
3299 ),
3300 "informed_supporter_count" = (
3301 SELECT coalesce(sum("di"."weight"), 0)
3302 FROM "direct_interest_snapshot" AS "di"
3303 JOIN "direct_supporter_snapshot" AS "ds"
3304 ON "di"."member_id" = "ds"."member_id"
3305 WHERE "di"."issue_id" = "issue_id_p"
3306 AND "di"."event" = 'periodic'
3307 AND "ds"."initiative_id" = "initiative_id_v"
3308 AND "ds"."event" = 'periodic'
3309 AND "ds"."informed"
3310 ),
3311 "satisfied_supporter_count" = (
3312 SELECT coalesce(sum("di"."weight"), 0)
3313 FROM "direct_interest_snapshot" AS "di"
3314 JOIN "direct_supporter_snapshot" AS "ds"
3315 ON "di"."member_id" = "ds"."member_id"
3316 WHERE "di"."issue_id" = "issue_id_p"
3317 AND "di"."event" = 'periodic'
3318 AND "ds"."initiative_id" = "initiative_id_v"
3319 AND "ds"."event" = 'periodic'
3320 AND "ds"."satisfied"
3321 ),
3322 "satisfied_informed_supporter_count" = (
3323 SELECT coalesce(sum("di"."weight"), 0)
3324 FROM "direct_interest_snapshot" AS "di"
3325 JOIN "direct_supporter_snapshot" AS "ds"
3326 ON "di"."member_id" = "ds"."member_id"
3327 WHERE "di"."issue_id" = "issue_id_p"
3328 AND "di"."event" = 'periodic'
3329 AND "ds"."initiative_id" = "initiative_id_v"
3330 AND "ds"."event" = 'periodic'
3331 AND "ds"."informed"
3332 AND "ds"."satisfied"
3333 )
3334 WHERE "id" = "initiative_id_v";
3335 FOR "suggestion_id_v" IN
3336 SELECT "id" FROM "suggestion"
3337 WHERE "initiative_id" = "initiative_id_v"
3338 LOOP
3339 UPDATE "suggestion" SET
3340 "minus2_unfulfilled_count" = (
3341 SELECT coalesce(sum("snapshot"."weight"), 0)
3342 FROM "issue" CROSS JOIN "opinion"
3343 JOIN "direct_interest_snapshot" AS "snapshot"
3344 ON "snapshot"."issue_id" = "issue"."id"
3345 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3346 AND "snapshot"."member_id" = "opinion"."member_id"
3347 WHERE "issue"."id" = "issue_id_p"
3348 AND "opinion"."suggestion_id" = "suggestion_id_v"
3349 AND "opinion"."degree" = -2
3350 AND "opinion"."fulfilled" = FALSE
3351 ),
3352 "minus2_fulfilled_count" = (
3353 SELECT coalesce(sum("snapshot"."weight"), 0)
3354 FROM "issue" CROSS JOIN "opinion"
3355 JOIN "direct_interest_snapshot" AS "snapshot"
3356 ON "snapshot"."issue_id" = "issue"."id"
3357 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3358 AND "snapshot"."member_id" = "opinion"."member_id"
3359 WHERE "issue"."id" = "issue_id_p"
3360 AND "opinion"."suggestion_id" = "suggestion_id_v"
3361 AND "opinion"."degree" = -2
3362 AND "opinion"."fulfilled" = TRUE
3363 ),
3364 "minus1_unfulfilled_count" = (
3365 SELECT coalesce(sum("snapshot"."weight"), 0)
3366 FROM "issue" CROSS JOIN "opinion"
3367 JOIN "direct_interest_snapshot" AS "snapshot"
3368 ON "snapshot"."issue_id" = "issue"."id"
3369 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3370 AND "snapshot"."member_id" = "opinion"."member_id"
3371 WHERE "issue"."id" = "issue_id_p"
3372 AND "opinion"."suggestion_id" = "suggestion_id_v"
3373 AND "opinion"."degree" = -1
3374 AND "opinion"."fulfilled" = FALSE
3375 ),
3376 "minus1_fulfilled_count" = (
3377 SELECT coalesce(sum("snapshot"."weight"), 0)
3378 FROM "issue" CROSS JOIN "opinion"
3379 JOIN "direct_interest_snapshot" AS "snapshot"
3380 ON "snapshot"."issue_id" = "issue"."id"
3381 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3382 AND "snapshot"."member_id" = "opinion"."member_id"
3383 WHERE "issue"."id" = "issue_id_p"
3384 AND "opinion"."suggestion_id" = "suggestion_id_v"
3385 AND "opinion"."degree" = -1
3386 AND "opinion"."fulfilled" = TRUE
3387 ),
3388 "plus1_unfulfilled_count" = (
3389 SELECT coalesce(sum("snapshot"."weight"), 0)
3390 FROM "issue" CROSS JOIN "opinion"
3391 JOIN "direct_interest_snapshot" AS "snapshot"
3392 ON "snapshot"."issue_id" = "issue"."id"
3393 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3394 AND "snapshot"."member_id" = "opinion"."member_id"
3395 WHERE "issue"."id" = "issue_id_p"
3396 AND "opinion"."suggestion_id" = "suggestion_id_v"
3397 AND "opinion"."degree" = 1
3398 AND "opinion"."fulfilled" = FALSE
3399 ),
3400 "plus1_fulfilled_count" = (
3401 SELECT coalesce(sum("snapshot"."weight"), 0)
3402 FROM "issue" CROSS JOIN "opinion"
3403 JOIN "direct_interest_snapshot" AS "snapshot"
3404 ON "snapshot"."issue_id" = "issue"."id"
3405 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3406 AND "snapshot"."member_id" = "opinion"."member_id"
3407 WHERE "issue"."id" = "issue_id_p"
3408 AND "opinion"."suggestion_id" = "suggestion_id_v"
3409 AND "opinion"."degree" = 1
3410 AND "opinion"."fulfilled" = TRUE
3411 ),
3412 "plus2_unfulfilled_count" = (
3413 SELECT coalesce(sum("snapshot"."weight"), 0)
3414 FROM "issue" CROSS JOIN "opinion"
3415 JOIN "direct_interest_snapshot" AS "snapshot"
3416 ON "snapshot"."issue_id" = "issue"."id"
3417 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3418 AND "snapshot"."member_id" = "opinion"."member_id"
3419 WHERE "issue"."id" = "issue_id_p"
3420 AND "opinion"."suggestion_id" = "suggestion_id_v"
3421 AND "opinion"."degree" = 2
3422 AND "opinion"."fulfilled" = FALSE
3423 ),
3424 "plus2_fulfilled_count" = (
3425 SELECT coalesce(sum("snapshot"."weight"), 0)
3426 FROM "issue" CROSS JOIN "opinion"
3427 JOIN "direct_interest_snapshot" AS "snapshot"
3428 ON "snapshot"."issue_id" = "issue"."id"
3429 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3430 AND "snapshot"."member_id" = "opinion"."member_id"
3431 WHERE "issue"."id" = "issue_id_p"
3432 AND "opinion"."suggestion_id" = "suggestion_id_v"
3433 AND "opinion"."degree" = 2
3434 AND "opinion"."fulfilled" = TRUE
3435 )
3436 WHERE "suggestion"."id" = "suggestion_id_v";
3437 END LOOP;
3438 END LOOP;
3439 RETURN;
3440 END;
3441 $$;
3443 COMMENT ON FUNCTION "create_snapshot"
3444 ( "issue"."id"%TYPE )
3445 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.';
3448 CREATE FUNCTION "set_snapshot_event"
3449 ( "issue_id_p" "issue"."id"%TYPE,
3450 "event_p" "snapshot_event" )
3451 RETURNS VOID
3452 LANGUAGE 'plpgsql' VOLATILE AS $$
3453 DECLARE
3454 "event_v" "issue"."latest_snapshot_event"%TYPE;
3455 BEGIN
3456 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
3457 WHERE "id" = "issue_id_p" FOR UPDATE;
3458 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
3459 WHERE "id" = "issue_id_p";
3460 UPDATE "direct_population_snapshot" SET "event" = "event_p"
3461 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3462 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
3463 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3464 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
3465 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3466 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
3467 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3468 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
3469 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3470 RETURN;
3471 END;
3472 $$;
3474 COMMENT ON FUNCTION "set_snapshot_event"
3475 ( "issue"."id"%TYPE,
3476 "snapshot_event" )
3477 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
3481 ---------------------
3482 -- Freezing issues --
3483 ---------------------
3485 CREATE FUNCTION "freeze_after_snapshot"
3486 ( "issue_id_p" "issue"."id"%TYPE )
3487 RETURNS VOID
3488 LANGUAGE 'plpgsql' VOLATILE AS $$
3489 DECLARE
3490 "issue_row" "issue"%ROWTYPE;
3491 "policy_row" "policy"%ROWTYPE;
3492 "initiative_row" "initiative"%ROWTYPE;
3493 BEGIN
3494 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3495 SELECT * INTO "policy_row"
3496 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3497 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
3498 FOR "initiative_row" IN
3499 SELECT * FROM "initiative"
3500 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3501 LOOP
3502 IF
3503 "initiative_row"."satisfied_supporter_count" > 0 AND
3504 "initiative_row"."satisfied_supporter_count" *
3505 "policy_row"."initiative_quorum_den" >=
3506 "issue_row"."population" * "policy_row"."initiative_quorum_num"
3507 THEN
3508 UPDATE "initiative" SET "admitted" = TRUE
3509 WHERE "id" = "initiative_row"."id";
3510 ELSE
3511 UPDATE "initiative" SET "admitted" = FALSE
3512 WHERE "id" = "initiative_row"."id";
3513 END IF;
3514 END LOOP;
3515 IF EXISTS (
3516 SELECT NULL FROM "initiative"
3517 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
3518 ) THEN
3519 UPDATE "issue" SET
3520 "state" = 'voting',
3521 "accepted" = coalesce("accepted", now()),
3522 "half_frozen" = coalesce("half_frozen", now()),
3523 "fully_frozen" = now()
3524 WHERE "id" = "issue_id_p";
3525 ELSE
3526 UPDATE "issue" SET
3527 "state" = 'canceled_no_initiative_admitted',
3528 "accepted" = coalesce("accepted", now()),
3529 "half_frozen" = coalesce("half_frozen", now()),
3530 "fully_frozen" = now(),
3531 "closed" = now(),
3532 "ranks_available" = TRUE
3533 WHERE "id" = "issue_id_p";
3534 -- NOTE: The following DELETE statements have effect only when
3535 -- issue state has been manipulated
3536 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
3537 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
3538 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3539 END IF;
3540 RETURN;
3541 END;
3542 $$;
3544 COMMENT ON FUNCTION "freeze_after_snapshot"
3545 ( "issue"."id"%TYPE )
3546 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
3549 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
3550 RETURNS VOID
3551 LANGUAGE 'plpgsql' VOLATILE AS $$
3552 DECLARE
3553 "issue_row" "issue"%ROWTYPE;
3554 BEGIN
3555 PERFORM "create_snapshot"("issue_id_p");
3556 PERFORM "freeze_after_snapshot"("issue_id_p");
3557 RETURN;
3558 END;
3559 $$;
3561 COMMENT ON FUNCTION "manual_freeze"
3562 ( "issue"."id"%TYPE )
3563 IS 'Freeze an issue manually (fully) and start voting';
3567 -----------------------
3568 -- Counting of votes --
3569 -----------------------
3572 CREATE FUNCTION "weight_of_added_vote_delegations"
3573 ( "issue_id_p" "issue"."id"%TYPE,
3574 "member_id_p" "member"."id"%TYPE,
3575 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
3576 RETURNS "direct_voter"."weight"%TYPE
3577 LANGUAGE 'plpgsql' VOLATILE AS $$
3578 DECLARE
3579 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3580 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
3581 "weight_v" INT4;
3582 "sub_weight_v" INT4;
3583 BEGIN
3584 "weight_v" := 0;
3585 FOR "issue_delegation_row" IN
3586 SELECT * FROM "issue_delegation"
3587 WHERE "trustee_id" = "member_id_p"
3588 AND "issue_id" = "issue_id_p"
3589 LOOP
3590 IF NOT EXISTS (
3591 SELECT NULL FROM "direct_voter"
3592 WHERE "member_id" = "issue_delegation_row"."truster_id"
3593 AND "issue_id" = "issue_id_p"
3594 ) AND NOT EXISTS (
3595 SELECT NULL FROM "delegating_voter"
3596 WHERE "member_id" = "issue_delegation_row"."truster_id"
3597 AND "issue_id" = "issue_id_p"
3598 ) THEN
3599 "delegate_member_ids_v" :=
3600 "member_id_p" || "delegate_member_ids_p";
3601 INSERT INTO "delegating_voter" (
3602 "issue_id",
3603 "member_id",
3604 "scope",
3605 "delegate_member_ids"
3606 ) VALUES (
3607 "issue_id_p",
3608 "issue_delegation_row"."truster_id",
3609 "issue_delegation_row"."scope",
3610 "delegate_member_ids_v"
3611 );
3612 "sub_weight_v" := 1 +
3613 "weight_of_added_vote_delegations"(
3614 "issue_id_p",
3615 "issue_delegation_row"."truster_id",
3616 "delegate_member_ids_v"
3617 );
3618 UPDATE "delegating_voter"
3619 SET "weight" = "sub_weight_v"
3620 WHERE "issue_id" = "issue_id_p"
3621 AND "member_id" = "issue_delegation_row"."truster_id";
3622 "weight_v" := "weight_v" + "sub_weight_v";
3623 END IF;
3624 END LOOP;
3625 RETURN "weight_v";
3626 END;
3627 $$;
3629 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
3630 ( "issue"."id"%TYPE,
3631 "member"."id"%TYPE,
3632 "delegating_voter"."delegate_member_ids"%TYPE )
3633 IS 'Helper function for "add_vote_delegations" function';
3636 CREATE FUNCTION "add_vote_delegations"
3637 ( "issue_id_p" "issue"."id"%TYPE )
3638 RETURNS VOID
3639 LANGUAGE 'plpgsql' VOLATILE AS $$
3640 DECLARE
3641 "member_id_v" "member"."id"%TYPE;
3642 BEGIN
3643 FOR "member_id_v" IN
3644 SELECT "member_id" FROM "direct_voter"
3645 WHERE "issue_id" = "issue_id_p"
3646 LOOP
3647 UPDATE "direct_voter" SET
3648 "weight" = "weight" + "weight_of_added_vote_delegations"(
3649 "issue_id_p",
3650 "member_id_v",
3651 '{}'
3652 )
3653 WHERE "member_id" = "member_id_v"
3654 AND "issue_id" = "issue_id_p";
3655 END LOOP;
3656 RETURN;
3657 END;
3658 $$;
3660 COMMENT ON FUNCTION "add_vote_delegations"
3661 ( "issue_id_p" "issue"."id"%TYPE )
3662 IS 'Helper function for "close_voting" function';
3665 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
3666 RETURNS VOID
3667 LANGUAGE 'plpgsql' VOLATILE AS $$
3668 DECLARE
3669 "area_id_v" "area"."id"%TYPE;
3670 "unit_id_v" "unit"."id"%TYPE;
3671 "member_id_v" "member"."id"%TYPE;
3672 BEGIN
3673 PERFORM "lock_issue"("issue_id_p");
3674 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
3675 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
3676 -- delete delegating votes (in cases of manual reset of issue state):
3677 DELETE FROM "delegating_voter"
3678 WHERE "issue_id" = "issue_id_p";
3679 -- delete votes from non-privileged voters:
3680 DELETE FROM "direct_voter"
3681 USING (
3682 SELECT
3683 "direct_voter"."member_id"
3684 FROM "direct_voter"
3685 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
3686 LEFT JOIN "privilege"
3687 ON "privilege"."unit_id" = "unit_id_v"
3688 AND "privilege"."member_id" = "direct_voter"."member_id"
3689 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
3690 "member"."active" = FALSE OR
3691 "privilege"."voting_right" ISNULL OR
3692 "privilege"."voting_right" = FALSE
3693 )
3694 ) AS "subquery"
3695 WHERE "direct_voter"."issue_id" = "issue_id_p"
3696 AND "direct_voter"."member_id" = "subquery"."member_id";
3697 -- consider delegations:
3698 UPDATE "direct_voter" SET "weight" = 1
3699 WHERE "issue_id" = "issue_id_p";
3700 PERFORM "add_vote_delegations"("issue_id_p");
3701 -- set voter count and mark issue as being calculated:
3702 UPDATE "issue" SET
3703 "state" = 'calculation',
3704 "closed" = now(),
3705 "voter_count" = (
3706 SELECT coalesce(sum("weight"), 0)
3707 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
3708 )
3709 WHERE "id" = "issue_id_p";
3710 -- materialize battle_view:
3711 -- NOTE: "closed" column of issue must be set at this point
3712 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3713 INSERT INTO "battle" (
3714 "issue_id",
3715 "winning_initiative_id", "losing_initiative_id",
3716 "count"
3717 ) SELECT
3718 "issue_id",
3719 "winning_initiative_id", "losing_initiative_id",
3720 "count"
3721 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
3722 -- copy "positive_votes" and "negative_votes" from "battle" table:
3723 UPDATE "initiative" SET
3724 "positive_votes" = "battle_win"."count",
3725 "negative_votes" = "battle_lose"."count"
3726 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
3727 WHERE
3728 "battle_win"."issue_id" = "issue_id_p" AND
3729 "battle_win"."winning_initiative_id" = "initiative"."id" AND
3730 "battle_win"."losing_initiative_id" ISNULL AND
3731 "battle_lose"."issue_id" = "issue_id_p" AND
3732 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
3733 "battle_lose"."winning_initiative_id" ISNULL;
3734 END;
3735 $$;
3737 COMMENT ON FUNCTION "close_voting"
3738 ( "issue"."id"%TYPE )
3739 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.';
3742 CREATE FUNCTION "defeat_strength"
3743 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
3744 RETURNS INT8
3745 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3746 BEGIN
3747 IF "positive_votes_p" > "negative_votes_p" THEN
3748 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
3749 ELSIF "positive_votes_p" = "negative_votes_p" THEN
3750 RETURN 0;
3751 ELSE
3752 RETURN -1;
3753 END IF;
3754 END;
3755 $$;
3757 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';
3760 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
3761 RETURNS VOID
3762 LANGUAGE 'plpgsql' VOLATILE AS $$
3763 DECLARE
3764 "issue_row" "issue"%ROWTYPE;
3765 "policy_row" "policy"%ROWTYPE;
3766 "dimension_v" INTEGER;
3767 "vote_matrix" INT4[][]; -- absolute votes
3768 "matrix" INT8[][]; -- defeat strength / best paths
3769 "i" INTEGER;
3770 "j" INTEGER;
3771 "k" INTEGER;
3772 "battle_row" "battle"%ROWTYPE;
3773 "rank_ary" INT4[];
3774 "rank_v" INT4;
3775 "done_v" INTEGER;
3776 "winners_ary" INTEGER[];
3777 "initiative_id_v" "initiative"."id"%TYPE;
3778 BEGIN
3779 SELECT * INTO "issue_row"
3780 FROM "issue" WHERE "id" = "issue_id_p"
3781 FOR UPDATE;
3782 SELECT * INTO "policy_row"
3783 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3784 SELECT count(1) INTO "dimension_v"
3785 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
3786 -- Create "vote_matrix" with absolute number of votes in pairwise
3787 -- comparison:
3788 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
3789 "i" := 1;
3790 "j" := 2;
3791 FOR "battle_row" IN
3792 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
3793 ORDER BY
3794 "winning_initiative_id" NULLS LAST,
3795 "losing_initiative_id" NULLS LAST
3796 LOOP
3797 "vote_matrix"["i"]["j"] := "battle_row"."count";
3798 IF "j" = "dimension_v" THEN
3799 "i" := "i" + 1;
3800 "j" := 1;
3801 ELSE
3802 "j" := "j" + 1;
3803 IF "j" = "i" THEN
3804 "j" := "j" + 1;
3805 END IF;
3806 END IF;
3807 END LOOP;
3808 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
3809 RAISE EXCEPTION 'Wrong battle count (should not happen)';
3810 END IF;
3811 -- Store defeat strengths in "matrix" using "defeat_strength"
3812 -- function:
3813 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
3814 "i" := 1;
3815 LOOP
3816 "j" := 1;
3817 LOOP
3818 IF "i" != "j" THEN
3819 "matrix"["i"]["j"] := "defeat_strength"(
3820 "vote_matrix"["i"]["j"],
3821 "vote_matrix"["j"]["i"]
3822 );
3823 END IF;
3824 EXIT WHEN "j" = "dimension_v";
3825 "j" := "j" + 1;
3826 END LOOP;
3827 EXIT WHEN "i" = "dimension_v";
3828 "i" := "i" + 1;
3829 END LOOP;
3830 -- Find best paths:
3831 "i" := 1;
3832 LOOP
3833 "j" := 1;
3834 LOOP
3835 IF "i" != "j" THEN
3836 "k" := 1;
3837 LOOP
3838 IF "i" != "k" AND "j" != "k" THEN
3839 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
3840 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
3841 "matrix"["j"]["k"] := "matrix"["j"]["i"];
3842 END IF;
3843 ELSE
3844 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
3845 "matrix"["j"]["k"] := "matrix"["i"]["k"];
3846 END IF;
3847 END IF;
3848 END IF;
3849 EXIT WHEN "k" = "dimension_v";
3850 "k" := "k" + 1;
3851 END LOOP;
3852 END IF;
3853 EXIT WHEN "j" = "dimension_v";
3854 "j" := "j" + 1;
3855 END LOOP;
3856 EXIT WHEN "i" = "dimension_v";
3857 "i" := "i" + 1;
3858 END LOOP;
3859 -- Determine order of winners:
3860 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
3861 "rank_v" := 1;
3862 "done_v" := 0;
3863 LOOP
3864 "winners_ary" := '{}';
3865 "i" := 1;
3866 LOOP
3867 IF "rank_ary"["i"] ISNULL THEN
3868 "j" := 1;
3869 LOOP
3870 IF
3871 "i" != "j" AND
3872 "rank_ary"["j"] ISNULL AND
3873 "matrix"["j"]["i"] > "matrix"["i"]["j"]
3874 THEN
3875 -- someone else is better
3876 EXIT;
3877 END IF;
3878 IF "j" = "dimension_v" THEN
3879 -- noone is better
3880 "winners_ary" := "winners_ary" || "i";
3881 EXIT;
3882 END IF;
3883 "j" := "j" + 1;
3884 END LOOP;
3885 END IF;
3886 EXIT WHEN "i" = "dimension_v";
3887 "i" := "i" + 1;
3888 END LOOP;
3889 "i" := 1;
3890 LOOP
3891 "rank_ary"["winners_ary"["i"]] := "rank_v";
3892 "done_v" := "done_v" + 1;
3893 EXIT WHEN "i" = array_upper("winners_ary", 1);
3894 "i" := "i" + 1;
3895 END LOOP;
3896 EXIT WHEN "done_v" = "dimension_v";
3897 "rank_v" := "rank_v" + 1;
3898 END LOOP;
3899 -- write preliminary results:
3900 "i" := 1;
3901 FOR "initiative_id_v" IN
3902 SELECT "id" FROM "initiative"
3903 WHERE "issue_id" = "issue_id_p" AND "admitted"
3904 ORDER BY "id"
3905 LOOP
3906 UPDATE "initiative" SET
3907 "direct_majority" =
3908 CASE WHEN "policy_row"."direct_majority_strict" THEN
3909 "positive_votes" * "policy_row"."direct_majority_den" >
3910 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
3911 ELSE
3912 "positive_votes" * "policy_row"."direct_majority_den" >=
3913 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
3914 END
3915 AND "positive_votes" >= "policy_row"."direct_majority_positive"
3916 AND "issue_row"."voter_count"-"negative_votes" >=
3917 "policy_row"."direct_majority_non_negative",
3918 "indirect_majority" =
3919 CASE WHEN "policy_row"."indirect_majority_strict" THEN
3920 "positive_votes" * "policy_row"."indirect_majority_den" >
3921 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
3922 ELSE
3923 "positive_votes" * "policy_row"."indirect_majority_den" >=
3924 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
3925 END
3926 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
3927 AND "issue_row"."voter_count"-"negative_votes" >=
3928 "policy_row"."indirect_majority_non_negative",
3929 "schulze_rank" = "rank_ary"["i"],
3930 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
3931 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
3932 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
3933 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0,
3934 "eligible" = FALSE,
3935 "winner" = FALSE,
3936 "rank" = NULL -- NOTE: in cases of manual reset of issue state
3937 WHERE "id" = "initiative_id_v";
3938 "i" := "i" + 1;
3939 END LOOP;
3940 IF "i" != "dimension_v" THEN
3941 RAISE EXCEPTION 'Wrong winner count (should not happen)';
3942 END IF;
3943 -- take indirect majorities into account:
3944 LOOP
3945 UPDATE "initiative" SET "indirect_majority" = TRUE
3946 FROM (
3947 SELECT "new_initiative"."id" AS "initiative_id"
3948 FROM "initiative" "old_initiative"
3949 JOIN "initiative" "new_initiative"
3950 ON "new_initiative"."issue_id" = "issue_id_p"
3951 AND "new_initiative"."indirect_majority" = FALSE
3952 JOIN "battle" "battle_win"
3953 ON "battle_win"."issue_id" = "issue_id_p"
3954 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
3955 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
3956 JOIN "battle" "battle_lose"
3957 ON "battle_lose"."issue_id" = "issue_id_p"
3958 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
3959 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
3960 WHERE "old_initiative"."issue_id" = "issue_id_p"
3961 AND "old_initiative"."indirect_majority" = TRUE
3962 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
3963 "battle_win"."count" * "policy_row"."indirect_majority_den" >
3964 "policy_row"."indirect_majority_num" *
3965 ("battle_win"."count"+"battle_lose"."count")
3966 ELSE
3967 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
3968 "policy_row"."indirect_majority_num" *
3969 ("battle_win"."count"+"battle_lose"."count")
3970 END
3971 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
3972 AND "issue_row"."voter_count"-"battle_lose"."count" >=
3973 "policy_row"."indirect_majority_non_negative"
3974 ) AS "subquery"
3975 WHERE "id" = "subquery"."initiative_id";
3976 EXIT WHEN NOT FOUND;
3977 END LOOP;
3978 -- set "multistage_majority" for remaining matching initiatives:
3979 UPDATE "initiative" SET "multistage_majority" = TRUE
3980 FROM (
3981 SELECT "losing_initiative"."id" AS "initiative_id"
3982 FROM "initiative" "losing_initiative"
3983 JOIN "initiative" "winning_initiative"
3984 ON "winning_initiative"."issue_id" = "issue_id_p"
3985 AND "winning_initiative"."admitted"
3986 JOIN "battle" "battle_win"
3987 ON "battle_win"."issue_id" = "issue_id_p"
3988 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
3989 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
3990 JOIN "battle" "battle_lose"
3991 ON "battle_lose"."issue_id" = "issue_id_p"
3992 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
3993 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
3994 WHERE "losing_initiative"."issue_id" = "issue_id_p"
3995 AND "losing_initiative"."admitted"
3996 AND "winning_initiative"."schulze_rank" <
3997 "losing_initiative"."schulze_rank"
3998 AND "battle_win"."count" > "battle_lose"."count"
3999 AND (
4000 "battle_win"."count" > "winning_initiative"."positive_votes" OR
4001 "battle_lose"."count" < "losing_initiative"."negative_votes" )
4002 ) AS "subquery"
4003 WHERE "id" = "subquery"."initiative_id";
4004 -- mark eligible initiatives:
4005 UPDATE "initiative" SET "eligible" = TRUE
4006 WHERE "issue_id" = "issue_id_p"
4007 AND "initiative"."direct_majority"
4008 AND "initiative"."indirect_majority"
4009 AND "initiative"."better_than_status_quo"
4010 AND (
4011 "policy_row"."no_multistage_majority" = FALSE OR
4012 "initiative"."multistage_majority" = FALSE )
4013 AND (
4014 "policy_row"."no_reverse_beat_path" = FALSE OR
4015 "initiative"."reverse_beat_path" = FALSE );
4016 -- mark final winner:
4017 UPDATE "initiative" SET "winner" = TRUE
4018 FROM (
4019 SELECT "id" AS "initiative_id"
4020 FROM "initiative"
4021 WHERE "issue_id" = "issue_id_p" AND "eligible"
4022 ORDER BY
4023 "schulze_rank",
4024 "vote_ratio"("positive_votes", "negative_votes"),
4025 "id"
4026 LIMIT 1
4027 ) AS "subquery"
4028 WHERE "id" = "subquery"."initiative_id";
4029 -- write (final) ranks:
4030 "rank_v" := 1;
4031 FOR "initiative_id_v" IN
4032 SELECT "id"
4033 FROM "initiative"
4034 WHERE "issue_id" = "issue_id_p" AND "admitted"
4035 ORDER BY
4036 "winner" DESC,
4037 "eligible" DESC,
4038 "schulze_rank",
4039 "vote_ratio"("positive_votes", "negative_votes"),
4040 "id"
4041 LOOP
4042 UPDATE "initiative" SET "rank" = "rank_v"
4043 WHERE "id" = "initiative_id_v";
4044 "rank_v" := "rank_v" + 1;
4045 END LOOP;
4046 -- set schulze rank of status quo and mark issue as finished:
4047 UPDATE "issue" SET
4048 "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
4049 "state" =
4050 CASE WHEN EXISTS (
4051 SELECT NULL FROM "initiative"
4052 WHERE "issue_id" = "issue_id_p" AND "winner"
4053 ) THEN
4054 'finished_with_winner'::"issue_state"
4055 ELSE
4056 'finished_without_winner'::"issue_state"
4057 END,
4058 "ranks_available" = TRUE
4059 WHERE "id" = "issue_id_p";
4060 RETURN;
4061 END;
4062 $$;
4064 COMMENT ON FUNCTION "calculate_ranks"
4065 ( "issue"."id"%TYPE )
4066 IS 'Determine ranking (Votes have to be counted first)';
4070 -----------------------------
4071 -- Automatic state changes --
4072 -----------------------------
4075 CREATE FUNCTION "check_issue"
4076 ( "issue_id_p" "issue"."id"%TYPE )
4077 RETURNS VOID
4078 LANGUAGE 'plpgsql' VOLATILE AS $$
4079 DECLARE
4080 "issue_row" "issue"%ROWTYPE;
4081 "policy_row" "policy"%ROWTYPE;
4082 BEGIN
4083 PERFORM "lock_issue"("issue_id_p");
4084 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4085 -- only process open issues:
4086 IF "issue_row"."closed" ISNULL THEN
4087 SELECT * INTO "policy_row" FROM "policy"
4088 WHERE "id" = "issue_row"."policy_id";
4089 -- create a snapshot, unless issue is already fully frozen:
4090 IF "issue_row"."fully_frozen" ISNULL THEN
4091 PERFORM "create_snapshot"("issue_id_p");
4092 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4093 END IF;
4094 -- eventually close or accept issues, which have not been accepted:
4095 IF "issue_row"."accepted" ISNULL THEN
4096 IF EXISTS (
4097 SELECT NULL FROM "initiative"
4098 WHERE "issue_id" = "issue_id_p"
4099 AND "supporter_count" > 0
4100 AND "supporter_count" * "policy_row"."issue_quorum_den"
4101 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
4102 ) THEN
4103 -- accept issues, if supporter count is high enough
4104 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
4105 -- NOTE: "issue_row" used later
4106 "issue_row"."state" := 'discussion';
4107 "issue_row"."accepted" := now();
4108 UPDATE "issue" SET
4109 "state" = "issue_row"."state",
4110 "accepted" = "issue_row"."accepted"
4111 WHERE "id" = "issue_row"."id";
4112 ELSIF
4113 now() >= "issue_row"."created" + "issue_row"."admission_time"
4114 THEN
4115 -- close issues, if admission time has expired
4116 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
4117 UPDATE "issue" SET
4118 "state" = 'canceled_issue_not_accepted',
4119 "closed" = now()
4120 WHERE "id" = "issue_row"."id";
4121 END IF;
4122 END IF;
4123 -- eventually half freeze issues:
4124 IF
4125 -- NOTE: issue can't be closed at this point, if it has been accepted
4126 "issue_row"."accepted" NOTNULL AND
4127 "issue_row"."half_frozen" ISNULL
4128 THEN
4129 IF
4130 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
4131 THEN
4132 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
4133 -- NOTE: "issue_row" used later
4134 "issue_row"."state" := 'verification';
4135 "issue_row"."half_frozen" := now();
4136 UPDATE "issue" SET
4137 "state" = "issue_row"."state",
4138 "half_frozen" = "issue_row"."half_frozen"
4139 WHERE "id" = "issue_row"."id";
4140 END IF;
4141 END IF;
4142 -- close issues after some time, if all initiatives have been revoked:
4143 IF
4144 "issue_row"."closed" ISNULL AND
4145 NOT EXISTS (
4146 -- all initiatives are revoked
4147 SELECT NULL FROM "initiative"
4148 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
4149 ) AND (
4150 -- and issue has not been accepted yet
4151 "issue_row"."accepted" ISNULL OR
4152 NOT EXISTS (
4153 -- or no initiatives have been revoked lately
4154 SELECT NULL FROM "initiative"
4155 WHERE "issue_id" = "issue_id_p"
4156 AND now() < "revoked" + "issue_row"."verification_time"
4157 ) OR (
4158 -- or verification time has elapsed
4159 "issue_row"."half_frozen" NOTNULL AND
4160 "issue_row"."fully_frozen" ISNULL AND
4161 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
4162 )
4163 )
4164 THEN
4165 -- NOTE: "issue_row" used later
4166 IF "issue_row"."accepted" ISNULL THEN
4167 "issue_row"."state" := 'canceled_revoked_before_accepted';
4168 ELSIF "issue_row"."half_frozen" ISNULL THEN
4169 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
4170 ELSE
4171 "issue_row"."state" := 'canceled_after_revocation_during_verification';
4172 END IF;
4173 "issue_row"."closed" := now();
4174 UPDATE "issue" SET
4175 "state" = "issue_row"."state",
4176 "closed" = "issue_row"."closed"
4177 WHERE "id" = "issue_row"."id";
4178 END IF;
4179 -- fully freeze issue after verification time:
4180 IF
4181 "issue_row"."half_frozen" NOTNULL AND
4182 "issue_row"."fully_frozen" ISNULL AND
4183 "issue_row"."closed" ISNULL AND
4184 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
4185 THEN
4186 PERFORM "freeze_after_snapshot"("issue_id_p");
4187 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
4188 END IF;
4189 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4190 -- close issue by calling close_voting(...) after voting time:
4191 IF
4192 "issue_row"."closed" ISNULL AND
4193 "issue_row"."fully_frozen" NOTNULL AND
4194 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
4195 THEN
4196 PERFORM "close_voting"("issue_id_p");
4197 -- calculate ranks will not consume much time and can be done now
4198 PERFORM "calculate_ranks"("issue_id_p");
4199 END IF;
4200 END IF;
4201 RETURN;
4202 END;
4203 $$;
4205 COMMENT ON FUNCTION "check_issue"
4206 ( "issue"."id"%TYPE )
4207 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.';
4210 CREATE FUNCTION "check_everything"()
4211 RETURNS VOID
4212 LANGUAGE 'plpgsql' VOLATILE AS $$
4213 DECLARE
4214 "issue_id_v" "issue"."id"%TYPE;
4215 BEGIN
4216 DELETE FROM "expired_session";
4217 PERFORM "check_activity"();
4218 PERFORM "calculate_member_counts"();
4219 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
4220 PERFORM "check_issue"("issue_id_v");
4221 END LOOP;
4222 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
4223 PERFORM "calculate_ranks"("issue_id_v");
4224 END LOOP;
4225 RETURN;
4226 END;
4227 $$;
4229 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.';
4233 ----------------------
4234 -- Deletion of data --
4235 ----------------------
4238 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
4239 RETURNS VOID
4240 LANGUAGE 'plpgsql' VOLATILE AS $$
4241 DECLARE
4242 "issue_row" "issue"%ROWTYPE;
4243 BEGIN
4244 SELECT * INTO "issue_row"
4245 FROM "issue" WHERE "id" = "issue_id_p"
4246 FOR UPDATE;
4247 IF "issue_row"."cleaned" ISNULL THEN
4248 UPDATE "issue" SET
4249 "state" = 'voting',
4250 "closed" = NULL,
4251 "ranks_available" = FALSE
4252 WHERE "id" = "issue_id_p";
4253 DELETE FROM "voting_comment"
4254 WHERE "issue_id" = "issue_id_p";
4255 DELETE FROM "delegating_voter"
4256 WHERE "issue_id" = "issue_id_p";
4257 DELETE FROM "direct_voter"
4258 WHERE "issue_id" = "issue_id_p";
4259 DELETE FROM "delegating_interest_snapshot"
4260 WHERE "issue_id" = "issue_id_p";
4261 DELETE FROM "direct_interest_snapshot"
4262 WHERE "issue_id" = "issue_id_p";
4263 DELETE FROM "delegating_population_snapshot"
4264 WHERE "issue_id" = "issue_id_p";
4265 DELETE FROM "direct_population_snapshot"
4266 WHERE "issue_id" = "issue_id_p";
4267 DELETE FROM "non_voter"
4268 WHERE "issue_id" = "issue_id_p";
4269 DELETE FROM "delegation"
4270 WHERE "issue_id" = "issue_id_p";
4271 DELETE FROM "supporter"
4272 WHERE "issue_id" = "issue_id_p";
4273 UPDATE "issue" SET
4274 "state" = "issue_row"."state",
4275 "closed" = "issue_row"."closed",
4276 "ranks_available" = "issue_row"."ranks_available",
4277 "cleaned" = now()
4278 WHERE "id" = "issue_id_p";
4279 END IF;
4280 RETURN;
4281 END;
4282 $$;
4284 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
4287 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
4288 RETURNS VOID
4289 LANGUAGE 'plpgsql' VOLATILE AS $$
4290 BEGIN
4291 UPDATE "member" SET
4292 "last_login" = NULL,
4293 "login" = NULL,
4294 "password" = NULL,
4295 "locked" = TRUE,
4296 "active" = FALSE,
4297 "notify_email" = NULL,
4298 "notify_email_unconfirmed" = NULL,
4299 "notify_email_secret" = NULL,
4300 "notify_email_secret_expiry" = NULL,
4301 "notify_email_lock_expiry" = NULL,
4302 "password_reset_secret" = NULL,
4303 "password_reset_secret_expiry" = NULL,
4304 "organizational_unit" = NULL,
4305 "internal_posts" = NULL,
4306 "realname" = NULL,
4307 "birthday" = NULL,
4308 "address" = NULL,
4309 "email" = NULL,
4310 "xmpp_address" = NULL,
4311 "website" = NULL,
4312 "phone" = NULL,
4313 "mobile_phone" = NULL,
4314 "profession" = NULL,
4315 "external_memberships" = NULL,
4316 "external_posts" = NULL,
4317 "statement" = NULL
4318 WHERE "id" = "member_id_p";
4319 -- "text_search_data" is updated by triggers
4320 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
4321 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
4322 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
4323 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
4324 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
4325 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
4326 DELETE FROM "session" WHERE "member_id" = "member_id_p";
4327 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
4328 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
4329 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
4330 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
4331 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
4332 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
4333 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
4334 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
4335 DELETE FROM "direct_voter" USING "issue"
4336 WHERE "direct_voter"."issue_id" = "issue"."id"
4337 AND "issue"."closed" ISNULL
4338 AND "member_id" = "member_id_p";
4339 RETURN;
4340 END;
4341 $$;
4343 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)';
4346 CREATE FUNCTION "delete_private_data"()
4347 RETURNS VOID
4348 LANGUAGE 'plpgsql' VOLATILE AS $$
4349 BEGIN
4350 DELETE FROM "member" WHERE "activated" ISNULL;
4351 UPDATE "member" SET
4352 "invite_code" = NULL,
4353 "invite_code_expiry" = NULL,
4354 "admin_comment" = NULL,
4355 "last_login" = NULL,
4356 "login" = NULL,
4357 "password" = NULL,
4358 "lang" = NULL,
4359 "notify_email" = NULL,
4360 "notify_email_unconfirmed" = NULL,
4361 "notify_email_secret" = NULL,
4362 "notify_email_secret_expiry" = NULL,
4363 "notify_email_lock_expiry" = NULL,
4364 "notify_level" = NULL,
4365 "password_reset_secret" = NULL,
4366 "password_reset_secret_expiry" = NULL,
4367 "organizational_unit" = NULL,
4368 "internal_posts" = NULL,
4369 "realname" = NULL,
4370 "birthday" = NULL,
4371 "address" = NULL,
4372 "email" = NULL,
4373 "xmpp_address" = NULL,
4374 "website" = NULL,
4375 "phone" = NULL,
4376 "mobile_phone" = NULL,
4377 "profession" = NULL,
4378 "external_memberships" = NULL,
4379 "external_posts" = NULL,
4380 "formatting_engine" = NULL,
4381 "statement" = NULL;
4382 -- "text_search_data" is updated by triggers
4383 DELETE FROM "setting";
4384 DELETE FROM "setting_map";
4385 DELETE FROM "member_relation_setting";
4386 DELETE FROM "member_image";
4387 DELETE FROM "contact";
4388 DELETE FROM "ignored_member";
4389 DELETE FROM "session";
4390 DELETE FROM "area_setting";
4391 DELETE FROM "issue_setting";
4392 DELETE FROM "ignored_initiative";
4393 DELETE FROM "initiative_setting";
4394 DELETE FROM "suggestion_setting";
4395 DELETE FROM "non_voter";
4396 DELETE FROM "direct_voter" USING "issue"
4397 WHERE "direct_voter"."issue_id" = "issue"."id"
4398 AND "issue"."closed" ISNULL;
4399 RETURN;
4400 END;
4401 $$;
4403 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.';
4407 COMMIT;