liquid_feedback_core
view core.sql @ 98:741b7a5a2783
Added comment in line where ISNULL is applied to a row record
| author | jbe |
|---|---|
| date | Fri Dec 24 02:57:15 2010 +0100 (2010-12-24) |
| parents | 58451b5565ae |
| children | 994dd8ff5ad1 |
line source
2 -- Execute the following command manually for PostgreSQL prior version 9.0:
3 -- CREATE LANGUAGE plpgsql;
5 -- NOTE: In PostgreSQL every UNIQUE constraint implies creation of an index
7 BEGIN;
9 CREATE VIEW "liquid_feedback_version" AS
10 SELECT * FROM (VALUES ('1.4.0', 1, 4, 0))
11 AS "subquery"("string", "major", "minor", "revision");
15 ----------------------
16 -- Full text search --
17 ----------------------
20 CREATE FUNCTION "text_search_query"("query_text_p" TEXT)
21 RETURNS TSQUERY
22 LANGUAGE 'plpgsql' IMMUTABLE AS $$
23 BEGIN
24 RETURN plainto_tsquery('pg_catalog.simple', "query_text_p");
25 END;
26 $$;
28 COMMENT ON FUNCTION "text_search_query"(TEXT) IS 'Usage: WHERE "text_search_data" @@ "text_search_query"(''<user query>'')';
31 CREATE FUNCTION "highlight"
32 ( "body_p" TEXT,
33 "query_text_p" TEXT )
34 RETURNS TEXT
35 LANGUAGE 'plpgsql' IMMUTABLE AS $$
36 BEGIN
37 RETURN ts_headline(
38 'pg_catalog.simple',
39 replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'),
40 "text_search_query"("query_text_p"),
41 'StartSel=* StopSel=* HighlightAll=TRUE' );
42 END;
43 $$;
45 COMMENT ON FUNCTION "highlight"
46 ( "body_p" TEXT,
47 "query_text_p" TEXT )
48 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.';
52 -------------------------
53 -- Tables and indicies --
54 -------------------------
57 CREATE TABLE "member" (
58 "id" SERIAL4 PRIMARY KEY,
59 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
60 "last_login" TIMESTAMPTZ,
61 "login" TEXT UNIQUE,
62 "password" TEXT,
63 "active" BOOLEAN NOT NULL DEFAULT TRUE,
64 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
65 "notify_email" TEXT,
66 "notify_email_unconfirmed" TEXT,
67 "notify_email_secret" TEXT UNIQUE,
68 "notify_email_secret_expiry" TIMESTAMPTZ,
69 "notify_email_lock_expiry" TIMESTAMPTZ,
70 "password_reset_secret" TEXT UNIQUE,
71 "password_reset_secret_expiry" TIMESTAMPTZ,
72 "name" TEXT NOT NULL UNIQUE,
73 "identification" TEXT UNIQUE,
74 "organizational_unit" TEXT,
75 "internal_posts" TEXT,
76 "realname" TEXT,
77 "birthday" DATE,
78 "address" TEXT,
79 "email" TEXT,
80 "xmpp_address" TEXT,
81 "website" TEXT,
82 "phone" TEXT,
83 "mobile_phone" TEXT,
84 "profession" TEXT,
85 "external_memberships" TEXT,
86 "external_posts" TEXT,
87 "statement" TEXT,
88 "text_search_data" TSVECTOR );
89 CREATE INDEX "member_active_idx" ON "member" ("active");
90 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
91 CREATE TRIGGER "update_text_search_data"
92 BEFORE INSERT OR UPDATE ON "member"
93 FOR EACH ROW EXECUTE PROCEDURE
94 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
95 "name", "identification", "organizational_unit", "internal_posts",
96 "realname", "external_memberships", "external_posts", "statement" );
98 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
100 COMMENT ON COLUMN "member"."login" IS 'Login name';
101 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
102 COMMENT ON COLUMN "member"."active" IS 'Inactive members can not login and their supports/votes are not counted by the system.';
103 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
104 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
105 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
106 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
107 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
108 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
109 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member';
110 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
111 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
112 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
113 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
114 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
115 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
116 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
117 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his homepage within the system';
120 CREATE TABLE "member_history" (
121 "id" SERIAL8 PRIMARY KEY,
122 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
123 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
124 "active" BOOLEAN NOT NULL,
125 "name" TEXT NOT NULL );
126 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
128 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
130 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
131 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
134 CREATE TABLE "invite_code" (
135 "id" SERIAL8 PRIMARY KEY,
136 "code" TEXT NOT NULL UNIQUE,
137 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
138 "used" TIMESTAMPTZ,
139 "member_id" INT4 UNIQUE REFERENCES "member" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
140 "comment" TEXT,
141 CONSTRAINT "only_used_codes_may_refer_to_member" CHECK ("used" NOTNULL OR "member_id" ISNULL) );
143 COMMENT ON TABLE "invite_code" IS 'Invite codes can be used once to create a new member account.';
145 COMMENT ON COLUMN "invite_code"."code" IS 'Secret code';
146 COMMENT ON COLUMN "invite_code"."created" IS 'Time of creation of the secret code';
147 COMMENT ON COLUMN "invite_code"."used" IS 'NULL, if not used yet, otherwise tells when this code was used to create a member account';
148 COMMENT ON COLUMN "invite_code"."member_id" IS 'References the member whose account was created with this code';
149 COMMENT ON COLUMN "invite_code"."comment" IS 'Comment on the code, which is to be used for administrative reasons only';
152 CREATE TABLE "setting" (
153 PRIMARY KEY ("member_id", "key"),
154 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
155 "key" TEXT NOT NULL,
156 "value" TEXT NOT NULL );
157 CREATE INDEX "setting_key_idx" ON "setting" ("key");
159 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
161 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
164 CREATE TABLE "setting_map" (
165 PRIMARY KEY ("member_id", "key", "subkey"),
166 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
167 "key" TEXT NOT NULL,
168 "subkey" TEXT NOT NULL,
169 "value" TEXT NOT NULL );
170 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
172 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
174 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
175 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
176 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
179 CREATE TABLE "member_relation_setting" (
180 PRIMARY KEY ("member_id", "key", "other_member_id"),
181 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
182 "key" TEXT NOT NULL,
183 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
184 "value" TEXT NOT NULL );
186 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
189 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
191 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
194 CREATE TABLE "member_image" (
195 PRIMARY KEY ("member_id", "image_type", "scaled"),
196 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
197 "image_type" "member_image_type",
198 "scaled" BOOLEAN,
199 "content_type" TEXT,
200 "data" BYTEA NOT NULL );
202 COMMENT ON TABLE "member_image" IS 'Images of members';
204 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
207 CREATE TABLE "member_count" (
208 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
209 "total_count" INT4 NOT NULL );
211 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';
213 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
214 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
217 CREATE TABLE "contact" (
218 PRIMARY KEY ("member_id", "other_member_id"),
219 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
220 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
221 "public" BOOLEAN NOT NULL DEFAULT FALSE,
222 CONSTRAINT "cant_save_yourself_as_contact"
223 CHECK ("member_id" != "other_member_id") );
225 COMMENT ON TABLE "contact" IS 'Contact lists';
227 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
228 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
229 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
232 CREATE TABLE "session" (
233 "ident" TEXT PRIMARY KEY,
234 "additional_secret" TEXT,
235 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
236 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
237 "lang" TEXT );
238 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
240 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';
242 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
243 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
244 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
245 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
248 CREATE TABLE "policy" (
249 "id" SERIAL4 PRIMARY KEY,
250 "index" INT4 NOT NULL,
251 "active" BOOLEAN NOT NULL DEFAULT TRUE,
252 "name" TEXT NOT NULL UNIQUE,
253 "description" TEXT NOT NULL DEFAULT '',
254 "admission_time" INTERVAL NOT NULL,
255 "discussion_time" INTERVAL NOT NULL,
256 "verification_time" INTERVAL NOT NULL,
257 "voting_time" INTERVAL NOT NULL,
258 "issue_quorum_num" INT4 NOT NULL,
259 "issue_quorum_den" INT4 NOT NULL,
260 "initiative_quorum_num" INT4 NOT NULL,
261 "initiative_quorum_den" INT4 NOT NULL,
262 "majority_num" INT4 NOT NULL DEFAULT 1,
263 "majority_den" INT4 NOT NULL DEFAULT 2,
264 "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE );
265 CREATE INDEX "policy_active_idx" ON "policy" ("active");
267 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
269 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
270 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
271 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"';
272 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"';
273 COMMENT ON COLUMN "policy"."verification_time" IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
274 COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "fully_frozen" but not "closed"';
275 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"';
276 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"';
277 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
278 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
279 COMMENT ON COLUMN "policy"."majority_num" IS 'Numerator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
280 COMMENT ON COLUMN "policy"."majority_den" IS 'Denominator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
281 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.';
284 CREATE TABLE "unit" (
285 "id" SERIAL4 PRIMARY KEY,
286 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
287 "active" BOOLEAN NOT NULL DEFAULT TRUE,
288 "name" TEXT NOT NULL,
289 "description" TEXT NOT NULL DEFAULT '',
290 "member_count" INT4,
291 "text_search_data" TSVECTOR );
292 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
293 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
294 CREATE INDEX "unit_active_idx" ON "unit" ("active");
295 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
296 CREATE TRIGGER "update_text_search_data"
297 BEFORE INSERT OR UPDATE ON "unit"
298 FOR EACH ROW EXECUTE PROCEDURE
299 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
300 "name", "description" );
302 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
304 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
305 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in units of this area';
306 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
309 CREATE TABLE "area" (
310 "id" SERIAL4 PRIMARY KEY,
311 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
312 "active" BOOLEAN NOT NULL DEFAULT TRUE,
313 "name" TEXT NOT NULL,
314 "description" TEXT NOT NULL DEFAULT '',
315 "direct_member_count" INT4,
316 "member_weight" INT4,
317 "autoreject_weight" INT4,
318 "text_search_data" TSVECTOR );
319 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
320 CREATE INDEX "area_active_idx" ON "area" ("active");
321 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
322 CREATE TRIGGER "update_text_search_data"
323 BEFORE INSERT OR UPDATE ON "area"
324 FOR EACH ROW EXECUTE PROCEDURE
325 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
326 "name", "description" );
328 COMMENT ON TABLE "area" IS 'Subject areas';
330 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
331 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"';
332 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
333 COMMENT ON COLUMN "area"."autoreject_weight" IS 'Sum of weight of members using the autoreject feature';
336 CREATE TABLE "area_setting" (
337 PRIMARY KEY ("member_id", "key", "area_id"),
338 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
339 "key" TEXT NOT NULL,
340 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
341 "value" TEXT NOT NULL );
343 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
346 CREATE TABLE "allowed_policy" (
347 PRIMARY KEY ("area_id", "policy_id"),
348 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
349 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
350 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
351 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
353 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
355 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
358 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
360 COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''half_freeze'' = saved state at end of discussion period, ''full_freeze'' = saved state at end of verification period';
363 CREATE TABLE "issue" (
364 "id" SERIAL4 PRIMARY KEY,
365 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
366 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
367 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
368 "accepted" TIMESTAMPTZ,
369 "half_frozen" TIMESTAMPTZ,
370 "fully_frozen" TIMESTAMPTZ,
371 "closed" TIMESTAMPTZ,
372 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
373 "cleaned" TIMESTAMPTZ,
374 "admission_time" INTERVAL NOT NULL,
375 "discussion_time" INTERVAL NOT NULL,
376 "verification_time" INTERVAL NOT NULL,
377 "voting_time" INTERVAL NOT NULL,
378 "snapshot" TIMESTAMPTZ,
379 "latest_snapshot_event" "snapshot_event",
380 "population" INT4,
381 "vote_now" INT4,
382 "vote_later" INT4,
383 "voter_count" INT4,
384 CONSTRAINT "valid_state" CHECK (
385 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
386 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
387 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
388 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
389 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
390 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
391 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
392 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
393 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ),
394 CONSTRAINT "state_change_order" CHECK (
395 "created" <= "accepted" AND
396 "accepted" <= "half_frozen" AND
397 "half_frozen" <= "fully_frozen" AND
398 "fully_frozen" <= "closed" ),
399 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
400 "cleaned" ISNULL OR "closed" NOTNULL ),
401 CONSTRAINT "last_snapshot_on_full_freeze"
402 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
403 CONSTRAINT "freeze_requires_snapshot"
404 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
405 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
406 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
407 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
408 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
409 CREATE INDEX "issue_created_idx" ON "issue" ("created");
410 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
411 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
412 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
413 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
414 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
415 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
417 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
419 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
420 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.';
421 COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed and voting has started; 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.';
422 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.';
423 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
424 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
425 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
426 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
427 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
428 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
429 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';
430 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';
431 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
432 COMMENT ON COLUMN "issue"."vote_now" IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"';
433 COMMENT ON COLUMN "issue"."vote_later" IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"';
434 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';
437 CREATE TABLE "issue_setting" (
438 PRIMARY KEY ("member_id", "key", "issue_id"),
439 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
440 "key" TEXT NOT NULL,
441 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
442 "value" TEXT NOT NULL );
444 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
447 CREATE TABLE "initiative" (
448 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
449 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
450 "id" SERIAL4 PRIMARY KEY,
451 "name" TEXT NOT NULL,
452 "discussion_url" TEXT,
453 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
454 "revoked" TIMESTAMPTZ,
455 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
456 "admitted" BOOLEAN,
457 "supporter_count" INT4,
458 "informed_supporter_count" INT4,
459 "satisfied_supporter_count" INT4,
460 "satisfied_informed_supporter_count" INT4,
461 "positive_votes" INT4,
462 "negative_votes" INT4,
463 "agreed" BOOLEAN,
464 "rank" INT4,
465 "text_search_data" TSVECTOR,
466 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
467 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
468 CONSTRAINT "revoked_initiatives_cant_be_admitted"
469 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
470 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"
471 CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "agreed" ISNULL)),
472 CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"
473 CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL),
474 CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"
475 CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) );
476 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
477 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
478 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
479 CREATE TRIGGER "update_text_search_data"
480 BEFORE INSERT OR UPDATE ON "initiative"
481 FOR EACH ROW EXECUTE PROCEDURE
482 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
483 "name", "discussion_url");
485 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.';
487 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
488 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
489 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
490 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
491 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
492 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
493 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
494 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
495 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
496 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"';
497 COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';
500 CREATE TABLE "battle" (
501 PRIMARY KEY ("issue_id", "winning_initiative_id", "losing_initiative_id"),
502 "issue_id" INT4,
503 "winning_initiative_id" INT4,
504 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
505 "losing_initiative_id" INT4,
506 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
507 "count" INT4 NOT NULL);
509 COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue';
512 CREATE TABLE "initiative_setting" (
513 PRIMARY KEY ("member_id", "key", "initiative_id"),
514 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
515 "key" TEXT NOT NULL,
516 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
517 "value" TEXT NOT NULL );
519 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
522 CREATE TABLE "draft" (
523 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
524 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
525 "id" SERIAL8 PRIMARY KEY,
526 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
527 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
528 "formatting_engine" TEXT,
529 "content" TEXT NOT NULL,
530 "text_search_data" TSVECTOR );
531 CREATE INDEX "draft_created_idx" ON "draft" ("created");
532 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
533 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
534 CREATE TRIGGER "update_text_search_data"
535 BEFORE INSERT OR UPDATE ON "draft"
536 FOR EACH ROW EXECUTE PROCEDURE
537 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
539 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.';
541 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
542 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
545 CREATE TABLE "rendered_draft" (
546 PRIMARY KEY ("draft_id", "format"),
547 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
548 "format" TEXT,
549 "content" TEXT NOT NULL );
551 COMMENT ON TABLE "rendered_draft" IS 'This table may be used by frontends to cache "rendered" drafts (e.g. HTML output generated from wiki text)';
554 CREATE TABLE "suggestion" (
555 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
556 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
557 "id" SERIAL8 PRIMARY KEY,
558 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
559 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
560 "name" TEXT NOT NULL,
561 "description" TEXT NOT NULL DEFAULT '',
562 "text_search_data" TSVECTOR,
563 "minus2_unfulfilled_count" INT4,
564 "minus2_fulfilled_count" INT4,
565 "minus1_unfulfilled_count" INT4,
566 "minus1_fulfilled_count" INT4,
567 "plus1_unfulfilled_count" INT4,
568 "plus1_fulfilled_count" INT4,
569 "plus2_unfulfilled_count" INT4,
570 "plus2_fulfilled_count" INT4 );
571 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
572 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
573 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
574 CREATE TRIGGER "update_text_search_data"
575 BEFORE INSERT OR UPDATE ON "suggestion"
576 FOR EACH ROW EXECUTE PROCEDURE
577 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
578 "name", "description");
580 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';
582 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
583 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
584 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
585 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
586 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
587 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
588 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
589 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
592 CREATE TABLE "suggestion_setting" (
593 PRIMARY KEY ("member_id", "key", "suggestion_id"),
594 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
595 "key" TEXT NOT NULL,
596 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
597 "value" TEXT NOT NULL );
599 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
602 CREATE TABLE "invite_code_unit" (
603 PRIMARY KEY ("invite_code_id", "unit_id"),
604 "invite_code_id" INT8 REFERENCES "invite_code" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
605 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
607 COMMENT ON TABLE "invite_code_unit" IS 'Units where accounts created with a given invite codes get voting rights';
610 CREATE TABLE "privilege" (
611 PRIMARY KEY ("unit_id", "member_id"),
612 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
613 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
614 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
615 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
616 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
617 "voting_right_manager" BOOLEAN NOT NULL DEFAULT FALSE,
618 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE );
620 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
622 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke admin privileges to/from other users';
623 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create or lock sub units';
624 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create or lock areas and set area parameters';
625 COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote inside the unit';
626 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to discuss and vote';
629 CREATE TABLE "membership" (
630 PRIMARY KEY ("area_id", "member_id"),
631 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
632 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
633 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
634 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
636 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
638 COMMENT ON COLUMN "membership"."autoreject" IS 'TRUE = member votes against all initiatives, if he is neither direct_ or delegating_voter; Entries in the "interest" table can override this setting.';
641 CREATE TABLE "interest" (
642 PRIMARY KEY ("issue_id", "member_id"),
643 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
644 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
645 "autoreject" BOOLEAN,
646 "voting_requested" BOOLEAN );
647 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
649 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.';
651 COMMENT ON COLUMN "interest"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure';
652 COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply';
655 CREATE TABLE "ignored_issue" (
656 PRIMARY KEY ("issue_id", "member_id"),
657 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
658 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
659 "new" BOOLEAN NOT NULL DEFAULT FALSE,
660 "accepted" BOOLEAN NOT NULL DEFAULT FALSE,
661 "half_frozen" BOOLEAN NOT NULL DEFAULT FALSE,
662 "fully_frozen" BOOLEAN NOT NULL DEFAULT FALSE );
663 CREATE INDEX "ignored_issue_member_id_idx" ON "ignored_issue" ("member_id");
665 COMMENT ON TABLE "ignored_issue" IS 'Table to store member specific options to ignore issues in selected states';
667 COMMENT ON COLUMN "ignored_issue"."new" IS 'Apply when issue is neither closed nor accepted';
668 COMMENT ON COLUMN "ignored_issue"."accepted" IS 'Apply when issue is accepted but not (half_)frozen or closed';
669 COMMENT ON COLUMN "ignored_issue"."half_frozen" IS 'Apply when issue is half_frozen but not fully_frozen or closed';
670 COMMENT ON COLUMN "ignored_issue"."fully_frozen" IS 'Apply when issue is fully_frozen (in voting) and not closed';
673 CREATE TABLE "initiator" (
674 PRIMARY KEY ("initiative_id", "member_id"),
675 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
676 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
677 "accepted" BOOLEAN );
678 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
680 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.';
682 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.';
685 CREATE TABLE "supporter" (
686 "issue_id" INT4 NOT NULL,
687 PRIMARY KEY ("initiative_id", "member_id"),
688 "initiative_id" INT4,
689 "member_id" INT4,
690 "draft_id" INT8 NOT NULL,
691 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
692 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
693 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
695 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.';
697 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
700 CREATE TABLE "opinion" (
701 "initiative_id" INT4 NOT NULL,
702 PRIMARY KEY ("suggestion_id", "member_id"),
703 "suggestion_id" INT8,
704 "member_id" INT4,
705 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
706 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
707 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
708 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
709 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
711 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.';
713 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
716 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
718 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
721 CREATE TABLE "delegation" (
722 "id" SERIAL8 PRIMARY KEY,
723 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
724 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
725 "scope" "delegation_scope" NOT NULL,
726 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
727 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
728 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
729 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
730 CONSTRAINT "no_unit_delegation_to_null"
731 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
732 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
733 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
734 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
735 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
736 UNIQUE ("unit_id", "truster_id"),
737 UNIQUE ("area_id", "truster_id"),
738 UNIQUE ("issue_id", "truster_id") );
739 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
740 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
742 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
744 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
745 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
746 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
749 CREATE TABLE "direct_population_snapshot" (
750 PRIMARY KEY ("issue_id", "event", "member_id"),
751 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
752 "event" "snapshot_event",
753 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
754 "weight" INT4 );
755 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
757 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
759 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
760 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
763 CREATE TABLE "delegating_population_snapshot" (
764 PRIMARY KEY ("issue_id", "event", "member_id"),
765 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
766 "event" "snapshot_event",
767 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
768 "weight" INT4,
769 "scope" "delegation_scope" NOT NULL,
770 "delegate_member_ids" INT4[] NOT NULL );
771 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
773 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
775 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
776 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
777 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
778 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"';
781 CREATE TABLE "direct_interest_snapshot" (
782 PRIMARY KEY ("issue_id", "event", "member_id"),
783 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
784 "event" "snapshot_event",
785 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
786 "weight" INT4,
787 "voting_requested" BOOLEAN );
788 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
790 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
792 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
793 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
794 COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';
797 CREATE TABLE "delegating_interest_snapshot" (
798 PRIMARY KEY ("issue_id", "event", "member_id"),
799 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
800 "event" "snapshot_event",
801 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
802 "weight" INT4,
803 "scope" "delegation_scope" NOT NULL,
804 "delegate_member_ids" INT4[] NOT NULL );
805 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
807 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
809 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
810 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
811 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
812 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"';
815 CREATE TABLE "direct_supporter_snapshot" (
816 "issue_id" INT4 NOT NULL,
817 PRIMARY KEY ("initiative_id", "event", "member_id"),
818 "initiative_id" INT4,
819 "event" "snapshot_event",
820 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
821 "informed" BOOLEAN NOT NULL,
822 "satisfied" BOOLEAN NOT NULL,
823 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
824 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
825 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
827 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
829 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
830 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
831 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
834 CREATE TABLE "direct_voter" (
835 PRIMARY KEY ("issue_id", "member_id"),
836 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
837 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
838 "weight" INT4,
839 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
840 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
842 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.';
844 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
845 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
848 CREATE TABLE "delegating_voter" (
849 PRIMARY KEY ("issue_id", "member_id"),
850 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
851 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
852 "weight" INT4,
853 "scope" "delegation_scope" NOT NULL,
854 "delegate_member_ids" INT4[] NOT NULL );
855 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
857 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
859 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
860 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
861 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"';
864 CREATE TABLE "vote" (
865 "issue_id" INT4 NOT NULL,
866 PRIMARY KEY ("initiative_id", "member_id"),
867 "initiative_id" INT4,
868 "member_id" INT4,
869 "grade" INT4,
870 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
871 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
872 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
874 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.';
876 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.';
879 CREATE TABLE "contingent" (
880 "time_frame" INTERVAL PRIMARY KEY,
881 "text_entry_limit" INT4,
882 "initiative_limit" INT4 );
884 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.';
886 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';
887 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
891 --------------------------------
892 -- Writing of history entries --
893 --------------------------------
895 CREATE FUNCTION "write_member_history_trigger"()
896 RETURNS TRIGGER
897 LANGUAGE 'plpgsql' VOLATILE AS $$
898 BEGIN
899 IF
900 NEW."active" != OLD."active" OR
901 NEW."name" != OLD."name"
902 THEN
903 INSERT INTO "member_history"
904 ("member_id", "active", "name")
905 VALUES (NEW."id", OLD."active", OLD."name");
906 END IF;
907 RETURN NULL;
908 END;
909 $$;
911 CREATE TRIGGER "write_member_history"
912 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
913 "write_member_history_trigger"();
915 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
916 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
920 ----------------------------
921 -- Additional constraints --
922 ----------------------------
925 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
926 RETURNS TRIGGER
927 LANGUAGE 'plpgsql' VOLATILE AS $$
928 BEGIN
929 IF NOT EXISTS (
930 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
931 ) THEN
932 --RAISE 'Cannot create issue without an initial initiative.' USING
933 -- ERRCODE = 'integrity_constraint_violation',
934 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
935 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
936 END IF;
937 RETURN NULL;
938 END;
939 $$;
941 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
942 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
943 FOR EACH ROW EXECUTE PROCEDURE
944 "issue_requires_first_initiative_trigger"();
946 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
947 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
950 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
951 RETURNS TRIGGER
952 LANGUAGE 'plpgsql' VOLATILE AS $$
953 DECLARE
954 "reference_lost" BOOLEAN;
955 BEGIN
956 IF TG_OP = 'DELETE' THEN
957 "reference_lost" := TRUE;
958 ELSE
959 "reference_lost" := NEW."issue_id" != OLD."issue_id";
960 END IF;
961 IF
962 "reference_lost" AND NOT EXISTS (
963 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
964 )
965 THEN
966 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
967 END IF;
968 RETURN NULL;
969 END;
970 $$;
972 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
973 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
974 FOR EACH ROW EXECUTE PROCEDURE
975 "last_initiative_deletes_issue_trigger"();
977 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
978 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
981 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
982 RETURNS TRIGGER
983 LANGUAGE 'plpgsql' VOLATILE AS $$
984 BEGIN
985 IF NOT EXISTS (
986 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
987 ) THEN
988 --RAISE 'Cannot create initiative without an initial draft.' USING
989 -- ERRCODE = 'integrity_constraint_violation',
990 -- HINT = 'Create issue, initiative and draft within the same transaction.';
991 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
992 END IF;
993 RETURN NULL;
994 END;
995 $$;
997 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
998 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
999 FOR EACH ROW EXECUTE PROCEDURE
1000 "initiative_requires_first_draft_trigger"();
1002 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
1003 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
1006 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
1007 RETURNS TRIGGER
1008 LANGUAGE 'plpgsql' VOLATILE AS $$
1009 DECLARE
1010 "reference_lost" BOOLEAN;
1011 BEGIN
1012 IF TG_OP = 'DELETE' THEN
1013 "reference_lost" := TRUE;
1014 ELSE
1015 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
1016 END IF;
1017 IF
1018 "reference_lost" AND NOT EXISTS (
1019 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
1020 )
1021 THEN
1022 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
1023 END IF;
1024 RETURN NULL;
1025 END;
1026 $$;
1028 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
1029 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
1030 FOR EACH ROW EXECUTE PROCEDURE
1031 "last_draft_deletes_initiative_trigger"();
1033 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
1034 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
1037 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
1038 RETURNS TRIGGER
1039 LANGUAGE 'plpgsql' VOLATILE AS $$
1040 BEGIN
1041 IF NOT EXISTS (
1042 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
1043 ) THEN
1044 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
1045 END IF;
1046 RETURN NULL;
1047 END;
1048 $$;
1050 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
1051 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
1052 FOR EACH ROW EXECUTE PROCEDURE
1053 "suggestion_requires_first_opinion_trigger"();
1055 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1056 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1059 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1060 RETURNS TRIGGER
1061 LANGUAGE 'plpgsql' VOLATILE AS $$
1062 DECLARE
1063 "reference_lost" BOOLEAN;
1064 BEGIN
1065 IF TG_OP = 'DELETE' THEN
1066 "reference_lost" := TRUE;
1067 ELSE
1068 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1069 END IF;
1070 IF
1071 "reference_lost" AND NOT EXISTS (
1072 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1073 )
1074 THEN
1075 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1076 END IF;
1077 RETURN NULL;
1078 END;
1079 $$;
1081 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1082 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1083 FOR EACH ROW EXECUTE PROCEDURE
1084 "last_opinion_deletes_suggestion_trigger"();
1086 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1087 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1091 ---------------------------------------------------------------
1092 -- Ensure that votes are not modified when issues are frozen --
1093 ---------------------------------------------------------------
1095 -- NOTE: Frontends should ensure this anyway, but in case of programming
1096 -- errors the following triggers ensure data integrity.
1099 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1100 RETURNS TRIGGER
1101 LANGUAGE 'plpgsql' VOLATILE AS $$
1102 DECLARE
1103 "issue_id_v" "issue"."id"%TYPE;
1104 "issue_row" "issue"%ROWTYPE;
1105 BEGIN
1106 IF TG_OP = 'DELETE' THEN
1107 "issue_id_v" := OLD."issue_id";
1108 ELSE
1109 "issue_id_v" := NEW."issue_id";
1110 END IF;
1111 SELECT INTO "issue_row" * FROM "issue"
1112 WHERE "id" = "issue_id_v" FOR SHARE;
1113 IF "issue_row"."closed" NOTNULL THEN
1114 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
1115 END IF;
1116 RETURN NULL;
1117 END;
1118 $$;
1120 CREATE TRIGGER "forbid_changes_on_closed_issue"
1121 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1122 FOR EACH ROW EXECUTE PROCEDURE
1123 "forbid_changes_on_closed_issue_trigger"();
1125 CREATE TRIGGER "forbid_changes_on_closed_issue"
1126 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1127 FOR EACH ROW EXECUTE PROCEDURE
1128 "forbid_changes_on_closed_issue_trigger"();
1130 CREATE TRIGGER "forbid_changes_on_closed_issue"
1131 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1132 FOR EACH ROW EXECUTE PROCEDURE
1133 "forbid_changes_on_closed_issue_trigger"();
1135 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"';
1136 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';
1137 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';
1138 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';
1142 --------------------------------------------------------------------
1143 -- Auto-retrieval of fields only needed for referential integrity --
1144 --------------------------------------------------------------------
1147 CREATE FUNCTION "autofill_issue_id_trigger"()
1148 RETURNS TRIGGER
1149 LANGUAGE 'plpgsql' VOLATILE AS $$
1150 BEGIN
1151 IF NEW."issue_id" ISNULL THEN
1152 SELECT "issue_id" INTO NEW."issue_id"
1153 FROM "initiative" WHERE "id" = NEW."initiative_id";
1154 END IF;
1155 RETURN NEW;
1156 END;
1157 $$;
1159 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1160 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1162 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1163 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1165 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1166 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1167 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1170 CREATE FUNCTION "autofill_initiative_id_trigger"()
1171 RETURNS TRIGGER
1172 LANGUAGE 'plpgsql' VOLATILE AS $$
1173 BEGIN
1174 IF NEW."initiative_id" ISNULL THEN
1175 SELECT "initiative_id" INTO NEW."initiative_id"
1176 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1177 END IF;
1178 RETURN NEW;
1179 END;
1180 $$;
1182 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1183 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1185 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1186 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1190 -----------------------------------------------------
1191 -- Automatic calculation of certain default values --
1192 -----------------------------------------------------
1195 CREATE FUNCTION "copy_timings_trigger"()
1196 RETURNS TRIGGER
1197 LANGUAGE 'plpgsql' VOLATILE AS $$
1198 DECLARE
1199 "policy_row" "policy"%ROWTYPE;
1200 BEGIN
1201 SELECT * INTO "policy_row" FROM "policy"
1202 WHERE "id" = NEW."policy_id";
1203 IF NEW."admission_time" ISNULL THEN
1204 NEW."admission_time" := "policy_row"."admission_time";
1205 END IF;
1206 IF NEW."discussion_time" ISNULL THEN
1207 NEW."discussion_time" := "policy_row"."discussion_time";
1208 END IF;
1209 IF NEW."verification_time" ISNULL THEN
1210 NEW."verification_time" := "policy_row"."verification_time";
1211 END IF;
1212 IF NEW."voting_time" ISNULL THEN
1213 NEW."voting_time" := "policy_row"."voting_time";
1214 END IF;
1215 RETURN NEW;
1216 END;
1217 $$;
1219 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1220 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1222 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1223 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1226 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
1227 RETURNS TRIGGER
1228 LANGUAGE 'plpgsql' VOLATILE AS $$
1229 BEGIN
1230 IF NEW."draft_id" ISNULL THEN
1231 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1232 WHERE "initiative_id" = NEW."initiative_id";
1233 END IF;
1234 RETURN NEW;
1235 END;
1236 $$;
1238 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1239 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
1241 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
1242 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';
1246 ----------------------------------------
1247 -- Automatic creation of dependencies --
1248 ----------------------------------------
1251 CREATE FUNCTION "autocreate_interest_trigger"()
1252 RETURNS TRIGGER
1253 LANGUAGE 'plpgsql' VOLATILE AS $$
1254 BEGIN
1255 IF NOT EXISTS (
1256 SELECT NULL FROM "initiative" JOIN "interest"
1257 ON "initiative"."issue_id" = "interest"."issue_id"
1258 WHERE "initiative"."id" = NEW."initiative_id"
1259 AND "interest"."member_id" = NEW."member_id"
1260 ) THEN
1261 BEGIN
1262 INSERT INTO "interest" ("issue_id", "member_id")
1263 SELECT "issue_id", NEW."member_id"
1264 FROM "initiative" WHERE "id" = NEW."initiative_id";
1265 EXCEPTION WHEN unique_violation THEN END;
1266 END IF;
1267 RETURN NEW;
1268 END;
1269 $$;
1271 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1272 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1274 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1275 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';
1278 CREATE FUNCTION "autocreate_supporter_trigger"()
1279 RETURNS TRIGGER
1280 LANGUAGE 'plpgsql' VOLATILE AS $$
1281 BEGIN
1282 IF NOT EXISTS (
1283 SELECT NULL FROM "suggestion" JOIN "supporter"
1284 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1285 WHERE "suggestion"."id" = NEW."suggestion_id"
1286 AND "supporter"."member_id" = NEW."member_id"
1287 ) THEN
1288 BEGIN
1289 INSERT INTO "supporter" ("initiative_id", "member_id")
1290 SELECT "initiative_id", NEW."member_id"
1291 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1292 EXCEPTION WHEN unique_violation THEN END;
1293 END IF;
1294 RETURN NEW;
1295 END;
1296 $$;
1298 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1299 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1301 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1302 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.';
1306 ------------------------------------------
1307 -- Views and helper functions for views --
1308 ------------------------------------------
1311 CREATE VIEW "unit_delegation" AS
1312 SELECT
1313 "unit"."id" AS "unit_id",
1314 "delegation"."id",
1315 "delegation"."truster_id",
1316 "delegation"."trustee_id",
1317 "delegation"."scope"
1318 FROM "unit"
1319 JOIN "delegation"
1320 ON "delegation"."unit_id" = "unit"."id"
1321 JOIN "member"
1322 ON "delegation"."truster_id" = "member"."id"
1323 JOIN "privilege"
1324 ON "delegation"."unit_id" = "privilege"."unit_id"
1325 AND "delegation"."truster_id" = "privilege"."member_id"
1326 WHERE "member"."active" AND "privilege"."voting_right";
1328 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
1331 CREATE VIEW "area_delegation" AS
1332 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1333 "area"."id" AS "area_id",
1334 "delegation"."id",
1335 "delegation"."truster_id",
1336 "delegation"."trustee_id",
1337 "delegation"."scope"
1338 FROM "area"
1339 JOIN "delegation"
1340 ON "delegation"."unit_id" = "area"."unit_id"
1341 OR "delegation"."area_id" = "area"."id"
1342 JOIN "member"
1343 ON "delegation"."truster_id" = "member"."id"
1344 JOIN "privilege"
1345 ON "area"."unit_id" = "privilege"."unit_id"
1346 AND "delegation"."truster_id" = "privilege"."member_id"
1347 WHERE "member"."active" AND "privilege"."voting_right"
1348 ORDER BY
1349 "area"."id",
1350 "delegation"."truster_id",
1351 "delegation"."scope" DESC;
1353 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
1356 CREATE VIEW "issue_delegation" AS
1357 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1358 "issue"."id" AS "issue_id",
1359 "delegation"."id",
1360 "delegation"."truster_id",
1361 "delegation"."trustee_id",
1362 "delegation"."scope"
1363 FROM "issue"
1364 JOIN "area"
1365 ON "area"."id" = "issue"."area_id"
1366 JOIN "delegation"
1367 ON "delegation"."unit_id" = "area"."unit_id"
1368 OR "delegation"."area_id" = "area"."id"
1369 OR "delegation"."issue_id" = "issue"."id"
1370 JOIN "member"
1371 ON "delegation"."truster_id" = "member"."id"
1372 JOIN "privilege"
1373 ON "area"."unit_id" = "privilege"."unit_id"
1374 AND "delegation"."truster_id" = "privilege"."member_id"
1375 WHERE "member"."active" AND "privilege"."voting_right"
1376 ORDER BY
1377 "issue"."id",
1378 "delegation"."truster_id",
1379 "delegation"."scope" DESC;
1381 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
1384 CREATE FUNCTION "membership_weight_with_skipping"
1385 ( "area_id_p" "area"."id"%TYPE,
1386 "member_id_p" "member"."id"%TYPE,
1387 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1388 RETURNS INT4
1389 LANGUAGE 'plpgsql' STABLE AS $$
1390 DECLARE
1391 "sum_v" INT4;
1392 "delegation_row" "area_delegation"%ROWTYPE;
1393 BEGIN
1394 "sum_v" := 1;
1395 FOR "delegation_row" IN
1396 SELECT "area_delegation".*
1397 FROM "area_delegation" LEFT JOIN "membership"
1398 ON "membership"."area_id" = "area_id_p"
1399 AND "membership"."member_id" = "area_delegation"."truster_id"
1400 WHERE "area_delegation"."area_id" = "area_id_p"
1401 AND "area_delegation"."trustee_id" = "member_id_p"
1402 AND "membership"."member_id" ISNULL
1403 LOOP
1404 IF NOT
1405 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1406 THEN
1407 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1408 "area_id_p",
1409 "delegation_row"."truster_id",
1410 "skip_member_ids_p" || "delegation_row"."truster_id"
1411 );
1412 END IF;
1413 END LOOP;
1414 RETURN "sum_v";
1415 END;
1416 $$;
1418 COMMENT ON FUNCTION "membership_weight_with_skipping"
1419 ( "area"."id"%TYPE,
1420 "member"."id"%TYPE,
1421 INT4[] )
1422 IS 'Helper function for "membership_weight" function';
1425 CREATE FUNCTION "membership_weight"
1426 ( "area_id_p" "area"."id"%TYPE,
1427 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1428 RETURNS INT4
1429 LANGUAGE 'plpgsql' STABLE AS $$
1430 BEGIN
1431 RETURN "membership_weight_with_skipping"(
1432 "area_id_p",
1433 "member_id_p",
1434 ARRAY["member_id_p"]
1435 );
1436 END;
1437 $$;
1439 COMMENT ON FUNCTION "membership_weight"
1440 ( "area"."id"%TYPE,
1441 "member"."id"%TYPE )
1442 IS 'Calculates the potential voting weight of a member in a given area';
1445 CREATE VIEW "member_count_view" AS
1446 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1448 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1451 CREATE VIEW "unit_member_count" AS
1452 SELECT
1453 "unit"."id" AS "unit_id",
1454 sum("member"."id") AS "member_count"
1455 FROM "unit"
1456 LEFT JOIN "privilege"
1457 ON "privilege"."unit_id" = "unit"."id"
1458 AND "privilege"."voting_right"
1459 LEFT JOIN "member"
1460 ON "member"."id" = "privilege"."member_id"
1461 AND "member"."active"
1462 GROUP BY "unit"."id";
1464 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
1467 CREATE VIEW "area_member_count" AS
1468 SELECT
1469 "area"."id" AS "area_id",
1470 count("member"."id") AS "direct_member_count",
1471 coalesce(
1472 sum(
1473 CASE WHEN "member"."id" NOTNULL THEN
1474 "membership_weight"("area"."id", "member"."id")
1475 ELSE 0 END
1476 )
1477 ) AS "member_weight",
1478 coalesce(
1479 sum(
1480 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
1481 "membership_weight"("area"."id", "member"."id")
1482 ELSE 0 END
1483 )
1484 ) AS "autoreject_weight"
1485 FROM "area"
1486 LEFT JOIN "membership"
1487 ON "area"."id" = "membership"."area_id"
1488 LEFT JOIN "privilege"
1489 ON "privilege"."unit_id" = "area"."unit_id"
1490 AND "privilege"."member_id" = "membership"."member_id"
1491 AND "privilege"."voting_right"
1492 LEFT JOIN "member"
1493 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
1494 AND "member"."active"
1495 GROUP BY "area"."id";
1497 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count", "member_weight" and "autoreject_weight" columns of table "area"';
1500 CREATE VIEW "opening_draft" AS
1501 SELECT "draft".* FROM (
1502 SELECT
1503 "initiative"."id" AS "initiative_id",
1504 min("draft"."id") AS "draft_id"
1505 FROM "initiative" JOIN "draft"
1506 ON "initiative"."id" = "draft"."initiative_id"
1507 GROUP BY "initiative"."id"
1508 ) AS "subquery"
1509 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1511 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1514 CREATE VIEW "current_draft" AS
1515 SELECT "draft".* FROM (
1516 SELECT
1517 "initiative"."id" AS "initiative_id",
1518 max("draft"."id") AS "draft_id"
1519 FROM "initiative" JOIN "draft"
1520 ON "initiative"."id" = "draft"."initiative_id"
1521 GROUP BY "initiative"."id"
1522 ) AS "subquery"
1523 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1525 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1528 CREATE VIEW "critical_opinion" AS
1529 SELECT * FROM "opinion"
1530 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
1531 OR ("degree" = -2 AND "fulfilled" = TRUE);
1533 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
1536 CREATE VIEW "battle_view" AS
1537 SELECT
1538 "issue"."id" AS "issue_id",
1539 "winning_initiative"."id" AS "winning_initiative_id",
1540 "losing_initiative"."id" AS "losing_initiative_id",
1541 sum(
1542 CASE WHEN
1543 coalesce("better_vote"."grade", 0) >
1544 coalesce("worse_vote"."grade", 0)
1545 THEN "direct_voter"."weight" ELSE 0 END
1546 ) AS "count"
1547 FROM "issue"
1548 LEFT JOIN "direct_voter"
1549 ON "issue"."id" = "direct_voter"."issue_id"
1550 JOIN "initiative" AS "winning_initiative"
1551 ON "issue"."id" = "winning_initiative"."issue_id"
1552 AND "winning_initiative"."agreed"
1553 JOIN "initiative" AS "losing_initiative"
1554 ON "issue"."id" = "losing_initiative"."issue_id"
1555 AND "losing_initiative"."agreed"
1556 LEFT JOIN "vote" AS "better_vote"
1557 ON "direct_voter"."member_id" = "better_vote"."member_id"
1558 AND "winning_initiative"."id" = "better_vote"."initiative_id"
1559 LEFT JOIN "vote" AS "worse_vote"
1560 ON "direct_voter"."member_id" = "worse_vote"."member_id"
1561 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
1562 WHERE "issue"."closed" NOTNULL
1563 AND "issue"."cleaned" ISNULL
1564 AND "winning_initiative"."id" != "losing_initiative"."id"
1565 GROUP BY
1566 "issue"."id",
1567 "winning_initiative"."id",
1568 "losing_initiative"."id";
1570 COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative to another; Used to fill "battle" table';
1573 CREATE VIEW "expired_session" AS
1574 SELECT * FROM "session" WHERE now() > "expiry";
1576 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
1577 DELETE FROM "session" WHERE "ident" = OLD."ident";
1579 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
1580 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
1583 CREATE VIEW "open_issue" AS
1584 SELECT * FROM "issue" WHERE "closed" ISNULL;
1586 COMMENT ON VIEW "open_issue" IS 'All open issues';
1589 CREATE VIEW "issue_with_ranks_missing" AS
1590 SELECT * FROM "issue"
1591 WHERE "fully_frozen" NOTNULL
1592 AND "closed" NOTNULL
1593 AND "ranks_available" = FALSE;
1595 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
1598 CREATE VIEW "member_contingent" AS
1599 SELECT
1600 "member"."id" AS "member_id",
1601 "contingent"."time_frame",
1602 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
1603 (
1604 SELECT count(1) FROM "draft"
1605 WHERE "draft"."author_id" = "member"."id"
1606 AND "draft"."created" > now() - "contingent"."time_frame"
1607 ) + (
1608 SELECT count(1) FROM "suggestion"
1609 WHERE "suggestion"."author_id" = "member"."id"
1610 AND "suggestion"."created" > now() - "contingent"."time_frame"
1611 )
1612 ELSE NULL END AS "text_entry_count",
1613 "contingent"."text_entry_limit",
1614 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
1615 SELECT count(1) FROM "opening_draft"
1616 WHERE "opening_draft"."author_id" = "member"."id"
1617 AND "opening_draft"."created" > now() - "contingent"."time_frame"
1618 ) ELSE NULL END AS "initiative_count",
1619 "contingent"."initiative_limit"
1620 FROM "member" CROSS JOIN "contingent";
1622 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
1624 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
1625 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
1628 CREATE VIEW "member_contingent_left" AS
1629 SELECT
1630 "member_id",
1631 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
1632 max("initiative_limit" - "initiative_count") AS "initiatives_left"
1633 FROM "member_contingent" GROUP BY "member_id";
1635 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.';
1638 CREATE TYPE "timeline_event" AS ENUM (
1639 'issue_created',
1640 'issue_canceled',
1641 'issue_accepted',
1642 'issue_half_frozen',
1643 'issue_finished_without_voting',
1644 'issue_voting_started',
1645 'issue_finished_after_voting',
1646 'initiative_created',
1647 'initiative_revoked',
1648 'draft_created',
1649 'suggestion_created');
1651 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables';
1654 CREATE VIEW "timeline_issue" AS
1655 SELECT
1656 "created" AS "occurrence",
1657 'issue_created'::"timeline_event" AS "event",
1658 "id" AS "issue_id"
1659 FROM "issue"
1660 UNION ALL
1661 SELECT
1662 "closed" AS "occurrence",
1663 'issue_canceled'::"timeline_event" AS "event",
1664 "id" AS "issue_id"
1665 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
1666 UNION ALL
1667 SELECT
1668 "accepted" AS "occurrence",
1669 'issue_accepted'::"timeline_event" AS "event",
1670 "id" AS "issue_id"
1671 FROM "issue" WHERE "accepted" NOTNULL
1672 UNION ALL
1673 SELECT
1674 "half_frozen" AS "occurrence",
1675 'issue_half_frozen'::"timeline_event" AS "event",
1676 "id" AS "issue_id"
1677 FROM "issue" WHERE "half_frozen" NOTNULL
1678 UNION ALL
1679 SELECT
1680 "fully_frozen" AS "occurrence",
1681 'issue_voting_started'::"timeline_event" AS "event",
1682 "id" AS "issue_id"
1683 FROM "issue"
1684 WHERE "fully_frozen" NOTNULL
1685 AND ("closed" ISNULL OR "closed" != "fully_frozen")
1686 UNION ALL
1687 SELECT
1688 "closed" AS "occurrence",
1689 CASE WHEN "fully_frozen" = "closed" THEN
1690 'issue_finished_without_voting'::"timeline_event"
1691 ELSE
1692 'issue_finished_after_voting'::"timeline_event"
1693 END AS "event",
1694 "id" AS "issue_id"
1695 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
1697 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view';
1700 CREATE VIEW "timeline_initiative" AS
1701 SELECT
1702 "created" AS "occurrence",
1703 'initiative_created'::"timeline_event" AS "event",
1704 "id" AS "initiative_id"
1705 FROM "initiative"
1706 UNION ALL
1707 SELECT
1708 "revoked" AS "occurrence",
1709 'initiative_revoked'::"timeline_event" AS "event",
1710 "id" AS "initiative_id"
1711 FROM "initiative" WHERE "revoked" NOTNULL;
1713 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view';
1716 CREATE VIEW "timeline_draft" AS
1717 SELECT
1718 "created" AS "occurrence",
1719 'draft_created'::"timeline_event" AS "event",
1720 "id" AS "draft_id"
1721 FROM "draft";
1723 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view';
1726 CREATE VIEW "timeline_suggestion" AS
1727 SELECT
1728 "created" AS "occurrence",
1729 'suggestion_created'::"timeline_event" AS "event",
1730 "id" AS "suggestion_id"
1731 FROM "suggestion";
1733 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view';
1736 CREATE VIEW "timeline" AS
1737 SELECT
1738 "occurrence",
1739 "event",
1740 "issue_id",
1741 NULL AS "initiative_id",
1742 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
1743 NULL::INT8 AS "suggestion_id"
1744 FROM "timeline_issue"
1745 UNION ALL
1746 SELECT
1747 "occurrence",
1748 "event",
1749 NULL AS "issue_id",
1750 "initiative_id",
1751 NULL AS "draft_id",
1752 NULL AS "suggestion_id"
1753 FROM "timeline_initiative"
1754 UNION ALL
1755 SELECT
1756 "occurrence",
1757 "event",
1758 NULL AS "issue_id",
1759 NULL AS "initiative_id",
1760 "draft_id",
1761 NULL AS "suggestion_id"
1762 FROM "timeline_draft"
1763 UNION ALL
1764 SELECT
1765 "occurrence",
1766 "event",
1767 NULL AS "issue_id",
1768 NULL AS "initiative_id",
1769 NULL AS "draft_id",
1770 "suggestion_id"
1771 FROM "timeline_suggestion";
1773 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system';
1777 --------------------------------------------------
1778 -- Set returning function for delegation chains --
1779 --------------------------------------------------
1782 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
1783 ('first', 'intermediate', 'last', 'repetition');
1785 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
1788 CREATE TYPE "delegation_chain_row" AS (
1789 "index" INT4,
1790 "member_id" INT4,
1791 "member_valid" BOOLEAN,
1792 "participation" BOOLEAN,
1793 "overridden" BOOLEAN,
1794 "scope_in" "delegation_scope",
1795 "scope_out" "delegation_scope",
1796 "disabled_out" BOOLEAN,
1797 "loop" "delegation_chain_loop_tag" );
1799 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
1801 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
1802 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';
1803 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
1804 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
1805 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
1806 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
1807 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
1810 CREATE FUNCTION "delegation_chain"
1811 ( "member_id_p" "member"."id"%TYPE,
1812 "unit_id_p" "unit"."id"%TYPE,
1813 "area_id_p" "area"."id"%TYPE,
1814 "issue_id_p" "issue"."id"%TYPE,
1815 "simulate_trustee_id_p" "member"."id"%TYPE )
1816 RETURNS SETOF "delegation_chain_row"
1817 LANGUAGE 'plpgsql' STABLE AS $$
1818 DECLARE
1819 "scope_v" "delegation_scope";
1820 "unit_id_v" "unit"."id"%TYPE;
1821 "area_id_v" "area"."id"%TYPE;
1822 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
1823 "loop_member_id_v" "member"."id"%TYPE;
1824 "output_row" "delegation_chain_row";
1825 "output_rows" "delegation_chain_row"[];
1826 "delegation_row" "delegation"%ROWTYPE;
1827 "row_count" INT4;
1828 "i" INT4;
1829 "loop_v" BOOLEAN;
1830 BEGIN
1831 IF
1832 "unit_id_p" NOTNULL AND
1833 "area_id_p" ISNULL AND
1834 "issue_id_p" ISNULL
1835 THEN
1836 "scope_v" := 'unit';
1837 "unit_id_v" := "unit_id_p";
1838 ELSIF
1839 "unit_id_p" ISNULL AND
1840 "area_id_p" NOTNULL AND
1841 "issue_id_p" ISNULL
1842 THEN
1843 "scope_v" := 'area';
1844 "area_id_v" := "area_id_p";
1845 SELECT "unit_id" INTO "unit_id_v"
1846 FROM "area" WHERE "id" = "area_id_v";
1847 ELSIF
1848 "unit_id_p" ISNULL AND
1849 "area_id_p" ISNULL AND
1850 "issue_id_p" NOTNULL
1851 THEN
1852 "scope_v" := 'issue';
1853 SELECT "area_id" INTO "area_id_v"
1854 FROM "issue" WHERE "id" = "issue_id_p";
1855 SELECT "unit_id" INTO "unit_id_v"
1856 FROM "area" WHERE "id" = "area_id_v";
1857 ELSE
1858 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
1859 END IF;
1860 "visited_member_ids" := '{}';
1861 "loop_member_id_v" := NULL;
1862 "output_rows" := '{}';
1863 "output_row"."index" := 0;
1864 "output_row"."member_id" := "member_id_p";
1865 "output_row"."member_valid" := TRUE;
1866 "output_row"."participation" := FALSE;
1867 "output_row"."overridden" := FALSE;
1868 "output_row"."disabled_out" := FALSE;
1869 "output_row"."scope_out" := NULL;
1870 LOOP
1871 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
1872 "loop_member_id_v" := "output_row"."member_id";
1873 ELSE
1874 "visited_member_ids" :=
1875 "visited_member_ids" || "output_row"."member_id";
1876 END IF;
1877 IF "output_row"."participation" THEN
1878 "output_row"."overridden" := TRUE;
1879 END IF;
1880 "output_row"."scope_in" := "output_row"."scope_out";
1881 IF EXISTS (
1882 SELECT NULL FROM "member" JOIN "privilege"
1883 ON "privilege"."member_id" = "member"."id"
1884 AND "privilege"."unit_id" = "unit_id_v"
1885 WHERE "id" = "output_row"."member_id"
1886 AND "member"."active" AND "privilege"."voting_right"
1887 ) THEN
1888 IF "scope_v" = 'unit' THEN
1889 SELECT * INTO "delegation_row" FROM "delegation"
1890 WHERE "truster_id" = "output_row"."member_id"
1891 AND "unit_id" = "unit_id_v";
1892 ELSIF "scope_v" = 'area' THEN
1893 "output_row"."participation" := EXISTS (
1894 SELECT NULL FROM "membership"
1895 WHERE "area_id" = "area_id_p"
1896 AND "member_id" = "output_row"."member_id"
1897 );
1898 SELECT * INTO "delegation_row" FROM "delegation"
1899 WHERE "truster_id" = "output_row"."member_id"
1900 AND (
1901 "unit_id" = "unit_id_v" OR
1902 "area_id" = "area_id_v"
1903 )
1904 ORDER BY "scope" DESC;
1905 ELSIF "scope_v" = 'issue' THEN
1906 "output_row"."participation" := EXISTS (
1907 SELECT NULL FROM "interest"
1908 WHERE "issue_id" = "issue_id_p"
1909 AND "member_id" = "output_row"."member_id"
1910 );
1911 SELECT * INTO "delegation_row" FROM "delegation"
1912 WHERE "truster_id" = "output_row"."member_id"
1913 AND (
1914 "unit_id" = "unit_id_v" OR
1915 "area_id" = "area_id_v" OR
1916 "issue_id" = "issue_id_p"
1917 )
1918 ORDER BY "scope" DESC;
1919 END IF;
1920 ELSE
1921 "output_row"."member_valid" := FALSE;
1922 "output_row"."participation" := FALSE;
1923 "output_row"."scope_out" := NULL;
1924 "delegation_row" := ROW(NULL);
1925 END IF;
1926 IF
1927 "output_row"."member_id" = "member_id_p" AND
1928 "simulate_trustee_id_p" NOTNULL
1929 THEN
1930 "output_row"."scope_out" := "scope_v";
1931 "output_rows" := "output_rows" || "output_row";
1932 "output_row"."member_id" := "simulate_trustee_id_p";
1933 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
1934 "output_row"."scope_out" := "delegation_row"."scope";
1935 "output_rows" := "output_rows" || "output_row";
1936 "output_row"."member_id" := "delegation_row"."trustee_id";
1937 ELSIF "delegation_row"."scope" NOTNULL THEN
1938 "output_row"."scope_out" := "delegation_row"."scope";
1939 "output_row"."disabled_out" := TRUE;
1940 "output_rows" := "output_rows" || "output_row";
1941 EXIT;
1942 ELSE
1943 "output_row"."scope_out" := NULL;
1944 "output_rows" := "output_rows" || "output_row";
1945 EXIT;
1946 END IF;
1947 EXIT WHEN "loop_member_id_v" NOTNULL;
1948 "output_row"."index" := "output_row"."index" + 1;
1949 END LOOP;
1950 "row_count" := array_upper("output_rows", 1);
1951 "i" := 1;
1952 "loop_v" := FALSE;
1953 LOOP
1954 "output_row" := "output_rows"["i"];
1955 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
1956 IF "loop_v" THEN
1957 IF "i" + 1 = "row_count" THEN
1958 "output_row"."loop" := 'last';
1959 ELSIF "i" = "row_count" THEN
1960 "output_row"."loop" := 'repetition';
1961 ELSE
1962 "output_row"."loop" := 'intermediate';
1963 END IF;
1964 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
1965 "output_row"."loop" := 'first';
1966 "loop_v" := TRUE;
1967 END IF;
1968 IF "scope_v" = 'unit' THEN
1969 "output_row"."participation" := NULL;
1970 END IF;
1971 RETURN NEXT "output_row";
1972 "i" := "i" + 1;
1973 END LOOP;
1974 RETURN;
1975 END;
1976 $$;
1978 COMMENT ON FUNCTION "delegation_chain"
1979 ( "member"."id"%TYPE,
1980 "unit"."id"%TYPE,
1981 "area"."id"%TYPE,
1982 "issue"."id"%TYPE,
1983 "member"."id"%TYPE )
1984 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
1987 CREATE FUNCTION "delegation_chain"
1988 ( "member_id_p" "member"."id"%TYPE,
1989 "unit_id_p" "unit"."id"%TYPE,
1990 "area_id_p" "area"."id"%TYPE,
1991 "issue_id_p" "issue"."id"%TYPE )
1992 RETURNS SETOF "delegation_chain_row"
1993 LANGUAGE 'plpgsql' STABLE AS $$
1994 DECLARE
1995 "result_row" "delegation_chain_row";
1996 BEGIN
1997 FOR "result_row" IN
1998 SELECT * FROM "delegation_chain"(
1999 "member_id_p", "area_id_p", "issue_id_p", NULL
2000 )
2001 LOOP
2002 RETURN NEXT "result_row";
2003 END LOOP;
2004 RETURN;
2005 END;
2006 $$;
2008 COMMENT ON FUNCTION "delegation_chain"
2009 ( "member"."id"%TYPE,
2010 "unit"."id"%TYPE,
2011 "area"."id"%TYPE,
2012 "issue"."id"%TYPE )
2013 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
2017 ------------------------------
2018 -- Comparison by vote count --
2019 ------------------------------
2021 CREATE FUNCTION "vote_ratio"
2022 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
2023 "negative_votes_p" "initiative"."negative_votes"%TYPE )
2024 RETURNS FLOAT8
2025 LANGUAGE 'plpgsql' STABLE AS $$
2026 BEGIN
2027 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
2028 RETURN
2029 "positive_votes_p"::FLOAT8 /
2030 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
2031 ELSIF "positive_votes_p" > 0 THEN
2032 RETURN "positive_votes_p";
2033 ELSIF "negative_votes_p" > 0 THEN
2034 RETURN 1 - "negative_votes_p";
2035 ELSE
2036 RETURN 0.5;
2037 END IF;
2038 END;
2039 $$;
2041 COMMENT ON FUNCTION "vote_ratio"
2042 ( "initiative"."positive_votes"%TYPE,
2043 "initiative"."negative_votes"%TYPE )
2044 IS 'Returns a number, which can be used for comparison of initiatives based on count of approvals and disapprovals. Greater numbers indicate a better result. This function is NOT injective.';
2048 ------------------------------------------------
2049 -- Locking for snapshots and voting procedure --
2050 ------------------------------------------------
2053 CREATE FUNCTION "share_row_lock_issue_trigger"()
2054 RETURNS TRIGGER
2055 LANGUAGE 'plpgsql' VOLATILE AS $$
2056 BEGIN
2057 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2058 PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;
2059 END IF;
2060 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2061 PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;
2062 RETURN NEW;
2063 ELSE
2064 RETURN OLD;
2065 END IF;
2066 END;
2067 $$;
2069 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';
2072 CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()
2073 RETURNS TRIGGER
2074 LANGUAGE 'plpgsql' VOLATILE AS $$
2075 BEGIN
2076 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2077 PERFORM NULL FROM "issue"
2078 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2079 WHERE "initiative"."id" = OLD."initiative_id"
2080 FOR SHARE OF "issue";
2081 END IF;
2082 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2083 PERFORM NULL FROM "issue"
2084 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2085 WHERE "initiative"."id" = NEW."initiative_id"
2086 FOR SHARE OF "issue";
2087 RETURN NEW;
2088 ELSE
2089 RETURN OLD;
2090 END IF;
2091 END;
2092 $$;
2094 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';
2097 CREATE TRIGGER "share_row_lock_issue"
2098 BEFORE INSERT OR UPDATE OR DELETE ON "initiative"
2099 FOR EACH ROW EXECUTE PROCEDURE
2100 "share_row_lock_issue_trigger"();
2102 CREATE TRIGGER "share_row_lock_issue"
2103 BEFORE INSERT OR UPDATE OR DELETE ON "interest"
2104 FOR EACH ROW EXECUTE PROCEDURE
2105 "share_row_lock_issue_trigger"();
2107 CREATE TRIGGER "share_row_lock_issue"
2108 BEFORE INSERT OR UPDATE OR DELETE ON "supporter"
2109 FOR EACH ROW EXECUTE PROCEDURE
2110 "share_row_lock_issue_trigger"();
2112 CREATE TRIGGER "share_row_lock_issue_via_initiative"
2113 BEFORE INSERT OR UPDATE OR DELETE ON "opinion"
2114 FOR EACH ROW EXECUTE PROCEDURE
2115 "share_row_lock_issue_via_initiative_trigger"();
2117 CREATE TRIGGER "share_row_lock_issue"
2118 BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter"
2119 FOR EACH ROW EXECUTE PROCEDURE
2120 "share_row_lock_issue_trigger"();
2122 CREATE TRIGGER "share_row_lock_issue"
2123 BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter"
2124 FOR EACH ROW EXECUTE PROCEDURE
2125 "share_row_lock_issue_trigger"();
2127 CREATE TRIGGER "share_row_lock_issue"
2128 BEFORE INSERT OR UPDATE OR DELETE ON "vote"
2129 FOR EACH ROW EXECUTE PROCEDURE
2130 "share_row_lock_issue_trigger"();
2132 COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function';
2133 COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function';
2134 COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function';
2135 COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function';
2136 COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function';
2137 COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function';
2138 COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function';
2141 CREATE FUNCTION "lock_issue"
2142 ( "issue_id_p" "issue"."id"%TYPE )
2143 RETURNS VOID
2144 LANGUAGE 'plpgsql' VOLATILE AS $$
2145 BEGIN
2146 LOCK TABLE "member" IN SHARE MODE;
2147 LOCK TABLE "privilege" IN SHARE MODE;
2148 LOCK TABLE "membership" IN SHARE MODE;
2149 LOCK TABLE "policy" IN SHARE MODE;
2150 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2151 -- NOTE: The row-level exclusive lock in combination with the
2152 -- share_row_lock_issue(_via_initiative)_trigger functions (which
2153 -- acquire a row-level share lock on the issue) ensure that no data
2154 -- is changed, which could affect calculation of snapshots or
2155 -- counting of votes. Table "delegation" must be table-level-locked,
2156 -- as it also contains issue- and global-scope delegations.
2157 LOCK TABLE "delegation" IN SHARE MODE;
2158 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
2159 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
2160 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
2161 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
2162 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
2163 RETURN;
2164 END;
2165 $$;
2167 COMMENT ON FUNCTION "lock_issue"
2168 ( "issue"."id"%TYPE )
2169 IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';
2173 -------------------------------
2174 -- Materialize member counts --
2175 -------------------------------
2177 CREATE FUNCTION "calculate_member_counts"()
2178 RETURNS VOID
2179 LANGUAGE 'plpgsql' VOLATILE AS $$
2180 BEGIN
2181 LOCK TABLE "member" IN SHARE MODE;
2182 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
2183 LOCK TABLE "unit" IN EXCLUSIVE MODE;
2184 LOCK TABLE "area" IN EXCLUSIVE MODE;
2185 LOCK TABLE "privilege" IN SHARE MODE;
2186 LOCK TABLE "membership" IN SHARE MODE;
2187 DELETE FROM "member_count";
2188 INSERT INTO "member_count" ("total_count")
2189 SELECT "total_count" FROM "member_count_view";
2190 UPDATE "unit" SET "member_count" = "view"."member_count"
2191 FROM "unit_member_count" AS "view"
2192 WHERE "view"."unit_id" = "unit"."id";
2193 UPDATE "area" SET
2194 "direct_member_count" = "view"."direct_member_count",
2195 "member_weight" = "view"."member_weight",
2196 "autoreject_weight" = "view"."autoreject_weight"
2197 FROM "area_member_count" AS "view"
2198 WHERE "view"."area_id" = "area"."id";
2199 RETURN;
2200 END;
2201 $$;
2203 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"';
2207 ------------------------------
2208 -- Calculation of snapshots --
2209 ------------------------------
2211 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
2212 ( "issue_id_p" "issue"."id"%TYPE,
2213 "member_id_p" "member"."id"%TYPE,
2214 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2215 RETURNS "direct_population_snapshot"."weight"%TYPE
2216 LANGUAGE 'plpgsql' VOLATILE AS $$
2217 DECLARE
2218 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2219 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
2220 "weight_v" INT4;
2221 "sub_weight_v" INT4;
2222 BEGIN
2223 "weight_v" := 0;
2224 FOR "issue_delegation_row" IN
2225 SELECT * FROM "issue_delegation"
2226 WHERE "trustee_id" = "member_id_p"
2227 AND "issue_id" = "issue_id_p"
2228 LOOP
2229 IF NOT EXISTS (
2230 SELECT NULL FROM "direct_population_snapshot"
2231 WHERE "issue_id" = "issue_id_p"
2232 AND "event" = 'periodic'
2233 AND "member_id" = "issue_delegation_row"."truster_id"
2234 ) AND NOT EXISTS (
2235 SELECT NULL FROM "delegating_population_snapshot"
2236 WHERE "issue_id" = "issue_id_p"
2237 AND "event" = 'periodic'
2238 AND "member_id" = "issue_delegation_row"."truster_id"
2239 ) THEN
2240 "delegate_member_ids_v" :=
2241 "member_id_p" || "delegate_member_ids_p";
2242 INSERT INTO "delegating_population_snapshot" (
2243 "issue_id",
2244 "event",
2245 "member_id",
2246 "scope",
2247 "delegate_member_ids"
2248 ) VALUES (
2249 "issue_id_p",
2250 'periodic',
2251 "issue_delegation_row"."truster_id",
2252 "issue_delegation_row"."scope",
2253 "delegate_member_ids_v"
2254 );
2255 "sub_weight_v" := 1 +
2256 "weight_of_added_delegations_for_population_snapshot"(
2257 "issue_id_p",
2258 "issue_delegation_row"."truster_id",
2259 "delegate_member_ids_v"
2260 );
2261 UPDATE "delegating_population_snapshot"
2262 SET "weight" = "sub_weight_v"
2263 WHERE "issue_id" = "issue_id_p"
2264 AND "event" = 'periodic'
2265 AND "member_id" = "issue_delegation_row"."truster_id";
2266 "weight_v" := "weight_v" + "sub_weight_v";
2267 END IF;
2268 END LOOP;
2269 RETURN "weight_v";
2270 END;
2271 $$;
2273 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
2274 ( "issue"."id"%TYPE,
2275 "member"."id"%TYPE,
2276 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2277 IS 'Helper function for "create_population_snapshot" function';
2280 CREATE FUNCTION "create_population_snapshot"
2281 ( "issue_id_p" "issue"."id"%TYPE )
2282 RETURNS VOID
2283 LANGUAGE 'plpgsql' VOLATILE AS $$
2284 DECLARE
2285 "member_id_v" "member"."id"%TYPE;
2286 BEGIN
2287 DELETE FROM "direct_population_snapshot"
2288 WHERE "issue_id" = "issue_id_p"
2289 AND "event" = 'periodic';
2290 DELETE FROM "delegating_population_snapshot"
2291 WHERE "issue_id" = "issue_id_p"
2292 AND "event" = 'periodic';
2293 INSERT INTO "direct_population_snapshot"
2294 ("issue_id", "event", "member_id")
2295 SELECT
2296 "issue_id_p" AS "issue_id",
2297 'periodic'::"snapshot_event" AS "event",
2298 "member"."id" AS "member_id"
2299 FROM "issue"
2300 JOIN "area" ON "issue"."area_id" = "area"."id"
2301 JOIN "membership" ON "area"."id" = "membership"."area_id"
2302 JOIN "member" ON "membership"."member_id" = "member"."id"
2303 JOIN "privilege"
2304 ON "privilege"."unit_id" = "area"."unit_id"
2305 AND "privilege"."member_id" = "member"."id"
2306 WHERE "issue"."id" = "issue_id_p"
2307 AND "member"."active" AND "privilege"."voting_right"
2308 UNION
2309 SELECT
2310 "issue_id_p" AS "issue_id",
2311 'periodic'::"snapshot_event" AS "event",
2312 "member"."id" AS "member_id"
2313 FROM "issue"
2314 JOIN "area" ON "issue"."area_id" = "area"."id"
2315 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2316 JOIN "member" ON "interest"."member_id" = "member"."id"
2317 JOIN "privilege"
2318 ON "privilege"."unit_id" = "area"."unit_id"
2319 AND "privilege"."member_id" = "member"."id"
2320 WHERE "issue"."id" = "issue_id_p"
2321 AND "member"."active" AND "privilege"."voting_right";
2322 FOR "member_id_v" IN
2323 SELECT "member_id" FROM "direct_population_snapshot"
2324 WHERE "issue_id" = "issue_id_p"
2325 AND "event" = 'periodic'
2326 LOOP
2327 UPDATE "direct_population_snapshot" SET
2328 "weight" = 1 +
2329 "weight_of_added_delegations_for_population_snapshot"(
2330 "issue_id_p",
2331 "member_id_v",
2332 '{}'
2333 )
2334 WHERE "issue_id" = "issue_id_p"
2335 AND "event" = 'periodic'
2336 AND "member_id" = "member_id_v";
2337 END LOOP;
2338 RETURN;
2339 END;
2340 $$;
2342 COMMENT ON FUNCTION "create_population_snapshot"
2343 ( "issue"."id"%TYPE )
2344 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.';
2347 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2348 ( "issue_id_p" "issue"."id"%TYPE,
2349 "member_id_p" "member"."id"%TYPE,
2350 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2351 RETURNS "direct_interest_snapshot"."weight"%TYPE
2352 LANGUAGE 'plpgsql' VOLATILE AS $$
2353 DECLARE
2354 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2355 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2356 "weight_v" INT4;
2357 "sub_weight_v" INT4;
2358 BEGIN
2359 "weight_v" := 0;
2360 FOR "issue_delegation_row" IN
2361 SELECT * FROM "issue_delegation"
2362 WHERE "trustee_id" = "member_id_p"
2363 AND "issue_id" = "issue_id_p"
2364 LOOP
2365 IF NOT EXISTS (
2366 SELECT NULL FROM "direct_interest_snapshot"
2367 WHERE "issue_id" = "issue_id_p"
2368 AND "event" = 'periodic'
2369 AND "member_id" = "issue_delegation_row"."truster_id"
2370 ) AND NOT EXISTS (
2371 SELECT NULL FROM "delegating_interest_snapshot"
2372 WHERE "issue_id" = "issue_id_p"
2373 AND "event" = 'periodic'
2374 AND "member_id" = "issue_delegation_row"."truster_id"
2375 ) THEN
2376 "delegate_member_ids_v" :=
2377 "member_id_p" || "delegate_member_ids_p";
2378 INSERT INTO "delegating_interest_snapshot" (
2379 "issue_id",
2380 "event",
2381 "member_id",
2382 "scope",
2383 "delegate_member_ids"
2384 ) VALUES (
2385 "issue_id_p",
2386 'periodic',
2387 "issue_delegation_row"."truster_id",
2388 "issue_delegation_row"."scope",
2389 "delegate_member_ids_v"
2390 );
2391 "sub_weight_v" := 1 +
2392 "weight_of_added_delegations_for_interest_snapshot"(
2393 "issue_id_p",
2394 "issue_delegation_row"."truster_id",
2395 "delegate_member_ids_v"
2396 );
2397 UPDATE "delegating_interest_snapshot"
2398 SET "weight" = "sub_weight_v"
2399 WHERE "issue_id" = "issue_id_p"
2400 AND "event" = 'periodic'
2401 AND "member_id" = "issue_delegation_row"."truster_id";
2402 "weight_v" := "weight_v" + "sub_weight_v";
2403 END IF;
2404 END LOOP;
2405 RETURN "weight_v";
2406 END;
2407 $$;
2409 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2410 ( "issue"."id"%TYPE,
2411 "member"."id"%TYPE,
2412 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2413 IS 'Helper function for "create_interest_snapshot" function';
2416 CREATE FUNCTION "create_interest_snapshot"
2417 ( "issue_id_p" "issue"."id"%TYPE )
2418 RETURNS VOID
2419 LANGUAGE 'plpgsql' VOLATILE AS $$
2420 DECLARE
2421 "member_id_v" "member"."id"%TYPE;
2422 BEGIN
2423 DELETE FROM "direct_interest_snapshot"
2424 WHERE "issue_id" = "issue_id_p"
2425 AND "event" = 'periodic';
2426 DELETE FROM "delegating_interest_snapshot"
2427 WHERE "issue_id" = "issue_id_p"
2428 AND "event" = 'periodic';
2429 DELETE FROM "direct_supporter_snapshot"
2430 WHERE "issue_id" = "issue_id_p"
2431 AND "event" = 'periodic';
2432 INSERT INTO "direct_interest_snapshot"
2433 ("issue_id", "event", "member_id", "voting_requested")
2434 SELECT
2435 "issue_id_p" AS "issue_id",
2436 'periodic' AS "event",
2437 "member"."id" AS "member_id",
2438 "interest"."voting_requested"
2439 FROM "issue"
2440 JOIN "area" ON "issue"."area_id" = "area"."id"
2441 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2442 JOIN "member" ON "interest"."member_id" = "member"."id"
2443 JOIN "privilege"
2444 ON "privilege"."unit_id" = "area"."unit_id"
2445 AND "privilege"."member_id" = "member"."id"
2446 WHERE "issue"."id" = "issue_id_p"
2447 AND "member"."active" AND "privilege"."voting_right";
2448 FOR "member_id_v" IN
2449 SELECT "member_id" FROM "direct_interest_snapshot"
2450 WHERE "issue_id" = "issue_id_p"
2451 AND "event" = 'periodic'
2452 LOOP
2453 UPDATE "direct_interest_snapshot" SET
2454 "weight" = 1 +
2455 "weight_of_added_delegations_for_interest_snapshot"(
2456 "issue_id_p",
2457 "member_id_v",
2458 '{}'
2459 )
2460 WHERE "issue_id" = "issue_id_p"
2461 AND "event" = 'periodic'
2462 AND "member_id" = "member_id_v";
2463 END LOOP;
2464 INSERT INTO "direct_supporter_snapshot"
2465 ( "issue_id", "initiative_id", "event", "member_id",
2466 "informed", "satisfied" )
2467 SELECT
2468 "issue_id_p" AS "issue_id",
2469 "initiative"."id" AS "initiative_id",
2470 'periodic' AS "event",
2471 "supporter"."member_id" AS "member_id",
2472 "supporter"."draft_id" = "current_draft"."id" AS "informed",
2473 NOT EXISTS (
2474 SELECT NULL FROM "critical_opinion"
2475 WHERE "initiative_id" = "initiative"."id"
2476 AND "member_id" = "supporter"."member_id"
2477 ) AS "satisfied"
2478 FROM "initiative"
2479 JOIN "supporter"
2480 ON "supporter"."initiative_id" = "initiative"."id"
2481 JOIN "current_draft"
2482 ON "initiative"."id" = "current_draft"."initiative_id"
2483 JOIN "direct_interest_snapshot"
2484 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
2485 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
2486 AND "event" = 'periodic'
2487 WHERE "initiative"."issue_id" = "issue_id_p";
2488 RETURN;
2489 END;
2490 $$;
2492 COMMENT ON FUNCTION "create_interest_snapshot"
2493 ( "issue"."id"%TYPE )
2494 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.';
2497 CREATE FUNCTION "create_snapshot"
2498 ( "issue_id_p" "issue"."id"%TYPE )
2499 RETURNS VOID
2500 LANGUAGE 'plpgsql' VOLATILE AS $$
2501 DECLARE
2502 "initiative_id_v" "initiative"."id"%TYPE;
2503 "suggestion_id_v" "suggestion"."id"%TYPE;
2504 BEGIN
2505 PERFORM "lock_issue"("issue_id_p");
2506 PERFORM "create_population_snapshot"("issue_id_p");
2507 PERFORM "create_interest_snapshot"("issue_id_p");
2508 UPDATE "issue" SET
2509 "snapshot" = now(),
2510 "latest_snapshot_event" = 'periodic',
2511 "population" = (
2512 SELECT coalesce(sum("weight"), 0)
2513 FROM "direct_population_snapshot"
2514 WHERE "issue_id" = "issue_id_p"
2515 AND "event" = 'periodic'
2516 ),
2517 "vote_now" = (
2518 SELECT coalesce(sum("weight"), 0)
2519 FROM "direct_interest_snapshot"
2520 WHERE "issue_id" = "issue_id_p"
2521 AND "event" = 'periodic'
2522 AND "voting_requested" = TRUE
2523 ),
2524 "vote_later" = (
2525 SELECT coalesce(sum("weight"), 0)
2526 FROM "direct_interest_snapshot"
2527 WHERE "issue_id" = "issue_id_p"
2528 AND "event" = 'periodic'
2529 AND "voting_requested" = FALSE
2530 )
2531 WHERE "id" = "issue_id_p";
2532 FOR "initiative_id_v" IN
2533 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
2534 LOOP
2535 UPDATE "initiative" SET
2536 "supporter_count" = (
2537 SELECT coalesce(sum("di"."weight"), 0)
2538 FROM "direct_interest_snapshot" AS "di"
2539 JOIN "direct_supporter_snapshot" AS "ds"
2540 ON "di"."member_id" = "ds"."member_id"
2541 WHERE "di"."issue_id" = "issue_id_p"
2542 AND "di"."event" = 'periodic'
2543 AND "ds"."initiative_id" = "initiative_id_v"
2544 AND "ds"."event" = 'periodic'
2545 ),
2546 "informed_supporter_count" = (
2547 SELECT coalesce(sum("di"."weight"), 0)
2548 FROM "direct_interest_snapshot" AS "di"
2549 JOIN "direct_supporter_snapshot" AS "ds"
2550 ON "di"."member_id" = "ds"."member_id"
2551 WHERE "di"."issue_id" = "issue_id_p"
2552 AND "di"."event" = 'periodic'
2553 AND "ds"."initiative_id" = "initiative_id_v"
2554 AND "ds"."event" = 'periodic'
2555 AND "ds"."informed"
2556 ),
2557 "satisfied_supporter_count" = (
2558 SELECT coalesce(sum("di"."weight"), 0)
2559 FROM "direct_interest_snapshot" AS "di"
2560 JOIN "direct_supporter_snapshot" AS "ds"
2561 ON "di"."member_id" = "ds"."member_id"
2562 WHERE "di"."issue_id" = "issue_id_p"
2563 AND "di"."event" = 'periodic'
2564 AND "ds"."initiative_id" = "initiative_id_v"
2565 AND "ds"."event" = 'periodic'
2566 AND "ds"."satisfied"
2567 ),
2568 "satisfied_informed_supporter_count" = (
2569 SELECT coalesce(sum("di"."weight"), 0)
2570 FROM "direct_interest_snapshot" AS "di"
2571 JOIN "direct_supporter_snapshot" AS "ds"
2572 ON "di"."member_id" = "ds"."member_id"
2573 WHERE "di"."issue_id" = "issue_id_p"
2574 AND "di"."event" = 'periodic'
2575 AND "ds"."initiative_id" = "initiative_id_v"
2576 AND "ds"."event" = 'periodic'
2577 AND "ds"."informed"
2578 AND "ds"."satisfied"
2579 )
2580 WHERE "id" = "initiative_id_v";
2581 FOR "suggestion_id_v" IN
2582 SELECT "id" FROM "suggestion"
2583 WHERE "initiative_id" = "initiative_id_v"
2584 LOOP
2585 UPDATE "suggestion" SET
2586 "minus2_unfulfilled_count" = (
2587 SELECT coalesce(sum("snapshot"."weight"), 0)
2588 FROM "issue" CROSS JOIN "opinion"
2589 JOIN "direct_interest_snapshot" AS "snapshot"
2590 ON "snapshot"."issue_id" = "issue"."id"
2591 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2592 AND "snapshot"."member_id" = "opinion"."member_id"
2593 WHERE "issue"."id" = "issue_id_p"
2594 AND "opinion"."suggestion_id" = "suggestion_id_v"
2595 AND "opinion"."degree" = -2
2596 AND "opinion"."fulfilled" = FALSE
2597 ),
2598 "minus2_fulfilled_count" = (
2599 SELECT coalesce(sum("snapshot"."weight"), 0)
2600 FROM "issue" CROSS JOIN "opinion"
2601 JOIN "direct_interest_snapshot" AS "snapshot"
2602 ON "snapshot"."issue_id" = "issue"."id"
2603 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2604 AND "snapshot"."member_id" = "opinion"."member_id"
2605 WHERE "issue"."id" = "issue_id_p"
2606 AND "opinion"."suggestion_id" = "suggestion_id_v"
2607 AND "opinion"."degree" = -2
2608 AND "opinion"."fulfilled" = TRUE
2609 ),
2610 "minus1_unfulfilled_count" = (
2611 SELECT coalesce(sum("snapshot"."weight"), 0)
2612 FROM "issue" CROSS JOIN "opinion"
2613 JOIN "direct_interest_snapshot" AS "snapshot"
2614 ON "snapshot"."issue_id" = "issue"."id"
2615 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2616 AND "snapshot"."member_id" = "opinion"."member_id"
2617 WHERE "issue"."id" = "issue_id_p"
2618 AND "opinion"."suggestion_id" = "suggestion_id_v"
2619 AND "opinion"."degree" = -1
2620 AND "opinion"."fulfilled" = FALSE
2621 ),
2622 "minus1_fulfilled_count" = (
2623 SELECT coalesce(sum("snapshot"."weight"), 0)
2624 FROM "issue" CROSS JOIN "opinion"
2625 JOIN "direct_interest_snapshot" AS "snapshot"
2626 ON "snapshot"."issue_id" = "issue"."id"
2627 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2628 AND "snapshot"."member_id" = "opinion"."member_id"
2629 WHERE "issue"."id" = "issue_id_p"
2630 AND "opinion"."suggestion_id" = "suggestion_id_v"
2631 AND "opinion"."degree" = -1
2632 AND "opinion"."fulfilled" = TRUE
2633 ),
2634 "plus1_unfulfilled_count" = (
2635 SELECT coalesce(sum("snapshot"."weight"), 0)
2636 FROM "issue" CROSS JOIN "opinion"
2637 JOIN "direct_interest_snapshot" AS "snapshot"
2638 ON "snapshot"."issue_id" = "issue"."id"
2639 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2640 AND "snapshot"."member_id" = "opinion"."member_id"
2641 WHERE "issue"."id" = "issue_id_p"
2642 AND "opinion"."suggestion_id" = "suggestion_id_v"
2643 AND "opinion"."degree" = 1
2644 AND "opinion"."fulfilled" = FALSE
2645 ),
2646 "plus1_fulfilled_count" = (
2647 SELECT coalesce(sum("snapshot"."weight"), 0)
2648 FROM "issue" CROSS JOIN "opinion"
2649 JOIN "direct_interest_snapshot" AS "snapshot"
2650 ON "snapshot"."issue_id" = "issue"."id"
2651 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2652 AND "snapshot"."member_id" = "opinion"."member_id"
2653 WHERE "issue"."id" = "issue_id_p"
2654 AND "opinion"."suggestion_id" = "suggestion_id_v"
2655 AND "opinion"."degree" = 1
2656 AND "opinion"."fulfilled" = TRUE
2657 ),
2658 "plus2_unfulfilled_count" = (
2659 SELECT coalesce(sum("snapshot"."weight"), 0)
2660 FROM "issue" CROSS JOIN "opinion"
2661 JOIN "direct_interest_snapshot" AS "snapshot"
2662 ON "snapshot"."issue_id" = "issue"."id"
2663 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2664 AND "snapshot"."member_id" = "opinion"."member_id"
2665 WHERE "issue"."id" = "issue_id_p"
2666 AND "opinion"."suggestion_id" = "suggestion_id_v"
2667 AND "opinion"."degree" = 2
2668 AND "opinion"."fulfilled" = FALSE
2669 ),
2670 "plus2_fulfilled_count" = (
2671 SELECT coalesce(sum("snapshot"."weight"), 0)
2672 FROM "issue" CROSS JOIN "opinion"
2673 JOIN "direct_interest_snapshot" AS "snapshot"
2674 ON "snapshot"."issue_id" = "issue"."id"
2675 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2676 AND "snapshot"."member_id" = "opinion"."member_id"
2677 WHERE "issue"."id" = "issue_id_p"
2678 AND "opinion"."suggestion_id" = "suggestion_id_v"
2679 AND "opinion"."degree" = 2
2680 AND "opinion"."fulfilled" = TRUE
2681 )
2682 WHERE "suggestion"."id" = "suggestion_id_v";
2683 END LOOP;
2684 END LOOP;
2685 RETURN;
2686 END;
2687 $$;
2689 COMMENT ON FUNCTION "create_snapshot"
2690 ( "issue"."id"%TYPE )
2691 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.';
2694 CREATE FUNCTION "set_snapshot_event"
2695 ( "issue_id_p" "issue"."id"%TYPE,
2696 "event_p" "snapshot_event" )
2697 RETURNS VOID
2698 LANGUAGE 'plpgsql' VOLATILE AS $$
2699 DECLARE
2700 "event_v" "issue"."latest_snapshot_event"%TYPE;
2701 BEGIN
2702 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
2703 WHERE "id" = "issue_id_p" FOR UPDATE;
2704 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
2705 WHERE "id" = "issue_id_p";
2706 UPDATE "direct_population_snapshot" SET "event" = "event_p"
2707 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2708 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
2709 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2710 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
2711 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2712 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
2713 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2714 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
2715 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2716 RETURN;
2717 END;
2718 $$;
2720 COMMENT ON FUNCTION "set_snapshot_event"
2721 ( "issue"."id"%TYPE,
2722 "snapshot_event" )
2723 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
2727 ---------------------
2728 -- Freezing issues --
2729 ---------------------
2731 CREATE FUNCTION "freeze_after_snapshot"
2732 ( "issue_id_p" "issue"."id"%TYPE )
2733 RETURNS VOID
2734 LANGUAGE 'plpgsql' VOLATILE AS $$
2735 DECLARE
2736 "issue_row" "issue"%ROWTYPE;
2737 "policy_row" "policy"%ROWTYPE;
2738 "initiative_row" "initiative"%ROWTYPE;
2739 BEGIN
2740 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2741 SELECT * INTO "policy_row"
2742 FROM "policy" WHERE "id" = "issue_row"."policy_id";
2743 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
2744 UPDATE "issue" SET
2745 "accepted" = coalesce("accepted", now()),
2746 "half_frozen" = coalesce("half_frozen", now()),
2747 "fully_frozen" = now()
2748 WHERE "id" = "issue_id_p";
2749 FOR "initiative_row" IN
2750 SELECT * FROM "initiative"
2751 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
2752 LOOP
2753 IF
2754 "initiative_row"."satisfied_supporter_count" > 0 AND
2755 "initiative_row"."satisfied_supporter_count" *
2756 "policy_row"."initiative_quorum_den" >=
2757 "issue_row"."population" * "policy_row"."initiative_quorum_num"
2758 THEN
2759 UPDATE "initiative" SET "admitted" = TRUE
2760 WHERE "id" = "initiative_row"."id";
2761 ELSE
2762 UPDATE "initiative" SET "admitted" = FALSE
2763 WHERE "id" = "initiative_row"."id";
2764 END IF;
2765 END LOOP;
2766 IF NOT EXISTS (
2767 SELECT NULL FROM "initiative"
2768 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
2769 ) THEN
2770 PERFORM "close_voting"("issue_id_p");
2771 END IF;
2772 RETURN;
2773 END;
2774 $$;
2776 COMMENT ON FUNCTION "freeze_after_snapshot"
2777 ( "issue"."id"%TYPE )
2778 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
2781 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
2782 RETURNS VOID
2783 LANGUAGE 'plpgsql' VOLATILE AS $$
2784 DECLARE
2785 "issue_row" "issue"%ROWTYPE;
2786 BEGIN
2787 PERFORM "create_snapshot"("issue_id_p");
2788 PERFORM "freeze_after_snapshot"("issue_id_p");
2789 RETURN;
2790 END;
2791 $$;
2793 COMMENT ON FUNCTION "manual_freeze"
2794 ( "issue"."id"%TYPE )
2795 IS 'Freeze an issue manually (fully) and start voting';
2799 -----------------------
2800 -- Counting of votes --
2801 -----------------------
2804 CREATE FUNCTION "weight_of_added_vote_delegations"
2805 ( "issue_id_p" "issue"."id"%TYPE,
2806 "member_id_p" "member"."id"%TYPE,
2807 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
2808 RETURNS "direct_voter"."weight"%TYPE
2809 LANGUAGE 'plpgsql' VOLATILE AS $$
2810 DECLARE
2811 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2812 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
2813 "weight_v" INT4;
2814 "sub_weight_v" INT4;
2815 BEGIN
2816 "weight_v" := 0;
2817 FOR "issue_delegation_row" IN
2818 SELECT * FROM "issue_delegation"
2819 WHERE "trustee_id" = "member_id_p"
2820 AND "issue_id" = "issue_id_p"
2821 LOOP
2822 IF NOT EXISTS (
2823 SELECT NULL FROM "direct_voter"
2824 WHERE "member_id" = "issue_delegation_row"."truster_id"
2825 AND "issue_id" = "issue_id_p"
2826 ) AND NOT EXISTS (
2827 SELECT NULL FROM "delegating_voter"
2828 WHERE "member_id" = "issue_delegation_row"."truster_id"
2829 AND "issue_id" = "issue_id_p"
2830 ) THEN
2831 "delegate_member_ids_v" :=
2832 "member_id_p" || "delegate_member_ids_p";
2833 INSERT INTO "delegating_voter" (
2834 "issue_id",
2835 "member_id",
2836 "scope",
2837 "delegate_member_ids"
2838 ) VALUES (
2839 "issue_id_p",
2840 "issue_delegation_row"."truster_id",
2841 "issue_delegation_row"."scope",
2842 "delegate_member_ids_v"
2843 );
2844 "sub_weight_v" := 1 +
2845 "weight_of_added_vote_delegations"(
2846 "issue_id_p",
2847 "issue_delegation_row"."truster_id",
2848 "delegate_member_ids_v"
2849 );
2850 UPDATE "delegating_voter"
2851 SET "weight" = "sub_weight_v"
2852 WHERE "issue_id" = "issue_id_p"
2853 AND "member_id" = "issue_delegation_row"."truster_id";
2854 "weight_v" := "weight_v" + "sub_weight_v";
2855 END IF;
2856 END LOOP;
2857 RETURN "weight_v";
2858 END;
2859 $$;
2861 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
2862 ( "issue"."id"%TYPE,
2863 "member"."id"%TYPE,
2864 "delegating_voter"."delegate_member_ids"%TYPE )
2865 IS 'Helper function for "add_vote_delegations" function';
2868 CREATE FUNCTION "add_vote_delegations"
2869 ( "issue_id_p" "issue"."id"%TYPE )
2870 RETURNS VOID
2871 LANGUAGE 'plpgsql' VOLATILE AS $$
2872 DECLARE
2873 "member_id_v" "member"."id"%TYPE;
2874 BEGIN
2875 FOR "member_id_v" IN
2876 SELECT "member_id" FROM "direct_voter"
2877 WHERE "issue_id" = "issue_id_p"
2878 LOOP
2879 UPDATE "direct_voter" SET
2880 "weight" = "weight" + "weight_of_added_vote_delegations"(
2881 "issue_id_p",
2882 "member_id_v",
2883 '{}'
2884 )
2885 WHERE "member_id" = "member_id_v"
2886 AND "issue_id" = "issue_id_p";
2887 END LOOP;
2888 RETURN;
2889 END;
2890 $$;
2892 COMMENT ON FUNCTION "add_vote_delegations"
2893 ( "issue_id_p" "issue"."id"%TYPE )
2894 IS 'Helper function for "close_voting" function';
2897 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
2898 RETURNS VOID
2899 LANGUAGE 'plpgsql' VOLATILE AS $$
2900 DECLARE
2901 "area_id_v" "area"."id"%TYPE;
2902 "unit_id_v" "unit"."id"%TYPE;
2903 "member_id_v" "member"."id"%TYPE;
2904 BEGIN
2905 PERFORM "lock_issue"("issue_id_p");
2906 SELECT "id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
2907 SELECT "id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
2908 DELETE FROM "delegating_voter"
2909 WHERE "issue_id" = "issue_id_p";
2910 DELETE FROM "direct_voter"
2911 WHERE "issue_id" = "issue_id_p"
2912 AND "autoreject" = TRUE;
2913 DELETE FROM "direct_voter"
2914 USING (
2915 SELECT
2916 "direct_voter"."member_id"
2917 FROM "direct_voter"
2918 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
2919 LEFT JOIN "privilege"
2920 ON "privilege"."unit_id" = "unit_id_v"
2921 AND "privilege"."member_id" = "direct_voter"."member_id"
2922 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
2923 "member"."active" = FALSE OR
2924 "privilege"."voting_right" ISNULL OR
2925 "privilege"."voting_right" = FALSE
2926 )
2927 ) AS "subquery"
2928 WHERE "direct_voter"."issue_id" = "issue_id_p"
2929 AND "direct_voter"."member_id" = "subquery"."member_id";
2930 UPDATE "direct_voter" SET "weight" = 1
2931 WHERE "issue_id" = "issue_id_p";
2932 PERFORM "add_vote_delegations"("issue_id_p");
2933 FOR "member_id_v" IN
2934 SELECT "interest"."member_id"
2935 FROM "interest"
2936 JOIN "member"
2937 ON "interest"."member_id" = "member"."id"
2938 LEFT JOIN "direct_voter"
2939 ON "interest"."member_id" = "direct_voter"."member_id"
2940 AND "interest"."issue_id" = "direct_voter"."issue_id"
2941 LEFT JOIN "delegating_voter"
2942 ON "interest"."member_id" = "delegating_voter"."member_id"
2943 AND "interest"."issue_id" = "delegating_voter"."issue_id"
2944 WHERE "interest"."issue_id" = "issue_id_p"
2945 AND "interest"."autoreject" = TRUE
2946 AND "member"."active"
2947 AND "direct_voter"."member_id" ISNULL
2948 AND "delegating_voter"."member_id" ISNULL
2949 UNION SELECT "membership"."member_id"
2950 FROM "membership"
2951 JOIN "member"
2952 ON "membership"."member_id" = "member"."id"
2953 LEFT JOIN "interest"
2954 ON "membership"."member_id" = "interest"."member_id"
2955 AND "interest"."issue_id" = "issue_id_p"
2956 LEFT JOIN "direct_voter"
2957 ON "membership"."member_id" = "direct_voter"."member_id"
2958 AND "direct_voter"."issue_id" = "issue_id_p"
2959 LEFT JOIN "delegating_voter"
2960 ON "membership"."member_id" = "delegating_voter"."member_id"
2961 AND "delegating_voter"."issue_id" = "issue_id_p"
2962 WHERE "membership"."area_id" = "area_id_v"
2963 AND "membership"."autoreject" = TRUE
2964 AND "member"."active"
2965 AND "interest"."autoreject" ISNULL
2966 AND "direct_voter"."member_id" ISNULL
2967 AND "delegating_voter"."member_id" ISNULL
2968 LOOP
2969 INSERT INTO "direct_voter"
2970 ("member_id", "issue_id", "weight", "autoreject") VALUES
2971 ("member_id_v", "issue_id_p", 1, TRUE);
2972 INSERT INTO "vote" (
2973 "member_id",
2974 "issue_id",
2975 "initiative_id",
2976 "grade"
2977 ) SELECT
2978 "member_id_v" AS "member_id",
2979 "issue_id_p" AS "issue_id",
2980 "id" AS "initiative_id",
2981 -1 AS "grade"
2982 FROM "initiative" WHERE "issue_id" = "issue_id_p";
2983 END LOOP;
2984 PERFORM "add_vote_delegations"("issue_id_p");
2985 UPDATE "issue" SET
2986 "closed" = now(),
2987 "voter_count" = (
2988 SELECT coalesce(sum("weight"), 0)
2989 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
2990 )
2991 WHERE "id" = "issue_id_p";
2992 UPDATE "initiative" SET
2993 "positive_votes" = "vote_counts"."positive_votes",
2994 "negative_votes" = "vote_counts"."negative_votes",
2995 "agreed" = CASE WHEN "majority_strict" THEN
2996 "vote_counts"."positive_votes" * "majority_den" >
2997 "majority_num" *
2998 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
2999 ELSE
3000 "vote_counts"."positive_votes" * "majority_den" >=
3001 "majority_num" *
3002 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
3003 END
3004 FROM
3005 ( SELECT
3006 "initiative"."id" AS "initiative_id",
3007 coalesce(
3008 sum(
3009 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
3010 ),
3011 0
3012 ) AS "positive_votes",
3013 coalesce(
3014 sum(
3015 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
3016 ),
3017 0
3018 ) AS "negative_votes"
3019 FROM "initiative"
3020 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
3021 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
3022 LEFT JOIN "direct_voter"
3023 ON "direct_voter"."issue_id" = "initiative"."issue_id"
3024 LEFT JOIN "vote"
3025 ON "vote"."initiative_id" = "initiative"."id"
3026 AND "vote"."member_id" = "direct_voter"."member_id"
3027 WHERE "initiative"."issue_id" = "issue_id_p"
3028 AND "initiative"."admitted" -- NOTE: NULL case is handled too
3029 GROUP BY "initiative"."id"
3030 ) AS "vote_counts",
3031 "issue",
3032 "policy"
3033 WHERE "vote_counts"."initiative_id" = "initiative"."id"
3034 AND "issue"."id" = "initiative"."issue_id"
3035 AND "policy"."id" = "issue"."policy_id";
3036 -- NOTE: "closed" column of issue must be set at this point
3037 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3038 INSERT INTO "battle" (
3039 "issue_id",
3040 "winning_initiative_id", "losing_initiative_id",
3041 "count"
3042 ) SELECT
3043 "issue_id",
3044 "winning_initiative_id", "losing_initiative_id",
3045 "count"
3046 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
3047 END;
3048 $$;
3050 COMMENT ON FUNCTION "close_voting"
3051 ( "issue"."id"%TYPE )
3052 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.';
3055 CREATE FUNCTION "defeat_strength"
3056 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
3057 RETURNS INT8
3058 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3059 BEGIN
3060 IF "positive_votes_p" > "negative_votes_p" THEN
3061 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
3062 ELSIF "positive_votes_p" = "negative_votes_p" THEN
3063 RETURN 0;
3064 ELSE
3065 RETURN -1;
3066 END IF;
3067 END;
3068 $$;
3070 COMMENT ON FUNCTION "defeat_strength"(INT4, INT4) IS 'Calculates defeat strength (INT8!) of a pairwise defeat primarily by the absolute number of votes for the winner and secondarily by the absolute number of votes for the loser';
3073 CREATE FUNCTION "array_init_string"("dim_p" INTEGER)
3074 RETURNS TEXT
3075 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3076 DECLARE
3077 "i" INTEGER;
3078 "ary_text_v" TEXT;
3079 BEGIN
3080 IF "dim_p" >= 1 THEN
3081 "ary_text_v" := '{NULL';
3082 "i" := "dim_p";
3083 LOOP
3084 "i" := "i" - 1;
3085 EXIT WHEN "i" = 0;
3086 "ary_text_v" := "ary_text_v" || ',NULL';
3087 END LOOP;
3088 "ary_text_v" := "ary_text_v" || '}';
3089 RETURN "ary_text_v";
3090 ELSE
3091 RAISE EXCEPTION 'Dimension needs to be at least 1.';
3092 END IF;
3093 END;
3094 $$;
3096 COMMENT ON FUNCTION "array_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
3099 CREATE FUNCTION "square_matrix_init_string"("dim_p" INTEGER)
3100 RETURNS TEXT
3101 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3102 DECLARE
3103 "i" INTEGER;
3104 "row_text_v" TEXT;
3105 "ary_text_v" TEXT;
3106 BEGIN
3107 IF "dim_p" >= 1 THEN
3108 "row_text_v" := '{NULL';
3109 "i" := "dim_p";
3110 LOOP
3111 "i" := "i" - 1;
3112 EXIT WHEN "i" = 0;
3113 "row_text_v" := "row_text_v" || ',NULL';
3114 END LOOP;
3115 "row_text_v" := "row_text_v" || '}';
3116 "ary_text_v" := '{' || "row_text_v";
3117 "i" := "dim_p";
3118 LOOP
3119 "i" := "i" - 1;
3120 EXIT WHEN "i" = 0;
3121 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
3122 END LOOP;
3123 "ary_text_v" := "ary_text_v" || '}';
3124 RETURN "ary_text_v";
3125 ELSE
3126 RAISE EXCEPTION 'Dimension needs to be at least 1.';
3127 END IF;
3128 END;
3129 $$;
3131 COMMENT ON FUNCTION "square_matrix_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
3134 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
3135 RETURNS VOID
3136 LANGUAGE 'plpgsql' VOLATILE AS $$
3137 DECLARE
3138 "dimension_v" INTEGER;
3139 "vote_matrix" INT4[][]; -- absolute votes
3140 "matrix" INT8[][]; -- defeat strength / best paths
3141 "i" INTEGER;
3142 "j" INTEGER;
3143 "k" INTEGER;
3144 "battle_row" "battle"%ROWTYPE;
3145 "rank_ary" INT4[];
3146 "rank_v" INT4;
3147 "done_v" INTEGER;
3148 "winners_ary" INTEGER[];
3149 "initiative_id_v" "initiative"."id"%TYPE;
3150 BEGIN
3151 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
3152 SELECT count(1) INTO "dimension_v" FROM "initiative"
3153 WHERE "issue_id" = "issue_id_p" AND "agreed";
3154 IF "dimension_v" = 1 THEN
3155 UPDATE "initiative" SET "rank" = 1
3156 WHERE "issue_id" = "issue_id_p" AND "agreed";
3157 ELSIF "dimension_v" > 1 THEN
3158 -- Create "vote_matrix" with absolute number of votes in pairwise
3159 -- comparison:
3160 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
3161 "i" := 1;
3162 "j" := 2;
3163 FOR "battle_row" IN
3164 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
3165 ORDER BY "winning_initiative_id", "losing_initiative_id"
3166 LOOP
3167 "vote_matrix"["i"]["j"] := "battle_row"."count";
3168 IF "j" = "dimension_v" THEN
3169 "i" := "i" + 1;
3170 "j" := 1;
3171 ELSE
3172 "j" := "j" + 1;
3173 IF "j" = "i" THEN
3174 "j" := "j" + 1;
3175 END IF;
3176 END IF;
3177 END LOOP;
3178 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
3179 RAISE EXCEPTION 'Wrong battle count (should not happen)';
3180 END IF;
3181 -- Store defeat strengths in "matrix" using "defeat_strength"
3182 -- function:
3183 "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
3184 "i" := 1;
3185 LOOP
3186 "j" := 1;
3187 LOOP
3188 IF "i" != "j" THEN
3189 "matrix"["i"]["j"] := "defeat_strength"(
3190 "vote_matrix"["i"]["j"],
3191 "vote_matrix"["j"]["i"]
3192 );
3193 END IF;
3194 EXIT WHEN "j" = "dimension_v";
3195 "j" := "j" + 1;
3196 END LOOP;
3197 EXIT WHEN "i" = "dimension_v";
3198 "i" := "i" + 1;
3199 END LOOP;
3200 -- Find best paths:
3201 "i" := 1;
3202 LOOP
3203 "j" := 1;
3204 LOOP
3205 IF "i" != "j" THEN
3206 "k" := 1;
3207 LOOP
3208 IF "i" != "k" AND "j" != "k" THEN
3209 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
3210 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
3211 "matrix"["j"]["k"] := "matrix"["j"]["i"];
3212 END IF;
3213 ELSE
3214 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
3215 "matrix"["j"]["k"] := "matrix"["i"]["k"];
3216 END IF;
3217 END IF;
3218 END IF;
3219 EXIT WHEN "k" = "dimension_v";
3220 "k" := "k" + 1;
3221 END LOOP;
3222 END IF;
3223 EXIT WHEN "j" = "dimension_v";
3224 "j" := "j" + 1;
3225 END LOOP;
3226 EXIT WHEN "i" = "dimension_v";
3227 "i" := "i" + 1;
3228 END LOOP;
3229 -- Determine order of winners:
3230 "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
3231 "rank_v" := 1;
3232 "done_v" := 0;
3233 LOOP
3234 "winners_ary" := '{}';
3235 "i" := 1;
3236 LOOP
3237 IF "rank_ary"["i"] ISNULL THEN
3238 "j" := 1;
3239 LOOP
3240 IF
3241 "i" != "j" AND
3242 "rank_ary"["j"] ISNULL AND
3243 "matrix"["j"]["i"] > "matrix"["i"]["j"]
3244 THEN
3245 -- someone else is better
3246 EXIT;
3247 END IF;
3248 IF "j" = "dimension_v" THEN
3249 -- noone is better
3250 "winners_ary" := "winners_ary" || "i";
3251 EXIT;
3252 END IF;
3253 "j" := "j" + 1;
3254 END LOOP;
3255 END IF;
3256 EXIT WHEN "i" = "dimension_v";
3257 "i" := "i" + 1;
3258 END LOOP;
3259 "i" := 1;
3260 LOOP
3261 "rank_ary"["winners_ary"["i"]] := "rank_v";
3262 "done_v" := "done_v" + 1;
3263 EXIT WHEN "i" = array_upper("winners_ary", 1);
3264 "i" := "i" + 1;
3265 END LOOP;
3266 EXIT WHEN "done_v" = "dimension_v";
3267 "rank_v" := "rank_v" + 1;
3268 END LOOP;
3269 -- write preliminary ranks:
3270 "i" := 1;
3271 FOR "initiative_id_v" IN
3272 SELECT "id" FROM "initiative"
3273 WHERE "issue_id" = "issue_id_p" AND "agreed"
3274 ORDER BY "id"
3275 LOOP
3276 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
3277 WHERE "id" = "initiative_id_v";
3278 "i" := "i" + 1;
3279 END LOOP;
3280 IF "i" != "dimension_v" + 1 THEN
3281 RAISE EXCEPTION 'Wrong winner count (should not happen)';
3282 END IF;
3283 -- straighten ranks (start counting with 1, no equal ranks):
3284 "rank_v" := 1;
3285 FOR "initiative_id_v" IN
3286 SELECT "id" FROM "initiative"
3287 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
3288 ORDER BY
3289 "rank",
3290 "vote_ratio"("positive_votes", "negative_votes") DESC,
3291 "id"
3292 LOOP
3293 UPDATE "initiative" SET "rank" = "rank_v"
3294 WHERE "id" = "initiative_id_v";
3295 "rank_v" := "rank_v" + 1;
3296 END LOOP;
3297 END IF;
3298 -- mark issue as finished
3299 UPDATE "issue" SET "ranks_available" = TRUE
3300 WHERE "id" = "issue_id_p";
3301 RETURN;
3302 END;
3303 $$;
3305 COMMENT ON FUNCTION "calculate_ranks"
3306 ( "issue"."id"%TYPE )
3307 IS 'Determine ranking (Votes have to be counted first)';
3311 -----------------------------
3312 -- Automatic state changes --
3313 -----------------------------
3316 CREATE FUNCTION "check_issue"
3317 ( "issue_id_p" "issue"."id"%TYPE )
3318 RETURNS VOID
3319 LANGUAGE 'plpgsql' VOLATILE AS $$
3320 DECLARE
3321 "issue_row" "issue"%ROWTYPE;
3322 "policy_row" "policy"%ROWTYPE;
3323 "voting_requested_v" BOOLEAN;
3324 BEGIN
3325 PERFORM "lock_issue"("issue_id_p");
3326 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3327 -- only process open issues:
3328 IF "issue_row"."closed" ISNULL THEN
3329 SELECT * INTO "policy_row" FROM "policy"
3330 WHERE "id" = "issue_row"."policy_id";
3331 -- create a snapshot, unless issue is already fully frozen:
3332 IF "issue_row"."fully_frozen" ISNULL THEN
3333 PERFORM "create_snapshot"("issue_id_p");
3334 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3335 END IF;
3336 -- eventually close or accept issues, which have not been accepted:
3337 IF "issue_row"."accepted" ISNULL THEN
3338 IF EXISTS (
3339 SELECT NULL FROM "initiative"
3340 WHERE "issue_id" = "issue_id_p"
3341 AND "supporter_count" > 0
3342 AND "supporter_count" * "policy_row"."issue_quorum_den"
3343 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
3344 ) THEN
3345 -- accept issues, if supporter count is high enough
3346 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3347 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later
3348 UPDATE "issue" SET "accepted" = "issue_row"."accepted"
3349 WHERE "id" = "issue_row"."id";
3350 ELSIF
3351 now() >= "issue_row"."created" + "issue_row"."admission_time"
3352 THEN
3353 -- close issues, if admission time has expired
3354 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3355 UPDATE "issue" SET "closed" = now()
3356 WHERE "id" = "issue_row"."id";
3357 END IF;
3358 END IF;
3359 -- eventually half freeze issues:
3360 IF
3361 -- NOTE: issue can't be closed at this point, if it has been accepted
3362 "issue_row"."accepted" NOTNULL AND
3363 "issue_row"."half_frozen" ISNULL
3364 THEN
3365 SELECT
3366 CASE
3367 WHEN "vote_now" * 2 > "issue_row"."population" THEN
3368 TRUE
3369 WHEN "vote_later" * 2 > "issue_row"."population" THEN
3370 FALSE
3371 ELSE NULL
3372 END
3373 INTO "voting_requested_v"
3374 FROM "issue" WHERE "id" = "issue_id_p";
3375 IF
3376 "voting_requested_v" OR (
3377 "voting_requested_v" ISNULL AND
3378 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
3379 )
3380 THEN
3381 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
3382 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later
3383 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
3384 WHERE "id" = "issue_row"."id";
3385 END IF;
3386 END IF;
3387 -- close issues after some time, if all initiatives have been revoked:
3388 IF
3389 "issue_row"."closed" ISNULL AND
3390 NOT EXISTS (
3391 -- all initiatives are revoked
3392 SELECT NULL FROM "initiative"
3393 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3394 ) AND (
3395 NOT EXISTS (
3396 -- and no initiatives have been revoked lately
3397 SELECT NULL FROM "initiative"
3398 WHERE "issue_id" = "issue_id_p"
3399 AND now() < "revoked" + "issue_row"."verification_time"
3400 ) OR (
3401 -- or verification time has elapsed
3402 "issue_row"."half_frozen" NOTNULL AND
3403 "issue_row"."fully_frozen" ISNULL AND
3404 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3405 )
3406 )
3407 THEN
3408 "issue_row"."closed" = now(); -- NOTE: "issue_row" used later
3409 UPDATE "issue" SET "closed" = "issue_row"."closed"
3410 WHERE "id" = "issue_row"."id";
3411 END IF;
3412 -- fully freeze issue after verification time:
3413 IF
3414 "issue_row"."half_frozen" NOTNULL AND
3415 "issue_row"."fully_frozen" ISNULL AND
3416 "issue_row"."closed" ISNULL AND
3417 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3418 THEN
3419 PERFORM "freeze_after_snapshot"("issue_id_p");
3420 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
3421 END IF;
3422 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3423 -- close issue by calling close_voting(...) after voting time:
3424 IF
3425 "issue_row"."closed" ISNULL AND
3426 "issue_row"."fully_frozen" NOTNULL AND
3427 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
3428 THEN
3429 PERFORM "close_voting"("issue_id_p");
3430 END IF;
3431 END IF;
3432 RETURN;
3433 END;
3434 $$;
3436 COMMENT ON FUNCTION "check_issue"
3437 ( "issue"."id"%TYPE )
3438 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.';
3441 CREATE FUNCTION "check_everything"()
3442 RETURNS VOID
3443 LANGUAGE 'plpgsql' VOLATILE AS $$
3444 DECLARE
3445 "issue_id_v" "issue"."id"%TYPE;
3446 BEGIN
3447 DELETE FROM "expired_session";
3448 PERFORM "calculate_member_counts"();
3449 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
3450 PERFORM "check_issue"("issue_id_v");
3451 END LOOP;
3452 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
3453 PERFORM "calculate_ranks"("issue_id_v");
3454 END LOOP;
3455 RETURN;
3456 END;
3457 $$;
3459 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.';
3463 ----------------------
3464 -- Deletion of data --
3465 ----------------------
3468 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
3469 RETURNS VOID
3470 LANGUAGE 'plpgsql' VOLATILE AS $$
3471 DECLARE
3472 "issue_row" "issue"%ROWTYPE;
3473 BEGIN
3474 SELECT * INTO "issue_row"
3475 FROM "issue" WHERE "id" = "issue_id_p"
3476 FOR UPDATE;
3477 IF "issue_row"."cleaned" ISNULL THEN
3478 UPDATE "issue" SET
3479 "closed" = NULL,
3480 "ranks_available" = FALSE
3481 WHERE "id" = "issue_id_p";
3482 DELETE FROM "delegating_voter"
3483 WHERE "issue_id" = "issue_id_p";
3484 DELETE FROM "direct_voter"
3485 WHERE "issue_id" = "issue_id_p";
3486 DELETE FROM "delegating_interest_snapshot"
3487 WHERE "issue_id" = "issue_id_p";
3488 DELETE FROM "direct_interest_snapshot"
3489 WHERE "issue_id" = "issue_id_p";
3490 DELETE FROM "delegating_population_snapshot"
3491 WHERE "issue_id" = "issue_id_p";
3492 DELETE FROM "direct_population_snapshot"
3493 WHERE "issue_id" = "issue_id_p";
3494 DELETE FROM "ignored_issue"
3495 WHERE "issue_id" = "issue_id_p";
3496 DELETE FROM "delegation"
3497 WHERE "issue_id" = "issue_id_p";
3498 DELETE FROM "supporter"
3499 WHERE "issue_id" = "issue_id_p";
3500 UPDATE "issue" SET
3501 "closed" = "issue_row"."closed",
3502 "ranks_available" = "issue_row"."ranks_available",
3503 "cleaned" = now()
3504 WHERE "id" = "issue_id_p";
3505 END IF;
3506 RETURN;
3507 END;
3508 $$;
3510 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
3513 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
3514 RETURNS VOID
3515 LANGUAGE 'plpgsql' VOLATILE AS $$
3516 BEGIN
3517 UPDATE "member" SET
3518 "last_login" = NULL,
3519 "login" = NULL,
3520 "password" = NULL,
3521 "active" = FALSE,
3522 "notify_email" = NULL,
3523 "notify_email_unconfirmed" = NULL,
3524 "notify_email_secret" = NULL,
3525 "notify_email_secret_expiry" = NULL,
3526 "notify_email_lock_expiry" = NULL,
3527 "password_reset_secret" = NULL,
3528 "password_reset_secret_expiry" = NULL,
3529 "organizational_unit" = NULL,
3530 "internal_posts" = NULL,
3531 "realname" = NULL,
3532 "birthday" = NULL,
3533 "address" = NULL,
3534 "email" = NULL,
3535 "xmpp_address" = NULL,
3536 "website" = NULL,
3537 "phone" = NULL,
3538 "mobile_phone" = NULL,
3539 "profession" = NULL,
3540 "external_memberships" = NULL,
3541 "external_posts" = NULL,
3542 "statement" = NULL
3543 WHERE "id" = "member_id_p";
3544 -- "text_search_data" is updated by triggers
3545 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
3546 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
3547 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
3548 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
3549 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
3550 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
3551 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
3552 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
3553 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
3554 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
3555 DELETE FROM "ignored_issue" WHERE "member_id" = "member_id_p";
3556 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
3557 DELETE FROM "direct_voter" USING "issue"
3558 WHERE "direct_voter"."issue_id" = "issue"."id"
3559 AND "issue"."closed" ISNULL
3560 AND "member_id" = "member_id_p";
3561 RETURN;
3562 END;
3563 $$;
3565 COMMENT ON FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) IS 'Deactivate member and clear certain settings and data of this member (data protection)';
3568 CREATE FUNCTION "delete_private_data"()
3569 RETURNS VOID
3570 LANGUAGE 'plpgsql' VOLATILE AS $$
3571 BEGIN
3572 UPDATE "member" SET
3573 "last_login" = NULL,
3574 "login" = NULL,
3575 "password" = NULL,
3576 "notify_email" = NULL,
3577 "notify_email_unconfirmed" = NULL,
3578 "notify_email_secret" = NULL,
3579 "notify_email_secret_expiry" = NULL,
3580 "notify_email_lock_expiry" = NULL,
3581 "password_reset_secret" = NULL,
3582 "password_reset_secret_expiry" = NULL,
3583 "organizational_unit" = NULL,
3584 "internal_posts" = NULL,
3585 "realname" = NULL,
3586 "birthday" = NULL,
3587 "address" = NULL,
3588 "email" = NULL,
3589 "xmpp_address" = NULL,
3590 "website" = NULL,
3591 "phone" = NULL,
3592 "mobile_phone" = NULL,
3593 "profession" = NULL,
3594 "external_memberships" = NULL,
3595 "external_posts" = NULL,
3596 "statement" = NULL;
3597 -- "text_search_data" is updated by triggers
3598 DELETE FROM "invite_code";
3599 DELETE FROM "setting";
3600 DELETE FROM "setting_map";
3601 DELETE FROM "member_relation_setting";
3602 DELETE FROM "member_image";
3603 DELETE FROM "contact";
3604 DELETE FROM "session";
3605 DELETE FROM "area_setting";
3606 DELETE FROM "issue_setting";
3607 DELETE FROM "initiative_setting";
3608 DELETE FROM "suggestion_setting";
3609 DELETE FROM "ignored_issue";
3610 DELETE FROM "direct_voter" USING "issue"
3611 WHERE "direct_voter"."issue_id" = "issue"."id"
3612 AND "issue"."closed" ISNULL;
3613 RETURN;
3614 END;
3615 $$;
3617 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.';
3621 COMMIT;