Open-ILS/src/sql/Pg/upgrade/0948.schema.fake-delete-parts.sql

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