liquid_feedback_core

annotate core.sql @ 454:63af9100628c

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

Impressum / About Us