liquid_feedback_core

view core.sql @ 154:8e7d583e02f9

find changesets by author, revision, files, or words in the commit message
Configurable supermajority requirements related to "disqualification" of initiatives for stability reasons

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

Impressum / About Us