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