3 INSERT INTO config.upgrade_log (version) VALUES ('0261'); --miker
5 -- Teach add/delete field/subfield stored procs about data matching
7 CREATE OR REPLACE FUNCTION vandelay.add_field ( target_xml TEXT, source_xml TEXT, field TEXT ) RETURNS TEXT AS $_$
12 my $target_xml = shift;
13 my $source_xml = shift;
14 my $field_spec = shift;
16 my $target_r = MARC::Record->new_from_xml( $target_xml );
17 my $source_r = MARC::Record->new_from_xml( $source_xml );
19 return $target_xml unless ($target_r && $source_r);
21 my @field_list = split(',', $field_spec);
24 for my $f (@field_list) {
25 $f =~ s/^\s*//; $f =~ s/\s*$//;
26 if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
32 $match =~ s/^\s*//; $match =~ s/\s*$//;
33 $fields{$field} = { sf => [ split('', $sf) ] };
35 my ($msf,$mre) = split('~', $match);
36 if (length($msf) > 0 and length($mre) > 0) {
37 $msf =~ s/^\s*//; $msf =~ s/\s*$//;
38 $mre =~ s/^\s*//; $mre =~ s/\s*$//;
39 $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
45 for my $f ( keys %fields) {
46 if ( @{$fields{$f}} ) {
47 for my $from_field ($source_r->field( $f )) {
48 for my $to_field ($target_r->field( $f )) {
49 if (exists($fields{$f}{match})) {
50 next unless (grep { $_ =~ $field{$f}{match}{re} } $to_field->subfield($field{$f}{match}{sf}));
52 my @new_sf = map { ($_ => $from_field->subfield($_)) } @{$fields{$f}};
53 $to_field->add_subfields( @new_sf );
57 my @new_fields = map { $_->clone } $source_r->field( $f );
58 $target_r->insert_fields_ordered( @new_fields );
62 $target_xml = $target_r->as_xml_record;
63 $target_xml =~ s/^<\?.+?\?>$//mo;
64 $target_xml =~ s/\n//sgo;
65 $target_xml =~ s/>\s+</></sgo;
71 CREATE OR REPLACE FUNCTION vandelay.strip_field ( xml TEXT, field TEXT ) RETURNS TEXT AS $_$
77 my $r = MARC::Record->new_from_xml( $xml );
79 return $xml unless ($r);
81 my $field_spec = shift;
82 my @field_list = split(',', $field_spec);
85 for my $f (@field_list) {
86 $f =~ s/^\s*//; $f =~ s/\s*$//;
87 if ($f =~ /^(.{3})(\w*)(?:\[([^]]*)\])?$/) {
93 $match =~ s/^\s*//; $match =~ s/\s*$//;
94 $fields{$field} = { sf => [ split('', $sf) ] };
96 my ($msf,$mre) = split('~', $match);
97 if (length($msf) > 0 and length($mre) > 0) {
98 $msf =~ s/^\s*//; $msf =~ s/\s*$//;
99 $mre =~ s/^\s*//; $mre =~ s/\s*$//;
100 $fields{$field}{match} = { sf => $msf, re => qr/$mre/ };
106 for my $f ( keys %fields) {
107 for my $to_field ($r->field( $f )) {
108 if (exists($fields{$f}{match})) {
109 next unless (grep { $_ =~ $field{$f}{match}{re} } $to_field->subfield($field{$f}{match}{sf}));
112 if ( @{$fields{$f}} ) {
113 $to_field->delete_subfield(code => $fields{$f}{sf});
115 $r->delete_field( $to_field );
120 $xml = $r->as_xml_record;
121 $xml =~ s/^<\?.+?\?>$//mo;
123 $xml =~ s/>\s+</></sgo;
127 $_$ LANGUAGE PLPERLU;
129 -- Template-based marc munging functions
130 CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
132 merge_profile vandelay.merge_profile%ROWTYPE;
133 dyn_profile vandelay.compile_profile%ROWTYPE;
143 SELECT b.marc INTO eg_marc
144 FROM biblio.record_entry b
145 JOIN vandelay.bib_match m ON (m.eg_record = b.id AND m.queued_record = import_id)
148 IF eg_marc IS NULL OR v_marc IS NULL THEN
149 -- RAISE NOTICE 'no marc for template or bib record';
153 dyn_profile := vandelay.compile_profile( v_marc );
155 IF merge_profile_id IS NOT NULL THEN
156 SELECT * INTO merge_profile FROM vandelay.merge_profile WHERE id = merge_profile_id;
158 dyn_profile.add_rule := BTRIM( dyn_profile.add_rule || ',' || COALESCE(merge_profile.add_spec,''), ',');
159 dyn_profile.strip_rule := BTRIM( dyn_profile.strip_rule || ',' || COALESCE(merge_profile.strip_spec,''), ',');
160 dyn_profile.replace_rule := BTRIM( dyn_profile.replace_rule || ',' || COALESCE(merge_profile.replace_spec,''), ',');
161 dyn_profile.preserve_rule := BTRIM( dyn_profile.preserve_rule || ',' || COALESCE(merge_profile.preserve_spec,''), ',');
165 IF dyn_profile.replace_rule <> '' AND dyn_profile.preserve_rule <> '' THEN
166 -- RAISE NOTICE 'both replace [%] and preserve [%] specified', dyn_profile.replace_rule, dyn_profile.preserve_rule;
170 IF dyn_profile.replace_rule <> '' THEN
171 source_marc = v_marc;
172 target_marc = eg_marc;
173 replace_rule = dyn_profile.replace_rule;
175 source_marc = eg_marc;
176 target_marc = v_marc;
177 replace_rule = dyn_profile.preserve_rule;
180 UPDATE biblio.record_entry
181 SET marc = vandelay.merge_record_xml( target_marc, source_marc, dyn_profile.add_rule, replace_rule, dyn_profile.strip_rule )
185 -- RAISE NOTICE 'update of biblio.record_entry failed';
194 CREATE OR REPLACE FUNCTION vandelay.template_overlay_bib_record ( v_marc TEXT, eg_id BIGINT) RETURNS BOOL AS $$
195 SELECT vandelay.template_overlay_bib_record( $1, $2, NULL);
198 CREATE OR REPLACE FUNCTION vandelay.overlay_bib_record ( import_id BIGINT, eg_id BIGINT, merge_profile_id INT ) RETURNS BOOL AS $$
200 merge_profile vandelay.merge_profile%ROWTYPE;
201 dyn_profile vandelay.compile_profile%ROWTYPE;
212 SELECT q.marc INTO v_marc
213 FROM vandelay.queued_record q
214 JOIN vandelay.bib_match m ON (m.queued_record = q.id AND q.id = import_id)
217 IF v_marc IS NULL THEN
218 -- RAISE NOTICE 'no marc for vandelay or bib record';
222 IF vandelay.template_overlay_bib_record( v_marc, eg_id, merge_profile_id) THEN
223 UPDATE vandelay.queued_bib_record
224 SET imported_as = eg_id,
226 WHERE id = import_id;
228 editor_string := (oils_xpath('//*[@tag="905"]/*[@code="u"]/text()',v_marc))[1];
230 IF editor_string IS NOT NULL AND editor_string <> '' THEN
231 SELECT usr INTO editor_id FROM actor.card WHERE barcode = editor_string;
233 IF editor_id IS NULL THEN
234 SELECT id INTO editor_id FROM actor.usr WHERE usrname = editor_string;
237 IF editor_id IS NOT NULL THEN
238 UPDATE biblio.record_entry SET editor = editor_id WHERE id = eg_id;
245 -- RAISE NOTICE 'update of biblio.record_entry failed';
252 -- Minor cleanup of overlay test logic
253 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_bib_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
255 queued_record vandelay.queued_bib_record%ROWTYPE;
258 FOR queued_record IN SELECT * FROM vandelay.queued_bib_record WHERE queue = queue_id AND import_time IS NULL LOOP
260 IF vandelay.auto_overlay_bib_record( queued_record.id, merge_profile_id ) THEN
261 RETURN NEXT queued_record.id;
271 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_queue ( queue_id BIGINT, merge_profile_id INT ) RETURNS SETOF BIGINT AS $$
273 queued_record vandelay.queued_authority_record%ROWTYPE;
276 FOR queued_record IN SELECT * FROM vandelay.queued_authority_record WHERE queue = queue_id AND import_time IS NULL LOOP
278 IF vandelay.auto_overlay_authority_record( queued_record.id, merge_profile_id ) THEN
279 RETURN NEXT queued_record.id;