liquid_feedback_core
view core.sql @ 231:61e20e161e9b
Bugfix in function "write_event_initiative_revoked_trigger": Set "draft_id" in "event" table
| author | jbe |
|---|---|
| date | Sat Mar 10 18:04:03 2012 +0100 (2012-03-10) |
| parents | 592c6a236523 |
| children | cf8a090503c0 |
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" ) AND
1195 OLD."activated" NOTNULL
1196 THEN
1197 INSERT INTO "member_history"
1198 ("member_id", "active", "name")
1199 VALUES (NEW."id", OLD."active", OLD."name");
1200 END IF;
1201 RETURN NULL;
1202 END;
1203 $$;
1205 CREATE TRIGGER "write_member_history"
1206 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1207 "write_member_history_trigger"();
1209 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
1210 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
1213 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
1214 RETURNS TRIGGER
1215 LANGUAGE 'plpgsql' VOLATILE AS $$
1216 BEGIN
1217 IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN
1218 INSERT INTO "event" ("event", "issue_id", "state")
1219 VALUES ('issue_state_changed', NEW."id", NEW."state");
1220 END IF;
1221 RETURN NULL;
1222 END;
1223 $$;
1225 CREATE TRIGGER "write_event_issue_state_changed"
1226 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
1227 "write_event_issue_state_changed_trigger"();
1229 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
1230 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
1233 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1234 RETURNS TRIGGER
1235 LANGUAGE 'plpgsql' VOLATILE AS $$
1236 DECLARE
1237 "initiative_row" "initiative"%ROWTYPE;
1238 "issue_row" "issue"%ROWTYPE;
1239 "event_v" "event_type";
1240 BEGIN
1241 SELECT * INTO "initiative_row" FROM "initiative"
1242 WHERE "id" = NEW."initiative_id";
1243 SELECT * INTO "issue_row" FROM "issue"
1244 WHERE "id" = "initiative_row"."issue_id";
1245 IF EXISTS (
1246 SELECT NULL FROM "draft"
1247 WHERE "initiative_id" = NEW."initiative_id"
1248 AND "id" != NEW."id"
1249 ) THEN
1250 "event_v" := 'new_draft_created';
1251 ELSE
1252 IF EXISTS (
1253 SELECT NULL FROM "initiative"
1254 WHERE "issue_id" = "initiative_row"."issue_id"
1255 AND "id" != "initiative_row"."id"
1256 ) THEN
1257 "event_v" := 'initiative_created_in_existing_issue';
1258 ELSE
1259 "event_v" := 'initiative_created_in_new_issue';
1260 END IF;
1261 END IF;
1262 INSERT INTO "event" (
1263 "event", "member_id",
1264 "issue_id", "state", "initiative_id", "draft_id"
1265 ) VALUES (
1266 "event_v",
1267 NEW."author_id",
1268 "initiative_row"."issue_id",
1269 "issue_row"."state",
1270 "initiative_row"."id",
1271 NEW."id" );
1272 RETURN NULL;
1273 END;
1274 $$;
1276 CREATE TRIGGER "write_event_initiative_or_draft_created"
1277 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
1278 "write_event_initiative_or_draft_created_trigger"();
1280 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
1281 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
1284 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
1285 RETURNS TRIGGER
1286 LANGUAGE 'plpgsql' VOLATILE AS $$
1287 DECLARE
1288 "issue_row" "issue"%ROWTYPE;
1289 "draft_id_v" "draft"."id"%TYPE;
1290 BEGIN
1291 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1292 SELECT * INTO "issue_row" FROM "issue"
1293 WHERE "id" = NEW."issue_id";
1294 SELECT "id" INTO "draft_id_v" FROM "current_draft"
1295 WHERE "initiative_id" = NEW."id";
1296 INSERT INTO "event" (
1297 "event", "member_id", "issue_id", "state", "initiative_id", "draft_id"
1298 ) VALUES (
1299 'initiative_revoked',
1300 NEW."revoked_by_member_id",
1301 NEW."issue_id",
1302 "issue_row"."state",
1303 NEW."id",
1304 "draft_id_v");
1305 END IF;
1306 RETURN NULL;
1307 END;
1308 $$;
1310 CREATE TRIGGER "write_event_initiative_revoked"
1311 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
1312 "write_event_initiative_revoked_trigger"();
1314 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
1315 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
1318 CREATE FUNCTION "write_event_suggestion_created_trigger"()
1319 RETURNS TRIGGER
1320 LANGUAGE 'plpgsql' VOLATILE AS $$
1321 DECLARE
1322 "initiative_row" "initiative"%ROWTYPE;
1323 "issue_row" "issue"%ROWTYPE;
1324 BEGIN
1325 SELECT * INTO "initiative_row" FROM "initiative"
1326 WHERE "id" = NEW."initiative_id";
1327 SELECT * INTO "issue_row" FROM "issue"
1328 WHERE "id" = "initiative_row"."issue_id";
1329 INSERT INTO "event" (
1330 "event", "member_id",
1331 "issue_id", "state", "initiative_id", "suggestion_id"
1332 ) VALUES (
1333 'suggestion_created',
1334 NEW."author_id",
1335 "initiative_row"."issue_id",
1336 "issue_row"."state",
1337 "initiative_row"."id",
1338 NEW."id" );
1339 RETURN NULL;
1340 END;
1341 $$;
1343 CREATE TRIGGER "write_event_suggestion_created"
1344 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1345 "write_event_suggestion_created_trigger"();
1347 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
1348 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1352 ----------------------------
1353 -- Additional constraints --
1354 ----------------------------
1357 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
1358 RETURNS TRIGGER
1359 LANGUAGE 'plpgsql' VOLATILE AS $$
1360 BEGIN
1361 IF NOT EXISTS (
1362 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
1363 ) THEN
1364 --RAISE 'Cannot create issue without an initial initiative.' USING
1365 -- ERRCODE = 'integrity_constraint_violation',
1366 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
1367 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
1368 END IF;
1369 RETURN NULL;
1370 END;
1371 $$;
1373 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
1374 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
1375 FOR EACH ROW EXECUTE PROCEDURE
1376 "issue_requires_first_initiative_trigger"();
1378 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
1379 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
1382 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
1383 RETURNS TRIGGER
1384 LANGUAGE 'plpgsql' VOLATILE AS $$
1385 DECLARE
1386 "reference_lost" BOOLEAN;
1387 BEGIN
1388 IF TG_OP = 'DELETE' THEN
1389 "reference_lost" := TRUE;
1390 ELSE
1391 "reference_lost" := NEW."issue_id" != OLD."issue_id";
1392 END IF;
1393 IF
1394 "reference_lost" AND NOT EXISTS (
1395 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
1396 )
1397 THEN
1398 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
1399 END IF;
1400 RETURN NULL;
1401 END;
1402 $$;
1404 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
1405 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1406 FOR EACH ROW EXECUTE PROCEDURE
1407 "last_initiative_deletes_issue_trigger"();
1409 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
1410 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
1413 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
1414 RETURNS TRIGGER
1415 LANGUAGE 'plpgsql' VOLATILE AS $$
1416 BEGIN
1417 IF NOT EXISTS (
1418 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
1419 ) THEN
1420 --RAISE 'Cannot create initiative without an initial draft.' USING
1421 -- ERRCODE = 'integrity_constraint_violation',
1422 -- HINT = 'Create issue, initiative and draft within the same transaction.';
1423 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
1424 END IF;
1425 RETURN NULL;
1426 END;
1427 $$;
1429 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
1430 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1431 FOR EACH ROW EXECUTE PROCEDURE
1432 "initiative_requires_first_draft_trigger"();
1434 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
1435 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
1438 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
1439 RETURNS TRIGGER
1440 LANGUAGE 'plpgsql' VOLATILE AS $$
1441 DECLARE
1442 "reference_lost" BOOLEAN;
1443 BEGIN
1444 IF TG_OP = 'DELETE' THEN
1445 "reference_lost" := TRUE;
1446 ELSE
1447 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
1448 END IF;
1449 IF
1450 "reference_lost" AND NOT EXISTS (
1451 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
1452 )
1453 THEN
1454 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
1455 END IF;
1456 RETURN NULL;
1457 END;
1458 $$;
1460 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
1461 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
1462 FOR EACH ROW EXECUTE PROCEDURE
1463 "last_draft_deletes_initiative_trigger"();
1465 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
1466 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
1469 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
1470 RETURNS TRIGGER
1471 LANGUAGE 'plpgsql' VOLATILE AS $$
1472 BEGIN
1473 IF NOT EXISTS (
1474 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
1475 ) THEN
1476 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
1477 END IF;
1478 RETURN NULL;
1479 END;
1480 $$;
1482 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
1483 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
1484 FOR EACH ROW EXECUTE PROCEDURE
1485 "suggestion_requires_first_opinion_trigger"();
1487 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1488 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1491 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1492 RETURNS TRIGGER
1493 LANGUAGE 'plpgsql' VOLATILE AS $$
1494 DECLARE
1495 "reference_lost" BOOLEAN;
1496 BEGIN
1497 IF TG_OP = 'DELETE' THEN
1498 "reference_lost" := TRUE;
1499 ELSE
1500 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1501 END IF;
1502 IF
1503 "reference_lost" AND NOT EXISTS (
1504 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1505 )
1506 THEN
1507 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1508 END IF;
1509 RETURN NULL;
1510 END;
1511 $$;
1513 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1514 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1515 FOR EACH ROW EXECUTE PROCEDURE
1516 "last_opinion_deletes_suggestion_trigger"();
1518 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1519 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1523 ---------------------------------------------------------------
1524 -- Ensure that votes are not modified when issues are frozen --
1525 ---------------------------------------------------------------
1527 -- NOTE: Frontends should ensure this anyway, but in case of programming
1528 -- errors the following triggers ensure data integrity.
1531 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1532 RETURNS TRIGGER
1533 LANGUAGE 'plpgsql' VOLATILE AS $$
1534 DECLARE
1535 "issue_id_v" "issue"."id"%TYPE;
1536 "issue_row" "issue"%ROWTYPE;
1537 BEGIN
1538 IF TG_OP = 'DELETE' THEN
1539 "issue_id_v" := OLD."issue_id";
1540 ELSE
1541 "issue_id_v" := NEW."issue_id";
1542 END IF;
1543 SELECT INTO "issue_row" * FROM "issue"
1544 WHERE "id" = "issue_id_v" FOR SHARE;
1545 IF "issue_row"."closed" NOTNULL THEN
1546 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
1547 END IF;
1548 RETURN NULL;
1549 END;
1550 $$;
1552 CREATE TRIGGER "forbid_changes_on_closed_issue"
1553 AFTER INSERT OR UPDATE OR DELETE ON "direct_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 "delegating_voter"
1559 FOR EACH ROW EXECUTE PROCEDURE
1560 "forbid_changes_on_closed_issue_trigger"();
1562 CREATE TRIGGER "forbid_changes_on_closed_issue"
1563 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1564 FOR EACH ROW EXECUTE PROCEDURE
1565 "forbid_changes_on_closed_issue_trigger"();
1567 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"';
1568 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';
1569 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';
1570 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';
1574 --------------------------------------------------------------------
1575 -- Auto-retrieval of fields only needed for referential integrity --
1576 --------------------------------------------------------------------
1579 CREATE FUNCTION "autofill_issue_id_trigger"()
1580 RETURNS TRIGGER
1581 LANGUAGE 'plpgsql' VOLATILE AS $$
1582 BEGIN
1583 IF NEW."issue_id" ISNULL THEN
1584 SELECT "issue_id" INTO NEW."issue_id"
1585 FROM "initiative" WHERE "id" = NEW."initiative_id";
1586 END IF;
1587 RETURN NEW;
1588 END;
1589 $$;
1591 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1592 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1594 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1595 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1597 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1598 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1599 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1602 CREATE FUNCTION "autofill_initiative_id_trigger"()
1603 RETURNS TRIGGER
1604 LANGUAGE 'plpgsql' VOLATILE AS $$
1605 BEGIN
1606 IF NEW."initiative_id" ISNULL THEN
1607 SELECT "initiative_id" INTO NEW."initiative_id"
1608 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1609 END IF;
1610 RETURN NEW;
1611 END;
1612 $$;
1614 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1615 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1617 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1618 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1622 -----------------------------------------------------
1623 -- Automatic calculation of certain default values --
1624 -----------------------------------------------------
1627 CREATE FUNCTION "copy_timings_trigger"()
1628 RETURNS TRIGGER
1629 LANGUAGE 'plpgsql' VOLATILE AS $$
1630 DECLARE
1631 "policy_row" "policy"%ROWTYPE;
1632 BEGIN
1633 SELECT * INTO "policy_row" FROM "policy"
1634 WHERE "id" = NEW."policy_id";
1635 IF NEW."admission_time" ISNULL THEN
1636 NEW."admission_time" := "policy_row"."admission_time";
1637 END IF;
1638 IF NEW."discussion_time" ISNULL THEN
1639 NEW."discussion_time" := "policy_row"."discussion_time";
1640 END IF;
1641 IF NEW."verification_time" ISNULL THEN
1642 NEW."verification_time" := "policy_row"."verification_time";
1643 END IF;
1644 IF NEW."voting_time" ISNULL THEN
1645 NEW."voting_time" := "policy_row"."voting_time";
1646 END IF;
1647 RETURN NEW;
1648 END;
1649 $$;
1651 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1652 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1654 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1655 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1658 CREATE FUNCTION "default_for_draft_id_trigger"()
1659 RETURNS TRIGGER
1660 LANGUAGE 'plpgsql' VOLATILE AS $$
1661 BEGIN
1662 IF NEW."draft_id" ISNULL THEN
1663 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1664 WHERE "initiative_id" = NEW."initiative_id";
1665 END IF;
1666 RETURN NEW;
1667 END;
1668 $$;
1670 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"
1671 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1672 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1673 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1675 COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';
1676 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';
1677 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';
1681 ----------------------------------------
1682 -- Automatic creation of dependencies --
1683 ----------------------------------------
1686 CREATE FUNCTION "autocreate_interest_trigger"()
1687 RETURNS TRIGGER
1688 LANGUAGE 'plpgsql' VOLATILE AS $$
1689 BEGIN
1690 IF NOT EXISTS (
1691 SELECT NULL FROM "initiative" JOIN "interest"
1692 ON "initiative"."issue_id" = "interest"."issue_id"
1693 WHERE "initiative"."id" = NEW."initiative_id"
1694 AND "interest"."member_id" = NEW."member_id"
1695 ) THEN
1696 BEGIN
1697 INSERT INTO "interest" ("issue_id", "member_id")
1698 SELECT "issue_id", NEW."member_id"
1699 FROM "initiative" WHERE "id" = NEW."initiative_id";
1700 EXCEPTION WHEN unique_violation THEN END;
1701 END IF;
1702 RETURN NEW;
1703 END;
1704 $$;
1706 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1707 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1709 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1710 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';
1713 CREATE FUNCTION "autocreate_supporter_trigger"()
1714 RETURNS TRIGGER
1715 LANGUAGE 'plpgsql' VOLATILE AS $$
1716 BEGIN
1717 IF NOT EXISTS (
1718 SELECT NULL FROM "suggestion" JOIN "supporter"
1719 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1720 WHERE "suggestion"."id" = NEW."suggestion_id"
1721 AND "supporter"."member_id" = NEW."member_id"
1722 ) THEN
1723 BEGIN
1724 INSERT INTO "supporter" ("initiative_id", "member_id")
1725 SELECT "initiative_id", NEW."member_id"
1726 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1727 EXCEPTION WHEN unique_violation THEN END;
1728 END IF;
1729 RETURN NEW;
1730 END;
1731 $$;
1733 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1734 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1736 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1737 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.';
1741 ------------------------------------------
1742 -- Views and helper functions for views --
1743 ------------------------------------------
1746 CREATE VIEW "unit_delegation" AS
1747 SELECT
1748 "unit"."id" AS "unit_id",
1749 "delegation"."id",
1750 "delegation"."truster_id",
1751 "delegation"."trustee_id",
1752 "delegation"."scope"
1753 FROM "unit"
1754 JOIN "delegation"
1755 ON "delegation"."unit_id" = "unit"."id"
1756 JOIN "member"
1757 ON "delegation"."truster_id" = "member"."id"
1758 JOIN "privilege"
1759 ON "delegation"."unit_id" = "privilege"."unit_id"
1760 AND "delegation"."truster_id" = "privilege"."member_id"
1761 WHERE "member"."active" AND "privilege"."voting_right";
1763 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
1766 CREATE VIEW "area_delegation" AS
1767 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1768 "area"."id" AS "area_id",
1769 "delegation"."id",
1770 "delegation"."truster_id",
1771 "delegation"."trustee_id",
1772 "delegation"."scope"
1773 FROM "area"
1774 JOIN "delegation"
1775 ON "delegation"."unit_id" = "area"."unit_id"
1776 OR "delegation"."area_id" = "area"."id"
1777 JOIN "member"
1778 ON "delegation"."truster_id" = "member"."id"
1779 JOIN "privilege"
1780 ON "area"."unit_id" = "privilege"."unit_id"
1781 AND "delegation"."truster_id" = "privilege"."member_id"
1782 WHERE "member"."active" AND "privilege"."voting_right"
1783 ORDER BY
1784 "area"."id",
1785 "delegation"."truster_id",
1786 "delegation"."scope" DESC;
1788 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
1791 CREATE VIEW "issue_delegation" AS
1792 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1793 "issue"."id" AS "issue_id",
1794 "delegation"."id",
1795 "delegation"."truster_id",
1796 "delegation"."trustee_id",
1797 "delegation"."scope"
1798 FROM "issue"
1799 JOIN "area"
1800 ON "area"."id" = "issue"."area_id"
1801 JOIN "delegation"
1802 ON "delegation"."unit_id" = "area"."unit_id"
1803 OR "delegation"."area_id" = "area"."id"
1804 OR "delegation"."issue_id" = "issue"."id"
1805 JOIN "member"
1806 ON "delegation"."truster_id" = "member"."id"
1807 JOIN "privilege"
1808 ON "area"."unit_id" = "privilege"."unit_id"
1809 AND "delegation"."truster_id" = "privilege"."member_id"
1810 WHERE "member"."active" AND "privilege"."voting_right"
1811 ORDER BY
1812 "issue"."id",
1813 "delegation"."truster_id",
1814 "delegation"."scope" DESC;
1816 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
1819 CREATE FUNCTION "membership_weight_with_skipping"
1820 ( "area_id_p" "area"."id"%TYPE,
1821 "member_id_p" "member"."id"%TYPE,
1822 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1823 RETURNS INT4
1824 LANGUAGE 'plpgsql' STABLE AS $$
1825 DECLARE
1826 "sum_v" INT4;
1827 "delegation_row" "area_delegation"%ROWTYPE;
1828 BEGIN
1829 "sum_v" := 1;
1830 FOR "delegation_row" IN
1831 SELECT "area_delegation".*
1832 FROM "area_delegation" LEFT JOIN "membership"
1833 ON "membership"."area_id" = "area_id_p"
1834 AND "membership"."member_id" = "area_delegation"."truster_id"
1835 WHERE "area_delegation"."area_id" = "area_id_p"
1836 AND "area_delegation"."trustee_id" = "member_id_p"
1837 AND "membership"."member_id" ISNULL
1838 LOOP
1839 IF NOT
1840 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1841 THEN
1842 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1843 "area_id_p",
1844 "delegation_row"."truster_id",
1845 "skip_member_ids_p" || "delegation_row"."truster_id"
1846 );
1847 END IF;
1848 END LOOP;
1849 RETURN "sum_v";
1850 END;
1851 $$;
1853 COMMENT ON FUNCTION "membership_weight_with_skipping"
1854 ( "area"."id"%TYPE,
1855 "member"."id"%TYPE,
1856 INT4[] )
1857 IS 'Helper function for "membership_weight" function';
1860 CREATE FUNCTION "membership_weight"
1861 ( "area_id_p" "area"."id"%TYPE,
1862 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1863 RETURNS INT4
1864 LANGUAGE 'plpgsql' STABLE AS $$
1865 BEGIN
1866 RETURN "membership_weight_with_skipping"(
1867 "area_id_p",
1868 "member_id_p",
1869 ARRAY["member_id_p"]
1870 );
1871 END;
1872 $$;
1874 COMMENT ON FUNCTION "membership_weight"
1875 ( "area"."id"%TYPE,
1876 "member"."id"%TYPE )
1877 IS 'Calculates the potential voting weight of a member in a given area';
1880 CREATE VIEW "member_count_view" AS
1881 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1883 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1886 CREATE VIEW "unit_member_count" AS
1887 SELECT
1888 "unit"."id" AS "unit_id",
1889 sum("member"."id") AS "member_count"
1890 FROM "unit"
1891 LEFT JOIN "privilege"
1892 ON "privilege"."unit_id" = "unit"."id"
1893 AND "privilege"."voting_right"
1894 LEFT JOIN "member"
1895 ON "member"."id" = "privilege"."member_id"
1896 AND "member"."active"
1897 GROUP BY "unit"."id";
1899 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
1902 CREATE VIEW "area_member_count" AS
1903 SELECT
1904 "area"."id" AS "area_id",
1905 count("member"."id") AS "direct_member_count",
1906 coalesce(
1907 sum(
1908 CASE WHEN "member"."id" NOTNULL THEN
1909 "membership_weight"("area"."id", "member"."id")
1910 ELSE 0 END
1911 )
1912 ) AS "member_weight"
1913 FROM "area"
1914 LEFT JOIN "membership"
1915 ON "area"."id" = "membership"."area_id"
1916 LEFT JOIN "privilege"
1917 ON "privilege"."unit_id" = "area"."unit_id"
1918 AND "privilege"."member_id" = "membership"."member_id"
1919 AND "privilege"."voting_right"
1920 LEFT JOIN "member"
1921 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
1922 AND "member"."active"
1923 GROUP BY "area"."id";
1925 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"';
1928 CREATE VIEW "opening_draft" AS
1929 SELECT "draft".* FROM (
1930 SELECT
1931 "initiative"."id" AS "initiative_id",
1932 min("draft"."id") AS "draft_id"
1933 FROM "initiative" JOIN "draft"
1934 ON "initiative"."id" = "draft"."initiative_id"
1935 GROUP BY "initiative"."id"
1936 ) AS "subquery"
1937 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1939 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1942 CREATE VIEW "current_draft" AS
1943 SELECT "draft".* FROM (
1944 SELECT
1945 "initiative"."id" AS "initiative_id",
1946 max("draft"."id") AS "draft_id"
1947 FROM "initiative" JOIN "draft"
1948 ON "initiative"."id" = "draft"."initiative_id"
1949 GROUP BY "initiative"."id"
1950 ) AS "subquery"
1951 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1953 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1956 CREATE VIEW "critical_opinion" AS
1957 SELECT * FROM "opinion"
1958 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
1959 OR ("degree" = -2 AND "fulfilled" = TRUE);
1961 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
1964 CREATE VIEW "battle_participant" AS
1965 SELECT "initiative"."id", "initiative"."issue_id"
1966 FROM "issue" JOIN "initiative"
1967 ON "issue"."id" = "initiative"."issue_id"
1968 WHERE "initiative"."admitted"
1969 UNION ALL
1970 SELECT NULL, "id" AS "issue_id"
1971 FROM "issue";
1973 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
1976 CREATE VIEW "battle_view" AS
1977 SELECT
1978 "issue"."id" AS "issue_id",
1979 "winning_initiative"."id" AS "winning_initiative_id",
1980 "losing_initiative"."id" AS "losing_initiative_id",
1981 sum(
1982 CASE WHEN
1983 coalesce("better_vote"."grade", 0) >
1984 coalesce("worse_vote"."grade", 0)
1985 THEN "direct_voter"."weight" ELSE 0 END
1986 ) AS "count"
1987 FROM "issue"
1988 LEFT JOIN "direct_voter"
1989 ON "issue"."id" = "direct_voter"."issue_id"
1990 JOIN "battle_participant" AS "winning_initiative"
1991 ON "issue"."id" = "winning_initiative"."issue_id"
1992 JOIN "battle_participant" AS "losing_initiative"
1993 ON "issue"."id" = "losing_initiative"."issue_id"
1994 LEFT JOIN "vote" AS "better_vote"
1995 ON "direct_voter"."member_id" = "better_vote"."member_id"
1996 AND "winning_initiative"."id" = "better_vote"."initiative_id"
1997 LEFT JOIN "vote" AS "worse_vote"
1998 ON "direct_voter"."member_id" = "worse_vote"."member_id"
1999 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
2000 WHERE "issue"."closed" NOTNULL
2001 AND "issue"."cleaned" ISNULL
2002 AND (
2003 "winning_initiative"."id" != "losing_initiative"."id" OR
2004 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
2005 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
2006 GROUP BY
2007 "issue"."id",
2008 "winning_initiative"."id",
2009 "losing_initiative"."id";
2011 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';
2014 CREATE VIEW "open_issue" AS
2015 SELECT * FROM "issue" WHERE "closed" ISNULL;
2017 COMMENT ON VIEW "open_issue" IS 'All open issues';
2020 CREATE VIEW "issue_with_ranks_missing" AS
2021 SELECT * FROM "issue"
2022 WHERE "fully_frozen" NOTNULL
2023 AND "closed" NOTNULL
2024 AND "ranks_available" = FALSE;
2026 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
2029 CREATE VIEW "member_contingent" AS
2030 SELECT
2031 "member"."id" AS "member_id",
2032 "contingent"."time_frame",
2033 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
2034 (
2035 SELECT count(1) FROM "draft"
2036 WHERE "draft"."author_id" = "member"."id"
2037 AND "draft"."created" > now() - "contingent"."time_frame"
2038 ) + (
2039 SELECT count(1) FROM "suggestion"
2040 WHERE "suggestion"."author_id" = "member"."id"
2041 AND "suggestion"."created" > now() - "contingent"."time_frame"
2042 )
2043 ELSE NULL END AS "text_entry_count",
2044 "contingent"."text_entry_limit",
2045 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
2046 SELECT count(1) FROM "opening_draft"
2047 WHERE "opening_draft"."author_id" = "member"."id"
2048 AND "opening_draft"."created" > now() - "contingent"."time_frame"
2049 ) ELSE NULL END AS "initiative_count",
2050 "contingent"."initiative_limit"
2051 FROM "member" CROSS JOIN "contingent";
2053 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
2055 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
2056 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
2059 CREATE VIEW "member_contingent_left" AS
2060 SELECT
2061 "member_id",
2062 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
2063 max("initiative_limit" - "initiative_count") AS "initiatives_left"
2064 FROM "member_contingent" GROUP BY "member_id";
2066 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.';
2069 CREATE VIEW "event_seen_by_member" AS
2070 SELECT
2071 "member"."id" AS "seen_by_member_id",
2072 CASE WHEN "event"."state" IN (
2073 'voting',
2074 'finished_without_winner',
2075 'finished_with_winner'
2076 ) THEN
2077 'voting'::"notify_level"
2078 ELSE
2079 CASE WHEN "event"."state" IN (
2080 'verification',
2081 'canceled_after_revocation_during_verification',
2082 'canceled_no_initiative_admitted'
2083 ) THEN
2084 'verification'::"notify_level"
2085 ELSE
2086 CASE WHEN "event"."state" IN (
2087 'discussion',
2088 'canceled_after_revocation_during_discussion'
2089 ) THEN
2090 'discussion'::"notify_level"
2091 ELSE
2092 'all'::"notify_level"
2093 END
2094 END
2095 END AS "notify_level",
2096 "event".*
2097 FROM "member" CROSS JOIN "event"
2098 LEFT JOIN "issue"
2099 ON "event"."issue_id" = "issue"."id"
2100 LEFT JOIN "membership"
2101 ON "member"."id" = "membership"."member_id"
2102 AND "issue"."area_id" = "membership"."area_id"
2103 LEFT JOIN "interest"
2104 ON "member"."id" = "interest"."member_id"
2105 AND "event"."issue_id" = "interest"."issue_id"
2106 LEFT JOIN "supporter"
2107 ON "member"."id" = "supporter"."member_id"
2108 AND "event"."initiative_id" = "supporter"."initiative_id"
2109 LEFT JOIN "ignored_member"
2110 ON "member"."id" = "ignored_member"."member_id"
2111 AND "event"."member_id" = "ignored_member"."other_member_id"
2112 LEFT JOIN "ignored_initiative"
2113 ON "member"."id" = "ignored_initiative"."member_id"
2114 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2115 WHERE (
2116 "supporter"."member_id" NOTNULL OR
2117 "interest"."member_id" NOTNULL OR
2118 ( "membership"."member_id" NOTNULL AND
2119 "event"."event" IN (
2120 'issue_state_changed',
2121 'initiative_created_in_new_issue',
2122 'initiative_created_in_existing_issue',
2123 'initiative_revoked' ) ) )
2124 AND "ignored_member"."member_id" ISNULL
2125 AND "ignored_initiative"."member_id" ISNULL;
2127 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"';
2130 CREATE VIEW "selected_event_seen_by_member" AS
2131 SELECT
2132 "member"."id" AS "seen_by_member_id",
2133 CASE WHEN "event"."state" IN (
2134 'voting',
2135 'finished_without_winner',
2136 'finished_with_winner'
2137 ) THEN
2138 'voting'::"notify_level"
2139 ELSE
2140 CASE WHEN "event"."state" IN (
2141 'verification',
2142 'canceled_after_revocation_during_verification',
2143 'canceled_no_initiative_admitted'
2144 ) THEN
2145 'verification'::"notify_level"
2146 ELSE
2147 CASE WHEN "event"."state" IN (
2148 'discussion',
2149 'canceled_after_revocation_during_discussion'
2150 ) THEN
2151 'discussion'::"notify_level"
2152 ELSE
2153 'all'::"notify_level"
2154 END
2155 END
2156 END AS "notify_level",
2157 "event".*
2158 FROM "member" CROSS JOIN "event"
2159 LEFT JOIN "issue"
2160 ON "event"."issue_id" = "issue"."id"
2161 LEFT JOIN "membership"
2162 ON "member"."id" = "membership"."member_id"
2163 AND "issue"."area_id" = "membership"."area_id"
2164 LEFT JOIN "interest"
2165 ON "member"."id" = "interest"."member_id"
2166 AND "event"."issue_id" = "interest"."issue_id"
2167 LEFT JOIN "supporter"
2168 ON "member"."id" = "supporter"."member_id"
2169 AND "event"."initiative_id" = "supporter"."initiative_id"
2170 LEFT JOIN "ignored_member"
2171 ON "member"."id" = "ignored_member"."member_id"
2172 AND "event"."member_id" = "ignored_member"."other_member_id"
2173 LEFT JOIN "ignored_initiative"
2174 ON "member"."id" = "ignored_initiative"."member_id"
2175 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2176 WHERE (
2177 ( "member"."notify_level" >= 'all' ) OR
2178 ( "member"."notify_level" >= 'voting' AND
2179 "event"."state" IN (
2180 'voting',
2181 'finished_without_winner',
2182 'finished_with_winner' ) ) OR
2183 ( "member"."notify_level" >= 'verification' AND
2184 "event"."state" IN (
2185 'verification',
2186 'canceled_after_revocation_during_verification',
2187 'canceled_no_initiative_admitted' ) ) OR
2188 ( "member"."notify_level" >= 'discussion' AND
2189 "event"."state" IN (
2190 'discussion',
2191 'canceled_after_revocation_during_discussion' ) ) )
2192 AND (
2193 "supporter"."member_id" NOTNULL OR
2194 "interest"."member_id" NOTNULL OR
2195 ( "membership"."member_id" NOTNULL AND
2196 "event"."event" IN (
2197 'issue_state_changed',
2198 'initiative_created_in_new_issue',
2199 'initiative_created_in_existing_issue',
2200 'initiative_revoked' ) ) )
2201 AND "ignored_member"."member_id" ISNULL
2202 AND "ignored_initiative"."member_id" ISNULL;
2204 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"';
2207 CREATE TYPE "timeline_event" AS ENUM (
2208 'issue_created',
2209 'issue_canceled',
2210 'issue_accepted',
2211 'issue_half_frozen',
2212 'issue_finished_without_voting',
2213 'issue_voting_started',
2214 'issue_finished_after_voting',
2215 'initiative_created',
2216 'initiative_revoked',
2217 'draft_created',
2218 'suggestion_created');
2220 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)';
2223 CREATE VIEW "timeline_issue" AS
2224 SELECT
2225 "created" AS "occurrence",
2226 'issue_created'::"timeline_event" AS "event",
2227 "id" AS "issue_id"
2228 FROM "issue"
2229 UNION ALL
2230 SELECT
2231 "closed" AS "occurrence",
2232 'issue_canceled'::"timeline_event" AS "event",
2233 "id" AS "issue_id"
2234 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
2235 UNION ALL
2236 SELECT
2237 "accepted" AS "occurrence",
2238 'issue_accepted'::"timeline_event" AS "event",
2239 "id" AS "issue_id"
2240 FROM "issue" WHERE "accepted" NOTNULL
2241 UNION ALL
2242 SELECT
2243 "half_frozen" AS "occurrence",
2244 'issue_half_frozen'::"timeline_event" AS "event",
2245 "id" AS "issue_id"
2246 FROM "issue" WHERE "half_frozen" NOTNULL
2247 UNION ALL
2248 SELECT
2249 "fully_frozen" AS "occurrence",
2250 'issue_voting_started'::"timeline_event" AS "event",
2251 "id" AS "issue_id"
2252 FROM "issue"
2253 WHERE "fully_frozen" NOTNULL
2254 AND ("closed" ISNULL OR "closed" != "fully_frozen")
2255 UNION ALL
2256 SELECT
2257 "closed" AS "occurrence",
2258 CASE WHEN "fully_frozen" = "closed" THEN
2259 'issue_finished_without_voting'::"timeline_event"
2260 ELSE
2261 'issue_finished_after_voting'::"timeline_event"
2262 END AS "event",
2263 "id" AS "issue_id"
2264 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
2266 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)';
2269 CREATE VIEW "timeline_initiative" AS
2270 SELECT
2271 "created" AS "occurrence",
2272 'initiative_created'::"timeline_event" AS "event",
2273 "id" AS "initiative_id"
2274 FROM "initiative"
2275 UNION ALL
2276 SELECT
2277 "revoked" AS "occurrence",
2278 'initiative_revoked'::"timeline_event" AS "event",
2279 "id" AS "initiative_id"
2280 FROM "initiative" WHERE "revoked" NOTNULL;
2282 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)';
2285 CREATE VIEW "timeline_draft" AS
2286 SELECT
2287 "created" AS "occurrence",
2288 'draft_created'::"timeline_event" AS "event",
2289 "id" AS "draft_id"
2290 FROM "draft";
2292 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)';
2295 CREATE VIEW "timeline_suggestion" AS
2296 SELECT
2297 "created" AS "occurrence",
2298 'suggestion_created'::"timeline_event" AS "event",
2299 "id" AS "suggestion_id"
2300 FROM "suggestion";
2302 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)';
2305 CREATE VIEW "timeline" AS
2306 SELECT
2307 "occurrence",
2308 "event",
2309 "issue_id",
2310 NULL AS "initiative_id",
2311 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
2312 NULL::INT8 AS "suggestion_id"
2313 FROM "timeline_issue"
2314 UNION ALL
2315 SELECT
2316 "occurrence",
2317 "event",
2318 NULL AS "issue_id",
2319 "initiative_id",
2320 NULL AS "draft_id",
2321 NULL AS "suggestion_id"
2322 FROM "timeline_initiative"
2323 UNION ALL
2324 SELECT
2325 "occurrence",
2326 "event",
2327 NULL AS "issue_id",
2328 NULL AS "initiative_id",
2329 "draft_id",
2330 NULL AS "suggestion_id"
2331 FROM "timeline_draft"
2332 UNION ALL
2333 SELECT
2334 "occurrence",
2335 "event",
2336 NULL AS "issue_id",
2337 NULL AS "initiative_id",
2338 NULL AS "draft_id",
2339 "suggestion_id"
2340 FROM "timeline_suggestion";
2342 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)';
2346 --------------------------------------------------
2347 -- Set returning function for delegation chains --
2348 --------------------------------------------------
2351 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
2352 ('first', 'intermediate', 'last', 'repetition');
2354 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
2357 CREATE TYPE "delegation_chain_row" AS (
2358 "index" INT4,
2359 "member_id" INT4,
2360 "member_valid" BOOLEAN,
2361 "participation" BOOLEAN,
2362 "overridden" BOOLEAN,
2363 "scope_in" "delegation_scope",
2364 "scope_out" "delegation_scope",
2365 "disabled_out" BOOLEAN,
2366 "loop" "delegation_chain_loop_tag" );
2368 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
2370 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
2371 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';
2372 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
2373 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
2374 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
2375 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
2376 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
2379 CREATE FUNCTION "delegation_chain"
2380 ( "member_id_p" "member"."id"%TYPE,
2381 "unit_id_p" "unit"."id"%TYPE,
2382 "area_id_p" "area"."id"%TYPE,
2383 "issue_id_p" "issue"."id"%TYPE,
2384 "simulate_trustee_id_p" "member"."id"%TYPE )
2385 RETURNS SETOF "delegation_chain_row"
2386 LANGUAGE 'plpgsql' STABLE AS $$
2387 DECLARE
2388 "scope_v" "delegation_scope";
2389 "unit_id_v" "unit"."id"%TYPE;
2390 "area_id_v" "area"."id"%TYPE;
2391 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2392 "loop_member_id_v" "member"."id"%TYPE;
2393 "output_row" "delegation_chain_row";
2394 "output_rows" "delegation_chain_row"[];
2395 "delegation_row" "delegation"%ROWTYPE;
2396 "row_count" INT4;
2397 "i" INT4;
2398 "loop_v" BOOLEAN;
2399 BEGIN
2400 IF
2401 "unit_id_p" NOTNULL AND
2402 "area_id_p" ISNULL AND
2403 "issue_id_p" ISNULL
2404 THEN
2405 "scope_v" := 'unit';
2406 "unit_id_v" := "unit_id_p";
2407 ELSIF
2408 "unit_id_p" ISNULL AND
2409 "area_id_p" NOTNULL AND
2410 "issue_id_p" ISNULL
2411 THEN
2412 "scope_v" := 'area';
2413 "area_id_v" := "area_id_p";
2414 SELECT "unit_id" INTO "unit_id_v"
2415 FROM "area" WHERE "id" = "area_id_v";
2416 ELSIF
2417 "unit_id_p" ISNULL AND
2418 "area_id_p" ISNULL AND
2419 "issue_id_p" NOTNULL
2420 THEN
2421 "scope_v" := 'issue';
2422 SELECT "area_id" INTO "area_id_v"
2423 FROM "issue" WHERE "id" = "issue_id_p";
2424 SELECT "unit_id" INTO "unit_id_v"
2425 FROM "area" WHERE "id" = "area_id_v";
2426 ELSE
2427 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2428 END IF;
2429 "visited_member_ids" := '{}';
2430 "loop_member_id_v" := NULL;
2431 "output_rows" := '{}';
2432 "output_row"."index" := 0;
2433 "output_row"."member_id" := "member_id_p";
2434 "output_row"."member_valid" := TRUE;
2435 "output_row"."participation" := FALSE;
2436 "output_row"."overridden" := FALSE;
2437 "output_row"."disabled_out" := FALSE;
2438 "output_row"."scope_out" := NULL;
2439 LOOP
2440 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2441 "loop_member_id_v" := "output_row"."member_id";
2442 ELSE
2443 "visited_member_ids" :=
2444 "visited_member_ids" || "output_row"."member_id";
2445 END IF;
2446 IF "output_row"."participation" THEN
2447 "output_row"."overridden" := TRUE;
2448 END IF;
2449 "output_row"."scope_in" := "output_row"."scope_out";
2450 IF EXISTS (
2451 SELECT NULL FROM "member" JOIN "privilege"
2452 ON "privilege"."member_id" = "member"."id"
2453 AND "privilege"."unit_id" = "unit_id_v"
2454 WHERE "id" = "output_row"."member_id"
2455 AND "member"."active" AND "privilege"."voting_right"
2456 ) THEN
2457 IF "scope_v" = 'unit' THEN
2458 SELECT * INTO "delegation_row" FROM "delegation"
2459 WHERE "truster_id" = "output_row"."member_id"
2460 AND "unit_id" = "unit_id_v";
2461 ELSIF "scope_v" = 'area' THEN
2462 "output_row"."participation" := EXISTS (
2463 SELECT NULL FROM "membership"
2464 WHERE "area_id" = "area_id_p"
2465 AND "member_id" = "output_row"."member_id"
2466 );
2467 SELECT * INTO "delegation_row" FROM "delegation"
2468 WHERE "truster_id" = "output_row"."member_id"
2469 AND (
2470 "unit_id" = "unit_id_v" OR
2471 "area_id" = "area_id_v"
2472 )
2473 ORDER BY "scope" DESC;
2474 ELSIF "scope_v" = 'issue' THEN
2475 "output_row"."participation" := EXISTS (
2476 SELECT NULL FROM "interest"
2477 WHERE "issue_id" = "issue_id_p"
2478 AND "member_id" = "output_row"."member_id"
2479 );
2480 SELECT * INTO "delegation_row" FROM "delegation"
2481 WHERE "truster_id" = "output_row"."member_id"
2482 AND (
2483 "unit_id" = "unit_id_v" OR
2484 "area_id" = "area_id_v" OR
2485 "issue_id" = "issue_id_p"
2486 )
2487 ORDER BY "scope" DESC;
2488 END IF;
2489 ELSE
2490 "output_row"."member_valid" := FALSE;
2491 "output_row"."participation" := FALSE;
2492 "output_row"."scope_out" := NULL;
2493 "delegation_row" := ROW(NULL);
2494 END IF;
2495 IF
2496 "output_row"."member_id" = "member_id_p" AND
2497 "simulate_trustee_id_p" NOTNULL
2498 THEN
2499 "output_row"."scope_out" := "scope_v";
2500 "output_rows" := "output_rows" || "output_row";
2501 "output_row"."member_id" := "simulate_trustee_id_p";
2502 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2503 "output_row"."scope_out" := "delegation_row"."scope";
2504 "output_rows" := "output_rows" || "output_row";
2505 "output_row"."member_id" := "delegation_row"."trustee_id";
2506 ELSIF "delegation_row"."scope" NOTNULL THEN
2507 "output_row"."scope_out" := "delegation_row"."scope";
2508 "output_row"."disabled_out" := TRUE;
2509 "output_rows" := "output_rows" || "output_row";
2510 EXIT;
2511 ELSE
2512 "output_row"."scope_out" := NULL;
2513 "output_rows" := "output_rows" || "output_row";
2514 EXIT;
2515 END IF;
2516 EXIT WHEN "loop_member_id_v" NOTNULL;
2517 "output_row"."index" := "output_row"."index" + 1;
2518 END LOOP;
2519 "row_count" := array_upper("output_rows", 1);
2520 "i" := 1;
2521 "loop_v" := FALSE;
2522 LOOP
2523 "output_row" := "output_rows"["i"];
2524 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2525 IF "loop_v" THEN
2526 IF "i" + 1 = "row_count" THEN
2527 "output_row"."loop" := 'last';
2528 ELSIF "i" = "row_count" THEN
2529 "output_row"."loop" := 'repetition';
2530 ELSE
2531 "output_row"."loop" := 'intermediate';
2532 END IF;
2533 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2534 "output_row"."loop" := 'first';
2535 "loop_v" := TRUE;
2536 END IF;
2537 IF "scope_v" = 'unit' THEN
2538 "output_row"."participation" := NULL;
2539 END IF;
2540 RETURN NEXT "output_row";
2541 "i" := "i" + 1;
2542 END LOOP;
2543 RETURN;
2544 END;
2545 $$;
2547 COMMENT ON FUNCTION "delegation_chain"
2548 ( "member"."id"%TYPE,
2549 "unit"."id"%TYPE,
2550 "area"."id"%TYPE,
2551 "issue"."id"%TYPE,
2552 "member"."id"%TYPE )
2553 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
2556 CREATE FUNCTION "delegation_chain"
2557 ( "member_id_p" "member"."id"%TYPE,
2558 "unit_id_p" "unit"."id"%TYPE,
2559 "area_id_p" "area"."id"%TYPE,
2560 "issue_id_p" "issue"."id"%TYPE )
2561 RETURNS SETOF "delegation_chain_row"
2562 LANGUAGE 'plpgsql' STABLE AS $$
2563 DECLARE
2564 "result_row" "delegation_chain_row";
2565 BEGIN
2566 FOR "result_row" IN
2567 SELECT * FROM "delegation_chain"(
2568 "member_id_p", "unit_id_p", "area_id_p", "issue_id_p", NULL
2569 )
2570 LOOP
2571 RETURN NEXT "result_row";
2572 END LOOP;
2573 RETURN;
2574 END;
2575 $$;
2577 COMMENT ON FUNCTION "delegation_chain"
2578 ( "member"."id"%TYPE,
2579 "unit"."id"%TYPE,
2580 "area"."id"%TYPE,
2581 "issue"."id"%TYPE )
2582 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
2586 ------------------------------
2587 -- Comparison by vote count --
2588 ------------------------------
2590 CREATE FUNCTION "vote_ratio"
2591 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
2592 "negative_votes_p" "initiative"."negative_votes"%TYPE )
2593 RETURNS FLOAT8
2594 LANGUAGE 'plpgsql' STABLE AS $$
2595 BEGIN
2596 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
2597 RETURN
2598 "positive_votes_p"::FLOAT8 /
2599 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
2600 ELSIF "positive_votes_p" > 0 THEN
2601 RETURN "positive_votes_p";
2602 ELSIF "negative_votes_p" > 0 THEN
2603 RETURN 1 - "negative_votes_p";
2604 ELSE
2605 RETURN 0.5;
2606 END IF;
2607 END;
2608 $$;
2610 COMMENT ON FUNCTION "vote_ratio"
2611 ( "initiative"."positive_votes"%TYPE,
2612 "initiative"."negative_votes"%TYPE )
2613 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.';
2617 ------------------------------------------------
2618 -- Locking for snapshots and voting procedure --
2619 ------------------------------------------------
2622 CREATE FUNCTION "share_row_lock_issue_trigger"()
2623 RETURNS TRIGGER
2624 LANGUAGE 'plpgsql' VOLATILE AS $$
2625 BEGIN
2626 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2627 PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;
2628 END IF;
2629 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2630 PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;
2631 RETURN NEW;
2632 ELSE
2633 RETURN OLD;
2634 END IF;
2635 END;
2636 $$;
2638 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';
2641 CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()
2642 RETURNS TRIGGER
2643 LANGUAGE 'plpgsql' VOLATILE AS $$
2644 BEGIN
2645 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2646 PERFORM NULL FROM "issue"
2647 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2648 WHERE "initiative"."id" = OLD."initiative_id"
2649 FOR SHARE OF "issue";
2650 END IF;
2651 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2652 PERFORM NULL FROM "issue"
2653 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2654 WHERE "initiative"."id" = NEW."initiative_id"
2655 FOR SHARE OF "issue";
2656 RETURN NEW;
2657 ELSE
2658 RETURN OLD;
2659 END IF;
2660 END;
2661 $$;
2663 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';
2666 CREATE TRIGGER "share_row_lock_issue"
2667 BEFORE INSERT OR UPDATE OR DELETE ON "initiative"
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 "interest"
2673 FOR EACH ROW EXECUTE PROCEDURE
2674 "share_row_lock_issue_trigger"();
2676 CREATE TRIGGER "share_row_lock_issue"
2677 BEFORE INSERT OR UPDATE OR DELETE ON "supporter"
2678 FOR EACH ROW EXECUTE PROCEDURE
2679 "share_row_lock_issue_trigger"();
2681 CREATE TRIGGER "share_row_lock_issue_via_initiative"
2682 BEFORE INSERT OR UPDATE OR DELETE ON "opinion"
2683 FOR EACH ROW EXECUTE PROCEDURE
2684 "share_row_lock_issue_via_initiative_trigger"();
2686 CREATE TRIGGER "share_row_lock_issue"
2687 BEFORE INSERT OR UPDATE OR DELETE ON "direct_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 "delegating_voter"
2693 FOR EACH ROW EXECUTE PROCEDURE
2694 "share_row_lock_issue_trigger"();
2696 CREATE TRIGGER "share_row_lock_issue"
2697 BEFORE INSERT OR UPDATE OR DELETE ON "vote"
2698 FOR EACH ROW EXECUTE PROCEDURE
2699 "share_row_lock_issue_trigger"();
2701 COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function';
2702 COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function';
2703 COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function';
2704 COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function';
2705 COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function';
2706 COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function';
2707 COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function';
2710 CREATE FUNCTION "lock_issue"
2711 ( "issue_id_p" "issue"."id"%TYPE )
2712 RETURNS VOID
2713 LANGUAGE 'plpgsql' VOLATILE AS $$
2714 BEGIN
2715 LOCK TABLE "member" IN SHARE MODE;
2716 LOCK TABLE "privilege" IN SHARE MODE;
2717 LOCK TABLE "membership" IN SHARE MODE;
2718 LOCK TABLE "policy" IN SHARE MODE;
2719 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2720 -- NOTE: The row-level exclusive lock in combination with the
2721 -- share_row_lock_issue(_via_initiative)_trigger functions (which
2722 -- acquire a row-level share lock on the issue) ensure that no data
2723 -- is changed, which could affect calculation of snapshots or
2724 -- counting of votes. Table "delegation" must be table-level-locked,
2725 -- as it also contains issue- and global-scope delegations.
2726 LOCK TABLE "delegation" IN SHARE MODE;
2727 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
2728 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
2729 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
2730 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
2731 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
2732 RETURN;
2733 END;
2734 $$;
2736 COMMENT ON FUNCTION "lock_issue"
2737 ( "issue"."id"%TYPE )
2738 IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';
2742 ------------------------------------------------------------------------
2743 -- Regular tasks, except calculcation of snapshots and voting results --
2744 ------------------------------------------------------------------------
2746 CREATE FUNCTION "check_activity"()
2747 RETURNS VOID
2748 LANGUAGE 'plpgsql' VOLATILE AS $$
2749 DECLARE
2750 "system_setting_row" "system_setting"%ROWTYPE;
2751 BEGIN
2752 SELECT * INTO "system_setting_row" FROM "system_setting";
2753 LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
2754 IF "system_setting_row"."member_ttl" NOTNULL THEN
2755 UPDATE "member" SET "active" = FALSE
2756 WHERE "active" = TRUE
2757 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
2758 END IF;
2759 RETURN;
2760 END;
2761 $$;
2763 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
2766 CREATE FUNCTION "calculate_member_counts"()
2767 RETURNS VOID
2768 LANGUAGE 'plpgsql' VOLATILE AS $$
2769 BEGIN
2770 LOCK TABLE "member" IN SHARE MODE;
2771 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
2772 LOCK TABLE "unit" IN EXCLUSIVE MODE;
2773 LOCK TABLE "area" IN EXCLUSIVE MODE;
2774 LOCK TABLE "privilege" IN SHARE MODE;
2775 LOCK TABLE "membership" IN SHARE MODE;
2776 DELETE FROM "member_count";
2777 INSERT INTO "member_count" ("total_count")
2778 SELECT "total_count" FROM "member_count_view";
2779 UPDATE "unit" SET "member_count" = "view"."member_count"
2780 FROM "unit_member_count" AS "view"
2781 WHERE "view"."unit_id" = "unit"."id";
2782 UPDATE "area" SET
2783 "direct_member_count" = "view"."direct_member_count",
2784 "member_weight" = "view"."member_weight"
2785 FROM "area_member_count" AS "view"
2786 WHERE "view"."area_id" = "area"."id";
2787 RETURN;
2788 END;
2789 $$;
2791 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"';
2795 ------------------------------
2796 -- Calculation of snapshots --
2797 ------------------------------
2799 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
2800 ( "issue_id_p" "issue"."id"%TYPE,
2801 "member_id_p" "member"."id"%TYPE,
2802 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2803 RETURNS "direct_population_snapshot"."weight"%TYPE
2804 LANGUAGE 'plpgsql' VOLATILE AS $$
2805 DECLARE
2806 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2807 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
2808 "weight_v" INT4;
2809 "sub_weight_v" INT4;
2810 BEGIN
2811 "weight_v" := 0;
2812 FOR "issue_delegation_row" IN
2813 SELECT * FROM "issue_delegation"
2814 WHERE "trustee_id" = "member_id_p"
2815 AND "issue_id" = "issue_id_p"
2816 LOOP
2817 IF NOT EXISTS (
2818 SELECT NULL FROM "direct_population_snapshot"
2819 WHERE "issue_id" = "issue_id_p"
2820 AND "event" = 'periodic'
2821 AND "member_id" = "issue_delegation_row"."truster_id"
2822 ) AND NOT EXISTS (
2823 SELECT NULL FROM "delegating_population_snapshot"
2824 WHERE "issue_id" = "issue_id_p"
2825 AND "event" = 'periodic'
2826 AND "member_id" = "issue_delegation_row"."truster_id"
2827 ) THEN
2828 "delegate_member_ids_v" :=
2829 "member_id_p" || "delegate_member_ids_p";
2830 INSERT INTO "delegating_population_snapshot" (
2831 "issue_id",
2832 "event",
2833 "member_id",
2834 "scope",
2835 "delegate_member_ids"
2836 ) VALUES (
2837 "issue_id_p",
2838 'periodic',
2839 "issue_delegation_row"."truster_id",
2840 "issue_delegation_row"."scope",
2841 "delegate_member_ids_v"
2842 );
2843 "sub_weight_v" := 1 +
2844 "weight_of_added_delegations_for_population_snapshot"(
2845 "issue_id_p",
2846 "issue_delegation_row"."truster_id",
2847 "delegate_member_ids_v"
2848 );
2849 UPDATE "delegating_population_snapshot"
2850 SET "weight" = "sub_weight_v"
2851 WHERE "issue_id" = "issue_id_p"
2852 AND "event" = 'periodic'
2853 AND "member_id" = "issue_delegation_row"."truster_id";
2854 "weight_v" := "weight_v" + "sub_weight_v";
2855 END IF;
2856 END LOOP;
2857 RETURN "weight_v";
2858 END;
2859 $$;
2861 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
2862 ( "issue"."id"%TYPE,
2863 "member"."id"%TYPE,
2864 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2865 IS 'Helper function for "create_population_snapshot" function';
2868 CREATE FUNCTION "create_population_snapshot"
2869 ( "issue_id_p" "issue"."id"%TYPE )
2870 RETURNS VOID
2871 LANGUAGE 'plpgsql' VOLATILE AS $$
2872 DECLARE
2873 "member_id_v" "member"."id"%TYPE;
2874 BEGIN
2875 DELETE FROM "direct_population_snapshot"
2876 WHERE "issue_id" = "issue_id_p"
2877 AND "event" = 'periodic';
2878 DELETE FROM "delegating_population_snapshot"
2879 WHERE "issue_id" = "issue_id_p"
2880 AND "event" = 'periodic';
2881 INSERT INTO "direct_population_snapshot"
2882 ("issue_id", "event", "member_id")
2883 SELECT
2884 "issue_id_p" AS "issue_id",
2885 'periodic'::"snapshot_event" AS "event",
2886 "member"."id" AS "member_id"
2887 FROM "issue"
2888 JOIN "area" ON "issue"."area_id" = "area"."id"
2889 JOIN "membership" ON "area"."id" = "membership"."area_id"
2890 JOIN "member" ON "membership"."member_id" = "member"."id"
2891 JOIN "privilege"
2892 ON "privilege"."unit_id" = "area"."unit_id"
2893 AND "privilege"."member_id" = "member"."id"
2894 WHERE "issue"."id" = "issue_id_p"
2895 AND "member"."active" AND "privilege"."voting_right"
2896 UNION
2897 SELECT
2898 "issue_id_p" AS "issue_id",
2899 'periodic'::"snapshot_event" AS "event",
2900 "member"."id" AS "member_id"
2901 FROM "issue"
2902 JOIN "area" ON "issue"."area_id" = "area"."id"
2903 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2904 JOIN "member" ON "interest"."member_id" = "member"."id"
2905 JOIN "privilege"
2906 ON "privilege"."unit_id" = "area"."unit_id"
2907 AND "privilege"."member_id" = "member"."id"
2908 WHERE "issue"."id" = "issue_id_p"
2909 AND "member"."active" AND "privilege"."voting_right";
2910 FOR "member_id_v" IN
2911 SELECT "member_id" FROM "direct_population_snapshot"
2912 WHERE "issue_id" = "issue_id_p"
2913 AND "event" = 'periodic'
2914 LOOP
2915 UPDATE "direct_population_snapshot" SET
2916 "weight" = 1 +
2917 "weight_of_added_delegations_for_population_snapshot"(
2918 "issue_id_p",
2919 "member_id_v",
2920 '{}'
2921 )
2922 WHERE "issue_id" = "issue_id_p"
2923 AND "event" = 'periodic'
2924 AND "member_id" = "member_id_v";
2925 END LOOP;
2926 RETURN;
2927 END;
2928 $$;
2930 COMMENT ON FUNCTION "create_population_snapshot"
2931 ( "issue"."id"%TYPE )
2932 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.';
2935 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2936 ( "issue_id_p" "issue"."id"%TYPE,
2937 "member_id_p" "member"."id"%TYPE,
2938 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2939 RETURNS "direct_interest_snapshot"."weight"%TYPE
2940 LANGUAGE 'plpgsql' VOLATILE AS $$
2941 DECLARE
2942 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2943 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2944 "weight_v" INT4;
2945 "sub_weight_v" INT4;
2946 BEGIN
2947 "weight_v" := 0;
2948 FOR "issue_delegation_row" IN
2949 SELECT * FROM "issue_delegation"
2950 WHERE "trustee_id" = "member_id_p"
2951 AND "issue_id" = "issue_id_p"
2952 LOOP
2953 IF NOT EXISTS (
2954 SELECT NULL FROM "direct_interest_snapshot"
2955 WHERE "issue_id" = "issue_id_p"
2956 AND "event" = 'periodic'
2957 AND "member_id" = "issue_delegation_row"."truster_id"
2958 ) AND NOT EXISTS (
2959 SELECT NULL FROM "delegating_interest_snapshot"
2960 WHERE "issue_id" = "issue_id_p"
2961 AND "event" = 'periodic'
2962 AND "member_id" = "issue_delegation_row"."truster_id"
2963 ) THEN
2964 "delegate_member_ids_v" :=
2965 "member_id_p" || "delegate_member_ids_p";
2966 INSERT INTO "delegating_interest_snapshot" (
2967 "issue_id",
2968 "event",
2969 "member_id",
2970 "scope",
2971 "delegate_member_ids"
2972 ) VALUES (
2973 "issue_id_p",
2974 'periodic',
2975 "issue_delegation_row"."truster_id",
2976 "issue_delegation_row"."scope",
2977 "delegate_member_ids_v"
2978 );
2979 "sub_weight_v" := 1 +
2980 "weight_of_added_delegations_for_interest_snapshot"(
2981 "issue_id_p",
2982 "issue_delegation_row"."truster_id",
2983 "delegate_member_ids_v"
2984 );
2985 UPDATE "delegating_interest_snapshot"
2986 SET "weight" = "sub_weight_v"
2987 WHERE "issue_id" = "issue_id_p"
2988 AND "event" = 'periodic'
2989 AND "member_id" = "issue_delegation_row"."truster_id";
2990 "weight_v" := "weight_v" + "sub_weight_v";
2991 END IF;
2992 END LOOP;
2993 RETURN "weight_v";
2994 END;
2995 $$;
2997 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2998 ( "issue"."id"%TYPE,
2999 "member"."id"%TYPE,
3000 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3001 IS 'Helper function for "create_interest_snapshot" function';
3004 CREATE FUNCTION "create_interest_snapshot"
3005 ( "issue_id_p" "issue"."id"%TYPE )
3006 RETURNS VOID
3007 LANGUAGE 'plpgsql' VOLATILE AS $$
3008 DECLARE
3009 "member_id_v" "member"."id"%TYPE;
3010 BEGIN
3011 DELETE FROM "direct_interest_snapshot"
3012 WHERE "issue_id" = "issue_id_p"
3013 AND "event" = 'periodic';
3014 DELETE FROM "delegating_interest_snapshot"
3015 WHERE "issue_id" = "issue_id_p"
3016 AND "event" = 'periodic';
3017 DELETE FROM "direct_supporter_snapshot"
3018 WHERE "issue_id" = "issue_id_p"
3019 AND "event" = 'periodic';
3020 INSERT INTO "direct_interest_snapshot"
3021 ("issue_id", "event", "member_id")
3022 SELECT
3023 "issue_id_p" AS "issue_id",
3024 'periodic' AS "event",
3025 "member"."id" AS "member_id"
3026 FROM "issue"
3027 JOIN "area" ON "issue"."area_id" = "area"."id"
3028 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3029 JOIN "member" ON "interest"."member_id" = "member"."id"
3030 JOIN "privilege"
3031 ON "privilege"."unit_id" = "area"."unit_id"
3032 AND "privilege"."member_id" = "member"."id"
3033 WHERE "issue"."id" = "issue_id_p"
3034 AND "member"."active" AND "privilege"."voting_right";
3035 FOR "member_id_v" IN
3036 SELECT "member_id" FROM "direct_interest_snapshot"
3037 WHERE "issue_id" = "issue_id_p"
3038 AND "event" = 'periodic'
3039 LOOP
3040 UPDATE "direct_interest_snapshot" SET
3041 "weight" = 1 +
3042 "weight_of_added_delegations_for_interest_snapshot"(
3043 "issue_id_p",
3044 "member_id_v",
3045 '{}'
3046 )
3047 WHERE "issue_id" = "issue_id_p"
3048 AND "event" = 'periodic'
3049 AND "member_id" = "member_id_v";
3050 END LOOP;
3051 INSERT INTO "direct_supporter_snapshot"
3052 ( "issue_id", "initiative_id", "event", "member_id",
3053 "draft_id", "informed", "satisfied" )
3054 SELECT
3055 "issue_id_p" AS "issue_id",
3056 "initiative"."id" AS "initiative_id",
3057 'periodic' AS "event",
3058 "supporter"."member_id" AS "member_id",
3059 "supporter"."draft_id" AS "draft_id",
3060 "supporter"."draft_id" = "current_draft"."id" AS "informed",
3061 NOT EXISTS (
3062 SELECT NULL FROM "critical_opinion"
3063 WHERE "initiative_id" = "initiative"."id"
3064 AND "member_id" = "supporter"."member_id"
3065 ) AS "satisfied"
3066 FROM "initiative"
3067 JOIN "supporter"
3068 ON "supporter"."initiative_id" = "initiative"."id"
3069 JOIN "current_draft"
3070 ON "initiative"."id" = "current_draft"."initiative_id"
3071 JOIN "direct_interest_snapshot"
3072 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
3073 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
3074 AND "event" = 'periodic'
3075 WHERE "initiative"."issue_id" = "issue_id_p";
3076 RETURN;
3077 END;
3078 $$;
3080 COMMENT ON FUNCTION "create_interest_snapshot"
3081 ( "issue"."id"%TYPE )
3082 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.';
3085 CREATE FUNCTION "create_snapshot"
3086 ( "issue_id_p" "issue"."id"%TYPE )
3087 RETURNS VOID
3088 LANGUAGE 'plpgsql' VOLATILE AS $$
3089 DECLARE
3090 "initiative_id_v" "initiative"."id"%TYPE;
3091 "suggestion_id_v" "suggestion"."id"%TYPE;
3092 BEGIN
3093 PERFORM "lock_issue"("issue_id_p");
3094 PERFORM "create_population_snapshot"("issue_id_p");
3095 PERFORM "create_interest_snapshot"("issue_id_p");
3096 UPDATE "issue" SET
3097 "snapshot" = now(),
3098 "latest_snapshot_event" = 'periodic',
3099 "population" = (
3100 SELECT coalesce(sum("weight"), 0)
3101 FROM "direct_population_snapshot"
3102 WHERE "issue_id" = "issue_id_p"
3103 AND "event" = 'periodic'
3104 )
3105 WHERE "id" = "issue_id_p";
3106 FOR "initiative_id_v" IN
3107 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
3108 LOOP
3109 UPDATE "initiative" SET
3110 "supporter_count" = (
3111 SELECT coalesce(sum("di"."weight"), 0)
3112 FROM "direct_interest_snapshot" AS "di"
3113 JOIN "direct_supporter_snapshot" AS "ds"
3114 ON "di"."member_id" = "ds"."member_id"
3115 WHERE "di"."issue_id" = "issue_id_p"
3116 AND "di"."event" = 'periodic'
3117 AND "ds"."initiative_id" = "initiative_id_v"
3118 AND "ds"."event" = 'periodic'
3119 ),
3120 "informed_supporter_count" = (
3121 SELECT coalesce(sum("di"."weight"), 0)
3122 FROM "direct_interest_snapshot" AS "di"
3123 JOIN "direct_supporter_snapshot" AS "ds"
3124 ON "di"."member_id" = "ds"."member_id"
3125 WHERE "di"."issue_id" = "issue_id_p"
3126 AND "di"."event" = 'periodic'
3127 AND "ds"."initiative_id" = "initiative_id_v"
3128 AND "ds"."event" = 'periodic'
3129 AND "ds"."informed"
3130 ),
3131 "satisfied_supporter_count" = (
3132 SELECT coalesce(sum("di"."weight"), 0)
3133 FROM "direct_interest_snapshot" AS "di"
3134 JOIN "direct_supporter_snapshot" AS "ds"
3135 ON "di"."member_id" = "ds"."member_id"
3136 WHERE "di"."issue_id" = "issue_id_p"
3137 AND "di"."event" = 'periodic'
3138 AND "ds"."initiative_id" = "initiative_id_v"
3139 AND "ds"."event" = 'periodic'
3140 AND "ds"."satisfied"
3141 ),
3142 "satisfied_informed_supporter_count" = (
3143 SELECT coalesce(sum("di"."weight"), 0)
3144 FROM "direct_interest_snapshot" AS "di"
3145 JOIN "direct_supporter_snapshot" AS "ds"
3146 ON "di"."member_id" = "ds"."member_id"
3147 WHERE "di"."issue_id" = "issue_id_p"
3148 AND "di"."event" = 'periodic'
3149 AND "ds"."initiative_id" = "initiative_id_v"
3150 AND "ds"."event" = 'periodic'
3151 AND "ds"."informed"
3152 AND "ds"."satisfied"
3153 )
3154 WHERE "id" = "initiative_id_v";
3155 FOR "suggestion_id_v" IN
3156 SELECT "id" FROM "suggestion"
3157 WHERE "initiative_id" = "initiative_id_v"
3158 LOOP
3159 UPDATE "suggestion" SET
3160 "minus2_unfulfilled_count" = (
3161 SELECT coalesce(sum("snapshot"."weight"), 0)
3162 FROM "issue" CROSS JOIN "opinion"
3163 JOIN "direct_interest_snapshot" AS "snapshot"
3164 ON "snapshot"."issue_id" = "issue"."id"
3165 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3166 AND "snapshot"."member_id" = "opinion"."member_id"
3167 WHERE "issue"."id" = "issue_id_p"
3168 AND "opinion"."suggestion_id" = "suggestion_id_v"
3169 AND "opinion"."degree" = -2
3170 AND "opinion"."fulfilled" = FALSE
3171 ),
3172 "minus2_fulfilled_count" = (
3173 SELECT coalesce(sum("snapshot"."weight"), 0)
3174 FROM "issue" CROSS JOIN "opinion"
3175 JOIN "direct_interest_snapshot" AS "snapshot"
3176 ON "snapshot"."issue_id" = "issue"."id"
3177 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3178 AND "snapshot"."member_id" = "opinion"."member_id"
3179 WHERE "issue"."id" = "issue_id_p"
3180 AND "opinion"."suggestion_id" = "suggestion_id_v"
3181 AND "opinion"."degree" = -2
3182 AND "opinion"."fulfilled" = TRUE
3183 ),
3184 "minus1_unfulfilled_count" = (
3185 SELECT coalesce(sum("snapshot"."weight"), 0)
3186 FROM "issue" CROSS JOIN "opinion"
3187 JOIN "direct_interest_snapshot" AS "snapshot"
3188 ON "snapshot"."issue_id" = "issue"."id"
3189 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3190 AND "snapshot"."member_id" = "opinion"."member_id"
3191 WHERE "issue"."id" = "issue_id_p"
3192 AND "opinion"."suggestion_id" = "suggestion_id_v"
3193 AND "opinion"."degree" = -1
3194 AND "opinion"."fulfilled" = FALSE
3195 ),
3196 "minus1_fulfilled_count" = (
3197 SELECT coalesce(sum("snapshot"."weight"), 0)
3198 FROM "issue" CROSS JOIN "opinion"
3199 JOIN "direct_interest_snapshot" AS "snapshot"
3200 ON "snapshot"."issue_id" = "issue"."id"
3201 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3202 AND "snapshot"."member_id" = "opinion"."member_id"
3203 WHERE "issue"."id" = "issue_id_p"
3204 AND "opinion"."suggestion_id" = "suggestion_id_v"
3205 AND "opinion"."degree" = -1
3206 AND "opinion"."fulfilled" = TRUE
3207 ),
3208 "plus1_unfulfilled_count" = (
3209 SELECT coalesce(sum("snapshot"."weight"), 0)
3210 FROM "issue" CROSS JOIN "opinion"
3211 JOIN "direct_interest_snapshot" AS "snapshot"
3212 ON "snapshot"."issue_id" = "issue"."id"
3213 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3214 AND "snapshot"."member_id" = "opinion"."member_id"
3215 WHERE "issue"."id" = "issue_id_p"
3216 AND "opinion"."suggestion_id" = "suggestion_id_v"
3217 AND "opinion"."degree" = 1
3218 AND "opinion"."fulfilled" = FALSE
3219 ),
3220 "plus1_fulfilled_count" = (
3221 SELECT coalesce(sum("snapshot"."weight"), 0)
3222 FROM "issue" CROSS JOIN "opinion"
3223 JOIN "direct_interest_snapshot" AS "snapshot"
3224 ON "snapshot"."issue_id" = "issue"."id"
3225 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3226 AND "snapshot"."member_id" = "opinion"."member_id"
3227 WHERE "issue"."id" = "issue_id_p"
3228 AND "opinion"."suggestion_id" = "suggestion_id_v"
3229 AND "opinion"."degree" = 1
3230 AND "opinion"."fulfilled" = TRUE
3231 ),
3232 "plus2_unfulfilled_count" = (
3233 SELECT coalesce(sum("snapshot"."weight"), 0)
3234 FROM "issue" CROSS JOIN "opinion"
3235 JOIN "direct_interest_snapshot" AS "snapshot"
3236 ON "snapshot"."issue_id" = "issue"."id"
3237 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3238 AND "snapshot"."member_id" = "opinion"."member_id"
3239 WHERE "issue"."id" = "issue_id_p"
3240 AND "opinion"."suggestion_id" = "suggestion_id_v"
3241 AND "opinion"."degree" = 2
3242 AND "opinion"."fulfilled" = FALSE
3243 ),
3244 "plus2_fulfilled_count" = (
3245 SELECT coalesce(sum("snapshot"."weight"), 0)
3246 FROM "issue" CROSS JOIN "opinion"
3247 JOIN "direct_interest_snapshot" AS "snapshot"
3248 ON "snapshot"."issue_id" = "issue"."id"
3249 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3250 AND "snapshot"."member_id" = "opinion"."member_id"
3251 WHERE "issue"."id" = "issue_id_p"
3252 AND "opinion"."suggestion_id" = "suggestion_id_v"
3253 AND "opinion"."degree" = 2
3254 AND "opinion"."fulfilled" = TRUE
3255 )
3256 WHERE "suggestion"."id" = "suggestion_id_v";
3257 END LOOP;
3258 END LOOP;
3259 RETURN;
3260 END;
3261 $$;
3263 COMMENT ON FUNCTION "create_snapshot"
3264 ( "issue"."id"%TYPE )
3265 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.';
3268 CREATE FUNCTION "set_snapshot_event"
3269 ( "issue_id_p" "issue"."id"%TYPE,
3270 "event_p" "snapshot_event" )
3271 RETURNS VOID
3272 LANGUAGE 'plpgsql' VOLATILE AS $$
3273 DECLARE
3274 "event_v" "issue"."latest_snapshot_event"%TYPE;
3275 BEGIN
3276 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
3277 WHERE "id" = "issue_id_p" FOR UPDATE;
3278 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
3279 WHERE "id" = "issue_id_p";
3280 UPDATE "direct_population_snapshot" SET "event" = "event_p"
3281 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3282 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
3283 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3284 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
3285 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3286 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
3287 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3288 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
3289 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3290 RETURN;
3291 END;
3292 $$;
3294 COMMENT ON FUNCTION "set_snapshot_event"
3295 ( "issue"."id"%TYPE,
3296 "snapshot_event" )
3297 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
3301 ---------------------
3302 -- Freezing issues --
3303 ---------------------
3305 CREATE FUNCTION "freeze_after_snapshot"
3306 ( "issue_id_p" "issue"."id"%TYPE )
3307 RETURNS VOID
3308 LANGUAGE 'plpgsql' VOLATILE AS $$
3309 DECLARE
3310 "issue_row" "issue"%ROWTYPE;
3311 "policy_row" "policy"%ROWTYPE;
3312 "initiative_row" "initiative"%ROWTYPE;
3313 BEGIN
3314 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3315 SELECT * INTO "policy_row"
3316 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3317 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
3318 FOR "initiative_row" IN
3319 SELECT * FROM "initiative"
3320 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3321 LOOP
3322 IF
3323 "initiative_row"."satisfied_supporter_count" > 0 AND
3324 "initiative_row"."satisfied_supporter_count" *
3325 "policy_row"."initiative_quorum_den" >=
3326 "issue_row"."population" * "policy_row"."initiative_quorum_num"
3327 THEN
3328 UPDATE "initiative" SET "admitted" = TRUE
3329 WHERE "id" = "initiative_row"."id";
3330 ELSE
3331 UPDATE "initiative" SET "admitted" = FALSE
3332 WHERE "id" = "initiative_row"."id";
3333 END IF;
3334 END LOOP;
3335 IF EXISTS (
3336 SELECT NULL FROM "initiative"
3337 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
3338 ) THEN
3339 UPDATE "issue" SET
3340 "state" = 'voting',
3341 "accepted" = coalesce("accepted", now()),
3342 "half_frozen" = coalesce("half_frozen", now()),
3343 "fully_frozen" = now()
3344 WHERE "id" = "issue_id_p";
3345 ELSE
3346 UPDATE "issue" SET
3347 "state" = 'canceled_no_initiative_admitted',
3348 "accepted" = coalesce("accepted", now()),
3349 "half_frozen" = coalesce("half_frozen", now()),
3350 "fully_frozen" = now(),
3351 "closed" = now(),
3352 "ranks_available" = TRUE
3353 WHERE "id" = "issue_id_p";
3354 -- NOTE: The following DELETE statements have effect only when
3355 -- issue state has been manipulated
3356 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
3357 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
3358 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3359 END IF;
3360 RETURN;
3361 END;
3362 $$;
3364 COMMENT ON FUNCTION "freeze_after_snapshot"
3365 ( "issue"."id"%TYPE )
3366 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
3369 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
3370 RETURNS VOID
3371 LANGUAGE 'plpgsql' VOLATILE AS $$
3372 DECLARE
3373 "issue_row" "issue"%ROWTYPE;
3374 BEGIN
3375 PERFORM "create_snapshot"("issue_id_p");
3376 PERFORM "freeze_after_snapshot"("issue_id_p");
3377 RETURN;
3378 END;
3379 $$;
3381 COMMENT ON FUNCTION "manual_freeze"
3382 ( "issue"."id"%TYPE )
3383 IS 'Freeze an issue manually (fully) and start voting';
3387 -----------------------
3388 -- Counting of votes --
3389 -----------------------
3392 CREATE FUNCTION "weight_of_added_vote_delegations"
3393 ( "issue_id_p" "issue"."id"%TYPE,
3394 "member_id_p" "member"."id"%TYPE,
3395 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
3396 RETURNS "direct_voter"."weight"%TYPE
3397 LANGUAGE 'plpgsql' VOLATILE AS $$
3398 DECLARE
3399 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3400 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
3401 "weight_v" INT4;
3402 "sub_weight_v" INT4;
3403 BEGIN
3404 "weight_v" := 0;
3405 FOR "issue_delegation_row" IN
3406 SELECT * FROM "issue_delegation"
3407 WHERE "trustee_id" = "member_id_p"
3408 AND "issue_id" = "issue_id_p"
3409 LOOP
3410 IF NOT EXISTS (
3411 SELECT NULL FROM "direct_voter"
3412 WHERE "member_id" = "issue_delegation_row"."truster_id"
3413 AND "issue_id" = "issue_id_p"
3414 ) AND NOT EXISTS (
3415 SELECT NULL FROM "delegating_voter"
3416 WHERE "member_id" = "issue_delegation_row"."truster_id"
3417 AND "issue_id" = "issue_id_p"
3418 ) THEN
3419 "delegate_member_ids_v" :=
3420 "member_id_p" || "delegate_member_ids_p";
3421 INSERT INTO "delegating_voter" (
3422 "issue_id",
3423 "member_id",
3424 "scope",
3425 "delegate_member_ids"
3426 ) VALUES (
3427 "issue_id_p",
3428 "issue_delegation_row"."truster_id",
3429 "issue_delegation_row"."scope",
3430 "delegate_member_ids_v"
3431 );
3432 "sub_weight_v" := 1 +
3433 "weight_of_added_vote_delegations"(
3434 "issue_id_p",
3435 "issue_delegation_row"."truster_id",
3436 "delegate_member_ids_v"
3437 );
3438 UPDATE "delegating_voter"
3439 SET "weight" = "sub_weight_v"
3440 WHERE "issue_id" = "issue_id_p"
3441 AND "member_id" = "issue_delegation_row"."truster_id";
3442 "weight_v" := "weight_v" + "sub_weight_v";
3443 END IF;
3444 END LOOP;
3445 RETURN "weight_v";
3446 END;
3447 $$;
3449 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
3450 ( "issue"."id"%TYPE,
3451 "member"."id"%TYPE,
3452 "delegating_voter"."delegate_member_ids"%TYPE )
3453 IS 'Helper function for "add_vote_delegations" function';
3456 CREATE FUNCTION "add_vote_delegations"
3457 ( "issue_id_p" "issue"."id"%TYPE )
3458 RETURNS VOID
3459 LANGUAGE 'plpgsql' VOLATILE AS $$
3460 DECLARE
3461 "member_id_v" "member"."id"%TYPE;
3462 BEGIN
3463 FOR "member_id_v" IN
3464 SELECT "member_id" FROM "direct_voter"
3465 WHERE "issue_id" = "issue_id_p"
3466 LOOP
3467 UPDATE "direct_voter" SET
3468 "weight" = "weight" + "weight_of_added_vote_delegations"(
3469 "issue_id_p",
3470 "member_id_v",
3471 '{}'
3472 )
3473 WHERE "member_id" = "member_id_v"
3474 AND "issue_id" = "issue_id_p";
3475 END LOOP;
3476 RETURN;
3477 END;
3478 $$;
3480 COMMENT ON FUNCTION "add_vote_delegations"
3481 ( "issue_id_p" "issue"."id"%TYPE )
3482 IS 'Helper function for "close_voting" function';
3485 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
3486 RETURNS VOID
3487 LANGUAGE 'plpgsql' VOLATILE AS $$
3488 DECLARE
3489 "area_id_v" "area"."id"%TYPE;
3490 "unit_id_v" "unit"."id"%TYPE;
3491 "member_id_v" "member"."id"%TYPE;
3492 BEGIN
3493 PERFORM "lock_issue"("issue_id_p");
3494 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
3495 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
3496 -- delete delegating votes (in cases of manual reset of issue state):
3497 DELETE FROM "delegating_voter"
3498 WHERE "issue_id" = "issue_id_p";
3499 -- delete votes from non-privileged voters:
3500 DELETE FROM "direct_voter"
3501 USING (
3502 SELECT
3503 "direct_voter"."member_id"
3504 FROM "direct_voter"
3505 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
3506 LEFT JOIN "privilege"
3507 ON "privilege"."unit_id" = "unit_id_v"
3508 AND "privilege"."member_id" = "direct_voter"."member_id"
3509 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
3510 "member"."active" = FALSE OR
3511 "privilege"."voting_right" ISNULL OR
3512 "privilege"."voting_right" = FALSE
3513 )
3514 ) AS "subquery"
3515 WHERE "direct_voter"."issue_id" = "issue_id_p"
3516 AND "direct_voter"."member_id" = "subquery"."member_id";
3517 -- consider delegations:
3518 UPDATE "direct_voter" SET "weight" = 1
3519 WHERE "issue_id" = "issue_id_p";
3520 PERFORM "add_vote_delegations"("issue_id_p");
3521 -- set voter count and mark issue as being calculated:
3522 UPDATE "issue" SET
3523 "state" = 'calculation',
3524 "closed" = now(),
3525 "voter_count" = (
3526 SELECT coalesce(sum("weight"), 0)
3527 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
3528 )
3529 WHERE "id" = "issue_id_p";
3530 -- materialize battle_view:
3531 -- NOTE: "closed" column of issue must be set at this point
3532 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3533 INSERT INTO "battle" (
3534 "issue_id",
3535 "winning_initiative_id", "losing_initiative_id",
3536 "count"
3537 ) SELECT
3538 "issue_id",
3539 "winning_initiative_id", "losing_initiative_id",
3540 "count"
3541 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
3542 -- copy "positive_votes" and "negative_votes" from "battle" table:
3543 UPDATE "initiative" SET
3544 "positive_votes" = "battle_win"."count",
3545 "negative_votes" = "battle_lose"."count"
3546 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
3547 WHERE
3548 "battle_win"."issue_id" = "issue_id_p" AND
3549 "battle_win"."winning_initiative_id" = "initiative"."id" AND
3550 "battle_win"."losing_initiative_id" ISNULL AND
3551 "battle_lose"."issue_id" = "issue_id_p" AND
3552 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
3553 "battle_lose"."winning_initiative_id" ISNULL;
3554 END;
3555 $$;
3557 COMMENT ON FUNCTION "close_voting"
3558 ( "issue"."id"%TYPE )
3559 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.';
3562 CREATE FUNCTION "defeat_strength"
3563 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
3564 RETURNS INT8
3565 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3566 BEGIN
3567 IF "positive_votes_p" > "negative_votes_p" THEN
3568 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
3569 ELSIF "positive_votes_p" = "negative_votes_p" THEN
3570 RETURN 0;
3571 ELSE
3572 RETURN -1;
3573 END IF;
3574 END;
3575 $$;
3577 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';
3580 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
3581 RETURNS VOID
3582 LANGUAGE 'plpgsql' VOLATILE AS $$
3583 DECLARE
3584 "issue_row" "issue"%ROWTYPE;
3585 "policy_row" "policy"%ROWTYPE;
3586 "dimension_v" INTEGER;
3587 "vote_matrix" INT4[][]; -- absolute votes
3588 "matrix" INT8[][]; -- defeat strength / best paths
3589 "i" INTEGER;
3590 "j" INTEGER;
3591 "k" INTEGER;
3592 "battle_row" "battle"%ROWTYPE;
3593 "rank_ary" INT4[];
3594 "rank_v" INT4;
3595 "done_v" INTEGER;
3596 "winners_ary" INTEGER[];
3597 "initiative_id_v" "initiative"."id"%TYPE;
3598 BEGIN
3599 SELECT * INTO "issue_row"
3600 FROM "issue" WHERE "id" = "issue_id_p"
3601 FOR UPDATE;
3602 SELECT * INTO "policy_row"
3603 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3604 SELECT count(1) INTO "dimension_v"
3605 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
3606 -- Create "vote_matrix" with absolute number of votes in pairwise
3607 -- comparison:
3608 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
3609 "i" := 1;
3610 "j" := 2;
3611 FOR "battle_row" IN
3612 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
3613 ORDER BY
3614 "winning_initiative_id" NULLS LAST,
3615 "losing_initiative_id" NULLS LAST
3616 LOOP
3617 "vote_matrix"["i"]["j"] := "battle_row"."count";
3618 IF "j" = "dimension_v" THEN
3619 "i" := "i" + 1;
3620 "j" := 1;
3621 ELSE
3622 "j" := "j" + 1;
3623 IF "j" = "i" THEN
3624 "j" := "j" + 1;
3625 END IF;
3626 END IF;
3627 END LOOP;
3628 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
3629 RAISE EXCEPTION 'Wrong battle count (should not happen)';
3630 END IF;
3631 -- Store defeat strengths in "matrix" using "defeat_strength"
3632 -- function:
3633 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
3634 "i" := 1;
3635 LOOP
3636 "j" := 1;
3637 LOOP
3638 IF "i" != "j" THEN
3639 "matrix"["i"]["j"] := "defeat_strength"(
3640 "vote_matrix"["i"]["j"],
3641 "vote_matrix"["j"]["i"]
3642 );
3643 END IF;
3644 EXIT WHEN "j" = "dimension_v";
3645 "j" := "j" + 1;
3646 END LOOP;
3647 EXIT WHEN "i" = "dimension_v";
3648 "i" := "i" + 1;
3649 END LOOP;
3650 -- Find best paths:
3651 "i" := 1;
3652 LOOP
3653 "j" := 1;
3654 LOOP
3655 IF "i" != "j" THEN
3656 "k" := 1;
3657 LOOP
3658 IF "i" != "k" AND "j" != "k" THEN
3659 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
3660 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
3661 "matrix"["j"]["k"] := "matrix"["j"]["i"];
3662 END IF;
3663 ELSE
3664 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
3665 "matrix"["j"]["k"] := "matrix"["i"]["k"];
3666 END IF;
3667 END IF;
3668 END IF;
3669 EXIT WHEN "k" = "dimension_v";
3670 "k" := "k" + 1;
3671 END LOOP;
3672 END IF;
3673 EXIT WHEN "j" = "dimension_v";
3674 "j" := "j" + 1;
3675 END LOOP;
3676 EXIT WHEN "i" = "dimension_v";
3677 "i" := "i" + 1;
3678 END LOOP;
3679 -- Determine order of winners:
3680 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
3681 "rank_v" := 1;
3682 "done_v" := 0;
3683 LOOP
3684 "winners_ary" := '{}';
3685 "i" := 1;
3686 LOOP
3687 IF "rank_ary"["i"] ISNULL THEN
3688 "j" := 1;
3689 LOOP
3690 IF
3691 "i" != "j" AND
3692 "rank_ary"["j"] ISNULL AND
3693 "matrix"["j"]["i"] > "matrix"["i"]["j"]
3694 THEN
3695 -- someone else is better
3696 EXIT;
3697 END IF;
3698 IF "j" = "dimension_v" THEN
3699 -- noone is better
3700 "winners_ary" := "winners_ary" || "i";
3701 EXIT;
3702 END IF;
3703 "j" := "j" + 1;
3704 END LOOP;
3705 END IF;
3706 EXIT WHEN "i" = "dimension_v";
3707 "i" := "i" + 1;
3708 END LOOP;
3709 "i" := 1;
3710 LOOP
3711 "rank_ary"["winners_ary"["i"]] := "rank_v";
3712 "done_v" := "done_v" + 1;
3713 EXIT WHEN "i" = array_upper("winners_ary", 1);
3714 "i" := "i" + 1;
3715 END LOOP;
3716 EXIT WHEN "done_v" = "dimension_v";
3717 "rank_v" := "rank_v" + 1;
3718 END LOOP;
3719 -- write preliminary results:
3720 "i" := 1;
3721 FOR "initiative_id_v" IN
3722 SELECT "id" FROM "initiative"
3723 WHERE "issue_id" = "issue_id_p" AND "admitted"
3724 ORDER BY "id"
3725 LOOP
3726 UPDATE "initiative" SET
3727 "direct_majority" =
3728 CASE WHEN "policy_row"."direct_majority_strict" THEN
3729 "positive_votes" * "policy_row"."direct_majority_den" >
3730 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
3731 ELSE
3732 "positive_votes" * "policy_row"."direct_majority_den" >=
3733 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
3734 END
3735 AND "positive_votes" >= "policy_row"."direct_majority_positive"
3736 AND "issue_row"."voter_count"-"negative_votes" >=
3737 "policy_row"."direct_majority_non_negative",
3738 "indirect_majority" =
3739 CASE WHEN "policy_row"."indirect_majority_strict" THEN
3740 "positive_votes" * "policy_row"."indirect_majority_den" >
3741 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
3742 ELSE
3743 "positive_votes" * "policy_row"."indirect_majority_den" >=
3744 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
3745 END
3746 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
3747 AND "issue_row"."voter_count"-"negative_votes" >=
3748 "policy_row"."indirect_majority_non_negative",
3749 "schulze_rank" = "rank_ary"["i"],
3750 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
3751 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
3752 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
3753 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0,
3754 "eligible" = FALSE,
3755 "winner" = FALSE
3756 WHERE "id" = "initiative_id_v";
3757 "i" := "i" + 1;
3758 END LOOP;
3759 IF "i" != "dimension_v" THEN
3760 RAISE EXCEPTION 'Wrong winner count (should not happen)';
3761 END IF;
3762 -- take indirect majorities into account:
3763 LOOP
3764 UPDATE "initiative" SET "indirect_majority" = TRUE
3765 FROM (
3766 SELECT "new_initiative"."id" AS "initiative_id"
3767 FROM "initiative" "old_initiative"
3768 JOIN "initiative" "new_initiative"
3769 ON "new_initiative"."issue_id" = "issue_id_p"
3770 AND "new_initiative"."indirect_majority" = FALSE
3771 JOIN "battle" "battle_win"
3772 ON "battle_win"."issue_id" = "issue_id_p"
3773 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
3774 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
3775 JOIN "battle" "battle_lose"
3776 ON "battle_lose"."issue_id" = "issue_id_p"
3777 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
3778 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
3779 WHERE "old_initiative"."issue_id" = "issue_id_p"
3780 AND "old_initiative"."indirect_majority" = TRUE
3781 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
3782 "battle_win"."count" * "policy_row"."indirect_majority_den" >
3783 "policy_row"."indirect_majority_num" *
3784 ("battle_win"."count"+"battle_lose"."count")
3785 ELSE
3786 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
3787 "policy_row"."indirect_majority_num" *
3788 ("battle_win"."count"+"battle_lose"."count")
3789 END
3790 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
3791 AND "issue_row"."voter_count"-"battle_lose"."count" >=
3792 "policy_row"."indirect_majority_non_negative"
3793 ) AS "subquery"
3794 WHERE "id" = "subquery"."initiative_id";
3795 EXIT WHEN NOT FOUND;
3796 END LOOP;
3797 -- set "multistage_majority" for remaining matching initiatives:
3798 UPDATE "initiative" SET "multistage_majority" = TRUE
3799 FROM (
3800 SELECT "losing_initiative"."id" AS "initiative_id"
3801 FROM "initiative" "losing_initiative"
3802 JOIN "initiative" "winning_initiative"
3803 ON "winning_initiative"."issue_id" = "issue_id_p"
3804 AND "winning_initiative"."admitted"
3805 JOIN "battle" "battle_win"
3806 ON "battle_win"."issue_id" = "issue_id_p"
3807 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
3808 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
3809 JOIN "battle" "battle_lose"
3810 ON "battle_lose"."issue_id" = "issue_id_p"
3811 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
3812 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
3813 WHERE "losing_initiative"."issue_id" = "issue_id_p"
3814 AND "losing_initiative"."admitted"
3815 AND "winning_initiative"."schulze_rank" <
3816 "losing_initiative"."schulze_rank"
3817 AND "battle_win"."count" > "battle_lose"."count"
3818 AND (
3819 "battle_win"."count" > "winning_initiative"."positive_votes" OR
3820 "battle_lose"."count" < "losing_initiative"."negative_votes" )
3821 ) AS "subquery"
3822 WHERE "id" = "subquery"."initiative_id";
3823 -- mark eligible initiatives:
3824 UPDATE "initiative" SET "eligible" = TRUE
3825 WHERE "issue_id" = "issue_id_p"
3826 AND "initiative"."direct_majority"
3827 AND "initiative"."indirect_majority"
3828 AND "initiative"."better_than_status_quo"
3829 AND (
3830 "policy_row"."no_multistage_majority" = FALSE OR
3831 "initiative"."multistage_majority" = FALSE )
3832 AND (
3833 "policy_row"."no_reverse_beat_path" = FALSE OR
3834 "initiative"."reverse_beat_path" = FALSE );
3835 -- mark final winner:
3836 UPDATE "initiative" SET "winner" = TRUE
3837 FROM (
3838 SELECT "id" AS "initiative_id"
3839 FROM "initiative"
3840 WHERE "issue_id" = "issue_id_p" AND "eligible"
3841 ORDER BY
3842 "schulze_rank",
3843 "vote_ratio"("positive_votes", "negative_votes"),
3844 "id"
3845 LIMIT 1
3846 ) AS "subquery"
3847 WHERE "id" = "subquery"."initiative_id";
3848 -- write (final) ranks:
3849 "rank_v" := 1;
3850 FOR "initiative_id_v" IN
3851 SELECT "id"
3852 FROM "initiative"
3853 WHERE "issue_id" = "issue_id_p" AND "admitted"
3854 ORDER BY
3855 "winner" DESC,
3856 "eligible" DESC,
3857 "schulze_rank",
3858 "vote_ratio"("positive_votes", "negative_votes"),
3859 "id"
3860 LOOP
3861 UPDATE "initiative" SET "rank" = "rank_v"
3862 WHERE "id" = "initiative_id_v";
3863 "rank_v" := "rank_v" + 1;
3864 END LOOP;
3865 -- set schulze rank of status quo and mark issue as finished:
3866 UPDATE "issue" SET
3867 "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
3868 "state" =
3869 CASE WHEN EXISTS (
3870 SELECT NULL FROM "initiative"
3871 WHERE "issue_id" = "issue_id_p" AND "winner"
3872 ) THEN
3873 'finished_with_winner'::"issue_state"
3874 ELSE
3875 'finished_without_winner'::"issue_state"
3876 END,
3877 "ranks_available" = TRUE
3878 WHERE "id" = "issue_id_p";
3879 RETURN;
3880 END;
3881 $$;
3883 COMMENT ON FUNCTION "calculate_ranks"
3884 ( "issue"."id"%TYPE )
3885 IS 'Determine ranking (Votes have to be counted first)';
3889 -----------------------------
3890 -- Automatic state changes --
3891 -----------------------------
3894 CREATE FUNCTION "check_issue"
3895 ( "issue_id_p" "issue"."id"%TYPE )
3896 RETURNS VOID
3897 LANGUAGE 'plpgsql' VOLATILE AS $$
3898 DECLARE
3899 "issue_row" "issue"%ROWTYPE;
3900 "policy_row" "policy"%ROWTYPE;
3901 BEGIN
3902 PERFORM "lock_issue"("issue_id_p");
3903 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3904 -- only process open issues:
3905 IF "issue_row"."closed" ISNULL THEN
3906 SELECT * INTO "policy_row" FROM "policy"
3907 WHERE "id" = "issue_row"."policy_id";
3908 -- create a snapshot, unless issue is already fully frozen:
3909 IF "issue_row"."fully_frozen" ISNULL THEN
3910 PERFORM "create_snapshot"("issue_id_p");
3911 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3912 END IF;
3913 -- eventually close or accept issues, which have not been accepted:
3914 IF "issue_row"."accepted" ISNULL THEN
3915 IF EXISTS (
3916 SELECT NULL FROM "initiative"
3917 WHERE "issue_id" = "issue_id_p"
3918 AND "supporter_count" > 0
3919 AND "supporter_count" * "policy_row"."issue_quorum_den"
3920 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
3921 ) THEN
3922 -- accept issues, if supporter count is high enough
3923 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3924 -- NOTE: "issue_row" used later
3925 "issue_row"."state" := 'discussion';
3926 "issue_row"."accepted" := now();
3927 UPDATE "issue" SET
3928 "state" = "issue_row"."state",
3929 "accepted" = "issue_row"."accepted"
3930 WHERE "id" = "issue_row"."id";
3931 ELSIF
3932 now() >= "issue_row"."created" + "issue_row"."admission_time"
3933 THEN
3934 -- close issues, if admission time has expired
3935 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3936 UPDATE "issue" SET
3937 "state" = 'canceled_issue_not_accepted',
3938 "closed" = now()
3939 WHERE "id" = "issue_row"."id";
3940 END IF;
3941 END IF;
3942 -- eventually half freeze issues:
3943 IF
3944 -- NOTE: issue can't be closed at this point, if it has been accepted
3945 "issue_row"."accepted" NOTNULL AND
3946 "issue_row"."half_frozen" ISNULL
3947 THEN
3948 IF
3949 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
3950 THEN
3951 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
3952 -- NOTE: "issue_row" used later
3953 "issue_row"."state" := 'verification';
3954 "issue_row"."half_frozen" := now();
3955 UPDATE "issue" SET
3956 "state" = "issue_row"."state",
3957 "half_frozen" = "issue_row"."half_frozen"
3958 WHERE "id" = "issue_row"."id";
3959 END IF;
3960 END IF;
3961 -- close issues after some time, if all initiatives have been revoked:
3962 IF
3963 "issue_row"."closed" ISNULL AND
3964 NOT EXISTS (
3965 -- all initiatives are revoked
3966 SELECT NULL FROM "initiative"
3967 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3968 ) AND (
3969 -- and issue has not been accepted yet
3970 "issue_row"."accepted" ISNULL OR
3971 NOT EXISTS (
3972 -- or no initiatives have been revoked lately
3973 SELECT NULL FROM "initiative"
3974 WHERE "issue_id" = "issue_id_p"
3975 AND now() < "revoked" + "issue_row"."verification_time"
3976 ) OR (
3977 -- or verification time has elapsed
3978 "issue_row"."half_frozen" NOTNULL AND
3979 "issue_row"."fully_frozen" ISNULL AND
3980 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3981 )
3982 )
3983 THEN
3984 -- NOTE: "issue_row" used later
3985 IF "issue_row"."accepted" ISNULL THEN
3986 "issue_row"."state" := 'canceled_revoked_before_accepted';
3987 ELSIF "issue_row"."half_frozen" ISNULL THEN
3988 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
3989 ELSE
3990 "issue_row"."state" := 'canceled_after_revocation_during_verification';
3991 END IF;
3992 "issue_row"."closed" := now();
3993 UPDATE "issue" SET
3994 "state" = "issue_row"."state",
3995 "closed" = "issue_row"."closed"
3996 WHERE "id" = "issue_row"."id";
3997 END IF;
3998 -- fully freeze issue after verification time:
3999 IF
4000 "issue_row"."half_frozen" NOTNULL AND
4001 "issue_row"."fully_frozen" ISNULL AND
4002 "issue_row"."closed" ISNULL AND
4003 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
4004 THEN
4005 PERFORM "freeze_after_snapshot"("issue_id_p");
4006 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
4007 END IF;
4008 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4009 -- close issue by calling close_voting(...) after voting time:
4010 IF
4011 "issue_row"."closed" ISNULL AND
4012 "issue_row"."fully_frozen" NOTNULL AND
4013 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
4014 THEN
4015 PERFORM "close_voting"("issue_id_p");
4016 -- calculate ranks will not consume much time and can be done now
4017 PERFORM "calculate_ranks"("issue_id_p");
4018 END IF;
4019 END IF;
4020 RETURN;
4021 END;
4022 $$;
4024 COMMENT ON FUNCTION "check_issue"
4025 ( "issue"."id"%TYPE )
4026 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.';
4029 CREATE FUNCTION "check_everything"()
4030 RETURNS VOID
4031 LANGUAGE 'plpgsql' VOLATILE AS $$
4032 DECLARE
4033 "issue_id_v" "issue"."id"%TYPE;
4034 BEGIN
4035 PERFORM "check_activity"();
4036 PERFORM "calculate_member_counts"();
4037 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
4038 PERFORM "check_issue"("issue_id_v");
4039 END LOOP;
4040 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
4041 PERFORM "calculate_ranks"("issue_id_v");
4042 END LOOP;
4043 RETURN;
4044 END;
4045 $$;
4047 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.';
4051 ----------------------
4052 -- Deletion of data --
4053 ----------------------
4056 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
4057 RETURNS VOID
4058 LANGUAGE 'plpgsql' VOLATILE AS $$
4059 DECLARE
4060 "issue_row" "issue"%ROWTYPE;
4061 BEGIN
4062 SELECT * INTO "issue_row"
4063 FROM "issue" WHERE "id" = "issue_id_p"
4064 FOR UPDATE;
4065 IF "issue_row"."cleaned" ISNULL THEN
4066 UPDATE "issue" SET
4067 "state" = 'voting',
4068 "closed" = NULL,
4069 "ranks_available" = FALSE
4070 WHERE "id" = "issue_id_p";
4071 DELETE FROM "issue_comment"
4072 WHERE "issue_id" = "issue_id_p";
4073 DELETE FROM "voting_comment"
4074 WHERE "issue_id" = "issue_id_p";
4075 DELETE FROM "delegating_voter"
4076 WHERE "issue_id" = "issue_id_p";
4077 DELETE FROM "direct_voter"
4078 WHERE "issue_id" = "issue_id_p";
4079 DELETE FROM "delegating_interest_snapshot"
4080 WHERE "issue_id" = "issue_id_p";
4081 DELETE FROM "direct_interest_snapshot"
4082 WHERE "issue_id" = "issue_id_p";
4083 DELETE FROM "delegating_population_snapshot"
4084 WHERE "issue_id" = "issue_id_p";
4085 DELETE FROM "direct_population_snapshot"
4086 WHERE "issue_id" = "issue_id_p";
4087 DELETE FROM "non_voter"
4088 WHERE "issue_id" = "issue_id_p";
4089 DELETE FROM "delegation"
4090 WHERE "issue_id" = "issue_id_p";
4091 DELETE FROM "supporter"
4092 WHERE "issue_id" = "issue_id_p";
4093 UPDATE "issue" SET
4094 "state" = "issue_row"."state",
4095 "closed" = "issue_row"."closed",
4096 "ranks_available" = "issue_row"."ranks_available",
4097 "cleaned" = now()
4098 WHERE "id" = "issue_id_p";
4099 END IF;
4100 RETURN;
4101 END;
4102 $$;
4104 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
4107 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
4108 RETURNS VOID
4109 LANGUAGE 'plpgsql' VOLATILE AS $$
4110 BEGIN
4111 UPDATE "member" SET
4112 "last_login" = NULL,
4113 "login" = NULL,
4114 "password" = NULL,
4115 "locked" = TRUE,
4116 "active" = FALSE,
4117 "notify_email" = NULL,
4118 "notify_email_unconfirmed" = NULL,
4119 "notify_email_secret" = NULL,
4120 "notify_email_secret_expiry" = NULL,
4121 "notify_email_lock_expiry" = NULL,
4122 "password_reset_secret" = NULL,
4123 "password_reset_secret_expiry" = NULL,
4124 "organizational_unit" = NULL,
4125 "internal_posts" = NULL,
4126 "realname" = NULL,
4127 "birthday" = NULL,
4128 "address" = NULL,
4129 "email" = NULL,
4130 "xmpp_address" = NULL,
4131 "website" = NULL,
4132 "phone" = NULL,
4133 "mobile_phone" = NULL,
4134 "profession" = NULL,
4135 "external_memberships" = NULL,
4136 "external_posts" = NULL,
4137 "statement" = NULL
4138 WHERE "id" = "member_id_p";
4139 -- "text_search_data" is updated by triggers
4140 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
4141 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
4142 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
4143 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
4144 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
4145 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
4146 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
4147 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
4148 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
4149 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
4150 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
4151 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
4152 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
4153 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
4154 DELETE FROM "direct_voter" USING "issue"
4155 WHERE "direct_voter"."issue_id" = "issue"."id"
4156 AND "issue"."closed" ISNULL
4157 AND "member_id" = "member_id_p";
4158 RETURN;
4159 END;
4160 $$;
4162 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)';
4165 CREATE FUNCTION "delete_private_data"()
4166 RETURNS VOID
4167 LANGUAGE 'plpgsql' VOLATILE AS $$
4168 BEGIN
4169 DELETE FROM "member" WHERE "activated" ISNULL;
4170 UPDATE "member" SET
4171 "invite_code" = NULL,
4172 "admin_comment" = NULL,
4173 "last_login" = NULL,
4174 "login" = NULL,
4175 "password" = NULL,
4176 "notify_email" = NULL,
4177 "notify_email_unconfirmed" = NULL,
4178 "notify_email_secret" = NULL,
4179 "notify_email_secret_expiry" = NULL,
4180 "notify_email_lock_expiry" = NULL,
4181 "password_reset_secret" = NULL,
4182 "password_reset_secret_expiry" = NULL,
4183 "organizational_unit" = NULL,
4184 "internal_posts" = NULL,
4185 "realname" = NULL,
4186 "birthday" = NULL,
4187 "address" = NULL,
4188 "email" = NULL,
4189 "xmpp_address" = NULL,
4190 "website" = NULL,
4191 "phone" = NULL,
4192 "mobile_phone" = NULL,
4193 "profession" = NULL,
4194 "external_memberships" = NULL,
4195 "external_posts" = NULL,
4196 "statement" = NULL;
4197 -- "text_search_data" is updated by triggers
4198 DELETE FROM "setting";
4199 DELETE FROM "setting_map";
4200 DELETE FROM "member_relation_setting";
4201 DELETE FROM "member_image";
4202 DELETE FROM "contact";
4203 DELETE FROM "ignored_member";
4204 DELETE FROM "area_setting";
4205 DELETE FROM "issue_setting";
4206 DELETE FROM "ignored_initiative";
4207 DELETE FROM "initiative_setting";
4208 DELETE FROM "suggestion_setting";
4209 DELETE FROM "non_voter";
4210 DELETE FROM "direct_voter" USING "issue"
4211 WHERE "direct_voter"."issue_id" = "issue"."id"
4212 AND "issue"."closed" ISNULL;
4213 RETURN;
4214 END;
4215 $$;
4217 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.';
4221 COMMIT;