liquid_feedback_core

annotate update/core-update.v3.0.2-v3.0.3.sql @ 437:eb12a069063c

find changesets by author, revision, files, or words in the commit message
Improved previous fix of "close_voting" function
author jbe
date Wed Jul 16 16:50:07 2014 +0200 (2014-07-16)
parents 34cc98defa8b
children 785ea3c0fd18
rev   line source
jbe@436 1 -- NOTICE: This update script disables the "no_reserve_beat_path" setting for
jbe@436 2 -- all policies. If this is not intended, please edit this script
jbe@436 3 -- before applying it to your database.
jbe@436 4
jbe@436 5 BEGIN;
jbe@436 6
jbe@436 7 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@436 8 SELECT * FROM (VALUES ('3.0.3', 3, 0, 3))
jbe@436 9 AS "subquery"("string", "major", "minor", "revision");
jbe@436 10
jbe@436 11 CREATE FUNCTION "update3_0_3_add_columns_if_missing"()
jbe@436 12 RETURNS VOID
jbe@436 13 LANGUAGE 'plpgsql' AS $$
jbe@436 14 BEGIN
jbe@436 15 BEGIN
jbe@436 16 ALTER TABLE "initiative" ADD COLUMN "first_preference_votes" INT4;
jbe@436 17 EXCEPTION
jbe@436 18 WHEN duplicate_column THEN
jbe@436 19 RAISE NOTICE 'column "first_preference_votes" of relation "initiative" already exists, skipping';
jbe@436 20 END;
jbe@436 21 BEGIN
jbe@436 22 ALTER TABLE "vote" ADD COLUMN "first_preference" BOOLEAN;
jbe@436 23 EXCEPTION
jbe@436 24 WHEN duplicate_column THEN
jbe@436 25 RAISE NOTICE 'column "first_preference" of relation "vote" already exists, skipping';
jbe@436 26 END;
jbe@436 27 RETURN;
jbe@436 28 END;
jbe@436 29 $$;
jbe@436 30
jbe@436 31 SELECT "update3_0_3_add_columns_if_missing"();
jbe@436 32
jbe@436 33 DROP FUNCTION "update3_0_3_add_columns_if_missing"();
jbe@436 34
jbe@436 35 ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results";
jbe@436 36 ALTER TABLE "initiative" ADD CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
jbe@436 37 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
jbe@436 38 ( "first_preference_votes" ISNULL AND
jbe@436 39 "positive_votes" ISNULL AND "negative_votes" ISNULL AND
jbe@436 40 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
jbe@436 41 "schulze_rank" ISNULL AND
jbe@436 42 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
jbe@436 43 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
jbe@436 44 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) );
jbe@436 45
jbe@436 46 COMMENT ON COLUMN "initiative"."first_preference_votes" IS 'Number of direct and delegating voters who ranked this initiative as their first choice';
jbe@436 47 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Number of direct and delegating voters who ranked this initiative better than the status quo';
jbe@436 48 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Number of direct and delegating voters who ranked this initiative worse than the status quo';
jbe@436 49
jbe@436 50 -- UPDATE TABLE "vote" SET "grade" = 0 WHERE "grade" ISNULL; -- should not be necessary
jbe@436 51 ALTER TABLE "vote" ALTER COLUMN "grade" SET NOT NULL;
jbe@436 52
jbe@436 53 ALTER TABLE "vote" DROP CONSTRAINT IF EXISTS "first_preference_flag_only_set_on_positive_grades";
jbe@436 54 ALTER TABLE "vote" ADD
jbe@436 55 CONSTRAINT "first_preference_flag_only_set_on_positive_grades"
jbe@436 56 CHECK ("grade" > 0 OR "first_preference" ISNULL);
jbe@436 57
jbe@436 58 COMMENT ON COLUMN "vote"."first_preference" IS 'Value is automatically set after voting is finished. For positive grades, this value is set to true for the highest (i.e. best) grade.';
jbe@436 59
jbe@436 60 INSERT INTO "temporary_transaction_data" ("key", "value")
jbe@436 61 VALUES ('override_protection_triggers', TRUE::TEXT);
jbe@436 62
jbe@436 63 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
jbe@436 64 FROM (
jbe@436 65 SELECT
jbe@436 66 "vote"."initiative_id",
jbe@436 67 "vote"."member_id",
jbe@436 68 CASE WHEN "vote"."grade" > 0 THEN
jbe@436 69 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
jbe@436 70 ELSE NULL
jbe@436 71 END AS "first_preference"
jbe@436 72 FROM "vote"
jbe@436 73 JOIN "initiative" -- NOTE: due to missing index on issue_id
jbe@436 74 ON "vote"."issue_id" = "initiative"."issue_id"
jbe@436 75 JOIN "vote" AS "agg"
jbe@436 76 ON "initiative"."id" = "agg"."initiative_id"
jbe@436 77 AND "vote"."member_id" = "agg"."member_id"
jbe@436 78 GROUP BY "vote"."initiative_id", "vote"."member_id"
jbe@436 79 ) AS "subquery"
jbe@436 80 WHERE "vote"."initiative_id" = "subquery"."initiative_id"
jbe@436 81 AND "vote"."member_id" = "subquery"."member_id";
jbe@436 82
jbe@436 83 DELETE FROM "temporary_transaction_data"
jbe@436 84 WHERE "key" = 'override_protection_triggers';
jbe@436 85
jbe@436 86 UPDATE "initiative" SET "first_preference_votes" = NULL
jbe@436 87 WHERE "first_preference_votes" = 0;
jbe@436 88
jbe@436 89 UPDATE "initiative"
jbe@436 90 SET "first_preference_votes" = "subquery"."sum"
jbe@436 91 FROM (
jbe@436 92 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
jbe@436 93 FROM "vote" JOIN "direct_voter"
jbe@436 94 ON "vote"."issue_id" = "direct_voter"."issue_id"
jbe@436 95 AND "vote"."member_id" = "direct_voter"."member_id"
jbe@436 96 WHERE "vote"."first_preference"
jbe@436 97 GROUP BY "vote"."initiative_id"
jbe@436 98 ) AS "subquery"
jbe@436 99 WHERE "initiative"."admitted"
jbe@436 100 AND "initiative"."id" = "subquery"."initiative_id"
jbe@436 101 AND "initiative"."first_preference_votes" ISNULL;
jbe@436 102
jbe@436 103 UPDATE "initiative" SET "first_preference_votes" = 0
jbe@436 104 WHERE "positive_votes" NOTNULL
jbe@436 105 AND "first_preference_votes" ISNULL;
jbe@436 106
jbe@436 107 -- reconstruct battle data (originating from LiquidFeedback Core before v2.0.0)
jbe@436 108 -- to avoid future data loss when executing "clean_issue" to delete voting data:
jbe@436 109 INSERT INTO "battle" (
jbe@436 110 "issue_id",
jbe@436 111 "winning_initiative_id",
jbe@436 112 "losing_initiative_id",
jbe@436 113 "count"
jbe@436 114 ) SELECT
jbe@436 115 "battle_view"."issue_id",
jbe@436 116 "battle_view"."winning_initiative_id",
jbe@436 117 "battle_view"."losing_initiative_id",
jbe@436 118 "battle_view"."count"
jbe@436 119 FROM (
jbe@436 120 SELECT
jbe@436 121 "issue"."id" AS "issue_id",
jbe@436 122 "winning_initiative"."id" AS "winning_initiative_id",
jbe@436 123 "losing_initiative"."id" AS "losing_initiative_id",
jbe@436 124 sum(
jbe@436 125 CASE WHEN
jbe@436 126 coalesce("better_vote"."grade", 0) >
jbe@436 127 coalesce("worse_vote"."grade", 0)
jbe@436 128 THEN "direct_voter"."weight" ELSE 0 END
jbe@436 129 ) AS "count"
jbe@436 130 FROM "issue"
jbe@436 131 LEFT JOIN "direct_voter"
jbe@436 132 ON "issue"."id" = "direct_voter"."issue_id"
jbe@436 133 JOIN "battle_participant" AS "winning_initiative"
jbe@436 134 ON "issue"."id" = "winning_initiative"."issue_id"
jbe@436 135 JOIN "battle_participant" AS "losing_initiative"
jbe@436 136 ON "issue"."id" = "losing_initiative"."issue_id"
jbe@436 137 LEFT JOIN "vote" AS "better_vote"
jbe@436 138 ON "direct_voter"."member_id" = "better_vote"."member_id"
jbe@436 139 AND "winning_initiative"."id" = "better_vote"."initiative_id"
jbe@436 140 LEFT JOIN "vote" AS "worse_vote"
jbe@436 141 ON "direct_voter"."member_id" = "worse_vote"."member_id"
jbe@436 142 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
jbe@436 143 WHERE "issue"."state" IN ('finished_with_winner', 'finished_without_winner')
jbe@436 144 AND "winning_initiative"."id" != "losing_initiative"."id"
jbe@436 145 -- NOTE: comparisons with status-quo are intentionally omitted to mark
jbe@436 146 -- issues that were counted prior LiquidFeedback Core v2.0.0
jbe@436 147 GROUP BY
jbe@436 148 "issue"."id",
jbe@436 149 "winning_initiative"."id",
jbe@436 150 "losing_initiative"."id"
jbe@436 151 ) AS "battle_view"
jbe@436 152 LEFT JOIN "battle"
jbe@436 153 ON "battle_view"."winning_initiative_id" = "battle"."winning_initiative_id"
jbe@436 154 AND "battle_view"."losing_initiative_id" = "battle"."losing_initiative_id"
jbe@436 155 WHERE "battle" ISNULL;
jbe@436 156
jbe@436 157 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@436 158 RETURNS VOID
jbe@436 159 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@436 160 DECLARE
jbe@436 161 "area_id_v" "area"."id"%TYPE;
jbe@436 162 "unit_id_v" "unit"."id"%TYPE;
jbe@436 163 "member_id_v" "member"."id"%TYPE;
jbe@436 164 BEGIN
jbe@436 165 PERFORM "require_transaction_isolation"();
jbe@436 166 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
jbe@436 167 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
jbe@436 168 -- override protection triggers:
jbe@436 169 INSERT INTO "temporary_transaction_data" ("key", "value")
jbe@436 170 VALUES ('override_protection_triggers', TRUE::TEXT);
jbe@436 171 -- delete timestamp of voting comment:
jbe@436 172 UPDATE "direct_voter" SET "comment_changed" = NULL
jbe@436 173 WHERE "issue_id" = "issue_id_p";
jbe@436 174 -- delete delegating votes (in cases of manual reset of issue state):
jbe@436 175 DELETE FROM "delegating_voter"
jbe@436 176 WHERE "issue_id" = "issue_id_p";
jbe@436 177 -- delete votes from non-privileged voters:
jbe@436 178 DELETE FROM "direct_voter"
jbe@436 179 USING (
jbe@436 180 SELECT
jbe@436 181 "direct_voter"."member_id"
jbe@436 182 FROM "direct_voter"
jbe@436 183 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
jbe@436 184 LEFT JOIN "privilege"
jbe@436 185 ON "privilege"."unit_id" = "unit_id_v"
jbe@436 186 AND "privilege"."member_id" = "direct_voter"."member_id"
jbe@436 187 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
jbe@436 188 "member"."active" = FALSE OR
jbe@436 189 "privilege"."voting_right" ISNULL OR
jbe@436 190 "privilege"."voting_right" = FALSE
jbe@436 191 )
jbe@436 192 ) AS "subquery"
jbe@436 193 WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@436 194 AND "direct_voter"."member_id" = "subquery"."member_id";
jbe@436 195 -- consider delegations:
jbe@436 196 UPDATE "direct_voter" SET "weight" = 1
jbe@436 197 WHERE "issue_id" = "issue_id_p";
jbe@436 198 PERFORM "add_vote_delegations"("issue_id_p");
jbe@436 199 -- mark first preferences:
jbe@436 200 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
jbe@436 201 FROM (
jbe@436 202 SELECT
jbe@436 203 "vote"."initiative_id",
jbe@436 204 "vote"."member_id",
jbe@436 205 CASE WHEN "vote"."grade" > 0 THEN
jbe@436 206 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
jbe@436 207 ELSE NULL
jbe@436 208 END AS "first_preference"
jbe@436 209 FROM "vote"
jbe@436 210 JOIN "initiative" -- NOTE: due to missing index on issue_id
jbe@436 211 ON "vote"."issue_id" = "initiative"."issue_id"
jbe@436 212 JOIN "vote" AS "agg"
jbe@436 213 ON "initiative"."id" = "agg"."initiative_id"
jbe@436 214 AND "vote"."member_id" = "agg"."member_id"
jbe@436 215 GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
jbe@436 216 ) AS "subquery"
jbe@436 217 WHERE "vote"."issue_id" = "issue_id_p"
jbe@436 218 AND "vote"."initiative_id" = "subquery"."initiative_id"
jbe@436 219 AND "vote"."member_id" = "subquery"."member_id";
jbe@436 220 -- finish overriding protection triggers (avoids garbage):
jbe@436 221 DELETE FROM "temporary_transaction_data"
jbe@436 222 WHERE "key" = 'override_protection_triggers';
jbe@436 223 -- materialize battle_view:
jbe@436 224 -- NOTE: "closed" column of issue must be set at this point
jbe@436 225 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@436 226 INSERT INTO "battle" (
jbe@436 227 "issue_id",
jbe@436 228 "winning_initiative_id", "losing_initiative_id",
jbe@436 229 "count"
jbe@436 230 ) SELECT
jbe@436 231 "issue_id",
jbe@436 232 "winning_initiative_id", "losing_initiative_id",
jbe@436 233 "count"
jbe@436 234 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@436 235 -- set voter count:
jbe@436 236 UPDATE "issue" SET
jbe@436 237 "voter_count" = (
jbe@436 238 SELECT coalesce(sum("weight"), 0)
jbe@436 239 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@436 240 )
jbe@436 241 WHERE "id" = "issue_id_p";
jbe@437 242 -- copy "positive_votes" and "negative_votes" from "battle" table:
jbe@437 243 -- NOTE: "first_preference_votes" is set to a default of 0 at this step
jbe@437 244 UPDATE "initiative" SET
jbe@437 245 "first_preference_votes" = 0,
jbe@437 246 "positive_votes" = "battle_win"."count",
jbe@437 247 "negative_votes" = "battle_lose"."count"
jbe@437 248 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
jbe@437 249 WHERE
jbe@437 250 "battle_win"."issue_id" = "issue_id_p" AND
jbe@437 251 "battle_win"."winning_initiative_id" = "initiative"."id" AND
jbe@437 252 "battle_win"."losing_initiative_id" ISNULL AND
jbe@437 253 "battle_lose"."issue_id" = "issue_id_p" AND
jbe@437 254 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
jbe@437 255 "battle_lose"."winning_initiative_id" ISNULL;
jbe@436 256 -- calculate "first_preference_votes":
jbe@437 257 -- NOTE: will only set values not equal to zero
jbe@437 258 UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum"
jbe@436 259 FROM (
jbe@436 260 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
jbe@436 261 FROM "vote" JOIN "direct_voter"
jbe@436 262 ON "vote"."issue_id" = "direct_voter"."issue_id"
jbe@436 263 AND "vote"."member_id" = "direct_voter"."member_id"
jbe@436 264 WHERE "vote"."first_preference"
jbe@436 265 GROUP BY "vote"."initiative_id"
jbe@436 266 ) AS "subquery"
jbe@436 267 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@436 268 AND "initiative"."admitted"
jbe@436 269 AND "initiative"."id" = "subquery"."initiative_id";
jbe@436 270 END;
jbe@436 271 $$;
jbe@436 272
jbe@436 273 COMMIT;

Impressum / About Us