From 1a824a37535f9698e72440083a2f2f44f533eaec Mon Sep 17 00:00:00 2001 From: Jeff Davis Date: Wed, 5 Jun 2019 14:30:11 -0700 Subject: [PATCH] LP#1831803: remove obsolete asset.opac_visible_copies and search.query_parser_fts from database Signed-off-by: Jeff Davis Signed-off-by: Jason Boyer Signed-off-by: Galen Charlton --- .../Application/Storage/Publisher/metabib.pm | 19 - Open-ILS/src/sql/Pg/040.schema.asset.sql | 15 - .../src/sql/Pg/300.schema.staged_search.sql | 374 +----------------- .../XXXX.schema.staged_search_cleanup.sql | 22 ++ 4 files changed, 23 insertions(+), 407 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.staged_search_cleanup.sql 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 987b906c5e..5766ccddfe 100644 --- 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 2002113b99..44de65b0dc 100644 --- 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 dcdb5af5db..5e2bbee415 100644 --- 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 index 0000000000..7a6357f323 --- /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; -- 2.43.2