liquid_feedback_core
view core.sql @ 22:063baac35f79
Copy admission_, discussion_, verification_ and voting_time values into issue
and use copied values for issue state changes
and use copied values for issue state changes
| author | jbe |
|---|---|
| date | Sat Feb 06 03:31:13 2010 +0100 (2010-02-06) |
| parents | 406090b1ed8e |
| children | 137c98fa0b4f |
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 ('beta19-dev', NULL, NULL, NULL))
10 AS "subquery"("string", "major", "minor", "revision");
14 ----------------------
15 -- Full text search --
16 ----------------------
19 CREATE FUNCTION "text_search_query"("query_text_p" TEXT)
20 RETURNS TSQUERY
21 LANGUAGE 'plpgsql' IMMUTABLE AS $$
22 BEGIN
23 RETURN plainto_tsquery('pg_catalog.simple', "query_text_p");
24 END;
25 $$;
27 COMMENT ON FUNCTION "text_search_query"(TEXT) IS 'Usage: WHERE "text_search_data" @@ "text_search_query"(''<user query>'')';
30 CREATE FUNCTION "highlight"
31 ( "body_p" TEXT,
32 "query_text_p" TEXT )
33 RETURNS TEXT
34 LANGUAGE 'plpgsql' IMMUTABLE AS $$
35 BEGIN
36 RETURN ts_headline(
37 'pg_catalog.simple',
38 replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'),
39 "text_search_query"("query_text_p"),
40 'StartSel=* StopSel=* HighlightAll=TRUE' );
41 END;
42 $$;
44 COMMENT ON FUNCTION "highlight"
45 ( "body_p" TEXT,
46 "query_text_p" TEXT )
47 IS 'For a given a user query this function encapsulates all matches with asterisks. Asterisks and backslashes being already present are preceeded with one extra backslash.';
51 -------------------------
52 -- Tables and indicies --
53 -------------------------
56 CREATE TABLE "member" (
57 "id" SERIAL4 PRIMARY KEY,
58 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
59 "login" TEXT NOT NULL UNIQUE,
60 "password" TEXT,
61 "active" BOOLEAN NOT NULL DEFAULT TRUE,
62 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
63 "notify_email" TEXT,
64 "notify_email_unconfirmed" TEXT,
65 "notify_email_secret" TEXT UNIQUE,
66 "notify_email_secret_expiry" TIMESTAMPTZ,
67 "password_reset_secret" TEXT UNIQUE,
68 "password_reset_secret_expiry" TIMESTAMPTZ,
69 "name" TEXT NOT NULL UNIQUE,
70 "identification" TEXT UNIQUE,
71 "organizational_unit" TEXT,
72 "internal_posts" TEXT,
73 "realname" TEXT,
74 "birthday" DATE,
75 "address" TEXT,
76 "email" TEXT,
77 "xmpp_address" TEXT,
78 "website" TEXT,
79 "phone" TEXT,
80 "mobile_phone" TEXT,
81 "profession" TEXT,
82 "external_memberships" TEXT,
83 "external_posts" TEXT,
84 "statement" TEXT,
85 "text_search_data" TSVECTOR );
86 CREATE INDEX "member_active_idx" ON "member" ("active");
87 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
88 CREATE TRIGGER "update_text_search_data"
89 BEFORE INSERT OR UPDATE ON "member"
90 FOR EACH ROW EXECUTE PROCEDURE
91 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
92 "name", "identification", "organizational_unit", "internal_posts",
93 "realname", "external_memberships", "external_posts", "statement" );
95 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
97 COMMENT ON COLUMN "member"."login" IS 'Login name';
98 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
99 COMMENT ON COLUMN "member"."active" IS 'Inactive members can not login and their supports/votes are not counted by the system.';
100 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
101 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
102 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
103 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
104 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
105 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member';
106 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
107 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
108 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
109 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
110 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
111 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
112 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
113 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his homepage within the system';
116 CREATE TABLE "member_history" (
117 "id" SERIAL8 PRIMARY KEY,
118 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
119 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
120 "login" TEXT NOT NULL,
121 "name" TEXT NOT NULL );
123 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and login names of members';
125 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
126 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the name and login had been valid';
129 CREATE TABLE "invite_code" (
130 "code" TEXT PRIMARY KEY,
131 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
132 "used" TIMESTAMPTZ,
133 "member_id" INT4 UNIQUE REFERENCES "member" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
134 "comment" TEXT,
135 CONSTRAINT "only_used_codes_may_refer_to_member" CHECK ("used" NOTNULL OR "member_id" ISNULL) );
137 COMMENT ON TABLE "invite_code" IS 'Invite codes can be used once to create a new member account.';
139 COMMENT ON COLUMN "invite_code"."code" IS 'Secret code';
140 COMMENT ON COLUMN "invite_code"."created" IS 'Time of creation of the secret code';
141 COMMENT ON COLUMN "invite_code"."used" IS 'NULL, if not used yet, otherwise tells when this code was used to create a member account';
142 COMMENT ON COLUMN "invite_code"."member_id" IS 'References the member whose account was created with this code';
143 COMMENT ON COLUMN "invite_code"."comment" IS 'Comment on the code, which is to be used for administrative reasons only';
146 CREATE TABLE "setting" (
147 PRIMARY KEY ("member_id", "key"),
148 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
149 "key" TEXT NOT NULL,
150 "value" TEXT NOT NULL );
151 CREATE INDEX "setting_key_idx" ON "setting" ("key");
153 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific member setting as a string';
155 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
158 CREATE TABLE "setting_map" (
159 PRIMARY KEY ("member_id", "key", "subkey"),
160 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
161 "key" TEXT NOT NULL,
162 "subkey" TEXT NOT NULL,
163 "value" TEXT NOT NULL );
164 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
166 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific member setting as a map of key value pairs';
168 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
169 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
170 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
173 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
175 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
178 CREATE TABLE "member_image" (
179 PRIMARY KEY ("member_id", "image_type", "scaled"),
180 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
181 "image_type" "member_image_type",
182 "scaled" BOOLEAN,
183 "content_type" TEXT,
184 "data" BYTEA NOT NULL );
186 COMMENT ON TABLE "member_image" IS 'Images of members';
188 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
191 CREATE TABLE "member_count" (
192 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
193 "total_count" INT4 NOT NULL );
195 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';
197 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
198 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
201 CREATE TABLE "contact" (
202 PRIMARY KEY ("member_id", "other_member_id"),
203 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
204 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
205 "public" BOOLEAN NOT NULL DEFAULT FALSE,
206 CONSTRAINT "cant_save_yourself_as_contact"
207 CHECK ("member_id" != "other_member_id") );
209 COMMENT ON TABLE "contact" IS 'Contact lists';
211 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
212 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
213 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
216 CREATE TABLE "session" (
217 "ident" TEXT PRIMARY KEY,
218 "additional_secret" TEXT,
219 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
220 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
221 "lang" TEXT );
222 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
224 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';
226 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
227 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
228 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
229 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
232 CREATE TABLE "policy" (
233 "id" SERIAL4 PRIMARY KEY,
234 "index" INT4 NOT NULL,
235 "active" BOOLEAN NOT NULL DEFAULT TRUE,
236 "name" TEXT NOT NULL UNIQUE,
237 "description" TEXT NOT NULL DEFAULT '',
238 "admission_time" INTERVAL NOT NULL,
239 "discussion_time" INTERVAL NOT NULL,
240 "verification_time" INTERVAL NOT NULL,
241 "voting_time" INTERVAL NOT NULL,
242 "issue_quorum_num" INT4 NOT NULL,
243 "issue_quorum_den" INT4 NOT NULL,
244 "initiative_quorum_num" INT4 NOT NULL,
245 "initiative_quorum_den" INT4 NOT NULL,
246 "majority_num" INT4 NOT NULL DEFAULT 1,
247 "majority_den" INT4 NOT NULL DEFAULT 2,
248 "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE );
249 CREATE INDEX "policy_active_idx" ON "policy" ("active");
251 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
253 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
254 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
255 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"';
256 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"';
257 COMMENT ON COLUMN "policy"."verification_time" IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
258 COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "fully_frozen" but not "closed"';
259 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"';
260 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"';
261 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
262 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
263 COMMENT ON COLUMN "policy"."majority_num" IS 'Numerator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
264 COMMENT ON COLUMN "policy"."majority_den" IS 'Denominator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
265 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.';
268 CREATE TABLE "area" (
269 "id" SERIAL4 PRIMARY KEY,
270 "active" BOOLEAN NOT NULL DEFAULT TRUE,
271 "name" TEXT NOT NULL,
272 "description" TEXT NOT NULL DEFAULT '',
273 "direct_member_count" INT4,
274 "member_weight" INT4,
275 "autoreject_weight" INT4,
276 "text_search_data" TSVECTOR );
277 CREATE INDEX "area_active_idx" ON "area" ("active");
278 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
279 CREATE TRIGGER "update_text_search_data"
280 BEFORE INSERT OR UPDATE ON "area"
281 FOR EACH ROW EXECUTE PROCEDURE
282 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
283 "name", "description" );
285 COMMENT ON TABLE "area" IS 'Subject areas';
287 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
288 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"';
289 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
290 COMMENT ON COLUMN "area"."autoreject_weight" IS 'Sum of weight of members using the autoreject feature';
293 CREATE TABLE "allowed_policy" (
294 PRIMARY KEY ("area_id", "policy_id"),
295 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
296 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
297 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
298 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
300 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
302 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
305 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
307 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';
310 CREATE TABLE "issue" (
311 "id" SERIAL4 PRIMARY KEY,
312 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
313 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
314 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
315 "accepted" TIMESTAMPTZ,
316 "half_frozen" TIMESTAMPTZ,
317 "fully_frozen" TIMESTAMPTZ,
318 "closed" TIMESTAMPTZ,
319 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
320 "admission_time" INTERVAL NOT NULL,
321 "discussion_time" INTERVAL NOT NULL,
322 "verification_time" INTERVAL NOT NULL,
323 "voting_time" INTERVAL NOT NULL,
324 "snapshot" TIMESTAMPTZ,
325 "latest_snapshot_event" "snapshot_event",
326 "population" INT4,
327 "vote_now" INT4,
328 "vote_later" INT4,
329 "voter_count" INT4,
330 CONSTRAINT "valid_state" CHECK (
331 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
332 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
333 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
334 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
335 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
336 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
337 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ),
338 CONSTRAINT "state_change_order" CHECK (
339 "created" <= "accepted" AND
340 "accepted" <= "half_frozen" AND
341 "half_frozen" <= "fully_frozen" AND
342 "fully_frozen" <= "closed" ),
343 CONSTRAINT "last_snapshot_on_full_freeze"
344 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
345 CONSTRAINT "freeze_requires_snapshot"
346 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
347 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
348 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
349 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
350 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
351 CREATE INDEX "issue_created_idx" ON "issue" ("created");
352 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
353 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
354 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
355 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
356 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
357 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
359 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
361 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
362 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.';
363 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.';
364 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.';
365 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
366 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
367 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
368 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
369 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
370 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';
371 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';
372 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
373 COMMENT ON COLUMN "issue"."vote_now" IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"';
374 COMMENT ON COLUMN "issue"."vote_later" IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"';
375 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';
378 CREATE TABLE "initiative" (
379 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
380 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
381 "id" SERIAL4 PRIMARY KEY,
382 "name" TEXT NOT NULL,
383 "discussion_url" TEXT,
384 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
385 "revoked" TIMESTAMPTZ,
386 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
387 "admitted" BOOLEAN,
388 "supporter_count" INT4,
389 "informed_supporter_count" INT4,
390 "satisfied_supporter_count" INT4,
391 "satisfied_informed_supporter_count" INT4,
392 "positive_votes" INT4,
393 "negative_votes" INT4,
394 "agreed" BOOLEAN,
395 "rank" INT4,
396 "text_search_data" TSVECTOR,
397 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
398 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
399 CONSTRAINT "revoked_initiatives_cant_be_admitted"
400 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
401 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"
402 CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "agreed" ISNULL)),
403 CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"
404 CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL),
405 CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"
406 CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) );
407 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
408 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
409 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
410 CREATE TRIGGER "update_text_search_data"
411 BEFORE INSERT OR UPDATE ON "initiative"
412 FOR EACH ROW EXECUTE PROCEDURE
413 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
414 "name", "discussion_url");
416 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.';
418 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
419 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
420 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
421 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
422 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
423 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
424 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
425 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
426 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
427 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"';
428 COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';
431 CREATE TABLE "draft" (
432 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
433 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
434 "id" SERIAL8 PRIMARY KEY,
435 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
436 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
437 "formatting_engine" TEXT,
438 "content" TEXT NOT NULL,
439 "text_search_data" TSVECTOR );
440 CREATE INDEX "draft_created_idx" ON "draft" ("created");
441 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
442 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
443 CREATE TRIGGER "update_text_search_data"
444 BEFORE INSERT OR UPDATE ON "draft"
445 FOR EACH ROW EXECUTE PROCEDURE
446 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
448 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.';
450 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
451 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
454 CREATE TABLE "suggestion" (
455 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
456 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
457 "id" SERIAL8 PRIMARY KEY,
458 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
459 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
460 "name" TEXT NOT NULL,
461 "description" TEXT NOT NULL DEFAULT '',
462 "text_search_data" TSVECTOR,
463 "minus2_unfulfilled_count" INT4,
464 "minus2_fulfilled_count" INT4,
465 "minus1_unfulfilled_count" INT4,
466 "minus1_fulfilled_count" INT4,
467 "plus1_unfulfilled_count" INT4,
468 "plus1_fulfilled_count" INT4,
469 "plus2_unfulfilled_count" INT4,
470 "plus2_fulfilled_count" INT4 );
471 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
472 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
473 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
474 CREATE TRIGGER "update_text_search_data"
475 BEFORE INSERT OR UPDATE ON "suggestion"
476 FOR EACH ROW EXECUTE PROCEDURE
477 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
478 "name", "description");
480 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';
482 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
483 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
484 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
485 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
486 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
487 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
488 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
489 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
492 CREATE TABLE "membership" (
493 PRIMARY KEY ("area_id", "member_id"),
494 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
495 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
496 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
497 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
499 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
501 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';
504 CREATE TABLE "interest" (
505 PRIMARY KEY ("issue_id", "member_id"),
506 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
507 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
508 "autoreject" BOOLEAN NOT NULL,
509 "voting_requested" BOOLEAN );
510 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
512 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.';
514 COMMENT ON COLUMN "interest"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure';
515 COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply';
518 CREATE TABLE "initiator" (
519 PRIMARY KEY ("initiative_id", "member_id"),
520 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
521 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
522 "accepted" BOOLEAN );
523 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
525 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.';
527 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.';
530 CREATE TABLE "supporter" (
531 "issue_id" INT4 NOT NULL,
532 PRIMARY KEY ("initiative_id", "member_id"),
533 "initiative_id" INT4,
534 "member_id" INT4,
535 "draft_id" INT8 NOT NULL,
536 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
537 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
538 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
540 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.';
542 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
545 CREATE TABLE "opinion" (
546 "initiative_id" INT4 NOT NULL,
547 PRIMARY KEY ("suggestion_id", "member_id"),
548 "suggestion_id" INT8,
549 "member_id" INT4,
550 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
551 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
552 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE,
553 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
554 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
556 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.';
558 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
561 CREATE TYPE "delegation_scope" AS ENUM ('global', 'area', 'issue');
563 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''global'', ''area'', or ''issue'' (order is relevant)';
566 CREATE TABLE "delegation" (
567 "id" SERIAL8 PRIMARY KEY,
568 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
569 "trustee_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
570 "scope" "delegation_scope" NOT NULL,
571 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
572 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
573 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
574 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
575 ("scope" = 'global' AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
576 ("scope" = 'area' AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
577 ("scope" = 'issue' AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
578 UNIQUE ("area_id", "truster_id", "trustee_id"),
579 UNIQUE ("issue_id", "truster_id", "trustee_id") );
580 CREATE UNIQUE INDEX "delegation_global_truster_id_trustee_id_unique_idx"
581 ON "delegation" ("truster_id", "trustee_id") WHERE "scope" = 'global';
582 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
583 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
585 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
587 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
588 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
591 CREATE TABLE "direct_population_snapshot" (
592 PRIMARY KEY ("issue_id", "event", "member_id"),
593 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
594 "event" "snapshot_event",
595 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
596 "weight" INT4,
597 "interest_exists" BOOLEAN NOT NULL );
598 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
600 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
602 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
603 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
604 COMMENT ON COLUMN "direct_population_snapshot"."interest_exists" IS 'TRUE if entry is due to interest in issue, FALSE if entry is only due to membership in area';
607 CREATE TABLE "delegating_population_snapshot" (
608 PRIMARY KEY ("issue_id", "event", "member_id"),
609 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
610 "event" "snapshot_event",
611 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
612 "weight" INT4,
613 "scope" "delegation_scope" NOT NULL,
614 "delegate_member_ids" INT4[] NOT NULL );
615 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
617 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
619 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
620 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
621 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
622 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"';
625 CREATE TABLE "direct_interest_snapshot" (
626 PRIMARY KEY ("issue_id", "event", "member_id"),
627 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
628 "event" "snapshot_event",
629 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
630 "weight" INT4,
631 "voting_requested" BOOLEAN );
632 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
634 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
636 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
637 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
638 COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';
641 CREATE TABLE "delegating_interest_snapshot" (
642 PRIMARY KEY ("issue_id", "event", "member_id"),
643 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
644 "event" "snapshot_event",
645 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
646 "weight" INT4,
647 "scope" "delegation_scope" NOT NULL,
648 "delegate_member_ids" INT4[] NOT NULL );
649 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
651 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
653 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
654 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
655 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
656 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"';
659 CREATE TABLE "direct_supporter_snapshot" (
660 "issue_id" INT4 NOT NULL,
661 PRIMARY KEY ("initiative_id", "event", "member_id"),
662 "initiative_id" INT4,
663 "event" "snapshot_event",
664 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
665 "informed" BOOLEAN NOT NULL,
666 "satisfied" BOOLEAN NOT NULL,
667 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
668 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
669 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
671 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
673 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
674 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
675 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
678 CREATE TABLE "direct_voter" (
679 PRIMARY KEY ("issue_id", "member_id"),
680 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
681 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
682 "weight" INT4,
683 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
684 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
686 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.';
688 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
689 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
692 CREATE TABLE "delegating_voter" (
693 PRIMARY KEY ("issue_id", "member_id"),
694 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
695 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
696 "weight" INT4,
697 "scope" "delegation_scope" NOT NULL,
698 "delegate_member_ids" INT4[] NOT NULL );
699 CREATE INDEX "delegating_voter_member_id_idx" ON "direct_voter" ("member_id");
701 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
703 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
704 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
705 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"';
708 CREATE TABLE "vote" (
709 "issue_id" INT4 NOT NULL,
710 PRIMARY KEY ("initiative_id", "member_id"),
711 "initiative_id" INT4,
712 "member_id" INT4,
713 "grade" INT4,
714 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
715 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
716 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
718 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.';
720 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.';
723 CREATE TABLE "contingent" (
724 "time_frame" INTERVAL PRIMARY KEY,
725 "text_entry_limit" INT4,
726 "initiative_limit" INT4 );
728 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.';
730 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';
731 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
735 --------------------------------
736 -- Writing of history entries --
737 --------------------------------
739 CREATE FUNCTION "write_member_history_trigger"()
740 RETURNS TRIGGER
741 LANGUAGE 'plpgsql' VOLATILE AS $$
742 BEGIN
743 IF NEW."login" != OLD."login" OR NEW."name" != OLD."name" THEN
744 INSERT INTO "member_history" ("member_id", "login", "name")
745 VALUES (NEW."id", OLD."login", OLD."name");
746 END IF;
747 RETURN NULL;
748 END;
749 $$;
751 CREATE TRIGGER "write_member_history"
752 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
753 "write_member_history_trigger"();
755 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
756 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing name or login of a member, create a history entry in "member_history" table';
760 ----------------------------
761 -- Additional constraints --
762 ----------------------------
765 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
766 RETURNS TRIGGER
767 LANGUAGE 'plpgsql' VOLATILE AS $$
768 BEGIN
769 IF NOT EXISTS (
770 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
771 ) THEN
772 --RAISE 'Cannot create issue without an initial initiative.' USING
773 -- ERRCODE = 'integrity_constraint_violation',
774 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
775 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
776 END IF;
777 RETURN NULL;
778 END;
779 $$;
781 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
782 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
783 FOR EACH ROW EXECUTE PROCEDURE
784 "issue_requires_first_initiative_trigger"();
786 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
787 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
790 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
791 RETURNS TRIGGER
792 LANGUAGE 'plpgsql' VOLATILE AS $$
793 DECLARE
794 "reference_lost" BOOLEAN;
795 BEGIN
796 IF TG_OP = 'DELETE' THEN
797 "reference_lost" := TRUE;
798 ELSE
799 "reference_lost" := NEW."issue_id" != OLD."issue_id";
800 END IF;
801 IF
802 "reference_lost" AND NOT EXISTS (
803 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
804 )
805 THEN
806 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
807 END IF;
808 RETURN NULL;
809 END;
810 $$;
812 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
813 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
814 FOR EACH ROW EXECUTE PROCEDURE
815 "last_initiative_deletes_issue_trigger"();
817 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
818 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
821 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
822 RETURNS TRIGGER
823 LANGUAGE 'plpgsql' VOLATILE AS $$
824 BEGIN
825 IF NOT EXISTS (
826 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
827 ) THEN
828 --RAISE 'Cannot create initiative without an initial draft.' USING
829 -- ERRCODE = 'integrity_constraint_violation',
830 -- HINT = 'Create issue, initiative and draft within the same transaction.';
831 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
832 END IF;
833 RETURN NULL;
834 END;
835 $$;
837 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
838 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
839 FOR EACH ROW EXECUTE PROCEDURE
840 "initiative_requires_first_draft_trigger"();
842 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
843 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
846 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
847 RETURNS TRIGGER
848 LANGUAGE 'plpgsql' VOLATILE AS $$
849 DECLARE
850 "reference_lost" BOOLEAN;
851 BEGIN
852 IF TG_OP = 'DELETE' THEN
853 "reference_lost" := TRUE;
854 ELSE
855 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
856 END IF;
857 IF
858 "reference_lost" AND NOT EXISTS (
859 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
860 )
861 THEN
862 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
863 END IF;
864 RETURN NULL;
865 END;
866 $$;
868 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
869 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
870 FOR EACH ROW EXECUTE PROCEDURE
871 "last_draft_deletes_initiative_trigger"();
873 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
874 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
877 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
878 RETURNS TRIGGER
879 LANGUAGE 'plpgsql' VOLATILE AS $$
880 BEGIN
881 IF NOT EXISTS (
882 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
883 ) THEN
884 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
885 END IF;
886 RETURN NULL;
887 END;
888 $$;
890 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
891 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
892 FOR EACH ROW EXECUTE PROCEDURE
893 "suggestion_requires_first_opinion_trigger"();
895 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
896 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
899 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
900 RETURNS TRIGGER
901 LANGUAGE 'plpgsql' VOLATILE AS $$
902 DECLARE
903 "reference_lost" BOOLEAN;
904 BEGIN
905 IF TG_OP = 'DELETE' THEN
906 "reference_lost" := TRUE;
907 ELSE
908 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
909 END IF;
910 IF
911 "reference_lost" AND NOT EXISTS (
912 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
913 )
914 THEN
915 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
916 END IF;
917 RETURN NULL;
918 END;
919 $$;
921 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
922 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
923 FOR EACH ROW EXECUTE PROCEDURE
924 "last_opinion_deletes_suggestion_trigger"();
926 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
927 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
931 ---------------------------------------------------------------
932 -- Ensure that votes are not modified when issues are frozen --
933 ---------------------------------------------------------------
935 -- NOTE: Frontends should ensure this anyway, but in case of programming
936 -- errors the following triggers ensure data integrity.
939 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
940 RETURNS TRIGGER
941 LANGUAGE 'plpgsql' VOLATILE AS $$
942 DECLARE
943 "issue_row" "issue"%ROWTYPE;
944 BEGIN
945 SELECT INTO "issue_row" * FROM "issue"
946 WHERE "id" = NEW."issue_id" FOR SHARE;
947 IF "issue_row"."closed" NOTNULL THEN
948 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
949 END IF;
950 RETURN NULL;
951 END;
952 $$;
954 CREATE TRIGGER "forbid_changes_on_closed_issue"
955 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
956 FOR EACH ROW EXECUTE PROCEDURE
957 "forbid_changes_on_closed_issue_trigger"();
959 CREATE TRIGGER "forbid_changes_on_closed_issue"
960 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
961 FOR EACH ROW EXECUTE PROCEDURE
962 "forbid_changes_on_closed_issue_trigger"();
964 CREATE TRIGGER "forbid_changes_on_closed_issue"
965 AFTER INSERT OR UPDATE OR DELETE ON "vote"
966 FOR EACH ROW EXECUTE PROCEDURE
967 "forbid_changes_on_closed_issue_trigger"();
969 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"';
970 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';
971 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';
972 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';
976 --------------------------------------------------------------------
977 -- Auto-retrieval of fields only needed for referential integrity --
978 --------------------------------------------------------------------
981 CREATE FUNCTION "autofill_issue_id_trigger"()
982 RETURNS TRIGGER
983 LANGUAGE 'plpgsql' VOLATILE AS $$
984 BEGIN
985 IF NEW."issue_id" ISNULL THEN
986 SELECT "issue_id" INTO NEW."issue_id"
987 FROM "initiative" WHERE "id" = NEW."initiative_id";
988 END IF;
989 RETURN NEW;
990 END;
991 $$;
993 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
994 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
996 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
997 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
999 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1000 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1001 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1004 CREATE FUNCTION "autofill_initiative_id_trigger"()
1005 RETURNS TRIGGER
1006 LANGUAGE 'plpgsql' VOLATILE AS $$
1007 BEGIN
1008 IF NEW."initiative_id" ISNULL THEN
1009 SELECT "initiative_id" INTO NEW."initiative_id"
1010 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1011 END IF;
1012 RETURN NEW;
1013 END;
1014 $$;
1016 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1017 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1019 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1020 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1024 -----------------------------------------------------
1025 -- Automatic calculation of certain default values --
1026 -----------------------------------------------------
1029 CREATE FUNCTION "copy_timings_trigger"()
1030 RETURNS TRIGGER
1031 LANGUAGE 'plpgsql' VOLATILE AS $$
1032 DECLARE
1033 "policy_row" "policy"%ROWTYPE;
1034 BEGIN
1035 SELECT * INTO "policy_row" FROM "policy"
1036 WHERE "id" = NEW."policy_id";
1037 IF NEW."admission_time" ISNULL THEN
1038 NEW."admission_time" := "policy_row"."admission_time";
1039 END IF;
1040 IF NEW."discussion_time" ISNULL THEN
1041 NEW."discussion_time" := "policy_row"."discussion_time";
1042 END IF;
1043 IF NEW."verification_time" ISNULL THEN
1044 NEW."verification_time" := "policy_row"."verification_time";
1045 END IF;
1046 IF NEW."voting_time" ISNULL THEN
1047 NEW."voting_time" := "policy_row"."voting_time";
1048 END IF;
1049 RETURN NEW;
1050 END;
1051 $$;
1053 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1054 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1056 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1057 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1060 CREATE FUNCTION "copy_autoreject_trigger"()
1061 RETURNS TRIGGER
1062 LANGUAGE 'plpgsql' VOLATILE AS $$
1063 BEGIN
1064 IF NEW."autoreject" ISNULL THEN
1065 SELECT "membership"."autoreject" INTO NEW."autoreject"
1066 FROM "issue" JOIN "membership"
1067 ON "issue"."area_id" = "membership"."area_id"
1068 WHERE "issue"."id" = NEW."issue_id"
1069 AND "membership"."member_id" = NEW."member_id";
1070 END IF;
1071 IF NEW."autoreject" ISNULL THEN
1072 NEW."autoreject" := FALSE;
1073 END IF;
1074 RETURN NEW;
1075 END;
1076 $$;
1078 CREATE TRIGGER "copy_autoreject" BEFORE INSERT OR UPDATE ON "interest"
1079 FOR EACH ROW EXECUTE PROCEDURE "copy_autoreject_trigger"();
1081 COMMENT ON FUNCTION "copy_autoreject_trigger"() IS 'Implementation of trigger "copy_autoreject" on table "interest"';
1082 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';
1085 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
1086 RETURNS TRIGGER
1087 LANGUAGE 'plpgsql' VOLATILE AS $$
1088 BEGIN
1089 IF NEW."draft_id" ISNULL THEN
1090 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1091 WHERE "initiative_id" = NEW."initiative_id";
1092 END IF;
1093 RETURN NEW;
1094 END;
1095 $$;
1097 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1098 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
1100 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
1101 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';
1105 ----------------------------------------
1106 -- Automatic creation of dependencies --
1107 ----------------------------------------
1110 CREATE FUNCTION "autocreate_interest_trigger"()
1111 RETURNS TRIGGER
1112 LANGUAGE 'plpgsql' VOLATILE AS $$
1113 BEGIN
1114 IF NOT EXISTS (
1115 SELECT NULL FROM "initiative" JOIN "interest"
1116 ON "initiative"."issue_id" = "interest"."issue_id"
1117 WHERE "initiative"."id" = NEW."initiative_id"
1118 AND "interest"."member_id" = NEW."member_id"
1119 ) THEN
1120 BEGIN
1121 INSERT INTO "interest" ("issue_id", "member_id")
1122 SELECT "issue_id", NEW."member_id"
1123 FROM "initiative" WHERE "id" = NEW."initiative_id";
1124 EXCEPTION WHEN unique_violation THEN END;
1125 END IF;
1126 RETURN NEW;
1127 END;
1128 $$;
1130 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1131 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1133 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1134 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';
1137 CREATE FUNCTION "autocreate_supporter_trigger"()
1138 RETURNS TRIGGER
1139 LANGUAGE 'plpgsql' VOLATILE AS $$
1140 BEGIN
1141 IF NOT EXISTS (
1142 SELECT NULL FROM "suggestion" JOIN "supporter"
1143 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1144 WHERE "suggestion"."id" = NEW."suggestion_id"
1145 AND "supporter"."member_id" = NEW."member_id"
1146 ) THEN
1147 BEGIN
1148 INSERT INTO "supporter" ("initiative_id", "member_id")
1149 SELECT "initiative_id", NEW."member_id"
1150 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1151 EXCEPTION WHEN unique_violation THEN END;
1152 END IF;
1153 RETURN NEW;
1154 END;
1155 $$;
1157 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1158 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1160 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1161 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.';
1165 ------------------------------------------
1166 -- Views and helper functions for views --
1167 ------------------------------------------
1170 CREATE VIEW "global_delegation" AS
1171 SELECT
1172 "delegation"."id",
1173 "delegation"."truster_id",
1174 "delegation"."trustee_id"
1175 FROM "delegation" JOIN "member"
1176 ON "delegation"."trustee_id" = "member"."id"
1177 WHERE "delegation"."scope" = 'global' AND "member"."active";
1179 COMMENT ON VIEW "global_delegation" IS 'Global delegations to active members';
1182 CREATE VIEW "area_delegation" AS
1183 SELECT "subquery".* FROM (
1184 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1185 "area"."id" AS "area_id",
1186 "delegation"."id",
1187 "delegation"."truster_id",
1188 "delegation"."trustee_id",
1189 "delegation"."scope"
1190 FROM "area" JOIN "delegation"
1191 ON "delegation"."scope" = 'global'
1192 OR "delegation"."area_id" = "area"."id"
1193 ORDER BY
1194 "area"."id",
1195 "delegation"."truster_id",
1196 "delegation"."scope" DESC
1197 ) AS "subquery"
1198 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
1199 WHERE "member"."active";
1201 COMMENT ON VIEW "area_delegation" IS 'Active delegations for areas';
1204 CREATE VIEW "issue_delegation" AS
1205 SELECT "subquery".* FROM (
1206 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1207 "issue"."id" AS "issue_id",
1208 "delegation"."id",
1209 "delegation"."truster_id",
1210 "delegation"."trustee_id",
1211 "delegation"."scope"
1212 FROM "issue" JOIN "delegation"
1213 ON "delegation"."scope" = 'global'
1214 OR "delegation"."area_id" = "issue"."area_id"
1215 OR "delegation"."issue_id" = "issue"."id"
1216 ORDER BY
1217 "issue"."id",
1218 "delegation"."truster_id",
1219 "delegation"."scope" DESC
1220 ) AS "subquery"
1221 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
1222 WHERE "member"."active";
1224 COMMENT ON VIEW "issue_delegation" IS 'Active delegations for issues';
1227 CREATE FUNCTION "membership_weight_with_skipping"
1228 ( "area_id_p" "area"."id"%TYPE,
1229 "member_id_p" "member"."id"%TYPE,
1230 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1231 RETURNS INT4
1232 LANGUAGE 'plpgsql' STABLE AS $$
1233 DECLARE
1234 "sum_v" INT4;
1235 "delegation_row" "area_delegation"%ROWTYPE;
1236 BEGIN
1237 "sum_v" := 1;
1238 FOR "delegation_row" IN
1239 SELECT "area_delegation".*
1240 FROM "area_delegation" LEFT JOIN "membership"
1241 ON "membership"."area_id" = "area_id_p"
1242 AND "membership"."member_id" = "area_delegation"."truster_id"
1243 WHERE "area_delegation"."area_id" = "area_id_p"
1244 AND "area_delegation"."trustee_id" = "member_id_p"
1245 AND "membership"."member_id" ISNULL
1246 LOOP
1247 IF NOT
1248 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1249 THEN
1250 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1251 "area_id_p",
1252 "delegation_row"."truster_id",
1253 "skip_member_ids_p" || "delegation_row"."truster_id"
1254 );
1255 END IF;
1256 END LOOP;
1257 RETURN "sum_v";
1258 END;
1259 $$;
1261 COMMENT ON FUNCTION "membership_weight_with_skipping"
1262 ( "area"."id"%TYPE,
1263 "member"."id"%TYPE,
1264 INT4[] )
1265 IS 'Helper function for "membership_weight" function';
1268 CREATE FUNCTION "membership_weight"
1269 ( "area_id_p" "area"."id"%TYPE,
1270 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1271 RETURNS INT4
1272 LANGUAGE 'plpgsql' STABLE AS $$
1273 BEGIN
1274 RETURN "membership_weight_with_skipping"(
1275 "area_id_p",
1276 "member_id_p",
1277 ARRAY["member_id_p"]
1278 );
1279 END;
1280 $$;
1282 COMMENT ON FUNCTION "membership_weight"
1283 ( "area"."id"%TYPE,
1284 "member"."id"%TYPE )
1285 IS 'Calculates the potential voting weight of a member in a given area';
1288 CREATE VIEW "member_count_view" AS
1289 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1291 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1294 CREATE VIEW "area_member_count" AS
1295 SELECT
1296 "area"."id" AS "area_id",
1297 count("member"."id") AS "direct_member_count",
1298 coalesce(
1299 sum(
1300 CASE WHEN "member"."id" NOTNULL THEN
1301 "membership_weight"("area"."id", "member"."id")
1302 ELSE 0 END
1303 )
1304 ) AS "member_weight",
1305 coalesce(
1306 sum(
1307 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
1308 "membership_weight"("area"."id", "member"."id")
1309 ELSE 0 END
1310 )
1311 ) AS "autoreject_weight"
1312 FROM "area"
1313 LEFT JOIN "membership"
1314 ON "area"."id" = "membership"."area_id"
1315 LEFT JOIN "member"
1316 ON "membership"."member_id" = "member"."id"
1317 AND "member"."active"
1318 GROUP BY "area"."id";
1320 COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"';
1323 CREATE VIEW "opening_draft" AS
1324 SELECT "draft".* FROM (
1325 SELECT
1326 "initiative"."id" AS "initiative_id",
1327 min("draft"."id") AS "draft_id"
1328 FROM "initiative" JOIN "draft"
1329 ON "initiative"."id" = "draft"."initiative_id"
1330 GROUP BY "initiative"."id"
1331 ) AS "subquery"
1332 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1334 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1337 CREATE VIEW "current_draft" AS
1338 SELECT "draft".* FROM (
1339 SELECT
1340 "initiative"."id" AS "initiative_id",
1341 max("draft"."id") AS "draft_id"
1342 FROM "initiative" JOIN "draft"
1343 ON "initiative"."id" = "draft"."initiative_id"
1344 GROUP BY "initiative"."id"
1345 ) AS "subquery"
1346 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1348 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1351 CREATE VIEW "critical_opinion" AS
1352 SELECT * FROM "opinion"
1353 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
1354 OR ("degree" = -2 AND "fulfilled" = TRUE);
1356 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
1359 CREATE VIEW "battle" AS
1360 SELECT
1361 "issue"."id" AS "issue_id",
1362 "winning_initiative"."id" AS "winning_initiative_id",
1363 "losing_initiative"."id" AS "losing_initiative_id",
1364 sum(
1365 CASE WHEN
1366 coalesce("better_vote"."grade", 0) >
1367 coalesce("worse_vote"."grade", 0)
1368 THEN "direct_voter"."weight" ELSE 0 END
1369 ) AS "count"
1370 FROM "issue"
1371 LEFT JOIN "direct_voter"
1372 ON "issue"."id" = "direct_voter"."issue_id"
1373 JOIN "initiative" AS "winning_initiative"
1374 ON "issue"."id" = "winning_initiative"."issue_id"
1375 AND "winning_initiative"."agreed"
1376 JOIN "initiative" AS "losing_initiative"
1377 ON "issue"."id" = "losing_initiative"."issue_id"
1378 AND "losing_initiative"."agreed"
1379 LEFT JOIN "vote" AS "better_vote"
1380 ON "direct_voter"."member_id" = "better_vote"."member_id"
1381 AND "winning_initiative"."id" = "better_vote"."initiative_id"
1382 LEFT JOIN "vote" AS "worse_vote"
1383 ON "direct_voter"."member_id" = "worse_vote"."member_id"
1384 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
1385 WHERE
1386 "winning_initiative"."id" != "losing_initiative"."id"
1387 GROUP BY
1388 "issue"."id",
1389 "winning_initiative"."id",
1390 "losing_initiative"."id";
1392 COMMENT ON VIEW "battle" IS 'Number of members preferring one initiative over another';
1395 CREATE VIEW "expired_session" AS
1396 SELECT * FROM "session" WHERE now() > "expiry";
1398 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
1399 DELETE FROM "session" WHERE "ident" = OLD."ident";
1401 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
1402 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
1405 CREATE VIEW "open_issue" AS
1406 SELECT * FROM "issue" WHERE "closed" ISNULL;
1408 COMMENT ON VIEW "open_issue" IS 'All open issues';
1411 CREATE VIEW "issue_with_ranks_missing" AS
1412 SELECT * FROM "issue"
1413 WHERE "fully_frozen" NOTNULL
1414 AND "closed" NOTNULL
1415 AND "ranks_available" = FALSE;
1417 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
1420 CREATE VIEW "member_contingent" AS
1421 SELECT
1422 "member"."id" AS "member_id",
1423 "contingent"."time_frame",
1424 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
1425 (
1426 SELECT count(1) FROM "draft"
1427 WHERE "draft"."author_id" = "member"."id"
1428 AND "draft"."created" > now() - "contingent"."time_frame"
1429 ) + (
1430 SELECT count(1) FROM "suggestion"
1431 WHERE "suggestion"."author_id" = "member"."id"
1432 AND "suggestion"."created" > now() - "contingent"."time_frame"
1433 )
1434 ELSE NULL END AS "text_entry_count",
1435 "contingent"."text_entry_limit",
1436 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
1437 SELECT count(1) FROM "opening_draft"
1438 WHERE "opening_draft"."author_id" = "member"."id"
1439 AND "opening_draft"."created" > now() - "contingent"."time_frame"
1440 ) ELSE NULL END AS "initiative_count",
1441 "contingent"."initiative_limit"
1442 FROM "member" CROSS JOIN "contingent";
1444 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
1446 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
1447 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
1450 CREATE VIEW "member_contingent_left" AS
1451 SELECT
1452 "member_id",
1453 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
1454 max("initiative_limit" - "initiative_count") AS "initiatives_left"
1455 FROM "member_contingent" GROUP BY "member_id";
1457 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.';
1460 CREATE TYPE "timeline_event" AS ENUM (
1461 'issue_created',
1462 'issue_canceled',
1463 'issue_accepted',
1464 'issue_half_frozen',
1465 'issue_finished_without_voting',
1466 'issue_voting_started',
1467 'issue_finished_after_voting',
1468 'initiative_created',
1469 'initiative_revoked',
1470 'draft_created',
1471 'suggestion_created');
1473 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables';
1476 CREATE VIEW "timeline_issue" AS
1477 SELECT
1478 "created" AS "occurrence",
1479 'issue_created'::"timeline_event" AS "event",
1480 "id" AS "issue_id"
1481 FROM "issue"
1482 UNION ALL
1483 SELECT
1484 "closed" AS "occurrence",
1485 'issue_canceled'::"timeline_event" AS "event",
1486 "id" AS "issue_id"
1487 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
1488 UNION ALL
1489 SELECT
1490 "accepted" AS "occurrence",
1491 'issue_accepted'::"timeline_event" AS "event",
1492 "id" AS "issue_id"
1493 FROM "issue" WHERE "accepted" NOTNULL
1494 UNION ALL
1495 SELECT
1496 "half_frozen" AS "occurrence",
1497 'issue_half_frozen'::"timeline_event" AS "event",
1498 "id" AS "issue_id"
1499 FROM "issue" WHERE "half_frozen" NOTNULL
1500 UNION ALL
1501 SELECT
1502 "fully_frozen" AS "occurrence",
1503 'issue_voting_started'::"timeline_event" AS "event",
1504 "id" AS "issue_id"
1505 FROM "issue"
1506 WHERE "fully_frozen" NOTNULL
1507 AND ("closed" ISNULL OR "closed" != "fully_frozen")
1508 UNION ALL
1509 SELECT
1510 "closed" AS "occurrence",
1511 CASE WHEN "fully_frozen" = "closed" THEN
1512 'issue_finished_without_voting'::"timeline_event"
1513 ELSE
1514 'issue_finished_after_voting'::"timeline_event"
1515 END AS "event",
1516 "id" AS "issue_id"
1517 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
1519 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view';
1522 CREATE VIEW "timeline_initiative" AS
1523 SELECT
1524 "created" AS "occurrence",
1525 'initiative_created'::"timeline_event" AS "event",
1526 "id" AS "initiative_id"
1527 FROM "initiative"
1528 UNION ALL
1529 SELECT
1530 "revoked" AS "occurrence",
1531 'initiative_revoked'::"timeline_event" AS "event",
1532 "id" AS "initiative_id"
1533 FROM "initiative" WHERE "revoked" NOTNULL;
1535 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view';
1538 CREATE VIEW "timeline_draft" AS
1539 SELECT
1540 "created" AS "occurrence",
1541 'draft_created'::"timeline_event" AS "event",
1542 "id" AS "draft_id"
1543 FROM "draft";
1545 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view';
1548 CREATE VIEW "timeline_suggestion" AS
1549 SELECT
1550 "created" AS "occurrence",
1551 'suggestion_created'::"timeline_event" AS "event",
1552 "id" AS "suggestion_id"
1553 FROM "suggestion";
1555 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view';
1558 CREATE VIEW "timeline" AS
1559 SELECT
1560 "occurrence",
1561 "event",
1562 "issue_id",
1563 NULL AS "initiative_id",
1564 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
1565 NULL::INT8 AS "suggestion_id"
1566 FROM "timeline_issue"
1567 UNION ALL
1568 SELECT
1569 "occurrence",
1570 "event",
1571 NULL AS "issue_id",
1572 "initiative_id",
1573 NULL AS "draft_id",
1574 NULL AS "suggestion_id"
1575 FROM "timeline_initiative"
1576 UNION ALL
1577 SELECT
1578 "occurrence",
1579 "event",
1580 NULL AS "issue_id",
1581 NULL AS "initiative_id",
1582 "draft_id",
1583 NULL AS "suggestion_id"
1584 FROM "timeline_draft"
1585 UNION ALL
1586 SELECT
1587 "occurrence",
1588 "event",
1589 NULL AS "issue_id",
1590 NULL AS "initiative_id",
1591 NULL AS "draft_id",
1592 "suggestion_id"
1593 FROM "timeline_suggestion";
1595 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system';
1599 --------------------------------------------------
1600 -- Set returning function for delegation chains --
1601 --------------------------------------------------
1604 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
1605 ('first', 'intermediate', 'last', 'repetition');
1607 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
1610 CREATE TYPE "delegation_chain_row" AS (
1611 "index" INT4,
1612 "member_id" INT4,
1613 "member_active" BOOLEAN,
1614 "participation" BOOLEAN,
1615 "overridden" BOOLEAN,
1616 "scope_in" "delegation_scope",
1617 "scope_out" "delegation_scope",
1618 "loop" "delegation_chain_loop_tag" );
1620 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
1622 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
1623 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';
1624 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
1625 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
1626 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
1627 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
1630 CREATE FUNCTION "delegation_chain"
1631 ( "member_id_p" "member"."id"%TYPE,
1632 "area_id_p" "area"."id"%TYPE,
1633 "issue_id_p" "issue"."id"%TYPE,
1634 "simulate_trustee_id_p" "member"."id"%TYPE )
1635 RETURNS SETOF "delegation_chain_row"
1636 LANGUAGE 'plpgsql' STABLE AS $$
1637 DECLARE
1638 "issue_row" "issue"%ROWTYPE;
1639 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
1640 "loop_member_id_v" "member"."id"%TYPE;
1641 "output_row" "delegation_chain_row";
1642 "output_rows" "delegation_chain_row"[];
1643 "delegation_row" "delegation"%ROWTYPE;
1644 "row_count" INT4;
1645 "i" INT4;
1646 "loop_v" BOOLEAN;
1647 BEGIN
1648 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1649 "visited_member_ids" := '{}';
1650 "loop_member_id_v" := NULL;
1651 "output_rows" := '{}';
1652 "output_row"."index" := 0;
1653 "output_row"."member_id" := "member_id_p";
1654 "output_row"."member_active" := TRUE;
1655 "output_row"."participation" := FALSE;
1656 "output_row"."overridden" := FALSE;
1657 "output_row"."scope_out" := NULL;
1658 LOOP
1659 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
1660 "loop_member_id_v" := "output_row"."member_id";
1661 ELSE
1662 "visited_member_ids" :=
1663 "visited_member_ids" || "output_row"."member_id";
1664 END IF;
1665 IF "output_row"."participation" THEN
1666 "output_row"."overridden" := TRUE;
1667 END IF;
1668 "output_row"."scope_in" := "output_row"."scope_out";
1669 IF EXISTS (
1670 SELECT NULL FROM "member"
1671 WHERE "id" = "output_row"."member_id" AND "active"
1672 ) THEN
1673 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
1674 SELECT * INTO "delegation_row" FROM "delegation"
1675 WHERE "truster_id" = "output_row"."member_id"
1676 AND "scope" = 'global';
1677 ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN
1678 "output_row"."participation" := EXISTS (
1679 SELECT NULL FROM "membership"
1680 WHERE "area_id" = "area_id_p"
1681 AND "member_id" = "output_row"."member_id"
1682 );
1683 SELECT * INTO "delegation_row" FROM "delegation"
1684 WHERE "truster_id" = "output_row"."member_id"
1685 AND ("scope" = 'global' OR "area_id" = "area_id_p")
1686 ORDER BY "scope" DESC;
1687 ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN
1688 "output_row"."participation" := EXISTS (
1689 SELECT NULL FROM "interest"
1690 WHERE "issue_id" = "issue_id_p"
1691 AND "member_id" = "output_row"."member_id"
1692 );
1693 SELECT * INTO "delegation_row" FROM "delegation"
1694 WHERE "truster_id" = "output_row"."member_id"
1695 AND ("scope" = 'global' OR
1696 "area_id" = "issue_row"."area_id" OR
1697 "issue_id" = "issue_id_p"
1698 )
1699 ORDER BY "scope" DESC;
1700 ELSE
1701 RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.';
1702 END IF;
1703 ELSE
1704 "output_row"."member_active" := FALSE;
1705 "output_row"."participation" := FALSE;
1706 "output_row"."scope_out" := NULL;
1707 "delegation_row" := ROW(NULL);
1708 END IF;
1709 IF
1710 "output_row"."member_id" = "member_id_p" AND
1711 "simulate_trustee_id_p" NOTNULL
1712 THEN
1713 "output_row"."scope_out" := CASE
1714 WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global'
1715 WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area'
1716 WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue'
1717 END;
1718 "output_rows" := "output_rows" || "output_row";
1719 "output_row"."member_id" := "simulate_trustee_id_p";
1720 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
1721 "output_row"."scope_out" := "delegation_row"."scope";
1722 "output_rows" := "output_rows" || "output_row";
1723 "output_row"."member_id" := "delegation_row"."trustee_id";
1724 ELSE
1725 "output_row"."scope_out" := NULL;
1726 "output_rows" := "output_rows" || "output_row";
1727 EXIT;
1728 END IF;
1729 EXIT WHEN "loop_member_id_v" NOTNULL;
1730 "output_row"."index" := "output_row"."index" + 1;
1731 END LOOP;
1732 "row_count" := array_upper("output_rows", 1);
1733 "i" := 1;
1734 "loop_v" := FALSE;
1735 LOOP
1736 "output_row" := "output_rows"["i"];
1737 EXIT WHEN "output_row"."member_id" ISNULL;
1738 IF "loop_v" THEN
1739 IF "i" + 1 = "row_count" THEN
1740 "output_row"."loop" := 'last';
1741 ELSIF "i" = "row_count" THEN
1742 "output_row"."loop" := 'repetition';
1743 ELSE
1744 "output_row"."loop" := 'intermediate';
1745 END IF;
1746 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
1747 "output_row"."loop" := 'first';
1748 "loop_v" := TRUE;
1749 END IF;
1750 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
1751 "output_row"."participation" := NULL;
1752 END IF;
1753 RETURN NEXT "output_row";
1754 "i" := "i" + 1;
1755 END LOOP;
1756 RETURN;
1757 END;
1758 $$;
1760 COMMENT ON FUNCTION "delegation_chain"
1761 ( "member"."id"%TYPE,
1762 "area"."id"%TYPE,
1763 "issue"."id"%TYPE,
1764 "member"."id"%TYPE )
1765 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
1767 CREATE FUNCTION "delegation_chain"
1768 ( "member_id_p" "member"."id"%TYPE,
1769 "area_id_p" "area"."id"%TYPE,
1770 "issue_id_p" "issue"."id"%TYPE )
1771 RETURNS SETOF "delegation_chain_row"
1772 LANGUAGE 'plpgsql' STABLE AS $$
1773 DECLARE
1774 "result_row" "delegation_chain_row";
1775 BEGIN
1776 FOR "result_row" IN
1777 SELECT * FROM "delegation_chain"(
1778 "member_id_p", "area_id_p", "issue_id_p", NULL
1779 )
1780 LOOP
1781 RETURN NEXT "result_row";
1782 END LOOP;
1783 RETURN;
1784 END;
1785 $$;
1787 COMMENT ON FUNCTION "delegation_chain"
1788 ( "member"."id"%TYPE,
1789 "area"."id"%TYPE,
1790 "issue"."id"%TYPE )
1791 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
1795 ------------------------------
1796 -- Comparison by vote count --
1797 ------------------------------
1799 CREATE FUNCTION "vote_ratio"
1800 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
1801 "negative_votes_p" "initiative"."negative_votes"%TYPE )
1802 RETURNS FLOAT8
1803 LANGUAGE 'plpgsql' STABLE AS $$
1804 DECLARE
1805 "total_v" INT4;
1806 BEGIN
1807 "total_v" := "positive_votes_p" + "negative_votes_p";
1808 IF "total_v" > 0 THEN
1809 RETURN "positive_votes_p"::FLOAT8 / "total_v"::FLOAT8;
1810 ELSE
1811 RETURN 0.5;
1812 END IF;
1813 END;
1814 $$;
1816 COMMENT ON FUNCTION "vote_ratio"
1817 ( "initiative"."positive_votes"%TYPE,
1818 "initiative"."negative_votes"%TYPE )
1819 IS 'Ratio of positive votes to sum of positive and negative votes; 0.5, if there are neither positive nor negative votes';
1823 ------------------------------------------------
1824 -- Locking for snapshots and voting procedure --
1825 ------------------------------------------------
1827 CREATE FUNCTION "global_lock"() RETURNS VOID
1828 LANGUAGE 'plpgsql' VOLATILE AS $$
1829 BEGIN
1830 -- NOTE: PostgreSQL allows reading, while tables are locked in
1831 -- exclusive move. Transactions should be kept short anyway!
1832 LOCK TABLE "member" IN EXCLUSIVE MODE;
1833 LOCK TABLE "area" IN EXCLUSIVE MODE;
1834 LOCK TABLE "membership" IN EXCLUSIVE MODE;
1835 -- NOTE: "member", "area" and "membership" are locked first to
1836 -- prevent deadlocks in combination with "calculate_member_counts"()
1837 LOCK TABLE "policy" IN EXCLUSIVE MODE;
1838 LOCK TABLE "issue" IN EXCLUSIVE MODE;
1839 LOCK TABLE "initiative" IN EXCLUSIVE MODE;
1840 LOCK TABLE "draft" IN EXCLUSIVE MODE;
1841 LOCK TABLE "suggestion" IN EXCLUSIVE MODE;
1842 LOCK TABLE "interest" IN EXCLUSIVE MODE;
1843 LOCK TABLE "initiator" IN EXCLUSIVE MODE;
1844 LOCK TABLE "supporter" IN EXCLUSIVE MODE;
1845 LOCK TABLE "opinion" IN EXCLUSIVE MODE;
1846 LOCK TABLE "delegation" IN EXCLUSIVE MODE;
1847 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
1848 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
1849 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
1850 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
1851 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
1852 LOCK TABLE "direct_voter" IN EXCLUSIVE MODE;
1853 LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;
1854 LOCK TABLE "vote" IN EXCLUSIVE MODE;
1855 RETURN;
1856 END;
1857 $$;
1859 COMMENT ON FUNCTION "global_lock"() IS 'Locks all tables related to support/voting until end of transaction; read access is still possible though';
1863 -------------------------------
1864 -- Materialize member counts --
1865 -------------------------------
1867 CREATE FUNCTION "calculate_member_counts"()
1868 RETURNS VOID
1869 LANGUAGE 'plpgsql' VOLATILE AS $$
1870 BEGIN
1871 LOCK TABLE "member" IN EXCLUSIVE MODE;
1872 LOCK TABLE "area" IN EXCLUSIVE MODE;
1873 LOCK TABLE "membership" IN EXCLUSIVE MODE;
1874 DELETE FROM "member_count";
1875 INSERT INTO "member_count" ("total_count")
1876 SELECT "total_count" FROM "member_count_view";
1877 UPDATE "area" SET
1878 "direct_member_count" = "view"."direct_member_count",
1879 "member_weight" = "view"."member_weight",
1880 "autoreject_weight" = "view"."autoreject_weight"
1881 FROM "area_member_count" AS "view"
1882 WHERE "view"."area_id" = "area"."id";
1883 RETURN;
1884 END;
1885 $$;
1887 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"';
1891 ------------------------------
1892 -- Calculation of snapshots --
1893 ------------------------------
1895 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
1896 ( "issue_id_p" "issue"."id"%TYPE,
1897 "member_id_p" "member"."id"%TYPE,
1898 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
1899 RETURNS "direct_population_snapshot"."weight"%TYPE
1900 LANGUAGE 'plpgsql' VOLATILE AS $$
1901 DECLARE
1902 "issue_delegation_row" "issue_delegation"%ROWTYPE;
1903 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
1904 "weight_v" INT4;
1905 "sub_weight_v" INT4;
1906 BEGIN
1907 "weight_v" := 0;
1908 FOR "issue_delegation_row" IN
1909 SELECT * FROM "issue_delegation"
1910 WHERE "trustee_id" = "member_id_p"
1911 AND "issue_id" = "issue_id_p"
1912 LOOP
1913 IF NOT EXISTS (
1914 SELECT NULL FROM "direct_population_snapshot"
1915 WHERE "issue_id" = "issue_id_p"
1916 AND "event" = 'periodic'
1917 AND "member_id" = "issue_delegation_row"."truster_id"
1918 ) AND NOT EXISTS (
1919 SELECT NULL FROM "delegating_population_snapshot"
1920 WHERE "issue_id" = "issue_id_p"
1921 AND "event" = 'periodic'
1922 AND "member_id" = "issue_delegation_row"."truster_id"
1923 ) THEN
1924 "delegate_member_ids_v" :=
1925 "member_id_p" || "delegate_member_ids_p";
1926 INSERT INTO "delegating_population_snapshot" (
1927 "issue_id",
1928 "event",
1929 "member_id",
1930 "scope",
1931 "delegate_member_ids"
1932 ) VALUES (
1933 "issue_id_p",
1934 'periodic',
1935 "issue_delegation_row"."truster_id",
1936 "issue_delegation_row"."scope",
1937 "delegate_member_ids_v"
1938 );
1939 "sub_weight_v" := 1 +
1940 "weight_of_added_delegations_for_population_snapshot"(
1941 "issue_id_p",
1942 "issue_delegation_row"."truster_id",
1943 "delegate_member_ids_v"
1944 );
1945 UPDATE "delegating_population_snapshot"
1946 SET "weight" = "sub_weight_v"
1947 WHERE "issue_id" = "issue_id_p"
1948 AND "event" = 'periodic'
1949 AND "member_id" = "issue_delegation_row"."truster_id";
1950 "weight_v" := "weight_v" + "sub_weight_v";
1951 END IF;
1952 END LOOP;
1953 RETURN "weight_v";
1954 END;
1955 $$;
1957 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
1958 ( "issue"."id"%TYPE,
1959 "member"."id"%TYPE,
1960 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
1961 IS 'Helper function for "create_population_snapshot" function';
1964 CREATE FUNCTION "create_population_snapshot"
1965 ( "issue_id_p" "issue"."id"%TYPE )
1966 RETURNS VOID
1967 LANGUAGE 'plpgsql' VOLATILE AS $$
1968 DECLARE
1969 "member_id_v" "member"."id"%TYPE;
1970 BEGIN
1971 DELETE FROM "direct_population_snapshot"
1972 WHERE "issue_id" = "issue_id_p"
1973 AND "event" = 'periodic';
1974 DELETE FROM "delegating_population_snapshot"
1975 WHERE "issue_id" = "issue_id_p"
1976 AND "event" = 'periodic';
1977 INSERT INTO "direct_population_snapshot"
1978 ("issue_id", "event", "member_id", "interest_exists")
1979 SELECT DISTINCT ON ("issue_id", "member_id")
1980 "issue_id_p" AS "issue_id",
1981 'periodic' AS "event",
1982 "subquery"."member_id",
1983 "subquery"."interest_exists"
1984 FROM (
1985 SELECT
1986 "member"."id" AS "member_id",
1987 FALSE AS "interest_exists"
1988 FROM "issue"
1989 JOIN "area" ON "issue"."area_id" = "area"."id"
1990 JOIN "membership" ON "area"."id" = "membership"."area_id"
1991 JOIN "member" ON "membership"."member_id" = "member"."id"
1992 WHERE "issue"."id" = "issue_id_p"
1993 AND "member"."active"
1994 UNION
1995 SELECT
1996 "member"."id" AS "member_id",
1997 TRUE AS "interest_exists"
1998 FROM "interest" JOIN "member"
1999 ON "interest"."member_id" = "member"."id"
2000 WHERE "interest"."issue_id" = "issue_id_p"
2001 AND "member"."active"
2002 ) AS "subquery"
2003 ORDER BY
2004 "issue_id_p",
2005 "subquery"."member_id",
2006 "subquery"."interest_exists" DESC;
2007 FOR "member_id_v" IN
2008 SELECT "member_id" FROM "direct_population_snapshot"
2009 WHERE "issue_id" = "issue_id_p"
2010 AND "event" = 'periodic'
2011 LOOP
2012 UPDATE "direct_population_snapshot" SET
2013 "weight" = 1 +
2014 "weight_of_added_delegations_for_population_snapshot"(
2015 "issue_id_p",
2016 "member_id_v",
2017 '{}'
2018 )
2019 WHERE "issue_id" = "issue_id_p"
2020 AND "event" = 'periodic'
2021 AND "member_id" = "member_id_v";
2022 END LOOP;
2023 RETURN;
2024 END;
2025 $$;
2027 COMMENT ON FUNCTION "create_population_snapshot"
2028 ( "issue_id_p" "issue"."id"%TYPE )
2029 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.';
2032 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2033 ( "issue_id_p" "issue"."id"%TYPE,
2034 "member_id_p" "member"."id"%TYPE,
2035 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2036 RETURNS "direct_interest_snapshot"."weight"%TYPE
2037 LANGUAGE 'plpgsql' VOLATILE AS $$
2038 DECLARE
2039 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2040 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2041 "weight_v" INT4;
2042 "sub_weight_v" INT4;
2043 BEGIN
2044 "weight_v" := 0;
2045 FOR "issue_delegation_row" IN
2046 SELECT * FROM "issue_delegation"
2047 WHERE "trustee_id" = "member_id_p"
2048 AND "issue_id" = "issue_id_p"
2049 LOOP
2050 IF NOT EXISTS (
2051 SELECT NULL FROM "direct_interest_snapshot"
2052 WHERE "issue_id" = "issue_id_p"
2053 AND "event" = 'periodic'
2054 AND "member_id" = "issue_delegation_row"."truster_id"
2055 ) AND NOT EXISTS (
2056 SELECT NULL FROM "delegating_interest_snapshot"
2057 WHERE "issue_id" = "issue_id_p"
2058 AND "event" = 'periodic'
2059 AND "member_id" = "issue_delegation_row"."truster_id"
2060 ) THEN
2061 "delegate_member_ids_v" :=
2062 "member_id_p" || "delegate_member_ids_p";
2063 INSERT INTO "delegating_interest_snapshot" (
2064 "issue_id",
2065 "event",
2066 "member_id",
2067 "scope",
2068 "delegate_member_ids"
2069 ) VALUES (
2070 "issue_id_p",
2071 'periodic',
2072 "issue_delegation_row"."truster_id",
2073 "issue_delegation_row"."scope",
2074 "delegate_member_ids_v"
2075 );
2076 "sub_weight_v" := 1 +
2077 "weight_of_added_delegations_for_interest_snapshot"(
2078 "issue_id_p",
2079 "issue_delegation_row"."truster_id",
2080 "delegate_member_ids_v"
2081 );
2082 UPDATE "delegating_interest_snapshot"
2083 SET "weight" = "sub_weight_v"
2084 WHERE "issue_id" = "issue_id_p"
2085 AND "event" = 'periodic'
2086 AND "member_id" = "issue_delegation_row"."truster_id";
2087 "weight_v" := "weight_v" + "sub_weight_v";
2088 END IF;
2089 END LOOP;
2090 RETURN "weight_v";
2091 END;
2092 $$;
2094 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2095 ( "issue"."id"%TYPE,
2096 "member"."id"%TYPE,
2097 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2098 IS 'Helper function for "create_interest_snapshot" function';
2101 CREATE FUNCTION "create_interest_snapshot"
2102 ( "issue_id_p" "issue"."id"%TYPE )
2103 RETURNS VOID
2104 LANGUAGE 'plpgsql' VOLATILE AS $$
2105 DECLARE
2106 "member_id_v" "member"."id"%TYPE;
2107 BEGIN
2108 DELETE FROM "direct_interest_snapshot"
2109 WHERE "issue_id" = "issue_id_p"
2110 AND "event" = 'periodic';
2111 DELETE FROM "delegating_interest_snapshot"
2112 WHERE "issue_id" = "issue_id_p"
2113 AND "event" = 'periodic';
2114 DELETE FROM "direct_supporter_snapshot"
2115 WHERE "issue_id" = "issue_id_p"
2116 AND "event" = 'periodic';
2117 INSERT INTO "direct_interest_snapshot"
2118 ("issue_id", "event", "member_id", "voting_requested")
2119 SELECT
2120 "issue_id_p" AS "issue_id",
2121 'periodic' AS "event",
2122 "member"."id" AS "member_id",
2123 "interest"."voting_requested"
2124 FROM "interest" JOIN "member"
2125 ON "interest"."member_id" = "member"."id"
2126 WHERE "interest"."issue_id" = "issue_id_p"
2127 AND "member"."active";
2128 FOR "member_id_v" IN
2129 SELECT "member_id" FROM "direct_interest_snapshot"
2130 WHERE "issue_id" = "issue_id_p"
2131 AND "event" = 'periodic'
2132 LOOP
2133 UPDATE "direct_interest_snapshot" SET
2134 "weight" = 1 +
2135 "weight_of_added_delegations_for_interest_snapshot"(
2136 "issue_id_p",
2137 "member_id_v",
2138 '{}'
2139 )
2140 WHERE "issue_id" = "issue_id_p"
2141 AND "event" = 'periodic'
2142 AND "member_id" = "member_id_v";
2143 END LOOP;
2144 INSERT INTO "direct_supporter_snapshot"
2145 ( "issue_id", "initiative_id", "event", "member_id",
2146 "informed", "satisfied" )
2147 SELECT
2148 "issue_id_p" AS "issue_id",
2149 "initiative"."id" AS "initiative_id",
2150 'periodic' AS "event",
2151 "member"."id" AS "member_id",
2152 "supporter"."draft_id" = "current_draft"."id" AS "informed",
2153 NOT EXISTS (
2154 SELECT NULL FROM "critical_opinion"
2155 WHERE "initiative_id" = "initiative"."id"
2156 AND "member_id" = "member"."id"
2157 ) AS "satisfied"
2158 FROM "supporter"
2159 JOIN "member"
2160 ON "supporter"."member_id" = "member"."id"
2161 JOIN "initiative"
2162 ON "supporter"."initiative_id" = "initiative"."id"
2163 JOIN "current_draft"
2164 ON "initiative"."id" = "current_draft"."initiative_id"
2165 JOIN "direct_interest_snapshot"
2166 ON "member"."id" = "direct_interest_snapshot"."member_id"
2167 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
2168 AND "event" = 'periodic'
2169 WHERE "member"."active"
2170 AND "initiative"."issue_id" = "issue_id_p";
2171 RETURN;
2172 END;
2173 $$;
2175 COMMENT ON FUNCTION "create_interest_snapshot"
2176 ( "issue"."id"%TYPE )
2177 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.';
2180 CREATE FUNCTION "create_snapshot"
2181 ( "issue_id_p" "issue"."id"%TYPE )
2182 RETURNS VOID
2183 LANGUAGE 'plpgsql' VOLATILE AS $$
2184 DECLARE
2185 "initiative_id_v" "initiative"."id"%TYPE;
2186 "suggestion_id_v" "suggestion"."id"%TYPE;
2187 BEGIN
2188 PERFORM "global_lock"();
2189 PERFORM "create_population_snapshot"("issue_id_p");
2190 PERFORM "create_interest_snapshot"("issue_id_p");
2191 UPDATE "issue" SET
2192 "snapshot" = now(),
2193 "latest_snapshot_event" = 'periodic',
2194 "population" = (
2195 SELECT coalesce(sum("weight"), 0)
2196 FROM "direct_population_snapshot"
2197 WHERE "issue_id" = "issue_id_p"
2198 AND "event" = 'periodic'
2199 ),
2200 "vote_now" = (
2201 SELECT coalesce(sum("weight"), 0)
2202 FROM "direct_interest_snapshot"
2203 WHERE "issue_id" = "issue_id_p"
2204 AND "event" = 'periodic'
2205 AND "voting_requested" = TRUE
2206 ),
2207 "vote_later" = (
2208 SELECT coalesce(sum("weight"), 0)
2209 FROM "direct_interest_snapshot"
2210 WHERE "issue_id" = "issue_id_p"
2211 AND "event" = 'periodic'
2212 AND "voting_requested" = FALSE
2213 )
2214 WHERE "id" = "issue_id_p";
2215 FOR "initiative_id_v" IN
2216 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
2217 LOOP
2218 UPDATE "initiative" SET
2219 "supporter_count" = (
2220 SELECT coalesce(sum("di"."weight"), 0)
2221 FROM "direct_interest_snapshot" AS "di"
2222 JOIN "direct_supporter_snapshot" AS "ds"
2223 ON "di"."member_id" = "ds"."member_id"
2224 WHERE "di"."issue_id" = "issue_id_p"
2225 AND "di"."event" = 'periodic'
2226 AND "ds"."initiative_id" = "initiative_id_v"
2227 AND "ds"."event" = 'periodic'
2228 ),
2229 "informed_supporter_count" = (
2230 SELECT coalesce(sum("di"."weight"), 0)
2231 FROM "direct_interest_snapshot" AS "di"
2232 JOIN "direct_supporter_snapshot" AS "ds"
2233 ON "di"."member_id" = "ds"."member_id"
2234 WHERE "di"."issue_id" = "issue_id_p"
2235 AND "di"."event" = 'periodic'
2236 AND "ds"."initiative_id" = "initiative_id_v"
2237 AND "ds"."event" = 'periodic'
2238 AND "ds"."informed"
2239 ),
2240 "satisfied_supporter_count" = (
2241 SELECT coalesce(sum("di"."weight"), 0)
2242 FROM "direct_interest_snapshot" AS "di"
2243 JOIN "direct_supporter_snapshot" AS "ds"
2244 ON "di"."member_id" = "ds"."member_id"
2245 WHERE "di"."issue_id" = "issue_id_p"
2246 AND "di"."event" = 'periodic'
2247 AND "ds"."initiative_id" = "initiative_id_v"
2248 AND "ds"."event" = 'periodic'
2249 AND "ds"."satisfied"
2250 ),
2251 "satisfied_informed_supporter_count" = (
2252 SELECT coalesce(sum("di"."weight"), 0)
2253 FROM "direct_interest_snapshot" AS "di"
2254 JOIN "direct_supporter_snapshot" AS "ds"
2255 ON "di"."member_id" = "ds"."member_id"
2256 WHERE "di"."issue_id" = "issue_id_p"
2257 AND "di"."event" = 'periodic'
2258 AND "ds"."initiative_id" = "initiative_id_v"
2259 AND "ds"."event" = 'periodic'
2260 AND "ds"."informed"
2261 AND "ds"."satisfied"
2262 )
2263 WHERE "id" = "initiative_id_v";
2264 FOR "suggestion_id_v" IN
2265 SELECT "id" FROM "suggestion"
2266 WHERE "initiative_id" = "initiative_id_v"
2267 LOOP
2268 UPDATE "suggestion" SET
2269 "minus2_unfulfilled_count" = (
2270 SELECT coalesce(sum("snapshot"."weight"), 0)
2271 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2272 ON "opinion"."member_id" = "snapshot"."member_id"
2273 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2274 AND "snapshot"."issue_id" = "issue_id_p"
2275 AND "opinion"."degree" = -2
2276 AND "opinion"."fulfilled" = FALSE
2277 ),
2278 "minus2_fulfilled_count" = (
2279 SELECT coalesce(sum("snapshot"."weight"), 0)
2280 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2281 ON "opinion"."member_id" = "snapshot"."member_id"
2282 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2283 AND "snapshot"."issue_id" = "issue_id_p"
2284 AND "opinion"."degree" = -2
2285 AND "opinion"."fulfilled" = TRUE
2286 ),
2287 "minus1_unfulfilled_count" = (
2288 SELECT coalesce(sum("snapshot"."weight"), 0)
2289 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2290 ON "opinion"."member_id" = "snapshot"."member_id"
2291 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2292 AND "snapshot"."issue_id" = "issue_id_p"
2293 AND "opinion"."degree" = -1
2294 AND "opinion"."fulfilled" = FALSE
2295 ),
2296 "minus1_fulfilled_count" = (
2297 SELECT coalesce(sum("snapshot"."weight"), 0)
2298 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2299 ON "opinion"."member_id" = "snapshot"."member_id"
2300 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2301 AND "snapshot"."issue_id" = "issue_id_p"
2302 AND "opinion"."degree" = -1
2303 AND "opinion"."fulfilled" = TRUE
2304 ),
2305 "plus1_unfulfilled_count" = (
2306 SELECT coalesce(sum("snapshot"."weight"), 0)
2307 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2308 ON "opinion"."member_id" = "snapshot"."member_id"
2309 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2310 AND "snapshot"."issue_id" = "issue_id_p"
2311 AND "opinion"."degree" = 1
2312 AND "opinion"."fulfilled" = FALSE
2313 ),
2314 "plus1_fulfilled_count" = (
2315 SELECT coalesce(sum("snapshot"."weight"), 0)
2316 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2317 ON "opinion"."member_id" = "snapshot"."member_id"
2318 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2319 AND "snapshot"."issue_id" = "issue_id_p"
2320 AND "opinion"."degree" = 1
2321 AND "opinion"."fulfilled" = TRUE
2322 ),
2323 "plus2_unfulfilled_count" = (
2324 SELECT coalesce(sum("snapshot"."weight"), 0)
2325 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2326 ON "opinion"."member_id" = "snapshot"."member_id"
2327 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2328 AND "snapshot"."issue_id" = "issue_id_p"
2329 AND "opinion"."degree" = 2
2330 AND "opinion"."fulfilled" = FALSE
2331 ),
2332 "plus2_fulfilled_count" = (
2333 SELECT coalesce(sum("snapshot"."weight"), 0)
2334 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2335 ON "opinion"."member_id" = "snapshot"."member_id"
2336 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2337 AND "snapshot"."issue_id" = "issue_id_p"
2338 AND "opinion"."degree" = 2
2339 AND "opinion"."fulfilled" = TRUE
2340 )
2341 WHERE "suggestion"."id" = "suggestion_id_v";
2342 END LOOP;
2343 END LOOP;
2344 RETURN;
2345 END;
2346 $$;
2348 COMMENT ON FUNCTION "create_snapshot"
2349 ( "issue"."id"%TYPE )
2350 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.';
2353 CREATE FUNCTION "set_snapshot_event"
2354 ( "issue_id_p" "issue"."id"%TYPE,
2355 "event_p" "snapshot_event" )
2356 RETURNS VOID
2357 LANGUAGE 'plpgsql' VOLATILE AS $$
2358 DECLARE
2359 "event_v" "issue"."latest_snapshot_event"%TYPE;
2360 BEGIN
2361 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
2362 WHERE "id" = "issue_id_p" FOR UPDATE;
2363 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
2364 WHERE "id" = "issue_id_p";
2365 UPDATE "direct_population_snapshot" SET "event" = "event_p"
2366 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2367 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
2368 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2369 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
2370 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2371 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
2372 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2373 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
2374 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2375 RETURN;
2376 END;
2377 $$;
2379 COMMENT ON FUNCTION "set_snapshot_event"
2380 ( "issue"."id"%TYPE,
2381 "snapshot_event" )
2382 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
2386 ---------------------
2387 -- Freezing issues --
2388 ---------------------
2390 CREATE FUNCTION "freeze_after_snapshot"
2391 ( "issue_id_p" "issue"."id"%TYPE )
2392 RETURNS VOID
2393 LANGUAGE 'plpgsql' VOLATILE AS $$
2394 DECLARE
2395 "issue_row" "issue"%ROWTYPE;
2396 "policy_row" "policy"%ROWTYPE;
2397 "initiative_row" "initiative"%ROWTYPE;
2398 BEGIN
2399 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2400 SELECT * INTO "policy_row"
2401 FROM "policy" WHERE "id" = "issue_row"."policy_id";
2402 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
2403 UPDATE "issue" SET
2404 "accepted" = coalesce("accepted", now()),
2405 "half_frozen" = coalesce("half_frozen", now()),
2406 "fully_frozen" = now()
2407 WHERE "id" = "issue_id_p";
2408 FOR "initiative_row" IN
2409 SELECT * FROM "initiative"
2410 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
2411 LOOP
2412 IF
2413 "initiative_row"."satisfied_supporter_count" > 0 AND
2414 "initiative_row"."satisfied_supporter_count" *
2415 "policy_row"."initiative_quorum_den" >=
2416 "issue_row"."population" * "policy_row"."initiative_quorum_num"
2417 THEN
2418 UPDATE "initiative" SET "admitted" = TRUE
2419 WHERE "id" = "initiative_row"."id";
2420 ELSE
2421 UPDATE "initiative" SET "admitted" = FALSE
2422 WHERE "id" = "initiative_row"."id";
2423 END IF;
2424 END LOOP;
2425 IF NOT EXISTS (
2426 SELECT NULL FROM "initiative"
2427 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
2428 ) THEN
2429 PERFORM "close_voting"("issue_id_p");
2430 END IF;
2431 RETURN;
2432 END;
2433 $$;
2435 COMMENT ON FUNCTION "freeze_after_snapshot"
2436 ( "issue"."id"%TYPE )
2437 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
2440 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
2441 RETURNS VOID
2442 LANGUAGE 'plpgsql' VOLATILE AS $$
2443 DECLARE
2444 "issue_row" "issue"%ROWTYPE;
2445 BEGIN
2446 PERFORM "create_snapshot"("issue_id_p");
2447 PERFORM "freeze_after_snapshot"("issue_id_p");
2448 RETURN;
2449 END;
2450 $$;
2452 COMMENT ON FUNCTION "freeze_after_snapshot"
2453 ( "issue"."id"%TYPE )
2454 IS 'Freeze an issue manually (fully) and start voting';
2458 -----------------------
2459 -- Counting of votes --
2460 -----------------------
2463 CREATE FUNCTION "weight_of_added_vote_delegations"
2464 ( "issue_id_p" "issue"."id"%TYPE,
2465 "member_id_p" "member"."id"%TYPE,
2466 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
2467 RETURNS "direct_voter"."weight"%TYPE
2468 LANGUAGE 'plpgsql' VOLATILE AS $$
2469 DECLARE
2470 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2471 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
2472 "weight_v" INT4;
2473 "sub_weight_v" INT4;
2474 BEGIN
2475 "weight_v" := 0;
2476 FOR "issue_delegation_row" IN
2477 SELECT * FROM "issue_delegation"
2478 WHERE "trustee_id" = "member_id_p"
2479 AND "issue_id" = "issue_id_p"
2480 LOOP
2481 IF NOT EXISTS (
2482 SELECT NULL FROM "direct_voter"
2483 WHERE "member_id" = "issue_delegation_row"."truster_id"
2484 AND "issue_id" = "issue_id_p"
2485 ) AND NOT EXISTS (
2486 SELECT NULL FROM "delegating_voter"
2487 WHERE "member_id" = "issue_delegation_row"."truster_id"
2488 AND "issue_id" = "issue_id_p"
2489 ) THEN
2490 "delegate_member_ids_v" :=
2491 "member_id_p" || "delegate_member_ids_p";
2492 INSERT INTO "delegating_voter" (
2493 "issue_id",
2494 "member_id",
2495 "scope",
2496 "delegate_member_ids"
2497 ) VALUES (
2498 "issue_id_p",
2499 "issue_delegation_row"."truster_id",
2500 "issue_delegation_row"."scope",
2501 "delegate_member_ids_v"
2502 );
2503 "sub_weight_v" := 1 +
2504 "weight_of_added_vote_delegations"(
2505 "issue_id_p",
2506 "issue_delegation_row"."truster_id",
2507 "delegate_member_ids_v"
2508 );
2509 UPDATE "delegating_voter"
2510 SET "weight" = "sub_weight_v"
2511 WHERE "issue_id" = "issue_id_p"
2512 AND "member_id" = "issue_delegation_row"."truster_id";
2513 "weight_v" := "weight_v" + "sub_weight_v";
2514 END IF;
2515 END LOOP;
2516 RETURN "weight_v";
2517 END;
2518 $$;
2520 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
2521 ( "issue"."id"%TYPE,
2522 "member"."id"%TYPE,
2523 "delegating_voter"."delegate_member_ids"%TYPE )
2524 IS 'Helper function for "add_vote_delegations" function';
2527 CREATE FUNCTION "add_vote_delegations"
2528 ( "issue_id_p" "issue"."id"%TYPE )
2529 RETURNS VOID
2530 LANGUAGE 'plpgsql' VOLATILE AS $$
2531 DECLARE
2532 "member_id_v" "member"."id"%TYPE;
2533 BEGIN
2534 FOR "member_id_v" IN
2535 SELECT "member_id" FROM "direct_voter"
2536 WHERE "issue_id" = "issue_id_p"
2537 LOOP
2538 UPDATE "direct_voter" SET
2539 "weight" = "weight" + "weight_of_added_vote_delegations"(
2540 "issue_id_p",
2541 "member_id_v",
2542 '{}'
2543 )
2544 WHERE "member_id" = "member_id_v"
2545 AND "issue_id" = "issue_id_p";
2546 END LOOP;
2547 RETURN;
2548 END;
2549 $$;
2551 COMMENT ON FUNCTION "add_vote_delegations"
2552 ( "issue_id_p" "issue"."id"%TYPE )
2553 IS 'Helper function for "close_voting" function';
2556 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
2557 RETURNS VOID
2558 LANGUAGE 'plpgsql' VOLATILE AS $$
2559 DECLARE
2560 "issue_row" "issue"%ROWTYPE;
2561 "member_id_v" "member"."id"%TYPE;
2562 BEGIN
2563 PERFORM "global_lock"();
2564 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2565 DELETE FROM "delegating_voter"
2566 WHERE "issue_id" = "issue_id_p";
2567 DELETE FROM "direct_voter"
2568 WHERE "issue_id" = "issue_id_p"
2569 AND "autoreject" = TRUE;
2570 DELETE FROM "direct_voter" USING "member"
2571 WHERE "direct_voter"."member_id" = "member"."id"
2572 AND "direct_voter"."issue_id" = "issue_id_p"
2573 AND "member"."active" = FALSE;
2574 UPDATE "direct_voter" SET "weight" = 1
2575 WHERE "issue_id" = "issue_id_p";
2576 PERFORM "add_vote_delegations"("issue_id_p");
2577 FOR "member_id_v" IN
2578 SELECT "interest"."member_id"
2579 FROM "interest"
2580 LEFT JOIN "direct_voter"
2581 ON "interest"."member_id" = "direct_voter"."member_id"
2582 AND "interest"."issue_id" = "direct_voter"."issue_id"
2583 LEFT JOIN "delegating_voter"
2584 ON "interest"."member_id" = "delegating_voter"."member_id"
2585 AND "interest"."issue_id" = "delegating_voter"."issue_id"
2586 WHERE "interest"."issue_id" = "issue_id_p"
2587 AND "interest"."autoreject" = TRUE
2588 AND "direct_voter"."member_id" ISNULL
2589 AND "delegating_voter"."member_id" ISNULL
2590 UNION SELECT "membership"."member_id"
2591 FROM "membership"
2592 LEFT JOIN "interest"
2593 ON "membership"."member_id" = "interest"."member_id"
2594 AND "interest"."issue_id" = "issue_id_p"
2595 LEFT JOIN "direct_voter"
2596 ON "membership"."member_id" = "direct_voter"."member_id"
2597 AND "direct_voter"."issue_id" = "issue_id_p"
2598 LEFT JOIN "delegating_voter"
2599 ON "membership"."member_id" = "delegating_voter"."member_id"
2600 AND "delegating_voter"."issue_id" = "issue_id_p"
2601 WHERE "membership"."area_id" = "issue_row"."area_id"
2602 AND "membership"."autoreject" = TRUE
2603 AND "interest"."autoreject" ISNULL
2604 AND "direct_voter"."member_id" ISNULL
2605 AND "delegating_voter"."member_id" ISNULL
2606 LOOP
2607 INSERT INTO "direct_voter"
2608 ("member_id", "issue_id", "weight", "autoreject") VALUES
2609 ("member_id_v", "issue_id_p", 1, TRUE);
2610 INSERT INTO "vote" (
2611 "member_id",
2612 "issue_id",
2613 "initiative_id",
2614 "grade"
2615 ) SELECT
2616 "member_id_v" AS "member_id",
2617 "issue_id_p" AS "issue_id",
2618 "id" AS "initiative_id",
2619 -1 AS "grade"
2620 FROM "initiative" WHERE "issue_id" = "issue_id_p";
2621 END LOOP;
2622 PERFORM "add_vote_delegations"("issue_id_p");
2623 UPDATE "issue" SET
2624 "voter_count" = (
2625 SELECT coalesce(sum("weight"), 0)
2626 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
2627 )
2628 WHERE "id" = "issue_id_p";
2629 UPDATE "initiative" SET
2630 "positive_votes" = "vote_counts"."positive_votes",
2631 "negative_votes" = "vote_counts"."negative_votes",
2632 "agreed" = CASE WHEN "majority_strict" THEN
2633 "vote_counts"."positive_votes" * "majority_den" >
2634 "majority_num" *
2635 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
2636 ELSE
2637 "vote_counts"."positive_votes" * "majority_den" >=
2638 "majority_num" *
2639 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
2640 END
2641 FROM
2642 ( SELECT
2643 "initiative"."id" AS "initiative_id",
2644 coalesce(
2645 sum(
2646 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
2647 ),
2648 0
2649 ) AS "positive_votes",
2650 coalesce(
2651 sum(
2652 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
2653 ),
2654 0
2655 ) AS "negative_votes"
2656 FROM "initiative"
2657 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
2658 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
2659 LEFT JOIN "direct_voter"
2660 ON "direct_voter"."issue_id" = "initiative"."issue_id"
2661 LEFT JOIN "vote"
2662 ON "vote"."initiative_id" = "initiative"."id"
2663 AND "vote"."member_id" = "direct_voter"."member_id"
2664 WHERE "initiative"."issue_id" = "issue_id_p"
2665 AND "initiative"."admitted" -- NOTE: NULL case is handled too
2666 GROUP BY "initiative"."id"
2667 ) AS "vote_counts",
2668 "issue",
2669 "policy"
2670 WHERE "vote_counts"."initiative_id" = "initiative"."id"
2671 AND "issue"."id" = "initiative"."issue_id"
2672 AND "policy"."id" = "issue"."policy_id";
2673 UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p";
2674 END;
2675 $$;
2677 COMMENT ON FUNCTION "close_voting"
2678 ( "issue"."id"%TYPE )
2679 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.';
2682 CREATE FUNCTION "init_array"("dim_p" INTEGER)
2683 RETURNS INT4[]
2684 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2685 DECLARE
2686 "i" INTEGER;
2687 "ary_text_v" TEXT;
2688 BEGIN
2689 IF "dim_p" >= 1 THEN
2690 "ary_text_v" := '{NULL';
2691 "i" := "dim_p";
2692 LOOP
2693 "i" := "i" - 1;
2694 EXIT WHEN "i" = 0;
2695 "ary_text_v" := "ary_text_v" || ',NULL';
2696 END LOOP;
2697 "ary_text_v" := "ary_text_v" || '}';
2698 RETURN "ary_text_v"::INT4[][];
2699 ELSE
2700 RAISE EXCEPTION 'Dimension needs to be at least 1.';
2701 END IF;
2702 END;
2703 $$;
2705 COMMENT ON FUNCTION "init_array"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
2708 CREATE FUNCTION "init_square_matrix"("dim_p" INTEGER)
2709 RETURNS INT4[][]
2710 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2711 DECLARE
2712 "i" INTEGER;
2713 "row_text_v" TEXT;
2714 "ary_text_v" TEXT;
2715 BEGIN
2716 IF "dim_p" >= 1 THEN
2717 "row_text_v" := '{NULL';
2718 "i" := "dim_p";
2719 LOOP
2720 "i" := "i" - 1;
2721 EXIT WHEN "i" = 0;
2722 "row_text_v" := "row_text_v" || ',NULL';
2723 END LOOP;
2724 "row_text_v" := "row_text_v" || '}';
2725 "ary_text_v" := '{' || "row_text_v";
2726 "i" := "dim_p";
2727 LOOP
2728 "i" := "i" - 1;
2729 EXIT WHEN "i" = 0;
2730 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
2731 END LOOP;
2732 "ary_text_v" := "ary_text_v" || '}';
2733 RETURN "ary_text_v"::INT4[][];
2734 ELSE
2735 RAISE EXCEPTION 'Dimension needs to be at least 1.';
2736 END IF;
2737 END;
2738 $$;
2740 COMMENT ON FUNCTION "init_square_matrix"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
2743 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
2744 RETURNS VOID
2745 LANGUAGE 'plpgsql' VOLATILE AS $$
2746 DECLARE
2747 "dimension_v" INTEGER;
2748 "matrix" INT4[][];
2749 "i" INTEGER;
2750 "j" INTEGER;
2751 "k" INTEGER;
2752 "battle_row" "battle"%ROWTYPE;
2753 "rank_ary" INT4[];
2754 "rank_v" INT4;
2755 "done_v" INTEGER;
2756 "winners_ary" INTEGER[];
2757 "initiative_id_v" "initiative"."id"%TYPE;
2758 BEGIN
2759 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2760 -- Prepare matrix for Schulze-Method:
2761 SELECT count(1) INTO "dimension_v" FROM "initiative"
2762 WHERE "issue_id" = "issue_id_p" AND "agreed";
2763 IF "dimension_v" = 1 THEN
2764 UPDATE "initiative" SET "rank" = 1
2765 WHERE "issue_id" = "issue_id_p" AND "agreed";
2766 ELSIF "dimension_v" > 1 THEN
2767 "matrix" := "init_square_matrix"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2768 "i" := 1;
2769 "j" := 2;
2770 -- Fill matrix with data from "battle" view
2771 FOR "battle_row" IN
2772 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
2773 ORDER BY "winning_initiative_id", "losing_initiative_id"
2774 LOOP
2775 "matrix"["i"]["j"] := "battle_row"."count";
2776 IF "j" = "dimension_v" THEN
2777 "i" := "i" + 1;
2778 "j" := 1;
2779 ELSE
2780 "j" := "j" + 1;
2781 IF "j" = "i" THEN
2782 "j" := "j" + 1;
2783 END IF;
2784 END IF;
2785 END LOOP;
2786 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
2787 RAISE EXCEPTION 'Wrong battle count (should not happen)';
2788 END IF;
2789 -- Delete losers from matrix:
2790 "i" := 1;
2791 LOOP
2792 "j" := "i" + 1;
2793 LOOP
2794 IF "i" != "j" THEN
2795 IF "matrix"["i"]["j"] < "matrix"["j"]["i"] THEN
2796 "matrix"["i"]["j"] := 0;
2797 ELSIF matrix[j][i] < matrix[i][j] THEN
2798 "matrix"["j"]["i"] := 0;
2799 ELSE
2800 "matrix"["i"]["j"] := 0;
2801 "matrix"["j"]["i"] := 0;
2802 END IF;
2803 END IF;
2804 EXIT WHEN "j" = "dimension_v";
2805 "j" := "j" + 1;
2806 END LOOP;
2807 EXIT WHEN "i" = "dimension_v" - 1;
2808 "i" := "i" + 1;
2809 END LOOP;
2810 -- Find best paths:
2811 "i" := 1;
2812 LOOP
2813 "j" := 1;
2814 LOOP
2815 IF "i" != "j" THEN
2816 "k" := 1;
2817 LOOP
2818 IF "i" != "k" AND "j" != "k" THEN
2819 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
2820 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
2821 "matrix"["j"]["k"] := "matrix"["j"]["i"];
2822 END IF;
2823 ELSE
2824 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
2825 "matrix"["j"]["k"] := "matrix"["i"]["k"];
2826 END IF;
2827 END IF;
2828 END IF;
2829 EXIT WHEN "k" = "dimension_v";
2830 "k" := "k" + 1;
2831 END LOOP;
2832 END IF;
2833 EXIT WHEN "j" = "dimension_v";
2834 "j" := "j" + 1;
2835 END LOOP;
2836 EXIT WHEN "i" = "dimension_v";
2837 "i" := "i" + 1;
2838 END LOOP;
2839 -- Determine order of winners:
2840 "rank_ary" := "init_array"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2841 "rank_v" := 1;
2842 "done_v" := 0;
2843 LOOP
2844 "winners_ary" := '{}';
2845 "i" := 1;
2846 LOOP
2847 IF "rank_ary"["i"] ISNULL THEN
2848 "j" := 1;
2849 LOOP
2850 IF
2851 "i" != "j" AND
2852 "rank_ary"["j"] ISNULL AND
2853 "matrix"["j"]["i"] > "matrix"["i"]["j"]
2854 THEN
2855 -- someone else is better
2856 EXIT;
2857 END IF;
2858 IF "j" = "dimension_v" THEN
2859 -- noone is better
2860 "winners_ary" := "winners_ary" || "i";
2861 EXIT;
2862 END IF;
2863 "j" := "j" + 1;
2864 END LOOP;
2865 END IF;
2866 EXIT WHEN "i" = "dimension_v";
2867 "i" := "i" + 1;
2868 END LOOP;
2869 "i" := 1;
2870 LOOP
2871 "rank_ary"["winners_ary"["i"]] := "rank_v";
2872 "done_v" := "done_v" + 1;
2873 EXIT WHEN "i" = array_upper("winners_ary", 1);
2874 "i" := "i" + 1;
2875 END LOOP;
2876 EXIT WHEN "done_v" = "dimension_v";
2877 "rank_v" := "rank_v" + 1;
2878 END LOOP;
2879 -- write preliminary ranks:
2880 "i" := 1;
2881 FOR "initiative_id_v" IN
2882 SELECT "id" FROM "initiative"
2883 WHERE "issue_id" = "issue_id_p" AND "agreed"
2884 ORDER BY "id"
2885 LOOP
2886 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
2887 WHERE "id" = "initiative_id_v";
2888 "i" := "i" + 1;
2889 END LOOP;
2890 IF "i" != "dimension_v" + 1 THEN
2891 RAISE EXCEPTION 'Wrong winner count (should not happen)';
2892 END IF;
2893 -- straighten ranks (start counting with 1, no equal ranks):
2894 "rank_v" := 1;
2895 FOR "initiative_id_v" IN
2896 SELECT "id" FROM "initiative"
2897 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
2898 ORDER BY
2899 "rank",
2900 "vote_ratio"("positive_votes", "negative_votes") DESC,
2901 "id"
2902 LOOP
2903 UPDATE "initiative" SET "rank" = "rank_v"
2904 WHERE "id" = "initiative_id_v";
2905 "rank_v" := "rank_v" + 1;
2906 END LOOP;
2907 END IF;
2908 -- mark issue as finished
2909 UPDATE "issue" SET "ranks_available" = TRUE
2910 WHERE "id" = "issue_id_p";
2911 RETURN;
2912 END;
2913 $$;
2915 COMMENT ON FUNCTION "calculate_ranks"
2916 ( "issue"."id"%TYPE )
2917 IS 'Determine ranking (Votes have to be counted first)';
2921 -----------------------------
2922 -- Automatic state changes --
2923 -----------------------------
2926 CREATE FUNCTION "check_issue"
2927 ( "issue_id_p" "issue"."id"%TYPE )
2928 RETURNS VOID
2929 LANGUAGE 'plpgsql' VOLATILE AS $$
2930 DECLARE
2931 "issue_row" "issue"%ROWTYPE;
2932 "policy_row" "policy"%ROWTYPE;
2933 "voting_requested_v" BOOLEAN;
2934 BEGIN
2935 PERFORM "global_lock"();
2936 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2937 IF "issue_row"."closed" ISNULL THEN
2938 SELECT * INTO "policy_row" FROM "policy"
2939 WHERE "id" = "issue_row"."policy_id";
2940 IF "issue_row"."fully_frozen" ISNULL THEN
2941 PERFORM "create_snapshot"("issue_id_p");
2942 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2943 END IF;
2944 IF "issue_row"."accepted" ISNULL THEN
2945 IF EXISTS (
2946 SELECT NULL FROM "initiative"
2947 WHERE "issue_id" = "issue_id_p"
2948 AND "supporter_count" > 0
2949 AND "supporter_count" * "policy_row"."issue_quorum_den"
2950 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
2951 ) THEN
2952 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
2953 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later
2954 UPDATE "issue" SET "accepted" = "issue_row"."accepted"
2955 WHERE "id" = "issue_row"."id";
2956 ELSIF
2957 now() >= "issue_row"."created" + "issue_row"."admission_time"
2958 THEN
2959 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
2960 UPDATE "issue" SET "closed" = now()
2961 WHERE "id" = "issue_row"."id";
2962 END IF;
2963 END IF;
2964 IF
2965 "issue_row"."accepted" NOTNULL AND
2966 "issue_row"."half_frozen" ISNULL
2967 THEN
2968 SELECT
2969 CASE
2970 WHEN "vote_now" * 2 > "issue_row"."population" THEN
2971 TRUE
2972 WHEN "vote_later" * 2 > "issue_row"."population" THEN
2973 FALSE
2974 ELSE NULL
2975 END
2976 INTO "voting_requested_v"
2977 FROM "issue" WHERE "id" = "issue_id_p";
2978 IF
2979 "voting_requested_v" OR (
2980 "voting_requested_v" ISNULL AND
2981 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
2982 )
2983 THEN
2984 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
2985 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later
2986 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
2987 WHERE "id" = "issue_row"."id";
2988 END IF;
2989 END IF;
2990 IF
2991 "issue_row"."half_frozen" NOTNULL AND
2992 "issue_row"."fully_frozen" ISNULL AND
2993 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
2994 THEN
2995 PERFORM "freeze_after_snapshot"("issue_id_p");
2996 -- "issue" might change, thus "issue_row" has to be updated below
2997 END IF;
2998 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2999 IF
3000 "issue_row"."closed" ISNULL AND
3001 "issue_row"."fully_frozen" NOTNULL AND
3002 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
3003 THEN
3004 PERFORM "close_voting"("issue_id_p");
3005 END IF;
3006 END IF;
3007 RETURN;
3008 END;
3009 $$;
3011 COMMENT ON FUNCTION "check_issue"
3012 ( "issue"."id"%TYPE )
3013 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.';
3016 CREATE FUNCTION "check_everything"()
3017 RETURNS VOID
3018 LANGUAGE 'plpgsql' VOLATILE AS $$
3019 DECLARE
3020 "issue_id_v" "issue"."id"%TYPE;
3021 BEGIN
3022 DELETE FROM "expired_session";
3023 PERFORM "calculate_member_counts"();
3024 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
3025 PERFORM "check_issue"("issue_id_v");
3026 END LOOP;
3027 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
3028 PERFORM "calculate_ranks"("issue_id_v");
3029 END LOOP;
3030 RETURN;
3031 END;
3032 $$;
3034 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.';
3038 ------------------------------
3039 -- Deletion of private data --
3040 ------------------------------
3043 CREATE FUNCTION "delete_private_data"()
3044 RETURNS VOID
3045 LANGUAGE 'plpgsql' VOLATILE AS $$
3046 DECLARE
3047 "issue_id_v" "issue"."id"%TYPE;
3048 BEGIN
3049 UPDATE "member" SET
3050 "login" = 'login' || "id"::text,
3051 "password" = NULL,
3052 "notify_email" = NULL,
3053 "notify_email_unconfirmed" = NULL,
3054 "notify_email_secret" = NULL,
3055 "notify_email_secret_expiry" = NULL,
3056 "password_reset_secret" = NULL,
3057 "password_reset_secret_expiry" = NULL,
3058 "organizational_unit" = NULL,
3059 "internal_posts" = NULL,
3060 "realname" = NULL,
3061 "birthday" = NULL,
3062 "address" = NULL,
3063 "email" = NULL,
3064 "xmpp_address" = NULL,
3065 "website" = NULL,
3066 "phone" = NULL,
3067 "mobile_phone" = NULL,
3068 "profession" = NULL,
3069 "external_memberships" = NULL,
3070 "external_posts" = NULL,
3071 "statement" = NULL;
3072 -- "text_search_data" is updated by triggers
3073 DELETE FROM "session";
3074 DELETE FROM "invite_code";
3075 DELETE FROM "contact";
3076 DELETE FROM "setting";
3077 DELETE FROM "member_image";
3078 DELETE FROM "direct_voter" USING "issue"
3079 WHERE "direct_voter"."issue_id" = "issue"."id"
3080 AND "issue"."closed" ISNULL;
3081 RETURN;
3082 END;
3083 $$;
3085 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.';
3089 COMMIT;