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