liquid_feedback_core
annotate update/core-update.beta22-beta23.sql @ 63:1af482e378a1
Added "rendered_draft" table for caching
(Version number updated to v1.2.3, update script included)
(Version number updated to v1.2.3, update script included)
author | jbe |
---|---|
date | Sat Jul 24 16:40:51 2010 +0200 (2010-07-24) |
parents | c3b72b644cc8 |
children |
rev | line source |
---|---|
jbe@36 | 1 BEGIN; |
jbe@36 | 2 |
jbe@36 | 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS |
jbe@36 | 4 SELECT * FROM (VALUES ('beta23', NULL, NULL, NULL)) |
jbe@36 | 5 AS "subquery"("string", "major", "minor", "revision"); |
jbe@36 | 6 |
jbe@36 | 7 CREATE OR REPLACE FUNCTION "create_snapshot" |
jbe@36 | 8 ( "issue_id_p" "issue"."id"%TYPE ) |
jbe@36 | 9 RETURNS VOID |
jbe@36 | 10 LANGUAGE 'plpgsql' VOLATILE AS $$ |
jbe@36 | 11 DECLARE |
jbe@36 | 12 "initiative_id_v" "initiative"."id"%TYPE; |
jbe@36 | 13 "suggestion_id_v" "suggestion"."id"%TYPE; |
jbe@36 | 14 BEGIN |
jbe@36 | 15 PERFORM "global_lock"(); |
jbe@36 | 16 PERFORM "create_population_snapshot"("issue_id_p"); |
jbe@36 | 17 PERFORM "create_interest_snapshot"("issue_id_p"); |
jbe@36 | 18 UPDATE "issue" SET |
jbe@36 | 19 "snapshot" = now(), |
jbe@36 | 20 "latest_snapshot_event" = 'periodic', |
jbe@36 | 21 "population" = ( |
jbe@36 | 22 SELECT coalesce(sum("weight"), 0) |
jbe@36 | 23 FROM "direct_population_snapshot" |
jbe@36 | 24 WHERE "issue_id" = "issue_id_p" |
jbe@36 | 25 AND "event" = 'periodic' |
jbe@36 | 26 ), |
jbe@36 | 27 "vote_now" = ( |
jbe@36 | 28 SELECT coalesce(sum("weight"), 0) |
jbe@36 | 29 FROM "direct_interest_snapshot" |
jbe@36 | 30 WHERE "issue_id" = "issue_id_p" |
jbe@36 | 31 AND "event" = 'periodic' |
jbe@36 | 32 AND "voting_requested" = TRUE |
jbe@36 | 33 ), |
jbe@36 | 34 "vote_later" = ( |
jbe@36 | 35 SELECT coalesce(sum("weight"), 0) |
jbe@36 | 36 FROM "direct_interest_snapshot" |
jbe@36 | 37 WHERE "issue_id" = "issue_id_p" |
jbe@36 | 38 AND "event" = 'periodic' |
jbe@36 | 39 AND "voting_requested" = FALSE |
jbe@36 | 40 ) |
jbe@36 | 41 WHERE "id" = "issue_id_p"; |
jbe@36 | 42 FOR "initiative_id_v" IN |
jbe@36 | 43 SELECT "id" FROM "initiative" WHERE "issue_id" = "issue_id_p" |
jbe@36 | 44 LOOP |
jbe@36 | 45 UPDATE "initiative" SET |
jbe@36 | 46 "supporter_count" = ( |
jbe@36 | 47 SELECT coalesce(sum("di"."weight"), 0) |
jbe@36 | 48 FROM "direct_interest_snapshot" AS "di" |
jbe@36 | 49 JOIN "direct_supporter_snapshot" AS "ds" |
jbe@36 | 50 ON "di"."member_id" = "ds"."member_id" |
jbe@36 | 51 WHERE "di"."issue_id" = "issue_id_p" |
jbe@36 | 52 AND "di"."event" = 'periodic' |
jbe@36 | 53 AND "ds"."initiative_id" = "initiative_id_v" |
jbe@36 | 54 AND "ds"."event" = 'periodic' |
jbe@36 | 55 ), |
jbe@36 | 56 "informed_supporter_count" = ( |
jbe@36 | 57 SELECT coalesce(sum("di"."weight"), 0) |
jbe@36 | 58 FROM "direct_interest_snapshot" AS "di" |
jbe@36 | 59 JOIN "direct_supporter_snapshot" AS "ds" |
jbe@36 | 60 ON "di"."member_id" = "ds"."member_id" |
jbe@36 | 61 WHERE "di"."issue_id" = "issue_id_p" |
jbe@36 | 62 AND "di"."event" = 'periodic' |
jbe@36 | 63 AND "ds"."initiative_id" = "initiative_id_v" |
jbe@36 | 64 AND "ds"."event" = 'periodic' |
jbe@36 | 65 AND "ds"."informed" |
jbe@36 | 66 ), |
jbe@36 | 67 "satisfied_supporter_count" = ( |
jbe@36 | 68 SELECT coalesce(sum("di"."weight"), 0) |
jbe@36 | 69 FROM "direct_interest_snapshot" AS "di" |
jbe@36 | 70 JOIN "direct_supporter_snapshot" AS "ds" |
jbe@36 | 71 ON "di"."member_id" = "ds"."member_id" |
jbe@36 | 72 WHERE "di"."issue_id" = "issue_id_p" |
jbe@36 | 73 AND "di"."event" = 'periodic' |
jbe@36 | 74 AND "ds"."initiative_id" = "initiative_id_v" |
jbe@36 | 75 AND "ds"."event" = 'periodic' |
jbe@36 | 76 AND "ds"."satisfied" |
jbe@36 | 77 ), |
jbe@36 | 78 "satisfied_informed_supporter_count" = ( |
jbe@36 | 79 SELECT coalesce(sum("di"."weight"), 0) |
jbe@36 | 80 FROM "direct_interest_snapshot" AS "di" |
jbe@36 | 81 JOIN "direct_supporter_snapshot" AS "ds" |
jbe@36 | 82 ON "di"."member_id" = "ds"."member_id" |
jbe@36 | 83 WHERE "di"."issue_id" = "issue_id_p" |
jbe@36 | 84 AND "di"."event" = 'periodic' |
jbe@36 | 85 AND "ds"."initiative_id" = "initiative_id_v" |
jbe@36 | 86 AND "ds"."event" = 'periodic' |
jbe@36 | 87 AND "ds"."informed" |
jbe@36 | 88 AND "ds"."satisfied" |
jbe@36 | 89 ) |
jbe@36 | 90 WHERE "id" = "initiative_id_v"; |
jbe@36 | 91 FOR "suggestion_id_v" IN |
jbe@36 | 92 SELECT "id" FROM "suggestion" |
jbe@36 | 93 WHERE "initiative_id" = "initiative_id_v" |
jbe@36 | 94 LOOP |
jbe@36 | 95 UPDATE "suggestion" SET |
jbe@36 | 96 "minus2_unfulfilled_count" = ( |
jbe@36 | 97 SELECT coalesce(sum("snapshot"."weight"), 0) |
jbe@36 | 98 FROM "issue" CROSS JOIN "opinion" |
jbe@36 | 99 JOIN "direct_interest_snapshot" AS "snapshot" |
jbe@36 | 100 ON "snapshot"."issue_id" = "issue"."id" |
jbe@36 | 101 AND "snapshot"."event" = "issue"."latest_snapshot_event" |
jbe@36 | 102 AND "snapshot"."member_id" = "opinion"."member_id" |
jbe@36 | 103 WHERE "issue"."id" = "issue_id_p" |
jbe@36 | 104 AND "opinion"."suggestion_id" = "suggestion_id_v" |
jbe@36 | 105 AND "opinion"."degree" = -2 |
jbe@36 | 106 AND "opinion"."fulfilled" = FALSE |
jbe@36 | 107 ), |
jbe@36 | 108 "minus2_fulfilled_count" = ( |
jbe@36 | 109 SELECT coalesce(sum("snapshot"."weight"), 0) |
jbe@36 | 110 FROM "issue" CROSS JOIN "opinion" |
jbe@36 | 111 JOIN "direct_interest_snapshot" AS "snapshot" |
jbe@36 | 112 ON "snapshot"."issue_id" = "issue"."id" |
jbe@36 | 113 AND "snapshot"."event" = "issue"."latest_snapshot_event" |
jbe@36 | 114 AND "snapshot"."member_id" = "opinion"."member_id" |
jbe@36 | 115 WHERE "issue"."id" = "issue_id_p" |
jbe@36 | 116 AND "opinion"."suggestion_id" = "suggestion_id_v" |
jbe@36 | 117 AND "opinion"."degree" = -2 |
jbe@36 | 118 AND "opinion"."fulfilled" = TRUE |
jbe@36 | 119 ), |
jbe@36 | 120 "minus1_unfulfilled_count" = ( |
jbe@36 | 121 SELECT coalesce(sum("snapshot"."weight"), 0) |
jbe@36 | 122 FROM "issue" CROSS JOIN "opinion" |
jbe@36 | 123 JOIN "direct_interest_snapshot" AS "snapshot" |
jbe@36 | 124 ON "snapshot"."issue_id" = "issue"."id" |
jbe@36 | 125 AND "snapshot"."event" = "issue"."latest_snapshot_event" |
jbe@36 | 126 AND "snapshot"."member_id" = "opinion"."member_id" |
jbe@36 | 127 WHERE "issue"."id" = "issue_id_p" |
jbe@36 | 128 AND "opinion"."suggestion_id" = "suggestion_id_v" |
jbe@36 | 129 AND "opinion"."degree" = -1 |
jbe@36 | 130 AND "opinion"."fulfilled" = FALSE |
jbe@36 | 131 ), |
jbe@36 | 132 "minus1_fulfilled_count" = ( |
jbe@36 | 133 SELECT coalesce(sum("snapshot"."weight"), 0) |
jbe@36 | 134 FROM "issue" CROSS JOIN "opinion" |
jbe@36 | 135 JOIN "direct_interest_snapshot" AS "snapshot" |
jbe@36 | 136 ON "snapshot"."issue_id" = "issue"."id" |
jbe@36 | 137 AND "snapshot"."event" = "issue"."latest_snapshot_event" |
jbe@36 | 138 AND "snapshot"."member_id" = "opinion"."member_id" |
jbe@36 | 139 WHERE "issue"."id" = "issue_id_p" |
jbe@36 | 140 AND "opinion"."suggestion_id" = "suggestion_id_v" |
jbe@36 | 141 AND "opinion"."degree" = -1 |
jbe@36 | 142 AND "opinion"."fulfilled" = TRUE |
jbe@36 | 143 ), |
jbe@36 | 144 "plus1_unfulfilled_count" = ( |
jbe@36 | 145 SELECT coalesce(sum("snapshot"."weight"), 0) |
jbe@36 | 146 FROM "issue" CROSS JOIN "opinion" |
jbe@36 | 147 JOIN "direct_interest_snapshot" AS "snapshot" |
jbe@36 | 148 ON "snapshot"."issue_id" = "issue"."id" |
jbe@36 | 149 AND "snapshot"."event" = "issue"."latest_snapshot_event" |
jbe@36 | 150 AND "snapshot"."member_id" = "opinion"."member_id" |
jbe@36 | 151 WHERE "issue"."id" = "issue_id_p" |
jbe@36 | 152 AND "opinion"."suggestion_id" = "suggestion_id_v" |
jbe@36 | 153 AND "opinion"."degree" = 1 |
jbe@36 | 154 AND "opinion"."fulfilled" = FALSE |
jbe@36 | 155 ), |
jbe@36 | 156 "plus1_fulfilled_count" = ( |
jbe@36 | 157 SELECT coalesce(sum("snapshot"."weight"), 0) |
jbe@36 | 158 FROM "issue" CROSS JOIN "opinion" |
jbe@36 | 159 JOIN "direct_interest_snapshot" AS "snapshot" |
jbe@36 | 160 ON "snapshot"."issue_id" = "issue"."id" |
jbe@36 | 161 AND "snapshot"."event" = "issue"."latest_snapshot_event" |
jbe@36 | 162 AND "snapshot"."member_id" = "opinion"."member_id" |
jbe@36 | 163 WHERE "issue"."id" = "issue_id_p" |
jbe@36 | 164 AND "opinion"."suggestion_id" = "suggestion_id_v" |
jbe@36 | 165 AND "opinion"."degree" = 1 |
jbe@36 | 166 AND "opinion"."fulfilled" = TRUE |
jbe@36 | 167 ), |
jbe@36 | 168 "plus2_unfulfilled_count" = ( |
jbe@36 | 169 SELECT coalesce(sum("snapshot"."weight"), 0) |
jbe@36 | 170 FROM "issue" CROSS JOIN "opinion" |
jbe@36 | 171 JOIN "direct_interest_snapshot" AS "snapshot" |
jbe@36 | 172 ON "snapshot"."issue_id" = "issue"."id" |
jbe@36 | 173 AND "snapshot"."event" = "issue"."latest_snapshot_event" |
jbe@36 | 174 AND "snapshot"."member_id" = "opinion"."member_id" |
jbe@36 | 175 WHERE "issue"."id" = "issue_id_p" |
jbe@36 | 176 AND "opinion"."suggestion_id" = "suggestion_id_v" |
jbe@36 | 177 AND "opinion"."degree" = 2 |
jbe@36 | 178 AND "opinion"."fulfilled" = FALSE |
jbe@36 | 179 ), |
jbe@36 | 180 "plus2_fulfilled_count" = ( |
jbe@36 | 181 SELECT coalesce(sum("snapshot"."weight"), 0) |
jbe@36 | 182 FROM "issue" CROSS JOIN "opinion" |
jbe@36 | 183 JOIN "direct_interest_snapshot" AS "snapshot" |
jbe@36 | 184 ON "snapshot"."issue_id" = "issue"."id" |
jbe@36 | 185 AND "snapshot"."event" = "issue"."latest_snapshot_event" |
jbe@36 | 186 AND "snapshot"."member_id" = "opinion"."member_id" |
jbe@36 | 187 WHERE "issue"."id" = "issue_id_p" |
jbe@36 | 188 AND "opinion"."suggestion_id" = "suggestion_id_v" |
jbe@36 | 189 AND "opinion"."degree" = 2 |
jbe@36 | 190 AND "opinion"."fulfilled" = TRUE |
jbe@36 | 191 ) |
jbe@36 | 192 WHERE "suggestion"."id" = "suggestion_id_v"; |
jbe@36 | 193 END LOOP; |
jbe@36 | 194 END LOOP; |
jbe@36 | 195 RETURN; |
jbe@36 | 196 END; |
jbe@36 | 197 $$; |
jbe@36 | 198 |
jbe@36 | 199 COMMIT; |