liquid_feedback_core
changeset 385:e474e9e1240a v2.2.3
Code cleanup regarding temporary storage of session/transaction data to disable protection triggers
author | jbe |
---|---|
date | Sat Mar 23 13:05:43 2013 +0100 (2013-03-23) |
parents | 1a5c4cc28d09 |
children | 0b0bfcf2e60c |
files | core.sql update/core-update.v2.2.2-v2.2.3.sql |
line diff
1.1 --- a/core.sql Fri Mar 22 22:46:51 2013 +0100 1.2 +++ b/core.sql Sat Mar 23 13:05:43 2013 +0100 1.3 @@ -7,7 +7,7 @@ 1.4 BEGIN; 1.5 1.6 CREATE VIEW "liquid_feedback_version" AS 1.7 - SELECT * FROM (VALUES ('2.2.2', 2, 2, 2)) 1.8 + SELECT * FROM (VALUES ('2.2.3', 2, 2, 3)) 1.9 AS "subquery"("string", "major", "minor", "revision"); 1.10 1.11 1.12 @@ -54,15 +54,15 @@ 1.13 ------------------------- 1.14 1.15 1.16 -CREATE TABLE "internal_session_store" ( 1.17 - PRIMARY KEY ("backend_pid", "key"), 1.18 - "backend_pid" INT4, 1.19 +CREATE TABLE "temporary_transaction_data" ( 1.20 + PRIMARY KEY ("txid", "key"), 1.21 + "txid" INT8 DEFAULT txid_current(), 1.22 "key" TEXT, 1.23 "value" TEXT NOT NULL ); 1.24 1.25 -COMMENT ON TABLE "internal_session_store" IS 'Table to store session variables; shall be emptied before a transaction is committed'; 1.26 - 1.27 -COMMENT ON COLUMN "internal_session_store"."backend_pid" IS 'Value returned by function pg_backend_pid()'; 1.28 +COMMENT ON TABLE "temporary_transaction_data" IS 'Table to store temporary transaction data; shall be emptied before a transaction is committed'; 1.29 + 1.30 +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'; 1.31 1.32 1.33 CREATE TABLE "system_setting" ( 1.34 @@ -1599,8 +1599,8 @@ 1.35 "issue_row" "issue"%ROWTYPE; 1.36 BEGIN 1.37 IF EXISTS ( 1.38 - SELECT NULL FROM "internal_session_store" 1.39 - WHERE "backend_pid" = pg_backend_pid() 1.40 + SELECT NULL FROM "temporary_transaction_data" 1.41 + WHERE "txid" = txid_current() 1.42 AND "key" = 'override_protection_triggers' 1.43 AND "value" = TRUE::TEXT 1.44 ) THEN 1.45 @@ -3671,9 +3671,8 @@ 1.46 SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 1.47 SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 1.48 -- override protection triggers: 1.49 - DELETE FROM "internal_session_store"; 1.50 - INSERT INTO "internal_session_store" ("backend_pid", "key", "value") 1.51 - VALUES (pg_backend_pid(), 'override_protection_triggers', TRUE::TEXT); 1.52 + INSERT INTO "temporary_transaction_data" ("key", "value") 1.53 + VALUES ('override_protection_triggers', TRUE::TEXT); 1.54 -- delete timestamp of voting comment: 1.55 UPDATE "direct_voter" SET "comment_changed" = NULL 1.56 WHERE "issue_id" = "issue_id_p"; 1.57 @@ -3702,8 +3701,9 @@ 1.58 UPDATE "direct_voter" SET "weight" = 1 1.59 WHERE "issue_id" = "issue_id_p"; 1.60 PERFORM "add_vote_delegations"("issue_id_p"); 1.61 - -- finish overriding protection triggers (mandatory, as pids may be reused): 1.62 - DELETE FROM "internal_session_store"; 1.63 + -- finish overriding protection triggers (avoids garbage): 1.64 + DELETE FROM "temporary_transaction_data" 1.65 + WHERE "key" = 'override_protection_triggers'; 1.66 -- materialize battle_view: 1.67 -- NOTE: "closed" column of issue must be set at this point 1.68 DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 1.69 @@ -4344,17 +4344,14 @@ 1.70 CREATE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE) 1.71 RETURNS VOID 1.72 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.73 - DECLARE 1.74 - "issue_row" "issue"%ROWTYPE; 1.75 BEGIN 1.76 - SELECT * INTO "issue_row" 1.77 - FROM "issue" WHERE "id" = "issue_id_p" 1.78 - FOR UPDATE; 1.79 - IF "issue_row"."cleaned" ISNULL THEN 1.80 - UPDATE "issue" SET 1.81 - "state" = 'voting', 1.82 - "closed" = NULL 1.83 - WHERE "id" = "issue_id_p"; 1.84 + IF EXISTS ( 1.85 + SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL 1.86 + ) THEN 1.87 + -- override protection triggers: 1.88 + INSERT INTO "temporary_transaction_data" ("key", "value") 1.89 + VALUES ('override_protection_triggers', TRUE::TEXT); 1.90 + -- clean data: 1.91 DELETE FROM "delegating_voter" 1.92 WHERE "issue_id" = "issue_id_p"; 1.93 DELETE FROM "direct_voter" 1.94 @@ -4375,11 +4372,11 @@ 1.95 USING "initiative" -- NOTE: due to missing index on issue_id 1.96 WHERE "initiative"."issue_id" = "issue_id_p" 1.97 AND "supporter"."initiative_id" = "initiative_id"; 1.98 - UPDATE "issue" SET 1.99 - "state" = "issue_row"."state", 1.100 - "closed" = "issue_row"."closed", 1.101 - "cleaned" = now() 1.102 - WHERE "id" = "issue_id_p"; 1.103 + -- mark issue as cleaned: 1.104 + UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p"; 1.105 + -- finish overriding protection triggers (avoids garbage): 1.106 + DELETE FROM "temporary_transaction_data" 1.107 + WHERE "key" = 'override_protection_triggers'; 1.108 END IF; 1.109 RETURN; 1.110 END; 1.111 @@ -4451,6 +4448,7 @@ 1.112 RETURNS VOID 1.113 LANGUAGE 'plpgsql' VOLATILE AS $$ 1.114 BEGIN 1.115 + DELETE FROM "temporary_transaction_data"; 1.116 DELETE FROM "member" WHERE "activated" ISNULL; 1.117 UPDATE "member" SET 1.118 "invite_code" = NULL,
2.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000 2.2 +++ b/update/core-update.v2.2.2-v2.2.3.sql Sat Mar 23 13:05:43 2013 +0100 2.3 @@ -0,0 +1,243 @@ 2.4 +BEGIN; 2.5 + 2.6 +CREATE OR REPLACE VIEW "liquid_feedback_version" AS 2.7 + SELECT * FROM (VALUES ('2.2.3', 2, 2, 3)) 2.8 + AS "subquery"("string", "major", "minor", "revision"); 2.9 + 2.10 +DROP TABLE "internal_session_store"; 2.11 + 2.12 +CREATE TABLE "temporary_transaction_data" ( 2.13 + PRIMARY KEY ("txid", "key"), 2.14 + "txid" INT8 DEFAULT txid_current(), 2.15 + "key" TEXT, 2.16 + "value" TEXT NOT NULL ); 2.17 + 2.18 +COMMENT ON TABLE "temporary_transaction_data" IS 'Table to store temporary transaction data; shall be emptied before a transaction is committed'; 2.19 + 2.20 +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'; 2.21 + 2.22 +CREATE OR REPLACE FUNCTION "forbid_changes_on_closed_issue_trigger"() 2.23 + RETURNS TRIGGER 2.24 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.25 + DECLARE 2.26 + "issue_id_v" "issue"."id"%TYPE; 2.27 + "issue_row" "issue"%ROWTYPE; 2.28 + BEGIN 2.29 + IF EXISTS ( 2.30 + SELECT NULL FROM "temporary_transaction_data" 2.31 + WHERE "txid" = txid_current() 2.32 + AND "key" = 'override_protection_triggers' 2.33 + AND "value" = TRUE::TEXT 2.34 + ) THEN 2.35 + RETURN NULL; 2.36 + END IF; 2.37 + IF TG_OP = 'DELETE' THEN 2.38 + "issue_id_v" := OLD."issue_id"; 2.39 + ELSE 2.40 + "issue_id_v" := NEW."issue_id"; 2.41 + END IF; 2.42 + SELECT INTO "issue_row" * FROM "issue" 2.43 + WHERE "id" = "issue_id_v" FOR SHARE; 2.44 + IF ( 2.45 + "issue_row"."closed" NOTNULL OR ( 2.46 + "issue_row"."state" = 'voting' AND 2.47 + "issue_row"."phase_finished" NOTNULL 2.48 + ) 2.49 + ) THEN 2.50 + IF 2.51 + TG_RELID = 'direct_voter'::regclass AND 2.52 + TG_OP = 'UPDATE' 2.53 + THEN 2.54 + IF 2.55 + OLD."issue_id" = NEW."issue_id" AND 2.56 + OLD."member_id" = NEW."member_id" AND 2.57 + OLD."weight" = NEW."weight" 2.58 + THEN 2.59 + RETURN NULL; -- allows changing of voter comment 2.60 + END IF; 2.61 + END IF; 2.62 + RAISE EXCEPTION 'Tried to modify data after voting has been closed.'; 2.63 + END IF; 2.64 + RETURN NULL; 2.65 + END; 2.66 + $$; 2.67 + 2.68 +CREATE OR REPLACE FUNCTION "close_voting"("issue_id_p" "issue"."id"%TYPE) 2.69 + RETURNS VOID 2.70 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.71 + DECLARE 2.72 + "area_id_v" "area"."id"%TYPE; 2.73 + "unit_id_v" "unit"."id"%TYPE; 2.74 + "member_id_v" "member"."id"%TYPE; 2.75 + BEGIN 2.76 + PERFORM "require_transaction_isolation"(); 2.77 + SELECT "area_id" INTO "area_id_v" FROM "issue" WHERE "id" = "issue_id_p"; 2.78 + SELECT "unit_id" INTO "unit_id_v" FROM "area" WHERE "id" = "area_id_v"; 2.79 + -- override protection triggers: 2.80 + INSERT INTO "temporary_transaction_data" ("key", "value") 2.81 + VALUES ('override_protection_triggers', TRUE::TEXT); 2.82 + -- delete timestamp of voting comment: 2.83 + UPDATE "direct_voter" SET "comment_changed" = NULL 2.84 + WHERE "issue_id" = "issue_id_p"; 2.85 + -- delete delegating votes (in cases of manual reset of issue state): 2.86 + DELETE FROM "delegating_voter" 2.87 + WHERE "issue_id" = "issue_id_p"; 2.88 + -- delete votes from non-privileged voters: 2.89 + DELETE FROM "direct_voter" 2.90 + USING ( 2.91 + SELECT 2.92 + "direct_voter"."member_id" 2.93 + FROM "direct_voter" 2.94 + JOIN "member" ON "direct_voter"."member_id" = "member"."id" 2.95 + LEFT JOIN "privilege" 2.96 + ON "privilege"."unit_id" = "unit_id_v" 2.97 + AND "privilege"."member_id" = "direct_voter"."member_id" 2.98 + WHERE "direct_voter"."issue_id" = "issue_id_p" AND ( 2.99 + "member"."active" = FALSE OR 2.100 + "privilege"."voting_right" ISNULL OR 2.101 + "privilege"."voting_right" = FALSE 2.102 + ) 2.103 + ) AS "subquery" 2.104 + WHERE "direct_voter"."issue_id" = "issue_id_p" 2.105 + AND "direct_voter"."member_id" = "subquery"."member_id"; 2.106 + -- consider delegations: 2.107 + UPDATE "direct_voter" SET "weight" = 1 2.108 + WHERE "issue_id" = "issue_id_p"; 2.109 + PERFORM "add_vote_delegations"("issue_id_p"); 2.110 + -- finish overriding protection triggers (avoids garbage): 2.111 + DELETE FROM "temporary_transaction_data" 2.112 + WHERE "key" = 'override_protection_triggers'; 2.113 + -- materialize battle_view: 2.114 + -- NOTE: "closed" column of issue must be set at this point 2.115 + DELETE FROM "battle" WHERE "issue_id" = "issue_id_p"; 2.116 + INSERT INTO "battle" ( 2.117 + "issue_id", 2.118 + "winning_initiative_id", "losing_initiative_id", 2.119 + "count" 2.120 + ) SELECT 2.121 + "issue_id", 2.122 + "winning_initiative_id", "losing_initiative_id", 2.123 + "count" 2.124 + FROM "battle_view" WHERE "issue_id" = "issue_id_p"; 2.125 + -- set voter count: 2.126 + UPDATE "issue" SET 2.127 + "voter_count" = ( 2.128 + SELECT coalesce(sum("weight"), 0) 2.129 + FROM "direct_voter" WHERE "issue_id" = "issue_id_p" 2.130 + ) 2.131 + WHERE "id" = "issue_id_p"; 2.132 + -- copy "positive_votes" and "negative_votes" from "battle" table: 2.133 + UPDATE "initiative" SET 2.134 + "positive_votes" = "battle_win"."count", 2.135 + "negative_votes" = "battle_lose"."count" 2.136 + FROM "battle" AS "battle_win", "battle" AS "battle_lose" 2.137 + WHERE 2.138 + "battle_win"."issue_id" = "issue_id_p" AND 2.139 + "battle_win"."winning_initiative_id" = "initiative"."id" AND 2.140 + "battle_win"."losing_initiative_id" ISNULL AND 2.141 + "battle_lose"."issue_id" = "issue_id_p" AND 2.142 + "battle_lose"."losing_initiative_id" = "initiative"."id" AND 2.143 + "battle_lose"."winning_initiative_id" ISNULL; 2.144 + END; 2.145 + $$; 2.146 + 2.147 +CREATE OR REPLACE FUNCTION "clean_issue"("issue_id_p" "issue"."id"%TYPE) 2.148 + RETURNS VOID 2.149 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.150 + BEGIN 2.151 + IF EXISTS ( 2.152 + SELECT NULL FROM "issue" WHERE "id" = "issue_id_p" AND "cleaned" ISNULL 2.153 + ) THEN 2.154 + -- override protection triggers: 2.155 + INSERT INTO "temporary_transaction_data" ("key", "value") 2.156 + VALUES ('override_protection_triggers', TRUE::TEXT); 2.157 + -- clean data: 2.158 + DELETE FROM "delegating_voter" 2.159 + WHERE "issue_id" = "issue_id_p"; 2.160 + DELETE FROM "direct_voter" 2.161 + WHERE "issue_id" = "issue_id_p"; 2.162 + DELETE FROM "delegating_interest_snapshot" 2.163 + WHERE "issue_id" = "issue_id_p"; 2.164 + DELETE FROM "direct_interest_snapshot" 2.165 + WHERE "issue_id" = "issue_id_p"; 2.166 + DELETE FROM "delegating_population_snapshot" 2.167 + WHERE "issue_id" = "issue_id_p"; 2.168 + DELETE FROM "direct_population_snapshot" 2.169 + WHERE "issue_id" = "issue_id_p"; 2.170 + DELETE FROM "non_voter" 2.171 + WHERE "issue_id" = "issue_id_p"; 2.172 + DELETE FROM "delegation" 2.173 + WHERE "issue_id" = "issue_id_p"; 2.174 + DELETE FROM "supporter" 2.175 + USING "initiative" -- NOTE: due to missing index on issue_id 2.176 + WHERE "initiative"."issue_id" = "issue_id_p" 2.177 + AND "supporter"."initiative_id" = "initiative_id"; 2.178 + -- mark issue as cleaned: 2.179 + UPDATE "issue" SET "cleaned" = now() WHERE "id" = "issue_id_p"; 2.180 + -- finish overriding protection triggers (avoids garbage): 2.181 + DELETE FROM "temporary_transaction_data" 2.182 + WHERE "key" = 'override_protection_triggers'; 2.183 + END IF; 2.184 + RETURN; 2.185 + END; 2.186 + $$; 2.187 + 2.188 +CREATE OR REPLACE FUNCTION "delete_private_data"() 2.189 + RETURNS VOID 2.190 + LANGUAGE 'plpgsql' VOLATILE AS $$ 2.191 + BEGIN 2.192 + DELETE FROM "temporary_transaction_data"; 2.193 + DELETE FROM "member" WHERE "activated" ISNULL; 2.194 + UPDATE "member" SET 2.195 + "invite_code" = NULL, 2.196 + "invite_code_expiry" = NULL, 2.197 + "admin_comment" = NULL, 2.198 + "last_login" = NULL, 2.199 + "login" = NULL, 2.200 + "password" = NULL, 2.201 + "lang" = NULL, 2.202 + "notify_email" = NULL, 2.203 + "notify_email_unconfirmed" = NULL, 2.204 + "notify_email_secret" = NULL, 2.205 + "notify_email_secret_expiry" = NULL, 2.206 + "notify_email_lock_expiry" = NULL, 2.207 + "notify_level" = NULL, 2.208 + "password_reset_secret" = NULL, 2.209 + "password_reset_secret_expiry" = NULL, 2.210 + "organizational_unit" = NULL, 2.211 + "internal_posts" = NULL, 2.212 + "realname" = NULL, 2.213 + "birthday" = NULL, 2.214 + "address" = NULL, 2.215 + "email" = NULL, 2.216 + "xmpp_address" = NULL, 2.217 + "website" = NULL, 2.218 + "phone" = NULL, 2.219 + "mobile_phone" = NULL, 2.220 + "profession" = NULL, 2.221 + "external_memberships" = NULL, 2.222 + "external_posts" = NULL, 2.223 + "formatting_engine" = NULL, 2.224 + "statement" = NULL; 2.225 + -- "text_search_data" is updated by triggers 2.226 + DELETE FROM "setting"; 2.227 + DELETE FROM "setting_map"; 2.228 + DELETE FROM "member_relation_setting"; 2.229 + DELETE FROM "member_image"; 2.230 + DELETE FROM "contact"; 2.231 + DELETE FROM "ignored_member"; 2.232 + DELETE FROM "session"; 2.233 + DELETE FROM "area_setting"; 2.234 + DELETE FROM "issue_setting"; 2.235 + DELETE FROM "ignored_initiative"; 2.236 + DELETE FROM "initiative_setting"; 2.237 + DELETE FROM "suggestion_setting"; 2.238 + DELETE FROM "non_voter"; 2.239 + DELETE FROM "direct_voter" USING "issue" 2.240 + WHERE "direct_voter"."issue_id" = "issue"."id" 2.241 + AND "issue"."closed" ISNULL; 2.242 + RETURN; 2.243 + END; 2.244 + $$; 2.245 + 2.246 +COMMIT;