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