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