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