liquid_feedback_core

annotate core.sql @ 423:73c2ab2d068f

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

Impressum / About Us