Forward-port 3.1.0 upgrade script
[working/Evergreen.git] / Open-ILS / src / sql / Pg / version-upgrade / 3.0.6-3.1.0-upgrade-db.sql
1 --Upgrade Script for 3.0.6 to 3.1.0
2 \set eg_version '''3.1.0'''
3 BEGIN;
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('3.1.0', :eg_version);
5
6 SELECT evergreen.upgrade_deps_block_check('1089', :eg_version);
7
8 -- Add the circ.holds.max_duplicate_holds org. unit setting type.
9 INSERT into config.org_unit_setting_type
10 ( name, grp, label, description, datatype, fm_class )
11 VALUES
12 ( 'circ.holds.max_duplicate_holds', 'holds',
13    oils_i18n_gettext(
14      'circ.holds.max_duplicate_holds',
15      'Maximum number of duplicate holds allowed.',
16      'coust', 'label'),
17    oils_i18n_gettext(
18      'circ.holds.max_duplicate_holds',
19      'Maximum number of duplicate title or metarecord holds allowed per patron.',
20      'coust', 'description'),
21    'integer', null );
22
23
24
25 SELECT evergreen.upgrade_deps_block_check('1090', :eg_version);
26
27 ALTER TABLE biblio.record_entry
28     ADD COLUMN merge_date TIMESTAMP WITH TIME ZONE,
29     ADD COLUMN merged_to BIGINT REFERENCES biblio.record_entry(id);
30
31 CREATE OR REPLACE FUNCTION asset.merge_record_assets( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
32 DECLARE
33     moved_objects INT := 0;
34     source_cn     asset.call_number%ROWTYPE;
35     target_cn     asset.call_number%ROWTYPE;
36     metarec       metabib.metarecord%ROWTYPE;
37     hold          action.hold_request%ROWTYPE;
38     ser_rec       serial.record_entry%ROWTYPE;
39     ser_sub       serial.subscription%ROWTYPE;
40     acq_lineitem  acq.lineitem%ROWTYPE;
41     acq_request   acq.user_request%ROWTYPE;
42     booking       booking.resource_type%ROWTYPE;
43     source_part   biblio.monograph_part%ROWTYPE;
44     target_part   biblio.monograph_part%ROWTYPE;
45     multi_home    biblio.peer_bib_copy_map%ROWTYPE;
46     uri_count     INT := 0;
47     counter       INT := 0;
48     uri_datafield TEXT;
49     uri_text      TEXT := '';
50 BEGIN
51
52     -- move any 856 entries on records that have at least one MARC-mapped URI entry
53     SELECT  INTO uri_count COUNT(*)
54       FROM  asset.uri_call_number_map m
55             JOIN asset.call_number cn ON (m.call_number = cn.id)
56       WHERE cn.record = source_record;
57
58     IF uri_count > 0 THEN
59         
60         -- This returns more nodes than you might expect:
61         -- 7 instead of 1 for an 856 with $u $y $9
62         SELECT  COUNT(*) INTO counter
63           FROM  oils_xpath_table(
64                     'id',
65                     'marc',
66                     'biblio.record_entry',
67                     '//*[@tag="856"]',
68                     'id=' || source_record
69                 ) as t(i int,c text);
70     
71         FOR i IN 1 .. counter LOOP
72             SELECT  '<datafield xmlns="http://www.loc.gov/MARC21/slim"' || 
73                         ' tag="856"' ||
74                         ' ind1="' || FIRST(ind1) || '"'  ||
75                         ' ind2="' || FIRST(ind2) || '">' ||
76                         STRING_AGG(
77                             '<subfield code="' || subfield || '">' ||
78                             regexp_replace(
79                                 regexp_replace(
80                                     regexp_replace(data,'&','&amp;','g'),
81                                     '>', '&gt;', 'g'
82                                 ),
83                                 '<', '&lt;', 'g'
84                             ) || '</subfield>', ''
85                         ) || '</datafield>' INTO uri_datafield
86               FROM  oils_xpath_table(
87                         'id',
88                         'marc',
89                         'biblio.record_entry',
90                         '//*[@tag="856"][position()=' || i || ']/@ind1|' ||
91                         '//*[@tag="856"][position()=' || i || ']/@ind2|' ||
92                         '//*[@tag="856"][position()=' || i || ']/*/@code|' ||
93                         '//*[@tag="856"][position()=' || i || ']/*[@code]',
94                         'id=' || source_record
95                     ) as t(id int,ind1 text, ind2 text,subfield text,data text);
96
97             -- As most of the results will be NULL, protect against NULLifying
98             -- the valid content that we do generate
99             uri_text := uri_text || COALESCE(uri_datafield, '');
100         END LOOP;
101
102         IF uri_text <> '' THEN
103             UPDATE  biblio.record_entry
104               SET   marc = regexp_replace(marc,'(</[^>]*record>)', uri_text || E'\\1')
105               WHERE id = target_record;
106         END IF;
107
108     END IF;
109
110         -- Find and move metarecords to the target record
111         SELECT  INTO metarec *
112           FROM  metabib.metarecord
113           WHERE master_record = source_record;
114
115         IF FOUND THEN
116                 UPDATE  metabib.metarecord
117                   SET   master_record = target_record,
118                         mods = NULL
119                   WHERE id = metarec.id;
120
121                 moved_objects := moved_objects + 1;
122         END IF;
123
124         -- Find call numbers attached to the source ...
125         FOR source_cn IN SELECT * FROM asset.call_number WHERE record = source_record LOOP
126
127                 SELECT  INTO target_cn *
128                   FROM  asset.call_number
129                   WHERE label = source_cn.label
130             AND prefix = source_cn.prefix
131             AND suffix = source_cn.suffix
132                         AND owning_lib = source_cn.owning_lib
133                         AND record = target_record
134                         AND NOT deleted;
135
136                 -- ... and if there's a conflicting one on the target ...
137                 IF FOUND THEN
138
139                         -- ... move the copies to that, and ...
140                         UPDATE  asset.copy
141                           SET   call_number = target_cn.id
142                           WHERE call_number = source_cn.id;
143
144                         -- ... move V holds to the move-target call number
145                         FOR hold IN SELECT * FROM action.hold_request WHERE target = source_cn.id AND hold_type = 'V' LOOP
146                 
147                                 UPDATE  action.hold_request
148                                   SET   target = target_cn.id
149                                   WHERE id = hold.id;
150                 
151                                 moved_objects := moved_objects + 1;
152                         END LOOP;
153         
154             UPDATE asset.call_number SET deleted = TRUE WHERE id = source_cn.id;
155
156                 -- ... if not ...
157                 ELSE
158                         -- ... just move the call number to the target record
159                         UPDATE  asset.call_number
160                           SET   record = target_record
161                           WHERE id = source_cn.id;
162                 END IF;
163
164                 moved_objects := moved_objects + 1;
165         END LOOP;
166
167         -- Find T holds targeting the source record ...
168         FOR hold IN SELECT * FROM action.hold_request WHERE target = source_record AND hold_type = 'T' LOOP
169
170                 -- ... and move them to the target record
171                 UPDATE  action.hold_request
172                   SET   target = target_record
173                   WHERE id = hold.id;
174
175                 moved_objects := moved_objects + 1;
176         END LOOP;
177
178         -- Find serial records targeting the source record ...
179         FOR ser_rec IN SELECT * FROM serial.record_entry WHERE record = source_record LOOP
180                 -- ... and move them to the target record
181                 UPDATE  serial.record_entry
182                   SET   record = target_record
183                   WHERE id = ser_rec.id;
184
185                 moved_objects := moved_objects + 1;
186         END LOOP;
187
188         -- Find serial subscriptions targeting the source record ...
189         FOR ser_sub IN SELECT * FROM serial.subscription WHERE record_entry = source_record LOOP
190                 -- ... and move them to the target record
191                 UPDATE  serial.subscription
192                   SET   record_entry = target_record
193                   WHERE id = ser_sub.id;
194
195                 moved_objects := moved_objects + 1;
196         END LOOP;
197
198         -- Find booking resource types targeting the source record ...
199         FOR booking IN SELECT * FROM booking.resource_type WHERE record = source_record LOOP
200                 -- ... and move them to the target record
201                 UPDATE  booking.resource_type
202                   SET   record = target_record
203                   WHERE id = booking.id;
204
205                 moved_objects := moved_objects + 1;
206         END LOOP;
207
208         -- Find acq lineitems targeting the source record ...
209         FOR acq_lineitem IN SELECT * FROM acq.lineitem WHERE eg_bib_id = source_record LOOP
210                 -- ... and move them to the target record
211                 UPDATE  acq.lineitem
212                   SET   eg_bib_id = target_record
213                   WHERE id = acq_lineitem.id;
214
215                 moved_objects := moved_objects + 1;
216         END LOOP;
217
218         -- Find acq user purchase requests targeting the source record ...
219         FOR acq_request IN SELECT * FROM acq.user_request WHERE eg_bib = source_record LOOP
220                 -- ... and move them to the target record
221                 UPDATE  acq.user_request
222                   SET   eg_bib = target_record
223                   WHERE id = acq_request.id;
224
225                 moved_objects := moved_objects + 1;
226         END LOOP;
227
228         -- Find parts attached to the source ...
229         FOR source_part IN SELECT * FROM biblio.monograph_part WHERE record = source_record LOOP
230
231                 SELECT  INTO target_part *
232                   FROM  biblio.monograph_part
233                   WHERE label = source_part.label
234                         AND record = target_record;
235
236                 -- ... and if there's a conflicting one on the target ...
237                 IF FOUND THEN
238
239                         -- ... move the copy-part maps to that, and ...
240                         UPDATE  asset.copy_part_map
241                           SET   part = target_part.id
242                           WHERE part = source_part.id;
243
244                         -- ... move P holds to the move-target part
245                         FOR hold IN SELECT * FROM action.hold_request WHERE target = source_part.id AND hold_type = 'P' LOOP
246                 
247                                 UPDATE  action.hold_request
248                                   SET   target = target_part.id
249                                   WHERE id = hold.id;
250                 
251                                 moved_objects := moved_objects + 1;
252                         END LOOP;
253
254                 -- ... if not ...
255                 ELSE
256                         -- ... just move the part to the target record
257                         UPDATE  biblio.monograph_part
258                           SET   record = target_record
259                           WHERE id = source_part.id;
260                 END IF;
261
262                 moved_objects := moved_objects + 1;
263         END LOOP;
264
265         -- Find multi_home items attached to the source ...
266         FOR multi_home IN SELECT * FROM biblio.peer_bib_copy_map WHERE peer_record = source_record LOOP
267                 -- ... and move them to the target record
268                 UPDATE  biblio.peer_bib_copy_map
269                   SET   peer_record = target_record
270                   WHERE id = multi_home.id;
271
272                 moved_objects := moved_objects + 1;
273         END LOOP;
274
275         -- And delete mappings where the item's home bib was merged with the peer bib
276         DELETE FROM biblio.peer_bib_copy_map WHERE peer_record = (
277                 SELECT (SELECT record FROM asset.call_number WHERE id = call_number)
278                 FROM asset.copy WHERE id = target_copy
279         );
280
281     -- Apply merge tracking
282     UPDATE biblio.record_entry 
283         SET merge_date = NOW() WHERE id = target_record;
284
285     UPDATE biblio.record_entry
286         SET merge_date = NOW(), merged_to = target_record
287         WHERE id = source_record;
288
289     -- Finally, "delete" the source record
290     DELETE FROM biblio.record_entry WHERE id = source_record;
291
292         -- That's all, folks!
293         RETURN moved_objects;
294 END;
295 $func$ LANGUAGE plpgsql;
296
297
298
299 SELECT evergreen.upgrade_deps_block_check('1091', :eg_version);
300
301 ALTER TABLE acq.funding_source DROP CONSTRAINT funding_source_code_key;
302 ALTER TABLE acq.funding_source ALTER COLUMN code SET NOT NULL;
303 ALTER TABLE acq.funding_source ADD CONSTRAINT funding_source_code_once_per_owner UNIQUE (code,owner);
304
305
306 SELECT evergreen.upgrade_deps_block_check('1092', :eg_version);
307
308 CREATE OR REPLACE FUNCTION metabib.reingest_record_attributes (rid BIGINT, pattr_list TEXT[] DEFAULT NULL, prmarc TEXT DEFAULT NULL, rdeleted BOOL DEFAULT TRUE) RETURNS VOID AS $func$
309 DECLARE
310     transformed_xml TEXT;
311     rmarc           TEXT := prmarc;
312     tmp_val         TEXT;
313     prev_xfrm       TEXT;
314     normalizer      RECORD;
315     xfrm            config.xml_transform%ROWTYPE;
316     attr_vector     INT[] := '{}'::INT[];
317     attr_vector_tmp INT[];
318     attr_list       TEXT[] := pattr_list;
319     attr_value      TEXT[];
320     norm_attr_value TEXT[];
321     tmp_xml         TEXT;
322     tmp_array       TEXT[];
323     attr_def        config.record_attr_definition%ROWTYPE;
324     ccvm_row        config.coded_value_map%ROWTYPE;
325     jump_past       BOOL;
326 BEGIN
327
328     IF attr_list IS NULL OR rdeleted THEN -- need to do the full dance on INSERT or undelete
329         SELECT ARRAY_AGG(name) INTO attr_list FROM config.record_attr_definition
330         WHERE (
331             tag IS NOT NULL OR
332             fixed_field IS NOT NULL OR
333             xpath IS NOT NULL OR
334             phys_char_sf IS NOT NULL OR
335             composite
336         ) AND (
337             filter OR sorter
338         );
339     END IF;
340
341     IF rmarc IS NULL THEN
342         SELECT marc INTO rmarc FROM biblio.record_entry WHERE id = rid;
343     END IF;
344
345     FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE NOT composite AND name = ANY( attr_list ) ORDER BY format LOOP
346
347         jump_past := FALSE; -- This gets set when we are non-multi and have found something
348         attr_value := '{}'::TEXT[];
349         norm_attr_value := '{}'::TEXT[];
350         attr_vector_tmp := '{}'::INT[];
351
352         SELECT * INTO ccvm_row FROM config.coded_value_map c WHERE c.ctype = attr_def.name LIMIT 1; 
353
354         IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection
355             SELECT  ARRAY_AGG(value) INTO attr_value
356               FROM  (SELECT * FROM metabib.full_rec ORDER BY tag, subfield) AS x
357               WHERE record = rid
358                     AND tag LIKE attr_def.tag
359                     AND CASE
360                         WHEN attr_def.sf_list IS NOT NULL 
361                             THEN POSITION(subfield IN attr_def.sf_list) > 0
362                         ELSE TRUE
363                     END
364               GROUP BY tag
365               ORDER BY tag;
366
367             IF NOT attr_def.multi THEN
368                 attr_value := ARRAY[ARRAY_TO_STRING(attr_value, COALESCE(attr_def.joiner,' '))];
369                 jump_past := TRUE;
370             END IF;
371         END IF;
372
373         IF NOT jump_past AND attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
374             attr_value := attr_value || vandelay.marc21_extract_fixed_field_list(rmarc, attr_def.fixed_field);
375
376             IF NOT attr_def.multi THEN
377                 attr_value := ARRAY[attr_value[1]];
378                 jump_past := TRUE;
379             END IF;
380         END IF;
381
382         IF NOT jump_past AND attr_def.xpath IS NOT NULL THEN -- and xpath expression
383
384             SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
385         
386             -- See if we can skip the XSLT ... it's expensive
387             IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
388                 -- Can't skip the transform
389                 IF xfrm.xslt <> '---' THEN
390                     transformed_xml := oils_xslt_process(rmarc,xfrm.xslt);
391                 ELSE
392                     transformed_xml := rmarc;
393                 END IF;
394     
395                 prev_xfrm := xfrm.name;
396             END IF;
397
398             IF xfrm.name IS NULL THEN
399                 -- just grab the marcxml (empty) transform
400                 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
401                 prev_xfrm := xfrm.name;
402             END IF;
403
404             FOR tmp_xml IN SELECT UNNEST(oils_xpath(attr_def.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]])) LOOP
405                 tmp_val := oils_xpath_string(
406                                 '//*',
407                                 tmp_xml,
408                                 COALESCE(attr_def.joiner,' '),
409                                 ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
410                             );
411                 IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
412                     attr_value := attr_value || tmp_val;
413                     EXIT WHEN NOT attr_def.multi;
414                 END IF;
415             END LOOP;
416         END IF;
417
418         IF NOT jump_past AND attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
419             SELECT  ARRAY_AGG(m.value) INTO tmp_array
420               FROM  vandelay.marc21_physical_characteristics(rmarc) v
421                     LEFT JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value)
422               WHERE v.subfield = attr_def.phys_char_sf AND (m.value IS NOT NULL AND BTRIM(m.value) <> '')
423                     AND ( ccvm_row.id IS NULL OR ( ccvm_row.id IS NOT NULL AND v.id IS NOT NULL) );
424
425             attr_value := attr_value || tmp_array;
426
427             IF NOT attr_def.multi THEN
428                 attr_value := ARRAY[attr_value[1]];
429             END IF;
430
431         END IF;
432
433                 -- apply index normalizers to attr_value
434         FOR tmp_val IN SELECT value FROM UNNEST(attr_value) x(value) LOOP
435             FOR normalizer IN
436                 SELECT  n.func AS func,
437                         n.param_count AS param_count,
438                         m.params AS params
439                   FROM  config.index_normalizer n
440                         JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
441                   WHERE attr = attr_def.name
442                   ORDER BY m.pos LOOP
443                     EXECUTE 'SELECT ' || normalizer.func || '(' ||
444                     COALESCE( quote_literal( tmp_val ), 'NULL' ) ||
445                         CASE
446                             WHEN normalizer.param_count > 0
447                                 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
448                                 ELSE ''
449                             END ||
450                     ')' INTO tmp_val;
451
452             END LOOP;
453             IF tmp_val IS NOT NULL AND tmp_val <> '' THEN
454                 -- note that a string that contains only blanks
455                 -- is a valid value for some attributes
456                 norm_attr_value := norm_attr_value || tmp_val;
457             END IF;
458         END LOOP;
459         
460         IF attr_def.filter THEN
461             -- Create unknown uncontrolled values and find the IDs of the values
462             IF ccvm_row.id IS NULL THEN
463                 FOR tmp_val IN SELECT value FROM UNNEST(norm_attr_value) x(value) LOOP
464                     IF tmp_val IS NOT NULL AND BTRIM(tmp_val) <> '' THEN
465                         BEGIN -- use subtransaction to isolate unique constraint violations
466                             INSERT INTO metabib.uncontrolled_record_attr_value ( attr, value ) VALUES ( attr_def.name, tmp_val );
467                         EXCEPTION WHEN unique_violation THEN END;
468                     END IF;
469                 END LOOP;
470
471                 SELECT ARRAY_AGG(id) INTO attr_vector_tmp FROM metabib.uncontrolled_record_attr_value WHERE attr = attr_def.name AND value = ANY( norm_attr_value );
472             ELSE
473                 SELECT ARRAY_AGG(id) INTO attr_vector_tmp FROM config.coded_value_map WHERE ctype = attr_def.name AND code = ANY( norm_attr_value );
474             END IF;
475
476             -- Add the new value to the vector
477             attr_vector := attr_vector || attr_vector_tmp;
478         END IF;
479
480         IF attr_def.sorter THEN
481             DELETE FROM metabib.record_sorter WHERE source = rid AND attr = attr_def.name;
482             IF norm_attr_value[1] IS NOT NULL THEN
483                 INSERT INTO metabib.record_sorter (source, attr, value) VALUES (rid, attr_def.name, norm_attr_value[1]);
484             END IF;
485         END IF;
486
487     END LOOP;
488
489 /* We may need to rewrite the vlist to contain
490    the intersection of new values for requested
491    attrs and old values for ignored attrs. To
492    do this, we take the old attr vlist and
493    subtract any values that are valid for the
494    requested attrs, and then add back the new
495    set of attr values. */
496
497     IF ARRAY_LENGTH(pattr_list, 1) > 0 THEN 
498         SELECT vlist INTO attr_vector_tmp FROM metabib.record_attr_vector_list WHERE source = rid;
499         SELECT attr_vector_tmp - ARRAY_AGG(id::INT) INTO attr_vector_tmp FROM metabib.full_attr_id_map WHERE attr = ANY (pattr_list);
500         attr_vector := attr_vector || attr_vector_tmp;
501     END IF;
502
503     -- On to composite attributes, now that the record attrs have been pulled.  Processed in name order, so later composite
504     -- attributes can depend on earlier ones.
505     PERFORM metabib.compile_composite_attr_cache_init();
506     FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE composite AND name = ANY( attr_list ) ORDER BY name LOOP
507
508         FOR ccvm_row IN SELECT * FROM config.coded_value_map c WHERE c.ctype = attr_def.name ORDER BY value LOOP
509
510             tmp_val := metabib.compile_composite_attr( ccvm_row.id );
511             CONTINUE WHEN tmp_val IS NULL OR tmp_val = ''; -- nothing to do
512
513             IF attr_def.filter THEN
514                 IF attr_vector @@ tmp_val::query_int THEN
515                     attr_vector = attr_vector + intset(ccvm_row.id);
516                     EXIT WHEN NOT attr_def.multi;
517                 END IF;
518             END IF;
519
520             IF attr_def.sorter THEN
521                 IF attr_vector @@ tmp_val THEN
522                     DELETE FROM metabib.record_sorter WHERE source = rid AND attr = attr_def.name;
523                     INSERT INTO metabib.record_sorter (source, attr, value) VALUES (rid, attr_def.name, ccvm_row.code);
524                 END IF;
525             END IF;
526
527         END LOOP;
528
529     END LOOP;
530
531     IF ARRAY_LENGTH(attr_vector, 1) > 0 THEN
532         IF rdeleted THEN -- initial insert OR revivication
533             DELETE FROM metabib.record_attr_vector_list WHERE source = rid;
534             INSERT INTO metabib.record_attr_vector_list (source, vlist) VALUES (rid, attr_vector);
535         ELSE
536             UPDATE metabib.record_attr_vector_list SET vlist = attr_vector WHERE source = rid;
537         END IF;
538     END IF;
539
540 END;
541
542 $func$ LANGUAGE PLPGSQL;
543
544
545
546 SELECT evergreen.upgrade_deps_block_check('1093', :eg_version);
547
548 UPDATE config.record_attr_definition SET tag = '041', sf_list = 'abdefgm' where name = 'item_lang';
549
550
551
552 SELECT evergreen.upgrade_deps_block_check('1094', :eg_version);
553
554 SELECT metabib.reingest_record_attributes (record, '{item_lang}'::TEXT[])
555   FROM (SELECT  DISTINCT record
556           FROM  metabib.real_full_rec
557            WHERE tag = '041'
558                   AND subfield IN ('a','b','d','e','f','g','m')
559        ) x;
560
561
562
563 SELECT evergreen.upgrade_deps_block_check('1095', :eg_version);
564
565 CREATE OR REPLACE FUNCTION asset.copy_state (cid BIGINT) RETURNS TEXT AS $$
566 DECLARE
567     last_circ_stop      TEXT;
568     the_copy        asset.copy%ROWTYPE;
569 BEGIN
570
571     SELECT * INTO the_copy FROM asset.copy WHERE id = cid;
572     IF NOT FOUND THEN RETURN NULL; END IF;
573
574     IF the_copy.status = 3 THEN -- Lost
575         RETURN 'LOST';
576     ELSIF the_copy.status = 4 THEN -- Missing
577         RETURN 'MISSING';
578     ELSIF the_copy.status = 14 THEN -- Damaged
579         RETURN 'DAMAGED';
580     ELSIF the_copy.status = 17 THEN -- Lost and paid
581         RETURN 'LOST_AND_PAID';
582     END IF;
583
584     SELECT stop_fines INTO last_circ_stop
585       FROM  action.circulation
586       WHERE target_copy = cid
587       ORDER BY xact_start DESC LIMIT 1;
588
589     IF FOUND THEN
590         IF last_circ_stop IN (
591             'CLAIMSNEVERCHECKEDOUT',
592             'CLAIMSRETURNED',
593             'LONGOVERDUE'
594         ) THEN
595             RETURN last_circ_stop;
596         END IF;
597     END IF;
598
599     RETURN 'NORMAL';
600 END;
601 $$ LANGUAGE PLPGSQL;
602
603 CREATE TYPE config.copy_alert_type_state AS ENUM (
604     'NORMAL',
605     'LOST',
606     'LOST_AND_PAID',
607     'MISSING',
608     'DAMAGED',
609     'CLAIMSRETURNED',
610     'LONGOVERDUE',
611     'CLAIMSNEVERCHECKEDOUT'
612 );
613
614 CREATE TYPE config.copy_alert_type_event AS ENUM (
615     'CHECKIN',
616     'CHECKOUT'
617 );
618
619 CREATE TABLE config.copy_alert_type (
620     id          serial  primary key, -- reserve 1-100 for system
621     scope_org   int not null references actor.org_unit (id) on delete cascade,
622     active      bool    not null default true,
623     name        text    not null unique,
624     state       config.copy_alert_type_state,
625     event       config.copy_alert_type_event,
626     in_renew    bool,
627     invert_location bool    not null default false,
628     at_circ     bool,
629     at_owning   bool,
630     next_status int[]
631 );
632 SELECT SETVAL('config.copy_alert_type_id_seq'::TEXT, 100);
633
634 CREATE OR REPLACE FUNCTION evergreen.asset_copy_alert_copy_inh_fkey() RETURNS TRIGGER AS $f$
635 BEGIN
636         PERFORM 1 FROM asset.copy WHERE id = NEW.copy;
637         IF NOT FOUND THEN
638                 RAISE foreign_key_violation USING MESSAGE = FORMAT(
639                         $$Referenced asset.copy id not found, copy:%s$$, NEW.copy
640                 );
641         END IF;
642         RETURN NEW;
643 END;
644 $f$ LANGUAGE PLPGSQL VOLATILE COST 50;
645
646 CREATE TABLE actor.copy_alert_suppress (
647     id          serial primary key,
648     org         int not null references actor.org_unit (id) on delete cascade,
649     alert_type  int not null references config.copy_alert_type (id) on delete cascade
650 );
651
652 CREATE TABLE asset.copy_alert (
653     id      bigserial   primary key,
654     alert_type  int     not null references config.copy_alert_type (id) on delete cascade,
655     copy        bigint  not null,
656     temp        bool    not null default false,
657     create_time timestamptz not null default now(),
658     create_staff    bigint  not null references actor.usr (id) on delete set null,
659     note        text,
660     ack_time    timestamptz,
661     ack_staff   bigint references actor.usr (id) on delete set null
662 );
663
664 CREATE CONSTRAINT TRIGGER inherit_asset_copy_alert_copy_fkey
665         AFTER UPDATE OR INSERT ON asset.copy_alert
666         DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.asset_copy_alert_copy_inh_fkey();
667
668 CREATE VIEW asset.active_copy_alert AS
669     SELECT  *
670       FROM  asset.copy_alert
671       WHERE ack_time IS NULL;
672
673
674
675 SELECT evergreen.upgrade_deps_block_check('1096', :eg_version);
676
677 -- staff-usable alert types with no location awareness
678 INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event, in_renew)
679 VALUES (1, 1, TRUE, 'Normal checkout', 'NORMAL', 'CHECKOUT', FALSE);
680 INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event, in_renew)
681 VALUES (2, 1, TRUE, 'Normal checkin', 'NORMAL', 'CHECKIN', FALSE);
682 INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event, in_renew)
683 VALUES (3, 1, FALSE, 'Normal renewal', 'NORMAL', 'CHECKIN', TRUE);
684
685 -- copy alerts upon checkin or renewal of exceptional copy statuses are not active by
686 -- default; they're meant to be turned once a site is ready to fully
687 -- commit to using the webstaff client for circulation
688 INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event)
689 VALUES (4, 1, FALSE, 'Checkin of lost copy', 'LOST', 'CHECKIN');
690 INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event)
691 VALUES (5, 1, FALSE, 'Checkin of missing copy', 'MISSING', 'CHECKIN');
692 INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event)
693 VALUES (6, 1, FALSE, 'Checkin of lost-and-paid copy', 'LOST_AND_PAID', 'CHECKIN');
694 INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event)
695 VALUES (7, 1, FALSE, 'Checkin of damaged copy', 'DAMAGED', 'CHECKIN');
696 INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event)
697 VALUES (8, 1, FALSE, 'Checkin of claims-returned copy', 'CLAIMSRETURNED', 'CHECKIN');
698 INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event)
699 VALUES (9, 1, FALSE, 'Checkin of long overdue copy', 'LONGOVERDUE', 'CHECKIN');
700 INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event)
701 VALUES (10, 1, FALSE, 'Checkin of claims-never-checked-out copy', 'CLAIMSNEVERCHECKEDOUT', 'CHECKIN');
702
703 -- copy alerts upon checkout of exceptional copy statuses are not active by
704 -- default; they're meant to be turned once a site is ready to fully
705 -- commit to using the webstaff client for circulation
706 INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event)
707 VALUES (11, 1, FALSE, 'Checkout of lost copy', 'LOST', 'CHECKOUT');
708 INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event)
709 VALUES (12, 1, FALSE, 'Checkout of missing copy', 'MISSING', 'CHECKOUT');
710 INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event)
711 VALUES (13, 1, FALSE, 'Checkout of lost-and-paid copy', 'LOST_AND_PAID', 'CHECKOUT');
712 INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event)
713 VALUES (14, 1, FALSE, 'Checkout of damaged copy', 'DAMAGED', 'CHECKOUT');
714 INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event)
715 VALUES (15, 1, FALSE, 'Checkout of claims-returned copy', 'CLAIMSRETURNED', 'CHECKOUT');
716 INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event)
717 VALUES (16, 1, FALSE, 'Checkout of long overdue copy', 'LONGOVERDUE', 'CHECKOUT');
718 INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event)
719 VALUES (17, 1, FALSE, 'Checkout of claims-never-checked-out copy', 'CLAIMSNEVERCHECKEDOUT', 'CHECKOUT');
720
721 -- staff-usable alert types based on location
722 INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event, in_renew, at_circ)
723 VALUES (18, 1, FALSE, 'Normal checkout at circ lib', 'NORMAL', 'CHECKOUT', FALSE, TRUE);
724 INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event, in_renew, at_circ)
725 VALUES (19, 1, FALSE, 'Normal checkin at circ lib', 'NORMAL', 'CHECKIN', FALSE, TRUE);
726 INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event, in_renew, at_circ)
727 VALUES (20, 1, FALSE, 'Normal renewal at circ lib', 'NORMAL', 'CHECKIN', TRUE, TRUE);
728
729 INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event, in_renew, at_owning)
730 VALUES (21, 1, FALSE, 'Normal checkout at owning lib', 'NORMAL', 'CHECKOUT', FALSE, TRUE);
731 INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event, in_renew, at_owning)
732 VALUES (22, 1, FALSE, 'Normal checkin at owning lib', 'NORMAL', 'CHECKIN', FALSE, TRUE);
733 INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event, in_renew, at_owning)
734 VALUES (23, 1, FALSE, 'Normal renewal at owning lib', 'NORMAL', 'CHECKIN', TRUE, TRUE);
735
736
737 SELECT evergreen.upgrade_deps_block_check('1097', :eg_version);
738
739 INSERT INTO config.org_unit_setting_type
740     (name, grp, label, description, datatype)
741     VALUES
742         ('circ.copy_alerts.forgive_fines_on_lost_checkin',
743          'circ',
744          oils_i18n_gettext('circ.copy_alerts.forgive_fines_on_lost_checkin',
745             'Forgive fines when checking out a lost item and copy alert is suppressed?',
746             'coust', 'label'),
747          oils_i18n_gettext('circ.copy_alerts.forgive_fines_on_lost_checkin',
748             'Controls whether fines are automatically forgiven when checking out an '||
749             'item that has been marked as lost, and the corresponding copy alert has been '||
750             'suppressed.',
751             'coust', 'description'),
752         'bool');
753
754 INSERT INTO config.org_unit_setting_type
755     (name, grp, label, description, datatype)
756     VALUES
757         ('circ.copy_alerts.forgive_fines_on_long_overdue_checkin',
758          'circ',
759          oils_i18n_gettext('circ.copy_alerts.forgive_fines_on_long_overdue_checkin',
760             'Forgive fines when checking out a long-overdue item and copy alert is suppressed?',
761             'coust', 'label'),
762          oils_i18n_gettext('circ.copy_alerts.forgive_fines_on_lost_checkin',
763             'Controls whether fines are automatically forgiven when checking out an '||
764             'item that has been marked as lost, and the corresponding copy alert has been '||
765             'suppressed.',
766             'coust', 'description'),
767         'bool');
768
769
770 SELECT evergreen.upgrade_deps_block_check('1098', :eg_version);
771
772 \qecho Copying copy alert messages to normal checkout copy alerts...
773 INSERT INTO asset.copy_alert (alert_type, copy, note, create_staff)
774 SELECT 1, id, alert_message, 1
775 FROM asset.copy
776 WHERE alert_message IS NOT NULL
777 AND   alert_message <> '';
778
779 \qecho Copying copy alert messages to normal checkin copy alerts...
780 INSERT INTO asset.copy_alert (alert_type, copy, note, create_staff)
781 SELECT 2, id, alert_message, 1
782 FROM asset.copy
783 WHERE alert_message IS NOT NULL
784 AND   alert_message <> '';
785
786 \qecho Clearing legacy copy alert field; this may take a while
787 UPDATE asset.copy SET alert_message = NULL
788 WHERE alert_message IS NOT NULL;
789
790
791 SELECT evergreen.upgrade_deps_block_check('1099', :eg_version);
792
793 \qecho Making the following copy alert types active by default; if you
794 \qecho are not using the web staff client yet, you may want to disable
795 \qecho them.
796 \qecho  - Checkin of lost, missing, lost-and-paid, damaged, claims returned,
797 \qecho    long overdue, and claims never checked out items.
798 \qecho  - Checkout of lost, missing, lost-and-paid, damaged, claims returned,
799 \qecho    long overdue, and claims never checked out items.
800
801 UPDATE config.copy_alert_type
802 SET active = TRUE
803 WHERE id IN (4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17);
804
805
806 SELECT evergreen.upgrade_deps_block_check('1100', :eg_version);
807
808 -- NEW config.metabib_field entries
809
810 UPDATE config.metabib_field SET display_xpath = facet_xpath, display_field = TRUE WHERE id = 33;
811
812 INSERT INTO config.metabib_field (id, field_class, name, 
813     label, xpath, display_field, search_field, browse_field)
814 VALUES (
815     38, 'identifier', 'edition', 
816     oils_i18n_gettext(38, 'Edition', 'cmf', 'label'),
817     $$//mods33:mods/mods33:originInfo//mods33:edition[1]$$,
818     TRUE, TRUE, FALSE
819 );
820
821 INSERT INTO config.metabib_field (id, field_class, name, 
822     label, xpath, display_field, search_field, browse_field)
823 VALUES (
824     39, 'keyword', 'physical_description', 
825     oils_i18n_gettext(39, 'Physical Descrption', 'cmf', 'label'),
826     $$(//mods33:mods/mods33:physicalDescription/mods33:form|//mods33:mods/mods33:physicalDescription/mods33:extent|//mods33:mods/mods33:physicalDescription/mods33:reformattingQuality|//mods33:mods/mods33:physicalDescription/mods33:internetMediaType|//mods33:mods/mods33:physicalDescription/mods33:digitalOrigin)$$,
827     TRUE, TRUE, FALSE
828 );
829
830 INSERT INTO config.metabib_field (id, field_class, name, 
831     label, xpath, display_field, search_field, browse_field)
832 VALUES (
833     40, 'identifier', 'publisher', 
834     oils_i18n_gettext(40, 'Publisher', 'cmf', 'label'),
835     $$//mods33:mods/mods33:originInfo//mods33:publisher[1]$$,
836     TRUE, TRUE, FALSE
837 );
838
839 INSERT INTO config.metabib_field (id, field_class, name, 
840     label, xpath, display_field, search_field, browse_field)
841 VALUES (
842     41, 'keyword', 'abstract', 
843     oils_i18n_gettext(41, 'Abstract', 'cmf', 'label'),
844     $$//mods33:mods/mods33:abstract$$,
845     TRUE, TRUE, FALSE
846 );
847
848 INSERT INTO config.metabib_field (id, field_class, name, 
849     label, xpath, display_field, search_field, browse_field)
850 VALUES (
851     42, 'keyword', 'toc', 
852     oils_i18n_gettext(42, 'Table of Contents', 'cmf', 'label'),
853     $$//mods33:tableOfContents$$,
854     TRUE, TRUE, FALSE
855 );
856
857 INSERT INTO config.metabib_field (id, field_class, name, 
858     label, xpath, display_field, search_field, browse_field)
859 VALUES (
860     43, 'identifier', 'type_of_resource', 
861     oils_i18n_gettext(43, 'Type of Resource', 'cmf', 'label'),
862     $$//mods33:mods/mods33:typeOfResource$$,
863     TRUE, FALSE, FALSE
864 );
865
866 INSERT INTO config.metabib_field (id, field_class, name, 
867     label, xpath, display_field, search_field, browse_field)
868 VALUES (
869     44, 'identifier', 'pubdate', 
870     oils_i18n_gettext(44, 'Publication Date', 'cmf', 'label'),
871     $$//mods33:mods/mods33:originInfo//mods33:dateIssued[@encoding="marc"]|//mods33:mods/mods33:originInfo//mods33:dateIssued[1]$$,
872     TRUE, FALSE, FALSE
873 );
874
875 INSERT INTO config.metabib_field (id, field_class, name, 
876     label, xpath, display_field, search_field, browse_field)
877 VALUES (
878     46, 'keyword', 'bibliography', 
879     oils_i18n_gettext(46, 'Bibliography', 'cmf', 'label'),
880     $$//mods33:note[@type='bibliography']$$,
881     TRUE, TRUE, FALSE
882 ),(
883     47, 'keyword', 'thesis', 
884     oils_i18n_gettext(47, 'Thesis', 'cmf', 'label'),
885     $$//mods33:note[@type='thesis']$$,
886     TRUE, TRUE, FALSE
887 ),(
888     48, 'keyword', 'production_credits', 
889     oils_i18n_gettext(48, 'Creation/Production Credits', 'cmf', 'label'),
890     $$//mods33:note[@type='creation/production credits']$$,
891     TRUE, TRUE, FALSE
892 ),(
893     49, 'keyword', 'performers', 
894     oils_i18n_gettext(49, 'Performers', 'cmf', 'label'),
895     $$//mods33:note[@type='performers']$$,
896     TRUE, TRUE, FALSE
897 ),(
898     50, 'keyword', 'general_note', 
899     oils_i18n_gettext(50, 'General Note', 'cmf', 'label'),
900     $$//mods33:note[not(@type)]$$,
901     TRUE, TRUE, FALSE
902 )
903 ;
904
905 INSERT INTO config.metabib_field (id, field_class, name, format,
906     label, xpath, display_xpath, display_field, search_field, browse_field)
907 VALUES (
908     51, 'author', 'first_author', 'mods32',
909     oils_i18n_gettext(51, 'Author', 'cmf', 'label'),
910     $$//mods32:mods/mods32:name[mods32:role/mods32:roleTerm[text()='creator']][1]$$,
911     $$//*[local-name()='namePart']$$,
912     TRUE, TRUE, FALSE
913 );
914
915 INSERT INTO config.metabib_field (id, field_class, name, format,
916     label, xpath, display_xpath, display_field, search_field, browse_field)
917 VALUES (
918     52, 'identifier', 'origin_info', 'marcxml',
919     oils_i18n_gettext(52, 'Origin Info', 'cmf', 'label'),
920     $$//*[@tag='260']$$,
921     $$//*[local-name()='subfield' and contains('abc',@code)]$$,
922     TRUE, FALSE, FALSE
923 );
924
925
926 -- Modify existing config.metabib_field entries
927
928 UPDATE config.metabib_field SET display_field = TRUE WHERE id IN (
929     1,  -- seriestitle
930     11, -- subject_geographic 
931     12, -- subject_name
932     13, -- subject_temporal
933     14, -- subject_topic
934     19, -- ISSN
935     20, -- UPC
936     26  -- TCN
937 );
938
939 -- Map display field names to config.metabib_field entries
940
941 INSERT INTO config.display_field_map (name, field, multi) VALUES 
942     ('series_title',         1, TRUE),
943     ('subject_geographic',  11, TRUE),
944     ('subject_name',        12, TRUE),
945     ('subject_temporal',    13, TRUE),
946     ('subject_topic',       14, TRUE),
947     ('issn',                19, TRUE),
948     ('upc',                 20, TRUE),
949     ('tcn',                 26, FALSE),
950     ('edition',             38, FALSE),
951     ('physical_description',39, TRUE),
952     ('genre',               33, TRUE),
953     ('bibliography',        46, TRUE),
954     ('thesis',              47, TRUE),
955     ('performers',          49, TRUE),
956     ('production_credits',  48, TRUE),
957     ('general_note',        50, TRUE),
958     ('publisher',           52, FALSE),
959     ('abstract',            41, FALSE),
960     ('toc',                 42, FALSE),
961     ('type_of_resource',    43, FALSE),
962     ('pubdate',             44, FALSE)
963 ;
964
965 UPDATE config.display_field_map SET field = 51 WHERE name = 'author';
966
967 -- Add a column to wide-display-entry per well-known field
968
969 DROP VIEW IF EXISTS metabib.wide_display_entry;
970 CREATE VIEW metabib.wide_display_entry AS
971     SELECT 
972         bre.id AS source,
973         COALESCE(mcde_title.value, 'null')::TEXT AS title,
974         COALESCE(mcde_author.value, 'null')::TEXT AS author,
975         COALESCE(mcde_subject_geographic.value, 'null')::TEXT AS subject_geographic,
976         COALESCE(mcde_subject_name.value, 'null')::TEXT AS subject_name,
977         COALESCE(mcde_subject_temporal.value, 'null')::TEXT AS subject_temporal,
978         COALESCE(mcde_subject_topic.value, 'null')::TEXT AS subject_topic,
979         COALESCE(mcde_creators.value, 'null')::TEXT AS creators,
980         COALESCE(mcde_isbn.value, 'null')::TEXT AS isbn,
981         COALESCE(mcde_issn.value, 'null')::TEXT AS issn,
982         COALESCE(mcde_upc.value, 'null')::TEXT AS upc,
983         COALESCE(mcde_tcn.value, 'null')::TEXT AS tcn,
984         COALESCE(mcde_edition.value, 'null')::TEXT AS edition,
985         COALESCE(mcde_physical_description.value, 'null')::TEXT AS physical_description,
986         COALESCE(mcde_publisher.value, 'null')::TEXT AS publisher,
987         COALESCE(mcde_series_title.value, 'null')::TEXT AS series_title,
988         COALESCE(mcde_abstract.value, 'null')::TEXT AS abstract,
989         COALESCE(mcde_toc.value, 'null')::TEXT AS toc,
990         COALESCE(mcde_pubdate.value, 'null')::TEXT AS pubdate,
991         COALESCE(mcde_type_of_resource.value, 'null')::TEXT AS type_of_resource
992     FROM biblio.record_entry bre 
993     LEFT JOIN metabib.compressed_display_entry mcde_title 
994         ON (bre.id = mcde_title.source AND mcde_title.name = 'title')
995     LEFT JOIN metabib.compressed_display_entry mcde_author 
996         ON (bre.id = mcde_author.source AND mcde_author.name = 'author')
997     LEFT JOIN metabib.compressed_display_entry mcde_subject 
998         ON (bre.id = mcde_subject.source AND mcde_subject.name = 'subject')
999     LEFT JOIN metabib.compressed_display_entry mcde_subject_geographic 
1000         ON (bre.id = mcde_subject_geographic.source 
1001             AND mcde_subject_geographic.name = 'subject_geographic')
1002     LEFT JOIN metabib.compressed_display_entry mcde_subject_name 
1003         ON (bre.id = mcde_subject_name.source 
1004             AND mcde_subject_name.name = 'subject_name')
1005     LEFT JOIN metabib.compressed_display_entry mcde_subject_temporal 
1006         ON (bre.id = mcde_subject_temporal.source 
1007             AND mcde_subject_temporal.name = 'subject_temporal')
1008     LEFT JOIN metabib.compressed_display_entry mcde_subject_topic 
1009         ON (bre.id = mcde_subject_topic.source 
1010             AND mcde_subject_topic.name = 'subject_topic')
1011     LEFT JOIN metabib.compressed_display_entry mcde_creators 
1012         ON (bre.id = mcde_creators.source AND mcde_creators.name = 'creators')
1013     LEFT JOIN metabib.compressed_display_entry mcde_isbn 
1014         ON (bre.id = mcde_isbn.source AND mcde_isbn.name = 'isbn')
1015     LEFT JOIN metabib.compressed_display_entry mcde_issn 
1016         ON (bre.id = mcde_issn.source AND mcde_issn.name = 'issn')
1017     LEFT JOIN metabib.compressed_display_entry mcde_upc 
1018         ON (bre.id = mcde_upc.source AND mcde_upc.name = 'upc')
1019     LEFT JOIN metabib.compressed_display_entry mcde_tcn 
1020         ON (bre.id = mcde_tcn.source AND mcde_tcn.name = 'tcn')
1021     LEFT JOIN metabib.compressed_display_entry mcde_edition 
1022         ON (bre.id = mcde_edition.source AND mcde_edition.name = 'edition')
1023     LEFT JOIN metabib.compressed_display_entry mcde_physical_description 
1024         ON (bre.id = mcde_physical_description.source 
1025             AND mcde_physical_description.name = 'physical_description')
1026     LEFT JOIN metabib.compressed_display_entry mcde_publisher 
1027         ON (bre.id = mcde_publisher.source AND mcde_publisher.name = 'publisher')
1028     LEFT JOIN metabib.compressed_display_entry mcde_series_title 
1029         ON (bre.id = mcde_series_title.source AND mcde_series_title.name = 'series_title')
1030     LEFT JOIN metabib.compressed_display_entry mcde_abstract 
1031         ON (bre.id = mcde_abstract.source AND mcde_abstract.name = 'abstract')
1032     LEFT JOIN metabib.compressed_display_entry mcde_toc 
1033         ON (bre.id = mcde_toc.source AND mcde_toc.name = 'toc')
1034     LEFT JOIN metabib.compressed_display_entry mcde_pubdate 
1035         ON (bre.id = mcde_pubdate.source AND mcde_pubdate.name = 'pubdate')
1036     LEFT JOIN metabib.compressed_display_entry mcde_type_of_resource 
1037         ON (bre.id = mcde_type_of_resource.source 
1038             AND mcde_type_of_resource.name = 'type_of_resource')
1039 ;
1040
1041 CREATE OR REPLACE VIEW reporter.old_super_simple_record AS
1042 SELECT  r.id,
1043     r.fingerprint,
1044     r.quality,
1045     r.tcn_source,
1046     r.tcn_value,
1047     evergreen.oils_json_to_text(d.title) AS title,
1048     evergreen.oils_json_to_text(d.author) AS author,
1049     evergreen.oils_json_to_text(d.publisher) AS publisher,
1050     evergreen.oils_json_to_text(d.pubdate) AS pubdate,
1051     CASE WHEN d.isbn = 'null'
1052         THEN NULL
1053         ELSE (SELECT ARRAY(SELECT json_array_elements_text(d.isbn::JSON)))
1054     END AS isbn,
1055     CASE WHEN d.issn = 'null'
1056         THEN NULL
1057         ELSE (SELECT ARRAY(SELECT json_array_elements_text(d.issn::JSON)))
1058     END AS issn
1059   FROM  biblio.record_entry r
1060         JOIN metabib.wide_display_entry d ON (r.id = d.source);
1061
1062
1063
1064 SELECT evergreen.upgrade_deps_block_check('1101', :eg_version);
1065
1066 ALTER TABLE config.metabib_field ALTER COLUMN xpath DROP NOT NULL;
1067
1068 CREATE TABLE config.metabib_field_virtual_map (
1069     id      SERIAL  PRIMARY KEY,
1070     real    INT NOT NULL REFERENCES config.metabib_field (id),
1071     virtual INT NOT NULL REFERENCES config.metabib_field (id),
1072     weight  INT NOT NULL DEFAULT 1
1073 );
1074 COMMENT ON TABLE config.metabib_field_virtual_map IS $$
1075 Maps between real (physically extracted) index definitions
1076 and virtual (target sync, no required extraction of its own)
1077 index definitions.
1078
1079 The virtual side may not extract any data of its own, but
1080 will collect data from all of the real fields.  This reduces
1081 extraction (ingest) overhead by eliminating duplcated extraction,
1082 and allows for searching across novel combinations of fields, such
1083 as names used as either subjects or authors.  By preserving this
1084 mapping rather than defining duplicate extractions, information
1085 about the originating, "real" index definitions can be used
1086 in interesting ways, such as highlighting in search results.
1087 $$;
1088
1089 CREATE OR REPLACE VIEW metabib.combined_all_field_entry AS
1090     SELECT * FROM metabib.combined_title_field_entry
1091         UNION ALL
1092     SELECT * FROM metabib.combined_author_field_entry
1093         UNION ALL
1094     SELECT * FROM metabib.combined_subject_field_entry
1095         UNION ALL
1096     SELECT * FROM metabib.combined_keyword_field_entry
1097         UNION ALL
1098     SELECT * FROM metabib.combined_identifier_field_entry
1099         UNION ALL
1100     SELECT * FROM metabib.combined_series_field_entry;
1101
1102
1103 CREATE OR REPLACE FUNCTION biblio.extract_metabib_field_entry (
1104     rid BIGINT,
1105     default_joiner TEXT,
1106     field_types TEXT[],
1107     only_fields INT[]
1108 ) RETURNS SETOF metabib.field_entry_template AS $func$
1109 DECLARE
1110     bib     biblio.record_entry%ROWTYPE;
1111     idx     config.metabib_field%ROWTYPE;
1112     xfrm        config.xml_transform%ROWTYPE;
1113     prev_xfrm   TEXT;
1114     transformed_xml TEXT;
1115     xml_node    TEXT;
1116     xml_node_list   TEXT[];
1117     facet_text  TEXT;
1118     display_text TEXT;
1119     browse_text TEXT;
1120     sort_value  TEXT;
1121     raw_text    TEXT;
1122     curr_text   TEXT;
1123     joiner      TEXT := default_joiner; -- XXX will index defs supply a joiner?
1124     authority_text TEXT;
1125     authority_link BIGINT;
1126     output_row  metabib.field_entry_template%ROWTYPE;
1127     process_idx BOOL;
1128 BEGIN
1129
1130     -- Start out with no field-use bools set
1131     output_row.browse_field = FALSE;
1132     output_row.facet_field = FALSE;
1133     output_row.display_field = FALSE;
1134     output_row.search_field = FALSE;
1135
1136     -- Get the record
1137     SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
1138
1139     -- Loop over the indexing entries
1140     FOR idx IN SELECT * FROM config.metabib_field WHERE id = ANY (only_fields) ORDER BY format LOOP
1141         CONTINUE WHEN idx.xpath IS NULL OR idx.xpath = ''; -- pure virtual field
1142
1143         process_idx := FALSE;
1144         IF idx.display_field AND 'display' = ANY (field_types) THEN process_idx = TRUE; END IF;
1145         IF idx.browse_field AND 'browse' = ANY (field_types) THEN process_idx = TRUE; END IF;
1146         IF idx.search_field AND 'search' = ANY (field_types) THEN process_idx = TRUE; END IF;
1147         IF idx.facet_field AND 'facet' = ANY (field_types) THEN process_idx = TRUE; END IF;
1148         CONTINUE WHEN process_idx = FALSE; -- disabled for all types
1149
1150         joiner := COALESCE(idx.joiner, default_joiner);
1151
1152         SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
1153
1154         -- See if we can skip the XSLT ... it's expensive
1155         IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
1156             -- Can't skip the transform
1157             IF xfrm.xslt <> '---' THEN
1158                 transformed_xml := oils_xslt_process(bib.marc,xfrm.xslt);
1159             ELSE
1160                 transformed_xml := bib.marc;
1161             END IF;
1162
1163             prev_xfrm := xfrm.name;
1164         END IF;
1165
1166         xml_node_list := oils_xpath( idx.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
1167
1168         raw_text := NULL;
1169         FOR xml_node IN SELECT x FROM unnest(xml_node_list) AS x LOOP
1170             CONTINUE WHEN xml_node !~ E'^\\s*<';
1171
1172             -- XXX much of this should be moved into oils_xpath_string...
1173             curr_text := ARRAY_TO_STRING(evergreen.array_remove_item_by_value(evergreen.array_remove_item_by_value(
1174                 oils_xpath( '//text()', -- get the content of all the nodes within the main selected node
1175                     REGEXP_REPLACE( xml_node, E'\\s+', ' ', 'g' ) -- Translate adjacent whitespace to a single space
1176                 ), ' '), ''),  -- throw away morally empty (bankrupt?) strings
1177                 joiner
1178             );
1179
1180             CONTINUE WHEN curr_text IS NULL OR curr_text = '';
1181
1182             IF raw_text IS NOT NULL THEN
1183                 raw_text := raw_text || joiner;
1184             END IF;
1185
1186             raw_text := COALESCE(raw_text,'') || curr_text;
1187
1188             -- autosuggest/metabib.browse_entry
1189             IF idx.browse_field THEN
1190
1191                 IF idx.browse_xpath IS NOT NULL AND idx.browse_xpath <> '' THEN
1192                     browse_text := oils_xpath_string( idx.browse_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
1193                 ELSE
1194                     browse_text := curr_text;
1195                 END IF;
1196
1197                 IF idx.browse_sort_xpath IS NOT NULL AND
1198                     idx.browse_sort_xpath <> '' THEN
1199
1200                     sort_value := oils_xpath_string(
1201                         idx.browse_sort_xpath, xml_node, joiner,
1202                         ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
1203                     );
1204                 ELSE
1205                     sort_value := browse_text;
1206                 END IF;
1207
1208                 output_row.field_class = idx.field_class;
1209                 output_row.field = idx.id;
1210                 output_row.source = rid;
1211                 output_row.value = BTRIM(REGEXP_REPLACE(browse_text, E'\\s+', ' ', 'g'));
1212                 output_row.sort_value :=
1213                     public.naco_normalize(sort_value);
1214
1215                 output_row.authority := NULL;
1216
1217                 IF idx.authority_xpath IS NOT NULL AND idx.authority_xpath <> '' THEN
1218                     authority_text := oils_xpath_string(
1219                         idx.authority_xpath, xml_node, joiner,
1220                         ARRAY[
1221                             ARRAY[xfrm.prefix, xfrm.namespace_uri],
1222                             ARRAY['xlink','http://www.w3.org/1999/xlink']
1223                         ]
1224                     );
1225
1226                     IF authority_text ~ '^\d+$' THEN
1227                         authority_link := authority_text::BIGINT;
1228                         PERFORM * FROM authority.record_entry WHERE id = authority_link;
1229                         IF FOUND THEN
1230                             output_row.authority := authority_link;
1231                         END IF;
1232                     END IF;
1233
1234                 END IF;
1235
1236                 output_row.browse_field = TRUE;
1237                 -- Returning browse rows with search_field = true for search+browse
1238                 -- configs allows us to retain granularity of being able to search
1239                 -- browse fields with "starts with" type operators (for example, for
1240                 -- titles of songs in music albums)
1241                 IF idx.search_field THEN
1242                     output_row.search_field = TRUE;
1243                 END IF;
1244                 RETURN NEXT output_row;
1245                 output_row.browse_field = FALSE;
1246                 output_row.search_field = FALSE;
1247                 output_row.sort_value := NULL;
1248             END IF;
1249
1250             -- insert raw node text for faceting
1251             IF idx.facet_field THEN
1252
1253                 IF idx.facet_xpath IS NOT NULL AND idx.facet_xpath <> '' THEN
1254                     facet_text := oils_xpath_string( idx.facet_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
1255                 ELSE
1256                     facet_text := curr_text;
1257                 END IF;
1258
1259                 output_row.field_class = idx.field_class;
1260                 output_row.field = -1 * idx.id;
1261                 output_row.source = rid;
1262                 output_row.value = BTRIM(REGEXP_REPLACE(facet_text, E'\\s+', ' ', 'g'));
1263
1264                 output_row.facet_field = TRUE;
1265                 RETURN NEXT output_row;
1266                 output_row.facet_field = FALSE;
1267             END IF;
1268
1269             -- insert raw node text for display
1270             IF idx.display_field THEN
1271
1272                 IF idx.display_xpath IS NOT NULL AND idx.display_xpath <> '' THEN
1273                     display_text := oils_xpath_string( idx.display_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
1274                 ELSE
1275                     display_text := curr_text;
1276                 END IF;
1277
1278                 output_row.field_class = idx.field_class;
1279                 output_row.field = -1 * idx.id;
1280                 output_row.source = rid;
1281                 output_row.value = BTRIM(REGEXP_REPLACE(display_text, E'\\s+', ' ', 'g'));
1282
1283                 output_row.display_field = TRUE;
1284                 RETURN NEXT output_row;
1285                 output_row.display_field = FALSE;
1286             END IF;
1287
1288         END LOOP;
1289
1290         CONTINUE WHEN raw_text IS NULL OR raw_text = '';
1291
1292         -- insert combined node text for searching
1293         IF idx.search_field THEN
1294             output_row.field_class = idx.field_class;
1295             output_row.field = idx.id;
1296             output_row.source = rid;
1297             output_row.value = BTRIM(REGEXP_REPLACE(raw_text, E'\\s+', ' ', 'g'));
1298
1299             output_row.search_field = TRUE;
1300             RETURN NEXT output_row;
1301             output_row.search_field = FALSE;
1302         END IF;
1303
1304     END LOOP;
1305
1306 END;
1307 $func$ LANGUAGE PLPGSQL;
1308
1309 CREATE OR REPLACE FUNCTION metabib.update_combined_index_vectors(bib_id BIGINT) RETURNS VOID AS $func$
1310 DECLARE
1311     rdata       TSVECTOR;
1312     vclass      TEXT;
1313     vfield      INT;
1314     rfields     INT[];
1315 BEGIN
1316     DELETE FROM metabib.combined_keyword_field_entry WHERE record = bib_id;
1317     INSERT INTO metabib.combined_keyword_field_entry(record, metabib_field, index_vector)
1318         SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
1319         FROM metabib.keyword_field_entry WHERE source = bib_id GROUP BY field;
1320     INSERT INTO metabib.combined_keyword_field_entry(record, metabib_field, index_vector)
1321         SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
1322         FROM metabib.keyword_field_entry WHERE source = bib_id;
1323
1324     DELETE FROM metabib.combined_title_field_entry WHERE record = bib_id;
1325     INSERT INTO metabib.combined_title_field_entry(record, metabib_field, index_vector)
1326         SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
1327         FROM metabib.title_field_entry WHERE source = bib_id GROUP BY field;
1328     INSERT INTO metabib.combined_title_field_entry(record, metabib_field, index_vector)
1329         SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
1330         FROM metabib.title_field_entry WHERE source = bib_id;
1331
1332     DELETE FROM metabib.combined_author_field_entry WHERE record = bib_id;
1333     INSERT INTO metabib.combined_author_field_entry(record, metabib_field, index_vector)
1334         SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
1335         FROM metabib.author_field_entry WHERE source = bib_id GROUP BY field;
1336     INSERT INTO metabib.combined_author_field_entry(record, metabib_field, index_vector)
1337         SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
1338         FROM metabib.author_field_entry WHERE source = bib_id;
1339
1340     DELETE FROM metabib.combined_subject_field_entry WHERE record = bib_id;
1341     INSERT INTO metabib.combined_subject_field_entry(record, metabib_field, index_vector)
1342         SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
1343         FROM metabib.subject_field_entry WHERE source = bib_id GROUP BY field;
1344     INSERT INTO metabib.combined_subject_field_entry(record, metabib_field, index_vector)
1345         SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
1346         FROM metabib.subject_field_entry WHERE source = bib_id;
1347
1348     DELETE FROM metabib.combined_series_field_entry WHERE record = bib_id;
1349     INSERT INTO metabib.combined_series_field_entry(record, metabib_field, index_vector)
1350         SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
1351         FROM metabib.series_field_entry WHERE source = bib_id GROUP BY field;
1352     INSERT INTO metabib.combined_series_field_entry(record, metabib_field, index_vector)
1353         SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
1354         FROM metabib.series_field_entry WHERE source = bib_id;
1355
1356     DELETE FROM metabib.combined_identifier_field_entry WHERE record = bib_id;
1357     INSERT INTO metabib.combined_identifier_field_entry(record, metabib_field, index_vector)
1358         SELECT bib_id, field, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
1359         FROM metabib.identifier_field_entry WHERE source = bib_id GROUP BY field;
1360     INSERT INTO metabib.combined_identifier_field_entry(record, metabib_field, index_vector)
1361         SELECT bib_id, NULL, strip(COALESCE(string_agg(index_vector::TEXT,' '),'')::tsvector)
1362         FROM metabib.identifier_field_entry WHERE source = bib_id;
1363
1364     -- For each virtual def, gather the data from the combined real field
1365     -- entries and append it to the virtual combined entry.
1366     FOR vfield, rfields IN SELECT virtual, ARRAY_AGG(real)  FROM config.metabib_field_virtual_map GROUP BY virtual LOOP
1367         SELECT  field_class INTO vclass
1368           FROM  config.metabib_field
1369           WHERE id = vfield;
1370
1371         SELECT  string_agg(index_vector::TEXT,' ')::tsvector INTO rdata
1372           FROM  metabib.combined_all_field_entry
1373           WHERE record = bib_id
1374                 AND metabib_field = ANY (rfields);
1375
1376         BEGIN -- I cannot wait for INSERT ON CONFLICT ... 9.5, though
1377             EXECUTE $$
1378                 INSERT INTO metabib.combined_$$ || vclass || $$_field_entry
1379                     (record, metabib_field, index_vector) VALUES ($1, $2, $3)
1380             $$ USING bib_id, vfield, rdata;
1381         EXCEPTION WHEN unique_violation THEN
1382             EXECUTE $$
1383                 UPDATE  metabib.combined_$$ || vclass || $$_field_entry
1384                   SET   index_vector = index_vector || $3
1385                   WHERE record = $1
1386                         AND metabib_field = $2
1387             $$ USING bib_id, vfield, rdata;
1388         WHEN OTHERS THEN
1389             -- ignore and move on
1390         END;
1391     END LOOP;
1392 END;
1393 $func$ LANGUAGE PLPGSQL;
1394
1395 CREATE OR REPLACE VIEW search.best_tsconfig AS
1396     SELECT  m.id AS id,
1397             COALESCE(f.ts_config, c.ts_config, 'simple') AS ts_config
1398       FROM  config.metabib_field m
1399             LEFT JOIN config.metabib_class_ts_map c ON (c.field_class = m.field_class AND c.index_weight = 'C')
1400             LEFT JOIN config.metabib_field_ts_map f ON (f.metabib_field = m.id AND f.index_weight = 'C');
1401
1402 CREATE TYPE search.highlight_result AS ( id BIGINT, source BIGINT, field INT, value TEXT, highlight TEXT );
1403
1404 CREATE OR REPLACE FUNCTION search.highlight_display_fields_impl(
1405     rid         BIGINT,
1406     tsq         TEXT,
1407     field_list  INT[] DEFAULT '{}'::INT[],
1408     css_class   TEXT DEFAULT 'oils_SH',
1409     hl_all      BOOL DEFAULT TRUE,
1410     minwords    INT DEFAULT 5,
1411     maxwords    INT DEFAULT 25,
1412     shortwords  INT DEFAULT 0,
1413     maxfrags    INT DEFAULT 0,
1414     delimiter   TEXT DEFAULT ' ... '
1415 ) RETURNS SETOF search.highlight_result AS $f$
1416 DECLARE
1417     opts            TEXT := '';
1418     v_css_class     TEXT := css_class;
1419     v_delimiter     TEXT := delimiter;
1420     v_field_list    INT[] := field_list;
1421     hl_query        TEXT;
1422 BEGIN
1423     IF v_delimiter LIKE $$%'%$$ OR v_delimiter LIKE '%"%' THEN --"
1424         v_delimiter := ' ... ';
1425     END IF;
1426
1427     IF NOT hl_all THEN
1428         opts := opts || 'MinWords=' || minwords;
1429         opts := opts || ', MaxWords=' || maxwords;
1430         opts := opts || ', ShortWords=' || shortwords;
1431         opts := opts || ', MaxFragments=' || maxfrags;
1432         opts := opts || ', FragmentDelimiter="' || delimiter || '"';
1433     ELSE
1434         opts := opts || 'HighlightAll=TRUE';
1435     END IF;
1436
1437     IF v_css_class LIKE $$%'%$$ OR v_css_class LIKE '%"%' THEN -- "
1438         v_css_class := 'oils_SH';
1439     END IF;
1440
1441     opts := opts || $$, StopSel=</b>, StartSel="<b class='$$ || v_css_class; -- "
1442
1443     IF v_field_list = '{}'::INT[] THEN
1444         SELECT ARRAY_AGG(id) INTO v_field_list FROM config.metabib_field WHERE display_field;
1445     END IF;
1446
1447     hl_query := $$
1448         SELECT  de.id,
1449                 de.source,
1450                 de.field,
1451                 de.value AS value,
1452                 ts_headline(
1453                     ts_config::REGCONFIG,
1454                     evergreen.escape_for_html(de.value),
1455                     $$ || quote_literal(tsq) || $$,
1456                     $1 || ' ' || mf.field_class || ' ' || mf.name || $xx$'>"$xx$ -- "'
1457                 ) AS highlight
1458           FROM  metabib.display_entry de
1459                 JOIN config.metabib_field mf ON (mf.id = de.field)
1460                 JOIN search.best_tsconfig t ON (t.id = de.field)
1461           WHERE de.source = $2
1462                 AND field = ANY ($3)
1463           ORDER BY de.id;$$;
1464
1465     RETURN QUERY EXECUTE hl_query USING opts, rid, v_field_list;
1466 END;
1467 $f$ LANGUAGE PLPGSQL;
1468
1469 CREATE OR REPLACE FUNCTION evergreen.escape_for_html (TEXT) RETURNS TEXT AS $$
1470     SELECT  regexp_replace(
1471                 regexp_replace(
1472                     regexp_replace(
1473                         $1,
1474                         '&',
1475                         '&amp;',
1476                         'g'
1477                     ),
1478                     '<',
1479                     '&lt;',
1480                     'g'
1481                 ),
1482                 '>',
1483                 '&gt;',
1484                 'g'
1485             );
1486 $$ LANGUAGE SQL IMMUTABLE LEAKPROOF STRICT COST 10;
1487
1488 CREATE OR REPLACE FUNCTION search.highlight_display_fields(
1489     rid         BIGINT,
1490     tsq_map     TEXT, -- { '(a | b) & c' => '1,2,3,4', ...}
1491     css_class   TEXT DEFAULT 'oils_SH',
1492     hl_all      BOOL DEFAULT TRUE,
1493     minwords    INT DEFAULT 5,
1494     maxwords    INT DEFAULT 25,
1495     shortwords  INT DEFAULT 0,
1496     maxfrags    INT DEFAULT 0,
1497     delimiter   TEXT DEFAULT ' ... '
1498 ) RETURNS SETOF search.highlight_result AS $f$
1499 DECLARE
1500     tsq_hstore  HSTORE;
1501     tsq         TEXT;
1502     fields      TEXT;
1503     afields     INT[];
1504     seen        INT[];
1505 BEGIN
1506
1507     IF (tsq_map ILIKE 'hstore%') THEN
1508         EXECUTE 'SELECT ' || tsq_map INTO tsq_hstore;
1509     ELSE
1510         tsq_hstore := tsq_map::HSTORE;
1511     END IF;
1512     
1513     FOR tsq, fields IN SELECT key, value FROM each(tsq_hstore::HSTORE) LOOP
1514         SELECT  ARRAY_AGG(unnest::INT) INTO afields
1515           FROM  unnest(regexp_split_to_array(fields,','));
1516         seen := seen || afields;
1517
1518         RETURN QUERY
1519             SELECT * FROM search.highlight_display_fields_impl(
1520                 rid, tsq, afields, css_class, hl_all,minwords,
1521                 maxwords, shortwords, maxfrags, delimiter
1522             );
1523     END LOOP;
1524
1525     RETURN QUERY
1526         SELECT  id,
1527                 source,
1528                 field,
1529                 value,
1530                 value AS highlight
1531           FROM  metabib.display_entry
1532           WHERE source = rid
1533                 AND NOT (field = ANY (seen));
1534 END;
1535 $f$ LANGUAGE PLPGSQL ROWS 10;
1536  
1537 CREATE OR REPLACE FUNCTION metabib.remap_metarecord_for_bib(
1538     bib_id bigint,
1539     fp text,
1540     bib_is_deleted boolean DEFAULT false,
1541     retain_deleted boolean DEFAULT false
1542 ) RETURNS bigint AS $function$
1543 DECLARE
1544     new_mapping     BOOL := TRUE;
1545     source_count    INT;
1546     old_mr          BIGINT;
1547     tmp_mr          metabib.metarecord%ROWTYPE;
1548     deleted_mrs     BIGINT[];
1549 BEGIN
1550
1551     -- We need to make sure we're not a deleted master record of an MR
1552     IF bib_is_deleted THEN
1553         IF NOT retain_deleted THEN -- Go away for any MR that we're master of, unless retained
1554             DELETE FROM metabib.metarecord_source_map WHERE source = bib_id;
1555         END IF;
1556
1557         FOR old_mr IN SELECT id FROM metabib.metarecord WHERE master_record = bib_id LOOP
1558
1559             -- Now, are there any more sources on this MR?
1560             SELECT COUNT(*) INTO source_count FROM metabib.metarecord_source_map WHERE metarecord = old_mr;
1561
1562             IF source_count = 0 AND NOT retain_deleted THEN -- No other records
1563                 deleted_mrs := ARRAY_APPEND(deleted_mrs, old_mr); -- Just in case...
1564                 DELETE FROM metabib.metarecord WHERE id = old_mr;
1565
1566             ELSE -- indeed there are. Update it with a null cache and recalcualated master record
1567                 UPDATE  metabib.metarecord
1568                   SET   mods = NULL,
1569                         master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = fp AND NOT deleted ORDER BY quality DESC LIMIT 1)
1570                   WHERE id = old_mr;
1571             END IF;
1572         END LOOP;
1573
1574     ELSE -- insert or update
1575
1576         FOR tmp_mr IN SELECT m.* FROM metabib.metarecord m JOIN metabib.metarecord_source_map s ON (s.metarecord = m.id) WHERE s.source = bib_id LOOP
1577
1578             -- Find the first fingerprint-matching
1579             IF old_mr IS NULL AND fp = tmp_mr.fingerprint THEN
1580                 old_mr := tmp_mr.id;
1581                 new_mapping := FALSE;
1582
1583             ELSE -- Our fingerprint changed ... maybe remove the old MR
1584                 DELETE FROM metabib.metarecord_source_map WHERE metarecord = tmp_mr.id AND source = bib_id; -- remove the old source mapping
1585                 SELECT COUNT(*) INTO source_count FROM metabib.metarecord_source_map WHERE metarecord = tmp_mr.id;
1586                 IF source_count = 0 THEN -- No other records
1587                     deleted_mrs := ARRAY_APPEND(deleted_mrs, tmp_mr.id);
1588                     DELETE FROM metabib.metarecord WHERE id = tmp_mr.id;
1589                 END IF;
1590             END IF;
1591
1592         END LOOP;
1593
1594         -- we found no suitable, preexisting MR based on old source maps
1595         IF old_mr IS NULL THEN
1596             SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = fp; -- is there one for our current fingerprint?
1597
1598             IF old_mr IS NULL THEN -- nope, create one and grab its id
1599                 INSERT INTO metabib.metarecord ( fingerprint, master_record ) VALUES ( fp, bib_id );
1600                 SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = fp;
1601
1602             ELSE -- indeed there is. update it with a null cache and recalcualated master record
1603                 UPDATE  metabib.metarecord
1604                   SET   mods = NULL,
1605                         master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = fp AND NOT deleted ORDER BY quality DESC LIMIT 1)
1606                   WHERE id = old_mr;
1607             END IF;
1608
1609         ELSE -- there was one we already attached to, update its mods cache and master_record
1610             UPDATE  metabib.metarecord
1611               SET   mods = NULL,
1612                     master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = fp AND NOT deleted ORDER BY quality DESC LIMIT 1)
1613               WHERE id = old_mr;
1614         END IF;
1615
1616         IF new_mapping THEN
1617             INSERT INTO metabib.metarecord_source_map (metarecord, source) VALUES (old_mr, bib_id); -- new source mapping
1618         END IF;
1619
1620     END IF;
1621
1622     IF ARRAY_UPPER(deleted_mrs,1) > 0 THEN
1623         UPDATE action.hold_request SET target = old_mr WHERE target IN ( SELECT unnest(deleted_mrs) ) AND hold_type = 'M'; -- if we had to delete any MRs above, make sure their holds are moved
1624     END IF;
1625
1626     RETURN old_mr;
1627
1628 END;
1629 $function$ LANGUAGE plpgsql;
1630
1631 CREATE OR REPLACE FUNCTION evergreen.marc_to (marc text, xfrm text) RETURNS TEXT AS $$
1632     SELECT evergreen.xml_pretty_print(xslt_process($1,xslt)::XML)::TEXT FROM config.xml_transform WHERE name = $2;
1633 $$ LANGUAGE SQL;
1634
1635
1636
1637 SELECT evergreen.upgrade_deps_block_check('1102', :eg_version);
1638
1639 update config.xml_transform set xslt = $XXXX$<?xml version="1.0" encoding="UTF-8"?>
1640 <xsl:stylesheet xmlns="http://www.loc.gov/mods/v3" xmlns:marc="http://www.loc.gov/MARC21/slim" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" exclude-result-prefixes="xlink marc" version="1.0">
1641         <xsl:output encoding="UTF-8" indent="yes" method="xml"/>
1642 <!--
1643 Revision 1.14 - Fixed template isValid and fields 010, 020, 022, 024, 028, and 037 to output additional identifier elements
1644   with corresponding @type and @invalid eq 'yes' when subfields z or y (in the case of 022) exist in the MARCXML ::: 2007/01/04 17:35:20 cred
1645
1646 Revision 1.13 - Changed order of output under cartographics to reflect schema  2006/11/28 tmee
1647
1648 Revision 1.12 - Updated to reflect MODS 3.2 Mapping  2006/10/11 tmee
1649
1650 Revision 1.11 - The attribute objectPart moved from <languageTerm> to <language>
1651       2006/04/08  jrad
1652
1653 Revision 1.10 MODS 3.1 revisions to language and classification elements
1654                                 (plus ability to find marc:collection embedded in wrapper elements such as SRU zs: wrappers)
1655                                 2006/02/06  ggar
1656
1657 Revision 1.9 subfield $y was added to field 242 2004/09/02 10:57 jrad
1658
1659 Revision 1.8 Subject chopPunctuation expanded and attribute fixes 2004/08/12 jrad
1660
1661 Revision 1.7 2004/03/25 08:29 jrad
1662
1663 Revision 1.6 various validation fixes 2004/02/20 ntra
1664
1665 Revision 1.5  2003/10/02 16:18:58  ntra
1666 MODS2 to MODS3 updates, language unstacking and
1667 de-duping, chopPunctuation expanded
1668
1669 Revision 1.3  2003/04/03 00:07:19  ntra
1670 Revision 1.3 Additional Changes not related to MODS Version 2.0 by ntra
1671
1672 Revision 1.2  2003/03/24 19:37:42  ckeith
1673 Added Log Comment
1674
1675 -->
1676         <xsl:template match="/">
1677                 <xsl:choose>
1678                         <xsl:when test="//marc:collection">
1679                                 <modsCollection xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.loc.gov/mods/v3 http://www.loc.gov/standards/mods/v3/mods-3-2.xsd">
1680                                         <xsl:for-each select="//marc:collection/marc:record">
1681                                                 <mods version="3.2">
1682                                                         <xsl:call-template name="marcRecord"/>
1683                                                 </mods>
1684                                         </xsl:for-each>
1685                                 </modsCollection>
1686                         </xsl:when>
1687                         <xsl:otherwise>
1688                                 <mods xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="3.2" xsi:schemaLocation="http://www.loc.gov/mods/v3 http://www.loc.gov/standards/mods/v3/mods-3-2.xsd">
1689                                         <xsl:for-each select="//marc:record">
1690                                                 <xsl:call-template name="marcRecord"/>
1691                                         </xsl:for-each>
1692                                 </mods>
1693                         </xsl:otherwise>
1694                 </xsl:choose>
1695         </xsl:template>
1696         <xsl:template name="marcRecord">
1697                 <xsl:variable name="leader" select="marc:leader"/>
1698                 <xsl:variable name="leader6" select="substring($leader,7,1)"/>
1699                 <xsl:variable name="leader7" select="substring($leader,8,1)"/>
1700                 <xsl:variable name="controlField008" select="marc:controlfield[@tag='008']"/>
1701                 <xsl:variable name="typeOf008">
1702                         <xsl:choose>
1703                                 <xsl:when test="$leader6='a'">
1704                                         <xsl:choose>
1705                                                 <xsl:when test="$leader7='a' or $leader7='c' or $leader7='d' or $leader7='m'">BK</xsl:when>
1706                                                 <xsl:when test="$leader7='b' or $leader7='i' or $leader7='s'">SE</xsl:when>
1707                                         </xsl:choose>
1708                                 </xsl:when>
1709                                 <xsl:when test="$leader6='t'">BK</xsl:when>
1710                                 <xsl:when test="$leader6='p'">MM</xsl:when>
1711                                 <xsl:when test="$leader6='m'">CF</xsl:when>
1712                                 <xsl:when test="$leader6='e' or $leader6='f'">MP</xsl:when>
1713                                 <xsl:when test="$leader6='g' or $leader6='k' or $leader6='o' or $leader6='r'">VM</xsl:when>
1714                                 <xsl:when test="$leader6='c' or $leader6='d' or $leader6='i' or $leader6='j'">MU</xsl:when>
1715                         </xsl:choose>
1716                 </xsl:variable>
1717                 <xsl:for-each select="marc:datafield[@tag='245']">
1718                         <titleInfo>
1719                                 <xsl:variable name="title">
1720                                         <xsl:choose>
1721                                                 <xsl:when test="marc:subfield[@code='b']">
1722                                                         <xsl:call-template name="specialSubfieldSelect">
1723                                                                 <xsl:with-param name="axis">b</xsl:with-param>
1724                                                                 <xsl:with-param name="beforeCodes">afgk</xsl:with-param>
1725                                                         </xsl:call-template>
1726                                                 </xsl:when>
1727                                                 <xsl:otherwise>
1728                                                         <xsl:call-template name="subfieldSelect">
1729                                                                 <xsl:with-param name="codes">abfgk</xsl:with-param>
1730                                                         </xsl:call-template>
1731                                                 </xsl:otherwise>
1732                                         </xsl:choose>
1733                                 </xsl:variable>
1734                                 <xsl:variable name="titleChop">
1735                                         <xsl:call-template name="chopPunctuation">
1736                                                 <xsl:with-param name="chopString">
1737                                                         <xsl:value-of select="$title"/>
1738                                                 </xsl:with-param>
1739                                                 <xsl:with-param name="punctuation">
1740                                                     <xsl:text>,;/ </xsl:text>
1741                                                 </xsl:with-param>
1742                                         </xsl:call-template>
1743                                 </xsl:variable>
1744                                 <xsl:choose>
1745                                         <xsl:when test="@ind2>0">
1746                                                 <nonSort>
1747                                                         <xsl:value-of select="substring($titleChop,1,@ind2)"/>
1748                                                 </nonSort>
1749                                                 <title>
1750                                                         <xsl:value-of select="substring($titleChop,@ind2+1)"/>
1751                                                 </title>
1752                                         </xsl:when>
1753                                         <xsl:otherwise>
1754                                                 <title>
1755                                                         <xsl:value-of select="$titleChop"/>
1756                                                 </title>
1757                                         </xsl:otherwise>
1758                                 </xsl:choose>
1759                                 <xsl:if test="marc:subfield[@code='b']">
1760                                         <subTitle>
1761                                                 <xsl:call-template name="chopPunctuation">
1762                                                         <xsl:with-param name="chopString">
1763                                                                 <xsl:call-template name="specialSubfieldSelect">
1764                                                                         <xsl:with-param name="axis">b</xsl:with-param>
1765                                                                         <xsl:with-param name="anyCodes">b</xsl:with-param>
1766                                                                         <xsl:with-param name="afterCodes">afgk</xsl:with-param>
1767                                                                 </xsl:call-template>
1768                                                         </xsl:with-param>
1769                                                 </xsl:call-template>
1770                                         </subTitle>
1771                                 </xsl:if>
1772                                 <xsl:call-template name="part"></xsl:call-template>
1773                         </titleInfo>
1774                         <!-- A form of title that ignores non-filing characters; useful
1775                                  for not converting "L'Oreal" into "L' Oreal" at index time -->
1776                         <titleNonfiling>
1777                                 <title>
1778                                         <xsl:call-template name="chopPunctuation">
1779                                                 <xsl:with-param name="chopString">
1780                                                         <xsl:call-template name="subfieldSelect">
1781                                                                 <xsl:with-param name="codes">abfgk</xsl:with-param>
1782                                                         </xsl:call-template>
1783                                                 </xsl:with-param>
1784                                         </xsl:call-template>
1785                                 </title>
1786                                 <xsl:call-template name="part"></xsl:call-template>
1787                         </titleNonfiling>
1788                         <!-- hybrid of titleInfo and titleNonfiling which will give us a preformatted string (for punctuation)
1789                                  but also keep the nonSort stuff in a separate field (for sorting) -->
1790                         <titleBrowse>
1791                                 <xsl:variable name="titleBrowseChop">
1792                                         <xsl:call-template name="chopPunctuation">
1793                                                 <xsl:with-param name="chopString">
1794                                                         <xsl:call-template name="subfieldSelect">
1795                                                                 <xsl:with-param name="codes">abfgk</xsl:with-param>
1796                                                         </xsl:call-template>
1797                                                 </xsl:with-param>
1798                                         </xsl:call-template>
1799                                 </xsl:variable>
1800                                 <xsl:choose>
1801                                         <xsl:when test="@ind2>0">
1802                                                 <nonSort>
1803                                                         <xsl:value-of select="substring($titleBrowseChop,1,@ind2)"/>
1804                                                 </nonSort>
1805                                                 <title>
1806                                                         <xsl:value-of select="substring($titleBrowseChop,@ind2+1)"/>
1807                                                 </title>
1808                                         </xsl:when>
1809                                         <xsl:otherwise>
1810                                                 <title>
1811                                                         <xsl:value-of select="$titleBrowseChop"/>
1812                                                 </title>
1813                                         </xsl:otherwise>
1814                                 </xsl:choose>
1815                                 <xsl:call-template name="part"></xsl:call-template>
1816                         </titleBrowse>
1817                 </xsl:for-each>
1818                 <xsl:for-each select="marc:datafield[@tag='210']">
1819                         <titleInfo type="abbreviated">
1820                                 <title>
1821                                         <xsl:call-template name="chopPunctuation">
1822                                                 <xsl:with-param name="chopString">
1823                                                         <xsl:call-template name="subfieldSelect">
1824                                                                 <xsl:with-param name="codes">a</xsl:with-param>
1825                                                         </xsl:call-template>
1826                                                 </xsl:with-param>
1827                                         </xsl:call-template>
1828                                 </title>
1829                                 <xsl:call-template name="subtitle"/>
1830                         </titleInfo>
1831                 </xsl:for-each>
1832                 <xsl:for-each select="marc:datafield[@tag='242']">
1833                         <xsl:variable name="titleChop">
1834                                 <xsl:call-template name="chopPunctuation">
1835                                         <xsl:with-param name="chopString">
1836                                                 <xsl:call-template name="subfieldSelect">
1837                                                         <!-- 1/04 removed $h, b -->
1838                                                         <xsl:with-param name="codes">a</xsl:with-param>
1839                                                 </xsl:call-template>
1840                                         </xsl:with-param>
1841                                 </xsl:call-template>
1842                         </xsl:variable>
1843                         <titleInfo type="translated">
1844                                 <!--09/01/04 Added subfield $y-->
1845                                 <xsl:for-each select="marc:subfield[@code='y']">
1846                                         <xsl:attribute name="lang">
1847                                                 <xsl:value-of select="text()"/>
1848                                         </xsl:attribute>
1849                                 </xsl:for-each>
1850                                 <title>
1851                                         <xsl:value-of select="$titleChop" />
1852                                 </title>
1853                                 <!-- 1/04 fix -->
1854                                 <xsl:call-template name="subtitle"/>
1855                                 <xsl:call-template name="part"/>
1856                         </titleInfo>
1857                         <titleInfo type="translated-nfi">
1858                                 <xsl:for-each select="marc:subfield[@code='y']">
1859                                         <xsl:attribute name="lang">
1860                                                 <xsl:value-of select="text()"/>
1861                                         </xsl:attribute>
1862                                 </xsl:for-each>
1863                                 <xsl:choose>
1864                                         <xsl:when test="@ind2>0">
1865                                                 <nonSort>
1866                                                         <xsl:value-of select="substring($titleChop,1,@ind2)"/>
1867                                                 </nonSort>
1868                                                 <title>
1869                                                         <xsl:value-of select="substring($titleChop,@ind2+1)"/>
1870                                                 </title>
1871                                         </xsl:when>
1872                                         <xsl:otherwise>
1873                                                 <title>
1874                                                         <xsl:value-of select="$titleChop" />
1875                                                 </title>
1876                                         </xsl:otherwise>
1877                                 </xsl:choose>
1878                                 <xsl:call-template name="subtitle"/>
1879                                 <xsl:call-template name="part"/>
1880                         </titleInfo>
1881                 </xsl:for-each>
1882                 <xsl:for-each select="marc:datafield[@tag='246']">
1883                         <titleInfo type="alternative">
1884                                 <xsl:for-each select="marc:subfield[@code='i']">
1885                                         <xsl:attribute name="displayLabel">
1886                                                 <xsl:value-of select="text()"/>
1887                                         </xsl:attribute>
1888                                 </xsl:for-each>
1889                                 <title>
1890                                         <xsl:call-template name="chopPunctuation">
1891                                                 <xsl:with-param name="chopString">
1892                                                         <xsl:call-template name="subfieldSelect">
1893                                                                 <!-- 1/04 removed $h, $b -->
1894                                                                 <xsl:with-param name="codes">af</xsl:with-param>
1895                                                         </xsl:call-template>
1896                                                 </xsl:with-param>
1897                                         </xsl:call-template>
1898                                 </title>
1899                                 <xsl:call-template name="subtitle"/>
1900                                 <xsl:call-template name="part"/>
1901                         </titleInfo>
1902                 </xsl:for-each>
1903                 <xsl:for-each select="marc:datafield[@tag='130']|marc:datafield[@tag='240']|marc:datafield[@tag='730'][@ind2!='2']">
1904                         <xsl:variable name="nfi">
1905                                 <xsl:choose>
1906                                         <xsl:when test="@tag='240'">
1907                                                 <xsl:value-of select="@ind2"/>
1908                                         </xsl:when>
1909                                         <xsl:otherwise>
1910                                                 <xsl:value-of select="@ind1"/>
1911                                         </xsl:otherwise>
1912                                 </xsl:choose>
1913                         </xsl:variable>
1914                         <xsl:variable name="titleChop">
1915                                 <xsl:call-template name="uri" />
1916                                 <xsl:variable name="str">
1917                                         <xsl:for-each select="marc:subfield">
1918                                                 <xsl:if test="(contains('adfklmor',@code) and (not(../marc:subfield[@code='n' or @code='p']) or (following-sibling::marc:subfield[@code='n' or @code='p'])))">
1919                                                         <xsl:value-of select="text()"/>
1920                                                         <xsl:text> </xsl:text>
1921                                                 </xsl:if>
1922                                         </xsl:for-each>
1923                                 </xsl:variable>
1924                                 <xsl:call-template name="chopPunctuation">
1925                                         <xsl:with-param name="chopString">
1926                                                 <xsl:value-of select="substring($str,1,string-length($str)-1)"/>
1927                                         </xsl:with-param>
1928                                 </xsl:call-template>
1929                         </xsl:variable>
1930                         <titleInfo type="uniform">
1931                                 <title>
1932                                         <xsl:value-of select="$titleChop"/>
1933                                 </title>
1934                                 <xsl:call-template name="part"/>
1935                         </titleInfo>
1936                         <titleInfo type="uniform-nfi">
1937                                 <xsl:choose>
1938                                         <xsl:when test="$nfi>0">
1939                                                 <nonSort>
1940                                                         <xsl:value-of select="substring($titleChop,1,$nfi)"/>
1941                                                 </nonSort>
1942                                                 <title>
1943                                                         <xsl:value-of select="substring($titleChop,$nfi+1)"/>
1944                                                 </title>
1945                                         </xsl:when>
1946                                         <xsl:otherwise>
1947                                                 <title>
1948                                                         <xsl:value-of select="$titleChop"/>
1949                                                 </title>
1950                                         </xsl:otherwise>
1951                                 </xsl:choose>
1952                                 <xsl:call-template name="part"/>
1953                         </titleInfo>
1954                 </xsl:for-each>
1955                 <xsl:for-each select="marc:datafield[@tag='740'][@ind2!='2']">
1956                         <xsl:variable name="titleChop">
1957                                 <xsl:call-template name="chopPunctuation">
1958                                         <xsl:with-param name="chopString">
1959                                                 <xsl:call-template name="subfieldSelect">
1960                                                         <xsl:with-param name="codes">ah</xsl:with-param>
1961                                                 </xsl:call-template>
1962                                         </xsl:with-param>
1963                                 </xsl:call-template>
1964                         </xsl:variable>
1965                         <titleInfo type="alternative">
1966                                 <title>
1967                                         <xsl:value-of select="$titleChop" />
1968                                 </title>
1969                                 <xsl:call-template name="part"/>
1970                         </titleInfo>
1971                         <titleInfo type="alternative-nfi">
1972                                 <xsl:choose>
1973                                         <xsl:when test="@ind1>0">
1974                                                 <nonSort>
1975                                                         <xsl:value-of select="substring($titleChop,1,@ind1)"/>
1976                                                 </nonSort>
1977                                                 <title>
1978                                                         <xsl:value-of select="substring($titleChop,@ind1+1)"/>
1979                                                 </title>
1980                                         </xsl:when>
1981                                         <xsl:otherwise>
1982                                                 <title>
1983                                                         <xsl:value-of select="$titleChop" />
1984                                                 </title>
1985                                         </xsl:otherwise>
1986                                 </xsl:choose>
1987                                 <xsl:call-template name="part"/>
1988                         </titleInfo>
1989                 </xsl:for-each>
1990                 <xsl:for-each select="marc:datafield[@tag='100']">
1991                         <name type="personal">
1992                                 <xsl:call-template name="uri" />
1993                                 <xsl:call-template name="nameABCDQ"/>
1994                                 <xsl:call-template name="affiliation"/>
1995                                 <role>
1996                                         <roleTerm authority="marcrelator" type="text">creator</roleTerm>
1997                                 </role>
1998                                 <xsl:call-template name="role"/>
1999                         </name>
2000                 </xsl:for-each>
2001                 <xsl:for-each select="marc:datafield[@tag='110']">
2002                         <name type="corporate">
2003                                 <xsl:call-template name="uri" />
2004                                 <xsl:call-template name="nameABCDN"/>
2005                                 <role>
2006                                         <roleTerm authority="marcrelator" type="text">creator</roleTerm>
2007                                 </role>
2008                                 <xsl:call-template name="role"/>
2009                         </name>
2010                 </xsl:for-each>
2011                 <xsl:for-each select="marc:datafield[@tag='111']">
2012                         <name type="conference">
2013                                 <xsl:call-template name="uri" />
2014                                 <xsl:call-template name="nameACDEQ"/>
2015                                 <role>
2016                                         <roleTerm authority="marcrelator" type="text">creator</roleTerm>
2017                                 </role>
2018                                 <xsl:call-template name="role"/>
2019                         </name>
2020                 </xsl:for-each>
2021                 <xsl:for-each select="marc:datafield[@tag='700'][not(marc:subfield[@code='t'])]">
2022                         <name type="personal">
2023                                 <xsl:call-template name="uri" />
2024                                 <xsl:call-template name="nameABCDQ"/>
2025                                 <xsl:call-template name="affiliation"/>
2026                                 <xsl:call-template name="role"/>
2027                         </name>
2028                 </xsl:for-each>
2029                 <xsl:for-each select="marc:datafield[@tag='710'][not(marc:subfield[@code='t'])]">
2030                         <name type="corporate">
2031                                 <xsl:call-template name="uri" />
2032                                 <xsl:call-template name="nameABCDN"/>
2033                                 <xsl:call-template name="role"/>
2034                         </name>
2035                 </xsl:for-each>
2036                 <xsl:for-each select="marc:datafield[@tag='711'][not(marc:subfield[@code='t'])]">
2037                         <name type="conference">
2038                                 <xsl:call-template name="uri" />
2039                                 <xsl:call-template name="nameACDEQ"/>
2040                                 <xsl:call-template name="role"/>
2041                         </name>
2042                 </xsl:for-each>
2043                 <xsl:for-each select="marc:datafield[@tag='720'][not(marc:subfield[@code='t'])]">
2044                         <name>
2045                                 <xsl:if test="@ind1=1">
2046                                         <xsl:attribute name="type">
2047                                                 <xsl:text>personal</xsl:text>
2048                                         </xsl:attribute>
2049                                 </xsl:if>
2050                                 <namePart>
2051                                         <xsl:value-of select="marc:subfield[@code='a']"/>
2052                                 </namePart>
2053                                 <xsl:call-template name="role"/>
2054                         </name>
2055                 </xsl:for-each>
2056                 <typeOfResource>
2057                         <xsl:if test="$leader7='c'">
2058                                 <xsl:attribute name="collection">yes</xsl:attribute>
2059                         </xsl:if>
2060                         <xsl:if test="$leader6='d' or $leader6='f' or $leader6='p' or $leader6='t'">
2061                                 <xsl:attribute name="manuscript">yes</xsl:attribute>
2062                         </xsl:if>
2063                         <xsl:choose>
2064                                 <xsl:when test="$leader6='a' or $leader6='t'">text</xsl:when>
2065                                 <xsl:when test="$leader6='e' or $leader6='f'">cartographic</xsl:when>
2066                                 <xsl:when test="$leader6='c' or $leader6='d'">notated music</xsl:when>
2067                                 <xsl:when test="$leader6='i'">sound recording-nonmusical</xsl:when>
2068                                 <xsl:when test="$leader6='j'">sound recording-musical</xsl:when>
2069                                 <xsl:when test="$leader6='k'">still image</xsl:when>
2070                                 <xsl:when test="$leader6='g'">moving image</xsl:when>
2071                                 <xsl:when test="$leader6='r'">three dimensional object</xsl:when>
2072                                 <xsl:when test="$leader6='m'">software, multimedia</xsl:when>
2073                                 <xsl:when test="$leader6='p'">mixed material</xsl:when>
2074                         </xsl:choose>
2075                 </typeOfResource>
2076                 <xsl:if test="substring($controlField008,26,1)='d'">
2077                         <genre authority="marc">globe</genre>
2078                 </xsl:if>
2079                 <xsl:if test="marc:controlfield[@tag='007'][substring(text(),1,1)='a'][substring(text(),2,1)='r']">
2080                         <genre authority="marc">remote sensing image</genre>
2081                 </xsl:if>
2082                 <xsl:if test="$typeOf008='MP'">
2083                         <xsl:variable name="controlField008-25" select="substring($controlField008,26,1)"></xsl:variable>
2084                         <xsl:choose>
2085                                 <xsl:when test="$controlField008-25='a' or $controlField008-25='b' or $controlField008-25='c' or marc:controlfield[@tag=007][substring(text(),1,1)='a'][substring(text(),2,1)='j']">
2086                                         <genre authority="marc">map</genre>
2087                                 </xsl:when>
2088                                 <xsl:when test="$controlField008-25='e' or marc:controlfield[@tag=007][substring(text(),1,1)='a'][substring(text(),2,1)='d']">
2089                                         <genre authority="marc">atlas</genre>
2090                                 </xsl:when>
2091                         </xsl:choose>
2092                 </xsl:if>
2093                 <xsl:if test="$typeOf008='SE'">
2094                         <xsl:variable name="controlField008-21" select="substring($controlField008,22,1)"></xsl:variable>
2095                         <xsl:choose>
2096                                 <xsl:when test="$controlField008-21='d'">
2097                                         <genre authority="marc">database</genre>
2098                                 </xsl:when>
2099                                 <xsl:when test="$controlField008-21='l'">
2100                                         <genre authority="marc">loose-leaf</genre>
2101                                 </xsl:when>
2102                                 <xsl:when test="$controlField008-21='m'">
2103                                         <genre authority="marc">series</genre>
2104                                 </xsl:when>
2105                                 <xsl:when test="$controlField008-21='n'">
2106                                         <genre authority="marc">newspaper</genre>
2107                                 </xsl:when>
2108                                 <xsl:when test="$controlField008-21='p'">
2109                                         <genre authority="marc">periodical</genre>
2110                                 </xsl:when>
2111                                 <xsl:when test="$controlField008-21='w'">
2112                                         <genre authority="marc">web site</genre>
2113                                 </xsl:when>
2114                         </xsl:choose>
2115                 </xsl:if>
2116                 <xsl:if test="$typeOf008='BK' or $typeOf008='SE'">
2117                         <xsl:variable name="controlField008-24" select="substring($controlField008,25,4)"></xsl:variable>
2118                         <xsl:choose>
2119                                 <xsl:when test="contains($controlField008-24,'a')">
2120                                         <genre authority="marc">abstract or summary</genre>
2121                                 </xsl:when>
2122                                 <xsl:when test="contains($controlField008-24,'b')">
2123                                         <genre authority="marc">bibliography</genre>
2124                                 </xsl:when>
2125                                 <xsl:when test="contains($controlField008-24,'c')">
2126                                         <genre authority="marc">catalog</genre>
2127                                 </xsl:when>
2128                                 <xsl:when test="contains($controlField008-24,'d')">
2129                                         <genre authority="marc">dictionary</genre>
2130                                 </xsl:when>
2131                                 <xsl:when test="contains($controlField008-24,'e')">
2132                                         <genre authority="marc">encyclopedia</genre>
2133                                 </xsl:when>
2134                                 <xsl:when test="contains($controlField008-24,'f')">
2135                                         <genre authority="marc">handbook</genre>
2136                                 </xsl:when>
2137                                 <xsl:when test="contains($controlField008-24,'g')">
2138                                         <genre authority="marc">legal article</genre>
2139                                 </xsl:when>
2140                                 <xsl:when test="contains($controlField008-24,'i')">
2141                                         <genre authority="marc">index</genre>
2142                                 </xsl:when>
2143                                 <xsl:when test="contains($controlField008-24,'k')">
2144                                         <genre authority="marc">discography</genre>
2145                                 </xsl:when>
2146                                 <xsl:when test="contains($controlField008-24,'l')">
2147                                         <genre authority="marc">legislation</genre>
2148                                 </xsl:when>
2149                                 <xsl:when test="contains($controlField008-24,'m')">
2150                                         <genre authority="marc">theses</genre>
2151                                 </xsl:when>
2152                                 <xsl:when test="contains($controlField008-24,'n')">
2153                                         <genre authority="marc">survey of literature</genre>
2154                                 </xsl:when>
2155                                 <xsl:when test="contains($controlField008-24,'o')">
2156                                         <genre authority="marc">review</genre>
2157                                 </xsl:when>
2158                                 <xsl:when test="contains($controlField008-24,'p')">
2159                                         <genre authority="marc">programmed text</genre>
2160                                 </xsl:when>
2161                                 <xsl:when test="contains($controlField008-24,'q')">
2162                                         <genre authority="marc">filmography</genre>
2163                                 </xsl:when>
2164                                 <xsl:when test="contains($controlField008-24,'r')">
2165                                         <genre authority="marc">directory</genre>
2166                                 </xsl:when>
2167                                 <xsl:when test="contains($controlField008-24,'s')">
2168                                         <genre authority="marc">statistics</genre>
2169                                 </xsl:when>
2170                                 <xsl:when test="contains($controlField008-24,'t')">
2171                                         <genre authority="marc">technical report</genre>
2172                                 </xsl:when>
2173                                 <xsl:when test="contains($controlField008-24,'v')">
2174                                         <genre authority="marc">legal case and case notes</genre>
2175                                 </xsl:when>
2176                                 <xsl:when test="contains($controlField008-24,'w')">
2177                                         <genre authority="marc">law report or digest</genre>
2178                                 </xsl:when>
2179                                 <xsl:when test="contains($controlField008-24,'z')">
2180                                         <genre authority="marc">treaty</genre>
2181                                 </xsl:when>
2182                         </xsl:choose>
2183                         <xsl:variable name="controlField008-29" select="substring($controlField008,30,1)"></xsl:variable>
2184                         <xsl:choose>
2185                                 <xsl:when test="$controlField008-29='1'">
2186                                         <genre authority="marc">conference publication</genre>
2187                                 </xsl:when>
2188                         </xsl:choose>
2189                 </xsl:if>
2190                 <xsl:if test="$typeOf008='CF'">
2191                         <xsl:variable name="controlField008-26" select="substring($controlField008,27,1)"></xsl:variable>
2192                         <xsl:choose>
2193                                 <xsl:when test="$controlField008-26='a'">
2194                                         <genre authority="marc">numeric data</genre>
2195                                 </xsl:when>
2196                                 <xsl:when test="$controlField008-26='e'">
2197                                         <genre authority="marc">database</genre>
2198                                 </xsl:when>
2199                                 <xsl:when test="$controlField008-26='f'">
2200                                         <genre authority="marc">font</genre>
2201                                 </xsl:when>
2202                                 <xsl:when test="$controlField008-26='g'">
2203                                         <genre authority="marc">game</genre>
2204                                 </xsl:when>
2205                         </xsl:choose>
2206                 </xsl:if>
2207                 <xsl:if test="$typeOf008='BK'">
2208                         <xsl:if test="substring($controlField008,25,1)='j'">
2209                                 <genre authority="marc">patent</genre>
2210                         </xsl:if>
2211                         <xsl:if test="substring($controlField008,31,1)='1'">
2212                                 <genre authority="marc">festschrift</genre>
2213                         </xsl:if>
2214                         <xsl:variable name="controlField008-34" select="substring($controlField008,35,1)"></xsl:variable>
2215                         <xsl:if test="$controlField008-34='a' or $controlField008-34='b' or $controlField008-34='c' or $controlField008-34='d'">
2216                                 <genre authority="marc">biography</genre>
2217                         </xsl:if>
2218                         <xsl:variable name="controlField008-33" select="substring($controlField008,34,1)"></xsl:variable>
2219                         <xsl:choose>
2220                                 <xsl:when test="$controlField008-33='e'">
2221                                         <genre authority="marc">essay</genre>
2222                                 </xsl:when>
2223                                 <xsl:when test="$controlField008-33='d'">
2224                                         <genre authority="marc">drama</genre>
2225                                 </xsl:when>
2226                                 <xsl:when test="$controlField008-33='c'">
2227                                         <genre authority="marc">comic strip</genre>
2228                                 </xsl:when>
2229                                 <xsl:when test="$controlField008-33='l'">
2230                                         <genre authority="marc">fiction</genre>
2231                                 </xsl:when>
2232                                 <xsl:when test="$controlField008-33='h'">
2233                                         <genre authority="marc">humor, satire</genre>
2234                                 </xsl:when>
2235                                 <xsl:when test="$controlField008-33='i'">
2236                                         <genre authority="marc">letter</genre>
2237                                 </xsl:when>
2238                                 <xsl:when test="$controlField008-33='f'">
2239                                         <genre authority="marc">novel</genre>
2240                                 </xsl:when>
2241                                 <xsl:when test="$controlField008-33='j'">
2242                                         <genre authority="marc">short story</genre>
2243                                 </xsl:when>
2244                                 <xsl:when test="$controlField008-33='s'">
2245                                         <genre authority="marc">speech</genre>
2246                                 </xsl:when>
2247                         </xsl:choose>
2248                 </xsl:if>
2249                 <xsl:if test="$typeOf008='MU'">
2250                         <xsl:variable name="controlField008-30-31" select="substring($controlField008,31,2)"></xsl:variable>
2251                         <xsl:if test="contains($controlField008-30-31,'b')">
2252                                 <genre authority="marc">biography</genre>
2253                         </xsl:if>
2254                         <xsl:if test="contains($controlField008-30-31,'c')">
2255                                 <genre authority="marc">conference publication</genre>
2256                         </xsl:if>
2257                         <xsl:if test="contains($controlField008-30-31,'d')">
2258                                 <genre authority="marc">drama</genre>
2259                         </xsl:if>
2260                         <xsl:if test="contains($controlField008-30-31,'e')">
2261                                 <genre authority="marc">essay</genre>
2262                         </xsl:if>
2263                         <xsl:if test="contains($controlField008-30-31,'f')">
2264                                 <genre authority="marc">fiction</genre>
2265                         </xsl:if>
2266                         <xsl:if test="contains($controlField008-30-31,'o')">
2267                                 <genre authority="marc">folktale</genre>
2268                         </xsl:if>
2269                         <xsl:if test="contains($controlField008-30-31,'h')">
2270                                 <genre authority="marc">history</genre>
2271                         </xsl:if>
2272                         <xsl:if test="contains($controlField008-30-31,'k')">
2273                                 <genre authority="marc">humor, satire</genre>
2274                         </xsl:if>
2275                         <xsl:if test="contains($controlField008-30-31,'m')">
2276                                 <genre authority="marc">memoir</genre>
2277                         </xsl:if>
2278                         <xsl:if test="contains($controlField008-30-31,'p')">
2279                                 <genre authority="marc">poetry</genre>
2280                         </xsl:if>
2281                         <xsl:if test="contains($controlField008-30-31,'r')">
2282                                 <genre authority="marc">rehearsal</genre>
2283                         </xsl:if>
2284                         <xsl:if test="contains($controlField008-30-31,'g')">
2285                                 <genre authority="marc">reporting</genre>
2286                         </xsl:if>
2287                         <xsl:if test="contains($controlField008-30-31,'s')">
2288                                 <genre authority="marc">sound</genre>
2289                         </xsl:if>
2290                         <xsl:if test="contains($controlField008-30-31,'l')">
2291                                 <genre authority="marc">speech</genre>
2292                         </xsl:if>
2293                 </xsl:if>
2294                 <xsl:if test="$typeOf008='VM'">
2295                         <xsl:variable name="controlField008-33" select="substring($controlField008,34,1)"></xsl:variable>
2296                         <xsl:choose>
2297                                 <xsl:when test="$controlField008-33='a'">
2298                                         <genre authority="marc">art original</genre>
2299                                 </xsl:when>
2300                                 <xsl:when test="$controlField008-33='b'">
2301                                         <genre authority="marc">kit</genre>
2302                                 </xsl:when>
2303                                 <xsl:when test="$controlField008-33='c'">
2304                                         <genre authority="marc">art reproduction</genre>
2305                                 </xsl:when>
2306                                 <xsl:when test="$controlField008-33='d'">
2307                                         <genre authority="marc">diorama</genre>
2308                                 </xsl:when>
2309                                 <xsl:when test="$controlField008-33='f'">
2310                                         <genre authority="marc">filmstrip</genre>
2311                                 </xsl:when>
2312                                 <xsl:when test="$controlField008-33='g'">
2313                                         <genre authority="marc">legal article</genre>
2314                                 </xsl:when>
2315                                 <xsl:when test="$controlField008-33='i'">
2316                                         <genre authority="marc">picture</genre>
2317                                 </xsl:when>
2318                                 <xsl:when test="$controlField008-33='k'">
2319                                         <genre authority="marc">graphic</genre>
2320                                 </xsl:when>
2321                                 <xsl:when test="$controlField008-33='l'">
2322                                         <genre authority="marc">technical drawing</genre>
2323                                 </xsl:when>
2324                                 <xsl:when test="$controlField008-33='m'">
2325                                         <genre authority="marc">motion picture</genre>
2326                                 </xsl:when>
2327                                 <xsl:when test="$controlField008-33='n'">
2328                                         <genre authority="marc">chart</genre>
2329                                 </xsl:when>
2330                                 <xsl:when test="$controlField008-33='o'">
2331                                         <genre authority="marc">flash card</genre>
2332                                 </xsl:when>
2333                                 <xsl:when test="$controlField008-33='p'">
2334                                         <genre authority="marc">microscope slide</genre>
2335                                 </xsl:when>
2336                                 <xsl:when test="$controlField008-33='q' or marc:controlfield[@tag=007][substring(text(),1,1)='a'][substring(text(),2,1)='q']">
2337                                         <genre authority="marc">model</genre>
2338                                 </xsl:when>
2339                                 <xsl:when test="$controlField008-33='r'">
2340                                         <genre authority="marc">realia</genre>
2341                                 </xsl:when>
2342                                 <xsl:when test="$controlField008-33='s'">
2343                                         <genre authority="marc">slide</genre>
2344                                 </xsl:when>
2345                                 <xsl:when test="$controlField008-33='t'">
2346                                         <genre authority="marc">transparency</genre>
2347                                 </xsl:when>
2348                                 <xsl:when test="$controlField008-33='v'">
2349                                         <genre authority="marc">videorecording</genre>
2350                                 </xsl:when>
2351                                 <xsl:when test="$controlField008-33='w'">
2352                                         <genre authority="marc">toy</genre>
2353                                 </xsl:when>
2354                         </xsl:choose>
2355                 </xsl:if>
2356                 <xsl:for-each select="marc:datafield[@tag=655]">
2357                         <genre authority="marc">
2358                                 <xsl:attribute name="authority">
2359                                         <xsl:value-of select="marc:subfield[@code='2']"/>
2360                                 </xsl:attribute>
2361                                 <xsl:call-template name="subfieldSelect">
2362                                         <xsl:with-param name="codes">abvxyz</xsl:with-param>
2363                                         <xsl:with-param name="delimeter">-</xsl:with-param>
2364                                 </xsl:call-template>
2365                         </genre>
2366                 </xsl:for-each>
2367                 <originInfo>
2368                         <xsl:variable name="MARCpublicationCode" select="normalize-space(substring($controlField008,16,3))"></xsl:variable>
2369                         <xsl:if test="translate($MARCpublicationCode,'|','')">
2370                                 <place>
2371                                         <placeTerm>
2372                                                 <xsl:attribute name="type">code</xsl:attribute>
2373                                                 <xsl:attribute name="authority">marccountry</xsl:attribute>
2374                                                 <xsl:value-of select="$MARCpublicationCode"/>
2375                                         </placeTerm>
2376                                 </place>
2377                         </xsl:if>
2378                         <xsl:for-each select="marc:datafield[@tag=044]/marc:subfield[@code='c']">
2379                                 <place>
2380                                         <placeTerm>
2381                                                 <xsl:attribute name="type">code</xsl:attribute>
2382                                                 <xsl:attribute name="authority">iso3166</xsl:attribute>
2383                                                 <xsl:value-of select="."/>
2384                                         </placeTerm>
2385                                 </place>
2386                         </xsl:for-each>
2387                         <xsl:for-each select="marc:datafield[@tag=260]/marc:subfield[@code='a']">
2388                                 <place>
2389                                         <placeTerm>
2390                                                 <xsl:attribute name="type">text</xsl:attribute>
2391                                                 <xsl:call-template name="chopPunctuationFront">
2392                                                         <xsl:with-param name="chopString">
2393                                                                 <xsl:call-template name="chopPunctuation">
2394                                                                         <xsl:with-param name="chopString" select="."/>
2395                                                                 </xsl:call-template>
2396                                                         </xsl:with-param>
2397                                                 </xsl:call-template>
2398                                         </placeTerm>
2399                                 </place>
2400                         </xsl:for-each>
2401                         <xsl:for-each select="marc:datafield[@tag=046]/marc:subfield[@code='m']">
2402                                 <dateValid point="start">
2403                                         <xsl:value-of select="."/>
2404                                 </dateValid>
2405                         </xsl:for-each>
2406                         <xsl:for-each select="marc:datafield[@tag=046]/marc:subfield[@code='n']">
2407                                 <dateValid point="end">
2408                                         <xsl:value-of select="."/>
2409                                 </dateValid>
2410                         </xsl:for-each>
2411                         <xsl:for-each select="marc:datafield[@tag=046]/marc:subfield[@code='j']">
2412                                 <dateModified>
2413                                         <xsl:value-of select="."/>
2414                                 </dateModified>
2415                         </xsl:for-each>
2416                         <xsl:for-each select="marc:datafield[@tag=260]/marc:subfield[@code='b' or @code='c' or @code='g']">
2417                                 <xsl:choose>
2418                                         <xsl:when test="@code='b'">
2419                                                 <publisher>
2420                                                         <xsl:call-template name="chopPunctuation">
2421                                                                 <xsl:with-param name="chopString" select="."/>
2422                                                                 <xsl:with-param name="punctuation">
2423                                                                         <xsl:text>:,;/ </xsl:text>
2424                                                                 </xsl:with-param>
2425                                                         </xsl:call-template>
2426                                                 </publisher>
2427                                         </xsl:when>
2428                                         <xsl:when test="@code='c'">
2429                                                 <dateIssued>
2430                                                         <xsl:call-template name="chopPunctuation">
2431                                                                 <xsl:with-param name="chopString" select="."/>
2432                                                         </xsl:call-template>
2433                                                 </dateIssued>
2434                                         </xsl:when>
2435                                         <xsl:when test="@code='g'">
2436                                                 <dateCreated>
2437                                                         <xsl:value-of select="."/>
2438                                                 </dateCreated>
2439                                         </xsl:when>
2440                                 </xsl:choose>
2441                         </xsl:for-each>
2442                         <xsl:variable name="dataField260c">
2443                                 <xsl:call-template name="chopPunctuation">
2444                                         <xsl:with-param name="chopString" select="marc:datafield[@tag=260]/marc:subfield[@code='c']"></xsl:with-param>
2445                                 </xsl:call-template>
2446                         </xsl:variable>
2447                         <xsl:variable name="controlField008-7-10" select="normalize-space(substring($controlField008, 8, 4))"></xsl:variable>
2448                         <xsl:variable name="controlField008-11-14" select="normalize-space(substring($controlField008, 12, 4))"></xsl:variable>
2449                         <xsl:variable name="controlField008-6" select="normalize-space(substring($controlField008, 7, 1))"></xsl:variable>
2450                         <xsl:if test="$controlField008-6='e' or $controlField008-6='p' or $controlField008-6='r' or $controlField008-6='t' or $controlField008-6='s'">
2451                                 <xsl:if test="$controlField008-7-10 and ($controlField008-7-10 != $dataField260c)">
2452                                         <dateIssued encoding="marc">
2453                                                 <xsl:value-of select="$controlField008-7-10"/>
2454                                         </dateIssued>
2455                                 </xsl:if>
2456                         </xsl:if>
2457                         <xsl:if test="$controlField008-6='c' or $controlField008-6='d' or $controlField008-6='i' or $controlField008-6='k' or $controlField008-6='m' or $controlField008-6='q' or $controlField008-6='u'">
2458                                 <xsl:if test="$controlField008-7-10">
2459                                         <dateIssued encoding="marc" point="start">
2460                                                 <xsl:value-of select="$controlField008-7-10"/>
2461                                         </dateIssued>
2462                                 </xsl:if>
2463                         </xsl:if>
2464                         <xsl:if test="$controlField008-6='c' or $controlField008-6='d' or $controlField008-6='i' or $controlField008-6='k' or $controlField008-6='m' or $controlField008-6='q' or $controlField008-6='u'">
2465                                 <xsl:if test="$controlField008-11-14">
2466                                         <dateIssued encoding="marc" point="end">
2467                                                 <xsl:value-of select="$controlField008-11-14"/>
2468                                         </dateIssued>
2469                                 </xsl:if>
2470                         </xsl:if>
2471                         <xsl:if test="$controlField008-6='q'">
2472                                 <xsl:if test="$controlField008-7-10">
2473                                         <dateIssued encoding="marc" point="start" qualifier="questionable">
2474                                                 <xsl:value-of select="$controlField008-7-10"/>
2475                                         </dateIssued>
2476                                 </xsl:if>
2477                         </xsl:if>
2478                         <xsl:if test="$controlField008-6='q'">
2479                                 <xsl:if test="$controlField008-11-14">
2480                                         <dateIssued encoding="marc" point="end" qualifier="questionable">
2481                                                 <xsl:value-of select="$controlField008-11-14"/>
2482                                         </dateIssued>
2483                                 </xsl:if>
2484                         </xsl:if>
2485                         <xsl:if test="$controlField008-6='t'">
2486                                 <xsl:if test="$controlField008-11-14">
2487                                         <copyrightDate encoding="marc">
2488                                                 <xsl:value-of select="$controlField008-11-14"/>
2489                                         </copyrightDate>
2490                                 </xsl:if>
2491                         </xsl:if>
2492                         <xsl:for-each select="marc:datafield[@tag=033][@ind1=0 or @ind1=1]/marc:subfield[@code='a']">
2493                                 <dateCaptured encoding="iso8601">
2494                                         <xsl:value-of select="."/>
2495                                 </dateCaptured>
2496                         </xsl:for-each>
2497                         <xsl:for-each select="marc:datafield[@tag=033][@ind1=2]/marc:subfield[@code='a'][1]">
2498                                 <dateCaptured encoding="iso8601" point="start">
2499                                         <xsl:value-of select="."/>
2500                                 </dateCaptured>
2501                         </xsl:for-each>
2502                         <xsl:for-each select="marc:datafield[@tag=033][@ind1=2]/marc:subfield[@code='a'][2]">
2503                                 <dateCaptured encoding="iso8601" point="end">
2504                                         <xsl:value-of select="."/>
2505                                 </dateCaptured>
2506                         </xsl:for-each>
2507                         <xsl:for-each select="marc:datafield[@tag=250]/marc:subfield[@code='a']">
2508                                 <edition>
2509                                         <xsl:value-of select="."/>
2510                                 </edition>
2511                         </xsl:for-each>
2512                         <xsl:for-each select="marc:leader">
2513                                 <issuance>
2514                                         <xsl:choose>
2515                                                 <xsl:when test="$leader7='a' or $leader7='c' or $leader7='d' or $leader7='m'">monographic</xsl:when>
2516                                                 <xsl:when test="$leader7='b' or $leader7='i' or $leader7='s'">continuing</xsl:when>
2517                                         </xsl:choose>
2518                                 </issuance>
2519                         </xsl:for-each>
2520                         <xsl:for-each select="marc:datafield[@tag=310]|marc:datafield[@tag=321]">
2521                                 <frequency>
2522                                         <xsl:call-template name="subfieldSelect">
2523                                                 <xsl:with-param name="codes">ab</xsl:with-param>
2524                                         </xsl:call-template>
2525                                 </frequency>
2526                         </xsl:for-each>
2527                 </originInfo>
2528                 <xsl:variable name="controlField008-35-37" select="normalize-space(translate(substring($controlField008,36,3),'|#',''))"></xsl:variable>
2529     &nb