liquid_feedback_core

annotate update/core-update.v1.4.0_rc1-v1.4.0_rc2.sql @ 352:98c14d8d07f1

find changesets by author, revision, files, or words in the commit message
Support for proportional ordering of suggestions in core.sql; Begin of work on "lf_update_suggestion_order" (a second background job for sorting suggestions based on a proportional preferential voting system)
author jbe
date Sat Mar 16 17:22:01 2013 +0100 (2013-03-16)
parents 07cfc7c1ce3f
children
rev   line source
jbe@129 1 BEGIN;
jbe@129 2
jbe@129 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@129 4 SELECT * FROM (VALUES ('1.4.0_rc2', 1, 4, -1))
jbe@129 5 AS "subquery"("string", "major", "minor", "revision");
jbe@129 6
jbe@129 7 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@129 8 RETURNS VOID
jbe@129 9 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@129 10 DECLARE
jbe@129 11 "area_id_v" "area"."id"%TYPE;
jbe@129 12 "unit_id_v" "unit"."id"%TYPE;
jbe@129 13 "member_id_v" "member"."id"%TYPE;
jbe@129 14 BEGIN
jbe@129 15 PERFORM "lock_issue"("issue_id_p");
jbe@129 16 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
jbe@129 17 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
jbe@129 18 DELETE FROM "delegating_voter"
jbe@129 19 WHERE "issue_id" = "issue_id_p";
jbe@129 20 DELETE FROM "direct_voter"
jbe@129 21 WHERE "issue_id" = "issue_id_p"
jbe@129 22 AND "autoreject" = TRUE;
jbe@129 23 DELETE FROM "direct_voter"
jbe@129 24 USING (
jbe@129 25 SELECT
jbe@129 26 "direct_voter"."member_id"
jbe@129 27 FROM "direct_voter"
jbe@129 28 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
jbe@129 29 LEFT JOIN "privilege"
jbe@129 30 ON "privilege"."unit_id" = "unit_id_v"
jbe@129 31 AND "privilege"."member_id" = "direct_voter"."member_id"
jbe@129 32 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
jbe@129 33 "member"."active" = FALSE OR
jbe@129 34 "privilege"."voting_right" ISNULL OR
jbe@129 35 "privilege"."voting_right" = FALSE
jbe@129 36 )
jbe@129 37 ) AS "subquery"
jbe@129 38 WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@129 39 AND "direct_voter"."member_id" = "subquery"."member_id";
jbe@129 40 UPDATE "direct_voter" SET "weight" = 1
jbe@129 41 WHERE "issue_id" = "issue_id_p";
jbe@129 42 PERFORM "add_vote_delegations"("issue_id_p");
jbe@129 43 FOR "member_id_v" IN
jbe@129 44 SELECT "interest"."member_id"
jbe@129 45 FROM "interest"
jbe@129 46 JOIN "member"
jbe@129 47 ON "interest"."member_id" = "member"."id"
jbe@129 48 LEFT JOIN "direct_voter"
jbe@129 49 ON "interest"."member_id" = "direct_voter"."member_id"
jbe@129 50 AND "interest"."issue_id" = "direct_voter"."issue_id"
jbe@129 51 LEFT JOIN "delegating_voter"
jbe@129 52 ON "interest"."member_id" = "delegating_voter"."member_id"
jbe@129 53 AND "interest"."issue_id" = "delegating_voter"."issue_id"
jbe@129 54 WHERE "interest"."issue_id" = "issue_id_p"
jbe@129 55 AND "interest"."autoreject" = TRUE
jbe@129 56 AND "member"."active"
jbe@129 57 AND "direct_voter"."member_id" ISNULL
jbe@129 58 AND "delegating_voter"."member_id" ISNULL
jbe@129 59 UNION SELECT "membership"."member_id"
jbe@129 60 FROM "membership"
jbe@129 61 JOIN "member"
jbe@129 62 ON "membership"."member_id" = "member"."id"
jbe@129 63 LEFT JOIN "interest"
jbe@129 64 ON "membership"."member_id" = "interest"."member_id"
jbe@129 65 AND "interest"."issue_id" = "issue_id_p"
jbe@129 66 LEFT JOIN "direct_voter"
jbe@129 67 ON "membership"."member_id" = "direct_voter"."member_id"
jbe@129 68 AND "direct_voter"."issue_id" = "issue_id_p"
jbe@129 69 LEFT JOIN "delegating_voter"
jbe@129 70 ON "membership"."member_id" = "delegating_voter"."member_id"
jbe@129 71 AND "delegating_voter"."issue_id" = "issue_id_p"
jbe@129 72 WHERE "membership"."area_id" = "area_id_v"
jbe@129 73 AND "membership"."autoreject" = TRUE
jbe@129 74 AND "member"."active"
jbe@129 75 AND "interest"."autoreject" ISNULL
jbe@129 76 AND "direct_voter"."member_id" ISNULL
jbe@129 77 AND "delegating_voter"."member_id" ISNULL
jbe@129 78 LOOP
jbe@129 79 INSERT INTO "direct_voter"
jbe@129 80 ("member_id", "issue_id", "weight", "autoreject") VALUES
jbe@129 81 ("member_id_v", "issue_id_p", 1, TRUE);
jbe@129 82 INSERT INTO "vote" (
jbe@129 83 "member_id",
jbe@129 84 "issue_id",
jbe@129 85 "initiative_id",
jbe@129 86 "grade"
jbe@129 87 ) SELECT
jbe@129 88 "member_id_v" AS "member_id",
jbe@129 89 "issue_id_p" AS "issue_id",
jbe@129 90 "id" AS "initiative_id",
jbe@129 91 -1 AS "grade"
jbe@129 92 FROM "initiative" WHERE "issue_id" = "issue_id_p";
jbe@129 93 END LOOP;
jbe@129 94 PERFORM "add_vote_delegations"("issue_id_p");
jbe@129 95 UPDATE "issue" SET
jbe@129 96 "state" = 'calculation',
jbe@129 97 "closed" = now(),
jbe@129 98 "voter_count" = (
jbe@129 99 SELECT coalesce(sum("weight"), 0)
jbe@129 100 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@129 101 )
jbe@129 102 WHERE "id" = "issue_id_p";
jbe@129 103 UPDATE "initiative" SET
jbe@129 104 "positive_votes" = "vote_counts"."positive_votes",
jbe@129 105 "negative_votes" = "vote_counts"."negative_votes",
jbe@129 106 "agreed" = CASE WHEN "majority_strict" THEN
jbe@129 107 "vote_counts"."positive_votes" * "majority_den" >
jbe@129 108 "majority_num" *
jbe@129 109 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@129 110 ELSE
jbe@129 111 "vote_counts"."positive_votes" * "majority_den" >=
jbe@129 112 "majority_num" *
jbe@129 113 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@129 114 END
jbe@129 115 FROM
jbe@129 116 ( SELECT
jbe@129 117 "initiative"."id" AS "initiative_id",
jbe@129 118 coalesce(
jbe@129 119 sum(
jbe@129 120 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@129 121 ),
jbe@129 122 0
jbe@129 123 ) AS "positive_votes",
jbe@129 124 coalesce(
jbe@129 125 sum(
jbe@129 126 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@129 127 ),
jbe@129 128 0
jbe@129 129 ) AS "negative_votes"
jbe@129 130 FROM "initiative"
jbe@129 131 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
jbe@129 132 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
jbe@129 133 LEFT JOIN "direct_voter"
jbe@129 134 ON "direct_voter"."issue_id" = "initiative"."issue_id"
jbe@129 135 LEFT JOIN "vote"
jbe@129 136 ON "vote"."initiative_id" = "initiative"."id"
jbe@129 137 AND "vote"."member_id" = "direct_voter"."member_id"
jbe@129 138 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@129 139 AND "initiative"."admitted" -- NOTE: NULL case is handled too
jbe@129 140 GROUP BY "initiative"."id"
jbe@129 141 ) AS "vote_counts",
jbe@129 142 "issue",
jbe@129 143 "policy"
jbe@129 144 WHERE "vote_counts"."initiative_id" = "initiative"."id"
jbe@129 145 AND "issue"."id" = "initiative"."issue_id"
jbe@129 146 AND "policy"."id" = "issue"."policy_id";
jbe@129 147 -- NOTE: "closed" column of issue must be set at this point
jbe@129 148 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@129 149 INSERT INTO "battle" (
jbe@129 150 "issue_id",
jbe@129 151 "winning_initiative_id", "losing_initiative_id",
jbe@129 152 "count"
jbe@129 153 ) SELECT
jbe@129 154 "issue_id",
jbe@129 155 "winning_initiative_id", "losing_initiative_id",
jbe@129 156 "count"
jbe@129 157 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@129 158 END;
jbe@129 159 $$;
jbe@129 160
jbe@129 161 COMMIT;

Impressum / About Us