liquid_feedback_core

annotate core.sql @ 441:f5c78b0590c6

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

Impressum / About Us