liquid_feedback_core
view core.sql @ 209:37bf1dcdb37a
merge
| author | jbe |
|---|---|
| date | Mon Nov 14 14:09:05 2011 +0100 (2011-11-14) |
| parents | 73d358fe20c7 |
| children | c8c5ac91f30a |
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.0.0', 2, 0, 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 "admin_comment" TEXT,
89 "activated" TIMESTAMPTZ,
90 "last_activity" DATE,
91 "last_login" TIMESTAMPTZ,
92 "login" TEXT UNIQUE,
93 "password" TEXT,
94 "locked" BOOLEAN NOT NULL DEFAULT FALSE,
95 "active" BOOLEAN NOT NULL DEFAULT FALSE,
96 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
97 "notify_email" TEXT,
98 "notify_email_unconfirmed" TEXT,
99 "notify_email_secret" TEXT UNIQUE,
100 "notify_email_secret_expiry" TIMESTAMPTZ,
101 "notify_email_lock_expiry" TIMESTAMPTZ,
102 "notify_level" "notify_level" NOT NULL DEFAULT 'none',
103 "notify_event_id" INT8,
104 "password_reset_secret" TEXT UNIQUE,
105 "password_reset_secret_expiry" TIMESTAMPTZ,
106 "name" TEXT NOT NULL UNIQUE,
107 "identification" TEXT UNIQUE,
108 "organizational_unit" TEXT,
109 "internal_posts" TEXT,
110 "realname" TEXT,
111 "birthday" DATE,
112 "address" TEXT,
113 "email" TEXT,
114 "xmpp_address" TEXT,
115 "website" TEXT,
116 "phone" TEXT,
117 "mobile_phone" TEXT,
118 "profession" TEXT,
119 "external_memberships" TEXT,
120 "external_posts" TEXT,
121 "formatting_engine" TEXT,
122 "statement" TEXT,
123 "text_search_data" TSVECTOR,
124 CONSTRAINT "active_requires_activated_and_last_activity"
125 CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)) );
126 CREATE INDEX "member_active_idx" ON "member" ("active");
127 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
128 CREATE TRIGGER "update_text_search_data"
129 BEFORE INSERT OR UPDATE ON "member"
130 FOR EACH ROW EXECUTE PROCEDURE
131 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
132 "name", "identification", "organizational_unit", "internal_posts",
133 "realname", "external_memberships", "external_posts", "statement" );
135 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
137 COMMENT ON COLUMN "member"."created" IS 'Creation of member record and/or invite code';
138 COMMENT ON COLUMN "member"."invite_code" IS 'Optional invite code, to allow a member to initialize his/her account the first time';
139 COMMENT ON COLUMN "member"."admin_comment" IS 'Hidden comment for administrative purposes';
140 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';
141 COMMENT ON COLUMN "member"."last_activity" IS 'Date of last activity of member; required to be set for "active" members';
142 COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login';
143 COMMENT ON COLUMN "member"."login" IS 'Login name';
144 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
145 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
146 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".';
147 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
148 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
149 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
150 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
151 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
152 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
153 COMMENT ON COLUMN "member"."notify_level" IS 'Selects which event notifications are to be sent to the "notify_email" mail address';
154 COMMENT ON COLUMN "member"."notify_event_id" IS 'Latest "id" of an "event" the member was notified about';
155 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member';
156 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
157 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
158 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
159 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
160 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
161 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
162 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
163 COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"';
164 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his/her profile';
167 CREATE TYPE "application_access_level" AS ENUM
168 ('member', 'full', 'pseudonymous', 'anonymous');
170 COMMENT ON TYPE "application_access_level" IS 'Access privileges for applications using the API';
173 CREATE TABLE "member_application" (
174 "id" SERIAL8 PRIMARY KEY,
175 UNIQUE ("member_id", "name"),
176 "member_id" INT4 NOT NULL REFERENCES "member" ("id")
177 ON DELETE CASCADE ON UPDATE CASCADE,
178 "name" TEXT NOT NULL,
179 "comment" TEXT,
180 "access_level" "application_access_level" NOT NULL,
181 "key" TEXT NOT NULL UNIQUE,
182 "last_usage" TIMESTAMPTZ );
184 COMMENT ON TABLE "member_application" IS 'Registered application being allowed to use the API';
187 CREATE TABLE "member_history" (
188 "id" SERIAL8 PRIMARY KEY,
189 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
190 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
191 "active" BOOLEAN NOT NULL,
192 "name" TEXT NOT NULL );
193 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
195 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
197 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
198 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
201 CREATE TABLE "rendered_member_statement" (
202 PRIMARY KEY ("member_id", "format"),
203 "member_id" INT8 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
204 "format" TEXT,
205 "content" TEXT NOT NULL );
207 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)';
210 CREATE TABLE "setting" (
211 PRIMARY KEY ("member_id", "key"),
212 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
213 "key" TEXT NOT NULL,
214 "value" TEXT NOT NULL );
215 CREATE INDEX "setting_key_idx" ON "setting" ("key");
217 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
219 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
222 CREATE TABLE "setting_map" (
223 PRIMARY KEY ("member_id", "key", "subkey"),
224 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
225 "key" TEXT NOT NULL,
226 "subkey" TEXT NOT NULL,
227 "value" TEXT NOT NULL );
228 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
230 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
232 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
233 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
234 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
237 CREATE TABLE "member_relation_setting" (
238 PRIMARY KEY ("member_id", "key", "other_member_id"),
239 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
240 "key" TEXT NOT NULL,
241 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
242 "value" TEXT NOT NULL );
244 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
247 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
249 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
252 CREATE TABLE "member_image" (
253 PRIMARY KEY ("member_id", "image_type", "scaled"),
254 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
255 "image_type" "member_image_type",
256 "scaled" BOOLEAN,
257 "content_type" TEXT,
258 "data" BYTEA NOT NULL );
260 COMMENT ON TABLE "member_image" IS 'Images of members';
262 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
265 CREATE TABLE "member_count" (
266 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
267 "total_count" INT4 NOT NULL );
269 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';
271 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
272 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
275 CREATE TABLE "contact" (
276 PRIMARY KEY ("member_id", "other_member_id"),
277 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
278 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
279 "public" BOOLEAN NOT NULL DEFAULT FALSE,
280 CONSTRAINT "cant_save_yourself_as_contact"
281 CHECK ("member_id" != "other_member_id") );
282 CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
284 COMMENT ON TABLE "contact" IS 'Contact lists';
286 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
287 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
288 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
291 CREATE TABLE "ignored_member" (
292 PRIMARY KEY ("member_id", "other_member_id"),
293 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
294 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
295 CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
297 COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
299 COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone';
300 COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
303 CREATE TABLE "policy" (
304 "id" SERIAL4 PRIMARY KEY,
305 "index" INT4 NOT NULL,
306 "active" BOOLEAN NOT NULL DEFAULT TRUE,
307 "name" TEXT NOT NULL UNIQUE,
308 "description" TEXT NOT NULL DEFAULT '',
309 "admission_time" INTERVAL NOT NULL,
310 "discussion_time" INTERVAL NOT NULL,
311 "verification_time" INTERVAL NOT NULL,
312 "voting_time" INTERVAL NOT NULL,
313 "issue_quorum_num" INT4 NOT NULL,
314 "issue_quorum_den" INT4 NOT NULL,
315 "initiative_quorum_num" INT4 NOT NULL,
316 "initiative_quorum_den" INT4 NOT NULL,
317 "direct_majority_num" INT4 NOT NULL DEFAULT 1,
318 "direct_majority_den" INT4 NOT NULL DEFAULT 2,
319 "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
320 "direct_majority_positive" INT4 NOT NULL DEFAULT 0,
321 "direct_majority_non_negative" INT4 NOT NULL DEFAULT 0,
322 "indirect_majority_num" INT4 NOT NULL DEFAULT 1,
323 "indirect_majority_den" INT4 NOT NULL DEFAULT 2,
324 "indirect_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
325 "indirect_majority_positive" INT4 NOT NULL DEFAULT 0,
326 "indirect_majority_non_negative" INT4 NOT NULL DEFAULT 0,
327 "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT TRUE,
328 "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE );
329 CREATE INDEX "policy_active_idx" ON "policy" ("active");
331 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
333 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
334 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
335 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"';
336 COMMENT ON COLUMN "policy"."discussion_time" IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"';
337 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"';
338 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'')';
339 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''';
340 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''';
341 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
342 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
343 COMMENT ON COLUMN "policy"."direct_majority_num" IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner';
344 COMMENT ON COLUMN "policy"."direct_majority_den" IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner';
345 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.';
346 COMMENT ON COLUMN "policy"."direct_majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner';
347 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';
348 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';
349 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';
350 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.';
351 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';
352 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';
353 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.';
354 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").';
357 CREATE TABLE "unit" (
358 "id" SERIAL4 PRIMARY KEY,
359 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
360 "active" BOOLEAN NOT NULL DEFAULT TRUE,
361 "name" TEXT NOT NULL,
362 "description" TEXT NOT NULL DEFAULT '',
363 "member_count" INT4,
364 "text_search_data" TSVECTOR );
365 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
366 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
367 CREATE INDEX "unit_active_idx" ON "unit" ("active");
368 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
369 CREATE TRIGGER "update_text_search_data"
370 BEFORE INSERT OR UPDATE ON "unit"
371 FOR EACH ROW EXECUTE PROCEDURE
372 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
373 "name", "description" );
375 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
377 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
378 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in areas of this area';
379 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
382 CREATE TABLE "unit_setting" (
383 PRIMARY KEY ("member_id", "key", "unit_id"),
384 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
385 "key" TEXT NOT NULL,
386 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
387 "value" TEXT NOT NULL );
389 COMMENT ON TABLE "unit_setting" IS 'Place for frontend to store unit specific settings of members as strings';
392 CREATE TABLE "area" (
393 "id" SERIAL4 PRIMARY KEY,
394 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
395 "active" BOOLEAN NOT NULL DEFAULT TRUE,
396 "name" TEXT NOT NULL,
397 "description" TEXT NOT NULL DEFAULT '',
398 "direct_member_count" INT4,
399 "member_weight" INT4,
400 "text_search_data" TSVECTOR );
401 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
402 CREATE INDEX "area_active_idx" ON "area" ("active");
403 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
404 CREATE TRIGGER "update_text_search_data"
405 BEFORE INSERT OR UPDATE ON "area"
406 FOR EACH ROW EXECUTE PROCEDURE
407 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
408 "name", "description" );
410 COMMENT ON TABLE "area" IS 'Subject areas';
412 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
413 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"';
414 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
417 CREATE TABLE "area_setting" (
418 PRIMARY KEY ("member_id", "key", "area_id"),
419 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
420 "key" TEXT NOT NULL,
421 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
422 "value" TEXT NOT NULL );
424 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
427 CREATE TABLE "allowed_policy" (
428 PRIMARY KEY ("area_id", "policy_id"),
429 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
430 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
431 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
432 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
434 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
436 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
439 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
441 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';
444 CREATE TYPE "issue_state" AS ENUM (
445 'admission', 'discussion', 'verification', 'voting',
446 'canceled_revoked_before_accepted',
447 'canceled_issue_not_accepted',
448 'canceled_after_revocation_during_discussion',
449 'canceled_after_revocation_during_verification',
450 'calculation',
451 'canceled_no_initiative_admitted',
452 'finished_without_winner', 'finished_with_winner');
454 COMMENT ON TYPE "issue_state" IS 'State of issues';
457 CREATE TABLE "issue" (
458 "id" SERIAL4 PRIMARY KEY,
459 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
460 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
461 "state" "issue_state" NOT NULL DEFAULT 'admission',
462 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
463 "accepted" TIMESTAMPTZ,
464 "half_frozen" TIMESTAMPTZ,
465 "fully_frozen" TIMESTAMPTZ,
466 "closed" TIMESTAMPTZ,
467 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
468 "cleaned" TIMESTAMPTZ,
469 "admission_time" INTERVAL NOT NULL,
470 "discussion_time" INTERVAL NOT NULL,
471 "verification_time" INTERVAL NOT NULL,
472 "voting_time" INTERVAL NOT NULL,
473 "snapshot" TIMESTAMPTZ,
474 "latest_snapshot_event" "snapshot_event",
475 "population" INT4,
476 "voter_count" INT4,
477 "status_quo_schulze_rank" INT4,
478 CONSTRAINT "valid_state" CHECK ((
479 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
480 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
481 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
482 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
483 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
484 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
485 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
486 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
487 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)) AND (
488 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
489 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
490 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
491 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
492 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
493 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
494 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
495 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
496 ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
497 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
498 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
499 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
500 )),
501 CONSTRAINT "state_change_order" CHECK (
502 "created" <= "accepted" AND
503 "accepted" <= "half_frozen" AND
504 "half_frozen" <= "fully_frozen" AND
505 "fully_frozen" <= "closed" ),
506 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
507 "cleaned" ISNULL OR "closed" NOTNULL ),
508 CONSTRAINT "last_snapshot_on_full_freeze"
509 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
510 CONSTRAINT "freeze_requires_snapshot"
511 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
512 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
513 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
514 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
515 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
516 CREATE INDEX "issue_created_idx" ON "issue" ("created");
517 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
518 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
519 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
520 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
521 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
522 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
524 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
526 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
527 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.';
528 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.';
529 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.';
530 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
531 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
532 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
533 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
534 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
535 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
536 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
537 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';
538 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
539 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';
540 COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
543 CREATE TABLE "issue_setting" (
544 PRIMARY KEY ("member_id", "key", "issue_id"),
545 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
546 "key" TEXT NOT NULL,
547 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
548 "value" TEXT NOT NULL );
550 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
553 CREATE TABLE "initiative" (
554 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
555 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
556 "id" SERIAL4 PRIMARY KEY,
557 "name" TEXT NOT NULL,
558 "discussion_url" TEXT,
559 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
560 "revoked" TIMESTAMPTZ,
561 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
562 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
563 "admitted" BOOLEAN,
564 "supporter_count" INT4,
565 "informed_supporter_count" INT4,
566 "satisfied_supporter_count" INT4,
567 "satisfied_informed_supporter_count" INT4,
568 "positive_votes" INT4,
569 "negative_votes" INT4,
570 "direct_majority" BOOLEAN,
571 "indirect_majority" BOOLEAN,
572 "schulze_rank" INT4,
573 "better_than_status_quo" BOOLEAN,
574 "worse_than_status_quo" BOOLEAN,
575 "reverse_beat_path" BOOLEAN,
576 "multistage_majority" BOOLEAN,
577 "eligible" BOOLEAN,
578 "winner" BOOLEAN,
579 "rank" INT4,
580 "text_search_data" TSVECTOR,
581 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
582 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL),
583 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
584 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
585 CONSTRAINT "revoked_initiatives_cant_be_admitted"
586 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
587 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
588 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
589 ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND
590 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
591 "schulze_rank" ISNULL AND
592 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
593 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
594 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ),
595 CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")),
596 CONSTRAINT "minimum_requirement_to_be_eligible" CHECK (
597 "eligible" = FALSE OR
598 ("direct_majority" AND "indirect_majority" AND "better_than_status_quo") ),
599 CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE),
600 CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1),
601 CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE),
602 CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") );
603 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
604 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
605 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
606 CREATE TRIGGER "update_text_search_data"
607 BEFORE INSERT OR UPDATE ON "initiative"
608 FOR EACH ROW EXECUTE PROCEDURE
609 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
610 "name", "discussion_url");
612 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.';
614 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
615 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
616 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoked the initiative';
617 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
618 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
619 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
620 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
621 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
622 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
623 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
624 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"';
625 COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths';
626 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking without tie-breaking';
627 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)';
628 COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)';
629 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';
630 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';
631 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"';
632 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"';
633 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';
636 CREATE TABLE "battle" (
637 "issue_id" INT4 NOT NULL,
638 "winning_initiative_id" INT4,
639 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
640 "losing_initiative_id" INT4,
641 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
642 "count" INT4 NOT NULL,
643 CONSTRAINT "initiative_ids_not_equal" CHECK (
644 "winning_initiative_id" != "losing_initiative_id" OR
645 ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
646 ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) );
647 CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
648 CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
649 CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
651 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';
654 CREATE TABLE "ignored_initiative" (
655 PRIMARY KEY ("initiative_id", "member_id"),
656 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
657 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
658 CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");
660 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
663 CREATE TABLE "initiative_setting" (
664 PRIMARY KEY ("member_id", "key", "initiative_id"),
665 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
666 "key" TEXT NOT NULL,
667 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
668 "value" TEXT NOT NULL );
670 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
673 CREATE TABLE "draft" (
674 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
675 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
676 "id" SERIAL8 PRIMARY KEY,
677 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
678 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
679 "formatting_engine" TEXT,
680 "content" TEXT NOT NULL,
681 "text_search_data" TSVECTOR );
682 CREATE INDEX "draft_created_idx" ON "draft" ("created");
683 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
684 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
685 CREATE TRIGGER "update_text_search_data"
686 BEFORE INSERT OR UPDATE ON "draft"
687 FOR EACH ROW EXECUTE PROCEDURE
688 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
690 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.';
692 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
693 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
696 CREATE TABLE "rendered_draft" (
697 PRIMARY KEY ("draft_id", "format"),
698 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
699 "format" TEXT,
700 "content" TEXT NOT NULL );
702 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)';
705 CREATE TABLE "suggestion" (
706 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
707 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
708 "id" SERIAL8 PRIMARY KEY,
709 "draft_id" INT8 NOT NULL,
710 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
711 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
712 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
713 "name" TEXT NOT NULL,
714 "formatting_engine" TEXT,
715 "content" TEXT NOT NULL DEFAULT '',
716 "text_search_data" TSVECTOR,
717 "minus2_unfulfilled_count" INT4,
718 "minus2_fulfilled_count" INT4,
719 "minus1_unfulfilled_count" INT4,
720 "minus1_fulfilled_count" INT4,
721 "plus1_unfulfilled_count" INT4,
722 "plus1_fulfilled_count" INT4,
723 "plus2_unfulfilled_count" INT4,
724 "plus2_fulfilled_count" INT4 );
725 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
726 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
727 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
728 CREATE TRIGGER "update_text_search_data"
729 BEFORE INSERT OR UPDATE ON "suggestion"
730 FOR EACH ROW EXECUTE PROCEDURE
731 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
732 "name", "content");
734 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';
736 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")';
737 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
738 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
739 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
740 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
741 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
742 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
743 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
744 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
747 CREATE TABLE "rendered_suggestion" (
748 PRIMARY KEY ("suggestion_id", "format"),
749 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
750 "format" TEXT,
751 "content" TEXT NOT NULL );
753 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)';
756 CREATE TABLE "suggestion_setting" (
757 PRIMARY KEY ("member_id", "key", "suggestion_id"),
758 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
759 "key" TEXT NOT NULL,
760 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
761 "value" TEXT NOT NULL );
763 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
766 CREATE TABLE "privilege" (
767 PRIMARY KEY ("unit_id", "member_id"),
768 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
769 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
770 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
771 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
772 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
773 "voting_right_manager" BOOLEAN NOT NULL DEFAULT FALSE,
774 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE );
776 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
778 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke admin privileges to/from other members';
779 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create and disable sub units';
780 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create and disable areas and set area parameters';
781 COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote within the unit';
782 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to discuss and vote';
785 CREATE TABLE "membership" (
786 PRIMARY KEY ("area_id", "member_id"),
787 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
788 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
789 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
791 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
794 CREATE TABLE "interest" (
795 PRIMARY KEY ("issue_id", "member_id"),
796 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
797 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
798 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
800 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.';
803 CREATE TABLE "initiator" (
804 PRIMARY KEY ("initiative_id", "member_id"),
805 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
806 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
807 "accepted" BOOLEAN );
808 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
810 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.';
812 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.';
815 CREATE TABLE "supporter" (
816 "issue_id" INT4 NOT NULL,
817 PRIMARY KEY ("initiative_id", "member_id"),
818 "initiative_id" INT4,
819 "member_id" INT4,
820 "draft_id" INT8 NOT NULL,
821 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
822 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE );
823 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
825 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.';
827 COMMENT ON COLUMN "supporter"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
828 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")';
831 CREATE TABLE "opinion" (
832 "initiative_id" INT4 NOT NULL,
833 PRIMARY KEY ("suggestion_id", "member_id"),
834 "suggestion_id" INT8,
835 "member_id" INT4,
836 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
837 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
838 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
839 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
840 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
842 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.';
844 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
847 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
849 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
852 CREATE TABLE "delegation" (
853 "id" SERIAL8 PRIMARY KEY,
854 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
855 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
856 "scope" "delegation_scope" NOT NULL,
857 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
858 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
859 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
860 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
861 CONSTRAINT "no_unit_delegation_to_null"
862 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
863 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
864 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
865 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
866 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
867 UNIQUE ("unit_id", "truster_id"),
868 UNIQUE ("area_id", "truster_id"),
869 UNIQUE ("issue_id", "truster_id") );
870 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
871 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
873 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
875 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
876 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
877 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
880 CREATE TABLE "direct_population_snapshot" (
881 PRIMARY KEY ("issue_id", "event", "member_id"),
882 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
883 "event" "snapshot_event",
884 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
885 "weight" INT4 );
886 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
888 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
890 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
891 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
894 CREATE TABLE "delegating_population_snapshot" (
895 PRIMARY KEY ("issue_id", "event", "member_id"),
896 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
897 "event" "snapshot_event",
898 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
899 "weight" INT4,
900 "scope" "delegation_scope" NOT NULL,
901 "delegate_member_ids" INT4[] NOT NULL );
902 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
904 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
906 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
907 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
908 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
909 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"';
912 CREATE TABLE "direct_interest_snapshot" (
913 PRIMARY KEY ("issue_id", "event", "member_id"),
914 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
915 "event" "snapshot_event",
916 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
917 "weight" INT4 );
918 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
920 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
922 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
923 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
926 CREATE TABLE "delegating_interest_snapshot" (
927 PRIMARY KEY ("issue_id", "event", "member_id"),
928 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
929 "event" "snapshot_event",
930 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
931 "weight" INT4,
932 "scope" "delegation_scope" NOT NULL,
933 "delegate_member_ids" INT4[] NOT NULL );
934 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
936 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
938 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
939 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
940 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
941 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"';
944 CREATE TABLE "direct_supporter_snapshot" (
945 "issue_id" INT4 NOT NULL,
946 PRIMARY KEY ("initiative_id", "event", "member_id"),
947 "initiative_id" INT4,
948 "event" "snapshot_event",
949 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
950 "draft_id" INT8 NOT NULL,
951 "informed" BOOLEAN NOT NULL,
952 "satisfied" BOOLEAN NOT NULL,
953 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
954 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
955 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
956 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
958 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
960 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';
961 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
962 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
963 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
966 CREATE TABLE "non_voter" (
967 PRIMARY KEY ("issue_id", "member_id"),
968 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
969 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
970 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
972 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
975 CREATE TABLE "direct_voter" (
976 PRIMARY KEY ("issue_id", "member_id"),
977 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
978 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
979 "weight" INT4 );
980 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
982 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.';
984 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
987 CREATE TABLE "delegating_voter" (
988 PRIMARY KEY ("issue_id", "member_id"),
989 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
990 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
991 "weight" INT4,
992 "scope" "delegation_scope" NOT NULL,
993 "delegate_member_ids" INT4[] NOT NULL );
994 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
996 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
998 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
999 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
1000 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"';
1003 CREATE TABLE "vote" (
1004 "issue_id" INT4 NOT NULL,
1005 PRIMARY KEY ("initiative_id", "member_id"),
1006 "initiative_id" INT4,
1007 "member_id" INT4,
1008 "grade" INT4,
1009 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1010 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1011 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
1013 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.';
1015 COMMENT ON COLUMN "vote"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
1016 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.';
1019 CREATE TABLE "issue_comment" (
1020 PRIMARY KEY ("issue_id", "member_id"),
1021 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1022 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1023 "changed" TIMESTAMPTZ NOT NULL DEFAULT now(),
1024 "formatting_engine" TEXT,
1025 "content" TEXT NOT NULL,
1026 "text_search_data" TSVECTOR );
1027 CREATE INDEX "issue_comment_member_id_idx" ON "issue_comment" ("member_id");
1028 CREATE INDEX "issue_comment_text_search_data_idx" ON "issue_comment" USING gin ("text_search_data");
1029 CREATE TRIGGER "update_text_search_data"
1030 BEFORE INSERT OR UPDATE ON "issue_comment"
1031 FOR EACH ROW EXECUTE PROCEDURE
1032 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
1034 COMMENT ON TABLE "issue_comment" IS 'Place to store free comments of members related to issues';
1036 COMMENT ON COLUMN "issue_comment"."changed" IS 'Time the comment was last changed';
1039 CREATE TABLE "rendered_issue_comment" (
1040 PRIMARY KEY ("issue_id", "member_id", "format"),
1041 FOREIGN KEY ("issue_id", "member_id")
1042 REFERENCES "issue_comment" ("issue_id", "member_id")
1043 ON DELETE CASCADE ON UPDATE CASCADE,
1044 "issue_id" INT4,
1045 "member_id" INT4,
1046 "format" TEXT,
1047 "content" TEXT NOT NULL );
1049 COMMENT ON TABLE "rendered_issue_comment" IS 'This table may be used by frontends to cache "rendered" issue comments (e.g. HTML output generated from wiki text)';
1052 CREATE TABLE "voting_comment" (
1053 PRIMARY KEY ("issue_id", "member_id"),
1054 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1055 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1056 "changed" TIMESTAMPTZ,
1057 "formatting_engine" TEXT,
1058 "content" TEXT NOT NULL,
1059 "text_search_data" TSVECTOR );
1060 CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id");
1061 CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data");
1062 CREATE TRIGGER "update_text_search_data"
1063 BEFORE INSERT OR UPDATE ON "voting_comment"
1064 FOR EACH ROW EXECUTE PROCEDURE
1065 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
1067 COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.';
1069 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.';
1072 CREATE TABLE "rendered_voting_comment" (
1073 PRIMARY KEY ("issue_id", "member_id", "format"),
1074 FOREIGN KEY ("issue_id", "member_id")
1075 REFERENCES "voting_comment" ("issue_id", "member_id")
1076 ON DELETE CASCADE ON UPDATE CASCADE,
1077 "issue_id" INT4,
1078 "member_id" INT4,
1079 "format" TEXT,
1080 "content" TEXT NOT NULL );
1082 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)';
1085 CREATE TYPE "event_type" AS ENUM (
1086 'issue_state_changed',
1087 'initiative_created_in_new_issue',
1088 'initiative_created_in_existing_issue',
1089 'initiative_revoked',
1090 'new_draft_created',
1091 'suggestion_created');
1093 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
1096 CREATE TABLE "event" (
1097 "id" SERIAL8 PRIMARY KEY,
1098 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
1099 "event" "event_type" NOT NULL,
1100 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1101 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1102 "state" "issue_state" CHECK ("state" != 'calculation'),
1103 "initiative_id" INT4,
1104 "draft_id" INT8,
1105 "suggestion_id" INT8,
1106 FOREIGN KEY ("issue_id", "initiative_id")
1107 REFERENCES "initiative" ("issue_id", "id")
1108 ON DELETE CASCADE ON UPDATE CASCADE,
1109 FOREIGN KEY ("initiative_id", "draft_id")
1110 REFERENCES "draft" ("initiative_id", "id")
1111 ON DELETE CASCADE ON UPDATE CASCADE,
1112 FOREIGN KEY ("initiative_id", "suggestion_id")
1113 REFERENCES "suggestion" ("initiative_id", "id")
1114 ON DELETE CASCADE ON UPDATE CASCADE,
1115 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
1116 "event" != 'issue_state_changed' OR (
1117 "member_id" ISNULL AND
1118 "issue_id" NOTNULL AND
1119 "state" NOTNULL AND
1120 "initiative_id" ISNULL AND
1121 "draft_id" ISNULL AND
1122 "suggestion_id" ISNULL )),
1123 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
1124 "event" NOT IN (
1125 'initiative_created_in_new_issue',
1126 'initiative_created_in_existing_issue',
1127 'initiative_revoked',
1128 'new_draft_created'
1129 ) OR (
1130 "member_id" NOTNULL AND
1131 "issue_id" NOTNULL AND
1132 "state" NOTNULL AND
1133 "initiative_id" NOTNULL AND
1134 "draft_id" NOTNULL AND
1135 "suggestion_id" ISNULL )),
1136 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
1137 "event" != 'suggestion_created' OR (
1138 "member_id" NOTNULL AND
1139 "issue_id" NOTNULL AND
1140 "state" NOTNULL AND
1141 "initiative_id" NOTNULL AND
1142 "draft_id" ISNULL AND
1143 "suggestion_id" NOTNULL )) );
1144 -- TODO: indicies
1146 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
1148 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
1149 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';
1150 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';
1151 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';
1155 ----------------------------------------------
1156 -- Writing of history entries and event log --
1157 ----------------------------------------------
1160 CREATE FUNCTION "write_member_history_trigger"()
1161 RETURNS TRIGGER
1162 LANGUAGE 'plpgsql' VOLATILE AS $$
1163 BEGIN
1164 IF
1165 NEW."active" != OLD."active" OR
1166 NEW."name" != OLD."name"
1167 THEN
1168 INSERT INTO "member_history"
1169 ("member_id", "active", "name")
1170 VALUES (NEW."id", OLD."active", OLD."name");
1171 END IF;
1172 RETURN NULL;
1173 END;
1174 $$;
1176 CREATE TRIGGER "write_member_history"
1177 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1178 "write_member_history_trigger"();
1180 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
1181 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
1184 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
1185 RETURNS TRIGGER
1186 LANGUAGE 'plpgsql' VOLATILE AS $$
1187 BEGIN
1188 IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN
1189 INSERT INTO "event" ("event", "issue_id", "state")
1190 VALUES ('issue_state_changed', NEW."id", NEW."state");
1191 END IF;
1192 RETURN NULL;
1193 END;
1194 $$;
1196 CREATE TRIGGER "write_event_issue_state_changed"
1197 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
1198 "write_event_issue_state_changed_trigger"();
1200 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
1201 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
1204 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1205 RETURNS TRIGGER
1206 LANGUAGE 'plpgsql' VOLATILE AS $$
1207 DECLARE
1208 "initiative_row" "initiative"%ROWTYPE;
1209 "issue_row" "issue"%ROWTYPE;
1210 "event_v" "event_type";
1211 BEGIN
1212 SELECT * INTO "initiative_row" FROM "initiative"
1213 WHERE "id" = NEW."initiative_id";
1214 SELECT * INTO "issue_row" FROM "issue"
1215 WHERE "id" = "initiative_row"."issue_id";
1216 IF EXISTS (
1217 SELECT NULL FROM "draft"
1218 WHERE "initiative_id" = NEW."initiative_id"
1219 AND "id" != NEW."id"
1220 ) THEN
1221 "event_v" := 'new_draft_created';
1222 ELSE
1223 IF EXISTS (
1224 SELECT NULL FROM "initiative"
1225 WHERE "issue_id" = "initiative_row"."issue_id"
1226 AND "id" != "initiative_row"."id"
1227 ) THEN
1228 "event_v" := 'initiative_created_in_existing_issue';
1229 ELSE
1230 "event_v" := 'initiative_created_in_new_issue';
1231 END IF;
1232 END IF;
1233 INSERT INTO "event" (
1234 "event", "member_id",
1235 "issue_id", "state", "initiative_id", "draft_id"
1236 ) VALUES (
1237 "event_v",
1238 NEW."author_id",
1239 "initiative_row"."issue_id",
1240 "issue_row"."state",
1241 "initiative_row"."id",
1242 NEW."id" );
1243 RETURN NULL;
1244 END;
1245 $$;
1247 CREATE TRIGGER "write_event_initiative_or_draft_created"
1248 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
1249 "write_event_initiative_or_draft_created_trigger"();
1251 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
1252 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
1255 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
1256 RETURNS TRIGGER
1257 LANGUAGE 'plpgsql' VOLATILE AS $$
1258 DECLARE
1259 "issue_row" "issue"%ROWTYPE;
1260 BEGIN
1261 SELECT * INTO "issue_row" FROM "issue"
1262 WHERE "id" = NEW."issue_id";
1263 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1264 INSERT INTO "event" (
1265 "event", "member_id", "issue_id", "state", "initiative_id"
1266 ) VALUES (
1267 'initiative_revoked',
1268 NEW."revoked_by_member_id",
1269 NEW."issue_id",
1270 "issue_row"."state",
1271 NEW."id" );
1272 END IF;
1273 RETURN NULL;
1274 END;
1275 $$;
1277 CREATE TRIGGER "write_event_initiative_revoked"
1278 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
1279 "write_event_initiative_revoked_trigger"();
1281 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
1282 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
1285 CREATE FUNCTION "write_event_suggestion_created_trigger"()
1286 RETURNS TRIGGER
1287 LANGUAGE 'plpgsql' VOLATILE AS $$
1288 DECLARE
1289 "initiative_row" "initiative"%ROWTYPE;
1290 "issue_row" "issue"%ROWTYPE;
1291 BEGIN
1292 SELECT * INTO "initiative_row" FROM "initiative"
1293 WHERE "id" = NEW."initiative_id";
1294 SELECT * INTO "issue_row" FROM "issue"
1295 WHERE "id" = "initiative_row"."issue_id";
1296 INSERT INTO "event" (
1297 "event", "member_id",
1298 "issue_id", "state", "initiative_id", "suggestion_id"
1299 ) VALUES (
1300 'suggestion_created',
1301 NEW."author_id",
1302 "initiative_row"."issue_id",
1303 "issue_row"."state",
1304 "initiative_row"."id",
1305 NEW."id" );
1306 RETURN NULL;
1307 END;
1308 $$;
1310 CREATE TRIGGER "write_event_suggestion_created"
1311 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1312 "write_event_suggestion_created_trigger"();
1314 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
1315 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1319 ----------------------------
1320 -- Additional constraints --
1321 ----------------------------
1324 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
1325 RETURNS TRIGGER
1326 LANGUAGE 'plpgsql' VOLATILE AS $$
1327 BEGIN
1328 IF NOT EXISTS (
1329 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
1330 ) THEN
1331 --RAISE 'Cannot create issue without an initial initiative.' USING
1332 -- ERRCODE = 'integrity_constraint_violation',
1333 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
1334 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
1335 END IF;
1336 RETURN NULL;
1337 END;
1338 $$;
1340 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
1341 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
1342 FOR EACH ROW EXECUTE PROCEDURE
1343 "issue_requires_first_initiative_trigger"();
1345 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
1346 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
1349 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
1350 RETURNS TRIGGER
1351 LANGUAGE 'plpgsql' VOLATILE AS $$
1352 DECLARE
1353 "reference_lost" BOOLEAN;
1354 BEGIN
1355 IF TG_OP = 'DELETE' THEN
1356 "reference_lost" := TRUE;
1357 ELSE
1358 "reference_lost" := NEW."issue_id" != OLD."issue_id";
1359 END IF;
1360 IF
1361 "reference_lost" AND NOT EXISTS (
1362 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
1363 )
1364 THEN
1365 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
1366 END IF;
1367 RETURN NULL;
1368 END;
1369 $$;
1371 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
1372 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1373 FOR EACH ROW EXECUTE PROCEDURE
1374 "last_initiative_deletes_issue_trigger"();
1376 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
1377 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
1380 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
1381 RETURNS TRIGGER
1382 LANGUAGE 'plpgsql' VOLATILE AS $$
1383 BEGIN
1384 IF NOT EXISTS (
1385 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
1386 ) THEN
1387 --RAISE 'Cannot create initiative without an initial draft.' USING
1388 -- ERRCODE = 'integrity_constraint_violation',
1389 -- HINT = 'Create issue, initiative and draft within the same transaction.';
1390 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
1391 END IF;
1392 RETURN NULL;
1393 END;
1394 $$;
1396 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
1397 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1398 FOR EACH ROW EXECUTE PROCEDURE
1399 "initiative_requires_first_draft_trigger"();
1401 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
1402 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
1405 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
1406 RETURNS TRIGGER
1407 LANGUAGE 'plpgsql' VOLATILE AS $$
1408 DECLARE
1409 "reference_lost" BOOLEAN;
1410 BEGIN
1411 IF TG_OP = 'DELETE' THEN
1412 "reference_lost" := TRUE;
1413 ELSE
1414 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
1415 END IF;
1416 IF
1417 "reference_lost" AND NOT EXISTS (
1418 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
1419 )
1420 THEN
1421 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
1422 END IF;
1423 RETURN NULL;
1424 END;
1425 $$;
1427 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
1428 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
1429 FOR EACH ROW EXECUTE PROCEDURE
1430 "last_draft_deletes_initiative_trigger"();
1432 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
1433 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
1436 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
1437 RETURNS TRIGGER
1438 LANGUAGE 'plpgsql' VOLATILE AS $$
1439 BEGIN
1440 IF NOT EXISTS (
1441 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
1442 ) THEN
1443 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
1444 END IF;
1445 RETURN NULL;
1446 END;
1447 $$;
1449 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
1450 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
1451 FOR EACH ROW EXECUTE PROCEDURE
1452 "suggestion_requires_first_opinion_trigger"();
1454 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1455 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1458 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1459 RETURNS TRIGGER
1460 LANGUAGE 'plpgsql' VOLATILE AS $$
1461 DECLARE
1462 "reference_lost" BOOLEAN;
1463 BEGIN
1464 IF TG_OP = 'DELETE' THEN
1465 "reference_lost" := TRUE;
1466 ELSE
1467 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1468 END IF;
1469 IF
1470 "reference_lost" AND NOT EXISTS (
1471 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1472 )
1473 THEN
1474 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1475 END IF;
1476 RETURN NULL;
1477 END;
1478 $$;
1480 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1481 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1482 FOR EACH ROW EXECUTE PROCEDURE
1483 "last_opinion_deletes_suggestion_trigger"();
1485 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1486 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1490 ---------------------------------------------------------------
1491 -- Ensure that votes are not modified when issues are frozen --
1492 ---------------------------------------------------------------
1494 -- NOTE: Frontends should ensure this anyway, but in case of programming
1495 -- errors the following triggers ensure data integrity.
1498 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1499 RETURNS TRIGGER
1500 LANGUAGE 'plpgsql' VOLATILE AS $$
1501 DECLARE
1502 "issue_id_v" "issue"."id"%TYPE;
1503 "issue_row" "issue"%ROWTYPE;
1504 BEGIN
1505 IF TG_OP = 'DELETE' THEN
1506 "issue_id_v" := OLD."issue_id";
1507 ELSE
1508 "issue_id_v" := NEW."issue_id";
1509 END IF;
1510 SELECT INTO "issue_row" * FROM "issue"
1511 WHERE "id" = "issue_id_v" FOR SHARE;
1512 IF "issue_row"."closed" NOTNULL THEN
1513 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
1514 END IF;
1515 RETURN NULL;
1516 END;
1517 $$;
1519 CREATE TRIGGER "forbid_changes_on_closed_issue"
1520 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1521 FOR EACH ROW EXECUTE PROCEDURE
1522 "forbid_changes_on_closed_issue_trigger"();
1524 CREATE TRIGGER "forbid_changes_on_closed_issue"
1525 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1526 FOR EACH ROW EXECUTE PROCEDURE
1527 "forbid_changes_on_closed_issue_trigger"();
1529 CREATE TRIGGER "forbid_changes_on_closed_issue"
1530 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1531 FOR EACH ROW EXECUTE PROCEDURE
1532 "forbid_changes_on_closed_issue_trigger"();
1534 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"';
1535 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';
1536 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';
1537 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';
1541 --------------------------------------------------------------------
1542 -- Auto-retrieval of fields only needed for referential integrity --
1543 --------------------------------------------------------------------
1546 CREATE FUNCTION "autofill_issue_id_trigger"()
1547 RETURNS TRIGGER
1548 LANGUAGE 'plpgsql' VOLATILE AS $$
1549 BEGIN
1550 IF NEW."issue_id" ISNULL THEN
1551 SELECT "issue_id" INTO NEW."issue_id"
1552 FROM "initiative" WHERE "id" = NEW."initiative_id";
1553 END IF;
1554 RETURN NEW;
1555 END;
1556 $$;
1558 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1559 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1561 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1562 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1564 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1565 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1566 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1569 CREATE FUNCTION "autofill_initiative_id_trigger"()
1570 RETURNS TRIGGER
1571 LANGUAGE 'plpgsql' VOLATILE AS $$
1572 BEGIN
1573 IF NEW."initiative_id" ISNULL THEN
1574 SELECT "initiative_id" INTO NEW."initiative_id"
1575 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1576 END IF;
1577 RETURN NEW;
1578 END;
1579 $$;
1581 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1582 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1584 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1585 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1589 -----------------------------------------------------
1590 -- Automatic calculation of certain default values --
1591 -----------------------------------------------------
1594 CREATE FUNCTION "copy_timings_trigger"()
1595 RETURNS TRIGGER
1596 LANGUAGE 'plpgsql' VOLATILE AS $$
1597 DECLARE
1598 "policy_row" "policy"%ROWTYPE;
1599 BEGIN
1600 SELECT * INTO "policy_row" FROM "policy"
1601 WHERE "id" = NEW."policy_id";
1602 IF NEW."admission_time" ISNULL THEN
1603 NEW."admission_time" := "policy_row"."admission_time";
1604 END IF;
1605 IF NEW."discussion_time" ISNULL THEN
1606 NEW."discussion_time" := "policy_row"."discussion_time";
1607 END IF;
1608 IF NEW."verification_time" ISNULL THEN
1609 NEW."verification_time" := "policy_row"."verification_time";
1610 END IF;
1611 IF NEW."voting_time" ISNULL THEN
1612 NEW."voting_time" := "policy_row"."voting_time";
1613 END IF;
1614 RETURN NEW;
1615 END;
1616 $$;
1618 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1619 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1621 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1622 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1625 CREATE FUNCTION "default_for_draft_id_trigger"()
1626 RETURNS TRIGGER
1627 LANGUAGE 'plpgsql' VOLATILE AS $$
1628 BEGIN
1629 IF NEW."draft_id" ISNULL THEN
1630 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1631 WHERE "initiative_id" = NEW."initiative_id";
1632 END IF;
1633 RETURN NEW;
1634 END;
1635 $$;
1637 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"
1638 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1639 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1640 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1642 COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';
1643 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';
1644 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';
1648 ----------------------------------------
1649 -- Automatic creation of dependencies --
1650 ----------------------------------------
1653 CREATE FUNCTION "autocreate_interest_trigger"()
1654 RETURNS TRIGGER
1655 LANGUAGE 'plpgsql' VOLATILE AS $$
1656 BEGIN
1657 IF NOT EXISTS (
1658 SELECT NULL FROM "initiative" JOIN "interest"
1659 ON "initiative"."issue_id" = "interest"."issue_id"
1660 WHERE "initiative"."id" = NEW."initiative_id"
1661 AND "interest"."member_id" = NEW."member_id"
1662 ) THEN
1663 BEGIN
1664 INSERT INTO "interest" ("issue_id", "member_id")
1665 SELECT "issue_id", NEW."member_id"
1666 FROM "initiative" WHERE "id" = NEW."initiative_id";
1667 EXCEPTION WHEN unique_violation THEN END;
1668 END IF;
1669 RETURN NEW;
1670 END;
1671 $$;
1673 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1674 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1676 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1677 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';
1680 CREATE FUNCTION "autocreate_supporter_trigger"()
1681 RETURNS TRIGGER
1682 LANGUAGE 'plpgsql' VOLATILE AS $$
1683 BEGIN
1684 IF NOT EXISTS (
1685 SELECT NULL FROM "suggestion" JOIN "supporter"
1686 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1687 WHERE "suggestion"."id" = NEW."suggestion_id"
1688 AND "supporter"."member_id" = NEW."member_id"
1689 ) THEN
1690 BEGIN
1691 INSERT INTO "supporter" ("initiative_id", "member_id")
1692 SELECT "initiative_id", NEW."member_id"
1693 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1694 EXCEPTION WHEN unique_violation THEN END;
1695 END IF;
1696 RETURN NEW;
1697 END;
1698 $$;
1700 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1701 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1703 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1704 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.';
1708 ------------------------------------------
1709 -- Views and helper functions for views --
1710 ------------------------------------------
1713 CREATE VIEW "unit_delegation" AS
1714 SELECT
1715 "unit"."id" AS "unit_id",
1716 "delegation"."id",
1717 "delegation"."truster_id",
1718 "delegation"."trustee_id",
1719 "delegation"."scope"
1720 FROM "unit"
1721 JOIN "delegation"
1722 ON "delegation"."unit_id" = "unit"."id"
1723 JOIN "member"
1724 ON "delegation"."truster_id" = "member"."id"
1725 JOIN "privilege"
1726 ON "delegation"."unit_id" = "privilege"."unit_id"
1727 AND "delegation"."truster_id" = "privilege"."member_id"
1728 WHERE "member"."active" AND "privilege"."voting_right";
1730 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
1733 CREATE VIEW "area_delegation" AS
1734 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1735 "area"."id" AS "area_id",
1736 "delegation"."id",
1737 "delegation"."truster_id",
1738 "delegation"."trustee_id",
1739 "delegation"."scope"
1740 FROM "area"
1741 JOIN "delegation"
1742 ON "delegation"."unit_id" = "area"."unit_id"
1743 OR "delegation"."area_id" = "area"."id"
1744 JOIN "member"
1745 ON "delegation"."truster_id" = "member"."id"
1746 JOIN "privilege"
1747 ON "area"."unit_id" = "privilege"."unit_id"
1748 AND "delegation"."truster_id" = "privilege"."member_id"
1749 WHERE "member"."active" AND "privilege"."voting_right"
1750 ORDER BY
1751 "area"."id",
1752 "delegation"."truster_id",
1753 "delegation"."scope" DESC;
1755 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
1758 CREATE VIEW "issue_delegation" AS
1759 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1760 "issue"."id" AS "issue_id",
1761 "delegation"."id",
1762 "delegation"."truster_id",
1763 "delegation"."trustee_id",
1764 "delegation"."scope"
1765 FROM "issue"
1766 JOIN "area"
1767 ON "area"."id" = "issue"."area_id"
1768 JOIN "delegation"
1769 ON "delegation"."unit_id" = "area"."unit_id"
1770 OR "delegation"."area_id" = "area"."id"
1771 OR "delegation"."issue_id" = "issue"."id"
1772 JOIN "member"
1773 ON "delegation"."truster_id" = "member"."id"
1774 JOIN "privilege"
1775 ON "area"."unit_id" = "privilege"."unit_id"
1776 AND "delegation"."truster_id" = "privilege"."member_id"
1777 WHERE "member"."active" AND "privilege"."voting_right"
1778 ORDER BY
1779 "issue"."id",
1780 "delegation"."truster_id",
1781 "delegation"."scope" DESC;
1783 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
1786 CREATE FUNCTION "membership_weight_with_skipping"
1787 ( "area_id_p" "area"."id"%TYPE,
1788 "member_id_p" "member"."id"%TYPE,
1789 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1790 RETURNS INT4
1791 LANGUAGE 'plpgsql' STABLE AS $$
1792 DECLARE
1793 "sum_v" INT4;
1794 "delegation_row" "area_delegation"%ROWTYPE;
1795 BEGIN
1796 "sum_v" := 1;
1797 FOR "delegation_row" IN
1798 SELECT "area_delegation".*
1799 FROM "area_delegation" LEFT JOIN "membership"
1800 ON "membership"."area_id" = "area_id_p"
1801 AND "membership"."member_id" = "area_delegation"."truster_id"
1802 WHERE "area_delegation"."area_id" = "area_id_p"
1803 AND "area_delegation"."trustee_id" = "member_id_p"
1804 AND "membership"."member_id" ISNULL
1805 LOOP
1806 IF NOT
1807 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1808 THEN
1809 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1810 "area_id_p",
1811 "delegation_row"."truster_id",
1812 "skip_member_ids_p" || "delegation_row"."truster_id"
1813 );
1814 END IF;
1815 END LOOP;
1816 RETURN "sum_v";
1817 END;
1818 $$;
1820 COMMENT ON FUNCTION "membership_weight_with_skipping"
1821 ( "area"."id"%TYPE,
1822 "member"."id"%TYPE,
1823 INT4[] )
1824 IS 'Helper function for "membership_weight" function';
1827 CREATE FUNCTION "membership_weight"
1828 ( "area_id_p" "area"."id"%TYPE,
1829 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1830 RETURNS INT4
1831 LANGUAGE 'plpgsql' STABLE AS $$
1832 BEGIN
1833 RETURN "membership_weight_with_skipping"(
1834 "area_id_p",
1835 "member_id_p",
1836 ARRAY["member_id_p"]
1837 );
1838 END;
1839 $$;
1841 COMMENT ON FUNCTION "membership_weight"
1842 ( "area"."id"%TYPE,
1843 "member"."id"%TYPE )
1844 IS 'Calculates the potential voting weight of a member in a given area';
1847 CREATE VIEW "member_count_view" AS
1848 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1850 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1853 CREATE VIEW "unit_member_count" AS
1854 SELECT
1855 "unit"."id" AS "unit_id",
1856 sum("member"."id") AS "member_count"
1857 FROM "unit"
1858 LEFT JOIN "privilege"
1859 ON "privilege"."unit_id" = "unit"."id"
1860 AND "privilege"."voting_right"
1861 LEFT JOIN "member"
1862 ON "member"."id" = "privilege"."member_id"
1863 AND "member"."active"
1864 GROUP BY "unit"."id";
1866 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
1869 CREATE VIEW "area_member_count" AS
1870 SELECT
1871 "area"."id" AS "area_id",
1872 count("member"."id") AS "direct_member_count",
1873 coalesce(
1874 sum(
1875 CASE WHEN "member"."id" NOTNULL THEN
1876 "membership_weight"("area"."id", "member"."id")
1877 ELSE 0 END
1878 )
1879 ) AS "member_weight"
1880 FROM "area"
1881 LEFT JOIN "membership"
1882 ON "area"."id" = "membership"."area_id"
1883 LEFT JOIN "privilege"
1884 ON "privilege"."unit_id" = "area"."unit_id"
1885 AND "privilege"."member_id" = "membership"."member_id"
1886 AND "privilege"."voting_right"
1887 LEFT JOIN "member"
1888 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
1889 AND "member"."active"
1890 GROUP BY "area"."id";
1892 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"';
1895 CREATE VIEW "opening_draft" AS
1896 SELECT "draft".* FROM (
1897 SELECT
1898 "initiative"."id" AS "initiative_id",
1899 min("draft"."id") AS "draft_id"
1900 FROM "initiative" JOIN "draft"
1901 ON "initiative"."id" = "draft"."initiative_id"
1902 GROUP BY "initiative"."id"
1903 ) AS "subquery"
1904 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1906 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1909 CREATE VIEW "current_draft" AS
1910 SELECT "draft".* FROM (
1911 SELECT
1912 "initiative"."id" AS "initiative_id",
1913 max("draft"."id") AS "draft_id"
1914 FROM "initiative" JOIN "draft"
1915 ON "initiative"."id" = "draft"."initiative_id"
1916 GROUP BY "initiative"."id"
1917 ) AS "subquery"
1918 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1920 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1923 CREATE VIEW "critical_opinion" AS
1924 SELECT * FROM "opinion"
1925 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
1926 OR ("degree" = -2 AND "fulfilled" = TRUE);
1928 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
1931 CREATE VIEW "battle_participant" AS
1932 SELECT "initiative"."id", "initiative"."issue_id"
1933 FROM "issue" JOIN "initiative"
1934 ON "issue"."id" = "initiative"."issue_id"
1935 WHERE "initiative"."admitted"
1936 UNION ALL
1937 SELECT NULL, "id" AS "issue_id"
1938 FROM "issue";
1940 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
1943 CREATE VIEW "battle_view" AS
1944 SELECT
1945 "issue"."id" AS "issue_id",
1946 "winning_initiative"."id" AS "winning_initiative_id",
1947 "losing_initiative"."id" AS "losing_initiative_id",
1948 sum(
1949 CASE WHEN
1950 coalesce("better_vote"."grade", 0) >
1951 coalesce("worse_vote"."grade", 0)
1952 THEN "direct_voter"."weight" ELSE 0 END
1953 ) AS "count"
1954 FROM "issue"
1955 LEFT JOIN "direct_voter"
1956 ON "issue"."id" = "direct_voter"."issue_id"
1957 JOIN "battle_participant" AS "winning_initiative"
1958 ON "issue"."id" = "winning_initiative"."issue_id"
1959 JOIN "battle_participant" AS "losing_initiative"
1960 ON "issue"."id" = "losing_initiative"."issue_id"
1961 LEFT JOIN "vote" AS "better_vote"
1962 ON "direct_voter"."member_id" = "better_vote"."member_id"
1963 AND "winning_initiative"."id" = "better_vote"."initiative_id"
1964 LEFT JOIN "vote" AS "worse_vote"
1965 ON "direct_voter"."member_id" = "worse_vote"."member_id"
1966 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
1967 WHERE "issue"."closed" NOTNULL
1968 AND "issue"."cleaned" ISNULL
1969 AND (
1970 "winning_initiative"."id" != "losing_initiative"."id" OR
1971 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
1972 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
1973 GROUP BY
1974 "issue"."id",
1975 "winning_initiative"."id",
1976 "losing_initiative"."id";
1978 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';
1981 CREATE VIEW "open_issue" AS
1982 SELECT * FROM "issue" WHERE "closed" ISNULL;
1984 COMMENT ON VIEW "open_issue" IS 'All open issues';
1987 CREATE VIEW "issue_with_ranks_missing" AS
1988 SELECT * FROM "issue"
1989 WHERE "fully_frozen" NOTNULL
1990 AND "closed" NOTNULL
1991 AND "ranks_available" = FALSE;
1993 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
1996 CREATE VIEW "member_contingent" AS
1997 SELECT
1998 "member"."id" AS "member_id",
1999 "contingent"."time_frame",
2000 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
2001 (
2002 SELECT count(1) FROM "draft"
2003 WHERE "draft"."author_id" = "member"."id"
2004 AND "draft"."created" > now() - "contingent"."time_frame"
2005 ) + (
2006 SELECT count(1) FROM "suggestion"
2007 WHERE "suggestion"."author_id" = "member"."id"
2008 AND "suggestion"."created" > now() - "contingent"."time_frame"
2009 )
2010 ELSE NULL END AS "text_entry_count",
2011 "contingent"."text_entry_limit",
2012 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
2013 SELECT count(1) FROM "opening_draft"
2014 WHERE "opening_draft"."author_id" = "member"."id"
2015 AND "opening_draft"."created" > now() - "contingent"."time_frame"
2016 ) ELSE NULL END AS "initiative_count",
2017 "contingent"."initiative_limit"
2018 FROM "member" CROSS JOIN "contingent";
2020 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
2022 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
2023 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
2026 CREATE VIEW "member_contingent_left" AS
2027 SELECT
2028 "member_id",
2029 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
2030 max("initiative_limit" - "initiative_count") AS "initiatives_left"
2031 FROM "member_contingent" GROUP BY "member_id";
2033 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.';
2036 CREATE VIEW "event_seen_by_member" AS
2037 SELECT
2038 "member"."id" AS "seen_by_member_id",
2039 CASE WHEN "event"."state" IN (
2040 'voting',
2041 'finished_without_winner',
2042 'finished_with_winner'
2043 ) THEN
2044 'voting'::"notify_level"
2045 ELSE
2046 CASE WHEN "event"."state" IN (
2047 'verification',
2048 'canceled_after_revocation_during_verification',
2049 'canceled_no_initiative_admitted'
2050 ) THEN
2051 'verification'::"notify_level"
2052 ELSE
2053 CASE WHEN "event"."state" IN (
2054 'discussion',
2055 'canceled_after_revocation_during_discussion'
2056 ) THEN
2057 'discussion'::"notify_level"
2058 ELSE
2059 'all'::"notify_level"
2060 END
2061 END
2062 END AS "notify_level",
2063 "event".*
2064 FROM "member" CROSS JOIN "event"
2065 LEFT JOIN "issue"
2066 ON "event"."issue_id" = "issue"."id"
2067 LEFT JOIN "membership"
2068 ON "member"."id" = "membership"."member_id"
2069 AND "issue"."area_id" = "membership"."area_id"
2070 LEFT JOIN "interest"
2071 ON "member"."id" = "interest"."member_id"
2072 AND "event"."issue_id" = "interest"."issue_id"
2073 LEFT JOIN "supporter"
2074 ON "member"."id" = "supporter"."member_id"
2075 AND "event"."initiative_id" = "supporter"."initiative_id"
2076 LEFT JOIN "ignored_member"
2077 ON "member"."id" = "ignored_member"."member_id"
2078 AND "event"."member_id" = "ignored_member"."other_member_id"
2079 LEFT JOIN "ignored_initiative"
2080 ON "member"."id" = "ignored_initiative"."member_id"
2081 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2082 WHERE (
2083 "supporter"."member_id" NOTNULL OR
2084 "interest"."member_id" NOTNULL OR
2085 ( "membership"."member_id" NOTNULL AND
2086 "event"."event" IN (
2087 'issue_state_changed',
2088 'initiative_created_in_new_issue',
2089 'initiative_created_in_existing_issue',
2090 'initiative_revoked' ) ) )
2091 AND "ignored_member"."member_id" ISNULL
2092 AND "ignored_initiative"."member_id" ISNULL;
2094 COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support';
2097 CREATE VIEW "pending_notification" AS
2098 SELECT
2099 "member"."id" AS "seen_by_member_id",
2100 "event".*
2101 FROM "member" CROSS JOIN "event"
2102 LEFT JOIN "issue"
2103 ON "event"."issue_id" = "issue"."id"
2104 LEFT JOIN "membership"
2105 ON "member"."id" = "membership"."member_id"
2106 AND "issue"."area_id" = "membership"."area_id"
2107 LEFT JOIN "interest"
2108 ON "member"."id" = "interest"."member_id"
2109 AND "event"."issue_id" = "interest"."issue_id"
2110 LEFT JOIN "supporter"
2111 ON "member"."id" = "supporter"."member_id"
2112 AND "event"."initiative_id" = "supporter"."initiative_id"
2113 LEFT JOIN "ignored_member"
2114 ON "member"."id" = "ignored_member"."member_id"
2115 AND "event"."member_id" = "ignored_member"."other_member_id"
2116 LEFT JOIN "ignored_initiative"
2117 ON "member"."id" = "ignored_initiative"."member_id"
2118 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2119 WHERE (
2120 "member"."notify_event_id" ISNULL OR
2121 ( "member"."notify_event_id" NOTNULL AND
2122 "member"."notify_event_id" < "event"."id" ) )
2123 AND (
2124 ( "member"."notify_level" >= 'all' ) OR
2125 ( "member"."notify_level" >= 'voting' AND
2126 "event"."state" IN (
2127 'voting',
2128 'finished_without_winner',
2129 'finished_with_winner' ) ) OR
2130 ( "member"."notify_level" >= 'verification' AND
2131 "event"."state" IN (
2132 'verification',
2133 'canceled_after_revocation_during_verification',
2134 'canceled_no_initiative_admitted' ) ) OR
2135 ( "member"."notify_level" >= 'discussion' AND
2136 "event"."state" IN (
2137 'discussion',
2138 'canceled_after_revocation_during_discussion' ) ) )
2139 AND (
2140 "supporter"."member_id" NOTNULL OR
2141 "interest"."member_id" NOTNULL OR
2142 ( "membership"."member_id" NOTNULL AND
2143 "event"."event" IN (
2144 'issue_state_changed',
2145 'initiative_created_in_new_issue',
2146 'initiative_created_in_existing_issue',
2147 'initiative_revoked' ) ) )
2148 AND "ignored_member"."member_id" ISNULL
2149 AND "ignored_initiative"."member_id" ISNULL;
2151 COMMENT ON VIEW "pending_notification" IS 'Events to be sent to "notify_email" address of member referred to by "seen_by_member_id"';
2154 CREATE TYPE "timeline_event" AS ENUM (
2155 'issue_created',
2156 'issue_canceled',
2157 'issue_accepted',
2158 'issue_half_frozen',
2159 'issue_finished_without_voting',
2160 'issue_voting_started',
2161 'issue_finished_after_voting',
2162 'initiative_created',
2163 'initiative_revoked',
2164 'draft_created',
2165 'suggestion_created');
2167 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)';
2170 CREATE VIEW "timeline_issue" AS
2171 SELECT
2172 "created" AS "occurrence",
2173 'issue_created'::"timeline_event" AS "event",
2174 "id" AS "issue_id"
2175 FROM "issue"
2176 UNION ALL
2177 SELECT
2178 "closed" AS "occurrence",
2179 'issue_canceled'::"timeline_event" AS "event",
2180 "id" AS "issue_id"
2181 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
2182 UNION ALL
2183 SELECT
2184 "accepted" AS "occurrence",
2185 'issue_accepted'::"timeline_event" AS "event",
2186 "id" AS "issue_id"
2187 FROM "issue" WHERE "accepted" NOTNULL
2188 UNION ALL
2189 SELECT
2190 "half_frozen" AS "occurrence",
2191 'issue_half_frozen'::"timeline_event" AS "event",
2192 "id" AS "issue_id"
2193 FROM "issue" WHERE "half_frozen" NOTNULL
2194 UNION ALL
2195 SELECT
2196 "fully_frozen" AS "occurrence",
2197 'issue_voting_started'::"timeline_event" AS "event",
2198 "id" AS "issue_id"
2199 FROM "issue"
2200 WHERE "fully_frozen" NOTNULL
2201 AND ("closed" ISNULL OR "closed" != "fully_frozen")
2202 UNION ALL
2203 SELECT
2204 "closed" AS "occurrence",
2205 CASE WHEN "fully_frozen" = "closed" THEN
2206 'issue_finished_without_voting'::"timeline_event"
2207 ELSE
2208 'issue_finished_after_voting'::"timeline_event"
2209 END AS "event",
2210 "id" AS "issue_id"
2211 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
2213 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)';
2216 CREATE VIEW "timeline_initiative" AS
2217 SELECT
2218 "created" AS "occurrence",
2219 'initiative_created'::"timeline_event" AS "event",
2220 "id" AS "initiative_id"
2221 FROM "initiative"
2222 UNION ALL
2223 SELECT
2224 "revoked" AS "occurrence",
2225 'initiative_revoked'::"timeline_event" AS "event",
2226 "id" AS "initiative_id"
2227 FROM "initiative" WHERE "revoked" NOTNULL;
2229 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)';
2232 CREATE VIEW "timeline_draft" AS
2233 SELECT
2234 "created" AS "occurrence",
2235 'draft_created'::"timeline_event" AS "event",
2236 "id" AS "draft_id"
2237 FROM "draft";
2239 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)';
2242 CREATE VIEW "timeline_suggestion" AS
2243 SELECT
2244 "created" AS "occurrence",
2245 'suggestion_created'::"timeline_event" AS "event",
2246 "id" AS "suggestion_id"
2247 FROM "suggestion";
2249 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)';
2252 CREATE VIEW "timeline" AS
2253 SELECT
2254 "occurrence",
2255 "event",
2256 "issue_id",
2257 NULL AS "initiative_id",
2258 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
2259 NULL::INT8 AS "suggestion_id"
2260 FROM "timeline_issue"
2261 UNION ALL
2262 SELECT
2263 "occurrence",
2264 "event",
2265 NULL AS "issue_id",
2266 "initiative_id",
2267 NULL AS "draft_id",
2268 NULL AS "suggestion_id"
2269 FROM "timeline_initiative"
2270 UNION ALL
2271 SELECT
2272 "occurrence",
2273 "event",
2274 NULL AS "issue_id",
2275 NULL AS "initiative_id",
2276 "draft_id",
2277 NULL AS "suggestion_id"
2278 FROM "timeline_draft"
2279 UNION ALL
2280 SELECT
2281 "occurrence",
2282 "event",
2283 NULL AS "issue_id",
2284 NULL AS "initiative_id",
2285 NULL AS "draft_id",
2286 "suggestion_id"
2287 FROM "timeline_suggestion";
2289 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)';
2293 --------------------------------------------------
2294 -- Set returning function for delegation chains --
2295 --------------------------------------------------
2298 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
2299 ('first', 'intermediate', 'last', 'repetition');
2301 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
2304 CREATE TYPE "delegation_chain_row" AS (
2305 "index" INT4,
2306 "member_id" INT4,
2307 "member_valid" BOOLEAN,
2308 "participation" BOOLEAN,
2309 "overridden" BOOLEAN,
2310 "scope_in" "delegation_scope",
2311 "scope_out" "delegation_scope",
2312 "disabled_out" BOOLEAN,
2313 "loop" "delegation_chain_loop_tag" );
2315 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
2317 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
2318 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';
2319 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
2320 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
2321 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
2322 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
2323 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
2326 CREATE FUNCTION "delegation_chain"
2327 ( "member_id_p" "member"."id"%TYPE,
2328 "unit_id_p" "unit"."id"%TYPE,
2329 "area_id_p" "area"."id"%TYPE,
2330 "issue_id_p" "issue"."id"%TYPE,
2331 "simulate_trustee_id_p" "member"."id"%TYPE )
2332 RETURNS SETOF "delegation_chain_row"
2333 LANGUAGE 'plpgsql' STABLE AS $$
2334 DECLARE
2335 "scope_v" "delegation_scope";
2336 "unit_id_v" "unit"."id"%TYPE;
2337 "area_id_v" "area"."id"%TYPE;
2338 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2339 "loop_member_id_v" "member"."id"%TYPE;
2340 "output_row" "delegation_chain_row";
2341 "output_rows" "delegation_chain_row"[];
2342 "delegation_row" "delegation"%ROWTYPE;
2343 "row_count" INT4;
2344 "i" INT4;
2345 "loop_v" BOOLEAN;
2346 BEGIN
2347 IF
2348 "unit_id_p" NOTNULL AND
2349 "area_id_p" ISNULL AND
2350 "issue_id_p" ISNULL
2351 THEN
2352 "scope_v" := 'unit';
2353 "unit_id_v" := "unit_id_p";
2354 ELSIF
2355 "unit_id_p" ISNULL AND
2356 "area_id_p" NOTNULL AND
2357 "issue_id_p" ISNULL
2358 THEN
2359 "scope_v" := 'area';
2360 "area_id_v" := "area_id_p";
2361 SELECT "unit_id" INTO "unit_id_v"
2362 FROM "area" WHERE "id" = "area_id_v";
2363 ELSIF
2364 "unit_id_p" ISNULL AND
2365 "area_id_p" ISNULL AND
2366 "issue_id_p" NOTNULL
2367 THEN
2368 "scope_v" := 'issue';
2369 SELECT "area_id" INTO "area_id_v"
2370 FROM "issue" WHERE "id" = "issue_id_p";
2371 SELECT "unit_id" INTO "unit_id_v"
2372 FROM "area" WHERE "id" = "area_id_v";
2373 ELSE
2374 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2375 END IF;
2376 "visited_member_ids" := '{}';
2377 "loop_member_id_v" := NULL;
2378 "output_rows" := '{}';
2379 "output_row"."index" := 0;
2380 "output_row"."member_id" := "member_id_p";
2381 "output_row"."member_valid" := TRUE;
2382 "output_row"."participation" := FALSE;
2383 "output_row"."overridden" := FALSE;
2384 "output_row"."disabled_out" := FALSE;
2385 "output_row"."scope_out" := NULL;
2386 LOOP
2387 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2388 "loop_member_id_v" := "output_row"."member_id";
2389 ELSE
2390 "visited_member_ids" :=
2391 "visited_member_ids" || "output_row"."member_id";
2392 END IF;
2393 IF "output_row"."participation" THEN
2394 "output_row"."overridden" := TRUE;
2395 END IF;
2396 "output_row"."scope_in" := "output_row"."scope_out";
2397 IF EXISTS (
2398 SELECT NULL FROM "member" JOIN "privilege"
2399 ON "privilege"."member_id" = "member"."id"
2400 AND "privilege"."unit_id" = "unit_id_v"
2401 WHERE "id" = "output_row"."member_id"
2402 AND "member"."active" AND "privilege"."voting_right"
2403 ) THEN
2404 IF "scope_v" = 'unit' THEN
2405 SELECT * INTO "delegation_row" FROM "delegation"
2406 WHERE "truster_id" = "output_row"."member_id"
2407 AND "unit_id" = "unit_id_v";
2408 ELSIF "scope_v" = 'area' THEN
2409 "output_row"."participation" := EXISTS (
2410 SELECT NULL FROM "membership"
2411 WHERE "area_id" = "area_id_p"
2412 AND "member_id" = "output_row"."member_id"
2413 );
2414 SELECT * INTO "delegation_row" FROM "delegation"
2415 WHERE "truster_id" = "output_row"."member_id"
2416 AND (
2417 "unit_id" = "unit_id_v" OR
2418 "area_id" = "area_id_v"
2419 )
2420 ORDER BY "scope" DESC;
2421 ELSIF "scope_v" = 'issue' THEN
2422 "output_row"."participation" := EXISTS (
2423 SELECT NULL FROM "interest"
2424 WHERE "issue_id" = "issue_id_p"
2425 AND "member_id" = "output_row"."member_id"
2426 );
2427 SELECT * INTO "delegation_row" FROM "delegation"
2428 WHERE "truster_id" = "output_row"."member_id"
2429 AND (
2430 "unit_id" = "unit_id_v" OR
2431 "area_id" = "area_id_v" OR
2432 "issue_id" = "issue_id_p"
2433 )
2434 ORDER BY "scope" DESC;
2435 END IF;
2436 ELSE
2437 "output_row"."member_valid" := FALSE;
2438 "output_row"."participation" := FALSE;
2439 "output_row"."scope_out" := NULL;
2440 "delegation_row" := ROW(NULL);
2441 END IF;
2442 IF
2443 "output_row"."member_id" = "member_id_p" AND
2444 "simulate_trustee_id_p" NOTNULL
2445 THEN
2446 "output_row"."scope_out" := "scope_v";
2447 "output_rows" := "output_rows" || "output_row";
2448 "output_row"."member_id" := "simulate_trustee_id_p";
2449 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2450 "output_row"."scope_out" := "delegation_row"."scope";
2451 "output_rows" := "output_rows" || "output_row";
2452 "output_row"."member_id" := "delegation_row"."trustee_id";
2453 ELSIF "delegation_row"."scope" NOTNULL THEN
2454 "output_row"."scope_out" := "delegation_row"."scope";
2455 "output_row"."disabled_out" := TRUE;
2456 "output_rows" := "output_rows" || "output_row";
2457 EXIT;
2458 ELSE
2459 "output_row"."scope_out" := NULL;
2460 "output_rows" := "output_rows" || "output_row";
2461 EXIT;
2462 END IF;
2463 EXIT WHEN "loop_member_id_v" NOTNULL;
2464 "output_row"."index" := "output_row"."index" + 1;
2465 END LOOP;
2466 "row_count" := array_upper("output_rows", 1);
2467 "i" := 1;
2468 "loop_v" := FALSE;
2469 LOOP
2470 "output_row" := "output_rows"["i"];
2471 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2472 IF "loop_v" THEN
2473 IF "i" + 1 = "row_count" THEN
2474 "output_row"."loop" := 'last';
2475 ELSIF "i" = "row_count" THEN
2476 "output_row"."loop" := 'repetition';
2477 ELSE
2478 "output_row"."loop" := 'intermediate';
2479 END IF;
2480 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2481 "output_row"."loop" := 'first';
2482 "loop_v" := TRUE;
2483 END IF;
2484 IF "scope_v" = 'unit' THEN
2485 "output_row"."participation" := NULL;
2486 END IF;
2487 RETURN NEXT "output_row";
2488 "i" := "i" + 1;
2489 END LOOP;
2490 RETURN;
2491 END;
2492 $$;
2494 COMMENT ON FUNCTION "delegation_chain"
2495 ( "member"."id"%TYPE,
2496 "unit"."id"%TYPE,
2497 "area"."id"%TYPE,
2498 "issue"."id"%TYPE,
2499 "member"."id"%TYPE )
2500 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
2503 CREATE FUNCTION "delegation_chain"
2504 ( "member_id_p" "member"."id"%TYPE,
2505 "unit_id_p" "unit"."id"%TYPE,
2506 "area_id_p" "area"."id"%TYPE,
2507 "issue_id_p" "issue"."id"%TYPE )
2508 RETURNS SETOF "delegation_chain_row"
2509 LANGUAGE 'plpgsql' STABLE AS $$
2510 DECLARE
2511 "result_row" "delegation_chain_row";
2512 BEGIN
2513 FOR "result_row" IN
2514 SELECT * FROM "delegation_chain"(
2515 "member_id_p", "unit_id_p", "area_id_p", "issue_id_p", NULL
2516 )
2517 LOOP
2518 RETURN NEXT "result_row";
2519 END LOOP;
2520 RETURN;
2521 END;
2522 $$;
2524 COMMENT ON FUNCTION "delegation_chain"
2525 ( "member"."id"%TYPE,
2526 "unit"."id"%TYPE,
2527 "area"."id"%TYPE,
2528 "issue"."id"%TYPE )
2529 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
2533 ------------------------------
2534 -- Comparison by vote count --
2535 ------------------------------
2537 CREATE FUNCTION "vote_ratio"
2538 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
2539 "negative_votes_p" "initiative"."negative_votes"%TYPE )
2540 RETURNS FLOAT8
2541 LANGUAGE 'plpgsql' STABLE AS $$
2542 BEGIN
2543 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
2544 RETURN
2545 "positive_votes_p"::FLOAT8 /
2546 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
2547 ELSIF "positive_votes_p" > 0 THEN
2548 RETURN "positive_votes_p";
2549 ELSIF "negative_votes_p" > 0 THEN
2550 RETURN 1 - "negative_votes_p";
2551 ELSE
2552 RETURN 0.5;
2553 END IF;
2554 END;
2555 $$;
2557 COMMENT ON FUNCTION "vote_ratio"
2558 ( "initiative"."positive_votes"%TYPE,
2559 "initiative"."negative_votes"%TYPE )
2560 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.';
2564 ------------------------------------------------
2565 -- Locking for snapshots and voting procedure --
2566 ------------------------------------------------
2569 CREATE FUNCTION "share_row_lock_issue_trigger"()
2570 RETURNS TRIGGER
2571 LANGUAGE 'plpgsql' VOLATILE AS $$
2572 BEGIN
2573 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2574 PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;
2575 END IF;
2576 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2577 PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;
2578 RETURN NEW;
2579 ELSE
2580 RETURN OLD;
2581 END IF;
2582 END;
2583 $$;
2585 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';
2588 CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()
2589 RETURNS TRIGGER
2590 LANGUAGE 'plpgsql' VOLATILE AS $$
2591 BEGIN
2592 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2593 PERFORM NULL FROM "issue"
2594 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2595 WHERE "initiative"."id" = OLD."initiative_id"
2596 FOR SHARE OF "issue";
2597 END IF;
2598 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2599 PERFORM NULL FROM "issue"
2600 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2601 WHERE "initiative"."id" = NEW."initiative_id"
2602 FOR SHARE OF "issue";
2603 RETURN NEW;
2604 ELSE
2605 RETURN OLD;
2606 END IF;
2607 END;
2608 $$;
2610 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';
2613 CREATE TRIGGER "share_row_lock_issue"
2614 BEFORE INSERT OR UPDATE OR DELETE ON "initiative"
2615 FOR EACH ROW EXECUTE PROCEDURE
2616 "share_row_lock_issue_trigger"();
2618 CREATE TRIGGER "share_row_lock_issue"
2619 BEFORE INSERT OR UPDATE OR DELETE ON "interest"
2620 FOR EACH ROW EXECUTE PROCEDURE
2621 "share_row_lock_issue_trigger"();
2623 CREATE TRIGGER "share_row_lock_issue"
2624 BEFORE INSERT OR UPDATE OR DELETE ON "supporter"
2625 FOR EACH ROW EXECUTE PROCEDURE
2626 "share_row_lock_issue_trigger"();
2628 CREATE TRIGGER "share_row_lock_issue_via_initiative"
2629 BEFORE INSERT OR UPDATE OR DELETE ON "opinion"
2630 FOR EACH ROW EXECUTE PROCEDURE
2631 "share_row_lock_issue_via_initiative_trigger"();
2633 CREATE TRIGGER "share_row_lock_issue"
2634 BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter"
2635 FOR EACH ROW EXECUTE PROCEDURE
2636 "share_row_lock_issue_trigger"();
2638 CREATE TRIGGER "share_row_lock_issue"
2639 BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter"
2640 FOR EACH ROW EXECUTE PROCEDURE
2641 "share_row_lock_issue_trigger"();
2643 CREATE TRIGGER "share_row_lock_issue"
2644 BEFORE INSERT OR UPDATE OR DELETE ON "vote"
2645 FOR EACH ROW EXECUTE PROCEDURE
2646 "share_row_lock_issue_trigger"();
2648 COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function';
2649 COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function';
2650 COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function';
2651 COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function';
2652 COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function';
2653 COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function';
2654 COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function';
2657 CREATE FUNCTION "lock_issue"
2658 ( "issue_id_p" "issue"."id"%TYPE )
2659 RETURNS VOID
2660 LANGUAGE 'plpgsql' VOLATILE AS $$
2661 BEGIN
2662 LOCK TABLE "member" IN SHARE MODE;
2663 LOCK TABLE "privilege" IN SHARE MODE;
2664 LOCK TABLE "membership" IN SHARE MODE;
2665 LOCK TABLE "policy" IN SHARE MODE;
2666 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2667 -- NOTE: The row-level exclusive lock in combination with the
2668 -- share_row_lock_issue(_via_initiative)_trigger functions (which
2669 -- acquire a row-level share lock on the issue) ensure that no data
2670 -- is changed, which could affect calculation of snapshots or
2671 -- counting of votes. Table "delegation" must be table-level-locked,
2672 -- as it also contains issue- and global-scope delegations.
2673 LOCK TABLE "delegation" IN SHARE MODE;
2674 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
2675 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
2676 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
2677 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
2678 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
2679 RETURN;
2680 END;
2681 $$;
2683 COMMENT ON FUNCTION "lock_issue"
2684 ( "issue"."id"%TYPE )
2685 IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';
2689 ------------------------------------------------------------------------
2690 -- Regular tasks, except calculcation of snapshots and voting results --
2691 ------------------------------------------------------------------------
2693 CREATE FUNCTION "check_activity"()
2694 RETURNS VOID
2695 LANGUAGE 'plpgsql' VOLATILE AS $$
2696 DECLARE
2697 "system_setting_row" "system_setting"%ROWTYPE;
2698 BEGIN
2699 SELECT * INTO "system_setting_row" FROM "system_setting";
2700 LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
2701 IF "system_setting_row"."member_ttl" NOTNULL THEN
2702 UPDATE "member" SET "active" = FALSE
2703 WHERE "active" = TRUE
2704 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
2705 END IF;
2706 RETURN;
2707 END;
2708 $$;
2710 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
2713 CREATE FUNCTION "calculate_member_counts"()
2714 RETURNS VOID
2715 LANGUAGE 'plpgsql' VOLATILE AS $$
2716 BEGIN
2717 LOCK TABLE "member" IN SHARE MODE;
2718 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
2719 LOCK TABLE "unit" IN EXCLUSIVE MODE;
2720 LOCK TABLE "area" IN EXCLUSIVE MODE;
2721 LOCK TABLE "privilege" IN SHARE MODE;
2722 LOCK TABLE "membership" IN SHARE MODE;
2723 DELETE FROM "member_count";
2724 INSERT INTO "member_count" ("total_count")
2725 SELECT "total_count" FROM "member_count_view";
2726 UPDATE "unit" SET "member_count" = "view"."member_count"
2727 FROM "unit_member_count" AS "view"
2728 WHERE "view"."unit_id" = "unit"."id";
2729 UPDATE "area" SET
2730 "direct_member_count" = "view"."direct_member_count",
2731 "member_weight" = "view"."member_weight"
2732 FROM "area_member_count" AS "view"
2733 WHERE "view"."area_id" = "area"."id";
2734 RETURN;
2735 END;
2736 $$;
2738 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"';
2742 ------------------------------
2743 -- Calculation of snapshots --
2744 ------------------------------
2746 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
2747 ( "issue_id_p" "issue"."id"%TYPE,
2748 "member_id_p" "member"."id"%TYPE,
2749 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2750 RETURNS "direct_population_snapshot"."weight"%TYPE
2751 LANGUAGE 'plpgsql' VOLATILE AS $$
2752 DECLARE
2753 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2754 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
2755 "weight_v" INT4;
2756 "sub_weight_v" INT4;
2757 BEGIN
2758 "weight_v" := 0;
2759 FOR "issue_delegation_row" IN
2760 SELECT * FROM "issue_delegation"
2761 WHERE "trustee_id" = "member_id_p"
2762 AND "issue_id" = "issue_id_p"
2763 LOOP
2764 IF NOT EXISTS (
2765 SELECT NULL FROM "direct_population_snapshot"
2766 WHERE "issue_id" = "issue_id_p"
2767 AND "event" = 'periodic'
2768 AND "member_id" = "issue_delegation_row"."truster_id"
2769 ) AND NOT EXISTS (
2770 SELECT NULL FROM "delegating_population_snapshot"
2771 WHERE "issue_id" = "issue_id_p"
2772 AND "event" = 'periodic'
2773 AND "member_id" = "issue_delegation_row"."truster_id"
2774 ) THEN
2775 "delegate_member_ids_v" :=
2776 "member_id_p" || "delegate_member_ids_p";
2777 INSERT INTO "delegating_population_snapshot" (
2778 "issue_id",
2779 "event",
2780 "member_id",
2781 "scope",
2782 "delegate_member_ids"
2783 ) VALUES (
2784 "issue_id_p",
2785 'periodic',
2786 "issue_delegation_row"."truster_id",
2787 "issue_delegation_row"."scope",
2788 "delegate_member_ids_v"
2789 );
2790 "sub_weight_v" := 1 +
2791 "weight_of_added_delegations_for_population_snapshot"(
2792 "issue_id_p",
2793 "issue_delegation_row"."truster_id",
2794 "delegate_member_ids_v"
2795 );
2796 UPDATE "delegating_population_snapshot"
2797 SET "weight" = "sub_weight_v"
2798 WHERE "issue_id" = "issue_id_p"
2799 AND "event" = 'periodic'
2800 AND "member_id" = "issue_delegation_row"."truster_id";
2801 "weight_v" := "weight_v" + "sub_weight_v";
2802 END IF;
2803 END LOOP;
2804 RETURN "weight_v";
2805 END;
2806 $$;
2808 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
2809 ( "issue"."id"%TYPE,
2810 "member"."id"%TYPE,
2811 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2812 IS 'Helper function for "create_population_snapshot" function';
2815 CREATE FUNCTION "create_population_snapshot"
2816 ( "issue_id_p" "issue"."id"%TYPE )
2817 RETURNS VOID
2818 LANGUAGE 'plpgsql' VOLATILE AS $$
2819 DECLARE
2820 "member_id_v" "member"."id"%TYPE;
2821 BEGIN
2822 DELETE FROM "direct_population_snapshot"
2823 WHERE "issue_id" = "issue_id_p"
2824 AND "event" = 'periodic';
2825 DELETE FROM "delegating_population_snapshot"
2826 WHERE "issue_id" = "issue_id_p"
2827 AND "event" = 'periodic';
2828 INSERT INTO "direct_population_snapshot"
2829 ("issue_id", "event", "member_id")
2830 SELECT
2831 "issue_id_p" AS "issue_id",
2832 'periodic'::"snapshot_event" AS "event",
2833 "member"."id" AS "member_id"
2834 FROM "issue"
2835 JOIN "area" ON "issue"."area_id" = "area"."id"
2836 JOIN "membership" ON "area"."id" = "membership"."area_id"
2837 JOIN "member" ON "membership"."member_id" = "member"."id"
2838 JOIN "privilege"
2839 ON "privilege"."unit_id" = "area"."unit_id"
2840 AND "privilege"."member_id" = "member"."id"
2841 WHERE "issue"."id" = "issue_id_p"
2842 AND "member"."active" AND "privilege"."voting_right"
2843 UNION
2844 SELECT
2845 "issue_id_p" AS "issue_id",
2846 'periodic'::"snapshot_event" AS "event",
2847 "member"."id" AS "member_id"
2848 FROM "issue"
2849 JOIN "area" ON "issue"."area_id" = "area"."id"
2850 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2851 JOIN "member" ON "interest"."member_id" = "member"."id"
2852 JOIN "privilege"
2853 ON "privilege"."unit_id" = "area"."unit_id"
2854 AND "privilege"."member_id" = "member"."id"
2855 WHERE "issue"."id" = "issue_id_p"
2856 AND "member"."active" AND "privilege"."voting_right";
2857 FOR "member_id_v" IN
2858 SELECT "member_id" FROM "direct_population_snapshot"
2859 WHERE "issue_id" = "issue_id_p"
2860 AND "event" = 'periodic'
2861 LOOP
2862 UPDATE "direct_population_snapshot" SET
2863 "weight" = 1 +
2864 "weight_of_added_delegations_for_population_snapshot"(
2865 "issue_id_p",
2866 "member_id_v",
2867 '{}'
2868 )
2869 WHERE "issue_id" = "issue_id_p"
2870 AND "event" = 'periodic'
2871 AND "member_id" = "member_id_v";
2872 END LOOP;
2873 RETURN;
2874 END;
2875 $$;
2877 COMMENT ON FUNCTION "create_population_snapshot"
2878 ( "issue"."id"%TYPE )
2879 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.';
2882 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2883 ( "issue_id_p" "issue"."id"%TYPE,
2884 "member_id_p" "member"."id"%TYPE,
2885 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2886 RETURNS "direct_interest_snapshot"."weight"%TYPE
2887 LANGUAGE 'plpgsql' VOLATILE AS $$
2888 DECLARE
2889 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2890 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2891 "weight_v" INT4;
2892 "sub_weight_v" INT4;
2893 BEGIN
2894 "weight_v" := 0;
2895 FOR "issue_delegation_row" IN
2896 SELECT * FROM "issue_delegation"
2897 WHERE "trustee_id" = "member_id_p"
2898 AND "issue_id" = "issue_id_p"
2899 LOOP
2900 IF NOT EXISTS (
2901 SELECT NULL FROM "direct_interest_snapshot"
2902 WHERE "issue_id" = "issue_id_p"
2903 AND "event" = 'periodic'
2904 AND "member_id" = "issue_delegation_row"."truster_id"
2905 ) AND NOT EXISTS (
2906 SELECT NULL FROM "delegating_interest_snapshot"
2907 WHERE "issue_id" = "issue_id_p"
2908 AND "event" = 'periodic'
2909 AND "member_id" = "issue_delegation_row"."truster_id"
2910 ) THEN
2911 "delegate_member_ids_v" :=
2912 "member_id_p" || "delegate_member_ids_p";
2913 INSERT INTO "delegating_interest_snapshot" (
2914 "issue_id",
2915 "event",
2916 "member_id",
2917 "scope",
2918 "delegate_member_ids"
2919 ) VALUES (
2920 "issue_id_p",
2921 'periodic',
2922 "issue_delegation_row"."truster_id",
2923 "issue_delegation_row"."scope",
2924 "delegate_member_ids_v"
2925 );
2926 "sub_weight_v" := 1 +
2927 "weight_of_added_delegations_for_interest_snapshot"(
2928 "issue_id_p",
2929 "issue_delegation_row"."truster_id",
2930 "delegate_member_ids_v"
2931 );
2932 UPDATE "delegating_interest_snapshot"
2933 SET "weight" = "sub_weight_v"
2934 WHERE "issue_id" = "issue_id_p"
2935 AND "event" = 'periodic'
2936 AND "member_id" = "issue_delegation_row"."truster_id";
2937 "weight_v" := "weight_v" + "sub_weight_v";
2938 END IF;
2939 END LOOP;
2940 RETURN "weight_v";
2941 END;
2942 $$;
2944 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2945 ( "issue"."id"%TYPE,
2946 "member"."id"%TYPE,
2947 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2948 IS 'Helper function for "create_interest_snapshot" function';
2951 CREATE FUNCTION "create_interest_snapshot"
2952 ( "issue_id_p" "issue"."id"%TYPE )
2953 RETURNS VOID
2954 LANGUAGE 'plpgsql' VOLATILE AS $$
2955 DECLARE
2956 "member_id_v" "member"."id"%TYPE;
2957 BEGIN
2958 DELETE FROM "direct_interest_snapshot"
2959 WHERE "issue_id" = "issue_id_p"
2960 AND "event" = 'periodic';
2961 DELETE FROM "delegating_interest_snapshot"
2962 WHERE "issue_id" = "issue_id_p"
2963 AND "event" = 'periodic';
2964 DELETE FROM "direct_supporter_snapshot"
2965 WHERE "issue_id" = "issue_id_p"
2966 AND "event" = 'periodic';
2967 INSERT INTO "direct_interest_snapshot"
2968 ("issue_id", "event", "member_id")
2969 SELECT
2970 "issue_id_p" AS "issue_id",
2971 'periodic' AS "event",
2972 "member"."id" AS "member_id"
2973 FROM "issue"
2974 JOIN "area" ON "issue"."area_id" = "area"."id"
2975 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2976 JOIN "member" ON "interest"."member_id" = "member"."id"
2977 JOIN "privilege"
2978 ON "privilege"."unit_id" = "area"."unit_id"
2979 AND "privilege"."member_id" = "member"."id"
2980 WHERE "issue"."id" = "issue_id_p"
2981 AND "member"."active" AND "privilege"."voting_right";
2982 FOR "member_id_v" IN
2983 SELECT "member_id" FROM "direct_interest_snapshot"
2984 WHERE "issue_id" = "issue_id_p"
2985 AND "event" = 'periodic'
2986 LOOP
2987 UPDATE "direct_interest_snapshot" SET
2988 "weight" = 1 +
2989 "weight_of_added_delegations_for_interest_snapshot"(
2990 "issue_id_p",
2991 "member_id_v",
2992 '{}'
2993 )
2994 WHERE "issue_id" = "issue_id_p"
2995 AND "event" = 'periodic'
2996 AND "member_id" = "member_id_v";
2997 END LOOP;
2998 INSERT INTO "direct_supporter_snapshot"
2999 ( "issue_id", "initiative_id", "event", "member_id",
3000 "draft_id", "informed", "satisfied" )
3001 SELECT
3002 "issue_id_p" AS "issue_id",
3003 "initiative"."id" AS "initiative_id",
3004 'periodic' AS "event",
3005 "supporter"."member_id" AS "member_id",
3006 "supporter"."draft_id" AS "draft_id",
3007 "supporter"."draft_id" = "current_draft"."id" AS "informed",
3008 NOT EXISTS (
3009 SELECT NULL FROM "critical_opinion"
3010 WHERE "initiative_id" = "initiative"."id"
3011 AND "member_id" = "supporter"."member_id"
3012 ) AS "satisfied"
3013 FROM "initiative"
3014 JOIN "supporter"
3015 ON "supporter"."initiative_id" = "initiative"."id"
3016 JOIN "current_draft"
3017 ON "initiative"."id" = "current_draft"."initiative_id"
3018 JOIN "direct_interest_snapshot"
3019 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
3020 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
3021 AND "event" = 'periodic'
3022 WHERE "initiative"."issue_id" = "issue_id_p";
3023 RETURN;
3024 END;
3025 $$;
3027 COMMENT ON FUNCTION "create_interest_snapshot"
3028 ( "issue"."id"%TYPE )
3029 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.';
3032 CREATE FUNCTION "create_snapshot"
3033 ( "issue_id_p" "issue"."id"%TYPE )
3034 RETURNS VOID
3035 LANGUAGE 'plpgsql' VOLATILE AS $$
3036 DECLARE
3037 "initiative_id_v" "initiative"."id"%TYPE;
3038 "suggestion_id_v" "suggestion"."id"%TYPE;
3039 BEGIN
3040 PERFORM "lock_issue"("issue_id_p");
3041 PERFORM "create_population_snapshot"("issue_id_p");
3042 PERFORM "create_interest_snapshot"("issue_id_p");
3043 UPDATE "issue" SET
3044 "snapshot" = now(),
3045 "latest_snapshot_event" = 'periodic',
3046 "population" = (
3047 SELECT coalesce(sum("weight"), 0)
3048 FROM "direct_population_snapshot"
3049 WHERE "issue_id" = "issue_id_p"
3050 AND "event" = 'periodic'
3051 )
3052 WHERE "id" = "issue_id_p";
3053 FOR "initiative_id_v" IN
3054 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
3055 LOOP
3056 UPDATE "initiative" SET
3057 "supporter_count" = (
3058 SELECT coalesce(sum("di"."weight"), 0)
3059 FROM "direct_interest_snapshot" AS "di"
3060 JOIN "direct_supporter_snapshot" AS "ds"
3061 ON "di"."member_id" = "ds"."member_id"
3062 WHERE "di"."issue_id" = "issue_id_p"
3063 AND "di"."event" = 'periodic'
3064 AND "ds"."initiative_id" = "initiative_id_v"
3065 AND "ds"."event" = 'periodic'
3066 ),
3067 "informed_supporter_count" = (
3068 SELECT coalesce(sum("di"."weight"), 0)
3069 FROM "direct_interest_snapshot" AS "di"
3070 JOIN "direct_supporter_snapshot" AS "ds"
3071 ON "di"."member_id" = "ds"."member_id"
3072 WHERE "di"."issue_id" = "issue_id_p"
3073 AND "di"."event" = 'periodic'
3074 AND "ds"."initiative_id" = "initiative_id_v"
3075 AND "ds"."event" = 'periodic'
3076 AND "ds"."informed"
3077 ),
3078 "satisfied_supporter_count" = (
3079 SELECT coalesce(sum("di"."weight"), 0)
3080 FROM "direct_interest_snapshot" AS "di"
3081 JOIN "direct_supporter_snapshot" AS "ds"
3082 ON "di"."member_id" = "ds"."member_id"
3083 WHERE "di"."issue_id" = "issue_id_p"
3084 AND "di"."event" = 'periodic'
3085 AND "ds"."initiative_id" = "initiative_id_v"
3086 AND "ds"."event" = 'periodic'
3087 AND "ds"."satisfied"
3088 ),
3089 "satisfied_informed_supporter_count" = (
3090 SELECT coalesce(sum("di"."weight"), 0)
3091 FROM "direct_interest_snapshot" AS "di"
3092 JOIN "direct_supporter_snapshot" AS "ds"
3093 ON "di"."member_id" = "ds"."member_id"
3094 WHERE "di"."issue_id" = "issue_id_p"
3095 AND "di"."event" = 'periodic'
3096 AND "ds"."initiative_id" = "initiative_id_v"
3097 AND "ds"."event" = 'periodic'
3098 AND "ds"."informed"
3099 AND "ds"."satisfied"
3100 )
3101 WHERE "id" = "initiative_id_v";
3102 FOR "suggestion_id_v" IN
3103 SELECT "id" FROM "suggestion"
3104 WHERE "initiative_id" = "initiative_id_v"
3105 LOOP
3106 UPDATE "suggestion" SET
3107 "minus2_unfulfilled_count" = (
3108 SELECT coalesce(sum("snapshot"."weight"), 0)
3109 FROM "issue" CROSS JOIN "opinion"
3110 JOIN "direct_interest_snapshot" AS "snapshot"
3111 ON "snapshot"."issue_id" = "issue"."id"
3112 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3113 AND "snapshot"."member_id" = "opinion"."member_id"
3114 WHERE "issue"."id" = "issue_id_p"
3115 AND "opinion"."suggestion_id" = "suggestion_id_v"
3116 AND "opinion"."degree" = -2
3117 AND "opinion"."fulfilled" = FALSE
3118 ),
3119 "minus2_fulfilled_count" = (
3120 SELECT coalesce(sum("snapshot"."weight"), 0)
3121 FROM "issue" CROSS JOIN "opinion"
3122 JOIN "direct_interest_snapshot" AS "snapshot"
3123 ON "snapshot"."issue_id" = "issue"."id"
3124 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3125 AND "snapshot"."member_id" = "opinion"."member_id"
3126 WHERE "issue"."id" = "issue_id_p"
3127 AND "opinion"."suggestion_id" = "suggestion_id_v"
3128 AND "opinion"."degree" = -2
3129 AND "opinion"."fulfilled" = TRUE
3130 ),
3131 "minus1_unfulfilled_count" = (
3132 SELECT coalesce(sum("snapshot"."weight"), 0)
3133 FROM "issue" CROSS JOIN "opinion"
3134 JOIN "direct_interest_snapshot" AS "snapshot"
3135 ON "snapshot"."issue_id" = "issue"."id"
3136 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3137 AND "snapshot"."member_id" = "opinion"."member_id"
3138 WHERE "issue"."id" = "issue_id_p"
3139 AND "opinion"."suggestion_id" = "suggestion_id_v"
3140 AND "opinion"."degree" = -1
3141 AND "opinion"."fulfilled" = FALSE
3142 ),
3143 "minus1_fulfilled_count" = (
3144 SELECT coalesce(sum("snapshot"."weight"), 0)
3145 FROM "issue" CROSS JOIN "opinion"
3146 JOIN "direct_interest_snapshot" AS "snapshot"
3147 ON "snapshot"."issue_id" = "issue"."id"
3148 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3149 AND "snapshot"."member_id" = "opinion"."member_id"
3150 WHERE "issue"."id" = "issue_id_p"
3151 AND "opinion"."suggestion_id" = "suggestion_id_v"
3152 AND "opinion"."degree" = -1
3153 AND "opinion"."fulfilled" = TRUE
3154 ),
3155 "plus1_unfulfilled_count" = (
3156 SELECT coalesce(sum("snapshot"."weight"), 0)
3157 FROM "issue" CROSS JOIN "opinion"
3158 JOIN "direct_interest_snapshot" AS "snapshot"
3159 ON "snapshot"."issue_id" = "issue"."id"
3160 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3161 AND "snapshot"."member_id" = "opinion"."member_id"
3162 WHERE "issue"."id" = "issue_id_p"
3163 AND "opinion"."suggestion_id" = "suggestion_id_v"
3164 AND "opinion"."degree" = 1
3165 AND "opinion"."fulfilled" = FALSE
3166 ),
3167 "plus1_fulfilled_count" = (
3168 SELECT coalesce(sum("snapshot"."weight"), 0)
3169 FROM "issue" CROSS JOIN "opinion"
3170 JOIN "direct_interest_snapshot" AS "snapshot"
3171 ON "snapshot"."issue_id" = "issue"."id"
3172 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3173 AND "snapshot"."member_id" = "opinion"."member_id"
3174 WHERE "issue"."id" = "issue_id_p"
3175 AND "opinion"."suggestion_id" = "suggestion_id_v"
3176 AND "opinion"."degree" = 1
3177 AND "opinion"."fulfilled" = TRUE
3178 ),
3179 "plus2_unfulfilled_count" = (
3180 SELECT coalesce(sum("snapshot"."weight"), 0)
3181 FROM "issue" CROSS JOIN "opinion"
3182 JOIN "direct_interest_snapshot" AS "snapshot"
3183 ON "snapshot"."issue_id" = "issue"."id"
3184 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3185 AND "snapshot"."member_id" = "opinion"."member_id"
3186 WHERE "issue"."id" = "issue_id_p"
3187 AND "opinion"."suggestion_id" = "suggestion_id_v"
3188 AND "opinion"."degree" = 2
3189 AND "opinion"."fulfilled" = FALSE
3190 ),
3191 "plus2_fulfilled_count" = (
3192 SELECT coalesce(sum("snapshot"."weight"), 0)
3193 FROM "issue" CROSS JOIN "opinion"
3194 JOIN "direct_interest_snapshot" AS "snapshot"
3195 ON "snapshot"."issue_id" = "issue"."id"
3196 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3197 AND "snapshot"."member_id" = "opinion"."member_id"
3198 WHERE "issue"."id" = "issue_id_p"
3199 AND "opinion"."suggestion_id" = "suggestion_id_v"
3200 AND "opinion"."degree" = 2
3201 AND "opinion"."fulfilled" = TRUE
3202 )
3203 WHERE "suggestion"."id" = "suggestion_id_v";
3204 END LOOP;
3205 END LOOP;
3206 RETURN;
3207 END;
3208 $$;
3210 COMMENT ON FUNCTION "create_snapshot"
3211 ( "issue"."id"%TYPE )
3212 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.';
3215 CREATE FUNCTION "set_snapshot_event"
3216 ( "issue_id_p" "issue"."id"%TYPE,
3217 "event_p" "snapshot_event" )
3218 RETURNS VOID
3219 LANGUAGE 'plpgsql' VOLATILE AS $$
3220 DECLARE
3221 "event_v" "issue"."latest_snapshot_event"%TYPE;
3222 BEGIN
3223 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
3224 WHERE "id" = "issue_id_p" FOR UPDATE;
3225 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
3226 WHERE "id" = "issue_id_p";
3227 UPDATE "direct_population_snapshot" SET "event" = "event_p"
3228 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3229 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
3230 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3231 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
3232 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3233 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
3234 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3235 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
3236 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3237 RETURN;
3238 END;
3239 $$;
3241 COMMENT ON FUNCTION "set_snapshot_event"
3242 ( "issue"."id"%TYPE,
3243 "snapshot_event" )
3244 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
3248 ---------------------
3249 -- Freezing issues --
3250 ---------------------
3252 CREATE FUNCTION "freeze_after_snapshot"
3253 ( "issue_id_p" "issue"."id"%TYPE )
3254 RETURNS VOID
3255 LANGUAGE 'plpgsql' VOLATILE AS $$
3256 DECLARE
3257 "issue_row" "issue"%ROWTYPE;
3258 "policy_row" "policy"%ROWTYPE;
3259 "initiative_row" "initiative"%ROWTYPE;
3260 BEGIN
3261 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3262 SELECT * INTO "policy_row"
3263 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3264 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
3265 FOR "initiative_row" IN
3266 SELECT * FROM "initiative"
3267 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3268 LOOP
3269 IF
3270 "initiative_row"."satisfied_supporter_count" > 0 AND
3271 "initiative_row"."satisfied_supporter_count" *
3272 "policy_row"."initiative_quorum_den" >=
3273 "issue_row"."population" * "policy_row"."initiative_quorum_num"
3274 THEN
3275 UPDATE "initiative" SET "admitted" = TRUE
3276 WHERE "id" = "initiative_row"."id";
3277 ELSE
3278 UPDATE "initiative" SET "admitted" = FALSE
3279 WHERE "id" = "initiative_row"."id";
3280 END IF;
3281 END LOOP;
3282 IF EXISTS (
3283 SELECT NULL FROM "initiative"
3284 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
3285 ) THEN
3286 UPDATE "issue" SET
3287 "state" = 'voting',
3288 "accepted" = coalesce("accepted", now()),
3289 "half_frozen" = coalesce("half_frozen", now()),
3290 "fully_frozen" = now()
3291 WHERE "id" = "issue_id_p";
3292 ELSE
3293 UPDATE "issue" SET
3294 "state" = 'canceled_no_initiative_admitted',
3295 "accepted" = coalesce("accepted", now()),
3296 "half_frozen" = coalesce("half_frozen", now()),
3297 "fully_frozen" = now(),
3298 "closed" = now(),
3299 "ranks_available" = TRUE
3300 WHERE "id" = "issue_id_p";
3301 -- NOTE: The following DELETE statements have effect only when
3302 -- issue state has been manipulated
3303 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
3304 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
3305 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3306 END IF;
3307 RETURN;
3308 END;
3309 $$;
3311 COMMENT ON FUNCTION "freeze_after_snapshot"
3312 ( "issue"."id"%TYPE )
3313 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
3316 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
3317 RETURNS VOID
3318 LANGUAGE 'plpgsql' VOLATILE AS $$
3319 DECLARE
3320 "issue_row" "issue"%ROWTYPE;
3321 BEGIN
3322 PERFORM "create_snapshot"("issue_id_p");
3323 PERFORM "freeze_after_snapshot"("issue_id_p");
3324 RETURN;
3325 END;
3326 $$;
3328 COMMENT ON FUNCTION "manual_freeze"
3329 ( "issue"."id"%TYPE )
3330 IS 'Freeze an issue manually (fully) and start voting';
3334 -----------------------
3335 -- Counting of votes --
3336 -----------------------
3339 CREATE FUNCTION "weight_of_added_vote_delegations"
3340 ( "issue_id_p" "issue"."id"%TYPE,
3341 "member_id_p" "member"."id"%TYPE,
3342 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
3343 RETURNS "direct_voter"."weight"%TYPE
3344 LANGUAGE 'plpgsql' VOLATILE AS $$
3345 DECLARE
3346 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3347 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
3348 "weight_v" INT4;
3349 "sub_weight_v" INT4;
3350 BEGIN
3351 "weight_v" := 0;
3352 FOR "issue_delegation_row" IN
3353 SELECT * FROM "issue_delegation"
3354 WHERE "trustee_id" = "member_id_p"
3355 AND "issue_id" = "issue_id_p"
3356 LOOP
3357 IF NOT EXISTS (
3358 SELECT NULL FROM "direct_voter"
3359 WHERE "member_id" = "issue_delegation_row"."truster_id"
3360 AND "issue_id" = "issue_id_p"
3361 ) AND NOT EXISTS (
3362 SELECT NULL FROM "delegating_voter"
3363 WHERE "member_id" = "issue_delegation_row"."truster_id"
3364 AND "issue_id" = "issue_id_p"
3365 ) THEN
3366 "delegate_member_ids_v" :=
3367 "member_id_p" || "delegate_member_ids_p";
3368 INSERT INTO "delegating_voter" (
3369 "issue_id",
3370 "member_id",
3371 "scope",
3372 "delegate_member_ids"
3373 ) VALUES (
3374 "issue_id_p",
3375 "issue_delegation_row"."truster_id",
3376 "issue_delegation_row"."scope",
3377 "delegate_member_ids_v"
3378 );
3379 "sub_weight_v" := 1 +
3380 "weight_of_added_vote_delegations"(
3381 "issue_id_p",
3382 "issue_delegation_row"."truster_id",
3383 "delegate_member_ids_v"
3384 );
3385 UPDATE "delegating_voter"
3386 SET "weight" = "sub_weight_v"
3387 WHERE "issue_id" = "issue_id_p"
3388 AND "member_id" = "issue_delegation_row"."truster_id";
3389 "weight_v" := "weight_v" + "sub_weight_v";
3390 END IF;
3391 END LOOP;
3392 RETURN "weight_v";
3393 END;
3394 $$;
3396 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
3397 ( "issue"."id"%TYPE,
3398 "member"."id"%TYPE,
3399 "delegating_voter"."delegate_member_ids"%TYPE )
3400 IS 'Helper function for "add_vote_delegations" function';
3403 CREATE FUNCTION "add_vote_delegations"
3404 ( "issue_id_p" "issue"."id"%TYPE )
3405 RETURNS VOID
3406 LANGUAGE 'plpgsql' VOLATILE AS $$
3407 DECLARE
3408 "member_id_v" "member"."id"%TYPE;
3409 BEGIN
3410 FOR "member_id_v" IN
3411 SELECT "member_id" FROM "direct_voter"
3412 WHERE "issue_id" = "issue_id_p"
3413 LOOP
3414 UPDATE "direct_voter" SET
3415 "weight" = "weight" + "weight_of_added_vote_delegations"(
3416 "issue_id_p",
3417 "member_id_v",
3418 '{}'
3419 )
3420 WHERE "member_id" = "member_id_v"
3421 AND "issue_id" = "issue_id_p";
3422 END LOOP;
3423 RETURN;
3424 END;
3425 $$;
3427 COMMENT ON FUNCTION "add_vote_delegations"
3428 ( "issue_id_p" "issue"."id"%TYPE )
3429 IS 'Helper function for "close_voting" function';
3432 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
3433 RETURNS VOID
3434 LANGUAGE 'plpgsql' VOLATILE AS $$
3435 DECLARE
3436 "area_id_v" "area"."id"%TYPE;
3437 "unit_id_v" "unit"."id"%TYPE;
3438 "member_id_v" "member"."id"%TYPE;
3439 BEGIN
3440 PERFORM "lock_issue"("issue_id_p");
3441 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
3442 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
3443 -- delete delegating votes (in cases of manual reset of issue state):
3444 DELETE FROM "delegating_voter"
3445 WHERE "issue_id" = "issue_id_p";
3446 -- delete votes from non-privileged voters:
3447 DELETE FROM "direct_voter"
3448 USING (
3449 SELECT
3450 "direct_voter"."member_id"
3451 FROM "direct_voter"
3452 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
3453 LEFT JOIN "privilege"
3454 ON "privilege"."unit_id" = "unit_id_v"
3455 AND "privilege"."member_id" = "direct_voter"."member_id"
3456 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
3457 "member"."active" = FALSE OR
3458 "privilege"."voting_right" ISNULL OR
3459 "privilege"."voting_right" = FALSE
3460 )
3461 ) AS "subquery"
3462 WHERE "direct_voter"."issue_id" = "issue_id_p"
3463 AND "direct_voter"."member_id" = "subquery"."member_id";
3464 -- consider delegations:
3465 UPDATE "direct_voter" SET "weight" = 1
3466 WHERE "issue_id" = "issue_id_p";
3467 PERFORM "add_vote_delegations"("issue_id_p");
3468 -- set voter count and mark issue as being calculated:
3469 UPDATE "issue" SET
3470 "state" = 'calculation',
3471 "closed" = now(),
3472 "voter_count" = (
3473 SELECT coalesce(sum("weight"), 0)
3474 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
3475 )
3476 WHERE "id" = "issue_id_p";
3477 -- materialize battle_view:
3478 -- NOTE: "closed" column of issue must be set at this point
3479 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3480 INSERT INTO "battle" (
3481 "issue_id",
3482 "winning_initiative_id", "losing_initiative_id",
3483 "count"
3484 ) SELECT
3485 "issue_id",
3486 "winning_initiative_id", "losing_initiative_id",
3487 "count"
3488 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
3489 -- copy "positive_votes" and "negative_votes" from "battle" table:
3490 UPDATE "initiative" SET
3491 "positive_votes" = "battle_win"."count",
3492 "negative_votes" = "battle_lose"."count"
3493 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
3494 WHERE
3495 "battle_win"."issue_id" = "issue_id_p" AND
3496 "battle_win"."winning_initiative_id" = "initiative"."id" AND
3497 "battle_win"."losing_initiative_id" ISNULL AND
3498 "battle_lose"."issue_id" = "issue_id_p" AND
3499 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
3500 "battle_lose"."winning_initiative_id" ISNULL;
3501 END;
3502 $$;
3504 COMMENT ON FUNCTION "close_voting"
3505 ( "issue"."id"%TYPE )
3506 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.';
3509 CREATE FUNCTION "defeat_strength"
3510 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
3511 RETURNS INT8
3512 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3513 BEGIN
3514 IF "positive_votes_p" > "negative_votes_p" THEN
3515 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
3516 ELSIF "positive_votes_p" = "negative_votes_p" THEN
3517 RETURN 0;
3518 ELSE
3519 RETURN -1;
3520 END IF;
3521 END;
3522 $$;
3524 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';
3527 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
3528 RETURNS VOID
3529 LANGUAGE 'plpgsql' VOLATILE AS $$
3530 DECLARE
3531 "issue_row" "issue"%ROWTYPE;
3532 "policy_row" "policy"%ROWTYPE;
3533 "dimension_v" INTEGER;
3534 "vote_matrix" INT4[][]; -- absolute votes
3535 "matrix" INT8[][]; -- defeat strength / best paths
3536 "i" INTEGER;
3537 "j" INTEGER;
3538 "k" INTEGER;
3539 "battle_row" "battle"%ROWTYPE;
3540 "rank_ary" INT4[];
3541 "rank_v" INT4;
3542 "done_v" INTEGER;
3543 "winners_ary" INTEGER[];
3544 "initiative_id_v" "initiative"."id"%TYPE;
3545 BEGIN
3546 SELECT * INTO "issue_row"
3547 FROM "issue" WHERE "id" = "issue_id_p"
3548 FOR UPDATE;
3549 SELECT * INTO "policy_row"
3550 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3551 SELECT count(1) INTO "dimension_v"
3552 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
3553 -- Create "vote_matrix" with absolute number of votes in pairwise
3554 -- comparison:
3555 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
3556 "i" := 1;
3557 "j" := 2;
3558 FOR "battle_row" IN
3559 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
3560 ORDER BY
3561 "winning_initiative_id" NULLS LAST,
3562 "losing_initiative_id" NULLS LAST
3563 LOOP
3564 "vote_matrix"["i"]["j"] := "battle_row"."count";
3565 IF "j" = "dimension_v" THEN
3566 "i" := "i" + 1;
3567 "j" := 1;
3568 ELSE
3569 "j" := "j" + 1;
3570 IF "j" = "i" THEN
3571 "j" := "j" + 1;
3572 END IF;
3573 END IF;
3574 END LOOP;
3575 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
3576 RAISE EXCEPTION 'Wrong battle count (should not happen)';
3577 END IF;
3578 -- Store defeat strengths in "matrix" using "defeat_strength"
3579 -- function:
3580 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
3581 "i" := 1;
3582 LOOP
3583 "j" := 1;
3584 LOOP
3585 IF "i" != "j" THEN
3586 "matrix"["i"]["j"] := "defeat_strength"(
3587 "vote_matrix"["i"]["j"],
3588 "vote_matrix"["j"]["i"]
3589 );
3590 END IF;
3591 EXIT WHEN "j" = "dimension_v";
3592 "j" := "j" + 1;
3593 END LOOP;
3594 EXIT WHEN "i" = "dimension_v";
3595 "i" := "i" + 1;
3596 END LOOP;
3597 -- Find best paths:
3598 "i" := 1;
3599 LOOP
3600 "j" := 1;
3601 LOOP
3602 IF "i" != "j" THEN
3603 "k" := 1;
3604 LOOP
3605 IF "i" != "k" AND "j" != "k" THEN
3606 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
3607 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
3608 "matrix"["j"]["k"] := "matrix"["j"]["i"];
3609 END IF;
3610 ELSE
3611 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
3612 "matrix"["j"]["k"] := "matrix"["i"]["k"];
3613 END IF;
3614 END IF;
3615 END IF;
3616 EXIT WHEN "k" = "dimension_v";
3617 "k" := "k" + 1;
3618 END LOOP;
3619 END IF;
3620 EXIT WHEN "j" = "dimension_v";
3621 "j" := "j" + 1;
3622 END LOOP;
3623 EXIT WHEN "i" = "dimension_v";
3624 "i" := "i" + 1;
3625 END LOOP;
3626 -- Determine order of winners:
3627 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
3628 "rank_v" := 1;
3629 "done_v" := 0;
3630 LOOP
3631 "winners_ary" := '{}';
3632 "i" := 1;
3633 LOOP
3634 IF "rank_ary"["i"] ISNULL THEN
3635 "j" := 1;
3636 LOOP
3637 IF
3638 "i" != "j" AND
3639 "rank_ary"["j"] ISNULL AND
3640 "matrix"["j"]["i"] > "matrix"["i"]["j"]
3641 THEN
3642 -- someone else is better
3643 EXIT;
3644 END IF;
3645 IF "j" = "dimension_v" THEN
3646 -- noone is better
3647 "winners_ary" := "winners_ary" || "i";
3648 EXIT;
3649 END IF;
3650 "j" := "j" + 1;
3651 END LOOP;
3652 END IF;
3653 EXIT WHEN "i" = "dimension_v";
3654 "i" := "i" + 1;
3655 END LOOP;
3656 "i" := 1;
3657 LOOP
3658 "rank_ary"["winners_ary"["i"]] := "rank_v";
3659 "done_v" := "done_v" + 1;
3660 EXIT WHEN "i" = array_upper("winners_ary", 1);
3661 "i" := "i" + 1;
3662 END LOOP;
3663 EXIT WHEN "done_v" = "dimension_v";
3664 "rank_v" := "rank_v" + 1;
3665 END LOOP;
3666 -- write preliminary results:
3667 "i" := 1;
3668 FOR "initiative_id_v" IN
3669 SELECT "id" FROM "initiative"
3670 WHERE "issue_id" = "issue_id_p" AND "admitted"
3671 ORDER BY "id"
3672 LOOP
3673 UPDATE "initiative" SET
3674 "direct_majority" =
3675 CASE WHEN "policy_row"."direct_majority_strict" THEN
3676 "positive_votes" * "policy_row"."direct_majority_den" >
3677 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
3678 ELSE
3679 "positive_votes" * "policy_row"."direct_majority_den" >=
3680 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
3681 END
3682 AND "positive_votes" >= "policy_row"."direct_majority_positive"
3683 AND "issue_row"."voter_count"-"negative_votes" >=
3684 "policy_row"."direct_majority_non_negative",
3685 "indirect_majority" =
3686 CASE WHEN "policy_row"."indirect_majority_strict" THEN
3687 "positive_votes" * "policy_row"."indirect_majority_den" >
3688 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
3689 ELSE
3690 "positive_votes" * "policy_row"."indirect_majority_den" >=
3691 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
3692 END
3693 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
3694 AND "issue_row"."voter_count"-"negative_votes" >=
3695 "policy_row"."indirect_majority_non_negative",
3696 "schulze_rank" = "rank_ary"["i"],
3697 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
3698 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
3699 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
3700 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0,
3701 "winner" = FALSE
3702 WHERE "id" = "initiative_id_v";
3703 "i" := "i" + 1;
3704 END LOOP;
3705 IF "i" != "dimension_v" THEN
3706 RAISE EXCEPTION 'Wrong winner count (should not happen)';
3707 END IF;
3708 -- take indirect majorities into account:
3709 LOOP
3710 UPDATE "initiative" SET "indirect_majority" = TRUE
3711 FROM (
3712 SELECT "new_initiative"."id" AS "initiative_id"
3713 FROM "initiative" "old_initiative"
3714 JOIN "initiative" "new_initiative"
3715 ON "new_initiative"."issue_id" = "issue_id_p"
3716 AND "new_initiative"."indirect_majority" = FALSE
3717 JOIN "battle" "battle_win"
3718 ON "battle_win"."issue_id" = "issue_id_p"
3719 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
3720 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
3721 JOIN "battle" "battle_lose"
3722 ON "battle_lose"."issue_id" = "issue_id_p"
3723 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
3724 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
3725 WHERE "old_initiative"."issue_id" = "issue_id_p"
3726 AND "old_initiative"."indirect_majority" = TRUE
3727 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
3728 "battle_win"."count" * "policy_row"."indirect_majority_den" >
3729 "policy_row"."indirect_majority_num" *
3730 ("battle_win"."count"+"battle_lose"."count")
3731 ELSE
3732 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
3733 "policy_row"."indirect_majority_num" *
3734 ("battle_win"."count"+"battle_lose"."count")
3735 END
3736 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
3737 AND "issue_row"."voter_count"-"battle_lose"."count" >=
3738 "policy_row"."indirect_majority_non_negative"
3739 ) AS "subquery"
3740 WHERE "id" = "subquery"."initiative_id";
3741 EXIT WHEN NOT FOUND;
3742 END LOOP;
3743 -- set "multistage_majority" for remaining matching initiatives:
3744 UPDATE "initiative" SET "multistage_majority" = TRUE
3745 FROM (
3746 SELECT "losing_initiative"."id" AS "initiative_id"
3747 FROM "initiative" "losing_initiative"
3748 JOIN "initiative" "winning_initiative"
3749 ON "winning_initiative"."issue_id" = "issue_id_p"
3750 AND "winning_initiative"."admitted"
3751 JOIN "battle" "battle_win"
3752 ON "battle_win"."issue_id" = "issue_id_p"
3753 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
3754 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
3755 JOIN "battle" "battle_lose"
3756 ON "battle_lose"."issue_id" = "issue_id_p"
3757 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
3758 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
3759 WHERE "losing_initiative"."issue_id" = "issue_id_p"
3760 AND "losing_initiative"."admitted"
3761 AND "winning_initiative"."schulze_rank" <
3762 "losing_initiative"."schulze_rank"
3763 AND "battle_win"."count" > "battle_lose"."count"
3764 AND (
3765 "battle_win"."count" > "winning_initiative"."positive_votes" OR
3766 "battle_lose"."count" < "losing_initiative"."negative_votes" )
3767 ) AS "subquery"
3768 WHERE "id" = "subquery"."initiative_id";
3769 -- mark eligible initiatives:
3770 UPDATE "initiative" SET "eligible" = TRUE
3771 WHERE "issue_id" = "issue_id_p"
3772 AND "initiative"."direct_majority"
3773 AND "initiative"."indirect_majority"
3774 AND "initiative"."better_than_status_quo"
3775 AND (
3776 "policy_row"."no_multistage_majority" = FALSE OR
3777 "initiative"."multistage_majority" = FALSE )
3778 AND (
3779 "policy_row"."no_reverse_beat_path" = FALSE OR
3780 "initiative"."reverse_beat_path" = FALSE );
3781 -- mark final winner:
3782 UPDATE "initiative" SET "winner" = TRUE
3783 FROM (
3784 SELECT "id" AS "initiative_id"
3785 FROM "initiative"
3786 WHERE "issue_id" = "issue_id_p" AND "eligible"
3787 ORDER BY "schulze_rank", "id"
3788 LIMIT 1
3789 ) AS "subquery"
3790 WHERE "id" = "subquery"."initiative_id";
3791 -- write (final) ranks:
3792 "rank_v" := 1;
3793 FOR "initiative_id_v" IN
3794 SELECT "id"
3795 FROM "initiative"
3796 WHERE "issue_id" = "issue_id_p" AND "admitted"
3797 ORDER BY
3798 "winner" DESC,
3799 ("direct_majority" AND "indirect_majority") DESC,
3800 "schulze_rank",
3801 "id"
3802 LOOP
3803 UPDATE "initiative" SET "rank" = "rank_v"
3804 WHERE "id" = "initiative_id_v";
3805 "rank_v" := "rank_v" + 1;
3806 END LOOP;
3807 -- set schulze rank of status quo and mark issue as finished:
3808 UPDATE "issue" SET
3809 "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
3810 "state" =
3811 CASE WHEN EXISTS (
3812 SELECT NULL FROM "initiative"
3813 WHERE "issue_id" = "issue_id_p" AND "winner"
3814 ) THEN
3815 'finished_with_winner'::"issue_state"
3816 ELSE
3817 'finished_without_winner'::"issue_state"
3818 END,
3819 "ranks_available" = TRUE
3820 WHERE "id" = "issue_id_p";
3821 RETURN;
3822 END;
3823 $$;
3825 COMMENT ON FUNCTION "calculate_ranks"
3826 ( "issue"."id"%TYPE )
3827 IS 'Determine ranking (Votes have to be counted first)';
3831 -----------------------------
3832 -- Automatic state changes --
3833 -----------------------------
3836 CREATE FUNCTION "check_issue"
3837 ( "issue_id_p" "issue"."id"%TYPE )
3838 RETURNS VOID
3839 LANGUAGE 'plpgsql' VOLATILE AS $$
3840 DECLARE
3841 "issue_row" "issue"%ROWTYPE;
3842 "policy_row" "policy"%ROWTYPE;
3843 BEGIN
3844 PERFORM "lock_issue"("issue_id_p");
3845 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3846 -- only process open issues:
3847 IF "issue_row"."closed" ISNULL THEN
3848 SELECT * INTO "policy_row" FROM "policy"
3849 WHERE "id" = "issue_row"."policy_id";
3850 -- create a snapshot, unless issue is already fully frozen:
3851 IF "issue_row"."fully_frozen" ISNULL THEN
3852 PERFORM "create_snapshot"("issue_id_p");
3853 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3854 END IF;
3855 -- eventually close or accept issues, which have not been accepted:
3856 IF "issue_row"."accepted" ISNULL THEN
3857 IF EXISTS (
3858 SELECT NULL FROM "initiative"
3859 WHERE "issue_id" = "issue_id_p"
3860 AND "supporter_count" > 0
3861 AND "supporter_count" * "policy_row"."issue_quorum_den"
3862 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
3863 ) THEN
3864 -- accept issues, if supporter count is high enough
3865 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3866 -- NOTE: "issue_row" used later
3867 "issue_row"."state" := 'discussion';
3868 "issue_row"."accepted" := now();
3869 UPDATE "issue" SET
3870 "state" = "issue_row"."state",
3871 "accepted" = "issue_row"."accepted"
3872 WHERE "id" = "issue_row"."id";
3873 ELSIF
3874 now() >= "issue_row"."created" + "issue_row"."admission_time"
3875 THEN
3876 -- close issues, if admission time has expired
3877 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3878 UPDATE "issue" SET
3879 "state" = 'canceled_issue_not_accepted',
3880 "closed" = now()
3881 WHERE "id" = "issue_row"."id";
3882 END IF;
3883 END IF;
3884 -- eventually half freeze issues:
3885 IF
3886 -- NOTE: issue can't be closed at this point, if it has been accepted
3887 "issue_row"."accepted" NOTNULL AND
3888 "issue_row"."half_frozen" ISNULL
3889 THEN
3890 IF
3891 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
3892 THEN
3893 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
3894 -- NOTE: "issue_row" used later
3895 "issue_row"."state" := 'verification';
3896 "issue_row"."half_frozen" := now();
3897 UPDATE "issue" SET
3898 "state" = "issue_row"."state",
3899 "half_frozen" = "issue_row"."half_frozen"
3900 WHERE "id" = "issue_row"."id";
3901 END IF;
3902 END IF;
3903 -- close issues after some time, if all initiatives have been revoked:
3904 IF
3905 "issue_row"."closed" ISNULL AND
3906 NOT EXISTS (
3907 -- all initiatives are revoked
3908 SELECT NULL FROM "initiative"
3909 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3910 ) AND (
3911 -- and issue has not been accepted yet
3912 "issue_row"."accepted" ISNULL OR
3913 NOT EXISTS (
3914 -- or no initiatives have been revoked lately
3915 SELECT NULL FROM "initiative"
3916 WHERE "issue_id" = "issue_id_p"
3917 AND now() < "revoked" + "issue_row"."verification_time"
3918 ) OR (
3919 -- or verification time has elapsed
3920 "issue_row"."half_frozen" NOTNULL AND
3921 "issue_row"."fully_frozen" ISNULL AND
3922 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3923 )
3924 )
3925 THEN
3926 -- NOTE: "issue_row" used later
3927 IF "issue_row"."accepted" ISNULL THEN
3928 "issue_row"."state" := 'canceled_revoked_before_accepted';
3929 ELSIF "issue_row"."half_frozen" ISNULL THEN
3930 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
3931 ELSE
3932 "issue_row"."state" := 'canceled_after_revocation_during_verification';
3933 END IF;
3934 "issue_row"."closed" := now();
3935 UPDATE "issue" SET
3936 "state" = "issue_row"."state",
3937 "closed" = "issue_row"."closed"
3938 WHERE "id" = "issue_row"."id";
3939 END IF;
3940 -- fully freeze issue after verification time:
3941 IF
3942 "issue_row"."half_frozen" NOTNULL AND
3943 "issue_row"."fully_frozen" ISNULL AND
3944 "issue_row"."closed" ISNULL AND
3945 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3946 THEN
3947 PERFORM "freeze_after_snapshot"("issue_id_p");
3948 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
3949 END IF;
3950 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3951 -- close issue by calling close_voting(...) after voting time:
3952 IF
3953 "issue_row"."closed" ISNULL AND
3954 "issue_row"."fully_frozen" NOTNULL AND
3955 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
3956 THEN
3957 PERFORM "close_voting"("issue_id_p");
3958 -- calculate ranks will not consume much time and can be done now
3959 PERFORM "calculate_ranks"("issue_id_p");
3960 END IF;
3961 END IF;
3962 RETURN;
3963 END;
3964 $$;
3966 COMMENT ON FUNCTION "check_issue"
3967 ( "issue"."id"%TYPE )
3968 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.';
3971 CREATE FUNCTION "check_everything"()
3972 RETURNS VOID
3973 LANGUAGE 'plpgsql' VOLATILE AS $$
3974 DECLARE
3975 "issue_id_v" "issue"."id"%TYPE;
3976 BEGIN
3977 PERFORM "check_activity"();
3978 PERFORM "calculate_member_counts"();
3979 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
3980 PERFORM "check_issue"("issue_id_v");
3981 END LOOP;
3982 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
3983 PERFORM "calculate_ranks"("issue_id_v");
3984 END LOOP;
3985 RETURN;
3986 END;
3987 $$;
3989 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.';
3993 ----------------------
3994 -- Deletion of data --
3995 ----------------------
3998 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
3999 RETURNS VOID
4000 LANGUAGE 'plpgsql' VOLATILE AS $$
4001 DECLARE
4002 "issue_row" "issue"%ROWTYPE;
4003 BEGIN
4004 SELECT * INTO "issue_row"
4005 FROM "issue" WHERE "id" = "issue_id_p"
4006 FOR UPDATE;
4007 IF "issue_row"."cleaned" ISNULL THEN
4008 UPDATE "issue" SET
4009 "state" = 'voting',
4010 "closed" = NULL,
4011 "ranks_available" = FALSE
4012 WHERE "id" = "issue_id_p";
4013 DELETE FROM "issue_comment"
4014 WHERE "issue_id" = "issue_id_p";
4015 DELETE FROM "voting_comment"
4016 WHERE "issue_id" = "issue_id_p";
4017 DELETE FROM "delegating_voter"
4018 WHERE "issue_id" = "issue_id_p";
4019 DELETE FROM "direct_voter"
4020 WHERE "issue_id" = "issue_id_p";
4021 DELETE FROM "delegating_interest_snapshot"
4022 WHERE "issue_id" = "issue_id_p";
4023 DELETE FROM "direct_interest_snapshot"
4024 WHERE "issue_id" = "issue_id_p";
4025 DELETE FROM "delegating_population_snapshot"
4026 WHERE "issue_id" = "issue_id_p";
4027 DELETE FROM "direct_population_snapshot"
4028 WHERE "issue_id" = "issue_id_p";
4029 DELETE FROM "non_voter"
4030 WHERE "issue_id" = "issue_id_p";
4031 DELETE FROM "delegation"
4032 WHERE "issue_id" = "issue_id_p";
4033 DELETE FROM "supporter"
4034 WHERE "issue_id" = "issue_id_p";
4035 UPDATE "issue" SET
4036 "state" = "issue_row"."state",
4037 "closed" = "issue_row"."closed",
4038 "ranks_available" = "issue_row"."ranks_available",
4039 "cleaned" = now()
4040 WHERE "id" = "issue_id_p";
4041 END IF;
4042 RETURN;
4043 END;
4044 $$;
4046 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
4049 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
4050 RETURNS VOID
4051 LANGUAGE 'plpgsql' VOLATILE AS $$
4052 BEGIN
4053 UPDATE "member" SET
4054 "last_login" = NULL,
4055 "login" = NULL,
4056 "password" = NULL,
4057 "locked" = TRUE,
4058 "active" = FALSE,
4059 "notify_email" = NULL,
4060 "notify_email_unconfirmed" = NULL,
4061 "notify_email_secret" = NULL,
4062 "notify_email_secret_expiry" = NULL,
4063 "notify_email_lock_expiry" = NULL,
4064 "password_reset_secret" = NULL,
4065 "password_reset_secret_expiry" = NULL,
4066 "organizational_unit" = NULL,
4067 "internal_posts" = NULL,
4068 "realname" = NULL,
4069 "birthday" = NULL,
4070 "address" = NULL,
4071 "email" = NULL,
4072 "xmpp_address" = NULL,
4073 "website" = NULL,
4074 "phone" = NULL,
4075 "mobile_phone" = NULL,
4076 "profession" = NULL,
4077 "external_memberships" = NULL,
4078 "external_posts" = NULL,
4079 "statement" = NULL
4080 WHERE "id" = "member_id_p";
4081 -- "text_search_data" is updated by triggers
4082 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
4083 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
4084 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
4085 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
4086 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
4087 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
4088 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
4089 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
4090 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
4091 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
4092 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
4093 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
4094 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
4095 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
4096 DELETE FROM "direct_voter" USING "issue"
4097 WHERE "direct_voter"."issue_id" = "issue"."id"
4098 AND "issue"."closed" ISNULL
4099 AND "member_id" = "member_id_p";
4100 RETURN;
4101 END;
4102 $$;
4104 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)';
4107 CREATE FUNCTION "delete_private_data"()
4108 RETURNS VOID
4109 LANGUAGE 'plpgsql' VOLATILE AS $$
4110 BEGIN
4111 UPDATE "member" SET
4112 "invite_code" = NULL,
4113 "last_login" = NULL,
4114 "login" = NULL,
4115 "password" = NULL,
4116 "notify_email" = NULL,
4117 "notify_email_unconfirmed" = NULL,
4118 "notify_email_secret" = NULL,
4119 "notify_email_secret_expiry" = NULL,
4120 "notify_email_lock_expiry" = NULL,
4121 "password_reset_secret" = NULL,
4122 "password_reset_secret_expiry" = NULL,
4123 "organizational_unit" = NULL,
4124 "internal_posts" = NULL,
4125 "realname" = NULL,
4126 "birthday" = NULL,
4127 "address" = NULL,
4128 "email" = NULL,
4129 "xmpp_address" = NULL,
4130 "website" = NULL,
4131 "phone" = NULL,
4132 "mobile_phone" = NULL,
4133 "profession" = NULL,
4134 "external_memberships" = NULL,
4135 "external_posts" = NULL,
4136 "statement" = NULL;
4137 -- "text_search_data" is updated by triggers
4138 DELETE FROM "setting";
4139 DELETE FROM "setting_map";
4140 DELETE FROM "member_relation_setting";
4141 DELETE FROM "member_image";
4142 DELETE FROM "contact";
4143 DELETE FROM "ignored_member";
4144 DELETE FROM "area_setting";
4145 DELETE FROM "issue_setting";
4146 DELETE FROM "ignored_initiative";
4147 DELETE FROM "initiative_setting";
4148 DELETE FROM "suggestion_setting";
4149 DELETE FROM "non_voter";
4150 DELETE FROM "direct_voter" USING "issue"
4151 WHERE "direct_voter"."issue_id" = "issue"."id"
4152 AND "issue"."closed" ISNULL;
4153 RETURN;
4154 END;
4155 $$;
4157 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.';
4161 COMMIT;