liquid_feedback_core

annotate update/core-update.v1.2.1-v1.2.2.sql @ 303:585c1cf4a3c9

find changesets by author, revision, files, or words in the commit message
Added TODO for broken implementation of tie-breaking
author jbe
date Sun Sep 30 00:56:01 2012 +0200 (2012-09-30)
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