liquid_feedback_core: bdccc56fb705 update/core-update.beta22-beta23.sql

liquid_feedback_core

view update/core-update.beta22-beta23.sql @ 65:bdccc56fb705

lf_update continues on error (but still reports errors)
and version number changed to v1.2.4 (without any other schema changes)

author	jbe
date	Sun Aug 15 17:10:47 2010 +0200 (2010-08-15)
parents	c3b72b644cc8
children

line source

1 BEGIN;

3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS

4 SELECT * FROM (VALUES ('beta23', NULL, NULL, NULL))

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

7 CREATE OR REPLACE FUNCTION "create_snapshot"

8 ( "issue_id_p" "issue"."id"%TYPE )

9 RETURNS VOID

10 LANGUAGE 'plpgsql' VOLATILE AS $$

11 DECLARE

12 "initiative_id_v" "initiative"."id"%TYPE;

13 "suggestion_id_v" "suggestion"."id"%TYPE;

14 BEGIN

15 PERFORM "global_lock"();

16 PERFORM "create_population_snapshot"("issue_id_p");

17 PERFORM "create_interest_snapshot"("issue_id_p");

18 UPDATE "issue" SET

19 "snapshot" = now(),

20 "latest_snapshot_event" = 'periodic',

21 "population" = (

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

23 FROM "direct_population_snapshot"

24 WHERE "issue_id" = "issue_id_p"

25 AND "event" = 'periodic'

26 ),

27 "vote_now" = (

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

29 FROM "direct_interest_snapshot"

30 WHERE "issue_id" = "issue_id_p"

31 AND "event" = 'periodic'

32 AND "voting_requested" = TRUE

33 ),

34 "vote_later" = (

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

36 FROM "direct_interest_snapshot"

37 WHERE "issue_id" = "issue_id_p"

38 AND "event" = 'periodic'

39 AND "voting_requested" = FALSE

40 )

41 WHERE "id" = "issue_id_p";

42 FOR "initiative_id_v" IN

43 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p"

44 LOOP

45 UPDATE "initiative" SET

46 "supporter_count" = (

47 SELECT coalesce(sum("di"."weight"), 0)

48 FROM "direct_interest_snapshot" AS "di"

49 JOIN "direct_supporter_snapshot" AS "ds"

50 ON "di"."member_id" = "ds"."member_id"

51 WHERE "di"."issue_id" = "issue_id_p"

52 AND "di"."event" = 'periodic'

53 AND "ds"."initiative_id" = "initiative_id_v"

54 AND "ds"."event" = 'periodic'

55 ),

56 "informed_supporter_count" = (

57 SELECT coalesce(sum("di"."weight"), 0)

58 FROM "direct_interest_snapshot" AS "di"

59 JOIN "direct_supporter_snapshot" AS "ds"

60 ON "di"."member_id" = "ds"."member_id"

61 WHERE "di"."issue_id" = "issue_id_p"

62 AND "di"."event" = 'periodic'

63 AND "ds"."initiative_id" = "initiative_id_v"

64 AND "ds"."event" = 'periodic'

65 AND "ds"."informed"

66 ),

67 "satisfied_supporter_count" = (

68 SELECT coalesce(sum("di"."weight"), 0)

69 FROM "direct_interest_snapshot" AS "di"

70 JOIN "direct_supporter_snapshot" AS "ds"

71 ON "di"."member_id" = "ds"."member_id"

72 WHERE "di"."issue_id" = "issue_id_p"

73 AND "di"."event" = 'periodic'

74 AND "ds"."initiative_id" = "initiative_id_v"

75 AND "ds"."event" = 'periodic'

76 AND "ds"."satisfied"

77 ),

78 "satisfied_informed_supporter_count" = (

79 SELECT coalesce(sum("di"."weight"), 0)

80 FROM "direct_interest_snapshot" AS "di"

81 JOIN "direct_supporter_snapshot" AS "ds"

82 ON "di"."member_id" = "ds"."member_id"

83 WHERE "di"."issue_id" = "issue_id_p"

84 AND "di"."event" = 'periodic'

85 AND "ds"."initiative_id" = "initiative_id_v"

86 AND "ds"."event" = 'periodic'

87 AND "ds"."informed"

88 AND "ds"."satisfied"

89 )

90 WHERE "id" = "initiative_id_v";

91 FOR "suggestion_id_v" IN

92 SELECT "id" FROM "suggestion"

93 WHERE "initiative_id" = "initiative_id_v"

94 LOOP

95 UPDATE "suggestion" SET

96 "minus2_unfulfilled_count" = (

97 SELECT coalesce(sum("snapshot"."weight"), 0)

98 FROM "issue" CROSS JOIN "opinion"

99 JOIN "direct_interest_snapshot" AS "snapshot"

100 ON "snapshot"."issue_id" = "issue"."id"

101 AND "snapshot"."event" = "issue"."latest_snapshot_event"

102 AND "snapshot"."member_id" = "opinion"."member_id"

103 WHERE "issue"."id" = "issue_id_p"

104 AND "opinion"."suggestion_id" = "suggestion_id_v"

105 AND "opinion"."degree" = -2

106 AND "opinion"."fulfilled" = FALSE

107 ),

