<field reporter:label="Control Set" name="control_set" reporter:datatype="link"/>
<field reporter:label="Tag" name="tag" reporter:datatype="text" oils_obj:required="true" oils_obj:validate="^.{3}$"/>
<field reporter:label="Subfield List" name="sf_list" reporter:datatype="text" />
+ <field reporter:label="Non-filing Indicator" name="nfi" reporter:datatype="text" />
<field reporter:label="Name" name="name" reporter:datatype="text" oils_persist:i18n="true" oils_obj:required="true" />
<field reporter:label="Description" name="description" reporter:datatype="text" oils_persist:i18n="true" />
<field reporter:label="Subordinate Entries" name="sub_entries" reporter:datatype="link" oils_persist:virtual="true"/>
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;
register_record_transforms();
+ register_new_authorities_methods();
+
return 1;
}
}
}
+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;
# 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;
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(
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(
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);
}
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
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 */
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;
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);
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;
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;
+
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')),
(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')),
(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')),
(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')),
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 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
--- /dev/null
+-- 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;
+
}
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');
{ "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
}
)
projects / transitory.git / commitdiff