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