liquid_feedback_core

annotate update/core-update.v2.2.2-v2.2.3.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 e474e9e1240a
children
rev   line source
jbe@385 1 BEGIN;
jbe@385 2
jbe@385 3 CREATE OR REPLACE VIEW "liquid_feedback_version" AS
jbe@385 4 SELECT * FROM (VALUES ('2.2.3', 2, 2, 3))
jbe@385 5 AS "subquery"("string", "major", "minor", "revision");
jbe@385 6
jbe@385 7 DROP TABLE "internal_session_store";
jbe@385 8
jbe@385 9 CREATE TABLE "temporary_transaction_data" (
jbe@385 10 PRIMARY KEY ("txid", "key"),
jbe@385 11 "txid" INT8 DEFAULT txid_current(),
jbe@385 12 "key" TEXT,
jbe@385 13 "value" TEXT NOT NULL );
jbe@385 14
jbe@385 15 COMMENT ON TABLE "temporary_transaction_data" IS 'Table to store temporary transaction data; shall be emptied before a transaction is committed';
jbe@385 16
jbe@385 17 COMMENT ON COLUMN "temporary_transaction_data"."txid" IS 'Value returned by function txid_current(); should be added to WHERE clause, when doing SELECT on this table, but ignored when doing DELETE on this table';
jbe@385 18
jbe@385 19 CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"()
jbe@385 20 RETURNS TRIGGER
jbe@385 21 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@385 22 DECLARE
jbe@385 23 "issue_id_v" "issue"."id"%TYPE;
jbe@385 24 "issue_row" "issue"%ROWTYPE;
jbe@385 25 BEGIN
jbe@385 26 IF EXISTS (
jbe@385 27 SELECT NULL FROM "temporary_transaction_data"
jbe@385 28 WHERE "txid" = txid_current()
jbe@385 29 AND "key" = 'override_protection_triggers'
jbe@385 30 AND "value" = TRUE::TEXT
jbe@385 31 ) THEN
jbe@385 32 RETURN NULL;
jbe@385 33 END IF;
jbe@385 34 IF TG_OP = 'DELETE' THEN
jbe@385 35 "issue_id_v" := OLD."issue_id";
jbe@385 36 ELSE
jbe@385 37 "issue_id_v" := NEW."issue_id";
jbe@385 38 END IF;
jbe@385 39 SELECT INTO "issue_row" * FROM "issue"
jbe@385 40 WHERE "id" = "issue_id_v" FOR SHARE;
jbe@385 41 IF (
jbe@385 42 "issue_row"."closed" NOTNULL OR (
jbe@385 43 "issue_row"."state" = 'voting' AND
jbe@385 44 "issue_row"."phase_finished" NOTNULL
jbe@385 45 )
jbe@385 46 ) THEN
jbe@385 47 IF
jbe@385 48 TG_RELID = 'direct_voter'::regclass AND
jbe@385 49 TG_OP = 'UPDATE'
jbe@385 50 THEN
jbe@385 51 IF
jbe@385 52 OLD."issue_id" = NEW."issue_id" AND
jbe@385 53 OLD."member_id" = NEW."member_id" AND
jbe@385 54 OLD."weight" = NEW."weight"
jbe@385 55 THEN
jbe@385 56 RETURN NULL; -- allows changing of voter comment
jbe@385 57 END IF;
jbe@385 58 END IF;
jbe@385 59 RAISE EXCEPTION 'Tried to modify data after voting has been closed.';
jbe@385 60 END IF;
jbe@385 61 RETURN NULL;
jbe@385 62 END;
jbe@385 63 $$;
jbe@385 64
jbe@385 65 CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE)
jbe@385 66 RETURNS VOID
jbe@385 67 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@385 68 DECLARE
jbe@385 69 "area_id_v" "area"."id"%TYPE;
jbe@385 70 "unit_id_v" "unit"."id"%TYPE;
jbe@385 71 "member_id_v" "member"."id"%TYPE;
jbe@385 72 BEGIN
jbe@385 73 PERFORM "require_transaction_isolation"();
jbe@385 74 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p";
jbe@385 75 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v";
jbe@385 76 -- override protection triggers:
jbe@385 77 INSERT INTO "temporary_transaction_data" ("key", "value")
jbe@385 78 VALUES ('override_protection_triggers', TRUE::TEXT);
jbe@385 79 -- delete timestamp of voting comment:
jbe@385 80 UPDATE "direct_voter" SET "comment_changed" = NULL
jbe@385 81 WHERE "issue_id" = "issue_id_p";
jbe@385 82 -- delete delegating votes (in cases of manual reset of issue state):
jbe@385 83 DELETE FROM "delegating_voter"
jbe@385 84 WHERE "issue_id" = "issue_id_p";
jbe@385 85 -- delete votes from non-privileged voters:
jbe@385 86 DELETE FROM "direct_voter"
jbe@385 87 USING (
jbe@385 88 SELECT
jbe@385 89 "direct_voter"."member_id"
jbe@385 90 FROM "direct_voter"
jbe@385 91 JOIN "member" ON "direct_voter"."member_id" = "member"."id"
jbe@385 92 LEFT JOIN "privilege"
jbe@385 93 ON "privilege"."unit_id" = "unit_id_v"
jbe@385 94 AND "privilege"."member_id" = "direct_voter"."member_id"
jbe@385 95 WHERE "direct_voter"."issue_id" = "issue_id_p" AND (
jbe@385 96 "member"."active" = FALSE OR
jbe@385 97 "privilege"."voting_right" ISNULL OR
jbe@385 98 "privilege"."voting_right" = FALSE
jbe@385 99 )
jbe@385 100 ) AS "subquery"
jbe@385 101 WHERE "direct_voter"."issue_id" = "issue_id_p"
jbe@385 102 AND "direct_voter"."member_id" = "subquery"."member_id";
jbe@385 103 -- consider delegations:
jbe@385 104 UPDATE "direct_voter" SET "weight" = 1
jbe@385 105 WHERE "issue_id" = "issue_id_p";
jbe@385 106 PERFORM "add_vote_delegations"("issue_id_p");
jbe@385 107 -- finish overriding protection triggers (avoids garbage):
jbe@385 108 DELETE FROM "temporary_transaction_data"
jbe@385 109 WHERE "key" = 'override_protection_triggers';
jbe@385 110 -- materialize battle_view:
jbe@385 111 -- NOTE: "closed" column of issue must be set at this point
jbe@385 112 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p";
jbe@385 113 INSERT INTO "battle" (
jbe@385 114 "issue_id",
jbe@385 115 "winning_initiative_id", "losing_initiative_id",
jbe@385 116 "count"
jbe@385 117 ) SELECT
jbe@385 118 "issue_id",
jbe@385 119 "winning_initiative_id", "losing_initiative_id",
jbe@385 120 "count"
jbe@385 121 FROM "battle_view" WHERE "issue_id" = "issue_id_p";
jbe@385 122 -- set voter count:
jbe@385 123 UPDATE "issue" SET
jbe@385 124 "voter_count" = (
jbe@385 125 SELECT coalesce(sum("weight"), 0)
jbe@385 126 FROM "direct_voter" WHERE "issue_id" = "issue_id_p"
jbe@385 127 )
jbe@385 128 WHERE "id" = "issue_id_p";
jbe@385 129 -- copy "positive_votes" and "negative_votes" from "battle" table:
jbe@385 130 UPDATE "initiative" SET
jbe@385 131 "positive_votes" = "battle_win"."count",
jbe@385 132 "negative_votes" = "battle_lose"."count"
jbe@385 133 FROM "battle" AS "battle_win", "battle" AS "battle_lose"
jbe@385 134 WHERE
jbe@385 135 "battle_win"."issue_id" = "issue_id_p" AND
jbe@385 136 "battle_win"."winning_initiative_id" = "initiative"."id" AND
jbe@385 137 "battle_win"."losing_initiative_id" ISNULL AND
jbe@385 138 "battle_lose"."issue_id" = "issue_id_p" AND
jbe@385 139 "battle_lose"."losing_initiative_id" = "initiative"."id" AND
jbe@385 140 "battle_lose"."winning_initiative_id" ISNULL;
jbe@385 141 END;
jbe@385 142 $$;
jbe@385 143
jbe@385 144 CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE)
jbe@385 145 RETURNS VOID
jbe@385 146 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@385 147 BEGIN
jbe@385 148 IF EXISTS (
jbe@385 149 SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL
jbe@385 150 ) THEN
jbe@385 151 -- override protection triggers:
jbe@385 152 INSERT INTO "temporary_transaction_data" ("key", "value")
jbe@385 153 VALUES ('override_protection_triggers', TRUE::TEXT);
jbe@385 154 -- clean data:
jbe@385 155 DELETE FROM "delegating_voter"
jbe@385 156 WHERE "issue_id" = "issue_id_p";
jbe@385 157 DELETE FROM "direct_voter"
jbe@385 158 WHERE "issue_id" = "issue_id_p";
jbe@385 159 DELETE FROM "delegating_interest_snapshot"
jbe@385 160 WHERE "issue_id" = "issue_id_p";
jbe@385 161 DELETE FROM "direct_interest_snapshot"
jbe@385 162 WHERE "issue_id" = "issue_id_p";
jbe@385 163 DELETE FROM "delegating_population_snapshot"
jbe@385 164 WHERE "issue_id" = "issue_id_p";
jbe@385 165 DELETE FROM "direct_population_snapshot"
jbe@385 166 WHERE "issue_id" = "issue_id_p";
jbe@385 167 DELETE FROM "non_voter"
jbe@385 168 WHERE "issue_id" = "issue_id_p";
jbe@385 169 DELETE FROM "delegation"
jbe@385 170 WHERE "issue_id" = "issue_id_p";
jbe@385 171 DELETE FROM "supporter"
jbe@385 172 USING "initiative" -- NOTE: due to missing index on issue_id
jbe@385 173 WHERE "initiative"."issue_id" = "issue_id_p"
jbe@385 174 AND "supporter"."initiative_id" = "initiative_id";
jbe@385 175 -- mark issue as cleaned:
jbe@385 176 UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p";
jbe@385 177 -- finish overriding protection triggers (avoids garbage):
jbe@385 178 DELETE FROM "temporary_transaction_data"
jbe@385 179 WHERE "key" = 'override_protection_triggers';
jbe@385 180 END IF;
jbe@385 181 RETURN;
jbe@385 182 END;
jbe@385 183 $$;
jbe@385 184
jbe@385 185 CREATE OR REPLACE FUNCTION "delete_private_data"()
jbe@385 186 RETURNS VOID
jbe@385 187 LANGUAGE 'plpgsql' VOLATILE AS $$
jbe@385 188 BEGIN
jbe@385 189 DELETE FROM "temporary_transaction_data";
jbe@385 190 DELETE FROM "member" WHERE "activated" ISNULL;
jbe@385 191 UPDATE "member" SET
jbe@385 192 "invite_code" = NULL,
jbe@385 193 "invite_code_expiry" = NULL,
jbe@385 194 "admin_comment" = NULL,
jbe@385 195 "last_login" = NULL,
jbe@385 196 "login" = NULL,
jbe@385 197 "password" = NULL,
jbe@385 198 "lang" = NULL,
jbe@385 199 "notify_email" = NULL,
jbe@385 200 "notify_email_unconfirmed" = NULL,
jbe@385 201 "notify_email_secret" = NULL,
jbe@385 202 "notify_email_secret_expiry" = NULL,
jbe@385 203 "notify_email_lock_expiry" = NULL,
jbe@385 204 "notify_level" = NULL,
jbe@385 205 "password_reset_secret" = NULL,
jbe@385 206 "password_reset_secret_expiry" = NULL,
jbe@385 207 "organizational_unit" = NULL,
jbe@385 208 "internal_posts" = NULL,
jbe@385 209 "realname" = NULL,
jbe@385 210 "birthday" = NULL,
jbe@385 211 "address" = NULL,
jbe@385 212 "email" = NULL,
jbe@385 213 "xmpp_address" = NULL,
jbe@385 214 "website" = NULL,
jbe@385 215 "phone" = NULL,
jbe@385 216 "mobile_phone" = NULL,
jbe@385 217 "profession" = NULL,
jbe@385 218 "external_memberships" = NULL,
jbe@385 219 "external_posts" = NULL,
jbe@385 220 "formatting_engine" = NULL,
jbe@385 221 "statement" = NULL;
jbe@385 222 -- "text_search_data" is updated by triggers
jbe@385 223 DELETE FROM "setting";
jbe@385 224 DELETE FROM "setting_map";
jbe@385 225 DELETE FROM "member_relation_setting";
jbe@385 226 DELETE FROM "member_image";
jbe@385 227 DELETE FROM "contact";
jbe@385 228 DELETE FROM "ignored_member";
jbe@385 229 DELETE FROM "session";
jbe@385 230 DELETE FROM "area_setting";
jbe@385 231 DELETE FROM "issue_setting";
jbe@385 232 DELETE FROM "ignored_initiative";
jbe@385 233 DELETE FROM "initiative_setting";
jbe@385 234 DELETE FROM "suggestion_setting";
jbe@385 235 DELETE FROM "non_voter";
jbe@385 236 DELETE FROM "direct_voter" USING "issue"
jbe@385 237 WHERE "direct_voter"."issue_id" = "issue"."id"
jbe@385 238 AND "issue"."closed" ISNULL;
jbe@385 239 RETURN;
jbe@385 240 END;
jbe@385 241 $$;
jbe@385 242
jbe@385 243 COMMIT;

Impressum / About Us