liquid_feedback_core

annotate update/core-update.v1.2.1-v1.2.2.sql @ 142:54ac8c473263

find changesets by author, revision, files, or words in the commit message
Use an improved definition for "disqualified" initiatives

"initiative"."disqualified" is TRUE, if the initiative may not win, because it either (a) has no better rank than the status quo, or (b) because 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
author jbe
date Wed Jun 01 16:58:00 2011 +0200 (2011-06-01)
parents 598af132a6f9
children
rev   line source
jbe@61 1 BEGIN;
jbe@61 2
jbe@61 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@61 4 SELECT * FROM (VALUES ('1.2.2', 1, 2, 2))
jbe@61 5 AS "subquery"("string", "major", "minor", "revision");
jbe@61 6
jbe@61 7 ALTER TABLE "issue" DROP CONSTRAINT "clean_restriction";
jbe@61 8
jbe@61 9 ALTER TABLE "issue" ADD CONSTRAINT "only_closed_issues_may_be_cleaned"
jbe@61 10 CHECK ("cleaned" ISNULL OR "closed" NOTNULL);
jbe@61 11
jbe@61 12 ALTER VIEW "battle" RENAME TO "battle_view";
jbe@61 13
jbe@61 14 CREATE TABLE "battle" (
jbe@61 15 PRIMARY KEY ("issue_id", "winning_initiative_id", "losing_initiative_id"),
jbe@61 16 "issue_id" INT4,
jbe@61 17 "winning_initiative_id" INT4,
jbe@61 18 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@61 19 "losing_initiative_id" INT4,
jbe@61 20 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,
jbe@61 21 "count" INT4 NOT NULL);
jbe@61 22
jbe@61 23 COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue';
jbe@61 24
jbe@61 25 CREATE OR REPLACE VIEW "battle_view" AS
jbe@61 26 SELECT
jbe@61 27 "issue"."id" AS "issue_id",
jbe@61 28 "winning_initiative"."id" AS "winning_initiative_id",
jbe@61 29 "losing_initiative"."id" AS "losing_initiative_id",
jbe@61 30 sum(
jbe@61 31 CASE WHEN
jbe@61 32 coalesce("better_vote"."grade", 0) >
jbe@61 33 coalesce("worse_vote"."grade", 0)
jbe@61 34 THEN "direct_voter"."weight" ELSE 0 END
jbe@61 35 ) AS "count"
jbe@61 36 FROM "issue"
jbe@61 37 LEFT JOIN "direct_voter"
jbe@61 38 ON "issue"."id" = "direct_voter"."issue_id"
jbe@61 39 JOIN "initiative" AS "winning_initiative"
jbe@61 40 ON "issue"."id" = "winning_initiative"."issue_id"
jbe@61 41 AND "winning_initiative"."agreed"
jbe@61 42 JOIN "initiative" AS "losing_initiative"
jbe@61 43 ON "issue"."id" = "losing_initiative"."issue_id"
jbe@61 44 AND "losing_initiative"."agreed"
jbe@61 45 LEFT JOIN "vote" AS "better_vote"
jbe@61 46 ON "direct_voter"."member_id" = "better_vote"."member_id"
jbe@61 47 AND "winning_initiative"."id" = "better_vote"."initiative_id"
jbe@61 48 LEFT JOIN "vote" AS "worse_vote"
jbe@61 49 ON "direct_voter"."member_id" = "worse_vote"."member_id"
jbe@61 50 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
jbe@61 51 WHERE "issue"."closed" NOTNULL
jbe@61 52 AND "issue"."cleaned" ISNULL
jbe@61 53 AND "winning_initiative"."id" != "losing_initiative"."id"
jbe@61 54 GROUP BY
jbe@61 55 "issue"."id",
jbe@61 56 "winning_initiative"."id",
jbe@61 57 "losing_initiative"."id";
jbe@61 58
jbe@61 59 COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative to another; Used to fill "battle" table';
jbe@61 60
jbe@61 61 INSERT INTO "battle" (
jbe@61 62 "issue_id",
jbe@61 63 "winning_initiative_id",
jbe@61 64 "losing_initiative_id",
jbe@61 65 "count"
jbe@61 66 ) SELECT
jbe@61 67 "issue_id",
jbe@61 68 "winning_initiative_id", "losing_initiative_id",
jbe@61 69 "count"
jbe@61 70 FROM "battle_view";
jbe@61 71
jbe@61 72 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@61 73 RETURNS VOID
jbe@61 74 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@61 75 DECLARE
jbe@61 76 "issue_row" "issue"%ROWTYPE;
jbe@61 77 "member_id_v" "member"."id"%TYPE;
jbe@61 78 BEGIN
jbe@61 79 PERFORM "global_lock"();
jbe@61 80 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";
jbe@61 81 DELETE FROM "delegating_voter"
jbe@61 82 WHERE "issue_id" = "issue_id_p";
jbe@61 83 DELETE FROM "direct_voter"
jbe@61 84 WHERE "issue_id" = "issue_id_p"
jbe@61 85 AND "autoreject" = TRUE;
jbe@61 86 DELETE FROM "direct_voter" USING "member"
jbe@61 87 WHERE "direct_voter"."member_id" = "member"."id"
jbe@61 88 AND "direct_voter"."issue_id" = "issue_id_p"
jbe@61 89 AND "member"."active" = FALSE;
jbe@61 90 UPDATE "direct_voter" SET "weight" = 1
jbe@61 91 WHERE "issue_id" = "issue_id_p";
jbe@61 92 PERFORM "add_vote_delegations"("issue_id_p");
jbe@61 93 FOR "member_id_v" IN
jbe@61 94 SELECT "interest"."member_id"
jbe@61 95 FROM "interest"
jbe@61 96 LEFT JOIN "direct_voter"
jbe@61 97 ON "interest"."member_id" = "direct_voter"."member_id"
jbe@61 98 AND "interest"."issue_id" = "direct_voter"."issue_id"
jbe@61 99 LEFT JOIN "delegating_voter"
jbe@61 100 ON "interest"."member_id" = "delegating_voter"."member_id"
jbe@61 101 AND "interest"."issue_id" = "delegating_voter"."issue_id"
jbe@61 102 WHERE "interest"."issue_id" = "issue_id_p"
jbe@61 103 AND "interest"."autoreject" = TRUE
jbe@61 104 AND "direct_voter"."member_id" ISNULL
jbe@61 105 AND "delegating_voter"."member_id" ISNULL
jbe@61 106 UNION SELECT "membership"."member_id"
jbe@61 107 FROM "membership"
jbe@61 108 LEFT JOIN "interest"
jbe@61 109 ON "membership"."member_id" = "interest"."member_id"
jbe@61 110 AND "interest"."issue_id" = "issue_id_p"
jbe@61 111 LEFT JOIN "direct_voter"
jbe@61 112 ON "membership"."member_id" = "direct_voter"."member_id"
jbe@61 113 AND "direct_voter"."issue_id" = "issue_id_p"
jbe@61 114 LEFT JOIN "delegating_voter"
jbe@61 115 ON "membership"."member_id" = "delegating_voter"."member_id"
jbe@61 116 AND "delegating_voter"."issue_id" = "issue_id_p"
jbe@61 117 WHERE "membership"."area_id" = "issue_row"."area_id"
jbe@61 118 AND "membership"."autoreject" = TRUE
jbe@61 119 AND "interest"."autoreject" ISNULL
jbe@61 120 AND "direct_voter"."member_id" ISNULL
jbe@61 121 AND "delegating_voter"."member_id" ISNULL
jbe@61 122 LOOP
jbe@61 123 INSERT INTO "direct_voter"
jbe@61 124 ("member_id", "issue_id", "weight", "autoreject") VALUES
jbe@61 125 ("member_id_v", "issue_id_p", 1, TRUE);
jbe@61 126 INSERT INTO "vote" (
jbe@61 127 "member_id",
jbe@61 128 "issue_id",
jbe@61 129 "initiative_id",
jbe@61 130 "grade"
jbe@61 131 ) SELECT
jbe@61 132 "member_id_v" AS "member_id",
jbe@61 133 "issue_id_p" AS "issue_id",
jbe@61 134 "id" AS "initiative_id",
jbe@61 135 -1 AS "grade"
jbe@61 136 FROM "initiative" WHERE "issue_id" = "issue_id_p";
jbe@61 137 END LOOP;
jbe@61 138 PERFORM "add_vote_delegations"("issue_id_p");
jbe@61 139 UPDATE "issue" SET
jbe@61 140 "closed" = now(),
jbe@61 141 "voter_count" = (
jbe@61 142 SELECT coalesce(sum("weight"), 0)
jbe@61 143 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@61 144 )
jbe@61 145 WHERE "id" = "issue_id_p";
jbe@61 146 UPDATE "initiative" SET
jbe@61 147 "positive_votes" = "vote_counts"."positive_votes",
jbe@61 148 "negative_votes" = "vote_counts"."negative_votes",
jbe@61 149 "agreed" = CASE WHEN "majority_strict" THEN
jbe@61 150 "vote_counts"."positive_votes" * "majority_den" >
jbe@61 151 "majority_num" *
jbe@61 152 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@61 153 ELSE
jbe@61 154 "vote_counts"."positive_votes" * "majority_den" >=
jbe@61 155 "majority_num" *
jbe@61 156 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@61 157 END
jbe@61 158 FROM
jbe@61 159 ( SELECT
jbe@61 160 "initiative"."id" AS "initiative_id",
jbe@61 161 coalesce(
jbe@61 162 sum(
jbe@61 163 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@61 164 ),
jbe@61 165 0
jbe@61 166 ) AS "positive_votes",
jbe@61 167 coalesce(
jbe@61 168 sum(
jbe@61 169 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@61 170 ),
jbe@61 171 0
jbe@61 172 ) AS "negative_votes"
jbe@61 173 FROM "initiative"
jbe@61 174 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
jbe@61 175 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
jbe@61 176 LEFT JOIN "direct_voter"
jbe@61 177 ON "direct_voter"."issue_id" = "initiative"."issue_id"
jbe@61 178 LEFT JOIN "vote"
jbe@61 179 ON "vote"."initiative_id" = "initiative"."id"
jbe@61 180 AND "vote"."member_id" = "direct_voter"."member_id"
jbe@61 181 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@61 182 AND "initiative"."admitted" -- NOTE: NULL case is handled too
jbe@61 183 GROUP BY "initiative"."id"
jbe@61 184 ) AS "vote_counts",
jbe@61 185 "issue",
jbe@61 186 "policy"
jbe@61 187 WHERE "vote_counts"."initiative_id" = "initiative"."id"
jbe@61 188 AND "issue"."id" = "initiative"."issue_id"
jbe@61 189 AND "policy"."id" = "issue"."policy_id";
jbe@61 190 -- NOTE: "closed" column of issue must be set at this point
jbe@61 191 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@61 192 INSERT INTO "battle" (
jbe@61 193 "issue_id",
jbe@61 194 "winning_initiative_id", "losing_initiative_id",
jbe@61 195 "count"
jbe@61 196 ) SELECT
jbe@61 197 "issue_id",
jbe@61 198 "winning_initiative_id", "losing_initiative_id",
jbe@61 199 "count"
jbe@61 200 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@61 201 END;
jbe@61 202 $$;
jbe@61 203
jbe@61 204 COMMIT;

Impressum / About Us