liquid_feedback_core

annotate core.sql @ 436:34cc98defa8b

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

Impressum / About Us