liquid_feedback_core

annotate core.sql @ 448:8730552eee9d

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

Impressum / About Us