Bi-directional authority enhanced bib browse
authorMike Rylander <mrylander@gmail.com>
Thu, 23 May 2013 18:51:27 +0000 (14:51 -0400)
committerLebbeous Fogle-Weekley <lebbeous@esilibrary.com>
Thu, 26 Sep 2013 16:59:58 +0000 (12:59 -0400)
This functionality extends the work committed as the following set:

e071eb91a40d3871bc0e0a078b88cfdde7ba3b5e Finish stamping for bib record browser
c7bad82a66554251b8a0f636e629fe98680b7765 Stamping upgrade scripts for bib record browser
32ec20b71540b1230ecc15f1aec95059e044499e Fix problem where basic search bar was flush with left edge of page
256b4d24240332ae3fd2a5cc14b1709c90f18d05 Fix issues with multiple divs having the search-wrapper id.
cfc828d832ecee92478466cd2f4e174c2f4b5c3c OPAC Browse: bugfixes squashed together from LP #1177810
6990fd819c0dae385c3cc7a40d39a0ebed91086c Break up expensive queries, match index to quals
c0dea517395c733b77e8eb219d2c3f43f6098d42 OPAC Browse: some squashed commits from LP #1177810
9d37f6890ce6b591d62972ce3e341cecf7c45535 Fix problem with YYYY.schema.bib-auth-browse.sql.
e710ecbee519d374bcf69b0c535c3f83814c782b Bib record browser with 'see also', etc from linked authority headings
d2f4cac923668ff05854ffc9720cafe514b9b71c Link browse entries to authority record when possible
3eb616e56c82a5106e14f5a446f1d875015db7f0 Inter-authority linking

With this, we enhance the browse method to allow the return of authority-only
terms (that is, main entries not used by a bib record) if the authority record
from which the term comes is linked to another authority record via the
mechanism created by 3eb616e5, and that linked authority record is itself in
use by visible bibliographic records.

In order to accomplish this we:
 * extend normalization of both bib and auth records to allow a custom string
   to be used when joining parts of a term (for instance, joining subject
   components with " -- ")
 * cause authority headings to be including in the unique set of values
   captured by metabib.browse_entry during authority ingest
 * link authority-heading derived browse entries to their authority-indexed
   counterparts
 * extend the metabib.browse() stored procedure (and related) to inspect
   authority-linked browse entries
 * extend bibliographic-visiblity testing to cover those bibliographic records
   in use by remote-linked authority records.
 * generate browse links between headings found to be appropriately linked
   and bibliographically in-use and visible

The addition of this functionality will require, in the least, reingest of
all authority records, and would benefit from reingest of bibliographic
records as well, as ingest has been improved and extended.

Signed-off-by: Mike Rylander <mrylander@gmail.com>
Signed-off-by: Lebbeous Fogle-Weekley <lebbeous@esilibrary.com>
Signed-off-by: Chris Sharp <csharp@georgialibraries.org>
Open-ILS/examples/fm_IDL.xml
Open-ILS/src/perlmods/lib/OpenILS/WWW/EGCatLoader/Browse.pm
Open-ILS/src/sql/Pg/002.schema.config.sql
Open-ILS/src/sql/Pg/011.schema.authority.sql
Open-ILS/src/sql/Pg/030.schema.metabib.sql
Open-ILS/src/sql/Pg/950.data.seed-values.sql
Open-ILS/src/sql/Pg/999.functions.global.sql
Open-ILS/src/sql/Pg/upgrade/XXXX.schema.browse-auth-linking.plus-joiner.sql [new file with mode: 0644]
Open-ILS/src/templates/opac/browse.tt2

index 7ae11f2..66f365d 100644 (file)
@@ -2126,6 +2126,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA
                        <field reporter:label="Controlled Bib Fields" name="bib_fields" reporter:datatype="link" oils_persist:virtual="true"/>
                        <field reporter:label="Thesauri" name="thesauri" reporter:datatype="link" oils_persist:virtual="true"/>
                        <field reporter:label="Browse Axis Maps" name="axis_maps" reporter:datatype="link" oils_persist:virtual="true"/>
+                       <field reporter:label="Joiner" name="joiner" reporter:datatype="text" />
                </fields>
                <links>
                        <link field="axis_maps" reltype="has_many" key="field" map="" class="abaafm"/>
@@ -2164,6 +2165,26 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA
                </permacrud>
        </class>
 
+       <class id="acsbfmfm" controller="open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="authority::control_set_bib_field_metabib_field_map" oils_persist:tablename="authority.control_set_bib_field_metabib_field_map" reporter:label="Authority Control Set Bib Field to Metabib Field Map" oils_persist:field_safe="true">
+               <fields oils_persist:primary="id" oils_persist:sequence="authority.control_set_bib_field_metabib_field_map_id_seq">
+                       <field reporter:label="ID" name="id" reporter:datatype="id" reporter:selector="name"/>
+                       <field reporter:label="Bib Field" name="bib_field" reporter:datatype="link"/>
+                       <field reporter:label="Metabib Field" name="metabib_field" reporter:datatype="link"/>
+               </fields>
+               <links>
+                       <link field="bib_field" reltype="has_a" key="id" map="" class="acsbf"/>
+                       <link field="metabib_field" reltype="has_a" key="id" map="" class="cmf"/>
+               </links>
+               <permacrud xmlns="http://open-ils.org/spec/opensrf/IDL/permacrud/v1">
+                       <actions>
+                               <create permission="CREATE_AUTHORITY_CONTROL_SET" global_required="true"/>
+                               <retrieve/>
+                               <update permission="UPDATE_AUTHORITY_CONTROL_SET" global_required="true"/>
+                               <delete permission="UPDATE_AUTHORITY_CONTROL_SET" global_required="true"/>
+                       </actions>
+               </permacrud>
+       </class>
+
        <class id="at" controller="open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="authority::thesaurus" oils_persist:tablename="authority.thesaurus" reporter:label="Authority Thesaurus" oils_persist:field_safe="true">
                <fields oils_persist:primary="code">
                        <field reporter:label="Thesaurus Code" name="code" reporter:datatype="id" reporter:selector="name"/>
@@ -2288,6 +2309,18 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA
                        <link field="authority" reltype="has_a" key="id" map="" class="are"/>
                </links>
        </class>
+       <class id="ash" controller="open-ils.cstore" oils_obj:fieldmapper="authority::simple_heading" oils_persist:tablename="authority.simple_heading" reporter:label="Authority Simple Heading">
+               <fields oils_persist:primary="id" oils_persist:sequence="authority.simple_heading_id_seq">
+                       <field name="id" reporter:datatype="id" />
+                       <field name="record" reporter:datatype="link" />
+                       <field name="atag" reporter:datatype="link" />
+                       <field name="value" reporter:datatype="text" />
+               </fields>
+               <links>
+                       <link field="record" reltype="has_a" key="id" map="" class="are"/>
+                       <link field="atag" reltype="has_a" key="id" map="" class="acsaf"/>
+               </links>
+       </class>
        <class id="clm" controller="open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="config::language_map" oils_persist:tablename="config.language_map" reporter:label="Language Map" oils_persist:field_safe="true">
                <fields oils_persist:primary="code" oils_persist:sequence="">
                        <field reporter:label="Language Code" name="code" reporter:selector="value" reporter:datatype="text"/>
@@ -3301,6 +3334,17 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA
                        <link field="source" reltype="has_a" key="id" map="" class="bre"/>
                </links>
        </class>
+       <class id="mbeshm" controller="open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="metabib::browse_entry_simple_heading_map" oils_persist:tablename="metabib.browse_entry_simple_heading_map" reporter:label="Combined Browse Entry Simple Authority Heading Map" oils_persist:readonly="true">
+               <fields oils_persist:primary="id" oils_persist:sequence="metabib.browse_entry_simple_heading_map_id_seq">
+                       <field name="id" reporter:datatype="id" />
+                       <field name="entry" reporter:datatype="link"/>
+                       <field name="simple_heading" reporter:datatype="link"/>
+               </fields>
+               <links>
+                       <link field="entry" reltype="has_a" key="id" map="" class="mbe"/>
+                       <link field="simple_heading" reltype="has_a" key="id" map="" class="ash"/>
+               </links>
+       </class>
        <class id="mfe" controller="open-ils.cstore open-ils.pcrud" oils_obj:fieldmapper="metabib::field_entry" reporter:label="Combined Field Entry View" oils_persist:readonly="true">
                <oils_persist:source_definition>
                        SELECT * FROM metabib.author_field_entry
index 593eb2b..1d49aa5 100644 (file)
@@ -37,10 +37,12 @@ sub _init_browse_cache {
 }
 
 sub _get_authority_heading {
-    my ($field, $sf_lookup) = @_;
+    my ($field, $sf_lookup, $joiner) = @_;
+
+    $joiner ||= ' ';
 
     return join(
-        " ",
+        $joiner,
         map { $_->[1] } grep { $sf_lookup->{$_->[0]} } $field->subfields
     );
 }
