Open-ILS/src/sql/Pg/upgrade/XXXX.schema.metabib_staged_browse.sql

   1 CREATE OR REPLACE FUNCTION metabib.staged_browse(
   2     query                   TEXT,
   3     fields                  INT[],
   4     context_org             INT,
   5     context_locations       INT[],
   6     staff                   BOOL,
   7     browse_superpage_size   INT,
   8     count_up_from_zero      BOOL,   -- if false, count down from -1
   9     result_limit            INT,
  10     next_pivot_pos          INT
  11 ) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$
  12 DECLARE
  13     curs                    REFCURSOR;
  14     rec                     RECORD;
  15     qpfts_query             TEXT;
  16     aqpfts_query            TEXT;
  17     afields                 INT[];
  18     bfields                 INT[];
  19     result_row              metabib.flat_browse_entry_appearance%ROWTYPE;
  20     results_skipped         INT := 0;
  21     row_counter             INT := 0;
  22     row_number              INT;
  23     slice_start             INT;
  24     slice_end               INT;
  25     full_end                INT;
  26     all_records             BIGINT[];
  27     all_brecords             BIGINT[];
  28     all_arecords            BIGINT[];
  29     superpage_of_records    BIGINT[];
  30     superpage_size          INT;
  31 BEGIN
  32     IF count_up_from_zero THEN
  33         row_number := 0;
  34     ELSE
  35         row_number := -1;
  36     END IF;
  37
  38     OPEN curs FOR EXECUTE query;
  39
  40     LOOP
  41         FETCH curs INTO rec;
  42         IF NOT FOUND THEN
  43             IF result_row.pivot_point IS NOT NULL THEN
  44                 RETURN NEXT result_row;
  45             END IF;
  46             RETURN;
  47         END IF;
  48
  49
  50         -- Gather aggregate data based on the MBE row we're looking at now, authority axis
  51         SELECT INTO all_arecords, result_row.sees, afields
  52                 ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
  53                 STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids
  54                 ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
  55
  56           FROM  metabib.browse_entry_simple_heading_map mbeshm
  57                 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
  58                 JOIN authority.authority_linking aal ON ( ash.record = aal.source )
  59                 JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
  60                 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
  61                     ash.atag = map.authority_field
  62                     AND map.metabib_field = ANY(fields)
  63                 )
  64           WHERE mbeshm.entry = rec.id;
  65
  66
  67         -- Gather aggregate data based on the MBE row we're looking at now, bib axis
  68         SELECT INTO all_brecords, result_row.authorities, bfields
  69                 ARRAY_AGG(DISTINCT source),
  70                 STRING_AGG(DISTINCT authority::TEXT, $$,$$),
  71                 ARRAY_AGG(DISTINCT def)
  72           FROM  metabib.browse_entry_def_map
  73           WHERE entry = rec.id
  74                 AND def = ANY(fields);
  75
  76         SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x;
  77
  78         result_row.sources := 0;
  79         result_row.asources := 0;
  80
  81         -- Bib-linked vis checking
  82         IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
  83
  84             full_end := ARRAY_LENGTH(all_brecords, 1);
  85             superpage_size := COALESCE(browse_superpage_size, full_end);
  86             slice_start := 1;
  87             slice_end := superpage_size;
  88
  89             WHILE result_row.sources = 0 AND slice_start <= full_end LOOP
  90                 superpage_of_records := all_brecords[slice_start:slice_end];
  91                 qpfts_query :=
  92                     'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, ' ||
  93                     '1::INT AS rel FROM (SELECT UNNEST(' ||
  94                     quote_literal(superpage_of_records) || '::BIGINT[]) AS r) rr';
  95
  96                 -- We use search.query_parser_fts() for visibility testing.
  97                 -- We're calling it once per browse-superpage worth of records
  98                 -- out of the set of records related to a given mbe, until we've
  99                 -- either exhausted that set of records or found at least 1
 100                 -- visible record.
 101
 102                 SELECT INTO result_row.sources visible
 103                     FROM search.query_parser_fts(
 104                         context_org, NULL, qpfts_query, NULL,
 105                         context_locations, 0, NULL, NULL, FALSE, staff, FALSE
 106                     ) qpfts
 107                     WHERE qpfts.rel IS NULL;
 108
 109                 slice_start := slice_start + superpage_size;
 110                 slice_end := slice_end + superpage_size;
 111             END LOOP;
 112
 113             -- Accurate?  Well, probably.
 114             result_row.accurate := browse_superpage_size IS NULL OR
 115                 browse_superpage_size >= full_end;
 116
 117         END IF;
 118
 119         -- Authority-linked vis checking
 120         IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
 121
 122             full_end := ARRAY_LENGTH(all_arecords, 1);
 123             superpage_size := COALESCE(browse_superpage_size, full_end);
 124             slice_start := 1;
 125             slice_end := superpage_size;
 126
 127             WHILE result_row.asources = 0 AND slice_start <= full_end LOOP
 128                 superpage_of_records := all_arecords[slice_start:slice_end];
 129                 qpfts_query :=
 130                     'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, ' ||
 131                     '1::INT AS rel FROM (SELECT UNNEST(' ||
 132                     quote_literal(superpage_of_records) || '::BIGINT[]) AS r) rr';
 133
 134                 -- We use search.query_parser_fts() for visibility testing.
 135                 -- We're calling it once per browse-superpage worth of records
 136                 -- out of the set of records related to a given mbe, via
 137                 -- authority until we've either exhausted that set of records
 138                 -- or found at least 1 visible record.
 139
 140                 SELECT INTO result_row.asources visible
 141                     FROM search.query_parser_fts(
 142                         context_org, NULL, qpfts_query, NULL,
 143                         context_locations, 0, NULL, NULL, FALSE, staff, FALSE
 144                     ) qpfts
 145                     WHERE qpfts.rel IS NULL;
 146
 147                 slice_start := slice_start + superpage_size;
 148                 slice_end := slice_end + superpage_size;
 149             END LOOP;
 150
 151
 152             -- Accurate?  Well, probably.
 153             result_row.aaccurate := browse_superpage_size IS NULL OR
 154                 browse_superpage_size >= full_end;
 155
 156         END IF;
 157
 158         IF result_row.sources > 0 OR result_row.asources > 0 THEN
 159
 160             -- The function that calls this function needs row_number in order
 161             -- to correctly order results from two different runs of this
 162             -- functions.
 163             result_row.row_number := row_number;
 164
 165             -- Now, if row_counter is still less than limit, return a row.  If
 166             -- not, but it is less than next_pivot_pos, continue on without
 167             -- returning actual result rows until we find
 168             -- that next pivot, and return it.
 169
 170             IF row_counter < result_limit THEN
 171                 result_row.browse_entry := rec.id;
 172                 result_row.value := rec.value;
 173
 174                 RETURN NEXT result_row;
 175             ELSE
 176                 result_row.browse_entry := NULL;
 177                 result_row.authorities := NULL;
 178                 result_row.fields := NULL;
 179                 result_row.value := NULL;
 180                 result_row.sources := NULL;
 181                 result_row.sees := NULL;
 182                 result_row.accurate := NULL;
 183                 result_row.aaccurate := NULL;
 184                 result_row.pivot_point := rec.id;
 185
 186                 IF row_counter >= next_pivot_pos THEN
 187                     RETURN NEXT result_row;
 188                     RETURN;
 189                 END IF;
 190             END IF;
 191
 192             IF count_up_from_zero THEN
 193                 row_number := row_number + 1;
 194             ELSE
 195                 row_number := row_number - 1;
 196             END IF;
 197
 198             -- row_counter is different from row_number.
 199             -- It simply counts up from zero so that we know when
 200             -- we've reached our limit.
 201             row_counter := row_counter + 1;
 202         END IF;
 203     END LOOP;
 204 END;
 205 $p$ LANGUAGE PLPGSQL;
 206
 207