liquid_feedback_core

annotate update/core-update.v3.0.0-v3.0.1.sql @ 423:73c2ab2d068f

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

Impressum / About Us