liquid_feedback_core
changeset 307:847d59f94ceb
Merged fix from v2.0.12: Removed unwanted (and broken) tie-breaking by approval rate
| author | jbe |
|---|---|
| date | Fri Oct 05 13:45:37 2012 +0200 (2012-10-05) |
| parents | e403f47525ce d8b455e3a9be |
| children | 5c98265b39a0 |
| files | .hgtags core.sql update/core-update.v2.0.11-v2.1.0.sql update/core-update.v2.0.12-v2.1.0.sql |
line diff
1.1 --- a/.hgtags Sun Sep 30 12:59:07 2012 +0200 1.2 +++ b/.hgtags Fri Oct 05 13:45:37 2012 +0200 1.3 @@ -58,3 +58,4 @@ 1.4 5ee7eed2f5b09e6a45fda0ede32a7af96f5f6cd1 v2.0.9 1.5 389200fd973d84082a7850e7e72adb0736100ca3 v2.0.10 1.6 e818f83e133bc545681b4bc06ce3c4f280de4672 v2.0.11 1.7 +a839e7efde9f8ff43a6ea71cf4d694bec84a1de1 v2.0.12
2.1 --- a/core.sql Sun Sep 30 12:59:07 2012 +0200 2.2 +++ b/core.sql Fri Oct 05 13:45:37 2012 +0200 2.3 @@ -2860,37 +2860,6 @@ 2.4 2.5 2.6 2.7 ------------------------------- 2.8 --- Comparison by vote count -- 2.9 ------------------------------- 2.10 - 2.11 -CREATE FUNCTION "vote_ratio" 2.12 - ( "positive_votes_p" "initiative"."positive_votes"%TYPE, 2.13 - "negative_votes_p" "initiative"."negative_votes"%TYPE ) 2.14 - RETURNS FLOAT8 2.15 - LANGUAGE 'plpgsql' STABLE AS $$ 2.16 - BEGIN 2.17 - IF "positive_votes_p" > 0 AND "negative_votes_p" > 0 THEN 2.18 - RETURN 2.19 - "positive_votes_p"::FLOAT8 / 2.20 - ("positive_votes_p" + "negative_votes_p")::FLOAT8; 2.21 - ELSIF "positive_votes_p" > 0 THEN 2.22 - RETURN "positive_votes_p"; 2.23 - ELSIF "negative_votes_p" > 0 THEN 2.24 - RETURN 1 - "negative_votes_p"; 2.25 - ELSE 2.26 - RETURN 0.5; 2.27 - END IF; 2.28 - END; 2.29 - $$; 2.30 - 2.31 -COMMENT ON FUNCTION "vote_ratio" 2.32 - ( "initiative"."positive_votes"%TYPE, 2.33 - "initiative"."negative_votes"%TYPE ) 2.34 - IS 'Returns a number, which can be used for comparison of initiatives based on count of approvals and disapprovals. Greater numbers indicate a better result. This function is NOT injective.'; 2.35 - 2.36 - 2.37 - 2.38 ------------------------------------------------ 2.39 -- Locking for snapshots and voting procedure -- 2.40 ------------------------------------------------ 2.41 @@ -4130,7 +4099,6 @@ 2.42 WHERE "issue_id" = "issue_id_p" AND "eligible" 2.43 ORDER BY 2.44 "schulze_rank", 2.45 - "vote_ratio"("positive_votes", "negative_votes"), 2.46 "id" 2.47 LIMIT 1 2.48 ) AS "subquery" 2.49 @@ -4145,7 +4113,6 @@ 2.50 "winner" DESC, 2.51 "eligible" DESC, 2.52 "schulze_rank", 2.53 - "vote_ratio"("positive_votes", "negative_votes"), 2.54 "id" 2.55 LOOP 2.56 UPDATE "initiative" SET "rank" = "rank_v"
3.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 3.2 +++ b/update/core-update.v2.0.11-v2.0.12.sql Fri Oct 05 13:45:37 2012 +0200 3.3 @@ -0,0 +1,309 @@ 3.4 +BEGIN; 3.5 + 3.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 3.7 + SELECT * FROM (VALUES ('2.0.12', 2, 0, 12)) 3.8 + AS "subquery"("string", "major", "minor", "revision"); 3.9 + 3.10 +CREATE OR REPLACE FUNCTION "calculate_ranks"("issue_id_p" "issue"."id"%TYPE) 3.11 + RETURNS VOID 3.12 + LANGUAGE 'plpgsql' VOLATILE AS $$ 3.13 + DECLARE 3.14 + "issue_row" "issue"%ROWTYPE; 3.15 + "policy_row" "policy"%ROWTYPE; 3.16 + "dimension_v" INTEGER; 3.17 + "vote_matrix" INT4[][]; -- absolute votes 3.18 + "matrix" INT8[][]; -- defeat strength / best paths 3.19 + "i" INTEGER; 3.20 + "j" INTEGER; 3.21 + "k" INTEGER; 3.22 + "battle_row" "battle"%ROWTYPE; 3.23 + "rank_ary" INT4[]; 3.24 + "rank_v" INT4; 3.25 + "done_v" INTEGER; 3.26 + "winners_ary" INTEGER[]; 3.27 + "initiative_id_v" "initiative"."id"%TYPE; 3.28 + BEGIN 3.29 + SELECT * INTO "issue_row" 3.30 + FROM "issue" WHERE "id" = "issue_id_p" 3.31 + FOR UPDATE; 3.32 + SELECT * INTO "policy_row" 3.33 + FROM "policy" WHERE "id" = "issue_row"."policy_id"; 3.34 + SELECT count(1) INTO "dimension_v" 3.35 + FROM "battle_participant" WHERE "issue_id" = "issue_id_p"; 3.36 + -- Create "vote_matrix" with absolute number of votes in pairwise 3.37 + -- comparison: 3.38 + "vote_matrix" := array_fill(NULL::INT4, ARRAY["dimension_v", "dimension_v"]); 3.39 + "i" := 1; 3.40 + "j" := 2; 3.41 + FOR "battle_row" IN 3.42 + SELECT * FROM "battle" WHERE "issue_id" = "issue_id_p" 3.43 + ORDER BY 3.44 + "winning_initiative_id" NULLS LAST, 3.45 + "losing_initiative_id" NULLS LAST 3.46 + LOOP 3.47 + "vote_matrix"["i"]["j"] := "battle_row"."count"; 3.48 + IF "j" = "dimension_v" THEN 3.49 + "i" := "i" + 1; 3.50 + "j" := 1; 3.51 + ELSE 3.52 + "j" := "j" + 1; 3.53 + IF "j" = "i" THEN 3.54 + "j" := "j" + 1; 3.55 + END IF; 3.56 + END IF; 3.57 + END LOOP; 3.58 + IF "i" != "dimension_v" OR "j" != "dimension_v" + 1 THEN 3.59 + RAISE EXCEPTION 'Wrong battle count (should not happen)'; 3.60 + END IF; 3.61 + -- Store defeat strengths in "matrix" using "defeat_strength" 3.62 + -- function: 3.63 + "matrix" := array_fill(NULL::INT8, ARRAY["dimension_v", "dimension_v"]); 3.64 + "i" := 1; 3.65 + LOOP 3.66 + "j" := 1; 3.67 + LOOP 3.68 + IF "i" != "j" THEN 3.69 + "matrix"["i"]["j"] := "defeat_strength"( 3.70 + "vote_matrix"["i"]["j"], 3.71 + "vote_matrix"["j"]["i"] 3.72 + ); 3.73 + END IF; 3.74 + EXIT WHEN "j" = "dimension_v"; 3.75 + "j" := "j" + 1; 3.76 + END LOOP; 3.77 + EXIT WHEN "i" = "dimension_v"; 3.78 + "i" := "i" + 1; 3.79 + END LOOP; 3.80 + -- Find best paths: 3.81 + "i" := 1; 3.82 + LOOP 3.83 + "j" := 1; 3.84 + LOOP 3.85 + IF "i" != "j" THEN 3.86 + "k" := 1; 3.87 + LOOP 3.88 + IF "i" != "k" AND "j" != "k" THEN 3.89 + IF "matrix"["j"]["i"] < "matrix"["i"]["k"] THEN 3.90 + IF "matrix"["j"]["i"] > "matrix"["j"]["k"] THEN 3.91 + "matrix"["j"]["k"] := "matrix"["j"]["i"]; 3.92 + END IF; 3.93 + ELSE 3.94 + IF "matrix"["i"]["k"] > "matrix"["j"]["k"] THEN 3.95 + "matrix"["j"]["k"] := "matrix"["i"]["k"]; 3.96 + END IF; 3.97 + END IF; 3.98 + END IF; 3.99 + EXIT WHEN "k" = "dimension_v"; 3.100 + "k" := "k" + 1; 3.101 + END LOOP; 3.102 + END IF; 3.103 + EXIT WHEN "j" = "dimension_v"; 3.104 + "j" := "j" + 1; 3.105 + END LOOP; 3.106 + EXIT WHEN "i" = "dimension_v"; 3.107 + "i" := "i" + 1; 3.108 + END LOOP; 3.109 + -- Determine order of winners: 3.110 + "rank_ary" := array_fill(NULL::INT4, ARRAY["dimension_v"]); 3.111 + "rank_v" := 1; 3.112 + "done_v" := 0; 3.113 + LOOP 3.114 + "winners_ary" := '{}'; 3.115 + "i" := 1; 3.116 + LOOP 3.117 + IF "rank_ary"["i"] ISNULL THEN 3.118 + "j" := 1; 3.119 + LOOP 3.120 + IF 3.121 + "i" != "j" AND 3.122 + "rank_ary"["j"] ISNULL AND 3.123 + "matrix"["j"]["i"] > "matrix"["i"]["j"] 3.124 + THEN 3.125 + -- someone else is better 3.126 + EXIT; 3.127 + END IF; 3.128 + IF "j" = "dimension_v" THEN 3.129 + -- noone is better 3.130 + "winners_ary" := "winners_ary" || "i"; 3.131 + EXIT; 3.132 + END IF; 3.133 + "j" := "j" + 1; 3.134 + END LOOP; 3.135 + END IF; 3.136 + EXIT WHEN "i" = "dimension_v"; 3.137 + "i" := "i" + 1; 3.138 + END LOOP; 3.139 + "i" := 1; 3.140 + LOOP 3.141 + "rank_ary"["winners_ary"["i"]] := "rank_v"; 3.142 + "done_v" := "done_v" + 1; 3.143 + EXIT WHEN "i" = array_upper("winners_ary", 1); 3.144 + "i" := "i" + 1; 3.145 + END LOOP; 3.146 + EXIT WHEN "done_v" = "dimension_v"; 3.147 + "rank_v" := "rank_v" + 1; 3.148 + END LOOP; 3.149 + -- write preliminary results: 3.150 + "i" := 1; 3.151 + FOR "initiative_id_v" IN 3.152 + SELECT "id" FROM "initiative" 3.153 + WHERE "issue_id" = "issue_id_p" AND "admitted" 3.154 + ORDER BY "id" 3.155 + LOOP 3.156 + UPDATE "initiative" SET 3.157 + "direct_majority" = 3.158 + CASE WHEN "policy_row"."direct_majority_strict" THEN 3.159 + "positive_votes" * "policy_row"."direct_majority_den" > 3.160 + "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") 3.161 + ELSE 3.162 + "positive_votes" * "policy_row"."direct_majority_den" >= 3.163 + "policy_row"."direct_majority_num" * ("positive_votes"+"negative_votes") 3.164 + END 3.165 + AND "positive_votes" >= "policy_row"."direct_majority_positive" 3.166 + AND "issue_row"."voter_count"-"negative_votes" >= 3.167 + "policy_row"."direct_majority_non_negative", 3.168 + "indirect_majority" = 3.169 + CASE WHEN "policy_row"."indirect_majority_strict" THEN 3.170 + "positive_votes" * "policy_row"."indirect_majority_den" > 3.171 + "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") 3.172 + ELSE 3.173 + "positive_votes" * "policy_row"."indirect_majority_den" >= 3.174 + "policy_row"."indirect_majority_num" * ("positive_votes"+"negative_votes") 3.175 + END 3.176 + AND "positive_votes" >= "policy_row"."indirect_majority_positive" 3.177 + AND "issue_row"."voter_count"-"negative_votes" >= 3.178 + "policy_row"."indirect_majority_non_negative", 3.179 + "schulze_rank" = "rank_ary"["i"], 3.180 + "better_than_status_quo" = "rank_ary"["i"] < "rank_ary"["dimension_v"], 3.181 + "worse_than_status_quo" = "rank_ary"["i"] > "rank_ary"["dimension_v"], 3.182 + "multistage_majority" = "rank_ary"["i"] >= "rank_ary"["dimension_v"], 3.183 + "reverse_beat_path" = "matrix"["dimension_v"]["i"] >= 0, 3.184 + "eligible" = FALSE, 3.185 + "winner" = FALSE, 3.186 + "rank" = NULL -- NOTE: in cases of manual reset of issue state 3.187 + WHERE "id" = "initiative_id_v"; 3.188 + "i" := "i" + 1; 3.189 + END LOOP; 3.190 + IF "i" != "dimension_v" THEN 3.191 + RAISE EXCEPTION 'Wrong winner count (should not happen)'; 3.192 + END IF; 3.193 + -- take indirect majorities into account: 3.194 + LOOP 3.195 + UPDATE "initiative" SET "indirect_majority" = TRUE 3.196 + FROM ( 3.197 + SELECT "new_initiative"."id" AS "initiative_id" 3.198 + FROM "initiative" "old_initiative" 3.199 + JOIN "initiative" "new_initiative" 3.200 + ON "new_initiative"."issue_id" = "issue_id_p" 3.201 + AND "new_initiative"."indirect_majority" = FALSE 3.202 + JOIN "battle" "battle_win" 3.203 + ON "battle_win"."issue_id" = "issue_id_p" 3.204 + AND "battle_win"."winning_initiative_id" = "new_initiative"."id" 3.205 + AND "battle_win"."losing_initiative_id" = "old_initiative"."id" 3.206 + JOIN "battle" "battle_lose" 3.207 + ON "battle_lose"."issue_id" = "issue_id_p" 3.208 + AND "battle_lose"."losing_initiative_id" = "new_initiative"."id" 3.209 + AND "battle_lose"."winning_initiative_id" = "old_initiative"."id" 3.210 + WHERE "old_initiative"."issue_id" = "issue_id_p" 3.211 + AND "old_initiative"."indirect_majority" = TRUE 3.212 + AND CASE WHEN "policy_row"."indirect_majority_strict" THEN 3.213 + "battle_win"."count" * "policy_row"."indirect_majority_den" > 3.214 + "policy_row"."indirect_majority_num" * 3.215 + ("battle_win"."count"+"battle_lose"."count") 3.216 + ELSE 3.217 + "battle_win"."count" * "policy_row"."indirect_majority_den" >= 3.218 + "policy_row"."indirect_majority_num" * 3.219 + ("battle_win"."count"+"battle_lose"."count") 3.220 + END 3.221 + AND "battle_win"."count" >= "policy_row"."indirect_majority_positive" 3.222 + AND "issue_row"."voter_count"-"battle_lose"."count" >= 3.223 + "policy_row"."indirect_majority_non_negative" 3.224 + ) AS "subquery" 3.225 + WHERE "id" = "subquery"."initiative_id"; 3.226 + EXIT WHEN NOT FOUND; 3.227 + END LOOP; 3.228 + -- set "multistage_majority" for remaining matching initiatives: 3.229 + UPDATE "initiative" SET "multistage_majority" = TRUE 3.230 + FROM ( 3.231 + SELECT "losing_initiative"."id" AS "initiative_id" 3.232 + FROM "initiative" "losing_initiative" 3.233 + JOIN "initiative" "winning_initiative" 3.234 + ON "winning_initiative"."issue_id" = "issue_id_p" 3.235 + AND "winning_initiative"."admitted" 3.236 + JOIN "battle" "battle_win" 3.237 + ON "battle_win"."issue_id" = "issue_id_p" 3.238 + AND "battle_win"."winning_initiative_id" = "winning_initiative"."id" 3.239 + AND "battle_win"."losing_initiative_id" = "losing_initiative"."id" 3.240 + JOIN "battle" "battle_lose" 3.241 + ON "battle_lose"."issue_id" = "issue_id_p" 3.242 + AND "battle_lose"."losing_initiative_id" = "winning_initiative"."id" 3.243 + AND "battle_lose"."winning_initiative_id" = "losing_initiative"."id" 3.244 + WHERE "losing_initiative"."issue_id" = "issue_id_p" 3.245 + AND "losing_initiative"."admitted" 3.246 + AND "winning_initiative"."schulze_rank" < 3.247 + "losing_initiative"."schulze_rank" 3.248 + AND "battle_win"."count" > "battle_lose"."count" 3.249 + AND ( 3.250 + "battle_win"."count" > "winning_initiative"."positive_votes" OR 3.251 + "battle_lose"."count" < "losing_initiative"."negative_votes" ) 3.252 + ) AS "subquery" 3.253 + WHERE "id" = "subquery"."initiative_id"; 3.254 + -- mark eligible initiatives: 3.255 + UPDATE "initiative" SET "eligible" = TRUE 3.256 + WHERE "issue_id" = "issue_id_p" 3.257 + AND "initiative"."direct_majority" 3.258 + AND "initiative"."indirect_majority" 3.259 + AND "initiative"."better_than_status_quo" 3.260 + AND ( 3.261 + "policy_row"."no_multistage_majority" = FALSE OR 3.262 + "initiative"."multistage_majority" = FALSE ) 3.263 + AND ( 3.264 + "policy_row"."no_reverse_beat_path" = FALSE OR 3.265 + "initiative"."reverse_beat_path" = FALSE ); 3.266 + -- mark final winner: 3.267 + UPDATE "initiative" SET "winner" = TRUE 3.268 + FROM ( 3.269 + SELECT "id" AS "initiative_id" 3.270 + FROM "initiative" 3.271 + WHERE "issue_id" = "issue_id_p" AND "eligible" 3.272 + ORDER BY 3.273 + "schulze_rank", 3.274 + "id" 3.275 + LIMIT 1 3.276 + ) AS "subquery" 3.277 + WHERE "id" = "subquery"."initiative_id"; 3.278 + -- write (final) ranks: 3.279 + "rank_v" := 1; 3.280 + FOR "initiative_id_v" IN 3.281 + SELECT "id" 3.282 + FROM "initiative" 3.283 + WHERE "issue_id" = "issue_id_p" AND "admitted" 3.284 + ORDER BY 3.285 + "winner" DESC, 3.286 + "eligible" DESC, 3.287 + "schulze_rank", 3.288 + "id" 3.289 + LOOP 3.290 + UPDATE "initiative" SET "rank" = "rank_v" 3.291 + WHERE "id" = "initiative_id_v"; 3.292 + "rank_v" := "rank_v" + 1; 3.293 + END LOOP; 3.294 + -- set schulze rank of status quo and mark issue as finished: 3.295 + UPDATE "issue" SET 3.296 + "status_quo_schulze_rank" = "rank_ary"["dimension_v"], 3.297 + "state" = 3.298 + CASE WHEN EXISTS ( 3.299 + SELECT NULL FROM "initiative" 3.300 + WHERE "issue_id" = "issue_id_p" AND "winner" 3.301 + ) THEN 3.302 + 'finished_with_winner'::"issue_state" 3.303 + ELSE 3.304 + 'finished_without_winner'::"issue_state" 3.305 + END, 3.306 + "ranks_available" = TRUE 3.307 + WHERE "id" = "issue_id_p"; 3.308 + RETURN; 3.309 + END; 3.310 + $$; 3.311 + 3.312 +COMMIT;
4.1 --- a/update/core-update.v2.0.11-v2.1.0.sql Sun Sep 30 12:59:07 2012 +0200 4.2 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 4.3 @@ -1,458 +0,0 @@ 4.4 -BEGIN; 4.5 - 4.6 - 4.7 --- update version number 4.8 - 4.9 -CREATE OR REPLACE VIEW "liquid_feedback_version" AS 4.10 - SELECT * FROM (VALUES ('2.1.0', 2, 1, 0)) 4.11 - AS "subquery"("string", "major", "minor", "revision"); 4.12 - 4.13 - 4.14 --- old API tables are now deprecated 4.15 - 4.16 -COMMENT ON TYPE "application_access_level" IS 'DEPRECATED, WILL BE REMOVED! Access privileges for applications using the API'; 4.17 -COMMENT ON TABLE "member_application" IS 'DEPRECATED, WILL BE REMOVED! Registered application being allowed to use the API'; 4.18 - 4.19 - 4.20 --- new polling mode and changed privileges 4.21 - 4.22 -ALTER TABLE "policy" ADD COLUMN "polling" BOOLEAN NOT NULL DEFAULT FALSE; 4.23 -ALTER TABLE "policy" ALTER COLUMN "admission_time" DROP NOT NULL; 4.24 -ALTER TABLE "policy" ALTER COLUMN "discussion_time" DROP NOT NULL; 4.25 -ALTER TABLE "policy" ALTER COLUMN "verification_time" DROP NOT NULL; 4.26 -ALTER TABLE "policy" ALTER COLUMN "voting_time" DROP NOT NULL; 4.27 -ALTER TABLE "policy" ALTER COLUMN "issue_quorum_num" DROP NOT NULL; 4.28 -ALTER TABLE "policy" ALTER COLUMN "issue_quorum_den" DROP NOT NULL; 4.29 -ALTER TABLE "policy" ADD CONSTRAINT "timing" CHECK ( 4.30 - ( "polling" = FALSE AND 4.31 - "admission_time" NOTNULL AND "discussion_time" NOTNULL AND 4.32 - "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR 4.33 - ( "polling" = TRUE AND 4.34 - "admission_time" ISNULL AND "discussion_time" NOTNULL AND 4.35 - "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR 4.36 - ( "polling" = TRUE AND 4.37 - "admission_time" ISNULL AND "discussion_time" ISNULL AND 4.38 - "verification_time" ISNULL AND "voting_time" ISNULL ) ); 4.39 -ALTER TABLE "policy" ADD CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK ( 4.40 - "polling" = "issue_quorum_num" ISNULL AND 4.41 - "polling" = "issue_quorum_den" ISNULL ); 4.42 -COMMENT ON COLUMN "policy"."polling" IS 'TRUE = special policy for non-user-generated issues without issue quorum, where certain initiatives (those having the "polling" flag set) do not need to pass the initiative quorum; "admission_time" MUST be set to NULL, the other timings may be set to NULL altogether, allowing individual timing for those issues'; 4.43 - 4.44 -ALTER TABLE "issue" ALTER COLUMN "admission_time" DROP NOT NULL; 4.45 -ALTER TABLE "issue" ADD CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK ( 4.46 - "admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created") ); 4.47 - 4.48 -ALTER TABLE "initiative" ADD COLUMN "polling" BOOLEAN NOT NULL DEFAULT FALSE; 4.49 -COMMENT ON COLUMN "initiative"."polling" IS 'Initiative does not need to pass the initiative quorum (see "policy"."polling")'; 4.50 - 4.51 -ALTER TABLE "privilege" RENAME COLUMN "voting_right_manager" TO "member_manager"; 4.52 -ALTER TABLE "privilege" ADD COLUMN "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE; 4.53 -ALTER TABLE "privilege" ADD COLUMN "polling_right" BOOLEAN NOT NULL DEFAULT FALSE; 4.54 -UPDATE "privilege" SET "initiative_right" = "voting_right"; 4.55 -COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke any privileges to/from other members'; 4.56 -COMMENT ON COLUMN "privilege"."member_manager" IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"'; 4.57 -COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative'; 4.58 -COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to support initiatives, create and rate suggestions, and to vote'; 4.59 -COMMENT ON COLUMN "privilege"."polling_right" IS 'Right to create issues with policies having the "policy"."polling" flag set, and to add initiatives having the "initiative"."polling" flag set to those issues'; 4.60 - 4.61 -DROP VIEW "member_contingent_left"; 4.62 -DROP VIEW "member_contingent"; 4.63 -ALTER TABLE "contingent" DROP CONSTRAINT "contingent_pkey"; 4.64 -ALTER TABLE "contingent" ALTER COLUMN "time_frame" DROP NOT NULL; 4.65 -ALTER TABLE "contingent" ADD COLUMN "polling" BOOLEAN DEFAULT FALSE; 4.66 -ALTER TABLE "contingent" ADD PRIMARY KEY ("polling", "time_frame"); 4.67 -ALTER TABLE "contingent" ALTER COLUMN "polling" DROP DEFAULT; 4.68 -COMMENT ON COLUMN "contingent"."polling" IS 'Determines if settings are for creating initiatives and new drafts of initiatives with "polling" flag set'; 4.69 - 4.70 -CREATE VIEW "member_contingent" AS 4.71 - SELECT 4.72 - "member"."id" AS "member_id", 4.73 - "contingent"."polling", 4.74 - "contingent"."time_frame", 4.75 - CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN 4.76 - ( 4.77 - SELECT count(1) FROM "draft" 4.78 - JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id" 4.79 - WHERE "draft"."author_id" = "member"."id" 4.80 - AND "initiative"."polling" = "contingent"."polling" 4.81 - AND "draft"."created" > now() - "contingent"."time_frame" 4.82 - ) + ( 4.83 - SELECT count(1) FROM "suggestion" 4.84 - JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id" 4.85 - WHERE "suggestion"."author_id" = "member"."id" 4.86 - AND "contingent"."polling" = FALSE 4.87 - AND "suggestion"."created" > now() - "contingent"."time_frame" 4.88 - ) 4.89 - ELSE NULL END AS "text_entry_count", 4.90 - "contingent"."text_entry_limit", 4.91 - CASE WHEN "contingent"."initiative_limit" NOTNULL THEN ( 4.92 - SELECT count(1) FROM "opening_draft" AS "draft" 4.93 - JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id" 4.94 - WHERE "draft"."author_id" = "member"."id" 4.95 - AND "initiative"."polling" = "contingent"."polling" 4.96 - AND "draft"."created" > now() - "contingent"."time_frame" 4.97 - ) ELSE NULL END AS "initiative_count", 4.98 - "contingent"."initiative_limit" 4.99 - FROM "member" CROSS JOIN "contingent"; 4.100 - 4.101 -COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.'; 4.102 - 4.103 -COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row'; 4.104 -COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row'; 4.105 - 4.106 -CREATE VIEW "member_contingent_left" AS 4.107 - SELECT 4.108 - "member_id", 4.109 - "polling", 4.110 - max("text_entry_limit" - "text_entry_count") AS "text_entries_left", 4.111 - max("initiative_limit" - "initiative_count") AS "initiatives_left" 4.112 - FROM "member_contingent" GROUP BY "member_id", "polling"; 4.113 - 4.114 -COMMENT ON VIEW "member_contingent_left" IS 'Amount of text entries or initiatives which can be posted now instantly by a member. This view should be used by a frontend to determine, if the contingent for posting is exhausted.'; 4.115 - 4.116 -CREATE OR REPLACE FUNCTION "freeze_after_snapshot" 4.117 - ( "issue_id_p" "issue"."id"%TYPE ) 4.118 - RETURNS VOID 4.119 - LANGUAGE 'plpgsql' VOLATILE AS $$ 4.120 - DECLARE 4.121 - "issue_row" "issue"%ROWTYPE; 4.122 - "policy_row" "policy"%ROWTYPE; 4.123 - "initiative_row" "initiative"%ROWTYPE; 4.124 - BEGIN 4.125 - SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 4.126 - SELECT * INTO "policy_row" 4.127 - FROM "policy" WHERE "id" = "issue_row"."policy_id"; 4.128 - PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze'); 4.129 - FOR "initiative_row" IN 4.130 - SELECT * FROM "initiative" 4.131 - WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 4.132 - LOOP 4.133 - IF 4.134 - "initiative_row"."polling" OR ( 4.135 - "initiative_row"."satisfied_supporter_count" > 0 AND 4.136 - "initiative_row"."satisfied_supporter_count" * 4.137 - "policy_row"."initiative_quorum_den" >= 4.138 - "issue_row"."population" * "policy_row"."initiative_quorum_num" 4.139 - ) 4.140 - THEN 4.141 - UPDATE "initiative" SET "admitted" = TRUE 4.142 - WHERE "id" = "initiative_row"."id"; 4.143 - ELSE 4.144 - UPDATE "initiative" SET "admitted" = FALSE 4.145 - WHERE "id" = "initiative_row"."id"; 4.146 - END IF; 4.147 - END LOOP; 4.148 - IF EXISTS ( 4.149 - SELECT NULL FROM "initiative" 4.150 - WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE 4.151 - ) THEN 4.152 - UPDATE "issue" SET 4.153 - "state" = 'voting', 4.154 - "accepted" = coalesce("accepted", now()), 4.155 - "half_frozen" = coalesce("half_frozen", now()), 4.156 - "fully_frozen" = now() 4.157 - WHERE "id" = "issue_id_p"; 4.158 - ELSE 4.159 - UPDATE "issue" SET 4.160 - "state" = 'canceled_no_initiative_admitted', 4.161 - "accepted" = coalesce("accepted", now()), 4.162 - "half_frozen" = coalesce("half_frozen", now()), 4.163 - "fully_frozen" = now(), 4.164 - "closed" = now(), 4.165 - "ranks_available" = TRUE 4.166 - WHERE "id" = "issue_id_p"; 4.167 - -- NOTE: The following DELETE statements have effect only when 4.168 - -- issue state has been manipulated 4.169 - DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p"; 4.170 - DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p"; 4.171 - DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 4.172 - END IF; 4.173 - RETURN; 4.174 - END; 4.175 - $$; 4.176 - 4.177 - 4.178 --- issue comments removed, voting comments integrated in "direct_voter" table 4.179 - 4.180 -ALTER TABLE "direct_voter" ADD COLUMN "comment_changed" TIMESTAMPTZ; 4.181 -ALTER TABLE "direct_voter" ADD COLUMN "formatting_engine" TEXT; 4.182 -ALTER TABLE "direct_voter" ADD COLUMN "comment" TEXT; 4.183 -ALTER TABLE "direct_voter" ADD COLUMN "text_search_data" TSVECTOR; 4.184 -CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data"); 4.185 -CREATE TRIGGER "update_text_search_data" 4.186 - BEFORE INSERT OR UPDATE ON "direct_voter" 4.187 - FOR EACH ROW EXECUTE PROCEDURE 4.188 - tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment"); 4.189 - 4.190 -COMMENT ON COLUMN "direct_voter"."comment_changed" IS 'Shall be set on comment change, to indicate a comment being modified after voting has been finished; Automatically set to NULL after voting phase; Automatically set to NULL by trigger, if "comment" is set to NULL'; 4.191 -COMMENT ON COLUMN "direct_voter"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "direct_voter"."comment"; Automatically set to NULL by trigger, if "comment" is set to NULL'; 4.192 -COMMENT ON COLUMN "direct_voter"."comment" IS 'Is to be set or updated by the frontend, if comment was inserted or updated AFTER the issue has been closed. Otherwise it shall be set to NULL.'; 4.193 - 4.194 -CREATE TABLE "rendered_voter_comment" ( 4.195 - PRIMARY KEY ("issue_id", "member_id", "format"), 4.196 - FOREIGN KEY ("issue_id", "member_id") 4.197 - REFERENCES "direct_voter" ("issue_id", "member_id") 4.198 - ON DELETE CASCADE ON UPDATE CASCADE, 4.199 - "issue_id" INT4, 4.200 - "member_id" INT4, 4.201 - "format" TEXT, 4.202 - "content" TEXT NOT NULL ); 4.203 - 4.204 -COMMENT ON TABLE "rendered_voter_comment" IS 'This table may be used by frontends to cache "rendered" voter comments (e.g. HTML output generated from wiki text)'; 4.205 - 4.206 -DROP TABLE "rendered_issue_comment"; 4.207 -DROP TABLE "issue_comment"; 4.208 -DROP TABLE "rendered_voting_comment"; 4.209 -DROP TABLE "voting_comment"; 4.210 - 4.211 -CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"() 4.212 - RETURNS TRIGGER 4.213 - LANGUAGE 'plpgsql' VOLATILE AS $$ 4.214 - BEGIN 4.215 - IF NEW."comment" ISNULL THEN 4.216 - NEW."comment_changed" := NULL; 4.217 - NEW."formatting_engine" := NULL; 4.218 - END IF; 4.219 - RETURN NEW; 4.220 - END; 4.221 - $$; 4.222 - 4.223 -CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set" 4.224 - BEFORE INSERT OR UPDATE ON "direct_voter" 4.225 - FOR EACH ROW EXECUTE PROCEDURE 4.226 - "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"(); 4.227 - 4.228 -COMMENT ON FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"() IS 'Implementation of trigger "voter_comment_fields_only_set_when_voter_comment_is_set" ON table "direct_voter"'; 4.229 -COMMENT ON TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set" ON "direct_voter" IS 'If "comment" is set to NULL, then other comment related fields are also set to NULL.'; 4.230 - 4.231 -CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"() 4.232 - RETURNS TRIGGER 4.233 - LANGUAGE 'plpgsql' VOLATILE AS $$ 4.234 - DECLARE 4.235 - "issue_id_v" "issue"."id"%TYPE; 4.236 - "issue_row" "issue"%ROWTYPE; 4.237 - BEGIN 4.238 - IF TG_RELID = 'direct_voter'::regclass AND TG_OP = 'UPDATE' THEN 4.239 - IF 4.240 - OLD."issue_id" = NEW."issue_id" AND 4.241 - OLD."member_id" = NEW."member_id" AND 4.242 - OLD."weight" = NEW."weight" 4.243 - THEN 4.244 - RETURN NULL; -- allows changing of voter comment 4.245 - END IF; 4.246 - END IF; 4.247 - IF TG_OP = 'DELETE' THEN 4.248 - "issue_id_v" := OLD."issue_id"; 4.249 - ELSE 4.250 - "issue_id_v" := NEW."issue_id"; 4.251 - END IF; 4.252 - SELECT INTO "issue_row" * FROM "issue" 4.253 - WHERE "id" = "issue_id_v" FOR SHARE; 4.254 - IF "issue_row"."closed" NOTNULL THEN 4.255 - RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.'; 4.256 - END IF; 4.257 - RETURN NULL; 4.258 - END; 4.259 - $$; 4.260 - 4.261 -CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) 4.262 - RETURNS VOID 4.263 - LANGUAGE 'plpgsql' VOLATILE AS $$ 4.264 - DECLARE 4.265 - "area_id_v" "area"."id"%TYPE; 4.266 - "unit_id_v" "unit"."id"%TYPE; 4.267 - "member_id_v" "member"."id"%TYPE; 4.268 - BEGIN 4.269 - PERFORM "lock_issue"("issue_id_p"); 4.270 - SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 4.271 - SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 4.272 - -- delete timestamp of voting comment: 4.273 - UPDATE "direct_voter" SET "comment_changed" = NULL 4.274 - WHERE "issue_id" = "issue_id_p"; 4.275 - -- delete delegating votes (in cases of manual reset of issue state): 4.276 - DELETE FROM "delegating_voter" 4.277 - WHERE "issue_id" = "issue_id_p"; 4.278 - -- delete votes from non-privileged voters: 4.279 - DELETE FROM "direct_voter" 4.280 - USING ( 4.281 - SELECT 4.282 - "direct_voter"."member_id" 4.283 - FROM "direct_voter" 4.284 - JOIN "member" ON "direct_voter"."member_id" = "member"."id" 4.285 - LEFT JOIN "privilege" 4.286 - ON "privilege"."unit_id" = "unit_id_v" 4.287 - AND "privilege"."member_id" = "direct_voter"."member_id" 4.288 - WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( 4.289 - "member"."active" = FALSE OR 4.290 - "privilege"."voting_right" ISNULL OR 4.291 - "privilege"."voting_right" = FALSE 4.292 - ) 4.293 - ) AS "subquery" 4.294 - WHERE "direct_voter"."issue_id" = "issue_id_p" 4.295 - AND "direct_voter"."member_id" = "subquery"."member_id"; 4.296 - -- consider delegations: 4.297 - UPDATE "direct_voter" SET "weight" = 1 4.298 - WHERE "issue_id" = "issue_id_p"; 4.299 - PERFORM "add_vote_delegations"("issue_id_p"); 4.300 - -- set voter count and mark issue as being calculated: 4.301 - UPDATE "issue" SET 4.302 - "state" = 'calculation', 4.303 - "closed" = now(), 4.304 - "voter_count" = ( 4.305 - SELECT coalesce(sum("weight"), 0) 4.306 - FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 4.307 - ) 4.308 - WHERE "id" = "issue_id_p"; 4.309 - -- materialize battle_view: 4.310 - -- NOTE: "closed" column of issue must be set at this point 4.311 - DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 4.312 - INSERT INTO "battle" ( 4.313 - "issue_id", 4.314 - "winning_initiative_id", "losing_initiative_id", 4.315 - "count" 4.316 - ) SELECT 4.317 - "issue_id", 4.318 - "winning_initiative_id", "losing_initiative_id", 4.319 - "count" 4.320 - FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 4.321 - -- copy "positive_votes" and "negative_votes" from "battle" table: 4.322 - UPDATE "initiative" SET 4.323 - "positive_votes" = "battle_win"."count", 4.324 - "negative_votes" = "battle_lose"."count" 4.325 - FROM "battle" AS "battle_win", "battle" AS "battle_lose" 4.326 - WHERE 4.327 - "battle_win"."issue_id" = "issue_id_p" AND 4.328 - "battle_win"."winning_initiative_id" = "initiative"."id" AND 4.329 - "battle_win"."losing_initiative_id" ISNULL AND 4.330 - "battle_lose"."issue_id" = "issue_id_p" AND 4.331 - "battle_lose"."losing_initiative_id" = "initiative"."id" AND 4.332 - "battle_lose"."winning_initiative_id" ISNULL; 4.333 - END; 4.334 - $$; 4.335 - 4.336 -CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE) 4.337 - RETURNS VOID 4.338 - LANGUAGE 'plpgsql' VOLATILE AS $$ 4.339 - DECLARE 4.340 - "issue_row" "issue"%ROWTYPE; 4.341 - BEGIN 4.342 - SELECT * INTO "issue_row" 4.343 - FROM "issue" WHERE "id" = "issue_id_p" 4.344 - FOR UPDATE; 4.345 - IF "issue_row"."cleaned" ISNULL THEN 4.346 - UPDATE "issue" SET 4.347 - "state" = 'voting', 4.348 - "closed" = NULL, 4.349 - "ranks_available" = FALSE 4.350 - WHERE "id" = "issue_id_p"; 4.351 - DELETE FROM "delegating_voter" 4.352 - WHERE "issue_id" = "issue_id_p"; 4.353 - DELETE FROM "direct_voter" 4.354 - WHERE "issue_id" = "issue_id_p"; 4.355 - DELETE FROM "delegating_interest_snapshot" 4.356 - WHERE "issue_id" = "issue_id_p"; 4.357 - DELETE FROM "direct_interest_snapshot" 4.358 - WHERE "issue_id" = "issue_id_p"; 4.359 - DELETE FROM "delegating_population_snapshot" 4.360 - WHERE "issue_id" = "issue_id_p"; 4.361 - DELETE FROM "direct_population_snapshot" 4.362 - WHERE "issue_id" = "issue_id_p"; 4.363 - DELETE FROM "non_voter" 4.364 - WHERE "issue_id" = "issue_id_p"; 4.365 - DELETE FROM "delegation" 4.366 - WHERE "issue_id" = "issue_id_p"; 4.367 - DELETE FROM "supporter" 4.368 - WHERE "issue_id" = "issue_id_p"; 4.369 - UPDATE "issue" SET 4.370 - "state" = "issue_row"."state", 4.371 - "closed" = "issue_row"."closed", 4.372 - "ranks_available" = "issue_row"."ranks_available", 4.373 - "cleaned" = now() 4.374 - WHERE "id" = "issue_id_p"; 4.375 - END IF; 4.376 - RETURN; 4.377 - END; 4.378 - $$; 4.379 - 4.380 - 4.381 --- "non_voter" deletes "direct_voter" and vice versa 4.382 - 4.383 -CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"() 4.384 - RETURNS TRIGGER 4.385 - LANGUAGE 'plpgsql' VOLATILE AS $$ 4.386 - BEGIN 4.387 - DELETE FROM "direct_voter" 4.388 - WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id"; 4.389 - RETURN NULL; 4.390 - END; 4.391 - $$; 4.392 - 4.393 -CREATE TRIGGER "non_voter_deletes_direct_voter" 4.394 - AFTER INSERT OR UPDATE ON "non_voter" 4.395 - FOR EACH ROW EXECUTE PROCEDURE 4.396 - "non_voter_deletes_direct_voter_trigger"(); 4.397 - 4.398 -COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"'; 4.399 -COMMENT ON TRIGGER "non_voter_deletes_direct_voter" ON "non_voter" IS 'An entry in the "non_voter" table deletes an entry in the "direct_voter" table (and vice versa due to trigger "direct_voter_deletes_non_voter" on table "direct_voter")'; 4.400 - 4.401 -CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"() 4.402 - RETURNS TRIGGER 4.403 - LANGUAGE 'plpgsql' VOLATILE AS $$ 4.404 - BEGIN 4.405 - DELETE FROM "non_voter" 4.406 - WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id"; 4.407 - RETURN NULL; 4.408 - END; 4.409 - $$; 4.410 - 4.411 -CREATE TRIGGER "direct_voter_deletes_non_voter" 4.412 - AFTER INSERT OR UPDATE ON "direct_voter" 4.413 - FOR EACH ROW EXECUTE PROCEDURE 4.414 - "direct_voter_deletes_non_voter_trigger"(); 4.415 - 4.416 -COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"'; 4.417 -COMMENT ON TRIGGER "direct_voter_deletes_non_voter" ON "direct_voter" IS 'An entry in the "direct_voter" table deletes an entry in the "non_voter" table (and vice versa due to trigger "non_voter_deletes_direct_voter" on table "non_voter")'; 4.418 - 4.419 - 4.420 --- different locking levels and different locking order to avoid deadlocks 4.421 - 4.422 -CREATE OR REPLACE FUNCTION "lock_issue" 4.423 - ( "issue_id_p" "issue"."id"%TYPE ) 4.424 - RETURNS VOID 4.425 - LANGUAGE 'plpgsql' VOLATILE AS $$ 4.426 - BEGIN 4.427 - -- The following locking order is used: 4.428 - -- 1st) row-level lock on the issue 4.429 - -- 2nd) table-level locks in order of occurrence in the core.sql file 4.430 - PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE; 4.431 - -- NOTE: The row-level exclusive lock in combination with the 4.432 - -- share_row_lock_issue(_via_initiative)_trigger functions (which 4.433 - -- acquire a row-level share lock on the issue) ensure that no data 4.434 - -- is changed, which could affect calculation of snapshots or 4.435 - -- counting of votes. Table "delegation" must be table-level-locked, 4.436 - -- as it also contains issue- and global-scope delegations. 4.437 - PERFORM NULL FROM "member" WHERE "active" FOR SHARE; 4.438 - -- NOTE: As we later cause implicit row-level share locks on many 4.439 - -- active members, we lock them before locking any other table 4.440 - -- to avoid deadlocks 4.441 - LOCK TABLE "member" IN SHARE MODE; 4.442 - LOCK TABLE "privilege" IN SHARE MODE; 4.443 - LOCK TABLE "membership" IN SHARE MODE; 4.444 - LOCK TABLE "policy" IN SHARE MODE; 4.445 - LOCK TABLE "delegation" IN SHARE MODE; 4.446 - LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE; 4.447 - LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE; 4.448 - LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE; 4.449 - LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE; 4.450 - LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE; 4.451 - RETURN; 4.452 - END; 4.453 - $$; 4.454 - 4.455 - 4.456 --- new comment on function "delete_private_data"() 4.457 - 4.458 -COMMENT ON FUNCTION "delete_private_data"() IS 'Used by lf_export script. DO NOT USE on productive database, but only on a copy! This function deletes all data which should not be publicly available, and can be used to create a database dump for publication. See source code to see which data is deleted. If you need a different behaviour, copy this function and modify lf_export accordingly, to avoid data-leaks after updating.'; 4.459 - 4.460 - 4.461 -COMMIT;
5.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 5.2 +++ b/update/core-update.v2.0.12-v2.1.0.sql Fri Oct 05 13:45:37 2012 +0200 5.3 @@ -0,0 +1,458 @@ 5.4 +BEGIN; 5.5 + 5.6 + 5.7 +-- update version number 5.8 + 5.9 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 5.10 + SELECT * FROM (VALUES ('2.1.0', 2, 1, 0)) 5.11 + AS "subquery"("string", "major", "minor", "revision"); 5.12 + 5.13 + 5.14 +-- old API tables are now deprecated 5.15 + 5.16 +COMMENT ON TYPE "application_access_level" IS 'DEPRECATED, WILL BE REMOVED! Access privileges for applications using the API'; 5.17 +COMMENT ON TABLE "member_application" IS 'DEPRECATED, WILL BE REMOVED! Registered application being allowed to use the API'; 5.18 + 5.19 + 5.20 +-- new polling mode and changed privileges 5.21 + 5.22 +ALTER TABLE "policy" ADD COLUMN "polling" BOOLEAN NOT NULL DEFAULT FALSE; 5.23 +ALTER TABLE "policy" ALTER COLUMN "admission_time" DROP NOT NULL; 5.24 +ALTER TABLE "policy" ALTER COLUMN "discussion_time" DROP NOT NULL; 5.25 +ALTER TABLE "policy" ALTER COLUMN "verification_time" DROP NOT NULL; 5.26 +ALTER TABLE "policy" ALTER COLUMN "voting_time" DROP NOT NULL; 5.27 +ALTER TABLE "policy" ALTER COLUMN "issue_quorum_num" DROP NOT NULL; 5.28 +ALTER TABLE "policy" ALTER COLUMN "issue_quorum_den" DROP NOT NULL; 5.29 +ALTER TABLE "policy" ADD CONSTRAINT "timing" CHECK ( 5.30 + ( "polling" = FALSE AND 5.31 + "admission_time" NOTNULL AND "discussion_time" NOTNULL AND 5.32 + "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR 5.33 + ( "polling" = TRUE AND 5.34 + "admission_time" ISNULL AND "discussion_time" NOTNULL AND 5.35 + "verification_time" NOTNULL AND "voting_time" NOTNULL ) OR 5.36 + ( "polling" = TRUE AND 5.37 + "admission_time" ISNULL AND "discussion_time" ISNULL AND 5.38 + "verification_time" ISNULL AND "voting_time" ISNULL ) ); 5.39 +ALTER TABLE "policy" ADD CONSTRAINT "issue_quorum_if_and_only_if_not_polling" CHECK ( 5.40 + "polling" = "issue_quorum_num" ISNULL AND 5.41 + "polling" = "issue_quorum_den" ISNULL ); 5.42 +COMMENT ON COLUMN "policy"."polling" IS 'TRUE = special policy for non-user-generated issues without issue quorum, where certain initiatives (those having the "polling" flag set) do not need to pass the initiative quorum; "admission_time" MUST be set to NULL, the other timings may be set to NULL altogether, allowing individual timing for those issues'; 5.43 + 5.44 +ALTER TABLE "issue" ALTER COLUMN "admission_time" DROP NOT NULL; 5.45 +ALTER TABLE "issue" ADD CONSTRAINT "admission_time_not_null_unless_instantly_accepted" CHECK ( 5.46 + "admission_time" NOTNULL OR ("accepted" NOTNULL AND "accepted" = "created") ); 5.47 + 5.48 +ALTER TABLE "initiative" ADD COLUMN "polling" BOOLEAN NOT NULL DEFAULT FALSE; 5.49 +COMMENT ON COLUMN "initiative"."polling" IS 'Initiative does not need to pass the initiative quorum (see "policy"."polling")'; 5.50 + 5.51 +ALTER TABLE "privilege" RENAME COLUMN "voting_right_manager" TO "member_manager"; 5.52 +ALTER TABLE "privilege" ADD COLUMN "initiative_right" BOOLEAN NOT NULL DEFAULT TRUE; 5.53 +ALTER TABLE "privilege" ADD COLUMN "polling_right" BOOLEAN NOT NULL DEFAULT FALSE; 5.54 +UPDATE "privilege" SET "initiative_right" = "voting_right"; 5.55 +COMMENT ON COLUMN "privilege"."admin_manager" IS 'Grant/revoke any privileges to/from other members'; 5.56 +COMMENT ON COLUMN "privilege"."member_manager" IS 'Adding/removing members from the unit, granting or revoking "initiative_right" and "voting_right"'; 5.57 +COMMENT ON COLUMN "privilege"."initiative_right" IS 'Right to create an initiative'; 5.58 +COMMENT ON COLUMN "privilege"."voting_right" IS 'Right to support initiatives, create and rate suggestions, and to vote'; 5.59 +COMMENT ON COLUMN "privilege"."polling_right" IS 'Right to create issues with policies having the "policy"."polling" flag set, and to add initiatives having the "initiative"."polling" flag set to those issues'; 5.60 + 5.61 +DROP VIEW "member_contingent_left"; 5.62 +DROP VIEW "member_contingent"; 5.63 +ALTER TABLE "contingent" DROP CONSTRAINT "contingent_pkey"; 5.64 +ALTER TABLE "contingent" ALTER COLUMN "time_frame" DROP NOT NULL; 5.65 +ALTER TABLE "contingent" ADD COLUMN "polling" BOOLEAN DEFAULT FALSE; 5.66 +ALTER TABLE "contingent" ADD PRIMARY KEY ("polling", "time_frame"); 5.67 +ALTER TABLE "contingent" ALTER COLUMN "polling" DROP DEFAULT; 5.68 +COMMENT ON COLUMN "contingent"."polling" IS 'Determines if settings are for creating initiatives and new drafts of initiatives with "polling" flag set'; 5.69 + 5.70 +CREATE VIEW "member_contingent" AS 5.71 + SELECT 5.72 + "member"."id" AS "member_id", 5.73 + "contingent"."polling", 5.74 + "contingent"."time_frame", 5.75 + CASE WHEN "contingent"."text_entry_limit" NOTNULL THEN 5.76 + ( 5.77 + SELECT count(1) FROM "draft" 5.78 + JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id" 5.79 + WHERE "draft"."author_id" = "member"."id" 5.80 + AND "initiative"."polling" = "contingent"."polling" 5.81 + AND "draft"."created" > now() - "contingent"."time_frame" 5.82 + ) + ( 5.83 + SELECT count(1) FROM "suggestion" 5.84 + JOIN "initiative" ON "initiative"."id" = "suggestion"."initiative_id" 5.85 + WHERE "suggestion"."author_id" = "member"."id" 5.86 + AND "contingent"."polling" = FALSE 5.87 + AND "suggestion"."created" > now() - "contingent"."time_frame" 5.88 + ) 5.89 + ELSE NULL END AS "text_entry_count", 5.90 + "contingent"."text_entry_limit", 5.91 + CASE WHEN "contingent"."initiative_limit" NOTNULL THEN ( 5.92 + SELECT count(1) FROM "opening_draft" AS "draft" 5.93 + JOIN "initiative" ON "initiative"."id" = "draft"."initiative_id" 5.94 + WHERE "draft"."author_id" = "member"."id" 5.95 + AND "initiative"."polling" = "contingent"."polling" 5.96 + AND "draft"."created" > now() - "contingent"."time_frame" 5.97 + ) ELSE NULL END AS "initiative_count", 5.98 + "contingent"."initiative_limit" 5.99 + FROM "member" CROSS JOIN "contingent"; 5.100 + 5.101 +COMMENT ON VIEW "member_contingent" IS 'Actual counts of text entries and initiatives are calculated per member for each limit in the "contingent" table.'; 5.102 + 5.103 +COMMENT ON COLUMN "member_contingent"."text_entry_count" IS 'Only calculated when "text_entry_limit" is not null in the same row'; 5.104 +COMMENT ON COLUMN "member_contingent"."initiative_count" IS 'Only calculated when "initiative_limit" is not null in the same row'; 5.105 + 5.106 +CREATE VIEW "member_contingent_left" AS 5.107 + SELECT 5.108 + "member_id", 5.109 + "polling", 5.110 + max("text_entry_limit" - "text_entry_count") AS "text_entries_left", 5.111 + max("initiative_limit" - "initiative_count") AS "initiatives_left" 5.112 + FROM "member_contingent" GROUP BY "member_id", "polling"; 5.113 + 5.114 +COMMENT ON VIEW "member_contingent_left" IS 'Amount of text entries or initiatives which can be posted now instantly by a member. This view should be used by a frontend to determine, if the contingent for posting is exhausted.'; 5.115 + 5.116 +CREATE OR REPLACE FUNCTION "freeze_after_snapshot" 5.117 + ( "issue_id_p" "issue"."id"%TYPE ) 5.118 + RETURNS VOID 5.119 + LANGUAGE 'plpgsql' VOLATILE AS $$ 5.120 + DECLARE 5.121 + "issue_row" "issue"%ROWTYPE; 5.122 + "policy_row" "policy"%ROWTYPE; 5.123 + "initiative_row" "initiative"%ROWTYPE; 5.124 + BEGIN 5.125 + SELECT * INTO "issue_row" FROM "issue" WHERE "id" = "issue_id_p"; 5.126 + SELECT * INTO "policy_row" 5.127 + FROM "policy" WHERE "id" = "issue_row"."policy_id"; 5.128 + PERFORM "set_snapshot_event"("issue_id_p", 'full_freeze'); 5.129 + FOR "initiative_row" IN 5.130 + SELECT * FROM "initiative" 5.131 + WHERE "issue_id" = "issue_id_p" AND "revoked" ISNULL 5.132 + LOOP 5.133 + IF 5.134 + "initiative_row"."polling" OR ( 5.135 + "initiative_row"."satisfied_supporter_count" > 0 AND 5.136 + "initiative_row"."satisfied_supporter_count" * 5.137 + "policy_row"."initiative_quorum_den" >= 5.138 + "issue_row"."population" * "policy_row"."initiative_quorum_num" 5.139 + ) 5.140 + THEN 5.141 + UPDATE "initiative" SET "admitted" = TRUE 5.142 + WHERE "id" = "initiative_row"."id"; 5.143 + ELSE 5.144 + UPDATE "initiative" SET "admitted" = FALSE 5.145 + WHERE "id" = "initiative_row"."id"; 5.146 + END IF; 5.147 + END LOOP; 5.148 + IF EXISTS ( 5.149 + SELECT NULL FROM "initiative" 5.150 + WHERE "issue_id" = "issue_id_p" AND "admitted" = TRUE 5.151 + ) THEN 5.152 + UPDATE "issue" SET 5.153 + "state" = 'voting', 5.154 + "accepted" = coalesce("accepted", now()), 5.155 + "half_frozen" = coalesce("half_frozen", now()), 5.156 + "fully_frozen" = now() 5.157 + WHERE "id" = "issue_id_p"; 5.158 + ELSE 5.159 + UPDATE "issue" SET 5.160 + "state" = 'canceled_no_initiative_admitted', 5.161 + "accepted" = coalesce("accepted", now()), 5.162 + "half_frozen" = coalesce("half_frozen", now()), 5.163 + "fully_frozen" = now(), 5.164 + "closed" = now(), 5.165 + "ranks_available" = TRUE 5.166 + WHERE "id" = "issue_id_p"; 5.167 + -- NOTE: The following DELETE statements have effect only when 5.168 + -- issue state has been manipulated 5.169 + DELETE FROM "direct_voter" WHERE "issue_id" = "issue_id_p"; 5.170 + DELETE FROM "delegating_voter" WHERE "issue_id" = "issue_id_p"; 5.171 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 5.172 + END IF; 5.173 + RETURN; 5.174 + END; 5.175 + $$; 5.176 + 5.177 + 5.178 +-- issue comments removed, voting comments integrated in "direct_voter" table 5.179 + 5.180 +ALTER TABLE "direct_voter" ADD COLUMN "comment_changed" TIMESTAMPTZ; 5.181 +ALTER TABLE "direct_voter" ADD COLUMN "formatting_engine" TEXT; 5.182 +ALTER TABLE "direct_voter" ADD COLUMN "comment" TEXT; 5.183 +ALTER TABLE "direct_voter" ADD COLUMN "text_search_data" TSVECTOR; 5.184 +CREATE INDEX "direct_voter_text_search_data_idx" ON "direct_voter" USING gin ("text_search_data"); 5.185 +CREATE TRIGGER "update_text_search_data" 5.186 + BEFORE INSERT OR UPDATE ON "direct_voter" 5.187 + FOR EACH ROW EXECUTE PROCEDURE 5.188 + tsvector_update_trigger('text_search_data', 'pg_catalog.simple', "comment"); 5.189 + 5.190 +COMMENT ON COLUMN "direct_voter"."comment_changed" IS 'Shall be set on comment change, to indicate a comment being modified after voting has been finished; Automatically set to NULL after voting phase; Automatically set to NULL by trigger, if "comment" is set to NULL'; 5.191 +COMMENT ON COLUMN "direct_voter"."formatting_engine" IS 'Allows different formatting engines (i.e. wiki formats) to be used for "direct_voter"."comment"; Automatically set to NULL by trigger, if "comment" is set to NULL'; 5.192 +COMMENT ON COLUMN "direct_voter"."comment" IS 'Is to be set or updated by the frontend, if comment was inserted or updated AFTER the issue has been closed. Otherwise it shall be set to NULL.'; 5.193 + 5.194 +CREATE TABLE "rendered_voter_comment" ( 5.195 + PRIMARY KEY ("issue_id", "member_id", "format"), 5.196 + FOREIGN KEY ("issue_id", "member_id") 5.197 + REFERENCES "direct_voter" ("issue_id", "member_id") 5.198 + ON DELETE CASCADE ON UPDATE CASCADE, 5.199 + "issue_id" INT4, 5.200 + "member_id" INT4, 5.201 + "format" TEXT, 5.202 + "content" TEXT NOT NULL ); 5.203 + 5.204 +COMMENT ON TABLE "rendered_voter_comment" IS 'This table may be used by frontends to cache "rendered" voter comments (e.g. HTML output generated from wiki text)'; 5.205 + 5.206 +DROP TABLE "rendered_issue_comment"; 5.207 +DROP TABLE "issue_comment"; 5.208 +DROP TABLE "rendered_voting_comment"; 5.209 +DROP TABLE "voting_comment"; 5.210 + 5.211 +CREATE FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"() 5.212 + RETURNS TRIGGER 5.213 + LANGUAGE 'plpgsql' VOLATILE AS $$ 5.214 + BEGIN 5.215 + IF NEW."comment" ISNULL THEN 5.216 + NEW."comment_changed" := NULL; 5.217 + NEW."formatting_engine" := NULL; 5.218 + END IF; 5.219 + RETURN NEW; 5.220 + END; 5.221 + $$; 5.222 + 5.223 +CREATE TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set" 5.224 + BEFORE INSERT OR UPDATE ON "direct_voter" 5.225 + FOR EACH ROW EXECUTE PROCEDURE 5.226 + "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"(); 5.227 + 5.228 +COMMENT ON FUNCTION "voter_comment_fields_only_set_when_voter_comment_is_set_trigger"() IS 'Implementation of trigger "voter_comment_fields_only_set_when_voter_comment_is_set" ON table "direct_voter"'; 5.229 +COMMENT ON TRIGGER "voter_comment_fields_only_set_when_voter_comment_is_set" ON "direct_voter" IS 'If "comment" is set to NULL, then other comment related fields are also set to NULL.'; 5.230 + 5.231 +CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"() 5.232 + RETURNS TRIGGER 5.233 + LANGUAGE 'plpgsql' VOLATILE AS $$ 5.234 + DECLARE 5.235 + "issue_id_v" "issue"."id"%TYPE; 5.236 + "issue_row" "issue"%ROWTYPE; 5.237 + BEGIN 5.238 + IF TG_RELID = 'direct_voter'::regclass AND TG_OP = 'UPDATE' THEN 5.239 + IF 5.240 + OLD."issue_id" = NEW."issue_id" AND 5.241 + OLD."member_id" = NEW."member_id" AND 5.242 + OLD."weight" = NEW."weight" 5.243 + THEN 5.244 + RETURN NULL; -- allows changing of voter comment 5.245 + END IF; 5.246 + END IF; 5.247 + IF TG_OP = 'DELETE' THEN 5.248 + "issue_id_v" := OLD."issue_id"; 5.249 + ELSE 5.250 + "issue_id_v" := NEW."issue_id"; 5.251 + END IF; 5.252 + SELECT INTO "issue_row" * FROM "issue" 5.253 + WHERE "id" = "issue_id_v" FOR SHARE; 5.254 + IF "issue_row"."closed" NOTNULL THEN 5.255 + RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.'; 5.256 + END IF; 5.257 + RETURN NULL; 5.258 + END; 5.259 + $$; 5.260 + 5.261 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) 5.262 + RETURNS VOID 5.263 + LANGUAGE 'plpgsql' VOLATILE AS $$ 5.264 + DECLARE 5.265 + "area_id_v" "area"."id"%TYPE; 5.266 + "unit_id_v" "unit"."id"%TYPE; 5.267 + "member_id_v" "member"."id"%TYPE; 5.268 + BEGIN 5.269 + PERFORM "lock_issue"("issue_id_p"); 5.270 + SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 5.271 + SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 5.272 + -- delete timestamp of voting comment: 5.273 + UPDATE "direct_voter" SET "comment_changed" = NULL 5.274 + WHERE "issue_id" = "issue_id_p"; 5.275 + -- delete delegating votes (in cases of manual reset of issue state): 5.276 + DELETE FROM "delegating_voter" 5.277 + WHERE "issue_id" = "issue_id_p"; 5.278 + -- delete votes from non-privileged voters: 5.279 + DELETE FROM "direct_voter" 5.280 + USING ( 5.281 + SELECT 5.282 + "direct_voter"."member_id" 5.283 + FROM "direct_voter" 5.284 + JOIN "member" ON "direct_voter"."member_id" = "member"."id" 5.285 + LEFT JOIN "privilege" 5.286 + ON "privilege"."unit_id" = "unit_id_v" 5.287 + AND "privilege"."member_id" = "direct_voter"."member_id" 5.288 + WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( 5.289 + "member"."active" = FALSE OR 5.290 + "privilege"."voting_right" ISNULL OR 5.291 + "privilege"."voting_right" = FALSE 5.292 + ) 5.293 + ) AS "subquery" 5.294 + WHERE "direct_voter"."issue_id" = "issue_id_p" 5.295 + AND "direct_voter"."member_id" = "subquery"."member_id"; 5.296 + -- consider delegations: 5.297 + UPDATE "direct_voter" SET "weight" = 1 5.298 + WHERE "issue_id" = "issue_id_p"; 5.299 + PERFORM "add_vote_delegations"("issue_id_p"); 5.300 + -- set voter count and mark issue as being calculated: 5.301 + UPDATE "issue" SET 5.302 + "state" = 'calculation', 5.303 + "closed" = now(), 5.304 + "voter_count" = ( 5.305 + SELECT coalesce(sum("weight"), 0) 5.306 + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 5.307 + ) 5.308 + WHERE "id" = "issue_id_p"; 5.309 + -- materialize battle_view: 5.310 + -- NOTE: "closed" column of issue must be set at this point 5.311 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 5.312 + INSERT INTO "battle" ( 5.313 + "issue_id", 5.314 + "winning_initiative_id", "losing_initiative_id", 5.315 + "count" 5.316 + ) SELECT 5.317 + "issue_id", 5.318 + "winning_initiative_id", "losing_initiative_id", 5.319 + "count" 5.320 + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 5.321 + -- copy "positive_votes" and "negative_votes" from "battle" table: 5.322 + UPDATE "initiative" SET 5.323 + "positive_votes" = "battle_win"."count", 5.324 + "negative_votes" = "battle_lose"."count" 5.325 + FROM "battle" AS "battle_win", "battle" AS "battle_lose" 5.326 + WHERE 5.327 + "battle_win"."issue_id" = "issue_id_p" AND 5.328 + "battle_win"."winning_initiative_id" = "initiative"."id" AND 5.329 + "battle_win"."losing_initiative_id" ISNULL AND 5.330 + "battle_lose"."issue_id" = "issue_id_p" AND 5.331 + "battle_lose"."losing_initiative_id" = "initiative"."id" AND 5.332 + "battle_lose"."winning_initiative_id" ISNULL; 5.333 + END; 5.334 + $$; 5.335 + 5.336 +CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE) 5.337 + RETURNS VOID 5.338 + LANGUAGE 'plpgsql' VOLATILE AS $$ 5.339 + DECLARE 5.340 + "issue_row" "issue"%ROWTYPE; 5.341 + BEGIN 5.342 + SELECT * INTO "issue_row" 5.343 + FROM "issue" WHERE "id" = "issue_id_p" 5.344 + FOR UPDATE; 5.345 + IF "issue_row"."cleaned" ISNULL THEN 5.346 + UPDATE "issue" SET 5.347 + "state" = 'voting', 5.348 + "closed" = NULL, 5.349 + "ranks_available" = FALSE 5.350 + WHERE "id" = "issue_id_p"; 5.351 + DELETE FROM "delegating_voter" 5.352 + WHERE "issue_id" = "issue_id_p"; 5.353 + DELETE FROM "direct_voter" 5.354 + WHERE "issue_id" = "issue_id_p"; 5.355 + DELETE FROM "delegating_interest_snapshot" 5.356 + WHERE "issue_id" = "issue_id_p"; 5.357 + DELETE FROM "direct_interest_snapshot" 5.358 + WHERE "issue_id" = "issue_id_p"; 5.359 + DELETE FROM "delegating_population_snapshot" 5.360 + WHERE "issue_id" = "issue_id_p"; 5.361 + DELETE FROM "direct_population_snapshot" 5.362 + WHERE "issue_id" = "issue_id_p"; 5.363 + DELETE FROM "non_voter" 5.364 + WHERE "issue_id" = "issue_id_p"; 5.365 + DELETE FROM "delegation" 5.366 + WHERE "issue_id" = "issue_id_p"; 5.367 + DELETE FROM "supporter" 5.368 + WHERE "issue_id" = "issue_id_p"; 5.369 + UPDATE "issue" SET 5.370 + "state" = "issue_row"."state", 5.371 + "closed" = "issue_row"."closed", 5.372 + "ranks_available" = "issue_row"."ranks_available", 5.373 + "cleaned" = now() 5.374 + WHERE "id" = "issue_id_p"; 5.375 + END IF; 5.376 + RETURN; 5.377 + END; 5.378 + $$; 5.379 + 5.380 + 5.381 +-- "non_voter" deletes "direct_voter" and vice versa 5.382 + 5.383 +CREATE FUNCTION "non_voter_deletes_direct_voter_trigger"() 5.384 + RETURNS TRIGGER 5.385 + LANGUAGE 'plpgsql' VOLATILE AS $$ 5.386 + BEGIN 5.387 + DELETE FROM "direct_voter" 5.388 + WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id"; 5.389 + RETURN NULL; 5.390 + END; 5.391 + $$; 5.392 + 5.393 +CREATE TRIGGER "non_voter_deletes_direct_voter" 5.394 + AFTER INSERT OR UPDATE ON "non_voter" 5.395 + FOR EACH ROW EXECUTE PROCEDURE 5.396 + "non_voter_deletes_direct_voter_trigger"(); 5.397 + 5.398 +COMMENT ON FUNCTION "non_voter_deletes_direct_voter_trigger"() IS 'Implementation of trigger "non_voter_deletes_direct_voter" on table "non_voter"'; 5.399 +COMMENT ON TRIGGER "non_voter_deletes_direct_voter" ON "non_voter" IS 'An entry in the "non_voter" table deletes an entry in the "direct_voter" table (and vice versa due to trigger "direct_voter_deletes_non_voter" on table "direct_voter")'; 5.400 + 5.401 +CREATE FUNCTION "direct_voter_deletes_non_voter_trigger"() 5.402 + RETURNS TRIGGER 5.403 + LANGUAGE 'plpgsql' VOLATILE AS $$ 5.404 + BEGIN 5.405 + DELETE FROM "non_voter" 5.406 + WHERE "issue_id" = NEW."issue_id" AND "member_id" = NEW."member_id"; 5.407 + RETURN NULL; 5.408 + END; 5.409 + $$; 5.410 + 5.411 +CREATE TRIGGER "direct_voter_deletes_non_voter" 5.412 + AFTER INSERT OR UPDATE ON "direct_voter" 5.413 + FOR EACH ROW EXECUTE PROCEDURE 5.414 + "direct_voter_deletes_non_voter_trigger"(); 5.415 + 5.416 +COMMENT ON FUNCTION "direct_voter_deletes_non_voter_trigger"() IS 'Implementation of trigger "direct_voter_deletes_non_voter" on table "direct_voter"'; 5.417 +COMMENT ON TRIGGER "direct_voter_deletes_non_voter" ON "direct_voter" IS 'An entry in the "direct_voter" table deletes an entry in the "non_voter" table (and vice versa due to trigger "non_voter_deletes_direct_voter" on table "non_voter")'; 5.418 + 5.419 + 5.420 +-- different locking levels and different locking order to avoid deadlocks 5.421 + 5.422 +CREATE OR REPLACE FUNCTION "lock_issue" 5.423 + ( "issue_id_p" "issue"."id"%TYPE ) 5.424 + RETURNS VOID 5.425 + LANGUAGE 'plpgsql' VOLATILE AS $$ 5.426 + BEGIN 5.427 + -- The following locking order is used: 5.428 + -- 1st) row-level lock on the issue 5.429 + -- 2nd) table-level locks in order of occurrence in the core.sql file 5.430 + PERFORM NULL FROM "issue" WHERE "id" = "issue_id_p" FOR UPDATE; 5.431 + -- NOTE: The row-level exclusive lock in combination with the 5.432 + -- share_row_lock_issue(_via_initiative)_trigger functions (which 5.433 + -- acquire a row-level share lock on the issue) ensure that no data 5.434 + -- is changed, which could affect calculation of snapshots or 5.435 + -- counting of votes. Table "delegation" must be table-level-locked, 5.436 + -- as it also contains issue- and global-scope delegations. 5.437 + PERFORM NULL FROM "member" WHERE "active" FOR SHARE; 5.438 + -- NOTE: As we later cause implicit row-level share locks on many 5.439 + -- active members, we lock them before locking any other table 5.440 + -- to avoid deadlocks 5.441 + LOCK TABLE "member" IN SHARE MODE; 5.442 + LOCK TABLE "privilege" IN SHARE MODE; 5.443 + LOCK TABLE "membership" IN SHARE MODE; 5.444 + LOCK TABLE "policy" IN SHARE MODE; 5.445 + LOCK TABLE "delegation" IN SHARE MODE; 5.446 + LOCK TABLE "direct_population_snapshot" IN EXCLUSIVE MODE; 5.447 + LOCK TABLE "delegating_population_snapshot" IN EXCLUSIVE MODE; 5.448 + LOCK TABLE "direct_interest_snapshot" IN EXCLUSIVE MODE; 5.449 + LOCK TABLE "delegating_interest_snapshot" IN EXCLUSIVE MODE; 5.450 + LOCK TABLE "direct_supporter_snapshot" IN EXCLUSIVE MODE; 5.451 + RETURN; 5.452 + END; 5.453 + $$; 5.454 + 5.455 + 5.456 +-- new comment on function "delete_private_data"() 5.457 + 5.458 +COMMENT ON FUNCTION "delete_private_data"() IS 'Used by lf_export script. DO NOT USE on productive database, but only on a copy! This function deletes all data which should not be publicly available, and can be used to create a database dump for publication. See source code to see which data is deleted. If you need a different behaviour, copy this function and modify lf_export accordingly, to avoid data-leaks after updating.'; 5.459 + 5.460 + 5.461 +COMMIT;