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