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