108 "minus2_fulfilled_count" = (

109 SELECT coalesce(sum("snapshot"."weight"), 0)

110 FROM "issue" CROSS JOIN "opinion"

111 JOIN "direct_interest_snapshot" AS "snapshot"

112 ON "snapshot"."issue_id" = "issue"."id"

113 AND "snapshot"."event" = "issue"."latest_snapshot_event"

114 AND "snapshot"."member_id" = "opinion"."member_id"

115 WHERE "issue"."id" = "issue_id_p"

116 AND "opinion"."suggestion_id" = "suggestion_id_v"

117 AND "opinion"."degree" = -2

118 AND "opinion"."fulfilled" = TRUE

119 ),

120 "minus1_unfulfilled_count" = (

121 SELECT coalesce(sum("snapshot"."weight"), 0)

122 FROM "issue" CROSS JOIN "opinion"

123 JOIN "direct_interest_snapshot" AS "snapshot"

124 ON "snapshot"."issue_id" = "issue"."id"

125 AND "snapshot"."event" = "issue"."latest_snapshot_event"

126 AND "snapshot"."member_id" = "opinion"."member_id"

127 WHERE "issue"."id" = "issue_id_p"

128 AND "opinion"."suggestion_id" = "suggestion_id_v"

129 AND "opinion"."degree" = -1

130 AND "opinion"."fulfilled" = FALSE

131 ),

132 "minus1_fulfilled_count" = (

133 SELECT coalesce(sum("snapshot"."weight"), 0)

134 FROM "issue" CROSS JOIN "opinion"

135 JOIN "direct_interest_snapshot" AS "snapshot"

136 ON "snapshot"."issue_id" = "issue"."id"

137 AND "snapshot"."event" = "issue"."latest_snapshot_event"

138 AND "snapshot"."member_id" = "opinion"."member_id"

139 WHERE "issue"."id" = "issue_id_p"

140 AND "opinion"."suggestion_id" = "suggestion_id_v"

141 AND "opinion"."degree" = -1

142 AND "opinion"."fulfilled" = TRUE

143 ),

144 "plus1_unfulfilled_count" = (

145 SELECT coalesce(sum("snapshot"."weight"), 0)

146 FROM "issue" CROSS JOIN "opinion"

147 JOIN "direct_interest_snapshot" AS "snapshot"

148 ON "snapshot"."issue_id" = "issue"."id"

149 AND "snapshot"."event" = "issue"."latest_snapshot_event"

150 AND "snapshot"."member_id" = "opinion"."member_id"

151 WHERE "issue"."id" = "issue_id_p"

152 AND "opinion"."suggestion_id" = "suggestion_id_v"

153 AND "opinion"."degree" = 1

154 AND "opinion"."fulfilled" = FALSE

155 ),

156 "plus1_fulfilled_count" = (

157 SELECT coalesce(sum("snapshot"."weight"), 0)

158 FROM "issue" CROSS JOIN "opinion"

159 JOIN "direct_interest_snapshot" AS "snapshot"

160 ON "snapshot"."issue_id" = "issue"."id"

161 AND "snapshot"."event" = "issue"."latest_snapshot_event"

162 AND "snapshot"."member_id" = "opinion"."member_id"

163 WHERE "issue"."id" = "issue_id_p"

164 AND "opinion"."suggestion_id" = "suggestion_id_v"

165 AND "opinion"."degree" = 1

166 AND "opinion"."fulfilled" = TRUE

167 ),

168 "plus2_unfulfilled_count" = (

169 SELECT coalesce(sum("snapshot"."weight"), 0)

170 FROM "issue" CROSS JOIN "opinion"

171 JOIN "direct_interest_snapshot" AS "snapshot"

172 ON "snapshot"."issue_id" = "issue"."id"

173 AND "snapshot"."event" = "issue"."latest_snapshot_event"

174 AND "snapshot"."member_id" = "opinion"."member_id"

175 WHERE "issue"."id" = "issue_id_p"

176 AND "opinion"."suggestion_id" = "suggestion_id_v"

177 AND "opinion"."degree" = 2

178 AND "opinion"."fulfilled" = FALSE

179 ),

180 "plus2_fulfilled_count" = (

181 SELECT coalesce(sum("snapshot"."weight"), 0)

182 FROM "issue" CROSS JOIN "opinion"

183 JOIN "direct_interest_snapshot" AS "snapshot"

184 ON "snapshot"."issue_id" = "issue"."id"

185 AND "snapshot"."event" = "issue"."latest_snapshot_event"

186 AND "snapshot"."member_id" = "opinion"."member_id"

187 WHERE "issue"."id" = "issue_id_p"

188 AND "opinion"."suggestion_id" = "suggestion_id_v"

189 AND "opinion"."degree" = 2

190 AND "opinion"."fulfilled" = TRUE

191 )

192 WHERE "suggestion"."id" = "suggestion_id_v";

193 END LOOP;

194 END LOOP;

195 RETURN;

196 END;

197 $$;

198

199 COMMIT;