liquid_feedback_core
view core.sql @ 85:1a412ec5e14e
Added comment on "supporter"."auto_support" also in update file
| author | jbe |
|---|---|
| date | Sun Oct 17 01:44:13 2010 +0200 (2010-10-17) |
| parents | b0866d51b754 |
| children | 3a86196ed0bf |
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.2.9', 1, 2, 9))
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; 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 in case of not explicitly taking part in the voting procedure; If there exists an "interest" entry, the interest entry has precedence';
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 NOT NULL,
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 "initiator" (
600 PRIMARY KEY ("initiative_id", "member_id"),
601 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
602 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
603 "accepted" BOOLEAN );
604 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
606 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.';
608 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.';
611 CREATE TABLE "supporter" (
612 "issue_id" INT4 NOT NULL,
613 PRIMARY KEY ("initiative_id", "member_id"),
614 "initiative_id" INT4,
615 "member_id" INT4,
616 "draft_id" INT8 NOT NULL,
617 "auto_support" BOOLEAN NOT NULL DEFAULT FALSE,
618 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
619 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
620 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
622 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.';
624 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
625 COMMENT ON COLUMN "supporter"."auto_support" IS 'Supporting member does not want to confirm new drafts of the initiative';
628 CREATE TABLE "opinion" (
629 "initiative_id" INT4 NOT NULL,
630 PRIMARY KEY ("suggestion_id", "member_id"),
631 "suggestion_id" INT8,
632 "member_id" INT4,
633 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
634 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
635 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
636 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
637 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
639 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.';
641 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
644 CREATE TYPE "delegation_scope" AS ENUM ('global', 'area', 'issue');
646 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''global'', ''area'', or ''issue'' (order is relevant)';
649 CREATE TABLE "delegation" (
650 "id" SERIAL8 PRIMARY KEY,
651 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
652 "trustee_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
653 "scope" "delegation_scope" NOT NULL,
654 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
655 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
656 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
657 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
658 ("scope" = 'global' AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
659 ("scope" = 'area' AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
660 ("scope" = 'issue' AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
661 UNIQUE ("area_id", "truster_id"),
662 UNIQUE ("issue_id", "truster_id") );
663 CREATE UNIQUE INDEX "delegation_global_truster_id_unique_idx"
664 ON "delegation" ("truster_id") WHERE "scope" = 'global';
665 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
666 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
668 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
670 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
671 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
674 CREATE TABLE "direct_population_snapshot" (
675 PRIMARY KEY ("issue_id", "event", "member_id"),
676 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
677 "event" "snapshot_event",
678 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
679 "weight" INT4 );
680 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
682 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
684 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
685 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
688 CREATE TABLE "delegating_population_snapshot" (
689 PRIMARY KEY ("issue_id", "event", "member_id"),
690 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
691 "event" "snapshot_event",
692 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
693 "weight" INT4,
694 "scope" "delegation_scope" NOT NULL,
695 "delegate_member_ids" INT4[] NOT NULL );
696 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
698 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
700 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
701 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
702 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
703 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"';
706 CREATE TABLE "direct_interest_snapshot" (
707 PRIMARY KEY ("issue_id", "event", "member_id"),
708 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
709 "event" "snapshot_event",
710 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
711 "weight" INT4,
712 "voting_requested" BOOLEAN );
713 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
715 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
717 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
718 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
719 COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';
722 CREATE TABLE "delegating_interest_snapshot" (
723 PRIMARY KEY ("issue_id", "event", "member_id"),
724 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
725 "event" "snapshot_event",
726 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
727 "weight" INT4,
728 "scope" "delegation_scope" NOT NULL,
729 "delegate_member_ids" INT4[] NOT NULL );
730 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
732 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
734 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
735 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
736 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
737 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"';
740 CREATE TABLE "direct_supporter_snapshot" (
741 "issue_id" INT4 NOT NULL,
742 PRIMARY KEY ("initiative_id", "event", "member_id"),
743 "initiative_id" INT4,
744 "event" "snapshot_event",
745 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
746 "informed" BOOLEAN NOT NULL,
747 "satisfied" BOOLEAN NOT NULL,
748 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
749 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
750 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
752 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
754 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
755 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
756 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
759 CREATE TABLE "direct_voter" (
760 PRIMARY KEY ("issue_id", "member_id"),
761 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
762 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
763 "weight" INT4,
764 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
765 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
767 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.';
769 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
770 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
773 CREATE TABLE "delegating_voter" (
774 PRIMARY KEY ("issue_id", "member_id"),
775 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
776 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
777 "weight" INT4,
778 "scope" "delegation_scope" NOT NULL,
779 "delegate_member_ids" INT4[] NOT NULL );
780 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
782 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
784 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
785 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
786 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"';
789 CREATE TABLE "vote" (
790 "issue_id" INT4 NOT NULL,
791 PRIMARY KEY ("initiative_id", "member_id"),
792 "initiative_id" INT4,
793 "member_id" INT4,
794 "grade" INT4,
795 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
796 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
797 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
799 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.';
801 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.';
804 CREATE TABLE "contingent" (
805 "time_frame" INTERVAL PRIMARY KEY,
806 "text_entry_limit" INT4,
807 "initiative_limit" INT4 );
809 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.';
811 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';
812 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
816 --------------------------------
817 -- Writing of history entries --
818 --------------------------------
820 CREATE FUNCTION "write_member_history_trigger"()
821 RETURNS TRIGGER
822 LANGUAGE 'plpgsql' VOLATILE AS $$
823 BEGIN
824 IF
825 NEW."active" != OLD."active" OR
826 NEW."name" != OLD."name"
827 THEN
828 INSERT INTO "member_history"
829 ("member_id", "active", "name")
830 VALUES (NEW."id", OLD."active", OLD."name");
831 END IF;
832 RETURN NULL;
833 END;
834 $$;
836 CREATE TRIGGER "write_member_history"
837 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
838 "write_member_history_trigger"();
840 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
841 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
845 ----------------------------
846 -- Additional constraints --
847 ----------------------------
850 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
851 RETURNS TRIGGER
852 LANGUAGE 'plpgsql' VOLATILE AS $$
853 BEGIN
854 IF NOT EXISTS (
855 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
856 ) THEN
857 --RAISE 'Cannot create issue without an initial initiative.' USING
858 -- ERRCODE = 'integrity_constraint_violation',
859 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
860 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
861 END IF;
862 RETURN NULL;
863 END;
864 $$;
866 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
867 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
868 FOR EACH ROW EXECUTE PROCEDURE
869 "issue_requires_first_initiative_trigger"();
871 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
872 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
875 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
876 RETURNS TRIGGER
877 LANGUAGE 'plpgsql' VOLATILE AS $$
878 DECLARE
879 "reference_lost" BOOLEAN;
880 BEGIN
881 IF TG_OP = 'DELETE' THEN
882 "reference_lost" := TRUE;
883 ELSE
884 "reference_lost" := NEW."issue_id" != OLD."issue_id";
885 END IF;
886 IF
887 "reference_lost" AND NOT EXISTS (
888 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
889 )
890 THEN
891 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
892 END IF;
893 RETURN NULL;
894 END;
895 $$;
897 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
898 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
899 FOR EACH ROW EXECUTE PROCEDURE
900 "last_initiative_deletes_issue_trigger"();
902 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
903 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
906 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
907 RETURNS TRIGGER
908 LANGUAGE 'plpgsql' VOLATILE AS $$
909 BEGIN
910 IF NOT EXISTS (
911 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
912 ) THEN
913 --RAISE 'Cannot create initiative without an initial draft.' USING
914 -- ERRCODE = 'integrity_constraint_violation',
915 -- HINT = 'Create issue, initiative and draft within the same transaction.';
916 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
917 END IF;
918 RETURN NULL;
919 END;
920 $$;
922 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
923 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
924 FOR EACH ROW EXECUTE PROCEDURE
925 "initiative_requires_first_draft_trigger"();
927 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
928 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
931 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
932 RETURNS TRIGGER
933 LANGUAGE 'plpgsql' VOLATILE AS $$
934 DECLARE
935 "reference_lost" BOOLEAN;
936 BEGIN
937 IF TG_OP = 'DELETE' THEN
938 "reference_lost" := TRUE;
939 ELSE
940 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
941 END IF;
942 IF
943 "reference_lost" AND NOT EXISTS (
944 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
945 )
946 THEN
947 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
948 END IF;
949 RETURN NULL;
950 END;
951 $$;
953 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
954 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
955 FOR EACH ROW EXECUTE PROCEDURE
956 "last_draft_deletes_initiative_trigger"();
958 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
959 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
962 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
963 RETURNS TRIGGER
964 LANGUAGE 'plpgsql' VOLATILE AS $$
965 BEGIN
966 IF NOT EXISTS (
967 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
968 ) THEN
969 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
970 END IF;
971 RETURN NULL;
972 END;
973 $$;
975 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
976 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
977 FOR EACH ROW EXECUTE PROCEDURE
978 "suggestion_requires_first_opinion_trigger"();
980 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
981 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
984 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
985 RETURNS TRIGGER
986 LANGUAGE 'plpgsql' VOLATILE AS $$
987 DECLARE
988 "reference_lost" BOOLEAN;
989 BEGIN
990 IF TG_OP = 'DELETE' THEN
991 "reference_lost" := TRUE;
992 ELSE
993 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
994 END IF;
995 IF
996 "reference_lost" AND NOT EXISTS (
997 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
998 )
999 THEN
1000 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1001 END IF;
1002 RETURN NULL;
1003 END;
1004 $$;
1006 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1007 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1008 FOR EACH ROW EXECUTE PROCEDURE
1009 "last_opinion_deletes_suggestion_trigger"();
1011 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1012 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1016 ---------------------------------------------------------------
1017 -- Ensure that votes are not modified when issues are frozen --
1018 ---------------------------------------------------------------
1020 -- NOTE: Frontends should ensure this anyway, but in case of programming
1021 -- errors the following triggers ensure data integrity.
1024 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1025 RETURNS TRIGGER
1026 LANGUAGE 'plpgsql' VOLATILE AS $$
1027 DECLARE
1028 "issue_id_v" "issue"."id"%TYPE;
1029 "issue_row" "issue"%ROWTYPE;
1030 BEGIN
1031 IF TG_OP = 'DELETE' THEN
1032 "issue_id_v" := OLD."issue_id";
1033 ELSE
1034 "issue_id_v" := NEW."issue_id";
1035 END IF;
1036 SELECT INTO "issue_row" * FROM "issue"
1037 WHERE "id" = "issue_id_v" FOR SHARE;
1038 IF "issue_row"."closed" NOTNULL THEN
1039 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
1040 END IF;
1041 RETURN NULL;
1042 END;
1043 $$;
1045 CREATE TRIGGER "forbid_changes_on_closed_issue"
1046 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1047 FOR EACH ROW EXECUTE PROCEDURE
1048 "forbid_changes_on_closed_issue_trigger"();
1050 CREATE TRIGGER "forbid_changes_on_closed_issue"
1051 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1052 FOR EACH ROW EXECUTE PROCEDURE
1053 "forbid_changes_on_closed_issue_trigger"();
1055 CREATE TRIGGER "forbid_changes_on_closed_issue"
1056 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1057 FOR EACH ROW EXECUTE PROCEDURE
1058 "forbid_changes_on_closed_issue_trigger"();
1060 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"';
1061 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';
1062 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';
1063 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';
1067 --------------------------------------------------------------------
1068 -- Auto-retrieval of fields only needed for referential integrity --
1069 --------------------------------------------------------------------
1072 CREATE FUNCTION "autofill_issue_id_trigger"()
1073 RETURNS TRIGGER
1074 LANGUAGE 'plpgsql' VOLATILE AS $$
1075 BEGIN
1076 IF NEW."issue_id" ISNULL THEN
1077 SELECT "issue_id" INTO NEW."issue_id"
1078 FROM "initiative" WHERE "id" = NEW."initiative_id";
1079 END IF;
1080 RETURN NEW;
1081 END;
1082 $$;
1084 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1085 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1087 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1088 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1090 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1091 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1092 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1095 CREATE FUNCTION "autofill_initiative_id_trigger"()
1096 RETURNS TRIGGER
1097 LANGUAGE 'plpgsql' VOLATILE AS $$
1098 BEGIN
1099 IF NEW."initiative_id" ISNULL THEN
1100 SELECT "initiative_id" INTO NEW."initiative_id"
1101 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1102 END IF;
1103 RETURN NEW;
1104 END;
1105 $$;
1107 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1108 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1110 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1111 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1115 -----------------------------------------------------
1116 -- Automatic calculation of certain default values --
1117 -----------------------------------------------------
1120 CREATE FUNCTION "copy_timings_trigger"()
1121 RETURNS TRIGGER
1122 LANGUAGE 'plpgsql' VOLATILE AS $$
1123 DECLARE
1124 "policy_row" "policy"%ROWTYPE;
1125 BEGIN
1126 SELECT * INTO "policy_row" FROM "policy"
1127 WHERE "id" = NEW."policy_id";
1128 IF NEW."admission_time" ISNULL THEN
1129 NEW."admission_time" := "policy_row"."admission_time";
1130 END IF;
1131 IF NEW."discussion_time" ISNULL THEN
1132 NEW."discussion_time" := "policy_row"."discussion_time";
1133 END IF;
1134 IF NEW."verification_time" ISNULL THEN
1135 NEW."verification_time" := "policy_row"."verification_time";
1136 END IF;
1137 IF NEW."voting_time" ISNULL THEN
1138 NEW."voting_time" := "policy_row"."voting_time";
1139 END IF;
1140 RETURN NEW;
1141 END;
1142 $$;
1144 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1145 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1147 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1148 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1151 CREATE FUNCTION "copy_autoreject_trigger"()
1152 RETURNS TRIGGER
1153 LANGUAGE 'plpgsql' VOLATILE AS $$
1154 BEGIN
1155 IF NEW."autoreject" ISNULL THEN
1156 SELECT "membership"."autoreject" INTO NEW."autoreject"
1157 FROM "issue" JOIN "membership"
1158 ON "issue"."area_id" = "membership"."area_id"
1159 WHERE "issue"."id" = NEW."issue_id"
1160 AND "membership"."member_id" = NEW."member_id";
1161 END IF;
1162 IF NEW."autoreject" ISNULL THEN
1163 NEW."autoreject" := FALSE;
1164 END IF;
1165 RETURN NEW;
1166 END;
1167 $$;
1169 CREATE TRIGGER "copy_autoreject" BEFORE INSERT OR UPDATE ON "interest"
1170 FOR EACH ROW EXECUTE PROCEDURE "copy_autoreject_trigger"();
1172 COMMENT ON FUNCTION "copy_autoreject_trigger"() IS 'Implementation of trigger "copy_autoreject" on table "interest"';
1173 COMMENT ON TRIGGER "copy_autoreject" ON "interest" IS 'If "autoreject" is NULL, then copy it from the area setting, or set to FALSE, if no membership existent';
1176 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
1177 RETURNS TRIGGER
1178 LANGUAGE 'plpgsql' VOLATILE AS $$
1179 BEGIN
1180 IF NEW."draft_id" ISNULL THEN
1181 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1182 WHERE "initiative_id" = NEW."initiative_id";
1183 END IF;
1184 RETURN NEW;
1185 END;
1186 $$;
1188 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1189 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
1191 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
1192 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';
1196 ----------------------------------------
1197 -- Automatic creation of dependencies --
1198 ----------------------------------------
1201 CREATE FUNCTION "autocreate_interest_trigger"()
1202 RETURNS TRIGGER
1203 LANGUAGE 'plpgsql' VOLATILE AS $$
1204 BEGIN
1205 IF NOT EXISTS (
1206 SELECT NULL FROM "initiative" JOIN "interest"
1207 ON "initiative"."issue_id" = "interest"."issue_id"
1208 WHERE "initiative"."id" = NEW."initiative_id"
1209 AND "interest"."member_id" = NEW."member_id"
1210 ) THEN
1211 BEGIN
1212 INSERT INTO "interest" ("issue_id", "member_id")
1213 SELECT "issue_id", NEW."member_id"
1214 FROM "initiative" WHERE "id" = NEW."initiative_id";
1215 EXCEPTION WHEN unique_violation THEN END;
1216 END IF;
1217 RETURN NEW;
1218 END;
1219 $$;
1221 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1222 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1224 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1225 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';
1228 CREATE FUNCTION "autocreate_supporter_trigger"()
1229 RETURNS TRIGGER
1230 LANGUAGE 'plpgsql' VOLATILE AS $$
1231 BEGIN
1232 IF NOT EXISTS (
1233 SELECT NULL FROM "suggestion" JOIN "supporter"
1234 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1235 WHERE "suggestion"."id" = NEW."suggestion_id"
1236 AND "supporter"."member_id" = NEW."member_id"
1237 ) THEN
1238 BEGIN
1239 INSERT INTO "supporter" ("initiative_id", "member_id")
1240 SELECT "initiative_id", NEW."member_id"
1241 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1242 EXCEPTION WHEN unique_violation THEN END;
1243 END IF;
1244 RETURN NEW;
1245 END;
1246 $$;
1248 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1249 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1251 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1252 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.';
1256 ------------------------------------------
1257 -- Views and helper functions for views --
1258 ------------------------------------------
1261 CREATE VIEW "active_delegation" AS
1262 SELECT "delegation".* FROM "delegation"
1263 JOIN "member" ON "delegation"."truster_id" = "member"."id"
1264 WHERE "member"."active" = TRUE;
1266 COMMENT ON VIEW "active_delegation" IS 'Delegations where the truster_id refers to an active member';
1269 CREATE VIEW "global_delegation" AS
1270 SELECT "id", "truster_id", "trustee_id"
1271 FROM "active_delegation" WHERE "scope" = 'global';
1273 COMMENT ON VIEW "global_delegation" IS 'Global delegations from active members';
1276 CREATE VIEW "area_delegation" AS
1277 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1278 "area"."id" AS "area_id",
1279 "delegation"."id",
1280 "delegation"."truster_id",
1281 "delegation"."trustee_id",
1282 "delegation"."scope"
1283 FROM "area" JOIN "active_delegation" AS "delegation"
1284 ON "delegation"."scope" = 'global'
1285 OR "delegation"."area_id" = "area"."id"
1286 ORDER BY
1287 "area"."id",
1288 "delegation"."truster_id",
1289 "delegation"."scope" DESC;
1291 COMMENT ON VIEW "area_delegation" IS 'Resulting area delegations from active members';
1294 CREATE VIEW "issue_delegation" AS
1295 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1296 "issue"."id" AS "issue_id",
1297 "delegation"."id",
1298 "delegation"."truster_id",
1299 "delegation"."trustee_id",
1300 "delegation"."scope"
1301 FROM "issue" JOIN "active_delegation" AS "delegation"
1302 ON "delegation"."scope" = 'global'
1303 OR "delegation"."area_id" = "issue"."area_id"
1304 OR "delegation"."issue_id" = "issue"."id"
1305 ORDER BY
1306 "issue"."id",
1307 "delegation"."truster_id",
1308 "delegation"."scope" DESC;
1310 COMMENT ON VIEW "issue_delegation" IS 'Resulting issue delegations from active members';
1313 CREATE FUNCTION "membership_weight_with_skipping"
1314 ( "area_id_p" "area"."id"%TYPE,
1315 "member_id_p" "member"."id"%TYPE,
1316 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1317 RETURNS INT4
1318 LANGUAGE 'plpgsql' STABLE AS $$
1319 DECLARE
1320 "sum_v" INT4;
1321 "delegation_row" "area_delegation"%ROWTYPE;
1322 BEGIN
1323 "sum_v" := 1;
1324 FOR "delegation_row" IN
1325 SELECT "area_delegation".*
1326 FROM "area_delegation" LEFT JOIN "membership"
1327 ON "membership"."area_id" = "area_id_p"
1328 AND "membership"."member_id" = "area_delegation"."truster_id"
1329 WHERE "area_delegation"."area_id" = "area_id_p"
1330 AND "area_delegation"."trustee_id" = "member_id_p"
1331 AND "membership"."member_id" ISNULL
1332 LOOP
1333 IF NOT
1334 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1335 THEN
1336 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1337 "area_id_p",
1338 "delegation_row"."truster_id",
1339 "skip_member_ids_p" || "delegation_row"."truster_id"
1340 );
1341 END IF;
1342 END LOOP;
1343 RETURN "sum_v";
1344 END;
1345 $$;
1347 COMMENT ON FUNCTION "membership_weight_with_skipping"
1348 ( "area"."id"%TYPE,
1349 "member"."id"%TYPE,
1350 INT4[] )
1351 IS 'Helper function for "membership_weight" function';
1354 CREATE FUNCTION "membership_weight"
1355 ( "area_id_p" "area"."id"%TYPE,
1356 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1357 RETURNS INT4
1358 LANGUAGE 'plpgsql' STABLE AS $$
1359 BEGIN
1360 RETURN "membership_weight_with_skipping"(
1361 "area_id_p",
1362 "member_id_p",
1363 ARRAY["member_id_p"]
1364 );
1365 END;
1366 $$;
1368 COMMENT ON FUNCTION "membership_weight"
1369 ( "area"."id"%TYPE,
1370 "member"."id"%TYPE )
1371 IS 'Calculates the potential voting weight of a member in a given area';
1374 CREATE VIEW "member_count_view" AS
1375 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1377 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1380 CREATE VIEW "area_member_count" AS
1381 SELECT
1382 "area"."id" AS "area_id",
1383 count("member"."id") AS "direct_member_count",
1384 coalesce(
1385 sum(
1386 CASE WHEN "member"."id" NOTNULL THEN
1387 "membership_weight"("area"."id", "member"."id")
1388 ELSE 0 END
1389 )
1390 ) AS "member_weight",
1391 coalesce(
1392 sum(
1393 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
1394 "membership_weight"("area"."id", "member"."id")
1395 ELSE 0 END
1396 )
1397 ) AS "autoreject_weight"
1398 FROM "area"
1399 LEFT JOIN "membership"
1400 ON "area"."id" = "membership"."area_id"
1401 LEFT JOIN "member"
1402 ON "membership"."member_id" = "member"."id"
1403 AND "member"."active"
1404 GROUP BY "area"."id";
1406 COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"';
1409 CREATE VIEW "opening_draft" AS
1410 SELECT "draft".* FROM (
1411 SELECT
1412 "initiative"."id" AS "initiative_id",
1413 min("draft"."id") AS "draft_id"
1414 FROM "initiative" JOIN "draft"
1415 ON "initiative"."id" = "draft"."initiative_id"
1416 GROUP BY "initiative"."id"
1417 ) AS "subquery"
1418 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1420 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1423 CREATE VIEW "current_draft" AS
1424 SELECT "draft".* FROM (
1425 SELECT
1426 "initiative"."id" AS "initiative_id",
1427 max("draft"."id") AS "draft_id"
1428 FROM "initiative" JOIN "draft"
1429 ON "initiative"."id" = "draft"."initiative_id"
1430 GROUP BY "initiative"."id"
1431 ) AS "subquery"
1432 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1434 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1437 CREATE VIEW "critical_opinion" AS
1438 SELECT * FROM "opinion"
1439 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
1440 OR ("degree" = -2 AND "fulfilled" = TRUE);
1442 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
1445 CREATE VIEW "battle_view" AS
1446 SELECT
1447 "issue"."id" AS "issue_id",
1448 "winning_initiative"."id" AS "winning_initiative_id",
1449 "losing_initiative"."id" AS "losing_initiative_id",
1450 sum(
1451 CASE WHEN
1452 coalesce("better_vote"."grade", 0) >
1453 coalesce("worse_vote"."grade", 0)
1454 THEN "direct_voter"."weight" ELSE 0 END
1455 ) AS "count"
1456 FROM "issue"
1457 LEFT JOIN "direct_voter"
1458 ON "issue"."id" = "direct_voter"."issue_id"
1459 JOIN "initiative" AS "winning_initiative"
1460 ON "issue"."id" = "winning_initiative"."issue_id"
1461 AND "winning_initiative"."agreed"
1462 JOIN "initiative" AS "losing_initiative"
1463 ON "issue"."id" = "losing_initiative"."issue_id"
1464 AND "losing_initiative"."agreed"
1465 LEFT JOIN "vote" AS "better_vote"
1466 ON "direct_voter"."member_id" = "better_vote"."member_id"
1467 AND "winning_initiative"."id" = "better_vote"."initiative_id"
1468 LEFT JOIN "vote" AS "worse_vote"
1469 ON "direct_voter"."member_id" = "worse_vote"."member_id"
1470 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
1471 WHERE "issue"."closed" NOTNULL
1472 AND "issue"."cleaned" ISNULL
1473 AND "winning_initiative"."id" != "losing_initiative"."id"
1474 GROUP BY
1475 "issue"."id",
1476 "winning_initiative"."id",
1477 "losing_initiative"."id";
1479 COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative to another; Used to fill "battle" table';
1482 CREATE VIEW "expired_session" AS
1483 SELECT * FROM "session" WHERE now() > "expiry";
1485 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
1486 DELETE FROM "session" WHERE "ident" = OLD."ident";
1488 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
1489 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
1492 CREATE VIEW "open_issue" AS
1493 SELECT * FROM "issue" WHERE "closed" ISNULL;
1495 COMMENT ON VIEW "open_issue" IS 'All open issues';
1498 CREATE VIEW "issue_with_ranks_missing" AS
1499 SELECT * FROM "issue"
1500 WHERE "fully_frozen" NOTNULL
1501 AND "closed" NOTNULL
1502 AND "ranks_available" = FALSE;
1504 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
1507 CREATE VIEW "member_contingent" AS
1508 SELECT
1509 "member"."id" AS "member_id",
1510 "contingent"."time_frame",
1511 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
1512 (
1513 SELECT count(1) FROM "draft"
1514 WHERE "draft"."author_id" = "member"."id"
1515 AND "draft"."created" > now() - "contingent"."time_frame"
1516 ) + (
1517 SELECT count(1) FROM "suggestion"
1518 WHERE "suggestion"."author_id" = "member"."id"
1519 AND "suggestion"."created" > now() - "contingent"."time_frame"
1520 )
1521 ELSE NULL END AS "text_entry_count",
1522 "contingent"."text_entry_limit",
1523 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
1524 SELECT count(1) FROM "opening_draft"
1525 WHERE "opening_draft"."author_id" = "member"."id"
1526 AND "opening_draft"."created" > now() - "contingent"."time_frame"
1527 ) ELSE NULL END AS "initiative_count",
1528 "contingent"."initiative_limit"
1529 FROM "member" CROSS JOIN "contingent";
1531 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
1533 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
1534 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
1537 CREATE VIEW "member_contingent_left" AS
1538 SELECT
1539 "member_id",
1540 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
1541 max("initiative_limit" - "initiative_count") AS "initiatives_left"
1542 FROM "member_contingent" GROUP BY "member_id";
1544 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.';
1547 CREATE TYPE "timeline_event" AS ENUM (
1548 'issue_created',
1549 'issue_canceled',
1550 'issue_accepted',
1551 'issue_half_frozen',
1552 'issue_finished_without_voting',
1553 'issue_voting_started',
1554 'issue_finished_after_voting',
1555 'initiative_created',
1556 'initiative_revoked',
1557 'draft_created',
1558 'suggestion_created');
1560 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables';
1563 CREATE VIEW "timeline_issue" AS
1564 SELECT
1565 "created" AS "occurrence",
1566 'issue_created'::"timeline_event" AS "event",
1567 "id" AS "issue_id"
1568 FROM "issue"
1569 UNION ALL
1570 SELECT
1571 "closed" AS "occurrence",
1572 'issue_canceled'::"timeline_event" AS "event",
1573 "id" AS "issue_id"
1574 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
1575 UNION ALL
1576 SELECT
1577 "accepted" AS "occurrence",
1578 'issue_accepted'::"timeline_event" AS "event",
1579 "id" AS "issue_id"
1580 FROM "issue" WHERE "accepted" NOTNULL
1581 UNION ALL
1582 SELECT
1583 "half_frozen" AS "occurrence",
1584 'issue_half_frozen'::"timeline_event" AS "event",
1585 "id" AS "issue_id"
1586 FROM "issue" WHERE "half_frozen" NOTNULL
1587 UNION ALL
1588 SELECT
1589 "fully_frozen" AS "occurrence",
1590 'issue_voting_started'::"timeline_event" AS "event",
1591 "id" AS "issue_id"
1592 FROM "issue"
1593 WHERE "fully_frozen" NOTNULL
1594 AND ("closed" ISNULL OR "closed" != "fully_frozen")
1595 UNION ALL
1596 SELECT
1597 "closed" AS "occurrence",
1598 CASE WHEN "fully_frozen" = "closed" THEN
1599 'issue_finished_without_voting'::"timeline_event"
1600 ELSE
1601 'issue_finished_after_voting'::"timeline_event"
1602 END AS "event",
1603 "id" AS "issue_id"
1604 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
1606 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view';
1609 CREATE VIEW "timeline_initiative" AS
1610 SELECT
1611 "created" AS "occurrence",
1612 'initiative_created'::"timeline_event" AS "event",
1613 "id" AS "initiative_id"
1614 FROM "initiative"
1615 UNION ALL
1616 SELECT
1617 "revoked" AS "occurrence",
1618 'initiative_revoked'::"timeline_event" AS "event",
1619 "id" AS "initiative_id"
1620 FROM "initiative" WHERE "revoked" NOTNULL;
1622 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view';
1625 CREATE VIEW "timeline_draft" AS
1626 SELECT
1627 "created" AS "occurrence",
1628 'draft_created'::"timeline_event" AS "event",
1629 "id" AS "draft_id"
1630 FROM "draft";
1632 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view';
1635 CREATE VIEW "timeline_suggestion" AS
1636 SELECT
1637 "created" AS "occurrence",
1638 'suggestion_created'::"timeline_event" AS "event",
1639 "id" AS "suggestion_id"
1640 FROM "suggestion";
1642 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view';
1645 CREATE VIEW "timeline" AS
1646 SELECT
1647 "occurrence",
1648 "event",
1649 "issue_id",
1650 NULL AS "initiative_id",
1651 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
1652 NULL::INT8 AS "suggestion_id"
1653 FROM "timeline_issue"
1654 UNION ALL
1655 SELECT
1656 "occurrence",
1657 "event",
1658 NULL AS "issue_id",
1659 "initiative_id",
1660 NULL AS "draft_id",
1661 NULL AS "suggestion_id"
1662 FROM "timeline_initiative"
1663 UNION ALL
1664 SELECT
1665 "occurrence",
1666 "event",
1667 NULL AS "issue_id",
1668 NULL AS "initiative_id",
1669 "draft_id",
1670 NULL AS "suggestion_id"
1671 FROM "timeline_draft"
1672 UNION ALL
1673 SELECT
1674 "occurrence",
1675 "event",
1676 NULL AS "issue_id",
1677 NULL AS "initiative_id",
1678 NULL AS "draft_id",
1679 "suggestion_id"
1680 FROM "timeline_suggestion";
1682 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system';
1686 --------------------------------------------------
1687 -- Set returning function for delegation chains --
1688 --------------------------------------------------
1691 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
1692 ('first', 'intermediate', 'last', 'repetition');
1694 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
1697 CREATE TYPE "delegation_chain_row" AS (
1698 "index" INT4,
1699 "member_id" INT4,
1700 "member_active" BOOLEAN,
1701 "participation" BOOLEAN,
1702 "overridden" BOOLEAN,
1703 "scope_in" "delegation_scope",
1704 "scope_out" "delegation_scope",
1705 "loop" "delegation_chain_loop_tag" );
1707 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
1709 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
1710 COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest, for areas: membership, for global delegation chains: always null';
1711 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
1712 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
1713 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
1714 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
1717 CREATE FUNCTION "delegation_chain"
1718 ( "member_id_p" "member"."id"%TYPE,
1719 "area_id_p" "area"."id"%TYPE,
1720 "issue_id_p" "issue"."id"%TYPE,
1721 "simulate_trustee_id_p" "member"."id"%TYPE )
1722 RETURNS SETOF "delegation_chain_row"
1723 LANGUAGE 'plpgsql' STABLE AS $$
1724 DECLARE
1725 "issue_row" "issue"%ROWTYPE;
1726 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
1727 "loop_member_id_v" "member"."id"%TYPE;
1728 "output_row" "delegation_chain_row";
1729 "output_rows" "delegation_chain_row"[];
1730 "delegation_row" "delegation"%ROWTYPE;
1731 "row_count" INT4;
1732 "i" INT4;
1733 "loop_v" BOOLEAN;
1734 BEGIN
1735 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1736 "visited_member_ids" := '{}';
1737 "loop_member_id_v" := NULL;
1738 "output_rows" := '{}';
1739 "output_row"."index" := 0;
1740 "output_row"."member_id" := "member_id_p";
1741 "output_row"."member_active" := TRUE;
1742 "output_row"."participation" := FALSE;
1743 "output_row"."overridden" := FALSE;
1744 "output_row"."scope_out" := NULL;
1745 LOOP
1746 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
1747 "loop_member_id_v" := "output_row"."member_id";
1748 ELSE
1749 "visited_member_ids" :=
1750 "visited_member_ids" || "output_row"."member_id";
1751 END IF;
1752 IF "output_row"."participation" THEN
1753 "output_row"."overridden" := TRUE;
1754 END IF;
1755 "output_row"."scope_in" := "output_row"."scope_out";
1756 IF EXISTS (
1757 SELECT NULL FROM "member"
1758 WHERE "id" = "output_row"."member_id" AND "active"
1759 ) THEN
1760 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
1761 SELECT * INTO "delegation_row" FROM "delegation"
1762 WHERE "truster_id" = "output_row"."member_id"
1763 AND "scope" = 'global';
1764 ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN
1765 "output_row"."participation" := EXISTS (
1766 SELECT NULL FROM "membership"
1767 WHERE "area_id" = "area_id_p"
1768 AND "member_id" = "output_row"."member_id"
1769 );
1770 SELECT * INTO "delegation_row" FROM "delegation"
1771 WHERE "truster_id" = "output_row"."member_id"
1772 AND ("scope" = 'global' OR "area_id" = "area_id_p")
1773 ORDER BY "scope" DESC;
1774 ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN
1775 "output_row"."participation" := EXISTS (
1776 SELECT NULL FROM "interest"
1777 WHERE "issue_id" = "issue_id_p"
1778 AND "member_id" = "output_row"."member_id"
1779 );
1780 SELECT * INTO "delegation_row" FROM "delegation"
1781 WHERE "truster_id" = "output_row"."member_id"
1782 AND ("scope" = 'global' OR
1783 "area_id" = "issue_row"."area_id" OR
1784 "issue_id" = "issue_id_p"
1785 )
1786 ORDER BY "scope" DESC;
1787 ELSE
1788 RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.';
1789 END IF;
1790 ELSE
1791 "output_row"."member_active" := FALSE;
1792 "output_row"."participation" := FALSE;
1793 "output_row"."scope_out" := NULL;
1794 "delegation_row" := ROW(NULL);
1795 END IF;
1796 IF
1797 "output_row"."member_id" = "member_id_p" AND
1798 "simulate_trustee_id_p" NOTNULL
1799 THEN
1800 "output_row"."scope_out" := CASE
1801 WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global'
1802 WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area'
1803 WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue'
1804 END;
1805 "output_rows" := "output_rows" || "output_row";
1806 "output_row"."member_id" := "simulate_trustee_id_p";
1807 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
1808 "output_row"."scope_out" := "delegation_row"."scope";
1809 "output_rows" := "output_rows" || "output_row";
1810 "output_row"."member_id" := "delegation_row"."trustee_id";
1811 ELSE
1812 "output_row"."scope_out" := NULL;
1813 "output_rows" := "output_rows" || "output_row";
1814 EXIT;
1815 END IF;
1816 EXIT WHEN "loop_member_id_v" NOTNULL;
1817 "output_row"."index" := "output_row"."index" + 1;
1818 END LOOP;
1819 "row_count" := array_upper("output_rows", 1);
1820 "i" := 1;
1821 "loop_v" := FALSE;
1822 LOOP
1823 "output_row" := "output_rows"["i"];
1824 EXIT WHEN "output_row"."member_id" ISNULL;
1825 IF "loop_v" THEN
1826 IF "i" + 1 = "row_count" THEN
1827 "output_row"."loop" := 'last';
1828 ELSIF "i" = "row_count" THEN
1829 "output_row"."loop" := 'repetition';
1830 ELSE
1831 "output_row"."loop" := 'intermediate';
1832 END IF;
1833 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
1834 "output_row"."loop" := 'first';
1835 "loop_v" := TRUE;
1836 END IF;
1837 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
1838 "output_row"."participation" := NULL;
1839 END IF;
1840 RETURN NEXT "output_row";
1841 "i" := "i" + 1;
1842 END LOOP;
1843 RETURN;
1844 END;
1845 $$;
1847 COMMENT ON FUNCTION "delegation_chain"
1848 ( "member"."id"%TYPE,
1849 "area"."id"%TYPE,
1850 "issue"."id"%TYPE,
1851 "member"."id"%TYPE )
1852 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
1854 CREATE FUNCTION "delegation_chain"
1855 ( "member_id_p" "member"."id"%TYPE,
1856 "area_id_p" "area"."id"%TYPE,
1857 "issue_id_p" "issue"."id"%TYPE )
1858 RETURNS SETOF "delegation_chain_row"
1859 LANGUAGE 'plpgsql' STABLE AS $$
1860 DECLARE
1861 "result_row" "delegation_chain_row";
1862 BEGIN
1863 FOR "result_row" IN
1864 SELECT * FROM "delegation_chain"(
1865 "member_id_p", "area_id_p", "issue_id_p", NULL
1866 )
1867 LOOP
1868 RETURN NEXT "result_row";
1869 END LOOP;
1870 RETURN;
1871 END;
1872 $$;
1874 COMMENT ON FUNCTION "delegation_chain"
1875 ( "member"."id"%TYPE,
1876 "area"."id"%TYPE,
1877 "issue"."id"%TYPE )
1878 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
1882 ------------------------------
1883 -- Comparison by vote count --
1884 ------------------------------
1886 CREATE FUNCTION "vote_ratio"
1887 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
1888 "negative_votes_p" "initiative"."negative_votes"%TYPE )
1889 RETURNS FLOAT8
1890 LANGUAGE 'plpgsql' STABLE AS $$
1891 BEGIN
1892 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
1893 RETURN
1894 "positive_votes_p"::FLOAT8 /
1895 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
1896 ELSIF "positive_votes_p" > 0 THEN
1897 RETURN "positive_votes_p";
1898 ELSIF "negative_votes_p" > 0 THEN
1899 RETURN 1 - "negative_votes_p";
1900 ELSE
1901 RETURN 0.5;
1902 END IF;
1903 END;
1904 $$;
1906 COMMENT ON FUNCTION "vote_ratio"
1907 ( "initiative"."positive_votes"%TYPE,
1908 "initiative"."negative_votes"%TYPE )
1909 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.';
1913 ------------------------------------------------
1914 -- Locking for snapshots and voting procedure --
1915 ------------------------------------------------
1918 CREATE FUNCTION "share_row_lock_issue_trigger"()
1919 RETURNS TRIGGER
1920 LANGUAGE 'plpgsql' VOLATILE AS $$
1921 BEGIN
1922 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
1923 PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;
1924 END IF;
1925 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1926 PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;
1927 RETURN NEW;
1928 ELSE
1929 RETURN OLD;
1930 END IF;
1931 END;
1932 $$;
1934 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';
1937 CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()
1938 RETURNS TRIGGER
1939 LANGUAGE 'plpgsql' VOLATILE AS $$
1940 BEGIN
1941 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
1942 PERFORM NULL FROM "issue"
1943 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
1944 WHERE "initiative"."id" = OLD."initiative_id"
1945 FOR SHARE OF "issue";
1946 END IF;
1947 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1948 PERFORM NULL FROM "issue"
1949 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
1950 WHERE "initiative"."id" = NEW."initiative_id"
1951 FOR SHARE OF "issue";
1952 RETURN NEW;
1953 ELSE
1954 RETURN OLD;
1955 END IF;
1956 END;
1957 $$;
1959 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';
1962 CREATE TRIGGER "share_row_lock_issue"
1963 BEFORE INSERT OR UPDATE OR DELETE ON "initiative"
1964 FOR EACH ROW EXECUTE PROCEDURE
1965 "share_row_lock_issue_trigger"();
1967 CREATE TRIGGER "share_row_lock_issue"
1968 BEFORE INSERT OR UPDATE OR DELETE ON "interest"
1969 FOR EACH ROW EXECUTE PROCEDURE
1970 "share_row_lock_issue_trigger"();
1972 CREATE TRIGGER "share_row_lock_issue"
1973 BEFORE INSERT OR UPDATE OR DELETE ON "supporter"
1974 FOR EACH ROW EXECUTE PROCEDURE
1975 "share_row_lock_issue_trigger"();
1977 CREATE TRIGGER "share_row_lock_issue_via_initiative"
1978 BEFORE INSERT OR UPDATE OR DELETE ON "opinion"
1979 FOR EACH ROW EXECUTE PROCEDURE
1980 "share_row_lock_issue_via_initiative_trigger"();
1982 CREATE TRIGGER "share_row_lock_issue"
1983 BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter"
1984 FOR EACH ROW EXECUTE PROCEDURE
1985 "share_row_lock_issue_trigger"();
1987 CREATE TRIGGER "share_row_lock_issue"
1988 BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter"
1989 FOR EACH ROW EXECUTE PROCEDURE
1990 "share_row_lock_issue_trigger"();
1992 CREATE TRIGGER "share_row_lock_issue"
1993 BEFORE INSERT OR UPDATE OR DELETE ON "vote"
1994 FOR EACH ROW EXECUTE PROCEDURE
1995 "share_row_lock_issue_trigger"();
1997 COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function';
1998 COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function';
1999 COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function';
2000 COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function';
2001 COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function';
2002 COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function';
2003 COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function';
2006 CREATE FUNCTION "lock_issue"
2007 ( "issue_id_p" "issue"."id"%TYPE )
2008 RETURNS VOID
2009 LANGUAGE 'plpgsql' VOLATILE AS $$
2010 BEGIN
2011 LOCK TABLE "member" IN SHARE MODE;
2012 LOCK TABLE "membership" IN SHARE MODE;
2013 LOCK TABLE "policy" IN SHARE MODE;
2014 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2015 -- NOTE: The row-level exclusive lock in combination with the
2016 -- share_row_lock_issue(_via_initiative)_trigger functions (which
2017 -- acquire a row-level share lock on the issue) ensure that no data
2018 -- is changed, which could affect calculation of snapshots or
2019 -- counting of votes. Table "delegation" must be table-level-locked,
2020 -- as it also contains issue- and global-scope delegations.
2021 LOCK TABLE "delegation" IN SHARE MODE;
2022 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
2023 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
2024 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
2025 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
2026 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
2027 RETURN;
2028 END;
2029 $$;
2031 COMMENT ON FUNCTION "lock_issue"
2032 ( "issue"."id"%TYPE )
2033 IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';
2037 -------------------------------
2038 -- Materialize member counts --
2039 -------------------------------
2041 CREATE FUNCTION "calculate_member_counts"()
2042 RETURNS VOID
2043 LANGUAGE 'plpgsql' VOLATILE AS $$
2044 BEGIN
2045 LOCK TABLE "member" IN SHARE MODE;
2046 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
2047 LOCK TABLE "area" IN EXCLUSIVE MODE;
2048 LOCK TABLE "membership" IN SHARE MODE;
2049 DELETE FROM "member_count";
2050 INSERT INTO "member_count" ("total_count")
2051 SELECT "total_count" FROM "member_count_view";
2052 UPDATE "area" SET
2053 "direct_member_count" = "view"."direct_member_count",
2054 "member_weight" = "view"."member_weight",
2055 "autoreject_weight" = "view"."autoreject_weight"
2056 FROM "area_member_count" AS "view"
2057 WHERE "view"."area_id" = "area"."id";
2058 RETURN;
2059 END;
2060 $$;
2062 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"';
2066 ------------------------------
2067 -- Calculation of snapshots --
2068 ------------------------------
2070 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
2071 ( "issue_id_p" "issue"."id"%TYPE,
2072 "member_id_p" "member"."id"%TYPE,
2073 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2074 RETURNS "direct_population_snapshot"."weight"%TYPE
2075 LANGUAGE 'plpgsql' VOLATILE AS $$
2076 DECLARE
2077 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2078 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
2079 "weight_v" INT4;
2080 "sub_weight_v" INT4;
2081 BEGIN
2082 "weight_v" := 0;
2083 FOR "issue_delegation_row" IN
2084 SELECT * FROM "issue_delegation"
2085 WHERE "trustee_id" = "member_id_p"
2086 AND "issue_id" = "issue_id_p"
2087 LOOP
2088 IF NOT EXISTS (
2089 SELECT NULL FROM "direct_population_snapshot"
2090 WHERE "issue_id" = "issue_id_p"
2091 AND "event" = 'periodic'
2092 AND "member_id" = "issue_delegation_row"."truster_id"
2093 ) AND NOT EXISTS (
2094 SELECT NULL FROM "delegating_population_snapshot"
2095 WHERE "issue_id" = "issue_id_p"
2096 AND "event" = 'periodic'
2097 AND "member_id" = "issue_delegation_row"."truster_id"
2098 ) THEN
2099 "delegate_member_ids_v" :=
2100 "member_id_p" || "delegate_member_ids_p";
2101 INSERT INTO "delegating_population_snapshot" (
2102 "issue_id",
2103 "event",
2104 "member_id",
2105 "scope",
2106 "delegate_member_ids"
2107 ) VALUES (
2108 "issue_id_p",
2109 'periodic',
2110 "issue_delegation_row"."truster_id",
2111 "issue_delegation_row"."scope",
2112 "delegate_member_ids_v"
2113 );
2114 "sub_weight_v" := 1 +
2115 "weight_of_added_delegations_for_population_snapshot"(
2116 "issue_id_p",
2117 "issue_delegation_row"."truster_id",
2118 "delegate_member_ids_v"
2119 );
2120 UPDATE "delegating_population_snapshot"
2121 SET "weight" = "sub_weight_v"
2122 WHERE "issue_id" = "issue_id_p"
2123 AND "event" = 'periodic'
2124 AND "member_id" = "issue_delegation_row"."truster_id";
2125 "weight_v" := "weight_v" + "sub_weight_v";
2126 END IF;
2127 END LOOP;
2128 RETURN "weight_v";
2129 END;
2130 $$;
2132 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
2133 ( "issue"."id"%TYPE,
2134 "member"."id"%TYPE,
2135 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2136 IS 'Helper function for "create_population_snapshot" function';
2139 CREATE FUNCTION "create_population_snapshot"
2140 ( "issue_id_p" "issue"."id"%TYPE )
2141 RETURNS VOID
2142 LANGUAGE 'plpgsql' VOLATILE AS $$
2143 DECLARE
2144 "member_id_v" "member"."id"%TYPE;
2145 BEGIN
2146 DELETE FROM "direct_population_snapshot"
2147 WHERE "issue_id" = "issue_id_p"
2148 AND "event" = 'periodic';
2149 DELETE FROM "delegating_population_snapshot"
2150 WHERE "issue_id" = "issue_id_p"
2151 AND "event" = 'periodic';
2152 INSERT INTO "direct_population_snapshot"
2153 ("issue_id", "event", "member_id")
2154 SELECT
2155 "issue_id_p" AS "issue_id",
2156 'periodic'::"snapshot_event" AS "event",
2157 "member"."id" AS "member_id"
2158 FROM "issue"
2159 JOIN "area" ON "issue"."area_id" = "area"."id"
2160 JOIN "membership" ON "area"."id" = "membership"."area_id"
2161 JOIN "member" ON "membership"."member_id" = "member"."id"
2162 WHERE "issue"."id" = "issue_id_p"
2163 AND "member"."active"
2164 UNION
2165 SELECT
2166 "issue_id_p" AS "issue_id",
2167 'periodic'::"snapshot_event" AS "event",
2168 "member"."id" AS "member_id"
2169 FROM "interest" JOIN "member"
2170 ON "interest"."member_id" = "member"."id"
2171 WHERE "interest"."issue_id" = "issue_id_p"
2172 AND "member"."active";
2173 FOR "member_id_v" IN
2174 SELECT "member_id" FROM "direct_population_snapshot"
2175 WHERE "issue_id" = "issue_id_p"
2176 AND "event" = 'periodic'
2177 LOOP
2178 UPDATE "direct_population_snapshot" SET
2179 "weight" = 1 +
2180 "weight_of_added_delegations_for_population_snapshot"(
2181 "issue_id_p",
2182 "member_id_v",
2183 '{}'
2184 )
2185 WHERE "issue_id" = "issue_id_p"
2186 AND "event" = 'periodic'
2187 AND "member_id" = "member_id_v";
2188 END LOOP;
2189 RETURN;
2190 END;
2191 $$;
2193 COMMENT ON FUNCTION "create_population_snapshot"
2194 ( "issue"."id"%TYPE )
2195 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.';
2198 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2199 ( "issue_id_p" "issue"."id"%TYPE,
2200 "member_id_p" "member"."id"%TYPE,
2201 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2202 RETURNS "direct_interest_snapshot"."weight"%TYPE
2203 LANGUAGE 'plpgsql' VOLATILE AS $$
2204 DECLARE
2205 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2206 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2207 "weight_v" INT4;
2208 "sub_weight_v" INT4;
2209 BEGIN
2210 "weight_v" := 0;
2211 FOR "issue_delegation_row" IN
2212 SELECT * FROM "issue_delegation"
2213 WHERE "trustee_id" = "member_id_p"
2214 AND "issue_id" = "issue_id_p"
2215 LOOP
2216 IF NOT EXISTS (
2217 SELECT NULL FROM "direct_interest_snapshot"
2218 WHERE "issue_id" = "issue_id_p"
2219 AND "event" = 'periodic'
2220 AND "member_id" = "issue_delegation_row"."truster_id"
2221 ) AND NOT EXISTS (
2222 SELECT NULL FROM "delegating_interest_snapshot"
2223 WHERE "issue_id" = "issue_id_p"
2224 AND "event" = 'periodic'
2225 AND "member_id" = "issue_delegation_row"."truster_id"
2226 ) THEN
2227 "delegate_member_ids_v" :=
2228 "member_id_p" || "delegate_member_ids_p";
2229 INSERT INTO "delegating_interest_snapshot" (
2230 "issue_id",
2231 "event",
2232 "member_id",
2233 "scope",
2234 "delegate_member_ids"
2235 ) VALUES (
2236 "issue_id_p",
2237 'periodic',
2238 "issue_delegation_row"."truster_id",
2239 "issue_delegation_row"."scope",
2240 "delegate_member_ids_v"
2241 );
2242 "sub_weight_v" := 1 +
2243 "weight_of_added_delegations_for_interest_snapshot"(
2244 "issue_id_p",
2245 "issue_delegation_row"."truster_id",
2246 "delegate_member_ids_v"
2247 );
2248 UPDATE "delegating_interest_snapshot"
2249 SET "weight" = "sub_weight_v"
2250 WHERE "issue_id" = "issue_id_p"
2251 AND "event" = 'periodic'
2252 AND "member_id" = "issue_delegation_row"."truster_id";
2253 "weight_v" := "weight_v" + "sub_weight_v";
2254 END IF;
2255 END LOOP;
2256 RETURN "weight_v";
2257 END;
2258 $$;
2260 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2261 ( "issue"."id"%TYPE,
2262 "member"."id"%TYPE,
2263 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2264 IS 'Helper function for "create_interest_snapshot" function';
2267 CREATE FUNCTION "create_interest_snapshot"
2268 ( "issue_id_p" "issue"."id"%TYPE )
2269 RETURNS VOID
2270 LANGUAGE 'plpgsql' VOLATILE AS $$
2271 DECLARE
2272 "member_id_v" "member"."id"%TYPE;
2273 BEGIN
2274 DELETE FROM "direct_interest_snapshot"
2275 WHERE "issue_id" = "issue_id_p"
2276 AND "event" = 'periodic';
2277 DELETE FROM "delegating_interest_snapshot"
2278 WHERE "issue_id" = "issue_id_p"
2279 AND "event" = 'periodic';
2280 DELETE FROM "direct_supporter_snapshot"
2281 WHERE "issue_id" = "issue_id_p"
2282 AND "event" = 'periodic';
2283 INSERT INTO "direct_interest_snapshot"
2284 ("issue_id", "event", "member_id", "voting_requested")
2285 SELECT
2286 "issue_id_p" AS "issue_id",
2287 'periodic' AS "event",
2288 "member"."id" AS "member_id",
2289 "interest"."voting_requested"
2290 FROM "interest" JOIN "member"
2291 ON "interest"."member_id" = "member"."id"
2292 WHERE "interest"."issue_id" = "issue_id_p"
2293 AND "member"."active";
2294 FOR "member_id_v" IN
2295 SELECT "member_id" FROM "direct_interest_snapshot"
2296 WHERE "issue_id" = "issue_id_p"
2297 AND "event" = 'periodic'
2298 LOOP
2299 UPDATE "direct_interest_snapshot" SET
2300 "weight" = 1 +
2301 "weight_of_added_delegations_for_interest_snapshot"(
2302 "issue_id_p",
2303 "member_id_v",
2304 '{}'
2305 )
2306 WHERE "issue_id" = "issue_id_p"
2307 AND "event" = 'periodic'
2308 AND "member_id" = "member_id_v";
2309 END LOOP;
2310 INSERT INTO "direct_supporter_snapshot"
2311 ( "issue_id", "initiative_id", "event", "member_id",
2312 "informed", "satisfied" )
2313 SELECT
2314 "issue_id_p" AS "issue_id",
2315 "initiative"."id" AS "initiative_id",
2316 'periodic' AS "event",
2317 "member"."id" AS "member_id",
2318 "supporter"."draft_id" = "current_draft"."id" AS "informed",
2319 NOT EXISTS (
2320 SELECT NULL FROM "critical_opinion"
2321 WHERE "initiative_id" = "initiative"."id"
2322 AND "member_id" = "member"."id"
2323 ) AS "satisfied"
2324 FROM "supporter"
2325 JOIN "member"
2326 ON "supporter"."member_id" = "member"."id"
2327 JOIN "initiative"
2328 ON "supporter"."initiative_id" = "initiative"."id"
2329 JOIN "current_draft"
2330 ON "initiative"."id" = "current_draft"."initiative_id"
2331 JOIN "direct_interest_snapshot"
2332 ON "member"."id" = "direct_interest_snapshot"."member_id"
2333 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
2334 AND "event" = 'periodic'
2335 WHERE "member"."active"
2336 AND "initiative"."issue_id" = "issue_id_p";
2337 RETURN;
2338 END;
2339 $$;
2341 COMMENT ON FUNCTION "create_interest_snapshot"
2342 ( "issue"."id"%TYPE )
2343 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.';
2346 CREATE FUNCTION "create_snapshot"
2347 ( "issue_id_p" "issue"."id"%TYPE )
2348 RETURNS VOID
2349 LANGUAGE 'plpgsql' VOLATILE AS $$
2350 DECLARE
2351 "initiative_id_v" "initiative"."id"%TYPE;
2352 "suggestion_id_v" "suggestion"."id"%TYPE;
2353 BEGIN
2354 PERFORM "lock_issue"("issue_id_p");
2355 PERFORM "create_population_snapshot"("issue_id_p");
2356 PERFORM "create_interest_snapshot"("issue_id_p");
2357 UPDATE "issue" SET
2358 "snapshot" = now(),
2359 "latest_snapshot_event" = 'periodic',
2360 "population" = (
2361 SELECT coalesce(sum("weight"), 0)
2362 FROM "direct_population_snapshot"
2363 WHERE "issue_id" = "issue_id_p"
2364 AND "event" = 'periodic'
2365 ),
2366 "vote_now" = (
2367 SELECT coalesce(sum("weight"), 0)
2368 FROM "direct_interest_snapshot"
2369 WHERE "issue_id" = "issue_id_p"
2370 AND "event" = 'periodic'
2371 AND "voting_requested" = TRUE
2372 ),
2373 "vote_later" = (
2374 SELECT coalesce(sum("weight"), 0)
2375 FROM "direct_interest_snapshot"
2376 WHERE "issue_id" = "issue_id_p"
2377 AND "event" = 'periodic'
2378 AND "voting_requested" = FALSE
2379 )
2380 WHERE "id" = "issue_id_p";
2381 FOR "initiative_id_v" IN
2382 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
2383 LOOP
2384 UPDATE "initiative" SET
2385 "supporter_count" = (
2386 SELECT coalesce(sum("di"."weight"), 0)
2387 FROM "direct_interest_snapshot" AS "di"
2388 JOIN "direct_supporter_snapshot" AS "ds"
2389 ON "di"."member_id" = "ds"."member_id"
2390 WHERE "di"."issue_id" = "issue_id_p"
2391 AND "di"."event" = 'periodic'
2392 AND "ds"."initiative_id" = "initiative_id_v"
2393 AND "ds"."event" = 'periodic'
2394 ),
2395 "informed_supporter_count" = (
2396 SELECT coalesce(sum("di"."weight"), 0)
2397 FROM "direct_interest_snapshot" AS "di"
2398 JOIN "direct_supporter_snapshot" AS "ds"
2399 ON "di"."member_id" = "ds"."member_id"
2400 WHERE "di"."issue_id" = "issue_id_p"
2401 AND "di"."event" = 'periodic'
2402 AND "ds"."initiative_id" = "initiative_id_v"
2403 AND "ds"."event" = 'periodic'
2404 AND "ds"."informed"
2405 ),
2406 "satisfied_supporter_count" = (
2407 SELECT coalesce(sum("di"."weight"), 0)
2408 FROM "direct_interest_snapshot" AS "di"
2409 JOIN "direct_supporter_snapshot" AS "ds"
2410 ON "di"."member_id" = "ds"."member_id"
2411 WHERE "di"."issue_id" = "issue_id_p"
2412 AND "di"."event" = 'periodic'
2413 AND "ds"."initiative_id" = "initiative_id_v"
2414 AND "ds"."event" = 'periodic'
2415 AND "ds"."satisfied"
2416 ),
2417 "satisfied_informed_supporter_count" = (
2418 SELECT coalesce(sum("di"."weight"), 0)
2419 FROM "direct_interest_snapshot" AS "di"
2420 JOIN "direct_supporter_snapshot" AS "ds"
2421 ON "di"."member_id" = "ds"."member_id"
2422 WHERE "di"."issue_id" = "issue_id_p"
2423 AND "di"."event" = 'periodic'
2424 AND "ds"."initiative_id" = "initiative_id_v"
2425 AND "ds"."event" = 'periodic'
2426 AND "ds"."informed"
2427 AND "ds"."satisfied"
2428 )
2429 WHERE "id" = "initiative_id_v";
2430 FOR "suggestion_id_v" IN
2431 SELECT "id" FROM "suggestion"
2432 WHERE "initiative_id" = "initiative_id_v"
2433 LOOP
2434 UPDATE "suggestion" SET
2435 "minus2_unfulfilled_count" = (
2436 SELECT coalesce(sum("snapshot"."weight"), 0)
2437 FROM "issue" CROSS JOIN "opinion"
2438 JOIN "direct_interest_snapshot" AS "snapshot"
2439 ON "snapshot"."issue_id" = "issue"."id"
2440 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2441 AND "snapshot"."member_id" = "opinion"."member_id"
2442 WHERE "issue"."id" = "issue_id_p"
2443 AND "opinion"."suggestion_id" = "suggestion_id_v"
2444 AND "opinion"."degree" = -2
2445 AND "opinion"."fulfilled" = FALSE
2446 ),
2447 "minus2_fulfilled_count" = (
2448 SELECT coalesce(sum("snapshot"."weight"), 0)
2449 FROM "issue" CROSS JOIN "opinion"
2450 JOIN "direct_interest_snapshot" AS "snapshot"
2451 ON "snapshot"."issue_id" = "issue"."id"
2452 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2453 AND "snapshot"."member_id" = "opinion"."member_id"
2454 WHERE "issue"."id" = "issue_id_p"
2455 AND "opinion"."suggestion_id" = "suggestion_id_v"
2456 AND "opinion"."degree" = -2
2457 AND "opinion"."fulfilled" = TRUE
2458 ),
2459 "minus1_unfulfilled_count" = (
2460 SELECT coalesce(sum("snapshot"."weight"), 0)
2461 FROM "issue" CROSS JOIN "opinion"
2462 JOIN "direct_interest_snapshot" AS "snapshot"
2463 ON "snapshot"."issue_id" = "issue"."id"
2464 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2465 AND "snapshot"."member_id" = "opinion"."member_id"
2466 WHERE "issue"."id" = "issue_id_p"
2467 AND "opinion"."suggestion_id" = "suggestion_id_v"
2468 AND "opinion"."degree" = -1
2469 AND "opinion"."fulfilled" = FALSE
2470 ),
2471 "minus1_fulfilled_count" = (
2472 SELECT coalesce(sum("snapshot"."weight"), 0)
2473 FROM "issue" CROSS JOIN "opinion"
2474 JOIN "direct_interest_snapshot" AS "snapshot"
2475 ON "snapshot"."issue_id" = "issue"."id"
2476 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2477 AND "snapshot"."member_id" = "opinion"."member_id"
2478 WHERE "issue"."id" = "issue_id_p"
2479 AND "opinion"."suggestion_id" = "suggestion_id_v"
2480 AND "opinion"."degree" = -1
2481 AND "opinion"."fulfilled" = TRUE
2482 ),
2483 "plus1_unfulfilled_count" = (
2484 SELECT coalesce(sum("snapshot"."weight"), 0)
2485 FROM "issue" CROSS JOIN "opinion"
2486 JOIN "direct_interest_snapshot" AS "snapshot"
2487 ON "snapshot"."issue_id" = "issue"."id"
2488 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2489 AND "snapshot"."member_id" = "opinion"."member_id"
2490 WHERE "issue"."id" = "issue_id_p"
2491 AND "opinion"."suggestion_id" = "suggestion_id_v"
2492 AND "opinion"."degree" = 1
2493 AND "opinion"."fulfilled" = FALSE
2494 ),
2495 "plus1_fulfilled_count" = (
2496 SELECT coalesce(sum("snapshot"."weight"), 0)
2497 FROM "issue" CROSS JOIN "opinion"
2498 JOIN "direct_interest_snapshot" AS "snapshot"
2499 ON "snapshot"."issue_id" = "issue"."id"
2500 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2501 AND "snapshot"."member_id" = "opinion"."member_id"
2502 WHERE "issue"."id" = "issue_id_p"
2503 AND "opinion"."suggestion_id" = "suggestion_id_v"
2504 AND "opinion"."degree" = 1
2505 AND "opinion"."fulfilled" = TRUE
2506 ),
2507 "plus2_unfulfilled_count" = (
2508 SELECT coalesce(sum("snapshot"."weight"), 0)
2509 FROM "issue" CROSS JOIN "opinion"
2510 JOIN "direct_interest_snapshot" AS "snapshot"
2511 ON "snapshot"."issue_id" = "issue"."id"
2512 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2513 AND "snapshot"."member_id" = "opinion"."member_id"
2514 WHERE "issue"."id" = "issue_id_p"
2515 AND "opinion"."suggestion_id" = "suggestion_id_v"
2516 AND "opinion"."degree" = 2
2517 AND "opinion"."fulfilled" = FALSE
2518 ),
2519 "plus2_fulfilled_count" = (
2520 SELECT coalesce(sum("snapshot"."weight"), 0)
2521 FROM "issue" CROSS JOIN "opinion"
2522 JOIN "direct_interest_snapshot" AS "snapshot"
2523 ON "snapshot"."issue_id" = "issue"."id"
2524 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2525 AND "snapshot"."member_id" = "opinion"."member_id"
2526 WHERE "issue"."id" = "issue_id_p"
2527 AND "opinion"."suggestion_id" = "suggestion_id_v"
2528 AND "opinion"."degree" = 2
2529 AND "opinion"."fulfilled" = TRUE
2530 )
2531 WHERE "suggestion"."id" = "suggestion_id_v";
2532 END LOOP;
2533 END LOOP;
2534 RETURN;
2535 END;
2536 $$;
2538 COMMENT ON FUNCTION "create_snapshot"
2539 ( "issue"."id"%TYPE )
2540 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.';
2543 CREATE FUNCTION "set_snapshot_event"
2544 ( "issue_id_p" "issue"."id"%TYPE,
2545 "event_p" "snapshot_event" )
2546 RETURNS VOID
2547 LANGUAGE 'plpgsql' VOLATILE AS $$
2548 DECLARE
2549 "event_v" "issue"."latest_snapshot_event"%TYPE;
2550 BEGIN
2551 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
2552 WHERE "id" = "issue_id_p" FOR UPDATE;
2553 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
2554 WHERE "id" = "issue_id_p";
2555 UPDATE "direct_population_snapshot" SET "event" = "event_p"
2556 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2557 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
2558 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2559 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
2560 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2561 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
2562 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2563 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
2564 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2565 RETURN;
2566 END;
2567 $$;
2569 COMMENT ON FUNCTION "set_snapshot_event"
2570 ( "issue"."id"%TYPE,
2571 "snapshot_event" )
2572 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
2576 ---------------------
2577 -- Freezing issues --
2578 ---------------------
2580 CREATE FUNCTION "freeze_after_snapshot"
2581 ( "issue_id_p" "issue"."id"%TYPE )
2582 RETURNS VOID
2583 LANGUAGE 'plpgsql' VOLATILE AS $$
2584 DECLARE
2585 "issue_row" "issue"%ROWTYPE;
2586 "policy_row" "policy"%ROWTYPE;
2587 "initiative_row" "initiative"%ROWTYPE;
2588 BEGIN
2589 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2590 SELECT * INTO "policy_row"
2591 FROM "policy" WHERE "id" = "issue_row"."policy_id";
2592 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
2593 UPDATE "issue" SET
2594 "accepted" = coalesce("accepted", now()),
2595 "half_frozen" = coalesce("half_frozen", now()),
2596 "fully_frozen" = now()
2597 WHERE "id" = "issue_id_p";
2598 FOR "initiative_row" IN
2599 SELECT * FROM "initiative"
2600 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
2601 LOOP
2602 IF
2603 "initiative_row"."satisfied_supporter_count" > 0 AND
2604 "initiative_row"."satisfied_supporter_count" *
2605 "policy_row"."initiative_quorum_den" >=
2606 "issue_row"."population" * "policy_row"."initiative_quorum_num"
2607 THEN
2608 UPDATE "initiative" SET "admitted" = TRUE
2609 WHERE "id" = "initiative_row"."id";
2610 ELSE
2611 UPDATE "initiative" SET "admitted" = FALSE
2612 WHERE "id" = "initiative_row"."id";
2613 END IF;
2614 END LOOP;
2615 IF NOT EXISTS (
2616 SELECT NULL FROM "initiative"
2617 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
2618 ) THEN
2619 PERFORM "close_voting"("issue_id_p");
2620 END IF;
2621 RETURN;
2622 END;
2623 $$;
2625 COMMENT ON FUNCTION "freeze_after_snapshot"
2626 ( "issue"."id"%TYPE )
2627 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
2630 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
2631 RETURNS VOID
2632 LANGUAGE 'plpgsql' VOLATILE AS $$
2633 DECLARE
2634 "issue_row" "issue"%ROWTYPE;
2635 BEGIN
2636 PERFORM "create_snapshot"("issue_id_p");
2637 PERFORM "freeze_after_snapshot"("issue_id_p");
2638 RETURN;
2639 END;
2640 $$;
2642 COMMENT ON FUNCTION "manual_freeze"
2643 ( "issue"."id"%TYPE )
2644 IS 'Freeze an issue manually (fully) and start voting';
2648 -----------------------
2649 -- Counting of votes --
2650 -----------------------
2653 CREATE FUNCTION "weight_of_added_vote_delegations"
2654 ( "issue_id_p" "issue"."id"%TYPE,
2655 "member_id_p" "member"."id"%TYPE,
2656 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
2657 RETURNS "direct_voter"."weight"%TYPE
2658 LANGUAGE 'plpgsql' VOLATILE AS $$
2659 DECLARE
2660 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2661 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
2662 "weight_v" INT4;
2663 "sub_weight_v" INT4;
2664 BEGIN
2665 "weight_v" := 0;
2666 FOR "issue_delegation_row" IN
2667 SELECT * FROM "issue_delegation"
2668 WHERE "trustee_id" = "member_id_p"
2669 AND "issue_id" = "issue_id_p"
2670 LOOP
2671 IF NOT EXISTS (
2672 SELECT NULL FROM "direct_voter"
2673 WHERE "member_id" = "issue_delegation_row"."truster_id"
2674 AND "issue_id" = "issue_id_p"
2675 ) AND NOT EXISTS (
2676 SELECT NULL FROM "delegating_voter"
2677 WHERE "member_id" = "issue_delegation_row"."truster_id"
2678 AND "issue_id" = "issue_id_p"
2679 ) THEN
2680 "delegate_member_ids_v" :=
2681 "member_id_p" || "delegate_member_ids_p";
2682 INSERT INTO "delegating_voter" (
2683 "issue_id",
2684 "member_id",
2685 "scope",
2686 "delegate_member_ids"
2687 ) VALUES (
2688 "issue_id_p",
2689 "issue_delegation_row"."truster_id",
2690 "issue_delegation_row"."scope",
2691 "delegate_member_ids_v"
2692 );
2693 "sub_weight_v" := 1 +
2694 "weight_of_added_vote_delegations"(
2695 "issue_id_p",
2696 "issue_delegation_row"."truster_id",
2697 "delegate_member_ids_v"
2698 );
2699 UPDATE "delegating_voter"
2700 SET "weight" = "sub_weight_v"
2701 WHERE "issue_id" = "issue_id_p"
2702 AND "member_id" = "issue_delegation_row"."truster_id";
2703 "weight_v" := "weight_v" + "sub_weight_v";
2704 END IF;
2705 END LOOP;
2706 RETURN "weight_v";
2707 END;
2708 $$;
2710 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
2711 ( "issue"."id"%TYPE,
2712 "member"."id"%TYPE,
2713 "delegating_voter"."delegate_member_ids"%TYPE )
2714 IS 'Helper function for "add_vote_delegations" function';
2717 CREATE FUNCTION "add_vote_delegations"
2718 ( "issue_id_p" "issue"."id"%TYPE )
2719 RETURNS VOID
2720 LANGUAGE 'plpgsql' VOLATILE AS $$
2721 DECLARE
2722 "member_id_v" "member"."id"%TYPE;
2723 BEGIN
2724 FOR "member_id_v" IN
2725 SELECT "member_id" FROM "direct_voter"
2726 WHERE "issue_id" = "issue_id_p"
2727 LOOP
2728 UPDATE "direct_voter" SET
2729 "weight" = "weight" + "weight_of_added_vote_delegations"(
2730 "issue_id_p",
2731 "member_id_v",
2732 '{}'
2733 )
2734 WHERE "member_id" = "member_id_v"
2735 AND "issue_id" = "issue_id_p";
2736 END LOOP;
2737 RETURN;
2738 END;
2739 $$;
2741 COMMENT ON FUNCTION "add_vote_delegations"
2742 ( "issue_id_p" "issue"."id"%TYPE )
2743 IS 'Helper function for "close_voting" function';
2746 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
2747 RETURNS VOID
2748 LANGUAGE 'plpgsql' VOLATILE AS $$
2749 DECLARE
2750 "issue_row" "issue"%ROWTYPE;
2751 "member_id_v" "member"."id"%TYPE;
2752 BEGIN
2753 PERFORM "lock_issue"("issue_id_p");
2754 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2755 DELETE FROM "delegating_voter"
2756 WHERE "issue_id" = "issue_id_p";
2757 DELETE FROM "direct_voter"
2758 WHERE "issue_id" = "issue_id_p"
2759 AND "autoreject" = TRUE;
2760 DELETE FROM "direct_voter" USING "member"
2761 WHERE "direct_voter"."member_id" = "member"."id"
2762 AND "direct_voter"."issue_id" = "issue_id_p"
2763 AND "member"."active" = FALSE;
2764 UPDATE "direct_voter" SET "weight" = 1
2765 WHERE "issue_id" = "issue_id_p";
2766 PERFORM "add_vote_delegations"("issue_id_p");
2767 FOR "member_id_v" IN
2768 SELECT "interest"."member_id"
2769 FROM "interest"
2770 LEFT JOIN "direct_voter"
2771 ON "interest"."member_id" = "direct_voter"."member_id"
2772 AND "interest"."issue_id" = "direct_voter"."issue_id"
2773 LEFT JOIN "delegating_voter"
2774 ON "interest"."member_id" = "delegating_voter"."member_id"
2775 AND "interest"."issue_id" = "delegating_voter"."issue_id"
2776 WHERE "interest"."issue_id" = "issue_id_p"
2777 AND "interest"."autoreject" = TRUE
2778 AND "direct_voter"."member_id" ISNULL
2779 AND "delegating_voter"."member_id" ISNULL
2780 UNION SELECT "membership"."member_id"
2781 FROM "membership"
2782 LEFT JOIN "interest"
2783 ON "membership"."member_id" = "interest"."member_id"
2784 AND "interest"."issue_id" = "issue_id_p"
2785 LEFT JOIN "direct_voter"
2786 ON "membership"."member_id" = "direct_voter"."member_id"
2787 AND "direct_voter"."issue_id" = "issue_id_p"
2788 LEFT JOIN "delegating_voter"
2789 ON "membership"."member_id" = "delegating_voter"."member_id"
2790 AND "delegating_voter"."issue_id" = "issue_id_p"
2791 WHERE "membership"."area_id" = "issue_row"."area_id"
2792 AND "membership"."autoreject" = TRUE
2793 AND "interest"."autoreject" ISNULL
2794 AND "direct_voter"."member_id" ISNULL
2795 AND "delegating_voter"."member_id" ISNULL
2796 LOOP
2797 INSERT INTO "direct_voter"
2798 ("member_id", "issue_id", "weight", "autoreject") VALUES
2799 ("member_id_v", "issue_id_p", 1, TRUE);
2800 INSERT INTO "vote" (
2801 "member_id",
2802 "issue_id",
2803 "initiative_id",
2804 "grade"
2805 ) SELECT
2806 "member_id_v" AS "member_id",
2807 "issue_id_p" AS "issue_id",
2808 "id" AS "initiative_id",
2809 -1 AS "grade"
2810 FROM "initiative" WHERE "issue_id" = "issue_id_p";
2811 END LOOP;
2812 PERFORM "add_vote_delegations"("issue_id_p");
2813 UPDATE "issue" SET
2814 "closed" = now(),
2815 "voter_count" = (
2816 SELECT coalesce(sum("weight"), 0)
2817 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
2818 )
2819 WHERE "id" = "issue_id_p";
2820 UPDATE "initiative" SET
2821 "positive_votes" = "vote_counts"."positive_votes",
2822 "negative_votes" = "vote_counts"."negative_votes",
2823 "agreed" = CASE WHEN "majority_strict" THEN
2824 "vote_counts"."positive_votes" * "majority_den" >
2825 "majority_num" *
2826 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
2827 ELSE
2828 "vote_counts"."positive_votes" * "majority_den" >=
2829 "majority_num" *
2830 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
2831 END
2832 FROM
2833 ( SELECT
2834 "initiative"."id" AS "initiative_id",
2835 coalesce(
2836 sum(
2837 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
2838 ),
2839 0
2840 ) AS "positive_votes",
2841 coalesce(
2842 sum(
2843 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
2844 ),
2845 0
2846 ) AS "negative_votes"
2847 FROM "initiative"
2848 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
2849 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
2850 LEFT JOIN "direct_voter"
2851 ON "direct_voter"."issue_id" = "initiative"."issue_id"
2852 LEFT JOIN "vote"
2853 ON "vote"."initiative_id" = "initiative"."id"
2854 AND "vote"."member_id" = "direct_voter"."member_id"
2855 WHERE "initiative"."issue_id" = "issue_id_p"
2856 AND "initiative"."admitted" -- NOTE: NULL case is handled too
2857 GROUP BY "initiative"."id"
2858 ) AS "vote_counts",
2859 "issue",
2860 "policy"
2861 WHERE "vote_counts"."initiative_id" = "initiative"."id"
2862 AND "issue"."id" = "initiative"."issue_id"
2863 AND "policy"."id" = "issue"."policy_id";
2864 -- NOTE: "closed" column of issue must be set at this point
2865 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
2866 INSERT INTO "battle" (
2867 "issue_id",
2868 "winning_initiative_id", "losing_initiative_id",
2869 "count"
2870 ) SELECT
2871 "issue_id",
2872 "winning_initiative_id", "losing_initiative_id",
2873 "count"
2874 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
2875 END;
2876 $$;
2878 COMMENT ON FUNCTION "close_voting"
2879 ( "issue"."id"%TYPE )
2880 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.';
2883 CREATE FUNCTION "defeat_strength"
2884 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
2885 RETURNS INT8
2886 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2887 BEGIN
2888 IF "positive_votes_p" > "negative_votes_p" THEN
2889 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
2890 ELSIF "positive_votes_p" = "negative_votes_p" THEN
2891 RETURN 0;
2892 ELSE
2893 RETURN -1;
2894 END IF;
2895 END;
2896 $$;
2898 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';
2901 CREATE FUNCTION "array_init_string"("dim_p" INTEGER)
2902 RETURNS TEXT
2903 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2904 DECLARE
2905 "i" INTEGER;
2906 "ary_text_v" TEXT;
2907 BEGIN
2908 IF "dim_p" >= 1 THEN
2909 "ary_text_v" := '{NULL';
2910 "i" := "dim_p";
2911 LOOP
2912 "i" := "i" - 1;
2913 EXIT WHEN "i" = 0;
2914 "ary_text_v" := "ary_text_v" || ',NULL';
2915 END LOOP;
2916 "ary_text_v" := "ary_text_v" || '}';
2917 RETURN "ary_text_v";
2918 ELSE
2919 RAISE EXCEPTION 'Dimension needs to be at least 1.';
2920 END IF;
2921 END;
2922 $$;
2924 COMMENT ON FUNCTION "array_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
2927 CREATE FUNCTION "square_matrix_init_string"("dim_p" INTEGER)
2928 RETURNS TEXT
2929 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2930 DECLARE
2931 "i" INTEGER;
2932 "row_text_v" TEXT;
2933 "ary_text_v" TEXT;
2934 BEGIN
2935 IF "dim_p" >= 1 THEN
2936 "row_text_v" := '{NULL';
2937 "i" := "dim_p";
2938 LOOP
2939 "i" := "i" - 1;
2940 EXIT WHEN "i" = 0;
2941 "row_text_v" := "row_text_v" || ',NULL';
2942 END LOOP;
2943 "row_text_v" := "row_text_v" || '}';
2944 "ary_text_v" := '{' || "row_text_v";
2945 "i" := "dim_p";
2946 LOOP
2947 "i" := "i" - 1;
2948 EXIT WHEN "i" = 0;
2949 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
2950 END LOOP;
2951 "ary_text_v" := "ary_text_v" || '}';
2952 RETURN "ary_text_v";
2953 ELSE
2954 RAISE EXCEPTION 'Dimension needs to be at least 1.';
2955 END IF;
2956 END;
2957 $$;
2959 COMMENT ON FUNCTION "square_matrix_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
2962 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
2963 RETURNS VOID
2964 LANGUAGE 'plpgsql' VOLATILE AS $$
2965 DECLARE
2966 "dimension_v" INTEGER;
2967 "vote_matrix" INT4[][]; -- absolute votes
2968 "matrix" INT8[][]; -- defeat strength / best paths
2969 "i" INTEGER;
2970 "j" INTEGER;
2971 "k" INTEGER;
2972 "battle_row" "battle"%ROWTYPE;
2973 "rank_ary" INT4[];
2974 "rank_v" INT4;
2975 "done_v" INTEGER;
2976 "winners_ary" INTEGER[];
2977 "initiative_id_v" "initiative"."id"%TYPE;
2978 BEGIN
2979 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2980 SELECT count(1) INTO "dimension_v" FROM "initiative"
2981 WHERE "issue_id" = "issue_id_p" AND "agreed";
2982 IF "dimension_v" = 1 THEN
2983 UPDATE "initiative" SET "rank" = 1
2984 WHERE "issue_id" = "issue_id_p" AND "agreed";
2985 ELSIF "dimension_v" > 1 THEN
2986 -- Create "vote_matrix" with absolute number of votes in pairwise
2987 -- comparison:
2988 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2989 "i" := 1;
2990 "j" := 2;
2991 FOR "battle_row" IN
2992 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
2993 ORDER BY "winning_initiative_id", "losing_initiative_id"
2994 LOOP
2995 "vote_matrix"["i"]["j"] := "battle_row"."count";
2996 IF "j" = "dimension_v" THEN
2997 "i" := "i" + 1;
2998 "j" := 1;
2999 ELSE
3000 "j" := "j" + 1;
3001 IF "j" = "i" THEN
3002 "j" := "j" + 1;
3003 END IF;
3004 END IF;
3005 END LOOP;
3006 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
3007 RAISE EXCEPTION 'Wrong battle count (should not happen)';
3008 END IF;
3009 -- Store defeat strengths in "matrix" using "defeat_strength"
3010 -- function:
3011 "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
3012 "i" := 1;
3013 LOOP
3014 "j" := 1;
3015 LOOP
3016 IF "i" != "j" THEN
3017 "matrix"["i"]["j"] := "defeat_strength"(
3018 "vote_matrix"["i"]["j"],
3019 "vote_matrix"["j"]["i"]
3020 );
3021 END IF;
3022 EXIT WHEN "j" = "dimension_v";
3023 "j" := "j" + 1;
3024 END LOOP;
3025 EXIT WHEN "i" = "dimension_v";
3026 "i" := "i" + 1;
3027 END LOOP;
3028 -- Find best paths:
3029 "i" := 1;
3030 LOOP
3031 "j" := 1;
3032 LOOP
3033 IF "i" != "j" THEN
3034 "k" := 1;
3035 LOOP
3036 IF "i" != "k" AND "j" != "k" THEN
3037 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
3038 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
3039 "matrix"["j"]["k"] := "matrix"["j"]["i"];
3040 END IF;
3041 ELSE
3042 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
3043 "matrix"["j"]["k"] := "matrix"["i"]["k"];
3044 END IF;
3045 END IF;
3046 END IF;
3047 EXIT WHEN "k" = "dimension_v";
3048 "k" := "k" + 1;
3049 END LOOP;
3050 END IF;
3051 EXIT WHEN "j" = "dimension_v";
3052 "j" := "j" + 1;
3053 END LOOP;
3054 EXIT WHEN "i" = "dimension_v";
3055 "i" := "i" + 1;
3056 END LOOP;
3057 -- Determine order of winners:
3058 "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
3059 "rank_v" := 1;
3060 "done_v" := 0;
3061 LOOP
3062 "winners_ary" := '{}';
3063 "i" := 1;
3064 LOOP
3065 IF "rank_ary"["i"] ISNULL THEN
3066 "j" := 1;
3067 LOOP
3068 IF
3069 "i" != "j" AND
3070 "rank_ary"["j"] ISNULL AND
3071 "matrix"["j"]["i"] > "matrix"["i"]["j"]
3072 THEN
3073 -- someone else is better
3074 EXIT;
3075 END IF;
3076 IF "j" = "dimension_v" THEN
3077 -- noone is better
3078 "winners_ary" := "winners_ary" || "i";
3079 EXIT;
3080 END IF;
3081 "j" := "j" + 1;
3082 END LOOP;
3083 END IF;
3084 EXIT WHEN "i" = "dimension_v";
3085 "i" := "i" + 1;
3086 END LOOP;
3087 "i" := 1;
3088 LOOP
3089 "rank_ary"["winners_ary"["i"]] := "rank_v";
3090 "done_v" := "done_v" + 1;
3091 EXIT WHEN "i" = array_upper("winners_ary", 1);
3092 "i" := "i" + 1;
3093 END LOOP;
3094 EXIT WHEN "done_v" = "dimension_v";
3095 "rank_v" := "rank_v" + 1;
3096 END LOOP;
3097 -- write preliminary ranks:
3098 "i" := 1;
3099 FOR "initiative_id_v" IN
3100 SELECT "id" FROM "initiative"
3101 WHERE "issue_id" = "issue_id_p" AND "agreed"
3102 ORDER BY "id"
3103 LOOP
3104 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
3105 WHERE "id" = "initiative_id_v";
3106 "i" := "i" + 1;
3107 END LOOP;
3108 IF "i" != "dimension_v" + 1 THEN
3109 RAISE EXCEPTION 'Wrong winner count (should not happen)';
3110 END IF;
3111 -- straighten ranks (start counting with 1, no equal ranks):
3112 "rank_v" := 1;
3113 FOR "initiative_id_v" IN
3114 SELECT "id" FROM "initiative"
3115 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
3116 ORDER BY
3117 "rank",
3118 "vote_ratio"("positive_votes", "negative_votes") DESC,
3119 "id"
3120 LOOP
3121 UPDATE "initiative" SET "rank" = "rank_v"
3122 WHERE "id" = "initiative_id_v";
3123 "rank_v" := "rank_v" + 1;
3124 END LOOP;
3125 END IF;
3126 -- mark issue as finished
3127 UPDATE "issue" SET "ranks_available" = TRUE
3128 WHERE "id" = "issue_id_p";
3129 RETURN;
3130 END;
3131 $$;
3133 COMMENT ON FUNCTION "calculate_ranks"
3134 ( "issue"."id"%TYPE )
3135 IS 'Determine ranking (Votes have to be counted first)';
3139 -----------------------------
3140 -- Automatic state changes --
3141 -----------------------------
3144 CREATE FUNCTION "check_issue"
3145 ( "issue_id_p" "issue"."id"%TYPE )
3146 RETURNS VOID
3147 LANGUAGE 'plpgsql' VOLATILE AS $$
3148 DECLARE
3149 "issue_row" "issue"%ROWTYPE;
3150 "policy_row" "policy"%ROWTYPE;
3151 "voting_requested_v" BOOLEAN;
3152 BEGIN
3153 PERFORM "lock_issue"("issue_id_p");
3154 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3155 -- only process open issues:
3156 IF "issue_row"."closed" ISNULL THEN
3157 SELECT * INTO "policy_row" FROM "policy"
3158 WHERE "id" = "issue_row"."policy_id";
3159 -- create a snapshot, unless issue is already fully frozen:
3160 IF "issue_row"."fully_frozen" ISNULL THEN
3161 PERFORM "create_snapshot"("issue_id_p");
3162 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3163 END IF;
3164 -- eventually close or accept issues, which have not been accepted:
3165 IF "issue_row"."accepted" ISNULL THEN
3166 IF EXISTS (
3167 SELECT NULL FROM "initiative"
3168 WHERE "issue_id" = "issue_id_p"
3169 AND "supporter_count" > 0
3170 AND "supporter_count" * "policy_row"."issue_quorum_den"
3171 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
3172 ) THEN
3173 -- accept issues, if supporter count is high enough
3174 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3175 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later
3176 UPDATE "issue" SET "accepted" = "issue_row"."accepted"
3177 WHERE "id" = "issue_row"."id";
3178 ELSIF
3179 now() >= "issue_row"."created" + "issue_row"."admission_time"
3180 THEN
3181 -- close issues, if admission time has expired
3182 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3183 UPDATE "issue" SET "closed" = now()
3184 WHERE "id" = "issue_row"."id";
3185 END IF;
3186 END IF;
3187 -- eventually half freeze issues:
3188 IF
3189 -- NOTE: issue can't be closed at this point, if it has been accepted
3190 "issue_row"."accepted" NOTNULL AND
3191 "issue_row"."half_frozen" ISNULL
3192 THEN
3193 SELECT
3194 CASE
3195 WHEN "vote_now" * 2 > "issue_row"."population" THEN
3196 TRUE
3197 WHEN "vote_later" * 2 > "issue_row"."population" THEN
3198 FALSE
3199 ELSE NULL
3200 END
3201 INTO "voting_requested_v"
3202 FROM "issue" WHERE "id" = "issue_id_p";
3203 IF
3204 "voting_requested_v" OR (
3205 "voting_requested_v" ISNULL AND
3206 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
3207 )
3208 THEN
3209 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
3210 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later
3211 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
3212 WHERE "id" = "issue_row"."id";
3213 END IF;
3214 END IF;
3215 -- close issues after some time, if all initiatives have been revoked:
3216 IF
3217 "issue_row"."closed" ISNULL AND
3218 NOT EXISTS (
3219 -- all initiatives are revoked
3220 SELECT NULL FROM "initiative"
3221 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3222 ) AND (
3223 NOT EXISTS (
3224 -- and no initiatives have been revoked lately
3225 SELECT NULL FROM "initiative"
3226 WHERE "issue_id" = "issue_id_p"
3227 AND now() < "revoked" + "issue_row"."verification_time"
3228 ) OR (
3229 -- or verification time has elapsed
3230 "issue_row"."half_frozen" NOTNULL AND
3231 "issue_row"."fully_frozen" ISNULL AND
3232 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3233 )
3234 )
3235 THEN
3236 "issue_row"."closed" = now(); -- NOTE: "issue_row" used later
3237 UPDATE "issue" SET "closed" = "issue_row"."closed"
3238 WHERE "id" = "issue_row"."id";
3239 END IF;
3240 -- fully freeze issue after verification time:
3241 IF
3242 "issue_row"."half_frozen" NOTNULL AND
3243 "issue_row"."fully_frozen" ISNULL AND
3244 "issue_row"."closed" ISNULL AND
3245 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3246 THEN
3247 PERFORM "freeze_after_snapshot"("issue_id_p");
3248 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
3249 END IF;
3250 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3251 -- close issue by calling close_voting(...) after voting time:
3252 IF
3253 "issue_row"."closed" ISNULL AND
3254 "issue_row"."fully_frozen" NOTNULL AND
3255 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
3256 THEN
3257 PERFORM "close_voting"("issue_id_p");
3258 END IF;
3259 END IF;
3260 RETURN;
3261 END;
3262 $$;
3264 COMMENT ON FUNCTION "check_issue"
3265 ( "issue"."id"%TYPE )
3266 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.';
3269 CREATE FUNCTION "check_everything"()
3270 RETURNS VOID
3271 LANGUAGE 'plpgsql' VOLATILE AS $$
3272 DECLARE
3273 "issue_id_v" "issue"."id"%TYPE;
3274 BEGIN
3275 DELETE FROM "expired_session";
3276 PERFORM "calculate_member_counts"();
3277 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
3278 PERFORM "check_issue"("issue_id_v");
3279 END LOOP;
3280 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
3281 PERFORM "calculate_ranks"("issue_id_v");
3282 END LOOP;
3283 RETURN;
3284 END;
3285 $$;
3287 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.';
3291 ----------------------
3292 -- Deletion of data --
3293 ----------------------
3296 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
3297 RETURNS VOID
3298 LANGUAGE 'plpgsql' VOLATILE AS $$
3299 DECLARE
3300 "issue_row" "issue"%ROWTYPE;
3301 BEGIN
3302 SELECT * INTO "issue_row"
3303 FROM "issue" WHERE "id" = "issue_id_p"
3304 FOR UPDATE;
3305 IF "issue_row"."cleaned" ISNULL THEN
3306 UPDATE "issue" SET
3307 "closed" = NULL,
3308 "ranks_available" = FALSE
3309 WHERE "id" = "issue_id_p";
3310 DELETE FROM "delegating_voter"
3311 WHERE "issue_id" = "issue_id_p";
3312 DELETE FROM "direct_voter"
3313 WHERE "issue_id" = "issue_id_p";
3314 DELETE FROM "delegating_interest_snapshot"
3315 WHERE "issue_id" = "issue_id_p";
3316 DELETE FROM "direct_interest_snapshot"
3317 WHERE "issue_id" = "issue_id_p";
3318 DELETE FROM "delegating_population_snapshot"
3319 WHERE "issue_id" = "issue_id_p";
3320 DELETE FROM "direct_population_snapshot"
3321 WHERE "issue_id" = "issue_id_p";
3322 DELETE FROM "delegation"
3323 WHERE "issue_id" = "issue_id_p";
3324 DELETE FROM "supporter"
3325 WHERE "issue_id" = "issue_id_p";
3326 UPDATE "issue" SET
3327 "closed" = "issue_row"."closed",
3328 "ranks_available" = "issue_row"."ranks_available",
3329 "cleaned" = now()
3330 WHERE "id" = "issue_id_p";
3331 END IF;
3332 RETURN;
3333 END;
3334 $$;
3336 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
3339 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
3340 RETURNS VOID
3341 LANGUAGE 'plpgsql' VOLATILE AS $$
3342 BEGIN
3343 UPDATE "member" SET
3344 "last_login" = NULL,
3345 "login" = NULL,
3346 "password" = NULL,
3347 "active" = FALSE,
3348 "notify_email" = NULL,
3349 "notify_email_unconfirmed" = NULL,
3350 "notify_email_secret" = NULL,
3351 "notify_email_secret_expiry" = NULL,
3352 "notify_email_lock_expiry" = NULL,
3353 "password_reset_secret" = NULL,
3354 "password_reset_secret_expiry" = NULL,
3355 "organizational_unit" = NULL,
3356 "internal_posts" = NULL,
3357 "realname" = NULL,
3358 "birthday" = NULL,
3359 "address" = NULL,
3360 "email" = NULL,
3361 "xmpp_address" = NULL,
3362 "website" = NULL,
3363 "phone" = NULL,
3364 "mobile_phone" = NULL,
3365 "profession" = NULL,
3366 "external_memberships" = NULL,
3367 "external_posts" = NULL,
3368 "statement" = NULL
3369 WHERE "id" = "member_id_p";
3370 -- "text_search_data" is updated by triggers
3371 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
3372 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
3373 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
3374 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
3375 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
3376 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
3377 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
3378 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
3379 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
3380 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
3381 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
3382 DELETE FROM "direct_voter" USING "issue"
3383 WHERE "direct_voter"."issue_id" = "issue"."id"
3384 AND "issue"."closed" ISNULL
3385 AND "member_id" = "member_id_p";
3386 RETURN;
3387 END;
3388 $$;
3390 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)';
3393 CREATE FUNCTION "delete_private_data"()
3394 RETURNS VOID
3395 LANGUAGE 'plpgsql' VOLATILE AS $$
3396 BEGIN
3397 UPDATE "member" SET
3398 "last_login" = NULL,
3399 "login" = NULL,
3400 "password" = NULL,
3401 "notify_email" = NULL,
3402 "notify_email_unconfirmed" = NULL,
3403 "notify_email_secret" = NULL,
3404 "notify_email_secret_expiry" = NULL,
3405 "notify_email_lock_expiry" = NULL,
3406 "password_reset_secret" = NULL,
3407 "password_reset_secret_expiry" = NULL,
3408 "organizational_unit" = NULL,
3409 "internal_posts" = NULL,
3410 "realname" = NULL,
3411 "birthday" = NULL,
3412 "address" = NULL,
3413 "email" = NULL,
3414 "xmpp_address" = NULL,
3415 "website" = NULL,
3416 "phone" = NULL,
3417 "mobile_phone" = NULL,
3418 "profession" = NULL,
3419 "external_memberships" = NULL,
3420 "external_posts" = NULL,
3421 "statement" = NULL;
3422 -- "text_search_data" is updated by triggers
3423 DELETE FROM "invite_code";
3424 DELETE FROM "setting";
3425 DELETE FROM "setting_map";
3426 DELETE FROM "member_relation_setting";
3427 DELETE FROM "member_image";
3428 DELETE FROM "contact";
3429 DELETE FROM "session";
3430 DELETE FROM "area_setting";
3431 DELETE FROM "issue_setting";
3432 DELETE FROM "initiative_setting";
3433 DELETE FROM "suggestion_setting";
3434 DELETE FROM "direct_voter" USING "issue"
3435 WHERE "direct_voter"."issue_id" = "issue"."id"
3436 AND "issue"."closed" ISNULL;
3437 RETURN;
3438 END;
3439 $$;
3441 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.';
3445 COMMIT;