liquid_feedback_core

annotate update/core-update.v1.4.0_rc3-v1.4.0_rc4.sql @ 378:e88d0606891f

find changesets by author, revision, files, or words in the commit message
Bugfix regarding "proportional_order" of suggestions:
Use NULL values explicitly to be sorted last
(includes new suggestions as well as suggestions without any individual rankings)
author jbe
date Mon Mar 18 09:36:21 2013 +0100 (2013-03-18)
parents 1be788313b84
children
rev   line source
jbe@164 1 BEGIN;
jbe@164 2
jbe@164 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@164 4 SELECT * FROM (VALUES ('1.4.0_rc4', 1, 4, -1))
jbe@164 5 AS "subquery"("string", "major", "minor", "revision");
jbe@164 6
jbe@164 7 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@164 8 RETURNS VOID
jbe@164 9 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@164 10 DECLARE
jbe@164 11 "area_id_v" "area"."id"%TYPE;
jbe@164 12 "unit_id_v" "unit"."id"%TYPE;
jbe@164 13 "member_id_v" "member"."id"%TYPE;
jbe@164 14 BEGIN
jbe@164 15 PERFORM "lock_issue"("issue_id_p");
jbe@164 16 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
jbe@164 17 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
jbe@164 18 DELETE FROM "delegating_voter"
jbe@164 19 WHERE "issue_id" = "issue_id_p";
jbe@164 20 DELETE FROM "direct_voter"
jbe@164 21 WHERE "issue_id" = "issue_id_p"
jbe@164 22 AND "autoreject" = TRUE;
jbe@164 23 DELETE FROM "direct_voter"
jbe@164 24 USING (
jbe@164 25 SELECT
jbe@164 26 "direct_voter"."member_id"
jbe@164 27 FROM "direct_voter"
jbe@164 28 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
jbe@164 29 LEFT JOIN "privilege"
jbe@164 30 ON "privilege"."unit_id" = "unit_id_v"
jbe@164 31 AND "privilege"."member_id" = "direct_voter"."member_id"
jbe@164 32 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
jbe@164 33 "member"."active" = FALSE OR
jbe@164 34 "privilege"."voting_right" ISNULL OR
jbe@164 35 "privilege"."voting_right" = FALSE
jbe@164 36 )
jbe@164 37 ) AS "subquery"
jbe@164 38 WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@164 39 AND "direct_voter"."member_id" = "subquery"."member_id";
jbe@164 40 UPDATE "direct_voter" SET "weight" = 1
jbe@164 41 WHERE "issue_id" = "issue_id_p";
jbe@164 42 PERFORM "add_vote_delegations"("issue_id_p");
jbe@164 43 FOR "member_id_v" IN
jbe@164 44 SELECT "interest"."member_id"
jbe@164 45 FROM "interest"
jbe@164 46 JOIN "member"
jbe@164 47 ON "interest"."member_id" = "member"."id"
jbe@164 48 JOIN "privilege"
jbe@164 49 ON "privilege"."unit_id" = "unit_id_v"
jbe@164 50 AND "privilege"."member_id" = "member"."id"
jbe@164 51 LEFT JOIN "direct_voter"
jbe@164 52 ON "interest"."member_id" = "direct_voter"."member_id"
jbe@164 53 AND "interest"."issue_id" = "direct_voter"."issue_id"
jbe@164 54 LEFT JOIN "delegating_voter"
jbe@164 55 ON "interest"."member_id" = "delegating_voter"."member_id"
jbe@164 56 AND "interest"."issue_id" = "delegating_voter"."issue_id"
jbe@164 57 WHERE "interest"."issue_id" = "issue_id_p"
jbe@164 58 AND "interest"."autoreject" = TRUE
jbe@164 59 AND "member"."active"
jbe@164 60 AND "privilege"."voting_right"
jbe@164 61 AND "direct_voter"."member_id" ISNULL
jbe@164 62 AND "delegating_voter"."member_id" ISNULL
jbe@164 63 UNION SELECT "membership"."member_id"
jbe@164 64 FROM "membership"
jbe@164 65 JOIN "member"
jbe@164 66 ON "membership"."member_id" = "member"."id"
jbe@164 67 JOIN "privilege"
jbe@164 68 ON "privilege"."unit_id" = "unit_id_v"
jbe@164 69 AND "privilege"."member_id" = "member"."id"
jbe@164 70 LEFT JOIN "interest"
jbe@164 71 ON "membership"."member_id" = "interest"."member_id"
jbe@164 72 AND "interest"."issue_id" = "issue_id_p"
jbe@164 73 LEFT JOIN "direct_voter"
jbe@164 74 ON "membership"."member_id" = "direct_voter"."member_id"
jbe@164 75 AND "direct_voter"."issue_id" = "issue_id_p"
jbe@164 76 LEFT JOIN "delegating_voter"
jbe@164 77 ON "membership"."member_id" = "delegating_voter"."member_id"
jbe@164 78 AND "delegating_voter"."issue_id" = "issue_id_p"
jbe@164 79 WHERE "membership"."area_id" = "area_id_v"
jbe@164 80 AND "membership"."autoreject" = TRUE
jbe@164 81 AND "member"."active"
jbe@164 82 AND "privilege"."voting_right"
jbe@164 83 AND "interest"."autoreject" ISNULL
jbe@164 84 AND "direct_voter"."member_id" ISNULL
jbe@164 85 AND "delegating_voter"."member_id" ISNULL
jbe@164 86 LOOP
jbe@164 87 INSERT INTO "direct_voter"
jbe@164 88 ("member_id", "issue_id", "weight", "autoreject") VALUES
jbe@164 89 ("member_id_v", "issue_id_p", 1, TRUE);
jbe@164 90 INSERT INTO "vote" (
jbe@164 91 "member_id",
jbe@164 92 "issue_id",
jbe@164 93 "initiative_id",
jbe@164 94 "grade"
jbe@164 95 ) SELECT
jbe@164 96 "member_id_v" AS "member_id",
jbe@164 97 "issue_id_p" AS "issue_id",
jbe@164 98 "id" AS "initiative_id",
jbe@164 99 -1 AS "grade"
jbe@164 100 FROM "initiative"
jbe@164 101 WHERE "issue_id" = "issue_id_p" AND "admitted";
jbe@164 102 END LOOP;
jbe@164 103 PERFORM "add_vote_delegations"("issue_id_p");
jbe@164 104 UPDATE "issue" SET
jbe@164 105 "state" = 'calculation',
jbe@164 106 "closed" = now(),
jbe@164 107 "voter_count" = (
jbe@164 108 SELECT coalesce(sum("weight"), 0)
jbe@164 109 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@164 110 )
jbe@164 111 WHERE "id" = "issue_id_p";
jbe@164 112 UPDATE "initiative" SET
jbe@164 113 "positive_votes" = "vote_counts"."positive_votes",
jbe@164 114 "negative_votes" = "vote_counts"."negative_votes",
jbe@164 115 "agreed" = CASE WHEN "majority_strict" THEN
jbe@164 116 "vote_counts"."positive_votes" * "majority_den" >
jbe@164 117 "majority_num" *
jbe@164 118 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@164 119 ELSE
jbe@164 120 "vote_counts"."positive_votes" * "majority_den" >=
jbe@164 121 "majority_num" *
jbe@164 122 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@164 123 END
jbe@164 124 FROM
jbe@164 125 ( SELECT
jbe@164 126 "initiative"."id" AS "initiative_id",
jbe@164 127 coalesce(
jbe@164 128 sum(
jbe@164 129 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@164 130 ),
jbe@164 131 0
jbe@164 132 ) AS "positive_votes",
jbe@164 133 coalesce(
jbe@164 134 sum(
jbe@164 135 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@164 136 ),
jbe@164 137 0
jbe@164 138 ) AS "negative_votes"
jbe@164 139 FROM "initiative"
jbe@164 140 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
jbe@164 141 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
jbe@164 142 LEFT JOIN "direct_voter"
jbe@164 143 ON "direct_voter"."issue_id" = "initiative"."issue_id"
jbe@164 144 LEFT JOIN "vote"
jbe@164 145 ON "vote"."initiative_id" = "initiative"."id"
jbe@164 146 AND "vote"."member_id" = "direct_voter"."member_id"
jbe@164 147 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@164 148 AND "initiative"."admitted" -- NOTE: NULL case is handled too
jbe@164 149 GROUP BY "initiative"."id"
jbe@164 150 ) AS "vote_counts",
jbe@164 151 "issue",
jbe@164 152 "policy"
jbe@164 153 WHERE "vote_counts"."initiative_id" = "initiative"."id"
jbe@164 154 AND "issue"."id" = "initiative"."issue_id"
jbe@164 155 AND "policy"."id" = "issue"."policy_id";
jbe@164 156 -- NOTE: "closed" column of issue must be set at this point
jbe@164 157 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@164 158 INSERT INTO "battle" (
jbe@164 159 "issue_id",
jbe@164 160 "winning_initiative_id", "losing_initiative_id",
jbe@164 161 "count"
jbe@164 162 ) SELECT
jbe@164 163 "issue_id",
jbe@164 164 "winning_initiative_id", "losing_initiative_id",
jbe@164 165 "count"
jbe@164 166 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@164 167 END;
jbe@164 168 $$;
jbe@164 169
jbe@164 170 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
jbe@164 171 RETURNS VOID
jbe@164 172 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@164 173 DECLARE
jbe@164 174 "issue_row" "issue"%ROWTYPE;
jbe@164 175 BEGIN
jbe@164 176 SELECT * INTO "issue_row"
jbe@164 177 FROM "issue" WHERE "id" = "issue_id_p"
jbe@164 178 FOR UPDATE;
jbe@164 179 IF "issue_row"."cleaned" ISNULL THEN
jbe@164 180 UPDATE "issue" SET
jbe@164 181 "state" = 'voting',
jbe@164 182 "closed" = NULL,
jbe@164 183 "ranks_available" = FALSE
jbe@164 184 WHERE "id" = "issue_id_p";
jbe@164 185 DELETE FROM "issue_comment"
jbe@164 186 WHERE "issue_id" = "issue_id_p";
jbe@164 187 DELETE FROM "voting_comment"
jbe@164 188 WHERE "issue_id" = "issue_id_p";
jbe@164 189 DELETE FROM "delegating_voter"
jbe@164 190 WHERE "issue_id" = "issue_id_p";
jbe@164 191 DELETE FROM "direct_voter"
jbe@164 192 WHERE "issue_id" = "issue_id_p";
jbe@164 193 DELETE FROM "delegating_interest_snapshot"
jbe@164 194 WHERE "issue_id" = "issue_id_p";
jbe@164 195 DELETE FROM "direct_interest_snapshot"
jbe@164 196 WHERE "issue_id" = "issue_id_p";
jbe@164 197 DELETE FROM "delegating_population_snapshot"
jbe@164 198 WHERE "issue_id" = "issue_id_p";
jbe@164 199 DELETE FROM "direct_population_snapshot"
jbe@164 200 WHERE "issue_id" = "issue_id_p";
jbe@164 201 DELETE FROM "non_voter"
jbe@164 202 WHERE "issue_id" = "issue_id_p";
jbe@164 203 DELETE FROM "delegation"
jbe@164 204 WHERE "issue_id" = "issue_id_p";
jbe@164 205 DELETE FROM "supporter"
jbe@164 206 WHERE "issue_id" = "issue_id_p";
jbe@164 207 UPDATE "issue" SET
jbe@164 208 "state" = "issue_row"."state",
jbe@164 209 "closed" = "issue_row"."closed",
jbe@164 210 "ranks_available" = "issue_row"."ranks_available",
jbe@164 211 "cleaned" = now()
jbe@164 212 WHERE "id" = "issue_id_p";
jbe@164 213 END IF;
jbe@164 214 RETURN;
jbe@164 215 END;
jbe@164 216 $$;
jbe@164 217
jbe@164 218 COMMIT;

Impressum / About Us