liquid_feedback_core

annotate core.sql @ 427:97f85b51e3d9

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

Impressum / About Us