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