liquid_feedback_core: 54ac8c473263 update/core-update.v1.2.1-v1.2.2.sql

liquid_feedback_core

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

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

line source

1 BEGIN;

3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

4 SELECT * FROM (VALUES ('1.2.2', 1, 2, 2))

5 AS "subquery"("string", "major", "minor", "revision");

7 ALTER TABLE "issue" DROP CONSTRAINT "clean_restriction";

9 ALTER TABLE "issue" ADD CONSTRAINT "only_closed_issues_may_be_cleaned"

10 CHECK ("cleaned" ISNULL OR "closed" NOTNULL);

12 ALTER VIEW "battle" RENAME TO "battle_view";

14 CREATE TABLE "battle" (

15 PRIMARY KEY ("issue_id", "winning_initiative_id", "losing_initiative_id"),

16 "issue_id" INT4,

17 "winning_initiative_id" INT4,

18 FOREIGN KEY ("issue_id", "winning_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,

19 "losing_initiative_id" INT4,

20 FOREIGN KEY ("issue_id", "losing_initiative_id") REFERENCES "initiative" ("issue_id", "id") ON DELETE CASCADE ON UPDATE CASCADE,

21 "count" INT4 NOT NULL);

23 COMMENT ON TABLE "battle" IS 'Number of members preferring one initiative to another; Filled by "battle_view" when closing an issue';

25 CREATE OR REPLACE VIEW "battle_view" AS

26 SELECT

27 "issue"."id" AS "issue_id",

28 "winning_initiative"."id" AS "winning_initiative_id",

29 "losing_initiative"."id" AS "losing_initiative_id",

30 sum(

31 CASE WHEN

32 coalesce("better_vote"."grade", 0) >

33 coalesce("worse_vote"."grade", 0)

34 THEN "direct_voter"."weight" ELSE 0 END

35 ) AS "count"

36 FROM "issue"

37 LEFT JOIN "direct_voter"

38 ON "issue"."id" = "direct_voter"."issue_id"

39 JOIN "initiative" AS "winning_initiative"

40 ON "issue"."id" = "winning_initiative"."issue_id"

41 AND "winning_initiative"."agreed"

42 JOIN "initiative" AS "losing_initiative"

43 ON "issue"."id" = "losing_initiative"."issue_id"

44 AND "losing_initiative"."agreed"

45 LEFT JOIN "vote" AS "better_vote"

46 ON "direct_voter"."member_id" = "better_vote"."member_id"

47 AND "winning_initiative"."id" = "better_vote"."initiative_id"

48 LEFT JOIN "vote" AS "worse_vote"

49 ON "direct_voter"."member_id" = "worse_vote"."member_id"

50 AND "losing_initiative"."id" = "worse_vote"."initiative_id"

51 WHERE "issue"."closed" NOTNULL

52 AND "issue"."cleaned" ISNULL

53 AND "winning_initiative"."id" != "losing_initiative"."id"

54 GROUP BY

55 "issue"."id",

56 "winning_initiative"."id",

57 "losing_initiative"."id";

59 COMMENT ON VIEW "battle_view" IS 'Number of members preferring one initiative to another; Used to fill "battle" table';

61 INSERT INTO "battle" (

62 "issue_id",

63 "winning_initiative_id",

64 "losing_initiative_id",

65 "count"

66 ) SELECT

67 "issue_id",

68 "winning_initiative_id", "losing_initiative_id",

69 "count"

70 FROM "battle_view";

72 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)

73 RETURNS VOID

74 LANGUAGE 'plpgsql' VOLATILE AS $$

75 DECLARE

76 "issue_row" "issue"%ROWTYPE;

77 "member_id_v" "member"."id"%TYPE;

78 BEGIN

79 PERFORM "global_lock"();

80 SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p";

81 DELETE FROM "delegating_voter"

82 WHERE "issue_id" = "issue_id_p";

83 DELETE FROM "direct_voter"

84 WHERE "issue_id" = "issue_id_p"

85 AND "autoreject" = TRUE;

86 DELETE FROM "direct_voter" USING "member"

87 WHERE "direct_voter"."member_id" = "member"."id"

88 AND "direct_voter"."issue_id" = "issue_id_p"

89 AND "member"."active" = FALSE;

90 UPDATE "direct_voter" SET "weight" = 1

91 WHERE "issue_id" = "issue_id_p";

92 PERFORM "add_vote_delegations"("issue_id_p");

93 FOR "member_id_v" IN

94 SELECT "interest"."member_id"

95 FROM "interest"

96 LEFT JOIN "direct_voter"

97 ON "interest"."member_id" = "direct_voter"."member_id"

