liquid_feedback_core
view core.sql @ 111:844c442c5a80
Work on new features; Slight change of behaviour regarding revocation of initiatives
New features:
- possiblity for members to store issue and voting comments
- initial work on notification system (new columns in table "membership" and new "event" table)
- added new type "issue_state" and new column "issue"."state"
Other changes:
- revocation of last initiative closes issue without delay, if issue has not been accepted yet
- moved table "contingent" up (after "system_setting" table)
New features:
- possiblity for members to store issue and voting comments
- initial work on notification system (new columns in table "membership" and new "event" table)
- added new type "issue_state" and new column "issue"."state"
Other changes:
- revocation of last initiative closes issue without delay, if issue has not been accepted yet
- moved table "contingent" up (after "system_setting" table)
| author | jbe |
|---|---|
| date | Thu Mar 03 00:01:37 2011 +0100 (2011-03-03) |
| parents | 575559c319e9 |
| children | 1b1e266df99b |
line source
2 -- Execute the following command manually for PostgreSQL prior version 9.0:
3 -- CREATE LANGUAGE plpgsql;
5 -- NOTE: In PostgreSQL every UNIQUE constraint implies creation of an index
7 BEGIN;
9 CREATE VIEW "liquid_feedback_version" AS
10 SELECT * FROM (VALUES ('1.4.0', 1, 4, 0))
11 AS "subquery"("string", "major", "minor", "revision");
15 ----------------------
16 -- Full text search --
17 ----------------------
20 CREATE FUNCTION "text_search_query"("query_text_p" TEXT)
21 RETURNS TSQUERY
22 LANGUAGE 'plpgsql' IMMUTABLE AS $$
23 BEGIN
24 RETURN plainto_tsquery('pg_catalog.simple', "query_text_p");
25 END;
26 $$;
28 COMMENT ON FUNCTION "text_search_query"(TEXT) IS 'Usage: WHERE "text_search_data" @@ "text_search_query"(''<user query>'')';
31 CREATE FUNCTION "highlight"
32 ( "body_p" TEXT,
33 "query_text_p" TEXT )
34 RETURNS TEXT
35 LANGUAGE 'plpgsql' IMMUTABLE AS $$
36 BEGIN
37 RETURN ts_headline(
38 'pg_catalog.simple',
39 replace(replace("body_p", e'\\', e'\\\\'), '*', e'\\*'),
40 "text_search_query"("query_text_p"),
41 'StartSel=* StopSel=* HighlightAll=TRUE' );
42 END;
43 $$;
45 COMMENT ON FUNCTION "highlight"
46 ( "body_p" TEXT,
47 "query_text_p" TEXT )
48 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.';
52 -------------------------
53 -- Tables and indicies --
54 -------------------------
57 CREATE TABLE "system_setting" (
58 "member_ttl" INTERVAL );
59 CREATE UNIQUE INDEX "system_setting_singleton_idx" ON "system_setting" ((1));
61 COMMENT ON TABLE "system_setting" IS 'This table contains only one row with different settings in each column.';
62 COMMENT ON INDEX "system_setting_singleton_idx" IS 'This index ensures that "system_setting" only contains one row maximum.';
64 COMMENT ON COLUMN "system_setting"."member_ttl" IS 'Time after members get their "active" flag set to FALSE, if they do not login anymore.';
67 CREATE TABLE "contingent" (
68 "time_frame" INTERVAL PRIMARY KEY,
69 "text_entry_limit" INT4,
70 "initiative_limit" INT4 );
72 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.';
74 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';
75 COMMENT ON COLUMN "contingent"."initiative_limit" IS 'Number of new initiatives to be opened by each member within a given time frame';
78 CREATE TABLE "member" (
79 "id" SERIAL4 PRIMARY KEY,
80 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
81 "last_login" TIMESTAMPTZ,
82 "last_login_public" DATE,
83 "login" TEXT UNIQUE,
84 "password" TEXT,
85 "locked" BOOLEAN NOT NULL DEFAULT FALSE,
86 "active" BOOLEAN NOT NULL DEFAULT TRUE,
87 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
88 "notify_email" TEXT,
89 "notify_email_unconfirmed" TEXT,
90 "notify_email_secret" TEXT UNIQUE,
91 "notify_email_secret_expiry" TIMESTAMPTZ,
92 "notify_email_lock_expiry" TIMESTAMPTZ,
93 "password_reset_secret" TEXT UNIQUE,
94 "password_reset_secret_expiry" TIMESTAMPTZ,
95 "name" TEXT NOT NULL UNIQUE,
96 "identification" TEXT UNIQUE,
97 "organizational_unit" TEXT,
98 "internal_posts" TEXT,
99 "realname" TEXT,
100 "birthday" DATE,
101 "address" TEXT,
102 "email" TEXT,
103 "xmpp_address" TEXT,
104 "website" TEXT,
105 "phone" TEXT,
106 "mobile_phone" TEXT,
107 "profession" TEXT,
108 "external_memberships" TEXT,
109 "external_posts" TEXT,
110 "statement" TEXT,
111 "text_search_data" TSVECTOR );
112 CREATE INDEX "member_active_idx" ON "member" ("active");
113 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
114 CREATE TRIGGER "update_text_search_data"
115 BEFORE INSERT OR UPDATE ON "member"
116 FOR EACH ROW EXECUTE PROCEDURE
117 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
118 "name", "identification", "organizational_unit", "internal_posts",
119 "realname", "external_memberships", "external_posts", "statement" );
121 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
123 COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login';
124 COMMENT ON COLUMN "member"."last_login_public" IS 'Date of last login (time stripped for privacy reasons, updated only after day change)';
125 COMMENT ON COLUMN "member"."login" IS 'Login name';
126 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
127 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
128 COMMENT ON COLUMN "member"."active" IS 'Memberships, support and votes are taken into account when corresponding members are marked as active. When the user does not log in for an extended period of time, this flag may be set to FALSE. If the user is not locked, he/she may reset the active flag by logging in.';
129 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
130 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
131 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
132 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
133 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
134 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
135 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member';
136 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
137 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
138 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
139 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
140 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
141 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
142 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
143 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his homepage within the system';
146 CREATE TABLE "member_history" (
147 "id" SERIAL8 PRIMARY KEY,
148 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
149 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
150 "active" BOOLEAN NOT NULL,
151 "name" TEXT NOT NULL );
152 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
154 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
156 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
157 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
160 CREATE TABLE "invite_code" (
161 "id" SERIAL8 PRIMARY KEY,
162 "code" TEXT NOT NULL UNIQUE,
163 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
164 "used" TIMESTAMPTZ,
165 "member_id" INT4 UNIQUE REFERENCES "member" ("id") ON DELETE SET NULL ON UPDATE CASCADE,
166 "comment" TEXT,
167 CONSTRAINT "only_used_codes_may_refer_to_member" CHECK ("used" NOTNULL OR "member_id" ISNULL) );
169 COMMENT ON TABLE "invite_code" IS 'Invite codes can be used once to create a new member account.';
171 COMMENT ON COLUMN "invite_code"."code" IS 'Secret code';
172 COMMENT ON COLUMN "invite_code"."created" IS 'Time of creation of the secret code';
173 COMMENT ON COLUMN "invite_code"."used" IS 'NULL, if not used yet, otherwise tells when this code was used to create a member account';
174 COMMENT ON COLUMN "invite_code"."member_id" IS 'References the member whose account was created with this code';
175 COMMENT ON COLUMN "invite_code"."comment" IS 'Comment on the code, which is to be used for administrative reasons only';
178 CREATE TABLE "setting" (
179 PRIMARY KEY ("member_id", "key"),
180 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
181 "key" TEXT NOT NULL,
182 "value" TEXT NOT NULL );
183 CREATE INDEX "setting_key_idx" ON "setting" ("key");
185 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
187 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
190 CREATE TABLE "setting_map" (
191 PRIMARY KEY ("member_id", "key", "subkey"),
192 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
193 "key" TEXT NOT NULL,
194 "subkey" TEXT NOT NULL,
195 "value" TEXT NOT NULL );
196 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
198 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
200 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
201 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
202 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
205 CREATE TABLE "member_relation_setting" (
206 PRIMARY KEY ("member_id", "key", "other_member_id"),
207 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
208 "key" TEXT NOT NULL,
209 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
210 "value" TEXT NOT NULL );
212 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
215 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
217 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
220 CREATE TABLE "member_image" (
221 PRIMARY KEY ("member_id", "image_type", "scaled"),
222 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
223 "image_type" "member_image_type",
224 "scaled" BOOLEAN,
225 "content_type" TEXT,
226 "data" BYTEA NOT NULL );
228 COMMENT ON TABLE "member_image" IS 'Images of members';
230 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
233 CREATE TABLE "member_count" (
234 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
235 "total_count" INT4 NOT NULL );
237 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';
239 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
240 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
243 CREATE TABLE "contact" (
244 PRIMARY KEY ("member_id", "other_member_id"),
245 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
246 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
247 "public" BOOLEAN NOT NULL DEFAULT FALSE,
248 CONSTRAINT "cant_save_yourself_as_contact"
249 CHECK ("member_id" != "other_member_id") );
251 COMMENT ON TABLE "contact" IS 'Contact lists';
253 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
254 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
255 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
258 CREATE TABLE "session" (
259 "ident" TEXT PRIMARY KEY,
260 "additional_secret" TEXT,
261 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
262 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
263 "lang" TEXT );
264 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
266 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend';
268 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
269 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
270 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
271 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
274 CREATE TABLE "policy" (
275 "id" SERIAL4 PRIMARY KEY,
276 "index" INT4 NOT NULL,
277 "active" BOOLEAN NOT NULL DEFAULT TRUE,
278 "name" TEXT NOT NULL UNIQUE,
279 "description" TEXT NOT NULL DEFAULT '',
280 "admission_time" INTERVAL NOT NULL,
281 "discussion_time" INTERVAL NOT NULL,
282 "verification_time" INTERVAL NOT NULL,
283 "voting_time" INTERVAL NOT NULL,
284 "issue_quorum_num" INT4 NOT NULL,
285 "issue_quorum_den" INT4 NOT NULL,
286 "initiative_quorum_num" INT4 NOT NULL,
287 "initiative_quorum_den" INT4 NOT NULL,
288 "majority_num" INT4 NOT NULL DEFAULT 1,
289 "majority_den" INT4 NOT NULL DEFAULT 2,
290 "majority_strict" BOOLEAN NOT NULL DEFAULT TRUE );
291 CREATE INDEX "policy_active_idx" ON "policy" ("active");
293 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
295 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
296 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
297 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum time an issue stays open without being "accepted"';
298 COMMENT ON COLUMN "policy"."discussion_time" IS 'Regular time until an issue is "half_frozen" after being "accepted"';
299 COMMENT ON COLUMN "policy"."verification_time" IS 'Regular time until an issue is "fully_frozen" after being "half_frozen"';
300 COMMENT ON COLUMN "policy"."voting_time" IS 'Time after an issue is "fully_frozen" but not "closed"';
301 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"';
302 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"';
303 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
304 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
305 COMMENT ON COLUMN "policy"."majority_num" IS 'Numerator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
306 COMMENT ON COLUMN "policy"."majority_den" IS 'Denominator of fraction of majority to be reached during voting by an initiative to be aggreed upon';
307 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.';
310 CREATE TABLE "unit" (
311 "id" SERIAL4 PRIMARY KEY,
312 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
313 "active" BOOLEAN NOT NULL DEFAULT TRUE,
314 "name" TEXT NOT NULL,
315 "description" TEXT NOT NULL DEFAULT '',
316 "member_count" INT4,
317 "text_search_data" TSVECTOR );
318 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
319 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
320 CREATE INDEX "unit_active_idx" ON "unit" ("active");
321 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
322 CREATE TRIGGER "update_text_search_data"
323 BEFORE INSERT OR UPDATE ON "unit"
324 FOR EACH ROW EXECUTE PROCEDURE
325 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
326 "name", "description" );
328 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
330 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
331 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in units of this area';
332 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
335 CREATE TABLE "area" (
336 "id" SERIAL4 PRIMARY KEY,
337 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
338 "active" BOOLEAN NOT NULL DEFAULT TRUE,
339 "name" TEXT NOT NULL,
340 "description" TEXT NOT NULL DEFAULT '',
341 "direct_member_count" INT4,
342 "member_weight" INT4,
343 "autoreject_weight" INT4,
344 "text_search_data" TSVECTOR );
345 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
346 CREATE INDEX "area_active_idx" ON "area" ("active");
347 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
348 CREATE TRIGGER "update_text_search_data"
349 BEFORE INSERT OR UPDATE ON "area"
350 FOR EACH ROW EXECUTE PROCEDURE
351 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
352 "name", "description" );
354 COMMENT ON TABLE "area" IS 'Subject areas';
356 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
357 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"';
358 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
359 COMMENT ON COLUMN "area"."autoreject_weight" IS 'Sum of weight of members using the autoreject feature';
362 CREATE TABLE "area_setting" (
363 PRIMARY KEY ("member_id", "key", "area_id"),
364 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
365 "key" TEXT NOT NULL,
366 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
367 "value" TEXT NOT NULL );
369 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
372 CREATE TABLE "allowed_policy" (
373 PRIMARY KEY ("area_id", "policy_id"),
374 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
375 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
376 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
377 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
379 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
381 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
384 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
386 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';
389 CREATE TYPE "issue_state" AS ENUM ('admission', 'discussion', 'verification', 'voting', 'canceled_all_initiatives_revoked', 'canceled_issue_not_accepted', 'calculation', 'canceled_no_initiative_admitted', 'finished_without_winner', 'finished_with_winner');
391 COMMENT ON TYPE "issue_state" IS 'State of issues';
394 CREATE TABLE "issue" (
395 "id" SERIAL4 PRIMARY KEY,
396 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
397 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
398 "state" "issue_state" NOT NULL DEFAULT 'admission',
399 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
400 "accepted" TIMESTAMPTZ,
401 "half_frozen" TIMESTAMPTZ,
402 "fully_frozen" TIMESTAMPTZ,
403 "closed" TIMESTAMPTZ,
404 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
405 "cleaned" TIMESTAMPTZ,
406 "admission_time" INTERVAL NOT NULL,
407 "discussion_time" INTERVAL NOT NULL,
408 "verification_time" INTERVAL NOT NULL,
409 "voting_time" INTERVAL NOT NULL,
410 "snapshot" TIMESTAMPTZ,
411 "latest_snapshot_event" "snapshot_event",
412 "population" INT4,
413 "vote_now" INT4,
414 "vote_later" INT4,
415 "voter_count" INT4,
416 CONSTRAINT "valid_state" CHECK ((
417 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
418 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
419 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
420 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
421 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
422 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
423 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
424 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
425 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)) AND (
426 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
427 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
428 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
429 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
430 ("state" = 'canceled_all_initiatives_revoked' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
431 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
432 ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
433 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
434 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
435 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
436 )),
437 CONSTRAINT "state_change_order" CHECK (
438 "created" <= "accepted" AND
439 "accepted" <= "half_frozen" AND
440 "half_frozen" <= "fully_frozen" AND
441 "fully_frozen" <= "closed" ),
442 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
443 "cleaned" ISNULL OR "closed" NOTNULL ),
444 CONSTRAINT "last_snapshot_on_full_freeze"
445 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
446 CONSTRAINT "freeze_requires_snapshot"
447 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
448 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
449 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
450 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
451 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
452 CREATE INDEX "issue_created_idx" ON "issue" ("created");
453 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
454 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
455 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
456 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
457 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
458 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
460 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
462 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
463 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.';
464 COMMENT ON COLUMN "issue"."fully_frozen" IS 'Point in time, when "verification_time" has elapsed and voting has started; 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.';
465 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.';
466 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
467 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
468 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
469 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
470 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
471 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
472 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';
473 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';
474 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
475 COMMENT ON COLUMN "issue"."vote_now" IS 'Number of votes in favor of voting now, as calculated from table "direct_interest_snapshot"';
476 COMMENT ON COLUMN "issue"."vote_later" IS 'Number of votes against voting now, as calculated from table "direct_interest_snapshot"';
477 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';
480 CREATE TABLE "issue_setting" (
481 PRIMARY KEY ("member_id", "key", "issue_id"),
482 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
483 "key" TEXT NOT NULL,
484 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
485 "value" TEXT NOT NULL );
487 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
490 CREATE TABLE "initiative" (
491 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
492 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
493 "id" SERIAL4 PRIMARY KEY,
494 "name" TEXT NOT NULL,
495 "discussion_url" TEXT,
496 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
497 "revoked" TIMESTAMPTZ,
498 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
499 "admitted" BOOLEAN,
500 "supporter_count" INT4,
501 "informed_supporter_count" INT4,
502 "satisfied_supporter_count" INT4,
503 "satisfied_informed_supporter_count" INT4,
504 "positive_votes" INT4,
505 "negative_votes" INT4,
506 "agreed" BOOLEAN,
507 "rank" INT4,
508 "text_search_data" TSVECTOR,
509 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
510 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
511 CONSTRAINT "revoked_initiatives_cant_be_admitted"
512 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
513 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results"
514 CHECK (("admitted" NOTNULL AND "admitted" = TRUE) OR ("positive_votes" ISNULL AND "negative_votes" ISNULL AND "agreed" ISNULL)),
515 CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null"
516 CHECK ("positive_votes" NOTNULL = "negative_votes" NOTNULL AND "positive_votes" NOTNULL = "agreed" NOTNULL),
517 CONSTRAINT "non_agreed_initiatives_cant_get_a_rank"
518 CHECK (("agreed" NOTNULL AND "agreed" = TRUE) OR "rank" ISNULL) );
519 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
520 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
521 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
522 CREATE TRIGGER "update_text_search_data"
523 BEFORE INSERT OR UPDATE ON "initiative"
524 FOR EACH ROW EXECUTE PROCEDURE
525 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
526 "name", "discussion_url");
528 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.';
530 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
531 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
532 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
533 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
534 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
535 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
536 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
537 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
538 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
539 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"';
540 COMMENT ON COLUMN "initiative"."rank" IS 'Rank of approved initiatives (winner is 1), calculated from table "direct_voter"';
543 CREATE TABLE "battle" (
544 PRIMARY KEY ("issue_id", "winning_initiative_id", "losing_initiative_id"),
545 "issue_id" INT4,
546 "winning_initiative_id" INT4,
547 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
548 "losing_initiative_id" INT4,
549 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
550 "count" INT4 NOT NULL);
552 COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue';
555 CREATE TABLE "initiative_setting" (
556 PRIMARY KEY ("member_id", "key", "initiative_id"),
557 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
558 "key" TEXT NOT NULL,
559 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
560 "value" TEXT NOT NULL );
562 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
565 CREATE TABLE "draft" (
566 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
567 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
568 "id" SERIAL8 PRIMARY KEY,
569 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
570 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
571 "formatting_engine" TEXT,
572 "content" TEXT NOT NULL,
573 "text_search_data" TSVECTOR );
574 CREATE INDEX "draft_created_idx" ON "draft" ("created");
575 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
576 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
577 CREATE TRIGGER "update_text_search_data"
578 BEFORE INSERT OR UPDATE ON "draft"
579 FOR EACH ROW EXECUTE PROCEDURE
580 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
582 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.';
584 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
585 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
588 CREATE TABLE "rendered_draft" (
589 PRIMARY KEY ("draft_id", "format"),
590 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
591 "format" TEXT,
592 "content" TEXT NOT NULL );
594 COMMENT ON TABLE "rendered_draft" IS 'This table may be used by frontends to cache "rendered" drafts (e.g. HTML output generated from wiki text)';
597 CREATE TABLE "suggestion" (
598 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
599 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
600 "id" SERIAL8 PRIMARY KEY,
601 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
602 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
603 "name" TEXT NOT NULL,
604 "description" TEXT NOT NULL DEFAULT '',
605 "text_search_data" TSVECTOR,
606 "minus2_unfulfilled_count" INT4,
607 "minus2_fulfilled_count" INT4,
608 "minus1_unfulfilled_count" INT4,
609 "minus1_fulfilled_count" INT4,
610 "plus1_unfulfilled_count" INT4,
611 "plus1_fulfilled_count" INT4,
612 "plus2_unfulfilled_count" INT4,
613 "plus2_fulfilled_count" INT4 );
614 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
615 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
616 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
617 CREATE TRIGGER "update_text_search_data"
618 BEFORE INSERT OR UPDATE ON "suggestion"
619 FOR EACH ROW EXECUTE PROCEDURE
620 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
621 "name", "description");
623 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';
625 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
626 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
627 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
628 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
629 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
630 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
631 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
632 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
635 CREATE TABLE "suggestion_setting" (
636 PRIMARY KEY ("member_id", "key", "suggestion_id"),
637 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
638 "key" TEXT NOT NULL,
639 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
640 "value" TEXT NOT NULL );
642 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
645 CREATE TABLE "invite_code_unit" (
646 PRIMARY KEY ("invite_code_id", "unit_id"),
647 "invite_code_id" INT8 REFERENCES "invite_code" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
648 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
650 COMMENT ON TABLE "invite_code_unit" IS 'Units where accounts created with a given invite codes get voting rights';
653 CREATE TABLE "privilege" (
654 PRIMARY KEY ("unit_id", "member_id"),
655 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
656 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
657 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
658 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
659 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
660 "voting_right_manager" BOOLEAN NOT NULL DEFAULT FALSE,
661 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE );
663 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
665 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke admin privileges to/from other users';
666 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create or lock sub units';
667 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create or lock areas and set area parameters';
668 COMMENT ON COLUMN "privilege"."voting_right_manager" IS 'Select which members are allowed to discuss and vote inside the unit';
669 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to discuss and vote';
672 CREATE TYPE "notify_level" AS ENUM ('never', 'supported', 'interested', 'always');
674 COMMENT ON TYPE "notify_level" IS 'Type used to indicate when a member wants to get certain notifications';
677 CREATE TABLE "membership" (
678 PRIMARY KEY ("area_id", "member_id"),
679 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
680 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
681 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE,
682 "notify_issue" "notify_level" NOT NULL DEFAULT 'always' CHECK ("notify_issue" IN ('never', 'always')),
683 "notify_state" "notify_level" NOT NULL DEFAULT 'interested' CHECK ("notify_state" IN ('never', 'interested', 'always')),
684 "notify_initiative" "notify_level" NOT NULL DEFAULT 'interested' CHECK ("notify_initiative" IN ('never', 'interested', 'always')),
685 "notify_draft" "notify_level" NOT NULL DEFAULT 'supported',
686 "notify_suggestion" "notify_level" NOT NULL DEFAULT 'supported');
687 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
689 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
691 COMMENT ON COLUMN "membership"."autoreject" IS 'TRUE = member votes against all initiatives, if he is neither direct_ or delegating_voter; Entries in the "interest" table can override this setting.';
692 COMMENT ON COLUMN "membership"."notify_issue" IS 'Selects when member gets notifications about a new issue (first initiative)';
693 COMMENT ON COLUMN "membership"."notify_state" IS 'Selects when member gets notifications about issue state changes';
694 COMMENT ON COLUMN "membership"."notify_initiative" IS 'Selects when member gets notifications about new initiatives';
695 COMMENT ON COLUMN "membership"."notify_draft" IS 'Selects when member gets notifications about new drafts';
696 COMMENT ON COLUMN "membership"."notify_suggestion" IS 'Selects when member gets notifications about new suggestions';
699 CREATE TABLE "interest" (
700 PRIMARY KEY ("issue_id", "member_id"),
701 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
702 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
703 "autoreject" BOOLEAN,
704 "voting_requested" BOOLEAN );
705 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
707 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.';
709 COMMENT ON COLUMN "interest"."autoreject" IS 'TRUE = member votes against all initiatives in case of not explicitly taking part in the voting procedure';
710 COMMENT ON COLUMN "interest"."voting_requested" IS 'TRUE = member wants to vote now, FALSE = member wants to vote later, NULL = policy rules should apply';
713 CREATE TABLE "ignored_issue" (
714 PRIMARY KEY ("issue_id", "member_id"),
715 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
716 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
717 "new" BOOLEAN NOT NULL DEFAULT FALSE,
718 "accepted" BOOLEAN NOT NULL DEFAULT FALSE,
719 "half_frozen" BOOLEAN NOT NULL DEFAULT FALSE,
720 "fully_frozen" BOOLEAN NOT NULL DEFAULT FALSE );
721 CREATE INDEX "ignored_issue_member_id_idx" ON "ignored_issue" ("member_id");
723 COMMENT ON TABLE "ignored_issue" IS 'Table to store member specific options to ignore issues in selected states';
725 COMMENT ON COLUMN "ignored_issue"."new" IS 'Apply when issue is neither closed nor accepted';
726 COMMENT ON COLUMN "ignored_issue"."accepted" IS 'Apply when issue is accepted but not (half_)frozen or closed';
727 COMMENT ON COLUMN "ignored_issue"."half_frozen" IS 'Apply when issue is half_frozen but not fully_frozen or closed';
728 COMMENT ON COLUMN "ignored_issue"."fully_frozen" IS 'Apply when issue is fully_frozen (in voting) and not closed';
731 CREATE TABLE "initiator" (
732 PRIMARY KEY ("initiative_id", "member_id"),
733 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
734 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
735 "accepted" BOOLEAN );
736 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
738 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.';
740 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.';
743 CREATE TABLE "supporter" (
744 "issue_id" INT4 NOT NULL,
745 PRIMARY KEY ("initiative_id", "member_id"),
746 "initiative_id" INT4,
747 "member_id" INT4,
748 "draft_id" INT8 NOT NULL,
749 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
750 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE );
751 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
753 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.';
755 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft, defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
758 CREATE TABLE "opinion" (
759 "initiative_id" INT4 NOT NULL,
760 PRIMARY KEY ("suggestion_id", "member_id"),
761 "suggestion_id" INT8,
762 "member_id" INT4,
763 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
764 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
765 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
766 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
767 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
769 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.';
771 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
774 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
776 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
779 CREATE TABLE "delegation" (
780 "id" SERIAL8 PRIMARY KEY,
781 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
782 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
783 "scope" "delegation_scope" NOT NULL,
784 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
785 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
786 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
787 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
788 CONSTRAINT "no_unit_delegation_to_null"
789 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
790 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
791 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
792 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
793 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
794 UNIQUE ("unit_id", "truster_id"),
795 UNIQUE ("area_id", "truster_id"),
796 UNIQUE ("issue_id", "truster_id") );
797 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
798 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
800 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
802 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
803 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
804 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
807 CREATE TABLE "direct_population_snapshot" (
808 PRIMARY KEY ("issue_id", "event", "member_id"),
809 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
810 "event" "snapshot_event",
811 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
812 "weight" INT4 );
813 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
815 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
817 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
818 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
821 CREATE TABLE "delegating_population_snapshot" (
822 PRIMARY KEY ("issue_id", "event", "member_id"),
823 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
824 "event" "snapshot_event",
825 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
826 "weight" INT4,
827 "scope" "delegation_scope" NOT NULL,
828 "delegate_member_ids" INT4[] NOT NULL );
829 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
831 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
833 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
834 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
835 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
836 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"';
839 CREATE TABLE "direct_interest_snapshot" (
840 PRIMARY KEY ("issue_id", "event", "member_id"),
841 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
842 "event" "snapshot_event",
843 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
844 "weight" INT4,
845 "voting_requested" BOOLEAN );
846 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
848 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
850 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
851 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
852 COMMENT ON COLUMN "direct_interest_snapshot"."voting_requested" IS 'Copied from column "voting_requested" of table "interest"';
855 CREATE TABLE "delegating_interest_snapshot" (
856 PRIMARY KEY ("issue_id", "event", "member_id"),
857 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
858 "event" "snapshot_event",
859 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
860 "weight" INT4,
861 "scope" "delegation_scope" NOT NULL,
862 "delegate_member_ids" INT4[] NOT NULL );
863 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
865 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
867 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
868 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
869 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
870 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"';
873 CREATE TABLE "direct_supporter_snapshot" (
874 "issue_id" INT4 NOT NULL,
875 PRIMARY KEY ("initiative_id", "event", "member_id"),
876 "initiative_id" INT4,
877 "event" "snapshot_event",
878 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
879 "informed" BOOLEAN NOT NULL,
880 "satisfied" BOOLEAN NOT NULL,
881 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
882 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
883 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
885 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
887 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
888 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
889 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
892 CREATE TABLE "direct_voter" (
893 PRIMARY KEY ("issue_id", "member_id"),
894 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
895 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
896 "weight" INT4,
897 "autoreject" BOOLEAN NOT NULL DEFAULT FALSE );
898 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
900 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.';
902 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
903 COMMENT ON COLUMN "direct_voter"."autoreject" IS 'Votes were inserted due to "autoreject" feature';
906 CREATE TABLE "delegating_voter" (
907 PRIMARY KEY ("issue_id", "member_id"),
908 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
909 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
910 "weight" INT4,
911 "scope" "delegation_scope" NOT NULL,
912 "delegate_member_ids" INT4[] NOT NULL );
913 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
915 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
917 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
918 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
919 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"';
922 CREATE TABLE "vote" (
923 "issue_id" INT4 NOT NULL,
924 PRIMARY KEY ("initiative_id", "member_id"),
925 "initiative_id" INT4,
926 "member_id" INT4,
927 "grade" INT4,
928 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
929 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
930 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
932 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.';
934 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.';
937 CREATE TABLE "issue_comment" (
938 PRIMARY KEY ("issue_id", "member_id"),
939 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
940 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
941 "changed" TIMESTAMPTZ NOT NULL DEFAULT now(),
942 "formatting_engine" TEXT,
943 "content" TEXT NOT NULL,
944 "text_search_data" TSVECTOR );
945 CREATE INDEX "issue_comment_member_id_idx" ON "issue_comment" ("member_id");
946 CREATE INDEX "issue_comment_text_search_data_idx" ON "issue_comment" USING gin ("text_search_data");
947 CREATE TRIGGER "update_text_search_data"
948 BEFORE INSERT OR UPDATE ON "issue_comment"
949 FOR EACH ROW EXECUTE PROCEDURE
950 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
952 COMMENT ON TABLE "issue_comment" IS 'Place to store free comments of members related to issues';
954 COMMENT ON COLUMN "issue_comment"."changed" IS 'Time the comment was last changed';
957 CREATE TABLE "rendered_issue_comment" (
958 PRIMARY KEY ("issue_id", "member_id", "format"),
959 FOREIGN KEY ("issue_id", "member_id")
960 REFERENCES "issue_comment" ("issue_id", "member_id")
961 ON DELETE CASCADE ON UPDATE CASCADE,
962 "issue_id" INT4,
963 "member_id" INT4,
964 "format" TEXT,
965 "content" TEXT NOT NULL );
967 COMMENT ON TABLE "rendered_issue_comment" IS 'This table may be used by frontends to cache "rendered" issue comments (e.g. HTML output generated from wiki text)';
970 CREATE TABLE "voting_comment" (
971 PRIMARY KEY ("issue_id", "member_id"),
972 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
973 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
974 "changed" TIMESTAMPTZ,
975 "formatting_engine" TEXT,
976 "content" TEXT NOT NULL,
977 "text_search_data" TSVECTOR );
978 CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id");
979 CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data");
980 CREATE TRIGGER "update_text_search_data"
981 BEFORE INSERT OR UPDATE ON "voting_comment"
982 FOR EACH ROW EXECUTE PROCEDURE
983 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
985 COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.';
987 COMMENT ON COLUMN "voting_comment"."changed" IS 'Is to be set or updated by the frontend, if comment was inserted or updated AFTER the issue has been closed. Otherwise it shall be set to NULL.';
990 CREATE TABLE "rendered_voting_comment" (
991 PRIMARY KEY ("issue_id", "member_id", "format"),
992 FOREIGN KEY ("issue_id", "member_id")
993 REFERENCES "voting_comment" ("issue_id", "member_id")
994 ON DELETE CASCADE ON UPDATE CASCADE,
995 "issue_id" INT4,
996 "member_id" INT4,
997 "format" TEXT,
998 "content" TEXT NOT NULL );
1000 COMMENT ON TABLE "rendered_voting_comment" IS 'This table may be used by frontends to cache "rendered" voting comments (e.g. HTML output generated from wiki text)';
1003 CREATE TABLE "event" ();
1005 COMMENT ON TABLE "event" IS 'TODO';
1009 --------------------------------
1010 -- Writing of history entries --
1011 --------------------------------
1013 CREATE FUNCTION "write_member_history_trigger"()
1014 RETURNS TRIGGER
1015 LANGUAGE 'plpgsql' VOLATILE AS $$
1016 BEGIN
1017 IF
1018 NEW."active" != OLD."active" OR
1019 NEW."name" != OLD."name"
1020 THEN
1021 INSERT INTO "member_history"
1022 ("member_id", "active", "name")
1023 VALUES (NEW."id", OLD."active", OLD."name");
1024 END IF;
1025 RETURN NULL;
1026 END;
1027 $$;
1029 CREATE TRIGGER "write_member_history"
1030 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1031 "write_member_history_trigger"();
1033 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
1034 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
1038 ----------------------------
1039 -- Additional constraints --
1040 ----------------------------
1043 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
1044 RETURNS TRIGGER
1045 LANGUAGE 'plpgsql' VOLATILE AS $$
1046 BEGIN
1047 IF NOT EXISTS (
1048 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
1049 ) THEN
1050 --RAISE 'Cannot create issue without an initial initiative.' USING
1051 -- ERRCODE = 'integrity_constraint_violation',
1052 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
1053 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
1054 END IF;
1055 RETURN NULL;
1056 END;
1057 $$;
1059 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
1060 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
1061 FOR EACH ROW EXECUTE PROCEDURE
1062 "issue_requires_first_initiative_trigger"();
1064 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
1065 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
1068 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
1069 RETURNS TRIGGER
1070 LANGUAGE 'plpgsql' VOLATILE AS $$
1071 DECLARE
1072 "reference_lost" BOOLEAN;
1073 BEGIN
1074 IF TG_OP = 'DELETE' THEN
1075 "reference_lost" := TRUE;
1076 ELSE
1077 "reference_lost" := NEW."issue_id" != OLD."issue_id";
1078 END IF;
1079 IF
1080 "reference_lost" AND NOT EXISTS (
1081 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
1082 )
1083 THEN
1084 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
1085 END IF;
1086 RETURN NULL;
1087 END;
1088 $$;
1090 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
1091 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1092 FOR EACH ROW EXECUTE PROCEDURE
1093 "last_initiative_deletes_issue_trigger"();
1095 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
1096 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
1099 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
1100 RETURNS TRIGGER
1101 LANGUAGE 'plpgsql' VOLATILE AS $$
1102 BEGIN
1103 IF NOT EXISTS (
1104 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
1105 ) THEN
1106 --RAISE 'Cannot create initiative without an initial draft.' USING
1107 -- ERRCODE = 'integrity_constraint_violation',
1108 -- HINT = 'Create issue, initiative and draft within the same transaction.';
1109 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
1110 END IF;
1111 RETURN NULL;
1112 END;
1113 $$;
1115 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
1116 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1117 FOR EACH ROW EXECUTE PROCEDURE
1118 "initiative_requires_first_draft_trigger"();
1120 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
1121 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
1124 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
1125 RETURNS TRIGGER
1126 LANGUAGE 'plpgsql' VOLATILE AS $$
1127 DECLARE
1128 "reference_lost" BOOLEAN;
1129 BEGIN
1130 IF TG_OP = 'DELETE' THEN
1131 "reference_lost" := TRUE;
1132 ELSE
1133 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
1134 END IF;
1135 IF
1136 "reference_lost" AND NOT EXISTS (
1137 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
1138 )
1139 THEN
1140 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
1141 END IF;
1142 RETURN NULL;
1143 END;
1144 $$;
1146 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
1147 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
1148 FOR EACH ROW EXECUTE PROCEDURE
1149 "last_draft_deletes_initiative_trigger"();
1151 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
1152 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
1155 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
1156 RETURNS TRIGGER
1157 LANGUAGE 'plpgsql' VOLATILE AS $$
1158 BEGIN
1159 IF NOT EXISTS (
1160 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
1161 ) THEN
1162 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
1163 END IF;
1164 RETURN NULL;
1165 END;
1166 $$;
1168 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
1169 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
1170 FOR EACH ROW EXECUTE PROCEDURE
1171 "suggestion_requires_first_opinion_trigger"();
1173 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1174 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1177 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1178 RETURNS TRIGGER
1179 LANGUAGE 'plpgsql' VOLATILE AS $$
1180 DECLARE
1181 "reference_lost" BOOLEAN;
1182 BEGIN
1183 IF TG_OP = 'DELETE' THEN
1184 "reference_lost" := TRUE;
1185 ELSE
1186 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1187 END IF;
1188 IF
1189 "reference_lost" AND NOT EXISTS (
1190 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1191 )
1192 THEN
1193 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1194 END IF;
1195 RETURN NULL;
1196 END;
1197 $$;
1199 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1200 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1201 FOR EACH ROW EXECUTE PROCEDURE
1202 "last_opinion_deletes_suggestion_trigger"();
1204 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1205 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1209 ---------------------------------------------------------------
1210 -- Ensure that votes are not modified when issues are frozen --
1211 ---------------------------------------------------------------
1213 -- NOTE: Frontends should ensure this anyway, but in case of programming
1214 -- errors the following triggers ensure data integrity.
1217 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1218 RETURNS TRIGGER
1219 LANGUAGE 'plpgsql' VOLATILE AS $$
1220 DECLARE
1221 "issue_id_v" "issue"."id"%TYPE;
1222 "issue_row" "issue"%ROWTYPE;
1223 BEGIN
1224 IF TG_OP = 'DELETE' THEN
1225 "issue_id_v" := OLD."issue_id";
1226 ELSE
1227 "issue_id_v" := NEW."issue_id";
1228 END IF;
1229 SELECT INTO "issue_row" * FROM "issue"
1230 WHERE "id" = "issue_id_v" FOR SHARE;
1231 IF "issue_row"."closed" NOTNULL THEN
1232 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
1233 END IF;
1234 RETURN NULL;
1235 END;
1236 $$;
1238 CREATE TRIGGER "forbid_changes_on_closed_issue"
1239 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1240 FOR EACH ROW EXECUTE PROCEDURE
1241 "forbid_changes_on_closed_issue_trigger"();
1243 CREATE TRIGGER "forbid_changes_on_closed_issue"
1244 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1245 FOR EACH ROW EXECUTE PROCEDURE
1246 "forbid_changes_on_closed_issue_trigger"();
1248 CREATE TRIGGER "forbid_changes_on_closed_issue"
1249 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1250 FOR EACH ROW EXECUTE PROCEDURE
1251 "forbid_changes_on_closed_issue_trigger"();
1253 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"';
1254 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';
1255 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';
1256 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';
1260 --------------------------------------------------------------------
1261 -- Auto-retrieval of fields only needed for referential integrity --
1262 --------------------------------------------------------------------
1265 CREATE FUNCTION "autofill_issue_id_trigger"()
1266 RETURNS TRIGGER
1267 LANGUAGE 'plpgsql' VOLATILE AS $$
1268 BEGIN
1269 IF NEW."issue_id" ISNULL THEN
1270 SELECT "issue_id" INTO NEW."issue_id"
1271 FROM "initiative" WHERE "id" = NEW."initiative_id";
1272 END IF;
1273 RETURN NEW;
1274 END;
1275 $$;
1277 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1278 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1280 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1281 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1283 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1284 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1285 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1288 CREATE FUNCTION "autofill_initiative_id_trigger"()
1289 RETURNS TRIGGER
1290 LANGUAGE 'plpgsql' VOLATILE AS $$
1291 BEGIN
1292 IF NEW."initiative_id" ISNULL THEN
1293 SELECT "initiative_id" INTO NEW."initiative_id"
1294 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1295 END IF;
1296 RETURN NEW;
1297 END;
1298 $$;
1300 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1301 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1303 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1304 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1308 -----------------------------------------------------
1309 -- Automatic calculation of certain default values --
1310 -----------------------------------------------------
1313 CREATE FUNCTION "copy_timings_trigger"()
1314 RETURNS TRIGGER
1315 LANGUAGE 'plpgsql' VOLATILE AS $$
1316 DECLARE
1317 "policy_row" "policy"%ROWTYPE;
1318 BEGIN
1319 SELECT * INTO "policy_row" FROM "policy"
1320 WHERE "id" = NEW."policy_id";
1321 IF NEW."admission_time" ISNULL THEN
1322 NEW."admission_time" := "policy_row"."admission_time";
1323 END IF;
1324 IF NEW."discussion_time" ISNULL THEN
1325 NEW."discussion_time" := "policy_row"."discussion_time";
1326 END IF;
1327 IF NEW."verification_time" ISNULL THEN
1328 NEW."verification_time" := "policy_row"."verification_time";
1329 END IF;
1330 IF NEW."voting_time" ISNULL THEN
1331 NEW."voting_time" := "policy_row"."voting_time";
1332 END IF;
1333 RETURN NEW;
1334 END;
1335 $$;
1337 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1338 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1340 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1341 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1344 CREATE FUNCTION "supporter_default_for_draft_id_trigger"()
1345 RETURNS TRIGGER
1346 LANGUAGE 'plpgsql' VOLATILE AS $$
1347 BEGIN
1348 IF NEW."draft_id" ISNULL THEN
1349 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1350 WHERE "initiative_id" = NEW."initiative_id";
1351 END IF;
1352 RETURN NEW;
1353 END;
1354 $$;
1356 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1357 FOR EACH ROW EXECUTE PROCEDURE "supporter_default_for_draft_id_trigger"();
1359 COMMENT ON FUNCTION "supporter_default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on table "supporter"';
1360 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';
1364 ----------------------------------------
1365 -- Automatic creation of dependencies --
1366 ----------------------------------------
1369 CREATE FUNCTION "autocreate_interest_trigger"()
1370 RETURNS TRIGGER
1371 LANGUAGE 'plpgsql' VOLATILE AS $$
1372 BEGIN
1373 IF NOT EXISTS (
1374 SELECT NULL FROM "initiative" JOIN "interest"
1375 ON "initiative"."issue_id" = "interest"."issue_id"
1376 WHERE "initiative"."id" = NEW."initiative_id"
1377 AND "interest"."member_id" = NEW."member_id"
1378 ) THEN
1379 BEGIN
1380 INSERT INTO "interest" ("issue_id", "member_id")
1381 SELECT "issue_id", NEW."member_id"
1382 FROM "initiative" WHERE "id" = NEW."initiative_id";
1383 EXCEPTION WHEN unique_violation THEN END;
1384 END IF;
1385 RETURN NEW;
1386 END;
1387 $$;
1389 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1390 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1392 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1393 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';
1396 CREATE FUNCTION "autocreate_supporter_trigger"()
1397 RETURNS TRIGGER
1398 LANGUAGE 'plpgsql' VOLATILE AS $$
1399 BEGIN
1400 IF NOT EXISTS (
1401 SELECT NULL FROM "suggestion" JOIN "supporter"
1402 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1403 WHERE "suggestion"."id" = NEW."suggestion_id"
1404 AND "supporter"."member_id" = NEW."member_id"
1405 ) THEN
1406 BEGIN
1407 INSERT INTO "supporter" ("initiative_id", "member_id")
1408 SELECT "initiative_id", NEW."member_id"
1409 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1410 EXCEPTION WHEN unique_violation THEN END;
1411 END IF;
1412 RETURN NEW;
1413 END;
1414 $$;
1416 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1417 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1419 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1420 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.';
1424 ------------------------------------------
1425 -- Views and helper functions for views --
1426 ------------------------------------------
1429 CREATE VIEW "unit_delegation" AS
1430 SELECT
1431 "unit"."id" AS "unit_id",
1432 "delegation"."id",
1433 "delegation"."truster_id",
1434 "delegation"."trustee_id",
1435 "delegation"."scope"
1436 FROM "unit"
1437 JOIN "delegation"
1438 ON "delegation"."unit_id" = "unit"."id"
1439 JOIN "member"
1440 ON "delegation"."truster_id" = "member"."id"
1441 JOIN "privilege"
1442 ON "delegation"."unit_id" = "privilege"."unit_id"
1443 AND "delegation"."truster_id" = "privilege"."member_id"
1444 WHERE "member"."active" AND "privilege"."voting_right";
1446 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
1449 CREATE VIEW "area_delegation" AS
1450 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1451 "area"."id" AS "area_id",
1452 "delegation"."id",
1453 "delegation"."truster_id",
1454 "delegation"."trustee_id",
1455 "delegation"."scope"
1456 FROM "area"
1457 JOIN "delegation"
1458 ON "delegation"."unit_id" = "area"."unit_id"
1459 OR "delegation"."area_id" = "area"."id"
1460 JOIN "member"
1461 ON "delegation"."truster_id" = "member"."id"
1462 JOIN "privilege"
1463 ON "area"."unit_id" = "privilege"."unit_id"
1464 AND "delegation"."truster_id" = "privilege"."member_id"
1465 WHERE "member"."active" AND "privilege"."voting_right"
1466 ORDER BY
1467 "area"."id",
1468 "delegation"."truster_id",
1469 "delegation"."scope" DESC;
1471 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
1474 CREATE VIEW "issue_delegation" AS
1475 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1476 "issue"."id" AS "issue_id",
1477 "delegation"."id",
1478 "delegation"."truster_id",
1479 "delegation"."trustee_id",
1480 "delegation"."scope"
1481 FROM "issue"
1482 JOIN "area"
1483 ON "area"."id" = "issue"."area_id"
1484 JOIN "delegation"
1485 ON "delegation"."unit_id" = "area"."unit_id"
1486 OR "delegation"."area_id" = "area"."id"
1487 OR "delegation"."issue_id" = "issue"."id"
1488 JOIN "member"
1489 ON "delegation"."truster_id" = "member"."id"
1490 JOIN "privilege"
1491 ON "area"."unit_id" = "privilege"."unit_id"
1492 AND "delegation"."truster_id" = "privilege"."member_id"
1493 WHERE "member"."active" AND "privilege"."voting_right"
1494 ORDER BY
1495 "issue"."id",
1496 "delegation"."truster_id",
1497 "delegation"."scope" DESC;
1499 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
1502 CREATE FUNCTION "membership_weight_with_skipping"
1503 ( "area_id_p" "area"."id"%TYPE,
1504 "member_id_p" "member"."id"%TYPE,
1505 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1506 RETURNS INT4
1507 LANGUAGE 'plpgsql' STABLE AS $$
1508 DECLARE
1509 "sum_v" INT4;
1510 "delegation_row" "area_delegation"%ROWTYPE;
1511 BEGIN
1512 "sum_v" := 1;
1513 FOR "delegation_row" IN
1514 SELECT "area_delegation".*
1515 FROM "area_delegation" LEFT JOIN "membership"
1516 ON "membership"."area_id" = "area_id_p"
1517 AND "membership"."member_id" = "area_delegation"."truster_id"
1518 WHERE "area_delegation"."area_id" = "area_id_p"
1519 AND "area_delegation"."trustee_id" = "member_id_p"
1520 AND "membership"."member_id" ISNULL
1521 LOOP
1522 IF NOT
1523 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1524 THEN
1525 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1526 "area_id_p",
1527 "delegation_row"."truster_id",
1528 "skip_member_ids_p" || "delegation_row"."truster_id"
1529 );
1530 END IF;
1531 END LOOP;
1532 RETURN "sum_v";
1533 END;
1534 $$;
1536 COMMENT ON FUNCTION "membership_weight_with_skipping"
1537 ( "area"."id"%TYPE,
1538 "member"."id"%TYPE,
1539 INT4[] )
1540 IS 'Helper function for "membership_weight" function';
1543 CREATE FUNCTION "membership_weight"
1544 ( "area_id_p" "area"."id"%TYPE,
1545 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1546 RETURNS INT4
1547 LANGUAGE 'plpgsql' STABLE AS $$
1548 BEGIN
1549 RETURN "membership_weight_with_skipping"(
1550 "area_id_p",
1551 "member_id_p",
1552 ARRAY["member_id_p"]
1553 );
1554 END;
1555 $$;
1557 COMMENT ON FUNCTION "membership_weight"
1558 ( "area"."id"%TYPE,
1559 "member"."id"%TYPE )
1560 IS 'Calculates the potential voting weight of a member in a given area';
1563 CREATE VIEW "member_count_view" AS
1564 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1566 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1569 CREATE VIEW "unit_member_count" AS
1570 SELECT
1571 "unit"."id" AS "unit_id",
1572 sum("member"."id") AS "member_count"
1573 FROM "unit"
1574 LEFT JOIN "privilege"
1575 ON "privilege"."unit_id" = "unit"."id"
1576 AND "privilege"."voting_right"
1577 LEFT JOIN "member"
1578 ON "member"."id" = "privilege"."member_id"
1579 AND "member"."active"
1580 GROUP BY "unit"."id";
1582 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
1585 CREATE VIEW "area_member_count" AS
1586 SELECT
1587 "area"."id" AS "area_id",
1588 count("member"."id") AS "direct_member_count",
1589 coalesce(
1590 sum(
1591 CASE WHEN "member"."id" NOTNULL THEN
1592 "membership_weight"("area"."id", "member"."id")
1593 ELSE 0 END
1594 )
1595 ) AS "member_weight",
1596 coalesce(
1597 sum(
1598 CASE WHEN "member"."id" NOTNULL AND "membership"."autoreject" THEN
1599 "membership_weight"("area"."id", "member"."id")
1600 ELSE 0 END
1601 )
1602 ) AS "autoreject_weight"
1603 FROM "area"
1604 LEFT JOIN "membership"
1605 ON "area"."id" = "membership"."area_id"
1606 LEFT JOIN "privilege"
1607 ON "privilege"."unit_id" = "area"."unit_id"
1608 AND "privilege"."member_id" = "membership"."member_id"
1609 AND "privilege"."voting_right"
1610 LEFT JOIN "member"
1611 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
1612 AND "member"."active"
1613 GROUP BY "area"."id";
1615 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count", "member_weight" and "autoreject_weight" columns of table "area"';
1618 CREATE VIEW "opening_draft" AS
1619 SELECT "draft".* FROM (
1620 SELECT
1621 "initiative"."id" AS "initiative_id",
1622 min("draft"."id") AS "draft_id"
1623 FROM "initiative" JOIN "draft"
1624 ON "initiative"."id" = "draft"."initiative_id"
1625 GROUP BY "initiative"."id"
1626 ) AS "subquery"
1627 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1629 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1632 CREATE VIEW "current_draft" AS
1633 SELECT "draft".* FROM (
1634 SELECT
1635 "initiative"."id" AS "initiative_id",
1636 max("draft"."id") AS "draft_id"
1637 FROM "initiative" JOIN "draft"
1638 ON "initiative"."id" = "draft"."initiative_id"
1639 GROUP BY "initiative"."id"
1640 ) AS "subquery"
1641 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1643 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1646 CREATE VIEW "critical_opinion" AS
1647 SELECT * FROM "opinion"
1648 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
1649 OR ("degree" = -2 AND "fulfilled" = TRUE);
1651 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
1654 CREATE VIEW "battle_view" AS
1655 SELECT
1656 "issue"."id" AS "issue_id",
1657 "winning_initiative"."id" AS "winning_initiative_id",
1658 "losing_initiative"."id" AS "losing_initiative_id",
1659 sum(
1660 CASE WHEN
1661 coalesce("better_vote"."grade", 0) >
1662 coalesce("worse_vote"."grade", 0)
1663 THEN "direct_voter"."weight" ELSE 0 END
1664 ) AS "count"
1665 FROM "issue"
1666 LEFT JOIN "direct_voter"
1667 ON "issue"."id" = "direct_voter"."issue_id"
1668 JOIN "initiative" AS "winning_initiative"
1669 ON "issue"."id" = "winning_initiative"."issue_id"
1670 AND "winning_initiative"."agreed"
1671 JOIN "initiative" AS "losing_initiative"
1672 ON "issue"."id" = "losing_initiative"."issue_id"
1673 AND "losing_initiative"."agreed"
1674 LEFT JOIN "vote" AS "better_vote"
1675 ON "direct_voter"."member_id" = "better_vote"."member_id"
1676 AND "winning_initiative"."id" = "better_vote"."initiative_id"
1677 LEFT JOIN "vote" AS "worse_vote"
1678 ON "direct_voter"."member_id" = "worse_vote"."member_id"
1679 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
1680 WHERE "issue"."closed" NOTNULL
1681 AND "issue"."cleaned" ISNULL
1682 AND "winning_initiative"."id" != "losing_initiative"."id"
1683 GROUP BY
1684 "issue"."id",
1685 "winning_initiative"."id",
1686 "losing_initiative"."id";
1688 COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative to another; Used to fill "battle" table';
1691 CREATE VIEW "expired_session" AS
1692 SELECT * FROM "session" WHERE now() > "expiry";
1694 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
1695 DELETE FROM "session" WHERE "ident" = OLD."ident";
1697 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
1698 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
1701 CREATE VIEW "open_issue" AS
1702 SELECT * FROM "issue" WHERE "closed" ISNULL;
1704 COMMENT ON VIEW "open_issue" IS 'All open issues';
1707 CREATE VIEW "issue_with_ranks_missing" AS
1708 SELECT * FROM "issue"
1709 WHERE "fully_frozen" NOTNULL
1710 AND "closed" NOTNULL
1711 AND "ranks_available" = FALSE;
1713 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
1716 CREATE VIEW "member_contingent" AS
1717 SELECT
1718 "member"."id" AS "member_id",
1719 "contingent"."time_frame",
1720 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
1721 (
1722 SELECT count(1) FROM "draft"
1723 WHERE "draft"."author_id" = "member"."id"
1724 AND "draft"."created" > now() - "contingent"."time_frame"
1725 ) + (
1726 SELECT count(1) FROM "suggestion"
1727 WHERE "suggestion"."author_id" = "member"."id"
1728 AND "suggestion"."created" > now() - "contingent"."time_frame"
1729 )
1730 ELSE NULL END AS "text_entry_count",
1731 "contingent"."text_entry_limit",
1732 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
1733 SELECT count(1) FROM "opening_draft"
1734 WHERE "opening_draft"."author_id" = "member"."id"
1735 AND "opening_draft"."created" > now() - "contingent"."time_frame"
1736 ) ELSE NULL END AS "initiative_count",
1737 "contingent"."initiative_limit"
1738 FROM "member" CROSS JOIN "contingent";
1740 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
1742 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
1743 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
1746 CREATE VIEW "member_contingent_left" AS
1747 SELECT
1748 "member_id",
1749 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
1750 max("initiative_limit" - "initiative_count") AS "initiatives_left"
1751 FROM "member_contingent" GROUP BY "member_id";
1753 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.';
1756 CREATE TYPE "timeline_event" AS ENUM (
1757 'issue_created',
1758 'issue_canceled',
1759 'issue_accepted',
1760 'issue_half_frozen',
1761 'issue_finished_without_voting',
1762 'issue_voting_started',
1763 'issue_finished_after_voting',
1764 'initiative_created',
1765 'initiative_revoked',
1766 'draft_created',
1767 'suggestion_created');
1769 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables';
1772 CREATE VIEW "timeline_issue" AS
1773 SELECT
1774 "created" AS "occurrence",
1775 'issue_created'::"timeline_event" AS "event",
1776 "id" AS "issue_id"
1777 FROM "issue"
1778 UNION ALL
1779 SELECT
1780 "closed" AS "occurrence",
1781 'issue_canceled'::"timeline_event" AS "event",
1782 "id" AS "issue_id"
1783 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
1784 UNION ALL
1785 SELECT
1786 "accepted" AS "occurrence",
1787 'issue_accepted'::"timeline_event" AS "event",
1788 "id" AS "issue_id"
1789 FROM "issue" WHERE "accepted" NOTNULL
1790 UNION ALL
1791 SELECT
1792 "half_frozen" AS "occurrence",
1793 'issue_half_frozen'::"timeline_event" AS "event",
1794 "id" AS "issue_id"
1795 FROM "issue" WHERE "half_frozen" NOTNULL
1796 UNION ALL
1797 SELECT
1798 "fully_frozen" AS "occurrence",
1799 'issue_voting_started'::"timeline_event" AS "event",
1800 "id" AS "issue_id"
1801 FROM "issue"
1802 WHERE "fully_frozen" NOTNULL
1803 AND ("closed" ISNULL OR "closed" != "fully_frozen")
1804 UNION ALL
1805 SELECT
1806 "closed" AS "occurrence",
1807 CASE WHEN "fully_frozen" = "closed" THEN
1808 'issue_finished_without_voting'::"timeline_event"
1809 ELSE
1810 'issue_finished_after_voting'::"timeline_event"
1811 END AS "event",
1812 "id" AS "issue_id"
1813 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
1815 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view';
1818 CREATE VIEW "timeline_initiative" AS
1819 SELECT
1820 "created" AS "occurrence",
1821 'initiative_created'::"timeline_event" AS "event",
1822 "id" AS "initiative_id"
1823 FROM "initiative"
1824 UNION ALL
1825 SELECT
1826 "revoked" AS "occurrence",
1827 'initiative_revoked'::"timeline_event" AS "event",
1828 "id" AS "initiative_id"
1829 FROM "initiative" WHERE "revoked" NOTNULL;
1831 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view';
1834 CREATE VIEW "timeline_draft" AS
1835 SELECT
1836 "created" AS "occurrence",
1837 'draft_created'::"timeline_event" AS "event",
1838 "id" AS "draft_id"
1839 FROM "draft";
1841 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view';
1844 CREATE VIEW "timeline_suggestion" AS
1845 SELECT
1846 "created" AS "occurrence",
1847 'suggestion_created'::"timeline_event" AS "event",
1848 "id" AS "suggestion_id"
1849 FROM "suggestion";
1851 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view';
1854 CREATE VIEW "timeline" AS
1855 SELECT
1856 "occurrence",
1857 "event",
1858 "issue_id",
1859 NULL AS "initiative_id",
1860 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
1861 NULL::INT8 AS "suggestion_id"
1862 FROM "timeline_issue"
1863 UNION ALL
1864 SELECT
1865 "occurrence",
1866 "event",
1867 NULL AS "issue_id",
1868 "initiative_id",
1869 NULL AS "draft_id",
1870 NULL AS "suggestion_id"
1871 FROM "timeline_initiative"
1872 UNION ALL
1873 SELECT
1874 "occurrence",
1875 "event",
1876 NULL AS "issue_id",
1877 NULL AS "initiative_id",
1878 "draft_id",
1879 NULL AS "suggestion_id"
1880 FROM "timeline_draft"
1881 UNION ALL
1882 SELECT
1883 "occurrence",
1884 "event",
1885 NULL AS "issue_id",
1886 NULL AS "initiative_id",
1887 NULL AS "draft_id",
1888 "suggestion_id"
1889 FROM "timeline_suggestion";
1891 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system';
1895 --------------------------------------------------
1896 -- Set returning function for delegation chains --
1897 --------------------------------------------------
1900 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
1901 ('first', 'intermediate', 'last', 'repetition');
1903 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
1906 CREATE TYPE "delegation_chain_row" AS (
1907 "index" INT4,
1908 "member_id" INT4,
1909 "member_valid" BOOLEAN,
1910 "participation" BOOLEAN,
1911 "overridden" BOOLEAN,
1912 "scope_in" "delegation_scope",
1913 "scope_out" "delegation_scope",
1914 "disabled_out" BOOLEAN,
1915 "loop" "delegation_chain_loop_tag" );
1917 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain"(...) functions';
1919 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
1920 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';
1921 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
1922 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
1923 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
1924 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
1925 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
1928 CREATE FUNCTION "delegation_chain"
1929 ( "member_id_p" "member"."id"%TYPE,
1930 "unit_id_p" "unit"."id"%TYPE,
1931 "area_id_p" "area"."id"%TYPE,
1932 "issue_id_p" "issue"."id"%TYPE,
1933 "simulate_trustee_id_p" "member"."id"%TYPE )
1934 RETURNS SETOF "delegation_chain_row"
1935 LANGUAGE 'plpgsql' STABLE AS $$
1936 DECLARE
1937 "scope_v" "delegation_scope";
1938 "unit_id_v" "unit"."id"%TYPE;
1939 "area_id_v" "area"."id"%TYPE;
1940 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
1941 "loop_member_id_v" "member"."id"%TYPE;
1942 "output_row" "delegation_chain_row";
1943 "output_rows" "delegation_chain_row"[];
1944 "delegation_row" "delegation"%ROWTYPE;
1945 "row_count" INT4;
1946 "i" INT4;
1947 "loop_v" BOOLEAN;
1948 BEGIN
1949 IF
1950 "unit_id_p" NOTNULL AND
1951 "area_id_p" ISNULL AND
1952 "issue_id_p" ISNULL
1953 THEN
1954 "scope_v" := 'unit';
1955 "unit_id_v" := "unit_id_p";
1956 ELSIF
1957 "unit_id_p" ISNULL AND
1958 "area_id_p" NOTNULL AND
1959 "issue_id_p" ISNULL
1960 THEN
1961 "scope_v" := 'area';
1962 "area_id_v" := "area_id_p";
1963 SELECT "unit_id" INTO "unit_id_v"
1964 FROM "area" WHERE "id" = "area_id_v";
1965 ELSIF
1966 "unit_id_p" ISNULL AND
1967 "area_id_p" ISNULL AND
1968 "issue_id_p" NOTNULL
1969 THEN
1970 "scope_v" := 'issue';
1971 SELECT "area_id" INTO "area_id_v"
1972 FROM "issue" WHERE "id" = "issue_id_p";
1973 SELECT "unit_id" INTO "unit_id_v"
1974 FROM "area" WHERE "id" = "area_id_v";
1975 ELSE
1976 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
1977 END IF;
1978 "visited_member_ids" := '{}';
1979 "loop_member_id_v" := NULL;
1980 "output_rows" := '{}';
1981 "output_row"."index" := 0;
1982 "output_row"."member_id" := "member_id_p";
1983 "output_row"."member_valid" := TRUE;
1984 "output_row"."participation" := FALSE;
1985 "output_row"."overridden" := FALSE;
1986 "output_row"."disabled_out" := FALSE;
1987 "output_row"."scope_out" := NULL;
1988 LOOP
1989 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
1990 "loop_member_id_v" := "output_row"."member_id";
1991 ELSE
1992 "visited_member_ids" :=
1993 "visited_member_ids" || "output_row"."member_id";
1994 END IF;
1995 IF "output_row"."participation" THEN
1996 "output_row"."overridden" := TRUE;
1997 END IF;
1998 "output_row"."scope_in" := "output_row"."scope_out";
1999 IF EXISTS (
2000 SELECT NULL FROM "member" JOIN "privilege"
2001 ON "privilege"."member_id" = "member"."id"
2002 AND "privilege"."unit_id" = "unit_id_v"
2003 WHERE "id" = "output_row"."member_id"
2004 AND "member"."active" AND "privilege"."voting_right"
2005 ) THEN
2006 IF "scope_v" = 'unit' THEN
2007 SELECT * INTO "delegation_row" FROM "delegation"
2008 WHERE "truster_id" = "output_row"."member_id"
2009 AND "unit_id" = "unit_id_v";
2010 ELSIF "scope_v" = 'area' THEN
2011 "output_row"."participation" := EXISTS (
2012 SELECT NULL FROM "membership"
2013 WHERE "area_id" = "area_id_p"
2014 AND "member_id" = "output_row"."member_id"
2015 );
2016 SELECT * INTO "delegation_row" FROM "delegation"
2017 WHERE "truster_id" = "output_row"."member_id"
2018 AND (
2019 "unit_id" = "unit_id_v" OR
2020 "area_id" = "area_id_v"
2021 )
2022 ORDER BY "scope" DESC;
2023 ELSIF "scope_v" = 'issue' THEN
2024 "output_row"."participation" := EXISTS (
2025 SELECT NULL FROM "interest"
2026 WHERE "issue_id" = "issue_id_p"
2027 AND "member_id" = "output_row"."member_id"
2028 );
2029 SELECT * INTO "delegation_row" FROM "delegation"
2030 WHERE "truster_id" = "output_row"."member_id"
2031 AND (
2032 "unit_id" = "unit_id_v" OR
2033 "area_id" = "area_id_v" OR
2034 "issue_id" = "issue_id_p"
2035 )
2036 ORDER BY "scope" DESC;
2037 END IF;
2038 ELSE
2039 "output_row"."member_valid" := FALSE;
2040 "output_row"."participation" := FALSE;
2041 "output_row"."scope_out" := NULL;
2042 "delegation_row" := ROW(NULL);
2043 END IF;
2044 IF
2045 "output_row"."member_id" = "member_id_p" AND
2046 "simulate_trustee_id_p" NOTNULL
2047 THEN
2048 "output_row"."scope_out" := "scope_v";
2049 "output_rows" := "output_rows" || "output_row";
2050 "output_row"."member_id" := "simulate_trustee_id_p";
2051 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2052 "output_row"."scope_out" := "delegation_row"."scope";
2053 "output_rows" := "output_rows" || "output_row";
2054 "output_row"."member_id" := "delegation_row"."trustee_id";
2055 ELSIF "delegation_row"."scope" NOTNULL THEN
2056 "output_row"."scope_out" := "delegation_row"."scope";
2057 "output_row"."disabled_out" := TRUE;
2058 "output_rows" := "output_rows" || "output_row";
2059 EXIT;
2060 ELSE
2061 "output_row"."scope_out" := NULL;
2062 "output_rows" := "output_rows" || "output_row";
2063 EXIT;
2064 END IF;
2065 EXIT WHEN "loop_member_id_v" NOTNULL;
2066 "output_row"."index" := "output_row"."index" + 1;
2067 END LOOP;
2068 "row_count" := array_upper("output_rows", 1);
2069 "i" := 1;
2070 "loop_v" := FALSE;
2071 LOOP
2072 "output_row" := "output_rows"["i"];
2073 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2074 IF "loop_v" THEN
2075 IF "i" + 1 = "row_count" THEN
2076 "output_row"."loop" := 'last';
2077 ELSIF "i" = "row_count" THEN
2078 "output_row"."loop" := 'repetition';
2079 ELSE
2080 "output_row"."loop" := 'intermediate';
2081 END IF;
2082 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2083 "output_row"."loop" := 'first';
2084 "loop_v" := TRUE;
2085 END IF;
2086 IF "scope_v" = 'unit' THEN
2087 "output_row"."participation" := NULL;
2088 END IF;
2089 RETURN NEXT "output_row";
2090 "i" := "i" + 1;
2091 END LOOP;
2092 RETURN;
2093 END;
2094 $$;
2096 COMMENT ON FUNCTION "delegation_chain"
2097 ( "member"."id"%TYPE,
2098 "unit"."id"%TYPE,
2099 "area"."id"%TYPE,
2100 "issue"."id"%TYPE,
2101 "member"."id"%TYPE )
2102 IS 'Helper function for frontends to display delegation chains; Not part of internal voting logic';
2105 CREATE FUNCTION "delegation_chain"
2106 ( "member_id_p" "member"."id"%TYPE,
2107 "unit_id_p" "unit"."id"%TYPE,
2108 "area_id_p" "area"."id"%TYPE,
2109 "issue_id_p" "issue"."id"%TYPE )
2110 RETURNS SETOF "delegation_chain_row"
2111 LANGUAGE 'plpgsql' STABLE AS $$
2112 DECLARE
2113 "result_row" "delegation_chain_row";
2114 BEGIN
2115 FOR "result_row" IN
2116 SELECT * FROM "delegation_chain"(
2117 "member_id_p", "area_id_p", "issue_id_p", NULL
2118 )
2119 LOOP
2120 RETURN NEXT "result_row";
2121 END LOOP;
2122 RETURN;
2123 END;
2124 $$;
2126 COMMENT ON FUNCTION "delegation_chain"
2127 ( "member"."id"%TYPE,
2128 "unit"."id"%TYPE,
2129 "area"."id"%TYPE,
2130 "issue"."id"%TYPE )
2131 IS 'Shortcut for "delegation_chain"(...) function where 4th parameter is null';
2135 ------------------------------
2136 -- Comparison by vote count --
2137 ------------------------------
2139 CREATE FUNCTION "vote_ratio"
2140 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
2141 "negative_votes_p" "initiative"."negative_votes"%TYPE )
2142 RETURNS FLOAT8
2143 LANGUAGE 'plpgsql' STABLE AS $$
2144 BEGIN
2145 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
2146 RETURN
2147 "positive_votes_p"::FLOAT8 /
2148 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
2149 ELSIF "positive_votes_p" > 0 THEN
2150 RETURN "positive_votes_p";
2151 ELSIF "negative_votes_p" > 0 THEN
2152 RETURN 1 - "negative_votes_p";
2153 ELSE
2154 RETURN 0.5;
2155 END IF;
2156 END;
2157 $$;
2159 COMMENT ON FUNCTION "vote_ratio"
2160 ( "initiative"."positive_votes"%TYPE,
2161 "initiative"."negative_votes"%TYPE )
2162 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.';
2166 ------------------------------------------------
2167 -- Locking for snapshots and voting procedure --
2168 ------------------------------------------------
2171 CREATE FUNCTION "share_row_lock_issue_trigger"()
2172 RETURNS TRIGGER
2173 LANGUAGE 'plpgsql' VOLATILE AS $$
2174 BEGIN
2175 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2176 PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;
2177 END IF;
2178 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2179 PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;
2180 RETURN NEW;
2181 ELSE
2182 RETURN OLD;
2183 END IF;
2184 END;
2185 $$;
2187 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';
2190 CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()
2191 RETURNS TRIGGER
2192 LANGUAGE 'plpgsql' VOLATILE AS $$
2193 BEGIN
2194 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2195 PERFORM NULL FROM "issue"
2196 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2197 WHERE "initiative"."id" = OLD."initiative_id"
2198 FOR SHARE OF "issue";
2199 END IF;
2200 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2201 PERFORM NULL FROM "issue"
2202 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2203 WHERE "initiative"."id" = NEW."initiative_id"
2204 FOR SHARE OF "issue";
2205 RETURN NEW;
2206 ELSE
2207 RETURN OLD;
2208 END IF;
2209 END;
2210 $$;
2212 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';
2215 CREATE TRIGGER "share_row_lock_issue"
2216 BEFORE INSERT OR UPDATE OR DELETE ON "initiative"
2217 FOR EACH ROW EXECUTE PROCEDURE
2218 "share_row_lock_issue_trigger"();
2220 CREATE TRIGGER "share_row_lock_issue"
2221 BEFORE INSERT OR UPDATE OR DELETE ON "interest"
2222 FOR EACH ROW EXECUTE PROCEDURE
2223 "share_row_lock_issue_trigger"();
2225 CREATE TRIGGER "share_row_lock_issue"
2226 BEFORE INSERT OR UPDATE OR DELETE ON "supporter"
2227 FOR EACH ROW EXECUTE PROCEDURE
2228 "share_row_lock_issue_trigger"();
2230 CREATE TRIGGER "share_row_lock_issue_via_initiative"
2231 BEFORE INSERT OR UPDATE OR DELETE ON "opinion"
2232 FOR EACH ROW EXECUTE PROCEDURE
2233 "share_row_lock_issue_via_initiative_trigger"();
2235 CREATE TRIGGER "share_row_lock_issue"
2236 BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter"
2237 FOR EACH ROW EXECUTE PROCEDURE
2238 "share_row_lock_issue_trigger"();
2240 CREATE TRIGGER "share_row_lock_issue"
2241 BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter"
2242 FOR EACH ROW EXECUTE PROCEDURE
2243 "share_row_lock_issue_trigger"();
2245 CREATE TRIGGER "share_row_lock_issue"
2246 BEFORE INSERT OR UPDATE OR DELETE ON "vote"
2247 FOR EACH ROW EXECUTE PROCEDURE
2248 "share_row_lock_issue_trigger"();
2250 COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function';
2251 COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function';
2252 COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function';
2253 COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function';
2254 COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function';
2255 COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function';
2256 COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function';
2259 CREATE FUNCTION "lock_issue"
2260 ( "issue_id_p" "issue"."id"%TYPE )
2261 RETURNS VOID
2262 LANGUAGE 'plpgsql' VOLATILE AS $$
2263 BEGIN
2264 LOCK TABLE "member" IN SHARE MODE;
2265 LOCK TABLE "privilege" IN SHARE MODE;
2266 LOCK TABLE "membership" IN SHARE MODE;
2267 LOCK TABLE "policy" IN SHARE MODE;
2268 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2269 -- NOTE: The row-level exclusive lock in combination with the
2270 -- share_row_lock_issue(_via_initiative)_trigger functions (which
2271 -- acquire a row-level share lock on the issue) ensure that no data
2272 -- is changed, which could affect calculation of snapshots or
2273 -- counting of votes. Table "delegation" must be table-level-locked,
2274 -- as it also contains issue- and global-scope delegations.
2275 LOCK TABLE "delegation" IN SHARE MODE;
2276 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
2277 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
2278 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
2279 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
2280 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
2281 RETURN;
2282 END;
2283 $$;
2285 COMMENT ON FUNCTION "lock_issue"
2286 ( "issue"."id"%TYPE )
2287 IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';
2291 ------------------------------------------------------------------------
2292 -- Regular tasks, except calculcation of snapshots and voting results --
2293 ------------------------------------------------------------------------
2295 CREATE FUNCTION "check_last_login"()
2296 RETURNS VOID
2297 LANGUAGE 'plpgsql' VOLATILE AS $$
2298 DECLARE
2299 "system_setting_row" "system_setting"%ROWTYPE;
2300 BEGIN
2301 SELECT * INTO "system_setting_row" FROM "system_setting";
2302 LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
2303 UPDATE "member" SET "last_login_public" = "last_login"::date
2304 FROM (
2305 SELECT DISTINCT "member"."id"
2306 FROM "member" LEFT JOIN "member_history"
2307 ON "member"."id" = "member_history"."member_id"
2308 WHERE "member"."last_login"::date < 'today' OR (
2309 "member_history"."until"::date >= 'today' AND
2310 "member_history"."active" = FALSE AND "member"."active" = TRUE
2311 )
2312 ) AS "subquery"
2313 WHERE "member"."id" = "subquery"."id";
2314 IF "system_setting_row"."member_ttl" NOTNULL THEN
2315 UPDATE "member" SET "active" = FALSE
2316 WHERE "active" = TRUE
2317 AND "last_login"::date < 'today'
2318 AND "last_login_public" <
2319 (now() - "system_setting_row"."member_ttl")::date;
2320 END IF;
2321 RETURN;
2322 END;
2323 $$;
2325 COMMENT ON FUNCTION "check_last_login"() IS 'Updates "last_login_public" field, which contains the date but not the time of the last login, and deactivates members who do not login for the time specified in "system_setting"."member_ttl". For privacy reasons this function does not update "last_login_public", if the last login of a member has been today (except when member was reactivated today).';
2328 CREATE FUNCTION "calculate_member_counts"()
2329 RETURNS VOID
2330 LANGUAGE 'plpgsql' VOLATILE AS $$
2331 BEGIN
2332 LOCK TABLE "member" IN SHARE MODE;
2333 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
2334 LOCK TABLE "unit" IN EXCLUSIVE MODE;
2335 LOCK TABLE "area" IN EXCLUSIVE MODE;
2336 LOCK TABLE "privilege" IN SHARE MODE;
2337 LOCK TABLE "membership" IN SHARE MODE;
2338 DELETE FROM "member_count";
2339 INSERT INTO "member_count" ("total_count")
2340 SELECT "total_count" FROM "member_count_view";
2341 UPDATE "unit" SET "member_count" = "view"."member_count"
2342 FROM "unit_member_count" AS "view"
2343 WHERE "view"."unit_id" = "unit"."id";
2344 UPDATE "area" SET
2345 "direct_member_count" = "view"."direct_member_count",
2346 "member_weight" = "view"."member_weight",
2347 "autoreject_weight" = "view"."autoreject_weight"
2348 FROM "area_member_count" AS "view"
2349 WHERE "view"."area_id" = "area"."id";
2350 RETURN;
2351 END;
2352 $$;
2354 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"';
2358 ------------------------------
2359 -- Calculation of snapshots --
2360 ------------------------------
2362 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
2363 ( "issue_id_p" "issue"."id"%TYPE,
2364 "member_id_p" "member"."id"%TYPE,
2365 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2366 RETURNS "direct_population_snapshot"."weight"%TYPE
2367 LANGUAGE 'plpgsql' VOLATILE AS $$
2368 DECLARE
2369 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2370 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
2371 "weight_v" INT4;
2372 "sub_weight_v" INT4;
2373 BEGIN
2374 "weight_v" := 0;
2375 FOR "issue_delegation_row" IN
2376 SELECT * FROM "issue_delegation"
2377 WHERE "trustee_id" = "member_id_p"
2378 AND "issue_id" = "issue_id_p"
2379 LOOP
2380 IF NOT EXISTS (
2381 SELECT NULL FROM "direct_population_snapshot"
2382 WHERE "issue_id" = "issue_id_p"
2383 AND "event" = 'periodic'
2384 AND "member_id" = "issue_delegation_row"."truster_id"
2385 ) AND NOT EXISTS (
2386 SELECT NULL FROM "delegating_population_snapshot"
2387 WHERE "issue_id" = "issue_id_p"
2388 AND "event" = 'periodic'
2389 AND "member_id" = "issue_delegation_row"."truster_id"
2390 ) THEN
2391 "delegate_member_ids_v" :=
2392 "member_id_p" || "delegate_member_ids_p";
2393 INSERT INTO "delegating_population_snapshot" (
2394 "issue_id",
2395 "event",
2396 "member_id",
2397 "scope",
2398 "delegate_member_ids"
2399 ) VALUES (
2400 "issue_id_p",
2401 'periodic',
2402 "issue_delegation_row"."truster_id",
2403 "issue_delegation_row"."scope",
2404 "delegate_member_ids_v"
2405 );
2406 "sub_weight_v" := 1 +
2407 "weight_of_added_delegations_for_population_snapshot"(
2408 "issue_id_p",
2409 "issue_delegation_row"."truster_id",
2410 "delegate_member_ids_v"
2411 );
2412 UPDATE "delegating_population_snapshot"
2413 SET "weight" = "sub_weight_v"
2414 WHERE "issue_id" = "issue_id_p"
2415 AND "event" = 'periodic'
2416 AND "member_id" = "issue_delegation_row"."truster_id";
2417 "weight_v" := "weight_v" + "sub_weight_v";
2418 END IF;
2419 END LOOP;
2420 RETURN "weight_v";
2421 END;
2422 $$;
2424 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
2425 ( "issue"."id"%TYPE,
2426 "member"."id"%TYPE,
2427 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
2428 IS 'Helper function for "create_population_snapshot" function';
2431 CREATE FUNCTION "create_population_snapshot"
2432 ( "issue_id_p" "issue"."id"%TYPE )
2433 RETURNS VOID
2434 LANGUAGE 'plpgsql' VOLATILE AS $$
2435 DECLARE
2436 "member_id_v" "member"."id"%TYPE;
2437 BEGIN
2438 DELETE FROM "direct_population_snapshot"
2439 WHERE "issue_id" = "issue_id_p"
2440 AND "event" = 'periodic';
2441 DELETE FROM "delegating_population_snapshot"
2442 WHERE "issue_id" = "issue_id_p"
2443 AND "event" = 'periodic';
2444 INSERT INTO "direct_population_snapshot"
2445 ("issue_id", "event", "member_id")
2446 SELECT
2447 "issue_id_p" AS "issue_id",
2448 'periodic'::"snapshot_event" AS "event",
2449 "member"."id" AS "member_id"
2450 FROM "issue"
2451 JOIN "area" ON "issue"."area_id" = "area"."id"
2452 JOIN "membership" ON "area"."id" = "membership"."area_id"
2453 JOIN "member" ON "membership"."member_id" = "member"."id"
2454 JOIN "privilege"
2455 ON "privilege"."unit_id" = "area"."unit_id"
2456 AND "privilege"."member_id" = "member"."id"
2457 WHERE "issue"."id" = "issue_id_p"
2458 AND "member"."active" AND "privilege"."voting_right"
2459 UNION
2460 SELECT
2461 "issue_id_p" AS "issue_id",
2462 'periodic'::"snapshot_event" AS "event",
2463 "member"."id" AS "member_id"
2464 FROM "issue"
2465 JOIN "area" ON "issue"."area_id" = "area"."id"
2466 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2467 JOIN "member" ON "interest"."member_id" = "member"."id"
2468 JOIN "privilege"
2469 ON "privilege"."unit_id" = "area"."unit_id"
2470 AND "privilege"."member_id" = "member"."id"
2471 WHERE "issue"."id" = "issue_id_p"
2472 AND "member"."active" AND "privilege"."voting_right";
2473 FOR "member_id_v" IN
2474 SELECT "member_id" FROM "direct_population_snapshot"
2475 WHERE "issue_id" = "issue_id_p"
2476 AND "event" = 'periodic'
2477 LOOP
2478 UPDATE "direct_population_snapshot" SET
2479 "weight" = 1 +
2480 "weight_of_added_delegations_for_population_snapshot"(
2481 "issue_id_p",
2482 "member_id_v",
2483 '{}'
2484 )
2485 WHERE "issue_id" = "issue_id_p"
2486 AND "event" = 'periodic'
2487 AND "member_id" = "member_id_v";
2488 END LOOP;
2489 RETURN;
2490 END;
2491 $$;
2493 COMMENT ON FUNCTION "create_population_snapshot"
2494 ( "issue"."id"%TYPE )
2495 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.';
2498 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2499 ( "issue_id_p" "issue"."id"%TYPE,
2500 "member_id_p" "member"."id"%TYPE,
2501 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2502 RETURNS "direct_interest_snapshot"."weight"%TYPE
2503 LANGUAGE 'plpgsql' VOLATILE AS $$
2504 DECLARE
2505 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2506 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
2507 "weight_v" INT4;
2508 "sub_weight_v" INT4;
2509 BEGIN
2510 "weight_v" := 0;
2511 FOR "issue_delegation_row" IN
2512 SELECT * FROM "issue_delegation"
2513 WHERE "trustee_id" = "member_id_p"
2514 AND "issue_id" = "issue_id_p"
2515 LOOP
2516 IF NOT EXISTS (
2517 SELECT NULL FROM "direct_interest_snapshot"
2518 WHERE "issue_id" = "issue_id_p"
2519 AND "event" = 'periodic'
2520 AND "member_id" = "issue_delegation_row"."truster_id"
2521 ) AND NOT EXISTS (
2522 SELECT NULL FROM "delegating_interest_snapshot"
2523 WHERE "issue_id" = "issue_id_p"
2524 AND "event" = 'periodic'
2525 AND "member_id" = "issue_delegation_row"."truster_id"
2526 ) THEN
2527 "delegate_member_ids_v" :=
2528 "member_id_p" || "delegate_member_ids_p";
2529 INSERT INTO "delegating_interest_snapshot" (
2530 "issue_id",
2531 "event",
2532 "member_id",
2533 "scope",
2534 "delegate_member_ids"
2535 ) VALUES (
2536 "issue_id_p",
2537 'periodic',
2538 "issue_delegation_row"."truster_id",
2539 "issue_delegation_row"."scope",
2540 "delegate_member_ids_v"
2541 );
2542 "sub_weight_v" := 1 +
2543 "weight_of_added_delegations_for_interest_snapshot"(
2544 "issue_id_p",
2545 "issue_delegation_row"."truster_id",
2546 "delegate_member_ids_v"
2547 );
2548 UPDATE "delegating_interest_snapshot"
2549 SET "weight" = "sub_weight_v"
2550 WHERE "issue_id" = "issue_id_p"
2551 AND "event" = 'periodic'
2552 AND "member_id" = "issue_delegation_row"."truster_id";
2553 "weight_v" := "weight_v" + "sub_weight_v";
2554 END IF;
2555 END LOOP;
2556 RETURN "weight_v";
2557 END;
2558 $$;
2560 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
2561 ( "issue"."id"%TYPE,
2562 "member"."id"%TYPE,
2563 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
2564 IS 'Helper function for "create_interest_snapshot" function';
2567 CREATE FUNCTION "create_interest_snapshot"
2568 ( "issue_id_p" "issue"."id"%TYPE )
2569 RETURNS VOID
2570 LANGUAGE 'plpgsql' VOLATILE AS $$
2571 DECLARE
2572 "member_id_v" "member"."id"%TYPE;
2573 BEGIN
2574 DELETE FROM "direct_interest_snapshot"
2575 WHERE "issue_id" = "issue_id_p"
2576 AND "event" = 'periodic';
2577 DELETE FROM "delegating_interest_snapshot"
2578 WHERE "issue_id" = "issue_id_p"
2579 AND "event" = 'periodic';
2580 DELETE FROM "direct_supporter_snapshot"
2581 WHERE "issue_id" = "issue_id_p"
2582 AND "event" = 'periodic';
2583 INSERT INTO "direct_interest_snapshot"
2584 ("issue_id", "event", "member_id", "voting_requested")
2585 SELECT
2586 "issue_id_p" AS "issue_id",
2587 'periodic' AS "event",
2588 "member"."id" AS "member_id",
2589 "interest"."voting_requested"
2590 FROM "issue"
2591 JOIN "area" ON "issue"."area_id" = "area"."id"
2592 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
2593 JOIN "member" ON "interest"."member_id" = "member"."id"
2594 JOIN "privilege"
2595 ON "privilege"."unit_id" = "area"."unit_id"
2596 AND "privilege"."member_id" = "member"."id"
2597 WHERE "issue"."id" = "issue_id_p"
2598 AND "member"."active" AND "privilege"."voting_right";
2599 FOR "member_id_v" IN
2600 SELECT "member_id" FROM "direct_interest_snapshot"
2601 WHERE "issue_id" = "issue_id_p"
2602 AND "event" = 'periodic'
2603 LOOP
2604 UPDATE "direct_interest_snapshot" SET
2605 "weight" = 1 +
2606 "weight_of_added_delegations_for_interest_snapshot"(
2607 "issue_id_p",
2608 "member_id_v",
2609 '{}'
2610 )
2611 WHERE "issue_id" = "issue_id_p"
2612 AND "event" = 'periodic'
2613 AND "member_id" = "member_id_v";
2614 END LOOP;
2615 INSERT INTO "direct_supporter_snapshot"
2616 ( "issue_id", "initiative_id", "event", "member_id",
2617 "informed", "satisfied" )
2618 SELECT
2619 "issue_id_p" AS "issue_id",
2620 "initiative"."id" AS "initiative_id",
2621 'periodic' AS "event",
2622 "supporter"."member_id" AS "member_id",
2623 "supporter"."draft_id" = "current_draft"."id" AS "informed",
2624 NOT EXISTS (
2625 SELECT NULL FROM "critical_opinion"
2626 WHERE "initiative_id" = "initiative"."id"
2627 AND "member_id" = "supporter"."member_id"
2628 ) AS "satisfied"
2629 FROM "initiative"
2630 JOIN "supporter"
2631 ON "supporter"."initiative_id" = "initiative"."id"
2632 JOIN "current_draft"
2633 ON "initiative"."id" = "current_draft"."initiative_id"
2634 JOIN "direct_interest_snapshot"
2635 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
2636 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
2637 AND "event" = 'periodic'
2638 WHERE "initiative"."issue_id" = "issue_id_p";
2639 RETURN;
2640 END;
2641 $$;
2643 COMMENT ON FUNCTION "create_interest_snapshot"
2644 ( "issue"."id"%TYPE )
2645 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.';
2648 CREATE FUNCTION "create_snapshot"
2649 ( "issue_id_p" "issue"."id"%TYPE )
2650 RETURNS VOID
2651 LANGUAGE 'plpgsql' VOLATILE AS $$
2652 DECLARE
2653 "initiative_id_v" "initiative"."id"%TYPE;
2654 "suggestion_id_v" "suggestion"."id"%TYPE;
2655 BEGIN
2656 PERFORM "lock_issue"("issue_id_p");
2657 PERFORM "create_population_snapshot"("issue_id_p");
2658 PERFORM "create_interest_snapshot"("issue_id_p");
2659 UPDATE "issue" SET
2660 "snapshot" = now(),
2661 "latest_snapshot_event" = 'periodic',
2662 "population" = (
2663 SELECT coalesce(sum("weight"), 0)
2664 FROM "direct_population_snapshot"
2665 WHERE "issue_id" = "issue_id_p"
2666 AND "event" = 'periodic'
2667 ),
2668 "vote_now" = (
2669 SELECT coalesce(sum("weight"), 0)
2670 FROM "direct_interest_snapshot"
2671 WHERE "issue_id" = "issue_id_p"
2672 AND "event" = 'periodic'
2673 AND "voting_requested" = TRUE
2674 ),
2675 "vote_later" = (
2676 SELECT coalesce(sum("weight"), 0)
2677 FROM "direct_interest_snapshot"
2678 WHERE "issue_id" = "issue_id_p"
2679 AND "event" = 'periodic'
2680 AND "voting_requested" = FALSE
2681 )
2682 WHERE "id" = "issue_id_p";
2683 FOR "initiative_id_v" IN
2684 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
2685 LOOP
2686 UPDATE "initiative" SET
2687 "supporter_count" = (
2688 SELECT coalesce(sum("di"."weight"), 0)
2689 FROM "direct_interest_snapshot" AS "di"
2690 JOIN "direct_supporter_snapshot" AS "ds"
2691 ON "di"."member_id" = "ds"."member_id"
2692 WHERE "di"."issue_id" = "issue_id_p"
2693 AND "di"."event" = 'periodic'
2694 AND "ds"."initiative_id" = "initiative_id_v"
2695 AND "ds"."event" = 'periodic'
2696 ),
2697 "informed_supporter_count" = (
2698 SELECT coalesce(sum("di"."weight"), 0)
2699 FROM "direct_interest_snapshot" AS "di"
2700 JOIN "direct_supporter_snapshot" AS "ds"
2701 ON "di"."member_id" = "ds"."member_id"
2702 WHERE "di"."issue_id" = "issue_id_p"
2703 AND "di"."event" = 'periodic'
2704 AND "ds"."initiative_id" = "initiative_id_v"
2705 AND "ds"."event" = 'periodic'
2706 AND "ds"."informed"
2707 ),
2708 "satisfied_supporter_count" = (
2709 SELECT coalesce(sum("di"."weight"), 0)
2710 FROM "direct_interest_snapshot" AS "di"
2711 JOIN "direct_supporter_snapshot" AS "ds"
2712 ON "di"."member_id" = "ds"."member_id"
2713 WHERE "di"."issue_id" = "issue_id_p"
2714 AND "di"."event" = 'periodic'
2715 AND "ds"."initiative_id" = "initiative_id_v"
2716 AND "ds"."event" = 'periodic'
2717 AND "ds"."satisfied"
2718 ),
2719 "satisfied_informed_supporter_count" = (
2720 SELECT coalesce(sum("di"."weight"), 0)
2721 FROM "direct_interest_snapshot" AS "di"
2722 JOIN "direct_supporter_snapshot" AS "ds"
2723 ON "di"."member_id" = "ds"."member_id"
2724 WHERE "di"."issue_id" = "issue_id_p"
2725 AND "di"."event" = 'periodic'
2726 AND "ds"."initiative_id" = "initiative_id_v"
2727 AND "ds"."event" = 'periodic'
2728 AND "ds"."informed"
2729 AND "ds"."satisfied"
2730 )
2731 WHERE "id" = "initiative_id_v";
2732 FOR "suggestion_id_v" IN
2733 SELECT "id" FROM "suggestion"
2734 WHERE "initiative_id" = "initiative_id_v"
2735 LOOP
2736 UPDATE "suggestion" SET
2737 "minus2_unfulfilled_count" = (
2738 SELECT coalesce(sum("snapshot"."weight"), 0)
2739 FROM "issue" CROSS JOIN "opinion"
2740 JOIN "direct_interest_snapshot" AS "snapshot"
2741 ON "snapshot"."issue_id" = "issue"."id"
2742 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2743 AND "snapshot"."member_id" = "opinion"."member_id"
2744 WHERE "issue"."id" = "issue_id_p"
2745 AND "opinion"."suggestion_id" = "suggestion_id_v"
2746 AND "opinion"."degree" = -2
2747 AND "opinion"."fulfilled" = FALSE
2748 ),
2749 "minus2_fulfilled_count" = (
2750 SELECT coalesce(sum("snapshot"."weight"), 0)
2751 FROM "issue" CROSS JOIN "opinion"
2752 JOIN "direct_interest_snapshot" AS "snapshot"
2753 ON "snapshot"."issue_id" = "issue"."id"
2754 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2755 AND "snapshot"."member_id" = "opinion"."member_id"
2756 WHERE "issue"."id" = "issue_id_p"
2757 AND "opinion"."suggestion_id" = "suggestion_id_v"
2758 AND "opinion"."degree" = -2
2759 AND "opinion"."fulfilled" = TRUE
2760 ),
2761 "minus1_unfulfilled_count" = (
2762 SELECT coalesce(sum("snapshot"."weight"), 0)
2763 FROM "issue" CROSS JOIN "opinion"
2764 JOIN "direct_interest_snapshot" AS "snapshot"
2765 ON "snapshot"."issue_id" = "issue"."id"
2766 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2767 AND "snapshot"."member_id" = "opinion"."member_id"
2768 WHERE "issue"."id" = "issue_id_p"
2769 AND "opinion"."suggestion_id" = "suggestion_id_v"
2770 AND "opinion"."degree" = -1
2771 AND "opinion"."fulfilled" = FALSE
2772 ),
2773 "minus1_fulfilled_count" = (
2774 SELECT coalesce(sum("snapshot"."weight"), 0)
2775 FROM "issue" CROSS JOIN "opinion"
2776 JOIN "direct_interest_snapshot" AS "snapshot"
2777 ON "snapshot"."issue_id" = "issue"."id"
2778 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2779 AND "snapshot"."member_id" = "opinion"."member_id"
2780 WHERE "issue"."id" = "issue_id_p"
2781 AND "opinion"."suggestion_id" = "suggestion_id_v"
2782 AND "opinion"."degree" = -1
2783 AND "opinion"."fulfilled" = TRUE
2784 ),
2785 "plus1_unfulfilled_count" = (
2786 SELECT coalesce(sum("snapshot"."weight"), 0)
2787 FROM "issue" CROSS JOIN "opinion"
2788 JOIN "direct_interest_snapshot" AS "snapshot"
2789 ON "snapshot"."issue_id" = "issue"."id"
2790 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2791 AND "snapshot"."member_id" = "opinion"."member_id"
2792 WHERE "issue"."id" = "issue_id_p"
2793 AND "opinion"."suggestion_id" = "suggestion_id_v"
2794 AND "opinion"."degree" = 1
2795 AND "opinion"."fulfilled" = FALSE
2796 ),
2797 "plus1_fulfilled_count" = (
2798 SELECT coalesce(sum("snapshot"."weight"), 0)
2799 FROM "issue" CROSS JOIN "opinion"
2800 JOIN "direct_interest_snapshot" AS "snapshot"
2801 ON "snapshot"."issue_id" = "issue"."id"
2802 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2803 AND "snapshot"."member_id" = "opinion"."member_id"
2804 WHERE "issue"."id" = "issue_id_p"
2805 AND "opinion"."suggestion_id" = "suggestion_id_v"
2806 AND "opinion"."degree" = 1
2807 AND "opinion"."fulfilled" = TRUE
2808 ),
2809 "plus2_unfulfilled_count" = (
2810 SELECT coalesce(sum("snapshot"."weight"), 0)
2811 FROM "issue" CROSS JOIN "opinion"
2812 JOIN "direct_interest_snapshot" AS "snapshot"
2813 ON "snapshot"."issue_id" = "issue"."id"
2814 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2815 AND "snapshot"."member_id" = "opinion"."member_id"
2816 WHERE "issue"."id" = "issue_id_p"
2817 AND "opinion"."suggestion_id" = "suggestion_id_v"
2818 AND "opinion"."degree" = 2
2819 AND "opinion"."fulfilled" = FALSE
2820 ),
2821 "plus2_fulfilled_count" = (
2822 SELECT coalesce(sum("snapshot"."weight"), 0)
2823 FROM "issue" CROSS JOIN "opinion"
2824 JOIN "direct_interest_snapshot" AS "snapshot"
2825 ON "snapshot"."issue_id" = "issue"."id"
2826 AND "snapshot"."event" = "issue"."latest_snapshot_event"
2827 AND "snapshot"."member_id" = "opinion"."member_id"
2828 WHERE "issue"."id" = "issue_id_p"
2829 AND "opinion"."suggestion_id" = "suggestion_id_v"
2830 AND "opinion"."degree" = 2
2831 AND "opinion"."fulfilled" = TRUE
2832 )
2833 WHERE "suggestion"."id" = "suggestion_id_v";
2834 END LOOP;
2835 END LOOP;
2836 RETURN;
2837 END;
2838 $$;
2840 COMMENT ON FUNCTION "create_snapshot"
2841 ( "issue"."id"%TYPE )
2842 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.';
2845 CREATE FUNCTION "set_snapshot_event"
2846 ( "issue_id_p" "issue"."id"%TYPE,
2847 "event_p" "snapshot_event" )
2848 RETURNS VOID
2849 LANGUAGE 'plpgsql' VOLATILE AS $$
2850 DECLARE
2851 "event_v" "issue"."latest_snapshot_event"%TYPE;
2852 BEGIN
2853 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
2854 WHERE "id" = "issue_id_p" FOR UPDATE;
2855 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
2856 WHERE "id" = "issue_id_p";
2857 UPDATE "direct_population_snapshot" SET "event" = "event_p"
2858 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2859 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
2860 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2861 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
2862 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2863 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
2864 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2865 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
2866 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
2867 RETURN;
2868 END;
2869 $$;
2871 COMMENT ON FUNCTION "set_snapshot_event"
2872 ( "issue"."id"%TYPE,
2873 "snapshot_event" )
2874 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
2878 ---------------------
2879 -- Freezing issues --
2880 ---------------------
2882 CREATE FUNCTION "freeze_after_snapshot"
2883 ( "issue_id_p" "issue"."id"%TYPE )
2884 RETURNS VOID
2885 LANGUAGE 'plpgsql' VOLATILE AS $$
2886 DECLARE
2887 "issue_row" "issue"%ROWTYPE;
2888 "policy_row" "policy"%ROWTYPE;
2889 "initiative_row" "initiative"%ROWTYPE;
2890 BEGIN
2891 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
2892 SELECT * INTO "policy_row"
2893 FROM "policy" WHERE "id" = "issue_row"."policy_id";
2894 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
2895 UPDATE "issue" SET
2896 "state" = 'voting',
2897 "accepted" = coalesce("accepted", now()),
2898 "half_frozen" = coalesce("half_frozen", now()),
2899 "fully_frozen" = now()
2900 WHERE "id" = "issue_id_p";
2901 FOR "initiative_row" IN
2902 SELECT * FROM "initiative"
2903 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
2904 LOOP
2905 IF
2906 "initiative_row"."satisfied_supporter_count" > 0 AND
2907 "initiative_row"."satisfied_supporter_count" *
2908 "policy_row"."initiative_quorum_den" >=
2909 "issue_row"."population" * "policy_row"."initiative_quorum_num"
2910 THEN
2911 UPDATE "initiative" SET "admitted" = TRUE
2912 WHERE "id" = "initiative_row"."id";
2913 ELSE
2914 UPDATE "initiative" SET "admitted" = FALSE
2915 WHERE "id" = "initiative_row"."id";
2916 END IF;
2917 END LOOP;
2918 IF NOT EXISTS (
2919 SELECT NULL FROM "initiative"
2920 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
2921 ) THEN
2922 PERFORM "close_voting"("issue_id_p");
2923 END IF;
2924 RETURN;
2925 END;
2926 $$;
2928 COMMENT ON FUNCTION "freeze_after_snapshot"
2929 ( "issue"."id"%TYPE )
2930 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
2933 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
2934 RETURNS VOID
2935 LANGUAGE 'plpgsql' VOLATILE AS $$
2936 DECLARE
2937 "issue_row" "issue"%ROWTYPE;
2938 BEGIN
2939 PERFORM "create_snapshot"("issue_id_p");
2940 PERFORM "freeze_after_snapshot"("issue_id_p");
2941 RETURN;
2942 END;
2943 $$;
2945 COMMENT ON FUNCTION "manual_freeze"
2946 ( "issue"."id"%TYPE )
2947 IS 'Freeze an issue manually (fully) and start voting';
2951 -----------------------
2952 -- Counting of votes --
2953 -----------------------
2956 CREATE FUNCTION "weight_of_added_vote_delegations"
2957 ( "issue_id_p" "issue"."id"%TYPE,
2958 "member_id_p" "member"."id"%TYPE,
2959 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
2960 RETURNS "direct_voter"."weight"%TYPE
2961 LANGUAGE 'plpgsql' VOLATILE AS $$
2962 DECLARE
2963 "issue_delegation_row" "issue_delegation"%ROWTYPE;
2964 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
2965 "weight_v" INT4;
2966 "sub_weight_v" INT4;
2967 BEGIN
2968 "weight_v" := 0;
2969 FOR "issue_delegation_row" IN
2970 SELECT * FROM "issue_delegation"
2971 WHERE "trustee_id" = "member_id_p"
2972 AND "issue_id" = "issue_id_p"
2973 LOOP
2974 IF NOT EXISTS (
2975 SELECT NULL FROM "direct_voter"
2976 WHERE "member_id" = "issue_delegation_row"."truster_id"
2977 AND "issue_id" = "issue_id_p"
2978 ) AND NOT EXISTS (
2979 SELECT NULL FROM "delegating_voter"
2980 WHERE "member_id" = "issue_delegation_row"."truster_id"
2981 AND "issue_id" = "issue_id_p"
2982 ) THEN
2983 "delegate_member_ids_v" :=
2984 "member_id_p" || "delegate_member_ids_p";
2985 INSERT INTO "delegating_voter" (
2986 "issue_id",
2987 "member_id",
2988 "scope",
2989 "delegate_member_ids"
2990 ) VALUES (
2991 "issue_id_p",
2992 "issue_delegation_row"."truster_id",
2993 "issue_delegation_row"."scope",
2994 "delegate_member_ids_v"
2995 );
2996 "sub_weight_v" := 1 +
2997 "weight_of_added_vote_delegations"(
2998 "issue_id_p",
2999 "issue_delegation_row"."truster_id",
3000 "delegate_member_ids_v"
3001 );
3002 UPDATE "delegating_voter"
3003 SET "weight" = "sub_weight_v"
3004 WHERE "issue_id" = "issue_id_p"
3005 AND "member_id" = "issue_delegation_row"."truster_id";
3006 "weight_v" := "weight_v" + "sub_weight_v";
3007 END IF;
3008 END LOOP;
3009 RETURN "weight_v";
3010 END;
3011 $$;
3013 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
3014 ( "issue"."id"%TYPE,
3015 "member"."id"%TYPE,
3016 "delegating_voter"."delegate_member_ids"%TYPE )
3017 IS 'Helper function for "add_vote_delegations" function';
3020 CREATE FUNCTION "add_vote_delegations"
3021 ( "issue_id_p" "issue"."id"%TYPE )
3022 RETURNS VOID
3023 LANGUAGE 'plpgsql' VOLATILE AS $$
3024 DECLARE
3025 "member_id_v" "member"."id"%TYPE;
3026 BEGIN
3027 FOR "member_id_v" IN
3028 SELECT "member_id" FROM "direct_voter"
3029 WHERE "issue_id" = "issue_id_p"
3030 LOOP
3031 UPDATE "direct_voter" SET
3032 "weight" = "weight" + "weight_of_added_vote_delegations"(
3033 "issue_id_p",
3034 "member_id_v",
3035 '{}'
3036 )
3037 WHERE "member_id" = "member_id_v"
3038 AND "issue_id" = "issue_id_p";
3039 END LOOP;
3040 RETURN;
3041 END;
3042 $$;
3044 COMMENT ON FUNCTION "add_vote_delegations"
3045 ( "issue_id_p" "issue"."id"%TYPE )
3046 IS 'Helper function for "close_voting" function';
3049 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
3050 RETURNS VOID
3051 LANGUAGE 'plpgsql' VOLATILE AS $$
3052 DECLARE
3053 "area_id_v" "area"."id"%TYPE;
3054 "unit_id_v" "unit"."id"%TYPE;
3055 "member_id_v" "member"."id"%TYPE;
3056 BEGIN
3057 PERFORM "lock_issue"("issue_id_p");
3058 SELECT "id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
3059 SELECT "id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
3060 DELETE FROM "delegating_voter"
3061 WHERE "issue_id" = "issue_id_p";
3062 DELETE FROM "direct_voter"
3063 WHERE "issue_id" = "issue_id_p"
3064 AND "autoreject" = TRUE;
3065 DELETE FROM "direct_voter"
3066 USING (
3067 SELECT
3068 "direct_voter"."member_id"
3069 FROM "direct_voter"
3070 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
3071 LEFT JOIN "privilege"
3072 ON "privilege"."unit_id" = "unit_id_v"
3073 AND "privilege"."member_id" = "direct_voter"."member_id"
3074 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
3075 "member"."active" = FALSE OR
3076 "privilege"."voting_right" ISNULL OR
3077 "privilege"."voting_right" = FALSE
3078 )
3079 ) AS "subquery"
3080 WHERE "direct_voter"."issue_id" = "issue_id_p"
3081 AND "direct_voter"."member_id" = "subquery"."member_id";
3082 UPDATE "direct_voter" SET "weight" = 1
3083 WHERE "issue_id" = "issue_id_p";
3084 PERFORM "add_vote_delegations"("issue_id_p");
3085 FOR "member_id_v" IN
3086 SELECT "interest"."member_id"
3087 FROM "interest"
3088 JOIN "member"
3089 ON "interest"."member_id" = "member"."id"
3090 LEFT JOIN "direct_voter"
3091 ON "interest"."member_id" = "direct_voter"."member_id"
3092 AND "interest"."issue_id" = "direct_voter"."issue_id"
3093 LEFT JOIN "delegating_voter"
3094 ON "interest"."member_id" = "delegating_voter"."member_id"
3095 AND "interest"."issue_id" = "delegating_voter"."issue_id"
3096 WHERE "interest"."issue_id" = "issue_id_p"
3097 AND "interest"."autoreject" = TRUE
3098 AND "member"."active"
3099 AND "direct_voter"."member_id" ISNULL
3100 AND "delegating_voter"."member_id" ISNULL
3101 UNION SELECT "membership"."member_id"
3102 FROM "membership"
3103 JOIN "member"
3104 ON "membership"."member_id" = "member"."id"
3105 LEFT JOIN "interest"
3106 ON "membership"."member_id" = "interest"."member_id"
3107 AND "interest"."issue_id" = "issue_id_p"
3108 LEFT JOIN "direct_voter"
3109 ON "membership"."member_id" = "direct_voter"."member_id"
3110 AND "direct_voter"."issue_id" = "issue_id_p"
3111 LEFT JOIN "delegating_voter"
3112 ON "membership"."member_id" = "delegating_voter"."member_id"
3113 AND "delegating_voter"."issue_id" = "issue_id_p"
3114 WHERE "membership"."area_id" = "area_id_v"
3115 AND "membership"."autoreject" = TRUE
3116 AND "member"."active"
3117 AND "interest"."autoreject" ISNULL
3118 AND "direct_voter"."member_id" ISNULL
3119 AND "delegating_voter"."member_id" ISNULL
3120 LOOP
3121 INSERT INTO "direct_voter"
3122 ("member_id", "issue_id", "weight", "autoreject") VALUES
3123 ("member_id_v", "issue_id_p", 1, TRUE);
3124 INSERT INTO "vote" (
3125 "member_id",
3126 "issue_id",
3127 "initiative_id",
3128 "grade"
3129 ) SELECT
3130 "member_id_v" AS "member_id",
3131 "issue_id_p" AS "issue_id",
3132 "id" AS "initiative_id",
3133 -1 AS "grade"
3134 FROM "initiative" WHERE "issue_id" = "issue_id_p";
3135 END LOOP;
3136 PERFORM "add_vote_delegations"("issue_id_p");
3137 UPDATE "issue" SET
3138 "state" = 'calculation',
3139 "closed" = now(),
3140 "voter_count" = (
3141 SELECT coalesce(sum("weight"), 0)
3142 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
3143 )
3144 WHERE "id" = "issue_id_p";
3145 UPDATE "initiative" SET
3146 "positive_votes" = "vote_counts"."positive_votes",
3147 "negative_votes" = "vote_counts"."negative_votes",
3148 "agreed" = CASE WHEN "majority_strict" THEN
3149 "vote_counts"."positive_votes" * "majority_den" >
3150 "majority_num" *
3151 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
3152 ELSE
3153 "vote_counts"."positive_votes" * "majority_den" >=
3154 "majority_num" *
3155 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
3156 END
3157 FROM
3158 ( SELECT
3159 "initiative"."id" AS "initiative_id",
3160 coalesce(
3161 sum(
3162 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
3163 ),
3164 0
3165 ) AS "positive_votes",
3166 coalesce(
3167 sum(
3168 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
3169 ),
3170 0
3171 ) AS "negative_votes"
3172 FROM "initiative"
3173 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
3174 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
3175 LEFT JOIN "direct_voter"
3176 ON "direct_voter"."issue_id" = "initiative"."issue_id"
3177 LEFT JOIN "vote"
3178 ON "vote"."initiative_id" = "initiative"."id"
3179 AND "vote"."member_id" = "direct_voter"."member_id"
3180 WHERE "initiative"."issue_id" = "issue_id_p"
3181 AND "initiative"."admitted" -- NOTE: NULL case is handled too
3182 GROUP BY "initiative"."id"
3183 ) AS "vote_counts",
3184 "issue",
3185 "policy"
3186 WHERE "vote_counts"."initiative_id" = "initiative"."id"
3187 AND "issue"."id" = "initiative"."issue_id"
3188 AND "policy"."id" = "issue"."policy_id";
3189 -- NOTE: "closed" column of issue must be set at this point
3190 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3191 INSERT INTO "battle" (
3192 "issue_id",
3193 "winning_initiative_id", "losing_initiative_id",
3194 "count"
3195 ) SELECT
3196 "issue_id",
3197 "winning_initiative_id", "losing_initiative_id",
3198 "count"
3199 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
3200 END;
3201 $$;
3203 COMMENT ON FUNCTION "close_voting"
3204 ( "issue"."id"%TYPE )
3205 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.';
3208 CREATE FUNCTION "defeat_strength"
3209 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
3210 RETURNS INT8
3211 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3212 BEGIN
3213 IF "positive_votes_p" > "negative_votes_p" THEN
3214 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
3215 ELSIF "positive_votes_p" = "negative_votes_p" THEN
3216 RETURN 0;
3217 ELSE
3218 RETURN -1;
3219 END IF;
3220 END;
3221 $$;
3223 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';
3226 CREATE FUNCTION "array_init_string"("dim_p" INTEGER)
3227 RETURNS TEXT
3228 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3229 DECLARE
3230 "i" INTEGER;
3231 "ary_text_v" TEXT;
3232 BEGIN
3233 IF "dim_p" >= 1 THEN
3234 "ary_text_v" := '{NULL';
3235 "i" := "dim_p";
3236 LOOP
3237 "i" := "i" - 1;
3238 EXIT WHEN "i" = 0;
3239 "ary_text_v" := "ary_text_v" || ',NULL';
3240 END LOOP;
3241 "ary_text_v" := "ary_text_v" || '}';
3242 RETURN "ary_text_v";
3243 ELSE
3244 RAISE EXCEPTION 'Dimension needs to be at least 1.';
3245 END IF;
3246 END;
3247 $$;
3249 COMMENT ON FUNCTION "array_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
3252 CREATE FUNCTION "square_matrix_init_string"("dim_p" INTEGER)
3253 RETURNS TEXT
3254 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3255 DECLARE
3256 "i" INTEGER;
3257 "row_text_v" TEXT;
3258 "ary_text_v" TEXT;
3259 BEGIN
3260 IF "dim_p" >= 1 THEN
3261 "row_text_v" := '{NULL';
3262 "i" := "dim_p";
3263 LOOP
3264 "i" := "i" - 1;
3265 EXIT WHEN "i" = 0;
3266 "row_text_v" := "row_text_v" || ',NULL';
3267 END LOOP;
3268 "row_text_v" := "row_text_v" || '}';
3269 "ary_text_v" := '{' || "row_text_v";
3270 "i" := "dim_p";
3271 LOOP
3272 "i" := "i" - 1;
3273 EXIT WHEN "i" = 0;
3274 "ary_text_v" := "ary_text_v" || ',' || "row_text_v";
3275 END LOOP;
3276 "ary_text_v" := "ary_text_v" || '}';
3277 RETURN "ary_text_v";
3278 ELSE
3279 RAISE EXCEPTION 'Dimension needs to be at least 1.';
3280 END IF;
3281 END;
3282 $$;
3284 COMMENT ON FUNCTION "square_matrix_init_string"(INTEGER) IS 'Needed for PostgreSQL < 8.4, due to missing "array_fill" function';
3287 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
3288 RETURNS VOID
3289 LANGUAGE 'plpgsql' VOLATILE AS $$
3290 DECLARE
3291 "dimension_v" INTEGER;
3292 "vote_matrix" INT4[][]; -- absolute votes
3293 "matrix" INT8[][]; -- defeat strength / best paths
3294 "i" INTEGER;
3295 "j" INTEGER;
3296 "k" INTEGER;
3297 "battle_row" "battle"%ROWTYPE;
3298 "rank_ary" INT4[];
3299 "rank_v" INT4;
3300 "done_v" INTEGER;
3301 "winners_ary" INTEGER[];
3302 "initiative_id_v" "initiative"."id"%TYPE;
3303 BEGIN
3304 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
3305 SELECT count(1) INTO "dimension_v" FROM "initiative"
3306 WHERE "issue_id" = "issue_id_p" AND "agreed";
3307 IF "dimension_v" = 1 THEN
3308 UPDATE "initiative" SET "rank" = 1
3309 WHERE "issue_id" = "issue_id_p" AND "agreed";
3310 ELSIF "dimension_v" > 1 THEN
3311 -- Create "vote_matrix" with absolute number of votes in pairwise
3312 -- comparison:
3313 "vote_matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
3314 "i" := 1;
3315 "j" := 2;
3316 FOR "battle_row" IN
3317 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
3318 ORDER BY "winning_initiative_id", "losing_initiative_id"
3319 LOOP
3320 "vote_matrix"["i"]["j"] := "battle_row"."count";
3321 IF "j" = "dimension_v" THEN
3322 "i" := "i" + 1;
3323 "j" := 1;
3324 ELSE
3325 "j" := "j" + 1;
3326 IF "j" = "i" THEN
3327 "j" := "j" + 1;
3328 END IF;
3329 END IF;
3330 END LOOP;
3331 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
3332 RAISE EXCEPTION 'Wrong battle count (should not happen)';
3333 END IF;
3334 -- Store defeat strengths in "matrix" using "defeat_strength"
3335 -- function:
3336 "matrix" := "square_matrix_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
3337 "i" := 1;
3338 LOOP
3339 "j" := 1;
3340 LOOP
3341 IF "i" != "j" THEN
3342 "matrix"["i"]["j"] := "defeat_strength"(
3343 "vote_matrix"["i"]["j"],
3344 "vote_matrix"["j"]["i"]
3345 );
3346 END IF;
3347 EXIT WHEN "j" = "dimension_v";
3348 "j" := "j" + 1;
3349 END LOOP;
3350 EXIT WHEN "i" = "dimension_v";
3351 "i" := "i" + 1;
3352 END LOOP;
3353 -- Find best paths:
3354 "i" := 1;
3355 LOOP
3356 "j" := 1;
3357 LOOP
3358 IF "i" != "j" THEN
3359 "k" := 1;
3360 LOOP
3361 IF "i" != "k" AND "j" != "k" THEN
3362 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
3363 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
3364 "matrix"["j"]["k"] := "matrix"["j"]["i"];
3365 END IF;
3366 ELSE
3367 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
3368 "matrix"["j"]["k"] := "matrix"["i"]["k"];
3369 END IF;
3370 END IF;
3371 END IF;
3372 EXIT WHEN "k" = "dimension_v";
3373 "k" := "k" + 1;
3374 END LOOP;
3375 END IF;
3376 EXIT WHEN "j" = "dimension_v";
3377 "j" := "j" + 1;
3378 END LOOP;
3379 EXIT WHEN "i" = "dimension_v";
3380 "i" := "i" + 1;
3381 END LOOP;
3382 -- Determine order of winners:
3383 "rank_ary" := "array_init_string"("dimension_v"); -- TODO: replace by "array_fill" function (PostgreSQL 8.4)
3384 "rank_v" := 1;
3385 "done_v" := 0;
3386 LOOP
3387 "winners_ary" := '{}';
3388 "i" := 1;
3389 LOOP
3390 IF "rank_ary"["i"] ISNULL THEN
3391 "j" := 1;
3392 LOOP
3393 IF
3394 "i" != "j" AND
3395 "rank_ary"["j"] ISNULL AND
3396 "matrix"["j"]["i"] > "matrix"["i"]["j"]
3397 THEN
3398 -- someone else is better
3399 EXIT;
3400 END IF;
3401 IF "j" = "dimension_v" THEN
3402 -- noone is better
3403 "winners_ary" := "winners_ary" || "i";
3404 EXIT;
3405 END IF;
3406 "j" := "j" + 1;
3407 END LOOP;
3408 END IF;
3409 EXIT WHEN "i" = "dimension_v";
3410 "i" := "i" + 1;
3411 END LOOP;
3412 "i" := 1;
3413 LOOP
3414 "rank_ary"["winners_ary"["i"]] := "rank_v";
3415 "done_v" := "done_v" + 1;
3416 EXIT WHEN "i" = array_upper("winners_ary", 1);
3417 "i" := "i" + 1;
3418 END LOOP;
3419 EXIT WHEN "done_v" = "dimension_v";
3420 "rank_v" := "rank_v" + 1;
3421 END LOOP;
3422 -- write preliminary ranks:
3423 "i" := 1;
3424 FOR "initiative_id_v" IN
3425 SELECT "id" FROM "initiative"
3426 WHERE "issue_id" = "issue_id_p" AND "agreed"
3427 ORDER BY "id"
3428 LOOP
3429 UPDATE "initiative" SET "rank" = "rank_ary"["i"]
3430 WHERE "id" = "initiative_id_v";
3431 "i" := "i" + 1;
3432 END LOOP;
3433 IF "i" != "dimension_v" + 1 THEN
3434 RAISE EXCEPTION 'Wrong winner count (should not happen)';
3435 END IF;
3436 -- straighten ranks (start counting with 1, no equal ranks):
3437 "rank_v" := 1;
3438 FOR "initiative_id_v" IN
3439 SELECT "id" FROM "initiative"
3440 WHERE "issue_id" = "issue_id_p" AND "rank" NOTNULL
3441 ORDER BY
3442 "rank",
3443 "vote_ratio"("positive_votes", "negative_votes") DESC,
3444 "id"
3445 LOOP
3446 UPDATE "initiative" SET "rank" = "rank_v"
3447 WHERE "id" = "initiative_id_v";
3448 "rank_v" := "rank_v" + 1;
3449 END LOOP;
3450 END IF;
3451 -- mark issue as finished
3452 UPDATE "issue" SET
3453 "state" =
3454 CASE WHEN NOT EXISTS (
3455 SELECT NULL FROM "initiative"
3456 WHERE "issue_id" = "issue_id_p" AND "admitted"
3457 ) THEN
3458 'canceled_no_initiative_admitted'::"issue_state"
3459 ELSE
3460 CASE WHEN "dimension_v" = 0 THEN
3461 'finished_without_winner'::"issue_state"
3462 ELSE
3463 'finished_with_winner'::"issue_state"
3464 END
3465 END,
3466 "ranks_available" = TRUE
3467 WHERE "id" = "issue_id_p";
3468 RETURN;
3469 END;
3470 $$;
3472 COMMENT ON FUNCTION "calculate_ranks"
3473 ( "issue"."id"%TYPE )
3474 IS 'Determine ranking (Votes have to be counted first)';
3478 -----------------------------
3479 -- Automatic state changes --
3480 -----------------------------
3483 CREATE FUNCTION "check_issue"
3484 ( "issue_id_p" "issue"."id"%TYPE )
3485 RETURNS VOID
3486 LANGUAGE 'plpgsql' VOLATILE AS $$
3487 DECLARE
3488 "issue_row" "issue"%ROWTYPE;
3489 "policy_row" "policy"%ROWTYPE;
3490 "voting_requested_v" BOOLEAN;
3491 BEGIN
3492 PERFORM "lock_issue"("issue_id_p");
3493 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3494 -- only process open issues:
3495 IF "issue_row"."closed" ISNULL THEN
3496 SELECT * INTO "policy_row" FROM "policy"
3497 WHERE "id" = "issue_row"."policy_id";
3498 -- create a snapshot, unless issue is already fully frozen:
3499 IF "issue_row"."fully_frozen" ISNULL THEN
3500 PERFORM "create_snapshot"("issue_id_p");
3501 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3502 END IF;
3503 -- eventually close or accept issues, which have not been accepted:
3504 IF "issue_row"."accepted" ISNULL THEN
3505 IF EXISTS (
3506 SELECT NULL FROM "initiative"
3507 WHERE "issue_id" = "issue_id_p"
3508 AND "supporter_count" > 0
3509 AND "supporter_count" * "policy_row"."issue_quorum_den"
3510 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
3511 ) THEN
3512 -- accept issues, if supporter count is high enough
3513 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3514 -- NOTE: "issue_row" used later
3515 "issue_row"."state" := 'discussion';
3516 "issue_row"."accepted" := now();
3517 UPDATE "issue" SET
3518 "state" = "issue_row"."state",
3519 "accepted" = "issue_row"."accepted"
3520 WHERE "id" = "issue_row"."id";
3521 ELSIF
3522 now() >= "issue_row"."created" + "issue_row"."admission_time"
3523 THEN
3524 -- close issues, if admission time has expired
3525 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
3526 UPDATE "issue" SET
3527 "state" = 'canceled_issue_not_accepted',
3528 "closed" = now()
3529 WHERE "id" = "issue_row"."id";
3530 END IF;
3531 END IF;
3532 -- eventually half freeze issues:
3533 IF
3534 -- NOTE: issue can't be closed at this point, if it has been accepted
3535 "issue_row"."accepted" NOTNULL AND
3536 "issue_row"."half_frozen" ISNULL
3537 THEN
3538 SELECT
3539 CASE
3540 WHEN "vote_now" * 2 > "issue_row"."population" THEN
3541 TRUE
3542 WHEN "vote_later" * 2 > "issue_row"."population" THEN
3543 FALSE
3544 ELSE NULL
3545 END
3546 INTO "voting_requested_v"
3547 FROM "issue" WHERE "id" = "issue_id_p";
3548 IF
3549 "voting_requested_v" OR (
3550 "voting_requested_v" ISNULL AND
3551 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
3552 )
3553 THEN
3554 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
3555 -- NOTE: "issue_row" used later
3556 "issue_row"."state" := 'verification';
3557 "issue_row"."half_frozen" := now();
3558 UPDATE "issue" SET
3559 "state" = "issue_row"."state",
3560 "half_frozen" = "issue_row"."half_frozen"
3561 WHERE "id" = "issue_row"."id";
3562 END IF;
3563 END IF;
3564 -- close issues after some time, if all initiatives have been revoked:
3565 IF
3566 "issue_row"."closed" ISNULL AND
3567 NOT EXISTS (
3568 -- all initiatives are revoked
3569 SELECT NULL FROM "initiative"
3570 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3571 ) AND (
3572 -- and issue has not been accepted yet
3573 "issue_row"."accepted" ISNULL OR
3574 NOT EXISTS (
3575 -- or no initiatives have been revoked lately
3576 SELECT NULL FROM "initiative"
3577 WHERE "issue_id" = "issue_id_p"
3578 AND now() < "revoked" + "issue_row"."verification_time"
3579 ) OR (
3580 -- or verification time has elapsed
3581 "issue_row"."half_frozen" NOTNULL AND
3582 "issue_row"."fully_frozen" ISNULL AND
3583 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3584 )
3585 )
3586 THEN
3587 -- NOTE: "issue_row" used later
3588 "issue_row"."state" := 'canceled_all_initiatives_revoked';
3589 "issue_row"."closed" := now();
3590 UPDATE "issue" SET
3591 "state" = "issue_row"."state",
3592 "closed" = "issue_row"."closed"
3593 WHERE "id" = "issue_row"."id";
3594 END IF;
3595 -- fully freeze issue after verification time:
3596 IF
3597 "issue_row"."half_frozen" NOTNULL AND
3598 "issue_row"."fully_frozen" ISNULL AND
3599 "issue_row"."closed" ISNULL AND
3600 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
3601 THEN
3602 PERFORM "freeze_after_snapshot"("issue_id_p");
3603 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
3604 END IF;
3605 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3606 -- close issue by calling close_voting(...) after voting time:
3607 IF
3608 "issue_row"."closed" ISNULL AND
3609 "issue_row"."fully_frozen" NOTNULL AND
3610 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
3611 THEN
3612 PERFORM "close_voting"("issue_id_p");
3613 -- calculate ranks will not consume much time and can be done now
3614 PERFORM "calculate_ranks"("issue_id_p");
3615 END IF;
3616 END IF;
3617 RETURN;
3618 END;
3619 $$;
3621 COMMENT ON FUNCTION "check_issue"
3622 ( "issue"."id"%TYPE )
3623 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.';
3626 CREATE FUNCTION "check_everything"()
3627 RETURNS VOID
3628 LANGUAGE 'plpgsql' VOLATILE AS $$
3629 DECLARE
3630 "issue_id_v" "issue"."id"%TYPE;
3631 BEGIN
3632 DELETE FROM "expired_session";
3633 PERFORM "check_last_login"();
3634 PERFORM "calculate_member_counts"();
3635 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
3636 PERFORM "check_issue"("issue_id_v");
3637 END LOOP;
3638 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
3639 PERFORM "calculate_ranks"("issue_id_v");
3640 END LOOP;
3641 RETURN;
3642 END;
3643 $$;
3645 COMMENT ON FUNCTION "check_everything"() IS 'Amongst other regular tasks this function performs "check_issue" for every open issue, and if possible, automatically calculates ranks. Use this function only for development and debugging purposes, as long transactions with exclusive locking may result. In productive environments you should call the lf_update program instead.';
3649 ----------------------
3650 -- Deletion of data --
3651 ----------------------
3654 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
3655 RETURNS VOID
3656 LANGUAGE 'plpgsql' VOLATILE AS $$
3657 DECLARE
3658 "issue_row" "issue"%ROWTYPE;
3659 BEGIN
3660 SELECT * INTO "issue_row"
3661 FROM "issue" WHERE "id" = "issue_id_p"
3662 FOR UPDATE;
3663 IF "issue_row"."cleaned" ISNULL THEN
3664 UPDATE "issue" SET
3665 "closed" = NULL,
3666 "ranks_available" = FALSE
3667 WHERE "id" = "issue_id_p";
3668 DELETE FROM "delegating_voter"
3669 WHERE "issue_id" = "issue_id_p";
3670 DELETE FROM "direct_voter"
3671 WHERE "issue_id" = "issue_id_p";
3672 DELETE FROM "delegating_interest_snapshot"
3673 WHERE "issue_id" = "issue_id_p";
3674 DELETE FROM "direct_interest_snapshot"
3675 WHERE "issue_id" = "issue_id_p";
3676 DELETE FROM "delegating_population_snapshot"
3677 WHERE "issue_id" = "issue_id_p";
3678 DELETE FROM "direct_population_snapshot"
3679 WHERE "issue_id" = "issue_id_p";
3680 DELETE FROM "ignored_issue"
3681 WHERE "issue_id" = "issue_id_p";
3682 DELETE FROM "delegation"
3683 WHERE "issue_id" = "issue_id_p";
3684 DELETE FROM "supporter"
3685 WHERE "issue_id" = "issue_id_p";
3686 UPDATE "issue" SET
3687 "closed" = "issue_row"."closed",
3688 "ranks_available" = "issue_row"."ranks_available",
3689 "cleaned" = now()
3690 WHERE "id" = "issue_id_p";
3691 END IF;
3692 RETURN;
3693 END;
3694 $$;
3696 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
3699 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
3700 RETURNS VOID
3701 LANGUAGE 'plpgsql' VOLATILE AS $$
3702 BEGIN
3703 UPDATE "member" SET
3704 "last_login" = NULL,
3705 "last_login_public" = NULL,
3706 "login" = NULL,
3707 "password" = NULL,
3708 "locked" = TRUE,
3709 "active" = FALSE,
3710 "notify_email" = NULL,
3711 "notify_email_unconfirmed" = NULL,
3712 "notify_email_secret" = NULL,
3713 "notify_email_secret_expiry" = NULL,
3714 "notify_email_lock_expiry" = NULL,
3715 "password_reset_secret" = NULL,
3716 "password_reset_secret_expiry" = NULL,
3717 "organizational_unit" = NULL,
3718 "internal_posts" = NULL,
3719 "realname" = NULL,
3720 "birthday" = NULL,
3721 "address" = NULL,
3722 "email" = NULL,
3723 "xmpp_address" = NULL,
3724 "website" = NULL,
3725 "phone" = NULL,
3726 "mobile_phone" = NULL,
3727 "profession" = NULL,
3728 "external_memberships" = NULL,
3729 "external_posts" = NULL,
3730 "statement" = NULL
3731 WHERE "id" = "member_id_p";
3732 -- "text_search_data" is updated by triggers
3733 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
3734 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
3735 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
3736 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
3737 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
3738 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
3739 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
3740 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
3741 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
3742 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
3743 DELETE FROM "ignored_issue" WHERE "member_id" = "member_id_p";
3744 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
3745 DELETE FROM "direct_voter" USING "issue"
3746 WHERE "direct_voter"."issue_id" = "issue"."id"
3747 AND "issue"."closed" ISNULL
3748 AND "member_id" = "member_id_p";
3749 RETURN;
3750 END;
3751 $$;
3753 COMMENT ON FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE) IS 'Deactivate member and clear certain settings and data of this member (data protection)';
3756 CREATE FUNCTION "delete_private_data"()
3757 RETURNS VOID
3758 LANGUAGE 'plpgsql' VOLATILE AS $$
3759 BEGIN
3760 UPDATE "member" SET
3761 "last_login" = NULL,
3762 "login" = NULL,
3763 "password" = NULL,
3764 "notify_email" = NULL,
3765 "notify_email_unconfirmed" = NULL,
3766 "notify_email_secret" = NULL,
3767 "notify_email_secret_expiry" = NULL,
3768 "notify_email_lock_expiry" = NULL,
3769 "password_reset_secret" = NULL,
3770 "password_reset_secret_expiry" = NULL,
3771 "organizational_unit" = NULL,
3772 "internal_posts" = NULL,
3773 "realname" = NULL,
3774 "birthday" = NULL,
3775 "address" = NULL,
3776 "email" = NULL,
3777 "xmpp_address" = NULL,
3778 "website" = NULL,
3779 "phone" = NULL,
3780 "mobile_phone" = NULL,
3781 "profession" = NULL,
3782 "external_memberships" = NULL,
3783 "external_posts" = NULL,
3784 "statement" = NULL;
3785 -- "text_search_data" is updated by triggers
3786 DELETE FROM "invite_code";
3787 DELETE FROM "setting";
3788 DELETE FROM "setting_map";
3789 DELETE FROM "member_relation_setting";
3790 DELETE FROM "member_image";
3791 DELETE FROM "contact";
3792 DELETE FROM "session";
3793 DELETE FROM "area_setting";
3794 DELETE FROM "issue_setting";
3795 DELETE FROM "initiative_setting";
3796 DELETE FROM "suggestion_setting";
3797 DELETE FROM "ignored_issue";
3798 DELETE FROM "direct_voter" USING "issue"
3799 WHERE "direct_voter"."issue_id" = "issue"."id"
3800 AND "issue"."closed" ISNULL;
3801 RETURN;
3802 END;
3803 $$;
3805 COMMENT ON FUNCTION "delete_private_data"() IS 'Used by lf_export script. 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.';
3809 COMMIT;