liquid_feedback_core
view core.sql @ 277:028a52d23e56
Marked API tables as preliminary
| author | jbe |
|---|---|
| date | Fri Aug 10 16:50:14 2012 +0200 (2012-08-10) |
| parents | b1360dc846be |
| children | 0a42a2600a2b |
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' );
176 COMMENT ON TYPE "api_access_level" IS 'PRELIMINARY, SUBJECT TO CHANGE! Access scope for API consumers.';
179 CREATE TABLE "api_client" (
180 "id" SERIAL8 PRIMARY KEY,
181 "name" TEXT,
182 UNIQUE ("member_id", "client_identifier"),
183 "member_id" INT4 REFERENCES "member" ("id")
184 ON DELETE CASCADE ON UPDATE CASCADE,
185 "client_identifier" TEXT NOT NULL,
186 "client_secret" TEXT,
187 "code_grant" BOOLEAN NOT NULL,
188 "implicit_grant" BOOLEAN NOT NULL,
189 "client_grant" BOOLEAN NOT NULL,
190 "code_grant_validity_period" INTERVAL,
191 "code_grant_multiple" BOOLEAN,
192 "access_level" "api_access_level",
193 "client_grant_access_level" "api_access_level",
194 "last_usage" TIMESTAMPTZ NOT NULL,
195 CONSTRAINT "system_clients_require_name"
196 CHECK ("name" NOTNULL OR "member_id" ISNULL),
197 CONSTRAINT "code_grant_requires_validity_period"
198 CHECK ("code_grant"=FALSE OR "code_grant_validity_period" NOTNULL),
199 CONSTRAINT "code_or_implicit_grant_requires_access_level"
200 CHECK (("code_grant"=FALSE AND "implicit_grant"=FALSE) OR "access_level" NOTNULL),
201 CONSTRAINT "client_grant_requires_client_grant_access_level"
202 CHECK ("client_grant"=FALSE OR "client_grant_access_level" NOTNULL) );
203 CREATE UNIQUE INDEX "api_client_non_member_client_identifier_idx"
204 ON "api_client" ("client_identifier") WHERE "member_id" ISNULL;
206 COMMENT ON TABLE "api_client" IS 'PRELIMINARY, SUBJECT TO CHANGE! Registered OAuth2 client for a member';
208 COMMENT ON COLUMN "api_client"."name" IS 'Name of the client as chosen by member or administrator, NULL is allowed for unnamed member-registered clients';
209 COMMENT ON COLUMN "api_client"."member_id" IS 'Member, who registered the client for him/herself, or NULL for clients registered by administrator';
210 COMMENT ON COLUMN "api_client"."client_identifier" IS 'OAuth2 client id, also used as redirection endpoint if "code_grant" or "implicit_grant" is set to TRUE';
211 COMMENT ON COLUMN "api_client"."client_secret" IS 'Secret for client authentication';
212 COMMENT ON COLUMN "api_client"."code_grant" IS 'Enable OAuth2 Authorization Code Grant';
213 COMMENT ON COLUMN "api_client"."implicit_grant" IS 'Enable OAuth2 Implicit Grant';
214 COMMENT ON COLUMN "api_client"."client_grant" IS 'Enable OAuth2 Client Credentials Grant';
215 COMMENT ON COLUMN "api_client"."code_grant_validity_period" IS 'Maximum validity period of OAuth2 Authorization Code Grant, after which no more refresh is possible';
216 COMMENT ON COLUMN "api_client"."code_grant_multiple" IS 'If set to FALSE, invalidates previously issued OAuth2 Refresh Tokens during authorization; If set to NULL, the member is asked interactively during authorization, whether previously issued authorizations shall be revoked';
217 COMMENT ON COLUMN "api_client"."access_level" IS 'Maximum access level for OAuth2 Authorization Code Grant and Implicit Grant';
218 COMMENT ON COLUMN "api_client"."client_grant_access_level" IS 'Maximum access level for OAuth2 Authorization Code Grant and Implicit Grant';
219 COMMENT ON COLUMN "api_client"."last_usage" IS 'Date/time when this client registration was last used';
222 CREATE TABLE "api_code_grant" (
223 "id" SERIAL8 PRIMARY KEY,
224 "api_client_id" INT8 NOT NULL REFERENCES "api_client" ("id")
225 ON DELETE CASCADE ON UPDATE CASCADE,
226 "member_id" INT4 REFERENCES "member" ("id")
227 ON DELETE CASCADE ON UPDATE CASCADE,
228 "access_level" "api_access_level" NOT NULL,
229 "validity_period" INTERVAL NOT NULL,
230 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
231 "authorization_code" TEXT,
232 "refreshed" TIMESTAMPTZ,
233 "refresh_token" TEXT,
234 "old_refresh_token" TEXT,
235 CONSTRAINT "one_of_authorization_code_and_refresh_token_set"
236 CHECK ("authorization_code" NOTNULL OR "refresh_token" NOTNULL),
237 CONSTRAINT "refresh_token_if_and_only_if_refreshed"
238 CHECK ("refreshed" NOTNULL = "refresh_token" NOTNULL),
239 CONSTRAINT "old_refresh_token_requires_current_refresh_token"
240 CHECK ("refresh_token" NOTNULL OR "old_refresh_token" ISNULL) );
242 COMMENT ON TABLE "api_code_grant" IS 'PRELIMINARY, SUBJECT TO CHANGE! Issued OAuth2 authorization codes and refresh tokens';
244 COMMENT ON COLUMN "api_code_grant"."validity_period" IS 'Period after which no more refreshing is possible';
245 COMMENT ON COLUMN "api_code_grant"."created" IS 'Date/time when authorization code (or first refresh token when there is no authorization code) has been created';
246 COMMENT ON COLUMN "api_code_grant"."authorization_code" IS 'OAuth2 authorization code (only valid for a very short time after it has been created)';
247 COMMENT ON COLUMN "api_code_grant"."refreshed" IS 'Date/time of last refresh';
248 COMMENT ON COLUMN "api_code_grant"."refresh_token" IS 'OAuth2 refresh token';
251 CREATE TABLE "member_history" (
252 "id" SERIAL8 PRIMARY KEY,
253 "member_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
254 "until" TIMESTAMPTZ NOT NULL DEFAULT now(),
255 "active" BOOLEAN NOT NULL,
256 "name" TEXT NOT NULL );
257 CREATE INDEX "member_history_member_id_idx" ON "member_history" ("member_id");
259 COMMENT ON TABLE "member_history" IS 'Filled by trigger; keeps information about old names and active flag of members';
261 COMMENT ON COLUMN "member_history"."id" IS 'Primary key, which can be used to sort entries correctly (and time warp resistant)';
262 COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid';
265 CREATE TABLE "rendered_member_statement" (
266 PRIMARY KEY ("member_id", "format"),
267 "member_id" INT8 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
268 "format" TEXT,
269 "content" TEXT NOT NULL );
271 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)';
274 CREATE TABLE "setting" (
275 PRIMARY KEY ("member_id", "key"),
276 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
277 "key" TEXT NOT NULL,
278 "value" TEXT NOT NULL );
279 CREATE INDEX "setting_key_idx" ON "setting" ("key");
281 COMMENT ON TABLE "setting" IS 'Place to store a frontend specific setting for members as a string';
283 COMMENT ON COLUMN "setting"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
286 CREATE TABLE "setting_map" (
287 PRIMARY KEY ("member_id", "key", "subkey"),
288 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
289 "key" TEXT NOT NULL,
290 "subkey" TEXT NOT NULL,
291 "value" TEXT NOT NULL );
292 CREATE INDEX "setting_map_key_idx" ON "setting_map" ("key");
294 COMMENT ON TABLE "setting_map" IS 'Place to store a frontend specific setting for members as a map of key value pairs';
296 COMMENT ON COLUMN "setting_map"."key" IS 'Name of the setting, preceded by a frontend specific prefix';
297 COMMENT ON COLUMN "setting_map"."subkey" IS 'Key of a map entry';
298 COMMENT ON COLUMN "setting_map"."value" IS 'Value of a map entry';
301 CREATE TABLE "member_relation_setting" (
302 PRIMARY KEY ("member_id", "key", "other_member_id"),
303 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
304 "key" TEXT NOT NULL,
305 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
306 "value" TEXT NOT NULL );
308 COMMENT ON TABLE "member_relation_setting" IS 'Place to store a frontend specific setting related to relations between members as a string';
311 CREATE TYPE "member_image_type" AS ENUM ('photo', 'avatar');
313 COMMENT ON TYPE "member_image_type" IS 'Types of images for a member';
316 CREATE TABLE "member_image" (
317 PRIMARY KEY ("member_id", "image_type", "scaled"),
318 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
319 "image_type" "member_image_type",
320 "scaled" BOOLEAN,
321 "content_type" TEXT,
322 "data" BYTEA NOT NULL );
324 COMMENT ON TABLE "member_image" IS 'Images of members';
326 COMMENT ON COLUMN "member_image"."scaled" IS 'FALSE for original image, TRUE for scaled version of the image';
329 CREATE TABLE "member_count" (
330 "calculated" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
331 "total_count" INT4 NOT NULL );
333 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';
335 COMMENT ON COLUMN "member_count"."calculated" IS 'timestamp indicating when the total member count and area member counts were calculated';
336 COMMENT ON COLUMN "member_count"."total_count" IS 'Total count of active(!) members';
339 CREATE TABLE "contact" (
340 PRIMARY KEY ("member_id", "other_member_id"),
341 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
342 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
343 "public" BOOLEAN NOT NULL DEFAULT FALSE,
344 CONSTRAINT "cant_save_yourself_as_contact"
345 CHECK ("member_id" != "other_member_id") );
346 CREATE INDEX "contact_other_member_id_idx" ON "contact" ("other_member_id");
348 COMMENT ON TABLE "contact" IS 'Contact lists';
350 COMMENT ON COLUMN "contact"."member_id" IS 'Member having the contact list';
351 COMMENT ON COLUMN "contact"."other_member_id" IS 'Member referenced in the contact list';
352 COMMENT ON COLUMN "contact"."public" IS 'TRUE = display contact publically';
355 CREATE TABLE "ignored_member" (
356 PRIMARY KEY ("member_id", "other_member_id"),
357 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
358 "other_member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
359 CREATE INDEX "ignored_member_other_member_id_idx" ON "ignored_member" ("other_member_id");
361 COMMENT ON TABLE "ignored_member" IS 'Possibility to filter other members';
363 COMMENT ON COLUMN "ignored_member"."member_id" IS 'Member ignoring someone';
364 COMMENT ON COLUMN "ignored_member"."other_member_id" IS 'Member being ignored';
367 CREATE TABLE "session" (
368 "ident" TEXT PRIMARY KEY,
369 "additional_secret" TEXT,
370 "expiry" TIMESTAMPTZ NOT NULL DEFAULT now() + '24 hours',
371 "member_id" INT8 REFERENCES "member" ("id") ON DELETE SET NULL,
372 "lang" TEXT );
373 CREATE INDEX "session_expiry_idx" ON "session" ("expiry");
375 COMMENT ON TABLE "session" IS 'Sessions, i.e. for a web-frontend or API layer';
377 COMMENT ON COLUMN "session"."ident" IS 'Secret session identifier (i.e. random string)';
378 COMMENT ON COLUMN "session"."additional_secret" IS 'Additional field to store a secret, which can be used against CSRF attacks';
379 COMMENT ON COLUMN "session"."member_id" IS 'Reference to member, who is logged in';
380 COMMENT ON COLUMN "session"."lang" IS 'Language code of the selected language';
383 CREATE TABLE "policy" (
384 "id" SERIAL4 PRIMARY KEY,
385 "index" INT4 NOT NULL,
386 "active" BOOLEAN NOT NULL DEFAULT TRUE,
387 "name" TEXT NOT NULL UNIQUE,
388 "description" TEXT NOT NULL DEFAULT '',
389 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
390 "admission_time" INTERVAL,
391 "discussion_time" INTERVAL,
392 "verification_time" INTERVAL,
393 "voting_time" INTERVAL,
394 "issue_quorum_num" INT4 NOT NULL,
395 "issue_quorum_den" INT4 NOT NULL,
396 "initiative_quorum_num" INT4 NOT NULL,
397 "initiative_quorum_den" INT4 NOT NULL,
398 "direct_majority_num" INT4 NOT NULL DEFAULT 1,
399 "direct_majority_den" INT4 NOT NULL DEFAULT 2,
400 "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
401 "direct_majority_positive" INT4 NOT NULL DEFAULT 0,
402 "direct_majority_non_negative" INT4 NOT NULL DEFAULT 0,
403 "indirect_majority_num" INT4 NOT NULL DEFAULT 1,
404 "indirect_majority_den" INT4 NOT NULL DEFAULT 2,
405 "indirect_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE,
406 "indirect_majority_positive" INT4 NOT NULL DEFAULT 0,
407 "indirect_majority_non_negative" INT4 NOT NULL DEFAULT 0,
408 "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT TRUE,
409 "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE,
410 CONSTRAINT "timing" CHECK (
411 ( "polling" = FALSE AND
412 "admission_time" NOTNULL AND "discussion_time" NOTNULL AND
413 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
414 ( "polling" = TRUE AND
415 "admission_time" ISNULL AND "discussion_time" NOTNULL AND
416 "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR
417 ( "polling" = TRUE AND
418 "admission_time" ISNULL AND "discussion_time" ISNULL AND
419 "verification_time" ISNULL AND "voting_time" ISNULL ) ) );
420 CREATE INDEX "policy_active_idx" ON "policy" ("active");
422 COMMENT ON TABLE "policy" IS 'Policies for a particular proceeding type (timelimits, quorum)';
424 COMMENT ON COLUMN "policy"."index" IS 'Determines the order in listings';
425 COMMENT ON COLUMN "policy"."active" IS 'TRUE = policy can be used for new issues';
426 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)';
427 COMMENT ON COLUMN "policy"."admission_time" IS 'Maximum duration of issue state ''admission''; Maximum time an issue stays open without being "accepted"';
428 COMMENT ON COLUMN "policy"."discussion_time" IS 'Duration of issue state ''discussion''; Regular time until an issue is "half_frozen" after being "accepted"';
429 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"';
430 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'')';
431 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''';
432 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''';
433 COMMENT ON COLUMN "policy"."initiative_quorum_num" IS 'Numerator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
434 COMMENT ON COLUMN "policy"."initiative_quorum_den" IS 'Denominator of satisfied supporter quorum to be reached by an initiative to be "admitted" for voting';
435 COMMENT ON COLUMN "policy"."direct_majority_num" IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner';
436 COMMENT ON COLUMN "policy"."direct_majority_den" IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner';
437 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.';
438 COMMENT ON COLUMN "policy"."direct_majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner';
439 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';
440 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';
441 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';
442 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.';
443 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';
444 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';
445 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.';
446 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").';
449 CREATE TABLE "unit" (
450 "id" SERIAL4 PRIMARY KEY,
451 "parent_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
452 "active" BOOLEAN NOT NULL DEFAULT TRUE,
453 "name" TEXT NOT NULL,
454 "description" TEXT NOT NULL DEFAULT '',
455 "member_count" INT4,
456 "text_search_data" TSVECTOR );
457 CREATE INDEX "unit_root_idx" ON "unit" ("id") WHERE "parent_id" ISNULL;
458 CREATE INDEX "unit_parent_id_idx" ON "unit" ("parent_id");
459 CREATE INDEX "unit_active_idx" ON "unit" ("active");
460 CREATE INDEX "unit_text_search_data_idx" ON "unit" USING gin ("text_search_data");
461 CREATE TRIGGER "update_text_search_data"
462 BEFORE INSERT OR UPDATE ON "unit"
463 FOR EACH ROW EXECUTE PROCEDURE
464 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
465 "name", "description" );
467 COMMENT ON TABLE "unit" IS 'Organizational units organized as trees; Delegations are not inherited through these trees.';
469 COMMENT ON COLUMN "unit"."parent_id" IS 'Parent id of tree node; Multiple roots allowed';
470 COMMENT ON COLUMN "unit"."active" IS 'TRUE means new issues can be created in areas of this unit';
471 COMMENT ON COLUMN "unit"."member_count" IS 'Count of members as determined by column "voting_right" in table "privilege"';
474 CREATE TABLE "unit_setting" (
475 PRIMARY KEY ("member_id", "key", "unit_id"),
476 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
477 "key" TEXT NOT NULL,
478 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
479 "value" TEXT NOT NULL );
481 COMMENT ON TABLE "unit_setting" IS 'Place for frontend to store unit specific settings of members as strings';
484 CREATE TABLE "area" (
485 "id" SERIAL4 PRIMARY KEY,
486 "unit_id" INT4 NOT NULL REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
487 "active" BOOLEAN NOT NULL DEFAULT TRUE,
488 "name" TEXT NOT NULL,
489 "description" TEXT NOT NULL DEFAULT '',
490 "direct_member_count" INT4,
491 "member_weight" INT4,
492 "text_search_data" TSVECTOR );
493 CREATE INDEX "area_unit_id_idx" ON "area" ("unit_id");
494 CREATE INDEX "area_active_idx" ON "area" ("active");
495 CREATE INDEX "area_text_search_data_idx" ON "area" USING gin ("text_search_data");
496 CREATE TRIGGER "update_text_search_data"
497 BEFORE INSERT OR UPDATE ON "area"
498 FOR EACH ROW EXECUTE PROCEDURE
499 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
500 "name", "description" );
502 COMMENT ON TABLE "area" IS 'Subject areas';
504 COMMENT ON COLUMN "area"."active" IS 'TRUE means new issues can be created in this area';
505 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"';
506 COMMENT ON COLUMN "area"."member_weight" IS 'Same as "direct_member_count" but respecting delegations';
509 CREATE TABLE "area_setting" (
510 PRIMARY KEY ("member_id", "key", "area_id"),
511 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
512 "key" TEXT NOT NULL,
513 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
514 "value" TEXT NOT NULL );
516 COMMENT ON TABLE "area_setting" IS 'Place for frontend to store area specific settings of members as strings';
519 CREATE TABLE "allowed_policy" (
520 PRIMARY KEY ("area_id", "policy_id"),
521 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
522 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
523 "default_policy" BOOLEAN NOT NULL DEFAULT FALSE );
524 CREATE UNIQUE INDEX "allowed_policy_one_default_per_area_idx" ON "allowed_policy" ("area_id") WHERE "default_policy";
526 COMMENT ON TABLE "allowed_policy" IS 'Selects which policies can be used in each area';
528 COMMENT ON COLUMN "allowed_policy"."default_policy" IS 'One policy per area can be set as default.';
531 CREATE TYPE "snapshot_event" AS ENUM ('periodic', 'end_of_admission', 'half_freeze', 'full_freeze');
533 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';
536 CREATE TYPE "issue_state" AS ENUM (
537 'admission', 'discussion', 'verification', 'voting',
538 'canceled_revoked_before_accepted',
539 'canceled_issue_not_accepted',
540 'canceled_after_revocation_during_discussion',
541 'canceled_after_revocation_during_verification',
542 'calculation',
543 'canceled_no_initiative_admitted',
544 'finished_without_winner', 'finished_with_winner');
546 COMMENT ON TYPE "issue_state" IS 'State of issues';
549 CREATE TABLE "issue" (
550 "id" SERIAL4 PRIMARY KEY,
551 "area_id" INT4 NOT NULL REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
552 "policy_id" INT4 NOT NULL REFERENCES "policy" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
553 "state" "issue_state" NOT NULL DEFAULT 'admission',
554 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
555 "accepted" TIMESTAMPTZ,
556 "half_frozen" TIMESTAMPTZ,
557 "fully_frozen" TIMESTAMPTZ,
558 "closed" TIMESTAMPTZ,
559 "ranks_available" BOOLEAN NOT NULL DEFAULT FALSE,
560 "cleaned" TIMESTAMPTZ,
561 "admission_time" INTERVAL NOT NULL,
562 "discussion_time" INTERVAL NOT NULL,
563 "verification_time" INTERVAL NOT NULL,
564 "voting_time" INTERVAL NOT NULL,
565 "snapshot" TIMESTAMPTZ,
566 "latest_snapshot_event" "snapshot_event",
567 "population" INT4,
568 "voter_count" INT4,
569 "status_quo_schulze_rank" INT4,
570 CONSTRAINT "valid_state" CHECK ((
571 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
572 ("accepted" ISNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
573 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
574 ("accepted" NOTNULL AND "half_frozen" ISNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
575 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
576 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
577 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" ISNULL AND "ranks_available" = FALSE) OR
578 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = FALSE) OR
579 ("accepted" NOTNULL AND "half_frozen" NOTNULL AND "fully_frozen" NOTNULL AND "closed" NOTNULL AND "ranks_available" = TRUE)) AND (
580 ("state" = 'admission' AND "closed" ISNULL AND "accepted" ISNULL) OR
581 ("state" = 'discussion' AND "closed" ISNULL AND "accepted" NOTNULL AND "half_frozen" ISNULL) OR
582 ("state" = 'verification' AND "closed" ISNULL AND "half_frozen" NOTNULL AND "fully_frozen" ISNULL) OR
583 ("state" = 'voting' AND "closed" ISNULL AND "fully_frozen" NOTNULL) OR
584 ("state" = 'canceled_revoked_before_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
585 ("state" = 'canceled_issue_not_accepted' AND "closed" NOTNULL AND "accepted" ISNULL) OR
586 ("state" = 'canceled_after_revocation_during_discussion' AND "closed" NOTNULL AND "half_frozen" ISNULL) OR
587 ("state" = 'canceled_after_revocation_during_verification' AND "closed" NOTNULL AND "fully_frozen" ISNULL) OR
588 ("state" = 'calculation' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = FALSE) OR
589 ("state" = 'canceled_no_initiative_admitted' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
590 ("state" = 'finished_without_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE) OR
591 ("state" = 'finished_with_winner' AND "closed" NOTNULL AND "fully_frozen" NOTNULL AND "ranks_available" = TRUE)
592 )),
593 CONSTRAINT "state_change_order" CHECK (
594 "created" <= "accepted" AND
595 "accepted" <= "half_frozen" AND
596 "half_frozen" <= "fully_frozen" AND
597 "fully_frozen" <= "closed" ),
598 CONSTRAINT "only_closed_issues_may_be_cleaned" CHECK (
599 "cleaned" ISNULL OR "closed" NOTNULL ),
600 CONSTRAINT "last_snapshot_on_full_freeze"
601 CHECK ("snapshot" = "fully_frozen"), -- NOTE: snapshot can be set, while frozen is NULL yet
602 CONSTRAINT "freeze_requires_snapshot"
603 CHECK ("fully_frozen" ISNULL OR "snapshot" NOTNULL),
604 CONSTRAINT "set_both_or_none_of_snapshot_and_latest_snapshot_event"
605 CHECK ("snapshot" NOTNULL = "latest_snapshot_event" NOTNULL) );
606 CREATE INDEX "issue_area_id_idx" ON "issue" ("area_id");
607 CREATE INDEX "issue_policy_id_idx" ON "issue" ("policy_id");
608 CREATE INDEX "issue_created_idx" ON "issue" ("created");
609 CREATE INDEX "issue_accepted_idx" ON "issue" ("accepted");
610 CREATE INDEX "issue_half_frozen_idx" ON "issue" ("half_frozen");
611 CREATE INDEX "issue_fully_frozen_idx" ON "issue" ("fully_frozen");
612 CREATE INDEX "issue_closed_idx" ON "issue" ("closed");
613 CREATE INDEX "issue_created_idx_open" ON "issue" ("created") WHERE "closed" ISNULL;
614 CREATE INDEX "issue_closed_idx_canceled" ON "issue" ("closed") WHERE "fully_frozen" ISNULL;
616 COMMENT ON TABLE "issue" IS 'Groups of initiatives';
618 COMMENT ON COLUMN "issue"."accepted" IS 'Point in time, when one initiative of issue reached the "issue_quorum"';
619 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.';
620 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.';
621 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.';
622 COMMENT ON COLUMN "issue"."ranks_available" IS 'TRUE = ranks have been calculated';
623 COMMENT ON COLUMN "issue"."cleaned" IS 'Point in time, when discussion data and votes had been deleted';
624 COMMENT ON COLUMN "issue"."admission_time" IS 'Copied from "policy" table at creation of issue';
625 COMMENT ON COLUMN "issue"."discussion_time" IS 'Copied from "policy" table at creation of issue';
626 COMMENT ON COLUMN "issue"."verification_time" IS 'Copied from "policy" table at creation of issue';
627 COMMENT ON COLUMN "issue"."voting_time" IS 'Copied from "policy" table at creation of issue';
628 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
629 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';
630 COMMENT ON COLUMN "issue"."population" IS 'Sum of "weight" column in table "direct_population_snapshot"';
631 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';
632 COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
635 CREATE TABLE "issue_setting" (
636 PRIMARY KEY ("member_id", "key", "issue_id"),
637 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
638 "key" TEXT NOT NULL,
639 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
640 "value" TEXT NOT NULL );
642 COMMENT ON TABLE "issue_setting" IS 'Place for frontend to store issue specific settings of members as strings';
645 CREATE TABLE "initiative" (
646 UNIQUE ("issue_id", "id"), -- index needed for foreign-key on table "vote"
647 "issue_id" INT4 NOT NULL REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
648 "id" SERIAL4 PRIMARY KEY,
649 "name" TEXT NOT NULL,
650 "polling" BOOLEAN NOT NULL DEFAULT FALSE,
651 "discussion_url" TEXT,
652 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
653 "revoked" TIMESTAMPTZ,
654 "revoked_by_member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
655 "suggested_initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
656 "admitted" BOOLEAN,
657 "supporter_count" INT4,
658 "informed_supporter_count" INT4,
659 "satisfied_supporter_count" INT4,
660 "satisfied_informed_supporter_count" INT4,
661 "positive_votes" INT4,
662 "negative_votes" INT4,
663 "direct_majority" BOOLEAN,
664 "indirect_majority" BOOLEAN,
665 "schulze_rank" INT4,
666 "better_than_status_quo" BOOLEAN,
667 "worse_than_status_quo" BOOLEAN,
668 "reverse_beat_path" BOOLEAN,
669 "multistage_majority" BOOLEAN,
670 "eligible" BOOLEAN,
671 "winner" BOOLEAN,
672 "rank" INT4,
673 "text_search_data" TSVECTOR,
674 CONSTRAINT "all_or_none_of_revoked_and_revoked_by_member_id_must_be_null"
675 CHECK ("revoked" NOTNULL = "revoked_by_member_id" NOTNULL),
676 CONSTRAINT "non_revoked_initiatives_cant_suggest_other"
677 CHECK ("revoked" NOTNULL OR "suggested_initiative_id" ISNULL),
678 CONSTRAINT "revoked_initiatives_cant_be_admitted"
679 CHECK ("revoked" ISNULL OR "admitted" ISNULL),
680 CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
681 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
682 ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND
683 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
684 "schulze_rank" ISNULL AND
685 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
686 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
687 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) ),
688 CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo")),
689 CONSTRAINT "minimum_requirement_to_be_eligible" CHECK (
690 "eligible" = FALSE OR
691 ("direct_majority" AND "indirect_majority" AND "better_than_status_quo") ),
692 CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE),
693 CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1),
694 CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE),
695 CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank") );
696 CREATE INDEX "initiative_created_idx" ON "initiative" ("created");
697 CREATE INDEX "initiative_revoked_idx" ON "initiative" ("revoked");
698 CREATE INDEX "initiative_text_search_data_idx" ON "initiative" USING gin ("text_search_data");
699 CREATE TRIGGER "update_text_search_data"
700 BEFORE INSERT OR UPDATE ON "initiative"
701 FOR EACH ROW EXECUTE PROCEDURE
702 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
703 "name", "discussion_url");
705 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.';
707 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';
708 COMMENT ON COLUMN "initiative"."discussion_url" IS 'URL pointing to a discussion platform for this initiative';
709 COMMENT ON COLUMN "initiative"."revoked" IS 'Point in time, when one initiator decided to revoke the initiative';
710 COMMENT ON COLUMN "initiative"."revoked_by_member_id" IS 'Member, who decided to revoke the initiative';
711 COMMENT ON COLUMN "initiative"."admitted" IS 'TRUE, if initiative reaches the "initiative_quorum" when freezing the issue';
712 COMMENT ON COLUMN "initiative"."supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
713 COMMENT ON COLUMN "initiative"."informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
714 COMMENT ON COLUMN "initiative"."satisfied_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
715 COMMENT ON COLUMN "initiative"."satisfied_informed_supporter_count" IS 'Calculated from table "direct_supporter_snapshot"';
716 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Calculated from table "direct_voter"';
717 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Calculated from table "direct_voter"';
718 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"';
719 COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths';
720 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking without tie-breaking';
721 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)';
722 COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)';
723 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';
724 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';
725 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"';
726 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"';
727 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';
730 CREATE TABLE "battle" (
731 "issue_id" INT4 NOT NULL,
732 "winning_initiative_id" INT4,
733 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
734 "losing_initiative_id" INT4,
735 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
736 "count" INT4 NOT NULL,
737 CONSTRAINT "initiative_ids_not_equal" CHECK (
738 "winning_initiative_id" != "losing_initiative_id" OR
739 ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
740 ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) ) );
741 CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
742 CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
743 CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
745 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';
748 CREATE TABLE "ignored_initiative" (
749 PRIMARY KEY ("initiative_id", "member_id"),
750 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
751 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
752 CREATE INDEX "ignored_initiative_member_id_idx" ON "ignored_initiative" ("member_id");
754 COMMENT ON TABLE "ignored_initiative" IS 'Possibility to filter initiatives';
757 CREATE TABLE "initiative_setting" (
758 PRIMARY KEY ("member_id", "key", "initiative_id"),
759 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
760 "key" TEXT NOT NULL,
761 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
762 "value" TEXT NOT NULL );
764 COMMENT ON TABLE "initiative_setting" IS 'Place for frontend to store initiative specific settings of members as strings';
767 CREATE TABLE "draft" (
768 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "supporter"
769 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
770 "id" SERIAL8 PRIMARY KEY,
771 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
772 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
773 "formatting_engine" TEXT,
774 "content" TEXT NOT NULL,
775 "text_search_data" TSVECTOR );
776 CREATE INDEX "draft_created_idx" ON "draft" ("created");
777 CREATE INDEX "draft_author_id_created_idx" ON "draft" ("author_id", "created");
778 CREATE INDEX "draft_text_search_data_idx" ON "draft" USING gin ("text_search_data");
779 CREATE TRIGGER "update_text_search_data"
780 BEFORE INSERT OR UPDATE ON "draft"
781 FOR EACH ROW EXECUTE PROCEDURE
782 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
784 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.';
786 COMMENT ON COLUMN "draft"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used';
787 COMMENT ON COLUMN "draft"."content" IS 'Text of the draft in a format depending on the field "formatting_engine"';
790 CREATE TABLE "rendered_draft" (
791 PRIMARY KEY ("draft_id", "format"),
792 "draft_id" INT8 REFERENCES "draft" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
793 "format" TEXT,
794 "content" TEXT NOT NULL );
796 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)';
799 CREATE TABLE "suggestion" (
800 UNIQUE ("initiative_id", "id"), -- index needed for foreign-key on table "opinion"
801 "initiative_id" INT4 NOT NULL REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
802 "id" SERIAL8 PRIMARY KEY,
803 "draft_id" INT8 NOT NULL,
804 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
805 "created" TIMESTAMPTZ NOT NULL DEFAULT now(),
806 "author_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
807 "name" TEXT NOT NULL,
808 "formatting_engine" TEXT,
809 "content" TEXT NOT NULL DEFAULT '',
810 "text_search_data" TSVECTOR,
811 "minus2_unfulfilled_count" INT4,
812 "minus2_fulfilled_count" INT4,
813 "minus1_unfulfilled_count" INT4,
814 "minus1_fulfilled_count" INT4,
815 "plus1_unfulfilled_count" INT4,
816 "plus1_fulfilled_count" INT4,
817 "plus2_unfulfilled_count" INT4,
818 "plus2_fulfilled_count" INT4 );
819 CREATE INDEX "suggestion_created_idx" ON "suggestion" ("created");
820 CREATE INDEX "suggestion_author_id_created_idx" ON "suggestion" ("author_id", "created");
821 CREATE INDEX "suggestion_text_search_data_idx" ON "suggestion" USING gin ("text_search_data");
822 CREATE TRIGGER "update_text_search_data"
823 BEFORE INSERT OR UPDATE ON "suggestion"
824 FOR EACH ROW EXECUTE PROCEDURE
825 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
826 "name", "content");
828 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';
830 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")';
831 COMMENT ON COLUMN "suggestion"."minus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
832 COMMENT ON COLUMN "suggestion"."minus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
833 COMMENT ON COLUMN "suggestion"."minus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
834 COMMENT ON COLUMN "suggestion"."minus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
835 COMMENT ON COLUMN "suggestion"."plus1_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
836 COMMENT ON COLUMN "suggestion"."plus1_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
837 COMMENT ON COLUMN "suggestion"."plus2_unfulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
838 COMMENT ON COLUMN "suggestion"."plus2_fulfilled_count" IS 'Calculated from table "direct_supporter_snapshot", not requiring informed supporters';
841 CREATE TABLE "rendered_suggestion" (
842 PRIMARY KEY ("suggestion_id", "format"),
843 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
844 "format" TEXT,
845 "content" TEXT NOT NULL );
847 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)';
850 CREATE TABLE "suggestion_setting" (
851 PRIMARY KEY ("member_id", "key", "suggestion_id"),
852 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
853 "key" TEXT NOT NULL,
854 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
855 "value" TEXT NOT NULL );
857 COMMENT ON TABLE "suggestion_setting" IS 'Place for frontend to store suggestion specific settings of members as strings';
860 CREATE TABLE "privilege" (
861 PRIMARY KEY ("unit_id", "member_id"),
862 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
863 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
864 "admin_manager" BOOLEAN NOT NULL DEFAULT FALSE,
865 "unit_manager" BOOLEAN NOT NULL DEFAULT FALSE,
866 "area_manager" BOOLEAN NOT NULL DEFAULT FALSE,
867 "member_manager" BOOLEAN NOT NULL DEFAULT FALSE,
868 "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE,
869 "voting_right" BOOLEAN NOT NULL DEFAULT TRUE,
870 "polling_right" BOOLEAN NOT NULL DEFAULT FALSE );
872 COMMENT ON TABLE "privilege" IS 'Members rights related to each unit';
874 COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke any privileges to/from other members';
875 COMMENT ON COLUMN "privilege"."unit_manager" IS 'Create and disable sub units';
876 COMMENT ON COLUMN "privilege"."area_manager" IS 'Create and disable areas and set area parameters';
877 COMMENT ON COLUMN "privilege"."member_manager" IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"';
878 COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative';
879 COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to support initiatives, create and rate suggestions, and to vote';
880 COMMENT ON COLUMN "privilege"."polling_right" IS 'Right to create polls (see "policy"."polling" and "initiative"."polling")';
883 CREATE TABLE "membership" (
884 PRIMARY KEY ("area_id", "member_id"),
885 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
886 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
887 CREATE INDEX "membership_member_id_idx" ON "membership" ("member_id");
889 COMMENT ON TABLE "membership" IS 'Interest of members in topic areas';
892 CREATE TABLE "interest" (
893 PRIMARY KEY ("issue_id", "member_id"),
894 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
895 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
896 CREATE INDEX "interest_member_id_idx" ON "interest" ("member_id");
898 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.';
901 CREATE TABLE "initiator" (
902 PRIMARY KEY ("initiative_id", "member_id"),
903 "initiative_id" INT4 REFERENCES "initiative" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
904 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
905 "accepted" BOOLEAN );
906 CREATE INDEX "initiator_member_id_idx" ON "initiator" ("member_id");
908 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.';
910 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.';
913 CREATE TABLE "supporter" (
914 "issue_id" INT4 NOT NULL,
915 PRIMARY KEY ("initiative_id", "member_id"),
916 "initiative_id" INT4,
917 "member_id" INT4,
918 "draft_id" INT8 NOT NULL,
919 FOREIGN KEY ("issue_id", "member_id") REFERENCES "interest" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE,
920 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE );
921 CREATE INDEX "supporter_member_id_idx" ON "supporter" ("member_id");
923 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.';
925 COMMENT ON COLUMN "supporter"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
926 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")';
929 CREATE TABLE "opinion" (
930 "initiative_id" INT4 NOT NULL,
931 PRIMARY KEY ("suggestion_id", "member_id"),
932 "suggestion_id" INT8,
933 "member_id" INT4,
934 "degree" INT2 NOT NULL CHECK ("degree" >= -2 AND "degree" <= 2 AND "degree" != 0),
935 "fulfilled" BOOLEAN NOT NULL DEFAULT FALSE,
936 FOREIGN KEY ("initiative_id", "suggestion_id") REFERENCES "suggestion" ("initiative_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
937 FOREIGN KEY ("initiative_id", "member_id") REFERENCES "supporter" ("initiative_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
938 CREATE INDEX "opinion_member_id_initiative_id_idx" ON "opinion" ("member_id", "initiative_id");
940 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.';
942 COMMENT ON COLUMN "opinion"."degree" IS '2 = fulfillment required for support; 1 = fulfillment desired; -1 = fulfillment unwanted; -2 = fulfillment cancels support';
945 CREATE TYPE "delegation_scope" AS ENUM ('unit', 'area', 'issue');
947 COMMENT ON TYPE "delegation_scope" IS 'Scope for delegations: ''unit'', ''area'', or ''issue'' (order is relevant)';
950 CREATE TABLE "delegation" (
951 "id" SERIAL8 PRIMARY KEY,
952 "truster_id" INT4 NOT NULL REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
953 "trustee_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
954 "scope" "delegation_scope" NOT NULL,
955 "unit_id" INT4 REFERENCES "unit" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
956 "area_id" INT4 REFERENCES "area" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
957 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
958 CONSTRAINT "cant_delegate_to_yourself" CHECK ("truster_id" != "trustee_id"),
959 CONSTRAINT "no_unit_delegation_to_null"
960 CHECK ("trustee_id" NOTNULL OR "scope" != 'unit'),
961 CONSTRAINT "area_id_and_issue_id_set_according_to_scope" CHECK (
962 ("scope" = 'unit' AND "unit_id" NOTNULL AND "area_id" ISNULL AND "issue_id" ISNULL ) OR
963 ("scope" = 'area' AND "unit_id" ISNULL AND "area_id" NOTNULL AND "issue_id" ISNULL ) OR
964 ("scope" = 'issue' AND "unit_id" ISNULL AND "area_id" ISNULL AND "issue_id" NOTNULL) ),
965 UNIQUE ("unit_id", "truster_id"),
966 UNIQUE ("area_id", "truster_id"),
967 UNIQUE ("issue_id", "truster_id") );
968 CREATE INDEX "delegation_truster_id_idx" ON "delegation" ("truster_id");
969 CREATE INDEX "delegation_trustee_id_idx" ON "delegation" ("trustee_id");
971 COMMENT ON TABLE "delegation" IS 'Delegation of vote-weight to other members';
973 COMMENT ON COLUMN "delegation"."unit_id" IS 'Reference to unit, if delegation is unit-wide, otherwise NULL';
974 COMMENT ON COLUMN "delegation"."area_id" IS 'Reference to area, if delegation is area-wide, otherwise NULL';
975 COMMENT ON COLUMN "delegation"."issue_id" IS 'Reference to issue, if delegation is issue-wide, otherwise NULL';
978 CREATE TABLE "direct_population_snapshot" (
979 PRIMARY KEY ("issue_id", "event", "member_id"),
980 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
981 "event" "snapshot_event",
982 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
983 "weight" INT4 );
984 CREATE INDEX "direct_population_snapshot_member_id_idx" ON "direct_population_snapshot" ("member_id");
986 COMMENT ON TABLE "direct_population_snapshot" IS 'Snapshot of active members having either a "membership" in the "area" or an "interest" in the "issue"';
988 COMMENT ON COLUMN "direct_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
989 COMMENT ON COLUMN "direct_population_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_population_snapshot"';
992 CREATE TABLE "delegating_population_snapshot" (
993 PRIMARY KEY ("issue_id", "event", "member_id"),
994 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
995 "event" "snapshot_event",
996 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
997 "weight" INT4,
998 "scope" "delegation_scope" NOT NULL,
999 "delegate_member_ids" INT4[] NOT NULL );
1000 CREATE INDEX "delegating_population_snapshot_member_id_idx" ON "delegating_population_snapshot" ("member_id");
1002 COMMENT ON TABLE "direct_population_snapshot" IS 'Delegations increasing the weight of entries in the "direct_population_snapshot" table';
1004 COMMENT ON COLUMN "delegating_population_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1005 COMMENT ON COLUMN "delegating_population_snapshot"."member_id" IS 'Delegating member';
1006 COMMENT ON COLUMN "delegating_population_snapshot"."weight" IS 'Intermediate weight';
1007 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"';
1010 CREATE TABLE "direct_interest_snapshot" (
1011 PRIMARY KEY ("issue_id", "event", "member_id"),
1012 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1013 "event" "snapshot_event",
1014 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1015 "weight" INT4 );
1016 CREATE INDEX "direct_interest_snapshot_member_id_idx" ON "direct_interest_snapshot" ("member_id");
1018 COMMENT ON TABLE "direct_interest_snapshot" IS 'Snapshot of active members having an "interest" in the "issue"';
1020 COMMENT ON COLUMN "direct_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1021 COMMENT ON COLUMN "direct_interest_snapshot"."weight" IS 'Weight of member (1 or higher) according to "delegating_interest_snapshot"';
1024 CREATE TABLE "delegating_interest_snapshot" (
1025 PRIMARY KEY ("issue_id", "event", "member_id"),
1026 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1027 "event" "snapshot_event",
1028 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1029 "weight" INT4,
1030 "scope" "delegation_scope" NOT NULL,
1031 "delegate_member_ids" INT4[] NOT NULL );
1032 CREATE INDEX "delegating_interest_snapshot_member_id_idx" ON "delegating_interest_snapshot" ("member_id");
1034 COMMENT ON TABLE "delegating_interest_snapshot" IS 'Delegations increasing the weight of entries in the "direct_interest_snapshot" table';
1036 COMMENT ON COLUMN "delegating_interest_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1037 COMMENT ON COLUMN "delegating_interest_snapshot"."member_id" IS 'Delegating member';
1038 COMMENT ON COLUMN "delegating_interest_snapshot"."weight" IS 'Intermediate weight';
1039 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"';
1042 CREATE TABLE "direct_supporter_snapshot" (
1043 "issue_id" INT4 NOT NULL,
1044 PRIMARY KEY ("initiative_id", "event", "member_id"),
1045 "initiative_id" INT4,
1046 "event" "snapshot_event",
1047 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1048 "draft_id" INT8 NOT NULL,
1049 "informed" BOOLEAN NOT NULL,
1050 "satisfied" BOOLEAN NOT NULL,
1051 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1052 FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE,
1053 FOREIGN KEY ("issue_id", "event", "member_id") REFERENCES "direct_interest_snapshot" ("issue_id", "event", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1054 CREATE INDEX "direct_supporter_snapshot_member_id_idx" ON "direct_supporter_snapshot" ("member_id");
1056 COMMENT ON TABLE "direct_supporter_snapshot" IS 'Snapshot of supporters of initiatives (weight is stored in "direct_interest_snapshot")';
1058 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';
1059 COMMENT ON COLUMN "direct_supporter_snapshot"."event" IS 'Reason for snapshot, see "snapshot_event" type for details';
1060 COMMENT ON COLUMN "direct_supporter_snapshot"."informed" IS 'Supporter has seen the latest draft of the initiative';
1061 COMMENT ON COLUMN "direct_supporter_snapshot"."satisfied" IS 'Supporter has no "critical_opinion"s';
1064 CREATE TABLE "non_voter" (
1065 PRIMARY KEY ("issue_id", "member_id"),
1066 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1067 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE );
1068 CREATE INDEX "non_voter_member_id_idx" ON "non_voter" ("member_id");
1070 COMMENT ON TABLE "non_voter" IS 'Members who decided to not vote directly on an issue';
1073 CREATE TABLE "direct_voter" (
1074 PRIMARY KEY ("issue_id", "member_id"),
1075 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1076 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1077 "weight" INT4 );
1078 CREATE INDEX "direct_voter_member_id_idx" ON "direct_voter" ("member_id");
1080 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.';
1082 COMMENT ON COLUMN "direct_voter"."weight" IS 'Weight of member (1 or higher) according to "delegating_voter" table';
1085 CREATE TABLE "delegating_voter" (
1086 PRIMARY KEY ("issue_id", "member_id"),
1087 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1088 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE RESTRICT,
1089 "weight" INT4,
1090 "scope" "delegation_scope" NOT NULL,
1091 "delegate_member_ids" INT4[] NOT NULL );
1092 CREATE INDEX "delegating_voter_member_id_idx" ON "delegating_voter" ("member_id");
1094 COMMENT ON TABLE "delegating_voter" IS 'Delegations increasing the weight of entries in the "direct_voter" table';
1096 COMMENT ON COLUMN "delegating_voter"."member_id" IS 'Delegating member';
1097 COMMENT ON COLUMN "delegating_voter"."weight" IS 'Intermediate weight';
1098 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"';
1101 CREATE TABLE "vote" (
1102 "issue_id" INT4 NOT NULL,
1103 PRIMARY KEY ("initiative_id", "member_id"),
1104 "initiative_id" INT4,
1105 "member_id" INT4,
1106 "grade" INT4,
1107 FOREIGN KEY ("issue_id", "initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
1108 FOREIGN KEY ("issue_id", "member_id") REFERENCES "direct_voter" ("issue_id", "member_id") ON DELETE CASCADE ON UPDATE CASCADE );
1109 CREATE INDEX "vote_member_id_idx" ON "vote" ("member_id");
1111 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.';
1113 COMMENT ON COLUMN "vote"."issue_id" IS 'WARNING: No index: For selections use column "initiative_id" and join via table "initiative" where neccessary';
1114 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.';
1117 CREATE TABLE "voting_comment" (
1118 PRIMARY KEY ("issue_id", "member_id"),
1119 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1120 "member_id" INT4 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1121 "changed" TIMESTAMPTZ,
1122 "formatting_engine" TEXT,
1123 "content" TEXT NOT NULL,
1124 "text_search_data" TSVECTOR );
1125 CREATE INDEX "voting_comment_member_id_idx" ON "voting_comment" ("member_id");
1126 CREATE INDEX "voting_comment_text_search_data_idx" ON "voting_comment" USING gin ("text_search_data");
1127 CREATE TRIGGER "update_text_search_data"
1128 BEFORE INSERT OR UPDATE ON "voting_comment"
1129 FOR EACH ROW EXECUTE PROCEDURE
1130 tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "content");
1132 COMMENT ON TABLE "voting_comment" IS 'Storage for comments of voters to be published after voting has finished.';
1134 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.';
1137 CREATE TABLE "rendered_voting_comment" (
1138 PRIMARY KEY ("issue_id", "member_id", "format"),
1139 FOREIGN KEY ("issue_id", "member_id")
1140 REFERENCES "voting_comment" ("issue_id", "member_id")
1141 ON DELETE CASCADE ON UPDATE CASCADE,
1142 "issue_id" INT4,
1143 "member_id" INT4,
1144 "format" TEXT,
1145 "content" TEXT NOT NULL );
1147 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)';
1150 CREATE TYPE "event_type" AS ENUM (
1151 'issue_state_changed',
1152 'initiative_created_in_new_issue',
1153 'initiative_created_in_existing_issue',
1154 'initiative_revoked',
1155 'new_draft_created',
1156 'suggestion_created');
1158 COMMENT ON TYPE "event_type" IS 'Type used for column "event" of table "event"';
1161 CREATE TABLE "event" (
1162 "id" SERIAL8 PRIMARY KEY,
1163 "occurrence" TIMESTAMPTZ NOT NULL DEFAULT now(),
1164 "event" "event_type" NOT NULL,
1165 "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE,
1166 "issue_id" INT4 REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
1167 "state" "issue_state" CHECK ("state" != 'calculation'),
1168 "initiative_id" INT4,
1169 "draft_id" INT8,
1170 "suggestion_id" INT8,
1171 FOREIGN KEY ("issue_id", "initiative_id")
1172 REFERENCES "initiative" ("issue_id", "id")
1173 ON DELETE CASCADE ON UPDATE CASCADE,
1174 FOREIGN KEY ("initiative_id", "draft_id")
1175 REFERENCES "draft" ("initiative_id", "id")
1176 ON DELETE CASCADE ON UPDATE CASCADE,
1177 FOREIGN KEY ("initiative_id", "suggestion_id")
1178 REFERENCES "suggestion" ("initiative_id", "id")
1179 ON DELETE CASCADE ON UPDATE CASCADE,
1180 CONSTRAINT "null_constraints_for_issue_state_changed" CHECK (
1181 "event" != 'issue_state_changed' OR (
1182 "member_id" ISNULL AND
1183 "issue_id" NOTNULL AND
1184 "state" NOTNULL AND
1185 "initiative_id" ISNULL AND
1186 "draft_id" ISNULL AND
1187 "suggestion_id" ISNULL )),
1188 CONSTRAINT "null_constraints_for_initiative_creation_or_revocation_or_new_draft" CHECK (
1189 "event" NOT IN (
1190 'initiative_created_in_new_issue',
1191 'initiative_created_in_existing_issue',
1192 'initiative_revoked',
1193 'new_draft_created'
1194 ) OR (
1195 "member_id" NOTNULL AND
1196 "issue_id" NOTNULL AND
1197 "state" NOTNULL AND
1198 "initiative_id" NOTNULL AND
1199 "draft_id" NOTNULL AND
1200 "suggestion_id" ISNULL )),
1201 CONSTRAINT "null_constraints_for_suggestion_creation" CHECK (
1202 "event" != 'suggestion_created' OR (
1203 "member_id" NOTNULL AND
1204 "issue_id" NOTNULL AND
1205 "state" NOTNULL AND
1206 "initiative_id" NOTNULL AND
1207 "draft_id" ISNULL AND
1208 "suggestion_id" NOTNULL )) );
1209 CREATE INDEX "event_occurrence_idx" ON "event" ("occurrence");
1211 COMMENT ON TABLE "event" IS 'Event table, automatically filled by triggers';
1213 COMMENT ON COLUMN "event"."occurrence" IS 'Point in time, when event occurred';
1214 COMMENT ON COLUMN "event"."event" IS 'Type of event (see TYPE "event_type")';
1215 COMMENT ON COLUMN "event"."member_id" IS 'Member who caused the event, if applicable';
1216 COMMENT ON COLUMN "event"."state" IS 'If issue_id is set: state of affected issue; If state changed: new state';
1219 CREATE TABLE "notification_sent" (
1220 "event_id" INT8 NOT NULL );
1221 CREATE UNIQUE INDEX "notification_sent_singleton_idx" ON "notification_sent" ((1));
1223 COMMENT ON TABLE "notification_sent" IS 'This table stores one row with the last event_id, for which notifications have been sent out';
1224 COMMENT ON INDEX "notification_sent_singleton_idx" IS 'This index ensures that "notification_sent" only contains one row maximum.';
1228 ----------------------------------------------
1229 -- Writing of history entries and event log --
1230 ----------------------------------------------
1233 CREATE FUNCTION "write_member_history_trigger"()
1234 RETURNS TRIGGER
1235 LANGUAGE 'plpgsql' VOLATILE AS $$
1236 BEGIN
1237 IF
1238 ( NEW."active" != OLD."active" OR
1239 NEW."name" != OLD."name" ) AND
1240 OLD."activated" NOTNULL
1241 THEN
1242 INSERT INTO "member_history"
1243 ("member_id", "active", "name")
1244 VALUES (NEW."id", OLD."active", OLD."name");
1245 END IF;
1246 RETURN NULL;
1247 END;
1248 $$;
1250 CREATE TRIGGER "write_member_history"
1251 AFTER UPDATE ON "member" FOR EACH ROW EXECUTE PROCEDURE
1252 "write_member_history_trigger"();
1254 COMMENT ON FUNCTION "write_member_history_trigger"() IS 'Implementation of trigger "write_member_history" on table "member"';
1255 COMMENT ON TRIGGER "write_member_history" ON "member" IS 'When changing certain fields of a member, create a history entry in "member_history" table';
1258 CREATE FUNCTION "write_event_issue_state_changed_trigger"()
1259 RETURNS TRIGGER
1260 LANGUAGE 'plpgsql' VOLATILE AS $$
1261 BEGIN
1262 IF NEW."state" != OLD."state" AND NEW."state" != 'calculation' THEN
1263 INSERT INTO "event" ("event", "issue_id", "state")
1264 VALUES ('issue_state_changed', NEW."id", NEW."state");
1265 END IF;
1266 RETURN NULL;
1267 END;
1268 $$;
1270 CREATE TRIGGER "write_event_issue_state_changed"
1271 AFTER UPDATE ON "issue" FOR EACH ROW EXECUTE PROCEDURE
1272 "write_event_issue_state_changed_trigger"();
1274 COMMENT ON FUNCTION "write_event_issue_state_changed_trigger"() IS 'Implementation of trigger "write_event_issue_state_changed" on table "issue"';
1275 COMMENT ON TRIGGER "write_event_issue_state_changed" ON "issue" IS 'Create entry in "event" table on "state" change';
1278 CREATE FUNCTION "write_event_initiative_or_draft_created_trigger"()
1279 RETURNS TRIGGER
1280 LANGUAGE 'plpgsql' VOLATILE AS $$
1281 DECLARE
1282 "initiative_row" "initiative"%ROWTYPE;
1283 "issue_row" "issue"%ROWTYPE;
1284 "event_v" "event_type";
1285 BEGIN
1286 SELECT * INTO "initiative_row" FROM "initiative"
1287 WHERE "id" = NEW."initiative_id";
1288 SELECT * INTO "issue_row" FROM "issue"
1289 WHERE "id" = "initiative_row"."issue_id";
1290 IF EXISTS (
1291 SELECT NULL FROM "draft"
1292 WHERE "initiative_id" = NEW."initiative_id"
1293 AND "id" != NEW."id"
1294 ) THEN
1295 "event_v" := 'new_draft_created';
1296 ELSE
1297 IF EXISTS (
1298 SELECT NULL FROM "initiative"
1299 WHERE "issue_id" = "initiative_row"."issue_id"
1300 AND "id" != "initiative_row"."id"
1301 ) THEN
1302 "event_v" := 'initiative_created_in_existing_issue';
1303 ELSE
1304 "event_v" := 'initiative_created_in_new_issue';
1305 END IF;
1306 END IF;
1307 INSERT INTO "event" (
1308 "event", "member_id",
1309 "issue_id", "state", "initiative_id", "draft_id"
1310 ) VALUES (
1311 "event_v",
1312 NEW."author_id",
1313 "initiative_row"."issue_id",
1314 "issue_row"."state",
1315 "initiative_row"."id",
1316 NEW."id" );
1317 RETURN NULL;
1318 END;
1319 $$;
1321 CREATE TRIGGER "write_event_initiative_or_draft_created"
1322 AFTER INSERT ON "draft" FOR EACH ROW EXECUTE PROCEDURE
1323 "write_event_initiative_or_draft_created_trigger"();
1325 COMMENT ON FUNCTION "write_event_initiative_or_draft_created_trigger"() IS 'Implementation of trigger "write_event_initiative_or_draft_created" on table "issue"';
1326 COMMENT ON TRIGGER "write_event_initiative_or_draft_created" ON "draft" IS 'Create entry in "event" table on draft creation';
1329 CREATE FUNCTION "write_event_initiative_revoked_trigger"()
1330 RETURNS TRIGGER
1331 LANGUAGE 'plpgsql' VOLATILE AS $$
1332 DECLARE
1333 "issue_row" "issue"%ROWTYPE;
1334 "draft_id_v" "draft"."id"%TYPE;
1335 BEGIN
1336 IF OLD."revoked" ISNULL AND NEW."revoked" NOTNULL THEN
1337 SELECT * INTO "issue_row" FROM "issue"
1338 WHERE "id" = NEW."issue_id";
1339 SELECT "id" INTO "draft_id_v" FROM "current_draft"
1340 WHERE "initiative_id" = NEW."id";
1341 INSERT INTO "event" (
1342 "event", "member_id", "issue_id", "state", "initiative_id", "draft_id"
1343 ) VALUES (
1344 'initiative_revoked',
1345 NEW."revoked_by_member_id",
1346 NEW."issue_id",
1347 "issue_row"."state",
1348 NEW."id",
1349 "draft_id_v");
1350 END IF;
1351 RETURN NULL;
1352 END;
1353 $$;
1355 CREATE TRIGGER "write_event_initiative_revoked"
1356 AFTER UPDATE ON "initiative" FOR EACH ROW EXECUTE PROCEDURE
1357 "write_event_initiative_revoked_trigger"();
1359 COMMENT ON FUNCTION "write_event_initiative_revoked_trigger"() IS 'Implementation of trigger "write_event_initiative_revoked" on table "issue"';
1360 COMMENT ON TRIGGER "write_event_initiative_revoked" ON "initiative" IS 'Create entry in "event" table, when an initiative is revoked';
1363 CREATE FUNCTION "write_event_suggestion_created_trigger"()
1364 RETURNS TRIGGER
1365 LANGUAGE 'plpgsql' VOLATILE AS $$
1366 DECLARE
1367 "initiative_row" "initiative"%ROWTYPE;
1368 "issue_row" "issue"%ROWTYPE;
1369 BEGIN
1370 SELECT * INTO "initiative_row" FROM "initiative"
1371 WHERE "id" = NEW."initiative_id";
1372 SELECT * INTO "issue_row" FROM "issue"
1373 WHERE "id" = "initiative_row"."issue_id";
1374 INSERT INTO "event" (
1375 "event", "member_id",
1376 "issue_id", "state", "initiative_id", "suggestion_id"
1377 ) VALUES (
1378 'suggestion_created',
1379 NEW."author_id",
1380 "initiative_row"."issue_id",
1381 "issue_row"."state",
1382 "initiative_row"."id",
1383 NEW."id" );
1384 RETURN NULL;
1385 END;
1386 $$;
1388 CREATE TRIGGER "write_event_suggestion_created"
1389 AFTER INSERT ON "suggestion" FOR EACH ROW EXECUTE PROCEDURE
1390 "write_event_suggestion_created_trigger"();
1392 COMMENT ON FUNCTION "write_event_suggestion_created_trigger"() IS 'Implementation of trigger "write_event_suggestion_created" on table "issue"';
1393 COMMENT ON TRIGGER "write_event_suggestion_created" ON "suggestion" IS 'Create entry in "event" table on suggestion creation';
1397 ----------------------------
1398 -- Additional constraints --
1399 ----------------------------
1402 CREATE FUNCTION "issue_requires_first_initiative_trigger"()
1403 RETURNS TRIGGER
1404 LANGUAGE 'plpgsql' VOLATILE AS $$
1405 BEGIN
1406 IF NOT EXISTS (
1407 SELECT NULL FROM "initiative" WHERE "issue_id" = NEW."id"
1408 ) THEN
1409 --RAISE 'Cannot create issue without an initial initiative.' USING
1410 -- ERRCODE = 'integrity_constraint_violation',
1411 -- HINT = 'Create issue, initiative, and draft within the same transaction.';
1412 RAISE EXCEPTION 'Cannot create issue without an initial initiative.';
1413 END IF;
1414 RETURN NULL;
1415 END;
1416 $$;
1418 CREATE CONSTRAINT TRIGGER "issue_requires_first_initiative"
1419 AFTER INSERT OR UPDATE ON "issue" DEFERRABLE INITIALLY DEFERRED
1420 FOR EACH ROW EXECUTE PROCEDURE
1421 "issue_requires_first_initiative_trigger"();
1423 COMMENT ON FUNCTION "issue_requires_first_initiative_trigger"() IS 'Implementation of trigger "issue_requires_first_initiative" on table "issue"';
1424 COMMENT ON TRIGGER "issue_requires_first_initiative" ON "issue" IS 'Ensure that new issues have at least one initiative';
1427 CREATE FUNCTION "last_initiative_deletes_issue_trigger"()
1428 RETURNS TRIGGER
1429 LANGUAGE 'plpgsql' VOLATILE AS $$
1430 DECLARE
1431 "reference_lost" BOOLEAN;
1432 BEGIN
1433 IF TG_OP = 'DELETE' THEN
1434 "reference_lost" := TRUE;
1435 ELSE
1436 "reference_lost" := NEW."issue_id" != OLD."issue_id";
1437 END IF;
1438 IF
1439 "reference_lost" AND NOT EXISTS (
1440 SELECT NULL FROM "initiative" WHERE "issue_id" = OLD."issue_id"
1441 )
1442 THEN
1443 DELETE FROM "issue" WHERE "id" = OLD."issue_id";
1444 END IF;
1445 RETURN NULL;
1446 END;
1447 $$;
1449 CREATE CONSTRAINT TRIGGER "last_initiative_deletes_issue"
1450 AFTER UPDATE OR DELETE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1451 FOR EACH ROW EXECUTE PROCEDURE
1452 "last_initiative_deletes_issue_trigger"();
1454 COMMENT ON FUNCTION "last_initiative_deletes_issue_trigger"() IS 'Implementation of trigger "last_initiative_deletes_issue" on table "initiative"';
1455 COMMENT ON TRIGGER "last_initiative_deletes_issue" ON "initiative" IS 'Removing the last initiative of an issue deletes the issue';
1458 CREATE FUNCTION "initiative_requires_first_draft_trigger"()
1459 RETURNS TRIGGER
1460 LANGUAGE 'plpgsql' VOLATILE AS $$
1461 BEGIN
1462 IF NOT EXISTS (
1463 SELECT NULL FROM "draft" WHERE "initiative_id" = NEW."id"
1464 ) THEN
1465 --RAISE 'Cannot create initiative without an initial draft.' USING
1466 -- ERRCODE = 'integrity_constraint_violation',
1467 -- HINT = 'Create issue, initiative and draft within the same transaction.';
1468 RAISE EXCEPTION 'Cannot create initiative without an initial draft.';
1469 END IF;
1470 RETURN NULL;
1471 END;
1472 $$;
1474 CREATE CONSTRAINT TRIGGER "initiative_requires_first_draft"
1475 AFTER INSERT OR UPDATE ON "initiative" DEFERRABLE INITIALLY DEFERRED
1476 FOR EACH ROW EXECUTE PROCEDURE
1477 "initiative_requires_first_draft_trigger"();
1479 COMMENT ON FUNCTION "initiative_requires_first_draft_trigger"() IS 'Implementation of trigger "initiative_requires_first_draft" on table "initiative"';
1480 COMMENT ON TRIGGER "initiative_requires_first_draft" ON "initiative" IS 'Ensure that new initiatives have at least one draft';
1483 CREATE FUNCTION "last_draft_deletes_initiative_trigger"()
1484 RETURNS TRIGGER
1485 LANGUAGE 'plpgsql' VOLATILE AS $$
1486 DECLARE
1487 "reference_lost" BOOLEAN;
1488 BEGIN
1489 IF TG_OP = 'DELETE' THEN
1490 "reference_lost" := TRUE;
1491 ELSE
1492 "reference_lost" := NEW."initiative_id" != OLD."initiative_id";
1493 END IF;
1494 IF
1495 "reference_lost" AND NOT EXISTS (
1496 SELECT NULL FROM "draft" WHERE "initiative_id" = OLD."initiative_id"
1497 )
1498 THEN
1499 DELETE FROM "initiative" WHERE "id" = OLD."initiative_id";
1500 END IF;
1501 RETURN NULL;
1502 END;
1503 $$;
1505 CREATE CONSTRAINT TRIGGER "last_draft_deletes_initiative"
1506 AFTER UPDATE OR DELETE ON "draft" DEFERRABLE INITIALLY DEFERRED
1507 FOR EACH ROW EXECUTE PROCEDURE
1508 "last_draft_deletes_initiative_trigger"();
1510 COMMENT ON FUNCTION "last_draft_deletes_initiative_trigger"() IS 'Implementation of trigger "last_draft_deletes_initiative" on table "draft"';
1511 COMMENT ON TRIGGER "last_draft_deletes_initiative" ON "draft" IS 'Removing the last draft of an initiative deletes the initiative';
1514 CREATE FUNCTION "suggestion_requires_first_opinion_trigger"()
1515 RETURNS TRIGGER
1516 LANGUAGE 'plpgsql' VOLATILE AS $$
1517 BEGIN
1518 IF NOT EXISTS (
1519 SELECT NULL FROM "opinion" WHERE "suggestion_id" = NEW."id"
1520 ) THEN
1521 RAISE EXCEPTION 'Cannot create a suggestion without an opinion.';
1522 END IF;
1523 RETURN NULL;
1524 END;
1525 $$;
1527 CREATE CONSTRAINT TRIGGER "suggestion_requires_first_opinion"
1528 AFTER INSERT OR UPDATE ON "suggestion" DEFERRABLE INITIALLY DEFERRED
1529 FOR EACH ROW EXECUTE PROCEDURE
1530 "suggestion_requires_first_opinion_trigger"();
1532 COMMENT ON FUNCTION "suggestion_requires_first_opinion_trigger"() IS 'Implementation of trigger "suggestion_requires_first_opinion" on table "suggestion"';
1533 COMMENT ON TRIGGER "suggestion_requires_first_opinion" ON "suggestion" IS 'Ensure that new suggestions have at least one opinion';
1536 CREATE FUNCTION "last_opinion_deletes_suggestion_trigger"()
1537 RETURNS TRIGGER
1538 LANGUAGE 'plpgsql' VOLATILE AS $$
1539 DECLARE
1540 "reference_lost" BOOLEAN;
1541 BEGIN
1542 IF TG_OP = 'DELETE' THEN
1543 "reference_lost" := TRUE;
1544 ELSE
1545 "reference_lost" := NEW."suggestion_id" != OLD."suggestion_id";
1546 END IF;
1547 IF
1548 "reference_lost" AND NOT EXISTS (
1549 SELECT NULL FROM "opinion" WHERE "suggestion_id" = OLD."suggestion_id"
1550 )
1551 THEN
1552 DELETE FROM "suggestion" WHERE "id" = OLD."suggestion_id";
1553 END IF;
1554 RETURN NULL;
1555 END;
1556 $$;
1558 CREATE CONSTRAINT TRIGGER "last_opinion_deletes_suggestion"
1559 AFTER UPDATE OR DELETE ON "opinion" DEFERRABLE INITIALLY DEFERRED
1560 FOR EACH ROW EXECUTE PROCEDURE
1561 "last_opinion_deletes_suggestion_trigger"();
1563 COMMENT ON FUNCTION "last_opinion_deletes_suggestion_trigger"() IS 'Implementation of trigger "last_opinion_deletes_suggestion" on table "opinion"';
1564 COMMENT ON TRIGGER "last_opinion_deletes_suggestion" ON "opinion" IS 'Removing the last opinion of a suggestion deletes the suggestion';
1568 ---------------------------------------------------------------
1569 -- Ensure that votes are not modified when issues are frozen --
1570 ---------------------------------------------------------------
1572 -- NOTE: Frontends should ensure this anyway, but in case of programming
1573 -- errors the following triggers ensure data integrity.
1576 CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"()
1577 RETURNS TRIGGER
1578 LANGUAGE 'plpgsql' VOLATILE AS $$
1579 DECLARE
1580 "issue_id_v" "issue"."id"%TYPE;
1581 "issue_row" "issue"%ROWTYPE;
1582 BEGIN
1583 IF TG_OP = 'DELETE' THEN
1584 "issue_id_v" := OLD."issue_id";
1585 ELSE
1586 "issue_id_v" := NEW."issue_id";
1587 END IF;
1588 SELECT INTO "issue_row" * FROM "issue"
1589 WHERE "id" = "issue_id_v" FOR SHARE;
1590 IF "issue_row"."closed" NOTNULL THEN
1591 RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.';
1592 END IF;
1593 RETURN NULL;
1594 END;
1595 $$;
1597 CREATE TRIGGER "forbid_changes_on_closed_issue"
1598 AFTER INSERT OR UPDATE OR DELETE ON "direct_voter"
1599 FOR EACH ROW EXECUTE PROCEDURE
1600 "forbid_changes_on_closed_issue_trigger"();
1602 CREATE TRIGGER "forbid_changes_on_closed_issue"
1603 AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter"
1604 FOR EACH ROW EXECUTE PROCEDURE
1605 "forbid_changes_on_closed_issue_trigger"();
1607 CREATE TRIGGER "forbid_changes_on_closed_issue"
1608 AFTER INSERT OR UPDATE OR DELETE ON "vote"
1609 FOR EACH ROW EXECUTE PROCEDURE
1610 "forbid_changes_on_closed_issue_trigger"();
1612 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"';
1613 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';
1614 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';
1615 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';
1619 --------------------------------------------------------------------
1620 -- Auto-retrieval of fields only needed for referential integrity --
1621 --------------------------------------------------------------------
1624 CREATE FUNCTION "autofill_issue_id_trigger"()
1625 RETURNS TRIGGER
1626 LANGUAGE 'plpgsql' VOLATILE AS $$
1627 BEGIN
1628 IF NEW."issue_id" ISNULL THEN
1629 SELECT "issue_id" INTO NEW."issue_id"
1630 FROM "initiative" WHERE "id" = NEW."initiative_id";
1631 END IF;
1632 RETURN NEW;
1633 END;
1634 $$;
1636 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "supporter"
1637 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1639 CREATE TRIGGER "autofill_issue_id" BEFORE INSERT ON "vote"
1640 FOR EACH ROW EXECUTE PROCEDURE "autofill_issue_id_trigger"();
1642 COMMENT ON FUNCTION "autofill_issue_id_trigger"() IS 'Implementation of triggers "autofill_issue_id" on tables "supporter" and "vote"';
1643 COMMENT ON TRIGGER "autofill_issue_id" ON "supporter" IS 'Set "issue_id" field automatically, if NULL';
1644 COMMENT ON TRIGGER "autofill_issue_id" ON "vote" IS 'Set "issue_id" field automatically, if NULL';
1647 CREATE FUNCTION "autofill_initiative_id_trigger"()
1648 RETURNS TRIGGER
1649 LANGUAGE 'plpgsql' VOLATILE AS $$
1650 BEGIN
1651 IF NEW."initiative_id" ISNULL THEN
1652 SELECT "initiative_id" INTO NEW."initiative_id"
1653 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1654 END IF;
1655 RETURN NEW;
1656 END;
1657 $$;
1659 CREATE TRIGGER "autofill_initiative_id" BEFORE INSERT ON "opinion"
1660 FOR EACH ROW EXECUTE PROCEDURE "autofill_initiative_id_trigger"();
1662 COMMENT ON FUNCTION "autofill_initiative_id_trigger"() IS 'Implementation of trigger "autofill_initiative_id" on table "opinion"';
1663 COMMENT ON TRIGGER "autofill_initiative_id" ON "opinion" IS 'Set "initiative_id" field automatically, if NULL';
1667 -----------------------------------------------------
1668 -- Automatic calculation of certain default values --
1669 -----------------------------------------------------
1672 CREATE FUNCTION "copy_timings_trigger"()
1673 RETURNS TRIGGER
1674 LANGUAGE 'plpgsql' VOLATILE AS $$
1675 DECLARE
1676 "policy_row" "policy"%ROWTYPE;
1677 BEGIN
1678 SELECT * INTO "policy_row" FROM "policy"
1679 WHERE "id" = NEW."policy_id";
1680 IF NEW."admission_time" ISNULL THEN
1681 NEW."admission_time" := "policy_row"."admission_time";
1682 END IF;
1683 IF NEW."discussion_time" ISNULL THEN
1684 NEW."discussion_time" := "policy_row"."discussion_time";
1685 END IF;
1686 IF NEW."verification_time" ISNULL THEN
1687 NEW."verification_time" := "policy_row"."verification_time";
1688 END IF;
1689 IF NEW."voting_time" ISNULL THEN
1690 NEW."voting_time" := "policy_row"."voting_time";
1691 END IF;
1692 RETURN NEW;
1693 END;
1694 $$;
1696 CREATE TRIGGER "copy_timings" BEFORE INSERT OR UPDATE ON "issue"
1697 FOR EACH ROW EXECUTE PROCEDURE "copy_timings_trigger"();
1699 COMMENT ON FUNCTION "copy_timings_trigger"() IS 'Implementation of trigger "copy_timings" on table "issue"';
1700 COMMENT ON TRIGGER "copy_timings" ON "issue" IS 'If timing fields are NULL, copy values from policy.';
1703 CREATE FUNCTION "default_for_draft_id_trigger"()
1704 RETURNS TRIGGER
1705 LANGUAGE 'plpgsql' VOLATILE AS $$
1706 BEGIN
1707 IF NEW."draft_id" ISNULL THEN
1708 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
1709 WHERE "initiative_id" = NEW."initiative_id";
1710 END IF;
1711 RETURN NEW;
1712 END;
1713 $$;
1715 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"
1716 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1717 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
1718 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
1720 COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';
1721 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';
1722 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';
1726 ----------------------------------------
1727 -- Automatic creation of dependencies --
1728 ----------------------------------------
1731 CREATE FUNCTION "autocreate_interest_trigger"()
1732 RETURNS TRIGGER
1733 LANGUAGE 'plpgsql' VOLATILE AS $$
1734 BEGIN
1735 IF NOT EXISTS (
1736 SELECT NULL FROM "initiative" JOIN "interest"
1737 ON "initiative"."issue_id" = "interest"."issue_id"
1738 WHERE "initiative"."id" = NEW."initiative_id"
1739 AND "interest"."member_id" = NEW."member_id"
1740 ) THEN
1741 BEGIN
1742 INSERT INTO "interest" ("issue_id", "member_id")
1743 SELECT "issue_id", NEW."member_id"
1744 FROM "initiative" WHERE "id" = NEW."initiative_id";
1745 EXCEPTION WHEN unique_violation THEN END;
1746 END IF;
1747 RETURN NEW;
1748 END;
1749 $$;
1751 CREATE TRIGGER "autocreate_interest" BEFORE INSERT ON "supporter"
1752 FOR EACH ROW EXECUTE PROCEDURE "autocreate_interest_trigger"();
1754 COMMENT ON FUNCTION "autocreate_interest_trigger"() IS 'Implementation of trigger "autocreate_interest" on table "supporter"';
1755 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';
1758 CREATE FUNCTION "autocreate_supporter_trigger"()
1759 RETURNS TRIGGER
1760 LANGUAGE 'plpgsql' VOLATILE AS $$
1761 BEGIN
1762 IF NOT EXISTS (
1763 SELECT NULL FROM "suggestion" JOIN "supporter"
1764 ON "suggestion"."initiative_id" = "supporter"."initiative_id"
1765 WHERE "suggestion"."id" = NEW."suggestion_id"
1766 AND "supporter"."member_id" = NEW."member_id"
1767 ) THEN
1768 BEGIN
1769 INSERT INTO "supporter" ("initiative_id", "member_id")
1770 SELECT "initiative_id", NEW."member_id"
1771 FROM "suggestion" WHERE "id" = NEW."suggestion_id";
1772 EXCEPTION WHEN unique_violation THEN END;
1773 END IF;
1774 RETURN NEW;
1775 END;
1776 $$;
1778 CREATE TRIGGER "autocreate_supporter" BEFORE INSERT ON "opinion"
1779 FOR EACH ROW EXECUTE PROCEDURE "autocreate_supporter_trigger"();
1781 COMMENT ON FUNCTION "autocreate_supporter_trigger"() IS 'Implementation of trigger "autocreate_supporter" on table "opinion"';
1782 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.';
1786 ------------------------------------------
1787 -- Views and helper functions for views --
1788 ------------------------------------------
1791 CREATE VIEW "unit_delegation" AS
1792 SELECT
1793 "unit"."id" AS "unit_id",
1794 "delegation"."id",
1795 "delegation"."truster_id",
1796 "delegation"."trustee_id",
1797 "delegation"."scope"
1798 FROM "unit"
1799 JOIN "delegation"
1800 ON "delegation"."unit_id" = "unit"."id"
1801 JOIN "member"
1802 ON "delegation"."truster_id" = "member"."id"
1803 JOIN "privilege"
1804 ON "delegation"."unit_id" = "privilege"."unit_id"
1805 AND "delegation"."truster_id" = "privilege"."member_id"
1806 WHERE "member"."active" AND "privilege"."voting_right";
1808 COMMENT ON VIEW "unit_delegation" IS 'Unit delegations where trusters are active and have voting right';
1811 CREATE VIEW "area_delegation" AS
1812 SELECT DISTINCT ON ("area"."id", "delegation"."truster_id")
1813 "area"."id" AS "area_id",
1814 "delegation"."id",
1815 "delegation"."truster_id",
1816 "delegation"."trustee_id",
1817 "delegation"."scope"
1818 FROM "area"
1819 JOIN "delegation"
1820 ON "delegation"."unit_id" = "area"."unit_id"
1821 OR "delegation"."area_id" = "area"."id"
1822 JOIN "member"
1823 ON "delegation"."truster_id" = "member"."id"
1824 JOIN "privilege"
1825 ON "area"."unit_id" = "privilege"."unit_id"
1826 AND "delegation"."truster_id" = "privilege"."member_id"
1827 WHERE "member"."active" AND "privilege"."voting_right"
1828 ORDER BY
1829 "area"."id",
1830 "delegation"."truster_id",
1831 "delegation"."scope" DESC;
1833 COMMENT ON VIEW "area_delegation" IS 'Area delegations where trusters are active and have voting right';
1836 CREATE VIEW "issue_delegation" AS
1837 SELECT DISTINCT ON ("issue"."id", "delegation"."truster_id")
1838 "issue"."id" AS "issue_id",
1839 "delegation"."id",
1840 "delegation"."truster_id",
1841 "delegation"."trustee_id",
1842 "delegation"."scope"
1843 FROM "issue"
1844 JOIN "area"
1845 ON "area"."id" = "issue"."area_id"
1846 JOIN "delegation"
1847 ON "delegation"."unit_id" = "area"."unit_id"
1848 OR "delegation"."area_id" = "area"."id"
1849 OR "delegation"."issue_id" = "issue"."id"
1850 JOIN "member"
1851 ON "delegation"."truster_id" = "member"."id"
1852 JOIN "privilege"
1853 ON "area"."unit_id" = "privilege"."unit_id"
1854 AND "delegation"."truster_id" = "privilege"."member_id"
1855 WHERE "member"."active" AND "privilege"."voting_right"
1856 ORDER BY
1857 "issue"."id",
1858 "delegation"."truster_id",
1859 "delegation"."scope" DESC;
1861 COMMENT ON VIEW "issue_delegation" IS 'Issue delegations where trusters are active and have voting right';
1864 CREATE FUNCTION "membership_weight_with_skipping"
1865 ( "area_id_p" "area"."id"%TYPE,
1866 "member_id_p" "member"."id"%TYPE,
1867 "skip_member_ids_p" INT4[] ) -- "member"."id"%TYPE[]
1868 RETURNS INT4
1869 LANGUAGE 'plpgsql' STABLE AS $$
1870 DECLARE
1871 "sum_v" INT4;
1872 "delegation_row" "area_delegation"%ROWTYPE;
1873 BEGIN
1874 "sum_v" := 1;
1875 FOR "delegation_row" IN
1876 SELECT "area_delegation".*
1877 FROM "area_delegation" LEFT JOIN "membership"
1878 ON "membership"."area_id" = "area_id_p"
1879 AND "membership"."member_id" = "area_delegation"."truster_id"
1880 WHERE "area_delegation"."area_id" = "area_id_p"
1881 AND "area_delegation"."trustee_id" = "member_id_p"
1882 AND "membership"."member_id" ISNULL
1883 LOOP
1884 IF NOT
1885 "skip_member_ids_p" @> ARRAY["delegation_row"."truster_id"]
1886 THEN
1887 "sum_v" := "sum_v" + "membership_weight_with_skipping"(
1888 "area_id_p",
1889 "delegation_row"."truster_id",
1890 "skip_member_ids_p" || "delegation_row"."truster_id"
1891 );
1892 END IF;
1893 END LOOP;
1894 RETURN "sum_v";
1895 END;
1896 $$;
1898 COMMENT ON FUNCTION "membership_weight_with_skipping"
1899 ( "area"."id"%TYPE,
1900 "member"."id"%TYPE,
1901 INT4[] )
1902 IS 'Helper function for "membership_weight" function';
1905 CREATE FUNCTION "membership_weight"
1906 ( "area_id_p" "area"."id"%TYPE,
1907 "member_id_p" "member"."id"%TYPE ) -- "member"."id"%TYPE[]
1908 RETURNS INT4
1909 LANGUAGE 'plpgsql' STABLE AS $$
1910 BEGIN
1911 RETURN "membership_weight_with_skipping"(
1912 "area_id_p",
1913 "member_id_p",
1914 ARRAY["member_id_p"]
1915 );
1916 END;
1917 $$;
1919 COMMENT ON FUNCTION "membership_weight"
1920 ( "area"."id"%TYPE,
1921 "member"."id"%TYPE )
1922 IS 'Calculates the potential voting weight of a member in a given area';
1925 CREATE VIEW "member_count_view" AS
1926 SELECT count(1) AS "total_count" FROM "member" WHERE "active";
1928 COMMENT ON VIEW "member_count_view" IS 'View used to update "member_count" table';
1931 CREATE VIEW "unit_member_count" AS
1932 SELECT
1933 "unit"."id" AS "unit_id",
1934 count("member"."id") AS "member_count"
1935 FROM "unit"
1936 LEFT JOIN "privilege"
1937 ON "privilege"."unit_id" = "unit"."id"
1938 AND "privilege"."voting_right"
1939 LEFT JOIN "member"
1940 ON "member"."id" = "privilege"."member_id"
1941 AND "member"."active"
1942 GROUP BY "unit"."id";
1944 COMMENT ON VIEW "unit_member_count" IS 'View used to update "member_count" column of "unit" table';
1947 CREATE VIEW "area_member_count" AS
1948 SELECT
1949 "area"."id" AS "area_id",
1950 count("member"."id") AS "direct_member_count",
1951 coalesce(
1952 sum(
1953 CASE WHEN "member"."id" NOTNULL THEN
1954 "membership_weight"("area"."id", "member"."id")
1955 ELSE 0 END
1956 )
1957 ) AS "member_weight"
1958 FROM "area"
1959 LEFT JOIN "membership"
1960 ON "area"."id" = "membership"."area_id"
1961 LEFT JOIN "privilege"
1962 ON "privilege"."unit_id" = "area"."unit_id"
1963 AND "privilege"."member_id" = "membership"."member_id"
1964 AND "privilege"."voting_right"
1965 LEFT JOIN "member"
1966 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
1967 AND "member"."active"
1968 GROUP BY "area"."id";
1970 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"';
1973 CREATE VIEW "opening_draft" AS
1974 SELECT "draft".* FROM (
1975 SELECT
1976 "initiative"."id" AS "initiative_id",
1977 min("draft"."id") AS "draft_id"
1978 FROM "initiative" JOIN "draft"
1979 ON "initiative"."id" = "draft"."initiative_id"
1980 GROUP BY "initiative"."id"
1981 ) AS "subquery"
1982 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1984 COMMENT ON VIEW "opening_draft" IS 'First drafts of all initiatives';
1987 CREATE VIEW "current_draft" AS
1988 SELECT "draft".* FROM (
1989 SELECT
1990 "initiative"."id" AS "initiative_id",
1991 max("draft"."id") AS "draft_id"
1992 FROM "initiative" JOIN "draft"
1993 ON "initiative"."id" = "draft"."initiative_id"
1994 GROUP BY "initiative"."id"
1995 ) AS "subquery"
1996 JOIN "draft" ON "subquery"."draft_id" = "draft"."id";
1998 COMMENT ON VIEW "current_draft" IS 'All latest drafts for each initiative';
2001 CREATE VIEW "critical_opinion" AS
2002 SELECT * FROM "opinion"
2003 WHERE ("degree" = 2 AND "fulfilled" = FALSE)
2004 OR ("degree" = -2 AND "fulfilled" = TRUE);
2006 COMMENT ON VIEW "critical_opinion" IS 'Opinions currently causing dissatisfaction';
2009 CREATE VIEW "battle_participant" AS
2010 SELECT "initiative"."id", "initiative"."issue_id"
2011 FROM "issue" JOIN "initiative"
2012 ON "issue"."id" = "initiative"."issue_id"
2013 WHERE "initiative"."admitted"
2014 UNION ALL
2015 SELECT NULL, "id" AS "issue_id"
2016 FROM "issue";
2018 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
2021 CREATE VIEW "battle_view" AS
2022 SELECT
2023 "issue"."id" AS "issue_id",
2024 "winning_initiative"."id" AS "winning_initiative_id",
2025 "losing_initiative"."id" AS "losing_initiative_id",
2026 sum(
2027 CASE WHEN
2028 coalesce("better_vote"."grade", 0) >
2029 coalesce("worse_vote"."grade", 0)
2030 THEN "direct_voter"."weight" ELSE 0 END
2031 ) AS "count"
2032 FROM "issue"
2033 LEFT JOIN "direct_voter"
2034 ON "issue"."id" = "direct_voter"."issue_id"
2035 JOIN "battle_participant" AS "winning_initiative"
2036 ON "issue"."id" = "winning_initiative"."issue_id"
2037 JOIN "battle_participant" AS "losing_initiative"
2038 ON "issue"."id" = "losing_initiative"."issue_id"
2039 LEFT JOIN "vote" AS "better_vote"
2040 ON "direct_voter"."member_id" = "better_vote"."member_id"
2041 AND "winning_initiative"."id" = "better_vote"."initiative_id"
2042 LEFT JOIN "vote" AS "worse_vote"
2043 ON "direct_voter"."member_id" = "worse_vote"."member_id"
2044 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
2045 WHERE "issue"."closed" NOTNULL
2046 AND "issue"."cleaned" ISNULL
2047 AND (
2048 "winning_initiative"."id" != "losing_initiative"."id" OR
2049 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
2050 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
2051 GROUP BY
2052 "issue"."id",
2053 "winning_initiative"."id",
2054 "losing_initiative"."id";
2056 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';
2059 CREATE VIEW "expired_session" AS
2060 SELECT * FROM "session" WHERE now() > "expiry";
2062 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
2063 DELETE FROM "session" WHERE "ident" = OLD."ident";
2065 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
2066 COMMENT ON RULE "delete" ON "expired_session" IS 'Rule allowing DELETE on rows in "expired_session" view, i.e. DELETE FROM "expired_session"';
2069 CREATE VIEW "open_issue" AS
2070 SELECT * FROM "issue" WHERE "closed" ISNULL;
2072 COMMENT ON VIEW "open_issue" IS 'All open issues';
2075 CREATE VIEW "issue_with_ranks_missing" AS
2076 SELECT * FROM "issue"
2077 WHERE "fully_frozen" NOTNULL
2078 AND "closed" NOTNULL
2079 AND "ranks_available" = FALSE;
2081 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
2084 CREATE VIEW "member_contingent" AS
2085 SELECT
2086 "member"."id" AS "member_id",
2087 "contingent"."time_frame",
2088 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
2089 (
2090 SELECT count(1) FROM "draft"
2091 WHERE "draft"."author_id" = "member"."id"
2092 AND "draft"."created" > now() - "contingent"."time_frame"
2093 ) + (
2094 SELECT count(1) FROM "suggestion"
2095 WHERE "suggestion"."author_id" = "member"."id"
2096 AND "suggestion"."created" > now() - "contingent"."time_frame"
2097 )
2098 ELSE NULL END AS "text_entry_count",
2099 "contingent"."text_entry_limit",
2100 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
2101 SELECT count(1) FROM "opening_draft"
2102 WHERE "opening_draft"."author_id" = "member"."id"
2103 AND "opening_draft"."created" > now() - "contingent"."time_frame"
2104 ) ELSE NULL END AS "initiative_count",
2105 "contingent"."initiative_limit"
2106 FROM "member" CROSS JOIN "contingent";
2108 COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.';
2110 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
2111 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
2114 CREATE VIEW "member_contingent_left" AS
2115 SELECT
2116 "member_id",
2117 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
2118 max("initiative_limit" - "initiative_count") AS "initiatives_left"
2119 FROM "member_contingent" GROUP BY "member_id";
2121 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.';
2124 CREATE VIEW "event_seen_by_member" AS
2125 SELECT
2126 "member"."id" AS "seen_by_member_id",
2127 CASE WHEN "event"."state" IN (
2128 'voting',
2129 'finished_without_winner',
2130 'finished_with_winner'
2131 ) THEN
2132 'voting'::"notify_level"
2133 ELSE
2134 CASE WHEN "event"."state" IN (
2135 'verification',
2136 'canceled_after_revocation_during_verification',
2137 'canceled_no_initiative_admitted'
2138 ) THEN
2139 'verification'::"notify_level"
2140 ELSE
2141 CASE WHEN "event"."state" IN (
2142 'discussion',
2143 'canceled_after_revocation_during_discussion'
2144 ) THEN
2145 'discussion'::"notify_level"
2146 ELSE
2147 'all'::"notify_level"
2148 END
2149 END
2150 END AS "notify_level",
2151 "event".*
2152 FROM "member" CROSS JOIN "event"
2153 LEFT JOIN "issue"
2154 ON "event"."issue_id" = "issue"."id"
2155 LEFT JOIN "membership"
2156 ON "member"."id" = "membership"."member_id"
2157 AND "issue"."area_id" = "membership"."area_id"
2158 LEFT JOIN "interest"
2159 ON "member"."id" = "interest"."member_id"
2160 AND "event"."issue_id" = "interest"."issue_id"
2161 LEFT JOIN "supporter"
2162 ON "member"."id" = "supporter"."member_id"
2163 AND "event"."initiative_id" = "supporter"."initiative_id"
2164 LEFT JOIN "ignored_member"
2165 ON "member"."id" = "ignored_member"."member_id"
2166 AND "event"."member_id" = "ignored_member"."other_member_id"
2167 LEFT JOIN "ignored_initiative"
2168 ON "member"."id" = "ignored_initiative"."member_id"
2169 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2170 WHERE (
2171 "supporter"."member_id" NOTNULL OR
2172 "interest"."member_id" NOTNULL OR
2173 ( "membership"."member_id" NOTNULL AND
2174 "event"."event" IN (
2175 'issue_state_changed',
2176 'initiative_created_in_new_issue',
2177 'initiative_created_in_existing_issue',
2178 'initiative_revoked' ) ) )
2179 AND "ignored_member"."member_id" ISNULL
2180 AND "ignored_initiative"."member_id" ISNULL;
2182 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"';
2185 CREATE VIEW "selected_event_seen_by_member" AS
2186 SELECT
2187 "member"."id" AS "seen_by_member_id",
2188 CASE WHEN "event"."state" IN (
2189 'voting',
2190 'finished_without_winner',
2191 'finished_with_winner'
2192 ) THEN
2193 'voting'::"notify_level"
2194 ELSE
2195 CASE WHEN "event"."state" IN (
2196 'verification',
2197 'canceled_after_revocation_during_verification',
2198 'canceled_no_initiative_admitted'
2199 ) THEN
2200 'verification'::"notify_level"
2201 ELSE
2202 CASE WHEN "event"."state" IN (
2203 'discussion',
2204 'canceled_after_revocation_during_discussion'
2205 ) THEN
2206 'discussion'::"notify_level"
2207 ELSE
2208 'all'::"notify_level"
2209 END
2210 END
2211 END AS "notify_level",
2212 "event".*
2213 FROM "member" CROSS JOIN "event"
2214 LEFT JOIN "issue"
2215 ON "event"."issue_id" = "issue"."id"
2216 LEFT JOIN "membership"
2217 ON "member"."id" = "membership"."member_id"
2218 AND "issue"."area_id" = "membership"."area_id"
2219 LEFT JOIN "interest"
2220 ON "member"."id" = "interest"."member_id"
2221 AND "event"."issue_id" = "interest"."issue_id"
2222 LEFT JOIN "supporter"
2223 ON "member"."id" = "supporter"."member_id"
2224 AND "event"."initiative_id" = "supporter"."initiative_id"
2225 LEFT JOIN "ignored_member"
2226 ON "member"."id" = "ignored_member"."member_id"
2227 AND "event"."member_id" = "ignored_member"."other_member_id"
2228 LEFT JOIN "ignored_initiative"
2229 ON "member"."id" = "ignored_initiative"."member_id"
2230 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
2231 WHERE (
2232 ( "member"."notify_level" >= 'all' ) OR
2233 ( "member"."notify_level" >= 'voting' AND
2234 "event"."state" IN (
2235 'voting',
2236 'finished_without_winner',
2237 'finished_with_winner' ) ) OR
2238 ( "member"."notify_level" >= 'verification' AND
2239 "event"."state" IN (
2240 'verification',
2241 'canceled_after_revocation_during_verification',
2242 'canceled_no_initiative_admitted' ) ) OR
2243 ( "member"."notify_level" >= 'discussion' AND
2244 "event"."state" IN (
2245 'discussion',
2246 'canceled_after_revocation_during_discussion' ) ) )
2247 AND (
2248 "supporter"."member_id" NOTNULL OR
2249 "interest"."member_id" NOTNULL OR
2250 ( "membership"."member_id" NOTNULL AND
2251 "event"."event" IN (
2252 'issue_state_changed',
2253 'initiative_created_in_new_issue',
2254 'initiative_created_in_existing_issue',
2255 'initiative_revoked' ) ) )
2256 AND "ignored_member"."member_id" ISNULL
2257 AND "ignored_initiative"."member_id" ISNULL;
2259 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"';
2262 CREATE TYPE "timeline_event" AS ENUM (
2263 'issue_created',
2264 'issue_canceled',
2265 'issue_accepted',
2266 'issue_half_frozen',
2267 'issue_finished_without_voting',
2268 'issue_voting_started',
2269 'issue_finished_after_voting',
2270 'initiative_created',
2271 'initiative_revoked',
2272 'draft_created',
2273 'suggestion_created');
2275 COMMENT ON TYPE "timeline_event" IS 'Types of event in timeline tables (DEPRECATED)';
2278 CREATE VIEW "timeline_issue" AS
2279 SELECT
2280 "created" AS "occurrence",
2281 'issue_created'::"timeline_event" AS "event",
2282 "id" AS "issue_id"
2283 FROM "issue"
2284 UNION ALL
2285 SELECT
2286 "closed" AS "occurrence",
2287 'issue_canceled'::"timeline_event" AS "event",
2288 "id" AS "issue_id"
2289 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" ISNULL
2290 UNION ALL
2291 SELECT
2292 "accepted" AS "occurrence",
2293 'issue_accepted'::"timeline_event" AS "event",
2294 "id" AS "issue_id"
2295 FROM "issue" WHERE "accepted" NOTNULL
2296 UNION ALL
2297 SELECT
2298 "half_frozen" AS "occurrence",
2299 'issue_half_frozen'::"timeline_event" AS "event",
2300 "id" AS "issue_id"
2301 FROM "issue" WHERE "half_frozen" NOTNULL
2302 UNION ALL
2303 SELECT
2304 "fully_frozen" AS "occurrence",
2305 'issue_voting_started'::"timeline_event" AS "event",
2306 "id" AS "issue_id"
2307 FROM "issue"
2308 WHERE "fully_frozen" NOTNULL
2309 AND ("closed" ISNULL OR "closed" != "fully_frozen")
2310 UNION ALL
2311 SELECT
2312 "closed" AS "occurrence",
2313 CASE WHEN "fully_frozen" = "closed" THEN
2314 'issue_finished_without_voting'::"timeline_event"
2315 ELSE
2316 'issue_finished_after_voting'::"timeline_event"
2317 END AS "event",
2318 "id" AS "issue_id"
2319 FROM "issue" WHERE "closed" NOTNULL AND "fully_frozen" NOTNULL;
2321 COMMENT ON VIEW "timeline_issue" IS 'Helper view for "timeline" view (DEPRECATED)';
2324 CREATE VIEW "timeline_initiative" AS
2325 SELECT
2326 "created" AS "occurrence",
2327 'initiative_created'::"timeline_event" AS "event",
2328 "id" AS "initiative_id"
2329 FROM "initiative"
2330 UNION ALL
2331 SELECT
2332 "revoked" AS "occurrence",
2333 'initiative_revoked'::"timeline_event" AS "event",
2334 "id" AS "initiative_id"
2335 FROM "initiative" WHERE "revoked" NOTNULL;
2337 COMMENT ON VIEW "timeline_initiative" IS 'Helper view for "timeline" view (DEPRECATED)';
2340 CREATE VIEW "timeline_draft" AS
2341 SELECT
2342 "created" AS "occurrence",
2343 'draft_created'::"timeline_event" AS "event",
2344 "id" AS "draft_id"
2345 FROM "draft";
2347 COMMENT ON VIEW "timeline_draft" IS 'Helper view for "timeline" view (DEPRECATED)';
2350 CREATE VIEW "timeline_suggestion" AS
2351 SELECT
2352 "created" AS "occurrence",
2353 'suggestion_created'::"timeline_event" AS "event",
2354 "id" AS "suggestion_id"
2355 FROM "suggestion";
2357 COMMENT ON VIEW "timeline_suggestion" IS 'Helper view for "timeline" view (DEPRECATED)';
2360 CREATE VIEW "timeline" AS
2361 SELECT
2362 "occurrence",
2363 "event",
2364 "issue_id",
2365 NULL AS "initiative_id",
2366 NULL::INT8 AS "draft_id", -- TODO: Why do we need a type-cast here? Is this due to 32 bit architecture?
2367 NULL::INT8 AS "suggestion_id"
2368 FROM "timeline_issue"
2369 UNION ALL
2370 SELECT
2371 "occurrence",
2372 "event",
2373 NULL AS "issue_id",
2374 "initiative_id",
2375 NULL AS "draft_id",
2376 NULL AS "suggestion_id"
2377 FROM "timeline_initiative"
2378 UNION ALL
2379 SELECT
2380 "occurrence",
2381 "event",
2382 NULL AS "issue_id",
2383 NULL AS "initiative_id",
2384 "draft_id",
2385 NULL AS "suggestion_id"
2386 FROM "timeline_draft"
2387 UNION ALL
2388 SELECT
2389 "occurrence",
2390 "event",
2391 NULL AS "issue_id",
2392 NULL AS "initiative_id",
2393 NULL AS "draft_id",
2394 "suggestion_id"
2395 FROM "timeline_suggestion";
2397 COMMENT ON VIEW "timeline" IS 'Aggregation of different events in the system (DEPRECATED)';
2401 ------------------------------------------------------
2402 -- Row set returning function for delegation chains --
2403 ------------------------------------------------------
2406 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
2407 ('first', 'intermediate', 'last', 'repetition');
2409 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
2412 CREATE TYPE "delegation_chain_row" AS (
2413 "index" INT4,
2414 "member_id" INT4,
2415 "member_valid" BOOLEAN,
2416 "participation" BOOLEAN,
2417 "overridden" BOOLEAN,
2418 "scope_in" "delegation_scope",
2419 "scope_out" "delegation_scope",
2420 "disabled_out" BOOLEAN,
2421 "loop" "delegation_chain_loop_tag" );
2423 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';
2425 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
2426 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';
2427 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
2428 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
2429 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
2430 COMMENT ON COLUMN "delegation_chain_row"."disabled_out" IS 'Outgoing delegation is explicitly disabled by a delegation with trustee_id set to NULL';
2431 COMMENT ON COLUMN "delegation_chain_row"."loop" IS 'Not null, if member is part of a loop, see "delegation_chain_loop_tag" type';
2434 CREATE FUNCTION "delegation_chain_for_closed_issue"
2435 ( "member_id_p" "member"."id"%TYPE,
2436 "issue_id_p" "issue"."id"%TYPE )
2437 RETURNS SETOF "delegation_chain_row"
2438 LANGUAGE 'plpgsql' STABLE AS $$
2439 DECLARE
2440 "output_row" "delegation_chain_row";
2441 "direct_voter_row" "direct_voter"%ROWTYPE;
2442 "delegating_voter_row" "delegating_voter"%ROWTYPE;
2443 BEGIN
2444 "output_row"."index" := 0;
2445 "output_row"."member_id" := "member_id_p";
2446 "output_row"."member_valid" := TRUE;
2447 "output_row"."participation" := FALSE;
2448 "output_row"."overridden" := FALSE;
2449 "output_row"."disabled_out" := FALSE;
2450 LOOP
2451 SELECT INTO "direct_voter_row" * FROM "direct_voter"
2452 WHERE "issue_id" = "issue_id_p"
2453 AND "member_id" = "output_row"."member_id";
2454 IF "direct_voter_row"."member_id" NOTNULL THEN
2455 "output_row"."participation" := TRUE;
2456 "output_row"."scope_out" := NULL;
2457 "output_row"."disabled_out" := NULL;
2458 RETURN NEXT "output_row";
2459 RETURN;
2460 END IF;
2461 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"
2462 WHERE "issue_id" = "issue_id_p"
2463 AND "member_id" = "output_row"."member_id";
2464 IF "delegating_voter_row"."member_id" ISNULL THEN
2465 RETURN;
2466 END IF;
2467 "output_row"."scope_out" := "delegating_voter_row"."scope";
2468 RETURN NEXT "output_row";
2469 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];
2470 "output_row"."scope_in" := "output_row"."scope_out";
2471 END LOOP;
2472 END;
2473 $$;
2475 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"
2476 ( "member"."id"%TYPE,
2477 "member"."id"%TYPE )
2478 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';
2481 CREATE FUNCTION "delegation_chain"
2482 ( "member_id_p" "member"."id"%TYPE,
2483 "unit_id_p" "unit"."id"%TYPE,
2484 "area_id_p" "area"."id"%TYPE,
2485 "issue_id_p" "issue"."id"%TYPE,
2486 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2487 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2488 RETURNS SETOF "delegation_chain_row"
2489 LANGUAGE 'plpgsql' STABLE AS $$
2490 DECLARE
2491 "scope_v" "delegation_scope";
2492 "unit_id_v" "unit"."id"%TYPE;
2493 "area_id_v" "area"."id"%TYPE;
2494 "issue_row" "issue"%ROWTYPE;
2495 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
2496 "loop_member_id_v" "member"."id"%TYPE;
2497 "output_row" "delegation_chain_row";
2498 "output_rows" "delegation_chain_row"[];
2499 "simulate_v" BOOLEAN;
2500 "simulate_here_v" BOOLEAN;
2501 "delegation_row" "delegation"%ROWTYPE;
2502 "row_count" INT4;
2503 "i" INT4;
2504 "loop_v" BOOLEAN;
2505 BEGIN
2506 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
2507 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
2508 END IF;
2509 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
2510 "simulate_v" := TRUE;
2511 ELSE
2512 "simulate_v" := FALSE;
2513 END IF;
2514 IF
2515 "unit_id_p" NOTNULL AND
2516 "area_id_p" ISNULL AND
2517 "issue_id_p" ISNULL
2518 THEN
2519 "scope_v" := 'unit';
2520 "unit_id_v" := "unit_id_p";
2521 ELSIF
2522 "unit_id_p" ISNULL AND
2523 "area_id_p" NOTNULL AND
2524 "issue_id_p" ISNULL
2525 THEN
2526 "scope_v" := 'area';
2527 "area_id_v" := "area_id_p";
2528 SELECT "unit_id" INTO "unit_id_v"
2529 FROM "area" WHERE "id" = "area_id_v";
2530 ELSIF
2531 "unit_id_p" ISNULL AND
2532 "area_id_p" ISNULL AND
2533 "issue_id_p" NOTNULL
2534 THEN
2535 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
2536 IF "issue_row"."id" ISNULL THEN
2537 RETURN;
2538 END IF;
2539 IF "issue_row"."closed" NOTNULL THEN
2540 IF "simulate_v" THEN
2541 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
2542 END IF;
2543 FOR "output_row" IN
2544 SELECT * FROM
2545 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
2546 LOOP
2547 RETURN NEXT "output_row";
2548 END LOOP;
2549 RETURN;
2550 END IF;
2551 "scope_v" := 'issue';
2552 SELECT "area_id" INTO "area_id_v"
2553 FROM "issue" WHERE "id" = "issue_id_p";
2554 SELECT "unit_id" INTO "unit_id_v"
2555 FROM "area" WHERE "id" = "area_id_v";
2556 ELSE
2557 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
2558 END IF;
2559 "visited_member_ids" := '{}';
2560 "loop_member_id_v" := NULL;
2561 "output_rows" := '{}';
2562 "output_row"."index" := 0;
2563 "output_row"."member_id" := "member_id_p";
2564 "output_row"."member_valid" := TRUE;
2565 "output_row"."participation" := FALSE;
2566 "output_row"."overridden" := FALSE;
2567 "output_row"."disabled_out" := FALSE;
2568 "output_row"."scope_out" := NULL;
2569 LOOP
2570 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
2571 "loop_member_id_v" := "output_row"."member_id";
2572 ELSE
2573 "visited_member_ids" :=
2574 "visited_member_ids" || "output_row"."member_id";
2575 END IF;
2576 IF "output_row"."participation" ISNULL THEN
2577 "output_row"."overridden" := NULL;
2578 ELSIF "output_row"."participation" THEN
2579 "output_row"."overridden" := TRUE;
2580 END IF;
2581 "output_row"."scope_in" := "output_row"."scope_out";
2582 "output_row"."member_valid" := EXISTS (
2583 SELECT NULL FROM "member" JOIN "privilege"
2584 ON "privilege"."member_id" = "member"."id"
2585 AND "privilege"."unit_id" = "unit_id_v"
2586 WHERE "id" = "output_row"."member_id"
2587 AND "member"."active" AND "privilege"."voting_right"
2588 );
2589 "simulate_here_v" := (
2590 "simulate_v" AND
2591 "output_row"."member_id" = "member_id_p"
2592 );
2593 "delegation_row" := ROW(NULL);
2594 IF "output_row"."member_valid" OR "simulate_here_v" THEN
2595 IF "scope_v" = 'unit' THEN
2596 IF NOT "simulate_here_v" THEN
2597 SELECT * INTO "delegation_row" FROM "delegation"
2598 WHERE "truster_id" = "output_row"."member_id"
2599 AND "unit_id" = "unit_id_v";
2600 END IF;
2601 ELSIF "scope_v" = 'area' THEN
2602 "output_row"."participation" := EXISTS (
2603 SELECT NULL FROM "membership"
2604 WHERE "area_id" = "area_id_p"
2605 AND "member_id" = "output_row"."member_id"
2606 );
2607 IF "simulate_here_v" THEN
2608 IF "simulate_trustee_id_p" ISNULL THEN
2609 SELECT * INTO "delegation_row" FROM "delegation"
2610 WHERE "truster_id" = "output_row"."member_id"
2611 AND "unit_id" = "unit_id_v";
2612 END IF;
2613 ELSE
2614 SELECT * INTO "delegation_row" FROM "delegation"
2615 WHERE "truster_id" = "output_row"."member_id"
2616 AND (
2617 "unit_id" = "unit_id_v" OR
2618 "area_id" = "area_id_v"
2619 )
2620 ORDER BY "scope" DESC;
2621 END IF;
2622 ELSIF "scope_v" = 'issue' THEN
2623 IF "issue_row"."fully_frozen" ISNULL THEN
2624 "output_row"."participation" := EXISTS (
2625 SELECT NULL FROM "interest"
2626 WHERE "issue_id" = "issue_id_p"
2627 AND "member_id" = "output_row"."member_id"
2628 );
2629 ELSE
2630 IF "output_row"."member_id" = "member_id_p" THEN
2631 "output_row"."participation" := EXISTS (
2632 SELECT NULL FROM "direct_voter"
2633 WHERE "issue_id" = "issue_id_p"
2634 AND "member_id" = "output_row"."member_id"
2635 );
2636 ELSE
2637 "output_row"."participation" := NULL;
2638 END IF;
2639 END IF;
2640 IF "simulate_here_v" THEN
2641 IF "simulate_trustee_id_p" ISNULL THEN
2642 SELECT * INTO "delegation_row" FROM "delegation"
2643 WHERE "truster_id" = "output_row"."member_id"
2644 AND (
2645 "unit_id" = "unit_id_v" OR
2646 "area_id" = "area_id_v"
2647 )
2648 ORDER BY "scope" DESC;
2649 END IF;
2650 ELSE
2651 SELECT * INTO "delegation_row" FROM "delegation"
2652 WHERE "truster_id" = "output_row"."member_id"
2653 AND (
2654 "unit_id" = "unit_id_v" OR
2655 "area_id" = "area_id_v" OR
2656 "issue_id" = "issue_id_p"
2657 )
2658 ORDER BY "scope" DESC;
2659 END IF;
2660 END IF;
2661 ELSE
2662 "output_row"."participation" := FALSE;
2663 END IF;
2664 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
2665 "output_row"."scope_out" := "scope_v";
2666 "output_rows" := "output_rows" || "output_row";
2667 "output_row"."member_id" := "simulate_trustee_id_p";
2668 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
2669 "output_row"."scope_out" := "delegation_row"."scope";
2670 "output_rows" := "output_rows" || "output_row";
2671 "output_row"."member_id" := "delegation_row"."trustee_id";
2672 ELSIF "delegation_row"."scope" NOTNULL THEN
2673 "output_row"."scope_out" := "delegation_row"."scope";
2674 "output_row"."disabled_out" := TRUE;
2675 "output_rows" := "output_rows" || "output_row";
2676 EXIT;
2677 ELSE
2678 "output_row"."scope_out" := NULL;
2679 "output_rows" := "output_rows" || "output_row";
2680 EXIT;
2681 END IF;
2682 EXIT WHEN "loop_member_id_v" NOTNULL;
2683 "output_row"."index" := "output_row"."index" + 1;
2684 END LOOP;
2685 "row_count" := array_upper("output_rows", 1);
2686 "i" := 1;
2687 "loop_v" := FALSE;
2688 LOOP
2689 "output_row" := "output_rows"["i"];
2690 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
2691 IF "loop_v" THEN
2692 IF "i" + 1 = "row_count" THEN
2693 "output_row"."loop" := 'last';
2694 ELSIF "i" = "row_count" THEN
2695 "output_row"."loop" := 'repetition';
2696 ELSE
2697 "output_row"."loop" := 'intermediate';
2698 END IF;
2699 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
2700 "output_row"."loop" := 'first';
2701 "loop_v" := TRUE;
2702 END IF;
2703 IF "scope_v" = 'unit' THEN
2704 "output_row"."participation" := NULL;
2705 END IF;
2706 RETURN NEXT "output_row";
2707 "i" := "i" + 1;
2708 END LOOP;
2709 RETURN;
2710 END;
2711 $$;
2713 COMMENT ON FUNCTION "delegation_chain"
2714 ( "member"."id"%TYPE,
2715 "unit"."id"%TYPE,
2716 "area"."id"%TYPE,
2717 "issue"."id"%TYPE,
2718 "member"."id"%TYPE,
2719 BOOLEAN )
2720 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
2724 ---------------------------------------------------------
2725 -- Single row returning function for delegation chains --
2726 ---------------------------------------------------------
2729 CREATE TYPE "delegation_info_loop_type" AS ENUM
2730 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');
2732 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''';
2735 CREATE TYPE "delegation_info_type" AS (
2736 "own_participation" BOOLEAN,
2737 "own_delegation_scope" "delegation_scope",
2738 "first_trustee_id" INT4,
2739 "first_trustee_participation" BOOLEAN,
2740 "first_trustee_ellipsis" BOOLEAN,
2741 "other_trustee_id" INT4,
2742 "other_trustee_participation" BOOLEAN,
2743 "other_trustee_ellipsis" BOOLEAN,
2744 "delegation_loop" "delegation_info_loop_type",
2745 "participating_member_id" INT4 );
2747 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';
2749 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';
2750 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';
2751 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';
2752 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
2753 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';
2754 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';
2755 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)';
2756 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';
2757 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';
2758 COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain';
2761 CREATE FUNCTION "delegation_info"
2762 ( "member_id_p" "member"."id"%TYPE,
2763 "unit_id_p" "unit"."id"%TYPE,
2764 "area_id_p" "area"."id"%TYPE,
2765 "issue_id_p" "issue"."id"%TYPE,
2766 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
2767 "simulate_default_p" BOOLEAN DEFAULT FALSE )
2768 RETURNS "delegation_info_type"
2769 LANGUAGE 'plpgsql' STABLE AS $$
2770 DECLARE
2771 "current_row" "delegation_chain_row";
2772 "result" "delegation_info_type";
2773 BEGIN
2774 "result"."own_participation" := FALSE;
2775 FOR "current_row" IN
2776 SELECT * FROM "delegation_chain"(
2777 "member_id_p",
2778 "unit_id_p", "area_id_p", "issue_id_p",
2779 "simulate_trustee_id_p", "simulate_default_p")
2780 LOOP
2781 IF
2782 "result"."participating_member_id" ISNULL AND
2783 "current_row"."participation"
2784 THEN
2785 "result"."participating_member_id" := "current_row"."member_id";
2786 END IF;
2787 IF "current_row"."member_id" = "member_id_p" THEN
2788 "result"."own_participation" := "current_row"."participation";
2789 "result"."own_delegation_scope" := "current_row"."scope_out";
2790 IF "current_row"."loop" = 'first' THEN
2791 "result"."delegation_loop" := 'own';
2792 END IF;
2793 ELSIF
2794 "current_row"."member_valid" AND
2795 ( "current_row"."loop" ISNULL OR
2796 "current_row"."loop" != 'repetition' )
2797 THEN
2798 IF "result"."first_trustee_id" ISNULL THEN
2799 "result"."first_trustee_id" := "current_row"."member_id";
2800 "result"."first_trustee_participation" := "current_row"."participation";
2801 "result"."first_trustee_ellipsis" := FALSE;
2802 IF "current_row"."loop" = 'first' THEN
2803 "result"."delegation_loop" := 'first';
2804 END IF;
2805 ELSIF "result"."other_trustee_id" ISNULL THEN
2806 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
2807 "result"."other_trustee_id" := "current_row"."member_id";
2808 "result"."other_trustee_participation" := TRUE;
2809 "result"."other_trustee_ellipsis" := FALSE;
2810 IF "current_row"."loop" = 'first' THEN
2811 "result"."delegation_loop" := 'other';
2812 END IF;
2813 ELSE
2814 "result"."first_trustee_ellipsis" := TRUE;
2815 IF "current_row"."loop" = 'first' THEN
2816 "result"."delegation_loop" := 'first_ellipsis';
2817 END IF;
2818 END IF;
2819 ELSE
2820 "result"."other_trustee_ellipsis" := TRUE;
2821 IF "current_row"."loop" = 'first' THEN
2822 "result"."delegation_loop" := 'other_ellipsis';
2823 END IF;
2824 END IF;
2825 END IF;
2826 END LOOP;
2827 RETURN "result";
2828 END;
2829 $$;
2831 COMMENT ON FUNCTION "delegation_info"
2832 ( "member"."id"%TYPE,
2833 "unit"."id"%TYPE,
2834 "area"."id"%TYPE,
2835 "issue"."id"%TYPE,
2836 "member"."id"%TYPE,
2837 BOOLEAN )
2838 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
2842 ------------------------------
2843 -- Comparison by vote count --
2844 ------------------------------
2846 CREATE FUNCTION "vote_ratio"
2847 ( "positive_votes_p" "initiative"."positive_votes"%TYPE,
2848 "negative_votes_p" "initiative"."negative_votes"%TYPE )
2849 RETURNS FLOAT8
2850 LANGUAGE 'plpgsql' STABLE AS $$
2851 BEGIN
2852 IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN
2853 RETURN
2854 "positive_votes_p"::FLOAT8 /
2855 ("positive_votes_p" + "negative_votes_p")::FLOAT8;
2856 ELSIF "positive_votes_p" > 0 THEN
2857 RETURN "positive_votes_p";
2858 ELSIF "negative_votes_p" > 0 THEN
2859 RETURN 1 - "negative_votes_p";
2860 ELSE
2861 RETURN 0.5;
2862 END IF;
2863 END;
2864 $$;
2866 COMMENT ON FUNCTION "vote_ratio"
2867 ( "initiative"."positive_votes"%TYPE,
2868 "initiative"."negative_votes"%TYPE )
2869 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.';
2873 ------------------------------------------------
2874 -- Locking for snapshots and voting procedure --
2875 ------------------------------------------------
2878 CREATE FUNCTION "share_row_lock_issue_trigger"()
2879 RETURNS TRIGGER
2880 LANGUAGE 'plpgsql' VOLATILE AS $$
2881 BEGIN
2882 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2883 PERFORM NULL FROM "issue" WHERE "id" = OLD."issue_id" FOR SHARE;
2884 END IF;
2885 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2886 PERFORM NULL FROM "issue" WHERE "id" = NEW."issue_id" FOR SHARE;
2887 RETURN NEW;
2888 ELSE
2889 RETURN OLD;
2890 END IF;
2891 END;
2892 $$;
2894 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of triggers "share_row_lock_issue" on multiple tables';
2897 CREATE FUNCTION "share_row_lock_issue_via_initiative_trigger"()
2898 RETURNS TRIGGER
2899 LANGUAGE 'plpgsql' VOLATILE AS $$
2900 BEGIN
2901 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
2902 PERFORM NULL FROM "issue"
2903 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2904 WHERE "initiative"."id" = OLD."initiative_id"
2905 FOR SHARE OF "issue";
2906 END IF;
2907 IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
2908 PERFORM NULL FROM "issue"
2909 JOIN "initiative" ON "issue"."id" = "initiative"."issue_id"
2910 WHERE "initiative"."id" = NEW."initiative_id"
2911 FOR SHARE OF "issue";
2912 RETURN NEW;
2913 ELSE
2914 RETURN OLD;
2915 END IF;
2916 END;
2917 $$;
2919 COMMENT ON FUNCTION "share_row_lock_issue_trigger"() IS 'Implementation of trigger "share_row_lock_issue_via_initiative" on table "opinion"';
2922 CREATE TRIGGER "share_row_lock_issue"
2923 BEFORE INSERT OR UPDATE OR DELETE ON "initiative"
2924 FOR EACH ROW EXECUTE PROCEDURE
2925 "share_row_lock_issue_trigger"();
2927 CREATE TRIGGER "share_row_lock_issue"
2928 BEFORE INSERT OR UPDATE OR DELETE ON "interest"
2929 FOR EACH ROW EXECUTE PROCEDURE
2930 "share_row_lock_issue_trigger"();
2932 CREATE TRIGGER "share_row_lock_issue"
2933 BEFORE INSERT OR UPDATE OR DELETE ON "supporter"
2934 FOR EACH ROW EXECUTE PROCEDURE
2935 "share_row_lock_issue_trigger"();
2937 CREATE TRIGGER "share_row_lock_issue_via_initiative"
2938 BEFORE INSERT OR UPDATE OR DELETE ON "opinion"
2939 FOR EACH ROW EXECUTE PROCEDURE
2940 "share_row_lock_issue_via_initiative_trigger"();
2942 CREATE TRIGGER "share_row_lock_issue"
2943 BEFORE INSERT OR UPDATE OR DELETE ON "direct_voter"
2944 FOR EACH ROW EXECUTE PROCEDURE
2945 "share_row_lock_issue_trigger"();
2947 CREATE TRIGGER "share_row_lock_issue"
2948 BEFORE INSERT OR UPDATE OR DELETE ON "delegating_voter"
2949 FOR EACH ROW EXECUTE PROCEDURE
2950 "share_row_lock_issue_trigger"();
2952 CREATE TRIGGER "share_row_lock_issue"
2953 BEFORE INSERT OR UPDATE OR DELETE ON "vote"
2954 FOR EACH ROW EXECUTE PROCEDURE
2955 "share_row_lock_issue_trigger"();
2957 COMMENT ON TRIGGER "share_row_lock_issue" ON "initiative" IS 'See "lock_issue" function';
2958 COMMENT ON TRIGGER "share_row_lock_issue" ON "interest" IS 'See "lock_issue" function';
2959 COMMENT ON TRIGGER "share_row_lock_issue" ON "supporter" IS 'See "lock_issue" function';
2960 COMMENT ON TRIGGER "share_row_lock_issue_via_initiative" ON "opinion" IS 'See "lock_issue" function';
2961 COMMENT ON TRIGGER "share_row_lock_issue" ON "direct_voter" IS 'See "lock_issue" function';
2962 COMMENT ON TRIGGER "share_row_lock_issue" ON "delegating_voter" IS 'See "lock_issue" function';
2963 COMMENT ON TRIGGER "share_row_lock_issue" ON "vote" IS 'See "lock_issue" function';
2966 CREATE FUNCTION "lock_issue"
2967 ( "issue_id_p" "issue"."id"%TYPE )
2968 RETURNS VOID
2969 LANGUAGE 'plpgsql' VOLATILE AS $$
2970 BEGIN
2971 LOCK TABLE "member" IN SHARE MODE;
2972 LOCK TABLE "privilege" IN SHARE MODE;
2973 LOCK TABLE "membership" IN SHARE MODE;
2974 LOCK TABLE "policy" IN SHARE MODE;
2975 PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE;
2976 -- NOTE: The row-level exclusive lock in combination with the
2977 -- share_row_lock_issue(_via_initiative)_trigger functions (which
2978 -- acquire a row-level share lock on the issue) ensure that no data
2979 -- is changed, which could affect calculation of snapshots or
2980 -- counting of votes. Table "delegation" must be table-level-locked,
2981 -- as it also contains issue- and global-scope delegations.
2982 LOCK TABLE "delegation" IN SHARE MODE;
2983 LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE;
2984 LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE;
2985 LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE;
2986 LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE;
2987 LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE;
2988 RETURN;
2989 END;
2990 $$;
2992 COMMENT ON FUNCTION "lock_issue"
2993 ( "issue"."id"%TYPE )
2994 IS 'Locks the issue and all other data which is used for calculating snapshots or counting votes.';
2998 ------------------------------------------------------------------------
2999 -- Regular tasks, except calculcation of snapshots and voting results --
3000 ------------------------------------------------------------------------
3002 CREATE FUNCTION "check_activity"()
3003 RETURNS VOID
3004 LANGUAGE 'plpgsql' VOLATILE AS $$
3005 DECLARE
3006 "system_setting_row" "system_setting"%ROWTYPE;
3007 BEGIN
3008 SELECT * INTO "system_setting_row" FROM "system_setting";
3009 LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
3010 IF "system_setting_row"."member_ttl" NOTNULL THEN
3011 UPDATE "member" SET "active" = FALSE
3012 WHERE "active" = TRUE
3013 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
3014 END IF;
3015 RETURN;
3016 END;
3017 $$;
3019 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
3022 CREATE FUNCTION "calculate_member_counts"()
3023 RETURNS VOID
3024 LANGUAGE 'plpgsql' VOLATILE AS $$
3025 BEGIN
3026 LOCK TABLE "member" IN SHARE MODE;
3027 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
3028 LOCK TABLE "unit" IN EXCLUSIVE MODE;
3029 LOCK TABLE "area" IN EXCLUSIVE MODE;
3030 LOCK TABLE "privilege" IN SHARE MODE;
3031 LOCK TABLE "membership" IN SHARE MODE;
3032 DELETE FROM "member_count";
3033 INSERT INTO "member_count" ("total_count")
3034 SELECT "total_count" FROM "member_count_view";
3035 UPDATE "unit" SET "member_count" = "view"."member_count"
3036 FROM "unit_member_count" AS "view"
3037 WHERE "view"."unit_id" = "unit"."id";
3038 UPDATE "area" SET
3039 "direct_member_count" = "view"."direct_member_count",
3040 "member_weight" = "view"."member_weight"
3041 FROM "area_member_count" AS "view"
3042 WHERE "view"."area_id" = "area"."id";
3043 RETURN;
3044 END;
3045 $$;
3047 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"';
3051 ------------------------------
3052 -- Calculation of snapshots --
3053 ------------------------------
3055 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
3056 ( "issue_id_p" "issue"."id"%TYPE,
3057 "member_id_p" "member"."id"%TYPE,
3058 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3059 RETURNS "direct_population_snapshot"."weight"%TYPE
3060 LANGUAGE 'plpgsql' VOLATILE AS $$
3061 DECLARE
3062 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3063 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
3064 "weight_v" INT4;
3065 "sub_weight_v" INT4;
3066 BEGIN
3067 "weight_v" := 0;
3068 FOR "issue_delegation_row" IN
3069 SELECT * FROM "issue_delegation"
3070 WHERE "trustee_id" = "member_id_p"
3071 AND "issue_id" = "issue_id_p"
3072 LOOP
3073 IF NOT EXISTS (
3074 SELECT NULL FROM "direct_population_snapshot"
3075 WHERE "issue_id" = "issue_id_p"
3076 AND "event" = 'periodic'
3077 AND "member_id" = "issue_delegation_row"."truster_id"
3078 ) AND NOT EXISTS (
3079 SELECT NULL FROM "delegating_population_snapshot"
3080 WHERE "issue_id" = "issue_id_p"
3081 AND "event" = 'periodic'
3082 AND "member_id" = "issue_delegation_row"."truster_id"
3083 ) THEN
3084 "delegate_member_ids_v" :=
3085 "member_id_p" || "delegate_member_ids_p";
3086 INSERT INTO "delegating_population_snapshot" (
3087 "issue_id",
3088 "event",
3089 "member_id",
3090 "scope",
3091 "delegate_member_ids"
3092 ) VALUES (
3093 "issue_id_p",
3094 'periodic',
3095 "issue_delegation_row"."truster_id",
3096 "issue_delegation_row"."scope",
3097 "delegate_member_ids_v"
3098 );
3099 "sub_weight_v" := 1 +
3100 "weight_of_added_delegations_for_population_snapshot"(
3101 "issue_id_p",
3102 "issue_delegation_row"."truster_id",
3103 "delegate_member_ids_v"
3104 );
3105 UPDATE "delegating_population_snapshot"
3106 SET "weight" = "sub_weight_v"
3107 WHERE "issue_id" = "issue_id_p"
3108 AND "event" = 'periodic'
3109 AND "member_id" = "issue_delegation_row"."truster_id";
3110 "weight_v" := "weight_v" + "sub_weight_v";
3111 END IF;
3112 END LOOP;
3113 RETURN "weight_v";
3114 END;
3115 $$;
3117 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
3118 ( "issue"."id"%TYPE,
3119 "member"."id"%TYPE,
3120 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
3121 IS 'Helper function for "create_population_snapshot" function';
3124 CREATE FUNCTION "create_population_snapshot"
3125 ( "issue_id_p" "issue"."id"%TYPE )
3126 RETURNS VOID
3127 LANGUAGE 'plpgsql' VOLATILE AS $$
3128 DECLARE
3129 "member_id_v" "member"."id"%TYPE;
3130 BEGIN
3131 DELETE FROM "direct_population_snapshot"
3132 WHERE "issue_id" = "issue_id_p"
3133 AND "event" = 'periodic';
3134 DELETE FROM "delegating_population_snapshot"
3135 WHERE "issue_id" = "issue_id_p"
3136 AND "event" = 'periodic';
3137 INSERT INTO "direct_population_snapshot"
3138 ("issue_id", "event", "member_id")
3139 SELECT
3140 "issue_id_p" AS "issue_id",
3141 'periodic'::"snapshot_event" AS "event",
3142 "member"."id" AS "member_id"
3143 FROM "issue"
3144 JOIN "area" ON "issue"."area_id" = "area"."id"
3145 JOIN "membership" ON "area"."id" = "membership"."area_id"
3146 JOIN "member" ON "membership"."member_id" = "member"."id"
3147 JOIN "privilege"
3148 ON "privilege"."unit_id" = "area"."unit_id"
3149 AND "privilege"."member_id" = "member"."id"
3150 WHERE "issue"."id" = "issue_id_p"
3151 AND "member"."active" AND "privilege"."voting_right"
3152 UNION
3153 SELECT
3154 "issue_id_p" AS "issue_id",
3155 'periodic'::"snapshot_event" AS "event",
3156 "member"."id" AS "member_id"
3157 FROM "issue"
3158 JOIN "area" ON "issue"."area_id" = "area"."id"
3159 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3160 JOIN "member" ON "interest"."member_id" = "member"."id"
3161 JOIN "privilege"
3162 ON "privilege"."unit_id" = "area"."unit_id"
3163 AND "privilege"."member_id" = "member"."id"
3164 WHERE "issue"."id" = "issue_id_p"
3165 AND "member"."active" AND "privilege"."voting_right";
3166 FOR "member_id_v" IN
3167 SELECT "member_id" FROM "direct_population_snapshot"
3168 WHERE "issue_id" = "issue_id_p"
3169 AND "event" = 'periodic'
3170 LOOP
3171 UPDATE "direct_population_snapshot" SET
3172 "weight" = 1 +
3173 "weight_of_added_delegations_for_population_snapshot"(
3174 "issue_id_p",
3175 "member_id_v",
3176 '{}'
3177 )
3178 WHERE "issue_id" = "issue_id_p"
3179 AND "event" = 'periodic'
3180 AND "member_id" = "member_id_v";
3181 END LOOP;
3182 RETURN;
3183 END;
3184 $$;
3186 COMMENT ON FUNCTION "create_population_snapshot"
3187 ( "issue"."id"%TYPE )
3188 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.';
3191 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3192 ( "issue_id_p" "issue"."id"%TYPE,
3193 "member_id_p" "member"."id"%TYPE,
3194 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3195 RETURNS "direct_interest_snapshot"."weight"%TYPE
3196 LANGUAGE 'plpgsql' VOLATILE AS $$
3197 DECLARE
3198 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3199 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
3200 "weight_v" INT4;
3201 "sub_weight_v" INT4;
3202 BEGIN
3203 "weight_v" := 0;
3204 FOR "issue_delegation_row" IN
3205 SELECT * FROM "issue_delegation"
3206 WHERE "trustee_id" = "member_id_p"
3207 AND "issue_id" = "issue_id_p"
3208 LOOP
3209 IF NOT EXISTS (
3210 SELECT NULL FROM "direct_interest_snapshot"
3211 WHERE "issue_id" = "issue_id_p"
3212 AND "event" = 'periodic'
3213 AND "member_id" = "issue_delegation_row"."truster_id"
3214 ) AND NOT EXISTS (
3215 SELECT NULL FROM "delegating_interest_snapshot"
3216 WHERE "issue_id" = "issue_id_p"
3217 AND "event" = 'periodic'
3218 AND "member_id" = "issue_delegation_row"."truster_id"
3219 ) THEN
3220 "delegate_member_ids_v" :=
3221 "member_id_p" || "delegate_member_ids_p";
3222 INSERT INTO "delegating_interest_snapshot" (
3223 "issue_id",
3224 "event",
3225 "member_id",
3226 "scope",
3227 "delegate_member_ids"
3228 ) VALUES (
3229 "issue_id_p",
3230 'periodic',
3231 "issue_delegation_row"."truster_id",
3232 "issue_delegation_row"."scope",
3233 "delegate_member_ids_v"
3234 );
3235 "sub_weight_v" := 1 +
3236 "weight_of_added_delegations_for_interest_snapshot"(
3237 "issue_id_p",
3238 "issue_delegation_row"."truster_id",
3239 "delegate_member_ids_v"
3240 );
3241 UPDATE "delegating_interest_snapshot"
3242 SET "weight" = "sub_weight_v"
3243 WHERE "issue_id" = "issue_id_p"
3244 AND "event" = 'periodic'
3245 AND "member_id" = "issue_delegation_row"."truster_id";
3246 "weight_v" := "weight_v" + "sub_weight_v";
3247 END IF;
3248 END LOOP;
3249 RETURN "weight_v";
3250 END;
3251 $$;
3253 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
3254 ( "issue"."id"%TYPE,
3255 "member"."id"%TYPE,
3256 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
3257 IS 'Helper function for "create_interest_snapshot" function';
3260 CREATE FUNCTION "create_interest_snapshot"
3261 ( "issue_id_p" "issue"."id"%TYPE )
3262 RETURNS VOID
3263 LANGUAGE 'plpgsql' VOLATILE AS $$
3264 DECLARE
3265 "member_id_v" "member"."id"%TYPE;
3266 BEGIN
3267 DELETE FROM "direct_interest_snapshot"
3268 WHERE "issue_id" = "issue_id_p"
3269 AND "event" = 'periodic';
3270 DELETE FROM "delegating_interest_snapshot"
3271 WHERE "issue_id" = "issue_id_p"
3272 AND "event" = 'periodic';
3273 DELETE FROM "direct_supporter_snapshot"
3274 WHERE "issue_id" = "issue_id_p"
3275 AND "event" = 'periodic';
3276 INSERT INTO "direct_interest_snapshot"
3277 ("issue_id", "event", "member_id")
3278 SELECT
3279 "issue_id_p" AS "issue_id",
3280 'periodic' AS "event",
3281 "member"."id" AS "member_id"
3282 FROM "issue"
3283 JOIN "area" ON "issue"."area_id" = "area"."id"
3284 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
3285 JOIN "member" ON "interest"."member_id" = "member"."id"
3286 JOIN "privilege"
3287 ON "privilege"."unit_id" = "area"."unit_id"
3288 AND "privilege"."member_id" = "member"."id"
3289 WHERE "issue"."id" = "issue_id_p"
3290 AND "member"."active" AND "privilege"."voting_right";
3291 FOR "member_id_v" IN
3292 SELECT "member_id" FROM "direct_interest_snapshot"
3293 WHERE "issue_id" = "issue_id_p"
3294 AND "event" = 'periodic'
3295 LOOP
3296 UPDATE "direct_interest_snapshot" SET
3297 "weight" = 1 +
3298 "weight_of_added_delegations_for_interest_snapshot"(
3299 "issue_id_p",
3300 "member_id_v",
3301 '{}'
3302 )
3303 WHERE "issue_id" = "issue_id_p"
3304 AND "event" = 'periodic'
3305 AND "member_id" = "member_id_v";
3306 END LOOP;
3307 INSERT INTO "direct_supporter_snapshot"
3308 ( "issue_id", "initiative_id", "event", "member_id",
3309 "draft_id", "informed", "satisfied" )
3310 SELECT
3311 "issue_id_p" AS "issue_id",
3312 "initiative"."id" AS "initiative_id",
3313 'periodic' AS "event",
3314 "supporter"."member_id" AS "member_id",
3315 "supporter"."draft_id" AS "draft_id",
3316 "supporter"."draft_id" = "current_draft"."id" AS "informed",
3317 NOT EXISTS (
3318 SELECT NULL FROM "critical_opinion"
3319 WHERE "initiative_id" = "initiative"."id"
3320 AND "member_id" = "supporter"."member_id"
3321 ) AS "satisfied"
3322 FROM "initiative"
3323 JOIN "supporter"
3324 ON "supporter"."initiative_id" = "initiative"."id"
3325 JOIN "current_draft"
3326 ON "initiative"."id" = "current_draft"."initiative_id"
3327 JOIN "direct_interest_snapshot"
3328 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
3329 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
3330 AND "event" = 'periodic'
3331 WHERE "initiative"."issue_id" = "issue_id_p";
3332 RETURN;
3333 END;
3334 $$;
3336 COMMENT ON FUNCTION "create_interest_snapshot"
3337 ( "issue"."id"%TYPE )
3338 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.';
3341 CREATE FUNCTION "create_snapshot"
3342 ( "issue_id_p" "issue"."id"%TYPE )
3343 RETURNS VOID
3344 LANGUAGE 'plpgsql' VOLATILE AS $$
3345 DECLARE
3346 "initiative_id_v" "initiative"."id"%TYPE;
3347 "suggestion_id_v" "suggestion"."id"%TYPE;
3348 BEGIN
3349 PERFORM "lock_issue"("issue_id_p");
3350 PERFORM "create_population_snapshot"("issue_id_p");
3351 PERFORM "create_interest_snapshot"("issue_id_p");
3352 UPDATE "issue" SET
3353 "snapshot" = now(),
3354 "latest_snapshot_event" = 'periodic',
3355 "population" = (
3356 SELECT coalesce(sum("weight"), 0)
3357 FROM "direct_population_snapshot"
3358 WHERE "issue_id" = "issue_id_p"
3359 AND "event" = 'periodic'
3360 )
3361 WHERE "id" = "issue_id_p";
3362 FOR "initiative_id_v" IN
3363 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
3364 LOOP
3365 UPDATE "initiative" SET
3366 "supporter_count" = (
3367 SELECT coalesce(sum("di"."weight"), 0)
3368 FROM "direct_interest_snapshot" AS "di"
3369 JOIN "direct_supporter_snapshot" AS "ds"
3370 ON "di"."member_id" = "ds"."member_id"
3371 WHERE "di"."issue_id" = "issue_id_p"
3372 AND "di"."event" = 'periodic'
3373 AND "ds"."initiative_id" = "initiative_id_v"
3374 AND "ds"."event" = 'periodic'
3375 ),
3376 "informed_supporter_count" = (
3377 SELECT coalesce(sum("di"."weight"), 0)
3378 FROM "direct_interest_snapshot" AS "di"
3379 JOIN "direct_supporter_snapshot" AS "ds"
3380 ON "di"."member_id" = "ds"."member_id"
3381 WHERE "di"."issue_id" = "issue_id_p"
3382 AND "di"."event" = 'periodic'
3383 AND "ds"."initiative_id" = "initiative_id_v"
3384 AND "ds"."event" = 'periodic'
3385 AND "ds"."informed"
3386 ),
3387 "satisfied_supporter_count" = (
3388 SELECT coalesce(sum("di"."weight"), 0)
3389 FROM "direct_interest_snapshot" AS "di"
3390 JOIN "direct_supporter_snapshot" AS "ds"
3391 ON "di"."member_id" = "ds"."member_id"
3392 WHERE "di"."issue_id" = "issue_id_p"
3393 AND "di"."event" = 'periodic'
3394 AND "ds"."initiative_id" = "initiative_id_v"
3395 AND "ds"."event" = 'periodic'
3396 AND "ds"."satisfied"
3397 ),
3398 "satisfied_informed_supporter_count" = (
3399 SELECT coalesce(sum("di"."weight"), 0)
3400 FROM "direct_interest_snapshot" AS "di"
3401 JOIN "direct_supporter_snapshot" AS "ds"
3402 ON "di"."member_id" = "ds"."member_id"
3403 WHERE "di"."issue_id" = "issue_id_p"
3404 AND "di"."event" = 'periodic'
3405 AND "ds"."initiative_id" = "initiative_id_v"
3406 AND "ds"."event" = 'periodic'
3407 AND "ds"."informed"
3408 AND "ds"."satisfied"
3409 )
3410 WHERE "id" = "initiative_id_v";
3411 FOR "suggestion_id_v" IN
3412 SELECT "id" FROM "suggestion"
3413 WHERE "initiative_id" = "initiative_id_v"
3414 LOOP
3415 UPDATE "suggestion" SET
3416 "minus2_unfulfilled_count" = (
3417 SELECT coalesce(sum("snapshot"."weight"), 0)
3418 FROM "issue" CROSS JOIN "opinion"
3419 JOIN "direct_interest_snapshot" AS "snapshot"
3420 ON "snapshot"."issue_id" = "issue"."id"
3421 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3422 AND "snapshot"."member_id" = "opinion"."member_id"
3423 WHERE "issue"."id" = "issue_id_p"
3424 AND "opinion"."suggestion_id" = "suggestion_id_v"
3425 AND "opinion"."degree" = -2
3426 AND "opinion"."fulfilled" = FALSE
3427 ),
3428 "minus2_fulfilled_count" = (
3429 SELECT coalesce(sum("snapshot"."weight"), 0)
3430 FROM "issue" CROSS JOIN "opinion"
3431 JOIN "direct_interest_snapshot" AS "snapshot"
3432 ON "snapshot"."issue_id" = "issue"."id"
3433 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3434 AND "snapshot"."member_id" = "opinion"."member_id"
3435 WHERE "issue"."id" = "issue_id_p"
3436 AND "opinion"."suggestion_id" = "suggestion_id_v"
3437 AND "opinion"."degree" = -2
3438 AND "opinion"."fulfilled" = TRUE
3439 ),
3440 "minus1_unfulfilled_count" = (
3441 SELECT coalesce(sum("snapshot"."weight"), 0)
3442 FROM "issue" CROSS JOIN "opinion"
3443 JOIN "direct_interest_snapshot" AS "snapshot"
3444 ON "snapshot"."issue_id" = "issue"."id"
3445 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3446 AND "snapshot"."member_id" = "opinion"."member_id"
3447 WHERE "issue"."id" = "issue_id_p"
3448 AND "opinion"."suggestion_id" = "suggestion_id_v"
3449 AND "opinion"."degree" = -1
3450 AND "opinion"."fulfilled" = FALSE
3451 ),
3452 "minus1_fulfilled_count" = (
3453 SELECT coalesce(sum("snapshot"."weight"), 0)
3454 FROM "issue" CROSS JOIN "opinion"
3455 JOIN "direct_interest_snapshot" AS "snapshot"
3456 ON "snapshot"."issue_id" = "issue"."id"
3457 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3458 AND "snapshot"."member_id" = "opinion"."member_id"
3459 WHERE "issue"."id" = "issue_id_p"
3460 AND "opinion"."suggestion_id" = "suggestion_id_v"
3461 AND "opinion"."degree" = -1
3462 AND "opinion"."fulfilled" = TRUE
3463 ),
3464 "plus1_unfulfilled_count" = (
3465 SELECT coalesce(sum("snapshot"."weight"), 0)
3466 FROM "issue" CROSS JOIN "opinion"
3467 JOIN "direct_interest_snapshot" AS "snapshot"
3468 ON "snapshot"."issue_id" = "issue"."id"
3469 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3470 AND "snapshot"."member_id" = "opinion"."member_id"
3471 WHERE "issue"."id" = "issue_id_p"
3472 AND "opinion"."suggestion_id" = "suggestion_id_v"
3473 AND "opinion"."degree" = 1
3474 AND "opinion"."fulfilled" = FALSE
3475 ),
3476 "plus1_fulfilled_count" = (
3477 SELECT coalesce(sum("snapshot"."weight"), 0)
3478 FROM "issue" CROSS JOIN "opinion"
3479 JOIN "direct_interest_snapshot" AS "snapshot"
3480 ON "snapshot"."issue_id" = "issue"."id"
3481 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3482 AND "snapshot"."member_id" = "opinion"."member_id"
3483 WHERE "issue"."id" = "issue_id_p"
3484 AND "opinion"."suggestion_id" = "suggestion_id_v"
3485 AND "opinion"."degree" = 1
3486 AND "opinion"."fulfilled" = TRUE
3487 ),
3488 "plus2_unfulfilled_count" = (
3489 SELECT coalesce(sum("snapshot"."weight"), 0)
3490 FROM "issue" CROSS JOIN "opinion"
3491 JOIN "direct_interest_snapshot" AS "snapshot"
3492 ON "snapshot"."issue_id" = "issue"."id"
3493 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3494 AND "snapshot"."member_id" = "opinion"."member_id"
3495 WHERE "issue"."id" = "issue_id_p"
3496 AND "opinion"."suggestion_id" = "suggestion_id_v"
3497 AND "opinion"."degree" = 2
3498 AND "opinion"."fulfilled" = FALSE
3499 ),
3500 "plus2_fulfilled_count" = (
3501 SELECT coalesce(sum("snapshot"."weight"), 0)
3502 FROM "issue" CROSS JOIN "opinion"
3503 JOIN "direct_interest_snapshot" AS "snapshot"
3504 ON "snapshot"."issue_id" = "issue"."id"
3505 AND "snapshot"."event" = "issue"."latest_snapshot_event"
3506 AND "snapshot"."member_id" = "opinion"."member_id"
3507 WHERE "issue"."id" = "issue_id_p"
3508 AND "opinion"."suggestion_id" = "suggestion_id_v"
3509 AND "opinion"."degree" = 2
3510 AND "opinion"."fulfilled" = TRUE
3511 )
3512 WHERE "suggestion"."id" = "suggestion_id_v";
3513 END LOOP;
3514 END LOOP;
3515 RETURN;
3516 END;
3517 $$;
3519 COMMENT ON FUNCTION "create_snapshot"
3520 ( "issue"."id"%TYPE )
3521 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.';
3524 CREATE FUNCTION "set_snapshot_event"
3525 ( "issue_id_p" "issue"."id"%TYPE,
3526 "event_p" "snapshot_event" )
3527 RETURNS VOID
3528 LANGUAGE 'plpgsql' VOLATILE AS $$
3529 DECLARE
3530 "event_v" "issue"."latest_snapshot_event"%TYPE;
3531 BEGIN
3532 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
3533 WHERE "id" = "issue_id_p" FOR UPDATE;
3534 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
3535 WHERE "id" = "issue_id_p";
3536 UPDATE "direct_population_snapshot" SET "event" = "event_p"
3537 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3538 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
3539 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3540 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
3541 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3542 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
3543 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3544 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
3545 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
3546 RETURN;
3547 END;
3548 $$;
3550 COMMENT ON FUNCTION "set_snapshot_event"
3551 ( "issue"."id"%TYPE,
3552 "snapshot_event" )
3553 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
3557 ---------------------
3558 -- Freezing issues --
3559 ---------------------
3561 CREATE FUNCTION "freeze_after_snapshot"
3562 ( "issue_id_p" "issue"."id"%TYPE )
3563 RETURNS VOID
3564 LANGUAGE 'plpgsql' VOLATILE AS $$
3565 DECLARE
3566 "issue_row" "issue"%ROWTYPE;
3567 "policy_row" "policy"%ROWTYPE;
3568 "initiative_row" "initiative"%ROWTYPE;
3569 BEGIN
3570 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
3571 SELECT * INTO "policy_row"
3572 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3573 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
3574 FOR "initiative_row" IN
3575 SELECT * FROM "initiative"
3576 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
3577 LOOP
3578 IF
3579 "initiative_row"."polling" OR (
3580 "initiative_row"."satisfied_supporter_count" > 0 AND
3581 "initiative_row"."satisfied_supporter_count" *
3582 "policy_row"."initiative_quorum_den" >=
3583 "issue_row"."population" * "policy_row"."initiative_quorum_num"
3584 )
3585 THEN
3586 UPDATE "initiative" SET "admitted" = TRUE
3587 WHERE "id" = "initiative_row"."id";
3588 ELSE
3589 UPDATE "initiative" SET "admitted" = FALSE
3590 WHERE "id" = "initiative_row"."id";
3591 END IF;
3592 END LOOP;
3593 IF EXISTS (
3594 SELECT NULL FROM "initiative"
3595 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
3596 ) THEN
3597 UPDATE "issue" SET
3598 "state" = 'voting',
3599 "accepted" = coalesce("accepted", now()),
3600 "half_frozen" = coalesce("half_frozen", now()),
3601 "fully_frozen" = now()
3602 WHERE "id" = "issue_id_p";
3603 ELSE
3604 UPDATE "issue" SET
3605 "state" = 'canceled_no_initiative_admitted',
3606 "accepted" = coalesce("accepted", now()),
3607 "half_frozen" = coalesce("half_frozen", now()),
3608 "fully_frozen" = now(),
3609 "closed" = now(),
3610 "ranks_available" = TRUE
3611 WHERE "id" = "issue_id_p";
3612 -- NOTE: The following DELETE statements have effect only when
3613 -- issue state has been manipulated
3614 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
3615 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
3616 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3617 END IF;
3618 RETURN;
3619 END;
3620 $$;
3622 COMMENT ON FUNCTION "freeze_after_snapshot"
3623 ( "issue"."id"%TYPE )
3624 IS 'This function freezes an issue (fully) and starts voting, but must only be called when "create_snapshot" was called in the same transaction.';
3627 CREATE FUNCTION "manual_freeze"("issue_id_p" "issue"."id"%TYPE)
3628 RETURNS VOID
3629 LANGUAGE 'plpgsql' VOLATILE AS $$
3630 DECLARE
3631 "issue_row" "issue"%ROWTYPE;
3632 BEGIN
3633 PERFORM "create_snapshot"("issue_id_p");
3634 PERFORM "freeze_after_snapshot"("issue_id_p");
3635 RETURN;
3636 END;
3637 $$;
3639 COMMENT ON FUNCTION "manual_freeze"
3640 ( "issue"."id"%TYPE )
3641 IS 'Freeze an issue manually (fully) and start voting';
3645 -----------------------
3646 -- Counting of votes --
3647 -----------------------
3650 CREATE FUNCTION "weight_of_added_vote_delegations"
3651 ( "issue_id_p" "issue"."id"%TYPE,
3652 "member_id_p" "member"."id"%TYPE,
3653 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
3654 RETURNS "direct_voter"."weight"%TYPE
3655 LANGUAGE 'plpgsql' VOLATILE AS $$
3656 DECLARE
3657 "issue_delegation_row" "issue_delegation"%ROWTYPE;
3658 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
3659 "weight_v" INT4;
3660 "sub_weight_v" INT4;
3661 BEGIN
3662 "weight_v" := 0;
3663 FOR "issue_delegation_row" IN
3664 SELECT * FROM "issue_delegation"
3665 WHERE "trustee_id" = "member_id_p"
3666 AND "issue_id" = "issue_id_p"
3667 LOOP
3668 IF NOT EXISTS (
3669 SELECT NULL FROM "direct_voter"
3670 WHERE "member_id" = "issue_delegation_row"."truster_id"
3671 AND "issue_id" = "issue_id_p"
3672 ) AND NOT EXISTS (
3673 SELECT NULL FROM "delegating_voter"
3674 WHERE "member_id" = "issue_delegation_row"."truster_id"
3675 AND "issue_id" = "issue_id_p"
3676 ) THEN
3677 "delegate_member_ids_v" :=
3678 "member_id_p" || "delegate_member_ids_p";
3679 INSERT INTO "delegating_voter" (
3680 "issue_id",
3681 "member_id",
3682 "scope",
3683 "delegate_member_ids"
3684 ) VALUES (
3685 "issue_id_p",
3686 "issue_delegation_row"."truster_id",
3687 "issue_delegation_row"."scope",
3688 "delegate_member_ids_v"
3689 );
3690 "sub_weight_v" := 1 +
3691 "weight_of_added_vote_delegations"(
3692 "issue_id_p",
3693 "issue_delegation_row"."truster_id",
3694 "delegate_member_ids_v"
3695 );
3696 UPDATE "delegating_voter"
3697 SET "weight" = "sub_weight_v"
3698 WHERE "issue_id" = "issue_id_p"
3699 AND "member_id" = "issue_delegation_row"."truster_id";
3700 "weight_v" := "weight_v" + "sub_weight_v";
3701 END IF;
3702 END LOOP;
3703 RETURN "weight_v";
3704 END;
3705 $$;
3707 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
3708 ( "issue"."id"%TYPE,
3709 "member"."id"%TYPE,
3710 "delegating_voter"."delegate_member_ids"%TYPE )
3711 IS 'Helper function for "add_vote_delegations" function';
3714 CREATE FUNCTION "add_vote_delegations"
3715 ( "issue_id_p" "issue"."id"%TYPE )
3716 RETURNS VOID
3717 LANGUAGE 'plpgsql' VOLATILE AS $$
3718 DECLARE
3719 "member_id_v" "member"."id"%TYPE;
3720 BEGIN
3721 FOR "member_id_v" IN
3722 SELECT "member_id" FROM "direct_voter"
3723 WHERE "issue_id" = "issue_id_p"
3724 LOOP
3725 UPDATE "direct_voter" SET
3726 "weight" = "weight" + "weight_of_added_vote_delegations"(
3727 "issue_id_p",
3728 "member_id_v",
3729 '{}'
3730 )
3731 WHERE "member_id" = "member_id_v"
3732 AND "issue_id" = "issue_id_p";
3733 END LOOP;
3734 RETURN;
3735 END;
3736 $$;
3738 COMMENT ON FUNCTION "add_vote_delegations"
3739 ( "issue_id_p" "issue"."id"%TYPE )
3740 IS 'Helper function for "close_voting" function';
3743 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
3744 RETURNS VOID
3745 LANGUAGE 'plpgsql' VOLATILE AS $$
3746 DECLARE
3747 "area_id_v" "area"."id"%TYPE;
3748 "unit_id_v" "unit"."id"%TYPE;
3749 "member_id_v" "member"."id"%TYPE;
3750 BEGIN
3751 PERFORM "lock_issue"("issue_id_p");
3752 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
3753 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
3754 -- delete delegating votes (in cases of manual reset of issue state):
3755 DELETE FROM "delegating_voter"
3756 WHERE "issue_id" = "issue_id_p";
3757 -- delete votes from non-privileged voters:
3758 DELETE FROM "direct_voter"
3759 USING (
3760 SELECT
3761 "direct_voter"."member_id"
3762 FROM "direct_voter"
3763 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
3764 LEFT JOIN "privilege"
3765 ON "privilege"."unit_id" = "unit_id_v"
3766 AND "privilege"."member_id" = "direct_voter"."member_id"
3767 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
3768 "member"."active" = FALSE OR
3769 "privilege"."voting_right" ISNULL OR
3770 "privilege"."voting_right" = FALSE
3771 )
3772 ) AS "subquery"
3773 WHERE "direct_voter"."issue_id" = "issue_id_p"
3774 AND "direct_voter"."member_id" = "subquery"."member_id";
3775 -- consider delegations:
3776 UPDATE "direct_voter" SET "weight" = 1
3777 WHERE "issue_id" = "issue_id_p";
3778 PERFORM "add_vote_delegations"("issue_id_p");
3779 -- set voter count and mark issue as being calculated:
3780 UPDATE "issue" SET
3781 "state" = 'calculation',
3782 "closed" = now(),
3783 "voter_count" = (
3784 SELECT coalesce(sum("weight"), 0)
3785 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
3786 )
3787 WHERE "id" = "issue_id_p";
3788 -- materialize battle_view:
3789 -- NOTE: "closed" column of issue must be set at this point
3790 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
3791 INSERT INTO "battle" (
3792 "issue_id",
3793 "winning_initiative_id", "losing_initiative_id",
3794 "count"
3795 ) SELECT
3796 "issue_id",
3797 "winning_initiative_id", "losing_initiative_id",
3798 "count"
3799 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
3800 -- copy "positive_votes" and "negative_votes" from "battle" table:
3801 UPDATE "initiative" SET
3802 "positive_votes" = "battle_win"."count",
3803 "negative_votes" = "battle_lose"."count"
3804 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
3805 WHERE
3806 "battle_win"."issue_id" = "issue_id_p" AND
3807 "battle_win"."winning_initiative_id" = "initiative"."id" AND
3808 "battle_win"."losing_initiative_id" ISNULL AND
3809 "battle_lose"."issue_id" = "issue_id_p" AND
3810 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
3811 "battle_lose"."winning_initiative_id" ISNULL;
3812 END;
3813 $$;
3815 COMMENT ON FUNCTION "close_voting"
3816 ( "issue"."id"%TYPE )
3817 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.';
3820 CREATE FUNCTION "defeat_strength"
3821 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
3822 RETURNS INT8
3823 LANGUAGE 'plpgsql' IMMUTABLE AS $$
3824 BEGIN
3825 IF "positive_votes_p" > "negative_votes_p" THEN
3826 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
3827 ELSIF "positive_votes_p" = "negative_votes_p" THEN
3828 RETURN 0;
3829 ELSE
3830 RETURN -1;
3831 END IF;
3832 END;
3833 $$;
3835 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';
3838 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
3839 RETURNS VOID
3840 LANGUAGE 'plpgsql' VOLATILE AS $$
3841 DECLARE
3842 "issue_row" "issue"%ROWTYPE;
3843 "policy_row" "policy"%ROWTYPE;
3844 "dimension_v" INTEGER;
3845 "vote_matrix" INT4[][]; -- absolute votes
3846 "matrix" INT8[][]; -- defeat strength / best paths
3847 "i" INTEGER;
3848 "j" INTEGER;
3849 "k" INTEGER;
3850 "battle_row" "battle"%ROWTYPE;
3851 "rank_ary" INT4[];
3852 "rank_v" INT4;
3853 "done_v" INTEGER;
3854 "winners_ary" INTEGER[];
3855 "initiative_id_v" "initiative"."id"%TYPE;
3856 BEGIN
3857 SELECT * INTO "issue_row"
3858 FROM "issue" WHERE "id" = "issue_id_p"
3859 FOR UPDATE;
3860 SELECT * INTO "policy_row"
3861 FROM "policy" WHERE "id" = "issue_row"."policy_id";
3862 SELECT count(1) INTO "dimension_v"
3863 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
3864 -- Create "vote_matrix" with absolute number of votes in pairwise
3865 -- comparison:
3866 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
3867 "i" := 1;
3868 "j" := 2;
3869 FOR "battle_row" IN
3870 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
3871 ORDER BY
3872 "winning_initiative_id" NULLS LAST,
3873 "losing_initiative_id" NULLS LAST
3874 LOOP
3875 "vote_matrix"["i"]["j"] := "battle_row"."count";
3876 IF "j" = "dimension_v" THEN
3877 "i" := "i" + 1;
3878 "j" := 1;
3879 ELSE
3880 "j" := "j" + 1;
3881 IF "j" = "i" THEN
3882 "j" := "j" + 1;
3883 END IF;
3884 END IF;
3885 END LOOP;
3886 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
3887 RAISE EXCEPTION 'Wrong battle count (should not happen)';
3888 END IF;
3889 -- Store defeat strengths in "matrix" using "defeat_strength"
3890 -- function:
3891 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
3892 "i" := 1;
3893 LOOP
3894 "j" := 1;
3895 LOOP
3896 IF "i" != "j" THEN
3897 "matrix"["i"]["j"] := "defeat_strength"(
3898 "vote_matrix"["i"]["j"],
3899 "vote_matrix"["j"]["i"]
3900 );
3901 END IF;
3902 EXIT WHEN "j" = "dimension_v";
3903 "j" := "j" + 1;
3904 END LOOP;
3905 EXIT WHEN "i" = "dimension_v";
3906 "i" := "i" + 1;
3907 END LOOP;
3908 -- Find best paths:
3909 "i" := 1;
3910 LOOP
3911 "j" := 1;
3912 LOOP
3913 IF "i" != "j" THEN
3914 "k" := 1;
3915 LOOP
3916 IF "i" != "k" AND "j" != "k" THEN
3917 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
3918 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
3919 "matrix"["j"]["k"] := "matrix"["j"]["i"];
3920 END IF;
3921 ELSE
3922 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
3923 "matrix"["j"]["k"] := "matrix"["i"]["k"];
3924 END IF;
3925 END IF;
3926 END IF;
3927 EXIT WHEN "k" = "dimension_v";
3928 "k" := "k" + 1;
3929 END LOOP;
3930 END IF;
3931 EXIT WHEN "j" = "dimension_v";
3932 "j" := "j" + 1;
3933 END LOOP;
3934 EXIT WHEN "i" = "dimension_v";
3935 "i" := "i" + 1;
3936 END LOOP;
3937 -- Determine order of winners:
3938 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
3939 "rank_v" := 1;
3940 "done_v" := 0;
3941 LOOP
3942 "winners_ary" := '{}';
3943 "i" := 1;
3944 LOOP
3945 IF "rank_ary"["i"] ISNULL THEN
3946 "j" := 1;
3947 LOOP
3948 IF
3949 "i" != "j" AND
3950 "rank_ary"["j"] ISNULL AND
3951 "matrix"["j"]["i"] > "matrix"["i"]["j"]
3952 THEN
3953 -- someone else is better
3954 EXIT;
3955 END IF;
3956 IF "j" = "dimension_v" THEN
3957 -- noone is better
3958 "winners_ary" := "winners_ary" || "i";
3959 EXIT;
3960 END IF;
3961 "j" := "j" + 1;
3962 END LOOP;
3963 END IF;
3964 EXIT WHEN "i" = "dimension_v";
3965 "i" := "i" + 1;
3966 END LOOP;
3967 "i" := 1;
3968 LOOP
3969 "rank_ary"["winners_ary"["i"]] := "rank_v";
3970 "done_v" := "done_v" + 1;
3971 EXIT WHEN "i" = array_upper("winners_ary", 1);
3972 "i" := "i" + 1;
3973 END LOOP;
3974 EXIT WHEN "done_v" = "dimension_v";
3975 "rank_v" := "rank_v" + 1;
3976 END LOOP;
3977 -- write preliminary results:
3978 "i" := 1;
3979 FOR "initiative_id_v" IN
3980 SELECT "id" FROM "initiative"
3981 WHERE "issue_id" = "issue_id_p" AND "admitted"
3982 ORDER BY "id"
3983 LOOP
3984 UPDATE "initiative" SET
3985 "direct_majority" =
3986 CASE WHEN "policy_row"."direct_majority_strict" THEN
3987 "positive_votes" * "policy_row"."direct_majority_den" >
3988 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
3989 ELSE
3990 "positive_votes" * "policy_row"."direct_majority_den" >=
3991 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
3992 END
3993 AND "positive_votes" >= "policy_row"."direct_majority_positive"
3994 AND "issue_row"."voter_count"-"negative_votes" >=
3995 "policy_row"."direct_majority_non_negative",
3996 "indirect_majority" =
3997 CASE WHEN "policy_row"."indirect_majority_strict" THEN
3998 "positive_votes" * "policy_row"."indirect_majority_den" >
3999 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
4000 ELSE
4001 "positive_votes" * "policy_row"."indirect_majority_den" >=
4002 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
4003 END
4004 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
4005 AND "issue_row"."voter_count"-"negative_votes" >=
4006 "policy_row"."indirect_majority_non_negative",
4007 "schulze_rank" = "rank_ary"["i"],
4008 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
4009 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
4010 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
4011 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0,
4012 "eligible" = FALSE,
4013 "winner" = FALSE,
4014 "rank" = NULL -- NOTE: in cases of manual reset of issue state
4015 WHERE "id" = "initiative_id_v";
4016 "i" := "i" + 1;
4017 END LOOP;
4018 IF "i" != "dimension_v" THEN
4019 RAISE EXCEPTION 'Wrong winner count (should not happen)';
4020 END IF;
4021 -- take indirect majorities into account:
4022 LOOP
4023 UPDATE "initiative" SET "indirect_majority" = TRUE
4024 FROM (
4025 SELECT "new_initiative"."id" AS "initiative_id"
4026 FROM "initiative" "old_initiative"
4027 JOIN "initiative" "new_initiative"
4028 ON "new_initiative"."issue_id" = "issue_id_p"
4029 AND "new_initiative"."indirect_majority" = FALSE
4030 JOIN "battle" "battle_win"
4031 ON "battle_win"."issue_id" = "issue_id_p"
4032 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
4033 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
4034 JOIN "battle" "battle_lose"
4035 ON "battle_lose"."issue_id" = "issue_id_p"
4036 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
4037 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
4038 WHERE "old_initiative"."issue_id" = "issue_id_p"
4039 AND "old_initiative"."indirect_majority" = TRUE
4040 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
4041 "battle_win"."count" * "policy_row"."indirect_majority_den" >
4042 "policy_row"."indirect_majority_num" *
4043 ("battle_win"."count"+"battle_lose"."count")
4044 ELSE
4045 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
4046 "policy_row"."indirect_majority_num" *
4047 ("battle_win"."count"+"battle_lose"."count")
4048 END
4049 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
4050 AND "issue_row"."voter_count"-"battle_lose"."count" >=
4051 "policy_row"."indirect_majority_non_negative"
4052 ) AS "subquery"
4053 WHERE "id" = "subquery"."initiative_id";
4054 EXIT WHEN NOT FOUND;
4055 END LOOP;
4056 -- set "multistage_majority" for remaining matching initiatives:
4057 UPDATE "initiative" SET "multistage_majority" = TRUE
4058 FROM (
4059 SELECT "losing_initiative"."id" AS "initiative_id"
4060 FROM "initiative" "losing_initiative"
4061 JOIN "initiative" "winning_initiative"
4062 ON "winning_initiative"."issue_id" = "issue_id_p"
4063 AND "winning_initiative"."admitted"
4064 JOIN "battle" "battle_win"
4065 ON "battle_win"."issue_id" = "issue_id_p"
4066 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
4067 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
4068 JOIN "battle" "battle_lose"
4069 ON "battle_lose"."issue_id" = "issue_id_p"
4070 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
4071 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
4072 WHERE "losing_initiative"."issue_id" = "issue_id_p"
4073 AND "losing_initiative"."admitted"
4074 AND "winning_initiative"."schulze_rank" <
4075 "losing_initiative"."schulze_rank"
4076 AND "battle_win"."count" > "battle_lose"."count"
4077 AND (
4078 "battle_win"."count" > "winning_initiative"."positive_votes" OR
4079 "battle_lose"."count" < "losing_initiative"."negative_votes" )
4080 ) AS "subquery"
4081 WHERE "id" = "subquery"."initiative_id";
4082 -- mark eligible initiatives:
4083 UPDATE "initiative" SET "eligible" = TRUE
4084 WHERE "issue_id" = "issue_id_p"
4085 AND "initiative"."direct_majority"
4086 AND "initiative"."indirect_majority"
4087 AND "initiative"."better_than_status_quo"
4088 AND (
4089 "policy_row"."no_multistage_majority" = FALSE OR
4090 "initiative"."multistage_majority" = FALSE )
4091 AND (
4092 "policy_row"."no_reverse_beat_path" = FALSE OR
4093 "initiative"."reverse_beat_path" = FALSE );
4094 -- mark final winner:
4095 UPDATE "initiative" SET "winner" = TRUE
4096 FROM (
4097 SELECT "id" AS "initiative_id"
4098 FROM "initiative"
4099 WHERE "issue_id" = "issue_id_p" AND "eligible"
4100 ORDER BY
4101 "schulze_rank",
4102 "vote_ratio"("positive_votes", "negative_votes"),
4103 "id"
4104 LIMIT 1
4105 ) AS "subquery"
4106 WHERE "id" = "subquery"."initiative_id";
4107 -- write (final) ranks:
4108 "rank_v" := 1;
4109 FOR "initiative_id_v" IN
4110 SELECT "id"
4111 FROM "initiative"
4112 WHERE "issue_id" = "issue_id_p" AND "admitted"
4113 ORDER BY
4114 "winner" DESC,
4115 "eligible" DESC,
4116 "schulze_rank",
4117 "vote_ratio"("positive_votes", "negative_votes"),
4118 "id"
4119 LOOP
4120 UPDATE "initiative" SET "rank" = "rank_v"
4121 WHERE "id" = "initiative_id_v";
4122 "rank_v" := "rank_v" + 1;
4123 END LOOP;
4124 -- set schulze rank of status quo and mark issue as finished:
4125 UPDATE "issue" SET
4126 "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
4127 "state" =
4128 CASE WHEN EXISTS (
4129 SELECT NULL FROM "initiative"
4130 WHERE "issue_id" = "issue_id_p" AND "winner"
4131 ) THEN
4132 'finished_with_winner'::"issue_state"
4133 ELSE
4134 'finished_without_winner'::"issue_state"
4135 END,
4136 "ranks_available" = TRUE
4137 WHERE "id" = "issue_id_p";
4138 RETURN;
4139 END;
4140 $$;
4142 COMMENT ON FUNCTION "calculate_ranks"
4143 ( "issue"."id"%TYPE )
4144 IS 'Determine ranking (Votes have to be counted first)';
4148 -----------------------------
4149 -- Automatic state changes --
4150 -----------------------------
4153 CREATE FUNCTION "check_issue"
4154 ( "issue_id_p" "issue"."id"%TYPE )
4155 RETURNS VOID
4156 LANGUAGE 'plpgsql' VOLATILE AS $$
4157 DECLARE
4158 "issue_row" "issue"%ROWTYPE;
4159 "policy_row" "policy"%ROWTYPE;
4160 BEGIN
4161 PERFORM "lock_issue"("issue_id_p");
4162 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4163 -- only process open issues:
4164 IF "issue_row"."closed" ISNULL THEN
4165 SELECT * INTO "policy_row" FROM "policy"
4166 WHERE "id" = "issue_row"."policy_id";
4167 -- create a snapshot, unless issue is already fully frozen:
4168 IF "issue_row"."fully_frozen" ISNULL THEN
4169 PERFORM "create_snapshot"("issue_id_p");
4170 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4171 END IF;
4172 -- eventually close or accept issues, which have not been accepted:
4173 IF "issue_row"."accepted" ISNULL THEN
4174 IF EXISTS (
4175 SELECT NULL FROM "initiative"
4176 WHERE "issue_id" = "issue_id_p"
4177 AND "supporter_count" > 0
4178 AND "supporter_count" * "policy_row"."issue_quorum_den"
4179 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
4180 ) THEN
4181 -- accept issues, if supporter count is high enough
4182 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
4183 -- NOTE: "issue_row" used later
4184 "issue_row"."state" := 'discussion';
4185 "issue_row"."accepted" := now();
4186 UPDATE "issue" SET
4187 "state" = "issue_row"."state",
4188 "accepted" = "issue_row"."accepted"
4189 WHERE "id" = "issue_row"."id";
4190 ELSIF
4191 now() >= "issue_row"."created" + "issue_row"."admission_time"
4192 THEN
4193 -- close issues, if admission time has expired
4194 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
4195 UPDATE "issue" SET
4196 "state" = 'canceled_issue_not_accepted',
4197 "closed" = now()
4198 WHERE "id" = "issue_row"."id";
4199 END IF;
4200 END IF;
4201 -- eventually half freeze issues:
4202 IF
4203 -- NOTE: issue can't be closed at this point, if it has been accepted
4204 "issue_row"."accepted" NOTNULL AND
4205 "issue_row"."half_frozen" ISNULL
4206 THEN
4207 IF
4208 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
4209 THEN
4210 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
4211 -- NOTE: "issue_row" used later
4212 "issue_row"."state" := 'verification';
4213 "issue_row"."half_frozen" := now();
4214 UPDATE "issue" SET
4215 "state" = "issue_row"."state",
4216 "half_frozen" = "issue_row"."half_frozen"
4217 WHERE "id" = "issue_row"."id";
4218 END IF;
4219 END IF;
4220 -- close issues after some time, if all initiatives have been revoked:
4221 IF
4222 "issue_row"."closed" ISNULL AND
4223 NOT EXISTS (
4224 -- all initiatives are revoked
4225 SELECT NULL FROM "initiative"
4226 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
4227 ) AND (
4228 -- and issue has not been accepted yet
4229 "issue_row"."accepted" ISNULL OR
4230 NOT EXISTS (
4231 -- or no initiatives have been revoked lately
4232 SELECT NULL FROM "initiative"
4233 WHERE "issue_id" = "issue_id_p"
4234 AND now() < "revoked" + "issue_row"."verification_time"
4235 ) OR (
4236 -- or verification time has elapsed
4237 "issue_row"."half_frozen" NOTNULL AND
4238 "issue_row"."fully_frozen" ISNULL AND
4239 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
4240 )
4241 )
4242 THEN
4243 -- NOTE: "issue_row" used later
4244 IF "issue_row"."accepted" ISNULL THEN
4245 "issue_row"."state" := 'canceled_revoked_before_accepted';
4246 ELSIF "issue_row"."half_frozen" ISNULL THEN
4247 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
4248 ELSE
4249 "issue_row"."state" := 'canceled_after_revocation_during_verification';
4250 END IF;
4251 "issue_row"."closed" := now();
4252 UPDATE "issue" SET
4253 "state" = "issue_row"."state",
4254 "closed" = "issue_row"."closed"
4255 WHERE "id" = "issue_row"."id";
4256 END IF;
4257 -- fully freeze issue after verification time:
4258 IF
4259 "issue_row"."half_frozen" NOTNULL AND
4260 "issue_row"."fully_frozen" ISNULL AND
4261 "issue_row"."closed" ISNULL AND
4262 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
4263 THEN
4264 PERFORM "freeze_after_snapshot"("issue_id_p");
4265 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
4266 END IF;
4267 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
4268 -- close issue by calling close_voting(...) after voting time:
4269 IF
4270 "issue_row"."closed" ISNULL AND
4271 "issue_row"."fully_frozen" NOTNULL AND
4272 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
4273 THEN
4274 PERFORM "close_voting"("issue_id_p");
4275 -- calculate ranks will not consume much time and can be done now
4276 PERFORM "calculate_ranks"("issue_id_p");
4277 END IF;
4278 END IF;
4279 RETURN;
4280 END;
4281 $$;
4283 COMMENT ON FUNCTION "check_issue"
4284 ( "issue"."id"%TYPE )
4285 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.';
4288 CREATE FUNCTION "check_everything"()
4289 RETURNS VOID
4290 LANGUAGE 'plpgsql' VOLATILE AS $$
4291 DECLARE
4292 "issue_id_v" "issue"."id"%TYPE;
4293 BEGIN
4294 DELETE FROM "expired_session";
4295 PERFORM "check_activity"();
4296 PERFORM "calculate_member_counts"();
4297 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
4298 PERFORM "check_issue"("issue_id_v");
4299 END LOOP;
4300 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
4301 PERFORM "calculate_ranks"("issue_id_v");
4302 END LOOP;
4303 RETURN;
4304 END;
4305 $$;
4307 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.';
4311 ----------------------
4312 -- Deletion of data --
4313 ----------------------
4316 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
4317 RETURNS VOID
4318 LANGUAGE 'plpgsql' VOLATILE AS $$
4319 DECLARE
4320 "issue_row" "issue"%ROWTYPE;
4321 BEGIN
4322 SELECT * INTO "issue_row"
4323 FROM "issue" WHERE "id" = "issue_id_p"
4324 FOR UPDATE;
4325 IF "issue_row"."cleaned" ISNULL THEN
4326 UPDATE "issue" SET
4327 "state" = 'voting',
4328 "closed" = NULL,
4329 "ranks_available" = FALSE
4330 WHERE "id" = "issue_id_p";
4331 DELETE FROM "voting_comment"
4332 WHERE "issue_id" = "issue_id_p";
4333 DELETE FROM "delegating_voter"
4334 WHERE "issue_id" = "issue_id_p";
4335 DELETE FROM "direct_voter"
4336 WHERE "issue_id" = "issue_id_p";
4337 DELETE FROM "delegating_interest_snapshot"
4338 WHERE "issue_id" = "issue_id_p";
4339 DELETE FROM "direct_interest_snapshot"
4340 WHERE "issue_id" = "issue_id_p";
4341 DELETE FROM "delegating_population_snapshot"
4342 WHERE "issue_id" = "issue_id_p";
4343 DELETE FROM "direct_population_snapshot"
4344 WHERE "issue_id" = "issue_id_p";
4345 DELETE FROM "non_voter"
4346 WHERE "issue_id" = "issue_id_p";
4347 DELETE FROM "delegation"
4348 WHERE "issue_id" = "issue_id_p";
4349 DELETE FROM "supporter"
4350 WHERE "issue_id" = "issue_id_p";
4351 UPDATE "issue" SET
4352 "state" = "issue_row"."state",
4353 "closed" = "issue_row"."closed",
4354 "ranks_available" = "issue_row"."ranks_available",
4355 "cleaned" = now()
4356 WHERE "id" = "issue_id_p";
4357 END IF;
4358 RETURN;
4359 END;
4360 $$;
4362 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
4365 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
4366 RETURNS VOID
4367 LANGUAGE 'plpgsql' VOLATILE AS $$
4368 BEGIN
4369 UPDATE "member" SET
4370 "last_login" = NULL,
4371 "login" = NULL,
4372 "password" = NULL,
4373 "locked" = TRUE,
4374 "active" = FALSE,
4375 "notify_email" = NULL,
4376 "notify_email_unconfirmed" = NULL,
4377 "notify_email_secret" = NULL,
4378 "notify_email_secret_expiry" = NULL,
4379 "notify_email_lock_expiry" = NULL,
4380 "password_reset_secret" = NULL,
4381 "password_reset_secret_expiry" = NULL,
4382 "organizational_unit" = NULL,
4383 "internal_posts" = NULL,
4384 "realname" = NULL,
4385 "birthday" = NULL,
4386 "address" = NULL,
4387 "email" = NULL,
4388 "xmpp_address" = NULL,
4389 "website" = NULL,
4390 "phone" = NULL,
4391 "mobile_phone" = NULL,
4392 "profession" = NULL,
4393 "external_memberships" = NULL,
4394 "external_posts" = NULL,
4395 "statement" = NULL
4396 WHERE "id" = "member_id_p";
4397 -- "text_search_data" is updated by triggers
4398 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
4399 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
4400 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
4401 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
4402 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
4403 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
4404 DELETE FROM "session" WHERE "member_id" = "member_id_p";
4405 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
4406 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
4407 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
4408 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
4409 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
4410 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
4411 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
4412 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
4413 DELETE FROM "direct_voter" USING "issue"
4414 WHERE "direct_voter"."issue_id" = "issue"."id"
4415 AND "issue"."closed" ISNULL
4416 AND "member_id" = "member_id_p";
4417 RETURN;
4418 END;
4419 $$;
4421 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)';
4424 CREATE FUNCTION "delete_private_data"()
4425 RETURNS VOID
4426 LANGUAGE 'plpgsql' VOLATILE AS $$
4427 BEGIN
4428 DELETE FROM "member" WHERE "activated" ISNULL;
4429 UPDATE "member" SET
4430 "invite_code" = NULL,
4431 "invite_code_expiry" = NULL,
4432 "admin_comment" = NULL,
4433 "last_login" = NULL,
4434 "login" = NULL,
4435 "password" = NULL,
4436 "lang" = NULL,
4437 "notify_email" = NULL,
4438 "notify_email_unconfirmed" = NULL,
4439 "notify_email_secret" = NULL,
4440 "notify_email_secret_expiry" = NULL,
4441 "notify_email_lock_expiry" = NULL,
4442 "notify_level" = NULL,
4443 "password_reset_secret" = NULL,
4444 "password_reset_secret_expiry" = NULL,
4445 "organizational_unit" = NULL,
4446 "internal_posts" = NULL,
4447 "realname" = NULL,
4448 "birthday" = NULL,
4449 "address" = NULL,
4450 "email" = NULL,
4451 "xmpp_address" = NULL,
4452 "website" = NULL,
4453 "phone" = NULL,
4454 "mobile_phone" = NULL,
4455 "profession" = NULL,
4456 "external_memberships" = NULL,
4457 "external_posts" = NULL,
4458 "formatting_engine" = NULL,
4459 "statement" = NULL;
4460 -- "text_search_data" is updated by triggers
4461 DELETE FROM "setting";
4462 DELETE FROM "setting_map";
4463 DELETE FROM "member_relation_setting";
4464 DELETE FROM "member_image";
4465 DELETE FROM "contact";
4466 DELETE FROM "ignored_member";
4467 DELETE FROM "session";
4468 DELETE FROM "area_setting";
4469 DELETE FROM "issue_setting";
4470 DELETE FROM "ignored_initiative";
4471 DELETE FROM "initiative_setting";
4472 DELETE FROM "suggestion_setting";
4473 DELETE FROM "non_voter";
4474 DELETE FROM "direct_voter" USING "issue"
4475 WHERE "direct_voter"."issue_id" = "issue"."id"
4476 AND "issue"."closed" ISNULL;
4477 RETURN;
4478 END;
4479 $$;
4481 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. See source code to see which data is deleted. If you need a different behaviour, copy this function and modify lf_export accordingly, to avoid data-leaks after updating.';
4485 COMMIT;