liquid_feedback_core
view core.sql @ 13:fd9295e23be4
Version beta14
Function delete_private_data() deletes now all member contacts, including private ones, to protect users privacy when database dumps are published
New table member_history logging changes of names and logins
Function delete_private_data() deletes now all member contacts, including private ones, to protect users privacy when database dumps are published
New table member_history logging changes of names and logins
| author | jbe |
|---|---|
| date | Mon Jan 04 12:00:00 2010 +0100 (2010-01-04) |
| parents | a67c1cd4facf |
| children | ac7836ac00d9 |
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 ('beta14', 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 frontend specific member settings';
155 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
158 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
160 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
163 CREATE TABLE "member_image" (
164 PRIMARY KEY ("member_id", "image_type", "scaled"),
165 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
166 "image_type" "member_image_type",
167 "scaled" BOOLEAN,
168 "content_type" TEXT,
169 "data" BYTEA NOT NULL );
171 COMMENT ON TABLE "member_image" IS 'Images of members';
173 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
176 CREATE TABLE "member_count" (
177 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
178 "total_count" INT4 NOT NULL );
180 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';
182 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
183 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
186 CREATE TABLE "contact" (
187 PRIMARY KEY ("member_id", "other_member_id"),
188 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
189 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
190 "public" BOOLEAN NOT NULL DEFAULT FALSE,
191 CONSTRAINT "cant_save_yourself_as_contact"
192 CHECK ("member_id" != "other_member_id") );
194 COMMENT ON TABLE "contact" IS 'Contact lists';
196 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
197 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
198 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
201 CREATE TABLE "session" (
202 "ident" TEXT PRIMARY KEY,
203 "additional_secret" TEXT,
204 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
205 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
206 "lang" TEXT );
207 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
209 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';
211 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
212 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
213 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
214 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
217 CREATE TABLE "policy" (
218 "id" SERIAL4 PRIMARY KEY,
219 "index" INT4 NOT NULL,
220 "active" BOOLEAN NOT NULL DEFAULT TRUE,
221 "name" TEXT NOT NULL UNIQUE,
222 "description" TEXT NOT NULL DEFAULT '',
223 "admission_time" INTERVAL NOT NULL,
224 "discussion_time" INTERVAL NOT NULL,
225 "verification_time" INTERVAL NOT NULL,
226 "voting_time" INTERVAL NOT NULL,
227 "issue_quorum_num" INT4 NOT NULL,
228 "issue_quorum_den" INT4 NOT NULL,
229 "initiative_quorum_num" INT4 NOT NULL,
230 "initiative_quorum_den" INT4 NOT NULL,
231 "majority_num" INT4 NOT NULL DEFAULT 1,
232 "majority_den" INT4 NOT NULL DEFAULT 2,
233 "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE );
234 CREATE INDEX "policy_active_idx" ON "policy" ("active");
236 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
238 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
239 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
240 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"';
241 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"';
242 COMMENT ON COLUMN "policy"."verification_time" IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
243 COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "fully_frozen" but not "closed"';
244 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"';
245 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"';
246 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
247 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
248 COMMENT ON COLUMN "policy"."majority_num" IS 'Numerator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
249 COMMENT ON COLUMN "policy"."majority_den" IS 'Denominator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
250 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.';
253 CREATE TABLE "area" (
254 "id" SERIAL4 PRIMARY KEY,
255 "active" BOOLEAN NOT NULL DEFAULT TRUE,
256 "name" TEXT NOT NULL,
257 "description" TEXT NOT NULL DEFAULT '',
258 "direct_member_count" INT4,
259 "member_weight" INT4,
260 "autoreject_weight" INT4,
261 "text_search_data" TSVECTOR );
262 CREATE INDEX "area_active_idx" ON "area" ("active");
263 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
264 CREATE TRIGGER "update_text_search_data"
265 BEFORE INSERT OR UPDATE ON "area"
266 FOR EACH ROW EXECUTE PROCEDURE
267 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
268 "name", "description" );
270 COMMENT ON TABLE "area" IS 'Subject areas';
272 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
273 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"';
274 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
275 COMMENT ON COLUMN "area"."autoreject_weight" IS 'Sum of weight of members using the autoreject feature';
278 CREATE TABLE "allowed_policy" (
279 PRIMARY KEY ("area_id", "policy_id"),
280 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
281 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
282 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
283 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
285 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
287 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
290 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'start_of_voting');
292 COMMENT ON TYPE "snapshot_event" IS 'Reason for snapshots: ''periodic'' = due to periodic recalculation, ''end_of_admission'' = saved state at end of admission period, ''start_of_voting'' = saved state at end of verification period';
295 CREATE TABLE "issue" (
296 "id" SERIAL4 PRIMARY KEY,
297 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
298 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
299 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
300 "accepted" TIMESTAMPTZ,
301 "half_frozen" TIMESTAMPTZ,
302 "fully_frozen" TIMESTAMPTZ,
303 "closed" TIMESTAMPTZ,
304 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
305 "snapshot" TIMESTAMPTZ,
306 "latest_snapshot_event" "snapshot_event",
307 "population" INT4,
308 "vote_now" INT4,
309 "vote_later" INT4,
310 "voter_count" INT4,
311 CONSTRAINT "valid_state" CHECK (
312 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
313 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
314 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
315 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
316 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
317 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
318 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE) ),
319 CONSTRAINT "state_change_order" CHECK (
320 "created" <= "accepted" AND
321 "accepted" <= "half_frozen" AND
322 "half_frozen" <= "fully_frozen" AND
323 "fully_frozen" <= "closed" ),
324 CONSTRAINT "last_snapshot_on_full_freeze"
325 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
326 CONSTRAINT "freeze_requires_snapshot"
327 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
328 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
329 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
330 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
331 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
332 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
334 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
336 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
337 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.';
338 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.';
339 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.';
340 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
341 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';
342 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';
343 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
344 COMMENT ON COLUMN "issue"."vote_now" IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"';
345 COMMENT ON COLUMN "issue"."vote_later" IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"';
346 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';
349 CREATE TABLE "initiative" (
350 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
351 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
352 "id" SERIAL4 PRIMARY KEY,
353 "name" TEXT NOT NULL,
354 "discussion_url" TEXT,
355 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
356 "revoked" TIMESTAMPTZ,
357 "admitted" BOOLEAN,
358 "supporter_count" INT4,
359 "informed_supporter_count" INT4,
360 "satisfied_supporter_count" INT4,
361 "satisfied_informed_supporter_count" INT4,
362 "positive_votes" INT4,
363 "negative_votes" INT4,
364 "agreed" BOOLEAN,
365 "rank" INT4,
366 "text_search_data" TSVECTOR,
367 CONSTRAINT "revoked_initiatives_cant_be_admitted"
368 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
369 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"
370 CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "agreed" ISNULL)),
371 CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"
372 CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL),
373 CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"
374 CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) );
375 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
376 CREATE TRIGGER "update_text_search_data"
377 BEFORE INSERT OR UPDATE ON "initiative"
378 FOR EACH ROW EXECUTE PROCEDURE
379 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
380 "name", "discussion_url");
382 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.';
384 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
385 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
386 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
387 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
388 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
389 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
390 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
391 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
392 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
393 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"';
394 COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';
397 CREATE TABLE "draft" (
398 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
399 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
400 "id" SERIAL8 PRIMARY KEY,
401 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
402 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
403 "formatting_engine" TEXT,
404 "content" TEXT NOT NULL,
405 "text_search_data" TSVECTOR );
406 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
407 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
408 CREATE TRIGGER "update_text_search_data"
409 BEFORE INSERT OR UPDATE ON "draft"
410 FOR EACH ROW EXECUTE PROCEDURE
411 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
413 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.';
415 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
416 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
419 CREATE TABLE "suggestion" (
420 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
421 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
422 "id" SERIAL8 PRIMARY KEY,
423 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
424 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
425 "name" TEXT NOT NULL,
426 "description" TEXT NOT NULL DEFAULT '',
427 "text_search_data" TSVECTOR,
428 "minus2_unfulfilled_count" INT4,
429 "minus2_fulfilled_count" INT4,
430 "minus1_unfulfilled_count" INT4,
431 "minus1_fulfilled_count" INT4,
432 "plus1_unfulfilled_count" INT4,
433 "plus1_fulfilled_count" INT4,
434 "plus2_unfulfilled_count" INT4,
435 "plus2_fulfilled_count" INT4 );
436 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
437 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
438 CREATE TRIGGER "update_text_search_data"
439 BEFORE INSERT OR UPDATE ON "suggestion"
440 FOR EACH ROW EXECUTE PROCEDURE
441 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
442 "name", "description");
444 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';
446 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
447 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
448 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
449 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
450 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
451 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
452 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
453 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
456 CREATE TABLE "membership" (
457 PRIMARY KEY ("area_id", "member_id"),
458 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
459 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
460 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
461 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
463 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
465 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';
468 CREATE TABLE "interest" (
469 PRIMARY KEY ("issue_id", "member_id"),
470 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
471 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
472 "autoreject" BOOLEAN NOT NULL,
473 "voting_requested" BOOLEAN );
474 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
476 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.';
478 COMMENT ON COLUMN "interest"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure';
479 COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply';
482 CREATE TABLE "initiator" (
483 PRIMARY KEY ("initiative_id", "member_id"),
484 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
485 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
486 "accepted" BOOLEAN NOT NULL DEFAULT TRUE );
487 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
489 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.';
491 COMMENT ON COLUMN "initiator"."accepted" IS 'If "accepted" = FALSE, then the member was invited to be a co-initiator, but has not answered yet.';
494 CREATE TABLE "supporter" (
495 "issue_id" INT4 NOT NULL,
496 PRIMARY KEY ("initiative_id", "member_id"),
497 "initiative_id" INT4,
498 "member_id" INT4,
499 "draft_id" INT8 NOT NULL,
500 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
501 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
502 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
504 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.';
506 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
509 CREATE TABLE "opinion" (
510 "initiative_id" INT4 NOT NULL,
511 PRIMARY KEY ("suggestion_id", "member_id"),
512 "suggestion_id" INT8,
513 "member_id" INT4,
514 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
515 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
516 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE RESTRICT ON UPDATE CASCADE,
517 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
518 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
520 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.';
522 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
525 CREATE TYPE "delegation_scope" AS ENUM ('global', 'area', 'issue');
527 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''global'', ''area'', or ''issue'' (order is relevant)';
530 CREATE TABLE "delegation" (
531 "id" SERIAL8 PRIMARY KEY,
532 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
533 "trustee_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
534 "scope" "delegation_scope" NOT NULL,
535 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
536 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
537 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
538 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
539 ("scope" = 'global' AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
540 ("scope" = 'area' AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
541 ("scope" = 'issue' AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
542 UNIQUE ("area_id", "truster_id", "trustee_id"),
543 UNIQUE ("issue_id", "truster_id", "trustee_id") );
544 CREATE UNIQUE INDEX "delegation_global_truster_id_trustee_id_unique_idx"
545 ON "delegation" ("truster_id", "trustee_id") WHERE "scope" = 'global';
546 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
547 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
549 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
551 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
552 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
555 CREATE TABLE "direct_population_snapshot" (
556 PRIMARY KEY ("issue_id", "event", "member_id"),
557 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
558 "event" "snapshot_event",
559 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
560 "weight" INT4,
561 "interest_exists" BOOLEAN NOT NULL );
562 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
564 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
566 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
567 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
568 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';
571 CREATE TABLE "delegating_population_snapshot" (
572 PRIMARY KEY ("issue_id", "event", "member_id"),
573 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
574 "event" "snapshot_event",
575 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
576 "weight" INT4,
577 "scope" "delegation_scope" NOT NULL,
578 "delegate_member_ids" INT4[] NOT NULL );
579 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
581 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
583 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
584 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
585 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
586 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"';
589 CREATE TABLE "direct_interest_snapshot" (
590 PRIMARY KEY ("issue_id", "event", "member_id"),
591 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
592 "event" "snapshot_event",
593 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
594 "weight" INT4,
595 "voting_requested" BOOLEAN );
596 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
598 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
600 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
601 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
602 COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';
605 CREATE TABLE "delegating_interest_snapshot" (
606 PRIMARY KEY ("issue_id", "event", "member_id"),
607 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
608 "event" "snapshot_event",
609 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
610 "weight" INT4,
611 "scope" "delegation_scope" NOT NULL,
612 "delegate_member_ids" INT4[] NOT NULL );
613 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
615 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
617 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
618 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
619 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
620 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"';
623 CREATE TABLE "direct_supporter_snapshot" (
624 "issue_id" INT4 NOT NULL,
625 PRIMARY KEY ("initiative_id", "event", "member_id"),
626 "initiative_id" INT4,
627 "event" "snapshot_event",
628 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
629 "informed" BOOLEAN NOT NULL,
630 "satisfied" BOOLEAN NOT NULL,
631 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
632 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
633 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
635 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
637 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
638 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
639 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
642 CREATE TABLE "direct_voter" (
643 PRIMARY KEY ("issue_id", "member_id"),
644 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
645 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
646 "weight" INT4,
647 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
648 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
650 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.';
652 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
653 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
656 CREATE TABLE "delegating_voter" (
657 PRIMARY KEY ("issue_id", "member_id"),
658 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
659 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
660 "weight" INT4,
661 "scope" "delegation_scope" NOT NULL,
662 "delegate_member_ids" INT4[] NOT NULL );
663 CREATE INDEX "delegating_voter_member_id_idx" ON "direct_voter" ("member_id");
665 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
667 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
668 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
669 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"';
672 CREATE TABLE "vote" (
673 "issue_id" INT4 NOT NULL,
674 PRIMARY KEY ("initiative_id", "member_id"),
675 "initiative_id" INT4,
676 "member_id" INT4,
677 "grade" INT4,
678 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
679 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
680 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
682 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.';
684 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.';
687 CREATE TABLE "contingent" (
688 "time_frame" INTERVAL PRIMARY KEY,
689 "text_entry_limit" INT4,
690 "initiative_limit" INT4 );
692 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.';
694 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';
695 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
699 --------------------------------
700 -- Writing of history entries --
701 --------------------------------
703 CREATE FUNCTION "write_member_history_trigger"()
704 RETURNS TRIGGER
705 LANGUAGE 'plpgsql' VOLATILE AS $$
706 BEGIN
707 IF NEW."login" != OLD."login" OR NEW."name" != OLD."name" THEN
708 INSERT INTO "member_history" ("member_id", "login", "name")
709 VALUES (NEW."id", OLD."login", OLD."name");
710 END IF;
711 RETURN NULL;
712 END;
713 $$;
715 CREATE TRIGGER "write_member_history"
716 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
717 "write_member_history_trigger"();
719 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
720 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';
724 ----------------------------
725 -- Additional constraints --
726 ----------------------------
729 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
730 RETURNS TRIGGER
731 LANGUAGE 'plpgsql' VOLATILE AS $$
732 BEGIN
733 IF NOT EXISTS (
734 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
735 ) THEN
736 --RAISE 'Cannot create issue without an initial initiative.' USING
737 -- ERRCODE = 'integrity_constraint_violation',
738 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
739 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
740 END IF;
741 RETURN NULL;
742 END;
743 $$;
745 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
746 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
747 FOR EACH ROW EXECUTE PROCEDURE
748 "issue_requires_first_initiative_trigger"();
750 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
751 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
754 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
755 RETURNS TRIGGER
756 LANGUAGE 'plpgsql' VOLATILE AS $$
757 DECLARE
758 "reference_lost" BOOLEAN;
759 BEGIN
760 IF TG_OP = 'DELETE' THEN
761 "reference_lost" := TRUE;
762 ELSE
763 "reference_lost" := NEW."issue_id" != OLD."issue_id";
764 END IF;
765 IF
766 "reference_lost" AND NOT EXISTS (
767 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
768 )
769 THEN
770 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
771 END IF;
772 RETURN NULL;
773 END;
774 $$;
776 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
777 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
778 FOR EACH ROW EXECUTE PROCEDURE
779 "last_initiative_deletes_issue_trigger"();
781 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
782 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
785 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
786 RETURNS TRIGGER
787 LANGUAGE 'plpgsql' VOLATILE AS $$
788 BEGIN
789 IF NOT EXISTS (
790 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
791 ) THEN
792 --RAISE 'Cannot create initiative without an initial draft.' USING
793 -- ERRCODE = 'integrity_constraint_violation',
794 -- HINT = 'Create issue, initiative and draft within the same transaction.';
795 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
796 END IF;
797 RETURN NULL;
798 END;
799 $$;
801 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
802 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
803 FOR EACH ROW EXECUTE PROCEDURE
804 "initiative_requires_first_draft_trigger"();
806 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
807 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
810 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
811 RETURNS TRIGGER
812 LANGUAGE 'plpgsql' VOLATILE AS $$
813 DECLARE
814 "reference_lost" BOOLEAN;
815 BEGIN
816 IF TG_OP = 'DELETE' THEN
817 "reference_lost" := TRUE;
818 ELSE
819 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
820 END IF;
821 IF
822 "reference_lost" AND NOT EXISTS (
823 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
824 )
825 THEN
826 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
827 END IF;
828 RETURN NULL;
829 END;
830 $$;
832 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
833 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
834 FOR EACH ROW EXECUTE PROCEDURE
835 "last_draft_deletes_initiative_trigger"();
837 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
838 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
841 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
842 RETURNS TRIGGER
843 LANGUAGE 'plpgsql' VOLATILE AS $$
844 BEGIN
845 IF NOT EXISTS (
846 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
847 ) THEN
848 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
849 END IF;
850 RETURN NULL;
851 END;
852 $$;
854 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
855 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
856 FOR EACH ROW EXECUTE PROCEDURE
857 "suggestion_requires_first_opinion_trigger"();
859 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
860 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
863 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
864 RETURNS TRIGGER
865 LANGUAGE 'plpgsql' VOLATILE AS $$
866 DECLARE
867 "reference_lost" BOOLEAN;
868 BEGIN
869 IF TG_OP = 'DELETE' THEN
870 "reference_lost" := TRUE;
871 ELSE
872 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
873 END IF;
874 IF
875 "reference_lost" AND NOT EXISTS (
876 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
877 )
878 THEN
879 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
880 END IF;
881 RETURN NULL;
882 END;
883 $$;
885 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
886 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
887 FOR EACH ROW EXECUTE PROCEDURE
888 "last_opinion_deletes_suggestion_trigger"();
890 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
891 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
895 --------------------------------------------------------------------
896 -- Auto-retrieval of fields only needed for referential integrity --
897 --------------------------------------------------------------------
899 CREATE FUNCTION "autofill_issue_id_trigger"()
900 RETURNS TRIGGER
901 LANGUAGE 'plpgsql' VOLATILE AS $$
902 BEGIN
903 IF NEW."issue_id" ISNULL THEN
904 SELECT "issue_id" INTO NEW."issue_id"
905 FROM "initiative" WHERE "id" = NEW."initiative_id";
906 END IF;
907 RETURN NEW;
908 END;
909 $$;
911 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
912 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
914 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
915 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
917 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
918 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
919 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
922 CREATE FUNCTION "autofill_initiative_id_trigger"()
923 RETURNS TRIGGER
924 LANGUAGE 'plpgsql' VOLATILE AS $$
925 BEGIN
926 IF NEW."initiative_id" ISNULL THEN
927 SELECT "initiative_id" INTO NEW."initiative_id"
928 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
929 END IF;
930 RETURN NEW;
931 END;
932 $$;
934 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
935 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
937 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
938 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
942 -----------------------------------------------------
943 -- Automatic calculation of certain default values --
944 -----------------------------------------------------
946 CREATE FUNCTION "copy_autoreject_trigger"()
947 RETURNS TRIGGER
948 LANGUAGE 'plpgsql' VOLATILE AS $$
949 BEGIN
950 IF NEW."autoreject" ISNULL THEN
951 SELECT "membership"."autoreject" INTO NEW."autoreject"
952 FROM "issue" JOIN "membership"
953 ON "issue"."area_id" = "membership"."area_id"
954 WHERE "issue"."id" = NEW."issue_id"
955 AND "membership"."member_id" = NEW."member_id";
956 END IF;
957 IF NEW."autoreject" ISNULL THEN
958 NEW."autoreject" := FALSE;
959 END IF;
960 RETURN NEW;
961 END;
962 $$;
964 CREATE TRIGGER "copy_autoreject" BEFORE INSERT OR UPDATE ON "interest"
965 FOR EACH ROW EXECUTE PROCEDURE "copy_autoreject_trigger"();
967 COMMENT ON FUNCTION "copy_autoreject_trigger"() IS 'Implementation of trigger "copy_autoreject" on table "interest"';
968 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';
971 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
972 RETURNS TRIGGER
973 LANGUAGE 'plpgsql' VOLATILE AS $$
974 BEGIN
975 IF NEW."draft_id" ISNULL THEN
976 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
977 WHERE "initiative_id" = NEW."initiative_id";
978 END IF;
979 RETURN NEW;
980 END;
981 $$;
983 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
984 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
986 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
987 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';
991 ----------------------------------------
992 -- Automatic creation of dependencies --
993 ----------------------------------------
995 CREATE FUNCTION "autocreate_interest_trigger"()
996 RETURNS TRIGGER
997 LANGUAGE 'plpgsql' VOLATILE AS $$
998 BEGIN
999 IF NOT EXISTS (
1000 SELECT NULL FROM "initiative" JOIN "interest"
1001 ON "initiative"."issue_id" = "interest"."issue_id"
1002 WHERE "initiative"."id" = NEW."initiative_id"
1003 AND "interest"."member_id" = NEW."member_id"
1004 ) THEN
1005 BEGIN
1006 INSERT INTO "interest" ("issue_id", "member_id")
1007 SELECT "issue_id", NEW."member_id"
1008 FROM "initiative" WHERE "id" = NEW."initiative_id";
1009 EXCEPTION WHEN unique_violation THEN END;
1010 END IF;
1011 RETURN NEW;
1012 END;
1013 $$;
1015 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1016 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1018 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1019 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';
1022 CREATE FUNCTION "autocreate_supporter_trigger"()
1023 RETURNS TRIGGER
1024 LANGUAGE 'plpgsql' VOLATILE AS $$
1025 BEGIN
1026 IF NOT EXISTS (
1027 SELECT NULL FROM "suggestion" JOIN "supporter"
1028 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1029 WHERE "suggestion"."id" = NEW."suggestion_id"
1030 AND "supporter"."member_id" = NEW."member_id"
1031 ) THEN
1032 BEGIN
1033 INSERT INTO "supporter" ("initiative_id", "member_id")
1034 SELECT "initiative_id", NEW."member_id"
1035 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1036 EXCEPTION WHEN unique_violation THEN END;
1037 END IF;
1038 RETURN NEW;
1039 END;
1040 $$;
1042 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1043 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1045 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1046 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.';
1050 ------------------------------------------
1051 -- Views and helper functions for views --
1052 ------------------------------------------
1055 CREATE VIEW "global_delegation" AS
1056 SELECT
1057 "delegation"."id",
1058 "delegation"."truster_id",
1059 "delegation"."trustee_id"
1060 FROM "delegation" JOIN "member"
1061 ON "delegation"."trustee_id" = "member"."id"
1062 WHERE "delegation"."scope" = 'global' AND "member"."active";
1064 COMMENT ON VIEW "global_delegation" IS 'Global delegations to active members';
1067 CREATE VIEW "area_delegation" AS
1068 SELECT "subquery".* FROM (
1069 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1070 "area"."id" AS "area_id",
1071 "delegation"."id",
1072 "delegation"."truster_id",
1073 "delegation"."trustee_id",
1074 "delegation"."scope"
1075 FROM "area" JOIN "delegation"
1076 ON "delegation"."scope" = 'global'
1077 OR "delegation"."area_id" = "area"."id"
1078 ORDER BY
1079 "area"."id",
1080 "delegation"."truster_id",
1081 "delegation"."scope" DESC
1082 ) AS "subquery"
1083 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
1084 WHERE "member"."active";
1086 COMMENT ON VIEW "area_delegation" IS 'Active delegations for areas';
1089 CREATE VIEW "issue_delegation" AS
1090 SELECT "subquery".* FROM (
1091 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1092 "issue"."id" AS "issue_id",
1093 "delegation"."id",
1094 "delegation"."truster_id",
1095 "delegation"."trustee_id",
1096 "delegation"."scope"
1097 FROM "issue" JOIN "delegation"
1098 ON "delegation"."scope" = 'global'
1099 OR "delegation"."area_id" = "issue"."area_id"
1100 OR "delegation"."issue_id" = "issue"."id"
1101 ORDER BY
1102 "issue"."id",
1103 "delegation"."truster_id",
1104 "delegation"."scope" DESC
1105 ) AS "subquery"
1106 JOIN "member" ON "subquery"."trustee_id" = "member"."id"
1107 WHERE "member"."active";
1109 COMMENT ON VIEW "issue_delegation" IS 'Active delegations for issues';
1112 CREATE FUNCTION "membership_weight_with_skipping"
1113 ( "area_id_p" "area"."id"%TYPE,
1114 "member_id_p" "member"."id"%TYPE,
1115 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1116 RETURNS INT4
1117 LANGUAGE 'plpgsql' STABLE AS $$
1118 DECLARE
1119 "sum_v" INT4;
1120 "delegation_row" "area_delegation"%ROWTYPE;
1121 BEGIN
1122 "sum_v" := 1;
1123 FOR "delegation_row" IN
1124 SELECT "area_delegation".*
1125 FROM "area_delegation" LEFT JOIN "membership"
1126 ON "membership"."area_id" = "area_id_p"
1127 AND "membership"."member_id" = "area_delegation"."truster_id"
1128 WHERE "area_delegation"."area_id" = "area_id_p"
1129 AND "area_delegation"."trustee_id" = "member_id_p"
1130 AND "membership"."member_id" ISNULL
1131 LOOP
1132 IF NOT
1133 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1134 THEN
1135 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1136 "area_id_p",
1137 "delegation_row"."truster_id",
1138 "skip_member_ids_p" || "delegation_row"."truster_id"
1139 );
1140 END IF;
1141 END LOOP;
1142 RETURN "sum_v";
1143 END;
1144 $$;
1146 COMMENT ON FUNCTION "membership_weight_with_skipping"
1147 ( "area"."id"%TYPE,
1148 "member"."id"%TYPE,
1149 INT4[] )
1150 IS 'Helper function for "membership_weight" function';
1153 CREATE FUNCTION "membership_weight"
1154 ( "area_id_p" "area"."id"%TYPE,
1155 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1156 RETURNS INT4
1157 LANGUAGE 'plpgsql' STABLE AS $$
1158 BEGIN
1159 RETURN "membership_weight_with_skipping"(
1160 "area_id_p",
1161 "member_id_p",
1162 ARRAY["member_id_p"]
1163 );
1164 END;
1165 $$;
1167 COMMENT ON FUNCTION "membership_weight"
1168 ( "area"."id"%TYPE,
1169 "member"."id"%TYPE )
1170 IS 'Calculates the potential voting weight of a member in a given area';
1173 CREATE VIEW "member_count_view" AS
1174 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1176 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1179 CREATE VIEW "area_member_count" AS
1180 SELECT
1181 "area"."id" AS "area_id",
1182 count("member"."id") AS "direct_member_count",
1183 coalesce(
1184 sum(
1185 CASE WHEN "member"."id" NOTNULL THEN
1186 "membership_weight"("area"."id", "member"."id")
1187 ELSE 0 END
1188 )
1189 ) AS "member_weight",
1190 coalesce(
1191 sum(
1192 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
1193 "membership_weight"("area"."id", "member"."id")
1194 ELSE 0 END
1195 )
1196 ) AS "autoreject_weight"
1197 FROM "area"
1198 LEFT JOIN "membership"
1199 ON "area"."id" = "membership"."area_id"
1200 LEFT JOIN "member"
1201 ON "membership"."member_id" = "member"."id"
1202 AND "member"."active"
1203 GROUP BY "area"."id";
1205 COMMENT ON VIEW "area_member_count" IS 'View used to update "member_count" column of table "area"';
1208 CREATE VIEW "opening_draft" AS
1209 SELECT "draft".* FROM (
1210 SELECT
1211 "initiative"."id" AS "initiative_id",
1212 min("draft"."id") AS "draft_id"
1213 FROM "initiative" JOIN "draft"
1214 ON "initiative"."id" = "draft"."initiative_id"
1215 GROUP BY "initiative"."id"
1216 ) AS "subquery"
1217 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1219 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1222 CREATE VIEW "current_draft" AS
1223 SELECT "draft".* FROM (
1224 SELECT
1225 "initiative"."id" AS "initiative_id",
1226 max("draft"."id") AS "draft_id"
1227 FROM "initiative" JOIN "draft"
1228 ON "initiative"."id" = "draft"."initiative_id"
1229 GROUP BY "initiative"."id"
1230 ) AS "subquery"
1231 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1233 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1236 CREATE VIEW "critical_opinion" AS
1237 SELECT * FROM "opinion"
1238 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
1239 OR ("degree" = -2 AND "fulfilled" = TRUE);
1241 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
1244 CREATE VIEW "battle" AS
1245 SELECT
1246 "issue"."id" AS "issue_id",
1247 "winning_initiative"."id" AS "winning_initiative_id",
1248 "losing_initiative"."id" AS "losing_initiative_id",
1249 sum(
1250 CASE WHEN
1251 coalesce("better_vote"."grade", 0) >
1252 coalesce("worse_vote"."grade", 0)
1253 THEN "direct_voter"."weight" ELSE 0 END
1254 ) AS "count"
1255 FROM "issue"
1256 LEFT JOIN "direct_voter"
1257 ON "issue"."id" = "direct_voter"."issue_id"
1258 JOIN "initiative" AS "winning_initiative"
1259 ON "issue"."id" = "winning_initiative"."issue_id"
1260 AND "winning_initiative"."agreed"
1261 JOIN "initiative" AS "losing_initiative"
1262 ON "issue"."id" = "losing_initiative"."issue_id"
1263 AND "losing_initiative"."agreed"
1264 LEFT JOIN "vote" AS "better_vote"
1265 ON "direct_voter"."member_id" = "better_vote"."member_id"
1266 AND "winning_initiative"."id" = "better_vote"."initiative_id"
1267 LEFT JOIN "vote" AS "worse_vote"
1268 ON "direct_voter"."member_id" = "worse_vote"."member_id"
1269 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
1270 WHERE
1271 "winning_initiative"."id" != "losing_initiative"."id"
1272 GROUP BY
1273 "issue"."id",
1274 "winning_initiative"."id",
1275 "losing_initiative"."id";
1277 COMMENT ON VIEW "battle" IS 'Number of members preferring one initiative over another';
1280 CREATE VIEW "expired_session" AS
1281 SELECT * FROM "session" WHERE now() > "expiry";
1283 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
1284 DELETE FROM "session" WHERE "ident" = OLD."ident";
1286 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
1287 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
1290 CREATE VIEW "open_issue" AS
1291 SELECT * FROM "issue" WHERE "closed" ISNULL;
1293 COMMENT ON VIEW "open_issue" IS 'All open issues';
1296 CREATE VIEW "issue_with_ranks_missing" AS
1297 SELECT * FROM "issue"
1298 WHERE "fully_frozen" NOTNULL
1299 AND "closed" NOTNULL
1300 AND "ranks_available" = FALSE;
1302 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
1305 CREATE VIEW "member_contingent" AS
1306 SELECT
1307 "member"."id" AS "member_id",
1308 "contingent"."time_frame",
1309 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
1310 (
1311 SELECT count(1) FROM "draft"
1312 WHERE "draft"."author_id" = "member"."id"
1313 AND "draft"."created" > now() - "contingent"."time_frame"
1314 ) + (
1315 SELECT count(1) FROM "suggestion"
1316 WHERE "suggestion"."author_id" = "member"."id"
1317 AND "suggestion"."created" > now() - "contingent"."time_frame"
1318 )
1319 ELSE NULL END AS "text_entry_count",
1320 "contingent"."text_entry_limit",
1321 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
1322 SELECT count(1) FROM "opening_draft"
1323 WHERE "opening_draft"."author_id" = "member"."id"
1324 AND "opening_draft"."created" > now() - "contingent"."time_frame"
1325 ) ELSE NULL END AS "initiative_count",
1326 "contingent"."initiative_limit"
1327 FROM "member" CROSS JOIN "contingent";
1329 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
1331 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
1332 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
1335 CREATE VIEW "member_contingent_left" AS
1336 SELECT
1337 "member_id",
1338 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
1339 max("initiative_limit" - "initiative_count") AS "initiatives_left"
1340 FROM "member_contingent" GROUP BY "member_id";
1342 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.';
1346 --------------------------------------------------
1347 -- Set returning function for delegation chains --
1348 --------------------------------------------------
1351 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
1352 ('first', 'intermediate', 'last', 'repetition');
1354 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
1357 CREATE TYPE "delegation_chain_row" AS (
1358 "index" INT4,
1359 "member_id" INT4,
1360 "member_active" BOOLEAN,
1361 "participation" BOOLEAN,
1362 "overridden" BOOLEAN,
1363 "scope_in" "delegation_scope",
1364 "scope_out" "delegation_scope",
1365 "loop" "delegation_chain_loop_tag" );
1367 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
1369 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
1370 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';
1371 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
1372 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
1373 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
1374 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
1377 CREATE FUNCTION "delegation_chain"
1378 ( "member_id_p" "member"."id"%TYPE,
1379 "area_id_p" "area"."id"%TYPE,
1380 "issue_id_p" "issue"."id"%TYPE,
1381 "simulate_trustee_id_p" "member"."id"%TYPE )
1382 RETURNS SETOF "delegation_chain_row"
1383 LANGUAGE 'plpgsql' STABLE AS $$
1384 DECLARE
1385 "issue_row" "issue"%ROWTYPE;
1386 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
1387 "loop_member_id_v" "member"."id"%TYPE;
1388 "output_row" "delegation_chain_row";
1389 "output_rows" "delegation_chain_row"[];
1390 "delegation_row" "delegation"%ROWTYPE;
1391 "row_count" INT4;
1392 "i" INT4;
1393 "loop_v" BOOLEAN;
1394 BEGIN
1395 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
1396 "visited_member_ids" := '{}';
1397 "loop_member_id_v" := NULL;
1398 "output_rows" := '{}';
1399 "output_row"."index" := 0;
1400 "output_row"."member_id" := "member_id_p";
1401 "output_row"."member_active" := TRUE;
1402 "output_row"."participation" := FALSE;
1403 "output_row"."overridden" := FALSE;
1404 "output_row"."scope_out" := NULL;
1405 LOOP
1406 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
1407 "loop_member_id_v" := "output_row"."member_id";
1408 ELSE
1409 "visited_member_ids" :=
1410 "visited_member_ids" || "output_row"."member_id";
1411 END IF;
1412 IF "output_row"."participation" THEN
1413 "output_row"."overridden" := TRUE;
1414 END IF;
1415 "output_row"."scope_in" := "output_row"."scope_out";
1416 IF EXISTS (
1417 SELECT NULL FROM "member"
1418 WHERE "id" = "output_row"."member_id" AND "active"
1419 ) THEN
1420 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
1421 SELECT * INTO "delegation_row" FROM "delegation"
1422 WHERE "truster_id" = "output_row"."member_id"
1423 AND "scope" = 'global';
1424 ELSIF "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN
1425 "output_row"."participation" := EXISTS (
1426 SELECT NULL FROM "membership"
1427 WHERE "area_id" = "area_id_p"
1428 AND "member_id" = "output_row"."member_id"
1429 );
1430 SELECT * INTO "delegation_row" FROM "delegation"
1431 WHERE "truster_id" = "output_row"."member_id"
1432 AND ("scope" = 'global' OR "area_id" = "area_id_p")
1433 ORDER BY "scope" DESC;
1434 ELSIF "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN
1435 "output_row"."participation" := EXISTS (
1436 SELECT NULL FROM "interest"
1437 WHERE "issue_id" = "issue_id_p"
1438 AND "member_id" = "output_row"."member_id"
1439 );
1440 SELECT * INTO "delegation_row" FROM "delegation"
1441 WHERE "truster_id" = "output_row"."member_id"
1442 AND ("scope" = 'global' OR
1443 "area_id" = "issue_row"."area_id" OR
1444 "issue_id" = "issue_id_p"
1445 )
1446 ORDER BY "scope" DESC;
1447 ELSE
1448 RAISE EXCEPTION 'Either area_id or issue_id or both must be NULL.';
1449 END IF;
1450 ELSE
1451 "output_row"."member_active" := FALSE;
1452 "output_row"."participation" := FALSE;
1453 "output_row"."scope_out" := NULL;
1454 "delegation_row" := ROW(NULL);
1455 END IF;
1456 IF
1457 "output_row"."member_id" = "member_id_p" AND
1458 "simulate_trustee_id_p" NOTNULL
1459 THEN
1460 "output_row"."scope_out" := CASE
1461 WHEN "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN 'global'
1462 WHEN "area_id_p" NOTNULL AND "issue_id_p" ISNULL THEN 'area'
1463 WHEN "area_id_p" ISNULL AND "issue_id_p" NOTNULL THEN 'issue'
1464 END;
1465 "output_rows" := "output_rows" || "output_row";
1466 "output_row"."member_id" := "simulate_trustee_id_p";
1467 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
1468 "output_row"."scope_out" := "delegation_row"."scope";
1469 "output_rows" := "output_rows" || "output_row";
1470 "output_row"."member_id" := "delegation_row"."trustee_id";
1471 ELSE
1472 "output_row"."scope_out" := NULL;
1473 "output_rows" := "output_rows" || "output_row";
1474 EXIT;
1475 END IF;
1476 EXIT WHEN "loop_member_id_v" NOTNULL;
1477 "output_row"."index" := "output_row"."index" + 1;
1478 END LOOP;
1479 "row_count" := array_upper("output_rows", 1);
1480 "i" := 1;
1481 "loop_v" := FALSE;
1482 LOOP
1483 "output_row" := "output_rows"["i"];
1484 EXIT WHEN "output_row"."member_id" ISNULL;
1485 IF "loop_v" THEN
1486 IF "i" + 1 = "row_count" THEN
1487 "output_row"."loop" := 'last';
1488 ELSIF "i" = "row_count" THEN
1489 "output_row"."loop" := 'repetition';
1490 ELSE
1491 "output_row"."loop" := 'intermediate';
1492 END IF;
1493 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
1494 "output_row"."loop" := 'first';
1495 "loop_v" := TRUE;
1496 END IF;
1497 IF "area_id_p" ISNULL AND "issue_id_p" ISNULL THEN
1498 "output_row"."participation" := NULL;
1499 END IF;
1500 RETURN NEXT "output_row";
1501 "i" := "i" + 1;
1502 END LOOP;
1503 RETURN;
1504 END;
1505 $$;
1507 COMMENT ON FUNCTION "delegation_chain"
1508 ( "member"."id"%TYPE,
1509 "area"."id"%TYPE,
1510 "issue"."id"%TYPE,
1511 "member"."id"%TYPE )
1512 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
1514 CREATE FUNCTION "delegation_chain"
1515 ( "member_id_p" "member"."id"%TYPE,
1516 "area_id_p" "area"."id"%TYPE,
1517 "issue_id_p" "issue"."id"%TYPE )
1518 RETURNS SETOF "delegation_chain_row"
1519 LANGUAGE 'plpgsql' STABLE AS $$
1520 DECLARE
1521 "result_row" "delegation_chain_row";
1522 BEGIN
1523 FOR "result_row" IN
1524 SELECT * FROM "delegation_chain"(
1525 "member_id_p", "area_id_p", "issue_id_p", NULL
1526 )
1527 LOOP
1528 RETURN NEXT "result_row";
1529 END LOOP;
1530 RETURN;
1531 END;
1532 $$;
1534 COMMENT ON FUNCTION "delegation_chain"
1535 ( "member"."id"%TYPE,
1536 "area"."id"%TYPE,
1537 "issue"."id"%TYPE )
1538 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
1542 ------------------------------
1543 -- Comparison by vote count --
1544 ------------------------------
1546 CREATE FUNCTION "vote_ratio"
1547 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
1548 "negative_votes_p" "initiative"."negative_votes"%TYPE )
1549 RETURNS FLOAT8
1550 LANGUAGE 'plpgsql' STABLE AS $$
1551 DECLARE
1552 "total_v" INT4;
1553 BEGIN
1554 "total_v" := "positive_votes_p" + "negative_votes_p";
1555 IF "total_v" > 0 THEN
1556 RETURN "positive_votes_p"::FLOAT8 / "total_v"::FLOAT8;
1557 ELSE
1558 RETURN 0.5;
1559 END IF;
1560 END;
1561 $$;
1563 COMMENT ON FUNCTION "vote_ratio"
1564 ( "initiative"."positive_votes"%TYPE,
1565 "initiative"."negative_votes"%TYPE )
1566 IS 'Ratio of positive votes to sum of positive and negative votes; 0.5, if there are neither positive nor negative votes';
1570 ------------------------------------------------
1571 -- Locking for snapshots and voting procedure --
1572 ------------------------------------------------
1574 CREATE FUNCTION "global_lock"() RETURNS VOID
1575 LANGUAGE 'plpgsql' VOLATILE AS $$
1576 BEGIN
1577 -- NOTE: PostgreSQL allows reading, while tables are locked in
1578 -- exclusive move. Transactions should be kept short anyway!
1579 LOCK TABLE "member" IN EXCLUSIVE MODE;
1580 LOCK TABLE "area" IN EXCLUSIVE MODE;
1581 LOCK TABLE "membership" IN EXCLUSIVE MODE;
1582 -- NOTE: "member", "area" and "membership" are locked first to
1583 -- prevent deadlocks in combination with "calculate_member_counts"()
1584 LOCK TABLE "policy" IN EXCLUSIVE MODE;
1585 LOCK TABLE "issue" IN EXCLUSIVE MODE;
1586 LOCK TABLE "initiative" IN EXCLUSIVE MODE;
1587 LOCK TABLE "draft" IN EXCLUSIVE MODE;
1588 LOCK TABLE "suggestion" IN EXCLUSIVE MODE;
1589 LOCK TABLE "interest" IN EXCLUSIVE MODE;
1590 LOCK TABLE "initiator" IN EXCLUSIVE MODE;
1591 LOCK TABLE "supporter" IN EXCLUSIVE MODE;
1592 LOCK TABLE "opinion" IN EXCLUSIVE MODE;
1593 LOCK TABLE "delegation" IN EXCLUSIVE MODE;
1594 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
1595 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
1596 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
1597 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
1598 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
1599 LOCK TABLE "direct_voter" IN EXCLUSIVE MODE;
1600 LOCK TABLE "delegating_voter" IN EXCLUSIVE MODE;
1601 LOCK TABLE "vote" IN EXCLUSIVE MODE;
1602 RETURN;
1603 END;
1604 $$;
1606 COMMENT ON FUNCTION "global_lock"() IS 'Locks all tables related to support/voting until end of transaction; read access is still possible though';
1610 -------------------------------
1611 -- Materialize member counts --
1612 -------------------------------
1614 CREATE FUNCTION "calculate_member_counts"()
1615 RETURNS VOID
1616 LANGUAGE 'plpgsql' VOLATILE AS $$
1617 BEGIN
1618 LOCK TABLE "member" IN EXCLUSIVE MODE;
1619 LOCK TABLE "area" IN EXCLUSIVE MODE;
1620 LOCK TABLE "membership" IN EXCLUSIVE MODE;
1621 DELETE FROM "member_count";
1622 INSERT INTO "member_count" ("total_count")
1623 SELECT "total_count" FROM "member_count_view";
1624 UPDATE "area" SET
1625 "direct_member_count" = "view"."direct_member_count",
1626 "member_weight" = "view"."member_weight",
1627 "autoreject_weight" = "view"."autoreject_weight"
1628 FROM "area_member_count" AS "view"
1629 WHERE "view"."area_id" = "area"."id";
1630 RETURN;
1631 END;
1632 $$;
1634 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"';
1638 ------------------------------
1639 -- Calculation of snapshots --
1640 ------------------------------
1642 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
1643 ( "issue_id_p" "issue"."id"%TYPE,
1644 "member_id_p" "member"."id"%TYPE,
1645 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
1646 RETURNS "direct_population_snapshot"."weight"%TYPE
1647 LANGUAGE 'plpgsql' VOLATILE AS $$
1648 DECLARE
1649 "issue_delegation_row" "issue_delegation"%ROWTYPE;
1650 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
1651 "weight_v" INT4;
1652 "sub_weight_v" INT4;
1653 BEGIN
1654 "weight_v" := 0;
1655 FOR "issue_delegation_row" IN
1656 SELECT * FROM "issue_delegation"
1657 WHERE "trustee_id" = "member_id_p"
1658 AND "issue_id" = "issue_id_p"
1659 LOOP
1660 IF NOT EXISTS (
1661 SELECT NULL FROM "direct_population_snapshot"
1662 WHERE "issue_id" = "issue_id_p"
1663 AND "event" = 'periodic'
1664 AND "member_id" = "issue_delegation_row"."truster_id"
1665 ) AND NOT EXISTS (
1666 SELECT NULL FROM "delegating_population_snapshot"
1667 WHERE "issue_id" = "issue_id_p"
1668 AND "event" = 'periodic'
1669 AND "member_id" = "issue_delegation_row"."truster_id"
1670 ) THEN
1671 "delegate_member_ids_v" :=
1672 "member_id_p" || "delegate_member_ids_p";
1673 INSERT INTO "delegating_population_snapshot" (
1674 "issue_id",
1675 "event",
1676 "member_id",
1677 "scope",
1678 "delegate_member_ids"
1679 ) VALUES (
1680 "issue_id_p",
1681 'periodic',
1682 "issue_delegation_row"."truster_id",
1683 "issue_delegation_row"."scope",
1684 "delegate_member_ids_v"
1685 );
1686 "sub_weight_v" := 1 +
1687 "weight_of_added_delegations_for_population_snapshot"(
1688 "issue_id_p",
1689 "issue_delegation_row"."truster_id",
1690 "delegate_member_ids_v"
1691 );
1692 UPDATE "delegating_population_snapshot"
1693 SET "weight" = "sub_weight_v"
1694 WHERE "issue_id" = "issue_id_p"
1695 AND "event" = 'periodic'
1696 AND "member_id" = "issue_delegation_row"."truster_id";
1697 "weight_v" := "weight_v" + "sub_weight_v";
1698 END IF;
1699 END LOOP;
1700 RETURN "weight_v";
1701 END;
1702 $$;
1704 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
1705 ( "issue"."id"%TYPE,
1706 "member"."id"%TYPE,
1707 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
1708 IS 'Helper function for "create_population_snapshot" function';
1711 CREATE FUNCTION "create_population_snapshot"
1712 ( "issue_id_p" "issue"."id"%TYPE )
1713 RETURNS VOID
1714 LANGUAGE 'plpgsql' VOLATILE AS $$
1715 DECLARE
1716 "member_id_v" "member"."id"%TYPE;
1717 BEGIN
1718 DELETE FROM "direct_population_snapshot"
1719 WHERE "issue_id" = "issue_id_p"
1720 AND "event" = 'periodic';
1721 DELETE FROM "delegating_population_snapshot"
1722 WHERE "issue_id" = "issue_id_p"
1723 AND "event" = 'periodic';
1724 INSERT INTO "direct_population_snapshot"
1725 ("issue_id", "event", "member_id", "interest_exists")
1726 SELECT DISTINCT ON ("issue_id", "member_id")
1727 "issue_id_p" AS "issue_id",
1728 'periodic' AS "event",
1729 "subquery"."member_id",
1730 "subquery"."interest_exists"
1731 FROM (
1732 SELECT
1733 "member"."id" AS "member_id",
1734 FALSE AS "interest_exists"
1735 FROM "issue"
1736 JOIN "area" ON "issue"."area_id" = "area"."id"
1737 JOIN "membership" ON "area"."id" = "membership"."area_id"
1738 JOIN "member" ON "membership"."member_id" = "member"."id"
1739 WHERE "issue"."id" = "issue_id_p"
1740 AND "member"."active"
1741 UNION
1742 SELECT
1743 "member"."id" AS "member_id",
1744 TRUE AS "interest_exists"
1745 FROM "interest" JOIN "member"
1746 ON "interest"."member_id" = "member"."id"
1747 WHERE "interest"."issue_id" = "issue_id_p"
1748 AND "member"."active"
1749 ) AS "subquery"
1750 ORDER BY
1751 "issue_id_p",
1752 "subquery"."member_id",
1753 "subquery"."interest_exists" DESC;
1754 FOR "member_id_v" IN
1755 SELECT "member_id" FROM "direct_population_snapshot"
1756 WHERE "issue_id" = "issue_id_p"
1757 AND "event" = 'periodic'
1758 LOOP
1759 UPDATE "direct_population_snapshot" SET
1760 "weight" = 1 +
1761 "weight_of_added_delegations_for_population_snapshot"(
1762 "issue_id_p",
1763 "member_id_v",
1764 '{}'
1765 )
1766 WHERE "issue_id" = "issue_id_p"
1767 AND "event" = 'periodic'
1768 AND "member_id" = "member_id_v";
1769 END LOOP;
1770 RETURN;
1771 END;
1772 $$;
1774 COMMENT ON FUNCTION "create_population_snapshot"
1775 ( "issue_id_p" "issue"."id"%TYPE )
1776 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.';
1779 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
1780 ( "issue_id_p" "issue"."id"%TYPE,
1781 "member_id_p" "member"."id"%TYPE,
1782 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
1783 RETURNS "direct_interest_snapshot"."weight"%TYPE
1784 LANGUAGE 'plpgsql' VOLATILE AS $$
1785 DECLARE
1786 "issue_delegation_row" "issue_delegation"%ROWTYPE;
1787 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
1788 "weight_v" INT4;
1789 "sub_weight_v" INT4;
1790 BEGIN
1791 "weight_v" := 0;
1792 FOR "issue_delegation_row" IN
1793 SELECT * FROM "issue_delegation"
1794 WHERE "trustee_id" = "member_id_p"
1795 AND "issue_id" = "issue_id_p"
1796 LOOP
1797 IF NOT EXISTS (
1798 SELECT NULL FROM "direct_interest_snapshot"
1799 WHERE "issue_id" = "issue_id_p"
1800 AND "event" = 'periodic'
1801 AND "member_id" = "issue_delegation_row"."truster_id"
1802 ) AND NOT EXISTS (
1803 SELECT NULL FROM "delegating_interest_snapshot"
1804 WHERE "issue_id" = "issue_id_p"
1805 AND "event" = 'periodic'
1806 AND "member_id" = "issue_delegation_row"."truster_id"
1807 ) THEN
1808 "delegate_member_ids_v" :=
1809 "member_id_p" || "delegate_member_ids_p";
1810 INSERT INTO "delegating_interest_snapshot" (
1811 "issue_id",
1812 "event",
1813 "member_id",
1814 "scope",
1815 "delegate_member_ids"
1816 ) VALUES (
1817 "issue_id_p",
1818 'periodic',
1819 "issue_delegation_row"."truster_id",
1820 "issue_delegation_row"."scope",
1821 "delegate_member_ids_v"
1822 );
1823 "sub_weight_v" := 1 +
1824 "weight_of_added_delegations_for_interest_snapshot"(
1825 "issue_id_p",
1826 "issue_delegation_row"."truster_id",
1827 "delegate_member_ids_v"
1828 );
1829 UPDATE "delegating_interest_snapshot"
1830 SET "weight" = "sub_weight_v"
1831 WHERE "issue_id" = "issue_id_p"
1832 AND "event" = 'periodic'
1833 AND "member_id" = "issue_delegation_row"."truster_id";
1834 "weight_v" := "weight_v" + "sub_weight_v";
1835 END IF;
1836 END LOOP;
1837 RETURN "weight_v";
1838 END;
1839 $$;
1841 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
1842 ( "issue"."id"%TYPE,
1843 "member"."id"%TYPE,
1844 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
1845 IS 'Helper function for "create_interest_snapshot" function';
1848 CREATE FUNCTION "create_interest_snapshot"
1849 ( "issue_id_p" "issue"."id"%TYPE )
1850 RETURNS VOID
1851 LANGUAGE 'plpgsql' VOLATILE AS $$
1852 DECLARE
1853 "member_id_v" "member"."id"%TYPE;
1854 BEGIN
1855 DELETE FROM "direct_interest_snapshot"
1856 WHERE "issue_id" = "issue_id_p"
1857 AND "event" = 'periodic';
1858 DELETE FROM "delegating_interest_snapshot"
1859 WHERE "issue_id" = "issue_id_p"
1860 AND "event" = 'periodic';
1861 DELETE FROM "direct_supporter_snapshot"
1862 WHERE "issue_id" = "issue_id_p"
1863 AND "event" = 'periodic';
1864 INSERT INTO "direct_interest_snapshot"
1865 ("issue_id", "event", "member_id", "voting_requested")
1866 SELECT
1867 "issue_id_p" AS "issue_id",
1868 'periodic' AS "event",
1869 "member"."id" AS "member_id",
1870 "interest"."voting_requested"
1871 FROM "interest" JOIN "member"
1872 ON "interest"."member_id" = "member"."id"
1873 WHERE "interest"."issue_id" = "issue_id_p"
1874 AND "member"."active";
1875 FOR "member_id_v" IN
1876 SELECT "member_id" FROM "direct_interest_snapshot"
1877 WHERE "issue_id" = "issue_id_p"
1878 AND "event" = 'periodic'
1879 LOOP
1880 UPDATE "direct_interest_snapshot" SET
1881 "weight" = 1 +
1882 "weight_of_added_delegations_for_interest_snapshot"(
1883 "issue_id_p",
1884 "member_id_v",
1885 '{}'
1886 )
1887 WHERE "issue_id" = "issue_id_p"
1888 AND "event" = 'periodic'
1889 AND "member_id" = "member_id_v";
1890 END LOOP;
1891 INSERT INTO "direct_supporter_snapshot"
1892 ( "issue_id", "initiative_id", "event", "member_id",
1893 "informed", "satisfied" )
1894 SELECT
1895 "issue_id_p" AS "issue_id",
1896 "initiative"."id" AS "initiative_id",
1897 'periodic' AS "event",
1898 "member"."id" AS "member_id",
1899 "supporter"."draft_id" = "current_draft"."id" AS "informed",
1900 NOT EXISTS (
1901 SELECT NULL FROM "critical_opinion"
1902 WHERE "initiative_id" = "initiative"."id"
1903 AND "member_id" = "member"."id"
1904 ) AS "satisfied"
1905 FROM "supporter"
1906 JOIN "member"
1907 ON "supporter"."member_id" = "member"."id"
1908 JOIN "initiative"
1909 ON "supporter"."initiative_id" = "initiative"."id"
1910 JOIN "current_draft"
1911 ON "initiative"."id" = "current_draft"."initiative_id"
1912 JOIN "direct_interest_snapshot"
1913 ON "member"."id" = "direct_interest_snapshot"."member_id"
1914 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
1915 AND "event" = 'periodic'
1916 WHERE "member"."active"
1917 AND "initiative"."issue_id" = "issue_id_p";
1918 RETURN;
1919 END;
1920 $$;
1922 COMMENT ON FUNCTION "create_interest_snapshot"
1923 ( "issue"."id"%TYPE )
1924 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.';
1927 CREATE FUNCTION "create_snapshot"
1928 ( "issue_id_p" "issue"."id"%TYPE )
1929 RETURNS VOID
1930 LANGUAGE 'plpgsql' VOLATILE AS $$
1931 DECLARE
1932 "initiative_id_v" "initiative"."id"%TYPE;
1933 "suggestion_id_v" "suggestion"."id"%TYPE;
1934 BEGIN
1935 PERFORM "global_lock"();
1936 PERFORM "create_population_snapshot"("issue_id_p");
1937 PERFORM "create_interest_snapshot"("issue_id_p");
1938 UPDATE "issue" SET
1939 "snapshot" = now(),
1940 "latest_snapshot_event" = 'periodic',
1941 "population" = (
1942 SELECT coalesce(sum("weight"), 0)
1943 FROM "direct_population_snapshot"
1944 WHERE "issue_id" = "issue_id_p"
1945 AND "event" = 'periodic'
1946 ),
1947 "vote_now" = (
1948 SELECT coalesce(sum("weight"), 0)
1949 FROM "direct_interest_snapshot"
1950 WHERE "issue_id" = "issue_id_p"
1951 AND "event" = 'periodic'
1952 AND "voting_requested" = TRUE
1953 ),
1954 "vote_later" = (
1955 SELECT coalesce(sum("weight"), 0)
1956 FROM "direct_interest_snapshot"
1957 WHERE "issue_id" = "issue_id_p"
1958 AND "event" = 'periodic'
1959 AND "voting_requested" = FALSE
1960 )
1961 WHERE "id" = "issue_id_p";
1962 FOR "initiative_id_v" IN
1963 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
1964 LOOP
1965 UPDATE "initiative" SET
1966 "supporter_count" = (
1967 SELECT coalesce(sum("di"."weight"), 0)
1968 FROM "direct_interest_snapshot" AS "di"
1969 JOIN "direct_supporter_snapshot" AS "ds"
1970 ON "di"."member_id" = "ds"."member_id"
1971 WHERE "di"."issue_id" = "issue_id_p"
1972 AND "di"."event" = 'periodic'
1973 AND "ds"."initiative_id" = "initiative_id_v"
1974 AND "ds"."event" = 'periodic'
1975 ),
1976 "informed_supporter_count" = (
1977 SELECT coalesce(sum("di"."weight"), 0)
1978 FROM "direct_interest_snapshot" AS "di"
1979 JOIN "direct_supporter_snapshot" AS "ds"
1980 ON "di"."member_id" = "ds"."member_id"
1981 WHERE "di"."issue_id" = "issue_id_p"
1982 AND "di"."event" = 'periodic'
1983 AND "ds"."initiative_id" = "initiative_id_v"
1984 AND "ds"."event" = 'periodic'
1985 AND "ds"."informed"
1986 ),
1987 "satisfied_supporter_count" = (
1988 SELECT coalesce(sum("di"."weight"), 0)
1989 FROM "direct_interest_snapshot" AS "di"
1990 JOIN "direct_supporter_snapshot" AS "ds"
1991 ON "di"."member_id" = "ds"."member_id"
1992 WHERE "di"."issue_id" = "issue_id_p"
1993 AND "di"."event" = 'periodic'
1994 AND "ds"."initiative_id" = "initiative_id_v"
1995 AND "ds"."event" = 'periodic'
1996 AND "ds"."satisfied"
1997 ),
1998 "satisfied_informed_supporter_count" = (
1999 SELECT coalesce(sum("di"."weight"), 0)
2000 FROM "direct_interest_snapshot" AS "di"
2001 JOIN "direct_supporter_snapshot" AS "ds"
2002 ON "di"."member_id" = "ds"."member_id"
2003 WHERE "di"."issue_id" = "issue_id_p"
2004 AND "di"."event" = 'periodic'
2005 AND "ds"."initiative_id" = "initiative_id_v"
2006 AND "ds"."event" = 'periodic'
2007 AND "ds"."informed"
2008 AND "ds"."satisfied"
2009 )
2010 WHERE "id" = "initiative_id_v";
2011 FOR "suggestion_id_v" IN
2012 SELECT "id" FROM "suggestion"
2013 WHERE "initiative_id" = "initiative_id_v"
2014 LOOP
2015 UPDATE "suggestion" SET
2016 "minus2_unfulfilled_count" = (
2017 SELECT coalesce(sum("snapshot"."weight"), 0)
2018 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2019 ON "opinion"."member_id" = "snapshot"."member_id"
2020 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2021 AND "snapshot"."issue_id" = "issue_id_p"
2022 AND "opinion"."degree" = -2
2023 AND "opinion"."fulfilled" = FALSE
2024 ),
2025 "minus2_fulfilled_count" = (
2026 SELECT coalesce(sum("snapshot"."weight"), 0)
2027 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2028 ON "opinion"."member_id" = "snapshot"."member_id"
2029 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2030 AND "snapshot"."issue_id" = "issue_id_p"
2031 AND "opinion"."degree" = -2
2032 AND "opinion"."fulfilled" = TRUE
2033 ),
2034 "minus1_unfulfilled_count" = (
2035 SELECT coalesce(sum("snapshot"."weight"), 0)
2036 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2037 ON "opinion"."member_id" = "snapshot"."member_id"
2038 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2039 AND "snapshot"."issue_id" = "issue_id_p"
2040 AND "opinion"."degree" = -1
2041 AND "opinion"."fulfilled" = FALSE
2042 ),
2043 "minus1_fulfilled_count" = (
2044 SELECT coalesce(sum("snapshot"."weight"), 0)
2045 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2046 ON "opinion"."member_id" = "snapshot"."member_id"
2047 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2048 AND "snapshot"."issue_id" = "issue_id_p"
2049 AND "opinion"."degree" = -1
2050 AND "opinion"."fulfilled" = TRUE
2051 ),
2052 "plus1_unfulfilled_count" = (
2053 SELECT coalesce(sum("snapshot"."weight"), 0)
2054 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2055 ON "opinion"."member_id" = "snapshot"."member_id"
2056 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2057 AND "snapshot"."issue_id" = "issue_id_p"
2058 AND "opinion"."degree" = 1
2059 AND "opinion"."fulfilled" = FALSE
2060 ),
2061 "plus1_fulfilled_count" = (
2062 SELECT coalesce(sum("snapshot"."weight"), 0)
2063 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2064 ON "opinion"."member_id" = "snapshot"."member_id"
2065 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2066 AND "snapshot"."issue_id" = "issue_id_p"
2067 AND "opinion"."degree" = 1
2068 AND "opinion"."fulfilled" = TRUE
2069 ),
2070 "plus2_unfulfilled_count" = (
2071 SELECT coalesce(sum("snapshot"."weight"), 0)
2072 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2073 ON "opinion"."member_id" = "snapshot"."member_id"
2074 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2075 AND "snapshot"."issue_id" = "issue_id_p"
2076 AND "opinion"."degree" = 2
2077 AND "opinion"."fulfilled" = FALSE
2078 ),
2079 "plus2_fulfilled_count" = (
2080 SELECT coalesce(sum("snapshot"."weight"), 0)
2081 FROM "opinion" JOIN "direct_interest_snapshot" AS "snapshot"
2082 ON "opinion"."member_id" = "snapshot"."member_id"
2083 WHERE "opinion"."suggestion_id" = "suggestion_id_v"
2084 AND "snapshot"."issue_id" = "issue_id_p"
2085 AND "opinion"."degree" = 2
2086 AND "opinion"."fulfilled" = TRUE
2087 )
2088 WHERE "suggestion"."id" = "suggestion_id_v";
2089 END LOOP;
2090 END LOOP;
2091 RETURN;
2092 END;
2093 $$;
2095 COMMENT ON FUNCTION "create_snapshot"
2096 ( "issue"."id"%TYPE )
2097 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.';
2100 CREATE FUNCTION "set_snapshot_event"
2101 ( "issue_id_p" "issue"."id"%TYPE,
2102 "event_p" "snapshot_event" )
2103 RETURNS VOID
2104 LANGUAGE 'plpgsql' VOLATILE AS $$
2105 BEGIN
2106 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
2107 WHERE "id" = "issue_id_p";
2108 UPDATE "direct_population_snapshot" SET "event" = "event_p"
2109 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
2110 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
2111 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
2112 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
2113 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
2114 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
2115 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
2116 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
2117 WHERE "issue_id" = "issue_id_p" AND "event" = 'periodic';
2118 RETURN;
2119 END;
2120 $$;
2122 COMMENT ON FUNCTION "set_snapshot_event"
2123 ( "issue"."id"%TYPE,
2124 "snapshot_event" )
2125 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
2129 ---------------------
2130 -- Freezing issues --
2131 ---------------------
2133 CREATE FUNCTION "freeze_after_snapshot"
2134 ( "issue_id_p" "issue"."id"%TYPE )
2135 RETURNS VOID
2136 LANGUAGE 'plpgsql' VOLATILE AS $$
2137 DECLARE
2138 "issue_row" "issue"%ROWTYPE;
2139 "policy_row" "policy"%ROWTYPE;
2140 "initiative_row" "initiative"%ROWTYPE;
2141 BEGIN
2142 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2143 SELECT * INTO "policy_row"
2144 FROM "policy" WHERE "id" = "issue_row"."policy_id";
2145 PERFORM "set_snapshot_event"("issue_id_p", 'start_of_voting');
2146 UPDATE "issue" SET
2147 "accepted" = coalesce("accepted", now()),
2148 "half_frozen" = coalesce("half_frozen", now()),
2149 "fully_frozen" = now()
2150 WHERE "id" = "issue_id_p";
2151 FOR "initiative_row" IN
2152 SELECT * FROM "initiative" WHERE "issue_id" = "issue_id_p"
2153 LOOP
2154 IF
2155 "initiative_row"."satisfied_supporter_count" > 0 AND
2156 "initiative_row"."satisfied_supporter_count" *
2157 "policy_row"."initiative_quorum_den" >=
2158 "issue_row"."population" * "policy_row"."initiative_quorum_num"
2159 THEN
2160 UPDATE "initiative" SET "admitted" = TRUE
2161 WHERE "id" = "initiative_row"."id";
2162 ELSE
2163 UPDATE "initiative" SET "admitted" = FALSE
2164 WHERE "id" = "initiative_row"."id";
2165 END IF;
2166 END LOOP;
2167 IF NOT EXISTS (
2168 SELECT NULL FROM "initiative"
2169 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
2170 ) THEN
2171 PERFORM "close_voting"("issue_id_p");
2172 END IF;
2173 RETURN;
2174 END;
2175 $$;
2177 COMMENT ON FUNCTION "freeze_after_snapshot"
2178 ( "issue"."id"%TYPE )
2179 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
2182 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
2183 RETURNS VOID
2184 LANGUAGE 'plpgsql' VOLATILE AS $$
2185 DECLARE
2186 "issue_row" "issue"%ROWTYPE;
2187 BEGIN
2188 PERFORM "create_snapshot"("issue_id_p");
2189 PERFORM "freeze_after_snapshot"("issue_id_p");
2190 RETURN;
2191 END;
2192 $$;
2194 COMMENT ON FUNCTION "freeze_after_snapshot"
2195 ( "issue"."id"%TYPE )
2196 IS 'Freeze an issue manually (fully) and start voting';
2200 -----------------------
2201 -- Counting of votes --
2202 -----------------------
2205 CREATE FUNCTION "weight_of_added_vote_delegations"
2206 ( "issue_id_p" "issue"."id"%TYPE,
2207 "member_id_p" "member"."id"%TYPE,
2208 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
2209 RETURNS "direct_voter"."weight"%TYPE
2210 LANGUAGE 'plpgsql' VOLATILE AS $$
2211 DECLARE
2212 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2213 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
2214 "weight_v" INT4;
2215 "sub_weight_v" INT4;
2216 BEGIN
2217 "weight_v" := 0;
2218 FOR "issue_delegation_row" IN
2219 SELECT * FROM "issue_delegation"
2220 WHERE "trustee_id" = "member_id_p"
2221 AND "issue_id" = "issue_id_p"
2222 LOOP
2223 IF NOT EXISTS (
2224 SELECT NULL FROM "direct_voter"
2225 WHERE "member_id" = "issue_delegation_row"."truster_id"
2226 AND "issue_id" = "issue_id_p"
2227 ) AND NOT EXISTS (
2228 SELECT NULL FROM "delegating_voter"
2229 WHERE "member_id" = "issue_delegation_row"."truster_id"
2230 AND "issue_id" = "issue_id_p"
2231 ) THEN
2232 "delegate_member_ids_v" :=
2233 "member_id_p" || "delegate_member_ids_p";
2234 INSERT INTO "delegating_voter" (
2235 "issue_id",
2236 "member_id",
2237 "scope",
2238 "delegate_member_ids"
2239 ) VALUES (
2240 "issue_id_p",
2241 "issue_delegation_row"."truster_id",
2242 "issue_delegation_row"."scope",
2243 "delegate_member_ids_v"
2244 );
2245 "sub_weight_v" := 1 +
2246 "weight_of_added_vote_delegations"(
2247 "issue_id_p",
2248 "issue_delegation_row"."truster_id",
2249 "delegate_member_ids_v"
2250 );
2251 UPDATE "delegating_voter"
2252 SET "weight" = "sub_weight_v"
2253 WHERE "issue_id" = "issue_id_p"
2254 AND "member_id" = "issue_delegation_row"."truster_id";
2255 "weight_v" := "weight_v" + "sub_weight_v";
2256 END IF;
2257 END LOOP;
2258 RETURN "weight_v";
2259 END;
2260 $$;
2262 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
2263 ( "issue"."id"%TYPE,
2264 "member"."id"%TYPE,
2265 "delegating_voter"."delegate_member_ids"%TYPE )
2266 IS 'Helper function for "add_vote_delegations" function';
2269 CREATE FUNCTION "add_vote_delegations"
2270 ( "issue_id_p" "issue"."id"%TYPE )
2271 RETURNS VOID
2272 LANGUAGE 'plpgsql' VOLATILE AS $$
2273 DECLARE
2274 "member_id_v" "member"."id"%TYPE;
2275 BEGIN
2276 FOR "member_id_v" IN
2277 SELECT "member_id" FROM "direct_voter"
2278 WHERE "issue_id" = "issue_id_p"
2279 LOOP
2280 UPDATE "direct_voter" SET
2281 "weight" = "weight" + "weight_of_added_vote_delegations"(
2282 "issue_id_p",
2283 "member_id_v",
2284 '{}'
2285 )
2286 WHERE "member_id" = "member_id_v"
2287 AND "issue_id" = "issue_id_p";
2288 END LOOP;
2289 RETURN;
2290 END;
2291 $$;
2293 COMMENT ON FUNCTION "add_vote_delegations"
2294 ( "issue_id_p" "issue"."id"%TYPE )
2295 IS 'Helper function for "close_voting" function';
2298 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
2299 RETURNS VOID
2300 LANGUAGE 'plpgsql' VOLATILE AS $$
2301 DECLARE
2302 "issue_row" "issue"%ROWTYPE;
2303 "member_id_v" "member"."id"%TYPE;
2304 BEGIN
2305 PERFORM "global_lock"();
2306 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2307 DELETE FROM "delegating_voter"
2308 WHERE "issue_id" = "issue_id_p";
2309 DELETE FROM "direct_voter"
2310 WHERE "issue_id" = "issue_id_p"
2311 AND "autoreject" = TRUE;
2312 DELETE FROM "direct_voter" USING "member"
2313 WHERE "direct_voter"."member_id" = "member"."id"
2314 AND "direct_voter"."issue_id" = "issue_id_p"
2315 AND "member"."active" = FALSE;
2316 UPDATE "direct_voter" SET "weight" = 1
2317 WHERE "issue_id" = "issue_id_p";
2318 PERFORM "add_vote_delegations"("issue_id_p");
2319 FOR "member_id_v" IN
2320 SELECT "interest"."member_id"
2321 FROM "interest"
2322 LEFT JOIN "direct_voter"
2323 ON "interest"."member_id" = "direct_voter"."member_id"
2324 AND "interest"."issue_id" = "direct_voter"."issue_id"
2325 LEFT JOIN "delegating_voter"
2326 ON "interest"."member_id" = "delegating_voter"."member_id"
2327 AND "interest"."issue_id" = "delegating_voter"."issue_id"
2328 WHERE "interest"."issue_id" = "issue_id_p"
2329 AND "interest"."autoreject" = TRUE
2330 AND "direct_voter"."member_id" ISNULL
2331 AND "delegating_voter"."member_id" ISNULL
2332 UNION SELECT "membership"."member_id"
2333 FROM "membership"
2334 LEFT JOIN "interest"
2335 ON "membership"."member_id" = "interest"."member_id"
2336 AND "interest"."issue_id" = "issue_id_p"
2337 LEFT JOIN "direct_voter"
2338 ON "membership"."member_id" = "direct_voter"."member_id"
2339 AND "direct_voter"."issue_id" = "issue_id_p"
2340 LEFT JOIN "delegating_voter"
2341 ON "membership"."member_id" = "delegating_voter"."member_id"
2342 AND "delegating_voter"."issue_id" = "issue_id_p"
2343 WHERE "membership"."area_id" = "issue_row"."area_id"
2344 AND "membership"."autoreject" = TRUE
2345 AND "interest"."autoreject" ISNULL
2346 AND "direct_voter"."member_id" ISNULL
2347 AND "delegating_voter"."member_id" ISNULL
2348 LOOP
2349 INSERT INTO "direct_voter"
2350 ("member_id", "issue_id", "weight", "autoreject") VALUES
2351 ("member_id_v", "issue_id_p", 1, TRUE);
2352 INSERT INTO "vote" (
2353 "member_id",
2354 "issue_id",
2355 "initiative_id",
2356 "grade"
2357 ) SELECT
2358 "member_id_v" AS "member_id",
2359 "issue_id_p" AS "issue_id",
2360 "id" AS "initiative_id",
2361 -1 AS "grade"
2362 FROM "initiative" WHERE "issue_id" = "issue_id_p";
2363 END LOOP;
2364 PERFORM "add_vote_delegations"("issue_id_p");
2365 UPDATE "issue" SET
2366 "voter_count" = (
2367 SELECT coalesce(sum("weight"), 0)
2368 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
2369 )
2370 WHERE "id" = "issue_id_p";
2371 UPDATE "initiative" SET
2372 "positive_votes" = "vote_counts"."positive_votes",
2373 "negative_votes" = "vote_counts"."negative_votes",
2374 "agreed" = CASE WHEN "majority_strict" THEN
2375 "vote_counts"."positive_votes" * "majority_den" >
2376 "majority_num" *
2377 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
2378 ELSE
2379 "vote_counts"."positive_votes" * "majority_den" >=
2380 "majority_num" *
2381 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
2382 END
2383 FROM
2384 ( SELECT
2385 "initiative"."id" AS "initiative_id",
2386 coalesce(
2387 sum(
2388 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
2389 ),
2390 0
2391 ) AS "positive_votes",
2392 coalesce(
2393 sum(
2394 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
2395 ),
2396 0
2397 ) AS "negative_votes"
2398 FROM "initiative"
2399 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
2400 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
2401 LEFT JOIN "direct_voter"
2402 ON "direct_voter"."issue_id" = "initiative"."issue_id"
2403 LEFT JOIN "vote"
2404 ON "vote"."initiative_id" = "initiative"."id"
2405 AND "vote"."member_id" = "direct_voter"."member_id"
2406 WHERE "initiative"."issue_id" = "issue_id_p"
2407 AND "initiative"."admitted"
2408 GROUP BY "initiative"."id"
2409 ) AS "vote_counts",
2410 "issue",
2411 "policy"
2412 WHERE "vote_counts"."initiative_id" = "initiative"."id"
2413 AND "issue"."id" = "initiative"."issue_id"
2414 AND "policy"."id" = "issue"."policy_id";
2415 UPDATE "issue" SET "closed" = now() WHERE "id" = "issue_id_p";
2416 END;
2417 $$;
2419 COMMENT ON FUNCTION "close_voting"
2420 ( "issue"."id"%TYPE )
2421 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.';
2424 CREATE FUNCTION "init_array"("dim_p" INTEGER)
2425 RETURNS INT4[]
2426 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2427 DECLARE
2428 "i" INTEGER;
2429 "ary_text_v" TEXT;
2430 BEGIN
2431 IF "dim_p" >= 1 THEN
2432 "ary_text_v" := '{NULL';
2433 "i" := "dim_p";
2434 LOOP
2435 "i" := "i" - 1;
2436 EXIT WHEN "i" = 0;
2437 "ary_text_v" := "ary_text_v" || ',NULL';
2438 END LOOP;
2439 "ary_text_v" := "ary_text_v" || '}';
2440 RETURN "ary_text_v"::INT4[][];
2441 ELSE
2442 RAISE EXCEPTION 'Dimension needs to be at least 1.';
2443 END IF;
2444 END;
2445 $$;
2447 COMMENT ON FUNCTION "init_array"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
2450 CREATE FUNCTION "init_square_matrix"("dim_p" INTEGER)
2451 RETURNS INT4[][]
2452 LANGUAGE 'plpgsql' IMMUTABLE AS $$
2453 DECLARE
2454 "i" INTEGER;
2455 "row_text_v" TEXT;
2456 "ary_text_v" TEXT;
2457 BEGIN
2458 IF "dim_p" >= 1 THEN
2459 "row_text_v" := '{NULL';
2460 "i" := "dim_p";
2461 LOOP
2462 "i" := "i" - 1;
2463 EXIT WHEN "i" = 0;
2464 "row_text_v" := "row_text_v" || ',NULL';
2465 END LOOP;
2466 "row_text_v" := "row_text_v" || '}';
2467 "ary_text_v" := '{' || "row_text_v";
2468 "i" := "dim_p";
2469 LOOP
2470 "i" := "i" - 1;
2471 EXIT WHEN "i" = 0;
2472 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
2473 END LOOP;
2474 "ary_text_v" := "ary_text_v" || '}';
2475 RETURN "ary_text_v"::INT4[][];
2476 ELSE
2477 RAISE EXCEPTION 'Dimension needs to be at least 1.';
2478 END IF;
2479 END;
2480 $$;
2482 COMMENT ON FUNCTION "init_square_matrix"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
2485 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
2486 RETURNS VOID
2487 LANGUAGE 'plpgsql' VOLATILE AS $$
2488 DECLARE
2489 "dimension_v" INTEGER;
2490 "matrix" INT4[][];
2491 "i" INTEGER;
2492 "j" INTEGER;
2493 "k" INTEGER;
2494 "battle_row" "battle"%ROWTYPE;
2495 "rank_ary" INT4[];
2496 "rank_v" INT4;
2497 "done_v" INTEGER;
2498 "winners_ary" INTEGER[];
2499 "initiative_id_v" "initiative"."id"%TYPE;
2500 BEGIN
2501 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2502 -- Prepare matrix for Schulze-Method:
2503 SELECT count(1) INTO "dimension_v" FROM "initiative"
2504 WHERE "issue_id" = "issue_id_p" AND "agreed";
2505 IF "dimension_v" = 1 THEN
2506 UPDATE "initiative" SET "rank" = 1
2507 WHERE "issue_id" = "issue_id_p" AND "agreed";
2508 ELSIF "dimension_v" > 1 THEN
2509 "matrix" := "init_square_matrix"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2510 "i" := 1;
2511 "j" := 2;
2512 -- Fill matrix with data from "battle" view
2513 FOR "battle_row" IN
2514 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
2515 ORDER BY "winning_initiative_id", "losing_initiative_id"
2516 LOOP
2517 "matrix"["i"]["j"] := "battle_row"."count";
2518 IF "j" = "dimension_v" THEN
2519 "i" := "i" + 1;
2520 "j" := 1;
2521 ELSE
2522 "j" := "j" + 1;
2523 IF "j" = "i" THEN
2524 "j" := "j" + 1;
2525 END IF;
2526 END IF;
2527 END LOOP;
2528 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
2529 RAISE EXCEPTION 'Wrong battle count (should not happen)';
2530 END IF;
2531 -- Delete losers from matrix:
2532 "i" := 1;
2533 LOOP
2534 "j" := "i" + 1;
2535 LOOP
2536 IF "i" != "j" THEN
2537 IF "matrix"["i"]["j"] < "matrix"["j"]["i"] THEN
2538 "matrix"["i"]["j"] := 0;
2539 ELSIF matrix[j][i] < matrix[i][j] THEN
2540 "matrix"["j"]["i"] := 0;
2541 ELSE
2542 "matrix"["i"]["j"] := 0;
2543 "matrix"["j"]["i"] := 0;
2544 END IF;
2545 END IF;
2546 EXIT WHEN "j" = "dimension_v";
2547 "j" := "j" + 1;
2548 END LOOP;
2549 EXIT WHEN "i" = "dimension_v" - 1;
2550 "i" := "i" + 1;
2551 END LOOP;
2552 -- Find best paths:
2553 "i" := 1;
2554 LOOP
2555 "j" := 1;
2556 LOOP
2557 IF "i" != "j" THEN
2558 "k" := 1;
2559 LOOP
2560 IF "i" != "k" AND "j" != "k" THEN
2561 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
2562 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
2563 "matrix"["j"]["k"] := "matrix"["j"]["i"];
2564 END IF;
2565 ELSE
2566 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
2567 "matrix"["j"]["k"] := "matrix"["i"]["k"];
2568 END IF;
2569 END IF;
2570 END IF;
2571 EXIT WHEN "k" = "dimension_v";
2572 "k" := "k" + 1;
2573 END LOOP;
2574 END IF;
2575 EXIT WHEN "j" = "dimension_v";
2576 "j" := "j" + 1;
2577 END LOOP;
2578 EXIT WHEN "i" = "dimension_v";
2579 "i" := "i" + 1;
2580 END LOOP;
2581 -- Determine order of winners:
2582 "rank_ary" := "init_array"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
2583 "rank_v" := 1;
2584 "done_v" := 0;
2585 LOOP
2586 "winners_ary" := '{}';
2587 "i" := 1;
2588 LOOP
2589 IF "rank_ary"["i"] ISNULL THEN
2590 "j" := 1;
2591 LOOP
2592 IF
2593 "i" != "j" AND
2594 "rank_ary"["j"] ISNULL AND
2595 "matrix"["j"]["i"] > "matrix"["i"]["j"]
2596 THEN
2597 -- someone else is better
2598 EXIT;
2599 END IF;
2600 IF "j" = "dimension_v" THEN
2601 -- noone is better
2602 "winners_ary" := "winners_ary" || "i";
2603 EXIT;
2604 END IF;
2605 "j" := "j" + 1;
2606 END LOOP;
2607 END IF;
2608 EXIT WHEN "i" = "dimension_v";
2609 "i" := "i" + 1;
2610 END LOOP;
2611 "i" := 1;
2612 LOOP
2613 "rank_ary"["winners_ary"["i"]] := "rank_v";
2614 "done_v" := "done_v" + 1;
2615 EXIT WHEN "i" = array_upper("winners_ary", 1);
2616 "i" := "i" + 1;
2617 END LOOP;
2618 EXIT WHEN "done_v" = "dimension_v";
2619 "rank_v" := "rank_v" + 1;
2620 END LOOP;
2621 -- write preliminary ranks:
2622 "i" := 1;
2623 FOR "initiative_id_v" IN
2624 SELECT "id" FROM "initiative"
2625 WHERE "issue_id" = "issue_id_p" AND "agreed"
2626 ORDER BY "id"
2627 LOOP
2628 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
2629 WHERE "id" = "initiative_id_v";
2630 "i" := "i" + 1;
2631 END LOOP;
2632 IF "i" != "dimension_v" + 1 THEN
2633 RAISE EXCEPTION 'Wrong winner count (should not happen)';
2634 END IF;
2635 -- straighten ranks (start counting with 1, no equal ranks):
2636 "rank_v" := 1;
2637 FOR "initiative_id_v" IN
2638 SELECT "id" FROM "initiative"
2639 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
2640 ORDER BY
2641 "rank",
2642 "vote_ratio"("positive_votes", "negative_votes") DESC,
2643 "id"
2644 LOOP
2645 UPDATE "initiative" SET "rank" = "rank_v"
2646 WHERE "id" = "initiative_id_v";
2647 "rank_v" := "rank_v" + 1;
2648 END LOOP;
2649 END IF;
2650 -- mark issue as finished
2651 UPDATE "issue" SET "ranks_available" = TRUE
2652 WHERE "id" = "issue_id_p";
2653 RETURN;
2654 END;
2655 $$;
2657 COMMENT ON FUNCTION "calculate_ranks"
2658 ( "issue"."id"%TYPE )
2659 IS 'Determine ranking (Votes have to be counted first)';
2663 -----------------------------
2664 -- Automatic state changes --
2665 -----------------------------
2668 CREATE FUNCTION "check_issue"
2669 ( "issue_id_p" "issue"."id"%TYPE )
2670 RETURNS VOID
2671 LANGUAGE 'plpgsql' VOLATILE AS $$
2672 DECLARE
2673 "issue_row" "issue"%ROWTYPE;
2674 "policy_row" "policy"%ROWTYPE;
2675 "voting_requested_v" BOOLEAN;
2676 BEGIN
2677 PERFORM "global_lock"();
2678 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2679 IF "issue_row"."closed" ISNULL THEN
2680 SELECT * INTO "policy_row" FROM "policy"
2681 WHERE "id" = "issue_row"."policy_id";
2682 IF "issue_row"."fully_frozen" ISNULL THEN
2683 PERFORM "create_snapshot"("issue_id_p");
2684 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2685 END IF;
2686 IF "issue_row"."accepted" ISNULL THEN
2687 IF EXISTS (
2688 SELECT NULL FROM "initiative"
2689 WHERE "issue_id" = "issue_id_p"
2690 AND "supporter_count" > 0
2691 AND "supporter_count" * "policy_row"."issue_quorum_den"
2692 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
2693 ) THEN
2694 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
2695 "issue_row"."accepted" = now(); -- NOTE: "issue_row" used later
2696 UPDATE "issue" SET "accepted" = "issue_row"."accepted"
2697 WHERE "id" = "issue_row"."id";
2698 ELSIF
2699 now() >= "issue_row"."created" + "policy_row"."admission_time"
2700 THEN
2701 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
2702 UPDATE "issue" SET "closed" = now()
2703 WHERE "id" = "issue_row"."id";
2704 END IF;
2705 END IF;
2706 IF
2707 "issue_row"."accepted" NOTNULL AND
2708 "issue_row"."half_frozen" ISNULL
2709 THEN
2710 SELECT
2711 CASE
2712 WHEN "vote_now" * 2 > "issue_row"."population" THEN
2713 TRUE
2714 WHEN "vote_later" * 2 > "issue_row"."population" THEN
2715 FALSE
2716 ELSE NULL
2717 END
2718 INTO "voting_requested_v"
2719 FROM "issue" WHERE "id" = "issue_id_p";
2720 IF
2721 "voting_requested_v" OR (
2722 "voting_requested_v" ISNULL AND
2723 now() >= "issue_row"."accepted" + "policy_row"."discussion_time"
2724 )
2725 THEN
2726 "issue_row"."half_frozen" = now(); -- NOTE: "issue_row" used later
2727 UPDATE "issue" SET "half_frozen" = "issue_row"."half_frozen"
2728 WHERE "id" = "issue_row"."id";
2729 END IF;
2730 END IF;
2731 IF
2732 "issue_row"."half_frozen" NOTNULL AND
2733 "issue_row"."fully_frozen" ISNULL AND
2734 now() >= "issue_row"."half_frozen" + "policy_row"."verification_time"
2735 THEN
2736 PERFORM "freeze_after_snapshot"("issue_id_p");
2737 -- "issue" might change, thus "issue_row" has to be updated below
2738 END IF;
2739 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2740 IF
2741 "issue_row"."closed" ISNULL AND
2742 "issue_row"."fully_frozen" NOTNULL AND
2743 now() >= "issue_row"."fully_frozen" + "policy_row"."voting_time"
2744 THEN
2745 PERFORM "close_voting"("issue_id_p");
2746 END IF;
2747 END IF;
2748 RETURN;
2749 END;
2750 $$;
2752 COMMENT ON FUNCTION "check_issue"
2753 ( "issue"."id"%TYPE )
2754 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.';
2757 CREATE FUNCTION "check_everything"()
2758 RETURNS VOID
2759 LANGUAGE 'plpgsql' VOLATILE AS $$
2760 DECLARE
2761 "issue_id_v" "issue"."id"%TYPE;
2762 BEGIN
2763 DELETE FROM "expired_session";
2764 PERFORM "calculate_member_counts"();
2765 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
2766 PERFORM "check_issue"("issue_id_v");
2767 END LOOP;
2768 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
2769 PERFORM "calculate_ranks"("issue_id_v");
2770 END LOOP;
2771 RETURN;
2772 END;
2773 $$;
2775 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.';
2779 ------------------------------
2780 -- Deletion of private data --
2781 ------------------------------
2784 CREATE FUNCTION "delete_private_data"()
2785 RETURNS VOID
2786 LANGUAGE 'plpgsql' VOLATILE AS $$
2787 DECLARE
2788 "issue_id_v" "issue"."id"%TYPE;
2789 BEGIN
2790 UPDATE "member" SET
2791 "login" = 'login' || "id"::text,
2792 "password" = NULL,
2793 "notify_email" = NULL,
2794 "notify_email_unconfirmed" = NULL,
2795 "notify_email_secret" = NULL,
2796 "notify_email_secret_expiry" = NULL,
2797 "password_reset_secret" = NULL,
2798 "password_reset_secret_expiry" = NULL,
2799 "organizational_unit" = NULL,
2800 "internal_posts" = NULL,
2801 "realname" = NULL,
2802 "birthday" = NULL,
2803 "address" = NULL,
2804 "email" = NULL,
2805 "xmpp_address" = NULL,
2806 "website" = NULL,
2807 "phone" = NULL,
2808 "mobile_phone" = NULL,
2809 "profession" = NULL,
2810 "external_memberships" = NULL,
2811 "external_posts" = NULL,
2812 "statement" = NULL;
2813 -- "text_search_data" is updated by triggers
2814 DELETE FROM "session";
2815 DELETE FROM "invite_code";
2816 DELETE FROM "contact";
2817 DELETE FROM "setting";
2818 DELETE FROM "member_image";
2819 DELETE FROM "direct_voter" USING "issue"
2820 WHERE "direct_voter"."issue_id" = "issue"."id"
2821 AND "issue"."closed" ISNULL;
2822 RETURN;
2823 END;
2824 $$;
2826 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.';
2830 COMMIT;