liquid_feedback_core

annotate core.sql @ 413:e024c50cfe3d

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

Impressum / About Us