liquid_feedback_core
changeset 20:3625d841da90
Triggers ensuring that votes can't be modified by a broken frontend after issues have been frozen
author | jbe |
---|---|
date | Sat Feb 06 03:27:32 2010 +0100 (2010-02-06) |
parents | 549b1a0fc042 |
children | 406090b1ed8e |
files | core.sql |
line diff
1.1 --- a/core.sql Sat Feb 06 03:24:51 2010 +0100 1.2 +++ b/core.sql Sat Feb 06 03:27:32 2010 +0100 1.3 @@ -920,10 +920,56 @@ 1.4 1.5 1.6 1.7 +--------------------------------------------------------------- 1.8 +-- Ensure that votes are not modified when issues are frozen -- 1.9 +--------------------------------------------------------------- 1.10 + 1.11 +-- NOTE: Frontends should ensure this anyway, but in case of programming 1.12 +-- errors the following triggers ensure data integrity. 1.13 + 1.14 + 1.15 +CREATE FUNCTION "forbid_changes_on_closed_issue_trigger"() 1.16 + RETURNS TRIGGER 1.17 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.18 + DECLARE 1.19 + "issue_row" "issue"%ROWTYPE; 1.20 + BEGIN 1.21 + SELECT INTO "issue_row" * FROM "issue" 1.22 + WHERE "id" = NEW."issue_id" FOR SHARE; 1.23 + IF "issue_row"."closed" NOTNULL THEN 1.24 + RAISE EXCEPTION 'Tried to modify data belonging to a closed issue.'; 1.25 + END IF; 1.26 + RETURN NULL; 1.27 + END; 1.28 + $$; 1.29 + 1.30 +CREATE TRIGGER "forbid_changes_on_closed_issue" 1.31 + AFTER INSERT OR UPDATE OR DELETE ON "direct_voter" 1.32 + FOR EACH ROW EXECUTE PROCEDURE 1.33 + "forbid_changes_on_closed_issue_trigger"(); 1.34 + 1.35 +CREATE TRIGGER "forbid_changes_on_closed_issue" 1.36 + AFTER INSERT OR UPDATE OR DELETE ON "delegating_voter" 1.37 + FOR EACH ROW EXECUTE PROCEDURE 1.38 + "forbid_changes_on_closed_issue_trigger"(); 1.39 + 1.40 +CREATE TRIGGER "forbid_changes_on_closed_issue" 1.41 + AFTER INSERT OR UPDATE OR DELETE ON "vote" 1.42 + FOR EACH ROW EXECUTE PROCEDURE 1.43 + "forbid_changes_on_closed_issue_trigger"(); 1.44 + 1.45 +COMMENT ON FUNCTION "forbid_changes_on_closed_issue_trigger"() IS 'Implementation of triggers "forbid_changes_on_closed_issue" on tables "direct_voter", "delegating_voter" and "vote"'; 1.46 +COMMENT ON TRIGGER "forbid_changes_on_closed_issue" ON "direct_voter" IS 'Ensures that frontends can''t tamper with votings of closed issues, in case of programming errors'; 1.47 +COMMENT ON TRIGGER "forbid_changes_on_closed_issue" ON "delegating_voter" IS 'Ensures that frontends can''t tamper with votings of closed issues, in case of programming errors'; 1.48 +COMMENT ON TRIGGER "forbid_changes_on_closed_issue" ON "vote" IS 'Ensures that frontends can''t tamper with votings of closed issues, in case of programming errors'; 1.49 + 1.50 + 1.51 + 1.52 -------------------------------------------------------------------- 1.53 -- Auto-retrieval of fields only needed for referential integrity -- 1.54 -------------------------------------------------------------------- 1.55 1.56 + 1.57 CREATE FUNCTION "autofill_issue_id_trigger"() 1.58 RETURNS TRIGGER 1.59 LANGUAGE 'plpgsql' VOLATILE AS $$