liquid_feedback_core
view core.sql @ 267:5bf2c973ec3c
Work on OAuth 2.0 authorization support
| author | jbe |
|---|---|
| date | Wed Aug 08 15:20:34 2012 +0200 (2012-08-08) |
| parents | d1d5dca15491 |
| children | 739ed2d3d372 |
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 ('2.1.0', 2, 1, 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 show any activity.';
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 TYPE "notify_level" AS ENUM
79 ('none', 'voting', 'verification', 'discussion', 'all');
81 COMMENT ON TYPE "notify_level" IS 'Level of notification: ''none'' = no notifications, ''voting'' = notifications about finished issues and issues in voting, ''verification'' = notifications about finished issues, issues in voting and verification phase, ''discussion'' = notifications about everything except issues in admission phase, ''all'' = notifications about everything';
84 CREATE TABLE "member" (
85 "id" SERIAL4 PRIMARY KEY,
86 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
87 "invite_code" TEXT UNIQUE,
88 "invite_code_expiry" TIMESTAMPTZ,
89 "admin_comment" TEXT,
90 "activated" TIMESTAMPTZ,
91 "last_activity" DATE,
92 "last_login" TIMESTAMPTZ,
93 "login" TEXT UNIQUE,
94 "password" TEXT,
95 "locked" BOOLEAN NOT NULL DEFAULT FALSE,
96 "active" BOOLEAN NOT NULL DEFAULT FALSE,
97 "admin" BOOLEAN NOT NULL DEFAULT FALSE,
98 "lang" TEXT,
99 "notify_email" TEXT,
100 "notify_email_unconfirmed" TEXT,
101 "notify_email_secret" TEXT UNIQUE,
102 "notify_email_secret_expiry" TIMESTAMPTZ,
103 "notify_email_lock_expiry" TIMESTAMPTZ,
104 "notify_level" "notify_level",
105 "password_reset_secret" TEXT UNIQUE,
106 "password_reset_secret_expiry" TIMESTAMPTZ,
107 "name" TEXT UNIQUE,
108 "identification" TEXT UNIQUE,
109 "authentication" TEXT,
110 "organizational_unit" TEXT,
111 "internal_posts" TEXT,
112 "realname" TEXT,
113 "birthday" DATE,
114 "address" TEXT,
115 "email" TEXT,
116 "xmpp_address" TEXT,
117 "website" TEXT,
118 "phone" TEXT,
119 "mobile_phone" TEXT,
120 "profession" TEXT,
121 "external_memberships" TEXT,
122 "external_posts" TEXT,
123 "formatting_engine" TEXT,
124 "statement" TEXT,
125 "text_search_data" TSVECTOR,
126 CONSTRAINT "active_requires_activated_and_last_activity"
127 CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL)),
128 CONSTRAINT "name_not_null_if_activated"
129 CHECK ("activated" ISNULL OR "name" NOTNULL) );
130 CREATE INDEX "member_active_idx" ON "member" ("active");
131 CREATE INDEX "member_text_search_data_idx" ON "member" USING gin ("text_search_data");
132 CREATE TRIGGER "update_text_search_data"
133 BEFORE INSERT OR UPDATE ON "member"
134 FOR EACH ROW EXECUTE PROCEDURE
135 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
136 "name", "identification", "organizational_unit", "internal_posts",
137 "realname", "external_memberships", "external_posts", "statement" );
139 COMMENT ON TABLE "member" IS 'Users of the system, e.g. members of an organization';
141 COMMENT ON COLUMN "member"."created" IS 'Creation of member record and/or invite code';
142 COMMENT ON COLUMN "member"."invite_code" IS 'Optional invite code, to allow a member to initialize his/her account the first time';
143 COMMENT ON COLUMN "member"."invite_code_expiry" IS 'Expiry data/time for "invite_code"';
144 COMMENT ON COLUMN "member"."admin_comment" IS 'Hidden comment for administrative purposes';
145 COMMENT ON COLUMN "member"."activated" IS 'Timestamp of first activation of account (i.e. usage of "invite_code"); required to be set for "active" members';
146 COMMENT ON COLUMN "member"."last_activity" IS 'Date of last activity of member; required to be set for "active" members';
147 COMMENT ON COLUMN "member"."last_login" IS 'Timestamp of last login';
148 COMMENT ON COLUMN "member"."login" IS 'Login name';
149 COMMENT ON COLUMN "member"."password" IS 'Password (preferably as crypto-hash, depending on the frontend or access layer)';
150 COMMENT ON COLUMN "member"."locked" IS 'Locked members can not log in.';
151 COMMENT ON COLUMN "member"."active" IS 'Memberships, support and votes are taken into account when corresponding members are marked as active. Automatically set to FALSE, if "last_activity" is older than "system_setting"."member_ttl".';
152 COMMENT ON COLUMN "member"."admin" IS 'TRUE for admins, which can administrate other users and setup policies and areas';
153 COMMENT ON COLUMN "member"."lang" IS 'Language code of the preferred language of the member';
154 COMMENT ON COLUMN "member"."notify_email" IS 'Email address where notifications of the system are sent to';
155 COMMENT ON COLUMN "member"."notify_email_unconfirmed" IS 'Unconfirmed email address provided by the member to be copied into "notify_email" field after verification';
156 COMMENT ON COLUMN "member"."notify_email_secret" IS 'Secret sent to the address in "notify_email_unconformed"';
157 COMMENT ON COLUMN "member"."notify_email_secret_expiry" IS 'Expiry date/time for "notify_email_secret"';
158 COMMENT ON COLUMN "member"."notify_email_lock_expiry" IS 'Date/time until no further email confirmation mails may be sent (abuse protection)';
159 COMMENT ON COLUMN "member"."notify_level" IS 'Selects which event notifications are to be sent to the "notify_email" mail address, may be NULL if member did not make any selection yet';
160 COMMENT ON COLUMN "member"."name" IS 'Distinct name of the member, may be NULL if account has not been activated yet';
161 COMMENT ON COLUMN "member"."identification" IS 'Optional identification number or code of the member';
162 COMMENT ON COLUMN "member"."authentication" IS 'Information about how this member was authenticated';
163 COMMENT ON COLUMN "member"."organizational_unit" IS 'Branch or division of the organization the member belongs to';
164 COMMENT ON COLUMN "member"."internal_posts" IS 'Posts (offices) of the member inside the organization';
165 COMMENT ON COLUMN "member"."realname" IS 'Real name of the member, may be identical with "name"';
166 COMMENT ON COLUMN "member"."email" IS 'Published email address of the member; not used for system notifications';
167 COMMENT ON COLUMN "member"."external_memberships" IS 'Other organizations the member is involved in';
168 COMMENT ON COLUMN "member"."external_posts" IS 'Posts (offices) outside the organization';
169 COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"';
170 COMMENT ON COLUMN "member"."statement" IS 'Freely chosen text of the member for his/her profile';
173 CREATE TYPE "api_access_level" AS ENUM (
174 'none', 'anonymous', 'authors_pseudonymous', 'all_pseudonymous', 'everything', 'member' );
177 CREATE TABLE "api_client" (
178 "id" SERIAL8 PRIMARY KEY,
179 UNIQUE ("member_id", "name"),
180 UNIQUE ("member_id", "client_identifier"),
181 "member_id" INT4 REFERENCES "member" ("id")
182 ON DELETE CASCADE ON UPDATE CASCADE,
183 "name" TEXT NOT NULL,
184 "client_identifier" TEXT NOT NULL,
185 "client_secret" TEXT,
186 "redirection_endpoint" TEXT,
187 "public_access_level" "api_access_level",
188 "access_level" "api_access_level" NOT NULL,
189 "validity_period" INTERVAL NOT NULL,
190 "last_usage" TIMESTAMPTZ NOT NULL,
191 CONSTRAINT "public_access_level_set_if_and_only_if_system_client"
192 CHECK ("member_id" ISNULL OR "client_secret" NOTNULL OR "access_level"='none'),
193 CONSTRAINT "system_client_with_public_access_requires_secret"
194 CHECK ("member_id" ISNULL OR "client_secret" NOTNULL OR "public_access_level"='none'),
195 CONSTRAINT "member_client_requires_redirection_endpoint"
196 CHECK ("member_id" ISNULL OR "redirection_endpoint" NOTNULL) );
197 CREATE UNIQUE INDEX "api_client_non_member_name_idx" ON "api_client" ("name") WHERE "member_id" ISNULL;
198 CREATE UNIQUE INDEX "api_client_non_member_client_identifier_idx" ON "api_client" ("client_identifier") WHERE "member_id" ISNULL;
200 COMMENT ON TABLE "api_client" IS 'Registered OAuth2 client for a member';
202 COMMENT ON COLUMN "api_client"."member_id" IS 'Member, who registered the client for him/herself, or NULL for clients registered by administrator';
203 COMMENT ON COLUMN "api_client"."name" IS 'Name of the client as chosen by member or administrator';
204 COMMENT ON COLUMN "api_client"."client_identifier" IS 'OAuth2 client id';
205 COMMENT ON COLUMN "api_client"."client_secret" IS 'Secret for client authentication, enables OAuth2 Client Credentials Grant when set';
206 COMMENT ON COLUMN "api_client"."redirection_endpoint" IS 'OAuth2 redirection endpoint, must be set for clients registered by members';
207 COMMENT ON COLUMN "api_client"."access_level" IS 'For clients registered by administrator: access level for OAuth2 Client Credentials Grant; For clients registered by member: access level for OAuth ';
208 COMMENT ON COLUMN "api_client"."validity_period" IS 'Life time of an OAuth2 access token';
211 CREATE TABLE "api_access" (
212 "id" SERIAL8 PRIMARY KEY,
213 "api_client_id" INT8 NOT NULL REFERENCES "api_client" ("id")
214 ON DELETE CASCADE ON UPDATE CASCADE,
215 "member_id" INT4 REFERENCES "member" ("id")
216 ON DELETE CASCADE ON UPDATE CASCADE,
217 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
218 "authorization_code" TEXT,
219 "refreshed" TIMESTAMPTZ,
220 "refresh_token" TEXT,
221 CONSTRAINT "one_of_authorization_code_and_refresh_token_set"
222 CHECK ("authorization_code" NOTNULL OR "refresh_token" NOTNULL),
223 CONSTRAINT "refresh_token_if_and_only_if_refreshed"
224 CHECK ("refreshed" NOTNULL = "refresh_token" NOTNULL) );
226 COMMENT ON TABLE "api_access" IS 'Issued OAuth2 authorization codes and refresh tokens';
228 COMMENT ON COLUMN "api_access"."created" IS 'Date/time when authorization code (or first refresh token when there is no authorization code) has been created';
229 COMMENT ON COLUMN "api_access"."authorization_code" IS 'OAuth2 authorization code';
230 COMMENT ON COLUMN "api_access"."refreshed" IS 'Date/time of last refresh';
231 COMMENT ON COLUMN "api_access"."refresh_token" IS 'OAuth2 refresh token';
234 CREATE TABLE "member_history" (
235 "id" SERIAL8 PRIMARY KEY,
236 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
237 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
238 "active" BOOLEAN NOT NULL,
239 "name" TEXT NOT NULL );
240 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
242 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
244 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
245 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
248 CREATE TABLE "rendered_member_statement" (
249 PRIMARY KEY ("member_id", "format"),
250 "member_id" INT8 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
251 "format" TEXT,
252 "content" TEXT NOT NULL );
254 COMMENT ON TABLE "rendered_member_statement" IS 'This table may be used by frontends to cache "rendered" member statements (e.g. HTML output generated from wiki text)';
257 CREATE TABLE "setting" (
258 PRIMARY KEY ("member_id", "key"),
259 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
260 "key" TEXT NOT NULL,
261 "value" TEXT NOT NULL );
262 CREATE INDEX "setting_key_idx" ON "setting" ("key");
264 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
266 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
269 CREATE TABLE "setting_map" (
270 PRIMARY KEY ("member_id", "key", "subkey"),
271 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
272 "key" TEXT NOT NULL,
273 "subkey" TEXT NOT NULL,
274 "value" TEXT NOT NULL );
275 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
277 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
279 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
280 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
281 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
284 CREATE TABLE "member_relation_setting" (
285 PRIMARY KEY ("member_id", "key", "other_member_id"),
286 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
287 "key" TEXT NOT NULL,
288 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
289 "value" TEXT NOT NULL );
291 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
294 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
296 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
299 CREATE TABLE "member_image" (
300 PRIMARY KEY ("member_id", "image_type", "scaled"),
301 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
302 "image_type" "member_image_type",
303 "scaled" BOOLEAN,
304 "content_type" TEXT,
305 "data" BYTEA NOT NULL );
307 COMMENT ON TABLE "member_image" IS 'Images of members';
309 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
312 CREATE TABLE "member_count" (
313 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
314 "total_count" INT4 NOT NULL );
316 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';
318 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
319 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
322 CREATE TABLE "contact" (
323 PRIMARY KEY ("member_id", "other_member_id"),
324 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
325 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
326 "public" BOOLEAN NOT NULL DEFAULT FALSE,
327 CONSTRAINT "cant_save_yourself_as_contact"
328 CHECK ("member_id" != "other_member_id") );
329 CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
331 COMMENT ON TABLE "contact" IS 'Contact lists';
333 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
334 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
335 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
338 CREATE TABLE "ignored_member" (
339 PRIMARY KEY ("member_id", "other_member_id"),
340 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
341 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
342 CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
344 COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
346 COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone';
347 COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
350 CREATE TABLE "session" (
351 "ident" TEXT PRIMARY KEY,
352 "additional_secret" TEXT,
353 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
354 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
355 "lang" TEXT );
356 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
358 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer';
360 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
361 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
362 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
363 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
366 CREATE TABLE "policy" (
367 "id" SERIAL4 PRIMARY KEY,
368 "index" INT4 NOT NULL,
369 "active" BOOLEAN NOT NULL DEFAULT TRUE,
370 "name" TEXT NOT NULL UNIQUE,
371 "description" TEXT NOT NULL DEFAULT '',
372 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
373 "admission_time" INTERVAL,
374 "discussion_time" INTERVAL,
375 "verification_time" INTERVAL,
376 "voting_time" INTERVAL,
377 "issue_quorum_num" INT4 NOT NULL,
378 "issue_quorum_den" INT4 NOT NULL,
379 "initiative_quorum_num" INT4 NOT NULL,
380 "initiative_quorum_den" INT4 NOT NULL,
381 "direct_majority_num" INT4 NOT NULL DEFAULT 1,
382 "direct_majority_den" INT4 NOT NULL DEFAULT 2,
383 "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
384 "direct_majority_positive" INT4 NOT NULL DEFAULT 0,
385 "direct_majority_non_negative" INT4 NOT NULL DEFAULT 0,
386 "indirect_majority_num" INT4 NOT NULL DEFAULT 1,
387 "indirect_majority_den" INT4 NOT NULL DEFAULT 2,
388 "indirect_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
389 "indirect_majority_positive" INT4 NOT NULL DEFAULT 0,
390 "indirect_majority_non_negative" INT4 NOT NULL DEFAULT 0,
391 "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT TRUE,
392 "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE,
393 CONSTRAINT "timing" CHECK (
394 ( "polling" = FALSE AND
395 "admission_time" NOTNULL AND "discussion_time" NOTNULL AND
396 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
397 ( "polling" = TRUE AND
398 "admission_time" ISNULL AND "discussion_time" NOTNULL AND
399 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
400 ( "polling" = TRUE AND
401 "admission_time" ISNULL AND "discussion_time" ISNULL AND
402 "verification_time" ISNULL AND "voting_time" ISNULL ) ) );
403 CREATE INDEX "policy_active_idx" ON "policy" ("active");
405 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
407 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
408 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
409 COMMENT ON COLUMN "policy"."polling" IS 'TRUE = special policy for non-user-generated issues, i.e. polls ("admission_time" MUST be set to NULL, the other timings may be set to NULL altogether, allowing individual timing for issues)';
410 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"';
411 COMMENT ON COLUMN "policy"."discussion_time" IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"';
412 COMMENT ON COLUMN "policy"."verification_time" IS 'Duration of issue state ''verification''; Regular time until an issue is "fully_frozen" (e.g. entering issue state ''voting'') after being "half_frozen"';
413 COMMENT ON COLUMN "policy"."voting_time" IS 'Duration of issue state ''voting''; Time after an issue is "fully_frozen" but not "closed" (duration of issue state ''voting'')';
414 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" and enter issue state ''discussion''';
415 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" and enter issue state ''discussion''';
416 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
417 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
418 COMMENT ON COLUMN "policy"."direct_majority_num" IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner';
419 COMMENT ON COLUMN "policy"."direct_majority_den" IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner';
420 COMMENT ON COLUMN "policy"."direct_majority_strict" IS 'If TRUE, then the direct majority must be strictly greater than "direct_majority_num"/"direct_majority_den", otherwise it may also be equal.';
421 COMMENT ON COLUMN "policy"."direct_majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner';
422 COMMENT ON COLUMN "policy"."direct_majority_non_negative" IS 'Absolute number of sum of "positive_votes" and abstentions neccessary for an initiative to be attainable as winner';
423 COMMENT ON COLUMN "policy"."indirect_majority_num" IS 'Numerator of fraction of neccessary indirect majority (through beat path) for initiatives to be attainable as winner';
424 COMMENT ON COLUMN "policy"."indirect_majority_den" IS 'Denominator of fraction of neccessary indirect majority (through beat path) for initiatives to be attainable as winner';
425 COMMENT ON COLUMN "policy"."indirect_majority_strict" IS 'If TRUE, then the indirect majority must be strictly greater than "indirect_majority_num"/"indirect_majority_den", otherwise it may also be equal.';
426 COMMENT ON COLUMN "policy"."indirect_majority_positive" IS 'Absolute number of votes in favor of the winner neccessary in a beat path to the status quo for an initaitive to be attainable as winner';
427 COMMENT ON COLUMN "policy"."indirect_majority_non_negative" IS 'Absolute number of sum of votes in favor and abstentions in a beat path to the status quo for an initiative to be attainable as winner';
428 COMMENT ON COLUMN "policy"."no_reverse_beat_path" IS 'Causes initiatives with "reverse_beat_path" flag to not be "eligible", thus disallowing them to be winner. See comment on column "initiative"."reverse_beat_path". This option ensures both that a winning initiative is never tied in a (weak) condorcet paradox with the status quo and a winning initiative always beats the status quo directly with a simple majority.';
429 COMMENT ON COLUMN "policy"."no_multistage_majority" IS 'Causes initiatives with "multistage_majority" flag to not be "eligible", thus disallowing them to be winner. See comment on column "initiative"."multistage_majority". This disqualifies initiatives which could cause an instable result. An instable result in this meaning is a result such that repeating the ballot with same preferences but with the winner of the first ballot as status quo would lead to a different winner in the second ballot. If there are no direct majorities required for the winner, or if in direct comparison only simple majorities are required and "no_reverse_beat_path" is true, then results are always stable and this flag does not have any effect on the winner (but still affects the "eligible" flag of an "initiative").';
432 CREATE TABLE "unit" (
433 "id" SERIAL4 PRIMARY KEY,
434 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
435 "active" BOOLEAN NOT NULL DEFAULT TRUE,
436 "name" TEXT NOT NULL,
437 "description" TEXT NOT NULL DEFAULT '',
438 "member_count" INT4,
439 "text_search_data" TSVECTOR );
440 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
441 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
442 CREATE INDEX "unit_active_idx" ON "unit" ("active");
443 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
444 CREATE TRIGGER "update_text_search_data"
445 BEFORE INSERT OR UPDATE ON "unit"
446 FOR EACH ROW EXECUTE PROCEDURE
447 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
448 "name", "description" );
450 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
452 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
453 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in areas of this unit';
454 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
457 CREATE TABLE "unit_setting" (
458 PRIMARY KEY ("member_id", "key", "unit_id"),
459 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
460 "key" TEXT NOT NULL,
461 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
462 "value" TEXT NOT NULL );
464 COMMENT ON TABLE "unit_setting" IS 'Place for frontend to store unit specific settings of members as strings';
467 CREATE TABLE "area" (
468 "id" SERIAL4 PRIMARY KEY,
469 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
470 "active" BOOLEAN NOT NULL DEFAULT TRUE,
471 "name" TEXT NOT NULL,
472 "description" TEXT NOT NULL DEFAULT '',
473 "direct_member_count" INT4,
474 "member_weight" INT4,
475 "text_search_data" TSVECTOR );
476 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
477 CREATE INDEX "area_active_idx" ON "area" ("active");
478 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
479 CREATE TRIGGER "update_text_search_data"
480 BEFORE INSERT OR UPDATE ON "area"
481 FOR EACH ROW EXECUTE PROCEDURE
482 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
483 "name", "description" );
485 COMMENT ON TABLE "area" IS 'Subject areas';
487 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
488 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"';
489 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
492 CREATE TABLE "area_setting" (
493 PRIMARY KEY ("member_id", "key", "area_id"),
494 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
495 "key" TEXT NOT NULL,
496 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
497 "value" TEXT NOT NULL );
499 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
502 CREATE TABLE "allowed_policy" (
503 PRIMARY KEY ("area_id", "policy_id"),
504 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
505 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
506 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
507 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
509 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
511 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
514 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
516 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';
519 CREATE TYPE "issue_state" AS ENUM (
520 'admission', 'discussion', 'verification', 'voting',
521 'canceled_revoked_before_accepted',
522 'canceled_issue_not_accepted',
523 'canceled_after_revocation_during_discussion',
524 'canceled_after_revocation_during_verification',
525 'calculation',
526 'canceled_no_initiative_admitted',
527 'finished_without_winner', 'finished_with_winner');
529 COMMENT ON TYPE "issue_state" IS 'State of issues';
532 CREATE TABLE "issue" (
533 "id" SERIAL4 PRIMARY KEY,
534 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
535 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
536 "state" "issue_state" NOT NULL DEFAULT 'admission',
537 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
538 "accepted" TIMESTAMPTZ,
539 "half_frozen" TIMESTAMPTZ,
540 "fully_frozen" TIMESTAMPTZ,
541 "closed" TIMESTAMPTZ,
542 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
543 "cleaned" TIMESTAMPTZ,
544 "admission_time" INTERVAL NOT NULL,
545 "discussion_time" INTERVAL NOT NULL,
546 "verification_time" INTERVAL NOT NULL,
547 "voting_time" INTERVAL NOT NULL,
548 "snapshot" TIMESTAMPTZ,
549 "latest_snapshot_event" "snapshot_event",
550 "population" INT4,
551 "voter_count" INT4,
552 "status_quo_schulze_rank" INT4,
553 CONSTRAINT "valid_state" CHECK ((
554 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
555 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
556 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
557 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
558 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
559 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
560 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
561 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
562 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)) AND (
563 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
564 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
565 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
566 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
567 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
568 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
569 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
570 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
571 ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
572 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
573 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
574 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
575 )),
576 CONSTRAINT "state_change_order" CHECK (
577 "created" <= "accepted" AND
578 "accepted" <= "half_frozen" AND
579 "half_frozen" <= "fully_frozen" AND
580 "fully_frozen" <= "closed" ),
581 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
582 "cleaned" ISNULL OR "closed" NOTNULL ),
583 CONSTRAINT "last_snapshot_on_full_freeze"
584 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
585 CONSTRAINT "freeze_requires_snapshot"
586 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
587 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
588 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
589 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
590 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
591 CREATE INDEX "issue_created_idx" ON "issue" ("created");
592 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
593 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
594 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
595 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
596 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
597 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
599 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
601 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
602 COMMENT ON COLUMN "issue"."half_frozen" IS 'Point in time, when "discussion_time" has elapsed; 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.';
603 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.';
604 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.';
605 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
606 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
607 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
608 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
609 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
610 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
611 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
612 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';
613 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
614 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';
615 COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
618 CREATE TABLE "issue_setting" (
619 PRIMARY KEY ("member_id", "key", "issue_id"),
620 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
621 "key" TEXT NOT NULL,
622 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
623 "value" TEXT NOT NULL );
625 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
628 CREATE TABLE "initiative" (
629 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
630 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
631 "id" SERIAL4 PRIMARY KEY,
632 "name" TEXT NOT NULL,
633 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
634 "discussion_url" TEXT,
635 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
636 "revoked" TIMESTAMPTZ,
637 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
638 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
639 "admitted" BOOLEAN,
640 "supporter_count" INT4,
641 "informed_supporter_count" INT4,
642 "satisfied_supporter_count" INT4,
643 "satisfied_informed_supporter_count" INT4,
644 "positive_votes" INT4,
645 "negative_votes" INT4,
646 "direct_majority" BOOLEAN,
647 "indirect_majority" BOOLEAN,
648 "schulze_rank" INT4,
649 "better_than_status_quo" BOOLEAN,
650 "worse_than_status_quo" BOOLEAN,
651 "reverse_beat_path" BOOLEAN,
652 "multistage_majority" BOOLEAN,
653 "eligible" BOOLEAN,
654 "winner" BOOLEAN,
655 "rank" INT4,
656 "text_search_data" TSVECTOR,
657 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
658 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL),
659 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
660 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
661 CONSTRAINT "revoked_initiatives_cant_be_admitted"
662 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
663 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
664 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
665 ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND
666 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
667 "schulze_rank" ISNULL AND
668 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
669 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
670 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ),
671 CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")),
672 CONSTRAINT "minimum_requirement_to_be_eligible" CHECK (
673 "eligible" = FALSE OR
674 ("direct_majority" AND "indirect_majority" AND "better_than_status_quo") ),
675 CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE),
676 CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1),
677 CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE),
678 CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") );
679 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
680 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
681 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
682 CREATE TRIGGER "update_text_search_data"
683 BEFORE INSERT OR UPDATE ON "initiative"
684 FOR EACH ROW EXECUTE PROCEDURE
685 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
686 "name", "discussion_url");
688 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.';
690 COMMENT ON COLUMN "initiative"."polling" IS 'Initiative is an option for a poll (see "policy"."polling"), and does not need to pass the initiative quorum';
691 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
692 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
693 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoke the initiative';
694 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
695 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
696 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
697 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
698 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
699 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
700 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
701 COMMENT ON COLUMN "initiative"."direct_majority" IS 'TRUE, if "positive_votes"/("positive_votes"+"negative_votes") is strictly greater or greater-equal than "direct_majority_num"/"direct_majority_den", and "positive_votes" is greater-equal than "direct_majority_positive", and ("positive_votes"+abstentions) is greater-equal than "direct_majority_non_negative"';
702 COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths';
703 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking without tie-breaking';
704 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)';
705 COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)';
706 COMMENT ON COLUMN "initiative"."reverse_beat_path" IS 'TRUE, if there is a beat path (may include ties) from this initiative to the status quo';
707 COMMENT ON COLUMN "initiative"."multistage_majority" IS 'TRUE, if either (a) this initiative has no better rank than the status quo, or (b) there exists a better ranked initiative X, which directly beats this initiative, and either more voters prefer X to this initiative than voters preferring X to the status quo or less voters prefer this initiative to X than voters preferring the status quo to X';
708 COMMENT ON COLUMN "initiative"."eligible" IS 'Initiative has a "direct_majority" and an "indirect_majority", is "better_than_status_quo" and depending on selected policy the initiative has no "reverse_beat_path" or "multistage_majority"';
709 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"';
710 COMMENT ON COLUMN "initiative"."rank" IS 'Unique ranking for all "admitted" initiatives per issue; lower rank is better; a winner always has rank 1, but rank 1 does not imply that an initiative is winner; initiatives with "direct_majority" AND "indirect_majority" always have a better (lower) rank than other initiatives';
713 CREATE TABLE "battle" (
714 "issue_id" INT4 NOT NULL,
715 "winning_initiative_id" INT4,
716 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
717 "losing_initiative_id" INT4,
718 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
719 "count" INT4 NOT NULL,
720 CONSTRAINT "initiative_ids_not_equal" CHECK (
721 "winning_initiative_id" != "losing_initiative_id" OR
722 ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
723 ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) );
724 CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
725 CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
726 CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
728 COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue; NULL as initiative_id denotes virtual "status-quo" initiative';
731 CREATE TABLE "ignored_initiative" (
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 CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");
737 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
740 CREATE TABLE "initiative_setting" (
741 PRIMARY KEY ("member_id", "key", "initiative_id"),
742 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
743 "key" TEXT NOT NULL,
744 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
745 "value" TEXT NOT NULL );
747 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
750 CREATE TABLE "draft" (
751 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
752 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
753 "id" SERIAL8 PRIMARY KEY,
754 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
755 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
756 "formatting_engine" TEXT,
757 "content" TEXT NOT NULL,
758 "text_search_data" TSVECTOR );
759 CREATE INDEX "draft_created_idx" ON "draft" ("created");
760 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
761 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
762 CREATE TRIGGER "update_text_search_data"
763 BEFORE INSERT OR UPDATE ON "draft"
764 FOR EACH ROW EXECUTE PROCEDURE
765 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
767 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.';
769 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
770 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
773 CREATE TABLE "rendered_draft" (
774 PRIMARY KEY ("draft_id", "format"),
775 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
776 "format" TEXT,
777 "content" TEXT NOT NULL );
779 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)';
782 CREATE TABLE "suggestion" (
783 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
784 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
785 "id" SERIAL8 PRIMARY KEY,
786 "draft_id" INT8 NOT NULL,
787 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
788 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
789 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
790 "name" TEXT NOT NULL,
791 "formatting_engine" TEXT,
792 "content" TEXT NOT NULL DEFAULT '',
793 "text_search_data" TSVECTOR,
794 "minus2_unfulfilled_count" INT4,
795 "minus2_fulfilled_count" INT4,
796 "minus1_unfulfilled_count" INT4,
797 "minus1_fulfilled_count" INT4,
798 "plus1_unfulfilled_count" INT4,
799 "plus1_fulfilled_count" INT4,
800 "plus2_unfulfilled_count" INT4,
801 "plus2_fulfilled_count" INT4 );
802 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
803 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
804 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
805 CREATE TRIGGER "update_text_search_data"
806 BEFORE INSERT OR UPDATE ON "suggestion"
807 FOR EACH ROW EXECUTE PROCEDURE
808 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
809 "name", "content");
811 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';
813 COMMENT ON COLUMN "suggestion"."draft_id" IS 'Draft, which the author has seen when composing the suggestion; should always be set by a frontend, but defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
814 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
815 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
816 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
817 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
818 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
819 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
820 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
821 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
824 CREATE TABLE "rendered_suggestion" (
825 PRIMARY KEY ("suggestion_id", "format"),
826 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
827 "format" TEXT,
828 "content" TEXT NOT NULL );
830 COMMENT ON TABLE "rendered_suggestion" IS 'This table may be used by frontends to cache "rendered" drafts (e.g. HTML output generated from wiki text)';
833 CREATE TABLE "suggestion_setting" (
834 PRIMARY KEY ("member_id", "key", "suggestion_id"),
835 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
836 "key" TEXT NOT NULL,
837 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
838 "value" TEXT NOT NULL );
840 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
843 CREATE TABLE "privilege" (
844 PRIMARY KEY ("unit_id", "member_id"),
845 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
846 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
847 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
848 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
849 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
850 "member_manager" BOOLEAN NOT NULL DEFAULT FALSE,
851 "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE,
852 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE,
853 "polling_right" BOOLEAN NOT NULL DEFAULT FALSE );
855 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
857 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke any privileges to/from other members';
858 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create and disable sub units';
859 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create and disable areas and set area parameters';
860 COMMENT ON COLUMN "privilege"."member_manager" IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"';
861 COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative';
862 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to support initiatives, create and rate suggestions, and to vote';
863 COMMENT ON COLUMN "privilege"."polling_right" IS 'Right to create polls (see "policy"."polling" and "initiative"."polling")';
866 CREATE TABLE "membership" (
867 PRIMARY KEY ("area_id", "member_id"),
868 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
869 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
870 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
872 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
875 CREATE TABLE "interest" (
876 PRIMARY KEY ("issue_id", "member_id"),
877 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
878 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
879 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
881 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.';
884 CREATE TABLE "initiator" (
885 PRIMARY KEY ("initiative_id", "member_id"),
886 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
887 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
888 "accepted" BOOLEAN );
889 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
891 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.';
893 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.';
896 CREATE TABLE "supporter" (
897 "issue_id" INT4 NOT NULL,
898 PRIMARY KEY ("initiative_id", "member_id"),
899 "initiative_id" INT4,
900 "member_id" INT4,
901 "draft_id" INT8 NOT NULL,
902 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
903 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE );
904 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
906 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.';
908 COMMENT ON COLUMN "supporter"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
909 COMMENT ON COLUMN "supporter"."draft_id" IS 'Latest seen draft; should always be set by a frontend, but defaults to current draft of the initiative (implemented by trigger "default_for_draft_id")';
912 CREATE TABLE "opinion" (
913 "initiative_id" INT4 NOT NULL,
914 PRIMARY KEY ("suggestion_id", "member_id"),
915 "suggestion_id" INT8,
916 "member_id" INT4,
917 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
918 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
919 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
920 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
921 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
923 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.';
925 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
928 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
930 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
933 CREATE TABLE "delegation" (
934 "id" SERIAL8 PRIMARY KEY,
935 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
936 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
937 "scope" "delegation_scope" NOT NULL,
938 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
939 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
940 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
941 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
942 CONSTRAINT "no_unit_delegation_to_null"
943 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
944 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
945 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
946 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
947 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
948 UNIQUE ("unit_id", "truster_id"),
949 UNIQUE ("area_id", "truster_id"),
950 UNIQUE ("issue_id", "truster_id") );
951 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
952 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
954 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
956 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
957 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
958 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
961 CREATE TABLE "direct_population_snapshot" (
962 PRIMARY KEY ("issue_id", "event", "member_id"),
963 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
964 "event" "snapshot_event",
965 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
966 "weight" INT4 );
967 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
969 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
971 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
972 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
975 CREATE TABLE "delegating_population_snapshot" (
976 PRIMARY KEY ("issue_id", "event", "member_id"),
977 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
978 "event" "snapshot_event",
979 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
980 "weight" INT4,
981 "scope" "delegation_scope" NOT NULL,
982 "delegate_member_ids" INT4[] NOT NULL );
983 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
985 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
987 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
988 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
989 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
990 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"';
993 CREATE TABLE "direct_interest_snapshot" (
994 PRIMARY KEY ("issue_id", "event", "member_id"),
995 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
996 "event" "snapshot_event",
997 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
998 "weight" INT4 );
999 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
1001 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
1003 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1004 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
1007 CREATE TABLE "delegating_interest_snapshot" (
1008 PRIMARY KEY ("issue_id", "event", "member_id"),
1009 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1010 "event" "snapshot_event",
1011 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1012 "weight" INT4,
1013 "scope" "delegation_scope" NOT NULL,
1014 "delegate_member_ids" INT4[] NOT NULL );
1015 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
1017 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
1019 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1020 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
1021 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
1022 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"';
1025 CREATE TABLE "direct_supporter_snapshot" (
1026 "issue_id" INT4 NOT NULL,
1027 PRIMARY KEY ("initiative_id", "event", "member_id"),
1028 "initiative_id" INT4,
1029 "event" "snapshot_event",
1030 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1031 "draft_id" INT8 NOT NULL,
1032 "informed" BOOLEAN NOT NULL,
1033 "satisfied" BOOLEAN NOT NULL,
1034 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1035 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
1036 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1037 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
1039 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
1041 COMMENT ON COLUMN "direct_supporter_snapshot"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
1042 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1043 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
1044 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
1047 CREATE TABLE "non_voter" (
1048 PRIMARY KEY ("issue_id", "member_id"),
1049 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1050 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
1051 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
1053 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
1056 CREATE TABLE "direct_voter" (
1057 PRIMARY KEY ("issue_id", "member_id"),
1058 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1059 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1060 "weight" INT4 );
1061 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
1063 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.';
1065 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
1068 CREATE TABLE "delegating_voter" (
1069 PRIMARY KEY ("issue_id", "member_id"),
1070 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1071 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1072 "weight" INT4,
1073 "scope" "delegation_scope" NOT NULL,
1074 "delegate_member_ids" INT4[] NOT NULL );
1075 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
1077 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
1079 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
1080 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
1081 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"';
1084 CREATE TABLE "vote" (
1085 "issue_id" INT4 NOT NULL,
1086 PRIMARY KEY ("initiative_id", "member_id"),
1087 "initiative_id" INT4,
1088 "member_id" INT4,
1089 "grade" INT4,
1090 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1091 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1092 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
1094 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.';
1096 COMMENT ON COLUMN "vote"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
1097 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.';
1100 CREATE TABLE "voting_comment" (
1101 PRIMARY KEY ("issue_id", "member_id"),
1102 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1103 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1104 "changed" TIMESTAMPTZ,
1105 "formatting_engine" TEXT,
1106 "content" TEXT NOT NULL,
1107 "text_search_data" TSVECTOR );
1108 CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id");
1109 CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data");
1110 CREATE TRIGGER "update_text_search_data"
1111 BEFORE INSERT OR UPDATE ON "voting_comment"
1112 FOR EACH ROW EXECUTE PROCEDURE
1113 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
1115 COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.';
1117 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.';
1120 CREATE TABLE "rendered_voting_comment" (
1121 PRIMARY KEY ("issue_id", "member_id", "format"),
1122 FOREIGN KEY ("issue_id", "member_id")
1123 REFERENCES "voting_comment" ("issue_id", "member_id")
1124 ON DELETE CASCADE ON UPDATE CASCADE,
1125 "issue_id" INT4,
1126 "member_id" INT4,
1127 "format" TEXT,
1128 "content" TEXT NOT NULL );
1130 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)';
1133 CREATE TYPE "event_type" AS ENUM (
1134 'issue_state_changed',
1135 'initiative_created_in_new_issue',
1136 'initiative_created_in_existing_issue',
1137 'initiative_revoked',
1138 'new_draft_created',
1139 'suggestion_created');
1141 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
1144 CREATE TABLE "event" (
1145 "id" SERIAL8 PRIMARY KEY,
1146 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
1147 "event" "event_type" NOT NULL,
1148 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1149 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1150 "state" "issue_state" CHECK ("state" != 'calculation'),
1151 "initiative_id" INT4,
1152 "draft_id" INT8,
1153 "suggestion_id" INT8,
1154 FOREIGN KEY ("issue_id", "initiative_id")
1155 REFERENCES "initiative" ("issue_id", "id")
1156 ON DELETE CASCADE ON UPDATE CASCADE,
1157 FOREIGN KEY ("initiative_id", "draft_id")
1158 REFERENCES "draft" ("initiative_id", "id")
1159 ON DELETE CASCADE ON UPDATE CASCADE,
1160 FOREIGN KEY ("initiative_id", "suggestion_id")
1161 REFERENCES "suggestion" ("initiative_id", "id")
1162 ON DELETE CASCADE ON UPDATE CASCADE,
1163 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
1164 "event" != 'issue_state_changed' OR (
1165 "member_id" ISNULL AND
1166 "issue_id" NOTNULL AND
1167 "state" NOTNULL AND
1168 "initiative_id" ISNULL AND
1169 "draft_id" ISNULL AND
1170 "suggestion_id" ISNULL )),
1171 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
1172 "event" NOT IN (
1173 'initiative_created_in_new_issue',
1174 'initiative_created_in_existing_issue',
1175 'initiative_revoked',
1176 'new_draft_created'
1177 ) OR (
1178 "member_id" NOTNULL AND
1179 "issue_id" NOTNULL AND
1180 "state" NOTNULL AND
1181 "initiative_id" NOTNULL AND
1182 "draft_id" NOTNULL AND
1183 "suggestion_id" ISNULL )),
1184 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
1185 "event" != 'suggestion_created' OR (
1186 "member_id" NOTNULL AND
1187 "issue_id" NOTNULL AND
1188 "state" NOTNULL AND
1189 "initiative_id" NOTNULL AND
1190 "draft_id" ISNULL AND
1191 "suggestion_id" NOTNULL )) );
1192 CREATE INDEX "event_occurrence_idx" ON "event" ("occurrence");
1194 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
1196 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
1197 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';
1198 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';
1199 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';
1202 CREATE TABLE "notification_sent" (
1203 "event_id" INT8 NOT NULL );
1204 CREATE UNIQUE INDEX "notification_sent_singleton_idx" ON "notification_sent" ((1));
1206 COMMENT ON TABLE "notification_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out';
1207 COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.';
1211 ----------------------------------------------
1212 -- Writing of history entries and event log --
1213 ----------------------------------------------
1216 CREATE FUNCTION "write_member_history_trigger"()
1217 RETURNS TRIGGER
1218 LANGUAGE 'plpgsql' VOLATILE AS $$
1219 BEGIN
1220 IF
1221 ( NEW."active" != OLD."active" OR
1222 NEW."name" != OLD."name" ) AND
1223 OLD."activated" NOTNULL
1224 THEN
1225 INSERT INTO "member_history"
1226 ("member_id", "active", "name")
1227 VALUES (NEW."id", OLD."active", OLD."name");
1228 END IF;
1229 RETURN NULL;
1230 END;
1231 $$;
1233 CREATE TRIGGER "write_member_history"
1234 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1235 "write_member_history_trigger"();
1237 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
1238 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
1241 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
1242 RETURNS TRIGGER
1243 LANGUAGE 'plpgsql' VOLATILE AS $$
1244 BEGIN
1245 IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN
1246 INSERT INTO "event" ("event", "issue_id", "state")
1247 VALUES ('issue_state_changed', NEW."id", NEW."state");
1248 END IF;
1249 RETURN NULL;
1250 END;
1251 $$;
1253 CREATE TRIGGER "write_event_issue_state_changed"
1254 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
1255 "write_event_issue_state_changed_trigger"();
1257 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
1258 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
1261 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1262 RETURNS TRIGGER
1263 LANGUAGE 'plpgsql' VOLATILE AS $$
1264 DECLARE
1265 "initiative_row" "initiative"%ROWTYPE;
1266 "issue_row" "issue"%ROWTYPE;
1267 "event_v" "event_type";
1268 BEGIN
1269 SELECT * INTO "initiative_row" FROM "initiative"
1270 WHERE "id" = NEW."initiative_id";
1271 SELECT * INTO "issue_row" FROM "issue"
1272 WHERE "id" = "initiative_row"."issue_id";
1273 IF EXISTS (
1274 SELECT NULL FROM "draft"
1275 WHERE "initiative_id" = NEW."initiative_id"
1276 AND "id" != NEW."id"
1277 ) THEN
1278 "event_v" := 'new_draft_created';
1279 ELSE
1280 IF EXISTS (
1281 SELECT NULL FROM "initiative"
1282 WHERE "issue_id" = "initiative_row"."issue_id"
1283 AND "id" != "initiative_row"."id"
1284 ) THEN
1285 "event_v" := 'initiative_created_in_existing_issue';
1286 ELSE
1287 "event_v" := 'initiative_created_in_new_issue';
1288 END IF;
1289 END IF;
1290 INSERT INTO "event" (
1291 "event", "member_id",
1292 "issue_id", "state", "initiative_id", "draft_id"
1293 ) VALUES (
1294 "event_v",
1295 NEW."author_id",
1296 "initiative_row"."issue_id",
1297 "issue_row"."state",
1298 "initiative_row"."id",
1299 NEW."id" );
1300 RETURN NULL;
1301 END;
1302 $$;
1304 CREATE TRIGGER "write_event_initiative_or_draft_created"
1305 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
1306 "write_event_initiative_or_draft_created_trigger"();
1308 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
1309 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
1312 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
1313 RETURNS TRIGGER
1314 LANGUAGE 'plpgsql' VOLATILE AS $$
1315 DECLARE
1316 "issue_row" "issue"%ROWTYPE;
1317 "draft_id_v" "draft"."id"%TYPE;
1318 BEGIN
1319 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1320 SELECT * INTO "issue_row" FROM "issue"
1321 WHERE "id" = NEW."issue_id";
1322 SELECT "id" INTO "draft_id_v" FROM "current_draft"
1323 WHERE "initiative_id" = NEW."id";
1324 INSERT INTO "event" (
1325 "event", "member_id", "issue_id", "state", "initiative_id", "draft_id"
1326 ) VALUES (
1327 'initiative_revoked',
1328 NEW."revoked_by_member_id",
1329 NEW."issue_id",
1330 "issue_row"."state",
1331 NEW."id",
1332 "draft_id_v");
1333 END IF;
1334 RETURN NULL;
1335 END;
1336 $$;
1338 CREATE TRIGGER "write_event_initiative_revoked"
1339 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
1340 "write_event_initiative_revoked_trigger"();
1342 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
1343 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
1346 CREATE FUNCTION "write_event_suggestion_created_trigger"()
1347 RETURNS TRIGGER
1348 LANGUAGE 'plpgsql' VOLATILE AS $$
1349 DECLARE
1350 "initiative_row" "initiative"%ROWTYPE;
1351 "issue_row" "issue"%ROWTYPE;
1352 BEGIN
1353 SELECT * INTO "initiative_row" FROM "initiative"
1354 WHERE "id" = NEW."initiative_id";
1355 SELECT * INTO "issue_row" FROM "issue"
1356 WHERE "id" = "initiative_row"."issue_id";
1357 INSERT INTO "event" (
1358 "event", "member_id",
1359 "issue_id", "state", "initiative_id", "suggestion_id"
1360 ) VALUES (
1361 'suggestion_created',
1362 NEW."author_id",
1363 "initiative_row"."issue_id",
1364 "issue_row"."state",
1365 "initiative_row"."id",
1366 NEW."id" );
1367 RETURN NULL;
1368 END;
1369 $$;
1371 CREATE TRIGGER "write_event_suggestion_created"
1372 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1373 "write_event_suggestion_created_trigger"();
1375 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
1376 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1380 ----------------------------
1381 -- Additional constraints --
1382 ----------------------------
1385 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
1386 RETURNS TRIGGER
1387 LANGUAGE 'plpgsql' VOLATILE AS $$
1388 BEGIN
1389 IF NOT EXISTS (
1390 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
1391 ) THEN
1392 --RAISE 'Cannot create issue without an initial initiative.' USING
1393 -- ERRCODE = 'integrity_constraint_violation',
1394 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
1395 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
1396 END IF;
1397 RETURN NULL;
1398 END;
1399 $$;
1401 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
1402 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
1403 FOR EACH ROW EXECUTE PROCEDURE
1404 "issue_requires_first_initiative_trigger"();
1406 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
1407 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
1410 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
1411 RETURNS TRIGGER
1412 LANGUAGE 'plpgsql' VOLATILE AS $$
1413 DECLARE
1414 "reference_lost" BOOLEAN;
1415 BEGIN
1416 IF TG_OP = 'DELETE' THEN
1417 "reference_lost" := TRUE;
1418 ELSE
1419 "reference_lost" := NEW."issue_id" != OLD."issue_id";
1420 END IF;
1421 IF
1422 "reference_lost" AND NOT EXISTS (
1423 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
1424 )
1425 THEN
1426 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
1427 END IF;
1428 RETURN NULL;
1429 END;
1430 $$;
1432 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
1433 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1434 FOR EACH ROW EXECUTE PROCEDURE
1435 "last_initiative_deletes_issue_trigger"();
1437 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
1438 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
1441 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
1442 RETURNS TRIGGER
1443 LANGUAGE 'plpgsql' VOLATILE AS $$
1444 BEGIN
1445 IF NOT EXISTS (
1446 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
1447 ) THEN
1448 --RAISE 'Cannot create initiative without an initial draft.' USING
1449 -- ERRCODE = 'integrity_constraint_violation',
1450 -- HINT = 'Create issue, initiative and draft within the same transaction.';
1451 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
1452 END IF;
1453 RETURN NULL;
1454 END;
1455 $$;
1457 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
1458 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1459 FOR EACH ROW EXECUTE PROCEDURE
1460 "initiative_requires_first_draft_trigger"();
1462 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
1463 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
1466 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
1467 RETURNS TRIGGER
1468 LANGUAGE 'plpgsql' VOLATILE AS $$
1469 DECLARE
1470 "reference_lost" BOOLEAN;
1471 BEGIN
1472 IF TG_OP = 'DELETE' THEN
1473 "reference_lost" := TRUE;
1474 ELSE
1475 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
1476 END IF;
1477 IF
1478 "reference_lost" AND NOT EXISTS (
1479 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
1480 )
1481 THEN
1482 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
1483 END IF;
1484 RETURN NULL;
1485 END;
1486 $$;
1488 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
1489 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
1490 FOR EACH ROW EXECUTE PROCEDURE
1491 "last_draft_deletes_initiative_trigger"();
1493 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
1494 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
1497 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
1498 RETURNS TRIGGER
1499 LANGUAGE 'plpgsql' VOLATILE AS $$
1500 BEGIN
1501 IF NOT EXISTS (
1502 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
1503 ) THEN
1504 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
1505 END IF;
1506 RETURN NULL;
1507 END;
1508 $$;
1510 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
1511 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
1512 FOR EACH ROW EXECUTE PROCEDURE
1513 "suggestion_requires_first_opinion_trigger"();
1515 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1516 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1519 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1520 RETURNS TRIGGER
1521 LANGUAGE 'plpgsql' VOLATILE AS $$
1522 DECLARE
1523 "reference_lost" BOOLEAN;
1524 BEGIN
1525 IF TG_OP = 'DELETE' THEN
1526 "reference_lost" := TRUE;
1527 ELSE
1528 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1529 END IF;
1530 IF
1531 "reference_lost" AND NOT EXISTS (
1532 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1533 )
1534 THEN
1535 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1536 END IF;
1537 RETURN NULL;
1538 END;
1539 $$;
1541 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1542 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1543 FOR EACH ROW EXECUTE PROCEDURE
1544 "last_opinion_deletes_suggestion_trigger"();
1546 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1547 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1551 ---------------------------------------------------------------
1552 -- Ensure that votes are not modified when issues are frozen --
1553 ---------------------------------------------------------------
1555 -- NOTE: Frontends should ensure this anyway, but in case of programming
1556 -- errors the following triggers ensure data integrity.
1559 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1560 RETURNS TRIGGER
1561 LANGUAGE 'plpgsql' VOLATILE AS $$
1562 DECLARE
1563 "issue_id_v" "issue"."id"%TYPE;
1564 "issue_row" "issue"%ROWTYPE;
1565 BEGIN
1566 IF TG_OP = 'DELETE' THEN
1567 "issue_id_v" := OLD."issue_id";
1568 ELSE
1569 "issue_id_v" := NEW."issue_id";
1570 END IF;
1571 SELECT INTO "issue_row" * FROM "issue"
1572 WHERE "id" = "issue_id_v" FOR SHARE;
1573 IF "issue_row"."closed" NOTNULL THEN
1574 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
1575 END IF;
1576 RETURN NULL;
1577 END;
1578 $$;
1580 CREATE TRIGGER "forbid_changes_on_closed_issue"
1581 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1582 FOR EACH ROW EXECUTE PROCEDURE
1583 "forbid_changes_on_closed_issue_trigger"();
1585 CREATE TRIGGER "forbid_changes_on_closed_issue"
1586 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1587 FOR EACH ROW EXECUTE PROCEDURE
1588 "forbid_changes_on_closed_issue_trigger"();
1590 CREATE TRIGGER "forbid_changes_on_closed_issue"
1591 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1592 FOR EACH ROW EXECUTE PROCEDURE
1593 "forbid_changes_on_closed_issue_trigger"();
1595 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"';
1596 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';
1597 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';
1598 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';
1602 --------------------------------------------------------------------
1603 -- Auto-retrieval of fields only needed for referential integrity --
1604 --------------------------------------------------------------------
1607 CREATE FUNCTION "autofill_issue_id_trigger"()
1608 RETURNS TRIGGER
1609 LANGUAGE 'plpgsql' VOLATILE AS $$
1610 BEGIN
1611 IF NEW."issue_id" ISNULL THEN
1612 SELECT "issue_id" INTO NEW."issue_id"
1613 FROM "initiative" WHERE "id" = NEW."initiative_id";
1614 END IF;
1615 RETURN NEW;
1616 END;
1617 $$;
1619 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1620 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1622 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1623 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1625 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1626 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1627 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1630 CREATE FUNCTION "autofill_initiative_id_trigger"()
1631 RETURNS TRIGGER
1632 LANGUAGE 'plpgsql' VOLATILE AS $$
1633 BEGIN
1634 IF NEW."initiative_id" ISNULL THEN
1635 SELECT "initiative_id" INTO NEW."initiative_id"
1636 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1637 END IF;
1638 RETURN NEW;
1639 END;
1640 $$;
1642 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1643 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1645 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1646 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1650 -----------------------------------------------------
1651 -- Automatic calculation of certain default values --
1652 -----------------------------------------------------
1655 CREATE FUNCTION "copy_timings_trigger"()
1656 RETURNS TRIGGER
1657 LANGUAGE 'plpgsql' VOLATILE AS $$
1658 DECLARE
1659 "policy_row" "policy"%ROWTYPE;
1660 BEGIN
1661 SELECT * INTO "policy_row" FROM "policy"
1662 WHERE "id" = NEW."policy_id";
1663 IF NEW."admission_time" ISNULL THEN
1664 NEW."admission_time" := "policy_row"."admission_time";
1665 END IF;
1666 IF NEW."discussion_time" ISNULL THEN
1667 NEW."discussion_time" := "policy_row"."discussion_time";
1668 END IF;
1669 IF NEW."verification_time" ISNULL THEN
1670 NEW."verification_time" := "policy_row"."verification_time";
1671 END IF;
1672 IF NEW."voting_time" ISNULL THEN
1673 NEW."voting_time" := "policy_row"."voting_time";
1674 END IF;
1675 RETURN NEW;
1676 END;
1677 $$;
1679 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1680 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1682 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1683 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1686 CREATE FUNCTION "default_for_draft_id_trigger"()
1687 RETURNS TRIGGER
1688 LANGUAGE 'plpgsql' VOLATILE AS $$
1689 BEGIN
1690 IF NEW."draft_id" ISNULL THEN
1691 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1692 WHERE "initiative_id" = NEW."initiative_id";
1693 END IF;
1694 RETURN NEW;
1695 END;
1696 $$;
1698 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"
1699 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1700 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1701 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1703 COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';
1704 COMMENT ON TRIGGER "default_for_draft_id" ON "suggestion" IS 'If "draft_id" is NULL, then use the current draft of the initiative as default';
1705 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';
1709 ----------------------------------------
1710 -- Automatic creation of dependencies --
1711 ----------------------------------------
1714 CREATE FUNCTION "autocreate_interest_trigger"()
1715 RETURNS TRIGGER
1716 LANGUAGE 'plpgsql' VOLATILE AS $$
1717 BEGIN
1718 IF NOT EXISTS (
1719 SELECT NULL FROM "initiative" JOIN "interest"
1720 ON "initiative"."issue_id" = "interest"."issue_id"
1721 WHERE "initiative"."id" = NEW."initiative_id"
1722 AND "interest"."member_id" = NEW."member_id"
1723 ) THEN
1724 BEGIN
1725 INSERT INTO "interest" ("issue_id", "member_id")
1726 SELECT "issue_id", NEW."member_id"
1727 FROM "initiative" WHERE "id" = NEW."initiative_id";
1728 EXCEPTION WHEN unique_violation THEN END;
1729 END IF;
1730 RETURN NEW;
1731 END;
1732 $$;
1734 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1735 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1737 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1738 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';
1741 CREATE FUNCTION "autocreate_supporter_trigger"()
1742 RETURNS TRIGGER
1743 LANGUAGE 'plpgsql' VOLATILE AS $$
1744 BEGIN
1745 IF NOT EXISTS (
1746 SELECT NULL FROM "suggestion" JOIN "supporter"
1747 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1748 WHERE "suggestion"."id" = NEW."suggestion_id"
1749 AND "supporter"."member_id" = NEW."member_id"
1750 ) THEN
1751 BEGIN
1752 INSERT INTO "supporter" ("initiative_id", "member_id")
1753 SELECT "initiative_id", NEW."member_id"
1754 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1755 EXCEPTION WHEN unique_violation THEN END;
1756 END IF;
1757 RETURN NEW;
1758 END;
1759 $$;
1761 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1762 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1764 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1765 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.';
1769 ------------------------------------------
1770 -- Views and helper functions for views --
1771 ------------------------------------------
1774 CREATE VIEW "unit_delegation" AS
1775 SELECT
1776 "unit"."id" AS "unit_id",
1777 "delegation"."id",
1778 "delegation"."truster_id",
1779 "delegation"."trustee_id",
1780 "delegation"."scope"
1781 FROM "unit"
1782 JOIN "delegation"
1783 ON "delegation"."unit_id" = "unit"."id"
1784 JOIN "member"
1785 ON "delegation"."truster_id" = "member"."id"
1786 JOIN "privilege"
1787 ON "delegation"."unit_id" = "privilege"."unit_id"
1788 AND "delegation"."truster_id" = "privilege"."member_id"
1789 WHERE "member"."active" AND "privilege"."voting_right";
1791 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
1794 CREATE VIEW "area_delegation" AS
1795 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1796 "area"."id" AS "area_id",
1797 "delegation"."id",
1798 "delegation"."truster_id",
1799 "delegation"."trustee_id",
1800 "delegation"."scope"
1801 FROM "area"
1802 JOIN "delegation"
1803 ON "delegation"."unit_id" = "area"."unit_id"
1804 OR "delegation"."area_id" = "area"."id"
1805 JOIN "member"
1806 ON "delegation"."truster_id" = "member"."id"
1807 JOIN "privilege"
1808 ON "area"."unit_id" = "privilege"."unit_id"
1809 AND "delegation"."truster_id" = "privilege"."member_id"
1810 WHERE "member"."active" AND "privilege"."voting_right"
1811 ORDER BY
1812 "area"."id",
1813 "delegation"."truster_id",
1814 "delegation"."scope" DESC;
1816 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
1819 CREATE VIEW "issue_delegation" AS
1820 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1821 "issue"."id" AS "issue_id",
1822 "delegation"."id",
1823 "delegation"."truster_id",
1824 "delegation"."trustee_id",
1825 "delegation"."scope"
1826 FROM "issue"
1827 JOIN "area"
1828 ON "area"."id" = "issue"."area_id"
1829 JOIN "delegation"
1830 ON "delegation"."unit_id" = "area"."unit_id"
1831 OR "delegation"."area_id" = "area"."id"
1832 OR "delegation"."issue_id" = "issue"."id"
1833 JOIN "member"
1834 ON "delegation"."truster_id" = "member"."id"
1835 JOIN "privilege"
1836 ON "area"."unit_id" = "privilege"."unit_id"
1837 AND "delegation"."truster_id" = "privilege"."member_id"
1838 WHERE "member"."active" AND "privilege"."voting_right"
1839 ORDER BY
1840 "issue"."id",
1841 "delegation"."truster_id",
1842 "delegation"."scope" DESC;
1844 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
1847 CREATE FUNCTION "membership_weight_with_skipping"
1848 ( "area_id_p" "area"."id"%TYPE,
1849 "member_id_p" "member"."id"%TYPE,
1850 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1851 RETURNS INT4
1852 LANGUAGE 'plpgsql' STABLE AS $$
1853 DECLARE
1854 "sum_v" INT4;
1855 "delegation_row" "area_delegation"%ROWTYPE;
1856 BEGIN
1857 "sum_v" := 1;
1858 FOR "delegation_row" IN
1859 SELECT "area_delegation".*
1860 FROM "area_delegation" LEFT JOIN "membership"
1861 ON "membership"."area_id" = "area_id_p"
1862 AND "membership"."member_id" = "area_delegation"."truster_id"
1863 WHERE "area_delegation"."area_id" = "area_id_p"
1864 AND "area_delegation"."trustee_id" = "member_id_p"
1865 AND "membership"."member_id" ISNULL
1866 LOOP
1867 IF NOT
1868 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1869 THEN
1870 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1871 "area_id_p",
1872 "delegation_row"."truster_id",
1873 "skip_member_ids_p" || "delegation_row"."truster_id"
1874 );
1875 END IF;
1876 END LOOP;
1877 RETURN "sum_v";
1878 END;
1879 $$;
1881 COMMENT ON FUNCTION "membership_weight_with_skipping"
1882 ( "area"."id"%TYPE,
1883 "member"."id"%TYPE,
1884 INT4[] )
1885 IS 'Helper function for "membership_weight" function';
1888 CREATE FUNCTION "membership_weight"
1889 ( "area_id_p" "area"."id"%TYPE,
1890 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1891 RETURNS INT4
1892 LANGUAGE 'plpgsql' STABLE AS $$
1893 BEGIN
1894 RETURN "membership_weight_with_skipping"(
1895 "area_id_p",
1896 "member_id_p",
1897 ARRAY["member_id_p"]
1898 );
1899 END;
1900 $$;
1902 COMMENT ON FUNCTION "membership_weight"
1903 ( "area"."id"%TYPE,
1904 "member"."id"%TYPE )
1905 IS 'Calculates the potential voting weight of a member in a given area';
1908 CREATE VIEW "member_count_view" AS
1909 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1911 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1914 CREATE VIEW "unit_member_count" AS
1915 SELECT
1916 "unit"."id" AS "unit_id",
1917 count("member"."id") AS "member_count"
1918 FROM "unit"
1919 LEFT JOIN "privilege"
1920 ON "privilege"."unit_id" = "unit"."id"
1921 AND "privilege"."voting_right"
1922 LEFT JOIN "member"
1923 ON "member"."id" = "privilege"."member_id"
1924 AND "member"."active"
1925 GROUP BY "unit"."id";
1927 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
1930 CREATE VIEW "area_member_count" AS
1931 SELECT
1932 "area"."id" AS "area_id",
1933 count("member"."id") AS "direct_member_count",
1934 coalesce(
1935 sum(
1936 CASE WHEN "member"."id" NOTNULL THEN
1937 "membership_weight"("area"."id", "member"."id")
1938 ELSE 0 END
1939 )
1940 ) AS "member_weight"
1941 FROM "area"
1942 LEFT JOIN "membership"
1943 ON "area"."id" = "membership"."area_id"
1944 LEFT JOIN "privilege"
1945 ON "privilege"."unit_id" = "area"."unit_id"
1946 AND "privilege"."member_id" = "membership"."member_id"
1947 AND "privilege"."voting_right"
1948 LEFT JOIN "member"
1949 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
1950 AND "member"."active"
1951 GROUP BY "area"."id";
1953 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"';
1956 CREATE VIEW "opening_draft" AS
1957 SELECT "draft".* FROM (
1958 SELECT
1959 "initiative"."id" AS "initiative_id",
1960 min("draft"."id") AS "draft_id"
1961 FROM "initiative" JOIN "draft"
1962 ON "initiative"."id" = "draft"."initiative_id"
1963 GROUP BY "initiative"."id"
1964 ) AS "subquery"
1965 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1967 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1970 CREATE VIEW "current_draft" AS
1971 SELECT "draft".* FROM (
1972 SELECT
1973 "initiative"."id" AS "initiative_id",
1974 max("draft"."id") AS "draft_id"
1975 FROM "initiative" JOIN "draft"
1976 ON "initiative"."id" = "draft"."initiative_id"
1977 GROUP BY "initiative"."id"
1978 ) AS "subquery"
1979 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1981 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
1984 CREATE VIEW "critical_opinion" AS
1985 SELECT * FROM "opinion"
1986 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
1987 OR ("degree" = -2 AND "fulfilled" = TRUE);
1989 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
1992 CREATE VIEW "battle_participant" AS
1993 SELECT "initiative"."id", "initiative"."issue_id"
1994 FROM "issue" JOIN "initiative"
1995 ON "issue"."id" = "initiative"."issue_id"
1996 WHERE "initiative"."admitted"
1997 UNION ALL
1998 SELECT NULL, "id" AS "issue_id"
1999 FROM "issue";
2001 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
2004 CREATE VIEW "battle_view" AS
2005 SELECT
2006 "issue"."id" AS "issue_id",
2007 "winning_initiative"."id" AS "winning_initiative_id",
2008 "losing_initiative"."id" AS "losing_initiative_id",
2009 sum(
2010 CASE WHEN
2011 coalesce("better_vote"."grade", 0) >
2012 coalesce("worse_vote"."grade", 0)
2013 THEN "direct_voter"."weight" ELSE 0 END
2014 ) AS "count"
2015 FROM "issue"
2016 LEFT JOIN "direct_voter"
2017 ON "issue"."id" = "direct_voter"."issue_id"
2018 JOIN "battle_participant" AS "winning_initiative"
2019 ON "issue"."id" = "winning_initiative"."issue_id"
2020 JOIN "battle_participant" AS "losing_initiative"
2021 ON "issue"."id" = "losing_initiative"."issue_id"
2022 LEFT JOIN "vote" AS "better_vote"
2023 ON "direct_voter"."member_id" = "better_vote"."member_id"
2024 AND "winning_initiative"."id" = "better_vote"."initiative_id"
2025 LEFT JOIN "vote" AS "worse_vote"
2026 ON "direct_voter"."member_id" = "worse_vote"."member_id"
2027 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
2028 WHERE "issue"."closed" NOTNULL
2029 AND "issue"."cleaned" ISNULL
2030 AND (
2031 "winning_initiative"."id" != "losing_initiative"."id" OR
2032 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
2033 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
2034 GROUP BY
2035 "issue"."id",
2036 "winning_initiative"."id",
2037 "losing_initiative"."id";
2039 COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative (or status-quo) to another initiative (or status-quo); Used to fill "battle" table';
2042 CREATE VIEW "expired_session" AS
2043 SELECT * FROM "session" WHERE now() > "expiry";
2045 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
2046 DELETE FROM "session" WHERE "ident" = OLD."ident";
2048 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
2049 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
2052 CREATE VIEW "open_issue" AS
2053 SELECT * FROM "issue" WHERE "closed" ISNULL;
2055 COMMENT ON VIEW "open_issue" IS 'All open issues';
2058 CREATE VIEW "issue_with_ranks_missing" AS
2059 SELECT * FROM "issue"
2060 WHERE "fully_frozen" NOTNULL
2061 AND "closed" NOTNULL
2062 AND "ranks_available" = FALSE;
2064 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
2067 CREATE VIEW "member_contingent" AS
2068 SELECT
2069 "member"."id" AS "member_id",
2070 "contingent"."time_frame",
2071 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
2072 (
2073 SELECT count(1) FROM "draft"
2074 WHERE "draft"."author_id" = "member"."id"
2075 AND "draft"."created" > now() - "contingent"."time_frame"
2076 ) + (
2077 SELECT count(1) FROM "suggestion"
2078 WHERE "suggestion"."author_id" = "member"."id"
2079 AND "suggestion"."created" > now() - "contingent"."time_frame"
2080 )
2081 ELSE NULL END AS "text_entry_count",
2082 "contingent"."text_entry_limit",
2083 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
2084 SELECT count(1) FROM "opening_draft"
2085 WHERE "opening_draft"."author_id" = "member"."id"
2086 AND "opening_draft"."created" > now() - "contingent"."time_frame"
2087 ) ELSE NULL END AS "initiative_count",
2088 "contingent"."initiative_limit"
2089 FROM "member" CROSS JOIN "contingent";
2091 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
2093 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
2094 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
2097 CREATE VIEW "member_contingent_left" AS
2098 SELECT
2099 "member_id",
2100 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
2101 max("initiative_limit" - "initiative_count") AS "initiatives_left"
2102 FROM "member_contingent" GROUP BY "member_id";
2104 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.';
2107 CREATE VIEW "event_seen_by_member" AS
2108 SELECT
2109 "member"."id" AS "seen_by_member_id",
2110 CASE WHEN "event"."state" IN (
2111 'voting',
2112 'finished_without_winner',
2113 'finished_with_winner'
2114 ) THEN
2115 'voting'::"notify_level"
2116 ELSE
2117 CASE WHEN "event"."state" IN (
2118 'verification',
2119 'canceled_after_revocation_during_verification',
2120 'canceled_no_initiative_admitted'
2121 ) THEN
2122 'verification'::"notify_level"
2123 ELSE
2124 CASE WHEN "event"."state" IN (
2125 'discussion',
2126 'canceled_after_revocation_during_discussion'
2127 ) THEN
2128 'discussion'::"notify_level"
2129 ELSE
2130 'all'::"notify_level"
2131 END
2132 END
2133 END AS "notify_level",
2134 "event".*
2135 FROM "member" CROSS JOIN "event"
2136 LEFT JOIN "issue"
2137 ON "event"."issue_id" = "issue"."id"
2138 LEFT JOIN "membership"
2139 ON "member"."id" = "membership"."member_id"
2140 AND "issue"."area_id" = "membership"."area_id"
2141 LEFT JOIN "interest"
2142 ON "member"."id" = "interest"."member_id"
2143 AND "event"."issue_id" = "interest"."issue_id"
2144 LEFT JOIN "supporter"
2145 ON "member"."id" = "supporter"."member_id"
2146 AND "event"."initiative_id" = "supporter"."initiative_id"
2147 LEFT JOIN "ignored_member"
2148 ON "member"."id" = "ignored_member"."member_id"
2149 AND "event"."member_id" = "ignored_member"."other_member_id"
2150 LEFT JOIN "ignored_initiative"
2151 ON "member"."id" = "ignored_initiative"."member_id"
2152 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2153 WHERE (
2154 "supporter"."member_id" NOTNULL OR
2155 "interest"."member_id" NOTNULL OR
2156 ( "membership"."member_id" NOTNULL AND
2157 "event"."event" IN (
2158 'issue_state_changed',
2159 'initiative_created_in_new_issue',
2160 'initiative_created_in_existing_issue',
2161 'initiative_revoked' ) ) )
2162 AND "ignored_member"."member_id" ISNULL
2163 AND "ignored_initiative"."member_id" ISNULL;
2165 COMMENT ON VIEW "event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests and support, but ignoring members "notify_level"';
2168 CREATE VIEW "selected_event_seen_by_member" AS
2169 SELECT
2170 "member"."id" AS "seen_by_member_id",
2171 CASE WHEN "event"."state" IN (
2172 'voting',
2173 'finished_without_winner',
2174 'finished_with_winner'
2175 ) THEN
2176 'voting'::"notify_level"
2177 ELSE
2178 CASE WHEN "event"."state" IN (
2179 'verification',
2180 'canceled_after_revocation_during_verification',
2181 'canceled_no_initiative_admitted'
2182 ) THEN
2183 'verification'::"notify_level"
2184 ELSE
2185 CASE WHEN "event"."state" IN (
2186 'discussion',
2187 'canceled_after_revocation_during_discussion'
2188 ) THEN
2189 'discussion'::"notify_level"
2190 ELSE
2191 'all'::"notify_level"
2192 END
2193 END
2194 END AS "notify_level",
2195 "event".*
2196 FROM "member" CROSS JOIN "event"
2197 LEFT JOIN "issue"
2198 ON "event"."issue_id" = "issue"."id"
2199 LEFT JOIN "membership"
2200 ON "member"."id" = "membership"."member_id"
2201 AND "issue"."area_id" = "membership"."area_id"
2202 LEFT JOIN "interest"
2203 ON "member"."id" = "interest"."member_id"
2204 AND "event"."issue_id" = "interest"."issue_id"
2205 LEFT JOIN "supporter"
2206 ON "member"."id" = "supporter"."member_id"
2207 AND "event"."initiative_id" = "supporter"."initiative_id"
2208 LEFT JOIN "ignored_member"
2209 ON "member"."id" = "ignored_member"."member_id"
2210 AND "event"."member_id" = "ignored_member"."other_member_id"
2211 LEFT JOIN "ignored_initiative"
2212 ON "member"."id" = "ignored_initiative"."member_id"
2213 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2214 WHERE (
2215 ( "member"."notify_level" >= 'all' ) OR
2216 ( "member"."notify_level" >= 'voting' AND
2217 "event"."state" IN (
2218 'voting',
2219 'finished_without_winner',
2220 'finished_with_winner' ) ) OR
2221 ( "member"."notify_level" >= 'verification' AND
2222 "event"."state" IN (
2223 'verification',
2224 'canceled_after_revocation_during_verification',
2225 'canceled_no_initiative_admitted' ) ) OR
2226 ( "member"."notify_level" >= 'discussion' AND
2227 "event"."state" IN (
2228 'discussion',
2229 'canceled_after_revocation_during_discussion' ) ) )
2230 AND (
2231 "supporter"."member_id" NOTNULL OR
2232 "interest"."member_id" NOTNULL OR
2233 ( "membership"."member_id" NOTNULL AND
2234 "event"."event" IN (
2235 'issue_state_changed',
2236 'initiative_created_in_new_issue',
2237 'initiative_created_in_existing_issue',
2238 'initiative_revoked' ) ) )
2239 AND "ignored_member"."member_id" ISNULL
2240 AND "ignored_initiative"."member_id" ISNULL;
2242 COMMENT ON VIEW "selected_event_seen_by_member" IS 'Events as seen by a member, depending on its memberships, interests, support and members "notify_level"';
2245 CREATE TYPE "timeline_event" AS ENUM (
2246 'issue_created',
2247 'issue_canceled',
2248 'issue_accepted',
2249 'issue_half_frozen',
2250 'issue_finished_without_voting',
2251 'issue_voting_started',
2252 'issue_finished_after_voting',
2253 'initiative_created',
2254 'initiative_revoked',
2255 'draft_created',
2256 'suggestion_created');
2258 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)';
2261 CREATE VIEW "timeline_issue" AS
2262 SELECT
2263 "created" AS "occurrence",
2264 'issue_created'::"timeline_event" AS "event",
2265 "id" AS "issue_id"
2266 FROM "issue"
2267 UNION ALL
2268 SELECT
2269 "closed" AS "occurrence",
2270 'issue_canceled'::"timeline_event" AS "event",
2271 "id" AS "issue_id"
2272 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
2273 UNION ALL
2274 SELECT
2275 "accepted" AS "occurrence",
2276 'issue_accepted'::"timeline_event" AS "event",
2277 "id" AS "issue_id"
2278 FROM "issue" WHERE "accepted" NOTNULL
2279 UNION ALL
2280 SELECT
2281 "half_frozen" AS "occurrence",
2282 'issue_half_frozen'::"timeline_event" AS "event",
2283 "id" AS "issue_id"
2284 FROM "issue" WHERE "half_frozen" NOTNULL
2285 UNION ALL
2286 SELECT
2287 "fully_frozen" AS "occurrence",
2288 'issue_voting_started'::"timeline_event" AS "event",
2289 "id" AS "issue_id"
2290 FROM "issue"
2291 WHERE "fully_frozen" NOTNULL
2292 AND ("closed" ISNULL OR "closed" != "fully_frozen")
2293 UNION ALL
2294 SELECT
2295 "closed" AS "occurrence",
2296 CASE WHEN "fully_frozen" = "closed" THEN
2297 'issue_finished_without_voting'::"timeline_event"
2298 ELSE
2299 'issue_finished_after_voting'::"timeline_event"
2300 END AS "event",
2301 "id" AS "issue_id"
2302 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
2304 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)';
2307 CREATE VIEW "timeline_initiative" AS
2308 SELECT
2309 "created" AS "occurrence",
2310 'initiative_created'::"timeline_event" AS "event",
2311 "id" AS "initiative_id"
2312 FROM "initiative"
2313 UNION ALL
2314 SELECT
2315 "revoked" AS "occurrence",
2316 'initiative_revoked'::"timeline_event" AS "event",
2317 "id" AS "initiative_id"
2318 FROM "initiative" WHERE "revoked" NOTNULL;
2320 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)';
2323 CREATE VIEW "timeline_draft" AS
2324 SELECT
2325 "created" AS "occurrence",
2326 'draft_created'::"timeline_event" AS "event",
2327 "id" AS "draft_id"
2328 FROM "draft";
2330 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)';
2333 CREATE VIEW "timeline_suggestion" AS
2334 SELECT
2335 "created" AS "occurrence",
2336 'suggestion_created'::"timeline_event" AS "event",
2337 "id" AS "suggestion_id"
2338 FROM "suggestion";
2340 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)';
2343 CREATE VIEW "timeline" AS
2344 SELECT
2345 "occurrence",
2346 "event",
2347 "issue_id",
2348 NULL AS "initiative_id",
2349 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
2350 NULL::INT8 AS "suggestion_id"
2351 FROM "timeline_issue"
2352 UNION ALL
2353 SELECT
2354 "occurrence",
2355 "event",
2356 NULL AS "issue_id",
2357 "initiative_id",
2358 NULL AS "draft_id",
2359 NULL AS "suggestion_id"
2360 FROM "timeline_initiative"
2361 UNION ALL
2362 SELECT
2363 "occurrence",
2364 "event",
2365 NULL AS "issue_id",
2366 NULL AS "initiative_id",
2367 "draft_id",
2368 NULL AS "suggestion_id"
2369 FROM "timeline_draft"
2370 UNION ALL
2371 SELECT
2372 "occurrence",
2373 "event",
2374 NULL AS "issue_id",
2375 NULL AS "initiative_id",
2376 NULL AS "draft_id",
2377 "suggestion_id"
2378 FROM "timeline_suggestion";
2380 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)';
2384 ------------------------------------------------------
2385 -- Row set returning function for delegation chains --
2386 ------------------------------------------------------
2389 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
2390 ('first', 'intermediate', 'last', 'repetition');
2392 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
2395 CREATE TYPE "delegation_chain_row" AS (
2396 "index" INT4,
2397 "member_id" INT4,
2398 "member_valid" BOOLEAN,
2399 "participation" BOOLEAN,
2400 "overridden" BOOLEAN,
2401 "scope_in" "delegation_scope",
2402 "scope_out" "delegation_scope",
2403 "disabled_out" BOOLEAN,
2404 "loop" "delegation_chain_loop_tag" );
2406 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';
2408 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
2409 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';
2410 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
2411 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
2412 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
2413 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
2414 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
2417 CREATE FUNCTION "delegation_chain_for_closed_issue"
2418 ( "member_id_p" "member"."id"%TYPE,
2419 "issue_id_p" "issue"."id"%TYPE )
2420 RETURNS SETOF "delegation_chain_row"
2421 LANGUAGE 'plpgsql' STABLE AS $$
2422 DECLARE
2423 "output_row" "delegation_chain_row";
2424 "direct_voter_row" "direct_voter"%ROWTYPE;
2425 "delegating_voter_row" "delegating_voter"%ROWTYPE;
2426 BEGIN
2427 "output_row"."index" := 0;
2428 "output_row"."member_id" := "member_id_p";
2429 "output_row"."member_valid" := TRUE;
2430 "output_row"."participation" := FALSE;
2431 "output_row"."overridden" := FALSE;
2432 "output_row"."disabled_out" := FALSE;
2433 LOOP
2434 SELECT INTO "direct_voter_row" * FROM "direct_voter"
2435 WHERE "issue_id" = "issue_id_p"
2436 AND "member_id" = "output_row"."member_id";
2437 IF "direct_voter_row"."member_id" NOTNULL THEN
2438 "output_row"."participation" := TRUE;
2439 "output_row"."scope_out" := NULL;
2440 "output_row"."disabled_out" := NULL;
2441 RETURN NEXT "output_row";
2442 RETURN;
2443 END IF;
2444 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"
2445 WHERE "issue_id" = "issue_id_p"
2446 AND "member_id" = "output_row"."member_id";
2447 IF "delegating_voter_row"."member_id" ISNULL THEN
2448 RETURN;
2449 END IF;
2450 "output_row"."scope_out" := "delegating_voter_row"."scope";
2451 RETURN NEXT "output_row";
2452 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];
2453 "output_row"."scope_in" := "output_row"."scope_out";
2454 END LOOP;
2455 END;
2456 $$;
2458 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"
2459 ( "member"."id"%TYPE,
2460 "member"."id"%TYPE )
2461 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';
2464 CREATE FUNCTION "delegation_chain"
2465 ( "member_id_p" "member"."id"%TYPE,
2466 "unit_id_p" "unit"."id"%TYPE,
2467 "area_id_p" "area"."id"%TYPE,
2468 "issue_id_p" "issue"."id"%TYPE,
2469 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2470 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2471 RETURNS SETOF "delegation_chain_row"
2472 LANGUAGE 'plpgsql' STABLE AS $$
2473 DECLARE
2474 "scope_v" "delegation_scope";
2475 "unit_id_v" "unit"."id"%TYPE;
2476 "area_id_v" "area"."id"%TYPE;
2477 "issue_row" "issue"%ROWTYPE;
2478 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2479 "loop_member_id_v" "member"."id"%TYPE;
2480 "output_row" "delegation_chain_row";
2481 "output_rows" "delegation_chain_row"[];
2482 "simulate_v" BOOLEAN;
2483 "simulate_here_v" BOOLEAN;
2484 "delegation_row" "delegation"%ROWTYPE;
2485 "row_count" INT4;
2486 "i" INT4;
2487 "loop_v" BOOLEAN;
2488 BEGIN
2489 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
2490 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
2491 END IF;
2492 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
2493 "simulate_v" := TRUE;
2494 ELSE
2495 "simulate_v" := FALSE;
2496 END IF;
2497 IF
2498 "unit_id_p" NOTNULL AND
2499 "area_id_p" ISNULL AND
2500 "issue_id_p" ISNULL
2501 THEN
2502 "scope_v" := 'unit';
2503 "unit_id_v" := "unit_id_p";
2504 ELSIF
2505 "unit_id_p" ISNULL AND
2506 "area_id_p" NOTNULL AND
2507 "issue_id_p" ISNULL
2508 THEN
2509 "scope_v" := 'area';
2510 "area_id_v" := "area_id_p";
2511 SELECT "unit_id" INTO "unit_id_v"
2512 FROM "area" WHERE "id" = "area_id_v";
2513 ELSIF
2514 "unit_id_p" ISNULL AND
2515 "area_id_p" ISNULL AND
2516 "issue_id_p" NOTNULL
2517 THEN
2518 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
2519 IF "issue_row"."id" ISNULL THEN
2520 RETURN;
2521 END IF;
2522 IF "issue_row"."closed" NOTNULL THEN
2523 IF "simulate_v" THEN
2524 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
2525 END IF;
2526 FOR "output_row" IN
2527 SELECT * FROM
2528 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
2529 LOOP
2530 RETURN NEXT "output_row";
2531 END LOOP;
2532 RETURN;
2533 END IF;
2534 "scope_v" := 'issue';
2535 SELECT "area_id" INTO "area_id_v"
2536 FROM "issue" WHERE "id" = "issue_id_p";
2537 SELECT "unit_id" INTO "unit_id_v"
2538 FROM "area" WHERE "id" = "area_id_v";
2539 ELSE
2540 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2541 END IF;
2542 "visited_member_ids" := '{}';
2543 "loop_member_id_v" := NULL;
2544 "output_rows" := '{}';
2545 "output_row"."index" := 0;
2546 "output_row"."member_id" := "member_id_p";
2547 "output_row"."member_valid" := TRUE;
2548 "output_row"."participation" := FALSE;
2549 "output_row"."overridden" := FALSE;
2550 "output_row"."disabled_out" := FALSE;
2551 "output_row"."scope_out" := NULL;
2552 LOOP
2553 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2554 "loop_member_id_v" := "output_row"."member_id";
2555 ELSE
2556 "visited_member_ids" :=
2557 "visited_member_ids" || "output_row"."member_id";
2558 END IF;
2559 IF "output_row"."participation" ISNULL THEN
2560 "output_row"."overridden" := NULL;
2561 ELSIF "output_row"."participation" THEN
2562 "output_row"."overridden" := TRUE;
2563 END IF;
2564 "output_row"."scope_in" := "output_row"."scope_out";
2565 "output_row"."member_valid" := EXISTS (
2566 SELECT NULL FROM "member" JOIN "privilege"
2567 ON "privilege"."member_id" = "member"."id"
2568 AND "privilege"."unit_id" = "unit_id_v"
2569 WHERE "id" = "output_row"."member_id"
2570 AND "member"."active" AND "privilege"."voting_right"
2571 );
2572 "simulate_here_v" := (
2573 "simulate_v" AND
2574 "output_row"."member_id" = "member_id_p"
2575 );
2576 "delegation_row" := ROW(NULL);
2577 IF "output_row"."member_valid" OR "simulate_here_v" THEN
2578 IF "scope_v" = 'unit' THEN
2579 IF NOT "simulate_here_v" THEN
2580 SELECT * INTO "delegation_row" FROM "delegation"
2581 WHERE "truster_id" = "output_row"."member_id"
2582 AND "unit_id" = "unit_id_v";
2583 END IF;
2584 ELSIF "scope_v" = 'area' THEN
2585 "output_row"."participation" := EXISTS (
2586 SELECT NULL FROM "membership"
2587 WHERE "area_id" = "area_id_p"
2588 AND "member_id" = "output_row"."member_id"
2589 );
2590 IF "simulate_here_v" THEN
2591 IF "simulate_trustee_id_p" ISNULL THEN
2592 SELECT * INTO "delegation_row" FROM "delegation"
2593 WHERE "truster_id" = "output_row"."member_id"
2594 AND "unit_id" = "unit_id_v";
2595 END IF;
2596 ELSE
2597 SELECT * INTO "delegation_row" FROM "delegation"
2598 WHERE "truster_id" = "output_row"."member_id"
2599 AND (
2600 "unit_id" = "unit_id_v" OR
2601 "area_id" = "area_id_v"
2602 )
2603 ORDER BY "scope" DESC;
2604 END IF;
2605 ELSIF "scope_v" = 'issue' THEN
2606 IF "issue_row"."fully_frozen" ISNULL THEN
2607 "output_row"."participation" := EXISTS (
2608 SELECT NULL FROM "interest"
2609 WHERE "issue_id" = "issue_id_p"
2610 AND "member_id" = "output_row"."member_id"
2611 );
2612 ELSE
2613 IF "output_row"."member_id" = "member_id_p" THEN
2614 "output_row"."participation" := EXISTS (
2615 SELECT NULL FROM "direct_voter"
2616 WHERE "issue_id" = "issue_id_p"
2617 AND "member_id" = "output_row"."member_id"
2618 );
2619 ELSE
2620 "output_row"."participation" := NULL;
2621 END IF;
2622 END IF;
2623 IF "simulate_here_v" THEN
2624 IF "simulate_trustee_id_p" ISNULL THEN
2625 SELECT * INTO "delegation_row" FROM "delegation"
2626 WHERE "truster_id" = "output_row"."member_id"
2627 AND (
2628 "unit_id" = "unit_id_v" OR
2629 "area_id" = "area_id_v"
2630 )
2631 ORDER BY "scope" DESC;
2632 END IF;
2633 ELSE
2634 SELECT * INTO "delegation_row" FROM "delegation"
2635 WHERE "truster_id" = "output_row"."member_id"
2636 AND (
2637 "unit_id" = "unit_id_v" OR
2638 "area_id" = "area_id_v" OR
2639 "issue_id" = "issue_id_p"
2640 )
2641 ORDER BY "scope" DESC;
2642 END IF;
2643 END IF;
2644 ELSE
2645 "output_row"."participation" := FALSE;
2646 END IF;
2647 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
2648 "output_row"."scope_out" := "scope_v";
2649 "output_rows" := "output_rows" || "output_row";
2650 "output_row"."member_id" := "simulate_trustee_id_p";
2651 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2652 "output_row"."scope_out" := "delegation_row"."scope";
2653 "output_rows" := "output_rows" || "output_row";
2654 "output_row"."member_id" := "delegation_row"."trustee_id";
2655 ELSIF "delegation_row"."scope" NOTNULL THEN
2656 "output_row"."scope_out" := "delegation_row"."scope";
2657 "output_row"."disabled_out" := TRUE;
2658 "output_rows" := "output_rows" || "output_row";
2659 EXIT;
2660 ELSE
2661 "output_row"."scope_out" := NULL;
2662 "output_rows" := "output_rows" || "output_row";
2663 EXIT;
2664 END IF;
2665 EXIT WHEN "loop_member_id_v" NOTNULL;
2666 "output_row"."index" := "output_row"."index" + 1;
2667 END LOOP;
2668 "row_count" := array_upper("output_rows", 1);
2669 "i" := 1;
2670 "loop_v" := FALSE;
2671 LOOP
2672 "output_row" := "output_rows"["i"];
2673 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2674 IF "loop_v" THEN
2675 IF "i" + 1 = "row_count" THEN
2676 "output_row"."loop" := 'last';
2677 ELSIF "i" = "row_count" THEN
2678 "output_row"."loop" := 'repetition';
2679 ELSE
2680 "output_row"."loop" := 'intermediate';
2681 END IF;
2682 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2683 "output_row"."loop" := 'first';
2684 "loop_v" := TRUE;
2685 END IF;
2686 IF "scope_v" = 'unit' THEN
2687 "output_row"."participation" := NULL;
2688 END IF;
2689 RETURN NEXT "output_row";
2690 "i" := "i" + 1;
2691 END LOOP;
2692 RETURN;
2693 END;
2694 $$;
2696 COMMENT ON FUNCTION "delegation_chain"
2697 ( "member"."id"%TYPE,
2698 "unit"."id"%TYPE,
2699 "area"."id"%TYPE,
2700 "issue"."id"%TYPE,
2701 "member"."id"%TYPE,
2702 BOOLEAN )
2703 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
2707 ---------------------------------------------------------
2708 -- Single row returning function for delegation chains --
2709 ---------------------------------------------------------
2712 CREATE TYPE "delegation_info_loop_type" AS ENUM
2713 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');
2715 COMMENT ON TYPE "delegation_info_loop_type" IS 'Type of "delegation_loop" in "delegation_info_type"; ''own'' means loop to self, ''first'' means loop to first trustee, ''first_ellipsis'' means loop to ellipsis after first trustee, ''other'' means loop to other trustee, ''other_ellipsis'' means loop to ellipsis after other trustee''';
2718 CREATE TYPE "delegation_info_type" AS (
2719 "own_participation" BOOLEAN,
2720 "own_delegation_scope" "delegation_scope",
2721 "first_trustee_id" INT4,
2722 "first_trustee_participation" BOOLEAN,
2723 "first_trustee_ellipsis" BOOLEAN,
2724 "other_trustee_id" INT4,
2725 "other_trustee_participation" BOOLEAN,
2726 "other_trustee_ellipsis" BOOLEAN,
2727 "delegation_loop" "delegation_info_loop_type",
2728 "participating_member_id" INT4 );
2730 COMMENT ON TYPE "delegation_info_type" IS 'Type of result returned by "delegation_info" function; For meaning of "participation" check comment on "delegation_chain_row" type';
2732 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';
2733 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';
2734 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';
2735 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
2736 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';
2737 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';
2738 COMMENT ON COLUMN "delegation_info_type"."other_trustee_participation" IS 'Another trustee is participating (redundant field: if "other_trustee_id" is set, then "other_trustee_participation" is always TRUE, else "other_trustee_participation" is NULL)';
2739 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';
2740 COMMENT ON COLUMN "delegation_info_type"."delegation_loop" IS 'Non-NULL value, if delegation chain contains a circle; See comment on "delegation_info_loop_type" for details';
2741 COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain';
2744 CREATE FUNCTION "delegation_info"
2745 ( "member_id_p" "member"."id"%TYPE,
2746 "unit_id_p" "unit"."id"%TYPE,
2747 "area_id_p" "area"."id"%TYPE,
2748 "issue_id_p" "issue"."id"%TYPE,
2749 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2750 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2751 RETURNS "delegation_info_type"
2752 LANGUAGE 'plpgsql' STABLE AS $$
2753 DECLARE
2754 "current_row" "delegation_chain_row";
2755 "result" "delegation_info_type";
2756 BEGIN
2757 "result"."own_participation" := FALSE;
2758 FOR "current_row" IN
2759 SELECT * FROM "delegation_chain"(
2760 "member_id_p",
2761 "unit_id_p", "area_id_p", "issue_id_p",
2762 "simulate_trustee_id_p", "simulate_default_p")
2763 LOOP
2764 IF
2765 "result"."participating_member_id" ISNULL AND
2766 "current_row"."participation"
2767 THEN
2768 "result"."participating_member_id" := "current_row"."member_id";
2769 END IF;
2770 IF "current_row"."member_id" = "member_id_p" THEN
2771 "result"."own_participation" := "current_row"."participation";
2772 "result"."own_delegation_scope" := "current_row"."scope_out";
2773 IF "current_row"."loop" = 'first' THEN
2774 "result"."delegation_loop" := 'own';
2775 END IF;
2776 ELSIF
2777 "current_row"."member_valid" AND
2778 ( "current_row"."loop" ISNULL OR
2779 "current_row"."loop" != 'repetition' )
2780 THEN
2781 IF "result"."first_trustee_id" ISNULL THEN
2782 "result"."first_trustee_id" := "current_row"."member_id";
2783 "result"."first_trustee_participation" := "current_row"."participation";
2784 "result"."first_trustee_ellipsis" := FALSE;
2785 IF "current_row"."loop" = 'first' THEN
2786 "result"."delegation_loop" := 'first';
2787 END IF;
2788 ELSIF "result"."other_trustee_id" ISNULL THEN
2789 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
2790 "result"."other_trustee_id" := "current_row"."member_id";
2791 "result"."other_trustee_participation" := TRUE;
2792 "result"."other_trustee_ellipsis" := FALSE;
2793 IF "current_row"."loop" = 'first' THEN
2794 "result"."delegation_loop" := 'other';
2795 END IF;
2796 ELSE
2797 "result"."first_trustee_ellipsis" := TRUE;
2798 IF "current_row"."loop" = 'first' THEN
2799 "result"."delegation_loop" := 'first_ellipsis';
2800 END IF;
2801 END IF;
2802 ELSE
2803 "result"."other_trustee_ellipsis" := TRUE;
2804 IF "current_row"."loop" = 'first' THEN
2805 "result"."delegation_loop" := 'other_ellipsis';
2806 END IF;
2807 END IF;
2808 END IF;
2809 END LOOP;
2810 RETURN "result";
2811 END;
2812 $$;
2814 COMMENT ON FUNCTION "delegation_info"
2815 ( "member"."id"%TYPE,
2816 "unit"."id"%TYPE,
2817 "area"."id"%TYPE,
2818 "issue"."id"%TYPE,
2819 "member"."id"%TYPE,
2820 BOOLEAN )
2821 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
2825 ------------------------------
2826 -- Comparison by vote count --
2827 ------------------------------
2829 CREATE FUNCTION "vote_ratio"
2830 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
2831 "negative_votes_p" "initiative"."negative_votes"%TYPE )
2832 RETURNS FLOAT8
2833 LANGUAGE 'plpgsql' STABLE AS $$
2834 BEGIN
2835 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
2836 RETURN
2837 "positive_votes_p"::FLOAT8 /
2838 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
2839 ELSIF "positive_votes_p" > 0 THEN
2840 RETURN "positive_votes_p";
2841 ELSIF "negative_votes_p" > 0 THEN
2842 RETURN 1 - "negative_votes_p";
2843 ELSE
2844 RETURN 0.5;
2845 END IF;
2846 END;
2847 $$;
2849 COMMENT ON FUNCTION "vote_ratio"
2850 ( "initiative"."positive_votes"%TYPE,
2851 "initiative"."negative_votes"%TYPE )
2852 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.';
2856 ------------------------------------------------
2857 -- Locking for snapshots and voting procedure --
2858 ------------------------------------------------
2861 CREATE FUNCTION "share_row_lock_issue_trigger"()
2862 RETURNS TRIGGER
2863 LANGUAGE 'plpgsql' VOLATILE AS $$
2864 BEGIN
2865 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2866 PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;
2867 END IF;
2868 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2869 PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;
2870 RETURN NEW;
2871 ELSE
2872 RETURN OLD;
2873 END IF;
2874 END;
2875 $$;
2877 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';
2880 CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()
2881 RETURNS TRIGGER
2882 LANGUAGE 'plpgsql' VOLATILE AS $$
2883 BEGIN
2884 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2885 PERFORM NULL FROM "issue"
2886 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2887 WHERE "initiative"."id" = OLD."initiative_id"
2888 FOR SHARE OF "issue";
2889 END IF;
2890 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2891 PERFORM NULL FROM "issue"
2892 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2893 WHERE "initiative"."id" = NEW."initiative_id"
2894 FOR SHARE OF "issue";
2895 RETURN NEW;
2896 ELSE
2897 RETURN OLD;
2898 END IF;
2899 END;
2900 $$;
2902 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';
2905 CREATE TRIGGER "share_row_lock_issue"
2906 BEFORE INSERT OR UPDATE OR DELETE ON "initiative"
2907 FOR EACH ROW EXECUTE PROCEDURE
2908 "share_row_lock_issue_trigger"();
2910 CREATE TRIGGER "share_row_lock_issue"
2911 BEFORE INSERT OR UPDATE OR DELETE ON "interest"
2912 FOR EACH ROW EXECUTE PROCEDURE
2913 "share_row_lock_issue_trigger"();
2915 CREATE TRIGGER "share_row_lock_issue"
2916 BEFORE INSERT OR UPDATE OR DELETE ON "supporter"
2917 FOR EACH ROW EXECUTE PROCEDURE
2918 "share_row_lock_issue_trigger"();
2920 CREATE TRIGGER "share_row_lock_issue_via_initiative"
2921 BEFORE INSERT OR UPDATE OR DELETE ON "opinion"
2922 FOR EACH ROW EXECUTE PROCEDURE
2923 "share_row_lock_issue_via_initiative_trigger"();
2925 CREATE TRIGGER "share_row_lock_issue"
2926 BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter"
2927 FOR EACH ROW EXECUTE PROCEDURE
2928 "share_row_lock_issue_trigger"();
2930 CREATE TRIGGER "share_row_lock_issue"
2931 BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter"
2932 FOR EACH ROW EXECUTE PROCEDURE
2933 "share_row_lock_issue_trigger"();
2935 CREATE TRIGGER "share_row_lock_issue"
2936 BEFORE INSERT OR UPDATE OR DELETE ON "vote"
2937 FOR EACH ROW EXECUTE PROCEDURE
2938 "share_row_lock_issue_trigger"();
2940 COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function';
2941 COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function';
2942 COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function';
2943 COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function';
2944 COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function';
2945 COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function';
2946 COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function';
2949 CREATE FUNCTION "lock_issue"
2950 ( "issue_id_p" "issue"."id"%TYPE )
2951 RETURNS VOID
2952 LANGUAGE 'plpgsql' VOLATILE AS $$
2953 BEGIN
2954 LOCK TABLE "member" IN SHARE MODE;
2955 LOCK TABLE "privilege" IN SHARE MODE;
2956 LOCK TABLE "membership" IN SHARE MODE;
2957 LOCK TABLE "policy" IN SHARE MODE;
2958 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2959 -- NOTE: The row-level exclusive lock in combination with the
2960 -- share_row_lock_issue(_via_initiative)_trigger functions (which
2961 -- acquire a row-level share lock on the issue) ensure that no data
2962 -- is changed, which could affect calculation of snapshots or
2963 -- counting of votes. Table "delegation" must be table-level-locked,
2964 -- as it also contains issue- and global-scope delegations.
2965 LOCK TABLE "delegation" IN SHARE MODE;
2966 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
2967 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
2968 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
2969 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
2970 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
2971 RETURN;
2972 END;
2973 $$;
2975 COMMENT ON FUNCTION "lock_issue"
2976 ( "issue"."id"%TYPE )
2977 IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';
2981 ------------------------------------------------------------------------
2982 -- Regular tasks, except calculcation of snapshots and voting results --
2983 ------------------------------------------------------------------------
2985 CREATE FUNCTION "check_activity"()
2986 RETURNS VOID
2987 LANGUAGE 'plpgsql' VOLATILE AS $$
2988 DECLARE
2989 "system_setting_row" "system_setting"%ROWTYPE;
2990 BEGIN
2991 SELECT * INTO "system_setting_row" FROM "system_setting";
2992 LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
2993 IF "system_setting_row"."member_ttl" NOTNULL THEN
2994 UPDATE "member" SET "active" = FALSE
2995 WHERE "active" = TRUE
2996 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
2997 END IF;
2998 RETURN;
2999 END;
3000 $$;
3002 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
3005 CREATE FUNCTION "calculate_member_counts"()
3006 RETURNS VOID
3007 LANGUAGE 'plpgsql' VOLATILE AS $$
3008 BEGIN
3009 LOCK TABLE "member" IN SHARE MODE;
3010 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
3011 LOCK TABLE "unit" IN EXCLUSIVE MODE;
3012 LOCK TABLE "area" IN EXCLUSIVE MODE;
3013 LOCK TABLE "privilege" IN SHARE MODE;
3014 LOCK TABLE "membership" IN SHARE MODE;
3015 DELETE FROM "member_count";
3016 INSERT INTO "member_count" ("total_count")
3017 SELECT "total_count" FROM "member_count_view";
3018 UPDATE "unit" SET "member_count" = "view"."member_count"
3019 FROM "unit_member_count" AS "view"
3020 WHERE "view"."unit_id" = "unit"."id";
3021 UPDATE "area" SET
3022 "direct_member_count" = "view"."direct_member_count",
3023 "member_weight" = "view"."member_weight"
3024 FROM "area_member_count" AS "view"
3025 WHERE "view"."area_id" = "area"."id";
3026 RETURN;
3027 END;
3028 $$;
3030 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"';
3034 ------------------------------
3035 -- Calculation of snapshots --
3036 ------------------------------
3038 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
3039 ( "issue_id_p" "issue"."id"%TYPE,
3040 "member_id_p" "member"."id"%TYPE,
3041 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3042 RETURNS "direct_population_snapshot"."weight"%TYPE
3043 LANGUAGE 'plpgsql' VOLATILE AS $$
3044 DECLARE
3045 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3046 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
3047 "weight_v" INT4;
3048 "sub_weight_v" INT4;
3049 BEGIN
3050 "weight_v" := 0;
3051 FOR "issue_delegation_row" IN
3052 SELECT * FROM "issue_delegation"
3053 WHERE "trustee_id" = "member_id_p"
3054 AND "issue_id" = "issue_id_p"
3055 LOOP
3056 IF NOT EXISTS (
3057 SELECT NULL FROM "direct_population_snapshot"
3058 WHERE "issue_id" = "issue_id_p"
3059 AND "event" = 'periodic'
3060 AND "member_id" = "issue_delegation_row"."truster_id"
3061 ) AND NOT EXISTS (
3062 SELECT NULL FROM "delegating_population_snapshot"
3063 WHERE "issue_id" = "issue_id_p"
3064 AND "event" = 'periodic'
3065 AND "member_id" = "issue_delegation_row"."truster_id"
3066 ) THEN
3067 "delegate_member_ids_v" :=
3068 "member_id_p" || "delegate_member_ids_p";
3069 INSERT INTO "delegating_population_snapshot" (
3070 "issue_id",
3071 "event",
3072 "member_id",
3073 "scope",
3074 "delegate_member_ids"
3075 ) VALUES (
3076 "issue_id_p",
3077 'periodic',
3078 "issue_delegation_row"."truster_id",
3079 "issue_delegation_row"."scope",
3080 "delegate_member_ids_v"
3081 );
3082 "sub_weight_v" := 1 +
3083 "weight_of_added_delegations_for_population_snapshot"(
3084 "issue_id_p",
3085 "issue_delegation_row"."truster_id",
3086 "delegate_member_ids_v"
3087 );
3088 UPDATE "delegating_population_snapshot"
3089 SET "weight" = "sub_weight_v"
3090 WHERE "issue_id" = "issue_id_p"
3091 AND "event" = 'periodic'
3092 AND "member_id" = "issue_delegation_row"."truster_id";
3093 "weight_v" := "weight_v" + "sub_weight_v";
3094 END IF;
3095 END LOOP;
3096 RETURN "weight_v";
3097 END;
3098 $$;
3100 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
3101 ( "issue"."id"%TYPE,
3102 "member"."id"%TYPE,
3103 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3104 IS 'Helper function for "create_population_snapshot" function';
3107 CREATE FUNCTION "create_population_snapshot"
3108 ( "issue_id_p" "issue"."id"%TYPE )
3109 RETURNS VOID
3110 LANGUAGE 'plpgsql' VOLATILE AS $$
3111 DECLARE
3112 "member_id_v" "member"."id"%TYPE;
3113 BEGIN
3114 DELETE FROM "direct_population_snapshot"
3115 WHERE "issue_id" = "issue_id_p"
3116 AND "event" = 'periodic';
3117 DELETE FROM "delegating_population_snapshot"
3118 WHERE "issue_id" = "issue_id_p"
3119 AND "event" = 'periodic';
3120 INSERT INTO "direct_population_snapshot"
3121 ("issue_id", "event", "member_id")
3122 SELECT
3123 "issue_id_p" AS "issue_id",
3124 'periodic'::"snapshot_event" AS "event",
3125 "member"."id" AS "member_id"
3126 FROM "issue"
3127 JOIN "area" ON "issue"."area_id" = "area"."id"
3128 JOIN "membership" ON "area"."id" = "membership"."area_id"
3129 JOIN "member" ON "membership"."member_id" = "member"."id"
3130 JOIN "privilege"
3131 ON "privilege"."unit_id" = "area"."unit_id"
3132 AND "privilege"."member_id" = "member"."id"
3133 WHERE "issue"."id" = "issue_id_p"
3134 AND "member"."active" AND "privilege"."voting_right"
3135 UNION
3136 SELECT
3137 "issue_id_p" AS "issue_id",
3138 'periodic'::"snapshot_event" AS "event",
3139 "member"."id" AS "member_id"
3140 FROM "issue"
3141 JOIN "area" ON "issue"."area_id" = "area"."id"
3142 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3143 JOIN "member" ON "interest"."member_id" = "member"."id"
3144 JOIN "privilege"
3145 ON "privilege"."unit_id" = "area"."unit_id"
3146 AND "privilege"."member_id" = "member"."id"
3147 WHERE "issue"."id" = "issue_id_p"
3148 AND "member"."active" AND "privilege"."voting_right";
3149 FOR "member_id_v" IN
3150 SELECT "member_id" FROM "direct_population_snapshot"
3151 WHERE "issue_id" = "issue_id_p"
3152 AND "event" = 'periodic'
3153 LOOP
3154 UPDATE "direct_population_snapshot" SET
3155 "weight" = 1 +
3156 "weight_of_added_delegations_for_population_snapshot"(
3157 "issue_id_p",
3158 "member_id_v",
3159 '{}'
3160 )
3161 WHERE "issue_id" = "issue_id_p"
3162 AND "event" = 'periodic'
3163 AND "member_id" = "member_id_v";
3164 END LOOP;
3165 RETURN;
3166 END;
3167 $$;
3169 COMMENT ON FUNCTION "create_population_snapshot"
3170 ( "issue"."id"%TYPE )
3171 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.';
3174 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3175 ( "issue_id_p" "issue"."id"%TYPE,
3176 "member_id_p" "member"."id"%TYPE,
3177 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3178 RETURNS "direct_interest_snapshot"."weight"%TYPE
3179 LANGUAGE 'plpgsql' VOLATILE AS $$
3180 DECLARE
3181 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3182 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
3183 "weight_v" INT4;
3184 "sub_weight_v" INT4;
3185 BEGIN
3186 "weight_v" := 0;
3187 FOR "issue_delegation_row" IN
3188 SELECT * FROM "issue_delegation"
3189 WHERE "trustee_id" = "member_id_p"
3190 AND "issue_id" = "issue_id_p"
3191 LOOP
3192 IF NOT EXISTS (
3193 SELECT NULL FROM "direct_interest_snapshot"
3194 WHERE "issue_id" = "issue_id_p"
3195 AND "event" = 'periodic'
3196 AND "member_id" = "issue_delegation_row"."truster_id"
3197 ) AND NOT EXISTS (
3198 SELECT NULL FROM "delegating_interest_snapshot"
3199 WHERE "issue_id" = "issue_id_p"
3200 AND "event" = 'periodic'
3201 AND "member_id" = "issue_delegation_row"."truster_id"
3202 ) THEN
3203 "delegate_member_ids_v" :=
3204 "member_id_p" || "delegate_member_ids_p";
3205 INSERT INTO "delegating_interest_snapshot" (
3206 "issue_id",
3207 "event",
3208 "member_id",
3209 "scope",
3210 "delegate_member_ids"
3211 ) VALUES (
3212 "issue_id_p",
3213 'periodic',
3214 "issue_delegation_row"."truster_id",
3215 "issue_delegation_row"."scope",
3216 "delegate_member_ids_v"
3217 );
3218 "sub_weight_v" := 1 +
3219 "weight_of_added_delegations_for_interest_snapshot"(
3220 "issue_id_p",
3221 "issue_delegation_row"."truster_id",
3222 "delegate_member_ids_v"
3223 );
3224 UPDATE "delegating_interest_snapshot"
3225 SET "weight" = "sub_weight_v"
3226 WHERE "issue_id" = "issue_id_p"
3227 AND "event" = 'periodic'
3228 AND "member_id" = "issue_delegation_row"."truster_id";
3229 "weight_v" := "weight_v" + "sub_weight_v";
3230 END IF;
3231 END LOOP;
3232 RETURN "weight_v";
3233 END;
3234 $$;
3236 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3237 ( "issue"."id"%TYPE,
3238 "member"."id"%TYPE,
3239 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3240 IS 'Helper function for "create_interest_snapshot" function';
3243 CREATE FUNCTION "create_interest_snapshot"
3244 ( "issue_id_p" "issue"."id"%TYPE )
3245 RETURNS VOID
3246 LANGUAGE 'plpgsql' VOLATILE AS $$
3247 DECLARE
3248 "member_id_v" "member"."id"%TYPE;
3249 BEGIN
3250 DELETE FROM "direct_interest_snapshot"
3251 WHERE "issue_id" = "issue_id_p"
3252 AND "event" = 'periodic';
3253 DELETE FROM "delegating_interest_snapshot"
3254 WHERE "issue_id" = "issue_id_p"
3255 AND "event" = 'periodic';
3256 DELETE FROM "direct_supporter_snapshot"
3257 WHERE "issue_id" = "issue_id_p"
3258 AND "event" = 'periodic';
3259 INSERT INTO "direct_interest_snapshot"
3260 ("issue_id", "event", "member_id")
3261 SELECT
3262 "issue_id_p" AS "issue_id",
3263 'periodic' AS "event",
3264 "member"."id" AS "member_id"
3265 FROM "issue"
3266 JOIN "area" ON "issue"."area_id" = "area"."id"
3267 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3268 JOIN "member" ON "interest"."member_id" = "member"."id"
3269 JOIN "privilege"
3270 ON "privilege"."unit_id" = "area"."unit_id"
3271 AND "privilege"."member_id" = "member"."id"
3272 WHERE "issue"."id" = "issue_id_p"
3273 AND "member"."active" AND "privilege"."voting_right";
3274 FOR "member_id_v" IN
3275 SELECT "member_id" FROM "direct_interest_snapshot"
3276 WHERE "issue_id" = "issue_id_p"
3277 AND "event" = 'periodic'
3278 LOOP
3279 UPDATE "direct_interest_snapshot" SET
3280 "weight" = 1 +
3281 "weight_of_added_delegations_for_interest_snapshot"(
3282 "issue_id_p",
3283 "member_id_v",
3284 '{}'
3285 )
3286 WHERE "issue_id" = "issue_id_p"
3287 AND "event" = 'periodic'
3288 AND "member_id" = "member_id_v";
3289 END LOOP;
3290 INSERT INTO "direct_supporter_snapshot"
3291 ( "issue_id", "initiative_id", "event", "member_id",
3292 "draft_id", "informed", "satisfied" )
3293 SELECT
3294 "issue_id_p" AS "issue_id",
3295 "initiative"."id" AS "initiative_id",
3296 'periodic' AS "event",
3297 "supporter"."member_id" AS "member_id",
3298 "supporter"."draft_id" AS "draft_id",
3299 "supporter"."draft_id" = "current_draft"."id" AS "informed",
3300 NOT EXISTS (
3301 SELECT NULL FROM "critical_opinion"
3302 WHERE "initiative_id" = "initiative"."id"
3303 AND "member_id" = "supporter"."member_id"
3304 ) AS "satisfied"
3305 FROM "initiative"
3306 JOIN "supporter"
3307 ON "supporter"."initiative_id" = "initiative"."id"
3308 JOIN "current_draft"
3309 ON "initiative"."id" = "current_draft"."initiative_id"
3310 JOIN "direct_interest_snapshot"
3311 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
3312 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
3313 AND "event" = 'periodic'
3314 WHERE "initiative"."issue_id" = "issue_id_p";
3315 RETURN;
3316 END;
3317 $$;
3319 COMMENT ON FUNCTION "create_interest_snapshot"
3320 ( "issue"."id"%TYPE )
3321 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.';
3324 CREATE FUNCTION "create_snapshot"
3325 ( "issue_id_p" "issue"."id"%TYPE )
3326 RETURNS VOID
3327 LANGUAGE 'plpgsql' VOLATILE AS $$
3328 DECLARE
3329 "initiative_id_v" "initiative"."id"%TYPE;
3330 "suggestion_id_v" "suggestion"."id"%TYPE;
3331 BEGIN
3332 PERFORM "lock_issue"("issue_id_p");
3333 PERFORM "create_population_snapshot"("issue_id_p");
3334 PERFORM "create_interest_snapshot"("issue_id_p");
3335 UPDATE "issue" SET
3336 "snapshot" = now(),
3337 "latest_snapshot_event" = 'periodic',
3338 "population" = (
3339 SELECT coalesce(sum("weight"), 0)
3340 FROM "direct_population_snapshot"
3341 WHERE "issue_id" = "issue_id_p"
3342 AND "event" = 'periodic'
3343 )
3344 WHERE "id" = "issue_id_p";
3345 FOR "initiative_id_v" IN
3346 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
3347 LOOP
3348 UPDATE "initiative" SET
3349 "supporter_count" = (
3350 SELECT coalesce(sum("di"."weight"), 0)
3351 FROM "direct_interest_snapshot" AS "di"
3352 JOIN "direct_supporter_snapshot" AS "ds"
3353 ON "di"."member_id" = "ds"."member_id"
3354 WHERE "di"."issue_id" = "issue_id_p"
3355 AND "di"."event" = 'periodic'
3356 AND "ds"."initiative_id" = "initiative_id_v"
3357 AND "ds"."event" = 'periodic'
3358 ),
3359 "informed_supporter_count" = (
3360 SELECT coalesce(sum("di"."weight"), 0)
3361 FROM "direct_interest_snapshot" AS "di"
3362 JOIN "direct_supporter_snapshot" AS "ds"
3363 ON "di"."member_id" = "ds"."member_id"
3364 WHERE "di"."issue_id" = "issue_id_p"
3365 AND "di"."event" = 'periodic'
3366 AND "ds"."initiative_id" = "initiative_id_v"
3367 AND "ds"."event" = 'periodic'
3368 AND "ds"."informed"
3369 ),
3370 "satisfied_supporter_count" = (
3371 SELECT coalesce(sum("di"."weight"), 0)
3372 FROM "direct_interest_snapshot" AS "di"
3373 JOIN "direct_supporter_snapshot" AS "ds"
3374 ON "di"."member_id" = "ds"."member_id"
3375 WHERE "di"."issue_id" = "issue_id_p"
3376 AND "di"."event" = 'periodic'
3377 AND "ds"."initiative_id" = "initiative_id_v"
3378 AND "ds"."event" = 'periodic'
3379 AND "ds"."satisfied"
3380 ),
3381 "satisfied_informed_supporter_count" = (
3382 SELECT coalesce(sum("di"."weight"), 0)
3383 FROM "direct_interest_snapshot" AS "di"
3384 JOIN "direct_supporter_snapshot" AS "ds"
3385 ON "di"."member_id" = "ds"."member_id"
3386 WHERE "di"."issue_id" = "issue_id_p"
3387 AND "di"."event" = 'periodic'
3388 AND "ds"."initiative_id" = "initiative_id_v"
3389 AND "ds"."event" = 'periodic'
3390 AND "ds"."informed"
3391 AND "ds"."satisfied"
3392 )
3393 WHERE "id" = "initiative_id_v";
3394 FOR "suggestion_id_v" IN
3395 SELECT "id" FROM "suggestion"
3396 WHERE "initiative_id" = "initiative_id_v"
3397 LOOP
3398 UPDATE "suggestion" SET
3399 "minus2_unfulfilled_count" = (
3400 SELECT coalesce(sum("snapshot"."weight"), 0)
3401 FROM "issue" CROSS JOIN "opinion"
3402 JOIN "direct_interest_snapshot" AS "snapshot"
3403 ON "snapshot"."issue_id" = "issue"."id"
3404 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3405 AND "snapshot"."member_id" = "opinion"."member_id"
3406 WHERE "issue"."id" = "issue_id_p"
3407 AND "opinion"."suggestion_id" = "suggestion_id_v"
3408 AND "opinion"."degree" = -2
3409 AND "opinion"."fulfilled" = FALSE
3410 ),
3411 "minus2_fulfilled_count" = (
3412 SELECT coalesce(sum("snapshot"."weight"), 0)
3413 FROM "issue" CROSS JOIN "opinion"
3414 JOIN "direct_interest_snapshot" AS "snapshot"
3415 ON "snapshot"."issue_id" = "issue"."id"
3416 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3417 AND "snapshot"."member_id" = "opinion"."member_id"
3418 WHERE "issue"."id" = "issue_id_p"
3419 AND "opinion"."suggestion_id" = "suggestion_id_v"
3420 AND "opinion"."degree" = -2
3421 AND "opinion"."fulfilled" = TRUE
3422 ),
3423 "minus1_unfulfilled_count" = (
3424 SELECT coalesce(sum("snapshot"."weight"), 0)
3425 FROM "issue" CROSS JOIN "opinion"
3426 JOIN "direct_interest_snapshot" AS "snapshot"
3427 ON "snapshot"."issue_id" = "issue"."id"
3428 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3429 AND "snapshot"."member_id" = "opinion"."member_id"
3430 WHERE "issue"."id" = "issue_id_p"
3431 AND "opinion"."suggestion_id" = "suggestion_id_v"
3432 AND "opinion"."degree" = -1
3433 AND "opinion"."fulfilled" = FALSE
3434 ),
3435 "minus1_fulfilled_count" = (
3436 SELECT coalesce(sum("snapshot"."weight"), 0)
3437 FROM "issue" CROSS JOIN "opinion"
3438 JOIN "direct_interest_snapshot" AS "snapshot"
3439 ON "snapshot"."issue_id" = "issue"."id"
3440 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3441 AND "snapshot"."member_id" = "opinion"."member_id"
3442 WHERE "issue"."id" = "issue_id_p"
3443 AND "opinion"."suggestion_id" = "suggestion_id_v"
3444 AND "opinion"."degree" = -1
3445 AND "opinion"."fulfilled" = TRUE
3446 ),
3447 "plus1_unfulfilled_count" = (
3448 SELECT coalesce(sum("snapshot"."weight"), 0)
3449 FROM "issue" CROSS JOIN "opinion"
3450 JOIN "direct_interest_snapshot" AS "snapshot"
3451 ON "snapshot"."issue_id" = "issue"."id"
3452 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3453 AND "snapshot"."member_id" = "opinion"."member_id"
3454 WHERE "issue"."id" = "issue_id_p"
3455 AND "opinion"."suggestion_id" = "suggestion_id_v"
3456 AND "opinion"."degree" = 1
3457 AND "opinion"."fulfilled" = FALSE
3458 ),
3459 "plus1_fulfilled_count" = (
3460 SELECT coalesce(sum("snapshot"."weight"), 0)
3461 FROM "issue" CROSS JOIN "opinion"
3462 JOIN "direct_interest_snapshot" AS "snapshot"
3463 ON "snapshot"."issue_id" = "issue"."id"
3464 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3465 AND "snapshot"."member_id" = "opinion"."member_id"
3466 WHERE "issue"."id" = "issue_id_p"
3467 AND "opinion"."suggestion_id" = "suggestion_id_v"
3468 AND "opinion"."degree" = 1
3469 AND "opinion"."fulfilled" = TRUE
3470 ),
3471 "plus2_unfulfilled_count" = (
3472 SELECT coalesce(sum("snapshot"."weight"), 0)
3473 FROM "issue" CROSS JOIN "opinion"
3474 JOIN "direct_interest_snapshot" AS "snapshot"
3475 ON "snapshot"."issue_id" = "issue"."id"
3476 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3477 AND "snapshot"."member_id" = "opinion"."member_id"
3478 WHERE "issue"."id" = "issue_id_p"
3479 AND "opinion"."suggestion_id" = "suggestion_id_v"
3480 AND "opinion"."degree" = 2
3481 AND "opinion"."fulfilled" = FALSE
3482 ),
3483 "plus2_fulfilled_count" = (
3484 SELECT coalesce(sum("snapshot"."weight"), 0)
3485 FROM "issue" CROSS JOIN "opinion"
3486 JOIN "direct_interest_snapshot" AS "snapshot"
3487 ON "snapshot"."issue_id" = "issue"."id"
3488 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3489 AND "snapshot"."member_id" = "opinion"."member_id"
3490 WHERE "issue"."id" = "issue_id_p"
3491 AND "opinion"."suggestion_id" = "suggestion_id_v"
3492 AND "opinion"."degree" = 2
3493 AND "opinion"."fulfilled" = TRUE
3494 )
3495 WHERE "suggestion"."id" = "suggestion_id_v";
3496 END LOOP;
3497 END LOOP;
3498 RETURN;
3499 END;
3500 $$;
3502 COMMENT ON FUNCTION "create_snapshot"
3503 ( "issue"."id"%TYPE )
3504 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.';
3507 CREATE FUNCTION "set_snapshot_event"
3508 ( "issue_id_p" "issue"."id"%TYPE,
3509 "event_p" "snapshot_event" )
3510 RETURNS VOID
3511 LANGUAGE 'plpgsql' VOLATILE AS $$
3512 DECLARE
3513 "event_v" "issue"."latest_snapshot_event"%TYPE;
3514 BEGIN
3515 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
3516 WHERE "id" = "issue_id_p" FOR UPDATE;
3517 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
3518 WHERE "id" = "issue_id_p";
3519 UPDATE "direct_population_snapshot" SET "event" = "event_p"
3520 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3521 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
3522 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3523 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
3524 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3525 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
3526 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3527 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
3528 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3529 RETURN;
3530 END;
3531 $$;
3533 COMMENT ON FUNCTION "set_snapshot_event"
3534 ( "issue"."id"%TYPE,
3535 "snapshot_event" )
3536 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
3540 ---------------------
3541 -- Freezing issues --
3542 ---------------------
3544 CREATE FUNCTION "freeze_after_snapshot"
3545 ( "issue_id_p" "issue"."id"%TYPE )
3546 RETURNS VOID
3547 LANGUAGE 'plpgsql' VOLATILE AS $$
3548 DECLARE
3549 "issue_row" "issue"%ROWTYPE;
3550 "policy_row" "policy"%ROWTYPE;
3551 "initiative_row" "initiative"%ROWTYPE;
3552 BEGIN
3553 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3554 SELECT * INTO "policy_row"
3555 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3556 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
3557 FOR "initiative_row" IN
3558 SELECT * FROM "initiative"
3559 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3560 LOOP
3561 IF
3562 "initiative_row"."polling" OR (
3563 "initiative_row"."satisfied_supporter_count" > 0 AND
3564 "initiative_row"."satisfied_supporter_count" *
3565 "policy_row"."initiative_quorum_den" >=
3566 "issue_row"."population" * "policy_row"."initiative_quorum_num"
3567 )
3568 THEN
3569 UPDATE "initiative" SET "admitted" = TRUE
3570 WHERE "id" = "initiative_row"."id";
3571 ELSE
3572 UPDATE "initiative" SET "admitted" = FALSE
3573 WHERE "id" = "initiative_row"."id";
3574 END IF;
3575 END LOOP;
3576 IF EXISTS (
3577 SELECT NULL FROM "initiative"
3578 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
3579 ) THEN
3580 UPDATE "issue" SET
3581 "state" = 'voting',
3582 "accepted" = coalesce("accepted", now()),
3583 "half_frozen" = coalesce("half_frozen", now()),
3584 "fully_frozen" = now()
3585 WHERE "id" = "issue_id_p";
3586 ELSE
3587 UPDATE "issue" SET
3588 "state" = 'canceled_no_initiative_admitted',
3589 "accepted" = coalesce("accepted", now()),
3590 "half_frozen" = coalesce("half_frozen", now()),
3591 "fully_frozen" = now(),
3592 "closed" = now(),
3593 "ranks_available" = TRUE
3594 WHERE "id" = "issue_id_p";
3595 -- NOTE: The following DELETE statements have effect only when
3596 -- issue state has been manipulated
3597 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
3598 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
3599 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3600 END IF;
3601 RETURN;
3602 END;
3603 $$;
3605 COMMENT ON FUNCTION "freeze_after_snapshot"
3606 ( "issue"."id"%TYPE )
3607 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
3610 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
3611 RETURNS VOID
3612 LANGUAGE 'plpgsql' VOLATILE AS $$
3613 DECLARE
3614 "issue_row" "issue"%ROWTYPE;
3615 BEGIN
3616 PERFORM "create_snapshot"("issue_id_p");
3617 PERFORM "freeze_after_snapshot"("issue_id_p");
3618 RETURN;
3619 END;
3620 $$;
3622 COMMENT ON FUNCTION "manual_freeze"
3623 ( "issue"."id"%TYPE )
3624 IS 'Freeze an issue manually (fully) and start voting';
3628 -----------------------
3629 -- Counting of votes --
3630 -----------------------
3633 CREATE FUNCTION "weight_of_added_vote_delegations"
3634 ( "issue_id_p" "issue"."id"%TYPE,
3635 "member_id_p" "member"."id"%TYPE,
3636 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
3637 RETURNS "direct_voter"."weight"%TYPE
3638 LANGUAGE 'plpgsql' VOLATILE AS $$
3639 DECLARE
3640 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3641 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
3642 "weight_v" INT4;
3643 "sub_weight_v" INT4;
3644 BEGIN
3645 "weight_v" := 0;
3646 FOR "issue_delegation_row" IN
3647 SELECT * FROM "issue_delegation"
3648 WHERE "trustee_id" = "member_id_p"
3649 AND "issue_id" = "issue_id_p"
3650 LOOP
3651 IF NOT EXISTS (
3652 SELECT NULL FROM "direct_voter"
3653 WHERE "member_id" = "issue_delegation_row"."truster_id"
3654 AND "issue_id" = "issue_id_p"
3655 ) AND NOT EXISTS (
3656 SELECT NULL FROM "delegating_voter"
3657 WHERE "member_id" = "issue_delegation_row"."truster_id"
3658 AND "issue_id" = "issue_id_p"
3659 ) THEN
3660 "delegate_member_ids_v" :=
3661 "member_id_p" || "delegate_member_ids_p";
3662 INSERT INTO "delegating_voter" (
3663 "issue_id",
3664 "member_id",
3665 "scope",
3666 "delegate_member_ids"
3667 ) VALUES (
3668 "issue_id_p",
3669 "issue_delegation_row"."truster_id",
3670 "issue_delegation_row"."scope",
3671 "delegate_member_ids_v"
3672 );
3673 "sub_weight_v" := 1 +
3674 "weight_of_added_vote_delegations"(
3675 "issue_id_p",
3676 "issue_delegation_row"."truster_id",
3677 "delegate_member_ids_v"
3678 );
3679 UPDATE "delegating_voter"
3680 SET "weight" = "sub_weight_v"
3681 WHERE "issue_id" = "issue_id_p"
3682 AND "member_id" = "issue_delegation_row"."truster_id";
3683 "weight_v" := "weight_v" + "sub_weight_v";
3684 END IF;
3685 END LOOP;
3686 RETURN "weight_v";
3687 END;
3688 $$;
3690 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
3691 ( "issue"."id"%TYPE,
3692 "member"."id"%TYPE,
3693 "delegating_voter"."delegate_member_ids"%TYPE )
3694 IS 'Helper function for "add_vote_delegations" function';
3697 CREATE FUNCTION "add_vote_delegations"
3698 ( "issue_id_p" "issue"."id"%TYPE )
3699 RETURNS VOID
3700 LANGUAGE 'plpgsql' VOLATILE AS $$
3701 DECLARE
3702 "member_id_v" "member"."id"%TYPE;
3703 BEGIN
3704 FOR "member_id_v" IN
3705 SELECT "member_id" FROM "direct_voter"
3706 WHERE "issue_id" = "issue_id_p"
3707 LOOP
3708 UPDATE "direct_voter" SET
3709 "weight" = "weight" + "weight_of_added_vote_delegations"(
3710 "issue_id_p",
3711 "member_id_v",
3712 '{}'
3713 )
3714 WHERE "member_id" = "member_id_v"
3715 AND "issue_id" = "issue_id_p";
3716 END LOOP;
3717 RETURN;
3718 END;
3719 $$;
3721 COMMENT ON FUNCTION "add_vote_delegations"
3722 ( "issue_id_p" "issue"."id"%TYPE )
3723 IS 'Helper function for "close_voting" function';
3726 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
3727 RETURNS VOID
3728 LANGUAGE 'plpgsql' VOLATILE AS $$
3729 DECLARE
3730 "area_id_v" "area"."id"%TYPE;
3731 "unit_id_v" "unit"."id"%TYPE;
3732 "member_id_v" "member"."id"%TYPE;
3733 BEGIN
3734 PERFORM "lock_issue"("issue_id_p");
3735 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
3736 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
3737 -- delete delegating votes (in cases of manual reset of issue state):
3738 DELETE FROM "delegating_voter"
3739 WHERE "issue_id" = "issue_id_p";
3740 -- delete votes from non-privileged voters:
3741 DELETE FROM "direct_voter"
3742 USING (
3743 SELECT
3744 "direct_voter"."member_id"
3745 FROM "direct_voter"
3746 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
3747 LEFT JOIN "privilege"
3748 ON "privilege"."unit_id" = "unit_id_v"
3749 AND "privilege"."member_id" = "direct_voter"."member_id"
3750 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
3751 "member"."active" = FALSE OR
3752 "privilege"."voting_right" ISNULL OR
3753 "privilege"."voting_right" = FALSE
3754 )
3755 ) AS "subquery"
3756 WHERE "direct_voter"."issue_id" = "issue_id_p"
3757 AND "direct_voter"."member_id" = "subquery"."member_id";
3758 -- consider delegations:
3759 UPDATE "direct_voter" SET "weight" = 1
3760 WHERE "issue_id" = "issue_id_p";
3761 PERFORM "add_vote_delegations"("issue_id_p");
3762 -- set voter count and mark issue as being calculated:
3763 UPDATE "issue" SET
3764 "state" = 'calculation',
3765 "closed" = now(),
3766 "voter_count" = (
3767 SELECT coalesce(sum("weight"), 0)
3768 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
3769 )
3770 WHERE "id" = "issue_id_p";
3771 -- materialize battle_view:
3772 -- NOTE: "closed" column of issue must be set at this point
3773 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3774 INSERT INTO "battle" (
3775 "issue_id",
3776 "winning_initiative_id", "losing_initiative_id",
3777 "count"
3778 ) SELECT
3779 "issue_id",
3780 "winning_initiative_id", "losing_initiative_id",
3781 "count"
3782 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
3783 -- copy "positive_votes" and "negative_votes" from "battle" table:
3784 UPDATE "initiative" SET
3785 "positive_votes" = "battle_win"."count",
3786 "negative_votes" = "battle_lose"."count"
3787 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
3788 WHERE
3789 "battle_win"."issue_id" = "issue_id_p" AND
3790 "battle_win"."winning_initiative_id" = "initiative"."id" AND
3791 "battle_win"."losing_initiative_id" ISNULL AND
3792 "battle_lose"."issue_id" = "issue_id_p" AND
3793 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
3794 "battle_lose"."winning_initiative_id" ISNULL;
3795 END;
3796 $$;
3798 COMMENT ON FUNCTION "close_voting"
3799 ( "issue"."id"%TYPE )
3800 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.';
3803 CREATE FUNCTION "defeat_strength"
3804 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
3805 RETURNS INT8
3806 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3807 BEGIN
3808 IF "positive_votes_p" > "negative_votes_p" THEN
3809 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
3810 ELSIF "positive_votes_p" = "negative_votes_p" THEN
3811 RETURN 0;
3812 ELSE
3813 RETURN -1;
3814 END IF;
3815 END;
3816 $$;
3818 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';
3821 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
3822 RETURNS VOID
3823 LANGUAGE 'plpgsql' VOLATILE AS $$
3824 DECLARE
3825 "issue_row" "issue"%ROWTYPE;
3826 "policy_row" "policy"%ROWTYPE;
3827 "dimension_v" INTEGER;
3828 "vote_matrix" INT4[][]; -- absolute votes
3829 "matrix" INT8[][]; -- defeat strength / best paths
3830 "i" INTEGER;
3831 "j" INTEGER;
3832 "k" INTEGER;
3833 "battle_row" "battle"%ROWTYPE;
3834 "rank_ary" INT4[];
3835 "rank_v" INT4;
3836 "done_v" INTEGER;
3837 "winners_ary" INTEGER[];
3838 "initiative_id_v" "initiative"."id"%TYPE;
3839 BEGIN
3840 SELECT * INTO "issue_row"
3841 FROM "issue" WHERE "id" = "issue_id_p"
3842 FOR UPDATE;
3843 SELECT * INTO "policy_row"
3844 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3845 SELECT count(1) INTO "dimension_v"
3846 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
3847 -- Create "vote_matrix" with absolute number of votes in pairwise
3848 -- comparison:
3849 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
3850 "i" := 1;
3851 "j" := 2;
3852 FOR "battle_row" IN
3853 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
3854 ORDER BY
3855 "winning_initiative_id" NULLS LAST,
3856 "losing_initiative_id" NULLS LAST
3857 LOOP
3858 "vote_matrix"["i"]["j"] := "battle_row"."count";
3859 IF "j" = "dimension_v" THEN
3860 "i" := "i" + 1;
3861 "j" := 1;
3862 ELSE
3863 "j" := "j" + 1;
3864 IF "j" = "i" THEN
3865 "j" := "j" + 1;
3866 END IF;
3867 END IF;
3868 END LOOP;
3869 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
3870 RAISE EXCEPTION 'Wrong battle count (should not happen)';
3871 END IF;
3872 -- Store defeat strengths in "matrix" using "defeat_strength"
3873 -- function:
3874 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
3875 "i" := 1;
3876 LOOP
3877 "j" := 1;
3878 LOOP
3879 IF "i" != "j" THEN
3880 "matrix"["i"]["j"] := "defeat_strength"(
3881 "vote_matrix"["i"]["j"],
3882 "vote_matrix"["j"]["i"]
3883 );
3884 END IF;
3885 EXIT WHEN "j" = "dimension_v";
3886 "j" := "j" + 1;
3887 END LOOP;
3888 EXIT WHEN "i" = "dimension_v";
3889 "i" := "i" + 1;
3890 END LOOP;
3891 -- Find best paths:
3892 "i" := 1;
3893 LOOP
3894 "j" := 1;
3895 LOOP
3896 IF "i" != "j" THEN
3897 "k" := 1;
3898 LOOP
3899 IF "i" != "k" AND "j" != "k" THEN
3900 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
3901 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
3902 "matrix"["j"]["k"] := "matrix"["j"]["i"];
3903 END IF;
3904 ELSE
3905 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
3906 "matrix"["j"]["k"] := "matrix"["i"]["k"];
3907 END IF;
3908 END IF;
3909 END IF;
3910 EXIT WHEN "k" = "dimension_v";
3911 "k" := "k" + 1;
3912 END LOOP;
3913 END IF;
3914 EXIT WHEN "j" = "dimension_v";
3915 "j" := "j" + 1;
3916 END LOOP;
3917 EXIT WHEN "i" = "dimension_v";
3918 "i" := "i" + 1;
3919 END LOOP;
3920 -- Determine order of winners:
3921 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
3922 "rank_v" := 1;
3923 "done_v" := 0;
3924 LOOP
3925 "winners_ary" := '{}';
3926 "i" := 1;
3927 LOOP
3928 IF "rank_ary"["i"] ISNULL THEN
3929 "j" := 1;
3930 LOOP
3931 IF
3932 "i" != "j" AND
3933 "rank_ary"["j"] ISNULL AND
3934 "matrix"["j"]["i"] > "matrix"["i"]["j"]
3935 THEN
3936 -- someone else is better
3937 EXIT;
3938 END IF;
3939 IF "j" = "dimension_v" THEN
3940 -- noone is better
3941 "winners_ary" := "winners_ary" || "i";
3942 EXIT;
3943 END IF;
3944 "j" := "j" + 1;
3945 END LOOP;
3946 END IF;
3947 EXIT WHEN "i" = "dimension_v";
3948 "i" := "i" + 1;
3949 END LOOP;
3950 "i" := 1;
3951 LOOP
3952 "rank_ary"["winners_ary"["i"]] := "rank_v";
3953 "done_v" := "done_v" + 1;
3954 EXIT WHEN "i" = array_upper("winners_ary", 1);
3955 "i" := "i" + 1;
3956 END LOOP;
3957 EXIT WHEN "done_v" = "dimension_v";
3958 "rank_v" := "rank_v" + 1;
3959 END LOOP;
3960 -- write preliminary results:
3961 "i" := 1;
3962 FOR "initiative_id_v" IN
3963 SELECT "id" FROM "initiative"
3964 WHERE "issue_id" = "issue_id_p" AND "admitted"
3965 ORDER BY "id"
3966 LOOP
3967 UPDATE "initiative" SET
3968 "direct_majority" =
3969 CASE WHEN "policy_row"."direct_majority_strict" THEN
3970 "positive_votes" * "policy_row"."direct_majority_den" >
3971 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
3972 ELSE
3973 "positive_votes" * "policy_row"."direct_majority_den" >=
3974 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
3975 END
3976 AND "positive_votes" >= "policy_row"."direct_majority_positive"
3977 AND "issue_row"."voter_count"-"negative_votes" >=
3978 "policy_row"."direct_majority_non_negative",
3979 "indirect_majority" =
3980 CASE WHEN "policy_row"."indirect_majority_strict" THEN
3981 "positive_votes" * "policy_row"."indirect_majority_den" >
3982 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
3983 ELSE
3984 "positive_votes" * "policy_row"."indirect_majority_den" >=
3985 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
3986 END
3987 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
3988 AND "issue_row"."voter_count"-"negative_votes" >=
3989 "policy_row"."indirect_majority_non_negative",
3990 "schulze_rank" = "rank_ary"["i"],
3991 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
3992 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
3993 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
3994 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0,
3995 "eligible" = FALSE,
3996 "winner" = FALSE,
3997 "rank" = NULL -- NOTE: in cases of manual reset of issue state
3998 WHERE "id" = "initiative_id_v";
3999 "i" := "i" + 1;
4000 END LOOP;
4001 IF "i" != "dimension_v" THEN
4002 RAISE EXCEPTION 'Wrong winner count (should not happen)';
4003 END IF;
4004 -- take indirect majorities into account:
4005 LOOP
4006 UPDATE "initiative" SET "indirect_majority" = TRUE
4007 FROM (
4008 SELECT "new_initiative"."id" AS "initiative_id"
4009 FROM "initiative" "old_initiative"
4010 JOIN "initiative" "new_initiative"
4011 ON "new_initiative"."issue_id" = "issue_id_p"
4012 AND "new_initiative"."indirect_majority" = FALSE
4013 JOIN "battle" "battle_win"
4014 ON "battle_win"."issue_id" = "issue_id_p"
4015 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
4016 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
4017 JOIN "battle" "battle_lose"
4018 ON "battle_lose"."issue_id" = "issue_id_p"
4019 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
4020 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
4021 WHERE "old_initiative"."issue_id" = "issue_id_p"
4022 AND "old_initiative"."indirect_majority" = TRUE
4023 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
4024 "battle_win"."count" * "policy_row"."indirect_majority_den" >
4025 "policy_row"."indirect_majority_num" *
4026 ("battle_win"."count"+"battle_lose"."count")
4027 ELSE
4028 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
4029 "policy_row"."indirect_majority_num" *
4030 ("battle_win"."count"+"battle_lose"."count")
4031 END
4032 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
4033 AND "issue_row"."voter_count"-"battle_lose"."count" >=
4034 "policy_row"."indirect_majority_non_negative"
4035 ) AS "subquery"
4036 WHERE "id" = "subquery"."initiative_id";
4037 EXIT WHEN NOT FOUND;
4038 END LOOP;
4039 -- set "multistage_majority" for remaining matching initiatives:
4040 UPDATE "initiative" SET "multistage_majority" = TRUE
4041 FROM (
4042 SELECT "losing_initiative"."id" AS "initiative_id"
4043 FROM "initiative" "losing_initiative"
4044 JOIN "initiative" "winning_initiative"
4045 ON "winning_initiative"."issue_id" = "issue_id_p"
4046 AND "winning_initiative"."admitted"
4047 JOIN "battle" "battle_win"
4048 ON "battle_win"."issue_id" = "issue_id_p"
4049 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
4050 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
4051 JOIN "battle" "battle_lose"
4052 ON "battle_lose"."issue_id" = "issue_id_p"
4053 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
4054 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
4055 WHERE "losing_initiative"."issue_id" = "issue_id_p"
4056 AND "losing_initiative"."admitted"
4057 AND "winning_initiative"."schulze_rank" <
4058 "losing_initiative"."schulze_rank"
4059 AND "battle_win"."count" > "battle_lose"."count"
4060 AND (
4061 "battle_win"."count" > "winning_initiative"."positive_votes" OR
4062 "battle_lose"."count" < "losing_initiative"."negative_votes" )
4063 ) AS "subquery"
4064 WHERE "id" = "subquery"."initiative_id";
4065 -- mark eligible initiatives:
4066 UPDATE "initiative" SET "eligible" = TRUE
4067 WHERE "issue_id" = "issue_id_p"
4068 AND "initiative"."direct_majority"
4069 AND "initiative"."indirect_majority"
4070 AND "initiative"."better_than_status_quo"
4071 AND (
4072 "policy_row"."no_multistage_majority" = FALSE OR
4073 "initiative"."multistage_majority" = FALSE )
4074 AND (
4075 "policy_row"."no_reverse_beat_path" = FALSE OR
4076 "initiative"."reverse_beat_path" = FALSE );
4077 -- mark final winner:
4078 UPDATE "initiative" SET "winner" = TRUE
4079 FROM (
4080 SELECT "id" AS "initiative_id"
4081 FROM "initiative"
4082 WHERE "issue_id" = "issue_id_p" AND "eligible"
4083 ORDER BY
4084 "schulze_rank",
4085 "vote_ratio"("positive_votes", "negative_votes"),
4086 "id"
4087 LIMIT 1
4088 ) AS "subquery"
4089 WHERE "id" = "subquery"."initiative_id";
4090 -- write (final) ranks:
4091 "rank_v" := 1;
4092 FOR "initiative_id_v" IN
4093 SELECT "id"
4094 FROM "initiative"
4095 WHERE "issue_id" = "issue_id_p" AND "admitted"
4096 ORDER BY
4097 "winner" DESC,
4098 "eligible" DESC,
4099 "schulze_rank",
4100 "vote_ratio"("positive_votes", "negative_votes"),
4101 "id"
4102 LOOP
4103 UPDATE "initiative" SET "rank" = "rank_v"
4104 WHERE "id" = "initiative_id_v";
4105 "rank_v" := "rank_v" + 1;
4106 END LOOP;
4107 -- set schulze rank of status quo and mark issue as finished:
4108 UPDATE "issue" SET
4109 "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
4110 "state" =
4111 CASE WHEN EXISTS (
4112 SELECT NULL FROM "initiative"
4113 WHERE "issue_id" = "issue_id_p" AND "winner"
4114 ) THEN
4115 'finished_with_winner'::"issue_state"
4116 ELSE
4117 'finished_without_winner'::"issue_state"
4118 END,
4119 "ranks_available" = TRUE
4120 WHERE "id" = "issue_id_p";
4121 RETURN;
4122 END;
4123 $$;
4125 COMMENT ON FUNCTION "calculate_ranks"
4126 ( "issue"."id"%TYPE )
4127 IS 'Determine ranking (Votes have to be counted first)';
4131 -----------------------------
4132 -- Automatic state changes --
4133 -----------------------------
4136 CREATE FUNCTION "check_issue"
4137 ( "issue_id_p" "issue"."id"%TYPE )
4138 RETURNS VOID
4139 LANGUAGE 'plpgsql' VOLATILE AS $$
4140 DECLARE
4141 "issue_row" "issue"%ROWTYPE;
4142 "policy_row" "policy"%ROWTYPE;
4143 BEGIN
4144 PERFORM "lock_issue"("issue_id_p");
4145 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4146 -- only process open issues:
4147 IF "issue_row"."closed" ISNULL THEN
4148 SELECT * INTO "policy_row" FROM "policy"
4149 WHERE "id" = "issue_row"."policy_id";
4150 -- create a snapshot, unless issue is already fully frozen:
4151 IF "issue_row"."fully_frozen" ISNULL THEN
4152 PERFORM "create_snapshot"("issue_id_p");
4153 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4154 END IF;
4155 -- eventually close or accept issues, which have not been accepted:
4156 IF "issue_row"."accepted" ISNULL THEN
4157 IF EXISTS (
4158 SELECT NULL FROM "initiative"
4159 WHERE "issue_id" = "issue_id_p"
4160 AND "supporter_count" > 0
4161 AND "supporter_count" * "policy_row"."issue_quorum_den"
4162 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
4163 ) THEN
4164 -- accept issues, if supporter count is high enough
4165 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
4166 -- NOTE: "issue_row" used later
4167 "issue_row"."state" := 'discussion';
4168 "issue_row"."accepted" := now();
4169 UPDATE "issue" SET
4170 "state" = "issue_row"."state",
4171 "accepted" = "issue_row"."accepted"
4172 WHERE "id" = "issue_row"."id";
4173 ELSIF
4174 now() >= "issue_row"."created" + "issue_row"."admission_time"
4175 THEN
4176 -- close issues, if admission time has expired
4177 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
4178 UPDATE "issue" SET
4179 "state" = 'canceled_issue_not_accepted',
4180 "closed" = now()
4181 WHERE "id" = "issue_row"."id";
4182 END IF;
4183 END IF;
4184 -- eventually half freeze issues:
4185 IF
4186 -- NOTE: issue can't be closed at this point, if it has been accepted
4187 "issue_row"."accepted" NOTNULL AND
4188 "issue_row"."half_frozen" ISNULL
4189 THEN
4190 IF
4191 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
4192 THEN
4193 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
4194 -- NOTE: "issue_row" used later
4195 "issue_row"."state" := 'verification';
4196 "issue_row"."half_frozen" := now();
4197 UPDATE "issue" SET
4198 "state" = "issue_row"."state",
4199 "half_frozen" = "issue_row"."half_frozen"
4200 WHERE "id" = "issue_row"."id";
4201 END IF;
4202 END IF;
4203 -- close issues after some time, if all initiatives have been revoked:
4204 IF
4205 "issue_row"."closed" ISNULL AND
4206 NOT EXISTS (
4207 -- all initiatives are revoked
4208 SELECT NULL FROM "initiative"
4209 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
4210 ) AND (
4211 -- and issue has not been accepted yet
4212 "issue_row"."accepted" ISNULL OR
4213 NOT EXISTS (
4214 -- or no initiatives have been revoked lately
4215 SELECT NULL FROM "initiative"
4216 WHERE "issue_id" = "issue_id_p"
4217 AND now() < "revoked" + "issue_row"."verification_time"
4218 ) OR (
4219 -- or verification time has elapsed
4220 "issue_row"."half_frozen" NOTNULL AND
4221 "issue_row"."fully_frozen" ISNULL AND
4222 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
4223 )
4224 )
4225 THEN
4226 -- NOTE: "issue_row" used later
4227 IF "issue_row"."accepted" ISNULL THEN
4228 "issue_row"."state" := 'canceled_revoked_before_accepted';
4229 ELSIF "issue_row"."half_frozen" ISNULL THEN
4230 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
4231 ELSE
4232 "issue_row"."state" := 'canceled_after_revocation_during_verification';
4233 END IF;
4234 "issue_row"."closed" := now();
4235 UPDATE "issue" SET
4236 "state" = "issue_row"."state",
4237 "closed" = "issue_row"."closed"
4238 WHERE "id" = "issue_row"."id";
4239 END IF;
4240 -- fully freeze issue after verification time:
4241 IF
4242 "issue_row"."half_frozen" NOTNULL AND
4243 "issue_row"."fully_frozen" ISNULL AND
4244 "issue_row"."closed" ISNULL AND
4245 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
4246 THEN
4247 PERFORM "freeze_after_snapshot"("issue_id_p");
4248 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
4249 END IF;
4250 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4251 -- close issue by calling close_voting(...) after voting time:
4252 IF
4253 "issue_row"."closed" ISNULL AND
4254 "issue_row"."fully_frozen" NOTNULL AND
4255 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
4256 THEN
4257 PERFORM "close_voting"("issue_id_p");
4258 -- calculate ranks will not consume much time and can be done now
4259 PERFORM "calculate_ranks"("issue_id_p");
4260 END IF;
4261 END IF;
4262 RETURN;
4263 END;
4264 $$;
4266 COMMENT ON FUNCTION "check_issue"
4267 ( "issue"."id"%TYPE )
4268 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.';
4271 CREATE FUNCTION "check_everything"()
4272 RETURNS VOID
4273 LANGUAGE 'plpgsql' VOLATILE AS $$
4274 DECLARE
4275 "issue_id_v" "issue"."id"%TYPE;
4276 BEGIN
4277 DELETE FROM "expired_session";
4278 PERFORM "check_activity"();
4279 PERFORM "calculate_member_counts"();
4280 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
4281 PERFORM "check_issue"("issue_id_v");
4282 END LOOP;
4283 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
4284 PERFORM "calculate_ranks"("issue_id_v");
4285 END LOOP;
4286 RETURN;
4287 END;
4288 $$;
4290 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.';
4294 ----------------------
4295 -- Deletion of data --
4296 ----------------------
4299 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
4300 RETURNS VOID
4301 LANGUAGE 'plpgsql' VOLATILE AS $$
4302 DECLARE
4303 "issue_row" "issue"%ROWTYPE;
4304 BEGIN
4305 SELECT * INTO "issue_row"
4306 FROM "issue" WHERE "id" = "issue_id_p"
4307 FOR UPDATE;
4308 IF "issue_row"."cleaned" ISNULL THEN
4309 UPDATE "issue" SET
4310 "state" = 'voting',
4311 "closed" = NULL,
4312 "ranks_available" = FALSE
4313 WHERE "id" = "issue_id_p";
4314 DELETE FROM "voting_comment"
4315 WHERE "issue_id" = "issue_id_p";
4316 DELETE FROM "delegating_voter"
4317 WHERE "issue_id" = "issue_id_p";
4318 DELETE FROM "direct_voter"
4319 WHERE "issue_id" = "issue_id_p";
4320 DELETE FROM "delegating_interest_snapshot"
4321 WHERE "issue_id" = "issue_id_p";
4322 DELETE FROM "direct_interest_snapshot"
4323 WHERE "issue_id" = "issue_id_p";
4324 DELETE FROM "delegating_population_snapshot"
4325 WHERE "issue_id" = "issue_id_p";
4326 DELETE FROM "direct_population_snapshot"
4327 WHERE "issue_id" = "issue_id_p";
4328 DELETE FROM "non_voter"
4329 WHERE "issue_id" = "issue_id_p";
4330 DELETE FROM "delegation"
4331 WHERE "issue_id" = "issue_id_p";
4332 DELETE FROM "supporter"
4333 WHERE "issue_id" = "issue_id_p";
4334 UPDATE "issue" SET
4335 "state" = "issue_row"."state",
4336 "closed" = "issue_row"."closed",
4337 "ranks_available" = "issue_row"."ranks_available",
4338 "cleaned" = now()
4339 WHERE "id" = "issue_id_p";
4340 END IF;
4341 RETURN;
4342 END;
4343 $$;
4345 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
4348 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
4349 RETURNS VOID
4350 LANGUAGE 'plpgsql' VOLATILE AS $$
4351 BEGIN
4352 UPDATE "member" SET
4353 "last_login" = NULL,
4354 "login" = NULL,
4355 "password" = NULL,
4356 "locked" = TRUE,
4357 "active" = FALSE,
4358 "notify_email" = NULL,
4359 "notify_email_unconfirmed" = NULL,
4360 "notify_email_secret" = NULL,
4361 "notify_email_secret_expiry" = NULL,
4362 "notify_email_lock_expiry" = NULL,
4363 "password_reset_secret" = NULL,
4364 "password_reset_secret_expiry" = NULL,
4365 "organizational_unit" = NULL,
4366 "internal_posts" = NULL,
4367 "realname" = NULL,
4368 "birthday" = NULL,
4369 "address" = NULL,
4370 "email" = NULL,
4371 "xmpp_address" = NULL,
4372 "website" = NULL,
4373 "phone" = NULL,
4374 "mobile_phone" = NULL,
4375 "profession" = NULL,
4376 "external_memberships" = NULL,
4377 "external_posts" = NULL,
4378 "statement" = NULL
4379 WHERE "id" = "member_id_p";
4380 -- "text_search_data" is updated by triggers
4381 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
4382 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
4383 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
4384 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
4385 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
4386 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
4387 DELETE FROM "session" WHERE "member_id" = "member_id_p";
4388 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
4389 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
4390 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
4391 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
4392 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
4393 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
4394 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
4395 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
4396 DELETE FROM "direct_voter" USING "issue"
4397 WHERE "direct_voter"."issue_id" = "issue"."id"
4398 AND "issue"."closed" ISNULL
4399 AND "member_id" = "member_id_p";
4400 RETURN;
4401 END;
4402 $$;
4404 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)';
4407 CREATE FUNCTION "delete_private_data"()
4408 RETURNS VOID
4409 LANGUAGE 'plpgsql' VOLATILE AS $$
4410 BEGIN
4411 DELETE FROM "member" WHERE "activated" ISNULL;
4412 UPDATE "member" SET
4413 "invite_code" = NULL,
4414 "invite_code_expiry" = NULL,
4415 "admin_comment" = NULL,
4416 "last_login" = NULL,
4417 "login" = NULL,
4418 "password" = NULL,
4419 "lang" = NULL,
4420 "notify_email" = NULL,
4421 "notify_email_unconfirmed" = NULL,
4422 "notify_email_secret" = NULL,
4423 "notify_email_secret_expiry" = NULL,
4424 "notify_email_lock_expiry" = NULL,
4425 "notify_level" = NULL,
4426 "password_reset_secret" = NULL,
4427 "password_reset_secret_expiry" = NULL,
4428 "organizational_unit" = NULL,
4429 "internal_posts" = NULL,
4430 "realname" = NULL,
4431 "birthday" = NULL,
4432 "address" = NULL,
4433 "email" = NULL,
4434 "xmpp_address" = NULL,
4435 "website" = NULL,
4436 "phone" = NULL,
4437 "mobile_phone" = NULL,
4438 "profession" = NULL,
4439 "external_memberships" = NULL,
4440 "external_posts" = NULL,
4441 "formatting_engine" = NULL,
4442 "statement" = NULL;
4443 -- "text_search_data" is updated by triggers
4444 DELETE FROM "setting";
4445 DELETE FROM "setting_map";
4446 DELETE FROM "member_relation_setting";
4447 DELETE FROM "member_image";
4448 DELETE FROM "contact";
4449 DELETE FROM "ignored_member";
4450 DELETE FROM "session";
4451 DELETE FROM "area_setting";
4452 DELETE FROM "issue_setting";
4453 DELETE FROM "ignored_initiative";
4454 DELETE FROM "initiative_setting";
4455 DELETE FROM "suggestion_setting";
4456 DELETE FROM "non_voter";
4457 DELETE FROM "direct_voter" USING "issue"
4458 WHERE "direct_voter"."issue_id" = "issue"."id"
4459 AND "issue"."closed" ISNULL;
4460 RETURN;
4461 END;
4462 $$;
4464 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.';
4468 COMMIT;