liquid_feedback_core
view core.sql @ 112:1b1e266df99b
Column "revoked_by_member_id"; Implemented event system
- Added column "revoked_by_member_id" to table "initiative"
- Implemented event system (table "event" and triggers)
- Timeline deprecated (replaced by event system)
- Added column "revoked_by_member_id" to table "initiative"
- Implemented event system (table "event" and triggers)
- Timeline deprecated (replaced by event system)
| author | jbe |
|---|---|
| date | Fri Mar 04 17:15:33 2011 +0100 (2011-03-04) |
| parents | 844c442c5a80 |
| children | 76ffbafb23b5 |
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 "system_setting" (
58 "member_ttl" INTERVAL );
59 CREATE UNIQUE INDEX "system_setting_singleton_idx" ON "system_setting" ((1));
61 COMMENT ON TABLE "system_setting" IS 'This table contains only one row with different settings in each column.';
62 COMMENT ON INDEX "system_setting_singleton_idx" IS 'This index ensures that "system_setting" only contains one row maximum.';
64 COMMENT ON COLUMN "system_setting"."member_ttl" IS 'Time after members get their "active" flag set to FALSE, if they do not login anymore.';
67 CREATE TABLE "contingent" (
68 "time_frame" INTERVAL PRIMARY KEY,
69 "text_entry_limit" INT4,
70 "initiative_limit" INT4 );
72 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.';
74 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';
75 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
78 CREATE TABLE "member" (
79 "id" SERIAL4 PRIMARY KEY,
80 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
81 "last_login" TIMESTAMPTZ,
82 "last_login_public" DATE,
83 "login" TEXT UNIQUE,
84 "password" TEXT,
85 "locked" BOOLEAN NOT NULL DEFAULT FALSE,
86 "active" BOOLEAN NOT NULL DEFAULT TRUE,
87 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
88 "notify_email" TEXT,
89 "notify_email_unconfirmed" TEXT,
90 "notify_email_secret" TEXT UNIQUE,
91 "notify_email_secret_expiry" TIMESTAMPTZ,
92 "notify_email_lock_expiry" TIMESTAMPTZ,
93 "password_reset_secret" TEXT UNIQUE,
94 "password_reset_secret_expiry" TIMESTAMPTZ,
95 "name" TEXT NOT NULL UNIQUE,
96 "identification" TEXT UNIQUE,
97 "organizational_unit" TEXT,
98 "internal_posts" TEXT,
99 "realname" TEXT,
100 "birthday" DATE,
101 "address" TEXT,
102 "email" TEXT,
103 "xmpp_address" TEXT,
104 "website" TEXT,
105 "phone" TEXT,
106 "mobile_phone" TEXT,
107 "profession" TEXT,
108 "external_memberships" TEXT,
109 "external_posts" TEXT,
110 "statement" TEXT,
111 "text_search_data" TSVECTOR );
112 CREATE INDEX "member_active_idx" ON "member" ("active");
113 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
114 CREATE TRIGGER "update_text_search_data"
115 BEFORE INSERT OR UPDATE ON "member"
116 FOR EACH ROW EXECUTE PROCEDURE
117 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
118 "name", "identification", "organizational_unit", "internal_posts",
119 "realname", "external_memberships", "external_posts", "statement" );
121 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
123 COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login';
124 COMMENT ON COLUMN "member"."last_login_public" IS 'Date of last login (time stripped for privacy reasons, updated only after day change)';
125 COMMENT ON COLUMN "member"."login" IS 'Login name';
126 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
127 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
128 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.';
129 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
130 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
131 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
132 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
133 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
134 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
135 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member';
136 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
137 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
138 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
139 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
140 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
141 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
142 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
143 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his homepage within the system';
146 CREATE TABLE "member_history" (
147 "id" SERIAL8 PRIMARY KEY,
148 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
149 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
150 "active" BOOLEAN NOT NULL,
151 "name" TEXT NOT NULL );
152 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
154 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
156 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
157 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
160 CREATE TABLE "invite_code" (
161 "id" SERIAL8 PRIMARY KEY,
162 "code" TEXT NOT NULL UNIQUE,
163 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
164 "used" TIMESTAMPTZ,
165 "member_id" INT4 UNIQUE REFERENCES "member" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
166 "comment" TEXT,
167 CONSTRAINT "only_used_codes_may_refer_to_member" CHECK ("used" NOTNULL OR "member_id" ISNULL) );
169 COMMENT ON TABLE "invite_code" IS 'Invite codes can be used once to create a new member account.';
171 COMMENT ON COLUMN "invite_code"."code" IS 'Secret code';
172 COMMENT ON COLUMN "invite_code"."created" IS 'Time of creation of the secret code';
173 COMMENT ON COLUMN "invite_code"."used" IS 'NULL, if not used yet, otherwise tells when this code was used to create a member account';
174 COMMENT ON COLUMN "invite_code"."member_id" IS 'References the member whose account was created with this code';
175 COMMENT ON COLUMN "invite_code"."comment" IS 'Comment on the code, which is to be used for administrative reasons only';
178 CREATE TABLE "setting" (
179 PRIMARY KEY ("member_id", "key"),
180 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
181 "key" TEXT NOT NULL,
182 "value" TEXT NOT NULL );
183 CREATE INDEX "setting_key_idx" ON "setting" ("key");
185 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
187 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
190 CREATE TABLE "setting_map" (
191 PRIMARY KEY ("member_id", "key", "subkey"),
192 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
193 "key" TEXT NOT NULL,
194 "subkey" TEXT NOT NULL,
195 "value" TEXT NOT NULL );
196 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
198 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
200 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
201 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
202 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
205 CREATE TABLE "member_relation_setting" (
206 PRIMARY KEY ("member_id", "key", "other_member_id"),
207 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
208 "key" TEXT NOT NULL,
209 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
210 "value" TEXT NOT NULL );
212 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
215 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
217 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
220 CREATE TABLE "member_image" (
221 PRIMARY KEY ("member_id", "image_type", "scaled"),
222 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
223 "image_type" "member_image_type",
224 "scaled" BOOLEAN,
225 "content_type" TEXT,
226 "data" BYTEA NOT NULL );
228 COMMENT ON TABLE "member_image" IS 'Images of members';
230 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
233 CREATE TABLE "member_count" (
234 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
235 "total_count" INT4 NOT NULL );
237 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';
239 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
240 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
243 CREATE TABLE "contact" (
244 PRIMARY KEY ("member_id", "other_member_id"),
245 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
246 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
247 "public" BOOLEAN NOT NULL DEFAULT FALSE,
248 CONSTRAINT "cant_save_yourself_as_contact"
249 CHECK ("member_id" != "other_member_id") );
251 COMMENT ON TABLE "contact" IS 'Contact lists';
253 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
254 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
255 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
258 CREATE TABLE "session" (
259 "ident" TEXT PRIMARY KEY,
260 "additional_secret" TEXT,
261 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
262 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
263 "lang" TEXT );
264 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
266 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';
268 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
269 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
270 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
271 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
274 CREATE TABLE "policy" (
275 "id" SERIAL4 PRIMARY KEY,
276 "index" INT4 NOT NULL,
277 "active" BOOLEAN NOT NULL DEFAULT TRUE,
278 "name" TEXT NOT NULL UNIQUE,
279 "description" TEXT NOT NULL DEFAULT '',
280 "admission_time" INTERVAL NOT NULL,
281 "discussion_time" INTERVAL NOT NULL,
282 "verification_time" INTERVAL NOT NULL,
283 "voting_time" INTERVAL NOT NULL,
284 "issue_quorum_num" INT4 NOT NULL,
285 "issue_quorum_den" INT4 NOT NULL,
286 "initiative_quorum_num" INT4 NOT NULL,
287 "initiative_quorum_den" INT4 NOT NULL,
288 "majority_num" INT4 NOT NULL DEFAULT 1,
289 "majority_den" INT4 NOT NULL DEFAULT 2,
290 "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE );
291 CREATE INDEX "policy_active_idx" ON "policy" ("active");
293 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
295 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
296 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
297 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"';
298 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"';
299 COMMENT ON COLUMN "policy"."verification_time" IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
300 COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "fully_frozen" but not "closed"';
301 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"';
302 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"';
303 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
304 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
305 COMMENT ON COLUMN "policy"."majority_num" IS 'Numerator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
306 COMMENT ON COLUMN "policy"."majority_den" IS 'Denominator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
307 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.';
310 CREATE TABLE "unit" (
311 "id" SERIAL4 PRIMARY KEY,
312 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
313 "active" BOOLEAN NOT NULL DEFAULT TRUE,
314 "name" TEXT NOT NULL,
315 "description" TEXT NOT NULL DEFAULT '',
316 "member_count" INT4,
317 "text_search_data" TSVECTOR );
318 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
319 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
320 CREATE INDEX "unit_active_idx" ON "unit" ("active");
321 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
322 CREATE TRIGGER "update_text_search_data"
323 BEFORE INSERT OR UPDATE ON "unit"
324 FOR EACH ROW EXECUTE PROCEDURE
325 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
326 "name", "description" );
328 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
330 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
331 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in units of this area';
332 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
335 CREATE TABLE "area" (
336 "id" SERIAL4 PRIMARY KEY,
337 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
338 "active" BOOLEAN NOT NULL DEFAULT TRUE,
339 "name" TEXT NOT NULL,
340 "description" TEXT NOT NULL DEFAULT '',
341 "direct_member_count" INT4,
342 "member_weight" INT4,
343 "autoreject_weight" INT4,
344 "text_search_data" TSVECTOR );
345 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
346 CREATE INDEX "area_active_idx" ON "area" ("active");
347 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
348 CREATE TRIGGER "update_text_search_data"
349 BEFORE INSERT OR UPDATE ON "area"
350 FOR EACH ROW EXECUTE PROCEDURE
351 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
352 "name", "description" );
354 COMMENT ON TABLE "area" IS 'Subject areas';
356 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
357 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"';
358 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
359 COMMENT ON COLUMN "area"."autoreject_weight" IS 'Sum of weight of members using the autoreject feature';
362 CREATE TABLE "area_setting" (
363 PRIMARY KEY ("member_id", "key", "area_id"),
364 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
365 "key" TEXT NOT NULL,
366 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
367 "value" TEXT NOT NULL );
369 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
372 CREATE TABLE "allowed_policy" (
373 PRIMARY KEY ("area_id", "policy_id"),
374 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
375 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
376 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
377 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
379 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
381 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
384 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
386 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';
389 CREATE TYPE "issue_state" AS ENUM (
390 'admission', 'discussion', 'verification', 'voting',
391 'canceled_all_initiatives_revoked', 'canceled_issue_not_accepted',
392 'calculation', 'canceled_no_initiative_admitted',
393 'finished_without_winner', 'finished_with_winner');
395 COMMENT ON TYPE "issue_state" IS 'State of issues';
398 CREATE TABLE "issue" (
399 "id" SERIAL4 PRIMARY KEY,
400 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
401 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
402 "state" "issue_state" NOT NULL DEFAULT 'admission',
403 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
404 "accepted" TIMESTAMPTZ,
405 "half_frozen" TIMESTAMPTZ,
406 "fully_frozen" TIMESTAMPTZ,
407 "closed" TIMESTAMPTZ,
408 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
409 "cleaned" TIMESTAMPTZ,
410 "admission_time" INTERVAL NOT NULL,
411 "discussion_time" INTERVAL NOT NULL,
412 "verification_time" INTERVAL NOT NULL,
413 "voting_time" INTERVAL NOT NULL,
414 "snapshot" TIMESTAMPTZ,
415 "latest_snapshot_event" "snapshot_event",
416 "population" INT4,
417 "vote_now" INT4,
418 "vote_later" INT4,
419 "voter_count" INT4,
420 CONSTRAINT "valid_state" CHECK ((
421 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
422 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
423 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
424 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
425 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
426 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
427 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
428 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
429 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)) AND (
430 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
431 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
432 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
433 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
434 ("state" = 'canceled_all_initiatives_revoked' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
435 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
436 ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
437 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
438 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
439 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
440 )),
441 CONSTRAINT "state_change_order" CHECK (
442 "created" <= "accepted" AND
443 "accepted" <= "half_frozen" AND
444 "half_frozen" <= "fully_frozen" AND
445 "fully_frozen" <= "closed" ),
446 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
447 "cleaned" ISNULL OR "closed" NOTNULL ),
448 CONSTRAINT "last_snapshot_on_full_freeze"
449 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
450 CONSTRAINT "freeze_requires_snapshot"
451 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
452 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
453 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
454 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
455 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
456 CREATE INDEX "issue_created_idx" ON "issue" ("created");
457 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
458 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
459 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
460 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
461 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
462 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
464 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
466 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
467 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.';
468 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.';
469 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.';
470 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
471 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
472 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
473 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
474 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
475 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
476 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';
477 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';
478 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
479 COMMENT ON COLUMN "issue"."vote_now" IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"';
480 COMMENT ON COLUMN "issue"."vote_later" IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"';
481 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';
484 CREATE TABLE "issue_setting" (
485 PRIMARY KEY ("member_id", "key", "issue_id"),
486 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
487 "key" TEXT NOT NULL,
488 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
489 "value" TEXT NOT NULL );
491 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
494 CREATE TABLE "initiative" (
495 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
496 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
497 "id" SERIAL4 PRIMARY KEY,
498 "name" TEXT NOT NULL,
499 "discussion_url" TEXT,
500 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
501 "revoked" TIMESTAMPTZ,
502 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
503 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
504 "admitted" BOOLEAN,
505 "supporter_count" INT4,
506 "informed_supporter_count" INT4,
507 "satisfied_supporter_count" INT4,
508 "satisfied_informed_supporter_count" INT4,
509 "positive_votes" INT4,
510 "negative_votes" INT4,
511 "agreed" BOOLEAN,
512 "rank" INT4,
513 "text_search_data" TSVECTOR,
514 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
515 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL),
516 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
517 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
518 CONSTRAINT "revoked_initiatives_cant_be_admitted"
519 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
520 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"
521 CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "agreed" ISNULL)),
522 CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"
523 CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL),
524 CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"
525 CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) );
526 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
527 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
528 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
529 CREATE TRIGGER "update_text_search_data"
530 BEFORE INSERT OR UPDATE ON "initiative"
531 FOR EACH ROW EXECUTE PROCEDURE
532 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
533 "name", "discussion_url");
535 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.';
537 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
538 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
539 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoked the initiative';
540 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
541 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
542 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
543 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
544 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
545 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
546 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
547 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"';
548 COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';
551 CREATE TABLE "battle" (
552 PRIMARY KEY ("issue_id", "winning_initiative_id", "losing_initiative_id"),
553 "issue_id" INT4,
554 "winning_initiative_id" INT4,
555 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
556 "losing_initiative_id" INT4,
557 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
558 "count" INT4 NOT NULL);
560 COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue';
563 CREATE TABLE "initiative_setting" (
564 PRIMARY KEY ("member_id", "key", "initiative_id"),
565 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
566 "key" TEXT NOT NULL,
567 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
568 "value" TEXT NOT NULL );
570 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
573 CREATE TABLE "draft" (
574 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
575 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
576 "id" SERIAL8 PRIMARY KEY,
577 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
578 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
579 "formatting_engine" TEXT,
580 "content" TEXT NOT NULL,
581 "text_search_data" TSVECTOR );
582 CREATE INDEX "draft_created_idx" ON "draft" ("created");
583 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
584 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
585 CREATE TRIGGER "update_text_search_data"
586 BEFORE INSERT OR UPDATE ON "draft"
587 FOR EACH ROW EXECUTE PROCEDURE
588 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
590 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.';
592 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
593 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
596 CREATE TABLE "rendered_draft" (
597 PRIMARY KEY ("draft_id", "format"),
598 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
599 "format" TEXT,
600 "content" TEXT NOT NULL );
602 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)';
605 CREATE TABLE "suggestion" (
606 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
607 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
608 "id" SERIAL8 PRIMARY KEY,
609 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
610 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
611 "name" TEXT NOT NULL,
612 "description" TEXT NOT NULL DEFAULT '',
613 "text_search_data" TSVECTOR,
614 "minus2_unfulfilled_count" INT4,
615 "minus2_fulfilled_count" INT4,
616 "minus1_unfulfilled_count" INT4,
617 "minus1_fulfilled_count" INT4,
618 "plus1_unfulfilled_count" INT4,
619 "plus1_fulfilled_count" INT4,
620 "plus2_unfulfilled_count" INT4,
621 "plus2_fulfilled_count" INT4 );
622 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
623 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
624 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
625 CREATE TRIGGER "update_text_search_data"
626 BEFORE INSERT OR UPDATE ON "suggestion"
627 FOR EACH ROW EXECUTE PROCEDURE
628 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
629 "name", "description");
631 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';
633 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
634 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
635 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
636 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
637 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
638 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
639 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
640 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
643 CREATE TABLE "suggestion_setting" (
644 PRIMARY KEY ("member_id", "key", "suggestion_id"),
645 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
646 "key" TEXT NOT NULL,
647 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
648 "value" TEXT NOT NULL );
650 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
653 CREATE TABLE "invite_code_unit" (
654 PRIMARY KEY ("invite_code_id", "unit_id"),
655 "invite_code_id" INT8 REFERENCES "invite_code" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
656 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
658 COMMENT ON TABLE "invite_code_unit" IS 'Units where accounts created with a given invite codes get voting rights';
661 CREATE TABLE "privilege" (
662 PRIMARY KEY ("unit_id", "member_id"),
663 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
664 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
665 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
666 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
667 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
668 "voting_right_manager" BOOLEAN NOT NULL DEFAULT FALSE,
669 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE );
671 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
673 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke admin privileges to/from other users';
674 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create or lock sub units';
675 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create or lock areas and set area parameters';
676 COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote inside the unit';
677 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to discuss and vote';
680 CREATE TYPE "notify_level" AS ENUM ('never', 'supported', 'interested', 'always');
682 COMMENT ON TYPE "notify_level" IS 'Type used to indicate when a member wants to get certain notifications';
685 CREATE TABLE "membership" (
686 PRIMARY KEY ("area_id", "member_id"),
687 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
688 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
689 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE,
690 "notify_issue" "notify_level" NOT NULL DEFAULT 'always' CHECK ("notify_issue" IN ('never', 'always')),
691 "notify_state" "notify_level" NOT NULL DEFAULT 'interested' CHECK ("notify_state" IN ('never', 'interested', 'always')),
692 "notify_initiative" "notify_level" NOT NULL DEFAULT 'interested' CHECK ("notify_initiative" IN ('never', 'interested', 'always')),
693 "notify_draft" "notify_level" NOT NULL DEFAULT 'supported',
694 "notify_suggestion" "notify_level" NOT NULL DEFAULT 'supported');
695 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
697 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
699 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.';
700 COMMENT ON COLUMN "membership"."notify_issue" IS 'Selects when member gets notifications about a new issue (first initiative)';
701 COMMENT ON COLUMN "membership"."notify_state" IS 'Selects when member gets notifications about issue state changes';
702 COMMENT ON COLUMN "membership"."notify_initiative" IS 'Selects when member gets notifications about new initiatives';
703 COMMENT ON COLUMN "membership"."notify_draft" IS 'Selects when member gets notifications about new drafts';
704 COMMENT ON COLUMN "membership"."notify_suggestion" IS 'Selects when member gets notifications about new suggestions';
707 CREATE TABLE "interest" (
708 PRIMARY KEY ("issue_id", "member_id"),
709 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
710 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
711 "autoreject" BOOLEAN,
712 "voting_requested" BOOLEAN );
713 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
715 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.';
717 COMMENT ON COLUMN "interest"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure';
718 COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply';
721 CREATE TABLE "ignored_issue" (
722 PRIMARY KEY ("issue_id", "member_id"),
723 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
724 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
725 "new" BOOLEAN NOT NULL DEFAULT FALSE,
726 "accepted" BOOLEAN NOT NULL DEFAULT FALSE,
727 "half_frozen" BOOLEAN NOT NULL DEFAULT FALSE,
728 "fully_frozen" BOOLEAN NOT NULL DEFAULT FALSE );
729 CREATE INDEX "ignored_issue_member_id_idx" ON "ignored_issue" ("member_id");
731 COMMENT ON TABLE "ignored_issue" IS 'Table to store member specific options to ignore issues in selected states';
733 COMMENT ON COLUMN "ignored_issue"."new" IS 'Apply when issue is neither closed nor accepted';
734 COMMENT ON COLUMN "ignored_issue"."accepted" IS 'Apply when issue is accepted but not (half_)frozen or closed';
735 COMMENT ON COLUMN "ignored_issue"."half_frozen" IS 'Apply when issue is half_frozen but not fully_frozen or closed';
736 COMMENT ON COLUMN "ignored_issue"."fully_frozen" IS 'Apply when issue is fully_frozen (in voting) and not closed';
739 CREATE TABLE "initiator" (
740 PRIMARY KEY ("initiative_id", "member_id"),
741 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
742 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
743 "accepted" BOOLEAN );
744 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
746 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.';
748 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.';
751 CREATE TABLE "supporter" (
752 "issue_id" INT4 NOT NULL,
753 PRIMARY KEY ("initiative_id", "member_id"),
754 "initiative_id" INT4,
755 "member_id" INT4,
756 "draft_id" INT8 NOT NULL,
757 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
758 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
759 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
761 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.';
763 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
766 CREATE TABLE "opinion" (
767 "initiative_id" INT4 NOT NULL,
768 PRIMARY KEY ("suggestion_id", "member_id"),
769 "suggestion_id" INT8,
770 "member_id" INT4,
771 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
772 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
773 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
774 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
775 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
777 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.';
779 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
782 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
784 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
787 CREATE TABLE "delegation" (
788 "id" SERIAL8 PRIMARY KEY,
789 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
790 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
791 "scope" "delegation_scope" NOT NULL,
792 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
793 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
794 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
795 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
796 CONSTRAINT "no_unit_delegation_to_null"
797 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
798 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
799 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
800 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
801 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
802 UNIQUE ("unit_id", "truster_id"),
803 UNIQUE ("area_id", "truster_id"),
804 UNIQUE ("issue_id", "truster_id") );
805 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
806 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
808 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
810 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
811 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
812 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
815 CREATE TABLE "direct_population_snapshot" (
816 PRIMARY KEY ("issue_id", "event", "member_id"),
817 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
818 "event" "snapshot_event",
819 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
820 "weight" INT4 );
821 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
823 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
825 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
826 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
829 CREATE TABLE "delegating_population_snapshot" (
830 PRIMARY KEY ("issue_id", "event", "member_id"),
831 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
832 "event" "snapshot_event",
833 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
834 "weight" INT4,
835 "scope" "delegation_scope" NOT NULL,
836 "delegate_member_ids" INT4[] NOT NULL );
837 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
839 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
841 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
842 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
843 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
844 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"';
847 CREATE TABLE "direct_interest_snapshot" (
848 PRIMARY KEY ("issue_id", "event", "member_id"),
849 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
850 "event" "snapshot_event",
851 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
852 "weight" INT4,
853 "voting_requested" BOOLEAN );
854 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
856 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
858 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
859 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
860 COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';
863 CREATE TABLE "delegating_interest_snapshot" (
864 PRIMARY KEY ("issue_id", "event", "member_id"),
865 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
866 "event" "snapshot_event",
867 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
868 "weight" INT4,
869 "scope" "delegation_scope" NOT NULL,
870 "delegate_member_ids" INT4[] NOT NULL );
871 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
873 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
875 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
876 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
877 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
878 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"';
881 CREATE TABLE "direct_supporter_snapshot" (
882 "issue_id" INT4 NOT NULL,
883 PRIMARY KEY ("initiative_id", "event", "member_id"),
884 "initiative_id" INT4,
885 "event" "snapshot_event",
886 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
887 "informed" BOOLEAN NOT NULL,
888 "satisfied" BOOLEAN NOT NULL,
889 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
890 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
891 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
893 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
895 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
896 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
897 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
900 CREATE TABLE "direct_voter" (
901 PRIMARY KEY ("issue_id", "member_id"),
902 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
903 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
904 "weight" INT4,
905 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
906 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
908 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.';
910 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
911 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
914 CREATE TABLE "delegating_voter" (
915 PRIMARY KEY ("issue_id", "member_id"),
916 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
917 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
918 "weight" INT4,
919 "scope" "delegation_scope" NOT NULL,
920 "delegate_member_ids" INT4[] NOT NULL );
921 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
923 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
925 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
926 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
927 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"';
930 CREATE TABLE "vote" (
931 "issue_id" INT4 NOT NULL,
932 PRIMARY KEY ("initiative_id", "member_id"),
933 "initiative_id" INT4,
934 "member_id" INT4,
935 "grade" INT4,
936 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
937 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
938 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
940 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.';
942 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.';
945 CREATE TABLE "issue_comment" (
946 PRIMARY KEY ("issue_id", "member_id"),
947 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
948 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
949 "changed" TIMESTAMPTZ NOT NULL DEFAULT now(),
950 "formatting_engine" TEXT,
951 "content" TEXT NOT NULL,
952 "text_search_data" TSVECTOR );
953 CREATE INDEX "issue_comment_member_id_idx" ON "issue_comment" ("member_id");
954 CREATE INDEX "issue_comment_text_search_data_idx" ON "issue_comment" USING gin ("text_search_data");
955 CREATE TRIGGER "update_text_search_data"
956 BEFORE INSERT OR UPDATE ON "issue_comment"
957 FOR EACH ROW EXECUTE PROCEDURE
958 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
960 COMMENT ON TABLE "issue_comment" IS 'Place to store free comments of members related to issues';
962 COMMENT ON COLUMN "issue_comment"."changed" IS 'Time the comment was last changed';
965 CREATE TABLE "rendered_issue_comment" (
966 PRIMARY KEY ("issue_id", "member_id", "format"),
967 FOREIGN KEY ("issue_id", "member_id")
968 REFERENCES "issue_comment" ("issue_id", "member_id")
969 ON DELETE CASCADE ON UPDATE CASCADE,
970 "issue_id" INT4,
971 "member_id" INT4,
972 "format" TEXT,
973 "content" TEXT NOT NULL );
975 COMMENT ON TABLE "rendered_issue_comment" IS 'This table may be used by frontends to cache "rendered" issue comments (e.g. HTML output generated from wiki text)';
978 CREATE TABLE "voting_comment" (
979 PRIMARY KEY ("issue_id", "member_id"),
980 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
981 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
982 "changed" TIMESTAMPTZ,
983 "formatting_engine" TEXT,
984 "content" TEXT NOT NULL,
985 "text_search_data" TSVECTOR );
986 CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id");
987 CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data");
988 CREATE TRIGGER "update_text_search_data"
989 BEFORE INSERT OR UPDATE ON "voting_comment"
990 FOR EACH ROW EXECUTE PROCEDURE
991 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
993 COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.';
995 COMMENT ON COLUMN "voting_comment"."changed" IS 'Is to be set or updated by the frontend, if comment was inserted or updated AFTER the issue has been closed. Otherwise it shall be set to NULL.';
998 CREATE TABLE "rendered_voting_comment" (
999 PRIMARY KEY ("issue_id", "member_id", "format"),
1000 FOREIGN KEY ("issue_id", "member_id")
1001 REFERENCES "voting_comment" ("issue_id", "member_id")
1002 ON DELETE CASCADE ON UPDATE CASCADE,
1003 "issue_id" INT4,
1004 "member_id" INT4,
1005 "format" TEXT,
1006 "content" TEXT NOT NULL );
1008 COMMENT ON TABLE "rendered_voting_comment" IS 'This table may be used by frontends to cache "rendered" voting comments (e.g. HTML output generated from wiki text)';
1011 CREATE TYPE "event_type" AS ENUM (
1012 'issue_state_changed',
1013 'initiative_created_in_new_issue',
1014 'initiative_created_in_existing_issue',
1015 'initiative_revoked',
1016 'new_draft_created',
1017 'suggestion_created');
1019 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
1022 CREATE TABLE "event" (
1023 "id" SERIAL8 PRIMARY KEY,
1024 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
1025 "event" "event_type" NOT NULL,
1026 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1027 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1028 "state" "issue_state" CHECK ("state" != 'calculation'),
1029 "initiative_id" INT4,
1030 "draft_id" INT8,
1031 "suggestion_id" INT8,
1032 FOREIGN KEY ("issue_id", "initiative_id")
1033 REFERENCES "initiative" ("issue_id", "id")
1034 ON DELETE CASCADE ON UPDATE CASCADE,
1035 FOREIGN KEY ("initiative_id", "draft_id")
1036 REFERENCES "draft" ("initiative_id", "id")
1037 ON DELETE CASCADE ON UPDATE CASCADE,
1038 FOREIGN KEY ("initiative_id", "suggestion_id")
1039 REFERENCES "suggestion" ("initiative_id", "id")
1040 ON DELETE CASCADE ON UPDATE CASCADE,
1041 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
1042 "event" != 'issue_state_changed' OR (
1043 "member_id" ISNULL AND
1044 "issue_id" NOTNULL AND
1045 "initiative_id" ISNULL AND
1046 "draft_id" ISNULL AND
1047 "suggestion_id" ISNULL )),
1048 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
1049 "event" NOT IN (
1050 'initiative_created_in_new_issue',
1051 'initiative_created_in_existing_issue',
1052 'initiative_revoked',
1053 'new_draft_created'
1054 ) OR (
1055 "member_id" NOTNULL AND
1056 "issue_id" NOTNULL AND
1057 "initiative_id" NOTNULL AND
1058 "draft_id" NOTNULL AND
1059 "suggestion_id" ISNULL )),
1060 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
1061 "event" != 'suggestion_created' OR (
1062 "member_id" NOTNULL AND
1063 "issue_id" NOTNULL AND
1064 "initiative_id" NOTNULL AND
1065 "draft_id" ISNULL AND
1066 "suggestion_id" NOTNULL )) );
1068 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
1072 ----------------------------------------------
1073 -- Writing of history entries and event log --
1074 ----------------------------------------------
1076 CREATE FUNCTION "write_member_history_trigger"()
1077 RETURNS TRIGGER
1078 LANGUAGE 'plpgsql' VOLATILE AS $$
1079 BEGIN
1080 IF
1081 NEW."active" != OLD."active" OR
1082 NEW."name" != OLD."name"
1083 THEN
1084 INSERT INTO "member_history"
1085 ("member_id", "active", "name")
1086 VALUES (NEW."id", OLD."active", OLD."name");
1087 END IF;
1088 RETURN NULL;
1089 END;
1090 $$;
1092 CREATE TRIGGER "write_member_history"
1093 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1094 "write_member_history_trigger"();
1096 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
1097 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
1100 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
1101 RETURNS TRIGGER
1102 LANGUAGE 'plpgsql' VOLATILE AS $$
1103 BEGIN
1104 IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN
1105 INSERT INTO "event" ("event", "issue_id", "state")
1106 VALUES ('issue_state_changed', NEW."id", NEW."state");
1107 END IF;
1108 RETURN NULL;
1109 END;
1110 $$;
1112 CREATE TRIGGER "write_event_issue_state_changed"
1113 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
1114 "write_event_issue_state_changed_trigger"();
1116 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
1117 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
1120 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1121 RETURNS TRIGGER
1122 LANGUAGE 'plpgsql' VOLATILE AS $$
1123 DECLARE
1124 "initiative_row" "initiative"%ROWTYPE;
1125 "event_v" "event_type";
1126 BEGIN
1127 SELECT * INTO "initiative_row" FROM "initiative"
1128 WHERE "id" = NEW."initiative_id";
1129 IF EXISTS (
1130 SELECT NULL FROM "draft"
1131 WHERE "initiative_id" = NEW."initiative_id"
1132 AND "id" != NEW."id"
1133 ) THEN
1134 "event_v" := 'new_draft_created';
1135 ELSE
1136 IF EXISTS (
1137 SELECT NULL FROM "initiative"
1138 WHERE "issue_id" = "initiative_row"."issue_id"
1139 AND "id" != "initiative_row"."id"
1140 ) THEN
1141 "event_v" := 'initiative_created_in_existing_issue';
1142 ELSE
1143 "event_v" := 'initiative_created_in_new_issue';
1144 END IF;
1145 END IF;
1146 INSERT INTO "event" (
1147 "event", "member_id",
1148 "issue_id", "initiative_id", "draft_id"
1149 ) VALUES (
1150 "event_v",
1151 NEW."author_id",
1152 "initiative_row"."issue_id",
1153 "initiative_row"."id",
1154 NEW."id" );
1155 RETURN NULL;
1156 END;
1157 $$;
1159 CREATE TRIGGER "write_event_initiative_or_draft_created"
1160 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
1161 "write_event_initiative_or_draft_created_trigger"();
1163 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
1164 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
1167 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
1168 RETURNS TRIGGER
1169 LANGUAGE 'plpgsql' VOLATILE AS $$
1170 BEGIN
1171 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1172 INSERT INTO "event" (
1173 "event", "member_id", "issue_id", "initiative_id"
1174 ) VALUES (
1175 'initiative_revoked',
1176 NEW."revoked_by_member_id",
1177 NEW."issue_id",
1178 NEW."id" );
1179 END IF;
1180 RETURN NULL;
1181 END;
1182 $$;
1184 CREATE TRIGGER "write_event_initiative_revoked"
1185 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
1186 "write_event_initiative_revoked_trigger"();
1188 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
1189 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
1192 CREATE FUNCTION "write_event_suggestion_created_trigger"()
1193 RETURNS TRIGGER
1194 LANGUAGE 'plpgsql' VOLATILE AS $$
1195 DECLARE
1196 "initiative_row" "initiative"%ROWTYPE;
1197 BEGIN
1198 SELECT * INTO "initiative_row" FROM "initiative"
1199 WHERE "id" = NEW."initiative_id";
1200 INSERT INTO "event" (
1201 "event", "member_id",
1202 "issue_id", "initiative_id", "suggestion_id"
1203 ) VALUES (
1204 'suggestion_created',
1205 NEW."author_id",
1206 "initiative_row"."issue_id",
1207 "initiative_row"."id",
1208 NEW."id" );
1209 RETURN NULL;
1210 END;
1211 $$;
1213 CREATE TRIGGER "write_event_suggestion_created"
1214 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1215 "write_event_suggestion_created_trigger"();
1217 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
1218 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1222 ----------------------------
1223 -- Additional constraints --
1224 ----------------------------
1227 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
1228 RETURNS TRIGGER
1229 LANGUAGE 'plpgsql' VOLATILE AS $$
1230 BEGIN
1231 IF NOT EXISTS (
1232 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
1233 ) THEN
1234 --RAISE 'Cannot create issue without an initial initiative.' USING
1235 -- ERRCODE = 'integrity_constraint_violation',
1236 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
1237 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
1238 END IF;
1239 RETURN NULL;
1240 END;
1241 $$;
1243 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
1244 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
1245 FOR EACH ROW EXECUTE PROCEDURE
1246 "issue_requires_first_initiative_trigger"();
1248 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
1249 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
1252 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
1253 RETURNS TRIGGER
1254 LANGUAGE 'plpgsql' VOLATILE AS $$
1255 DECLARE
1256 "reference_lost" BOOLEAN;
1257 BEGIN
1258 IF TG_OP = 'DELETE' THEN
1259 "reference_lost" := TRUE;
1260 ELSE
1261 "reference_lost" := NEW."issue_id" != OLD."issue_id";
1262 END IF;
1263 IF
1264 "reference_lost" AND NOT EXISTS (
1265 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
1266 )
1267 THEN
1268 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
1269 END IF;
1270 RETURN NULL;
1271 END;
1272 $$;
1274 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
1275 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1276 FOR EACH ROW EXECUTE PROCEDURE
1277 "last_initiative_deletes_issue_trigger"();
1279 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
1280 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
1283 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
1284 RETURNS TRIGGER
1285 LANGUAGE 'plpgsql' VOLATILE AS $$
1286 BEGIN
1287 IF NOT EXISTS (
1288 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
1289 ) THEN
1290 --RAISE 'Cannot create initiative without an initial draft.' USING
1291 -- ERRCODE = 'integrity_constraint_violation',
1292 -- HINT = 'Create issue, initiative and draft within the same transaction.';
1293 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
1294 END IF;
1295 RETURN NULL;
1296 END;
1297 $$;
1299 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
1300 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1301 FOR EACH ROW EXECUTE PROCEDURE
1302 "initiative_requires_first_draft_trigger"();
1304 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
1305 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
1308 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
1309 RETURNS TRIGGER
1310 LANGUAGE 'plpgsql' VOLATILE AS $$
1311 DECLARE
1312 "reference_lost" BOOLEAN;
1313 BEGIN
1314 IF TG_OP = 'DELETE' THEN
1315 "reference_lost" := TRUE;
1316 ELSE
1317 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
1318 END IF;
1319 IF
1320 "reference_lost" AND NOT EXISTS (
1321 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
1322 )
1323 THEN
1324 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
1325 END IF;
1326 RETURN NULL;
1327 END;
1328 $$;
1330 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
1331 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
1332 FOR EACH ROW EXECUTE PROCEDURE
1333 "last_draft_deletes_initiative_trigger"();
1335 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
1336 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
1339 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
1340 RETURNS TRIGGER
1341 LANGUAGE 'plpgsql' VOLATILE AS $$
1342 BEGIN
1343 IF NOT EXISTS (
1344 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
1345 ) THEN
1346 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
1347 END IF;
1348 RETURN NULL;
1349 END;
1350 $$;
1352 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
1353 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
1354 FOR EACH ROW EXECUTE PROCEDURE
1355 "suggestion_requires_first_opinion_trigger"();
1357 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1358 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1361 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1362 RETURNS TRIGGER
1363 LANGUAGE 'plpgsql' VOLATILE AS $$
1364 DECLARE
1365 "reference_lost" BOOLEAN;
1366 BEGIN
1367 IF TG_OP = 'DELETE' THEN
1368 "reference_lost" := TRUE;
1369 ELSE
1370 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1371 END IF;
1372 IF
1373 "reference_lost" AND NOT EXISTS (
1374 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1375 )
1376 THEN
1377 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1378 END IF;
1379 RETURN NULL;
1380 END;
1381 $$;
1383 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1384 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1385 FOR EACH ROW EXECUTE PROCEDURE
1386 "last_opinion_deletes_suggestion_trigger"();
1388 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1389 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1393 ---------------------------------------------------------------
1394 -- Ensure that votes are not modified when issues are frozen --
1395 ---------------------------------------------------------------
1397 -- NOTE: Frontends should ensure this anyway, but in case of programming
1398 -- errors the following triggers ensure data integrity.
1401 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1402 RETURNS TRIGGER
1403 LANGUAGE 'plpgsql' VOLATILE AS $$
1404 DECLARE
1405 "issue_id_v" "issue"."id"%TYPE;
1406 "issue_row" "issue"%ROWTYPE;
1407 BEGIN
1408 IF TG_OP = 'DELETE' THEN
1409 "issue_id_v" := OLD."issue_id";
1410 ELSE
1411 "issue_id_v" := NEW."issue_id";
1412 END IF;
1413 SELECT INTO "issue_row" * FROM "issue"
1414 WHERE "id" = "issue_id_v" FOR SHARE;
1415 IF "issue_row"."closed" NOTNULL THEN
1416 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
1417 END IF;
1418 RETURN NULL;
1419 END;
1420 $$;
1422 CREATE TRIGGER "forbid_changes_on_closed_issue"
1423 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1424 FOR EACH ROW EXECUTE PROCEDURE
1425 "forbid_changes_on_closed_issue_trigger"();
1427 CREATE TRIGGER "forbid_changes_on_closed_issue"
1428 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1429 FOR EACH ROW EXECUTE PROCEDURE
1430 "forbid_changes_on_closed_issue_trigger"();
1432 CREATE TRIGGER "forbid_changes_on_closed_issue"
1433 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1434 FOR EACH ROW EXECUTE PROCEDURE
1435 "forbid_changes_on_closed_issue_trigger"();
1437 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"';
1438 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';
1439 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';
1440 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';
1444 --------------------------------------------------------------------
1445 -- Auto-retrieval of fields only needed for referential integrity --
1446 --------------------------------------------------------------------
1449 CREATE FUNCTION "autofill_issue_id_trigger"()
1450 RETURNS TRIGGER
1451 LANGUAGE 'plpgsql' VOLATILE AS $$
1452 BEGIN
1453 IF NEW."issue_id" ISNULL THEN
1454 SELECT "issue_id" INTO NEW."issue_id"
1455 FROM "initiative" WHERE "id" = NEW."initiative_id";
1456 END IF;
1457 RETURN NEW;
1458 END;
1459 $$;
1461 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1462 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1464 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1465 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1467 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1468 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1469 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1472 CREATE FUNCTION "autofill_initiative_id_trigger"()
1473 RETURNS TRIGGER
1474 LANGUAGE 'plpgsql' VOLATILE AS $$
1475 BEGIN
1476 IF NEW."initiative_id" ISNULL THEN
1477 SELECT "initiative_id" INTO NEW."initiative_id"
1478 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1479 END IF;
1480 RETURN NEW;
1481 END;
1482 $$;
1484 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1485 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1487 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1488 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1492 -----------------------------------------------------
1493 -- Automatic calculation of certain default values --
1494 -----------------------------------------------------
1497 CREATE FUNCTION "copy_timings_trigger"()
1498 RETURNS TRIGGER
1499 LANGUAGE 'plpgsql' VOLATILE AS $$
1500 DECLARE
1501 "policy_row" "policy"%ROWTYPE;
1502 BEGIN
1503 SELECT * INTO "policy_row" FROM "policy"
1504 WHERE "id" = NEW."policy_id";
1505 IF NEW."admission_time" ISNULL THEN
1506 NEW."admission_time" := "policy_row"."admission_time";
1507 END IF;
1508 IF NEW."discussion_time" ISNULL THEN
1509 NEW."discussion_time" := "policy_row"."discussion_time";
1510 END IF;
1511 IF NEW."verification_time" ISNULL THEN
1512 NEW."verification_time" := "policy_row"."verification_time";
1513 END IF;
1514 IF NEW."voting_time" ISNULL THEN
1515 NEW."voting_time" := "policy_row"."voting_time";
1516 END IF;
1517 RETURN NEW;
1518 END;
1519 $$;
1521 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1522 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1524 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1525 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1528 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
1529 RETURNS TRIGGER
1530 LANGUAGE 'plpgsql' VOLATILE AS $$
1531 BEGIN
1532 IF NEW."draft_id" ISNULL THEN
1533 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1534 WHERE "initiative_id" = NEW."initiative_id";
1535 END IF;
1536 RETURN NEW;
1537 END;
1538 $$;
1540 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1541 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
1543 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
1544 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';
1548 ----------------------------------------
1549 -- Automatic creation of dependencies --
1550 ----------------------------------------
1553 CREATE FUNCTION "autocreate_interest_trigger"()
1554 RETURNS TRIGGER
1555 LANGUAGE 'plpgsql' VOLATILE AS $$
1556 BEGIN
1557 IF NOT EXISTS (
1558 SELECT NULL FROM "initiative" JOIN "interest"
1559 ON "initiative"."issue_id" = "interest"."issue_id"
1560 WHERE "initiative"."id" = NEW."initiative_id"
1561 AND "interest"."member_id" = NEW."member_id"
1562 ) THEN
1563 BEGIN
1564 INSERT INTO "interest" ("issue_id", "member_id")
1565 SELECT "issue_id", NEW."member_id"
1566 FROM "initiative" WHERE "id" = NEW."initiative_id";
1567 EXCEPTION WHEN unique_violation THEN END;
1568 END IF;
1569 RETURN NEW;
1570 END;
1571 $$;
1573 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1574 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1576 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1577 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';
1580 CREATE FUNCTION "autocreate_supporter_trigger"()
1581 RETURNS TRIGGER
1582 LANGUAGE 'plpgsql' VOLATILE AS $$
1583 BEGIN
1584 IF NOT EXISTS (
1585 SELECT NULL FROM "suggestion" JOIN "supporter"
1586 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1587 WHERE "suggestion"."id" = NEW."suggestion_id"
1588 AND "supporter"."member_id" = NEW."member_id"
1589 ) THEN
1590 BEGIN
1591 INSERT INTO "supporter" ("initiative_id", "member_id")
1592 SELECT "initiative_id", NEW."member_id"
1593 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1594 EXCEPTION WHEN unique_violation THEN END;
1595 END IF;
1596 RETURN NEW;
1597 END;
1598 $$;
1600 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1601 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1603 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1604 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.';
1608 ------------------------------------------
1609 -- Views and helper functions for views --
1610 ------------------------------------------
1613 CREATE VIEW "unit_delegation" AS
1614 SELECT
1615 "unit"."id" AS "unit_id",
1616 "delegation"."id",
1617 "delegation"."truster_id",
1618 "delegation"."trustee_id",
1619 "delegation"."scope"
1620 FROM "unit"
1621 JOIN "delegation"
1622 ON "delegation"."unit_id" = "unit"."id"
1623 JOIN "member"
1624 ON "delegation"."truster_id" = "member"."id"
1625 JOIN "privilege"
1626 ON "delegation"."unit_id" = "privilege"."unit_id"
1627 AND "delegation"."truster_id" = "privilege"."member_id"
1628 WHERE "member"."active" AND "privilege"."voting_right";
1630 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
1633 CREATE VIEW "area_delegation" AS
1634 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1635 "area"."id" AS "area_id",
1636 "delegation"."id",
1637 "delegation"."truster_id",
1638 "delegation"."trustee_id",
1639 "delegation"."scope"
1640 FROM "area"
1641 JOIN "delegation"
1642 ON "delegation"."unit_id" = "area"."unit_id"
1643 OR "delegation"."area_id" = "area"."id"
1644 JOIN "member"
1645 ON "delegation"."truster_id" = "member"."id"
1646 JOIN "privilege"
1647 ON "area"."unit_id" = "privilege"."unit_id"
1648 AND "delegation"."truster_id" = "privilege"."member_id"
1649 WHERE "member"."active" AND "privilege"."voting_right"
1650 ORDER BY
1651 "area"."id",
1652 "delegation"."truster_id",
1653 "delegation"."scope" DESC;
1655 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
1658 CREATE VIEW "issue_delegation" AS
1659 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1660 "issue"."id" AS "issue_id",
1661 "delegation"."id",
1662 "delegation"."truster_id",
1663 "delegation"."trustee_id",
1664 "delegation"."scope"
1665 FROM "issue"
1666 JOIN "area"
1667 ON "area"."id" = "issue"."area_id"
1668 JOIN "delegation"
1669 ON "delegation"."unit_id" = "area"."unit_id"
1670 OR "delegation"."area_id" = "area"."id"
1671 OR "delegation"."issue_id" = "issue"."id"
1672 JOIN "member"
1673 ON "delegation"."truster_id" = "member"."id"
1674 JOIN "privilege"
1675 ON "area"."unit_id" = "privilege"."unit_id"
1676 AND "delegation"."truster_id" = "privilege"."member_id"
1677 WHERE "member"."active" AND "privilege"."voting_right"
1678 ORDER BY
1679 "issue"."id",
1680 "delegation"."truster_id",
1681 "delegation"."scope" DESC;
1683 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
1686 CREATE FUNCTION "membership_weight_with_skipping"
1687 ( "area_id_p" "area"."id"%TYPE,
1688 "member_id_p" "member"."id"%TYPE,
1689 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1690 RETURNS INT4
1691 LANGUAGE 'plpgsql' STABLE AS $$
1692 DECLARE
1693 "sum_v" INT4;
1694 "delegation_row" "area_delegation"%ROWTYPE;
1695 BEGIN
1696 "sum_v" := 1;
1697 FOR "delegation_row" IN
1698 SELECT "area_delegation".*
1699 FROM "area_delegation" LEFT JOIN "membership"
1700 ON "membership"."area_id" = "area_id_p"
1701 AND "membership"."member_id" = "area_delegation"."truster_id"
1702 WHERE "area_delegation"."area_id" = "area_id_p"
1703 AND "area_delegation"."trustee_id" = "member_id_p"
1704 AND "membership"."member_id" ISNULL
1705 LOOP
1706 IF NOT
1707 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1708 THEN
1709 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1710 "area_id_p",
1711 "delegation_row"."truster_id",
1712 "skip_member_ids_p" || "delegation_row"."truster_id"
1713 );
1714 END IF;
1715 END LOOP;
1716 RETURN "sum_v";
1717 END;
1718 $$;
1720 COMMENT ON FUNCTION "membership_weight_with_skipping"
1721 ( "area"."id"%TYPE,
1722 "member"."id"%TYPE,
1723 INT4[] )
1724 IS 'Helper function for "membership_weight" function';
1727 CREATE FUNCTION "membership_weight"
1728 ( "area_id_p" "area"."id"%TYPE,
1729 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1730 RETURNS INT4
1731 LANGUAGE 'plpgsql' STABLE AS $$
1732 BEGIN
1733 RETURN "membership_weight_with_skipping"(
1734 "area_id_p",
1735 "member_id_p",
1736 ARRAY["member_id_p"]
1737 );
1738 END;
1739 $$;
1741 COMMENT ON FUNCTION "membership_weight"
1742 ( "area"."id"%TYPE,
1743 "member"."id"%TYPE )
1744 IS 'Calculates the potential voting weight of a member in a given area';
1747 CREATE VIEW "member_count_view" AS
1748 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1750 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1753 CREATE VIEW "unit_member_count" AS
1754 SELECT
1755 "unit"."id" AS "unit_id",
1756 sum("member"."id") AS "member_count"
1757 FROM "unit"
1758 LEFT JOIN "privilege"
1759 ON "privilege"."unit_id" = "unit"."id"
1760 AND "privilege"."voting_right"
1761 LEFT JOIN "member"
1762 ON "member"."id" = "privilege"."member_id"
1763 AND "member"."active"
1764 GROUP BY "unit"."id";
1766 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
1769 CREATE VIEW "area_member_count" AS
1770 SELECT
1771 "area"."id" AS "area_id",
1772 count("member"."id") AS "direct_member_count",
1773 coalesce(
1774 sum(
1775 CASE WHEN "member"."id" NOTNULL THEN
1776 "membership_weight"("area"."id", "member"."id")
1777 ELSE 0 END
1778 )
1779 ) AS "member_weight",
1780 coalesce(
1781 sum(
1782 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
1783 "membership_weight"("area"."id", "member"."id")
1784 ELSE 0 END
1785 )
1786 ) AS "autoreject_weight"
1787 FROM "area"
1788 LEFT JOIN "membership"
1789 ON "area"."id" = "membership"."area_id"
1790 LEFT JOIN "privilege"
1791 ON "privilege"."unit_id" = "area"."unit_id"
1792 AND "privilege"."member_id" = "membership"."member_id"
1793 AND "privilege"."voting_right"
1794 LEFT JOIN "member"
1795 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
1796 AND "member"."active"
1797 GROUP BY "area"."id";
1799 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count", "member_weight" and "autoreject_weight" columns of table "area"';
1802 CREATE VIEW "opening_draft" AS
1803 SELECT "draft".* FROM (
1804 SELECT
1805 "initiative"."id" AS "initiative_id",
1806 min("draft"."id") AS "draft_id"
1807 FROM "initiative" JOIN "draft"
1808 ON "initiative"."id" = "draft"."initiative_id"
1809 GROUP BY "initiative"."id"
1810 ) AS "subquery"
1811 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1813 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1816 CREATE VIEW "current_draft" AS
1817 SELECT "draft".* FROM (
1818 SELECT
1819 "initiative"."id" AS "initiative_id",
1820 max("draft"."id") AS "draft_id"
1821 FROM "initiative" JOIN "draft"
1822 ON "initiative"."id" = "draft"."initiative_id"
1823 GROUP BY "initiative"."id"
1824 ) AS "subquery"
1825 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1827 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1830 CREATE VIEW "critical_opinion" AS
1831 SELECT * FROM "opinion"
1832 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
1833 OR ("degree" = -2 AND "fulfilled" = TRUE);
1835 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
1838 CREATE VIEW "battle_view" AS
1839 SELECT
1840 "issue"."id" AS "issue_id",
1841 "winning_initiative"."id" AS "winning_initiative_id",
1842 "losing_initiative"."id" AS "losing_initiative_id",
1843 sum(
1844 CASE WHEN
1845 coalesce("better_vote"."grade", 0) >
1846 coalesce("worse_vote"."grade", 0)
1847 THEN "direct_voter"."weight" ELSE 0 END
1848 ) AS "count"
1849 FROM "issue"
1850 LEFT JOIN "direct_voter"
1851 ON "issue"."id" = "direct_voter"."issue_id"
1852 JOIN "initiative" AS "winning_initiative"
1853 ON "issue"."id" = "winning_initiative"."issue_id"
1854 AND "winning_initiative"."agreed"
1855 JOIN "initiative" AS "losing_initiative"
1856 ON "issue"."id" = "losing_initiative"."issue_id"
1857 AND "losing_initiative"."agreed"
1858 LEFT JOIN "vote" AS "better_vote"
1859 ON "direct_voter"."member_id" = "better_vote"."member_id"
1860 AND "winning_initiative"."id" = "better_vote"."initiative_id"
1861 LEFT JOIN "vote" AS "worse_vote"
1862 ON "direct_voter"."member_id" = "worse_vote"."member_id"
1863 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
1864 WHERE "issue"."closed" NOTNULL
1865 AND "issue"."cleaned" ISNULL
1866 AND "winning_initiative"."id" != "losing_initiative"."id"
1867 GROUP BY
1868 "issue"."id",
1869 "winning_initiative"."id",
1870 "losing_initiative"."id";
1872 COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative to another; Used to fill "battle" table';
1875 CREATE VIEW "expired_session" AS
1876 SELECT * FROM "session" WHERE now() > "expiry";
1878 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
1879 DELETE FROM "session" WHERE "ident" = OLD."ident";
1881 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
1882 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
1885 CREATE VIEW "open_issue" AS
1886 SELECT * FROM "issue" WHERE "closed" ISNULL;
1888 COMMENT ON VIEW "open_issue" IS 'All open issues';
1891 CREATE VIEW "issue_with_ranks_missing" AS
1892 SELECT * FROM "issue"
1893 WHERE "fully_frozen" NOTNULL
1894 AND "closed" NOTNULL
1895 AND "ranks_available" = FALSE;
1897 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
1900 CREATE VIEW "member_contingent" AS
1901 SELECT
1902 "member"."id" AS "member_id",
1903 "contingent"."time_frame",
1904 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
1905 (
1906 SELECT count(1) FROM "draft"
1907 WHERE "draft"."author_id" = "member"."id"
1908 AND "draft"."created" > now() - "contingent"."time_frame"
1909 ) + (
1910 SELECT count(1) FROM "suggestion"
1911 WHERE "suggestion"."author_id" = "member"."id"
1912 AND "suggestion"."created" > now() - "contingent"."time_frame"
1913 )
1914 ELSE NULL END AS "text_entry_count",
1915 "contingent"."text_entry_limit",
1916 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
1917 SELECT count(1) FROM "opening_draft"
1918 WHERE "opening_draft"."author_id" = "member"."id"
1919 AND "opening_draft"."created" > now() - "contingent"."time_frame"
1920 ) ELSE NULL END AS "initiative_count",
1921 "contingent"."initiative_limit"
1922 FROM "member" CROSS JOIN "contingent";
1924 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
1926 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
1927 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
1930 CREATE VIEW "member_contingent_left" AS
1931 SELECT
1932 "member_id",
1933 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
1934 max("initiative_limit" - "initiative_count") AS "initiatives_left"
1935 FROM "member_contingent" GROUP BY "member_id";
1937 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.';
1940 CREATE TYPE "timeline_event" AS ENUM (
1941 'issue_created',
1942 'issue_canceled',
1943 'issue_accepted',
1944 'issue_half_frozen',
1945 'issue_finished_without_voting',
1946 'issue_voting_started',
1947 'issue_finished_after_voting',
1948 'initiative_created',
1949 'initiative_revoked',
1950 'draft_created',
1951 'suggestion_created');
1953 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)';
1956 CREATE VIEW "timeline_issue" AS
1957 SELECT
1958 "created" AS "occurrence",
1959 'issue_created'::"timeline_event" AS "event",
1960 "id" AS "issue_id"
1961 FROM "issue"
1962 UNION ALL
1963 SELECT
1964 "closed" AS "occurrence",
1965 'issue_canceled'::"timeline_event" AS "event",
1966 "id" AS "issue_id"
1967 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
1968 UNION ALL
1969 SELECT
1970 "accepted" AS "occurrence",
1971 'issue_accepted'::"timeline_event" AS "event",
1972 "id" AS "issue_id"
1973 FROM "issue" WHERE "accepted" NOTNULL
1974 UNION ALL
1975 SELECT
1976 "half_frozen" AS "occurrence",
1977 'issue_half_frozen'::"timeline_event" AS "event",
1978 "id" AS "issue_id"
1979 FROM "issue" WHERE "half_frozen" NOTNULL
1980 UNION ALL
1981 SELECT
1982 "fully_frozen" AS "occurrence",
1983 'issue_voting_started'::"timeline_event" AS "event",
1984 "id" AS "issue_id"
1985 FROM "issue"
1986 WHERE "fully_frozen" NOTNULL
1987 AND ("closed" ISNULL OR "closed" != "fully_frozen")
1988 UNION ALL
1989 SELECT
1990 "closed" AS "occurrence",
1991 CASE WHEN "fully_frozen" = "closed" THEN
1992 'issue_finished_without_voting'::"timeline_event"
1993 ELSE
1994 'issue_finished_after_voting'::"timeline_event"
1995 END AS "event",
1996 "id" AS "issue_id"
1997 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
1999 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)';
2002 CREATE VIEW "timeline_initiative" AS
2003 SELECT
2004 "created" AS "occurrence",
2005 'initiative_created'::"timeline_event" AS "event",
2006 "id" AS "initiative_id"
2007 FROM "initiative"
2008 UNION ALL
2009 SELECT
2010 "revoked" AS "occurrence",
2011 'initiative_revoked'::"timeline_event" AS "event",
2012 "id" AS "initiative_id"
2013 FROM "initiative" WHERE "revoked" NOTNULL;
2015 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)';
2018 CREATE VIEW "timeline_draft" AS
2019 SELECT
2020 "created" AS "occurrence",
2021 'draft_created'::"timeline_event" AS "event",
2022 "id" AS "draft_id"
2023 FROM "draft";
2025 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)';
2028 CREATE VIEW "timeline_suggestion" AS
2029 SELECT
2030 "created" AS "occurrence",
2031 'suggestion_created'::"timeline_event" AS "event",
2032 "id" AS "suggestion_id"
2033 FROM "suggestion";
2035 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)';
2038 CREATE VIEW "timeline" AS
2039 SELECT
2040 "occurrence",
2041 "event",
2042 "issue_id",
2043 NULL AS "initiative_id",
2044 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
2045 NULL::INT8 AS "suggestion_id"
2046 FROM "timeline_issue"
2047 UNION ALL
2048 SELECT
2049 "occurrence",
2050 "event",
2051 NULL AS "issue_id",
2052 "initiative_id",
2053 NULL AS "draft_id",
2054 NULL AS "suggestion_id"
2055 FROM "timeline_initiative"
2056 UNION ALL
2057 SELECT
2058 "occurrence",
2059 "event",
2060 NULL AS "issue_id",
2061 NULL AS "initiative_id",
2062 "draft_id",
2063 NULL AS "suggestion_id"
2064 FROM "timeline_draft"
2065 UNION ALL
2066 SELECT
2067 "occurrence",
2068 "event",
2069 NULL AS "issue_id",
2070 NULL AS "initiative_id",
2071 NULL AS "draft_id",
2072 "suggestion_id"
2073 FROM "timeline_suggestion";
2075 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)';
2079 --------------------------------------------------
2080 -- Set returning function for delegation chains --
2081 --------------------------------------------------
2084 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
2085 ('first', 'intermediate', 'last', 'repetition');
2087 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
2090 CREATE TYPE "delegation_chain_row" AS (
2091 "index" INT4,
2092 "member_id" INT4,
2093 "member_valid" BOOLEAN,
2094 "participation" BOOLEAN,
2095 "overridden" BOOLEAN,
2096 "scope_in" "delegation_scope",
2097 "scope_out" "delegation_scope",
2098 "disabled_out" BOOLEAN,
2099 "loop" "delegation_chain_loop_tag" );
2101 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
2103 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
2104 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';
2105 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
2106 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
2107 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
2108 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
2109 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
2112 CREATE FUNCTION "delegation_chain"
2113 ( "member_id_p" "member"."id"%TYPE,
2114 "unit_id_p" "unit"."id"%TYPE,
2115 "area_id_p" "area"."id"%TYPE,
2116 "issue_id_p" "issue"."id"%TYPE,
2117 "simulate_trustee_id_p" "member"."id"%TYPE )
2118 RETURNS SETOF "delegation_chain_row"
2119 LANGUAGE 'plpgsql' STABLE AS $$
2120 DECLARE
2121 "scope_v" "delegation_scope";
2122 "unit_id_v" "unit"."id"%TYPE;
2123 "area_id_v" "area"."id"%TYPE;
2124 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2125 "loop_member_id_v" "member"."id"%TYPE;
2126 "output_row" "delegation_chain_row";
2127 "output_rows" "delegation_chain_row"[];
2128 "delegation_row" "delegation"%ROWTYPE;
2129 "row_count" INT4;
2130 "i" INT4;
2131 "loop_v" BOOLEAN;
2132 BEGIN
2133 IF
2134 "unit_id_p" NOTNULL AND
2135 "area_id_p" ISNULL AND
2136 "issue_id_p" ISNULL
2137 THEN
2138 "scope_v" := 'unit';
2139 "unit_id_v" := "unit_id_p";
2140 ELSIF
2141 "unit_id_p" ISNULL AND
2142 "area_id_p" NOTNULL AND
2143 "issue_id_p" ISNULL
2144 THEN
2145 "scope_v" := 'area';
2146 "area_id_v" := "area_id_p";
2147 SELECT "unit_id" INTO "unit_id_v"
2148 FROM "area" WHERE "id" = "area_id_v";
2149 ELSIF
2150 "unit_id_p" ISNULL AND
2151 "area_id_p" ISNULL AND
2152 "issue_id_p" NOTNULL
2153 THEN
2154 "scope_v" := 'issue';
2155 SELECT "area_id" INTO "area_id_v"
2156 FROM "issue" WHERE "id" = "issue_id_p";
2157 SELECT "unit_id" INTO "unit_id_v"
2158 FROM "area" WHERE "id" = "area_id_v";
2159 ELSE
2160 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2161 END IF;
2162 "visited_member_ids" := '{}';
2163 "loop_member_id_v" := NULL;
2164 "output_rows" := '{}';
2165 "output_row"."index" := 0;
2166 "output_row"."member_id" := "member_id_p";
2167 "output_row"."member_valid" := TRUE;
2168 "output_row"."participation" := FALSE;
2169 "output_row"."overridden" := FALSE;
2170 "output_row"."disabled_out" := FALSE;
2171 "output_row"."scope_out" := NULL;
2172 LOOP
2173 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2174 "loop_member_id_v" := "output_row"."member_id";
2175 ELSE
2176 "visited_member_ids" :=
2177 "visited_member_ids" || "output_row"."member_id";
2178 END IF;
2179 IF "output_row"."participation" THEN
2180 "output_row"."overridden" := TRUE;
2181 END IF;
2182 "output_row"."scope_in" := "output_row"."scope_out";
2183 IF EXISTS (
2184 SELECT NULL FROM "member" JOIN "privilege"
2185 ON "privilege"."member_id" = "member"."id"
2186 AND "privilege"."unit_id" = "unit_id_v"
2187 WHERE "id" = "output_row"."member_id"
2188 AND "member"."active" AND "privilege"."voting_right"
2189 ) THEN
2190 IF "scope_v" = 'unit' THEN
2191 SELECT * INTO "delegation_row" FROM "delegation"
2192 WHERE "truster_id" = "output_row"."member_id"
2193 AND "unit_id" = "unit_id_v";
2194 ELSIF "scope_v" = 'area' THEN
2195 "output_row"."participation" := EXISTS (
2196 SELECT NULL FROM "membership"
2197 WHERE "area_id" = "area_id_p"
2198 AND "member_id" = "output_row"."member_id"
2199 );
2200 SELECT * INTO "delegation_row" FROM "delegation"
2201 WHERE "truster_id" = "output_row"."member_id"
2202 AND (
2203 "unit_id" = "unit_id_v" OR
2204 "area_id" = "area_id_v"
2205 )
2206 ORDER BY "scope" DESC;
2207 ELSIF "scope_v" = 'issue' THEN
2208 "output_row"."participation" := EXISTS (
2209 SELECT NULL FROM "interest"
2210 WHERE "issue_id" = "issue_id_p"
2211 AND "member_id" = "output_row"."member_id"
2212 );
2213 SELECT * INTO "delegation_row" FROM "delegation"
2214 WHERE "truster_id" = "output_row"."member_id"
2215 AND (
2216 "unit_id" = "unit_id_v" OR
2217 "area_id" = "area_id_v" OR
2218 "issue_id" = "issue_id_p"
2219 )
2220 ORDER BY "scope" DESC;
2221 END IF;
2222 ELSE
2223 "output_row"."member_valid" := FALSE;
2224 "output_row"."participation" := FALSE;
2225 "output_row"."scope_out" := NULL;
2226 "delegation_row" := ROW(NULL);
2227 END IF;
2228 IF
2229 "output_row"."member_id" = "member_id_p" AND
2230 "simulate_trustee_id_p" NOTNULL
2231 THEN
2232 "output_row"."scope_out" := "scope_v";
2233 "output_rows" := "output_rows" || "output_row";
2234 "output_row"."member_id" := "simulate_trustee_id_p";
2235 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2236 "output_row"."scope_out" := "delegation_row"."scope";
2237 "output_rows" := "output_rows" || "output_row";
2238 "output_row"."member_id" := "delegation_row"."trustee_id";
2239 ELSIF "delegation_row"."scope" NOTNULL THEN
2240 "output_row"."scope_out" := "delegation_row"."scope";
2241 "output_row"."disabled_out" := TRUE;
2242 "output_rows" := "output_rows" || "output_row";
2243 EXIT;
2244 ELSE
2245 "output_row"."scope_out" := NULL;
2246 "output_rows" := "output_rows" || "output_row";
2247 EXIT;
2248 END IF;
2249 EXIT WHEN "loop_member_id_v" NOTNULL;
2250 "output_row"."index" := "output_row"."index" + 1;
2251 END LOOP;
2252 "row_count" := array_upper("output_rows", 1);
2253 "i" := 1;
2254 "loop_v" := FALSE;
2255 LOOP
2256 "output_row" := "output_rows"["i"];
2257 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2258 IF "loop_v" THEN
2259 IF "i" + 1 = "row_count" THEN
2260 "output_row"."loop" := 'last';
2261 ELSIF "i" = "row_count" THEN
2262 "output_row"."loop" := 'repetition';
2263 ELSE
2264 "output_row"."loop" := 'intermediate';
2265 END IF;
2266 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2267 "output_row"."loop" := 'first';
2268 "loop_v" := TRUE;
2269 END IF;
2270 IF "scope_v" = 'unit' THEN
2271 "output_row"."participation" := NULL;
2272 END IF;
2273 RETURN NEXT "output_row";
2274 "i" := "i" + 1;
2275 END LOOP;
2276 RETURN;
2277 END;
2278 $$;
2280 COMMENT ON FUNCTION "delegation_chain"
2281 ( "member"."id"%TYPE,
2282 "unit"."id"%TYPE,
2283 "area"."id"%TYPE,
2284 "issue"."id"%TYPE,
2285 "member"."id"%TYPE )
2286 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
2289 CREATE FUNCTION "delegation_chain"
2290 ( "member_id_p" "member"."id"%TYPE,
2291 "unit_id_p" "unit"."id"%TYPE,
2292 "area_id_p" "area"."id"%TYPE,
2293 "issue_id_p" "issue"."id"%TYPE )
2294 RETURNS SETOF "delegation_chain_row"
2295 LANGUAGE 'plpgsql' STABLE AS $$
2296 DECLARE
2297 "result_row" "delegation_chain_row";
2298 BEGIN
2299 FOR "result_row" IN
2300 SELECT * FROM "delegation_chain"(
2301 "member_id_p", "area_id_p", "issue_id_p", NULL
2302 )
2303 LOOP
2304 RETURN NEXT "result_row";
2305 END LOOP;
2306 RETURN;
2307 END;
2308 $$;
2310 COMMENT ON FUNCTION "delegation_chain"
2311 ( "member"."id"%TYPE,
2312 "unit"."id"%TYPE,
2313 "area"."id"%TYPE,
2314 "issue"."id"%TYPE )
2315 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
2319 ------------------------------
2320 -- Comparison by vote count --
2321 ------------------------------
2323 CREATE FUNCTION "vote_ratio"
2324 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
2325 "negative_votes_p" "initiative"."negative_votes"%TYPE )
2326 RETURNS FLOAT8
2327 LANGUAGE 'plpgsql' STABLE AS $$
2328 BEGIN
2329 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
2330 RETURN
2331 "positive_votes_p"::FLOAT8 /
2332 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
2333 ELSIF "positive_votes_p" > 0 THEN
2334 RETURN "positive_votes_p";
2335 ELSIF "negative_votes_p" > 0 THEN
2336 RETURN 1 - "negative_votes_p";
2337 ELSE
2338 RETURN 0.5;
2339 END IF;
2340 END;
2341 $$;
2343 COMMENT ON FUNCTION "vote_ratio"
2344 ( "initiative"."positive_votes"%TYPE,
2345 "initiative"."negative_votes"%TYPE )
2346 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.';
2350 ------------------------------------------------
2351 -- Locking for snapshots and voting procedure --
2352 ------------------------------------------------
2355 CREATE FUNCTION "share_row_lock_issue_trigger"()
2356 RETURNS TRIGGER
2357 LANGUAGE 'plpgsql' VOLATILE AS $$
2358 BEGIN
2359 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2360 PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;
2361 END IF;
2362 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2363 PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;
2364 RETURN NEW;
2365 ELSE
2366 RETURN OLD;
2367 END IF;
2368 END;
2369 $$;
2371 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';
2374 CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()
2375 RETURNS TRIGGER
2376 LANGUAGE 'plpgsql' VOLATILE AS $$
2377 BEGIN
2378 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2379 PERFORM NULL FROM "issue"
2380 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2381 WHERE "initiative"."id" = OLD."initiative_id"
2382 FOR SHARE OF "issue";
2383 END IF;
2384 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2385 PERFORM NULL FROM "issue"
2386 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2387 WHERE "initiative"."id" = NEW."initiative_id"
2388 FOR SHARE OF "issue";
2389 RETURN NEW;
2390 ELSE
2391 RETURN OLD;
2392 END IF;
2393 END;
2394 $$;
2396 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';
2399 CREATE TRIGGER "share_row_lock_issue"
2400 BEFORE INSERT OR UPDATE OR DELETE ON "initiative"
2401 FOR EACH ROW EXECUTE PROCEDURE
2402 "share_row_lock_issue_trigger"();
2404 CREATE TRIGGER "share_row_lock_issue"
2405 BEFORE INSERT OR UPDATE OR DELETE ON "interest"
2406 FOR EACH ROW EXECUTE PROCEDURE
2407 "share_row_lock_issue_trigger"();
2409 CREATE TRIGGER "share_row_lock_issue"
2410 BEFORE INSERT OR UPDATE OR DELETE ON "supporter"
2411 FOR EACH ROW EXECUTE PROCEDURE
2412 "share_row_lock_issue_trigger"();
2414 CREATE TRIGGER "share_row_lock_issue_via_initiative"
2415 BEFORE INSERT OR UPDATE OR DELETE ON "opinion"
2416 FOR EACH ROW EXECUTE PROCEDURE
2417 "share_row_lock_issue_via_initiative_trigger"();
2419 CREATE TRIGGER "share_row_lock_issue"
2420 BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter"
2421 FOR EACH ROW EXECUTE PROCEDURE
2422 "share_row_lock_issue_trigger"();
2424 CREATE TRIGGER "share_row_lock_issue"
2425 BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter"
2426 FOR EACH ROW EXECUTE PROCEDURE
2427 "share_row_lock_issue_trigger"();
2429 CREATE TRIGGER "share_row_lock_issue"
2430 BEFORE INSERT OR UPDATE OR DELETE ON "vote"
2431 FOR EACH ROW EXECUTE PROCEDURE
2432 "share_row_lock_issue_trigger"();
2434 COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function';
2435 COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function';
2436 COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function';
2437 COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function';
2438 COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function';
2439 COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function';
2440 COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function';
2443 CREATE FUNCTION "lock_issue"
2444 ( "issue_id_p" "issue"."id"%TYPE )
2445 RETURNS VOID
2446 LANGUAGE 'plpgsql' VOLATILE AS $$
2447 BEGIN
2448 LOCK TABLE "member" IN SHARE MODE;
2449 LOCK TABLE "privilege" IN SHARE MODE;
2450 LOCK TABLE "membership" IN SHARE MODE;
2451 LOCK TABLE "policy" IN SHARE MODE;
2452 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2453 -- NOTE: The row-level exclusive lock in combination with the
2454 -- share_row_lock_issue(_via_initiative)_trigger functions (which
2455 -- acquire a row-level share lock on the issue) ensure that no data
2456 -- is changed, which could affect calculation of snapshots or
2457 -- counting of votes. Table "delegation" must be table-level-locked,
2458 -- as it also contains issue- and global-scope delegations.
2459 LOCK TABLE "delegation" IN SHARE MODE;
2460 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
2461 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
2462 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
2463 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
2464 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
2465 RETURN;
2466 END;
2467 $$;
2469 COMMENT ON FUNCTION "lock_issue"
2470 ( "issue"."id"%TYPE )
2471 IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';
2475 ------------------------------------------------------------------------
2476 -- Regular tasks, except calculcation of snapshots and voting results --
2477 ------------------------------------------------------------------------
2479 CREATE FUNCTION "check_last_login"()
2480 RETURNS VOID
2481 LANGUAGE 'plpgsql' VOLATILE AS $$
2482 DECLARE
2483 "system_setting_row" "system_setting"%ROWTYPE;
2484 BEGIN
2485 SELECT * INTO "system_setting_row" FROM "system_setting";
2486 LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
2487 UPDATE "member" SET "last_login_public" = "last_login"::date
2488 FROM (
2489 SELECT DISTINCT "member"."id"
2490 FROM "member" LEFT JOIN "member_history"
2491 ON "member"."id" = "member_history"."member_id"
2492 WHERE "member"."last_login"::date < 'today' OR (
2493 "member_history"."until"::date >= 'today' AND
2494 "member_history"."active" = FALSE AND "member"."active" = TRUE
2495 )
2496 ) AS "subquery"
2497 WHERE "member"."id" = "subquery"."id";
2498 IF "system_setting_row"."member_ttl" NOTNULL THEN
2499 UPDATE "member" SET "active" = FALSE
2500 WHERE "active" = TRUE
2501 AND "last_login"::date < 'today'
2502 AND "last_login_public" <
2503 (now() - "system_setting_row"."member_ttl")::date;
2504 END IF;
2505 RETURN;
2506 END;
2507 $$;
2509 COMMENT ON FUNCTION "check_last_login"() IS 'Updates "last_login_public" field, which contains the date but not the time of the last login, and deactivates members who do not login for the time specified in "system_setting"."member_ttl". For privacy reasons this function does not update "last_login_public", if the last login of a member has been today (except when member was reactivated today).';
2512 CREATE FUNCTION "calculate_member_counts"()
2513 RETURNS VOID
2514 LANGUAGE 'plpgsql' VOLATILE AS $$
2515 BEGIN
2516 LOCK TABLE "member" IN SHARE MODE;
2517 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
2518 LOCK TABLE "unit" IN EXCLUSIVE MODE;
2519 LOCK TABLE "area" IN EXCLUSIVE MODE;
2520 LOCK TABLE "privilege" IN SHARE MODE;
2521 LOCK TABLE "membership" IN SHARE MODE;
2522 DELETE FROM "member_count";
2523 INSERT INTO "member_count" ("total_count")
2524 SELECT "total_count" FROM "member_count_view";
2525 UPDATE "unit" SET "member_count" = "view"."member_count"
2526 FROM "unit_member_count" AS "view"
2527 WHERE "view"."unit_id" = "unit"."id";
2528 UPDATE "area" SET
2529 "direct_member_count" = "view"."direct_member_count",
2530 "member_weight" = "view"."member_weight",
2531 "autoreject_weight" = "view"."autoreject_weight"
2532 FROM "area_member_count" AS "view"
2533 WHERE "view"."area_id" = "area"."id";
2534 RETURN;
2535 END;
2536 $$;
2538 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"';
2542 ------------------------------
2543 -- Calculation of snapshots --
2544 ------------------------------
2546 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
2547 ( "issue_id_p" "issue"."id"%TYPE,
2548 "member_id_p" "member"."id"%TYPE,
2549 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2550 RETURNS "direct_population_snapshot"."weight"%TYPE
2551 LANGUAGE 'plpgsql' VOLATILE AS $$
2552 DECLARE
2553 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2554 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
2555 "weight_v" INT4;
2556 "sub_weight_v" INT4;
2557 BEGIN
2558 "weight_v" := 0;
2559 FOR "issue_delegation_row" IN
2560 SELECT * FROM "issue_delegation"
2561 WHERE "trustee_id" = "member_id_p"
2562 AND "issue_id" = "issue_id_p"
2563 LOOP
2564 IF NOT EXISTS (
2565 SELECT NULL FROM "direct_population_snapshot"
2566 WHERE "issue_id" = "issue_id_p"
2567 AND "event" = 'periodic'
2568 AND "member_id" = "issue_delegation_row"."truster_id"
2569 ) AND NOT EXISTS (
2570 SELECT NULL FROM "delegating_population_snapshot"
2571 WHERE "issue_id" = "issue_id_p"
2572 AND "event" = 'periodic'
2573 AND "member_id" = "issue_delegation_row"."truster_id"
2574 ) THEN
2575 "delegate_member_ids_v" :=
2576 "member_id_p" || "delegate_member_ids_p";
2577 INSERT INTO "delegating_population_snapshot" (
2578 "issue_id",
2579 "event",
2580 "member_id",
2581 "scope",
2582 "delegate_member_ids"
2583 ) VALUES (
2584 "issue_id_p",
2585 'periodic',
2586 "issue_delegation_row"."truster_id",
2587 "issue_delegation_row"."scope",
2588 "delegate_member_ids_v"
2589 );
2590 "sub_weight_v" := 1 +
2591 "weight_of_added_delegations_for_population_snapshot"(
2592 "issue_id_p",
2593 "issue_delegation_row"."truster_id",
2594 "delegate_member_ids_v"
2595 );
2596 UPDATE "delegating_population_snapshot"
2597 SET "weight" = "sub_weight_v"
2598 WHERE "issue_id" = "issue_id_p"
2599 AND "event" = 'periodic'
2600 AND "member_id" = "issue_delegation_row"."truster_id";
2601 "weight_v" := "weight_v" + "sub_weight_v";
2602 END IF;
2603 END LOOP;
2604 RETURN "weight_v";
2605 END;
2606 $$;
2608 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
2609 ( "issue"."id"%TYPE,
2610 "member"."id"%TYPE,
2611 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2612 IS 'Helper function for "create_population_snapshot" function';
2615 CREATE FUNCTION "create_population_snapshot"
2616 ( "issue_id_p" "issue"."id"%TYPE )
2617 RETURNS VOID
2618 LANGUAGE 'plpgsql' VOLATILE AS $$
2619 DECLARE
2620 "member_id_v" "member"."id"%TYPE;
2621 BEGIN
2622 DELETE FROM "direct_population_snapshot"
2623 WHERE "issue_id" = "issue_id_p"
2624 AND "event" = 'periodic';
2625 DELETE FROM "delegating_population_snapshot"
2626 WHERE "issue_id" = "issue_id_p"
2627 AND "event" = 'periodic';
2628 INSERT INTO "direct_population_snapshot"
2629 ("issue_id", "event", "member_id")
2630 SELECT
2631 "issue_id_p" AS "issue_id",
2632 'periodic'::"snapshot_event" AS "event",
2633 "member"."id" AS "member_id"
2634 FROM "issue"
2635 JOIN "area" ON "issue"."area_id" = "area"."id"
2636 JOIN "membership" ON "area"."id" = "membership"."area_id"
2637 JOIN "member" ON "membership"."member_id" = "member"."id"
2638 JOIN "privilege"
2639 ON "privilege"."unit_id" = "area"."unit_id"
2640 AND "privilege"."member_id" = "member"."id"
2641 WHERE "issue"."id" = "issue_id_p"
2642 AND "member"."active" AND "privilege"."voting_right"
2643 UNION
2644 SELECT
2645 "issue_id_p" AS "issue_id",
2646 'periodic'::"snapshot_event" AS "event",
2647 "member"."id" AS "member_id"
2648 FROM "issue"
2649 JOIN "area" ON "issue"."area_id" = "area"."id"
2650 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2651 JOIN "member" ON "interest"."member_id" = "member"."id"
2652 JOIN "privilege"
2653 ON "privilege"."unit_id" = "area"."unit_id"
2654 AND "privilege"."member_id" = "member"."id"
2655 WHERE "issue"."id" = "issue_id_p"
2656 AND "member"."active" AND "privilege"."voting_right";
2657 FOR "member_id_v" IN
2658 SELECT "member_id" FROM "direct_population_snapshot"
2659 WHERE "issue_id" = "issue_id_p"
2660 AND "event" = 'periodic'
2661 LOOP
2662 UPDATE "direct_population_snapshot" SET
2663 "weight" = 1 +
2664 "weight_of_added_delegations_for_population_snapshot"(
2665 "issue_id_p",
2666 "member_id_v",
2667 '{}'
2668 )
2669 WHERE "issue_id" = "issue_id_p"
2670 AND "event" = 'periodic'
2671 AND "member_id" = "member_id_v";
2672 END LOOP;
2673 RETURN;
2674 END;
2675 $$;
2677 COMMENT ON FUNCTION "create_population_snapshot"
2678 ( "issue"."id"%TYPE )
2679 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.';
2682 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2683 ( "issue_id_p" "issue"."id"%TYPE,
2684 "member_id_p" "member"."id"%TYPE,
2685 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2686 RETURNS "direct_interest_snapshot"."weight"%TYPE
2687 LANGUAGE 'plpgsql' VOLATILE AS $$
2688 DECLARE
2689 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2690 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2691 "weight_v" INT4;
2692 "sub_weight_v" INT4;
2693 BEGIN
2694 "weight_v" := 0;
2695 FOR "issue_delegation_row" IN
2696 SELECT * FROM "issue_delegation"
2697 WHERE "trustee_id" = "member_id_p"
2698 AND "issue_id" = "issue_id_p"
2699 LOOP
2700 IF NOT EXISTS (
2701 SELECT NULL FROM "direct_interest_snapshot"
2702 WHERE "issue_id" = "issue_id_p"
2703 AND "event" = 'periodic'
2704 AND "member_id" = "issue_delegation_row"."truster_id"
2705 ) AND NOT EXISTS (
2706 SELECT NULL FROM "delegating_interest_snapshot"
2707 WHERE "issue_id" = "issue_id_p"
2708 AND "event" = 'periodic'
2709 AND "member_id" = "issue_delegation_row"."truster_id"
2710 ) THEN
2711 "delegate_member_ids_v" :=
2712 "member_id_p" || "delegate_member_ids_p";
2713 INSERT INTO "delegating_interest_snapshot" (
2714 "issue_id",
2715 "event",
2716 "member_id",
2717 "scope",
2718 "delegate_member_ids"
2719 ) VALUES (
2720 "issue_id_p",
2721 'periodic',
2722 "issue_delegation_row"."truster_id",
2723 "issue_delegation_row"."scope",
2724 "delegate_member_ids_v"
2725 );
2726 "sub_weight_v" := 1 +
2727 "weight_of_added_delegations_for_interest_snapshot"(
2728 "issue_id_p",
2729 "issue_delegation_row"."truster_id",
2730 "delegate_member_ids_v"
2731 );
2732 UPDATE "delegating_interest_snapshot"
2733 SET "weight" = "sub_weight_v"
2734 WHERE "issue_id" = "issue_id_p"
2735 AND "event" = 'periodic'
2736 AND "member_id" = "issue_delegation_row"."truster_id";
2737 "weight_v" := "weight_v" + "sub_weight_v";
2738 END IF;
2739 END LOOP;
2740 RETURN "weight_v";
2741 END;
2742 $$;
2744 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2745 ( "issue"."id"%TYPE,
2746 "member"."id"%TYPE,
2747 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2748 IS 'Helper function for "create_interest_snapshot" function';
2751 CREATE FUNCTION "create_interest_snapshot"
2752 ( "issue_id_p" "issue"."id"%TYPE )
2753 RETURNS VOID
2754 LANGUAGE 'plpgsql' VOLATILE AS $$
2755 DECLARE
2756 "member_id_v" "member"."id"%TYPE;
2757 BEGIN
2758 DELETE FROM "direct_interest_snapshot"
2759 WHERE "issue_id" = "issue_id_p"
2760 AND "event" = 'periodic';
2761 DELETE FROM "delegating_interest_snapshot"
2762 WHERE "issue_id" = "issue_id_p"
2763 AND "event" = 'periodic';
2764 DELETE FROM "direct_supporter_snapshot"
2765 WHERE "issue_id" = "issue_id_p"
2766 AND "event" = 'periodic';
2767 INSERT INTO "direct_interest_snapshot"
2768 ("issue_id", "event", "member_id", "voting_requested")
2769 SELECT
2770 "issue_id_p" AS "issue_id",
2771 'periodic' AS "event",
2772 "member"."id" AS "member_id",
2773 "interest"."voting_requested"
2774 FROM "issue"
2775 JOIN "area" ON "issue"."area_id" = "area"."id"
2776 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2777 JOIN "member" ON "interest"."member_id" = "member"."id"
2778 JOIN "privilege"
2779 ON "privilege"."unit_id" = "area"."unit_id"
2780 AND "privilege"."member_id" = "member"."id"
2781 WHERE "issue"."id" = "issue_id_p"
2782 AND "member"."active" AND "privilege"."voting_right";
2783 FOR "member_id_v" IN
2784 SELECT "member_id" FROM "direct_interest_snapshot"
2785 WHERE "issue_id" = "issue_id_p"
2786 AND "event" = 'periodic'
2787 LOOP
2788 UPDATE "direct_interest_snapshot" SET
2789 "weight" = 1 +
2790 "weight_of_added_delegations_for_interest_snapshot"(
2791 "issue_id_p",
2792 "member_id_v",
2793 '{}'
2794 )
2795 WHERE "issue_id" = "issue_id_p"
2796 AND "event" = 'periodic'
2797 AND "member_id" = "member_id_v";
2798 END LOOP;
2799 INSERT INTO "direct_supporter_snapshot"
2800 ( "issue_id", "initiative_id", "event", "member_id",
2801 "informed", "satisfied" )
2802 SELECT
2803 "issue_id_p" AS "issue_id",
2804 "initiative"."id" AS "initiative_id",
2805 'periodic' AS "event",
2806 "supporter"."member_id" AS "member_id",
2807 "supporter"."draft_id" = "current_draft"."id" AS "informed",
2808 NOT EXISTS (
2809 SELECT NULL FROM "critical_opinion"
2810 WHERE "initiative_id" = "initiative"."id"
2811 AND "member_id" = "supporter"."member_id"
2812 ) AS "satisfied"
2813 FROM "initiative"
2814 JOIN "supporter"
2815 ON "supporter"."initiative_id" = "initiative"."id"
2816 JOIN "current_draft"
2817 ON "initiative"."id" = "current_draft"."initiative_id"
2818 JOIN "direct_interest_snapshot"
2819 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
2820 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
2821 AND "event" = 'periodic'
2822 WHERE "initiative"."issue_id" = "issue_id_p";
2823 RETURN;
2824 END;
2825 $$;
2827 COMMENT ON FUNCTION "create_interest_snapshot"
2828 ( "issue"."id"%TYPE )
2829 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.';
2832 CREATE FUNCTION "create_snapshot"
2833 ( "issue_id_p" "issue"."id"%TYPE )
2834 RETURNS VOID
2835 LANGUAGE 'plpgsql' VOLATILE AS $$
2836 DECLARE
2837 "initiative_id_v" "initiative"."id"%TYPE;
2838 "suggestion_id_v" "suggestion"."id"%TYPE;
2839 BEGIN
2840 PERFORM "lock_issue"("issue_id_p");
2841 PERFORM "create_population_snapshot"("issue_id_p");
2842 PERFORM "create_interest_snapshot"("issue_id_p");
2843 UPDATE "issue" SET
2844 "snapshot" = now(),
2845 "latest_snapshot_event" = 'periodic',
2846 "population" = (
2847 SELECT coalesce(sum("weight"), 0)
2848 FROM "direct_population_snapshot"
2849 WHERE "issue_id" = "issue_id_p"
2850 AND "event" = 'periodic'
2851 ),
2852 "vote_now" = (
2853 SELECT coalesce(sum("weight"), 0)
2854 FROM "direct_interest_snapshot"
2855 WHERE "issue_id" = "issue_id_p"
2856 AND "event" = 'periodic'
2857 AND "voting_requested" = TRUE
2858 ),
2859 "vote_later" = (
2860 SELECT coalesce(sum("weight"), 0)
2861 FROM "direct_interest_snapshot"
2862 WHERE "issue_id" = "issue_id_p"
2863 AND "event" = 'periodic'
2864 AND "voting_requested" = FALSE
2865 )
2866 WHERE "id" = "issue_id_p";
2867 FOR "initiative_id_v" IN
2868 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
2869 LOOP
2870 UPDATE "initiative" SET
2871 "supporter_count" = (
2872 SELECT coalesce(sum("di"."weight"), 0)
2873 FROM "direct_interest_snapshot" AS "di"
2874 JOIN "direct_supporter_snapshot" AS "ds"
2875 ON "di"."member_id" = "ds"."member_id"
2876 WHERE "di"."issue_id" = "issue_id_p"
2877 AND "di"."event" = 'periodic'
2878 AND "ds"."initiative_id" = "initiative_id_v"
2879 AND "ds"."event" = 'periodic'
2880 ),
2881 "informed_supporter_count" = (
2882 SELECT coalesce(sum("di"."weight"), 0)
2883 FROM "direct_interest_snapshot" AS "di"
2884 JOIN "direct_supporter_snapshot" AS "ds"
2885 ON "di"."member_id" = "ds"."member_id"
2886 WHERE "di"."issue_id" = "issue_id_p"
2887 AND "di"."event" = 'periodic'
2888 AND "ds"."initiative_id" = "initiative_id_v"
2889 AND "ds"."event" = 'periodic'
2890 AND "ds"."informed"
2891 ),
2892 "satisfied_supporter_count" = (
2893 SELECT coalesce(sum("di"."weight"), 0)
2894 FROM "direct_interest_snapshot" AS "di"
2895 JOIN "direct_supporter_snapshot" AS "ds"
2896 ON "di"."member_id" = "ds"."member_id"
2897 WHERE "di"."issue_id" = "issue_id_p"
2898 AND "di"."event" = 'periodic'
2899 AND "ds"."initiative_id" = "initiative_id_v"
2900 AND "ds"."event" = 'periodic'
2901 AND "ds"."satisfied"
2902 ),
2903 "satisfied_informed_supporter_count" = (
2904 SELECT coalesce(sum("di"."weight"), 0)
2905 FROM "direct_interest_snapshot" AS "di"
2906 JOIN "direct_supporter_snapshot" AS "ds"
2907 ON "di"."member_id" = "ds"."member_id"
2908 WHERE "di"."issue_id" = "issue_id_p"
2909 AND "di"."event" = 'periodic'
2910 AND "ds"."initiative_id" = "initiative_id_v"
2911 AND "ds"."event" = 'periodic'
2912 AND "ds"."informed"
2913 AND "ds"."satisfied"
2914 )
2915 WHERE "id" = "initiative_id_v";
2916 FOR "suggestion_id_v" IN
2917 SELECT "id" FROM "suggestion"
2918 WHERE "initiative_id" = "initiative_id_v"
2919 LOOP
2920 UPDATE "suggestion" SET
2921 "minus2_unfulfilled_count" = (
2922 SELECT coalesce(sum("snapshot"."weight"), 0)
2923 FROM "issue" CROSS JOIN "opinion"
2924 JOIN "direct_interest_snapshot" AS "snapshot"
2925 ON "snapshot"."issue_id" = "issue"."id"
2926 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2927 AND "snapshot"."member_id" = "opinion"."member_id"
2928 WHERE "issue"."id" = "issue_id_p"
2929 AND "opinion"."suggestion_id" = "suggestion_id_v"
2930 AND "opinion"."degree" = -2
2931 AND "opinion"."fulfilled" = FALSE
2932 ),
2933 "minus2_fulfilled_count" = (
2934 SELECT coalesce(sum("snapshot"."weight"), 0)
2935 FROM "issue" CROSS JOIN "opinion"
2936 JOIN "direct_interest_snapshot" AS "snapshot"
2937 ON "snapshot"."issue_id" = "issue"."id"
2938 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2939 AND "snapshot"."member_id" = "opinion"."member_id"
2940 WHERE "issue"."id" = "issue_id_p"
2941 AND "opinion"."suggestion_id" = "suggestion_id_v"
2942 AND "opinion"."degree" = -2
2943 AND "opinion"."fulfilled" = TRUE
2944 ),
2945 "minus1_unfulfilled_count" = (
2946 SELECT coalesce(sum("snapshot"."weight"), 0)
2947 FROM "issue" CROSS JOIN "opinion"
2948 JOIN "direct_interest_snapshot" AS "snapshot"
2949 ON "snapshot"."issue_id" = "issue"."id"
2950 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2951 AND "snapshot"."member_id" = "opinion"."member_id"
2952 WHERE "issue"."id" = "issue_id_p"
2953 AND "opinion"."suggestion_id" = "suggestion_id_v"
2954 AND "opinion"."degree" = -1
2955 AND "opinion"."fulfilled" = FALSE
2956 ),
2957 "minus1_fulfilled_count" = (
2958 SELECT coalesce(sum("snapshot"."weight"), 0)
2959 FROM "issue" CROSS JOIN "opinion"
2960 JOIN "direct_interest_snapshot" AS "snapshot"
2961 ON "snapshot"."issue_id" = "issue"."id"
2962 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2963 AND "snapshot"."member_id" = "opinion"."member_id"
2964 WHERE "issue"."id" = "issue_id_p"
2965 AND "opinion"."suggestion_id" = "suggestion_id_v"
2966 AND "opinion"."degree" = -1
2967 AND "opinion"."fulfilled" = TRUE
2968 ),
2969 "plus1_unfulfilled_count" = (
2970 SELECT coalesce(sum("snapshot"."weight"), 0)
2971 FROM "issue" CROSS JOIN "opinion"
2972 JOIN "direct_interest_snapshot" AS "snapshot"
2973 ON "snapshot"."issue_id" = "issue"."id"
2974 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2975 AND "snapshot"."member_id" = "opinion"."member_id"
2976 WHERE "issue"."id" = "issue_id_p"
2977 AND "opinion"."suggestion_id" = "suggestion_id_v"
2978 AND "opinion"."degree" = 1
2979 AND "opinion"."fulfilled" = FALSE
2980 ),
2981 "plus1_fulfilled_count" = (
2982 SELECT coalesce(sum("snapshot"."weight"), 0)
2983 FROM "issue" CROSS JOIN "opinion"
2984 JOIN "direct_interest_snapshot" AS "snapshot"
2985 ON "snapshot"."issue_id" = "issue"."id"
2986 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2987 AND "snapshot"."member_id" = "opinion"."member_id"
2988 WHERE "issue"."id" = "issue_id_p"
2989 AND "opinion"."suggestion_id" = "suggestion_id_v"
2990 AND "opinion"."degree" = 1
2991 AND "opinion"."fulfilled" = TRUE
2992 ),
2993 "plus2_unfulfilled_count" = (
2994 SELECT coalesce(sum("snapshot"."weight"), 0)
2995 FROM "issue" CROSS JOIN "opinion"
2996 JOIN "direct_interest_snapshot" AS "snapshot"
2997 ON "snapshot"."issue_id" = "issue"."id"
2998 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2999 AND "snapshot"."member_id" = "opinion"."member_id"
3000 WHERE "issue"."id" = "issue_id_p"
3001 AND "opinion"."suggestion_id" = "suggestion_id_v"
3002 AND "opinion"."degree" = 2
3003 AND "opinion"."fulfilled" = FALSE
3004 ),
3005 "plus2_fulfilled_count" = (
3006 SELECT coalesce(sum("snapshot"."weight"), 0)
3007 FROM "issue" CROSS JOIN "opinion"
3008 JOIN "direct_interest_snapshot" AS "snapshot"
3009 ON "snapshot"."issue_id" = "issue"."id"
3010 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3011 AND "snapshot"."member_id" = "opinion"."member_id"
3012 WHERE "issue"."id" = "issue_id_p"
3013 AND "opinion"."suggestion_id" = "suggestion_id_v"
3014 AND "opinion"."degree" = 2
3015 AND "opinion"."fulfilled" = TRUE
3016 )
3017 WHERE "suggestion"."id" = "suggestion_id_v";
3018 END LOOP;
3019 END LOOP;
3020 RETURN;
3021 END;
3022 $$;
3024 COMMENT ON FUNCTION "create_snapshot"
3025 ( "issue"."id"%TYPE )
3026 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.';
3029 CREATE FUNCTION "set_snapshot_event"
3030 ( "issue_id_p" "issue"."id"%TYPE,
3031 "event_p" "snapshot_event" )
3032 RETURNS VOID
3033 LANGUAGE 'plpgsql' VOLATILE AS $$
3034 DECLARE
3035 "event_v" "issue"."latest_snapshot_event"%TYPE;
3036 BEGIN
3037 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
3038 WHERE "id" = "issue_id_p" FOR UPDATE;
3039 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
3040 WHERE "id" = "issue_id_p";
3041 UPDATE "direct_population_snapshot" SET "event" = "event_p"
3042 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3043 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
3044 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3045 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
3046 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3047 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
3048 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3049 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
3050 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3051 RETURN;
3052 END;
3053 $$;
3055 COMMENT ON FUNCTION "set_snapshot_event"
3056 ( "issue"."id"%TYPE,
3057 "snapshot_event" )
3058 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
3062 ---------------------
3063 -- Freezing issues --
3064 ---------------------
3066 CREATE FUNCTION "freeze_after_snapshot"
3067 ( "issue_id_p" "issue"."id"%TYPE )
3068 RETURNS VOID
3069 LANGUAGE 'plpgsql' VOLATILE AS $$
3070 DECLARE
3071 "issue_row" "issue"%ROWTYPE;
3072 "policy_row" "policy"%ROWTYPE;
3073 "initiative_row" "initiative"%ROWTYPE;
3074 BEGIN
3075 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3076 SELECT * INTO "policy_row"
3077 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3078 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
3079 UPDATE "issue" SET
3080 "state" = 'voting',
3081 "accepted" = coalesce("accepted", now()),
3082 "half_frozen" = coalesce("half_frozen", now()),
3083 "fully_frozen" = now()
3084 WHERE "id" = "issue_id_p";
3085 FOR "initiative_row" IN
3086 SELECT * FROM "initiative"
3087 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3088 LOOP
3089 IF
3090 "initiative_row"."satisfied_supporter_count" > 0 AND
3091 "initiative_row"."satisfied_supporter_count" *
3092 "policy_row"."initiative_quorum_den" >=
3093 "issue_row"."population" * "policy_row"."initiative_quorum_num"
3094 THEN
3095 UPDATE "initiative" SET "admitted" = TRUE
3096 WHERE "id" = "initiative_row"."id";
3097 ELSE
3098 UPDATE "initiative" SET "admitted" = FALSE
3099 WHERE "id" = "initiative_row"."id";
3100 END IF;
3101 END LOOP;
3102 IF NOT EXISTS (
3103 SELECT NULL FROM "initiative"
3104 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
3105 ) THEN
3106 PERFORM "close_voting"("issue_id_p");
3107 END IF;
3108 RETURN;
3109 END;
3110 $$;
3112 COMMENT ON FUNCTION "freeze_after_snapshot"
3113 ( "issue"."id"%TYPE )
3114 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
3117 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
3118 RETURNS VOID
3119 LANGUAGE 'plpgsql' VOLATILE AS $$
3120 DECLARE
3121 "issue_row" "issue"%ROWTYPE;
3122 BEGIN
3123 PERFORM "create_snapshot"("issue_id_p");
3124 PERFORM "freeze_after_snapshot"("issue_id_p");
3125 RETURN;
3126 END;
3127 $$;
3129 COMMENT ON FUNCTION "manual_freeze"
3130 ( "issue"."id"%TYPE )
3131 IS 'Freeze an issue manually (fully) and start voting';
3135 -----------------------
3136 -- Counting of votes --
3137 -----------------------
3140 CREATE FUNCTION "weight_of_added_vote_delegations"
3141 ( "issue_id_p" "issue"."id"%TYPE,
3142 "member_id_p" "member"."id"%TYPE,
3143 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
3144 RETURNS "direct_voter"."weight"%TYPE
3145 LANGUAGE 'plpgsql' VOLATILE AS $$
3146 DECLARE
3147 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3148 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
3149 "weight_v" INT4;
3150 "sub_weight_v" INT4;
3151 BEGIN
3152 "weight_v" := 0;
3153 FOR "issue_delegation_row" IN
3154 SELECT * FROM "issue_delegation"
3155 WHERE "trustee_id" = "member_id_p"
3156 AND "issue_id" = "issue_id_p"
3157 LOOP
3158 IF NOT EXISTS (
3159 SELECT NULL FROM "direct_voter"
3160 WHERE "member_id" = "issue_delegation_row"."truster_id"
3161 AND "issue_id" = "issue_id_p"
3162 ) AND NOT EXISTS (
3163 SELECT NULL FROM "delegating_voter"
3164 WHERE "member_id" = "issue_delegation_row"."truster_id"
3165 AND "issue_id" = "issue_id_p"
3166 ) THEN
3167 "delegate_member_ids_v" :=
3168 "member_id_p" || "delegate_member_ids_p";
3169 INSERT INTO "delegating_voter" (
3170 "issue_id",
3171 "member_id",
3172 "scope",
3173 "delegate_member_ids"
3174 ) VALUES (
3175 "issue_id_p",
3176 "issue_delegation_row"."truster_id",
3177 "issue_delegation_row"."scope",
3178 "delegate_member_ids_v"
3179 );
3180 "sub_weight_v" := 1 +
3181 "weight_of_added_vote_delegations"(
3182 "issue_id_p",
3183 "issue_delegation_row"."truster_id",
3184 "delegate_member_ids_v"
3185 );
3186 UPDATE "delegating_voter"
3187 SET "weight" = "sub_weight_v"
3188 WHERE "issue_id" = "issue_id_p"
3189 AND "member_id" = "issue_delegation_row"."truster_id";
3190 "weight_v" := "weight_v" + "sub_weight_v";
3191 END IF;
3192 END LOOP;
3193 RETURN "weight_v";
3194 END;
3195 $$;
3197 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
3198 ( "issue"."id"%TYPE,
3199 "member"."id"%TYPE,
3200 "delegating_voter"."delegate_member_ids"%TYPE )
3201 IS 'Helper function for "add_vote_delegations" function';
3204 CREATE FUNCTION "add_vote_delegations"
3205 ( "issue_id_p" "issue"."id"%TYPE )
3206 RETURNS VOID
3207 LANGUAGE 'plpgsql' VOLATILE AS $$
3208 DECLARE
3209 "member_id_v" "member"."id"%TYPE;
3210 BEGIN
3211 FOR "member_id_v" IN
3212 SELECT "member_id" FROM "direct_voter"
3213 WHERE "issue_id" = "issue_id_p"
3214 LOOP
3215 UPDATE "direct_voter" SET
3216 "weight" = "weight" + "weight_of_added_vote_delegations"(
3217 "issue_id_p",
3218 "member_id_v",
3219 '{}'
3220 )
3221 WHERE "member_id" = "member_id_v"
3222 AND "issue_id" = "issue_id_p";
3223 END LOOP;
3224 RETURN;
3225 END;
3226 $$;
3228 COMMENT ON FUNCTION "add_vote_delegations"
3229 ( "issue_id_p" "issue"."id"%TYPE )
3230 IS 'Helper function for "close_voting" function';
3233 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
3234 RETURNS VOID
3235 LANGUAGE 'plpgsql' VOLATILE AS $$
3236 DECLARE
3237 "area_id_v" "area"."id"%TYPE;
3238 "unit_id_v" "unit"."id"%TYPE;
3239 "member_id_v" "member"."id"%TYPE;
3240 BEGIN
3241 PERFORM "lock_issue"("issue_id_p");
3242 SELECT "id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
3243 SELECT "id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
3244 DELETE FROM "delegating_voter"
3245 WHERE "issue_id" = "issue_id_p";
3246 DELETE FROM "direct_voter"
3247 WHERE "issue_id" = "issue_id_p"
3248 AND "autoreject" = TRUE;
3249 DELETE FROM "direct_voter"
3250 USING (
3251 SELECT
3252 "direct_voter"."member_id"
3253 FROM "direct_voter"
3254 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
3255 LEFT JOIN "privilege"
3256 ON "privilege"."unit_id" = "unit_id_v"
3257 AND "privilege"."member_id" = "direct_voter"."member_id"
3258 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
3259 "member"."active" = FALSE OR
3260 "privilege"."voting_right" ISNULL OR
3261 "privilege"."voting_right" = FALSE
3262 )
3263 ) AS "subquery"
3264 WHERE "direct_voter"."issue_id" = "issue_id_p"
3265 AND "direct_voter"."member_id" = "subquery"."member_id";
3266 UPDATE "direct_voter" SET "weight" = 1
3267 WHERE "issue_id" = "issue_id_p";
3268 PERFORM "add_vote_delegations"("issue_id_p");
3269 FOR "member_id_v" IN
3270 SELECT "interest"."member_id"
3271 FROM "interest"
3272 JOIN "member"
3273 ON "interest"."member_id" = "member"."id"
3274 LEFT JOIN "direct_voter"
3275 ON "interest"."member_id" = "direct_voter"."member_id"
3276 AND "interest"."issue_id" = "direct_voter"."issue_id"
3277 LEFT JOIN "delegating_voter"
3278 ON "interest"."member_id" = "delegating_voter"."member_id"
3279 AND "interest"."issue_id" = "delegating_voter"."issue_id"
3280 WHERE "interest"."issue_id" = "issue_id_p"
3281 AND "interest"."autoreject" = TRUE
3282 AND "member"."active"
3283 AND "direct_voter"."member_id" ISNULL
3284 AND "delegating_voter"."member_id" ISNULL
3285 UNION SELECT "membership"."member_id"
3286 FROM "membership"
3287 JOIN "member"
3288 ON "membership"."member_id" = "member"."id"
3289 LEFT JOIN "interest"
3290 ON "membership"."member_id" = "interest"."member_id"
3291 AND "interest"."issue_id" = "issue_id_p"
3292 LEFT JOIN "direct_voter"
3293 ON "membership"."member_id" = "direct_voter"."member_id"
3294 AND "direct_voter"."issue_id" = "issue_id_p"
3295 LEFT JOIN "delegating_voter"
3296 ON "membership"."member_id" = "delegating_voter"."member_id"
3297 AND "delegating_voter"."issue_id" = "issue_id_p"
3298 WHERE "membership"."area_id" = "area_id_v"
3299 AND "membership"."autoreject" = TRUE
3300 AND "member"."active"
3301 AND "interest"."autoreject" ISNULL
3302 AND "direct_voter"."member_id" ISNULL
3303 AND "delegating_voter"."member_id" ISNULL
3304 LOOP
3305 INSERT INTO "direct_voter"
3306 ("member_id", "issue_id", "weight", "autoreject") VALUES
3307 ("member_id_v", "issue_id_p", 1, TRUE);
3308 INSERT INTO "vote" (
3309 "member_id",
3310 "issue_id",
3311 "initiative_id",
3312 "grade"
3313 ) SELECT
3314 "member_id_v" AS "member_id",
3315 "issue_id_p" AS "issue_id",
3316 "id" AS "initiative_id",
3317 -1 AS "grade"
3318 FROM "initiative" WHERE "issue_id" = "issue_id_p";
3319 END LOOP;
3320 PERFORM "add_vote_delegations"("issue_id_p");
3321 UPDATE "issue" SET
3322 "state" = 'calculation',
3323 "closed" = now(),
3324 "voter_count" = (
3325 SELECT coalesce(sum("weight"), 0)
3326 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
3327 )
3328 WHERE "id" = "issue_id_p";
3329 UPDATE "initiative" SET
3330 "positive_votes" = "vote_counts"."positive_votes",
3331 "negative_votes" = "vote_counts"."negative_votes",
3332 "agreed" = CASE WHEN "majority_strict" THEN
3333 "vote_counts"."positive_votes" * "majority_den" >
3334 "majority_num" *
3335 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
3336 ELSE
3337 "vote_counts"."positive_votes" * "majority_den" >=
3338 "majority_num" *
3339 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
3340 END
3341 FROM
3342 ( SELECT
3343 "initiative"."id" AS "initiative_id",
3344 coalesce(
3345 sum(
3346 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
3347 ),
3348 0
3349 ) AS "positive_votes",
3350 coalesce(
3351 sum(
3352 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
3353 ),
3354 0
3355 ) AS "negative_votes"
3356 FROM "initiative"
3357 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
3358 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
3359 LEFT JOIN "direct_voter"
3360 ON "direct_voter"."issue_id" = "initiative"."issue_id"
3361 LEFT JOIN "vote"
3362 ON "vote"."initiative_id" = "initiative"."id"
3363 AND "vote"."member_id" = "direct_voter"."member_id"
3364 WHERE "initiative"."issue_id" = "issue_id_p"
3365 AND "initiative"."admitted" -- NOTE: NULL case is handled too
3366 GROUP BY "initiative"."id"
3367 ) AS "vote_counts",
3368 "issue",
3369 "policy"
3370 WHERE "vote_counts"."initiative_id" = "initiative"."id"
3371 AND "issue"."id" = "initiative"."issue_id"
3372 AND "policy"."id" = "issue"."policy_id";
3373 -- NOTE: "closed" column of issue must be set at this point
3374 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3375 INSERT INTO "battle" (
3376 "issue_id",
3377 "winning_initiative_id", "losing_initiative_id",
3378 "count"
3379 ) SELECT
3380 "issue_id",
3381 "winning_initiative_id", "losing_initiative_id",
3382 "count"
3383 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
3384 END;
3385 $$;
3387 COMMENT ON FUNCTION "close_voting"
3388 ( "issue"."id"%TYPE )
3389 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.';
3392 CREATE FUNCTION "defeat_strength"
3393 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
3394 RETURNS INT8
3395 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3396 BEGIN
3397 IF "positive_votes_p" > "negative_votes_p" THEN
3398 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
3399 ELSIF "positive_votes_p" = "negative_votes_p" THEN
3400 RETURN 0;
3401 ELSE
3402 RETURN -1;
3403 END IF;
3404 END;
3405 $$;
3407 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';
3410 CREATE FUNCTION "array_init_string"("dim_p" INTEGER)
3411 RETURNS TEXT
3412 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3413 DECLARE
3414 "i" INTEGER;
3415 "ary_text_v" TEXT;
3416 BEGIN
3417 IF "dim_p" >= 1 THEN
3418 "ary_text_v" := '{NULL';
3419 "i" := "dim_p";
3420 LOOP
3421 "i" := "i" - 1;
3422 EXIT WHEN "i" = 0;
3423 "ary_text_v" := "ary_text_v" || ',NULL';
3424 END LOOP;
3425 "ary_text_v" := "ary_text_v" || '}';
3426 RETURN "ary_text_v";
3427 ELSE
3428 RAISE EXCEPTION 'Dimension needs to be at least 1.';
3429 END IF;
3430 END;
3431 $$;
3433 COMMENT ON FUNCTION "array_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
3436 CREATE FUNCTION "square_matrix_init_string"("dim_p" INTEGER)
3437 RETURNS TEXT
3438 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3439 DECLARE
3440 "i" INTEGER;
3441 "row_text_v" TEXT;
3442 "ary_text_v" TEXT;
3443 BEGIN
3444 IF "dim_p" >= 1 THEN
3445 "row_text_v" := '{NULL';
3446 "i" := "dim_p";
3447 LOOP
3448 "i" := "i" - 1;
3449 EXIT WHEN "i" = 0;
3450 "row_text_v" := "row_text_v" || ',NULL';
3451 END LOOP;
3452 "row_text_v" := "row_text_v" || '}';
3453 "ary_text_v" := '{' || "row_text_v";
3454 "i" := "dim_p";
3455 LOOP
3456 "i" := "i" - 1;
3457 EXIT WHEN "i" = 0;
3458 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
3459 END LOOP;
3460 "ary_text_v" := "ary_text_v" || '}';
3461 RETURN "ary_text_v";
3462 ELSE
3463 RAISE EXCEPTION 'Dimension needs to be at least 1.';
3464 END IF;
3465 END;
3466 $$;
3468 COMMENT ON FUNCTION "square_matrix_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
3471 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
3472 RETURNS VOID
3473 LANGUAGE 'plpgsql' VOLATILE AS $$
3474 DECLARE
3475 "dimension_v" INTEGER;
3476 "vote_matrix" INT4[][]; -- absolute votes
3477 "matrix" INT8[][]; -- defeat strength / best paths
3478 "i" INTEGER;
3479 "j" INTEGER;
3480 "k" INTEGER;
3481 "battle_row" "battle"%ROWTYPE;
3482 "rank_ary" INT4[];
3483 "rank_v" INT4;
3484 "done_v" INTEGER;
3485 "winners_ary" INTEGER[];
3486 "initiative_id_v" "initiative"."id"%TYPE;
3487 BEGIN
3488 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
3489 SELECT count(1) INTO "dimension_v" FROM "initiative"
3490 WHERE "issue_id" = "issue_id_p" AND "agreed";
3491 IF "dimension_v" = 1 THEN
3492 UPDATE "initiative" SET "rank" = 1
3493 WHERE "issue_id" = "issue_id_p" AND "agreed";
3494 ELSIF "dimension_v" > 1 THEN
3495 -- Create "vote_matrix" with absolute number of votes in pairwise
3496 -- comparison:
3497 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
3498 "i" := 1;
3499 "j" := 2;
3500 FOR "battle_row" IN
3501 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
3502 ORDER BY "winning_initiative_id", "losing_initiative_id"
3503 LOOP
3504 "vote_matrix"["i"]["j"] := "battle_row"."count";
3505 IF "j" = "dimension_v" THEN
3506 "i" := "i" + 1;
3507 "j" := 1;
3508 ELSE
3509 "j" := "j" + 1;
3510 IF "j" = "i" THEN
3511 "j" := "j" + 1;
3512 END IF;
3513 END IF;
3514 END LOOP;
3515 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
3516 RAISE EXCEPTION 'Wrong battle count (should not happen)';
3517 END IF;
3518 -- Store defeat strengths in "matrix" using "defeat_strength"
3519 -- function:
3520 "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
3521 "i" := 1;
3522 LOOP
3523 "j" := 1;
3524 LOOP
3525 IF "i" != "j" THEN
3526 "matrix"["i"]["j"] := "defeat_strength"(
3527 "vote_matrix"["i"]["j"],
3528 "vote_matrix"["j"]["i"]
3529 );
3530 END IF;
3531 EXIT WHEN "j" = "dimension_v";
3532 "j" := "j" + 1;
3533 END LOOP;
3534 EXIT WHEN "i" = "dimension_v";
3535 "i" := "i" + 1;
3536 END LOOP;
3537 -- Find best paths:
3538 "i" := 1;
3539 LOOP
3540 "j" := 1;
3541 LOOP
3542 IF "i" != "j" THEN
3543 "k" := 1;
3544 LOOP
3545 IF "i" != "k" AND "j" != "k" THEN
3546 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
3547 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
3548 "matrix"["j"]["k"] := "matrix"["j"]["i"];
3549 END IF;
3550 ELSE
3551 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
3552 "matrix"["j"]["k"] := "matrix"["i"]["k"];
3553 END IF;
3554 END IF;
3555 END IF;
3556 EXIT WHEN "k" = "dimension_v";
3557 "k" := "k" + 1;
3558 END LOOP;
3559 END IF;
3560 EXIT WHEN "j" = "dimension_v";
3561 "j" := "j" + 1;
3562 END LOOP;
3563 EXIT WHEN "i" = "dimension_v";
3564 "i" := "i" + 1;
3565 END LOOP;
3566 -- Determine order of winners:
3567 "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
3568 "rank_v" := 1;
3569 "done_v" := 0;
3570 LOOP
3571 "winners_ary" := '{}';
3572 "i" := 1;
3573 LOOP
3574 IF "rank_ary"["i"] ISNULL THEN
3575 "j" := 1;
3576 LOOP
3577 IF
3578 "i" != "j" AND
3579 "rank_ary"["j"] ISNULL AND
3580 "matrix"["j"]["i"] > "matrix"["i"]["j"]
3581 THEN
3582 -- someone else is better
3583 EXIT;
3584 END IF;
3585 IF "j" = "dimension_v" THEN
3586 -- noone is better
3587 "winners_ary" := "winners_ary" || "i";
3588 EXIT;
3589 END IF;
3590 "j" := "j" + 1;
3591 END LOOP;
3592 END IF;
3593 EXIT WHEN "i" = "dimension_v";
3594 "i" := "i" + 1;
3595 END LOOP;
3596 "i" := 1;
3597 LOOP
3598 "rank_ary"["winners_ary"["i"]] := "rank_v";
3599 "done_v" := "done_v" + 1;
3600 EXIT WHEN "i" = array_upper("winners_ary", 1);
3601 "i" := "i" + 1;
3602 END LOOP;
3603 EXIT WHEN "done_v" = "dimension_v";
3604 "rank_v" := "rank_v" + 1;
3605 END LOOP;
3606 -- write preliminary ranks:
3607 "i" := 1;
3608 FOR "initiative_id_v" IN
3609 SELECT "id" FROM "initiative"
3610 WHERE "issue_id" = "issue_id_p" AND "agreed"
3611 ORDER BY "id"
3612 LOOP
3613 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
3614 WHERE "id" = "initiative_id_v";
3615 "i" := "i" + 1;
3616 END LOOP;
3617 IF "i" != "dimension_v" + 1 THEN
3618 RAISE EXCEPTION 'Wrong winner count (should not happen)';
3619 END IF;
3620 -- straighten ranks (start counting with 1, no equal ranks):
3621 "rank_v" := 1;
3622 FOR "initiative_id_v" IN
3623 SELECT "id" FROM "initiative"
3624 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
3625 ORDER BY
3626 "rank",
3627 "vote_ratio"("positive_votes", "negative_votes") DESC,
3628 "id"
3629 LOOP
3630 UPDATE "initiative" SET "rank" = "rank_v"
3631 WHERE "id" = "initiative_id_v";
3632 "rank_v" := "rank_v" + 1;
3633 END LOOP;
3634 END IF;
3635 -- mark issue as finished
3636 UPDATE "issue" SET
3637 "state" =
3638 CASE WHEN NOT EXISTS (
3639 SELECT NULL FROM "initiative"
3640 WHERE "issue_id" = "issue_id_p" AND "admitted"
3641 ) THEN
3642 'canceled_no_initiative_admitted'::"issue_state"
3643 ELSE
3644 CASE WHEN "dimension_v" = 0 THEN
3645 'finished_without_winner'::"issue_state"
3646 ELSE
3647 'finished_with_winner'::"issue_state"
3648 END
3649 END,
3650 "ranks_available" = TRUE
3651 WHERE "id" = "issue_id_p";
3652 RETURN;
3653 END;
3654 $$;
3656 COMMENT ON FUNCTION "calculate_ranks"
3657 ( "issue"."id"%TYPE )
3658 IS 'Determine ranking (Votes have to be counted first)';
3662 -----------------------------
3663 -- Automatic state changes --
3664 -----------------------------
3667 CREATE FUNCTION "check_issue"
3668 ( "issue_id_p" "issue"."id"%TYPE )
3669 RETURNS VOID
3670 LANGUAGE 'plpgsql' VOLATILE AS $$
3671 DECLARE
3672 "issue_row" "issue"%ROWTYPE;
3673 "policy_row" "policy"%ROWTYPE;
3674 "voting_requested_v" BOOLEAN;
3675 BEGIN
3676 PERFORM "lock_issue"("issue_id_p");
3677 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3678 -- only process open issues:
3679 IF "issue_row"."closed" ISNULL THEN
3680 SELECT * INTO "policy_row" FROM "policy"
3681 WHERE "id" = "issue_row"."policy_id";
3682 -- create a snapshot, unless issue is already fully frozen:
3683 IF "issue_row"."fully_frozen" ISNULL THEN
3684 PERFORM "create_snapshot"("issue_id_p");
3685 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3686 END IF;
3687 -- eventually close or accept issues, which have not been accepted:
3688 IF "issue_row"."accepted" ISNULL THEN
3689 IF EXISTS (
3690 SELECT NULL FROM "initiative"
3691 WHERE "issue_id" = "issue_id_p"
3692 AND "supporter_count" > 0
3693 AND "supporter_count" * "policy_row"."issue_quorum_den"
3694 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
3695 ) THEN
3696 -- accept issues, if supporter count is high enough
3697 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3698 -- NOTE: "issue_row" used later
3699 "issue_row"."state" := 'discussion';
3700 "issue_row"."accepted" := now();
3701 UPDATE "issue" SET
3702 "state" = "issue_row"."state",
3703 "accepted" = "issue_row"."accepted"
3704 WHERE "id" = "issue_row"."id";
3705 ELSIF
3706 now() >= "issue_row"."created" + "issue_row"."admission_time"
3707 THEN
3708 -- close issues, if admission time has expired
3709 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3710 UPDATE "issue" SET
3711 "state" = 'canceled_issue_not_accepted',
3712 "closed" = now()
3713 WHERE "id" = "issue_row"."id";
3714 END IF;
3715 END IF;
3716 -- eventually half freeze issues:
3717 IF
3718 -- NOTE: issue can't be closed at this point, if it has been accepted
3719 "issue_row"."accepted" NOTNULL AND
3720 "issue_row"."half_frozen" ISNULL
3721 THEN
3722 SELECT
3723 CASE
3724 WHEN "vote_now" * 2 > "issue_row"."population" THEN
3725 TRUE
3726 WHEN "vote_later" * 2 > "issue_row"."population" THEN
3727 FALSE
3728 ELSE NULL
3729 END
3730 INTO "voting_requested_v"
3731 FROM "issue" WHERE "id" = "issue_id_p";
3732 IF
3733 "voting_requested_v" OR (
3734 "voting_requested_v" ISNULL AND
3735 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
3736 )
3737 THEN
3738 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
3739 -- NOTE: "issue_row" used later
3740 "issue_row"."state" := 'verification';
3741 "issue_row"."half_frozen" := now();
3742 UPDATE "issue" SET
3743 "state" = "issue_row"."state",
3744 "half_frozen" = "issue_row"."half_frozen"
3745 WHERE "id" = "issue_row"."id";
3746 END IF;
3747 END IF;
3748 -- close issues after some time, if all initiatives have been revoked:
3749 IF
3750 "issue_row"."closed" ISNULL AND
3751 NOT EXISTS (
3752 -- all initiatives are revoked
3753 SELECT NULL FROM "initiative"
3754 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3755 ) AND (
3756 -- and issue has not been accepted yet
3757 "issue_row"."accepted" ISNULL OR
3758 NOT EXISTS (
3759 -- or no initiatives have been revoked lately
3760 SELECT NULL FROM "initiative"
3761 WHERE "issue_id" = "issue_id_p"
3762 AND now() < "revoked" + "issue_row"."verification_time"
3763 ) OR (
3764 -- or verification time has elapsed
3765 "issue_row"."half_frozen" NOTNULL AND
3766 "issue_row"."fully_frozen" ISNULL AND
3767 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3768 )
3769 )
3770 THEN
3771 -- NOTE: "issue_row" used later
3772 "issue_row"."state" := 'canceled_all_initiatives_revoked';
3773 "issue_row"."closed" := now();
3774 UPDATE "issue" SET
3775 "state" = "issue_row"."state",
3776 "closed" = "issue_row"."closed"
3777 WHERE "id" = "issue_row"."id";
3778 END IF;
3779 -- fully freeze issue after verification time:
3780 IF
3781 "issue_row"."half_frozen" NOTNULL AND
3782 "issue_row"."fully_frozen" ISNULL AND
3783 "issue_row"."closed" ISNULL AND
3784 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3785 THEN
3786 PERFORM "freeze_after_snapshot"("issue_id_p");
3787 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
3788 END IF;
3789 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3790 -- close issue by calling close_voting(...) after voting time:
3791 IF
3792 "issue_row"."closed" ISNULL AND
3793 "issue_row"."fully_frozen" NOTNULL AND
3794 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
3795 THEN
3796 PERFORM "close_voting"("issue_id_p");
3797 -- calculate ranks will not consume much time and can be done now
3798 PERFORM "calculate_ranks"("issue_id_p");
3799 END IF;
3800 END IF;
3801 RETURN;
3802 END;
3803 $$;
3805 COMMENT ON FUNCTION "check_issue"
3806 ( "issue"."id"%TYPE )
3807 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.';
3810 CREATE FUNCTION "check_everything"()
3811 RETURNS VOID
3812 LANGUAGE 'plpgsql' VOLATILE AS $$
3813 DECLARE
3814 "issue_id_v" "issue"."id"%TYPE;
3815 BEGIN
3816 DELETE FROM "expired_session";
3817 PERFORM "check_last_login"();
3818 PERFORM "calculate_member_counts"();
3819 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
3820 PERFORM "check_issue"("issue_id_v");
3821 END LOOP;
3822 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
3823 PERFORM "calculate_ranks"("issue_id_v");
3824 END LOOP;
3825 RETURN;
3826 END;
3827 $$;
3829 COMMENT ON FUNCTION "check_everything"() IS 'Amongst other regular tasks this function performs "check_issue" for every open issue, and if possible, automatically calculates ranks. Use this function only for development and debugging purposes, as long transactions with exclusive locking may result. In productive environments you should call the lf_update program instead.';
3833 ----------------------
3834 -- Deletion of data --
3835 ----------------------
3838 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
3839 RETURNS VOID
3840 LANGUAGE 'plpgsql' VOLATILE AS $$
3841 DECLARE
3842 "issue_row" "issue"%ROWTYPE;
3843 BEGIN
3844 SELECT * INTO "issue_row"
3845 FROM "issue" WHERE "id" = "issue_id_p"
3846 FOR UPDATE;
3847 IF "issue_row"."cleaned" ISNULL THEN
3848 UPDATE "issue" SET
3849 "closed" = NULL,
3850 "ranks_available" = FALSE
3851 WHERE "id" = "issue_id_p";
3852 DELETE FROM "delegating_voter"
3853 WHERE "issue_id" = "issue_id_p";
3854 DELETE FROM "direct_voter"
3855 WHERE "issue_id" = "issue_id_p";
3856 DELETE FROM "delegating_interest_snapshot"
3857 WHERE "issue_id" = "issue_id_p";
3858 DELETE FROM "direct_interest_snapshot"
3859 WHERE "issue_id" = "issue_id_p";
3860 DELETE FROM "delegating_population_snapshot"
3861 WHERE "issue_id" = "issue_id_p";
3862 DELETE FROM "direct_population_snapshot"
3863 WHERE "issue_id" = "issue_id_p";
3864 DELETE FROM "ignored_issue"
3865 WHERE "issue_id" = "issue_id_p";
3866 DELETE FROM "delegation"
3867 WHERE "issue_id" = "issue_id_p";
3868 DELETE FROM "supporter"
3869 WHERE "issue_id" = "issue_id_p";
3870 UPDATE "issue" SET
3871 "closed" = "issue_row"."closed",
3872 "ranks_available" = "issue_row"."ranks_available",
3873 "cleaned" = now()
3874 WHERE "id" = "issue_id_p";
3875 END IF;
3876 RETURN;
3877 END;
3878 $$;
3880 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
3883 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
3884 RETURNS VOID
3885 LANGUAGE 'plpgsql' VOLATILE AS $$
3886 BEGIN
3887 UPDATE "member" SET
3888 "last_login" = NULL,
3889 "last_login_public" = NULL,
3890 "login" = NULL,
3891 "password" = NULL,
3892 "locked" = TRUE,
3893 "active" = FALSE,
3894 "notify_email" = NULL,
3895 "notify_email_unconfirmed" = NULL,
3896 "notify_email_secret" = NULL,
3897 "notify_email_secret_expiry" = NULL,
3898 "notify_email_lock_expiry" = NULL,
3899 "password_reset_secret" = NULL,
3900 "password_reset_secret_expiry" = NULL,
3901 "organizational_unit" = NULL,
3902 "internal_posts" = NULL,
3903 "realname" = NULL,
3904 "birthday" = NULL,
3905 "address" = NULL,
3906 "email" = NULL,
3907 "xmpp_address" = NULL,
3908 "website" = NULL,
3909 "phone" = NULL,
3910 "mobile_phone" = NULL,
3911 "profession" = NULL,
3912 "external_memberships" = NULL,
3913 "external_posts" = NULL,
3914 "statement" = NULL
3915 WHERE "id" = "member_id_p";
3916 -- "text_search_data" is updated by triggers
3917 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
3918 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
3919 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
3920 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
3921 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
3922 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
3923 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
3924 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
3925 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
3926 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
3927 DELETE FROM "ignored_issue" WHERE "member_id" = "member_id_p";
3928 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
3929 DELETE FROM "direct_voter" USING "issue"
3930 WHERE "direct_voter"."issue_id" = "issue"."id"
3931 AND "issue"."closed" ISNULL
3932 AND "member_id" = "member_id_p";
3933 RETURN;
3934 END;
3935 $$;
3937 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)';
3940 CREATE FUNCTION "delete_private_data"()
3941 RETURNS VOID
3942 LANGUAGE 'plpgsql' VOLATILE AS $$
3943 BEGIN
3944 UPDATE "member" SET
3945 "last_login" = NULL,
3946 "login" = NULL,
3947 "password" = NULL,
3948 "notify_email" = NULL,
3949 "notify_email_unconfirmed" = NULL,
3950 "notify_email_secret" = NULL,
3951 "notify_email_secret_expiry" = NULL,
3952 "notify_email_lock_expiry" = NULL,
3953 "password_reset_secret" = NULL,
3954 "password_reset_secret_expiry" = NULL,
3955 "organizational_unit" = NULL,
3956 "internal_posts" = NULL,
3957 "realname" = NULL,
3958 "birthday" = NULL,
3959 "address" = NULL,
3960 "email" = NULL,
3961 "xmpp_address" = NULL,
3962 "website" = NULL,
3963 "phone" = NULL,
3964 "mobile_phone" = NULL,
3965 "profession" = NULL,
3966 "external_memberships" = NULL,
3967 "external_posts" = NULL,
3968 "statement" = NULL;
3969 -- "text_search_data" is updated by triggers
3970 DELETE FROM "invite_code";
3971 DELETE FROM "setting";
3972 DELETE FROM "setting_map";
3973 DELETE FROM "member_relation_setting";
3974 DELETE FROM "member_image";
3975 DELETE FROM "contact";
3976 DELETE FROM "session";
3977 DELETE FROM "area_setting";
3978 DELETE FROM "issue_setting";
3979 DELETE FROM "initiative_setting";
3980 DELETE FROM "suggestion_setting";
3981 DELETE FROM "ignored_issue";
3982 DELETE FROM "direct_voter" USING "issue"
3983 WHERE "direct_voter"."issue_id" = "issue"."id"
3984 AND "issue"."closed" ISNULL;
3985 RETURN;
3986 END;
3987 $$;
3989 COMMENT ON FUNCTION "delete_private_data"() IS 'Used by lf_export script. 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.';
3993 COMMIT;