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