From 52fc7f08004508d0355f93dd7c4c51cc193b0513 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Wed, 21 Sep 2011 16:38:08 -0400 Subject: [PATCH] New in-db search and browse of authorities with non-filing-indicator support Upon authority ingest, record simple authority headings for searching, browsing and sorting. Respect non-filing indicators, if configured (configuration is the nfi field on authority.control_set_authority_field). Provide a full suite of search/browse top/center alpha/rank functions based on authority tag, bib tag or browse axis at the database layer. Provide functions to find the main-only and full tag list, and ref-variant versions of the previously defined functions. Teach OpenILS::WWW::SuperCat (responsible for RESTful feed-generating interfaces) to use new OpenILS::Application::SuperCat methods that in turn use the database methods. The change should be transparent to users of SuperCat web APIs. Carry indicators over from authority main entries to bibs, when applying them in the marc editor. Signed-off-by: Lebbeous Fogle-Weekley Signed-off-by: Bill Erickson --- Open-ILS/examples/fm_IDL.xml | 1 + .../Application/Storage/Publisher/authority.pm | 45 ++ .../perlmods/lib/OpenILS/Application/SuperCat.pm | 86 ++++ Open-ILS/src/perlmods/lib/OpenILS/WWW/SuperCat.pm | 41 +- Open-ILS/src/sql/Pg/011.schema.authority.sql | 448 ++++++++++++++++- Open-ILS/src/sql/Pg/950.data.seed-values.sql | 11 +- Open-ILS/src/sql/Pg/999.functions.global.sql | 7 + .../src/sql/Pg/upgrade/XXXX.schema-acs-nfi.sql | 533 +++++++++++++++++++++ Open-ILS/xul/staff_client/server/cat/marcedit.js | 9 + 9 files changed, 1153 insertions(+), 28 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema-acs-nfi.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index f205fd9429..fae1de503f 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -1789,6 +1789,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/authority.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/authority.pm index af0dff69b7..b0cdfe47a8 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/authority.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/authority.pm @@ -259,6 +259,51 @@ for my $class ( qw/title author subject keyword series identifier/ ) { cachable => 1, ); } +__PACKAGE__->register_method( + api_name => "open-ils.storage.authority.in_db.browse_or_search", + method => "authority_in_db_browse_or_search", + api_level => 1, + argc => 5, + signature => { + desc => q/Use stored procedures to perform authorities-based + browses or searches/, + params => [ + {name => "method", type => "string", desc => q/ + The name of a method within the authority schema to call. This + is an API call on a private service for a reason. Do not pass + unfiltered user input into this API call, especially in this + parameter./}, + {name => "what", type => "string", desc => q/ + What to search. Could be an axis name, an authority tag + number, or a bib tag number/}, + {name => "term", type => "string", desc => "Search term"}, + {name => "page", type => "number", desc => "Zero-based page number"}, + {name => "page_size", type => "number", + desc => "Number of records per page"} + ], + return => { + desc => "A list of authority record IDs", + type => "array" + } + } +); + +sub authority_in_db_browse_or_search { + my ($self, $shift, $method, @args) = @_; + return unless $method =~ /^\w+$/; + + my $db = authority::full_rec->db_Main; + my $list = $db->selectcol_arrayref( + qq/ + SELECT + (SELECT record FROM authority.simple_heading WHERE id = func.heading) + FROM authority.$method(?, ?, ?, ?) func(heading) + /, + {}, @args + ); + + return $list; +} 1; diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/SuperCat.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/SuperCat.pm index a43abc5fb8..879ba77c5b 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/SuperCat.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/SuperCat.pm @@ -179,6 +179,8 @@ sub child_init { register_record_transforms(); + register_new_authorities_methods(); + return 1; } @@ -226,6 +228,90 @@ sub register_record_transforms { } } +sub register_new_authorities_methods { + my %register_args = ( + method => "generic_new_authorities_method", + api_level => 1, + argc => 1, + signature => { + desc => q/Generated method/, + params => [ + {name => "what", + desc => "An axis, an authority tag number, or a bibliographic tag number, depending on invocation", + type => "string"}, + {name => "term", + desc => "A search term", + type => "string"}, + {name => "page", + desc => "zero-based page number of results", + type => "number"}, + {name => "page size", + desc => "number of results per page", + type => "number"} + ], + return => { + desc => "A list of authority record IDs", type => "array" + } + } + ); + + foreach my $how (qw/axis atag btag/) { + foreach my $action (qw/browse_center browse_top + search_rank search_heading/) { + + $register_args{api_name} = + "open-ils.supercat.authority.$action.by_$how"; + __PACKAGE__->register_method(%register_args); + + $register_args{api_name} = + "open-ils.supercat.authority.$action.by_$how.refs"; + __PACKAGE__->register_method(%register_args); + + } + } +} + +sub generic_new_authorities_method { + my $self = shift; + my $client = shift; + + # We want to be extra careful with these arguments, since the next + # thing we're doing with them is passing them to a DB procedure. + my $what = ''.shift; + my $term = ''.shift; + my $page = int(shift || 0); + my $page_size = shift; + + # undef ok, but other non numbers not ok + $page_size = int($page_size) if defined $page_size; + + # Figure out how we were called and what DB procedure we'll call in turn. + $self->api_name =~ /\.by_(\w+)($|\.)/; + my $metaaxis = $1; + my $refs = $2; + + $self->api_name =~ /authority\.(\w+)\./; + my $action = $1; + + my $method = "${metaaxis}_$action"; + $method .= "_refs" if $refs; + + # Match authority.full_rec normalization + # XXX don't know whether we need second arg 'subfield'? + $term = naco_normalize($term); + + my $storage = create OpenSRF::AppSession("open-ils.storage"); + my $list = $storage->request( + "open-ils.storage.authority.in_db.browse_or_search", + $method, $what, $term, $page, $page_size + )->gather(1); + + $storage->kill_me; + + return $list; +} + + sub tree_walker { my $tree = shift; my $field = shift; diff --git a/Open-ILS/src/perlmods/lib/OpenILS/WWW/SuperCat.pm b/Open-ILS/src/perlmods/lib/OpenILS/WWW/SuperCat.pm index 4ca4b9d24d..708cc22478 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/WWW/SuperCat.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/WWW/SuperCat.pm @@ -36,6 +36,8 @@ my $U = 'OpenILS::Application::AppUtils'; # set the bootstrap config when this module is loaded my ($bootstrap, $supercat, $actor, $parser, $search, $xslt, $cn_browse_xslt, %browse_types, %qualifier_map); +my $authority_axis_re = qr/^authority\.(\w+)(\.refs)?$/; + $browse_types{call_number}{xml} = sub { my $tree = shift; @@ -1561,12 +1563,17 @@ sub string_browse { my $tree; if ($axis =~ /^authority/) { + my ($realaxis, $refs) = ($axis =~ $authority_axis_re); + + my $method = "open-ils.supercat.authority.browse_center.by_axis"; + $method .= ".refs" if $refs; + $tree = $supercat->request( - "open-ils.supercat.authority.browse.by_axis", - $axis, + $method, + $realaxis, $string, - $page_size, - $page + $page, + $page_size )->gather(1); } else { $tree = $supercat->request( @@ -1637,12 +1644,17 @@ sub string_startwith { my $tree; if ($axis =~ /^authority/) { + my ($realaxis, $refs) = ($axis =~ $authority_axis_re); + + my $method = "open-ils.supercat.authority.browse_top.by_axis"; + $method .= ".refs" if $refs; + $tree = $supercat->request( - "open-ils.supercat.authority.startwith.by_axis", - $axis, + $method, + $realaxis, $string, - $page_size, - $page + $page, + $page_size )->gather(1); } else { $tree = $supercat->request( @@ -2110,12 +2122,17 @@ sub return_auth_response { if ($qualifier eq "id") { $recs = [ int($term) ]; } else { + my ($realaxis, $refs) = ($qualifier =~ $authority_axis_re); + + my $method = "open-ils.supercat.authority.browse_top.by_axis"; + $method .= ".refs" if $refs; + $recs = $supercat->request( - "open-ils.supercat.authority.startwith.by_axis", - $qualifier, + $method, + $realaxis, $term, - $page_size, - $page + $page, + $page_size )->gather(1); } diff --git a/Open-ILS/src/sql/Pg/011.schema.authority.sql b/Open-ILS/src/sql/Pg/011.schema.authority.sql index 329f3567d0..09bd745b66 100644 --- a/Open-ILS/src/sql/Pg/011.schema.authority.sql +++ b/Open-ILS/src/sql/Pg/011.schema.authority.sql @@ -33,6 +33,7 @@ CREATE TABLE authority.control_set_authority_field ( main_entry INT REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, control_set INT NOT NULL REFERENCES authority.control_set (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, tag CHAR(3) NOT NULL, + nfi CHAR(1), -- non-filing indicator sf_list TEXT NOT NULL, name TEXT NOT NULL, -- i18n description TEXT -- i18n @@ -131,7 +132,7 @@ CREATE INDEX authority_full_rec_tag_part_idx ON authority.full_rec (SUBSTRING(ta CREATE INDEX authority_full_rec_subfield_a_idx ON authority.full_rec (value) WHERE subfield = 'a'; CREATE TRIGGER authority_full_rec_fti_trigger BEFORE UPDATE OR INSERT ON authority.full_rec - FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value); + FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword'); CREATE INDEX authority_full_rec_index_vector_idx ON authority.full_rec USING GIST (index_vector); /* Enable LIKE to use an index for database clusters with locales other than C or POSIX */ @@ -149,27 +150,58 @@ CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT, no_thesaur DECLARE acsaf authority.control_set_authority_field%ROWTYPE; tag_used TEXT; + nfi_used TEXT; sf TEXT; thes_code TEXT; cset INT; heading_text TEXT; tmp_text TEXT; + first_sf BOOL; + auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml)::INT; BEGIN - thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj'); - IF thes_code IS NULL THEN - thes_code := '|'; + SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id; + + IF cset IS NULL THEN + SELECT control_set INTO cset + FROM authority.control_set_authority_field + WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[])) + LIMIT 1; END IF; - SELECT control_set INTO cset FROM authority.thesaurus WHERE code = thes_code; - IF NOT FOUND THEN - cset = 1; + IF thes_code = 'z' THEN + thes_code := COALESCE( oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml), '' ); END IF; heading_text := ''; FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP tag_used := acsaf.tag; + nfi_used := acsaf.nfi; + first_sf := TRUE; FOR sf IN SELECT * FROM regexp_split_to_table(acsaf.sf_list,'') LOOP tmp_text := oils_xpath_string('//*[@tag="'||tag_used||'"]/*[@code="'||sf||'"]', marcxml); + + IF first_sf AND tmp_text IS NOT NULL AND nfi_used IS NOT NULL THEN + + tmp_text := SUBSTRING( + tmp_text FROM + COALESCE( + NULLIF( + REGEXP_REPLACE( + oils_xpath_string('//*[@tag="'||tag_used||'"]/@ind'||nfi_used, marcxml), + $$\D+$$, + '', + 'g' + ), + '' + )::INT, + 0 + ) + 1 + ); + + END IF; + + first_sf := FALSE; + IF tmp_text IS NOT NULL AND tmp_text <> '' THEN heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text; END IF; @@ -177,15 +209,11 @@ BEGIN EXIT WHEN heading_text <> ''; END LOOP; - IF thes_code = 'z' THEN - thes_code := oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml); - END IF; - IF heading_text <> '' THEN IF no_thesaurus IS TRUE THEN heading_text := tag_used || ' ' || public.naco_normalize(heading_text); ELSE - heading_text := tag_used || '_' || thes_code || ' ' || public.naco_normalize(heading_text); + heading_text := tag_used || '_' || COALESCE(nfi_used,'-') || '_' || thes_code || ' ' || public.naco_normalize(heading_text); END IF; ELSE heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml); @@ -195,6 +223,96 @@ BEGIN END; $func$ LANGUAGE PLPGSQL IMMUTABLE; +CREATE TABLE authority.simple_heading ( + id BIGSERIAL PRIMARY KEY, + record BIGINT NOT NULL REFERENCES authority.record_entry (id), + atag INT NOT NULL REFERENCES authority.control_set_authority_field (id), + value TEXT NOT NULL, + sort_value TEXT NOT NULL, + index_vector tsvector NOT NULL +); +CREATE TRIGGER authority_simple_heading_fti_trigger + BEFORE UPDATE OR INSERT ON authority.simple_heading + FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword'); + +CREATE INDEX authority_simple_heading_index_vector_idx ON authority.simple_heading USING GIST (index_vector); +CREATE INDEX authority_simple_heading_value_idx ON authority.simple_heading (value); +CREATE INDEX authority_simple_heading_sort_value_idx ON authority.simple_heading (sort_value); + +CREATE OR REPLACE FUNCTION authority.simple_heading_set( marcxml TEXT ) RETURNS SETOF authority.simple_heading AS $func$ +DECLARE + res authority.simple_heading%ROWTYPE; + acsaf authority.control_set_authority_field%ROWTYPE; + tag_used TEXT; + nfi_used TEXT; + sf TEXT; + cset INT; + heading_text TEXT; + sort_text TEXT; + tmp_text TEXT; + tmp_xml TEXT; + first_sf BOOL; + auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml)::INT; +BEGIN + + res.record := auth_id; + + SELECT control_set INTO cset + FROM authority.control_set_authority_field + WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]) ) + LIMIT 1; + + FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP + + res.atag := acsaf.id; + tag_used := acsaf.tag; + nfi_used := acsaf.nfi; + + FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)) LOOP + heading_text := ''; + + FOR sf IN SELECT * FROM regexp_split_to_table(acsaf.sf_list,'') LOOP + heading_text := heading_text || COALESCE( ' ' || oils_xpath_string('//*[@code="'||sf||'"]',tmp_xml::TEXT), ''); + END LOOP; + + heading_text := public.naco_normalize(heading_text); + + IF nfi_used IS NOT NULL THEN + + sort_text := SUBSTRING( + heading_text FROM + COALESCE( + NULLIF( + REGEXP_REPLACE( + oils_xpath_string('//*[@tag="'||tag_used||'"]/@ind'||nfi_used, marcxml), + $$\D+$$, + '', + 'g' + ), + '' + )::INT, + 0 + ) + 1 + ); + + ELSE + sort_text := heading_text; + END IF; + + IF heading_text IS NOT NULL AND heading_text <> '' THEN + res.value := heading_text; + res.sort_value := sort_text; + RETURN NEXT res; + END IF; + + END LOOP; + + END LOOP; + + RETURN; +END; +$func$ LANGUAGE PLPGSQL IMMUTABLE; + CREATE OR REPLACE FUNCTION authority.simple_normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$ SELECT authority.normalize_heading($1, TRUE); $func$ LANGUAGE SQL IMMUTABLE; @@ -385,4 +503,310 @@ BEGIN END; $func$ LANGUAGE plpgsql; + +-- Support function used to find the pivot for alpha-heading-browse style searching +CREATE OR REPLACE FUNCTION authority.simple_heading_find_pivot( a INT[], q TEXT ) RETURNS TEXT AS $$ +DECLARE + sort_value_row RECORD; + value_row RECORD; + t_term TEXT; +BEGIN + + t_term := public.naco_normalize(q); + + SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END + + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank, + ash.sort_value + INTO sort_value_row + FROM authority.simple_heading ash + WHERE ash.atag = ANY (a) + AND ash.sort_value >= t_term + ORDER BY rank DESC, ash.sort_value + LIMIT 1; + + SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END + + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank, + ash.sort_value + INTO value_row + FROM authority.simple_heading ash + WHERE ash.atag = ANY (a) + AND ash.value >= t_term + ORDER BY rank DESC, ash.sort_value + LIMIT 1; + + IF value_row.rank > sort_value_row.rank THEN + RETURN value_row.sort_value; + ELSE + RETURN sort_value_row.sort_value; + END IF; +END; +$$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION authority.simple_heading_browse_center( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$ +DECLARE + pivot_sort_value TEXT; + boffset INT DEFAULT 0; + aoffset INT DEFAULT 0; + blimit INT DEFAULT 0; + alimit INT DEFAULT 0; +BEGIN + + pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q); + + IF page = 0 THEN + blimit := pagesize / 2; + alimit := blimit; + + IF pagesize % 2 <> 0 THEN + alimit := alimit + 1; + END IF; + ELSE + blimit := pagesize; + alimit := blimit; + + boffset := pagesize / 2; + aoffset := boffset; + + IF pagesize % 2 <> 0 THEN + boffset := boffset + 1; + END IF; + END IF; + + IF page <= 0 THEN + RETURN QUERY + -- "bottom" half of the browse results + SELECT id FROM ( + SELECT ash.id, + row_number() over () + FROM authority.simple_heading ash + WHERE ash.atag = ANY (atag_list) + AND ash.sort_value < pivot_sort_value + ORDER BY ash.sort_value DESC + LIMIT blimit + OFFSET ABS(page) * pagesize - boffset + ) x ORDER BY row_number DESC; + END IF; + + IF page >= 0 THEN + RETURN QUERY + -- "bottom" half of the browse results + SELECT ash.id + FROM authority.simple_heading ash + WHERE ash.atag = ANY (atag_list) + AND ash.sort_value >= pivot_sort_value + ORDER BY ash.sort_value + LIMIT alimit + OFFSET ABS(page) * pagesize - aoffset; + END IF; +END; +$$ LANGUAGE PLPGSQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.axis_authority_tags(a TEXT) RETURNS INT[] AS $$ + SELECT ARRAY_ACCUM(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1; +$$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION authority.axis_authority_tags_refs(a TEXT) RETURNS INT[] AS $$ + SELECT ARRAY_CAT( + ARRAY[a.field], + (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.field) + ) + FROM authority.browse_axis_authority_field_map a + WHERE axis = $1 +$$ LANGUAGE SQL; + + + +CREATE OR REPLACE FUNCTION authority.btag_authority_tags(btag TEXT) RETURNS INT[] AS $$ + SELECT ARRAY_ACCUM(authority_field) FROM authority.control_set_bib_field WHERE tag = $1 +$$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION authority.btag_authority_tags_refs(btag TEXT) RETURNS INT[] AS $$ + SELECT ARRAY_CAT( + ARRAY[a.authority_field], + (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.authority_field) + ) + FROM authority.control_set_bib_field a + WHERE a.tag = $1 +$$ LANGUAGE SQL; + + + +CREATE OR REPLACE FUNCTION authority.atag_authority_tags(atag TEXT) RETURNS INT[] AS $$ + SELECT ARRAY_ACCUM(id) FROM authority.control_set_authority_field WHERE tag = $1 +$$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION authority.atag_authority_tags_refs(atag TEXT) RETURNS INT[] AS $$ + SELECT ARRAY_CAT( + ARRAY[a.id], + (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.id) + ) + FROM authority.control_set_authority_field a + WHERE a.tag = $1 +$$ LANGUAGE SQL; + + + +CREATE OR REPLACE FUNCTION authority.axis_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.btag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.atag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.axis_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.btag_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.atag_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + + +CREATE OR REPLACE FUNCTION authority.simple_heading_browse_top( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ +DECLARE + pivot_sort_value TEXT; +BEGIN + + pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q); + + IF page < 0 THEN + RETURN QUERY + -- "bottom" half of the browse results + SELECT id FROM ( + SELECT ash.id, + row_number() over () + FROM authority.simple_heading ash + WHERE ash.atag = ANY (atag_list) + AND ash.sort_value < pivot_sort_value + ORDER BY ash.sort_value DESC + LIMIT pagesize + OFFSET (ABS(page) - 1) * pagesize + ) x ORDER BY row_number DESC; + END IF; + + IF page >= 0 THEN + RETURN QUERY + -- "bottom" half of the browse results + SELECT ash.id + FROM authority.simple_heading ash + WHERE ash.atag = ANY (atag_list) + AND ash.sort_value >= pivot_sort_value + ORDER BY ash.sort_value + LIMIT pagesize + OFFSET ABS(page) * pagesize ; + END IF; +END; +$$ LANGUAGE PLPGSQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.axis_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.btag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.atag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.axis_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.btag_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.atag_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + + +CREATE OR REPLACE FUNCTION authority.simple_heading_search_rank( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT ash.id + FROM authority.simple_heading ash, + public.naco_normalize($2) t(term), + plainto_tsquery('keyword'::regconfig,$2) ptsq(term) + WHERE ash.atag = ANY ($1) + AND ash.index_vector @@ ptsq.term + ORDER BY ts_rank_cd(ash.index_vector,ptsq.term,14)::numeric + + CASE WHEN ash.sort_value LIKE t.term || '%' THEN 2 ELSE 0 END + + CASE WHEN ash.value LIKE t.term || '%' THEN 1 ELSE 0 END DESC + LIMIT $4 + OFFSET $4 * $3; +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.axis_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.btag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.atag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.axis_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.btag_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.atag_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + + +CREATE OR REPLACE FUNCTION authority.simple_heading_search_heading( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT ash.id + FROM authority.simple_heading ash, + public.naco_normalize($2) t(term), + plainto_tsquery('keyword'::regconfig,$2) ptsq(term) + WHERE ash.atag = ANY ($1) + AND ash.index_vector @@ ptsq.term + ORDER BY ash.sort_value + LIMIT $4 + OFFSET $4 * $3; +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.axis_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.btag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.atag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.axis_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.btag_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.atag_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + + COMMIT; + diff --git a/Open-ILS/src/sql/Pg/950.data.seed-values.sql b/Open-ILS/src/sql/Pg/950.data.seed-values.sql index 7f0263b400..637a01447c 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -10218,13 +10218,19 @@ INSERT INTO authority.control_set (id, name, description) VALUES ( oils_i18n_gettext('1','Library of Congress standard authority record control semantics','acs','description') ); +-- Entries that need to respect an NFI +INSERT INTO authority.control_set_authority_field (id, control_set, main_entry, tag, sf_list, name, nfi) VALUES + (4, 1, NULL, '130', 'adfgklmnoprstvxyz', oils_i18n_gettext('4','Heading -- Uniform Title','acsaf','name'), '2'), + (24, 1, 4, '530', 'adfgiklmnoprstvwxyz4', oils_i18n_gettext('24','See Also From Tracing -- Uniform Title','acsaf','name'), '2'), + (44, 1, 4, '730', 'adfghklmnoprstvwxyz25', oils_i18n_gettext('44','Established Heading Linking Entry -- Uniform Title','acsaf','name'), '2'), + (64, 1, 4, '430', 'adfgiklmnoprstvwxyz4', oils_i18n_gettext('64','See Also Tracing -- Uniform Title','acsaf','name'), '2'); + INSERT INTO authority.control_set_authority_field (id, control_set, main_entry, tag, sf_list, name) VALUES -- Main entries (1, 1, NULL, '100', 'abcdefklmnopqrstvxyz', oils_i18n_gettext('1','Heading -- Personal Name','acsaf','name')), (2, 1, NULL, '110', 'abcdefgklmnoprstvxyz', oils_i18n_gettext('2','Heading -- Corporate Name','acsaf','name')), (3, 1, NULL, '111', 'acdefgklnpqstvxyz', oils_i18n_gettext('3','Heading -- Meeting Name','acsaf','name')), - (4, 1, NULL, '130', 'adfgklmnoprstvxyz', oils_i18n_gettext('4','Heading -- Uniform Title','acsaf','name')), (5, 1, NULL, '150', 'abvxyz', oils_i18n_gettext('5','Heading -- Topical Term','acsaf','name')), (6, 1, NULL, '151', 'avxyz', oils_i18n_gettext('6','Heading -- Geographic Name','acsaf','name')), (7, 1, NULL, '155', 'avxyz', oils_i18n_gettext('7','Heading -- Genre/Form Term','acsaf','name')), @@ -10238,7 +10244,6 @@ INSERT INTO authority.control_set_authority_field (id, control_set, main_entry, (21, 1, 1, '500', 'abcdefiklmnopqrstvwxyz4', oils_i18n_gettext('21','See Also From Tracing -- Personal Name','acsaf','name')), (22, 1, 2, '510', 'abcdefgiklmnoprstvwxyz4', oils_i18n_gettext('22','See Also From Tracing -- Corporate Name','acsaf','name')), (23, 1, 3, '511', 'acdefgiklnpqstvwxyz4', oils_i18n_gettext('23','See Also From Tracing -- Meeting Name','acsaf','name')), - (24, 1, 4, '530', 'adfgiklmnoprstvwxyz4', oils_i18n_gettext('24','See Also From Tracing -- Uniform Title','acsaf','name')), (25, 1, 5, '550', 'abivwxyz4', oils_i18n_gettext('25','See Also From Tracing -- Topical Term','acsaf','name')), (26, 1, 6, '551', 'aivwxyz4', oils_i18n_gettext('26','See Also From Tracing -- Geographic Name','acsaf','name')), (27, 1, 7, '555', 'aivwxyz4', oils_i18n_gettext('27','See Also From Tracing -- Genre/Form Term','acsaf','name')), @@ -10252,7 +10257,6 @@ INSERT INTO authority.control_set_authority_field (id, control_set, main_entry, (41, 1, 1, '700', 'abcdefghjklmnopqrstvwxyz25', oils_i18n_gettext('41','Established Heading Linking Entry -- Personal Name','acsaf','name')), (42, 1, 2, '710', 'abcdefghklmnoprstvwxyz25', oils_i18n_gettext('42','Established Heading Linking Entry -- Corporate Name','acsaf','name')), (43, 1, 3, '711', 'acdefghklnpqstvwxyz25', oils_i18n_gettext('43','Established Heading Linking Entry -- Meeting Name','acsaf','name')), - (44, 1, 4, '730', 'adfghklmnoprstvwxyz25', oils_i18n_gettext('44','Established Heading Linking Entry -- Uniform Title','acsaf','name')), (45, 1, 5, '750', 'abvwxyz25', oils_i18n_gettext('45','Established Heading Linking Entry -- Topical Term','acsaf','name')), (46, 1, 6, '751', 'avwxyz25', oils_i18n_gettext('46','Established Heading Linking Entry -- Geographic Name','acsaf','name')), (47, 1, 7, '755', 'avwxyz25', oils_i18n_gettext('47','Established Heading Linking Entry -- Genre/Form Term','acsaf','name')), @@ -10266,7 +10270,6 @@ INSERT INTO authority.control_set_authority_field (id, control_set, main_entry, (61, 1, 1, '400', 'abcdefiklmnopqrstvwxyz4', oils_i18n_gettext('61','See Also Tracing -- Personal Name','acsaf','name')), (62, 1, 2, '410', 'abcdefgiklmnoprstvwxyz4', oils_i18n_gettext('62','See Also Tracing -- Corporate Name','acsaf','name')), (63, 1, 3, '411', 'acdefgiklnpqstvwxyz4', oils_i18n_gettext('63','See Also Tracing -- Meeting Name','acsaf','name')), - (64, 1, 4, '430', 'adfgiklmnoprstvwxyz4', oils_i18n_gettext('64','See Also Tracing -- Uniform Title','acsaf','name')), (65, 1, 5, '450', 'abivwxyz4', oils_i18n_gettext('65','See Also Tracing -- Topical Term','acsaf','name')), (66, 1, 6, '451', 'aivwxyz4', oils_i18n_gettext('66','See Also Tracing -- Geographic Name','acsaf','name')), (67, 1, 7, '455', 'aivwxyz4', oils_i18n_gettext('67','See Also Tracing -- Genre/Form Term','acsaf','name')), diff --git a/Open-ILS/src/sql/Pg/999.functions.global.sql b/Open-ILS/src/sql/Pg/999.functions.global.sql index ca108f1008..a083584ad6 100644 --- a/Open-ILS/src/sql/Pg/999.functions.global.sql +++ b/Open-ILS/src/sql/Pg/999.functions.global.sql @@ -1425,6 +1425,7 @@ BEGIN IF NEW.deleted IS TRUE THEN -- If this authority is deleted DELETE FROM authority.bib_linking WHERE authority = NEW.id; -- Avoid updating fields in bibs that are no longer visible DELETE FROM authority.full_rec WHERE record = NEW.id; -- Avoid validating fields against deleted authority records + DELETE FROM authority.simple_heading WHERE record = NEW.id; -- Should remove matching $0 from controlled fields at the same time? RETURN NEW; -- and we're done END IF; @@ -1435,10 +1436,16 @@ BEGIN IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change RETURN NEW; END IF; + -- Propagate these updates to any linked bib records PERFORM authority.propagate_changes(NEW.id) FROM authority.record_entry WHERE id = NEW.id; + + DELETE FROM authority.simple_heading WHERE record = NEW.id; END IF; + INSERT INTO authority.simple_heading (record,atag,value,sort_value) + SELECT record, atag, value, sort_value FROM authority.simple_heading_set(NEW.marc); + -- Flatten and insert the afr data PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_full_rec' AND enabled; IF NOT FOUND THEN diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema-acs-nfi.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema-acs-nfi.sql new file mode 100644 index 0000000000..9f5762f748 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema-acs-nfi.sql @@ -0,0 +1,533 @@ +-- XXXX.schema-acs-nfi.sql +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +-- AFTER UPDATE OR INSERT trigger for authority.record_entry +CREATE OR REPLACE FUNCTION authority.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$ +BEGIN + + IF NEW.deleted IS TRUE THEN -- If this authority is deleted + DELETE FROM authority.bib_linking WHERE authority = NEW.id; -- Avoid updating fields in bibs that are no longer visible + DELETE FROM authority.full_rec WHERE record = NEW.id; -- Avoid validating fields against deleted authority records + DELETE FROM authority.simple_heading WHERE record = NEW.id; + -- Should remove matching $0 from controlled fields at the same time? + RETURN NEW; -- and we're done + END IF; + + IF TG_OP = 'UPDATE' THEN -- re-ingest? + PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled; + + IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change + RETURN NEW; + END IF; + + -- Propagate these updates to any linked bib records + PERFORM authority.propagate_changes(NEW.id) FROM authority.record_entry WHERE id = NEW.id; + + DELETE FROM authority.simple_heading WHERE record = NEW.id; + END IF; + + INSERT INTO authority.simple_heading (record,atag,value,sort_value) + SELECT record, atag, value, sort_value FROM authority.simple_heading_set(NEW.marc); + + -- Flatten and insert the afr data + PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_full_rec' AND enabled; + IF NOT FOUND THEN + PERFORM authority.reingest_authority_full_rec(NEW.id); + PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_rec_descriptor' AND enabled; + IF NOT FOUND THEN + PERFORM authority.reingest_authority_rec_descriptor(NEW.id); + END IF; + END IF; + + RETURN NEW; +END; +$func$ LANGUAGE PLPGSQL; + +ALTER TABLE authority.control_set_authority_field ADD COLUMN nfi CHAR(1); + +-- Entries that need to respect an NFI +UPDATE authority.control_set_authority_field SET nfi = '2' + WHERE id IN (4,24,44,64); + +DROP TRIGGER authority_full_rec_fti_trigger ON authority.full_rec; +CREATE TRIGGER authority_full_rec_fti_trigger + BEFORE UPDATE OR INSERT ON authority.full_rec + FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword'); + +CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT, no_thesaurus BOOL ) RETURNS TEXT AS $func$ +DECLARE + acsaf authority.control_set_authority_field%ROWTYPE; + tag_used TEXT; + nfi_used TEXT; + sf TEXT; + thes_code TEXT; + cset INT; + heading_text TEXT; + tmp_text TEXT; + first_sf BOOL; + auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml)::INT; +BEGIN + SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id; + + IF cset IS NULL THEN + SELECT control_set INTO cset + FROM authority.control_set_authority_field + WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[])) + LIMIT 1; + END IF; + + IF thes_code = 'z' THEN + thes_code := COALESCE( oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml), '' ); + END IF; + + heading_text := ''; + FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP + tag_used := acsaf.tag; + nfi_used := acsaf.nfi; + first_sf := TRUE; + FOR sf IN SELECT * FROM regexp_split_to_table(acsaf.sf_list,'') LOOP + tmp_text := oils_xpath_string('//*[@tag="'||tag_used||'"]/*[@code="'||sf||'"]', marcxml); + + IF first_sf AND tmp_text IS NOT NULL AND nfi_used IS NOT NULL THEN + + tmp_text := SUBSTRING( + tmp_text FROM + COALESCE( + NULLIF( + REGEXP_REPLACE( + oils_xpath_string('//*[@tag="'||tag_used||'"]/@ind'||nfi_used, marcxml), + $$\D+$$, + '', + 'g' + ), + '' + )::INT, + 0 + ) + 1 + ); + + END IF; + + first_sf := FALSE; + + IF tmp_text IS NOT NULL AND tmp_text <> '' THEN + heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text; + END IF; + END LOOP; + EXIT WHEN heading_text <> ''; + END LOOP; + + IF heading_text <> '' THEN + IF no_thesaurus IS TRUE THEN + heading_text := tag_used || ' ' || public.naco_normalize(heading_text); + ELSE + heading_text := tag_used || '_' || COALESCE(nfi_used,'-') || '_' || thes_code || ' ' || public.naco_normalize(heading_text); + END IF; + ELSE + heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml); + END IF; + + RETURN heading_text; +END; +$func$ LANGUAGE PLPGSQL IMMUTABLE; + + +CREATE TABLE authority.simple_heading ( + id BIGSERIAL PRIMARY KEY, + record BIGINT NOT NULL REFERENCES authority.record_entry (id), + atag INT NOT NULL REFERENCES authority.control_set_authority_field (id), + value TEXT NOT NULL, + sort_value TEXT NOT NULL, + index_vector tsvector NOT NULL +); +CREATE TRIGGER authority_simple_heading_fti_trigger + BEFORE UPDATE OR INSERT ON authority.simple_heading + FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword'); + +CREATE INDEX authority_simple_heading_index_vector_idx ON authority.simple_heading USING GIST (index_vector); +CREATE INDEX authority_simple_heading_value_idx ON authority.simple_heading (value); +CREATE INDEX authority_simple_heading_sort_value_idx ON authority.simple_heading (sort_value); + +CREATE OR REPLACE FUNCTION authority.simple_heading_set( marcxml TEXT ) RETURNS SETOF authority.simple_heading AS $func$ +DECLARE + res authority.simple_heading%ROWTYPE; + acsaf authority.control_set_authority_field%ROWTYPE; + tag_used TEXT; + nfi_used TEXT; + sf TEXT; + cset INT; + heading_text TEXT; + sort_text TEXT; + tmp_text TEXT; + tmp_xml TEXT; + first_sf BOOL; + auth_id INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml)::INT; +BEGIN + + res.record := auth_id; + + SELECT control_set INTO cset + FROM authority.control_set_authority_field + WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]) ) + LIMIT 1; + + FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP + + res.atag := acsaf.id; + tag_used := acsaf.tag; + nfi_used := acsaf.nfi; + + FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)) LOOP + heading_text := ''; + + FOR sf IN SELECT * FROM regexp_split_to_table(acsaf.sf_list,'') LOOP + heading_text := heading_text || COALESCE( ' ' || oils_xpath_string('//*[@code="'||sf||'"]',tmp_xml::TEXT), ''); + END LOOP; + + heading_text := public.naco_normalize(heading_text); + + IF nfi_used IS NOT NULL THEN + + sort_text := SUBSTRING( + heading_text FROM + COALESCE( + NULLIF( + REGEXP_REPLACE( + oils_xpath_string('//*[@tag="'||tag_used||'"]/@ind'||nfi_used, marcxml), + $$\D+$$, + '', + 'g' + ), + '' + )::INT, + 0 + ) + 1 + ); + + ELSE + sort_text := heading_text; + END IF; + + IF heading_text IS NOT NULL AND heading_text <> '' THEN + res.value := heading_text; + res.sort_value := sort_text; + RETURN NEXT res; + END IF; + + END LOOP; + + END LOOP; + + RETURN; +END; +$func$ LANGUAGE PLPGSQL IMMUTABLE; + +-- Support function used to find the pivot for alpha-heading-browse style searching +CREATE OR REPLACE FUNCTION authority.simple_heading_find_pivot( a INT[], q TEXT ) RETURNS TEXT AS $$ +DECLARE + sort_value_row RECORD; + value_row RECORD; + t_term TEXT; +BEGIN + + t_term := public.naco_normalize(q); + + SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END + + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank, + ash.sort_value + INTO sort_value_row + FROM authority.simple_heading ash + WHERE ash.atag = ANY (a) + AND ash.sort_value >= t_term + ORDER BY rank DESC, ash.sort_value + LIMIT 1; + + SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END + + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank, + ash.sort_value + INTO value_row + FROM authority.simple_heading ash + WHERE ash.atag = ANY (a) + AND ash.value >= t_term + ORDER BY rank DESC, ash.sort_value + LIMIT 1; + + IF value_row.rank > sort_value_row.rank THEN + RETURN value_row.sort_value; + ELSE + RETURN sort_value_row.sort_value; + END IF; +END; +$$ LANGUAGE PLPGSQL; + + +CREATE OR REPLACE FUNCTION authority.simple_heading_browse_center( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$ +DECLARE + pivot_sort_value TEXT; + boffset INT DEFAULT 0; + aoffset INT DEFAULT 0; + blimit INT DEFAULT 0; + alimit INT DEFAULT 0; +BEGIN + + pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q); + + IF page = 0 THEN + blimit := pagesize / 2; + alimit := blimit; + + IF pagesize % 2 <> 0 THEN + alimit := alimit + 1; + END IF; + ELSE + blimit := pagesize; + alimit := blimit; + + boffset := pagesize / 2; + aoffset := boffset; + + IF pagesize % 2 <> 0 THEN + boffset := boffset + 1; + END IF; + END IF; + + IF page <= 0 THEN + RETURN QUERY + -- "bottom" half of the browse results + SELECT id FROM ( + SELECT ash.id, + row_number() over () + FROM authority.simple_heading ash + WHERE ash.atag = ANY (atag_list) + AND ash.sort_value < pivot_sort_value + ORDER BY ash.sort_value DESC + LIMIT blimit + OFFSET ABS(page) * pagesize - boffset + ) x ORDER BY row_number DESC; + END IF; + + IF page >= 0 THEN + RETURN QUERY + -- "bottom" half of the browse results + SELECT ash.id + FROM authority.simple_heading ash + WHERE ash.atag = ANY (atag_list) + AND ash.sort_value >= pivot_sort_value + ORDER BY ash.sort_value + LIMIT alimit + OFFSET ABS(page) * pagesize - aoffset; + END IF; +END; +$$ LANGUAGE PLPGSQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.simple_heading_browse_top( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ +DECLARE + pivot_sort_value TEXT; +BEGIN + + pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q); + + IF page < 0 THEN + RETURN QUERY + -- "bottom" half of the browse results + SELECT id FROM ( + SELECT ash.id, + row_number() over () + FROM authority.simple_heading ash + WHERE ash.atag = ANY (atag_list) + AND ash.sort_value < pivot_sort_value + ORDER BY ash.sort_value DESC + LIMIT pagesize + OFFSET (ABS(page) - 1) * pagesize + ) x ORDER BY row_number DESC; + END IF; + + IF page >= 0 THEN + RETURN QUERY + -- "bottom" half of the browse results + SELECT ash.id + FROM authority.simple_heading ash + WHERE ash.atag = ANY (atag_list) + AND ash.sort_value >= pivot_sort_value + ORDER BY ash.sort_value + LIMIT pagesize + OFFSET ABS(page) * pagesize ; + END IF; +END; +$$ LANGUAGE PLPGSQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.simple_heading_search_rank( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT ash.id + FROM authority.simple_heading ash, + public.naco_normalize($2) t(term), + plainto_tsquery('keyword'::regconfig,$2) ptsq(term) + WHERE ash.atag = ANY ($1) + AND ash.index_vector @@ ptsq.term + ORDER BY ts_rank_cd(ash.index_vector,ptsq.term,14)::numeric + + CASE WHEN ash.sort_value LIKE t.term || '%' THEN 2 ELSE 0 END + + CASE WHEN ash.value LIKE t.term || '%' THEN 1 ELSE 0 END DESC + LIMIT $4 + OFFSET $4 * $3; +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.simple_heading_search_heading( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT ash.id + FROM authority.simple_heading ash, + public.naco_normalize($2) t(term), + plainto_tsquery('keyword'::regconfig,$2) ptsq(term) + WHERE ash.atag = ANY ($1) + AND ash.index_vector @@ ptsq.term + ORDER BY ash.sort_value + LIMIT $4 + OFFSET $4 * $3; +$$ LANGUAGE SQL ROWS 10; + + +CREATE OR REPLACE FUNCTION authority.axis_authority_tags(a TEXT) RETURNS INT[] AS $$ + SELECT ARRAY_ACCUM(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1; +$$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION authority.axis_authority_tags_refs(a TEXT) RETURNS INT[] AS $$ + SELECT ARRAY_CAT( + ARRAY[a.field], + (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.field) + ) + FROM authority.browse_axis_authority_field_map a + WHERE axis = $1 +$$ LANGUAGE SQL; + + + +CREATE OR REPLACE FUNCTION authority.btag_authority_tags(btag TEXT) RETURNS INT[] AS $$ + SELECT ARRAY_ACCUM(authority_field) FROM authority.control_set_bib_field WHERE tag = $1 +$$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION authority.btag_authority_tags_refs(btag TEXT) RETURNS INT[] AS $$ + SELECT ARRAY_CAT( + ARRAY[a.authority_field], + (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.authority_field) + ) + FROM authority.control_set_bib_field a + WHERE a.tag = $1 +$$ LANGUAGE SQL; + + + +CREATE OR REPLACE FUNCTION authority.atag_authority_tags(atag TEXT) RETURNS INT[] AS $$ + SELECT ARRAY_ACCUM(id) FROM authority.control_set_authority_field WHERE tag = $1 +$$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION authority.atag_authority_tags_refs(atag TEXT) RETURNS INT[] AS $$ + SELECT ARRAY_CAT( + ARRAY[a.id], + (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.id) + ) + FROM authority.control_set_authority_field a + WHERE a.tag = $1 +$$ LANGUAGE SQL; + + +CREATE OR REPLACE FUNCTION authority.axis_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.btag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.atag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.axis_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.btag_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.atag_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + + +CREATE OR REPLACE FUNCTION authority.axis_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.btag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.atag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.axis_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.btag_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.atag_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + + +CREATE OR REPLACE FUNCTION authority.axis_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.btag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.atag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.axis_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.btag_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.atag_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + + +CREATE OR REPLACE FUNCTION authority.axis_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.btag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.atag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.axis_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.btag_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.atag_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + + +COMMIT; + diff --git a/Open-ILS/xul/staff_client/server/cat/marcedit.js b/Open-ILS/xul/staff_client/server/cat/marcedit.js index b18c8a7442..066a0845d7 100644 --- a/Open-ILS/xul/staff_client/server/cat/marcedit.js +++ b/Open-ILS/xul/staff_client/server/cat/marcedit.js @@ -1551,6 +1551,13 @@ function applyAuthority ( target, ui_sf, e4x_sf, new_vals ) { } for (var i = 0; i < new_vals.length; i++) { + + /* indicators for the authority datafield are carried over in the main entry linking subfield */ + if (new_vals[i].getAttribute('subfield') == '0') { + field.@ind1 = new_vals[i].getAttribute('ind1'); + field.@ind2 = new_vals[i].getAttribute('ind2'); + } + if (!new_vals[i].getAttribute('subfield')) continue; var val = new_vals[i].getAttribute('value'); @@ -1907,6 +1914,8 @@ function buildAuthorityPopupSelector (field, grid, auth_org, auth_id) { { "label" : '\u2021' + '0' + ' (' + auth_org + ')' + auth_id, "subfield" : '0', "tag" : dojo.attr(field, 'tag'), + "ind1" : dojo.attr(field, 'ind1'), + "ind2" : dojo.attr(field, 'ind2'), "value" : '(' + auth_org + ')' + auth_id } ) -- 2.11.0