Uploaded image for project: 'mod-inventory-storage'
  1. mod-inventory-storage
  2. MODINVSTOR-676

Item APIs allow non-existing statistical code IDs to be provided

    XMLWordPrintable

    Details

    • Template:
      Standard Bug Write-Up Format
    • Sprint:
      Prokopovych - Sprint 114, Prokopovych - Sprint 115, Prokopovych - Sprint 116, Prokopovych - Sprint 117, Prokopovych - Sprint 118, Prokopovych - Sprint 119, Prokopovych - Sprint 120, Prokopovych - Sprint 121, Prokopovych - Sprint 124, Prokopovych - Sprint 125, Prokopovych - Sprint 126, Prokopovych - Sprint 127, Prokopovych - Sprint 128
    • Story Points:
      5
    • Development Team:
      Prokopovych

      Description

      Overview

      Making a POST request to /item-storage/items with non-existing statisticalCodeId doesn't error out but gives a 201 – Success response
       
      Steps to Reproduce:

      1. POST /item-storage/items with the body containing a random "statisticalCodeIds": [ "980b3f32-74b3-407f-895e-e3d49078aadd"]

      Expected Results
      Non existing statistical codes should be rejected and the client should receive an appropriate error code

      Actual Results
      201

      Note
      Checking for non-UUID statisticalCodeIDs is split off into MODINVSTOR-755

      Approach

      Use database triggers courtesy of Julian Ladisch

      SET search_path TO diku_mod_inventory_storage;
      
      CREATE OR REPLACE FUNCTION check_statistical_code_references()
      RETURNS TRIGGER AS $$
      DECLARE
        invalid text;
      BEGIN
        SELECT ref
        INTO invalid
        FROM jsonb_array_elements_text(NEW.jsonb->'statisticalCodeIds') ref
        LEFT JOIN statistical_code ON id=ref::uuid
        WHERE id IS NULL
        LIMIT 1;
        IF FOUND THEN
          RAISE foreign_key_violation USING
            MESSAGE='statistical code doesn''t exist: ' || invalid,
            DETAIL='foreign key violation in statisticalCodeIds array of ' || TG_TABLE_NAME || ' with id=' || NEW.id,
            SCHEMA=TG_TABLE_SCHEMA,
            TABLE=TG_TABLE_NAME;
        END IF;
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;
      
      CREATE TRIGGER check_statistical_code_references_on_insert
        BEFORE INSERT ON item
        FOR EACH ROW
        WHEN (NEW.jsonb->'statisticalCodeIds' IS NOT NULL AND NEW.jsonb->'statisticalCodeIds' <> '[]')
        EXECUTE FUNCTION check_statistical_code_references();
      
      CREATE TRIGGER check_statistical_code_references_on_update
        BEFORE UPDATE ON item
        FOR EACH ROW
        WHEN (NEW.jsonb->'statisticalCodeIds' IS NOT NULL AND NEW.jsonb->'statisticalCodeIds' <> '[]'
                   AND OLD.jsonb->'statisticalCodeIds' IS DISTINCT FROM NEW.jsonb->'statisticalCodeIds')
        EXECUTE FUNCTION check_statistical_code_references();
      

      Additional Steps

      • Create issue to handle delete of statistical code associated with instance, holdings, or item.
      • Create issue to handle either add automated migration to cleanup non-existing statistical code ids or document SysOps process to manual cleanup

        TestRail: Results

          Attachments

            Issue Links

              Activity

                People

                Assignee:
                wwelling William Welling
                Reporter:
                sduvvuri Sobha Duvvuri
                Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                  Dates

                  Created:
                  Updated:

                    TestRail: Runs

                      TestRail: Cases