liquid_feedback_core
view core.sql @ 31:6d634325a604
Added tag beta20 for changeset d386cd067983
| author | jbe |
|---|---|
| date | Sat Feb 20 15:09:32 2010 +0100 (2010-02-20) |
| parents | d386cd067983 |
| children | 3ccab7349f28 |
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 ('beta20', NULL, NULL, NULL))
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 "login" TEXT NOT NULL UNIQUE,
60 "password" TEXT,
61 "active" BOOLEAN NOT NULL DEFAULT TRUE,
62 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
63 "notify_email" TEXT,
64 "notify_email_unconfirmed" TEXT,
65 "notify_email_secret" TEXT UNIQUE,
66 "notify_email_secret_expiry" TIMESTAMPTZ,
67 "password_reset_secret" TEXT UNIQUE,
68 "password_reset_secret_expiry" TIMESTAMPTZ,
69 "name" TEXT NOT NULL UNIQUE,
70 "identification" TEXT UNIQUE,
71 "organizational_unit" TEXT,
72 "internal_posts" TEXT,
73 "realname" TEXT,
74 "birthday" DATE,
75 "address" TEXT,
76 "email" TEXT,
77 "xmpp_address" TEXT,
78 "website" TEXT,
79 "phone" TEXT,
80 "mobile_phone" TEXT,
81 "profession" TEXT,
82 "external_memberships" TEXT,
83 "external_posts" TEXT,
84 "statement" TEXT,
85 "text_search_data" TSVECTOR );
86 CREATE INDEX "member_active_idx" ON "member" ("active");
87 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
88 CREATE TRIGGER "update_text_search_data"
89 BEFORE INSERT OR UPDATE ON "member"
90 FOR EACH ROW EXECUTE PROCEDURE
91 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
92 "name", "identification", "organizational_unit", "internal_posts",
93 "realname", "external_memberships", "external_posts", "statement" );
95 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
97 COMMENT ON COLUMN "member"."login" IS 'Login name';
98 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
99 COMMENT ON COLUMN "member"."active" IS 'Inactive members can not login and their supports/votes are not counted by the system.';
100 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
101 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
102 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
103 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
104 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
105 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member';
106 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
107 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
108 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
109 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
110 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
111 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
112 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
113 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his homepage within the system';
116 CREATE TABLE "member_history" (
117 "id" SERIAL8 PRIMARY KEY,
118 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
119 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
120 "login" TEXT NOT NULL,
121 "name" TEXT NOT NULL );
123 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and login names of members';
125 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
126 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the name and login had been valid';
129 CREATE TABLE "invite_code" (
130 "code" TEXT PRIMARY KEY,
131 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
132 "used" TIMESTAMPTZ,
133 "member_id" INT4 UNIQUE REFERENCES "member" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
134 "comment" TEXT,
135 CONSTRAINT "only_used_codes_may_refer_to_member" CHECK ("used" NOTNULL OR "member_id" ISNULL) );
137 COMMENT ON TABLE "invite_code" IS 'Invite codes can be used once to create a new member account.';
139 COMMENT ON COLUMN "invite_code"."code" IS 'Secret code';
140 COMMENT ON COLUMN "invite_code"."created" IS 'Time of creation of the secret code';
141 COMMENT ON COLUMN "invite_code"."used" IS 'NULL, if not used yet, otherwise tells when this code was used to create a member account';
142 COMMENT ON COLUMN "invite_code"."member_id" IS 'References the member whose account was created with this code';
143 COMMENT ON COLUMN "invite_code"."comment" IS 'Comment on the code, which is to be used for administrative reasons only';
146 CREATE TABLE "setting" (
147 PRIMARY KEY ("member_id", "key"),
148 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
149 "key" TEXT NOT NULL,
150 "value" TEXT NOT NULL );
151 CREATE INDEX "setting_key_idx" ON "setting" ("key");
153 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific settings for members as a string';
155 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
158 CREATE TABLE "setting_map" (
159 PRIMARY KEY ("member_id", "key", "subkey"),
160 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
161 "key" TEXT NOT NULL,
162 "subkey" TEXT NOT NULL,
163 "value" TEXT NOT NULL );
164 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
166 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
168 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
169 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
170 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
173 CREATE TABLE "member_relation_setting" (
174 PRIMARY KEY ("member_id", "key", "other_member_id"),
175 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
176 "key" TEXT NOT NULL,
177 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
178 "value" TEXT NOT NULL );
180 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific settings related to relations between members as a string';
183 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
185 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
188 CREATE TABLE "member_image" (
189 PRIMARY KEY ("member_id", "image_type", "scaled"),
190 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
191 "image_type" "member_image_type",
192 "scaled" BOOLEAN,
193 "content_type" TEXT,
194 "data" BYTEA NOT NULL );
196 COMMENT ON TABLE "member_image" IS 'Images of members';
198 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
201 CREATE TABLE "member_count" (
202 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
203 "total_count" INT4 NOT NULL );
205 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';
207 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
208 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
211 CREATE TABLE "contact" (
212 PRIMARY KEY ("member_id", "other_member_id"),
213 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
214 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
215 "public" BOOLEAN NOT NULL DEFAULT FALSE,
216 CONSTRAINT "cant_save_yourself_as_contact"
217 CHECK ("member_id" != "other_member_id") );
219 COMMENT ON TABLE "contact" IS 'Contact lists';
221 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
222 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
223 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
226 CREATE TABLE "session" (
227 "ident" TEXT PRIMARY KEY,
228 "additional_secret" TEXT,
229 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
230 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
231 "lang" TEXT );
232 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
234 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';
236 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
237 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
238 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
239 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
242 CREATE TABLE "policy" (
243 "id" SERIAL4 PRIMARY KEY,
244 "index" INT4 NOT NULL,
245 "active" BOOLEAN NOT NULL DEFAULT TRUE,
246 "name" TEXT NOT NULL UNIQUE,
247 "description" TEXT NOT NULL DEFAULT '',
248 "admission_time" INTERVAL NOT NULL,
249 "discussion_time" INTERVAL NOT NULL,
250 "verification_time" INTERVAL NOT NULL,
251 "voting_time" INTERVAL NOT NULL,
252 "issue_quorum_num" INT4 NOT NULL,
253 "issue_quorum_den" INT4 NOT NULL,
254 "initiative_quorum_num" INT4 NOT NULL,
255 "initiative_quorum_den" INT4 NOT NULL,
256 "majority_num" INT4 NOT NULL DEFAULT 1,
257 "majority_den" INT4 NOT NULL DEFAULT 2,
258 "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE );
259 CREATE INDEX "policy_active_idx" ON "policy" ("active");
261 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
263 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
264 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
265 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"';
266 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"';
267 COMMENT ON COLUMN "policy"."verification_time" IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
268 COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "fully_frozen" but not "closed"';
269 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"';
270 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"';
271 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
272 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
273 COMMENT ON COLUMN "policy"."majority_num" IS 'Numerator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
274 COMMENT ON COLUMN "policy"."majority_den" IS 'Denominator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
275 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.';
278 CREATE TABLE "area" (
279 "id" SERIAL4 PRIMARY KEY,
280 "active" BOOLEAN NOT NULL DEFAULT TRUE,
281 "name" TEXT NOT NULL,
282 "description" TEXT NOT NULL DEFAULT '',
283 "direct_member_count" INT4,
284 "member_weight" INT4,
285 "autoreject_weight" INT4,
286 "text_search_data" TSVECTOR );
287 CREATE INDEX "area_active_idx" ON "area" ("active");
288 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
289 CREATE TRIGGER "update_text_search_data"
290 BEFORE INSERT OR UPDATE ON "area"
291 FOR EACH ROW EXECUTE PROCEDURE
292 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
293 "name", "description" );
295 COMMENT ON TABLE "area" IS 'Subject areas';
297 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
298 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"';
299 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
300 COMMENT ON COLUMN "area"."autoreject_weight" IS 'Sum of weight of members using the autoreject feature';
303 CREATE TABLE "area_setting" (
304 PRIMARY KEY ("member_id", "key", "area_id"),
305 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
306 "key" TEXT NOT NULL,
307 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
308 "value" TEXT NOT NULL );
310 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
313 CREATE TABLE "allowed_policy" (
314 PRIMARY KEY ("area_id", "policy_id"),
315 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
316 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
317 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
318 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
320 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
322 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
325 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
327 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';
330 CREATE TABLE "issue" (
331 "id" SERIAL4 PRIMARY KEY,
332 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
333 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
334 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
335 "accepted" TIMESTAMPTZ,
336 "half_frozen" TIMESTAMPTZ,
337 "fully_frozen" TIMESTAMPTZ,
338 "closed" TIMESTAMPTZ,
339 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
340 "admission_time" INTERVAL NOT NULL,
341 "discussion_time" INTERVAL NOT NULL,
342 "verification_time" INTERVAL NOT NULL,
343 "voting_time" INTERVAL NOT NULL,
344 "snapshot" TIMESTAMPTZ,
345 "latest_snapshot_event" "snapshot_event",
346 "population" INT4,
347 "vote_now" INT4,
348 "vote_later" INT4,
349 "voter_count" INT4,
350 CONSTRAINT "valid_state" CHECK (
351 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
352 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
353 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
354 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
355 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
356 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
357 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ),
358 CONSTRAINT "state_change_order" CHECK (
359 "created" <= "accepted" AND
360 "accepted" <= "half_frozen" AND
361 "half_frozen" <= "fully_frozen" AND
362 "fully_frozen" <= "closed" ),
363 CONSTRAINT "last_snapshot_on_full_freeze"
364 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
365 CONSTRAINT "freeze_requires_snapshot"
366 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
367 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
368 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
369 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
370 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
371 CREATE INDEX "issue_created_idx" ON "issue" ("created");
372 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
373 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
374 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
375 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
376 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
377 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
379 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
381 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
382 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.';
383 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.';
384 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.';
385 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
386 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
387 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
388 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
389 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
390 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';
391 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';
392 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
393 COMMENT ON COLUMN "issue"."vote_now" IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"';
394 COMMENT ON COLUMN "issue"."vote_later" IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"';
395 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';
398 CREATE TABLE "issue_setting" (
399 PRIMARY KEY ("member_id", "key", "issue_id"),
400 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
401 "key" TEXT NOT NULL,
402 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
403 "value" TEXT NOT NULL );
405 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
408 CREATE TABLE "initiative" (
409 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
410 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
411 "id" SERIAL4 PRIMARY KEY,
412 "name" TEXT NOT NULL,
413 "discussion_url" TEXT,
414 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
415 "revoked" TIMESTAMPTZ,
416 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
417 "admitted" BOOLEAN,
418 "supporter_count" INT4,
419 "informed_supporter_count" INT4,
420 "satisfied_supporter_count" INT4,
421 "satisfied_informed_supporter_count" INT4,
422 "positive_votes" INT4,
423 "negative_votes" INT4,
424 "agreed" BOOLEAN,
425 "rank" INT4,
426 "text_search_data" TSVECTOR,
427 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
428 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
429 CONSTRAINT "revoked_initiatives_cant_be_admitted"
430 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
431 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"
432 CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "agreed" ISNULL)),
433 CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"
434 CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL),
435 CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"
436 CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) );
437 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
438 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
439 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
440 CREATE TRIGGER "update_text_search_data"
441 BEFORE INSERT OR UPDATE ON "initiative"
442 FOR EACH ROW EXECUTE PROCEDURE
443 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
444 "name", "discussion_url");
446 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.';
448 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
449 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
450 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
451 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
452 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
453 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
454 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
455 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
456 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
457 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"';
458 COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';
461 CREATE TABLE "initiative_setting" (
462 PRIMARY KEY ("member_id", "key", "initiative_id"),
463 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
464 "key" TEXT NOT NULL,
465 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
466 "value" TEXT NOT NULL );
468 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
471 CREATE TABLE "draft" (
472 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
473 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
474 "id" SERIAL8 PRIMARY KEY,
475 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
476 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
477 "formatting_engine" TEXT,
478 "content" TEXT NOT NULL,
479 "text_search_data" TSVECTOR );
480 CREATE INDEX "draft_created_idx" ON "draft" ("created");
481 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
482 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
483 CREATE TRIGGER "update_text_search_data"
484 BEFORE INSERT OR UPDATE ON "draft"
485 FOR EACH ROW EXECUTE PROCEDURE
486 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
488 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.';
490 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
491 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
494 CREATE TABLE "suggestion" (
495 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
496 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
497 "id" SERIAL8 PRIMARY KEY,
498 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
499 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
500 "name" TEXT NOT NULL,
501 "description" TEXT NOT NULL DEFAULT '',
502 "text_search_data" TSVECTOR,
503 "minus2_unfulfilled_count" INT4,
504 "minus2_fulfilled_count" INT4,
505 "minus1_unfulfilled_count" INT4,
506 "minus1_fulfilled_count" INT4,
507 "plus1_unfulfilled_count" INT4,
508 "plus1_fulfilled_count" INT4,
509 "plus2_unfulfilled_count" INT4,
510 "plus2_fulfilled_count" INT4 );
511 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
512 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
513 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
514 CREATE TRIGGER "update_text_search_data"
515 BEFORE INSERT OR UPDATE ON "suggestion"
516 FOR EACH ROW EXECUTE PROCEDURE
517 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
518 "name", "description");
520 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';
522 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
523 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
524 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
525 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
526 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
527 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
528 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
529 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
532 CREATE TABLE "suggestion_setting" (
533 PRIMARY KEY ("member_id", "key", "suggestion_id"),
534 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
535 "key" TEXT NOT NULL,
536 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
537 "value" TEXT NOT NULL );
539 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
542 CREATE TABLE "membership" (
543 PRIMARY KEY ("area_id", "member_id"),
544 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
545 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
546 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
547 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
549 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
551 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';
554 CREATE TABLE "interest" (
555 PRIMARY KEY ("issue_id", "member_id"),
556 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
557 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
558 "autoreject" BOOLEAN NOT NULL,
559 "voting_requested" BOOLEAN );
560 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
562 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.';
564 COMMENT ON COLUMN "interest"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure';
565 COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply';
568 CREATE TABLE "initiator" (
569 PRIMARY KEY ("initiative_id", "member_id"),
570 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
571 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
572 "accepted" BOOLEAN );
573 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
575 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.';
577 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.';
580 CREATE TABLE "supporter" (
581 "issue_id" INT4 NOT NULL,
582 PRIMARY KEY ("initiative_id", "member_id"),
583 "initiative_id" INT4,
584 "member_id" INT4,
585 "draft_id" INT8 NOT NULL,
586 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
587 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
588 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
590 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.';
592 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
595 CREATE TABLE "opinion" (
596 "initiative_id" INT4 NOT NULL,
597 PRIMARY KEY ("suggestion_id", "member_id"),
598 "suggestion_id" INT8,
599 "member_id" INT4,
600 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
601 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
602 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE,
603 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
604 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
606 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.';
608 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
611 CREATE TYPE "delegation_scope" AS ENUM ('global', 'area', 'issue');
613 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''global'', ''area'', or ''issue'' (order is relevant)';
616 CREATE TABLE "delegation" (
617 "id" SERIAL8 PRIMARY KEY,
618 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
619 "trustee_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
620 "scope" "delegation_scope" NOT NULL,
621 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
622 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
623 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
624 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
625 ("scope" = 'global' AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
626 ("scope" = 'area' AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
627 ("scope" = 'issue' AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
628 UNIQUE ("area_id", "truster_id", "trustee_id"),
629 UNIQUE ("issue_id", "truster_id", "trustee_id") );
630 CREATE UNIQUE INDEX "delegation_global_truster_id_trustee_id_unique_idx"
631 ON "delegation" ("truster_id", "trustee_id") WHERE "scope" = 'global';
632 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
633 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
635 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
637 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
638 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
641 CREATE TABLE "direct_population_snapshot" (
642 PRIMARY KEY ("issue_id", "event", "member_id"),
643 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
644 "event" "snapshot_event",
645 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
646 "weight" INT4,
647 "interest_exists" BOOLEAN NOT NULL );
648 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
650 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
652 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
653 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
654 COMMENT ON COLUMN "direct_population_snapshot"."interest_exists" IS 'TRUE if entry is due to interest in issue, FALSE if entry is only due to membership in area';
657 CREATE TABLE "delegating_population_snapshot" (
658 PRIMARY KEY ("issue_id", "event", "member_id"),
659 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
660 "event" "snapshot_event",
661 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
662 "weight" INT4,
663 "scope" "delegation_scope" NOT NULL,
664 "delegate_member_ids" INT4[] NOT NULL );
665 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
667 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
669 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
670 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
671 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
672 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"';
675 CREATE TABLE "direct_interest_snapshot" (
676 PRIMARY KEY ("issue_id", "event", "member_id"),
677 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
678 "event" "snapshot_event",
679 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
680 "weight" INT4,
681 "voting_requested" BOOLEAN );
682 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
684 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
686 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
687 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
688 COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';
691 CREATE TABLE "delegating_interest_snapshot" (
692 PRIMARY KEY ("issue_id", "event", "member_id"),
693 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
694 "event" "snapshot_event",
695 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
696 "weight" INT4,
697 "scope" "delegation_scope" NOT NULL,
698 "delegate_member_ids" INT4[] NOT NULL );
699 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
701 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
703 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
704 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
705 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
706 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"';
709 CREATE TABLE "direct_supporter_snapshot" (
710 "issue_id" INT4 NOT NULL,
711 PRIMARY KEY ("initiative_id", "event", "member_id"),
712 "initiative_id" INT4,
713 "event" "snapshot_event",
714 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
715 "informed" BOOLEAN NOT NULL,
716 "satisfied" BOOLEAN NOT NULL,
717 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
718 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
719 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
721 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
723 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
724 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
725 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
728 CREATE TABLE "direct_voter" (
729 PRIMARY KEY ("issue_id", "member_id"),
730 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
731 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
732 "weight" INT4,
733 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
734 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
736 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.';
738 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
739 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
742 CREATE TABLE "delegating_voter" (
743 PRIMARY KEY ("issue_id", "member_id"),
744 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
745 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
746 "weight" INT4,
747 "scope" "delegation_scope" NOT NULL,
748 "delegate_member_ids" INT4[] NOT NULL );
749 CREATE INDEX "delegating_voter_member_id_idx" ON "direct_voter" ("member_id");
751 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
753 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
754 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
755 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"';
758 CREATE TABLE "vote" (
759 "issue_id" INT4 NOT NULL,
760 PRIMARY KEY ("initiative_id", "member_id"),
761 "initiative_id" INT4,
762 "member_id" INT4,
763 "grade" INT4,
764 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
765 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
766 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
768 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.';
770 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.';
773 CREATE TABLE "contingent" (
774 "time_frame" INTERVAL PRIMARY KEY,
775 "text_entry_limit" INT4,
776 "initiative_limit" INT4 );
778 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.';
780 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';
781 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
785 --------------------------------
786 -- Writing of history entries --
787 --------------------------------
789 CREATE FUNCTION "write_member_history_trigger"()
790 RETURNS TRIGGER
791 LANGUAGE 'plpgsql' VOLATILE AS $$
792 BEGIN
793 IF NEW."login" != OLD."login" OR NEW."name" != OLD."name" THEN
794 INSERT INTO "member_history" ("member_id", "login", "name")
795 VALUES (NEW."id", OLD."login", OLD."name");
796 END IF;
797 RETURN NULL;
798 END;
799 $$;
801 CREATE TRIGGER "write_member_history"
802 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
803 "write_member_history_trigger"();
805 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
806 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing name or login of a member, create a history entry in "member_history" table';
810 ----------------------------
811 -- Additional constraints --
812 ----------------------------
815 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
816 RETURNS TRIGGER
817 LANGUAGE 'plpgsql' VOLATILE AS $$
818 BEGIN
819 IF NOT EXISTS (
820 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
821 ) THEN
822 --RAISE 'Cannot create issue without an initial initiative.' USING
823 -- ERRCODE = 'integrity_constraint_violation',
824 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
825 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
826 END IF;
827 RETURN NULL;
828 END;
829 $$;
831 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
832 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
833 FOR EACH ROW EXECUTE PROCEDURE
834 "issue_requires_first_initiative_trigger"();
836 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
837 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
840 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
841 RETURNS TRIGGER
842 LANGUAGE 'plpgsql' VOLATILE AS $$
843 DECLARE
844 "reference_lost" BOOLEAN;
845 BEGIN
846 IF TG_OP = 'DELETE' THEN
847 "reference_lost" := TRUE;
848 ELSE
849 "reference_lost" := NEW."issue_id" != OLD."issue_id";
850 END IF;
851 IF
852 "reference_lost" AND NOT EXISTS (
853 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
854 )
855 THEN
856 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
857 END IF;
858 RETURN NULL;
859 END;
860 $$;
862 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
863 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
864 FOR EACH ROW EXECUTE PROCEDURE
865 "last_initiative_deletes_issue_trigger"();
867 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
868 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
871 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
872 RETURNS TRIGGER
873 LANGUAGE 'plpgsql' VOLATILE AS $$
874 BEGIN
875 IF NOT EXISTS (
876 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
877 ) THEN
878 --RAISE 'Cannot create initiative without an initial draft.' USING
879 -- ERRCODE = 'integrity_constraint_violation',
880 -- HINT = 'Create issue, initiative and draft within the same transaction.';
881 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
882 END IF;
883 RETURN NULL;
884 END;
885 $$;
887 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
888 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
889 FOR EACH ROW EXECUTE PROCEDURE
890 "initiative_requires_first_draft_trigger"();
892 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
893 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
896 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
897 RETURNS TRIGGER
898 LANGUAGE 'plpgsql' VOLATILE AS $$
899 DECLARE
900 "reference_lost" BOOLEAN;
901 BEGIN
902 IF TG_OP = 'DELETE' THEN
903 "reference_lost" := TRUE;
904 ELSE
905 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
906 END IF;
907 IF
908 "reference_lost" AND NOT EXISTS (
909 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
910 )
911 THEN
912 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
913 END IF;
914 RETURN NULL;
915 END;
916 $$;
918 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
919 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
920 FOR EACH ROW EXECUTE PROCEDURE
921 "last_draft_deletes_initiative_trigger"();
923 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
924 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
927 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
928 RETURNS TRIGGER
929 LANGUAGE 'plpgsql' VOLATILE AS $$
930 BEGIN
931 IF NOT EXISTS (
932 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
933 ) THEN
934 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
935 END IF;
936 RETURN NULL;
937 END;
938 $$;
940 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
941 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
942 FOR EACH ROW EXECUTE PROCEDURE
943 "suggestion_requires_first_opinion_trigger"();
945 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
946 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
949 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
950 RETURNS TRIGGER
951 LANGUAGE 'plpgsql' VOLATILE AS $$
952 DECLARE
953 "reference_lost" BOOLEAN;
954 BEGIN
955 IF TG_OP = 'DELETE' THEN
956 "reference_lost" := TRUE;
957 ELSE
958 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
959 END IF;
960 IF
961 "reference_lost" AND NOT EXISTS (
962 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
963 )
964 THEN
965 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
966 END IF;
967 RETURN NULL;
968 END;
969 $$;
971 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
972 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
973 FOR EACH ROW EXECUTE PROCEDURE
974 "last_opinion_deletes_suggestion_trigger"();
976 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
977 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
981 ---------------------------------------------------------------
982 -- Ensure that votes are not modified when issues are frozen --
983 ---------------------------------------------------------------
985 -- NOTE: Frontends should ensure this anyway, but in case of programming
986 -- errors the following triggers ensure data integrity.
989 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
990 RETURNS TRIGGER
991 LANGUAGE 'plpgsql' VOLATILE AS $$
992 DECLARE
993 "issue_row" "issue"%ROWTYPE;
994 BEGIN
995 SELECT INTO "issue_row" * FROM "issue"
996 WHERE "id" = NEW."issue_id" FOR SHARE;
997 IF "issue_row"."closed" NOTNULL THEN
998 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
999 END IF;
1000 RETURN NULL;
1001 END;
1002 $$;
1004 CREATE TRIGGER "forbid_changes_on_closed_issue"
1005 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1006 FOR EACH ROW EXECUTE PROCEDURE
1007 "forbid_changes_on_closed_issue_trigger"();
1009 CREATE TRIGGER "forbid_changes_on_closed_issue"
1010 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1011 FOR EACH ROW EXECUTE PROCEDURE
1012 "forbid_changes_on_closed_issue_trigger"();
1014 CREATE TRIGGER "forbid_changes_on_closed_issue"
1015 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1016 FOR EACH ROW EXECUTE PROCEDURE
1017 "forbid_changes_on_closed_issue_trigger"();
1019 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"';
1020 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';
1021 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';
1022 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';
1026 --------------------------------------------------------------------
1027 -- Auto-retrieval of fields only needed for referential integrity --
1028 --------------------------------------------------------------------
1031 CREATE FUNCTION "autofill_issue_id_trigger"()
1032 RETURNS TRIGGER
1033 LANGUAGE 'plpgsql' VOLATILE AS $$
1034 BEGIN
1035 IF NEW."issue_id" ISNULL THEN
1036 SELECT "issue_id" INTO NEW."issue_id"
1037 FROM "initiative" WHERE "id" = NEW."initiative_id";
1038 END IF;
1039 RETURN NEW;
1040 END;
1041 $$;
1043 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1044 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1046 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1047 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1049 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1050 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1051 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1054 CREATE FUNCTION "autofill_initiative_id_trigger"()
1055 RETURNS TRIGGER
1056 LANGUAGE 'plpgsql' VOLATILE AS $$
1057 BEGIN
1058 IF NEW."initiative_id" ISNULL THEN
1059 SELECT "initiative_id" INTO NEW."initiative_id"
1060 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1061 END IF;
1062 RETURN NEW;
1063 END;
1064 $$;
1066 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1067 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1069 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1070 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1074 -----------------------------------------------------
1075 -- Automatic calculation of certain default values --
1076 -----------------------------------------------------
1079 CREATE FUNCTION "copy_timings_trigger"()
1080 RETURNS TRIGGER
1081 LANGUAGE 'plpgsql' VOLATILE AS $$
1082 DECLARE
1083 "policy_row" "policy"%ROWTYPE;
1084 BEGIN
1085 SELECT * INTO "policy_row" FROM "policy"
1086 WHERE "id" = NEW."policy_id";
1087 IF NEW."admission_time" ISNULL THEN
1088 NEW."admission_time" := "policy_row"."admission_time";
1089 END IF;
1090 IF NEW."discussion_time" ISNULL THEN
1091 NEW."discussion_time" := "policy_row"."discussion_time";
1092 END IF;
1093 IF NEW."verification_time" ISNULL THEN
1094 NEW."verification_time" := "policy_row"."verification_time";
1095 END IF;
1096 IF NEW."voting_time" ISNULL THEN
1097 NEW."voting_time" := "policy_row"."voting_time";
1098 END IF;
1099 RETURN NEW;
1100 END;
1101 $$;
1103 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1104 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1106 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1107 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1110 CREATE FUNCTION "copy_autoreject_trigger"()
1111 RETURNS TRIGGER
1112 LANGUAGE 'plpgsql' VOLATILE AS $$
1113 BEGIN
1114 IF NEW."autoreject" ISNULL THEN
1115 SELECT "membership"."autoreject" INTO NEW."autoreject"
1116 FROM "issue" JOIN "membership"
1117 ON "issue"."area_id" = "membership"."area_id"
1118 WHERE "issue"."id" = NEW."issue_id"
1119 AND "membership"."member_id" = NEW."member_id";
1120 END IF;
1121 IF NEW."autoreject" ISNULL THEN
1122 NEW."autoreject" := FALSE;
1123 END IF;
1124 RETURN NEW;
1125 END;
1126 $$;
1128 CREATE TRIGGER "copy_autoreject" BEFORE INSERT OR UPDATE ON "interest"
1129 FOR EACH ROW EXECUTE PROCEDURE "copy_autoreject_trigger"();
1131 COMMENT ON FUNCTION "copy_autoreject_trigger"() IS 'Implementation of trigger "copy_autoreject" on table "interest"';
1132 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';
1135 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
1136 RETURNS TRIGGER
1137 LANGUAGE 'plpgsql' VOLATILE AS $$
1138 BEGIN
1139 IF NEW."draft_id" ISNULL THEN
1140 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1141 WHERE "initiative_id" = NEW."initiative_id";
1142 END IF;
1143 RETURN NEW;
1144 END;
1145 $$;
1147 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1148 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
1150 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
1151 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';
1155 ----------------------------------------
1156 -- Automatic creation of dependencies --
1157 ----------------------------------------
1160 CREATE FUNCTION "autocreate_interest_trigger"()
1161 RETURNS TRIGGER
1162 LANGUAGE 'plpgsql' VOLATILE AS $$
1163 BEGIN
1164 IF NOT EXISTS (
1165 SELECT NULL FROM "initiative" JOIN "interest"
1166 ON "initiative"."issue_id" = "interest"."issue_id"
1167 WHERE "initiative"."id" = NEW."initiative_id"
1168 AND "interest"."member_id" = NEW."member_id"
1169 ) THEN
1170 BEGIN
1171 INSERT INTO "interest" ("issue_id", "member_id")
1172 SELECT "issue_id", NEW."member_id"
1173 FROM "initiative" WHERE "id" = NEW."initiative_id";
1174 EXCEPTION WHEN unique_violation THEN END;
1175 END IF;
1176 RETURN NEW;
1177 END;
1178 $$;
1180 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1181 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1183 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1184 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';
1187 CREATE FUNCTION "autocreate_supporter_trigger"()
1188 RETURNS TRIGGER
1189 LANGUAGE 'plpgsql' VOLATILE AS $$
1190 BEGIN
1191 IF NOT EXISTS (
1192 SELECT NULL FROM "suggestion" JOIN "supporter"
1193 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1194 WHERE "suggestion"."id" = NEW."suggestion_id"
1195 AND "supporter"."member_id" = NEW."member_id"
1196 ) THEN
1197 BEGIN
1198 INSERT INTO "supporter" ("initiative_id", "member_id")
1199 SELECT "initiative_id", NEW."member_id"
1200 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1201 EXCEPTION WHEN unique_violation THEN END;
1202 END IF;
1203 RETURN NEW;
1204 END;
1205 $$;
1207 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1208 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1210 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1211 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.';
1215 ------------------------------------------
1216 -- Views and helper functions for views --
1217 ------------------------------------------
1220 CREATE VIEW "global_delegation" AS
1221 SELECT
1222 "delegation"."id",
1223 "delegation"."truster_id",
1224 "delegation"."trustee_id"
1225 FROM "delegation" JOIN "member"
1226 ON "delegation"."trustee_id" = "member"."id"
1227 WHERE "delegation"."scope" = 'global' AND "member"."active";
1229 COMMENT ON VIEW "global_delegation" IS 'Global delegations to active members';
1232 CREATE VIEW "area_delegation" AS
1233 SELECT "subquery".* FROM (
1234 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1235 "area"."id" AS "area_id",
1236 "delegation"."id",
1237 "delegation"."truster_id",
1238 "delegation"."trustee_id",
1239 "delegation"."scope"
1240 FROM "area" JOIN "delegation"
1241 ON "delegation"."scope" = 'global'
1242 OR "delegation"."area_id" = "area"."id"
1243 ORDER BY
1244 "area"."id",
1245 "delegation"."truster_id",
1246 "delegation"."scope" DESC
1247 ) AS "subquery"
1248 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
1249 WHERE "member"."active";
1251 COMMENT ON VIEW "area_delegation" IS 'Active delegations for areas';
1254 CREATE VIEW "issue_delegation" AS
1255 SELECT "subquery".* FROM (
1256 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1257 "issue"."id" AS "issue_id",
1258 "delegation"."id",
1259 "delegation"."truster_id",
1260 "delegation"."trustee_id",
1261 "delegation"."scope"
1262 FROM "issue" JOIN "delegation"
1263 ON "delegation"."scope" = 'global'
1264 OR "delegation"."area_id" = "issue"."area_id"
1265 OR "delegation"."issue_id" = "issue"."id"
1266 ORDER BY
1267 "issue"."id",
1268 "delegation"."truster_id",
1269 "delegation"."scope" DESC
1270 ) AS "subquery"
1271 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
1272 WHERE "member"."active";
1274 COMMENT ON VIEW "issue_delegation" IS 'Active delegations for issues';
1277 CREATE FUNCTION "membership_weight_with_skipping"
1278 ( "area_id_p" "area"."id"%TYPE,
1279 "member_id_p" "member"."id"%TYPE,
1280 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1281 RETURNS INT4
1282 LANGUAGE 'plpgsql' STABLE AS $$
1283 DECLARE
1284 "sum_v" INT4;
1285 "delegation_row" "area_delegation"%ROWTYPE;
1286 BEGIN
1287 "sum_v" := 1;
1288 FOR "delegation_row" IN
1289 SELECT "area_delegation".*
1290 FROM "area_delegation" LEFT JOIN "membership"
1291 ON "membership"."area_id" = "area_id_p"
1292 AND "membership"."member_id" = "area_delegation"."truster_id"
1293 WHERE "area_delegation"."area_id" = "area_id_p"
1294 AND "area_delegation"."trustee_id" = "member_id_p"
1295 AND "membership"."member_id" ISNULL
1296 LOOP
1297 IF NOT
1298 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1299 THEN
1300 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1301 "area_id_p",
1302 "delegation_row"."truster_id",
1303 "skip_member_ids_p" || "delegation_row"."truster_id"
1304 );
1305 END IF;
1306 END LOOP;
1307 RETURN "sum_v";
1308 END;
1309 $$;
1311 COMMENT ON FUNCTION "membership_weight_with_skipping"
1312 ( "area"."id"%TYPE,
1313 "member"."id"%TYPE,
1314 INT4[] )
1315 IS 'Helper function for "membership_weight" function';
1318 CREATE FUNCTION "membership_weight"
1319 ( "area_id_p" "area"."id"%TYPE,
1320 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1321 RETURNS INT4
1322 LANGUAGE 'plpgsql' STABLE AS $$
1323 BEGIN
1324 RETURN "membership_weight_with_skipping"(
1325 "area_id_p",
1326 "member_id_p",
1327 ARRAY["member_id_p"]
1328 );
1329 END;
1330 $$;
1332 COMMENT ON FUNCTION "membership_weight"
1333 ( "area"."id"%TYPE,
1334 "member"."id"%TYPE )
1335 IS 'Calculates the potential voting weight of a member in a given area';
1338 CREATE VIEW "member_count_view" AS
1339 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1341 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1344 CREATE VIEW "area_member_count" AS
1345 SELECT
1346 "area"."id" AS "area_id",
1347 count("member"."id") AS "direct_member_count",
1348 coalesce(
1349 sum(
1350 CASE WHEN "member"."id" NOTNULL THEN
1351 "membership_weight"("area"."id", "member"."id")
1352 ELSE 0 END
1353 )
1354 ) AS "member_weight",
1355 coalesce(
1356 sum(
1357 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
1358 "membership_weight"("area"."id", "member"."id")
1359 ELSE 0 END
1360 )
1361 ) AS "autoreject_weight"
1362 FROM "area"
1363 LEFT JOIN "membership"
1364 ON "area"."id" = "membership"."area_id"
1365 LEFT JOIN "member"
1366 ON "membership"."member_id" = "member"."id"
1367 AND "member"."active"
1368 GROUP BY "area"."id";
1370 COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"';
1373 CREATE VIEW "opening_draft" AS
1374 SELECT "draft".* FROM (
1375 SELECT
1376 "initiative"."id" AS "initiative_id",
1377 min("draft"."id") AS "draft_id"
1378 FROM "initiative" JOIN "draft"
1379 ON "initiative"."id" = "draft"."initiative_id"
1380 GROUP BY "initiative"."id"
1381 ) AS "subquery"
1382 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1384 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1387 CREATE VIEW "current_draft" AS
1388 SELECT "draft".* FROM (
1389 SELECT
1390 "initiative"."id" AS "initiative_id",
1391 max("draft"."id") AS "draft_id"
1392 FROM "initiative" JOIN "draft"
1393 ON "initiative"."id" = "draft"."initiative_id"
1394 GROUP BY "initiative"."id"
1395 ) AS "subquery"
1396 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1398 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1401 CREATE VIEW "critical_opinion" AS
1402 SELECT * FROM "opinion"
1403 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
1404 OR ("degree" = -2 AND "fulfilled" = TRUE);
1406 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
1409 CREATE VIEW "battle" AS
1410 SELECT
1411 "issue"."id" AS "issue_id",
1412 "winning_initiative"."id" AS "winning_initiative_id",
1413 "losing_initiative"."id" AS "losing_initiative_id",
1414 sum(
1415 CASE WHEN
1416 coalesce("better_vote"."grade", 0) >
1417 coalesce("worse_vote"."grade", 0)
1418 THEN "direct_voter"."weight" ELSE 0 END
1419 ) AS "count"
1420 FROM "issue"
1421 LEFT JOIN "direct_voter"
1422 ON "issue"."id" = "direct_voter"."issue_id"
1423 JOIN "initiative" AS "winning_initiative"
1424 ON "issue"."id" = "winning_initiative"."issue_id"
1425 AND "winning_initiative"."agreed"
1426 JOIN "initiative" AS "losing_initiative"
1427 ON "issue"."id" = "losing_initiative"."issue_id"
1428 AND "losing_initiative"."agreed"
1429 LEFT JOIN "vote" AS "better_vote"
1430 ON "direct_voter"."member_id" = "better_vote"."member_id"
1431 AND "winning_initiative"."id" = "better_vote"."initiative_id"
1432 LEFT JOIN "vote" AS "worse_vote"
1433 ON "direct_voter"."member_id" = "worse_vote"."member_id"
1434 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
1435 WHERE
1436 "winning_initiative"."id" != "losing_initiative"."id"
1437 GROUP BY
1438 "issue"."id",
1439 "winning_initiative"."id",
1440 "losing_initiative"."id";
1442 COMMENT ON VIEW "battle" IS 'Number of members preferring one initiative over another';
1445 CREATE VIEW "expired_session" AS
1446 SELECT * FROM "session" WHERE now() > "expiry";
1448 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
1449 DELETE FROM "session" WHERE "ident" = OLD."ident";
1451 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
1452 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
1455 CREATE VIEW "open_issue" AS
1456 SELECT * FROM "issue" WHERE "closed" ISNULL;
1458 COMMENT ON VIEW "open_issue" IS 'All open issues';
1461 CREATE VIEW "issue_with_ranks_missing" AS
1462 SELECT * FROM "issue"
1463 WHERE "fully_frozen" NOTNULL
1464 AND "closed" NOTNULL
1465 AND "ranks_available" = FALSE;
1467 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
1470 CREATE VIEW "member_contingent" AS
1471 SELECT
1472 "member"."id" AS "member_id",
1473 "contingent"."time_frame",
1474 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
1475 (
1476 SELECT count(1) FROM "draft"
1477 WHERE "draft"."author_id" = "member"."id"
1478 AND "draft"."created" > now() - "contingent"."time_frame"
1479 ) + (
1480 SELECT count(1) FROM "suggestion"
1481 WHERE "suggestion"."author_id" = "member"."id"
1482 AND "suggestion"."created" > now() - "contingent"."time_frame"
1483 )
1484 ELSE NULL END AS "text_entry_count",
1485 "contingent"."text_entry_limit",
1486 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
1487 SELECT count(1) FROM "opening_draft"
1488 WHERE "opening_draft"."author_id" = "member"."id"
1489 AND "opening_draft"."created" > now() - "contingent"."time_frame"
1490 ) ELSE NULL END AS "initiative_count",
1491 "contingent"."initiative_limit"
1492 FROM "member" CROSS JOIN "contingent";
1494 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
1496 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
1497 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
1500 CREATE VIEW "member_contingent_left" AS
1501 SELECT
1502 "member_id",
1503 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
1504 max("initiative_limit" - "initiative_count") AS "initiatives_left"
1505 FROM "member_contingent" GROUP BY "member_id";
1507 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.';
1510 CREATE TYPE "timeline_event" AS ENUM (
1511 'issue_created',
1512 'issue_canceled',
1513 'issue_accepted',
1514 'issue_half_frozen',
1515 'issue_finished_without_voting',
1516 'issue_voting_started',
1517 'issue_finished_after_voting',
1518 'initiative_created',
1519 'initiative_revoked',
1520 'draft_created',
1521 'suggestion_created');
1523 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables';
1526 CREATE VIEW "timeline_issue" AS
1527 SELECT
1528 "created" AS "occurrence",
1529 'issue_created'::"timeline_event" AS "event",
1530 "id" AS "issue_id"
1531 FROM "issue"
1532 UNION ALL
1533 SELECT
1534 "closed" AS "occurrence",
1535 'issue_canceled'::"timeline_event" AS "event",
1536 "id" AS "issue_id"
1537 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
1538 UNION ALL
1539 SELECT
1540 "accepted" AS "occurrence",
1541 'issue_accepted'::"timeline_event" AS "event",
1542 "id" AS "issue_id"
1543 FROM "issue" WHERE "accepted" NOTNULL
1544 UNION ALL
1545 SELECT
1546 "half_frozen" AS "occurrence",
1547 'issue_half_frozen'::"timeline_event" AS "event",
1548 "id" AS "issue_id"
1549 FROM "issue" WHERE "half_frozen" NOTNULL
1550 UNION ALL
1551 SELECT
1552 "fully_frozen" AS "occurrence",
1553 'issue_voting_started'::"timeline_event" AS "event",
1554 "id" AS "issue_id"
1555 FROM "issue"
1556 WHERE "fully_frozen" NOTNULL
1557 AND ("closed" ISNULL OR "closed" != "fully_frozen")
1558 UNION ALL
1559 SELECT
1560 "closed" AS "occurrence",
1561 CASE WHEN "fully_frozen" = "closed" THEN
1562 'issue_finished_without_voting'::"timeline_event"
1563 ELSE
1564 'issue_finished_after_voting'::"timeline_event"
1565 END AS "event",
1566 "id" AS "issue_id"
1567 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
1569 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view';
1572 CREATE VIEW "timeline_initiative" AS
1573 SELECT
1574 "created" AS "occurrence",
1575 'initiative_created'::"timeline_event" AS "event",
1576 "id" AS "initiative_id"
1577 FROM "initiative"
1578 UNION ALL
1579 SELECT
1580 "revoked" AS "occurrence",
1581 'initiative_revoked'::"timeline_event" AS "event",
1582 "id" AS "initiative_id"
1583 FROM "initiative" WHERE "revoked" NOTNULL;
1585 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view';
1588 CREATE VIEW "timeline_draft" AS
1589 SELECT
1590 "created" AS "occurrence",
1591 'draft_created'::"timeline_event" AS "event",
1592 "id" AS "draft_id"
1593 FROM "draft";
1595 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view';
1598 CREATE VIEW "timeline_suggestion" AS
1599 SELECT
1600 "created" AS "occurrence",
1601 'suggestion_created'::"timeline_event" AS "event",
1602 "id" AS "suggestion_id"
1603 FROM "suggestion";
1605 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view';
1608 CREATE VIEW "timeline" AS
1609 SELECT
1610 "occurrence",
1611 "event",
1612 "issue_id",
1613 NULL AS "initiative_id",
1614 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
1615 NULL::INT8 AS "suggestion_id"
1616 FROM "timeline_issue"
1617 UNION ALL
1618 SELECT
1619 "occurrence",
1620 "event",
1621 NULL AS "issue_id",
1622 "initiative_id",
1623 NULL AS "draft_id",
1624 NULL AS "suggestion_id"
1625 FROM "timeline_initiative"
1626 UNION ALL
1627 SELECT
1628 "occurrence",
1629 "event",
1630 NULL AS "issue_id",
1631 NULL AS "initiative_id",
1632 "draft_id",
1633 NULL AS "suggestion_id"
1634 FROM "timeline_draft"
1635 UNION ALL
1636 SELECT
1637 "occurrence",
1638 "event",
1639 NULL AS "issue_id",
1640 NULL AS "initiative_id",
1641 NULL AS "draft_id",
1642 "suggestion_id"
1643 FROM "timeline_suggestion";
1645 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system';
1649 --------------------------------------------------
1650 -- Set returning function for delegation chains --
1651 --------------------------------------------------
1654 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
1655 ('first', 'intermediate', 'last', 'repetition');
1657 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
1660 CREATE TYPE "delegation_chain_row" AS (
1661 "index" INT4,
1662 "member_id" INT4,
1663 "member_active" BOOLEAN,
1664 "participation" BOOLEAN,
1665 "overridden" BOOLEAN,
1666 "scope_in" "delegation_scope",
1667 "scope_out" "delegation_scope",
1668 "loop" "delegation_chain_loop_tag" );
1670 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
1672 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
1673 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';
1674 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
1675 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
1676 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
1677 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
1680 CREATE FUNCTION "delegation_chain"
1681 ( "member_id_p" "member"."id"%TYPE,
1682 "area_id_p" "area"."id"%TYPE,
1683 "issue_id_p" "issue"."id"%TYPE,
1684 "simulate_trustee_id_p" "member"."id"%TYPE )
1685 RETURNS SETOF "delegation_chain_row"
1686 LANGUAGE 'plpgsql' STABLE AS $$
1687 DECLARE
1688 "issue_row" "issue"%ROWTYPE;
1689 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
1690 "loop_member_id_v" "member"."id"%TYPE;
1691 "output_row" "delegation_chain_row";
1692 "output_rows" "delegation_chain_row"[];
1693 "delegation_row" "delegation"%ROWTYPE;
1694 "row_count" INT4;
1695 "i" INT4;
1696 "loop_v" BOOLEAN;
1697 BEGIN
1698 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1699 "visited_member_ids" := '{}';
1700 "loop_member_id_v" := NULL;
1701 "output_rows" := '{}';
1702 "output_row"."index" := 0;
1703 "output_row"."member_id" := "member_id_p";
1704 "output_row"."member_active" := TRUE;
1705 "output_row"."participation" := FALSE;
1706 "output_row"."overridden" := FALSE;
1707 "output_row"."scope_out" := NULL;
1708 LOOP
1709 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
1710 "loop_member_id_v" := "output_row"."member_id";
1711 ELSE
1712 "visited_member_ids" :=
1713 "visited_member_ids" || "output_row"."member_id";
1714 END IF;
1715 IF "output_row"."participation" THEN
1716 "output_row"."overridden" := TRUE;
1717 END IF;
1718 "output_row"."scope_in" := "output_row"."scope_out";
1719 IF EXISTS (
1720 SELECT NULL FROM "member"
1721 WHERE "id" = "output_row"."member_id" AND "active"
1722 ) THEN
1723 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
1724 SELECT * INTO "delegation_row" FROM "delegation"
1725 WHERE "truster_id" = "output_row"."member_id"
1726 AND "scope" = 'global';
1727 ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN
1728 "output_row"."participation" := EXISTS (
1729 SELECT NULL FROM "membership"
1730 WHERE "area_id" = "area_id_p"
1731 AND "member_id" = "output_row"."member_id"
1732 );
1733 SELECT * INTO "delegation_row" FROM "delegation"
1734 WHERE "truster_id" = "output_row"."member_id"
1735 AND ("scope" = 'global' OR "area_id" = "area_id_p")
1736 ORDER BY "scope" DESC;
1737 ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN
1738 "output_row"."participation" := EXISTS (
1739 SELECT NULL FROM "interest"
1740 WHERE "issue_id" = "issue_id_p"
1741 AND "member_id" = "output_row"."member_id"
1742 );
1743 SELECT * INTO "delegation_row" FROM "delegation"
1744 WHERE "truster_id" = "output_row"."member_id"
1745 AND ("scope" = 'global' OR
1746 "area_id" = "issue_row"."area_id" OR
1747 "issue_id" = "issue_id_p"
1748 )
1749 ORDER BY "scope" DESC;
1750 ELSE
1751 RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.';
1752 END IF;
1753 ELSE
1754 "output_row"."member_active" := FALSE;
1755 "output_row"."participation" := FALSE;
1756 "output_row"."scope_out" := NULL;
1757 "delegation_row" := ROW(NULL);
1758 END IF;
1759 IF
1760 "output_row"."member_id" = "member_id_p" AND
1761 "simulate_trustee_id_p" NOTNULL
1762 THEN
1763 "output_row"."scope_out" := CASE
1764 WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global'
1765 WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area'
1766 WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue'
1767 END;
1768 "output_rows" := "output_rows" || "output_row";
1769 "output_row"."member_id" := "simulate_trustee_id_p";
1770 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
1771 "output_row"."scope_out" := "delegation_row"."scope";
1772 "output_rows" := "output_rows" || "output_row";
1773 "output_row"."member_id" := "delegation_row"."trustee_id";
1774 ELSE
1775 "output_row"."scope_out" := NULL;
1776 "output_rows" := "output_rows" || "output_row";
1777 EXIT;
1778 END IF;
1779 EXIT WHEN "loop_member_id_v" NOTNULL;
1780 "output_row"."index" := "output_row"."index" + 1;
1781 END LOOP;
1782 "row_count" := array_upper("output_rows", 1);
1783 "i" := 1;
1784 "loop_v" := FALSE;
1785 LOOP
1786 "output_row" := "output_rows"["i"];
1787 EXIT WHEN "output_row"."member_id" ISNULL;
1788 IF "loop_v" THEN
1789 IF "i" + 1 = "row_count" THEN
1790 "output_row"."loop" := 'last';
1791 ELSIF "i" = "row_count" THEN
1792 "output_row"."loop" := 'repetition';
1793 ELSE
1794 "output_row"."loop" := 'intermediate';
1795 END IF;
1796 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
1797 "output_row"."loop" := 'first';
1798 "loop_v" := TRUE;
1799 END IF;
1800 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
1801 "output_row"."participation" := NULL;
1802 END IF;
1803 RETURN NEXT "output_row";
1804 "i" := "i" + 1;
1805 END LOOP;
1806 RETURN;
1807 END;
1808 $$;
1810 COMMENT ON FUNCTION "delegation_chain"
1811 ( "member"."id"%TYPE,
1812 "area"."id"%TYPE,
1813 "issue"."id"%TYPE,
1814 "member"."id"%TYPE )
1815 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
1817 CREATE FUNCTION "delegation_chain"
1818 ( "member_id_p" "member"."id"%TYPE,
1819 "area_id_p" "area"."id"%TYPE,
1820 "issue_id_p" "issue"."id"%TYPE )
1821 RETURNS SETOF "delegation_chain_row"
1822 LANGUAGE 'plpgsql' STABLE AS $$
1823 DECLARE
1824 "result_row" "delegation_chain_row";
1825 BEGIN
1826 FOR "result_row" IN
1827 SELECT * FROM "delegation_chain"(
1828 "member_id_p", "area_id_p", "issue_id_p", NULL
1829 )
1830 LOOP
1831 RETURN NEXT "result_row";
1832 END LOOP;
1833 RETURN;
1834 END;
1835 $$;
1837 COMMENT ON FUNCTION "delegation_chain"
1838 ( "member"."id"%TYPE,
1839 "area"."id"%TYPE,
1840 "issue"."id"%TYPE )
1841 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
1845 ------------------------------
1846 -- Comparison by vote count --
1847 ------------------------------
1849 CREATE FUNCTION "vote_ratio"
1850 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
1851 "negative_votes_p" "initiative"."negative_votes"%TYPE )
1852 RETURNS FLOAT8
1853 LANGUAGE 'plpgsql' STABLE AS $$
1854 BEGIN
1855 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
1856 RETURN
1857 "positive_votes_p"::FLOAT8 /
1858 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
1859 ELSIF "positive_votes_p" > 0 THEN
1860 RETURN "positive_votes_p";
1861 ELSIF "negative_votes_p" > 0 THEN
1862 RETURN 1 - "negative_votes_p";
1863 ELSE
1864 RETURN 0.5;
1865 END IF;
1866 END;
1867 $$;
1869 COMMENT ON FUNCTION "vote_ratio"
1870 ( "initiative"."positive_votes"%TYPE,
1871 "initiative"."negative_votes"%TYPE )
1872 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.';
1876 ------------------------------------------------
1877 -- Locking for snapshots and voting procedure --
1878 ------------------------------------------------
1880 CREATE FUNCTION "global_lock"() RETURNS VOID
1881 LANGUAGE 'plpgsql' VOLATILE AS $$
1882 BEGIN
1883 -- NOTE: PostgreSQL allows reading, while tables are locked in
1884 -- exclusive move. Transactions should be kept short anyway!
1885 LOCK TABLE "member" IN EXCLUSIVE MODE;
1886 LOCK TABLE "area" IN EXCLUSIVE MODE;
1887 LOCK TABLE "membership" IN EXCLUSIVE MODE;
1888 -- NOTE: "member", "area" and "membership" are locked first to
1889 -- prevent deadlocks in combination with "calculate_member_counts"()
1890 LOCK TABLE "policy" IN EXCLUSIVE MODE;
1891 LOCK TABLE "issue" IN EXCLUSIVE MODE;
1892 LOCK TABLE "initiative" IN EXCLUSIVE MODE;
1893 LOCK TABLE "draft" IN EXCLUSIVE MODE;
1894 LOCK TABLE "suggestion" IN EXCLUSIVE MODE;
1895 LOCK TABLE "interest" IN EXCLUSIVE MODE;
1896 LOCK TABLE "initiator" IN EXCLUSIVE MODE;
1897 LOCK TABLE "supporter" IN EXCLUSIVE MODE;
1898 LOCK TABLE "opinion" IN EXCLUSIVE MODE;
1899 LOCK TABLE "delegation" IN EXCLUSIVE MODE;
1900 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
1901 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
1902 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
1903 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
1904 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
1905 LOCK TABLE "direct_voter" IN EXCLUSIVE MODE;
1906 LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;
1907 LOCK TABLE "vote" IN EXCLUSIVE MODE;
1908 RETURN;
1909 END;
1910 $$;
1912 COMMENT ON FUNCTION "global_lock"() IS 'Locks all tables related to support/voting until end of transaction; read access is still possible though';
1916 -------------------------------
1917 -- Materialize member counts --
1918 -------------------------------
1920 CREATE FUNCTION "calculate_member_counts"()
1921 RETURNS VOID
1922 LANGUAGE 'plpgsql' VOLATILE AS $$
1923 BEGIN
1924 LOCK TABLE "member" IN EXCLUSIVE MODE;
1925 LOCK TABLE "area" IN EXCLUSIVE MODE;
1926 LOCK TABLE "membership" IN EXCLUSIVE MODE;
1927 DELETE FROM "member_count";
1928 INSERT INTO "member_count" ("total_count")
1929 SELECT "total_count" FROM "member_count_view";
1930 UPDATE "area" SET
1931 "direct_member_count" = "view"."direct_member_count",
1932 "member_weight" = "view"."member_weight",
1933 "autoreject_weight" = "view"."autoreject_weight"
1934 FROM "area_member_count" AS "view"
1935 WHERE "view"."area_id" = "area"."id";
1936 RETURN;
1937 END;
1938 $$;
1940 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"';
1944 ------------------------------
1945 -- Calculation of snapshots --
1946 ------------------------------
1948 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
1949 ( "issue_id_p" "issue"."id"%TYPE,
1950 "member_id_p" "member"."id"%TYPE,
1951 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
1952 RETURNS "direct_population_snapshot"."weight"%TYPE
1953 LANGUAGE 'plpgsql' VOLATILE AS $$
1954 DECLARE
1955 "issue_delegation_row" "issue_delegation"%ROWTYPE;
1956 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
1957 "weight_v" INT4;
1958 "sub_weight_v" INT4;
1959 BEGIN
1960 "weight_v" := 0;
1961 FOR "issue_delegation_row" IN
1962 SELECT * FROM "issue_delegation"
1963 WHERE "trustee_id" = "member_id_p"
1964 AND "issue_id" = "issue_id_p"
1965 LOOP
1966 IF NOT EXISTS (
1967 SELECT NULL FROM "direct_population_snapshot"
1968 WHERE "issue_id" = "issue_id_p"
1969 AND "event" = 'periodic'
1970 AND "member_id" = "issue_delegation_row"."truster_id"
1971 ) AND NOT EXISTS (
1972 SELECT NULL FROM "delegating_population_snapshot"
1973 WHERE "issue_id" = "issue_id_p"
1974 AND "event" = 'periodic'
1975 AND "member_id" = "issue_delegation_row"."truster_id"
1976 ) THEN
1977 "delegate_member_ids_v" :=
1978 "member_id_p" || "delegate_member_ids_p";
1979 INSERT INTO "delegating_population_snapshot" (
1980 "issue_id",
1981 "event",
1982 "member_id",
1983 "scope",
1984 "delegate_member_ids"
1985 ) VALUES (
1986 "issue_id_p",
1987 'periodic',
1988 "issue_delegation_row"."truster_id",
1989 "issue_delegation_row"."scope",
1990 "delegate_member_ids_v"
1991 );
1992 "sub_weight_v" := 1 +
1993 "weight_of_added_delegations_for_population_snapshot"(
1994 "issue_id_p",
1995 "issue_delegation_row"."truster_id",
1996 "delegate_member_ids_v"
1997 );
1998 UPDATE "delegating_population_snapshot"
1999 SET "weight" = "sub_weight_v"
2000 WHERE "issue_id" = "issue_id_p"
2001 AND "event" = 'periodic'
2002 AND "member_id" = "issue_delegation_row"."truster_id";
2003 "weight_v" := "weight_v" + "sub_weight_v";
2004 END IF;
2005 END LOOP;
2006 RETURN "weight_v";
2007 END;
2008 $$;
2010 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
2011 ( "issue"."id"%TYPE,
2012 "member"."id"%TYPE,
2013 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2014 IS 'Helper function for "create_population_snapshot" function';
2017 CREATE FUNCTION "create_population_snapshot"
2018 ( "issue_id_p" "issue"."id"%TYPE )
2019 RETURNS VOID
2020 LANGUAGE 'plpgsql' VOLATILE AS $$
2021 DECLARE
2022 "member_id_v" "member"."id"%TYPE;
2023 BEGIN
2024 DELETE FROM "direct_population_snapshot"
2025 WHERE "issue_id" = "issue_id_p"
2026 AND "event" = 'periodic';
2027 DELETE FROM "delegating_population_snapshot"
2028 WHERE "issue_id" = "issue_id_p"
2029 AND "event" = 'periodic';
2030 INSERT INTO "direct_population_snapshot"
2031 ("issue_id", "event", "member_id", "interest_exists")
2032 SELECT DISTINCT ON ("issue_id", "member_id")
2033 "issue_id_p" AS "issue_id",
2034 'periodic' AS "event",
2035 "subquery"."member_id",
2036 "subquery"."interest_exists"
2037 FROM (
2038 SELECT
2039 "member"."id" AS "member_id",
2040 FALSE AS "interest_exists"
2041 FROM "issue"
2042 JOIN "area" ON "issue"."area_id" = "area"."id"
2043 JOIN "membership" ON "area"."id" = "membership"."area_id"
2044 JOIN "member" ON "membership"."member_id" = "member"."id"
2045 WHERE "issue"."id" = "issue_id_p"
2046 AND "member"."active"
2047 UNION
2048 SELECT
2049 "member"."id" AS "member_id",
2050 TRUE AS "interest_exists"
2051 FROM "interest" JOIN "member"
2052 ON "interest"."member_id" = "member"."id"
2053 WHERE "interest"."issue_id" = "issue_id_p"
2054 AND "member"."active"
2055 ) AS "subquery"
2056 ORDER BY
2057 "issue_id_p",
2058 "subquery"."member_id",
2059 "subquery"."interest_exists" DESC;
2060 FOR "member_id_v" IN
2061 SELECT "member_id" FROM "direct_population_snapshot"
2062 WHERE "issue_id" = "issue_id_p"
2063 AND "event" = 'periodic'
2064 LOOP
2065 UPDATE "direct_population_snapshot" SET
2066 "weight" = 1 +
2067 "weight_of_added_delegations_for_population_snapshot"(
2068 "issue_id_p",
2069 "member_id_v",
2070 '{}'
2071 )
2072 WHERE "issue_id" = "issue_id_p"
2073 AND "event" = 'periodic'
2074 AND "member_id" = "member_id_v";
2075 END LOOP;
2076 RETURN;
2077 END;
2078 $$;
2080 COMMENT ON FUNCTION "create_population_snapshot"
2081 ( "issue_id_p" "issue"."id"%TYPE )
2082 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.';
2085 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2086 ( "issue_id_p" "issue"."id"%TYPE,
2087 "member_id_p" "member"."id"%TYPE,
2088 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2089 RETURNS "direct_interest_snapshot"."weight"%TYPE
2090 LANGUAGE 'plpgsql' VOLATILE AS $$
2091 DECLARE
2092 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2093 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2094 "weight_v" INT4;
2095 "sub_weight_v" INT4;
2096 BEGIN
2097 "weight_v" := 0;
2098 FOR "issue_delegation_row" IN
2099 SELECT * FROM "issue_delegation"
2100 WHERE "trustee_id" = "member_id_p"
2101 AND "issue_id" = "issue_id_p"
2102 LOOP
2103 IF NOT EXISTS (
2104 SELECT NULL FROM "direct_interest_snapshot"
2105 WHERE "issue_id" = "issue_id_p"
2106 AND "event" = 'periodic'
2107 AND "member_id" = "issue_delegation_row"."truster_id"
2108 ) AND NOT EXISTS (
2109 SELECT NULL FROM "delegating_interest_snapshot"
2110 WHERE "issue_id" = "issue_id_p"
2111 AND "event" = 'periodic'
2112 AND "member_id" = "issue_delegation_row"."truster_id"
2113 ) THEN
2114 "delegate_member_ids_v" :=
2115 "member_id_p" || "delegate_member_ids_p";
2116 INSERT INTO "delegating_interest_snapshot" (
2117 "issue_id",
2118 "event",
2119 "member_id",
2120 "scope",
2121 "delegate_member_ids"
2122 ) VALUES (
2123 "issue_id_p",
2124 'periodic',
2125 "issue_delegation_row"."truster_id",
2126 "issue_delegation_row"."scope",
2127 "delegate_member_ids_v"
2128 );
2129 "sub_weight_v" := 1 +
2130 "weight_of_added_delegations_for_interest_snapshot"(
2131 "issue_id_p",
2132 "issue_delegation_row"."truster_id",
2133 "delegate_member_ids_v"
2134 );
2135 UPDATE "delegating_interest_snapshot"
2136 SET "weight" = "sub_weight_v"
2137 WHERE "issue_id" = "issue_id_p"
2138 AND "event" = 'periodic'
2139 AND "member_id" = "issue_delegation_row"."truster_id";
2140 "weight_v" := "weight_v" + "sub_weight_v";
2141 END IF;
2142 END LOOP;
2143 RETURN "weight_v";
2144 END;
2145 $$;
2147 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2148 ( "issue"."id"%TYPE,
2149 "member"."id"%TYPE,
2150 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2151 IS 'Helper function for "create_interest_snapshot" function';
2154 CREATE FUNCTION "create_interest_snapshot"
2155 ( "issue_id_p" "issue"."id"%TYPE )
2156 RETURNS VOID
2157 LANGUAGE 'plpgsql' VOLATILE AS $$
2158 DECLARE
2159 "member_id_v" "member"."id"%TYPE;
2160 BEGIN
2161 DELETE FROM "direct_interest_snapshot"
2162 WHERE "issue_id" = "issue_id_p"
2163 AND "event" = 'periodic';
2164 DELETE FROM "delegating_interest_snapshot"
2165 WHERE "issue_id" = "issue_id_p"
2166 AND "event" = 'periodic';
2167 DELETE FROM "direct_supporter_snapshot"
2168 WHERE "issue_id" = "issue_id_p"
2169 AND "event" = 'periodic';
2170 INSERT INTO "direct_interest_snapshot"
2171 ("issue_id", "event", "member_id", "voting_requested")
2172 SELECT
2173 "issue_id_p" AS "issue_id",
2174 'periodic' AS "event",
2175 "member"."id" AS "member_id",
2176 "interest"."voting_requested"
2177 FROM "interest" JOIN "member"
2178 ON "interest"."member_id" = "member"."id"
2179 WHERE "interest"."issue_id" = "issue_id_p"
2180 AND "member"."active";
2181 FOR "member_id_v" IN
2182 SELECT "member_id" FROM "direct_interest_snapshot"
2183 WHERE "issue_id" = "issue_id_p"
2184 AND "event" = 'periodic'
2185 LOOP
2186 UPDATE "direct_interest_snapshot" SET
2187 "weight" = 1 +
2188 "weight_of_added_delegations_for_interest_snapshot"(
2189 "issue_id_p",
2190 "member_id_v",
2191 '{}'
2192 )
2193 WHERE "issue_id" = "issue_id_p"
2194 AND "event" = 'periodic'
2195 AND "member_id" = "member_id_v";
2196 END LOOP;
2197 INSERT INTO "direct_supporter_snapshot"
2198 ( "issue_id", "initiative_id", "event", "member_id",
2199 "informed", "satisfied" )
2200 SELECT
2201 "issue_id_p" AS "issue_id",
2202 "initiative"."id" AS "initiative_id",
2203 'periodic' AS "event",
2204 "member"."id" AS "member_id",
2205 "supporter"."draft_id" = "current_draft"."id" AS "informed",
2206 NOT EXISTS (
2207 SELECT NULL FROM "critical_opinion"
2208 WHERE "initiative_id" = "initiative"."id"
2209 AND "member_id" = "member"."id"
2210 ) AS "satisfied"
2211 FROM "supporter"
2212 JOIN "member"
2213 ON "supporter"."member_id" = "member"."id"
2214 JOIN "initiative"
2215 ON "supporter"."initiative_id" = "initiative"."id"
2216 JOIN "current_draft"
2217 ON "initiative"."id" = "current_draft"."initiative_id"
2218 JOIN "direct_interest_snapshot"
2219 ON "member"."id" = "direct_interest_snapshot"."member_id"
2220 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
2221 AND "event" = 'periodic'
2222 WHERE "member"."active"
2223 AND "initiative"."issue_id" = "issue_id_p";
2224 RETURN;
2225 END;
2226 $$;
2228 COMMENT ON FUNCTION "create_interest_snapshot"
2229 ( "issue"."id"%TYPE )
2230 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.';
2233 CREATE FUNCTION "create_snapshot"
2234 ( "issue_id_p" "issue"."id"%TYPE )
2235 RETURNS VOID
2236 LANGUAGE 'plpgsql' VOLATILE AS $$
2237 DECLARE
2238 "initiative_id_v" "initiative"."id"%TYPE;
2239 "suggestion_id_v" "suggestion"."id"%TYPE;
2240 BEGIN
2241 PERFORM "global_lock"();
2242 PERFORM "create_population_snapshot"("issue_id_p");
2243 PERFORM "create_interest_snapshot"("issue_id_p");
2244 UPDATE "issue" SET
2245 "snapshot" = now(),
2246 "latest_snapshot_event" = 'periodic',
2247 "population" = (
2248 SELECT coalesce(sum("weight"), 0)
2249 FROM "direct_population_snapshot"
2250 WHERE "issue_id" = "issue_id_p"
2251 AND "event" = 'periodic'
2252 ),
2253 "vote_now" = (
2254 SELECT coalesce(sum("weight"), 0)
2255 FROM "direct_interest_snapshot"
2256 WHERE "issue_id" = "issue_id_p"
2257 AND "event" = 'periodic'
2258 AND "voting_requested" = TRUE
2259 ),
2260 "vote_later" = (
2261 SELECT coalesce(sum("weight"), 0)
2262 FROM "direct_interest_snapshot"
2263 WHERE "issue_id" = "issue_id_p"
2264 AND "event" = 'periodic'
2265 AND "voting_requested" = FALSE
2266 )
2267 WHERE "id" = "issue_id_p";
2268 FOR "initiative_id_v" IN
2269 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
2270 LOOP
2271 UPDATE "initiative" SET
2272 "supporter_count" = (
2273 SELECT coalesce(sum("di"."weight"), 0)
2274 FROM "direct_interest_snapshot" AS "di"
2275 JOIN "direct_supporter_snapshot" AS "ds"
2276 ON "di"."member_id" = "ds"."member_id"
2277 WHERE "di"."issue_id" = "issue_id_p"
2278 AND "di"."event" = 'periodic'
2279 AND "ds"."initiative_id" = "initiative_id_v"
2280 AND "ds"."event" = 'periodic'
2281 ),
2282 "informed_supporter_count" = (
2283 SELECT coalesce(sum("di"."weight"), 0)
2284 FROM "direct_interest_snapshot" AS "di"
2285 JOIN "direct_supporter_snapshot" AS "ds"
2286 ON "di"."member_id" = "ds"."member_id"
2287 WHERE "di"."issue_id" = "issue_id_p"
2288 AND "di"."event" = 'periodic'
2289 AND "ds"."initiative_id" = "initiative_id_v"
2290 AND "ds"."event" = 'periodic'
2291 AND "ds"."informed"
2292 ),
2293 "satisfied_supporter_count" = (
2294 SELECT coalesce(sum("di"."weight"), 0)
2295 FROM "direct_interest_snapshot" AS "di"
2296 JOIN "direct_supporter_snapshot" AS "ds"
2297 ON "di"."member_id" = "ds"."member_id"
2298 WHERE "di"."issue_id" = "issue_id_p"
2299 AND "di"."event" = 'periodic'
2300 AND "ds"."initiative_id" = "initiative_id_v"
2301 AND "ds"."event" = 'periodic'
2302 AND "ds"."satisfied"
2303 ),
2304 "satisfied_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 AND "ds"."satisfied"
2315 )
2316 WHERE "id" = "initiative_id_v";
2317 FOR "suggestion_id_v" IN
2318 SELECT "id" FROM "suggestion"
2319 WHERE "initiative_id" = "initiative_id_v"
2320 LOOP
2321 UPDATE "suggestion" SET
2322 "minus2_unfulfilled_count" = (
2323 SELECT coalesce(sum("snapshot"."weight"), 0)
2324 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2325 ON "opinion"."member_id" = "snapshot"."member_id"
2326 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2327 AND "snapshot"."issue_id" = "issue_id_p"
2328 AND "opinion"."degree" = -2
2329 AND "opinion"."fulfilled" = FALSE
2330 ),
2331 "minus2_fulfilled_count" = (
2332 SELECT coalesce(sum("snapshot"."weight"), 0)
2333 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2334 ON "opinion"."member_id" = "snapshot"."member_id"
2335 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2336 AND "snapshot"."issue_id" = "issue_id_p"
2337 AND "opinion"."degree" = -2
2338 AND "opinion"."fulfilled" = TRUE
2339 ),
2340 "minus1_unfulfilled_count" = (
2341 SELECT coalesce(sum("snapshot"."weight"), 0)
2342 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2343 ON "opinion"."member_id" = "snapshot"."member_id"
2344 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2345 AND "snapshot"."issue_id" = "issue_id_p"
2346 AND "opinion"."degree" = -1
2347 AND "opinion"."fulfilled" = FALSE
2348 ),
2349 "minus1_fulfilled_count" = (
2350 SELECT coalesce(sum("snapshot"."weight"), 0)
2351 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2352 ON "opinion"."member_id" = "snapshot"."member_id"
2353 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2354 AND "snapshot"."issue_id" = "issue_id_p"
2355 AND "opinion"."degree" = -1
2356 AND "opinion"."fulfilled" = TRUE
2357 ),
2358 "plus1_unfulfilled_count" = (
2359 SELECT coalesce(sum("snapshot"."weight"), 0)
2360 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2361 ON "opinion"."member_id" = "snapshot"."member_id"
2362 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2363 AND "snapshot"."issue_id" = "issue_id_p"
2364 AND "opinion"."degree" = 1
2365 AND "opinion"."fulfilled" = FALSE
2366 ),
2367 "plus1_fulfilled_count" = (
2368 SELECT coalesce(sum("snapshot"."weight"), 0)
2369 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2370 ON "opinion"."member_id" = "snapshot"."member_id"
2371 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2372 AND "snapshot"."issue_id" = "issue_id_p"
2373 AND "opinion"."degree" = 1
2374 AND "opinion"."fulfilled" = TRUE
2375 ),
2376 "plus2_unfulfilled_count" = (
2377 SELECT coalesce(sum("snapshot"."weight"), 0)
2378 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2379 ON "opinion"."member_id" = "snapshot"."member_id"
2380 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2381 AND "snapshot"."issue_id" = "issue_id_p"
2382 AND "opinion"."degree" = 2
2383 AND "opinion"."fulfilled" = FALSE
2384 ),
2385 "plus2_fulfilled_count" = (
2386 SELECT coalesce(sum("snapshot"."weight"), 0)
2387 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2388 ON "opinion"."member_id" = "snapshot"."member_id"
2389 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2390 AND "snapshot"."issue_id" = "issue_id_p"
2391 AND "opinion"."degree" = 2
2392 AND "opinion"."fulfilled" = TRUE
2393 )
2394 WHERE "suggestion"."id" = "suggestion_id_v";
2395 END LOOP;
2396 END LOOP;
2397 RETURN;
2398 END;
2399 $$;
2401 COMMENT ON FUNCTION "create_snapshot"
2402 ( "issue"."id"%TYPE )
2403 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.';
2406 CREATE FUNCTION "set_snapshot_event"
2407 ( "issue_id_p" "issue"."id"%TYPE,
2408 "event_p" "snapshot_event" )
2409 RETURNS VOID
2410 LANGUAGE 'plpgsql' VOLATILE AS $$
2411 DECLARE
2412 "event_v" "issue"."latest_snapshot_event"%TYPE;
2413 BEGIN
2414 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
2415 WHERE "id" = "issue_id_p" FOR UPDATE;
2416 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
2417 WHERE "id" = "issue_id_p";
2418 UPDATE "direct_population_snapshot" SET "event" = "event_p"
2419 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2420 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
2421 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2422 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
2423 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2424 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
2425 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2426 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
2427 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2428 RETURN;
2429 END;
2430 $$;
2432 COMMENT ON FUNCTION "set_snapshot_event"
2433 ( "issue"."id"%TYPE,
2434 "snapshot_event" )
2435 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
2439 ---------------------
2440 -- Freezing issues --
2441 ---------------------
2443 CREATE FUNCTION "freeze_after_snapshot"
2444 ( "issue_id_p" "issue"."id"%TYPE )
2445 RETURNS VOID
2446 LANGUAGE 'plpgsql' VOLATILE AS $$
2447 DECLARE
2448 "issue_row" "issue"%ROWTYPE;
2449 "policy_row" "policy"%ROWTYPE;
2450 "initiative_row" "initiative"%ROWTYPE;
2451 BEGIN
2452 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2453 SELECT * INTO "policy_row"
2454 FROM "policy" WHERE "id" = "issue_row"."policy_id";
2455 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
2456 UPDATE "issue" SET
2457 "accepted" = coalesce("accepted", now()),
2458 "half_frozen" = coalesce("half_frozen", now()),
2459 "fully_frozen" = now()
2460 WHERE "id" = "issue_id_p";
2461 FOR "initiative_row" IN
2462 SELECT * FROM "initiative"
2463 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
2464 LOOP
2465 IF
2466 "initiative_row"."satisfied_supporter_count" > 0 AND
2467 "initiative_row"."satisfied_supporter_count" *
2468 "policy_row"."initiative_quorum_den" >=
2469 "issue_row"."population" * "policy_row"."initiative_quorum_num"
2470 THEN
2471 UPDATE "initiative" SET "admitted" = TRUE
2472 WHERE "id" = "initiative_row"."id";
2473 ELSE
2474 UPDATE "initiative" SET "admitted" = FALSE
2475 WHERE "id" = "initiative_row"."id";
2476 END IF;
2477 END LOOP;
2478 IF NOT EXISTS (
2479 SELECT NULL FROM "initiative"
2480 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
2481 ) THEN
2482 PERFORM "close_voting"("issue_id_p");
2483 END IF;
2484 RETURN;
2485 END;
2486 $$;
2488 COMMENT ON FUNCTION "freeze_after_snapshot"
2489 ( "issue"."id"%TYPE )
2490 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
2493 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
2494 RETURNS VOID
2495 LANGUAGE 'plpgsql' VOLATILE AS $$
2496 DECLARE
2497 "issue_row" "issue"%ROWTYPE;
2498 BEGIN
2499 PERFORM "create_snapshot"("issue_id_p");
2500 PERFORM "freeze_after_snapshot"("issue_id_p");
2501 RETURN;
2502 END;
2503 $$;
2505 COMMENT ON FUNCTION "freeze_after_snapshot"
2506 ( "issue"."id"%TYPE )
2507 IS 'Freeze an issue manually (fully) and start voting';
2511 -----------------------
2512 -- Counting of votes --
2513 -----------------------
2516 CREATE FUNCTION "weight_of_added_vote_delegations"
2517 ( "issue_id_p" "issue"."id"%TYPE,
2518 "member_id_p" "member"."id"%TYPE,
2519 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
2520 RETURNS "direct_voter"."weight"%TYPE
2521 LANGUAGE 'plpgsql' VOLATILE AS $$
2522 DECLARE
2523 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2524 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
2525 "weight_v" INT4;
2526 "sub_weight_v" INT4;
2527 BEGIN
2528 "weight_v" := 0;
2529 FOR "issue_delegation_row" IN
2530 SELECT * FROM "issue_delegation"
2531 WHERE "trustee_id" = "member_id_p"
2532 AND "issue_id" = "issue_id_p"
2533 LOOP
2534 IF NOT EXISTS (
2535 SELECT NULL FROM "direct_voter"
2536 WHERE "member_id" = "issue_delegation_row"."truster_id"
2537 AND "issue_id" = "issue_id_p"
2538 ) AND NOT EXISTS (
2539 SELECT NULL FROM "delegating_voter"
2540 WHERE "member_id" = "issue_delegation_row"."truster_id"
2541 AND "issue_id" = "issue_id_p"
2542 ) THEN
2543 "delegate_member_ids_v" :=
2544 "member_id_p" || "delegate_member_ids_p";
2545 INSERT INTO "delegating_voter" (
2546 "issue_id",
2547 "member_id",
2548 "scope",
2549 "delegate_member_ids"
2550 ) VALUES (
2551 "issue_id_p",
2552 "issue_delegation_row"."truster_id",
2553 "issue_delegation_row"."scope",
2554 "delegate_member_ids_v"
2555 );
2556 "sub_weight_v" := 1 +
2557 "weight_of_added_vote_delegations"(
2558 "issue_id_p",
2559 "issue_delegation_row"."truster_id",
2560 "delegate_member_ids_v"
2561 );
2562 UPDATE "delegating_voter"
2563 SET "weight" = "sub_weight_v"
2564 WHERE "issue_id" = "issue_id_p"
2565 AND "member_id" = "issue_delegation_row"."truster_id";
2566 "weight_v" := "weight_v" + "sub_weight_v";
2567 END IF;
2568 END LOOP;
2569 RETURN "weight_v";
2570 END;
2571 $$;
2573 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
2574 ( "issue"."id"%TYPE,
2575 "member"."id"%TYPE,
2576 "delegating_voter"."delegate_member_ids"%TYPE )
2577 IS 'Helper function for "add_vote_delegations" function';
2580 CREATE FUNCTION "add_vote_delegations"
2581 ( "issue_id_p" "issue"."id"%TYPE )
2582 RETURNS VOID
2583 LANGUAGE 'plpgsql' VOLATILE AS $$
2584 DECLARE
2585 "member_id_v" "member"."id"%TYPE;
2586 BEGIN
2587 FOR "member_id_v" IN
2588 SELECT "member_id" FROM "direct_voter"
2589 WHERE "issue_id" = "issue_id_p"
2590 LOOP
2591 UPDATE "direct_voter" SET
2592 "weight" = "weight" + "weight_of_added_vote_delegations"(
2593 "issue_id_p",
2594 "member_id_v",
2595 '{}'
2596 )
2597 WHERE "member_id" = "member_id_v"
2598 AND "issue_id" = "issue_id_p";
2599 END LOOP;
2600 RETURN;
2601 END;
2602 $$;
2604 COMMENT ON FUNCTION "add_vote_delegations"
2605 ( "issue_id_p" "issue"."id"%TYPE )
2606 IS 'Helper function for "close_voting" function';
2609 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
2610 RETURNS VOID
2611 LANGUAGE 'plpgsql' VOLATILE AS $$
2612 DECLARE
2613 "issue_row" "issue"%ROWTYPE;
2614 "member_id_v" "member"."id"%TYPE;
2615 BEGIN
2616 PERFORM "global_lock"();
2617 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2618 DELETE FROM "delegating_voter"
2619 WHERE "issue_id" = "issue_id_p";
2620 DELETE FROM "direct_voter"
2621 WHERE "issue_id" = "issue_id_p"
2622 AND "autoreject" = TRUE;
2623 DELETE FROM "direct_voter" USING "member"
2624 WHERE "direct_voter"."member_id" = "member"."id"
2625 AND "direct_voter"."issue_id" = "issue_id_p"
2626 AND "member"."active" = FALSE;
2627 UPDATE "direct_voter" SET "weight" = 1
2628 WHERE "issue_id" = "issue_id_p";
2629 PERFORM "add_vote_delegations"("issue_id_p");
2630 FOR "member_id_v" IN
2631 SELECT "interest"."member_id"
2632 FROM "interest"
2633 LEFT JOIN "direct_voter"
2634 ON "interest"."member_id" = "direct_voter"."member_id"
2635 AND "interest"."issue_id" = "direct_voter"."issue_id"
2636 LEFT JOIN "delegating_voter"
2637 ON "interest"."member_id" = "delegating_voter"."member_id"
2638 AND "interest"."issue_id" = "delegating_voter"."issue_id"
2639 WHERE "interest"."issue_id" = "issue_id_p"
2640 AND "interest"."autoreject" = TRUE
2641 AND "direct_voter"."member_id" ISNULL
2642 AND "delegating_voter"."member_id" ISNULL
2643 UNION SELECT "membership"."member_id"
2644 FROM "membership"
2645 LEFT JOIN "interest"
2646 ON "membership"."member_id" = "interest"."member_id"
2647 AND "interest"."issue_id" = "issue_id_p"
2648 LEFT JOIN "direct_voter"
2649 ON "membership"."member_id" = "direct_voter"."member_id"
2650 AND "direct_voter"."issue_id" = "issue_id_p"
2651 LEFT JOIN "delegating_voter"
2652 ON "membership"."member_id" = "delegating_voter"."member_id"
2653 AND "delegating_voter"."issue_id" = "issue_id_p"
2654 WHERE "membership"."area_id" = "issue_row"."area_id"
2655 AND "membership"."autoreject" = TRUE
2656 AND "interest"."autoreject" ISNULL
2657 AND "direct_voter"."member_id" ISNULL
2658 AND "delegating_voter"."member_id" ISNULL
2659 LOOP
2660 INSERT INTO "direct_voter"
2661 ("member_id", "issue_id", "weight", "autoreject") VALUES
2662 ("member_id_v", "issue_id_p", 1, TRUE);
2663 INSERT INTO "vote" (
2664 "member_id",
2665 "issue_id",
2666 "initiative_id",
2667 "grade"
2668 ) SELECT
2669 "member_id_v" AS "member_id",
2670 "issue_id_p" AS "issue_id",
2671 "id" AS "initiative_id",
2672 -1 AS "grade"
2673 FROM "initiative" WHERE "issue_id" = "issue_id_p";
2674 END LOOP;
2675 PERFORM "add_vote_delegations"("issue_id_p");
2676 UPDATE "issue" SET
2677 "voter_count" = (
2678 SELECT coalesce(sum("weight"), 0)
2679 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
2680 )
2681 WHERE "id" = "issue_id_p";
2682 UPDATE "initiative" SET
2683 "positive_votes" = "vote_counts"."positive_votes",
2684 "negative_votes" = "vote_counts"."negative_votes",
2685 "agreed" = CASE WHEN "majority_strict" THEN
2686 "vote_counts"."positive_votes" * "majority_den" >
2687 "majority_num" *
2688 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
2689 ELSE
2690 "vote_counts"."positive_votes" * "majority_den" >=
2691 "majority_num" *
2692 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
2693 END
2694 FROM
2695 ( SELECT
2696 "initiative"."id" AS "initiative_id",
2697 coalesce(
2698 sum(
2699 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
2700 ),
2701 0
2702 ) AS "positive_votes",
2703 coalesce(
2704 sum(
2705 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
2706 ),
2707 0
2708 ) AS "negative_votes"
2709 FROM "initiative"
2710 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
2711 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
2712 LEFT JOIN "direct_voter"
2713 ON "direct_voter"."issue_id" = "initiative"."issue_id"
2714 LEFT JOIN "vote"
2715 ON "vote"."initiative_id" = "initiative"."id"
2716 AND "vote"."member_id" = "direct_voter"."member_id"
2717 WHERE "initiative"."issue_id" = "issue_id_p"
2718 AND "initiative"."admitted" -- NOTE: NULL case is handled too
2719 GROUP BY "initiative"."id"
2720 ) AS "vote_counts",
2721 "issue",
2722 "policy"
2723 WHERE "vote_counts"."initiative_id" = "initiative"."id"
2724 AND "issue"."id" = "initiative"."issue_id"
2725 AND "policy"."id" = "issue"."policy_id";
2726 UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p";
2727 END;
2728 $$;
2730 COMMENT ON FUNCTION "close_voting"
2731 ( "issue"."id"%TYPE )
2732 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.';
2735 CREATE FUNCTION "defeat_strength"
2736 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
2737 RETURNS INT8
2738 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2739 BEGIN
2740 IF "positive_votes_p" > "negative_votes_p" THEN
2741 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
2742 ELSIF "positive_votes_p" = "negative_votes_p" THEN
2743 RETURN 0;
2744 ELSE
2745 RETURN -1;
2746 END IF;
2747 END;
2748 $$;
2750 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';
2753 CREATE FUNCTION "array_init_string"("dim_p" INTEGER)
2754 RETURNS TEXT
2755 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2756 DECLARE
2757 "i" INTEGER;
2758 "ary_text_v" TEXT;
2759 BEGIN
2760 IF "dim_p" >= 1 THEN
2761 "ary_text_v" := '{NULL';
2762 "i" := "dim_p";
2763 LOOP
2764 "i" := "i" - 1;
2765 EXIT WHEN "i" = 0;
2766 "ary_text_v" := "ary_text_v" || ',NULL';
2767 END LOOP;
2768 "ary_text_v" := "ary_text_v" || '}';
2769 RETURN "ary_text_v";
2770 ELSE
2771 RAISE EXCEPTION 'Dimension needs to be at least 1.';
2772 END IF;
2773 END;
2774 $$;
2776 COMMENT ON FUNCTION "array_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
2779 CREATE FUNCTION "square_matrix_init_string"("dim_p" INTEGER)
2780 RETURNS TEXT
2781 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2782 DECLARE
2783 "i" INTEGER;
2784 "row_text_v" TEXT;
2785 "ary_text_v" TEXT;
2786 BEGIN
2787 IF "dim_p" >= 1 THEN
2788 "row_text_v" := '{NULL';
2789 "i" := "dim_p";
2790 LOOP
2791 "i" := "i" - 1;
2792 EXIT WHEN "i" = 0;
2793 "row_text_v" := "row_text_v" || ',NULL';
2794 END LOOP;
2795 "row_text_v" := "row_text_v" || '}';
2796 "ary_text_v" := '{' || "row_text_v";
2797 "i" := "dim_p";
2798 LOOP
2799 "i" := "i" - 1;
2800 EXIT WHEN "i" = 0;
2801 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
2802 END LOOP;
2803 "ary_text_v" := "ary_text_v" || '}';
2804 RETURN "ary_text_v";
2805 ELSE
2806 RAISE EXCEPTION 'Dimension needs to be at least 1.';
2807 END IF;
2808 END;
2809 $$;
2811 COMMENT ON FUNCTION "square_matrix_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
2814 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
2815 RETURNS VOID
2816 LANGUAGE 'plpgsql' VOLATILE AS $$
2817 DECLARE
2818 "dimension_v" INTEGER;
2819 "vote_matrix" INT4[][]; -- absolute votes
2820 "matrix" INT8[][]; -- defeat strength / best paths
2821 "i" INTEGER;
2822 "j" INTEGER;
2823 "k" INTEGER;
2824 "battle_row" "battle"%ROWTYPE;
2825 "rank_ary" INT4[];
2826 "rank_v" INT4;
2827 "done_v" INTEGER;
2828 "winners_ary" INTEGER[];
2829 "initiative_id_v" "initiative"."id"%TYPE;
2830 BEGIN
2831 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2832 SELECT count(1) INTO "dimension_v" FROM "initiative"
2833 WHERE "issue_id" = "issue_id_p" AND "agreed";
2834 IF "dimension_v" = 1 THEN
2835 UPDATE "initiative" SET "rank" = 1
2836 WHERE "issue_id" = "issue_id_p" AND "agreed";
2837 ELSIF "dimension_v" > 1 THEN
2838 -- Create "vote_matrix" with absolute number of votes in pairwise
2839 -- comparison:
2840 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2841 "i" := 1;
2842 "j" := 2;
2843 FOR "battle_row" IN
2844 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
2845 ORDER BY "winning_initiative_id", "losing_initiative_id"
2846 LOOP
2847 "vote_matrix"["i"]["j"] := "battle_row"."count";
2848 IF "j" = "dimension_v" THEN
2849 "i" := "i" + 1;
2850 "j" := 1;
2851 ELSE
2852 "j" := "j" + 1;
2853 IF "j" = "i" THEN
2854 "j" := "j" + 1;
2855 END IF;
2856 END IF;
2857 END LOOP;
2858 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
2859 RAISE EXCEPTION 'Wrong battle count (should not happen)';
2860 END IF;
2861 -- Store defeat strengths in "matrix" using "defeat_strength"
2862 -- function:
2863 "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2864 "i" := 1;
2865 LOOP
2866 "j" := 1;
2867 LOOP
2868 IF "i" != "j" THEN
2869 "matrix"["i"]["j"] := "defeat_strength"(
2870 "vote_matrix"["i"]["j"],
2871 "vote_matrix"["j"]["i"]
2872 );
2873 END IF;
2874 EXIT WHEN "j" = "dimension_v";
2875 "j" := "j" + 1;
2876 END LOOP;
2877 EXIT WHEN "i" = "dimension_v";
2878 "i" := "i" + 1;
2879 END LOOP;
2880 -- Find best paths:
2881 "i" := 1;
2882 LOOP
2883 "j" := 1;
2884 LOOP
2885 IF "i" != "j" THEN
2886 "k" := 1;
2887 LOOP
2888 IF "i" != "k" AND "j" != "k" THEN
2889 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
2890 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
2891 "matrix"["j"]["k"] := "matrix"["j"]["i"];
2892 END IF;
2893 ELSE
2894 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
2895 "matrix"["j"]["k"] := "matrix"["i"]["k"];
2896 END IF;
2897 END IF;
2898 END IF;
2899 EXIT WHEN "k" = "dimension_v";
2900 "k" := "k" + 1;
2901 END LOOP;
2902 END IF;
2903 EXIT WHEN "j" = "dimension_v";
2904 "j" := "j" + 1;
2905 END LOOP;
2906 EXIT WHEN "i" = "dimension_v";
2907 "i" := "i" + 1;
2908 END LOOP;
2909 -- Determine order of winners:
2910 "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2911 "rank_v" := 1;
2912 "done_v" := 0;
2913 LOOP
2914 "winners_ary" := '{}';
2915 "i" := 1;
2916 LOOP
2917 IF "rank_ary"["i"] ISNULL THEN
2918 "j" := 1;
2919 LOOP
2920 IF
2921 "i" != "j" AND
2922 "rank_ary"["j"] ISNULL AND
2923 "matrix"["j"]["i"] > "matrix"["i"]["j"]
2924 THEN
2925 -- someone else is better
2926 EXIT;
2927 END IF;
2928 IF "j" = "dimension_v" THEN
2929 -- noone is better
2930 "winners_ary" := "winners_ary" || "i";
2931 EXIT;
2932 END IF;
2933 "j" := "j" + 1;
2934 END LOOP;
2935 END IF;
2936 EXIT WHEN "i" = "dimension_v";
2937 "i" := "i" + 1;
2938 END LOOP;
2939 "i" := 1;
2940 LOOP
2941 "rank_ary"["winners_ary"["i"]] := "rank_v";
2942 "done_v" := "done_v" + 1;
2943 EXIT WHEN "i" = array_upper("winners_ary", 1);
2944 "i" := "i" + 1;
2945 END LOOP;
2946 EXIT WHEN "done_v" = "dimension_v";
2947 "rank_v" := "rank_v" + 1;
2948 END LOOP;
2949 -- write preliminary ranks:
2950 "i" := 1;
2951 FOR "initiative_id_v" IN
2952 SELECT "id" FROM "initiative"
2953 WHERE "issue_id" = "issue_id_p" AND "agreed"
2954 ORDER BY "id"
2955 LOOP
2956 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
2957 WHERE "id" = "initiative_id_v";
2958 "i" := "i" + 1;
2959 END LOOP;
2960 IF "i" != "dimension_v" + 1 THEN
2961 RAISE EXCEPTION 'Wrong winner count (should not happen)';
2962 END IF;
2963 -- straighten ranks (start counting with 1, no equal ranks):
2964 "rank_v" := 1;
2965 FOR "initiative_id_v" IN
2966 SELECT "id" FROM "initiative"
2967 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
2968 ORDER BY
2969 "rank",
2970 "vote_ratio"("positive_votes", "negative_votes") DESC,
2971 "id"
2972 LOOP
2973 UPDATE "initiative" SET "rank" = "rank_v"
2974 WHERE "id" = "initiative_id_v";
2975 "rank_v" := "rank_v" + 1;
2976 END LOOP;
2977 END IF;
2978 -- mark issue as finished
2979 UPDATE "issue" SET "ranks_available" = TRUE
2980 WHERE "id" = "issue_id_p";
2981 RETURN;
2982 END;
2983 $$;
2985 COMMENT ON FUNCTION "calculate_ranks"
2986 ( "issue"."id"%TYPE )
2987 IS 'Determine ranking (Votes have to be counted first)';
2991 -----------------------------
2992 -- Automatic state changes --
2993 -----------------------------
2996 CREATE FUNCTION "check_issue"
2997 ( "issue_id_p" "issue"."id"%TYPE )
2998 RETURNS VOID
2999 LANGUAGE 'plpgsql' VOLATILE AS $$
3000 DECLARE
3001 "issue_row" "issue"%ROWTYPE;
3002 "policy_row" "policy"%ROWTYPE;
3003 "voting_requested_v" BOOLEAN;
3004 BEGIN
3005 PERFORM "global_lock"();
3006 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3007 -- only process open issues:
3008 IF "issue_row"."closed" ISNULL THEN
3009 SELECT * INTO "policy_row" FROM "policy"
3010 WHERE "id" = "issue_row"."policy_id";
3011 -- create a snapshot, unless issue is already fully frozen:
3012 IF "issue_row"."fully_frozen" ISNULL THEN
3013 PERFORM "create_snapshot"("issue_id_p");
3014 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3015 END IF;
3016 -- eventually close or accept issues, which have not been accepted:
3017 IF "issue_row"."accepted" ISNULL THEN
3018 IF EXISTS (
3019 SELECT NULL FROM "initiative"
3020 WHERE "issue_id" = "issue_id_p"
3021 AND "supporter_count" > 0
3022 AND "supporter_count" * "policy_row"."issue_quorum_den"
3023 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
3024 ) THEN
3025 -- accept issues, if supporter count is high enough
3026 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3027 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later
3028 UPDATE "issue" SET "accepted" = "issue_row"."accepted"
3029 WHERE "id" = "issue_row"."id";
3030 ELSIF
3031 now() >= "issue_row"."created" + "issue_row"."admission_time"
3032 THEN
3033 -- close issues, if admission time has expired
3034 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3035 UPDATE "issue" SET "closed" = now()
3036 WHERE "id" = "issue_row"."id";
3037 END IF;
3038 END IF;
3039 -- eventually half freeze issues:
3040 IF
3041 -- NOTE: issue can't be closed at this point, if it has been accepted
3042 "issue_row"."accepted" NOTNULL AND
3043 "issue_row"."half_frozen" ISNULL
3044 THEN
3045 SELECT
3046 CASE
3047 WHEN "vote_now" * 2 > "issue_row"."population" THEN
3048 TRUE
3049 WHEN "vote_later" * 2 > "issue_row"."population" THEN
3050 FALSE
3051 ELSE NULL
3052 END
3053 INTO "voting_requested_v"
3054 FROM "issue" WHERE "id" = "issue_id_p";
3055 IF
3056 "voting_requested_v" OR (
3057 "voting_requested_v" ISNULL AND
3058 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
3059 )
3060 THEN
3061 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
3062 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later
3063 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
3064 WHERE "id" = "issue_row"."id";
3065 END IF;
3066 END IF;
3067 -- close issues after some time, if all initiatives have been revoked:
3068 IF
3069 "issue_row"."closed" ISNULL AND
3070 NOT EXISTS (
3071 -- all initiatives are revoked
3072 SELECT NULL FROM "initiative"
3073 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3074 ) AND (
3075 NOT EXISTS (
3076 -- and no initiatives have been revoked lately
3077 SELECT NULL FROM "initiative"
3078 WHERE "issue_id" = "issue_id_p"
3079 AND now() < "revoked" + "issue_row"."verification_time"
3080 ) OR (
3081 -- or verification time has elapsed
3082 "issue_row"."half_frozen" NOTNULL AND
3083 "issue_row"."fully_frozen" ISNULL AND
3084 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3085 )
3086 )
3087 THEN
3088 "issue_row"."closed" = now(); -- NOTE: "issue_row" used later
3089 UPDATE "issue" SET "closed" = "issue_row"."closed"
3090 WHERE "id" = "issue_row"."id";
3091 END IF;
3092 -- fully freeze issue after verification time:
3093 IF
3094 "issue_row"."half_frozen" NOTNULL AND
3095 "issue_row"."fully_frozen" ISNULL AND
3096 "issue_row"."closed" ISNULL AND
3097 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3098 THEN
3099 PERFORM "freeze_after_snapshot"("issue_id_p");
3100 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
3101 END IF;
3102 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3103 -- close issue by calling close_voting(...) after voting time:
3104 IF
3105 "issue_row"."closed" ISNULL AND
3106 "issue_row"."fully_frozen" NOTNULL AND
3107 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
3108 THEN
3109 PERFORM "close_voting"("issue_id_p");
3110 END IF;
3111 END IF;
3112 RETURN;
3113 END;
3114 $$;
3116 COMMENT ON FUNCTION "check_issue"
3117 ( "issue"."id"%TYPE )
3118 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.';
3121 CREATE FUNCTION "check_everything"()
3122 RETURNS VOID
3123 LANGUAGE 'plpgsql' VOLATILE AS $$
3124 DECLARE
3125 "issue_id_v" "issue"."id"%TYPE;
3126 BEGIN
3127 DELETE FROM "expired_session";
3128 PERFORM "calculate_member_counts"();
3129 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
3130 PERFORM "check_issue"("issue_id_v");
3131 END LOOP;
3132 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
3133 PERFORM "calculate_ranks"("issue_id_v");
3134 END LOOP;
3135 RETURN;
3136 END;
3137 $$;
3139 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.';
3143 ------------------------------
3144 -- Deletion of private data --
3145 ------------------------------
3148 CREATE FUNCTION "delete_private_data"()
3149 RETURNS VOID
3150 LANGUAGE 'plpgsql' VOLATILE AS $$
3151 DECLARE
3152 "issue_id_v" "issue"."id"%TYPE;
3153 BEGIN
3154 UPDATE "member" SET
3155 "login" = 'login' || "id"::text,
3156 "password" = NULL,
3157 "notify_email" = NULL,
3158 "notify_email_unconfirmed" = NULL,
3159 "notify_email_secret" = NULL,
3160 "notify_email_secret_expiry" = NULL,
3161 "password_reset_secret" = NULL,
3162 "password_reset_secret_expiry" = NULL,
3163 "organizational_unit" = NULL,
3164 "internal_posts" = NULL,
3165 "realname" = NULL,
3166 "birthday" = NULL,
3167 "address" = NULL,
3168 "email" = NULL,
3169 "xmpp_address" = NULL,
3170 "website" = NULL,
3171 "phone" = NULL,
3172 "mobile_phone" = NULL,
3173 "profession" = NULL,
3174 "external_memberships" = NULL,
3175 "external_posts" = NULL,
3176 "statement" = NULL;
3177 -- "text_search_data" is updated by triggers
3178 DELETE FROM "session";
3179 DELETE FROM "invite_code";
3180 DELETE FROM "contact";
3181 DELETE FROM "setting";
3182 DELETE FROM "member_image";
3183 DELETE FROM "direct_voter" USING "issue"
3184 WHERE "direct_voter"."issue_id" = "issue"."id"
3185 AND "issue"."closed" ISNULL;
3186 RETURN;
3187 END;
3188 $$;
3190 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.';
3194 COMMIT;