liquid_feedback_core
view core.sql @ 57:a7ad50614d82
Removed "login" name history; Deletion functions delete more data
Details:
- Removed "login" name history
- Deletion functions "delete_member" and "delete_private_data" delete more data
-- Fields "last_login" and "notify_email_lock_expiry" of table "member"
-- Final votes on open issues are deleted by "delete_member" function as well
- Version number changed to v1.2.0
- Update script
Details:
- Removed "login" name history
- Deletion functions "delete_member" and "delete_private_data" delete more data
-- Fields "last_login" and "notify_email_lock_expiry" of table "member"
-- Final votes on open issues are deleted by "delete_member" function as well
- Version number changed to v1.2.0
- Update script
| author | jbe |
|---|---|
| date | Wed Jul 14 13:28:28 2010 +0200 (2010-07-14) |
| parents | b63515611a60 |
| children | fdd1729d7e2a |
line source
2 CREATE LANGUAGE plpgsql; -- Triggers are implemented in PL/pgSQL
4 -- NOTE: In PostgreSQL every UNIQUE constraint implies creation of an index
6 BEGIN;
8 CREATE VIEW "liquid_feedback_version" AS
9 SELECT * FROM (VALUES ('1.2.0', 1, 2, 0))
10 AS "subquery"("string", "major", "minor", "revision");
14 ----------------------
15 -- Full text search --
16 ----------------------
19 CREATE FUNCTION "text_search_query"("query_text_p" TEXT)
20 RETURNS TSQUERY
21 LANGUAGE 'plpgsql' IMMUTABLE AS $$
22 BEGIN
23 RETURN plainto_tsquery('pg_catalog.simple', "query_text_p");
24 END;
25 $$;
27 COMMENT ON FUNCTION "text_search_query"(TEXT) IS 'Usage: WHERE "text_search_data" @@ "text_search_query"(''<user query>'')';
30 CREATE FUNCTION "highlight"
31 ( "body_p" TEXT,
32 "query_text_p" TEXT )
33 RETURNS TEXT
34 LANGUAGE 'plpgsql' IMMUTABLE AS $$
35 BEGIN
36 RETURN ts_headline(
37 'pg_catalog.simple',
38 replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'),
39 "text_search_query"("query_text_p"),
40 'StartSel=* StopSel=* HighlightAll=TRUE' );
41 END;
42 $$;
44 COMMENT ON FUNCTION "highlight"
45 ( "body_p" TEXT,
46 "query_text_p" TEXT )
47 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.';
51 -------------------------
52 -- Tables and indicies --
53 -------------------------
56 CREATE TABLE "member" (
57 "id" SERIAL4 PRIMARY KEY,
58 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
59 "last_login" TIMESTAMPTZ,
60 "login" TEXT UNIQUE,
61 "password" TEXT,
62 "active" BOOLEAN NOT NULL DEFAULT TRUE,
63 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
64 "notify_email" TEXT,
65 "notify_email_unconfirmed" TEXT,
66 "notify_email_secret" TEXT UNIQUE,
67 "notify_email_secret_expiry" TIMESTAMPTZ,
68 "notify_email_lock_expiry" TIMESTAMPTZ,
69 "password_reset_secret" TEXT UNIQUE,
70 "password_reset_secret_expiry" TIMESTAMPTZ,
71 "name" TEXT NOT NULL UNIQUE,
72 "identification" TEXT UNIQUE,
73 "organizational_unit" TEXT,
74 "internal_posts" TEXT,
75 "realname" TEXT,
76 "birthday" DATE,
77 "address" TEXT,
78 "email" TEXT,
79 "xmpp_address" TEXT,
80 "website" TEXT,
81 "phone" TEXT,
82 "mobile_phone" TEXT,
83 "profession" TEXT,
84 "external_memberships" TEXT,
85 "external_posts" TEXT,
86 "statement" TEXT,
87 "text_search_data" TSVECTOR );
88 CREATE INDEX "member_active_idx" ON "member" ("active");
89 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
90 CREATE TRIGGER "update_text_search_data"
91 BEFORE INSERT OR UPDATE ON "member"
92 FOR EACH ROW EXECUTE PROCEDURE
93 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
94 "name", "identification", "organizational_unit", "internal_posts",
95 "realname", "external_memberships", "external_posts", "statement" );
97 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
99 COMMENT ON COLUMN "member"."login" IS 'Login name';
100 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
101 COMMENT ON COLUMN "member"."active" IS 'Inactive members can not login and their supports/votes are not counted by the system.';
102 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
103 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
104 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
105 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
106 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
107 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
108 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member';
109 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
110 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
111 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
112 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
113 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
114 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
115 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
116 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his homepage within the system';
119 CREATE TABLE "member_history" (
120 "id" SERIAL8 PRIMARY KEY,
121 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
122 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
123 "active" BOOLEAN NOT NULL,
124 "name" TEXT NOT NULL );
125 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
127 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
129 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
130 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
133 CREATE TABLE "invite_code" (
134 "code" TEXT PRIMARY KEY,
135 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
136 "used" TIMESTAMPTZ,
137 "member_id" INT4 UNIQUE REFERENCES "member" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
138 "comment" TEXT,
139 CONSTRAINT "only_used_codes_may_refer_to_member" CHECK ("used" NOTNULL OR "member_id" ISNULL) );
141 COMMENT ON TABLE "invite_code" IS 'Invite codes can be used once to create a new member account.';
143 COMMENT ON COLUMN "invite_code"."code" IS 'Secret code';
144 COMMENT ON COLUMN "invite_code"."created" IS 'Time of creation of the secret code';
145 COMMENT ON COLUMN "invite_code"."used" IS 'NULL, if not used yet, otherwise tells when this code was used to create a member account';
146 COMMENT ON COLUMN "invite_code"."member_id" IS 'References the member whose account was created with this code';
147 COMMENT ON COLUMN "invite_code"."comment" IS 'Comment on the code, which is to be used for administrative reasons only';
150 CREATE TABLE "setting" (
151 PRIMARY KEY ("member_id", "key"),
152 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
153 "key" TEXT NOT NULL,
154 "value" TEXT NOT NULL );
155 CREATE INDEX "setting_key_idx" ON "setting" ("key");
157 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
159 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
162 CREATE TABLE "setting_map" (
163 PRIMARY KEY ("member_id", "key", "subkey"),
164 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
165 "key" TEXT NOT NULL,
166 "subkey" TEXT NOT NULL,
167 "value" TEXT NOT NULL );
168 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
170 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
172 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
173 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
174 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
177 CREATE TABLE "member_relation_setting" (
178 PRIMARY KEY ("member_id", "key", "other_member_id"),
179 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
180 "key" TEXT NOT NULL,
181 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
182 "value" TEXT NOT NULL );
184 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
187 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
189 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
192 CREATE TABLE "member_image" (
193 PRIMARY KEY ("member_id", "image_type", "scaled"),
194 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
195 "image_type" "member_image_type",
196 "scaled" BOOLEAN,
197 "content_type" TEXT,
198 "data" BYTEA NOT NULL );
200 COMMENT ON TABLE "member_image" IS 'Images of members';
202 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
205 CREATE TABLE "member_count" (
206 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
207 "total_count" INT4 NOT NULL );
209 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';
211 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
212 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
215 CREATE TABLE "contact" (
216 PRIMARY KEY ("member_id", "other_member_id"),
217 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
218 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
219 "public" BOOLEAN NOT NULL DEFAULT FALSE,
220 CONSTRAINT "cant_save_yourself_as_contact"
221 CHECK ("member_id" != "other_member_id") );
223 COMMENT ON TABLE "contact" IS 'Contact lists';
225 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
226 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
227 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
230 CREATE TABLE "session" (
231 "ident" TEXT PRIMARY KEY,
232 "additional_secret" TEXT,
233 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
234 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
235 "lang" TEXT );
236 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
238 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';
240 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
241 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
242 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
243 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
246 CREATE TABLE "policy" (
247 "id" SERIAL4 PRIMARY KEY,
248 "index" INT4 NOT NULL,
249 "active" BOOLEAN NOT NULL DEFAULT TRUE,
250 "name" TEXT NOT NULL UNIQUE,
251 "description" TEXT NOT NULL DEFAULT '',
252 "admission_time" INTERVAL NOT NULL,
253 "discussion_time" INTERVAL NOT NULL,
254 "verification_time" INTERVAL NOT NULL,
255 "voting_time" INTERVAL NOT NULL,
256 "issue_quorum_num" INT4 NOT NULL,
257 "issue_quorum_den" INT4 NOT NULL,
258 "initiative_quorum_num" INT4 NOT NULL,
259 "initiative_quorum_den" INT4 NOT NULL,
260 "majority_num" INT4 NOT NULL DEFAULT 1,
261 "majority_den" INT4 NOT NULL DEFAULT 2,
262 "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE );
263 CREATE INDEX "policy_active_idx" ON "policy" ("active");
265 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
267 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
268 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
269 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"';
270 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"';
271 COMMENT ON COLUMN "policy"."verification_time" IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
272 COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "fully_frozen" but not "closed"';
273 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"';
274 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"';
275 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
276 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
277 COMMENT ON COLUMN "policy"."majority_num" IS 'Numerator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
278 COMMENT ON COLUMN "policy"."majority_den" IS 'Denominator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
279 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.';
282 CREATE TABLE "area" (
283 "id" SERIAL4 PRIMARY KEY,
284 "active" BOOLEAN NOT NULL DEFAULT TRUE,
285 "name" TEXT NOT NULL,
286 "description" TEXT NOT NULL DEFAULT '',
287 "direct_member_count" INT4,
288 "member_weight" INT4,
289 "autoreject_weight" INT4,
290 "text_search_data" TSVECTOR );
291 CREATE INDEX "area_active_idx" ON "area" ("active");
292 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
293 CREATE TRIGGER "update_text_search_data"
294 BEFORE INSERT OR UPDATE ON "area"
295 FOR EACH ROW EXECUTE PROCEDURE
296 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
297 "name", "description" );
299 COMMENT ON TABLE "area" IS 'Subject areas';
301 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
302 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"';
303 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
304 COMMENT ON COLUMN "area"."autoreject_weight" IS 'Sum of weight of members using the autoreject feature';
307 CREATE TABLE "area_setting" (
308 PRIMARY KEY ("member_id", "key", "area_id"),
309 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
310 "key" TEXT NOT NULL,
311 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
312 "value" TEXT NOT NULL );
314 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
317 CREATE TABLE "allowed_policy" (
318 PRIMARY KEY ("area_id", "policy_id"),
319 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
320 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
321 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
322 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
324 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
326 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
329 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
331 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';
334 CREATE TABLE "issue" (
335 "id" SERIAL4 PRIMARY KEY,
336 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
337 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
338 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
339 "accepted" TIMESTAMPTZ,
340 "half_frozen" TIMESTAMPTZ,
341 "fully_frozen" TIMESTAMPTZ,
342 "closed" TIMESTAMPTZ,
343 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
344 "admission_time" INTERVAL NOT NULL,
345 "discussion_time" INTERVAL NOT NULL,
346 "verification_time" INTERVAL NOT NULL,
347 "voting_time" INTERVAL NOT NULL,
348 "snapshot" TIMESTAMPTZ,
349 "latest_snapshot_event" "snapshot_event",
350 "population" INT4,
351 "vote_now" INT4,
352 "vote_later" INT4,
353 "voter_count" INT4,
354 CONSTRAINT "valid_state" CHECK (
355 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
356 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
357 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
358 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
359 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
360 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
361 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
362 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
363 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ),
364 CONSTRAINT "state_change_order" CHECK (
365 "created" <= "accepted" AND
366 "accepted" <= "half_frozen" AND
367 "half_frozen" <= "fully_frozen" AND
368 "fully_frozen" <= "closed" ),
369 CONSTRAINT "last_snapshot_on_full_freeze"
370 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
371 CONSTRAINT "freeze_requires_snapshot"
372 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
373 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
374 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
375 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
376 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
377 CREATE INDEX "issue_created_idx" ON "issue" ("created");
378 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
379 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
380 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
381 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
382 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
383 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
385 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
387 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
388 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.';
389 COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed; 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.';
390 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.';
391 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
392 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
393 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
394 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
395 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
396 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';
397 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';
398 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
399 COMMENT ON COLUMN "issue"."vote_now" IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"';
400 COMMENT ON COLUMN "issue"."vote_later" IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"';
401 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';
404 CREATE TABLE "issue_setting" (
405 PRIMARY KEY ("member_id", "key", "issue_id"),
406 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
407 "key" TEXT NOT NULL,
408 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
409 "value" TEXT NOT NULL );
411 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
414 CREATE TABLE "initiative" (
415 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
416 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
417 "id" SERIAL4 PRIMARY KEY,
418 "name" TEXT NOT NULL,
419 "discussion_url" TEXT,
420 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
421 "revoked" TIMESTAMPTZ,
422 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
423 "admitted" BOOLEAN,
424 "supporter_count" INT4,
425 "informed_supporter_count" INT4,
426 "satisfied_supporter_count" INT4,
427 "satisfied_informed_supporter_count" INT4,
428 "positive_votes" INT4,
429 "negative_votes" INT4,
430 "agreed" BOOLEAN,
431 "rank" INT4,
432 "text_search_data" TSVECTOR,
433 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
434 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
435 CONSTRAINT "revoked_initiatives_cant_be_admitted"
436 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
437 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"
438 CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "agreed" ISNULL)),
439 CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"
440 CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL),
441 CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"
442 CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) );
443 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
444 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
445 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
446 CREATE TRIGGER "update_text_search_data"
447 BEFORE INSERT OR UPDATE ON "initiative"
448 FOR EACH ROW EXECUTE PROCEDURE
449 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
450 "name", "discussion_url");
452 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.';
454 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
455 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
456 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
457 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
458 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
459 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
460 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
461 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
462 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
463 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"';
464 COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';
467 CREATE TABLE "initiative_setting" (
468 PRIMARY KEY ("member_id", "key", "initiative_id"),
469 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
470 "key" TEXT NOT NULL,
471 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
472 "value" TEXT NOT NULL );
474 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
477 CREATE TABLE "draft" (
478 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
479 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
480 "id" SERIAL8 PRIMARY KEY,
481 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
482 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
483 "formatting_engine" TEXT,
484 "content" TEXT NOT NULL,
485 "text_search_data" TSVECTOR );
486 CREATE INDEX "draft_created_idx" ON "draft" ("created");
487 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
488 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
489 CREATE TRIGGER "update_text_search_data"
490 BEFORE INSERT OR UPDATE ON "draft"
491 FOR EACH ROW EXECUTE PROCEDURE
492 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
494 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.';
496 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
497 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
500 CREATE TABLE "suggestion" (
501 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
502 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
503 "id" SERIAL8 PRIMARY KEY,
504 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
505 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
506 "name" TEXT NOT NULL,
507 "description" TEXT NOT NULL DEFAULT '',
508 "text_search_data" TSVECTOR,
509 "minus2_unfulfilled_count" INT4,
510 "minus2_fulfilled_count" INT4,
511 "minus1_unfulfilled_count" INT4,
512 "minus1_fulfilled_count" INT4,
513 "plus1_unfulfilled_count" INT4,
514 "plus1_fulfilled_count" INT4,
515 "plus2_unfulfilled_count" INT4,
516 "plus2_fulfilled_count" INT4 );
517 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
518 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
519 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
520 CREATE TRIGGER "update_text_search_data"
521 BEFORE INSERT OR UPDATE ON "suggestion"
522 FOR EACH ROW EXECUTE PROCEDURE
523 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
524 "name", "description");
526 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';
528 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
529 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
530 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
531 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
532 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
533 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
534 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
535 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
538 CREATE TABLE "suggestion_setting" (
539 PRIMARY KEY ("member_id", "key", "suggestion_id"),
540 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
541 "key" TEXT NOT NULL,
542 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
543 "value" TEXT NOT NULL );
545 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
548 CREATE TABLE "membership" (
549 PRIMARY KEY ("area_id", "member_id"),
550 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
551 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
552 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
553 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
555 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
557 COMMENT ON COLUMN "membership"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure; If there exists an "interest" entry, the interest entry has precedence';
560 CREATE TABLE "interest" (
561 PRIMARY KEY ("issue_id", "member_id"),
562 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
563 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
564 "autoreject" BOOLEAN NOT NULL,
565 "voting_requested" BOOLEAN );
566 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
568 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.';
570 COMMENT ON COLUMN "interest"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure';
571 COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply';
574 CREATE TABLE "initiator" (
575 PRIMARY KEY ("initiative_id", "member_id"),
576 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
577 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
578 "accepted" BOOLEAN );
579 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
581 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.';
583 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.';
586 CREATE TABLE "supporter" (
587 "issue_id" INT4 NOT NULL,
588 PRIMARY KEY ("initiative_id", "member_id"),
589 "initiative_id" INT4,
590 "member_id" INT4,
591 "draft_id" INT8 NOT NULL,
592 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
593 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
594 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
596 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.';
598 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
601 CREATE TABLE "opinion" (
602 "initiative_id" INT4 NOT NULL,
603 PRIMARY KEY ("suggestion_id", "member_id"),
604 "suggestion_id" INT8,
605 "member_id" INT4,
606 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
607 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
608 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
609 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
610 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
612 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.';
614 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
617 CREATE TYPE "delegation_scope" AS ENUM ('global', 'area', 'issue');
619 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''global'', ''area'', or ''issue'' (order is relevant)';
622 CREATE TABLE "delegation" (
623 "id" SERIAL8 PRIMARY KEY,
624 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
625 "trustee_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
626 "scope" "delegation_scope" NOT NULL,
627 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
628 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
629 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
630 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
631 ("scope" = 'global' AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
632 ("scope" = 'area' AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
633 ("scope" = 'issue' AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
634 UNIQUE ("area_id", "truster_id", "trustee_id"),
635 UNIQUE ("issue_id", "truster_id", "trustee_id") );
636 CREATE UNIQUE INDEX "delegation_global_truster_id_trustee_id_unique_idx"
637 ON "delegation" ("truster_id", "trustee_id") WHERE "scope" = 'global';
638 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
639 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
641 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
643 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
644 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
647 CREATE TABLE "direct_population_snapshot" (
648 PRIMARY KEY ("issue_id", "event", "member_id"),
649 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
650 "event" "snapshot_event",
651 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
652 "weight" INT4 );
653 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
655 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
657 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
658 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
661 CREATE TABLE "delegating_population_snapshot" (
662 PRIMARY KEY ("issue_id", "event", "member_id"),
663 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
664 "event" "snapshot_event",
665 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
666 "weight" INT4,
667 "scope" "delegation_scope" NOT NULL,
668 "delegate_member_ids" INT4[] NOT NULL );
669 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
671 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
673 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
674 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
675 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
676 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"';
679 CREATE TABLE "direct_interest_snapshot" (
680 PRIMARY KEY ("issue_id", "event", "member_id"),
681 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
682 "event" "snapshot_event",
683 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
684 "weight" INT4,
685 "voting_requested" BOOLEAN );
686 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
688 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
690 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
691 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
692 COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';
695 CREATE TABLE "delegating_interest_snapshot" (
696 PRIMARY KEY ("issue_id", "event", "member_id"),
697 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
698 "event" "snapshot_event",
699 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
700 "weight" INT4,
701 "scope" "delegation_scope" NOT NULL,
702 "delegate_member_ids" INT4[] NOT NULL );
703 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
705 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
707 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
708 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
709 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
710 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"';
713 CREATE TABLE "direct_supporter_snapshot" (
714 "issue_id" INT4 NOT NULL,
715 PRIMARY KEY ("initiative_id", "event", "member_id"),
716 "initiative_id" INT4,
717 "event" "snapshot_event",
718 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
719 "informed" BOOLEAN NOT NULL,
720 "satisfied" BOOLEAN NOT NULL,
721 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
722 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
723 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
725 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
727 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
728 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
729 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
732 CREATE TABLE "direct_voter" (
733 PRIMARY KEY ("issue_id", "member_id"),
734 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
735 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
736 "weight" INT4,
737 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
738 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
740 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.';
742 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
743 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
746 CREATE TABLE "delegating_voter" (
747 PRIMARY KEY ("issue_id", "member_id"),
748 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
749 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
750 "weight" INT4,
751 "scope" "delegation_scope" NOT NULL,
752 "delegate_member_ids" INT4[] NOT NULL );
753 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
755 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
757 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
758 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
759 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"';
762 CREATE TABLE "vote" (
763 "issue_id" INT4 NOT NULL,
764 PRIMARY KEY ("initiative_id", "member_id"),
765 "initiative_id" INT4,
766 "member_id" INT4,
767 "grade" INT4,
768 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
769 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
770 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
772 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.';
774 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.';
777 CREATE TABLE "contingent" (
778 "time_frame" INTERVAL PRIMARY KEY,
779 "text_entry_limit" INT4,
780 "initiative_limit" INT4 );
782 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.';
784 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';
785 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
789 --------------------------------
790 -- Writing of history entries --
791 --------------------------------
793 CREATE FUNCTION "write_member_history_trigger"()
794 RETURNS TRIGGER
795 LANGUAGE 'plpgsql' VOLATILE AS $$
796 BEGIN
797 IF
798 NEW."active" != OLD."active" OR
799 NEW."name" != OLD."name"
800 THEN
801 INSERT INTO "member_history"
802 ("member_id", "active", "name")
803 VALUES (NEW."id", OLD."active", OLD."name");
804 END IF;
805 RETURN NULL;
806 END;
807 $$;
809 CREATE TRIGGER "write_member_history"
810 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
811 "write_member_history_trigger"();
813 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
814 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
818 ----------------------------
819 -- Additional constraints --
820 ----------------------------
823 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
824 RETURNS TRIGGER
825 LANGUAGE 'plpgsql' VOLATILE AS $$
826 BEGIN
827 IF NOT EXISTS (
828 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
829 ) THEN
830 --RAISE 'Cannot create issue without an initial initiative.' USING
831 -- ERRCODE = 'integrity_constraint_violation',
832 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
833 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
834 END IF;
835 RETURN NULL;
836 END;
837 $$;
839 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
840 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
841 FOR EACH ROW EXECUTE PROCEDURE
842 "issue_requires_first_initiative_trigger"();
844 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
845 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
848 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
849 RETURNS TRIGGER
850 LANGUAGE 'plpgsql' VOLATILE AS $$
851 DECLARE
852 "reference_lost" BOOLEAN;
853 BEGIN
854 IF TG_OP = 'DELETE' THEN
855 "reference_lost" := TRUE;
856 ELSE
857 "reference_lost" := NEW."issue_id" != OLD."issue_id";
858 END IF;
859 IF
860 "reference_lost" AND NOT EXISTS (
861 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
862 )
863 THEN
864 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
865 END IF;
866 RETURN NULL;
867 END;
868 $$;
870 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
871 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
872 FOR EACH ROW EXECUTE PROCEDURE
873 "last_initiative_deletes_issue_trigger"();
875 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
876 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
879 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
880 RETURNS TRIGGER
881 LANGUAGE 'plpgsql' VOLATILE AS $$
882 BEGIN
883 IF NOT EXISTS (
884 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
885 ) THEN
886 --RAISE 'Cannot create initiative without an initial draft.' USING
887 -- ERRCODE = 'integrity_constraint_violation',
888 -- HINT = 'Create issue, initiative and draft within the same transaction.';
889 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
890 END IF;
891 RETURN NULL;
892 END;
893 $$;
895 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
896 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
897 FOR EACH ROW EXECUTE PROCEDURE
898 "initiative_requires_first_draft_trigger"();
900 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
901 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
904 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
905 RETURNS TRIGGER
906 LANGUAGE 'plpgsql' VOLATILE AS $$
907 DECLARE
908 "reference_lost" BOOLEAN;
909 BEGIN
910 IF TG_OP = 'DELETE' THEN
911 "reference_lost" := TRUE;
912 ELSE
913 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
914 END IF;
915 IF
916 "reference_lost" AND NOT EXISTS (
917 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
918 )
919 THEN
920 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
921 END IF;
922 RETURN NULL;
923 END;
924 $$;
926 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
927 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
928 FOR EACH ROW EXECUTE PROCEDURE
929 "last_draft_deletes_initiative_trigger"();
931 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
932 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
935 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
936 RETURNS TRIGGER
937 LANGUAGE 'plpgsql' VOLATILE AS $$
938 BEGIN
939 IF NOT EXISTS (
940 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
941 ) THEN
942 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
943 END IF;
944 RETURN NULL;
945 END;
946 $$;
948 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
949 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
950 FOR EACH ROW EXECUTE PROCEDURE
951 "suggestion_requires_first_opinion_trigger"();
953 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
954 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
957 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
958 RETURNS TRIGGER
959 LANGUAGE 'plpgsql' VOLATILE AS $$
960 DECLARE
961 "reference_lost" BOOLEAN;
962 BEGIN
963 IF TG_OP = 'DELETE' THEN
964 "reference_lost" := TRUE;
965 ELSE
966 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
967 END IF;
968 IF
969 "reference_lost" AND NOT EXISTS (
970 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
971 )
972 THEN
973 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
974 END IF;
975 RETURN NULL;
976 END;
977 $$;
979 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
980 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
981 FOR EACH ROW EXECUTE PROCEDURE
982 "last_opinion_deletes_suggestion_trigger"();
984 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
985 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
989 ---------------------------------------------------------------
990 -- Ensure that votes are not modified when issues are frozen --
991 ---------------------------------------------------------------
993 -- NOTE: Frontends should ensure this anyway, but in case of programming
994 -- errors the following triggers ensure data integrity.
997 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
998 RETURNS TRIGGER
999 LANGUAGE 'plpgsql' VOLATILE AS $$
1000 DECLARE
1001 "issue_id_v" "issue"."id"%TYPE;
1002 "issue_row" "issue"%ROWTYPE;
1003 BEGIN
1004 IF TG_OP = 'DELETE' THEN
1005 "issue_id_v" := OLD."issue_id";
1006 ELSE
1007 "issue_id_v" := NEW."issue_id";
1008 END IF;
1009 SELECT INTO "issue_row" * FROM "issue"
1010 WHERE "id" = "issue_id_v" FOR SHARE;
1011 IF "issue_row"."closed" NOTNULL THEN
1012 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
1013 END IF;
1014 RETURN NULL;
1015 END;
1016 $$;
1018 CREATE TRIGGER "forbid_changes_on_closed_issue"
1019 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1020 FOR EACH ROW EXECUTE PROCEDURE
1021 "forbid_changes_on_closed_issue_trigger"();
1023 CREATE TRIGGER "forbid_changes_on_closed_issue"
1024 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1025 FOR EACH ROW EXECUTE PROCEDURE
1026 "forbid_changes_on_closed_issue_trigger"();
1028 CREATE TRIGGER "forbid_changes_on_closed_issue"
1029 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1030 FOR EACH ROW EXECUTE PROCEDURE
1031 "forbid_changes_on_closed_issue_trigger"();
1033 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"';
1034 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';
1035 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';
1036 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';
1040 --------------------------------------------------------------------
1041 -- Auto-retrieval of fields only needed for referential integrity --
1042 --------------------------------------------------------------------
1045 CREATE FUNCTION "autofill_issue_id_trigger"()
1046 RETURNS TRIGGER
1047 LANGUAGE 'plpgsql' VOLATILE AS $$
1048 BEGIN
1049 IF NEW."issue_id" ISNULL THEN
1050 SELECT "issue_id" INTO NEW."issue_id"
1051 FROM "initiative" WHERE "id" = NEW."initiative_id";
1052 END IF;
1053 RETURN NEW;
1054 END;
1055 $$;
1057 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1058 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1060 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1061 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1063 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1064 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1065 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1068 CREATE FUNCTION "autofill_initiative_id_trigger"()
1069 RETURNS TRIGGER
1070 LANGUAGE 'plpgsql' VOLATILE AS $$
1071 BEGIN
1072 IF NEW."initiative_id" ISNULL THEN
1073 SELECT "initiative_id" INTO NEW."initiative_id"
1074 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1075 END IF;
1076 RETURN NEW;
1077 END;
1078 $$;
1080 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1081 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1083 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1084 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1088 -----------------------------------------------------
1089 -- Automatic calculation of certain default values --
1090 -----------------------------------------------------
1093 CREATE FUNCTION "copy_timings_trigger"()
1094 RETURNS TRIGGER
1095 LANGUAGE 'plpgsql' VOLATILE AS $$
1096 DECLARE
1097 "policy_row" "policy"%ROWTYPE;
1098 BEGIN
1099 SELECT * INTO "policy_row" FROM "policy"
1100 WHERE "id" = NEW."policy_id";
1101 IF NEW."admission_time" ISNULL THEN
1102 NEW."admission_time" := "policy_row"."admission_time";
1103 END IF;
1104 IF NEW."discussion_time" ISNULL THEN
1105 NEW."discussion_time" := "policy_row"."discussion_time";
1106 END IF;
1107 IF NEW."verification_time" ISNULL THEN
1108 NEW."verification_time" := "policy_row"."verification_time";
1109 END IF;
1110 IF NEW."voting_time" ISNULL THEN
1111 NEW."voting_time" := "policy_row"."voting_time";
1112 END IF;
1113 RETURN NEW;
1114 END;
1115 $$;
1117 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1118 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1120 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1121 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1124 CREATE FUNCTION "copy_autoreject_trigger"()
1125 RETURNS TRIGGER
1126 LANGUAGE 'plpgsql' VOLATILE AS $$
1127 BEGIN
1128 IF NEW."autoreject" ISNULL THEN
1129 SELECT "membership"."autoreject" INTO NEW."autoreject"
1130 FROM "issue" JOIN "membership"
1131 ON "issue"."area_id" = "membership"."area_id"
1132 WHERE "issue"."id" = NEW."issue_id"
1133 AND "membership"."member_id" = NEW."member_id";
1134 END IF;
1135 IF NEW."autoreject" ISNULL THEN
1136 NEW."autoreject" := FALSE;
1137 END IF;
1138 RETURN NEW;
1139 END;
1140 $$;
1142 CREATE TRIGGER "copy_autoreject" BEFORE INSERT OR UPDATE ON "interest"
1143 FOR EACH ROW EXECUTE PROCEDURE "copy_autoreject_trigger"();
1145 COMMENT ON FUNCTION "copy_autoreject_trigger"() IS 'Implementation of trigger "copy_autoreject" on table "interest"';
1146 COMMENT ON TRIGGER "copy_autoreject" ON "interest" IS 'If "autoreject" is NULL, then copy it from the area setting, or set to FALSE, if no membership existent';
1149 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
1150 RETURNS TRIGGER
1151 LANGUAGE 'plpgsql' VOLATILE AS $$
1152 BEGIN
1153 IF NEW."draft_id" ISNULL THEN
1154 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1155 WHERE "initiative_id" = NEW."initiative_id";
1156 END IF;
1157 RETURN NEW;
1158 END;
1159 $$;
1161 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1162 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
1164 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
1165 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';
1169 ----------------------------------------
1170 -- Automatic creation of dependencies --
1171 ----------------------------------------
1174 CREATE FUNCTION "autocreate_interest_trigger"()
1175 RETURNS TRIGGER
1176 LANGUAGE 'plpgsql' VOLATILE AS $$
1177 BEGIN
1178 IF NOT EXISTS (
1179 SELECT NULL FROM "initiative" JOIN "interest"
1180 ON "initiative"."issue_id" = "interest"."issue_id"
1181 WHERE "initiative"."id" = NEW."initiative_id"
1182 AND "interest"."member_id" = NEW."member_id"
1183 ) THEN
1184 BEGIN
1185 INSERT INTO "interest" ("issue_id", "member_id")
1186 SELECT "issue_id", NEW."member_id"
1187 FROM "initiative" WHERE "id" = NEW."initiative_id";
1188 EXCEPTION WHEN unique_violation THEN END;
1189 END IF;
1190 RETURN NEW;
1191 END;
1192 $$;
1194 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1195 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1197 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1198 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';
1201 CREATE FUNCTION "autocreate_supporter_trigger"()
1202 RETURNS TRIGGER
1203 LANGUAGE 'plpgsql' VOLATILE AS $$
1204 BEGIN
1205 IF NOT EXISTS (
1206 SELECT NULL FROM "suggestion" JOIN "supporter"
1207 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1208 WHERE "suggestion"."id" = NEW."suggestion_id"
1209 AND "supporter"."member_id" = NEW."member_id"
1210 ) THEN
1211 BEGIN
1212 INSERT INTO "supporter" ("initiative_id", "member_id")
1213 SELECT "initiative_id", NEW."member_id"
1214 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1215 EXCEPTION WHEN unique_violation THEN END;
1216 END IF;
1217 RETURN NEW;
1218 END;
1219 $$;
1221 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1222 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1224 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1225 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.';
1229 ------------------------------------------
1230 -- Views and helper functions for views --
1231 ------------------------------------------
1234 CREATE VIEW "global_delegation" AS
1235 SELECT
1236 "delegation"."id",
1237 "delegation"."truster_id",
1238 "delegation"."trustee_id"
1239 FROM "delegation" JOIN "member"
1240 ON "delegation"."trustee_id" = "member"."id"
1241 WHERE "delegation"."scope" = 'global' AND "member"."active";
1243 COMMENT ON VIEW "global_delegation" IS 'Global delegations to active members';
1246 CREATE VIEW "area_delegation" AS
1247 SELECT "subquery".* FROM (
1248 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1249 "area"."id" AS "area_id",
1250 "delegation"."id",
1251 "delegation"."truster_id",
1252 "delegation"."trustee_id",
1253 "delegation"."scope"
1254 FROM "area" JOIN "delegation"
1255 ON "delegation"."scope" = 'global'
1256 OR "delegation"."area_id" = "area"."id"
1257 ORDER BY
1258 "area"."id",
1259 "delegation"."truster_id",
1260 "delegation"."scope" DESC
1261 ) AS "subquery"
1262 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
1263 WHERE "member"."active";
1265 COMMENT ON VIEW "area_delegation" IS 'Active delegations for areas';
1268 CREATE VIEW "issue_delegation" AS
1269 SELECT "subquery".* FROM (
1270 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1271 "issue"."id" AS "issue_id",
1272 "delegation"."id",
1273 "delegation"."truster_id",
1274 "delegation"."trustee_id",
1275 "delegation"."scope"
1276 FROM "issue" JOIN "delegation"
1277 ON "delegation"."scope" = 'global'
1278 OR "delegation"."area_id" = "issue"."area_id"
1279 OR "delegation"."issue_id" = "issue"."id"
1280 ORDER BY
1281 "issue"."id",
1282 "delegation"."truster_id",
1283 "delegation"."scope" DESC
1284 ) AS "subquery"
1285 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
1286 WHERE "member"."active";
1288 COMMENT ON VIEW "issue_delegation" IS 'Active delegations for issues';
1291 CREATE FUNCTION "membership_weight_with_skipping"
1292 ( "area_id_p" "area"."id"%TYPE,
1293 "member_id_p" "member"."id"%TYPE,
1294 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1295 RETURNS INT4
1296 LANGUAGE 'plpgsql' STABLE AS $$
1297 DECLARE
1298 "sum_v" INT4;
1299 "delegation_row" "area_delegation"%ROWTYPE;
1300 BEGIN
1301 "sum_v" := 1;
1302 FOR "delegation_row" IN
1303 SELECT "area_delegation".*
1304 FROM "area_delegation" LEFT JOIN "membership"
1305 ON "membership"."area_id" = "area_id_p"
1306 AND "membership"."member_id" = "area_delegation"."truster_id"
1307 WHERE "area_delegation"."area_id" = "area_id_p"
1308 AND "area_delegation"."trustee_id" = "member_id_p"
1309 AND "membership"."member_id" ISNULL
1310 LOOP
1311 IF NOT
1312 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1313 THEN
1314 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1315 "area_id_p",
1316 "delegation_row"."truster_id",
1317 "skip_member_ids_p" || "delegation_row"."truster_id"
1318 );
1319 END IF;
1320 END LOOP;
1321 RETURN "sum_v";
1322 END;
1323 $$;
1325 COMMENT ON FUNCTION "membership_weight_with_skipping"
1326 ( "area"."id"%TYPE,
1327 "member"."id"%TYPE,
1328 INT4[] )
1329 IS 'Helper function for "membership_weight" function';
1332 CREATE FUNCTION "membership_weight"
1333 ( "area_id_p" "area"."id"%TYPE,
1334 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1335 RETURNS INT4
1336 LANGUAGE 'plpgsql' STABLE AS $$
1337 BEGIN
1338 RETURN "membership_weight_with_skipping"(
1339 "area_id_p",
1340 "member_id_p",
1341 ARRAY["member_id_p"]
1342 );
1343 END;
1344 $$;
1346 COMMENT ON FUNCTION "membership_weight"
1347 ( "area"."id"%TYPE,
1348 "member"."id"%TYPE )
1349 IS 'Calculates the potential voting weight of a member in a given area';
1352 CREATE VIEW "member_count_view" AS
1353 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1355 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1358 CREATE VIEW "area_member_count" AS
1359 SELECT
1360 "area"."id" AS "area_id",
1361 count("member"."id") AS "direct_member_count",
1362 coalesce(
1363 sum(
1364 CASE WHEN "member"."id" NOTNULL THEN
1365 "membership_weight"("area"."id", "member"."id")
1366 ELSE 0 END
1367 )
1368 ) AS "member_weight",
1369 coalesce(
1370 sum(
1371 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
1372 "membership_weight"("area"."id", "member"."id")
1373 ELSE 0 END
1374 )
1375 ) AS "autoreject_weight"
1376 FROM "area"
1377 LEFT JOIN "membership"
1378 ON "area"."id" = "membership"."area_id"
1379 LEFT JOIN "member"
1380 ON "membership"."member_id" = "member"."id"
1381 AND "member"."active"
1382 GROUP BY "area"."id";
1384 COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"';
1387 CREATE VIEW "opening_draft" AS
1388 SELECT "draft".* FROM (
1389 SELECT
1390 "initiative"."id" AS "initiative_id",
1391 min("draft"."id") AS "draft_id"
1392 FROM "initiative" JOIN "draft"
1393 ON "initiative"."id" = "draft"."initiative_id"
1394 GROUP BY "initiative"."id"
1395 ) AS "subquery"
1396 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1398 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1401 CREATE VIEW "current_draft" AS
1402 SELECT "draft".* FROM (
1403 SELECT
1404 "initiative"."id" AS "initiative_id",
1405 max("draft"."id") AS "draft_id"
1406 FROM "initiative" JOIN "draft"
1407 ON "initiative"."id" = "draft"."initiative_id"
1408 GROUP BY "initiative"."id"
1409 ) AS "subquery"
1410 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1412 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1415 CREATE VIEW "critical_opinion" AS
1416 SELECT * FROM "opinion"
1417 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
1418 OR ("degree" = -2 AND "fulfilled" = TRUE);
1420 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
1423 CREATE VIEW "battle" AS
1424 SELECT
1425 "issue"."id" AS "issue_id",
1426 "winning_initiative"."id" AS "winning_initiative_id",
1427 "losing_initiative"."id" AS "losing_initiative_id",
1428 sum(
1429 CASE WHEN
1430 coalesce("better_vote"."grade", 0) >
1431 coalesce("worse_vote"."grade", 0)
1432 THEN "direct_voter"."weight" ELSE 0 END
1433 ) AS "count"
1434 FROM "issue"
1435 LEFT JOIN "direct_voter"
1436 ON "issue"."id" = "direct_voter"."issue_id"
1437 JOIN "initiative" AS "winning_initiative"
1438 ON "issue"."id" = "winning_initiative"."issue_id"
1439 AND "winning_initiative"."agreed"
1440 JOIN "initiative" AS "losing_initiative"
1441 ON "issue"."id" = "losing_initiative"."issue_id"
1442 AND "losing_initiative"."agreed"
1443 LEFT JOIN "vote" AS "better_vote"
1444 ON "direct_voter"."member_id" = "better_vote"."member_id"
1445 AND "winning_initiative"."id" = "better_vote"."initiative_id"
1446 LEFT JOIN "vote" AS "worse_vote"
1447 ON "direct_voter"."member_id" = "worse_vote"."member_id"
1448 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
1449 WHERE
1450 "winning_initiative"."id" != "losing_initiative"."id"
1451 GROUP BY
1452 "issue"."id",
1453 "winning_initiative"."id",
1454 "losing_initiative"."id";
1456 COMMENT ON VIEW "battle" IS 'Number of members preferring one initiative over another';
1459 CREATE VIEW "expired_session" AS
1460 SELECT * FROM "session" WHERE now() > "expiry";
1462 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
1463 DELETE FROM "session" WHERE "ident" = OLD."ident";
1465 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
1466 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
1469 CREATE VIEW "open_issue" AS
1470 SELECT * FROM "issue" WHERE "closed" ISNULL;
1472 COMMENT ON VIEW "open_issue" IS 'All open issues';
1475 CREATE VIEW "issue_with_ranks_missing" AS
1476 SELECT * FROM "issue"
1477 WHERE "fully_frozen" NOTNULL
1478 AND "closed" NOTNULL
1479 AND "ranks_available" = FALSE;
1481 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
1484 CREATE VIEW "member_contingent" AS
1485 SELECT
1486 "member"."id" AS "member_id",
1487 "contingent"."time_frame",
1488 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
1489 (
1490 SELECT count(1) FROM "draft"
1491 WHERE "draft"."author_id" = "member"."id"
1492 AND "draft"."created" > now() - "contingent"."time_frame"
1493 ) + (
1494 SELECT count(1) FROM "suggestion"
1495 WHERE "suggestion"."author_id" = "member"."id"
1496 AND "suggestion"."created" > now() - "contingent"."time_frame"
1497 )
1498 ELSE NULL END AS "text_entry_count",
1499 "contingent"."text_entry_limit",
1500 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
1501 SELECT count(1) FROM "opening_draft"
1502 WHERE "opening_draft"."author_id" = "member"."id"
1503 AND "opening_draft"."created" > now() - "contingent"."time_frame"
1504 ) ELSE NULL END AS "initiative_count",
1505 "contingent"."initiative_limit"
1506 FROM "member" CROSS JOIN "contingent";
1508 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
1510 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
1511 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
1514 CREATE VIEW "member_contingent_left" AS
1515 SELECT
1516 "member_id",
1517 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
1518 max("initiative_limit" - "initiative_count") AS "initiatives_left"
1519 FROM "member_contingent" GROUP BY "member_id";
1521 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.';
1524 CREATE TYPE "timeline_event" AS ENUM (
1525 'issue_created',
1526 'issue_canceled',
1527 'issue_accepted',
1528 'issue_half_frozen',
1529 'issue_finished_without_voting',
1530 'issue_voting_started',
1531 'issue_finished_after_voting',
1532 'initiative_created',
1533 'initiative_revoked',
1534 'draft_created',
1535 'suggestion_created');
1537 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables';
1540 CREATE VIEW "timeline_issue" AS
1541 SELECT
1542 "created" AS "occurrence",
1543 'issue_created'::"timeline_event" AS "event",
1544 "id" AS "issue_id"
1545 FROM "issue"
1546 UNION ALL
1547 SELECT
1548 "closed" AS "occurrence",
1549 'issue_canceled'::"timeline_event" AS "event",
1550 "id" AS "issue_id"
1551 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
1552 UNION ALL
1553 SELECT
1554 "accepted" AS "occurrence",
1555 'issue_accepted'::"timeline_event" AS "event",
1556 "id" AS "issue_id"
1557 FROM "issue" WHERE "accepted" NOTNULL
1558 UNION ALL
1559 SELECT
1560 "half_frozen" AS "occurrence",
1561 'issue_half_frozen'::"timeline_event" AS "event",
1562 "id" AS "issue_id"
1563 FROM "issue" WHERE "half_frozen" NOTNULL
1564 UNION ALL
1565 SELECT
1566 "fully_frozen" AS "occurrence",
1567 'issue_voting_started'::"timeline_event" AS "event",
1568 "id" AS "issue_id"
1569 FROM "issue"
1570 WHERE "fully_frozen" NOTNULL
1571 AND ("closed" ISNULL OR "closed" != "fully_frozen")
1572 UNION ALL
1573 SELECT
1574 "closed" AS "occurrence",
1575 CASE WHEN "fully_frozen" = "closed" THEN
1576 'issue_finished_without_voting'::"timeline_event"
1577 ELSE
1578 'issue_finished_after_voting'::"timeline_event"
1579 END AS "event",
1580 "id" AS "issue_id"
1581 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
1583 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view';
1586 CREATE VIEW "timeline_initiative" AS
1587 SELECT
1588 "created" AS "occurrence",
1589 'initiative_created'::"timeline_event" AS "event",
1590 "id" AS "initiative_id"
1591 FROM "initiative"
1592 UNION ALL
1593 SELECT
1594 "revoked" AS "occurrence",
1595 'initiative_revoked'::"timeline_event" AS "event",
1596 "id" AS "initiative_id"
1597 FROM "initiative" WHERE "revoked" NOTNULL;
1599 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view';
1602 CREATE VIEW "timeline_draft" AS
1603 SELECT
1604 "created" AS "occurrence",
1605 'draft_created'::"timeline_event" AS "event",
1606 "id" AS "draft_id"
1607 FROM "draft";
1609 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view';
1612 CREATE VIEW "timeline_suggestion" AS
1613 SELECT
1614 "created" AS "occurrence",
1615 'suggestion_created'::"timeline_event" AS "event",
1616 "id" AS "suggestion_id"
1617 FROM "suggestion";
1619 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view';
1622 CREATE VIEW "timeline" AS
1623 SELECT
1624 "occurrence",
1625 "event",
1626 "issue_id",
1627 NULL AS "initiative_id",
1628 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
1629 NULL::INT8 AS "suggestion_id"
1630 FROM "timeline_issue"
1631 UNION ALL
1632 SELECT
1633 "occurrence",
1634 "event",
1635 NULL AS "issue_id",
1636 "initiative_id",
1637 NULL AS "draft_id",
1638 NULL AS "suggestion_id"
1639 FROM "timeline_initiative"
1640 UNION ALL
1641 SELECT
1642 "occurrence",
1643 "event",
1644 NULL AS "issue_id",
1645 NULL AS "initiative_id",
1646 "draft_id",
1647 NULL AS "suggestion_id"
1648 FROM "timeline_draft"
1649 UNION ALL
1650 SELECT
1651 "occurrence",
1652 "event",
1653 NULL AS "issue_id",
1654 NULL AS "initiative_id",
1655 NULL AS "draft_id",
1656 "suggestion_id"
1657 FROM "timeline_suggestion";
1659 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system';
1663 --------------------------------------------------
1664 -- Set returning function for delegation chains --
1665 --------------------------------------------------
1668 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
1669 ('first', 'intermediate', 'last', 'repetition');
1671 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
1674 CREATE TYPE "delegation_chain_row" AS (
1675 "index" INT4,
1676 "member_id" INT4,
1677 "member_active" BOOLEAN,
1678 "participation" BOOLEAN,
1679 "overridden" BOOLEAN,
1680 "scope_in" "delegation_scope",
1681 "scope_out" "delegation_scope",
1682 "loop" "delegation_chain_loop_tag" );
1684 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
1686 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
1687 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';
1688 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
1689 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
1690 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
1691 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
1694 CREATE FUNCTION "delegation_chain"
1695 ( "member_id_p" "member"."id"%TYPE,
1696 "area_id_p" "area"."id"%TYPE,
1697 "issue_id_p" "issue"."id"%TYPE,
1698 "simulate_trustee_id_p" "member"."id"%TYPE )
1699 RETURNS SETOF "delegation_chain_row"
1700 LANGUAGE 'plpgsql' STABLE AS $$
1701 DECLARE
1702 "issue_row" "issue"%ROWTYPE;
1703 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
1704 "loop_member_id_v" "member"."id"%TYPE;
1705 "output_row" "delegation_chain_row";
1706 "output_rows" "delegation_chain_row"[];
1707 "delegation_row" "delegation"%ROWTYPE;
1708 "row_count" INT4;
1709 "i" INT4;
1710 "loop_v" BOOLEAN;
1711 BEGIN
1712 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1713 "visited_member_ids" := '{}';
1714 "loop_member_id_v" := NULL;
1715 "output_rows" := '{}';
1716 "output_row"."index" := 0;
1717 "output_row"."member_id" := "member_id_p";
1718 "output_row"."member_active" := TRUE;
1719 "output_row"."participation" := FALSE;
1720 "output_row"."overridden" := FALSE;
1721 "output_row"."scope_out" := NULL;
1722 LOOP
1723 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
1724 "loop_member_id_v" := "output_row"."member_id";
1725 ELSE
1726 "visited_member_ids" :=
1727 "visited_member_ids" || "output_row"."member_id";
1728 END IF;
1729 IF "output_row"."participation" THEN
1730 "output_row"."overridden" := TRUE;
1731 END IF;
1732 "output_row"."scope_in" := "output_row"."scope_out";
1733 IF EXISTS (
1734 SELECT NULL FROM "member"
1735 WHERE "id" = "output_row"."member_id" AND "active"
1736 ) THEN
1737 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
1738 SELECT * INTO "delegation_row" FROM "delegation"
1739 WHERE "truster_id" = "output_row"."member_id"
1740 AND "scope" = 'global';
1741 ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN
1742 "output_row"."participation" := EXISTS (
1743 SELECT NULL FROM "membership"
1744 WHERE "area_id" = "area_id_p"
1745 AND "member_id" = "output_row"."member_id"
1746 );
1747 SELECT * INTO "delegation_row" FROM "delegation"
1748 WHERE "truster_id" = "output_row"."member_id"
1749 AND ("scope" = 'global' OR "area_id" = "area_id_p")
1750 ORDER BY "scope" DESC;
1751 ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN
1752 "output_row"."participation" := EXISTS (
1753 SELECT NULL FROM "interest"
1754 WHERE "issue_id" = "issue_id_p"
1755 AND "member_id" = "output_row"."member_id"
1756 );
1757 SELECT * INTO "delegation_row" FROM "delegation"
1758 WHERE "truster_id" = "output_row"."member_id"
1759 AND ("scope" = 'global' OR
1760 "area_id" = "issue_row"."area_id" OR
1761 "issue_id" = "issue_id_p"
1762 )
1763 ORDER BY "scope" DESC;
1764 ELSE
1765 RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.';
1766 END IF;
1767 ELSE
1768 "output_row"."member_active" := FALSE;
1769 "output_row"."participation" := FALSE;
1770 "output_row"."scope_out" := NULL;
1771 "delegation_row" := ROW(NULL);
1772 END IF;
1773 IF
1774 "output_row"."member_id" = "member_id_p" AND
1775 "simulate_trustee_id_p" NOTNULL
1776 THEN
1777 "output_row"."scope_out" := CASE
1778 WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global'
1779 WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area'
1780 WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue'
1781 END;
1782 "output_rows" := "output_rows" || "output_row";
1783 "output_row"."member_id" := "simulate_trustee_id_p";
1784 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
1785 "output_row"."scope_out" := "delegation_row"."scope";
1786 "output_rows" := "output_rows" || "output_row";
1787 "output_row"."member_id" := "delegation_row"."trustee_id";
1788 ELSE
1789 "output_row"."scope_out" := NULL;
1790 "output_rows" := "output_rows" || "output_row";
1791 EXIT;
1792 END IF;
1793 EXIT WHEN "loop_member_id_v" NOTNULL;
1794 "output_row"."index" := "output_row"."index" + 1;
1795 END LOOP;
1796 "row_count" := array_upper("output_rows", 1);
1797 "i" := 1;
1798 "loop_v" := FALSE;
1799 LOOP
1800 "output_row" := "output_rows"["i"];
1801 EXIT WHEN "output_row"."member_id" ISNULL;
1802 IF "loop_v" THEN
1803 IF "i" + 1 = "row_count" THEN
1804 "output_row"."loop" := 'last';
1805 ELSIF "i" = "row_count" THEN
1806 "output_row"."loop" := 'repetition';
1807 ELSE
1808 "output_row"."loop" := 'intermediate';
1809 END IF;
1810 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
1811 "output_row"."loop" := 'first';
1812 "loop_v" := TRUE;
1813 END IF;
1814 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
1815 "output_row"."participation" := NULL;
1816 END IF;
1817 RETURN NEXT "output_row";
1818 "i" := "i" + 1;
1819 END LOOP;
1820 RETURN;
1821 END;
1822 $$;
1824 COMMENT ON FUNCTION "delegation_chain"
1825 ( "member"."id"%TYPE,
1826 "area"."id"%TYPE,
1827 "issue"."id"%TYPE,
1828 "member"."id"%TYPE )
1829 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
1831 CREATE FUNCTION "delegation_chain"
1832 ( "member_id_p" "member"."id"%TYPE,
1833 "area_id_p" "area"."id"%TYPE,
1834 "issue_id_p" "issue"."id"%TYPE )
1835 RETURNS SETOF "delegation_chain_row"
1836 LANGUAGE 'plpgsql' STABLE AS $$
1837 DECLARE
1838 "result_row" "delegation_chain_row";
1839 BEGIN
1840 FOR "result_row" IN
1841 SELECT * FROM "delegation_chain"(
1842 "member_id_p", "area_id_p", "issue_id_p", NULL
1843 )
1844 LOOP
1845 RETURN NEXT "result_row";
1846 END LOOP;
1847 RETURN;
1848 END;
1849 $$;
1851 COMMENT ON FUNCTION "delegation_chain"
1852 ( "member"."id"%TYPE,
1853 "area"."id"%TYPE,
1854 "issue"."id"%TYPE )
1855 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
1859 ------------------------------
1860 -- Comparison by vote count --
1861 ------------------------------
1863 CREATE FUNCTION "vote_ratio"
1864 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
1865 "negative_votes_p" "initiative"."negative_votes"%TYPE )
1866 RETURNS FLOAT8
1867 LANGUAGE 'plpgsql' STABLE AS $$
1868 BEGIN
1869 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
1870 RETURN
1871 "positive_votes_p"::FLOAT8 /
1872 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
1873 ELSIF "positive_votes_p" > 0 THEN
1874 RETURN "positive_votes_p";
1875 ELSIF "negative_votes_p" > 0 THEN
1876 RETURN 1 - "negative_votes_p";
1877 ELSE
1878 RETURN 0.5;
1879 END IF;
1880 END;
1881 $$;
1883 COMMENT ON FUNCTION "vote_ratio"
1884 ( "initiative"."positive_votes"%TYPE,
1885 "initiative"."negative_votes"%TYPE )
1886 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.';
1890 ------------------------------------------------
1891 -- Locking for snapshots and voting procedure --
1892 ------------------------------------------------
1894 CREATE FUNCTION "global_lock"() RETURNS VOID
1895 LANGUAGE 'plpgsql' VOLATILE AS $$
1896 BEGIN
1897 -- NOTE: PostgreSQL allows reading, while tables are locked in
1898 -- exclusive move. Transactions should be kept short anyway!
1899 LOCK TABLE "member" IN EXCLUSIVE MODE;
1900 LOCK TABLE "area" IN EXCLUSIVE MODE;
1901 LOCK TABLE "membership" IN EXCLUSIVE MODE;
1902 -- NOTE: "member", "area" and "membership" are locked first to
1903 -- prevent deadlocks in combination with "calculate_member_counts"()
1904 LOCK TABLE "policy" IN EXCLUSIVE MODE;
1905 LOCK TABLE "issue" IN EXCLUSIVE MODE;
1906 LOCK TABLE "initiative" IN EXCLUSIVE MODE;
1907 LOCK TABLE "draft" IN EXCLUSIVE MODE;
1908 LOCK TABLE "suggestion" IN EXCLUSIVE MODE;
1909 LOCK TABLE "interest" IN EXCLUSIVE MODE;
1910 LOCK TABLE "initiator" IN EXCLUSIVE MODE;
1911 LOCK TABLE "supporter" IN EXCLUSIVE MODE;
1912 LOCK TABLE "opinion" IN EXCLUSIVE MODE;
1913 LOCK TABLE "delegation" IN EXCLUSIVE MODE;
1914 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
1915 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
1916 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
1917 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
1918 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
1919 LOCK TABLE "direct_voter" IN EXCLUSIVE MODE;
1920 LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;
1921 LOCK TABLE "vote" IN EXCLUSIVE MODE;
1922 RETURN;
1923 END;
1924 $$;
1926 COMMENT ON FUNCTION "global_lock"() IS 'Locks all tables related to support/voting until end of transaction; read access is still possible though';
1930 -------------------------------
1931 -- Materialize member counts --
1932 -------------------------------
1934 CREATE FUNCTION "calculate_member_counts"()
1935 RETURNS VOID
1936 LANGUAGE 'plpgsql' VOLATILE AS $$
1937 BEGIN
1938 LOCK TABLE "member" IN EXCLUSIVE MODE;
1939 LOCK TABLE "area" IN EXCLUSIVE MODE;
1940 LOCK TABLE "membership" IN EXCLUSIVE MODE;
1941 DELETE FROM "member_count";
1942 INSERT INTO "member_count" ("total_count")
1943 SELECT "total_count" FROM "member_count_view";
1944 UPDATE "area" SET
1945 "direct_member_count" = "view"."direct_member_count",
1946 "member_weight" = "view"."member_weight",
1947 "autoreject_weight" = "view"."autoreject_weight"
1948 FROM "area_member_count" AS "view"
1949 WHERE "view"."area_id" = "area"."id";
1950 RETURN;
1951 END;
1952 $$;
1954 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"';
1958 ------------------------------
1959 -- Calculation of snapshots --
1960 ------------------------------
1962 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
1963 ( "issue_id_p" "issue"."id"%TYPE,
1964 "member_id_p" "member"."id"%TYPE,
1965 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
1966 RETURNS "direct_population_snapshot"."weight"%TYPE
1967 LANGUAGE 'plpgsql' VOLATILE AS $$
1968 DECLARE
1969 "issue_delegation_row" "issue_delegation"%ROWTYPE;
1970 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
1971 "weight_v" INT4;
1972 "sub_weight_v" INT4;
1973 BEGIN
1974 "weight_v" := 0;
1975 FOR "issue_delegation_row" IN
1976 SELECT * FROM "issue_delegation"
1977 WHERE "trustee_id" = "member_id_p"
1978 AND "issue_id" = "issue_id_p"
1979 LOOP
1980 IF NOT EXISTS (
1981 SELECT NULL FROM "direct_population_snapshot"
1982 WHERE "issue_id" = "issue_id_p"
1983 AND "event" = 'periodic'
1984 AND "member_id" = "issue_delegation_row"."truster_id"
1985 ) AND NOT EXISTS (
1986 SELECT NULL FROM "delegating_population_snapshot"
1987 WHERE "issue_id" = "issue_id_p"
1988 AND "event" = 'periodic'
1989 AND "member_id" = "issue_delegation_row"."truster_id"
1990 ) THEN
1991 "delegate_member_ids_v" :=
1992 "member_id_p" || "delegate_member_ids_p";
1993 INSERT INTO "delegating_population_snapshot" (
1994 "issue_id",
1995 "event",
1996 "member_id",
1997 "scope",
1998 "delegate_member_ids"
1999 ) VALUES (
2000 "issue_id_p",
2001 'periodic',
2002 "issue_delegation_row"."truster_id",
2003 "issue_delegation_row"."scope",
2004 "delegate_member_ids_v"
2005 );
2006 "sub_weight_v" := 1 +
2007 "weight_of_added_delegations_for_population_snapshot"(
2008 "issue_id_p",
2009 "issue_delegation_row"."truster_id",
2010 "delegate_member_ids_v"
2011 );
2012 UPDATE "delegating_population_snapshot"
2013 SET "weight" = "sub_weight_v"
2014 WHERE "issue_id" = "issue_id_p"
2015 AND "event" = 'periodic'
2016 AND "member_id" = "issue_delegation_row"."truster_id";
2017 "weight_v" := "weight_v" + "sub_weight_v";
2018 END IF;
2019 END LOOP;
2020 RETURN "weight_v";
2021 END;
2022 $$;
2024 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
2025 ( "issue"."id"%TYPE,
2026 "member"."id"%TYPE,
2027 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2028 IS 'Helper function for "create_population_snapshot" function';
2031 CREATE FUNCTION "create_population_snapshot"
2032 ( "issue_id_p" "issue"."id"%TYPE )
2033 RETURNS VOID
2034 LANGUAGE 'plpgsql' VOLATILE AS $$
2035 DECLARE
2036 "member_id_v" "member"."id"%TYPE;
2037 BEGIN
2038 DELETE FROM "direct_population_snapshot"
2039 WHERE "issue_id" = "issue_id_p"
2040 AND "event" = 'periodic';
2041 DELETE FROM "delegating_population_snapshot"
2042 WHERE "issue_id" = "issue_id_p"
2043 AND "event" = 'periodic';
2044 INSERT INTO "direct_population_snapshot"
2045 ("issue_id", "event", "member_id")
2046 SELECT
2047 "issue_id_p" AS "issue_id",
2048 'periodic'::"snapshot_event" AS "event",
2049 "member"."id" AS "member_id"
2050 FROM "issue"
2051 JOIN "area" ON "issue"."area_id" = "area"."id"
2052 JOIN "membership" ON "area"."id" = "membership"."area_id"
2053 JOIN "member" ON "membership"."member_id" = "member"."id"
2054 WHERE "issue"."id" = "issue_id_p"
2055 AND "member"."active"
2056 UNION
2057 SELECT
2058 "issue_id_p" AS "issue_id",
2059 'periodic'::"snapshot_event" AS "event",
2060 "member"."id" AS "member_id"
2061 FROM "interest" JOIN "member"
2062 ON "interest"."member_id" = "member"."id"
2063 WHERE "interest"."issue_id" = "issue_id_p"
2064 AND "member"."active";
2065 FOR "member_id_v" IN
2066 SELECT "member_id" FROM "direct_population_snapshot"
2067 WHERE "issue_id" = "issue_id_p"
2068 AND "event" = 'periodic'
2069 LOOP
2070 UPDATE "direct_population_snapshot" SET
2071 "weight" = 1 +
2072 "weight_of_added_delegations_for_population_snapshot"(
2073 "issue_id_p",
2074 "member_id_v",
2075 '{}'
2076 )
2077 WHERE "issue_id" = "issue_id_p"
2078 AND "event" = 'periodic'
2079 AND "member_id" = "member_id_v";
2080 END LOOP;
2081 RETURN;
2082 END;
2083 $$;
2085 COMMENT ON FUNCTION "create_population_snapshot"
2086 ( "issue_id_p" "issue"."id"%TYPE )
2087 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.';
2090 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2091 ( "issue_id_p" "issue"."id"%TYPE,
2092 "member_id_p" "member"."id"%TYPE,
2093 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2094 RETURNS "direct_interest_snapshot"."weight"%TYPE
2095 LANGUAGE 'plpgsql' VOLATILE AS $$
2096 DECLARE
2097 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2098 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2099 "weight_v" INT4;
2100 "sub_weight_v" INT4;
2101 BEGIN
2102 "weight_v" := 0;
2103 FOR "issue_delegation_row" IN
2104 SELECT * FROM "issue_delegation"
2105 WHERE "trustee_id" = "member_id_p"
2106 AND "issue_id" = "issue_id_p"
2107 LOOP
2108 IF NOT EXISTS (
2109 SELECT NULL FROM "direct_interest_snapshot"
2110 WHERE "issue_id" = "issue_id_p"
2111 AND "event" = 'periodic'
2112 AND "member_id" = "issue_delegation_row"."truster_id"
2113 ) AND NOT EXISTS (
2114 SELECT NULL FROM "delegating_interest_snapshot"
2115 WHERE "issue_id" = "issue_id_p"
2116 AND "event" = 'periodic'
2117 AND "member_id" = "issue_delegation_row"."truster_id"
2118 ) THEN
2119 "delegate_member_ids_v" :=
2120 "member_id_p" || "delegate_member_ids_p";
2121 INSERT INTO "delegating_interest_snapshot" (
2122 "issue_id",
2123 "event",
2124 "member_id",
2125 "scope",
2126 "delegate_member_ids"
2127 ) VALUES (
2128 "issue_id_p",
2129 'periodic',
2130 "issue_delegation_row"."truster_id",
2131 "issue_delegation_row"."scope",
2132 "delegate_member_ids_v"
2133 );
2134 "sub_weight_v" := 1 +
2135 "weight_of_added_delegations_for_interest_snapshot"(
2136 "issue_id_p",
2137 "issue_delegation_row"."truster_id",
2138 "delegate_member_ids_v"
2139 );
2140 UPDATE "delegating_interest_snapshot"
2141 SET "weight" = "sub_weight_v"
2142 WHERE "issue_id" = "issue_id_p"
2143 AND "event" = 'periodic'
2144 AND "member_id" = "issue_delegation_row"."truster_id";
2145 "weight_v" := "weight_v" + "sub_weight_v";
2146 END IF;
2147 END LOOP;
2148 RETURN "weight_v";
2149 END;
2150 $$;
2152 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2153 ( "issue"."id"%TYPE,
2154 "member"."id"%TYPE,
2155 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2156 IS 'Helper function for "create_interest_snapshot" function';
2159 CREATE FUNCTION "create_interest_snapshot"
2160 ( "issue_id_p" "issue"."id"%TYPE )
2161 RETURNS VOID
2162 LANGUAGE 'plpgsql' VOLATILE AS $$
2163 DECLARE
2164 "member_id_v" "member"."id"%TYPE;
2165 BEGIN
2166 DELETE FROM "direct_interest_snapshot"
2167 WHERE "issue_id" = "issue_id_p"
2168 AND "event" = 'periodic';
2169 DELETE FROM "delegating_interest_snapshot"
2170 WHERE "issue_id" = "issue_id_p"
2171 AND "event" = 'periodic';
2172 DELETE FROM "direct_supporter_snapshot"
2173 WHERE "issue_id" = "issue_id_p"
2174 AND "event" = 'periodic';
2175 INSERT INTO "direct_interest_snapshot"
2176 ("issue_id", "event", "member_id", "voting_requested")
2177 SELECT
2178 "issue_id_p" AS "issue_id",
2179 'periodic' AS "event",
2180 "member"."id" AS "member_id",
2181 "interest"."voting_requested"
2182 FROM "interest" JOIN "member"
2183 ON "interest"."member_id" = "member"."id"
2184 WHERE "interest"."issue_id" = "issue_id_p"
2185 AND "member"."active";
2186 FOR "member_id_v" IN
2187 SELECT "member_id" FROM "direct_interest_snapshot"
2188 WHERE "issue_id" = "issue_id_p"
2189 AND "event" = 'periodic'
2190 LOOP
2191 UPDATE "direct_interest_snapshot" SET
2192 "weight" = 1 +
2193 "weight_of_added_delegations_for_interest_snapshot"(
2194 "issue_id_p",
2195 "member_id_v",
2196 '{}'
2197 )
2198 WHERE "issue_id" = "issue_id_p"
2199 AND "event" = 'periodic'
2200 AND "member_id" = "member_id_v";
2201 END LOOP;
2202 INSERT INTO "direct_supporter_snapshot"
2203 ( "issue_id", "initiative_id", "event", "member_id",
2204 "informed", "satisfied" )
2205 SELECT
2206 "issue_id_p" AS "issue_id",
2207 "initiative"."id" AS "initiative_id",
2208 'periodic' AS "event",
2209 "member"."id" AS "member_id",
2210 "supporter"."draft_id" = "current_draft"."id" AS "informed",
2211 NOT EXISTS (
2212 SELECT NULL FROM "critical_opinion"
2213 WHERE "initiative_id" = "initiative"."id"
2214 AND "member_id" = "member"."id"
2215 ) AS "satisfied"
2216 FROM "supporter"
2217 JOIN "member"
2218 ON "supporter"."member_id" = "member"."id"
2219 JOIN "initiative"
2220 ON "supporter"."initiative_id" = "initiative"."id"
2221 JOIN "current_draft"
2222 ON "initiative"."id" = "current_draft"."initiative_id"
2223 JOIN "direct_interest_snapshot"
2224 ON "member"."id" = "direct_interest_snapshot"."member_id"
2225 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
2226 AND "event" = 'periodic'
2227 WHERE "member"."active"
2228 AND "initiative"."issue_id" = "issue_id_p";
2229 RETURN;
2230 END;
2231 $$;
2233 COMMENT ON FUNCTION "create_interest_snapshot"
2234 ( "issue"."id"%TYPE )
2235 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.';
2238 CREATE FUNCTION "create_snapshot"
2239 ( "issue_id_p" "issue"."id"%TYPE )
2240 RETURNS VOID
2241 LANGUAGE 'plpgsql' VOLATILE AS $$
2242 DECLARE
2243 "initiative_id_v" "initiative"."id"%TYPE;
2244 "suggestion_id_v" "suggestion"."id"%TYPE;
2245 BEGIN
2246 PERFORM "global_lock"();
2247 PERFORM "create_population_snapshot"("issue_id_p");
2248 PERFORM "create_interest_snapshot"("issue_id_p");
2249 UPDATE "issue" SET
2250 "snapshot" = now(),
2251 "latest_snapshot_event" = 'periodic',
2252 "population" = (
2253 SELECT coalesce(sum("weight"), 0)
2254 FROM "direct_population_snapshot"
2255 WHERE "issue_id" = "issue_id_p"
2256 AND "event" = 'periodic'
2257 ),
2258 "vote_now" = (
2259 SELECT coalesce(sum("weight"), 0)
2260 FROM "direct_interest_snapshot"
2261 WHERE "issue_id" = "issue_id_p"
2262 AND "event" = 'periodic'
2263 AND "voting_requested" = TRUE
2264 ),
2265 "vote_later" = (
2266 SELECT coalesce(sum("weight"), 0)
2267 FROM "direct_interest_snapshot"
2268 WHERE "issue_id" = "issue_id_p"
2269 AND "event" = 'periodic'
2270 AND "voting_requested" = FALSE
2271 )
2272 WHERE "id" = "issue_id_p";
2273 FOR "initiative_id_v" IN
2274 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
2275 LOOP
2276 UPDATE "initiative" SET
2277 "supporter_count" = (
2278 SELECT coalesce(sum("di"."weight"), 0)
2279 FROM "direct_interest_snapshot" AS "di"
2280 JOIN "direct_supporter_snapshot" AS "ds"
2281 ON "di"."member_id" = "ds"."member_id"
2282 WHERE "di"."issue_id" = "issue_id_p"
2283 AND "di"."event" = 'periodic'
2284 AND "ds"."initiative_id" = "initiative_id_v"
2285 AND "ds"."event" = 'periodic'
2286 ),
2287 "informed_supporter_count" = (
2288 SELECT coalesce(sum("di"."weight"), 0)
2289 FROM "direct_interest_snapshot" AS "di"
2290 JOIN "direct_supporter_snapshot" AS "ds"
2291 ON "di"."member_id" = "ds"."member_id"
2292 WHERE "di"."issue_id" = "issue_id_p"
2293 AND "di"."event" = 'periodic'
2294 AND "ds"."initiative_id" = "initiative_id_v"
2295 AND "ds"."event" = 'periodic'
2296 AND "ds"."informed"
2297 ),
2298 "satisfied_supporter_count" = (
2299 SELECT coalesce(sum("di"."weight"), 0)
2300 FROM "direct_interest_snapshot" AS "di"
2301 JOIN "direct_supporter_snapshot" AS "ds"
2302 ON "di"."member_id" = "ds"."member_id"
2303 WHERE "di"."issue_id" = "issue_id_p"
2304 AND "di"."event" = 'periodic'
2305 AND "ds"."initiative_id" = "initiative_id_v"
2306 AND "ds"."event" = 'periodic'
2307 AND "ds"."satisfied"
2308 ),
2309 "satisfied_informed_supporter_count" = (
2310 SELECT coalesce(sum("di"."weight"), 0)
2311 FROM "direct_interest_snapshot" AS "di"
2312 JOIN "direct_supporter_snapshot" AS "ds"
2313 ON "di"."member_id" = "ds"."member_id"
2314 WHERE "di"."issue_id" = "issue_id_p"
2315 AND "di"."event" = 'periodic'
2316 AND "ds"."initiative_id" = "initiative_id_v"
2317 AND "ds"."event" = 'periodic'
2318 AND "ds"."informed"
2319 AND "ds"."satisfied"
2320 )
2321 WHERE "id" = "initiative_id_v";
2322 FOR "suggestion_id_v" IN
2323 SELECT "id" FROM "suggestion"
2324 WHERE "initiative_id" = "initiative_id_v"
2325 LOOP
2326 UPDATE "suggestion" SET
2327 "minus2_unfulfilled_count" = (
2328 SELECT coalesce(sum("snapshot"."weight"), 0)
2329 FROM "issue" CROSS JOIN "opinion"
2330 JOIN "direct_interest_snapshot" AS "snapshot"
2331 ON "snapshot"."issue_id" = "issue"."id"
2332 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2333 AND "snapshot"."member_id" = "opinion"."member_id"
2334 WHERE "issue"."id" = "issue_id_p"
2335 AND "opinion"."suggestion_id" = "suggestion_id_v"
2336 AND "opinion"."degree" = -2
2337 AND "opinion"."fulfilled" = FALSE
2338 ),
2339 "minus2_fulfilled_count" = (
2340 SELECT coalesce(sum("snapshot"."weight"), 0)
2341 FROM "issue" CROSS JOIN "opinion"
2342 JOIN "direct_interest_snapshot" AS "snapshot"
2343 ON "snapshot"."issue_id" = "issue"."id"
2344 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2345 AND "snapshot"."member_id" = "opinion"."member_id"
2346 WHERE "issue"."id" = "issue_id_p"
2347 AND "opinion"."suggestion_id" = "suggestion_id_v"
2348 AND "opinion"."degree" = -2
2349 AND "opinion"."fulfilled" = TRUE
2350 ),
2351 "minus1_unfulfilled_count" = (
2352 SELECT coalesce(sum("snapshot"."weight"), 0)
2353 FROM "issue" CROSS JOIN "opinion"
2354 JOIN "direct_interest_snapshot" AS "snapshot"
2355 ON "snapshot"."issue_id" = "issue"."id"
2356 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2357 AND "snapshot"."member_id" = "opinion"."member_id"
2358 WHERE "issue"."id" = "issue_id_p"
2359 AND "opinion"."suggestion_id" = "suggestion_id_v"
2360 AND "opinion"."degree" = -1
2361 AND "opinion"."fulfilled" = FALSE
2362 ),
2363 "minus1_fulfilled_count" = (
2364 SELECT coalesce(sum("snapshot"."weight"), 0)
2365 FROM "issue" CROSS JOIN "opinion"
2366 JOIN "direct_interest_snapshot" AS "snapshot"
2367 ON "snapshot"."issue_id" = "issue"."id"
2368 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2369 AND "snapshot"."member_id" = "opinion"."member_id"
2370 WHERE "issue"."id" = "issue_id_p"
2371 AND "opinion"."suggestion_id" = "suggestion_id_v"
2372 AND "opinion"."degree" = -1
2373 AND "opinion"."fulfilled" = TRUE
2374 ),
2375 "plus1_unfulfilled_count" = (
2376 SELECT coalesce(sum("snapshot"."weight"), 0)
2377 FROM "issue" CROSS JOIN "opinion"
2378 JOIN "direct_interest_snapshot" AS "snapshot"
2379 ON "snapshot"."issue_id" = "issue"."id"
2380 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2381 AND "snapshot"."member_id" = "opinion"."member_id"
2382 WHERE "issue"."id" = "issue_id_p"
2383 AND "opinion"."suggestion_id" = "suggestion_id_v"
2384 AND "opinion"."degree" = 1
2385 AND "opinion"."fulfilled" = FALSE
2386 ),
2387 "plus1_fulfilled_count" = (
2388 SELECT coalesce(sum("snapshot"."weight"), 0)
2389 FROM "issue" CROSS JOIN "opinion"
2390 JOIN "direct_interest_snapshot" AS "snapshot"
2391 ON "snapshot"."issue_id" = "issue"."id"
2392 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2393 AND "snapshot"."member_id" = "opinion"."member_id"
2394 WHERE "issue"."id" = "issue_id_p"
2395 AND "opinion"."suggestion_id" = "suggestion_id_v"
2396 AND "opinion"."degree" = 1
2397 AND "opinion"."fulfilled" = TRUE
2398 ),
2399 "plus2_unfulfilled_count" = (
2400 SELECT coalesce(sum("snapshot"."weight"), 0)
2401 FROM "issue" CROSS JOIN "opinion"
2402 JOIN "direct_interest_snapshot" AS "snapshot"
2403 ON "snapshot"."issue_id" = "issue"."id"
2404 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2405 AND "snapshot"."member_id" = "opinion"."member_id"
2406 WHERE "issue"."id" = "issue_id_p"
2407 AND "opinion"."suggestion_id" = "suggestion_id_v"
2408 AND "opinion"."degree" = 2
2409 AND "opinion"."fulfilled" = FALSE
2410 ),
2411 "plus2_fulfilled_count" = (
2412 SELECT coalesce(sum("snapshot"."weight"), 0)
2413 FROM "issue" CROSS JOIN "opinion"
2414 JOIN "direct_interest_snapshot" AS "snapshot"
2415 ON "snapshot"."issue_id" = "issue"."id"
2416 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2417 AND "snapshot"."member_id" = "opinion"."member_id"
2418 WHERE "issue"."id" = "issue_id_p"
2419 AND "opinion"."suggestion_id" = "suggestion_id_v"
2420 AND "opinion"."degree" = 2
2421 AND "opinion"."fulfilled" = TRUE
2422 )
2423 WHERE "suggestion"."id" = "suggestion_id_v";
2424 END LOOP;
2425 END LOOP;
2426 RETURN;
2427 END;
2428 $$;
2430 COMMENT ON FUNCTION "create_snapshot"
2431 ( "issue"."id"%TYPE )
2432 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.';
2435 CREATE FUNCTION "set_snapshot_event"
2436 ( "issue_id_p" "issue"."id"%TYPE,
2437 "event_p" "snapshot_event" )
2438 RETURNS VOID
2439 LANGUAGE 'plpgsql' VOLATILE AS $$
2440 DECLARE
2441 "event_v" "issue"."latest_snapshot_event"%TYPE;
2442 BEGIN
2443 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
2444 WHERE "id" = "issue_id_p" FOR UPDATE;
2445 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
2446 WHERE "id" = "issue_id_p";
2447 UPDATE "direct_population_snapshot" SET "event" = "event_p"
2448 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2449 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
2450 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2451 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
2452 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2453 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
2454 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2455 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
2456 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2457 RETURN;
2458 END;
2459 $$;
2461 COMMENT ON FUNCTION "set_snapshot_event"
2462 ( "issue"."id"%TYPE,
2463 "snapshot_event" )
2464 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
2468 ---------------------
2469 -- Freezing issues --
2470 ---------------------
2472 CREATE FUNCTION "freeze_after_snapshot"
2473 ( "issue_id_p" "issue"."id"%TYPE )
2474 RETURNS VOID
2475 LANGUAGE 'plpgsql' VOLATILE AS $$
2476 DECLARE
2477 "issue_row" "issue"%ROWTYPE;
2478 "policy_row" "policy"%ROWTYPE;
2479 "initiative_row" "initiative"%ROWTYPE;
2480 BEGIN
2481 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2482 SELECT * INTO "policy_row"
2483 FROM "policy" WHERE "id" = "issue_row"."policy_id";
2484 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
2485 UPDATE "issue" SET
2486 "accepted" = coalesce("accepted", now()),
2487 "half_frozen" = coalesce("half_frozen", now()),
2488 "fully_frozen" = now()
2489 WHERE "id" = "issue_id_p";
2490 FOR "initiative_row" IN
2491 SELECT * FROM "initiative"
2492 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
2493 LOOP
2494 IF
2495 "initiative_row"."satisfied_supporter_count" > 0 AND
2496 "initiative_row"."satisfied_supporter_count" *
2497 "policy_row"."initiative_quorum_den" >=
2498 "issue_row"."population" * "policy_row"."initiative_quorum_num"
2499 THEN
2500 UPDATE "initiative" SET "admitted" = TRUE
2501 WHERE "id" = "initiative_row"."id";
2502 ELSE
2503 UPDATE "initiative" SET "admitted" = FALSE
2504 WHERE "id" = "initiative_row"."id";
2505 END IF;
2506 END LOOP;
2507 IF NOT EXISTS (
2508 SELECT NULL FROM "initiative"
2509 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
2510 ) THEN
2511 PERFORM "close_voting"("issue_id_p");
2512 END IF;
2513 RETURN;
2514 END;
2515 $$;
2517 COMMENT ON FUNCTION "freeze_after_snapshot"
2518 ( "issue"."id"%TYPE )
2519 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
2522 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
2523 RETURNS VOID
2524 LANGUAGE 'plpgsql' VOLATILE AS $$
2525 DECLARE
2526 "issue_row" "issue"%ROWTYPE;
2527 BEGIN
2528 PERFORM "create_snapshot"("issue_id_p");
2529 PERFORM "freeze_after_snapshot"("issue_id_p");
2530 RETURN;
2531 END;
2532 $$;
2534 COMMENT ON FUNCTION "manual_freeze"
2535 ( "issue"."id"%TYPE )
2536 IS 'Freeze an issue manually (fully) and start voting';
2540 -----------------------
2541 -- Counting of votes --
2542 -----------------------
2545 CREATE FUNCTION "weight_of_added_vote_delegations"
2546 ( "issue_id_p" "issue"."id"%TYPE,
2547 "member_id_p" "member"."id"%TYPE,
2548 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
2549 RETURNS "direct_voter"."weight"%TYPE
2550 LANGUAGE 'plpgsql' VOLATILE AS $$
2551 DECLARE
2552 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2553 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
2554 "weight_v" INT4;
2555 "sub_weight_v" INT4;
2556 BEGIN
2557 "weight_v" := 0;
2558 FOR "issue_delegation_row" IN
2559 SELECT * FROM "issue_delegation"
2560 WHERE "trustee_id" = "member_id_p"
2561 AND "issue_id" = "issue_id_p"
2562 LOOP
2563 IF NOT EXISTS (
2564 SELECT NULL FROM "direct_voter"
2565 WHERE "member_id" = "issue_delegation_row"."truster_id"
2566 AND "issue_id" = "issue_id_p"
2567 ) AND NOT EXISTS (
2568 SELECT NULL FROM "delegating_voter"
2569 WHERE "member_id" = "issue_delegation_row"."truster_id"
2570 AND "issue_id" = "issue_id_p"
2571 ) THEN
2572 "delegate_member_ids_v" :=
2573 "member_id_p" || "delegate_member_ids_p";
2574 INSERT INTO "delegating_voter" (
2575 "issue_id",
2576 "member_id",
2577 "scope",
2578 "delegate_member_ids"
2579 ) VALUES (
2580 "issue_id_p",
2581 "issue_delegation_row"."truster_id",
2582 "issue_delegation_row"."scope",
2583 "delegate_member_ids_v"
2584 );
2585 "sub_weight_v" := 1 +
2586 "weight_of_added_vote_delegations"(
2587 "issue_id_p",
2588 "issue_delegation_row"."truster_id",
2589 "delegate_member_ids_v"
2590 );
2591 UPDATE "delegating_voter"
2592 SET "weight" = "sub_weight_v"
2593 WHERE "issue_id" = "issue_id_p"
2594 AND "member_id" = "issue_delegation_row"."truster_id";
2595 "weight_v" := "weight_v" + "sub_weight_v";
2596 END IF;
2597 END LOOP;
2598 RETURN "weight_v";
2599 END;
2600 $$;
2602 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
2603 ( "issue"."id"%TYPE,
2604 "member"."id"%TYPE,
2605 "delegating_voter"."delegate_member_ids"%TYPE )
2606 IS 'Helper function for "add_vote_delegations" function';
2609 CREATE FUNCTION "add_vote_delegations"
2610 ( "issue_id_p" "issue"."id"%TYPE )
2611 RETURNS VOID
2612 LANGUAGE 'plpgsql' VOLATILE AS $$
2613 DECLARE
2614 "member_id_v" "member"."id"%TYPE;
2615 BEGIN
2616 FOR "member_id_v" IN
2617 SELECT "member_id" FROM "direct_voter"
2618 WHERE "issue_id" = "issue_id_p"
2619 LOOP
2620 UPDATE "direct_voter" SET
2621 "weight" = "weight" + "weight_of_added_vote_delegations"(
2622 "issue_id_p",
2623 "member_id_v",
2624 '{}'
2625 )
2626 WHERE "member_id" = "member_id_v"
2627 AND "issue_id" = "issue_id_p";
2628 END LOOP;
2629 RETURN;
2630 END;
2631 $$;
2633 COMMENT ON FUNCTION "add_vote_delegations"
2634 ( "issue_id_p" "issue"."id"%TYPE )
2635 IS 'Helper function for "close_voting" function';
2638 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
2639 RETURNS VOID
2640 LANGUAGE 'plpgsql' VOLATILE AS $$
2641 DECLARE
2642 "issue_row" "issue"%ROWTYPE;
2643 "member_id_v" "member"."id"%TYPE;
2644 BEGIN
2645 PERFORM "global_lock"();
2646 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2647 DELETE FROM "delegating_voter"
2648 WHERE "issue_id" = "issue_id_p";
2649 DELETE FROM "direct_voter"
2650 WHERE "issue_id" = "issue_id_p"
2651 AND "autoreject" = TRUE;
2652 DELETE FROM "direct_voter" USING "member"
2653 WHERE "direct_voter"."member_id" = "member"."id"
2654 AND "direct_voter"."issue_id" = "issue_id_p"
2655 AND "member"."active" = FALSE;
2656 UPDATE "direct_voter" SET "weight" = 1
2657 WHERE "issue_id" = "issue_id_p";
2658 PERFORM "add_vote_delegations"("issue_id_p");
2659 FOR "member_id_v" IN
2660 SELECT "interest"."member_id"
2661 FROM "interest"
2662 LEFT JOIN "direct_voter"
2663 ON "interest"."member_id" = "direct_voter"."member_id"
2664 AND "interest"."issue_id" = "direct_voter"."issue_id"
2665 LEFT JOIN "delegating_voter"
2666 ON "interest"."member_id" = "delegating_voter"."member_id"
2667 AND "interest"."issue_id" = "delegating_voter"."issue_id"
2668 WHERE "interest"."issue_id" = "issue_id_p"
2669 AND "interest"."autoreject" = TRUE
2670 AND "direct_voter"."member_id" ISNULL
2671 AND "delegating_voter"."member_id" ISNULL
2672 UNION SELECT "membership"."member_id"
2673 FROM "membership"
2674 LEFT JOIN "interest"
2675 ON "membership"."member_id" = "interest"."member_id"
2676 AND "interest"."issue_id" = "issue_id_p"
2677 LEFT JOIN "direct_voter"
2678 ON "membership"."member_id" = "direct_voter"."member_id"
2679 AND "direct_voter"."issue_id" = "issue_id_p"
2680 LEFT JOIN "delegating_voter"
2681 ON "membership"."member_id" = "delegating_voter"."member_id"
2682 AND "delegating_voter"."issue_id" = "issue_id_p"
2683 WHERE "membership"."area_id" = "issue_row"."area_id"
2684 AND "membership"."autoreject" = TRUE
2685 AND "interest"."autoreject" ISNULL
2686 AND "direct_voter"."member_id" ISNULL
2687 AND "delegating_voter"."member_id" ISNULL
2688 LOOP
2689 INSERT INTO "direct_voter"
2690 ("member_id", "issue_id", "weight", "autoreject") VALUES
2691 ("member_id_v", "issue_id_p", 1, TRUE);
2692 INSERT INTO "vote" (
2693 "member_id",
2694 "issue_id",
2695 "initiative_id",
2696 "grade"
2697 ) SELECT
2698 "member_id_v" AS "member_id",
2699 "issue_id_p" AS "issue_id",
2700 "id" AS "initiative_id",
2701 -1 AS "grade"
2702 FROM "initiative" WHERE "issue_id" = "issue_id_p";
2703 END LOOP;
2704 PERFORM "add_vote_delegations"("issue_id_p");
2705 UPDATE "issue" SET
2706 "voter_count" = (
2707 SELECT coalesce(sum("weight"), 0)
2708 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
2709 )
2710 WHERE "id" = "issue_id_p";
2711 UPDATE "initiative" SET
2712 "positive_votes" = "vote_counts"."positive_votes",
2713 "negative_votes" = "vote_counts"."negative_votes",
2714 "agreed" = CASE WHEN "majority_strict" THEN
2715 "vote_counts"."positive_votes" * "majority_den" >
2716 "majority_num" *
2717 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
2718 ELSE
2719 "vote_counts"."positive_votes" * "majority_den" >=
2720 "majority_num" *
2721 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
2722 END
2723 FROM
2724 ( SELECT
2725 "initiative"."id" AS "initiative_id",
2726 coalesce(
2727 sum(
2728 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
2729 ),
2730 0
2731 ) AS "positive_votes",
2732 coalesce(
2733 sum(
2734 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
2735 ),
2736 0
2737 ) AS "negative_votes"
2738 FROM "initiative"
2739 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
2740 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
2741 LEFT JOIN "direct_voter"
2742 ON "direct_voter"."issue_id" = "initiative"."issue_id"
2743 LEFT JOIN "vote"
2744 ON "vote"."initiative_id" = "initiative"."id"
2745 AND "vote"."member_id" = "direct_voter"."member_id"
2746 WHERE "initiative"."issue_id" = "issue_id_p"
2747 AND "initiative"."admitted" -- NOTE: NULL case is handled too
2748 GROUP BY "initiative"."id"
2749 ) AS "vote_counts",
2750 "issue",
2751 "policy"
2752 WHERE "vote_counts"."initiative_id" = "initiative"."id"
2753 AND "issue"."id" = "initiative"."issue_id"
2754 AND "policy"."id" = "issue"."policy_id";
2755 UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p";
2756 END;
2757 $$;
2759 COMMENT ON FUNCTION "close_voting"
2760 ( "issue"."id"%TYPE )
2761 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.';
2764 CREATE FUNCTION "defeat_strength"
2765 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
2766 RETURNS INT8
2767 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2768 BEGIN
2769 IF "positive_votes_p" > "negative_votes_p" THEN
2770 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
2771 ELSIF "positive_votes_p" = "negative_votes_p" THEN
2772 RETURN 0;
2773 ELSE
2774 RETURN -1;
2775 END IF;
2776 END;
2777 $$;
2779 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';
2782 CREATE FUNCTION "array_init_string"("dim_p" INTEGER)
2783 RETURNS TEXT
2784 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2785 DECLARE
2786 "i" INTEGER;
2787 "ary_text_v" TEXT;
2788 BEGIN
2789 IF "dim_p" >= 1 THEN
2790 "ary_text_v" := '{NULL';
2791 "i" := "dim_p";
2792 LOOP
2793 "i" := "i" - 1;
2794 EXIT WHEN "i" = 0;
2795 "ary_text_v" := "ary_text_v" || ',NULL';
2796 END LOOP;
2797 "ary_text_v" := "ary_text_v" || '}';
2798 RETURN "ary_text_v";
2799 ELSE
2800 RAISE EXCEPTION 'Dimension needs to be at least 1.';
2801 END IF;
2802 END;
2803 $$;
2805 COMMENT ON FUNCTION "array_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
2808 CREATE FUNCTION "square_matrix_init_string"("dim_p" INTEGER)
2809 RETURNS TEXT
2810 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2811 DECLARE
2812 "i" INTEGER;
2813 "row_text_v" TEXT;
2814 "ary_text_v" TEXT;
2815 BEGIN
2816 IF "dim_p" >= 1 THEN
2817 "row_text_v" := '{NULL';
2818 "i" := "dim_p";
2819 LOOP
2820 "i" := "i" - 1;
2821 EXIT WHEN "i" = 0;
2822 "row_text_v" := "row_text_v" || ',NULL';
2823 END LOOP;
2824 "row_text_v" := "row_text_v" || '}';
2825 "ary_text_v" := '{' || "row_text_v";
2826 "i" := "dim_p";
2827 LOOP
2828 "i" := "i" - 1;
2829 EXIT WHEN "i" = 0;
2830 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
2831 END LOOP;
2832 "ary_text_v" := "ary_text_v" || '}';
2833 RETURN "ary_text_v";
2834 ELSE
2835 RAISE EXCEPTION 'Dimension needs to be at least 1.';
2836 END IF;
2837 END;
2838 $$;
2840 COMMENT ON FUNCTION "square_matrix_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
2843 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
2844 RETURNS VOID
2845 LANGUAGE 'plpgsql' VOLATILE AS $$
2846 DECLARE
2847 "dimension_v" INTEGER;
2848 "vote_matrix" INT4[][]; -- absolute votes
2849 "matrix" INT8[][]; -- defeat strength / best paths
2850 "i" INTEGER;
2851 "j" INTEGER;
2852 "k" INTEGER;
2853 "battle_row" "battle"%ROWTYPE;
2854 "rank_ary" INT4[];
2855 "rank_v" INT4;
2856 "done_v" INTEGER;
2857 "winners_ary" INTEGER[];
2858 "initiative_id_v" "initiative"."id"%TYPE;
2859 BEGIN
2860 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2861 SELECT count(1) INTO "dimension_v" FROM "initiative"
2862 WHERE "issue_id" = "issue_id_p" AND "agreed";
2863 IF "dimension_v" = 1 THEN
2864 UPDATE "initiative" SET "rank" = 1
2865 WHERE "issue_id" = "issue_id_p" AND "agreed";
2866 ELSIF "dimension_v" > 1 THEN
2867 -- Create "vote_matrix" with absolute number of votes in pairwise
2868 -- comparison:
2869 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2870 "i" := 1;
2871 "j" := 2;
2872 FOR "battle_row" IN
2873 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
2874 ORDER BY "winning_initiative_id", "losing_initiative_id"
2875 LOOP
2876 "vote_matrix"["i"]["j"] := "battle_row"."count";
2877 IF "j" = "dimension_v" THEN
2878 "i" := "i" + 1;
2879 "j" := 1;
2880 ELSE
2881 "j" := "j" + 1;
2882 IF "j" = "i" THEN
2883 "j" := "j" + 1;
2884 END IF;
2885 END IF;
2886 END LOOP;
2887 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
2888 RAISE EXCEPTION 'Wrong battle count (should not happen)';
2889 END IF;
2890 -- Store defeat strengths in "matrix" using "defeat_strength"
2891 -- function:
2892 "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2893 "i" := 1;
2894 LOOP
2895 "j" := 1;
2896 LOOP
2897 IF "i" != "j" THEN
2898 "matrix"["i"]["j"] := "defeat_strength"(
2899 "vote_matrix"["i"]["j"],
2900 "vote_matrix"["j"]["i"]
2901 );
2902 END IF;
2903 EXIT WHEN "j" = "dimension_v";
2904 "j" := "j" + 1;
2905 END LOOP;
2906 EXIT WHEN "i" = "dimension_v";
2907 "i" := "i" + 1;
2908 END LOOP;
2909 -- Find best paths:
2910 "i" := 1;
2911 LOOP
2912 "j" := 1;
2913 LOOP
2914 IF "i" != "j" THEN
2915 "k" := 1;
2916 LOOP
2917 IF "i" != "k" AND "j" != "k" THEN
2918 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
2919 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
2920 "matrix"["j"]["k"] := "matrix"["j"]["i"];
2921 END IF;
2922 ELSE
2923 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
2924 "matrix"["j"]["k"] := "matrix"["i"]["k"];
2925 END IF;
2926 END IF;
2927 END IF;
2928 EXIT WHEN "k" = "dimension_v";
2929 "k" := "k" + 1;
2930 END LOOP;
2931 END IF;
2932 EXIT WHEN "j" = "dimension_v";
2933 "j" := "j" + 1;
2934 END LOOP;
2935 EXIT WHEN "i" = "dimension_v";
2936 "i" := "i" + 1;
2937 END LOOP;
2938 -- Determine order of winners:
2939 "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2940 "rank_v" := 1;
2941 "done_v" := 0;
2942 LOOP
2943 "winners_ary" := '{}';
2944 "i" := 1;
2945 LOOP
2946 IF "rank_ary"["i"] ISNULL THEN
2947 "j" := 1;
2948 LOOP
2949 IF
2950 "i" != "j" AND
2951 "rank_ary"["j"] ISNULL AND
2952 "matrix"["j"]["i"] > "matrix"["i"]["j"]
2953 THEN
2954 -- someone else is better
2955 EXIT;
2956 END IF;
2957 IF "j" = "dimension_v" THEN
2958 -- noone is better
2959 "winners_ary" := "winners_ary" || "i";
2960 EXIT;
2961 END IF;
2962 "j" := "j" + 1;
2963 END LOOP;
2964 END IF;
2965 EXIT WHEN "i" = "dimension_v";
2966 "i" := "i" + 1;
2967 END LOOP;
2968 "i" := 1;
2969 LOOP
2970 "rank_ary"["winners_ary"["i"]] := "rank_v";
2971 "done_v" := "done_v" + 1;
2972 EXIT WHEN "i" = array_upper("winners_ary", 1);
2973 "i" := "i" + 1;
2974 END LOOP;
2975 EXIT WHEN "done_v" = "dimension_v";
2976 "rank_v" := "rank_v" + 1;
2977 END LOOP;
2978 -- write preliminary ranks:
2979 "i" := 1;
2980 FOR "initiative_id_v" IN
2981 SELECT "id" FROM "initiative"
2982 WHERE "issue_id" = "issue_id_p" AND "agreed"
2983 ORDER BY "id"
2984 LOOP
2985 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
2986 WHERE "id" = "initiative_id_v";
2987 "i" := "i" + 1;
2988 END LOOP;
2989 IF "i" != "dimension_v" + 1 THEN
2990 RAISE EXCEPTION 'Wrong winner count (should not happen)';
2991 END IF;
2992 -- straighten ranks (start counting with 1, no equal ranks):
2993 "rank_v" := 1;
2994 FOR "initiative_id_v" IN
2995 SELECT "id" FROM "initiative"
2996 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
2997 ORDER BY
2998 "rank",
2999 "vote_ratio"("positive_votes", "negative_votes") DESC,
3000 "id"
3001 LOOP
3002 UPDATE "initiative" SET "rank" = "rank_v"
3003 WHERE "id" = "initiative_id_v";
3004 "rank_v" := "rank_v" + 1;
3005 END LOOP;
3006 END IF;
3007 -- mark issue as finished
3008 UPDATE "issue" SET "ranks_available" = TRUE
3009 WHERE "id" = "issue_id_p";
3010 RETURN;
3011 END;
3012 $$;
3014 COMMENT ON FUNCTION "calculate_ranks"
3015 ( "issue"."id"%TYPE )
3016 IS 'Determine ranking (Votes have to be counted first)';
3020 -----------------------------
3021 -- Automatic state changes --
3022 -----------------------------
3025 CREATE FUNCTION "check_issue"
3026 ( "issue_id_p" "issue"."id"%TYPE )
3027 RETURNS VOID
3028 LANGUAGE 'plpgsql' VOLATILE AS $$
3029 DECLARE
3030 "issue_row" "issue"%ROWTYPE;
3031 "policy_row" "policy"%ROWTYPE;
3032 "voting_requested_v" BOOLEAN;
3033 BEGIN
3034 PERFORM "global_lock"();
3035 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3036 -- only process open issues:
3037 IF "issue_row"."closed" ISNULL THEN
3038 SELECT * INTO "policy_row" FROM "policy"
3039 WHERE "id" = "issue_row"."policy_id";
3040 -- create a snapshot, unless issue is already fully frozen:
3041 IF "issue_row"."fully_frozen" ISNULL THEN
3042 PERFORM "create_snapshot"("issue_id_p");
3043 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3044 END IF;
3045 -- eventually close or accept issues, which have not been accepted:
3046 IF "issue_row"."accepted" ISNULL THEN
3047 IF EXISTS (
3048 SELECT NULL FROM "initiative"
3049 WHERE "issue_id" = "issue_id_p"
3050 AND "supporter_count" > 0
3051 AND "supporter_count" * "policy_row"."issue_quorum_den"
3052 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
3053 ) THEN
3054 -- accept issues, if supporter count is high enough
3055 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3056 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later
3057 UPDATE "issue" SET "accepted" = "issue_row"."accepted"
3058 WHERE "id" = "issue_row"."id";
3059 ELSIF
3060 now() >= "issue_row"."created" + "issue_row"."admission_time"
3061 THEN
3062 -- close issues, if admission time has expired
3063 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3064 UPDATE "issue" SET "closed" = now()
3065 WHERE "id" = "issue_row"."id";
3066 END IF;
3067 END IF;
3068 -- eventually half freeze issues:
3069 IF
3070 -- NOTE: issue can't be closed at this point, if it has been accepted
3071 "issue_row"."accepted" NOTNULL AND
3072 "issue_row"."half_frozen" ISNULL
3073 THEN
3074 SELECT
3075 CASE
3076 WHEN "vote_now" * 2 > "issue_row"."population" THEN
3077 TRUE
3078 WHEN "vote_later" * 2 > "issue_row"."population" THEN
3079 FALSE
3080 ELSE NULL
3081 END
3082 INTO "voting_requested_v"
3083 FROM "issue" WHERE "id" = "issue_id_p";
3084 IF
3085 "voting_requested_v" OR (
3086 "voting_requested_v" ISNULL AND
3087 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
3088 )
3089 THEN
3090 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
3091 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later
3092 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
3093 WHERE "id" = "issue_row"."id";
3094 END IF;
3095 END IF;
3096 -- close issues after some time, if all initiatives have been revoked:
3097 IF
3098 "issue_row"."closed" ISNULL AND
3099 NOT EXISTS (
3100 -- all initiatives are revoked
3101 SELECT NULL FROM "initiative"
3102 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3103 ) AND (
3104 NOT EXISTS (
3105 -- and no initiatives have been revoked lately
3106 SELECT NULL FROM "initiative"
3107 WHERE "issue_id" = "issue_id_p"
3108 AND now() < "revoked" + "issue_row"."verification_time"
3109 ) OR (
3110 -- or verification time has elapsed
3111 "issue_row"."half_frozen" NOTNULL AND
3112 "issue_row"."fully_frozen" ISNULL AND
3113 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3114 )
3115 )
3116 THEN
3117 "issue_row"."closed" = now(); -- NOTE: "issue_row" used later
3118 UPDATE "issue" SET "closed" = "issue_row"."closed"
3119 WHERE "id" = "issue_row"."id";
3120 END IF;
3121 -- fully freeze issue after verification time:
3122 IF
3123 "issue_row"."half_frozen" NOTNULL AND
3124 "issue_row"."fully_frozen" ISNULL AND
3125 "issue_row"."closed" ISNULL AND
3126 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3127 THEN
3128 PERFORM "freeze_after_snapshot"("issue_id_p");
3129 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
3130 END IF;
3131 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3132 -- close issue by calling close_voting(...) after voting time:
3133 IF
3134 "issue_row"."closed" ISNULL AND
3135 "issue_row"."fully_frozen" NOTNULL AND
3136 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
3137 THEN
3138 PERFORM "close_voting"("issue_id_p");
3139 END IF;
3140 END IF;
3141 RETURN;
3142 END;
3143 $$;
3145 COMMENT ON FUNCTION "check_issue"
3146 ( "issue"."id"%TYPE )
3147 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.';
3150 CREATE FUNCTION "check_everything"()
3151 RETURNS VOID
3152 LANGUAGE 'plpgsql' VOLATILE AS $$
3153 DECLARE
3154 "issue_id_v" "issue"."id"%TYPE;
3155 BEGIN
3156 DELETE FROM "expired_session";
3157 PERFORM "calculate_member_counts"();
3158 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
3159 PERFORM "check_issue"("issue_id_v");
3160 END LOOP;
3161 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
3162 PERFORM "calculate_ranks"("issue_id_v");
3163 END LOOP;
3164 RETURN;
3165 END;
3166 $$;
3168 COMMENT ON FUNCTION "check_everything"() IS 'Perform "check_issue" for every open issue, and if possible, automatically calculate ranks. Use this function only for development and debugging purposes, as long transactions with exclusive locking may result.';
3172 ------------------------------
3173 -- Deletion of private data --
3174 ------------------------------
3177 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
3178 RETURNS VOID
3179 LANGUAGE 'plpgsql' VOLATILE AS $$
3180 BEGIN
3181 UPDATE "member" SET
3182 "last_login" = NULL,
3183 "login" = NULL,
3184 "password" = NULL,
3185 "active" = FALSE,
3186 "notify_email" = NULL,
3187 "notify_email_unconfirmed" = NULL,
3188 "notify_email_secret" = NULL,
3189 "notify_email_secret_expiry" = NULL,
3190 "notify_email_lock_expiry" = NULL,
3191 "password_reset_secret" = NULL,
3192 "password_reset_secret_expiry" = NULL,
3193 "organizational_unit" = NULL,
3194 "internal_posts" = NULL,
3195 "realname" = NULL,
3196 "birthday" = NULL,
3197 "address" = NULL,
3198 "email" = NULL,
3199 "xmpp_address" = NULL,
3200 "website" = NULL,
3201 "phone" = NULL,
3202 "mobile_phone" = NULL,
3203 "profession" = NULL,
3204 "external_memberships" = NULL,
3205 "external_posts" = NULL,
3206 "statement" = NULL
3207 WHERE "id" = "member_id_p";
3208 -- "text_search_data" is updated by triggers
3209 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
3210 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
3211 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
3212 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
3213 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
3214 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
3215 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
3216 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
3217 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
3218 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
3219 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
3220 DELETE FROM "direct_voter" USING "issue"
3221 WHERE "direct_voter"."issue_id" = "issue"."id"
3222 AND "issue"."closed" ISNULL
3223 AND "member_id" = "member_id_p";
3224 RETURN;
3225 END;
3226 $$;
3228 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)';
3231 CREATE FUNCTION "delete_private_data"()
3232 RETURNS VOID
3233 LANGUAGE 'plpgsql' VOLATILE AS $$
3234 BEGIN
3235 UPDATE "member" SET
3236 "last_login" = NULL,
3237 "login" = NULL,
3238 "password" = NULL,
3239 "notify_email" = NULL,
3240 "notify_email_unconfirmed" = NULL,
3241 "notify_email_secret" = NULL,
3242 "notify_email_secret_expiry" = NULL,
3243 "notify_email_lock_expiry" = NULL,
3244 "password_reset_secret" = NULL,
3245 "password_reset_secret_expiry" = NULL,
3246 "organizational_unit" = NULL,
3247 "internal_posts" = NULL,
3248 "realname" = NULL,
3249 "birthday" = NULL,
3250 "address" = NULL,
3251 "email" = NULL,
3252 "xmpp_address" = NULL,
3253 "website" = NULL,
3254 "phone" = NULL,
3255 "mobile_phone" = NULL,
3256 "profession" = NULL,
3257 "external_memberships" = NULL,
3258 "external_posts" = NULL,
3259 "statement" = NULL;
3260 -- "text_search_data" is updated by triggers
3261 DELETE FROM "invite_code";
3262 DELETE FROM "setting";
3263 DELETE FROM "setting_map";
3264 DELETE FROM "member_relation_setting";
3265 DELETE FROM "member_image";
3266 DELETE FROM "contact";
3267 DELETE FROM "session";
3268 DELETE FROM "area_setting";
3269 DELETE FROM "issue_setting";
3270 DELETE FROM "initiative_setting";
3271 DELETE FROM "suggestion_setting";
3272 DELETE FROM "direct_voter" USING "issue"
3273 WHERE "direct_voter"."issue_id" = "issue"."id"
3274 AND "issue"."closed" ISNULL;
3275 RETURN;
3276 END;
3277 $$;
3279 COMMENT ON FUNCTION "delete_private_data"() IS 'DO NOT USE on productive database, but only on a copy! This function deletes all data which should not be publicly available, and can be used to create a database dump for publication.';
3283 COMMIT;