From 86680b4008cb43c670dde836a09e6cf88cd50536 Mon Sep 17 00:00:00 2001 From: Lebbeous Fogle-Weekley Date: Tue, 24 Jan 2012 17:53:34 -0500 Subject: [PATCH] resync database upgrade script with baseline schema. They should both give you the same thing again. Signed-off-by: Lebbeous Fogle-Weekley --- Open-ILS/src/sql/Pg/002.schema.config.sql | 3 +- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 187 +++++++++++++++++++++ Open-ILS/src/sql/Pg/950.data.seed-values.sql | 4 +- .../sql/Pg/upgrade/YYYY.schema.bib_autosuggest.sql | 13 +- 4 files changed, 200 insertions(+), 7 deletions(-) diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index fa24e609d7..e30be8cd63 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -183,7 +183,8 @@ CREATE TABLE config.metabib_field ( facet_field BOOL NOT NULL DEFAULT FALSE, browse_field BOOL NOT NULL DEFAULT TRUE, browse_xpath TEXT, - facet_xpath TEXT + facet_xpath TEXT, + restrict BOOL DEFAULT FALSE NOT NULL ); COMMENT ON TABLE config.metabib_field IS $$ XPath used for record indexing ingest diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index d6acc28e7a..150e18ad22 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -1216,4 +1216,191 @@ END; $$ LANGUAGE PLPGSQL; +-- This mimics a specific part of QueryParser, turning the first part of a +-- classed search (search_class) into a set of classes and possibly fields. +-- search_class might look like "author" or "title|proper" or "ti|uniform" +-- or "au" or "au|corporate|personal" or anything like that, where the first +-- element of the list you get by separating on the "|" character is either +-- a registered class (config.metabib_class) or an alias +-- (config.metabib_search_alias), and the rest of any such elements are +-- fields (config.metabib_field). +CREATE OR REPLACE + FUNCTION metabib.search_class_to_registered_components(search_class TEXT) + RETURNS SETOF RECORD AS $func$ +DECLARE + search_parts TEXT[]; + field_name TEXT; + search_part_count INTEGER; + rec RECORD; + registered_class config.metabib_class%ROWTYPE; + registered_alias config.metabib_search_alias%ROWTYPE; + registered_field config.metabib_field%ROWTYPE; +BEGIN + search_parts := REGEXP_SPLIT_TO_ARRAY(search_class, E'\\|'); + + search_part_count := ARRAY_LENGTH(search_parts, 1); + IF search_part_count = 0 THEN + RETURN; + ELSE + SELECT INTO registered_class + * FROM config.metabib_class WHERE name = search_parts[1]; + IF FOUND THEN + IF search_part_count < 2 THEN -- all fields + rec := (registered_class.name, NULL::INTEGER); + RETURN NEXT rec; + RETURN; -- done + END IF; + FOR field_name IN SELECT * + FROM UNNEST(search_parts[2:search_part_count]) LOOP + SELECT INTO registered_field + * FROM config.metabib_field + WHERE name = field_name AND + field_class = registered_class.name; + IF FOUND THEN + rec := (registered_class.name, registered_field.id); + RETURN NEXT rec; + END IF; + END LOOP; + ELSE + -- maybe we have an alias? + SELECT INTO registered_alias + * FROM config.metabib_search_alias WHERE alias=search_parts[1]; + IF NOT FOUND THEN + RETURN; + ELSE + IF search_part_count < 2 THEN -- return w/e the alias says + rec := ( + registered_alias.field_class, registered_alias.field + ); + RETURN NEXT rec; + RETURN; -- done + ELSE + FOR field_name IN SELECT * + FROM UNNEST(search_parts[2:search_part_count]) LOOP + SELECT INTO registered_field + * FROM config.metabib_field + WHERE name = field_name AND + field_class = registered_alias.field_class; + IF FOUND THEN + rec := ( + registered_alias.field_class, + registered_field.id + ); + RETURN NEXT rec; + END IF; + END LOOP; + END IF; + END IF; + END IF; + END IF; +END; +$func$ LANGUAGE PLPGSQL; + + +CREATE OR REPLACE + FUNCTION metabib.suggest_browse_entries( + query_text TEXT, -- 'foo' or 'foo & ba:*',ready for to_tsquery() + search_class TEXT, -- 'alias' or 'class' or 'class|field..', etc + headline_opts TEXT, -- markup options for ts_headline() + visibility_org INTEGER,-- null if you don't want opac visibility test + query_limit INTEGER -- use in LIMIT clause of interal query + ) RETURNS TABLE ( + value TEXT, -- plain + match TEXT, -- marked up + field INTEGER, + bouyant_and_class_match BOOL, + field_match BOOL, + field_weight INTEGER, + rank REAL, + bouyant BOOL + ) AS $func$ +DECLARE + query TSQUERY; + opac_visibility_join TEXT; + search_class_join TEXT; + r_fields RECORD; +BEGIN + query := TO_TSQUERY('keyword', query_text); + + IF visibility_org IS NOT NULL THEN + opac_visibility_join := ' + JOIN asset.opac_visible_copies aovc ON ( + aovc.record = mbedm.source AND + aovc.circ_lib IN (SELECT id FROM actor.org_unit_descendants($4)) + )'; + ELSE + opac_visibility_join := ''; + END IF; + + -- The following determines whether we only provide suggestsons matching + -- the user's selected search_class, or whether we show other suggestions + -- too. The reason for MIN() is that for search_classes like + -- 'title|proper|uniform' you would otherwise get multiple rows. The + -- implication is that if title as a class doesn't have restrict, + -- nor does the proper field, but the uniform field does, you're going + -- to get 'false' for your overall evaluation of 'should we restrict?' + -- To invert that, change from MIN() to MAX(). + + SELECT + INTO r_fields + MIN(cmc.restrict::INT) AS restrict_class, + MIN(cmf.restrict::INT) AS restrict_field + FROM metabib.search_class_to_registered_components(search_class) + AS _registered (field_class TEXT, field INT) + JOIN + config.metabib_class cmc ON (cmc.name = _registered.field_class) + LEFT JOIN + config.metabib_field cmf ON (cmf.id = _registered.field); + + -- evaluate 'should we restrict?' + IF r_fields.restrict_field::BOOL OR r_fields.restrict_class::BOOL THEN + search_class_join := ' + JOIN + metabib.search_class_to_registered_components($2) + AS _registered (field_class TEXT, field INT) ON ( + (_registered.field IS NULL AND + _registered.field_class = cmf.field_class) OR + (_registered.field = cmf.id) + ) + '; + ELSE + search_class_join := ' + LEFT JOIN + metabib.search_class_to_registered_components($2) + AS _registered (field_class TEXT, field INT) ON ( + _registered.field_class = cmc.name + ) + '; + END IF; + + RETURN QUERY EXECUTE 'SELECT DISTINCT + mbe.value, + TS_HEADLINE(mbe.value, $1, $3), + cmf.id, + cmc.bouyant AND _registered.field_class IS NOT NULL, + _registered.field = cmf.id, + cmf.weight, + TS_RANK_CD(mbe.index_vector, $1), + cmc.bouyant + FROM metabib.browse_entry_def_map mbedm + JOIN metabib.browse_entry mbe ON (mbe.id = mbedm.entry) + JOIN config.metabib_field cmf ON (cmf.id = mbedm.def) + JOIN config.metabib_class cmc ON (cmf.field_class = cmc.name) + ' || search_class_join || opac_visibility_join || ' + WHERE $1 @@ mbe.index_vector + ORDER BY 4 DESC, 5 DESC NULLS LAST, 6 DESC, 7 DESC, 8 DESC, 1 ASC + LIMIT $5 + ' USING query, search_class, headline_opts, visibility_org, query_limit; + + -- sort order: + -- bouyant AND chosen class = match class + -- chosen field = match field + -- field weight + -- rank + -- bouyancy + -- value itself + +END; +$func$ LANGUAGE PLPGSQL; + COMMIT; diff --git a/Open-ILS/src/sql/Pg/950.data.seed-values.sql b/Open-ILS/src/sql/Pg/950.data.seed-values.sql index 420ef9cbb7..55d189981f 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -141,8 +141,8 @@ INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, (14, 'subject', 'topic', oils_i18n_gettext(14, 'Topic Subject', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:subject/mods32:topic$$, TRUE ); --INSERT INTO config.metabib_field ( id, field_class, name, format, xpath ) VALUES -- ( id, field_class, name, xpath ) VALUES ( 'subject', 'genre', 'mods32', $$//mods32:mods/mods32:genre$$ ); -INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath ) VALUES - (15, 'keyword', 'keyword', oils_i18n_gettext(15, 'General Keywords', 'cmf', 'label'), 'mods32', $$//mods32:mods/*[not(local-name()='originInfo')]$$ ); -- /* to fool vim */; +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, browse_field ) VALUES + (15, 'keyword', 'keyword', oils_i18n_gettext(15, 'General Keywords', 'cmf', 'label'), 'mods32', $$//mods32:mods/*[not(local-name()='originInfo')]$$, FALSE ); -- /* to fool vim */; INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath ) VALUES (16, 'subject', 'complete', oils_i18n_gettext(16, 'All Subjects', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:subject$$ ); diff --git a/Open-ILS/src/sql/Pg/upgrade/YYYY.schema.bib_autosuggest.sql b/Open-ILS/src/sql/Pg/upgrade/YYYY.schema.bib_autosuggest.sql index 091265ab9a..e03f13cc24 100644 --- a/Open-ILS/src/sql/Pg/upgrade/YYYY.schema.bib_autosuggest.sql +++ b/Open-ILS/src/sql/Pg/upgrade/YYYY.schema.bib_autosuggest.sql @@ -14,6 +14,10 @@ CREATE TABLE metabib.browse_entry ( index_vector tsvector ); CREATE INDEX metabib_browse_entry_index_vector_idx ON metabib.browse_entry USING GIST (index_vector); +CREATE TRIGGER metabib_browse_entry_fti_trigger + BEFORE INSERT OR UPDATE ON metabib.browse_entry + FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword'); + CREATE TABLE metabib.browse_entry_def_map ( id BIGSERIAL PRIMARY KEY, @@ -29,6 +33,11 @@ ALTER TABLE config.metabib_class ADD COLUMN bouyant BOOLEAN DEFAULT FALSE NOT NU ALTER TABLE config.metabib_class ADD COLUMN restrict BOOLEAN DEFAULT FALSE NOT NULL; ALTER TABLE config.metabib_field ADD COLUMN restrict BOOLEAN DEFAULT FALSE NOT NULL; +-- one good exception to default true: +UPDATE config.metabib_field + SET browse_field = FALSE + WHERE field_class = 'keyword' AND name = 'keyword'; + -- AFTER UPDATE OR INSERT trigger for biblio.record_entry -- We're only touching it here to add a DELETE statement to the IF NEW.deleted -- block. @@ -224,10 +233,6 @@ END; $$ LANGUAGE PLPGSQL; -CREATE TRIGGER metabib_browse_entry_fti_trigger - BEFORE INSERT OR UPDATE ON metabib.browse_entry - FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword'); - DROP FUNCTION biblio.extract_metabib_field_entry(bigint, text); DROP FUNCTION biblio.extract_metabib_field_entry(bigint); -- 2.11.0