liquid_feedback_core

annotate update/core-update.v1.4.0-v2.0.0.sql @ 378:e88d0606891f

find changesets by author, revision, files, or words in the commit message
Bugfix regarding "proportional_order" of suggestions:
Use NULL values explicitly to be sorted last
(includes new suggestions as well as suggestions without any individual rankings)
author jbe
date Mon Mar 18 09:36:21 2013 +0100 (2013-03-18)
parents d4c754c4aeaa
children
rev   line source
jbe@178 1 SELECT "calculate_ranks"("id") FROM "issue_with_ranks_missing";
jbe@178 2
jbe@177 3 BEGIN;
jbe@177 4
jbe@177 5 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@193 6 SELECT * FROM (VALUES ('2.0.0', 2, 0, 0))
jbe@177 7 AS "subquery"("string", "major", "minor", "revision");
jbe@177 8
jbe@182 9 ALTER TABLE "member" ADD COLUMN "invite_code" TEXT UNIQUE;
jbe@182 10 ALTER TABLE "member" ADD COLUMN "admin_comment" TEXT;
jbe@182 11 ALTER TABLE "member" ADD COLUMN "activated" TIMESTAMPTZ;
jbe@184 12 ALTER TABLE "member" ADD COLUMN "last_activity" DATE;
jbe@184 13 ALTER TABLE "member" DROP COLUMN "last_login_public";
jbe@182 14 ALTER TABLE "member" ALTER COLUMN "active" SET DEFAULT FALSE;
jbe@177 15 ALTER TABLE "member" ADD COLUMN "formatting_engine" TEXT;
jbe@182 16
jbe@246 17 -- Backported fix of future version to include unused invite codes in member table:
jbe@246 18 ALTER TABLE "member" ALTER COLUMN "name" DROP NOT NULL;
jbe@246 19
jbe@182 20 COMMENT ON COLUMN "member"."created" IS 'Creation of member record and/or invite code';
jbe@182 21 COMMENT ON COLUMN "member"."invite_code" IS 'Optional invite code, to allow a member to initialize his/her account the first time';
jbe@186 22 COMMENT ON COLUMN "member"."admin_comment" IS 'Hidden comment for administrative purposes';
jbe@184 23 COMMENT ON COLUMN "member"."activated" IS 'Timestamp of activation of account (i.e. usage of "invite_code"); required to be set for "active" members';
jbe@184 24 COMMENT ON COLUMN "member"."last_activity" IS 'Date of last activity of member; required to be set for "active" members';
jbe@184 25 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@177 26 COMMENT ON COLUMN "member"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "member"."statement"';
jbe@177 27
jbe@185 28 CREATE TYPE "application_access_level" AS ENUM
jbe@185 29 ('member', 'full', 'pseudonymous', 'anonymous');
jbe@185 30
jbe@185 31 COMMENT ON TYPE "application_access_level" IS 'Access privileges for applications using the API';
jbe@185 32
jbe@185 33 CREATE TABLE "member_application" (
jbe@185 34 "id" SERIAL8 PRIMARY KEY,
jbe@185 35 UNIQUE ("member_id", "name"),
jbe@185 36 "member_id" INT4 NOT NULL REFERENCES "member" ("id")
jbe@185 37 ON DELETE CASCADE ON UPDATE CASCADE,
jbe@185 38 "name" TEXT NOT NULL,
jbe@185 39 "comment" TEXT,
jbe@185 40 "access_level" "application_access_level" NOT NULL,
jbe@190 41 "key" TEXT NOT NULL UNIQUE,
jbe@185 42 "last_usage" TIMESTAMPTZ );
jbe@185 43
jbe@185 44 COMMENT ON TABLE "member_application" IS 'Registered application being allowed to use the API';
jbe@185 45
jbe@177 46 CREATE TABLE "rendered_member_statement" (
jbe@177 47 PRIMARY KEY ("member_id", "format"),
jbe@177 48 "member_id" INT8 REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@177 49 "format" TEXT,
jbe@177 50 "content" TEXT NOT NULL );
jbe@177 51
jbe@177 52 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@177 53
jbe@183 54 DROP VIEW "expired_session";
jbe@183 55 DROP TABLE "session";
jbe@183 56
jbe@177 57 ALTER TABLE "policy" ADD COLUMN "direct_majority_num" INT4 NOT NULL DEFAULT 1;
jbe@177 58 ALTER TABLE "policy" ADD COLUMN "direct_majority_den" INT4 NOT NULL DEFAULT 2;
jbe@177 59 ALTER TABLE "policy" ADD COLUMN "direct_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE;
jbe@177 60 ALTER TABLE "policy" ADD COLUMN "direct_majority_positive" INT4 NOT NULL DEFAULT 0;
jbe@177 61 ALTER TABLE "policy" ADD COLUMN "direct_majority_non_negative" INT4 NOT NULL DEFAULT 0;
jbe@177 62 ALTER TABLE "policy" ADD COLUMN "indirect_majority_num" INT4 NOT NULL DEFAULT 1;
jbe@177 63 ALTER TABLE "policy" ADD COLUMN "indirect_majority_den" INT4 NOT NULL DEFAULT 2;
jbe@177 64 ALTER TABLE "policy" ADD COLUMN "indirect_majority_strict" BOOLEAN NOT NULL DEFAULT TRUE;
jbe@177 65 ALTER TABLE "policy" ADD COLUMN "indirect_majority_positive" INT4 NOT NULL DEFAULT 0;
jbe@177 66 ALTER TABLE "policy" ADD COLUMN "indirect_majority_non_negative" INT4 NOT NULL DEFAULT 0;
jbe@177 67 ALTER TABLE "policy" ADD COLUMN "no_reverse_beat_path" BOOLEAN NOT NULL DEFAULT TRUE;
jbe@177 68 ALTER TABLE "policy" ADD COLUMN "no_multistage_majority" BOOLEAN NOT NULL DEFAULT FALSE;
jbe@177 69
jbe@177 70 UPDATE "policy" SET
jbe@177 71 "direct_majority_num" = "majority_num",
jbe@177 72 "direct_majority_den" = "majority_den",
jbe@177 73 "direct_majority_strict" = "majority_strict",
jbe@177 74 "indirect_majority_num" = "majority_num",
jbe@177 75 "indirect_majority_den" = "majority_den",
jbe@177 76 "indirect_majority_strict" = "majority_strict";
jbe@177 77
jbe@177 78 ALTER TABLE "policy" DROP COLUMN "majority_num";
jbe@177 79 ALTER TABLE "policy" DROP COLUMN "majority_den";
jbe@177 80 ALTER TABLE "policy" DROP COLUMN "majority_strict";
jbe@177 81
jbe@177 82 COMMENT ON COLUMN "policy"."direct_majority_num" IS 'Numerator of fraction of neccessary direct majority for initiatives to be attainable as winner';
jbe@177 83 COMMENT ON COLUMN "policy"."direct_majority_den" IS 'Denominator of fraction of neccessary direct majority for initaitives to be attainable as winner';
jbe@177 84 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@177 85 COMMENT ON COLUMN "policy"."direct_majority_positive" IS 'Absolute number of "positive_votes" neccessary for an initiative to be attainable as winner';
jbe@177 86 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@177 87 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@177 88 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@177 89 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@177 90 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@177 91 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@177 92 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@177 93 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@177 94
jbe@177 95 ALTER TABLE "area" DROP COLUMN "autoreject_weight";
jbe@177 96
jbe@177 97 DROP VIEW "open_issue";
jbe@177 98 DROP VIEW "issue_with_ranks_missing";
jbe@177 99
jbe@177 100 ALTER TABLE "issue" DROP COLUMN "vote_now";
jbe@177 101 ALTER TABLE "issue" DROP COLUMN "vote_later";
jbe@177 102 ALTER TABLE "issue" ADD COLUMN "status_quo_schulze_rank" INT4;
jbe@177 103
jbe@177 104 CREATE VIEW "open_issue" AS
jbe@177 105 SELECT * FROM "issue" WHERE "closed" ISNULL;
jbe@177 106
jbe@177 107 COMMENT ON VIEW "open_issue" IS 'All open issues';
jbe@177 108
jbe@177 109 CREATE VIEW "issue_with_ranks_missing" AS
jbe@177 110 SELECT * FROM "issue"
jbe@177 111 WHERE "fully_frozen" NOTNULL
jbe@177 112 AND "closed" NOTNULL
jbe@177 113 AND "ranks_available" = FALSE;
jbe@177 114
jbe@177 115 COMMENT ON VIEW "issue_with_ranks_missing" IS 'Issues where voting was finished, but no ranks have been calculated yet';
jbe@177 116
jbe@177 117 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@177 118 COMMENT ON COLUMN "issue"."snapshot" IS 'Point in time, when snapshot tables have been updated and "population" and *_count values were precalculated';
jbe@177 119 COMMENT ON COLUMN "issue"."status_quo_schulze_rank" IS 'Schulze rank of status quo, as calculated by "calculate_ranks" function';
jbe@177 120
jbe@179 121 DROP VIEW "battle_view";
jbe@179 122
jbe@180 123 ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results";
jbe@180 124 ALTER TABLE "initiative" DROP CONSTRAINT "all_or_none_of_positive_votes_negative_votes_and_agreed_must_be_null";
jbe@180 125 ALTER TABLE "initiative" DROP CONSTRAINT "non_agreed_initiatives_cant_get_a_rank";
jbe@180 126
jbe@178 127 ALTER TABLE "initiative" DROP COLUMN "agreed";
jbe@177 128 ALTER TABLE "initiative" ADD COLUMN "direct_majority" BOOLEAN;
jbe@177 129 ALTER TABLE "initiative" ADD COLUMN "indirect_majority" BOOLEAN;
jbe@177 130 ALTER TABLE "initiative" ADD COLUMN "schulze_rank" INT4;
jbe@177 131 ALTER TABLE "initiative" ADD COLUMN "better_than_status_quo" BOOLEAN;
jbe@177 132 ALTER TABLE "initiative" ADD COLUMN "worse_than_status_quo" BOOLEAN;
jbe@177 133 ALTER TABLE "initiative" ADD COLUMN "reverse_beat_path" BOOLEAN;
jbe@177 134 ALTER TABLE "initiative" ADD COLUMN "multistage_majority" BOOLEAN;
jbe@177 135 ALTER TABLE "initiative" ADD COLUMN "eligible" BOOLEAN;
jbe@177 136 ALTER TABLE "initiative" ADD COLUMN "winner" BOOLEAN;
jbe@177 137
jbe@177 138 ALTER TABLE "initiative" ADD CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
jbe@177 139 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
jbe@177 140 ( "positive_votes" ISNULL AND "negative_votes" ISNULL AND
jbe@177 141 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
jbe@177 142 "schulze_rank" ISNULL AND
jbe@177 143 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
jbe@177 144 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
jbe@177 145 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) );
jbe@177 146 ALTER TABLE "initiative" ADD CONSTRAINT "better_excludes_worse" CHECK (NOT ("better_than_status_quo" AND "worse_than_status_quo"));
jbe@177 147 ALTER TABLE "initiative" ADD CONSTRAINT "minimum_requirement_to_be_eligible" CHECK (
jbe@177 148 "eligible" = FALSE OR
jbe@177 149 ("direct_majority" AND "indirect_majority" AND "better_than_status_quo") );
jbe@177 150 ALTER TABLE "initiative" ADD CONSTRAINT "winner_must_be_eligible" CHECK ("winner"=FALSE OR "eligible"=TRUE);
jbe@177 151 ALTER TABLE "initiative" ADD CONSTRAINT "winner_must_have_first_rank" CHECK ("winner"=FALSE OR "rank"=1);
jbe@177 152 ALTER TABLE "initiative" ADD CONSTRAINT "eligible_at_first_rank_is_winner" CHECK ("eligible"=FALSE OR "rank"!=1 OR "winner"=TRUE);
jbe@177 153 ALTER TABLE "initiative" ADD CONSTRAINT "unique_rank_per_issue" UNIQUE ("issue_id", "rank");
jbe@177 154
jbe@177 155 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@177 156 COMMENT ON COLUMN "initiative"."indirect_majority" IS 'Same as "direct_majority", but also considering indirect beat paths';
jbe@177 157 COMMENT ON COLUMN "initiative"."schulze_rank" IS 'Schulze-Ranking without tie-breaking';
jbe@177 158 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@177 159 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@177 160 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@177 161 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@177 162 COMMENT ON COLUMN "initiative"."eligible" IS 'Initiative is "attainable" and depending on selected policy has no "reverse_beat_path" or "multistage_majority"';
jbe@177 163 COMMENT ON COLUMN "initiative"."winner" IS 'Winner is the "eligible" initiative with best "schulze_rank" and in case of ties with lowest "id"';
jbe@177 164 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@177 165
jbe@177 166 ALTER TABLE "battle" DROP CONSTRAINT "battle_pkey";
jbe@177 167 ALTER TABLE "battle" ALTER COLUMN "issue_id" SET NOT NULL;
jbe@249 168 ALTER TABLE "battle" ALTER COLUMN "winning_initiative_id" DROP NOT NULL;
jbe@249 169 ALTER TABLE "battle" ALTER COLUMN "losing_initiative_id" DROP NOT NULL;
jbe@177 170 ALTER TABLE "battle" ADD CONSTRAINT "initiative_ids_not_equal" CHECK (
jbe@177 171 "winning_initiative_id" != "losing_initiative_id" OR
jbe@177 172 ( ("winning_initiative_id" NOTNULL AND "losing_initiative_id" ISNULL) OR
jbe@177 173 ("winning_initiative_id" ISNULL AND "losing_initiative_id" NOTNULL) ) );
jbe@177 174
jbe@177 175 CREATE UNIQUE INDEX "battle_winning_losing_idx" ON "battle" ("issue_id", "winning_initiative_id", "losing_initiative_id");
jbe@177 176 CREATE UNIQUE INDEX "battle_winning_null_idx" ON "battle" ("issue_id", "winning_initiative_id") WHERE "losing_initiative_id" ISNULL;
jbe@177 177 CREATE UNIQUE INDEX "battle_null_losing_idx" ON "battle" ("issue_id", "losing_initiative_id") WHERE "winning_initiative_id" ISNULL;
jbe@177 178
jbe@177 179 ALTER TABLE "suggestion" ADD COLUMN "draft_id" INT8;
jbe@177 180 ALTER TABLE "suggestion" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE;
jbe@177 181 ALTER TABLE "suggestion" ADD COLUMN "formatting_engine" TEXT;
jbe@177 182 ALTER TABLE "suggestion" RENAME COLUMN "description" TO "content";
jbe@177 183
jbe@177 184 DROP TRIGGER "update_text_search_data" ON "suggestion";
jbe@177 185
jbe@177 186 CREATE TRIGGER "update_text_search_data"
jbe@177 187 BEFORE INSERT OR UPDATE ON "suggestion"
jbe@177 188 FOR EACH ROW EXECUTE PROCEDURE
jbe@177 189 tsvector_update_trigger('text_search_data', 'pg_catalog.simple',
jbe@177 190 "name", "content");
jbe@177 191
jbe@177 192 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@177 193
jbe@177 194 CREATE TABLE "rendered_suggestion" (
jbe@177 195 PRIMARY KEY ("suggestion_id", "format"),
jbe@177 196 "suggestion_id" INT8 REFERENCES "suggestion" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@177 197 "format" TEXT,
jbe@177 198 "content" TEXT NOT NULL );
jbe@177 199
jbe@177 200 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@177 201
jbe@183 202 DROP TABLE "invite_code_unit";
jbe@183 203
jbe@177 204 DROP VIEW "area_member_count";
jbe@177 205
jbe@177 206 ALTER TABLE "membership" DROP COLUMN "autoreject";
jbe@177 207
jbe@177 208 ALTER TABLE "interest" DROP COLUMN "autoreject";
jbe@177 209 ALTER TABLE "interest" DROP COLUMN "voting_requested";
jbe@177 210
jbe@177 211 ALTER TABLE "supporter" DROP CONSTRAINT "supporter_initiative_id_fkey";
jbe@177 212 ALTER TABLE "supporter" ADD FOREIGN KEY ("initiative_id", "draft_id") REFERENCES "draft" ("initiative_id", "id") ON DELETE NO ACTION ON UPDATE CASCADE;
jbe@177 213
jbe@177 214 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@177 215
jbe@177 216 ALTER TABLE "direct_interest_snapshot" DROP COLUMN "voting_requested";
jbe@177 217 ALTER TABLE "direct_voter" DROP COLUMN "autoreject";
jbe@177 218
jbe@177 219 DROP TRIGGER "default_for_draft_id" ON "supporter";
jbe@177 220 DROP FUNCTION "supporter_default_for_draft_id_trigger"();
jbe@177 221
jbe@177 222 CREATE FUNCTION "default_for_draft_id_trigger"()
jbe@177 223 RETURNS TRIGGER
jbe@177 224 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@177 225 BEGIN
jbe@177 226 IF NEW."draft_id" ISNULL THEN
jbe@177 227 SELECT "id" INTO NEW."draft_id" FROM "current_draft"
jbe@177 228 WHERE "initiative_id" = NEW."initiative_id";
jbe@177 229 END IF;
jbe@177 230 RETURN NEW;
jbe@177 231 END;
jbe@177 232 $$;
jbe@177 233
jbe@177 234 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "suggestion"
jbe@177 235 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
jbe@177 236 CREATE TRIGGER "default_for_draft_id" BEFORE INSERT OR UPDATE ON "supporter"
jbe@177 237 FOR EACH ROW EXECUTE PROCEDURE "default_for_draft_id_trigger"();
jbe@177 238
jbe@177 239 COMMENT ON FUNCTION "default_for_draft_id_trigger"() IS 'Implementation of trigger "default_for_draft" on tables "supporter" and "suggestion"';
jbe@177 240 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@177 241 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@177 242
jbe@177 243 CREATE VIEW "area_member_count" AS
jbe@177 244 SELECT
jbe@177 245 "area"."id" AS "area_id",
jbe@177 246 count("member"."id") AS "direct_member_count",
jbe@177 247 coalesce(
jbe@177 248 sum(
jbe@177 249 CASE WHEN "member"."id" NOTNULL THEN
jbe@177 250 "membership_weight"("area"."id", "member"."id")
jbe@177 251 ELSE 0 END
jbe@177 252 )
jbe@177 253 ) AS "member_weight"
jbe@177 254 FROM "area"
jbe@177 255 LEFT JOIN "membership"
jbe@177 256 ON "area"."id" = "membership"."area_id"
jbe@177 257 LEFT JOIN "privilege"
jbe@177 258 ON "privilege"."unit_id" = "area"."unit_id"
jbe@177 259 AND "privilege"."member_id" = "membership"."member_id"
jbe@177 260 AND "privilege"."voting_right"
jbe@177 261 LEFT JOIN "member"
jbe@177 262 ON "member"."id" = "privilege"."member_id" -- NOTE: no membership here!
jbe@177 263 AND "member"."active"
jbe@177 264 GROUP BY "area"."id";
jbe@177 265
jbe@177 266 COMMENT ON VIEW "area_member_count" IS 'View used to update "direct_member_count" and "member_weight" columns of table "area"';
jbe@177 267
jbe@177 268 CREATE VIEW "battle_participant" AS
jbe@177 269 SELECT "initiative"."id", "initiative"."issue_id"
jbe@177 270 FROM "issue" JOIN "initiative"
jbe@177 271 ON "issue"."id" = "initiative"."issue_id"
jbe@177 272 WHERE "initiative"."admitted"
jbe@177 273 UNION ALL
jbe@177 274 SELECT NULL, "id" AS "issue_id"
jbe@177 275 FROM "issue";
jbe@177 276
jbe@177 277 COMMENT ON VIEW "battle_participant" IS 'Helper view for "battle_view" containing admitted initiatives plus virtual "status-quo" initiative denoted by NULL reference';
jbe@177 278
jbe@179 279 CREATE VIEW "battle_view" AS
jbe@177 280 SELECT
jbe@177 281 "issue"."id" AS "issue_id",
jbe@177 282 "winning_initiative"."id" AS "winning_initiative_id",
jbe@177 283 "losing_initiative"."id" AS "losing_initiative_id",
jbe@177 284 sum(
jbe@177 285 CASE WHEN
jbe@177 286 coalesce("better_vote"."grade", 0) >
jbe@177 287 coalesce("worse_vote"."grade", 0)
jbe@177 288 THEN "direct_voter"."weight" ELSE 0 END
jbe@177 289 ) AS "count"
jbe@177 290 FROM "issue"
jbe@177 291 LEFT JOIN "direct_voter"
jbe@177 292 ON "issue"."id" = "direct_voter"."issue_id"
jbe@177 293 JOIN "battle_participant" AS "winning_initiative"
jbe@177 294 ON "issue"."id" = "winning_initiative"."issue_id"
jbe@177 295 JOIN "battle_participant" AS "losing_initiative"
jbe@177 296 ON "issue"."id" = "losing_initiative"."issue_id"
jbe@177 297 LEFT JOIN "vote" AS "better_vote"
jbe@177 298 ON "direct_voter"."member_id" = "better_vote"."member_id"
jbe@177 299 AND "winning_initiative"."id" = "better_vote"."initiative_id"
jbe@177 300 LEFT JOIN "vote" AS "worse_vote"
jbe@177 301 ON "direct_voter"."member_id" = "worse_vote"."member_id"
jbe@177 302 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
jbe@177 303 WHERE "issue"."closed" NOTNULL
jbe@177 304 AND "issue"."cleaned" ISNULL
jbe@177 305 AND (
jbe@177 306 "winning_initiative"."id" != "losing_initiative"."id" OR
jbe@177 307 ( ("winning_initiative"."id" NOTNULL AND "losing_initiative"."id" ISNULL) OR
jbe@177 308 ("winning_initiative"."id" ISNULL AND "losing_initiative"."id" NOTNULL) ) )
jbe@177 309 GROUP BY
jbe@177 310 "issue"."id",
jbe@177 311 "winning_initiative"."id",
jbe@177 312 "losing_initiative"."id";
jbe@177 313
jbe@177 314 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@177 315
jbe@184 316 DROP FUNCTION "check_last_login"();
jbe@184 317
jbe@184 318 CREATE FUNCTION "check_activity"()
jbe@184 319 RETURNS VOID
jbe@184 320 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@184 321 DECLARE
jbe@184 322 "system_setting_row" "system_setting"%ROWTYPE;
jbe@184 323 BEGIN
jbe@184 324 SELECT * INTO "system_setting_row" FROM "system_setting";
jbe@184 325 LOCK TABLE "member" IN SHARE ROW EXCLUSIVE MODE;
jbe@184 326 IF "system_setting_row"."member_ttl" NOTNULL THEN
jbe@184 327 UPDATE "member" SET "active" = FALSE
jbe@184 328 WHERE "active" = TRUE
jbe@184 329 AND "last_activity" < (now() - "system_setting_row"."member_ttl")::DATE;
jbe@184 330 END IF;
jbe@184 331 RETURN;
jbe@184 332 END;
jbe@184 333 $$;
jbe@184 334
jbe@184 335 COMMENT ON FUNCTION "check_activity"() IS 'Deactivates members when "last_activity" is older than "system_setting"."member_ttl".';
jbe@184 336
jbe@189 337 CREATE OR REPLACE FUNCTION "calculate_member_counts"()
jbe@189 338 RETURNS VOID
jbe@189 339 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@189 340 BEGIN
jbe@189 341 LOCK TABLE "member" IN SHARE MODE;
jbe@189 342 LOCK TABLE "member_count" IN EXCLUSIVE MODE;
jbe@189 343 LOCK TABLE "unit" IN EXCLUSIVE MODE;
jbe@189 344 LOCK TABLE "area" IN EXCLUSIVE MODE;
jbe@189 345 LOCK TABLE "privilege" IN SHARE MODE;
jbe@189 346 LOCK TABLE "membership" IN SHARE MODE;
jbe@189 347 DELETE FROM "member_count";
jbe@189 348 INSERT INTO "member_count" ("total_count")
jbe@189 349 SELECT "total_count" FROM "member_count_view";
jbe@189 350 UPDATE "unit" SET "member_count" = "view"."member_count"
jbe@189 351 FROM "unit_member_count" AS "view"
jbe@189 352 WHERE "view"."unit_id" = "unit"."id";
jbe@189 353 UPDATE "area" SET
jbe@189 354 "direct_member_count" = "view"."direct_member_count",
jbe@189 355 "member_weight" = "view"."member_weight"
jbe@189 356 FROM "area_member_count" AS "view"
jbe@189 357 WHERE "view"."area_id" = "area"."id";
jbe@189 358 RETURN;
jbe@189 359 END;
jbe@189 360 $$;
jbe@189 361
jbe@177 362 CREATE OR REPLACE FUNCTION "create_interest_snapshot"
jbe@177 363 ( "issue_id_p" "issue"."id"%TYPE )
jbe@177 364 RETURNS VOID
jbe@177 365 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@177 366 DECLARE
jbe@177 367 "member_id_v" "member"."id"%TYPE;
jbe@177 368 BEGIN
jbe@177 369 DELETE FROM "direct_interest_snapshot"
jbe@177 370 WHERE "issue_id" = "issue_id_p"
jbe@177 371 AND "event" = 'periodic';
jbe@177 372 DELETE FROM "delegating_interest_snapshot"
jbe@177 373 WHERE "issue_id" = "issue_id_p"
jbe@177 374 AND "event" = 'periodic';
jbe@177 375 DELETE FROM "direct_supporter_snapshot"
jbe@177 376 WHERE "issue_id" = "issue_id_p"
jbe@177 377 AND "event" = 'periodic';
jbe@177 378 INSERT INTO "direct_interest_snapshot"
jbe@177 379 ("issue_id", "event", "member_id")
jbe@177 380 SELECT
jbe@177 381 "issue_id_p" AS "issue_id",
jbe@177 382 'periodic' AS "event",
jbe@177 383 "member"."id" AS "member_id"
jbe@177 384 FROM "issue"
jbe@177 385 JOIN "area" ON "issue"."area_id" = "area"."id"
jbe@177 386 JOIN "interest" ON "issue"."id" = "interest"."issue_id"
jbe@177 387 JOIN "member" ON "interest"."member_id" = "member"."id"
jbe@177 388 JOIN "privilege"
jbe@177 389 ON "privilege"."unit_id" = "area"."unit_id"
jbe@177 390 AND "privilege"."member_id" = "member"."id"
jbe@177 391 WHERE "issue"."id" = "issue_id_p"
jbe@177 392 AND "member"."active" AND "privilege"."voting_right";
jbe@177 393 FOR "member_id_v" IN
jbe@177 394 SELECT "member_id" FROM "direct_interest_snapshot"
jbe@177 395 WHERE "issue_id" = "issue_id_p"
jbe@177 396 AND "event" = 'periodic'
jbe@177 397 LOOP
jbe@177 398 UPDATE "direct_interest_snapshot" SET
jbe@177 399 "weight" = 1 +
jbe@177 400 "weight_of_added_delegations_for_interest_snapshot"(
jbe@177 401 "issue_id_p",
jbe@177 402 "member_id_v",
jbe@177 403 '{}'
jbe@177 404 )
jbe@177 405 WHERE "issue_id" = "issue_id_p"
jbe@177 406 AND "event" = 'periodic'
jbe@177 407 AND "member_id" = "member_id_v";
jbe@177 408 END LOOP;
jbe@177 409 INSERT INTO "direct_supporter_snapshot"
jbe@177 410 ( "issue_id", "initiative_id", "event", "member_id",
jbe@177 411 "informed", "satisfied" )
jbe@177 412 SELECT
jbe@177 413 "issue_id_p" AS "issue_id",
jbe@177 414 "initiative"."id" AS "initiative_id",
jbe@177 415 'periodic' AS "event",
jbe@177 416 "supporter"."member_id" AS "member_id",
jbe@177 417 "supporter"."draft_id" = "current_draft"."id" AS "informed",
jbe@177 418 NOT EXISTS (
jbe@177 419 SELECT NULL FROM "critical_opinion"
jbe@177 420 WHERE "initiative_id" = "initiative"."id"
jbe@177 421 AND "member_id" = "supporter"."member_id"
jbe@177 422 ) AS "satisfied"
jbe@177 423 FROM "initiative"
jbe@177 424 JOIN "supporter"
jbe@177 425 ON "supporter"."initiative_id" = "initiative"."id"
jbe@177 426 JOIN "current_draft"
jbe@177 427 ON "initiative"."id" = "current_draft"."initiative_id"
jbe@177 428 JOIN "direct_interest_snapshot"
jbe@177 429 ON "supporter"."member_id" = "direct_interest_snapshot"."member_id"
jbe@177 430 AND "initiative"."issue_id" = "direct_interest_snapshot"."issue_id"
jbe@177 431 AND "event" = 'periodic'
jbe@177 432 WHERE "initiative"."issue_id" = "issue_id_p";
jbe@177 433 RETURN;
jbe@177 434 END;
jbe@177 435 $$;
jbe@177 436
jbe@177 437 CREATE OR REPLACE FUNCTION "create_snapshot"
jbe@177 438 ( "issue_id_p" "issue"."id"%TYPE )
jbe@177 439 RETURNS VOID
jbe@177 440 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@177 441 DECLARE
jbe@177 442 "initiative_id_v" "initiative"."id"%TYPE;
jbe@177 443 "suggestion_id_v" "suggestion"."id"%TYPE;
jbe@177 444 BEGIN
jbe@177 445 PERFORM "lock_issue"("issue_id_p");
jbe@177 446 PERFORM "create_population_snapshot"("issue_id_p");
jbe@177 447 PERFORM "create_interest_snapshot"("issue_id_p");
jbe@177 448 UPDATE "issue" SET
jbe@177 449 "snapshot" = now(),
jbe@177 450 "latest_snapshot_event" = 'periodic',
jbe@177 451 "population" = (
jbe@177 452 SELECT coalesce(sum("weight"), 0)
jbe@177 453 FROM "direct_population_snapshot"
jbe@177 454 WHERE "issue_id" = "issue_id_p"
jbe@177 455 AND "event" = 'periodic'
jbe@177 456 )
jbe@177 457 WHERE "id" = "issue_id_p";
jbe@177 458 FOR "initiative_id_v" IN
jbe@177 459 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"
jbe@177 460 LOOP
jbe@177 461 UPDATE "initiative" SET
jbe@177 462 "supporter_count" = (
jbe@177 463 SELECT coalesce(sum("di"."weight"), 0)
jbe@177 464 FROM "direct_interest_snapshot" AS "di"
jbe@177 465 JOIN "direct_supporter_snapshot" AS "ds"
jbe@177 466 ON "di"."member_id" = "ds"."member_id"
jbe@177 467 WHERE "di"."issue_id" = "issue_id_p"
jbe@177 468 AND "di"."event" = 'periodic'
jbe@177 469 AND "ds"."initiative_id" = "initiative_id_v"
jbe@177 470 AND "ds"."event" = 'periodic'
jbe@177 471 ),
jbe@177 472 "informed_supporter_count" = (
jbe@177 473 SELECT coalesce(sum("di"."weight"), 0)
jbe@177 474 FROM "direct_interest_snapshot" AS "di"
jbe@177 475 JOIN "direct_supporter_snapshot" AS "ds"
jbe@177 476 ON "di"."member_id" = "ds"."member_id"
jbe@177 477 WHERE "di"."issue_id" = "issue_id_p"
jbe@177 478 AND "di"."event" = 'periodic'
jbe@177 479 AND "ds"."initiative_id" = "initiative_id_v"
jbe@177 480 AND "ds"."event" = 'periodic'
jbe@177 481 AND "ds"."informed"
jbe@177 482 ),
jbe@177 483 "satisfied_supporter_count" = (
jbe@177 484 SELECT coalesce(sum("di"."weight"), 0)
jbe@177 485 FROM "direct_interest_snapshot" AS "di"
jbe@177 486 JOIN "direct_supporter_snapshot" AS "ds"
jbe@177 487 ON "di"."member_id" = "ds"."member_id"
jbe@177 488 WHERE "di"."issue_id" = "issue_id_p"
jbe@177 489 AND "di"."event" = 'periodic'
jbe@177 490 AND "ds"."initiative_id" = "initiative_id_v"
jbe@177 491 AND "ds"."event" = 'periodic'
jbe@177 492 AND "ds"."satisfied"
jbe@177 493 ),
jbe@177 494 "satisfied_informed_supporter_count" = (
jbe@177 495 SELECT coalesce(sum("di"."weight"), 0)
jbe@177 496 FROM "direct_interest_snapshot" AS "di"
jbe@177 497 JOIN "direct_supporter_snapshot" AS "ds"
jbe@177 498 ON "di"."member_id" = "ds"."member_id"
jbe@177 499 WHERE "di"."issue_id" = "issue_id_p"
jbe@177 500 AND "di"."event" = 'periodic'
jbe@177 501 AND "ds"."initiative_id" = "initiative_id_v"
jbe@177 502 AND "ds"."event" = 'periodic'
jbe@177 503 AND "ds"."informed"
jbe@177 504 AND "ds"."satisfied"
jbe@177 505 )
jbe@177 506 WHERE "id" = "initiative_id_v";
jbe@177 507 FOR "suggestion_id_v" IN
jbe@177 508 SELECT "id" FROM "suggestion"
jbe@177 509 WHERE "initiative_id" = "initiative_id_v"
jbe@177 510 LOOP
jbe@177 511 UPDATE "suggestion" SET
jbe@177 512 "minus2_unfulfilled_count" = (
jbe@177 513 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@177 514 FROM "issue" CROSS JOIN "opinion"
jbe@177 515 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@177 516 ON "snapshot"."issue_id" = "issue"."id"
jbe@177 517 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@177 518 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@177 519 WHERE "issue"."id" = "issue_id_p"
jbe@177 520 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@177 521 AND "opinion"."degree" = -2
jbe@177 522 AND "opinion"."fulfilled" = FALSE
jbe@177 523 ),
jbe@177 524 "minus2_fulfilled_count" = (
jbe@177 525 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@177 526 FROM "issue" CROSS JOIN "opinion"
jbe@177 527 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@177 528 ON "snapshot"."issue_id" = "issue"."id"
jbe@177 529 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@177 530 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@177 531 WHERE "issue"."id" = "issue_id_p"
jbe@177 532 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@177 533 AND "opinion"."degree" = -2
jbe@177 534 AND "opinion"."fulfilled" = TRUE
jbe@177 535 ),
jbe@177 536 "minus1_unfulfilled_count" = (
jbe@177 537 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@177 538 FROM "issue" CROSS JOIN "opinion"
jbe@177 539 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@177 540 ON "snapshot"."issue_id" = "issue"."id"
jbe@177 541 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@177 542 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@177 543 WHERE "issue"."id" = "issue_id_p"
jbe@177 544 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@177 545 AND "opinion"."degree" = -1
jbe@177 546 AND "opinion"."fulfilled" = FALSE
jbe@177 547 ),
jbe@177 548 "minus1_fulfilled_count" = (
jbe@177 549 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@177 550 FROM "issue" CROSS JOIN "opinion"
jbe@177 551 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@177 552 ON "snapshot"."issue_id" = "issue"."id"
jbe@177 553 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@177 554 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@177 555 WHERE "issue"."id" = "issue_id_p"
jbe@177 556 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@177 557 AND "opinion"."degree" = -1
jbe@177 558 AND "opinion"."fulfilled" = TRUE
jbe@177 559 ),
jbe@177 560 "plus1_unfulfilled_count" = (
jbe@177 561 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@177 562 FROM "issue" CROSS JOIN "opinion"
jbe@177 563 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@177 564 ON "snapshot"."issue_id" = "issue"."id"
jbe@177 565 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@177 566 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@177 567 WHERE "issue"."id" = "issue_id_p"
jbe@177 568 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@177 569 AND "opinion"."degree" = 1
jbe@177 570 AND "opinion"."fulfilled" = FALSE
jbe@177 571 ),
jbe@177 572 "plus1_fulfilled_count" = (
jbe@177 573 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@177 574 FROM "issue" CROSS JOIN "opinion"
jbe@177 575 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@177 576 ON "snapshot"."issue_id" = "issue"."id"
jbe@177 577 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@177 578 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@177 579 WHERE "issue"."id" = "issue_id_p"
jbe@177 580 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@177 581 AND "opinion"."degree" = 1
jbe@177 582 AND "opinion"."fulfilled" = TRUE
jbe@177 583 ),
jbe@177 584 "plus2_unfulfilled_count" = (
jbe@177 585 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@177 586 FROM "issue" CROSS JOIN "opinion"
jbe@177 587 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@177 588 ON "snapshot"."issue_id" = "issue"."id"
jbe@177 589 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@177 590 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@177 591 WHERE "issue"."id" = "issue_id_p"
jbe@177 592 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@177 593 AND "opinion"."degree" = 2
jbe@177 594 AND "opinion"."fulfilled" = FALSE
jbe@177 595 ),
jbe@177 596 "plus2_fulfilled_count" = (
jbe@177 597 SELECT coalesce(sum("snapshot"."weight"), 0)
jbe@177 598 FROM "issue" CROSS JOIN "opinion"
jbe@177 599 JOIN "direct_interest_snapshot" AS "snapshot"
jbe@177 600 ON "snapshot"."issue_id" = "issue"."id"
jbe@177 601 AND "snapshot"."event" = "issue"."latest_snapshot_event"
jbe@177 602 AND "snapshot"."member_id" = "opinion"."member_id"
jbe@177 603 WHERE "issue"."id" = "issue_id_p"
jbe@177 604 AND "opinion"."suggestion_id" = "suggestion_id_v"
jbe@177 605 AND "opinion"."degree" = 2
jbe@177 606 AND "opinion"."fulfilled" = TRUE
jbe@177 607 )
jbe@177 608 WHERE "suggestion"."id" = "suggestion_id_v";
jbe@177 609 END LOOP;
jbe@177 610 END LOOP;
jbe@177 611 RETURN;
jbe@177 612 END;
jbe@177 613 $$;
jbe@177 614
jbe@177 615 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@177 616 RETURNS VOID
jbe@177 617 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@177 618 DECLARE
jbe@177 619 "area_id_v" "area"."id"%TYPE;
jbe@177 620 "unit_id_v" "unit"."id"%TYPE;
jbe@177 621 "member_id_v" "member"."id"%TYPE;
jbe@177 622 BEGIN
jbe@177 623 PERFORM "lock_issue"("issue_id_p");
jbe@177 624 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
jbe@177 625 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
jbe@177 626 -- delete delegating votes (in cases of manual reset of issue state):
jbe@177 627 DELETE FROM "delegating_voter"
jbe@177 628 WHERE "issue_id" = "issue_id_p";
jbe@177 629 -- delete votes from non-privileged voters:
jbe@177 630 DELETE FROM "direct_voter"
jbe@177 631 USING (
jbe@177 632 SELECT
jbe@177 633 "direct_voter"."member_id"
jbe@177 634 FROM "direct_voter"
jbe@177 635 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
jbe@177 636 LEFT JOIN "privilege"
jbe@177 637 ON "privilege"."unit_id" = "unit_id_v"
jbe@177 638 AND "privilege"."member_id" = "direct_voter"."member_id"
jbe@177 639 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
jbe@177 640 "member"."active" = FALSE OR
jbe@177 641 "privilege"."voting_right" ISNULL OR
jbe@177 642 "privilege"."voting_right" = FALSE
jbe@177 643 )
jbe@177 644 ) AS "subquery"
jbe@177 645 WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@177 646 AND "direct_voter"."member_id" = "subquery"."member_id";
jbe@177 647 -- consider delegations:
jbe@177 648 UPDATE "direct_voter" SET "weight" = 1
jbe@177 649 WHERE "issue_id" = "issue_id_p";
jbe@177 650 PERFORM "add_vote_delegations"("issue_id_p");
jbe@177 651 -- set voter count and mark issue as being calculated:
jbe@177 652 UPDATE "issue" SET
jbe@177 653 "state" = 'calculation',
jbe@177 654 "closed" = now(),
jbe@177 655 "voter_count" = (
jbe@177 656 SELECT coalesce(sum("weight"), 0)
jbe@177 657 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@177 658 )
jbe@177 659 WHERE "id" = "issue_id_p";
jbe@177 660 -- materialize battle_view:
jbe@177 661 -- NOTE: "closed" column of issue must be set at this point
jbe@177 662 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@177 663 INSERT INTO "battle" (
jbe@177 664 "issue_id",
jbe@177 665 "winning_initiative_id", "losing_initiative_id",
jbe@177 666 "count"
jbe@177 667 ) SELECT
jbe@177 668 "issue_id",
jbe@177 669 "winning_initiative_id", "losing_initiative_id",
jbe@177 670 "count"
jbe@177 671 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@177 672 -- copy "positive_votes" and "negative_votes" from "battle" table:
jbe@177 673 UPDATE "initiative" SET
jbe@177 674 "positive_votes" = "battle_win"."count",
jbe@177 675 "negative_votes" = "battle_lose"."count"
jbe@177 676 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
jbe@177 677 WHERE
jbe@177 678 "battle_win"."issue_id" = "issue_id_p" AND
jbe@177 679 "battle_win"."winning_initiative_id" = "initiative"."id" AND
jbe@177 680 "battle_win"."losing_initiative_id" ISNULL AND
jbe@177 681 "battle_lose"."issue_id" = "issue_id_p" AND
jbe@177 682 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
jbe@177 683 "battle_lose"."winning_initiative_id" ISNULL;
jbe@177 684 END;
jbe@177 685 $$;
jbe@177 686
jbe@177 687 DROP FUNCTION "array_init_string"(INTEGER);
jbe@177 688 DROP FUNCTION "square_matrix_init_string"(INTEGER);
jbe@177 689
jbe@177 690 CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE)
jbe@177 691 RETURNS VOID
jbe@177 692 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@177 693 DECLARE
jbe@177 694 "issue_row" "issue"%ROWTYPE;
jbe@177 695 "policy_row" "policy"%ROWTYPE;
jbe@177 696 "dimension_v" INTEGER;
jbe@177 697 "vote_matrix" INT4[][]; -- absolute votes
jbe@177 698 "matrix" INT8[][]; -- defeat strength / best paths
jbe@177 699 "i" INTEGER;
jbe@177 700 "j" INTEGER;
jbe@177 701 "k" INTEGER;
jbe@177 702 "battle_row" "battle"%ROWTYPE;
jbe@177 703 "rank_ary" INT4[];
jbe@177 704 "rank_v" INT4;
jbe@177 705 "done_v" INTEGER;
jbe@177 706 "winners_ary" INTEGER[];
jbe@177 707 "initiative_id_v" "initiative"."id"%TYPE;
jbe@177 708 BEGIN
jbe@177 709 SELECT * INTO "issue_row"
jbe@177 710 FROM "issue" WHERE "id" = "issue_id_p"
jbe@177 711 FOR UPDATE;
jbe@177 712 SELECT * INTO "policy_row"
jbe@177 713 FROM "policy" WHERE "id" = "issue_row"."policy_id";
jbe@177 714 SELECT count(1) INTO "dimension_v"
jbe@177 715 FROM "battle_participant" WHERE "issue_id" = "issue_id_p";
jbe@177 716 -- Create "vote_matrix" with absolute number of votes in pairwise
jbe@177 717 -- comparison:
jbe@177 718 "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]);
jbe@177 719 "i" := 1;
jbe@177 720 "j" := 2;
jbe@177 721 FOR "battle_row" IN
jbe@177 722 SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p"
jbe@177 723 ORDER BY
jbe@177 724 "winning_initiative_id" NULLS LAST,
jbe@177 725 "losing_initiative_id" NULLS LAST
jbe@177 726 LOOP
jbe@177 727 "vote_matrix"["i"]["j"] := "battle_row"."count";
jbe@177 728 IF "j" = "dimension_v" THEN
jbe@177 729 "i" := "i" + 1;
jbe@177 730 "j" := 1;
jbe@177 731 ELSE
jbe@177 732 "j" := "j" + 1;
jbe@177 733 IF "j" = "i" THEN
jbe@177 734 "j" := "j" + 1;
jbe@177 735 END IF;
jbe@177 736 END IF;
jbe@177 737 END LOOP;
jbe@177 738 IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN
jbe@177 739 RAISE EXCEPTION 'Wrong battle count (should not happen)';
jbe@177 740 END IF;
jbe@177 741 -- Store defeat strengths in "matrix" using "defeat_strength"
jbe@177 742 -- function:
jbe@177 743 "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]);
jbe@177 744 "i" := 1;
jbe@177 745 LOOP
jbe@177 746 "j" := 1;
jbe@177 747 LOOP
jbe@177 748 IF "i" != "j" THEN
jbe@177 749 "matrix"["i"]["j"] := "defeat_strength"(
jbe@177 750 "vote_matrix"["i"]["j"],
jbe@177 751 "vote_matrix"["j"]["i"]
jbe@177 752 );
jbe@177 753 END IF;
jbe@177 754 EXIT WHEN "j" = "dimension_v";
jbe@177 755 "j" := "j" + 1;
jbe@177 756 END LOOP;
jbe@177 757 EXIT WHEN "i" = "dimension_v";
jbe@177 758 "i" := "i" + 1;
jbe@177 759 END LOOP;
jbe@177 760 -- Find best paths:
jbe@177 761 "i" := 1;
jbe@177 762 LOOP
jbe@177 763 "j" := 1;
jbe@177 764 LOOP
jbe@177 765 IF "i" != "j" THEN
jbe@177 766 "k" := 1;
jbe@177 767 LOOP
jbe@177 768 IF "i" != "k" AND "j" != "k" THEN
jbe@177 769 IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN
jbe@177 770 IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN
jbe@177 771 "matrix"["j"]["k"] := "matrix"["j"]["i"];
jbe@177 772 END IF;
jbe@177 773 ELSE
jbe@177 774 IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN
jbe@177 775 "matrix"["j"]["k"] := "matrix"["i"]["k"];
jbe@177 776 END IF;
jbe@177 777 END IF;
jbe@177 778 END IF;
jbe@177 779 EXIT WHEN "k" = "dimension_v";
jbe@177 780 "k" := "k" + 1;
jbe@177 781 END LOOP;
jbe@177 782 END IF;
jbe@177 783 EXIT WHEN "j" = "dimension_v";
jbe@177 784 "j" := "j" + 1;
jbe@177 785 END LOOP;
jbe@177 786 EXIT WHEN "i" = "dimension_v";
jbe@177 787 "i" := "i" + 1;
jbe@177 788 END LOOP;
jbe@177 789 -- Determine order of winners:
jbe@177 790 "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]);
jbe@177 791 "rank_v" := 1;
jbe@177 792 "done_v" := 0;
jbe@177 793 LOOP
jbe@177 794 "winners_ary" := '{}';
jbe@177 795 "i" := 1;
jbe@177 796 LOOP
jbe@177 797 IF "rank_ary"["i"] ISNULL THEN
jbe@177 798 "j" := 1;
jbe@177 799 LOOP
jbe@177 800 IF
jbe@177 801 "i" != "j" AND
jbe@177 802 "rank_ary"["j"] ISNULL AND
jbe@177 803 "matrix"["j"]["i"] > "matrix"["i"]["j"]
jbe@177 804 THEN
jbe@177 805 -- someone else is better
jbe@177 806 EXIT;
jbe@177 807 END IF;
jbe@177 808 IF "j" = "dimension_v" THEN
jbe@177 809 -- noone is better
jbe@177 810 "winners_ary" := "winners_ary" || "i";
jbe@177 811 EXIT;
jbe@177 812 END IF;
jbe@177 813 "j" := "j" + 1;
jbe@177 814 END LOOP;
jbe@177 815 END IF;
jbe@177 816 EXIT WHEN "i" = "dimension_v";
jbe@177 817 "i" := "i" + 1;
jbe@177 818 END LOOP;
jbe@177 819 "i" := 1;
jbe@177 820 LOOP
jbe@177 821 "rank_ary"["winners_ary"["i"]] := "rank_v";
jbe@177 822 "done_v" := "done_v" + 1;
jbe@177 823 EXIT WHEN "i" = array_upper("winners_ary", 1);
jbe@177 824 "i" := "i" + 1;
jbe@177 825 END LOOP;
jbe@177 826 EXIT WHEN "done_v" = "dimension_v";
jbe@177 827 "rank_v" := "rank_v" + 1;
jbe@177 828 END LOOP;
jbe@177 829 -- write preliminary results:
jbe@177 830 "i" := 1;
jbe@177 831 FOR "initiative_id_v" IN
jbe@177 832 SELECT "id" FROM "initiative"
jbe@177 833 WHERE "issue_id" = "issue_id_p" AND "admitted"
jbe@177 834 ORDER BY "id"
jbe@177 835 LOOP
jbe@177 836 UPDATE "initiative" SET
jbe@177 837 "direct_majority" =
jbe@177 838 CASE WHEN "policy_row"."direct_majority_strict" THEN
jbe@177 839 "positive_votes" * "policy_row"."direct_majority_den" >
jbe@177 840 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
jbe@177 841 ELSE
jbe@177 842 "positive_votes" * "policy_row"."direct_majority_den" >=
jbe@177 843 "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes")
jbe@177 844 END
jbe@177 845 AND "positive_votes" >= "policy_row"."direct_majority_positive"
jbe@177 846 AND "issue_row"."voter_count"-"negative_votes" >=
jbe@177 847 "policy_row"."direct_majority_non_negative",
jbe@177 848 "indirect_majority" =
jbe@177 849 CASE WHEN "policy_row"."indirect_majority_strict" THEN
jbe@177 850 "positive_votes" * "policy_row"."indirect_majority_den" >
jbe@177 851 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
jbe@177 852 ELSE
jbe@177 853 "positive_votes" * "policy_row"."indirect_majority_den" >=
jbe@177 854 "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes")
jbe@177 855 END
jbe@177 856 AND "positive_votes" >= "policy_row"."indirect_majority_positive"
jbe@177 857 AND "issue_row"."voter_count"-"negative_votes" >=
jbe@177 858 "policy_row"."indirect_majority_non_negative",
jbe@177 859 "schulze_rank" = "rank_ary"["i"],
jbe@177 860 "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"],
jbe@177 861 "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"],
jbe@177 862 "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"],
jbe@177 863 "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0,
jbe@177 864 "winner" = FALSE
jbe@177 865 WHERE "id" = "initiative_id_v";
jbe@177 866 "i" := "i" + 1;
jbe@177 867 END LOOP;
jbe@177 868 IF "i" != "dimension_v" THEN
jbe@177 869 RAISE EXCEPTION 'Wrong winner count (should not happen)';
jbe@177 870 END IF;
jbe@177 871 -- take indirect majorities into account:
jbe@177 872 LOOP
jbe@177 873 UPDATE "initiative" SET "indirect_majority" = TRUE
jbe@177 874 FROM (
jbe@177 875 SELECT "new_initiative"."id" AS "initiative_id"
jbe@177 876 FROM "initiative" "old_initiative"
jbe@177 877 JOIN "initiative" "new_initiative"
jbe@177 878 ON "new_initiative"."issue_id" = "issue_id_p"
jbe@177 879 AND "new_initiative"."indirect_majority" = FALSE
jbe@177 880 JOIN "battle" "battle_win"
jbe@177 881 ON "battle_win"."issue_id" = "issue_id_p"
jbe@177 882 AND "battle_win"."winning_initiative_id" = "new_initiative"."id"
jbe@177 883 AND "battle_win"."losing_initiative_id" = "old_initiative"."id"
jbe@177 884 JOIN "battle" "battle_lose"
jbe@177 885 ON "battle_lose"."issue_id" = "issue_id_p"
jbe@177 886 AND "battle_lose"."losing_initiative_id" = "new_initiative"."id"
jbe@177 887 AND "battle_lose"."winning_initiative_id" = "old_initiative"."id"
jbe@177 888 WHERE "old_initiative"."issue_id" = "issue_id_p"
jbe@177 889 AND "old_initiative"."indirect_majority" = TRUE
jbe@177 890 AND CASE WHEN "policy_row"."indirect_majority_strict" THEN
jbe@177 891 "battle_win"."count" * "policy_row"."indirect_majority_den" >
jbe@177 892 "policy_row"."indirect_majority_num" *
jbe@177 893 ("battle_win"."count"+"battle_lose"."count")
jbe@177 894 ELSE
jbe@177 895 "battle_win"."count" * "policy_row"."indirect_majority_den" >=
jbe@177 896 "policy_row"."indirect_majority_num" *
jbe@177 897 ("battle_win"."count"+"battle_lose"."count")
jbe@177 898 END
jbe@177 899 AND "battle_win"."count" >= "policy_row"."indirect_majority_positive"
jbe@177 900 AND "issue_row"."voter_count"-"battle_lose"."count" >=
jbe@177 901 "policy_row"."indirect_majority_non_negative"
jbe@177 902 ) AS "subquery"
jbe@177 903 WHERE "id" = "subquery"."initiative_id";
jbe@177 904 EXIT WHEN NOT FOUND;
jbe@177 905 END LOOP;
jbe@177 906 -- set "multistage_majority" for remaining matching initiatives:
jbe@177 907 UPDATE "initiative" SET "multistage_majority" = TRUE
jbe@177 908 FROM (
jbe@177 909 SELECT "losing_initiative"."id" AS "initiative_id"
jbe@177 910 FROM "initiative" "losing_initiative"
jbe@177 911 JOIN "initiative" "winning_initiative"
jbe@177 912 ON "winning_initiative"."issue_id" = "issue_id_p"
jbe@177 913 AND "winning_initiative"."admitted"
jbe@177 914 JOIN "battle" "battle_win"
jbe@177 915 ON "battle_win"."issue_id" = "issue_id_p"
jbe@177 916 AND "battle_win"."winning_initiative_id" = "winning_initiative"."id"
jbe@177 917 AND "battle_win"."losing_initiative_id" = "losing_initiative"."id"
jbe@177 918 JOIN "battle" "battle_lose"
jbe@177 919 ON "battle_lose"."issue_id" = "issue_id_p"
jbe@177 920 AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id"
jbe@177 921 AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id"
jbe@177 922 WHERE "losing_initiative"."issue_id" = "issue_id_p"
jbe@177 923 AND "losing_initiative"."admitted"
jbe@177 924 AND "winning_initiative"."schulze_rank" <
jbe@177 925 "losing_initiative"."schulze_rank"
jbe@177 926 AND "battle_win"."count" > "battle_lose"."count"
jbe@177 927 AND (
jbe@177 928 "battle_win"."count" > "winning_initiative"."positive_votes" OR
jbe@177 929 "battle_lose"."count" < "losing_initiative"."negative_votes" )
jbe@177 930 ) AS "subquery"
jbe@177 931 WHERE "id" = "subquery"."initiative_id";
jbe@177 932 -- mark eligible initiatives:
jbe@177 933 UPDATE "initiative" SET "eligible" = TRUE
jbe@177 934 WHERE "issue_id" = "issue_id_p"
jbe@177 935 AND "initiative"."direct_majority"
jbe@177 936 AND "initiative"."indirect_majority"
jbe@177 937 AND "initiative"."better_than_status_quo"
jbe@177 938 AND (
jbe@177 939 "policy_row"."no_multistage_majority" = FALSE OR
jbe@177 940 "initiative"."multistage_majority" = FALSE )
jbe@177 941 AND (
jbe@177 942 "policy_row"."no_reverse_beat_path" = FALSE OR
jbe@177 943 "initiative"."reverse_beat_path" = FALSE );
jbe@177 944 -- mark final winner:
jbe@177 945 UPDATE "initiative" SET "winner" = TRUE
jbe@177 946 FROM (
jbe@177 947 SELECT "id" AS "initiative_id"
jbe@177 948 FROM "initiative"
jbe@177 949 WHERE "issue_id" = "issue_id_p" AND "eligible"
jbe@177 950 ORDER BY "schulze_rank", "id"
jbe@177 951 LIMIT 1
jbe@177 952 ) AS "subquery"
jbe@177 953 WHERE "id" = "subquery"."initiative_id";
jbe@177 954 -- write (final) ranks:
jbe@177 955 "rank_v" := 1;
jbe@177 956 FOR "initiative_id_v" IN
jbe@177 957 SELECT "id"
jbe@177 958 FROM "initiative"
jbe@177 959 WHERE "issue_id" = "issue_id_p" AND "admitted"
jbe@177 960 ORDER BY
jbe@177 961 "winner" DESC,
jbe@177 962 ("direct_majority" AND "indirect_majority") DESC,
jbe@177 963 "schulze_rank",
jbe@177 964 "id"
jbe@177 965 LOOP
jbe@177 966 UPDATE "initiative" SET "rank" = "rank_v"
jbe@177 967 WHERE "id" = "initiative_id_v";
jbe@177 968 "rank_v" := "rank_v" + 1;
jbe@177 969 END LOOP;
jbe@177 970 -- set schulze rank of status quo and mark issue as finished:
jbe@177 971 UPDATE "issue" SET
jbe@177 972 "status_quo_schulze_rank" = "rank_ary"["dimension_v"],
jbe@177 973 "state" =
jbe@177 974 CASE WHEN EXISTS (
jbe@177 975 SELECT NULL FROM "initiative"
jbe@177 976 WHERE "issue_id" = "issue_id_p" AND "winner"
jbe@177 977 ) THEN
jbe@177 978 'finished_with_winner'::"issue_state"
jbe@177 979 ELSE
jbe@177 980 'finished_without_winner'::"issue_state"
jbe@177 981 END,
jbe@177 982 "ranks_available" = TRUE
jbe@177 983 WHERE "id" = "issue_id_p";
jbe@177 984 RETURN;
jbe@177 985 END;
jbe@177 986 $$;
jbe@177 987
jbe@177 988 CREATE OR REPLACE FUNCTION "check_issue"
jbe@177 989 ( "issue_id_p" "issue"."id"%TYPE )
jbe@177 990 RETURNS VOID
jbe@177 991 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@177 992 DECLARE
jbe@177 993 "issue_row" "issue"%ROWTYPE;
jbe@177 994 "policy_row" "policy"%ROWTYPE;
jbe@177 995 BEGIN
jbe@177 996 PERFORM "lock_issue"("issue_id_p");
jbe@177 997 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@177 998 -- only process open issues:
jbe@177 999 IF "issue_row"."closed" ISNULL THEN
jbe@177 1000 SELECT * INTO "policy_row" FROM "policy"
jbe@177 1001 WHERE "id" = "issue_row"."policy_id";
jbe@177 1002 -- create a snapshot, unless issue is already fully frozen:
jbe@177 1003 IF "issue_row"."fully_frozen" ISNULL THEN
jbe@177 1004 PERFORM "create_snapshot"("issue_id_p");
jbe@177 1005 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@177 1006 END IF;
jbe@177 1007 -- eventually close or accept issues, which have not been accepted:
jbe@177 1008 IF "issue_row"."accepted" ISNULL THEN
jbe@177 1009 IF EXISTS (
jbe@177 1010 SELECT NULL FROM "initiative"
jbe@177 1011 WHERE "issue_id" = "issue_id_p"
jbe@177 1012 AND "supporter_count" > 0
jbe@177 1013 AND "supporter_count" * "policy_row"."issue_quorum_den"
jbe@177 1014 >= "issue_row"."population" * "policy_row"."issue_quorum_num"
jbe@177 1015 ) THEN
jbe@177 1016 -- accept issues, if supporter count is high enough
jbe@177 1017 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@177 1018 -- NOTE: "issue_row" used later
jbe@177 1019 "issue_row"."state" := 'discussion';
jbe@177 1020 "issue_row"."accepted" := now();
jbe@177 1021 UPDATE "issue" SET
jbe@177 1022 "state" = "issue_row"."state",
jbe@177 1023 "accepted" = "issue_row"."accepted"
jbe@177 1024 WHERE "id" = "issue_row"."id";
jbe@177 1025 ELSIF
jbe@177 1026 now() >= "issue_row"."created" + "issue_row"."admission_time"
jbe@177 1027 THEN
jbe@177 1028 -- close issues, if admission time has expired
jbe@177 1029 PERFORM "set_snapshot_event"("issue_id_p", 'end_of_admission');
jbe@177 1030 UPDATE "issue" SET
jbe@177 1031 "state" = 'canceled_issue_not_accepted',
jbe@177 1032 "closed" = now()
jbe@177 1033 WHERE "id" = "issue_row"."id";
jbe@177 1034 END IF;
jbe@177 1035 END IF;
jbe@177 1036 -- eventually half freeze issues:
jbe@177 1037 IF
jbe@177 1038 -- NOTE: issue can't be closed at this point, if it has been accepted
jbe@177 1039 "issue_row"."accepted" NOTNULL AND
jbe@177 1040 "issue_row"."half_frozen" ISNULL
jbe@177 1041 THEN
jbe@177 1042 IF
jbe@177 1043 now() >= "issue_row"."accepted" + "issue_row"."discussion_time"
jbe@177 1044 THEN
jbe@177 1045 PERFORM "set_snapshot_event"("issue_id_p", 'half_freeze');
jbe@177 1046 -- NOTE: "issue_row" used later
jbe@177 1047 "issue_row"."state" := 'verification';
jbe@177 1048 "issue_row"."half_frozen" := now();
jbe@177 1049 UPDATE "issue" SET
jbe@177 1050 "state" = "issue_row"."state",
jbe@177 1051 "half_frozen" = "issue_row"."half_frozen"
jbe@177 1052 WHERE "id" = "issue_row"."id";
jbe@177 1053 END IF;
jbe@177 1054 END IF;
jbe@177 1055 -- close issues after some time, if all initiatives have been revoked:
jbe@177 1056 IF
jbe@177 1057 "issue_row"."closed" ISNULL AND
jbe@177 1058 NOT EXISTS (
jbe@177 1059 -- all initiatives are revoked
jbe@177 1060 SELECT NULL FROM "initiative"
jbe@177 1061 WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL
jbe@177 1062 ) AND (
jbe@177 1063 -- and issue has not been accepted yet
jbe@177 1064 "issue_row"."accepted" ISNULL OR
jbe@177 1065 NOT EXISTS (
jbe@177 1066 -- or no initiatives have been revoked lately
jbe@177 1067 SELECT NULL FROM "initiative"
jbe@177 1068 WHERE "issue_id" = "issue_id_p"
jbe@177 1069 AND now() < "revoked" + "issue_row"."verification_time"
jbe@177 1070 ) OR (
jbe@177 1071 -- or verification time has elapsed
jbe@177 1072 "issue_row"."half_frozen" NOTNULL AND
jbe@177 1073 "issue_row"."fully_frozen" ISNULL AND
jbe@177 1074 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@177 1075 )
jbe@177 1076 )
jbe@177 1077 THEN
jbe@177 1078 -- NOTE: "issue_row" used later
jbe@177 1079 IF "issue_row"."accepted" ISNULL THEN
jbe@177 1080 "issue_row"."state" := 'canceled_revoked_before_accepted';
jbe@177 1081 ELSIF "issue_row"."half_frozen" ISNULL THEN
jbe@177 1082 "issue_row"."state" := 'canceled_after_revocation_during_discussion';
jbe@177 1083 ELSE
jbe@177 1084 "issue_row"."state" := 'canceled_after_revocation_during_verification';
jbe@177 1085 END IF;
jbe@177 1086 "issue_row"."closed" := now();
jbe@177 1087 UPDATE "issue" SET
jbe@177 1088 "state" = "issue_row"."state",
jbe@177 1089 "closed" = "issue_row"."closed"
jbe@177 1090 WHERE "id" = "issue_row"."id";
jbe@177 1091 END IF;
jbe@177 1092 -- fully freeze issue after verification time:
jbe@177 1093 IF
jbe@177 1094 "issue_row"."half_frozen" NOTNULL AND
jbe@177 1095 "issue_row"."fully_frozen" ISNULL AND
jbe@177 1096 "issue_row"."closed" ISNULL AND
jbe@177 1097 now() >= "issue_row"."half_frozen" + "issue_row"."verification_time"
jbe@177 1098 THEN
jbe@177 1099 PERFORM "freeze_after_snapshot"("issue_id_p");
jbe@177 1100 -- NOTE: "issue" might change, thus "issue_row" has to be updated below
jbe@177 1101 END IF;
jbe@177 1102 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@177 1103 -- close issue by calling close_voting(...) after voting time:
jbe@177 1104 IF
jbe@177 1105 "issue_row"."closed" ISNULL AND
jbe@177 1106 "issue_row"."fully_frozen" NOTNULL AND
jbe@177 1107 now() >= "issue_row"."fully_frozen" + "issue_row"."voting_time"
jbe@177 1108 THEN
jbe@177 1109 PERFORM "close_voting"("issue_id_p");
jbe@177 1110 -- calculate ranks will not consume much time and can be done now
jbe@177 1111 PERFORM "calculate_ranks"("issue_id_p");
jbe@177 1112 END IF;
jbe@177 1113 END IF;
jbe@177 1114 RETURN;
jbe@177 1115 END;
jbe@177 1116 $$;
jbe@177 1117
jbe@183 1118 CREATE OR REPLACE FUNCTION "check_everything"()
jbe@183 1119 RETURNS VOID
jbe@183 1120 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@183 1121 DECLARE
jbe@183 1122 "issue_id_v" "issue"."id"%TYPE;
jbe@183 1123 BEGIN
jbe@184 1124 PERFORM "check_activity"();
jbe@183 1125 PERFORM "calculate_member_counts"();
jbe@183 1126 FOR "issue_id_v" IN SELECT "id" FROM "open_issue" LOOP
jbe@183 1127 PERFORM "check_issue"("issue_id_v");
jbe@183 1128 END LOOP;
jbe@183 1129 FOR "issue_id_v" IN SELECT "id" FROM "issue_with_ranks_missing" LOOP
jbe@183 1130 PERFORM "calculate_ranks"("issue_id_v");
jbe@183 1131 END LOOP;
jbe@183 1132 RETURN;
jbe@183 1133 END;
jbe@183 1134 $$;
jbe@183 1135
jbe@183 1136 CREATE OR REPLACE FUNCTION "delete_member"("member_id_p" "member"."id"%TYPE)
jbe@183 1137 RETURNS VOID
jbe@183 1138 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@183 1139 BEGIN
jbe@183 1140 UPDATE "member" SET
jbe@183 1141 "last_login" = NULL,
jbe@183 1142 "login" = NULL,
jbe@183 1143 "password" = NULL,
jbe@183 1144 "locked" = TRUE,
jbe@183 1145 "active" = FALSE,
jbe@183 1146 "notify_email" = NULL,
jbe@183 1147 "notify_email_unconfirmed" = NULL,
jbe@183 1148 "notify_email_secret" = NULL,
jbe@183 1149 "notify_email_secret_expiry" = NULL,
jbe@183 1150 "notify_email_lock_expiry" = NULL,
jbe@183 1151 "password_reset_secret" = NULL,
jbe@183 1152 "password_reset_secret_expiry" = NULL,
jbe@183 1153 "organizational_unit" = NULL,
jbe@183 1154 "internal_posts" = NULL,
jbe@183 1155 "realname" = NULL,
jbe@183 1156 "birthday" = NULL,
jbe@183 1157 "address" = NULL,
jbe@183 1158 "email" = NULL,
jbe@183 1159 "xmpp_address" = NULL,
jbe@183 1160 "website" = NULL,
jbe@183 1161 "phone" = NULL,
jbe@183 1162 "mobile_phone" = NULL,
jbe@183 1163 "profession" = NULL,
jbe@183 1164 "external_memberships" = NULL,
jbe@183 1165 "external_posts" = NULL,
jbe@183 1166 "statement" = NULL
jbe@183 1167 WHERE "id" = "member_id_p";
jbe@183 1168 -- "text_search_data" is updated by triggers
jbe@183 1169 DELETE FROM "setting" WHERE "member_id" = "member_id_p";
jbe@183 1170 DELETE FROM "setting_map" WHERE "member_id" = "member_id_p";
jbe@183 1171 DELETE FROM "member_relation_setting" WHERE "member_id" = "member_id_p";
jbe@183 1172 DELETE FROM "member_image" WHERE "member_id" = "member_id_p";
jbe@183 1173 DELETE FROM "contact" WHERE "member_id" = "member_id_p";
jbe@183 1174 DELETE FROM "ignored_member" WHERE "member_id" = "member_id_p";
jbe@183 1175 DELETE FROM "area_setting" WHERE "member_id" = "member_id_p";
jbe@183 1176 DELETE FROM "issue_setting" WHERE "member_id" = "member_id_p";
jbe@183 1177 DELETE FROM "ignored_initiative" WHERE "member_id" = "member_id_p";
jbe@183 1178 DELETE FROM "initiative_setting" WHERE "member_id" = "member_id_p";
jbe@183 1179 DELETE FROM "suggestion_setting" WHERE "member_id" = "member_id_p";
jbe@183 1180 DELETE FROM "membership" WHERE "member_id" = "member_id_p";
jbe@183 1181 DELETE FROM "delegation" WHERE "truster_id" = "member_id_p";
jbe@183 1182 DELETE FROM "non_voter" WHERE "member_id" = "member_id_p";
jbe@183 1183 DELETE FROM "direct_voter" USING "issue"
jbe@183 1184 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@183 1185 AND "issue"."closed" ISNULL
jbe@183 1186 AND "member_id" = "member_id_p";
jbe@183 1187 RETURN;
jbe@183 1188 END;
jbe@183 1189 $$;
jbe@183 1190
jbe@183 1191 CREATE OR REPLACE FUNCTION "delete_private_data"()
jbe@183 1192 RETURNS VOID
jbe@183 1193 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@183 1194 BEGIN
jbe@183 1195 UPDATE "member" SET
jbe@183 1196 "last_login" = NULL,
jbe@183 1197 "login" = NULL,
jbe@183 1198 "password" = NULL,
jbe@183 1199 "notify_email" = NULL,
jbe@183 1200 "notify_email_unconfirmed" = NULL,
jbe@183 1201 "notify_email_secret" = NULL,
jbe@183 1202 "notify_email_secret_expiry" = NULL,
jbe@183 1203 "notify_email_lock_expiry" = NULL,
jbe@183 1204 "password_reset_secret" = NULL,
jbe@183 1205 "password_reset_secret_expiry" = NULL,
jbe@183 1206 "organizational_unit" = NULL,
jbe@183 1207 "internal_posts" = NULL,
jbe@183 1208 "realname" = NULL,
jbe@183 1209 "birthday" = NULL,
jbe@183 1210 "address" = NULL,
jbe@183 1211 "email" = NULL,
jbe@183 1212 "xmpp_address" = NULL,
jbe@183 1213 "website" = NULL,
jbe@183 1214 "phone" = NULL,
jbe@183 1215 "mobile_phone" = NULL,
jbe@183 1216 "profession" = NULL,
jbe@183 1217 "external_memberships" = NULL,
jbe@183 1218 "external_posts" = NULL,
jbe@183 1219 "statement" = NULL;
jbe@183 1220 -- "text_search_data" is updated by triggers
jbe@183 1221 DELETE FROM "invite_code";
jbe@183 1222 DELETE FROM "setting";
jbe@183 1223 DELETE FROM "setting_map";
jbe@183 1224 DELETE FROM "member_relation_setting";
jbe@183 1225 DELETE FROM "member_image";
jbe@183 1226 DELETE FROM "contact";
jbe@183 1227 DELETE FROM "ignored_member";
jbe@183 1228 DELETE FROM "area_setting";
jbe@183 1229 DELETE FROM "issue_setting";
jbe@183 1230 DELETE FROM "ignored_initiative";
jbe@183 1231 DELETE FROM "initiative_setting";
jbe@183 1232 DELETE FROM "suggestion_setting";
jbe@183 1233 DELETE FROM "non_voter";
jbe@183 1234 DELETE FROM "direct_voter" USING "issue"
jbe@183 1235 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@183 1236 AND "issue"."closed" ISNULL;
jbe@183 1237 RETURN;
jbe@183 1238 END;
jbe@183 1239 $$;
jbe@183 1240
jbe@178 1241 COMMIT;
jbe@178 1242
jbe@178 1243 BEGIN;
jbe@178 1244
jbe@184 1245 UPDATE "member" SET
jbe@184 1246 "activated" = "created",
jbe@184 1247 "last_activity" = CASE WHEN "active" THEN
jbe@184 1248 coalesce("last_login"::DATE, now())
jbe@184 1249 ELSE
jbe@184 1250 "last_login"::DATE
jbe@184 1251 END;
jbe@182 1252
jbe@182 1253 UPDATE "member" SET
jbe@182 1254 "created" = "invite_code"."created",
jbe@182 1255 "invite_code" = "invite_code"."code",
jbe@182 1256 "admin_comment" = "invite_code"."comment"
jbe@182 1257 FROM "invite_code"
jbe@182 1258 WHERE "member"."id" = "invite_code"."member_id";
jbe@182 1259
jbe@245 1260 INSERT INTO "member" ("created", "invite_code", "admin_comment")
jbe@245 1261 SELECT "created", "code", "comment"
jbe@245 1262 FROM "invite_code" WHERE "member_id" ISNULL;
jbe@245 1263
jbe@245 1264 INSERT INTO "privilege" ("unit_id", "member_id", "voting_right")
jbe@245 1265 SELECT 1 AS "unit_id", "id" AS "member_id", TRUE AS "voting_right"
jbe@245 1266 FROM "member" WHERE "activated" ISNULL;
jbe@245 1267
jbe@182 1268 DROP TABLE "invite_code";
jbe@182 1269
jbe@178 1270 UPDATE "initiative" SET
jbe@178 1271 "direct_majority" = "rank" NOTNULL,
jbe@178 1272 "indirect_majority" = "rank" NOTNULL,
jbe@178 1273 "schulze_rank" = "rank",
jbe@178 1274 "better_than_status_quo" = "rank" NOTNULL,
jbe@178 1275 "worse_than_status_quo" = "rank" ISNULL,
jbe@178 1276 "reverse_beat_path" = "rank" ISNULL,
jbe@178 1277 "multistage_majority" = "rank" ISNULL,
jbe@178 1278 "eligible" = "rank" NOTNULL,
jbe@178 1279 "winner" = ("rank" = 1)
jbe@178 1280 FROM "issue"
jbe@178 1281 WHERE "issue"."id" = "initiative"."issue_id"
jbe@178 1282 AND "issue"."state" IN ('finished_without_winner', 'finished_with_winner')
jbe@178 1283 AND "initiative"."admitted";
jbe@177 1284
jbe@178 1285 UPDATE "issue" SET "status_quo_schulze_rank" = "subquery"."rank"
jbe@178 1286 FROM (
jbe@178 1287 SELECT
jbe@178 1288 "issue"."id" AS "issue_id",
jbe@186 1289 COALESCE(max("initiative"."rank") + 1, 1) AS "rank"
jbe@178 1290 FROM "issue" JOIN "initiative"
jbe@178 1291 ON "issue"."id" = "initiative"."issue_id"
jbe@178 1292 WHERE "issue"."state" IN ('finished_without_winner', 'finished_with_winner')
jbe@178 1293 AND "initiative"."admitted"
jbe@178 1294 GROUP BY "issue"."id"
jbe@178 1295 ) AS "subquery"
jbe@178 1296 WHERE "issue"."id" = "subquery"."issue_id";
jbe@178 1297
jbe@178 1298 CREATE FUNCTION "update__set_remaining_ranks"("issue_id_p" "issue"."id"%TYPE)
jbe@178 1299 RETURNS VOID
jbe@178 1300 LANGUAGE 'plpgsql' AS $$
jbe@178 1301 DECLARE
jbe@178 1302 "rank_v" INT4;
jbe@178 1303 "initiative_id_v" INT4;
jbe@178 1304 BEGIN
jbe@178 1305 SELECT "status_quo_schulze_rank" INTO "rank_v"
jbe@178 1306 FROM "issue" WHERE "id" = "issue_id_p";
jbe@178 1307 FOR "initiative_id_v" IN
jbe@178 1308 SELECT "id" FROM "initiative"
jbe@178 1309 WHERE "issue_id" = "issue_id_p" AND "admitted" AND "rank" ISNULL
jbe@178 1310 ORDER BY "vote_ratio"("positive_votes", "negative_votes") DESC
jbe@178 1311 LOOP
jbe@178 1312 UPDATE "initiative" SET
jbe@178 1313 "schulze_rank" = "rank_v" + 1,
jbe@178 1314 "rank" = "rank_v"
jbe@178 1315 WHERE "id" = "initiative_id_v";
jbe@178 1316 "rank_v" := "rank_v" + 1;
jbe@178 1317 END LOOP;
jbe@178 1318 RETURN;
jbe@178 1319 END;
jbe@178 1320 $$;
jbe@178 1321
jbe@178 1322 SELECT "update__set_remaining_ranks"("id") FROM "issue"
jbe@178 1323 WHERE "state" IN ('finished_without_winner', 'finished_with_winner');
jbe@178 1324
jbe@178 1325 DROP FUNCTION "update__set_remaining_ranks"("issue"."id"%TYPE);
jbe@177 1326
jbe@177 1327 UPDATE "suggestion" SET "draft_id" = "subquery"."draft_id"
jbe@177 1328 FROM (
jbe@177 1329 SELECT DISTINCT ON ("suggestion"."id")
jbe@177 1330 "suggestion"."id" AS "suggestion_id",
jbe@177 1331 "draft"."id" AS "draft_id"
jbe@177 1332 FROM "suggestion" JOIN "draft"
jbe@177 1333 ON "suggestion"."initiative_id" = "draft"."initiative_id"
jbe@177 1334 WHERE "draft"."created" <= "suggestion"."created"
jbe@177 1335 ORDER BY "suggestion"."id", "draft"."created" DESC
jbe@177 1336 ) AS "subquery"
jbe@177 1337 WHERE "suggestion"."id" = "subquery"."suggestion_id";
jbe@177 1338
jbe@178 1339 COMMIT;
jbe@178 1340
jbe@184 1341 ALTER TABLE "member" ADD CONSTRAINT "active_requires_activated_and_last_activity"
jbe@184 1342 CHECK ("active" = FALSE OR ("activated" NOTNULL AND "last_activity" NOTNULL));
jbe@177 1343 ALTER TABLE "suggestion" ALTER COLUMN "draft_id" SET NOT NULL;

Impressum / About Us