liquid_feedback_core
view core.sql @ 62:bc01495a4afa
Added tag v1.2.2 for changeset 598af132a6f9
| author | jbe |
|---|---|
| date | Tue Jul 20 00:59:06 2010 +0200 (2010-07-20) |
| parents | 598af132a6f9 |
| children | 1af482e378a1 |
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.2', 1, 2, 2))
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 "cleaned" TIMESTAMPTZ,
345 "admission_time" INTERVAL NOT NULL,
346 "discussion_time" INTERVAL NOT NULL,
347 "verification_time" INTERVAL NOT NULL,
348 "voting_time" INTERVAL NOT NULL,
349 "snapshot" TIMESTAMPTZ,
350 "latest_snapshot_event" "snapshot_event",
351 "population" INT4,
352 "vote_now" INT4,
353 "vote_later" INT4,
354 "voter_count" INT4,
355 CONSTRAINT "valid_state" CHECK (
356 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
357 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
358 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
359 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
360 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
361 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
362 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
363 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
364 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ),
365 CONSTRAINT "state_change_order" CHECK (
366 "created" <= "accepted" AND
367 "accepted" <= "half_frozen" AND
368 "half_frozen" <= "fully_frozen" AND
369 "fully_frozen" <= "closed" ),
370 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
371 "cleaned" ISNULL OR "closed" NOTNULL ),
372 CONSTRAINT "last_snapshot_on_full_freeze"
373 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
374 CONSTRAINT "freeze_requires_snapshot"
375 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
376 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
377 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
378 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
379 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
380 CREATE INDEX "issue_created_idx" ON "issue" ("created");
381 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
382 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
383 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
384 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
385 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
386 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
388 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
390 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
391 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.';
392 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.';
393 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.';
394 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
395 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
396 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
397 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
398 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
399 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
400 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';
401 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';
402 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
403 COMMENT ON COLUMN "issue"."vote_now" IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"';
404 COMMENT ON COLUMN "issue"."vote_later" IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"';
405 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';
408 CREATE TABLE "issue_setting" (
409 PRIMARY KEY ("member_id", "key", "issue_id"),
410 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
411 "key" TEXT NOT NULL,
412 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
413 "value" TEXT NOT NULL );
415 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
418 CREATE TABLE "initiative" (
419 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
420 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
421 "id" SERIAL4 PRIMARY KEY,
422 "name" TEXT NOT NULL,
423 "discussion_url" TEXT,
424 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
425 "revoked" TIMESTAMPTZ,
426 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
427 "admitted" BOOLEAN,
428 "supporter_count" INT4,
429 "informed_supporter_count" INT4,
430 "satisfied_supporter_count" INT4,
431 "satisfied_informed_supporter_count" INT4,
432 "positive_votes" INT4,
433 "negative_votes" INT4,
434 "agreed" BOOLEAN,
435 "rank" INT4,
436 "text_search_data" TSVECTOR,
437 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
438 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
439 CONSTRAINT "revoked_initiatives_cant_be_admitted"
440 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
441 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"
442 CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "agreed" ISNULL)),
443 CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"
444 CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL),
445 CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"
446 CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) );
447 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
448 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
449 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
450 CREATE TRIGGER "update_text_search_data"
451 BEFORE INSERT OR UPDATE ON "initiative"
452 FOR EACH ROW EXECUTE PROCEDURE
453 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
454 "name", "discussion_url");
456 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.';
458 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
459 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
460 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
461 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
462 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
463 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
464 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
465 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
466 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
467 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"';
468 COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';
471 CREATE TABLE "battle" (
472 PRIMARY KEY ("issue_id", "winning_initiative_id", "losing_initiative_id"),
473 "issue_id" INT4,
474 "winning_initiative_id" INT4,
475 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
476 "losing_initiative_id" INT4,
477 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
478 "count" INT4 NOT NULL);
480 COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue';
483 CREATE TABLE "initiative_setting" (
484 PRIMARY KEY ("member_id", "key", "initiative_id"),
485 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
486 "key" TEXT NOT NULL,
487 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
488 "value" TEXT NOT NULL );
490 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
493 CREATE TABLE "draft" (
494 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
495 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
496 "id" SERIAL8 PRIMARY KEY,
497 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
498 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
499 "formatting_engine" TEXT,
500 "content" TEXT NOT NULL,
501 "text_search_data" TSVECTOR );
502 CREATE INDEX "draft_created_idx" ON "draft" ("created");
503 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
504 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
505 CREATE TRIGGER "update_text_search_data"
506 BEFORE INSERT OR UPDATE ON "draft"
507 FOR EACH ROW EXECUTE PROCEDURE
508 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
510 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.';
512 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
513 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
516 CREATE TABLE "suggestion" (
517 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
518 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
519 "id" SERIAL8 PRIMARY KEY,
520 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
521 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
522 "name" TEXT NOT NULL,
523 "description" TEXT NOT NULL DEFAULT '',
524 "text_search_data" TSVECTOR,
525 "minus2_unfulfilled_count" INT4,
526 "minus2_fulfilled_count" INT4,
527 "minus1_unfulfilled_count" INT4,
528 "minus1_fulfilled_count" INT4,
529 "plus1_unfulfilled_count" INT4,
530 "plus1_fulfilled_count" INT4,
531 "plus2_unfulfilled_count" INT4,
532 "plus2_fulfilled_count" INT4 );
533 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
534 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
535 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
536 CREATE TRIGGER "update_text_search_data"
537 BEFORE INSERT OR UPDATE ON "suggestion"
538 FOR EACH ROW EXECUTE PROCEDURE
539 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
540 "name", "description");
542 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';
544 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
545 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
546 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
547 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
548 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
549 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
550 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
551 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
554 CREATE TABLE "suggestion_setting" (
555 PRIMARY KEY ("member_id", "key", "suggestion_id"),
556 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
557 "key" TEXT NOT NULL,
558 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
559 "value" TEXT NOT NULL );
561 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
564 CREATE TABLE "membership" (
565 PRIMARY KEY ("area_id", "member_id"),
566 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
567 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
568 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
569 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
571 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
573 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';
576 CREATE TABLE "interest" (
577 PRIMARY KEY ("issue_id", "member_id"),
578 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
579 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
580 "autoreject" BOOLEAN NOT NULL,
581 "voting_requested" BOOLEAN );
582 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
584 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.';
586 COMMENT ON COLUMN "interest"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure';
587 COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply';
590 CREATE TABLE "initiator" (
591 PRIMARY KEY ("initiative_id", "member_id"),
592 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
593 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
594 "accepted" BOOLEAN );
595 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
597 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.';
599 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.';
602 CREATE TABLE "supporter" (
603 "issue_id" INT4 NOT NULL,
604 PRIMARY KEY ("initiative_id", "member_id"),
605 "initiative_id" INT4,
606 "member_id" INT4,
607 "draft_id" INT8 NOT NULL,
608 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
609 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
610 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
612 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.';
614 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
617 CREATE TABLE "opinion" (
618 "initiative_id" INT4 NOT NULL,
619 PRIMARY KEY ("suggestion_id", "member_id"),
620 "suggestion_id" INT8,
621 "member_id" INT4,
622 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
623 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
624 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
625 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
626 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
628 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.';
630 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
633 CREATE TYPE "delegation_scope" AS ENUM ('global', 'area', 'issue');
635 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''global'', ''area'', or ''issue'' (order is relevant)';
638 CREATE TABLE "delegation" (
639 "id" SERIAL8 PRIMARY KEY,
640 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
641 "trustee_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
642 "scope" "delegation_scope" NOT NULL,
643 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
644 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
645 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
646 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
647 ("scope" = 'global' AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
648 ("scope" = 'area' AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
649 ("scope" = 'issue' AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
650 UNIQUE ("area_id", "truster_id", "trustee_id"),
651 UNIQUE ("issue_id", "truster_id", "trustee_id") );
652 CREATE UNIQUE INDEX "delegation_global_truster_id_trustee_id_unique_idx"
653 ON "delegation" ("truster_id", "trustee_id") WHERE "scope" = 'global';
654 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
655 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
657 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
659 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
660 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
663 CREATE TABLE "direct_population_snapshot" (
664 PRIMARY KEY ("issue_id", "event", "member_id"),
665 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
666 "event" "snapshot_event",
667 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
668 "weight" INT4 );
669 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
671 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
673 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
674 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
677 CREATE TABLE "delegating_population_snapshot" (
678 PRIMARY KEY ("issue_id", "event", "member_id"),
679 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
680 "event" "snapshot_event",
681 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
682 "weight" INT4,
683 "scope" "delegation_scope" NOT NULL,
684 "delegate_member_ids" INT4[] NOT NULL );
685 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
687 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
689 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
690 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
691 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
692 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"';
695 CREATE TABLE "direct_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 "voting_requested" BOOLEAN );
702 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
704 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
706 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
707 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
708 COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';
711 CREATE TABLE "delegating_interest_snapshot" (
712 PRIMARY KEY ("issue_id", "event", "member_id"),
713 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
714 "event" "snapshot_event",
715 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
716 "weight" INT4,
717 "scope" "delegation_scope" NOT NULL,
718 "delegate_member_ids" INT4[] NOT NULL );
719 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
721 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
723 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
724 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
725 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
726 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"';
729 CREATE TABLE "direct_supporter_snapshot" (
730 "issue_id" INT4 NOT NULL,
731 PRIMARY KEY ("initiative_id", "event", "member_id"),
732 "initiative_id" INT4,
733 "event" "snapshot_event",
734 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
735 "informed" BOOLEAN NOT NULL,
736 "satisfied" BOOLEAN NOT NULL,
737 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
738 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
739 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
741 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
743 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
744 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
745 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
748 CREATE TABLE "direct_voter" (
749 PRIMARY KEY ("issue_id", "member_id"),
750 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
751 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
752 "weight" INT4,
753 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
754 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
756 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.';
758 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
759 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
762 CREATE TABLE "delegating_voter" (
763 PRIMARY KEY ("issue_id", "member_id"),
764 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
765 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
766 "weight" INT4,
767 "scope" "delegation_scope" NOT NULL,
768 "delegate_member_ids" INT4[] NOT NULL );
769 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
771 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
773 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
774 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
775 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"';
778 CREATE TABLE "vote" (
779 "issue_id" INT4 NOT NULL,
780 PRIMARY KEY ("initiative_id", "member_id"),
781 "initiative_id" INT4,
782 "member_id" INT4,
783 "grade" INT4,
784 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
785 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
786 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
788 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.';
790 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.';
793 CREATE TABLE "contingent" (
794 "time_frame" INTERVAL PRIMARY KEY,
795 "text_entry_limit" INT4,
796 "initiative_limit" INT4 );
798 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.';
800 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';
801 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
805 --------------------------------
806 -- Writing of history entries --
807 --------------------------------
809 CREATE FUNCTION "write_member_history_trigger"()
810 RETURNS TRIGGER
811 LANGUAGE 'plpgsql' VOLATILE AS $$
812 BEGIN
813 IF
814 NEW."active" != OLD."active" OR
815 NEW."name" != OLD."name"
816 THEN
817 INSERT INTO "member_history"
818 ("member_id", "active", "name")
819 VALUES (NEW."id", OLD."active", OLD."name");
820 END IF;
821 RETURN NULL;
822 END;
823 $$;
825 CREATE TRIGGER "write_member_history"
826 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
827 "write_member_history_trigger"();
829 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
830 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
834 ----------------------------
835 -- Additional constraints --
836 ----------------------------
839 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
840 RETURNS TRIGGER
841 LANGUAGE 'plpgsql' VOLATILE AS $$
842 BEGIN
843 IF NOT EXISTS (
844 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
845 ) THEN
846 --RAISE 'Cannot create issue without an initial initiative.' USING
847 -- ERRCODE = 'integrity_constraint_violation',
848 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
849 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
850 END IF;
851 RETURN NULL;
852 END;
853 $$;
855 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
856 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
857 FOR EACH ROW EXECUTE PROCEDURE
858 "issue_requires_first_initiative_trigger"();
860 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
861 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
864 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
865 RETURNS TRIGGER
866 LANGUAGE 'plpgsql' VOLATILE AS $$
867 DECLARE
868 "reference_lost" BOOLEAN;
869 BEGIN
870 IF TG_OP = 'DELETE' THEN
871 "reference_lost" := TRUE;
872 ELSE
873 "reference_lost" := NEW."issue_id" != OLD."issue_id";
874 END IF;
875 IF
876 "reference_lost" AND NOT EXISTS (
877 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
878 )
879 THEN
880 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
881 END IF;
882 RETURN NULL;
883 END;
884 $$;
886 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
887 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
888 FOR EACH ROW EXECUTE PROCEDURE
889 "last_initiative_deletes_issue_trigger"();
891 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
892 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
895 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
896 RETURNS TRIGGER
897 LANGUAGE 'plpgsql' VOLATILE AS $$
898 BEGIN
899 IF NOT EXISTS (
900 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
901 ) THEN
902 --RAISE 'Cannot create initiative without an initial draft.' USING
903 -- ERRCODE = 'integrity_constraint_violation',
904 -- HINT = 'Create issue, initiative and draft within the same transaction.';
905 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
906 END IF;
907 RETURN NULL;
908 END;
909 $$;
911 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
912 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
913 FOR EACH ROW EXECUTE PROCEDURE
914 "initiative_requires_first_draft_trigger"();
916 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
917 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
920 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
921 RETURNS TRIGGER
922 LANGUAGE 'plpgsql' VOLATILE AS $$
923 DECLARE
924 "reference_lost" BOOLEAN;
925 BEGIN
926 IF TG_OP = 'DELETE' THEN
927 "reference_lost" := TRUE;
928 ELSE
929 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
930 END IF;
931 IF
932 "reference_lost" AND NOT EXISTS (
933 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
934 )
935 THEN
936 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
937 END IF;
938 RETURN NULL;
939 END;
940 $$;
942 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
943 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
944 FOR EACH ROW EXECUTE PROCEDURE
945 "last_draft_deletes_initiative_trigger"();
947 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
948 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
951 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
952 RETURNS TRIGGER
953 LANGUAGE 'plpgsql' VOLATILE AS $$
954 BEGIN
955 IF NOT EXISTS (
956 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
957 ) THEN
958 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
959 END IF;
960 RETURN NULL;
961 END;
962 $$;
964 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
965 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
966 FOR EACH ROW EXECUTE PROCEDURE
967 "suggestion_requires_first_opinion_trigger"();
969 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
970 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
973 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
974 RETURNS TRIGGER
975 LANGUAGE 'plpgsql' VOLATILE AS $$
976 DECLARE
977 "reference_lost" BOOLEAN;
978 BEGIN
979 IF TG_OP = 'DELETE' THEN
980 "reference_lost" := TRUE;
981 ELSE
982 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
983 END IF;
984 IF
985 "reference_lost" AND NOT EXISTS (
986 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
987 )
988 THEN
989 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
990 END IF;
991 RETURN NULL;
992 END;
993 $$;
995 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
996 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
997 FOR EACH ROW EXECUTE PROCEDURE
998 "last_opinion_deletes_suggestion_trigger"();
1000 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1001 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1005 ---------------------------------------------------------------
1006 -- Ensure that votes are not modified when issues are frozen --
1007 ---------------------------------------------------------------
1009 -- NOTE: Frontends should ensure this anyway, but in case of programming
1010 -- errors the following triggers ensure data integrity.
1013 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1014 RETURNS TRIGGER
1015 LANGUAGE 'plpgsql' VOLATILE AS $$
1016 DECLARE
1017 "issue_id_v" "issue"."id"%TYPE;
1018 "issue_row" "issue"%ROWTYPE;
1019 BEGIN
1020 IF TG_OP = 'DELETE' THEN
1021 "issue_id_v" := OLD."issue_id";
1022 ELSE
1023 "issue_id_v" := NEW."issue_id";
1024 END IF;
1025 SELECT INTO "issue_row" * FROM "issue"
1026 WHERE "id" = "issue_id_v" FOR SHARE;
1027 IF "issue_row"."closed" NOTNULL THEN
1028 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
1029 END IF;
1030 RETURN NULL;
1031 END;
1032 $$;
1034 CREATE TRIGGER "forbid_changes_on_closed_issue"
1035 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1036 FOR EACH ROW EXECUTE PROCEDURE
1037 "forbid_changes_on_closed_issue_trigger"();
1039 CREATE TRIGGER "forbid_changes_on_closed_issue"
1040 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1041 FOR EACH ROW EXECUTE PROCEDURE
1042 "forbid_changes_on_closed_issue_trigger"();
1044 CREATE TRIGGER "forbid_changes_on_closed_issue"
1045 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1046 FOR EACH ROW EXECUTE PROCEDURE
1047 "forbid_changes_on_closed_issue_trigger"();
1049 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"';
1050 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';
1051 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';
1052 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';
1056 --------------------------------------------------------------------
1057 -- Auto-retrieval of fields only needed for referential integrity --
1058 --------------------------------------------------------------------
1061 CREATE FUNCTION "autofill_issue_id_trigger"()
1062 RETURNS TRIGGER
1063 LANGUAGE 'plpgsql' VOLATILE AS $$
1064 BEGIN
1065 IF NEW."issue_id" ISNULL THEN
1066 SELECT "issue_id" INTO NEW."issue_id"
1067 FROM "initiative" WHERE "id" = NEW."initiative_id";
1068 END IF;
1069 RETURN NEW;
1070 END;
1071 $$;
1073 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1074 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1076 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1077 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1079 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1080 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1081 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1084 CREATE FUNCTION "autofill_initiative_id_trigger"()
1085 RETURNS TRIGGER
1086 LANGUAGE 'plpgsql' VOLATILE AS $$
1087 BEGIN
1088 IF NEW."initiative_id" ISNULL THEN
1089 SELECT "initiative_id" INTO NEW."initiative_id"
1090 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1091 END IF;
1092 RETURN NEW;
1093 END;
1094 $$;
1096 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1097 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1099 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1100 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1104 -----------------------------------------------------
1105 -- Automatic calculation of certain default values --
1106 -----------------------------------------------------
1109 CREATE FUNCTION "copy_timings_trigger"()
1110 RETURNS TRIGGER
1111 LANGUAGE 'plpgsql' VOLATILE AS $$
1112 DECLARE
1113 "policy_row" "policy"%ROWTYPE;
1114 BEGIN
1115 SELECT * INTO "policy_row" FROM "policy"
1116 WHERE "id" = NEW."policy_id";
1117 IF NEW."admission_time" ISNULL THEN
1118 NEW."admission_time" := "policy_row"."admission_time";
1119 END IF;
1120 IF NEW."discussion_time" ISNULL THEN
1121 NEW."discussion_time" := "policy_row"."discussion_time";
1122 END IF;
1123 IF NEW."verification_time" ISNULL THEN
1124 NEW."verification_time" := "policy_row"."verification_time";
1125 END IF;
1126 IF NEW."voting_time" ISNULL THEN
1127 NEW."voting_time" := "policy_row"."voting_time";
1128 END IF;
1129 RETURN NEW;
1130 END;
1131 $$;
1133 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1134 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1136 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1137 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1140 CREATE FUNCTION "copy_autoreject_trigger"()
1141 RETURNS TRIGGER
1142 LANGUAGE 'plpgsql' VOLATILE AS $$
1143 BEGIN
1144 IF NEW."autoreject" ISNULL THEN
1145 SELECT "membership"."autoreject" INTO NEW."autoreject"
1146 FROM "issue" JOIN "membership"
1147 ON "issue"."area_id" = "membership"."area_id"
1148 WHERE "issue"."id" = NEW."issue_id"
1149 AND "membership"."member_id" = NEW."member_id";
1150 END IF;
1151 IF NEW."autoreject" ISNULL THEN
1152 NEW."autoreject" := FALSE;
1153 END IF;
1154 RETURN NEW;
1155 END;
1156 $$;
1158 CREATE TRIGGER "copy_autoreject" BEFORE INSERT OR UPDATE ON "interest"
1159 FOR EACH ROW EXECUTE PROCEDURE "copy_autoreject_trigger"();
1161 COMMENT ON FUNCTION "copy_autoreject_trigger"() IS 'Implementation of trigger "copy_autoreject" on table "interest"';
1162 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';
1165 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
1166 RETURNS TRIGGER
1167 LANGUAGE 'plpgsql' VOLATILE AS $$
1168 BEGIN
1169 IF NEW."draft_id" ISNULL THEN
1170 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1171 WHERE "initiative_id" = NEW."initiative_id";
1172 END IF;
1173 RETURN NEW;
1174 END;
1175 $$;
1177 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1178 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
1180 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
1181 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';
1185 ----------------------------------------
1186 -- Automatic creation of dependencies --
1187 ----------------------------------------
1190 CREATE FUNCTION "autocreate_interest_trigger"()
1191 RETURNS TRIGGER
1192 LANGUAGE 'plpgsql' VOLATILE AS $$
1193 BEGIN
1194 IF NOT EXISTS (
1195 SELECT NULL FROM "initiative" JOIN "interest"
1196 ON "initiative"."issue_id" = "interest"."issue_id"
1197 WHERE "initiative"."id" = NEW."initiative_id"
1198 AND "interest"."member_id" = NEW."member_id"
1199 ) THEN
1200 BEGIN
1201 INSERT INTO "interest" ("issue_id", "member_id")
1202 SELECT "issue_id", NEW."member_id"
1203 FROM "initiative" WHERE "id" = NEW."initiative_id";
1204 EXCEPTION WHEN unique_violation THEN END;
1205 END IF;
1206 RETURN NEW;
1207 END;
1208 $$;
1210 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1211 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1213 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1214 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';
1217 CREATE FUNCTION "autocreate_supporter_trigger"()
1218 RETURNS TRIGGER
1219 LANGUAGE 'plpgsql' VOLATILE AS $$
1220 BEGIN
1221 IF NOT EXISTS (
1222 SELECT NULL FROM "suggestion" JOIN "supporter"
1223 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1224 WHERE "suggestion"."id" = NEW."suggestion_id"
1225 AND "supporter"."member_id" = NEW."member_id"
1226 ) THEN
1227 BEGIN
1228 INSERT INTO "supporter" ("initiative_id", "member_id")
1229 SELECT "initiative_id", NEW."member_id"
1230 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1231 EXCEPTION WHEN unique_violation THEN END;
1232 END IF;
1233 RETURN NEW;
1234 END;
1235 $$;
1237 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1238 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1240 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1241 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.';
1245 ------------------------------------------
1246 -- Views and helper functions for views --
1247 ------------------------------------------
1250 CREATE VIEW "global_delegation" AS
1251 SELECT
1252 "delegation"."id",
1253 "delegation"."truster_id",
1254 "delegation"."trustee_id"
1255 FROM "delegation" JOIN "member"
1256 ON "delegation"."trustee_id" = "member"."id"
1257 WHERE "delegation"."scope" = 'global' AND "member"."active";
1259 COMMENT ON VIEW "global_delegation" IS 'Global delegations to active members';
1262 CREATE VIEW "area_delegation" AS
1263 SELECT "subquery".* FROM (
1264 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1265 "area"."id" AS "area_id",
1266 "delegation"."id",
1267 "delegation"."truster_id",
1268 "delegation"."trustee_id",
1269 "delegation"."scope"
1270 FROM "area" JOIN "delegation"
1271 ON "delegation"."scope" = 'global'
1272 OR "delegation"."area_id" = "area"."id"
1273 ORDER BY
1274 "area"."id",
1275 "delegation"."truster_id",
1276 "delegation"."scope" DESC
1277 ) AS "subquery"
1278 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
1279 WHERE "member"."active";
1281 COMMENT ON VIEW "area_delegation" IS 'Active delegations for areas';
1284 CREATE VIEW "issue_delegation" AS
1285 SELECT "subquery".* FROM (
1286 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1287 "issue"."id" AS "issue_id",
1288 "delegation"."id",
1289 "delegation"."truster_id",
1290 "delegation"."trustee_id",
1291 "delegation"."scope"
1292 FROM "issue" JOIN "delegation"
1293 ON "delegation"."scope" = 'global'
1294 OR "delegation"."area_id" = "issue"."area_id"
1295 OR "delegation"."issue_id" = "issue"."id"
1296 ORDER BY
1297 "issue"."id",
1298 "delegation"."truster_id",
1299 "delegation"."scope" DESC
1300 ) AS "subquery"
1301 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
1302 WHERE "member"."active";
1304 COMMENT ON VIEW "issue_delegation" IS 'Active delegations for issues';
1307 CREATE FUNCTION "membership_weight_with_skipping"
1308 ( "area_id_p" "area"."id"%TYPE,
1309 "member_id_p" "member"."id"%TYPE,
1310 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1311 RETURNS INT4
1312 LANGUAGE 'plpgsql' STABLE AS $$
1313 DECLARE
1314 "sum_v" INT4;
1315 "delegation_row" "area_delegation"%ROWTYPE;
1316 BEGIN
1317 "sum_v" := 1;
1318 FOR "delegation_row" IN
1319 SELECT "area_delegation".*
1320 FROM "area_delegation" LEFT JOIN "membership"
1321 ON "membership"."area_id" = "area_id_p"
1322 AND "membership"."member_id" = "area_delegation"."truster_id"
1323 WHERE "area_delegation"."area_id" = "area_id_p"
1324 AND "area_delegation"."trustee_id" = "member_id_p"
1325 AND "membership"."member_id" ISNULL
1326 LOOP
1327 IF NOT
1328 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1329 THEN
1330 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1331 "area_id_p",
1332 "delegation_row"."truster_id",
1333 "skip_member_ids_p" || "delegation_row"."truster_id"
1334 );
1335 END IF;
1336 END LOOP;
1337 RETURN "sum_v";
1338 END;
1339 $$;
1341 COMMENT ON FUNCTION "membership_weight_with_skipping"
1342 ( "area"."id"%TYPE,
1343 "member"."id"%TYPE,
1344 INT4[] )
1345 IS 'Helper function for "membership_weight" function';
1348 CREATE FUNCTION "membership_weight"
1349 ( "area_id_p" "area"."id"%TYPE,
1350 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1351 RETURNS INT4
1352 LANGUAGE 'plpgsql' STABLE AS $$
1353 BEGIN
1354 RETURN "membership_weight_with_skipping"(
1355 "area_id_p",
1356 "member_id_p",
1357 ARRAY["member_id_p"]
1358 );
1359 END;
1360 $$;
1362 COMMENT ON FUNCTION "membership_weight"
1363 ( "area"."id"%TYPE,
1364 "member"."id"%TYPE )
1365 IS 'Calculates the potential voting weight of a member in a given area';
1368 CREATE VIEW "member_count_view" AS
1369 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1371 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1374 CREATE VIEW "area_member_count" AS
1375 SELECT
1376 "area"."id" AS "area_id",
1377 count("member"."id") AS "direct_member_count",
1378 coalesce(
1379 sum(
1380 CASE WHEN "member"."id" NOTNULL THEN
1381 "membership_weight"("area"."id", "member"."id")
1382 ELSE 0 END
1383 )
1384 ) AS "member_weight",
1385 coalesce(
1386 sum(
1387 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
1388 "membership_weight"("area"."id", "member"."id")
1389 ELSE 0 END
1390 )
1391 ) AS "autoreject_weight"
1392 FROM "area"
1393 LEFT JOIN "membership"
1394 ON "area"."id" = "membership"."area_id"
1395 LEFT JOIN "member"
1396 ON "membership"."member_id" = "member"."id"
1397 AND "member"."active"
1398 GROUP BY "area"."id";
1400 COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"';
1403 CREATE VIEW "opening_draft" AS
1404 SELECT "draft".* FROM (
1405 SELECT
1406 "initiative"."id" AS "initiative_id",
1407 min("draft"."id") AS "draft_id"
1408 FROM "initiative" JOIN "draft"
1409 ON "initiative"."id" = "draft"."initiative_id"
1410 GROUP BY "initiative"."id"
1411 ) AS "subquery"
1412 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1414 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1417 CREATE VIEW "current_draft" AS
1418 SELECT "draft".* FROM (
1419 SELECT
1420 "initiative"."id" AS "initiative_id",
1421 max("draft"."id") AS "draft_id"
1422 FROM "initiative" JOIN "draft"
1423 ON "initiative"."id" = "draft"."initiative_id"
1424 GROUP BY "initiative"."id"
1425 ) AS "subquery"
1426 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1428 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1431 CREATE VIEW "critical_opinion" AS
1432 SELECT * FROM "opinion"
1433 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
1434 OR ("degree" = -2 AND "fulfilled" = TRUE);
1436 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
1439 CREATE VIEW "battle_view" AS
1440 SELECT
1441 "issue"."id" AS "issue_id",
1442 "winning_initiative"."id" AS "winning_initiative_id",
1443 "losing_initiative"."id" AS "losing_initiative_id",
1444 sum(
1445 CASE WHEN
1446 coalesce("better_vote"."grade", 0) >
1447 coalesce("worse_vote"."grade", 0)
1448 THEN "direct_voter"."weight" ELSE 0 END
1449 ) AS "count"
1450 FROM "issue"
1451 LEFT JOIN "direct_voter"
1452 ON "issue"."id" = "direct_voter"."issue_id"
1453 JOIN "initiative" AS "winning_initiative"
1454 ON "issue"."id" = "winning_initiative"."issue_id"
1455 AND "winning_initiative"."agreed"
1456 JOIN "initiative" AS "losing_initiative"
1457 ON "issue"."id" = "losing_initiative"."issue_id"
1458 AND "losing_initiative"."agreed"
1459 LEFT JOIN "vote" AS "better_vote"
1460 ON "direct_voter"."member_id" = "better_vote"."member_id"
1461 AND "winning_initiative"."id" = "better_vote"."initiative_id"
1462 LEFT JOIN "vote" AS "worse_vote"
1463 ON "direct_voter"."member_id" = "worse_vote"."member_id"
1464 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
1465 WHERE "issue"."closed" NOTNULL
1466 AND "issue"."cleaned" ISNULL
1467 AND "winning_initiative"."id" != "losing_initiative"."id"
1468 GROUP BY
1469 "issue"."id",
1470 "winning_initiative"."id",
1471 "losing_initiative"."id";
1473 COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative to another; Used to fill "battle" table';
1476 CREATE VIEW "expired_session" AS
1477 SELECT * FROM "session" WHERE now() > "expiry";
1479 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
1480 DELETE FROM "session" WHERE "ident" = OLD."ident";
1482 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
1483 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
1486 CREATE VIEW "open_issue" AS
1487 SELECT * FROM "issue" WHERE "closed" ISNULL;
1489 COMMENT ON VIEW "open_issue" IS 'All open issues';
1492 CREATE VIEW "issue_with_ranks_missing" AS
1493 SELECT * FROM "issue"
1494 WHERE "fully_frozen" NOTNULL
1495 AND "closed" NOTNULL
1496 AND "ranks_available" = FALSE;
1498 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
1501 CREATE VIEW "member_contingent" AS
1502 SELECT
1503 "member"."id" AS "member_id",
1504 "contingent"."time_frame",
1505 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
1506 (
1507 SELECT count(1) FROM "draft"
1508 WHERE "draft"."author_id" = "member"."id"
1509 AND "draft"."created" > now() - "contingent"."time_frame"
1510 ) + (
1511 SELECT count(1) FROM "suggestion"
1512 WHERE "suggestion"."author_id" = "member"."id"
1513 AND "suggestion"."created" > now() - "contingent"."time_frame"
1514 )
1515 ELSE NULL END AS "text_entry_count",
1516 "contingent"."text_entry_limit",
1517 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
1518 SELECT count(1) FROM "opening_draft"
1519 WHERE "opening_draft"."author_id" = "member"."id"
1520 AND "opening_draft"."created" > now() - "contingent"."time_frame"
1521 ) ELSE NULL END AS "initiative_count",
1522 "contingent"."initiative_limit"
1523 FROM "member" CROSS JOIN "contingent";
1525 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
1527 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
1528 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
1531 CREATE VIEW "member_contingent_left" AS
1532 SELECT
1533 "member_id",
1534 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
1535 max("initiative_limit" - "initiative_count") AS "initiatives_left"
1536 FROM "member_contingent" GROUP BY "member_id";
1538 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.';
1541 CREATE TYPE "timeline_event" AS ENUM (
1542 'issue_created',
1543 'issue_canceled',
1544 'issue_accepted',
1545 'issue_half_frozen',
1546 'issue_finished_without_voting',
1547 'issue_voting_started',
1548 'issue_finished_after_voting',
1549 'initiative_created',
1550 'initiative_revoked',
1551 'draft_created',
1552 'suggestion_created');
1554 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables';
1557 CREATE VIEW "timeline_issue" AS
1558 SELECT
1559 "created" AS "occurrence",
1560 'issue_created'::"timeline_event" AS "event",
1561 "id" AS "issue_id"
1562 FROM "issue"
1563 UNION ALL
1564 SELECT
1565 "closed" AS "occurrence",
1566 'issue_canceled'::"timeline_event" AS "event",
1567 "id" AS "issue_id"
1568 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
1569 UNION ALL
1570 SELECT
1571 "accepted" AS "occurrence",
1572 'issue_accepted'::"timeline_event" AS "event",
1573 "id" AS "issue_id"
1574 FROM "issue" WHERE "accepted" NOTNULL
1575 UNION ALL
1576 SELECT
1577 "half_frozen" AS "occurrence",
1578 'issue_half_frozen'::"timeline_event" AS "event",
1579 "id" AS "issue_id"
1580 FROM "issue" WHERE "half_frozen" NOTNULL
1581 UNION ALL
1582 SELECT
1583 "fully_frozen" AS "occurrence",
1584 'issue_voting_started'::"timeline_event" AS "event",
1585 "id" AS "issue_id"
1586 FROM "issue"
1587 WHERE "fully_frozen" NOTNULL
1588 AND ("closed" ISNULL OR "closed" != "fully_frozen")
1589 UNION ALL
1590 SELECT
1591 "closed" AS "occurrence",
1592 CASE WHEN "fully_frozen" = "closed" THEN
1593 'issue_finished_without_voting'::"timeline_event"
1594 ELSE
1595 'issue_finished_after_voting'::"timeline_event"
1596 END AS "event",
1597 "id" AS "issue_id"
1598 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
1600 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view';
1603 CREATE VIEW "timeline_initiative" AS
1604 SELECT
1605 "created" AS "occurrence",
1606 'initiative_created'::"timeline_event" AS "event",
1607 "id" AS "initiative_id"
1608 FROM "initiative"
1609 UNION ALL
1610 SELECT
1611 "revoked" AS "occurrence",
1612 'initiative_revoked'::"timeline_event" AS "event",
1613 "id" AS "initiative_id"
1614 FROM "initiative" WHERE "revoked" NOTNULL;
1616 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view';
1619 CREATE VIEW "timeline_draft" AS
1620 SELECT
1621 "created" AS "occurrence",
1622 'draft_created'::"timeline_event" AS "event",
1623 "id" AS "draft_id"
1624 FROM "draft";
1626 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view';
1629 CREATE VIEW "timeline_suggestion" AS
1630 SELECT
1631 "created" AS "occurrence",
1632 'suggestion_created'::"timeline_event" AS "event",
1633 "id" AS "suggestion_id"
1634 FROM "suggestion";
1636 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view';
1639 CREATE VIEW "timeline" AS
1640 SELECT
1641 "occurrence",
1642 "event",
1643 "issue_id",
1644 NULL AS "initiative_id",
1645 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
1646 NULL::INT8 AS "suggestion_id"
1647 FROM "timeline_issue"
1648 UNION ALL
1649 SELECT
1650 "occurrence",
1651 "event",
1652 NULL AS "issue_id",
1653 "initiative_id",
1654 NULL AS "draft_id",
1655 NULL AS "suggestion_id"
1656 FROM "timeline_initiative"
1657 UNION ALL
1658 SELECT
1659 "occurrence",
1660 "event",
1661 NULL AS "issue_id",
1662 NULL AS "initiative_id",
1663 "draft_id",
1664 NULL AS "suggestion_id"
1665 FROM "timeline_draft"
1666 UNION ALL
1667 SELECT
1668 "occurrence",
1669 "event",
1670 NULL AS "issue_id",
1671 NULL AS "initiative_id",
1672 NULL AS "draft_id",
1673 "suggestion_id"
1674 FROM "timeline_suggestion";
1676 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system';
1680 --------------------------------------------------
1681 -- Set returning function for delegation chains --
1682 --------------------------------------------------
1685 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
1686 ('first', 'intermediate', 'last', 'repetition');
1688 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
1691 CREATE TYPE "delegation_chain_row" AS (
1692 "index" INT4,
1693 "member_id" INT4,
1694 "member_active" BOOLEAN,
1695 "participation" BOOLEAN,
1696 "overridden" BOOLEAN,
1697 "scope_in" "delegation_scope",
1698 "scope_out" "delegation_scope",
1699 "loop" "delegation_chain_loop_tag" );
1701 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
1703 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
1704 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';
1705 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
1706 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
1707 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
1708 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
1711 CREATE FUNCTION "delegation_chain"
1712 ( "member_id_p" "member"."id"%TYPE,
1713 "area_id_p" "area"."id"%TYPE,
1714 "issue_id_p" "issue"."id"%TYPE,
1715 "simulate_trustee_id_p" "member"."id"%TYPE )
1716 RETURNS SETOF "delegation_chain_row"
1717 LANGUAGE 'plpgsql' STABLE AS $$
1718 DECLARE
1719 "issue_row" "issue"%ROWTYPE;
1720 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
1721 "loop_member_id_v" "member"."id"%TYPE;
1722 "output_row" "delegation_chain_row";
1723 "output_rows" "delegation_chain_row"[];
1724 "delegation_row" "delegation"%ROWTYPE;
1725 "row_count" INT4;
1726 "i" INT4;
1727 "loop_v" BOOLEAN;
1728 BEGIN
1729 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1730 "visited_member_ids" := '{}';
1731 "loop_member_id_v" := NULL;
1732 "output_rows" := '{}';
1733 "output_row"."index" := 0;
1734 "output_row"."member_id" := "member_id_p";
1735 "output_row"."member_active" := TRUE;
1736 "output_row"."participation" := FALSE;
1737 "output_row"."overridden" := FALSE;
1738 "output_row"."scope_out" := NULL;
1739 LOOP
1740 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
1741 "loop_member_id_v" := "output_row"."member_id";
1742 ELSE
1743 "visited_member_ids" :=
1744 "visited_member_ids" || "output_row"."member_id";
1745 END IF;
1746 IF "output_row"."participation" THEN
1747 "output_row"."overridden" := TRUE;
1748 END IF;
1749 "output_row"."scope_in" := "output_row"."scope_out";
1750 IF EXISTS (
1751 SELECT NULL FROM "member"
1752 WHERE "id" = "output_row"."member_id" AND "active"
1753 ) THEN
1754 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
1755 SELECT * INTO "delegation_row" FROM "delegation"
1756 WHERE "truster_id" = "output_row"."member_id"
1757 AND "scope" = 'global';
1758 ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN
1759 "output_row"."participation" := EXISTS (
1760 SELECT NULL FROM "membership"
1761 WHERE "area_id" = "area_id_p"
1762 AND "member_id" = "output_row"."member_id"
1763 );
1764 SELECT * INTO "delegation_row" FROM "delegation"
1765 WHERE "truster_id" = "output_row"."member_id"
1766 AND ("scope" = 'global' OR "area_id" = "area_id_p")
1767 ORDER BY "scope" DESC;
1768 ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN
1769 "output_row"."participation" := EXISTS (
1770 SELECT NULL FROM "interest"
1771 WHERE "issue_id" = "issue_id_p"
1772 AND "member_id" = "output_row"."member_id"
1773 );
1774 SELECT * INTO "delegation_row" FROM "delegation"
1775 WHERE "truster_id" = "output_row"."member_id"
1776 AND ("scope" = 'global' OR
1777 "area_id" = "issue_row"."area_id" OR
1778 "issue_id" = "issue_id_p"
1779 )
1780 ORDER BY "scope" DESC;
1781 ELSE
1782 RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.';
1783 END IF;
1784 ELSE
1785 "output_row"."member_active" := FALSE;
1786 "output_row"."participation" := FALSE;
1787 "output_row"."scope_out" := NULL;
1788 "delegation_row" := ROW(NULL);
1789 END IF;
1790 IF
1791 "output_row"."member_id" = "member_id_p" AND
1792 "simulate_trustee_id_p" NOTNULL
1793 THEN
1794 "output_row"."scope_out" := CASE
1795 WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global'
1796 WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area'
1797 WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue'
1798 END;
1799 "output_rows" := "output_rows" || "output_row";
1800 "output_row"."member_id" := "simulate_trustee_id_p";
1801 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
1802 "output_row"."scope_out" := "delegation_row"."scope";
1803 "output_rows" := "output_rows" || "output_row";
1804 "output_row"."member_id" := "delegation_row"."trustee_id";
1805 ELSE
1806 "output_row"."scope_out" := NULL;
1807 "output_rows" := "output_rows" || "output_row";
1808 EXIT;
1809 END IF;
1810 EXIT WHEN "loop_member_id_v" NOTNULL;
1811 "output_row"."index" := "output_row"."index" + 1;
1812 END LOOP;
1813 "row_count" := array_upper("output_rows", 1);
1814 "i" := 1;
1815 "loop_v" := FALSE;
1816 LOOP
1817 "output_row" := "output_rows"["i"];
1818 EXIT WHEN "output_row"."member_id" ISNULL;
1819 IF "loop_v" THEN
1820 IF "i" + 1 = "row_count" THEN
1821 "output_row"."loop" := 'last';
1822 ELSIF "i" = "row_count" THEN
1823 "output_row"."loop" := 'repetition';
1824 ELSE
1825 "output_row"."loop" := 'intermediate';
1826 END IF;
1827 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
1828 "output_row"."loop" := 'first';
1829 "loop_v" := TRUE;
1830 END IF;
1831 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
1832 "output_row"."participation" := NULL;
1833 END IF;
1834 RETURN NEXT "output_row";
1835 "i" := "i" + 1;
1836 END LOOP;
1837 RETURN;
1838 END;
1839 $$;
1841 COMMENT ON FUNCTION "delegation_chain"
1842 ( "member"."id"%TYPE,
1843 "area"."id"%TYPE,
1844 "issue"."id"%TYPE,
1845 "member"."id"%TYPE )
1846 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
1848 CREATE FUNCTION "delegation_chain"
1849 ( "member_id_p" "member"."id"%TYPE,
1850 "area_id_p" "area"."id"%TYPE,
1851 "issue_id_p" "issue"."id"%TYPE )
1852 RETURNS SETOF "delegation_chain_row"
1853 LANGUAGE 'plpgsql' STABLE AS $$
1854 DECLARE
1855 "result_row" "delegation_chain_row";
1856 BEGIN
1857 FOR "result_row" IN
1858 SELECT * FROM "delegation_chain"(
1859 "member_id_p", "area_id_p", "issue_id_p", NULL
1860 )
1861 LOOP
1862 RETURN NEXT "result_row";
1863 END LOOP;
1864 RETURN;
1865 END;
1866 $$;
1868 COMMENT ON FUNCTION "delegation_chain"
1869 ( "member"."id"%TYPE,
1870 "area"."id"%TYPE,
1871 "issue"."id"%TYPE )
1872 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
1876 ------------------------------
1877 -- Comparison by vote count --
1878 ------------------------------
1880 CREATE FUNCTION "vote_ratio"
1881 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
1882 "negative_votes_p" "initiative"."negative_votes"%TYPE )
1883 RETURNS FLOAT8
1884 LANGUAGE 'plpgsql' STABLE AS $$
1885 BEGIN
1886 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
1887 RETURN
1888 "positive_votes_p"::FLOAT8 /
1889 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
1890 ELSIF "positive_votes_p" > 0 THEN
1891 RETURN "positive_votes_p";
1892 ELSIF "negative_votes_p" > 0 THEN
1893 RETURN 1 - "negative_votes_p";
1894 ELSE
1895 RETURN 0.5;
1896 END IF;
1897 END;
1898 $$;
1900 COMMENT ON FUNCTION "vote_ratio"
1901 ( "initiative"."positive_votes"%TYPE,
1902 "initiative"."negative_votes"%TYPE )
1903 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.';
1907 ------------------------------------------------
1908 -- Locking for snapshots and voting procedure --
1909 ------------------------------------------------
1911 CREATE FUNCTION "global_lock"() RETURNS VOID
1912 LANGUAGE 'plpgsql' VOLATILE AS $$
1913 BEGIN
1914 -- NOTE: PostgreSQL allows reading, while tables are locked in
1915 -- exclusive move. Transactions should be kept short anyway!
1916 LOCK TABLE "member" IN EXCLUSIVE MODE;
1917 LOCK TABLE "area" IN EXCLUSIVE MODE;
1918 LOCK TABLE "membership" IN EXCLUSIVE MODE;
1919 -- NOTE: "member", "area" and "membership" are locked first to
1920 -- prevent deadlocks in combination with "calculate_member_counts"()
1921 LOCK TABLE "policy" IN EXCLUSIVE MODE;
1922 LOCK TABLE "issue" IN EXCLUSIVE MODE;
1923 LOCK TABLE "initiative" IN EXCLUSIVE MODE;
1924 LOCK TABLE "draft" IN EXCLUSIVE MODE;
1925 LOCK TABLE "suggestion" IN EXCLUSIVE MODE;
1926 LOCK TABLE "interest" IN EXCLUSIVE MODE;
1927 LOCK TABLE "initiator" IN EXCLUSIVE MODE;
1928 LOCK TABLE "supporter" IN EXCLUSIVE MODE;
1929 LOCK TABLE "opinion" IN EXCLUSIVE MODE;
1930 LOCK TABLE "delegation" IN EXCLUSIVE MODE;
1931 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
1932 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
1933 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
1934 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
1935 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
1936 LOCK TABLE "direct_voter" IN EXCLUSIVE MODE;
1937 LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;
1938 LOCK TABLE "vote" IN EXCLUSIVE MODE;
1939 RETURN;
1940 END;
1941 $$;
1943 COMMENT ON FUNCTION "global_lock"() IS 'Locks all tables related to support/voting until end of transaction; read access is still possible though';
1947 -------------------------------
1948 -- Materialize member counts --
1949 -------------------------------
1951 CREATE FUNCTION "calculate_member_counts"()
1952 RETURNS VOID
1953 LANGUAGE 'plpgsql' VOLATILE AS $$
1954 BEGIN
1955 LOCK TABLE "member" IN EXCLUSIVE MODE;
1956 LOCK TABLE "area" IN EXCLUSIVE MODE;
1957 LOCK TABLE "membership" IN EXCLUSIVE MODE;
1958 DELETE FROM "member_count";
1959 INSERT INTO "member_count" ("total_count")
1960 SELECT "total_count" FROM "member_count_view";
1961 UPDATE "area" SET
1962 "direct_member_count" = "view"."direct_member_count",
1963 "member_weight" = "view"."member_weight",
1964 "autoreject_weight" = "view"."autoreject_weight"
1965 FROM "area_member_count" AS "view"
1966 WHERE "view"."area_id" = "area"."id";
1967 RETURN;
1968 END;
1969 $$;
1971 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"';
1975 ------------------------------
1976 -- Calculation of snapshots --
1977 ------------------------------
1979 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
1980 ( "issue_id_p" "issue"."id"%TYPE,
1981 "member_id_p" "member"."id"%TYPE,
1982 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
1983 RETURNS "direct_population_snapshot"."weight"%TYPE
1984 LANGUAGE 'plpgsql' VOLATILE AS $$
1985 DECLARE
1986 "issue_delegation_row" "issue_delegation"%ROWTYPE;
1987 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
1988 "weight_v" INT4;
1989 "sub_weight_v" INT4;
1990 BEGIN
1991 "weight_v" := 0;
1992 FOR "issue_delegation_row" IN
1993 SELECT * FROM "issue_delegation"
1994 WHERE "trustee_id" = "member_id_p"
1995 AND "issue_id" = "issue_id_p"
1996 LOOP
1997 IF NOT EXISTS (
1998 SELECT NULL FROM "direct_population_snapshot"
1999 WHERE "issue_id" = "issue_id_p"
2000 AND "event" = 'periodic'
2001 AND "member_id" = "issue_delegation_row"."truster_id"
2002 ) AND NOT EXISTS (
2003 SELECT NULL FROM "delegating_population_snapshot"
2004 WHERE "issue_id" = "issue_id_p"
2005 AND "event" = 'periodic'
2006 AND "member_id" = "issue_delegation_row"."truster_id"
2007 ) THEN
2008 "delegate_member_ids_v" :=
2009 "member_id_p" || "delegate_member_ids_p";
2010 INSERT INTO "delegating_population_snapshot" (
2011 "issue_id",
2012 "event",
2013 "member_id",
2014 "scope",
2015 "delegate_member_ids"
2016 ) VALUES (
2017 "issue_id_p",
2018 'periodic',
2019 "issue_delegation_row"."truster_id",
2020 "issue_delegation_row"."scope",
2021 "delegate_member_ids_v"
2022 );
2023 "sub_weight_v" := 1 +
2024 "weight_of_added_delegations_for_population_snapshot"(
2025 "issue_id_p",
2026 "issue_delegation_row"."truster_id",
2027 "delegate_member_ids_v"
2028 );
2029 UPDATE "delegating_population_snapshot"
2030 SET "weight" = "sub_weight_v"
2031 WHERE "issue_id" = "issue_id_p"
2032 AND "event" = 'periodic'
2033 AND "member_id" = "issue_delegation_row"."truster_id";
2034 "weight_v" := "weight_v" + "sub_weight_v";
2035 END IF;
2036 END LOOP;
2037 RETURN "weight_v";
2038 END;
2039 $$;
2041 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
2042 ( "issue"."id"%TYPE,
2043 "member"."id"%TYPE,
2044 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2045 IS 'Helper function for "create_population_snapshot" function';
2048 CREATE FUNCTION "create_population_snapshot"
2049 ( "issue_id_p" "issue"."id"%TYPE )
2050 RETURNS VOID
2051 LANGUAGE 'plpgsql' VOLATILE AS $$
2052 DECLARE
2053 "member_id_v" "member"."id"%TYPE;
2054 BEGIN
2055 DELETE FROM "direct_population_snapshot"
2056 WHERE "issue_id" = "issue_id_p"
2057 AND "event" = 'periodic';
2058 DELETE FROM "delegating_population_snapshot"
2059 WHERE "issue_id" = "issue_id_p"
2060 AND "event" = 'periodic';
2061 INSERT INTO "direct_population_snapshot"
2062 ("issue_id", "event", "member_id")
2063 SELECT
2064 "issue_id_p" AS "issue_id",
2065 'periodic'::"snapshot_event" AS "event",
2066 "member"."id" AS "member_id"
2067 FROM "issue"
2068 JOIN "area" ON "issue"."area_id" = "area"."id"
2069 JOIN "membership" ON "area"."id" = "membership"."area_id"
2070 JOIN "member" ON "membership"."member_id" = "member"."id"
2071 WHERE "issue"."id" = "issue_id_p"
2072 AND "member"."active"
2073 UNION
2074 SELECT
2075 "issue_id_p" AS "issue_id",
2076 'periodic'::"snapshot_event" AS "event",
2077 "member"."id" AS "member_id"
2078 FROM "interest" JOIN "member"
2079 ON "interest"."member_id" = "member"."id"
2080 WHERE "interest"."issue_id" = "issue_id_p"
2081 AND "member"."active";
2082 FOR "member_id_v" IN
2083 SELECT "member_id" FROM "direct_population_snapshot"
2084 WHERE "issue_id" = "issue_id_p"
2085 AND "event" = 'periodic'
2086 LOOP
2087 UPDATE "direct_population_snapshot" SET
2088 "weight" = 1 +
2089 "weight_of_added_delegations_for_population_snapshot"(
2090 "issue_id_p",
2091 "member_id_v",
2092 '{}'
2093 )
2094 WHERE "issue_id" = "issue_id_p"
2095 AND "event" = 'periodic'
2096 AND "member_id" = "member_id_v";
2097 END LOOP;
2098 RETURN;
2099 END;
2100 $$;
2102 COMMENT ON FUNCTION "create_population_snapshot"
2103 ( "issue_id_p" "issue"."id"%TYPE )
2104 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.';
2107 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2108 ( "issue_id_p" "issue"."id"%TYPE,
2109 "member_id_p" "member"."id"%TYPE,
2110 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2111 RETURNS "direct_interest_snapshot"."weight"%TYPE
2112 LANGUAGE 'plpgsql' VOLATILE AS $$
2113 DECLARE
2114 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2115 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2116 "weight_v" INT4;
2117 "sub_weight_v" INT4;
2118 BEGIN
2119 "weight_v" := 0;
2120 FOR "issue_delegation_row" IN
2121 SELECT * FROM "issue_delegation"
2122 WHERE "trustee_id" = "member_id_p"
2123 AND "issue_id" = "issue_id_p"
2124 LOOP
2125 IF NOT EXISTS (
2126 SELECT NULL FROM "direct_interest_snapshot"
2127 WHERE "issue_id" = "issue_id_p"
2128 AND "event" = 'periodic'
2129 AND "member_id" = "issue_delegation_row"."truster_id"
2130 ) AND NOT EXISTS (
2131 SELECT NULL FROM "delegating_interest_snapshot"
2132 WHERE "issue_id" = "issue_id_p"
2133 AND "event" = 'periodic'
2134 AND "member_id" = "issue_delegation_row"."truster_id"
2135 ) THEN
2136 "delegate_member_ids_v" :=
2137 "member_id_p" || "delegate_member_ids_p";
2138 INSERT INTO "delegating_interest_snapshot" (
2139 "issue_id",
2140 "event",
2141 "member_id",
2142 "scope",
2143 "delegate_member_ids"
2144 ) VALUES (
2145 "issue_id_p",
2146 'periodic',
2147 "issue_delegation_row"."truster_id",
2148 "issue_delegation_row"."scope",
2149 "delegate_member_ids_v"
2150 );
2151 "sub_weight_v" := 1 +
2152 "weight_of_added_delegations_for_interest_snapshot"(
2153 "issue_id_p",
2154 "issue_delegation_row"."truster_id",
2155 "delegate_member_ids_v"
2156 );
2157 UPDATE "delegating_interest_snapshot"
2158 SET "weight" = "sub_weight_v"
2159 WHERE "issue_id" = "issue_id_p"
2160 AND "event" = 'periodic'
2161 AND "member_id" = "issue_delegation_row"."truster_id";
2162 "weight_v" := "weight_v" + "sub_weight_v";
2163 END IF;
2164 END LOOP;
2165 RETURN "weight_v";
2166 END;
2167 $$;
2169 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2170 ( "issue"."id"%TYPE,
2171 "member"."id"%TYPE,
2172 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2173 IS 'Helper function for "create_interest_snapshot" function';
2176 CREATE FUNCTION "create_interest_snapshot"
2177 ( "issue_id_p" "issue"."id"%TYPE )
2178 RETURNS VOID
2179 LANGUAGE 'plpgsql' VOLATILE AS $$
2180 DECLARE
2181 "member_id_v" "member"."id"%TYPE;
2182 BEGIN
2183 DELETE FROM "direct_interest_snapshot"
2184 WHERE "issue_id" = "issue_id_p"
2185 AND "event" = 'periodic';
2186 DELETE FROM "delegating_interest_snapshot"
2187 WHERE "issue_id" = "issue_id_p"
2188 AND "event" = 'periodic';
2189 DELETE FROM "direct_supporter_snapshot"
2190 WHERE "issue_id" = "issue_id_p"
2191 AND "event" = 'periodic';
2192 INSERT INTO "direct_interest_snapshot"
2193 ("issue_id", "event", "member_id", "voting_requested")
2194 SELECT
2195 "issue_id_p" AS "issue_id",
2196 'periodic' AS "event",
2197 "member"."id" AS "member_id",
2198 "interest"."voting_requested"
2199 FROM "interest" JOIN "member"
2200 ON "interest"."member_id" = "member"."id"
2201 WHERE "interest"."issue_id" = "issue_id_p"
2202 AND "member"."active";
2203 FOR "member_id_v" IN
2204 SELECT "member_id" FROM "direct_interest_snapshot"
2205 WHERE "issue_id" = "issue_id_p"
2206 AND "event" = 'periodic'
2207 LOOP
2208 UPDATE "direct_interest_snapshot" SET
2209 "weight" = 1 +
2210 "weight_of_added_delegations_for_interest_snapshot"(
2211 "issue_id_p",
2212 "member_id_v",
2213 '{}'
2214 )
2215 WHERE "issue_id" = "issue_id_p"
2216 AND "event" = 'periodic'
2217 AND "member_id" = "member_id_v";
2218 END LOOP;
2219 INSERT INTO "direct_supporter_snapshot"
2220 ( "issue_id", "initiative_id", "event", "member_id",
2221 "informed", "satisfied" )
2222 SELECT
2223 "issue_id_p" AS "issue_id",
2224 "initiative"."id" AS "initiative_id",
2225 'periodic' AS "event",
2226 "member"."id" AS "member_id",
2227 "supporter"."draft_id" = "current_draft"."id" AS "informed",
2228 NOT EXISTS (
2229 SELECT NULL FROM "critical_opinion"
2230 WHERE "initiative_id" = "initiative"."id"
2231 AND "member_id" = "member"."id"
2232 ) AS "satisfied"
2233 FROM "supporter"
2234 JOIN "member"
2235 ON "supporter"."member_id" = "member"."id"
2236 JOIN "initiative"
2237 ON "supporter"."initiative_id" = "initiative"."id"
2238 JOIN "current_draft"
2239 ON "initiative"."id" = "current_draft"."initiative_id"
2240 JOIN "direct_interest_snapshot"
2241 ON "member"."id" = "direct_interest_snapshot"."member_id"
2242 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
2243 AND "event" = 'periodic'
2244 WHERE "member"."active"
2245 AND "initiative"."issue_id" = "issue_id_p";
2246 RETURN;
2247 END;
2248 $$;
2250 COMMENT ON FUNCTION "create_interest_snapshot"
2251 ( "issue"."id"%TYPE )
2252 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.';
2255 CREATE FUNCTION "create_snapshot"
2256 ( "issue_id_p" "issue"."id"%TYPE )
2257 RETURNS VOID
2258 LANGUAGE 'plpgsql' VOLATILE AS $$
2259 DECLARE
2260 "initiative_id_v" "initiative"."id"%TYPE;
2261 "suggestion_id_v" "suggestion"."id"%TYPE;
2262 BEGIN
2263 PERFORM "global_lock"();
2264 PERFORM "create_population_snapshot"("issue_id_p");
2265 PERFORM "create_interest_snapshot"("issue_id_p");
2266 UPDATE "issue" SET
2267 "snapshot" = now(),
2268 "latest_snapshot_event" = 'periodic',
2269 "population" = (
2270 SELECT coalesce(sum("weight"), 0)
2271 FROM "direct_population_snapshot"
2272 WHERE "issue_id" = "issue_id_p"
2273 AND "event" = 'periodic'
2274 ),
2275 "vote_now" = (
2276 SELECT coalesce(sum("weight"), 0)
2277 FROM "direct_interest_snapshot"
2278 WHERE "issue_id" = "issue_id_p"
2279 AND "event" = 'periodic'
2280 AND "voting_requested" = TRUE
2281 ),
2282 "vote_later" = (
2283 SELECT coalesce(sum("weight"), 0)
2284 FROM "direct_interest_snapshot"
2285 WHERE "issue_id" = "issue_id_p"
2286 AND "event" = 'periodic'
2287 AND "voting_requested" = FALSE
2288 )
2289 WHERE "id" = "issue_id_p";
2290 FOR "initiative_id_v" IN
2291 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
2292 LOOP
2293 UPDATE "initiative" SET
2294 "supporter_count" = (
2295 SELECT coalesce(sum("di"."weight"), 0)
2296 FROM "direct_interest_snapshot" AS "di"
2297 JOIN "direct_supporter_snapshot" AS "ds"
2298 ON "di"."member_id" = "ds"."member_id"
2299 WHERE "di"."issue_id" = "issue_id_p"
2300 AND "di"."event" = 'periodic'
2301 AND "ds"."initiative_id" = "initiative_id_v"
2302 AND "ds"."event" = 'periodic'
2303 ),
2304 "informed_supporter_count" = (
2305 SELECT coalesce(sum("di"."weight"), 0)
2306 FROM "direct_interest_snapshot" AS "di"
2307 JOIN "direct_supporter_snapshot" AS "ds"
2308 ON "di"."member_id" = "ds"."member_id"
2309 WHERE "di"."issue_id" = "issue_id_p"
2310 AND "di"."event" = 'periodic'
2311 AND "ds"."initiative_id" = "initiative_id_v"
2312 AND "ds"."event" = 'periodic'
2313 AND "ds"."informed"
2314 ),
2315 "satisfied_supporter_count" = (
2316 SELECT coalesce(sum("di"."weight"), 0)
2317 FROM "direct_interest_snapshot" AS "di"
2318 JOIN "direct_supporter_snapshot" AS "ds"
2319 ON "di"."member_id" = "ds"."member_id"
2320 WHERE "di"."issue_id" = "issue_id_p"
2321 AND "di"."event" = 'periodic'
2322 AND "ds"."initiative_id" = "initiative_id_v"
2323 AND "ds"."event" = 'periodic'
2324 AND "ds"."satisfied"
2325 ),
2326 "satisfied_informed_supporter_count" = (
2327 SELECT coalesce(sum("di"."weight"), 0)
2328 FROM "direct_interest_snapshot" AS "di"
2329 JOIN "direct_supporter_snapshot" AS "ds"
2330 ON "di"."member_id" = "ds"."member_id"
2331 WHERE "di"."issue_id" = "issue_id_p"
2332 AND "di"."event" = 'periodic'
2333 AND "ds"."initiative_id" = "initiative_id_v"
2334 AND "ds"."event" = 'periodic'
2335 AND "ds"."informed"
2336 AND "ds"."satisfied"
2337 )
2338 WHERE "id" = "initiative_id_v";
2339 FOR "suggestion_id_v" IN
2340 SELECT "id" FROM "suggestion"
2341 WHERE "initiative_id" = "initiative_id_v"
2342 LOOP
2343 UPDATE "suggestion" SET
2344 "minus2_unfulfilled_count" = (
2345 SELECT coalesce(sum("snapshot"."weight"), 0)
2346 FROM "issue" CROSS JOIN "opinion"
2347 JOIN "direct_interest_snapshot" AS "snapshot"
2348 ON "snapshot"."issue_id" = "issue"."id"
2349 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2350 AND "snapshot"."member_id" = "opinion"."member_id"
2351 WHERE "issue"."id" = "issue_id_p"
2352 AND "opinion"."suggestion_id" = "suggestion_id_v"
2353 AND "opinion"."degree" = -2
2354 AND "opinion"."fulfilled" = FALSE
2355 ),
2356 "minus2_fulfilled_count" = (
2357 SELECT coalesce(sum("snapshot"."weight"), 0)
2358 FROM "issue" CROSS JOIN "opinion"
2359 JOIN "direct_interest_snapshot" AS "snapshot"
2360 ON "snapshot"."issue_id" = "issue"."id"
2361 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2362 AND "snapshot"."member_id" = "opinion"."member_id"
2363 WHERE "issue"."id" = "issue_id_p"
2364 AND "opinion"."suggestion_id" = "suggestion_id_v"
2365 AND "opinion"."degree" = -2
2366 AND "opinion"."fulfilled" = TRUE
2367 ),
2368 "minus1_unfulfilled_count" = (
2369 SELECT coalesce(sum("snapshot"."weight"), 0)
2370 FROM "issue" CROSS JOIN "opinion"
2371 JOIN "direct_interest_snapshot" AS "snapshot"
2372 ON "snapshot"."issue_id" = "issue"."id"
2373 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2374 AND "snapshot"."member_id" = "opinion"."member_id"
2375 WHERE "issue"."id" = "issue_id_p"
2376 AND "opinion"."suggestion_id" = "suggestion_id_v"
2377 AND "opinion"."degree" = -1
2378 AND "opinion"."fulfilled" = FALSE
2379 ),
2380 "minus1_fulfilled_count" = (
2381 SELECT coalesce(sum("snapshot"."weight"), 0)
2382 FROM "issue" CROSS JOIN "opinion"
2383 JOIN "direct_interest_snapshot" AS "snapshot"
2384 ON "snapshot"."issue_id" = "issue"."id"
2385 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2386 AND "snapshot"."member_id" = "opinion"."member_id"
2387 WHERE "issue"."id" = "issue_id_p"
2388 AND "opinion"."suggestion_id" = "suggestion_id_v"
2389 AND "opinion"."degree" = -1
2390 AND "opinion"."fulfilled" = TRUE
2391 ),
2392 "plus1_unfulfilled_count" = (
2393 SELECT coalesce(sum("snapshot"."weight"), 0)
2394 FROM "issue" CROSS JOIN "opinion"
2395 JOIN "direct_interest_snapshot" AS "snapshot"
2396 ON "snapshot"."issue_id" = "issue"."id"
2397 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2398 AND "snapshot"."member_id" = "opinion"."member_id"
2399 WHERE "issue"."id" = "issue_id_p"
2400 AND "opinion"."suggestion_id" = "suggestion_id_v"
2401 AND "opinion"."degree" = 1
2402 AND "opinion"."fulfilled" = FALSE
2403 ),
2404 "plus1_fulfilled_count" = (
2405 SELECT coalesce(sum("snapshot"."weight"), 0)
2406 FROM "issue" CROSS JOIN "opinion"
2407 JOIN "direct_interest_snapshot" AS "snapshot"
2408 ON "snapshot"."issue_id" = "issue"."id"
2409 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2410 AND "snapshot"."member_id" = "opinion"."member_id"
2411 WHERE "issue"."id" = "issue_id_p"
2412 AND "opinion"."suggestion_id" = "suggestion_id_v"
2413 AND "opinion"."degree" = 1
2414 AND "opinion"."fulfilled" = TRUE
2415 ),
2416 "plus2_unfulfilled_count" = (
2417 SELECT coalesce(sum("snapshot"."weight"), 0)
2418 FROM "issue" CROSS JOIN "opinion"
2419 JOIN "direct_interest_snapshot" AS "snapshot"
2420 ON "snapshot"."issue_id" = "issue"."id"
2421 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2422 AND "snapshot"."member_id" = "opinion"."member_id"
2423 WHERE "issue"."id" = "issue_id_p"
2424 AND "opinion"."suggestion_id" = "suggestion_id_v"
2425 AND "opinion"."degree" = 2
2426 AND "opinion"."fulfilled" = FALSE
2427 ),
2428 "plus2_fulfilled_count" = (
2429 SELECT coalesce(sum("snapshot"."weight"), 0)
2430 FROM "issue" CROSS JOIN "opinion"
2431 JOIN "direct_interest_snapshot" AS "snapshot"
2432 ON "snapshot"."issue_id" = "issue"."id"
2433 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2434 AND "snapshot"."member_id" = "opinion"."member_id"
2435 WHERE "issue"."id" = "issue_id_p"
2436 AND "opinion"."suggestion_id" = "suggestion_id_v"
2437 AND "opinion"."degree" = 2
2438 AND "opinion"."fulfilled" = TRUE
2439 )
2440 WHERE "suggestion"."id" = "suggestion_id_v";
2441 END LOOP;
2442 END LOOP;
2443 RETURN;
2444 END;
2445 $$;
2447 COMMENT ON FUNCTION "create_snapshot"
2448 ( "issue"."id"%TYPE )
2449 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.';
2452 CREATE FUNCTION "set_snapshot_event"
2453 ( "issue_id_p" "issue"."id"%TYPE,
2454 "event_p" "snapshot_event" )
2455 RETURNS VOID
2456 LANGUAGE 'plpgsql' VOLATILE AS $$
2457 DECLARE
2458 "event_v" "issue"."latest_snapshot_event"%TYPE;
2459 BEGIN
2460 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
2461 WHERE "id" = "issue_id_p" FOR UPDATE;
2462 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
2463 WHERE "id" = "issue_id_p";
2464 UPDATE "direct_population_snapshot" SET "event" = "event_p"
2465 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2466 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
2467 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2468 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
2469 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2470 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
2471 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2472 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
2473 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2474 RETURN;
2475 END;
2476 $$;
2478 COMMENT ON FUNCTION "set_snapshot_event"
2479 ( "issue"."id"%TYPE,
2480 "snapshot_event" )
2481 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
2485 ---------------------
2486 -- Freezing issues --
2487 ---------------------
2489 CREATE FUNCTION "freeze_after_snapshot"
2490 ( "issue_id_p" "issue"."id"%TYPE )
2491 RETURNS VOID
2492 LANGUAGE 'plpgsql' VOLATILE AS $$
2493 DECLARE
2494 "issue_row" "issue"%ROWTYPE;
2495 "policy_row" "policy"%ROWTYPE;
2496 "initiative_row" "initiative"%ROWTYPE;
2497 BEGIN
2498 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2499 SELECT * INTO "policy_row"
2500 FROM "policy" WHERE "id" = "issue_row"."policy_id";
2501 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
2502 UPDATE "issue" SET
2503 "accepted" = coalesce("accepted", now()),
2504 "half_frozen" = coalesce("half_frozen", now()),
2505 "fully_frozen" = now()
2506 WHERE "id" = "issue_id_p";
2507 FOR "initiative_row" IN
2508 SELECT * FROM "initiative"
2509 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
2510 LOOP
2511 IF
2512 "initiative_row"."satisfied_supporter_count" > 0 AND
2513 "initiative_row"."satisfied_supporter_count" *
2514 "policy_row"."initiative_quorum_den" >=
2515 "issue_row"."population" * "policy_row"."initiative_quorum_num"
2516 THEN
2517 UPDATE "initiative" SET "admitted" = TRUE
2518 WHERE "id" = "initiative_row"."id";
2519 ELSE
2520 UPDATE "initiative" SET "admitted" = FALSE
2521 WHERE "id" = "initiative_row"."id";
2522 END IF;
2523 END LOOP;
2524 IF NOT EXISTS (
2525 SELECT NULL FROM "initiative"
2526 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
2527 ) THEN
2528 PERFORM "close_voting"("issue_id_p");
2529 END IF;
2530 RETURN;
2531 END;
2532 $$;
2534 COMMENT ON FUNCTION "freeze_after_snapshot"
2535 ( "issue"."id"%TYPE )
2536 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
2539 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
2540 RETURNS VOID
2541 LANGUAGE 'plpgsql' VOLATILE AS $$
2542 DECLARE
2543 "issue_row" "issue"%ROWTYPE;
2544 BEGIN
2545 PERFORM "create_snapshot"("issue_id_p");
2546 PERFORM "freeze_after_snapshot"("issue_id_p");
2547 RETURN;
2548 END;
2549 $$;
2551 COMMENT ON FUNCTION "manual_freeze"
2552 ( "issue"."id"%TYPE )
2553 IS 'Freeze an issue manually (fully) and start voting';
2557 -----------------------
2558 -- Counting of votes --
2559 -----------------------
2562 CREATE FUNCTION "weight_of_added_vote_delegations"
2563 ( "issue_id_p" "issue"."id"%TYPE,
2564 "member_id_p" "member"."id"%TYPE,
2565 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
2566 RETURNS "direct_voter"."weight"%TYPE
2567 LANGUAGE 'plpgsql' VOLATILE AS $$
2568 DECLARE
2569 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2570 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
2571 "weight_v" INT4;
2572 "sub_weight_v" INT4;
2573 BEGIN
2574 "weight_v" := 0;
2575 FOR "issue_delegation_row" IN
2576 SELECT * FROM "issue_delegation"
2577 WHERE "trustee_id" = "member_id_p"
2578 AND "issue_id" = "issue_id_p"
2579 LOOP
2580 IF NOT EXISTS (
2581 SELECT NULL FROM "direct_voter"
2582 WHERE "member_id" = "issue_delegation_row"."truster_id"
2583 AND "issue_id" = "issue_id_p"
2584 ) AND NOT EXISTS (
2585 SELECT NULL FROM "delegating_voter"
2586 WHERE "member_id" = "issue_delegation_row"."truster_id"
2587 AND "issue_id" = "issue_id_p"
2588 ) THEN
2589 "delegate_member_ids_v" :=
2590 "member_id_p" || "delegate_member_ids_p";
2591 INSERT INTO "delegating_voter" (
2592 "issue_id",
2593 "member_id",
2594 "scope",
2595 "delegate_member_ids"
2596 ) VALUES (
2597 "issue_id_p",
2598 "issue_delegation_row"."truster_id",
2599 "issue_delegation_row"."scope",
2600 "delegate_member_ids_v"
2601 );
2602 "sub_weight_v" := 1 +
2603 "weight_of_added_vote_delegations"(
2604 "issue_id_p",
2605 "issue_delegation_row"."truster_id",
2606 "delegate_member_ids_v"
2607 );
2608 UPDATE "delegating_voter"
2609 SET "weight" = "sub_weight_v"
2610 WHERE "issue_id" = "issue_id_p"
2611 AND "member_id" = "issue_delegation_row"."truster_id";
2612 "weight_v" := "weight_v" + "sub_weight_v";
2613 END IF;
2614 END LOOP;
2615 RETURN "weight_v";
2616 END;
2617 $$;
2619 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
2620 ( "issue"."id"%TYPE,
2621 "member"."id"%TYPE,
2622 "delegating_voter"."delegate_member_ids"%TYPE )
2623 IS 'Helper function for "add_vote_delegations" function';
2626 CREATE FUNCTION "add_vote_delegations"
2627 ( "issue_id_p" "issue"."id"%TYPE )
2628 RETURNS VOID
2629 LANGUAGE 'plpgsql' VOLATILE AS $$
2630 DECLARE
2631 "member_id_v" "member"."id"%TYPE;
2632 BEGIN
2633 FOR "member_id_v" IN
2634 SELECT "member_id" FROM "direct_voter"
2635 WHERE "issue_id" = "issue_id_p"
2636 LOOP
2637 UPDATE "direct_voter" SET
2638 "weight" = "weight" + "weight_of_added_vote_delegations"(
2639 "issue_id_p",
2640 "member_id_v",
2641 '{}'
2642 )
2643 WHERE "member_id" = "member_id_v"
2644 AND "issue_id" = "issue_id_p";
2645 END LOOP;
2646 RETURN;
2647 END;
2648 $$;
2650 COMMENT ON FUNCTION "add_vote_delegations"
2651 ( "issue_id_p" "issue"."id"%TYPE )
2652 IS 'Helper function for "close_voting" function';
2655 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
2656 RETURNS VOID
2657 LANGUAGE 'plpgsql' VOLATILE AS $$
2658 DECLARE
2659 "issue_row" "issue"%ROWTYPE;
2660 "member_id_v" "member"."id"%TYPE;
2661 BEGIN
2662 PERFORM "global_lock"();
2663 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2664 DELETE FROM "delegating_voter"
2665 WHERE "issue_id" = "issue_id_p";
2666 DELETE FROM "direct_voter"
2667 WHERE "issue_id" = "issue_id_p"
2668 AND "autoreject" = TRUE;
2669 DELETE FROM "direct_voter" USING "member"
2670 WHERE "direct_voter"."member_id" = "member"."id"
2671 AND "direct_voter"."issue_id" = "issue_id_p"
2672 AND "member"."active" = FALSE;
2673 UPDATE "direct_voter" SET "weight" = 1
2674 WHERE "issue_id" = "issue_id_p";
2675 PERFORM "add_vote_delegations"("issue_id_p");
2676 FOR "member_id_v" IN
2677 SELECT "interest"."member_id"
2678 FROM "interest"
2679 LEFT JOIN "direct_voter"
2680 ON "interest"."member_id" = "direct_voter"."member_id"
2681 AND "interest"."issue_id" = "direct_voter"."issue_id"
2682 LEFT JOIN "delegating_voter"
2683 ON "interest"."member_id" = "delegating_voter"."member_id"
2684 AND "interest"."issue_id" = "delegating_voter"."issue_id"
2685 WHERE "interest"."issue_id" = "issue_id_p"
2686 AND "interest"."autoreject" = TRUE
2687 AND "direct_voter"."member_id" ISNULL
2688 AND "delegating_voter"."member_id" ISNULL
2689 UNION SELECT "membership"."member_id"
2690 FROM "membership"
2691 LEFT JOIN "interest"
2692 ON "membership"."member_id" = "interest"."member_id"
2693 AND "interest"."issue_id" = "issue_id_p"
2694 LEFT JOIN "direct_voter"
2695 ON "membership"."member_id" = "direct_voter"."member_id"
2696 AND "direct_voter"."issue_id" = "issue_id_p"
2697 LEFT JOIN "delegating_voter"
2698 ON "membership"."member_id" = "delegating_voter"."member_id"
2699 AND "delegating_voter"."issue_id" = "issue_id_p"
2700 WHERE "membership"."area_id" = "issue_row"."area_id"
2701 AND "membership"."autoreject" = TRUE
2702 AND "interest"."autoreject" ISNULL
2703 AND "direct_voter"."member_id" ISNULL
2704 AND "delegating_voter"."member_id" ISNULL
2705 LOOP
2706 INSERT INTO "direct_voter"
2707 ("member_id", "issue_id", "weight", "autoreject") VALUES
2708 ("member_id_v", "issue_id_p", 1, TRUE);
2709 INSERT INTO "vote" (
2710 "member_id",
2711 "issue_id",
2712 "initiative_id",
2713 "grade"
2714 ) SELECT
2715 "member_id_v" AS "member_id",
2716 "issue_id_p" AS "issue_id",
2717 "id" AS "initiative_id",
2718 -1 AS "grade"
2719 FROM "initiative" WHERE "issue_id" = "issue_id_p";
2720 END LOOP;
2721 PERFORM "add_vote_delegations"("issue_id_p");
2722 UPDATE "issue" SET
2723 "closed" = now(),
2724 "voter_count" = (
2725 SELECT coalesce(sum("weight"), 0)
2726 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
2727 )
2728 WHERE "id" = "issue_id_p";
2729 UPDATE "initiative" SET
2730 "positive_votes" = "vote_counts"."positive_votes",
2731 "negative_votes" = "vote_counts"."negative_votes",
2732 "agreed" = CASE WHEN "majority_strict" THEN
2733 "vote_counts"."positive_votes" * "majority_den" >
2734 "majority_num" *
2735 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
2736 ELSE
2737 "vote_counts"."positive_votes" * "majority_den" >=
2738 "majority_num" *
2739 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
2740 END
2741 FROM
2742 ( SELECT
2743 "initiative"."id" AS "initiative_id",
2744 coalesce(
2745 sum(
2746 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
2747 ),
2748 0
2749 ) AS "positive_votes",
2750 coalesce(
2751 sum(
2752 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
2753 ),
2754 0
2755 ) AS "negative_votes"
2756 FROM "initiative"
2757 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
2758 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
2759 LEFT JOIN "direct_voter"
2760 ON "direct_voter"."issue_id" = "initiative"."issue_id"
2761 LEFT JOIN "vote"
2762 ON "vote"."initiative_id" = "initiative"."id"
2763 AND "vote"."member_id" = "direct_voter"."member_id"
2764 WHERE "initiative"."issue_id" = "issue_id_p"
2765 AND "initiative"."admitted" -- NOTE: NULL case is handled too
2766 GROUP BY "initiative"."id"
2767 ) AS "vote_counts",
2768 "issue",
2769 "policy"
2770 WHERE "vote_counts"."initiative_id" = "initiative"."id"
2771 AND "issue"."id" = "initiative"."issue_id"
2772 AND "policy"."id" = "issue"."policy_id";
2773 -- NOTE: "closed" column of issue must be set at this point
2774 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
2775 INSERT INTO "battle" (
2776 "issue_id",
2777 "winning_initiative_id", "losing_initiative_id",
2778 "count"
2779 ) SELECT
2780 "issue_id",
2781 "winning_initiative_id", "losing_initiative_id",
2782 "count"
2783 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
2784 END;
2785 $$;
2787 COMMENT ON FUNCTION "close_voting"
2788 ( "issue"."id"%TYPE )
2789 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.';
2792 CREATE FUNCTION "defeat_strength"
2793 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
2794 RETURNS INT8
2795 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2796 BEGIN
2797 IF "positive_votes_p" > "negative_votes_p" THEN
2798 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
2799 ELSIF "positive_votes_p" = "negative_votes_p" THEN
2800 RETURN 0;
2801 ELSE
2802 RETURN -1;
2803 END IF;
2804 END;
2805 $$;
2807 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';
2810 CREATE FUNCTION "array_init_string"("dim_p" INTEGER)
2811 RETURNS TEXT
2812 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2813 DECLARE
2814 "i" INTEGER;
2815 "ary_text_v" TEXT;
2816 BEGIN
2817 IF "dim_p" >= 1 THEN
2818 "ary_text_v" := '{NULL';
2819 "i" := "dim_p";
2820 LOOP
2821 "i" := "i" - 1;
2822 EXIT WHEN "i" = 0;
2823 "ary_text_v" := "ary_text_v" || ',NULL';
2824 END LOOP;
2825 "ary_text_v" := "ary_text_v" || '}';
2826 RETURN "ary_text_v";
2827 ELSE
2828 RAISE EXCEPTION 'Dimension needs to be at least 1.';
2829 END IF;
2830 END;
2831 $$;
2833 COMMENT ON FUNCTION "array_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
2836 CREATE FUNCTION "square_matrix_init_string"("dim_p" INTEGER)
2837 RETURNS TEXT
2838 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2839 DECLARE
2840 "i" INTEGER;
2841 "row_text_v" TEXT;
2842 "ary_text_v" TEXT;
2843 BEGIN
2844 IF "dim_p" >= 1 THEN
2845 "row_text_v" := '{NULL';
2846 "i" := "dim_p";
2847 LOOP
2848 "i" := "i" - 1;
2849 EXIT WHEN "i" = 0;
2850 "row_text_v" := "row_text_v" || ',NULL';
2851 END LOOP;
2852 "row_text_v" := "row_text_v" || '}';
2853 "ary_text_v" := '{' || "row_text_v";
2854 "i" := "dim_p";
2855 LOOP
2856 "i" := "i" - 1;
2857 EXIT WHEN "i" = 0;
2858 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
2859 END LOOP;
2860 "ary_text_v" := "ary_text_v" || '}';
2861 RETURN "ary_text_v";
2862 ELSE
2863 RAISE EXCEPTION 'Dimension needs to be at least 1.';
2864 END IF;
2865 END;
2866 $$;
2868 COMMENT ON FUNCTION "square_matrix_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
2871 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
2872 RETURNS VOID
2873 LANGUAGE 'plpgsql' VOLATILE AS $$
2874 DECLARE
2875 "dimension_v" INTEGER;
2876 "vote_matrix" INT4[][]; -- absolute votes
2877 "matrix" INT8[][]; -- defeat strength / best paths
2878 "i" INTEGER;
2879 "j" INTEGER;
2880 "k" INTEGER;
2881 "battle_row" "battle"%ROWTYPE;
2882 "rank_ary" INT4[];
2883 "rank_v" INT4;
2884 "done_v" INTEGER;
2885 "winners_ary" INTEGER[];
2886 "initiative_id_v" "initiative"."id"%TYPE;
2887 BEGIN
2888 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2889 SELECT count(1) INTO "dimension_v" FROM "initiative"
2890 WHERE "issue_id" = "issue_id_p" AND "agreed";
2891 IF "dimension_v" = 1 THEN
2892 UPDATE "initiative" SET "rank" = 1
2893 WHERE "issue_id" = "issue_id_p" AND "agreed";
2894 ELSIF "dimension_v" > 1 THEN
2895 -- Create "vote_matrix" with absolute number of votes in pairwise
2896 -- comparison:
2897 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2898 "i" := 1;
2899 "j" := 2;
2900 FOR "battle_row" IN
2901 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
2902 ORDER BY "winning_initiative_id", "losing_initiative_id"
2903 LOOP
2904 "vote_matrix"["i"]["j"] := "battle_row"."count";
2905 IF "j" = "dimension_v" THEN
2906 "i" := "i" + 1;
2907 "j" := 1;
2908 ELSE
2909 "j" := "j" + 1;
2910 IF "j" = "i" THEN
2911 "j" := "j" + 1;
2912 END IF;
2913 END IF;
2914 END LOOP;
2915 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
2916 RAISE EXCEPTION 'Wrong battle count (should not happen)';
2917 END IF;
2918 -- Store defeat strengths in "matrix" using "defeat_strength"
2919 -- function:
2920 "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2921 "i" := 1;
2922 LOOP
2923 "j" := 1;
2924 LOOP
2925 IF "i" != "j" THEN
2926 "matrix"["i"]["j"] := "defeat_strength"(
2927 "vote_matrix"["i"]["j"],
2928 "vote_matrix"["j"]["i"]
2929 );
2930 END IF;
2931 EXIT WHEN "j" = "dimension_v";
2932 "j" := "j" + 1;
2933 END LOOP;
2934 EXIT WHEN "i" = "dimension_v";
2935 "i" := "i" + 1;
2936 END LOOP;
2937 -- Find best paths:
2938 "i" := 1;
2939 LOOP
2940 "j" := 1;
2941 LOOP
2942 IF "i" != "j" THEN
2943 "k" := 1;
2944 LOOP
2945 IF "i" != "k" AND "j" != "k" THEN
2946 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
2947 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
2948 "matrix"["j"]["k"] := "matrix"["j"]["i"];
2949 END IF;
2950 ELSE
2951 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
2952 "matrix"["j"]["k"] := "matrix"["i"]["k"];
2953 END IF;
2954 END IF;
2955 END IF;
2956 EXIT WHEN "k" = "dimension_v";
2957 "k" := "k" + 1;
2958 END LOOP;
2959 END IF;
2960 EXIT WHEN "j" = "dimension_v";
2961 "j" := "j" + 1;
2962 END LOOP;
2963 EXIT WHEN "i" = "dimension_v";
2964 "i" := "i" + 1;
2965 END LOOP;
2966 -- Determine order of winners:
2967 "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2968 "rank_v" := 1;
2969 "done_v" := 0;
2970 LOOP
2971 "winners_ary" := '{}';
2972 "i" := 1;
2973 LOOP
2974 IF "rank_ary"["i"] ISNULL THEN
2975 "j" := 1;
2976 LOOP
2977 IF
2978 "i" != "j" AND
2979 "rank_ary"["j"] ISNULL AND
2980 "matrix"["j"]["i"] > "matrix"["i"]["j"]
2981 THEN
2982 -- someone else is better
2983 EXIT;
2984 END IF;
2985 IF "j" = "dimension_v" THEN
2986 -- noone is better
2987 "winners_ary" := "winners_ary" || "i";
2988 EXIT;
2989 END IF;
2990 "j" := "j" + 1;
2991 END LOOP;
2992 END IF;
2993 EXIT WHEN "i" = "dimension_v";
2994 "i" := "i" + 1;
2995 END LOOP;
2996 "i" := 1;
2997 LOOP
2998 "rank_ary"["winners_ary"["i"]] := "rank_v";
2999 "done_v" := "done_v" + 1;
3000 EXIT WHEN "i" = array_upper("winners_ary", 1);
3001 "i" := "i" + 1;
3002 END LOOP;
3003 EXIT WHEN "done_v" = "dimension_v";
3004 "rank_v" := "rank_v" + 1;
3005 END LOOP;
3006 -- write preliminary ranks:
3007 "i" := 1;
3008 FOR "initiative_id_v" IN
3009 SELECT "id" FROM "initiative"
3010 WHERE "issue_id" = "issue_id_p" AND "agreed"
3011 ORDER BY "id"
3012 LOOP
3013 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
3014 WHERE "id" = "initiative_id_v";
3015 "i" := "i" + 1;
3016 END LOOP;
3017 IF "i" != "dimension_v" + 1 THEN
3018 RAISE EXCEPTION 'Wrong winner count (should not happen)';
3019 END IF;
3020 -- straighten ranks (start counting with 1, no equal ranks):
3021 "rank_v" := 1;
3022 FOR "initiative_id_v" IN
3023 SELECT "id" FROM "initiative"
3024 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
3025 ORDER BY
3026 "rank",
3027 "vote_ratio"("positive_votes", "negative_votes") DESC,
3028 "id"
3029 LOOP
3030 UPDATE "initiative" SET "rank" = "rank_v"
3031 WHERE "id" = "initiative_id_v";
3032 "rank_v" := "rank_v" + 1;
3033 END LOOP;
3034 END IF;
3035 -- mark issue as finished
3036 UPDATE "issue" SET "ranks_available" = TRUE
3037 WHERE "id" = "issue_id_p";
3038 RETURN;
3039 END;
3040 $$;
3042 COMMENT ON FUNCTION "calculate_ranks"
3043 ( "issue"."id"%TYPE )
3044 IS 'Determine ranking (Votes have to be counted first)';
3048 -----------------------------
3049 -- Automatic state changes --
3050 -----------------------------
3053 CREATE FUNCTION "check_issue"
3054 ( "issue_id_p" "issue"."id"%TYPE )
3055 RETURNS VOID
3056 LANGUAGE 'plpgsql' VOLATILE AS $$
3057 DECLARE
3058 "issue_row" "issue"%ROWTYPE;
3059 "policy_row" "policy"%ROWTYPE;
3060 "voting_requested_v" BOOLEAN;
3061 BEGIN
3062 PERFORM "global_lock"();
3063 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3064 -- only process open issues:
3065 IF "issue_row"."closed" ISNULL THEN
3066 SELECT * INTO "policy_row" FROM "policy"
3067 WHERE "id" = "issue_row"."policy_id";
3068 -- create a snapshot, unless issue is already fully frozen:
3069 IF "issue_row"."fully_frozen" ISNULL THEN
3070 PERFORM "create_snapshot"("issue_id_p");
3071 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3072 END IF;
3073 -- eventually close or accept issues, which have not been accepted:
3074 IF "issue_row"."accepted" ISNULL THEN
3075 IF EXISTS (
3076 SELECT NULL FROM "initiative"
3077 WHERE "issue_id" = "issue_id_p"
3078 AND "supporter_count" > 0
3079 AND "supporter_count" * "policy_row"."issue_quorum_den"
3080 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
3081 ) THEN
3082 -- accept issues, if supporter count is high enough
3083 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3084 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later
3085 UPDATE "issue" SET "accepted" = "issue_row"."accepted"
3086 WHERE "id" = "issue_row"."id";
3087 ELSIF
3088 now() >= "issue_row"."created" + "issue_row"."admission_time"
3089 THEN
3090 -- close issues, if admission time has expired
3091 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3092 UPDATE "issue" SET "closed" = now()
3093 WHERE "id" = "issue_row"."id";
3094 END IF;
3095 END IF;
3096 -- eventually half freeze issues:
3097 IF
3098 -- NOTE: issue can't be closed at this point, if it has been accepted
3099 "issue_row"."accepted" NOTNULL AND
3100 "issue_row"."half_frozen" ISNULL
3101 THEN
3102 SELECT
3103 CASE
3104 WHEN "vote_now" * 2 > "issue_row"."population" THEN
3105 TRUE
3106 WHEN "vote_later" * 2 > "issue_row"."population" THEN
3107 FALSE
3108 ELSE NULL
3109 END
3110 INTO "voting_requested_v"
3111 FROM "issue" WHERE "id" = "issue_id_p";
3112 IF
3113 "voting_requested_v" OR (
3114 "voting_requested_v" ISNULL AND
3115 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
3116 )
3117 THEN
3118 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
3119 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later
3120 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
3121 WHERE "id" = "issue_row"."id";
3122 END IF;
3123 END IF;
3124 -- close issues after some time, if all initiatives have been revoked:
3125 IF
3126 "issue_row"."closed" ISNULL AND
3127 NOT EXISTS (
3128 -- all initiatives are revoked
3129 SELECT NULL FROM "initiative"
3130 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3131 ) AND (
3132 NOT EXISTS (
3133 -- and no initiatives have been revoked lately
3134 SELECT NULL FROM "initiative"
3135 WHERE "issue_id" = "issue_id_p"
3136 AND now() < "revoked" + "issue_row"."verification_time"
3137 ) OR (
3138 -- or verification time has elapsed
3139 "issue_row"."half_frozen" NOTNULL AND
3140 "issue_row"."fully_frozen" ISNULL AND
3141 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3142 )
3143 )
3144 THEN
3145 "issue_row"."closed" = now(); -- NOTE: "issue_row" used later
3146 UPDATE "issue" SET "closed" = "issue_row"."closed"
3147 WHERE "id" = "issue_row"."id";
3148 END IF;
3149 -- fully freeze issue after verification time:
3150 IF
3151 "issue_row"."half_frozen" NOTNULL AND
3152 "issue_row"."fully_frozen" ISNULL AND
3153 "issue_row"."closed" ISNULL AND
3154 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3155 THEN
3156 PERFORM "freeze_after_snapshot"("issue_id_p");
3157 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
3158 END IF;
3159 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3160 -- close issue by calling close_voting(...) after voting time:
3161 IF
3162 "issue_row"."closed" ISNULL AND
3163 "issue_row"."fully_frozen" NOTNULL AND
3164 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
3165 THEN
3166 PERFORM "close_voting"("issue_id_p");
3167 END IF;
3168 END IF;
3169 RETURN;
3170 END;
3171 $$;
3173 COMMENT ON FUNCTION "check_issue"
3174 ( "issue"."id"%TYPE )
3175 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.';
3178 CREATE FUNCTION "check_everything"()
3179 RETURNS VOID
3180 LANGUAGE 'plpgsql' VOLATILE AS $$
3181 DECLARE
3182 "issue_id_v" "issue"."id"%TYPE;
3183 BEGIN
3184 DELETE FROM "expired_session";
3185 PERFORM "calculate_member_counts"();
3186 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
3187 PERFORM "check_issue"("issue_id_v");
3188 END LOOP;
3189 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
3190 PERFORM "calculate_ranks"("issue_id_v");
3191 END LOOP;
3192 RETURN;
3193 END;
3194 $$;
3196 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.';
3200 ----------------------
3201 -- Deletion of data --
3202 ----------------------
3205 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
3206 RETURNS VOID
3207 LANGUAGE 'plpgsql' VOLATILE AS $$
3208 DECLARE
3209 "issue_row" "issue"%ROWTYPE;
3210 BEGIN
3211 SELECT * INTO "issue_row"
3212 FROM "issue" WHERE "id" = "issue_id_p"
3213 FOR UPDATE;
3214 IF "issue_row"."cleaned" ISNULL THEN
3215 UPDATE "issue" SET
3216 "closed" = NULL,
3217 "ranks_available" = FALSE
3218 WHERE "id" = "issue_id_p";
3219 DELETE FROM "delegating_voter"
3220 WHERE "issue_id" = "issue_id_p";
3221 DELETE FROM "direct_voter"
3222 WHERE "issue_id" = "issue_id_p";
3223 DELETE FROM "delegating_interest_snapshot"
3224 WHERE "issue_id" = "issue_id_p";
3225 DELETE FROM "direct_interest_snapshot"
3226 WHERE "issue_id" = "issue_id_p";
3227 DELETE FROM "delegating_population_snapshot"
3228 WHERE "issue_id" = "issue_id_p";
3229 DELETE FROM "direct_population_snapshot"
3230 WHERE "issue_id" = "issue_id_p";
3231 DELETE FROM "delegation"
3232 WHERE "issue_id" = "issue_id_p";
3233 DELETE FROM "supporter"
3234 WHERE "issue_id" = "issue_id_p";
3235 UPDATE "issue" SET
3236 "closed" = "issue_row"."closed",
3237 "ranks_available" = "issue_row"."ranks_available",
3238 "cleaned" = now()
3239 WHERE "id" = "issue_id_p";
3240 END IF;
3241 RETURN;
3242 END;
3243 $$;
3245 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
3248 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
3249 RETURNS VOID
3250 LANGUAGE 'plpgsql' VOLATILE AS $$
3251 BEGIN
3252 UPDATE "member" SET
3253 "last_login" = NULL,
3254 "login" = NULL,
3255 "password" = NULL,
3256 "active" = FALSE,
3257 "notify_email" = NULL,
3258 "notify_email_unconfirmed" = NULL,
3259 "notify_email_secret" = NULL,
3260 "notify_email_secret_expiry" = NULL,
3261 "notify_email_lock_expiry" = NULL,
3262 "password_reset_secret" = NULL,
3263 "password_reset_secret_expiry" = NULL,
3264 "organizational_unit" = NULL,
3265 "internal_posts" = NULL,
3266 "realname" = NULL,
3267 "birthday" = NULL,
3268 "address" = NULL,
3269 "email" = NULL,
3270 "xmpp_address" = NULL,
3271 "website" = NULL,
3272 "phone" = NULL,
3273 "mobile_phone" = NULL,
3274 "profession" = NULL,
3275 "external_memberships" = NULL,
3276 "external_posts" = NULL,
3277 "statement" = NULL
3278 WHERE "id" = "member_id_p";
3279 -- "text_search_data" is updated by triggers
3280 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
3281 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
3282 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
3283 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
3284 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
3285 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
3286 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
3287 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
3288 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
3289 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
3290 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
3291 DELETE FROM "direct_voter" USING "issue"
3292 WHERE "direct_voter"."issue_id" = "issue"."id"
3293 AND "issue"."closed" ISNULL
3294 AND "member_id" = "member_id_p";
3295 RETURN;
3296 END;
3297 $$;
3299 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)';
3302 CREATE FUNCTION "delete_private_data"()
3303 RETURNS VOID
3304 LANGUAGE 'plpgsql' VOLATILE AS $$
3305 BEGIN
3306 UPDATE "member" SET
3307 "last_login" = NULL,
3308 "login" = NULL,
3309 "password" = NULL,
3310 "notify_email" = NULL,
3311 "notify_email_unconfirmed" = NULL,
3312 "notify_email_secret" = NULL,
3313 "notify_email_secret_expiry" = NULL,
3314 "notify_email_lock_expiry" = NULL,
3315 "password_reset_secret" = NULL,
3316 "password_reset_secret_expiry" = NULL,
3317 "organizational_unit" = NULL,
3318 "internal_posts" = NULL,
3319 "realname" = NULL,
3320 "birthday" = NULL,
3321 "address" = NULL,
3322 "email" = NULL,
3323 "xmpp_address" = NULL,
3324 "website" = NULL,
3325 "phone" = NULL,
3326 "mobile_phone" = NULL,
3327 "profession" = NULL,
3328 "external_memberships" = NULL,
3329 "external_posts" = NULL,
3330 "statement" = NULL;
3331 -- "text_search_data" is updated by triggers
3332 DELETE FROM "invite_code";
3333 DELETE FROM "setting";
3334 DELETE FROM "setting_map";
3335 DELETE FROM "member_relation_setting";
3336 DELETE FROM "member_image";
3337 DELETE FROM "contact";
3338 DELETE FROM "session";
3339 DELETE FROM "area_setting";
3340 DELETE FROM "issue_setting";
3341 DELETE FROM "initiative_setting";
3342 DELETE FROM "suggestion_setting";
3343 DELETE FROM "direct_voter" USING "issue"
3344 WHERE "direct_voter"."issue_id" = "issue"."id"
3345 AND "issue"."closed" ISNULL;
3346 RETURN;
3347 END;
3348 $$;
3350 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.';
3354 COMMIT;