liquid_feedback_core

annotate core.sql @ 387:ae69cf82c05f

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

Impressum / About Us