LP#1831803: remove obsolete asset.opac_visible_copies and search.query_parser_fts...

Signed-off-by: Jeff Davis <jeff.davis@bc.libraries.coop>
Signed-off-by: Jason Boyer <JBoyer@equinoxOLI.org>
Signed-off-by: Galen Charlton <gmc@equinoxOLI.org>

diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/metabib.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/metabib.pm
index 987b906..5766ccd 100644 (file)
--- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/metabib.pm
+++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/metabib.pm
@@ -3097,7 +3097,6 @@ sub query_parser_fts {
 
     my $param_search_ou = $ou;
     my $param_depth = $depth; $param_depth = 'NULL' unless (defined($depth) and length($depth) > 0 );
-#    my $param_core_query = "\$core_query_$$\$" . $query->parse_tree->toSQL . "\$core_query_$$\$";
     my $param_core_query = $query->parse_tree->toSQL;
     my $param_statuses = '$${' . join(',', map { s/\$//go; "\"$_\""} @statuses) . '}$$';
     my $param_locations = '$${' . join(',', map { s/\$//go; "\"$_\""} @location) . '}$$';
@@ -3106,24 +3105,6 @@ sub query_parser_fts {
     my $metarecord = ($self->api_name =~ /metabib/ or $query->parse_tree->find_modifier('metabib') or $query->parse_tree->find_modifier('metarecord')) ? "'t'" : "'f'";
     my $param_pref_ou = $pref_ou || 'NULL';
 
-#    my $sth = metabib::metarecord_source_map->db_Main->prepare(<<"    SQL");
-#        SELECT  * -- bib search: $args{query}
-#          FROM  search.query_parser_fts(
-#                    $param_search_ou\:\:INT,
-#                    $param_depth\:\:INT,
-#                    $param_core_query\:\:TEXT,
-#                    $param_statuses\:\:INT[],
-#                    $param_locations\:\:INT[],
-#                    $param_offset\:\:INT,
-#                    $param_check\:\:INT,
-#                    $param_limit\:\:INT,
-#                    $metarecord\:\:BOOL,
-#                    $staff\:\:BOOL,
-#                    $deleted_search\:\:BOOL,
-#                    $param_pref_ou\:\:INT
-#                );
-#    SQL
-
     my $sth = metabib::metarecord_source_map->db_Main->prepare(<<"    SQL");
         -- bib search: $args{query}
         $param_core_query

diff --git a/Open-ILS/src/sql/Pg/040.schema.asset.sql b/Open-ILS/src/sql/Pg/040.schema.asset.sql
index 2002113..44de65b 100644 (file)
--- a/Open-ILS/src/sql/Pg/040.schema.asset.sql
+++ b/Open-ILS/src/sql/Pg/040.schema.asset.sql
@@ -128,21 +128,6 @@ CREATE TABLE asset.latest_inventory (
 );
 CREATE INDEX latest_inventory_copy_idx ON asset.latest_inventory (copy);
 
-CREATE TABLE asset.opac_visible_copies (
-  id        BIGSERIAL primary key,
-  copy_id   BIGINT, -- copy id
-  record    BIGINT,
-  circ_lib  INTEGER
-);
-COMMENT ON TABLE asset.opac_visible_copies IS $$
-Materialized view of copies that are visible in the OPAC, used by
-search.query_parser_fts() to speed up OPAC visibility checks on large
-databases.  Contents are maintained by a set of triggers.
-$$;
-CREATE INDEX opac_visible_copies_idx1 on asset.opac_visible_copies (record, circ_lib);
-CREATE INDEX opac_visible_copies_copy_id_idx on asset.opac_visible_copies (copy_id);
-CREATE UNIQUE INDEX opac_visible_copies_once_per_record_idx on asset.opac_visible_copies (copy_id, record);
-
 CREATE OR REPLACE FUNCTION asset.acp_status_changed()
 RETURNS TRIGGER AS $$
 BEGIN

diff --git a/Open-ILS/src/sql/Pg/300.schema.staged_search.sql b/Open-ILS/src/sql/Pg/300.schema.staged_search.sql
index dcdb5af..5e2bbee 100644 (file)
--- a/Open-ILS/src/sql/Pg/300.schema.staged_search.sql
+++ b/Open-ILS/src/sql/Pg/300.schema.staged_search.sql
@@ -61,382 +61,10 @@ CREATE TABLE search.relevance_adjustment (
 );
 CREATE UNIQUE INDEX bump_once_per_field_idx ON search.relevance_adjustment ( field, bump_type );
 
+-- XXX not required in 3.0+ ?
 CREATE TYPE search.search_result AS ( id BIGINT, rel NUMERIC, record INT, total INT, checked INT, visible INT, deleted INT, excluded INT, badges TEXT, popularity NUMERIC );
 CREATE TYPE search.search_args AS ( id INT, field_class TEXT, field_name TEXT, table_alias TEXT, term TEXT, term_type TEXT );
 
-CREATE OR REPLACE FUNCTION search.query_parser_fts (
-
-    param_search_ou INT,
-    param_depth     INT,
-    param_query     TEXT,
-    param_statuses  INT[],
-    param_locations INT[],
-    param_offset    INT,
-    param_check     INT,
-    param_limit     INT,
-    metarecord      BOOL,
-    staff           BOOL,
-    deleted_search  BOOL,
-    param_pref_ou   INT DEFAULT NULL
-) RETURNS SETOF search.search_result AS $func$
-DECLARE
-
-    current_res         search.search_result%ROWTYPE;
-    search_org_list     INT[];
-    luri_org_list       INT[];
-    tmp_int_list        INT[];
-
-    check_limit         INT;
-    core_limit          INT;
-    core_offset         INT;
-    tmp_int             INT;
-
-    core_result         RECORD;
-    core_cursor         REFCURSOR;
-    core_rel_query      TEXT;
-
-    total_count         INT := 0;
-    check_count         INT := 0;
-    deleted_count       INT := 0;
-    visible_count       INT := 0;
-    excluded_count      INT := 0;
-
-    luri_as_copy        BOOL;
-BEGIN
-
-    check_limit := COALESCE( param_check, 1000 );
-    core_limit  := COALESCE( param_limit, 25000 );
-    core_offset := COALESCE( param_offset, 0 );
-
-    SELECT COALESCE( enabled, FALSE ) INTO luri_as_copy FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy';
-
-    -- core_skip_chk := COALESCE( param_skip_chk, 1 );
-
-    IF param_search_ou > 0 THEN
-        IF param_depth IS NOT NULL THEN
-            SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
-        ELSE
-            SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
-        END IF;
-
-        IF luri_as_copy THEN
-            SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_full_path( param_search_ou );
-        ELSE
-            SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( param_search_ou );
-        END IF;
-
-    ELSIF param_search_ou < 0 THEN
-        SELECT ARRAY_AGG(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
-
-        FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP
-
-            IF luri_as_copy THEN
-                SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_full_path( tmp_int );
-            ELSE
-                SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int );
-            END IF;
-
-            luri_org_list := luri_org_list || tmp_int_list;
-        END LOOP;
-
-        SELECT ARRAY_AGG(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id);
-
-    ELSIF param_search_ou = 0 THEN
-        -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
-    END IF;
-
-    IF param_pref_ou IS NOT NULL THEN
-            IF luri_as_copy THEN
-                SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_full_path( param_pref_ou );
-            ELSE
-                SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( param_pref_ou );
-            END IF;
-
-        luri_org_list := luri_org_list || tmp_int_list;
-    END IF;
-
-    OPEN core_cursor FOR EXECUTE param_query;
-
-    LOOP
-
-        FETCH core_cursor INTO core_result;
-        EXIT WHEN NOT FOUND;
-        EXIT WHEN total_count >= core_limit;
-
-        total_count := total_count + 1;
-
-        CONTINUE WHEN total_count NOT BETWEEN  core_offset + 1 AND check_limit + core_offset;
-
-        check_count := check_count + 1;
-
-        IF NOT deleted_search THEN
-
-            PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
-            IF NOT FOUND THEN
-                -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
-                deleted_count := deleted_count + 1;
-                CONTINUE;
-            END IF;
-
-            PERFORM 1
-              FROM  biblio.record_entry b
-                    JOIN config.bib_source s ON (b.source = s.id)
-              WHERE s.transcendant
-                    AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
-
-            IF FOUND THEN
-                -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
-                visible_count := visible_count + 1;
-
-                current_res.id = core_result.id;
-                current_res.rel = core_result.rel;
-                current_res.badges = core_result.badges;
-                current_res.popularity = core_result.popularity;
-
-                tmp_int := 1;
-                IF metarecord THEN
-                    SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
-                END IF;
-
-                IF tmp_int = 1 THEN
-                    current_res.record = core_result.records[1];
-                ELSE
-                    current_res.record = NULL;
-                END IF;
-
-                RETURN NEXT current_res;
-
-                CONTINUE;
-            END IF;
-
-            PERFORM 1
-              FROM  asset.call_number cn
-                    JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
-                    JOIN asset.uri uri ON (map.uri = uri.id)
-              WHERE NOT cn.deleted
-                    AND cn.label = '##URI##'
-                    AND uri.active
-                    AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
-                    AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
-                    AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) )
-              LIMIT 1;
-
-            IF FOUND THEN
-                -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
-                visible_count := visible_count + 1;
-
-                current_res.id = core_result.id;
-                current_res.rel = core_result.rel;
-                current_res.badges = core_result.badges;
-                current_res.popularity = core_result.popularity;
-
-                tmp_int := 1;
-                IF metarecord THEN
-                    SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
-                END IF;
-
-                IF tmp_int = 1 THEN
-                    current_res.record = core_result.records[1];
-                ELSE
-                    current_res.record = NULL;
-                END IF;
-
-                RETURN NEXT current_res;
-
-                CONTINUE;
-            END IF;
-
-            IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
-
-                PERFORM 1
-                  FROM  asset.call_number cn
-                        JOIN asset.copy cp ON (cp.call_number = cn.id)
-                  WHERE NOT cn.deleted
-                        AND NOT cp.deleted
-                        AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
-                        AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
-                        AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
-                  LIMIT 1;
-
-                IF NOT FOUND THEN
-                    PERFORM 1
-                      FROM  biblio.peer_bib_copy_map pr
-                            JOIN asset.copy cp ON (cp.id = pr.target_copy)
-                      WHERE NOT cp.deleted
-                            AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
-                            AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
-                            AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
-                      LIMIT 1;
-
-                    IF NOT FOUND THEN
-                    -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records;
-                        excluded_count := excluded_count + 1;
-                        CONTINUE;
-                    END IF;
-                END IF;
-
-            END IF;
-
-            IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
-
-                PERFORM 1
-                  FROM  asset.call_number cn
-                        JOIN asset.copy cp ON (cp.call_number = cn.id)
-                  WHERE NOT cn.deleted
-                        AND NOT cp.deleted
-                        AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
-                        AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
-                        AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
-                  LIMIT 1;
-
-                IF NOT FOUND THEN
-                    PERFORM 1
-                      FROM  biblio.peer_bib_copy_map pr
-                            JOIN asset.copy cp ON (cp.id = pr.target_copy)
-                      WHERE NOT cp.deleted
-                            AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
-                            AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
-                            AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
-                      LIMIT 1;
-
-                    IF NOT FOUND THEN
-                        -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records;
-                        excluded_count := excluded_count + 1;
-                        CONTINUE;
-                    END IF;
-                END IF;
-
-            END IF;
-
-            IF staff IS NULL OR NOT staff THEN
-
-                PERFORM 1
-                  FROM  asset.opac_visible_copies
-                  WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
-                        AND record IN ( SELECT * FROM unnest( core_result.records ) )
-                  LIMIT 1;
-
-                IF NOT FOUND THEN
-                    PERFORM 1
-                      FROM  biblio.peer_bib_copy_map pr
-                            JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy)
-                      WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
-                            AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
-                      LIMIT 1;
-
-                    IF NOT FOUND THEN
-
-                        -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
-                        excluded_count := excluded_count + 1;
-                        CONTINUE;
-                    END IF;
-                END IF;
-
-            ELSE
-
-                PERFORM 1
-                  FROM  asset.call_number cn
-                        JOIN asset.copy cp ON (cp.call_number = cn.id)
-                  WHERE NOT cn.deleted
-                        AND NOT cp.deleted
-                        AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
-                        AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
-                  LIMIT 1;
-
-                IF NOT FOUND THEN
-
-                    PERFORM 1
-                      FROM  biblio.peer_bib_copy_map pr
-                            JOIN asset.copy cp ON (cp.id = pr.target_copy)
-                      WHERE NOT cp.deleted
-                            AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
-                            AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
-                      LIMIT 1;
-
-                    IF NOT FOUND THEN
-
-                        PERFORM 1
-                          FROM  asset.call_number cn
-                                JOIN asset.copy cp ON (cp.call_number = cn.id)
-                          WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) )
-                                AND NOT cp.deleted
-                          LIMIT 1;
-
-                        IF NOT FOUND THEN
-                            -- Recheck Located URI visibility in the case of no "foreign" copies
-                            PERFORM 1
-                              FROM  asset.call_number cn
-                                    JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
-                                    JOIN asset.uri uri ON (map.uri = uri.id)
-                              WHERE NOT cn.deleted
-                                    AND cn.label = '##URI##'
-                                    AND uri.active
-                                    AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
-                                    AND cn.owning_lib NOT IN ( SELECT * FROM unnest( luri_org_list ) )
-                              LIMIT 1;
-
-                            IF FOUND THEN
-                                -- RAISE NOTICE ' % were excluded for foreign located URIs... ', core_result.records;
-                                excluded_count := excluded_count + 1;
-                                CONTINUE;
-                            END IF;
-                        ELSE
-                            -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
-                            excluded_count := excluded_count + 1;
-                            CONTINUE;
-                        END IF;
-                    END IF;
-
-                END IF;
-
-            END IF;
-
-        END IF;
-
-        visible_count := visible_count + 1;
-
-        current_res.id = core_result.id;
-        current_res.rel = core_result.rel;
-        current_res.badges = core_result.badges;
-        current_res.popularity = core_result.popularity;
-
-        tmp_int := 1;
-        IF metarecord THEN
-            SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
-        END IF;
-
-        IF tmp_int = 1 THEN
-            current_res.record = core_result.records[1];
-        ELSE
-            current_res.record = NULL;
-        END IF;
-
-        RETURN NEXT current_res;
-
-        IF visible_count % 1000 = 0 THEN
-            -- RAISE NOTICE ' % visible so far ... ', visible_count;
-        END IF;
-
-    END LOOP;
-
-    current_res.id = NULL;
-    current_res.rel = NULL;
-    current_res.record = NULL;
-    current_res.badges = NULL;
-    current_res.popularity = NULL;
-    current_res.total = total_count;
-    current_res.checked = check_count;
-    current_res.deleted = deleted_count;
-    current_res.visible = visible_count;
-    current_res.excluded = excluded_count;
-
-    CLOSE core_cursor;
-
-    RETURN NEXT current_res;
-
-END;
-$func$ LANGUAGE PLPGSQL;
-
 CREATE OR REPLACE FUNCTION search.facets_for_record_set(ignore_facet_classes text[], hits bigint[]) RETURNS TABLE(id integer, value text, count bigint)
 AS $f$
     SELECT id, value, count

diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.staged_search_cleanup.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.staged_search_cleanup.sql
new file mode 100644 (file)
index 0000000..7a6357f
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.staged_search_cleanup.sql
@@ -0,0 +1,22 @@
+BEGIN;
+
+SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
+
+DROP FUNCTION search.query_parser_fts (
+    INT,
+    INT,
+    TEXT,
+    INT[],
+    INT[],
+    INT,
+    INT,
+    INT,
+    BOOL,
+    BOOL,
+    BOOL,
+    INT 
+);
+
+DROP TABLE asset.opac_visible_copies;
+
+COMMIT;