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