liquid_feedback_core
view core.sql @ 19:549b1a0fc042
Changed version to "beta19-dev"; Changed year in LICENSE to "2009-2010"
| author | jbe |
|---|---|
| date | Sat Feb 06 03:24:51 2010 +0100 (2010-02-06) |
| parents | 1cba764373d6 |
| children | 3625d841da90 |
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 ('beta19-dev', NULL, NULL, NULL))
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 "login" TEXT NOT NULL UNIQUE,
60 "password" TEXT,
61 "active" BOOLEAN NOT NULL DEFAULT TRUE,
62 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
63 "notify_email" TEXT,
64 "notify_email_unconfirmed" TEXT,
65 "notify_email_secret" TEXT UNIQUE,
66 "notify_email_secret_expiry" TIMESTAMPTZ,
67 "password_reset_secret" TEXT UNIQUE,
68 "password_reset_secret_expiry" TIMESTAMPTZ,
69 "name" TEXT NOT NULL UNIQUE,
70 "identification" TEXT UNIQUE,
71 "organizational_unit" TEXT,
72 "internal_posts" TEXT,
73 "realname" TEXT,
74 "birthday" DATE,
75 "address" TEXT,
76 "email" TEXT,
77 "xmpp_address" TEXT,
78 "website" TEXT,
79 "phone" TEXT,
80 "mobile_phone" TEXT,
81 "profession" TEXT,
82 "external_memberships" TEXT,
83 "external_posts" TEXT,
84 "statement" TEXT,
85 "text_search_data" TSVECTOR );
86 CREATE INDEX "member_active_idx" ON "member" ("active");
87 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
88 CREATE TRIGGER "update_text_search_data"
89 BEFORE INSERT OR UPDATE ON "member"
90 FOR EACH ROW EXECUTE PROCEDURE
91 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
92 "name", "identification", "organizational_unit", "internal_posts",
93 "realname", "external_memberships", "external_posts", "statement" );
95 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
97 COMMENT ON COLUMN "member"."login" IS 'Login name';
98 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
99 COMMENT ON COLUMN "member"."active" IS 'Inactive members can not login and their supports/votes are not counted by the system.';
100 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
101 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
102 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
103 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
104 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
105 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member';
106 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
107 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
108 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
109 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
110 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
111 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
112 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
113 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his homepage within the system';
116 CREATE TABLE "member_history" (
117 "id" SERIAL8 PRIMARY KEY,
118 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
119 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
120 "login" TEXT NOT NULL,
121 "name" TEXT NOT NULL );
123 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and login names of members';
125 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
126 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the name and login had been valid';
129 CREATE TABLE "invite_code" (
130 "code" TEXT PRIMARY KEY,
131 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
132 "used" TIMESTAMPTZ,
133 "member_id" INT4 UNIQUE REFERENCES "member" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
134 "comment" TEXT,
135 CONSTRAINT "only_used_codes_may_refer_to_member" CHECK ("used" NOTNULL OR "member_id" ISNULL) );
137 COMMENT ON TABLE "invite_code" IS 'Invite codes can be used once to create a new member account.';
139 COMMENT ON COLUMN "invite_code"."code" IS 'Secret code';
140 COMMENT ON COLUMN "invite_code"."created" IS 'Time of creation of the secret code';
141 COMMENT ON COLUMN "invite_code"."used" IS 'NULL, if not used yet, otherwise tells when this code was used to create a member account';
142 COMMENT ON COLUMN "invite_code"."member_id" IS 'References the member whose account was created with this code';
143 COMMENT ON COLUMN "invite_code"."comment" IS 'Comment on the code, which is to be used for administrative reasons only';
146 CREATE TABLE "setting" (
147 PRIMARY KEY ("member_id", "key"),
148 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
149 "key" TEXT NOT NULL,
150 "value" TEXT NOT NULL );
151 CREATE INDEX "setting_key_idx" ON "setting" ("key");
153 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific member setting as a string';
155 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
158 CREATE TABLE "setting_map" (
159 PRIMARY KEY ("member_id", "key", "subkey"),
160 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
161 "key" TEXT NOT NULL,
162 "subkey" TEXT NOT NULL,
163 "value" TEXT NOT NULL );
164 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
166 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific member setting as a map of key value pairs';
168 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
169 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
170 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
173 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
175 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
178 CREATE TABLE "member_image" (
179 PRIMARY KEY ("member_id", "image_type", "scaled"),
180 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
181 "image_type" "member_image_type",
182 "scaled" BOOLEAN,
183 "content_type" TEXT,
184 "data" BYTEA NOT NULL );
186 COMMENT ON TABLE "member_image" IS 'Images of members';
188 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
191 CREATE TABLE "member_count" (
192 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
193 "total_count" INT4 NOT NULL );
195 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';
197 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
198 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
201 CREATE TABLE "contact" (
202 PRIMARY KEY ("member_id", "other_member_id"),
203 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
204 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
205 "public" BOOLEAN NOT NULL DEFAULT FALSE,
206 CONSTRAINT "cant_save_yourself_as_contact"
207 CHECK ("member_id" != "other_member_id") );
209 COMMENT ON TABLE "contact" IS 'Contact lists';
211 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
212 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
213 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
216 CREATE TABLE "session" (
217 "ident" TEXT PRIMARY KEY,
218 "additional_secret" TEXT,
219 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
220 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
221 "lang" TEXT );
222 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
224 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';
226 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
227 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
228 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
229 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
232 CREATE TABLE "policy" (
233 "id" SERIAL4 PRIMARY KEY,
234 "index" INT4 NOT NULL,
235 "active" BOOLEAN NOT NULL DEFAULT TRUE,
236 "name" TEXT NOT NULL UNIQUE,
237 "description" TEXT NOT NULL DEFAULT '',
238 "admission_time" INTERVAL NOT NULL,
239 "discussion_time" INTERVAL NOT NULL,
240 "verification_time" INTERVAL NOT NULL,
241 "voting_time" INTERVAL NOT NULL,
242 "issue_quorum_num" INT4 NOT NULL,
243 "issue_quorum_den" INT4 NOT NULL,
244 "initiative_quorum_num" INT4 NOT NULL,
245 "initiative_quorum_den" INT4 NOT NULL,
246 "majority_num" INT4 NOT NULL DEFAULT 1,
247 "majority_den" INT4 NOT NULL DEFAULT 2,
248 "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE );
249 CREATE INDEX "policy_active_idx" ON "policy" ("active");
251 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
253 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
254 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
255 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"';
256 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"';
257 COMMENT ON COLUMN "policy"."verification_time" IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
258 COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "fully_frozen" but not "closed"';
259 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"';
260 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"';
261 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
262 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
263 COMMENT ON COLUMN "policy"."majority_num" IS 'Numerator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
264 COMMENT ON COLUMN "policy"."majority_den" IS 'Denominator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
265 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.';
268 CREATE TABLE "area" (
269 "id" SERIAL4 PRIMARY KEY,
270 "active" BOOLEAN NOT NULL DEFAULT TRUE,
271 "name" TEXT NOT NULL,
272 "description" TEXT NOT NULL DEFAULT '',
273 "direct_member_count" INT4,
274 "member_weight" INT4,
275 "autoreject_weight" INT4,
276 "text_search_data" TSVECTOR );
277 CREATE INDEX "area_active_idx" ON "area" ("active");
278 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
279 CREATE TRIGGER "update_text_search_data"
280 BEFORE INSERT OR UPDATE ON "area"
281 FOR EACH ROW EXECUTE PROCEDURE
282 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
283 "name", "description" );
285 COMMENT ON TABLE "area" IS 'Subject areas';
287 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
288 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"';
289 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
290 COMMENT ON COLUMN "area"."autoreject_weight" IS 'Sum of weight of members using the autoreject feature';
293 CREATE TABLE "allowed_policy" (
294 PRIMARY KEY ("area_id", "policy_id"),
295 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
296 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
297 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
298 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
300 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
302 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
305 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'start_of_voting');
307 COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''start_of_voting'' = saved state at end of verification period';
310 CREATE TABLE "issue" (
311 "id" SERIAL4 PRIMARY KEY,
312 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
313 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
314 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
315 "accepted" TIMESTAMPTZ,
316 "half_frozen" TIMESTAMPTZ,
317 "fully_frozen" TIMESTAMPTZ,
318 "closed" TIMESTAMPTZ,
319 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
320 "snapshot" TIMESTAMPTZ,
321 "latest_snapshot_event" "snapshot_event",
322 "population" INT4,
323 "vote_now" INT4,
324 "vote_later" INT4,
325 "voter_count" INT4,
326 CONSTRAINT "valid_state" CHECK (
327 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
328 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
329 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
330 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
331 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
332 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
333 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ),
334 CONSTRAINT "state_change_order" CHECK (
335 "created" <= "accepted" AND
336 "accepted" <= "half_frozen" AND
337 "half_frozen" <= "fully_frozen" AND
338 "fully_frozen" <= "closed" ),
339 CONSTRAINT "last_snapshot_on_full_freeze"
340 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
341 CONSTRAINT "freeze_requires_snapshot"
342 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
343 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
344 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
345 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
346 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
347 CREATE INDEX "issue_created_idx" ON "issue" ("created");
348 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
349 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
350 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
351 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
352 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
353 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
355 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
357 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
358 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.';
359 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.';
360 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.';
361 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
362 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';
363 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';
364 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
365 COMMENT ON COLUMN "issue"."vote_now" IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"';
366 COMMENT ON COLUMN "issue"."vote_later" IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"';
367 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';
370 CREATE TABLE "initiative" (
371 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
372 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
373 "id" SERIAL4 PRIMARY KEY,
374 "name" TEXT NOT NULL,
375 "discussion_url" TEXT,
376 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
377 "revoked" TIMESTAMPTZ,
378 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
379 "admitted" BOOLEAN,
380 "supporter_count" INT4,
381 "informed_supporter_count" INT4,
382 "satisfied_supporter_count" INT4,
383 "satisfied_informed_supporter_count" INT4,
384 "positive_votes" INT4,
385 "negative_votes" INT4,
386 "agreed" BOOLEAN,
387 "rank" INT4,
388 "text_search_data" TSVECTOR,
389 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
390 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
391 CONSTRAINT "revoked_initiatives_cant_be_admitted"
392 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
393 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"
394 CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "agreed" ISNULL)),
395 CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"
396 CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL),
397 CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"
398 CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) );
399 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
400 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
401 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
402 CREATE TRIGGER "update_text_search_data"
403 BEFORE INSERT OR UPDATE ON "initiative"
404 FOR EACH ROW EXECUTE PROCEDURE
405 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
406 "name", "discussion_url");
408 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.';
410 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
411 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
412 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
413 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
414 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
415 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
416 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
417 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
418 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
419 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"';
420 COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';
423 CREATE TABLE "draft" (
424 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
425 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
426 "id" SERIAL8 PRIMARY KEY,
427 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
428 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
429 "formatting_engine" TEXT,
430 "content" TEXT NOT NULL,
431 "text_search_data" TSVECTOR );
432 CREATE INDEX "draft_created_idx" ON "draft" ("created");
433 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
434 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
435 CREATE TRIGGER "update_text_search_data"
436 BEFORE INSERT OR UPDATE ON "draft"
437 FOR EACH ROW EXECUTE PROCEDURE
438 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
440 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.';
442 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
443 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
446 CREATE TABLE "suggestion" (
447 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
448 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
449 "id" SERIAL8 PRIMARY KEY,
450 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
451 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
452 "name" TEXT NOT NULL,
453 "description" TEXT NOT NULL DEFAULT '',
454 "text_search_data" TSVECTOR,
455 "minus2_unfulfilled_count" INT4,
456 "minus2_fulfilled_count" INT4,
457 "minus1_unfulfilled_count" INT4,
458 "minus1_fulfilled_count" INT4,
459 "plus1_unfulfilled_count" INT4,
460 "plus1_fulfilled_count" INT4,
461 "plus2_unfulfilled_count" INT4,
462 "plus2_fulfilled_count" INT4 );
463 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
464 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
465 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
466 CREATE TRIGGER "update_text_search_data"
467 BEFORE INSERT OR UPDATE ON "suggestion"
468 FOR EACH ROW EXECUTE PROCEDURE
469 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
470 "name", "description");
472 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';
474 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
475 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
476 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
477 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
478 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
479 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
480 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
481 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
484 CREATE TABLE "membership" (
485 PRIMARY KEY ("area_id", "member_id"),
486 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
487 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
488 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
489 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
491 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
493 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';
496 CREATE TABLE "interest" (
497 PRIMARY KEY ("issue_id", "member_id"),
498 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
499 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
500 "autoreject" BOOLEAN NOT NULL,
501 "voting_requested" BOOLEAN );
502 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
504 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.';
506 COMMENT ON COLUMN "interest"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure';
507 COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply';
510 CREATE TABLE "initiator" (
511 PRIMARY KEY ("initiative_id", "member_id"),
512 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
513 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
514 "accepted" BOOLEAN );
515 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
517 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.';
519 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.';
522 CREATE TABLE "supporter" (
523 "issue_id" INT4 NOT NULL,
524 PRIMARY KEY ("initiative_id", "member_id"),
525 "initiative_id" INT4,
526 "member_id" INT4,
527 "draft_id" INT8 NOT NULL,
528 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
529 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
530 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
532 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.';
534 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
537 CREATE TABLE "opinion" (
538 "initiative_id" INT4 NOT NULL,
539 PRIMARY KEY ("suggestion_id", "member_id"),
540 "suggestion_id" INT8,
541 "member_id" INT4,
542 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
543 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
544 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE,
545 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
546 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
548 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.';
550 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
553 CREATE TYPE "delegation_scope" AS ENUM ('global', 'area', 'issue');
555 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''global'', ''area'', or ''issue'' (order is relevant)';
558 CREATE TABLE "delegation" (
559 "id" SERIAL8 PRIMARY KEY,
560 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
561 "trustee_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
562 "scope" "delegation_scope" NOT NULL,
563 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
564 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
565 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
566 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
567 ("scope" = 'global' AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
568 ("scope" = 'area' AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
569 ("scope" = 'issue' AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
570 UNIQUE ("area_id", "truster_id", "trustee_id"),
571 UNIQUE ("issue_id", "truster_id", "trustee_id") );
572 CREATE UNIQUE INDEX "delegation_global_truster_id_trustee_id_unique_idx"
573 ON "delegation" ("truster_id", "trustee_id") WHERE "scope" = 'global';
574 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
575 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
577 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
579 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
580 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
583 CREATE TABLE "direct_population_snapshot" (
584 PRIMARY KEY ("issue_id", "event", "member_id"),
585 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
586 "event" "snapshot_event",
587 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
588 "weight" INT4,
589 "interest_exists" BOOLEAN NOT NULL );
590 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
592 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
594 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
595 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
596 COMMENT ON COLUMN "direct_population_snapshot"."interest_exists" IS 'TRUE if entry is due to interest in issue, FALSE if entry is only due to membership in area';
599 CREATE TABLE "delegating_population_snapshot" (
600 PRIMARY KEY ("issue_id", "event", "member_id"),
601 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
602 "event" "snapshot_event",
603 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
604 "weight" INT4,
605 "scope" "delegation_scope" NOT NULL,
606 "delegate_member_ids" INT4[] NOT NULL );
607 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
609 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
611 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
612 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
613 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
614 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"';
617 CREATE TABLE "direct_interest_snapshot" (
618 PRIMARY KEY ("issue_id", "event", "member_id"),
619 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
620 "event" "snapshot_event",
621 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
622 "weight" INT4,
623 "voting_requested" BOOLEAN );
624 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
626 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
628 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
629 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
630 COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';
633 CREATE TABLE "delegating_interest_snapshot" (
634 PRIMARY KEY ("issue_id", "event", "member_id"),
635 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
636 "event" "snapshot_event",
637 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
638 "weight" INT4,
639 "scope" "delegation_scope" NOT NULL,
640 "delegate_member_ids" INT4[] NOT NULL );
641 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
643 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
645 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
646 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
647 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
648 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"';
651 CREATE TABLE "direct_supporter_snapshot" (
652 "issue_id" INT4 NOT NULL,
653 PRIMARY KEY ("initiative_id", "event", "member_id"),
654 "initiative_id" INT4,
655 "event" "snapshot_event",
656 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
657 "informed" BOOLEAN NOT NULL,
658 "satisfied" BOOLEAN NOT NULL,
659 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
660 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
661 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
663 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
665 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
666 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
667 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
670 CREATE TABLE "direct_voter" (
671 PRIMARY KEY ("issue_id", "member_id"),
672 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
673 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
674 "weight" INT4,
675 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
676 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
678 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.';
680 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
681 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
684 CREATE TABLE "delegating_voter" (
685 PRIMARY KEY ("issue_id", "member_id"),
686 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
687 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
688 "weight" INT4,
689 "scope" "delegation_scope" NOT NULL,
690 "delegate_member_ids" INT4[] NOT NULL );
691 CREATE INDEX "delegating_voter_member_id_idx" ON "direct_voter" ("member_id");
693 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
695 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
696 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
697 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"';
700 CREATE TABLE "vote" (
701 "issue_id" INT4 NOT NULL,
702 PRIMARY KEY ("initiative_id", "member_id"),
703 "initiative_id" INT4,
704 "member_id" INT4,
705 "grade" INT4,
706 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
707 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
708 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
710 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.';
712 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.';
715 CREATE TABLE "contingent" (
716 "time_frame" INTERVAL PRIMARY KEY,
717 "text_entry_limit" INT4,
718 "initiative_limit" INT4 );
720 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.';
722 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';
723 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
727 --------------------------------
728 -- Writing of history entries --
729 --------------------------------
731 CREATE FUNCTION "write_member_history_trigger"()
732 RETURNS TRIGGER
733 LANGUAGE 'plpgsql' VOLATILE AS $$
734 BEGIN
735 IF NEW."login" != OLD."login" OR NEW."name" != OLD."name" THEN
736 INSERT INTO "member_history" ("member_id", "login", "name")
737 VALUES (NEW."id", OLD."login", OLD."name");
738 END IF;
739 RETURN NULL;
740 END;
741 $$;
743 CREATE TRIGGER "write_member_history"
744 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
745 "write_member_history_trigger"();
747 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
748 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing name or login of a member, create a history entry in "member_history" table';
752 ----------------------------
753 -- Additional constraints --
754 ----------------------------
757 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
758 RETURNS TRIGGER
759 LANGUAGE 'plpgsql' VOLATILE AS $$
760 BEGIN
761 IF NOT EXISTS (
762 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
763 ) THEN
764 --RAISE 'Cannot create issue without an initial initiative.' USING
765 -- ERRCODE = 'integrity_constraint_violation',
766 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
767 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
768 END IF;
769 RETURN NULL;
770 END;
771 $$;
773 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
774 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
775 FOR EACH ROW EXECUTE PROCEDURE
776 "issue_requires_first_initiative_trigger"();
778 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
779 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
782 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
783 RETURNS TRIGGER
784 LANGUAGE 'plpgsql' VOLATILE AS $$
785 DECLARE
786 "reference_lost" BOOLEAN;
787 BEGIN
788 IF TG_OP = 'DELETE' THEN
789 "reference_lost" := TRUE;
790 ELSE
791 "reference_lost" := NEW."issue_id" != OLD."issue_id";
792 END IF;
793 IF
794 "reference_lost" AND NOT EXISTS (
795 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
796 )
797 THEN
798 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
799 END IF;
800 RETURN NULL;
801 END;
802 $$;
804 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
805 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
806 FOR EACH ROW EXECUTE PROCEDURE
807 "last_initiative_deletes_issue_trigger"();
809 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
810 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
813 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
814 RETURNS TRIGGER
815 LANGUAGE 'plpgsql' VOLATILE AS $$
816 BEGIN
817 IF NOT EXISTS (
818 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
819 ) THEN
820 --RAISE 'Cannot create initiative without an initial draft.' USING
821 -- ERRCODE = 'integrity_constraint_violation',
822 -- HINT = 'Create issue, initiative and draft within the same transaction.';
823 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
824 END IF;
825 RETURN NULL;
826 END;
827 $$;
829 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
830 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
831 FOR EACH ROW EXECUTE PROCEDURE
832 "initiative_requires_first_draft_trigger"();
834 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
835 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
838 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
839 RETURNS TRIGGER
840 LANGUAGE 'plpgsql' VOLATILE AS $$
841 DECLARE
842 "reference_lost" BOOLEAN;
843 BEGIN
844 IF TG_OP = 'DELETE' THEN
845 "reference_lost" := TRUE;
846 ELSE
847 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
848 END IF;
849 IF
850 "reference_lost" AND NOT EXISTS (
851 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
852 )
853 THEN
854 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
855 END IF;
856 RETURN NULL;
857 END;
858 $$;
860 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
861 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
862 FOR EACH ROW EXECUTE PROCEDURE
863 "last_draft_deletes_initiative_trigger"();
865 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
866 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
869 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
870 RETURNS TRIGGER
871 LANGUAGE 'plpgsql' VOLATILE AS $$
872 BEGIN
873 IF NOT EXISTS (
874 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
875 ) THEN
876 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
877 END IF;
878 RETURN NULL;
879 END;
880 $$;
882 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
883 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
884 FOR EACH ROW EXECUTE PROCEDURE
885 "suggestion_requires_first_opinion_trigger"();
887 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
888 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
891 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
892 RETURNS TRIGGER
893 LANGUAGE 'plpgsql' VOLATILE AS $$
894 DECLARE
895 "reference_lost" BOOLEAN;
896 BEGIN
897 IF TG_OP = 'DELETE' THEN
898 "reference_lost" := TRUE;
899 ELSE
900 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
901 END IF;
902 IF
903 "reference_lost" AND NOT EXISTS (
904 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
905 )
906 THEN
907 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
908 END IF;
909 RETURN NULL;
910 END;
911 $$;
913 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
914 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
915 FOR EACH ROW EXECUTE PROCEDURE
916 "last_opinion_deletes_suggestion_trigger"();
918 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
919 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
923 --------------------------------------------------------------------
924 -- Auto-retrieval of fields only needed for referential integrity --
925 --------------------------------------------------------------------
927 CREATE FUNCTION "autofill_issue_id_trigger"()
928 RETURNS TRIGGER
929 LANGUAGE 'plpgsql' VOLATILE AS $$
930 BEGIN
931 IF NEW."issue_id" ISNULL THEN
932 SELECT "issue_id" INTO NEW."issue_id"
933 FROM "initiative" WHERE "id" = NEW."initiative_id";
934 END IF;
935 RETURN NEW;
936 END;
937 $$;
939 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
940 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
942 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
943 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
945 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
946 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
947 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
950 CREATE FUNCTION "autofill_initiative_id_trigger"()
951 RETURNS TRIGGER
952 LANGUAGE 'plpgsql' VOLATILE AS $$
953 BEGIN
954 IF NEW."initiative_id" ISNULL THEN
955 SELECT "initiative_id" INTO NEW."initiative_id"
956 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
957 END IF;
958 RETURN NEW;
959 END;
960 $$;
962 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
963 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
965 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
966 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
970 -----------------------------------------------------
971 -- Automatic calculation of certain default values --
972 -----------------------------------------------------
974 CREATE FUNCTION "copy_autoreject_trigger"()
975 RETURNS TRIGGER
976 LANGUAGE 'plpgsql' VOLATILE AS $$
977 BEGIN
978 IF NEW."autoreject" ISNULL THEN
979 SELECT "membership"."autoreject" INTO NEW."autoreject"
980 FROM "issue" JOIN "membership"
981 ON "issue"."area_id" = "membership"."area_id"
982 WHERE "issue"."id" = NEW."issue_id"
983 AND "membership"."member_id" = NEW."member_id";
984 END IF;
985 IF NEW."autoreject" ISNULL THEN
986 NEW."autoreject" := FALSE;
987 END IF;
988 RETURN NEW;
989 END;
990 $$;
992 CREATE TRIGGER "copy_autoreject" BEFORE INSERT OR UPDATE ON "interest"
993 FOR EACH ROW EXECUTE PROCEDURE "copy_autoreject_trigger"();
995 COMMENT ON FUNCTION "copy_autoreject_trigger"() IS 'Implementation of trigger "copy_autoreject" on table "interest"';
996 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';
999 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
1000 RETURNS TRIGGER
1001 LANGUAGE 'plpgsql' VOLATILE AS $$
1002 BEGIN
1003 IF NEW."draft_id" ISNULL THEN
1004 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1005 WHERE "initiative_id" = NEW."initiative_id";
1006 END IF;
1007 RETURN NEW;
1008 END;
1009 $$;
1011 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1012 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
1014 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
1015 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';
1019 ----------------------------------------
1020 -- Automatic creation of dependencies --
1021 ----------------------------------------
1023 CREATE FUNCTION "autocreate_interest_trigger"()
1024 RETURNS TRIGGER
1025 LANGUAGE 'plpgsql' VOLATILE AS $$
1026 BEGIN
1027 IF NOT EXISTS (
1028 SELECT NULL FROM "initiative" JOIN "interest"
1029 ON "initiative"."issue_id" = "interest"."issue_id"
1030 WHERE "initiative"."id" = NEW."initiative_id"
1031 AND "interest"."member_id" = NEW."member_id"
1032 ) THEN
1033 BEGIN
1034 INSERT INTO "interest" ("issue_id", "member_id")
1035 SELECT "issue_id", NEW."member_id"
1036 FROM "initiative" WHERE "id" = NEW."initiative_id";
1037 EXCEPTION WHEN unique_violation THEN END;
1038 END IF;
1039 RETURN NEW;
1040 END;
1041 $$;
1043 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1044 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1046 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1047 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';
1050 CREATE FUNCTION "autocreate_supporter_trigger"()
1051 RETURNS TRIGGER
1052 LANGUAGE 'plpgsql' VOLATILE AS $$
1053 BEGIN
1054 IF NOT EXISTS (
1055 SELECT NULL FROM "suggestion" JOIN "supporter"
1056 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1057 WHERE "suggestion"."id" = NEW."suggestion_id"
1058 AND "supporter"."member_id" = NEW."member_id"
1059 ) THEN
1060 BEGIN
1061 INSERT INTO "supporter" ("initiative_id", "member_id")
1062 SELECT "initiative_id", NEW."member_id"
1063 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1064 EXCEPTION WHEN unique_violation THEN END;
1065 END IF;
1066 RETURN NEW;
1067 END;
1068 $$;
1070 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1071 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1073 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1074 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.';
1078 ------------------------------------------
1079 -- Views and helper functions for views --
1080 ------------------------------------------
1083 CREATE VIEW "global_delegation" AS
1084 SELECT
1085 "delegation"."id",
1086 "delegation"."truster_id",
1087 "delegation"."trustee_id"
1088 FROM "delegation" JOIN "member"
1089 ON "delegation"."trustee_id" = "member"."id"
1090 WHERE "delegation"."scope" = 'global' AND "member"."active";
1092 COMMENT ON VIEW "global_delegation" IS 'Global delegations to active members';
1095 CREATE VIEW "area_delegation" AS
1096 SELECT "subquery".* FROM (
1097 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1098 "area"."id" AS "area_id",
1099 "delegation"."id",
1100 "delegation"."truster_id",
1101 "delegation"."trustee_id",
1102 "delegation"."scope"
1103 FROM "area" JOIN "delegation"
1104 ON "delegation"."scope" = 'global'
1105 OR "delegation"."area_id" = "area"."id"
1106 ORDER BY
1107 "area"."id",
1108 "delegation"."truster_id",
1109 "delegation"."scope" DESC
1110 ) AS "subquery"
1111 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
1112 WHERE "member"."active";
1114 COMMENT ON VIEW "area_delegation" IS 'Active delegations for areas';
1117 CREATE VIEW "issue_delegation" AS
1118 SELECT "subquery".* FROM (
1119 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1120 "issue"."id" AS "issue_id",
1121 "delegation"."id",
1122 "delegation"."truster_id",
1123 "delegation"."trustee_id",
1124 "delegation"."scope"
1125 FROM "issue" JOIN "delegation"
1126 ON "delegation"."scope" = 'global'
1127 OR "delegation"."area_id" = "issue"."area_id"
1128 OR "delegation"."issue_id" = "issue"."id"
1129 ORDER BY
1130 "issue"."id",
1131 "delegation"."truster_id",
1132 "delegation"."scope" DESC
1133 ) AS "subquery"
1134 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
1135 WHERE "member"."active";
1137 COMMENT ON VIEW "issue_delegation" IS 'Active delegations for issues';
1140 CREATE FUNCTION "membership_weight_with_skipping"
1141 ( "area_id_p" "area"."id"%TYPE,
1142 "member_id_p" "member"."id"%TYPE,
1143 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1144 RETURNS INT4
1145 LANGUAGE 'plpgsql' STABLE AS $$
1146 DECLARE
1147 "sum_v" INT4;
1148 "delegation_row" "area_delegation"%ROWTYPE;
1149 BEGIN
1150 "sum_v" := 1;
1151 FOR "delegation_row" IN
1152 SELECT "area_delegation".*
1153 FROM "area_delegation" LEFT JOIN "membership"
1154 ON "membership"."area_id" = "area_id_p"
1155 AND "membership"."member_id" = "area_delegation"."truster_id"
1156 WHERE "area_delegation"."area_id" = "area_id_p"
1157 AND "area_delegation"."trustee_id" = "member_id_p"
1158 AND "membership"."member_id" ISNULL
1159 LOOP
1160 IF NOT
1161 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1162 THEN
1163 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1164 "area_id_p",
1165 "delegation_row"."truster_id",
1166 "skip_member_ids_p" || "delegation_row"."truster_id"
1167 );
1168 END IF;
1169 END LOOP;
1170 RETURN "sum_v";
1171 END;
1172 $$;
1174 COMMENT ON FUNCTION "membership_weight_with_skipping"
1175 ( "area"."id"%TYPE,
1176 "member"."id"%TYPE,
1177 INT4[] )
1178 IS 'Helper function for "membership_weight" function';
1181 CREATE FUNCTION "membership_weight"
1182 ( "area_id_p" "area"."id"%TYPE,
1183 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1184 RETURNS INT4
1185 LANGUAGE 'plpgsql' STABLE AS $$
1186 BEGIN
1187 RETURN "membership_weight_with_skipping"(
1188 "area_id_p",
1189 "member_id_p",
1190 ARRAY["member_id_p"]
1191 );
1192 END;
1193 $$;
1195 COMMENT ON FUNCTION "membership_weight"
1196 ( "area"."id"%TYPE,
1197 "member"."id"%TYPE )
1198 IS 'Calculates the potential voting weight of a member in a given area';
1201 CREATE VIEW "member_count_view" AS
1202 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1204 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1207 CREATE VIEW "area_member_count" AS
1208 SELECT
1209 "area"."id" AS "area_id",
1210 count("member"."id") AS "direct_member_count",
1211 coalesce(
1212 sum(
1213 CASE WHEN "member"."id" NOTNULL THEN
1214 "membership_weight"("area"."id", "member"."id")
1215 ELSE 0 END
1216 )
1217 ) AS "member_weight",
1218 coalesce(
1219 sum(
1220 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
1221 "membership_weight"("area"."id", "member"."id")
1222 ELSE 0 END
1223 )
1224 ) AS "autoreject_weight"
1225 FROM "area"
1226 LEFT JOIN "membership"
1227 ON "area"."id" = "membership"."area_id"
1228 LEFT JOIN "member"
1229 ON "membership"."member_id" = "member"."id"
1230 AND "member"."active"
1231 GROUP BY "area"."id";
1233 COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"';
1236 CREATE VIEW "opening_draft" AS
1237 SELECT "draft".* FROM (
1238 SELECT
1239 "initiative"."id" AS "initiative_id",
1240 min("draft"."id") AS "draft_id"
1241 FROM "initiative" JOIN "draft"
1242 ON "initiative"."id" = "draft"."initiative_id"
1243 GROUP BY "initiative"."id"
1244 ) AS "subquery"
1245 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1247 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1250 CREATE VIEW "current_draft" AS
1251 SELECT "draft".* FROM (
1252 SELECT
1253 "initiative"."id" AS "initiative_id",
1254 max("draft"."id") AS "draft_id"
1255 FROM "initiative" JOIN "draft"
1256 ON "initiative"."id" = "draft"."initiative_id"
1257 GROUP BY "initiative"."id"
1258 ) AS "subquery"
1259 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1261 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1264 CREATE VIEW "critical_opinion" AS
1265 SELECT * FROM "opinion"
1266 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
1267 OR ("degree" = -2 AND "fulfilled" = TRUE);
1269 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
1272 CREATE VIEW "battle" AS
1273 SELECT
1274 "issue"."id" AS "issue_id",
1275 "winning_initiative"."id" AS "winning_initiative_id",
1276 "losing_initiative"."id" AS "losing_initiative_id",
1277 sum(
1278 CASE WHEN
1279 coalesce("better_vote"."grade", 0) >
1280 coalesce("worse_vote"."grade", 0)
1281 THEN "direct_voter"."weight" ELSE 0 END
1282 ) AS "count"
1283 FROM "issue"
1284 LEFT JOIN "direct_voter"
1285 ON "issue"."id" = "direct_voter"."issue_id"
1286 JOIN "initiative" AS "winning_initiative"
1287 ON "issue"."id" = "winning_initiative"."issue_id"
1288 AND "winning_initiative"."agreed"
1289 JOIN "initiative" AS "losing_initiative"
1290 ON "issue"."id" = "losing_initiative"."issue_id"
1291 AND "losing_initiative"."agreed"
1292 LEFT JOIN "vote" AS "better_vote"
1293 ON "direct_voter"."member_id" = "better_vote"."member_id"
1294 AND "winning_initiative"."id" = "better_vote"."initiative_id"
1295 LEFT JOIN "vote" AS "worse_vote"
1296 ON "direct_voter"."member_id" = "worse_vote"."member_id"
1297 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
1298 WHERE
1299 "winning_initiative"."id" != "losing_initiative"."id"
1300 GROUP BY
1301 "issue"."id",
1302 "winning_initiative"."id",
1303 "losing_initiative"."id";
1305 COMMENT ON VIEW "battle" IS 'Number of members preferring one initiative over another';
1308 CREATE VIEW "expired_session" AS
1309 SELECT * FROM "session" WHERE now() > "expiry";
1311 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
1312 DELETE FROM "session" WHERE "ident" = OLD."ident";
1314 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
1315 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
1318 CREATE VIEW "open_issue" AS
1319 SELECT * FROM "issue" WHERE "closed" ISNULL;
1321 COMMENT ON VIEW "open_issue" IS 'All open issues';
1324 CREATE VIEW "issue_with_ranks_missing" AS
1325 SELECT * FROM "issue"
1326 WHERE "fully_frozen" NOTNULL
1327 AND "closed" NOTNULL
1328 AND "ranks_available" = FALSE;
1330 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
1333 CREATE VIEW "member_contingent" AS
1334 SELECT
1335 "member"."id" AS "member_id",
1336 "contingent"."time_frame",
1337 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
1338 (
1339 SELECT count(1) FROM "draft"
1340 WHERE "draft"."author_id" = "member"."id"
1341 AND "draft"."created" > now() - "contingent"."time_frame"
1342 ) + (
1343 SELECT count(1) FROM "suggestion"
1344 WHERE "suggestion"."author_id" = "member"."id"
1345 AND "suggestion"."created" > now() - "contingent"."time_frame"
1346 )
1347 ELSE NULL END AS "text_entry_count",
1348 "contingent"."text_entry_limit",
1349 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
1350 SELECT count(1) FROM "opening_draft"
1351 WHERE "opening_draft"."author_id" = "member"."id"
1352 AND "opening_draft"."created" > now() - "contingent"."time_frame"
1353 ) ELSE NULL END AS "initiative_count",
1354 "contingent"."initiative_limit"
1355 FROM "member" CROSS JOIN "contingent";
1357 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
1359 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
1360 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
1363 CREATE VIEW "member_contingent_left" AS
1364 SELECT
1365 "member_id",
1366 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
1367 max("initiative_limit" - "initiative_count") AS "initiatives_left"
1368 FROM "member_contingent" GROUP BY "member_id";
1370 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.';
1373 CREATE TYPE "timeline_event" AS ENUM (
1374 'issue_created',
1375 'issue_canceled',
1376 'issue_accepted',
1377 'issue_half_frozen',
1378 'issue_finished_without_voting',
1379 'issue_voting_started',
1380 'issue_finished_after_voting',
1381 'initiative_created',
1382 'initiative_revoked',
1383 'draft_created',
1384 'suggestion_created');
1386 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables';
1389 CREATE VIEW "timeline_issue" AS
1390 SELECT
1391 "created" AS "occurrence",
1392 'issue_created'::"timeline_event" AS "event",
1393 "id" AS "issue_id"
1394 FROM "issue"
1395 UNION ALL
1396 SELECT
1397 "closed" AS "occurrence",
1398 'issue_canceled'::"timeline_event" AS "event",
1399 "id" AS "issue_id"
1400 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
1401 UNION ALL
1402 SELECT
1403 "accepted" AS "occurrence",
1404 'issue_accepted'::"timeline_event" AS "event",
1405 "id" AS "issue_id"
1406 FROM "issue" WHERE "accepted" NOTNULL
1407 UNION ALL
1408 SELECT
1409 "half_frozen" AS "occurrence",
1410 'issue_half_frozen'::"timeline_event" AS "event",
1411 "id" AS "issue_id"
1412 FROM "issue" WHERE "half_frozen" NOTNULL
1413 UNION ALL
1414 SELECT
1415 "fully_frozen" AS "occurrence",
1416 'issue_voting_started'::"timeline_event" AS "event",
1417 "id" AS "issue_id"
1418 FROM "issue"
1419 WHERE "fully_frozen" NOTNULL
1420 AND ("closed" ISNULL OR "closed" != "fully_frozen")
1421 UNION ALL
1422 SELECT
1423 "closed" AS "occurrence",
1424 CASE WHEN "fully_frozen" = "closed" THEN
1425 'issue_finished_without_voting'::"timeline_event"
1426 ELSE
1427 'issue_finished_after_voting'::"timeline_event"
1428 END AS "event",
1429 "id" AS "issue_id"
1430 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
1432 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view';
1435 CREATE VIEW "timeline_initiative" AS
1436 SELECT
1437 "created" AS "occurrence",
1438 'initiative_created'::"timeline_event" AS "event",
1439 "id" AS "initiative_id"
1440 FROM "initiative"
1441 UNION ALL
1442 SELECT
1443 "revoked" AS "occurrence",
1444 'initiative_revoked'::"timeline_event" AS "event",
1445 "id" AS "initiative_id"
1446 FROM "initiative" WHERE "revoked" NOTNULL;
1448 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view';
1451 CREATE VIEW "timeline_draft" AS
1452 SELECT
1453 "created" AS "occurrence",
1454 'draft_created'::"timeline_event" AS "event",
1455 "id" AS "draft_id"
1456 FROM "draft";
1458 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view';
1461 CREATE VIEW "timeline_suggestion" AS
1462 SELECT
1463 "created" AS "occurrence",
1464 'suggestion_created'::"timeline_event" AS "event",
1465 "id" AS "suggestion_id"
1466 FROM "suggestion";
1468 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view';
1471 CREATE VIEW "timeline" AS
1472 SELECT
1473 "occurrence",
1474 "event",
1475 "issue_id",
1476 NULL AS "initiative_id",
1477 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
1478 NULL::INT8 AS "suggestion_id"
1479 FROM "timeline_issue"
1480 UNION ALL
1481 SELECT
1482 "occurrence",
1483 "event",
1484 NULL AS "issue_id",
1485 "initiative_id",
1486 NULL AS "draft_id",
1487 NULL AS "suggestion_id"
1488 FROM "timeline_initiative"
1489 UNION ALL
1490 SELECT
1491 "occurrence",
1492 "event",
1493 NULL AS "issue_id",
1494 NULL AS "initiative_id",
1495 "draft_id",
1496 NULL AS "suggestion_id"
1497 FROM "timeline_draft"
1498 UNION ALL
1499 SELECT
1500 "occurrence",
1501 "event",
1502 NULL AS "issue_id",
1503 NULL AS "initiative_id",
1504 NULL AS "draft_id",
1505 "suggestion_id"
1506 FROM "timeline_suggestion";
1508 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system';
1512 --------------------------------------------------
1513 -- Set returning function for delegation chains --
1514 --------------------------------------------------
1517 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
1518 ('first', 'intermediate', 'last', 'repetition');
1520 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
1523 CREATE TYPE "delegation_chain_row" AS (
1524 "index" INT4,
1525 "member_id" INT4,
1526 "member_active" BOOLEAN,
1527 "participation" BOOLEAN,
1528 "overridden" BOOLEAN,
1529 "scope_in" "delegation_scope",
1530 "scope_out" "delegation_scope",
1531 "loop" "delegation_chain_loop_tag" );
1533 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
1535 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
1536 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';
1537 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
1538 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
1539 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
1540 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
1543 CREATE FUNCTION "delegation_chain"
1544 ( "member_id_p" "member"."id"%TYPE,
1545 "area_id_p" "area"."id"%TYPE,
1546 "issue_id_p" "issue"."id"%TYPE,
1547 "simulate_trustee_id_p" "member"."id"%TYPE )
1548 RETURNS SETOF "delegation_chain_row"
1549 LANGUAGE 'plpgsql' STABLE AS $$
1550 DECLARE
1551 "issue_row" "issue"%ROWTYPE;
1552 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
1553 "loop_member_id_v" "member"."id"%TYPE;
1554 "output_row" "delegation_chain_row";
1555 "output_rows" "delegation_chain_row"[];
1556 "delegation_row" "delegation"%ROWTYPE;
1557 "row_count" INT4;
1558 "i" INT4;
1559 "loop_v" BOOLEAN;
1560 BEGIN
1561 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1562 "visited_member_ids" := '{}';
1563 "loop_member_id_v" := NULL;
1564 "output_rows" := '{}';
1565 "output_row"."index" := 0;
1566 "output_row"."member_id" := "member_id_p";
1567 "output_row"."member_active" := TRUE;
1568 "output_row"."participation" := FALSE;
1569 "output_row"."overridden" := FALSE;
1570 "output_row"."scope_out" := NULL;
1571 LOOP
1572 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
1573 "loop_member_id_v" := "output_row"."member_id";
1574 ELSE
1575 "visited_member_ids" :=
1576 "visited_member_ids" || "output_row"."member_id";
1577 END IF;
1578 IF "output_row"."participation" THEN
1579 "output_row"."overridden" := TRUE;
1580 END IF;
1581 "output_row"."scope_in" := "output_row"."scope_out";
1582 IF EXISTS (
1583 SELECT NULL FROM "member"
1584 WHERE "id" = "output_row"."member_id" AND "active"
1585 ) THEN
1586 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
1587 SELECT * INTO "delegation_row" FROM "delegation"
1588 WHERE "truster_id" = "output_row"."member_id"
1589 AND "scope" = 'global';
1590 ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN
1591 "output_row"."participation" := EXISTS (
1592 SELECT NULL FROM "membership"
1593 WHERE "area_id" = "area_id_p"
1594 AND "member_id" = "output_row"."member_id"
1595 );
1596 SELECT * INTO "delegation_row" FROM "delegation"
1597 WHERE "truster_id" = "output_row"."member_id"
1598 AND ("scope" = 'global' OR "area_id" = "area_id_p")
1599 ORDER BY "scope" DESC;
1600 ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN
1601 "output_row"."participation" := EXISTS (
1602 SELECT NULL FROM "interest"
1603 WHERE "issue_id" = "issue_id_p"
1604 AND "member_id" = "output_row"."member_id"
1605 );
1606 SELECT * INTO "delegation_row" FROM "delegation"
1607 WHERE "truster_id" = "output_row"."member_id"
1608 AND ("scope" = 'global' OR
1609 "area_id" = "issue_row"."area_id" OR
1610 "issue_id" = "issue_id_p"
1611 )
1612 ORDER BY "scope" DESC;
1613 ELSE
1614 RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.';
1615 END IF;
1616 ELSE
1617 "output_row"."member_active" := FALSE;
1618 "output_row"."participation" := FALSE;
1619 "output_row"."scope_out" := NULL;
1620 "delegation_row" := ROW(NULL);
1621 END IF;
1622 IF
1623 "output_row"."member_id" = "member_id_p" AND
1624 "simulate_trustee_id_p" NOTNULL
1625 THEN
1626 "output_row"."scope_out" := CASE
1627 WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global'
1628 WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area'
1629 WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue'
1630 END;
1631 "output_rows" := "output_rows" || "output_row";
1632 "output_row"."member_id" := "simulate_trustee_id_p";
1633 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
1634 "output_row"."scope_out" := "delegation_row"."scope";
1635 "output_rows" := "output_rows" || "output_row";
1636 "output_row"."member_id" := "delegation_row"."trustee_id";
1637 ELSE
1638 "output_row"."scope_out" := NULL;
1639 "output_rows" := "output_rows" || "output_row";
1640 EXIT;
1641 END IF;
1642 EXIT WHEN "loop_member_id_v" NOTNULL;
1643 "output_row"."index" := "output_row"."index" + 1;
1644 END LOOP;
1645 "row_count" := array_upper("output_rows", 1);
1646 "i" := 1;
1647 "loop_v" := FALSE;
1648 LOOP
1649 "output_row" := "output_rows"["i"];
1650 EXIT WHEN "output_row"."member_id" ISNULL;
1651 IF "loop_v" THEN
1652 IF "i" + 1 = "row_count" THEN
1653 "output_row"."loop" := 'last';
1654 ELSIF "i" = "row_count" THEN
1655 "output_row"."loop" := 'repetition';
1656 ELSE
1657 "output_row"."loop" := 'intermediate';
1658 END IF;
1659 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
1660 "output_row"."loop" := 'first';
1661 "loop_v" := TRUE;
1662 END IF;
1663 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
1664 "output_row"."participation" := NULL;
1665 END IF;
1666 RETURN NEXT "output_row";
1667 "i" := "i" + 1;
1668 END LOOP;
1669 RETURN;
1670 END;
1671 $$;
1673 COMMENT ON FUNCTION "delegation_chain"
1674 ( "member"."id"%TYPE,
1675 "area"."id"%TYPE,
1676 "issue"."id"%TYPE,
1677 "member"."id"%TYPE )
1678 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
1680 CREATE FUNCTION "delegation_chain"
1681 ( "member_id_p" "member"."id"%TYPE,
1682 "area_id_p" "area"."id"%TYPE,
1683 "issue_id_p" "issue"."id"%TYPE )
1684 RETURNS SETOF "delegation_chain_row"
1685 LANGUAGE 'plpgsql' STABLE AS $$
1686 DECLARE
1687 "result_row" "delegation_chain_row";
1688 BEGIN
1689 FOR "result_row" IN
1690 SELECT * FROM "delegation_chain"(
1691 "member_id_p", "area_id_p", "issue_id_p", NULL
1692 )
1693 LOOP
1694 RETURN NEXT "result_row";
1695 END LOOP;
1696 RETURN;
1697 END;
1698 $$;
1700 COMMENT ON FUNCTION "delegation_chain"
1701 ( "member"."id"%TYPE,
1702 "area"."id"%TYPE,
1703 "issue"."id"%TYPE )
1704 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
1708 ------------------------------
1709 -- Comparison by vote count --
1710 ------------------------------
1712 CREATE FUNCTION "vote_ratio"
1713 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
1714 "negative_votes_p" "initiative"."negative_votes"%TYPE )
1715 RETURNS FLOAT8
1716 LANGUAGE 'plpgsql' STABLE AS $$
1717 DECLARE
1718 "total_v" INT4;
1719 BEGIN
1720 "total_v" := "positive_votes_p" + "negative_votes_p";
1721 IF "total_v" > 0 THEN
1722 RETURN "positive_votes_p"::FLOAT8 / "total_v"::FLOAT8;
1723 ELSE
1724 RETURN 0.5;
1725 END IF;
1726 END;
1727 $$;
1729 COMMENT ON FUNCTION "vote_ratio"
1730 ( "initiative"."positive_votes"%TYPE,
1731 "initiative"."negative_votes"%TYPE )
1732 IS 'Ratio of positive votes to sum of positive and negative votes; 0.5, if there are neither positive nor negative votes';
1736 ------------------------------------------------
1737 -- Locking for snapshots and voting procedure --
1738 ------------------------------------------------
1740 CREATE FUNCTION "global_lock"() RETURNS VOID
1741 LANGUAGE 'plpgsql' VOLATILE AS $$
1742 BEGIN
1743 -- NOTE: PostgreSQL allows reading, while tables are locked in
1744 -- exclusive move. Transactions should be kept short anyway!
1745 LOCK TABLE "member" IN EXCLUSIVE MODE;
1746 LOCK TABLE "area" IN EXCLUSIVE MODE;
1747 LOCK TABLE "membership" IN EXCLUSIVE MODE;
1748 -- NOTE: "member", "area" and "membership" are locked first to
1749 -- prevent deadlocks in combination with "calculate_member_counts"()
1750 LOCK TABLE "policy" IN EXCLUSIVE MODE;
1751 LOCK TABLE "issue" IN EXCLUSIVE MODE;
1752 LOCK TABLE "initiative" IN EXCLUSIVE MODE;
1753 LOCK TABLE "draft" IN EXCLUSIVE MODE;
1754 LOCK TABLE "suggestion" IN EXCLUSIVE MODE;
1755 LOCK TABLE "interest" IN EXCLUSIVE MODE;
1756 LOCK TABLE "initiator" IN EXCLUSIVE MODE;
1757 LOCK TABLE "supporter" IN EXCLUSIVE MODE;
1758 LOCK TABLE "opinion" IN EXCLUSIVE MODE;
1759 LOCK TABLE "delegation" IN EXCLUSIVE MODE;
1760 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
1761 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
1762 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
1763 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
1764 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
1765 LOCK TABLE "direct_voter" IN EXCLUSIVE MODE;
1766 LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;
1767 LOCK TABLE "vote" IN EXCLUSIVE MODE;
1768 RETURN;
1769 END;
1770 $$;
1772 COMMENT ON FUNCTION "global_lock"() IS 'Locks all tables related to support/voting until end of transaction; read access is still possible though';
1776 -------------------------------
1777 -- Materialize member counts --
1778 -------------------------------
1780 CREATE FUNCTION "calculate_member_counts"()
1781 RETURNS VOID
1782 LANGUAGE 'plpgsql' VOLATILE AS $$
1783 BEGIN
1784 LOCK TABLE "member" IN EXCLUSIVE MODE;
1785 LOCK TABLE "area" IN EXCLUSIVE MODE;
1786 LOCK TABLE "membership" IN EXCLUSIVE MODE;
1787 DELETE FROM "member_count";
1788 INSERT INTO "member_count" ("total_count")
1789 SELECT "total_count" FROM "member_count_view";
1790 UPDATE "area" SET
1791 "direct_member_count" = "view"."direct_member_count",
1792 "member_weight" = "view"."member_weight",
1793 "autoreject_weight" = "view"."autoreject_weight"
1794 FROM "area_member_count" AS "view"
1795 WHERE "view"."area_id" = "area"."id";
1796 RETURN;
1797 END;
1798 $$;
1800 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"';
1804 ------------------------------
1805 -- Calculation of snapshots --
1806 ------------------------------
1808 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
1809 ( "issue_id_p" "issue"."id"%TYPE,
1810 "member_id_p" "member"."id"%TYPE,
1811 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
1812 RETURNS "direct_population_snapshot"."weight"%TYPE
1813 LANGUAGE 'plpgsql' VOLATILE AS $$
1814 DECLARE
1815 "issue_delegation_row" "issue_delegation"%ROWTYPE;
1816 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
1817 "weight_v" INT4;
1818 "sub_weight_v" INT4;
1819 BEGIN
1820 "weight_v" := 0;
1821 FOR "issue_delegation_row" IN
1822 SELECT * FROM "issue_delegation"
1823 WHERE "trustee_id" = "member_id_p"
1824 AND "issue_id" = "issue_id_p"
1825 LOOP
1826 IF NOT EXISTS (
1827 SELECT NULL FROM "direct_population_snapshot"
1828 WHERE "issue_id" = "issue_id_p"
1829 AND "event" = 'periodic'
1830 AND "member_id" = "issue_delegation_row"."truster_id"
1831 ) AND NOT EXISTS (
1832 SELECT NULL FROM "delegating_population_snapshot"
1833 WHERE "issue_id" = "issue_id_p"
1834 AND "event" = 'periodic'
1835 AND "member_id" = "issue_delegation_row"."truster_id"
1836 ) THEN
1837 "delegate_member_ids_v" :=
1838 "member_id_p" || "delegate_member_ids_p";
1839 INSERT INTO "delegating_population_snapshot" (
1840 "issue_id",
1841 "event",
1842 "member_id",
1843 "scope",
1844 "delegate_member_ids"
1845 ) VALUES (
1846 "issue_id_p",
1847 'periodic',
1848 "issue_delegation_row"."truster_id",
1849 "issue_delegation_row"."scope",
1850 "delegate_member_ids_v"
1851 );
1852 "sub_weight_v" := 1 +
1853 "weight_of_added_delegations_for_population_snapshot"(
1854 "issue_id_p",
1855 "issue_delegation_row"."truster_id",
1856 "delegate_member_ids_v"
1857 );
1858 UPDATE "delegating_population_snapshot"
1859 SET "weight" = "sub_weight_v"
1860 WHERE "issue_id" = "issue_id_p"
1861 AND "event" = 'periodic'
1862 AND "member_id" = "issue_delegation_row"."truster_id";
1863 "weight_v" := "weight_v" + "sub_weight_v";
1864 END IF;
1865 END LOOP;
1866 RETURN "weight_v";
1867 END;
1868 $$;
1870 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
1871 ( "issue"."id"%TYPE,
1872 "member"."id"%TYPE,
1873 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
1874 IS 'Helper function for "create_population_snapshot" function';
1877 CREATE FUNCTION "create_population_snapshot"
1878 ( "issue_id_p" "issue"."id"%TYPE )
1879 RETURNS VOID
1880 LANGUAGE 'plpgsql' VOLATILE AS $$
1881 DECLARE
1882 "member_id_v" "member"."id"%TYPE;
1883 BEGIN
1884 DELETE FROM "direct_population_snapshot"
1885 WHERE "issue_id" = "issue_id_p"
1886 AND "event" = 'periodic';
1887 DELETE FROM "delegating_population_snapshot"
1888 WHERE "issue_id" = "issue_id_p"
1889 AND "event" = 'periodic';
1890 INSERT INTO "direct_population_snapshot"
1891 ("issue_id", "event", "member_id", "interest_exists")
1892 SELECT DISTINCT ON ("issue_id", "member_id")
1893 "issue_id_p" AS "issue_id",
1894 'periodic' AS "event",
1895 "subquery"."member_id",
1896 "subquery"."interest_exists"
1897 FROM (
1898 SELECT
1899 "member"."id" AS "member_id",
1900 FALSE AS "interest_exists"
1901 FROM "issue"
1902 JOIN "area" ON "issue"."area_id" = "area"."id"
1903 JOIN "membership" ON "area"."id" = "membership"."area_id"
1904 JOIN "member" ON "membership"."member_id" = "member"."id"
1905 WHERE "issue"."id" = "issue_id_p"
1906 AND "member"."active"
1907 UNION
1908 SELECT
1909 "member"."id" AS "member_id",
1910 TRUE AS "interest_exists"
1911 FROM "interest" JOIN "member"
1912 ON "interest"."member_id" = "member"."id"
1913 WHERE "interest"."issue_id" = "issue_id_p"
1914 AND "member"."active"
1915 ) AS "subquery"
1916 ORDER BY
1917 "issue_id_p",
1918 "subquery"."member_id",
1919 "subquery"."interest_exists" DESC;
1920 FOR "member_id_v" IN
1921 SELECT "member_id" FROM "direct_population_snapshot"
1922 WHERE "issue_id" = "issue_id_p"
1923 AND "event" = 'periodic'
1924 LOOP
1925 UPDATE "direct_population_snapshot" SET
1926 "weight" = 1 +
1927 "weight_of_added_delegations_for_population_snapshot"(
1928 "issue_id_p",
1929 "member_id_v",
1930 '{}'
1931 )
1932 WHERE "issue_id" = "issue_id_p"
1933 AND "event" = 'periodic'
1934 AND "member_id" = "member_id_v";
1935 END LOOP;
1936 RETURN;
1937 END;
1938 $$;
1940 COMMENT ON FUNCTION "create_population_snapshot"
1941 ( "issue_id_p" "issue"."id"%TYPE )
1942 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.';
1945 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
1946 ( "issue_id_p" "issue"."id"%TYPE,
1947 "member_id_p" "member"."id"%TYPE,
1948 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
1949 RETURNS "direct_interest_snapshot"."weight"%TYPE
1950 LANGUAGE 'plpgsql' VOLATILE AS $$
1951 DECLARE
1952 "issue_delegation_row" "issue_delegation"%ROWTYPE;
1953 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
1954 "weight_v" INT4;
1955 "sub_weight_v" INT4;
1956 BEGIN
1957 "weight_v" := 0;
1958 FOR "issue_delegation_row" IN
1959 SELECT * FROM "issue_delegation"
1960 WHERE "trustee_id" = "member_id_p"
1961 AND "issue_id" = "issue_id_p"
1962 LOOP
1963 IF NOT EXISTS (
1964 SELECT NULL FROM "direct_interest_snapshot"
1965 WHERE "issue_id" = "issue_id_p"
1966 AND "event" = 'periodic'
1967 AND "member_id" = "issue_delegation_row"."truster_id"
1968 ) AND NOT EXISTS (
1969 SELECT NULL FROM "delegating_interest_snapshot"
1970 WHERE "issue_id" = "issue_id_p"
1971 AND "event" = 'periodic'
1972 AND "member_id" = "issue_delegation_row"."truster_id"
1973 ) THEN
1974 "delegate_member_ids_v" :=
1975 "member_id_p" || "delegate_member_ids_p";
1976 INSERT INTO "delegating_interest_snapshot" (
1977 "issue_id",
1978 "event",
1979 "member_id",
1980 "scope",
1981 "delegate_member_ids"
1982 ) VALUES (
1983 "issue_id_p",
1984 'periodic',
1985 "issue_delegation_row"."truster_id",
1986 "issue_delegation_row"."scope",
1987 "delegate_member_ids_v"
1988 );
1989 "sub_weight_v" := 1 +
1990 "weight_of_added_delegations_for_interest_snapshot"(
1991 "issue_id_p",
1992 "issue_delegation_row"."truster_id",
1993 "delegate_member_ids_v"
1994 );
1995 UPDATE "delegating_interest_snapshot"
1996 SET "weight" = "sub_weight_v"
1997 WHERE "issue_id" = "issue_id_p"
1998 AND "event" = 'periodic'
1999 AND "member_id" = "issue_delegation_row"."truster_id";
2000 "weight_v" := "weight_v" + "sub_weight_v";
2001 END IF;
2002 END LOOP;
2003 RETURN "weight_v";
2004 END;
2005 $$;
2007 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2008 ( "issue"."id"%TYPE,
2009 "member"."id"%TYPE,
2010 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2011 IS 'Helper function for "create_interest_snapshot" function';
2014 CREATE FUNCTION "create_interest_snapshot"
2015 ( "issue_id_p" "issue"."id"%TYPE )
2016 RETURNS VOID
2017 LANGUAGE 'plpgsql' VOLATILE AS $$
2018 DECLARE
2019 "member_id_v" "member"."id"%TYPE;
2020 BEGIN
2021 DELETE FROM "direct_interest_snapshot"
2022 WHERE "issue_id" = "issue_id_p"
2023 AND "event" = 'periodic';
2024 DELETE FROM "delegating_interest_snapshot"
2025 WHERE "issue_id" = "issue_id_p"
2026 AND "event" = 'periodic';
2027 DELETE FROM "direct_supporter_snapshot"
2028 WHERE "issue_id" = "issue_id_p"
2029 AND "event" = 'periodic';
2030 INSERT INTO "direct_interest_snapshot"
2031 ("issue_id", "event", "member_id", "voting_requested")
2032 SELECT
2033 "issue_id_p" AS "issue_id",
2034 'periodic' AS "event",
2035 "member"."id" AS "member_id",
2036 "interest"."voting_requested"
2037 FROM "interest" JOIN "member"
2038 ON "interest"."member_id" = "member"."id"
2039 WHERE "interest"."issue_id" = "issue_id_p"
2040 AND "member"."active";
2041 FOR "member_id_v" IN
2042 SELECT "member_id" FROM "direct_interest_snapshot"
2043 WHERE "issue_id" = "issue_id_p"
2044 AND "event" = 'periodic'
2045 LOOP
2046 UPDATE "direct_interest_snapshot" SET
2047 "weight" = 1 +
2048 "weight_of_added_delegations_for_interest_snapshot"(
2049 "issue_id_p",
2050 "member_id_v",
2051 '{}'
2052 )
2053 WHERE "issue_id" = "issue_id_p"
2054 AND "event" = 'periodic'
2055 AND "member_id" = "member_id_v";
2056 END LOOP;
2057 INSERT INTO "direct_supporter_snapshot"
2058 ( "issue_id", "initiative_id", "event", "member_id",
2059 "informed", "satisfied" )
2060 SELECT
2061 "issue_id_p" AS "issue_id",
2062 "initiative"."id" AS "initiative_id",
2063 'periodic' AS "event",
2064 "member"."id" AS "member_id",
2065 "supporter"."draft_id" = "current_draft"."id" AS "informed",
2066 NOT EXISTS (
2067 SELECT NULL FROM "critical_opinion"
2068 WHERE "initiative_id" = "initiative"."id"
2069 AND "member_id" = "member"."id"
2070 ) AS "satisfied"
2071 FROM "supporter"
2072 JOIN "member"
2073 ON "supporter"."member_id" = "member"."id"
2074 JOIN "initiative"
2075 ON "supporter"."initiative_id" = "initiative"."id"
2076 JOIN "current_draft"
2077 ON "initiative"."id" = "current_draft"."initiative_id"
2078 JOIN "direct_interest_snapshot"
2079 ON "member"."id" = "direct_interest_snapshot"."member_id"
2080 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
2081 AND "event" = 'periodic'
2082 WHERE "member"."active"
2083 AND "initiative"."issue_id" = "issue_id_p";
2084 RETURN;
2085 END;
2086 $$;
2088 COMMENT ON FUNCTION "create_interest_snapshot"
2089 ( "issue"."id"%TYPE )
2090 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.';
2093 CREATE FUNCTION "create_snapshot"
2094 ( "issue_id_p" "issue"."id"%TYPE )
2095 RETURNS VOID
2096 LANGUAGE 'plpgsql' VOLATILE AS $$
2097 DECLARE
2098 "initiative_id_v" "initiative"."id"%TYPE;
2099 "suggestion_id_v" "suggestion"."id"%TYPE;
2100 BEGIN
2101 PERFORM "global_lock"();
2102 PERFORM "create_population_snapshot"("issue_id_p");
2103 PERFORM "create_interest_snapshot"("issue_id_p");
2104 UPDATE "issue" SET
2105 "snapshot" = now(),
2106 "latest_snapshot_event" = 'periodic',
2107 "population" = (
2108 SELECT coalesce(sum("weight"), 0)
2109 FROM "direct_population_snapshot"
2110 WHERE "issue_id" = "issue_id_p"
2111 AND "event" = 'periodic'
2112 ),
2113 "vote_now" = (
2114 SELECT coalesce(sum("weight"), 0)
2115 FROM "direct_interest_snapshot"
2116 WHERE "issue_id" = "issue_id_p"
2117 AND "event" = 'periodic'
2118 AND "voting_requested" = TRUE
2119 ),
2120 "vote_later" = (
2121 SELECT coalesce(sum("weight"), 0)
2122 FROM "direct_interest_snapshot"
2123 WHERE "issue_id" = "issue_id_p"
2124 AND "event" = 'periodic'
2125 AND "voting_requested" = FALSE
2126 )
2127 WHERE "id" = "issue_id_p";
2128 FOR "initiative_id_v" IN
2129 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
2130 LOOP
2131 UPDATE "initiative" SET
2132 "supporter_count" = (
2133 SELECT coalesce(sum("di"."weight"), 0)
2134 FROM "direct_interest_snapshot" AS "di"
2135 JOIN "direct_supporter_snapshot" AS "ds"
2136 ON "di"."member_id" = "ds"."member_id"
2137 WHERE "di"."issue_id" = "issue_id_p"
2138 AND "di"."event" = 'periodic'
2139 AND "ds"."initiative_id" = "initiative_id_v"
2140 AND "ds"."event" = 'periodic'
2141 ),
2142 "informed_supporter_count" = (
2143 SELECT coalesce(sum("di"."weight"), 0)
2144 FROM "direct_interest_snapshot" AS "di"
2145 JOIN "direct_supporter_snapshot" AS "ds"
2146 ON "di"."member_id" = "ds"."member_id"
2147 WHERE "di"."issue_id" = "issue_id_p"
2148 AND "di"."event" = 'periodic'
2149 AND "ds"."initiative_id" = "initiative_id_v"
2150 AND "ds"."event" = 'periodic'
2151 AND "ds"."informed"
2152 ),
2153 "satisfied_supporter_count" = (
2154 SELECT coalesce(sum("di"."weight"), 0)
2155 FROM "direct_interest_snapshot" AS "di"
2156 JOIN "direct_supporter_snapshot" AS "ds"
2157 ON "di"."member_id" = "ds"."member_id"
2158 WHERE "di"."issue_id" = "issue_id_p"
2159 AND "di"."event" = 'periodic'
2160 AND "ds"."initiative_id" = "initiative_id_v"
2161 AND "ds"."event" = 'periodic'
2162 AND "ds"."satisfied"
2163 ),
2164 "satisfied_informed_supporter_count" = (
2165 SELECT coalesce(sum("di"."weight"), 0)
2166 FROM "direct_interest_snapshot" AS "di"
2167 JOIN "direct_supporter_snapshot" AS "ds"
2168 ON "di"."member_id" = "ds"."member_id"
2169 WHERE "di"."issue_id" = "issue_id_p"
2170 AND "di"."event" = 'periodic'
2171 AND "ds"."initiative_id" = "initiative_id_v"
2172 AND "ds"."event" = 'periodic'
2173 AND "ds"."informed"
2174 AND "ds"."satisfied"
2175 )
2176 WHERE "id" = "initiative_id_v";
2177 FOR "suggestion_id_v" IN
2178 SELECT "id" FROM "suggestion"
2179 WHERE "initiative_id" = "initiative_id_v"
2180 LOOP
2181 UPDATE "suggestion" SET
2182 "minus2_unfulfilled_count" = (
2183 SELECT coalesce(sum("snapshot"."weight"), 0)
2184 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2185 ON "opinion"."member_id" = "snapshot"."member_id"
2186 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2187 AND "snapshot"."issue_id" = "issue_id_p"
2188 AND "opinion"."degree" = -2
2189 AND "opinion"."fulfilled" = FALSE
2190 ),
2191 "minus2_fulfilled_count" = (
2192 SELECT coalesce(sum("snapshot"."weight"), 0)
2193 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2194 ON "opinion"."member_id" = "snapshot"."member_id"
2195 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2196 AND "snapshot"."issue_id" = "issue_id_p"
2197 AND "opinion"."degree" = -2
2198 AND "opinion"."fulfilled" = TRUE
2199 ),
2200 "minus1_unfulfilled_count" = (
2201 SELECT coalesce(sum("snapshot"."weight"), 0)
2202 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2203 ON "opinion"."member_id" = "snapshot"."member_id"
2204 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2205 AND "snapshot"."issue_id" = "issue_id_p"
2206 AND "opinion"."degree" = -1
2207 AND "opinion"."fulfilled" = FALSE
2208 ),
2209 "minus1_fulfilled_count" = (
2210 SELECT coalesce(sum("snapshot"."weight"), 0)
2211 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2212 ON "opinion"."member_id" = "snapshot"."member_id"
2213 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2214 AND "snapshot"."issue_id" = "issue_id_p"
2215 AND "opinion"."degree" = -1
2216 AND "opinion"."fulfilled" = TRUE
2217 ),
2218 "plus1_unfulfilled_count" = (
2219 SELECT coalesce(sum("snapshot"."weight"), 0)
2220 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2221 ON "opinion"."member_id" = "snapshot"."member_id"
2222 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2223 AND "snapshot"."issue_id" = "issue_id_p"
2224 AND "opinion"."degree" = 1
2225 AND "opinion"."fulfilled" = FALSE
2226 ),
2227 "plus1_fulfilled_count" = (
2228 SELECT coalesce(sum("snapshot"."weight"), 0)
2229 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2230 ON "opinion"."member_id" = "snapshot"."member_id"
2231 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2232 AND "snapshot"."issue_id" = "issue_id_p"
2233 AND "opinion"."degree" = 1
2234 AND "opinion"."fulfilled" = TRUE
2235 ),
2236 "plus2_unfulfilled_count" = (
2237 SELECT coalesce(sum("snapshot"."weight"), 0)
2238 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2239 ON "opinion"."member_id" = "snapshot"."member_id"
2240 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2241 AND "snapshot"."issue_id" = "issue_id_p"
2242 AND "opinion"."degree" = 2
2243 AND "opinion"."fulfilled" = FALSE
2244 ),
2245 "plus2_fulfilled_count" = (
2246 SELECT coalesce(sum("snapshot"."weight"), 0)
2247 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2248 ON "opinion"."member_id" = "snapshot"."member_id"
2249 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2250 AND "snapshot"."issue_id" = "issue_id_p"
2251 AND "opinion"."degree" = 2
2252 AND "opinion"."fulfilled" = TRUE
2253 )
2254 WHERE "suggestion"."id" = "suggestion_id_v";
2255 END LOOP;
2256 END LOOP;
2257 RETURN;
2258 END;
2259 $$;
2261 COMMENT ON FUNCTION "create_snapshot"
2262 ( "issue"."id"%TYPE )
2263 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.';
2266 CREATE FUNCTION "set_snapshot_event"
2267 ( "issue_id_p" "issue"."id"%TYPE,
2268 "event_p" "snapshot_event" )
2269 RETURNS VOID
2270 LANGUAGE 'plpgsql' VOLATILE AS $$
2271 BEGIN
2272 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
2273 WHERE "id" = "issue_id_p";
2274 UPDATE "direct_population_snapshot" SET "event" = "event_p"
2275 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
2276 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
2277 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
2278 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
2279 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
2280 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
2281 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
2282 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
2283 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
2284 RETURN;
2285 END;
2286 $$;
2288 COMMENT ON FUNCTION "set_snapshot_event"
2289 ( "issue"."id"%TYPE,
2290 "snapshot_event" )
2291 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
2295 ---------------------
2296 -- Freezing issues --
2297 ---------------------
2299 CREATE FUNCTION "freeze_after_snapshot"
2300 ( "issue_id_p" "issue"."id"%TYPE )
2301 RETURNS VOID
2302 LANGUAGE 'plpgsql' VOLATILE AS $$
2303 DECLARE
2304 "issue_row" "issue"%ROWTYPE;
2305 "policy_row" "policy"%ROWTYPE;
2306 "initiative_row" "initiative"%ROWTYPE;
2307 BEGIN
2308 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2309 SELECT * INTO "policy_row"
2310 FROM "policy" WHERE "id" = "issue_row"."policy_id";
2311 PERFORM "set_snapshot_event"("issue_id_p", 'start_of_voting');
2312 UPDATE "issue" SET
2313 "accepted" = coalesce("accepted", now()),
2314 "half_frozen" = coalesce("half_frozen", now()),
2315 "fully_frozen" = now()
2316 WHERE "id" = "issue_id_p";
2317 FOR "initiative_row" IN
2318 SELECT * FROM "initiative"
2319 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
2320 LOOP
2321 IF
2322 "initiative_row"."satisfied_supporter_count" > 0 AND
2323 "initiative_row"."satisfied_supporter_count" *
2324 "policy_row"."initiative_quorum_den" >=
2325 "issue_row"."population" * "policy_row"."initiative_quorum_num"
2326 THEN
2327 UPDATE "initiative" SET "admitted" = TRUE
2328 WHERE "id" = "initiative_row"."id";
2329 ELSE
2330 UPDATE "initiative" SET "admitted" = FALSE
2331 WHERE "id" = "initiative_row"."id";
2332 END IF;
2333 END LOOP;
2334 IF NOT EXISTS (
2335 SELECT NULL FROM "initiative"
2336 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
2337 ) THEN
2338 PERFORM "close_voting"("issue_id_p");
2339 END IF;
2340 RETURN;
2341 END;
2342 $$;
2344 COMMENT ON FUNCTION "freeze_after_snapshot"
2345 ( "issue"."id"%TYPE )
2346 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
2349 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
2350 RETURNS VOID
2351 LANGUAGE 'plpgsql' VOLATILE AS $$
2352 DECLARE
2353 "issue_row" "issue"%ROWTYPE;
2354 BEGIN
2355 PERFORM "create_snapshot"("issue_id_p");
2356 PERFORM "freeze_after_snapshot"("issue_id_p");
2357 RETURN;
2358 END;
2359 $$;
2361 COMMENT ON FUNCTION "freeze_after_snapshot"
2362 ( "issue"."id"%TYPE )
2363 IS 'Freeze an issue manually (fully) and start voting';
2367 -----------------------
2368 -- Counting of votes --
2369 -----------------------
2372 CREATE FUNCTION "weight_of_added_vote_delegations"
2373 ( "issue_id_p" "issue"."id"%TYPE,
2374 "member_id_p" "member"."id"%TYPE,
2375 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
2376 RETURNS "direct_voter"."weight"%TYPE
2377 LANGUAGE 'plpgsql' VOLATILE AS $$
2378 DECLARE
2379 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2380 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
2381 "weight_v" INT4;
2382 "sub_weight_v" INT4;
2383 BEGIN
2384 "weight_v" := 0;
2385 FOR "issue_delegation_row" IN
2386 SELECT * FROM "issue_delegation"
2387 WHERE "trustee_id" = "member_id_p"
2388 AND "issue_id" = "issue_id_p"
2389 LOOP
2390 IF NOT EXISTS (
2391 SELECT NULL FROM "direct_voter"
2392 WHERE "member_id" = "issue_delegation_row"."truster_id"
2393 AND "issue_id" = "issue_id_p"
2394 ) AND NOT EXISTS (
2395 SELECT NULL FROM "delegating_voter"
2396 WHERE "member_id" = "issue_delegation_row"."truster_id"
2397 AND "issue_id" = "issue_id_p"
2398 ) THEN
2399 "delegate_member_ids_v" :=
2400 "member_id_p" || "delegate_member_ids_p";
2401 INSERT INTO "delegating_voter" (
2402 "issue_id",
2403 "member_id",
2404 "scope",
2405 "delegate_member_ids"
2406 ) VALUES (
2407 "issue_id_p",
2408 "issue_delegation_row"."truster_id",
2409 "issue_delegation_row"."scope",
2410 "delegate_member_ids_v"
2411 );
2412 "sub_weight_v" := 1 +
2413 "weight_of_added_vote_delegations"(
2414 "issue_id_p",
2415 "issue_delegation_row"."truster_id",
2416 "delegate_member_ids_v"
2417 );
2418 UPDATE "delegating_voter"
2419 SET "weight" = "sub_weight_v"
2420 WHERE "issue_id" = "issue_id_p"
2421 AND "member_id" = "issue_delegation_row"."truster_id";
2422 "weight_v" := "weight_v" + "sub_weight_v";
2423 END IF;
2424 END LOOP;
2425 RETURN "weight_v";
2426 END;
2427 $$;
2429 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
2430 ( "issue"."id"%TYPE,
2431 "member"."id"%TYPE,
2432 "delegating_voter"."delegate_member_ids"%TYPE )
2433 IS 'Helper function for "add_vote_delegations" function';
2436 CREATE FUNCTION "add_vote_delegations"
2437 ( "issue_id_p" "issue"."id"%TYPE )
2438 RETURNS VOID
2439 LANGUAGE 'plpgsql' VOLATILE AS $$
2440 DECLARE
2441 "member_id_v" "member"."id"%TYPE;
2442 BEGIN
2443 FOR "member_id_v" IN
2444 SELECT "member_id" FROM "direct_voter"
2445 WHERE "issue_id" = "issue_id_p"
2446 LOOP
2447 UPDATE "direct_voter" SET
2448 "weight" = "weight" + "weight_of_added_vote_delegations"(
2449 "issue_id_p",
2450 "member_id_v",
2451 '{}'
2452 )
2453 WHERE "member_id" = "member_id_v"
2454 AND "issue_id" = "issue_id_p";
2455 END LOOP;
2456 RETURN;
2457 END;
2458 $$;
2460 COMMENT ON FUNCTION "add_vote_delegations"
2461 ( "issue_id_p" "issue"."id"%TYPE )
2462 IS 'Helper function for "close_voting" function';
2465 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
2466 RETURNS VOID
2467 LANGUAGE 'plpgsql' VOLATILE AS $$
2468 DECLARE
2469 "issue_row" "issue"%ROWTYPE;
2470 "member_id_v" "member"."id"%TYPE;
2471 BEGIN
2472 PERFORM "global_lock"();
2473 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2474 DELETE FROM "delegating_voter"
2475 WHERE "issue_id" = "issue_id_p";
2476 DELETE FROM "direct_voter"
2477 WHERE "issue_id" = "issue_id_p"
2478 AND "autoreject" = TRUE;
2479 DELETE FROM "direct_voter" USING "member"
2480 WHERE "direct_voter"."member_id" = "member"."id"
2481 AND "direct_voter"."issue_id" = "issue_id_p"
2482 AND "member"."active" = FALSE;
2483 UPDATE "direct_voter" SET "weight" = 1
2484 WHERE "issue_id" = "issue_id_p";
2485 PERFORM "add_vote_delegations"("issue_id_p");
2486 FOR "member_id_v" IN
2487 SELECT "interest"."member_id"
2488 FROM "interest"
2489 LEFT JOIN "direct_voter"
2490 ON "interest"."member_id" = "direct_voter"."member_id"
2491 AND "interest"."issue_id" = "direct_voter"."issue_id"
2492 LEFT JOIN "delegating_voter"
2493 ON "interest"."member_id" = "delegating_voter"."member_id"
2494 AND "interest"."issue_id" = "delegating_voter"."issue_id"
2495 WHERE "interest"."issue_id" = "issue_id_p"
2496 AND "interest"."autoreject" = TRUE
2497 AND "direct_voter"."member_id" ISNULL
2498 AND "delegating_voter"."member_id" ISNULL
2499 UNION SELECT "membership"."member_id"
2500 FROM "membership"
2501 LEFT JOIN "interest"
2502 ON "membership"."member_id" = "interest"."member_id"
2503 AND "interest"."issue_id" = "issue_id_p"
2504 LEFT JOIN "direct_voter"
2505 ON "membership"."member_id" = "direct_voter"."member_id"
2506 AND "direct_voter"."issue_id" = "issue_id_p"
2507 LEFT JOIN "delegating_voter"
2508 ON "membership"."member_id" = "delegating_voter"."member_id"
2509 AND "delegating_voter"."issue_id" = "issue_id_p"
2510 WHERE "membership"."area_id" = "issue_row"."area_id"
2511 AND "membership"."autoreject" = TRUE
2512 AND "interest"."autoreject" ISNULL
2513 AND "direct_voter"."member_id" ISNULL
2514 AND "delegating_voter"."member_id" ISNULL
2515 LOOP
2516 INSERT INTO "direct_voter"
2517 ("member_id", "issue_id", "weight", "autoreject") VALUES
2518 ("member_id_v", "issue_id_p", 1, TRUE);
2519 INSERT INTO "vote" (
2520 "member_id",
2521 "issue_id",
2522 "initiative_id",
2523 "grade"
2524 ) SELECT
2525 "member_id_v" AS "member_id",
2526 "issue_id_p" AS "issue_id",
2527 "id" AS "initiative_id",
2528 -1 AS "grade"
2529 FROM "initiative" WHERE "issue_id" = "issue_id_p";
2530 END LOOP;
2531 PERFORM "add_vote_delegations"("issue_id_p");
2532 UPDATE "issue" SET
2533 "voter_count" = (
2534 SELECT coalesce(sum("weight"), 0)
2535 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
2536 )
2537 WHERE "id" = "issue_id_p";
2538 UPDATE "initiative" SET
2539 "positive_votes" = "vote_counts"."positive_votes",
2540 "negative_votes" = "vote_counts"."negative_votes",
2541 "agreed" = CASE WHEN "majority_strict" THEN
2542 "vote_counts"."positive_votes" * "majority_den" >
2543 "majority_num" *
2544 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
2545 ELSE
2546 "vote_counts"."positive_votes" * "majority_den" >=
2547 "majority_num" *
2548 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
2549 END
2550 FROM
2551 ( SELECT
2552 "initiative"."id" AS "initiative_id",
2553 coalesce(
2554 sum(
2555 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
2556 ),
2557 0
2558 ) AS "positive_votes",
2559 coalesce(
2560 sum(
2561 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
2562 ),
2563 0
2564 ) AS "negative_votes"
2565 FROM "initiative"
2566 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
2567 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
2568 LEFT JOIN "direct_voter"
2569 ON "direct_voter"."issue_id" = "initiative"."issue_id"
2570 LEFT JOIN "vote"
2571 ON "vote"."initiative_id" = "initiative"."id"
2572 AND "vote"."member_id" = "direct_voter"."member_id"
2573 WHERE "initiative"."issue_id" = "issue_id_p"
2574 AND "initiative"."admitted" -- NOTE: NULL case is handled too
2575 GROUP BY "initiative"."id"
2576 ) AS "vote_counts",
2577 "issue",
2578 "policy"
2579 WHERE "vote_counts"."initiative_id" = "initiative"."id"
2580 AND "issue"."id" = "initiative"."issue_id"
2581 AND "policy"."id" = "issue"."policy_id";
2582 UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p";
2583 END;
2584 $$;
2586 COMMENT ON FUNCTION "close_voting"
2587 ( "issue"."id"%TYPE )
2588 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.';
2591 CREATE FUNCTION "init_array"("dim_p" INTEGER)
2592 RETURNS INT4[]
2593 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2594 DECLARE
2595 "i" INTEGER;
2596 "ary_text_v" TEXT;
2597 BEGIN
2598 IF "dim_p" >= 1 THEN
2599 "ary_text_v" := '{NULL';
2600 "i" := "dim_p";
2601 LOOP
2602 "i" := "i" - 1;
2603 EXIT WHEN "i" = 0;
2604 "ary_text_v" := "ary_text_v" || ',NULL';
2605 END LOOP;
2606 "ary_text_v" := "ary_text_v" || '}';
2607 RETURN "ary_text_v"::INT4[][];
2608 ELSE
2609 RAISE EXCEPTION 'Dimension needs to be at least 1.';
2610 END IF;
2611 END;
2612 $$;
2614 COMMENT ON FUNCTION "init_array"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
2617 CREATE FUNCTION "init_square_matrix"("dim_p" INTEGER)
2618 RETURNS INT4[][]
2619 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2620 DECLARE
2621 "i" INTEGER;
2622 "row_text_v" TEXT;
2623 "ary_text_v" TEXT;
2624 BEGIN
2625 IF "dim_p" >= 1 THEN
2626 "row_text_v" := '{NULL';
2627 "i" := "dim_p";
2628 LOOP
2629 "i" := "i" - 1;
2630 EXIT WHEN "i" = 0;
2631 "row_text_v" := "row_text_v" || ',NULL';
2632 END LOOP;
2633 "row_text_v" := "row_text_v" || '}';
2634 "ary_text_v" := '{' || "row_text_v";
2635 "i" := "dim_p";
2636 LOOP
2637 "i" := "i" - 1;
2638 EXIT WHEN "i" = 0;
2639 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
2640 END LOOP;
2641 "ary_text_v" := "ary_text_v" || '}';
2642 RETURN "ary_text_v"::INT4[][];
2643 ELSE
2644 RAISE EXCEPTION 'Dimension needs to be at least 1.';
2645 END IF;
2646 END;
2647 $$;
2649 COMMENT ON FUNCTION "init_square_matrix"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
2652 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
2653 RETURNS VOID
2654 LANGUAGE 'plpgsql' VOLATILE AS $$
2655 DECLARE
2656 "dimension_v" INTEGER;
2657 "matrix" INT4[][];
2658 "i" INTEGER;
2659 "j" INTEGER;
2660 "k" INTEGER;
2661 "battle_row" "battle"%ROWTYPE;
2662 "rank_ary" INT4[];
2663 "rank_v" INT4;
2664 "done_v" INTEGER;
2665 "winners_ary" INTEGER[];
2666 "initiative_id_v" "initiative"."id"%TYPE;
2667 BEGIN
2668 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2669 -- Prepare matrix for Schulze-Method:
2670 SELECT count(1) INTO "dimension_v" FROM "initiative"
2671 WHERE "issue_id" = "issue_id_p" AND "agreed";
2672 IF "dimension_v" = 1 THEN
2673 UPDATE "initiative" SET "rank" = 1
2674 WHERE "issue_id" = "issue_id_p" AND "agreed";
2675 ELSIF "dimension_v" > 1 THEN
2676 "matrix" := "init_square_matrix"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2677 "i" := 1;
2678 "j" := 2;
2679 -- Fill matrix with data from "battle" view
2680 FOR "battle_row" IN
2681 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
2682 ORDER BY "winning_initiative_id", "losing_initiative_id"
2683 LOOP
2684 "matrix"["i"]["j"] := "battle_row"."count";
2685 IF "j" = "dimension_v" THEN
2686 "i" := "i" + 1;
2687 "j" := 1;
2688 ELSE
2689 "j" := "j" + 1;
2690 IF "j" = "i" THEN
2691 "j" := "j" + 1;
2692 END IF;
2693 END IF;
2694 END LOOP;
2695 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
2696 RAISE EXCEPTION 'Wrong battle count (should not happen)';
2697 END IF;
2698 -- Delete losers from matrix:
2699 "i" := 1;
2700 LOOP
2701 "j" := "i" + 1;
2702 LOOP
2703 IF "i" != "j" THEN
2704 IF "matrix"["i"]["j"] < "matrix"["j"]["i"] THEN
2705 "matrix"["i"]["j"] := 0;
2706 ELSIF matrix[j][i] < matrix[i][j] THEN
2707 "matrix"["j"]["i"] := 0;
2708 ELSE
2709 "matrix"["i"]["j"] := 0;
2710 "matrix"["j"]["i"] := 0;
2711 END IF;
2712 END IF;
2713 EXIT WHEN "j" = "dimension_v";
2714 "j" := "j" + 1;
2715 END LOOP;
2716 EXIT WHEN "i" = "dimension_v" - 1;
2717 "i" := "i" + 1;
2718 END LOOP;
2719 -- Find best paths:
2720 "i" := 1;
2721 LOOP
2722 "j" := 1;
2723 LOOP
2724 IF "i" != "j" THEN
2725 "k" := 1;
2726 LOOP
2727 IF "i" != "k" AND "j" != "k" THEN
2728 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
2729 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
2730 "matrix"["j"]["k"] := "matrix"["j"]["i"];
2731 END IF;
2732 ELSE
2733 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
2734 "matrix"["j"]["k"] := "matrix"["i"]["k"];
2735 END IF;
2736 END IF;
2737 END IF;
2738 EXIT WHEN "k" = "dimension_v";
2739 "k" := "k" + 1;
2740 END LOOP;
2741 END IF;
2742 EXIT WHEN "j" = "dimension_v";
2743 "j" := "j" + 1;
2744 END LOOP;
2745 EXIT WHEN "i" = "dimension_v";
2746 "i" := "i" + 1;
2747 END LOOP;
2748 -- Determine order of winners:
2749 "rank_ary" := "init_array"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2750 "rank_v" := 1;
2751 "done_v" := 0;
2752 LOOP
2753 "winners_ary" := '{}';
2754 "i" := 1;
2755 LOOP
2756 IF "rank_ary"["i"] ISNULL THEN
2757 "j" := 1;
2758 LOOP
2759 IF
2760 "i" != "j" AND
2761 "rank_ary"["j"] ISNULL AND
2762 "matrix"["j"]["i"] > "matrix"["i"]["j"]
2763 THEN
2764 -- someone else is better
2765 EXIT;
2766 END IF;
2767 IF "j" = "dimension_v" THEN
2768 -- noone is better
2769 "winners_ary" := "winners_ary" || "i";
2770 EXIT;
2771 END IF;
2772 "j" := "j" + 1;
2773 END LOOP;
2774 END IF;
2775 EXIT WHEN "i" = "dimension_v";
2776 "i" := "i" + 1;
2777 END LOOP;
2778 "i" := 1;
2779 LOOP
2780 "rank_ary"["winners_ary"["i"]] := "rank_v";
2781 "done_v" := "done_v" + 1;
2782 EXIT WHEN "i" = array_upper("winners_ary", 1);
2783 "i" := "i" + 1;
2784 END LOOP;
2785 EXIT WHEN "done_v" = "dimension_v";
2786 "rank_v" := "rank_v" + 1;
2787 END LOOP;
2788 -- write preliminary ranks:
2789 "i" := 1;
2790 FOR "initiative_id_v" IN
2791 SELECT "id" FROM "initiative"
2792 WHERE "issue_id" = "issue_id_p" AND "agreed"
2793 ORDER BY "id"
2794 LOOP
2795 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
2796 WHERE "id" = "initiative_id_v";
2797 "i" := "i" + 1;
2798 END LOOP;
2799 IF "i" != "dimension_v" + 1 THEN
2800 RAISE EXCEPTION 'Wrong winner count (should not happen)';
2801 END IF;
2802 -- straighten ranks (start counting with 1, no equal ranks):
2803 "rank_v" := 1;
2804 FOR "initiative_id_v" IN
2805 SELECT "id" FROM "initiative"
2806 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
2807 ORDER BY
2808 "rank",
2809 "vote_ratio"("positive_votes", "negative_votes") DESC,
2810 "id"
2811 LOOP
2812 UPDATE "initiative" SET "rank" = "rank_v"
2813 WHERE "id" = "initiative_id_v";
2814 "rank_v" := "rank_v" + 1;
2815 END LOOP;
2816 END IF;
2817 -- mark issue as finished
2818 UPDATE "issue" SET "ranks_available" = TRUE
2819 WHERE "id" = "issue_id_p";
2820 RETURN;
2821 END;
2822 $$;
2824 COMMENT ON FUNCTION "calculate_ranks"
2825 ( "issue"."id"%TYPE )
2826 IS 'Determine ranking (Votes have to be counted first)';
2830 -----------------------------
2831 -- Automatic state changes --
2832 -----------------------------
2835 CREATE FUNCTION "check_issue"
2836 ( "issue_id_p" "issue"."id"%TYPE )
2837 RETURNS VOID
2838 LANGUAGE 'plpgsql' VOLATILE AS $$
2839 DECLARE
2840 "issue_row" "issue"%ROWTYPE;
2841 "policy_row" "policy"%ROWTYPE;
2842 "voting_requested_v" BOOLEAN;
2843 BEGIN
2844 PERFORM "global_lock"();
2845 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2846 IF "issue_row"."closed" ISNULL THEN
2847 SELECT * INTO "policy_row" FROM "policy"
2848 WHERE "id" = "issue_row"."policy_id";
2849 IF "issue_row"."fully_frozen" ISNULL THEN
2850 PERFORM "create_snapshot"("issue_id_p");
2851 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2852 END IF;
2853 IF "issue_row"."accepted" ISNULL THEN
2854 IF EXISTS (
2855 SELECT NULL FROM "initiative"
2856 WHERE "issue_id" = "issue_id_p"
2857 AND "supporter_count" > 0
2858 AND "supporter_count" * "policy_row"."issue_quorum_den"
2859 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
2860 ) THEN
2861 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
2862 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later
2863 UPDATE "issue" SET "accepted" = "issue_row"."accepted"
2864 WHERE "id" = "issue_row"."id";
2865 ELSIF
2866 now() >= "issue_row"."created" + "policy_row"."admission_time"
2867 THEN
2868 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
2869 UPDATE "issue" SET "closed" = now()
2870 WHERE "id" = "issue_row"."id";
2871 END IF;
2872 END IF;
2873 IF
2874 "issue_row"."accepted" NOTNULL AND
2875 "issue_row"."half_frozen" ISNULL
2876 THEN
2877 SELECT
2878 CASE
2879 WHEN "vote_now" * 2 > "issue_row"."population" THEN
2880 TRUE
2881 WHEN "vote_later" * 2 > "issue_row"."population" THEN
2882 FALSE
2883 ELSE NULL
2884 END
2885 INTO "voting_requested_v"
2886 FROM "issue" WHERE "id" = "issue_id_p";
2887 IF
2888 "voting_requested_v" OR (
2889 "voting_requested_v" ISNULL AND
2890 now() >= "issue_row"."accepted" + "policy_row"."discussion_time"
2891 )
2892 THEN
2893 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later
2894 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
2895 WHERE "id" = "issue_row"."id";
2896 END IF;
2897 END IF;
2898 IF
2899 "issue_row"."half_frozen" NOTNULL AND
2900 "issue_row"."fully_frozen" ISNULL AND
2901 now() >= "issue_row"."half_frozen" + "policy_row"."verification_time"
2902 THEN
2903 PERFORM "freeze_after_snapshot"("issue_id_p");
2904 -- "issue" might change, thus "issue_row" has to be updated below
2905 END IF;
2906 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2907 IF
2908 "issue_row"."closed" ISNULL AND
2909 "issue_row"."fully_frozen" NOTNULL AND
2910 now() >= "issue_row"."fully_frozen" + "policy_row"."voting_time"
2911 THEN
2912 PERFORM "close_voting"("issue_id_p");
2913 END IF;
2914 END IF;
2915 RETURN;
2916 END;
2917 $$;
2919 COMMENT ON FUNCTION "check_issue"
2920 ( "issue"."id"%TYPE )
2921 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.';
2924 CREATE FUNCTION "check_everything"()
2925 RETURNS VOID
2926 LANGUAGE 'plpgsql' VOLATILE AS $$
2927 DECLARE
2928 "issue_id_v" "issue"."id"%TYPE;
2929 BEGIN
2930 DELETE FROM "expired_session";
2931 PERFORM "calculate_member_counts"();
2932 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
2933 PERFORM "check_issue"("issue_id_v");
2934 END LOOP;
2935 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
2936 PERFORM "calculate_ranks"("issue_id_v");
2937 END LOOP;
2938 RETURN;
2939 END;
2940 $$;
2942 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.';
2946 ------------------------------
2947 -- Deletion of private data --
2948 ------------------------------
2951 CREATE FUNCTION "delete_private_data"()
2952 RETURNS VOID
2953 LANGUAGE 'plpgsql' VOLATILE AS $$
2954 DECLARE
2955 "issue_id_v" "issue"."id"%TYPE;
2956 BEGIN
2957 UPDATE "member" SET
2958 "login" = 'login' || "id"::text,
2959 "password" = NULL,
2960 "notify_email" = NULL,
2961 "notify_email_unconfirmed" = NULL,
2962 "notify_email_secret" = NULL,
2963 "notify_email_secret_expiry" = NULL,
2964 "password_reset_secret" = NULL,
2965 "password_reset_secret_expiry" = NULL,
2966 "organizational_unit" = NULL,
2967 "internal_posts" = NULL,
2968 "realname" = NULL,
2969 "birthday" = NULL,
2970 "address" = NULL,
2971 "email" = NULL,
2972 "xmpp_address" = NULL,
2973 "website" = NULL,
2974 "phone" = NULL,
2975 "mobile_phone" = NULL,
2976 "profession" = NULL,
2977 "external_memberships" = NULL,
2978 "external_posts" = NULL,
2979 "statement" = NULL;
2980 -- "text_search_data" is updated by triggers
2981 DELETE FROM "session";
2982 DELETE FROM "invite_code";
2983 DELETE FROM "contact";
2984 DELETE FROM "setting";
2985 DELETE FROM "member_image";
2986 DELETE FROM "direct_voter" USING "issue"
2987 WHERE "direct_voter"."issue_id" = "issue"."id"
2988 AND "issue"."closed" ISNULL;
2989 RETURN;
2990 END;
2991 $$;
2993 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.';
2997 COMMIT;