From 147ffa832834a30560259bef3937442efafe7d50 Mon Sep 17 00:00:00 2001 From: miker Date: Tue, 18 May 2010 19:10:07 +0000 Subject: [PATCH] identifier search class, including some (I believe) sane default index defs git-svn-id: svn://svn.open-ils.org/ILS/trunk@16448 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/examples/fm_IDL.xml | 18 ++++++ .../perlmods/OpenILS/Application/Ingest.pm | 2 +- .../OpenILS/Application/Storage/CDBI.pm | 4 ++ .../Application/Storage/CDBI/metabib.pm | 9 +++ .../Application/Storage/Driver/Pg/dbi.pm | 9 +++ .../Storage/Publisher/authority.pm | 4 +- .../Application/Storage/Publisher/metabib.pm | 13 ++-- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 16 +++++ Open-ILS/src/sql/Pg/800.fkeys.sql | 6 ++ Open-ILS/src/sql/Pg/950.data.seed-values.sql | 29 ++++++++- .../0265.schema.identifier-search-class.sql | 62 +++++++++++++++++++ 11 files changed, 164 insertions(+), 8 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0265.schema.identifier-search-class.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index bf93f13d94..bfe2bafd7f 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -1734,6 +1734,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + @@ -1751,6 +1752,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + @@ -2124,6 +2126,8 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA UNION ALL SELECT * FROM metabib.keyword_field_entry UNION ALL + SELECT * FROM metabib.identifier_field_entry + UNION ALL SELECT * FROM metabib.title_field_entry UNION ALL SELECT * FROM metabib.subject_field_entry @@ -2149,6 +2153,8 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA UNION ALL SELECT * FROM metabib.keyword_field_entry UNION ALL + SELECT * FROM metabib.identifier_field_entry + UNION ALL SELECT * FROM metabib.title_field_entry UNION ALL SELECT * FROM metabib.subject_field_entry @@ -4044,6 +4050,18 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + + + + + + + + + diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Ingest.pm b/Open-ILS/src/perlmods/OpenILS/Application/Ingest.pm index 30892b517b..765000ce31 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Ingest.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Ingest.pm @@ -209,7 +209,7 @@ sub rw_biblio_ingest_single_object { $cstore->request( 'open-ils.cstore.direct.metabib.record_descriptor.create' => $blob->{descriptor} )->gather(1); # deal with classed fields... - for my $class ( qw/title author subject keyword series/ ) { + for my $class ( qw/title author subject keyword series identifier/ ) { $tmp = $cstore->request( "open-ils.cstore.direct.metabib.${class}_field_entry.id_list.atomic", { source => $bib->id } diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI.pm index 419179c9f1..e16cf42e46 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI.pm @@ -538,6 +538,9 @@ sub modify_from_fieldmapper { metabib::title_field_entry->has_a( source => 'biblio::record_entry' ); metabib::title_field_entry->has_a( field => 'config::metabib_field' ); + + metabib::identifier_field_entry->has_a( source => 'biblio::record_entry' ); + metabib::identifier_field_entry->has_a( field => 'config::metabib_field' ); metabib::author_field_entry->has_a( source => 'biblio::record_entry' ); metabib::author_field_entry->has_a( field => 'config::metabib_field' ); @@ -616,6 +619,7 @@ sub modify_from_fieldmapper { biblio::record_entry->has_many( call_numbers => 'asset::call_number' ); biblio::record_entry->has_many( full_record_entries => 'metabib::full_rec' ); biblio::record_entry->has_many( title_field_entries => 'metabib::title_field_entry' ); + biblio::record_entry->has_many( identifier_field_entries => 'metabib::identifier_field_entry' ); biblio::record_entry->has_many( author_field_entries => 'metabib::author_field_entry' ); biblio::record_entry->has_many( subject_field_entries => 'metabib::subject_field_entry' ); biblio::record_entry->has_many( keyword_field_entries => 'metabib::keyword_field_entry' ); diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/metabib.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/metabib.pm index bba4a400db..1316e93279 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/metabib.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/CDBI/metabib.pm @@ -12,6 +12,15 @@ metabib::metarecord->table( 'metabib_metarecord' ); metabib::metarecord->columns( Primary => qw/id/ ); metabib::metarecord->columns( Essential => qw/fingerprint master_record mods/ ); +#------------------------------------------------------------------------------- +package metabib::identifier_field_entry; +use base qw/metabib/; + +metabib::identifier_field_entry->table( 'metabib_identifier_field_entry' ); +metabib::identifier_field_entry->columns( Primary => qw/id/ ); +metabib::identifier_field_entry->columns( Essential => qw/field value source/ ); + + #------------------------------------------------------------------------------- package metabib::title_field_entry; use base qw/metabib/; diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg/dbi.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg/dbi.pm index b44a836321..55b44dae0d 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg/dbi.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Driver/Pg/dbi.pm @@ -503,6 +503,15 @@ metabib::metarecord->sequence( 'metabib.metarecord_id_seq' ); + #------------------------------------------------------------------------------- + + #------------------------------------------------------------------------------- + package metabib::identifier_field_entry; + + metabib::identifier_field_entry->table( 'metabib.identifier_field_entry' ); + metabib::identifier_field_entry->sequence( 'metabib.identifier_field_entry_id_seq' ); + metabib::identifier_field_entry->columns( 'FTS' => 'index_vector' ); + #------------------------------------------------------------------------------- #------------------------------------------------------------------------------- diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/authority.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/authority.pm index 9e3b1f2518..bdfe1eef8c 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/authority.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/authority.pm @@ -202,7 +202,7 @@ sub find_see_from_controlled { } return undef; } -for my $class ( qw/title author subject keyword series/ ) { +for my $class ( qw/title author subject keyword series identifier/ ) { __PACKAGE__->register_method( api_name => "open-ils.storage.authority.$class.see_from.controlled", method => 'find_see_from_controlled', @@ -233,7 +233,7 @@ sub find_see_also_from_controlled { } return undef; } -for my $class ( qw/title author subject keyword series/ ) { +for my $class ( qw/title author subject keyword series identifier/ ) { __PACKAGE__->register_method( api_name => "open-ils.storage.authority.$class.see_also_from.controlled", method => 'find_see_also_from_controlled', diff --git a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/metabib.pm b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/metabib.pm index 4135c193a0..bb715ef94e 100644 --- a/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/metabib.pm +++ b/Open-ILS/src/perlmods/OpenILS/Application/Storage/Publisher/metabib.pm @@ -880,7 +880,7 @@ sub search_class_fts { return undef; } -for my $class ( qw/title author subject keyword series/ ) { +for my $class ( qw/title author subject keyword series identifier/ ) { __PACKAGE__->register_method( api_name => "open-ils.storage.metabib.$class.search_fts.metarecord", method => 'search_class_fts', @@ -1028,7 +1028,7 @@ sub search_class_fts_count { return $recs; } -for my $class ( qw/title author subject keyword series/ ) { +for my $class ( qw/title author subject keyword series identifier/ ) { __PACKAGE__->register_method( api_name => "open-ils.storage.metabib.$class.search_fts.metarecord_count", method => 'search_class_fts_count', @@ -1149,7 +1149,11 @@ sub postfilter_search_class_fts { my @fts_ranks = $fts->fts_rank; my %bonus = (); - $bonus{'metabib::keyword_field_entry'} = [ { 'CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END' => $SQLstring } ]; + $bonus{'metabib::identifier_field_entry'} = + $bonus{'metabib::keyword_field_entry'} = [ + { 'CASE WHEN f.value ILIKE ? THEN 1.2 ELSE 1 END' => $SQLstring } + ]; + $bonus{'metabib::title_field_entry'} = $bonus{'metabib::series_field_entry'} = [ { 'CASE WHEN f.value ILIKE ? THEN 1.5 ELSE 1 END' => $first_word }, @@ -1397,7 +1401,7 @@ sub postfilter_search_class_fts { return undef; } -for my $class ( qw/title author subject keyword series/ ) { +for my $class ( qw/title author subject keyword series identifier/ ) { __PACKAGE__->register_method( api_name => "open-ils.storage.metabib.$class.post_filter.search_fts.metarecord", method => 'postfilter_search_class_fts', @@ -1423,6 +1427,7 @@ my $_cdbi = { title => "metabib::title_field_entry", subject => "metabib::subject_field_entry", keyword => "metabib::keyword_field_entry", series => "metabib::series_field_entry", + identifier => "metabib::identifier_field_entry", }; # XXX factored most of the PG dependant stuff out of here... need to find a way to do "dependants". diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index ee5701e185..afb3ba509b 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -29,6 +29,22 @@ CREATE TABLE metabib.metarecord ( CREATE INDEX metabib_metarecord_master_record_idx ON metabib.metarecord (master_record); CREATE INDEX metabib_metarecord_fingerprint_idx ON metabib.metarecord (fingerprint); +CREATE TABLE metabib.identifier_field_entry ( + id BIGSERIAL PRIMARY KEY, + source BIGINT NOT NULL, + field INT NOT NULL, + value TEXT NOT NULL, + index_vector tsvector NOT NULL +); +CREATE TRIGGER metabib_identifier_field_entry_fti_trigger + BEFORE UPDATE OR INSERT ON metabib.identifier_field_entry + FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword'); + +CREATE INDEX metabib_identifier_field_entry_index_vector_idx ON metabib.identifier_field_entry USING GIST (index_vector); +CREATE INDEX metabib_identifier_field_entry_value_idx ON metabib.identifier_field_entry (SUBSTRING(value,1,1024)) WHERE index_vector = ''::TSVECTOR; +CREATE INDEX metabib_identifier_field_entry_source_idx ON metabib.identifier_field_entry (source); + + CREATE TABLE metabib.title_field_entry ( id BIGSERIAL PRIMARY KEY, source BIGINT NOT NULL, diff --git a/Open-ILS/src/sql/Pg/800.fkeys.sql b/Open-ILS/src/sql/Pg/800.fkeys.sql index 06de96b52b..fc05bf5d2d 100644 --- a/Open-ILS/src/sql/Pg/800.fkeys.sql +++ b/Open-ILS/src/sql/Pg/800.fkeys.sql @@ -54,6 +54,9 @@ ALTER TABLE metabib.metarecord ADD CONSTRAINT metabib_metarecord_master_record_f ALTER TABLE metabib.title_field_entry ADD CONSTRAINT metabib_title_field_entry_source_pkey FOREIGN KEY (source) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; ALTER TABLE metabib.title_field_entry ADD CONSTRAINT metabib_title_field_entry_field_pkey FOREIGN KEY (field) REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE metabib.identifier_field_entry ADD CONSTRAINT metabib_identifier_field_entry_source_pkey FOREIGN KEY (source) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE metabib.identifier_field_entry ADD CONSTRAINT metabib_identifier_field_entry_field_pkey FOREIGN KEY (field) REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + ALTER TABLE metabib.author_field_entry ADD CONSTRAINT metabib_author_field_entry_source_pkey FOREIGN KEY (source) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; ALTER TABLE metabib.author_field_entry ADD CONSTRAINT metabib_author_field_entry_field_pkey FOREIGN KEY (field) REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; @@ -63,6 +66,9 @@ ALTER TABLE metabib.subject_field_entry ADD CONSTRAINT metabib_subject_field_ent ALTER TABLE metabib.keyword_field_entry ADD CONSTRAINT metabib_keyword_field_entry_source_pkey FOREIGN KEY (source) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; ALTER TABLE metabib.keyword_field_entry ADD CONSTRAINT metabib_keyword_field_entry_field_pkey FOREIGN KEY (field) REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE metabib.series_field_entry ADD CONSTRAINT metabib_series_field_entry_source_pkey FOREIGN KEY (source) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE metabib.series_field_entry ADD CONSTRAINT metabib_series_field_entry_field_pkey FOREIGN KEY (field) REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + ALTER TABLE metabib.rec_descriptor ADD CONSTRAINT metabib_rec_descriptor_record_fkey FOREIGN KEY (record) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; ALTER TABLE metabib.real_full_rec ADD CONSTRAINT metabib_full_rec_record_fkey FOREIGN KEY (record) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; 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 a6e928d460..cd8c42e912 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -11,6 +11,7 @@ INSERT INTO config.standing (id, value) VALUES (1, oils_i18n_gettext(1, 'Good', INSERT INTO config.standing (id, value) VALUES (2, oils_i18n_gettext(2, 'Barred', 'cst', 'value')); SELECT SETVAL('config.standing_id_seq'::TEXT, 100); +INSERT INTO config.metabib_class ( name, label ) VALUES ( 'identifier', oils_i18n_gettext('identifier', 'Identifier', 'cmc', 'name') ); INSERT INTO config.metabib_class ( name, label ) VALUES ( 'keyword', oils_i18n_gettext('keyword', 'Keyword', 'cmc', 'name') ); INSERT INTO config.metabib_class ( name, label ) VALUES ( 'title', oils_i18n_gettext('title', 'Title', 'cmc', 'name') ); INSERT INTO config.metabib_class ( name, label ) VALUES ( 'author', oils_i18n_gettext('author', 'Author', 'cmc', 'name') ); @@ -54,17 +55,43 @@ INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath ) (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 ) VALUES (16, 'subject', 'complete', oils_i18n_gettext(16, 'All Subjects', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:subject//text()$$ ); + +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES + (17, 'identifier', 'accession', oils_i18n_gettext(17, 'Accession Number', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="001"]/text()$$, TRUE ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES + (18, 'identifier', 'isbn', oils_i18n_gettext(18, 'ISBN', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="020"]/marcxml:subfield[code="a" or code="z"]/text()$$, TRUE ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES + (19, 'identifier', 'issn', oils_i18n_gettext(19, 'ISSN', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="022"]/marcxml:subfield[code="a" or code="z"]/text()$$, TRUE ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES + (20, 'identifier', 'upc', oils_i18n_gettext(20, 'UPC', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="024" and ind1="1"]/marcxml:subfield[code="a" or code="z"]/text()$$, TRUE ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES + (21, 'identifier', 'ismn', oils_i18n_gettext(21, 'ISMN', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="024" and ind1="2"]/marcxml:subfield[code="a" or code="z"]/text()$$, TRUE ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES + (22, 'identifier', 'ean', oils_i18n_gettext(22, 'EAN', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="024" and ind1="3"]/marcxml:subfield[code="a" or code="z"]/text()$$, TRUE ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES + (23, 'identifier', 'isrc', oils_i18n_gettext(23, 'ISRC', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="024" and ind1="0"]/marcxml:subfield[code="a" or code="z"]/text()$$, TRUE ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES + (24, 'identifier', 'sici', oils_i18n_gettext(24, 'SICI', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="024" and ind1="4"]/marcxml:subfield[code="a" or code="z"]/text()$$, TRUE ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES + (25, 'identifier', 'bibcn', oils_i18n_gettext(25, 'Local Free-Text Call Number', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="099"]//text()$$, TRUE ); + SELECT SETVAL('config.metabib_field_id_seq'::TEXT, (SELECT MAX(id) FROM config.metabib_field), TRUE); INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('kw','keyword'); INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('eg.keyword','keyword'); INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('dc.publisher','keyword'); -INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('dc.identifier','keyword'); INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('bib.subjecttitle','keyword'); INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('bib.genre','keyword'); INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('bib.edition','keyword'); INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('srw.serverchoice','keyword'); +INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('id','identifier'); +INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('dc.identifier','identifier'); +INSERT INTO config.metabib_search_alias (alias,field_class,field) VALUES ('eg.isbn','identifier', 18); +INSERT INTO config.metabib_search_alias (alias,field_class,field) VALUES ('eg.issn','identifier', 19); +INSERT INTO config.metabib_search_alias (alias,field_class,field) VALUES ('eg.upc','identifier', 20); +INSERT INTO config.metabib_search_alias (alias,field_class,field) VALUES ('eg.callnumber','identifier', 25); + INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('au','author'); INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('name','author'); INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('creator','author'); diff --git a/Open-ILS/src/sql/Pg/upgrade/0265.schema.identifier-search-class.sql b/Open-ILS/src/sql/Pg/upgrade/0265.schema.identifier-search-class.sql new file mode 100644 index 0000000000..26ba259a0e --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0265.schema.identifier-search-class.sql @@ -0,0 +1,62 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0265'); -- miker + +ALTER TABLE config.metabib_field DROP CONSTRAINT metabib_field_field_class_check; + +INSERT INTO config.metabib_class ( name, label ) VALUES ( 'identifier', oils_i18n_gettext('identifier', 'Identifier', 'cmc', 'name') ); + +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES + (17, 'identifier', 'accession', oils_i18n_gettext(17, 'Accession Number', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="001"]/text()$$, TRUE ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES + (18, 'identifier', 'isbn', oils_i18n_gettext(18, 'ISBN', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="020"]/marcxml:subfield[code="a" or code="z"]/text()$$, TRUE ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES + (19, 'identifier', 'issn', oils_i18n_gettext(19, 'ISSN', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="022"]/marcxml:subfield[code="a" or code="z"]/text()$$, TRUE ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES + (20, 'identifier', 'upc', oils_i18n_gettext(20, 'UPC', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="024" and ind1="1"]/marcxml:subfield[code="a" or code="z"]/text()$$, TRUE ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES + (21, 'identifier', 'ismn', oils_i18n_gettext(21, 'ISMN', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="024" and ind1="2"]/marcxml:subfield[code="a" or code="z"]/text()$$, TRUE ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES + (22, 'identifier', 'ean', oils_i18n_gettext(22, 'EAN', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="024" and ind1="3"]/marcxml:subfield[code="a" or code="z"]/text()$$, TRUE ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES + (23, 'identifier', 'isrc', oils_i18n_gettext(23, 'ISRC', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="024" and ind1="0"]/marcxml:subfield[code="a" or code="z"]/text()$$, TRUE ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES + (24, 'identifier', 'sici', oils_i18n_gettext(24, 'SICI', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="024" and ind1="4"]/marcxml:subfield[code="a" or code="z"]/text()$$, TRUE ); +INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES + (25, 'identifier', 'bibcn', oils_i18n_gettext(25, 'Local Free-Text Call Number', 'cmf', 'label'), 'marcxml', $$//marcxml:datafield[tag="099"]//text()$$, TRUE ); + +SELECT SETVAL('config.metabib_field_id_seq'::TEXT, (SELECT MAX(id) FROM config.metabib_field), TRUE); + + +DELETE FROM config.metabib_search_alias WHERE alias = 'dc.identifier'; + +INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('id','identifier'); +INSERT INTO config.metabib_search_alias (alias,field_class) VALUES ('dc.identifier','identifier'); +INSERT INTO config.metabib_search_alias (alias,field_class,field) VALUES ('eg.isbn','identifier', 18); +INSERT INTO config.metabib_search_alias (alias,field_class,field) VALUES ('eg.issn','identifier', 19); +INSERT INTO config.metabib_search_alias (alias,field_class,field) VALUES ('eg.upc','identifier', 20); +INSERT INTO config.metabib_search_alias (alias,field_class,field) VALUES ('eg.callnumber','identifier', 25); + +CREATE TABLE metabib.identifier_field_entry ( + id BIGSERIAL PRIMARY KEY, + source BIGINT NOT NULL, + field INT NOT NULL, + value TEXT NOT NULL, + index_vector tsvector NOT NULL +); +CREATE TRIGGER metabib_identifier_field_entry_fti_trigger + BEFORE UPDATE OR INSERT ON metabib.identifier_field_entry + FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword'); + +CREATE INDEX metabib_identifier_field_entry_index_vector_idx ON metabib.identifier_field_entry USING GIST (index_vector); +CREATE INDEX metabib_identifier_field_entry_value_idx ON metabib.identifier_field_entry + (SUBSTRING(value,1,1024)) WHERE index_vector = ''::TSVECTOR; +CREATE INDEX metabib_identifier_field_entry_source_idx ON metabib.identifier_field_entry (source); + +ALTER TABLE metabib.identifier_field_entry ADD CONSTRAINT metabib_identifier_field_entry_source_pkey + FOREIGN KEY (source) REFERENCES biblio.record_entry (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE metabib.identifier_field_entry ADD CONSTRAINT metabib_identifier_field_entry_field_pkey + FOREIGN KEY (field) REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + +COMMIT; + -- 2.43.2