liquid_feedback_core
view core.sql @ 101:9b18ee4682c7
Calling function "delete_member" now sets "locked" field to TRUE
| author | jbe |
|---|---|
| date | Tue Jan 04 02:48:41 2011 +0100 (2011-01-04) |
| parents | 46260129d0ce |
| children | bc8aa59b0945 |
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.3.1', 1, 3, 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 "member" (
58 "id" SERIAL4 PRIMARY KEY,
59 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
60 "last_login" TIMESTAMPTZ,
61 "login" TEXT UNIQUE,
62 "password" TEXT,
63 "locked" BOOLEAN NOT NULL DEFAULT FALSE,
64 "active" BOOLEAN NOT NULL DEFAULT TRUE,
65 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
66 "notify_email" TEXT,
67 "notify_email_unconfirmed" TEXT,
68 "notify_email_secret" TEXT UNIQUE,
69 "notify_email_secret_expiry" TIMESTAMPTZ,
70 "notify_email_lock_expiry" TIMESTAMPTZ,
71 "password_reset_secret" TEXT UNIQUE,
72 "password_reset_secret_expiry" TIMESTAMPTZ,
73 "name" TEXT NOT NULL UNIQUE,
74 "identification" TEXT UNIQUE,
75 "organizational_unit" TEXT,
76 "internal_posts" TEXT,
77 "realname" TEXT,
78 "birthday" DATE,
79 "address" TEXT,
80 "email" TEXT,
81 "xmpp_address" TEXT,
82 "website" TEXT,
83 "phone" TEXT,
84 "mobile_phone" TEXT,
85 "profession" TEXT,
86 "external_memberships" TEXT,
87 "external_posts" TEXT,
88 "statement" TEXT,
89 "text_search_data" TSVECTOR );
90 CREATE INDEX "member_active_idx" ON "member" ("active");
91 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
92 CREATE TRIGGER "update_text_search_data"
93 BEFORE INSERT OR UPDATE ON "member"
94 FOR EACH ROW EXECUTE PROCEDURE
95 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
96 "name", "identification", "organizational_unit", "internal_posts",
97 "realname", "external_memberships", "external_posts", "statement" );
99 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
101 COMMENT ON COLUMN "member"."login" IS 'Login name';
102 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
103 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
104 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.';
105 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
106 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
107 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
108 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
109 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
110 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
111 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member';
112 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
113 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
114 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
115 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
116 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
117 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
118 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
119 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his homepage within the system';
122 CREATE TABLE "member_history" (
123 "id" SERIAL8 PRIMARY KEY,
124 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
125 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
126 "active" BOOLEAN NOT NULL,
127 "name" TEXT NOT NULL );
128 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
130 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
132 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
133 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
136 CREATE TABLE "invite_code" (
137 "code" TEXT PRIMARY KEY,
138 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
139 "used" TIMESTAMPTZ,
140 "member_id" INT4 UNIQUE REFERENCES "member" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
141 "comment" TEXT,
142 CONSTRAINT "only_used_codes_may_refer_to_member" CHECK ("used" NOTNULL OR "member_id" ISNULL) );
144 COMMENT ON TABLE "invite_code" IS 'Invite codes can be used once to create a new member account.';
146 COMMENT ON COLUMN "invite_code"."code" IS 'Secret code';
147 COMMENT ON COLUMN "invite_code"."created" IS 'Time of creation of the secret code';
148 COMMENT ON COLUMN "invite_code"."used" IS 'NULL, if not used yet, otherwise tells when this code was used to create a member account';
149 COMMENT ON COLUMN "invite_code"."member_id" IS 'References the member whose account was created with this code';
150 COMMENT ON COLUMN "invite_code"."comment" IS 'Comment on the code, which is to be used for administrative reasons only';
153 CREATE TABLE "setting" (
154 PRIMARY KEY ("member_id", "key"),
155 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
156 "key" TEXT NOT NULL,
157 "value" TEXT NOT NULL );
158 CREATE INDEX "setting_key_idx" ON "setting" ("key");
160 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
162 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
165 CREATE TABLE "setting_map" (
166 PRIMARY KEY ("member_id", "key", "subkey"),
167 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
168 "key" TEXT NOT NULL,
169 "subkey" TEXT NOT NULL,
170 "value" TEXT NOT NULL );
171 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
173 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
175 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
176 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
177 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
180 CREATE TABLE "member_relation_setting" (
181 PRIMARY KEY ("member_id", "key", "other_member_id"),
182 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
183 "key" TEXT NOT NULL,
184 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
185 "value" TEXT NOT NULL );
187 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
190 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
192 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
195 CREATE TABLE "member_image" (
196 PRIMARY KEY ("member_id", "image_type", "scaled"),
197 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
198 "image_type" "member_image_type",
199 "scaled" BOOLEAN,
200 "content_type" TEXT,
201 "data" BYTEA NOT NULL );
203 COMMENT ON TABLE "member_image" IS 'Images of members';
205 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
208 CREATE TABLE "member_count" (
209 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
210 "total_count" INT4 NOT NULL );
212 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';
214 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
215 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
218 CREATE TABLE "contact" (
219 PRIMARY KEY ("member_id", "other_member_id"),
220 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
221 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
222 "public" BOOLEAN NOT NULL DEFAULT FALSE,
223 CONSTRAINT "cant_save_yourself_as_contact"
224 CHECK ("member_id" != "other_member_id") );
226 COMMENT ON TABLE "contact" IS 'Contact lists';
228 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
229 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
230 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
233 CREATE TABLE "session" (
234 "ident" TEXT PRIMARY KEY,
235 "additional_secret" TEXT,
236 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
237 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
238 "lang" TEXT );
239 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
241 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';
243 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
244 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
245 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
246 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
249 CREATE TABLE "policy" (
250 "id" SERIAL4 PRIMARY KEY,
251 "index" INT4 NOT NULL,
252 "active" BOOLEAN NOT NULL DEFAULT TRUE,
253 "name" TEXT NOT NULL UNIQUE,
254 "description" TEXT NOT NULL DEFAULT '',
255 "admission_time" INTERVAL NOT NULL,
256 "discussion_time" INTERVAL NOT NULL,
257 "verification_time" INTERVAL NOT NULL,
258 "voting_time" INTERVAL NOT NULL,
259 "issue_quorum_num" INT4 NOT NULL,
260 "issue_quorum_den" INT4 NOT NULL,
261 "initiative_quorum_num" INT4 NOT NULL,
262 "initiative_quorum_den" INT4 NOT NULL,
263 "majority_num" INT4 NOT NULL DEFAULT 1,
264 "majority_den" INT4 NOT NULL DEFAULT 2,
265 "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE );
266 CREATE INDEX "policy_active_idx" ON "policy" ("active");
268 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
270 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
271 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
272 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"';
273 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"';
274 COMMENT ON COLUMN "policy"."verification_time" IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
275 COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "fully_frozen" but not "closed"';
276 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"';
277 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"';
278 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
279 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
280 COMMENT ON COLUMN "policy"."majority_num" IS 'Numerator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
281 COMMENT ON COLUMN "policy"."majority_den" IS 'Denominator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
282 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.';
285 CREATE TABLE "area" (
286 "id" SERIAL4 PRIMARY KEY,
287 "active" BOOLEAN NOT NULL DEFAULT TRUE,
288 "name" TEXT NOT NULL,
289 "description" TEXT NOT NULL DEFAULT '',
290 "direct_member_count" INT4,
291 "member_weight" INT4,
292 "autoreject_weight" INT4,
293 "text_search_data" TSVECTOR );
294 CREATE INDEX "area_active_idx" ON "area" ("active");
295 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
296 CREATE TRIGGER "update_text_search_data"
297 BEFORE INSERT OR UPDATE ON "area"
298 FOR EACH ROW EXECUTE PROCEDURE
299 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
300 "name", "description" );
302 COMMENT ON TABLE "area" IS 'Subject areas';
304 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
305 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"';
306 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
307 COMMENT ON COLUMN "area"."autoreject_weight" IS 'Sum of weight of members using the autoreject feature';
310 CREATE TABLE "area_setting" (
311 PRIMARY KEY ("member_id", "key", "area_id"),
312 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
313 "key" TEXT NOT NULL,
314 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
315 "value" TEXT NOT NULL );
317 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
320 CREATE TABLE "allowed_policy" (
321 PRIMARY KEY ("area_id", "policy_id"),
322 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
323 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
324 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
325 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
327 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
329 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
332 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
334 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';
337 CREATE TABLE "issue" (
338 "id" SERIAL4 PRIMARY KEY,
339 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
340 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
341 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
342 "accepted" TIMESTAMPTZ,
343 "half_frozen" TIMESTAMPTZ,
344 "fully_frozen" TIMESTAMPTZ,
345 "closed" TIMESTAMPTZ,
346 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
347 "cleaned" TIMESTAMPTZ,
348 "admission_time" INTERVAL NOT NULL,
349 "discussion_time" INTERVAL NOT NULL,
350 "verification_time" INTERVAL NOT NULL,
351 "voting_time" INTERVAL NOT NULL,
352 "snapshot" TIMESTAMPTZ,
353 "latest_snapshot_event" "snapshot_event",
354 "population" INT4,
355 "vote_now" INT4,
356 "vote_later" INT4,
357 "voter_count" INT4,
358 CONSTRAINT "valid_state" CHECK (
359 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
360 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
361 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
362 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
363 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
364 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
365 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
366 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
367 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ),
368 CONSTRAINT "state_change_order" CHECK (
369 "created" <= "accepted" AND
370 "accepted" <= "half_frozen" AND
371 "half_frozen" <= "fully_frozen" AND
372 "fully_frozen" <= "closed" ),
373 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
374 "cleaned" ISNULL OR "closed" NOTNULL ),
375 CONSTRAINT "last_snapshot_on_full_freeze"
376 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
377 CONSTRAINT "freeze_requires_snapshot"
378 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
379 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
380 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
381 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
382 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
383 CREATE INDEX "issue_created_idx" ON "issue" ("created");
384 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
385 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
386 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
387 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
388 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
389 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
391 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
393 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
394 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.';
395 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.';
396 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.';
397 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
398 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
399 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
400 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
401 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
402 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
403 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';
404 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';
405 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
406 COMMENT ON COLUMN "issue"."vote_now" IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"';
407 COMMENT ON COLUMN "issue"."vote_later" IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"';
408 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';
411 CREATE TABLE "issue_setting" (
412 PRIMARY KEY ("member_id", "key", "issue_id"),
413 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
414 "key" TEXT NOT NULL,
415 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
416 "value" TEXT NOT NULL );
418 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
421 CREATE TABLE "initiative" (
422 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
423 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
424 "id" SERIAL4 PRIMARY KEY,
425 "name" TEXT NOT NULL,
426 "discussion_url" TEXT,
427 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
428 "revoked" TIMESTAMPTZ,
429 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
430 "admitted" BOOLEAN,
431 "supporter_count" INT4,
432 "informed_supporter_count" INT4,
433 "satisfied_supporter_count" INT4,
434 "satisfied_informed_supporter_count" INT4,
435 "positive_votes" INT4,
436 "negative_votes" INT4,
437 "agreed" BOOLEAN,
438 "rank" INT4,
439 "text_search_data" TSVECTOR,
440 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
441 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
442 CONSTRAINT "revoked_initiatives_cant_be_admitted"
443 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
444 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"
445 CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "agreed" ISNULL)),
446 CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"
447 CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL),
448 CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"
449 CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) );
450 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
451 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
452 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
453 CREATE TRIGGER "update_text_search_data"
454 BEFORE INSERT OR UPDATE ON "initiative"
455 FOR EACH ROW EXECUTE PROCEDURE
456 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
457 "name", "discussion_url");
459 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.';
461 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
462 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
463 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
464 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
465 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
466 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
467 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
468 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
469 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
470 COMMENT ON COLUMN "initiative"."agreed" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "majority_num"/"majority_den"';
471 COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';
474 CREATE TABLE "battle" (
475 PRIMARY KEY ("issue_id", "winning_initiative_id", "losing_initiative_id"),
476 "issue_id" INT4,
477 "winning_initiative_id" INT4,
478 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
479 "losing_initiative_id" INT4,
480 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
481 "count" INT4 NOT NULL);
483 COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue';
486 CREATE TABLE "initiative_setting" (
487 PRIMARY KEY ("member_id", "key", "initiative_id"),
488 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
489 "key" TEXT NOT NULL,
490 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
491 "value" TEXT NOT NULL );
493 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
496 CREATE TABLE "draft" (
497 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
498 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
499 "id" SERIAL8 PRIMARY KEY,
500 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
501 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
502 "formatting_engine" TEXT,
503 "content" TEXT NOT NULL,
504 "text_search_data" TSVECTOR );
505 CREATE INDEX "draft_created_idx" ON "draft" ("created");
506 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
507 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
508 CREATE TRIGGER "update_text_search_data"
509 BEFORE INSERT OR UPDATE ON "draft"
510 FOR EACH ROW EXECUTE PROCEDURE
511 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
513 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.';
515 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
516 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
519 CREATE TABLE "rendered_draft" (
520 PRIMARY KEY ("draft_id", "format"),
521 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
522 "format" TEXT,
523 "content" TEXT NOT NULL );
525 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)';
528 CREATE TABLE "suggestion" (
529 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
530 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
531 "id" SERIAL8 PRIMARY KEY,
532 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
533 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
534 "name" TEXT NOT NULL,
535 "description" TEXT NOT NULL DEFAULT '',
536 "text_search_data" TSVECTOR,
537 "minus2_unfulfilled_count" INT4,
538 "minus2_fulfilled_count" INT4,
539 "minus1_unfulfilled_count" INT4,
540 "minus1_fulfilled_count" INT4,
541 "plus1_unfulfilled_count" INT4,
542 "plus1_fulfilled_count" INT4,
543 "plus2_unfulfilled_count" INT4,
544 "plus2_fulfilled_count" INT4 );
545 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
546 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
547 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
548 CREATE TRIGGER "update_text_search_data"
549 BEFORE INSERT OR UPDATE ON "suggestion"
550 FOR EACH ROW EXECUTE PROCEDURE
551 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
552 "name", "description");
554 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';
556 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
557 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
558 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
559 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
560 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
561 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
562 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
563 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
566 CREATE TABLE "suggestion_setting" (
567 PRIMARY KEY ("member_id", "key", "suggestion_id"),
568 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
569 "key" TEXT NOT NULL,
570 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
571 "value" TEXT NOT NULL );
573 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
576 CREATE TABLE "membership" (
577 PRIMARY KEY ("area_id", "member_id"),
578 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
579 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
580 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
581 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
583 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
585 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.';
588 CREATE TABLE "interest" (
589 PRIMARY KEY ("issue_id", "member_id"),
590 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
591 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
592 "autoreject" BOOLEAN,
593 "voting_requested" BOOLEAN );
594 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
596 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.';
598 COMMENT ON COLUMN "interest"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure';
599 COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply';
602 CREATE TABLE "ignored_issue" (
603 PRIMARY KEY ("issue_id", "member_id"),
604 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
605 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
606 "new" BOOLEAN NOT NULL DEFAULT FALSE,
607 "accepted" BOOLEAN NOT NULL DEFAULT FALSE,
608 "half_frozen" BOOLEAN NOT NULL DEFAULT FALSE,
609 "fully_frozen" BOOLEAN NOT NULL DEFAULT FALSE );
610 CREATE INDEX "ignored_issue_member_id_idx" ON "ignored_issue" ("member_id");
612 COMMENT ON TABLE "ignored_issue" IS 'Table to store member specific options to ignore issues in selected states';
614 COMMENT ON COLUMN "ignored_issue"."new" IS 'Apply when issue is neither closed nor accepted';
615 COMMENT ON COLUMN "ignored_issue"."accepted" IS 'Apply when issue is accepted but not (half_)frozen or closed';
616 COMMENT ON COLUMN "ignored_issue"."half_frozen" IS 'Apply when issue is half_frozen but not fully_frozen or closed';
617 COMMENT ON COLUMN "ignored_issue"."fully_frozen" IS 'Apply when issue is fully_frozen (in voting) and not closed';
620 CREATE TABLE "initiator" (
621 PRIMARY KEY ("initiative_id", "member_id"),
622 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
623 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
624 "accepted" BOOLEAN );
625 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
627 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.';
629 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.';
632 CREATE TABLE "supporter" (
633 "issue_id" INT4 NOT NULL,
634 PRIMARY KEY ("initiative_id", "member_id"),
635 "initiative_id" INT4,
636 "member_id" INT4,
637 "draft_id" INT8 NOT NULL,
638 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
639 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
640 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
642 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.';
644 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
647 CREATE TABLE "opinion" (
648 "initiative_id" INT4 NOT NULL,
649 PRIMARY KEY ("suggestion_id", "member_id"),
650 "suggestion_id" INT8,
651 "member_id" INT4,
652 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
653 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
654 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
655 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
656 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
658 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.';
660 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
663 CREATE TYPE "delegation_scope" AS ENUM ('global', 'area', 'issue');
665 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''global'', ''area'', or ''issue'' (order is relevant)';
668 CREATE TABLE "delegation" (
669 "id" SERIAL8 PRIMARY KEY,
670 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
671 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
672 "scope" "delegation_scope" NOT NULL,
673 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
674 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
675 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
676 CONSTRAINT "no_global_delegation_to_null"
677 CHECK ("trustee_id" NOTNULL OR "scope" != 'global'),
678 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
679 ("scope" = 'global' AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
680 ("scope" = 'area' AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
681 ("scope" = 'issue' AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
682 UNIQUE ("area_id", "truster_id"),
683 UNIQUE ("issue_id", "truster_id") );
684 CREATE UNIQUE INDEX "delegation_global_truster_id_unique_idx"
685 ON "delegation" ("truster_id") WHERE "scope" = 'global';
686 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
687 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
689 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
691 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
692 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
695 CREATE TABLE "direct_population_snapshot" (
696 PRIMARY KEY ("issue_id", "event", "member_id"),
697 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
698 "event" "snapshot_event",
699 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
700 "weight" INT4 );
701 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
703 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
705 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
706 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
709 CREATE TABLE "delegating_population_snapshot" (
710 PRIMARY KEY ("issue_id", "event", "member_id"),
711 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
712 "event" "snapshot_event",
713 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
714 "weight" INT4,
715 "scope" "delegation_scope" NOT NULL,
716 "delegate_member_ids" INT4[] NOT NULL );
717 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
719 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
721 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
722 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
723 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
724 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"';
727 CREATE TABLE "direct_interest_snapshot" (
728 PRIMARY KEY ("issue_id", "event", "member_id"),
729 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
730 "event" "snapshot_event",
731 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
732 "weight" INT4,
733 "voting_requested" BOOLEAN );
734 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
736 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
738 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
739 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
740 COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';
743 CREATE TABLE "delegating_interest_snapshot" (
744 PRIMARY KEY ("issue_id", "event", "member_id"),
745 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
746 "event" "snapshot_event",
747 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
748 "weight" INT4,
749 "scope" "delegation_scope" NOT NULL,
750 "delegate_member_ids" INT4[] NOT NULL );
751 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
753 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
755 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
756 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
757 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
758 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"';
761 CREATE TABLE "direct_supporter_snapshot" (
762 "issue_id" INT4 NOT NULL,
763 PRIMARY KEY ("initiative_id", "event", "member_id"),
764 "initiative_id" INT4,
765 "event" "snapshot_event",
766 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
767 "informed" BOOLEAN NOT NULL,
768 "satisfied" BOOLEAN NOT NULL,
769 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
770 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
771 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
773 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
775 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
776 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
777 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
780 CREATE TABLE "direct_voter" (
781 PRIMARY KEY ("issue_id", "member_id"),
782 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
783 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
784 "weight" INT4,
785 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
786 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
788 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.';
790 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
791 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
794 CREATE TABLE "delegating_voter" (
795 PRIMARY KEY ("issue_id", "member_id"),
796 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
797 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
798 "weight" INT4,
799 "scope" "delegation_scope" NOT NULL,
800 "delegate_member_ids" INT4[] NOT NULL );
801 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
803 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
805 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
806 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
807 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"';
810 CREATE TABLE "vote" (
811 "issue_id" INT4 NOT NULL,
812 PRIMARY KEY ("initiative_id", "member_id"),
813 "initiative_id" INT4,
814 "member_id" INT4,
815 "grade" INT4,
816 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
817 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
818 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
820 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.';
822 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.';
825 CREATE TABLE "contingent" (
826 "time_frame" INTERVAL PRIMARY KEY,
827 "text_entry_limit" INT4,
828 "initiative_limit" INT4 );
830 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.';
832 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';
833 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
837 --------------------------------
838 -- Writing of history entries --
839 --------------------------------
841 CREATE FUNCTION "write_member_history_trigger"()
842 RETURNS TRIGGER
843 LANGUAGE 'plpgsql' VOLATILE AS $$
844 BEGIN
845 IF
846 NEW."active" != OLD."active" OR
847 NEW."name" != OLD."name"
848 THEN
849 INSERT INTO "member_history"
850 ("member_id", "active", "name")
851 VALUES (NEW."id", OLD."active", OLD."name");
852 END IF;
853 RETURN NULL;
854 END;
855 $$;
857 CREATE TRIGGER "write_member_history"
858 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
859 "write_member_history_trigger"();
861 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
862 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
866 ----------------------------
867 -- Additional constraints --
868 ----------------------------
871 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
872 RETURNS TRIGGER
873 LANGUAGE 'plpgsql' VOLATILE AS $$
874 BEGIN
875 IF NOT EXISTS (
876 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
877 ) THEN
878 --RAISE 'Cannot create issue without an initial initiative.' USING
879 -- ERRCODE = 'integrity_constraint_violation',
880 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
881 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
882 END IF;
883 RETURN NULL;
884 END;
885 $$;
887 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
888 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
889 FOR EACH ROW EXECUTE PROCEDURE
890 "issue_requires_first_initiative_trigger"();
892 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
893 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
896 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
897 RETURNS TRIGGER
898 LANGUAGE 'plpgsql' VOLATILE AS $$
899 DECLARE
900 "reference_lost" BOOLEAN;
901 BEGIN
902 IF TG_OP = 'DELETE' THEN
903 "reference_lost" := TRUE;
904 ELSE
905 "reference_lost" := NEW."issue_id" != OLD."issue_id";
906 END IF;
907 IF
908 "reference_lost" AND NOT EXISTS (
909 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
910 )
911 THEN
912 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
913 END IF;
914 RETURN NULL;
915 END;
916 $$;
918 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
919 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
920 FOR EACH ROW EXECUTE PROCEDURE
921 "last_initiative_deletes_issue_trigger"();
923 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
924 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
927 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
928 RETURNS TRIGGER
929 LANGUAGE 'plpgsql' VOLATILE AS $$
930 BEGIN
931 IF NOT EXISTS (
932 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
933 ) THEN
934 --RAISE 'Cannot create initiative without an initial draft.' USING
935 -- ERRCODE = 'integrity_constraint_violation',
936 -- HINT = 'Create issue, initiative and draft within the same transaction.';
937 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
938 END IF;
939 RETURN NULL;
940 END;
941 $$;
943 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
944 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
945 FOR EACH ROW EXECUTE PROCEDURE
946 "initiative_requires_first_draft_trigger"();
948 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
949 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
952 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
953 RETURNS TRIGGER
954 LANGUAGE 'plpgsql' VOLATILE AS $$
955 DECLARE
956 "reference_lost" BOOLEAN;
957 BEGIN
958 IF TG_OP = 'DELETE' THEN
959 "reference_lost" := TRUE;
960 ELSE
961 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
962 END IF;
963 IF
964 "reference_lost" AND NOT EXISTS (
965 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
966 )
967 THEN
968 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
969 END IF;
970 RETURN NULL;
971 END;
972 $$;
974 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
975 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
976 FOR EACH ROW EXECUTE PROCEDURE
977 "last_draft_deletes_initiative_trigger"();
979 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
980 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
983 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
984 RETURNS TRIGGER
985 LANGUAGE 'plpgsql' VOLATILE AS $$
986 BEGIN
987 IF NOT EXISTS (
988 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
989 ) THEN
990 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
991 END IF;
992 RETURN NULL;
993 END;
994 $$;
996 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
997 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
998 FOR EACH ROW EXECUTE PROCEDURE
999 "suggestion_requires_first_opinion_trigger"();
1001 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1002 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1005 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1006 RETURNS TRIGGER
1007 LANGUAGE 'plpgsql' VOLATILE AS $$
1008 DECLARE
1009 "reference_lost" BOOLEAN;
1010 BEGIN
1011 IF TG_OP = 'DELETE' THEN
1012 "reference_lost" := TRUE;
1013 ELSE
1014 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1015 END IF;
1016 IF
1017 "reference_lost" AND NOT EXISTS (
1018 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1019 )
1020 THEN
1021 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1022 END IF;
1023 RETURN NULL;
1024 END;
1025 $$;
1027 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1028 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1029 FOR EACH ROW EXECUTE PROCEDURE
1030 "last_opinion_deletes_suggestion_trigger"();
1032 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1033 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1037 ---------------------------------------------------------------
1038 -- Ensure that votes are not modified when issues are frozen --
1039 ---------------------------------------------------------------
1041 -- NOTE: Frontends should ensure this anyway, but in case of programming
1042 -- errors the following triggers ensure data integrity.
1045 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1046 RETURNS TRIGGER
1047 LANGUAGE 'plpgsql' VOLATILE AS $$
1048 DECLARE
1049 "issue_id_v" "issue"."id"%TYPE;
1050 "issue_row" "issue"%ROWTYPE;
1051 BEGIN
1052 IF TG_OP = 'DELETE' THEN
1053 "issue_id_v" := OLD."issue_id";
1054 ELSE
1055 "issue_id_v" := NEW."issue_id";
1056 END IF;
1057 SELECT INTO "issue_row" * FROM "issue"
1058 WHERE "id" = "issue_id_v" FOR SHARE;
1059 IF "issue_row"."closed" NOTNULL THEN
1060 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
1061 END IF;
1062 RETURN NULL;
1063 END;
1064 $$;
1066 CREATE TRIGGER "forbid_changes_on_closed_issue"
1067 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1068 FOR EACH ROW EXECUTE PROCEDURE
1069 "forbid_changes_on_closed_issue_trigger"();
1071 CREATE TRIGGER "forbid_changes_on_closed_issue"
1072 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1073 FOR EACH ROW EXECUTE PROCEDURE
1074 "forbid_changes_on_closed_issue_trigger"();
1076 CREATE TRIGGER "forbid_changes_on_closed_issue"
1077 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1078 FOR EACH ROW EXECUTE PROCEDURE
1079 "forbid_changes_on_closed_issue_trigger"();
1081 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"';
1082 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';
1083 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';
1084 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';
1088 --------------------------------------------------------------------
1089 -- Auto-retrieval of fields only needed for referential integrity --
1090 --------------------------------------------------------------------
1093 CREATE FUNCTION "autofill_issue_id_trigger"()
1094 RETURNS TRIGGER
1095 LANGUAGE 'plpgsql' VOLATILE AS $$
1096 BEGIN
1097 IF NEW."issue_id" ISNULL THEN
1098 SELECT "issue_id" INTO NEW."issue_id"
1099 FROM "initiative" WHERE "id" = NEW."initiative_id";
1100 END IF;
1101 RETURN NEW;
1102 END;
1103 $$;
1105 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1106 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1108 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1109 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1111 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1112 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1113 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1116 CREATE FUNCTION "autofill_initiative_id_trigger"()
1117 RETURNS TRIGGER
1118 LANGUAGE 'plpgsql' VOLATILE AS $$
1119 BEGIN
1120 IF NEW."initiative_id" ISNULL THEN
1121 SELECT "initiative_id" INTO NEW."initiative_id"
1122 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1123 END IF;
1124 RETURN NEW;
1125 END;
1126 $$;
1128 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1129 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1131 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1132 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1136 -----------------------------------------------------
1137 -- Automatic calculation of certain default values --
1138 -----------------------------------------------------
1141 CREATE FUNCTION "copy_timings_trigger"()
1142 RETURNS TRIGGER
1143 LANGUAGE 'plpgsql' VOLATILE AS $$
1144 DECLARE
1145 "policy_row" "policy"%ROWTYPE;
1146 BEGIN
1147 SELECT * INTO "policy_row" FROM "policy"
1148 WHERE "id" = NEW."policy_id";
1149 IF NEW."admission_time" ISNULL THEN
1150 NEW."admission_time" := "policy_row"."admission_time";
1151 END IF;
1152 IF NEW."discussion_time" ISNULL THEN
1153 NEW."discussion_time" := "policy_row"."discussion_time";
1154 END IF;
1155 IF NEW."verification_time" ISNULL THEN
1156 NEW."verification_time" := "policy_row"."verification_time";
1157 END IF;
1158 IF NEW."voting_time" ISNULL THEN
1159 NEW."voting_time" := "policy_row"."voting_time";
1160 END IF;
1161 RETURN NEW;
1162 END;
1163 $$;
1165 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1166 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1168 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1169 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1172 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
1173 RETURNS TRIGGER
1174 LANGUAGE 'plpgsql' VOLATILE AS $$
1175 BEGIN
1176 IF NEW."draft_id" ISNULL THEN
1177 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1178 WHERE "initiative_id" = NEW."initiative_id";
1179 END IF;
1180 RETURN NEW;
1181 END;
1182 $$;
1184 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1185 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
1187 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
1188 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';
1192 ----------------------------------------
1193 -- Automatic creation of dependencies --
1194 ----------------------------------------
1197 CREATE FUNCTION "autocreate_interest_trigger"()
1198 RETURNS TRIGGER
1199 LANGUAGE 'plpgsql' VOLATILE AS $$
1200 BEGIN
1201 IF NOT EXISTS (
1202 SELECT NULL FROM "initiative" JOIN "interest"
1203 ON "initiative"."issue_id" = "interest"."issue_id"
1204 WHERE "initiative"."id" = NEW."initiative_id"
1205 AND "interest"."member_id" = NEW."member_id"
1206 ) THEN
1207 BEGIN
1208 INSERT INTO "interest" ("issue_id", "member_id")
1209 SELECT "issue_id", NEW."member_id"
1210 FROM "initiative" WHERE "id" = NEW."initiative_id";
1211 EXCEPTION WHEN unique_violation THEN END;
1212 END IF;
1213 RETURN NEW;
1214 END;
1215 $$;
1217 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1218 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1220 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1221 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';
1224 CREATE FUNCTION "autocreate_supporter_trigger"()
1225 RETURNS TRIGGER
1226 LANGUAGE 'plpgsql' VOLATILE AS $$
1227 BEGIN
1228 IF NOT EXISTS (
1229 SELECT NULL FROM "suggestion" JOIN "supporter"
1230 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1231 WHERE "suggestion"."id" = NEW."suggestion_id"
1232 AND "supporter"."member_id" = NEW."member_id"
1233 ) THEN
1234 BEGIN
1235 INSERT INTO "supporter" ("initiative_id", "member_id")
1236 SELECT "initiative_id", NEW."member_id"
1237 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1238 EXCEPTION WHEN unique_violation THEN END;
1239 END IF;
1240 RETURN NEW;
1241 END;
1242 $$;
1244 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1245 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1247 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1248 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.';
1252 ------------------------------------------
1253 -- Views and helper functions for views --
1254 ------------------------------------------
1257 CREATE VIEW "active_delegation" AS
1258 SELECT "delegation".* FROM "delegation"
1259 JOIN "member" ON "delegation"."truster_id" = "member"."id"
1260 WHERE "member"."active" = TRUE;
1262 COMMENT ON VIEW "active_delegation" IS 'Helper view for views "global_delegation", "area_delegation" and "issue_delegation": Contains delegations where the truster_id refers to an active member and includes those delegations where trustee_id is NULL';
1265 CREATE VIEW "global_delegation" AS
1266 SELECT "id", "truster_id", "trustee_id"
1267 FROM "active_delegation" WHERE "scope" = 'global';
1269 COMMENT ON VIEW "global_delegation" IS 'Global delegations from active members';
1272 CREATE VIEW "area_delegation" AS
1273 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1274 "area"."id" AS "area_id",
1275 "delegation"."id",
1276 "delegation"."truster_id",
1277 "delegation"."trustee_id",
1278 "delegation"."scope"
1279 FROM "area" JOIN "active_delegation" AS "delegation"
1280 ON "delegation"."scope" = 'global'
1281 OR "delegation"."area_id" = "area"."id"
1282 ORDER BY
1283 "area"."id",
1284 "delegation"."truster_id",
1285 "delegation"."scope" DESC;
1287 COMMENT ON VIEW "area_delegation" IS 'Resulting area delegations from active members; can include rows with trustee_id set to NULL';
1290 CREATE VIEW "issue_delegation" AS
1291 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1292 "issue"."id" AS "issue_id",
1293 "delegation"."id",
1294 "delegation"."truster_id",
1295 "delegation"."trustee_id",
1296 "delegation"."scope"
1297 FROM "issue" JOIN "active_delegation" AS "delegation"
1298 ON "delegation"."scope" = 'global'
1299 OR "delegation"."area_id" = "issue"."area_id"
1300 OR "delegation"."issue_id" = "issue"."id"
1301 ORDER BY
1302 "issue"."id",
1303 "delegation"."truster_id",
1304 "delegation"."scope" DESC;
1306 COMMENT ON VIEW "issue_delegation" IS 'Resulting issue delegations from active members; can include rows with trustee_id set to NULL';
1309 CREATE FUNCTION "membership_weight_with_skipping"
1310 ( "area_id_p" "area"."id"%TYPE,
1311 "member_id_p" "member"."id"%TYPE,
1312 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1313 RETURNS INT4
1314 LANGUAGE 'plpgsql' STABLE AS $$
1315 DECLARE
1316 "sum_v" INT4;
1317 "delegation_row" "area_delegation"%ROWTYPE;
1318 BEGIN
1319 "sum_v" := 1;
1320 FOR "delegation_row" IN
1321 SELECT "area_delegation".*
1322 FROM "area_delegation" LEFT JOIN "membership"
1323 ON "membership"."area_id" = "area_id_p"
1324 AND "membership"."member_id" = "area_delegation"."truster_id"
1325 WHERE "area_delegation"."area_id" = "area_id_p"
1326 AND "area_delegation"."trustee_id" = "member_id_p"
1327 AND "membership"."member_id" ISNULL
1328 LOOP
1329 IF NOT
1330 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1331 THEN
1332 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1333 "area_id_p",
1334 "delegation_row"."truster_id",
1335 "skip_member_ids_p" || "delegation_row"."truster_id"
1336 );
1337 END IF;
1338 END LOOP;
1339 RETURN "sum_v";
1340 END;
1341 $$;
1343 COMMENT ON FUNCTION "membership_weight_with_skipping"
1344 ( "area"."id"%TYPE,
1345 "member"."id"%TYPE,
1346 INT4[] )
1347 IS 'Helper function for "membership_weight" function';
1350 CREATE FUNCTION "membership_weight"
1351 ( "area_id_p" "area"."id"%TYPE,
1352 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1353 RETURNS INT4
1354 LANGUAGE 'plpgsql' STABLE AS $$
1355 BEGIN
1356 RETURN "membership_weight_with_skipping"(
1357 "area_id_p",
1358 "member_id_p",
1359 ARRAY["member_id_p"]
1360 );
1361 END;
1362 $$;
1364 COMMENT ON FUNCTION "membership_weight"
1365 ( "area"."id"%TYPE,
1366 "member"."id"%TYPE )
1367 IS 'Calculates the potential voting weight of a member in a given area';
1370 CREATE VIEW "member_count_view" AS
1371 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1373 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1376 CREATE VIEW "area_member_count" AS
1377 SELECT
1378 "area"."id" AS "area_id",
1379 count("member"."id") AS "direct_member_count",
1380 coalesce(
1381 sum(
1382 CASE WHEN "member"."id" NOTNULL THEN
1383 "membership_weight"("area"."id", "member"."id")
1384 ELSE 0 END
1385 )
1386 ) AS "member_weight",
1387 coalesce(
1388 sum(
1389 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
1390 "membership_weight"("area"."id", "member"."id")
1391 ELSE 0 END
1392 )
1393 ) AS "autoreject_weight"
1394 FROM "area"
1395 LEFT JOIN "membership"
1396 ON "area"."id" = "membership"."area_id"
1397 LEFT JOIN "member"
1398 ON "membership"."member_id" = "member"."id"
1399 AND "member"."active"
1400 GROUP BY "area"."id";
1402 COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"';
1405 CREATE VIEW "opening_draft" AS
1406 SELECT "draft".* FROM (
1407 SELECT
1408 "initiative"."id" AS "initiative_id",
1409 min("draft"."id") AS "draft_id"
1410 FROM "initiative" JOIN "draft"
1411 ON "initiative"."id" = "draft"."initiative_id"
1412 GROUP BY "initiative"."id"
1413 ) AS "subquery"
1414 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1416 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1419 CREATE VIEW "current_draft" AS
1420 SELECT "draft".* FROM (
1421 SELECT
1422 "initiative"."id" AS "initiative_id",
1423 max("draft"."id") AS "draft_id"
1424 FROM "initiative" JOIN "draft"
1425 ON "initiative"."id" = "draft"."initiative_id"
1426 GROUP BY "initiative"."id"
1427 ) AS "subquery"
1428 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1430 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1433 CREATE VIEW "critical_opinion" AS
1434 SELECT * FROM "opinion"
1435 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
1436 OR ("degree" = -2 AND "fulfilled" = TRUE);
1438 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
1441 CREATE VIEW "battle_view" AS
1442 SELECT
1443 "issue"."id" AS "issue_id",
1444 "winning_initiative"."id" AS "winning_initiative_id",
1445 "losing_initiative"."id" AS "losing_initiative_id",
1446 sum(
1447 CASE WHEN
1448 coalesce("better_vote"."grade", 0) >
1449 coalesce("worse_vote"."grade", 0)
1450 THEN "direct_voter"."weight" ELSE 0 END
1451 ) AS "count"
1452 FROM "issue"
1453 LEFT JOIN "direct_voter"
1454 ON "issue"."id" = "direct_voter"."issue_id"
1455 JOIN "initiative" AS "winning_initiative"
1456 ON "issue"."id" = "winning_initiative"."issue_id"
1457 AND "winning_initiative"."agreed"
1458 JOIN "initiative" AS "losing_initiative"
1459 ON "issue"."id" = "losing_initiative"."issue_id"
1460 AND "losing_initiative"."agreed"
1461 LEFT JOIN "vote" AS "better_vote"
1462 ON "direct_voter"."member_id" = "better_vote"."member_id"
1463 AND "winning_initiative"."id" = "better_vote"."initiative_id"
1464 LEFT JOIN "vote" AS "worse_vote"
1465 ON "direct_voter"."member_id" = "worse_vote"."member_id"
1466 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
1467 WHERE "issue"."closed" NOTNULL
1468 AND "issue"."cleaned" ISNULL
1469 AND "winning_initiative"."id" != "losing_initiative"."id"
1470 GROUP BY
1471 "issue"."id",
1472 "winning_initiative"."id",
1473 "losing_initiative"."id";
1475 COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative to another; Used to fill "battle" table';
1478 CREATE VIEW "expired_session" AS
1479 SELECT * FROM "session" WHERE now() > "expiry";
1481 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
1482 DELETE FROM "session" WHERE "ident" = OLD."ident";
1484 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
1485 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
1488 CREATE VIEW "open_issue" AS
1489 SELECT * FROM "issue" WHERE "closed" ISNULL;
1491 COMMENT ON VIEW "open_issue" IS 'All open issues';
1494 CREATE VIEW "issue_with_ranks_missing" AS
1495 SELECT * FROM "issue"
1496 WHERE "fully_frozen" NOTNULL
1497 AND "closed" NOTNULL
1498 AND "ranks_available" = FALSE;
1500 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
1503 CREATE VIEW "member_contingent" AS
1504 SELECT
1505 "member"."id" AS "member_id",
1506 "contingent"."time_frame",
1507 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
1508 (
1509 SELECT count(1) FROM "draft"
1510 WHERE "draft"."author_id" = "member"."id"
1511 AND "draft"."created" > now() - "contingent"."time_frame"
1512 ) + (
1513 SELECT count(1) FROM "suggestion"
1514 WHERE "suggestion"."author_id" = "member"."id"
1515 AND "suggestion"."created" > now() - "contingent"."time_frame"
1516 )
1517 ELSE NULL END AS "text_entry_count",
1518 "contingent"."text_entry_limit",
1519 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
1520 SELECT count(1) FROM "opening_draft"
1521 WHERE "opening_draft"."author_id" = "member"."id"
1522 AND "opening_draft"."created" > now() - "contingent"."time_frame"
1523 ) ELSE NULL END AS "initiative_count",
1524 "contingent"."initiative_limit"
1525 FROM "member" CROSS JOIN "contingent";
1527 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
1529 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
1530 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
1533 CREATE VIEW "member_contingent_left" AS
1534 SELECT
1535 "member_id",
1536 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
1537 max("initiative_limit" - "initiative_count") AS "initiatives_left"
1538 FROM "member_contingent" GROUP BY "member_id";
1540 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.';
1543 CREATE TYPE "timeline_event" AS ENUM (
1544 'issue_created',
1545 'issue_canceled',
1546 'issue_accepted',
1547 'issue_half_frozen',
1548 'issue_finished_without_voting',
1549 'issue_voting_started',
1550 'issue_finished_after_voting',
1551 'initiative_created',
1552 'initiative_revoked',
1553 'draft_created',
1554 'suggestion_created');
1556 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables';
1559 CREATE VIEW "timeline_issue" AS
1560 SELECT
1561 "created" AS "occurrence",
1562 'issue_created'::"timeline_event" AS "event",
1563 "id" AS "issue_id"
1564 FROM "issue"
1565 UNION ALL
1566 SELECT
1567 "closed" AS "occurrence",
1568 'issue_canceled'::"timeline_event" AS "event",
1569 "id" AS "issue_id"
1570 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
1571 UNION ALL
1572 SELECT
1573 "accepted" AS "occurrence",
1574 'issue_accepted'::"timeline_event" AS "event",
1575 "id" AS "issue_id"
1576 FROM "issue" WHERE "accepted" NOTNULL
1577 UNION ALL
1578 SELECT
1579 "half_frozen" AS "occurrence",
1580 'issue_half_frozen'::"timeline_event" AS "event",
1581 "id" AS "issue_id"
1582 FROM "issue" WHERE "half_frozen" NOTNULL
1583 UNION ALL
1584 SELECT
1585 "fully_frozen" AS "occurrence",
1586 'issue_voting_started'::"timeline_event" AS "event",
1587 "id" AS "issue_id"
1588 FROM "issue"
1589 WHERE "fully_frozen" NOTNULL
1590 AND ("closed" ISNULL OR "closed" != "fully_frozen")
1591 UNION ALL
1592 SELECT
1593 "closed" AS "occurrence",
1594 CASE WHEN "fully_frozen" = "closed" THEN
1595 'issue_finished_without_voting'::"timeline_event"
1596 ELSE
1597 'issue_finished_after_voting'::"timeline_event"
1598 END AS "event",
1599 "id" AS "issue_id"
1600 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
1602 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view';
1605 CREATE VIEW "timeline_initiative" AS
1606 SELECT
1607 "created" AS "occurrence",
1608 'initiative_created'::"timeline_event" AS "event",
1609 "id" AS "initiative_id"
1610 FROM "initiative"
1611 UNION ALL
1612 SELECT
1613 "revoked" AS "occurrence",
1614 'initiative_revoked'::"timeline_event" AS "event",
1615 "id" AS "initiative_id"
1616 FROM "initiative" WHERE "revoked" NOTNULL;
1618 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view';
1621 CREATE VIEW "timeline_draft" AS
1622 SELECT
1623 "created" AS "occurrence",
1624 'draft_created'::"timeline_event" AS "event",
1625 "id" AS "draft_id"
1626 FROM "draft";
1628 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view';
1631 CREATE VIEW "timeline_suggestion" AS
1632 SELECT
1633 "created" AS "occurrence",
1634 'suggestion_created'::"timeline_event" AS "event",
1635 "id" AS "suggestion_id"
1636 FROM "suggestion";
1638 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view';
1641 CREATE VIEW "timeline" AS
1642 SELECT
1643 "occurrence",
1644 "event",
1645 "issue_id",
1646 NULL AS "initiative_id",
1647 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
1648 NULL::INT8 AS "suggestion_id"
1649 FROM "timeline_issue"
1650 UNION ALL
1651 SELECT
1652 "occurrence",
1653 "event",
1654 NULL AS "issue_id",
1655 "initiative_id",
1656 NULL AS "draft_id",
1657 NULL AS "suggestion_id"
1658 FROM "timeline_initiative"
1659 UNION ALL
1660 SELECT
1661 "occurrence",
1662 "event",
1663 NULL AS "issue_id",
1664 NULL AS "initiative_id",
1665 "draft_id",
1666 NULL AS "suggestion_id"
1667 FROM "timeline_draft"
1668 UNION ALL
1669 SELECT
1670 "occurrence",
1671 "event",
1672 NULL AS "issue_id",
1673 NULL AS "initiative_id",
1674 NULL AS "draft_id",
1675 "suggestion_id"
1676 FROM "timeline_suggestion";
1678 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system';
1682 --------------------------------------------------
1683 -- Set returning function for delegation chains --
1684 --------------------------------------------------
1687 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
1688 ('first', 'intermediate', 'last', 'repetition');
1690 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
1693 CREATE TYPE "delegation_chain_row" AS (
1694 "index" INT4,
1695 "member_id" INT4,
1696 "member_active" BOOLEAN,
1697 "participation" BOOLEAN,
1698 "overridden" BOOLEAN,
1699 "scope_in" "delegation_scope",
1700 "scope_out" "delegation_scope",
1701 "disabled_out" BOOLEAN,
1702 "loop" "delegation_chain_loop_tag" );
1704 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
1706 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
1707 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';
1708 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
1709 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
1710 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
1711 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
1712 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
1715 CREATE FUNCTION "delegation_chain"
1716 ( "member_id_p" "member"."id"%TYPE,
1717 "area_id_p" "area"."id"%TYPE,
1718 "issue_id_p" "issue"."id"%TYPE,
1719 "simulate_trustee_id_p" "member"."id"%TYPE )
1720 RETURNS SETOF "delegation_chain_row"
1721 LANGUAGE 'plpgsql' STABLE AS $$
1722 DECLARE
1723 "issue_row" "issue"%ROWTYPE;
1724 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
1725 "loop_member_id_v" "member"."id"%TYPE;
1726 "output_row" "delegation_chain_row";
1727 "output_rows" "delegation_chain_row"[];
1728 "delegation_row" "delegation"%ROWTYPE;
1729 "row_count" INT4;
1730 "i" INT4;
1731 "loop_v" BOOLEAN;
1732 BEGIN
1733 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1734 "visited_member_ids" := '{}';
1735 "loop_member_id_v" := NULL;
1736 "output_rows" := '{}';
1737 "output_row"."index" := 0;
1738 "output_row"."member_id" := "member_id_p";
1739 "output_row"."member_active" := TRUE;
1740 "output_row"."participation" := FALSE;
1741 "output_row"."overridden" := FALSE;
1742 "output_row"."disabled_out" := FALSE;
1743 "output_row"."scope_out" := NULL;
1744 LOOP
1745 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
1746 "loop_member_id_v" := "output_row"."member_id";
1747 ELSE
1748 "visited_member_ids" :=
1749 "visited_member_ids" || "output_row"."member_id";
1750 END IF;
1751 IF "output_row"."participation" THEN
1752 "output_row"."overridden" := TRUE;
1753 END IF;
1754 "output_row"."scope_in" := "output_row"."scope_out";
1755 IF EXISTS (
1756 SELECT NULL FROM "member"
1757 WHERE "id" = "output_row"."member_id" AND "active"
1758 ) THEN
1759 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
1760 SELECT * INTO "delegation_row" FROM "delegation"
1761 WHERE "truster_id" = "output_row"."member_id"
1762 AND "scope" = 'global';
1763 ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN
1764 "output_row"."participation" := EXISTS (
1765 SELECT NULL FROM "membership"
1766 WHERE "area_id" = "area_id_p"
1767 AND "member_id" = "output_row"."member_id"
1768 );
1769 SELECT * INTO "delegation_row" FROM "delegation"
1770 WHERE "truster_id" = "output_row"."member_id"
1771 AND ("scope" = 'global' OR "area_id" = "area_id_p")
1772 ORDER BY "scope" DESC;
1773 ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN
1774 "output_row"."participation" := EXISTS (
1775 SELECT NULL FROM "interest"
1776 WHERE "issue_id" = "issue_id_p"
1777 AND "member_id" = "output_row"."member_id"
1778 );
1779 SELECT * INTO "delegation_row" FROM "delegation"
1780 WHERE "truster_id" = "output_row"."member_id"
1781 AND ("scope" = 'global' OR
1782 "area_id" = "issue_row"."area_id" OR
1783 "issue_id" = "issue_id_p"
1784 )
1785 ORDER BY "scope" DESC;
1786 ELSE
1787 RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.';
1788 END IF;
1789 ELSE
1790 "output_row"."member_active" := FALSE;
1791 "output_row"."participation" := FALSE;
1792 "output_row"."scope_out" := NULL;
1793 "delegation_row" := ROW(NULL);
1794 END IF;
1795 IF
1796 "output_row"."member_id" = "member_id_p" AND
1797 "simulate_trustee_id_p" NOTNULL
1798 THEN
1799 "output_row"."scope_out" := CASE
1800 WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global'
1801 WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area'
1802 WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue'
1803 END;
1804 "output_rows" := "output_rows" || "output_row";
1805 "output_row"."member_id" := "simulate_trustee_id_p";
1806 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
1807 "output_row"."scope_out" := "delegation_row"."scope";
1808 "output_rows" := "output_rows" || "output_row";
1809 "output_row"."member_id" := "delegation_row"."trustee_id";
1810 ELSIF "delegation_row"."scope" NOTNULL THEN
1811 "output_row"."scope_out" := "delegation_row"."scope";
1812 "output_row"."disabled_out" := TRUE;
1813 "output_rows" := "output_rows" || "output_row";
1814 EXIT;
1815 ELSE
1816 "output_row"."scope_out" := NULL;
1817 "output_rows" := "output_rows" || "output_row";
1818 EXIT;
1819 END IF;
1820 EXIT WHEN "loop_member_id_v" NOTNULL;
1821 "output_row"."index" := "output_row"."index" + 1;
1822 END LOOP;
1823 "row_count" := array_upper("output_rows", 1);
1824 "i" := 1;
1825 "loop_v" := FALSE;
1826 LOOP
1827 "output_row" := "output_rows"["i"];
1828 EXIT WHEN "output_row" ISNULL;
1829 IF "loop_v" THEN
1830 IF "i" + 1 = "row_count" THEN
1831 "output_row"."loop" := 'last';
1832 ELSIF "i" = "row_count" THEN
1833 "output_row"."loop" := 'repetition';
1834 ELSE
1835 "output_row"."loop" := 'intermediate';
1836 END IF;
1837 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
1838 "output_row"."loop" := 'first';
1839 "loop_v" := TRUE;
1840 END IF;
1841 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
1842 "output_row"."participation" := NULL;
1843 END IF;
1844 RETURN NEXT "output_row";
1845 "i" := "i" + 1;
1846 END LOOP;
1847 RETURN;
1848 END;
1849 $$;
1851 COMMENT ON FUNCTION "delegation_chain"
1852 ( "member"."id"%TYPE,
1853 "area"."id"%TYPE,
1854 "issue"."id"%TYPE,
1855 "member"."id"%TYPE )
1856 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
1858 CREATE FUNCTION "delegation_chain"
1859 ( "member_id_p" "member"."id"%TYPE,
1860 "area_id_p" "area"."id"%TYPE,
1861 "issue_id_p" "issue"."id"%TYPE )
1862 RETURNS SETOF "delegation_chain_row"
1863 LANGUAGE 'plpgsql' STABLE AS $$
1864 DECLARE
1865 "result_row" "delegation_chain_row";
1866 BEGIN
1867 FOR "result_row" IN
1868 SELECT * FROM "delegation_chain"(
1869 "member_id_p", "area_id_p", "issue_id_p", NULL
1870 )
1871 LOOP
1872 RETURN NEXT "result_row";
1873 END LOOP;
1874 RETURN;
1875 END;
1876 $$;
1878 COMMENT ON FUNCTION "delegation_chain"
1879 ( "member"."id"%TYPE,
1880 "area"."id"%TYPE,
1881 "issue"."id"%TYPE )
1882 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
1886 ------------------------------
1887 -- Comparison by vote count --
1888 ------------------------------
1890 CREATE FUNCTION "vote_ratio"
1891 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
1892 "negative_votes_p" "initiative"."negative_votes"%TYPE )
1893 RETURNS FLOAT8
1894 LANGUAGE 'plpgsql' STABLE AS $$
1895 BEGIN
1896 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
1897 RETURN
1898 "positive_votes_p"::FLOAT8 /
1899 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
1900 ELSIF "positive_votes_p" > 0 THEN
1901 RETURN "positive_votes_p";
1902 ELSIF "negative_votes_p" > 0 THEN
1903 RETURN 1 - "negative_votes_p";
1904 ELSE
1905 RETURN 0.5;
1906 END IF;
1907 END;
1908 $$;
1910 COMMENT ON FUNCTION "vote_ratio"
1911 ( "initiative"."positive_votes"%TYPE,
1912 "initiative"."negative_votes"%TYPE )
1913 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.';
1917 ------------------------------------------------
1918 -- Locking for snapshots and voting procedure --
1919 ------------------------------------------------
1922 CREATE FUNCTION "share_row_lock_issue_trigger"()
1923 RETURNS TRIGGER
1924 LANGUAGE 'plpgsql' VOLATILE AS $$
1925 BEGIN
1926 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
1927 PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;
1928 END IF;
1929 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1930 PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;
1931 RETURN NEW;
1932 ELSE
1933 RETURN OLD;
1934 END IF;
1935 END;
1936 $$;
1938 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';
1941 CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()
1942 RETURNS TRIGGER
1943 LANGUAGE 'plpgsql' VOLATILE AS $$
1944 BEGIN
1945 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
1946 PERFORM NULL FROM "issue"
1947 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
1948 WHERE "initiative"."id" = OLD."initiative_id"
1949 FOR SHARE OF "issue";
1950 END IF;
1951 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1952 PERFORM NULL FROM "issue"
1953 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
1954 WHERE "initiative"."id" = NEW."initiative_id"
1955 FOR SHARE OF "issue";
1956 RETURN NEW;
1957 ELSE
1958 RETURN OLD;
1959 END IF;
1960 END;
1961 $$;
1963 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';
1966 CREATE TRIGGER "share_row_lock_issue"
1967 BEFORE INSERT OR UPDATE OR DELETE ON "initiative"
1968 FOR EACH ROW EXECUTE PROCEDURE
1969 "share_row_lock_issue_trigger"();
1971 CREATE TRIGGER "share_row_lock_issue"
1972 BEFORE INSERT OR UPDATE OR DELETE ON "interest"
1973 FOR EACH ROW EXECUTE PROCEDURE
1974 "share_row_lock_issue_trigger"();
1976 CREATE TRIGGER "share_row_lock_issue"
1977 BEFORE INSERT OR UPDATE OR DELETE ON "supporter"
1978 FOR EACH ROW EXECUTE PROCEDURE
1979 "share_row_lock_issue_trigger"();
1981 CREATE TRIGGER "share_row_lock_issue_via_initiative"
1982 BEFORE INSERT OR UPDATE OR DELETE ON "opinion"
1983 FOR EACH ROW EXECUTE PROCEDURE
1984 "share_row_lock_issue_via_initiative_trigger"();
1986 CREATE TRIGGER "share_row_lock_issue"
1987 BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter"
1988 FOR EACH ROW EXECUTE PROCEDURE
1989 "share_row_lock_issue_trigger"();
1991 CREATE TRIGGER "share_row_lock_issue"
1992 BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter"
1993 FOR EACH ROW EXECUTE PROCEDURE
1994 "share_row_lock_issue_trigger"();
1996 CREATE TRIGGER "share_row_lock_issue"
1997 BEFORE INSERT OR UPDATE OR DELETE ON "vote"
1998 FOR EACH ROW EXECUTE PROCEDURE
1999 "share_row_lock_issue_trigger"();
2001 COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function';
2002 COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function';
2003 COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function';
2004 COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function';
2005 COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function';
2006 COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function';
2007 COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function';
2010 CREATE FUNCTION "lock_issue"
2011 ( "issue_id_p" "issue"."id"%TYPE )
2012 RETURNS VOID
2013 LANGUAGE 'plpgsql' VOLATILE AS $$
2014 BEGIN
2015 LOCK TABLE "member" IN SHARE MODE;
2016 LOCK TABLE "membership" IN SHARE MODE;
2017 LOCK TABLE "policy" IN SHARE MODE;
2018 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2019 -- NOTE: The row-level exclusive lock in combination with the
2020 -- share_row_lock_issue(_via_initiative)_trigger functions (which
2021 -- acquire a row-level share lock on the issue) ensure that no data
2022 -- is changed, which could affect calculation of snapshots or
2023 -- counting of votes. Table "delegation" must be table-level-locked,
2024 -- as it also contains issue- and global-scope delegations.
2025 LOCK TABLE "delegation" IN SHARE MODE;
2026 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
2027 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
2028 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
2029 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
2030 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
2031 RETURN;
2032 END;
2033 $$;
2035 COMMENT ON FUNCTION "lock_issue"
2036 ( "issue"."id"%TYPE )
2037 IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';
2041 -------------------------------
2042 -- Materialize member counts --
2043 -------------------------------
2045 CREATE FUNCTION "calculate_member_counts"()
2046 RETURNS VOID
2047 LANGUAGE 'plpgsql' VOLATILE AS $$
2048 BEGIN
2049 LOCK TABLE "member" IN SHARE MODE;
2050 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
2051 LOCK TABLE "area" IN EXCLUSIVE MODE;
2052 LOCK TABLE "membership" IN SHARE MODE;
2053 DELETE FROM "member_count";
2054 INSERT INTO "member_count" ("total_count")
2055 SELECT "total_count" FROM "member_count_view";
2056 UPDATE "area" SET
2057 "direct_member_count" = "view"."direct_member_count",
2058 "member_weight" = "view"."member_weight",
2059 "autoreject_weight" = "view"."autoreject_weight"
2060 FROM "area_member_count" AS "view"
2061 WHERE "view"."area_id" = "area"."id";
2062 RETURN;
2063 END;
2064 $$;
2066 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"';
2070 ------------------------------
2071 -- Calculation of snapshots --
2072 ------------------------------
2074 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
2075 ( "issue_id_p" "issue"."id"%TYPE,
2076 "member_id_p" "member"."id"%TYPE,
2077 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2078 RETURNS "direct_population_snapshot"."weight"%TYPE
2079 LANGUAGE 'plpgsql' VOLATILE AS $$
2080 DECLARE
2081 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2082 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
2083 "weight_v" INT4;
2084 "sub_weight_v" INT4;
2085 BEGIN
2086 "weight_v" := 0;
2087 FOR "issue_delegation_row" IN
2088 SELECT * FROM "issue_delegation"
2089 WHERE "trustee_id" = "member_id_p"
2090 AND "issue_id" = "issue_id_p"
2091 LOOP
2092 IF NOT EXISTS (
2093 SELECT NULL FROM "direct_population_snapshot"
2094 WHERE "issue_id" = "issue_id_p"
2095 AND "event" = 'periodic'
2096 AND "member_id" = "issue_delegation_row"."truster_id"
2097 ) AND NOT EXISTS (
2098 SELECT NULL FROM "delegating_population_snapshot"
2099 WHERE "issue_id" = "issue_id_p"
2100 AND "event" = 'periodic'
2101 AND "member_id" = "issue_delegation_row"."truster_id"
2102 ) THEN
2103 "delegate_member_ids_v" :=
2104 "member_id_p" || "delegate_member_ids_p";
2105 INSERT INTO "delegating_population_snapshot" (
2106 "issue_id",
2107 "event",
2108 "member_id",
2109 "scope",
2110 "delegate_member_ids"
2111 ) VALUES (
2112 "issue_id_p",
2113 'periodic',
2114 "issue_delegation_row"."truster_id",
2115 "issue_delegation_row"."scope",
2116 "delegate_member_ids_v"
2117 );
2118 "sub_weight_v" := 1 +
2119 "weight_of_added_delegations_for_population_snapshot"(
2120 "issue_id_p",
2121 "issue_delegation_row"."truster_id",
2122 "delegate_member_ids_v"
2123 );
2124 UPDATE "delegating_population_snapshot"
2125 SET "weight" = "sub_weight_v"
2126 WHERE "issue_id" = "issue_id_p"
2127 AND "event" = 'periodic'
2128 AND "member_id" = "issue_delegation_row"."truster_id";
2129 "weight_v" := "weight_v" + "sub_weight_v";
2130 END IF;
2131 END LOOP;
2132 RETURN "weight_v";
2133 END;
2134 $$;
2136 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
2137 ( "issue"."id"%TYPE,
2138 "member"."id"%TYPE,
2139 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2140 IS 'Helper function for "create_population_snapshot" function';
2143 CREATE FUNCTION "create_population_snapshot"
2144 ( "issue_id_p" "issue"."id"%TYPE )
2145 RETURNS VOID
2146 LANGUAGE 'plpgsql' VOLATILE AS $$
2147 DECLARE
2148 "member_id_v" "member"."id"%TYPE;
2149 BEGIN
2150 DELETE FROM "direct_population_snapshot"
2151 WHERE "issue_id" = "issue_id_p"
2152 AND "event" = 'periodic';
2153 DELETE FROM "delegating_population_snapshot"
2154 WHERE "issue_id" = "issue_id_p"
2155 AND "event" = 'periodic';
2156 INSERT INTO "direct_population_snapshot"
2157 ("issue_id", "event", "member_id")
2158 SELECT
2159 "issue_id_p" AS "issue_id",
2160 'periodic'::"snapshot_event" AS "event",
2161 "member"."id" AS "member_id"
2162 FROM "issue"
2163 JOIN "area" ON "issue"."area_id" = "area"."id"
2164 JOIN "membership" ON "area"."id" = "membership"."area_id"
2165 JOIN "member" ON "membership"."member_id" = "member"."id"
2166 WHERE "issue"."id" = "issue_id_p"
2167 AND "member"."active"
2168 UNION
2169 SELECT
2170 "issue_id_p" AS "issue_id",
2171 'periodic'::"snapshot_event" AS "event",
2172 "member"."id" AS "member_id"
2173 FROM "interest" JOIN "member"
2174 ON "interest"."member_id" = "member"."id"
2175 WHERE "interest"."issue_id" = "issue_id_p"
2176 AND "member"."active";
2177 FOR "member_id_v" IN
2178 SELECT "member_id" FROM "direct_population_snapshot"
2179 WHERE "issue_id" = "issue_id_p"
2180 AND "event" = 'periodic'
2181 LOOP
2182 UPDATE "direct_population_snapshot" SET
2183 "weight" = 1 +
2184 "weight_of_added_delegations_for_population_snapshot"(
2185 "issue_id_p",
2186 "member_id_v",
2187 '{}'
2188 )
2189 WHERE "issue_id" = "issue_id_p"
2190 AND "event" = 'periodic'
2191 AND "member_id" = "member_id_v";
2192 END LOOP;
2193 RETURN;
2194 END;
2195 $$;
2197 COMMENT ON FUNCTION "create_population_snapshot"
2198 ( "issue"."id"%TYPE )
2199 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.';
2202 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2203 ( "issue_id_p" "issue"."id"%TYPE,
2204 "member_id_p" "member"."id"%TYPE,
2205 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2206 RETURNS "direct_interest_snapshot"."weight"%TYPE
2207 LANGUAGE 'plpgsql' VOLATILE AS $$
2208 DECLARE
2209 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2210 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2211 "weight_v" INT4;
2212 "sub_weight_v" INT4;
2213 BEGIN
2214 "weight_v" := 0;
2215 FOR "issue_delegation_row" IN
2216 SELECT * FROM "issue_delegation"
2217 WHERE "trustee_id" = "member_id_p"
2218 AND "issue_id" = "issue_id_p"
2219 LOOP
2220 IF NOT EXISTS (
2221 SELECT NULL FROM "direct_interest_snapshot"
2222 WHERE "issue_id" = "issue_id_p"
2223 AND "event" = 'periodic'
2224 AND "member_id" = "issue_delegation_row"."truster_id"
2225 ) AND NOT EXISTS (
2226 SELECT NULL FROM "delegating_interest_snapshot"
2227 WHERE "issue_id" = "issue_id_p"
2228 AND "event" = 'periodic'
2229 AND "member_id" = "issue_delegation_row"."truster_id"
2230 ) THEN
2231 "delegate_member_ids_v" :=
2232 "member_id_p" || "delegate_member_ids_p";
2233 INSERT INTO "delegating_interest_snapshot" (
2234 "issue_id",
2235 "event",
2236 "member_id",
2237 "scope",
2238 "delegate_member_ids"
2239 ) VALUES (
2240 "issue_id_p",
2241 'periodic',
2242 "issue_delegation_row"."truster_id",
2243 "issue_delegation_row"."scope",
2244 "delegate_member_ids_v"
2245 );
2246 "sub_weight_v" := 1 +
2247 "weight_of_added_delegations_for_interest_snapshot"(
2248 "issue_id_p",
2249 "issue_delegation_row"."truster_id",
2250 "delegate_member_ids_v"
2251 );
2252 UPDATE "delegating_interest_snapshot"
2253 SET "weight" = "sub_weight_v"
2254 WHERE "issue_id" = "issue_id_p"
2255 AND "event" = 'periodic'
2256 AND "member_id" = "issue_delegation_row"."truster_id";
2257 "weight_v" := "weight_v" + "sub_weight_v";
2258 END IF;
2259 END LOOP;
2260 RETURN "weight_v";
2261 END;
2262 $$;
2264 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2265 ( "issue"."id"%TYPE,
2266 "member"."id"%TYPE,
2267 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2268 IS 'Helper function for "create_interest_snapshot" function';
2271 CREATE FUNCTION "create_interest_snapshot"
2272 ( "issue_id_p" "issue"."id"%TYPE )
2273 RETURNS VOID
2274 LANGUAGE 'plpgsql' VOLATILE AS $$
2275 DECLARE
2276 "member_id_v" "member"."id"%TYPE;
2277 BEGIN
2278 DELETE FROM "direct_interest_snapshot"
2279 WHERE "issue_id" = "issue_id_p"
2280 AND "event" = 'periodic';
2281 DELETE FROM "delegating_interest_snapshot"
2282 WHERE "issue_id" = "issue_id_p"
2283 AND "event" = 'periodic';
2284 DELETE FROM "direct_supporter_snapshot"
2285 WHERE "issue_id" = "issue_id_p"
2286 AND "event" = 'periodic';
2287 INSERT INTO "direct_interest_snapshot"
2288 ("issue_id", "event", "member_id", "voting_requested")
2289 SELECT
2290 "issue_id_p" AS "issue_id",
2291 'periodic' AS "event",
2292 "member"."id" AS "member_id",
2293 "interest"."voting_requested"
2294 FROM "interest" JOIN "member"
2295 ON "interest"."member_id" = "member"."id"
2296 WHERE "interest"."issue_id" = "issue_id_p"
2297 AND "member"."active";
2298 FOR "member_id_v" IN
2299 SELECT "member_id" FROM "direct_interest_snapshot"
2300 WHERE "issue_id" = "issue_id_p"
2301 AND "event" = 'periodic'
2302 LOOP
2303 UPDATE "direct_interest_snapshot" SET
2304 "weight" = 1 +
2305 "weight_of_added_delegations_for_interest_snapshot"(
2306 "issue_id_p",
2307 "member_id_v",
2308 '{}'
2309 )
2310 WHERE "issue_id" = "issue_id_p"
2311 AND "event" = 'periodic'
2312 AND "member_id" = "member_id_v";
2313 END LOOP;
2314 INSERT INTO "direct_supporter_snapshot"
2315 ( "issue_id", "initiative_id", "event", "member_id",
2316 "informed", "satisfied" )
2317 SELECT
2318 "issue_id_p" AS "issue_id",
2319 "initiative"."id" AS "initiative_id",
2320 'periodic' AS "event",
2321 "supporter"."member_id" AS "member_id",
2322 "supporter"."draft_id" = "current_draft"."id" AS "informed",
2323 NOT EXISTS (
2324 SELECT NULL FROM "critical_opinion"
2325 WHERE "initiative_id" = "initiative"."id"
2326 AND "member_id" = "supporter"."member_id"
2327 ) AS "satisfied"
2328 FROM "initiative"
2329 JOIN "supporter"
2330 ON "supporter"."initiative_id" = "initiative"."id"
2331 JOIN "current_draft"
2332 ON "initiative"."id" = "current_draft"."initiative_id"
2333 JOIN "direct_interest_snapshot"
2334 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
2335 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
2336 AND "event" = 'periodic'
2337 WHERE "initiative"."issue_id" = "issue_id_p";
2338 RETURN;
2339 END;
2340 $$;
2342 COMMENT ON FUNCTION "create_interest_snapshot"
2343 ( "issue"."id"%TYPE )
2344 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.';
2347 CREATE FUNCTION "create_snapshot"
2348 ( "issue_id_p" "issue"."id"%TYPE )
2349 RETURNS VOID
2350 LANGUAGE 'plpgsql' VOLATILE AS $$
2351 DECLARE
2352 "initiative_id_v" "initiative"."id"%TYPE;
2353 "suggestion_id_v" "suggestion"."id"%TYPE;
2354 BEGIN
2355 PERFORM "lock_issue"("issue_id_p");
2356 PERFORM "create_population_snapshot"("issue_id_p");
2357 PERFORM "create_interest_snapshot"("issue_id_p");
2358 UPDATE "issue" SET
2359 "snapshot" = now(),
2360 "latest_snapshot_event" = 'periodic',
2361 "population" = (
2362 SELECT coalesce(sum("weight"), 0)
2363 FROM "direct_population_snapshot"
2364 WHERE "issue_id" = "issue_id_p"
2365 AND "event" = 'periodic'
2366 ),
2367 "vote_now" = (
2368 SELECT coalesce(sum("weight"), 0)
2369 FROM "direct_interest_snapshot"
2370 WHERE "issue_id" = "issue_id_p"
2371 AND "event" = 'periodic'
2372 AND "voting_requested" = TRUE
2373 ),
2374 "vote_later" = (
2375 SELECT coalesce(sum("weight"), 0)
2376 FROM "direct_interest_snapshot"
2377 WHERE "issue_id" = "issue_id_p"
2378 AND "event" = 'periodic'
2379 AND "voting_requested" = FALSE
2380 )
2381 WHERE "id" = "issue_id_p";
2382 FOR "initiative_id_v" IN
2383 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
2384 LOOP
2385 UPDATE "initiative" SET
2386 "supporter_count" = (
2387 SELECT coalesce(sum("di"."weight"), 0)
2388 FROM "direct_interest_snapshot" AS "di"
2389 JOIN "direct_supporter_snapshot" AS "ds"
2390 ON "di"."member_id" = "ds"."member_id"
2391 WHERE "di"."issue_id" = "issue_id_p"
2392 AND "di"."event" = 'periodic'
2393 AND "ds"."initiative_id" = "initiative_id_v"
2394 AND "ds"."event" = 'periodic'
2395 ),
2396 "informed_supporter_count" = (
2397 SELECT coalesce(sum("di"."weight"), 0)
2398 FROM "direct_interest_snapshot" AS "di"
2399 JOIN "direct_supporter_snapshot" AS "ds"
2400 ON "di"."member_id" = "ds"."member_id"
2401 WHERE "di"."issue_id" = "issue_id_p"
2402 AND "di"."event" = 'periodic'
2403 AND "ds"."initiative_id" = "initiative_id_v"
2404 AND "ds"."event" = 'periodic'
2405 AND "ds"."informed"
2406 ),
2407 "satisfied_supporter_count" = (
2408 SELECT coalesce(sum("di"."weight"), 0)
2409 FROM "direct_interest_snapshot" AS "di"
2410 JOIN "direct_supporter_snapshot" AS "ds"
2411 ON "di"."member_id" = "ds"."member_id"
2412 WHERE "di"."issue_id" = "issue_id_p"
2413 AND "di"."event" = 'periodic'
2414 AND "ds"."initiative_id" = "initiative_id_v"
2415 AND "ds"."event" = 'periodic'
2416 AND "ds"."satisfied"
2417 ),
2418 "satisfied_informed_supporter_count" = (
2419 SELECT coalesce(sum("di"."weight"), 0)
2420 FROM "direct_interest_snapshot" AS "di"
2421 JOIN "direct_supporter_snapshot" AS "ds"
2422 ON "di"."member_id" = "ds"."member_id"
2423 WHERE "di"."issue_id" = "issue_id_p"
2424 AND "di"."event" = 'periodic'
2425 AND "ds"."initiative_id" = "initiative_id_v"
2426 AND "ds"."event" = 'periodic'
2427 AND "ds"."informed"
2428 AND "ds"."satisfied"
2429 )
2430 WHERE "id" = "initiative_id_v";
2431 FOR "suggestion_id_v" IN
2432 SELECT "id" FROM "suggestion"
2433 WHERE "initiative_id" = "initiative_id_v"
2434 LOOP
2435 UPDATE "suggestion" SET
2436 "minus2_unfulfilled_count" = (
2437 SELECT coalesce(sum("snapshot"."weight"), 0)
2438 FROM "issue" CROSS JOIN "opinion"
2439 JOIN "direct_interest_snapshot" AS "snapshot"
2440 ON "snapshot"."issue_id" = "issue"."id"
2441 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2442 AND "snapshot"."member_id" = "opinion"."member_id"
2443 WHERE "issue"."id" = "issue_id_p"
2444 AND "opinion"."suggestion_id" = "suggestion_id_v"
2445 AND "opinion"."degree" = -2
2446 AND "opinion"."fulfilled" = FALSE
2447 ),
2448 "minus2_fulfilled_count" = (
2449 SELECT coalesce(sum("snapshot"."weight"), 0)
2450 FROM "issue" CROSS JOIN "opinion"
2451 JOIN "direct_interest_snapshot" AS "snapshot"
2452 ON "snapshot"."issue_id" = "issue"."id"
2453 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2454 AND "snapshot"."member_id" = "opinion"."member_id"
2455 WHERE "issue"."id" = "issue_id_p"
2456 AND "opinion"."suggestion_id" = "suggestion_id_v"
2457 AND "opinion"."degree" = -2
2458 AND "opinion"."fulfilled" = TRUE
2459 ),
2460 "minus1_unfulfilled_count" = (
2461 SELECT coalesce(sum("snapshot"."weight"), 0)
2462 FROM "issue" CROSS JOIN "opinion"
2463 JOIN "direct_interest_snapshot" AS "snapshot"
2464 ON "snapshot"."issue_id" = "issue"."id"
2465 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2466 AND "snapshot"."member_id" = "opinion"."member_id"
2467 WHERE "issue"."id" = "issue_id_p"
2468 AND "opinion"."suggestion_id" = "suggestion_id_v"
2469 AND "opinion"."degree" = -1
2470 AND "opinion"."fulfilled" = FALSE
2471 ),
2472 "minus1_fulfilled_count" = (
2473 SELECT coalesce(sum("snapshot"."weight"), 0)
2474 FROM "issue" CROSS JOIN "opinion"
2475 JOIN "direct_interest_snapshot" AS "snapshot"
2476 ON "snapshot"."issue_id" = "issue"."id"
2477 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2478 AND "snapshot"."member_id" = "opinion"."member_id"
2479 WHERE "issue"."id" = "issue_id_p"
2480 AND "opinion"."suggestion_id" = "suggestion_id_v"
2481 AND "opinion"."degree" = -1
2482 AND "opinion"."fulfilled" = TRUE
2483 ),
2484 "plus1_unfulfilled_count" = (
2485 SELECT coalesce(sum("snapshot"."weight"), 0)
2486 FROM "issue" CROSS JOIN "opinion"
2487 JOIN "direct_interest_snapshot" AS "snapshot"
2488 ON "snapshot"."issue_id" = "issue"."id"
2489 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2490 AND "snapshot"."member_id" = "opinion"."member_id"
2491 WHERE "issue"."id" = "issue_id_p"
2492 AND "opinion"."suggestion_id" = "suggestion_id_v"
2493 AND "opinion"."degree" = 1
2494 AND "opinion"."fulfilled" = FALSE
2495 ),
2496 "plus1_fulfilled_count" = (
2497 SELECT coalesce(sum("snapshot"."weight"), 0)
2498 FROM "issue" CROSS JOIN "opinion"
2499 JOIN "direct_interest_snapshot" AS "snapshot"
2500 ON "snapshot"."issue_id" = "issue"."id"
2501 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2502 AND "snapshot"."member_id" = "opinion"."member_id"
2503 WHERE "issue"."id" = "issue_id_p"
2504 AND "opinion"."suggestion_id" = "suggestion_id_v"
2505 AND "opinion"."degree" = 1
2506 AND "opinion"."fulfilled" = TRUE
2507 ),
2508 "plus2_unfulfilled_count" = (
2509 SELECT coalesce(sum("snapshot"."weight"), 0)
2510 FROM "issue" CROSS JOIN "opinion"
2511 JOIN "direct_interest_snapshot" AS "snapshot"
2512 ON "snapshot"."issue_id" = "issue"."id"
2513 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2514 AND "snapshot"."member_id" = "opinion"."member_id"
2515 WHERE "issue"."id" = "issue_id_p"
2516 AND "opinion"."suggestion_id" = "suggestion_id_v"
2517 AND "opinion"."degree" = 2
2518 AND "opinion"."fulfilled" = FALSE
2519 ),
2520 "plus2_fulfilled_count" = (
2521 SELECT coalesce(sum("snapshot"."weight"), 0)
2522 FROM "issue" CROSS JOIN "opinion"
2523 JOIN "direct_interest_snapshot" AS "snapshot"
2524 ON "snapshot"."issue_id" = "issue"."id"
2525 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2526 AND "snapshot"."member_id" = "opinion"."member_id"
2527 WHERE "issue"."id" = "issue_id_p"
2528 AND "opinion"."suggestion_id" = "suggestion_id_v"
2529 AND "opinion"."degree" = 2
2530 AND "opinion"."fulfilled" = TRUE
2531 )
2532 WHERE "suggestion"."id" = "suggestion_id_v";
2533 END LOOP;
2534 END LOOP;
2535 RETURN;
2536 END;
2537 $$;
2539 COMMENT ON FUNCTION "create_snapshot"
2540 ( "issue"."id"%TYPE )
2541 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.';
2544 CREATE FUNCTION "set_snapshot_event"
2545 ( "issue_id_p" "issue"."id"%TYPE,
2546 "event_p" "snapshot_event" )
2547 RETURNS VOID
2548 LANGUAGE 'plpgsql' VOLATILE AS $$
2549 DECLARE
2550 "event_v" "issue"."latest_snapshot_event"%TYPE;
2551 BEGIN
2552 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
2553 WHERE "id" = "issue_id_p" FOR UPDATE;
2554 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
2555 WHERE "id" = "issue_id_p";
2556 UPDATE "direct_population_snapshot" SET "event" = "event_p"
2557 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2558 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
2559 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2560 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
2561 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2562 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
2563 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2564 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
2565 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2566 RETURN;
2567 END;
2568 $$;
2570 COMMENT ON FUNCTION "set_snapshot_event"
2571 ( "issue"."id"%TYPE,
2572 "snapshot_event" )
2573 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
2577 ---------------------
2578 -- Freezing issues --
2579 ---------------------
2581 CREATE FUNCTION "freeze_after_snapshot"
2582 ( "issue_id_p" "issue"."id"%TYPE )
2583 RETURNS VOID
2584 LANGUAGE 'plpgsql' VOLATILE AS $$
2585 DECLARE
2586 "issue_row" "issue"%ROWTYPE;
2587 "policy_row" "policy"%ROWTYPE;
2588 "initiative_row" "initiative"%ROWTYPE;
2589 BEGIN
2590 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2591 SELECT * INTO "policy_row"
2592 FROM "policy" WHERE "id" = "issue_row"."policy_id";
2593 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
2594 UPDATE "issue" SET
2595 "accepted" = coalesce("accepted", now()),
2596 "half_frozen" = coalesce("half_frozen", now()),
2597 "fully_frozen" = now()
2598 WHERE "id" = "issue_id_p";
2599 FOR "initiative_row" IN
2600 SELECT * FROM "initiative"
2601 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
2602 LOOP
2603 IF
2604 "initiative_row"."satisfied_supporter_count" > 0 AND
2605 "initiative_row"."satisfied_supporter_count" *
2606 "policy_row"."initiative_quorum_den" >=
2607 "issue_row"."population" * "policy_row"."initiative_quorum_num"
2608 THEN
2609 UPDATE "initiative" SET "admitted" = TRUE
2610 WHERE "id" = "initiative_row"."id";
2611 ELSE
2612 UPDATE "initiative" SET "admitted" = FALSE
2613 WHERE "id" = "initiative_row"."id";
2614 END IF;
2615 END LOOP;
2616 IF NOT EXISTS (
2617 SELECT NULL FROM "initiative"
2618 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
2619 ) THEN
2620 PERFORM "close_voting"("issue_id_p");
2621 END IF;
2622 RETURN;
2623 END;
2624 $$;
2626 COMMENT ON FUNCTION "freeze_after_snapshot"
2627 ( "issue"."id"%TYPE )
2628 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
2631 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
2632 RETURNS VOID
2633 LANGUAGE 'plpgsql' VOLATILE AS $$
2634 DECLARE
2635 "issue_row" "issue"%ROWTYPE;
2636 BEGIN
2637 PERFORM "create_snapshot"("issue_id_p");
2638 PERFORM "freeze_after_snapshot"("issue_id_p");
2639 RETURN;
2640 END;
2641 $$;
2643 COMMENT ON FUNCTION "manual_freeze"
2644 ( "issue"."id"%TYPE )
2645 IS 'Freeze an issue manually (fully) and start voting';
2649 -----------------------
2650 -- Counting of votes --
2651 -----------------------
2654 CREATE FUNCTION "weight_of_added_vote_delegations"
2655 ( "issue_id_p" "issue"."id"%TYPE,
2656 "member_id_p" "member"."id"%TYPE,
2657 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
2658 RETURNS "direct_voter"."weight"%TYPE
2659 LANGUAGE 'plpgsql' VOLATILE AS $$
2660 DECLARE
2661 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2662 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
2663 "weight_v" INT4;
2664 "sub_weight_v" INT4;
2665 BEGIN
2666 "weight_v" := 0;
2667 FOR "issue_delegation_row" IN
2668 SELECT * FROM "issue_delegation"
2669 WHERE "trustee_id" = "member_id_p"
2670 AND "issue_id" = "issue_id_p"
2671 LOOP
2672 IF NOT EXISTS (
2673 SELECT NULL FROM "direct_voter"
2674 WHERE "member_id" = "issue_delegation_row"."truster_id"
2675 AND "issue_id" = "issue_id_p"
2676 ) AND NOT EXISTS (
2677 SELECT NULL FROM "delegating_voter"
2678 WHERE "member_id" = "issue_delegation_row"."truster_id"
2679 AND "issue_id" = "issue_id_p"
2680 ) THEN
2681 "delegate_member_ids_v" :=
2682 "member_id_p" || "delegate_member_ids_p";
2683 INSERT INTO "delegating_voter" (
2684 "issue_id",
2685 "member_id",
2686 "scope",
2687 "delegate_member_ids"
2688 ) VALUES (
2689 "issue_id_p",
2690 "issue_delegation_row"."truster_id",
2691 "issue_delegation_row"."scope",
2692 "delegate_member_ids_v"
2693 );
2694 "sub_weight_v" := 1 +
2695 "weight_of_added_vote_delegations"(
2696 "issue_id_p",
2697 "issue_delegation_row"."truster_id",
2698 "delegate_member_ids_v"
2699 );
2700 UPDATE "delegating_voter"
2701 SET "weight" = "sub_weight_v"
2702 WHERE "issue_id" = "issue_id_p"
2703 AND "member_id" = "issue_delegation_row"."truster_id";
2704 "weight_v" := "weight_v" + "sub_weight_v";
2705 END IF;
2706 END LOOP;
2707 RETURN "weight_v";
2708 END;
2709 $$;
2711 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
2712 ( "issue"."id"%TYPE,
2713 "member"."id"%TYPE,
2714 "delegating_voter"."delegate_member_ids"%TYPE )
2715 IS 'Helper function for "add_vote_delegations" function';
2718 CREATE FUNCTION "add_vote_delegations"
2719 ( "issue_id_p" "issue"."id"%TYPE )
2720 RETURNS VOID
2721 LANGUAGE 'plpgsql' VOLATILE AS $$
2722 DECLARE
2723 "member_id_v" "member"."id"%TYPE;
2724 BEGIN
2725 FOR "member_id_v" IN
2726 SELECT "member_id" FROM "direct_voter"
2727 WHERE "issue_id" = "issue_id_p"
2728 LOOP
2729 UPDATE "direct_voter" SET
2730 "weight" = "weight" + "weight_of_added_vote_delegations"(
2731 "issue_id_p",
2732 "member_id_v",
2733 '{}'
2734 )
2735 WHERE "member_id" = "member_id_v"
2736 AND "issue_id" = "issue_id_p";
2737 END LOOP;
2738 RETURN;
2739 END;
2740 $$;
2742 COMMENT ON FUNCTION "add_vote_delegations"
2743 ( "issue_id_p" "issue"."id"%TYPE )
2744 IS 'Helper function for "close_voting" function';
2747 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
2748 RETURNS VOID
2749 LANGUAGE 'plpgsql' VOLATILE AS $$
2750 DECLARE
2751 "issue_row" "issue"%ROWTYPE;
2752 "member_id_v" "member"."id"%TYPE;
2753 BEGIN
2754 PERFORM "lock_issue"("issue_id_p");
2755 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2756 DELETE FROM "delegating_voter"
2757 WHERE "issue_id" = "issue_id_p";
2758 DELETE FROM "direct_voter"
2759 WHERE "issue_id" = "issue_id_p"
2760 AND "autoreject" = TRUE;
2761 DELETE FROM "direct_voter" USING "member"
2762 WHERE "direct_voter"."member_id" = "member"."id"
2763 AND "direct_voter"."issue_id" = "issue_id_p"
2764 AND "member"."active" = FALSE;
2765 UPDATE "direct_voter" SET "weight" = 1
2766 WHERE "issue_id" = "issue_id_p";
2767 PERFORM "add_vote_delegations"("issue_id_p");
2768 FOR "member_id_v" IN
2769 SELECT "interest"."member_id"
2770 FROM "interest"
2771 JOIN "member"
2772 ON "interest"."member_id" = "member"."id"
2773 LEFT JOIN "direct_voter"
2774 ON "interest"."member_id" = "direct_voter"."member_id"
2775 AND "interest"."issue_id" = "direct_voter"."issue_id"
2776 LEFT JOIN "delegating_voter"
2777 ON "interest"."member_id" = "delegating_voter"."member_id"
2778 AND "interest"."issue_id" = "delegating_voter"."issue_id"
2779 WHERE "interest"."issue_id" = "issue_id_p"
2780 AND "interest"."autoreject" = TRUE
2781 AND "member"."active"
2782 AND "direct_voter"."member_id" ISNULL
2783 AND "delegating_voter"."member_id" ISNULL
2784 UNION SELECT "membership"."member_id"
2785 FROM "membership"
2786 JOIN "member"
2787 ON "membership"."member_id" = "member"."id"
2788 LEFT JOIN "interest"
2789 ON "membership"."member_id" = "interest"."member_id"
2790 AND "interest"."issue_id" = "issue_id_p"
2791 LEFT JOIN "direct_voter"
2792 ON "membership"."member_id" = "direct_voter"."member_id"
2793 AND "direct_voter"."issue_id" = "issue_id_p"
2794 LEFT JOIN "delegating_voter"
2795 ON "membership"."member_id" = "delegating_voter"."member_id"
2796 AND "delegating_voter"."issue_id" = "issue_id_p"
2797 WHERE "membership"."area_id" = "issue_row"."area_id"
2798 AND "membership"."autoreject" = TRUE
2799 AND "member"."active"
2800 AND "interest"."autoreject" ISNULL
2801 AND "direct_voter"."member_id" ISNULL
2802 AND "delegating_voter"."member_id" ISNULL
2803 LOOP
2804 INSERT INTO "direct_voter"
2805 ("member_id", "issue_id", "weight", "autoreject") VALUES
2806 ("member_id_v", "issue_id_p", 1, TRUE);
2807 INSERT INTO "vote" (
2808 "member_id",
2809 "issue_id",
2810 "initiative_id",
2811 "grade"
2812 ) SELECT
2813 "member_id_v" AS "member_id",
2814 "issue_id_p" AS "issue_id",
2815 "id" AS "initiative_id",
2816 -1 AS "grade"
2817 FROM "initiative" WHERE "issue_id" = "issue_id_p";
2818 END LOOP;
2819 PERFORM "add_vote_delegations"("issue_id_p");
2820 UPDATE "issue" SET
2821 "closed" = now(),
2822 "voter_count" = (
2823 SELECT coalesce(sum("weight"), 0)
2824 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
2825 )
2826 WHERE "id" = "issue_id_p";
2827 UPDATE "initiative" SET
2828 "positive_votes" = "vote_counts"."positive_votes",
2829 "negative_votes" = "vote_counts"."negative_votes",
2830 "agreed" = CASE WHEN "majority_strict" THEN
2831 "vote_counts"."positive_votes" * "majority_den" >
2832 "majority_num" *
2833 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
2834 ELSE
2835 "vote_counts"."positive_votes" * "majority_den" >=
2836 "majority_num" *
2837 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
2838 END
2839 FROM
2840 ( SELECT
2841 "initiative"."id" AS "initiative_id",
2842 coalesce(
2843 sum(
2844 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
2845 ),
2846 0
2847 ) AS "positive_votes",
2848 coalesce(
2849 sum(
2850 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
2851 ),
2852 0
2853 ) AS "negative_votes"
2854 FROM "initiative"
2855 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
2856 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
2857 LEFT JOIN "direct_voter"
2858 ON "direct_voter"."issue_id" = "initiative"."issue_id"
2859 LEFT JOIN "vote"
2860 ON "vote"."initiative_id" = "initiative"."id"
2861 AND "vote"."member_id" = "direct_voter"."member_id"
2862 WHERE "initiative"."issue_id" = "issue_id_p"
2863 AND "initiative"."admitted" -- NOTE: NULL case is handled too
2864 GROUP BY "initiative"."id"
2865 ) AS "vote_counts",
2866 "issue",
2867 "policy"
2868 WHERE "vote_counts"."initiative_id" = "initiative"."id"
2869 AND "issue"."id" = "initiative"."issue_id"
2870 AND "policy"."id" = "issue"."policy_id";
2871 -- NOTE: "closed" column of issue must be set at this point
2872 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
2873 INSERT INTO "battle" (
2874 "issue_id",
2875 "winning_initiative_id", "losing_initiative_id",
2876 "count"
2877 ) SELECT
2878 "issue_id",
2879 "winning_initiative_id", "losing_initiative_id",
2880 "count"
2881 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
2882 END;
2883 $$;
2885 COMMENT ON FUNCTION "close_voting"
2886 ( "issue"."id"%TYPE )
2887 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.';
2890 CREATE FUNCTION "defeat_strength"
2891 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
2892 RETURNS INT8
2893 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2894 BEGIN
2895 IF "positive_votes_p" > "negative_votes_p" THEN
2896 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
2897 ELSIF "positive_votes_p" = "negative_votes_p" THEN
2898 RETURN 0;
2899 ELSE
2900 RETURN -1;
2901 END IF;
2902 END;
2903 $$;
2905 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';
2908 CREATE FUNCTION "array_init_string"("dim_p" INTEGER)
2909 RETURNS TEXT
2910 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2911 DECLARE
2912 "i" INTEGER;
2913 "ary_text_v" TEXT;
2914 BEGIN
2915 IF "dim_p" >= 1 THEN
2916 "ary_text_v" := '{NULL';
2917 "i" := "dim_p";
2918 LOOP
2919 "i" := "i" - 1;
2920 EXIT WHEN "i" = 0;
2921 "ary_text_v" := "ary_text_v" || ',NULL';
2922 END LOOP;
2923 "ary_text_v" := "ary_text_v" || '}';
2924 RETURN "ary_text_v";
2925 ELSE
2926 RAISE EXCEPTION 'Dimension needs to be at least 1.';
2927 END IF;
2928 END;
2929 $$;
2931 COMMENT ON FUNCTION "array_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
2934 CREATE FUNCTION "square_matrix_init_string"("dim_p" INTEGER)
2935 RETURNS TEXT
2936 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2937 DECLARE
2938 "i" INTEGER;
2939 "row_text_v" TEXT;
2940 "ary_text_v" TEXT;
2941 BEGIN
2942 IF "dim_p" >= 1 THEN
2943 "row_text_v" := '{NULL';
2944 "i" := "dim_p";
2945 LOOP
2946 "i" := "i" - 1;
2947 EXIT WHEN "i" = 0;
2948 "row_text_v" := "row_text_v" || ',NULL';
2949 END LOOP;
2950 "row_text_v" := "row_text_v" || '}';
2951 "ary_text_v" := '{' || "row_text_v";
2952 "i" := "dim_p";
2953 LOOP
2954 "i" := "i" - 1;
2955 EXIT WHEN "i" = 0;
2956 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
2957 END LOOP;
2958 "ary_text_v" := "ary_text_v" || '}';
2959 RETURN "ary_text_v";
2960 ELSE
2961 RAISE EXCEPTION 'Dimension needs to be at least 1.';
2962 END IF;
2963 END;
2964 $$;
2966 COMMENT ON FUNCTION "square_matrix_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
2969 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
2970 RETURNS VOID
2971 LANGUAGE 'plpgsql' VOLATILE AS $$
2972 DECLARE
2973 "dimension_v" INTEGER;
2974 "vote_matrix" INT4[][]; -- absolute votes
2975 "matrix" INT8[][]; -- defeat strength / best paths
2976 "i" INTEGER;
2977 "j" INTEGER;
2978 "k" INTEGER;
2979 "battle_row" "battle"%ROWTYPE;
2980 "rank_ary" INT4[];
2981 "rank_v" INT4;
2982 "done_v" INTEGER;
2983 "winners_ary" INTEGER[];
2984 "initiative_id_v" "initiative"."id"%TYPE;
2985 BEGIN
2986 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2987 SELECT count(1) INTO "dimension_v" FROM "initiative"
2988 WHERE "issue_id" = "issue_id_p" AND "agreed";
2989 IF "dimension_v" = 1 THEN
2990 UPDATE "initiative" SET "rank" = 1
2991 WHERE "issue_id" = "issue_id_p" AND "agreed";
2992 ELSIF "dimension_v" > 1 THEN
2993 -- Create "vote_matrix" with absolute number of votes in pairwise
2994 -- comparison:
2995 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2996 "i" := 1;
2997 "j" := 2;
2998 FOR "battle_row" IN
2999 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
3000 ORDER BY "winning_initiative_id", "losing_initiative_id"
3001 LOOP
3002 "vote_matrix"["i"]["j"] := "battle_row"."count";
3003 IF "j" = "dimension_v" THEN
3004 "i" := "i" + 1;
3005 "j" := 1;
3006 ELSE
3007 "j" := "j" + 1;
3008 IF "j" = "i" THEN
3009 "j" := "j" + 1;
3010 END IF;
3011 END IF;
3012 END LOOP;
3013 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
3014 RAISE EXCEPTION 'Wrong battle count (should not happen)';
3015 END IF;
3016 -- Store defeat strengths in "matrix" using "defeat_strength"
3017 -- function:
3018 "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
3019 "i" := 1;
3020 LOOP
3021 "j" := 1;
3022 LOOP
3023 IF "i" != "j" THEN
3024 "matrix"["i"]["j"] := "defeat_strength"(
3025 "vote_matrix"["i"]["j"],
3026 "vote_matrix"["j"]["i"]
3027 );
3028 END IF;
3029 EXIT WHEN "j" = "dimension_v";
3030 "j" := "j" + 1;
3031 END LOOP;
3032 EXIT WHEN "i" = "dimension_v";
3033 "i" := "i" + 1;
3034 END LOOP;
3035 -- Find best paths:
3036 "i" := 1;
3037 LOOP
3038 "j" := 1;
3039 LOOP
3040 IF "i" != "j" THEN
3041 "k" := 1;
3042 LOOP
3043 IF "i" != "k" AND "j" != "k" THEN
3044 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
3045 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
3046 "matrix"["j"]["k"] := "matrix"["j"]["i"];
3047 END IF;
3048 ELSE
3049 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
3050 "matrix"["j"]["k"] := "matrix"["i"]["k"];
3051 END IF;
3052 END IF;
3053 END IF;
3054 EXIT WHEN "k" = "dimension_v";
3055 "k" := "k" + 1;
3056 END LOOP;
3057 END IF;
3058 EXIT WHEN "j" = "dimension_v";
3059 "j" := "j" + 1;
3060 END LOOP;
3061 EXIT WHEN "i" = "dimension_v";
3062 "i" := "i" + 1;
3063 END LOOP;
3064 -- Determine order of winners:
3065 "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
3066 "rank_v" := 1;
3067 "done_v" := 0;
3068 LOOP
3069 "winners_ary" := '{}';
3070 "i" := 1;
3071 LOOP
3072 IF "rank_ary"["i"] ISNULL THEN
3073 "j" := 1;
3074 LOOP
3075 IF
3076 "i" != "j" AND
3077 "rank_ary"["j"] ISNULL AND
3078 "matrix"["j"]["i"] > "matrix"["i"]["j"]
3079 THEN
3080 -- someone else is better
3081 EXIT;
3082 END IF;
3083 IF "j" = "dimension_v" THEN
3084 -- noone is better
3085 "winners_ary" := "winners_ary" || "i";
3086 EXIT;
3087 END IF;
3088 "j" := "j" + 1;
3089 END LOOP;
3090 END IF;
3091 EXIT WHEN "i" = "dimension_v";
3092 "i" := "i" + 1;
3093 END LOOP;
3094 "i" := 1;
3095 LOOP
3096 "rank_ary"["winners_ary"["i"]] := "rank_v";
3097 "done_v" := "done_v" + 1;
3098 EXIT WHEN "i" = array_upper("winners_ary", 1);
3099 "i" := "i" + 1;
3100 END LOOP;
3101 EXIT WHEN "done_v" = "dimension_v";
3102 "rank_v" := "rank_v" + 1;
3103 END LOOP;
3104 -- write preliminary ranks:
3105 "i" := 1;
3106 FOR "initiative_id_v" IN
3107 SELECT "id" FROM "initiative"
3108 WHERE "issue_id" = "issue_id_p" AND "agreed"
3109 ORDER BY "id"
3110 LOOP
3111 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
3112 WHERE "id" = "initiative_id_v";
3113 "i" := "i" + 1;
3114 END LOOP;
3115 IF "i" != "dimension_v" + 1 THEN
3116 RAISE EXCEPTION 'Wrong winner count (should not happen)';
3117 END IF;
3118 -- straighten ranks (start counting with 1, no equal ranks):
3119 "rank_v" := 1;
3120 FOR "initiative_id_v" IN
3121 SELECT "id" FROM "initiative"
3122 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
3123 ORDER BY
3124 "rank",
3125 "vote_ratio"("positive_votes", "negative_votes") DESC,
3126 "id"
3127 LOOP
3128 UPDATE "initiative" SET "rank" = "rank_v"
3129 WHERE "id" = "initiative_id_v";
3130 "rank_v" := "rank_v" + 1;
3131 END LOOP;
3132 END IF;
3133 -- mark issue as finished
3134 UPDATE "issue" SET "ranks_available" = TRUE
3135 WHERE "id" = "issue_id_p";
3136 RETURN;
3137 END;
3138 $$;
3140 COMMENT ON FUNCTION "calculate_ranks"
3141 ( "issue"."id"%TYPE )
3142 IS 'Determine ranking (Votes have to be counted first)';
3146 -----------------------------
3147 -- Automatic state changes --
3148 -----------------------------
3151 CREATE FUNCTION "check_issue"
3152 ( "issue_id_p" "issue"."id"%TYPE )
3153 RETURNS VOID
3154 LANGUAGE 'plpgsql' VOLATILE AS $$
3155 DECLARE
3156 "issue_row" "issue"%ROWTYPE;
3157 "policy_row" "policy"%ROWTYPE;
3158 "voting_requested_v" BOOLEAN;
3159 BEGIN
3160 PERFORM "lock_issue"("issue_id_p");
3161 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3162 -- only process open issues:
3163 IF "issue_row"."closed" ISNULL THEN
3164 SELECT * INTO "policy_row" FROM "policy"
3165 WHERE "id" = "issue_row"."policy_id";
3166 -- create a snapshot, unless issue is already fully frozen:
3167 IF "issue_row"."fully_frozen" ISNULL THEN
3168 PERFORM "create_snapshot"("issue_id_p");
3169 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3170 END IF;
3171 -- eventually close or accept issues, which have not been accepted:
3172 IF "issue_row"."accepted" ISNULL THEN
3173 IF EXISTS (
3174 SELECT NULL FROM "initiative"
3175 WHERE "issue_id" = "issue_id_p"
3176 AND "supporter_count" > 0
3177 AND "supporter_count" * "policy_row"."issue_quorum_den"
3178 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
3179 ) THEN
3180 -- accept issues, if supporter count is high enough
3181 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3182 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later
3183 UPDATE "issue" SET "accepted" = "issue_row"."accepted"
3184 WHERE "id" = "issue_row"."id";
3185 ELSIF
3186 now() >= "issue_row"."created" + "issue_row"."admission_time"
3187 THEN
3188 -- close issues, if admission time has expired
3189 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3190 UPDATE "issue" SET "closed" = now()
3191 WHERE "id" = "issue_row"."id";
3192 END IF;
3193 END IF;
3194 -- eventually half freeze issues:
3195 IF
3196 -- NOTE: issue can't be closed at this point, if it has been accepted
3197 "issue_row"."accepted" NOTNULL AND
3198 "issue_row"."half_frozen" ISNULL
3199 THEN
3200 SELECT
3201 CASE
3202 WHEN "vote_now" * 2 > "issue_row"."population" THEN
3203 TRUE
3204 WHEN "vote_later" * 2 > "issue_row"."population" THEN
3205 FALSE
3206 ELSE NULL
3207 END
3208 INTO "voting_requested_v"
3209 FROM "issue" WHERE "id" = "issue_id_p";
3210 IF
3211 "voting_requested_v" OR (
3212 "voting_requested_v" ISNULL AND
3213 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
3214 )
3215 THEN
3216 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
3217 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later
3218 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
3219 WHERE "id" = "issue_row"."id";
3220 END IF;
3221 END IF;
3222 -- close issues after some time, if all initiatives have been revoked:
3223 IF
3224 "issue_row"."closed" ISNULL AND
3225 NOT EXISTS (
3226 -- all initiatives are revoked
3227 SELECT NULL FROM "initiative"
3228 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3229 ) AND (
3230 NOT EXISTS (
3231 -- and no initiatives have been revoked lately
3232 SELECT NULL FROM "initiative"
3233 WHERE "issue_id" = "issue_id_p"
3234 AND now() < "revoked" + "issue_row"."verification_time"
3235 ) OR (
3236 -- or verification time has elapsed
3237 "issue_row"."half_frozen" NOTNULL AND
3238 "issue_row"."fully_frozen" ISNULL AND
3239 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3240 )
3241 )
3242 THEN
3243 "issue_row"."closed" = now(); -- NOTE: "issue_row" used later
3244 UPDATE "issue" SET "closed" = "issue_row"."closed"
3245 WHERE "id" = "issue_row"."id";
3246 END IF;
3247 -- fully freeze issue after verification time:
3248 IF
3249 "issue_row"."half_frozen" NOTNULL AND
3250 "issue_row"."fully_frozen" ISNULL AND
3251 "issue_row"."closed" ISNULL AND
3252 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3253 THEN
3254 PERFORM "freeze_after_snapshot"("issue_id_p");
3255 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
3256 END IF;
3257 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3258 -- close issue by calling close_voting(...) after voting time:
3259 IF
3260 "issue_row"."closed" ISNULL AND
3261 "issue_row"."fully_frozen" NOTNULL AND
3262 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
3263 THEN
3264 PERFORM "close_voting"("issue_id_p");
3265 END IF;
3266 END IF;
3267 RETURN;
3268 END;
3269 $$;
3271 COMMENT ON FUNCTION "check_issue"
3272 ( "issue"."id"%TYPE )
3273 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.';
3276 CREATE FUNCTION "check_everything"()
3277 RETURNS VOID
3278 LANGUAGE 'plpgsql' VOLATILE AS $$
3279 DECLARE
3280 "issue_id_v" "issue"."id"%TYPE;
3281 BEGIN
3282 DELETE FROM "expired_session";
3283 PERFORM "calculate_member_counts"();
3284 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
3285 PERFORM "check_issue"("issue_id_v");
3286 END LOOP;
3287 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
3288 PERFORM "calculate_ranks"("issue_id_v");
3289 END LOOP;
3290 RETURN;
3291 END;
3292 $$;
3294 COMMENT ON FUNCTION "check_everything"() IS 'Perform "check_issue" for every open issue, and if possible, automatically calculate ranks. Use this function only for development and debugging purposes, as long transactions with exclusive locking may result.';
3298 ----------------------
3299 -- Deletion of data --
3300 ----------------------
3303 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
3304 RETURNS VOID
3305 LANGUAGE 'plpgsql' VOLATILE AS $$
3306 DECLARE
3307 "issue_row" "issue"%ROWTYPE;
3308 BEGIN
3309 SELECT * INTO "issue_row"
3310 FROM "issue" WHERE "id" = "issue_id_p"
3311 FOR UPDATE;
3312 IF "issue_row"."cleaned" ISNULL THEN
3313 UPDATE "issue" SET
3314 "closed" = NULL,
3315 "ranks_available" = FALSE
3316 WHERE "id" = "issue_id_p";
3317 DELETE FROM "delegating_voter"
3318 WHERE "issue_id" = "issue_id_p";
3319 DELETE FROM "direct_voter"
3320 WHERE "issue_id" = "issue_id_p";
3321 DELETE FROM "delegating_interest_snapshot"
3322 WHERE "issue_id" = "issue_id_p";
3323 DELETE FROM "direct_interest_snapshot"
3324 WHERE "issue_id" = "issue_id_p";
3325 DELETE FROM "delegating_population_snapshot"
3326 WHERE "issue_id" = "issue_id_p";
3327 DELETE FROM "direct_population_snapshot"
3328 WHERE "issue_id" = "issue_id_p";
3329 DELETE FROM "ignored_issue"
3330 WHERE "issue_id" = "issue_id_p";
3331 DELETE FROM "delegation"
3332 WHERE "issue_id" = "issue_id_p";
3333 DELETE FROM "supporter"
3334 WHERE "issue_id" = "issue_id_p";
3335 UPDATE "issue" SET
3336 "closed" = "issue_row"."closed",
3337 "ranks_available" = "issue_row"."ranks_available",
3338 "cleaned" = now()
3339 WHERE "id" = "issue_id_p";
3340 END IF;
3341 RETURN;
3342 END;
3343 $$;
3345 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
3348 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
3349 RETURNS VOID
3350 LANGUAGE 'plpgsql' VOLATILE AS $$
3351 BEGIN
3352 UPDATE "member" SET
3353 "last_login" = NULL,
3354 "login" = NULL,
3355 "password" = NULL,
3356 "locked" = TRUE,
3357 "active" = FALSE,
3358 "notify_email" = NULL,
3359 "notify_email_unconfirmed" = NULL,
3360 "notify_email_secret" = NULL,
3361 "notify_email_secret_expiry" = NULL,
3362 "notify_email_lock_expiry" = NULL,
3363 "password_reset_secret" = NULL,
3364 "password_reset_secret_expiry" = NULL,
3365 "organizational_unit" = NULL,
3366 "internal_posts" = NULL,
3367 "realname" = NULL,
3368 "birthday" = NULL,
3369 "address" = NULL,
3370 "email" = NULL,
3371 "xmpp_address" = NULL,
3372 "website" = NULL,
3373 "phone" = NULL,
3374 "mobile_phone" = NULL,
3375 "profession" = NULL,
3376 "external_memberships" = NULL,
3377 "external_posts" = NULL,
3378 "statement" = NULL
3379 WHERE "id" = "member_id_p";
3380 -- "text_search_data" is updated by triggers
3381 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
3382 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
3383 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
3384 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
3385 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
3386 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
3387 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
3388 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
3389 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
3390 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
3391 DELETE FROM "ignored_issue" WHERE "member_id" = "member_id_p";
3392 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
3393 DELETE FROM "direct_voter" USING "issue"
3394 WHERE "direct_voter"."issue_id" = "issue"."id"
3395 AND "issue"."closed" ISNULL
3396 AND "member_id" = "member_id_p";
3397 RETURN;
3398 END;
3399 $$;
3401 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)';
3404 CREATE FUNCTION "delete_private_data"()
3405 RETURNS VOID
3406 LANGUAGE 'plpgsql' VOLATILE AS $$
3407 BEGIN
3408 UPDATE "member" SET
3409 "last_login" = NULL,
3410 "login" = NULL,
3411 "password" = NULL,
3412 "notify_email" = NULL,
3413 "notify_email_unconfirmed" = NULL,
3414 "notify_email_secret" = NULL,
3415 "notify_email_secret_expiry" = NULL,
3416 "notify_email_lock_expiry" = NULL,
3417 "password_reset_secret" = NULL,
3418 "password_reset_secret_expiry" = NULL,
3419 "organizational_unit" = NULL,
3420 "internal_posts" = NULL,
3421 "realname" = NULL,
3422 "birthday" = NULL,
3423 "address" = NULL,
3424 "email" = NULL,
3425 "xmpp_address" = NULL,
3426 "website" = NULL,
3427 "phone" = NULL,
3428 "mobile_phone" = NULL,
3429 "profession" = NULL,
3430 "external_memberships" = NULL,
3431 "external_posts" = NULL,
3432 "statement" = NULL;
3433 -- "text_search_data" is updated by triggers
3434 DELETE FROM "invite_code";
3435 DELETE FROM "setting";
3436 DELETE FROM "setting_map";
3437 DELETE FROM "member_relation_setting";
3438 DELETE FROM "member_image";
3439 DELETE FROM "contact";
3440 DELETE FROM "session";
3441 DELETE FROM "area_setting";
3442 DELETE FROM "issue_setting";
3443 DELETE FROM "initiative_setting";
3444 DELETE FROM "suggestion_setting";
3445 DELETE FROM "ignored_issue";
3446 DELETE FROM "direct_voter" USING "issue"
3447 WHERE "direct_voter"."issue_id" = "issue"."id"
3448 AND "issue"."closed" ISNULL;
3449 RETURN;
3450 END;
3451 $$;
3453 COMMENT ON FUNCTION "delete_private_data"() IS '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.';
3457 COMMIT;