liquid_feedback_core

annotate update/core-update.beta22-beta23.sql @ 61:598af132a6f9

find changesets by author, revision, files, or words in the commit message
Save battle data from calls of "clean_issue"("issue"."id")

Details:
- Calling "clean_issue" is also allowed before ranks are calculated
- View "battle" was renamed to "battle_view"
- "battle_view" only contains entries for closed issues
- New table "battle", which is filled when an issue is closed
- Version change to v1.2.2
- Update script to v1.2.2 materializes battles of existent (closed) issues
author jbe
date Tue Jul 20 00:53:30 2010 +0200 (2010-07-20)
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;

Impressum / About Us