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