3 INSERT INTO config.upgrade_log (version) VALUES ('0498');
5 -- Rather than polluting the public schema with general Evergreen
6 -- functions, carve out a dedicated schema
7 CREATE SCHEMA evergreen;
9 -- Replace all uses of PostgreSQL's built-in LOWER() function with
10 -- a more locale-savvy PLPERLU evergreen.lowercase() function
11 CREATE OR REPLACE FUNCTION evergreen.lowercase( TEXT ) RETURNS TEXT AS $$
13 $$ LANGUAGE PLPERLU STRICT IMMUTABLE;
15 -- update actor.usr_address indexes
16 DROP INDEX IF EXISTS actor.actor_usr_addr_street1_idx;
17 DROP INDEX IF EXISTS actor.actor_usr_addr_street2_idx;
18 DROP INDEX IF EXISTS actor.actor_usr_addr_city_idx;
19 DROP INDEX IF EXISTS actor.actor_usr_addr_state_idx;
20 DROP INDEX IF EXISTS actor.actor_usr_addr_post_code_idx;
22 CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1));
23 CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2));
24 CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city));
25 CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state));
26 CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code));
28 -- update actor.usr indexes
29 DROP INDEX IF EXISTS actor.actor_usr_first_given_name_idx;
30 DROP INDEX IF EXISTS actor.actor_usr_second_given_name_idx;
31 DROP INDEX IF EXISTS actor.actor_usr_family_name_idx;
32 DROP INDEX IF EXISTS actor.actor_usr_email_idx;
33 DROP INDEX IF EXISTS actor.actor_usr_day_phone_idx;
34 DROP INDEX IF EXISTS actor.actor_usr_evening_phone_idx;
35 DROP INDEX IF EXISTS actor.actor_usr_other_phone_idx;
36 DROP INDEX IF EXISTS actor.actor_usr_ident_value_idx;
37 DROP INDEX IF EXISTS actor.actor_usr_ident_value2_idx;
39 CREATE INDEX actor_usr_first_given_name_idx ON actor.usr (evergreen.lowercase(first_given_name));
40 CREATE INDEX actor_usr_second_given_name_idx ON actor.usr (evergreen.lowercase(second_given_name));
41 CREATE INDEX actor_usr_family_name_idx ON actor.usr (evergreen.lowercase(family_name));
42 CREATE INDEX actor_usr_email_idx ON actor.usr (evergreen.lowercase(email));
43 CREATE INDEX actor_usr_day_phone_idx ON actor.usr (evergreen.lowercase(day_phone));
44 CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (evergreen.lowercase(evening_phone));
45 CREATE INDEX actor_usr_other_phone_idx ON actor.usr (evergreen.lowercase(other_phone));
46 CREATE INDEX actor_usr_ident_value_idx ON actor.usr (evergreen.lowercase(ident_value));
47 CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (evergreen.lowercase(ident_value2));
49 -- update actor.card indexes
50 DROP INDEX IF EXISTS actor.actor_card_barcode_evergreen_lowercase_idx;
51 CREATE INDEX actor_card_barcode_evergreen_lowercase_idx ON actor.card (evergreen.lowercase(barcode));
53 CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$
62 DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id;
64 SELECT * INTO attr_def FROM vandelay.bib_attr_definition WHERE xpath = '//*[@tag="901"]/*[@code="c"]' ORDER BY id LIMIT 1;
66 IF attr_def IS NOT NULL AND attr_def.id IS NOT NULL THEN
67 id_value := extract_marc_field('vandelay.queued_bib_record', NEW.id, attr_def.xpath, attr_def.remove);
69 IF id_value IS NOT NULL AND id_value <> '' AND id_value ~ $r$^\d+$$r$ THEN
70 SELECT id INTO exact_id FROM biblio.record_entry WHERE id = id_value::BIGINT AND NOT deleted;
71 SELECT * INTO attr FROM vandelay.queued_bib_record_attr WHERE record = NEW.id and field = attr_def.id LIMIT 1;
72 IF exact_id IS NOT NULL THEN
73 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, exact_id);
78 IF exact_id IS NULL THEN
79 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
81 -- All numbers? check for an id match
82 IF (attr.attr_value ~ $r$^\d+$$r$) THEN
83 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE id = attr.attr_value::BIGINT AND deleted IS FALSE LOOP
84 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
88 -- Looks like an ISBN? check for an isbn match
89 IF (attr.attr_value ~* $r$^[0-9x]+$$r$ AND character_length(attr.attr_value) IN (10,13)) THEN
90 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
91 PERFORM id FROM biblio.record_entry WHERE id = eg_rec.record AND deleted IS FALSE;
93 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record);
97 -- subcheck for isbn-as-tcn
98 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = 'i' || attr.attr_value AND deleted IS FALSE LOOP
99 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
103 -- check for an OCLC tcn_value match
104 IF (attr.attr_value ~ $r$^o\d+$$r$) THEN
105 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = regexp_replace(attr.attr_value,'^o','ocm') AND deleted IS FALSE LOOP
106 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
110 -- check for a direct tcn_value match
111 FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = attr.attr_value AND deleted IS FALSE LOOP
112 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id);
115 -- check for a direct item barcode match
118 FROM biblio.record_entry b
119 JOIN asset.call_number cn ON (cn.record = b.id)
120 JOIN asset.copy cp ON (cp.call_number = cn.id)
121 WHERE cp.barcode = attr.attr_value AND cp.deleted IS FALSE
123 INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id);
131 $func$ LANGUAGE PLPGSQL;
133 INSERT INTO config.upgrade_log (version) VALUES ('0499');
135 CREATE OR REPLACE FUNCTION asset.label_normalizer_generic(TEXT) RETURNS TEXT AS $func$
136 # Created after looking at the Koha C4::ClassSortRoutine::Generic module,
137 # thus could probably be considered a derived work, although nothing was
138 # directly copied - but to err on the safe side of providing attribution:
139 # Copyright (C) 2007 LibLime
140 # Copyright (C) 2011 Equinox Software, Inc (Steve Callendar)
141 # Licensed under the GPL v2 or later
146 # Converts the callnumber to uppercase
147 # Strips spaces from start and end of the call number
148 # Converts anything other than letters, digits, and periods into spaces
149 # Collapses multiple spaces into a single underscore
150 my $callnum = uc(shift);
151 $callnum =~ s/^\s//g;
152 $callnum =~ s/\s$//g;
153 # NOTE: this previously used underscores, but this caused sorting issues
154 # for the "before" half of page 0 on CN browse, sorting CNs containing a
155 # decimal before "whole number" CNs
156 $callnum =~ s/[^A-Z0-9_.]/ /g;
157 $callnum =~ s/ {2,}/ /g;
160 $func$ LANGUAGE PLPERLU;
162 UPDATE asset.call_number SET id = id;