liquid_feedback_core

annotate core.sql @ 231:61e20e161e9b

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

Impressum / About Us