liquid_feedback_core
view core.sql @ 113:76ffbafb23b5
Work on event and notification system; Added more "issue_state"s; Replaced "ignored_issue" table; Removal of sessions in "delete_member" function
- "state" column of table "event" is now always filled
- splitted revocation state into 3 new "issue_state"s:
- 'canceled_revoked_before_accepted'
- 'canceled_after_revocation_during_discussion'
- 'canceled_after_revocation_during_verification'
- Added columns "notify_level" and "notify_event_id" to "member" table
- Replaced view "ignored_issue" by three new views:
- TABLE "ignored_member"
- TABLE "ignored_initiative"
- TABLE "non_voter"
- Function "delete_member" now removes "session"s
- Added member specific views on events:
- VIEW "event_seen_by_member"
- VIEW "pending_notification"
- "state" column of table "event" is now always filled
- splitted revocation state into 3 new "issue_state"s:
- 'canceled_revoked_before_accepted'
- 'canceled_after_revocation_during_discussion'
- 'canceled_after_revocation_during_verification'
- Added columns "notify_level" and "notify_event_id" to "member" table
- Replaced view "ignored_issue" by three new views:
- TABLE "ignored_member"
- TABLE "ignored_initiative"
- TABLE "non_voter"
- Function "delete_member" now removes "session"s
- Added member specific views on events:
- VIEW "event_seen_by_member"
- VIEW "pending_notification"
| author | jbe |
|---|---|
| date | Sat Mar 05 22:05:13 2011 +0100 (2011-03-05) |
| parents | 1b1e266df99b |
| children | 2abc6bc59f06 |
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_rc1', 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 TYPE "notify_level" AS ENUM
79 ('none', 'voting', 'verification', 'discussion', 'all');
81 COMMENT ON TYPE "notify_level" IS 'Level of notification: ''none'' = no notifications, ''voting'' = notifications about finished issues and issues in voting, ''verification'' = notifications about finished issues, issues in voting and verification phase, ''discussion'' = notifications about everything except issues in admission phase, ''all'' = notifications about everything';
84 CREATE TABLE "member" (
85 "id" SERIAL4 PRIMARY KEY,
86 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
87 "last_login" TIMESTAMPTZ,
88 "last_login_public" DATE,
89 "login" TEXT UNIQUE,
90 "password" TEXT,
91 "locked" BOOLEAN NOT NULL DEFAULT FALSE,
92 "active" BOOLEAN NOT NULL DEFAULT TRUE,
93 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
94 "notify_email" TEXT,
95 "notify_email_unconfirmed" TEXT,
96 "notify_email_secret" TEXT UNIQUE,
97 "notify_email_secret_expiry" TIMESTAMPTZ,
98 "notify_email_lock_expiry" TIMESTAMPTZ,
99 "notify_level" "notify_level" NOT NULL DEFAULT 'none',
100 "notify_event_id" INT8,
101 "password_reset_secret" TEXT UNIQUE,
102 "password_reset_secret_expiry" TIMESTAMPTZ,
103 "name" TEXT NOT NULL UNIQUE,
104 "identification" TEXT UNIQUE,
105 "organizational_unit" TEXT,
106 "internal_posts" TEXT,
107 "realname" TEXT,
108 "birthday" DATE,
109 "address" TEXT,
110 "email" TEXT,
111 "xmpp_address" TEXT,
112 "website" TEXT,
113 "phone" TEXT,
114 "mobile_phone" TEXT,
115 "profession" TEXT,
116 "external_memberships" TEXT,
117 "external_posts" TEXT,
118 "statement" TEXT,
119 "text_search_data" TSVECTOR );
120 CREATE INDEX "member_active_idx" ON "member" ("active");
121 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
122 CREATE TRIGGER "update_text_search_data"
123 BEFORE INSERT OR UPDATE ON "member"
124 FOR EACH ROW EXECUTE PROCEDURE
125 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
126 "name", "identification", "organizational_unit", "internal_posts",
127 "realname", "external_memberships", "external_posts", "statement" );
129 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
131 COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login';
132 COMMENT ON COLUMN "member"."last_login_public" IS 'Date of last login (time stripped for privacy reasons, updated only after day change)';
133 COMMENT ON COLUMN "member"."login" IS 'Login name';
134 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
135 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
136 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.';
137 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
138 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
139 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
140 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
141 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
142 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
143 COMMENT ON COLUMN "member"."notify_level" IS 'Selects which event notifications are to be sent to the "notify_email" mail address';
144 COMMENT ON COLUMN "member"."notify_event_id" IS 'Latest "id" of an "event" the member was notified about';
145 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member';
146 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
147 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
148 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
149 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
150 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
151 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
152 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
153 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his homepage within the system';
156 CREATE TABLE "member_history" (
157 "id" SERIAL8 PRIMARY KEY,
158 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
159 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
160 "active" BOOLEAN NOT NULL,
161 "name" TEXT NOT NULL );
162 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
164 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
166 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
167 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
170 CREATE TABLE "invite_code" (
171 "id" SERIAL8 PRIMARY KEY,
172 "code" TEXT NOT NULL UNIQUE,
173 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
174 "used" TIMESTAMPTZ,
175 "member_id" INT4 UNIQUE REFERENCES "member" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
176 "comment" TEXT,
177 CONSTRAINT "only_used_codes_may_refer_to_member" CHECK ("used" NOTNULL OR "member_id" ISNULL) );
179 COMMENT ON TABLE "invite_code" IS 'Invite codes can be used once to create a new member account.';
181 COMMENT ON COLUMN "invite_code"."code" IS 'Secret code';
182 COMMENT ON COLUMN "invite_code"."created" IS 'Time of creation of the secret code';
183 COMMENT ON COLUMN "invite_code"."used" IS 'NULL, if not used yet, otherwise tells when this code was used to create a member account';
184 COMMENT ON COLUMN "invite_code"."member_id" IS 'References the member whose account was created with this code';
185 COMMENT ON COLUMN "invite_code"."comment" IS 'Comment on the code, which is to be used for administrative reasons only';
188 CREATE TABLE "setting" (
189 PRIMARY KEY ("member_id", "key"),
190 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
191 "key" TEXT NOT NULL,
192 "value" TEXT NOT NULL );
193 CREATE INDEX "setting_key_idx" ON "setting" ("key");
195 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
197 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
200 CREATE TABLE "setting_map" (
201 PRIMARY KEY ("member_id", "key", "subkey"),
202 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
203 "key" TEXT NOT NULL,
204 "subkey" TEXT NOT NULL,
205 "value" TEXT NOT NULL );
206 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
208 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
210 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
211 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
212 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
215 CREATE TABLE "member_relation_setting" (
216 PRIMARY KEY ("member_id", "key", "other_member_id"),
217 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
218 "key" TEXT NOT NULL,
219 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
220 "value" TEXT NOT NULL );
222 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
225 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
227 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
230 CREATE TABLE "member_image" (
231 PRIMARY KEY ("member_id", "image_type", "scaled"),
232 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
233 "image_type" "member_image_type",
234 "scaled" BOOLEAN,
235 "content_type" TEXT,
236 "data" BYTEA NOT NULL );
238 COMMENT ON TABLE "member_image" IS 'Images of members';
240 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
243 CREATE TABLE "member_count" (
244 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
245 "total_count" INT4 NOT NULL );
247 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';
249 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
250 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
253 CREATE TABLE "contact" (
254 PRIMARY KEY ("member_id", "other_member_id"),
255 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
256 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
257 "public" BOOLEAN NOT NULL DEFAULT FALSE,
258 CONSTRAINT "cant_save_yourself_as_contact"
259 CHECK ("member_id" != "other_member_id") );
260 CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
262 COMMENT ON TABLE "contact" IS 'Contact lists';
264 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
265 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
266 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
269 CREATE TABLE "ignored_member" (
270 PRIMARY KEY ("member_id", "other_member_id"),
271 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
272 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
273 CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
275 COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
277 COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone';
278 COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
281 CREATE TABLE "session" (
282 "ident" TEXT PRIMARY KEY,
283 "additional_secret" TEXT,
284 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
285 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
286 "lang" TEXT );
287 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
289 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';
291 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
292 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
293 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
294 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
297 CREATE TABLE "policy" (
298 "id" SERIAL4 PRIMARY KEY,
299 "index" INT4 NOT NULL,
300 "active" BOOLEAN NOT NULL DEFAULT TRUE,
301 "name" TEXT NOT NULL UNIQUE,
302 "description" TEXT NOT NULL DEFAULT '',
303 "admission_time" INTERVAL NOT NULL,
304 "discussion_time" INTERVAL NOT NULL,
305 "verification_time" INTERVAL NOT NULL,
306 "voting_time" INTERVAL NOT NULL,
307 "issue_quorum_num" INT4 NOT NULL,
308 "issue_quorum_den" INT4 NOT NULL,
309 "initiative_quorum_num" INT4 NOT NULL,
310 "initiative_quorum_den" INT4 NOT NULL,
311 "majority_num" INT4 NOT NULL DEFAULT 1,
312 "majority_den" INT4 NOT NULL DEFAULT 2,
313 "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE );
314 CREATE INDEX "policy_active_idx" ON "policy" ("active");
316 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
318 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
319 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
320 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"';
321 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"';
322 COMMENT ON COLUMN "policy"."verification_time" IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
323 COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "fully_frozen" but not "closed"';
324 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"';
325 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"';
326 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
327 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
328 COMMENT ON COLUMN "policy"."majority_num" IS 'Numerator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
329 COMMENT ON COLUMN "policy"."majority_den" IS 'Denominator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
330 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.';
333 CREATE TABLE "unit" (
334 "id" SERIAL4 PRIMARY KEY,
335 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
336 "active" BOOLEAN NOT NULL DEFAULT TRUE,
337 "name" TEXT NOT NULL,
338 "description" TEXT NOT NULL DEFAULT '',
339 "member_count" INT4,
340 "text_search_data" TSVECTOR );
341 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
342 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
343 CREATE INDEX "unit_active_idx" ON "unit" ("active");
344 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
345 CREATE TRIGGER "update_text_search_data"
346 BEFORE INSERT OR UPDATE ON "unit"
347 FOR EACH ROW EXECUTE PROCEDURE
348 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
349 "name", "description" );
351 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
353 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
354 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in units of this area';
355 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
358 CREATE TABLE "area" (
359 "id" SERIAL4 PRIMARY KEY,
360 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
361 "active" BOOLEAN NOT NULL DEFAULT TRUE,
362 "name" TEXT NOT NULL,
363 "description" TEXT NOT NULL DEFAULT '',
364 "direct_member_count" INT4,
365 "member_weight" INT4,
366 "autoreject_weight" INT4,
367 "text_search_data" TSVECTOR );
368 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
369 CREATE INDEX "area_active_idx" ON "area" ("active");
370 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
371 CREATE TRIGGER "update_text_search_data"
372 BEFORE INSERT OR UPDATE ON "area"
373 FOR EACH ROW EXECUTE PROCEDURE
374 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
375 "name", "description" );
377 COMMENT ON TABLE "area" IS 'Subject areas';
379 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
380 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"';
381 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
382 COMMENT ON COLUMN "area"."autoreject_weight" IS 'Sum of weight of members using the autoreject feature';
385 CREATE TABLE "area_setting" (
386 PRIMARY KEY ("member_id", "key", "area_id"),
387 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
388 "key" TEXT NOT NULL,
389 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
390 "value" TEXT NOT NULL );
392 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
395 CREATE TABLE "allowed_policy" (
396 PRIMARY KEY ("area_id", "policy_id"),
397 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
398 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
399 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
400 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
402 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
404 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
407 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
409 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';
412 CREATE TYPE "issue_state" AS ENUM (
413 'admission', 'discussion', 'verification', 'voting',
414 'canceled_revoked_before_accepted',
415 'canceled_issue_not_accepted',
416 'canceled_after_revocation_during_discussion',
417 'canceled_after_revocation_during_verification',
418 'calculation',
419 'canceled_no_initiative_admitted',
420 'finished_without_winner', 'finished_with_winner');
422 COMMENT ON TYPE "issue_state" IS 'State of issues';
425 CREATE TABLE "issue" (
426 "id" SERIAL4 PRIMARY KEY,
427 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
428 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
429 "state" "issue_state" NOT NULL DEFAULT 'admission',
430 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
431 "accepted" TIMESTAMPTZ,
432 "half_frozen" TIMESTAMPTZ,
433 "fully_frozen" TIMESTAMPTZ,
434 "closed" TIMESTAMPTZ,
435 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
436 "cleaned" TIMESTAMPTZ,
437 "admission_time" INTERVAL NOT NULL,
438 "discussion_time" INTERVAL NOT NULL,
439 "verification_time" INTERVAL NOT NULL,
440 "voting_time" INTERVAL NOT NULL,
441 "snapshot" TIMESTAMPTZ,
442 "latest_snapshot_event" "snapshot_event",
443 "population" INT4,
444 "vote_now" INT4,
445 "vote_later" INT4,
446 "voter_count" INT4,
447 CONSTRAINT "valid_state" CHECK ((
448 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
449 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
450 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
451 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
452 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
453 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
454 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
455 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
456 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)) AND (
457 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
458 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
459 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
460 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
461 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
462 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
463 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
464 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
465 ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
466 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
467 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
468 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
469 )),
470 CONSTRAINT "state_change_order" CHECK (
471 "created" <= "accepted" AND
472 "accepted" <= "half_frozen" AND
473 "half_frozen" <= "fully_frozen" AND
474 "fully_frozen" <= "closed" ),
475 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
476 "cleaned" ISNULL OR "closed" NOTNULL ),
477 CONSTRAINT "last_snapshot_on_full_freeze"
478 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
479 CONSTRAINT "freeze_requires_snapshot"
480 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
481 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
482 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
483 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
484 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
485 CREATE INDEX "issue_created_idx" ON "issue" ("created");
486 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
487 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
488 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
489 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
490 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
491 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
493 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
495 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
496 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.';
497 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.';
498 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.';
499 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
500 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
501 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
502 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
503 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
504 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
505 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';
506 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';
507 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
508 COMMENT ON COLUMN "issue"."vote_now" IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"';
509 COMMENT ON COLUMN "issue"."vote_later" IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"';
510 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';
513 CREATE TABLE "issue_setting" (
514 PRIMARY KEY ("member_id", "key", "issue_id"),
515 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
516 "key" TEXT NOT NULL,
517 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
518 "value" TEXT NOT NULL );
520 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
523 CREATE TABLE "initiative" (
524 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
525 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
526 "id" SERIAL4 PRIMARY KEY,
527 "name" TEXT NOT NULL,
528 "discussion_url" TEXT,
529 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
530 "revoked" TIMESTAMPTZ,
531 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
532 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
533 "admitted" BOOLEAN,
534 "supporter_count" INT4,
535 "informed_supporter_count" INT4,
536 "satisfied_supporter_count" INT4,
537 "satisfied_informed_supporter_count" INT4,
538 "positive_votes" INT4,
539 "negative_votes" INT4,
540 "agreed" BOOLEAN,
541 "rank" INT4,
542 "text_search_data" TSVECTOR,
543 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
544 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL),
545 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
546 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
547 CONSTRAINT "revoked_initiatives_cant_be_admitted"
548 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
549 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"
550 CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "agreed" ISNULL)),
551 CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"
552 CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL),
553 CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"
554 CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) );
555 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
556 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
557 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
558 CREATE TRIGGER "update_text_search_data"
559 BEFORE INSERT OR UPDATE ON "initiative"
560 FOR EACH ROW EXECUTE PROCEDURE
561 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
562 "name", "discussion_url");
564 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.';
566 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
567 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
568 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoked the initiative';
569 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
570 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
571 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
572 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
573 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
574 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
575 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
576 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"';
577 COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';
580 CREATE TABLE "battle" (
581 PRIMARY KEY ("issue_id", "winning_initiative_id", "losing_initiative_id"),
582 "issue_id" INT4,
583 "winning_initiative_id" INT4,
584 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
585 "losing_initiative_id" INT4,
586 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
587 "count" INT4 NOT NULL);
589 COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue';
592 CREATE TABLE "ignored_initiative" (
593 PRIMARY KEY ("initiative_id", "member_id"),
594 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
595 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
596 CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");
598 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
601 CREATE TABLE "initiative_setting" (
602 PRIMARY KEY ("member_id", "key", "initiative_id"),
603 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
604 "key" TEXT NOT NULL,
605 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
606 "value" TEXT NOT NULL );
608 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
611 CREATE TABLE "draft" (
612 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
613 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
614 "id" SERIAL8 PRIMARY KEY,
615 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
616 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
617 "formatting_engine" TEXT,
618 "content" TEXT NOT NULL,
619 "text_search_data" TSVECTOR );
620 CREATE INDEX "draft_created_idx" ON "draft" ("created");
621 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
622 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
623 CREATE TRIGGER "update_text_search_data"
624 BEFORE INSERT OR UPDATE ON "draft"
625 FOR EACH ROW EXECUTE PROCEDURE
626 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
628 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.';
630 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
631 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
634 CREATE TABLE "rendered_draft" (
635 PRIMARY KEY ("draft_id", "format"),
636 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
637 "format" TEXT,
638 "content" TEXT NOT NULL );
640 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)';
643 CREATE TABLE "suggestion" (
644 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
645 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
646 "id" SERIAL8 PRIMARY KEY,
647 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
648 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
649 "name" TEXT NOT NULL,
650 "description" TEXT NOT NULL DEFAULT '',
651 "text_search_data" TSVECTOR,
652 "minus2_unfulfilled_count" INT4,
653 "minus2_fulfilled_count" INT4,
654 "minus1_unfulfilled_count" INT4,
655 "minus1_fulfilled_count" INT4,
656 "plus1_unfulfilled_count" INT4,
657 "plus1_fulfilled_count" INT4,
658 "plus2_unfulfilled_count" INT4,
659 "plus2_fulfilled_count" INT4 );
660 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
661 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
662 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
663 CREATE TRIGGER "update_text_search_data"
664 BEFORE INSERT OR UPDATE ON "suggestion"
665 FOR EACH ROW EXECUTE PROCEDURE
666 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
667 "name", "description");
669 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';
671 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
672 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
673 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
674 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
675 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
676 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
677 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
678 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
681 CREATE TABLE "suggestion_setting" (
682 PRIMARY KEY ("member_id", "key", "suggestion_id"),
683 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
684 "key" TEXT NOT NULL,
685 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
686 "value" TEXT NOT NULL );
688 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
691 CREATE TABLE "invite_code_unit" (
692 PRIMARY KEY ("invite_code_id", "unit_id"),
693 "invite_code_id" INT8 REFERENCES "invite_code" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
694 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
696 COMMENT ON TABLE "invite_code_unit" IS 'Units where accounts created with a given invite codes get voting rights';
699 CREATE TABLE "privilege" (
700 PRIMARY KEY ("unit_id", "member_id"),
701 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
702 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
703 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
704 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
705 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
706 "voting_right_manager" BOOLEAN NOT NULL DEFAULT FALSE,
707 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE );
709 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
711 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke admin privileges to/from other users';
712 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create or lock sub units';
713 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create or lock areas and set area parameters';
714 COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote inside the unit';
715 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to discuss and vote';
718 CREATE TABLE "membership" (
719 PRIMARY KEY ("area_id", "member_id"),
720 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
721 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
722 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
723 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
725 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
727 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.';
730 CREATE TABLE "interest" (
731 PRIMARY KEY ("issue_id", "member_id"),
732 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
733 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
734 "autoreject" BOOLEAN,
735 "voting_requested" BOOLEAN );
736 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
738 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.';
740 COMMENT ON COLUMN "interest"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure';
741 COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply';
744 CREATE TABLE "initiator" (
745 PRIMARY KEY ("initiative_id", "member_id"),
746 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
747 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
748 "accepted" BOOLEAN );
749 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
751 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.';
753 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.';
756 CREATE TABLE "supporter" (
757 "issue_id" INT4 NOT NULL,
758 PRIMARY KEY ("initiative_id", "member_id"),
759 "initiative_id" INT4,
760 "member_id" INT4,
761 "draft_id" INT8 NOT NULL,
762 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
763 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
764 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
766 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.';
768 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
771 CREATE TABLE "opinion" (
772 "initiative_id" INT4 NOT NULL,
773 PRIMARY KEY ("suggestion_id", "member_id"),
774 "suggestion_id" INT8,
775 "member_id" INT4,
776 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
777 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
778 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
779 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
780 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
782 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.';
784 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
787 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
789 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
792 CREATE TABLE "delegation" (
793 "id" SERIAL8 PRIMARY KEY,
794 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
795 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
796 "scope" "delegation_scope" NOT NULL,
797 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
798 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
799 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
800 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
801 CONSTRAINT "no_unit_delegation_to_null"
802 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
803 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
804 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
805 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
806 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
807 UNIQUE ("unit_id", "truster_id"),
808 UNIQUE ("area_id", "truster_id"),
809 UNIQUE ("issue_id", "truster_id") );
810 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
811 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
813 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
815 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
816 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
817 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
820 CREATE TABLE "direct_population_snapshot" (
821 PRIMARY KEY ("issue_id", "event", "member_id"),
822 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
823 "event" "snapshot_event",
824 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
825 "weight" INT4 );
826 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
828 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
830 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
831 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
834 CREATE TABLE "delegating_population_snapshot" (
835 PRIMARY KEY ("issue_id", "event", "member_id"),
836 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
837 "event" "snapshot_event",
838 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
839 "weight" INT4,
840 "scope" "delegation_scope" NOT NULL,
841 "delegate_member_ids" INT4[] NOT NULL );
842 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
844 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
846 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
847 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
848 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
849 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"';
852 CREATE TABLE "direct_interest_snapshot" (
853 PRIMARY KEY ("issue_id", "event", "member_id"),
854 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
855 "event" "snapshot_event",
856 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
857 "weight" INT4,
858 "voting_requested" BOOLEAN );
859 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
861 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
863 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
864 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
865 COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';
868 CREATE TABLE "delegating_interest_snapshot" (
869 PRIMARY KEY ("issue_id", "event", "member_id"),
870 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
871 "event" "snapshot_event",
872 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
873 "weight" INT4,
874 "scope" "delegation_scope" NOT NULL,
875 "delegate_member_ids" INT4[] NOT NULL );
876 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
878 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
880 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
881 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
882 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
883 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"';
886 CREATE TABLE "direct_supporter_snapshot" (
887 "issue_id" INT4 NOT NULL,
888 PRIMARY KEY ("initiative_id", "event", "member_id"),
889 "initiative_id" INT4,
890 "event" "snapshot_event",
891 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
892 "informed" BOOLEAN NOT NULL,
893 "satisfied" BOOLEAN NOT NULL,
894 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
895 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
896 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
898 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
900 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
901 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
902 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
905 CREATE TABLE "non_voter" (
906 PRIMARY KEY ("issue_id", "member_id"),
907 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
908 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
909 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
911 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
914 CREATE TABLE "direct_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 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
920 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
922 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.';
924 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
925 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
928 CREATE TABLE "delegating_voter" (
929 PRIMARY KEY ("issue_id", "member_id"),
930 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
931 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
932 "weight" INT4,
933 "scope" "delegation_scope" NOT NULL,
934 "delegate_member_ids" INT4[] NOT NULL );
935 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
937 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
939 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
940 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
941 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"';
944 CREATE TABLE "vote" (
945 "issue_id" INT4 NOT NULL,
946 PRIMARY KEY ("initiative_id", "member_id"),
947 "initiative_id" INT4,
948 "member_id" INT4,
949 "grade" INT4,
950 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
951 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
952 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
954 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.';
956 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.';
959 CREATE TABLE "issue_comment" (
960 PRIMARY KEY ("issue_id", "member_id"),
961 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
962 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
963 "changed" TIMESTAMPTZ NOT NULL DEFAULT now(),
964 "formatting_engine" TEXT,
965 "content" TEXT NOT NULL,
966 "text_search_data" TSVECTOR );
967 CREATE INDEX "issue_comment_member_id_idx" ON "issue_comment" ("member_id");
968 CREATE INDEX "issue_comment_text_search_data_idx" ON "issue_comment" USING gin ("text_search_data");
969 CREATE TRIGGER "update_text_search_data"
970 BEFORE INSERT OR UPDATE ON "issue_comment"
971 FOR EACH ROW EXECUTE PROCEDURE
972 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
974 COMMENT ON TABLE "issue_comment" IS 'Place to store free comments of members related to issues';
976 COMMENT ON COLUMN "issue_comment"."changed" IS 'Time the comment was last changed';
979 CREATE TABLE "rendered_issue_comment" (
980 PRIMARY KEY ("issue_id", "member_id", "format"),
981 FOREIGN KEY ("issue_id", "member_id")
982 REFERENCES "issue_comment" ("issue_id", "member_id")
983 ON DELETE CASCADE ON UPDATE CASCADE,
984 "issue_id" INT4,
985 "member_id" INT4,
986 "format" TEXT,
987 "content" TEXT NOT NULL );
989 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)';
992 CREATE TABLE "voting_comment" (
993 PRIMARY KEY ("issue_id", "member_id"),
994 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
995 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
996 "changed" TIMESTAMPTZ,
997 "formatting_engine" TEXT,
998 "content" TEXT NOT NULL,
999 "text_search_data" TSVECTOR );
1000 CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id");
1001 CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data");
1002 CREATE TRIGGER "update_text_search_data"
1003 BEFORE INSERT OR UPDATE ON "voting_comment"
1004 FOR EACH ROW EXECUTE PROCEDURE
1005 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
1007 COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.';
1009 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.';
1012 CREATE TABLE "rendered_voting_comment" (
1013 PRIMARY KEY ("issue_id", "member_id", "format"),
1014 FOREIGN KEY ("issue_id", "member_id")
1015 REFERENCES "voting_comment" ("issue_id", "member_id")
1016 ON DELETE CASCADE ON UPDATE CASCADE,
1017 "issue_id" INT4,
1018 "member_id" INT4,
1019 "format" TEXT,
1020 "content" TEXT NOT NULL );
1022 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)';
1025 CREATE TYPE "event_type" AS ENUM (
1026 'issue_state_changed',
1027 'initiative_created_in_new_issue',
1028 'initiative_created_in_existing_issue',
1029 'initiative_revoked',
1030 'new_draft_created',
1031 'suggestion_created');
1033 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
1036 CREATE TABLE "event" (
1037 "id" SERIAL8 PRIMARY KEY,
1038 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
1039 "event" "event_type" NOT NULL,
1040 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1041 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1042 "state" "issue_state" CHECK ("state" != 'calculation'),
1043 "initiative_id" INT4,
1044 "draft_id" INT8,
1045 "suggestion_id" INT8,
1046 FOREIGN KEY ("issue_id", "initiative_id")
1047 REFERENCES "initiative" ("issue_id", "id")
1048 ON DELETE CASCADE ON UPDATE CASCADE,
1049 FOREIGN KEY ("initiative_id", "draft_id")
1050 REFERENCES "draft" ("initiative_id", "id")
1051 ON DELETE CASCADE ON UPDATE CASCADE,
1052 FOREIGN KEY ("initiative_id", "suggestion_id")
1053 REFERENCES "suggestion" ("initiative_id", "id")
1054 ON DELETE CASCADE ON UPDATE CASCADE,
1055 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
1056 "event" != 'issue_state_changed' OR (
1057 "member_id" ISNULL AND
1058 "issue_id" NOTNULL AND
1059 "state" NOTNULL AND
1060 "initiative_id" ISNULL AND
1061 "draft_id" ISNULL AND
1062 "suggestion_id" ISNULL )),
1063 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
1064 "event" NOT IN (
1065 'initiative_created_in_new_issue',
1066 'initiative_created_in_existing_issue',
1067 'initiative_revoked',
1068 'new_draft_created'
1069 ) OR (
1070 "member_id" NOTNULL AND
1071 "issue_id" NOTNULL AND
1072 "state" NOTNULL AND
1073 "initiative_id" NOTNULL AND
1074 "draft_id" NOTNULL AND
1075 "suggestion_id" ISNULL )),
1076 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
1077 "event" != 'suggestion_created' OR (
1078 "member_id" NOTNULL AND
1079 "issue_id" NOTNULL AND
1080 "state" NOTNULL AND
1081 "initiative_id" NOTNULL AND
1082 "draft_id" ISNULL AND
1083 "suggestion_id" NOTNULL )) );
1085 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
1089 ----------------------------------------------
1090 -- Writing of history entries and event log --
1091 ----------------------------------------------
1093 CREATE FUNCTION "write_member_history_trigger"()
1094 RETURNS TRIGGER
1095 LANGUAGE 'plpgsql' VOLATILE AS $$
1096 BEGIN
1097 IF
1098 NEW."active" != OLD."active" OR
1099 NEW."name" != OLD."name"
1100 THEN
1101 INSERT INTO "member_history"
1102 ("member_id", "active", "name")
1103 VALUES (NEW."id", OLD."active", OLD."name");
1104 END IF;
1105 RETURN NULL;
1106 END;
1107 $$;
1109 CREATE TRIGGER "write_member_history"
1110 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1111 "write_member_history_trigger"();
1113 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
1114 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
1117 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
1118 RETURNS TRIGGER
1119 LANGUAGE 'plpgsql' VOLATILE AS $$
1120 BEGIN
1121 IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN
1122 INSERT INTO "event" ("event", "issue_id", "state")
1123 VALUES ('issue_state_changed', NEW."id", NEW."state");
1124 END IF;
1125 RETURN NULL;
1126 END;
1127 $$;
1129 CREATE TRIGGER "write_event_issue_state_changed"
1130 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
1131 "write_event_issue_state_changed_trigger"();
1133 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
1134 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
1137 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1138 RETURNS TRIGGER
1139 LANGUAGE 'plpgsql' VOLATILE AS $$
1140 DECLARE
1141 "initiative_row" "initiative"%ROWTYPE;
1142 "issue_row" "issue"%ROWTYPE;
1143 "event_v" "event_type";
1144 BEGIN
1145 SELECT * INTO "initiative_row" FROM "initiative"
1146 WHERE "id" = NEW."initiative_id";
1147 SELECT * INTO "issue_row" FROM "issue"
1148 WHERE "id" = "initiative_row"."issue_id";
1149 IF EXISTS (
1150 SELECT NULL FROM "draft"
1151 WHERE "initiative_id" = NEW."initiative_id"
1152 AND "id" != NEW."id"
1153 ) THEN
1154 "event_v" := 'new_draft_created';
1155 ELSE
1156 IF EXISTS (
1157 SELECT NULL FROM "initiative"
1158 WHERE "issue_id" = "initiative_row"."issue_id"
1159 AND "id" != "initiative_row"."id"
1160 ) THEN
1161 "event_v" := 'initiative_created_in_existing_issue';
1162 ELSE
1163 "event_v" := 'initiative_created_in_new_issue';
1164 END IF;
1165 END IF;
1166 INSERT INTO "event" (
1167 "event", "member_id",
1168 "issue_id", "state", "initiative_id", "draft_id"
1169 ) VALUES (
1170 "event_v",
1171 NEW."author_id",
1172 "initiative_row"."issue_id",
1173 "issue_row"."state",
1174 "initiative_row"."id",
1175 NEW."id" );
1176 RETURN NULL;
1177 END;
1178 $$;
1180 CREATE TRIGGER "write_event_initiative_or_draft_created"
1181 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
1182 "write_event_initiative_or_draft_created_trigger"();
1184 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
1185 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
1188 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
1189 RETURNS TRIGGER
1190 LANGUAGE 'plpgsql' VOLATILE AS $$
1191 DECLARE
1192 "issue_row" "issue"%ROWTYPE;
1193 BEGIN
1194 SELECT * INTO "issue_row" FROM "issue"
1195 WHERE "id" = NEW."issue_id";
1196 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1197 INSERT INTO "event" (
1198 "event", "member_id", "issue_id", "state", "initiative_id"
1199 ) VALUES (
1200 'initiative_revoked',
1201 NEW."revoked_by_member_id",
1202 NEW."issue_id",
1203 "issue_row"."state",
1204 NEW."id" );
1205 END IF;
1206 RETURN NULL;
1207 END;
1208 $$;
1210 CREATE TRIGGER "write_event_initiative_revoked"
1211 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
1212 "write_event_initiative_revoked_trigger"();
1214 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
1215 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
1218 CREATE FUNCTION "write_event_suggestion_created_trigger"()
1219 RETURNS TRIGGER
1220 LANGUAGE 'plpgsql' VOLATILE AS $$
1221 DECLARE
1222 "initiative_row" "initiative"%ROWTYPE;
1223 "issue_row" "issue"%ROWTYPE;
1224 BEGIN
1225 SELECT * INTO "initiative_row" FROM "initiative"
1226 WHERE "id" = NEW."initiative_id";
1227 SELECT * INTO "issue_row" FROM "issue"
1228 WHERE "id" = "initiative_row"."issue_id";
1229 INSERT INTO "event" (
1230 "event", "member_id",
1231 "issue_id", "state", "initiative_id", "suggestion_id"
1232 ) VALUES (
1233 'suggestion_created',
1234 NEW."author_id",
1235 "initiative_row"."issue_id",
1236 "issue_row"."state",
1237 "initiative_row"."id",
1238 NEW."id" );
1239 RETURN NULL;
1240 END;
1241 $$;
1243 CREATE TRIGGER "write_event_suggestion_created"
1244 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1245 "write_event_suggestion_created_trigger"();
1247 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
1248 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1252 ----------------------------
1253 -- Additional constraints --
1254 ----------------------------
1257 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
1258 RETURNS TRIGGER
1259 LANGUAGE 'plpgsql' VOLATILE AS $$
1260 BEGIN
1261 IF NOT EXISTS (
1262 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
1263 ) THEN
1264 --RAISE 'Cannot create issue without an initial initiative.' USING
1265 -- ERRCODE = 'integrity_constraint_violation',
1266 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
1267 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
1268 END IF;
1269 RETURN NULL;
1270 END;
1271 $$;
1273 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
1274 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
1275 FOR EACH ROW EXECUTE PROCEDURE
1276 "issue_requires_first_initiative_trigger"();
1278 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
1279 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
1282 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
1283 RETURNS TRIGGER
1284 LANGUAGE 'plpgsql' VOLATILE AS $$
1285 DECLARE
1286 "reference_lost" BOOLEAN;
1287 BEGIN
1288 IF TG_OP = 'DELETE' THEN
1289 "reference_lost" := TRUE;
1290 ELSE
1291 "reference_lost" := NEW."issue_id" != OLD."issue_id";
1292 END IF;
1293 IF
1294 "reference_lost" AND NOT EXISTS (
1295 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
1296 )
1297 THEN
1298 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
1299 END IF;
1300 RETURN NULL;
1301 END;
1302 $$;
1304 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
1305 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1306 FOR EACH ROW EXECUTE PROCEDURE
1307 "last_initiative_deletes_issue_trigger"();
1309 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
1310 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
1313 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
1314 RETURNS TRIGGER
1315 LANGUAGE 'plpgsql' VOLATILE AS $$
1316 BEGIN
1317 IF NOT EXISTS (
1318 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
1319 ) THEN
1320 --RAISE 'Cannot create initiative without an initial draft.' USING
1321 -- ERRCODE = 'integrity_constraint_violation',
1322 -- HINT = 'Create issue, initiative and draft within the same transaction.';
1323 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
1324 END IF;
1325 RETURN NULL;
1326 END;
1327 $$;
1329 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
1330 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1331 FOR EACH ROW EXECUTE PROCEDURE
1332 "initiative_requires_first_draft_trigger"();
1334 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
1335 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
1338 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
1339 RETURNS TRIGGER
1340 LANGUAGE 'plpgsql' VOLATILE AS $$
1341 DECLARE
1342 "reference_lost" BOOLEAN;
1343 BEGIN
1344 IF TG_OP = 'DELETE' THEN
1345 "reference_lost" := TRUE;
1346 ELSE
1347 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
1348 END IF;
1349 IF
1350 "reference_lost" AND NOT EXISTS (
1351 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
1352 )
1353 THEN
1354 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
1355 END IF;
1356 RETURN NULL;
1357 END;
1358 $$;
1360 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
1361 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
1362 FOR EACH ROW EXECUTE PROCEDURE
1363 "last_draft_deletes_initiative_trigger"();
1365 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
1366 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
1369 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
1370 RETURNS TRIGGER
1371 LANGUAGE 'plpgsql' VOLATILE AS $$
1372 BEGIN
1373 IF NOT EXISTS (
1374 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
1375 ) THEN
1376 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
1377 END IF;
1378 RETURN NULL;
1379 END;
1380 $$;
1382 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
1383 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
1384 FOR EACH ROW EXECUTE PROCEDURE
1385 "suggestion_requires_first_opinion_trigger"();
1387 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1388 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1391 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1392 RETURNS TRIGGER
1393 LANGUAGE 'plpgsql' VOLATILE AS $$
1394 DECLARE
1395 "reference_lost" BOOLEAN;
1396 BEGIN
1397 IF TG_OP = 'DELETE' THEN
1398 "reference_lost" := TRUE;
1399 ELSE
1400 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1401 END IF;
1402 IF
1403 "reference_lost" AND NOT EXISTS (
1404 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1405 )
1406 THEN
1407 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1408 END IF;
1409 RETURN NULL;
1410 END;
1411 $$;
1413 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1414 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1415 FOR EACH ROW EXECUTE PROCEDURE
1416 "last_opinion_deletes_suggestion_trigger"();
1418 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1419 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1423 ---------------------------------------------------------------
1424 -- Ensure that votes are not modified when issues are frozen --
1425 ---------------------------------------------------------------
1427 -- NOTE: Frontends should ensure this anyway, but in case of programming
1428 -- errors the following triggers ensure data integrity.
1431 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1432 RETURNS TRIGGER
1433 LANGUAGE 'plpgsql' VOLATILE AS $$
1434 DECLARE
1435 "issue_id_v" "issue"."id"%TYPE;
1436 "issue_row" "issue"%ROWTYPE;
1437 BEGIN
1438 IF TG_OP = 'DELETE' THEN
1439 "issue_id_v" := OLD."issue_id";
1440 ELSE
1441 "issue_id_v" := NEW."issue_id";
1442 END IF;
1443 SELECT INTO "issue_row" * FROM "issue"
1444 WHERE "id" = "issue_id_v" FOR SHARE;
1445 IF "issue_row"."closed" NOTNULL THEN
1446 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
1447 END IF;
1448 RETURN NULL;
1449 END;
1450 $$;
1452 CREATE TRIGGER "forbid_changes_on_closed_issue"
1453 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1454 FOR EACH ROW EXECUTE PROCEDURE
1455 "forbid_changes_on_closed_issue_trigger"();
1457 CREATE TRIGGER "forbid_changes_on_closed_issue"
1458 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1459 FOR EACH ROW EXECUTE PROCEDURE
1460 "forbid_changes_on_closed_issue_trigger"();
1462 CREATE TRIGGER "forbid_changes_on_closed_issue"
1463 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1464 FOR EACH ROW EXECUTE PROCEDURE
1465 "forbid_changes_on_closed_issue_trigger"();
1467 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"';
1468 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';
1469 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';
1470 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';
1474 --------------------------------------------------------------------
1475 -- Auto-retrieval of fields only needed for referential integrity --
1476 --------------------------------------------------------------------
1479 CREATE FUNCTION "autofill_issue_id_trigger"()
1480 RETURNS TRIGGER
1481 LANGUAGE 'plpgsql' VOLATILE AS $$
1482 BEGIN
1483 IF NEW."issue_id" ISNULL THEN
1484 SELECT "issue_id" INTO NEW."issue_id"
1485 FROM "initiative" WHERE "id" = NEW."initiative_id";
1486 END IF;
1487 RETURN NEW;
1488 END;
1489 $$;
1491 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1492 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1494 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1495 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1497 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1498 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1499 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1502 CREATE FUNCTION "autofill_initiative_id_trigger"()
1503 RETURNS TRIGGER
1504 LANGUAGE 'plpgsql' VOLATILE AS $$
1505 BEGIN
1506 IF NEW."initiative_id" ISNULL THEN
1507 SELECT "initiative_id" INTO NEW."initiative_id"
1508 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1509 END IF;
1510 RETURN NEW;
1511 END;
1512 $$;
1514 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1515 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1517 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1518 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1522 -----------------------------------------------------
1523 -- Automatic calculation of certain default values --
1524 -----------------------------------------------------
1527 CREATE FUNCTION "copy_timings_trigger"()
1528 RETURNS TRIGGER
1529 LANGUAGE 'plpgsql' VOLATILE AS $$
1530 DECLARE
1531 "policy_row" "policy"%ROWTYPE;
1532 BEGIN
1533 SELECT * INTO "policy_row" FROM "policy"
1534 WHERE "id" = NEW."policy_id";
1535 IF NEW."admission_time" ISNULL THEN
1536 NEW."admission_time" := "policy_row"."admission_time";
1537 END IF;
1538 IF NEW."discussion_time" ISNULL THEN
1539 NEW."discussion_time" := "policy_row"."discussion_time";
1540 END IF;
1541 IF NEW."verification_time" ISNULL THEN
1542 NEW."verification_time" := "policy_row"."verification_time";
1543 END IF;
1544 IF NEW."voting_time" ISNULL THEN
1545 NEW."voting_time" := "policy_row"."voting_time";
1546 END IF;
1547 RETURN NEW;
1548 END;
1549 $$;
1551 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1552 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1554 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1555 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1558 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
1559 RETURNS TRIGGER
1560 LANGUAGE 'plpgsql' VOLATILE AS $$
1561 BEGIN
1562 IF NEW."draft_id" ISNULL THEN
1563 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1564 WHERE "initiative_id" = NEW."initiative_id";
1565 END IF;
1566 RETURN NEW;
1567 END;
1568 $$;
1570 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1571 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
1573 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
1574 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';
1578 ----------------------------------------
1579 -- Automatic creation of dependencies --
1580 ----------------------------------------
1583 CREATE FUNCTION "autocreate_interest_trigger"()
1584 RETURNS TRIGGER
1585 LANGUAGE 'plpgsql' VOLATILE AS $$
1586 BEGIN
1587 IF NOT EXISTS (
1588 SELECT NULL FROM "initiative" JOIN "interest"
1589 ON "initiative"."issue_id" = "interest"."issue_id"
1590 WHERE "initiative"."id" = NEW."initiative_id"
1591 AND "interest"."member_id" = NEW."member_id"
1592 ) THEN
1593 BEGIN
1594 INSERT INTO "interest" ("issue_id", "member_id")
1595 SELECT "issue_id", NEW."member_id"
1596 FROM "initiative" WHERE "id" = NEW."initiative_id";
1597 EXCEPTION WHEN unique_violation THEN END;
1598 END IF;
1599 RETURN NEW;
1600 END;
1601 $$;
1603 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1604 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1606 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1607 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';
1610 CREATE FUNCTION "autocreate_supporter_trigger"()
1611 RETURNS TRIGGER
1612 LANGUAGE 'plpgsql' VOLATILE AS $$
1613 BEGIN
1614 IF NOT EXISTS (
1615 SELECT NULL FROM "suggestion" JOIN "supporter"
1616 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1617 WHERE "suggestion"."id" = NEW."suggestion_id"
1618 AND "supporter"."member_id" = NEW."member_id"
1619 ) THEN
1620 BEGIN
1621 INSERT INTO "supporter" ("initiative_id", "member_id")
1622 SELECT "initiative_id", NEW."member_id"
1623 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1624 EXCEPTION WHEN unique_violation THEN END;
1625 END IF;
1626 RETURN NEW;
1627 END;
1628 $$;
1630 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1631 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1633 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1634 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.';
1638 ------------------------------------------
1639 -- Views and helper functions for views --
1640 ------------------------------------------
1643 CREATE VIEW "unit_delegation" AS
1644 SELECT
1645 "unit"."id" AS "unit_id",
1646 "delegation"."id",
1647 "delegation"."truster_id",
1648 "delegation"."trustee_id",
1649 "delegation"."scope"
1650 FROM "unit"
1651 JOIN "delegation"
1652 ON "delegation"."unit_id" = "unit"."id"
1653 JOIN "member"
1654 ON "delegation"."truster_id" = "member"."id"
1655 JOIN "privilege"
1656 ON "delegation"."unit_id" = "privilege"."unit_id"
1657 AND "delegation"."truster_id" = "privilege"."member_id"
1658 WHERE "member"."active" AND "privilege"."voting_right";
1660 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
1663 CREATE VIEW "area_delegation" AS
1664 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1665 "area"."id" AS "area_id",
1666 "delegation"."id",
1667 "delegation"."truster_id",
1668 "delegation"."trustee_id",
1669 "delegation"."scope"
1670 FROM "area"
1671 JOIN "delegation"
1672 ON "delegation"."unit_id" = "area"."unit_id"
1673 OR "delegation"."area_id" = "area"."id"
1674 JOIN "member"
1675 ON "delegation"."truster_id" = "member"."id"
1676 JOIN "privilege"
1677 ON "area"."unit_id" = "privilege"."unit_id"
1678 AND "delegation"."truster_id" = "privilege"."member_id"
1679 WHERE "member"."active" AND "privilege"."voting_right"
1680 ORDER BY
1681 "area"."id",
1682 "delegation"."truster_id",
1683 "delegation"."scope" DESC;
1685 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
1688 CREATE VIEW "issue_delegation" AS
1689 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1690 "issue"."id" AS "issue_id",
1691 "delegation"."id",
1692 "delegation"."truster_id",
1693 "delegation"."trustee_id",
1694 "delegation"."scope"
1695 FROM "issue"
1696 JOIN "area"
1697 ON "area"."id" = "issue"."area_id"
1698 JOIN "delegation"
1699 ON "delegation"."unit_id" = "area"."unit_id"
1700 OR "delegation"."area_id" = "area"."id"
1701 OR "delegation"."issue_id" = "issue"."id"
1702 JOIN "member"
1703 ON "delegation"."truster_id" = "member"."id"
1704 JOIN "privilege"
1705 ON "area"."unit_id" = "privilege"."unit_id"
1706 AND "delegation"."truster_id" = "privilege"."member_id"
1707 WHERE "member"."active" AND "privilege"."voting_right"
1708 ORDER BY
1709 "issue"."id",
1710 "delegation"."truster_id",
1711 "delegation"."scope" DESC;
1713 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
1716 CREATE FUNCTION "membership_weight_with_skipping"
1717 ( "area_id_p" "area"."id"%TYPE,
1718 "member_id_p" "member"."id"%TYPE,
1719 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1720 RETURNS INT4
1721 LANGUAGE 'plpgsql' STABLE AS $$
1722 DECLARE
1723 "sum_v" INT4;
1724 "delegation_row" "area_delegation"%ROWTYPE;
1725 BEGIN
1726 "sum_v" := 1;
1727 FOR "delegation_row" IN
1728 SELECT "area_delegation".*
1729 FROM "area_delegation" LEFT JOIN "membership"
1730 ON "membership"."area_id" = "area_id_p"
1731 AND "membership"."member_id" = "area_delegation"."truster_id"
1732 WHERE "area_delegation"."area_id" = "area_id_p"
1733 AND "area_delegation"."trustee_id" = "member_id_p"
1734 AND "membership"."member_id" ISNULL
1735 LOOP
1736 IF NOT
1737 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1738 THEN
1739 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1740 "area_id_p",
1741 "delegation_row"."truster_id",
1742 "skip_member_ids_p" || "delegation_row"."truster_id"
1743 );
1744 END IF;
1745 END LOOP;
1746 RETURN "sum_v";
1747 END;
1748 $$;
1750 COMMENT ON FUNCTION "membership_weight_with_skipping"
1751 ( "area"."id"%TYPE,
1752 "member"."id"%TYPE,
1753 INT4[] )
1754 IS 'Helper function for "membership_weight" function';
1757 CREATE FUNCTION "membership_weight"
1758 ( "area_id_p" "area"."id"%TYPE,
1759 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1760 RETURNS INT4
1761 LANGUAGE 'plpgsql' STABLE AS $$
1762 BEGIN
1763 RETURN "membership_weight_with_skipping"(
1764 "area_id_p",
1765 "member_id_p",
1766 ARRAY["member_id_p"]
1767 );
1768 END;
1769 $$;
1771 COMMENT ON FUNCTION "membership_weight"
1772 ( "area"."id"%TYPE,
1773 "member"."id"%TYPE )
1774 IS 'Calculates the potential voting weight of a member in a given area';
1777 CREATE VIEW "member_count_view" AS
1778 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1780 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1783 CREATE VIEW "unit_member_count" AS
1784 SELECT
1785 "unit"."id" AS "unit_id",
1786 sum("member"."id") AS "member_count"
1787 FROM "unit"
1788 LEFT JOIN "privilege"
1789 ON "privilege"."unit_id" = "unit"."id"
1790 AND "privilege"."voting_right"
1791 LEFT JOIN "member"
1792 ON "member"."id" = "privilege"."member_id"
1793 AND "member"."active"
1794 GROUP BY "unit"."id";
1796 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
1799 CREATE VIEW "area_member_count" AS
1800 SELECT
1801 "area"."id" AS "area_id",
1802 count("member"."id") AS "direct_member_count",
1803 coalesce(
1804 sum(
1805 CASE WHEN "member"."id" NOTNULL THEN
1806 "membership_weight"("area"."id", "member"."id")
1807 ELSE 0 END
1808 )
1809 ) AS "member_weight",
1810 coalesce(
1811 sum(
1812 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
1813 "membership_weight"("area"."id", "member"."id")
1814 ELSE 0 END
1815 )
1816 ) AS "autoreject_weight"
1817 FROM "area"
1818 LEFT JOIN "membership"
1819 ON "area"."id" = "membership"."area_id"
1820 LEFT JOIN "privilege"
1821 ON "privilege"."unit_id" = "area"."unit_id"
1822 AND "privilege"."member_id" = "membership"."member_id"
1823 AND "privilege"."voting_right"
1824 LEFT JOIN "member"
1825 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
1826 AND "member"."active"
1827 GROUP BY "area"."id";
1829 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count", "member_weight" and "autoreject_weight" columns of table "area"';
1832 CREATE VIEW "opening_draft" AS
1833 SELECT "draft".* FROM (
1834 SELECT
1835 "initiative"."id" AS "initiative_id",
1836 min("draft"."id") AS "draft_id"
1837 FROM "initiative" JOIN "draft"
1838 ON "initiative"."id" = "draft"."initiative_id"
1839 GROUP BY "initiative"."id"
1840 ) AS "subquery"
1841 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1843 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1846 CREATE VIEW "current_draft" AS
1847 SELECT "draft".* FROM (
1848 SELECT
1849 "initiative"."id" AS "initiative_id",
1850 max("draft"."id") AS "draft_id"
1851 FROM "initiative" JOIN "draft"
1852 ON "initiative"."id" = "draft"."initiative_id"
1853 GROUP BY "initiative"."id"
1854 ) AS "subquery"
1855 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1857 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1860 CREATE VIEW "critical_opinion" AS
1861 SELECT * FROM "opinion"
1862 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
1863 OR ("degree" = -2 AND "fulfilled" = TRUE);
1865 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
1868 CREATE VIEW "battle_view" AS
1869 SELECT
1870 "issue"."id" AS "issue_id",
1871 "winning_initiative"."id" AS "winning_initiative_id",
1872 "losing_initiative"."id" AS "losing_initiative_id",
1873 sum(
1874 CASE WHEN
1875 coalesce("better_vote"."grade", 0) >
1876 coalesce("worse_vote"."grade", 0)
1877 THEN "direct_voter"."weight" ELSE 0 END
1878 ) AS "count"
1879 FROM "issue"
1880 LEFT JOIN "direct_voter"
1881 ON "issue"."id" = "direct_voter"."issue_id"
1882 JOIN "initiative" AS "winning_initiative"
1883 ON "issue"."id" = "winning_initiative"."issue_id"
1884 AND "winning_initiative"."agreed"
1885 JOIN "initiative" AS "losing_initiative"
1886 ON "issue"."id" = "losing_initiative"."issue_id"
1887 AND "losing_initiative"."agreed"
1888 LEFT JOIN "vote" AS "better_vote"
1889 ON "direct_voter"."member_id" = "better_vote"."member_id"
1890 AND "winning_initiative"."id" = "better_vote"."initiative_id"
1891 LEFT JOIN "vote" AS "worse_vote"
1892 ON "direct_voter"."member_id" = "worse_vote"."member_id"
1893 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
1894 WHERE "issue"."closed" NOTNULL
1895 AND "issue"."cleaned" ISNULL
1896 AND "winning_initiative"."id" != "losing_initiative"."id"
1897 GROUP BY
1898 "issue"."id",
1899 "winning_initiative"."id",
1900 "losing_initiative"."id";
1902 COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative to another; Used to fill "battle" table';
1905 CREATE VIEW "expired_session" AS
1906 SELECT * FROM "session" WHERE now() > "expiry";
1908 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
1909 DELETE FROM "session" WHERE "ident" = OLD."ident";
1911 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
1912 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
1915 CREATE VIEW "open_issue" AS
1916 SELECT * FROM "issue" WHERE "closed" ISNULL;
1918 COMMENT ON VIEW "open_issue" IS 'All open issues';
1921 CREATE VIEW "issue_with_ranks_missing" AS
1922 SELECT * FROM "issue"
1923 WHERE "fully_frozen" NOTNULL
1924 AND "closed" NOTNULL
1925 AND "ranks_available" = FALSE;
1927 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
1930 CREATE VIEW "member_contingent" AS
1931 SELECT
1932 "member"."id" AS "member_id",
1933 "contingent"."time_frame",
1934 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
1935 (
1936 SELECT count(1) FROM "draft"
1937 WHERE "draft"."author_id" = "member"."id"
1938 AND "draft"."created" > now() - "contingent"."time_frame"
1939 ) + (
1940 SELECT count(1) FROM "suggestion"
1941 WHERE "suggestion"."author_id" = "member"."id"
1942 AND "suggestion"."created" > now() - "contingent"."time_frame"
1943 )
1944 ELSE NULL END AS "text_entry_count",
1945 "contingent"."text_entry_limit",
1946 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
1947 SELECT count(1) FROM "opening_draft"
1948 WHERE "opening_draft"."author_id" = "member"."id"
1949 AND "opening_draft"."created" > now() - "contingent"."time_frame"
1950 ) ELSE NULL END AS "initiative_count",
1951 "contingent"."initiative_limit"
1952 FROM "member" CROSS JOIN "contingent";
1954 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
1956 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
1957 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
1960 CREATE VIEW "member_contingent_left" AS
1961 SELECT
1962 "member_id",
1963 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
1964 max("initiative_limit" - "initiative_count") AS "initiatives_left"
1965 FROM "member_contingent" GROUP BY "member_id";
1967 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.';
1970 CREATE VIEW "event_seen_by_member" AS
1971 SELECT
1972 "member"."id" AS "seen_by_member_id",
1973 CASE WHEN "event"."state" IN (
1974 'voting',
1975 'finished_without_winner',
1976 'finished_with_winner'
1977 ) THEN
1978 'voting'::"notify_level"
1979 ELSE
1980 CASE WHEN "event"."state" IN (
1981 'verification',
1982 'canceled_after_revocation_during_verification',
1983 'canceled_no_initiative_admitted'
1984 ) THEN
1985 'verification'::"notify_level"
1986 ELSE
1987 CASE WHEN "event"."state" IN (
1988 'discussion',
1989 'canceled_after_revocation_during_discussion'
1990 ) THEN
1991 'discussion'::"notify_level"
1992 ELSE
1993 'all'::"notify_level"
1994 END
1995 END
1996 END AS "notify_level",
1997 "event".*
1998 FROM "member" CROSS JOIN "event"
1999 LEFT JOIN "issue"
2000 ON "event"."issue_id" = "issue"."id"
2001 LEFT JOIN "membership"
2002 ON "member"."id" = "membership"."member_id"
2003 AND "issue"."area_id" = "membership"."area_id"
2004 LEFT JOIN "interest"
2005 ON "member"."id" = "interest"."member_id"
2006 AND "event"."issue_id" = "interest"."issue_id"
2007 LEFT JOIN "supporter"
2008 ON "member"."id" = "supporter"."member_id"
2009 AND "event"."initiative_id" = "supporter"."initiative_id"
2010 LEFT JOIN "ignored_member"
2011 ON "member"."id" = "ignored_member"."member_id"
2012 AND "event"."member_id" = "ignored_member"."other_member_id"
2013 LEFT JOIN "ignored_initiative"
2014 ON "member"."id" = "ignored_initiative"."member_id"
2015 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2016 WHERE (
2017 "supporter"."member_id" NOTNULL OR
2018 "interest"."member_id" NOTNULL OR
2019 ( "membership"."member_id" NOTNULL AND
2020 "event"."event" IN (
2021 'issue_state_changed',
2022 'initiative_created_in_new_issue',
2023 'initiative_created_in_existing_issue',
2024 'initiative_revoked' ) ) )
2025 AND "ignored_member"."member_id" ISNULL
2026 AND "ignored_initiative"."member_id" ISNULL;
2028 COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support';
2031 CREATE VIEW "pending_notification" AS
2032 SELECT
2033 "member"."id" AS "seen_by_member_id",
2034 "event".*
2035 FROM "member" CROSS JOIN "event"
2036 LEFT JOIN "issue"
2037 ON "event"."issue_id" = "issue"."id"
2038 LEFT JOIN "membership"
2039 ON "member"."id" = "membership"."member_id"
2040 AND "issue"."area_id" = "membership"."area_id"
2041 LEFT JOIN "interest"
2042 ON "member"."id" = "interest"."member_id"
2043 AND "event"."issue_id" = "interest"."issue_id"
2044 LEFT JOIN "supporter"
2045 ON "member"."id" = "supporter"."member_id"
2046 AND "event"."initiative_id" = "supporter"."initiative_id"
2047 LEFT JOIN "ignored_member"
2048 ON "member"."id" = "ignored_member"."member_id"
2049 AND "event"."member_id" = "ignored_member"."other_member_id"
2050 LEFT JOIN "ignored_initiative"
2051 ON "member"."id" = "ignored_initiative"."member_id"
2052 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2053 WHERE (
2054 "member"."notify_event_id" ISNULL OR
2055 ( "member"."notify_event_id" NOTNULL AND
2056 "member"."notify_event_id" < "event"."id" ) )
2057 AND (
2058 ( "member"."notify_level" >= 'all' ) OR
2059 ( "member"."notify_level" >= 'voting' AND
2060 "event"."state" IN (
2061 'voting',
2062 'finished_without_winner',
2063 'finished_with_winner' ) ) OR
2064 ( "member"."notify_level" >= 'verification' AND
2065 "event"."state" IN (
2066 'verification',
2067 'canceled_after_revocation_during_verification',
2068 'canceled_no_initiative_admitted' ) ) OR
2069 ( "member"."notify_level" >= 'discussion' AND
2070 "event"."state" IN (
2071 'discussion',
2072 'canceled_after_revocation_during_discussion' ) ) )
2073 AND (
2074 "supporter"."member_id" NOTNULL OR
2075 "interest"."member_id" NOTNULL OR
2076 ( "membership"."member_id" NOTNULL AND
2077 "event"."event" IN (
2078 'issue_state_changed',
2079 'initiative_created_in_new_issue',
2080 'initiative_created_in_existing_issue',
2081 'initiative_revoked' ) ) )
2082 AND "ignored_member"."member_id" ISNULL
2083 AND "ignored_initiative"."member_id" ISNULL;
2085 COMMENT ON VIEW "pending_notification" IS 'Events to be sent to "notify_email" address of member referred to by "seen_by_member_id"';
2088 CREATE TYPE "timeline_event" AS ENUM (
2089 'issue_created',
2090 'issue_canceled',
2091 'issue_accepted',
2092 'issue_half_frozen',
2093 'issue_finished_without_voting',
2094 'issue_voting_started',
2095 'issue_finished_after_voting',
2096 'initiative_created',
2097 'initiative_revoked',
2098 'draft_created',
2099 'suggestion_created');
2101 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)';
2104 CREATE VIEW "timeline_issue" AS
2105 SELECT
2106 "created" AS "occurrence",
2107 'issue_created'::"timeline_event" AS "event",
2108 "id" AS "issue_id"
2109 FROM "issue"
2110 UNION ALL
2111 SELECT
2112 "closed" AS "occurrence",
2113 'issue_canceled'::"timeline_event" AS "event",
2114 "id" AS "issue_id"
2115 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
2116 UNION ALL
2117 SELECT
2118 "accepted" AS "occurrence",
2119 'issue_accepted'::"timeline_event" AS "event",
2120 "id" AS "issue_id"
2121 FROM "issue" WHERE "accepted" NOTNULL
2122 UNION ALL
2123 SELECT
2124 "half_frozen" AS "occurrence",
2125 'issue_half_frozen'::"timeline_event" AS "event",
2126 "id" AS "issue_id"
2127 FROM "issue" WHERE "half_frozen" NOTNULL
2128 UNION ALL
2129 SELECT
2130 "fully_frozen" AS "occurrence",
2131 'issue_voting_started'::"timeline_event" AS "event",
2132 "id" AS "issue_id"
2133 FROM "issue"
2134 WHERE "fully_frozen" NOTNULL
2135 AND ("closed" ISNULL OR "closed" != "fully_frozen")
2136 UNION ALL
2137 SELECT
2138 "closed" AS "occurrence",
2139 CASE WHEN "fully_frozen" = "closed" THEN
2140 'issue_finished_without_voting'::"timeline_event"
2141 ELSE
2142 'issue_finished_after_voting'::"timeline_event"
2143 END AS "event",
2144 "id" AS "issue_id"
2145 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
2147 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)';
2150 CREATE VIEW "timeline_initiative" AS
2151 SELECT
2152 "created" AS "occurrence",
2153 'initiative_created'::"timeline_event" AS "event",
2154 "id" AS "initiative_id"
2155 FROM "initiative"
2156 UNION ALL
2157 SELECT
2158 "revoked" AS "occurrence",
2159 'initiative_revoked'::"timeline_event" AS "event",
2160 "id" AS "initiative_id"
2161 FROM "initiative" WHERE "revoked" NOTNULL;
2163 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)';
2166 CREATE VIEW "timeline_draft" AS
2167 SELECT
2168 "created" AS "occurrence",
2169 'draft_created'::"timeline_event" AS "event",
2170 "id" AS "draft_id"
2171 FROM "draft";
2173 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)';
2176 CREATE VIEW "timeline_suggestion" AS
2177 SELECT
2178 "created" AS "occurrence",
2179 'suggestion_created'::"timeline_event" AS "event",
2180 "id" AS "suggestion_id"
2181 FROM "suggestion";
2183 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)';
2186 CREATE VIEW "timeline" AS
2187 SELECT
2188 "occurrence",
2189 "event",
2190 "issue_id",
2191 NULL AS "initiative_id",
2192 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
2193 NULL::INT8 AS "suggestion_id"
2194 FROM "timeline_issue"
2195 UNION ALL
2196 SELECT
2197 "occurrence",
2198 "event",
2199 NULL AS "issue_id",
2200 "initiative_id",
2201 NULL AS "draft_id",
2202 NULL AS "suggestion_id"
2203 FROM "timeline_initiative"
2204 UNION ALL
2205 SELECT
2206 "occurrence",
2207 "event",
2208 NULL AS "issue_id",
2209 NULL AS "initiative_id",
2210 "draft_id",
2211 NULL AS "suggestion_id"
2212 FROM "timeline_draft"
2213 UNION ALL
2214 SELECT
2215 "occurrence",
2216 "event",
2217 NULL AS "issue_id",
2218 NULL AS "initiative_id",
2219 NULL AS "draft_id",
2220 "suggestion_id"
2221 FROM "timeline_suggestion";
2223 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)';
2227 --------------------------------------------------
2228 -- Set returning function for delegation chains --
2229 --------------------------------------------------
2232 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
2233 ('first', 'intermediate', 'last', 'repetition');
2235 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
2238 CREATE TYPE "delegation_chain_row" AS (
2239 "index" INT4,
2240 "member_id" INT4,
2241 "member_valid" BOOLEAN,
2242 "participation" BOOLEAN,
2243 "overridden" BOOLEAN,
2244 "scope_in" "delegation_scope",
2245 "scope_out" "delegation_scope",
2246 "disabled_out" BOOLEAN,
2247 "loop" "delegation_chain_loop_tag" );
2249 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
2251 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
2252 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';
2253 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
2254 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
2255 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
2256 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
2257 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
2260 CREATE FUNCTION "delegation_chain"
2261 ( "member_id_p" "member"."id"%TYPE,
2262 "unit_id_p" "unit"."id"%TYPE,
2263 "area_id_p" "area"."id"%TYPE,
2264 "issue_id_p" "issue"."id"%TYPE,
2265 "simulate_trustee_id_p" "member"."id"%TYPE )
2266 RETURNS SETOF "delegation_chain_row"
2267 LANGUAGE 'plpgsql' STABLE AS $$
2268 DECLARE
2269 "scope_v" "delegation_scope";
2270 "unit_id_v" "unit"."id"%TYPE;
2271 "area_id_v" "area"."id"%TYPE;
2272 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2273 "loop_member_id_v" "member"."id"%TYPE;
2274 "output_row" "delegation_chain_row";
2275 "output_rows" "delegation_chain_row"[];
2276 "delegation_row" "delegation"%ROWTYPE;
2277 "row_count" INT4;
2278 "i" INT4;
2279 "loop_v" BOOLEAN;
2280 BEGIN
2281 IF
2282 "unit_id_p" NOTNULL AND
2283 "area_id_p" ISNULL AND
2284 "issue_id_p" ISNULL
2285 THEN
2286 "scope_v" := 'unit';
2287 "unit_id_v" := "unit_id_p";
2288 ELSIF
2289 "unit_id_p" ISNULL AND
2290 "area_id_p" NOTNULL AND
2291 "issue_id_p" ISNULL
2292 THEN
2293 "scope_v" := 'area';
2294 "area_id_v" := "area_id_p";
2295 SELECT "unit_id" INTO "unit_id_v"
2296 FROM "area" WHERE "id" = "area_id_v";
2297 ELSIF
2298 "unit_id_p" ISNULL AND
2299 "area_id_p" ISNULL AND
2300 "issue_id_p" NOTNULL
2301 THEN
2302 "scope_v" := 'issue';
2303 SELECT "area_id" INTO "area_id_v"
2304 FROM "issue" WHERE "id" = "issue_id_p";
2305 SELECT "unit_id" INTO "unit_id_v"
2306 FROM "area" WHERE "id" = "area_id_v";
2307 ELSE
2308 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2309 END IF;
2310 "visited_member_ids" := '{}';
2311 "loop_member_id_v" := NULL;
2312 "output_rows" := '{}';
2313 "output_row"."index" := 0;
2314 "output_row"."member_id" := "member_id_p";
2315 "output_row"."member_valid" := TRUE;
2316 "output_row"."participation" := FALSE;
2317 "output_row"."overridden" := FALSE;
2318 "output_row"."disabled_out" := FALSE;
2319 "output_row"."scope_out" := NULL;
2320 LOOP
2321 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2322 "loop_member_id_v" := "output_row"."member_id";
2323 ELSE
2324 "visited_member_ids" :=
2325 "visited_member_ids" || "output_row"."member_id";
2326 END IF;
2327 IF "output_row"."participation" THEN
2328 "output_row"."overridden" := TRUE;
2329 END IF;
2330 "output_row"."scope_in" := "output_row"."scope_out";
2331 IF EXISTS (
2332 SELECT NULL FROM "member" JOIN "privilege"
2333 ON "privilege"."member_id" = "member"."id"
2334 AND "privilege"."unit_id" = "unit_id_v"
2335 WHERE "id" = "output_row"."member_id"
2336 AND "member"."active" AND "privilege"."voting_right"
2337 ) THEN
2338 IF "scope_v" = 'unit' THEN
2339 SELECT * INTO "delegation_row" FROM "delegation"
2340 WHERE "truster_id" = "output_row"."member_id"
2341 AND "unit_id" = "unit_id_v";
2342 ELSIF "scope_v" = 'area' THEN
2343 "output_row"."participation" := EXISTS (
2344 SELECT NULL FROM "membership"
2345 WHERE "area_id" = "area_id_p"
2346 AND "member_id" = "output_row"."member_id"
2347 );
2348 SELECT * INTO "delegation_row" FROM "delegation"
2349 WHERE "truster_id" = "output_row"."member_id"
2350 AND (
2351 "unit_id" = "unit_id_v" OR
2352 "area_id" = "area_id_v"
2353 )
2354 ORDER BY "scope" DESC;
2355 ELSIF "scope_v" = 'issue' THEN
2356 "output_row"."participation" := EXISTS (
2357 SELECT NULL FROM "interest"
2358 WHERE "issue_id" = "issue_id_p"
2359 AND "member_id" = "output_row"."member_id"
2360 );
2361 SELECT * INTO "delegation_row" FROM "delegation"
2362 WHERE "truster_id" = "output_row"."member_id"
2363 AND (
2364 "unit_id" = "unit_id_v" OR
2365 "area_id" = "area_id_v" OR
2366 "issue_id" = "issue_id_p"
2367 )
2368 ORDER BY "scope" DESC;
2369 END IF;
2370 ELSE
2371 "output_row"."member_valid" := FALSE;
2372 "output_row"."participation" := FALSE;
2373 "output_row"."scope_out" := NULL;
2374 "delegation_row" := ROW(NULL);
2375 END IF;
2376 IF
2377 "output_row"."member_id" = "member_id_p" AND
2378 "simulate_trustee_id_p" NOTNULL
2379 THEN
2380 "output_row"."scope_out" := "scope_v";
2381 "output_rows" := "output_rows" || "output_row";
2382 "output_row"."member_id" := "simulate_trustee_id_p";
2383 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2384 "output_row"."scope_out" := "delegation_row"."scope";
2385 "output_rows" := "output_rows" || "output_row";
2386 "output_row"."member_id" := "delegation_row"."trustee_id";
2387 ELSIF "delegation_row"."scope" NOTNULL THEN
2388 "output_row"."scope_out" := "delegation_row"."scope";
2389 "output_row"."disabled_out" := TRUE;
2390 "output_rows" := "output_rows" || "output_row";
2391 EXIT;
2392 ELSE
2393 "output_row"."scope_out" := NULL;
2394 "output_rows" := "output_rows" || "output_row";
2395 EXIT;
2396 END IF;
2397 EXIT WHEN "loop_member_id_v" NOTNULL;
2398 "output_row"."index" := "output_row"."index" + 1;
2399 END LOOP;
2400 "row_count" := array_upper("output_rows", 1);
2401 "i" := 1;
2402 "loop_v" := FALSE;
2403 LOOP
2404 "output_row" := "output_rows"["i"];
2405 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2406 IF "loop_v" THEN
2407 IF "i" + 1 = "row_count" THEN
2408 "output_row"."loop" := 'last';
2409 ELSIF "i" = "row_count" THEN
2410 "output_row"."loop" := 'repetition';
2411 ELSE
2412 "output_row"."loop" := 'intermediate';
2413 END IF;
2414 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2415 "output_row"."loop" := 'first';
2416 "loop_v" := TRUE;
2417 END IF;
2418 IF "scope_v" = 'unit' THEN
2419 "output_row"."participation" := NULL;
2420 END IF;
2421 RETURN NEXT "output_row";
2422 "i" := "i" + 1;
2423 END LOOP;
2424 RETURN;
2425 END;
2426 $$;
2428 COMMENT ON FUNCTION "delegation_chain"
2429 ( "member"."id"%TYPE,
2430 "unit"."id"%TYPE,
2431 "area"."id"%TYPE,
2432 "issue"."id"%TYPE,
2433 "member"."id"%TYPE )
2434 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
2437 CREATE FUNCTION "delegation_chain"
2438 ( "member_id_p" "member"."id"%TYPE,
2439 "unit_id_p" "unit"."id"%TYPE,
2440 "area_id_p" "area"."id"%TYPE,
2441 "issue_id_p" "issue"."id"%TYPE )
2442 RETURNS SETOF "delegation_chain_row"
2443 LANGUAGE 'plpgsql' STABLE AS $$
2444 DECLARE
2445 "result_row" "delegation_chain_row";
2446 BEGIN
2447 FOR "result_row" IN
2448 SELECT * FROM "delegation_chain"(
2449 "member_id_p", "area_id_p", "issue_id_p", NULL
2450 )
2451 LOOP
2452 RETURN NEXT "result_row";
2453 END LOOP;
2454 RETURN;
2455 END;
2456 $$;
2458 COMMENT ON FUNCTION "delegation_chain"
2459 ( "member"."id"%TYPE,
2460 "unit"."id"%TYPE,
2461 "area"."id"%TYPE,
2462 "issue"."id"%TYPE )
2463 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
2467 ------------------------------
2468 -- Comparison by vote count --
2469 ------------------------------
2471 CREATE FUNCTION "vote_ratio"
2472 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
2473 "negative_votes_p" "initiative"."negative_votes"%TYPE )
2474 RETURNS FLOAT8
2475 LANGUAGE 'plpgsql' STABLE AS $$
2476 BEGIN
2477 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
2478 RETURN
2479 "positive_votes_p"::FLOAT8 /
2480 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
2481 ELSIF "positive_votes_p" > 0 THEN
2482 RETURN "positive_votes_p";
2483 ELSIF "negative_votes_p" > 0 THEN
2484 RETURN 1 - "negative_votes_p";
2485 ELSE
2486 RETURN 0.5;
2487 END IF;
2488 END;
2489 $$;
2491 COMMENT ON FUNCTION "vote_ratio"
2492 ( "initiative"."positive_votes"%TYPE,
2493 "initiative"."negative_votes"%TYPE )
2494 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.';
2498 ------------------------------------------------
2499 -- Locking for snapshots and voting procedure --
2500 ------------------------------------------------
2503 CREATE FUNCTION "share_row_lock_issue_trigger"()
2504 RETURNS TRIGGER
2505 LANGUAGE 'plpgsql' VOLATILE AS $$
2506 BEGIN
2507 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2508 PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;
2509 END IF;
2510 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2511 PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;
2512 RETURN NEW;
2513 ELSE
2514 RETURN OLD;
2515 END IF;
2516 END;
2517 $$;
2519 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';
2522 CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()
2523 RETURNS TRIGGER
2524 LANGUAGE 'plpgsql' VOLATILE AS $$
2525 BEGIN
2526 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2527 PERFORM NULL FROM "issue"
2528 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2529 WHERE "initiative"."id" = OLD."initiative_id"
2530 FOR SHARE OF "issue";
2531 END IF;
2532 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2533 PERFORM NULL FROM "issue"
2534 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2535 WHERE "initiative"."id" = NEW."initiative_id"
2536 FOR SHARE OF "issue";
2537 RETURN NEW;
2538 ELSE
2539 RETURN OLD;
2540 END IF;
2541 END;
2542 $$;
2544 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';
2547 CREATE TRIGGER "share_row_lock_issue"
2548 BEFORE INSERT OR UPDATE OR DELETE ON "initiative"
2549 FOR EACH ROW EXECUTE PROCEDURE
2550 "share_row_lock_issue_trigger"();
2552 CREATE TRIGGER "share_row_lock_issue"
2553 BEFORE INSERT OR UPDATE OR DELETE ON "interest"
2554 FOR EACH ROW EXECUTE PROCEDURE
2555 "share_row_lock_issue_trigger"();
2557 CREATE TRIGGER "share_row_lock_issue"
2558 BEFORE INSERT OR UPDATE OR DELETE ON "supporter"
2559 FOR EACH ROW EXECUTE PROCEDURE
2560 "share_row_lock_issue_trigger"();
2562 CREATE TRIGGER "share_row_lock_issue_via_initiative"
2563 BEFORE INSERT OR UPDATE OR DELETE ON "opinion"
2564 FOR EACH ROW EXECUTE PROCEDURE
2565 "share_row_lock_issue_via_initiative_trigger"();
2567 CREATE TRIGGER "share_row_lock_issue"
2568 BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter"
2569 FOR EACH ROW EXECUTE PROCEDURE
2570 "share_row_lock_issue_trigger"();
2572 CREATE TRIGGER "share_row_lock_issue"
2573 BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter"
2574 FOR EACH ROW EXECUTE PROCEDURE
2575 "share_row_lock_issue_trigger"();
2577 CREATE TRIGGER "share_row_lock_issue"
2578 BEFORE INSERT OR UPDATE OR DELETE ON "vote"
2579 FOR EACH ROW EXECUTE PROCEDURE
2580 "share_row_lock_issue_trigger"();
2582 COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function';
2583 COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function';
2584 COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function';
2585 COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function';
2586 COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function';
2587 COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function';
2588 COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function';
2591 CREATE FUNCTION "lock_issue"
2592 ( "issue_id_p" "issue"."id"%TYPE )
2593 RETURNS VOID
2594 LANGUAGE 'plpgsql' VOLATILE AS $$
2595 BEGIN
2596 LOCK TABLE "member" IN SHARE MODE;
2597 LOCK TABLE "privilege" IN SHARE MODE;
2598 LOCK TABLE "membership" IN SHARE MODE;
2599 LOCK TABLE "policy" IN SHARE MODE;
2600 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2601 -- NOTE: The row-level exclusive lock in combination with the
2602 -- share_row_lock_issue(_via_initiative)_trigger functions (which
2603 -- acquire a row-level share lock on the issue) ensure that no data
2604 -- is changed, which could affect calculation of snapshots or
2605 -- counting of votes. Table "delegation" must be table-level-locked,
2606 -- as it also contains issue- and global-scope delegations.
2607 LOCK TABLE "delegation" IN SHARE MODE;
2608 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
2609 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
2610 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
2611 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
2612 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
2613 RETURN;
2614 END;
2615 $$;
2617 COMMENT ON FUNCTION "lock_issue"
2618 ( "issue"."id"%TYPE )
2619 IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';
2623 ------------------------------------------------------------------------
2624 -- Regular tasks, except calculcation of snapshots and voting results --
2625 ------------------------------------------------------------------------
2627 CREATE FUNCTION "check_last_login"()
2628 RETURNS VOID
2629 LANGUAGE 'plpgsql' VOLATILE AS $$
2630 DECLARE
2631 "system_setting_row" "system_setting"%ROWTYPE;
2632 BEGIN
2633 SELECT * INTO "system_setting_row" FROM "system_setting";
2634 LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
2635 UPDATE "member" SET "last_login_public" = "last_login"::date
2636 FROM (
2637 SELECT DISTINCT "member"."id"
2638 FROM "member" LEFT JOIN "member_history"
2639 ON "member"."id" = "member_history"."member_id"
2640 WHERE "member"."last_login"::date < 'today' OR (
2641 "member_history"."until"::date >= 'today' AND
2642 "member_history"."active" = FALSE AND "member"."active" = TRUE
2643 )
2644 ) AS "subquery"
2645 WHERE "member"."id" = "subquery"."id";
2646 IF "system_setting_row"."member_ttl" NOTNULL THEN
2647 UPDATE "member" SET "active" = FALSE
2648 WHERE "active" = TRUE
2649 AND "last_login"::date < 'today'
2650 AND "last_login_public" <
2651 (now() - "system_setting_row"."member_ttl")::date;
2652 END IF;
2653 RETURN;
2654 END;
2655 $$;
2657 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).';
2660 CREATE FUNCTION "calculate_member_counts"()
2661 RETURNS VOID
2662 LANGUAGE 'plpgsql' VOLATILE AS $$
2663 BEGIN
2664 LOCK TABLE "member" IN SHARE MODE;
2665 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
2666 LOCK TABLE "unit" IN EXCLUSIVE MODE;
2667 LOCK TABLE "area" IN EXCLUSIVE MODE;
2668 LOCK TABLE "privilege" IN SHARE MODE;
2669 LOCK TABLE "membership" IN SHARE MODE;
2670 DELETE FROM "member_count";
2671 INSERT INTO "member_count" ("total_count")
2672 SELECT "total_count" FROM "member_count_view";
2673 UPDATE "unit" SET "member_count" = "view"."member_count"
2674 FROM "unit_member_count" AS "view"
2675 WHERE "view"."unit_id" = "unit"."id";
2676 UPDATE "area" SET
2677 "direct_member_count" = "view"."direct_member_count",
2678 "member_weight" = "view"."member_weight",
2679 "autoreject_weight" = "view"."autoreject_weight"
2680 FROM "area_member_count" AS "view"
2681 WHERE "view"."area_id" = "area"."id";
2682 RETURN;
2683 END;
2684 $$;
2686 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"';
2690 ------------------------------
2691 -- Calculation of snapshots --
2692 ------------------------------
2694 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
2695 ( "issue_id_p" "issue"."id"%TYPE,
2696 "member_id_p" "member"."id"%TYPE,
2697 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2698 RETURNS "direct_population_snapshot"."weight"%TYPE
2699 LANGUAGE 'plpgsql' VOLATILE AS $$
2700 DECLARE
2701 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2702 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
2703 "weight_v" INT4;
2704 "sub_weight_v" INT4;
2705 BEGIN
2706 "weight_v" := 0;
2707 FOR "issue_delegation_row" IN
2708 SELECT * FROM "issue_delegation"
2709 WHERE "trustee_id" = "member_id_p"
2710 AND "issue_id" = "issue_id_p"
2711 LOOP
2712 IF NOT EXISTS (
2713 SELECT NULL FROM "direct_population_snapshot"
2714 WHERE "issue_id" = "issue_id_p"
2715 AND "event" = 'periodic'
2716 AND "member_id" = "issue_delegation_row"."truster_id"
2717 ) AND NOT EXISTS (
2718 SELECT NULL FROM "delegating_population_snapshot"
2719 WHERE "issue_id" = "issue_id_p"
2720 AND "event" = 'periodic'
2721 AND "member_id" = "issue_delegation_row"."truster_id"
2722 ) THEN
2723 "delegate_member_ids_v" :=
2724 "member_id_p" || "delegate_member_ids_p";
2725 INSERT INTO "delegating_population_snapshot" (
2726 "issue_id",
2727 "event",
2728 "member_id",
2729 "scope",
2730 "delegate_member_ids"
2731 ) VALUES (
2732 "issue_id_p",
2733 'periodic',
2734 "issue_delegation_row"."truster_id",
2735 "issue_delegation_row"."scope",
2736 "delegate_member_ids_v"
2737 );
2738 "sub_weight_v" := 1 +
2739 "weight_of_added_delegations_for_population_snapshot"(
2740 "issue_id_p",
2741 "issue_delegation_row"."truster_id",
2742 "delegate_member_ids_v"
2743 );
2744 UPDATE "delegating_population_snapshot"
2745 SET "weight" = "sub_weight_v"
2746 WHERE "issue_id" = "issue_id_p"
2747 AND "event" = 'periodic'
2748 AND "member_id" = "issue_delegation_row"."truster_id";
2749 "weight_v" := "weight_v" + "sub_weight_v";
2750 END IF;
2751 END LOOP;
2752 RETURN "weight_v";
2753 END;
2754 $$;
2756 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
2757 ( "issue"."id"%TYPE,
2758 "member"."id"%TYPE,
2759 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2760 IS 'Helper function for "create_population_snapshot" function';
2763 CREATE FUNCTION "create_population_snapshot"
2764 ( "issue_id_p" "issue"."id"%TYPE )
2765 RETURNS VOID
2766 LANGUAGE 'plpgsql' VOLATILE AS $$
2767 DECLARE
2768 "member_id_v" "member"."id"%TYPE;
2769 BEGIN
2770 DELETE FROM "direct_population_snapshot"
2771 WHERE "issue_id" = "issue_id_p"
2772 AND "event" = 'periodic';
2773 DELETE FROM "delegating_population_snapshot"
2774 WHERE "issue_id" = "issue_id_p"
2775 AND "event" = 'periodic';
2776 INSERT INTO "direct_population_snapshot"
2777 ("issue_id", "event", "member_id")
2778 SELECT
2779 "issue_id_p" AS "issue_id",
2780 'periodic'::"snapshot_event" AS "event",
2781 "member"."id" AS "member_id"
2782 FROM "issue"
2783 JOIN "area" ON "issue"."area_id" = "area"."id"
2784 JOIN "membership" ON "area"."id" = "membership"."area_id"
2785 JOIN "member" ON "membership"."member_id" = "member"."id"
2786 JOIN "privilege"
2787 ON "privilege"."unit_id" = "area"."unit_id"
2788 AND "privilege"."member_id" = "member"."id"
2789 WHERE "issue"."id" = "issue_id_p"
2790 AND "member"."active" AND "privilege"."voting_right"
2791 UNION
2792 SELECT
2793 "issue_id_p" AS "issue_id",
2794 'periodic'::"snapshot_event" AS "event",
2795 "member"."id" AS "member_id"
2796 FROM "issue"
2797 JOIN "area" ON "issue"."area_id" = "area"."id"
2798 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2799 JOIN "member" ON "interest"."member_id" = "member"."id"
2800 JOIN "privilege"
2801 ON "privilege"."unit_id" = "area"."unit_id"
2802 AND "privilege"."member_id" = "member"."id"
2803 WHERE "issue"."id" = "issue_id_p"
2804 AND "member"."active" AND "privilege"."voting_right";
2805 FOR "member_id_v" IN
2806 SELECT "member_id" FROM "direct_population_snapshot"
2807 WHERE "issue_id" = "issue_id_p"
2808 AND "event" = 'periodic'
2809 LOOP
2810 UPDATE "direct_population_snapshot" SET
2811 "weight" = 1 +
2812 "weight_of_added_delegations_for_population_snapshot"(
2813 "issue_id_p",
2814 "member_id_v",
2815 '{}'
2816 )
2817 WHERE "issue_id" = "issue_id_p"
2818 AND "event" = 'periodic'
2819 AND "member_id" = "member_id_v";
2820 END LOOP;
2821 RETURN;
2822 END;
2823 $$;
2825 COMMENT ON FUNCTION "create_population_snapshot"
2826 ( "issue"."id"%TYPE )
2827 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.';
2830 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2831 ( "issue_id_p" "issue"."id"%TYPE,
2832 "member_id_p" "member"."id"%TYPE,
2833 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2834 RETURNS "direct_interest_snapshot"."weight"%TYPE
2835 LANGUAGE 'plpgsql' VOLATILE AS $$
2836 DECLARE
2837 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2838 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2839 "weight_v" INT4;
2840 "sub_weight_v" INT4;
2841 BEGIN
2842 "weight_v" := 0;
2843 FOR "issue_delegation_row" IN
2844 SELECT * FROM "issue_delegation"
2845 WHERE "trustee_id" = "member_id_p"
2846 AND "issue_id" = "issue_id_p"
2847 LOOP
2848 IF NOT EXISTS (
2849 SELECT NULL FROM "direct_interest_snapshot"
2850 WHERE "issue_id" = "issue_id_p"
2851 AND "event" = 'periodic'
2852 AND "member_id" = "issue_delegation_row"."truster_id"
2853 ) AND NOT EXISTS (
2854 SELECT NULL FROM "delegating_interest_snapshot"
2855 WHERE "issue_id" = "issue_id_p"
2856 AND "event" = 'periodic'
2857 AND "member_id" = "issue_delegation_row"."truster_id"
2858 ) THEN
2859 "delegate_member_ids_v" :=
2860 "member_id_p" || "delegate_member_ids_p";
2861 INSERT INTO "delegating_interest_snapshot" (
2862 "issue_id",
2863 "event",
2864 "member_id",
2865 "scope",
2866 "delegate_member_ids"
2867 ) VALUES (
2868 "issue_id_p",
2869 'periodic',
2870 "issue_delegation_row"."truster_id",
2871 "issue_delegation_row"."scope",
2872 "delegate_member_ids_v"
2873 );
2874 "sub_weight_v" := 1 +
2875 "weight_of_added_delegations_for_interest_snapshot"(
2876 "issue_id_p",
2877 "issue_delegation_row"."truster_id",
2878 "delegate_member_ids_v"
2879 );
2880 UPDATE "delegating_interest_snapshot"
2881 SET "weight" = "sub_weight_v"
2882 WHERE "issue_id" = "issue_id_p"
2883 AND "event" = 'periodic'
2884 AND "member_id" = "issue_delegation_row"."truster_id";
2885 "weight_v" := "weight_v" + "sub_weight_v";
2886 END IF;
2887 END LOOP;
2888 RETURN "weight_v";
2889 END;
2890 $$;
2892 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2893 ( "issue"."id"%TYPE,
2894 "member"."id"%TYPE,
2895 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2896 IS 'Helper function for "create_interest_snapshot" function';
2899 CREATE FUNCTION "create_interest_snapshot"
2900 ( "issue_id_p" "issue"."id"%TYPE )
2901 RETURNS VOID
2902 LANGUAGE 'plpgsql' VOLATILE AS $$
2903 DECLARE
2904 "member_id_v" "member"."id"%TYPE;
2905 BEGIN
2906 DELETE FROM "direct_interest_snapshot"
2907 WHERE "issue_id" = "issue_id_p"
2908 AND "event" = 'periodic';
2909 DELETE FROM "delegating_interest_snapshot"
2910 WHERE "issue_id" = "issue_id_p"
2911 AND "event" = 'periodic';
2912 DELETE FROM "direct_supporter_snapshot"
2913 WHERE "issue_id" = "issue_id_p"
2914 AND "event" = 'periodic';
2915 INSERT INTO "direct_interest_snapshot"
2916 ("issue_id", "event", "member_id", "voting_requested")
2917 SELECT
2918 "issue_id_p" AS "issue_id",
2919 'periodic' AS "event",
2920 "member"."id" AS "member_id",
2921 "interest"."voting_requested"
2922 FROM "issue"
2923 JOIN "area" ON "issue"."area_id" = "area"."id"
2924 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2925 JOIN "member" ON "interest"."member_id" = "member"."id"
2926 JOIN "privilege"
2927 ON "privilege"."unit_id" = "area"."unit_id"
2928 AND "privilege"."member_id" = "member"."id"
2929 WHERE "issue"."id" = "issue_id_p"
2930 AND "member"."active" AND "privilege"."voting_right";
2931 FOR "member_id_v" IN
2932 SELECT "member_id" FROM "direct_interest_snapshot"
2933 WHERE "issue_id" = "issue_id_p"
2934 AND "event" = 'periodic'
2935 LOOP
2936 UPDATE "direct_interest_snapshot" SET
2937 "weight" = 1 +
2938 "weight_of_added_delegations_for_interest_snapshot"(
2939 "issue_id_p",
2940 "member_id_v",
2941 '{}'
2942 )
2943 WHERE "issue_id" = "issue_id_p"
2944 AND "event" = 'periodic'
2945 AND "member_id" = "member_id_v";
2946 END LOOP;
2947 INSERT INTO "direct_supporter_snapshot"
2948 ( "issue_id", "initiative_id", "event", "member_id",
2949 "informed", "satisfied" )
2950 SELECT
2951 "issue_id_p" AS "issue_id",
2952 "initiative"."id" AS "initiative_id",
2953 'periodic' AS "event",
2954 "supporter"."member_id" AS "member_id",
2955 "supporter"."draft_id" = "current_draft"."id" AS "informed",
2956 NOT EXISTS (
2957 SELECT NULL FROM "critical_opinion"
2958 WHERE "initiative_id" = "initiative"."id"
2959 AND "member_id" = "supporter"."member_id"
2960 ) AS "satisfied"
2961 FROM "initiative"
2962 JOIN "supporter"
2963 ON "supporter"."initiative_id" = "initiative"."id"
2964 JOIN "current_draft"
2965 ON "initiative"."id" = "current_draft"."initiative_id"
2966 JOIN "direct_interest_snapshot"
2967 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
2968 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
2969 AND "event" = 'periodic'
2970 WHERE "initiative"."issue_id" = "issue_id_p";
2971 RETURN;
2972 END;
2973 $$;
2975 COMMENT ON FUNCTION "create_interest_snapshot"
2976 ( "issue"."id"%TYPE )
2977 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.';
2980 CREATE FUNCTION "create_snapshot"
2981 ( "issue_id_p" "issue"."id"%TYPE )
2982 RETURNS VOID
2983 LANGUAGE 'plpgsql' VOLATILE AS $$
2984 DECLARE
2985 "initiative_id_v" "initiative"."id"%TYPE;
2986 "suggestion_id_v" "suggestion"."id"%TYPE;
2987 BEGIN
2988 PERFORM "lock_issue"("issue_id_p");
2989 PERFORM "create_population_snapshot"("issue_id_p");
2990 PERFORM "create_interest_snapshot"("issue_id_p");
2991 UPDATE "issue" SET
2992 "snapshot" = now(),
2993 "latest_snapshot_event" = 'periodic',
2994 "population" = (
2995 SELECT coalesce(sum("weight"), 0)
2996 FROM "direct_population_snapshot"
2997 WHERE "issue_id" = "issue_id_p"
2998 AND "event" = 'periodic'
2999 ),
3000 "vote_now" = (
3001 SELECT coalesce(sum("weight"), 0)
3002 FROM "direct_interest_snapshot"
3003 WHERE "issue_id" = "issue_id_p"
3004 AND "event" = 'periodic'
3005 AND "voting_requested" = TRUE
3006 ),
3007 "vote_later" = (
3008 SELECT coalesce(sum("weight"), 0)
3009 FROM "direct_interest_snapshot"
3010 WHERE "issue_id" = "issue_id_p"
3011 AND "event" = 'periodic'
3012 AND "voting_requested" = FALSE
3013 )
3014 WHERE "id" = "issue_id_p";
3015 FOR "initiative_id_v" IN
3016 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
3017 LOOP
3018 UPDATE "initiative" SET
3019 "supporter_count" = (
3020 SELECT coalesce(sum("di"."weight"), 0)
3021 FROM "direct_interest_snapshot" AS "di"
3022 JOIN "direct_supporter_snapshot" AS "ds"
3023 ON "di"."member_id" = "ds"."member_id"
3024 WHERE "di"."issue_id" = "issue_id_p"
3025 AND "di"."event" = 'periodic'
3026 AND "ds"."initiative_id" = "initiative_id_v"
3027 AND "ds"."event" = 'periodic'
3028 ),
3029 "informed_supporter_count" = (
3030 SELECT coalesce(sum("di"."weight"), 0)
3031 FROM "direct_interest_snapshot" AS "di"
3032 JOIN "direct_supporter_snapshot" AS "ds"
3033 ON "di"."member_id" = "ds"."member_id"
3034 WHERE "di"."issue_id" = "issue_id_p"
3035 AND "di"."event" = 'periodic'
3036 AND "ds"."initiative_id" = "initiative_id_v"
3037 AND "ds"."event" = 'periodic'
3038 AND "ds"."informed"
3039 ),
3040 "satisfied_supporter_count" = (
3041 SELECT coalesce(sum("di"."weight"), 0)
3042 FROM "direct_interest_snapshot" AS "di"
3043 JOIN "direct_supporter_snapshot" AS "ds"
3044 ON "di"."member_id" = "ds"."member_id"
3045 WHERE "di"."issue_id" = "issue_id_p"
3046 AND "di"."event" = 'periodic'
3047 AND "ds"."initiative_id" = "initiative_id_v"
3048 AND "ds"."event" = 'periodic'
3049 AND "ds"."satisfied"
3050 ),
3051 "satisfied_informed_supporter_count" = (
3052 SELECT coalesce(sum("di"."weight"), 0)
3053 FROM "direct_interest_snapshot" AS "di"
3054 JOIN "direct_supporter_snapshot" AS "ds"
3055 ON "di"."member_id" = "ds"."member_id"
3056 WHERE "di"."issue_id" = "issue_id_p"
3057 AND "di"."event" = 'periodic'
3058 AND "ds"."initiative_id" = "initiative_id_v"
3059 AND "ds"."event" = 'periodic'
3060 AND "ds"."informed"
3061 AND "ds"."satisfied"
3062 )
3063 WHERE "id" = "initiative_id_v";
3064 FOR "suggestion_id_v" IN
3065 SELECT "id" FROM "suggestion"
3066 WHERE "initiative_id" = "initiative_id_v"
3067 LOOP
3068 UPDATE "suggestion" SET
3069 "minus2_unfulfilled_count" = (
3070 SELECT coalesce(sum("snapshot"."weight"), 0)
3071 FROM "issue" CROSS JOIN "opinion"
3072 JOIN "direct_interest_snapshot" AS "snapshot"
3073 ON "snapshot"."issue_id" = "issue"."id"
3074 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3075 AND "snapshot"."member_id" = "opinion"."member_id"
3076 WHERE "issue"."id" = "issue_id_p"
3077 AND "opinion"."suggestion_id" = "suggestion_id_v"
3078 AND "opinion"."degree" = -2
3079 AND "opinion"."fulfilled" = FALSE
3080 ),
3081 "minus2_fulfilled_count" = (
3082 SELECT coalesce(sum("snapshot"."weight"), 0)
3083 FROM "issue" CROSS JOIN "opinion"
3084 JOIN "direct_interest_snapshot" AS "snapshot"
3085 ON "snapshot"."issue_id" = "issue"."id"
3086 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3087 AND "snapshot"."member_id" = "opinion"."member_id"
3088 WHERE "issue"."id" = "issue_id_p"
3089 AND "opinion"."suggestion_id" = "suggestion_id_v"
3090 AND "opinion"."degree" = -2
3091 AND "opinion"."fulfilled" = TRUE
3092 ),
3093 "minus1_unfulfilled_count" = (
3094 SELECT coalesce(sum("snapshot"."weight"), 0)
3095 FROM "issue" CROSS JOIN "opinion"
3096 JOIN "direct_interest_snapshot" AS "snapshot"
3097 ON "snapshot"."issue_id" = "issue"."id"
3098 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3099 AND "snapshot"."member_id" = "opinion"."member_id"
3100 WHERE "issue"."id" = "issue_id_p"
3101 AND "opinion"."suggestion_id" = "suggestion_id_v"
3102 AND "opinion"."degree" = -1
3103 AND "opinion"."fulfilled" = FALSE
3104 ),
3105 "minus1_fulfilled_count" = (
3106 SELECT coalesce(sum("snapshot"."weight"), 0)
3107 FROM "issue" CROSS JOIN "opinion"
3108 JOIN "direct_interest_snapshot" AS "snapshot"
3109 ON "snapshot"."issue_id" = "issue"."id"
3110 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3111 AND "snapshot"."member_id" = "opinion"."member_id"
3112 WHERE "issue"."id" = "issue_id_p"
3113 AND "opinion"."suggestion_id" = "suggestion_id_v"
3114 AND "opinion"."degree" = -1
3115 AND "opinion"."fulfilled" = TRUE
3116 ),
3117 "plus1_unfulfilled_count" = (
3118 SELECT coalesce(sum("snapshot"."weight"), 0)
3119 FROM "issue" CROSS JOIN "opinion"
3120 JOIN "direct_interest_snapshot" AS "snapshot"
3121 ON "snapshot"."issue_id" = "issue"."id"
3122 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3123 AND "snapshot"."member_id" = "opinion"."member_id"
3124 WHERE "issue"."id" = "issue_id_p"
3125 AND "opinion"."suggestion_id" = "suggestion_id_v"
3126 AND "opinion"."degree" = 1
3127 AND "opinion"."fulfilled" = FALSE
3128 ),
3129 "plus1_fulfilled_count" = (
3130 SELECT coalesce(sum("snapshot"."weight"), 0)
3131 FROM "issue" CROSS JOIN "opinion"
3132 JOIN "direct_interest_snapshot" AS "snapshot"
3133 ON "snapshot"."issue_id" = "issue"."id"
3134 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3135 AND "snapshot"."member_id" = "opinion"."member_id"
3136 WHERE "issue"."id" = "issue_id_p"
3137 AND "opinion"."suggestion_id" = "suggestion_id_v"
3138 AND "opinion"."degree" = 1
3139 AND "opinion"."fulfilled" = TRUE
3140 ),
3141 "plus2_unfulfilled_count" = (
3142 SELECT coalesce(sum("snapshot"."weight"), 0)
3143 FROM "issue" CROSS JOIN "opinion"
3144 JOIN "direct_interest_snapshot" AS "snapshot"
3145 ON "snapshot"."issue_id" = "issue"."id"
3146 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3147 AND "snapshot"."member_id" = "opinion"."member_id"
3148 WHERE "issue"."id" = "issue_id_p"
3149 AND "opinion"."suggestion_id" = "suggestion_id_v"
3150 AND "opinion"."degree" = 2
3151 AND "opinion"."fulfilled" = FALSE
3152 ),
3153 "plus2_fulfilled_count" = (
3154 SELECT coalesce(sum("snapshot"."weight"), 0)
3155 FROM "issue" CROSS JOIN "opinion"
3156 JOIN "direct_interest_snapshot" AS "snapshot"
3157 ON "snapshot"."issue_id" = "issue"."id"
3158 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3159 AND "snapshot"."member_id" = "opinion"."member_id"
3160 WHERE "issue"."id" = "issue_id_p"
3161 AND "opinion"."suggestion_id" = "suggestion_id_v"
3162 AND "opinion"."degree" = 2
3163 AND "opinion"."fulfilled" = TRUE
3164 )
3165 WHERE "suggestion"."id" = "suggestion_id_v";
3166 END LOOP;
3167 END LOOP;
3168 RETURN;
3169 END;
3170 $$;
3172 COMMENT ON FUNCTION "create_snapshot"
3173 ( "issue"."id"%TYPE )
3174 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.';
3177 CREATE FUNCTION "set_snapshot_event"
3178 ( "issue_id_p" "issue"."id"%TYPE,
3179 "event_p" "snapshot_event" )
3180 RETURNS VOID
3181 LANGUAGE 'plpgsql' VOLATILE AS $$
3182 DECLARE
3183 "event_v" "issue"."latest_snapshot_event"%TYPE;
3184 BEGIN
3185 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
3186 WHERE "id" = "issue_id_p" FOR UPDATE;
3187 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
3188 WHERE "id" = "issue_id_p";
3189 UPDATE "direct_population_snapshot" SET "event" = "event_p"
3190 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3191 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
3192 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3193 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
3194 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3195 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
3196 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3197 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
3198 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3199 RETURN;
3200 END;
3201 $$;
3203 COMMENT ON FUNCTION "set_snapshot_event"
3204 ( "issue"."id"%TYPE,
3205 "snapshot_event" )
3206 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
3210 ---------------------
3211 -- Freezing issues --
3212 ---------------------
3214 CREATE FUNCTION "freeze_after_snapshot"
3215 ( "issue_id_p" "issue"."id"%TYPE )
3216 RETURNS VOID
3217 LANGUAGE 'plpgsql' VOLATILE AS $$
3218 DECLARE
3219 "issue_row" "issue"%ROWTYPE;
3220 "policy_row" "policy"%ROWTYPE;
3221 "initiative_row" "initiative"%ROWTYPE;
3222 BEGIN
3223 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3224 SELECT * INTO "policy_row"
3225 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3226 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
3227 FOR "initiative_row" IN
3228 SELECT * FROM "initiative"
3229 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3230 LOOP
3231 IF
3232 "initiative_row"."satisfied_supporter_count" > 0 AND
3233 "initiative_row"."satisfied_supporter_count" *
3234 "policy_row"."initiative_quorum_den" >=
3235 "issue_row"."population" * "policy_row"."initiative_quorum_num"
3236 THEN
3237 UPDATE "initiative" SET "admitted" = TRUE
3238 WHERE "id" = "initiative_row"."id";
3239 ELSE
3240 UPDATE "initiative" SET "admitted" = FALSE
3241 WHERE "id" = "initiative_row"."id";
3242 END IF;
3243 END LOOP;
3244 IF EXISTS (
3245 SELECT NULL FROM "initiative"
3246 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
3247 ) THEN
3248 UPDATE "issue" SET
3249 "state" = 'voting',
3250 "accepted" = coalesce("accepted", now()),
3251 "half_frozen" = coalesce("half_frozen", now()),
3252 "fully_frozen" = now()
3253 WHERE "id" = "issue_id_p";
3254 ELSE
3255 UPDATE "issue" SET
3256 "state" = 'canceled_no_initiative_admitted',
3257 "accepted" = coalesce("accepted", now()),
3258 "half_frozen" = coalesce("half_frozen", now()),
3259 "fully_frozen" = now(),
3260 "closed" = now()
3261 WHERE "id" = "issue_id_p";
3262 -- NOTE: The following DELETE statements have effect only when
3263 -- issue state has been manipulated
3264 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
3265 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
3266 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3267 END IF;
3268 RETURN;
3269 END;
3270 $$;
3272 COMMENT ON FUNCTION "freeze_after_snapshot"
3273 ( "issue"."id"%TYPE )
3274 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
3277 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
3278 RETURNS VOID
3279 LANGUAGE 'plpgsql' VOLATILE AS $$
3280 DECLARE
3281 "issue_row" "issue"%ROWTYPE;
3282 BEGIN
3283 PERFORM "create_snapshot"("issue_id_p");
3284 PERFORM "freeze_after_snapshot"("issue_id_p");
3285 RETURN;
3286 END;
3287 $$;
3289 COMMENT ON FUNCTION "manual_freeze"
3290 ( "issue"."id"%TYPE )
3291 IS 'Freeze an issue manually (fully) and start voting';
3295 -----------------------
3296 -- Counting of votes --
3297 -----------------------
3300 CREATE FUNCTION "weight_of_added_vote_delegations"
3301 ( "issue_id_p" "issue"."id"%TYPE,
3302 "member_id_p" "member"."id"%TYPE,
3303 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
3304 RETURNS "direct_voter"."weight"%TYPE
3305 LANGUAGE 'plpgsql' VOLATILE AS $$
3306 DECLARE
3307 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3308 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
3309 "weight_v" INT4;
3310 "sub_weight_v" INT4;
3311 BEGIN
3312 "weight_v" := 0;
3313 FOR "issue_delegation_row" IN
3314 SELECT * FROM "issue_delegation"
3315 WHERE "trustee_id" = "member_id_p"
3316 AND "issue_id" = "issue_id_p"
3317 LOOP
3318 IF NOT EXISTS (
3319 SELECT NULL FROM "direct_voter"
3320 WHERE "member_id" = "issue_delegation_row"."truster_id"
3321 AND "issue_id" = "issue_id_p"
3322 ) AND NOT EXISTS (
3323 SELECT NULL FROM "delegating_voter"
3324 WHERE "member_id" = "issue_delegation_row"."truster_id"
3325 AND "issue_id" = "issue_id_p"
3326 ) THEN
3327 "delegate_member_ids_v" :=
3328 "member_id_p" || "delegate_member_ids_p";
3329 INSERT INTO "delegating_voter" (
3330 "issue_id",
3331 "member_id",
3332 "scope",
3333 "delegate_member_ids"
3334 ) VALUES (
3335 "issue_id_p",
3336 "issue_delegation_row"."truster_id",
3337 "issue_delegation_row"."scope",
3338 "delegate_member_ids_v"
3339 );
3340 "sub_weight_v" := 1 +
3341 "weight_of_added_vote_delegations"(
3342 "issue_id_p",
3343 "issue_delegation_row"."truster_id",
3344 "delegate_member_ids_v"
3345 );
3346 UPDATE "delegating_voter"
3347 SET "weight" = "sub_weight_v"
3348 WHERE "issue_id" = "issue_id_p"
3349 AND "member_id" = "issue_delegation_row"."truster_id";
3350 "weight_v" := "weight_v" + "sub_weight_v";
3351 END IF;
3352 END LOOP;
3353 RETURN "weight_v";
3354 END;
3355 $$;
3357 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
3358 ( "issue"."id"%TYPE,
3359 "member"."id"%TYPE,
3360 "delegating_voter"."delegate_member_ids"%TYPE )
3361 IS 'Helper function for "add_vote_delegations" function';
3364 CREATE FUNCTION "add_vote_delegations"
3365 ( "issue_id_p" "issue"."id"%TYPE )
3366 RETURNS VOID
3367 LANGUAGE 'plpgsql' VOLATILE AS $$
3368 DECLARE
3369 "member_id_v" "member"."id"%TYPE;
3370 BEGIN
3371 FOR "member_id_v" IN
3372 SELECT "member_id" FROM "direct_voter"
3373 WHERE "issue_id" = "issue_id_p"
3374 LOOP
3375 UPDATE "direct_voter" SET
3376 "weight" = "weight" + "weight_of_added_vote_delegations"(
3377 "issue_id_p",
3378 "member_id_v",
3379 '{}'
3380 )
3381 WHERE "member_id" = "member_id_v"
3382 AND "issue_id" = "issue_id_p";
3383 END LOOP;
3384 RETURN;
3385 END;
3386 $$;
3388 COMMENT ON FUNCTION "add_vote_delegations"
3389 ( "issue_id_p" "issue"."id"%TYPE )
3390 IS 'Helper function for "close_voting" function';
3393 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
3394 RETURNS VOID
3395 LANGUAGE 'plpgsql' VOLATILE AS $$
3396 DECLARE
3397 "area_id_v" "area"."id"%TYPE;
3398 "unit_id_v" "unit"."id"%TYPE;
3399 "member_id_v" "member"."id"%TYPE;
3400 BEGIN
3401 PERFORM "lock_issue"("issue_id_p");
3402 SELECT "id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
3403 SELECT "id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
3404 DELETE FROM "delegating_voter"
3405 WHERE "issue_id" = "issue_id_p";
3406 DELETE FROM "direct_voter"
3407 WHERE "issue_id" = "issue_id_p"
3408 AND "autoreject" = TRUE;
3409 DELETE FROM "direct_voter"
3410 USING (
3411 SELECT
3412 "direct_voter"."member_id"
3413 FROM "direct_voter"
3414 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
3415 LEFT JOIN "privilege"
3416 ON "privilege"."unit_id" = "unit_id_v"
3417 AND "privilege"."member_id" = "direct_voter"."member_id"
3418 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
3419 "member"."active" = FALSE OR
3420 "privilege"."voting_right" ISNULL OR
3421 "privilege"."voting_right" = FALSE
3422 )
3423 ) AS "subquery"
3424 WHERE "direct_voter"."issue_id" = "issue_id_p"
3425 AND "direct_voter"."member_id" = "subquery"."member_id";
3426 UPDATE "direct_voter" SET "weight" = 1
3427 WHERE "issue_id" = "issue_id_p";
3428 PERFORM "add_vote_delegations"("issue_id_p");
3429 FOR "member_id_v" IN
3430 SELECT "interest"."member_id"
3431 FROM "interest"
3432 JOIN "member"
3433 ON "interest"."member_id" = "member"."id"
3434 LEFT JOIN "direct_voter"
3435 ON "interest"."member_id" = "direct_voter"."member_id"
3436 AND "interest"."issue_id" = "direct_voter"."issue_id"
3437 LEFT JOIN "delegating_voter"
3438 ON "interest"."member_id" = "delegating_voter"."member_id"
3439 AND "interest"."issue_id" = "delegating_voter"."issue_id"
3440 WHERE "interest"."issue_id" = "issue_id_p"
3441 AND "interest"."autoreject" = TRUE
3442 AND "member"."active"
3443 AND "direct_voter"."member_id" ISNULL
3444 AND "delegating_voter"."member_id" ISNULL
3445 UNION SELECT "membership"."member_id"
3446 FROM "membership"
3447 JOIN "member"
3448 ON "membership"."member_id" = "member"."id"
3449 LEFT JOIN "interest"
3450 ON "membership"."member_id" = "interest"."member_id"
3451 AND "interest"."issue_id" = "issue_id_p"
3452 LEFT JOIN "direct_voter"
3453 ON "membership"."member_id" = "direct_voter"."member_id"
3454 AND "direct_voter"."issue_id" = "issue_id_p"
3455 LEFT JOIN "delegating_voter"
3456 ON "membership"."member_id" = "delegating_voter"."member_id"
3457 AND "delegating_voter"."issue_id" = "issue_id_p"
3458 WHERE "membership"."area_id" = "area_id_v"
3459 AND "membership"."autoreject" = TRUE
3460 AND "member"."active"
3461 AND "interest"."autoreject" ISNULL
3462 AND "direct_voter"."member_id" ISNULL
3463 AND "delegating_voter"."member_id" ISNULL
3464 LOOP
3465 INSERT INTO "direct_voter"
3466 ("member_id", "issue_id", "weight", "autoreject") VALUES
3467 ("member_id_v", "issue_id_p", 1, TRUE);
3468 INSERT INTO "vote" (
3469 "member_id",
3470 "issue_id",
3471 "initiative_id",
3472 "grade"
3473 ) SELECT
3474 "member_id_v" AS "member_id",
3475 "issue_id_p" AS "issue_id",
3476 "id" AS "initiative_id",
3477 -1 AS "grade"
3478 FROM "initiative" WHERE "issue_id" = "issue_id_p";
3479 END LOOP;
3480 PERFORM "add_vote_delegations"("issue_id_p");
3481 UPDATE "issue" SET
3482 "state" = 'calculation',
3483 "closed" = now(),
3484 "voter_count" = (
3485 SELECT coalesce(sum("weight"), 0)
3486 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
3487 )
3488 WHERE "id" = "issue_id_p";
3489 UPDATE "initiative" SET
3490 "positive_votes" = "vote_counts"."positive_votes",
3491 "negative_votes" = "vote_counts"."negative_votes",
3492 "agreed" = CASE WHEN "majority_strict" THEN
3493 "vote_counts"."positive_votes" * "majority_den" >
3494 "majority_num" *
3495 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
3496 ELSE
3497 "vote_counts"."positive_votes" * "majority_den" >=
3498 "majority_num" *
3499 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
3500 END
3501 FROM
3502 ( SELECT
3503 "initiative"."id" AS "initiative_id",
3504 coalesce(
3505 sum(
3506 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
3507 ),
3508 0
3509 ) AS "positive_votes",
3510 coalesce(
3511 sum(
3512 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
3513 ),
3514 0
3515 ) AS "negative_votes"
3516 FROM "initiative"
3517 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
3518 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
3519 LEFT JOIN "direct_voter"
3520 ON "direct_voter"."issue_id" = "initiative"."issue_id"
3521 LEFT JOIN "vote"
3522 ON "vote"."initiative_id" = "initiative"."id"
3523 AND "vote"."member_id" = "direct_voter"."member_id"
3524 WHERE "initiative"."issue_id" = "issue_id_p"
3525 AND "initiative"."admitted" -- NOTE: NULL case is handled too
3526 GROUP BY "initiative"."id"
3527 ) AS "vote_counts",
3528 "issue",
3529 "policy"
3530 WHERE "vote_counts"."initiative_id" = "initiative"."id"
3531 AND "issue"."id" = "initiative"."issue_id"
3532 AND "policy"."id" = "issue"."policy_id";
3533 -- NOTE: "closed" column of issue must be set at this point
3534 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3535 INSERT INTO "battle" (
3536 "issue_id",
3537 "winning_initiative_id", "losing_initiative_id",
3538 "count"
3539 ) SELECT
3540 "issue_id",
3541 "winning_initiative_id", "losing_initiative_id",
3542 "count"
3543 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
3544 END;
3545 $$;
3547 COMMENT ON FUNCTION "close_voting"
3548 ( "issue"."id"%TYPE )
3549 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.';
3552 CREATE FUNCTION "defeat_strength"
3553 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
3554 RETURNS INT8
3555 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3556 BEGIN
3557 IF "positive_votes_p" > "negative_votes_p" THEN
3558 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
3559 ELSIF "positive_votes_p" = "negative_votes_p" THEN
3560 RETURN 0;
3561 ELSE
3562 RETURN -1;
3563 END IF;
3564 END;
3565 $$;
3567 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';
3570 CREATE FUNCTION "array_init_string"("dim_p" INTEGER)
3571 RETURNS TEXT
3572 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3573 DECLARE
3574 "i" INTEGER;
3575 "ary_text_v" TEXT;
3576 BEGIN
3577 IF "dim_p" >= 1 THEN
3578 "ary_text_v" := '{NULL';
3579 "i" := "dim_p";
3580 LOOP
3581 "i" := "i" - 1;
3582 EXIT WHEN "i" = 0;
3583 "ary_text_v" := "ary_text_v" || ',NULL';
3584 END LOOP;
3585 "ary_text_v" := "ary_text_v" || '}';
3586 RETURN "ary_text_v";
3587 ELSE
3588 RAISE EXCEPTION 'Dimension needs to be at least 1.';
3589 END IF;
3590 END;
3591 $$;
3593 COMMENT ON FUNCTION "array_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
3596 CREATE FUNCTION "square_matrix_init_string"("dim_p" INTEGER)
3597 RETURNS TEXT
3598 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3599 DECLARE
3600 "i" INTEGER;
3601 "row_text_v" TEXT;
3602 "ary_text_v" TEXT;
3603 BEGIN
3604 IF "dim_p" >= 1 THEN
3605 "row_text_v" := '{NULL';
3606 "i" := "dim_p";
3607 LOOP
3608 "i" := "i" - 1;
3609 EXIT WHEN "i" = 0;
3610 "row_text_v" := "row_text_v" || ',NULL';
3611 END LOOP;
3612 "row_text_v" := "row_text_v" || '}';
3613 "ary_text_v" := '{' || "row_text_v";
3614 "i" := "dim_p";
3615 LOOP
3616 "i" := "i" - 1;
3617 EXIT WHEN "i" = 0;
3618 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
3619 END LOOP;
3620 "ary_text_v" := "ary_text_v" || '}';
3621 RETURN "ary_text_v";
3622 ELSE
3623 RAISE EXCEPTION 'Dimension needs to be at least 1.';
3624 END IF;
3625 END;
3626 $$;
3628 COMMENT ON FUNCTION "square_matrix_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
3631 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
3632 RETURNS VOID
3633 LANGUAGE 'plpgsql' VOLATILE AS $$
3634 DECLARE
3635 "dimension_v" INTEGER;
3636 "vote_matrix" INT4[][]; -- absolute votes
3637 "matrix" INT8[][]; -- defeat strength / best paths
3638 "i" INTEGER;
3639 "j" INTEGER;
3640 "k" INTEGER;
3641 "battle_row" "battle"%ROWTYPE;
3642 "rank_ary" INT4[];
3643 "rank_v" INT4;
3644 "done_v" INTEGER;
3645 "winners_ary" INTEGER[];
3646 "initiative_id_v" "initiative"."id"%TYPE;
3647 BEGIN
3648 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
3649 SELECT count(1) INTO "dimension_v" FROM "initiative"
3650 WHERE "issue_id" = "issue_id_p" AND "agreed";
3651 IF "dimension_v" = 1 THEN
3652 UPDATE "initiative" SET "rank" = 1
3653 WHERE "issue_id" = "issue_id_p" AND "agreed";
3654 ELSIF "dimension_v" > 1 THEN
3655 -- Create "vote_matrix" with absolute number of votes in pairwise
3656 -- comparison:
3657 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
3658 "i" := 1;
3659 "j" := 2;
3660 FOR "battle_row" IN
3661 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
3662 ORDER BY "winning_initiative_id", "losing_initiative_id"
3663 LOOP
3664 "vote_matrix"["i"]["j"] := "battle_row"."count";
3665 IF "j" = "dimension_v" THEN
3666 "i" := "i" + 1;
3667 "j" := 1;
3668 ELSE
3669 "j" := "j" + 1;
3670 IF "j" = "i" THEN
3671 "j" := "j" + 1;
3672 END IF;
3673 END IF;
3674 END LOOP;
3675 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
3676 RAISE EXCEPTION 'Wrong battle count (should not happen)';
3677 END IF;
3678 -- Store defeat strengths in "matrix" using "defeat_strength"
3679 -- function:
3680 "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
3681 "i" := 1;
3682 LOOP
3683 "j" := 1;
3684 LOOP
3685 IF "i" != "j" THEN
3686 "matrix"["i"]["j"] := "defeat_strength"(
3687 "vote_matrix"["i"]["j"],
3688 "vote_matrix"["j"]["i"]
3689 );
3690 END IF;
3691 EXIT WHEN "j" = "dimension_v";
3692 "j" := "j" + 1;
3693 END LOOP;
3694 EXIT WHEN "i" = "dimension_v";
3695 "i" := "i" + 1;
3696 END LOOP;
3697 -- Find best paths:
3698 "i" := 1;
3699 LOOP
3700 "j" := 1;
3701 LOOP
3702 IF "i" != "j" THEN
3703 "k" := 1;
3704 LOOP
3705 IF "i" != "k" AND "j" != "k" THEN
3706 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
3707 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
3708 "matrix"["j"]["k"] := "matrix"["j"]["i"];
3709 END IF;
3710 ELSE
3711 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
3712 "matrix"["j"]["k"] := "matrix"["i"]["k"];
3713 END IF;
3714 END IF;
3715 END IF;
3716 EXIT WHEN "k" = "dimension_v";
3717 "k" := "k" + 1;
3718 END LOOP;
3719 END IF;
3720 EXIT WHEN "j" = "dimension_v";
3721 "j" := "j" + 1;
3722 END LOOP;
3723 EXIT WHEN "i" = "dimension_v";
3724 "i" := "i" + 1;
3725 END LOOP;
3726 -- Determine order of winners:
3727 "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
3728 "rank_v" := 1;
3729 "done_v" := 0;
3730 LOOP
3731 "winners_ary" := '{}';
3732 "i" := 1;
3733 LOOP
3734 IF "rank_ary"["i"] ISNULL THEN
3735 "j" := 1;
3736 LOOP
3737 IF
3738 "i" != "j" AND
3739 "rank_ary"["j"] ISNULL AND
3740 "matrix"["j"]["i"] > "matrix"["i"]["j"]
3741 THEN
3742 -- someone else is better
3743 EXIT;
3744 END IF;
3745 IF "j" = "dimension_v" THEN
3746 -- noone is better
3747 "winners_ary" := "winners_ary" || "i";
3748 EXIT;
3749 END IF;
3750 "j" := "j" + 1;
3751 END LOOP;
3752 END IF;
3753 EXIT WHEN "i" = "dimension_v";
3754 "i" := "i" + 1;
3755 END LOOP;
3756 "i" := 1;
3757 LOOP
3758 "rank_ary"["winners_ary"["i"]] := "rank_v";
3759 "done_v" := "done_v" + 1;
3760 EXIT WHEN "i" = array_upper("winners_ary", 1);
3761 "i" := "i" + 1;
3762 END LOOP;
3763 EXIT WHEN "done_v" = "dimension_v";
3764 "rank_v" := "rank_v" + 1;
3765 END LOOP;
3766 -- write preliminary ranks:
3767 "i" := 1;
3768 FOR "initiative_id_v" IN
3769 SELECT "id" FROM "initiative"
3770 WHERE "issue_id" = "issue_id_p" AND "agreed"
3771 ORDER BY "id"
3772 LOOP
3773 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
3774 WHERE "id" = "initiative_id_v";
3775 "i" := "i" + 1;
3776 END LOOP;
3777 IF "i" != "dimension_v" + 1 THEN
3778 RAISE EXCEPTION 'Wrong winner count (should not happen)';
3779 END IF;
3780 -- straighten ranks (start counting with 1, no equal ranks):
3781 "rank_v" := 1;
3782 FOR "initiative_id_v" IN
3783 SELECT "id" FROM "initiative"
3784 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
3785 ORDER BY
3786 "rank",
3787 "vote_ratio"("positive_votes", "negative_votes") DESC,
3788 "id"
3789 LOOP
3790 UPDATE "initiative" SET "rank" = "rank_v"
3791 WHERE "id" = "initiative_id_v";
3792 "rank_v" := "rank_v" + 1;
3793 END LOOP;
3794 END IF;
3795 -- mark issue as finished
3796 UPDATE "issue" SET
3797 "state" =
3798 CASE WHEN NOT EXISTS (
3799 SELECT NULL FROM "initiative"
3800 WHERE "issue_id" = "issue_id_p" AND "admitted"
3801 ) THEN
3802 'canceled_no_initiative_admitted'::"issue_state"
3803 ELSE
3804 CASE WHEN "dimension_v" = 0 THEN
3805 'finished_without_winner'::"issue_state"
3806 ELSE
3807 'finished_with_winner'::"issue_state"
3808 END
3809 END,
3810 "ranks_available" = TRUE
3811 WHERE "id" = "issue_id_p";
3812 RETURN;
3813 END;
3814 $$;
3816 COMMENT ON FUNCTION "calculate_ranks"
3817 ( "issue"."id"%TYPE )
3818 IS 'Determine ranking (Votes have to be counted first)';
3822 -----------------------------
3823 -- Automatic state changes --
3824 -----------------------------
3827 CREATE FUNCTION "check_issue"
3828 ( "issue_id_p" "issue"."id"%TYPE )
3829 RETURNS VOID
3830 LANGUAGE 'plpgsql' VOLATILE AS $$
3831 DECLARE
3832 "issue_row" "issue"%ROWTYPE;
3833 "policy_row" "policy"%ROWTYPE;
3834 "voting_requested_v" BOOLEAN;
3835 BEGIN
3836 PERFORM "lock_issue"("issue_id_p");
3837 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3838 -- only process open issues:
3839 IF "issue_row"."closed" ISNULL THEN
3840 SELECT * INTO "policy_row" FROM "policy"
3841 WHERE "id" = "issue_row"."policy_id";
3842 -- create a snapshot, unless issue is already fully frozen:
3843 IF "issue_row"."fully_frozen" ISNULL THEN
3844 PERFORM "create_snapshot"("issue_id_p");
3845 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3846 END IF;
3847 -- eventually close or accept issues, which have not been accepted:
3848 IF "issue_row"."accepted" ISNULL THEN
3849 IF EXISTS (
3850 SELECT NULL FROM "initiative"
3851 WHERE "issue_id" = "issue_id_p"
3852 AND "supporter_count" > 0
3853 AND "supporter_count" * "policy_row"."issue_quorum_den"
3854 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
3855 ) THEN
3856 -- accept issues, if supporter count is high enough
3857 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3858 -- NOTE: "issue_row" used later
3859 "issue_row"."state" := 'discussion';
3860 "issue_row"."accepted" := now();
3861 UPDATE "issue" SET
3862 "state" = "issue_row"."state",
3863 "accepted" = "issue_row"."accepted"
3864 WHERE "id" = "issue_row"."id";
3865 ELSIF
3866 now() >= "issue_row"."created" + "issue_row"."admission_time"
3867 THEN
3868 -- close issues, if admission time has expired
3869 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3870 UPDATE "issue" SET
3871 "state" = 'canceled_issue_not_accepted',
3872 "closed" = now()
3873 WHERE "id" = "issue_row"."id";
3874 END IF;
3875 END IF;
3876 -- eventually half freeze issues:
3877 IF
3878 -- NOTE: issue can't be closed at this point, if it has been accepted
3879 "issue_row"."accepted" NOTNULL AND
3880 "issue_row"."half_frozen" ISNULL
3881 THEN
3882 SELECT
3883 CASE
3884 WHEN "vote_now" * 2 > "issue_row"."population" THEN
3885 TRUE
3886 WHEN "vote_later" * 2 > "issue_row"."population" THEN
3887 FALSE
3888 ELSE NULL
3889 END
3890 INTO "voting_requested_v"
3891 FROM "issue" WHERE "id" = "issue_id_p";
3892 IF
3893 "voting_requested_v" OR (
3894 "voting_requested_v" ISNULL AND
3895 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
3896 )
3897 THEN
3898 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
3899 -- NOTE: "issue_row" used later
3900 "issue_row"."state" := 'verification';
3901 "issue_row"."half_frozen" := now();
3902 UPDATE "issue" SET
3903 "state" = "issue_row"."state",
3904 "half_frozen" = "issue_row"."half_frozen"
3905 WHERE "id" = "issue_row"."id";
3906 END IF;
3907 END IF;
3908 -- close issues after some time, if all initiatives have been revoked:
3909 IF
3910 "issue_row"."closed" ISNULL AND
3911 NOT EXISTS (
3912 -- all initiatives are revoked
3913 SELECT NULL FROM "initiative"
3914 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3915 ) AND (
3916 -- and issue has not been accepted yet
3917 "issue_row"."accepted" ISNULL OR
3918 NOT EXISTS (
3919 -- or no initiatives have been revoked lately
3920 SELECT NULL FROM "initiative"
3921 WHERE "issue_id" = "issue_id_p"
3922 AND now() < "revoked" + "issue_row"."verification_time"
3923 ) OR (
3924 -- or verification time has elapsed
3925 "issue_row"."half_frozen" NOTNULL AND
3926 "issue_row"."fully_frozen" ISNULL AND
3927 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3928 )
3929 )
3930 THEN
3931 -- NOTE: "issue_row" used later
3932 IF "issue_row"."accepted" ISNULL THEN
3933 "issue_row"."state" := 'canceled_revoked_before_accepted';
3934 ELSIF "issue_row"."half_frozen" ISNULL THEN
3935 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
3936 ELSE
3937 "issue_row"."state" := 'canceled_after_revocation_during_verification';
3938 END IF;
3939 "issue_row"."closed" := now();
3940 UPDATE "issue" SET
3941 "state" = "issue_row"."state",
3942 "closed" = "issue_row"."closed"
3943 WHERE "id" = "issue_row"."id";
3944 END IF;
3945 -- fully freeze issue after verification time:
3946 IF
3947 "issue_row"."half_frozen" NOTNULL AND
3948 "issue_row"."fully_frozen" ISNULL AND
3949 "issue_row"."closed" ISNULL AND
3950 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3951 THEN
3952 PERFORM "freeze_after_snapshot"("issue_id_p");
3953 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
3954 END IF;
3955 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3956 -- close issue by calling close_voting(...) after voting time:
3957 IF
3958 "issue_row"."closed" ISNULL AND
3959 "issue_row"."fully_frozen" NOTNULL AND
3960 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
3961 THEN
3962 PERFORM "close_voting"("issue_id_p");
3963 -- calculate ranks will not consume much time and can be done now
3964 PERFORM "calculate_ranks"("issue_id_p");
3965 END IF;
3966 END IF;
3967 RETURN;
3968 END;
3969 $$;
3971 COMMENT ON FUNCTION "check_issue"
3972 ( "issue"."id"%TYPE )
3973 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.';
3976 CREATE FUNCTION "check_everything"()
3977 RETURNS VOID
3978 LANGUAGE 'plpgsql' VOLATILE AS $$
3979 DECLARE
3980 "issue_id_v" "issue"."id"%TYPE;
3981 BEGIN
3982 DELETE FROM "expired_session";
3983 PERFORM "check_last_login"();
3984 PERFORM "calculate_member_counts"();
3985 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
3986 PERFORM "check_issue"("issue_id_v");
3987 END LOOP;
3988 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
3989 PERFORM "calculate_ranks"("issue_id_v");
3990 END LOOP;
3991 RETURN;
3992 END;
3993 $$;
3995 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.';
3999 ----------------------
4000 -- Deletion of data --
4001 ----------------------
4004 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
4005 RETURNS VOID
4006 LANGUAGE 'plpgsql' VOLATILE AS $$
4007 DECLARE
4008 "issue_row" "issue"%ROWTYPE;
4009 BEGIN
4010 SELECT * INTO "issue_row"
4011 FROM "issue" WHERE "id" = "issue_id_p"
4012 FOR UPDATE;
4013 IF "issue_row"."cleaned" ISNULL THEN
4014 UPDATE "issue" SET
4015 "closed" = NULL,
4016 "ranks_available" = FALSE
4017 WHERE "id" = "issue_id_p";
4018 DELETE FROM "delegating_voter"
4019 WHERE "issue_id" = "issue_id_p";
4020 DELETE FROM "direct_voter"
4021 WHERE "issue_id" = "issue_id_p";
4022 DELETE FROM "delegating_interest_snapshot"
4023 WHERE "issue_id" = "issue_id_p";
4024 DELETE FROM "direct_interest_snapshot"
4025 WHERE "issue_id" = "issue_id_p";
4026 DELETE FROM "delegating_population_snapshot"
4027 WHERE "issue_id" = "issue_id_p";
4028 DELETE FROM "direct_population_snapshot"
4029 WHERE "issue_id" = "issue_id_p";
4030 DELETE FROM "non_voter"
4031 WHERE "issue_id" = "issue_id_p";
4032 DELETE FROM "delegation"
4033 WHERE "issue_id" = "issue_id_p";
4034 DELETE FROM "supporter"
4035 WHERE "issue_id" = "issue_id_p";
4036 UPDATE "issue" SET
4037 "closed" = "issue_row"."closed",
4038 "ranks_available" = "issue_row"."ranks_available",
4039 "cleaned" = now()
4040 WHERE "id" = "issue_id_p";
4041 END IF;
4042 RETURN;
4043 END;
4044 $$;
4046 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
4049 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
4050 RETURNS VOID
4051 LANGUAGE 'plpgsql' VOLATILE AS $$
4052 BEGIN
4053 UPDATE "member" SET
4054 "last_login" = NULL,
4055 "last_login_public" = NULL,
4056 "login" = NULL,
4057 "password" = NULL,
4058 "locked" = TRUE,
4059 "active" = FALSE,
4060 "notify_email" = NULL,
4061 "notify_email_unconfirmed" = NULL,
4062 "notify_email_secret" = NULL,
4063 "notify_email_secret_expiry" = NULL,
4064 "notify_email_lock_expiry" = NULL,
4065 "password_reset_secret" = NULL,
4066 "password_reset_secret_expiry" = NULL,
4067 "organizational_unit" = NULL,
4068 "internal_posts" = NULL,
4069 "realname" = NULL,
4070 "birthday" = NULL,
4071 "address" = NULL,
4072 "email" = NULL,
4073 "xmpp_address" = NULL,
4074 "website" = NULL,
4075 "phone" = NULL,
4076 "mobile_phone" = NULL,
4077 "profession" = NULL,
4078 "external_memberships" = NULL,
4079 "external_posts" = NULL,
4080 "statement" = NULL
4081 WHERE "id" = "member_id_p";
4082 -- "text_search_data" is updated by triggers
4083 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
4084 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
4085 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
4086 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
4087 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
4088 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
4089 DELETE FROM "session" WHERE "member_id" = "member_id_p";
4090 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
4091 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
4092 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
4093 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
4094 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
4095 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
4096 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
4097 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
4098 DELETE FROM "direct_voter" USING "issue"
4099 WHERE "direct_voter"."issue_id" = "issue"."id"
4100 AND "issue"."closed" ISNULL
4101 AND "member_id" = "member_id_p";
4102 RETURN;
4103 END;
4104 $$;
4106 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)';
4109 CREATE FUNCTION "delete_private_data"()
4110 RETURNS VOID
4111 LANGUAGE 'plpgsql' VOLATILE AS $$
4112 BEGIN
4113 UPDATE "member" SET
4114 "last_login" = NULL,
4115 "login" = NULL,
4116 "password" = NULL,
4117 "notify_email" = NULL,
4118 "notify_email_unconfirmed" = NULL,
4119 "notify_email_secret" = NULL,
4120 "notify_email_secret_expiry" = NULL,
4121 "notify_email_lock_expiry" = NULL,
4122 "password_reset_secret" = NULL,
4123 "password_reset_secret_expiry" = NULL,
4124 "organizational_unit" = NULL,
4125 "internal_posts" = NULL,
4126 "realname" = NULL,
4127 "birthday" = NULL,
4128 "address" = NULL,
4129 "email" = NULL,
4130 "xmpp_address" = NULL,
4131 "website" = NULL,
4132 "phone" = NULL,
4133 "mobile_phone" = NULL,
4134 "profession" = NULL,
4135 "external_memberships" = NULL,
4136 "external_posts" = NULL,
4137 "statement" = NULL;
4138 -- "text_search_data" is updated by triggers
4139 DELETE FROM "invite_code";
4140 DELETE FROM "setting";
4141 DELETE FROM "setting_map";
4142 DELETE FROM "member_relation_setting";
4143 DELETE FROM "member_image";
4144 DELETE FROM "contact";
4145 DELETE FROM "ignored_member";
4146 DELETE FROM "session";
4147 DELETE FROM "area_setting";
4148 DELETE FROM "issue_setting";
4149 DELETE FROM "ignored_initiative";
4150 DELETE FROM "initiative_setting";
4151 DELETE FROM "suggestion_setting";
4152 DELETE FROM "non_voter";
4153 DELETE FROM "direct_voter" USING "issue"
4154 WHERE "direct_voter"."issue_id" = "issue"."id"
4155 AND "issue"."closed" ISNULL;
4156 RETURN;
4157 END;
4158 $$;
4160 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.';
4164 COMMIT;