Patch from Steve Callendar addressing CN sort order on page 0 (and before) of CN...

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 (file)
--- 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 (file)
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 (file)
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;
+