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 --

Impressum / About Us