98 AND "interest"."issue_id" = "direct_voter"."issue_id"

99 LEFT JOIN "delegating_voter"

100 ON "interest"."member_id" = "delegating_voter"."member_id"

101 AND "interest"."issue_id" = "delegating_voter"."issue_id"

102 WHERE "interest"."issue_id" = "issue_id_p"

103 AND "interest"."autoreject" = TRUE

104 AND "direct_voter"."member_id" ISNULL

105 AND "delegating_voter"."member_id" ISNULL

106 UNION SELECT "membership"."member_id"

107 FROM "membership"

108 LEFT JOIN "interest"

109 ON "membership"."member_id" = "interest"."member_id"

110 AND "interest"."issue_id" = "issue_id_p"

111 LEFT JOIN "direct_voter"

112 ON "membership"."member_id" = "direct_voter"."member_id"

113 AND "direct_voter"."issue_id" = "issue_id_p"

114 LEFT JOIN "delegating_voter"

115 ON "membership"."member_id" = "delegating_voter"."member_id"

116 AND "delegating_voter"."issue_id" = "issue_id_p"

117 WHERE "membership"."area_id" = "issue_row"."area_id"

118 AND "membership"."autoreject" = TRUE

119 AND "interest"."autoreject" ISNULL

120 AND "direct_voter"."member_id" ISNULL

121 AND "delegating_voter"."member_id" ISNULL

122 LOOP

123 INSERT INTO "direct_voter"

124 ("member_id", "issue_id", "weight", "autoreject") VALUES

125 ("member_id_v", "issue_id_p", 1, TRUE);

126 INSERT INTO "vote" (

127 "member_id",

128 "issue_id",

129 "initiative_id",

130 "grade"

131 ) SELECT

132 "member_id_v" AS "member_id",

133 "issue_id_p" AS "issue_id",

134 "id" AS "initiative_id",

135 -1 AS "grade"

136 FROM "initiative" WHERE "issue_id" = "issue_id_p";

137 END LOOP;

138 PERFORM "add_vote_delegations"("issue_id_p");

139 UPDATE "issue" SET

140 "closed" = now(),

141 "voter_count" = (

142 SELECT coalesce(sum("weight"), 0)

143 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"

144 )

145 WHERE "id" = "issue_id_p";

146 UPDATE "initiative" SET

147 "positive_votes" = "vote_counts"."positive_votes",

148 "negative_votes" = "vote_counts"."negative_votes",

149 "agreed" = CASE WHEN "majority_strict" THEN

150 "vote_counts"."positive_votes" * "majority_den" >

151 "majority_num" *

152 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")

153 ELSE

154 "vote_counts"."positive_votes" * "majority_den" >=

155 "majority_num" *

156 ("vote_counts"."positive_votes"+"vote_counts"."negative_votes")

157 END

158 FROM

159 ( SELECT

160 "initiative"."id" AS "initiative_id",

161 coalesce(

162 sum(

163 CASE WHEN "grade" > 0 THEN "direct_voter"."weight" ELSE 0 END

164 ),

165 0

166 ) AS "positive_votes",

167 coalesce(

168 sum(

169 CASE WHEN "grade" < 0 THEN "direct_voter"."weight" ELSE 0 END

170 ),

171 0

172 ) AS "negative_votes"

173 FROM "initiative"

174 JOIN "issue" ON "initiative"."issue_id" = "issue"."id"

175 JOIN "policy" ON "issue"."policy_id" = "policy"."id"

176 LEFT JOIN "direct_voter"

177 ON "direct_voter"."issue_id" = "initiative"."issue_id"

178 LEFT JOIN "vote"

179 ON "vote"."initiative_id" = "initiative"."id"

180 AND "vote"."member_id" = "direct_voter"."member_id"

181 WHERE "initiative"."issue_id" = "issue_id_p"

182 AND "initiative"."admitted" -- NOTE: NULL case is handled too

183 GROUP BY "initiative"."id"

184 ) AS "vote_counts",

185 "issue",

186 "policy"

187 WHERE "vote_counts"."initiative_id" = "initiative"."id"

188 AND "issue"."id" = "initiative"."issue_id"

189 AND "policy"."id" = "issue"."policy_id";

190 -- NOTE: "closed" column of issue must be set at this point

191 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";

192 INSERT INTO "battle" (

193 "issue_id",

194 "winning_initiative_id", "losing_initiative_id",

195 "count"

196 ) SELECT

197 "issue_id",

198 "winning_initiative_id", "losing_initiative_id",

199 "count"

200 FROM "battle_view" WHERE "issue_id" = "issue_id_p";

201 END;

202 $$;

203

204 COMMIT;