liquid_feedback_core

annotate update/core-update.v3.0.2-v3.0.3.sql @ 616:ae53fc96c953

find changesets by author, revision, files, or words in the commit message
Added JSONB column "attr" to table "unit" for extended attributes of any kind
author jbe
date Tue Jul 28 18:57:20 2020 +0200 (2020-07-28)
parents 785ea3c0fd18
children
rev   line source
jbe@436 1 BEGIN;
jbe@436 2
jbe@436 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@436 4 SELECT * FROM (VALUES ('3.0.3', 3, 0, 3))
jbe@436 5 AS "subquery"("string", "major", "minor", "revision");
jbe@436 6
jbe@436 7 CREATE FUNCTION "update3_0_3_add_columns_if_missing"()
jbe@436 8 RETURNS VOID
jbe@436 9 LANGUAGE 'plpgsql' AS $$
jbe@436 10 BEGIN
jbe@436 11 BEGIN
jbe@436 12 ALTER TABLE "initiative" ADD COLUMN "first_preference_votes" INT4;
jbe@436 13 EXCEPTION
jbe@436 14 WHEN duplicate_column THEN
jbe@436 15 RAISE NOTICE 'column "first_preference_votes" of relation "initiative" already exists, skipping';
jbe@436 16 END;
jbe@436 17 BEGIN
jbe@436 18 ALTER TABLE "vote" ADD COLUMN "first_preference" BOOLEAN;
jbe@436 19 EXCEPTION
jbe@436 20 WHEN duplicate_column THEN
jbe@436 21 RAISE NOTICE 'column "first_preference" of relation "vote" already exists, skipping';
jbe@436 22 END;
jbe@436 23 RETURN;
jbe@436 24 END;
jbe@436 25 $$;
jbe@436 26
jbe@436 27 SELECT "update3_0_3_add_columns_if_missing"();
jbe@436 28
jbe@436 29 DROP FUNCTION "update3_0_3_add_columns_if_missing"();
jbe@436 30
jbe@436 31 ALTER TABLE "initiative" DROP CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results";
jbe@436 32 ALTER TABLE "initiative" ADD CONSTRAINT "non_admitted_initiatives_cant_contain_voting_results" CHECK (
jbe@436 33 ( "admitted" NOTNULL AND "admitted" = TRUE ) OR
jbe@436 34 ( "first_preference_votes" ISNULL AND
jbe@436 35 "positive_votes" ISNULL AND "negative_votes" ISNULL AND
jbe@436 36 "direct_majority" ISNULL AND "indirect_majority" ISNULL AND
jbe@436 37 "schulze_rank" ISNULL AND
jbe@436 38 "better_than_status_quo" ISNULL AND "worse_than_status_quo" ISNULL AND
jbe@436 39 "reverse_beat_path" ISNULL AND "multistage_majority" ISNULL AND
jbe@436 40 "eligible" ISNULL AND "winner" ISNULL AND "rank" ISNULL ) );
jbe@436 41
jbe@436 42 COMMENT ON COLUMN "initiative"."first_preference_votes" IS 'Number of direct and delegating voters who ranked this initiative as their first choice';
jbe@436 43 COMMENT ON COLUMN "initiative"."positive_votes" IS 'Number of direct and delegating voters who ranked this initiative better than the status quo';
jbe@436 44 COMMENT ON COLUMN "initiative"."negative_votes" IS 'Number of direct and delegating voters who ranked this initiative worse than the status quo';
jbe@436 45
jbe@436 46 -- UPDATE TABLE "vote" SET "grade" = 0 WHERE "grade" ISNULL; -- should not be necessary
jbe@436 47 ALTER TABLE "vote" ALTER COLUMN "grade" SET NOT NULL;
jbe@436 48
jbe@436 49 ALTER TABLE "vote" DROP CONSTRAINT IF EXISTS "first_preference_flag_only_set_on_positive_grades";
jbe@436 50 ALTER TABLE "vote" ADD
jbe@436 51 CONSTRAINT "first_preference_flag_only_set_on_positive_grades"
jbe@436 52 CHECK ("grade" > 0 OR "first_preference" ISNULL);
jbe@436 53
jbe@436 54 COMMENT ON COLUMN "vote"."first_preference" IS 'Value is automatically set after voting is finished. For positive grades, this value is set to true for the highest (i.e. best) grade.';
jbe@436 55
jbe@436 56 INSERT INTO "temporary_transaction_data" ("key", "value")
jbe@436 57 VALUES ('override_protection_triggers', TRUE::TEXT);
jbe@436 58
jbe@436 59 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
jbe@436 60 FROM (
jbe@436 61 SELECT
jbe@436 62 "vote"."initiative_id",
jbe@436 63 "vote"."member_id",
jbe@436 64 CASE WHEN "vote"."grade" > 0 THEN
jbe@436 65 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
jbe@436 66 ELSE NULL
jbe@436 67 END AS "first_preference"
jbe@436 68 FROM "vote"
jbe@436 69 JOIN "initiative" -- NOTE: due to missing index on issue_id
jbe@436 70 ON "vote"."issue_id" = "initiative"."issue_id"
jbe@436 71 JOIN "vote" AS "agg"
jbe@436 72 ON "initiative"."id" = "agg"."initiative_id"
jbe@436 73 AND "vote"."member_id" = "agg"."member_id"
jbe@436 74 GROUP BY "vote"."initiative_id", "vote"."member_id"
jbe@436 75 ) AS "subquery"
jbe@436 76 WHERE "vote"."initiative_id" = "subquery"."initiative_id"
jbe@436 77 AND "vote"."member_id" = "subquery"."member_id";
jbe@436 78
jbe@436 79 DELETE FROM "temporary_transaction_data"
jbe@436 80 WHERE "key" = 'override_protection_triggers';
jbe@436 81
jbe@436 82 UPDATE "initiative" SET "first_preference_votes" = NULL
jbe@436 83 WHERE "first_preference_votes" = 0;
jbe@436 84
jbe@436 85 UPDATE "initiative"
jbe@436 86 SET "first_preference_votes" = "subquery"."sum"
jbe@436 87 FROM (
jbe@436 88 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
jbe@436 89 FROM "vote" JOIN "direct_voter"
jbe@436 90 ON "vote"."issue_id" = "direct_voter"."issue_id"
jbe@436 91 AND "vote"."member_id" = "direct_voter"."member_id"
jbe@436 92 WHERE "vote"."first_preference"
jbe@436 93 GROUP BY "vote"."initiative_id"
jbe@436 94 ) AS "subquery"
jbe@436 95 WHERE "initiative"."admitted"
jbe@436 96 AND "initiative"."id" = "subquery"."initiative_id"
jbe@436 97 AND "initiative"."first_preference_votes" ISNULL;
jbe@436 98
jbe@436 99 UPDATE "initiative" SET "first_preference_votes" = 0
jbe@436 100 WHERE "positive_votes" NOTNULL
jbe@436 101 AND "first_preference_votes" ISNULL;
jbe@436 102
jbe@436 103 -- reconstruct battle data (originating from LiquidFeedback Core before v2.0.0)
jbe@436 104 -- to avoid future data loss when executing "clean_issue" to delete voting data:
jbe@436 105 INSERT INTO "battle" (
jbe@436 106 "issue_id",
jbe@436 107 "winning_initiative_id",
jbe@436 108 "losing_initiative_id",
jbe@436 109 "count"
jbe@436 110 ) SELECT
jbe@436 111 "battle_view"."issue_id",
jbe@436 112 "battle_view"."winning_initiative_id",
jbe@436 113 "battle_view"."losing_initiative_id",
jbe@436 114 "battle_view"."count"
jbe@436 115 FROM (
jbe@436 116 SELECT
jbe@436 117 "issue"."id" AS "issue_id",
jbe@436 118 "winning_initiative"."id" AS "winning_initiative_id",
jbe@436 119 "losing_initiative"."id" AS "losing_initiative_id",
jbe@436 120 sum(
jbe@436 121 CASE WHEN
jbe@436 122 coalesce("better_vote"."grade", 0) >
jbe@436 123 coalesce("worse_vote"."grade", 0)
jbe@436 124 THEN "direct_voter"."weight" ELSE 0 END
jbe@436 125 ) AS "count"
jbe@436 126 FROM "issue"
jbe@436 127 LEFT JOIN "direct_voter"
jbe@436 128 ON "issue"."id" = "direct_voter"."issue_id"
jbe@436 129 JOIN "battle_participant" AS "winning_initiative"
jbe@436 130 ON "issue"."id" = "winning_initiative"."issue_id"
jbe@436 131 JOIN "battle_participant" AS "losing_initiative"
jbe@436 132 ON "issue"."id" = "losing_initiative"."issue_id"
jbe@436 133 LEFT JOIN "vote" AS "better_vote"
jbe@436 134 ON "direct_voter"."member_id" = "better_vote"."member_id"
jbe@436 135 AND "winning_initiative"."id" = "better_vote"."initiative_id"
jbe@436 136 LEFT JOIN "vote" AS "worse_vote"
jbe@436 137 ON "direct_voter"."member_id" = "worse_vote"."member_id"
jbe@436 138 AND "losing_initiative"."id" = "worse_vote"."initiative_id"
jbe@436 139 WHERE "issue"."state" IN ('finished_with_winner', 'finished_without_winner')
jbe@436 140 AND "winning_initiative"."id" != "losing_initiative"."id"
jbe@436 141 -- NOTE: comparisons with status-quo are intentionally omitted to mark
jbe@436 142 -- issues that were counted prior LiquidFeedback Core v2.0.0
jbe@436 143 GROUP BY
jbe@436 144 "issue"."id",
jbe@436 145 "winning_initiative"."id",
jbe@436 146 "losing_initiative"."id"
jbe@436 147 ) AS "battle_view"
jbe@436 148 LEFT JOIN "battle"
jbe@436 149 ON "battle_view"."winning_initiative_id" = "battle"."winning_initiative_id"
jbe@436 150 AND "battle_view"."losing_initiative_id" = "battle"."losing_initiative_id"
jbe@436 151 WHERE "battle" ISNULL;
jbe@436 152
jbe@436 153 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@436 154 RETURNS VOID
jbe@436 155 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@436 156 DECLARE
jbe@436 157 "area_id_v" "area"."id"%TYPE;
jbe@436 158 "unit_id_v" "unit"."id"%TYPE;
jbe@436 159 "member_id_v" "member"."id"%TYPE;
jbe@436 160 BEGIN
jbe@436 161 PERFORM "require_transaction_isolation"();
jbe@436 162 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
jbe@436 163 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
jbe@436 164 -- override protection triggers:
jbe@436 165 INSERT INTO "temporary_transaction_data" ("key", "value")
jbe@436 166 VALUES ('override_protection_triggers', TRUE::TEXT);
jbe@436 167 -- delete timestamp of voting comment:
jbe@436 168 UPDATE "direct_voter" SET "comment_changed" = NULL
jbe@436 169 WHERE "issue_id" = "issue_id_p";
jbe@436 170 -- delete delegating votes (in cases of manual reset of issue state):
jbe@436 171 DELETE FROM "delegating_voter"
jbe@436 172 WHERE "issue_id" = "issue_id_p";
jbe@436 173 -- delete votes from non-privileged voters:
jbe@436 174 DELETE FROM "direct_voter"
jbe@436 175 USING (
jbe@436 176 SELECT
jbe@436 177 "direct_voter"."member_id"
jbe@436 178 FROM "direct_voter"
jbe@436 179 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
jbe@436 180 LEFT JOIN "privilege"
jbe@436 181 ON "privilege"."unit_id" = "unit_id_v"
jbe@436 182 AND "privilege"."member_id" = "direct_voter"."member_id"
jbe@436 183 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
jbe@436 184 "member"."active" = FALSE OR
jbe@436 185 "privilege"."voting_right" ISNULL OR
jbe@436 186 "privilege"."voting_right" = FALSE
jbe@436 187 )
jbe@436 188 ) AS "subquery"
jbe@436 189 WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@436 190 AND "direct_voter"."member_id" = "subquery"."member_id";
jbe@436 191 -- consider delegations:
jbe@436 192 UPDATE "direct_voter" SET "weight" = 1
jbe@436 193 WHERE "issue_id" = "issue_id_p";
jbe@436 194 PERFORM "add_vote_delegations"("issue_id_p");
jbe@436 195 -- mark first preferences:
jbe@436 196 UPDATE "vote" SET "first_preference" = "subquery"."first_preference"
jbe@436 197 FROM (
jbe@436 198 SELECT
jbe@436 199 "vote"."initiative_id",
jbe@436 200 "vote"."member_id",
jbe@436 201 CASE WHEN "vote"."grade" > 0 THEN
jbe@436 202 CASE WHEN "vote"."grade" = max("agg"."grade") THEN TRUE ELSE FALSE END
jbe@436 203 ELSE NULL
jbe@436 204 END AS "first_preference"
jbe@436 205 FROM "vote"
jbe@436 206 JOIN "initiative" -- NOTE: due to missing index on issue_id
jbe@436 207 ON "vote"."issue_id" = "initiative"."issue_id"
jbe@436 208 JOIN "vote" AS "agg"
jbe@436 209 ON "initiative"."id" = "agg"."initiative_id"
jbe@436 210 AND "vote"."member_id" = "agg"."member_id"
jbe@436 211 GROUP BY "vote"."initiative_id", "vote"."member_id", "vote"."grade"
jbe@436 212 ) AS "subquery"
jbe@436 213 WHERE "vote"."issue_id" = "issue_id_p"
jbe@436 214 AND "vote"."initiative_id" = "subquery"."initiative_id"
jbe@436 215 AND "vote"."member_id" = "subquery"."member_id";
jbe@436 216 -- finish overriding protection triggers (avoids garbage):
jbe@436 217 DELETE FROM "temporary_transaction_data"
jbe@436 218 WHERE "key" = 'override_protection_triggers';
jbe@436 219 -- materialize battle_view:
jbe@436 220 -- NOTE: "closed" column of issue must be set at this point
jbe@436 221 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@436 222 INSERT INTO "battle" (
jbe@436 223 "issue_id",
jbe@436 224 "winning_initiative_id", "losing_initiative_id",
jbe@436 225 "count"
jbe@436 226 ) SELECT
jbe@436 227 "issue_id",
jbe@436 228 "winning_initiative_id", "losing_initiative_id",
jbe@436 229 "count"
jbe@436 230 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@436 231 -- set voter count:
jbe@436 232 UPDATE "issue" SET
jbe@436 233 "voter_count" = (
jbe@436 234 SELECT coalesce(sum("weight"), 0)
jbe@436 235 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@436 236 )
jbe@436 237 WHERE "id" = "issue_id_p";
jbe@437 238 -- copy "positive_votes" and "negative_votes" from "battle" table:
jbe@437 239 -- NOTE: "first_preference_votes" is set to a default of 0 at this step
jbe@437 240 UPDATE "initiative" SET
jbe@437 241 "first_preference_votes" = 0,
jbe@437 242 "positive_votes" = "battle_win"."count",
jbe@437 243 "negative_votes" = "battle_lose"."count"
jbe@437 244 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
jbe@437 245 WHERE
jbe@437 246 "battle_win"."issue_id" = "issue_id_p" AND
jbe@437 247 "battle_win"."winning_initiative_id" = "initiative"."id" AND
jbe@437 248 "battle_win"."losing_initiative_id" ISNULL AND
jbe@437 249 "battle_lose"."issue_id" = "issue_id_p" AND
jbe@437 250 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
jbe@437 251 "battle_lose"."winning_initiative_id" ISNULL;
jbe@436 252 -- calculate "first_preference_votes":
jbe@437 253 -- NOTE: will only set values not equal to zero
jbe@437 254 UPDATE "initiative" SET "first_preference_votes" = "subquery"."sum"
jbe@436 255 FROM (
jbe@436 256 SELECT "vote"."initiative_id", sum("direct_voter"."weight")
jbe@436 257 FROM "vote" JOIN "direct_voter"
jbe@436 258 ON "vote"."issue_id" = "direct_voter"."issue_id"
jbe@436 259 AND "vote"."member_id" = "direct_voter"."member_id"
jbe@436 260 WHERE "vote"."first_preference"
jbe@436 261 GROUP BY "vote"."initiative_id"
jbe@436 262 ) AS "subquery"
jbe@436 263 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@436 264 AND "initiative"."admitted"
jbe@436 265 AND "initiative"."id" = "subquery"."initiative_id";
jbe@436 266 END;
jbe@436 267 $$;
jbe@436 268
jbe@436 269 COMMIT;

Impressum / About Us