liquid_feedback_core
view core.sql @ 20:3625d841da90
Triggers ensuring that votes can't be modified by a broken frontend after issues have been frozen
| author | jbe |
|---|---|
| date | Sat Feb 06 03:27:32 2010 +0100 (2010-02-06) |
| parents | 549b1a0fc042 |
| children | 406090b1ed8e |
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 -- Ensure that votes are not modified when issues are frozen --
925 ---------------------------------------------------------------
927 -- NOTE: Frontends should ensure this anyway, but in case of programming
928 -- errors the following triggers ensure data integrity.
931 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
932 RETURNS TRIGGER
933 LANGUAGE 'plpgsql' VOLATILE AS $$
934 DECLARE
935 "issue_row" "issue"%ROWTYPE;
936 BEGIN
937 SELECT INTO "issue_row" * FROM "issue"
938 WHERE "id" = NEW."issue_id" FOR SHARE;
939 IF "issue_row"."closed" NOTNULL THEN
940 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
941 END IF;
942 RETURN NULL;
943 END;
944 $$;
946 CREATE TRIGGER "forbid_changes_on_closed_issue"
947 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
948 FOR EACH ROW EXECUTE PROCEDURE
949 "forbid_changes_on_closed_issue_trigger"();
951 CREATE TRIGGER "forbid_changes_on_closed_issue"
952 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
953 FOR EACH ROW EXECUTE PROCEDURE
954 "forbid_changes_on_closed_issue_trigger"();
956 CREATE TRIGGER "forbid_changes_on_closed_issue"
957 AFTER INSERT OR UPDATE OR DELETE ON "vote"
958 FOR EACH ROW EXECUTE PROCEDURE
959 "forbid_changes_on_closed_issue_trigger"();
961 COMMENT ON FUNCTION "forbid_changes_on_closed_issue_trigger"() IS 'Implementation of triggers "forbid_changes_on_closed_issue" on tables "direct_voter", "delegating_voter" and "vote"';
962 COMMENT ON TRIGGER "forbid_changes_on_closed_issue" ON "direct_voter" IS 'Ensures that frontends can''t tamper with votings of closed issues, in case of programming errors';
963 COMMENT ON TRIGGER "forbid_changes_on_closed_issue" ON "delegating_voter" IS 'Ensures that frontends can''t tamper with votings of closed issues, in case of programming errors';
964 COMMENT ON TRIGGER "forbid_changes_on_closed_issue" ON "vote" IS 'Ensures that frontends can''t tamper with votings of closed issues, in case of programming errors';
968 --------------------------------------------------------------------
969 -- Auto-retrieval of fields only needed for referential integrity --
970 --------------------------------------------------------------------
973 CREATE FUNCTION "autofill_issue_id_trigger"()
974 RETURNS TRIGGER
975 LANGUAGE 'plpgsql' VOLATILE AS $$
976 BEGIN
977 IF NEW."issue_id" ISNULL THEN
978 SELECT "issue_id" INTO NEW."issue_id"
979 FROM "initiative" WHERE "id" = NEW."initiative_id";
980 END IF;
981 RETURN NEW;
982 END;
983 $$;
985 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
986 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
988 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
989 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
991 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
992 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
993 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
996 CREATE FUNCTION "autofill_initiative_id_trigger"()
997 RETURNS TRIGGER
998 LANGUAGE 'plpgsql' VOLATILE AS $$
999 BEGIN
1000 IF NEW."initiative_id" ISNULL THEN
1001 SELECT "initiative_id" INTO NEW."initiative_id"
1002 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1003 END IF;
1004 RETURN NEW;
1005 END;
1006 $$;
1008 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1009 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1011 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1012 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1016 -----------------------------------------------------
1017 -- Automatic calculation of certain default values --
1018 -----------------------------------------------------
1020 CREATE FUNCTION "copy_autoreject_trigger"()
1021 RETURNS TRIGGER
1022 LANGUAGE 'plpgsql' VOLATILE AS $$
1023 BEGIN
1024 IF NEW."autoreject" ISNULL THEN
1025 SELECT "membership"."autoreject" INTO NEW."autoreject"
1026 FROM "issue" JOIN "membership"
1027 ON "issue"."area_id" = "membership"."area_id"
1028 WHERE "issue"."id" = NEW."issue_id"
1029 AND "membership"."member_id" = NEW."member_id";
1030 END IF;
1031 IF NEW."autoreject" ISNULL THEN
1032 NEW."autoreject" := FALSE;
1033 END IF;
1034 RETURN NEW;
1035 END;
1036 $$;
1038 CREATE TRIGGER "copy_autoreject" BEFORE INSERT OR UPDATE ON "interest"
1039 FOR EACH ROW EXECUTE PROCEDURE "copy_autoreject_trigger"();
1041 COMMENT ON FUNCTION "copy_autoreject_trigger"() IS 'Implementation of trigger "copy_autoreject" on table "interest"';
1042 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';
1045 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
1046 RETURNS TRIGGER
1047 LANGUAGE 'plpgsql' VOLATILE AS $$
1048 BEGIN
1049 IF NEW."draft_id" ISNULL THEN
1050 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1051 WHERE "initiative_id" = NEW."initiative_id";
1052 END IF;
1053 RETURN NEW;
1054 END;
1055 $$;
1057 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1058 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
1060 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
1061 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';
1065 ----------------------------------------
1066 -- Automatic creation of dependencies --
1067 ----------------------------------------
1069 CREATE FUNCTION "autocreate_interest_trigger"()
1070 RETURNS TRIGGER
1071 LANGUAGE 'plpgsql' VOLATILE AS $$
1072 BEGIN
1073 IF NOT EXISTS (
1074 SELECT NULL FROM "initiative" JOIN "interest"
1075 ON "initiative"."issue_id" = "interest"."issue_id"
1076 WHERE "initiative"."id" = NEW."initiative_id"
1077 AND "interest"."member_id" = NEW."member_id"
1078 ) THEN
1079 BEGIN
1080 INSERT INTO "interest" ("issue_id", "member_id")
1081 SELECT "issue_id", NEW."member_id"
1082 FROM "initiative" WHERE "id" = NEW."initiative_id";
1083 EXCEPTION WHEN unique_violation THEN END;
1084 END IF;
1085 RETURN NEW;
1086 END;
1087 $$;
1089 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1090 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1092 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1093 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';
1096 CREATE FUNCTION "autocreate_supporter_trigger"()
1097 RETURNS TRIGGER
1098 LANGUAGE 'plpgsql' VOLATILE AS $$
1099 BEGIN
1100 IF NOT EXISTS (
1101 SELECT NULL FROM "suggestion" JOIN "supporter"
1102 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1103 WHERE "suggestion"."id" = NEW."suggestion_id"
1104 AND "supporter"."member_id" = NEW."member_id"
1105 ) THEN
1106 BEGIN
1107 INSERT INTO "supporter" ("initiative_id", "member_id")
1108 SELECT "initiative_id", NEW."member_id"
1109 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1110 EXCEPTION WHEN unique_violation THEN END;
1111 END IF;
1112 RETURN NEW;
1113 END;
1114 $$;
1116 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1117 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1119 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1120 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.';
1124 ------------------------------------------
1125 -- Views and helper functions for views --
1126 ------------------------------------------
1129 CREATE VIEW "global_delegation" AS
1130 SELECT
1131 "delegation"."id",
1132 "delegation"."truster_id",
1133 "delegation"."trustee_id"
1134 FROM "delegation" JOIN "member"
1135 ON "delegation"."trustee_id" = "member"."id"
1136 WHERE "delegation"."scope" = 'global' AND "member"."active";
1138 COMMENT ON VIEW "global_delegation" IS 'Global delegations to active members';
1141 CREATE VIEW "area_delegation" AS
1142 SELECT "subquery".* FROM (
1143 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1144 "area"."id" AS "area_id",
1145 "delegation"."id",
1146 "delegation"."truster_id",
1147 "delegation"."trustee_id",
1148 "delegation"."scope"
1149 FROM "area" JOIN "delegation"
1150 ON "delegation"."scope" = 'global'
1151 OR "delegation"."area_id" = "area"."id"
1152 ORDER BY
1153 "area"."id",
1154 "delegation"."truster_id",
1155 "delegation"."scope" DESC
1156 ) AS "subquery"
1157 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
1158 WHERE "member"."active";
1160 COMMENT ON VIEW "area_delegation" IS 'Active delegations for areas';
1163 CREATE VIEW "issue_delegation" AS
1164 SELECT "subquery".* FROM (
1165 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1166 "issue"."id" AS "issue_id",
1167 "delegation"."id",
1168 "delegation"."truster_id",
1169 "delegation"."trustee_id",
1170 "delegation"."scope"
1171 FROM "issue" JOIN "delegation"
1172 ON "delegation"."scope" = 'global'
1173 OR "delegation"."area_id" = "issue"."area_id"
1174 OR "delegation"."issue_id" = "issue"."id"
1175 ORDER BY
1176 "issue"."id",
1177 "delegation"."truster_id",
1178 "delegation"."scope" DESC
1179 ) AS "subquery"
1180 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
1181 WHERE "member"."active";
1183 COMMENT ON VIEW "issue_delegation" IS 'Active delegations for issues';
1186 CREATE FUNCTION "membership_weight_with_skipping"
1187 ( "area_id_p" "area"."id"%TYPE,
1188 "member_id_p" "member"."id"%TYPE,
1189 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1190 RETURNS INT4
1191 LANGUAGE 'plpgsql' STABLE AS $$
1192 DECLARE
1193 "sum_v" INT4;
1194 "delegation_row" "area_delegation"%ROWTYPE;
1195 BEGIN
1196 "sum_v" := 1;
1197 FOR "delegation_row" IN
1198 SELECT "area_delegation".*
1199 FROM "area_delegation" LEFT JOIN "membership"
1200 ON "membership"."area_id" = "area_id_p"
1201 AND "membership"."member_id" = "area_delegation"."truster_id"
1202 WHERE "area_delegation"."area_id" = "area_id_p"
1203 AND "area_delegation"."trustee_id" = "member_id_p"
1204 AND "membership"."member_id" ISNULL
1205 LOOP
1206 IF NOT
1207 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1208 THEN
1209 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1210 "area_id_p",
1211 "delegation_row"."truster_id",
1212 "skip_member_ids_p" || "delegation_row"."truster_id"
1213 );
1214 END IF;
1215 END LOOP;
1216 RETURN "sum_v";
1217 END;
1218 $$;
1220 COMMENT ON FUNCTION "membership_weight_with_skipping"
1221 ( "area"."id"%TYPE,
1222 "member"."id"%TYPE,
1223 INT4[] )
1224 IS 'Helper function for "membership_weight" function';
1227 CREATE FUNCTION "membership_weight"
1228 ( "area_id_p" "area"."id"%TYPE,
1229 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1230 RETURNS INT4
1231 LANGUAGE 'plpgsql' STABLE AS $$
1232 BEGIN
1233 RETURN "membership_weight_with_skipping"(
1234 "area_id_p",
1235 "member_id_p",
1236 ARRAY["member_id_p"]
1237 );
1238 END;
1239 $$;
1241 COMMENT ON FUNCTION "membership_weight"
1242 ( "area"."id"%TYPE,
1243 "member"."id"%TYPE )
1244 IS 'Calculates the potential voting weight of a member in a given area';
1247 CREATE VIEW "member_count_view" AS
1248 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1250 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1253 CREATE VIEW "area_member_count" AS
1254 SELECT
1255 "area"."id" AS "area_id",
1256 count("member"."id") AS "direct_member_count",
1257 coalesce(
1258 sum(
1259 CASE WHEN "member"."id" NOTNULL THEN
1260 "membership_weight"("area"."id", "member"."id")
1261 ELSE 0 END
1262 )
1263 ) AS "member_weight",
1264 coalesce(
1265 sum(
1266 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
1267 "membership_weight"("area"."id", "member"."id")
1268 ELSE 0 END
1269 )
1270 ) AS "autoreject_weight"
1271 FROM "area"
1272 LEFT JOIN "membership"
1273 ON "area"."id" = "membership"."area_id"
1274 LEFT JOIN "member"
1275 ON "membership"."member_id" = "member"."id"
1276 AND "member"."active"
1277 GROUP BY "area"."id";
1279 COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"';
1282 CREATE VIEW "opening_draft" AS
1283 SELECT "draft".* FROM (
1284 SELECT
1285 "initiative"."id" AS "initiative_id",
1286 min("draft"."id") AS "draft_id"
1287 FROM "initiative" JOIN "draft"
1288 ON "initiative"."id" = "draft"."initiative_id"
1289 GROUP BY "initiative"."id"
1290 ) AS "subquery"
1291 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1293 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1296 CREATE VIEW "current_draft" AS
1297 SELECT "draft".* FROM (
1298 SELECT
1299 "initiative"."id" AS "initiative_id",
1300 max("draft"."id") AS "draft_id"
1301 FROM "initiative" JOIN "draft"
1302 ON "initiative"."id" = "draft"."initiative_id"
1303 GROUP BY "initiative"."id"
1304 ) AS "subquery"
1305 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1307 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1310 CREATE VIEW "critical_opinion" AS
1311 SELECT * FROM "opinion"
1312 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
1313 OR ("degree" = -2 AND "fulfilled" = TRUE);
1315 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
1318 CREATE VIEW "battle" AS
1319 SELECT
1320 "issue"."id" AS "issue_id",
1321 "winning_initiative"."id" AS "winning_initiative_id",
1322 "losing_initiative"."id" AS "losing_initiative_id",
1323 sum(
1324 CASE WHEN
1325 coalesce("better_vote"."grade", 0) >
1326 coalesce("worse_vote"."grade", 0)
1327 THEN "direct_voter"."weight" ELSE 0 END
1328 ) AS "count"
1329 FROM "issue"
1330 LEFT JOIN "direct_voter"
1331 ON "issue"."id" = "direct_voter"."issue_id"
1332 JOIN "initiative" AS "winning_initiative"
1333 ON "issue"."id" = "winning_initiative"."issue_id"
1334 AND "winning_initiative"."agreed"
1335 JOIN "initiative" AS "losing_initiative"
1336 ON "issue"."id" = "losing_initiative"."issue_id"
1337 AND "losing_initiative"."agreed"
1338 LEFT JOIN "vote" AS "better_vote"
1339 ON "direct_voter"."member_id" = "better_vote"."member_id"
1340 AND "winning_initiative"."id" = "better_vote"."initiative_id"
1341 LEFT JOIN "vote" AS "worse_vote"
1342 ON "direct_voter"."member_id" = "worse_vote"."member_id"
1343 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
1344 WHERE
1345 "winning_initiative"."id" != "losing_initiative"."id"
1346 GROUP BY
1347 "issue"."id",
1348 "winning_initiative"."id",
1349 "losing_initiative"."id";
1351 COMMENT ON VIEW "battle" IS 'Number of members preferring one initiative over another';
1354 CREATE VIEW "expired_session" AS
1355 SELECT * FROM "session" WHERE now() > "expiry";
1357 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
1358 DELETE FROM "session" WHERE "ident" = OLD."ident";
1360 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
1361 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
1364 CREATE VIEW "open_issue" AS
1365 SELECT * FROM "issue" WHERE "closed" ISNULL;
1367 COMMENT ON VIEW "open_issue" IS 'All open issues';
1370 CREATE VIEW "issue_with_ranks_missing" AS
1371 SELECT * FROM "issue"
1372 WHERE "fully_frozen" NOTNULL
1373 AND "closed" NOTNULL
1374 AND "ranks_available" = FALSE;
1376 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
1379 CREATE VIEW "member_contingent" AS
1380 SELECT
1381 "member"."id" AS "member_id",
1382 "contingent"."time_frame",
1383 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
1384 (
1385 SELECT count(1) FROM "draft"
1386 WHERE "draft"."author_id" = "member"."id"
1387 AND "draft"."created" > now() - "contingent"."time_frame"
1388 ) + (
1389 SELECT count(1) FROM "suggestion"
1390 WHERE "suggestion"."author_id" = "member"."id"
1391 AND "suggestion"."created" > now() - "contingent"."time_frame"
1392 )
1393 ELSE NULL END AS "text_entry_count",
1394 "contingent"."text_entry_limit",
1395 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
1396 SELECT count(1) FROM "opening_draft"
1397 WHERE "opening_draft"."author_id" = "member"."id"
1398 AND "opening_draft"."created" > now() - "contingent"."time_frame"
1399 ) ELSE NULL END AS "initiative_count",
1400 "contingent"."initiative_limit"
1401 FROM "member" CROSS JOIN "contingent";
1403 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
1405 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
1406 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
1409 CREATE VIEW "member_contingent_left" AS
1410 SELECT
1411 "member_id",
1412 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
1413 max("initiative_limit" - "initiative_count") AS "initiatives_left"
1414 FROM "member_contingent" GROUP BY "member_id";
1416 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.';
1419 CREATE TYPE "timeline_event" AS ENUM (
1420 'issue_created',
1421 'issue_canceled',
1422 'issue_accepted',
1423 'issue_half_frozen',
1424 'issue_finished_without_voting',
1425 'issue_voting_started',
1426 'issue_finished_after_voting',
1427 'initiative_created',
1428 'initiative_revoked',
1429 'draft_created',
1430 'suggestion_created');
1432 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables';
1435 CREATE VIEW "timeline_issue" AS
1436 SELECT
1437 "created" AS "occurrence",
1438 'issue_created'::"timeline_event" AS "event",
1439 "id" AS "issue_id"
1440 FROM "issue"
1441 UNION ALL
1442 SELECT
1443 "closed" AS "occurrence",
1444 'issue_canceled'::"timeline_event" AS "event",
1445 "id" AS "issue_id"
1446 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
1447 UNION ALL
1448 SELECT
1449 "accepted" AS "occurrence",
1450 'issue_accepted'::"timeline_event" AS "event",
1451 "id" AS "issue_id"
1452 FROM "issue" WHERE "accepted" NOTNULL
1453 UNION ALL
1454 SELECT
1455 "half_frozen" AS "occurrence",
1456 'issue_half_frozen'::"timeline_event" AS "event",
1457 "id" AS "issue_id"
1458 FROM "issue" WHERE "half_frozen" NOTNULL
1459 UNION ALL
1460 SELECT
1461 "fully_frozen" AS "occurrence",
1462 'issue_voting_started'::"timeline_event" AS "event",
1463 "id" AS "issue_id"
1464 FROM "issue"
1465 WHERE "fully_frozen" NOTNULL
1466 AND ("closed" ISNULL OR "closed" != "fully_frozen")
1467 UNION ALL
1468 SELECT
1469 "closed" AS "occurrence",
1470 CASE WHEN "fully_frozen" = "closed" THEN
1471 'issue_finished_without_voting'::"timeline_event"
1472 ELSE
1473 'issue_finished_after_voting'::"timeline_event"
1474 END AS "event",
1475 "id" AS "issue_id"
1476 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
1478 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view';
1481 CREATE VIEW "timeline_initiative" AS
1482 SELECT
1483 "created" AS "occurrence",
1484 'initiative_created'::"timeline_event" AS "event",
1485 "id" AS "initiative_id"
1486 FROM "initiative"
1487 UNION ALL
1488 SELECT
1489 "revoked" AS "occurrence",
1490 'initiative_revoked'::"timeline_event" AS "event",
1491 "id" AS "initiative_id"
1492 FROM "initiative" WHERE "revoked" NOTNULL;
1494 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view';
1497 CREATE VIEW "timeline_draft" AS
1498 SELECT
1499 "created" AS "occurrence",
1500 'draft_created'::"timeline_event" AS "event",
1501 "id" AS "draft_id"
1502 FROM "draft";
1504 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view';
1507 CREATE VIEW "timeline_suggestion" AS
1508 SELECT
1509 "created" AS "occurrence",
1510 'suggestion_created'::"timeline_event" AS "event",
1511 "id" AS "suggestion_id"
1512 FROM "suggestion";
1514 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view';
1517 CREATE VIEW "timeline" AS
1518 SELECT
1519 "occurrence",
1520 "event",
1521 "issue_id",
1522 NULL AS "initiative_id",
1523 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
1524 NULL::INT8 AS "suggestion_id"
1525 FROM "timeline_issue"
1526 UNION ALL
1527 SELECT
1528 "occurrence",
1529 "event",
1530 NULL AS "issue_id",
1531 "initiative_id",
1532 NULL AS "draft_id",
1533 NULL AS "suggestion_id"
1534 FROM "timeline_initiative"
1535 UNION ALL
1536 SELECT
1537 "occurrence",
1538 "event",
1539 NULL AS "issue_id",
1540 NULL AS "initiative_id",
1541 "draft_id",
1542 NULL AS "suggestion_id"
1543 FROM "timeline_draft"
1544 UNION ALL
1545 SELECT
1546 "occurrence",
1547 "event",
1548 NULL AS "issue_id",
1549 NULL AS "initiative_id",
1550 NULL AS "draft_id",
1551 "suggestion_id"
1552 FROM "timeline_suggestion";
1554 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system';
1558 --------------------------------------------------
1559 -- Set returning function for delegation chains --
1560 --------------------------------------------------
1563 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
1564 ('first', 'intermediate', 'last', 'repetition');
1566 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
1569 CREATE TYPE "delegation_chain_row" AS (
1570 "index" INT4,
1571 "member_id" INT4,
1572 "member_active" BOOLEAN,
1573 "participation" BOOLEAN,
1574 "overridden" BOOLEAN,
1575 "scope_in" "delegation_scope",
1576 "scope_out" "delegation_scope",
1577 "loop" "delegation_chain_loop_tag" );
1579 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
1581 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
1582 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';
1583 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
1584 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
1585 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
1586 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
1589 CREATE FUNCTION "delegation_chain"
1590 ( "member_id_p" "member"."id"%TYPE,
1591 "area_id_p" "area"."id"%TYPE,
1592 "issue_id_p" "issue"."id"%TYPE,
1593 "simulate_trustee_id_p" "member"."id"%TYPE )
1594 RETURNS SETOF "delegation_chain_row"
1595 LANGUAGE 'plpgsql' STABLE AS $$
1596 DECLARE
1597 "issue_row" "issue"%ROWTYPE;
1598 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
1599 "loop_member_id_v" "member"."id"%TYPE;
1600 "output_row" "delegation_chain_row";
1601 "output_rows" "delegation_chain_row"[];
1602 "delegation_row" "delegation"%ROWTYPE;
1603 "row_count" INT4;
1604 "i" INT4;
1605 "loop_v" BOOLEAN;
1606 BEGIN
1607 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1608 "visited_member_ids" := '{}';
1609 "loop_member_id_v" := NULL;
1610 "output_rows" := '{}';
1611 "output_row"."index" := 0;
1612 "output_row"."member_id" := "member_id_p";
1613 "output_row"."member_active" := TRUE;
1614 "output_row"."participation" := FALSE;
1615 "output_row"."overridden" := FALSE;
1616 "output_row"."scope_out" := NULL;
1617 LOOP
1618 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
1619 "loop_member_id_v" := "output_row"."member_id";
1620 ELSE
1621 "visited_member_ids" :=
1622 "visited_member_ids" || "output_row"."member_id";
1623 END IF;
1624 IF "output_row"."participation" THEN
1625 "output_row"."overridden" := TRUE;
1626 END IF;
1627 "output_row"."scope_in" := "output_row"."scope_out";
1628 IF EXISTS (
1629 SELECT NULL FROM "member"
1630 WHERE "id" = "output_row"."member_id" AND "active"
1631 ) THEN
1632 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
1633 SELECT * INTO "delegation_row" FROM "delegation"
1634 WHERE "truster_id" = "output_row"."member_id"
1635 AND "scope" = 'global';
1636 ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN
1637 "output_row"."participation" := EXISTS (
1638 SELECT NULL FROM "membership"
1639 WHERE "area_id" = "area_id_p"
1640 AND "member_id" = "output_row"."member_id"
1641 );
1642 SELECT * INTO "delegation_row" FROM "delegation"
1643 WHERE "truster_id" = "output_row"."member_id"
1644 AND ("scope" = 'global' OR "area_id" = "area_id_p")
1645 ORDER BY "scope" DESC;
1646 ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN
1647 "output_row"."participation" := EXISTS (
1648 SELECT NULL FROM "interest"
1649 WHERE "issue_id" = "issue_id_p"
1650 AND "member_id" = "output_row"."member_id"
1651 );
1652 SELECT * INTO "delegation_row" FROM "delegation"
1653 WHERE "truster_id" = "output_row"."member_id"
1654 AND ("scope" = 'global' OR
1655 "area_id" = "issue_row"."area_id" OR
1656 "issue_id" = "issue_id_p"
1657 )
1658 ORDER BY "scope" DESC;
1659 ELSE
1660 RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.';
1661 END IF;
1662 ELSE
1663 "output_row"."member_active" := FALSE;
1664 "output_row"."participation" := FALSE;
1665 "output_row"."scope_out" := NULL;
1666 "delegation_row" := ROW(NULL);
1667 END IF;
1668 IF
1669 "output_row"."member_id" = "member_id_p" AND
1670 "simulate_trustee_id_p" NOTNULL
1671 THEN
1672 "output_row"."scope_out" := CASE
1673 WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global'
1674 WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area'
1675 WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue'
1676 END;
1677 "output_rows" := "output_rows" || "output_row";
1678 "output_row"."member_id" := "simulate_trustee_id_p";
1679 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
1680 "output_row"."scope_out" := "delegation_row"."scope";
1681 "output_rows" := "output_rows" || "output_row";
1682 "output_row"."member_id" := "delegation_row"."trustee_id";
1683 ELSE
1684 "output_row"."scope_out" := NULL;
1685 "output_rows" := "output_rows" || "output_row";
1686 EXIT;
1687 END IF;
1688 EXIT WHEN "loop_member_id_v" NOTNULL;
1689 "output_row"."index" := "output_row"."index" + 1;
1690 END LOOP;
1691 "row_count" := array_upper("output_rows", 1);
1692 "i" := 1;
1693 "loop_v" := FALSE;
1694 LOOP
1695 "output_row" := "output_rows"["i"];
1696 EXIT WHEN "output_row"."member_id" ISNULL;
1697 IF "loop_v" THEN
1698 IF "i" + 1 = "row_count" THEN
1699 "output_row"."loop" := 'last';
1700 ELSIF "i" = "row_count" THEN
1701 "output_row"."loop" := 'repetition';
1702 ELSE
1703 "output_row"."loop" := 'intermediate';
1704 END IF;
1705 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
1706 "output_row"."loop" := 'first';
1707 "loop_v" := TRUE;
1708 END IF;
1709 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
1710 "output_row"."participation" := NULL;
1711 END IF;
1712 RETURN NEXT "output_row";
1713 "i" := "i" + 1;
1714 END LOOP;
1715 RETURN;
1716 END;
1717 $$;
1719 COMMENT ON FUNCTION "delegation_chain"
1720 ( "member"."id"%TYPE,
1721 "area"."id"%TYPE,
1722 "issue"."id"%TYPE,
1723 "member"."id"%TYPE )
1724 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
1726 CREATE FUNCTION "delegation_chain"
1727 ( "member_id_p" "member"."id"%TYPE,
1728 "area_id_p" "area"."id"%TYPE,
1729 "issue_id_p" "issue"."id"%TYPE )
1730 RETURNS SETOF "delegation_chain_row"
1731 LANGUAGE 'plpgsql' STABLE AS $$
1732 DECLARE
1733 "result_row" "delegation_chain_row";
1734 BEGIN
1735 FOR "result_row" IN
1736 SELECT * FROM "delegation_chain"(
1737 "member_id_p", "area_id_p", "issue_id_p", NULL
1738 )
1739 LOOP
1740 RETURN NEXT "result_row";
1741 END LOOP;
1742 RETURN;
1743 END;
1744 $$;
1746 COMMENT ON FUNCTION "delegation_chain"
1747 ( "member"."id"%TYPE,
1748 "area"."id"%TYPE,
1749 "issue"."id"%TYPE )
1750 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
1754 ------------------------------
1755 -- Comparison by vote count --
1756 ------------------------------
1758 CREATE FUNCTION "vote_ratio"
1759 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
1760 "negative_votes_p" "initiative"."negative_votes"%TYPE )
1761 RETURNS FLOAT8
1762 LANGUAGE 'plpgsql' STABLE AS $$
1763 DECLARE
1764 "total_v" INT4;
1765 BEGIN
1766 "total_v" := "positive_votes_p" + "negative_votes_p";
1767 IF "total_v" > 0 THEN
1768 RETURN "positive_votes_p"::FLOAT8 / "total_v"::FLOAT8;
1769 ELSE
1770 RETURN 0.5;
1771 END IF;
1772 END;
1773 $$;
1775 COMMENT ON FUNCTION "vote_ratio"
1776 ( "initiative"."positive_votes"%TYPE,
1777 "initiative"."negative_votes"%TYPE )
1778 IS 'Ratio of positive votes to sum of positive and negative votes; 0.5, if there are neither positive nor negative votes';
1782 ------------------------------------------------
1783 -- Locking for snapshots and voting procedure --
1784 ------------------------------------------------
1786 CREATE FUNCTION "global_lock"() RETURNS VOID
1787 LANGUAGE 'plpgsql' VOLATILE AS $$
1788 BEGIN
1789 -- NOTE: PostgreSQL allows reading, while tables are locked in
1790 -- exclusive move. Transactions should be kept short anyway!
1791 LOCK TABLE "member" IN EXCLUSIVE MODE;
1792 LOCK TABLE "area" IN EXCLUSIVE MODE;
1793 LOCK TABLE "membership" IN EXCLUSIVE MODE;
1794 -- NOTE: "member", "area" and "membership" are locked first to
1795 -- prevent deadlocks in combination with "calculate_member_counts"()
1796 LOCK TABLE "policy" IN EXCLUSIVE MODE;
1797 LOCK TABLE "issue" IN EXCLUSIVE MODE;
1798 LOCK TABLE "initiative" IN EXCLUSIVE MODE;
1799 LOCK TABLE "draft" IN EXCLUSIVE MODE;
1800 LOCK TABLE "suggestion" IN EXCLUSIVE MODE;
1801 LOCK TABLE "interest" IN EXCLUSIVE MODE;
1802 LOCK TABLE "initiator" IN EXCLUSIVE MODE;
1803 LOCK TABLE "supporter" IN EXCLUSIVE MODE;
1804 LOCK TABLE "opinion" IN EXCLUSIVE MODE;
1805 LOCK TABLE "delegation" IN EXCLUSIVE MODE;
1806 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
1807 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
1808 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
1809 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
1810 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
1811 LOCK TABLE "direct_voter" IN EXCLUSIVE MODE;
1812 LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;
1813 LOCK TABLE "vote" IN EXCLUSIVE MODE;
1814 RETURN;
1815 END;
1816 $$;
1818 COMMENT ON FUNCTION "global_lock"() IS 'Locks all tables related to support/voting until end of transaction; read access is still possible though';
1822 -------------------------------
1823 -- Materialize member counts --
1824 -------------------------------
1826 CREATE FUNCTION "calculate_member_counts"()
1827 RETURNS VOID
1828 LANGUAGE 'plpgsql' VOLATILE AS $$
1829 BEGIN
1830 LOCK TABLE "member" IN EXCLUSIVE MODE;
1831 LOCK TABLE "area" IN EXCLUSIVE MODE;
1832 LOCK TABLE "membership" IN EXCLUSIVE MODE;
1833 DELETE FROM "member_count";
1834 INSERT INTO "member_count" ("total_count")
1835 SELECT "total_count" FROM "member_count_view";
1836 UPDATE "area" SET
1837 "direct_member_count" = "view"."direct_member_count",
1838 "member_weight" = "view"."member_weight",
1839 "autoreject_weight" = "view"."autoreject_weight"
1840 FROM "area_member_count" AS "view"
1841 WHERE "view"."area_id" = "area"."id";
1842 RETURN;
1843 END;
1844 $$;
1846 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"';
1850 ------------------------------
1851 -- Calculation of snapshots --
1852 ------------------------------
1854 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
1855 ( "issue_id_p" "issue"."id"%TYPE,
1856 "member_id_p" "member"."id"%TYPE,
1857 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
1858 RETURNS "direct_population_snapshot"."weight"%TYPE
1859 LANGUAGE 'plpgsql' VOLATILE AS $$
1860 DECLARE
1861 "issue_delegation_row" "issue_delegation"%ROWTYPE;
1862 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
1863 "weight_v" INT4;
1864 "sub_weight_v" INT4;
1865 BEGIN
1866 "weight_v" := 0;
1867 FOR "issue_delegation_row" IN
1868 SELECT * FROM "issue_delegation"
1869 WHERE "trustee_id" = "member_id_p"
1870 AND "issue_id" = "issue_id_p"
1871 LOOP
1872 IF NOT EXISTS (
1873 SELECT NULL FROM "direct_population_snapshot"
1874 WHERE "issue_id" = "issue_id_p"
1875 AND "event" = 'periodic'
1876 AND "member_id" = "issue_delegation_row"."truster_id"
1877 ) AND NOT EXISTS (
1878 SELECT NULL FROM "delegating_population_snapshot"
1879 WHERE "issue_id" = "issue_id_p"
1880 AND "event" = 'periodic'
1881 AND "member_id" = "issue_delegation_row"."truster_id"
1882 ) THEN
1883 "delegate_member_ids_v" :=
1884 "member_id_p" || "delegate_member_ids_p";
1885 INSERT INTO "delegating_population_snapshot" (
1886 "issue_id",
1887 "event",
1888 "member_id",
1889 "scope",
1890 "delegate_member_ids"
1891 ) VALUES (
1892 "issue_id_p",
1893 'periodic',
1894 "issue_delegation_row"."truster_id",
1895 "issue_delegation_row"."scope",
1896 "delegate_member_ids_v"
1897 );
1898 "sub_weight_v" := 1 +
1899 "weight_of_added_delegations_for_population_snapshot"(
1900 "issue_id_p",
1901 "issue_delegation_row"."truster_id",
1902 "delegate_member_ids_v"
1903 );
1904 UPDATE "delegating_population_snapshot"
1905 SET "weight" = "sub_weight_v"
1906 WHERE "issue_id" = "issue_id_p"
1907 AND "event" = 'periodic'
1908 AND "member_id" = "issue_delegation_row"."truster_id";
1909 "weight_v" := "weight_v" + "sub_weight_v";
1910 END IF;
1911 END LOOP;
1912 RETURN "weight_v";
1913 END;
1914 $$;
1916 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
1917 ( "issue"."id"%TYPE,
1918 "member"."id"%TYPE,
1919 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
1920 IS 'Helper function for "create_population_snapshot" function';
1923 CREATE FUNCTION "create_population_snapshot"
1924 ( "issue_id_p" "issue"."id"%TYPE )
1925 RETURNS VOID
1926 LANGUAGE 'plpgsql' VOLATILE AS $$
1927 DECLARE
1928 "member_id_v" "member"."id"%TYPE;
1929 BEGIN
1930 DELETE FROM "direct_population_snapshot"
1931 WHERE "issue_id" = "issue_id_p"
1932 AND "event" = 'periodic';
1933 DELETE FROM "delegating_population_snapshot"
1934 WHERE "issue_id" = "issue_id_p"
1935 AND "event" = 'periodic';
1936 INSERT INTO "direct_population_snapshot"
1937 ("issue_id", "event", "member_id", "interest_exists")
1938 SELECT DISTINCT ON ("issue_id", "member_id")
1939 "issue_id_p" AS "issue_id",
1940 'periodic' AS "event",
1941 "subquery"."member_id",
1942 "subquery"."interest_exists"
1943 FROM (
1944 SELECT
1945 "member"."id" AS "member_id",
1946 FALSE AS "interest_exists"
1947 FROM "issue"
1948 JOIN "area" ON "issue"."area_id" = "area"."id"
1949 JOIN "membership" ON "area"."id" = "membership"."area_id"
1950 JOIN "member" ON "membership"."member_id" = "member"."id"
1951 WHERE "issue"."id" = "issue_id_p"
1952 AND "member"."active"
1953 UNION
1954 SELECT
1955 "member"."id" AS "member_id",
1956 TRUE AS "interest_exists"
1957 FROM "interest" JOIN "member"
1958 ON "interest"."member_id" = "member"."id"
1959 WHERE "interest"."issue_id" = "issue_id_p"
1960 AND "member"."active"
1961 ) AS "subquery"
1962 ORDER BY
1963 "issue_id_p",
1964 "subquery"."member_id",
1965 "subquery"."interest_exists" DESC;
1966 FOR "member_id_v" IN
1967 SELECT "member_id" FROM "direct_population_snapshot"
1968 WHERE "issue_id" = "issue_id_p"
1969 AND "event" = 'periodic'
1970 LOOP
1971 UPDATE "direct_population_snapshot" SET
1972 "weight" = 1 +
1973 "weight_of_added_delegations_for_population_snapshot"(
1974 "issue_id_p",
1975 "member_id_v",
1976 '{}'
1977 )
1978 WHERE "issue_id" = "issue_id_p"
1979 AND "event" = 'periodic'
1980 AND "member_id" = "member_id_v";
1981 END LOOP;
1982 RETURN;
1983 END;
1984 $$;
1986 COMMENT ON FUNCTION "create_population_snapshot"
1987 ( "issue_id_p" "issue"."id"%TYPE )
1988 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.';
1991 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
1992 ( "issue_id_p" "issue"."id"%TYPE,
1993 "member_id_p" "member"."id"%TYPE,
1994 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
1995 RETURNS "direct_interest_snapshot"."weight"%TYPE
1996 LANGUAGE 'plpgsql' VOLATILE AS $$
1997 DECLARE
1998 "issue_delegation_row" "issue_delegation"%ROWTYPE;
1999 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2000 "weight_v" INT4;
2001 "sub_weight_v" INT4;
2002 BEGIN
2003 "weight_v" := 0;
2004 FOR "issue_delegation_row" IN
2005 SELECT * FROM "issue_delegation"
2006 WHERE "trustee_id" = "member_id_p"
2007 AND "issue_id" = "issue_id_p"
2008 LOOP
2009 IF NOT EXISTS (
2010 SELECT NULL FROM "direct_interest_snapshot"
2011 WHERE "issue_id" = "issue_id_p"
2012 AND "event" = 'periodic'
2013 AND "member_id" = "issue_delegation_row"."truster_id"
2014 ) AND NOT EXISTS (
2015 SELECT NULL FROM "delegating_interest_snapshot"
2016 WHERE "issue_id" = "issue_id_p"
2017 AND "event" = 'periodic'
2018 AND "member_id" = "issue_delegation_row"."truster_id"
2019 ) THEN
2020 "delegate_member_ids_v" :=
2021 "member_id_p" || "delegate_member_ids_p";
2022 INSERT INTO "delegating_interest_snapshot" (
2023 "issue_id",
2024 "event",
2025 "member_id",
2026 "scope",
2027 "delegate_member_ids"
2028 ) VALUES (
2029 "issue_id_p",
2030 'periodic',
2031 "issue_delegation_row"."truster_id",
2032 "issue_delegation_row"."scope",
2033 "delegate_member_ids_v"
2034 );
2035 "sub_weight_v" := 1 +
2036 "weight_of_added_delegations_for_interest_snapshot"(
2037 "issue_id_p",
2038 "issue_delegation_row"."truster_id",
2039 "delegate_member_ids_v"
2040 );
2041 UPDATE "delegating_interest_snapshot"
2042 SET "weight" = "sub_weight_v"
2043 WHERE "issue_id" = "issue_id_p"
2044 AND "event" = 'periodic'
2045 AND "member_id" = "issue_delegation_row"."truster_id";
2046 "weight_v" := "weight_v" + "sub_weight_v";
2047 END IF;
2048 END LOOP;
2049 RETURN "weight_v";
2050 END;
2051 $$;
2053 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2054 ( "issue"."id"%TYPE,
2055 "member"."id"%TYPE,
2056 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2057 IS 'Helper function for "create_interest_snapshot" function';
2060 CREATE FUNCTION "create_interest_snapshot"
2061 ( "issue_id_p" "issue"."id"%TYPE )
2062 RETURNS VOID
2063 LANGUAGE 'plpgsql' VOLATILE AS $$
2064 DECLARE
2065 "member_id_v" "member"."id"%TYPE;
2066 BEGIN
2067 DELETE FROM "direct_interest_snapshot"
2068 WHERE "issue_id" = "issue_id_p"
2069 AND "event" = 'periodic';
2070 DELETE FROM "delegating_interest_snapshot"
2071 WHERE "issue_id" = "issue_id_p"
2072 AND "event" = 'periodic';
2073 DELETE FROM "direct_supporter_snapshot"
2074 WHERE "issue_id" = "issue_id_p"
2075 AND "event" = 'periodic';
2076 INSERT INTO "direct_interest_snapshot"
2077 ("issue_id", "event", "member_id", "voting_requested")
2078 SELECT
2079 "issue_id_p" AS "issue_id",
2080 'periodic' AS "event",
2081 "member"."id" AS "member_id",
2082 "interest"."voting_requested"
2083 FROM "interest" JOIN "member"
2084 ON "interest"."member_id" = "member"."id"
2085 WHERE "interest"."issue_id" = "issue_id_p"
2086 AND "member"."active";
2087 FOR "member_id_v" IN
2088 SELECT "member_id" FROM "direct_interest_snapshot"
2089 WHERE "issue_id" = "issue_id_p"
2090 AND "event" = 'periodic'
2091 LOOP
2092 UPDATE "direct_interest_snapshot" SET
2093 "weight" = 1 +
2094 "weight_of_added_delegations_for_interest_snapshot"(
2095 "issue_id_p",
2096 "member_id_v",
2097 '{}'
2098 )
2099 WHERE "issue_id" = "issue_id_p"
2100 AND "event" = 'periodic'
2101 AND "member_id" = "member_id_v";
2102 END LOOP;
2103 INSERT INTO "direct_supporter_snapshot"
2104 ( "issue_id", "initiative_id", "event", "member_id",
2105 "informed", "satisfied" )
2106 SELECT
2107 "issue_id_p" AS "issue_id",
2108 "initiative"."id" AS "initiative_id",
2109 'periodic' AS "event",
2110 "member"."id" AS "member_id",
2111 "supporter"."draft_id" = "current_draft"."id" AS "informed",
2112 NOT EXISTS (
2113 SELECT NULL FROM "critical_opinion"
2114 WHERE "initiative_id" = "initiative"."id"
2115 AND "member_id" = "member"."id"
2116 ) AS "satisfied"
2117 FROM "supporter"
2118 JOIN "member"
2119 ON "supporter"."member_id" = "member"."id"
2120 JOIN "initiative"
2121 ON "supporter"."initiative_id" = "initiative"."id"
2122 JOIN "current_draft"
2123 ON "initiative"."id" = "current_draft"."initiative_id"
2124 JOIN "direct_interest_snapshot"
2125 ON "member"."id" = "direct_interest_snapshot"."member_id"
2126 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
2127 AND "event" = 'periodic'
2128 WHERE "member"."active"
2129 AND "initiative"."issue_id" = "issue_id_p";
2130 RETURN;
2131 END;
2132 $$;
2134 COMMENT ON FUNCTION "create_interest_snapshot"
2135 ( "issue"."id"%TYPE )
2136 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.';
2139 CREATE FUNCTION "create_snapshot"
2140 ( "issue_id_p" "issue"."id"%TYPE )
2141 RETURNS VOID
2142 LANGUAGE 'plpgsql' VOLATILE AS $$
2143 DECLARE
2144 "initiative_id_v" "initiative"."id"%TYPE;
2145 "suggestion_id_v" "suggestion"."id"%TYPE;
2146 BEGIN
2147 PERFORM "global_lock"();
2148 PERFORM "create_population_snapshot"("issue_id_p");
2149 PERFORM "create_interest_snapshot"("issue_id_p");
2150 UPDATE "issue" SET
2151 "snapshot" = now(),
2152 "latest_snapshot_event" = 'periodic',
2153 "population" = (
2154 SELECT coalesce(sum("weight"), 0)
2155 FROM "direct_population_snapshot"
2156 WHERE "issue_id" = "issue_id_p"
2157 AND "event" = 'periodic'
2158 ),
2159 "vote_now" = (
2160 SELECT coalesce(sum("weight"), 0)
2161 FROM "direct_interest_snapshot"
2162 WHERE "issue_id" = "issue_id_p"
2163 AND "event" = 'periodic'
2164 AND "voting_requested" = TRUE
2165 ),
2166 "vote_later" = (
2167 SELECT coalesce(sum("weight"), 0)
2168 FROM "direct_interest_snapshot"
2169 WHERE "issue_id" = "issue_id_p"
2170 AND "event" = 'periodic'
2171 AND "voting_requested" = FALSE
2172 )
2173 WHERE "id" = "issue_id_p";
2174 FOR "initiative_id_v" IN
2175 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
2176 LOOP
2177 UPDATE "initiative" SET
2178 "supporter_count" = (
2179 SELECT coalesce(sum("di"."weight"), 0)
2180 FROM "direct_interest_snapshot" AS "di"
2181 JOIN "direct_supporter_snapshot" AS "ds"
2182 ON "di"."member_id" = "ds"."member_id"
2183 WHERE "di"."issue_id" = "issue_id_p"
2184 AND "di"."event" = 'periodic'
2185 AND "ds"."initiative_id" = "initiative_id_v"
2186 AND "ds"."event" = 'periodic'
2187 ),
2188 "informed_supporter_count" = (
2189 SELECT coalesce(sum("di"."weight"), 0)
2190 FROM "direct_interest_snapshot" AS "di"
2191 JOIN "direct_supporter_snapshot" AS "ds"
2192 ON "di"."member_id" = "ds"."member_id"
2193 WHERE "di"."issue_id" = "issue_id_p"
2194 AND "di"."event" = 'periodic'
2195 AND "ds"."initiative_id" = "initiative_id_v"
2196 AND "ds"."event" = 'periodic'
2197 AND "ds"."informed"
2198 ),
2199 "satisfied_supporter_count" = (
2200 SELECT coalesce(sum("di"."weight"), 0)
2201 FROM "direct_interest_snapshot" AS "di"
2202 JOIN "direct_supporter_snapshot" AS "ds"
2203 ON "di"."member_id" = "ds"."member_id"
2204 WHERE "di"."issue_id" = "issue_id_p"
2205 AND "di"."event" = 'periodic'
2206 AND "ds"."initiative_id" = "initiative_id_v"
2207 AND "ds"."event" = 'periodic'
2208 AND "ds"."satisfied"
2209 ),
2210 "satisfied_informed_supporter_count" = (
2211 SELECT coalesce(sum("di"."weight"), 0)
2212 FROM "direct_interest_snapshot" AS "di"
2213 JOIN "direct_supporter_snapshot" AS "ds"
2214 ON "di"."member_id" = "ds"."member_id"
2215 WHERE "di"."issue_id" = "issue_id_p"
2216 AND "di"."event" = 'periodic'
2217 AND "ds"."initiative_id" = "initiative_id_v"
2218 AND "ds"."event" = 'periodic'
2219 AND "ds"."informed"
2220 AND "ds"."satisfied"
2221 )
2222 WHERE "id" = "initiative_id_v";
2223 FOR "suggestion_id_v" IN
2224 SELECT "id" FROM "suggestion"
2225 WHERE "initiative_id" = "initiative_id_v"
2226 LOOP
2227 UPDATE "suggestion" SET
2228 "minus2_unfulfilled_count" = (
2229 SELECT coalesce(sum("snapshot"."weight"), 0)
2230 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2231 ON "opinion"."member_id" = "snapshot"."member_id"
2232 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2233 AND "snapshot"."issue_id" = "issue_id_p"
2234 AND "opinion"."degree" = -2
2235 AND "opinion"."fulfilled" = FALSE
2236 ),
2237 "minus2_fulfilled_count" = (
2238 SELECT coalesce(sum("snapshot"."weight"), 0)
2239 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2240 ON "opinion"."member_id" = "snapshot"."member_id"
2241 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2242 AND "snapshot"."issue_id" = "issue_id_p"
2243 AND "opinion"."degree" = -2
2244 AND "opinion"."fulfilled" = TRUE
2245 ),
2246 "minus1_unfulfilled_count" = (
2247 SELECT coalesce(sum("snapshot"."weight"), 0)
2248 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2249 ON "opinion"."member_id" = "snapshot"."member_id"
2250 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2251 AND "snapshot"."issue_id" = "issue_id_p"
2252 AND "opinion"."degree" = -1
2253 AND "opinion"."fulfilled" = FALSE
2254 ),
2255 "minus1_fulfilled_count" = (
2256 SELECT coalesce(sum("snapshot"."weight"), 0)
2257 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2258 ON "opinion"."member_id" = "snapshot"."member_id"
2259 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2260 AND "snapshot"."issue_id" = "issue_id_p"
2261 AND "opinion"."degree" = -1
2262 AND "opinion"."fulfilled" = TRUE
2263 ),
2264 "plus1_unfulfilled_count" = (
2265 SELECT coalesce(sum("snapshot"."weight"), 0)
2266 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2267 ON "opinion"."member_id" = "snapshot"."member_id"
2268 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2269 AND "snapshot"."issue_id" = "issue_id_p"
2270 AND "opinion"."degree" = 1
2271 AND "opinion"."fulfilled" = FALSE
2272 ),
2273 "plus1_fulfilled_count" = (
2274 SELECT coalesce(sum("snapshot"."weight"), 0)
2275 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2276 ON "opinion"."member_id" = "snapshot"."member_id"
2277 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2278 AND "snapshot"."issue_id" = "issue_id_p"
2279 AND "opinion"."degree" = 1
2280 AND "opinion"."fulfilled" = TRUE
2281 ),
2282 "plus2_unfulfilled_count" = (
2283 SELECT coalesce(sum("snapshot"."weight"), 0)
2284 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2285 ON "opinion"."member_id" = "snapshot"."member_id"
2286 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2287 AND "snapshot"."issue_id" = "issue_id_p"
2288 AND "opinion"."degree" = 2
2289 AND "opinion"."fulfilled" = FALSE
2290 ),
2291 "plus2_fulfilled_count" = (
2292 SELECT coalesce(sum("snapshot"."weight"), 0)
2293 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2294 ON "opinion"."member_id" = "snapshot"."member_id"
2295 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2296 AND "snapshot"."issue_id" = "issue_id_p"
2297 AND "opinion"."degree" = 2
2298 AND "opinion"."fulfilled" = TRUE
2299 )
2300 WHERE "suggestion"."id" = "suggestion_id_v";
2301 END LOOP;
2302 END LOOP;
2303 RETURN;
2304 END;
2305 $$;
2307 COMMENT ON FUNCTION "create_snapshot"
2308 ( "issue"."id"%TYPE )
2309 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.';
2312 CREATE FUNCTION "set_snapshot_event"
2313 ( "issue_id_p" "issue"."id"%TYPE,
2314 "event_p" "snapshot_event" )
2315 RETURNS VOID
2316 LANGUAGE 'plpgsql' VOLATILE AS $$
2317 BEGIN
2318 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
2319 WHERE "id" = "issue_id_p";
2320 UPDATE "direct_population_snapshot" SET "event" = "event_p"
2321 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
2322 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
2323 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
2324 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
2325 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
2326 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
2327 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
2328 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
2329 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
2330 RETURN;
2331 END;
2332 $$;
2334 COMMENT ON FUNCTION "set_snapshot_event"
2335 ( "issue"."id"%TYPE,
2336 "snapshot_event" )
2337 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
2341 ---------------------
2342 -- Freezing issues --
2343 ---------------------
2345 CREATE FUNCTION "freeze_after_snapshot"
2346 ( "issue_id_p" "issue"."id"%TYPE )
2347 RETURNS VOID
2348 LANGUAGE 'plpgsql' VOLATILE AS $$
2349 DECLARE
2350 "issue_row" "issue"%ROWTYPE;
2351 "policy_row" "policy"%ROWTYPE;
2352 "initiative_row" "initiative"%ROWTYPE;
2353 BEGIN
2354 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2355 SELECT * INTO "policy_row"
2356 FROM "policy" WHERE "id" = "issue_row"."policy_id";
2357 PERFORM "set_snapshot_event"("issue_id_p", 'start_of_voting');
2358 UPDATE "issue" SET
2359 "accepted" = coalesce("accepted", now()),
2360 "half_frozen" = coalesce("half_frozen", now()),
2361 "fully_frozen" = now()
2362 WHERE "id" = "issue_id_p";
2363 FOR "initiative_row" IN
2364 SELECT * FROM "initiative"
2365 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
2366 LOOP
2367 IF
2368 "initiative_row"."satisfied_supporter_count" > 0 AND
2369 "initiative_row"."satisfied_supporter_count" *
2370 "policy_row"."initiative_quorum_den" >=
2371 "issue_row"."population" * "policy_row"."initiative_quorum_num"
2372 THEN
2373 UPDATE "initiative" SET "admitted" = TRUE
2374 WHERE "id" = "initiative_row"."id";
2375 ELSE
2376 UPDATE "initiative" SET "admitted" = FALSE
2377 WHERE "id" = "initiative_row"."id";
2378 END IF;
2379 END LOOP;
2380 IF NOT EXISTS (
2381 SELECT NULL FROM "initiative"
2382 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
2383 ) THEN
2384 PERFORM "close_voting"("issue_id_p");
2385 END IF;
2386 RETURN;
2387 END;
2388 $$;
2390 COMMENT ON FUNCTION "freeze_after_snapshot"
2391 ( "issue"."id"%TYPE )
2392 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
2395 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
2396 RETURNS VOID
2397 LANGUAGE 'plpgsql' VOLATILE AS $$
2398 DECLARE
2399 "issue_row" "issue"%ROWTYPE;
2400 BEGIN
2401 PERFORM "create_snapshot"("issue_id_p");
2402 PERFORM "freeze_after_snapshot"("issue_id_p");
2403 RETURN;
2404 END;
2405 $$;
2407 COMMENT ON FUNCTION "freeze_after_snapshot"
2408 ( "issue"."id"%TYPE )
2409 IS 'Freeze an issue manually (fully) and start voting';
2413 -----------------------
2414 -- Counting of votes --
2415 -----------------------
2418 CREATE FUNCTION "weight_of_added_vote_delegations"
2419 ( "issue_id_p" "issue"."id"%TYPE,
2420 "member_id_p" "member"."id"%TYPE,
2421 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
2422 RETURNS "direct_voter"."weight"%TYPE
2423 LANGUAGE 'plpgsql' VOLATILE AS $$
2424 DECLARE
2425 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2426 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
2427 "weight_v" INT4;
2428 "sub_weight_v" INT4;
2429 BEGIN
2430 "weight_v" := 0;
2431 FOR "issue_delegation_row" IN
2432 SELECT * FROM "issue_delegation"
2433 WHERE "trustee_id" = "member_id_p"
2434 AND "issue_id" = "issue_id_p"
2435 LOOP
2436 IF NOT EXISTS (
2437 SELECT NULL FROM "direct_voter"
2438 WHERE "member_id" = "issue_delegation_row"."truster_id"
2439 AND "issue_id" = "issue_id_p"
2440 ) AND NOT EXISTS (
2441 SELECT NULL FROM "delegating_voter"
2442 WHERE "member_id" = "issue_delegation_row"."truster_id"
2443 AND "issue_id" = "issue_id_p"
2444 ) THEN
2445 "delegate_member_ids_v" :=
2446 "member_id_p" || "delegate_member_ids_p";
2447 INSERT INTO "delegating_voter" (
2448 "issue_id",
2449 "member_id",
2450 "scope",
2451 "delegate_member_ids"
2452 ) VALUES (
2453 "issue_id_p",
2454 "issue_delegation_row"."truster_id",
2455 "issue_delegation_row"."scope",
2456 "delegate_member_ids_v"
2457 );
2458 "sub_weight_v" := 1 +
2459 "weight_of_added_vote_delegations"(
2460 "issue_id_p",
2461 "issue_delegation_row"."truster_id",
2462 "delegate_member_ids_v"
2463 );
2464 UPDATE "delegating_voter"
2465 SET "weight" = "sub_weight_v"
2466 WHERE "issue_id" = "issue_id_p"
2467 AND "member_id" = "issue_delegation_row"."truster_id";
2468 "weight_v" := "weight_v" + "sub_weight_v";
2469 END IF;
2470 END LOOP;
2471 RETURN "weight_v";
2472 END;
2473 $$;
2475 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
2476 ( "issue"."id"%TYPE,
2477 "member"."id"%TYPE,
2478 "delegating_voter"."delegate_member_ids"%TYPE )
2479 IS 'Helper function for "add_vote_delegations" function';
2482 CREATE FUNCTION "add_vote_delegations"
2483 ( "issue_id_p" "issue"."id"%TYPE )
2484 RETURNS VOID
2485 LANGUAGE 'plpgsql' VOLATILE AS $$
2486 DECLARE
2487 "member_id_v" "member"."id"%TYPE;
2488 BEGIN
2489 FOR "member_id_v" IN
2490 SELECT "member_id" FROM "direct_voter"
2491 WHERE "issue_id" = "issue_id_p"
2492 LOOP
2493 UPDATE "direct_voter" SET
2494 "weight" = "weight" + "weight_of_added_vote_delegations"(
2495 "issue_id_p",
2496 "member_id_v",
2497 '{}'
2498 )
2499 WHERE "member_id" = "member_id_v"
2500 AND "issue_id" = "issue_id_p";
2501 END LOOP;
2502 RETURN;
2503 END;
2504 $$;
2506 COMMENT ON FUNCTION "add_vote_delegations"
2507 ( "issue_id_p" "issue"."id"%TYPE )
2508 IS 'Helper function for "close_voting" function';
2511 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
2512 RETURNS VOID
2513 LANGUAGE 'plpgsql' VOLATILE AS $$
2514 DECLARE
2515 "issue_row" "issue"%ROWTYPE;
2516 "member_id_v" "member"."id"%TYPE;
2517 BEGIN
2518 PERFORM "global_lock"();
2519 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2520 DELETE FROM "delegating_voter"
2521 WHERE "issue_id" = "issue_id_p";
2522 DELETE FROM "direct_voter"
2523 WHERE "issue_id" = "issue_id_p"
2524 AND "autoreject" = TRUE;
2525 DELETE FROM "direct_voter" USING "member"
2526 WHERE "direct_voter"."member_id" = "member"."id"
2527 AND "direct_voter"."issue_id" = "issue_id_p"
2528 AND "member"."active" = FALSE;
2529 UPDATE "direct_voter" SET "weight" = 1
2530 WHERE "issue_id" = "issue_id_p";
2531 PERFORM "add_vote_delegations"("issue_id_p");
2532 FOR "member_id_v" IN
2533 SELECT "interest"."member_id"
2534 FROM "interest"
2535 LEFT JOIN "direct_voter"
2536 ON "interest"."member_id" = "direct_voter"."member_id"
2537 AND "interest"."issue_id" = "direct_voter"."issue_id"
2538 LEFT JOIN "delegating_voter"
2539 ON "interest"."member_id" = "delegating_voter"."member_id"
2540 AND "interest"."issue_id" = "delegating_voter"."issue_id"
2541 WHERE "interest"."issue_id" = "issue_id_p"
2542 AND "interest"."autoreject" = TRUE
2543 AND "direct_voter"."member_id" ISNULL
2544 AND "delegating_voter"."member_id" ISNULL
2545 UNION SELECT "membership"."member_id"
2546 FROM "membership"
2547 LEFT JOIN "interest"
2548 ON "membership"."member_id" = "interest"."member_id"
2549 AND "interest"."issue_id" = "issue_id_p"
2550 LEFT JOIN "direct_voter"
2551 ON "membership"."member_id" = "direct_voter"."member_id"
2552 AND "direct_voter"."issue_id" = "issue_id_p"
2553 LEFT JOIN "delegating_voter"
2554 ON "membership"."member_id" = "delegating_voter"."member_id"
2555 AND "delegating_voter"."issue_id" = "issue_id_p"
2556 WHERE "membership"."area_id" = "issue_row"."area_id"
2557 AND "membership"."autoreject" = TRUE
2558 AND "interest"."autoreject" ISNULL
2559 AND "direct_voter"."member_id" ISNULL
2560 AND "delegating_voter"."member_id" ISNULL
2561 LOOP
2562 INSERT INTO "direct_voter"
2563 ("member_id", "issue_id", "weight", "autoreject") VALUES
2564 ("member_id_v", "issue_id_p", 1, TRUE);
2565 INSERT INTO "vote" (
2566 "member_id",
2567 "issue_id",
2568 "initiative_id",
2569 "grade"
2570 ) SELECT
2571 "member_id_v" AS "member_id",
2572 "issue_id_p" AS "issue_id",
2573 "id" AS "initiative_id",
2574 -1 AS "grade"
2575 FROM "initiative" WHERE "issue_id" = "issue_id_p";
2576 END LOOP;
2577 PERFORM "add_vote_delegations"("issue_id_p");
2578 UPDATE "issue" SET
2579 "voter_count" = (
2580 SELECT coalesce(sum("weight"), 0)
2581 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
2582 )
2583 WHERE "id" = "issue_id_p";
2584 UPDATE "initiative" SET
2585 "positive_votes" = "vote_counts"."positive_votes",
2586 "negative_votes" = "vote_counts"."negative_votes",
2587 "agreed" = CASE WHEN "majority_strict" THEN
2588 "vote_counts"."positive_votes" * "majority_den" >
2589 "majority_num" *
2590 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
2591 ELSE
2592 "vote_counts"."positive_votes" * "majority_den" >=
2593 "majority_num" *
2594 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
2595 END
2596 FROM
2597 ( SELECT
2598 "initiative"."id" AS "initiative_id",
2599 coalesce(
2600 sum(
2601 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
2602 ),
2603 0
2604 ) AS "positive_votes",
2605 coalesce(
2606 sum(
2607 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
2608 ),
2609 0
2610 ) AS "negative_votes"
2611 FROM "initiative"
2612 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
2613 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
2614 LEFT JOIN "direct_voter"
2615 ON "direct_voter"."issue_id" = "initiative"."issue_id"
2616 LEFT JOIN "vote"
2617 ON "vote"."initiative_id" = "initiative"."id"
2618 AND "vote"."member_id" = "direct_voter"."member_id"
2619 WHERE "initiative"."issue_id" = "issue_id_p"
2620 AND "initiative"."admitted" -- NOTE: NULL case is handled too
2621 GROUP BY "initiative"."id"
2622 ) AS "vote_counts",
2623 "issue",
2624 "policy"
2625 WHERE "vote_counts"."initiative_id" = "initiative"."id"
2626 AND "issue"."id" = "initiative"."issue_id"
2627 AND "policy"."id" = "issue"."policy_id";
2628 UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p";
2629 END;
2630 $$;
2632 COMMENT ON FUNCTION "close_voting"
2633 ( "issue"."id"%TYPE )
2634 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.';
2637 CREATE FUNCTION "init_array"("dim_p" INTEGER)
2638 RETURNS INT4[]
2639 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2640 DECLARE
2641 "i" INTEGER;
2642 "ary_text_v" TEXT;
2643 BEGIN
2644 IF "dim_p" >= 1 THEN
2645 "ary_text_v" := '{NULL';
2646 "i" := "dim_p";
2647 LOOP
2648 "i" := "i" - 1;
2649 EXIT WHEN "i" = 0;
2650 "ary_text_v" := "ary_text_v" || ',NULL';
2651 END LOOP;
2652 "ary_text_v" := "ary_text_v" || '}';
2653 RETURN "ary_text_v"::INT4[][];
2654 ELSE
2655 RAISE EXCEPTION 'Dimension needs to be at least 1.';
2656 END IF;
2657 END;
2658 $$;
2660 COMMENT ON FUNCTION "init_array"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
2663 CREATE FUNCTION "init_square_matrix"("dim_p" INTEGER)
2664 RETURNS INT4[][]
2665 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2666 DECLARE
2667 "i" INTEGER;
2668 "row_text_v" TEXT;
2669 "ary_text_v" TEXT;
2670 BEGIN
2671 IF "dim_p" >= 1 THEN
2672 "row_text_v" := '{NULL';
2673 "i" := "dim_p";
2674 LOOP
2675 "i" := "i" - 1;
2676 EXIT WHEN "i" = 0;
2677 "row_text_v" := "row_text_v" || ',NULL';
2678 END LOOP;
2679 "row_text_v" := "row_text_v" || '}';
2680 "ary_text_v" := '{' || "row_text_v";
2681 "i" := "dim_p";
2682 LOOP
2683 "i" := "i" - 1;
2684 EXIT WHEN "i" = 0;
2685 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
2686 END LOOP;
2687 "ary_text_v" := "ary_text_v" || '}';
2688 RETURN "ary_text_v"::INT4[][];
2689 ELSE
2690 RAISE EXCEPTION 'Dimension needs to be at least 1.';
2691 END IF;
2692 END;
2693 $$;
2695 COMMENT ON FUNCTION "init_square_matrix"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
2698 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
2699 RETURNS VOID
2700 LANGUAGE 'plpgsql' VOLATILE AS $$
2701 DECLARE
2702 "dimension_v" INTEGER;
2703 "matrix" INT4[][];
2704 "i" INTEGER;
2705 "j" INTEGER;
2706 "k" INTEGER;
2707 "battle_row" "battle"%ROWTYPE;
2708 "rank_ary" INT4[];
2709 "rank_v" INT4;
2710 "done_v" INTEGER;
2711 "winners_ary" INTEGER[];
2712 "initiative_id_v" "initiative"."id"%TYPE;
2713 BEGIN
2714 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2715 -- Prepare matrix for Schulze-Method:
2716 SELECT count(1) INTO "dimension_v" FROM "initiative"
2717 WHERE "issue_id" = "issue_id_p" AND "agreed";
2718 IF "dimension_v" = 1 THEN
2719 UPDATE "initiative" SET "rank" = 1
2720 WHERE "issue_id" = "issue_id_p" AND "agreed";
2721 ELSIF "dimension_v" > 1 THEN
2722 "matrix" := "init_square_matrix"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2723 "i" := 1;
2724 "j" := 2;
2725 -- Fill matrix with data from "battle" view
2726 FOR "battle_row" IN
2727 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
2728 ORDER BY "winning_initiative_id", "losing_initiative_id"
2729 LOOP
2730 "matrix"["i"]["j"] := "battle_row"."count";
2731 IF "j" = "dimension_v" THEN
2732 "i" := "i" + 1;
2733 "j" := 1;
2734 ELSE
2735 "j" := "j" + 1;
2736 IF "j" = "i" THEN
2737 "j" := "j" + 1;
2738 END IF;
2739 END IF;
2740 END LOOP;
2741 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
2742 RAISE EXCEPTION 'Wrong battle count (should not happen)';
2743 END IF;
2744 -- Delete losers from matrix:
2745 "i" := 1;
2746 LOOP
2747 "j" := "i" + 1;
2748 LOOP
2749 IF "i" != "j" THEN
2750 IF "matrix"["i"]["j"] < "matrix"["j"]["i"] THEN
2751 "matrix"["i"]["j"] := 0;
2752 ELSIF matrix[j][i] < matrix[i][j] THEN
2753 "matrix"["j"]["i"] := 0;
2754 ELSE
2755 "matrix"["i"]["j"] := 0;
2756 "matrix"["j"]["i"] := 0;
2757 END IF;
2758 END IF;
2759 EXIT WHEN "j" = "dimension_v";
2760 "j" := "j" + 1;
2761 END LOOP;
2762 EXIT WHEN "i" = "dimension_v" - 1;
2763 "i" := "i" + 1;
2764 END LOOP;
2765 -- Find best paths:
2766 "i" := 1;
2767 LOOP
2768 "j" := 1;
2769 LOOP
2770 IF "i" != "j" THEN
2771 "k" := 1;
2772 LOOP
2773 IF "i" != "k" AND "j" != "k" THEN
2774 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
2775 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
2776 "matrix"["j"]["k"] := "matrix"["j"]["i"];
2777 END IF;
2778 ELSE
2779 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
2780 "matrix"["j"]["k"] := "matrix"["i"]["k"];
2781 END IF;
2782 END IF;
2783 END IF;
2784 EXIT WHEN "k" = "dimension_v";
2785 "k" := "k" + 1;
2786 END LOOP;
2787 END IF;
2788 EXIT WHEN "j" = "dimension_v";
2789 "j" := "j" + 1;
2790 END LOOP;
2791 EXIT WHEN "i" = "dimension_v";
2792 "i" := "i" + 1;
2793 END LOOP;
2794 -- Determine order of winners:
2795 "rank_ary" := "init_array"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2796 "rank_v" := 1;
2797 "done_v" := 0;
2798 LOOP
2799 "winners_ary" := '{}';
2800 "i" := 1;
2801 LOOP
2802 IF "rank_ary"["i"] ISNULL THEN
2803 "j" := 1;
2804 LOOP
2805 IF
2806 "i" != "j" AND
2807 "rank_ary"["j"] ISNULL AND
2808 "matrix"["j"]["i"] > "matrix"["i"]["j"]
2809 THEN
2810 -- someone else is better
2811 EXIT;
2812 END IF;
2813 IF "j" = "dimension_v" THEN
2814 -- noone is better
2815 "winners_ary" := "winners_ary" || "i";
2816 EXIT;
2817 END IF;
2818 "j" := "j" + 1;
2819 END LOOP;
2820 END IF;
2821 EXIT WHEN "i" = "dimension_v";
2822 "i" := "i" + 1;
2823 END LOOP;
2824 "i" := 1;
2825 LOOP
2826 "rank_ary"["winners_ary"["i"]] := "rank_v";
2827 "done_v" := "done_v" + 1;
2828 EXIT WHEN "i" = array_upper("winners_ary", 1);
2829 "i" := "i" + 1;
2830 END LOOP;
2831 EXIT WHEN "done_v" = "dimension_v";
2832 "rank_v" := "rank_v" + 1;
2833 END LOOP;
2834 -- write preliminary ranks:
2835 "i" := 1;
2836 FOR "initiative_id_v" IN
2837 SELECT "id" FROM "initiative"
2838 WHERE "issue_id" = "issue_id_p" AND "agreed"
2839 ORDER BY "id"
2840 LOOP
2841 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
2842 WHERE "id" = "initiative_id_v";
2843 "i" := "i" + 1;
2844 END LOOP;
2845 IF "i" != "dimension_v" + 1 THEN
2846 RAISE EXCEPTION 'Wrong winner count (should not happen)';
2847 END IF;
2848 -- straighten ranks (start counting with 1, no equal ranks):
2849 "rank_v" := 1;
2850 FOR "initiative_id_v" IN
2851 SELECT "id" FROM "initiative"
2852 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
2853 ORDER BY
2854 "rank",
2855 "vote_ratio"("positive_votes", "negative_votes") DESC,
2856 "id"
2857 LOOP
2858 UPDATE "initiative" SET "rank" = "rank_v"
2859 WHERE "id" = "initiative_id_v";
2860 "rank_v" := "rank_v" + 1;
2861 END LOOP;
2862 END IF;
2863 -- mark issue as finished
2864 UPDATE "issue" SET "ranks_available" = TRUE
2865 WHERE "id" = "issue_id_p";
2866 RETURN;
2867 END;
2868 $$;
2870 COMMENT ON FUNCTION "calculate_ranks"
2871 ( "issue"."id"%TYPE )
2872 IS 'Determine ranking (Votes have to be counted first)';
2876 -----------------------------
2877 -- Automatic state changes --
2878 -----------------------------
2881 CREATE FUNCTION "check_issue"
2882 ( "issue_id_p" "issue"."id"%TYPE )
2883 RETURNS VOID
2884 LANGUAGE 'plpgsql' VOLATILE AS $$
2885 DECLARE
2886 "issue_row" "issue"%ROWTYPE;
2887 "policy_row" "policy"%ROWTYPE;
2888 "voting_requested_v" BOOLEAN;
2889 BEGIN
2890 PERFORM "global_lock"();
2891 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2892 IF "issue_row"."closed" ISNULL THEN
2893 SELECT * INTO "policy_row" FROM "policy"
2894 WHERE "id" = "issue_row"."policy_id";
2895 IF "issue_row"."fully_frozen" ISNULL THEN
2896 PERFORM "create_snapshot"("issue_id_p");
2897 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2898 END IF;
2899 IF "issue_row"."accepted" ISNULL THEN
2900 IF EXISTS (
2901 SELECT NULL FROM "initiative"
2902 WHERE "issue_id" = "issue_id_p"
2903 AND "supporter_count" > 0
2904 AND "supporter_count" * "policy_row"."issue_quorum_den"
2905 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
2906 ) THEN
2907 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
2908 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later
2909 UPDATE "issue" SET "accepted" = "issue_row"."accepted"
2910 WHERE "id" = "issue_row"."id";
2911 ELSIF
2912 now() >= "issue_row"."created" + "policy_row"."admission_time"
2913 THEN
2914 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
2915 UPDATE "issue" SET "closed" = now()
2916 WHERE "id" = "issue_row"."id";
2917 END IF;
2918 END IF;
2919 IF
2920 "issue_row"."accepted" NOTNULL AND
2921 "issue_row"."half_frozen" ISNULL
2922 THEN
2923 SELECT
2924 CASE
2925 WHEN "vote_now" * 2 > "issue_row"."population" THEN
2926 TRUE
2927 WHEN "vote_later" * 2 > "issue_row"."population" THEN
2928 FALSE
2929 ELSE NULL
2930 END
2931 INTO "voting_requested_v"
2932 FROM "issue" WHERE "id" = "issue_id_p";
2933 IF
2934 "voting_requested_v" OR (
2935 "voting_requested_v" ISNULL AND
2936 now() >= "issue_row"."accepted" + "policy_row"."discussion_time"
2937 )
2938 THEN
2939 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later
2940 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
2941 WHERE "id" = "issue_row"."id";
2942 END IF;
2943 END IF;
2944 IF
2945 "issue_row"."half_frozen" NOTNULL AND
2946 "issue_row"."fully_frozen" ISNULL AND
2947 now() >= "issue_row"."half_frozen" + "policy_row"."verification_time"
2948 THEN
2949 PERFORM "freeze_after_snapshot"("issue_id_p");
2950 -- "issue" might change, thus "issue_row" has to be updated below
2951 END IF;
2952 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2953 IF
2954 "issue_row"."closed" ISNULL AND
2955 "issue_row"."fully_frozen" NOTNULL AND
2956 now() >= "issue_row"."fully_frozen" + "policy_row"."voting_time"
2957 THEN
2958 PERFORM "close_voting"("issue_id_p");
2959 END IF;
2960 END IF;
2961 RETURN;
2962 END;
2963 $$;
2965 COMMENT ON FUNCTION "check_issue"
2966 ( "issue"."id"%TYPE )
2967 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.';
2970 CREATE FUNCTION "check_everything"()
2971 RETURNS VOID
2972 LANGUAGE 'plpgsql' VOLATILE AS $$
2973 DECLARE
2974 "issue_id_v" "issue"."id"%TYPE;
2975 BEGIN
2976 DELETE FROM "expired_session";
2977 PERFORM "calculate_member_counts"();
2978 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
2979 PERFORM "check_issue"("issue_id_v");
2980 END LOOP;
2981 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
2982 PERFORM "calculate_ranks"("issue_id_v");
2983 END LOOP;
2984 RETURN;
2985 END;
2986 $$;
2988 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.';
2992 ------------------------------
2993 -- Deletion of private data --
2994 ------------------------------
2997 CREATE FUNCTION "delete_private_data"()
2998 RETURNS VOID
2999 LANGUAGE 'plpgsql' VOLATILE AS $$
3000 DECLARE
3001 "issue_id_v" "issue"."id"%TYPE;
3002 BEGIN
3003 UPDATE "member" SET
3004 "login" = 'login' || "id"::text,
3005 "password" = NULL,
3006 "notify_email" = NULL,
3007 "notify_email_unconfirmed" = NULL,
3008 "notify_email_secret" = NULL,
3009 "notify_email_secret_expiry" = NULL,
3010 "password_reset_secret" = NULL,
3011 "password_reset_secret_expiry" = NULL,
3012 "organizational_unit" = NULL,
3013 "internal_posts" = NULL,
3014 "realname" = NULL,
3015 "birthday" = NULL,
3016 "address" = NULL,
3017 "email" = NULL,
3018 "xmpp_address" = NULL,
3019 "website" = NULL,
3020 "phone" = NULL,
3021 "mobile_phone" = NULL,
3022 "profession" = NULL,
3023 "external_memberships" = NULL,
3024 "external_posts" = NULL,
3025 "statement" = NULL;
3026 -- "text_search_data" is updated by triggers
3027 DELETE FROM "session";
3028 DELETE FROM "invite_code";
3029 DELETE FROM "contact";
3030 DELETE FROM "setting";
3031 DELETE FROM "member_image";
3032 DELETE FROM "direct_voter" USING "issue"
3033 WHERE "direct_voter"."issue_id" = "issue"."id"
3034 AND "issue"."closed" ISNULL;
3035 RETURN;
3036 END;
3037 $$;
3039 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.';
3043 COMMIT;