liquid_feedback_core

annotate update/core-update.v3.0.0-v3.0.1.sql @ 416:db9ccf3c05f4

find changesets by author, revision, files, or words in the commit message
Added update script to introduce "first_preference_votes"
author jbe
date Wed Mar 26 14:45:49 2014 +0100 (2014-03-26)
parents
children bc94ea65757b
rev   line source
jbe@416 1 BEGIN;
jbe@416 2
jbe@416 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@416 4 SELECT * FROM (VALUES ('3.0.1', 3, 0, 1))
jbe@416 5 AS "subquery"("string", "major", "minor", "revision");
jbe@416 6
jbe@416 7 ALTER TABLE "initiative" ADD COLUMN "first_preference_votes" INT4;
jbe@416 8
jbe@416 9 ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results";
jbe@416 10 ALTER TABLE "initiative" ADD CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
jbe@416 11 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
jbe@416 12 ( "first_preference_votes" ISNULL AND
jbe@416 13 "positive_votes" ISNULL AND "negative_votes" ISNULL AND
jbe@416 14 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
jbe@416 15 "schulze_rank" ISNULL AND
jbe@416 16 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
jbe@416 17 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
jbe@416 18 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) );
jbe@416 19
jbe@416 20 COMMENT ON COLUMN "initiative"."first_preference_votes" IS 'Number of direct and delegating voters who ranked this initiative as their first choice';
jbe@416 21 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Number of direct and delegating voters who ranked this initiative better than the status quo';
jbe@416 22 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Number of direct and delegating voters who ranked this initiative worse than the status quo';
jbe@416 23
jbe@416 24 -- UPDATE TABLE "vote" SET "grade" = 0 WHERE "grade" ISNULL; -- should not be necessary
jbe@416 25 ALTER TABLE "vote" ALTER COLUMN "grade" SET NOT NULL;
jbe@416 26
jbe@416 27 ALTER TABLE "vote" ADD COLUMN "first_preference" BOOLEAN;
jbe@416 28
jbe@416 29 ALTER TABLE "vote" ADD
jbe@416 30 CONSTRAINT "first_preference_flag_only_set_on_positive_grades"
jbe@416 31 CHECK ("grade" > 0 OR "first_preference" ISNULL);
jbe@416 32
jbe@416 33 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 34
jbe@416 35 INSERT INTO "temporary_transaction_data" ("key", "value")
jbe@416 36 VALUES ('override_protection_triggers', TRUE::TEXT);
jbe@416 37
jbe@416 38 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
jbe@416 39 FROM (
jbe@416 40 SELECT
jbe@416 41 "vote"."initiative_id",
jbe@416 42 "vote"."member_id",
jbe@416 43 CASE WHEN "vote"."grade" > 0 THEN
jbe@416 44 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
jbe@416 45 ELSE NULL
jbe@416 46 END AS "first_preference"
jbe@416 47 FROM "vote"
jbe@416 48 JOIN "initiative" -- NOTE: due to missing index on issue_id
jbe@416 49 ON "vote"."issue_id" = "initiative"."issue_id"
jbe@416 50 JOIN "vote" AS "agg"
jbe@416 51 ON "initiative"."id" = "agg"."initiative_id"
jbe@416 52 AND "vote"."member_id" = "agg"."member_id"
jbe@416 53 GROUP BY "vote"."initiative_id", "vote"."member_id"
jbe@416 54 ) AS "subquery"
jbe@416 55 WHERE "vote"."initiative_id" = "subquery"."initiative_id"
jbe@416 56 AND "vote"."member_id" = "subquery"."member_id";
jbe@416 57
jbe@416 58 DELETE FROM "temporary_transaction_data"
jbe@416 59 WHERE "key" = 'override_protection_triggers';
jbe@416 60
jbe@416 61 UPDATE "initiative"
jbe@416 62 SET "first_preference_votes" = coalesce("subquery"."sum", 0)
jbe@416 63 FROM (
jbe@416 64 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
jbe@416 65 FROM "vote" JOIN "direct_voter"
jbe@416 66 ON "vote"."issue_id" = "direct_voter"."issue_id"
jbe@416 67 AND "vote"."member_id" = "direct_voter"."member_id"
jbe@416 68 WHERE "vote"."first_preference"
jbe@416 69 GROUP BY "vote"."initiative_id"
jbe@416 70 ) AS "subquery"
jbe@416 71 WHERE "initiative"."admitted"
jbe@416 72 AND "initiative"."id" = "subquery"."initiative_id";
jbe@416 73
jbe@416 74 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@416 75 RETURNS VOID
jbe@416 76 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@416 77 DECLARE
jbe@416 78 "area_id_v" "area"."id"%TYPE;
jbe@416 79 "unit_id_v" "unit"."id"%TYPE;
jbe@416 80 "member_id_v" "member"."id"%TYPE;
jbe@416 81 BEGIN
jbe@416 82 PERFORM "require_transaction_isolation"();
jbe@416 83 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
jbe@416 84 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
jbe@416 85 -- override protection triggers:
jbe@416 86 INSERT INTO "temporary_transaction_data" ("key", "value")
jbe@416 87 VALUES ('override_protection_triggers', TRUE::TEXT);
jbe@416 88 -- delete timestamp of voting comment:
jbe@416 89 UPDATE "direct_voter" SET "comment_changed" = NULL
jbe@416 90 WHERE "issue_id" = "issue_id_p";
jbe@416 91 -- delete delegating votes (in cases of manual reset of issue state):
jbe@416 92 DELETE FROM "delegating_voter"
jbe@416 93 WHERE "issue_id" = "issue_id_p";
jbe@416 94 -- delete votes from non-privileged voters:
jbe@416 95 DELETE FROM "direct_voter"
jbe@416 96 USING (
jbe@416 97 SELECT
jbe@416 98 "direct_voter"."member_id"
jbe@416 99 FROM "direct_voter"
jbe@416 100 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
jbe@416 101 LEFT JOIN "privilege"
jbe@416 102 ON "privilege"."unit_id" = "unit_id_v"
jbe@416 103 AND "privilege"."member_id" = "direct_voter"."member_id"
jbe@416 104 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
jbe@416 105 "member"."active" = FALSE OR
jbe@416 106 "privilege"."voting_right" ISNULL OR
jbe@416 107 "privilege"."voting_right" = FALSE
jbe@416 108 )
jbe@416 109 ) AS "subquery"
jbe@416 110 WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@416 111 AND "direct_voter"."member_id" = "subquery"."member_id";
jbe@416 112 -- consider delegations:
jbe@416 113 UPDATE "direct_voter" SET "weight" = 1
jbe@416 114 WHERE "issue_id" = "issue_id_p";
jbe@416 115 PERFORM "add_vote_delegations"("issue_id_p");
jbe@416 116 -- mark first preferences:
jbe@416 117 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
jbe@416 118 FROM (
jbe@416 119 SELECT
jbe@416 120 "vote"."initiative_id",
jbe@416 121 "vote"."member_id",
jbe@416 122 CASE WHEN "vote"."grade" > 0 THEN
jbe@416 123 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
jbe@416 124 ELSE NULL
jbe@416 125 END AS "first_preference"
jbe@416 126 FROM "vote"
jbe@416 127 JOIN "initiative" -- NOTE: due to missing index on issue_id
jbe@416 128 ON "vote"."issue_id" = "initiative"."issue_id"
jbe@416 129 JOIN "vote" AS "agg"
jbe@416 130 ON "initiative"."id" = "agg"."initiative_id"
jbe@416 131 AND "vote"."member_id" = "agg"."member_id"
jbe@416 132 GROUP BY "vote"."initiative_id", "vote"."member_id"
jbe@416 133 ) AS "subquery"
jbe@416 134 WHERE "vote"."issue_id" = "issue_id_p"
jbe@416 135 AND "vote"."initiative_id" = "subquery"."initiative_id"
jbe@416 136 AND "vote"."member_id" = "subquery"."member_id";
jbe@416 137 -- finish overriding protection triggers (avoids garbage):
jbe@416 138 DELETE FROM "temporary_transaction_data"
jbe@416 139 WHERE "key" = 'override_protection_triggers';
jbe@416 140 -- materialize battle_view:
jbe@416 141 -- NOTE: "closed" column of issue must be set at this point
jbe@416 142 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@416 143 INSERT INTO "battle" (
jbe@416 144 "issue_id",
jbe@416 145 "winning_initiative_id", "losing_initiative_id",
jbe@416 146 "count"
jbe@416 147 ) SELECT
jbe@416 148 "issue_id",
jbe@416 149 "winning_initiative_id", "losing_initiative_id",
jbe@416 150 "count"
jbe@416 151 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@416 152 -- set voter count:
jbe@416 153 UPDATE "issue" SET
jbe@416 154 "voter_count" = (
jbe@416 155 SELECT coalesce(sum("weight"), 0)
jbe@416 156 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@416 157 )
jbe@416 158 WHERE "id" = "issue_id_p";
jbe@416 159 -- calculate "first_preference_votes":
jbe@416 160 UPDATE "initiative"
jbe@416 161 SET "first_preference_votes" = coalesce("subquery"."sum", 0)
jbe@416 162 FROM (
jbe@416 163 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
jbe@416 164 FROM "vote" JOIN "direct_voter"
jbe@416 165 ON "vote"."issue_id" = "direct_voter"."issue_id"
jbe@416 166 AND "vote"."member_id" = "direct_voter"."member_id"
jbe@416 167 WHERE "vote"."first_preference"
jbe@416 168 GROUP BY "vote"."initiative_id"
jbe@416 169 ) AS "subquery"
jbe@416 170 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@416 171 AND "initiative"."admitted"
jbe@416 172 AND "initiative"."id" = "subquery"."initiative_id";
jbe@416 173 -- copy "positive_votes" and "negative_votes" from "battle" table:
jbe@416 174 UPDATE "initiative" SET
jbe@416 175 "positive_votes" = "battle_win"."count",
jbe@416 176 "negative_votes" = "battle_lose"."count"
jbe@416 177 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
jbe@416 178 WHERE
jbe@416 179 "battle_win"."issue_id" = "issue_id_p" AND
jbe@416 180 "battle_win"."winning_initiative_id" = "initiative"."id" AND
jbe@416 181 "battle_win"."losing_initiative_id" ISNULL AND
jbe@416 182 "battle_lose"."issue_id" = "issue_id_p" AND
jbe@416 183 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
jbe@416 184 "battle_lose"."winning_initiative_id" ISNULL;
jbe@416 185 END;
jbe@416 186 $$;
jbe@416 187
jbe@416 188 COMMIT;

Impressum / About Us