liquid_feedback_core

annotate core.sql @ 400:782eb17c7ad9

find changesets by author, revision, files, or words in the commit message
Remove referential integrity from "issue_order" table to avoid performance/locking issues
author jbe
date Sat Oct 12 15:33:35 2013 +0200 (2013-10-12)
parents 806561cce3b1
children f7b4457cf1a6
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@396 10 SELECT * FROM (VALUES ('2.2.6', 2, 2, 6))
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@398 609 CREATE TABLE "issue_order" (
jbe@400 610 "id" INT8 PRIMARY KEY, --REFERENCES "issue" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@398 611 "order_in_admission_state" INT4,
jbe@398 612 "order_in_open_states" INT4 );
jbe@398 613
jbe@398 614 COMMENT ON TABLE "issue_order" IS 'Ordering information for issues that are not stored in the "issue" table to avoid locking of multiple issues at once';
jbe@398 615
jbe@400 616 COMMENT ON COLUMN "issue_order"."id" IS 'References "issue" ("id") but has no referential integrity trigger associated, due to performance/locking issues';
jbe@398 617 COMMENT ON COLUMN "issue_order"."order_in_admission_state" IS 'To be used for sorting issues within an area, when showing only issues in admission state; NULL values sort last; updated by "lf_update_issue_order"';
jbe@398 618 COMMENT ON COLUMN "issue_order"."order_in_open_states" IS 'To be used for sorting issues within an area, when showing all open issues; NULL values sort last; updated by "lf_update_issue_order"';
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@210 710 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking without tie-breaking';
jbe@210 711 COMMENT ON COLUMN "initiative"."better_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking better than the status quo (without tie-breaking)';
jbe@210 712 COMMENT ON COLUMN "initiative"."worse_than_status_quo" IS 'TRUE, if initiative has a schulze-ranking worse than the status quo (without tie-breaking)';
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@210 716 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"';
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@392 2078 "issue"."area_id",
jbe@392 2079 "issue"."id" AS "issue_id",
jbe@392 2080 "supporter"."member_id",
jbe@392 2081 "direct_interest_snapshot"."weight"
jbe@392 2082 FROM "issue"
jbe@392 2083 JOIN "supporter" ON "supporter"."issue_id" = "issue"."id"
jbe@392 2084 JOIN "direct_interest_snapshot"
jbe@392 2085 ON "direct_interest_snapshot"."issue_id" = "issue"."id"
jbe@392 2086 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
jbe@392 2087 AND "direct_interest_snapshot"."member_id" = "supporter"."member_id"
jbe@392 2088 WHERE "issue"."state" = 'admission'::"issue_state";
jbe@392 2089
jbe@392 2090 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 2091
jbe@392 2092
jbe@397 2093 CREATE VIEW "open_issues_ordered_with_minimum_position" AS
jbe@397 2094 SELECT
jbe@397 2095 "area_id",
jbe@397 2096 "id" AS "issue_id",
jbe@397 2097 "order_in_admission_state" * 2 - 1 AS "minimum_position"
jbe@398 2098 FROM "issue" NATURAL LEFT JOIN "issue_order"
jbe@397 2099 WHERE "closed" ISNULL
jbe@397 2100 ORDER BY
jbe@397 2101 coalesce(
jbe@397 2102 "fully_frozen" + "voting_time",
jbe@397 2103 "half_frozen" + "verification_time",
jbe@397 2104 "accepted" + "discussion_time",
jbe@397 2105 "created" + "admission_time"
jbe@397 2106 ) - now();
jbe@397 2107
jbe@397 2108 COMMENT ON VIEW "open_issues_ordered_with_minimum_position" IS 'Helper view for "lf_update_issue_order" to allow a (mixed) ordering of issues within an area';
jbe@397 2109
jbe@397 2110
jbe@352 2111 CREATE VIEW "initiative_suggestion_order_calculation" AS
jbe@352 2112 SELECT
jbe@352 2113 "initiative"."id" AS "initiative_id",
jbe@352 2114 ("issue"."closed" NOTNULL OR "issue"."fully_frozen" NOTNULL) AS "final"
jbe@352 2115 FROM "initiative" JOIN "issue"
jbe@352 2116 ON "initiative"."issue_id" = "issue"."id"
jbe@352 2117 WHERE ("issue"."closed" ISNULL AND "issue"."fully_frozen" ISNULL)
jbe@352 2118 OR ("initiative"."final_suggestion_order_calculated" = FALSE);
jbe@352 2119
jbe@352 2120 COMMENT ON VIEW "initiative_suggestion_order_calculation" IS 'Initiatives, where the "proportional_order" of its suggestions has to be calculated';
jbe@352 2121
jbe@360 2122 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 2123
jbe@352 2124
jbe@352 2125 CREATE VIEW "individual_suggestion_ranking" AS
jbe@352 2126 SELECT
jbe@352 2127 "opinion"."initiative_id",
jbe@352 2128 "opinion"."member_id",
jbe@352 2129 "direct_interest_snapshot"."weight",
jbe@352 2130 CASE WHEN
jbe@352 2131 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = FALSE) OR
jbe@352 2132 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = TRUE)
jbe@352 2133 THEN 1 ELSE
jbe@352 2134 CASE WHEN
jbe@352 2135 ("opinion"."degree" = 1 AND "opinion"."fulfilled" = FALSE) OR
jbe@352 2136 ("opinion"."degree" = -1 AND "opinion"."fulfilled" = TRUE)
jbe@352 2137 THEN 2 ELSE
jbe@352 2138 CASE WHEN
jbe@352 2139 ("opinion"."degree" = 2 AND "opinion"."fulfilled" = TRUE) OR
jbe@352 2140 ("opinion"."degree" = -2 AND "opinion"."fulfilled" = FALSE)
jbe@352 2141 THEN 3 ELSE 4 END
jbe@352 2142 END
jbe@352 2143 END AS "preference",
jbe@352 2144 "opinion"."suggestion_id"
jbe@352 2145 FROM "opinion"
jbe@352 2146 JOIN "initiative" ON "initiative"."id" = "opinion"."initiative_id"
jbe@352 2147 JOIN "issue" ON "issue"."id" = "initiative"."issue_id"
jbe@352 2148 JOIN "direct_interest_snapshot"
jbe@352 2149 ON "direct_interest_snapshot"."issue_id" = "issue"."id"
jbe@352 2150 AND "direct_interest_snapshot"."event" = "issue"."latest_snapshot_event"
jbe@352 2151 AND "direct_interest_snapshot"."member_id" = "opinion"."member_id";
jbe@352 2152
jbe@352 2153 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 2154
jbe@352 2155
jbe@126 2156 CREATE VIEW "battle_participant" AS
jbe@126 2157 SELECT "initiative"."id", "initiative"."issue_id"
jbe@126 2158 FROM "issue" JOIN "initiative"
jbe@126 2159 ON "issue"."id" = "initiative"."issue_id"
jbe@126 2160 WHERE "initiative"."admitted"
jbe@126 2161 UNION ALL
jbe@126 2162 SELECT NULL, "id" AS "issue_id"
jbe@126 2163 FROM "issue";
jbe@126 2164
jbe@126 2165 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 2166
jbe@126 2167
jbe@61 2168 CREATE VIEW "battle_view" AS
jbe@0 2169 SELECT
jbe@0 2170 "issue"."id" AS "issue_id",
jbe@10 2171 "winning_initiative"."id" AS "winning_initiative_id",
jbe@10 2172 "losing_initiative"."id" AS "losing_initiative_id",
jbe@0 2173 sum(
jbe@0 2174 CASE WHEN
jbe@0 2175 coalesce("better_vote"."grade", 0) >
jbe@0 2176 coalesce("worse_vote"."grade", 0)
jbe@0 2177 THEN "direct_voter"."weight" ELSE 0 END
jbe@0 2178 ) AS "count"
jbe@0 2179 FROM "issue"
jbe@0 2180 LEFT JOIN "direct_voter"
jbe@0 2181 ON "issue"."id" = "direct_voter"."issue_id"
jbe@126 2182 JOIN "battle_participant" AS "winning_initiative"
jbe@10 2183 ON "issue"."id" = "winning_initiative"."issue_id"
jbe@126 2184 JOIN "battle_participant" AS "losing_initiative"
jbe@10 2185 ON "issue"."id" = "losing_initiative"."issue_id"
jbe@0 2186 LEFT JOIN "vote" AS "better_vote"
jbe@10 2187 ON "direct_voter"."member_id" = "better_vote"."member_id"
jbe@10 2188 AND "winning_initiative"."id" = "better_vote"."initiative_id"
jbe@0 2189 LEFT JOIN "vote" AS "worse_vote"
jbe@10 2190 ON "direct_voter"."member_id" = "worse_vote"."member_id"
jbe@10 2191 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
jbe@328 2192 WHERE "issue"."state" = 'voting'
jbe@328 2193 AND "issue"."phase_finished" NOTNULL
jbe@126 2194 AND (
jbe@126 2195 "winning_initiative"."id" != "losing_initiative"."id" OR
jbe@126 2196 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
jbe@126 2197 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
jbe@0 2198 GROUP BY
jbe@0 2199 "issue"."id",
jbe@10 2200 "winning_initiative"."id",
jbe@10 2201 "losing_initiative"."id";
jbe@0 2202
jbe@126 2203 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 2204
jbe@1 2205
jbe@235 2206 CREATE VIEW "expired_session" AS
jbe@235 2207 SELECT * FROM "session" WHERE now() > "expiry";
jbe@235 2208
jbe@235 2209 CREATE RULE "delete" AS ON DELETE TO "expired_session" DO INSTEAD
jbe@235 2210 DELETE FROM "session" WHERE "ident" = OLD."ident";
jbe@235 2211
jbe@235 2212 COMMENT ON VIEW "expired_session" IS 'View containing all expired sessions where DELETE is possible';
jbe@235 2213 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 2214
jbe@235 2215
jbe@0 2216 CREATE VIEW "open_issue" AS
jbe@0 2217 SELECT * FROM "issue" WHERE "closed" ISNULL;
jbe@0 2218
jbe@0 2219 COMMENT ON VIEW "open_issue" IS 'All open issues';
jbe@0 2220
jbe@0 2221
jbe@9 2222 CREATE VIEW "member_contingent" AS
jbe@9 2223 SELECT
jbe@9 2224 "member"."id" AS "member_id",
jbe@293 2225 "contingent"."polling",
jbe@9 2226 "contingent"."time_frame",
jbe@9 2227 CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN
jbe@9 2228 (
jbe@9 2229 SELECT count(1) FROM "draft"
jbe@293 2230 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
jbe@9 2231 WHERE "draft"."author_id" = "member"."id"
jbe@293 2232 AND "initiative"."polling" = "contingent"."polling"
jbe@9 2233 AND "draft"."created" > now() - "contingent"."time_frame"
jbe@9 2234 ) + (
jbe@9 2235 SELECT count(1) FROM "suggestion"
jbe@293 2236 JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id"
jbe@9 2237 WHERE "suggestion"."author_id" = "member"."id"
jbe@293 2238 AND "contingent"."polling" = FALSE
jbe@9 2239 AND "suggestion"."created" > now() - "contingent"."time_frame"
jbe@9 2240 )
jbe@9 2241 ELSE NULL END AS "text_entry_count",
jbe@9 2242 "contingent"."text_entry_limit",
jbe@9 2243 CASE WHEN "contingent"."initiative_limit" NOTNULL THEN (
jbe@293 2244 SELECT count(1) FROM "opening_draft" AS "draft"
jbe@293 2245 JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id"
jbe@293 2246 WHERE "draft"."author_id" = "member"."id"
jbe@293 2247 AND "initiative"."polling" = "contingent"."polling"
jbe@293 2248 AND "draft"."created" > now() - "contingent"."time_frame"
jbe@9 2249 ) ELSE NULL END AS "initiative_count",
jbe@9 2250 "contingent"."initiative_limit"
jbe@9 2251 FROM "member" CROSS JOIN "contingent";
jbe@9 2252
jbe@9 2253 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 2254
jbe@9 2255 COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row';
jbe@9 2256 COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row';
jbe@9 2257
jbe@9 2258
jbe@9 2259 CREATE VIEW "member_contingent_left" AS
jbe@9 2260 SELECT
jbe@9 2261 "member_id",
jbe@293 2262 "polling",
jbe@9 2263 max("text_entry_limit" - "text_entry_count") AS "text_entries_left",
jbe@9 2264 max("initiative_limit" - "initiative_count") AS "initiatives_left"
jbe@293 2265 FROM "member_contingent" GROUP BY "member_id", "polling";
jbe@9 2266
jbe@9 2267 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 2268
jbe@9 2269
jbe@113 2270 CREATE VIEW "event_seen_by_member" AS
jbe@113 2271 SELECT
jbe@113 2272 "member"."id" AS "seen_by_member_id",
jbe@113 2273 CASE WHEN "event"."state" IN (
jbe@113 2274 'voting',
jbe@113 2275 'finished_without_winner',
jbe@113 2276 'finished_with_winner'
jbe@113 2277 ) THEN
jbe@113 2278 'voting'::"notify_level"
jbe@113 2279 ELSE
jbe@113 2280 CASE WHEN "event"."state" IN (
jbe@113 2281 'verification',
jbe@113 2282 'canceled_after_revocation_during_verification',
jbe@113 2283 'canceled_no_initiative_admitted'
jbe@113 2284 ) THEN
jbe@113 2285 'verification'::"notify_level"
jbe@113 2286 ELSE
jbe@113 2287 CASE WHEN "event"."state" IN (
jbe@113 2288 'discussion',
jbe@113 2289 'canceled_after_revocation_during_discussion'
jbe@113 2290 ) THEN
jbe@113 2291 'discussion'::"notify_level"
jbe@113 2292 ELSE
jbe@113 2293 'all'::"notify_level"
jbe@113 2294 END
jbe@113 2295 END
jbe@113 2296 END AS "notify_level",
jbe@113 2297 "event".*
jbe@113 2298 FROM "member" CROSS JOIN "event"
jbe@113 2299 LEFT JOIN "issue"
jbe@113 2300 ON "event"."issue_id" = "issue"."id"
jbe@113 2301 LEFT JOIN "membership"
jbe@113 2302 ON "member"."id" = "membership"."member_id"
jbe@113 2303 AND "issue"."area_id" = "membership"."area_id"
jbe@113 2304 LEFT JOIN "interest"
jbe@113 2305 ON "member"."id" = "interest"."member_id"
jbe@113 2306 AND "event"."issue_id" = "interest"."issue_id"
jbe@113 2307 LEFT JOIN "ignored_member"
jbe@113 2308 ON "member"."id" = "ignored_member"."member_id"
jbe@113 2309 AND "event"."member_id" = "ignored_member"."other_member_id"
jbe@113 2310 LEFT JOIN "ignored_initiative"
jbe@113 2311 ON "member"."id" = "ignored_initiative"."member_id"
jbe@113 2312 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
jbe@113 2313 WHERE (
jbe@113 2314 "interest"."member_id" NOTNULL OR
jbe@113 2315 ( "membership"."member_id" NOTNULL AND
jbe@113 2316 "event"."event" IN (
jbe@113 2317 'issue_state_changed',
jbe@113 2318 'initiative_created_in_new_issue',
jbe@113 2319 'initiative_created_in_existing_issue',
jbe@113 2320 'initiative_revoked' ) ) )
jbe@113 2321 AND "ignored_member"."member_id" ISNULL
jbe@113 2322 AND "ignored_initiative"."member_id" ISNULL;
jbe@113 2323
jbe@222 2324 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 2325
jbe@222 2326
jbe@222 2327 CREATE VIEW "selected_event_seen_by_member" AS
jbe@113 2328 SELECT
jbe@113 2329 "member"."id" AS "seen_by_member_id",
jbe@222 2330 CASE WHEN "event"."state" IN (
jbe@222 2331 'voting',
jbe@222 2332 'finished_without_winner',
jbe@222 2333 'finished_with_winner'
jbe@222 2334 ) THEN
jbe@222 2335 'voting'::"notify_level"
jbe@222 2336 ELSE
jbe@222 2337 CASE WHEN "event"."state" IN (
jbe@222 2338 'verification',
jbe@222 2339 'canceled_after_revocation_during_verification',
jbe@222 2340 'canceled_no_initiative_admitted'
jbe@222 2341 ) THEN
jbe@222 2342 'verification'::"notify_level"
jbe@222 2343 ELSE
jbe@222 2344 CASE WHEN "event"."state" IN (
jbe@222 2345 'discussion',
jbe@222 2346 'canceled_after_revocation_during_discussion'
jbe@222 2347 ) THEN
jbe@222 2348 'discussion'::"notify_level"
jbe@222 2349 ELSE
jbe@222 2350 'all'::"notify_level"
jbe@222 2351 END
jbe@222 2352 END
jbe@222 2353 END AS "notify_level",
jbe@113 2354 "event".*
jbe@113 2355 FROM "member" CROSS JOIN "event"
jbe@113 2356 LEFT JOIN "issue"
jbe@113 2357 ON "event"."issue_id" = "issue"."id"
jbe@113 2358 LEFT JOIN "membership"
jbe@113 2359 ON "member"."id" = "membership"."member_id"
jbe@113 2360 AND "issue"."area_id" = "membership"."area_id"
jbe@113 2361 LEFT JOIN "interest"
jbe@113 2362 ON "member"."id" = "interest"."member_id"
jbe@113 2363 AND "event"."issue_id" = "interest"."issue_id"
jbe@113 2364 LEFT JOIN "ignored_member"
jbe@113 2365 ON "member"."id" = "ignored_member"."member_id"
jbe@113 2366 AND "event"."member_id" = "ignored_member"."other_member_id"
jbe@113 2367 LEFT JOIN "ignored_initiative"
jbe@113 2368 ON "member"."id" = "ignored_initiative"."member_id"
jbe@113 2369 AND "event"."initiative_id" = "ignored_initiative"."initiative_id"
jbe@113 2370 WHERE (
jbe@113 2371 ( "member"."notify_level" >= 'all' ) OR
jbe@113 2372 ( "member"."notify_level" >= 'voting' AND
jbe@113 2373 "event"."state" IN (
jbe@113 2374 'voting',
jbe@113 2375 'finished_without_winner',
jbe@113 2376 'finished_with_winner' ) ) OR
jbe@113 2377 ( "member"."notify_level" >= 'verification' AND
jbe@113 2378 "event"."state" IN (
jbe@113 2379 'verification',
jbe@113 2380 'canceled_after_revocation_during_verification',
jbe@113 2381 'canceled_no_initiative_admitted' ) ) OR
jbe@113 2382 ( "member"."notify_level" >= 'discussion' AND
jbe@113 2383 "event"."state" IN (
jbe@113 2384 'discussion',
jbe@113 2385 'canceled_after_revocation_during_discussion' ) ) )
jbe@113 2386 AND (
jbe@113 2387 "interest"."member_id" NOTNULL OR
jbe@113 2388 ( "membership"."member_id" NOTNULL AND
jbe@113 2389 "event"."event" IN (
jbe@113 2390 'issue_state_changed',
jbe@113 2391 'initiative_created_in_new_issue',
jbe@113 2392 'initiative_created_in_existing_issue',
jbe@113 2393 'initiative_revoked' ) ) )
jbe@113 2394 AND "ignored_member"."member_id" ISNULL
jbe@113 2395 AND "ignored_initiative"."member_id" ISNULL;
jbe@113 2396
jbe@222 2397 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 2398
jbe@113 2399
jbe@0 2400
jbe@242 2401 ------------------------------------------------------
jbe@242 2402 -- Row set returning function for delegation chains --
jbe@242 2403 ------------------------------------------------------
jbe@5 2404
jbe@5 2405
jbe@5 2406 CREATE TYPE "delegation_chain_loop_tag" AS ENUM
jbe@5 2407 ('first', 'intermediate', 'last', 'repetition');
jbe@5 2408
jbe@5 2409 COMMENT ON TYPE "delegation_chain_loop_tag" IS 'Type for loop tags in "delegation_chain_row" type';
jbe@5 2410
jbe@5 2411
jbe@5 2412 CREATE TYPE "delegation_chain_row" AS (
jbe@5 2413 "index" INT4,
jbe@5 2414 "member_id" INT4,
jbe@97 2415 "member_valid" BOOLEAN,
jbe@5 2416 "participation" BOOLEAN,
jbe@5 2417 "overridden" BOOLEAN,
jbe@5 2418 "scope_in" "delegation_scope",
jbe@5 2419 "scope_out" "delegation_scope",
jbe@86 2420 "disabled_out" BOOLEAN,
jbe@5 2421 "loop" "delegation_chain_loop_tag" );
jbe@5 2422
jbe@243 2423 COMMENT ON TYPE "delegation_chain_row" IS 'Type of rows returned by "delegation_chain" function';
jbe@5 2424
jbe@5 2425 COMMENT ON COLUMN "delegation_chain_row"."index" IS 'Index starting with 0 and counting up';
jbe@5 2426 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 2427 COMMENT ON COLUMN "delegation_chain_row"."overridden" IS 'True, if an entry with lower index has "participation" set to true';
jbe@5 2428 COMMENT ON COLUMN "delegation_chain_row"."scope_in" IS 'Scope of used incoming delegation';
jbe@5 2429 COMMENT ON COLUMN "delegation_chain_row"."scope_out" IS 'Scope of used outgoing delegation';
jbe@86 2430 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 2431 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 2432
jbe@5 2433
jbe@242 2434 CREATE FUNCTION "delegation_chain_for_closed_issue"
jbe@242 2435 ( "member_id_p" "member"."id"%TYPE,
jbe@242 2436 "issue_id_p" "issue"."id"%TYPE )
jbe@242 2437 RETURNS SETOF "delegation_chain_row"
jbe@242 2438 LANGUAGE 'plpgsql' STABLE AS $$
jbe@242 2439 DECLARE
jbe@242 2440 "output_row" "delegation_chain_row";
jbe@242 2441 "direct_voter_row" "direct_voter"%ROWTYPE;
jbe@242 2442 "delegating_voter_row" "delegating_voter"%ROWTYPE;
jbe@242 2443 BEGIN
jbe@242 2444 "output_row"."index" := 0;
jbe@242 2445 "output_row"."member_id" := "member_id_p";
jbe@242 2446 "output_row"."member_valid" := TRUE;
jbe@242 2447 "output_row"."participation" := FALSE;
jbe@242 2448 "output_row"."overridden" := FALSE;
jbe@242 2449 "output_row"."disabled_out" := FALSE;
jbe@242 2450 LOOP
jbe@242 2451 SELECT INTO "direct_voter_row" * FROM "direct_voter"
jbe@242 2452 WHERE "issue_id" = "issue_id_p"
jbe@242 2453 AND "member_id" = "output_row"."member_id";
jbe@242 2454 IF "direct_voter_row"."member_id" NOTNULL THEN
jbe@242 2455 "output_row"."participation" := TRUE;
jbe@242 2456 "output_row"."scope_out" := NULL;
jbe@242 2457 "output_row"."disabled_out" := NULL;
jbe@242 2458 RETURN NEXT "output_row";
jbe@242 2459 RETURN;
jbe@242 2460 END IF;
jbe@242 2461 SELECT INTO "delegating_voter_row" * FROM "delegating_voter"
jbe@242 2462 WHERE "issue_id" = "issue_id_p"
jbe@242 2463 AND "member_id" = "output_row"."member_id";
jbe@242 2464 IF "delegating_voter_row"."member_id" ISNULL THEN
jbe@242 2465 RETURN;
jbe@242 2466 END IF;
jbe@242 2467 "output_row"."scope_out" := "delegating_voter_row"."scope";
jbe@242 2468 RETURN NEXT "output_row";
jbe@242 2469 "output_row"."member_id" := "delegating_voter_row"."delegate_member_ids"[1];
jbe@242 2470 "output_row"."scope_in" := "output_row"."scope_out";
jbe@242 2471 END LOOP;
jbe@242 2472 END;
jbe@242 2473 $$;
jbe@242 2474
jbe@242 2475 COMMENT ON FUNCTION "delegation_chain_for_closed_issue"
jbe@242 2476 ( "member"."id"%TYPE,
jbe@242 2477 "member"."id"%TYPE )
jbe@242 2478 IS 'Helper function for "delegation_chain" function, handling the special case of closed issues after voting';
jbe@242 2479
jbe@242 2480
jbe@5 2481 CREATE FUNCTION "delegation_chain"
jbe@5 2482 ( "member_id_p" "member"."id"%TYPE,
jbe@97 2483 "unit_id_p" "unit"."id"%TYPE,
jbe@5 2484 "area_id_p" "area"."id"%TYPE,
jbe@5 2485 "issue_id_p" "issue"."id"%TYPE,
jbe@255 2486 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
jbe@255 2487 "simulate_default_p" BOOLEAN DEFAULT FALSE )
jbe@5 2488 RETURNS SETOF "delegation_chain_row"
jbe@5 2489 LANGUAGE 'plpgsql' STABLE AS $$
jbe@5 2490 DECLARE
jbe@97 2491 "scope_v" "delegation_scope";
jbe@97 2492 "unit_id_v" "unit"."id"%TYPE;
jbe@97 2493 "area_id_v" "area"."id"%TYPE;
jbe@241 2494 "issue_row" "issue"%ROWTYPE;
jbe@5 2495 "visited_member_ids" INT4[]; -- "member"."id"%TYPE[]
jbe@5 2496 "loop_member_id_v" "member"."id"%TYPE;
jbe@5 2497 "output_row" "delegation_chain_row";
jbe@5 2498 "output_rows" "delegation_chain_row"[];
jbe@255 2499 "simulate_v" BOOLEAN;
jbe@255 2500 "simulate_here_v" BOOLEAN;
jbe@5 2501 "delegation_row" "delegation"%ROWTYPE;
jbe@5 2502 "row_count" INT4;
jbe@5 2503 "i" INT4;
jbe@5 2504 "loop_v" BOOLEAN;
jbe@5 2505 BEGIN
jbe@255 2506 IF "simulate_trustee_id_p" NOTNULL AND "simulate_default_p" THEN
jbe@255 2507 RAISE EXCEPTION 'Both "simulate_trustee_id_p" is set, and "simulate_default_p" is true';
jbe@255 2508 END IF;
jbe@255 2509 IF "simulate_trustee_id_p" NOTNULL OR "simulate_default_p" THEN
jbe@255 2510 "simulate_v" := TRUE;
jbe@255 2511 ELSE
jbe@255 2512 "simulate_v" := FALSE;
jbe@255 2513 END IF;
jbe@97 2514 IF
jbe@97 2515 "unit_id_p" NOTNULL AND
jbe@97 2516 "area_id_p" ISNULL AND
jbe@97 2517 "issue_id_p" ISNULL
jbe@97 2518 THEN
jbe@97 2519 "scope_v" := 'unit';
jbe@97 2520 "unit_id_v" := "unit_id_p";
jbe@97 2521 ELSIF
jbe@97 2522 "unit_id_p" ISNULL AND
jbe@97 2523 "area_id_p" NOTNULL AND
jbe@97 2524 "issue_id_p" ISNULL
jbe@97 2525 THEN
jbe@97 2526 "scope_v" := 'area';
jbe@97 2527 "area_id_v" := "area_id_p";
jbe@97 2528 SELECT "unit_id" INTO "unit_id_v"
jbe@97 2529 FROM "area" WHERE "id" = "area_id_v";
jbe@97 2530 ELSIF
jbe@97 2531 "unit_id_p" ISNULL AND
jbe@97 2532 "area_id_p" ISNULL AND
jbe@97 2533 "issue_id_p" NOTNULL
jbe@97 2534 THEN
jbe@242 2535 SELECT INTO "issue_row" * FROM "issue" WHERE "id" = "issue_id_p";
jbe@242 2536 IF "issue_row"."id" ISNULL THEN
jbe@242 2537 RETURN;
jbe@242 2538 END IF;
jbe@242 2539 IF "issue_row"."closed" NOTNULL THEN
jbe@255 2540 IF "simulate_v" THEN
jbe@242 2541 RAISE EXCEPTION 'Tried to simulate delegation chain for closed issue.';
jbe@242 2542 END IF;
jbe@242 2543 FOR "output_row" IN
jbe@242 2544 SELECT * FROM
jbe@242 2545 "delegation_chain_for_closed_issue"("member_id_p", "issue_id_p")
jbe@242 2546 LOOP
jbe@242 2547 RETURN NEXT "output_row";
jbe@242 2548 END LOOP;
jbe@242 2549 RETURN;
jbe@242 2550 END IF;
jbe@97 2551 "scope_v" := 'issue';
jbe@97 2552 SELECT "area_id" INTO "area_id_v"
jbe@97 2553 FROM "issue" WHERE "id" = "issue_id_p";
jbe@97 2554 SELECT "unit_id" INTO "unit_id_v"
jbe@97 2555 FROM "area" WHERE "id" = "area_id_v";
jbe@97 2556 ELSE
jbe@97 2557 RAISE EXCEPTION 'Exactly one of unit_id_p, area_id_p, or issue_id_p must be NOTNULL.';
jbe@97 2558 END IF;
jbe@5 2559 "visited_member_ids" := '{}';
jbe@5 2560 "loop_member_id_v" := NULL;
jbe@5 2561 "output_rows" := '{}';
jbe@5 2562 "output_row"."index" := 0;
jbe@5 2563 "output_row"."member_id" := "member_id_p";
jbe@97 2564 "output_row"."member_valid" := TRUE;
jbe@5 2565 "output_row"."participation" := FALSE;
jbe@5 2566 "output_row"."overridden" := FALSE;
jbe@86 2567 "output_row"."disabled_out" := FALSE;
jbe@5 2568 "output_row"."scope_out" := NULL;
jbe@5 2569 LOOP
jbe@5 2570 IF "visited_member_ids" @> ARRAY["output_row"."member_id"] THEN
jbe@5 2571 "loop_member_id_v" := "output_row"."member_id";
jbe@5 2572 ELSE
jbe@5 2573 "visited_member_ids" :=
jbe@5 2574 "visited_member_ids" || "output_row"."member_id";
jbe@5 2575 END IF;
jbe@241 2576 IF "output_row"."participation" ISNULL THEN
jbe@241 2577 "output_row"."overridden" := NULL;
jbe@241 2578 ELSIF "output_row"."participation" THEN
jbe@5 2579 "output_row"."overridden" := TRUE;
jbe@5 2580 END IF;
jbe@5 2581 "output_row"."scope_in" := "output_row"."scope_out";
jbe@255 2582 "output_row"."member_valid" := EXISTS (
jbe@97 2583 SELECT NULL FROM "member" JOIN "privilege"
jbe@97 2584 ON "privilege"."member_id" = "member"."id"
jbe@97 2585 AND "privilege"."unit_id" = "unit_id_v"
jbe@97 2586 WHERE "id" = "output_row"."member_id"
jbe@97 2587 AND "member"."active" AND "privilege"."voting_right"
jbe@255 2588 );
jbe@255 2589 "simulate_here_v" := (
jbe@255 2590 "simulate_v" AND
jbe@255 2591 "output_row"."member_id" = "member_id_p"
jbe@255 2592 );
jbe@255 2593 "delegation_row" := ROW(NULL);
jbe@255 2594 IF "output_row"."member_valid" OR "simulate_here_v" THEN
jbe@97 2595 IF "scope_v" = 'unit' THEN
jbe@255 2596 IF NOT "simulate_here_v" THEN
jbe@255 2597 SELECT * INTO "delegation_row" FROM "delegation"
jbe@255 2598 WHERE "truster_id" = "output_row"."member_id"
jbe@255 2599 AND "unit_id" = "unit_id_v";
jbe@255 2600 END IF;
jbe@97 2601 ELSIF "scope_v" = 'area' THEN
jbe@5 2602 "output_row"."participation" := EXISTS (
jbe@5 2603 SELECT NULL FROM "membership"
jbe@5 2604 WHERE "area_id" = "area_id_p"
jbe@5 2605 AND "member_id" = "output_row"."member_id"
jbe@5 2606 );
jbe@255 2607 IF "simulate_here_v" THEN
jbe@255 2608 IF "simulate_trustee_id_p" ISNULL THEN
jbe@255 2609 SELECT * INTO "delegation_row" FROM "delegation"
jbe@255 2610 WHERE "truster_id" = "output_row"."member_id"
jbe@255 2611 AND "unit_id" = "unit_id_v";
jbe@255 2612 END IF;
jbe@255 2613 ELSE
jbe@255 2614 SELECT * INTO "delegation_row" FROM "delegation"
jbe@255 2615 WHERE "truster_id" = "output_row"."member_id"
jbe@255 2616 AND (
jbe@255 2617 "unit_id" = "unit_id_v" OR
jbe@255 2618 "area_id" = "area_id_v"
jbe@255 2619 )
jbe@255 2620 ORDER BY "scope" DESC;
jbe@255 2621 END IF;
jbe@97 2622 ELSIF "scope_v" = 'issue' THEN
jbe@241 2623 IF "issue_row"."fully_frozen" ISNULL THEN
jbe@241 2624 "output_row"."participation" := EXISTS (
jbe@241 2625 SELECT NULL FROM "interest"
jbe@241 2626 WHERE "issue_id" = "issue_id_p"
jbe@241 2627 AND "member_id" = "output_row"."member_id"
jbe@241 2628 );
jbe@241 2629 ELSE
jbe@241 2630 IF "output_row"."member_id" = "member_id_p" THEN
jbe@241 2631 "output_row"."participation" := EXISTS (
jbe@241 2632 SELECT NULL FROM "direct_voter"
jbe@241 2633 WHERE "issue_id" = "issue_id_p"
jbe@241 2634 AND "member_id" = "output_row"."member_id"
jbe@241 2635 );
jbe@241 2636 ELSE
jbe@241 2637 "output_row"."participation" := NULL;
jbe@241 2638 END IF;
jbe@241 2639 END IF;
jbe@255 2640 IF "simulate_here_v" THEN
jbe@255 2641 IF "simulate_trustee_id_p" ISNULL THEN
jbe@255 2642 SELECT * INTO "delegation_row" FROM "delegation"
jbe@255 2643 WHERE "truster_id" = "output_row"."member_id"
jbe@255 2644 AND (
jbe@255 2645 "unit_id" = "unit_id_v" OR
jbe@255 2646 "area_id" = "area_id_v"
jbe@255 2647 )
jbe@255 2648 ORDER BY "scope" DESC;
jbe@255 2649 END IF;
jbe@255 2650 ELSE
jbe@255 2651 SELECT * INTO "delegation_row" FROM "delegation"
jbe@255 2652 WHERE "truster_id" = "output_row"."member_id"
jbe@255 2653 AND (
jbe@255 2654 "unit_id" = "unit_id_v" OR
jbe@255 2655 "area_id" = "area_id_v" OR
jbe@255 2656 "issue_id" = "issue_id_p"
jbe@255 2657 )
jbe@255 2658 ORDER BY "scope" DESC;
jbe@255 2659 END IF;
jbe@5 2660 END IF;
jbe@5 2661 ELSE
jbe@5 2662 "output_row"."participation" := FALSE;
jbe@5 2663 END IF;
jbe@255 2664 IF "simulate_here_v" AND "simulate_trustee_id_p" NOTNULL THEN
jbe@97 2665 "output_row"."scope_out" := "scope_v";
jbe@5 2666 "output_rows" := "output_rows" || "output_row";
jbe@5 2667 "output_row"."member_id" := "simulate_trustee_id_p";
jbe@5 2668 ELSIF "delegation_row"."trustee_id" NOTNULL THEN
jbe@10 2669 "output_row"."scope_out" := "delegation_row"."scope";
jbe@5 2670 "output_rows" := "output_rows" || "output_row";
jbe@5 2671 "output_row"."member_id" := "delegation_row"."trustee_id";
jbe@86 2672 ELSIF "delegation_row"."scope" NOTNULL THEN
jbe@86 2673 "output_row"."scope_out" := "delegation_row"."scope";
jbe@86 2674 "output_row"."disabled_out" := TRUE;
jbe@86 2675 "output_rows" := "output_rows" || "output_row";
jbe@86 2676 EXIT;
jbe@5 2677 ELSE
jbe@5 2678 "output_row"."scope_out" := NULL;
jbe@5 2679 "output_rows" := "output_rows" || "output_row";
jbe@5 2680 EXIT;
jbe@5 2681 END IF;
jbe@5 2682 EXIT WHEN "loop_member_id_v" NOTNULL;
jbe@5 2683 "output_row"."index" := "output_row"."index" + 1;
jbe@5 2684 END LOOP;
jbe@5 2685 "row_count" := array_upper("output_rows", 1);
jbe@5 2686 "i" := 1;
jbe@5 2687 "loop_v" := FALSE;
jbe@5 2688 LOOP
jbe@5 2689 "output_row" := "output_rows"["i"];
jbe@98 2690 EXIT WHEN "output_row" ISNULL; -- NOTE: ISNULL and NOT ... NOTNULL produce different results!
jbe@5 2691 IF "loop_v" THEN
jbe@5 2692 IF "i" + 1 = "row_count" THEN
jbe@5 2693 "output_row"."loop" := 'last';
jbe@5 2694 ELSIF "i" = "row_count" THEN
jbe@5 2695 "output_row"."loop" := 'repetition';
jbe@5 2696 ELSE
jbe@5 2697 "output_row"."loop" := 'intermediate';
jbe@5 2698 END IF;
jbe@5 2699 ELSIF "output_row"."member_id" = "loop_member_id_v" THEN
jbe@5 2700 "output_row"."loop" := 'first';
jbe@5 2701 "loop_v" := TRUE;
jbe@5 2702 END IF;
jbe@97 2703 IF "scope_v" = 'unit' THEN
jbe@5 2704 "output_row"."participation" := NULL;
jbe@5 2705 END IF;
jbe@5 2706 RETURN NEXT "output_row";
jbe@5 2707 "i" := "i" + 1;
jbe@5 2708 END LOOP;
jbe@5 2709 RETURN;
jbe@5 2710 END;
jbe@5 2711 $$;
jbe@5 2712
jbe@5 2713 COMMENT ON FUNCTION "delegation_chain"
jbe@5 2714 ( "member"."id"%TYPE,
jbe@97 2715 "unit"."id"%TYPE,
jbe@5 2716 "area"."id"%TYPE,
jbe@5 2717 "issue"."id"%TYPE,
jbe@255 2718 "member"."id"%TYPE,
jbe@255 2719 BOOLEAN )
jbe@242 2720 IS 'Shows a delegation chain for unit, area, or issue; See "delegation_chain_row" type for more information';
jbe@242 2721
jbe@242 2722
jbe@242 2723
jbe@242 2724 ---------------------------------------------------------
jbe@242 2725 -- Single row returning function for delegation chains --
jbe@242 2726 ---------------------------------------------------------
jbe@242 2727
jbe@242 2728
jbe@242 2729 CREATE TYPE "delegation_info_loop_type" AS ENUM
jbe@242 2730 ('own', 'first', 'first_ellipsis', 'other', 'other_ellipsis');
jbe@240 2731
jbe@243 2732 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 2733
jbe@243 2734
jbe@240 2735 CREATE TYPE "delegation_info_type" AS (
jbe@242 2736 "own_participation" BOOLEAN,
jbe@242 2737 "own_delegation_scope" "delegation_scope",
jbe@242 2738 "first_trustee_id" INT4,
jbe@240 2739 "first_trustee_participation" BOOLEAN,
jbe@242 2740 "first_trustee_ellipsis" BOOLEAN,
jbe@242 2741 "other_trustee_id" INT4,
jbe@240 2742 "other_trustee_participation" BOOLEAN,
jbe@242 2743 "other_trustee_ellipsis" BOOLEAN,
jbe@253 2744 "delegation_loop" "delegation_info_loop_type",
jbe@253 2745 "participating_member_id" INT4 );
jbe@240 2746
jbe@243 2747 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 2748
jbe@243 2749 COMMENT ON COLUMN "delegation_info_type"."own_participation" IS 'Member is directly participating';
jbe@243 2750 COMMENT ON COLUMN "delegation_info_type"."own_delegation_scope" IS 'Delegation scope of member';
jbe@243 2751 COMMENT ON COLUMN "delegation_info_type"."first_trustee_id" IS 'Direct trustee of member';
jbe@243 2752 COMMENT ON COLUMN "delegation_info_type"."first_trustee_participation" IS 'Direct trustee of member is participating';
jbe@243 2753 COMMENT ON COLUMN "delegation_info_type"."first_trustee_ellipsis" IS 'Ellipsis in delegation chain after "first_trustee"';
jbe@243 2754 COMMENT ON COLUMN "delegation_info_type"."other_trustee_id" IS 'Another relevant trustee (due to participation)';
jbe@243 2755 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 2756 COMMENT ON COLUMN "delegation_info_type"."other_trustee_ellipsis" IS 'Ellipsis in delegation chain after "other_trustee"';
jbe@243 2757 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 2758 COMMENT ON COLUMN "delegation_info_type"."participating_member_id" IS 'First participating member in delegation chain';
jbe@243 2759
jbe@243 2760
jbe@240 2761 CREATE FUNCTION "delegation_info"
jbe@242 2762 ( "member_id_p" "member"."id"%TYPE,
jbe@242 2763 "unit_id_p" "unit"."id"%TYPE,
jbe@242 2764 "area_id_p" "area"."id"%TYPE,
jbe@242 2765 "issue_id_p" "issue"."id"%TYPE,
jbe@255 2766 "simulate_trustee_id_p" "member"."id"%TYPE DEFAULT NULL,
jbe@255 2767 "simulate_default_p" BOOLEAN DEFAULT FALSE )
jbe@240 2768 RETURNS "delegation_info_type"
jbe@240 2769 LANGUAGE 'plpgsql' STABLE AS $$
jbe@240 2770 DECLARE
jbe@242 2771 "current_row" "delegation_chain_row";
jbe@242 2772 "result" "delegation_info_type";
jbe@240 2773 BEGIN
jbe@242 2774 "result"."own_participation" := FALSE;
jbe@242 2775 FOR "current_row" IN
jbe@242 2776 SELECT * FROM "delegation_chain"(
jbe@242 2777 "member_id_p",
jbe@242 2778 "unit_id_p", "area_id_p", "issue_id_p",
jbe@255 2779 "simulate_trustee_id_p", "simulate_default_p")
jbe@242 2780 LOOP
jbe@253 2781 IF
jbe@253 2782 "result"."participating_member_id" ISNULL AND
jbe@253 2783 "current_row"."participation"
jbe@253 2784 THEN
jbe@253 2785 "result"."participating_member_id" := "current_row"."member_id";
jbe@253 2786 END IF;
jbe@242 2787 IF "current_row"."member_id" = "member_id_p" THEN
jbe@242 2788 "result"."own_participation" := "current_row"."participation";
jbe@242 2789 "result"."own_delegation_scope" := "current_row"."scope_out";
jbe@242 2790 IF "current_row"."loop" = 'first' THEN
jbe@242 2791 "result"."delegation_loop" := 'own';
jbe@242 2792 END IF;
jbe@242 2793 ELSIF
jbe@242 2794 "current_row"."member_valid" AND
jbe@242 2795 ( "current_row"."loop" ISNULL OR
jbe@242 2796 "current_row"."loop" != 'repetition' )
jbe@242 2797 THEN
jbe@242 2798 IF "result"."first_trustee_id" ISNULL THEN
jbe@242 2799 "result"."first_trustee_id" := "current_row"."member_id";
jbe@242 2800 "result"."first_trustee_participation" := "current_row"."participation";
jbe@242 2801 "result"."first_trustee_ellipsis" := FALSE;
jbe@242 2802 IF "current_row"."loop" = 'first' THEN
jbe@242 2803 "result"."delegation_loop" := 'first';
jbe@242 2804 END IF;
jbe@242 2805 ELSIF "result"."other_trustee_id" ISNULL THEN
jbe@247 2806 IF "current_row"."participation" AND NOT "current_row"."overridden" THEN
jbe@242 2807 "result"."other_trustee_id" := "current_row"."member_id";
jbe@242 2808 "result"."other_trustee_participation" := TRUE;
jbe@242 2809 "result"."other_trustee_ellipsis" := FALSE;
jbe@242 2810 IF "current_row"."loop" = 'first' THEN
jbe@242 2811 "result"."delegation_loop" := 'other';
jbe@240 2812 END IF;
jbe@240 2813 ELSE
jbe@242 2814 "result"."first_trustee_ellipsis" := TRUE;
jbe@242 2815 IF "current_row"."loop" = 'first' THEN
jbe@242 2816 "result"."delegation_loop" := 'first_ellipsis';
jbe@242 2817 END IF;
jbe@242 2818 END IF;
jbe@242 2819 ELSE
jbe@242 2820 "result"."other_trustee_ellipsis" := TRUE;
jbe@242 2821 IF "current_row"."loop" = 'first' THEN
jbe@242 2822 "result"."delegation_loop" := 'other_ellipsis';
jbe@240 2823 END IF;
jbe@240 2824 END IF;
jbe@240 2825 END IF;
jbe@242 2826 END LOOP;
jbe@240 2827 RETURN "result";
jbe@240 2828 END;
jbe@240 2829 $$;
jbe@240 2830
jbe@243 2831 COMMENT ON FUNCTION "delegation_info"
jbe@243 2832 ( "member"."id"%TYPE,
jbe@243 2833 "unit"."id"%TYPE,
jbe@243 2834 "area"."id"%TYPE,
jbe@243 2835 "issue"."id"%TYPE,
jbe@255 2836 "member"."id"%TYPE,
jbe@255 2837 BOOLEAN )
jbe@243 2838 IS 'Notable information about a delegation chain for unit, area, or issue; See "delegation_info_type" for more information';
jbe@243 2839
jbe@240 2840
jbe@240 2841
jbe@333 2842 ---------------------------
jbe@333 2843 -- Transaction isolation --
jbe@333 2844 ---------------------------
jbe@333 2845
jbe@344 2846
jbe@333 2847 CREATE FUNCTION "require_transaction_isolation"()
jbe@333 2848 RETURNS VOID
jbe@333 2849 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@333 2850 BEGIN
jbe@333 2851 IF
jbe@333 2852 current_setting('transaction_isolation') NOT IN
jbe@333 2853 ('repeatable read', 'serializable')
jbe@333 2854 THEN
jbe@333 2855 RAISE EXCEPTION 'Insufficient transaction isolation level';
jbe@333 2856 END IF;
jbe@333 2857 RETURN;
jbe@333 2858 END;
jbe@333 2859 $$;
jbe@333 2860
jbe@344 2861 COMMENT ON FUNCTION "require_transaction_isolation"() IS 'Throws an exception, if transaction isolation level is too low to provide a consistent snapshot';
jbe@344 2862
jbe@333 2863
jbe@333 2864 CREATE FUNCTION "dont_require_transaction_isolation"()
jbe@333 2865 RETURNS VOID
jbe@333 2866 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@333 2867 BEGIN
jbe@333 2868 IF
jbe@333 2869 current_setting('transaction_isolation') IN
jbe@333 2870 ('repeatable read', 'serializable')
jbe@333 2871 THEN
jbe@333 2872 RAISE WARNING 'Unneccessary transaction isolation level: %',
jbe@333 2873 current_setting('transaction_isolation');
jbe@333 2874 END IF;
jbe@333 2875 RETURN;
jbe@333 2876 END;
jbe@333 2877 $$;
jbe@333 2878
jbe@344 2879 COMMENT ON FUNCTION "dont_require_transaction_isolation"() IS 'Raises a warning, if transaction isolation level is higher than READ COMMITTED';
jbe@344 2880
jbe@333 2881
jbe@333 2882
jbe@103 2883 ------------------------------------------------------------------------
jbe@103 2884 -- Regular tasks, except calculcation of snapshots and voting results --
jbe@103 2885 ------------------------------------------------------------------------
jbe@103 2886
jbe@333 2887
jbe@184 2888 CREATE FUNCTION "check_activity"()
jbe@103 2889 RETURNS VOID
jbe@103 2890 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@104 2891 DECLARE
jbe@104 2892 "system_setting_row" "system_setting"%ROWTYPE;
jbe@103 2893 BEGIN
jbe@333 2894 PERFORM "dont_require_transaction_isolation"();
jbe@104 2895 SELECT * INTO "system_setting_row" FROM "system_setting";
jbe@104 2896 IF "system_setting_row"."member_ttl" NOTNULL THEN
jbe@104 2897 UPDATE "member" SET "active" = FALSE
jbe@104 2898 WHERE "active" = TRUE
jbe@184 2899 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
jbe@104 2900 END IF;
jbe@103 2901 RETURN;
jbe@103 2902 END;
jbe@103 2903 $$;
jbe@103 2904
jbe@184 2905 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
jbe@103 2906
jbe@4 2907
jbe@4 2908 CREATE FUNCTION "calculate_member_counts"()
jbe@4 2909 RETURNS VOID
jbe@4 2910 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@4 2911 BEGIN
jbe@333 2912 PERFORM "require_transaction_isolation"();
jbe@4 2913 DELETE FROM "member_count";
jbe@5 2914 INSERT INTO "member_count" ("total_count")
jbe@5 2915 SELECT "total_count" FROM "member_count_view";
jbe@97 2916 UPDATE "unit" SET "member_count" = "view"."member_count"
jbe@97 2917 FROM "unit_member_count" AS "view"
jbe@97 2918 WHERE "view"."unit_id" = "unit"."id";
jbe@5 2919 UPDATE "area" SET
jbe@5 2920 "direct_member_count" = "view"."direct_member_count",
jbe@169 2921 "member_weight" = "view"."member_weight"
jbe@5 2922 FROM "area_member_count" AS "view"
jbe@5 2923 WHERE "view"."area_id" = "area"."id";
jbe@4 2924 RETURN;
jbe@4 2925 END;
jbe@4 2926 $$;
jbe@4 2927
jbe@4 2928 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 2929
jbe@4 2930
jbe@4 2931
jbe@327 2932 ------------------------------------
jbe@327 2933 -- Calculation of harmonic weight --
jbe@327 2934 ------------------------------------
jbe@310 2935
jbe@312 2936
jbe@310 2937 CREATE VIEW "remaining_harmonic_supporter_weight" AS
jbe@310 2938 SELECT
jbe@310 2939 "direct_interest_snapshot"."issue_id",
jbe@310 2940 "direct_interest_snapshot"."event",
jbe@310 2941 "direct_interest_snapshot"."member_id",
jbe@310 2942 "direct_interest_snapshot"."weight" AS "weight_num",
jbe@310 2943 count("initiative"."id") AS "weight_den"
jbe@312 2944 FROM "issue"
jbe@312 2945 JOIN "direct_interest_snapshot"
jbe@312 2946 ON "issue"."id" = "direct_interest_snapshot"."issue_id"
jbe@312 2947 AND "issue"."latest_snapshot_event" = "direct_interest_snapshot"."event"
jbe@327 2948 JOIN "initiative"
jbe@327 2949 ON "issue"."id" = "initiative"."issue_id"
jbe@327 2950 AND "initiative"."harmonic_weight" ISNULL
jbe@310 2951 JOIN "direct_supporter_snapshot"
jbe@327 2952 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
jbe@310 2953 AND "direct_interest_snapshot"."event" = "direct_supporter_snapshot"."event"
jbe@310 2954 AND "direct_interest_snapshot"."member_id" = "direct_supporter_snapshot"."member_id"
jbe@321 2955 AND (
jbe@321 2956 "direct_supporter_snapshot"."satisfied" = TRUE OR
jbe@321 2957 coalesce("initiative"."admitted", FALSE) = FALSE
jbe@321 2958 )
jbe@310 2959 GROUP BY
jbe@310 2960 "direct_interest_snapshot"."issue_id",
jbe@310 2961 "direct_interest_snapshot"."event",
jbe@310 2962 "direct_interest_snapshot"."member_id",
jbe@310 2963 "direct_interest_snapshot"."weight";
jbe@310 2964
jbe@310 2965 COMMENT ON VIEW "remaining_harmonic_supporter_weight" IS 'Helper view for function "set_harmonic_initiative_weights"';
jbe@310 2966
jbe@310 2967
jbe@310 2968 CREATE VIEW "remaining_harmonic_initiative_weight_summands" AS
jbe@310 2969 SELECT
jbe@310 2970 "initiative"."issue_id",
jbe@310 2971 "initiative"."id" AS "initiative_id",
jbe@320 2972 "initiative"."admitted",
jbe@310 2973 sum("remaining_harmonic_supporter_weight"."weight_num") AS "weight_num",
jbe@310 2974 "remaining_harmonic_supporter_weight"."weight_den"
jbe@310 2975 FROM "remaining_harmonic_supporter_weight"
jbe@327 2976 JOIN "initiative"
jbe@327 2977 ON "remaining_harmonic_supporter_weight"."issue_id" = "initiative"."issue_id"
jbe@327 2978 AND "initiative"."harmonic_weight" ISNULL
jbe@310 2979 JOIN "direct_supporter_snapshot"
jbe@327 2980 ON "initiative"."id" = "direct_supporter_snapshot"."initiative_id"
jbe@310 2981 AND "remaining_harmonic_supporter_weight"."event" = "direct_supporter_snapshot"."event"
jbe@310 2982 AND "remaining_harmonic_supporter_weight"."member_id" = "direct_supporter_snapshot"."member_id"
jbe@321 2983 AND (
jbe@321 2984 "direct_supporter_snapshot"."satisfied" = TRUE OR
jbe@321 2985 coalesce("initiative"."admitted", FALSE) = FALSE
jbe@321 2986 )
jbe@310 2987 GROUP BY
jbe@310 2988 "initiative"."issue_id",
jbe@310 2989 "initiative"."id",
jbe@320 2990 "initiative"."admitted",
jbe@310 2991 "remaining_harmonic_supporter_weight"."weight_den";
jbe@310 2992
jbe@310 2993 COMMENT ON VIEW "remaining_harmonic_initiative_weight_summands" IS 'Helper view for function "set_harmonic_initiative_weights"';
jbe@310 2994
jbe@310 2995
jbe@349 2996 CREATE VIEW "remaining_harmonic_initiative_weight_dummies" AS
jbe@349 2997 SELECT
jbe@349 2998 "issue_id",
jbe@349 2999 "id" AS "initiative_id",
jbe@349 3000 "admitted",
jbe@349 3001 0 AS "weight_num",
jbe@349 3002 1 AS "weight_den"
jbe@349 3003 FROM "initiative"
jbe@349 3004 WHERE "harmonic_weight" ISNULL;
jbe@349 3005
jbe@349 3006 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 3007
jbe@349 3008
jbe@310 3009 CREATE FUNCTION "set_harmonic_initiative_weights"
jbe@310 3010 ( "issue_id_p" "issue"."id"%TYPE )
jbe@310 3011 RETURNS VOID
jbe@310 3012 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@310 3013 DECLARE
jbe@310 3014 "weight_row" "remaining_harmonic_initiative_weight_summands"%ROWTYPE;
jbe@310 3015 "i" INT4;
jbe@310 3016 "count_v" INT4;
jbe@310 3017 "summand_v" FLOAT;
jbe@310 3018 "id_ary" INT4[];
jbe@310 3019 "weight_ary" FLOAT[];
jbe@310 3020 "min_weight_v" FLOAT;
jbe@310 3021 BEGIN
jbe@333 3022 PERFORM "require_transaction_isolation"();
jbe@312 3023 UPDATE "initiative" SET "harmonic_weight" = NULL
jbe@312 3024 WHERE "issue_id" = "issue_id_p";
jbe@310 3025 LOOP
jbe@310 3026 "min_weight_v" := NULL;
jbe@310 3027 "i" := 0;
jbe@310 3028 "count_v" := 0;
jbe@310 3029 FOR "weight_row" IN
jbe@310 3030 SELECT * FROM "remaining_harmonic_initiative_weight_summands"
jbe@310 3031 WHERE "issue_id" = "issue_id_p"
jbe@320 3032 AND (
jbe@320 3033 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
jbe@320 3034 SELECT NULL FROM "initiative"
jbe@320 3035 WHERE "issue_id" = "issue_id_p"
jbe@320 3036 AND "harmonic_weight" ISNULL
jbe@320 3037 AND coalesce("admitted", FALSE) = FALSE
jbe@320 3038 )
jbe@320 3039 )
jbe@349 3040 UNION ALL -- needed for corner cases
jbe@349 3041 SELECT * FROM "remaining_harmonic_initiative_weight_dummies"
jbe@349 3042 WHERE "issue_id" = "issue_id_p"
jbe@349 3043 AND (
jbe@349 3044 coalesce("admitted", FALSE) = FALSE OR NOT EXISTS (
jbe@349 3045 SELECT NULL FROM "initiative"
jbe@349 3046 WHERE "issue_id" = "issue_id_p"
jbe@349 3047 AND "harmonic_weight" ISNULL
jbe@349 3048 AND coalesce("admitted", FALSE) = FALSE
jbe@349 3049 )
jbe@349 3050 )
jbe@310 3051 ORDER BY "initiative_id" DESC, "weight_den" DESC
jbe@320 3052 -- NOTE: non-admitted initiatives placed first (at last positions),
jbe@320 3053 -- latest initiatives treated worse in case of tie
jbe@310 3054 LOOP
jbe@310 3055 "summand_v" := "weight_row"."weight_num"::FLOAT / "weight_row"."weight_den"::FLOAT;
jbe@310 3056 IF "i" = 0 OR "weight_row"."initiative_id" != "id_ary"["i"] THEN
jbe@310 3057 "i" := "i" + 1;
jbe@310 3058 "count_v" := "i";
jbe@310 3059 "id_ary"["i"] := "weight_row"."initiative_id";
jbe@310 3060 "weight_ary"["i"] := "summand_v";
jbe@310 3061 ELSE
jbe@310 3062 "weight_ary"["i"] := "weight_ary"["i"] + "summand_v";
jbe@310 3063 END IF;
jbe@310 3064 END LOOP;
jbe@310 3065 EXIT WHEN "count_v" = 0;
jbe@310 3066 "i" := 1;
jbe@310 3067 LOOP
jbe@313 3068 "weight_ary"["i"] := "weight_ary"["i"]::NUMERIC(18,9)::NUMERIC(12,3);
jbe@310 3069 IF "min_weight_v" ISNULL OR "weight_ary"["i"] < "min_weight_v" THEN
jbe@310 3070 "min_weight_v" := "weight_ary"["i"];
jbe@310 3071 END IF;
jbe@310 3072 "i" := "i" + 1;
jbe@310 3073 EXIT WHEN "i" > "count_v";
jbe@310 3074 END LOOP;
jbe@310 3075 "i" := 1;
jbe@310 3076 LOOP
jbe@310 3077 IF "weight_ary"["i"] = "min_weight_v" THEN
jbe@310 3078 UPDATE "initiative" SET "harmonic_weight" = "min_weight_v"
jbe@310 3079 WHERE "id" = "id_ary"["i"];
jbe@310 3080 EXIT;
jbe@310 3081 END IF;
jbe@310 3082 "i" := "i" + 1;
jbe@310 3083 END LOOP;
jbe@310 3084 END LOOP;
jbe@316 3085 UPDATE "initiative" SET "harmonic_weight" = 0
jbe@316 3086 WHERE "issue_id" = "issue_id_p" AND "harmonic_weight" ISNULL;
jbe@310 3087 END;
jbe@310 3088 $$;
jbe@310 3089
jbe@310 3090 COMMENT ON FUNCTION "set_harmonic_initiative_weights"
jbe@310 3091 ( "issue"."id"%TYPE )
jbe@310 3092 IS 'Calculates and sets "harmonic_weight" of initiatives in a given issue';
jbe@310 3093
jbe@310 3094
jbe@312 3095
jbe@0 3096 ------------------------------
jbe@0 3097 -- Calculation of snapshots --
jbe@0 3098 ------------------------------
jbe@0 3099
jbe@312 3100
jbe@0 3101 CREATE FUNCTION "weight_of_added_delegations_for_population_snapshot"
jbe@0 3102 ( "issue_id_p" "issue"."id"%TYPE,
jbe@0 3103 "member_id_p" "member"."id"%TYPE,
jbe@0 3104 "delegate_member_ids_p" "delegating_population_snapshot"."delegate_member_ids"%TYPE )
jbe@0 3105 RETURNS "direct_population_snapshot"."weight"%TYPE
jbe@0 3106 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 3107 DECLARE
jbe@0 3108 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@0 3109 "delegate_member_ids_v" "delegating_population_snapshot"."delegate_member_ids"%TYPE;
jbe@0 3110 "weight_v" INT4;
jbe@8 3111 "sub_weight_v" INT4;
jbe@0 3112 BEGIN
jbe@336 3113 PERFORM "require_transaction_isolation"();
jbe@0 3114 "weight_v" := 0;
jbe@0 3115 FOR "issue_delegation_row" IN
jbe@0 3116 SELECT * FROM "issue_delegation"
jbe@0 3117 WHERE "trustee_id" = "member_id_p"
jbe@0 3118 AND "issue_id" = "issue_id_p"
jbe@0 3119 LOOP
jbe@0 3120 IF NOT EXISTS (
jbe@0 3121 SELECT NULL FROM "direct_population_snapshot"
jbe@0 3122 WHERE "issue_id" = "issue_id_p"
jbe@0 3123 AND "event" = 'periodic'
jbe@0 3124 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0 3125 ) AND NOT EXISTS (
jbe@0 3126 SELECT NULL FROM "delegating_population_snapshot"
jbe@0 3127 WHERE "issue_id" = "issue_id_p"
jbe@0 3128 AND "event" = 'periodic'
jbe@0 3129 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0 3130 ) THEN
jbe@0 3131 "delegate_member_ids_v" :=
jbe@0 3132 "member_id_p" || "delegate_member_ids_p";
jbe@10 3133 INSERT INTO "delegating_population_snapshot" (
jbe@10 3134 "issue_id",
jbe@10 3135 "event",
jbe@10 3136 "member_id",
jbe@10 3137 "scope",
jbe@10 3138 "delegate_member_ids"
jbe@10 3139 ) VALUES (
jbe@0 3140 "issue_id_p",
jbe@0 3141 'periodic',
jbe@0 3142 "issue_delegation_row"."truster_id",
jbe@10 3143 "issue_delegation_row"."scope",
jbe@0 3144 "delegate_member_ids_v"
jbe@0 3145 );
jbe@8 3146 "sub_weight_v" := 1 +
jbe@0 3147 "weight_of_added_delegations_for_population_snapshot"(
jbe@0 3148 "issue_id_p",
jbe@0 3149 "issue_delegation_row"."truster_id",
jbe@0 3150 "delegate_member_ids_v"
jbe@0 3151 );
jbe@8 3152 UPDATE "delegating_population_snapshot"
jbe@8 3153 SET "weight" = "sub_weight_v"
jbe@8 3154 WHERE "issue_id" = "issue_id_p"
jbe@8 3155 AND "event" = 'periodic'
jbe@8 3156 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@8 3157 "weight_v" := "weight_v" + "sub_weight_v";
jbe@0 3158 END IF;
jbe@0 3159 END LOOP;
jbe@0 3160 RETURN "weight_v";
jbe@0 3161 END;
jbe@0 3162 $$;
jbe@0 3163
jbe@0 3164 COMMENT ON FUNCTION "weight_of_added_delegations_for_population_snapshot"
jbe@0 3165 ( "issue"."id"%TYPE,
jbe@0 3166 "member"."id"%TYPE,
jbe@0 3167 "delegating_population_snapshot"."delegate_member_ids"%TYPE )
jbe@0 3168 IS 'Helper function for "create_population_snapshot" function';
jbe@0 3169
jbe@0 3170
jbe@0 3171 CREATE FUNCTION "create_population_snapshot"
jbe@0 3172 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 3173 RETURNS VOID
jbe@0 3174 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 3175 DECLARE
jbe@0 3176 "member_id_v" "member"."id"%TYPE;
jbe@0 3177 BEGIN
jbe@336 3178 PERFORM "require_transaction_isolation"();
jbe@0 3179 DELETE FROM "direct_population_snapshot"
jbe@0 3180 WHERE "issue_id" = "issue_id_p"
jbe@0 3181 AND "event" = 'periodic';
jbe@0 3182 DELETE FROM "delegating_population_snapshot"
jbe@0 3183 WHERE "issue_id" = "issue_id_p"
jbe@0 3184 AND "event" = 'periodic';
jbe@0 3185 INSERT INTO "direct_population_snapshot"
jbe@54 3186 ("issue_id", "event", "member_id")
jbe@54 3187 SELECT
jbe@54 3188 "issue_id_p" AS "issue_id",
jbe@54 3189 'periodic'::"snapshot_event" AS "event",
jbe@54 3190 "member"."id" AS "member_id"
jbe@54 3191 FROM "issue"
jbe@54 3192 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@54 3193 JOIN "membership" ON "area"."id" = "membership"."area_id"
jbe@54 3194 JOIN "member" ON "membership"."member_id" = "member"."id"
jbe@97 3195 JOIN "privilege"
jbe@97 3196 ON "privilege"."unit_id" = "area"."unit_id"
jbe@97 3197 AND "privilege"."member_id" = "member"."id"
jbe@54 3198 WHERE "issue"."id" = "issue_id_p"
jbe@97 3199 AND "member"."active" AND "privilege"."voting_right"
jbe@54 3200 UNION
jbe@54 3201 SELECT
jbe@54 3202 "issue_id_p" AS "issue_id",
jbe@54 3203 'periodic'::"snapshot_event" AS "event",
jbe@54 3204 "member"."id" AS "member_id"
jbe@97 3205 FROM "issue"
jbe@97 3206 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@97 3207 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
jbe@97 3208 JOIN "member" ON "interest"."member_id" = "member"."id"
jbe@97 3209 JOIN "privilege"
jbe@97 3210 ON "privilege"."unit_id" = "area"."unit_id"
jbe@97 3211 AND "privilege"."member_id" = "member"."id"
jbe@97 3212 WHERE "issue"."id" = "issue_id_p"
jbe@97 3213 AND "member"."active" AND "privilege"."voting_right";
jbe@0 3214 FOR "member_id_v" IN
jbe@0 3215 SELECT "member_id" FROM "direct_population_snapshot"
jbe@0 3216 WHERE "issue_id" = "issue_id_p"
jbe@0 3217 AND "event" = 'periodic'
jbe@0 3218 LOOP
jbe@0 3219 UPDATE "direct_population_snapshot" SET
jbe@0 3220 "weight" = 1 +
jbe@0 3221 "weight_of_added_delegations_for_population_snapshot"(
jbe@0 3222 "issue_id_p",
jbe@0 3223 "member_id_v",
jbe@0 3224 '{}'
jbe@0 3225 )
jbe@0 3226 WHERE "issue_id" = "issue_id_p"
jbe@0 3227 AND "event" = 'periodic'
jbe@0 3228 AND "member_id" = "member_id_v";
jbe@0 3229 END LOOP;
jbe@0 3230 RETURN;
jbe@0 3231 END;
jbe@0 3232 $$;
jbe@0 3233
jbe@0 3234 COMMENT ON FUNCTION "create_population_snapshot"
jbe@67 3235 ( "issue"."id"%TYPE )
jbe@0 3236 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 3237
jbe@0 3238
jbe@0 3239 CREATE FUNCTION "weight_of_added_delegations_for_interest_snapshot"
jbe@0 3240 ( "issue_id_p" "issue"."id"%TYPE,
jbe@0 3241 "member_id_p" "member"."id"%TYPE,
jbe@0 3242 "delegate_member_ids_p" "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@0 3243 RETURNS "direct_interest_snapshot"."weight"%TYPE
jbe@0 3244 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 3245 DECLARE
jbe@0 3246 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@0 3247 "delegate_member_ids_v" "delegating_interest_snapshot"."delegate_member_ids"%TYPE;
jbe@0 3248 "weight_v" INT4;
jbe@8 3249 "sub_weight_v" INT4;
jbe@0 3250 BEGIN
jbe@336 3251 PERFORM "require_transaction_isolation"();
jbe@0 3252 "weight_v" := 0;
jbe@0 3253 FOR "issue_delegation_row" IN
jbe@0 3254 SELECT * FROM "issue_delegation"
jbe@0 3255 WHERE "trustee_id" = "member_id_p"
jbe@0 3256 AND "issue_id" = "issue_id_p"
jbe@0 3257 LOOP
jbe@0 3258 IF NOT EXISTS (
jbe@0 3259 SELECT NULL FROM "direct_interest_snapshot"
jbe@0 3260 WHERE "issue_id" = "issue_id_p"
jbe@0 3261 AND "event" = 'periodic'
jbe@0 3262 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0 3263 ) AND NOT EXISTS (
jbe@0 3264 SELECT NULL FROM "delegating_interest_snapshot"
jbe@0 3265 WHERE "issue_id" = "issue_id_p"
jbe@0 3266 AND "event" = 'periodic'
jbe@0 3267 AND "member_id" = "issue_delegation_row"."truster_id"
jbe@0 3268 ) THEN
jbe@0 3269 "delegate_member_ids_v" :=
jbe@0 3270 "member_id_p" || "delegate_member_ids_p";
jbe@10 3271 INSERT INTO "delegating_interest_snapshot" (
jbe@10 3272 "issue_id",
jbe@10 3273 "event",
jbe@10 3274 "member_id",
jbe@10 3275 "scope",
jbe@10 3276 "delegate_member_ids"
jbe@10 3277 ) VALUES (
jbe@0 3278 "issue_id_p",
jbe@0 3279 'periodic',
jbe@0 3280 "issue_delegation_row"."truster_id",
jbe@10 3281 "issue_delegation_row"."scope",
jbe@0 3282 "delegate_member_ids_v"
jbe@0 3283 );
jbe@8 3284 "sub_weight_v" := 1 +
jbe@0 3285 "weight_of_added_delegations_for_interest_snapshot"(
jbe@0 3286 "issue_id_p",
jbe@0 3287 "issue_delegation_row"."truster_id",
jbe@0 3288 "delegate_member_ids_v"
jbe@0 3289 );
jbe@8 3290 UPDATE "delegating_interest_snapshot"
jbe@8 3291 SET "weight" = "sub_weight_v"
jbe@8 3292 WHERE "issue_id" = "issue_id_p"
jbe@8 3293 AND "event" = 'periodic'
jbe@8 3294 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@8 3295 "weight_v" := "weight_v" + "sub_weight_v";
jbe@0 3296 END IF;
jbe@0 3297 END LOOP;
jbe@0 3298 RETURN "weight_v";
jbe@0 3299 END;
jbe@0 3300 $$;
jbe@0 3301
jbe@0 3302 COMMENT ON FUNCTION "weight_of_added_delegations_for_interest_snapshot"
jbe@0 3303 ( "issue"."id"%TYPE,
jbe@0 3304 "member"."id"%TYPE,
jbe@0 3305 "delegating_interest_snapshot"."delegate_member_ids"%TYPE )
jbe@0 3306 IS 'Helper function for "create_interest_snapshot" function';
jbe@0 3307
jbe@0 3308
jbe@0 3309 CREATE FUNCTION "create_interest_snapshot"
jbe@0 3310 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 3311 RETURNS VOID
jbe@0 3312 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 3313 DECLARE
jbe@0 3314 "member_id_v" "member"."id"%TYPE;
jbe@0 3315 BEGIN
jbe@336 3316 PERFORM "require_transaction_isolation"();
jbe@0 3317 DELETE FROM "direct_interest_snapshot"
jbe@0 3318 WHERE "issue_id" = "issue_id_p"
jbe@0 3319 AND "event" = 'periodic';
jbe@0 3320 DELETE FROM "delegating_interest_snapshot"
jbe@0 3321 WHERE "issue_id" = "issue_id_p"
jbe@0 3322 AND "event" = 'periodic';
jbe@0 3323 DELETE FROM "direct_supporter_snapshot"
jbe@325 3324 USING "initiative" -- NOTE: due to missing index on issue_id
jbe@325 3325 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@325 3326 AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
jbe@325 3327 AND "direct_supporter_snapshot"."event" = 'periodic';
jbe@0 3328 INSERT INTO "direct_interest_snapshot"
jbe@144 3329 ("issue_id", "event", "member_id")
jbe@0 3330 SELECT
jbe@0 3331 "issue_id_p" AS "issue_id",
jbe@0 3332 'periodic' AS "event",
jbe@144 3333 "member"."id" AS "member_id"
jbe@97 3334 FROM "issue"
jbe@97 3335 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@97 3336 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
jbe@97 3337 JOIN "member" ON "interest"."member_id" = "member"."id"
jbe@97 3338 JOIN "privilege"
jbe@97 3339 ON "privilege"."unit_id" = "area"."unit_id"
jbe@97 3340 AND "privilege"."member_id" = "member"."id"
jbe@97 3341 WHERE "issue"."id" = "issue_id_p"
jbe@97 3342 AND "member"."active" AND "privilege"."voting_right";
jbe@0 3343 FOR "member_id_v" IN
jbe@0 3344 SELECT "member_id" FROM "direct_interest_snapshot"
jbe@0 3345 WHERE "issue_id" = "issue_id_p"
jbe@0 3346 AND "event" = 'periodic'
jbe@0 3347 LOOP
jbe@0 3348 UPDATE "direct_interest_snapshot" SET
jbe@0 3349 "weight" = 1 +
jbe@0 3350 "weight_of_added_delegations_for_interest_snapshot"(
jbe@0 3351 "issue_id_p",
jbe@0 3352 "member_id_v",
jbe@0 3353 '{}'
jbe@0 3354 )
jbe@0 3355 WHERE "issue_id" = "issue_id_p"
jbe@0 3356 AND "event" = 'periodic'
jbe@0 3357 AND "member_id" = "member_id_v";
jbe@0 3358 END LOOP;
jbe@0 3359 INSERT INTO "direct_supporter_snapshot"
jbe@0 3360 ( "issue_id", "initiative_id", "event", "member_id",
jbe@204 3361 "draft_id", "informed", "satisfied" )
jbe@0 3362 SELECT
jbe@96 3363 "issue_id_p" AS "issue_id",
jbe@96 3364 "initiative"."id" AS "initiative_id",
jbe@96 3365 'periodic' AS "event",
jbe@96 3366 "supporter"."member_id" AS "member_id",
jbe@204 3367 "supporter"."draft_id" AS "draft_id",
jbe@0 3368 "supporter"."draft_id" = "current_draft"."id" AS "informed",
jbe@0 3369 NOT EXISTS (
jbe@0 3370 SELECT NULL FROM "critical_opinion"
jbe@0 3371 WHERE "initiative_id" = "initiative"."id"
jbe@96 3372 AND "member_id" = "supporter"."member_id"
jbe@0 3373 ) AS "satisfied"
jbe@96 3374 FROM "initiative"
jbe@96 3375 JOIN "supporter"
jbe@0 3376 ON "supporter"."initiative_id" = "initiative"."id"
jbe@0 3377 JOIN "current_draft"
jbe@0 3378 ON "initiative"."id" = "current_draft"."initiative_id"
jbe@0 3379 JOIN "direct_interest_snapshot"
jbe@96 3380 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
jbe@0 3381 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
jbe@3 3382 AND "event" = 'periodic'
jbe@96 3383 WHERE "initiative"."issue_id" = "issue_id_p";
jbe@0 3384 RETURN;
jbe@0 3385 END;
jbe@0 3386 $$;
jbe@0 3387
jbe@0 3388 COMMENT ON FUNCTION "create_interest_snapshot"
jbe@0 3389 ( "issue"."id"%TYPE )
jbe@0 3390 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 3391
jbe@0 3392
jbe@0 3393 CREATE FUNCTION "create_snapshot"
jbe@0 3394 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 3395 RETURNS VOID
jbe@0 3396 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 3397 DECLARE
jbe@0 3398 "initiative_id_v" "initiative"."id"%TYPE;
jbe@0 3399 "suggestion_id_v" "suggestion"."id"%TYPE;
jbe@0 3400 BEGIN
jbe@333 3401 PERFORM "require_transaction_isolation"();
jbe@0 3402 PERFORM "create_population_snapshot"("issue_id_p");
jbe@0 3403 PERFORM "create_interest_snapshot"("issue_id_p");
jbe@0 3404 UPDATE "issue" SET
jbe@331 3405 "snapshot" = coalesce("phase_finished", now()),
jbe@8 3406 "latest_snapshot_event" = 'periodic',
jbe@0 3407 "population" = (
jbe@0 3408 SELECT coalesce(sum("weight"), 0)
jbe@0 3409 FROM "direct_population_snapshot"
jbe@0 3410 WHERE "issue_id" = "issue_id_p"
jbe@0 3411 AND "event" = 'periodic'
jbe@0 3412 )
jbe@0 3413 WHERE "id" = "issue_id_p";
jbe@0 3414 FOR "initiative_id_v" IN
jbe@0 3415 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
jbe@0 3416 LOOP
jbe@0 3417 UPDATE "initiative" SET
jbe@0 3418 "supporter_count" = (
jbe@0 3419 SELECT coalesce(sum("di"."weight"), 0)
jbe@0 3420 FROM "direct_interest_snapshot" AS "di"
jbe@0 3421 JOIN "direct_supporter_snapshot" AS "ds"
jbe@0 3422 ON "di"."member_id" = "ds"."member_id"
jbe@0 3423 WHERE "di"."issue_id" = "issue_id_p"
jbe@0 3424 AND "di"."event" = 'periodic'
jbe@0 3425 AND "ds"."initiative_id" = "initiative_id_v"
jbe@0 3426 AND "ds"."event" = 'periodic'
jbe@0 3427 ),
jbe@0 3428 "informed_supporter_count" = (
jbe@0 3429 SELECT coalesce(sum("di"."weight"), 0)
jbe@0 3430 FROM "direct_interest_snapshot" AS "di"
jbe@0 3431 JOIN "direct_supporter_snapshot" AS "ds"
jbe@0 3432 ON "di"."member_id" = "ds"."member_id"
jbe@0 3433 WHERE "di"."issue_id" = "issue_id_p"
jbe@0 3434 AND "di"."event" = 'periodic'
jbe@0 3435 AND "ds"."initiative_id" = "initiative_id_v"
jbe@0 3436 AND "ds"."event" = 'periodic'
jbe@0 3437 AND "ds"."informed"
jbe@0 3438 ),
jbe@0 3439 "satisfied_supporter_count" = (
jbe@0 3440 SELECT coalesce(sum("di"."weight"), 0)
jbe@0 3441 FROM "direct_interest_snapshot" AS "di"
jbe@0 3442 JOIN "direct_supporter_snapshot" AS "ds"
jbe@0 3443 ON "di"."member_id" = "ds"."member_id"
jbe@0 3444 WHERE "di"."issue_id" = "issue_id_p"
jbe@0 3445 AND "di"."event" = 'periodic'
jbe@0 3446 AND "ds"."initiative_id" = "initiative_id_v"
jbe@0 3447 AND "ds"."event" = 'periodic'
jbe@0 3448 AND "ds"."satisfied"
jbe@0 3449 ),
jbe@0 3450 "satisfied_informed_supporter_count" = (
jbe@0 3451 SELECT coalesce(sum("di"."weight"), 0)
jbe@0 3452 FROM "direct_interest_snapshot" AS "di"
jbe@0 3453 JOIN "direct_supporter_snapshot" AS "ds"
jbe@0 3454 ON "di"."member_id" = "ds"."member_id"
jbe@0 3455 WHERE "di"."issue_id" = "issue_id_p"
jbe@0 3456 AND "di"."event" = 'periodic'
jbe@0 3457 AND "ds"."initiative_id" = "initiative_id_v"
jbe@0 3458 AND "ds"."event" = 'periodic'
jbe@0 3459 AND "ds"."informed"
jbe@0 3460 AND "ds"."satisfied"
jbe@0 3461 )
jbe@0 3462 WHERE "id" = "initiative_id_v";
jbe@0 3463 FOR "suggestion_id_v" IN
jbe@0 3464 SELECT "id" FROM "suggestion"
jbe@0 3465 WHERE "initiative_id" = "initiative_id_v"
jbe@0 3466 LOOP
jbe@0 3467 UPDATE "suggestion" SET
jbe@0 3468 "minus2_unfulfilled_count" = (
jbe@0 3469 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 3470 FROM "issue" CROSS JOIN "opinion"
jbe@36 3471 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 3472 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 3473 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 3474 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 3475 WHERE "issue"."id" = "issue_id_p"
jbe@36 3476 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 3477 AND "opinion"."degree" = -2
jbe@0 3478 AND "opinion"."fulfilled" = FALSE
jbe@0 3479 ),
jbe@0 3480 "minus2_fulfilled_count" = (
jbe@0 3481 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 3482 FROM "issue" CROSS JOIN "opinion"
jbe@36 3483 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 3484 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 3485 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 3486 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 3487 WHERE "issue"."id" = "issue_id_p"
jbe@36 3488 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 3489 AND "opinion"."degree" = -2
jbe@0 3490 AND "opinion"."fulfilled" = TRUE
jbe@0 3491 ),
jbe@0 3492 "minus1_unfulfilled_count" = (
jbe@0 3493 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 3494 FROM "issue" CROSS JOIN "opinion"
jbe@36 3495 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 3496 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 3497 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 3498 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 3499 WHERE "issue"."id" = "issue_id_p"
jbe@36 3500 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 3501 AND "opinion"."degree" = -1
jbe@0 3502 AND "opinion"."fulfilled" = FALSE
jbe@0 3503 ),
jbe@0 3504 "minus1_fulfilled_count" = (
jbe@0 3505 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 3506 FROM "issue" CROSS JOIN "opinion"
jbe@36 3507 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 3508 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 3509 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 3510 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 3511 WHERE "issue"."id" = "issue_id_p"
jbe@36 3512 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 3513 AND "opinion"."degree" = -1
jbe@0 3514 AND "opinion"."fulfilled" = TRUE
jbe@0 3515 ),
jbe@0 3516 "plus1_unfulfilled_count" = (
jbe@0 3517 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 3518 FROM "issue" CROSS JOIN "opinion"
jbe@36 3519 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 3520 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 3521 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 3522 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 3523 WHERE "issue"."id" = "issue_id_p"
jbe@36 3524 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 3525 AND "opinion"."degree" = 1
jbe@0 3526 AND "opinion"."fulfilled" = FALSE
jbe@0 3527 ),
jbe@0 3528 "plus1_fulfilled_count" = (
jbe@0 3529 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 3530 FROM "issue" CROSS JOIN "opinion"
jbe@36 3531 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 3532 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 3533 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 3534 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 3535 WHERE "issue"."id" = "issue_id_p"
jbe@36 3536 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 3537 AND "opinion"."degree" = 1
jbe@0 3538 AND "opinion"."fulfilled" = TRUE
jbe@0 3539 ),
jbe@0 3540 "plus2_unfulfilled_count" = (
jbe@0 3541 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 3542 FROM "issue" CROSS JOIN "opinion"
jbe@36 3543 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 3544 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 3545 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 3546 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 3547 WHERE "issue"."id" = "issue_id_p"
jbe@36 3548 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 3549 AND "opinion"."degree" = 2
jbe@0 3550 AND "opinion"."fulfilled" = FALSE
jbe@0 3551 ),
jbe@0 3552 "plus2_fulfilled_count" = (
jbe@0 3553 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@36 3554 FROM "issue" CROSS JOIN "opinion"
jbe@36 3555 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@36 3556 ON "snapshot"."issue_id" = "issue"."id"
jbe@36 3557 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@36 3558 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@36 3559 WHERE "issue"."id" = "issue_id_p"
jbe@36 3560 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@0 3561 AND "opinion"."degree" = 2
jbe@0 3562 AND "opinion"."fulfilled" = TRUE
jbe@0 3563 )
jbe@0 3564 WHERE "suggestion"."id" = "suggestion_id_v";
jbe@0 3565 END LOOP;
jbe@0 3566 END LOOP;
jbe@0 3567 RETURN;
jbe@0 3568 END;
jbe@0 3569 $$;
jbe@0 3570
jbe@0 3571 COMMENT ON FUNCTION "create_snapshot"
jbe@0 3572 ( "issue"."id"%TYPE )
jbe@0 3573 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 3574
jbe@0 3575
jbe@0 3576 CREATE FUNCTION "set_snapshot_event"
jbe@0 3577 ( "issue_id_p" "issue"."id"%TYPE,
jbe@0 3578 "event_p" "snapshot_event" )
jbe@0 3579 RETURNS VOID
jbe@0 3580 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@21 3581 DECLARE
jbe@21 3582 "event_v" "issue"."latest_snapshot_event"%TYPE;
jbe@0 3583 BEGIN
jbe@333 3584 PERFORM "require_transaction_isolation"();
jbe@21 3585 SELECT "latest_snapshot_event" INTO "event_v" FROM "issue"
jbe@21 3586 WHERE "id" = "issue_id_p" FOR UPDATE;
jbe@8 3587 UPDATE "issue" SET "latest_snapshot_event" = "event_p"
jbe@8 3588 WHERE "id" = "issue_id_p";
jbe@3 3589 UPDATE "direct_population_snapshot" SET "event" = "event_p"
jbe@21 3590 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@3 3591 UPDATE "delegating_population_snapshot" SET "event" = "event_p"
jbe@21 3592 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@3 3593 UPDATE "direct_interest_snapshot" SET "event" = "event_p"
jbe@21 3594 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@3 3595 UPDATE "delegating_interest_snapshot" SET "event" = "event_p"
jbe@21 3596 WHERE "issue_id" = "issue_id_p" AND "event" = "event_v";
jbe@3 3597 UPDATE "direct_supporter_snapshot" SET "event" = "event_p"
jbe@325 3598 FROM "initiative" -- NOTE: due to missing index on issue_id
jbe@325 3599 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@325 3600 AND "direct_supporter_snapshot"."initiative_id" = "initiative"."id"
jbe@325 3601 AND "direct_supporter_snapshot"."event" = "event_v";
jbe@0 3602 RETURN;
jbe@0 3603 END;
jbe@0 3604 $$;
jbe@0 3605
jbe@0 3606 COMMENT ON FUNCTION "set_snapshot_event"
jbe@0 3607 ( "issue"."id"%TYPE,
jbe@0 3608 "snapshot_event" )
jbe@0 3609 IS 'Change "event" attribute of the previous ''periodic'' snapshot';
jbe@0 3610
jbe@0 3611
jbe@0 3612
jbe@0 3613 -----------------------
jbe@0 3614 -- Counting of votes --
jbe@0 3615 -----------------------
jbe@0 3616
jbe@0 3617
jbe@5 3618 CREATE FUNCTION "weight_of_added_vote_delegations"
jbe@0 3619 ( "issue_id_p" "issue"."id"%TYPE,
jbe@0 3620 "member_id_p" "member"."id"%TYPE,
jbe@0 3621 "delegate_member_ids_p" "delegating_voter"."delegate_member_ids"%TYPE )
jbe@0 3622 RETURNS "direct_voter"."weight"%TYPE
jbe@0 3623 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 3624 DECLARE
jbe@0 3625 "issue_delegation_row" "issue_delegation"%ROWTYPE;
jbe@0 3626 "delegate_member_ids_v" "delegating_voter"."delegate_member_ids"%TYPE;
jbe@0 3627 "weight_v" INT4;
jbe@8 3628 "sub_weight_v" INT4;
jbe@0 3629 BEGIN
jbe@336 3630 PERFORM "require_transaction_isolation"();
jbe@0 3631 "weight_v" := 0;
jbe@0 3632 FOR "issue_delegation_row" IN
jbe@0 3633 SELECT * FROM "issue_delegation"
jbe@0 3634 WHERE "trustee_id" = "member_id_p"
jbe@0 3635 AND "issue_id" = "issue_id_p"
jbe@0 3636 LOOP
jbe@0 3637 IF NOT EXISTS (
jbe@0 3638 SELECT NULL FROM "direct_voter"
jbe@0 3639 WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@0 3640 AND "issue_id" = "issue_id_p"
jbe@0 3641 ) AND NOT EXISTS (
jbe@0 3642 SELECT NULL FROM "delegating_voter"
jbe@0 3643 WHERE "member_id" = "issue_delegation_row"."truster_id"
jbe@0 3644 AND "issue_id" = "issue_id_p"
jbe@0 3645 ) THEN
jbe@0 3646 "delegate_member_ids_v" :=
jbe@0 3647 "member_id_p" || "delegate_member_ids_p";
jbe@10 3648 INSERT INTO "delegating_voter" (
jbe@10 3649 "issue_id",
jbe@10 3650 "member_id",
jbe@10 3651 "scope",
jbe@10 3652 "delegate_member_ids"
jbe@10 3653 ) VALUES (
jbe@5 3654 "issue_id_p",
jbe@5 3655 "issue_delegation_row"."truster_id",
jbe@10 3656 "issue_delegation_row"."scope",
jbe@5 3657 "delegate_member_ids_v"
jbe@5 3658 );
jbe@8 3659 "sub_weight_v" := 1 +
jbe@8 3660 "weight_of_added_vote_delegations"(
jbe@8 3661 "issue_id_p",
jbe@8 3662 "issue_delegation_row"."truster_id",
jbe@8 3663 "delegate_member_ids_v"
jbe@8 3664 );
jbe@8 3665 UPDATE "delegating_voter"
jbe@8 3666 SET "weight" = "sub_weight_v"
jbe@8 3667 WHERE "issue_id" = "issue_id_p"
jbe@8 3668 AND "member_id" = "issue_delegation_row"."truster_id";
jbe@8 3669 "weight_v" := "weight_v" + "sub_weight_v";
jbe@0 3670 END IF;
jbe@0 3671 END LOOP;
jbe@0 3672 RETURN "weight_v";
jbe@0 3673 END;
jbe@0 3674 $$;
jbe@0 3675
jbe@5 3676 COMMENT ON FUNCTION "weight_of_added_vote_delegations"
jbe@0 3677 ( "issue"."id"%TYPE,
jbe@0 3678 "member"."id"%TYPE,
jbe@0 3679 "delegating_voter"."delegate_member_ids"%TYPE )
jbe@0 3680 IS 'Helper function for "add_vote_delegations" function';
jbe@0 3681
jbe@0 3682
jbe@0 3683 CREATE FUNCTION "add_vote_delegations"
jbe@0 3684 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 3685 RETURNS VOID
jbe@0 3686 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 3687 DECLARE
jbe@0 3688 "member_id_v" "member"."id"%TYPE;
jbe@0 3689 BEGIN
jbe@336 3690 PERFORM "require_transaction_isolation"();
jbe@0 3691 FOR "member_id_v" IN
jbe@0 3692 SELECT "member_id" FROM "direct_voter"
jbe@0 3693 WHERE "issue_id" = "issue_id_p"
jbe@0 3694 LOOP
jbe@0 3695 UPDATE "direct_voter" SET
jbe@5 3696 "weight" = "weight" + "weight_of_added_vote_delegations"(
jbe@0 3697 "issue_id_p",
jbe@0 3698 "member_id_v",
jbe@0 3699 '{}'
jbe@0 3700 )
jbe@0 3701 WHERE "member_id" = "member_id_v"
jbe@0 3702 AND "issue_id" = "issue_id_p";
jbe@0 3703 END LOOP;
jbe@0 3704 RETURN;
jbe@0 3705 END;
jbe@0 3706 $$;
jbe@0 3707
jbe@0 3708 COMMENT ON FUNCTION "add_vote_delegations"
jbe@0 3709 ( "issue_id_p" "issue"."id"%TYPE )
jbe@0 3710 IS 'Helper function for "close_voting" function';
jbe@0 3711
jbe@0 3712
jbe@0 3713 CREATE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@0 3714 RETURNS VOID
jbe@0 3715 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 3716 DECLARE
jbe@97 3717 "area_id_v" "area"."id"%TYPE;
jbe@97 3718 "unit_id_v" "unit"."id"%TYPE;
jbe@0 3719 "member_id_v" "member"."id"%TYPE;
jbe@0 3720 BEGIN
jbe@333 3721 PERFORM "require_transaction_isolation"();
jbe@129 3722 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
jbe@129 3723 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
jbe@383 3724 -- override protection triggers:
jbe@385 3725 INSERT INTO "temporary_transaction_data" ("key", "value")
jbe@385 3726 VALUES ('override_protection_triggers', TRUE::TEXT);
jbe@285 3727 -- delete timestamp of voting comment:
jbe@285 3728 UPDATE "direct_voter" SET "comment_changed" = NULL
jbe@285 3729 WHERE "issue_id" = "issue_id_p";
jbe@169 3730 -- delete delegating votes (in cases of manual reset of issue state):
jbe@0 3731 DELETE FROM "delegating_voter"
jbe@0 3732 WHERE "issue_id" = "issue_id_p";
jbe@169 3733 -- delete votes from non-privileged voters:
jbe@97 3734 DELETE FROM "direct_voter"
jbe@97 3735 USING (
jbe@97 3736 SELECT
jbe@97 3737 "direct_voter"."member_id"
jbe@97 3738 FROM "direct_voter"
jbe@97 3739 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
jbe@97 3740 LEFT JOIN "privilege"
jbe@97 3741 ON "privilege"."unit_id" = "unit_id_v"
jbe@97 3742 AND "privilege"."member_id" = "direct_voter"."member_id"
jbe@97 3743 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
jbe@97 3744 "member"."active" = FALSE OR
jbe@97 3745 "privilege"."voting_right" ISNULL OR
jbe@97 3746 "privilege"."voting_right" = FALSE
jbe@97 3747 )
jbe@97 3748 ) AS "subquery"
jbe@97 3749 WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@97 3750 AND "direct_voter"."member_id" = "subquery"."member_id";
jbe@169 3751 -- consider delegations:
jbe@0 3752 UPDATE "direct_voter" SET "weight" = 1
jbe@0 3753 WHERE "issue_id" = "issue_id_p";
jbe@0 3754 PERFORM "add_vote_delegations"("issue_id_p");
jbe@385 3755 -- finish overriding protection triggers (avoids garbage):
jbe@385 3756 DELETE FROM "temporary_transaction_data"
jbe@385 3757 WHERE "key" = 'override_protection_triggers';
jbe@137 3758 -- materialize battle_view:
jbe@61 3759 -- NOTE: "closed" column of issue must be set at this point
jbe@61 3760 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@61 3761 INSERT INTO "battle" (
jbe@61 3762 "issue_id",
jbe@61 3763 "winning_initiative_id", "losing_initiative_id",
jbe@61 3764 "count"
jbe@61 3765 ) SELECT
jbe@61 3766 "issue_id",
jbe@61 3767 "winning_initiative_id", "losing_initiative_id",
jbe@61 3768 "count"
jbe@61 3769 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@331 3770 -- set voter count:
jbe@331 3771 UPDATE "issue" SET
jbe@331 3772 "voter_count" = (
jbe@331 3773 SELECT coalesce(sum("weight"), 0)
jbe@331 3774 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@331 3775 )
jbe@331 3776 WHERE "id" = "issue_id_p";
jbe@155 3777 -- copy "positive_votes" and "negative_votes" from "battle" table:
jbe@155 3778 UPDATE "initiative" SET
jbe@155 3779 "positive_votes" = "battle_win"."count",
jbe@155 3780 "negative_votes" = "battle_lose"."count"
jbe@155 3781 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
jbe@155 3782 WHERE
jbe@155 3783 "battle_win"."issue_id" = "issue_id_p" AND
jbe@155 3784 "battle_win"."winning_initiative_id" = "initiative"."id" AND
jbe@155 3785 "battle_win"."losing_initiative_id" ISNULL AND
jbe@155 3786 "battle_lose"."issue_id" = "issue_id_p" AND
jbe@155 3787 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
jbe@155 3788 "battle_lose"."winning_initiative_id" ISNULL;
jbe@0 3789 END;
jbe@0 3790 $$;
jbe@0 3791
jbe@0 3792 COMMENT ON FUNCTION "close_voting"
jbe@0 3793 ( "issue"."id"%TYPE )
jbe@0 3794 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 3795
jbe@0 3796
jbe@30 3797 CREATE FUNCTION "defeat_strength"
jbe@30 3798 ( "positive_votes_p" INT4, "negative_votes_p" INT4 )
jbe@30 3799 RETURNS INT8
jbe@30 3800 LANGUAGE 'plpgsql' IMMUTABLE AS $$
jbe@30 3801 BEGIN
jbe@30 3802 IF "positive_votes_p" > "negative_votes_p" THEN
jbe@30 3803 RETURN ("positive_votes_p"::INT8 << 31) - "negative_votes_p"::INT8;
jbe@30 3804 ELSIF "positive_votes_p" = "negative_votes_p" THEN
jbe@30 3805 RETURN 0;
jbe@30 3806 ELSE
jbe@30 3807 RETURN -1;
jbe@30 3808 END IF;
jbe@30 3809 END;
jbe@30 3810 $$;
jbe@30 3811
jbe@30 3812 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 3813
jbe@30 3814
jbe@0 3815 CREATE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
jbe@0 3816 RETURNS VOID
jbe@0 3817 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 3818 DECLARE
jbe@155 3819 "issue_row" "issue"%ROWTYPE;
jbe@155 3820 "policy_row" "policy"%ROWTYPE;
jbe@134 3821 "dimension_v" INTEGER;
jbe@134 3822 "vote_matrix" INT4[][]; -- absolute votes
jbe@134 3823 "matrix" INT8[][]; -- defeat strength / best paths
jbe@134 3824 "i" INTEGER;
jbe@134 3825 "j" INTEGER;
jbe@134 3826 "k" INTEGER;
jbe@134 3827 "battle_row" "battle"%ROWTYPE;
jbe@134 3828 "rank_ary" INT4[];
jbe@134 3829 "rank_v" INT4;
jbe@134 3830 "done_v" INTEGER;
jbe@134 3831 "winners_ary" INTEGER[];
jbe@134 3832 "initiative_id_v" "initiative"."id"%TYPE;
jbe@0 3833 BEGIN
jbe@333 3834 PERFORM "require_transaction_isolation"();
jbe@155 3835 SELECT * INTO "issue_row"
jbe@331 3836 FROM "issue" WHERE "id" = "issue_id_p";
jbe@155 3837 SELECT * INTO "policy_row"
jbe@155 3838 FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@126 3839 SELECT count(1) INTO "dimension_v"
jbe@126 3840 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
jbe@170 3841 -- Create "vote_matrix" with absolute number of votes in pairwise
jbe@170 3842 -- comparison:
jbe@170 3843 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
jbe@170 3844 "i" := 1;
jbe@170 3845 "j" := 2;
jbe@170 3846 FOR "battle_row" IN
jbe@170 3847 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
jbe@170 3848 ORDER BY
jbe@170 3849 "winning_initiative_id" NULLS LAST,
jbe@170 3850 "losing_initiative_id" NULLS LAST
jbe@170 3851 LOOP
jbe@170 3852 "vote_matrix"["i"]["j"] := "battle_row"."count";
jbe@170 3853 IF "j" = "dimension_v" THEN
jbe@170 3854 "i" := "i" + 1;
jbe@170 3855 "j" := 1;
jbe@170 3856 ELSE
jbe@170 3857 "j" := "j" + 1;
jbe@170 3858 IF "j" = "i" THEN
jbe@170 3859 "j" := "j" + 1;
jbe@170 3860 END IF;
jbe@170 3861 END IF;
jbe@170 3862 END LOOP;
jbe@170 3863 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
jbe@170 3864 RAISE EXCEPTION 'Wrong battle count (should not happen)';
jbe@170 3865 END IF;
jbe@170 3866 -- Store defeat strengths in "matrix" using "defeat_strength"
jbe@170 3867 -- function:
jbe@170 3868 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
jbe@170 3869 "i" := 1;
jbe@170 3870 LOOP
jbe@170 3871 "j" := 1;
jbe@0 3872 LOOP
jbe@170 3873 IF "i" != "j" THEN
jbe@170 3874 "matrix"["i"]["j"] := "defeat_strength"(
jbe@170 3875 "vote_matrix"["i"]["j"],
jbe@170 3876 "vote_matrix"["j"]["i"]
jbe@170 3877 );
jbe@0 3878 END IF;
jbe@170 3879 EXIT WHEN "j" = "dimension_v";
jbe@170 3880 "j" := "j" + 1;
jbe@0 3881 END LOOP;
jbe@170 3882 EXIT WHEN "i" = "dimension_v";
jbe@170 3883 "i" := "i" + 1;
jbe@170 3884 END LOOP;
jbe@170 3885 -- Find best paths:
jbe@170 3886 "i" := 1;
jbe@170 3887 LOOP
jbe@170 3888 "j" := 1;
jbe@170 3889 LOOP
jbe@170 3890 IF "i" != "j" THEN
jbe@170 3891 "k" := 1;
jbe@170 3892 LOOP
jbe@170 3893 IF "i" != "k" AND "j" != "k" THEN
jbe@170 3894 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
jbe@170 3895 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
jbe@170 3896 "matrix"["j"]["k"] := "matrix"["j"]["i"];
jbe@170 3897 END IF;
jbe@170 3898 ELSE
jbe@170 3899 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
jbe@170 3900 "matrix"["j"]["k"] := "matrix"["i"]["k"];
jbe@170 3901 END IF;
jbe@170 3902 END IF;
jbe@170 3903 END IF;
jbe@170 3904 EXIT WHEN "k" = "dimension_v";
jbe@170 3905 "k" := "k" + 1;
jbe@170 3906 END LOOP;
jbe@170 3907 END IF;
jbe@170 3908 EXIT WHEN "j" = "dimension_v";
jbe@170 3909 "j" := "j" + 1;
jbe@170 3910 END LOOP;
jbe@170 3911 EXIT WHEN "i" = "dimension_v";
jbe@170 3912 "i" := "i" + 1;
jbe@170 3913 END LOOP;
jbe@170 3914 -- Determine order of winners:
jbe@170 3915 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
jbe@170 3916 "rank_v" := 1;
jbe@170 3917 "done_v" := 0;
jbe@170 3918 LOOP
jbe@170 3919 "winners_ary" := '{}';
jbe@0 3920 "i" := 1;
jbe@0 3921 LOOP
jbe@170 3922 IF "rank_ary"["i"] ISNULL THEN
jbe@170 3923 "j" := 1;
jbe@170 3924 LOOP
jbe@170 3925 IF
jbe@170 3926 "i" != "j" AND
jbe@170 3927 "rank_ary"["j"] ISNULL AND
jbe@170 3928 "matrix"["j"]["i"] > "matrix"["i"]["j"]
jbe@170 3929 THEN
jbe@170 3930 -- someone else is better
jbe@170 3931 EXIT;
jbe@170 3932 END IF;
jbe@170 3933 IF "j" = "dimension_v" THEN
jbe@170 3934 -- noone is better
jbe@170 3935 "winners_ary" := "winners_ary" || "i";
jbe@170 3936 EXIT;
jbe@170 3937 END IF;
jbe@170 3938 "j" := "j" + 1;
jbe@170 3939 END LOOP;
jbe@170 3940 END IF;
jbe@30 3941 EXIT WHEN "i" = "dimension_v";
jbe@0 3942 "i" := "i" + 1;
jbe@0 3943 END LOOP;
jbe@0 3944 "i" := 1;
jbe@0 3945 LOOP
jbe@170 3946 "rank_ary"["winners_ary"["i"]] := "rank_v";
jbe@170 3947 "done_v" := "done_v" + 1;
jbe@170 3948 EXIT WHEN "i" = array_upper("winners_ary", 1);
jbe@0 3949 "i" := "i" + 1;
jbe@0 3950 END LOOP;
jbe@170 3951 EXIT WHEN "done_v" = "dimension_v";
jbe@170 3952 "rank_v" := "rank_v" + 1;
jbe@170 3953 END LOOP;
jbe@170 3954 -- write preliminary results:
jbe@170 3955 "i" := 1;
jbe@170 3956 FOR "initiative_id_v" IN
jbe@170 3957 SELECT "id" FROM "initiative"
jbe@170 3958 WHERE "issue_id" = "issue_id_p" AND "admitted"
jbe@170 3959 ORDER BY "id"
jbe@170 3960 LOOP
jbe@170 3961 UPDATE "initiative" SET
jbe@170 3962 "direct_majority" =
jbe@170 3963 CASE WHEN "policy_row"."direct_majority_strict" THEN
jbe@170 3964 "positive_votes" * "policy_row"."direct_majority_den" >
jbe@170 3965 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
jbe@170 3966 ELSE
jbe@170 3967 "positive_votes" * "policy_row"."direct_majority_den" >=
jbe@170 3968 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
jbe@170 3969 END
jbe@170 3970 AND "positive_votes" >= "policy_row"."direct_majority_positive"
jbe@170 3971 AND "issue_row"."voter_count"-"negative_votes" >=
jbe@170 3972 "policy_row"."direct_majority_non_negative",
jbe@170 3973 "indirect_majority" =
jbe@170 3974 CASE WHEN "policy_row"."indirect_majority_strict" THEN
jbe@170 3975 "positive_votes" * "policy_row"."indirect_majority_den" >
jbe@170 3976 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
jbe@170 3977 ELSE
jbe@170 3978 "positive_votes" * "policy_row"."indirect_majority_den" >=
jbe@170 3979 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
jbe@170 3980 END
jbe@170 3981 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
jbe@170 3982 AND "issue_row"."voter_count"-"negative_votes" >=
jbe@170 3983 "policy_row"."indirect_majority_non_negative",
jbe@171 3984 "schulze_rank" = "rank_ary"["i"],
jbe@170 3985 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
jbe@170 3986 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
jbe@170 3987 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
jbe@172 3988 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0,
jbe@216 3989 "eligible" = FALSE,
jbe@250 3990 "winner" = FALSE,
jbe@250 3991 "rank" = NULL -- NOTE: in cases of manual reset of issue state
jbe@170 3992 WHERE "id" = "initiative_id_v";
jbe@170 3993 "i" := "i" + 1;
jbe@170 3994 END LOOP;
jbe@170 3995 IF "i" != "dimension_v" THEN
jbe@170 3996 RAISE EXCEPTION 'Wrong winner count (should not happen)';
jbe@0 3997 END IF;
jbe@170 3998 -- take indirect majorities into account:
jbe@170 3999 LOOP
jbe@170 4000 UPDATE "initiative" SET "indirect_majority" = TRUE
jbe@139 4001 FROM (
jbe@170 4002 SELECT "new_initiative"."id" AS "initiative_id"
jbe@170 4003 FROM "initiative" "old_initiative"
jbe@170 4004 JOIN "initiative" "new_initiative"
jbe@170 4005 ON "new_initiative"."issue_id" = "issue_id_p"
jbe@170 4006 AND "new_initiative"."indirect_majority" = FALSE
jbe@139 4007 JOIN "battle" "battle_win"
jbe@139 4008 ON "battle_win"."issue_id" = "issue_id_p"
jbe@170 4009 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
jbe@170 4010 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
jbe@139 4011 JOIN "battle" "battle_lose"
jbe@139 4012 ON "battle_lose"."issue_id" = "issue_id_p"
jbe@170 4013 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
jbe@170 4014 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
jbe@170 4015 WHERE "old_initiative"."issue_id" = "issue_id_p"
jbe@170 4016 AND "old_initiative"."indirect_majority" = TRUE
jbe@170 4017 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
jbe@170 4018 "battle_win"."count" * "policy_row"."indirect_majority_den" >
jbe@170 4019 "policy_row"."indirect_majority_num" *
jbe@170 4020 ("battle_win"."count"+"battle_lose"."count")
jbe@170 4021 ELSE
jbe@170 4022 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
jbe@170 4023 "policy_row"."indirect_majority_num" *
jbe@170 4024 ("battle_win"."count"+"battle_lose"."count")
jbe@170 4025 END
jbe@170 4026 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
jbe@170 4027 AND "issue_row"."voter_count"-"battle_lose"."count" >=
jbe@170 4028 "policy_row"."indirect_majority_non_negative"
jbe@139 4029 ) AS "subquery"
jbe@139 4030 WHERE "id" = "subquery"."initiative_id";
jbe@170 4031 EXIT WHEN NOT FOUND;
jbe@170 4032 END LOOP;
jbe@170 4033 -- set "multistage_majority" for remaining matching initiatives:
jbe@216 4034 UPDATE "initiative" SET "multistage_majority" = TRUE
jbe@170 4035 FROM (
jbe@170 4036 SELECT "losing_initiative"."id" AS "initiative_id"
jbe@170 4037 FROM "initiative" "losing_initiative"
jbe@170 4038 JOIN "initiative" "winning_initiative"
jbe@170 4039 ON "winning_initiative"."issue_id" = "issue_id_p"
jbe@170 4040 AND "winning_initiative"."admitted"
jbe@170 4041 JOIN "battle" "battle_win"
jbe@170 4042 ON "battle_win"."issue_id" = "issue_id_p"
jbe@170 4043 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
jbe@170 4044 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
jbe@170 4045 JOIN "battle" "battle_lose"
jbe@170 4046 ON "battle_lose"."issue_id" = "issue_id_p"
jbe@170 4047 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
jbe@170 4048 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
jbe@170 4049 WHERE "losing_initiative"."issue_id" = "issue_id_p"
jbe@170 4050 AND "losing_initiative"."admitted"
jbe@170 4051 AND "winning_initiative"."schulze_rank" <
jbe@170 4052 "losing_initiative"."schulze_rank"
jbe@170 4053 AND "battle_win"."count" > "battle_lose"."count"
jbe@170 4054 AND (
jbe@170 4055 "battle_win"."count" > "winning_initiative"."positive_votes" OR
jbe@170 4056 "battle_lose"."count" < "losing_initiative"."negative_votes" )
jbe@170 4057 ) AS "subquery"
jbe@170 4058 WHERE "id" = "subquery"."initiative_id";
jbe@170 4059 -- mark eligible initiatives:
jbe@170 4060 UPDATE "initiative" SET "eligible" = TRUE
jbe@171 4061 WHERE "issue_id" = "issue_id_p"
jbe@171 4062 AND "initiative"."direct_majority"
jbe@171 4063 AND "initiative"."indirect_majority"
jbe@171 4064 AND "initiative"."better_than_status_quo"
jbe@171 4065 AND (
jbe@171 4066 "policy_row"."no_multistage_majority" = FALSE OR
jbe@171 4067 "initiative"."multistage_majority" = FALSE )
jbe@171 4068 AND (
jbe@171 4069 "policy_row"."no_reverse_beat_path" = FALSE OR
jbe@171 4070 "initiative"."reverse_beat_path" = FALSE );
jbe@170 4071 -- mark final winner:
jbe@170 4072 UPDATE "initiative" SET "winner" = TRUE
jbe@170 4073 FROM (
jbe@170 4074 SELECT "id" AS "initiative_id"
jbe@170 4075 FROM "initiative"
jbe@170 4076 WHERE "issue_id" = "issue_id_p" AND "eligible"
jbe@217 4077 ORDER BY
jbe@217 4078 "schulze_rank",
jbe@217 4079 "id"
jbe@170 4080 LIMIT 1
jbe@170 4081 ) AS "subquery"
jbe@170 4082 WHERE "id" = "subquery"."initiative_id";
jbe@173 4083 -- write (final) ranks:
jbe@173 4084 "rank_v" := 1;
jbe@173 4085 FOR "initiative_id_v" IN
jbe@173 4086 SELECT "id"
jbe@173 4087 FROM "initiative"
jbe@173 4088 WHERE "issue_id" = "issue_id_p" AND "admitted"
jbe@174 4089 ORDER BY
jbe@174 4090 "winner" DESC,
jbe@217 4091 "eligible" DESC,
jbe@174 4092 "schulze_rank",
jbe@174 4093 "id"
jbe@173 4094 LOOP
jbe@173 4095 UPDATE "initiative" SET "rank" = "rank_v"
jbe@173 4096 WHERE "id" = "initiative_id_v";
jbe@173 4097 "rank_v" := "rank_v" + 1;
jbe@173 4098 END LOOP;
jbe@170 4099 -- set schulze rank of status quo and mark issue as finished:
jbe@111 4100 UPDATE "issue" SET
jbe@170 4101 "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
jbe@111 4102 "state" =
jbe@139 4103 CASE WHEN EXISTS (
jbe@139 4104 SELECT NULL FROM "initiative"
jbe@139 4105 WHERE "issue_id" = "issue_id_p" AND "winner"
jbe@139 4106 ) THEN
jbe@139 4107 'finished_with_winner'::"issue_state"
jbe@139 4108 ELSE
jbe@121 4109 'finished_without_winner'::"issue_state"
jbe@111 4110 END,
jbe@331 4111 "closed" = "phase_finished",
jbe@331 4112 "phase_finished" = NULL
jbe@0 4113 WHERE "id" = "issue_id_p";
jbe@0 4114 RETURN;
jbe@0 4115 END;
jbe@0 4116 $$;
jbe@0 4117
jbe@0 4118 COMMENT ON FUNCTION "calculate_ranks"
jbe@0 4119 ( "issue"."id"%TYPE )
jbe@0 4120 IS 'Determine ranking (Votes have to be counted first)';
jbe@0 4121
jbe@0 4122
jbe@0 4123
jbe@0 4124 -----------------------------
jbe@0 4125 -- Automatic state changes --
jbe@0 4126 -----------------------------
jbe@0 4127
jbe@0 4128
jbe@331 4129 CREATE TYPE "check_issue_persistence" AS (
jbe@331 4130 "state" "issue_state",
jbe@331 4131 "phase_finished" BOOLEAN,
jbe@331 4132 "issue_revoked" BOOLEAN,
jbe@331 4133 "snapshot_created" BOOLEAN,
jbe@331 4134 "harmonic_weights_set" BOOLEAN,
jbe@331 4135 "closed_voting" BOOLEAN );
jbe@331 4136
jbe@336 4137 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 4138
jbe@336 4139
jbe@0 4140 CREATE FUNCTION "check_issue"
jbe@331 4141 ( "issue_id_p" "issue"."id"%TYPE,
jbe@331 4142 "persist" "check_issue_persistence" )
jbe@331 4143 RETURNS "check_issue_persistence"
jbe@0 4144 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 4145 DECLARE
jbe@336 4146 "issue_row" "issue"%ROWTYPE;
jbe@336 4147 "policy_row" "policy"%ROWTYPE;
jbe@336 4148 "initiative_row" "initiative"%ROWTYPE;
jbe@336 4149 "state_v" "issue_state";
jbe@0 4150 BEGIN
jbe@333 4151 PERFORM "require_transaction_isolation"();
jbe@331 4152 IF "persist" ISNULL THEN
jbe@331 4153 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@331 4154 FOR UPDATE;
jbe@331 4155 IF "issue_row"."closed" NOTNULL THEN
jbe@331 4156 RETURN NULL;
jbe@0 4157 END IF;
jbe@331 4158 "persist"."state" := "issue_row"."state";
jbe@331 4159 IF
jbe@331 4160 ( "issue_row"."state" = 'admission' AND now() >=
jbe@331 4161 "issue_row"."created" + "issue_row"."admission_time" ) OR
jbe@331 4162 ( "issue_row"."state" = 'discussion' AND now() >=
jbe@331 4163 "issue_row"."accepted" + "issue_row"."discussion_time" ) OR
jbe@331 4164 ( "issue_row"."state" = 'verification' AND now() >=
jbe@331 4165 "issue_row"."half_frozen" + "issue_row"."verification_time" ) OR
jbe@331 4166 ( "issue_row"."state" = 'voting' AND now() >=
jbe@331 4167 "issue_row"."fully_frozen" + "issue_row"."voting_time" )
jbe@331 4168 THEN
jbe@331 4169 "persist"."phase_finished" := TRUE;
jbe@331 4170 ELSE
jbe@331 4171 "persist"."phase_finished" := FALSE;
jbe@0 4172 END IF;
jbe@0 4173 IF
jbe@24 4174 NOT EXISTS (
jbe@24 4175 -- all initiatives are revoked
jbe@24 4176 SELECT NULL FROM "initiative"
jbe@24 4177 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@24 4178 ) AND (
jbe@111 4179 -- and issue has not been accepted yet
jbe@331 4180 "persist"."state" = 'admission' OR
jbe@331 4181 -- or verification time has elapsed
jbe@331 4182 ( "persist"."state" = 'verification' AND
jbe@331 4183 "persist"."phase_finished" ) OR
jbe@331 4184 -- or no initiatives have been revoked lately
jbe@24 4185 NOT EXISTS (
jbe@24 4186 SELECT NULL FROM "initiative"
jbe@24 4187 WHERE "issue_id" = "issue_id_p"
jbe@24 4188 AND now() < "revoked" + "issue_row"."verification_time"
jbe@24 4189 )
jbe@24 4190 )
jbe@24 4191 THEN
jbe@331 4192 "persist"."issue_revoked" := TRUE;
jbe@331 4193 ELSE
jbe@331 4194 "persist"."issue_revoked" := FALSE;
jbe@24 4195 END IF;
jbe@331 4196 IF "persist"."phase_finished" OR "persist"."issue_revoked" THEN
jbe@331 4197 UPDATE "issue" SET "phase_finished" = now()
jbe@331 4198 WHERE "id" = "issue_row"."id";
jbe@331 4199 RETURN "persist";
jbe@331 4200 ELSIF
jbe@331 4201 "persist"."state" IN ('admission', 'discussion', 'verification')
jbe@3 4202 THEN
jbe@331 4203 RETURN "persist";
jbe@331 4204 ELSE
jbe@331 4205 RETURN NULL;
jbe@322 4206 END IF;
jbe@0 4207 END IF;
jbe@331 4208 IF
jbe@331 4209 "persist"."state" IN ('admission', 'discussion', 'verification') AND
jbe@331 4210 coalesce("persist"."snapshot_created", FALSE) = FALSE
jbe@331 4211 THEN
jbe@331 4212 PERFORM "create_snapshot"("issue_id_p");
jbe@331 4213 "persist"."snapshot_created" = TRUE;
jbe@331 4214 IF "persist"."phase_finished" THEN
jbe@331 4215 IF "persist"."state" = 'admission' THEN
jbe@331 4216 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@331 4217 ELSIF "persist"."state" = 'discussion' THEN
jbe@331 4218 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
jbe@331 4219 ELSIF "persist"."state" = 'verification' THEN
jbe@331 4220 PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze');
jbe@336 4221 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@336 4222 SELECT * INTO "policy_row" FROM "policy"
jbe@336 4223 WHERE "id" = "issue_row"."policy_id";
jbe@336 4224 FOR "initiative_row" IN
jbe@336 4225 SELECT * FROM "initiative"
jbe@336 4226 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@336 4227 FOR UPDATE
jbe@336 4228 LOOP
jbe@336 4229 IF
jbe@336 4230 "initiative_row"."polling" OR (
jbe@336 4231 "initiative_row"."satisfied_supporter_count" > 0 AND
jbe@336 4232 "initiative_row"."satisfied_supporter_count" *
jbe@336 4233 "policy_row"."initiative_quorum_den" >=
jbe@336 4234 "issue_row"."population" * "policy_row"."initiative_quorum_num"
jbe@336 4235 )
jbe@336 4236 THEN
jbe@336 4237 UPDATE "initiative" SET "admitted" = TRUE
jbe@336 4238 WHERE "id" = "initiative_row"."id";
jbe@336 4239 ELSE
jbe@336 4240 UPDATE "initiative" SET "admitted" = FALSE
jbe@336 4241 WHERE "id" = "initiative_row"."id";
jbe@336 4242 END IF;
jbe@336 4243 END LOOP;
jbe@331 4244 END IF;
jbe@331 4245 END IF;
jbe@331 4246 RETURN "persist";
jbe@331 4247 END IF;
jbe@331 4248 IF
jbe@331 4249 "persist"."state" IN ('admission', 'discussion', 'verification') AND
jbe@331 4250 coalesce("persist"."harmonic_weights_set", FALSE) = FALSE
jbe@331 4251 THEN
jbe@331 4252 PERFORM "set_harmonic_initiative_weights"("issue_id_p");
jbe@331 4253 "persist"."harmonic_weights_set" = TRUE;
jbe@332 4254 IF
jbe@332 4255 "persist"."phase_finished" OR
jbe@332 4256 "persist"."issue_revoked" OR
jbe@332 4257 "persist"."state" = 'admission'
jbe@332 4258 THEN
jbe@331 4259 RETURN "persist";
jbe@331 4260 ELSE
jbe@331 4261 RETURN NULL;
jbe@331 4262 END IF;
jbe@331 4263 END IF;
jbe@331 4264 IF "persist"."issue_revoked" THEN
jbe@331 4265 IF "persist"."state" = 'admission' THEN
jbe@331 4266 "state_v" := 'canceled_revoked_before_accepted';
jbe@331 4267 ELSIF "persist"."state" = 'discussion' THEN
jbe@331 4268 "state_v" := 'canceled_after_revocation_during_discussion';
jbe@331 4269 ELSIF "persist"."state" = 'verification' THEN
jbe@331 4270 "state_v" := 'canceled_after_revocation_during_verification';
jbe@331 4271 END IF;
jbe@331 4272 UPDATE "issue" SET
jbe@331 4273 "state" = "state_v",
jbe@331 4274 "closed" = "phase_finished",
jbe@331 4275 "phase_finished" = NULL
jbe@332 4276 WHERE "id" = "issue_id_p";
jbe@331 4277 RETURN NULL;
jbe@331 4278 END IF;
jbe@331 4279 IF "persist"."state" = 'admission' THEN
jbe@336 4280 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@336 4281 FOR UPDATE;
jbe@336 4282 SELECT * INTO "policy_row"
jbe@336 4283 FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@336 4284 IF EXISTS (
jbe@336 4285 SELECT NULL FROM "initiative"
jbe@336 4286 WHERE "issue_id" = "issue_id_p"
jbe@336 4287 AND "supporter_count" > 0
jbe@336 4288 AND "supporter_count" * "policy_row"."issue_quorum_den"
jbe@336 4289 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
jbe@336 4290 ) THEN
jbe@336 4291 UPDATE "issue" SET
jbe@336 4292 "state" = 'discussion',
jbe@336 4293 "accepted" = coalesce("phase_finished", now()),
jbe@336 4294 "phase_finished" = NULL
jbe@336 4295 WHERE "id" = "issue_id_p";
jbe@336 4296 ELSIF "issue_row"."phase_finished" NOTNULL THEN
jbe@336 4297 UPDATE "issue" SET
jbe@336 4298 "state" = 'canceled_issue_not_accepted',
jbe@336 4299 "closed" = "phase_finished",
jbe@336 4300 "phase_finished" = NULL
jbe@336 4301 WHERE "id" = "issue_id_p";
jbe@336 4302 END IF;
jbe@331 4303 RETURN NULL;
jbe@331 4304 END IF;
jbe@332 4305 IF "persist"."phase_finished" THEN
jbe@332 4306 if "persist"."state" = 'discussion' THEN
jbe@332 4307 UPDATE "issue" SET
jbe@332 4308 "state" = 'verification',
jbe@332 4309 "half_frozen" = "phase_finished",
jbe@332 4310 "phase_finished" = NULL
jbe@332 4311 WHERE "id" = "issue_id_p";
jbe@332 4312 RETURN NULL;
jbe@332 4313 END IF;
jbe@332 4314 IF "persist"."state" = 'verification' THEN
jbe@336 4315 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"
jbe@336 4316 FOR UPDATE;
jbe@336 4317 SELECT * INTO "policy_row" FROM "policy"
jbe@336 4318 WHERE "id" = "issue_row"."policy_id";
jbe@336 4319 IF EXISTS (
jbe@336 4320 SELECT NULL FROM "initiative"
jbe@336 4321 WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE
jbe@336 4322 ) THEN
jbe@336 4323 UPDATE "issue" SET
jbe@343 4324 "state" = 'voting',
jbe@343 4325 "fully_frozen" = "phase_finished",
jbe@336 4326 "phase_finished" = NULL
jbe@336 4327 WHERE "id" = "issue_id_p";
jbe@336 4328 ELSE
jbe@336 4329 UPDATE "issue" SET
jbe@343 4330 "state" = 'canceled_no_initiative_admitted',
jbe@343 4331 "fully_frozen" = "phase_finished",
jbe@343 4332 "closed" = "phase_finished",
jbe@343 4333 "phase_finished" = NULL
jbe@336 4334 WHERE "id" = "issue_id_p";
jbe@336 4335 -- NOTE: The following DELETE statements have effect only when
jbe@336 4336 -- issue state has been manipulated
jbe@336 4337 DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p";
jbe@336 4338 DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p";
jbe@336 4339 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@336 4340 END IF;
jbe@332 4341 RETURN NULL;
jbe@332 4342 END IF;
jbe@332 4343 IF "persist"."state" = 'voting' THEN
jbe@332 4344 IF coalesce("persist"."closed_voting", FALSE) = FALSE THEN
jbe@332 4345 PERFORM "close_voting"("issue_id_p");
jbe@332 4346 "persist"."closed_voting" = TRUE;
jbe@332 4347 RETURN "persist";
jbe@332 4348 END IF;
jbe@332 4349 PERFORM "calculate_ranks"("issue_id_p");
jbe@332 4350 RETURN NULL;
jbe@332 4351 END IF;
jbe@331 4352 END IF;
jbe@331 4353 RAISE WARNING 'should not happen';
jbe@331 4354 RETURN NULL;
jbe@0 4355 END;
jbe@0 4356 $$;
jbe@0 4357
jbe@0 4358 COMMENT ON FUNCTION "check_issue"
jbe@331 4359 ( "issue"."id"%TYPE,
jbe@331 4360 "check_issue_persistence" )
jbe@336 4361 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 4362
jbe@0 4363
jbe@0 4364 CREATE FUNCTION "check_everything"()
jbe@0 4365 RETURNS VOID
jbe@0 4366 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@0 4367 DECLARE
jbe@0 4368 "issue_id_v" "issue"."id"%TYPE;
jbe@331 4369 "persist_v" "check_issue_persistence";
jbe@0 4370 BEGIN
jbe@333 4371 RAISE WARNING 'Function "check_everything" should only be used for development and debugging purposes';
jbe@235 4372 DELETE FROM "expired_session";
jbe@184 4373 PERFORM "check_activity"();
jbe@4 4374 PERFORM "calculate_member_counts"();
jbe@4 4375 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
jbe@331 4376 "persist_v" := NULL;
jbe@331 4377 LOOP
jbe@331 4378 "persist_v" := "check_issue"("issue_id_v", "persist_v");
jbe@331 4379 EXIT WHEN "persist_v" ISNULL;
jbe@331 4380 END LOOP;
jbe@0 4381 END LOOP;
jbe@0 4382 RETURN;
jbe@0 4383 END;
jbe@0 4384 $$;
jbe@0 4385
jbe@336 4386 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 4387
jbe@0 4388
jbe@0 4389
jbe@59 4390 ----------------------
jbe@59 4391 -- Deletion of data --
jbe@59 4392 ----------------------
jbe@59 4393
jbe@59 4394
jbe@59 4395 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
jbe@59 4396 RETURNS VOID
jbe@59 4397 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@59 4398 BEGIN
jbe@385 4399 IF EXISTS (
jbe@385 4400 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
jbe@385 4401 ) THEN
jbe@385 4402 -- override protection triggers:
jbe@385 4403 INSERT INTO "temporary_transaction_data" ("key", "value")
jbe@385 4404 VALUES ('override_protection_triggers', TRUE::TEXT);
jbe@385 4405 -- clean data:
jbe@59 4406 DELETE FROM "delegating_voter"
jbe@59 4407 WHERE "issue_id" = "issue_id_p";
jbe@59 4408 DELETE FROM "direct_voter"
jbe@59 4409 WHERE "issue_id" = "issue_id_p";
jbe@59 4410 DELETE FROM "delegating_interest_snapshot"
jbe@59 4411 WHERE "issue_id" = "issue_id_p";
jbe@59 4412 DELETE FROM "direct_interest_snapshot"
jbe@59 4413 WHERE "issue_id" = "issue_id_p";
jbe@59 4414 DELETE FROM "delegating_population_snapshot"
jbe@59 4415 WHERE "issue_id" = "issue_id_p";
jbe@59 4416 DELETE FROM "direct_population_snapshot"
jbe@59 4417 WHERE "issue_id" = "issue_id_p";
jbe@113 4418 DELETE FROM "non_voter"
jbe@94 4419 WHERE "issue_id" = "issue_id_p";
jbe@59 4420 DELETE FROM "delegation"
jbe@59 4421 WHERE "issue_id" = "issue_id_p";
jbe@59 4422 DELETE FROM "supporter"
jbe@329 4423 USING "initiative" -- NOTE: due to missing index on issue_id
jbe@325 4424 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@325 4425 AND "supporter"."initiative_id" = "initiative_id";
jbe@385 4426 -- mark issue as cleaned:
jbe@385 4427 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
jbe@385 4428 -- finish overriding protection triggers (avoids garbage):
jbe@385 4429 DELETE FROM "temporary_transaction_data"
jbe@385 4430 WHERE "key" = 'override_protection_triggers';
jbe@59 4431 END IF;
jbe@59 4432 RETURN;
jbe@59 4433 END;
jbe@59 4434 $$;
jbe@59 4435
jbe@59 4436 COMMENT ON FUNCTION "clean_issue"("issue"."id"%TYPE) IS 'Delete discussion data and votes belonging to an issue';
jbe@8 4437
jbe@8 4438
jbe@54 4439 CREATE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
jbe@8 4440 RETURNS VOID
jbe@8 4441 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@8 4442 BEGIN
jbe@9 4443 UPDATE "member" SET
jbe@57 4444 "last_login" = NULL,
jbe@387 4445 "last_delegation_check" = NULL,
jbe@45 4446 "login" = NULL,
jbe@11 4447 "password" = NULL,
jbe@101 4448 "locked" = TRUE,
jbe@54 4449 "active" = FALSE,
jbe@11 4450 "notify_email" = NULL,
jbe@11 4451 "notify_email_unconfirmed" = NULL,
jbe@11 4452 "notify_email_secret" = NULL,
jbe@11 4453 "notify_email_secret_expiry" = NULL,
jbe@57 4454 "notify_email_lock_expiry" = NULL,
jbe@387 4455 "login_recovery_expiry" = NULL,
jbe@11 4456 "password_reset_secret" = NULL,
jbe@11 4457 "password_reset_secret_expiry" = NULL,
jbe@11 4458 "organizational_unit" = NULL,
jbe@11 4459 "internal_posts" = NULL,
jbe@11 4460 "realname" = NULL,
jbe@11 4461 "birthday" = NULL,
jbe@11 4462 "address" = NULL,
jbe@11 4463 "email" = NULL,
jbe@11 4464 "xmpp_address" = NULL,
jbe@11 4465 "website" = NULL,
jbe@11 4466 "phone" = NULL,
jbe@11 4467 "mobile_phone" = NULL,
jbe@11 4468 "profession" = NULL,
jbe@11 4469 "external_memberships" = NULL,
jbe@11 4470 "external_posts" = NULL,
jbe@45 4471 "statement" = NULL
jbe@45 4472 WHERE "id" = "member_id_p";
jbe@11 4473 -- "text_search_data" is updated by triggers
jbe@45 4474 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
jbe@45 4475 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
jbe@45 4476 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
jbe@45 4477 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
jbe@45 4478 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
jbe@113 4479 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
jbe@235 4480 DELETE FROM "session" WHERE "member_id" = "member_id_p";
jbe@45 4481 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
jbe@45 4482 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
jbe@113 4483 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
jbe@45 4484 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
jbe@45 4485 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
jbe@54 4486 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
jbe@54 4487 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
jbe@113 4488 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
jbe@57 4489 DELETE FROM "direct_voter" USING "issue"
jbe@57 4490 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@57 4491 AND "issue"."closed" ISNULL
jbe@57 4492 AND "member_id" = "member_id_p";
jbe@45 4493 RETURN;
jbe@45 4494 END;
jbe@45 4495 $$;
jbe@45 4496
jbe@57 4497 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 4498
jbe@45 4499
jbe@45 4500 CREATE FUNCTION "delete_private_data"()
jbe@45 4501 RETURNS VOID
jbe@45 4502 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@45 4503 BEGIN
jbe@385 4504 DELETE FROM "temporary_transaction_data";
jbe@226 4505 DELETE FROM "member" WHERE "activated" ISNULL;
jbe@50 4506 UPDATE "member" SET
jbe@206 4507 "invite_code" = NULL,
jbe@232 4508 "invite_code_expiry" = NULL,
jbe@228 4509 "admin_comment" = NULL,
jbe@57 4510 "last_login" = NULL,
jbe@387 4511 "last_delegation_check" = NULL,
jbe@50 4512 "login" = NULL,
jbe@50 4513 "password" = NULL,
jbe@238 4514 "lang" = NULL,
jbe@50 4515 "notify_email" = NULL,
jbe@50 4516 "notify_email_unconfirmed" = NULL,
jbe@50 4517 "notify_email_secret" = NULL,
jbe@50 4518 "notify_email_secret_expiry" = NULL,
jbe@57 4519 "notify_email_lock_expiry" = NULL,
jbe@238 4520 "notify_level" = NULL,
jbe@387 4521 "login_recovery_expiry" = NULL,
jbe@50 4522 "password_reset_secret" = NULL,
jbe@50 4523 "password_reset_secret_expiry" = NULL,
jbe@50 4524 "organizational_unit" = NULL,
jbe@50 4525 "internal_posts" = NULL,
jbe@50 4526 "realname" = NULL,
jbe@50 4527 "birthday" = NULL,
jbe@50 4528 "address" = NULL,
jbe@50 4529 "email" = NULL,
jbe@50 4530 "xmpp_address" = NULL,
jbe@50 4531 "website" = NULL,
jbe@50 4532 "phone" = NULL,
jbe@50 4533 "mobile_phone" = NULL,
jbe@50 4534 "profession" = NULL,
jbe@50 4535 "external_memberships" = NULL,
jbe@50 4536 "external_posts" = NULL,
jbe@238 4537 "formatting_engine" = NULL,
jbe@50 4538 "statement" = NULL;
jbe@50 4539 -- "text_search_data" is updated by triggers
jbe@50 4540 DELETE FROM "setting";
jbe@50 4541 DELETE FROM "setting_map";
jbe@50 4542 DELETE FROM "member_relation_setting";
jbe@50 4543 DELETE FROM "member_image";
jbe@50 4544 DELETE FROM "contact";
jbe@113 4545 DELETE FROM "ignored_member";
jbe@235 4546 DELETE FROM "session";
jbe@50 4547 DELETE FROM "area_setting";
jbe@50 4548 DELETE FROM "issue_setting";
jbe@113 4549 DELETE FROM "ignored_initiative";
jbe@50 4550 DELETE FROM "initiative_setting";
jbe@50 4551 DELETE FROM "suggestion_setting";
jbe@113 4552 DELETE FROM "non_voter";
jbe@8 4553 DELETE FROM "direct_voter" USING "issue"
jbe@8 4554 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@8 4555 AND "issue"."closed" ISNULL;
jbe@8 4556 RETURN;
jbe@8 4557 END;
jbe@8 4558 $$;
jbe@8 4559
jbe@273 4560 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 4561
jbe@8 4562
jbe@8 4563
jbe@0 4564 COMMIT;

Impressum / About Us