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