From 6ae4a57cb7a7428cbcc331222142ee5bdf1626ae Mon Sep 17 00:00:00 2001 From: dbs Date: Fri, 16 Oct 2009 04:00:11 +0000 Subject: [PATCH] Add a "complete subject" index and corresponding upgrade script git-svn-id: svn://svn.open-ils.org/ILS/branches/rel_1_6_0@14469 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 1 + Open-ILS/src/sql/Pg/1.4.0.5-1.6.0.0-upgrade-db.sql | 40 +++++++++++++++++++++- Open-ILS/src/sql/Pg/950.data.seed-values.sql | 2 ++ 3 files changed, 42 insertions(+), 1 deletion(-) diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index f6b87270e0..6f2d440a10 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -69,6 +69,7 @@ CREATE TRIGGER metabib_subject_field_entry_fti_trigger FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('subject'); CREATE INDEX metabib_subject_field_entry_index_vector_idx ON metabib.subject_field_entry USING GIST (index_vector); +CREATE INDEX metabib_subject_field_entry_source_idx ON metabib.subject_field_entry (source); CREATE TABLE metabib.keyword_field_entry ( diff --git a/Open-ILS/src/sql/Pg/1.4.0.5-1.6.0.0-upgrade-db.sql b/Open-ILS/src/sql/Pg/1.4.0.5-1.6.0.0-upgrade-db.sql index 1abb13ffde..7024b93ab9 100644 --- a/Open-ILS/src/sql/Pg/1.4.0.5-1.6.0.0-upgrade-db.sql +++ b/Open-ILS/src/sql/Pg/1.4.0.5-1.6.0.0-upgrade-db.sql @@ -3922,7 +3922,45 @@ CREATE TRIGGER zzz_update_materialized_simple_rec_delete_tgr AFTER UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE reporter.simple_rec_bib_sync(); - +-- Add a complete subject index +INSERT INTO config.metabib_field ( field_class, name, format, xpath ) VALUES + ( 'subject', 'complete', 'mods32', $$//mods32:mods/mods32:subject//text()$$ ); + +CREATE INDEX metabib_subject_field_entry_source_idx ON metabib.subject_field_entry (source); + +-- Insert all of the existing subject values into our new complete index +INSERT INTO metabib.subject_field_entry (source, field, value) + SELECT source, ( + SELECT id + FROM config.metabib_field + WHERE field_class = 'subject' AND name = 'complete' + ), + ARRAY_TO_STRING ( + ARRAY ( + SELECT value + FROM metabib.subject_field_entry msfe + WHERE msfe.source = groupee.source + ORDER BY source + ), ' ' + ) AS grouped + FROM ( + SELECT source + FROM metabib.subject_field_entry + GROUP BY source + ) AS groupee; + +-- Add values that weren't in the existing subject indices - primarily genres +UPDATE metabib.subject_field_entry msfe SET value = msfe.value || ' ' || mfr.value + FROM metabib.full_rec mfr + WHERE tag LIKE '65%' + AND subfield = 'v' + AND mfr.record = msfe.source + AND field IN ( + SELECT id + FROM config.metabib_field + WHERE field_class = 'subject' + AND name = 'complete' + ); ---------!!!!!!!!!!!!!!!!!!!!!!--------------- -- Must go after 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 f8adc5b6e8..31c8225cc9 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -43,6 +43,8 @@ INSERT INTO config.metabib_field ( field_class, name, format, xpath ) VALUES -- ( field_class, name, xpath ) VALUES ( 'subject', 'genre', 'mods32', $$//mods32:mods/mods32:genre$$ ); INSERT INTO config.metabib_field ( field_class, name, format, xpath ) VALUES ( 'keyword', 'keyword', 'mods32', $$//mods32:mods/*[not(local-name()='originInfo')]$$ ); -- /* to fool vim */; +INSERT INTO config.metabib_field ( field_class, name, format, xpath ) VALUES + ( 'subject', 'complete', 'mods32', $$//mods32:mods/mods32:subject//text()$$ ); INSERT INTO config.non_cataloged_type ( id, owning_lib, name ) VALUES ( 1, 1, oils_i18n_gettext(1, 'Paperback Book', 'cnct', 'name') ); SELECT SETVAL('config.non_cataloged_type_id_seq'::TEXT, 100); -- 2.11.0