liquid_feedback_core
view core.sql @ 33:959e5453e6c7
Added tag beta21 for changeset 3ccab7349f28
| author | jbe |
|---|---|
| date | Sun Feb 21 15:03:59 2010 +0100 (2010-02-21) |
| parents | 3ccab7349f28 |
| children | 9970f73c1140 |
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 ('beta21', 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_id_v" "issue"."id"%TYPE;
994 "issue_row" "issue"%ROWTYPE;
995 BEGIN
996 IF TG_OP = 'DELETE' THEN
997 "issue_id_v" := OLD."issue_id";
998 ELSE
999 "issue_id_v" := NEW."issue_id";
1000 END IF;
1001 SELECT INTO "issue_row" * FROM "issue"
1002 WHERE "id" = "issue_id_v" FOR SHARE;
1003 IF "issue_row"."closed" NOTNULL THEN
1004 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
1005 END IF;
1006 RETURN NULL;
1007 END;
1008 $$;
1010 CREATE TRIGGER "forbid_changes_on_closed_issue"
1011 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1012 FOR EACH ROW EXECUTE PROCEDURE
1013 "forbid_changes_on_closed_issue_trigger"();
1015 CREATE TRIGGER "forbid_changes_on_closed_issue"
1016 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1017 FOR EACH ROW EXECUTE PROCEDURE
1018 "forbid_changes_on_closed_issue_trigger"();
1020 CREATE TRIGGER "forbid_changes_on_closed_issue"
1021 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1022 FOR EACH ROW EXECUTE PROCEDURE
1023 "forbid_changes_on_closed_issue_trigger"();
1025 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"';
1026 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';
1027 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';
1028 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';
1032 --------------------------------------------------------------------
1033 -- Auto-retrieval of fields only needed for referential integrity --
1034 --------------------------------------------------------------------
1037 CREATE FUNCTION "autofill_issue_id_trigger"()
1038 RETURNS TRIGGER
1039 LANGUAGE 'plpgsql' VOLATILE AS $$
1040 BEGIN
1041 IF NEW."issue_id" ISNULL THEN
1042 SELECT "issue_id" INTO NEW."issue_id"
1043 FROM "initiative" WHERE "id" = NEW."initiative_id";
1044 END IF;
1045 RETURN NEW;
1046 END;
1047 $$;
1049 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1050 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1052 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1053 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1055 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1056 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1057 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1060 CREATE FUNCTION "autofill_initiative_id_trigger"()
1061 RETURNS TRIGGER
1062 LANGUAGE 'plpgsql' VOLATILE AS $$
1063 BEGIN
1064 IF NEW."initiative_id" ISNULL THEN
1065 SELECT "initiative_id" INTO NEW."initiative_id"
1066 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1067 END IF;
1068 RETURN NEW;
1069 END;
1070 $$;
1072 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1073 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1075 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1076 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1080 -----------------------------------------------------
1081 -- Automatic calculation of certain default values --
1082 -----------------------------------------------------
1085 CREATE FUNCTION "copy_timings_trigger"()
1086 RETURNS TRIGGER
1087 LANGUAGE 'plpgsql' VOLATILE AS $$
1088 DECLARE
1089 "policy_row" "policy"%ROWTYPE;
1090 BEGIN
1091 SELECT * INTO "policy_row" FROM "policy"
1092 WHERE "id" = NEW."policy_id";
1093 IF NEW."admission_time" ISNULL THEN
1094 NEW."admission_time" := "policy_row"."admission_time";
1095 END IF;
1096 IF NEW."discussion_time" ISNULL THEN
1097 NEW."discussion_time" := "policy_row"."discussion_time";
1098 END IF;
1099 IF NEW."verification_time" ISNULL THEN
1100 NEW."verification_time" := "policy_row"."verification_time";
1101 END IF;
1102 IF NEW."voting_time" ISNULL THEN
1103 NEW."voting_time" := "policy_row"."voting_time";
1104 END IF;
1105 RETURN NEW;
1106 END;
1107 $$;
1109 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1110 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1112 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1113 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1116 CREATE FUNCTION "copy_autoreject_trigger"()
1117 RETURNS TRIGGER
1118 LANGUAGE 'plpgsql' VOLATILE AS $$
1119 BEGIN
1120 IF NEW."autoreject" ISNULL THEN
1121 SELECT "membership"."autoreject" INTO NEW."autoreject"
1122 FROM "issue" JOIN "membership"
1123 ON "issue"."area_id" = "membership"."area_id"
1124 WHERE "issue"."id" = NEW."issue_id"
1125 AND "membership"."member_id" = NEW."member_id";
1126 END IF;
1127 IF NEW."autoreject" ISNULL THEN
1128 NEW."autoreject" := FALSE;
1129 END IF;
1130 RETURN NEW;
1131 END;
1132 $$;
1134 CREATE TRIGGER "copy_autoreject" BEFORE INSERT OR UPDATE ON "interest"
1135 FOR EACH ROW EXECUTE PROCEDURE "copy_autoreject_trigger"();
1137 COMMENT ON FUNCTION "copy_autoreject_trigger"() IS 'Implementation of trigger "copy_autoreject" on table "interest"';
1138 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';
1141 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
1142 RETURNS TRIGGER
1143 LANGUAGE 'plpgsql' VOLATILE AS $$
1144 BEGIN
1145 IF NEW."draft_id" ISNULL THEN
1146 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1147 WHERE "initiative_id" = NEW."initiative_id";
1148 END IF;
1149 RETURN NEW;
1150 END;
1151 $$;
1153 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1154 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
1156 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
1157 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';
1161 ----------------------------------------
1162 -- Automatic creation of dependencies --
1163 ----------------------------------------
1166 CREATE FUNCTION "autocreate_interest_trigger"()
1167 RETURNS TRIGGER
1168 LANGUAGE 'plpgsql' VOLATILE AS $$
1169 BEGIN
1170 IF NOT EXISTS (
1171 SELECT NULL FROM "initiative" JOIN "interest"
1172 ON "initiative"."issue_id" = "interest"."issue_id"
1173 WHERE "initiative"."id" = NEW."initiative_id"
1174 AND "interest"."member_id" = NEW."member_id"
1175 ) THEN
1176 BEGIN
1177 INSERT INTO "interest" ("issue_id", "member_id")
1178 SELECT "issue_id", NEW."member_id"
1179 FROM "initiative" WHERE "id" = NEW."initiative_id";
1180 EXCEPTION WHEN unique_violation THEN END;
1181 END IF;
1182 RETURN NEW;
1183 END;
1184 $$;
1186 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1187 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1189 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1190 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';
1193 CREATE FUNCTION "autocreate_supporter_trigger"()
1194 RETURNS TRIGGER
1195 LANGUAGE 'plpgsql' VOLATILE AS $$
1196 BEGIN
1197 IF NOT EXISTS (
1198 SELECT NULL FROM "suggestion" JOIN "supporter"
1199 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1200 WHERE "suggestion"."id" = NEW."suggestion_id"
1201 AND "supporter"."member_id" = NEW."member_id"
1202 ) THEN
1203 BEGIN
1204 INSERT INTO "supporter" ("initiative_id", "member_id")
1205 SELECT "initiative_id", NEW."member_id"
1206 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1207 EXCEPTION WHEN unique_violation THEN END;
1208 END IF;
1209 RETURN NEW;
1210 END;
1211 $$;
1213 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1214 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1216 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1217 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.';
1221 ------------------------------------------
1222 -- Views and helper functions for views --
1223 ------------------------------------------
1226 CREATE VIEW "global_delegation" AS
1227 SELECT
1228 "delegation"."id",
1229 "delegation"."truster_id",
1230 "delegation"."trustee_id"
1231 FROM "delegation" JOIN "member"
1232 ON "delegation"."trustee_id" = "member"."id"
1233 WHERE "delegation"."scope" = 'global' AND "member"."active";
1235 COMMENT ON VIEW "global_delegation" IS 'Global delegations to active members';
1238 CREATE VIEW "area_delegation" AS
1239 SELECT "subquery".* FROM (
1240 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1241 "area"."id" AS "area_id",
1242 "delegation"."id",
1243 "delegation"."truster_id",
1244 "delegation"."trustee_id",
1245 "delegation"."scope"
1246 FROM "area" JOIN "delegation"
1247 ON "delegation"."scope" = 'global'
1248 OR "delegation"."area_id" = "area"."id"
1249 ORDER BY
1250 "area"."id",
1251 "delegation"."truster_id",
1252 "delegation"."scope" DESC
1253 ) AS "subquery"
1254 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
1255 WHERE "member"."active";
1257 COMMENT ON VIEW "area_delegation" IS 'Active delegations for areas';
1260 CREATE VIEW "issue_delegation" AS
1261 SELECT "subquery".* FROM (
1262 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1263 "issue"."id" AS "issue_id",
1264 "delegation"."id",
1265 "delegation"."truster_id",
1266 "delegation"."trustee_id",
1267 "delegation"."scope"
1268 FROM "issue" JOIN "delegation"
1269 ON "delegation"."scope" = 'global'
1270 OR "delegation"."area_id" = "issue"."area_id"
1271 OR "delegation"."issue_id" = "issue"."id"
1272 ORDER BY
1273 "issue"."id",
1274 "delegation"."truster_id",
1275 "delegation"."scope" DESC
1276 ) AS "subquery"
1277 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
1278 WHERE "member"."active";
1280 COMMENT ON VIEW "issue_delegation" IS 'Active delegations for issues';
1283 CREATE FUNCTION "membership_weight_with_skipping"
1284 ( "area_id_p" "area"."id"%TYPE,
1285 "member_id_p" "member"."id"%TYPE,
1286 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1287 RETURNS INT4
1288 LANGUAGE 'plpgsql' STABLE AS $$
1289 DECLARE
1290 "sum_v" INT4;
1291 "delegation_row" "area_delegation"%ROWTYPE;
1292 BEGIN
1293 "sum_v" := 1;
1294 FOR "delegation_row" IN
1295 SELECT "area_delegation".*
1296 FROM "area_delegation" LEFT JOIN "membership"
1297 ON "membership"."area_id" = "area_id_p"
1298 AND "membership"."member_id" = "area_delegation"."truster_id"
1299 WHERE "area_delegation"."area_id" = "area_id_p"
1300 AND "area_delegation"."trustee_id" = "member_id_p"
1301 AND "membership"."member_id" ISNULL
1302 LOOP
1303 IF NOT
1304 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1305 THEN
1306 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1307 "area_id_p",
1308 "delegation_row"."truster_id",
1309 "skip_member_ids_p" || "delegation_row"."truster_id"
1310 );
1311 END IF;
1312 END LOOP;
1313 RETURN "sum_v";
1314 END;
1315 $$;
1317 COMMENT ON FUNCTION "membership_weight_with_skipping"
1318 ( "area"."id"%TYPE,
1319 "member"."id"%TYPE,
1320 INT4[] )
1321 IS 'Helper function for "membership_weight" function';
1324 CREATE FUNCTION "membership_weight"
1325 ( "area_id_p" "area"."id"%TYPE,
1326 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1327 RETURNS INT4
1328 LANGUAGE 'plpgsql' STABLE AS $$
1329 BEGIN
1330 RETURN "membership_weight_with_skipping"(
1331 "area_id_p",
1332 "member_id_p",
1333 ARRAY["member_id_p"]
1334 );
1335 END;
1336 $$;
1338 COMMENT ON FUNCTION "membership_weight"
1339 ( "area"."id"%TYPE,
1340 "member"."id"%TYPE )
1341 IS 'Calculates the potential voting weight of a member in a given area';
1344 CREATE VIEW "member_count_view" AS
1345 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1347 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1350 CREATE VIEW "area_member_count" AS
1351 SELECT
1352 "area"."id" AS "area_id",
1353 count("member"."id") AS "direct_member_count",
1354 coalesce(
1355 sum(
1356 CASE WHEN "member"."id" NOTNULL THEN
1357 "membership_weight"("area"."id", "member"."id")
1358 ELSE 0 END
1359 )
1360 ) AS "member_weight",
1361 coalesce(
1362 sum(
1363 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
1364 "membership_weight"("area"."id", "member"."id")
1365 ELSE 0 END
1366 )
1367 ) AS "autoreject_weight"
1368 FROM "area"
1369 LEFT JOIN "membership"
1370 ON "area"."id" = "membership"."area_id"
1371 LEFT JOIN "member"
1372 ON "membership"."member_id" = "member"."id"
1373 AND "member"."active"
1374 GROUP BY "area"."id";
1376 COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"';
1379 CREATE VIEW "opening_draft" AS
1380 SELECT "draft".* FROM (
1381 SELECT
1382 "initiative"."id" AS "initiative_id",
1383 min("draft"."id") AS "draft_id"
1384 FROM "initiative" JOIN "draft"
1385 ON "initiative"."id" = "draft"."initiative_id"
1386 GROUP BY "initiative"."id"
1387 ) AS "subquery"
1388 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1390 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1393 CREATE VIEW "current_draft" AS
1394 SELECT "draft".* FROM (
1395 SELECT
1396 "initiative"."id" AS "initiative_id",
1397 max("draft"."id") AS "draft_id"
1398 FROM "initiative" JOIN "draft"
1399 ON "initiative"."id" = "draft"."initiative_id"
1400 GROUP BY "initiative"."id"
1401 ) AS "subquery"
1402 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1404 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1407 CREATE VIEW "critical_opinion" AS
1408 SELECT * FROM "opinion"
1409 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
1410 OR ("degree" = -2 AND "fulfilled" = TRUE);
1412 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
1415 CREATE VIEW "battle" AS
1416 SELECT
1417 "issue"."id" AS "issue_id",
1418 "winning_initiative"."id" AS "winning_initiative_id",
1419 "losing_initiative"."id" AS "losing_initiative_id",
1420 sum(
1421 CASE WHEN
1422 coalesce("better_vote"."grade", 0) >
1423 coalesce("worse_vote"."grade", 0)
1424 THEN "direct_voter"."weight" ELSE 0 END
1425 ) AS "count"
1426 FROM "issue"
1427 LEFT JOIN "direct_voter"
1428 ON "issue"."id" = "direct_voter"."issue_id"
1429 JOIN "initiative" AS "winning_initiative"
1430 ON "issue"."id" = "winning_initiative"."issue_id"
1431 AND "winning_initiative"."agreed"
1432 JOIN "initiative" AS "losing_initiative"
1433 ON "issue"."id" = "losing_initiative"."issue_id"
1434 AND "losing_initiative"."agreed"
1435 LEFT JOIN "vote" AS "better_vote"
1436 ON "direct_voter"."member_id" = "better_vote"."member_id"
1437 AND "winning_initiative"."id" = "better_vote"."initiative_id"
1438 LEFT JOIN "vote" AS "worse_vote"
1439 ON "direct_voter"."member_id" = "worse_vote"."member_id"
1440 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
1441 WHERE
1442 "winning_initiative"."id" != "losing_initiative"."id"
1443 GROUP BY
1444 "issue"."id",
1445 "winning_initiative"."id",
1446 "losing_initiative"."id";
1448 COMMENT ON VIEW "battle" IS 'Number of members preferring one initiative over another';
1451 CREATE VIEW "expired_session" AS
1452 SELECT * FROM "session" WHERE now() > "expiry";
1454 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
1455 DELETE FROM "session" WHERE "ident" = OLD."ident";
1457 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
1458 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
1461 CREATE VIEW "open_issue" AS
1462 SELECT * FROM "issue" WHERE "closed" ISNULL;
1464 COMMENT ON VIEW "open_issue" IS 'All open issues';
1467 CREATE VIEW "issue_with_ranks_missing" AS
1468 SELECT * FROM "issue"
1469 WHERE "fully_frozen" NOTNULL
1470 AND "closed" NOTNULL
1471 AND "ranks_available" = FALSE;
1473 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
1476 CREATE VIEW "member_contingent" AS
1477 SELECT
1478 "member"."id" AS "member_id",
1479 "contingent"."time_frame",
1480 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
1481 (
1482 SELECT count(1) FROM "draft"
1483 WHERE "draft"."author_id" = "member"."id"
1484 AND "draft"."created" > now() - "contingent"."time_frame"
1485 ) + (
1486 SELECT count(1) FROM "suggestion"
1487 WHERE "suggestion"."author_id" = "member"."id"
1488 AND "suggestion"."created" > now() - "contingent"."time_frame"
1489 )
1490 ELSE NULL END AS "text_entry_count",
1491 "contingent"."text_entry_limit",
1492 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
1493 SELECT count(1) FROM "opening_draft"
1494 WHERE "opening_draft"."author_id" = "member"."id"
1495 AND "opening_draft"."created" > now() - "contingent"."time_frame"
1496 ) ELSE NULL END AS "initiative_count",
1497 "contingent"."initiative_limit"
1498 FROM "member" CROSS JOIN "contingent";
1500 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
1502 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
1503 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
1506 CREATE VIEW "member_contingent_left" AS
1507 SELECT
1508 "member_id",
1509 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
1510 max("initiative_limit" - "initiative_count") AS "initiatives_left"
1511 FROM "member_contingent" GROUP BY "member_id";
1513 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.';
1516 CREATE TYPE "timeline_event" AS ENUM (
1517 'issue_created',
1518 'issue_canceled',
1519 'issue_accepted',
1520 'issue_half_frozen',
1521 'issue_finished_without_voting',
1522 'issue_voting_started',
1523 'issue_finished_after_voting',
1524 'initiative_created',
1525 'initiative_revoked',
1526 'draft_created',
1527 'suggestion_created');
1529 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables';
1532 CREATE VIEW "timeline_issue" AS
1533 SELECT
1534 "created" AS "occurrence",
1535 'issue_created'::"timeline_event" AS "event",
1536 "id" AS "issue_id"
1537 FROM "issue"
1538 UNION ALL
1539 SELECT
1540 "closed" AS "occurrence",
1541 'issue_canceled'::"timeline_event" AS "event",
1542 "id" AS "issue_id"
1543 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
1544 UNION ALL
1545 SELECT
1546 "accepted" AS "occurrence",
1547 'issue_accepted'::"timeline_event" AS "event",
1548 "id" AS "issue_id"
1549 FROM "issue" WHERE "accepted" NOTNULL
1550 UNION ALL
1551 SELECT
1552 "half_frozen" AS "occurrence",
1553 'issue_half_frozen'::"timeline_event" AS "event",
1554 "id" AS "issue_id"
1555 FROM "issue" WHERE "half_frozen" NOTNULL
1556 UNION ALL
1557 SELECT
1558 "fully_frozen" AS "occurrence",
1559 'issue_voting_started'::"timeline_event" AS "event",
1560 "id" AS "issue_id"
1561 FROM "issue"
1562 WHERE "fully_frozen" NOTNULL
1563 AND ("closed" ISNULL OR "closed" != "fully_frozen")
1564 UNION ALL
1565 SELECT
1566 "closed" AS "occurrence",
1567 CASE WHEN "fully_frozen" = "closed" THEN
1568 'issue_finished_without_voting'::"timeline_event"
1569 ELSE
1570 'issue_finished_after_voting'::"timeline_event"
1571 END AS "event",
1572 "id" AS "issue_id"
1573 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
1575 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view';
1578 CREATE VIEW "timeline_initiative" AS
1579 SELECT
1580 "created" AS "occurrence",
1581 'initiative_created'::"timeline_event" AS "event",
1582 "id" AS "initiative_id"
1583 FROM "initiative"
1584 UNION ALL
1585 SELECT
1586 "revoked" AS "occurrence",
1587 'initiative_revoked'::"timeline_event" AS "event",
1588 "id" AS "initiative_id"
1589 FROM "initiative" WHERE "revoked" NOTNULL;
1591 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view';
1594 CREATE VIEW "timeline_draft" AS
1595 SELECT
1596 "created" AS "occurrence",
1597 'draft_created'::"timeline_event" AS "event",
1598 "id" AS "draft_id"
1599 FROM "draft";
1601 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view';
1604 CREATE VIEW "timeline_suggestion" AS
1605 SELECT
1606 "created" AS "occurrence",
1607 'suggestion_created'::"timeline_event" AS "event",
1608 "id" AS "suggestion_id"
1609 FROM "suggestion";
1611 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view';
1614 CREATE VIEW "timeline" AS
1615 SELECT
1616 "occurrence",
1617 "event",
1618 "issue_id",
1619 NULL AS "initiative_id",
1620 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
1621 NULL::INT8 AS "suggestion_id"
1622 FROM "timeline_issue"
1623 UNION ALL
1624 SELECT
1625 "occurrence",
1626 "event",
1627 NULL AS "issue_id",
1628 "initiative_id",
1629 NULL AS "draft_id",
1630 NULL AS "suggestion_id"
1631 FROM "timeline_initiative"
1632 UNION ALL
1633 SELECT
1634 "occurrence",
1635 "event",
1636 NULL AS "issue_id",
1637 NULL AS "initiative_id",
1638 "draft_id",
1639 NULL AS "suggestion_id"
1640 FROM "timeline_draft"
1641 UNION ALL
1642 SELECT
1643 "occurrence",
1644 "event",
1645 NULL AS "issue_id",
1646 NULL AS "initiative_id",
1647 NULL AS "draft_id",
1648 "suggestion_id"
1649 FROM "timeline_suggestion";
1651 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system';
1655 --------------------------------------------------
1656 -- Set returning function for delegation chains --
1657 --------------------------------------------------
1660 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
1661 ('first', 'intermediate', 'last', 'repetition');
1663 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
1666 CREATE TYPE "delegation_chain_row" AS (
1667 "index" INT4,
1668 "member_id" INT4,
1669 "member_active" BOOLEAN,
1670 "participation" BOOLEAN,
1671 "overridden" BOOLEAN,
1672 "scope_in" "delegation_scope",
1673 "scope_out" "delegation_scope",
1674 "loop" "delegation_chain_loop_tag" );
1676 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
1678 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
1679 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';
1680 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
1681 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
1682 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
1683 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
1686 CREATE FUNCTION "delegation_chain"
1687 ( "member_id_p" "member"."id"%TYPE,
1688 "area_id_p" "area"."id"%TYPE,
1689 "issue_id_p" "issue"."id"%TYPE,
1690 "simulate_trustee_id_p" "member"."id"%TYPE )
1691 RETURNS SETOF "delegation_chain_row"
1692 LANGUAGE 'plpgsql' STABLE AS $$
1693 DECLARE
1694 "issue_row" "issue"%ROWTYPE;
1695 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
1696 "loop_member_id_v" "member"."id"%TYPE;
1697 "output_row" "delegation_chain_row";
1698 "output_rows" "delegation_chain_row"[];
1699 "delegation_row" "delegation"%ROWTYPE;
1700 "row_count" INT4;
1701 "i" INT4;
1702 "loop_v" BOOLEAN;
1703 BEGIN
1704 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1705 "visited_member_ids" := '{}';
1706 "loop_member_id_v" := NULL;
1707 "output_rows" := '{}';
1708 "output_row"."index" := 0;
1709 "output_row"."member_id" := "member_id_p";
1710 "output_row"."member_active" := TRUE;
1711 "output_row"."participation" := FALSE;
1712 "output_row"."overridden" := FALSE;
1713 "output_row"."scope_out" := NULL;
1714 LOOP
1715 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
1716 "loop_member_id_v" := "output_row"."member_id";
1717 ELSE
1718 "visited_member_ids" :=
1719 "visited_member_ids" || "output_row"."member_id";
1720 END IF;
1721 IF "output_row"."participation" THEN
1722 "output_row"."overridden" := TRUE;
1723 END IF;
1724 "output_row"."scope_in" := "output_row"."scope_out";
1725 IF EXISTS (
1726 SELECT NULL FROM "member"
1727 WHERE "id" = "output_row"."member_id" AND "active"
1728 ) THEN
1729 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
1730 SELECT * INTO "delegation_row" FROM "delegation"
1731 WHERE "truster_id" = "output_row"."member_id"
1732 AND "scope" = 'global';
1733 ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN
1734 "output_row"."participation" := EXISTS (
1735 SELECT NULL FROM "membership"
1736 WHERE "area_id" = "area_id_p"
1737 AND "member_id" = "output_row"."member_id"
1738 );
1739 SELECT * INTO "delegation_row" FROM "delegation"
1740 WHERE "truster_id" = "output_row"."member_id"
1741 AND ("scope" = 'global' OR "area_id" = "area_id_p")
1742 ORDER BY "scope" DESC;
1743 ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN
1744 "output_row"."participation" := EXISTS (
1745 SELECT NULL FROM "interest"
1746 WHERE "issue_id" = "issue_id_p"
1747 AND "member_id" = "output_row"."member_id"
1748 );
1749 SELECT * INTO "delegation_row" FROM "delegation"
1750 WHERE "truster_id" = "output_row"."member_id"
1751 AND ("scope" = 'global' OR
1752 "area_id" = "issue_row"."area_id" OR
1753 "issue_id" = "issue_id_p"
1754 )
1755 ORDER BY "scope" DESC;
1756 ELSE
1757 RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.';
1758 END IF;
1759 ELSE
1760 "output_row"."member_active" := FALSE;
1761 "output_row"."participation" := FALSE;
1762 "output_row"."scope_out" := NULL;
1763 "delegation_row" := ROW(NULL);
1764 END IF;
1765 IF
1766 "output_row"."member_id" = "member_id_p" AND
1767 "simulate_trustee_id_p" NOTNULL
1768 THEN
1769 "output_row"."scope_out" := CASE
1770 WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global'
1771 WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area'
1772 WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue'
1773 END;
1774 "output_rows" := "output_rows" || "output_row";
1775 "output_row"."member_id" := "simulate_trustee_id_p";
1776 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
1777 "output_row"."scope_out" := "delegation_row"."scope";
1778 "output_rows" := "output_rows" || "output_row";
1779 "output_row"."member_id" := "delegation_row"."trustee_id";
1780 ELSE
1781 "output_row"."scope_out" := NULL;
1782 "output_rows" := "output_rows" || "output_row";
1783 EXIT;
1784 END IF;
1785 EXIT WHEN "loop_member_id_v" NOTNULL;
1786 "output_row"."index" := "output_row"."index" + 1;
1787 END LOOP;
1788 "row_count" := array_upper("output_rows", 1);
1789 "i" := 1;
1790 "loop_v" := FALSE;
1791 LOOP
1792 "output_row" := "output_rows"["i"];
1793 EXIT WHEN "output_row"."member_id" ISNULL;
1794 IF "loop_v" THEN
1795 IF "i" + 1 = "row_count" THEN
1796 "output_row"."loop" := 'last';
1797 ELSIF "i" = "row_count" THEN
1798 "output_row"."loop" := 'repetition';
1799 ELSE
1800 "output_row"."loop" := 'intermediate';
1801 END IF;
1802 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
1803 "output_row"."loop" := 'first';
1804 "loop_v" := TRUE;
1805 END IF;
1806 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
1807 "output_row"."participation" := NULL;
1808 END IF;
1809 RETURN NEXT "output_row";
1810 "i" := "i" + 1;
1811 END LOOP;
1812 RETURN;
1813 END;
1814 $$;
1816 COMMENT ON FUNCTION "delegation_chain"
1817 ( "member"."id"%TYPE,
1818 "area"."id"%TYPE,
1819 "issue"."id"%TYPE,
1820 "member"."id"%TYPE )
1821 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
1823 CREATE FUNCTION "delegation_chain"
1824 ( "member_id_p" "member"."id"%TYPE,
1825 "area_id_p" "area"."id"%TYPE,
1826 "issue_id_p" "issue"."id"%TYPE )
1827 RETURNS SETOF "delegation_chain_row"
1828 LANGUAGE 'plpgsql' STABLE AS $$
1829 DECLARE
1830 "result_row" "delegation_chain_row";
1831 BEGIN
1832 FOR "result_row" IN
1833 SELECT * FROM "delegation_chain"(
1834 "member_id_p", "area_id_p", "issue_id_p", NULL
1835 )
1836 LOOP
1837 RETURN NEXT "result_row";
1838 END LOOP;
1839 RETURN;
1840 END;
1841 $$;
1843 COMMENT ON FUNCTION "delegation_chain"
1844 ( "member"."id"%TYPE,
1845 "area"."id"%TYPE,
1846 "issue"."id"%TYPE )
1847 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
1851 ------------------------------
1852 -- Comparison by vote count --
1853 ------------------------------
1855 CREATE FUNCTION "vote_ratio"
1856 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
1857 "negative_votes_p" "initiative"."negative_votes"%TYPE )
1858 RETURNS FLOAT8
1859 LANGUAGE 'plpgsql' STABLE AS $$
1860 BEGIN
1861 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
1862 RETURN
1863 "positive_votes_p"::FLOAT8 /
1864 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
1865 ELSIF "positive_votes_p" > 0 THEN
1866 RETURN "positive_votes_p";
1867 ELSIF "negative_votes_p" > 0 THEN
1868 RETURN 1 - "negative_votes_p";
1869 ELSE
1870 RETURN 0.5;
1871 END IF;
1872 END;
1873 $$;
1875 COMMENT ON FUNCTION "vote_ratio"
1876 ( "initiative"."positive_votes"%TYPE,
1877 "initiative"."negative_votes"%TYPE )
1878 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.';
1882 ------------------------------------------------
1883 -- Locking for snapshots and voting procedure --
1884 ------------------------------------------------
1886 CREATE FUNCTION "global_lock"() RETURNS VOID
1887 LANGUAGE 'plpgsql' VOLATILE AS $$
1888 BEGIN
1889 -- NOTE: PostgreSQL allows reading, while tables are locked in
1890 -- exclusive move. Transactions should be kept short anyway!
1891 LOCK TABLE "member" IN EXCLUSIVE MODE;
1892 LOCK TABLE "area" IN EXCLUSIVE MODE;
1893 LOCK TABLE "membership" IN EXCLUSIVE MODE;
1894 -- NOTE: "member", "area" and "membership" are locked first to
1895 -- prevent deadlocks in combination with "calculate_member_counts"()
1896 LOCK TABLE "policy" IN EXCLUSIVE MODE;
1897 LOCK TABLE "issue" IN EXCLUSIVE MODE;
1898 LOCK TABLE "initiative" IN EXCLUSIVE MODE;
1899 LOCK TABLE "draft" IN EXCLUSIVE MODE;
1900 LOCK TABLE "suggestion" IN EXCLUSIVE MODE;
1901 LOCK TABLE "interest" IN EXCLUSIVE MODE;
1902 LOCK TABLE "initiator" IN EXCLUSIVE MODE;
1903 LOCK TABLE "supporter" IN EXCLUSIVE MODE;
1904 LOCK TABLE "opinion" IN EXCLUSIVE MODE;
1905 LOCK TABLE "delegation" IN EXCLUSIVE MODE;
1906 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
1907 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
1908 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
1909 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
1910 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
1911 LOCK TABLE "direct_voter" IN EXCLUSIVE MODE;
1912 LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;
1913 LOCK TABLE "vote" IN EXCLUSIVE MODE;
1914 RETURN;
1915 END;
1916 $$;
1918 COMMENT ON FUNCTION "global_lock"() IS 'Locks all tables related to support/voting until end of transaction; read access is still possible though';
1922 -------------------------------
1923 -- Materialize member counts --
1924 -------------------------------
1926 CREATE FUNCTION "calculate_member_counts"()
1927 RETURNS VOID
1928 LANGUAGE 'plpgsql' VOLATILE AS $$
1929 BEGIN
1930 LOCK TABLE "member" IN EXCLUSIVE MODE;
1931 LOCK TABLE "area" IN EXCLUSIVE MODE;
1932 LOCK TABLE "membership" IN EXCLUSIVE MODE;
1933 DELETE FROM "member_count";
1934 INSERT INTO "member_count" ("total_count")
1935 SELECT "total_count" FROM "member_count_view";
1936 UPDATE "area" SET
1937 "direct_member_count" = "view"."direct_member_count",
1938 "member_weight" = "view"."member_weight",
1939 "autoreject_weight" = "view"."autoreject_weight"
1940 FROM "area_member_count" AS "view"
1941 WHERE "view"."area_id" = "area"."id";
1942 RETURN;
1943 END;
1944 $$;
1946 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"';
1950 ------------------------------
1951 -- Calculation of snapshots --
1952 ------------------------------
1954 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
1955 ( "issue_id_p" "issue"."id"%TYPE,
1956 "member_id_p" "member"."id"%TYPE,
1957 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
1958 RETURNS "direct_population_snapshot"."weight"%TYPE
1959 LANGUAGE 'plpgsql' VOLATILE AS $$
1960 DECLARE
1961 "issue_delegation_row" "issue_delegation"%ROWTYPE;
1962 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
1963 "weight_v" INT4;
1964 "sub_weight_v" INT4;
1965 BEGIN
1966 "weight_v" := 0;
1967 FOR "issue_delegation_row" IN
1968 SELECT * FROM "issue_delegation"
1969 WHERE "trustee_id" = "member_id_p"
1970 AND "issue_id" = "issue_id_p"
1971 LOOP
1972 IF NOT EXISTS (
1973 SELECT NULL FROM "direct_population_snapshot"
1974 WHERE "issue_id" = "issue_id_p"
1975 AND "event" = 'periodic'
1976 AND "member_id" = "issue_delegation_row"."truster_id"
1977 ) AND NOT EXISTS (
1978 SELECT NULL FROM "delegating_population_snapshot"
1979 WHERE "issue_id" = "issue_id_p"
1980 AND "event" = 'periodic'
1981 AND "member_id" = "issue_delegation_row"."truster_id"
1982 ) THEN
1983 "delegate_member_ids_v" :=
1984 "member_id_p" || "delegate_member_ids_p";
1985 INSERT INTO "delegating_population_snapshot" (
1986 "issue_id",
1987 "event",
1988 "member_id",
1989 "scope",
1990 "delegate_member_ids"
1991 ) VALUES (
1992 "issue_id_p",
1993 'periodic',
1994 "issue_delegation_row"."truster_id",
1995 "issue_delegation_row"."scope",
1996 "delegate_member_ids_v"
1997 );
1998 "sub_weight_v" := 1 +
1999 "weight_of_added_delegations_for_population_snapshot"(
2000 "issue_id_p",
2001 "issue_delegation_row"."truster_id",
2002 "delegate_member_ids_v"
2003 );
2004 UPDATE "delegating_population_snapshot"
2005 SET "weight" = "sub_weight_v"
2006 WHERE "issue_id" = "issue_id_p"
2007 AND "event" = 'periodic'
2008 AND "member_id" = "issue_delegation_row"."truster_id";
2009 "weight_v" := "weight_v" + "sub_weight_v";
2010 END IF;
2011 END LOOP;
2012 RETURN "weight_v";
2013 END;
2014 $$;
2016 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
2017 ( "issue"."id"%TYPE,
2018 "member"."id"%TYPE,
2019 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2020 IS 'Helper function for "create_population_snapshot" function';
2023 CREATE FUNCTION "create_population_snapshot"
2024 ( "issue_id_p" "issue"."id"%TYPE )
2025 RETURNS VOID
2026 LANGUAGE 'plpgsql' VOLATILE AS $$
2027 DECLARE
2028 "member_id_v" "member"."id"%TYPE;
2029 BEGIN
2030 DELETE FROM "direct_population_snapshot"
2031 WHERE "issue_id" = "issue_id_p"
2032 AND "event" = 'periodic';
2033 DELETE FROM "delegating_population_snapshot"
2034 WHERE "issue_id" = "issue_id_p"
2035 AND "event" = 'periodic';
2036 INSERT INTO "direct_population_snapshot"
2037 ("issue_id", "event", "member_id", "interest_exists")
2038 SELECT DISTINCT ON ("issue_id", "member_id")
2039 "issue_id_p" AS "issue_id",
2040 'periodic' AS "event",
2041 "subquery"."member_id",
2042 "subquery"."interest_exists"
2043 FROM (
2044 SELECT
2045 "member"."id" AS "member_id",
2046 FALSE AS "interest_exists"
2047 FROM "issue"
2048 JOIN "area" ON "issue"."area_id" = "area"."id"
2049 JOIN "membership" ON "area"."id" = "membership"."area_id"
2050 JOIN "member" ON "membership"."member_id" = "member"."id"
2051 WHERE "issue"."id" = "issue_id_p"
2052 AND "member"."active"
2053 UNION
2054 SELECT
2055 "member"."id" AS "member_id",
2056 TRUE AS "interest_exists"
2057 FROM "interest" JOIN "member"
2058 ON "interest"."member_id" = "member"."id"
2059 WHERE "interest"."issue_id" = "issue_id_p"
2060 AND "member"."active"
2061 ) AS "subquery"
2062 ORDER BY
2063 "issue_id_p",
2064 "subquery"."member_id",
2065 "subquery"."interest_exists" DESC;
2066 FOR "member_id_v" IN
2067 SELECT "member_id" FROM "direct_population_snapshot"
2068 WHERE "issue_id" = "issue_id_p"
2069 AND "event" = 'periodic'
2070 LOOP
2071 UPDATE "direct_population_snapshot" SET
2072 "weight" = 1 +
2073 "weight_of_added_delegations_for_population_snapshot"(
2074 "issue_id_p",
2075 "member_id_v",
2076 '{}'
2077 )
2078 WHERE "issue_id" = "issue_id_p"
2079 AND "event" = 'periodic'
2080 AND "member_id" = "member_id_v";
2081 END LOOP;
2082 RETURN;
2083 END;
2084 $$;
2086 COMMENT ON FUNCTION "create_population_snapshot"
2087 ( "issue_id_p" "issue"."id"%TYPE )
2088 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.';
2091 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2092 ( "issue_id_p" "issue"."id"%TYPE,
2093 "member_id_p" "member"."id"%TYPE,
2094 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2095 RETURNS "direct_interest_snapshot"."weight"%TYPE
2096 LANGUAGE 'plpgsql' VOLATILE AS $$
2097 DECLARE
2098 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2099 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2100 "weight_v" INT4;
2101 "sub_weight_v" INT4;
2102 BEGIN
2103 "weight_v" := 0;
2104 FOR "issue_delegation_row" IN
2105 SELECT * FROM "issue_delegation"
2106 WHERE "trustee_id" = "member_id_p"
2107 AND "issue_id" = "issue_id_p"
2108 LOOP
2109 IF NOT EXISTS (
2110 SELECT NULL FROM "direct_interest_snapshot"
2111 WHERE "issue_id" = "issue_id_p"
2112 AND "event" = 'periodic'
2113 AND "member_id" = "issue_delegation_row"."truster_id"
2114 ) AND NOT EXISTS (
2115 SELECT NULL FROM "delegating_interest_snapshot"
2116 WHERE "issue_id" = "issue_id_p"
2117 AND "event" = 'periodic'
2118 AND "member_id" = "issue_delegation_row"."truster_id"
2119 ) THEN
2120 "delegate_member_ids_v" :=
2121 "member_id_p" || "delegate_member_ids_p";
2122 INSERT INTO "delegating_interest_snapshot" (
2123 "issue_id",
2124 "event",
2125 "member_id",
2126 "scope",
2127 "delegate_member_ids"
2128 ) VALUES (
2129 "issue_id_p",
2130 'periodic',
2131 "issue_delegation_row"."truster_id",
2132 "issue_delegation_row"."scope",
2133 "delegate_member_ids_v"
2134 );
2135 "sub_weight_v" := 1 +
2136 "weight_of_added_delegations_for_interest_snapshot"(
2137 "issue_id_p",
2138 "issue_delegation_row"."truster_id",
2139 "delegate_member_ids_v"
2140 );
2141 UPDATE "delegating_interest_snapshot"
2142 SET "weight" = "sub_weight_v"
2143 WHERE "issue_id" = "issue_id_p"
2144 AND "event" = 'periodic'
2145 AND "member_id" = "issue_delegation_row"."truster_id";
2146 "weight_v" := "weight_v" + "sub_weight_v";
2147 END IF;
2148 END LOOP;
2149 RETURN "weight_v";
2150 END;
2151 $$;
2153 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2154 ( "issue"."id"%TYPE,
2155 "member"."id"%TYPE,
2156 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2157 IS 'Helper function for "create_interest_snapshot" function';
2160 CREATE FUNCTION "create_interest_snapshot"
2161 ( "issue_id_p" "issue"."id"%TYPE )
2162 RETURNS VOID
2163 LANGUAGE 'plpgsql' VOLATILE AS $$
2164 DECLARE
2165 "member_id_v" "member"."id"%TYPE;
2166 BEGIN
2167 DELETE FROM "direct_interest_snapshot"
2168 WHERE "issue_id" = "issue_id_p"
2169 AND "event" = 'periodic';
2170 DELETE FROM "delegating_interest_snapshot"
2171 WHERE "issue_id" = "issue_id_p"
2172 AND "event" = 'periodic';
2173 DELETE FROM "direct_supporter_snapshot"
2174 WHERE "issue_id" = "issue_id_p"
2175 AND "event" = 'periodic';
2176 INSERT INTO "direct_interest_snapshot"
2177 ("issue_id", "event", "member_id", "voting_requested")
2178 SELECT
2179 "issue_id_p" AS "issue_id",
2180 'periodic' AS "event",
2181 "member"."id" AS "member_id",
2182 "interest"."voting_requested"
2183 FROM "interest" JOIN "member"
2184 ON "interest"."member_id" = "member"."id"
2185 WHERE "interest"."issue_id" = "issue_id_p"
2186 AND "member"."active";
2187 FOR "member_id_v" IN
2188 SELECT "member_id" FROM "direct_interest_snapshot"
2189 WHERE "issue_id" = "issue_id_p"
2190 AND "event" = 'periodic'
2191 LOOP
2192 UPDATE "direct_interest_snapshot" SET
2193 "weight" = 1 +
2194 "weight_of_added_delegations_for_interest_snapshot"(
2195 "issue_id_p",
2196 "member_id_v",
2197 '{}'
2198 )
2199 WHERE "issue_id" = "issue_id_p"
2200 AND "event" = 'periodic'
2201 AND "member_id" = "member_id_v";
2202 END LOOP;
2203 INSERT INTO "direct_supporter_snapshot"
2204 ( "issue_id", "initiative_id", "event", "member_id",
2205 "informed", "satisfied" )
2206 SELECT
2207 "issue_id_p" AS "issue_id",
2208 "initiative"."id" AS "initiative_id",
2209 'periodic' AS "event",
2210 "member"."id" AS "member_id",
2211 "supporter"."draft_id" = "current_draft"."id" AS "informed",
2212 NOT EXISTS (
2213 SELECT NULL FROM "critical_opinion"
2214 WHERE "initiative_id" = "initiative"."id"
2215 AND "member_id" = "member"."id"
2216 ) AS "satisfied"
2217 FROM "supporter"
2218 JOIN "member"
2219 ON "supporter"."member_id" = "member"."id"
2220 JOIN "initiative"
2221 ON "supporter"."initiative_id" = "initiative"."id"
2222 JOIN "current_draft"
2223 ON "initiative"."id" = "current_draft"."initiative_id"
2224 JOIN "direct_interest_snapshot"
2225 ON "member"."id" = "direct_interest_snapshot"."member_id"
2226 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
2227 AND "event" = 'periodic'
2228 WHERE "member"."active"
2229 AND "initiative"."issue_id" = "issue_id_p";
2230 RETURN;
2231 END;
2232 $$;
2234 COMMENT ON FUNCTION "create_interest_snapshot"
2235 ( "issue"."id"%TYPE )
2236 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.';
2239 CREATE FUNCTION "create_snapshot"
2240 ( "issue_id_p" "issue"."id"%TYPE )
2241 RETURNS VOID
2242 LANGUAGE 'plpgsql' VOLATILE AS $$
2243 DECLARE
2244 "initiative_id_v" "initiative"."id"%TYPE;
2245 "suggestion_id_v" "suggestion"."id"%TYPE;
2246 BEGIN
2247 PERFORM "global_lock"();
2248 PERFORM "create_population_snapshot"("issue_id_p");
2249 PERFORM "create_interest_snapshot"("issue_id_p");
2250 UPDATE "issue" SET
2251 "snapshot" = now(),
2252 "latest_snapshot_event" = 'periodic',
2253 "population" = (
2254 SELECT coalesce(sum("weight"), 0)
2255 FROM "direct_population_snapshot"
2256 WHERE "issue_id" = "issue_id_p"
2257 AND "event" = 'periodic'
2258 ),
2259 "vote_now" = (
2260 SELECT coalesce(sum("weight"), 0)
2261 FROM "direct_interest_snapshot"
2262 WHERE "issue_id" = "issue_id_p"
2263 AND "event" = 'periodic'
2264 AND "voting_requested" = TRUE
2265 ),
2266 "vote_later" = (
2267 SELECT coalesce(sum("weight"), 0)
2268 FROM "direct_interest_snapshot"
2269 WHERE "issue_id" = "issue_id_p"
2270 AND "event" = 'periodic'
2271 AND "voting_requested" = FALSE
2272 )
2273 WHERE "id" = "issue_id_p";
2274 FOR "initiative_id_v" IN
2275 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
2276 LOOP
2277 UPDATE "initiative" SET
2278 "supporter_count" = (
2279 SELECT coalesce(sum("di"."weight"), 0)
2280 FROM "direct_interest_snapshot" AS "di"
2281 JOIN "direct_supporter_snapshot" AS "ds"
2282 ON "di"."member_id" = "ds"."member_id"
2283 WHERE "di"."issue_id" = "issue_id_p"
2284 AND "di"."event" = 'periodic'
2285 AND "ds"."initiative_id" = "initiative_id_v"
2286 AND "ds"."event" = 'periodic'
2287 ),
2288 "informed_supporter_count" = (
2289 SELECT coalesce(sum("di"."weight"), 0)
2290 FROM "direct_interest_snapshot" AS "di"
2291 JOIN "direct_supporter_snapshot" AS "ds"
2292 ON "di"."member_id" = "ds"."member_id"
2293 WHERE "di"."issue_id" = "issue_id_p"
2294 AND "di"."event" = 'periodic'
2295 AND "ds"."initiative_id" = "initiative_id_v"
2296 AND "ds"."event" = 'periodic'
2297 AND "ds"."informed"
2298 ),
2299 "satisfied_supporter_count" = (
2300 SELECT coalesce(sum("di"."weight"), 0)
2301 FROM "direct_interest_snapshot" AS "di"
2302 JOIN "direct_supporter_snapshot" AS "ds"
2303 ON "di"."member_id" = "ds"."member_id"
2304 WHERE "di"."issue_id" = "issue_id_p"
2305 AND "di"."event" = 'periodic'
2306 AND "ds"."initiative_id" = "initiative_id_v"
2307 AND "ds"."event" = 'periodic'
2308 AND "ds"."satisfied"
2309 ),
2310 "satisfied_informed_supporter_count" = (
2311 SELECT coalesce(sum("di"."weight"), 0)
2312 FROM "direct_interest_snapshot" AS "di"
2313 JOIN "direct_supporter_snapshot" AS "ds"
2314 ON "di"."member_id" = "ds"."member_id"
2315 WHERE "di"."issue_id" = "issue_id_p"
2316 AND "di"."event" = 'periodic'
2317 AND "ds"."initiative_id" = "initiative_id_v"
2318 AND "ds"."event" = 'periodic'
2319 AND "ds"."informed"
2320 AND "ds"."satisfied"
2321 )
2322 WHERE "id" = "initiative_id_v";
2323 FOR "suggestion_id_v" IN
2324 SELECT "id" FROM "suggestion"
2325 WHERE "initiative_id" = "initiative_id_v"
2326 LOOP
2327 UPDATE "suggestion" SET
2328 "minus2_unfulfilled_count" = (
2329 SELECT coalesce(sum("snapshot"."weight"), 0)
2330 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2331 ON "opinion"."member_id" = "snapshot"."member_id"
2332 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2333 AND "snapshot"."issue_id" = "issue_id_p"
2334 AND "opinion"."degree" = -2
2335 AND "opinion"."fulfilled" = FALSE
2336 ),
2337 "minus2_fulfilled_count" = (
2338 SELECT coalesce(sum("snapshot"."weight"), 0)
2339 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2340 ON "opinion"."member_id" = "snapshot"."member_id"
2341 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2342 AND "snapshot"."issue_id" = "issue_id_p"
2343 AND "opinion"."degree" = -2
2344 AND "opinion"."fulfilled" = TRUE
2345 ),
2346 "minus1_unfulfilled_count" = (
2347 SELECT coalesce(sum("snapshot"."weight"), 0)
2348 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2349 ON "opinion"."member_id" = "snapshot"."member_id"
2350 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2351 AND "snapshot"."issue_id" = "issue_id_p"
2352 AND "opinion"."degree" = -1
2353 AND "opinion"."fulfilled" = FALSE
2354 ),
2355 "minus1_fulfilled_count" = (
2356 SELECT coalesce(sum("snapshot"."weight"), 0)
2357 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2358 ON "opinion"."member_id" = "snapshot"."member_id"
2359 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2360 AND "snapshot"."issue_id" = "issue_id_p"
2361 AND "opinion"."degree" = -1
2362 AND "opinion"."fulfilled" = TRUE
2363 ),
2364 "plus1_unfulfilled_count" = (
2365 SELECT coalesce(sum("snapshot"."weight"), 0)
2366 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2367 ON "opinion"."member_id" = "snapshot"."member_id"
2368 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2369 AND "snapshot"."issue_id" = "issue_id_p"
2370 AND "opinion"."degree" = 1
2371 AND "opinion"."fulfilled" = FALSE
2372 ),
2373 "plus1_fulfilled_count" = (
2374 SELECT coalesce(sum("snapshot"."weight"), 0)
2375 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2376 ON "opinion"."member_id" = "snapshot"."member_id"
2377 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2378 AND "snapshot"."issue_id" = "issue_id_p"
2379 AND "opinion"."degree" = 1
2380 AND "opinion"."fulfilled" = TRUE
2381 ),
2382 "plus2_unfulfilled_count" = (
2383 SELECT coalesce(sum("snapshot"."weight"), 0)
2384 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2385 ON "opinion"."member_id" = "snapshot"."member_id"
2386 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2387 AND "snapshot"."issue_id" = "issue_id_p"
2388 AND "opinion"."degree" = 2
2389 AND "opinion"."fulfilled" = FALSE
2390 ),
2391 "plus2_fulfilled_count" = (
2392 SELECT coalesce(sum("snapshot"."weight"), 0)
2393 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2394 ON "opinion"."member_id" = "snapshot"."member_id"
2395 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2396 AND "snapshot"."issue_id" = "issue_id_p"
2397 AND "opinion"."degree" = 2
2398 AND "opinion"."fulfilled" = TRUE
2399 )
2400 WHERE "suggestion"."id" = "suggestion_id_v";
2401 END LOOP;
2402 END LOOP;
2403 RETURN;
2404 END;
2405 $$;
2407 COMMENT ON FUNCTION "create_snapshot"
2408 ( "issue"."id"%TYPE )
2409 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.';
2412 CREATE FUNCTION "set_snapshot_event"
2413 ( "issue_id_p" "issue"."id"%TYPE,
2414 "event_p" "snapshot_event" )
2415 RETURNS VOID
2416 LANGUAGE 'plpgsql' VOLATILE AS $$
2417 DECLARE
2418 "event_v" "issue"."latest_snapshot_event"%TYPE;
2419 BEGIN
2420 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
2421 WHERE "id" = "issue_id_p" FOR UPDATE;
2422 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
2423 WHERE "id" = "issue_id_p";
2424 UPDATE "direct_population_snapshot" SET "event" = "event_p"
2425 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2426 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
2427 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2428 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
2429 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2430 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
2431 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2432 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
2433 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2434 RETURN;
2435 END;
2436 $$;
2438 COMMENT ON FUNCTION "set_snapshot_event"
2439 ( "issue"."id"%TYPE,
2440 "snapshot_event" )
2441 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
2445 ---------------------
2446 -- Freezing issues --
2447 ---------------------
2449 CREATE FUNCTION "freeze_after_snapshot"
2450 ( "issue_id_p" "issue"."id"%TYPE )
2451 RETURNS VOID
2452 LANGUAGE 'plpgsql' VOLATILE AS $$
2453 DECLARE
2454 "issue_row" "issue"%ROWTYPE;
2455 "policy_row" "policy"%ROWTYPE;
2456 "initiative_row" "initiative"%ROWTYPE;
2457 BEGIN
2458 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2459 SELECT * INTO "policy_row"
2460 FROM "policy" WHERE "id" = "issue_row"."policy_id";
2461 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
2462 UPDATE "issue" SET
2463 "accepted" = coalesce("accepted", now()),
2464 "half_frozen" = coalesce("half_frozen", now()),
2465 "fully_frozen" = now()
2466 WHERE "id" = "issue_id_p";
2467 FOR "initiative_row" IN
2468 SELECT * FROM "initiative"
2469 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
2470 LOOP
2471 IF
2472 "initiative_row"."satisfied_supporter_count" > 0 AND
2473 "initiative_row"."satisfied_supporter_count" *
2474 "policy_row"."initiative_quorum_den" >=
2475 "issue_row"."population" * "policy_row"."initiative_quorum_num"
2476 THEN
2477 UPDATE "initiative" SET "admitted" = TRUE
2478 WHERE "id" = "initiative_row"."id";
2479 ELSE
2480 UPDATE "initiative" SET "admitted" = FALSE
2481 WHERE "id" = "initiative_row"."id";
2482 END IF;
2483 END LOOP;
2484 IF NOT EXISTS (
2485 SELECT NULL FROM "initiative"
2486 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
2487 ) THEN
2488 PERFORM "close_voting"("issue_id_p");
2489 END IF;
2490 RETURN;
2491 END;
2492 $$;
2494 COMMENT ON FUNCTION "freeze_after_snapshot"
2495 ( "issue"."id"%TYPE )
2496 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
2499 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
2500 RETURNS VOID
2501 LANGUAGE 'plpgsql' VOLATILE AS $$
2502 DECLARE
2503 "issue_row" "issue"%ROWTYPE;
2504 BEGIN
2505 PERFORM "create_snapshot"("issue_id_p");
2506 PERFORM "freeze_after_snapshot"("issue_id_p");
2507 RETURN;
2508 END;
2509 $$;
2511 COMMENT ON FUNCTION "freeze_after_snapshot"
2512 ( "issue"."id"%TYPE )
2513 IS 'Freeze an issue manually (fully) and start voting';
2517 -----------------------
2518 -- Counting of votes --
2519 -----------------------
2522 CREATE FUNCTION "weight_of_added_vote_delegations"
2523 ( "issue_id_p" "issue"."id"%TYPE,
2524 "member_id_p" "member"."id"%TYPE,
2525 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
2526 RETURNS "direct_voter"."weight"%TYPE
2527 LANGUAGE 'plpgsql' VOLATILE AS $$
2528 DECLARE
2529 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2530 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
2531 "weight_v" INT4;
2532 "sub_weight_v" INT4;
2533 BEGIN
2534 "weight_v" := 0;
2535 FOR "issue_delegation_row" IN
2536 SELECT * FROM "issue_delegation"
2537 WHERE "trustee_id" = "member_id_p"
2538 AND "issue_id" = "issue_id_p"
2539 LOOP
2540 IF NOT EXISTS (
2541 SELECT NULL FROM "direct_voter"
2542 WHERE "member_id" = "issue_delegation_row"."truster_id"
2543 AND "issue_id" = "issue_id_p"
2544 ) AND NOT EXISTS (
2545 SELECT NULL FROM "delegating_voter"
2546 WHERE "member_id" = "issue_delegation_row"."truster_id"
2547 AND "issue_id" = "issue_id_p"
2548 ) THEN
2549 "delegate_member_ids_v" :=
2550 "member_id_p" || "delegate_member_ids_p";
2551 INSERT INTO "delegating_voter" (
2552 "issue_id",
2553 "member_id",
2554 "scope",
2555 "delegate_member_ids"
2556 ) VALUES (
2557 "issue_id_p",
2558 "issue_delegation_row"."truster_id",
2559 "issue_delegation_row"."scope",
2560 "delegate_member_ids_v"
2561 );
2562 "sub_weight_v" := 1 +
2563 "weight_of_added_vote_delegations"(
2564 "issue_id_p",
2565 "issue_delegation_row"."truster_id",
2566 "delegate_member_ids_v"
2567 );
2568 UPDATE "delegating_voter"
2569 SET "weight" = "sub_weight_v"
2570 WHERE "issue_id" = "issue_id_p"
2571 AND "member_id" = "issue_delegation_row"."truster_id";
2572 "weight_v" := "weight_v" + "sub_weight_v";
2573 END IF;
2574 END LOOP;
2575 RETURN "weight_v";
2576 END;
2577 $$;
2579 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
2580 ( "issue"."id"%TYPE,
2581 "member"."id"%TYPE,
2582 "delegating_voter"."delegate_member_ids"%TYPE )
2583 IS 'Helper function for "add_vote_delegations" function';
2586 CREATE FUNCTION "add_vote_delegations"
2587 ( "issue_id_p" "issue"."id"%TYPE )
2588 RETURNS VOID
2589 LANGUAGE 'plpgsql' VOLATILE AS $$
2590 DECLARE
2591 "member_id_v" "member"."id"%TYPE;
2592 BEGIN
2593 FOR "member_id_v" IN
2594 SELECT "member_id" FROM "direct_voter"
2595 WHERE "issue_id" = "issue_id_p"
2596 LOOP
2597 UPDATE "direct_voter" SET
2598 "weight" = "weight" + "weight_of_added_vote_delegations"(
2599 "issue_id_p",
2600 "member_id_v",
2601 '{}'
2602 )
2603 WHERE "member_id" = "member_id_v"
2604 AND "issue_id" = "issue_id_p";
2605 END LOOP;
2606 RETURN;
2607 END;
2608 $$;
2610 COMMENT ON FUNCTION "add_vote_delegations"
2611 ( "issue_id_p" "issue"."id"%TYPE )
2612 IS 'Helper function for "close_voting" function';
2615 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
2616 RETURNS VOID
2617 LANGUAGE 'plpgsql' VOLATILE AS $$
2618 DECLARE
2619 "issue_row" "issue"%ROWTYPE;
2620 "member_id_v" "member"."id"%TYPE;
2621 BEGIN
2622 PERFORM "global_lock"();
2623 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2624 DELETE FROM "delegating_voter"
2625 WHERE "issue_id" = "issue_id_p";
2626 DELETE FROM "direct_voter"
2627 WHERE "issue_id" = "issue_id_p"
2628 AND "autoreject" = TRUE;
2629 DELETE FROM "direct_voter" USING "member"
2630 WHERE "direct_voter"."member_id" = "member"."id"
2631 AND "direct_voter"."issue_id" = "issue_id_p"
2632 AND "member"."active" = FALSE;
2633 UPDATE "direct_voter" SET "weight" = 1
2634 WHERE "issue_id" = "issue_id_p";
2635 PERFORM "add_vote_delegations"("issue_id_p");
2636 FOR "member_id_v" IN
2637 SELECT "interest"."member_id"
2638 FROM "interest"
2639 LEFT JOIN "direct_voter"
2640 ON "interest"."member_id" = "direct_voter"."member_id"
2641 AND "interest"."issue_id" = "direct_voter"."issue_id"
2642 LEFT JOIN "delegating_voter"
2643 ON "interest"."member_id" = "delegating_voter"."member_id"
2644 AND "interest"."issue_id" = "delegating_voter"."issue_id"
2645 WHERE "interest"."issue_id" = "issue_id_p"
2646 AND "interest"."autoreject" = TRUE
2647 AND "direct_voter"."member_id" ISNULL
2648 AND "delegating_voter"."member_id" ISNULL
2649 UNION SELECT "membership"."member_id"
2650 FROM "membership"
2651 LEFT JOIN "interest"
2652 ON "membership"."member_id" = "interest"."member_id"
2653 AND "interest"."issue_id" = "issue_id_p"
2654 LEFT JOIN "direct_voter"
2655 ON "membership"."member_id" = "direct_voter"."member_id"
2656 AND "direct_voter"."issue_id" = "issue_id_p"
2657 LEFT JOIN "delegating_voter"
2658 ON "membership"."member_id" = "delegating_voter"."member_id"
2659 AND "delegating_voter"."issue_id" = "issue_id_p"
2660 WHERE "membership"."area_id" = "issue_row"."area_id"
2661 AND "membership"."autoreject" = TRUE
2662 AND "interest"."autoreject" ISNULL
2663 AND "direct_voter"."member_id" ISNULL
2664 AND "delegating_voter"."member_id" ISNULL
2665 LOOP
2666 INSERT INTO "direct_voter"
2667 ("member_id", "issue_id", "weight", "autoreject") VALUES
2668 ("member_id_v", "issue_id_p", 1, TRUE);
2669 INSERT INTO "vote" (
2670 "member_id",
2671 "issue_id",
2672 "initiative_id",
2673 "grade"
2674 ) SELECT
2675 "member_id_v" AS "member_id",
2676 "issue_id_p" AS "issue_id",
2677 "id" AS "initiative_id",
2678 -1 AS "grade"
2679 FROM "initiative" WHERE "issue_id" = "issue_id_p";
2680 END LOOP;
2681 PERFORM "add_vote_delegations"("issue_id_p");
2682 UPDATE "issue" SET
2683 "voter_count" = (
2684 SELECT coalesce(sum("weight"), 0)
2685 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
2686 )
2687 WHERE "id" = "issue_id_p";
2688 UPDATE "initiative" SET
2689 "positive_votes" = "vote_counts"."positive_votes",
2690 "negative_votes" = "vote_counts"."negative_votes",
2691 "agreed" = CASE WHEN "majority_strict" THEN
2692 "vote_counts"."positive_votes" * "majority_den" >
2693 "majority_num" *
2694 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
2695 ELSE
2696 "vote_counts"."positive_votes" * "majority_den" >=
2697 "majority_num" *
2698 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
2699 END
2700 FROM
2701 ( SELECT
2702 "initiative"."id" AS "initiative_id",
2703 coalesce(
2704 sum(
2705 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
2706 ),
2707 0
2708 ) AS "positive_votes",
2709 coalesce(
2710 sum(
2711 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
2712 ),
2713 0
2714 ) AS "negative_votes"
2715 FROM "initiative"
2716 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
2717 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
2718 LEFT JOIN "direct_voter"
2719 ON "direct_voter"."issue_id" = "initiative"."issue_id"
2720 LEFT JOIN "vote"
2721 ON "vote"."initiative_id" = "initiative"."id"
2722 AND "vote"."member_id" = "direct_voter"."member_id"
2723 WHERE "initiative"."issue_id" = "issue_id_p"
2724 AND "initiative"."admitted" -- NOTE: NULL case is handled too
2725 GROUP BY "initiative"."id"
2726 ) AS "vote_counts",
2727 "issue",
2728 "policy"
2729 WHERE "vote_counts"."initiative_id" = "initiative"."id"
2730 AND "issue"."id" = "initiative"."issue_id"
2731 AND "policy"."id" = "issue"."policy_id";
2732 UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p";
2733 END;
2734 $$;
2736 COMMENT ON FUNCTION "close_voting"
2737 ( "issue"."id"%TYPE )
2738 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.';
2741 CREATE FUNCTION "defeat_strength"
2742 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
2743 RETURNS INT8
2744 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2745 BEGIN
2746 IF "positive_votes_p" > "negative_votes_p" THEN
2747 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
2748 ELSIF "positive_votes_p" = "negative_votes_p" THEN
2749 RETURN 0;
2750 ELSE
2751 RETURN -1;
2752 END IF;
2753 END;
2754 $$;
2756 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';
2759 CREATE FUNCTION "array_init_string"("dim_p" INTEGER)
2760 RETURNS TEXT
2761 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2762 DECLARE
2763 "i" INTEGER;
2764 "ary_text_v" TEXT;
2765 BEGIN
2766 IF "dim_p" >= 1 THEN
2767 "ary_text_v" := '{NULL';
2768 "i" := "dim_p";
2769 LOOP
2770 "i" := "i" - 1;
2771 EXIT WHEN "i" = 0;
2772 "ary_text_v" := "ary_text_v" || ',NULL';
2773 END LOOP;
2774 "ary_text_v" := "ary_text_v" || '}';
2775 RETURN "ary_text_v";
2776 ELSE
2777 RAISE EXCEPTION 'Dimension needs to be at least 1.';
2778 END IF;
2779 END;
2780 $$;
2782 COMMENT ON FUNCTION "array_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
2785 CREATE FUNCTION "square_matrix_init_string"("dim_p" INTEGER)
2786 RETURNS TEXT
2787 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2788 DECLARE
2789 "i" INTEGER;
2790 "row_text_v" TEXT;
2791 "ary_text_v" TEXT;
2792 BEGIN
2793 IF "dim_p" >= 1 THEN
2794 "row_text_v" := '{NULL';
2795 "i" := "dim_p";
2796 LOOP
2797 "i" := "i" - 1;
2798 EXIT WHEN "i" = 0;
2799 "row_text_v" := "row_text_v" || ',NULL';
2800 END LOOP;
2801 "row_text_v" := "row_text_v" || '}';
2802 "ary_text_v" := '{' || "row_text_v";
2803 "i" := "dim_p";
2804 LOOP
2805 "i" := "i" - 1;
2806 EXIT WHEN "i" = 0;
2807 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
2808 END LOOP;
2809 "ary_text_v" := "ary_text_v" || '}';
2810 RETURN "ary_text_v";
2811 ELSE
2812 RAISE EXCEPTION 'Dimension needs to be at least 1.';
2813 END IF;
2814 END;
2815 $$;
2817 COMMENT ON FUNCTION "square_matrix_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
2820 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
2821 RETURNS VOID
2822 LANGUAGE 'plpgsql' VOLATILE AS $$
2823 DECLARE
2824 "dimension_v" INTEGER;
2825 "vote_matrix" INT4[][]; -- absolute votes
2826 "matrix" INT8[][]; -- defeat strength / best paths
2827 "i" INTEGER;
2828 "j" INTEGER;
2829 "k" INTEGER;
2830 "battle_row" "battle"%ROWTYPE;
2831 "rank_ary" INT4[];
2832 "rank_v" INT4;
2833 "done_v" INTEGER;
2834 "winners_ary" INTEGER[];
2835 "initiative_id_v" "initiative"."id"%TYPE;
2836 BEGIN
2837 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2838 SELECT count(1) INTO "dimension_v" FROM "initiative"
2839 WHERE "issue_id" = "issue_id_p" AND "agreed";
2840 IF "dimension_v" = 1 THEN
2841 UPDATE "initiative" SET "rank" = 1
2842 WHERE "issue_id" = "issue_id_p" AND "agreed";
2843 ELSIF "dimension_v" > 1 THEN
2844 -- Create "vote_matrix" with absolute number of votes in pairwise
2845 -- comparison:
2846 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2847 "i" := 1;
2848 "j" := 2;
2849 FOR "battle_row" IN
2850 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
2851 ORDER BY "winning_initiative_id", "losing_initiative_id"
2852 LOOP
2853 "vote_matrix"["i"]["j"] := "battle_row"."count";
2854 IF "j" = "dimension_v" THEN
2855 "i" := "i" + 1;
2856 "j" := 1;
2857 ELSE
2858 "j" := "j" + 1;
2859 IF "j" = "i" THEN
2860 "j" := "j" + 1;
2861 END IF;
2862 END IF;
2863 END LOOP;
2864 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
2865 RAISE EXCEPTION 'Wrong battle count (should not happen)';
2866 END IF;
2867 -- Store defeat strengths in "matrix" using "defeat_strength"
2868 -- function:
2869 "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2870 "i" := 1;
2871 LOOP
2872 "j" := 1;
2873 LOOP
2874 IF "i" != "j" THEN
2875 "matrix"["i"]["j"] := "defeat_strength"(
2876 "vote_matrix"["i"]["j"],
2877 "vote_matrix"["j"]["i"]
2878 );
2879 END IF;
2880 EXIT WHEN "j" = "dimension_v";
2881 "j" := "j" + 1;
2882 END LOOP;
2883 EXIT WHEN "i" = "dimension_v";
2884 "i" := "i" + 1;
2885 END LOOP;
2886 -- Find best paths:
2887 "i" := 1;
2888 LOOP
2889 "j" := 1;
2890 LOOP
2891 IF "i" != "j" THEN
2892 "k" := 1;
2893 LOOP
2894 IF "i" != "k" AND "j" != "k" THEN
2895 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
2896 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
2897 "matrix"["j"]["k"] := "matrix"["j"]["i"];
2898 END IF;
2899 ELSE
2900 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
2901 "matrix"["j"]["k"] := "matrix"["i"]["k"];
2902 END IF;
2903 END IF;
2904 END IF;
2905 EXIT WHEN "k" = "dimension_v";
2906 "k" := "k" + 1;
2907 END LOOP;
2908 END IF;
2909 EXIT WHEN "j" = "dimension_v";
2910 "j" := "j" + 1;
2911 END LOOP;
2912 EXIT WHEN "i" = "dimension_v";
2913 "i" := "i" + 1;
2914 END LOOP;
2915 -- Determine order of winners:
2916 "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2917 "rank_v" := 1;
2918 "done_v" := 0;
2919 LOOP
2920 "winners_ary" := '{}';
2921 "i" := 1;
2922 LOOP
2923 IF "rank_ary"["i"] ISNULL THEN
2924 "j" := 1;
2925 LOOP
2926 IF
2927 "i" != "j" AND
2928 "rank_ary"["j"] ISNULL AND
2929 "matrix"["j"]["i"] > "matrix"["i"]["j"]
2930 THEN
2931 -- someone else is better
2932 EXIT;
2933 END IF;
2934 IF "j" = "dimension_v" THEN
2935 -- noone is better
2936 "winners_ary" := "winners_ary" || "i";
2937 EXIT;
2938 END IF;
2939 "j" := "j" + 1;
2940 END LOOP;
2941 END IF;
2942 EXIT WHEN "i" = "dimension_v";
2943 "i" := "i" + 1;
2944 END LOOP;
2945 "i" := 1;
2946 LOOP
2947 "rank_ary"["winners_ary"["i"]] := "rank_v";
2948 "done_v" := "done_v" + 1;
2949 EXIT WHEN "i" = array_upper("winners_ary", 1);
2950 "i" := "i" + 1;
2951 END LOOP;
2952 EXIT WHEN "done_v" = "dimension_v";
2953 "rank_v" := "rank_v" + 1;
2954 END LOOP;
2955 -- write preliminary ranks:
2956 "i" := 1;
2957 FOR "initiative_id_v" IN
2958 SELECT "id" FROM "initiative"
2959 WHERE "issue_id" = "issue_id_p" AND "agreed"
2960 ORDER BY "id"
2961 LOOP
2962 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
2963 WHERE "id" = "initiative_id_v";
2964 "i" := "i" + 1;
2965 END LOOP;
2966 IF "i" != "dimension_v" + 1 THEN
2967 RAISE EXCEPTION 'Wrong winner count (should not happen)';
2968 END IF;
2969 -- straighten ranks (start counting with 1, no equal ranks):
2970 "rank_v" := 1;
2971 FOR "initiative_id_v" IN
2972 SELECT "id" FROM "initiative"
2973 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
2974 ORDER BY
2975 "rank",
2976 "vote_ratio"("positive_votes", "negative_votes") DESC,
2977 "id"
2978 LOOP
2979 UPDATE "initiative" SET "rank" = "rank_v"
2980 WHERE "id" = "initiative_id_v";
2981 "rank_v" := "rank_v" + 1;
2982 END LOOP;
2983 END IF;
2984 -- mark issue as finished
2985 UPDATE "issue" SET "ranks_available" = TRUE
2986 WHERE "id" = "issue_id_p";
2987 RETURN;
2988 END;
2989 $$;
2991 COMMENT ON FUNCTION "calculate_ranks"
2992 ( "issue"."id"%TYPE )
2993 IS 'Determine ranking (Votes have to be counted first)';
2997 -----------------------------
2998 -- Automatic state changes --
2999 -----------------------------
3002 CREATE FUNCTION "check_issue"
3003 ( "issue_id_p" "issue"."id"%TYPE )
3004 RETURNS VOID
3005 LANGUAGE 'plpgsql' VOLATILE AS $$
3006 DECLARE
3007 "issue_row" "issue"%ROWTYPE;
3008 "policy_row" "policy"%ROWTYPE;
3009 "voting_requested_v" BOOLEAN;
3010 BEGIN
3011 PERFORM "global_lock"();
3012 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3013 -- only process open issues:
3014 IF "issue_row"."closed" ISNULL THEN
3015 SELECT * INTO "policy_row" FROM "policy"
3016 WHERE "id" = "issue_row"."policy_id";
3017 -- create a snapshot, unless issue is already fully frozen:
3018 IF "issue_row"."fully_frozen" ISNULL THEN
3019 PERFORM "create_snapshot"("issue_id_p");
3020 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3021 END IF;
3022 -- eventually close or accept issues, which have not been accepted:
3023 IF "issue_row"."accepted" ISNULL THEN
3024 IF EXISTS (
3025 SELECT NULL FROM "initiative"
3026 WHERE "issue_id" = "issue_id_p"
3027 AND "supporter_count" > 0
3028 AND "supporter_count" * "policy_row"."issue_quorum_den"
3029 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
3030 ) THEN
3031 -- accept issues, if supporter count is high enough
3032 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3033 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later
3034 UPDATE "issue" SET "accepted" = "issue_row"."accepted"
3035 WHERE "id" = "issue_row"."id";
3036 ELSIF
3037 now() >= "issue_row"."created" + "issue_row"."admission_time"
3038 THEN
3039 -- close issues, if admission time has expired
3040 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3041 UPDATE "issue" SET "closed" = now()
3042 WHERE "id" = "issue_row"."id";
3043 END IF;
3044 END IF;
3045 -- eventually half freeze issues:
3046 IF
3047 -- NOTE: issue can't be closed at this point, if it has been accepted
3048 "issue_row"."accepted" NOTNULL AND
3049 "issue_row"."half_frozen" ISNULL
3050 THEN
3051 SELECT
3052 CASE
3053 WHEN "vote_now" * 2 > "issue_row"."population" THEN
3054 TRUE
3055 WHEN "vote_later" * 2 > "issue_row"."population" THEN
3056 FALSE
3057 ELSE NULL
3058 END
3059 INTO "voting_requested_v"
3060 FROM "issue" WHERE "id" = "issue_id_p";
3061 IF
3062 "voting_requested_v" OR (
3063 "voting_requested_v" ISNULL AND
3064 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
3065 )
3066 THEN
3067 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
3068 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later
3069 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
3070 WHERE "id" = "issue_row"."id";
3071 END IF;
3072 END IF;
3073 -- close issues after some time, if all initiatives have been revoked:
3074 IF
3075 "issue_row"."closed" ISNULL AND
3076 NOT EXISTS (
3077 -- all initiatives are revoked
3078 SELECT NULL FROM "initiative"
3079 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3080 ) AND (
3081 NOT EXISTS (
3082 -- and no initiatives have been revoked lately
3083 SELECT NULL FROM "initiative"
3084 WHERE "issue_id" = "issue_id_p"
3085 AND now() < "revoked" + "issue_row"."verification_time"
3086 ) OR (
3087 -- or verification time has elapsed
3088 "issue_row"."half_frozen" NOTNULL AND
3089 "issue_row"."fully_frozen" ISNULL AND
3090 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3091 )
3092 )
3093 THEN
3094 "issue_row"."closed" = now(); -- NOTE: "issue_row" used later
3095 UPDATE "issue" SET "closed" = "issue_row"."closed"
3096 WHERE "id" = "issue_row"."id";
3097 END IF;
3098 -- fully freeze issue after verification time:
3099 IF
3100 "issue_row"."half_frozen" NOTNULL AND
3101 "issue_row"."fully_frozen" ISNULL AND
3102 "issue_row"."closed" ISNULL AND
3103 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3104 THEN
3105 PERFORM "freeze_after_snapshot"("issue_id_p");
3106 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
3107 END IF;
3108 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3109 -- close issue by calling close_voting(...) after voting time:
3110 IF
3111 "issue_row"."closed" ISNULL AND
3112 "issue_row"."fully_frozen" NOTNULL AND
3113 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
3114 THEN
3115 PERFORM "close_voting"("issue_id_p");
3116 END IF;
3117 END IF;
3118 RETURN;
3119 END;
3120 $$;
3122 COMMENT ON FUNCTION "check_issue"
3123 ( "issue"."id"%TYPE )
3124 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.';
3127 CREATE FUNCTION "check_everything"()
3128 RETURNS VOID
3129 LANGUAGE 'plpgsql' VOLATILE AS $$
3130 DECLARE
3131 "issue_id_v" "issue"."id"%TYPE;
3132 BEGIN
3133 DELETE FROM "expired_session";
3134 PERFORM "calculate_member_counts"();
3135 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
3136 PERFORM "check_issue"("issue_id_v");
3137 END LOOP;
3138 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
3139 PERFORM "calculate_ranks"("issue_id_v");
3140 END LOOP;
3141 RETURN;
3142 END;
3143 $$;
3145 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.';
3149 ------------------------------
3150 -- Deletion of private data --
3151 ------------------------------
3154 CREATE FUNCTION "delete_private_data"()
3155 RETURNS VOID
3156 LANGUAGE 'plpgsql' VOLATILE AS $$
3157 DECLARE
3158 "issue_id_v" "issue"."id"%TYPE;
3159 BEGIN
3160 UPDATE "member" SET
3161 "login" = 'login' || "id"::text,
3162 "password" = NULL,
3163 "notify_email" = NULL,
3164 "notify_email_unconfirmed" = NULL,
3165 "notify_email_secret" = NULL,
3166 "notify_email_secret_expiry" = NULL,
3167 "password_reset_secret" = NULL,
3168 "password_reset_secret_expiry" = NULL,
3169 "organizational_unit" = NULL,
3170 "internal_posts" = NULL,
3171 "realname" = NULL,
3172 "birthday" = NULL,
3173 "address" = NULL,
3174 "email" = NULL,
3175 "xmpp_address" = NULL,
3176 "website" = NULL,
3177 "phone" = NULL,
3178 "mobile_phone" = NULL,
3179 "profession" = NULL,
3180 "external_memberships" = NULL,
3181 "external_posts" = NULL,
3182 "statement" = NULL;
3183 -- "text_search_data" is updated by triggers
3184 DELETE FROM "session";
3185 DELETE FROM "invite_code";
3186 DELETE FROM "contact";
3187 DELETE FROM "setting";
3188 DELETE FROM "member_image";
3189 DELETE FROM "direct_voter" USING "issue"
3190 WHERE "direct_voter"."issue_id" = "issue"."id"
3191 AND "issue"."closed" ISNULL;
3192 RETURN;
3193 END;
3194 $$;
3196 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.';
3200 COMMIT;