liquid_feedback_core
changeset 599:81cd9463878f
Referential integrity for files: Delete files when last reference is removed
author | jbe |
---|---|
date | Thu Feb 06 19:17:22 2020 +0100 (2020-02-06) |
parents | f02b7ea48971 |
children | f61caa45de94 |
files | core.sql |
line diff
1.1 --- a/core.sql Thu Feb 06 18:40:46 2020 +0100 1.2 +++ b/core.sql Thu Feb 06 19:17:22 2020 +0100 1.3 @@ -3162,6 +3162,61 @@ 1.4 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.'; 1.5 1.6 1.7 +CREATE FUNCTION "file_requires_reference_trigger"() 1.8 + RETURNS TRIGGER 1.9 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.10 + BEGIN 1.11 + IF NOT EXISTS ( 1.12 + SELECT NULL FROM "draft_attachment" WHERE "file_id" = NEW."id" 1.13 + ) THEN 1.14 + RAISE EXCEPTION 'Cannot create an unreferenced file.' USING 1.15 + ERRCODE = 'integrity_constraint_violation', 1.16 + HINT = 'Create file and its reference in another table within the same transaction.'; 1.17 + END IF; 1.18 + RETURN NULL; 1.19 + END; 1.20 + $$; 1.21 + 1.22 +CREATE CONSTRAINT TRIGGER "file_requires_reference" 1.23 + AFTER INSERT OR UPDATE ON "file" DEFERRABLE INITIALLY DEFERRED 1.24 + FOR EACH ROW EXECUTE PROCEDURE 1.25 + "file_requires_reference_trigger"(); 1.26 + 1.27 +COMMENT ON FUNCTION "file_requires_reference_trigger"() IS 'Implementation of trigger "file_requires_reference" on table "file"'; 1.28 +COMMENT ON TRIGGER "file_requires_reference" ON "file" IS 'Ensure that files are always referenced'; 1.29 + 1.30 + 1.31 +CREATE FUNCTION "last_reference_deletes_file_trigger"() 1.32 + RETURNS TRIGGER 1.33 + LANGUAGE 'plpgsql' VOLATILE AS $$ 1.34 + DECLARE 1.35 + "reference_lost" BOOLEAN; 1.36 + BEGIN 1.37 + IF TG_OP = 'DELETE' THEN 1.38 + "reference_lost" := TRUE; 1.39 + ELSE 1.40 + "reference_lost" := NEW."file_id" != OLD."file_id"; 1.41 + END IF; 1.42 + IF 1.43 + "reference_lost" AND NOT EXISTS ( 1.44 + SELECT NULL FROM "draft_attachment" WHERE "file_id" = OLD."file_id" 1.45 + ) 1.46 + THEN 1.47 + DELETE FROM "file" WHERE "id" = OLD."file_id"; 1.48 + END IF; 1.49 + RETURN NULL; 1.50 + END; 1.51 + $$; 1.52 + 1.53 +CREATE CONSTRAINT TRIGGER "last_reference_deletes_file" 1.54 + AFTER UPDATE OR DELETE ON "draft_attachment" DEFERRABLE INITIALLY DEFERRED 1.55 + FOR EACH ROW EXECUTE PROCEDURE 1.56 + "last_reference_deletes_file_trigger"(); 1.57 + 1.58 +COMMENT ON FUNCTION "last_reference_deletes_file_trigger"() IS 'Implementation of trigger "last_reference_deletes_file" on table "draft_attachment"'; 1.59 +COMMENT ON TRIGGER "last_reference_deletes_file" ON "draft_attachment" IS 'Removing the last reference to a file deletes the file'; 1.60 + 1.61 + 1.62 1.63 --------------------------------- 1.64 -- Delete incomplete snapshots --