liquid_feedback_core

annotate core.sql @ 207:73d358fe20c7

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

Impressum / About Us