liquid_feedback_core

annotate update/core-update.v1.4.0_rc2-v1.4.0_rc3.sql @ 267:5bf2c973ec3c

find changesets by author, revision, files, or words in the commit message
Work on OAuth 2.0 authorization support
author jbe
date Wed Aug 08 15:20:34 2012 +0200 (2012-08-08)
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_rc3', 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" WHERE "issue_id" = "issue_id_p";
jbe@164 101 END LOOP;
jbe@164 102 PERFORM "add_vote_delegations"("issue_id_p");
jbe@164 103 UPDATE "issue" SET
jbe@164 104 "state" = 'calculation',
jbe@164 105 "closed" = now(),
jbe@164 106 "voter_count" = (
jbe@164 107 SELECT coalesce(sum("weight"), 0)
jbe@164 108 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@164 109 )
jbe@164 110 WHERE "id" = "issue_id_p";
jbe@164 111 UPDATE "initiative" SET
jbe@164 112 "positive_votes" = "vote_counts"."positive_votes",
jbe@164 113 "negative_votes" = "vote_counts"."negative_votes",
jbe@164 114 "agreed" = CASE WHEN "majority_strict" THEN
jbe@164 115 "vote_counts"."positive_votes" * "majority_den" >
jbe@164 116 "majority_num" *
jbe@164 117 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@164 118 ELSE
jbe@164 119 "vote_counts"."positive_votes" * "majority_den" >=
jbe@164 120 "majority_num" *
jbe@164 121 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")
jbe@164 122 END
jbe@164 123 FROM
jbe@164 124 ( SELECT
jbe@164 125 "initiative"."id" AS "initiative_id",
jbe@164 126 coalesce(
jbe@164 127 sum(
jbe@164 128 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@164 129 ),
jbe@164 130 0
jbe@164 131 ) AS "positive_votes",
jbe@164 132 coalesce(
jbe@164 133 sum(
jbe@164 134 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END
jbe@164 135 ),
jbe@164 136 0
jbe@164 137 ) AS "negative_votes"
jbe@164 138 FROM "initiative"
jbe@164 139 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"
jbe@164 140 JOIN "policy" ON "issue"."policy_id" = "policy"."id"
jbe@164 141 LEFT JOIN "direct_voter"
jbe@164 142 ON "direct_voter"."issue_id" = "initiative"."issue_id"
jbe@164 143 LEFT JOIN "vote"
jbe@164 144 ON "vote"."initiative_id" = "initiative"."id"
jbe@164 145 AND "vote"."member_id" = "direct_voter"."member_id"
jbe@164 146 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@164 147 AND "initiative"."admitted" -- NOTE: NULL case is handled too
jbe@164 148 GROUP BY "initiative"."id"
jbe@164 149 ) AS "vote_counts",
jbe@164 150 "issue",
jbe@164 151 "policy"
jbe@164 152 WHERE "vote_counts"."initiative_id" = "initiative"."id"
jbe@164 153 AND "issue"."id" = "initiative"."issue_id"
jbe@164 154 AND "policy"."id" = "issue"."policy_id";
jbe@164 155 -- NOTE: "closed" column of issue must be set at this point
jbe@164 156 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@164 157 INSERT INTO "battle" (
jbe@164 158 "issue_id",
jbe@164 159 "winning_initiative_id", "losing_initiative_id",
jbe@164 160 "count"
jbe@164 161 ) SELECT
jbe@164 162 "issue_id",
jbe@164 163 "winning_initiative_id", "losing_initiative_id",
jbe@164 164 "count"
jbe@164 165 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@164 166 END;
jbe@164 167 $$;
jbe@164 168
jbe@164 169 COMMIT;

Impressum / About Us