liquid_feedback_core

annotate core.sql @ 268:739ed2d3d372

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

Impressum / About Us