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