476ee0774569c103251311492b81cc0c90c17948
[working/Evergreen.git] / Open-ILS / src / sql / Pg / 012.schema.vandelay.sql
1 DROP SCHEMA IF EXISTS vandelay CASCADE;
2
3 BEGIN;
4
5 CREATE SCHEMA vandelay;
6
7 CREATE TABLE vandelay.queue (
8         id                              BIGSERIAL       PRIMARY KEY,
9         owner                   INT                     NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
10         name                    TEXT            NOT NULL,
11         complete                BOOL            NOT NULL DEFAULT FALSE,
12         queue_type              TEXT            NOT NULL DEFAULT 'bib' CHECK (queue_type IN ('bib','authority')),
13         CONSTRAINT vand_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type)
14 );
15
16 CREATE TABLE vandelay.queued_record (
17     id                  BIGSERIAL                   PRIMARY KEY,
18     create_time TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
19     import_time TIMESTAMP WITH TIME ZONE,
20         purpose         TEXT                                            NOT NULL DEFAULT 'import' CHECK (purpose IN ('import','overlay')),
21     marc                TEXT                        NOT NULL
22 );
23
24
25
26 /* Bib stuff at the top */
27 ----------------------------------------------------
28
29 CREATE TABLE vandelay.bib_attr_definition (
30         id                      SERIAL  PRIMARY KEY,
31         code            TEXT    UNIQUE NOT NULL,
32         description     TEXT,
33         xpath           TEXT    NOT NULL,
34         remove          TEXT    NOT NULL DEFAULT '',
35         ident           BOOL    NOT NULL DEFAULT FALSE
36 );
37
38 -- Each TEXT field (other than 'name') should hold an XPath predicate for pulling the data needed
39 -- DROP TABLE vandelay.import_item_attr_definition CASCADE;
40 CREATE TABLE vandelay.import_item_attr_definition (
41     id              BIGSERIAL   PRIMARY KEY,
42     owner           INT         NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
43     name            TEXT        NOT NULL,
44     tag             TEXT        NOT NULL,
45     keep            BOOL        NOT NULL DEFAULT FALSE,
46     owning_lib      TEXT,
47     circ_lib        TEXT,
48     call_number     TEXT,
49     copy_number     TEXT,
50     status          TEXT,
51     location        TEXT,
52     circulate       TEXT,
53     deposit         TEXT,
54     deposit_amount  TEXT,
55     ref             TEXT,
56     holdable        TEXT,
57     price           TEXT,
58     barcode         TEXT,
59     circ_modifier   TEXT,
60     circ_as_type    TEXT,
61     alert_message   TEXT,
62     opac_visible    TEXT,
63     pub_note_title  TEXT,
64     pub_note        TEXT,
65     priv_note_title TEXT,
66     priv_note       TEXT,
67         CONSTRAINT vand_import_item_attr_def_idx UNIQUE (owner,name)
68 );
69
70 CREATE TABLE vandelay.bib_queue (
71         queue_type          TEXT        NOT NULL DEFAULT 'bib' CHECK (queue_type = 'bib'),
72         item_attr_def   BIGINT REFERENCES vandelay.import_item_attr_definition (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
73         CONSTRAINT vand_bib_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type)
74 ) INHERITS (vandelay.queue);
75 ALTER TABLE vandelay.bib_queue ADD PRIMARY KEY (id);
76
77 CREATE TABLE vandelay.queued_bib_record (
78         queue           INT             NOT NULL REFERENCES vandelay.bib_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
79         bib_source      INT             REFERENCES config.bib_source (id) DEFERRABLE INITIALLY DEFERRED,
80         imported_as     BIGINT  REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED
81 ) INHERITS (vandelay.queued_record);
82 ALTER TABLE vandelay.queued_bib_record ADD PRIMARY KEY (id);
83 CREATE INDEX queued_bib_record_queue_idx ON vandelay.queued_bib_record (queue);
84
85 CREATE TABLE vandelay.queued_bib_record_attr (
86         id                      BIGSERIAL       PRIMARY KEY,
87         record          BIGINT          NOT NULL REFERENCES vandelay.queued_bib_record (id) DEFERRABLE INITIALLY DEFERRED,
88         field           INT                     NOT NULL REFERENCES vandelay.bib_attr_definition (id) DEFERRABLE INITIALLY DEFERRED,
89         attr_value      TEXT            NOT NULL
90 );
91 CREATE INDEX queued_bib_record_attr_record_idx ON vandelay.queued_bib_record_attr (record);
92
93 CREATE TABLE vandelay.bib_match (
94         id                              BIGSERIAL       PRIMARY KEY,
95         field_type              TEXT            NOT NULL CHECK (field_type in ('isbn','tcn_value','id')),
96         matched_attr    INT                     REFERENCES vandelay.queued_bib_record_attr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
97         queued_record   BIGINT          REFERENCES vandelay.queued_bib_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
98         eg_record               BIGINT          REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
99 );
100
101 -- DROP TABLE vandelay.import_item CASCADE;
102 CREATE TABLE vandelay.import_item (
103     id              BIGSERIAL   PRIMARY KEY,
104     record          BIGINT      NOT NULL REFERENCES vandelay.queued_bib_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
105     definition      BIGINT      NOT NULL REFERENCES vandelay.import_item_attr_definition (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
106     owning_lib      INT,
107     circ_lib        INT,
108     call_number     TEXT,
109     copy_number     INT,
110     status          INT,
111     location        INT,
112     circulate       BOOL,
113     deposit         BOOL,
114     deposit_amount  NUMERIC(8,2),
115     ref             BOOL,
116     holdable        BOOL,
117     price           NUMERIC(8,2),
118     barcode         TEXT,
119     circ_modifier   TEXT,
120     circ_as_type    TEXT,
121     alert_message   TEXT,
122     pub_note        TEXT,
123     priv_note       TEXT,
124     opac_visible    BOOL
125 );
126  
127 CREATE TABLE vandelay.import_bib_trash_fields (
128     id              BIGSERIAL   PRIMARY KEY,
129     owner           INT         NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
130     field           TEXT        NOT NULL,
131         CONSTRAINT vand_import_bib_trash_fields_idx UNIQUE (owner,field)
132 );
133
134 CREATE TABLE vandelay.merge_profile (
135     id              BIGSERIAL   PRIMARY KEY,
136     owner           INT         NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
137     name            TEXT        NOT NULL,
138     add_spec        TEXT,
139     replace_spec    TEXT,
140     strip_spec      TEXT,
141     preserve_spec   TEXT,
142         CONSTRAINT vand_merge_prof_owner_name_idx UNIQUE (owner,name),
143         CONSTRAINT add_replace_strip_or_preserve CHECK ((preserve_spec IS NOT NULL OR replace_spec IS NOT NULL) OR (preserve_spec IS NULL AND replace_spec IS NULL))
144 );
145
146
147 CREATE TYPE vandelay.tcn_data AS (tcn TEXT, tcn_source TEXT, used BOOL);
148 CREATE OR REPLACE FUNCTION vandelay.find_bib_tcn_data ( xml TEXT ) RETURNS SETOF vandelay.tcn_data AS $_$
149 DECLARE
150     eg_tcn          TEXT;
151     eg_tcn_source   TEXT;
152     output          vandelay.tcn_data%ROWTYPE;
153 BEGIN
154
155     -- 001/003
156     eg_tcn := BTRIM((oils_xpath('//*[@tag="001"]/text()',xml))[1]);
157     IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
158
159         eg_tcn_source := BTRIM((oils_xpath('//*[@tag="003"]/text()',xml))[1]);
160         IF eg_tcn_source IS NULL OR eg_tcn_source = '' THEN
161             eg_tcn_source := 'System Local';
162         END IF;
163
164         PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn  AND NOT deleted;
165
166         IF NOT FOUND THEN
167             output.used := FALSE;
168         ELSE
169             output.used := TRUE;
170         END IF;
171
172         output.tcn := eg_tcn;
173         output.tcn_source := eg_tcn_source;
174         RETURN NEXT output;
175
176     END IF;
177
178     -- 901 ab
179     eg_tcn := BTRIM((oils_xpath('//*[@tag="901"]/*[@code="a"]/text()',xml))[1]);
180     IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
181
182         eg_tcn_source := BTRIM((oils_xpath('//*[@tag="901"]/*[@code="b"]/text()',xml))[1]);
183         IF eg_tcn_source IS NULL OR eg_tcn_source = '' THEN
184             eg_tcn_source := 'System Local';
185         END IF;
186
187         PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn  AND NOT deleted;
188
189         IF NOT FOUND THEN
190             output.used := FALSE;
191         ELSE
192             output.used := TRUE;
193         END IF;
194
195         output.tcn := eg_tcn;
196         output.tcn_source := eg_tcn_source;
197         RETURN NEXT output;
198
199     END IF;
200
201     -- 039 ab
202     eg_tcn := BTRIM((oils_xpath('//*[@tag="039"]/*[@code="a"]/text()',xml))[1]);
203     IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
204
205         eg_tcn_source := BTRIM((oils_xpath('//*[@tag="039"]/*[@code="b"]/text()',xml))[1]);
206         IF eg_tcn_source IS NULL OR eg_tcn_source = '' THEN
207             eg_tcn_source := 'System Local';
208         END IF;
209
210         PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn  AND NOT deleted;
211
212         IF NOT FOUND THEN
213             output.used := FALSE;
214         ELSE
215             output.used := TRUE;
216         END IF;
217
218         output.tcn := eg_tcn;
219         output.tcn_source := eg_tcn_source;
220         RETURN NEXT output;
221
222     END IF;
223
224     -- 020 a
225     eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="020"]/*[@code="a"]/text()',xml))[1], $re$^(\w+).*?$$re$, $re$\1$re$);
226     IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
227
228         eg_tcn_source := 'ISBN';
229
230         PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn  AND NOT deleted;
231
232         IF NOT FOUND THEN
233             output.used := FALSE;
234         ELSE
235             output.used := TRUE;
236         END IF;
237
238         output.tcn := eg_tcn;
239         output.tcn_source := eg_tcn_source;
240         RETURN NEXT output;
241
242     END IF;
243
244     -- 022 a
245     eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="022"]/*[@code="a"]/text()',xml))[1], $re$^(\w+).*?$$re$, $re$\1$re$);
246     IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
247
248         eg_tcn_source := 'ISSN';
249
250         PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn  AND NOT deleted;
251
252         IF NOT FOUND THEN
253             output.used := FALSE;
254         ELSE
255             output.used := TRUE;
256         END IF;
257
258         output.tcn := eg_tcn;
259         output.tcn_source := eg_tcn_source;
260         RETURN NEXT output;
261
262     END IF;
263
264     -- 010 a
265     eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="010"]/*[@code="a"]/text()',xml))[1], $re$^(\w+).*?$$re$, $re$\1$re$);
266     IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
267
268         eg_tcn_source := 'LCCN';
269
270         PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn  AND NOT deleted;
271
272         IF NOT FOUND THEN
273             output.used := FALSE;
274         ELSE
275             output.used := TRUE;
276         END IF;
277
278         output.tcn := eg_tcn;
279         output.tcn_source := eg_tcn_source;
280         RETURN NEXT output;
281
282     END IF;
283
284     -- 035 a
285     eg_tcn := REGEXP_REPLACE((oils_xpath('//*[@tag="035"]/*[@code="a"]/text()',xml))[1], $re$^.*?(\w+)$$re$, $re$\1$re$);
286     IF eg_tcn IS NOT NULL AND eg_tcn <> '' THEN
287
288         eg_tcn_source := 'System Legacy';
289
290         PERFORM id FROM biblio.record_entry WHERE tcn_value = eg_tcn  AND NOT deleted;
291
292         IF NOT FOUND THEN
293             output.used := FALSE;
294         ELSE
295             output.used := TRUE;
296         END IF;
297
298         output.tcn := eg_tcn;
299         output.tcn_source := eg_tcn_source;
300         RETURN NEXT output;
301
302     END IF;
303
304     RETURN;
305 END;
306 $_$ LANGUAGE PLPGSQL;
307
308 CREATE OR REPLACE FUNCTION vandelay.add_field ( target_xml TEXT, source_xml TEXT, field TEXT, force_add INT ) RETURNS TEXT AS $_$
309
310     use MARC::Record;
311     use MARC::File::XML (BinaryEncoding => 'UTF-8');
312     use strict;
313
314     my $target_xml = shift;
315     my $source_xml = shift;
316     my $field_spec = shift;
317     my $force_add = shift || 0;
318
319     my $target_r = MARC::Record->new_from_xml( $target_xml );
320     my $source_r = MARC::Record->new_from_xml( $source_xml );
321
322     return $target_xml unless ($target_r && $source_r);
323
324     my @field_list = split(',', $field_spec);
325
326     my %fields;
327     for my $f (@field_list) {
328         $f =~ s/^\s*//; $f =~ s/\s*$//;
329         if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
330             my $field = $1;
331             $field =~ s/\s+//;
332             my $sf = $2;
333             $sf =~ s/\s+//;
334             my $match = $3;
335             $match =~ s/^\s*//; $match =~ s/\s*$//;
336             $fields{$field} = { sf => [ split('', $sf) ] };
337             if ($match) {
338                 my ($msf,$mre) = split('~', $match);
339                 if (length($msf) > 0 and length($mre) > 0) {
340                     $msf =~ s/^\s*//; $msf =~ s/\s*$//;
341                     $mre =~ s/^\s*//; $mre =~ s/\s*$//;
342                     $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
343                 }
344             }
345         }
346     }
347
348     for my $f ( keys %fields) {
349         if ( @{$fields{$f}{sf}} ) {
350             for my $from_field ($source_r->field( $f )) {
351                 my @tos = $target_r->field( $f );
352                 if (!@tos) {
353                     next if (exists($fields{$f}{match}) and !$force_add);
354                     my @new_fields = map { $_->clone } $source_r->field( $f );
355                     $target_r->insert_fields_ordered( @new_fields );
356                 } else {
357                     for my $to_field (@tos) {
358                         if (exists($fields{$f}{match})) {
359                             next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf}));
360                         }
361                         my @new_sf = map { ($_ => $from_field->subfield($_)) } @{$fields{$f}{sf}};
362                         $to_field->add_subfields( @new_sf );
363                     }
364                 }
365             }
366         } else {
367             my @new_fields = map { $_->clone } $source_r->field( $f );
368             $target_r->insert_fields_ordered( @new_fields );
369         }
370     }
371
372     $target_xml = $target_r->as_xml_record;
373     $target_xml =~ s/^<\?.+?\?>$//mo;
374     $target_xml =~ s/\n//sgo;
375     $target_xml =~ s/>\s+</></sgo;
376
377     return $target_xml;
378
379 $_$ LANGUAGE PLPERLU;
380
381 CREATE OR REPLACE FUNCTION vandelay.add_field ( target_xml TEXT, source_xml TEXT, field TEXT ) RETURNS TEXT AS $_$
382     SELECT vandelay.add_field( $1, $2, $3, 0 );
383 $_$ LANGUAGE SQL;
384
385 CREATE OR REPLACE FUNCTION vandelay.strip_field ( xml TEXT, field TEXT ) RETURNS TEXT AS $_$
386
387     use MARC::Record;
388     use MARC::File::XML (BinaryEncoding => 'UTF-8');
389     use strict;
390
391     my $xml = shift;
392     my $r = MARC::Record->new_from_xml( $xml );
393
394     return $xml unless ($r);
395
396     my $field_spec = shift;
397     my @field_list = split(',', $field_spec);
398
399     my %fields;
400     for my $f (@field_list) {
401         $f =~ s/^\s*//; $f =~ s/\s*$//;
402         if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
403             my $field = $1;
404             $field =~ s/\s+//;
405             my $sf = $2;
406             $sf =~ s/\s+//;
407             my $match = $3;
408             $match =~ s/^\s*//; $match =~ s/\s*$//;
409             $fields{$field} = { sf => [ split('', $sf) ] };
410             if ($match) {
411                 my ($msf,$mre) = split('~', $match);
412                 if (length($msf) > 0 and length($mre) > 0) {
413                     $msf =~ s/^\s*//; $msf =~ s/\s*$//;
414                     $mre =~ s/^\s*//; $mre =~ s/\s*$//;
415                     $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
416                 }
417             }
418         }
419     }
420
421     for my $f ( keys %fields) {
422         for my $to_field ($r->field( $f )) {
423             if (exists($fields{$f}{match})) {
424                 next unless (grep { $_ =~ $fields{$f}{match}{re} } $to_field->subfield($fields{$f}{match}{sf}));
425             }
426
427             if ( @{$fields{$f}{sf}} ) {
428                 $to_field->delete_subfield(code => $fields{$f}{sf});
429             } else {
430                 $r->delete_field( $to_field );
431             }
432         }
433     }
434
435     $xml = $r->as_xml_record;
436     $xml =~ s/^<\?.+?\?>$//mo;
437     $xml =~ s/\n//sgo;
438     $xml =~ s/>\s+</></sgo;
439
440     return $xml;
441
442 $_$ LANGUAGE PLPERLU;
443
444 CREATE OR REPLACE FUNCTION vandelay.replace_field ( target_xml TEXT, source_xml TEXT, field TEXT ) RETURNS TEXT AS $_$
445 DECLARE
446     xml_output TEXT;
447     parsed_target TEXT;
448     curr_field TEXT;
449 BEGIN
450
451     parsed_target := vandelay.strip_field( target_xml, ''); -- this dance normalizes the format of the xml for the IF below
452
453     FOR curr_field IN SELECT UNNEST( STRING_TO_ARRAY(field, ',') ) LOOP -- naive split, but it's the same we use in the perl
454
455         xml_output := vandelay.strip_field( parsed_target, curr_field);
456
457         IF xml_output <> parsed_target  AND curr_field ~ E'~' THEN
458             -- we removed something, and there was a regexp restriction in the curr_field definition, so proceed
459             xml_output := vandelay.add_field( xml_output, source_xml, curr_field, 1 );
460         ELSIF curr_field !~ E'~' THEN
461             -- No regexp restriction, add the curr_field
462             xml_output := vandelay.add_field( xml_output, source_xml, curr_field, 0 );
463         END IF;
464
465         parsed_target := xml_output; -- in prep for any following loop iterations
466
467     END LOOP;
468
469     RETURN xml_output;
470 END;
471 $_$ LANGUAGE PLPGSQL;
472
473 CREATE OR REPLACE FUNCTION vandelay.merge_record_xml ( target_xml TEXT, source_xml TEXT, add_rule TEXT, replace_preserve_rule TEXT, strip_rule TEXT ) RETURNS TEXT AS $_$
474     SELECT vandelay.replace_field( vandelay.add_field( vandelay.strip_field( $1, $5) , $2, $3 ), $2, $4);
475 $_$ LANGUAGE SQL;
476
477 CREATE TYPE vandelay.compile_profile AS (add_rule TEXT, replace_rule TEXT, preserve_rule TEXT, strip_rule TEXT);
478 CREATE OR REPLACE FUNCTION vandelay.compile_profile ( incoming_xml TEXT ) RETURNS vandelay.compile_profile AS $_$
479 DECLARE
480     output              vandelay.compile_profile%ROWTYPE;
481     profile             vandelay.merge_profile%ROWTYPE;
482     profile_tmpl        TEXT;
483     profile_tmpl_owner  TEXT;
484     add_rule            TEXT := '';
485     strip_rule          TEXT := '';
486     replace_rule        TEXT := '';
487     preserve_rule       TEXT := '';
488
489 BEGIN
490
491     profile_tmpl := (oils_xpath('//*[@tag="905"]/*[@code="t"]/text()',incoming_xml))[1];
492     profile_tmpl_owner := (oils_xpath('//*[@tag="905"]/*[@code="o"]/text()',incoming_xml))[1];
493
494     IF profile_tmpl IS NOT NULL AND profile_tmpl <> '' AND profile_tmpl_owner IS NOT NULL AND profile_tmpl_owner <> '' THEN
495         SELECT  p.* INTO profile
496           FROM  vandelay.merge_profile p
497                 JOIN actor.org_unit u ON (u.id = p.owner)
498           WHERE p.name = profile_tmpl
499                 AND u.shortname = profile_tmpl_owner;
500
501         IF profile.id IS NOT NULL THEN
502             add_rule := COALESCE(profile.add_spec,'');
503             strip_rule := COALESCE(profile.strip_spec,'');
504             replace_rule := COALESCE(profile.replace_spec,'');
505             preserve_rule := COALESCE(profile.preserve_spec,'');
506         END IF;
507     END IF;
508
509     add_rule := add_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="a"]/text()',incoming_xml),','),'');
510     strip_rule := strip_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="d"]/text()',incoming_xml),','),'');
511     replace_rule := replace_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="r"]/text()',incoming_xml),','),'');
512     preserve_rule := preserve_rule || ',' || COALESCE(ARRAY_TO_STRING(oils_xpath('//*[@tag="905"]/*[@code="p"]/text()',incoming_xml),','),'');
513
514     output.add_rule := BTRIM(add_rule,',');
515     output.replace_rule := BTRIM(replace_rule,',');
516     output.strip_rule := BTRIM(strip_rule,',');
517     output.preserve_rule := BTRIM(preserve_rule,',');
518
519     RETURN output;
520 END;
521 $_$ LANGUAGE PLPGSQL;
522
523 CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
524 DECLARE
525     merge_profile   vandelay.merge_profile%ROWTYPE;
526     dyn_profile     vandelay.compile_profile%ROWTYPE;
527     editor_string   TEXT;
528     editor_id       INT;
529     source_marc     TEXT;
530     target_marc     TEXT;
531     eg_marc         TEXT;
532     replace_rule    TEXT;
533     match_count     INT;
534 BEGIN
535
536     SELECT  b.marc INTO eg_marc
537       FROM  biblio.record_entry b
538       WHERE b.id = eg_id
539       LIMIT 1;
540
541     IF eg_marc IS NULL OR v_marc IS NULL THEN
542         -- RAISE NOTICE 'no marc for template or bib record';
543         RETURN FALSE;
544     END IF;
545
546     dyn_profile := vandelay.compile_profile( v_marc );
547
548     IF merge_profile_id IS NOT NULL THEN
549         SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
550         IF FOUND THEN
551             dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
552             dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
553             dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
554             dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
555         END IF;
556     END IF;
557
558     IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
559         -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
560         RETURN FALSE;
561     END IF;
562
563     IF dyn_profile.replace_rule <> '' THEN
564         source_marc = v_marc;
565         target_marc = eg_marc;
566         replace_rule = dyn_profile.replace_rule;
567     ELSE
568         source_marc = eg_marc;
569         target_marc = v_marc;
570         replace_rule = dyn_profile.preserve_rule;
571     END IF;
572
573     UPDATE  biblio.record_entry
574       SET   marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
575       WHERE id = eg_id;
576
577     IF NOT FOUND THEN
578         -- RAISE NOTICE 'update of biblio.record_entry failed';
579         RETURN FALSE;
580     END IF;
581
582     RETURN TRUE;
583
584 END;
585 $$ LANGUAGE PLPGSQL;
586
587 CREATE OR REPLACE FUNCTION vandelay.merge_record_xml ( target_marc TEXT, template_marc TEXT ) RETURNS TEXT AS $$
588 DECLARE
589     dyn_profile     vandelay.compile_profile%ROWTYPE;
590     replace_rule    TEXT;
591     tmp_marc        TEXT;
592     trgt_marc        TEXT;
593     tmpl_marc        TEXT;
594     match_count     INT;
595 BEGIN
596
597     IF target_marc IS NULL OR template_marc IS NULL THEN
598         -- RAISE NOTICE 'no marc for target or template record';
599         RETURN NULL;
600     END IF;
601
602     dyn_profile := vandelay.compile_profile( template_marc );
603
604     IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
605         -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
606         RETURN NULL;
607     END IF;
608
609     IF dyn_profile.replace_rule <> '' THEN
610         trgt_marc = target_marc;
611         tmpl_marc = template_marc;
612         replace_rule = dyn_profile.replace_rule;
613     ELSE
614         tmp_marc = target_marc;
615         trgt_marc = template_marc;
616         tmpl_marc = tmp_marc;
617         replace_rule = dyn_profile.preserve_rule;
618     END IF;
619
620     RETURN vandelay.merge_record_xml( trgt_marc, tmpl_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule );
621
622 END;
623 $$ LANGUAGE PLPGSQL;
624
625 CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT) RETURNS BOOL AS $$
626     SELECT vandelay.template_overlay_bib_record( $1, $2, NULL);
627 $$ LANGUAGE SQL;
628
629 CREATE OR REPLACE FUNCTION vandelay.overlay_bib_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
630 DECLARE
631     merge_profile   vandelay.merge_profile%ROWTYPE;
632     dyn_profile     vandelay.compile_profile%ROWTYPE;
633     editor_string   TEXT;
634     editor_id       INT;
635     source_marc     TEXT;
636     target_marc     TEXT;
637     eg_marc         TEXT;
638     v_marc          TEXT;
639     replace_rule    TEXT;
640     match_count     INT;
641 BEGIN
642
643     SELECT  q.marc INTO v_marc
644       FROM  vandelay.queued_record q
645             JOIN vandelay.bib_match m ON (m.queued_record = q.id AND q.id = import_id)
646       LIMIT 1;
647
648     IF v_marc IS NULL THEN
649         -- RAISE NOTICE 'no marc for vandelay or bib record';
650         RETURN FALSE;
651     END IF;
652
653     IF vandelay.template_overlay_bib_record( v_marc, eg_id, merge_profile_id) THEN
654         UPDATE  vandelay.queued_bib_record
655           SET   imported_as = eg_id,
656                 import_time = NOW()
657           WHERE id = import_id;
658
659         editor_string := (oils_xpath('//*[@tag="905"]/*[@code="u"]/text()',v_marc))[1];
660
661         IF editor_string IS NOT NULL AND editor_string <> '' THEN
662             SELECT usr INTO editor_id FROM actor.card WHERE barcode = editor_string;
663
664             IF editor_id IS NULL THEN
665                 SELECT id INTO editor_id FROM actor.usr WHERE usrname = editor_string;
666             END IF;
667
668             IF editor_id IS NOT NULL THEN
669                 UPDATE biblio.record_entry SET editor = editor_id WHERE id = eg_id;
670             END IF;
671         END IF;
672
673         RETURN TRUE;
674     END IF;
675
676     -- RAISE NOTICE 'update of biblio.record_entry failed';
677
678     RETURN FALSE;
679
680 END;
681 $$ LANGUAGE PLPGSQL;
682
683 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
684 DECLARE
685     eg_id           BIGINT;
686     match_count     INT;
687     match_attr      vandelay.bib_attr_definition%ROWTYPE;
688 BEGIN
689
690     PERFORM * FROM vandelay.queued_bib_record WHERE import_time IS NOT NULL AND id = import_id;
691
692     IF FOUND THEN
693         -- RAISE NOTICE 'already imported, cannot auto-overlay'
694         RETURN FALSE;
695     END IF;
696
697     SELECT COUNT(*) INTO match_count FROM vandelay.bib_match WHERE queued_record = import_id;
698
699     IF match_count <> 1 THEN
700         -- RAISE NOTICE 'not an exact match';
701         RETURN FALSE;
702     END IF;
703
704     SELECT  d.* INTO match_attr
705       FROM  vandelay.bib_attr_definition d
706             JOIN vandelay.queued_bib_record_attr a ON (a.field = d.id)
707             JOIN vandelay.bib_match m ON (m.matched_attr = a.id)
708       WHERE m.queued_record = import_id;
709
710     IF NOT (match_attr.xpath ~ '@tag="901"' AND match_attr.xpath ~ '@code="c"') THEN
711         -- RAISE NOTICE 'not a 901c match: %', match_attr.xpath;
712         RETURN FALSE;
713     END IF;
714
715     SELECT  m.eg_record INTO eg_id
716       FROM  vandelay.bib_match m
717       WHERE m.queued_record = import_id
718       LIMIT 1;
719
720     IF eg_id IS NULL THEN
721         RETURN FALSE;
722     END IF;
723
724     RETURN vandelay.overlay_bib_record( import_id, eg_id, merge_profile_id );
725 END;
726 $$ LANGUAGE PLPGSQL;
727
728 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
729 DECLARE
730     queued_record   vandelay.queued_bib_record%ROWTYPE;
731 BEGIN
732
733     FOR queued_record IN SELECT * FROM vandelay.queued_bib_record WHERE queue = queue_id AND import_time IS NULL LOOP
734
735         IF vandelay.auto_overlay_bib_record( queued_record.id, merge_profile_id ) THEN
736             RETURN NEXT queued_record.id;
737         END IF;
738
739     END LOOP;
740
741     RETURN;
742     
743 END;
744 $$ LANGUAGE PLPGSQL;
745
746 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
747     SELECT * FROM vandelay.auto_overlay_bib_queue( $1, NULL );
748 $$ LANGUAGE SQL;
749
750 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
751 DECLARE
752
753     owning_lib      TEXT;
754     circ_lib        TEXT;
755     call_number     TEXT;
756     copy_number     TEXT;
757     status          TEXT;
758     location        TEXT;
759     circulate       TEXT;
760     deposit         TEXT;
761     deposit_amount  TEXT;
762     ref             TEXT;
763     holdable        TEXT;
764     price           TEXT;
765     barcode         TEXT;
766     circ_modifier   TEXT;
767     circ_as_type    TEXT;
768     alert_message   TEXT;
769     opac_visible    TEXT;
770     pub_note        TEXT;
771     priv_note       TEXT;
772
773     attr_def        RECORD;
774     tmp_attr_set    RECORD;
775     attr_set        vandelay.import_item%ROWTYPE;
776
777     xpath           TEXT;
778
779 BEGIN
780
781     SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
782
783     IF FOUND THEN
784
785         attr_set.definition := attr_def.id; 
786     
787         -- Build the combined XPath
788     
789         owning_lib :=
790             CASE
791                 WHEN attr_def.owning_lib IS NULL THEN 'null()'
792                 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
793                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
794             END;
795     
796         circ_lib :=
797             CASE
798                 WHEN attr_def.circ_lib IS NULL THEN 'null()'
799                 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
800                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
801             END;
802     
803         call_number :=
804             CASE
805                 WHEN attr_def.call_number IS NULL THEN 'null()'
806                 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
807                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
808             END;
809     
810         copy_number :=
811             CASE
812                 WHEN attr_def.copy_number IS NULL THEN 'null()'
813                 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
814                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
815             END;
816     
817         status :=
818             CASE
819                 WHEN attr_def.status IS NULL THEN 'null()'
820                 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
821                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
822             END;
823     
824         location :=
825             CASE
826                 WHEN attr_def.location IS NULL THEN 'null()'
827                 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
828                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
829             END;
830     
831         circulate :=
832             CASE
833                 WHEN attr_def.circulate IS NULL THEN 'null()'
834                 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
835                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
836             END;
837     
838         deposit :=
839             CASE
840                 WHEN attr_def.deposit IS NULL THEN 'null()'
841                 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
842                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
843             END;
844     
845         deposit_amount :=
846             CASE
847                 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
848                 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
849                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
850             END;
851     
852         ref :=
853             CASE
854                 WHEN attr_def.ref IS NULL THEN 'null()'
855                 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
856                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
857             END;
858     
859         holdable :=
860             CASE
861                 WHEN attr_def.holdable IS NULL THEN 'null()'
862                 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
863                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
864             END;
865     
866         price :=
867             CASE
868                 WHEN attr_def.price IS NULL THEN 'null()'
869                 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
870                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
871             END;
872     
873         barcode :=
874             CASE
875                 WHEN attr_def.barcode IS NULL THEN 'null()'
876                 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
877                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
878             END;
879     
880         circ_modifier :=
881             CASE
882                 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
883                 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
884                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
885             END;
886     
887         circ_as_type :=
888             CASE
889                 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
890                 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
891                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
892             END;
893     
894         alert_message :=
895             CASE
896                 WHEN attr_def.alert_message IS NULL THEN 'null()'
897                 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
898                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
899             END;
900     
901         opac_visible :=
902             CASE
903                 WHEN attr_def.opac_visible IS NULL THEN 'null()'
904                 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
905                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
906             END;
907
908         pub_note :=
909             CASE
910                 WHEN attr_def.pub_note IS NULL THEN 'null()'
911                 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
912                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
913             END;
914         priv_note :=
915             CASE
916                 WHEN attr_def.priv_note IS NULL THEN 'null()'
917                 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
918                 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
919             END;
920     
921     
922         xpath := 
923             owning_lib      || '|' || 
924             circ_lib        || '|' || 
925             call_number     || '|' || 
926             copy_number     || '|' || 
927             status          || '|' || 
928             location        || '|' || 
929             circulate       || '|' || 
930             deposit         || '|' || 
931             deposit_amount  || '|' || 
932             ref             || '|' || 
933             holdable        || '|' || 
934             price           || '|' || 
935             barcode         || '|' || 
936             circ_modifier   || '|' || 
937             circ_as_type    || '|' || 
938             alert_message   || '|' || 
939             pub_note        || '|' || 
940             priv_note       || '|' || 
941             opac_visible;
942
943         -- RAISE NOTICE 'XPath: %', xpath;
944         
945         FOR tmp_attr_set IN
946                 SELECT  *
947                   FROM  oils_xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
948                             AS t( id INT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
949                                   dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
950                                   circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, opac_vis TEXT )
951         LOOP
952     
953             tmp_attr_set.pr = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
954             tmp_attr_set.dep_amount = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
955
956             tmp_attr_set.pr := NULLIF( tmp_attr_set.pr, '' );
957             tmp_attr_set.dep_amount := NULLIF( tmp_attr_set.dep_amount, '' );
958     
959             SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
960             SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
961             SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
962     
963             SELECT  id INTO attr_set.location
964               FROM  asset.copy_location
965               WHERE LOWER(name) = LOWER(tmp_attr_set.cl)
966                     AND asset.copy_location.owning_lib = COALESCE(attr_set.owning_lib, attr_set.circ_lib); -- INT
967     
968             attr_set.circulate      :=
969                 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
970                 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
971
972             attr_set.deposit        :=
973                 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
974                 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
975
976             attr_set.holdable       :=
977                 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
978                 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
979
980             attr_set.opac_visible   :=
981                 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
982                 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
983
984             attr_set.ref            :=
985                 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
986                 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
987     
988             attr_set.copy_number    := tmp_attr_set.cnum::INT; -- INT,
989             attr_set.deposit_amount := tmp_attr_set.dep_amount::NUMERIC(6,2); -- NUMERIC(6,2),
990             attr_set.price          := tmp_attr_set.pr::NUMERIC(8,2); -- NUMERIC(8,2),
991     
992             attr_set.call_number    := tmp_attr_set.cn; -- TEXT
993             attr_set.barcode        := tmp_attr_set.bc; -- TEXT,
994             attr_set.circ_modifier  := tmp_attr_set.circ_mod; -- TEXT,
995             attr_set.circ_as_type   := tmp_attr_set.circ_as; -- TEXT,
996             attr_set.alert_message  := tmp_attr_set.amessage; -- TEXT,
997             attr_set.pub_note       := tmp_attr_set.note; -- TEXT,
998             attr_set.priv_note      := tmp_attr_set.pnote; -- TEXT,
999             attr_set.alert_message  := tmp_attr_set.amessage; -- TEXT,
1000     
1001             RETURN NEXT attr_set;
1002     
1003         END LOOP;
1004     
1005     END IF;
1006
1007     RETURN;
1008
1009 END;
1010 $$ LANGUAGE PLPGSQL;
1011
1012
1013 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_marc ( ) RETURNS TRIGGER AS $$
1014 DECLARE
1015     value   TEXT;
1016     atype   TEXT;
1017     adef    RECORD;
1018 BEGIN
1019     FOR adef IN SELECT * FROM vandelay.bib_attr_definition LOOP
1020
1021         SELECT extract_marc_field('vandelay.queued_bib_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_bib_record WHERE id = NEW.id;
1022         IF (value IS NOT NULL AND value <> '') THEN
1023             INSERT INTO vandelay.queued_bib_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
1024         END IF;
1025
1026     END LOOP;
1027
1028     RETURN NULL;
1029 END;
1030 $$ LANGUAGE PLPGSQL;
1031
1032 CREATE OR REPLACE FUNCTION vandelay.ingest_bib_items ( ) RETURNS TRIGGER AS $func$
1033 DECLARE
1034     attr_def    BIGINT;
1035     item_data   vandelay.import_item%ROWTYPE;
1036 BEGIN
1037
1038     SELECT item_attr_def INTO attr_def FROM vandelay.bib_queue WHERE id = NEW.queue;
1039
1040     FOR item_data IN SELECT * FROM vandelay.ingest_items( NEW.id::BIGINT, attr_def ) LOOP
1041         INSERT INTO vandelay.import_item (
1042             record,
1043             definition,
1044             owning_lib,
1045             circ_lib,
1046             call_number,
1047             copy_number,
1048             status,
1049             location,
1050             circulate,
1051             deposit,
1052             deposit_amount,
1053             ref,
1054             holdable,
1055             price,
1056             barcode,
1057             circ_modifier,
1058             circ_as_type,
1059             alert_message,
1060             pub_note,
1061             priv_note,
1062             opac_visible
1063         ) VALUES (
1064             NEW.id,
1065             item_data.definition,
1066             item_data.owning_lib,
1067             item_data.circ_lib,
1068             item_data.call_number,
1069             item_data.copy_number,
1070             item_data.status,
1071             item_data.location,
1072             item_data.circulate,
1073             item_data.deposit,
1074             item_data.deposit_amount,
1075             item_data.ref,
1076             item_data.holdable,
1077             item_data.price,
1078             item_data.barcode,
1079             item_data.circ_modifier,
1080             item_data.circ_as_type,
1081             item_data.alert_message,
1082             item_data.pub_note,
1083             item_data.priv_note,
1084             item_data.opac_visible
1085         );
1086     END LOOP;
1087
1088     RETURN NULL;
1089 END;
1090 $func$ LANGUAGE PLPGSQL;
1091
1092 CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$
1093 DECLARE
1094     attr        RECORD;
1095     attr_def    RECORD;
1096     eg_rec      RECORD;
1097     id_value    TEXT;
1098     exact_id    BIGINT;
1099 BEGIN
1100
1101     DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id;
1102
1103     SELECT * INTO attr_def FROM vandelay.bib_attr_definition WHERE xpath = '//*[@tag="901"]/*[@code="c"]' ORDER BY id LIMIT 1;
1104
1105     IF attr_def IS NOT NULL AND attr_def.id IS NOT NULL THEN
1106         id_value := extract_marc_field('vandelay.queued_bib_record', NEW.id, attr_def.xpath, attr_def.remove);
1107     
1108         IF id_value IS NOT NULL AND id_value <> '' AND id_value ~ $r$^\d+$$r$ THEN
1109             SELECT id INTO exact_id FROM biblio.record_entry WHERE id = id_value::BIGINT AND NOT deleted;
1110             SELECT * INTO attr FROM vandelay.queued_bib_record_attr WHERE record = NEW.id and field = attr_def.id LIMIT 1;
1111             IF exact_id IS NOT NULL THEN
1112                 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, exact_id);
1113             END IF;
1114         END IF;
1115     END IF;
1116
1117     IF exact_id IS NULL THEN
1118         FOR attr IN SELECT a.* FROM vandelay.queued_bib_record_attr a JOIN vandelay.bib_attr_definition d ON (d.id = a.field) WHERE record = NEW.id AND d.ident IS TRUE LOOP
1119     
1120                 -- All numbers? check for an id match
1121                 IF (attr.attr_value ~ $r$^\d+$$r$) THEN
1122                 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE id = attr.attr_value::BIGINT AND deleted IS FALSE LOOP
1123                         INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
1124                         END LOOP;
1125                 END IF;
1126     
1127                 -- Looks like an ISBN? check for an isbn match
1128                 IF (attr.attr_value ~* $r$^[0-9x]+$$r$ AND character_length(attr.attr_value) IN (10,13)) THEN
1129                 FOR eg_rec IN EXECUTE $$SELECT * FROM metabib.full_rec fr WHERE fr.value LIKE evergreen.lowercase('$$ || attr.attr_value || $$%') AND fr.tag = '020' AND fr.subfield = 'a'$$ LOOP
1130                                 PERFORM id FROM biblio.record_entry WHERE id = eg_rec.record AND deleted IS FALSE;
1131                                 IF FOUND THEN
1132                                 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record);
1133                                 END IF;
1134                         END LOOP;
1135     
1136                         -- subcheck for isbn-as-tcn
1137                     FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = 'i' || attr.attr_value AND deleted IS FALSE LOOP
1138                             INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1139                 END LOOP;
1140                 END IF;
1141     
1142                 -- check for an OCLC tcn_value match
1143                 IF (attr.attr_value ~ $r$^o\d+$$r$) THEN
1144                     FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = regexp_replace(attr.attr_value,'^o','ocm') AND deleted IS FALSE LOOP
1145                             INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1146                 END LOOP;
1147                 END IF;
1148     
1149                 -- check for a direct tcn_value match
1150             FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = attr.attr_value AND deleted IS FALSE LOOP
1151                 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
1152             END LOOP;
1153     
1154                 -- check for a direct item barcode match
1155             FOR eg_rec IN
1156                     SELECT  DISTINCT b.*
1157                       FROM  biblio.record_entry b
1158                             JOIN asset.call_number cn ON (cn.record = b.id)
1159                             JOIN asset.copy cp ON (cp.call_number = cn.id)
1160                       WHERE cp.barcode = attr.attr_value AND cp.deleted IS FALSE
1161             LOOP
1162                 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
1163             END LOOP;
1164     
1165         END LOOP;
1166     END IF;
1167
1168     RETURN NULL;
1169 END;
1170 $func$ LANGUAGE PLPGSQL;
1171
1172 CREATE OR REPLACE FUNCTION vandelay.cleanup_bib_marc ( ) RETURNS TRIGGER AS $$
1173 BEGIN
1174     DELETE FROM vandelay.queued_bib_record_attr WHERE record = OLD.id;
1175     DELETE FROM vandelay.import_item WHERE record = OLD.id;
1176
1177     IF TG_OP = 'UPDATE' THEN
1178         RETURN NEW;
1179     END IF;
1180     RETURN OLD;
1181 END;
1182 $$ LANGUAGE PLPGSQL;
1183
1184 CREATE TRIGGER cleanup_bib_trigger
1185     BEFORE UPDATE OR DELETE ON vandelay.queued_bib_record
1186     FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_bib_marc();
1187
1188 CREATE TRIGGER ingest_bib_trigger
1189     AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1190     FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_marc();
1191
1192 CREATE TRIGGER ingest_item_trigger
1193     AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1194     FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_bib_items();
1195
1196 CREATE TRIGGER zz_match_bibs_trigger
1197     AFTER INSERT OR UPDATE ON vandelay.queued_bib_record
1198     FOR EACH ROW EXECUTE PROCEDURE vandelay.match_bib_record();
1199
1200
1201 /* Authority stuff down here */
1202 ---------------------------------------
1203 CREATE TABLE vandelay.authority_attr_definition (
1204         id                      SERIAL  PRIMARY KEY,
1205         code            TEXT    UNIQUE NOT NULL,
1206         description     TEXT,
1207         xpath           TEXT    NOT NULL,
1208         remove          TEXT    NOT NULL DEFAULT '',
1209         ident           BOOL    NOT NULL DEFAULT FALSE
1210 );
1211
1212 CREATE TABLE vandelay.authority_queue (
1213         queue_type      TEXT            NOT NULL DEFAULT 'authority' CHECK (queue_type = 'authority'),
1214         CONSTRAINT vand_authority_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type)
1215 ) INHERITS (vandelay.queue);
1216 ALTER TABLE vandelay.authority_queue ADD PRIMARY KEY (id);
1217
1218 CREATE TABLE vandelay.queued_authority_record (
1219         queue           INT     NOT NULL REFERENCES vandelay.authority_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1220         imported_as     INT     REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
1221 ) INHERITS (vandelay.queued_record);
1222 ALTER TABLE vandelay.queued_authority_record ADD PRIMARY KEY (id);
1223 CREATE INDEX queued_authority_record_queue_idx ON vandelay.queued_authority_record (queue);
1224
1225 CREATE TABLE vandelay.queued_authority_record_attr (
1226         id                      BIGSERIAL       PRIMARY KEY,
1227         record          BIGINT          NOT NULL REFERENCES vandelay.queued_authority_record (id) DEFERRABLE INITIALLY DEFERRED,
1228         field           INT                     NOT NULL REFERENCES vandelay.authority_attr_definition (id) DEFERRABLE INITIALLY DEFERRED,
1229         attr_value      TEXT            NOT NULL
1230 );
1231 CREATE INDEX queued_authority_record_attr_record_idx ON vandelay.queued_authority_record_attr (record);
1232
1233 CREATE TABLE vandelay.authority_match (
1234         id                              BIGSERIAL       PRIMARY KEY,
1235         matched_attr    INT                     REFERENCES vandelay.queued_authority_record_attr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1236         queued_record   BIGINT          REFERENCES vandelay.queued_authority_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1237         eg_record               BIGINT          REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED
1238 );
1239
1240 CREATE OR REPLACE FUNCTION vandelay.ingest_authority_marc ( ) RETURNS TRIGGER AS $$
1241 DECLARE
1242     value   TEXT;
1243     atype   TEXT;
1244     adef    RECORD;
1245 BEGIN
1246     FOR adef IN SELECT * FROM vandelay.authority_attr_definition LOOP
1247
1248         SELECT extract_marc_field('vandelay.queued_authority_record', id, adef.xpath, adef.remove) INTO value FROM vandelay.queued_authority_record WHERE id = NEW.id;
1249         IF (value IS NOT NULL AND value <> '') THEN
1250             INSERT INTO vandelay.queued_authority_record_attr (record, field, attr_value) VALUES (NEW.id, adef.id, value);
1251         END IF;
1252
1253     END LOOP;
1254
1255     RETURN NULL;
1256 END;
1257 $$ LANGUAGE PLPGSQL;
1258
1259 CREATE OR REPLACE FUNCTION vandelay.cleanup_authority_marc ( ) RETURNS TRIGGER AS $$
1260 BEGIN
1261     DELETE FROM vandelay.queued_authority_record_attr WHERE record = OLD.id;
1262     IF TG_OP = 'UPDATE' THEN
1263         RETURN NEW;
1264     END IF;
1265     RETURN OLD;
1266 END;
1267 $$ LANGUAGE PLPGSQL;
1268
1269 CREATE TRIGGER cleanup_authority_trigger
1270     BEFORE UPDATE OR DELETE ON vandelay.queued_authority_record
1271     FOR EACH ROW EXECUTE PROCEDURE vandelay.cleanup_authority_marc();
1272
1273 CREATE TRIGGER ingest_authority_trigger
1274     AFTER INSERT OR UPDATE ON vandelay.queued_authority_record
1275     FOR EACH ROW EXECUTE PROCEDURE vandelay.ingest_authority_marc();
1276
1277 CREATE OR REPLACE FUNCTION vandelay.overlay_authority_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1278 DECLARE
1279     merge_profile   vandelay.merge_profile%ROWTYPE;
1280     dyn_profile     vandelay.compile_profile%ROWTYPE;
1281     source_marc     TEXT;
1282     target_marc     TEXT;
1283     eg_marc         TEXT;
1284     v_marc          TEXT;
1285     replace_rule    TEXT;
1286     match_count     INT;
1287 BEGIN
1288
1289     SELECT  b.marc INTO eg_marc
1290       FROM  authority.record_entry b
1291             JOIN vandelay.authority_match m ON (m.eg_record = b.id AND m.queued_record = import_id)
1292       LIMIT 1;
1293
1294     SELECT  q.marc INTO v_marc
1295       FROM  vandelay.queued_record q
1296             JOIN vandelay.authority_match m ON (m.queued_record = q.id AND q.id = import_id)
1297       LIMIT 1;
1298
1299     IF eg_marc IS NULL OR v_marc IS NULL THEN
1300         -- RAISE NOTICE 'no marc for vandelay or authority record';
1301         RETURN FALSE;
1302     END IF;
1303
1304     dyn_profile := vandelay.compile_profile( v_marc );
1305
1306     IF merge_profile_id IS NOT NULL THEN
1307         SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
1308         IF FOUND THEN
1309             dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
1310             dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
1311             dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
1312             dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
1313         END IF;
1314     END IF;
1315
1316     IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
1317         -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
1318         RETURN FALSE;
1319     END IF;
1320
1321     IF dyn_profile.replace_rule <> '' THEN
1322         source_marc = v_marc;
1323         target_marc = eg_marc;
1324         replace_rule = dyn_profile.replace_rule;
1325     ELSE
1326         source_marc = eg_marc;
1327         target_marc = v_marc;
1328         replace_rule = dyn_profile.preserve_rule;
1329     END IF;
1330
1331     UPDATE  authority.record_entry
1332       SET   marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
1333       WHERE id = eg_id;
1334
1335     IF FOUND THEN
1336         UPDATE  vandelay.queued_authority_record
1337           SET   imported_as = eg_id,
1338                 import_time = NOW()
1339           WHERE id = import_id;
1340         RETURN TRUE;
1341     END IF;
1342
1343     -- RAISE NOTICE 'update of authority.record_entry failed';
1344
1345     RETURN FALSE;
1346
1347 END;
1348 $$ LANGUAGE PLPGSQL;
1349
1350 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_record ( import_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
1351 DECLARE
1352     eg_id           BIGINT;
1353     match_count     INT;
1354 BEGIN
1355     SELECT COUNT(*) INTO match_count FROM vandelay.authority_match WHERE queued_record = import_id;
1356
1357     IF match_count <> 1 THEN
1358         -- RAISE NOTICE 'not an exact match';
1359         RETURN FALSE;
1360     END IF;
1361
1362     SELECT  m.eg_record INTO eg_id
1363       FROM  vandelay.authority_match m
1364       WHERE m.queued_record = import_id
1365       LIMIT 1;
1366
1367     IF eg_id IS NULL THEN
1368         RETURN FALSE;
1369     END IF;
1370
1371     RETURN vandelay.overlay_authority_record( import_id, eg_id, merge_profile_id );
1372 END;
1373 $$ LANGUAGE PLPGSQL;
1374
1375 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
1376 DECLARE
1377     queued_record   vandelay.queued_authority_record%ROWTYPE;
1378 BEGIN
1379
1380     FOR queued_record IN SELECT * FROM vandelay.queued_authority_record WHERE queue = queue_id AND import_time IS NULL LOOP
1381
1382         IF vandelay.auto_overlay_authority_record( queued_record.id, merge_profile_id ) THEN
1383             RETURN NEXT queued_record.id;
1384         END IF;
1385
1386     END LOOP;
1387
1388     RETURN;
1389     
1390 END;
1391 $$ LANGUAGE PLPGSQL;
1392
1393 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT ) RETURNS SETOF BIGINT AS $$
1394     SELECT * FROM vandelay.auto_overlay_authority_queue( $1, NULL );
1395 $$ LANGUAGE SQL;
1396
1397
1398 -- Vandelay (for importing and exporting records) 012.schema.vandelay.sql 
1399 --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath ) VALUES (1, 'title', oils_i18n_gettext(1, 'vqbrad', 'Title of work', 'description'),'//*[@tag="245"]/*[contains("abcmnopr",@code)]');
1400 --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath ) VALUES (2, 'author', oils_i18n_gettext(1, 'vqbrad', 'Author of work', 'description'),'//*[@tag="100" or @tag="110" or @tag="113"]/*[contains("ad",@code)]');
1401 --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath ) VALUES (3, 'language', oils_i18n_gettext(3, 'vqbrad', 'Language of work', 'description'),'//*[@tag="240"]/*[@code="l"][1]');
1402 --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath ) VALUES (4, 'pagination', oils_i18n_gettext(4, 'vqbrad', 'Pagination', 'description'),'//*[@tag="300"]/*[@code="a"][1]');
1403 --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath, ident, remove ) VALUES (5, 'isbn',oils_i18n_gettext(5, 'vqbrad', 'ISBN', 'description'),'//*[@tag="020"]/*[@code="a"]', TRUE, $r$(?:-|\s.+$)$r$);
1404 --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath, ident, remove ) VALUES (6, 'issn',oils_i18n_gettext(6, 'vqbrad', 'ISSN', 'description'),'//*[@tag="022"]/*[@code="a"]', TRUE, $r$(?:-|\s.+$)$r$);
1405 --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath ) VALUES (7, 'price',oils_i18n_gettext(7, 'vqbrad', 'Price', 'description'),'//*[@tag="020" or @tag="022"]/*[@code="c"][1]');
1406 --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath, ident ) VALUES (8, 'rec_identifier',oils_i18n_gettext(8, 'vqbrad', 'Accession Number', 'description'),'//*[@tag="001"]', TRUE);
1407 --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath, ident ) VALUES (9, 'eg_tcn',oils_i18n_gettext(9, 'vqbrad', 'TCN Value', 'description'),'//*[@tag="901"]/*[@code="a"]', TRUE);
1408 --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath, ident ) VALUES (10, 'eg_tcn_source',oils_i18n_gettext(10, 'vqbrad', 'TCN Source', 'description'),'//*[@tag="901"]/*[@code="b"]', TRUE);
1409 --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath, ident ) VALUES (11, 'eg_identifier',oils_i18n_gettext(11, 'vqbrad', 'Internal ID', 'description'),'//*[@tag="901"]/*[@code="c"]', TRUE);
1410 --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath ) VALUES (12, 'publisher',oils_i18n_gettext(12, 'vqbrad', 'Publisher', 'description'),'//*[@tag="260"]/*[@code="b"][1]');
1411 --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath, remove ) VALUES (13, 'pubdate',oils_i18n_gettext(13, 'vqbrad', 'Publication Date', 'description'),'//*[@tag="260"]/*[@code="c"][1]',$r$\D$r$);
1412 --INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath ) VALUES (14, 'edition',oils_i18n_gettext(14, 'vqbrad', 'Edition', 'description'),'//*[@tag="250"]/*[@code="a"][1]');
1413 --
1414 --INSERT INTO vandelay.import_item_attr_definition (
1415 --    owner, name, tag, owning_lib, circ_lib, location,
1416 --    call_number, circ_modifier, barcode, price, copy_number,
1417 --    circulate, ref, holdable, opac_visible, status
1418 --) VALUES (
1419 --    1,
1420 --    'Evergreen 852 export format',
1421 --    '852',
1422 --    '[@code = "b"][1]',
1423 --    '[@code = "b"][2]',
1424 --    'c',
1425 --    'j',
1426 --    'g',
1427 --    'p',
1428 --    'y',
1429 --    't',
1430 --    '[@code = "x" and text() = "circulating"]',
1431 --    '[@code = "x" and text() = "reference"]',
1432 --    '[@code = "x" and text() = "holdable"]',
1433 --    '[@code = "x" and text() = "visible"]',
1434 --    'z'
1435 --);
1436 --
1437 --INSERT INTO vandelay.import_item_attr_definition (
1438 --    owner,
1439 --    name,
1440 --    tag,
1441 --    owning_lib,
1442 --    location,
1443 --    call_number,
1444 --    circ_modifier,
1445 --    barcode,
1446 --    price,
1447 --    status
1448 --) VALUES (
1449 --    1,
1450 --    'Unicorn Import format -- 999',
1451 --    '999',
1452 --    'm',
1453 --    'l',
1454 --    'a',
1455 --    't',
1456 --    'i',
1457 --    'p',
1458 --    'k'
1459 --);
1460 --
1461 --INSERT INTO vandelay.authority_attr_definition ( code, description, xpath, ident ) VALUES ('rec_identifier','Identifier','//*[@tag="001"]', TRUE);
1462
1463 COMMIT;
1464