Open-ILS/src/sql/Pg/upgrade/0664.schema.hold-current-shelf-lib.sql

   1 -- Evergreen DB patch 0664.schema.hold-current-shelf-lib.sql
   2 --
   3 --
   4 BEGIN;
   5
   6
   7 -- check whether patch can be applied
   8 SELECT evergreen.upgrade_deps_block_check('0664', :eg_version);
   9
  10 -- add the new column
  11 ALTER TABLE action.hold_request ADD COLUMN current_shelf_lib
  12     INT REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED;
  13
  14 -- set the value for current_shelf_lib on existing shelved holds
  15 UPDATE action.hold_request
  16     SET current_shelf_lib = pickup_lib
  17     FROM asset.copy
  18     WHERE
  19             action.hold_request.shelf_time IS NOT NULL
  20         AND action.hold_request.capture_time IS NOT NULL
  21         AND action.hold_request.current_copy IS NOT NULL
  22         AND action.hold_request.fulfillment_time IS NULL
  23         AND action.hold_request.cancel_time IS NULL
  24         AND asset.copy.id = action.hold_request.current_copy
  25         AND asset.copy.status = 8; -- on holds shelf
  26
  27 COMMIT;