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

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