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