@@ -119,13 +121,14 @@ sub find_authority_headings_and_notes {
 
     # By applying grep in this way, we get acsaf objects that *have* and
     # therefore *aren't* main entries, which is what we want.
-    foreach my $acsaf (grep { $_->main_entry } values(%$acsaf_table)) {
+    foreach my $acsaf (values(%$acsaf_table)) {
         my @fields = $record->field($acsaf->tag);
         my %sf_lookup = map { $_ => 1 } split("", $acsaf->display_sf_list);
         my @headings;
 
         foreach my $field (@fields) {
-            my $h = { heading => _get_authority_heading($field, \%sf_lookup) };
+            my $h = { main_entry => ( $acsaf->main_entry ? 0 : 1 ),
+                      heading => _get_authority_heading($field, \%sf_lookup), $acsaf->joiner };
 
             # XXX I was getting "target" from authority.authority_linking, but
             # that makes no sense: that table can only tell you that one
@@ -137,6 +140,9 @@ sub find_authority_headings_and_notes {
             $h->{target} = $2
                 if ($field->subfield('0') || "") =~ /(^|\))(\d+)$/;
 
+            # The target is the row id if this is a main entry...
+            $h->{target} = $row->{id} if $h->{main_entry};
+
             push @headings, $h;
         }
 
@@ -147,7 +153,7 @@ sub find_authority_headings_and_notes {
 }
 
 sub map_authority_headings_to_results {
-    my ($self, $linked, $results, $auth_ids) = @_;
+    my ($self, $linked, $results, $auth_ids, $authority_field_name) = @_;
 
     # Use the linked authority records' control sets to find and pick
     # out non-main-entry headings. Build the headings and make a
@@ -157,10 +163,10 @@ sub map_authority_headings_to_results {
     } @$linked;
 
     # Graft this authority heading data onto our main result set at the
-    # "authorities" column.
+    # named column, either "authorities" or "sees".
     foreach my $row (@$results) {
-        $row->{authorities} = [
-            map { $linked_headings_by_auth_id{$_} } @{$row->{authorities}}
+        $row->{$authority_field_name} = [
+            map { $linked_headings_by_auth_id{$_} } @{$row->{$authority_field_name}}
         ];
     }
 
@@ -190,7 +196,7 @@ sub map_authority_headings_to_results {
     # Soooo nesty!  We look for places where we'll need a count of bibs
     # linked to an authority record, and put it there for the template to find.
     for my $row (@$results) {
-        for my $auth (@{$row->{authorities}}) {
+        for my $auth (@{$row->{$authority_field_name}}) {
             if ($auth->{headings}) {
                 for my $outer_heading (@{$auth->{headings}}) {
                     for my $heading_blob (@{(values %$outer_heading)[0]}) {
@@ -212,34 +218,43 @@ sub map_authority_headings_to_results {
 sub flesh_browse_results {
     my ($self, $results) = @_;
 
-    # Turn comma-seprated strings of numbers in "authorities" column
-    # into arrays.
-    $_->{authorities} = [split /,/, $_->{authorities}] foreach @$results;
-
-    # Group them in one arrray, not worrying about dupes because we're about
-    # to use them in an IN () comparison in a SQL query.
-    my @auth_ids = map { @{$_->{authorities}} } @$results;
-
-    if (@auth_ids) {
-        # Get all linked authority records themselves
-        my $linked = $self->editor->json_query({
-            select => {
-                are => [qw/id marc control_set/],
-                aalink => [{column => "target", transform => "array_agg",
-                    aggregate => 1}]
-            },
-            from => {
-                are => {
-                    aalink => {
-                        type => "left",
-                        fkey => "id", field => "source"
+    for my $authority_field_name ( qw/authorities sees/ ) {
+        for my $r (@$results) {
+            # Turn comma-seprated strings of numbers in "authorities" and "sees"
+            # columns into arrays.
+            if ($r->{$authority_field_name}) {
+                $r->{$authority_field_name} = [split /,/, $r->{$authority_field_name}];
+            } else {
+                $r->{$authority_field_name} = [];
+            }
+            $r->{"list_$authority_field_name"} = [ @{$r->{$authority_field_name} } ];
+        }
+
+        # Group them in one arrray, not worrying about dupes because we're about
+        # to use them in an IN () comparison in a SQL query.
+        my @auth_ids = map { @{$_->{$authority_field_name}} } @$results;
+
+        if (@auth_ids) {
+            # Get all linked authority records themselves
+            my $linked = $self->editor->json_query({
+                select => {
+                    are => [qw/id marc control_set/],
+                    aalink => [{column => "target", transform => "array_agg",
+                        aggregate => 1}]
+                },
+                from => {
+                    are => {
+                        aalink => {
+                            type => "left",
+                            fkey => "id", field => "source"
+                        }
                     }
-                }
-            },
-            where => {"+are" => {id => \@auth_ids}}
-        }) or return;
+                },
+                where => {"+are" => {id => \@auth_ids}}
+            }) or return;
 
-        $self->map_authority_headings_to_results($linked, $results, \@auth_ids);
+            $self->map_authority_headings_to_results($linked, $results, \@auth_ids, $authority_field_name);
+        }
     }
 
     return 1;
index 702fbf9..89006c6 100644 (file)
@@ -196,6 +196,7 @@ CREATE TABLE config.metabib_field (
        browse_sort_xpath TEXT,
        facet_xpath     TEXT,
        authority_xpath TEXT,
+       joiner      TEXT,
        restrict        BOOL    DEFAULT FALSE NOT NULL
 );
 COMMENT ON TABLE config.metabib_field IS $$
index 3277555..17fe07d 100644 (file)
@@ -38,6 +38,7 @@ CREATE TABLE authority.control_set_authority_field (
     display_sf_list     TEXT NOT NULL,
     name        TEXT    NOT NULL, -- i18n
     description TEXT,             -- i18n
+    joiner      TEXT,
     linking_subfield CHAR(1)
 );
 
@@ -47,6 +48,57 @@ CREATE TABLE authority.control_set_bib_field (
     tag             CHAR(3) NOT NULL
 );
 
+-- Seed data will be generated from class <-> axis mapping
+CREATE TABLE authority.control_set_bib_field_metabib_field_map (
+    id              SERIAL  PRIMARY KEY,
+    bib_field       INT     NOT NULL REFERENCES authority.control_set_bib_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+    metabib_field   INT     NOT NULL REFERENCES config.metabib_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+    CONSTRAINT a_bf_mf_map_once UNIQUE (bib_field, metabib_field)
+);
+
+CREATE VIEW authority.control_set_auth_field_metabib_field_map_main AS
+    SELECT  DISTINCT b.authority_field, m.metabib_field
+      FROM  authority.control_set_bib_field_metabib_field_map m JOIN authority.control_set_bib_field b ON (b.id = m.bib_field);
+COMMENT ON VIEW authority.control_set_auth_field_metabib_field_map_main IS $$metabib fields for main entry auth fields$$;
+
+CREATE VIEW authority.control_set_auth_field_metabib_field_map_refs_only AS
+    SELECT  DISTINCT a.id AS authority_field, m.metabib_field
+      FROM  authority.control_set_authority_field a
+            JOIN authority.control_set_authority_field ame ON (a.main_entry = ame.id)
+            JOIN authority.control_set_bib_field b ON (b.authority_field = ame.id)
+            JOIN authority.control_set_bib_field_metabib_field_map mf ON (mf.bib_field = b.id)
+            JOIN authority.control_set_auth_field_metabib_field_map_main m ON (ame.id = m.authority_field);
+COMMENT ON VIEW authority.control_set_auth_field_metabib_field_map_refs_only IS $$metabib fields for NON-main entry auth fields$$;
+
+CREATE VIEW authority.control_set_auth_field_metabib_field_map_refs AS
+    SELECT * FROM authority.control_set_auth_field_metabib_field_map_main
+        UNION
+    SELECT * FROM authority.control_set_auth_field_metabib_field_map_refs_only;
+COMMENT ON VIEW authority.control_set_auth_field_metabib_field_map_refs IS $$metabib fields for all auth fields$$;
+
+
+-- blind refs only is probably what we want for lookup in bib/auth browse
+CREATE VIEW authority.control_set_auth_field_metabib_field_map_blind_refs_only AS
+    SELECT  r.*
+      FROM  authority.control_set_auth_field_metabib_field_map_refs_only r
+            JOIN authority.control_set_authority_field a ON (r.authority_field = a.id)
+      WHERE linking_subfield IS NULL;
+COMMENT ON VIEW authority.control_set_auth_field_metabib_field_map_blind_refs_only IS $$metabib fields for NON-main entry auth fields that can't be linked to other records$$; -- '
+
+CREATE VIEW authority.control_set_auth_field_metabib_field_map_blind_refs AS
+    SELECT  r.*
+      FROM  authority.control_set_auth_field_metabib_field_map_refs r
+            JOIN authority.control_set_authority_field a ON (r.authority_field = a.id)
+      WHERE linking_subfield IS NULL;
+COMMENT ON VIEW authority.control_set_auth_field_metabib_field_map_blind_refs IS $$metabib fields for all auth fields that can't be linked to other records$$; -- '
+
+CREATE VIEW authority.control_set_auth_field_metabib_field_map_blind_main AS
+    SELECT  r.*
+      FROM  authority.control_set_auth_field_metabib_field_map_main r
+            JOIN authority.control_set_authority_field a ON (r.authority_field = a.id)
+      WHERE linking_subfield IS NULL;
+COMMENT ON VIEW authority.control_set_auth_field_metabib_field_map_blind_main IS $$metabib fields for main entry auth fields that can't be linked to other records$$; -- '
+
 CREATE TABLE authority.thesaurus (
     code        TEXT    PRIMARY KEY,     -- MARC21 thesaurus code
     control_set INT     REFERENCES authority.control_set (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
@@ -193,7 +245,7 @@ BEGIN
         first_sf := TRUE;
 
         FOR tag_node IN SELECT unnest(oils_xpath('//*[@tag="'||tag_used||'"]',marcxml)) LOOP
-            FOR sf_node IN SELECT unnest(oils_xpath('//*[contains("'||acsaf.sf_list||'",@code)]',tag_node)) LOOP
+            FOR sf_node IN SELECT unnest(oils_xpath('./*[contains("'||acsaf.sf_list||'",@code)]',tag_node)) LOOP
 
                 tmp_text := oils_xpath_string('.', sf_node);
                 sf := oils_xpath_string('./@code', sf_node);
@@ -270,40 +322,39 @@ DECLARE
     sf              TEXT;
     cset            INT;
     heading_text    TEXT;
+    joiner_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;
+    auth_id         INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT; 
 BEGIN
 
-    res.record := auth_id;
+    SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
 
-    -- XXX this SELECT control_set... business below should actually only
-    -- be a fallback.  We should (SELECT control_set FROM authority.record_entry
-    -- WHERE id = auth_id) when we have an auth_id, and use that if we can get
-    -- it.
+    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.control_set_authority_field
-      WHERE tag IN ( SELECT UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]) )
-      LIMIT 1;
+    res.record := auth_id;
 
     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;
+        joiner_text := COALESCE(acsaf.joiner, ' ');
 
         FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)) LOOP
 
-            heading_text := public.naco_normalize(
-                COALESCE(
-                    oils_xpath_string('//*[contains("'||acsaf.sf_list||'",@code)]',tmp_xml::TEXT, ' '),
-                    ''
-                )
+            heading_text := COALESCE(
+                oils_xpath_string('./*[contains("'||acsaf.display_sf_list||'",@code)]', tmp_xml::TEXT, joiner_text),
+                ''
             );
-            
+
             IF nfi_used IS NOT NULL THEN
 
                 sort_text := SUBSTRING(
@@ -328,7 +379,8 @@ BEGIN
 
             IF heading_text IS NOT NULL AND heading_text <> '' THEN
                 res.value := heading_text;
-                res.sort_value := sort_text;
+                res.sort_value := public.naco_normalize(sort_text);
+                res.index_vector = to_tsvector('keyword'::regconfig, res.sort_value);
                 RETURN NEXT res;
             END IF;
 
index 542eaed..9cf1ed8 100644 (file)
@@ -212,7 +212,13 @@ CREATE INDEX browse_entry_def_map_def_idx ON metabib.browse_entry_def_map (def);
 CREATE INDEX browse_entry_def_map_entry_idx ON metabib.browse_entry_def_map (entry);
 CREATE INDEX browse_entry_def_map_source_idx ON metabib.browse_entry_def_map (source);
 
-
+CREATE TABLE metabib.browse_entry_simple_heading_map (
+    id BIGSERIAL PRIMARY KEY,
+    entry BIGINT REFERENCES metabib.browse_entry (id),
+    simple_heading BIGINT REFERENCES authority.simple_heading (id) ON DELETE CASCADE
+);
+CREATE INDEX browse_entry_sh_map_entry_idx ON metabib.browse_entry_simple_heading_map (entry);
+CREATE INDEX browse_entry_sh_map_sh_idx ON metabib.browse_entry_simple_heading_map (simple_heading);
 
 CREATE OR REPLACE FUNCTION metabib.facet_normalize_trigger () RETURNS TRIGGER AS $$
 DECLARE
@@ -435,6 +441,8 @@ BEGIN
     -- Loop over the indexing entries
     FOR idx IN SELECT * FROM config.metabib_field ORDER BY format LOOP
 
+        joiner := COALESCE(idx.joiner, default_joiner);
+
         SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
 
         -- See if we can skip the XSLT ... it's expensive
@@ -455,21 +463,27 @@ BEGIN
         FOR xml_node IN SELECT x FROM unnest(xml_node_list) AS x LOOP
             CONTINUE WHEN xml_node !~ E'^\\s*<';
 
-            curr_text := ARRAY_TO_STRING(
+            -- XXX much of this should be moved into oils_xpath_string...
+            curr_text := ARRAY_TO_STRING(evergreen.array_remove_item_by_value(evergreen.array_remove_item_by_value(
                 oils_xpath( '//text()',
-                    REGEXP_REPLACE( -- This escapes all &s not followed by "amp;".  Data ise returned from oils_xpath (above) in UTF-8, not entity encoded
-                        REGEXP_REPLACE( -- This escapes embeded <s
-                            xml_node,
-                            $re$(>[^<]+)(<)([^>]+<)$re$,
-                            E'\\1&lt;\\3',
+                    REGEXP_REPLACE(
+                        REGEXP_REPLACE( -- This escapes all &s not followed by "amp;".  Data ise returned from oils_xpath (above) in UTF-8, not entity encoded
+                            REGEXP_REPLACE( -- This escapes embeded <s
+                                xml_node,
+                                $re$(>[^<]+)(<)([^>]+<)$re$,
+                                E'\\1&lt;\\3',
+                                'g'
+                            ),
+                            '&(?!amp;)',
+                            '&amp;',
                             'g'
                         ),
-                        '&(?!amp;)',
-                        '&amp;',
+                        E'\\s+',
+                        ' ',
                         'g'
                     )
-                ),
-                ' '
+                ), ' '), ''),
+                joiner
             );
 
             CONTINUE WHEN curr_text IS NULL OR curr_text = '';
@@ -505,7 +519,7 @@ BEGIN
                 output_row.source = rid;
                 output_row.value = BTRIM(REGEXP_REPLACE(browse_text, E'\\s+', ' ', 'g'));
                 output_row.sort_value :=
-                    public.search_normalize(sort_value);
+                    public.naco_normalize(sort_value);
 
                 output_row.authority := NULL;
 
@@ -1534,7 +1548,7 @@ BEGIN
         evergreen.regexp_split_to_array(orig, E'\\W+'), ' '
     );
 
-    normalized := public.search_normalize(orig); -- also trim()s
+    normalized := public.naco_normalize(orig); -- also trim()s
     plain := trim(orig);
 
     IF NOT orig_ended_in_space THEN
@@ -1779,33 +1793,76 @@ CREATE TYPE metabib.flat_browse_entry_appearance AS (
     value           TEXT,
     fields          TEXT,
     authorities     TEXT,
+    sees            TEXT,
     sources         INT,        -- visible ones, that is
+    asources        INT,        -- visible ones, that is
     row_number      INT,        -- internal use, sort of
     accurate        BOOL,       -- Count in sources field is accurate? Not
                                 -- if we had more than a browse superpage
                                 -- of records to look at.
+    aaccurate       BOOL,       -- See previous comment...
     pivot_point     BIGINT
 );
 
 
+CREATE OR REPLACE FUNCTION metabib.browse_bib_pivot(
+    INT[],
+    TEXT
+) RETURNS BIGINT AS $p$
+    SELECT  mbe.id
+      FROM  metabib.browse_entry mbe
+            JOIN metabib.browse_entry_def_map mbedm ON (
+                mbedm.entry = mbe.id
+                AND mbedm.def = ANY($1)
+            )
+      WHERE mbe.sort_value >= public.naco_normalize($2)
+      ORDER BY mbe.sort_value, mbe.value LIMIT 1;
+$p$ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION metabib.browse_authority_pivot(
+    INT[],
+    TEXT
+) RETURNS BIGINT AS $p$
+    SELECT  mbe.id
+      FROM  metabib.browse_entry mbe
+            JOIN metabib.browse_entry_simple_heading_map mbeshm ON ( mbeshm.entry = mbe.id )
+            JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
+            JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
+                ash.atag = map.authority_field
+                AND map.metabib_field = ANY($1)
+            )
+      WHERE mbe.sort_value >= public.naco_normalize($2)
+      ORDER BY mbe.sort_value, mbe.value LIMIT 1;
+$p$ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION metabib.browse_authority_refs_pivot(
+    INT[],
+    TEXT
+) RETURNS BIGINT AS $p$
+    SELECT  mbe.id
+      FROM  metabib.browse_entry mbe
+            JOIN metabib.browse_entry_simple_heading_map mbeshm ON ( mbeshm.entry = mbe.id )
+            JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
+            JOIN authority.control_set_auth_field_metabib_field_map_refs_only map ON (
+                ash.atag = map.authority_field
+                AND map.metabib_field = ANY($1)
+            )
+      WHERE mbe.sort_value >= public.naco_normalize($2)
+      ORDER BY mbe.sort_value, mbe.value LIMIT 1;
+$p$ LANGUAGE SQL;
+
 CREATE OR REPLACE FUNCTION metabib.browse_pivot(
-    search_field        INT[],
-    browse_term         TEXT
+    INT[],
+    TEXT
 ) RETURNS BIGINT AS $p$
-DECLARE
-    id                  BIGINT;
-BEGIN
-    SELECT INTO id mbe.id FROM metabib.browse_entry mbe
-        JOIN metabib.browse_entry_def_map mbedm ON (
-            mbedm.entry = mbe.id AND
-            mbedm.def = ANY(search_field)
-        )
-        WHERE mbe.sort_value >= public.search_normalize(browse_term)
-        ORDER BY mbe.sort_value, mbe.value LIMIT 1;
+    SELECT  id FROM metabib.browse_entry
+      WHERE id IN (
+                metabib.browse_bib_pivot($1, $2),
+                metabib.browse_authority_refs_pivot($1,$2) -- only look in 4xx, 5xx, 7xx of authority
+            )
+      ORDER BY sort_value, value LIMIT 1;
+$p$ LANGUAGE SQL;
 
-    RETURN id;
-END;
-$p$ LANGUAGE PLPGSQL;
 
 CREATE OR REPLACE FUNCTION metabib.staged_browse(
     query                   TEXT,
@@ -1822,6 +1879,9 @@ DECLARE
     curs                    REFCURSOR;
     rec                     RECORD;
     qpfts_query             TEXT;
+    aqpfts_query            TEXT;
+    afields                 INT[];
+    bfields                 INT[];
     result_row              metabib.flat_browse_entry_appearance%ROWTYPE;
     results_skipped         INT := 0;
     row_counter             INT := 0;
@@ -1830,6 +1890,8 @@ DECLARE
     slice_end               INT;
     full_end                INT;
     all_records             BIGINT[];
+    all_brecords             BIGINT[];
+    all_arecords            BIGINT[];
     superpage_of_records    BIGINT[];
     superpage_size          INT;
 BEGIN
@@ -1850,53 +1912,116 @@ BEGIN
             RETURN;
         END IF;
 
-        -- Gather aggregate data based on the MBE row we're looking at now
-        SELECT INTO all_records, result_row.authorities, result_row.fields
+
+        -- Gather aggregate data based on the MBE row we're looking at now, authority axis
+        SELECT INTO all_arecords, result_row.sees, afields
+                ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
+                ARRAY_TO_STRING(ARRAY_AGG(DISTINCT aal.source), $$,$$), -- authority record ids
+                ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
+
+          FROM  metabib.browse_entry_simple_heading_map mbeshm
+                JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
+                JOIN authority.authority_linking aal ON ( ash.record = aal.source )
+                JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
+                JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
+                    ash.atag = map.authority_field
+                    AND map.metabib_field = ANY(fields)
+                )
+          WHERE mbeshm.entry = rec.id;
+
+
+        -- Gather aggregate data based on the MBE row we're looking at now, bib axis
+        SELECT INTO all_brecords, result_row.authorities, bfields
                 ARRAY_AGG(DISTINCT source),
                 ARRAY_TO_STRING(ARRAY_AGG(DISTINCT authority), $$,$$),
-                ARRAY_TO_STRING(ARRAY_AGG(DISTINCT def), $$,$$)
+                ARRAY_AGG(DISTINCT def)
           FROM  metabib.browse_entry_def_map
           WHERE entry = rec.id
                 AND def = ANY(fields);
 
+        SELECT INTO result_row.fields ARRAY_TO_STRING(ARRAY_AGG(DISTINCT x), $$,$$) FROM UNNEST(afields || bfields) x;
+
         result_row.sources := 0;
+        result_row.asources := 0;
+
+        -- Bib-linked vis checking
+        IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
+
+            full_end := ARRAY_LENGTH(all_brecords, 1);
+            superpage_size := COALESCE(browse_superpage_size, full_end);
+            slice_start := 1;
+            slice_end := superpage_size;
+
+            WHILE result_row.sources = 0 AND slice_start <= full_end LOOP
+                superpage_of_records := all_brecords[slice_start:slice_end];
+                qpfts_query :=
+                    'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, ' ||
+                    '1::INT AS rel FROM (SELECT UNNEST(' ||
+                    quote_literal(superpage_of_records) || '::BIGINT[]) AS r) rr';
+
+                -- We use search.query_parser_fts() for visibility testing.
+                -- We're calling it once per browse-superpage worth of records
+                -- out of the set of records related to a given mbe, until we've
+                -- either exhausted that set of records or found at least 1
+                -- visible record.
+
+                SELECT INTO result_row.sources visible
+                    FROM search.query_parser_fts(
+                        context_org, NULL, qpfts_query, NULL,
+                        context_locations, 0, NULL, NULL, FALSE, staff, FALSE
+                    ) qpfts
+                    WHERE qpfts.rel IS NULL;
+
+                slice_start := slice_start + superpage_size;
+                slice_end := slice_end + superpage_size;
+            END LOOP;
 
-        full_end := ARRAY_LENGTH(all_records, 1);
-        superpage_size := COALESCE(browse_superpage_size, full_end);
-        slice_start := 1;
-        slice_end := superpage_size;
-
-        WHILE result_row.sources = 0 AND slice_start <= full_end LOOP
-            superpage_of_records := all_records[slice_start:slice_end];
-            qpfts_query :=
-                'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, ' ||
-                '1::INT AS rel FROM (SELECT UNNEST(' ||
-                quote_literal(superpage_of_records) || '::BIGINT[]) AS r) rr';
-
-            -- We use search.query_parser_fts() for visibility testing.
-            -- We're calling it once per browse-superpage worth of records
-            -- out of the set of records related to a given mbe, until we've
-            -- either exhausted that set of records or found at least 1
-            -- visible record.
-
-            SELECT INTO result_row.sources visible
-                FROM search.query_parser_fts(
-                    context_org, NULL, qpfts_query, NULL,
-                    context_locations, 0, NULL, NULL, FALSE, staff, FALSE
-                ) qpfts
-                WHERE qpfts.rel IS NULL;
-
-            slice_start := slice_start + superpage_size;
-            slice_end := slice_end + superpage_size;
-        END LOOP;
+            -- Accurate?  Well, probably.
+            result_row.accurate := browse_superpage_size IS NULL OR
+                browse_superpage_size >= full_end;
 
-        -- Accurate?  Well, probably.
-        result_row.accurate := browse_superpage_size IS NULL OR
-            browse_superpage_size >= full_end;
+        END IF;
+
+        -- Authority-linked vis checking
+        IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
+
+            full_end := ARRAY_LENGTH(all_arecords, 1);
+            superpage_size := COALESCE(browse_superpage_size, full_end);
+            slice_start := 1;
+            slice_end := superpage_size;
+
+            WHILE result_row.asources = 0 AND slice_start <= full_end LOOP
+                superpage_of_records := all_arecords[slice_start:slice_end];
+                qpfts_query :=
+                    'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, ' ||
+                    '1::INT AS rel FROM (SELECT UNNEST(' ||
+                    quote_literal(superpage_of_records) || '::BIGINT[]) AS r) rr';
+
+                -- We use search.query_parser_fts() for visibility testing.
+                -- We're calling it once per browse-superpage worth of records
+                -- out of the set of records related to a given mbe, via
+                -- authority until we've either exhausted that set of records
+                -- or found at least 1 visible record.
+
+                SELECT INTO result_row.asources visible
+                    FROM search.query_parser_fts(
+                        context_org, NULL, qpfts_query, NULL,
+                        context_locations, 0, NULL, NULL, FALSE, staff, FALSE
+                    ) qpfts
+                    WHERE qpfts.rel IS NULL;
+
+                slice_start := slice_start + superpage_size;
+                slice_end := slice_end + superpage_size;
+            END LOOP;
 
-        IF result_row.sources > 0 THEN
-            -- We've got a browse entry with visible holdings. Yay.
 
+            -- Accurate?  Well, probably.
+            result_row.aaccurate := browse_superpage_size IS NULL OR
+                browse_superpage_size >= full_end;
+
+        END IF;
+
+        IF result_row.sources > 0 OR result_row.asources > 0 THEN
 
             -- The function that calls this function needs row_number in order
             -- to correctly order results from two different runs of this
@@ -1919,7 +2044,9 @@ BEGIN
                 result_row.fields := NULL;
                 result_row.value := NULL;
                 result_row.sources := NULL;
+                result_row.sees := NULL;
                 result_row.accurate := NULL;
+                result_row.aaccurate := NULL;
                 result_row.pivot_point := rec.id;
 
                 IF row_counter >= next_pivot_pos THEN
@@ -2013,15 +2140,27 @@ BEGIN
     -- rows may be fetched in a loop until some condition is satisfied, without
     -- waiting for a result set of fixed size to be collected all at once.
     core_query := '
-    SELECT
-        mbe.id,
+SELECT  mbe.id,
         mbe.value,
         mbe.sort_value
-    FROM metabib.browse_entry mbe
-    WHERE EXISTS (SELECT 1 FROM  metabib.browse_entry_def_map mbedm WHERE
-        mbedm.entry = mbe.id AND
-        mbedm.def = ANY(' || quote_literal(search_field) || ')
-    ) AND ';
+  FROM  metabib.browse_entry mbe
+  WHERE (
+            EXISTS ( -- are there any bibs using this mbe via the requested fields?
+                SELECT  1
+                  FROM  metabib.browse_entry_def_map mbedm
+                  WHERE mbedm.entry = mbe.id AND mbedm.def = ANY(' || quote_literal(search_field) || ')
+                  LIMIT 1
+            ) OR EXISTS ( -- are there any authorities using this mbe via the requested fields?
+                SELECT  1
+                  FROM  metabib.browse_entry_simple_heading_map mbeshm
+                        JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
+                        JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
+                            ash.atag = map.authority_field
+                            AND map.metabib_field = ANY(' || quote_literal(search_field) || ')
+                        )
+                  WHERE mbeshm.entry = mbe.id
+            )
+        ) AND ';
 
     -- This is the variant of the query for browsing backward.
     back_query := core_query ||
index 90bd1e0..47a6a46 100644 (file)
@@ -179,6 +179,7 @@ INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath,
 INSERT INTO config.metabib_field ( id, field_class, name, label, xpath, format, search_field, facet_field, browse_field) VALUES
     (31, 'title', 'browse', oils_i18n_gettext(31, 'Title Proper (Browse)', 'cmf', 'label'), $$//mods32:mods/mods32:titleInfo[not (@type)]/mods32:title$$, 'mods32', FALSE, FALSE, TRUE);
 
+UPDATE config.metabib_field SET joiner = ' -- ' WHERE field_class = 'subject' AND name NOT IN ('name', 'complete');
 
 SELECT SETVAL('config.metabib_field_id_seq'::TEXT, (SELECT MAX(id) FROM config.metabib_field), TRUE);
 
@@ -10577,8 +10578,13 @@ INSERT INTO authority.control_set_authority_field (id, control_set, main_entry,
     (71, 1, 11, '485', 'ivwxyz4', 'ivxyz', oils_i18n_gettext('71','See From Tracing -- Form Subdivision','acsaf','name')),
     (72, 1, 12, '448', 'aivwxyz4', 'aivxyz', oils_i18n_gettext('72','See From Tracing -- Chronological Term','acsaf','name'));
 
+-- No linking on 4xx fields for LOC
 UPDATE authority.control_set_authority_field
-    SET linking_subfield = '0' WHERE main_entry IS NOT NULL;
+    SET linking_subfield = '0' WHERE tag LIKE ANY (ARRAY['5%','7%']);
+
+-- Set the default joiner for subject-ish authority fields
+UPDATE authority.control_set_authority_field
+    SET joiner = ' -- ' WHERE tag LIKE ANY (ARRAY['_4_','_5_','_8_']);
 
 
 INSERT INTO authority.browse_axis (code,name,description,sorter) VALUES
@@ -10647,6 +10653,55 @@ INSERT INTO authority.control_set_bib_field (tag, authority_field)
     SELECT '655', id FROM authority.control_set_authority_field WHERE tag IN ('155','180','181','182','185')
 ;
 
+-- Map between authority controlled bib fields and stock indexing metabib fields
+INSERT INTO authority.control_set_bib_field_metabib_field_map (bib_field, metabib_field)
+    SELECT  DISTINCT b.id AS bib_field, m.id AS metabib_field
+      FROM  authority.control_set_bib_field b JOIN authority.control_set_authority_field a ON (b.authority_field = a.id), config.metabib_field m
+      WHERE a.tag = '100' AND m.name = 'personal'
+
+        UNION
+
+    SELECT  DISTINCT b.id AS bib_field, m.id AS metabib_field
+      FROM  authority.control_set_bib_field b JOIN authority.control_set_authority_field a ON (b.authority_field = a.id), config.metabib_field m
+      WHERE a.tag = '110' AND m.name = 'corporate'
+
+        UNION
+
+    SELECT  DISTINCT b.id AS bib_field, m.id AS metabib_field
+      FROM  authority.control_set_bib_field b JOIN authority.control_set_authority_field a ON (b.authority_field = a.id), config.metabib_field m
+      WHERE a.tag = '111' AND m.name = 'conference'
+
+        UNION
+
+    SELECT  DISTINCT b.id AS bib_field, m.id AS metabib_field
+      FROM  authority.control_set_bib_field b JOIN authority.control_set_authority_field a ON (b.authority_field = a.id), config.metabib_field m
+      WHERE a.tag = '130' AND m.name = 'uniform'
+
+        UNION
+
+    SELECT  DISTINCT b.id AS bib_field, m.id AS metabib_field
+      FROM  authority.control_set_bib_field b JOIN authority.control_set_authority_field a ON (b.authority_field = a.id), config.metabib_field m
+      WHERE a.tag = '148' AND m.name = 'temporal'
+
+        UNION
+
+    SELECT  DISTINCT b.id AS bib_field, m.id AS metabib_field
+      FROM  authority.control_set_bib_field b JOIN authority.control_set_authority_field a ON (b.authority_field = a.id), config.metabib_field m
+      WHERE a.tag = '150' AND m.name = 'topic'
+
+        UNION
+
+    SELECT  DISTINCT b.id AS bib_field, m.id AS metabib_field
+      FROM  authority.control_set_bib_field b JOIN authority.control_set_authority_field a ON (b.authority_field = a.id), config.metabib_field m
+      WHERE a.tag = '151' AND m.name = 'geographic'
+
+        UNION
+
+    SELECT  DISTINCT b.id AS bib_field, m.id AS metabib_field
+      FROM  authority.control_set_bib_field b JOIN authority.control_set_authority_field a ON (b.authority_field = a.id), config.metabib_field m
+      WHERE a.tag = '155' AND m.name = 'genre' -- Just in case...
+;
+
 INSERT INTO authority.thesaurus (code, name, control_set) VALUES
     ('a', oils_i18n_gettext('a','Library of Congress Subject Headings','at','name'), 1),
     ('b', oils_i18n_gettext('b','LC subject headings for children''s literature','at','name'), 1), 
index c8f3f7c..e57158d 100644 (file)
@@ -1577,6 +1577,11 @@ $func$ LANGUAGE PLPGSQL;
 
 -- AFTER UPDATE OR INSERT trigger for authority.record_entry
 CREATE OR REPLACE FUNCTION authority.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
+DECLARE
+    ashs    authority.simple_heading%ROWTYPE;
+    mbe_row metabib.browse_entry%ROWTYPE;
+    mbe_id  BIGINT;
+    ash_id  BIGINT;
 BEGIN
 
     IF NEW.deleted IS TRUE THEN -- If this authority is deleted
@@ -1612,8 +1617,28 @@ BEGIN
             NEW.id, NEW.control_set, NEW.marc::XML
         );
 
-    INSERT INTO authority.simple_heading (record,atag,value,sort_value)
-        SELECT record, atag, value, sort_value FROM authority.simple_heading_set(NEW.marc);
+    FOR ashs IN SELECT * FROM authority.simple_heading_set(NEW.marc) LOOP
+
+        INSERT INTO authority.simple_heading (record,atag,value,sort_value)
+            VALUES (ashs.record, ashs.atag, ashs.value, ashs.sort_value);
+            ash_id := CURRVAL('authority.simple_heading_id_seq'::REGCLASS);
+
+        SELECT INTO mbe_row * FROM metabib.browse_entry
+            WHERE value = ashs.value AND sort_value = ashs.sort_value;
+
+        IF FOUND THEN
+            mbe_id := mbe_row.id;
+        ELSE
+            INSERT INTO metabib.browse_entry
+                ( value, sort_value ) VALUES
+                ( ashs.value, ashs.sort_value );
+
+            mbe_id := CURRVAL('metabib.browse_entry_id_seq'::REGCLASS);
+        END IF;
+
+        INSERT INTO metabib.browse_entry_simple_heading_map (entry,simple_heading) VALUES (mbe_id,ash_id);
+
+    END LOOP;
 
     -- Flatten and insert the afr data
     PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_full_rec' AND enabled;
diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.browse-auth-linking.plus-joiner.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.browse-auth-linking.plus-joiner.sql
new file mode 100644 (file)
index 0000000..ecbe751
--- /dev/null
@@ -0,0 +1,989 @@
+-- Evergreen DB patch XXXX.schema.browse-auth-linking.plus-joiner.sql
+--
+-- In this upgrade script we complete inter-subfield joiner support, so that
+-- subject components can be separated by " -- ", for instance.  That's the
+-- easy part.
+--
+-- We also add the ability to browse by in-use authority main entries and find
+-- bibs that use unauthorized versions of the authority's value, by string matching.
+--
+BEGIN;
+
+
+-- check whether patch can be applied
+SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
+
+ALTER TABLE config.metabib_field ADD COLUMN joiner TEXT;
+UPDATE config.metabib_field SET joiner = ' -- ' WHERE field_class = 'subject' AND name NOT IN ('name', 'complete');
+
+-- To avoid problems with altering a table column after doing an
+-- update.
+ALTER TABLE authority.control_set_authority_field DISABLE TRIGGER ALL;
+
+ALTER TABLE authority.control_set_authority_field ADD COLUMN joiner TEXT;
+UPDATE authority.control_set_authority_field SET joiner = ' -- ' WHERE tag LIKE ANY (ARRAY['_4_','_5_','_8_']);
+
+ALTER TABLE authority.control_set_authority_field ENABLE TRIGGER ALL;
+
+-- Seed data will be generated from class <-> axis mapping
+CREATE TABLE authority.control_set_bib_field_metabib_field_map (
+    id              SERIAL  PRIMARY KEY,
+    bib_field       INT     NOT NULL REFERENCES authority.control_set_bib_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+    metabib_field   INT     NOT NULL REFERENCES config.metabib_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+    CONSTRAINT a_bf_mf_map_once UNIQUE (bib_field, metabib_field)
+);
+
+CREATE VIEW authority.control_set_auth_field_metabib_field_map_main AS
+    SELECT  DISTINCT b.authority_field, m.metabib_field
+      FROM  authority.control_set_bib_field_metabib_field_map m JOIN authority.control_set_bib_field b ON (b.id = m.bib_field);
+COMMENT ON VIEW authority.control_set_auth_field_metabib_field_map_main IS $$metabib fields for main entry auth fields$$;
+
+CREATE VIEW authority.control_set_auth_field_metabib_field_map_refs_only AS
+    SELECT  DISTINCT a.id AS authority_field, m.metabib_field
+      FROM  authority.control_set_authority_field a
+            JOIN authority.control_set_authority_field ame ON (a.main_entry = ame.id)
+            JOIN authority.control_set_bib_field b ON (b.authority_field = ame.id)
+            JOIN authority.control_set_bib_field_metabib_field_map mf ON (mf.bib_field = b.id)
+            JOIN authority.control_set_auth_field_metabib_field_map_main m ON (ame.id = m.authority_field);
+COMMENT ON VIEW authority.control_set_auth_field_metabib_field_map_refs_only IS $$metabib fields for NON-main entry auth fields$$;
+
+CREATE VIEW authority.control_set_auth_field_metabib_field_map_refs AS
+    SELECT * FROM authority.control_set_auth_field_metabib_field_map_main
+        UNION
+    SELECT * FROM authority.control_set_auth_field_metabib_field_map_refs_only;
+COMMENT ON VIEW authority.control_set_auth_field_metabib_field_map_refs IS $$metabib fields for all auth fields$$;
+
+
+-- blind refs only is probably what we want for lookup in bib/auth browse
+CREATE VIEW authority.control_set_auth_field_metabib_field_map_blind_refs_only AS
+    SELECT  r.*
+      FROM  authority.control_set_auth_field_metabib_field_map_refs_only r
+            JOIN authority.control_set_authority_field a ON (r.authority_field = a.id)
+      WHERE linking_subfield IS NULL;
+COMMENT ON VIEW authority.control_set_auth_field_metabib_field_map_blind_refs_only IS $$metabib fields for NON-main entry auth fields that can't be linked to other records$$; -- '
+
+CREATE VIEW authority.control_set_auth_field_metabib_field_map_blind_refs AS
+    SELECT  r.*
+      FROM  authority.control_set_auth_field_metabib_field_map_refs r
+            JOIN authority.control_set_authority_field a ON (r.authority_field = a.id)
+      WHERE linking_subfield IS NULL;
+COMMENT ON VIEW authority.control_set_auth_field_metabib_field_map_blind_refs IS $$metabib fields for all auth fields that can't be linked to other records$$; -- '
+
+CREATE VIEW authority.control_set_auth_field_metabib_field_map_blind_main AS
+    SELECT  r.*
+      FROM  authority.control_set_auth_field_metabib_field_map_main r
+            JOIN authority.control_set_authority_field a ON (r.authority_field = a.id)
+      WHERE linking_subfield IS NULL;
+COMMENT ON VIEW authority.control_set_auth_field_metabib_field_map_blind_main IS $$metabib fields for main entry auth fields that can't be linked to other records$$; -- '
+
+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;
+    sf_node         TEXT;
+    tag_node        TEXT;
+    thes_code       TEXT;
+    cset            INT;
+    heading_text    TEXT;
+    tmp_text        TEXT;
+    first_sf        BOOL;
+    auth_id         INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::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;
+
+    thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj');
+    IF thes_code IS NULL THEN
+        thes_code := '|';
+    ELSIF 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 tag_node IN SELECT unnest(oils_xpath('//*[@tag="'||tag_used||'"]',marcxml)) LOOP
+            FOR sf_node IN SELECT unnest(oils_xpath('./*[contains("'||acsaf.sf_list||'",@code)]',tag_node)) LOOP
+
+                tmp_text := oils_xpath_string('.', sf_node);
+                sf := oils_xpath_string('./@code', sf_node);
+
+                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('./@ind'||nfi_used, tag_node),
+                                    $$\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;
+
+        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 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;
+    joiner_text    TEXT;
+    sort_text       TEXT;
+    tmp_text        TEXT;
+    tmp_xml         TEXT;
+    first_sf        BOOL;
+    auth_id         INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::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;
+
+    res.record := auth_id;
+
+    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;
+        joiner_text := COALESCE(acsaf.joiner, ' ');
+
+        FOR tmp_xml IN SELECT UNNEST(XPATH('//*[@tag="'||tag_used||'"]', marcxml::XML)) LOOP
+
+            heading_text := COALESCE(
+                oils_xpath_string('./*[contains("'||acsaf.sf_list||'",@code)]', tmp_xml::TEXT, joiner_text),
+                ''
+            );
+
+            IF nfi_used IS NOT NULL THEN
+
+                sort_text := SUBSTRING(
+                    heading_text FROM
+                    COALESCE(
+                        NULLIF(
+                            REGEXP_REPLACE(
+                                oils_xpath_string('./@ind'||nfi_used, tmp_xml::TEXT),
+                                $$\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 := public.naco_normalize(sort_text);
+                res.index_vector = to_tsvector('keyword'::regconfig, res.sort_value);
+                RETURN NEXT res;
+            END IF;
+
+        END LOOP;
+
+    END LOOP;
+
+    RETURN;
+END;
+$func$ LANGUAGE PLPGSQL IMMUTABLE;
+
+CREATE TABLE metabib.browse_entry_simple_heading_map (
+    id BIGSERIAL PRIMARY KEY,
+    entry BIGINT REFERENCES metabib.browse_entry (id),
+    simple_heading BIGINT REFERENCES authority.simple_heading (id) ON DELETE CASCADE
+);
+CREATE INDEX browse_entry_sh_map_entry_idx ON metabib.browse_entry_simple_heading_map (entry);
+CREATE INDEX browse_entry_sh_map_sh_idx ON metabib.browse_entry_simple_heading_map (simple_heading);
+
+CREATE OR REPLACE FUNCTION biblio.extract_metabib_field_entry ( rid BIGINT, default_joiner TEXT ) RETURNS SETOF metabib.field_entry_template AS $func$
+DECLARE
+    bib     biblio.record_entry%ROWTYPE;
+    idx     config.metabib_field%ROWTYPE;
+    xfrm        config.xml_transform%ROWTYPE;
+    prev_xfrm   TEXT;
+    transformed_xml TEXT;
+    xml_node    TEXT;
+    xml_node_list   TEXT[];
+    facet_text  TEXT;
+    browse_text TEXT;
+    sort_value  TEXT;
+    raw_text    TEXT;
+    curr_text   TEXT;
+    joiner      TEXT := default_joiner; -- XXX will index defs supply a joiner?
+    authority_text TEXT;
+    authority_link BIGINT;
+    output_row  metabib.field_entry_template%ROWTYPE;
+BEGIN
+
+    -- Start out with no field-use bools set
+    output_row.browse_field = FALSE;
+    output_row.facet_field = FALSE;
+    output_row.search_field = FALSE;
+
+    -- Get the record
+    SELECT INTO bib * FROM biblio.record_entry WHERE id = rid;
+
+    -- Loop over the indexing entries
+    FOR idx IN SELECT * FROM config.metabib_field ORDER BY format LOOP
+
+        joiner := COALESCE(idx.joiner, default_joiner);
+
+        SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
+
+        -- See if we can skip the XSLT ... it's expensive
+        IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
+            -- Can't skip the transform
+            IF xfrm.xslt <> '---' THEN
+                transformed_xml := oils_xslt_process(bib.marc,xfrm.xslt);
+            ELSE
+                transformed_xml := bib.marc;
+            END IF;
+
+            prev_xfrm := xfrm.name;
+        END IF;
+
+        xml_node_list := oils_xpath( idx.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
+
+        raw_text := NULL;
+        FOR xml_node IN SELECT x FROM unnest(xml_node_list) AS x LOOP
+            CONTINUE WHEN xml_node !~ E'^\\s*<';
+
+            -- XXX much of this should be moved into oils_xpath_string...
+            curr_text := ARRAY_TO_STRING(evergreen.array_remove_item_by_value(evergreen.array_remove_item_by_value(
+                oils_xpath( '//text()',
+                    REGEXP_REPLACE(
+                        REGEXP_REPLACE( -- This escapes all &s not followed by "amp;".  Data ise returned from oils_xpath (above) in UTF-8, not entity encoded
+                            REGEXP_REPLACE( -- This escapes embeded <s
+                                xml_node,
+                                $re$(>[^<]+)(<)([^>]+<)$re$,
+                                E'\\1&lt;\\3',
+                                'g'
+                            ),
+                            '&(?!amp;)',
+                            '&amp;',
+                            'g'
+                        ),
+                        E'\\s+',
+                        ' ',
+                        'g'
+                    )
+                ), ' '), ''),
+                joiner
+            );
+
+            CONTINUE WHEN curr_text IS NULL OR curr_text = '';
+
+            IF raw_text IS NOT NULL THEN
+                raw_text := raw_text || joiner;
+            END IF;
+
+            raw_text := COALESCE(raw_text,'') || curr_text;
+
+            -- autosuggest/metabib.browse_entry
+            IF idx.browse_field THEN
+
+                IF idx.browse_xpath IS NOT NULL AND idx.browse_xpath <> '' THEN
+                    browse_text := oils_xpath_string( idx.browse_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
+                ELSE
+                    browse_text := curr_text;
+                END IF;
+
+                IF idx.browse_sort_xpath IS NOT NULL AND
+                    idx.browse_sort_xpath <> '' THEN
+
+                    sort_value := oils_xpath_string(
+                        idx.browse_sort_xpath, xml_node, joiner,
+                        ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
+                    );
+                ELSE
+                    sort_value := browse_text;
+                END IF;
+
+                output_row.field_class = idx.field_class;
+                output_row.field = idx.id;
+                output_row.source = rid;
+                output_row.value = BTRIM(REGEXP_REPLACE(browse_text, E'\\s+', ' ', 'g'));
+                output_row.sort_value :=
+                    public.naco_normalize(sort_value);
+
+                output_row.authority := NULL;
+
+                IF idx.authority_xpath IS NOT NULL AND idx.authority_xpath <> '' THEN
+                    authority_text := oils_xpath_string(
+                        idx.authority_xpath, xml_node, joiner,
+                        ARRAY[
+                            ARRAY[xfrm.prefix, xfrm.namespace_uri],
+                            ARRAY['xlink','http://www.w3.org/1999/xlink']
+                        ]
+                    );
+
+                    IF authority_text ~ '^\d+$' THEN
+                        authority_link := authority_text::BIGINT;
+                        PERFORM * FROM authority.record_entry WHERE id = authority_link;
+                        IF FOUND THEN
+                            output_row.authority := authority_link;
+                        END IF;
+                    END IF;
+
+                END IF;
+
+                output_row.browse_field = TRUE;
+                RETURN NEXT output_row;
+                output_row.browse_field = FALSE;
+                output_row.sort_value := NULL;
+            END IF;
+
+            -- insert raw node text for faceting
+            IF idx.facet_field THEN
+
+                IF idx.facet_xpath IS NOT NULL AND idx.facet_xpath <> '' THEN
+                    facet_text := oils_xpath_string( idx.facet_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] );
+                ELSE
+                    facet_text := curr_text;
+                END IF;
+
+                output_row.field_class = idx.field_class;
+                output_row.field = -1 * idx.id;
+                output_row.source = rid;
+                output_row.value = BTRIM(REGEXP_REPLACE(facet_text, E'\\s+', ' ', 'g'));
+
+                output_row.facet_field = TRUE;
+                RETURN NEXT output_row;
+                output_row.facet_field = FALSE;
+            END IF;
+
+        END LOOP;
+
+        CONTINUE WHEN raw_text IS NULL OR raw_text = '';
+
+        -- insert combined node text for searching
+        IF idx.search_field THEN
+            output_row.field_class = idx.field_class;
+            output_row.field = idx.id;
+            output_row.source = rid;
+            output_row.value = BTRIM(REGEXP_REPLACE(raw_text, E'\\s+', ' ', 'g'));
+
+            output_row.search_field = TRUE;
+            RETURN NEXT output_row;
+            output_row.search_field = FALSE;
+        END IF;
+
+    END LOOP;
+
+END;
+
+$func$ LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE
+    FUNCTION metabib.autosuggest_prepare_tsquery(orig TEXT) RETURNS TEXT[] AS
+$$
+DECLARE
+    orig_ended_in_space     BOOLEAN;
+    result                  RECORD;
+    plain                   TEXT;
+    normalized              TEXT;
+BEGIN
+    orig_ended_in_space := orig ~ E'\\s$';
+
+    orig := ARRAY_TO_STRING(
+        evergreen.regexp_split_to_array(orig, E'\\W+'), ' '
+    );
+
+    normalized := public.naco_normalize(orig); -- also trim()s
+    plain := trim(orig);
+
+    IF NOT orig_ended_in_space THEN
+        plain := plain || ':*';
+        normalized := normalized || ':*';
+    END IF;
+
+    plain := ARRAY_TO_STRING(
+        evergreen.regexp_split_to_array(plain, E'\\s+'), ' & '
+    );
+    normalized := ARRAY_TO_STRING(
+        evergreen.regexp_split_to_array(normalized, E'\\s+'), ' & '
+    );
+
+    RETURN ARRAY[normalized, plain];
+END;
+$$ LANGUAGE PLPGSQL;
+
+ALTER TYPE metabib.flat_browse_entry_appearance ADD ATTRIBUTE sees TEXT;
+ALTER TYPE metabib.flat_browse_entry_appearance ADD ATTRIBUTE asources INT;
+ALTER TYPE metabib.flat_browse_entry_appearance ADD ATTRIBUTE aaccurate TEXT;
+
+CREATE OR REPLACE FUNCTION metabib.browse_bib_pivot(
+    INT[],
+    TEXT
+) RETURNS BIGINT AS $p$
+    SELECT  mbe.id
+      FROM  metabib.browse_entry mbe
+            JOIN metabib.browse_entry_def_map mbedm ON (
+                mbedm.entry = mbe.id
+                AND mbedm.def = ANY($1)
+            )
+      WHERE mbe.sort_value >= public.naco_normalize($2)
+      ORDER BY mbe.sort_value, mbe.value LIMIT 1;
+$p$ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION metabib.browse_authority_pivot(
+    INT[],
+    TEXT
+) RETURNS BIGINT AS $p$
+    SELECT  mbe.id
+      FROM  metabib.browse_entry mbe
+            JOIN metabib.browse_entry_simple_heading_map mbeshm ON ( mbeshm.entry = mbe.id )
+            JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
+            JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
+                ash.atag = map.authority_field
+                AND map.metabib_field = ANY($1)
+            )
+      WHERE mbe.sort_value >= public.naco_normalize($2)
+      ORDER BY mbe.sort_value, mbe.value LIMIT 1;
+$p$ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION metabib.browse_authority_refs_pivot(
+    INT[],
+    TEXT
+) RETURNS BIGINT AS $p$
+    SELECT  mbe.id
+      FROM  metabib.browse_entry mbe
+            JOIN metabib.browse_entry_simple_heading_map mbeshm ON ( mbeshm.entry = mbe.id )
+            JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
+            JOIN authority.control_set_auth_field_metabib_field_map_refs_only map ON (
+                ash.atag = map.authority_field
+                AND map.metabib_field = ANY($1)
+            )
+      WHERE mbe.sort_value >= public.naco_normalize($2)
+      ORDER BY mbe.sort_value, mbe.value LIMIT 1;
+$p$ LANGUAGE SQL;
+
+-- The drop is necessary because the language change from PLPGSQL to SQL
+-- carries with it name changes to the parameters
+DROP FUNCTION metabib.browse_pivot(INT[], TEXT);
+CREATE FUNCTION metabib.browse_pivot(
+    INT[],
+    TEXT
+) RETURNS BIGINT AS $p$
+    SELECT  id FROM metabib.browse_entry
+      WHERE id IN (
+                metabib.browse_bib_pivot($1, $2),
+                metabib.browse_authority_refs_pivot($1,$2) -- only look in 4xx, 5xx, 7xx of authority
+            )
+      ORDER BY sort_value, value LIMIT 1;
+$p$ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION metabib.staged_browse(
+    query                   TEXT,
+    fields                  INT[],
+    context_org             INT,
+    context_locations       INT[],
+    staff                   BOOL,
+    browse_superpage_size   INT,
+    count_up_from_zero      BOOL,   -- if false, count down from -1
+    result_limit            INT,
+    next_pivot_pos          INT
+) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$
+DECLARE
+    curs                    REFCURSOR;
+    rec                     RECORD;
+    qpfts_query             TEXT;
+    aqpfts_query            TEXT;
+    afields                 INT[];
+    bfields                 INT[];
+    result_row              metabib.flat_browse_entry_appearance%ROWTYPE;
+    results_skipped         INT := 0;
+    row_counter             INT := 0;
+    row_number              INT;
+    slice_start             INT;
+    slice_end               INT;
+    full_end                INT;
+    all_records             BIGINT[];
+    all_brecords             BIGINT[];
+    all_arecords            BIGINT[];
+    superpage_of_records    BIGINT[];
+    superpage_size          INT;
+BEGIN
+    IF count_up_from_zero THEN
+        row_number := 0;
+    ELSE
+        row_number := -1;
+    END IF;
+
+    OPEN curs FOR EXECUTE query;
+
+    LOOP
+        FETCH curs INTO rec;
+        IF NOT FOUND THEN
+            IF result_row.pivot_point IS NOT NULL THEN
+                RETURN NEXT result_row;
+            END IF;
+            RETURN;
+        END IF;
+
+
+        -- Gather aggregate data based on the MBE row we're looking at now, authority axis
+        SELECT INTO all_arecords, result_row.sees, afields
+                ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
+                ARRAY_TO_STRING(ARRAY_AGG(DISTINCT aal.source), $$,$$), -- authority record ids
+                ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
+
+          FROM  metabib.browse_entry_simple_heading_map mbeshm
+                JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
+                JOIN authority.authority_linking aal ON ( ash.record = aal.source )
+                JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
+                JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
+                    ash.atag = map.authority_field
+                    AND map.metabib_field = ANY(fields)
+                )
+          WHERE mbeshm.entry = rec.id;
+
+
+        -- Gather aggregate data based on the MBE row we're looking at now, bib axis
+        SELECT INTO all_brecords, result_row.authorities, bfields
+                ARRAY_AGG(DISTINCT source),
+                ARRAY_TO_STRING(ARRAY_AGG(DISTINCT authority), $$,$$),
+                ARRAY_AGG(DISTINCT def)
+          FROM  metabib.browse_entry_def_map
+          WHERE entry = rec.id
+                AND def = ANY(fields);
+
+        SELECT INTO result_row.fields ARRAY_TO_STRING(ARRAY_AGG(DISTINCT x), $$,$$) FROM UNNEST(afields || bfields) x;
+
+        result_row.sources := 0;
+        result_row.asources := 0;
+
+        -- Bib-linked vis checking
+        IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
+
+            full_end := ARRAY_LENGTH(all_brecords, 1);
+            superpage_size := COALESCE(browse_superpage_size, full_end);
+            slice_start := 1;
+            slice_end := superpage_size;
+
+            WHILE result_row.sources = 0 AND slice_start <= full_end LOOP
+                superpage_of_records := all_brecords[slice_start:slice_end];
+                qpfts_query :=
+                    'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, ' ||
+                    '1::INT AS rel FROM (SELECT UNNEST(' ||
+                    quote_literal(superpage_of_records) || '::BIGINT[]) AS r) rr';
+
+                -- We use search.query_parser_fts() for visibility testing.
+                -- We're calling it once per browse-superpage worth of records
+                -- out of the set of records related to a given mbe, until we've
+                -- either exhausted that set of records or found at least 1
+                -- visible record.
+
+                SELECT INTO result_row.sources visible
+                    FROM search.query_parser_fts(
+                        context_org, NULL, qpfts_query, NULL,
+                        context_locations, 0, NULL, NULL, FALSE, staff, FALSE
+                    ) qpfts
+                    WHERE qpfts.rel IS NULL;
+
+                slice_start := slice_start + superpage_size;
+                slice_end := slice_end + superpage_size;
+            END LOOP;
+
+            -- Accurate?  Well, probably.
+            result_row.accurate := browse_superpage_size IS NULL OR
+                browse_superpage_size >= full_end;
+
+        END IF;
+
+        -- Authority-linked vis checking
+        IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
+
+            full_end := ARRAY_LENGTH(all_arecords, 1);
+            superpage_size := COALESCE(browse_superpage_size, full_end);
+            slice_start := 1;
+            slice_end := superpage_size;
+
+            WHILE result_row.asources = 0 AND slice_start <= full_end LOOP
+                superpage_of_records := all_arecords[slice_start:slice_end];
+                qpfts_query :=
+                    'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, ' ||
+                    '1::INT AS rel FROM (SELECT UNNEST(' ||
+                    quote_literal(superpage_of_records) || '::BIGINT[]) AS r) rr';
+
+                -- We use search.query_parser_fts() for visibility testing.
+                -- We're calling it once per browse-superpage worth of records
+                -- out of the set of records related to a given mbe, via
+                -- authority until we've either exhausted that set of records
+                -- or found at least 1 visible record.
+
+                SELECT INTO result_row.asources visible
+                    FROM search.query_parser_fts(
+                        context_org, NULL, qpfts_query, NULL,
+                        context_locations, 0, NULL, NULL, FALSE, staff, FALSE
+                    ) qpfts
+                    WHERE qpfts.rel IS NULL;
+
+                slice_start := slice_start + superpage_size;
+                slice_end := slice_end + superpage_size;
+            END LOOP;
+
+
+            -- Accurate?  Well, probably.
+            result_row.aaccurate := browse_superpage_size IS NULL OR
+                browse_superpage_size >= full_end;
+
+        END IF;
+
+        IF result_row.sources > 0 OR result_row.asources > 0 THEN
+
+            -- The function that calls this function needs row_number in order
+            -- to correctly order results from two different runs of this
+            -- functions.
+            result_row.row_number := row_number;
+
+            -- Now, if row_counter is still less than limit, return a row.  If
+            -- not, but it is less than next_pivot_pos, continue on without
+            -- returning actual result rows until we find
+            -- that next pivot, and return it.
+
+            IF row_counter < result_limit THEN
+                result_row.browse_entry := rec.id;
+                result_row.value := rec.value;
+
+                RETURN NEXT result_row;
+            ELSE
+                result_row.browse_entry := NULL;
+                result_row.authorities := NULL;
+                result_row.fields := NULL;
+                result_row.value := NULL;
+                result_row.sources := NULL;
+                result_row.sees := NULL;
+                result_row.accurate := NULL;
+                result_row.aaccurate := NULL;
+                result_row.pivot_point := rec.id;
+
+                IF row_counter >= next_pivot_pos THEN
+                    RETURN NEXT result_row;
+                    RETURN;
+                END IF;
+            END IF;
+
+            IF count_up_from_zero THEN
+                row_number := row_number + 1;
+            ELSE
+                row_number := row_number - 1;
+            END IF;
+
+            -- row_counter is different from row_number.
+            -- It simply counts up from zero so that we know when
+            -- we've reached our limit.
+            row_counter := row_counter + 1;
+        END IF;
+    END LOOP;
+END;
+$p$ LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION metabib.browse(
+    search_field            INT[],
+    browse_term             TEXT,
+    context_org             INT DEFAULT NULL,
+    context_loc_group       INT DEFAULT NULL,
+    staff                   BOOL DEFAULT FALSE,
+    pivot_id                BIGINT DEFAULT NULL,
+    result_limit            INT DEFAULT 10
+) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$
+DECLARE
+    core_query              TEXT;
+    back_query              TEXT;
+    forward_query           TEXT;
+    pivot_sort_value        TEXT;
+    pivot_sort_fallback     TEXT;
+    context_locations       INT[];
+    browse_superpage_size   INT;
+    results_skipped         INT := 0;
+    back_limit              INT;
+    back_to_pivot           INT;
+    forward_limit           INT;
+    forward_to_pivot        INT;
+BEGIN
+    -- First, find the pivot if we were given a browse term but not a pivot.
+    IF pivot_id IS NULL THEN
+        pivot_id := metabib.browse_pivot(search_field, browse_term);
+    END IF;
+
+    SELECT INTO pivot_sort_value, pivot_sort_fallback
+        sort_value, value FROM metabib.browse_entry WHERE id = pivot_id;
+
+    -- Bail if we couldn't find a pivot.
+    IF pivot_sort_value IS NULL THEN
+        RETURN;
+    END IF;
+
+    -- Transform the context_loc_group argument (if any) (logc at the
+    -- TPAC layer) into a form we'll be able to use.
+    IF context_loc_group IS NOT NULL THEN
+        SELECT INTO context_locations ARRAY_AGG(location)
+            FROM asset.copy_location_group_map
+            WHERE lgroup = context_loc_group;
+    END IF;
+
+    -- Get the configured size of browse superpages.
+    SELECT INTO browse_superpage_size value     -- NULL ok
+        FROM config.global_flag
+        WHERE enabled AND name = 'opac.browse.holdings_visibility_test_limit';
+
+    -- First we're going to search backward from the pivot, then we're going
+    -- to search forward.  In each direction, we need two limits.  At the
+    -- lesser of the two limits, we delineate the edge of the result set
+    -- we're going to return.  At the greater of the two limits, we find the
+    -- pivot value that would represent an offset from the current pivot
+    -- at a distance of one "page" in either direction, where a "page" is a
+    -- result set of the size specified in the "result_limit" argument.
+    --
+    -- The two limits in each direction make four derived values in total,
+    -- and we calculate them now.
+    back_limit := CEIL(result_limit::FLOAT / 2);
+    back_to_pivot := result_limit;
+    forward_limit := result_limit / 2;
+    forward_to_pivot := result_limit - 1;
+
+    -- This is the meat of the SQL query that finds browse entries.  We'll
+    -- pass this to a function which uses it with a cursor, so that individual
+    -- rows may be fetched in a loop until some condition is satisfied, without
+    -- waiting for a result set of fixed size to be collected all at once.
+    core_query := '
+SELECT  mbe.id,
+        mbe.value,
+        mbe.sort_value
+  FROM  metabib.browse_entry mbe
+  WHERE (
+            EXISTS ( -- are there any bibs using this mbe via the requested fields?
+                SELECT  1
+                  FROM  metabib.browse_entry_def_map mbedm
+                  WHERE mbedm.entry = mbe.id AND mbedm.def = ANY(' || quote_literal(search_field) || ')
+                  LIMIT 1
+            ) OR EXISTS ( -- are there any authorities using this mbe via the requested fields?
+                SELECT  1
+                  FROM  metabib.browse_entry_simple_heading_map mbeshm
+                        JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
+                        JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
+                            ash.atag = map.authority_field
+                            AND map.metabib_field = ANY(' || quote_literal(search_field) || ')
+                        )
+                  WHERE mbeshm.entry = mbe.id
+            )
+        ) AND ';
+
+    -- This is the variant of the query for browsing backward.
+    back_query := core_query ||
+        ' mbe.sort_value <= ' || quote_literal(pivot_sort_value) ||
+    ' ORDER BY mbe.sort_value DESC, mbe.value DESC ';
+
+    -- This variant browses forward.
+    forward_query := core_query ||
+        ' mbe.sort_value > ' || quote_literal(pivot_sort_value) ||
+    ' ORDER BY mbe.sort_value, mbe.value ';
+
+    -- We now call the function which applies a cursor to the provided
+    -- queries, stopping at the appropriate limits and also giving us
+    -- the next page's pivot.
+    RETURN QUERY
+        SELECT * FROM metabib.staged_browse(
+            back_query, search_field, context_org, context_locations,
+            staff, browse_superpage_size, TRUE, back_limit, back_to_pivot
+        ) UNION
+        SELECT * FROM metabib.staged_browse(
+            forward_query, search_field, context_org, context_locations,
+            staff, browse_superpage_size, FALSE, forward_limit, forward_to_pivot
+        ) ORDER BY row_number DESC;
+
+END;
+$p$ LANGUAGE PLPGSQL;
+
+-- No 4XX inter-authority linking
+UPDATE authority.control_set_authority_field SET linking_subfield = NULL;
+UPDATE authority.control_set_authority_field SET linking_subfield = '0' WHERE tag LIKE ANY (ARRAY['5%','7%']);
+
+-- Map between authority controlled bib fields and stock indexing metabib fields
+INSERT INTO authority.control_set_bib_field_metabib_field_map (bib_field, metabib_field)
+    SELECT  DISTINCT b.id AS bib_field, m.id AS metabib_field
+      FROM  authority.control_set_bib_field b JOIN authority.control_set_authority_field a ON (b.authority_field = a.id), config.metabib_field m
+      WHERE a.tag = '100' AND m.name = 'personal'
+
+        UNION
+
+    SELECT  DISTINCT b.id AS bib_field, m.id AS metabib_field
+      FROM  authority.control_set_bib_field b JOIN authority.control_set_authority_field a ON (b.authority_field = a.id), config.metabib_field m
+      WHERE a.tag = '110' AND m.name = 'corporate'
+
+        UNION
+
+    SELECT  DISTINCT b.id AS bib_field, m.id AS metabib_field
+      FROM  authority.control_set_bib_field b JOIN authority.control_set_authority_field a ON (b.authority_field = a.id), config.metabib_field m
+      WHERE a.tag = '111' AND m.name = 'conference'
+
+        UNION
+
+    SELECT  DISTINCT b.id AS bib_field, m.id AS metabib_field
+      FROM  authority.control_set_bib_field b JOIN authority.control_set_authority_field a ON (b.authority_field = a.id), config.metabib_field m
+      WHERE a.tag = '130' AND m.name = 'uniform'
+
+        UNION
+
+    SELECT  DISTINCT b.id AS bib_field, m.id AS metabib_field
+      FROM  authority.control_set_bib_field b JOIN authority.control_set_authority_field a ON (b.authority_field = a.id), config.metabib_field m
+      WHERE a.tag = '148' AND m.name = 'temporal'
+
+        UNION
+
+    SELECT  DISTINCT b.id AS bib_field, m.id AS metabib_field
+      FROM  authority.control_set_bib_field b JOIN authority.control_set_authority_field a ON (b.authority_field = a.id), config.metabib_field m
+      WHERE a.tag = '150' AND m.name = 'topic'
+
+        UNION
+
+    SELECT  DISTINCT b.id AS bib_field, m.id AS metabib_field
+      FROM  authority.control_set_bib_field b JOIN authority.control_set_authority_field a ON (b.authority_field = a.id), config.metabib_field m
+      WHERE a.tag = '151' AND m.name = 'geographic'
+
+        UNION
+
+    SELECT  DISTINCT b.id AS bib_field, m.id AS metabib_field
+      FROM  authority.control_set_bib_field b JOIN authority.control_set_authority_field a ON (b.authority_field = a.id), config.metabib_field m
+      WHERE a.tag = '155' AND m.name = 'genre' -- Just in case...
+;
+
+CREATE OR REPLACE FUNCTION authority.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
+DECLARE
+    ashs    authority.simple_heading%ROWTYPE;
+    mbe_row metabib.browse_entry%ROWTYPE;
+    mbe_id  BIGINT;
+    ash_id  BIGINT;
+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?
+
+        -- XXX What do we about the actual linking subfields present in
+        -- authority records that target this one when this happens?
+        DELETE FROM authority.authority_linking
+            WHERE source = NEW.id OR target = NEW.id;
+
+        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;
+        DELETE FROM authority.authority_linking WHERE source = NEW.id;
+    END IF;
+
+    INSERT INTO authority.authority_linking (source, target, field)
+        SELECT source, target, field FROM authority.calculate_authority_linking(
+            NEW.id, NEW.control_set, NEW.marc::XML
+        );
+
+    FOR ashs IN SELECT * FROM authority.simple_heading_set(NEW.marc) LOOP
+
+        INSERT INTO authority.simple_heading (record,atag,value,sort_value)
+            VALUES (ashs.record, ashs.atag, ashs.value, ashs.sort_value);
+        ash_id := CURRVAL('authority.simple_heading_id_seq'::REGCLASS);
+
+        SELECT INTO mbe_row * FROM metabib.browse_entry
+            WHERE value = ashs.value AND sort_value = ashs.sort_value;
+
+        IF FOUND THEN
+            mbe_id := mbe_row.id;
+        ELSE
+            INSERT INTO metabib.browse_entry
+                ( value, sort_value ) VALUES
+                ( ashs.value, ashs.sort_value );
+
+            mbe_id := CURRVAL('metabib.browse_entry_id_seq'::REGCLASS);
+        END IF;
+
+        INSERT INTO metabib.browse_entry_simple_heading_map (entry,simple_heading) VALUES (mbe_id,ash_id);
+
+    END LOOP;
+
+    -- 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;
+
+COMMIT;
+
index 415e41c..9e48c92 100644 (file)
                     [% FOR result IN ctx.browse_results %]
                         <li class="browse-result">
                             <span class="browse-result-value[% result.row_number == 0 && !CGI.param('bpivot') ? ' browse-result-best-match' : '' %]">
-                                <a href="[% mkurl(
-                                    ctx.opac_root _ '/results', {
-                                        'fi:has_browse_entry' => (result.browse_entry _ ',' _ result.fields)
-                                    }) %]">[% result.value | html %]</a>
+                                [% IF result.sources > 0 %] <!-- bib-linked browse value -->
+                                    <a href="[% mkurl(
+                                        ctx.opac_root _ '/results', {
+                                            'fi:has_browse_entry' => (result.browse_entry _ ',' _ result.fields)
+                                        }) %]">[% result.value | html %]</a>
+                                    <span class="browse-result-sources">([%
+                                        IF result.accurate == 'f';
+                                            l("At least"); " ";
+                                        END;
+                                    result.sources %])</span>
+                                [% ELSE %] <!-- only authority links -->
+                                    [% result.value | html %]
+                                [% END %]
                             </span>
-                            <span class="browse-result-sources">([%
-                                IF result.accurate == 'f';
-                                    l("At least"); " ";
-                                END;
-                                result.sources %])</span>
-                            [% IF result.authorities.size %]
+
                             <ul class="browse-result-authority-headings">
-                                [% FOR a IN result.authorities;
+                                [% seenit = {}; # for headings we've rendered
+                                   FOR a IN result.sees;
                                     PROCESS authority_notes authority=a;
 
                                     # Other than displaying public general
                                             # that h.target_count is only
                                             # defined when h.target is.
 
-                                            IF h.target AND h.target_count %]
-                                            <li><span class="browse-result-authority-field-name">[% field.name %]</span>
-                                            <a href="[% mkurl(ctx.opac_root _ '/results', {query => 'identifier|authority_id[' _ h.target _ ']'}) %]">[% h.heading | html %]</a>
-                                            <span class="browse-result-authority-bib-links">([% h.target_count %])</span>
-                                            </li>
+                                            IF h.target AND h.target_count AND result.list_authorities.grep('^' _ h.target _ '$').size == 0;
+                                                id = h.target; NEXT IF seenit.$id; seenit.$id = 1; %]
+                                                <li><span class="browse-result-authority-field-name">[% field.name %]</span>
+                                                <a href="[% mkurl(ctx.opac_root _ '/browse', {bterm => h.heading}, ['bpivot']) %]">[% h.heading | html %]</a>
+                                                <span class="browse-result-authority-bib-links">([% h.target_count %])</span>
+                                                </li>
+                                            [% END %]
+                                        [% END %]
+                                    [% END %]
+                                [% END %]
+                                [% FOR a IN result.authorities; # I don't think we'll ever get here...
+                                    PROCESS authority_notes authority=a IF !sees.grep(a.id);
+
+                                    # Other than displaying public general
+                                    # notes, we can go no further sans
+                                    # control_set.
+                                    NEXT UNLESS a.control_set;
+
+                                    # get_authority_fields is fast and cache-y.
+                                    acs = ctx.get_authority_fields(a.control_set);
+                                    FOR field_group IN a.headings;
+                                        field_id = field_group.keys.0;
+                                        field = acs.$field_id;
+                                        headings = field_group.values.0;
+                                        FOR h IN headings;
+                                            # We could display headings without
+                                            # links here when h.target is
+                                            # undef, if we wanted to, but note
+                                            # that h.target_count is only
+                                            # defined when h.target is.
+
+                                            IF h.target AND h.target_count AND result.list_sees.grep('^' _ h.target _ '$').size == 0 AND !h.main_entry;
+                                                id = h.target; NEXT IF seenit.$id; seenit.$id = 1; %]
+                                                <li><span class="browse-result-authority-field-name">[% field.name %]</span>
+                                                <a href="[% mkurl(ctx.opac_root _ '/results', {query => 'identifier|authority_id[' _ h.target _ ']'}) %]">[% h.heading | html %]</a>
+                                                <span class="browse-result-authority-bib-links">([% h.target_count %])</span>
+                                                </li>
                                             [% END %]
                                         [% END %]
                                     [% END %]
                                 [% END %]
                             </ul>
-                            [% END %]
                         </li>
                     [% END %]
                     </ul>