Open-ILS/src/sql/Pg/version-upgrade/2.9.0-2.9.1-upgrade-db.sql

   1 --Upgrade Script for 2.9.0 to 2.9.1
   2 \set eg_version '''2.9.1'''
   3 BEGIN;
   4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.9.1', :eg_version);
   5
   6 SELECT evergreen.upgrade_deps_block_check('0948', :eg_version);
   7
   8 ALTER TABLE biblio.monograph_part ADD COLUMN deleted BOOL NOT NULL DEFAULT FALSE;
   9 CREATE RULE protect_mono_part_delete AS ON DELETE TO biblio.monograph_part DO INSTEAD (
  10     UPDATE biblio.monograph_part SET deleted = TRUE WHERE OLD.id = biblio.monograph_part.id;
  11     DELETE FROM asset.copy_part_map WHERE part = OLD.id
  12 );
  13
  14 CREATE OR REPLACE FUNCTION unapi.holdings_xml (
  15     bid BIGINT,
  16     ouid INT,
  17     org TEXT,
  18     depth INT DEFAULT NULL,
  19     includes TEXT[] DEFAULT NULL::TEXT[],
  20     slimit HSTORE DEFAULT NULL,
  21     soffset HSTORE DEFAULT NULL,
  22     include_xmlns BOOL DEFAULT TRUE,
  23     pref_lib INT DEFAULT NULL
  24 )
  25 RETURNS XML AS $F$
  26      SELECT  XMLELEMENT(
  27                  name holdings,
  28                  XMLATTRIBUTES(
  29                     CASE WHEN $8 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
  30                     CASE WHEN ('bre' = ANY ($5)) THEN 'tag:open-ils.org:U2@bre/' || $1 || '/' || $3 ELSE NULL END AS id,
  31                     (SELECT record_has_holdable_copy FROM asset.record_has_holdable_copy($1)) AS has_holdable
  32                  ),
  33                  XMLELEMENT(
  34                      name counts,
  35                      (SELECT  XMLAGG(XMLELEMENT::XML) FROM (
  36                          SELECT  XMLELEMENT(
  37                                      name count,
  38                                      XMLATTRIBUTES('public' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
  39                                  )::text
  40                            FROM  asset.opac_ou_record_copy_count($2,  $1)
  41                                      UNION
  42                          SELECT  XMLELEMENT(
  43                                      name count,
  44                                      XMLATTRIBUTES('staff' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
  45                                  )::text
  46                            FROM  asset.staff_ou_record_copy_count($2, $1)
  47                                      UNION
  48                          SELECT  XMLELEMENT(
  49                                      name count,
  50                                      XMLATTRIBUTES('pref_lib' as type, depth, org_unit, coalesce(transcendant,0) as transcendant, available, visible as count, unshadow)
  51                                  )::text
  52                            FROM  asset.opac_ou_record_copy_count($9,  $1)
  53                                      ORDER BY 1
  54                      )x)
  55                  ),
  56                  CASE
  57                      WHEN ('bmp' = ANY ($5)) THEN
  58                         XMLELEMENT(
  59                             name monograph_parts,
  60                             (SELECT XMLAGG(bmp) FROM (
  61                                 SELECT  unapi.bmp( id, 'xml', 'monograph_part', evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'bre'), 'holdings_xml'), $3, $4, $6, $7, FALSE)
  62                                   FROM  biblio.monograph_part
  63                                   WHERE NOT deleted AND record = $1
  64                             )x)
  65                         )
  66                      ELSE NULL
  67                  END,
  68                  XMLELEMENT(
  69                      name volumes,
  70                      (SELECT XMLAGG(acn ORDER BY rank, name, label_sortkey) FROM (
  71                         -- Physical copies
  72                         SELECT  unapi.acn(y.id,'xml','volume',evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE), y.rank, name, label_sortkey
  73                         FROM evergreen.ranked_volumes($1, $2, $4, $6, $7, $9, $5) AS y
  74                         UNION ALL
  75                         -- Located URIs
  76                         SELECT unapi.acn(uris.id,'xml','volume',evergreen.array_remove_item_by_value( evergreen.array_remove_item_by_value($5,'holdings_xml'),'bre'), $3, $4, $6, $7, FALSE), uris.rank, name, label_sortkey
  77                         FROM evergreen.located_uris($1, $2, $9) AS uris
  78                      )x)
  79                  ),
  80                  CASE WHEN ('ssub' = ANY ($5)) THEN
  81                      XMLELEMENT(
  82                          name subscriptions,
  83                          (SELECT XMLAGG(ssub) FROM (
  84                             SELECT  unapi.ssub(id,'xml','subscription','{}'::TEXT[], $3, $4, $6, $7, FALSE)
  85                               FROM  serial.subscription
  86                               WHERE record_entry = $1
  87                         )x)
  88                      )
  89                  ELSE NULL END,
  90                  CASE WHEN ('acp' = ANY ($5)) THEN
  91                      XMLELEMENT(
  92                          name foreign_copies,
  93                          (SELECT XMLAGG(acp) FROM (
  94                             SELECT  unapi.acp(p.target_copy,'xml','copy',evergreen.array_remove_item_by_value($5,'acp'), $3, $4, $6, $7, FALSE)
  95                               FROM  biblio.peer_bib_copy_map p
  96                                     JOIN asset.copy c ON (p.target_copy = c.id)
  97                               WHERE NOT c.deleted AND p.peer_record = $1
  98                             LIMIT ($6 -> 'acp')::INT
  99                             OFFSET ($7 -> 'acp')::INT
 100                         )x)
 101                      )
 102                  ELSE NULL END
 103              );
 104 $F$ LANGUAGE SQL STABLE;
 105
 106 CREATE OR REPLACE FUNCTION unapi.bmp ( obj_id BIGINT, format TEXT,  ename TEXT, includes TEXT[], org TEXT, depth INT DEFAULT NULL, slimit HSTORE DEFAULT NULL, soffset HSTORE DEFAULT NULL, include_xmlns BOOL DEFAULT TRUE ) RETURNS XML AS $F$
 107         SELECT  XMLELEMENT(
 108                     name monograph_part,
 109                     XMLATTRIBUTES(
 110                         CASE WHEN $9 THEN 'http://open-ils.org/spec/holdings/v1' ELSE NULL END AS xmlns,
 111                         'tag:open-ils.org:U2@bmp/' || id AS id,
 112                         id AS ident,
 113                         label,
 114                         label_sortkey,
 115                         'tag:open-ils.org:U2@bre/' || record AS record
 116                     ),
 117                     CASE
 118                         WHEN ('acp' = ANY ($4)) THEN
 119                             XMLELEMENT( name copies,
 120                                 (SELECT XMLAGG(acp) FROM (
 121                                     SELECT  unapi.acp( cp.id, 'xml', 'copy', evergreen.array_remove_item_by_value($4,'bmp'), $5, $6, $7, $8, FALSE)
 122                                       FROM  asset.copy cp
 123                                             JOIN asset.copy_part_map cpm ON (cpm.target_copy = cp.id)
 124                                       WHERE cpm.part = $1
 125                                           AND cp.deleted IS FALSE
 126                                       ORDER BY COALESCE(cp.copy_number,0), cp.barcode
 127                                       LIMIT ($7 -> 'acp')::INT
 128                                       OFFSET ($8 -> 'acp')::INT
 129
 130                                 )x)
 131                             )
 132                         ELSE NULL
 133                     END,
 134                     CASE WHEN ('bre' = ANY ($4)) THEN unapi.bre( record, 'marcxml', 'record', evergreen.array_remove_item_by_value($4,'bmp'), $5, $6, $7, $8, FALSE) ELSE NULL END
 135                 )
 136           FROM  biblio.monograph_part
 137           WHERE NOT deleted AND id = $1
 138           GROUP BY id, label, label_sortkey, record;
 139 $F$ LANGUAGE SQL STABLE;
 140
 141
 142
 143 SELECT evergreen.upgrade_deps_block_check('0949', :eg_version);
 144
 145 CREATE OR REPLACE FUNCTION evergreen.protect_reserved_rows_from_delete() RETURNS trigger AS $protect_reserved$
 146 BEGIN
 147 IF OLD.id < TG_ARGV[0]::INT THEN
 148     RAISE EXCEPTION 'Cannot delete row with reserved ID %', OLD.id;
 149 END IF;
 150 END
 151 $protect_reserved$
 152 LANGUAGE plpgsql;
 153
 154 DROP TRIGGER IF EXISTS acq_no_deleted_reserved_cancel_reasons ON acq.cancel_reason;
 155
 156 CREATE TRIGGER acq_no_deleted_reserved_cancel_reasons BEFORE DELETE ON acq.cancel_reason
 157     FOR EACH ROW EXECUTE PROCEDURE evergreen.protect_reserved_rows_from_delete(2000);
 158
 159 ALTER TABLE acq.cancel_reason ENABLE TRIGGER acq_no_deleted_reserved_cancel_reasons;
 160
 161 COMMIT;