liquid_feedback_core
view core.sql @ 71:7626e290d537
Added tag v1.2.6 for changeset 5745db0b1a34
| author | jbe |
|---|---|
| date | Mon Aug 30 22:45:50 2010 +0200 (2010-08-30) |
| parents | 5745db0b1a34 |
| children | dcaa1525c388 |
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.6', 1, 2, 6))
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", "trustee_id"),
660 UNIQUE ("issue_id", "truster_id", "trustee_id") );
661 CREATE UNIQUE INDEX "delegation_global_truster_id_trustee_id_unique_idx"
662 ON "delegation" ("truster_id", "trustee_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 * FROM "active_delegation" WHERE "scope" = 'global';
1270 COMMENT ON VIEW "global_delegation" IS 'Global delegations from active members';
1273 CREATE VIEW "area_delegation" AS
1274 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1275 "area"."id" AS "area_id",
1276 "delegation"."id",
1277 "delegation"."truster_id",
1278 "delegation"."trustee_id",
1279 "delegation"."scope"
1280 FROM "area" JOIN "active_delegation" AS "delegation"
1281 ON "delegation"."scope" = 'global'
1282 OR "delegation"."area_id" = "area"."id"
1283 ORDER BY
1284 "area"."id",
1285 "delegation"."truster_id",
1286 "delegation"."scope" DESC;
1288 COMMENT ON VIEW "area_delegation" IS 'Resulting area delegations from active members';
1291 CREATE VIEW "issue_delegation" AS
1292 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1293 "issue"."id" AS "issue_id",
1294 "delegation"."id",
1295 "delegation"."truster_id",
1296 "delegation"."trustee_id",
1297 "delegation"."scope"
1298 FROM "issue" JOIN "active_delegation" AS "delegation"
1299 ON "delegation"."scope" = 'global'
1300 OR "delegation"."area_id" = "issue"."area_id"
1301 OR "delegation"."issue_id" = "issue"."id"
1302 ORDER BY
1303 "issue"."id",
1304 "delegation"."truster_id",
1305 "delegation"."scope" DESC;
1307 COMMENT ON VIEW "issue_delegation" IS 'Resulting issue delegations from active members';
1310 CREATE FUNCTION "membership_weight_with_skipping"
1311 ( "area_id_p" "area"."id"%TYPE,
1312 "member_id_p" "member"."id"%TYPE,
1313 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1314 RETURNS INT4
1315 LANGUAGE 'plpgsql' STABLE AS $$
1316 DECLARE
1317 "sum_v" INT4;
1318 "delegation_row" "area_delegation"%ROWTYPE;
1319 BEGIN
1320 "sum_v" := 1;
1321 FOR "delegation_row" IN
1322 SELECT "area_delegation".*
1323 FROM "area_delegation" LEFT JOIN "membership"
1324 ON "membership"."area_id" = "area_id_p"
1325 AND "membership"."member_id" = "area_delegation"."truster_id"
1326 WHERE "area_delegation"."area_id" = "area_id_p"
1327 AND "area_delegation"."trustee_id" = "member_id_p"
1328 AND "membership"."member_id" ISNULL
1329 LOOP
1330 IF NOT
1331 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1332 THEN
1333 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1334 "area_id_p",
1335 "delegation_row"."truster_id",
1336 "skip_member_ids_p" || "delegation_row"."truster_id"
1337 );
1338 END IF;
1339 END LOOP;
1340 RETURN "sum_v";
1341 END;
1342 $$;
1344 COMMENT ON FUNCTION "membership_weight_with_skipping"
1345 ( "area"."id"%TYPE,
1346 "member"."id"%TYPE,
1347 INT4[] )
1348 IS 'Helper function for "membership_weight" function';
1351 CREATE FUNCTION "membership_weight"
1352 ( "area_id_p" "area"."id"%TYPE,
1353 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1354 RETURNS INT4
1355 LANGUAGE 'plpgsql' STABLE AS $$
1356 BEGIN
1357 RETURN "membership_weight_with_skipping"(
1358 "area_id_p",
1359 "member_id_p",
1360 ARRAY["member_id_p"]
1361 );
1362 END;
1363 $$;
1365 COMMENT ON FUNCTION "membership_weight"
1366 ( "area"."id"%TYPE,
1367 "member"."id"%TYPE )
1368 IS 'Calculates the potential voting weight of a member in a given area';
1371 CREATE VIEW "member_count_view" AS
1372 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1374 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1377 CREATE VIEW "area_member_count" AS
1378 SELECT
1379 "area"."id" AS "area_id",
1380 count("member"."id") AS "direct_member_count",
1381 coalesce(
1382 sum(
1383 CASE WHEN "member"."id" NOTNULL THEN
1384 "membership_weight"("area"."id", "member"."id")
1385 ELSE 0 END
1386 )
1387 ) AS "member_weight",
1388 coalesce(
1389 sum(
1390 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
1391 "membership_weight"("area"."id", "member"."id")
1392 ELSE 0 END
1393 )
1394 ) AS "autoreject_weight"
1395 FROM "area"
1396 LEFT JOIN "membership"
1397 ON "area"."id" = "membership"."area_id"
1398 LEFT JOIN "member"
1399 ON "membership"."member_id" = "member"."id"
1400 AND "member"."active"
1401 GROUP BY "area"."id";
1403 COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"';
1406 CREATE VIEW "opening_draft" AS
1407 SELECT "draft".* FROM (
1408 SELECT
1409 "initiative"."id" AS "initiative_id",
1410 min("draft"."id") AS "draft_id"
1411 FROM "initiative" JOIN "draft"
1412 ON "initiative"."id" = "draft"."initiative_id"
1413 GROUP BY "initiative"."id"
1414 ) AS "subquery"
1415 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1417 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1420 CREATE VIEW "current_draft" AS
1421 SELECT "draft".* FROM (
1422 SELECT
1423 "initiative"."id" AS "initiative_id",
1424 max("draft"."id") AS "draft_id"
1425 FROM "initiative" JOIN "draft"
1426 ON "initiative"."id" = "draft"."initiative_id"
1427 GROUP BY "initiative"."id"
1428 ) AS "subquery"
1429 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1431 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1434 CREATE VIEW "critical_opinion" AS
1435 SELECT * FROM "opinion"
1436 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
1437 OR ("degree" = -2 AND "fulfilled" = TRUE);
1439 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
1442 CREATE VIEW "battle_view" AS
1443 SELECT
1444 "issue"."id" AS "issue_id",
1445 "winning_initiative"."id" AS "winning_initiative_id",
1446 "losing_initiative"."id" AS "losing_initiative_id",
1447 sum(
1448 CASE WHEN
1449 coalesce("better_vote"."grade", 0) >
1450 coalesce("worse_vote"."grade", 0)
1451 THEN "direct_voter"."weight" ELSE 0 END
1452 ) AS "count"
1453 FROM "issue"
1454 LEFT JOIN "direct_voter"
1455 ON "issue"."id" = "direct_voter"."issue_id"
1456 JOIN "initiative" AS "winning_initiative"
1457 ON "issue"."id" = "winning_initiative"."issue_id"
1458 AND "winning_initiative"."agreed"
1459 JOIN "initiative" AS "losing_initiative"
1460 ON "issue"."id" = "losing_initiative"."issue_id"
1461 AND "losing_initiative"."agreed"
1462 LEFT JOIN "vote" AS "better_vote"
1463 ON "direct_voter"."member_id" = "better_vote"."member_id"
1464 AND "winning_initiative"."id" = "better_vote"."initiative_id"
1465 LEFT JOIN "vote" AS "worse_vote"
1466 ON "direct_voter"."member_id" = "worse_vote"."member_id"
1467 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
1468 WHERE "issue"."closed" NOTNULL
1469 AND "issue"."cleaned" ISNULL
1470 AND "winning_initiative"."id" != "losing_initiative"."id"
1471 GROUP BY
1472 "issue"."id",
1473 "winning_initiative"."id",
1474 "losing_initiative"."id";
1476 COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative to another; Used to fill "battle" table';
1479 CREATE VIEW "expired_session" AS
1480 SELECT * FROM "session" WHERE now() > "expiry";
1482 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
1483 DELETE FROM "session" WHERE "ident" = OLD."ident";
1485 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
1486 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
1489 CREATE VIEW "open_issue" AS
1490 SELECT * FROM "issue" WHERE "closed" ISNULL;
1492 COMMENT ON VIEW "open_issue" IS 'All open issues';
1495 CREATE VIEW "issue_with_ranks_missing" AS
1496 SELECT * FROM "issue"
1497 WHERE "fully_frozen" NOTNULL
1498 AND "closed" NOTNULL
1499 AND "ranks_available" = FALSE;
1501 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
1504 CREATE VIEW "member_contingent" AS
1505 SELECT
1506 "member"."id" AS "member_id",
1507 "contingent"."time_frame",
1508 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
1509 (
1510 SELECT count(1) FROM "draft"
1511 WHERE "draft"."author_id" = "member"."id"
1512 AND "draft"."created" > now() - "contingent"."time_frame"
1513 ) + (
1514 SELECT count(1) FROM "suggestion"
1515 WHERE "suggestion"."author_id" = "member"."id"
1516 AND "suggestion"."created" > now() - "contingent"."time_frame"
1517 )
1518 ELSE NULL END AS "text_entry_count",
1519 "contingent"."text_entry_limit",
1520 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
1521 SELECT count(1) FROM "opening_draft"
1522 WHERE "opening_draft"."author_id" = "member"."id"
1523 AND "opening_draft"."created" > now() - "contingent"."time_frame"
1524 ) ELSE NULL END AS "initiative_count",
1525 "contingent"."initiative_limit"
1526 FROM "member" CROSS JOIN "contingent";
1528 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
1530 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
1531 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
1534 CREATE VIEW "member_contingent_left" AS
1535 SELECT
1536 "member_id",
1537 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
1538 max("initiative_limit" - "initiative_count") AS "initiatives_left"
1539 FROM "member_contingent" GROUP BY "member_id";
1541 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.';
1544 CREATE TYPE "timeline_event" AS ENUM (
1545 'issue_created',
1546 'issue_canceled',
1547 'issue_accepted',
1548 'issue_half_frozen',
1549 'issue_finished_without_voting',
1550 'issue_voting_started',
1551 'issue_finished_after_voting',
1552 'initiative_created',
1553 'initiative_revoked',
1554 'draft_created',
1555 'suggestion_created');
1557 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables';
1560 CREATE VIEW "timeline_issue" AS
1561 SELECT
1562 "created" AS "occurrence",
1563 'issue_created'::"timeline_event" AS "event",
1564 "id" AS "issue_id"
1565 FROM "issue"
1566 UNION ALL
1567 SELECT
1568 "closed" AS "occurrence",
1569 'issue_canceled'::"timeline_event" AS "event",
1570 "id" AS "issue_id"
1571 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
1572 UNION ALL
1573 SELECT
1574 "accepted" AS "occurrence",
1575 'issue_accepted'::"timeline_event" AS "event",
1576 "id" AS "issue_id"
1577 FROM "issue" WHERE "accepted" NOTNULL
1578 UNION ALL
1579 SELECT
1580 "half_frozen" AS "occurrence",
1581 'issue_half_frozen'::"timeline_event" AS "event",
1582 "id" AS "issue_id"
1583 FROM "issue" WHERE "half_frozen" NOTNULL
1584 UNION ALL
1585 SELECT
1586 "fully_frozen" AS "occurrence",
1587 'issue_voting_started'::"timeline_event" AS "event",
1588 "id" AS "issue_id"
1589 FROM "issue"
1590 WHERE "fully_frozen" NOTNULL
1591 AND ("closed" ISNULL OR "closed" != "fully_frozen")
1592 UNION ALL
1593 SELECT
1594 "closed" AS "occurrence",
1595 CASE WHEN "fully_frozen" = "closed" THEN
1596 'issue_finished_without_voting'::"timeline_event"
1597 ELSE
1598 'issue_finished_after_voting'::"timeline_event"
1599 END AS "event",
1600 "id" AS "issue_id"
1601 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
1603 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view';
1606 CREATE VIEW "timeline_initiative" AS
1607 SELECT
1608 "created" AS "occurrence",
1609 'initiative_created'::"timeline_event" AS "event",
1610 "id" AS "initiative_id"
1611 FROM "initiative"
1612 UNION ALL
1613 SELECT
1614 "revoked" AS "occurrence",
1615 'initiative_revoked'::"timeline_event" AS "event",
1616 "id" AS "initiative_id"
1617 FROM "initiative" WHERE "revoked" NOTNULL;
1619 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view';
1622 CREATE VIEW "timeline_draft" AS
1623 SELECT
1624 "created" AS "occurrence",
1625 'draft_created'::"timeline_event" AS "event",
1626 "id" AS "draft_id"
1627 FROM "draft";
1629 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view';
1632 CREATE VIEW "timeline_suggestion" AS
1633 SELECT
1634 "created" AS "occurrence",
1635 'suggestion_created'::"timeline_event" AS "event",
1636 "id" AS "suggestion_id"
1637 FROM "suggestion";
1639 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view';
1642 CREATE VIEW "timeline" AS
1643 SELECT
1644 "occurrence",
1645 "event",
1646 "issue_id",
1647 NULL AS "initiative_id",
1648 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
1649 NULL::INT8 AS "suggestion_id"
1650 FROM "timeline_issue"
1651 UNION ALL
1652 SELECT
1653 "occurrence",
1654 "event",
1655 NULL AS "issue_id",
1656 "initiative_id",
1657 NULL AS "draft_id",
1658 NULL AS "suggestion_id"
1659 FROM "timeline_initiative"
1660 UNION ALL
1661 SELECT
1662 "occurrence",
1663 "event",
1664 NULL AS "issue_id",
1665 NULL AS "initiative_id",
1666 "draft_id",
1667 NULL AS "suggestion_id"
1668 FROM "timeline_draft"
1669 UNION ALL
1670 SELECT
1671 "occurrence",
1672 "event",
1673 NULL AS "issue_id",
1674 NULL AS "initiative_id",
1675 NULL AS "draft_id",
1676 "suggestion_id"
1677 FROM "timeline_suggestion";
1679 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system';
1683 --------------------------------------------------
1684 -- Set returning function for delegation chains --
1685 --------------------------------------------------
1688 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
1689 ('first', 'intermediate', 'last', 'repetition');
1691 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
1694 CREATE TYPE "delegation_chain_row" AS (
1695 "index" INT4,
1696 "member_id" INT4,
1697 "member_active" BOOLEAN,
1698 "participation" BOOLEAN,
1699 "overridden" BOOLEAN,
1700 "scope_in" "delegation_scope",
1701 "scope_out" "delegation_scope",
1702 "loop" "delegation_chain_loop_tag" );
1704 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
1706 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
1707 COMMENT ON COLUMN "delegation_chain_row"."participation" IS 'In case of delegation chains for issues: interest, for areas: membership, for global delegation chains: always null';
1708 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
1709 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
1710 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
1711 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
1714 CREATE FUNCTION "delegation_chain"
1715 ( "member_id_p" "member"."id"%TYPE,
1716 "area_id_p" "area"."id"%TYPE,
1717 "issue_id_p" "issue"."id"%TYPE,
1718 "simulate_trustee_id_p" "member"."id"%TYPE )
1719 RETURNS SETOF "delegation_chain_row"
1720 LANGUAGE 'plpgsql' STABLE AS $$
1721 DECLARE
1722 "issue_row" "issue"%ROWTYPE;
1723 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
1724 "loop_member_id_v" "member"."id"%TYPE;
1725 "output_row" "delegation_chain_row";
1726 "output_rows" "delegation_chain_row"[];
1727 "delegation_row" "delegation"%ROWTYPE;
1728 "row_count" INT4;
1729 "i" INT4;
1730 "loop_v" BOOLEAN;
1731 BEGIN
1732 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1733 "visited_member_ids" := '{}';
1734 "loop_member_id_v" := NULL;
1735 "output_rows" := '{}';
1736 "output_row"."index" := 0;
1737 "output_row"."member_id" := "member_id_p";
1738 "output_row"."member_active" := TRUE;
1739 "output_row"."participation" := FALSE;
1740 "output_row"."overridden" := FALSE;
1741 "output_row"."scope_out" := NULL;
1742 LOOP
1743 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
1744 "loop_member_id_v" := "output_row"."member_id";
1745 ELSE
1746 "visited_member_ids" :=
1747 "visited_member_ids" || "output_row"."member_id";
1748 END IF;
1749 IF "output_row"."participation" THEN
1750 "output_row"."overridden" := TRUE;
1751 END IF;
1752 "output_row"."scope_in" := "output_row"."scope_out";
1753 IF EXISTS (
1754 SELECT NULL FROM "member"
1755 WHERE "id" = "output_row"."member_id" AND "active"
1756 ) THEN
1757 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
1758 SELECT * INTO "delegation_row" FROM "delegation"
1759 WHERE "truster_id" = "output_row"."member_id"
1760 AND "scope" = 'global';
1761 ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN
1762 "output_row"."participation" := EXISTS (
1763 SELECT NULL FROM "membership"
1764 WHERE "area_id" = "area_id_p"
1765 AND "member_id" = "output_row"."member_id"
1766 );
1767 SELECT * INTO "delegation_row" FROM "delegation"
1768 WHERE "truster_id" = "output_row"."member_id"
1769 AND ("scope" = 'global' OR "area_id" = "area_id_p")
1770 ORDER BY "scope" DESC;
1771 ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN
1772 "output_row"."participation" := EXISTS (
1773 SELECT NULL FROM "interest"
1774 WHERE "issue_id" = "issue_id_p"
1775 AND "member_id" = "output_row"."member_id"
1776 );
1777 SELECT * INTO "delegation_row" FROM "delegation"
1778 WHERE "truster_id" = "output_row"."member_id"
1779 AND ("scope" = 'global' OR
1780 "area_id" = "issue_row"."area_id" OR
1781 "issue_id" = "issue_id_p"
1782 )
1783 ORDER BY "scope" DESC;
1784 ELSE
1785 RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.';
1786 END IF;
1787 ELSE
1788 "output_row"."member_active" := FALSE;
1789 "output_row"."participation" := FALSE;
1790 "output_row"."scope_out" := NULL;
1791 "delegation_row" := ROW(NULL);
1792 END IF;
1793 IF
1794 "output_row"."member_id" = "member_id_p" AND
1795 "simulate_trustee_id_p" NOTNULL
1796 THEN
1797 "output_row"."scope_out" := CASE
1798 WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global'
1799 WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area'
1800 WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue'
1801 END;
1802 "output_rows" := "output_rows" || "output_row";
1803 "output_row"."member_id" := "simulate_trustee_id_p";
1804 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
1805 "output_row"."scope_out" := "delegation_row"."scope";
1806 "output_rows" := "output_rows" || "output_row";
1807 "output_row"."member_id" := "delegation_row"."trustee_id";
1808 ELSE
1809 "output_row"."scope_out" := NULL;
1810 "output_rows" := "output_rows" || "output_row";
1811 EXIT;
1812 END IF;
1813 EXIT WHEN "loop_member_id_v" NOTNULL;
1814 "output_row"."index" := "output_row"."index" + 1;
1815 END LOOP;
1816 "row_count" := array_upper("output_rows", 1);
1817 "i" := 1;
1818 "loop_v" := FALSE;
1819 LOOP
1820 "output_row" := "output_rows"["i"];
1821 EXIT WHEN "output_row"."member_id" ISNULL;
1822 IF "loop_v" THEN
1823 IF "i" + 1 = "row_count" THEN
1824 "output_row"."loop" := 'last';
1825 ELSIF "i" = "row_count" THEN
1826 "output_row"."loop" := 'repetition';
1827 ELSE
1828 "output_row"."loop" := 'intermediate';
1829 END IF;
1830 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
1831 "output_row"."loop" := 'first';
1832 "loop_v" := TRUE;
1833 END IF;
1834 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
1835 "output_row"."participation" := NULL;
1836 END IF;
1837 RETURN NEXT "output_row";
1838 "i" := "i" + 1;
1839 END LOOP;
1840 RETURN;
1841 END;
1842 $$;
1844 COMMENT ON FUNCTION "delegation_chain"
1845 ( "member"."id"%TYPE,
1846 "area"."id"%TYPE,
1847 "issue"."id"%TYPE,
1848 "member"."id"%TYPE )
1849 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
1851 CREATE FUNCTION "delegation_chain"
1852 ( "member_id_p" "member"."id"%TYPE,
1853 "area_id_p" "area"."id"%TYPE,
1854 "issue_id_p" "issue"."id"%TYPE )
1855 RETURNS SETOF "delegation_chain_row"
1856 LANGUAGE 'plpgsql' STABLE AS $$
1857 DECLARE
1858 "result_row" "delegation_chain_row";
1859 BEGIN
1860 FOR "result_row" IN
1861 SELECT * FROM "delegation_chain"(
1862 "member_id_p", "area_id_p", "issue_id_p", NULL
1863 )
1864 LOOP
1865 RETURN NEXT "result_row";
1866 END LOOP;
1867 RETURN;
1868 END;
1869 $$;
1871 COMMENT ON FUNCTION "delegation_chain"
1872 ( "member"."id"%TYPE,
1873 "area"."id"%TYPE,
1874 "issue"."id"%TYPE )
1875 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
1879 ------------------------------
1880 -- Comparison by vote count --
1881 ------------------------------
1883 CREATE FUNCTION "vote_ratio"
1884 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
1885 "negative_votes_p" "initiative"."negative_votes"%TYPE )
1886 RETURNS FLOAT8
1887 LANGUAGE 'plpgsql' STABLE AS $$
1888 BEGIN
1889 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
1890 RETURN
1891 "positive_votes_p"::FLOAT8 /
1892 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
1893 ELSIF "positive_votes_p" > 0 THEN
1894 RETURN "positive_votes_p";
1895 ELSIF "negative_votes_p" > 0 THEN
1896 RETURN 1 - "negative_votes_p";
1897 ELSE
1898 RETURN 0.5;
1899 END IF;
1900 END;
1901 $$;
1903 COMMENT ON FUNCTION "vote_ratio"
1904 ( "initiative"."positive_votes"%TYPE,
1905 "initiative"."negative_votes"%TYPE )
1906 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.';
1910 ------------------------------------------------
1911 -- Locking for snapshots and voting procedure --
1912 ------------------------------------------------
1915 CREATE FUNCTION "share_row_lock_issue_trigger"()
1916 RETURNS TRIGGER
1917 LANGUAGE 'plpgsql' VOLATILE AS $$
1918 BEGIN
1919 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
1920 PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;
1921 END IF;
1922 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1923 PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;
1924 RETURN NEW;
1925 ELSE
1926 RETURN OLD;
1927 END IF;
1928 END;
1929 $$;
1931 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';
1934 CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()
1935 RETURNS TRIGGER
1936 LANGUAGE 'plpgsql' VOLATILE AS $$
1937 BEGIN
1938 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
1939 PERFORM NULL FROM "issue"
1940 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
1941 WHERE "initiative"."id" = OLD."initiative_id"
1942 FOR SHARE OF "issue";
1943 END IF;
1944 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
1945 PERFORM NULL FROM "issue"
1946 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
1947 WHERE "initiative"."id" = NEW."initiative_id"
1948 FOR SHARE OF "issue";
1949 RETURN NEW;
1950 ELSE
1951 RETURN OLD;
1952 END IF;
1953 END;
1954 $$;
1956 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';
1959 CREATE TRIGGER "share_row_lock_issue"
1960 BEFORE INSERT OR UPDATE OR DELETE ON "initiative"
1961 FOR EACH ROW EXECUTE PROCEDURE
1962 "share_row_lock_issue_trigger"();
1964 CREATE TRIGGER "share_row_lock_issue"
1965 BEFORE INSERT OR UPDATE OR DELETE ON "interest"
1966 FOR EACH ROW EXECUTE PROCEDURE
1967 "share_row_lock_issue_trigger"();
1969 CREATE TRIGGER "share_row_lock_issue"
1970 BEFORE INSERT OR UPDATE OR DELETE ON "supporter"
1971 FOR EACH ROW EXECUTE PROCEDURE
1972 "share_row_lock_issue_trigger"();
1974 CREATE TRIGGER "share_row_lock_issue_via_initiative"
1975 BEFORE INSERT OR UPDATE OR DELETE ON "opinion"
1976 FOR EACH ROW EXECUTE PROCEDURE
1977 "share_row_lock_issue_via_initiative_trigger"();
1979 CREATE TRIGGER "share_row_lock_issue"
1980 BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter"
1981 FOR EACH ROW EXECUTE PROCEDURE
1982 "share_row_lock_issue_trigger"();
1984 CREATE TRIGGER "share_row_lock_issue"
1985 BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter"
1986 FOR EACH ROW EXECUTE PROCEDURE
1987 "share_row_lock_issue_trigger"();
1989 CREATE TRIGGER "share_row_lock_issue"
1990 BEFORE INSERT OR UPDATE OR DELETE ON "vote"
1991 FOR EACH ROW EXECUTE PROCEDURE
1992 "share_row_lock_issue_trigger"();
1994 COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function';
1995 COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function';
1996 COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function';
1997 COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function';
1998 COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function';
1999 COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function';
2000 COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function';
2003 CREATE FUNCTION "lock_issue"
2004 ( "issue_id_p" "issue"."id"%TYPE )
2005 RETURNS VOID
2006 LANGUAGE 'plpgsql' VOLATILE AS $$
2007 BEGIN
2008 LOCK TABLE "member" IN SHARE MODE;
2009 LOCK TABLE "membership" IN SHARE MODE;
2010 LOCK TABLE "policy" IN SHARE MODE;
2011 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2012 -- NOTE: The row-level exclusive lock in combination with the
2013 -- share_row_lock_issue(_via_initiative)_trigger functions (which
2014 -- acquire a row-level share lock on the issue) ensure that no data
2015 -- is changed, which could affect calculation of snapshots or
2016 -- counting of votes. Table "delegation" must be table-level-locked,
2017 -- as it also contains issue- and global-scope delegations.
2018 LOCK TABLE "delegation" IN SHARE MODE;
2019 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
2020 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
2021 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
2022 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
2023 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
2024 RETURN;
2025 END;
2026 $$;
2028 COMMENT ON FUNCTION "lock_issue"
2029 ( "issue"."id"%TYPE )
2030 IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';
2034 -------------------------------
2035 -- Materialize member counts --
2036 -------------------------------
2038 CREATE FUNCTION "calculate_member_counts"()
2039 RETURNS VOID
2040 LANGUAGE 'plpgsql' VOLATILE AS $$
2041 BEGIN
2042 LOCK TABLE "member" IN SHARE MODE;
2043 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
2044 LOCK TABLE "area" IN EXCLUSIVE MODE;
2045 LOCK TABLE "membership" IN SHARE MODE;
2046 DELETE FROM "member_count";
2047 INSERT INTO "member_count" ("total_count")
2048 SELECT "total_count" FROM "member_count_view";
2049 UPDATE "area" SET
2050 "direct_member_count" = "view"."direct_member_count",
2051 "member_weight" = "view"."member_weight",
2052 "autoreject_weight" = "view"."autoreject_weight"
2053 FROM "area_member_count" AS "view"
2054 WHERE "view"."area_id" = "area"."id";
2055 RETURN;
2056 END;
2057 $$;
2059 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"';
2063 ------------------------------
2064 -- Calculation of snapshots --
2065 ------------------------------
2067 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
2068 ( "issue_id_p" "issue"."id"%TYPE,
2069 "member_id_p" "member"."id"%TYPE,
2070 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2071 RETURNS "direct_population_snapshot"."weight"%TYPE
2072 LANGUAGE 'plpgsql' VOLATILE AS $$
2073 DECLARE
2074 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2075 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
2076 "weight_v" INT4;
2077 "sub_weight_v" INT4;
2078 BEGIN
2079 "weight_v" := 0;
2080 FOR "issue_delegation_row" IN
2081 SELECT * FROM "issue_delegation"
2082 WHERE "trustee_id" = "member_id_p"
2083 AND "issue_id" = "issue_id_p"
2084 LOOP
2085 IF NOT EXISTS (
2086 SELECT NULL FROM "direct_population_snapshot"
2087 WHERE "issue_id" = "issue_id_p"
2088 AND "event" = 'periodic'
2089 AND "member_id" = "issue_delegation_row"."truster_id"
2090 ) AND NOT EXISTS (
2091 SELECT NULL FROM "delegating_population_snapshot"
2092 WHERE "issue_id" = "issue_id_p"
2093 AND "event" = 'periodic'
2094 AND "member_id" = "issue_delegation_row"."truster_id"
2095 ) THEN
2096 "delegate_member_ids_v" :=
2097 "member_id_p" || "delegate_member_ids_p";
2098 INSERT INTO "delegating_population_snapshot" (
2099 "issue_id",
2100 "event",
2101 "member_id",
2102 "scope",
2103 "delegate_member_ids"
2104 ) VALUES (
2105 "issue_id_p",
2106 'periodic',
2107 "issue_delegation_row"."truster_id",
2108 "issue_delegation_row"."scope",
2109 "delegate_member_ids_v"
2110 );
2111 "sub_weight_v" := 1 +
2112 "weight_of_added_delegations_for_population_snapshot"(
2113 "issue_id_p",
2114 "issue_delegation_row"."truster_id",
2115 "delegate_member_ids_v"
2116 );
2117 UPDATE "delegating_population_snapshot"
2118 SET "weight" = "sub_weight_v"
2119 WHERE "issue_id" = "issue_id_p"
2120 AND "event" = 'periodic'
2121 AND "member_id" = "issue_delegation_row"."truster_id";
2122 "weight_v" := "weight_v" + "sub_weight_v";
2123 END IF;
2124 END LOOP;
2125 RETURN "weight_v";
2126 END;
2127 $$;
2129 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
2130 ( "issue"."id"%TYPE,
2131 "member"."id"%TYPE,
2132 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2133 IS 'Helper function for "create_population_snapshot" function';
2136 CREATE FUNCTION "create_population_snapshot"
2137 ( "issue_id_p" "issue"."id"%TYPE )
2138 RETURNS VOID
2139 LANGUAGE 'plpgsql' VOLATILE AS $$
2140 DECLARE
2141 "member_id_v" "member"."id"%TYPE;
2142 BEGIN
2143 DELETE FROM "direct_population_snapshot"
2144 WHERE "issue_id" = "issue_id_p"
2145 AND "event" = 'periodic';
2146 DELETE FROM "delegating_population_snapshot"
2147 WHERE "issue_id" = "issue_id_p"
2148 AND "event" = 'periodic';
2149 INSERT INTO "direct_population_snapshot"
2150 ("issue_id", "event", "member_id")
2151 SELECT
2152 "issue_id_p" AS "issue_id",
2153 'periodic'::"snapshot_event" AS "event",
2154 "member"."id" AS "member_id"
2155 FROM "issue"
2156 JOIN "area" ON "issue"."area_id" = "area"."id"
2157 JOIN "membership" ON "area"."id" = "membership"."area_id"
2158 JOIN "member" ON "membership"."member_id" = "member"."id"
2159 WHERE "issue"."id" = "issue_id_p"
2160 AND "member"."active"
2161 UNION
2162 SELECT
2163 "issue_id_p" AS "issue_id",
2164 'periodic'::"snapshot_event" AS "event",
2165 "member"."id" AS "member_id"
2166 FROM "interest" JOIN "member"
2167 ON "interest"."member_id" = "member"."id"
2168 WHERE "interest"."issue_id" = "issue_id_p"
2169 AND "member"."active";
2170 FOR "member_id_v" IN
2171 SELECT "member_id" FROM "direct_population_snapshot"
2172 WHERE "issue_id" = "issue_id_p"
2173 AND "event" = 'periodic'
2174 LOOP
2175 UPDATE "direct_population_snapshot" SET
2176 "weight" = 1 +
2177 "weight_of_added_delegations_for_population_snapshot"(
2178 "issue_id_p",
2179 "member_id_v",
2180 '{}'
2181 )
2182 WHERE "issue_id" = "issue_id_p"
2183 AND "event" = 'periodic'
2184 AND "member_id" = "member_id_v";
2185 END LOOP;
2186 RETURN;
2187 END;
2188 $$;
2190 COMMENT ON FUNCTION "create_population_snapshot"
2191 ( "issue"."id"%TYPE )
2192 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.';
2195 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2196 ( "issue_id_p" "issue"."id"%TYPE,
2197 "member_id_p" "member"."id"%TYPE,
2198 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2199 RETURNS "direct_interest_snapshot"."weight"%TYPE
2200 LANGUAGE 'plpgsql' VOLATILE AS $$
2201 DECLARE
2202 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2203 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2204 "weight_v" INT4;
2205 "sub_weight_v" INT4;
2206 BEGIN
2207 "weight_v" := 0;
2208 FOR "issue_delegation_row" IN
2209 SELECT * FROM "issue_delegation"
2210 WHERE "trustee_id" = "member_id_p"
2211 AND "issue_id" = "issue_id_p"
2212 LOOP
2213 IF NOT EXISTS (
2214 SELECT NULL FROM "direct_interest_snapshot"
2215 WHERE "issue_id" = "issue_id_p"
2216 AND "event" = 'periodic'
2217 AND "member_id" = "issue_delegation_row"."truster_id"
2218 ) AND NOT EXISTS (
2219 SELECT NULL FROM "delegating_interest_snapshot"
2220 WHERE "issue_id" = "issue_id_p"
2221 AND "event" = 'periodic'
2222 AND "member_id" = "issue_delegation_row"."truster_id"
2223 ) THEN
2224 "delegate_member_ids_v" :=
2225 "member_id_p" || "delegate_member_ids_p";
2226 INSERT INTO "delegating_interest_snapshot" (
2227 "issue_id",
2228 "event",
2229 "member_id",
2230 "scope",
2231 "delegate_member_ids"
2232 ) VALUES (
2233 "issue_id_p",
2234 'periodic',
2235 "issue_delegation_row"."truster_id",
2236 "issue_delegation_row"."scope",
2237 "delegate_member_ids_v"
2238 );
2239 "sub_weight_v" := 1 +
2240 "weight_of_added_delegations_for_interest_snapshot"(
2241 "issue_id_p",
2242 "issue_delegation_row"."truster_id",
2243 "delegate_member_ids_v"
2244 );
2245 UPDATE "delegating_interest_snapshot"
2246 SET "weight" = "sub_weight_v"
2247 WHERE "issue_id" = "issue_id_p"
2248 AND "event" = 'periodic'
2249 AND "member_id" = "issue_delegation_row"."truster_id";
2250 "weight_v" := "weight_v" + "sub_weight_v";
2251 END IF;
2252 END LOOP;
2253 RETURN "weight_v";
2254 END;
2255 $$;
2257 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2258 ( "issue"."id"%TYPE,
2259 "member"."id"%TYPE,
2260 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2261 IS 'Helper function for "create_interest_snapshot" function';
2264 CREATE FUNCTION "create_interest_snapshot"
2265 ( "issue_id_p" "issue"."id"%TYPE )
2266 RETURNS VOID
2267 LANGUAGE 'plpgsql' VOLATILE AS $$
2268 DECLARE
2269 "member_id_v" "member"."id"%TYPE;
2270 BEGIN
2271 DELETE FROM "direct_interest_snapshot"
2272 WHERE "issue_id" = "issue_id_p"
2273 AND "event" = 'periodic';
2274 DELETE FROM "delegating_interest_snapshot"
2275 WHERE "issue_id" = "issue_id_p"
2276 AND "event" = 'periodic';
2277 DELETE FROM "direct_supporter_snapshot"
2278 WHERE "issue_id" = "issue_id_p"
2279 AND "event" = 'periodic';
2280 INSERT INTO "direct_interest_snapshot"
2281 ("issue_id", "event", "member_id", "voting_requested")
2282 SELECT
2283 "issue_id_p" AS "issue_id",
2284 'periodic' AS "event",
2285 "member"."id" AS "member_id",
2286 "interest"."voting_requested"
2287 FROM "interest" JOIN "member"
2288 ON "interest"."member_id" = "member"."id"
2289 WHERE "interest"."issue_id" = "issue_id_p"
2290 AND "member"."active";
2291 FOR "member_id_v" IN
2292 SELECT "member_id" FROM "direct_interest_snapshot"
2293 WHERE "issue_id" = "issue_id_p"
2294 AND "event" = 'periodic'
2295 LOOP
2296 UPDATE "direct_interest_snapshot" SET
2297 "weight" = 1 +
2298 "weight_of_added_delegations_for_interest_snapshot"(
2299 "issue_id_p",
2300 "member_id_v",
2301 '{}'
2302 )
2303 WHERE "issue_id" = "issue_id_p"
2304 AND "event" = 'periodic'
2305 AND "member_id" = "member_id_v";
2306 END LOOP;
2307 INSERT INTO "direct_supporter_snapshot"
2308 ( "issue_id", "initiative_id", "event", "member_id",
2309 "informed", "satisfied" )
2310 SELECT
2311 "issue_id_p" AS "issue_id",
2312 "initiative"."id" AS "initiative_id",
2313 'periodic' AS "event",
2314 "member"."id" AS "member_id",
2315 "supporter"."draft_id" = "current_draft"."id" AS "informed",
2316 NOT EXISTS (
2317 SELECT NULL FROM "critical_opinion"
2318 WHERE "initiative_id" = "initiative"."id"
2319 AND "member_id" = "member"."id"
2320 ) AS "satisfied"
2321 FROM "supporter"
2322 JOIN "member"
2323 ON "supporter"."member_id" = "member"."id"
2324 JOIN "initiative"
2325 ON "supporter"."initiative_id" = "initiative"."id"
2326 JOIN "current_draft"
2327 ON "initiative"."id" = "current_draft"."initiative_id"
2328 JOIN "direct_interest_snapshot"
2329 ON "member"."id" = "direct_interest_snapshot"."member_id"
2330 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
2331 AND "event" = 'periodic'
2332 WHERE "member"."active"
2333 AND "initiative"."issue_id" = "issue_id_p";
2334 RETURN;
2335 END;
2336 $$;
2338 COMMENT ON FUNCTION "create_interest_snapshot"
2339 ( "issue"."id"%TYPE )
2340 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.';
2343 CREATE FUNCTION "create_snapshot"
2344 ( "issue_id_p" "issue"."id"%TYPE )
2345 RETURNS VOID
2346 LANGUAGE 'plpgsql' VOLATILE AS $$
2347 DECLARE
2348 "initiative_id_v" "initiative"."id"%TYPE;
2349 "suggestion_id_v" "suggestion"."id"%TYPE;
2350 BEGIN
2351 PERFORM "lock_issue"("issue_id_p");
2352 PERFORM "create_population_snapshot"("issue_id_p");
2353 PERFORM "create_interest_snapshot"("issue_id_p");
2354 UPDATE "issue" SET
2355 "snapshot" = now(),
2356 "latest_snapshot_event" = 'periodic',
2357 "population" = (
2358 SELECT coalesce(sum("weight"), 0)
2359 FROM "direct_population_snapshot"
2360 WHERE "issue_id" = "issue_id_p"
2361 AND "event" = 'periodic'
2362 ),
2363 "vote_now" = (
2364 SELECT coalesce(sum("weight"), 0)
2365 FROM "direct_interest_snapshot"
2366 WHERE "issue_id" = "issue_id_p"
2367 AND "event" = 'periodic'
2368 AND "voting_requested" = TRUE
2369 ),
2370 "vote_later" = (
2371 SELECT coalesce(sum("weight"), 0)
2372 FROM "direct_interest_snapshot"
2373 WHERE "issue_id" = "issue_id_p"
2374 AND "event" = 'periodic'
2375 AND "voting_requested" = FALSE
2376 )
2377 WHERE "id" = "issue_id_p";
2378 FOR "initiative_id_v" IN
2379 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
2380 LOOP
2381 UPDATE "initiative" SET
2382 "supporter_count" = (
2383 SELECT coalesce(sum("di"."weight"), 0)
2384 FROM "direct_interest_snapshot" AS "di"
2385 JOIN "direct_supporter_snapshot" AS "ds"
2386 ON "di"."member_id" = "ds"."member_id"
2387 WHERE "di"."issue_id" = "issue_id_p"
2388 AND "di"."event" = 'periodic'
2389 AND "ds"."initiative_id" = "initiative_id_v"
2390 AND "ds"."event" = 'periodic'
2391 ),
2392 "informed_supporter_count" = (
2393 SELECT coalesce(sum("di"."weight"), 0)
2394 FROM "direct_interest_snapshot" AS "di"
2395 JOIN "direct_supporter_snapshot" AS "ds"
2396 ON "di"."member_id" = "ds"."member_id"
2397 WHERE "di"."issue_id" = "issue_id_p"
2398 AND "di"."event" = 'periodic'
2399 AND "ds"."initiative_id" = "initiative_id_v"
2400 AND "ds"."event" = 'periodic'
2401 AND "ds"."informed"
2402 ),
2403 "satisfied_supporter_count" = (
2404 SELECT coalesce(sum("di"."weight"), 0)
2405 FROM "direct_interest_snapshot" AS "di"
2406 JOIN "direct_supporter_snapshot" AS "ds"
2407 ON "di"."member_id" = "ds"."member_id"
2408 WHERE "di"."issue_id" = "issue_id_p"
2409 AND "di"."event" = 'periodic'
2410 AND "ds"."initiative_id" = "initiative_id_v"
2411 AND "ds"."event" = 'periodic'
2412 AND "ds"."satisfied"
2413 ),
2414 "satisfied_informed_supporter_count" = (
2415 SELECT coalesce(sum("di"."weight"), 0)
2416 FROM "direct_interest_snapshot" AS "di"
2417 JOIN "direct_supporter_snapshot" AS "ds"
2418 ON "di"."member_id" = "ds"."member_id"
2419 WHERE "di"."issue_id" = "issue_id_p"
2420 AND "di"."event" = 'periodic'
2421 AND "ds"."initiative_id" = "initiative_id_v"
2422 AND "ds"."event" = 'periodic'
2423 AND "ds"."informed"
2424 AND "ds"."satisfied"
2425 )
2426 WHERE "id" = "initiative_id_v";
2427 FOR "suggestion_id_v" IN
2428 SELECT "id" FROM "suggestion"
2429 WHERE "initiative_id" = "initiative_id_v"
2430 LOOP
2431 UPDATE "suggestion" SET
2432 "minus2_unfulfilled_count" = (
2433 SELECT coalesce(sum("snapshot"."weight"), 0)
2434 FROM "issue" CROSS JOIN "opinion"
2435 JOIN "direct_interest_snapshot" AS "snapshot"
2436 ON "snapshot"."issue_id" = "issue"."id"
2437 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2438 AND "snapshot"."member_id" = "opinion"."member_id"
2439 WHERE "issue"."id" = "issue_id_p"
2440 AND "opinion"."suggestion_id" = "suggestion_id_v"
2441 AND "opinion"."degree" = -2
2442 AND "opinion"."fulfilled" = FALSE
2443 ),
2444 "minus2_fulfilled_count" = (
2445 SELECT coalesce(sum("snapshot"."weight"), 0)
2446 FROM "issue" CROSS JOIN "opinion"
2447 JOIN "direct_interest_snapshot" AS "snapshot"
2448 ON "snapshot"."issue_id" = "issue"."id"
2449 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2450 AND "snapshot"."member_id" = "opinion"."member_id"
2451 WHERE "issue"."id" = "issue_id_p"
2452 AND "opinion"."suggestion_id" = "suggestion_id_v"
2453 AND "opinion"."degree" = -2
2454 AND "opinion"."fulfilled" = TRUE
2455 ),
2456 "minus1_unfulfilled_count" = (
2457 SELECT coalesce(sum("snapshot"."weight"), 0)
2458 FROM "issue" CROSS JOIN "opinion"
2459 JOIN "direct_interest_snapshot" AS "snapshot"
2460 ON "snapshot"."issue_id" = "issue"."id"
2461 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2462 AND "snapshot"."member_id" = "opinion"."member_id"
2463 WHERE "issue"."id" = "issue_id_p"
2464 AND "opinion"."suggestion_id" = "suggestion_id_v"
2465 AND "opinion"."degree" = -1
2466 AND "opinion"."fulfilled" = FALSE
2467 ),
2468 "minus1_fulfilled_count" = (
2469 SELECT coalesce(sum("snapshot"."weight"), 0)
2470 FROM "issue" CROSS JOIN "opinion"
2471 JOIN "direct_interest_snapshot" AS "snapshot"
2472 ON "snapshot"."issue_id" = "issue"."id"
2473 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2474 AND "snapshot"."member_id" = "opinion"."member_id"
2475 WHERE "issue"."id" = "issue_id_p"
2476 AND "opinion"."suggestion_id" = "suggestion_id_v"
2477 AND "opinion"."degree" = -1
2478 AND "opinion"."fulfilled" = TRUE
2479 ),
2480 "plus1_unfulfilled_count" = (
2481 SELECT coalesce(sum("snapshot"."weight"), 0)
2482 FROM "issue" CROSS JOIN "opinion"
2483 JOIN "direct_interest_snapshot" AS "snapshot"
2484 ON "snapshot"."issue_id" = "issue"."id"
2485 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2486 AND "snapshot"."member_id" = "opinion"."member_id"
2487 WHERE "issue"."id" = "issue_id_p"
2488 AND "opinion"."suggestion_id" = "suggestion_id_v"
2489 AND "opinion"."degree" = 1
2490 AND "opinion"."fulfilled" = FALSE
2491 ),
2492 "plus1_fulfilled_count" = (
2493 SELECT coalesce(sum("snapshot"."weight"), 0)
2494 FROM "issue" CROSS JOIN "opinion"
2495 JOIN "direct_interest_snapshot" AS "snapshot"
2496 ON "snapshot"."issue_id" = "issue"."id"
2497 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2498 AND "snapshot"."member_id" = "opinion"."member_id"
2499 WHERE "issue"."id" = "issue_id_p"
2500 AND "opinion"."suggestion_id" = "suggestion_id_v"
2501 AND "opinion"."degree" = 1
2502 AND "opinion"."fulfilled" = TRUE
2503 ),
2504 "plus2_unfulfilled_count" = (
2505 SELECT coalesce(sum("snapshot"."weight"), 0)
2506 FROM "issue" CROSS JOIN "opinion"
2507 JOIN "direct_interest_snapshot" AS "snapshot"
2508 ON "snapshot"."issue_id" = "issue"."id"
2509 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2510 AND "snapshot"."member_id" = "opinion"."member_id"
2511 WHERE "issue"."id" = "issue_id_p"
2512 AND "opinion"."suggestion_id" = "suggestion_id_v"
2513 AND "opinion"."degree" = 2
2514 AND "opinion"."fulfilled" = FALSE
2515 ),
2516 "plus2_fulfilled_count" = (
2517 SELECT coalesce(sum("snapshot"."weight"), 0)
2518 FROM "issue" CROSS JOIN "opinion"
2519 JOIN "direct_interest_snapshot" AS "snapshot"
2520 ON "snapshot"."issue_id" = "issue"."id"
2521 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2522 AND "snapshot"."member_id" = "opinion"."member_id"
2523 WHERE "issue"."id" = "issue_id_p"
2524 AND "opinion"."suggestion_id" = "suggestion_id_v"
2525 AND "opinion"."degree" = 2
2526 AND "opinion"."fulfilled" = TRUE
2527 )
2528 WHERE "suggestion"."id" = "suggestion_id_v";
2529 END LOOP;
2530 END LOOP;
2531 RETURN;
2532 END;
2533 $$;
2535 COMMENT ON FUNCTION "create_snapshot"
2536 ( "issue"."id"%TYPE )
2537 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.';
2540 CREATE FUNCTION "set_snapshot_event"
2541 ( "issue_id_p" "issue"."id"%TYPE,
2542 "event_p" "snapshot_event" )
2543 RETURNS VOID
2544 LANGUAGE 'plpgsql' VOLATILE AS $$
2545 DECLARE
2546 "event_v" "issue"."latest_snapshot_event"%TYPE;
2547 BEGIN
2548 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
2549 WHERE "id" = "issue_id_p" FOR UPDATE;
2550 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
2551 WHERE "id" = "issue_id_p";
2552 UPDATE "direct_population_snapshot" SET "event" = "event_p"
2553 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2554 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
2555 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2556 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
2557 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2558 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
2559 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2560 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
2561 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2562 RETURN;
2563 END;
2564 $$;
2566 COMMENT ON FUNCTION "set_snapshot_event"
2567 ( "issue"."id"%TYPE,
2568 "snapshot_event" )
2569 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
2573 ---------------------
2574 -- Freezing issues --
2575 ---------------------
2577 CREATE FUNCTION "freeze_after_snapshot"
2578 ( "issue_id_p" "issue"."id"%TYPE )
2579 RETURNS VOID
2580 LANGUAGE 'plpgsql' VOLATILE AS $$
2581 DECLARE
2582 "issue_row" "issue"%ROWTYPE;
2583 "policy_row" "policy"%ROWTYPE;
2584 "initiative_row" "initiative"%ROWTYPE;
2585 BEGIN
2586 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2587 SELECT * INTO "policy_row"
2588 FROM "policy" WHERE "id" = "issue_row"."policy_id";
2589 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
2590 UPDATE "issue" SET
2591 "accepted" = coalesce("accepted", now()),
2592 "half_frozen" = coalesce("half_frozen", now()),
2593 "fully_frozen" = now()
2594 WHERE "id" = "issue_id_p";
2595 FOR "initiative_row" IN
2596 SELECT * FROM "initiative"
2597 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
2598 LOOP
2599 IF
2600 "initiative_row"."satisfied_supporter_count" > 0 AND
2601 "initiative_row"."satisfied_supporter_count" *
2602 "policy_row"."initiative_quorum_den" >=
2603 "issue_row"."population" * "policy_row"."initiative_quorum_num"
2604 THEN
2605 UPDATE "initiative" SET "admitted" = TRUE
2606 WHERE "id" = "initiative_row"."id";
2607 ELSE
2608 UPDATE "initiative" SET "admitted" = FALSE
2609 WHERE "id" = "initiative_row"."id";
2610 END IF;
2611 END LOOP;
2612 IF NOT EXISTS (
2613 SELECT NULL FROM "initiative"
2614 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
2615 ) THEN
2616 PERFORM "close_voting"("issue_id_p");
2617 END IF;
2618 RETURN;
2619 END;
2620 $$;
2622 COMMENT ON FUNCTION "freeze_after_snapshot"
2623 ( "issue"."id"%TYPE )
2624 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
2627 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
2628 RETURNS VOID
2629 LANGUAGE 'plpgsql' VOLATILE AS $$
2630 DECLARE
2631 "issue_row" "issue"%ROWTYPE;
2632 BEGIN
2633 PERFORM "create_snapshot"("issue_id_p");
2634 PERFORM "freeze_after_snapshot"("issue_id_p");
2635 RETURN;
2636 END;
2637 $$;
2639 COMMENT ON FUNCTION "manual_freeze"
2640 ( "issue"."id"%TYPE )
2641 IS 'Freeze an issue manually (fully) and start voting';
2645 -----------------------
2646 -- Counting of votes --
2647 -----------------------
2650 CREATE FUNCTION "weight_of_added_vote_delegations"
2651 ( "issue_id_p" "issue"."id"%TYPE,
2652 "member_id_p" "member"."id"%TYPE,
2653 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
2654 RETURNS "direct_voter"."weight"%TYPE
2655 LANGUAGE 'plpgsql' VOLATILE AS $$
2656 DECLARE
2657 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2658 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
2659 "weight_v" INT4;
2660 "sub_weight_v" INT4;
2661 BEGIN
2662 "weight_v" := 0;
2663 FOR "issue_delegation_row" IN
2664 SELECT * FROM "issue_delegation"
2665 WHERE "trustee_id" = "member_id_p"
2666 AND "issue_id" = "issue_id_p"
2667 LOOP
2668 IF NOT EXISTS (
2669 SELECT NULL FROM "direct_voter"
2670 WHERE "member_id" = "issue_delegation_row"."truster_id"
2671 AND "issue_id" = "issue_id_p"
2672 ) AND NOT EXISTS (
2673 SELECT NULL FROM "delegating_voter"
2674 WHERE "member_id" = "issue_delegation_row"."truster_id"
2675 AND "issue_id" = "issue_id_p"
2676 ) THEN
2677 "delegate_member_ids_v" :=
2678 "member_id_p" || "delegate_member_ids_p";
2679 INSERT INTO "delegating_voter" (
2680 "issue_id",
2681 "member_id",
2682 "scope",
2683 "delegate_member_ids"
2684 ) VALUES (
2685 "issue_id_p",
2686 "issue_delegation_row"."truster_id",
2687 "issue_delegation_row"."scope",
2688 "delegate_member_ids_v"
2689 );
2690 "sub_weight_v" := 1 +
2691 "weight_of_added_vote_delegations"(
2692 "issue_id_p",
2693 "issue_delegation_row"."truster_id",
2694 "delegate_member_ids_v"
2695 );
2696 UPDATE "delegating_voter"
2697 SET "weight" = "sub_weight_v"
2698 WHERE "issue_id" = "issue_id_p"
2699 AND "member_id" = "issue_delegation_row"."truster_id";
2700 "weight_v" := "weight_v" + "sub_weight_v";
2701 END IF;
2702 END LOOP;
2703 RETURN "weight_v";
2704 END;
2705 $$;
2707 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
2708 ( "issue"."id"%TYPE,
2709 "member"."id"%TYPE,
2710 "delegating_voter"."delegate_member_ids"%TYPE )
2711 IS 'Helper function for "add_vote_delegations" function';
2714 CREATE FUNCTION "add_vote_delegations"
2715 ( "issue_id_p" "issue"."id"%TYPE )
2716 RETURNS VOID
2717 LANGUAGE 'plpgsql' VOLATILE AS $$
2718 DECLARE
2719 "member_id_v" "member"."id"%TYPE;
2720 BEGIN
2721 FOR "member_id_v" IN
2722 SELECT "member_id" FROM "direct_voter"
2723 WHERE "issue_id" = "issue_id_p"
2724 LOOP
2725 UPDATE "direct_voter" SET
2726 "weight" = "weight" + "weight_of_added_vote_delegations"(
2727 "issue_id_p",
2728 "member_id_v",
2729 '{}'
2730 )
2731 WHERE "member_id" = "member_id_v"
2732 AND "issue_id" = "issue_id_p";
2733 END LOOP;
2734 RETURN;
2735 END;
2736 $$;
2738 COMMENT ON FUNCTION "add_vote_delegations"
2739 ( "issue_id_p" "issue"."id"%TYPE )
2740 IS 'Helper function for "close_voting" function';
2743 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
2744 RETURNS VOID
2745 LANGUAGE 'plpgsql' VOLATILE AS $$
2746 DECLARE
2747 "issue_row" "issue"%ROWTYPE;
2748 "member_id_v" "member"."id"%TYPE;
2749 BEGIN
2750 PERFORM "lock_issue"("issue_id_p");
2751 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2752 DELETE FROM "delegating_voter"
2753 WHERE "issue_id" = "issue_id_p";
2754 DELETE FROM "direct_voter"
2755 WHERE "issue_id" = "issue_id_p"
2756 AND "autoreject" = TRUE;
2757 DELETE FROM "direct_voter" USING "member"
2758 WHERE "direct_voter"."member_id" = "member"."id"
2759 AND "direct_voter"."issue_id" = "issue_id_p"
2760 AND "member"."active" = FALSE;
2761 UPDATE "direct_voter" SET "weight" = 1
2762 WHERE "issue_id" = "issue_id_p";
2763 PERFORM "add_vote_delegations"("issue_id_p");
2764 FOR "member_id_v" IN
2765 SELECT "interest"."member_id"
2766 FROM "interest"
2767 LEFT JOIN "direct_voter"
2768 ON "interest"."member_id" = "direct_voter"."member_id"
2769 AND "interest"."issue_id" = "direct_voter"."issue_id"
2770 LEFT JOIN "delegating_voter"
2771 ON "interest"."member_id" = "delegating_voter"."member_id"
2772 AND "interest"."issue_id" = "delegating_voter"."issue_id"
2773 WHERE "interest"."issue_id" = "issue_id_p"
2774 AND "interest"."autoreject" = TRUE
2775 AND "direct_voter"."member_id" ISNULL
2776 AND "delegating_voter"."member_id" ISNULL
2777 UNION SELECT "membership"."member_id"
2778 FROM "membership"
2779 LEFT JOIN "interest"
2780 ON "membership"."member_id" = "interest"."member_id"
2781 AND "interest"."issue_id" = "issue_id_p"
2782 LEFT JOIN "direct_voter"
2783 ON "membership"."member_id" = "direct_voter"."member_id"
2784 AND "direct_voter"."issue_id" = "issue_id_p"
2785 LEFT JOIN "delegating_voter"
2786 ON "membership"."member_id" = "delegating_voter"."member_id"
2787 AND "delegating_voter"."issue_id" = "issue_id_p"
2788 WHERE "membership"."area_id" = "issue_row"."area_id"
2789 AND "membership"."autoreject" = TRUE
2790 AND "interest"."autoreject" ISNULL
2791 AND "direct_voter"."member_id" ISNULL
2792 AND "delegating_voter"."member_id" ISNULL
2793 LOOP
2794 INSERT INTO "direct_voter"
2795 ("member_id", "issue_id", "weight", "autoreject") VALUES
2796 ("member_id_v", "issue_id_p", 1, TRUE);
2797 INSERT INTO "vote" (
2798 "member_id",
2799 "issue_id",
2800 "initiative_id",
2801 "grade"
2802 ) SELECT
2803 "member_id_v" AS "member_id",
2804 "issue_id_p" AS "issue_id",
2805 "id" AS "initiative_id",
2806 -1 AS "grade"
2807 FROM "initiative" WHERE "issue_id" = "issue_id_p";
2808 END LOOP;
2809 PERFORM "add_vote_delegations"("issue_id_p");
2810 UPDATE "issue" SET
2811 "closed" = now(),
2812 "voter_count" = (
2813 SELECT coalesce(sum("weight"), 0)
2814 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
2815 )
2816 WHERE "id" = "issue_id_p";
2817 UPDATE "initiative" SET
2818 "positive_votes" = "vote_counts"."positive_votes",
2819 "negative_votes" = "vote_counts"."negative_votes",
2820 "agreed" = CASE WHEN "majority_strict" THEN
2821 "vote_counts"."positive_votes" * "majority_den" >
2822 "majority_num" *
2823 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
2824 ELSE
2825 "vote_counts"."positive_votes" * "majority_den" >=
2826 "majority_num" *
2827 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
2828 END
2829 FROM
2830 ( SELECT
2831 "initiative"."id" AS "initiative_id",
2832 coalesce(
2833 sum(
2834 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
2835 ),
2836 0
2837 ) AS "positive_votes",
2838 coalesce(
2839 sum(
2840 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
2841 ),
2842 0
2843 ) AS "negative_votes"
2844 FROM "initiative"
2845 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
2846 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
2847 LEFT JOIN "direct_voter"
2848 ON "direct_voter"."issue_id" = "initiative"."issue_id"
2849 LEFT JOIN "vote"
2850 ON "vote"."initiative_id" = "initiative"."id"
2851 AND "vote"."member_id" = "direct_voter"."member_id"
2852 WHERE "initiative"."issue_id" = "issue_id_p"
2853 AND "initiative"."admitted" -- NOTE: NULL case is handled too
2854 GROUP BY "initiative"."id"
2855 ) AS "vote_counts",
2856 "issue",
2857 "policy"
2858 WHERE "vote_counts"."initiative_id" = "initiative"."id"
2859 AND "issue"."id" = "initiative"."issue_id"
2860 AND "policy"."id" = "issue"."policy_id";
2861 -- NOTE: "closed" column of issue must be set at this point
2862 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
2863 INSERT INTO "battle" (
2864 "issue_id",
2865 "winning_initiative_id", "losing_initiative_id",
2866 "count"
2867 ) SELECT
2868 "issue_id",
2869 "winning_initiative_id", "losing_initiative_id",
2870 "count"
2871 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
2872 END;
2873 $$;
2875 COMMENT ON FUNCTION "close_voting"
2876 ( "issue"."id"%TYPE )
2877 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.';
2880 CREATE FUNCTION "defeat_strength"
2881 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
2882 RETURNS INT8
2883 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2884 BEGIN
2885 IF "positive_votes_p" > "negative_votes_p" THEN
2886 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
2887 ELSIF "positive_votes_p" = "negative_votes_p" THEN
2888 RETURN 0;
2889 ELSE
2890 RETURN -1;
2891 END IF;
2892 END;
2893 $$;
2895 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';
2898 CREATE FUNCTION "array_init_string"("dim_p" INTEGER)
2899 RETURNS TEXT
2900 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2901 DECLARE
2902 "i" INTEGER;
2903 "ary_text_v" TEXT;
2904 BEGIN
2905 IF "dim_p" >= 1 THEN
2906 "ary_text_v" := '{NULL';
2907 "i" := "dim_p";
2908 LOOP
2909 "i" := "i" - 1;
2910 EXIT WHEN "i" = 0;
2911 "ary_text_v" := "ary_text_v" || ',NULL';
2912 END LOOP;
2913 "ary_text_v" := "ary_text_v" || '}';
2914 RETURN "ary_text_v";
2915 ELSE
2916 RAISE EXCEPTION 'Dimension needs to be at least 1.';
2917 END IF;
2918 END;
2919 $$;
2921 COMMENT ON FUNCTION "array_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
2924 CREATE FUNCTION "square_matrix_init_string"("dim_p" INTEGER)
2925 RETURNS TEXT
2926 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2927 DECLARE
2928 "i" INTEGER;
2929 "row_text_v" TEXT;
2930 "ary_text_v" TEXT;
2931 BEGIN
2932 IF "dim_p" >= 1 THEN
2933 "row_text_v" := '{NULL';
2934 "i" := "dim_p";
2935 LOOP
2936 "i" := "i" - 1;
2937 EXIT WHEN "i" = 0;
2938 "row_text_v" := "row_text_v" || ',NULL';
2939 END LOOP;
2940 "row_text_v" := "row_text_v" || '}';
2941 "ary_text_v" := '{' || "row_text_v";
2942 "i" := "dim_p";
2943 LOOP
2944 "i" := "i" - 1;
2945 EXIT WHEN "i" = 0;
2946 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
2947 END LOOP;
2948 "ary_text_v" := "ary_text_v" || '}';
2949 RETURN "ary_text_v";
2950 ELSE
2951 RAISE EXCEPTION 'Dimension needs to be at least 1.';
2952 END IF;
2953 END;
2954 $$;
2956 COMMENT ON FUNCTION "square_matrix_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
2959 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
2960 RETURNS VOID
2961 LANGUAGE 'plpgsql' VOLATILE AS $$
2962 DECLARE
2963 "dimension_v" INTEGER;
2964 "vote_matrix" INT4[][]; -- absolute votes
2965 "matrix" INT8[][]; -- defeat strength / best paths
2966 "i" INTEGER;
2967 "j" INTEGER;
2968 "k" INTEGER;
2969 "battle_row" "battle"%ROWTYPE;
2970 "rank_ary" INT4[];
2971 "rank_v" INT4;
2972 "done_v" INTEGER;
2973 "winners_ary" INTEGER[];
2974 "initiative_id_v" "initiative"."id"%TYPE;
2975 BEGIN
2976 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2977 SELECT count(1) INTO "dimension_v" FROM "initiative"
2978 WHERE "issue_id" = "issue_id_p" AND "agreed";
2979 IF "dimension_v" = 1 THEN
2980 UPDATE "initiative" SET "rank" = 1
2981 WHERE "issue_id" = "issue_id_p" AND "agreed";
2982 ELSIF "dimension_v" > 1 THEN
2983 -- Create "vote_matrix" with absolute number of votes in pairwise
2984 -- comparison:
2985 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2986 "i" := 1;
2987 "j" := 2;
2988 FOR "battle_row" IN
2989 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
2990 ORDER BY "winning_initiative_id", "losing_initiative_id"
2991 LOOP
2992 "vote_matrix"["i"]["j"] := "battle_row"."count";
2993 IF "j" = "dimension_v" THEN
2994 "i" := "i" + 1;
2995 "j" := 1;
2996 ELSE
2997 "j" := "j" + 1;
2998 IF "j" = "i" THEN
2999 "j" := "j" + 1;
3000 END IF;
3001 END IF;
3002 END LOOP;
3003 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
3004 RAISE EXCEPTION 'Wrong battle count (should not happen)';
3005 END IF;
3006 -- Store defeat strengths in "matrix" using "defeat_strength"
3007 -- function:
3008 "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
3009 "i" := 1;
3010 LOOP
3011 "j" := 1;
3012 LOOP
3013 IF "i" != "j" THEN
3014 "matrix"["i"]["j"] := "defeat_strength"(
3015 "vote_matrix"["i"]["j"],
3016 "vote_matrix"["j"]["i"]
3017 );
3018 END IF;
3019 EXIT WHEN "j" = "dimension_v";
3020 "j" := "j" + 1;
3021 END LOOP;
3022 EXIT WHEN "i" = "dimension_v";
3023 "i" := "i" + 1;
3024 END LOOP;
3025 -- Find best paths:
3026 "i" := 1;
3027 LOOP
3028 "j" := 1;
3029 LOOP
3030 IF "i" != "j" THEN
3031 "k" := 1;
3032 LOOP
3033 IF "i" != "k" AND "j" != "k" THEN
3034 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
3035 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
3036 "matrix"["j"]["k"] := "matrix"["j"]["i"];
3037 END IF;
3038 ELSE
3039 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
3040 "matrix"["j"]["k"] := "matrix"["i"]["k"];
3041 END IF;
3042 END IF;
3043 END IF;
3044 EXIT WHEN "k" = "dimension_v";
3045 "k" := "k" + 1;
3046 END LOOP;
3047 END IF;
3048 EXIT WHEN "j" = "dimension_v";
3049 "j" := "j" + 1;
3050 END LOOP;
3051 EXIT WHEN "i" = "dimension_v";
3052 "i" := "i" + 1;
3053 END LOOP;
3054 -- Determine order of winners:
3055 "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
3056 "rank_v" := 1;
3057 "done_v" := 0;
3058 LOOP
3059 "winners_ary" := '{}';
3060 "i" := 1;
3061 LOOP
3062 IF "rank_ary"["i"] ISNULL THEN
3063 "j" := 1;
3064 LOOP
3065 IF
3066 "i" != "j" AND
3067 "rank_ary"["j"] ISNULL AND
3068 "matrix"["j"]["i"] > "matrix"["i"]["j"]
3069 THEN
3070 -- someone else is better
3071 EXIT;
3072 END IF;
3073 IF "j" = "dimension_v" THEN
3074 -- noone is better
3075 "winners_ary" := "winners_ary" || "i";
3076 EXIT;
3077 END IF;
3078 "j" := "j" + 1;
3079 END LOOP;
3080 END IF;
3081 EXIT WHEN "i" = "dimension_v";
3082 "i" := "i" + 1;
3083 END LOOP;
3084 "i" := 1;
3085 LOOP
3086 "rank_ary"["winners_ary"["i"]] := "rank_v";
3087 "done_v" := "done_v" + 1;
3088 EXIT WHEN "i" = array_upper("winners_ary", 1);
3089 "i" := "i" + 1;
3090 END LOOP;
3091 EXIT WHEN "done_v" = "dimension_v";
3092 "rank_v" := "rank_v" + 1;
3093 END LOOP;
3094 -- write preliminary ranks:
3095 "i" := 1;
3096 FOR "initiative_id_v" IN
3097 SELECT "id" FROM "initiative"
3098 WHERE "issue_id" = "issue_id_p" AND "agreed"
3099 ORDER BY "id"
3100 LOOP
3101 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
3102 WHERE "id" = "initiative_id_v";
3103 "i" := "i" + 1;
3104 END LOOP;
3105 IF "i" != "dimension_v" + 1 THEN
3106 RAISE EXCEPTION 'Wrong winner count (should not happen)';
3107 END IF;
3108 -- straighten ranks (start counting with 1, no equal ranks):
3109 "rank_v" := 1;
3110 FOR "initiative_id_v" IN
3111 SELECT "id" FROM "initiative"
3112 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
3113 ORDER BY
3114 "rank",
3115 "vote_ratio"("positive_votes", "negative_votes") DESC,
3116 "id"
3117 LOOP
3118 UPDATE "initiative" SET "rank" = "rank_v"
3119 WHERE "id" = "initiative_id_v";
3120 "rank_v" := "rank_v" + 1;
3121 END LOOP;
3122 END IF;
3123 -- mark issue as finished
3124 UPDATE "issue" SET "ranks_available" = TRUE
3125 WHERE "id" = "issue_id_p";
3126 RETURN;
3127 END;
3128 $$;
3130 COMMENT ON FUNCTION "calculate_ranks"
3131 ( "issue"."id"%TYPE )
3132 IS 'Determine ranking (Votes have to be counted first)';
3136 -----------------------------
3137 -- Automatic state changes --
3138 -----------------------------
3141 CREATE FUNCTION "check_issue"
3142 ( "issue_id_p" "issue"."id"%TYPE )
3143 RETURNS VOID
3144 LANGUAGE 'plpgsql' VOLATILE AS $$
3145 DECLARE
3146 "issue_row" "issue"%ROWTYPE;
3147 "policy_row" "policy"%ROWTYPE;
3148 "voting_requested_v" BOOLEAN;
3149 BEGIN
3150 PERFORM "lock_issue"("issue_id_p");
3151 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3152 -- only process open issues:
3153 IF "issue_row"."closed" ISNULL THEN
3154 SELECT * INTO "policy_row" FROM "policy"
3155 WHERE "id" = "issue_row"."policy_id";
3156 -- create a snapshot, unless issue is already fully frozen:
3157 IF "issue_row"."fully_frozen" ISNULL THEN
3158 PERFORM "create_snapshot"("issue_id_p");
3159 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3160 END IF;
3161 -- eventually close or accept issues, which have not been accepted:
3162 IF "issue_row"."accepted" ISNULL THEN
3163 IF EXISTS (
3164 SELECT NULL FROM "initiative"
3165 WHERE "issue_id" = "issue_id_p"
3166 AND "supporter_count" > 0
3167 AND "supporter_count" * "policy_row"."issue_quorum_den"
3168 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
3169 ) THEN
3170 -- accept issues, if supporter count is high enough
3171 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3172 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later
3173 UPDATE "issue" SET "accepted" = "issue_row"."accepted"
3174 WHERE "id" = "issue_row"."id";
3175 ELSIF
3176 now() >= "issue_row"."created" + "issue_row"."admission_time"
3177 THEN
3178 -- close issues, if admission time has expired
3179 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3180 UPDATE "issue" SET "closed" = now()
3181 WHERE "id" = "issue_row"."id";
3182 END IF;
3183 END IF;
3184 -- eventually half freeze issues:
3185 IF
3186 -- NOTE: issue can't be closed at this point, if it has been accepted
3187 "issue_row"."accepted" NOTNULL AND
3188 "issue_row"."half_frozen" ISNULL
3189 THEN
3190 SELECT
3191 CASE
3192 WHEN "vote_now" * 2 > "issue_row"."population" THEN
3193 TRUE
3194 WHEN "vote_later" * 2 > "issue_row"."population" THEN
3195 FALSE
3196 ELSE NULL
3197 END
3198 INTO "voting_requested_v"
3199 FROM "issue" WHERE "id" = "issue_id_p";
3200 IF
3201 "voting_requested_v" OR (
3202 "voting_requested_v" ISNULL AND
3203 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
3204 )
3205 THEN
3206 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
3207 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later
3208 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
3209 WHERE "id" = "issue_row"."id";
3210 END IF;
3211 END IF;
3212 -- close issues after some time, if all initiatives have been revoked:
3213 IF
3214 "issue_row"."closed" ISNULL AND
3215 NOT EXISTS (
3216 -- all initiatives are revoked
3217 SELECT NULL FROM "initiative"
3218 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3219 ) AND (
3220 NOT EXISTS (
3221 -- and no initiatives have been revoked lately
3222 SELECT NULL FROM "initiative"
3223 WHERE "issue_id" = "issue_id_p"
3224 AND now() < "revoked" + "issue_row"."verification_time"
3225 ) OR (
3226 -- or verification time has elapsed
3227 "issue_row"."half_frozen" NOTNULL AND
3228 "issue_row"."fully_frozen" ISNULL AND
3229 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3230 )
3231 )
3232 THEN
3233 "issue_row"."closed" = now(); -- NOTE: "issue_row" used later
3234 UPDATE "issue" SET "closed" = "issue_row"."closed"
3235 WHERE "id" = "issue_row"."id";
3236 END IF;
3237 -- fully freeze issue after verification time:
3238 IF
3239 "issue_row"."half_frozen" NOTNULL AND
3240 "issue_row"."fully_frozen" ISNULL AND
3241 "issue_row"."closed" ISNULL AND
3242 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3243 THEN
3244 PERFORM "freeze_after_snapshot"("issue_id_p");
3245 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
3246 END IF;
3247 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3248 -- close issue by calling close_voting(...) after voting time:
3249 IF
3250 "issue_row"."closed" ISNULL AND
3251 "issue_row"."fully_frozen" NOTNULL AND
3252 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
3253 THEN
3254 PERFORM "close_voting"("issue_id_p");
3255 END IF;
3256 END IF;
3257 RETURN;
3258 END;
3259 $$;
3261 COMMENT ON FUNCTION "check_issue"
3262 ( "issue"."id"%TYPE )
3263 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.';
3266 CREATE FUNCTION "check_everything"()
3267 RETURNS VOID
3268 LANGUAGE 'plpgsql' VOLATILE AS $$
3269 DECLARE
3270 "issue_id_v" "issue"."id"%TYPE;
3271 BEGIN
3272 DELETE FROM "expired_session";
3273 PERFORM "calculate_member_counts"();
3274 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
3275 PERFORM "check_issue"("issue_id_v");
3276 END LOOP;
3277 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
3278 PERFORM "calculate_ranks"("issue_id_v");
3279 END LOOP;
3280 RETURN;
3281 END;
3282 $$;
3284 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.';
3288 ----------------------
3289 -- Deletion of data --
3290 ----------------------
3293 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
3294 RETURNS VOID
3295 LANGUAGE 'plpgsql' VOLATILE AS $$
3296 DECLARE
3297 "issue_row" "issue"%ROWTYPE;
3298 BEGIN
3299 SELECT * INTO "issue_row"
3300 FROM "issue" WHERE "id" = "issue_id_p"
3301 FOR UPDATE;
3302 IF "issue_row"."cleaned" ISNULL THEN
3303 UPDATE "issue" SET
3304 "closed" = NULL,
3305 "ranks_available" = FALSE
3306 WHERE "id" = "issue_id_p";
3307 DELETE FROM "delegating_voter"
3308 WHERE "issue_id" = "issue_id_p";
3309 DELETE FROM "direct_voter"
3310 WHERE "issue_id" = "issue_id_p";
3311 DELETE FROM "delegating_interest_snapshot"
3312 WHERE "issue_id" = "issue_id_p";
3313 DELETE FROM "direct_interest_snapshot"
3314 WHERE "issue_id" = "issue_id_p";
3315 DELETE FROM "delegating_population_snapshot"
3316 WHERE "issue_id" = "issue_id_p";
3317 DELETE FROM "direct_population_snapshot"
3318 WHERE "issue_id" = "issue_id_p";
3319 DELETE FROM "delegation"
3320 WHERE "issue_id" = "issue_id_p";
3321 DELETE FROM "supporter"
3322 WHERE "issue_id" = "issue_id_p";
3323 UPDATE "issue" SET
3324 "closed" = "issue_row"."closed",
3325 "ranks_available" = "issue_row"."ranks_available",
3326 "cleaned" = now()
3327 WHERE "id" = "issue_id_p";
3328 END IF;
3329 RETURN;
3330 END;
3331 $$;
3333 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
3336 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
3337 RETURNS VOID
3338 LANGUAGE 'plpgsql' VOLATILE AS $$
3339 BEGIN
3340 UPDATE "member" SET
3341 "last_login" = NULL,
3342 "login" = NULL,
3343 "password" = NULL,
3344 "active" = FALSE,
3345 "notify_email" = NULL,
3346 "notify_email_unconfirmed" = NULL,
3347 "notify_email_secret" = NULL,
3348 "notify_email_secret_expiry" = NULL,
3349 "notify_email_lock_expiry" = NULL,
3350 "password_reset_secret" = NULL,
3351 "password_reset_secret_expiry" = NULL,
3352 "organizational_unit" = NULL,
3353 "internal_posts" = NULL,
3354 "realname" = NULL,
3355 "birthday" = NULL,
3356 "address" = NULL,
3357 "email" = NULL,
3358 "xmpp_address" = NULL,
3359 "website" = NULL,
3360 "phone" = NULL,
3361 "mobile_phone" = NULL,
3362 "profession" = NULL,
3363 "external_memberships" = NULL,
3364 "external_posts" = NULL,
3365 "statement" = NULL
3366 WHERE "id" = "member_id_p";
3367 -- "text_search_data" is updated by triggers
3368 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
3369 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
3370 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
3371 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
3372 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
3373 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
3374 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
3375 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
3376 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
3377 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
3378 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
3379 DELETE FROM "direct_voter" USING "issue"
3380 WHERE "direct_voter"."issue_id" = "issue"."id"
3381 AND "issue"."closed" ISNULL
3382 AND "member_id" = "member_id_p";
3383 RETURN;
3384 END;
3385 $$;
3387 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)';
3390 CREATE FUNCTION "delete_private_data"()
3391 RETURNS VOID
3392 LANGUAGE 'plpgsql' VOLATILE AS $$
3393 BEGIN
3394 UPDATE "member" SET
3395 "last_login" = NULL,
3396 "login" = NULL,
3397 "password" = NULL,
3398 "notify_email" = NULL,
3399 "notify_email_unconfirmed" = NULL,
3400 "notify_email_secret" = NULL,
3401 "notify_email_secret_expiry" = NULL,
3402 "notify_email_lock_expiry" = NULL,
3403 "password_reset_secret" = NULL,
3404 "password_reset_secret_expiry" = NULL,
3405 "organizational_unit" = NULL,
3406 "internal_posts" = NULL,
3407 "realname" = NULL,
3408 "birthday" = NULL,
3409 "address" = NULL,
3410 "email" = NULL,
3411 "xmpp_address" = NULL,
3412 "website" = NULL,
3413 "phone" = NULL,
3414 "mobile_phone" = NULL,
3415 "profession" = NULL,
3416 "external_memberships" = NULL,
3417 "external_posts" = NULL,
3418 "statement" = NULL;
3419 -- "text_search_data" is updated by triggers
3420 DELETE FROM "invite_code";
3421 DELETE FROM "setting";
3422 DELETE FROM "setting_map";
3423 DELETE FROM "member_relation_setting";
3424 DELETE FROM "member_image";
3425 DELETE FROM "contact";
3426 DELETE FROM "session";
3427 DELETE FROM "area_setting";
3428 DELETE FROM "issue_setting";
3429 DELETE FROM "initiative_setting";
3430 DELETE FROM "suggestion_setting";
3431 DELETE FROM "direct_voter" USING "issue"
3432 WHERE "direct_voter"."issue_id" = "issue"."id"
3433 AND "issue"."closed" ISNULL;
3434 RETURN;
3435 END;
3436 $$;
3438 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.';
3442 COMMIT;