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