liquid_feedback_core
view core.sql @ 221:4a7ae2507515
Added column "lang" to table "member"
| author | jbe |
|---|---|
| date | Sat Feb 25 15:09:59 2012 +0100 (2012-02-25) |
| parents | 4e3d8f1ca3a9 |
| children | cd5fa6c46e13 |
line source
2 -- Execute the following command manually for PostgreSQL prior version 9.0:
3 -- CREATE LANGUAGE plpgsql;
5 -- NOTE: In PostgreSQL every UNIQUE constraint implies creation of an index
7 BEGIN;
9 CREATE VIEW "liquid_feedback_version" AS
10 SELECT * FROM (VALUES ('2.0.3', 2, 0, 3))
11 AS "subquery"("string", "major", "minor", "revision");
15 ----------------------
16 -- Full text search --
17 ----------------------
20 CREATE FUNCTION "text_search_query"("query_text_p" TEXT)
21 RETURNS TSQUERY
22 LANGUAGE 'plpgsql' IMMUTABLE AS $$
23 BEGIN
24 RETURN plainto_tsquery('pg_catalog.simple', "query_text_p");
25 END;
26 $$;
28 COMMENT ON FUNCTION "text_search_query"(TEXT) IS 'Usage: WHERE "text_search_data" @@ "text_search_query"(''<user query>'')';
31 CREATE FUNCTION "highlight"
32 ( "body_p" TEXT,
33 "query_text_p" TEXT )
34 RETURNS TEXT
35 LANGUAGE 'plpgsql' IMMUTABLE AS $$
36 BEGIN
37 RETURN ts_headline(
38 'pg_catalog.simple',
39 replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'),
40 "text_search_query"("query_text_p"),
41 'StartSel=* StopSel=* HighlightAll=TRUE' );
42 END;
43 $$;
45 COMMENT ON FUNCTION "highlight"
46 ( "body_p" TEXT,
47 "query_text_p" TEXT )
48 IS 'For a given a user query this function encapsulates all matches with asterisks. Asterisks and backslashes being already present are preceeded with one extra backslash.';
52 -------------------------
53 -- Tables and indicies --
54 -------------------------
57 CREATE TABLE "system_setting" (
58 "member_ttl" INTERVAL );
59 CREATE UNIQUE INDEX "system_setting_singleton_idx" ON "system_setting" ((1));
61 COMMENT ON TABLE "system_setting" IS 'This table contains only one row with different settings in each column.';
62 COMMENT ON INDEX "system_setting_singleton_idx" IS 'This index ensures that "system_setting" only contains one row maximum.';
64 COMMENT ON COLUMN "system_setting"."member_ttl" IS 'Time after members get their "active" flag set to FALSE, if they do not show any activity.';
67 CREATE TABLE "contingent" (
68 "time_frame" INTERVAL PRIMARY KEY,
69 "text_entry_limit" INT4,
70 "initiative_limit" INT4 );
72 COMMENT ON TABLE "contingent" IS 'Amount of text entries or initiatives a user may create within a given time frame. Only one row needs to be fulfilled for a member to be allowed to post. This table must not be empty.';
74 COMMENT ON COLUMN "contingent"."text_entry_limit" IS 'Number of new drafts or suggestions to be submitted by each member within the given time frame';
75 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
78 CREATE TYPE "notify_level" AS ENUM
79 ('none', 'voting', 'verification', 'discussion', 'all');
81 COMMENT ON TYPE "notify_level" IS 'Level of notification: ''none'' = no notifications, ''voting'' = notifications about finished issues and issues in voting, ''verification'' = notifications about finished issues, issues in voting and verification phase, ''discussion'' = notifications about everything except issues in admission phase, ''all'' = notifications about everything';
84 CREATE TABLE "member" (
85 "id" SERIAL4 PRIMARY KEY,
86 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
87 "invite_code" TEXT UNIQUE,
88 "admin_comment" TEXT,
89 "activated" TIMESTAMPTZ,
90 "last_activity" DATE,
91 "last_login" TIMESTAMPTZ,
92 "login" TEXT UNIQUE,
93 "password" TEXT,
94 "locked" BOOLEAN NOT NULL DEFAULT FALSE,
95 "active" BOOLEAN NOT NULL DEFAULT FALSE,
96 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
97 "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" NOT NULL DEFAULT 'none',
104 "notify_event_id" INT8,
105 "password_reset_secret" TEXT UNIQUE,
106 "password_reset_secret_expiry" TIMESTAMPTZ,
107 "name" TEXT NOT NULL UNIQUE,
108 "identification" TEXT UNIQUE,
109 "authentication" TEXT,
110 "organizational_unit" TEXT,
111 "internal_posts" TEXT,
112 "realname" TEXT,
113 "birthday" DATE,
114 "address" TEXT,
115 "email" TEXT,
116 "xmpp_address" TEXT,
117 "website" TEXT,
118 "phone" TEXT,
119 "mobile_phone" TEXT,
120 "profession" TEXT,
121 "external_memberships" TEXT,
122 "external_posts" TEXT,
123 "formatting_engine" TEXT,
124 "statement" TEXT,
125 "text_search_data" TSVECTOR,
126 CONSTRAINT "active_requires_activated_and_last_activity"
127 CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)) );
128 CREATE INDEX "member_active_idx" ON "member" ("active");
129 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
130 CREATE TRIGGER "update_text_search_data"
131 BEFORE INSERT OR UPDATE ON "member"
132 FOR EACH ROW EXECUTE PROCEDURE
133 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
134 "name", "identification", "organizational_unit", "internal_posts",
135 "realname", "external_memberships", "external_posts", "statement" );
137 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
139 COMMENT ON COLUMN "member"."created" IS 'Creation of member record and/or invite code';
140 COMMENT ON COLUMN "member"."invite_code" IS 'Optional invite code, to allow a member to initialize his/her account the first time';
141 COMMENT ON COLUMN "member"."admin_comment" IS 'Hidden comment for administrative purposes';
142 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';
143 COMMENT ON COLUMN "member"."last_activity" IS 'Date of last activity of member; required to be set for "active" members';
144 COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login';
145 COMMENT ON COLUMN "member"."login" IS 'Login name';
146 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
147 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
148 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".';
149 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
150 COMMENT ON COLUMN "member"."lang" IS 'Language code of the preferred language of the member';
151 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
152 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
153 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
154 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
155 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
156 COMMENT ON COLUMN "member"."notify_level" IS 'Selects which event notifications are to be sent to the "notify_email" mail address';
157 COMMENT ON COLUMN "member"."notify_event_id" IS 'Latest "id" of an "event" the member was notified about';
158 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member';
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 -- TODO: indicies
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';
1175 ----------------------------------------------
1176 -- Writing of history entries and event log --
1177 ----------------------------------------------
1180 CREATE FUNCTION "write_member_history_trigger"()
1181 RETURNS TRIGGER
1182 LANGUAGE 'plpgsql' VOLATILE AS $$
1183 BEGIN
1184 IF
1185 NEW."active" != OLD."active" OR
1186 NEW."name" != OLD."name"
1187 THEN
1188 INSERT INTO "member_history"
1189 ("member_id", "active", "name")
1190 VALUES (NEW."id", OLD."active", OLD."name");
1191 END IF;
1192 RETURN NULL;
1193 END;
1194 $$;
1196 CREATE TRIGGER "write_member_history"
1197 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1198 "write_member_history_trigger"();
1200 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
1201 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
1204 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
1205 RETURNS TRIGGER
1206 LANGUAGE 'plpgsql' VOLATILE AS $$
1207 BEGIN
1208 IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN
1209 INSERT INTO "event" ("event", "issue_id", "state")
1210 VALUES ('issue_state_changed', NEW."id", NEW."state");
1211 END IF;
1212 RETURN NULL;
1213 END;
1214 $$;
1216 CREATE TRIGGER "write_event_issue_state_changed"
1217 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
1218 "write_event_issue_state_changed_trigger"();
1220 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
1221 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
1224 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1225 RETURNS TRIGGER
1226 LANGUAGE 'plpgsql' VOLATILE AS $$
1227 DECLARE
1228 "initiative_row" "initiative"%ROWTYPE;
1229 "issue_row" "issue"%ROWTYPE;
1230 "event_v" "event_type";
1231 BEGIN
1232 SELECT * INTO "initiative_row" FROM "initiative"
1233 WHERE "id" = NEW."initiative_id";
1234 SELECT * INTO "issue_row" FROM "issue"
1235 WHERE "id" = "initiative_row"."issue_id";
1236 IF EXISTS (
1237 SELECT NULL FROM "draft"
1238 WHERE "initiative_id" = NEW."initiative_id"
1239 AND "id" != NEW."id"
1240 ) THEN
1241 "event_v" := 'new_draft_created';
1242 ELSE
1243 IF EXISTS (
1244 SELECT NULL FROM "initiative"
1245 WHERE "issue_id" = "initiative_row"."issue_id"
1246 AND "id" != "initiative_row"."id"
1247 ) THEN
1248 "event_v" := 'initiative_created_in_existing_issue';
1249 ELSE
1250 "event_v" := 'initiative_created_in_new_issue';
1251 END IF;
1252 END IF;
1253 INSERT INTO "event" (
1254 "event", "member_id",
1255 "issue_id", "state", "initiative_id", "draft_id"
1256 ) VALUES (
1257 "event_v",
1258 NEW."author_id",
1259 "initiative_row"."issue_id",
1260 "issue_row"."state",
1261 "initiative_row"."id",
1262 NEW."id" );
1263 RETURN NULL;
1264 END;
1265 $$;
1267 CREATE TRIGGER "write_event_initiative_or_draft_created"
1268 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
1269 "write_event_initiative_or_draft_created_trigger"();
1271 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
1272 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
1275 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
1276 RETURNS TRIGGER
1277 LANGUAGE 'plpgsql' VOLATILE AS $$
1278 DECLARE
1279 "issue_row" "issue"%ROWTYPE;
1280 BEGIN
1281 SELECT * INTO "issue_row" FROM "issue"
1282 WHERE "id" = NEW."issue_id";
1283 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1284 INSERT INTO "event" (
1285 "event", "member_id", "issue_id", "state", "initiative_id"
1286 ) VALUES (
1287 'initiative_revoked',
1288 NEW."revoked_by_member_id",
1289 NEW."issue_id",
1290 "issue_row"."state",
1291 NEW."id" );
1292 END IF;
1293 RETURN NULL;
1294 END;
1295 $$;
1297 CREATE TRIGGER "write_event_initiative_revoked"
1298 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
1299 "write_event_initiative_revoked_trigger"();
1301 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
1302 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
1305 CREATE FUNCTION "write_event_suggestion_created_trigger"()
1306 RETURNS TRIGGER
1307 LANGUAGE 'plpgsql' VOLATILE AS $$
1308 DECLARE
1309 "initiative_row" "initiative"%ROWTYPE;
1310 "issue_row" "issue"%ROWTYPE;
1311 BEGIN
1312 SELECT * INTO "initiative_row" FROM "initiative"
1313 WHERE "id" = NEW."initiative_id";
1314 SELECT * INTO "issue_row" FROM "issue"
1315 WHERE "id" = "initiative_row"."issue_id";
1316 INSERT INTO "event" (
1317 "event", "member_id",
1318 "issue_id", "state", "initiative_id", "suggestion_id"
1319 ) VALUES (
1320 'suggestion_created',
1321 NEW."author_id",
1322 "initiative_row"."issue_id",
1323 "issue_row"."state",
1324 "initiative_row"."id",
1325 NEW."id" );
1326 RETURN NULL;
1327 END;
1328 $$;
1330 CREATE TRIGGER "write_event_suggestion_created"
1331 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1332 "write_event_suggestion_created_trigger"();
1334 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
1335 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1339 ----------------------------
1340 -- Additional constraints --
1341 ----------------------------
1344 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
1345 RETURNS TRIGGER
1346 LANGUAGE 'plpgsql' VOLATILE AS $$
1347 BEGIN
1348 IF NOT EXISTS (
1349 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
1350 ) THEN
1351 --RAISE 'Cannot create issue without an initial initiative.' USING
1352 -- ERRCODE = 'integrity_constraint_violation',
1353 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
1354 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
1355 END IF;
1356 RETURN NULL;
1357 END;
1358 $$;
1360 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
1361 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
1362 FOR EACH ROW EXECUTE PROCEDURE
1363 "issue_requires_first_initiative_trigger"();
1365 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
1366 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
1369 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
1370 RETURNS TRIGGER
1371 LANGUAGE 'plpgsql' VOLATILE AS $$
1372 DECLARE
1373 "reference_lost" BOOLEAN;
1374 BEGIN
1375 IF TG_OP = 'DELETE' THEN
1376 "reference_lost" := TRUE;
1377 ELSE
1378 "reference_lost" := NEW."issue_id" != OLD."issue_id";
1379 END IF;
1380 IF
1381 "reference_lost" AND NOT EXISTS (
1382 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
1383 )
1384 THEN
1385 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
1386 END IF;
1387 RETURN NULL;
1388 END;
1389 $$;
1391 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
1392 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1393 FOR EACH ROW EXECUTE PROCEDURE
1394 "last_initiative_deletes_issue_trigger"();
1396 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
1397 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
1400 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
1401 RETURNS TRIGGER
1402 LANGUAGE 'plpgsql' VOLATILE AS $$
1403 BEGIN
1404 IF NOT EXISTS (
1405 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
1406 ) THEN
1407 --RAISE 'Cannot create initiative without an initial draft.' USING
1408 -- ERRCODE = 'integrity_constraint_violation',
1409 -- HINT = 'Create issue, initiative and draft within the same transaction.';
1410 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
1411 END IF;
1412 RETURN NULL;
1413 END;
1414 $$;
1416 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
1417 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1418 FOR EACH ROW EXECUTE PROCEDURE
1419 "initiative_requires_first_draft_trigger"();
1421 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
1422 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
1425 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
1426 RETURNS TRIGGER
1427 LANGUAGE 'plpgsql' VOLATILE AS $$
1428 DECLARE
1429 "reference_lost" BOOLEAN;
1430 BEGIN
1431 IF TG_OP = 'DELETE' THEN
1432 "reference_lost" := TRUE;
1433 ELSE
1434 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
1435 END IF;
1436 IF
1437 "reference_lost" AND NOT EXISTS (
1438 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
1439 )
1440 THEN
1441 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
1442 END IF;
1443 RETURN NULL;
1444 END;
1445 $$;
1447 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
1448 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
1449 FOR EACH ROW EXECUTE PROCEDURE
1450 "last_draft_deletes_initiative_trigger"();
1452 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
1453 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
1456 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
1457 RETURNS TRIGGER
1458 LANGUAGE 'plpgsql' VOLATILE AS $$
1459 BEGIN
1460 IF NOT EXISTS (
1461 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
1462 ) THEN
1463 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
1464 END IF;
1465 RETURN NULL;
1466 END;
1467 $$;
1469 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
1470 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
1471 FOR EACH ROW EXECUTE PROCEDURE
1472 "suggestion_requires_first_opinion_trigger"();
1474 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1475 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1478 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1479 RETURNS TRIGGER
1480 LANGUAGE 'plpgsql' VOLATILE AS $$
1481 DECLARE
1482 "reference_lost" BOOLEAN;
1483 BEGIN
1484 IF TG_OP = 'DELETE' THEN
1485 "reference_lost" := TRUE;
1486 ELSE
1487 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1488 END IF;
1489 IF
1490 "reference_lost" AND NOT EXISTS (
1491 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1492 )
1493 THEN
1494 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1495 END IF;
1496 RETURN NULL;
1497 END;
1498 $$;
1500 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1501 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1502 FOR EACH ROW EXECUTE PROCEDURE
1503 "last_opinion_deletes_suggestion_trigger"();
1505 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1506 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1510 ---------------------------------------------------------------
1511 -- Ensure that votes are not modified when issues are frozen --
1512 ---------------------------------------------------------------
1514 -- NOTE: Frontends should ensure this anyway, but in case of programming
1515 -- errors the following triggers ensure data integrity.
1518 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1519 RETURNS TRIGGER
1520 LANGUAGE 'plpgsql' VOLATILE AS $$
1521 DECLARE
1522 "issue_id_v" "issue"."id"%TYPE;
1523 "issue_row" "issue"%ROWTYPE;
1524 BEGIN
1525 IF TG_OP = 'DELETE' THEN
1526 "issue_id_v" := OLD."issue_id";
1527 ELSE
1528 "issue_id_v" := NEW."issue_id";
1529 END IF;
1530 SELECT INTO "issue_row" * FROM "issue"
1531 WHERE "id" = "issue_id_v" FOR SHARE;
1532 IF "issue_row"."closed" NOTNULL THEN
1533 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
1534 END IF;
1535 RETURN NULL;
1536 END;
1537 $$;
1539 CREATE TRIGGER "forbid_changes_on_closed_issue"
1540 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1541 FOR EACH ROW EXECUTE PROCEDURE
1542 "forbid_changes_on_closed_issue_trigger"();
1544 CREATE TRIGGER "forbid_changes_on_closed_issue"
1545 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1546 FOR EACH ROW EXECUTE PROCEDURE
1547 "forbid_changes_on_closed_issue_trigger"();
1549 CREATE TRIGGER "forbid_changes_on_closed_issue"
1550 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1551 FOR EACH ROW EXECUTE PROCEDURE
1552 "forbid_changes_on_closed_issue_trigger"();
1554 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"';
1555 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';
1556 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';
1557 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';
1561 --------------------------------------------------------------------
1562 -- Auto-retrieval of fields only needed for referential integrity --
1563 --------------------------------------------------------------------
1566 CREATE FUNCTION "autofill_issue_id_trigger"()
1567 RETURNS TRIGGER
1568 LANGUAGE 'plpgsql' VOLATILE AS $$
1569 BEGIN
1570 IF NEW."issue_id" ISNULL THEN
1571 SELECT "issue_id" INTO NEW."issue_id"
1572 FROM "initiative" WHERE "id" = NEW."initiative_id";
1573 END IF;
1574 RETURN NEW;
1575 END;
1576 $$;
1578 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1579 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1581 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1582 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1584 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1585 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1586 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1589 CREATE FUNCTION "autofill_initiative_id_trigger"()
1590 RETURNS TRIGGER
1591 LANGUAGE 'plpgsql' VOLATILE AS $$
1592 BEGIN
1593 IF NEW."initiative_id" ISNULL THEN
1594 SELECT "initiative_id" INTO NEW."initiative_id"
1595 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1596 END IF;
1597 RETURN NEW;
1598 END;
1599 $$;
1601 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1602 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1604 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1605 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1609 -----------------------------------------------------
1610 -- Automatic calculation of certain default values --
1611 -----------------------------------------------------
1614 CREATE FUNCTION "copy_timings_trigger"()
1615 RETURNS TRIGGER
1616 LANGUAGE 'plpgsql' VOLATILE AS $$
1617 DECLARE
1618 "policy_row" "policy"%ROWTYPE;
1619 BEGIN
1620 SELECT * INTO "policy_row" FROM "policy"
1621 WHERE "id" = NEW."policy_id";
1622 IF NEW."admission_time" ISNULL THEN
1623 NEW."admission_time" := "policy_row"."admission_time";
1624 END IF;
1625 IF NEW."discussion_time" ISNULL THEN
1626 NEW."discussion_time" := "policy_row"."discussion_time";
1627 END IF;
1628 IF NEW."verification_time" ISNULL THEN
1629 NEW."verification_time" := "policy_row"."verification_time";
1630 END IF;
1631 IF NEW."voting_time" ISNULL THEN
1632 NEW."voting_time" := "policy_row"."voting_time";
1633 END IF;
1634 RETURN NEW;
1635 END;
1636 $$;
1638 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1639 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1641 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1642 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1645 CREATE FUNCTION "default_for_draft_id_trigger"()
1646 RETURNS TRIGGER
1647 LANGUAGE 'plpgsql' VOLATILE AS $$
1648 BEGIN
1649 IF NEW."draft_id" ISNULL THEN
1650 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1651 WHERE "initiative_id" = NEW."initiative_id";
1652 END IF;
1653 RETURN NEW;
1654 END;
1655 $$;
1657 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"
1658 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1659 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1660 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1662 COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';
1663 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';
1664 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';
1668 ----------------------------------------
1669 -- Automatic creation of dependencies --
1670 ----------------------------------------
1673 CREATE FUNCTION "autocreate_interest_trigger"()
1674 RETURNS TRIGGER
1675 LANGUAGE 'plpgsql' VOLATILE AS $$
1676 BEGIN
1677 IF NOT EXISTS (
1678 SELECT NULL FROM "initiative" JOIN "interest"
1679 ON "initiative"."issue_id" = "interest"."issue_id"
1680 WHERE "initiative"."id" = NEW."initiative_id"
1681 AND "interest"."member_id" = NEW."member_id"
1682 ) THEN
1683 BEGIN
1684 INSERT INTO "interest" ("issue_id", "member_id")
1685 SELECT "issue_id", NEW."member_id"
1686 FROM "initiative" WHERE "id" = NEW."initiative_id";
1687 EXCEPTION WHEN unique_violation THEN END;
1688 END IF;
1689 RETURN NEW;
1690 END;
1691 $$;
1693 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1694 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1696 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1697 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';
1700 CREATE FUNCTION "autocreate_supporter_trigger"()
1701 RETURNS TRIGGER
1702 LANGUAGE 'plpgsql' VOLATILE AS $$
1703 BEGIN
1704 IF NOT EXISTS (
1705 SELECT NULL FROM "suggestion" JOIN "supporter"
1706 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1707 WHERE "suggestion"."id" = NEW."suggestion_id"
1708 AND "supporter"."member_id" = NEW."member_id"
1709 ) THEN
1710 BEGIN
1711 INSERT INTO "supporter" ("initiative_id", "member_id")
1712 SELECT "initiative_id", NEW."member_id"
1713 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1714 EXCEPTION WHEN unique_violation THEN END;
1715 END IF;
1716 RETURN NEW;
1717 END;
1718 $$;
1720 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1721 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1723 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1724 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.';
1728 ------------------------------------------
1729 -- Views and helper functions for views --
1730 ------------------------------------------
1733 CREATE VIEW "unit_delegation" AS
1734 SELECT
1735 "unit"."id" AS "unit_id",
1736 "delegation"."id",
1737 "delegation"."truster_id",
1738 "delegation"."trustee_id",
1739 "delegation"."scope"
1740 FROM "unit"
1741 JOIN "delegation"
1742 ON "delegation"."unit_id" = "unit"."id"
1743 JOIN "member"
1744 ON "delegation"."truster_id" = "member"."id"
1745 JOIN "privilege"
1746 ON "delegation"."unit_id" = "privilege"."unit_id"
1747 AND "delegation"."truster_id" = "privilege"."member_id"
1748 WHERE "member"."active" AND "privilege"."voting_right";
1750 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
1753 CREATE VIEW "area_delegation" AS
1754 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1755 "area"."id" AS "area_id",
1756 "delegation"."id",
1757 "delegation"."truster_id",
1758 "delegation"."trustee_id",
1759 "delegation"."scope"
1760 FROM "area"
1761 JOIN "delegation"
1762 ON "delegation"."unit_id" = "area"."unit_id"
1763 OR "delegation"."area_id" = "area"."id"
1764 JOIN "member"
1765 ON "delegation"."truster_id" = "member"."id"
1766 JOIN "privilege"
1767 ON "area"."unit_id" = "privilege"."unit_id"
1768 AND "delegation"."truster_id" = "privilege"."member_id"
1769 WHERE "member"."active" AND "privilege"."voting_right"
1770 ORDER BY
1771 "area"."id",
1772 "delegation"."truster_id",
1773 "delegation"."scope" DESC;
1775 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
1778 CREATE VIEW "issue_delegation" AS
1779 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1780 "issue"."id" AS "issue_id",
1781 "delegation"."id",
1782 "delegation"."truster_id",
1783 "delegation"."trustee_id",
1784 "delegation"."scope"
1785 FROM "issue"
1786 JOIN "area"
1787 ON "area"."id" = "issue"."area_id"
1788 JOIN "delegation"
1789 ON "delegation"."unit_id" = "area"."unit_id"
1790 OR "delegation"."area_id" = "area"."id"
1791 OR "delegation"."issue_id" = "issue"."id"
1792 JOIN "member"
1793 ON "delegation"."truster_id" = "member"."id"
1794 JOIN "privilege"
1795 ON "area"."unit_id" = "privilege"."unit_id"
1796 AND "delegation"."truster_id" = "privilege"."member_id"
1797 WHERE "member"."active" AND "privilege"."voting_right"
1798 ORDER BY
1799 "issue"."id",
1800 "delegation"."truster_id",
1801 "delegation"."scope" DESC;
1803 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
1806 CREATE FUNCTION "membership_weight_with_skipping"
1807 ( "area_id_p" "area"."id"%TYPE,
1808 "member_id_p" "member"."id"%TYPE,
1809 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1810 RETURNS INT4
1811 LANGUAGE 'plpgsql' STABLE AS $$
1812 DECLARE
1813 "sum_v" INT4;
1814 "delegation_row" "area_delegation"%ROWTYPE;
1815 BEGIN
1816 "sum_v" := 1;
1817 FOR "delegation_row" IN
1818 SELECT "area_delegation".*
1819 FROM "area_delegation" LEFT JOIN "membership"
1820 ON "membership"."area_id" = "area_id_p"
1821 AND "membership"."member_id" = "area_delegation"."truster_id"
1822 WHERE "area_delegation"."area_id" = "area_id_p"
1823 AND "area_delegation"."trustee_id" = "member_id_p"
1824 AND "membership"."member_id" ISNULL
1825 LOOP
1826 IF NOT
1827 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1828 THEN
1829 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1830 "area_id_p",
1831 "delegation_row"."truster_id",
1832 "skip_member_ids_p" || "delegation_row"."truster_id"
1833 );
1834 END IF;
1835 END LOOP;
1836 RETURN "sum_v";
1837 END;
1838 $$;
1840 COMMENT ON FUNCTION "membership_weight_with_skipping"
1841 ( "area"."id"%TYPE,
1842 "member"."id"%TYPE,
1843 INT4[] )
1844 IS 'Helper function for "membership_weight" function';
1847 CREATE FUNCTION "membership_weight"
1848 ( "area_id_p" "area"."id"%TYPE,
1849 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1850 RETURNS INT4
1851 LANGUAGE 'plpgsql' STABLE AS $$
1852 BEGIN
1853 RETURN "membership_weight_with_skipping"(
1854 "area_id_p",
1855 "member_id_p",
1856 ARRAY["member_id_p"]
1857 );
1858 END;
1859 $$;
1861 COMMENT ON FUNCTION "membership_weight"
1862 ( "area"."id"%TYPE,
1863 "member"."id"%TYPE )
1864 IS 'Calculates the potential voting weight of a member in a given area';
1867 CREATE VIEW "member_count_view" AS
1868 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1870 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1873 CREATE VIEW "unit_member_count" AS
1874 SELECT
1875 "unit"."id" AS "unit_id",
1876 sum("member"."id") AS "member_count"
1877 FROM "unit"
1878 LEFT JOIN "privilege"
1879 ON "privilege"."unit_id" = "unit"."id"
1880 AND "privilege"."voting_right"
1881 LEFT JOIN "member"
1882 ON "member"."id" = "privilege"."member_id"
1883 AND "member"."active"
1884 GROUP BY "unit"."id";
1886 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
1889 CREATE VIEW "area_member_count" AS
1890 SELECT
1891 "area"."id" AS "area_id",
1892 count("member"."id") AS "direct_member_count",
1893 coalesce(
1894 sum(
1895 CASE WHEN "member"."id" NOTNULL THEN
1896 "membership_weight"("area"."id", "member"."id")
1897 ELSE 0 END
1898 )
1899 ) AS "member_weight"
1900 FROM "area"
1901 LEFT JOIN "membership"
1902 ON "area"."id" = "membership"."area_id"
1903 LEFT JOIN "privilege"
1904 ON "privilege"."unit_id" = "area"."unit_id"
1905 AND "privilege"."member_id" = "membership"."member_id"
1906 AND "privilege"."voting_right"
1907 LEFT JOIN "member"
1908 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
1909 AND "member"."active"
1910 GROUP BY "area"."id";
1912 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"';
1915 CREATE VIEW "opening_draft" AS
1916 SELECT "draft".* FROM (
1917 SELECT
1918 "initiative"."id" AS "initiative_id",
1919 min("draft"."id") AS "draft_id"
1920 FROM "initiative" JOIN "draft"
1921 ON "initiative"."id" = "draft"."initiative_id"
1922 GROUP BY "initiative"."id"
1923 ) AS "subquery"
1924 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1926 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1929 CREATE VIEW "current_draft" AS
1930 SELECT "draft".* FROM (
1931 SELECT
1932 "initiative"."id" AS "initiative_id",
1933 max("draft"."id") AS "draft_id"
1934 FROM "initiative" JOIN "draft"
1935 ON "initiative"."id" = "draft"."initiative_id"
1936 GROUP BY "initiative"."id"
1937 ) AS "subquery"
1938 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1940 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1943 CREATE VIEW "critical_opinion" AS
1944 SELECT * FROM "opinion"
1945 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
1946 OR ("degree" = -2 AND "fulfilled" = TRUE);
1948 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
1951 CREATE VIEW "battle_participant" AS
1952 SELECT "initiative"."id", "initiative"."issue_id"
1953 FROM "issue" JOIN "initiative"
1954 ON "issue"."id" = "initiative"."issue_id"
1955 WHERE "initiative"."admitted"
1956 UNION ALL
1957 SELECT NULL, "id" AS "issue_id"
1958 FROM "issue";
1960 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
1963 CREATE VIEW "battle_view" AS
1964 SELECT
1965 "issue"."id" AS "issue_id",
1966 "winning_initiative"."id" AS "winning_initiative_id",
1967 "losing_initiative"."id" AS "losing_initiative_id",
1968 sum(
1969 CASE WHEN
1970 coalesce("better_vote"."grade", 0) >
1971 coalesce("worse_vote"."grade", 0)
1972 THEN "direct_voter"."weight" ELSE 0 END
1973 ) AS "count"
1974 FROM "issue"
1975 LEFT JOIN "direct_voter"
1976 ON "issue"."id" = "direct_voter"."issue_id"
1977 JOIN "battle_participant" AS "winning_initiative"
1978 ON "issue"."id" = "winning_initiative"."issue_id"
1979 JOIN "battle_participant" AS "losing_initiative"
1980 ON "issue"."id" = "losing_initiative"."issue_id"
1981 LEFT JOIN "vote" AS "better_vote"
1982 ON "direct_voter"."member_id" = "better_vote"."member_id"
1983 AND "winning_initiative"."id" = "better_vote"."initiative_id"
1984 LEFT JOIN "vote" AS "worse_vote"
1985 ON "direct_voter"."member_id" = "worse_vote"."member_id"
1986 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
1987 WHERE "issue"."closed" NOTNULL
1988 AND "issue"."cleaned" ISNULL
1989 AND (
1990 "winning_initiative"."id" != "losing_initiative"."id" OR
1991 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
1992 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
1993 GROUP BY
1994 "issue"."id",
1995 "winning_initiative"."id",
1996 "losing_initiative"."id";
1998 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';
2001 CREATE VIEW "open_issue" AS
2002 SELECT * FROM "issue" WHERE "closed" ISNULL;
2004 COMMENT ON VIEW "open_issue" IS 'All open issues';
2007 CREATE VIEW "issue_with_ranks_missing" AS
2008 SELECT * FROM "issue"
2009 WHERE "fully_frozen" NOTNULL
2010 AND "closed" NOTNULL
2011 AND "ranks_available" = FALSE;
2013 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
2016 CREATE VIEW "member_contingent" AS
2017 SELECT
2018 "member"."id" AS "member_id",
2019 "contingent"."time_frame",
2020 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
2021 (
2022 SELECT count(1) FROM "draft"
2023 WHERE "draft"."author_id" = "member"."id"
2024 AND "draft"."created" > now() - "contingent"."time_frame"
2025 ) + (
2026 SELECT count(1) FROM "suggestion"
2027 WHERE "suggestion"."author_id" = "member"."id"
2028 AND "suggestion"."created" > now() - "contingent"."time_frame"
2029 )
2030 ELSE NULL END AS "text_entry_count",
2031 "contingent"."text_entry_limit",
2032 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
2033 SELECT count(1) FROM "opening_draft"
2034 WHERE "opening_draft"."author_id" = "member"."id"
2035 AND "opening_draft"."created" > now() - "contingent"."time_frame"
2036 ) ELSE NULL END AS "initiative_count",
2037 "contingent"."initiative_limit"
2038 FROM "member" CROSS JOIN "contingent";
2040 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
2042 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
2043 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
2046 CREATE VIEW "member_contingent_left" AS
2047 SELECT
2048 "member_id",
2049 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
2050 max("initiative_limit" - "initiative_count") AS "initiatives_left"
2051 FROM "member_contingent" GROUP BY "member_id";
2053 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.';
2056 CREATE VIEW "event_seen_by_member" AS
2057 SELECT
2058 "member"."id" AS "seen_by_member_id",
2059 CASE WHEN "event"."state" IN (
2060 'voting',
2061 'finished_without_winner',
2062 'finished_with_winner'
2063 ) THEN
2064 'voting'::"notify_level"
2065 ELSE
2066 CASE WHEN "event"."state" IN (
2067 'verification',
2068 'canceled_after_revocation_during_verification',
2069 'canceled_no_initiative_admitted'
2070 ) THEN
2071 'verification'::"notify_level"
2072 ELSE
2073 CASE WHEN "event"."state" IN (
2074 'discussion',
2075 'canceled_after_revocation_during_discussion'
2076 ) THEN
2077 'discussion'::"notify_level"
2078 ELSE
2079 'all'::"notify_level"
2080 END
2081 END
2082 END AS "notify_level",
2083 "event".*
2084 FROM "member" CROSS JOIN "event"
2085 LEFT JOIN "issue"
2086 ON "event"."issue_id" = "issue"."id"
2087 LEFT JOIN "membership"
2088 ON "member"."id" = "membership"."member_id"
2089 AND "issue"."area_id" = "membership"."area_id"
2090 LEFT JOIN "interest"
2091 ON "member"."id" = "interest"."member_id"
2092 AND "event"."issue_id" = "interest"."issue_id"
2093 LEFT JOIN "supporter"
2094 ON "member"."id" = "supporter"."member_id"
2095 AND "event"."initiative_id" = "supporter"."initiative_id"
2096 LEFT JOIN "ignored_member"
2097 ON "member"."id" = "ignored_member"."member_id"
2098 AND "event"."member_id" = "ignored_member"."other_member_id"
2099 LEFT JOIN "ignored_initiative"
2100 ON "member"."id" = "ignored_initiative"."member_id"
2101 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2102 WHERE (
2103 "supporter"."member_id" NOTNULL OR
2104 "interest"."member_id" NOTNULL OR
2105 ( "membership"."member_id" NOTNULL AND
2106 "event"."event" IN (
2107 'issue_state_changed',
2108 'initiative_created_in_new_issue',
2109 'initiative_created_in_existing_issue',
2110 'initiative_revoked' ) ) )
2111 AND "ignored_member"."member_id" ISNULL
2112 AND "ignored_initiative"."member_id" ISNULL;
2114 COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support';
2117 CREATE VIEW "pending_notification" AS
2118 SELECT
2119 "member"."id" AS "seen_by_member_id",
2120 "event".*
2121 FROM "member" CROSS JOIN "event"
2122 LEFT JOIN "issue"
2123 ON "event"."issue_id" = "issue"."id"
2124 LEFT JOIN "membership"
2125 ON "member"."id" = "membership"."member_id"
2126 AND "issue"."area_id" = "membership"."area_id"
2127 LEFT JOIN "interest"
2128 ON "member"."id" = "interest"."member_id"
2129 AND "event"."issue_id" = "interest"."issue_id"
2130 LEFT JOIN "supporter"
2131 ON "member"."id" = "supporter"."member_id"
2132 AND "event"."initiative_id" = "supporter"."initiative_id"
2133 LEFT JOIN "ignored_member"
2134 ON "member"."id" = "ignored_member"."member_id"
2135 AND "event"."member_id" = "ignored_member"."other_member_id"
2136 LEFT JOIN "ignored_initiative"
2137 ON "member"."id" = "ignored_initiative"."member_id"
2138 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2139 WHERE (
2140 "member"."notify_event_id" ISNULL OR
2141 ( "member"."notify_event_id" NOTNULL AND
2142 "member"."notify_event_id" < "event"."id" ) )
2143 AND (
2144 ( "member"."notify_level" >= 'all' ) OR
2145 ( "member"."notify_level" >= 'voting' AND
2146 "event"."state" IN (
2147 'voting',
2148 'finished_without_winner',
2149 'finished_with_winner' ) ) OR
2150 ( "member"."notify_level" >= 'verification' AND
2151 "event"."state" IN (
2152 'verification',
2153 'canceled_after_revocation_during_verification',
2154 'canceled_no_initiative_admitted' ) ) OR
2155 ( "member"."notify_level" >= 'discussion' AND
2156 "event"."state" IN (
2157 'discussion',
2158 'canceled_after_revocation_during_discussion' ) ) )
2159 AND (
2160 "supporter"."member_id" NOTNULL OR
2161 "interest"."member_id" NOTNULL OR
2162 ( "membership"."member_id" NOTNULL AND
2163 "event"."event" IN (
2164 'issue_state_changed',
2165 'initiative_created_in_new_issue',
2166 'initiative_created_in_existing_issue',
2167 'initiative_revoked' ) ) )
2168 AND "ignored_member"."member_id" ISNULL
2169 AND "ignored_initiative"."member_id" ISNULL;
2171 COMMENT ON VIEW "pending_notification" IS 'Events to be sent to "notify_email" address of member referred to by "seen_by_member_id"';
2174 CREATE TYPE "timeline_event" AS ENUM (
2175 'issue_created',
2176 'issue_canceled',
2177 'issue_accepted',
2178 'issue_half_frozen',
2179 'issue_finished_without_voting',
2180 'issue_voting_started',
2181 'issue_finished_after_voting',
2182 'initiative_created',
2183 'initiative_revoked',
2184 'draft_created',
2185 'suggestion_created');
2187 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)';
2190 CREATE VIEW "timeline_issue" AS
2191 SELECT
2192 "created" AS "occurrence",
2193 'issue_created'::"timeline_event" AS "event",
2194 "id" AS "issue_id"
2195 FROM "issue"
2196 UNION ALL
2197 SELECT
2198 "closed" AS "occurrence",
2199 'issue_canceled'::"timeline_event" AS "event",
2200 "id" AS "issue_id"
2201 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
2202 UNION ALL
2203 SELECT
2204 "accepted" AS "occurrence",
2205 'issue_accepted'::"timeline_event" AS "event",
2206 "id" AS "issue_id"
2207 FROM "issue" WHERE "accepted" NOTNULL
2208 UNION ALL
2209 SELECT
2210 "half_frozen" AS "occurrence",
2211 'issue_half_frozen'::"timeline_event" AS "event",
2212 "id" AS "issue_id"
2213 FROM "issue" WHERE "half_frozen" NOTNULL
2214 UNION ALL
2215 SELECT
2216 "fully_frozen" AS "occurrence",
2217 'issue_voting_started'::"timeline_event" AS "event",
2218 "id" AS "issue_id"
2219 FROM "issue"
2220 WHERE "fully_frozen" NOTNULL
2221 AND ("closed" ISNULL OR "closed" != "fully_frozen")
2222 UNION ALL
2223 SELECT
2224 "closed" AS "occurrence",
2225 CASE WHEN "fully_frozen" = "closed" THEN
2226 'issue_finished_without_voting'::"timeline_event"
2227 ELSE
2228 'issue_finished_after_voting'::"timeline_event"
2229 END AS "event",
2230 "id" AS "issue_id"
2231 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
2233 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)';
2236 CREATE VIEW "timeline_initiative" AS
2237 SELECT
2238 "created" AS "occurrence",
2239 'initiative_created'::"timeline_event" AS "event",
2240 "id" AS "initiative_id"
2241 FROM "initiative"
2242 UNION ALL
2243 SELECT
2244 "revoked" AS "occurrence",
2245 'initiative_revoked'::"timeline_event" AS "event",
2246 "id" AS "initiative_id"
2247 FROM "initiative" WHERE "revoked" NOTNULL;
2249 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)';
2252 CREATE VIEW "timeline_draft" AS
2253 SELECT
2254 "created" AS "occurrence",
2255 'draft_created'::"timeline_event" AS "event",
2256 "id" AS "draft_id"
2257 FROM "draft";
2259 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)';
2262 CREATE VIEW "timeline_suggestion" AS
2263 SELECT
2264 "created" AS "occurrence",
2265 'suggestion_created'::"timeline_event" AS "event",
2266 "id" AS "suggestion_id"
2267 FROM "suggestion";
2269 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)';
2272 CREATE VIEW "timeline" AS
2273 SELECT
2274 "occurrence",
2275 "event",
2276 "issue_id",
2277 NULL AS "initiative_id",
2278 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
2279 NULL::INT8 AS "suggestion_id"
2280 FROM "timeline_issue"
2281 UNION ALL
2282 SELECT
2283 "occurrence",
2284 "event",
2285 NULL AS "issue_id",
2286 "initiative_id",
2287 NULL AS "draft_id",
2288 NULL AS "suggestion_id"
2289 FROM "timeline_initiative"
2290 UNION ALL
2291 SELECT
2292 "occurrence",
2293 "event",
2294 NULL AS "issue_id",
2295 NULL AS "initiative_id",
2296 "draft_id",
2297 NULL AS "suggestion_id"
2298 FROM "timeline_draft"
2299 UNION ALL
2300 SELECT
2301 "occurrence",
2302 "event",
2303 NULL AS "issue_id",
2304 NULL AS "initiative_id",
2305 NULL AS "draft_id",
2306 "suggestion_id"
2307 FROM "timeline_suggestion";
2309 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)';
2313 --------------------------------------------------
2314 -- Set returning function for delegation chains --
2315 --------------------------------------------------
2318 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
2319 ('first', 'intermediate', 'last', 'repetition');
2321 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
2324 CREATE TYPE "delegation_chain_row" AS (
2325 "index" INT4,
2326 "member_id" INT4,
2327 "member_valid" BOOLEAN,
2328 "participation" BOOLEAN,
2329 "overridden" BOOLEAN,
2330 "scope_in" "delegation_scope",
2331 "scope_out" "delegation_scope",
2332 "disabled_out" BOOLEAN,
2333 "loop" "delegation_chain_loop_tag" );
2335 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
2337 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
2338 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';
2339 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
2340 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
2341 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
2342 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
2343 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
2346 CREATE FUNCTION "delegation_chain"
2347 ( "member_id_p" "member"."id"%TYPE,
2348 "unit_id_p" "unit"."id"%TYPE,
2349 "area_id_p" "area"."id"%TYPE,
2350 "issue_id_p" "issue"."id"%TYPE,
2351 "simulate_trustee_id_p" "member"."id"%TYPE )
2352 RETURNS SETOF "delegation_chain_row"
2353 LANGUAGE 'plpgsql' STABLE AS $$
2354 DECLARE
2355 "scope_v" "delegation_scope";
2356 "unit_id_v" "unit"."id"%TYPE;
2357 "area_id_v" "area"."id"%TYPE;
2358 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2359 "loop_member_id_v" "member"."id"%TYPE;
2360 "output_row" "delegation_chain_row";
2361 "output_rows" "delegation_chain_row"[];
2362 "delegation_row" "delegation"%ROWTYPE;
2363 "row_count" INT4;
2364 "i" INT4;
2365 "loop_v" BOOLEAN;
2366 BEGIN
2367 IF
2368 "unit_id_p" NOTNULL AND
2369 "area_id_p" ISNULL AND
2370 "issue_id_p" ISNULL
2371 THEN
2372 "scope_v" := 'unit';
2373 "unit_id_v" := "unit_id_p";
2374 ELSIF
2375 "unit_id_p" ISNULL AND
2376 "area_id_p" NOTNULL AND
2377 "issue_id_p" ISNULL
2378 THEN
2379 "scope_v" := 'area';
2380 "area_id_v" := "area_id_p";
2381 SELECT "unit_id" INTO "unit_id_v"
2382 FROM "area" WHERE "id" = "area_id_v";
2383 ELSIF
2384 "unit_id_p" ISNULL AND
2385 "area_id_p" ISNULL AND
2386 "issue_id_p" NOTNULL
2387 THEN
2388 "scope_v" := 'issue';
2389 SELECT "area_id" INTO "area_id_v"
2390 FROM "issue" WHERE "id" = "issue_id_p";
2391 SELECT "unit_id" INTO "unit_id_v"
2392 FROM "area" WHERE "id" = "area_id_v";
2393 ELSE
2394 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2395 END IF;
2396 "visited_member_ids" := '{}';
2397 "loop_member_id_v" := NULL;
2398 "output_rows" := '{}';
2399 "output_row"."index" := 0;
2400 "output_row"."member_id" := "member_id_p";
2401 "output_row"."member_valid" := TRUE;
2402 "output_row"."participation" := FALSE;
2403 "output_row"."overridden" := FALSE;
2404 "output_row"."disabled_out" := FALSE;
2405 "output_row"."scope_out" := NULL;
2406 LOOP
2407 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2408 "loop_member_id_v" := "output_row"."member_id";
2409 ELSE
2410 "visited_member_ids" :=
2411 "visited_member_ids" || "output_row"."member_id";
2412 END IF;
2413 IF "output_row"."participation" THEN
2414 "output_row"."overridden" := TRUE;
2415 END IF;
2416 "output_row"."scope_in" := "output_row"."scope_out";
2417 IF EXISTS (
2418 SELECT NULL FROM "member" JOIN "privilege"
2419 ON "privilege"."member_id" = "member"."id"
2420 AND "privilege"."unit_id" = "unit_id_v"
2421 WHERE "id" = "output_row"."member_id"
2422 AND "member"."active" AND "privilege"."voting_right"
2423 ) THEN
2424 IF "scope_v" = 'unit' THEN
2425 SELECT * INTO "delegation_row" FROM "delegation"
2426 WHERE "truster_id" = "output_row"."member_id"
2427 AND "unit_id" = "unit_id_v";
2428 ELSIF "scope_v" = 'area' THEN
2429 "output_row"."participation" := EXISTS (
2430 SELECT NULL FROM "membership"
2431 WHERE "area_id" = "area_id_p"
2432 AND "member_id" = "output_row"."member_id"
2433 );
2434 SELECT * INTO "delegation_row" FROM "delegation"
2435 WHERE "truster_id" = "output_row"."member_id"
2436 AND (
2437 "unit_id" = "unit_id_v" OR
2438 "area_id" = "area_id_v"
2439 )
2440 ORDER BY "scope" DESC;
2441 ELSIF "scope_v" = 'issue' THEN
2442 "output_row"."participation" := EXISTS (
2443 SELECT NULL FROM "interest"
2444 WHERE "issue_id" = "issue_id_p"
2445 AND "member_id" = "output_row"."member_id"
2446 );
2447 SELECT * INTO "delegation_row" FROM "delegation"
2448 WHERE "truster_id" = "output_row"."member_id"
2449 AND (
2450 "unit_id" = "unit_id_v" OR
2451 "area_id" = "area_id_v" OR
2452 "issue_id" = "issue_id_p"
2453 )
2454 ORDER BY "scope" DESC;
2455 END IF;
2456 ELSE
2457 "output_row"."member_valid" := FALSE;
2458 "output_row"."participation" := FALSE;
2459 "output_row"."scope_out" := NULL;
2460 "delegation_row" := ROW(NULL);
2461 END IF;
2462 IF
2463 "output_row"."member_id" = "member_id_p" AND
2464 "simulate_trustee_id_p" NOTNULL
2465 THEN
2466 "output_row"."scope_out" := "scope_v";
2467 "output_rows" := "output_rows" || "output_row";
2468 "output_row"."member_id" := "simulate_trustee_id_p";
2469 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2470 "output_row"."scope_out" := "delegation_row"."scope";
2471 "output_rows" := "output_rows" || "output_row";
2472 "output_row"."member_id" := "delegation_row"."trustee_id";
2473 ELSIF "delegation_row"."scope" NOTNULL THEN
2474 "output_row"."scope_out" := "delegation_row"."scope";
2475 "output_row"."disabled_out" := TRUE;
2476 "output_rows" := "output_rows" || "output_row";
2477 EXIT;
2478 ELSE
2479 "output_row"."scope_out" := NULL;
2480 "output_rows" := "output_rows" || "output_row";
2481 EXIT;
2482 END IF;
2483 EXIT WHEN "loop_member_id_v" NOTNULL;
2484 "output_row"."index" := "output_row"."index" + 1;
2485 END LOOP;
2486 "row_count" := array_upper("output_rows", 1);
2487 "i" := 1;
2488 "loop_v" := FALSE;
2489 LOOP
2490 "output_row" := "output_rows"["i"];
2491 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2492 IF "loop_v" THEN
2493 IF "i" + 1 = "row_count" THEN
2494 "output_row"."loop" := 'last';
2495 ELSIF "i" = "row_count" THEN
2496 "output_row"."loop" := 'repetition';
2497 ELSE
2498 "output_row"."loop" := 'intermediate';
2499 END IF;
2500 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2501 "output_row"."loop" := 'first';
2502 "loop_v" := TRUE;
2503 END IF;
2504 IF "scope_v" = 'unit' THEN
2505 "output_row"."participation" := NULL;
2506 END IF;
2507 RETURN NEXT "output_row";
2508 "i" := "i" + 1;
2509 END LOOP;
2510 RETURN;
2511 END;
2512 $$;
2514 COMMENT ON FUNCTION "delegation_chain"
2515 ( "member"."id"%TYPE,
2516 "unit"."id"%TYPE,
2517 "area"."id"%TYPE,
2518 "issue"."id"%TYPE,
2519 "member"."id"%TYPE )
2520 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
2523 CREATE FUNCTION "delegation_chain"
2524 ( "member_id_p" "member"."id"%TYPE,
2525 "unit_id_p" "unit"."id"%TYPE,
2526 "area_id_p" "area"."id"%TYPE,
2527 "issue_id_p" "issue"."id"%TYPE )
2528 RETURNS SETOF "delegation_chain_row"
2529 LANGUAGE 'plpgsql' STABLE AS $$
2530 DECLARE
2531 "result_row" "delegation_chain_row";
2532 BEGIN
2533 FOR "result_row" IN
2534 SELECT * FROM "delegation_chain"(
2535 "member_id_p", "unit_id_p", "area_id_p", "issue_id_p", NULL
2536 )
2537 LOOP
2538 RETURN NEXT "result_row";
2539 END LOOP;
2540 RETURN;
2541 END;
2542 $$;
2544 COMMENT ON FUNCTION "delegation_chain"
2545 ( "member"."id"%TYPE,
2546 "unit"."id"%TYPE,
2547 "area"."id"%TYPE,
2548 "issue"."id"%TYPE )
2549 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
2553 ------------------------------
2554 -- Comparison by vote count --
2555 ------------------------------
2557 CREATE FUNCTION "vote_ratio"
2558 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
2559 "negative_votes_p" "initiative"."negative_votes"%TYPE )
2560 RETURNS FLOAT8
2561 LANGUAGE 'plpgsql' STABLE AS $$
2562 BEGIN
2563 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
2564 RETURN
2565 "positive_votes_p"::FLOAT8 /
2566 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
2567 ELSIF "positive_votes_p" > 0 THEN
2568 RETURN "positive_votes_p";
2569 ELSIF "negative_votes_p" > 0 THEN
2570 RETURN 1 - "negative_votes_p";
2571 ELSE
2572 RETURN 0.5;
2573 END IF;
2574 END;
2575 $$;
2577 COMMENT ON FUNCTION "vote_ratio"
2578 ( "initiative"."positive_votes"%TYPE,
2579 "initiative"."negative_votes"%TYPE )
2580 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.';
2584 ------------------------------------------------
2585 -- Locking for snapshots and voting procedure --
2586 ------------------------------------------------
2589 CREATE FUNCTION "share_row_lock_issue_trigger"()
2590 RETURNS TRIGGER
2591 LANGUAGE 'plpgsql' VOLATILE AS $$
2592 BEGIN
2593 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2594 PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;
2595 END IF;
2596 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2597 PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;
2598 RETURN NEW;
2599 ELSE
2600 RETURN OLD;
2601 END IF;
2602 END;
2603 $$;
2605 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';
2608 CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()
2609 RETURNS TRIGGER
2610 LANGUAGE 'plpgsql' VOLATILE AS $$
2611 BEGIN
2612 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2613 PERFORM NULL FROM "issue"
2614 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2615 WHERE "initiative"."id" = OLD."initiative_id"
2616 FOR SHARE OF "issue";
2617 END IF;
2618 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2619 PERFORM NULL FROM "issue"
2620 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2621 WHERE "initiative"."id" = NEW."initiative_id"
2622 FOR SHARE OF "issue";
2623 RETURN NEW;
2624 ELSE
2625 RETURN OLD;
2626 END IF;
2627 END;
2628 $$;
2630 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';
2633 CREATE TRIGGER "share_row_lock_issue"
2634 BEFORE INSERT OR UPDATE OR DELETE ON "initiative"
2635 FOR EACH ROW EXECUTE PROCEDURE
2636 "share_row_lock_issue_trigger"();
2638 CREATE TRIGGER "share_row_lock_issue"
2639 BEFORE INSERT OR UPDATE OR DELETE ON "interest"
2640 FOR EACH ROW EXECUTE PROCEDURE
2641 "share_row_lock_issue_trigger"();
2643 CREATE TRIGGER "share_row_lock_issue"
2644 BEFORE INSERT OR UPDATE OR DELETE ON "supporter"
2645 FOR EACH ROW EXECUTE PROCEDURE
2646 "share_row_lock_issue_trigger"();
2648 CREATE TRIGGER "share_row_lock_issue_via_initiative"
2649 BEFORE INSERT OR UPDATE OR DELETE ON "opinion"
2650 FOR EACH ROW EXECUTE PROCEDURE
2651 "share_row_lock_issue_via_initiative_trigger"();
2653 CREATE TRIGGER "share_row_lock_issue"
2654 BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter"
2655 FOR EACH ROW EXECUTE PROCEDURE
2656 "share_row_lock_issue_trigger"();
2658 CREATE TRIGGER "share_row_lock_issue"
2659 BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter"
2660 FOR EACH ROW EXECUTE PROCEDURE
2661 "share_row_lock_issue_trigger"();
2663 CREATE TRIGGER "share_row_lock_issue"
2664 BEFORE INSERT OR UPDATE OR DELETE ON "vote"
2665 FOR EACH ROW EXECUTE PROCEDURE
2666 "share_row_lock_issue_trigger"();
2668 COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function';
2669 COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function';
2670 COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function';
2671 COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function';
2672 COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function';
2673 COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function';
2674 COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function';
2677 CREATE FUNCTION "lock_issue"
2678 ( "issue_id_p" "issue"."id"%TYPE )
2679 RETURNS VOID
2680 LANGUAGE 'plpgsql' VOLATILE AS $$
2681 BEGIN
2682 LOCK TABLE "member" IN SHARE MODE;
2683 LOCK TABLE "privilege" IN SHARE MODE;
2684 LOCK TABLE "membership" IN SHARE MODE;
2685 LOCK TABLE "policy" IN SHARE MODE;
2686 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2687 -- NOTE: The row-level exclusive lock in combination with the
2688 -- share_row_lock_issue(_via_initiative)_trigger functions (which
2689 -- acquire a row-level share lock on the issue) ensure that no data
2690 -- is changed, which could affect calculation of snapshots or
2691 -- counting of votes. Table "delegation" must be table-level-locked,
2692 -- as it also contains issue- and global-scope delegations.
2693 LOCK TABLE "delegation" IN SHARE MODE;
2694 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
2695 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
2696 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
2697 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
2698 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
2699 RETURN;
2700 END;
2701 $$;
2703 COMMENT ON FUNCTION "lock_issue"
2704 ( "issue"."id"%TYPE )
2705 IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';
2709 ------------------------------------------------------------------------
2710 -- Regular tasks, except calculcation of snapshots and voting results --
2711 ------------------------------------------------------------------------
2713 CREATE FUNCTION "check_activity"()
2714 RETURNS VOID
2715 LANGUAGE 'plpgsql' VOLATILE AS $$
2716 DECLARE
2717 "system_setting_row" "system_setting"%ROWTYPE;
2718 BEGIN
2719 SELECT * INTO "system_setting_row" FROM "system_setting";
2720 LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
2721 IF "system_setting_row"."member_ttl" NOTNULL THEN
2722 UPDATE "member" SET "active" = FALSE
2723 WHERE "active" = TRUE
2724 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
2725 END IF;
2726 RETURN;
2727 END;
2728 $$;
2730 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
2733 CREATE FUNCTION "calculate_member_counts"()
2734 RETURNS VOID
2735 LANGUAGE 'plpgsql' VOLATILE AS $$
2736 BEGIN
2737 LOCK TABLE "member" IN SHARE MODE;
2738 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
2739 LOCK TABLE "unit" IN EXCLUSIVE MODE;
2740 LOCK TABLE "area" IN EXCLUSIVE MODE;
2741 LOCK TABLE "privilege" IN SHARE MODE;
2742 LOCK TABLE "membership" IN SHARE MODE;
2743 DELETE FROM "member_count";
2744 INSERT INTO "member_count" ("total_count")
2745 SELECT "total_count" FROM "member_count_view";
2746 UPDATE "unit" SET "member_count" = "view"."member_count"
2747 FROM "unit_member_count" AS "view"
2748 WHERE "view"."unit_id" = "unit"."id";
2749 UPDATE "area" SET
2750 "direct_member_count" = "view"."direct_member_count",
2751 "member_weight" = "view"."member_weight"
2752 FROM "area_member_count" AS "view"
2753 WHERE "view"."area_id" = "area"."id";
2754 RETURN;
2755 END;
2756 $$;
2758 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"';
2762 ------------------------------
2763 -- Calculation of snapshots --
2764 ------------------------------
2766 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
2767 ( "issue_id_p" "issue"."id"%TYPE,
2768 "member_id_p" "member"."id"%TYPE,
2769 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2770 RETURNS "direct_population_snapshot"."weight"%TYPE
2771 LANGUAGE 'plpgsql' VOLATILE AS $$
2772 DECLARE
2773 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2774 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
2775 "weight_v" INT4;
2776 "sub_weight_v" INT4;
2777 BEGIN
2778 "weight_v" := 0;
2779 FOR "issue_delegation_row" IN
2780 SELECT * FROM "issue_delegation"
2781 WHERE "trustee_id" = "member_id_p"
2782 AND "issue_id" = "issue_id_p"
2783 LOOP
2784 IF NOT EXISTS (
2785 SELECT NULL FROM "direct_population_snapshot"
2786 WHERE "issue_id" = "issue_id_p"
2787 AND "event" = 'periodic'
2788 AND "member_id" = "issue_delegation_row"."truster_id"
2789 ) AND NOT EXISTS (
2790 SELECT NULL FROM "delegating_population_snapshot"
2791 WHERE "issue_id" = "issue_id_p"
2792 AND "event" = 'periodic'
2793 AND "member_id" = "issue_delegation_row"."truster_id"
2794 ) THEN
2795 "delegate_member_ids_v" :=
2796 "member_id_p" || "delegate_member_ids_p";
2797 INSERT INTO "delegating_population_snapshot" (
2798 "issue_id",
2799 "event",
2800 "member_id",
2801 "scope",
2802 "delegate_member_ids"
2803 ) VALUES (
2804 "issue_id_p",
2805 'periodic',
2806 "issue_delegation_row"."truster_id",
2807 "issue_delegation_row"."scope",
2808 "delegate_member_ids_v"
2809 );
2810 "sub_weight_v" := 1 +
2811 "weight_of_added_delegations_for_population_snapshot"(
2812 "issue_id_p",
2813 "issue_delegation_row"."truster_id",
2814 "delegate_member_ids_v"
2815 );
2816 UPDATE "delegating_population_snapshot"
2817 SET "weight" = "sub_weight_v"
2818 WHERE "issue_id" = "issue_id_p"
2819 AND "event" = 'periodic'
2820 AND "member_id" = "issue_delegation_row"."truster_id";
2821 "weight_v" := "weight_v" + "sub_weight_v";
2822 END IF;
2823 END LOOP;
2824 RETURN "weight_v";
2825 END;
2826 $$;
2828 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
2829 ( "issue"."id"%TYPE,
2830 "member"."id"%TYPE,
2831 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2832 IS 'Helper function for "create_population_snapshot" function';
2835 CREATE FUNCTION "create_population_snapshot"
2836 ( "issue_id_p" "issue"."id"%TYPE )
2837 RETURNS VOID
2838 LANGUAGE 'plpgsql' VOLATILE AS $$
2839 DECLARE
2840 "member_id_v" "member"."id"%TYPE;
2841 BEGIN
2842 DELETE FROM "direct_population_snapshot"
2843 WHERE "issue_id" = "issue_id_p"
2844 AND "event" = 'periodic';
2845 DELETE FROM "delegating_population_snapshot"
2846 WHERE "issue_id" = "issue_id_p"
2847 AND "event" = 'periodic';
2848 INSERT INTO "direct_population_snapshot"
2849 ("issue_id", "event", "member_id")
2850 SELECT
2851 "issue_id_p" AS "issue_id",
2852 'periodic'::"snapshot_event" AS "event",
2853 "member"."id" AS "member_id"
2854 FROM "issue"
2855 JOIN "area" ON "issue"."area_id" = "area"."id"
2856 JOIN "membership" ON "area"."id" = "membership"."area_id"
2857 JOIN "member" ON "membership"."member_id" = "member"."id"
2858 JOIN "privilege"
2859 ON "privilege"."unit_id" = "area"."unit_id"
2860 AND "privilege"."member_id" = "member"."id"
2861 WHERE "issue"."id" = "issue_id_p"
2862 AND "member"."active" AND "privilege"."voting_right"
2863 UNION
2864 SELECT
2865 "issue_id_p" AS "issue_id",
2866 'periodic'::"snapshot_event" AS "event",
2867 "member"."id" AS "member_id"
2868 FROM "issue"
2869 JOIN "area" ON "issue"."area_id" = "area"."id"
2870 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2871 JOIN "member" ON "interest"."member_id" = "member"."id"
2872 JOIN "privilege"
2873 ON "privilege"."unit_id" = "area"."unit_id"
2874 AND "privilege"."member_id" = "member"."id"
2875 WHERE "issue"."id" = "issue_id_p"
2876 AND "member"."active" AND "privilege"."voting_right";
2877 FOR "member_id_v" IN
2878 SELECT "member_id" FROM "direct_population_snapshot"
2879 WHERE "issue_id" = "issue_id_p"
2880 AND "event" = 'periodic'
2881 LOOP
2882 UPDATE "direct_population_snapshot" SET
2883 "weight" = 1 +
2884 "weight_of_added_delegations_for_population_snapshot"(
2885 "issue_id_p",
2886 "member_id_v",
2887 '{}'
2888 )
2889 WHERE "issue_id" = "issue_id_p"
2890 AND "event" = 'periodic'
2891 AND "member_id" = "member_id_v";
2892 END LOOP;
2893 RETURN;
2894 END;
2895 $$;
2897 COMMENT ON FUNCTION "create_population_snapshot"
2898 ( "issue"."id"%TYPE )
2899 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.';
2902 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2903 ( "issue_id_p" "issue"."id"%TYPE,
2904 "member_id_p" "member"."id"%TYPE,
2905 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2906 RETURNS "direct_interest_snapshot"."weight"%TYPE
2907 LANGUAGE 'plpgsql' VOLATILE AS $$
2908 DECLARE
2909 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2910 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2911 "weight_v" INT4;
2912 "sub_weight_v" INT4;
2913 BEGIN
2914 "weight_v" := 0;
2915 FOR "issue_delegation_row" IN
2916 SELECT * FROM "issue_delegation"
2917 WHERE "trustee_id" = "member_id_p"
2918 AND "issue_id" = "issue_id_p"
2919 LOOP
2920 IF NOT EXISTS (
2921 SELECT NULL FROM "direct_interest_snapshot"
2922 WHERE "issue_id" = "issue_id_p"
2923 AND "event" = 'periodic'
2924 AND "member_id" = "issue_delegation_row"."truster_id"
2925 ) AND NOT EXISTS (
2926 SELECT NULL FROM "delegating_interest_snapshot"
2927 WHERE "issue_id" = "issue_id_p"
2928 AND "event" = 'periodic'
2929 AND "member_id" = "issue_delegation_row"."truster_id"
2930 ) THEN
2931 "delegate_member_ids_v" :=
2932 "member_id_p" || "delegate_member_ids_p";
2933 INSERT INTO "delegating_interest_snapshot" (
2934 "issue_id",
2935 "event",
2936 "member_id",
2937 "scope",
2938 "delegate_member_ids"
2939 ) VALUES (
2940 "issue_id_p",
2941 'periodic',
2942 "issue_delegation_row"."truster_id",
2943 "issue_delegation_row"."scope",
2944 "delegate_member_ids_v"
2945 );
2946 "sub_weight_v" := 1 +
2947 "weight_of_added_delegations_for_interest_snapshot"(
2948 "issue_id_p",
2949 "issue_delegation_row"."truster_id",
2950 "delegate_member_ids_v"
2951 );
2952 UPDATE "delegating_interest_snapshot"
2953 SET "weight" = "sub_weight_v"
2954 WHERE "issue_id" = "issue_id_p"
2955 AND "event" = 'periodic'
2956 AND "member_id" = "issue_delegation_row"."truster_id";
2957 "weight_v" := "weight_v" + "sub_weight_v";
2958 END IF;
2959 END LOOP;
2960 RETURN "weight_v";
2961 END;
2962 $$;
2964 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2965 ( "issue"."id"%TYPE,
2966 "member"."id"%TYPE,
2967 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2968 IS 'Helper function for "create_interest_snapshot" function';
2971 CREATE FUNCTION "create_interest_snapshot"
2972 ( "issue_id_p" "issue"."id"%TYPE )
2973 RETURNS VOID
2974 LANGUAGE 'plpgsql' VOLATILE AS $$
2975 DECLARE
2976 "member_id_v" "member"."id"%TYPE;
2977 BEGIN
2978 DELETE FROM "direct_interest_snapshot"
2979 WHERE "issue_id" = "issue_id_p"
2980 AND "event" = 'periodic';
2981 DELETE FROM "delegating_interest_snapshot"
2982 WHERE "issue_id" = "issue_id_p"
2983 AND "event" = 'periodic';
2984 DELETE FROM "direct_supporter_snapshot"
2985 WHERE "issue_id" = "issue_id_p"
2986 AND "event" = 'periodic';
2987 INSERT INTO "direct_interest_snapshot"
2988 ("issue_id", "event", "member_id")
2989 SELECT
2990 "issue_id_p" AS "issue_id",
2991 'periodic' AS "event",
2992 "member"."id" AS "member_id"
2993 FROM "issue"
2994 JOIN "area" ON "issue"."area_id" = "area"."id"
2995 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2996 JOIN "member" ON "interest"."member_id" = "member"."id"
2997 JOIN "privilege"
2998 ON "privilege"."unit_id" = "area"."unit_id"
2999 AND "privilege"."member_id" = "member"."id"
3000 WHERE "issue"."id" = "issue_id_p"
3001 AND "member"."active" AND "privilege"."voting_right";
3002 FOR "member_id_v" IN
3003 SELECT "member_id" FROM "direct_interest_snapshot"
3004 WHERE "issue_id" = "issue_id_p"
3005 AND "event" = 'periodic'
3006 LOOP
3007 UPDATE "direct_interest_snapshot" SET
3008 "weight" = 1 +
3009 "weight_of_added_delegations_for_interest_snapshot"(
3010 "issue_id_p",
3011 "member_id_v",
3012 '{}'
3013 )
3014 WHERE "issue_id" = "issue_id_p"
3015 AND "event" = 'periodic'
3016 AND "member_id" = "member_id_v";
3017 END LOOP;
3018 INSERT INTO "direct_supporter_snapshot"
3019 ( "issue_id", "initiative_id", "event", "member_id",
3020 "draft_id", "informed", "satisfied" )
3021 SELECT
3022 "issue_id_p" AS "issue_id",
3023 "initiative"."id" AS "initiative_id",
3024 'periodic' AS "event",
3025 "supporter"."member_id" AS "member_id",
3026 "supporter"."draft_id" AS "draft_id",
3027 "supporter"."draft_id" = "current_draft"."id" AS "informed",
3028 NOT EXISTS (
3029 SELECT NULL FROM "critical_opinion"
3030 WHERE "initiative_id" = "initiative"."id"
3031 AND "member_id" = "supporter"."member_id"
3032 ) AS "satisfied"
3033 FROM "initiative"
3034 JOIN "supporter"
3035 ON "supporter"."initiative_id" = "initiative"."id"
3036 JOIN "current_draft"
3037 ON "initiative"."id" = "current_draft"."initiative_id"
3038 JOIN "direct_interest_snapshot"
3039 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
3040 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
3041 AND "event" = 'periodic'
3042 WHERE "initiative"."issue_id" = "issue_id_p";
3043 RETURN;
3044 END;
3045 $$;
3047 COMMENT ON FUNCTION "create_interest_snapshot"
3048 ( "issue"."id"%TYPE )
3049 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.';
3052 CREATE FUNCTION "create_snapshot"
3053 ( "issue_id_p" "issue"."id"%TYPE )
3054 RETURNS VOID
3055 LANGUAGE 'plpgsql' VOLATILE AS $$
3056 DECLARE
3057 "initiative_id_v" "initiative"."id"%TYPE;
3058 "suggestion_id_v" "suggestion"."id"%TYPE;
3059 BEGIN
3060 PERFORM "lock_issue"("issue_id_p");
3061 PERFORM "create_population_snapshot"("issue_id_p");
3062 PERFORM "create_interest_snapshot"("issue_id_p");
3063 UPDATE "issue" SET
3064 "snapshot" = now(),
3065 "latest_snapshot_event" = 'periodic',
3066 "population" = (
3067 SELECT coalesce(sum("weight"), 0)
3068 FROM "direct_population_snapshot"
3069 WHERE "issue_id" = "issue_id_p"
3070 AND "event" = 'periodic'
3071 )
3072 WHERE "id" = "issue_id_p";
3073 FOR "initiative_id_v" IN
3074 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
3075 LOOP
3076 UPDATE "initiative" SET
3077 "supporter_count" = (
3078 SELECT coalesce(sum("di"."weight"), 0)
3079 FROM "direct_interest_snapshot" AS "di"
3080 JOIN "direct_supporter_snapshot" AS "ds"
3081 ON "di"."member_id" = "ds"."member_id"
3082 WHERE "di"."issue_id" = "issue_id_p"
3083 AND "di"."event" = 'periodic'
3084 AND "ds"."initiative_id" = "initiative_id_v"
3085 AND "ds"."event" = 'periodic'
3086 ),
3087 "informed_supporter_count" = (
3088 SELECT coalesce(sum("di"."weight"), 0)
3089 FROM "direct_interest_snapshot" AS "di"
3090 JOIN "direct_supporter_snapshot" AS "ds"
3091 ON "di"."member_id" = "ds"."member_id"
3092 WHERE "di"."issue_id" = "issue_id_p"
3093 AND "di"."event" = 'periodic'
3094 AND "ds"."initiative_id" = "initiative_id_v"
3095 AND "ds"."event" = 'periodic'
3096 AND "ds"."informed"
3097 ),
3098 "satisfied_supporter_count" = (
3099 SELECT coalesce(sum("di"."weight"), 0)
3100 FROM "direct_interest_snapshot" AS "di"
3101 JOIN "direct_supporter_snapshot" AS "ds"
3102 ON "di"."member_id" = "ds"."member_id"
3103 WHERE "di"."issue_id" = "issue_id_p"
3104 AND "di"."event" = 'periodic'
3105 AND "ds"."initiative_id" = "initiative_id_v"
3106 AND "ds"."event" = 'periodic'
3107 AND "ds"."satisfied"
3108 ),
3109 "satisfied_informed_supporter_count" = (
3110 SELECT coalesce(sum("di"."weight"), 0)
3111 FROM "direct_interest_snapshot" AS "di"
3112 JOIN "direct_supporter_snapshot" AS "ds"
3113 ON "di"."member_id" = "ds"."member_id"
3114 WHERE "di"."issue_id" = "issue_id_p"
3115 AND "di"."event" = 'periodic'
3116 AND "ds"."initiative_id" = "initiative_id_v"
3117 AND "ds"."event" = 'periodic'
3118 AND "ds"."informed"
3119 AND "ds"."satisfied"
3120 )
3121 WHERE "id" = "initiative_id_v";
3122 FOR "suggestion_id_v" IN
3123 SELECT "id" FROM "suggestion"
3124 WHERE "initiative_id" = "initiative_id_v"
3125 LOOP
3126 UPDATE "suggestion" SET
3127 "minus2_unfulfilled_count" = (
3128 SELECT coalesce(sum("snapshot"."weight"), 0)
3129 FROM "issue" CROSS JOIN "opinion"
3130 JOIN "direct_interest_snapshot" AS "snapshot"
3131 ON "snapshot"."issue_id" = "issue"."id"
3132 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3133 AND "snapshot"."member_id" = "opinion"."member_id"
3134 WHERE "issue"."id" = "issue_id_p"
3135 AND "opinion"."suggestion_id" = "suggestion_id_v"
3136 AND "opinion"."degree" = -2
3137 AND "opinion"."fulfilled" = FALSE
3138 ),
3139 "minus2_fulfilled_count" = (
3140 SELECT coalesce(sum("snapshot"."weight"), 0)
3141 FROM "issue" CROSS JOIN "opinion"
3142 JOIN "direct_interest_snapshot" AS "snapshot"
3143 ON "snapshot"."issue_id" = "issue"."id"
3144 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3145 AND "snapshot"."member_id" = "opinion"."member_id"
3146 WHERE "issue"."id" = "issue_id_p"
3147 AND "opinion"."suggestion_id" = "suggestion_id_v"
3148 AND "opinion"."degree" = -2
3149 AND "opinion"."fulfilled" = TRUE
3150 ),
3151 "minus1_unfulfilled_count" = (
3152 SELECT coalesce(sum("snapshot"."weight"), 0)
3153 FROM "issue" CROSS JOIN "opinion"
3154 JOIN "direct_interest_snapshot" AS "snapshot"
3155 ON "snapshot"."issue_id" = "issue"."id"
3156 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3157 AND "snapshot"."member_id" = "opinion"."member_id"
3158 WHERE "issue"."id" = "issue_id_p"
3159 AND "opinion"."suggestion_id" = "suggestion_id_v"
3160 AND "opinion"."degree" = -1
3161 AND "opinion"."fulfilled" = FALSE
3162 ),
3163 "minus1_fulfilled_count" = (
3164 SELECT coalesce(sum("snapshot"."weight"), 0)
3165 FROM "issue" CROSS JOIN "opinion"
3166 JOIN "direct_interest_snapshot" AS "snapshot"
3167 ON "snapshot"."issue_id" = "issue"."id"
3168 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3169 AND "snapshot"."member_id" = "opinion"."member_id"
3170 WHERE "issue"."id" = "issue_id_p"
3171 AND "opinion"."suggestion_id" = "suggestion_id_v"
3172 AND "opinion"."degree" = -1
3173 AND "opinion"."fulfilled" = TRUE
3174 ),
3175 "plus1_unfulfilled_count" = (
3176 SELECT coalesce(sum("snapshot"."weight"), 0)
3177 FROM "issue" CROSS JOIN "opinion"
3178 JOIN "direct_interest_snapshot" AS "snapshot"
3179 ON "snapshot"."issue_id" = "issue"."id"
3180 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3181 AND "snapshot"."member_id" = "opinion"."member_id"
3182 WHERE "issue"."id" = "issue_id_p"
3183 AND "opinion"."suggestion_id" = "suggestion_id_v"
3184 AND "opinion"."degree" = 1
3185 AND "opinion"."fulfilled" = FALSE
3186 ),
3187 "plus1_fulfilled_count" = (
3188 SELECT coalesce(sum("snapshot"."weight"), 0)
3189 FROM "issue" CROSS JOIN "opinion"
3190 JOIN "direct_interest_snapshot" AS "snapshot"
3191 ON "snapshot"."issue_id" = "issue"."id"
3192 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3193 AND "snapshot"."member_id" = "opinion"."member_id"
3194 WHERE "issue"."id" = "issue_id_p"
3195 AND "opinion"."suggestion_id" = "suggestion_id_v"
3196 AND "opinion"."degree" = 1
3197 AND "opinion"."fulfilled" = TRUE
3198 ),
3199 "plus2_unfulfilled_count" = (
3200 SELECT coalesce(sum("snapshot"."weight"), 0)
3201 FROM "issue" CROSS JOIN "opinion"
3202 JOIN "direct_interest_snapshot" AS "snapshot"
3203 ON "snapshot"."issue_id" = "issue"."id"
3204 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3205 AND "snapshot"."member_id" = "opinion"."member_id"
3206 WHERE "issue"."id" = "issue_id_p"
3207 AND "opinion"."suggestion_id" = "suggestion_id_v"
3208 AND "opinion"."degree" = 2
3209 AND "opinion"."fulfilled" = FALSE
3210 ),
3211 "plus2_fulfilled_count" = (
3212 SELECT coalesce(sum("snapshot"."weight"), 0)
3213 FROM "issue" CROSS JOIN "opinion"
3214 JOIN "direct_interest_snapshot" AS "snapshot"
3215 ON "snapshot"."issue_id" = "issue"."id"
3216 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3217 AND "snapshot"."member_id" = "opinion"."member_id"
3218 WHERE "issue"."id" = "issue_id_p"
3219 AND "opinion"."suggestion_id" = "suggestion_id_v"
3220 AND "opinion"."degree" = 2
3221 AND "opinion"."fulfilled" = TRUE
3222 )
3223 WHERE "suggestion"."id" = "suggestion_id_v";
3224 END LOOP;
3225 END LOOP;
3226 RETURN;
3227 END;
3228 $$;
3230 COMMENT ON FUNCTION "create_snapshot"
3231 ( "issue"."id"%TYPE )
3232 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.';
3235 CREATE FUNCTION "set_snapshot_event"
3236 ( "issue_id_p" "issue"."id"%TYPE,
3237 "event_p" "snapshot_event" )
3238 RETURNS VOID
3239 LANGUAGE 'plpgsql' VOLATILE AS $$
3240 DECLARE
3241 "event_v" "issue"."latest_snapshot_event"%TYPE;
3242 BEGIN
3243 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
3244 WHERE "id" = "issue_id_p" FOR UPDATE;
3245 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
3246 WHERE "id" = "issue_id_p";
3247 UPDATE "direct_population_snapshot" SET "event" = "event_p"
3248 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3249 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
3250 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3251 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
3252 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3253 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
3254 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3255 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
3256 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3257 RETURN;
3258 END;
3259 $$;
3261 COMMENT ON FUNCTION "set_snapshot_event"
3262 ( "issue"."id"%TYPE,
3263 "snapshot_event" )
3264 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
3268 ---------------------
3269 -- Freezing issues --
3270 ---------------------
3272 CREATE FUNCTION "freeze_after_snapshot"
3273 ( "issue_id_p" "issue"."id"%TYPE )
3274 RETURNS VOID
3275 LANGUAGE 'plpgsql' VOLATILE AS $$
3276 DECLARE
3277 "issue_row" "issue"%ROWTYPE;
3278 "policy_row" "policy"%ROWTYPE;
3279 "initiative_row" "initiative"%ROWTYPE;
3280 BEGIN
3281 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3282 SELECT * INTO "policy_row"
3283 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3284 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
3285 FOR "initiative_row" IN
3286 SELECT * FROM "initiative"
3287 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3288 LOOP
3289 IF
3290 "initiative_row"."satisfied_supporter_count" > 0 AND
3291 "initiative_row"."satisfied_supporter_count" *
3292 "policy_row"."initiative_quorum_den" >=
3293 "issue_row"."population" * "policy_row"."initiative_quorum_num"
3294 THEN
3295 UPDATE "initiative" SET "admitted" = TRUE
3296 WHERE "id" = "initiative_row"."id";
3297 ELSE
3298 UPDATE "initiative" SET "admitted" = FALSE
3299 WHERE "id" = "initiative_row"."id";
3300 END IF;
3301 END LOOP;
3302 IF EXISTS (
3303 SELECT NULL FROM "initiative"
3304 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
3305 ) THEN
3306 UPDATE "issue" SET
3307 "state" = 'voting',
3308 "accepted" = coalesce("accepted", now()),
3309 "half_frozen" = coalesce("half_frozen", now()),
3310 "fully_frozen" = now()
3311 WHERE "id" = "issue_id_p";
3312 ELSE
3313 UPDATE "issue" SET
3314 "state" = 'canceled_no_initiative_admitted',
3315 "accepted" = coalesce("accepted", now()),
3316 "half_frozen" = coalesce("half_frozen", now()),
3317 "fully_frozen" = now(),
3318 "closed" = now(),
3319 "ranks_available" = TRUE
3320 WHERE "id" = "issue_id_p";
3321 -- NOTE: The following DELETE statements have effect only when
3322 -- issue state has been manipulated
3323 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
3324 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
3325 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3326 END IF;
3327 RETURN;
3328 END;
3329 $$;
3331 COMMENT ON FUNCTION "freeze_after_snapshot"
3332 ( "issue"."id"%TYPE )
3333 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
3336 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
3337 RETURNS VOID
3338 LANGUAGE 'plpgsql' VOLATILE AS $$
3339 DECLARE
3340 "issue_row" "issue"%ROWTYPE;
3341 BEGIN
3342 PERFORM "create_snapshot"("issue_id_p");
3343 PERFORM "freeze_after_snapshot"("issue_id_p");
3344 RETURN;
3345 END;
3346 $$;
3348 COMMENT ON FUNCTION "manual_freeze"
3349 ( "issue"."id"%TYPE )
3350 IS 'Freeze an issue manually (fully) and start voting';
3354 -----------------------
3355 -- Counting of votes --
3356 -----------------------
3359 CREATE FUNCTION "weight_of_added_vote_delegations"
3360 ( "issue_id_p" "issue"."id"%TYPE,
3361 "member_id_p" "member"."id"%TYPE,
3362 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
3363 RETURNS "direct_voter"."weight"%TYPE
3364 LANGUAGE 'plpgsql' VOLATILE AS $$
3365 DECLARE
3366 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3367 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
3368 "weight_v" INT4;
3369 "sub_weight_v" INT4;
3370 BEGIN
3371 "weight_v" := 0;
3372 FOR "issue_delegation_row" IN
3373 SELECT * FROM "issue_delegation"
3374 WHERE "trustee_id" = "member_id_p"
3375 AND "issue_id" = "issue_id_p"
3376 LOOP
3377 IF NOT EXISTS (
3378 SELECT NULL FROM "direct_voter"
3379 WHERE "member_id" = "issue_delegation_row"."truster_id"
3380 AND "issue_id" = "issue_id_p"
3381 ) AND NOT EXISTS (
3382 SELECT NULL FROM "delegating_voter"
3383 WHERE "member_id" = "issue_delegation_row"."truster_id"
3384 AND "issue_id" = "issue_id_p"
3385 ) THEN
3386 "delegate_member_ids_v" :=
3387 "member_id_p" || "delegate_member_ids_p";
3388 INSERT INTO "delegating_voter" (
3389 "issue_id",
3390 "member_id",
3391 "scope",
3392 "delegate_member_ids"
3393 ) VALUES (
3394 "issue_id_p",
3395 "issue_delegation_row"."truster_id",
3396 "issue_delegation_row"."scope",
3397 "delegate_member_ids_v"
3398 );
3399 "sub_weight_v" := 1 +
3400 "weight_of_added_vote_delegations"(
3401 "issue_id_p",
3402 "issue_delegation_row"."truster_id",
3403 "delegate_member_ids_v"
3404 );
3405 UPDATE "delegating_voter"
3406 SET "weight" = "sub_weight_v"
3407 WHERE "issue_id" = "issue_id_p"
3408 AND "member_id" = "issue_delegation_row"."truster_id";
3409 "weight_v" := "weight_v" + "sub_weight_v";
3410 END IF;
3411 END LOOP;
3412 RETURN "weight_v";
3413 END;
3414 $$;
3416 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
3417 ( "issue"."id"%TYPE,
3418 "member"."id"%TYPE,
3419 "delegating_voter"."delegate_member_ids"%TYPE )
3420 IS 'Helper function for "add_vote_delegations" function';
3423 CREATE FUNCTION "add_vote_delegations"
3424 ( "issue_id_p" "issue"."id"%TYPE )
3425 RETURNS VOID
3426 LANGUAGE 'plpgsql' VOLATILE AS $$
3427 DECLARE
3428 "member_id_v" "member"."id"%TYPE;
3429 BEGIN
3430 FOR "member_id_v" IN
3431 SELECT "member_id" FROM "direct_voter"
3432 WHERE "issue_id" = "issue_id_p"
3433 LOOP
3434 UPDATE "direct_voter" SET
3435 "weight" = "weight" + "weight_of_added_vote_delegations"(
3436 "issue_id_p",
3437 "member_id_v",
3438 '{}'
3439 )
3440 WHERE "member_id" = "member_id_v"
3441 AND "issue_id" = "issue_id_p";
3442 END LOOP;
3443 RETURN;
3444 END;
3445 $$;
3447 COMMENT ON FUNCTION "add_vote_delegations"
3448 ( "issue_id_p" "issue"."id"%TYPE )
3449 IS 'Helper function for "close_voting" function';
3452 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
3453 RETURNS VOID
3454 LANGUAGE 'plpgsql' VOLATILE AS $$
3455 DECLARE
3456 "area_id_v" "area"."id"%TYPE;
3457 "unit_id_v" "unit"."id"%TYPE;
3458 "member_id_v" "member"."id"%TYPE;
3459 BEGIN
3460 PERFORM "lock_issue"("issue_id_p");
3461 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
3462 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
3463 -- delete delegating votes (in cases of manual reset of issue state):
3464 DELETE FROM "delegating_voter"
3465 WHERE "issue_id" = "issue_id_p";
3466 -- delete votes from non-privileged voters:
3467 DELETE FROM "direct_voter"
3468 USING (
3469 SELECT
3470 "direct_voter"."member_id"
3471 FROM "direct_voter"
3472 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
3473 LEFT JOIN "privilege"
3474 ON "privilege"."unit_id" = "unit_id_v"
3475 AND "privilege"."member_id" = "direct_voter"."member_id"
3476 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
3477 "member"."active" = FALSE OR
3478 "privilege"."voting_right" ISNULL OR
3479 "privilege"."voting_right" = FALSE
3480 )
3481 ) AS "subquery"
3482 WHERE "direct_voter"."issue_id" = "issue_id_p"
3483 AND "direct_voter"."member_id" = "subquery"."member_id";
3484 -- consider delegations:
3485 UPDATE "direct_voter" SET "weight" = 1
3486 WHERE "issue_id" = "issue_id_p";
3487 PERFORM "add_vote_delegations"("issue_id_p");
3488 -- set voter count and mark issue as being calculated:
3489 UPDATE "issue" SET
3490 "state" = 'calculation',
3491 "closed" = now(),
3492 "voter_count" = (
3493 SELECT coalesce(sum("weight"), 0)
3494 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
3495 )
3496 WHERE "id" = "issue_id_p";
3497 -- materialize battle_view:
3498 -- NOTE: "closed" column of issue must be set at this point
3499 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3500 INSERT INTO "battle" (
3501 "issue_id",
3502 "winning_initiative_id", "losing_initiative_id",
3503 "count"
3504 ) SELECT
3505 "issue_id",
3506 "winning_initiative_id", "losing_initiative_id",
3507 "count"
3508 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
3509 -- copy "positive_votes" and "negative_votes" from "battle" table:
3510 UPDATE "initiative" SET
3511 "positive_votes" = "battle_win"."count",
3512 "negative_votes" = "battle_lose"."count"
3513 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
3514 WHERE
3515 "battle_win"."issue_id" = "issue_id_p" AND
3516 "battle_win"."winning_initiative_id" = "initiative"."id" AND
3517 "battle_win"."losing_initiative_id" ISNULL AND
3518 "battle_lose"."issue_id" = "issue_id_p" AND
3519 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
3520 "battle_lose"."winning_initiative_id" ISNULL;
3521 END;
3522 $$;
3524 COMMENT ON FUNCTION "close_voting"
3525 ( "issue"."id"%TYPE )
3526 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.';
3529 CREATE FUNCTION "defeat_strength"
3530 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
3531 RETURNS INT8
3532 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3533 BEGIN
3534 IF "positive_votes_p" > "negative_votes_p" THEN
3535 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
3536 ELSIF "positive_votes_p" = "negative_votes_p" THEN
3537 RETURN 0;
3538 ELSE
3539 RETURN -1;
3540 END IF;
3541 END;
3542 $$;
3544 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';
3547 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
3548 RETURNS VOID
3549 LANGUAGE 'plpgsql' VOLATILE AS $$
3550 DECLARE
3551 "issue_row" "issue"%ROWTYPE;
3552 "policy_row" "policy"%ROWTYPE;
3553 "dimension_v" INTEGER;
3554 "vote_matrix" INT4[][]; -- absolute votes
3555 "matrix" INT8[][]; -- defeat strength / best paths
3556 "i" INTEGER;
3557 "j" INTEGER;
3558 "k" INTEGER;
3559 "battle_row" "battle"%ROWTYPE;
3560 "rank_ary" INT4[];
3561 "rank_v" INT4;
3562 "done_v" INTEGER;
3563 "winners_ary" INTEGER[];
3564 "initiative_id_v" "initiative"."id"%TYPE;
3565 BEGIN
3566 SELECT * INTO "issue_row"
3567 FROM "issue" WHERE "id" = "issue_id_p"
3568 FOR UPDATE;
3569 SELECT * INTO "policy_row"
3570 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3571 SELECT count(1) INTO "dimension_v"
3572 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
3573 -- Create "vote_matrix" with absolute number of votes in pairwise
3574 -- comparison:
3575 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
3576 "i" := 1;
3577 "j" := 2;
3578 FOR "battle_row" IN
3579 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
3580 ORDER BY
3581 "winning_initiative_id" NULLS LAST,
3582 "losing_initiative_id" NULLS LAST
3583 LOOP
3584 "vote_matrix"["i"]["j"] := "battle_row"."count";
3585 IF "j" = "dimension_v" THEN
3586 "i" := "i" + 1;
3587 "j" := 1;
3588 ELSE
3589 "j" := "j" + 1;
3590 IF "j" = "i" THEN
3591 "j" := "j" + 1;
3592 END IF;
3593 END IF;
3594 END LOOP;
3595 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
3596 RAISE EXCEPTION 'Wrong battle count (should not happen)';
3597 END IF;
3598 -- Store defeat strengths in "matrix" using "defeat_strength"
3599 -- function:
3600 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
3601 "i" := 1;
3602 LOOP
3603 "j" := 1;
3604 LOOP
3605 IF "i" != "j" THEN
3606 "matrix"["i"]["j"] := "defeat_strength"(
3607 "vote_matrix"["i"]["j"],
3608 "vote_matrix"["j"]["i"]
3609 );
3610 END IF;
3611 EXIT WHEN "j" = "dimension_v";
3612 "j" := "j" + 1;
3613 END LOOP;
3614 EXIT WHEN "i" = "dimension_v";
3615 "i" := "i" + 1;
3616 END LOOP;
3617 -- Find best paths:
3618 "i" := 1;
3619 LOOP
3620 "j" := 1;
3621 LOOP
3622 IF "i" != "j" THEN
3623 "k" := 1;
3624 LOOP
3625 IF "i" != "k" AND "j" != "k" THEN
3626 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
3627 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
3628 "matrix"["j"]["k"] := "matrix"["j"]["i"];
3629 END IF;
3630 ELSE
3631 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
3632 "matrix"["j"]["k"] := "matrix"["i"]["k"];
3633 END IF;
3634 END IF;
3635 END IF;
3636 EXIT WHEN "k" = "dimension_v";
3637 "k" := "k" + 1;
3638 END LOOP;
3639 END IF;
3640 EXIT WHEN "j" = "dimension_v";
3641 "j" := "j" + 1;
3642 END LOOP;
3643 EXIT WHEN "i" = "dimension_v";
3644 "i" := "i" + 1;
3645 END LOOP;
3646 -- Determine order of winners:
3647 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
3648 "rank_v" := 1;
3649 "done_v" := 0;
3650 LOOP
3651 "winners_ary" := '{}';
3652 "i" := 1;
3653 LOOP
3654 IF "rank_ary"["i"] ISNULL THEN
3655 "j" := 1;
3656 LOOP
3657 IF
3658 "i" != "j" AND
3659 "rank_ary"["j"] ISNULL AND
3660 "matrix"["j"]["i"] > "matrix"["i"]["j"]
3661 THEN
3662 -- someone else is better
3663 EXIT;
3664 END IF;
3665 IF "j" = "dimension_v" THEN
3666 -- noone is better
3667 "winners_ary" := "winners_ary" || "i";
3668 EXIT;
3669 END IF;
3670 "j" := "j" + 1;
3671 END LOOP;
3672 END IF;
3673 EXIT WHEN "i" = "dimension_v";
3674 "i" := "i" + 1;
3675 END LOOP;
3676 "i" := 1;
3677 LOOP
3678 "rank_ary"["winners_ary"["i"]] := "rank_v";
3679 "done_v" := "done_v" + 1;
3680 EXIT WHEN "i" = array_upper("winners_ary", 1);
3681 "i" := "i" + 1;
3682 END LOOP;
3683 EXIT WHEN "done_v" = "dimension_v";
3684 "rank_v" := "rank_v" + 1;
3685 END LOOP;
3686 -- write preliminary results:
3687 "i" := 1;
3688 FOR "initiative_id_v" IN
3689 SELECT "id" FROM "initiative"
3690 WHERE "issue_id" = "issue_id_p" AND "admitted"
3691 ORDER BY "id"
3692 LOOP
3693 UPDATE "initiative" SET
3694 "direct_majority" =
3695 CASE WHEN "policy_row"."direct_majority_strict" THEN
3696 "positive_votes" * "policy_row"."direct_majority_den" >
3697 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
3698 ELSE
3699 "positive_votes" * "policy_row"."direct_majority_den" >=
3700 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
3701 END
3702 AND "positive_votes" >= "policy_row"."direct_majority_positive"
3703 AND "issue_row"."voter_count"-"negative_votes" >=
3704 "policy_row"."direct_majority_non_negative",
3705 "indirect_majority" =
3706 CASE WHEN "policy_row"."indirect_majority_strict" THEN
3707 "positive_votes" * "policy_row"."indirect_majority_den" >
3708 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
3709 ELSE
3710 "positive_votes" * "policy_row"."indirect_majority_den" >=
3711 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
3712 END
3713 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
3714 AND "issue_row"."voter_count"-"negative_votes" >=
3715 "policy_row"."indirect_majority_non_negative",
3716 "schulze_rank" = "rank_ary"["i"],
3717 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
3718 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
3719 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
3720 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0,
3721 "eligible" = FALSE,
3722 "winner" = FALSE
3723 WHERE "id" = "initiative_id_v";
3724 "i" := "i" + 1;
3725 END LOOP;
3726 IF "i" != "dimension_v" THEN
3727 RAISE EXCEPTION 'Wrong winner count (should not happen)';
3728 END IF;
3729 -- take indirect majorities into account:
3730 LOOP
3731 UPDATE "initiative" SET "indirect_majority" = TRUE
3732 FROM (
3733 SELECT "new_initiative"."id" AS "initiative_id"
3734 FROM "initiative" "old_initiative"
3735 JOIN "initiative" "new_initiative"
3736 ON "new_initiative"."issue_id" = "issue_id_p"
3737 AND "new_initiative"."indirect_majority" = FALSE
3738 JOIN "battle" "battle_win"
3739 ON "battle_win"."issue_id" = "issue_id_p"
3740 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
3741 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
3742 JOIN "battle" "battle_lose"
3743 ON "battle_lose"."issue_id" = "issue_id_p"
3744 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
3745 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
3746 WHERE "old_initiative"."issue_id" = "issue_id_p"
3747 AND "old_initiative"."indirect_majority" = TRUE
3748 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
3749 "battle_win"."count" * "policy_row"."indirect_majority_den" >
3750 "policy_row"."indirect_majority_num" *
3751 ("battle_win"."count"+"battle_lose"."count")
3752 ELSE
3753 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
3754 "policy_row"."indirect_majority_num" *
3755 ("battle_win"."count"+"battle_lose"."count")
3756 END
3757 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
3758 AND "issue_row"."voter_count"-"battle_lose"."count" >=
3759 "policy_row"."indirect_majority_non_negative"
3760 ) AS "subquery"
3761 WHERE "id" = "subquery"."initiative_id";
3762 EXIT WHEN NOT FOUND;
3763 END LOOP;
3764 -- set "multistage_majority" for remaining matching initiatives:
3765 UPDATE "initiative" SET "multistage_majority" = TRUE
3766 FROM (
3767 SELECT "losing_initiative"."id" AS "initiative_id"
3768 FROM "initiative" "losing_initiative"
3769 JOIN "initiative" "winning_initiative"
3770 ON "winning_initiative"."issue_id" = "issue_id_p"
3771 AND "winning_initiative"."admitted"
3772 JOIN "battle" "battle_win"
3773 ON "battle_win"."issue_id" = "issue_id_p"
3774 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
3775 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
3776 JOIN "battle" "battle_lose"
3777 ON "battle_lose"."issue_id" = "issue_id_p"
3778 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
3779 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
3780 WHERE "losing_initiative"."issue_id" = "issue_id_p"
3781 AND "losing_initiative"."admitted"
3782 AND "winning_initiative"."schulze_rank" <
3783 "losing_initiative"."schulze_rank"
3784 AND "battle_win"."count" > "battle_lose"."count"
3785 AND (
3786 "battle_win"."count" > "winning_initiative"."positive_votes" OR
3787 "battle_lose"."count" < "losing_initiative"."negative_votes" )
3788 ) AS "subquery"
3789 WHERE "id" = "subquery"."initiative_id";
3790 -- mark eligible initiatives:
3791 UPDATE "initiative" SET "eligible" = TRUE
3792 WHERE "issue_id" = "issue_id_p"
3793 AND "initiative"."direct_majority"
3794 AND "initiative"."indirect_majority"
3795 AND "initiative"."better_than_status_quo"
3796 AND (
3797 "policy_row"."no_multistage_majority" = FALSE OR
3798 "initiative"."multistage_majority" = FALSE )
3799 AND (
3800 "policy_row"."no_reverse_beat_path" = FALSE OR
3801 "initiative"."reverse_beat_path" = FALSE );
3802 -- mark final winner:
3803 UPDATE "initiative" SET "winner" = TRUE
3804 FROM (
3805 SELECT "id" AS "initiative_id"
3806 FROM "initiative"
3807 WHERE "issue_id" = "issue_id_p" AND "eligible"
3808 ORDER BY
3809 "schulze_rank",
3810 "vote_ratio"("positive_votes", "negative_votes"),
3811 "id"
3812 LIMIT 1
3813 ) AS "subquery"
3814 WHERE "id" = "subquery"."initiative_id";
3815 -- write (final) ranks:
3816 "rank_v" := 1;
3817 FOR "initiative_id_v" IN
3818 SELECT "id"
3819 FROM "initiative"
3820 WHERE "issue_id" = "issue_id_p" AND "admitted"
3821 ORDER BY
3822 "winner" DESC,
3823 "eligible" DESC,
3824 "schulze_rank",
3825 "vote_ratio"("positive_votes", "negative_votes"),
3826 "id"
3827 LOOP
3828 UPDATE "initiative" SET "rank" = "rank_v"
3829 WHERE "id" = "initiative_id_v";
3830 "rank_v" := "rank_v" + 1;
3831 END LOOP;
3832 -- set schulze rank of status quo and mark issue as finished:
3833 UPDATE "issue" SET
3834 "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
3835 "state" =
3836 CASE WHEN EXISTS (
3837 SELECT NULL FROM "initiative"
3838 WHERE "issue_id" = "issue_id_p" AND "winner"
3839 ) THEN
3840 'finished_with_winner'::"issue_state"
3841 ELSE
3842 'finished_without_winner'::"issue_state"
3843 END,
3844 "ranks_available" = TRUE
3845 WHERE "id" = "issue_id_p";
3846 RETURN;
3847 END;
3848 $$;
3850 COMMENT ON FUNCTION "calculate_ranks"
3851 ( "issue"."id"%TYPE )
3852 IS 'Determine ranking (Votes have to be counted first)';
3856 -----------------------------
3857 -- Automatic state changes --
3858 -----------------------------
3861 CREATE FUNCTION "check_issue"
3862 ( "issue_id_p" "issue"."id"%TYPE )
3863 RETURNS VOID
3864 LANGUAGE 'plpgsql' VOLATILE AS $$
3865 DECLARE
3866 "issue_row" "issue"%ROWTYPE;
3867 "policy_row" "policy"%ROWTYPE;
3868 BEGIN
3869 PERFORM "lock_issue"("issue_id_p");
3870 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3871 -- only process open issues:
3872 IF "issue_row"."closed" ISNULL THEN
3873 SELECT * INTO "policy_row" FROM "policy"
3874 WHERE "id" = "issue_row"."policy_id";
3875 -- create a snapshot, unless issue is already fully frozen:
3876 IF "issue_row"."fully_frozen" ISNULL THEN
3877 PERFORM "create_snapshot"("issue_id_p");
3878 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3879 END IF;
3880 -- eventually close or accept issues, which have not been accepted:
3881 IF "issue_row"."accepted" ISNULL THEN
3882 IF EXISTS (
3883 SELECT NULL FROM "initiative"
3884 WHERE "issue_id" = "issue_id_p"
3885 AND "supporter_count" > 0
3886 AND "supporter_count" * "policy_row"."issue_quorum_den"
3887 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
3888 ) THEN
3889 -- accept issues, if supporter count is high enough
3890 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3891 -- NOTE: "issue_row" used later
3892 "issue_row"."state" := 'discussion';
3893 "issue_row"."accepted" := now();
3894 UPDATE "issue" SET
3895 "state" = "issue_row"."state",
3896 "accepted" = "issue_row"."accepted"
3897 WHERE "id" = "issue_row"."id";
3898 ELSIF
3899 now() >= "issue_row"."created" + "issue_row"."admission_time"
3900 THEN
3901 -- close issues, if admission time has expired
3902 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3903 UPDATE "issue" SET
3904 "state" = 'canceled_issue_not_accepted',
3905 "closed" = now()
3906 WHERE "id" = "issue_row"."id";
3907 END IF;
3908 END IF;
3909 -- eventually half freeze issues:
3910 IF
3911 -- NOTE: issue can't be closed at this point, if it has been accepted
3912 "issue_row"."accepted" NOTNULL AND
3913 "issue_row"."half_frozen" ISNULL
3914 THEN
3915 IF
3916 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
3917 THEN
3918 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
3919 -- NOTE: "issue_row" used later
3920 "issue_row"."state" := 'verification';
3921 "issue_row"."half_frozen" := now();
3922 UPDATE "issue" SET
3923 "state" = "issue_row"."state",
3924 "half_frozen" = "issue_row"."half_frozen"
3925 WHERE "id" = "issue_row"."id";
3926 END IF;
3927 END IF;
3928 -- close issues after some time, if all initiatives have been revoked:
3929 IF
3930 "issue_row"."closed" ISNULL AND
3931 NOT EXISTS (
3932 -- all initiatives are revoked
3933 SELECT NULL FROM "initiative"
3934 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3935 ) AND (
3936 -- and issue has not been accepted yet
3937 "issue_row"."accepted" ISNULL OR
3938 NOT EXISTS (
3939 -- or no initiatives have been revoked lately
3940 SELECT NULL FROM "initiative"
3941 WHERE "issue_id" = "issue_id_p"
3942 AND now() < "revoked" + "issue_row"."verification_time"
3943 ) OR (
3944 -- or verification time has elapsed
3945 "issue_row"."half_frozen" NOTNULL AND
3946 "issue_row"."fully_frozen" ISNULL AND
3947 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3948 )
3949 )
3950 THEN
3951 -- NOTE: "issue_row" used later
3952 IF "issue_row"."accepted" ISNULL THEN
3953 "issue_row"."state" := 'canceled_revoked_before_accepted';
3954 ELSIF "issue_row"."half_frozen" ISNULL THEN
3955 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
3956 ELSE
3957 "issue_row"."state" := 'canceled_after_revocation_during_verification';
3958 END IF;
3959 "issue_row"."closed" := now();
3960 UPDATE "issue" SET
3961 "state" = "issue_row"."state",
3962 "closed" = "issue_row"."closed"
3963 WHERE "id" = "issue_row"."id";
3964 END IF;
3965 -- fully freeze issue after verification time:
3966 IF
3967 "issue_row"."half_frozen" NOTNULL AND
3968 "issue_row"."fully_frozen" ISNULL AND
3969 "issue_row"."closed" ISNULL AND
3970 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3971 THEN
3972 PERFORM "freeze_after_snapshot"("issue_id_p");
3973 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
3974 END IF;
3975 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3976 -- close issue by calling close_voting(...) after voting time:
3977 IF
3978 "issue_row"."closed" ISNULL AND
3979 "issue_row"."fully_frozen" NOTNULL AND
3980 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
3981 THEN
3982 PERFORM "close_voting"("issue_id_p");
3983 -- calculate ranks will not consume much time and can be done now
3984 PERFORM "calculate_ranks"("issue_id_p");
3985 END IF;
3986 END IF;
3987 RETURN;
3988 END;
3989 $$;
3991 COMMENT ON FUNCTION "check_issue"
3992 ( "issue"."id"%TYPE )
3993 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.';
3996 CREATE FUNCTION "check_everything"()
3997 RETURNS VOID
3998 LANGUAGE 'plpgsql' VOLATILE AS $$
3999 DECLARE
4000 "issue_id_v" "issue"."id"%TYPE;
4001 BEGIN
4002 PERFORM "check_activity"();
4003 PERFORM "calculate_member_counts"();
4004 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
4005 PERFORM "check_issue"("issue_id_v");
4006 END LOOP;
4007 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
4008 PERFORM "calculate_ranks"("issue_id_v");
4009 END LOOP;
4010 RETURN;
4011 END;
4012 $$;
4014 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.';
4018 ----------------------
4019 -- Deletion of data --
4020 ----------------------
4023 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
4024 RETURNS VOID
4025 LANGUAGE 'plpgsql' VOLATILE AS $$
4026 DECLARE
4027 "issue_row" "issue"%ROWTYPE;
4028 BEGIN
4029 SELECT * INTO "issue_row"
4030 FROM "issue" WHERE "id" = "issue_id_p"
4031 FOR UPDATE;
4032 IF "issue_row"."cleaned" ISNULL THEN
4033 UPDATE "issue" SET
4034 "state" = 'voting',
4035 "closed" = NULL,
4036 "ranks_available" = FALSE
4037 WHERE "id" = "issue_id_p";
4038 DELETE FROM "issue_comment"
4039 WHERE "issue_id" = "issue_id_p";
4040 DELETE FROM "voting_comment"
4041 WHERE "issue_id" = "issue_id_p";
4042 DELETE FROM "delegating_voter"
4043 WHERE "issue_id" = "issue_id_p";
4044 DELETE FROM "direct_voter"
4045 WHERE "issue_id" = "issue_id_p";
4046 DELETE FROM "delegating_interest_snapshot"
4047 WHERE "issue_id" = "issue_id_p";
4048 DELETE FROM "direct_interest_snapshot"
4049 WHERE "issue_id" = "issue_id_p";
4050 DELETE FROM "delegating_population_snapshot"
4051 WHERE "issue_id" = "issue_id_p";
4052 DELETE FROM "direct_population_snapshot"
4053 WHERE "issue_id" = "issue_id_p";
4054 DELETE FROM "non_voter"
4055 WHERE "issue_id" = "issue_id_p";
4056 DELETE FROM "delegation"
4057 WHERE "issue_id" = "issue_id_p";
4058 DELETE FROM "supporter"
4059 WHERE "issue_id" = "issue_id_p";
4060 UPDATE "issue" SET
4061 "state" = "issue_row"."state",
4062 "closed" = "issue_row"."closed",
4063 "ranks_available" = "issue_row"."ranks_available",
4064 "cleaned" = now()
4065 WHERE "id" = "issue_id_p";
4066 END IF;
4067 RETURN;
4068 END;
4069 $$;
4071 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
4074 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
4075 RETURNS VOID
4076 LANGUAGE 'plpgsql' VOLATILE AS $$
4077 BEGIN
4078 UPDATE "member" SET
4079 "last_login" = NULL,
4080 "login" = NULL,
4081 "password" = NULL,
4082 "locked" = TRUE,
4083 "active" = FALSE,
4084 "notify_email" = NULL,
4085 "notify_email_unconfirmed" = NULL,
4086 "notify_email_secret" = NULL,
4087 "notify_email_secret_expiry" = NULL,
4088 "notify_email_lock_expiry" = NULL,
4089 "password_reset_secret" = NULL,
4090 "password_reset_secret_expiry" = NULL,
4091 "organizational_unit" = NULL,
4092 "internal_posts" = NULL,
4093 "realname" = NULL,
4094 "birthday" = NULL,
4095 "address" = NULL,
4096 "email" = NULL,
4097 "xmpp_address" = NULL,
4098 "website" = NULL,
4099 "phone" = NULL,
4100 "mobile_phone" = NULL,
4101 "profession" = NULL,
4102 "external_memberships" = NULL,
4103 "external_posts" = NULL,
4104 "statement" = NULL
4105 WHERE "id" = "member_id_p";
4106 -- "text_search_data" is updated by triggers
4107 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
4108 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
4109 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
4110 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
4111 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
4112 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
4113 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
4114 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
4115 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
4116 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
4117 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
4118 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
4119 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
4120 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
4121 DELETE FROM "direct_voter" USING "issue"
4122 WHERE "direct_voter"."issue_id" = "issue"."id"
4123 AND "issue"."closed" ISNULL
4124 AND "member_id" = "member_id_p";
4125 RETURN;
4126 END;
4127 $$;
4129 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)';
4132 CREATE FUNCTION "delete_private_data"()
4133 RETURNS VOID
4134 LANGUAGE 'plpgsql' VOLATILE AS $$
4135 BEGIN
4136 UPDATE "member" SET
4137 "invite_code" = NULL,
4138 "last_login" = NULL,
4139 "login" = NULL,
4140 "password" = NULL,
4141 "notify_email" = NULL,
4142 "notify_email_unconfirmed" = NULL,
4143 "notify_email_secret" = NULL,
4144 "notify_email_secret_expiry" = NULL,
4145 "notify_email_lock_expiry" = NULL,
4146 "password_reset_secret" = NULL,
4147 "password_reset_secret_expiry" = NULL,
4148 "organizational_unit" = NULL,
4149 "internal_posts" = NULL,
4150 "realname" = NULL,
4151 "birthday" = NULL,
4152 "address" = NULL,
4153 "email" = NULL,
4154 "xmpp_address" = NULL,
4155 "website" = NULL,
4156 "phone" = NULL,
4157 "mobile_phone" = NULL,
4158 "profession" = NULL,
4159 "external_memberships" = NULL,
4160 "external_posts" = NULL,
4161 "statement" = NULL;
4162 -- "text_search_data" is updated by triggers
4163 DELETE FROM "setting";
4164 DELETE FROM "setting_map";
4165 DELETE FROM "member_relation_setting";
4166 DELETE FROM "member_image";
4167 DELETE FROM "contact";
4168 DELETE FROM "ignored_member";
4169 DELETE FROM "area_setting";
4170 DELETE FROM "issue_setting";
4171 DELETE FROM "ignored_initiative";
4172 DELETE FROM "initiative_setting";
4173 DELETE FROM "suggestion_setting";
4174 DELETE FROM "non_voter";
4175 DELETE FROM "direct_voter" USING "issue"
4176 WHERE "direct_voter"."issue_id" = "issue"."id"
4177 AND "issue"."closed" ISNULL;
4178 RETURN;
4179 END;
4180 $$;
4182 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.';
4186 COMMIT;