From: miker Date: Wed, 16 Mar 2011 14:19:54 +0000 (+0000) Subject: Patch from Steve Callendar addressing CN sort order on page 0 (and before) of CN... X-Git-Tag: kcls-grey-screen-prod2~3^2~4 X-Git-Url: http://git.equinoxoli.org/?p=evergreen-equinox.git;a=commitdiff_plain;h=ad227038c73bb615e5ddbc173f3ab8f756b3e51b Patch from Steve Callendar addressing CN sort order on page 0 (and before) of CN browse git-svn-id: svn://svn.open-ils.org/ILS/trunk@19768 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- diff --git a/Open-ILS/src/sql/Pg/040.schema.asset.sql b/Open-ILS/src/sql/Pg/040.schema.asset.sql index 18357ef..d2a8204 100644 --- a/Open-ILS/src/sql/Pg/040.schema.asset.sql +++ b/Open-ILS/src/sql/Pg/040.schema.asset.sql @@ -207,6 +207,7 @@ CREATE OR REPLACE FUNCTION asset.label_normalizer_generic(TEXT) RETURNS TEXT AS # thus could probably be considered a derived work, although nothing was # directly copied - but to err on the safe side of providing attribution: # Copyright (C) 2007 LibLime + # Copyright (C) 2011 Equinox Software, Inc (Steve Callendar) # Licensed under the GPL v2 or later use strict; @@ -214,13 +215,16 @@ CREATE OR REPLACE FUNCTION asset.label_normalizer_generic(TEXT) RETURNS TEXT AS # Converts the callnumber to uppercase # Strips spaces from start and end of the call number - # Converts anything other than letters, digits, and periods into underscores - # Collapses multiple underscores into a single underscore + # Converts anything other than letters, digits, and periods into spaces + # Collapses multiple spaces into a single underscore my $callnum = uc(shift); $callnum =~ s/^\s//g; $callnum =~ s/\s$//g; - $callnum =~ s/[^A-Z0-9_.]/_/g; - $callnum =~ s/_{2,}/_/g; + # NOTE: this previously used underscores, but this caused sorting issues + # for the "before" half of page 0 on CN browse, sorting CNs containing a + # decimal before "whole number" CNs + $callnum =~ s/[^A-Z0-9_.]/ /g; + $callnum =~ s/ {2,}/ /g; return $callnum; $func$ LANGUAGE PLPERLU; diff --git a/Open-ILS/src/sql/Pg/2.0.3-2.0.4-upgrade-db.sql b/Open-ILS/src/sql/Pg/2.0.3-2.0.4-upgrade-db.sql new file mode 100644 index 0000000..8726a21 --- /dev/null +++ b/Open-ILS/src/sql/Pg/2.0.3-2.0.4-upgrade-db.sql @@ -0,0 +1,164 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0498'); + +-- Rather than polluting the public schema with general Evergreen +-- functions, carve out a dedicated schema +CREATE SCHEMA evergreen; + +-- Replace all uses of PostgreSQL's built-in LOWER() function with +-- a more locale-savvy PLPERLU evergreen.lowercase() function +CREATE OR REPLACE FUNCTION evergreen.lowercase( TEXT ) RETURNS TEXT AS $$ + return lc(shift); +$$ LANGUAGE PLPERLU STRICT IMMUTABLE; + +-- update actor.usr_address indexes +DROP INDEX IF EXISTS actor.actor_usr_addr_street1_idx; +DROP INDEX IF EXISTS actor.actor_usr_addr_street2_idx; +DROP INDEX IF EXISTS actor.actor_usr_addr_city_idx; +DROP INDEX IF EXISTS actor.actor_usr_addr_state_idx; +DROP INDEX IF EXISTS actor.actor_usr_addr_post_code_idx; + +CREATE INDEX actor_usr_addr_street1_idx ON actor.usr_address (evergreen.lowercase(street1)); +CREATE INDEX actor_usr_addr_street2_idx ON actor.usr_address (evergreen.lowercase(street2)); +CREATE INDEX actor_usr_addr_city_idx ON actor.usr_address (evergreen.lowercase(city)); +CREATE INDEX actor_usr_addr_state_idx ON actor.usr_address (evergreen.lowercase(state)); +CREATE INDEX actor_usr_addr_post_code_idx ON actor.usr_address (evergreen.lowercase(post_code)); + +-- update actor.usr indexes +DROP INDEX IF EXISTS actor.actor_usr_first_given_name_idx; +DROP INDEX IF EXISTS actor.actor_usr_second_given_name_idx; +DROP INDEX IF EXISTS actor.actor_usr_family_name_idx; +DROP INDEX IF EXISTS actor.actor_usr_email_idx; +DROP INDEX IF EXISTS actor.actor_usr_day_phone_idx; +DROP INDEX IF EXISTS actor.actor_usr_evening_phone_idx; +DROP INDEX IF EXISTS actor.actor_usr_other_phone_idx; +DROP INDEX IF EXISTS actor.actor_usr_ident_value_idx; +DROP INDEX IF EXISTS actor.actor_usr_ident_value2_idx; + +CREATE INDEX actor_usr_first_given_name_idx ON actor.usr (evergreen.lowercase(first_given_name)); +CREATE INDEX actor_usr_second_given_name_idx ON actor.usr (evergreen.lowercase(second_given_name)); +CREATE INDEX actor_usr_family_name_idx ON actor.usr (evergreen.lowercase(family_name)); +CREATE INDEX actor_usr_email_idx ON actor.usr (evergreen.lowercase(email)); +CREATE INDEX actor_usr_day_phone_idx ON actor.usr (evergreen.lowercase(day_phone)); +CREATE INDEX actor_usr_evening_phone_idx ON actor.usr (evergreen.lowercase(evening_phone)); +CREATE INDEX actor_usr_other_phone_idx ON actor.usr (evergreen.lowercase(other_phone)); +CREATE INDEX actor_usr_ident_value_idx ON actor.usr (evergreen.lowercase(ident_value)); +CREATE INDEX actor_usr_ident_value2_idx ON actor.usr (evergreen.lowercase(ident_value2)); + +-- update actor.card indexes +DROP INDEX IF EXISTS actor.actor_card_barcode_evergreen_lowercase_idx; +CREATE INDEX actor_card_barcode_evergreen_lowercase_idx ON actor.card (evergreen.lowercase(barcode)); + +CREATE OR REPLACE FUNCTION vandelay.match_bib_record ( ) RETURNS TRIGGER AS $func$ +DECLARE + attr RECORD; + attr_def RECORD; + eg_rec RECORD; + id_value TEXT; + exact_id BIGINT; +BEGIN + + DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id; + + SELECT * INTO attr_def FROM vandelay.bib_attr_definition WHERE xpath = '//*[@tag="901"]/*[@code="c"]' ORDER BY id LIMIT 1; + + IF attr_def IS NOT NULL AND attr_def.id IS NOT NULL THEN + id_value := extract_marc_field('vandelay.queued_bib_record', NEW.id, attr_def.xpath, attr_def.remove); + + IF id_value IS NOT NULL AND id_value <> '' AND id_value ~ $r$^\d+$$r$ THEN + SELECT id INTO exact_id FROM biblio.record_entry WHERE id = id_value::BIGINT AND NOT deleted; + SELECT * INTO attr FROM vandelay.queued_bib_record_attr WHERE record = NEW.id and field = attr_def.id LIMIT 1; + IF exact_id IS NOT NULL THEN + INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, exact_id); + END IF; + END IF; + END IF; + + IF exact_id IS NULL THEN + 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 + + -- All numbers? check for an id match + IF (attr.attr_value ~ $r$^\d+$$r$) THEN + FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE id = attr.attr_value::BIGINT AND deleted IS FALSE LOOP + INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id); + END LOOP; + END IF; + + -- Looks like an ISBN? check for an isbn match + IF (attr.attr_value ~* $r$^[0-9x]+$$r$ AND character_length(attr.attr_value) IN (10,13)) THEN + 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 + PERFORM id FROM biblio.record_entry WHERE id = eg_rec.record AND deleted IS FALSE; + IF FOUND THEN + INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('isbn', attr.id, NEW.id, eg_rec.record); + END IF; + END LOOP; + + -- subcheck for isbn-as-tcn + FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = 'i' || attr.attr_value AND deleted IS FALSE LOOP + INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id); + END LOOP; + END IF; + + -- check for an OCLC tcn_value match + IF (attr.attr_value ~ $r$^o\d+$$r$) THEN + FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = regexp_replace(attr.attr_value,'^o','ocm') AND deleted IS FALSE LOOP + INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id); + END LOOP; + END IF; + + -- check for a direct tcn_value match + FOR eg_rec IN SELECT * FROM biblio.record_entry WHERE tcn_value = attr.attr_value AND deleted IS FALSE LOOP + INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('tcn_value', attr.id, NEW.id, eg_rec.id); + END LOOP; + + -- check for a direct item barcode match + FOR eg_rec IN + SELECT DISTINCT b.* + FROM biblio.record_entry b + JOIN asset.call_number cn ON (cn.record = b.id) + JOIN asset.copy cp ON (cp.call_number = cn.id) + WHERE cp.barcode = attr.attr_value AND cp.deleted IS FALSE + LOOP + INSERT INTO vandelay.bib_match (field_type, matched_attr, queued_record, eg_record) VALUES ('id', attr.id, NEW.id, eg_rec.id); + END LOOP; + + END LOOP; + END IF; + + RETURN NULL; +END; +$func$ LANGUAGE PLPGSQL; + +INSERT INTO config.upgrade_log (version) VALUES ('0499'); + +CREATE OR REPLACE FUNCTION asset.label_normalizer_generic(TEXT) RETURNS TEXT AS $func$ + # Created after looking at the Koha C4::ClassSortRoutine::Generic module, + # thus could probably be considered a derived work, although nothing was + # directly copied - but to err on the safe side of providing attribution: + # Copyright (C) 2007 LibLime + # Copyright (C) 2011 Equinox Software, Inc (Steve Callendar) + # Licensed under the GPL v2 or later + + use strict; + use warnings; + + # Converts the callnumber to uppercase + # Strips spaces from start and end of the call number + # Converts anything other than letters, digits, and periods into spaces + # Collapses multiple spaces into a single underscore + my $callnum = uc(shift); + $callnum =~ s/^\s//g; + $callnum =~ s/\s$//g; + # NOTE: this previously used underscores, but this caused sorting issues + # for the "before" half of page 0 on CN browse, sorting CNs containing a + # decimal before "whole number" CNs + $callnum =~ s/[^A-Z0-9_.]/ /g; + $callnum =~ s/ {2,}/ /g; + + return $callnum; +$func$ LANGUAGE PLPERLU; + +UPDATE asset.call_number SET id = id; + +COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/0499.schema.generic_CN_normalizer.sql b/Open-ILS/src/sql/Pg/upgrade/0499.schema.generic_CN_normalizer.sql new file mode 100644 index 0000000..7fdf72a --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0499.schema.generic_CN_normalizer.sql @@ -0,0 +1,35 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0499'); -- miker for Steve Callendar + +CREATE OR REPLACE FUNCTION asset.label_normalizer_generic(TEXT) RETURNS TEXT AS $func$ + # Created after looking at the Koha C4::ClassSortRoutine::Generic module, + # thus could probably be considered a derived work, although nothing was + # directly copied - but to err on the safe side of providing attribution: + # Copyright (C) 2007 LibLime + # Copyright (C) 2011 Equinox Software, Inc (Steve Callendar) + # Licensed under the GPL v2 or later + + use strict; + use warnings; + + # Converts the callnumber to uppercase + # Strips spaces from start and end of the call number + # Converts anything other than letters, digits, and periods into spaces + # Collapses multiple spaces into a single underscore + my $callnum = uc(shift); + $callnum =~ s/^\s//g; + $callnum =~ s/\s$//g; + # NOTE: this previously used underscores, but this caused sorting issues + # for the "before" half of page 0 on CN browse, sorting CNs containing a + # decimal before "whole number" CNs + $callnum =~ s/[^A-Z0-9_.]/ /g; + $callnum =~ s/ {2,}/ /g; + + return $callnum; +$func$ LANGUAGE PLPERLU; + +UPDATE asset.call_number SET id = id; + +COMMIT; +