liquid_feedback_core

annotate core.sql @ 460:6d4e51332251

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

Impressum / About Us