liquid_feedback_core

annotate update/core-update.v2.2.1-v2.2.2.sql @ 423:73c2ab2d068f

find changesets by author, revision, files, or words in the commit message
Work on configuration of complexity of counting of the votes (extent of tie-breaking):
- added configuration field in "policy" table
- calculation of secondary criterion for the defeat strength (tie-breaking of the links) based on initiative id's
author jbe
date Thu Apr 10 00:20:03 2014 +0200 (2014-04-10)
parents 1c991490f075
children
rev   line source
jbe@383 1 BEGIN;
jbe@383 2
jbe@383 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@383 4 SELECT * FROM (VALUES ('2.2.2', 2, 2, 2))
jbe@383 5 AS "subquery"("string", "major", "minor", "revision");
jbe@383 6
jbe@383 7 CREATE TABLE "internal_session_store" (
jbe@383 8 PRIMARY KEY ("backend_pid", "key"),
jbe@383 9 "backend_pid" INT4,
jbe@383 10 "key" TEXT,
jbe@383 11 "value" TEXT NOT NULL );
jbe@383 12
jbe@383 13 COMMENT ON TABLE "internal_session_store" IS 'Table to store session variables; shall be emptied before a transaction is committed';
jbe@383 14
jbe@383 15 COMMENT ON COLUMN "internal_session_store"."backend_pid" IS 'Value returned by function pg_backend_pid()';
jbe@383 16
jbe@383 17 CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"()
jbe@383 18 RETURNS TRIGGER
jbe@383 19 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@383 20 DECLARE
jbe@383 21 "issue_id_v" "issue"."id"%TYPE;
jbe@383 22 "issue_row" "issue"%ROWTYPE;
jbe@383 23 BEGIN
jbe@383 24 IF EXISTS (
jbe@383 25 SELECT NULL FROM "internal_session_store"
jbe@383 26 WHERE "backend_pid" = pg_backend_pid()
jbe@383 27 AND "key" = 'override_protection_triggers'
jbe@383 28 AND "value" = TRUE::TEXT
jbe@383 29 ) THEN
jbe@383 30 RETURN NULL;
jbe@383 31 END IF;
jbe@383 32 IF TG_OP = 'DELETE' THEN
jbe@383 33 "issue_id_v" := OLD."issue_id";
jbe@383 34 ELSE
jbe@383 35 "issue_id_v" := NEW."issue_id";
jbe@383 36 END IF;
jbe@383 37 SELECT INTO "issue_row" * FROM "issue"
jbe@383 38 WHERE "id" = "issue_id_v" FOR SHARE;
jbe@383 39 IF (
jbe@383 40 "issue_row"."closed" NOTNULL OR (
jbe@383 41 "issue_row"."state" = 'voting' AND
jbe@383 42 "issue_row"."phase_finished" NOTNULL
jbe@383 43 )
jbe@383 44 ) THEN
jbe@383 45 IF
jbe@383 46 TG_RELID = 'direct_voter'::regclass AND
jbe@383 47 TG_OP = 'UPDATE'
jbe@383 48 THEN
jbe@383 49 IF
jbe@383 50 OLD."issue_id" = NEW."issue_id" AND
jbe@383 51 OLD."member_id" = NEW."member_id" AND
jbe@383 52 OLD."weight" = NEW."weight"
jbe@383 53 THEN
jbe@383 54 RETURN NULL; -- allows changing of voter comment
jbe@383 55 END IF;
jbe@383 56 END IF;
jbe@383 57 RAISE EXCEPTION 'Tried to modify data after voting has been closed.';
jbe@383 58 END IF;
jbe@383 59 RETURN NULL;
jbe@383 60 END;
jbe@383 61 $$;
jbe@383 62
jbe@383 63 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@383 64 RETURNS VOID
jbe@383 65 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@383 66 DECLARE
jbe@383 67 "area_id_v" "area"."id"%TYPE;
jbe@383 68 "unit_id_v" "unit"."id"%TYPE;
jbe@383 69 "member_id_v" "member"."id"%TYPE;
jbe@383 70 BEGIN
jbe@383 71 PERFORM "require_transaction_isolation"();
jbe@383 72 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
jbe@383 73 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
jbe@383 74 -- override protection triggers:
jbe@383 75 DELETE FROM "internal_session_store";
jbe@383 76 INSERT INTO "internal_session_store" ("backend_pid", "key", "value")
jbe@383 77 VALUES (pg_backend_pid(), 'override_protection_triggers', TRUE::TEXT);
jbe@383 78 -- delete timestamp of voting comment:
jbe@383 79 UPDATE "direct_voter" SET "comment_changed" = NULL
jbe@383 80 WHERE "issue_id" = "issue_id_p";
jbe@383 81 -- delete delegating votes (in cases of manual reset of issue state):
jbe@383 82 DELETE FROM "delegating_voter"
jbe@383 83 WHERE "issue_id" = "issue_id_p";
jbe@383 84 -- delete votes from non-privileged voters:
jbe@383 85 DELETE FROM "direct_voter"
jbe@383 86 USING (
jbe@383 87 SELECT
jbe@383 88 "direct_voter"."member_id"
jbe@383 89 FROM "direct_voter"
jbe@383 90 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
jbe@383 91 LEFT JOIN "privilege"
jbe@383 92 ON "privilege"."unit_id" = "unit_id_v"
jbe@383 93 AND "privilege"."member_id" = "direct_voter"."member_id"
jbe@383 94 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
jbe@383 95 "member"."active" = FALSE OR
jbe@383 96 "privilege"."voting_right" ISNULL OR
jbe@383 97 "privilege"."voting_right" = FALSE
jbe@383 98 )
jbe@383 99 ) AS "subquery"
jbe@383 100 WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@383 101 AND "direct_voter"."member_id" = "subquery"."member_id";
jbe@383 102 -- consider delegations:
jbe@383 103 UPDATE "direct_voter" SET "weight" = 1
jbe@383 104 WHERE "issue_id" = "issue_id_p";
jbe@383 105 PERFORM "add_vote_delegations"("issue_id_p");
jbe@383 106 -- finish overriding protection triggers (mandatory, as pids may be reused):
jbe@383 107 DELETE FROM "internal_session_store";
jbe@383 108 -- materialize battle_view:
jbe@383 109 -- NOTE: "closed" column of issue must be set at this point
jbe@383 110 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@383 111 INSERT INTO "battle" (
jbe@383 112 "issue_id",
jbe@383 113 "winning_initiative_id", "losing_initiative_id",
jbe@383 114 "count"
jbe@383 115 ) SELECT
jbe@383 116 "issue_id",
jbe@383 117 "winning_initiative_id", "losing_initiative_id",
jbe@383 118 "count"
jbe@383 119 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@383 120 -- set voter count:
jbe@383 121 UPDATE "issue" SET
jbe@383 122 "voter_count" = (
jbe@383 123 SELECT coalesce(sum("weight"), 0)
jbe@383 124 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@383 125 )
jbe@383 126 WHERE "id" = "issue_id_p";
jbe@383 127 -- copy "positive_votes" and "negative_votes" from "battle" table:
jbe@383 128 UPDATE "initiative" SET
jbe@383 129 "positive_votes" = "battle_win"."count",
jbe@383 130 "negative_votes" = "battle_lose"."count"
jbe@383 131 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
jbe@383 132 WHERE
jbe@383 133 "battle_win"."issue_id" = "issue_id_p" AND
jbe@383 134 "battle_win"."winning_initiative_id" = "initiative"."id" AND
jbe@383 135 "battle_win"."losing_initiative_id" ISNULL AND
jbe@383 136 "battle_lose"."issue_id" = "issue_id_p" AND
jbe@383 137 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
jbe@383 138 "battle_lose"."winning_initiative_id" ISNULL;
jbe@383 139 END;
jbe@383 140 $$;
jbe@383 141
jbe@383 142 COMMIT;

Impressum / About Us