liquid_feedback_core
view core.sql @ 132:3d5e38ea2fab
Added new column "promising" to table "initiative"
Also added example to demo.sql, which results in a promising initiative.
Also added example to demo.sql, which results in a promising initiative.
| author | jbe |
|---|---|
| date | Tue May 24 23:24:43 2011 +0200 (2011-05-24) |
| parents | 284113a50c3b |
| children | 72792038f7f0 |
line source
2 -- Execute the following command manually for PostgreSQL prior version 9.0:
3 -- CREATE LANGUAGE plpgsql;
5 -- NOTE: In PostgreSQL every UNIQUE constraint implies creation of an index
7 BEGIN;
9 CREATE VIEW "liquid_feedback_version" AS
10 SELECT * FROM (VALUES ('1.4.0_rc1', 1, 4, -1))
11 AS "subquery"("string", "major", "minor", "revision");
15 ----------------------
16 -- Full text search --
17 ----------------------
20 CREATE FUNCTION "text_search_query"("query_text_p" TEXT)
21 RETURNS TSQUERY
22 LANGUAGE 'plpgsql' IMMUTABLE AS $$
23 BEGIN
24 RETURN plainto_tsquery('pg_catalog.simple', "query_text_p");
25 END;
26 $$;
28 COMMENT ON FUNCTION "text_search_query"(TEXT) IS 'Usage: WHERE "text_search_data" @@ "text_search_query"(''<user query>'')';
31 CREATE FUNCTION "highlight"
32 ( "body_p" TEXT,
33 "query_text_p" TEXT )
34 RETURNS TEXT
35 LANGUAGE 'plpgsql' IMMUTABLE AS $$
36 BEGIN
37 RETURN ts_headline(
38 'pg_catalog.simple',
39 replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'),
40 "text_search_query"("query_text_p"),
41 'StartSel=* StopSel=* HighlightAll=TRUE' );
42 END;
43 $$;
45 COMMENT ON FUNCTION "highlight"
46 ( "body_p" TEXT,
47 "query_text_p" TEXT )
48 IS 'For a given a user query this function encapsulates all matches with asterisks. Asterisks and backslashes being already present are preceeded with one extra backslash.';
52 -------------------------
53 -- Tables and indicies --
54 -------------------------
57 CREATE TABLE "system_setting" (
58 "member_ttl" INTERVAL );
59 CREATE UNIQUE INDEX "system_setting_singleton_idx" ON "system_setting" ((1));
61 COMMENT ON TABLE "system_setting" IS 'This table contains only one row with different settings in each column.';
62 COMMENT ON INDEX "system_setting_singleton_idx" IS 'This index ensures that "system_setting" only contains one row maximum.';
64 COMMENT ON COLUMN "system_setting"."member_ttl" IS 'Time after members get their "active" flag set to FALSE, if they do not login anymore.';
67 CREATE TABLE "contingent" (
68 "time_frame" INTERVAL PRIMARY KEY,
69 "text_entry_limit" INT4,
70 "initiative_limit" INT4 );
72 COMMENT ON TABLE "contingent" IS 'Amount of text entries or initiatives a user may create within a given time frame. Only one row needs to be fulfilled for a member to be allowed to post. This table must not be empty.';
74 COMMENT ON COLUMN "contingent"."text_entry_limit" IS 'Number of new drafts or suggestions to be submitted by each member within the given time frame';
75 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
78 CREATE TYPE "notify_level" AS ENUM
79 ('none', 'voting', 'verification', 'discussion', 'all');
81 COMMENT ON TYPE "notify_level" IS 'Level of notification: ''none'' = no notifications, ''voting'' = notifications about finished issues and issues in voting, ''verification'' = notifications about finished issues, issues in voting and verification phase, ''discussion'' = notifications about everything except issues in admission phase, ''all'' = notifications about everything';
84 CREATE TABLE "member" (
85 "id" SERIAL4 PRIMARY KEY,
86 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
87 "last_login" TIMESTAMPTZ,
88 "last_login_public" DATE,
89 "login" TEXT UNIQUE,
90 "password" TEXT,
91 "locked" BOOLEAN NOT NULL DEFAULT FALSE,
92 "active" BOOLEAN NOT NULL DEFAULT TRUE,
93 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
94 "notify_email" TEXT,
95 "notify_email_unconfirmed" TEXT,
96 "notify_email_secret" TEXT UNIQUE,
97 "notify_email_secret_expiry" TIMESTAMPTZ,
98 "notify_email_lock_expiry" TIMESTAMPTZ,
99 "notify_level" "notify_level" NOT NULL DEFAULT 'none',
100 "notify_event_id" INT8,
101 "password_reset_secret" TEXT UNIQUE,
102 "password_reset_secret_expiry" TIMESTAMPTZ,
103 "name" TEXT NOT NULL UNIQUE,
104 "identification" TEXT UNIQUE,
105 "organizational_unit" TEXT,
106 "internal_posts" TEXT,
107 "realname" TEXT,
108 "birthday" DATE,
109 "address" TEXT,
110 "email" TEXT,
111 "xmpp_address" TEXT,
112 "website" TEXT,
113 "phone" TEXT,
114 "mobile_phone" TEXT,
115 "profession" TEXT,
116 "external_memberships" TEXT,
117 "external_posts" TEXT,
118 "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" );
129 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
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';
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");
164 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
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';
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) );
179 COMMENT ON TABLE "invite_code" IS 'Invite codes can be used once to create a new member account.';
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';
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");
195 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
197 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
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");
208 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
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';
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 );
222 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
225 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
227 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
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 );
238 COMMENT ON TABLE "member_image" IS 'Images of members';
240 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
243 CREATE TABLE "member_count" (
244 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
245 "total_count" INT4 NOT NULL );
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';
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';
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");
262 COMMENT ON TABLE "contact" IS 'Contact lists';
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';
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");
275 COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
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';
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");
289 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';
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';
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 CREATE INDEX "policy_active_idx" ON "policy" ("active");
316 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
318 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
319 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
320 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"';
321 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"';
322 COMMENT ON COLUMN "policy"."verification_time" IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
323 COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "fully_frozen" but not "closed"';
324 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"';
325 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"';
326 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
327 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
328 COMMENT ON COLUMN "policy"."majority_num" IS 'Numerator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
329 COMMENT ON COLUMN "policy"."majority_den" IS 'Denominator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
330 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.';
333 CREATE TABLE "unit" (
334 "id" SERIAL4 PRIMARY KEY,
335 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
336 "active" BOOLEAN NOT NULL DEFAULT TRUE,
337 "name" TEXT NOT NULL,
338 "description" TEXT NOT NULL DEFAULT '',
339 "member_count" INT4,
340 "text_search_data" TSVECTOR );
341 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
342 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
343 CREATE INDEX "unit_active_idx" ON "unit" ("active");
344 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
345 CREATE TRIGGER "update_text_search_data"
346 BEFORE INSERT OR UPDATE ON "unit"
347 FOR EACH ROW EXECUTE PROCEDURE
348 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
349 "name", "description" );
351 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
353 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
354 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in units of this area';
355 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
358 CREATE TABLE "area" (
359 "id" SERIAL4 PRIMARY KEY,
360 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
361 "active" BOOLEAN NOT NULL DEFAULT TRUE,
362 "name" TEXT NOT NULL,
363 "description" TEXT NOT NULL DEFAULT '',
364 "direct_member_count" INT4,
365 "member_weight" INT4,
366 "autoreject_weight" INT4,
367 "text_search_data" TSVECTOR );
368 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
369 CREATE INDEX "area_active_idx" ON "area" ("active");
370 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
371 CREATE TRIGGER "update_text_search_data"
372 BEFORE INSERT OR UPDATE ON "area"
373 FOR EACH ROW EXECUTE PROCEDURE
374 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
375 "name", "description" );
377 COMMENT ON TABLE "area" IS 'Subject areas';
379 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
380 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"';
381 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
382 COMMENT ON COLUMN "area"."autoreject_weight" IS 'Sum of weight of members using the autoreject feature';
385 CREATE TABLE "area_setting" (
386 PRIMARY KEY ("member_id", "key", "area_id"),
387 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
388 "key" TEXT NOT NULL,
389 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
390 "value" TEXT NOT NULL );
392 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
395 CREATE TABLE "allowed_policy" (
396 PRIMARY KEY ("area_id", "policy_id"),
397 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
398 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
399 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
400 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
402 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
404 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
407 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
409 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';
412 CREATE TYPE "issue_state" AS ENUM (
413 'admission', 'discussion', 'verification', 'voting',
414 'canceled_revoked_before_accepted',
415 'canceled_issue_not_accepted',
416 'canceled_after_revocation_during_discussion',
417 'canceled_after_revocation_during_verification',
418 'calculation',
419 'canceled_no_initiative_admitted',
420 'finished_without_winner', 'finished_with_winner');
422 COMMENT ON TYPE "issue_state" IS 'State of issues';
425 CREATE TABLE "issue" (
426 "id" SERIAL4 PRIMARY KEY,
427 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
428 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
429 "state" "issue_state" NOT NULL DEFAULT 'admission',
430 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
431 "accepted" TIMESTAMPTZ,
432 "half_frozen" TIMESTAMPTZ,
433 "fully_frozen" TIMESTAMPTZ,
434 "closed" TIMESTAMPTZ,
435 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
436 "cleaned" TIMESTAMPTZ,
437 "admission_time" INTERVAL NOT NULL,
438 "discussion_time" INTERVAL NOT NULL,
439 "verification_time" INTERVAL NOT NULL,
440 "voting_time" INTERVAL NOT NULL,
441 "snapshot" TIMESTAMPTZ,
442 "latest_snapshot_event" "snapshot_event",
443 "population" INT4,
444 "vote_now" INT4,
445 "vote_later" INT4,
446 "voter_count" INT4,
447 CONSTRAINT "valid_state" CHECK ((
448 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
449 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
450 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
451 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
452 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
453 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
454 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
455 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
456 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)) AND (
457 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
458 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
459 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
460 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
461 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
462 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
463 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
464 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
465 ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
466 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
467 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
468 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
469 )),
470 CONSTRAINT "state_change_order" CHECK (
471 "created" <= "accepted" AND
472 "accepted" <= "half_frozen" AND
473 "half_frozen" <= "fully_frozen" AND
474 "fully_frozen" <= "closed" ),
475 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
476 "cleaned" ISNULL OR "closed" NOTNULL ),
477 CONSTRAINT "last_snapshot_on_full_freeze"
478 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
479 CONSTRAINT "freeze_requires_snapshot"
480 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
481 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
482 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
483 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
484 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
485 CREATE INDEX "issue_created_idx" ON "issue" ("created");
486 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
487 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
488 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
489 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
490 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
491 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
493 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
495 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
496 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.';
497 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.';
498 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.';
499 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
500 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
501 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
502 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
503 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
504 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
505 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population", "vote_now", "vote_later" and *_count values were precalculated';
506 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';
507 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
508 COMMENT ON COLUMN "issue"."vote_now" IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"';
509 COMMENT ON COLUMN "issue"."vote_later" IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"';
510 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';
513 CREATE TABLE "issue_setting" (
514 PRIMARY KEY ("member_id", "key", "issue_id"),
515 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
516 "key" TEXT NOT NULL,
517 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
518 "value" TEXT NOT NULL );
520 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
523 CREATE TABLE "initiative" (
524 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
525 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
526 "id" SERIAL4 PRIMARY KEY,
527 "name" TEXT NOT NULL,
528 "discussion_url" TEXT,
529 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
530 "revoked" TIMESTAMPTZ,
531 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
532 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
533 "admitted" BOOLEAN,
534 "supporter_count" INT4,
535 "informed_supporter_count" INT4,
536 "satisfied_supporter_count" INT4,
537 "satisfied_informed_supporter_count" INT4,
538 "positive_votes" INT4,
539 "negative_votes" INT4,
540 "attainable" BOOLEAN,
541 "favored" BOOLEAN,
542 "unfavored" BOOLEAN,
543 "eligible" BOOLEAN,
544 "rank" INT4,
545 "winner" BOOLEAN,
546 "promising" BOOLEAN,
547 "text_search_data" TSVECTOR,
548 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
549 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL),
550 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
551 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
552 CONSTRAINT "revoked_initiatives_cant_be_admitted"
553 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
554 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
555 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
556 ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND
557 "attainable" ISNULL AND "favored" ISNULL AND "unfavored" ISNULL AND
558 "eligible" ISNULL AND "rank" ISNULL AND
559 "winner" ISNULL AND "promising" ISNULL ) ),
560 CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_attainable_must_be_null"
561 CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "attainable" NOTNULL),
562 CONSTRAINT "favored_excludes_unfavored" CHECK (NOT ("favored" AND "unfavored")),
563 CONSTRAINT "attainable_and_favored_result_in_eligible" CHECK (
564 ( "favored" ISNULL AND "eligible" ISNULL ) OR
565 ( "attainable" NOTNULL AND "favored" NOTNULL AND "eligible" NOTNULL AND
566 ("attainable" AND "favored") = "eligible" ) ),
567 CONSTRAINT "winner_excludes_promising" CHECK (NOT ("winner" AND "promising")) );
568 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
569 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
570 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
571 CREATE TRIGGER "update_text_search_data"
572 BEFORE INSERT OR UPDATE ON "initiative"
573 FOR EACH ROW EXECUTE PROCEDURE
574 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
575 "name", "discussion_url");
577 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.';
579 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
580 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
581 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoked the initiative';
582 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
583 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
584 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
585 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
586 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
587 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
588 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
589 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"';
590 COMMENT ON COLUMN "initiative"."favored" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)';
591 COMMENT ON COLUMN "initiative"."unfavored" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)';
592 COMMENT ON COLUMN "initiative"."eligible" IS 'TRUE, if initiative is "attainable" and "favored"';
593 COMMENT ON COLUMN "initiative"."rank" IS 'Schulze-Ranking after tie-breaking';
594 COMMENT ON COLUMN "initiative"."winner" IS 'TRUE, if initiative is final winner (best ranked initiative being "eligible")';
595 COMMENT ON COLUMN "initiative"."promising" IS 'TRUE, if initiative would win against current winner, when voting is repeated with ballots based on same preferences. Non "attainable" initiatives may never be "winner", but they can be "promising".';
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;
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';
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");
622 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
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 );
632 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
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");
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.';
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"';
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 );
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)';
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");
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';
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';
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 );
712 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
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 );
720 COMMENT ON TABLE "invite_code_unit" IS 'Units where accounts created with a given invite codes get voting rights';
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 );
733 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
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';
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");
749 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
751 COMMENT ON COLUMN "membership"."autoreject" IS 'TRUE = member votes against all initiatives, if he is neither direct_ or delegating_voter; Entries in the "interest" table can override this setting.';
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 "autoreject" BOOLEAN,
759 "voting_requested" BOOLEAN );
760 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
762 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.';
764 COMMENT ON COLUMN "interest"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure';
765 COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply';
768 CREATE TABLE "initiator" (
769 PRIMARY KEY ("initiative_id", "member_id"),
770 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
771 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
772 "accepted" BOOLEAN );
773 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
775 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.';
777 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.';
780 CREATE TABLE "supporter" (
781 "issue_id" INT4 NOT NULL,
782 PRIMARY KEY ("initiative_id", "member_id"),
783 "initiative_id" INT4,
784 "member_id" INT4,
785 "draft_id" INT8 NOT NULL,
786 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
787 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
788 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
790 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.';
792 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
795 CREATE TABLE "opinion" (
796 "initiative_id" INT4 NOT NULL,
797 PRIMARY KEY ("suggestion_id", "member_id"),
798 "suggestion_id" INT8,
799 "member_id" INT4,
800 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
801 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
802 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
803 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
804 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
806 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.';
808 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
811 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
813 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
816 CREATE TABLE "delegation" (
817 "id" SERIAL8 PRIMARY KEY,
818 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
819 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
820 "scope" "delegation_scope" NOT NULL,
821 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
822 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
823 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
824 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
825 CONSTRAINT "no_unit_delegation_to_null"
826 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
827 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
828 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
829 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
830 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
831 UNIQUE ("unit_id", "truster_id"),
832 UNIQUE ("area_id", "truster_id"),
833 UNIQUE ("issue_id", "truster_id") );
834 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
835 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
837 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
839 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
840 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
841 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
844 CREATE TABLE "direct_population_snapshot" (
845 PRIMARY KEY ("issue_id", "event", "member_id"),
846 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
847 "event" "snapshot_event",
848 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
849 "weight" INT4 );
850 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
852 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
854 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
855 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
858 CREATE TABLE "delegating_population_snapshot" (
859 PRIMARY KEY ("issue_id", "event", "member_id"),
860 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
861 "event" "snapshot_event",
862 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
863 "weight" INT4,
864 "scope" "delegation_scope" NOT NULL,
865 "delegate_member_ids" INT4[] NOT NULL );
866 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
868 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
870 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
871 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
872 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
873 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"';
876 CREATE TABLE "direct_interest_snapshot" (
877 PRIMARY KEY ("issue_id", "event", "member_id"),
878 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
879 "event" "snapshot_event",
880 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
881 "weight" INT4,
882 "voting_requested" BOOLEAN );
883 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
885 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
887 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
888 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
889 COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';
892 CREATE TABLE "delegating_interest_snapshot" (
893 PRIMARY KEY ("issue_id", "event", "member_id"),
894 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
895 "event" "snapshot_event",
896 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
897 "weight" INT4,
898 "scope" "delegation_scope" NOT NULL,
899 "delegate_member_ids" INT4[] NOT NULL );
900 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
902 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
904 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
905 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
906 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
907 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"';
910 CREATE TABLE "direct_supporter_snapshot" (
911 "issue_id" INT4 NOT NULL,
912 PRIMARY KEY ("initiative_id", "event", "member_id"),
913 "initiative_id" INT4,
914 "event" "snapshot_event",
915 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
916 "informed" BOOLEAN NOT NULL,
917 "satisfied" BOOLEAN NOT NULL,
918 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
919 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
920 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
922 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
924 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
925 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
926 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
929 CREATE TABLE "non_voter" (
930 PRIMARY KEY ("issue_id", "member_id"),
931 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
932 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
933 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
935 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
938 CREATE TABLE "direct_voter" (
939 PRIMARY KEY ("issue_id", "member_id"),
940 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
941 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
942 "weight" INT4,
943 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
944 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
946 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.';
948 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
949 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
952 CREATE TABLE "delegating_voter" (
953 PRIMARY KEY ("issue_id", "member_id"),
954 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
955 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
956 "weight" INT4,
957 "scope" "delegation_scope" NOT NULL,
958 "delegate_member_ids" INT4[] NOT NULL );
959 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
961 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
963 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
964 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
965 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"';
968 CREATE TABLE "vote" (
969 "issue_id" INT4 NOT NULL,
970 PRIMARY KEY ("initiative_id", "member_id"),
971 "initiative_id" INT4,
972 "member_id" INT4,
973 "grade" INT4,
974 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
975 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
976 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
978 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.';
980 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.';
983 CREATE TABLE "issue_comment" (
984 PRIMARY KEY ("issue_id", "member_id"),
985 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
986 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
987 "changed" TIMESTAMPTZ NOT NULL DEFAULT now(),
988 "formatting_engine" TEXT,
989 "content" TEXT NOT NULL,
990 "text_search_data" TSVECTOR );
991 CREATE INDEX "issue_comment_member_id_idx" ON "issue_comment" ("member_id");
992 CREATE INDEX "issue_comment_text_search_data_idx" ON "issue_comment" USING gin ("text_search_data");
993 CREATE TRIGGER "update_text_search_data"
994 BEFORE INSERT OR UPDATE ON "issue_comment"
995 FOR EACH ROW EXECUTE PROCEDURE
996 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
998 COMMENT ON TABLE "issue_comment" IS 'Place to store free comments of members related to issues';
1000 COMMENT ON COLUMN "issue_comment"."changed" IS 'Time the comment was last changed';
1003 CREATE TABLE "rendered_issue_comment" (
1004 PRIMARY KEY ("issue_id", "member_id", "format"),
1005 FOREIGN KEY ("issue_id", "member_id")
1006 REFERENCES "issue_comment" ("issue_id", "member_id")
1007 ON DELETE CASCADE ON UPDATE CASCADE,
1008 "issue_id" INT4,
1009 "member_id" INT4,
1010 "format" TEXT,
1011 "content" TEXT NOT NULL );
1013 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)';
1016 CREATE TABLE "voting_comment" (
1017 PRIMARY KEY ("issue_id", "member_id"),
1018 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1019 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1020 "changed" TIMESTAMPTZ,
1021 "formatting_engine" TEXT,
1022 "content" TEXT NOT NULL,
1023 "text_search_data" TSVECTOR );
1024 CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id");
1025 CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data");
1026 CREATE TRIGGER "update_text_search_data"
1027 BEFORE INSERT OR UPDATE ON "voting_comment"
1028 FOR EACH ROW EXECUTE PROCEDURE
1029 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
1031 COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.';
1033 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.';
1036 CREATE TABLE "rendered_voting_comment" (
1037 PRIMARY KEY ("issue_id", "member_id", "format"),
1038 FOREIGN KEY ("issue_id", "member_id")
1039 REFERENCES "voting_comment" ("issue_id", "member_id")
1040 ON DELETE CASCADE ON UPDATE CASCADE,
1041 "issue_id" INT4,
1042 "member_id" INT4,
1043 "format" TEXT,
1044 "content" TEXT NOT NULL );
1046 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)';
1049 CREATE TYPE "event_type" AS ENUM (
1050 'issue_state_changed',
1051 'initiative_created_in_new_issue',
1052 'initiative_created_in_existing_issue',
1053 'initiative_revoked',
1054 'new_draft_created',
1055 'suggestion_created');
1057 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
1060 CREATE TABLE "event" (
1061 "id" SERIAL8 PRIMARY KEY,
1062 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
1063 "event" "event_type" NOT NULL,
1064 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1065 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1066 "state" "issue_state" CHECK ("state" != 'calculation'),
1067 "initiative_id" INT4,
1068 "draft_id" INT8,
1069 "suggestion_id" INT8,
1070 FOREIGN KEY ("issue_id", "initiative_id")
1071 REFERENCES "initiative" ("issue_id", "id")
1072 ON DELETE CASCADE ON UPDATE CASCADE,
1073 FOREIGN KEY ("initiative_id", "draft_id")
1074 REFERENCES "draft" ("initiative_id", "id")
1075 ON DELETE CASCADE ON UPDATE CASCADE,
1076 FOREIGN KEY ("initiative_id", "suggestion_id")
1077 REFERENCES "suggestion" ("initiative_id", "id")
1078 ON DELETE CASCADE ON UPDATE CASCADE,
1079 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
1080 "event" != 'issue_state_changed' OR (
1081 "member_id" ISNULL AND
1082 "issue_id" NOTNULL AND
1083 "state" NOTNULL AND
1084 "initiative_id" ISNULL AND
1085 "draft_id" ISNULL AND
1086 "suggestion_id" ISNULL )),
1087 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
1088 "event" NOT IN (
1089 'initiative_created_in_new_issue',
1090 'initiative_created_in_existing_issue',
1091 'initiative_revoked',
1092 'new_draft_created'
1093 ) OR (
1094 "member_id" NOTNULL AND
1095 "issue_id" NOTNULL AND
1096 "state" NOTNULL AND
1097 "initiative_id" NOTNULL AND
1098 "draft_id" NOTNULL AND
1099 "suggestion_id" ISNULL )),
1100 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
1101 "event" != 'suggestion_created' OR (
1102 "member_id" NOTNULL AND
1103 "issue_id" NOTNULL AND
1104 "state" NOTNULL AND
1105 "initiative_id" NOTNULL AND
1106 "draft_id" ISNULL AND
1107 "suggestion_id" NOTNULL )) );
1109 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
1111 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
1112 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';
1113 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';
1114 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';
1118 ----------------------------------------------
1119 -- Writing of history entries and event log --
1120 ----------------------------------------------
1122 CREATE FUNCTION "write_member_history_trigger"()
1123 RETURNS TRIGGER
1124 LANGUAGE 'plpgsql' VOLATILE AS $$
1125 BEGIN
1126 IF
1127 NEW."active" != OLD."active" OR
1128 NEW."name" != OLD."name"
1129 THEN
1130 INSERT INTO "member_history"
1131 ("member_id", "active", "name")
1132 VALUES (NEW."id", OLD."active", OLD."name");
1133 END IF;
1134 RETURN NULL;
1135 END;
1136 $$;
1138 CREATE TRIGGER "write_member_history"
1139 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1140 "write_member_history_trigger"();
1142 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
1143 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
1146 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
1147 RETURNS TRIGGER
1148 LANGUAGE 'plpgsql' VOLATILE AS $$
1149 BEGIN
1150 IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN
1151 INSERT INTO "event" ("event", "issue_id", "state")
1152 VALUES ('issue_state_changed', NEW."id", NEW."state");
1153 END IF;
1154 RETURN NULL;
1155 END;
1156 $$;
1158 CREATE TRIGGER "write_event_issue_state_changed"
1159 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
1160 "write_event_issue_state_changed_trigger"();
1162 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
1163 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
1166 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1167 RETURNS TRIGGER
1168 LANGUAGE 'plpgsql' VOLATILE AS $$
1169 DECLARE
1170 "initiative_row" "initiative"%ROWTYPE;
1171 "issue_row" "issue"%ROWTYPE;
1172 "event_v" "event_type";
1173 BEGIN
1174 SELECT * INTO "initiative_row" FROM "initiative"
1175 WHERE "id" = NEW."initiative_id";
1176 SELECT * INTO "issue_row" FROM "issue"
1177 WHERE "id" = "initiative_row"."issue_id";
1178 IF EXISTS (
1179 SELECT NULL FROM "draft"
1180 WHERE "initiative_id" = NEW."initiative_id"
1181 AND "id" != NEW."id"
1182 ) THEN
1183 "event_v" := 'new_draft_created';
1184 ELSE
1185 IF EXISTS (
1186 SELECT NULL FROM "initiative"
1187 WHERE "issue_id" = "initiative_row"."issue_id"
1188 AND "id" != "initiative_row"."id"
1189 ) THEN
1190 "event_v" := 'initiative_created_in_existing_issue';
1191 ELSE
1192 "event_v" := 'initiative_created_in_new_issue';
1193 END IF;
1194 END IF;
1195 INSERT INTO "event" (
1196 "event", "member_id",
1197 "issue_id", "state", "initiative_id", "draft_id"
1198 ) VALUES (
1199 "event_v",
1200 NEW."author_id",
1201 "initiative_row"."issue_id",
1202 "issue_row"."state",
1203 "initiative_row"."id",
1204 NEW."id" );
1205 RETURN NULL;
1206 END;
1207 $$;
1209 CREATE TRIGGER "write_event_initiative_or_draft_created"
1210 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
1211 "write_event_initiative_or_draft_created_trigger"();
1213 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
1214 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
1217 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
1218 RETURNS TRIGGER
1219 LANGUAGE 'plpgsql' VOLATILE AS $$
1220 DECLARE
1221 "issue_row" "issue"%ROWTYPE;
1222 BEGIN
1223 SELECT * INTO "issue_row" FROM "issue"
1224 WHERE "id" = NEW."issue_id";
1225 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1226 INSERT INTO "event" (
1227 "event", "member_id", "issue_id", "state", "initiative_id"
1228 ) VALUES (
1229 'initiative_revoked',
1230 NEW."revoked_by_member_id",
1231 NEW."issue_id",
1232 "issue_row"."state",
1233 NEW."id" );
1234 END IF;
1235 RETURN NULL;
1236 END;
1237 $$;
1239 CREATE TRIGGER "write_event_initiative_revoked"
1240 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
1241 "write_event_initiative_revoked_trigger"();
1243 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
1244 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
1247 CREATE FUNCTION "write_event_suggestion_created_trigger"()
1248 RETURNS TRIGGER
1249 LANGUAGE 'plpgsql' VOLATILE AS $$
1250 DECLARE
1251 "initiative_row" "initiative"%ROWTYPE;
1252 "issue_row" "issue"%ROWTYPE;
1253 BEGIN
1254 SELECT * INTO "initiative_row" FROM "initiative"
1255 WHERE "id" = NEW."initiative_id";
1256 SELECT * INTO "issue_row" FROM "issue"
1257 WHERE "id" = "initiative_row"."issue_id";
1258 INSERT INTO "event" (
1259 "event", "member_id",
1260 "issue_id", "state", "initiative_id", "suggestion_id"
1261 ) VALUES (
1262 'suggestion_created',
1263 NEW."author_id",
1264 "initiative_row"."issue_id",
1265 "issue_row"."state",
1266 "initiative_row"."id",
1267 NEW."id" );
1268 RETURN NULL;
1269 END;
1270 $$;
1272 CREATE TRIGGER "write_event_suggestion_created"
1273 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1274 "write_event_suggestion_created_trigger"();
1276 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
1277 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1281 ----------------------------
1282 -- Additional constraints --
1283 ----------------------------
1286 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
1287 RETURNS TRIGGER
1288 LANGUAGE 'plpgsql' VOLATILE AS $$
1289 BEGIN
1290 IF NOT EXISTS (
1291 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
1292 ) THEN
1293 --RAISE 'Cannot create issue without an initial initiative.' USING
1294 -- ERRCODE = 'integrity_constraint_violation',
1295 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
1296 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
1297 END IF;
1298 RETURN NULL;
1299 END;
1300 $$;
1302 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
1303 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
1304 FOR EACH ROW EXECUTE PROCEDURE
1305 "issue_requires_first_initiative_trigger"();
1307 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
1308 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
1311 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
1312 RETURNS TRIGGER
1313 LANGUAGE 'plpgsql' VOLATILE AS $$
1314 DECLARE
1315 "reference_lost" BOOLEAN;
1316 BEGIN
1317 IF TG_OP = 'DELETE' THEN
1318 "reference_lost" := TRUE;
1319 ELSE
1320 "reference_lost" := NEW."issue_id" != OLD."issue_id";
1321 END IF;
1322 IF
1323 "reference_lost" AND NOT EXISTS (
1324 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
1325 )
1326 THEN
1327 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
1328 END IF;
1329 RETURN NULL;
1330 END;
1331 $$;
1333 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
1334 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1335 FOR EACH ROW EXECUTE PROCEDURE
1336 "last_initiative_deletes_issue_trigger"();
1338 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
1339 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
1342 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
1343 RETURNS TRIGGER
1344 LANGUAGE 'plpgsql' VOLATILE AS $$
1345 BEGIN
1346 IF NOT EXISTS (
1347 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
1348 ) THEN
1349 --RAISE 'Cannot create initiative without an initial draft.' USING
1350 -- ERRCODE = 'integrity_constraint_violation',
1351 -- HINT = 'Create issue, initiative and draft within the same transaction.';
1352 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
1353 END IF;
1354 RETURN NULL;
1355 END;
1356 $$;
1358 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
1359 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1360 FOR EACH ROW EXECUTE PROCEDURE
1361 "initiative_requires_first_draft_trigger"();
1363 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
1364 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
1367 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
1368 RETURNS TRIGGER
1369 LANGUAGE 'plpgsql' VOLATILE AS $$
1370 DECLARE
1371 "reference_lost" BOOLEAN;
1372 BEGIN
1373 IF TG_OP = 'DELETE' THEN
1374 "reference_lost" := TRUE;
1375 ELSE
1376 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
1377 END IF;
1378 IF
1379 "reference_lost" AND NOT EXISTS (
1380 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
1381 )
1382 THEN
1383 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
1384 END IF;
1385 RETURN NULL;
1386 END;
1387 $$;
1389 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
1390 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
1391 FOR EACH ROW EXECUTE PROCEDURE
1392 "last_draft_deletes_initiative_trigger"();
1394 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
1395 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
1398 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
1399 RETURNS TRIGGER
1400 LANGUAGE 'plpgsql' VOLATILE AS $$
1401 BEGIN
1402 IF NOT EXISTS (
1403 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
1404 ) THEN
1405 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
1406 END IF;
1407 RETURN NULL;
1408 END;
1409 $$;
1411 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
1412 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
1413 FOR EACH ROW EXECUTE PROCEDURE
1414 "suggestion_requires_first_opinion_trigger"();
1416 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1417 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1420 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1421 RETURNS TRIGGER
1422 LANGUAGE 'plpgsql' VOLATILE AS $$
1423 DECLARE
1424 "reference_lost" BOOLEAN;
1425 BEGIN
1426 IF TG_OP = 'DELETE' THEN
1427 "reference_lost" := TRUE;
1428 ELSE
1429 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1430 END IF;
1431 IF
1432 "reference_lost" AND NOT EXISTS (
1433 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1434 )
1435 THEN
1436 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1437 END IF;
1438 RETURN NULL;
1439 END;
1440 $$;
1442 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1443 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1444 FOR EACH ROW EXECUTE PROCEDURE
1445 "last_opinion_deletes_suggestion_trigger"();
1447 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1448 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1452 ---------------------------------------------------------------
1453 -- Ensure that votes are not modified when issues are frozen --
1454 ---------------------------------------------------------------
1456 -- NOTE: Frontends should ensure this anyway, but in case of programming
1457 -- errors the following triggers ensure data integrity.
1460 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1461 RETURNS TRIGGER
1462 LANGUAGE 'plpgsql' VOLATILE AS $$
1463 DECLARE
1464 "issue_id_v" "issue"."id"%TYPE;
1465 "issue_row" "issue"%ROWTYPE;
1466 BEGIN
1467 IF TG_OP = 'DELETE' THEN
1468 "issue_id_v" := OLD."issue_id";
1469 ELSE
1470 "issue_id_v" := NEW."issue_id";
1471 END IF;
1472 SELECT INTO "issue_row" * FROM "issue"
1473 WHERE "id" = "issue_id_v" FOR SHARE;
1474 IF "issue_row"."closed" NOTNULL THEN
1475 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
1476 END IF;
1477 RETURN NULL;
1478 END;
1479 $$;
1481 CREATE TRIGGER "forbid_changes_on_closed_issue"
1482 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1483 FOR EACH ROW EXECUTE PROCEDURE
1484 "forbid_changes_on_closed_issue_trigger"();
1486 CREATE TRIGGER "forbid_changes_on_closed_issue"
1487 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1488 FOR EACH ROW EXECUTE PROCEDURE
1489 "forbid_changes_on_closed_issue_trigger"();
1491 CREATE TRIGGER "forbid_changes_on_closed_issue"
1492 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1493 FOR EACH ROW EXECUTE PROCEDURE
1494 "forbid_changes_on_closed_issue_trigger"();
1496 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"';
1497 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';
1498 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';
1499 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 -- Auto-retrieval of fields only needed for referential integrity --
1505 --------------------------------------------------------------------
1508 CREATE FUNCTION "autofill_issue_id_trigger"()
1509 RETURNS TRIGGER
1510 LANGUAGE 'plpgsql' VOLATILE AS $$
1511 BEGIN
1512 IF NEW."issue_id" ISNULL THEN
1513 SELECT "issue_id" INTO NEW."issue_id"
1514 FROM "initiative" WHERE "id" = NEW."initiative_id";
1515 END IF;
1516 RETURN NEW;
1517 END;
1518 $$;
1520 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1521 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1523 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1524 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1526 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1527 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1528 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1531 CREATE FUNCTION "autofill_initiative_id_trigger"()
1532 RETURNS TRIGGER
1533 LANGUAGE 'plpgsql' VOLATILE AS $$
1534 BEGIN
1535 IF NEW."initiative_id" ISNULL THEN
1536 SELECT "initiative_id" INTO NEW."initiative_id"
1537 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1538 END IF;
1539 RETURN NEW;
1540 END;
1541 $$;
1543 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1544 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1546 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1547 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1551 -----------------------------------------------------
1552 -- Automatic calculation of certain default values --
1553 -----------------------------------------------------
1556 CREATE FUNCTION "copy_timings_trigger"()
1557 RETURNS TRIGGER
1558 LANGUAGE 'plpgsql' VOLATILE AS $$
1559 DECLARE
1560 "policy_row" "policy"%ROWTYPE;
1561 BEGIN
1562 SELECT * INTO "policy_row" FROM "policy"
1563 WHERE "id" = NEW."policy_id";
1564 IF NEW."admission_time" ISNULL THEN
1565 NEW."admission_time" := "policy_row"."admission_time";
1566 END IF;
1567 IF NEW."discussion_time" ISNULL THEN
1568 NEW."discussion_time" := "policy_row"."discussion_time";
1569 END IF;
1570 IF NEW."verification_time" ISNULL THEN
1571 NEW."verification_time" := "policy_row"."verification_time";
1572 END IF;
1573 IF NEW."voting_time" ISNULL THEN
1574 NEW."voting_time" := "policy_row"."voting_time";
1575 END IF;
1576 RETURN NEW;
1577 END;
1578 $$;
1580 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1581 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1583 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1584 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1587 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
1588 RETURNS TRIGGER
1589 LANGUAGE 'plpgsql' VOLATILE AS $$
1590 BEGIN
1591 IF NEW."draft_id" ISNULL THEN
1592 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1593 WHERE "initiative_id" = NEW."initiative_id";
1594 END IF;
1595 RETURN NEW;
1596 END;
1597 $$;
1599 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1600 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
1602 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
1603 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 -- Automatic creation of dependencies --
1609 ----------------------------------------
1612 CREATE FUNCTION "autocreate_interest_trigger"()
1613 RETURNS TRIGGER
1614 LANGUAGE 'plpgsql' VOLATILE AS $$
1615 BEGIN
1616 IF NOT EXISTS (
1617 SELECT NULL FROM "initiative" JOIN "interest"
1618 ON "initiative"."issue_id" = "interest"."issue_id"
1619 WHERE "initiative"."id" = NEW."initiative_id"
1620 AND "interest"."member_id" = NEW."member_id"
1621 ) THEN
1622 BEGIN
1623 INSERT INTO "interest" ("issue_id", "member_id")
1624 SELECT "issue_id", NEW."member_id"
1625 FROM "initiative" WHERE "id" = NEW."initiative_id";
1626 EXCEPTION WHEN unique_violation THEN END;
1627 END IF;
1628 RETURN NEW;
1629 END;
1630 $$;
1632 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1633 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1635 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1636 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';
1639 CREATE FUNCTION "autocreate_supporter_trigger"()
1640 RETURNS TRIGGER
1641 LANGUAGE 'plpgsql' VOLATILE AS $$
1642 BEGIN
1643 IF NOT EXISTS (
1644 SELECT NULL FROM "suggestion" JOIN "supporter"
1645 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1646 WHERE "suggestion"."id" = NEW."suggestion_id"
1647 AND "supporter"."member_id" = NEW."member_id"
1648 ) THEN
1649 BEGIN
1650 INSERT INTO "supporter" ("initiative_id", "member_id")
1651 SELECT "initiative_id", NEW."member_id"
1652 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1653 EXCEPTION WHEN unique_violation THEN END;
1654 END IF;
1655 RETURN NEW;
1656 END;
1657 $$;
1659 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1660 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1662 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1663 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 -- Views and helper functions for views --
1669 ------------------------------------------
1672 CREATE VIEW "unit_delegation" AS
1673 SELECT
1674 "unit"."id" AS "unit_id",
1675 "delegation"."id",
1676 "delegation"."truster_id",
1677 "delegation"."trustee_id",
1678 "delegation"."scope"
1679 FROM "unit"
1680 JOIN "delegation"
1681 ON "delegation"."unit_id" = "unit"."id"
1682 JOIN "member"
1683 ON "delegation"."truster_id" = "member"."id"
1684 JOIN "privilege"
1685 ON "delegation"."unit_id" = "privilege"."unit_id"
1686 AND "delegation"."truster_id" = "privilege"."member_id"
1687 WHERE "member"."active" AND "privilege"."voting_right";
1689 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
1692 CREATE VIEW "area_delegation" AS
1693 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1694 "area"."id" AS "area_id",
1695 "delegation"."id",
1696 "delegation"."truster_id",
1697 "delegation"."trustee_id",
1698 "delegation"."scope"
1699 FROM "area"
1700 JOIN "delegation"
1701 ON "delegation"."unit_id" = "area"."unit_id"
1702 OR "delegation"."area_id" = "area"."id"
1703 JOIN "member"
1704 ON "delegation"."truster_id" = "member"."id"
1705 JOIN "privilege"
1706 ON "area"."unit_id" = "privilege"."unit_id"
1707 AND "delegation"."truster_id" = "privilege"."member_id"
1708 WHERE "member"."active" AND "privilege"."voting_right"
1709 ORDER BY
1710 "area"."id",
1711 "delegation"."truster_id",
1712 "delegation"."scope" DESC;
1714 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
1717 CREATE VIEW "issue_delegation" AS
1718 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1719 "issue"."id" AS "issue_id",
1720 "delegation"."id",
1721 "delegation"."truster_id",
1722 "delegation"."trustee_id",
1723 "delegation"."scope"
1724 FROM "issue"
1725 JOIN "area"
1726 ON "area"."id" = "issue"."area_id"
1727 JOIN "delegation"
1728 ON "delegation"."unit_id" = "area"."unit_id"
1729 OR "delegation"."area_id" = "area"."id"
1730 OR "delegation"."issue_id" = "issue"."id"
1731 JOIN "member"
1732 ON "delegation"."truster_id" = "member"."id"
1733 JOIN "privilege"
1734 ON "area"."unit_id" = "privilege"."unit_id"
1735 AND "delegation"."truster_id" = "privilege"."member_id"
1736 WHERE "member"."active" AND "privilege"."voting_right"
1737 ORDER BY
1738 "issue"."id",
1739 "delegation"."truster_id",
1740 "delegation"."scope" DESC;
1742 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
1745 CREATE FUNCTION "membership_weight_with_skipping"
1746 ( "area_id_p" "area"."id"%TYPE,
1747 "member_id_p" "member"."id"%TYPE,
1748 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1749 RETURNS INT4
1750 LANGUAGE 'plpgsql' STABLE AS $$
1751 DECLARE
1752 "sum_v" INT4;
1753 "delegation_row" "area_delegation"%ROWTYPE;
1754 BEGIN
1755 "sum_v" := 1;
1756 FOR "delegation_row" IN
1757 SELECT "area_delegation".*
1758 FROM "area_delegation" LEFT JOIN "membership"
1759 ON "membership"."area_id" = "area_id_p"
1760 AND "membership"."member_id" = "area_delegation"."truster_id"
1761 WHERE "area_delegation"."area_id" = "area_id_p"
1762 AND "area_delegation"."trustee_id" = "member_id_p"
1763 AND "membership"."member_id" ISNULL
1764 LOOP
1765 IF NOT
1766 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1767 THEN
1768 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1769 "area_id_p",
1770 "delegation_row"."truster_id",
1771 "skip_member_ids_p" || "delegation_row"."truster_id"
1772 );
1773 END IF;
1774 END LOOP;
1775 RETURN "sum_v";
1776 END;
1777 $$;
1779 COMMENT ON FUNCTION "membership_weight_with_skipping"
1780 ( "area"."id"%TYPE,
1781 "member"."id"%TYPE,
1782 INT4[] )
1783 IS 'Helper function for "membership_weight" function';
1786 CREATE FUNCTION "membership_weight"
1787 ( "area_id_p" "area"."id"%TYPE,
1788 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1789 RETURNS INT4
1790 LANGUAGE 'plpgsql' STABLE AS $$
1791 BEGIN
1792 RETURN "membership_weight_with_skipping"(
1793 "area_id_p",
1794 "member_id_p",
1795 ARRAY["member_id_p"]
1796 );
1797 END;
1798 $$;
1800 COMMENT ON FUNCTION "membership_weight"
1801 ( "area"."id"%TYPE,
1802 "member"."id"%TYPE )
1803 IS 'Calculates the potential voting weight of a member in a given area';
1806 CREATE VIEW "member_count_view" AS
1807 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1809 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1812 CREATE VIEW "unit_member_count" AS
1813 SELECT
1814 "unit"."id" AS "unit_id",
1815 sum("member"."id") AS "member_count"
1816 FROM "unit"
1817 LEFT JOIN "privilege"
1818 ON "privilege"."unit_id" = "unit"."id"
1819 AND "privilege"."voting_right"
1820 LEFT JOIN "member"
1821 ON "member"."id" = "privilege"."member_id"
1822 AND "member"."active"
1823 GROUP BY "unit"."id";
1825 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
1828 CREATE VIEW "area_member_count" AS
1829 SELECT
1830 "area"."id" AS "area_id",
1831 count("member"."id") AS "direct_member_count",
1832 coalesce(
1833 sum(
1834 CASE WHEN "member"."id" NOTNULL THEN
1835 "membership_weight"("area"."id", "member"."id")
1836 ELSE 0 END
1837 )
1838 ) AS "member_weight",
1839 coalesce(
1840 sum(
1841 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
1842 "membership_weight"("area"."id", "member"."id")
1843 ELSE 0 END
1844 )
1845 ) AS "autoreject_weight"
1846 FROM "area"
1847 LEFT JOIN "membership"
1848 ON "area"."id" = "membership"."area_id"
1849 LEFT JOIN "privilege"
1850 ON "privilege"."unit_id" = "area"."unit_id"
1851 AND "privilege"."member_id" = "membership"."member_id"
1852 AND "privilege"."voting_right"
1853 LEFT JOIN "member"
1854 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
1855 AND "member"."active"
1856 GROUP BY "area"."id";
1858 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count", "member_weight" and "autoreject_weight" columns of table "area"';
1861 CREATE VIEW "opening_draft" AS
1862 SELECT "draft".* FROM (
1863 SELECT
1864 "initiative"."id" AS "initiative_id",
1865 min("draft"."id") AS "draft_id"
1866 FROM "initiative" JOIN "draft"
1867 ON "initiative"."id" = "draft"."initiative_id"
1868 GROUP BY "initiative"."id"
1869 ) AS "subquery"
1870 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1872 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1875 CREATE VIEW "current_draft" AS
1876 SELECT "draft".* FROM (
1877 SELECT
1878 "initiative"."id" AS "initiative_id",
1879 max("draft"."id") AS "draft_id"
1880 FROM "initiative" JOIN "draft"
1881 ON "initiative"."id" = "draft"."initiative_id"
1882 GROUP BY "initiative"."id"
1883 ) AS "subquery"
1884 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1886 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1889 CREATE VIEW "critical_opinion" AS
1890 SELECT * FROM "opinion"
1891 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
1892 OR ("degree" = -2 AND "fulfilled" = TRUE);
1894 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
1897 CREATE VIEW "battle_participant" AS
1898 SELECT "initiative"."id", "initiative"."issue_id"
1899 FROM "issue" JOIN "initiative"
1900 ON "issue"."id" = "initiative"."issue_id"
1901 WHERE "initiative"."admitted"
1902 UNION ALL
1903 SELECT NULL, "id" AS "issue_id"
1904 FROM "issue";
1906 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
1909 CREATE VIEW "battle_view" AS
1910 SELECT
1911 "issue"."id" AS "issue_id",
1912 "winning_initiative"."id" AS "winning_initiative_id",
1913 "losing_initiative"."id" AS "losing_initiative_id",
1914 sum(
1915 CASE WHEN
1916 coalesce("better_vote"."grade", 0) >
1917 coalesce("worse_vote"."grade", 0)
1918 THEN "direct_voter"."weight" ELSE 0 END
1919 ) AS "count"
1920 FROM "issue"
1921 LEFT JOIN "direct_voter"
1922 ON "issue"."id" = "direct_voter"."issue_id"
1923 JOIN "battle_participant" AS "winning_initiative"
1924 ON "issue"."id" = "winning_initiative"."issue_id"
1925 JOIN "battle_participant" AS "losing_initiative"
1926 ON "issue"."id" = "losing_initiative"."issue_id"
1927 LEFT JOIN "vote" AS "better_vote"
1928 ON "direct_voter"."member_id" = "better_vote"."member_id"
1929 AND "winning_initiative"."id" = "better_vote"."initiative_id"
1930 LEFT JOIN "vote" AS "worse_vote"
1931 ON "direct_voter"."member_id" = "worse_vote"."member_id"
1932 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
1933 WHERE "issue"."closed" NOTNULL
1934 AND "issue"."cleaned" ISNULL
1935 AND (
1936 "winning_initiative"."id" != "losing_initiative"."id" OR
1937 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
1938 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
1939 GROUP BY
1940 "issue"."id",
1941 "winning_initiative"."id",
1942 "losing_initiative"."id";
1944 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';
1947 CREATE VIEW "expired_session" AS
1948 SELECT * FROM "session" WHERE now() > "expiry";
1950 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
1951 DELETE FROM "session" WHERE "ident" = OLD."ident";
1953 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
1954 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
1957 CREATE VIEW "open_issue" AS
1958 SELECT * FROM "issue" WHERE "closed" ISNULL;
1960 COMMENT ON VIEW "open_issue" IS 'All open issues';
1963 CREATE VIEW "issue_with_ranks_missing" AS
1964 SELECT * FROM "issue"
1965 WHERE "fully_frozen" NOTNULL
1966 AND "closed" NOTNULL
1967 AND "ranks_available" = FALSE;
1969 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
1972 CREATE VIEW "member_contingent" AS
1973 SELECT
1974 "member"."id" AS "member_id",
1975 "contingent"."time_frame",
1976 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
1977 (
1978 SELECT count(1) FROM "draft"
1979 WHERE "draft"."author_id" = "member"."id"
1980 AND "draft"."created" > now() - "contingent"."time_frame"
1981 ) + (
1982 SELECT count(1) FROM "suggestion"
1983 WHERE "suggestion"."author_id" = "member"."id"
1984 AND "suggestion"."created" > now() - "contingent"."time_frame"
1985 )
1986 ELSE NULL END AS "text_entry_count",
1987 "contingent"."text_entry_limit",
1988 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
1989 SELECT count(1) FROM "opening_draft"
1990 WHERE "opening_draft"."author_id" = "member"."id"
1991 AND "opening_draft"."created" > now() - "contingent"."time_frame"
1992 ) ELSE NULL END AS "initiative_count",
1993 "contingent"."initiative_limit"
1994 FROM "member" CROSS JOIN "contingent";
1996 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
1998 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
1999 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
2002 CREATE VIEW "member_contingent_left" AS
2003 SELECT
2004 "member_id",
2005 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
2006 max("initiative_limit" - "initiative_count") AS "initiatives_left"
2007 FROM "member_contingent" GROUP BY "member_id";
2009 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.';
2012 CREATE VIEW "event_seen_by_member" AS
2013 SELECT
2014 "member"."id" AS "seen_by_member_id",
2015 CASE WHEN "event"."state" IN (
2016 'voting',
2017 'finished_without_winner',
2018 'finished_with_winner'
2019 ) THEN
2020 'voting'::"notify_level"
2021 ELSE
2022 CASE WHEN "event"."state" IN (
2023 'verification',
2024 'canceled_after_revocation_during_verification',
2025 'canceled_no_initiative_admitted'
2026 ) THEN
2027 'verification'::"notify_level"
2028 ELSE
2029 CASE WHEN "event"."state" IN (
2030 'discussion',
2031 'canceled_after_revocation_during_discussion'
2032 ) THEN
2033 'discussion'::"notify_level"
2034 ELSE
2035 'all'::"notify_level"
2036 END
2037 END
2038 END AS "notify_level",
2039 "event".*
2040 FROM "member" CROSS JOIN "event"
2041 LEFT JOIN "issue"
2042 ON "event"."issue_id" = "issue"."id"
2043 LEFT JOIN "membership"
2044 ON "member"."id" = "membership"."member_id"
2045 AND "issue"."area_id" = "membership"."area_id"
2046 LEFT JOIN "interest"
2047 ON "member"."id" = "interest"."member_id"
2048 AND "event"."issue_id" = "interest"."issue_id"
2049 LEFT JOIN "supporter"
2050 ON "member"."id" = "supporter"."member_id"
2051 AND "event"."initiative_id" = "supporter"."initiative_id"
2052 LEFT JOIN "ignored_member"
2053 ON "member"."id" = "ignored_member"."member_id"
2054 AND "event"."member_id" = "ignored_member"."other_member_id"
2055 LEFT JOIN "ignored_initiative"
2056 ON "member"."id" = "ignored_initiative"."member_id"
2057 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2058 WHERE (
2059 "supporter"."member_id" NOTNULL OR
2060 "interest"."member_id" NOTNULL OR
2061 ( "membership"."member_id" NOTNULL AND
2062 "event"."event" IN (
2063 'issue_state_changed',
2064 'initiative_created_in_new_issue',
2065 'initiative_created_in_existing_issue',
2066 'initiative_revoked' ) ) )
2067 AND "ignored_member"."member_id" ISNULL
2068 AND "ignored_initiative"."member_id" ISNULL;
2070 COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support';
2073 CREATE VIEW "pending_notification" AS
2074 SELECT
2075 "member"."id" AS "seen_by_member_id",
2076 "event".*
2077 FROM "member" CROSS JOIN "event"
2078 LEFT JOIN "issue"
2079 ON "event"."issue_id" = "issue"."id"
2080 LEFT JOIN "membership"
2081 ON "member"."id" = "membership"."member_id"
2082 AND "issue"."area_id" = "membership"."area_id"
2083 LEFT JOIN "interest"
2084 ON "member"."id" = "interest"."member_id"
2085 AND "event"."issue_id" = "interest"."issue_id"
2086 LEFT JOIN "supporter"
2087 ON "member"."id" = "supporter"."member_id"
2088 AND "event"."initiative_id" = "supporter"."initiative_id"
2089 LEFT JOIN "ignored_member"
2090 ON "member"."id" = "ignored_member"."member_id"
2091 AND "event"."member_id" = "ignored_member"."other_member_id"
2092 LEFT JOIN "ignored_initiative"
2093 ON "member"."id" = "ignored_initiative"."member_id"
2094 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2095 WHERE (
2096 "member"."notify_event_id" ISNULL OR
2097 ( "member"."notify_event_id" NOTNULL AND
2098 "member"."notify_event_id" < "event"."id" ) )
2099 AND (
2100 ( "member"."notify_level" >= 'all' ) OR
2101 ( "member"."notify_level" >= 'voting' AND
2102 "event"."state" IN (
2103 'voting',
2104 'finished_without_winner',
2105 'finished_with_winner' ) ) OR
2106 ( "member"."notify_level" >= 'verification' AND
2107 "event"."state" IN (
2108 'verification',
2109 'canceled_after_revocation_during_verification',
2110 'canceled_no_initiative_admitted' ) ) OR
2111 ( "member"."notify_level" >= 'discussion' AND
2112 "event"."state" IN (
2113 'discussion',
2114 'canceled_after_revocation_during_discussion' ) ) )
2115 AND (
2116 "supporter"."member_id" NOTNULL OR
2117 "interest"."member_id" NOTNULL OR
2118 ( "membership"."member_id" NOTNULL AND
2119 "event"."event" IN (
2120 'issue_state_changed',
2121 'initiative_created_in_new_issue',
2122 'initiative_created_in_existing_issue',
2123 'initiative_revoked' ) ) )
2124 AND "ignored_member"."member_id" ISNULL
2125 AND "ignored_initiative"."member_id" ISNULL;
2127 COMMENT ON VIEW "pending_notification" IS 'Events to be sent to "notify_email" address of member referred to by "seen_by_member_id"';
2130 CREATE TYPE "timeline_event" AS ENUM (
2131 'issue_created',
2132 'issue_canceled',
2133 'issue_accepted',
2134 'issue_half_frozen',
2135 'issue_finished_without_voting',
2136 'issue_voting_started',
2137 'issue_finished_after_voting',
2138 'initiative_created',
2139 'initiative_revoked',
2140 'draft_created',
2141 'suggestion_created');
2143 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)';
2146 CREATE VIEW "timeline_issue" AS
2147 SELECT
2148 "created" AS "occurrence",
2149 'issue_created'::"timeline_event" AS "event",
2150 "id" AS "issue_id"
2151 FROM "issue"
2152 UNION ALL
2153 SELECT
2154 "closed" AS "occurrence",
2155 'issue_canceled'::"timeline_event" AS "event",
2156 "id" AS "issue_id"
2157 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
2158 UNION ALL
2159 SELECT
2160 "accepted" AS "occurrence",
2161 'issue_accepted'::"timeline_event" AS "event",
2162 "id" AS "issue_id"
2163 FROM "issue" WHERE "accepted" NOTNULL
2164 UNION ALL
2165 SELECT
2166 "half_frozen" AS "occurrence",
2167 'issue_half_frozen'::"timeline_event" AS "event",
2168 "id" AS "issue_id"
2169 FROM "issue" WHERE "half_frozen" NOTNULL
2170 UNION ALL
2171 SELECT
2172 "fully_frozen" AS "occurrence",
2173 'issue_voting_started'::"timeline_event" AS "event",
2174 "id" AS "issue_id"
2175 FROM "issue"
2176 WHERE "fully_frozen" NOTNULL
2177 AND ("closed" ISNULL OR "closed" != "fully_frozen")
2178 UNION ALL
2179 SELECT
2180 "closed" AS "occurrence",
2181 CASE WHEN "fully_frozen" = "closed" THEN
2182 'issue_finished_without_voting'::"timeline_event"
2183 ELSE
2184 'issue_finished_after_voting'::"timeline_event"
2185 END AS "event",
2186 "id" AS "issue_id"
2187 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
2189 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)';
2192 CREATE VIEW "timeline_initiative" AS
2193 SELECT
2194 "created" AS "occurrence",
2195 'initiative_created'::"timeline_event" AS "event",
2196 "id" AS "initiative_id"
2197 FROM "initiative"
2198 UNION ALL
2199 SELECT
2200 "revoked" AS "occurrence",
2201 'initiative_revoked'::"timeline_event" AS "event",
2202 "id" AS "initiative_id"
2203 FROM "initiative" WHERE "revoked" NOTNULL;
2205 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)';
2208 CREATE VIEW "timeline_draft" AS
2209 SELECT
2210 "created" AS "occurrence",
2211 'draft_created'::"timeline_event" AS "event",
2212 "id" AS "draft_id"
2213 FROM "draft";
2215 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)';
2218 CREATE VIEW "timeline_suggestion" AS
2219 SELECT
2220 "created" AS "occurrence",
2221 'suggestion_created'::"timeline_event" AS "event",
2222 "id" AS "suggestion_id"
2223 FROM "suggestion";
2225 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)';
2228 CREATE VIEW "timeline" AS
2229 SELECT
2230 "occurrence",
2231 "event",
2232 "issue_id",
2233 NULL AS "initiative_id",
2234 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
2235 NULL::INT8 AS "suggestion_id"
2236 FROM "timeline_issue"
2237 UNION ALL
2238 SELECT
2239 "occurrence",
2240 "event",
2241 NULL AS "issue_id",
2242 "initiative_id",
2243 NULL AS "draft_id",
2244 NULL AS "suggestion_id"
2245 FROM "timeline_initiative"
2246 UNION ALL
2247 SELECT
2248 "occurrence",
2249 "event",
2250 NULL AS "issue_id",
2251 NULL AS "initiative_id",
2252 "draft_id",
2253 NULL AS "suggestion_id"
2254 FROM "timeline_draft"
2255 UNION ALL
2256 SELECT
2257 "occurrence",
2258 "event",
2259 NULL AS "issue_id",
2260 NULL AS "initiative_id",
2261 NULL AS "draft_id",
2262 "suggestion_id"
2263 FROM "timeline_suggestion";
2265 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)';
2269 --------------------------------------------------
2270 -- Set returning function for delegation chains --
2271 --------------------------------------------------
2274 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
2275 ('first', 'intermediate', 'last', 'repetition');
2277 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
2280 CREATE TYPE "delegation_chain_row" AS (
2281 "index" INT4,
2282 "member_id" INT4,
2283 "member_valid" BOOLEAN,
2284 "participation" BOOLEAN,
2285 "overridden" BOOLEAN,
2286 "scope_in" "delegation_scope",
2287 "scope_out" "delegation_scope",
2288 "disabled_out" BOOLEAN,
2289 "loop" "delegation_chain_loop_tag" );
2291 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
2293 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
2294 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';
2295 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
2296 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
2297 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
2298 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
2299 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
2302 CREATE FUNCTION "delegation_chain"
2303 ( "member_id_p" "member"."id"%TYPE,
2304 "unit_id_p" "unit"."id"%TYPE,
2305 "area_id_p" "area"."id"%TYPE,
2306 "issue_id_p" "issue"."id"%TYPE,
2307 "simulate_trustee_id_p" "member"."id"%TYPE )
2308 RETURNS SETOF "delegation_chain_row"
2309 LANGUAGE 'plpgsql' STABLE AS $$
2310 DECLARE
2311 "scope_v" "delegation_scope";
2312 "unit_id_v" "unit"."id"%TYPE;
2313 "area_id_v" "area"."id"%TYPE;
2314 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2315 "loop_member_id_v" "member"."id"%TYPE;
2316 "output_row" "delegation_chain_row";
2317 "output_rows" "delegation_chain_row"[];
2318 "delegation_row" "delegation"%ROWTYPE;
2319 "row_count" INT4;
2320 "i" INT4;
2321 "loop_v" BOOLEAN;
2322 BEGIN
2323 IF
2324 "unit_id_p" NOTNULL AND
2325 "area_id_p" ISNULL AND
2326 "issue_id_p" ISNULL
2327 THEN
2328 "scope_v" := 'unit';
2329 "unit_id_v" := "unit_id_p";
2330 ELSIF
2331 "unit_id_p" ISNULL AND
2332 "area_id_p" NOTNULL AND
2333 "issue_id_p" ISNULL
2334 THEN
2335 "scope_v" := 'area';
2336 "area_id_v" := "area_id_p";
2337 SELECT "unit_id" INTO "unit_id_v"
2338 FROM "area" WHERE "id" = "area_id_v";
2339 ELSIF
2340 "unit_id_p" ISNULL AND
2341 "area_id_p" ISNULL AND
2342 "issue_id_p" NOTNULL
2343 THEN
2344 "scope_v" := 'issue';
2345 SELECT "area_id" INTO "area_id_v"
2346 FROM "issue" WHERE "id" = "issue_id_p";
2347 SELECT "unit_id" INTO "unit_id_v"
2348 FROM "area" WHERE "id" = "area_id_v";
2349 ELSE
2350 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2351 END IF;
2352 "visited_member_ids" := '{}';
2353 "loop_member_id_v" := NULL;
2354 "output_rows" := '{}';
2355 "output_row"."index" := 0;
2356 "output_row"."member_id" := "member_id_p";
2357 "output_row"."member_valid" := TRUE;
2358 "output_row"."participation" := FALSE;
2359 "output_row"."overridden" := FALSE;
2360 "output_row"."disabled_out" := FALSE;
2361 "output_row"."scope_out" := NULL;
2362 LOOP
2363 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2364 "loop_member_id_v" := "output_row"."member_id";
2365 ELSE
2366 "visited_member_ids" :=
2367 "visited_member_ids" || "output_row"."member_id";
2368 END IF;
2369 IF "output_row"."participation" THEN
2370 "output_row"."overridden" := TRUE;
2371 END IF;
2372 "output_row"."scope_in" := "output_row"."scope_out";
2373 IF EXISTS (
2374 SELECT NULL FROM "member" JOIN "privilege"
2375 ON "privilege"."member_id" = "member"."id"
2376 AND "privilege"."unit_id" = "unit_id_v"
2377 WHERE "id" = "output_row"."member_id"
2378 AND "member"."active" AND "privilege"."voting_right"
2379 ) THEN
2380 IF "scope_v" = 'unit' THEN
2381 SELECT * INTO "delegation_row" FROM "delegation"
2382 WHERE "truster_id" = "output_row"."member_id"
2383 AND "unit_id" = "unit_id_v";
2384 ELSIF "scope_v" = 'area' THEN
2385 "output_row"."participation" := EXISTS (
2386 SELECT NULL FROM "membership"
2387 WHERE "area_id" = "area_id_p"
2388 AND "member_id" = "output_row"."member_id"
2389 );
2390 SELECT * INTO "delegation_row" FROM "delegation"
2391 WHERE "truster_id" = "output_row"."member_id"
2392 AND (
2393 "unit_id" = "unit_id_v" OR
2394 "area_id" = "area_id_v"
2395 )
2396 ORDER BY "scope" DESC;
2397 ELSIF "scope_v" = 'issue' THEN
2398 "output_row"."participation" := EXISTS (
2399 SELECT NULL FROM "interest"
2400 WHERE "issue_id" = "issue_id_p"
2401 AND "member_id" = "output_row"."member_id"
2402 );
2403 SELECT * INTO "delegation_row" FROM "delegation"
2404 WHERE "truster_id" = "output_row"."member_id"
2405 AND (
2406 "unit_id" = "unit_id_v" OR
2407 "area_id" = "area_id_v" OR
2408 "issue_id" = "issue_id_p"
2409 )
2410 ORDER BY "scope" DESC;
2411 END IF;
2412 ELSE
2413 "output_row"."member_valid" := FALSE;
2414 "output_row"."participation" := FALSE;
2415 "output_row"."scope_out" := NULL;
2416 "delegation_row" := ROW(NULL);
2417 END IF;
2418 IF
2419 "output_row"."member_id" = "member_id_p" AND
2420 "simulate_trustee_id_p" NOTNULL
2421 THEN
2422 "output_row"."scope_out" := "scope_v";
2423 "output_rows" := "output_rows" || "output_row";
2424 "output_row"."member_id" := "simulate_trustee_id_p";
2425 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2426 "output_row"."scope_out" := "delegation_row"."scope";
2427 "output_rows" := "output_rows" || "output_row";
2428 "output_row"."member_id" := "delegation_row"."trustee_id";
2429 ELSIF "delegation_row"."scope" NOTNULL THEN
2430 "output_row"."scope_out" := "delegation_row"."scope";
2431 "output_row"."disabled_out" := TRUE;
2432 "output_rows" := "output_rows" || "output_row";
2433 EXIT;
2434 ELSE
2435 "output_row"."scope_out" := NULL;
2436 "output_rows" := "output_rows" || "output_row";
2437 EXIT;
2438 END IF;
2439 EXIT WHEN "loop_member_id_v" NOTNULL;
2440 "output_row"."index" := "output_row"."index" + 1;
2441 END LOOP;
2442 "row_count" := array_upper("output_rows", 1);
2443 "i" := 1;
2444 "loop_v" := FALSE;
2445 LOOP
2446 "output_row" := "output_rows"["i"];
2447 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2448 IF "loop_v" THEN
2449 IF "i" + 1 = "row_count" THEN
2450 "output_row"."loop" := 'last';
2451 ELSIF "i" = "row_count" THEN
2452 "output_row"."loop" := 'repetition';
2453 ELSE
2454 "output_row"."loop" := 'intermediate';
2455 END IF;
2456 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2457 "output_row"."loop" := 'first';
2458 "loop_v" := TRUE;
2459 END IF;
2460 IF "scope_v" = 'unit' THEN
2461 "output_row"."participation" := NULL;
2462 END IF;
2463 RETURN NEXT "output_row";
2464 "i" := "i" + 1;
2465 END LOOP;
2466 RETURN;
2467 END;
2468 $$;
2470 COMMENT ON FUNCTION "delegation_chain"
2471 ( "member"."id"%TYPE,
2472 "unit"."id"%TYPE,
2473 "area"."id"%TYPE,
2474 "issue"."id"%TYPE,
2475 "member"."id"%TYPE )
2476 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
2479 CREATE FUNCTION "delegation_chain"
2480 ( "member_id_p" "member"."id"%TYPE,
2481 "unit_id_p" "unit"."id"%TYPE,
2482 "area_id_p" "area"."id"%TYPE,
2483 "issue_id_p" "issue"."id"%TYPE )
2484 RETURNS SETOF "delegation_chain_row"
2485 LANGUAGE 'plpgsql' STABLE AS $$
2486 DECLARE
2487 "result_row" "delegation_chain_row";
2488 BEGIN
2489 FOR "result_row" IN
2490 SELECT * FROM "delegation_chain"(
2491 "member_id_p", "unit_id_p", "area_id_p", "issue_id_p", NULL
2492 )
2493 LOOP
2494 RETURN NEXT "result_row";
2495 END LOOP;
2496 RETURN;
2497 END;
2498 $$;
2500 COMMENT ON FUNCTION "delegation_chain"
2501 ( "member"."id"%TYPE,
2502 "unit"."id"%TYPE,
2503 "area"."id"%TYPE,
2504 "issue"."id"%TYPE )
2505 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
2509 ------------------------------
2510 -- Comparison by vote count --
2511 ------------------------------
2513 CREATE FUNCTION "vote_ratio"
2514 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
2515 "negative_votes_p" "initiative"."negative_votes"%TYPE )
2516 RETURNS FLOAT8
2517 LANGUAGE 'plpgsql' STABLE AS $$
2518 BEGIN
2519 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
2520 RETURN
2521 "positive_votes_p"::FLOAT8 /
2522 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
2523 ELSIF "positive_votes_p" > 0 THEN
2524 RETURN "positive_votes_p";
2525 ELSIF "negative_votes_p" > 0 THEN
2526 RETURN 1 - "negative_votes_p";
2527 ELSE
2528 RETURN 0.5;
2529 END IF;
2530 END;
2531 $$;
2533 COMMENT ON FUNCTION "vote_ratio"
2534 ( "initiative"."positive_votes"%TYPE,
2535 "initiative"."negative_votes"%TYPE )
2536 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 -- Locking for snapshots and voting procedure --
2542 ------------------------------------------------
2545 CREATE FUNCTION "share_row_lock_issue_trigger"()
2546 RETURNS TRIGGER
2547 LANGUAGE 'plpgsql' VOLATILE AS $$
2548 BEGIN
2549 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2550 PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;
2551 END IF;
2552 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2553 PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;
2554 RETURN NEW;
2555 ELSE
2556 RETURN OLD;
2557 END IF;
2558 END;
2559 $$;
2561 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';
2564 CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()
2565 RETURNS TRIGGER
2566 LANGUAGE 'plpgsql' VOLATILE AS $$
2567 BEGIN
2568 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2569 PERFORM NULL FROM "issue"
2570 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2571 WHERE "initiative"."id" = OLD."initiative_id"
2572 FOR SHARE OF "issue";
2573 END IF;
2574 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2575 PERFORM NULL FROM "issue"
2576 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2577 WHERE "initiative"."id" = NEW."initiative_id"
2578 FOR SHARE OF "issue";
2579 RETURN NEW;
2580 ELSE
2581 RETURN OLD;
2582 END IF;
2583 END;
2584 $$;
2586 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';
2589 CREATE TRIGGER "share_row_lock_issue"
2590 BEFORE INSERT OR UPDATE OR DELETE ON "initiative"
2591 FOR EACH ROW EXECUTE PROCEDURE
2592 "share_row_lock_issue_trigger"();
2594 CREATE TRIGGER "share_row_lock_issue"
2595 BEFORE INSERT OR UPDATE OR DELETE ON "interest"
2596 FOR EACH ROW EXECUTE PROCEDURE
2597 "share_row_lock_issue_trigger"();
2599 CREATE TRIGGER "share_row_lock_issue"
2600 BEFORE INSERT OR UPDATE OR DELETE ON "supporter"
2601 FOR EACH ROW EXECUTE PROCEDURE
2602 "share_row_lock_issue_trigger"();
2604 CREATE TRIGGER "share_row_lock_issue_via_initiative"
2605 BEFORE INSERT OR UPDATE OR DELETE ON "opinion"
2606 FOR EACH ROW EXECUTE PROCEDURE
2607 "share_row_lock_issue_via_initiative_trigger"();
2609 CREATE TRIGGER "share_row_lock_issue"
2610 BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter"
2611 FOR EACH ROW EXECUTE PROCEDURE
2612 "share_row_lock_issue_trigger"();
2614 CREATE TRIGGER "share_row_lock_issue"
2615 BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter"
2616 FOR EACH ROW EXECUTE PROCEDURE
2617 "share_row_lock_issue_trigger"();
2619 CREATE TRIGGER "share_row_lock_issue"
2620 BEFORE INSERT OR UPDATE OR DELETE ON "vote"
2621 FOR EACH ROW EXECUTE PROCEDURE
2622 "share_row_lock_issue_trigger"();
2624 COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function';
2625 COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function';
2626 COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function';
2627 COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function';
2628 COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function';
2629 COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function';
2630 COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function';
2633 CREATE FUNCTION "lock_issue"
2634 ( "issue_id_p" "issue"."id"%TYPE )
2635 RETURNS VOID
2636 LANGUAGE 'plpgsql' VOLATILE AS $$
2637 BEGIN
2638 LOCK TABLE "member" IN SHARE MODE;
2639 LOCK TABLE "privilege" IN SHARE MODE;
2640 LOCK TABLE "membership" IN SHARE MODE;
2641 LOCK TABLE "policy" IN SHARE MODE;
2642 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2643 -- NOTE: The row-level exclusive lock in combination with the
2644 -- share_row_lock_issue(_via_initiative)_trigger functions (which
2645 -- acquire a row-level share lock on the issue) ensure that no data
2646 -- is changed, which could affect calculation of snapshots or
2647 -- counting of votes. Table "delegation" must be table-level-locked,
2648 -- as it also contains issue- and global-scope delegations.
2649 LOCK TABLE "delegation" IN SHARE MODE;
2650 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
2651 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
2652 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
2653 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
2654 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
2655 RETURN;
2656 END;
2657 $$;
2659 COMMENT ON FUNCTION "lock_issue"
2660 ( "issue"."id"%TYPE )
2661 IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';
2665 ------------------------------------------------------------------------
2666 -- Regular tasks, except calculcation of snapshots and voting results --
2667 ------------------------------------------------------------------------
2669 CREATE FUNCTION "check_last_login"()
2670 RETURNS VOID
2671 LANGUAGE 'plpgsql' VOLATILE AS $$
2672 DECLARE
2673 "system_setting_row" "system_setting"%ROWTYPE;
2674 BEGIN
2675 SELECT * INTO "system_setting_row" FROM "system_setting";
2676 LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
2677 UPDATE "member" SET "last_login_public" = "last_login"::date
2678 FROM (
2679 SELECT DISTINCT "member"."id"
2680 FROM "member" LEFT JOIN "member_history"
2681 ON "member"."id" = "member_history"."member_id"
2682 WHERE "member"."last_login"::date < 'today' OR (
2683 "member_history"."until"::date >= 'today' AND
2684 "member_history"."active" = FALSE AND "member"."active" = TRUE
2685 )
2686 ) AS "subquery"
2687 WHERE "member"."id" = "subquery"."id";
2688 IF "system_setting_row"."member_ttl" NOTNULL THEN
2689 UPDATE "member" SET "active" = FALSE
2690 WHERE "active" = TRUE
2691 AND "last_login"::date < 'today'
2692 AND "last_login_public" <
2693 (now() - "system_setting_row"."member_ttl")::date;
2694 END IF;
2695 RETURN;
2696 END;
2697 $$;
2699 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).';
2702 CREATE FUNCTION "calculate_member_counts"()
2703 RETURNS VOID
2704 LANGUAGE 'plpgsql' VOLATILE AS $$
2705 BEGIN
2706 LOCK TABLE "member" IN SHARE MODE;
2707 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
2708 LOCK TABLE "unit" IN EXCLUSIVE MODE;
2709 LOCK TABLE "area" IN EXCLUSIVE MODE;
2710 LOCK TABLE "privilege" IN SHARE MODE;
2711 LOCK TABLE "membership" IN SHARE MODE;
2712 DELETE FROM "member_count";
2713 INSERT INTO "member_count" ("total_count")
2714 SELECT "total_count" FROM "member_count_view";
2715 UPDATE "unit" SET "member_count" = "view"."member_count"
2716 FROM "unit_member_count" AS "view"
2717 WHERE "view"."unit_id" = "unit"."id";
2718 UPDATE "area" SET
2719 "direct_member_count" = "view"."direct_member_count",
2720 "member_weight" = "view"."member_weight",
2721 "autoreject_weight" = "view"."autoreject_weight"
2722 FROM "area_member_count" AS "view"
2723 WHERE "view"."area_id" = "area"."id";
2724 RETURN;
2725 END;
2726 $$;
2728 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 -- Calculation of snapshots --
2734 ------------------------------
2736 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
2737 ( "issue_id_p" "issue"."id"%TYPE,
2738 "member_id_p" "member"."id"%TYPE,
2739 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2740 RETURNS "direct_population_snapshot"."weight"%TYPE
2741 LANGUAGE 'plpgsql' VOLATILE AS $$
2742 DECLARE
2743 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2744 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
2745 "weight_v" INT4;
2746 "sub_weight_v" INT4;
2747 BEGIN
2748 "weight_v" := 0;
2749 FOR "issue_delegation_row" IN
2750 SELECT * FROM "issue_delegation"
2751 WHERE "trustee_id" = "member_id_p"
2752 AND "issue_id" = "issue_id_p"
2753 LOOP
2754 IF NOT EXISTS (
2755 SELECT NULL FROM "direct_population_snapshot"
2756 WHERE "issue_id" = "issue_id_p"
2757 AND "event" = 'periodic'
2758 AND "member_id" = "issue_delegation_row"."truster_id"
2759 ) AND NOT EXISTS (
2760 SELECT NULL FROM "delegating_population_snapshot"
2761 WHERE "issue_id" = "issue_id_p"
2762 AND "event" = 'periodic'
2763 AND "member_id" = "issue_delegation_row"."truster_id"
2764 ) THEN
2765 "delegate_member_ids_v" :=
2766 "member_id_p" || "delegate_member_ids_p";
2767 INSERT INTO "delegating_population_snapshot" (
2768 "issue_id",
2769 "event",
2770 "member_id",
2771 "scope",
2772 "delegate_member_ids"
2773 ) VALUES (
2774 "issue_id_p",
2775 'periodic',
2776 "issue_delegation_row"."truster_id",
2777 "issue_delegation_row"."scope",
2778 "delegate_member_ids_v"
2779 );
2780 "sub_weight_v" := 1 +
2781 "weight_of_added_delegations_for_population_snapshot"(
2782 "issue_id_p",
2783 "issue_delegation_row"."truster_id",
2784 "delegate_member_ids_v"
2785 );
2786 UPDATE "delegating_population_snapshot"
2787 SET "weight" = "sub_weight_v"
2788 WHERE "issue_id" = "issue_id_p"
2789 AND "event" = 'periodic'
2790 AND "member_id" = "issue_delegation_row"."truster_id";
2791 "weight_v" := "weight_v" + "sub_weight_v";
2792 END IF;
2793 END LOOP;
2794 RETURN "weight_v";
2795 END;
2796 $$;
2798 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
2799 ( "issue"."id"%TYPE,
2800 "member"."id"%TYPE,
2801 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2802 IS 'Helper function for "create_population_snapshot" function';
2805 CREATE FUNCTION "create_population_snapshot"
2806 ( "issue_id_p" "issue"."id"%TYPE )
2807 RETURNS VOID
2808 LANGUAGE 'plpgsql' VOLATILE AS $$
2809 DECLARE
2810 "member_id_v" "member"."id"%TYPE;
2811 BEGIN
2812 DELETE FROM "direct_population_snapshot"
2813 WHERE "issue_id" = "issue_id_p"
2814 AND "event" = 'periodic';
2815 DELETE FROM "delegating_population_snapshot"
2816 WHERE "issue_id" = "issue_id_p"
2817 AND "event" = 'periodic';
2818 INSERT INTO "direct_population_snapshot"
2819 ("issue_id", "event", "member_id")
2820 SELECT
2821 "issue_id_p" AS "issue_id",
2822 'periodic'::"snapshot_event" AS "event",
2823 "member"."id" AS "member_id"
2824 FROM "issue"
2825 JOIN "area" ON "issue"."area_id" = "area"."id"
2826 JOIN "membership" ON "area"."id" = "membership"."area_id"
2827 JOIN "member" ON "membership"."member_id" = "member"."id"
2828 JOIN "privilege"
2829 ON "privilege"."unit_id" = "area"."unit_id"
2830 AND "privilege"."member_id" = "member"."id"
2831 WHERE "issue"."id" = "issue_id_p"
2832 AND "member"."active" AND "privilege"."voting_right"
2833 UNION
2834 SELECT
2835 "issue_id_p" AS "issue_id",
2836 'periodic'::"snapshot_event" AS "event",
2837 "member"."id" AS "member_id"
2838 FROM "issue"
2839 JOIN "area" ON "issue"."area_id" = "area"."id"
2840 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2841 JOIN "member" ON "interest"."member_id" = "member"."id"
2842 JOIN "privilege"
2843 ON "privilege"."unit_id" = "area"."unit_id"
2844 AND "privilege"."member_id" = "member"."id"
2845 WHERE "issue"."id" = "issue_id_p"
2846 AND "member"."active" AND "privilege"."voting_right";
2847 FOR "member_id_v" IN
2848 SELECT "member_id" FROM "direct_population_snapshot"
2849 WHERE "issue_id" = "issue_id_p"
2850 AND "event" = 'periodic'
2851 LOOP
2852 UPDATE "direct_population_snapshot" SET
2853 "weight" = 1 +
2854 "weight_of_added_delegations_for_population_snapshot"(
2855 "issue_id_p",
2856 "member_id_v",
2857 '{}'
2858 )
2859 WHERE "issue_id" = "issue_id_p"
2860 AND "event" = 'periodic'
2861 AND "member_id" = "member_id_v";
2862 END LOOP;
2863 RETURN;
2864 END;
2865 $$;
2867 COMMENT ON FUNCTION "create_population_snapshot"
2868 ( "issue"."id"%TYPE )
2869 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.';
2872 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2873 ( "issue_id_p" "issue"."id"%TYPE,
2874 "member_id_p" "member"."id"%TYPE,
2875 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2876 RETURNS "direct_interest_snapshot"."weight"%TYPE
2877 LANGUAGE 'plpgsql' VOLATILE AS $$
2878 DECLARE
2879 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2880 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2881 "weight_v" INT4;
2882 "sub_weight_v" INT4;
2883 BEGIN
2884 "weight_v" := 0;
2885 FOR "issue_delegation_row" IN
2886 SELECT * FROM "issue_delegation"
2887 WHERE "trustee_id" = "member_id_p"
2888 AND "issue_id" = "issue_id_p"
2889 LOOP
2890 IF NOT EXISTS (
2891 SELECT NULL FROM "direct_interest_snapshot"
2892 WHERE "issue_id" = "issue_id_p"
2893 AND "event" = 'periodic'
2894 AND "member_id" = "issue_delegation_row"."truster_id"
2895 ) AND NOT EXISTS (
2896 SELECT NULL FROM "delegating_interest_snapshot"
2897 WHERE "issue_id" = "issue_id_p"
2898 AND "event" = 'periodic'
2899 AND "member_id" = "issue_delegation_row"."truster_id"
2900 ) THEN
2901 "delegate_member_ids_v" :=
2902 "member_id_p" || "delegate_member_ids_p";
2903 INSERT INTO "delegating_interest_snapshot" (
2904 "issue_id",
2905 "event",
2906 "member_id",
2907 "scope",
2908 "delegate_member_ids"
2909 ) VALUES (
2910 "issue_id_p",
2911 'periodic',
2912 "issue_delegation_row"."truster_id",
2913 "issue_delegation_row"."scope",
2914 "delegate_member_ids_v"
2915 );
2916 "sub_weight_v" := 1 +
2917 "weight_of_added_delegations_for_interest_snapshot"(
2918 "issue_id_p",
2919 "issue_delegation_row"."truster_id",
2920 "delegate_member_ids_v"
2921 );
2922 UPDATE "delegating_interest_snapshot"
2923 SET "weight" = "sub_weight_v"
2924 WHERE "issue_id" = "issue_id_p"
2925 AND "event" = 'periodic'
2926 AND "member_id" = "issue_delegation_row"."truster_id";
2927 "weight_v" := "weight_v" + "sub_weight_v";
2928 END IF;
2929 END LOOP;
2930 RETURN "weight_v";
2931 END;
2932 $$;
2934 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2935 ( "issue"."id"%TYPE,
2936 "member"."id"%TYPE,
2937 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2938 IS 'Helper function for "create_interest_snapshot" function';
2941 CREATE FUNCTION "create_interest_snapshot"
2942 ( "issue_id_p" "issue"."id"%TYPE )
2943 RETURNS VOID
2944 LANGUAGE 'plpgsql' VOLATILE AS $$
2945 DECLARE
2946 "member_id_v" "member"."id"%TYPE;
2947 BEGIN
2948 DELETE FROM "direct_interest_snapshot"
2949 WHERE "issue_id" = "issue_id_p"
2950 AND "event" = 'periodic';
2951 DELETE FROM "delegating_interest_snapshot"
2952 WHERE "issue_id" = "issue_id_p"
2953 AND "event" = 'periodic';
2954 DELETE FROM "direct_supporter_snapshot"
2955 WHERE "issue_id" = "issue_id_p"
2956 AND "event" = 'periodic';
2957 INSERT INTO "direct_interest_snapshot"
2958 ("issue_id", "event", "member_id", "voting_requested")
2959 SELECT
2960 "issue_id_p" AS "issue_id",
2961 'periodic' AS "event",
2962 "member"."id" AS "member_id",
2963 "interest"."voting_requested"
2964 FROM "issue"
2965 JOIN "area" ON "issue"."area_id" = "area"."id"
2966 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2967 JOIN "member" ON "interest"."member_id" = "member"."id"
2968 JOIN "privilege"
2969 ON "privilege"."unit_id" = "area"."unit_id"
2970 AND "privilege"."member_id" = "member"."id"
2971 WHERE "issue"."id" = "issue_id_p"
2972 AND "member"."active" AND "privilege"."voting_right";
2973 FOR "member_id_v" IN
2974 SELECT "member_id" FROM "direct_interest_snapshot"
2975 WHERE "issue_id" = "issue_id_p"
2976 AND "event" = 'periodic'
2977 LOOP
2978 UPDATE "direct_interest_snapshot" SET
2979 "weight" = 1 +
2980 "weight_of_added_delegations_for_interest_snapshot"(
2981 "issue_id_p",
2982 "member_id_v",
2983 '{}'
2984 )
2985 WHERE "issue_id" = "issue_id_p"
2986 AND "event" = 'periodic'
2987 AND "member_id" = "member_id_v";
2988 END LOOP;
2989 INSERT INTO "direct_supporter_snapshot"
2990 ( "issue_id", "initiative_id", "event", "member_id",
2991 "informed", "satisfied" )
2992 SELECT
2993 "issue_id_p" AS "issue_id",
2994 "initiative"."id" AS "initiative_id",
2995 'periodic' AS "event",
2996 "supporter"."member_id" AS "member_id",
2997 "supporter"."draft_id" = "current_draft"."id" AS "informed",
2998 NOT EXISTS (
2999 SELECT NULL FROM "critical_opinion"
3000 WHERE "initiative_id" = "initiative"."id"
3001 AND "member_id" = "supporter"."member_id"
3002 ) AS "satisfied"
3003 FROM "initiative"
3004 JOIN "supporter"
3005 ON "supporter"."initiative_id" = "initiative"."id"
3006 JOIN "current_draft"
3007 ON "initiative"."id" = "current_draft"."initiative_id"
3008 JOIN "direct_interest_snapshot"
3009 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
3010 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
3011 AND "event" = 'periodic'
3012 WHERE "initiative"."issue_id" = "issue_id_p";
3013 RETURN;
3014 END;
3015 $$;
3017 COMMENT ON FUNCTION "create_interest_snapshot"
3018 ( "issue"."id"%TYPE )
3019 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.';
3022 CREATE FUNCTION "create_snapshot"
3023 ( "issue_id_p" "issue"."id"%TYPE )
3024 RETURNS VOID
3025 LANGUAGE 'plpgsql' VOLATILE AS $$
3026 DECLARE
3027 "initiative_id_v" "initiative"."id"%TYPE;
3028 "suggestion_id_v" "suggestion"."id"%TYPE;
3029 BEGIN
3030 PERFORM "lock_issue"("issue_id_p");
3031 PERFORM "create_population_snapshot"("issue_id_p");
3032 PERFORM "create_interest_snapshot"("issue_id_p");
3033 UPDATE "issue" SET
3034 "snapshot" = now(),
3035 "latest_snapshot_event" = 'periodic',
3036 "population" = (
3037 SELECT coalesce(sum("weight"), 0)
3038 FROM "direct_population_snapshot"
3039 WHERE "issue_id" = "issue_id_p"
3040 AND "event" = 'periodic'
3041 ),
3042 "vote_now" = (
3043 SELECT coalesce(sum("weight"), 0)
3044 FROM "direct_interest_snapshot"
3045 WHERE "issue_id" = "issue_id_p"
3046 AND "event" = 'periodic'
3047 AND "voting_requested" = TRUE
3048 ),
3049 "vote_later" = (
3050 SELECT coalesce(sum("weight"), 0)
3051 FROM "direct_interest_snapshot"
3052 WHERE "issue_id" = "issue_id_p"
3053 AND "event" = 'periodic'
3054 AND "voting_requested" = FALSE
3055 )
3056 WHERE "id" = "issue_id_p";
3057 FOR "initiative_id_v" IN
3058 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
3059 LOOP
3060 UPDATE "initiative" SET
3061 "supporter_count" = (
3062 SELECT coalesce(sum("di"."weight"), 0)
3063 FROM "direct_interest_snapshot" AS "di"
3064 JOIN "direct_supporter_snapshot" AS "ds"
3065 ON "di"."member_id" = "ds"."member_id"
3066 WHERE "di"."issue_id" = "issue_id_p"
3067 AND "di"."event" = 'periodic'
3068 AND "ds"."initiative_id" = "initiative_id_v"
3069 AND "ds"."event" = 'periodic'
3070 ),
3071 "informed_supporter_count" = (
3072 SELECT coalesce(sum("di"."weight"), 0)
3073 FROM "direct_interest_snapshot" AS "di"
3074 JOIN "direct_supporter_snapshot" AS "ds"
3075 ON "di"."member_id" = "ds"."member_id"
3076 WHERE "di"."issue_id" = "issue_id_p"
3077 AND "di"."event" = 'periodic'
3078 AND "ds"."initiative_id" = "initiative_id_v"
3079 AND "ds"."event" = 'periodic'
3080 AND "ds"."informed"
3081 ),
3082 "satisfied_supporter_count" = (
3083 SELECT coalesce(sum("di"."weight"), 0)
3084 FROM "direct_interest_snapshot" AS "di"
3085 JOIN "direct_supporter_snapshot" AS "ds"
3086 ON "di"."member_id" = "ds"."member_id"
3087 WHERE "di"."issue_id" = "issue_id_p"
3088 AND "di"."event" = 'periodic'
3089 AND "ds"."initiative_id" = "initiative_id_v"
3090 AND "ds"."event" = 'periodic'
3091 AND "ds"."satisfied"
3092 ),
3093 "satisfied_informed_supporter_count" = (
3094 SELECT coalesce(sum("di"."weight"), 0)
3095 FROM "direct_interest_snapshot" AS "di"
3096 JOIN "direct_supporter_snapshot" AS "ds"
3097 ON "di"."member_id" = "ds"."member_id"
3098 WHERE "di"."issue_id" = "issue_id_p"
3099 AND "di"."event" = 'periodic'
3100 AND "ds"."initiative_id" = "initiative_id_v"
3101 AND "ds"."event" = 'periodic'
3102 AND "ds"."informed"
3103 AND "ds"."satisfied"
3104 )
3105 WHERE "id" = "initiative_id_v";
3106 FOR "suggestion_id_v" IN
3107 SELECT "id" FROM "suggestion"
3108 WHERE "initiative_id" = "initiative_id_v"
3109 LOOP
3110 UPDATE "suggestion" SET
3111 "minus2_unfulfilled_count" = (
3112 SELECT coalesce(sum("snapshot"."weight"), 0)
3113 FROM "issue" CROSS JOIN "opinion"
3114 JOIN "direct_interest_snapshot" AS "snapshot"
3115 ON "snapshot"."issue_id" = "issue"."id"
3116 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3117 AND "snapshot"."member_id" = "opinion"."member_id"
3118 WHERE "issue"."id" = "issue_id_p"
3119 AND "opinion"."suggestion_id" = "suggestion_id_v"
3120 AND "opinion"."degree" = -2
3121 AND "opinion"."fulfilled" = FALSE
3122 ),
3123 "minus2_fulfilled_count" = (
3124 SELECT coalesce(sum("snapshot"."weight"), 0)
3125 FROM "issue" CROSS JOIN "opinion"
3126 JOIN "direct_interest_snapshot" AS "snapshot"
3127 ON "snapshot"."issue_id" = "issue"."id"
3128 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3129 AND "snapshot"."member_id" = "opinion"."member_id"
3130 WHERE "issue"."id" = "issue_id_p"
3131 AND "opinion"."suggestion_id" = "suggestion_id_v"
3132 AND "opinion"."degree" = -2
3133 AND "opinion"."fulfilled" = TRUE
3134 ),
3135 "minus1_unfulfilled_count" = (
3136 SELECT coalesce(sum("snapshot"."weight"), 0)
3137 FROM "issue" CROSS JOIN "opinion"
3138 JOIN "direct_interest_snapshot" AS "snapshot"
3139 ON "snapshot"."issue_id" = "issue"."id"
3140 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3141 AND "snapshot"."member_id" = "opinion"."member_id"
3142 WHERE "issue"."id" = "issue_id_p"
3143 AND "opinion"."suggestion_id" = "suggestion_id_v"
3144 AND "opinion"."degree" = -1
3145 AND "opinion"."fulfilled" = FALSE
3146 ),
3147 "minus1_fulfilled_count" = (
3148 SELECT coalesce(sum("snapshot"."weight"), 0)
3149 FROM "issue" CROSS JOIN "opinion"
3150 JOIN "direct_interest_snapshot" AS "snapshot"
3151 ON "snapshot"."issue_id" = "issue"."id"
3152 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3153 AND "snapshot"."member_id" = "opinion"."member_id"
3154 WHERE "issue"."id" = "issue_id_p"
3155 AND "opinion"."suggestion_id" = "suggestion_id_v"
3156 AND "opinion"."degree" = -1
3157 AND "opinion"."fulfilled" = TRUE
3158 ),
3159 "plus1_unfulfilled_count" = (
3160 SELECT coalesce(sum("snapshot"."weight"), 0)
3161 FROM "issue" CROSS JOIN "opinion"
3162 JOIN "direct_interest_snapshot" AS "snapshot"
3163 ON "snapshot"."issue_id" = "issue"."id"
3164 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3165 AND "snapshot"."member_id" = "opinion"."member_id"
3166 WHERE "issue"."id" = "issue_id_p"
3167 AND "opinion"."suggestion_id" = "suggestion_id_v"
3168 AND "opinion"."degree" = 1
3169 AND "opinion"."fulfilled" = FALSE
3170 ),
3171 "plus1_fulfilled_count" = (
3172 SELECT coalesce(sum("snapshot"."weight"), 0)
3173 FROM "issue" CROSS JOIN "opinion"
3174 JOIN "direct_interest_snapshot" AS "snapshot"
3175 ON "snapshot"."issue_id" = "issue"."id"
3176 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3177 AND "snapshot"."member_id" = "opinion"."member_id"
3178 WHERE "issue"."id" = "issue_id_p"
3179 AND "opinion"."suggestion_id" = "suggestion_id_v"
3180 AND "opinion"."degree" = 1
3181 AND "opinion"."fulfilled" = TRUE
3182 ),
3183 "plus2_unfulfilled_count" = (
3184 SELECT coalesce(sum("snapshot"."weight"), 0)
3185 FROM "issue" CROSS JOIN "opinion"
3186 JOIN "direct_interest_snapshot" AS "snapshot"
3187 ON "snapshot"."issue_id" = "issue"."id"
3188 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3189 AND "snapshot"."member_id" = "opinion"."member_id"
3190 WHERE "issue"."id" = "issue_id_p"
3191 AND "opinion"."suggestion_id" = "suggestion_id_v"
3192 AND "opinion"."degree" = 2
3193 AND "opinion"."fulfilled" = FALSE
3194 ),
3195 "plus2_fulfilled_count" = (
3196 SELECT coalesce(sum("snapshot"."weight"), 0)
3197 FROM "issue" CROSS JOIN "opinion"
3198 JOIN "direct_interest_snapshot" AS "snapshot"
3199 ON "snapshot"."issue_id" = "issue"."id"
3200 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3201 AND "snapshot"."member_id" = "opinion"."member_id"
3202 WHERE "issue"."id" = "issue_id_p"
3203 AND "opinion"."suggestion_id" = "suggestion_id_v"
3204 AND "opinion"."degree" = 2
3205 AND "opinion"."fulfilled" = TRUE
3206 )
3207 WHERE "suggestion"."id" = "suggestion_id_v";
3208 END LOOP;
3209 END LOOP;
3210 RETURN;
3211 END;
3212 $$;
3214 COMMENT ON FUNCTION "create_snapshot"
3215 ( "issue"."id"%TYPE )
3216 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 CREATE FUNCTION "set_snapshot_event"
3220 ( "issue_id_p" "issue"."id"%TYPE,
3221 "event_p" "snapshot_event" )
3222 RETURNS VOID
3223 LANGUAGE 'plpgsql' VOLATILE AS $$
3224 DECLARE
3225 "event_v" "issue"."latest_snapshot_event"%TYPE;
3226 BEGIN
3227 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
3228 WHERE "id" = "issue_id_p" FOR UPDATE;
3229 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
3230 WHERE "id" = "issue_id_p";
3231 UPDATE "direct_population_snapshot" SET "event" = "event_p"
3232 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3233 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
3234 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3235 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
3236 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3237 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
3238 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3239 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
3240 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3241 RETURN;
3242 END;
3243 $$;
3245 COMMENT ON FUNCTION "set_snapshot_event"
3246 ( "issue"."id"%TYPE,
3247 "snapshot_event" )
3248 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
3252 ---------------------
3253 -- Freezing issues --
3254 ---------------------
3256 CREATE FUNCTION "freeze_after_snapshot"
3257 ( "issue_id_p" "issue"."id"%TYPE )
3258 RETURNS VOID
3259 LANGUAGE 'plpgsql' VOLATILE AS $$
3260 DECLARE
3261 "issue_row" "issue"%ROWTYPE;
3262 "policy_row" "policy"%ROWTYPE;
3263 "initiative_row" "initiative"%ROWTYPE;
3264 BEGIN
3265 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3266 SELECT * INTO "policy_row"
3267 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3268 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
3269 FOR "initiative_row" IN
3270 SELECT * FROM "initiative"
3271 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3272 LOOP
3273 IF
3274 "initiative_row"."satisfied_supporter_count" > 0 AND
3275 "initiative_row"."satisfied_supporter_count" *
3276 "policy_row"."initiative_quorum_den" >=
3277 "issue_row"."population" * "policy_row"."initiative_quorum_num"
3278 THEN
3279 UPDATE "initiative" SET "admitted" = TRUE
3280 WHERE "id" = "initiative_row"."id";
3281 ELSE
3282 UPDATE "initiative" SET "admitted" = FALSE
3283 WHERE "id" = "initiative_row"."id";
3284 END IF;
3285 END LOOP;
3286 IF EXISTS (
3287 SELECT NULL FROM "initiative"
3288 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
3289 ) THEN
3290 UPDATE "issue" SET
3291 "state" = 'voting',
3292 "accepted" = coalesce("accepted", now()),
3293 "half_frozen" = coalesce("half_frozen", now()),
3294 "fully_frozen" = now()
3295 WHERE "id" = "issue_id_p";
3296 ELSE
3297 UPDATE "issue" SET
3298 "state" = 'canceled_no_initiative_admitted',
3299 "accepted" = coalesce("accepted", now()),
3300 "half_frozen" = coalesce("half_frozen", now()),
3301 "fully_frozen" = now(),
3302 "closed" = now(),
3303 "ranks_available" = TRUE
3304 WHERE "id" = "issue_id_p";
3305 -- NOTE: The following DELETE statements have effect only when
3306 -- issue state has been manipulated
3307 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
3308 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
3309 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3310 END IF;
3311 RETURN;
3312 END;
3313 $$;
3315 COMMENT ON FUNCTION "freeze_after_snapshot"
3316 ( "issue"."id"%TYPE )
3317 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 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
3321 RETURNS VOID
3322 LANGUAGE 'plpgsql' VOLATILE AS $$
3323 DECLARE
3324 "issue_row" "issue"%ROWTYPE;
3325 BEGIN
3326 PERFORM "create_snapshot"("issue_id_p");
3327 PERFORM "freeze_after_snapshot"("issue_id_p");
3328 RETURN;
3329 END;
3330 $$;
3332 COMMENT ON FUNCTION "manual_freeze"
3333 ( "issue"."id"%TYPE )
3334 IS 'Freeze an issue manually (fully) and start voting';
3338 -----------------------
3339 -- Counting of votes --
3340 -----------------------
3343 CREATE FUNCTION "weight_of_added_vote_delegations"
3344 ( "issue_id_p" "issue"."id"%TYPE,
3345 "member_id_p" "member"."id"%TYPE,
3346 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
3347 RETURNS "direct_voter"."weight"%TYPE
3348 LANGUAGE 'plpgsql' VOLATILE AS $$
3349 DECLARE
3350 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3351 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
3352 "weight_v" INT4;
3353 "sub_weight_v" INT4;
3354 BEGIN
3355 "weight_v" := 0;
3356 FOR "issue_delegation_row" IN
3357 SELECT * FROM "issue_delegation"
3358 WHERE "trustee_id" = "member_id_p"
3359 AND "issue_id" = "issue_id_p"
3360 LOOP
3361 IF NOT EXISTS (
3362 SELECT NULL FROM "direct_voter"
3363 WHERE "member_id" = "issue_delegation_row"."truster_id"
3364 AND "issue_id" = "issue_id_p"
3365 ) AND NOT EXISTS (
3366 SELECT NULL FROM "delegating_voter"
3367 WHERE "member_id" = "issue_delegation_row"."truster_id"
3368 AND "issue_id" = "issue_id_p"
3369 ) THEN
3370 "delegate_member_ids_v" :=
3371 "member_id_p" || "delegate_member_ids_p";
3372 INSERT INTO "delegating_voter" (
3373 "issue_id",
3374 "member_id",
3375 "scope",
3376 "delegate_member_ids"
3377 ) VALUES (
3378 "issue_id_p",
3379 "issue_delegation_row"."truster_id",
3380 "issue_delegation_row"."scope",
3381 "delegate_member_ids_v"
3382 );
3383 "sub_weight_v" := 1 +
3384 "weight_of_added_vote_delegations"(
3385 "issue_id_p",
3386 "issue_delegation_row"."truster_id",
3387 "delegate_member_ids_v"
3388 );
3389 UPDATE "delegating_voter"
3390 SET "weight" = "sub_weight_v"
3391 WHERE "issue_id" = "issue_id_p"
3392 AND "member_id" = "issue_delegation_row"."truster_id";
3393 "weight_v" := "weight_v" + "sub_weight_v";
3394 END IF;
3395 END LOOP;
3396 RETURN "weight_v";
3397 END;
3398 $$;
3400 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
3401 ( "issue"."id"%TYPE,
3402 "member"."id"%TYPE,
3403 "delegating_voter"."delegate_member_ids"%TYPE )
3404 IS 'Helper function for "add_vote_delegations" function';
3407 CREATE FUNCTION "add_vote_delegations"
3408 ( "issue_id_p" "issue"."id"%TYPE )
3409 RETURNS VOID
3410 LANGUAGE 'plpgsql' VOLATILE AS $$
3411 DECLARE
3412 "member_id_v" "member"."id"%TYPE;
3413 BEGIN
3414 FOR "member_id_v" IN
3415 SELECT "member_id" FROM "direct_voter"
3416 WHERE "issue_id" = "issue_id_p"
3417 LOOP
3418 UPDATE "direct_voter" SET
3419 "weight" = "weight" + "weight_of_added_vote_delegations"(
3420 "issue_id_p",
3421 "member_id_v",
3422 '{}'
3423 )
3424 WHERE "member_id" = "member_id_v"
3425 AND "issue_id" = "issue_id_p";
3426 END LOOP;
3427 RETURN;
3428 END;
3429 $$;
3431 COMMENT ON FUNCTION "add_vote_delegations"
3432 ( "issue_id_p" "issue"."id"%TYPE )
3433 IS 'Helper function for "close_voting" function';
3436 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
3437 RETURNS VOID
3438 LANGUAGE 'plpgsql' VOLATILE AS $$
3439 DECLARE
3440 "area_id_v" "area"."id"%TYPE;
3441 "unit_id_v" "unit"."id"%TYPE;
3442 "member_id_v" "member"."id"%TYPE;
3443 BEGIN
3444 PERFORM "lock_issue"("issue_id_p");
3445 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
3446 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
3447 DELETE FROM "delegating_voter"
3448 WHERE "issue_id" = "issue_id_p";
3449 DELETE FROM "direct_voter"
3450 WHERE "issue_id" = "issue_id_p"
3451 AND "autoreject" = TRUE;
3452 DELETE FROM "direct_voter"
3453 USING (
3454 SELECT
3455 "direct_voter"."member_id"
3456 FROM "direct_voter"
3457 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
3458 LEFT JOIN "privilege"
3459 ON "privilege"."unit_id" = "unit_id_v"
3460 AND "privilege"."member_id" = "direct_voter"."member_id"
3461 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
3462 "member"."active" = FALSE OR
3463 "privilege"."voting_right" ISNULL OR
3464 "privilege"."voting_right" = FALSE
3465 )
3466 ) AS "subquery"
3467 WHERE "direct_voter"."issue_id" = "issue_id_p"
3468 AND "direct_voter"."member_id" = "subquery"."member_id";
3469 UPDATE "direct_voter" SET "weight" = 1
3470 WHERE "issue_id" = "issue_id_p";
3471 PERFORM "add_vote_delegations"("issue_id_p");
3472 FOR "member_id_v" IN
3473 SELECT "interest"."member_id"
3474 FROM "interest"
3475 JOIN "member"
3476 ON "interest"."member_id" = "member"."id"
3477 LEFT JOIN "direct_voter"
3478 ON "interest"."member_id" = "direct_voter"."member_id"
3479 AND "interest"."issue_id" = "direct_voter"."issue_id"
3480 LEFT JOIN "delegating_voter"
3481 ON "interest"."member_id" = "delegating_voter"."member_id"
3482 AND "interest"."issue_id" = "delegating_voter"."issue_id"
3483 WHERE "interest"."issue_id" = "issue_id_p"
3484 AND "interest"."autoreject" = TRUE
3485 AND "member"."active"
3486 AND "direct_voter"."member_id" ISNULL
3487 AND "delegating_voter"."member_id" ISNULL
3488 UNION SELECT "membership"."member_id"
3489 FROM "membership"
3490 JOIN "member"
3491 ON "membership"."member_id" = "member"."id"
3492 LEFT JOIN "interest"
3493 ON "membership"."member_id" = "interest"."member_id"
3494 AND "interest"."issue_id" = "issue_id_p"
3495 LEFT JOIN "direct_voter"
3496 ON "membership"."member_id" = "direct_voter"."member_id"
3497 AND "direct_voter"."issue_id" = "issue_id_p"
3498 LEFT JOIN "delegating_voter"
3499 ON "membership"."member_id" = "delegating_voter"."member_id"
3500 AND "delegating_voter"."issue_id" = "issue_id_p"
3501 WHERE "membership"."area_id" = "area_id_v"
3502 AND "membership"."autoreject" = TRUE
3503 AND "member"."active"
3504 AND "interest"."autoreject" ISNULL
3505 AND "direct_voter"."member_id" ISNULL
3506 AND "delegating_voter"."member_id" ISNULL
3507 LOOP
3508 INSERT INTO "direct_voter"
3509 ("member_id", "issue_id", "weight", "autoreject") VALUES
3510 ("member_id_v", "issue_id_p", 1, TRUE);
3511 INSERT INTO "vote" (
3512 "member_id",
3513 "issue_id",
3514 "initiative_id",
3515 "grade"
3516 ) SELECT
3517 "member_id_v" AS "member_id",
3518 "issue_id_p" AS "issue_id",
3519 "id" AS "initiative_id",
3520 -1 AS "grade"
3521 FROM "initiative" WHERE "issue_id" = "issue_id_p";
3522 END LOOP;
3523 PERFORM "add_vote_delegations"("issue_id_p");
3524 UPDATE "issue" SET
3525 "state" = 'calculation',
3526 "closed" = now(),
3527 "voter_count" = (
3528 SELECT coalesce(sum("weight"), 0)
3529 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
3530 )
3531 WHERE "id" = "issue_id_p";
3532 UPDATE "initiative" SET
3533 "positive_votes" = "vote_counts"."positive_votes",
3534 "negative_votes" = "vote_counts"."negative_votes",
3535 "attainable" = CASE WHEN "majority_strict" THEN
3536 "vote_counts"."positive_votes" * "majority_den" >
3537 "majority_num" *
3538 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
3539 ELSE
3540 "vote_counts"."positive_votes" * "majority_den" >=
3541 "majority_num" *
3542 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
3543 END
3544 FROM
3545 ( SELECT
3546 "initiative"."id" AS "initiative_id",
3547 coalesce(
3548 sum(
3549 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
3550 ),
3551 0
3552 ) AS "positive_votes",
3553 coalesce(
3554 sum(
3555 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
3556 ),
3557 0
3558 ) AS "negative_votes"
3559 FROM "initiative"
3560 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
3561 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
3562 LEFT JOIN "direct_voter"
3563 ON "direct_voter"."issue_id" = "initiative"."issue_id"
3564 LEFT JOIN "vote"
3565 ON "vote"."initiative_id" = "initiative"."id"
3566 AND "vote"."member_id" = "direct_voter"."member_id"
3567 WHERE "initiative"."issue_id" = "issue_id_p"
3568 AND "initiative"."admitted" -- NOTE: NULL case is handled too
3569 GROUP BY "initiative"."id"
3570 ) AS "vote_counts",
3571 "issue",
3572 "policy"
3573 WHERE "vote_counts"."initiative_id" = "initiative"."id"
3574 AND "issue"."id" = "initiative"."issue_id"
3575 AND "policy"."id" = "issue"."policy_id";
3576 -- NOTE: "closed" column of issue must be set at this point
3577 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3578 INSERT INTO "battle" (
3579 "issue_id",
3580 "winning_initiative_id", "losing_initiative_id",
3581 "count"
3582 ) SELECT
3583 "issue_id",
3584 "winning_initiative_id", "losing_initiative_id",
3585 "count"
3586 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
3587 END;
3588 $$;
3590 COMMENT ON FUNCTION "close_voting"
3591 ( "issue"."id"%TYPE )
3592 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.';
3595 CREATE FUNCTION "defeat_strength"
3596 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
3597 RETURNS INT8
3598 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3599 BEGIN
3600 IF "positive_votes_p" > "negative_votes_p" THEN
3601 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
3602 ELSIF "positive_votes_p" = "negative_votes_p" THEN
3603 RETURN 0;
3604 ELSE
3605 RETURN -1;
3606 END IF;
3607 END;
3608 $$;
3610 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';
3613 CREATE FUNCTION "array_init_string"("dim_p" INTEGER)
3614 RETURNS TEXT
3615 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3616 DECLARE
3617 "i" INTEGER;
3618 "ary_text_v" TEXT;
3619 BEGIN
3620 IF "dim_p" >= 1 THEN
3621 "ary_text_v" := '{NULL';
3622 "i" := "dim_p";
3623 LOOP
3624 "i" := "i" - 1;
3625 EXIT WHEN "i" = 0;
3626 "ary_text_v" := "ary_text_v" || ',NULL';
3627 END LOOP;
3628 "ary_text_v" := "ary_text_v" || '}';
3629 RETURN "ary_text_v";
3630 ELSE
3631 RAISE EXCEPTION 'Dimension needs to be at least 1.';
3632 END IF;
3633 END;
3634 $$;
3636 COMMENT ON FUNCTION "array_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
3639 CREATE FUNCTION "square_matrix_init_string"("dim_p" INTEGER)
3640 RETURNS TEXT
3641 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3642 DECLARE
3643 "i" INTEGER;
3644 "row_text_v" TEXT;
3645 "ary_text_v" TEXT;
3646 BEGIN
3647 IF "dim_p" >= 1 THEN
3648 "row_text_v" := '{NULL';
3649 "i" := "dim_p";
3650 LOOP
3651 "i" := "i" - 1;
3652 EXIT WHEN "i" = 0;
3653 "row_text_v" := "row_text_v" || ',NULL';
3654 END LOOP;
3655 "row_text_v" := "row_text_v" || '}';
3656 "ary_text_v" := '{' || "row_text_v";
3657 "i" := "dim_p";
3658 LOOP
3659 "i" := "i" - 1;
3660 EXIT WHEN "i" = 0;
3661 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
3662 END LOOP;
3663 "ary_text_v" := "ary_text_v" || '}';
3664 RETURN "ary_text_v";
3665 ELSE
3666 RAISE EXCEPTION 'Dimension needs to be at least 1.';
3667 END IF;
3668 END;
3669 $$;
3671 COMMENT ON FUNCTION "square_matrix_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
3674 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
3675 RETURNS VOID
3676 LANGUAGE 'plpgsql' VOLATILE AS $$
3677 DECLARE
3678 "dimension_v" INTEGER;
3679 "vote_matrix" INT4[][]; -- absolute votes
3680 "matrix" INT8[][]; -- defeat strength / best paths
3681 "i" INTEGER;
3682 "j" INTEGER;
3683 "k" INTEGER;
3684 "battle_row" "battle"%ROWTYPE;
3685 "rank_ary" INT4[];
3686 "rank_v" INT4;
3687 "done_v" INTEGER;
3688 "winners_ary" INTEGER[];
3689 "initiative_id_v" "initiative"."id"%TYPE;
3690 "promising_added_v" BOOLEAN;
3691 BEGIN
3692 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
3693 SELECT count(1) INTO "dimension_v"
3694 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
3695 IF "dimension_v" > 1 THEN
3696 -- Create "vote_matrix" with absolute number of votes in pairwise
3697 -- comparison:
3698 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
3699 "i" := 1;
3700 "j" := 2;
3701 FOR "battle_row" IN
3702 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
3703 ORDER BY
3704 "winning_initiative_id" NULLS LAST,
3705 "losing_initiative_id" NULLS LAST
3706 LOOP
3707 "vote_matrix"["i"]["j"] := "battle_row"."count";
3708 IF "j" = "dimension_v" THEN
3709 "i" := "i" + 1;
3710 "j" := 1;
3711 ELSE
3712 "j" := "j" + 1;
3713 IF "j" = "i" THEN
3714 "j" := "j" + 1;
3715 END IF;
3716 END IF;
3717 END LOOP;
3718 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
3719 RAISE EXCEPTION 'Wrong battle count (should not happen)';
3720 END IF;
3721 -- Store defeat strengths in "matrix" using "defeat_strength"
3722 -- function:
3723 "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
3724 "i" := 1;
3725 LOOP
3726 "j" := 1;
3727 LOOP
3728 IF "i" != "j" THEN
3729 "matrix"["i"]["j"] := "defeat_strength"(
3730 "vote_matrix"["i"]["j"],
3731 "vote_matrix"["j"]["i"]
3732 );
3733 END IF;
3734 EXIT WHEN "j" = "dimension_v";
3735 "j" := "j" + 1;
3736 END LOOP;
3737 EXIT WHEN "i" = "dimension_v";
3738 "i" := "i" + 1;
3739 END LOOP;
3740 -- Find best paths:
3741 "i" := 1;
3742 LOOP
3743 "j" := 1;
3744 LOOP
3745 IF "i" != "j" THEN
3746 "k" := 1;
3747 LOOP
3748 IF "i" != "k" AND "j" != "k" THEN
3749 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
3750 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
3751 "matrix"["j"]["k"] := "matrix"["j"]["i"];
3752 END IF;
3753 ELSE
3754 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
3755 "matrix"["j"]["k"] := "matrix"["i"]["k"];
3756 END IF;
3757 END IF;
3758 END IF;
3759 EXIT WHEN "k" = "dimension_v";
3760 "k" := "k" + 1;
3761 END LOOP;
3762 END IF;
3763 EXIT WHEN "j" = "dimension_v";
3764 "j" := "j" + 1;
3765 END LOOP;
3766 EXIT WHEN "i" = "dimension_v";
3767 "i" := "i" + 1;
3768 END LOOP;
3769 -- Determine order of winners:
3770 "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
3771 "rank_v" := 1;
3772 "done_v" := 0;
3773 LOOP
3774 "winners_ary" := '{}';
3775 "i" := 1;
3776 LOOP
3777 IF "rank_ary"["i"] ISNULL THEN
3778 "j" := 1;
3779 LOOP
3780 IF
3781 "i" != "j" AND
3782 "rank_ary"["j"] ISNULL AND
3783 "matrix"["j"]["i"] > "matrix"["i"]["j"]
3784 THEN
3785 -- someone else is better
3786 EXIT;
3787 END IF;
3788 IF "j" = "dimension_v" THEN
3789 -- noone is better
3790 "winners_ary" := "winners_ary" || "i";
3791 EXIT;
3792 END IF;
3793 "j" := "j" + 1;
3794 END LOOP;
3795 END IF;
3796 EXIT WHEN "i" = "dimension_v";
3797 "i" := "i" + 1;
3798 END LOOP;
3799 "i" := 1;
3800 LOOP
3801 "rank_ary"["winners_ary"["i"]] := "rank_v";
3802 "done_v" := "done_v" + 1;
3803 EXIT WHEN "i" = array_upper("winners_ary", 1);
3804 "i" := "i" + 1;
3805 END LOOP;
3806 EXIT WHEN "done_v" = "dimension_v";
3807 "rank_v" := "rank_v" + 1;
3808 END LOOP;
3809 -- write preliminary results:
3810 "i" := 1;
3811 FOR "initiative_id_v" IN
3812 SELECT "id" FROM "initiative"
3813 WHERE "issue_id" = "issue_id_p" AND "admitted"
3814 ORDER BY "id"
3815 LOOP
3816 UPDATE "initiative" SET
3817 "favored" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
3818 "unfavored" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
3819 "eligible" = "attainable" AND
3820 "rank_ary"["i"] < "rank_ary"["dimension_v"],
3821 "rank" = "rank_ary"["i"],
3822 "winner" = FALSE,
3823 "promising" = FALSE
3824 WHERE "id" = "initiative_id_v";
3825 "i" := "i" + 1;
3826 END LOOP;
3827 IF "i" != "dimension_v" THEN
3828 RAISE EXCEPTION 'Wrong winner count (should not happen)';
3829 END IF;
3830 -- mark final winner:
3831 SELECT "id" INTO "initiative_id_v" FROM "initiative"
3832 WHERE "issue_id" = "issue_id_p" AND "admitted" AND "eligible"
3833 ORDER BY "rank", "id"
3834 LIMIT 1;
3835 UPDATE "initiative" SET "winner" = TRUE WHERE "id" = "initiative_id_v";
3836 -- determine promising initiatives:
3837 LOOP
3838 "promising_added_v" := FALSE;
3839 FOR "initiative_id_v" IN
3840 SELECT "new_initiative"."id"
3841 FROM "issue"
3842 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
3843 JOIN "initiative" "old_initiative"
3844 ON "old_initiative"."issue_id" = "issue_id_p"
3845 AND "old_initiative"."admitted"
3846 AND ("old_initiative"."winner" OR "old_initiative"."promising")
3847 JOIN "initiative" "new_initiative"
3848 ON "new_initiative"."issue_id" = "issue_id_p"
3849 AND "new_initiative"."admitted"
3850 AND NOT ("new_initiative"."winner" OR "new_initiative"."promising")
3851 JOIN "battle" "battle_win"
3852 ON "battle_win"."issue_id" = "issue_id_p"
3853 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
3854 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
3855 JOIN "battle" "battle_lose"
3856 ON "battle_lose"."issue_id" = "issue_id_p"
3857 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
3858 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
3859 WHERE "issue"."id" = "issue_id_p"
3860 AND "new_initiative"."rank" < "old_initiative"."rank"
3861 -- NOTE: non-straightened ranks are used
3862 AND CASE WHEN "policy"."majority_strict" THEN
3863 "battle_win"."count" * "policy"."majority_den" >
3864 "policy"."majority_num" *
3865 ("battle_win"."count"+"battle_lose"."count")
3866 ELSE
3867 "battle_win"."count" * "policy"."majority_den" >=
3868 "policy"."majority_num" *
3869 ("battle_win"."count"+"battle_lose"."count")
3870 END
3871 LOOP
3872 UPDATE "initiative" SET "promising" = TRUE
3873 WHERE "id" = "initiative_id_v";
3874 "promising_added_v" := TRUE;
3875 END LOOP;
3876 EXIT WHEN NOT "promising_added_v";
3877 END LOOP;
3878 -- straighten ranks (start counting with 1, no equal ranks):
3879 "rank_v" := 1;
3880 FOR "initiative_id_v" IN
3881 SELECT "id" FROM "initiative"
3882 WHERE "issue_id" = "issue_id_p" AND "admitted"
3883 ORDER BY "rank", "id"
3884 LOOP
3885 UPDATE "initiative" SET "rank" = "rank_v"
3886 WHERE "id" = "initiative_id_v";
3887 "rank_v" := "rank_v" + 1;
3888 END LOOP;
3889 END IF;
3890 -- mark issue as finished
3891 UPDATE "issue" SET
3892 "state" =
3893 CASE WHEN "dimension_v" = 0 THEN -- TODO: Broken! To be fixed!
3894 'finished_without_winner'::"issue_state"
3895 ELSE
3896 'finished_with_winner'::"issue_state"
3897 END,
3898 "ranks_available" = TRUE
3899 WHERE "id" = "issue_id_p";
3900 RETURN;
3901 END;
3902 $$;
3904 COMMENT ON FUNCTION "calculate_ranks"
3905 ( "issue"."id"%TYPE )
3906 IS 'Determine ranking (Votes have to be counted first)';
3910 -----------------------------
3911 -- Automatic state changes --
3912 -----------------------------
3915 CREATE FUNCTION "check_issue"
3916 ( "issue_id_p" "issue"."id"%TYPE )
3917 RETURNS VOID
3918 LANGUAGE 'plpgsql' VOLATILE AS $$
3919 DECLARE
3920 "issue_row" "issue"%ROWTYPE;
3921 "policy_row" "policy"%ROWTYPE;
3922 "voting_requested_v" BOOLEAN;
3923 BEGIN
3924 PERFORM "lock_issue"("issue_id_p");
3925 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3926 -- only process open issues:
3927 IF "issue_row"."closed" ISNULL THEN
3928 SELECT * INTO "policy_row" FROM "policy"
3929 WHERE "id" = "issue_row"."policy_id";
3930 -- create a snapshot, unless issue is already fully frozen:
3931 IF "issue_row"."fully_frozen" ISNULL THEN
3932 PERFORM "create_snapshot"("issue_id_p");
3933 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3934 END IF;
3935 -- eventually close or accept issues, which have not been accepted:
3936 IF "issue_row"."accepted" ISNULL THEN
3937 IF EXISTS (
3938 SELECT NULL FROM "initiative"
3939 WHERE "issue_id" = "issue_id_p"
3940 AND "supporter_count" > 0
3941 AND "supporter_count" * "policy_row"."issue_quorum_den"
3942 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
3943 ) THEN
3944 -- accept issues, if supporter count is high enough
3945 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3946 -- NOTE: "issue_row" used later
3947 "issue_row"."state" := 'discussion';
3948 "issue_row"."accepted" := now();
3949 UPDATE "issue" SET
3950 "state" = "issue_row"."state",
3951 "accepted" = "issue_row"."accepted"
3952 WHERE "id" = "issue_row"."id";
3953 ELSIF
3954 now() >= "issue_row"."created" + "issue_row"."admission_time"
3955 THEN
3956 -- close issues, if admission time has expired
3957 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3958 UPDATE "issue" SET
3959 "state" = 'canceled_issue_not_accepted',
3960 "closed" = now()
3961 WHERE "id" = "issue_row"."id";
3962 END IF;
3963 END IF;
3964 -- eventually half freeze issues:
3965 IF
3966 -- NOTE: issue can't be closed at this point, if it has been accepted
3967 "issue_row"."accepted" NOTNULL AND
3968 "issue_row"."half_frozen" ISNULL
3969 THEN
3970 SELECT
3971 CASE
3972 WHEN "vote_now" * 2 > "issue_row"."population" THEN
3973 TRUE
3974 WHEN "vote_later" * 2 > "issue_row"."population" THEN
3975 FALSE
3976 ELSE NULL
3977 END
3978 INTO "voting_requested_v"
3979 FROM "issue" WHERE "id" = "issue_id_p";
3980 IF
3981 "voting_requested_v" OR (
3982 "voting_requested_v" ISNULL AND
3983 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
3984 )
3985 THEN
3986 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
3987 -- NOTE: "issue_row" used later
3988 "issue_row"."state" := 'verification';
3989 "issue_row"."half_frozen" := now();
3990 UPDATE "issue" SET
3991 "state" = "issue_row"."state",
3992 "half_frozen" = "issue_row"."half_frozen"
3993 WHERE "id" = "issue_row"."id";
3994 END IF;
3995 END IF;
3996 -- close issues after some time, if all initiatives have been revoked:
3997 IF
3998 "issue_row"."closed" ISNULL AND
3999 NOT EXISTS (
4000 -- all initiatives are revoked
4001 SELECT NULL FROM "initiative"
4002 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
4003 ) AND (
4004 -- and issue has not been accepted yet
4005 "issue_row"."accepted" ISNULL OR
4006 NOT EXISTS (
4007 -- or no initiatives have been revoked lately
4008 SELECT NULL FROM "initiative"
4009 WHERE "issue_id" = "issue_id_p"
4010 AND now() < "revoked" + "issue_row"."verification_time"
4011 ) OR (
4012 -- or verification time has elapsed
4013 "issue_row"."half_frozen" NOTNULL AND
4014 "issue_row"."fully_frozen" ISNULL AND
4015 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
4016 )
4017 )
4018 THEN
4019 -- NOTE: "issue_row" used later
4020 IF "issue_row"."accepted" ISNULL THEN
4021 "issue_row"."state" := 'canceled_revoked_before_accepted';
4022 ELSIF "issue_row"."half_frozen" ISNULL THEN
4023 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
4024 ELSE
4025 "issue_row"."state" := 'canceled_after_revocation_during_verification';
4026 END IF;
4027 "issue_row"."closed" := now();
4028 UPDATE "issue" SET
4029 "state" = "issue_row"."state",
4030 "closed" = "issue_row"."closed"
4031 WHERE "id" = "issue_row"."id";
4032 END IF;
4033 -- fully freeze issue after verification time:
4034 IF
4035 "issue_row"."half_frozen" NOTNULL AND
4036 "issue_row"."fully_frozen" ISNULL AND
4037 "issue_row"."closed" ISNULL AND
4038 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
4039 THEN
4040 PERFORM "freeze_after_snapshot"("issue_id_p");
4041 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
4042 END IF;
4043 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4044 -- close issue by calling close_voting(...) after voting time:
4045 IF
4046 "issue_row"."closed" ISNULL AND
4047 "issue_row"."fully_frozen" NOTNULL AND
4048 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
4049 THEN
4050 PERFORM "close_voting"("issue_id_p");
4051 -- calculate ranks will not consume much time and can be done now
4052 PERFORM "calculate_ranks"("issue_id_p");
4053 END IF;
4054 END IF;
4055 RETURN;
4056 END;
4057 $$;
4059 COMMENT ON FUNCTION "check_issue"
4060 ( "issue"."id"%TYPE )
4061 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.';
4064 CREATE FUNCTION "check_everything"()
4065 RETURNS VOID
4066 LANGUAGE 'plpgsql' VOLATILE AS $$
4067 DECLARE
4068 "issue_id_v" "issue"."id"%TYPE;
4069 BEGIN
4070 DELETE FROM "expired_session";
4071 PERFORM "check_last_login"();
4072 PERFORM "calculate_member_counts"();
4073 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
4074 PERFORM "check_issue"("issue_id_v");
4075 END LOOP;
4076 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
4077 PERFORM "calculate_ranks"("issue_id_v");
4078 END LOOP;
4079 RETURN;
4080 END;
4081 $$;
4083 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.';
4087 ----------------------
4088 -- Deletion of data --
4089 ----------------------
4092 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
4093 RETURNS VOID
4094 LANGUAGE 'plpgsql' VOLATILE AS $$
4095 DECLARE
4096 "issue_row" "issue"%ROWTYPE;
4097 BEGIN
4098 SELECT * INTO "issue_row"
4099 FROM "issue" WHERE "id" = "issue_id_p"
4100 FOR UPDATE;
4101 IF "issue_row"."cleaned" ISNULL THEN
4102 UPDATE "issue" SET
4103 "closed" = NULL,
4104 "ranks_available" = FALSE
4105 WHERE "id" = "issue_id_p";
4106 DELETE FROM "delegating_voter"
4107 WHERE "issue_id" = "issue_id_p";
4108 DELETE FROM "direct_voter"
4109 WHERE "issue_id" = "issue_id_p";
4110 DELETE FROM "delegating_interest_snapshot"
4111 WHERE "issue_id" = "issue_id_p";
4112 DELETE FROM "direct_interest_snapshot"
4113 WHERE "issue_id" = "issue_id_p";
4114 DELETE FROM "delegating_population_snapshot"
4115 WHERE "issue_id" = "issue_id_p";
4116 DELETE FROM "direct_population_snapshot"
4117 WHERE "issue_id" = "issue_id_p";
4118 DELETE FROM "non_voter"
4119 WHERE "issue_id" = "issue_id_p";
4120 DELETE FROM "delegation"
4121 WHERE "issue_id" = "issue_id_p";
4122 DELETE FROM "supporter"
4123 WHERE "issue_id" = "issue_id_p";
4124 UPDATE "issue" SET
4125 "closed" = "issue_row"."closed",
4126 "ranks_available" = "issue_row"."ranks_available",
4127 "cleaned" = now()
4128 WHERE "id" = "issue_id_p";
4129 END IF;
4130 RETURN;
4131 END;
4132 $$;
4134 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
4137 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
4138 RETURNS VOID
4139 LANGUAGE 'plpgsql' VOLATILE AS $$
4140 BEGIN
4141 UPDATE "member" SET
4142 "last_login" = NULL,
4143 "last_login_public" = NULL,
4144 "login" = NULL,
4145 "password" = NULL,
4146 "locked" = TRUE,
4147 "active" = FALSE,
4148 "notify_email" = NULL,
4149 "notify_email_unconfirmed" = NULL,
4150 "notify_email_secret" = NULL,
4151 "notify_email_secret_expiry" = NULL,
4152 "notify_email_lock_expiry" = NULL,
4153 "password_reset_secret" = NULL,
4154 "password_reset_secret_expiry" = NULL,
4155 "organizational_unit" = NULL,
4156 "internal_posts" = NULL,
4157 "realname" = NULL,
4158 "birthday" = NULL,
4159 "address" = NULL,
4160 "email" = NULL,
4161 "xmpp_address" = NULL,
4162 "website" = NULL,
4163 "phone" = NULL,
4164 "mobile_phone" = NULL,
4165 "profession" = NULL,
4166 "external_memberships" = NULL,
4167 "external_posts" = NULL,
4168 "statement" = NULL
4169 WHERE "id" = "member_id_p";
4170 -- "text_search_data" is updated by triggers
4171 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
4172 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
4173 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
4174 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
4175 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
4176 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
4177 DELETE FROM "session" WHERE "member_id" = "member_id_p";
4178 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
4179 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
4180 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
4181 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
4182 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
4183 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
4184 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
4185 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
4186 DELETE FROM "direct_voter" USING "issue"
4187 WHERE "direct_voter"."issue_id" = "issue"."id"
4188 AND "issue"."closed" ISNULL
4189 AND "member_id" = "member_id_p";
4190 RETURN;
4191 END;
4192 $$;
4194 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)';
4197 CREATE FUNCTION "delete_private_data"()
4198 RETURNS VOID
4199 LANGUAGE 'plpgsql' VOLATILE AS $$
4200 BEGIN
4201 UPDATE "member" SET
4202 "last_login" = NULL,
4203 "login" = NULL,
4204 "password" = NULL,
4205 "notify_email" = NULL,
4206 "notify_email_unconfirmed" = NULL,
4207 "notify_email_secret" = NULL,
4208 "notify_email_secret_expiry" = NULL,
4209 "notify_email_lock_expiry" = NULL,
4210 "password_reset_secret" = NULL,
4211 "password_reset_secret_expiry" = NULL,
4212 "organizational_unit" = NULL,
4213 "internal_posts" = NULL,
4214 "realname" = NULL,
4215 "birthday" = NULL,
4216 "address" = NULL,
4217 "email" = NULL,
4218 "xmpp_address" = NULL,
4219 "website" = NULL,
4220 "phone" = NULL,
4221 "mobile_phone" = NULL,
4222 "profession" = NULL,
4223 "external_memberships" = NULL,
4224 "external_posts" = NULL,
4225 "statement" = NULL;
4226 -- "text_search_data" is updated by triggers
4227 DELETE FROM "invite_code";
4228 DELETE FROM "setting";
4229 DELETE FROM "setting_map";
4230 DELETE FROM "member_relation_setting";
4231 DELETE FROM "member_image";
4232 DELETE FROM "contact";
4233 DELETE FROM "ignored_member";
4234 DELETE FROM "session";
4235 DELETE FROM "area_setting";
4236 DELETE FROM "issue_setting";
4237 DELETE FROM "ignored_initiative";
4238 DELETE FROM "initiative_setting";
4239 DELETE FROM "suggestion_setting";
4240 DELETE FROM "non_voter";
4241 DELETE FROM "direct_voter" USING "issue"
4242 WHERE "direct_voter"."issue_id" = "issue"."id"
4243 AND "issue"."closed" ISNULL;
4244 RETURN;
4245 END;
4246 $$;
4248 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.';
4252 COMMIT;