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