liquid_feedback_core

annotate core.sql @ 472:0fa0d2daa54a

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

Impressum / About Us