From 162115a371245783dee6b72778d301cdb80b6f55 Mon Sep 17 00:00:00 2001 From: miker Date: Mon, 7 Mar 2011 16:02:36 +0000 Subject: [PATCH] Working towards SVF. This is most of phase 1, which is basic DB and QP support. git-svn-id: svn://svn.open-ils.org/ILS/trunk@19596 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/examples/fm_IDL.xml | 74 ++++ .../Application/Storage/Driver/Pg/QueryParser.pm | 198 ++++++--- .../lib/OpenILS/Application/Storage/QueryParser.pm | 29 ++ Open-ILS/src/sql/Pg/002.schema.config.sql | 90 +++-- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 264 ++++++++---- Open-ILS/src/sql/Pg/040.schema.asset.sql | 27 -- Open-ILS/src/sql/Pg/090.schema.action.sql | 15 - Open-ILS/src/sql/Pg/100.circ_matrix.sql | 6 +- Open-ILS/src/sql/Pg/110.hold_matrix.sql | 6 +- Open-ILS/src/sql/Pg/800.fkeys.sql | 2 - Open-ILS/src/sql/Pg/950.data.seed-values.sql | 30 ++ .../sql/Pg/upgrade/XXXX.schema.svf-phase-one.sql | 452 ++++++++++++++++++++ 12 files changed, 957 insertions(+), 236 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.svf-phase-one.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 60e38b8..13b5913 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -571,6 +571,80 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm index a27081e..a84b9e8 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm @@ -33,7 +33,6 @@ sub quote_phrase_value { sub init { my $class = shift; - } sub default_preferred_language { @@ -72,6 +71,24 @@ sub toSQL { return $self->parse_tree->toSQL; } +sub dynamic_filters { + my $self = shift; + my $new = shift; + + $self->custom_data->{dynamic_filters} ||= []; + push(@{$self->custom_data->{dynamic_filters}}, $new) if ($new); + return $self->custom_data->{dynamic_filters}; +} + +sub dynamic_sorters { + my $self = shift; + my $new = shift; + + $self->custom_data->{dynamic_sorters} ||= []; + push(@{$self->custom_data->{dynamic_sorters}}, $new) if ($new); + return $self->custom_data->{dynamic_sorters}; +} + sub facet_field_id_map { my $self = shift; my $map = shift; @@ -237,7 +254,7 @@ sub initialize_relevance_bumps { return $self->relevance_bumps; } -sub initialize_normalizers { +sub initialize_query_normalizers { my $self = shift; my $tree = shift; # open-ils.cstore.direct.config.metabib_field_index_norm_map.search.atomic { "id" : { "!=" : null } }, { "flesh" : 1, "flesh_fields" : { "cmfinm" : ["norm"] }, "order_by" : [{ "class" : "cmfinm", "field" : "pos" }] } @@ -247,6 +264,25 @@ sub initialize_normalizers { } } +sub initialize_dynamic_filters { + my $self = shift; + my $list = shift; # open-ils.cstore.direct.config.record_attr_definition.search.atomic { "id" : { "!=" : null } } + + for my $crad ( @$list ) { + __PACKAGE__->dynamic_filters( __PACKAGE__->add_search_filter( $crad->name ) ) if ($U->is_true($crad->filter)); + __PACKAGE__->dynamic_sorters( $crad->name ) if ($U->is_true($crad->sorter)); + } +} + +sub initialize_filter_normalizers { + my $self = shift; + my $tree = shift; # open-ils.cstore.direct.config.record_attr_index_norm_map.search.atomic { "id" : { "!=" : null } }, { "flesh" : 1, "flesh_fields" : { "crainm" : ["norm"] }, "order_by" : [{ "class" : "crainm", "field" : "pos" }] } + + for my $crainm ( @$tree ) { + __PACKAGE__->add_filter_normalizer( $crainm->name, $crainm->norm->func, OpenSRF::Utils::JSON->JSON2perl($crainm->params) ); + } +} + our $_complete = 0; sub initialization_complete { return $_complete; @@ -258,6 +294,18 @@ sub initialize { return $_complete if ($_complete); + # tsearch rank normalization adjustments. see http://www.postgresql.org/docs/9.0/interactive/textsearch-controls.html#TEXTSEARCH-RANKING for details + $self->custom_data->{rank_cd_weight_map} = { + CD_logDocumentLength => 1, + CD_documentLength => 2, + CD_meanHarmonic => 4, + CD_uniqueWords => 8, + CD_logUniqueWords => 16, + CD_selfPlusOne => 32 + }; + + $self->add_search_modifier( $_ ) for (keys %{ $self->custom_data->{rank_cd_weight_map} }); + $self->initialize_search_field_id_map( $args{config_metabib_field} ) if ($args{config_metabib_field}); @@ -267,14 +315,21 @@ sub initialize { $self->initialize_relevance_bumps( $args{search_relevance_adjustment} ) if ($args{search_relevance_adjustment}); - $self->initialize_normalizers( $args{config_metabib_field_index_norm_map} ) + $self->initialize_query_normalizers( $args{config_metabib_field_index_norm_map} ) if ($args{config_metabib_field_index_norm_map}); + $self->initialize_dynamic_filters( $args{config_record_attr_definition} ) + if ($args{config_record_attr_definition}); + + $self->initialize_filter_normalizers( $args{config_record_attr_index_norm_map} ) + if ($args{config_record_attr_index_norm_map}); + $_complete = 1 if ( $args{config_metabib_field_index_norm_map} && $args{search_relevance_adjustment} && $args{config_metabib_search_alias} && - $args{config_metabib_field} + $args{config_metabib_field} && + $args{config_record_attr_definition} ); return $_complete; @@ -348,43 +403,55 @@ sub TEST_SETUP { __PACKAGE__->default_search_class( 'keyword' ); +# XXX to become magic filters __PACKAGE__->add_search_filter( 'audience' ); __PACKAGE__->add_search_filter( 'vr_format' ); -__PACKAGE__->add_search_filter( 'format' ); __PACKAGE__->add_search_filter( 'item_type' ); __PACKAGE__->add_search_filter( 'item_form' ); __PACKAGE__->add_search_filter( 'lit_form' ); +__PACKAGE__->add_search_filter( 'bib_level' ); + +# will be retained simply for back-compat +__PACKAGE__->add_search_filter( 'format' ); + +# grumble grumble, special cases against date1 and date2 +__PACKAGE__->add_search_filter( 'before' ); +__PACKAGE__->add_search_filter( 'after' ); +__PACKAGE__->add_search_filter( 'between' ); +__PACKAGE__->add_search_filter( 'during' ); + +# used by layers above this +__PACKAGE__->add_search_filter( 'statuses' ); __PACKAGE__->add_search_filter( 'locations' ); __PACKAGE__->add_search_filter( 'site' ); __PACKAGE__->add_search_filter( 'lasso' ); __PACKAGE__->add_search_filter( 'my_lasso' ); __PACKAGE__->add_search_filter( 'depth' ); -__PACKAGE__->add_search_filter( 'sort' ); __PACKAGE__->add_search_filter( 'language' ); -__PACKAGE__->add_search_filter( 'preferred_language' ); -__PACKAGE__->add_search_filter( 'preferred_language_weight' ); -__PACKAGE__->add_search_filter( 'preferred_language_multiplier' ); -__PACKAGE__->add_search_filter( 'statuses' ); -__PACKAGE__->add_search_filter( 'bib_level' ); -__PACKAGE__->add_search_filter( 'before' ); -__PACKAGE__->add_search_filter( 'after' ); -__PACKAGE__->add_search_filter( 'between' ); -__PACKAGE__->add_search_filter( 'during' ); __PACKAGE__->add_search_filter( 'offset' ); __PACKAGE__->add_search_filter( 'limit' ); -__PACKAGE__->add_search_filter( 'core_limit' ); __PACKAGE__->add_search_filter( 'check_limit' ); __PACKAGE__->add_search_filter( 'skip_check' ); __PACKAGE__->add_search_filter( 'superpage' ); __PACKAGE__->add_search_filter( 'superpage_size' ); __PACKAGE__->add_search_filter( 'estimation_strategy' ); - __PACKAGE__->add_search_modifier( 'available' ); +__PACKAGE__->add_search_modifier( 'staff' ); + +# used internally, but generally not user-settable +__PACKAGE__->add_search_filter( 'preferred_language' ); +__PACKAGE__->add_search_filter( 'preferred_language_weight' ); +__PACKAGE__->add_search_filter( 'preferred_language_multiplier' ); +__PACKAGE__->add_search_filter( 'core_limit' ); + +# XXX Valid values to be supplied by SVF +__PACKAGE__->add_search_filter( 'sort' ); + +# modifies core query, not configurable __PACKAGE__->add_search_modifier( 'descending' ); __PACKAGE__->add_search_modifier( 'ascending' ); __PACKAGE__->add_search_modifier( 'metarecord' ); __PACKAGE__->add_search_modifier( 'metabib' ); -__PACKAGE__->add_search_modifier( 'staff' ); #------------------------------- @@ -435,54 +502,53 @@ sub toSQL { if (($filters{preferred_language} || $self->QueryParser->default_preferred_language) && ($filters{preferred_language_multiplier} || $self->QueryParser->default_preferred_language_multiplier)) { my $pl = $self->QueryParser->quote_value( $filters{preferred_language} ? $filters{preferred_language} : $self->QueryParser->default_preferred_language ); my $plw = $filters{preferred_language_multiplier} ? $filters{preferred_language_multiplier} : $self->QueryParser->default_preferred_language_multiplier; - $rel = "($rel * COALESCE( NULLIF( FIRST(mrd.item_lang) = $pl , FALSE )::INT * $plw, 1))"; + $rel = "($rel * COALESCE( NULLIF( mrd.attrs \@> hstore('item_lang', $pl), FALSE )::INT * $plw, 1))"; } - $rel .= '::NUMERIC'; + $rel = "1.0/($rel)::NUMERIC"; - for my $f ( qw/audience vr_format item_type item_form lit_form language bib_level/ ) { + my %dyn_filters = ( '' => [] ); # the "catch-all" key + for my $f ( @{ $self->dynamic_filters } ) { my $col = $f; - $col = 'item_lang' if ($f eq 'language'); - $filters{$f} = ''; + $col = 'item_lang' if ($f eq 'language'); #XXX filter aliases would address this ... booo ... later + + $dyn_filters{$f} = ''; + my ($filter) = $self->find_filter($f); if ($filter) { - $filters{$f} = "AND mrd.$col in (" . join(",",map { $self->QueryParser->quote_value($_) } @{$filter->args}) . ")"; + my @fargs = @{$filter->args}; + + if (@fargs > 1) { + $dyn_filters{$f} = "( " . + join( + " OR ", + map { "mrd.attrs \@> hstore('$col', " . $self->QueryParser->quote_value($_) . ")" } @fargs + ) . + " )"; + } else { + push(@{$dyn_filters{''}}, "hstore('$col', " . $self->QueryParser->quote_value($fargs[0]) . ")"); + } } } - my $audience = $filters{audience}; - my $vr_format = $filters{vr_format}; - my $item_type = $filters{item_type}; - my $item_form = $filters{item_form}; - my $lit_form = $filters{lit_form}; - my $language = $filters{language}; - my $bib_level = $filters{bib_level}; + my $combined_dyn_filters = 'mrd.attrs @> (' . join(' || ', @{$dyn_filters{''}}) . ')'; + delete($dyn_filters{''}); + $combined_dyn_filters .= join(' AND ', values(%dyn_filters)); + my $rank = $rel; my $desc = 'ASC'; $desc = 'DESC' if ($self->find_modifier('descending')); - if ($sort_filter eq 'rel') { # relevance ranking flips sort dir - if ($desc eq 'ASC') { - $desc = 'DESC'; - } else { - $desc = 'ASC'; - } + if (grep {$_ eq $sort_filter} @{$self->dynamic_sorters}) { + $rank = "(mrd.attrs->'$sort_filter')" + } elsif ($sort_filter eq 'create_date') { + $rank = "FIRST((SELECT create_date FROM biblio.record_entry rbr WHERE rbr.id = m.source))"; + } elsif ($sort_filter eq 'edit_date') { + $rank = "FIRST((SELECT edit_date FROM biblio.record_entry rbr WHERE rbr.id = m.source))"; } else { - if ($sort_filter eq 'title') { - $rank = "FIRST((SELECT frt.value FROM metabib.full_rec frt WHERE frt.record = m.source AND frt.tag = 'tnf' AND frt.subfield = 'a' LIMIT 1))"; - } elsif ($sort_filter eq 'pubdate') { - $rank = "FIRST(mrd.date1)::NUMERIC"; - } elsif ($sort_filter eq 'create_date') { - $rank = "FIRST((SELECT create_date FROM biblio.record_entry rbr WHERE rbr.id = m.source))"; - } elsif ($sort_filter eq 'edit_date') { - $rank = "FIRST((SELECT edit_date FROM biblio.record_entry rbr WHERE rbr.id = m.source))"; - } elsif ($sort_filter eq 'author') { - $rank = "FIRST((SELECT fra.value FROM metabib.full_rec fra WHERE fra.record = m.source AND fra.tag LIKE '1%' AND fra.subfield = 'a' ORDER BY fra.tag LIMIT 1))"; - } else { - # default to rel ranking - $rank = $rel; - } + # default to rel ranking + $rank = $rel; } my $key = 'm.source'; @@ -494,25 +560,25 @@ sub toSQL { my ($between) = $self->find_filter('between'); if ($before and @{$before->args} == 1) { - $before = "AND mrd.date1 <= " . $self->QueryParser->quote_value($before->args->[0]); + $before = "AND (mrd.attrs->'date1') <= " . $self->QueryParser->quote_value($before->args->[0]); } else { $before = ''; } if ($after and @{$after->args} == 1) { - $after = "AND mrd.date1 >= " . $self->QueryParser->quote_value($after->args->[0]); + $after = "AND (mrd.attrs->'date1') >= " . $self->QueryParser->quote_value($after->args->[0]); } else { $after = ''; } if ($during and @{$during->args} == 1) { - $during = "AND " . $self->QueryParser->quote_value($during->args->[0]) . " BETWEEN mrd.date1 AND mrd.date2"; + $during = "AND " . $self->QueryParser->quote_value($during->args->[0]) . " BETWEEN (mrd.attrs->'date1') AND (mrd.attrs->'date2')"; } else { $during = ''; } if ($between and @{$between->args} == 2) { - $between = "AND mrd.date1 BETWEEN " . $self->QueryParser->quote_value($between->args->[0]) . " AND " . $self->QueryParser->quote_value($between->args->[1]); + $between = "AND (mrd.attrs->'date1') BETWEEN " . $self->QueryParser->quote_value($between->args->[0]) . " AND " . $self->QueryParser->quote_value($between->args->[1]); } else { $between = ''; } @@ -524,22 +590,16 @@ SELECT $key AS id, ARRAY_ACCUM(DISTINCT m.source) AS records, $rel AS rel, $rank AS rank, - FIRST(mrd.date1) AS tie_break + FIRST(mrd.attrs->'date1') AS tie_break FROM metabib.metarecord_source_map m - JOIN metabib.rec_descriptor mrd ON (m.source = mrd.record) + JOIN metabib.record_attr mrd ON (m.source = mrd.id) $$flat_plan{from} WHERE 1=1 $before $after $during $between - $audience - $vr_format - $item_type - $item_form - $lit_form - $language - $bib_level + $combined_dyn_filters AND $$flat_plan{where} GROUP BY 1 ORDER BY 4 $desc NULLS LAST, 5 DESC NULLS LAST, 3 DESC @@ -828,8 +888,16 @@ sub tsquery { sub rank { my $self = shift; + + my $rank_norm_map = $self->plan->QueryParser->custom_data->{rank_cd_weight_map}; + + my $cover_density = 0; + for my $norm ( keys %$rank_norm_map) { + $cover_density += $$rank_norm_map{$norm} if ($self->plan->find_modifier($norm)); + } + return $self->{rank} if ($self->{rank}); - return $self->{rank} = 'rank(' . $self->table_alias . '.index_vector, ' . $self->table_alias . '.tsq)'; + return $self->{rank} = 'rank_cd(' . $self->table_alias . '.index_vector, ' . $self->table_alias . ".tsq, $cover_density)"; } diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/QueryParser.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/QueryParser.pm index 6338384..7792b49 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/QueryParser.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/QueryParser.pm @@ -207,6 +207,35 @@ sub query_normalizers { return $parser_config{$pkg}{normalizers}; } +sub add_filter_normalizer { + my $pkg = shift; + $pkg = ref($pkg) || $pkg; + my $filter = shift; + my $func = shift; + my $params = shift || []; + + return $func if (grep { $_ eq $func } @{$pkg->filter_normalizers->{$filter}}); + + push(@{$pkg->filter_normalizers->{$filter}}, { function => $func, params => $params }); + + return $func; +} + +sub filter_normalizers { + my $pkg = shift; + $pkg = ref($pkg) || $pkg; + + my $filter = shift; + + $parser_config{$pkg}{filter_normalizers} ||= {}; + if ($filter) { + $parser_config{$pkg}{filter_normalizers}{$filter} ||= []; + return $parser_config{$pkg}{filter_normalizers}{$filter}; + } + + return $parser_config{$pkg}{filter_normalizers}; +} + sub default_search_class { my $pkg = shift; $pkg = ref($pkg) || $pkg; diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 68bf85d..fd7773a 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -566,44 +566,6 @@ CREATE TABLE config.remote_account ( last_activity TIMESTAMP WITH TIME ZONE ); -CREATE TABLE config.audience_map ( - code TEXT PRIMARY KEY, - value TEXT NOT NULL, - description TEXT -); - -CREATE TABLE config.lit_form_map ( - code TEXT PRIMARY KEY, - value TEXT NOT NULL, - description TEXT -); - -CREATE TABLE config.language_map ( - code TEXT PRIMARY KEY, - value TEXT NOT NULL -); - -CREATE TABLE config.item_form_map ( - code TEXT PRIMARY KEY, - value TEXT NOT NULL -); - -CREATE TABLE config.item_type_map ( - code TEXT PRIMARY KEY, - value TEXT NOT NULL -); - -CREATE TABLE config.bib_level_map ( - code TEXT PRIMARY KEY, - value TEXT NOT NULL -); - -CREATE TABLE config.marc21_rec_type_map ( - code TEXT PRIMARY KEY, - type_val TEXT NOT NULL, - blvl_val TEXT NOT NULL -); - CREATE TABLE config.marc21_ff_pos_map ( id SERIAL PRIMARY KEY, fixed_field TEXT NOT NULL, @@ -675,7 +637,7 @@ CREATE TABLE config.z3950_attr ( CREATE TABLE config.i18n_locale ( code TEXT PRIMARY KEY, - marc_code TEXT NOT NULL REFERENCES config.language_map (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang' name TEXT UNIQUE NOT NULL, description TEXT ); @@ -866,6 +828,56 @@ CREATE TABLE config.metabib_field_index_norm_map ( pos INT NOT NULL DEFAULT 0 ); +CREATE TABLE config.record_attr_definition ( + name TEXT PRIMARY KEY, + label TEXT NOT NULL, -- I18N + filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true + sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true + +-- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering + tag TEXT, -- LIKE format + sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d + +-- This is used for both tag/sf and xpath entries + joiner TEXT, + +-- For xpath-extracted attrs + xpath TEXT, + format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + start_pos INT, + string_len INT, + +-- For fixed fields + fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field + +-- For phys-char fields + phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id) +); + +CREATE TABLE config.record_attr_index_norm_map ( + id SERIAL PRIMARY KEY, + attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + params TEXT, + pos INT NOT NULL DEFAULT 0 +); + +CREATE TABLE config.coded_value_map ( + id SERIAL PRIMARY KEY, + ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + code TEXT NOT NULL, + value TEXT NOT NULL, + description TEXT +); + +CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang'; +CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level'; +CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form'; +CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type'; +CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form'; +CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience'; +CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format'; + CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$ DECLARE normalizer RECORD; diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index 4064d06..42400f6 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -135,41 +135,38 @@ CREATE INDEX metabib_facet_entry_field_idx ON metabib.facet_entry (field); CREATE INDEX metabib_facet_entry_value_idx ON metabib.facet_entry (SUBSTRING(value,1,1024)); CREATE INDEX metabib_facet_entry_source_idx ON metabib.facet_entry (source); - -CREATE TABLE metabib.rec_descriptor ( - id BIGSERIAL PRIMARY KEY, - record BIGINT, - item_type TEXT, - item_form TEXT, - bib_level TEXT, - control_type TEXT, - char_encoding TEXT, - enc_level TEXT, - audience TEXT, - lit_form TEXT, - type_mat TEXT, - cat_form TEXT, - pub_status TEXT, - item_lang TEXT, - vr_format TEXT, - date1 TEXT, - date2 TEXT +CREATE TABLE metabib.record_attr ( + id BIGINT PRIMARY KEY REFERENCES biblio.record_entry (id) ON DELETE CASCADE, + attrs HSTORE NOT NULL DEFAULT ''::HSTORE +); +CREATE INDEX metabib_svf_attrs_idx ON metabib.record_attr USING GIST (attrs); +CREATE INDEX metabib_svf_date1_idx ON metabib.record_attr ((attrs->'date1')); +CREATE INDEX metabib_svf_dates_idx ON metabib.record_attr ((attrs->'date1'),(attrs->'date2')); + +-- Back-compat view ... we're moving to an HSTORE world +CREATE TYPE metabib.rec_desc_type AS ( + item_type TEXT, + item_form TEXT, + bib_level TEXT, + control_type TEXT, + char_encoding TEXT, + enc_level TEXT, + audience TEXT, + lit_form TEXT, + type_mat TEXT, + cat_form TEXT, + pub_status TEXT, + item_lang TEXT, + vr_format TEXT, + date1 TEXT, + date2 TEXT ); -CREATE INDEX metabib_rec_descriptor_record_idx ON metabib.rec_descriptor (record); -CREATE INDEX metabib_rec_descriptor_item_type_idx ON metabib.rec_descriptor (item_type); -CREATE INDEX metabib_rec_descriptor_item_form_idx ON metabib.rec_descriptor (item_form); -CREATE INDEX metabib_rec_descriptor_bib_level_idx ON metabib.rec_descriptor (bib_level); -CREATE INDEX metabib_rec_descriptor_control_type_idx ON metabib.rec_descriptor (control_type); -CREATE INDEX metabib_rec_descriptor_char_encoding_idx ON metabib.rec_descriptor (char_encoding); -CREATE INDEX metabib_rec_descriptor_enc_level_idx ON metabib.rec_descriptor (enc_level); -CREATE INDEX metabib_rec_descriptor_audience_idx ON metabib.rec_descriptor (audience); -CREATE INDEX metabib_rec_descriptor_lit_form_idx ON metabib.rec_descriptor (lit_form); -CREATE INDEX metabib_rec_descriptor_cat_form_idx ON metabib.rec_descriptor (cat_form); -CREATE INDEX metabib_rec_descriptor_pub_status_idx ON metabib.rec_descriptor (pub_status); -CREATE INDEX metabib_rec_descriptor_item_lang_idx ON metabib.rec_descriptor (item_lang); -CREATE INDEX metabib_rec_descriptor_vr_format_idx ON metabib.rec_descriptor (vr_format); -CREATE INDEX metabib_rec_descriptor_date1_idx ON metabib.rec_descriptor (date1); -CREATE INDEX metabib_rec_descriptor_dates_idx ON metabib.rec_descriptor (date1,date2); + +CREATE VIEW metabib.rec_descriptor AS + SELECT id, + id AS record, + (populate_record(NULL::metabib.rec_desc_type, attrs)).* + FROM metabib.record_attr; -- Use a sequence that matches previous version, for easier upgrading. CREATE SEQUENCE metabib.full_rec_id_seq; @@ -459,18 +456,18 @@ return undef; $func$ LANGUAGE PLPERLU; -CREATE OR REPLACE FUNCTION biblio.marc21_record_type( rid BIGINT ) RETURNS config.marc21_rec_type_map AS $func$ +CREATE OR REPLACE FUNCTION vandelay.marc21_record_type( marc TEXT ) RETURNS config.marc21_rec_type_map AS $func$ DECLARE - ldr RECORD; + ldr TEXT; tval TEXT; tval_rec RECORD; bval TEXT; bval_rec RECORD; retval config.marc21_rec_type_map%ROWTYPE; BEGIN - SELECT * INTO ldr FROM metabib.full_rec WHERE record = rid AND tag = 'LDR' LIMIT 1; + ldr := oils_xpath_string( '//*[local-name()="leader"]', marc ); - IF ldr.id IS NULL THEN + IF ldr IS NULL OR ldr = '' THEN SELECT * INTO retval FROM config.marc21_rec_type_map WHERE code = 'BKS'; RETURN retval; END IF; @@ -479,10 +476,10 @@ BEGIN SELECT * INTO bval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'BLvl' LIMIT 1; -- They're all the same - tval := SUBSTRING( ldr.value, tval_rec.start_pos + 1, tval_rec.length ); - bval := SUBSTRING( ldr.value, bval_rec.start_pos + 1, bval_rec.length ); + tval := SUBSTRING( ldr, tval_rec.start_pos + 1, tval_rec.length ); + bval := SUBSTRING( ldr, bval_rec.start_pos + 1, bval_rec.length ); - -- RAISE NOTICE 'type %, blvl %, ldr %', tval, bval, ldr.value; + -- RAISE NOTICE 'type %, blvl %, ldr %', tval, bval, ldr; SELECT * INTO retval FROM config.marc21_rec_type_map WHERE type_val LIKE '%' || tval || '%' AND blvl_val LIKE '%' || bval || '%'; @@ -495,16 +492,20 @@ BEGIN END; $func$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION biblio.marc21_extract_fixed_field( rid BIGINT, ff TEXT ) RETURNS TEXT AS $func$ +CREATE OR REPLACE FUNCTION biblio.marc21_record_type( rid BIGINT ) RETURNS config.marc21_rec_type_map AS $func$ + SELECT * FROM vandelay.marc21_record_type( (SELECT marc FROM biblio.record_entry WHERE id = $1) ); +$func$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION vandelay.marc21_extract_fixed_field( marc TEXT, ff TEXT ) RETURNS TEXT AS $func$ DECLARE rtype TEXT; ff_pos RECORD; tag_data RECORD; val TEXT; BEGIN - rtype := (biblio.marc21_record_type( rid )).code; + rtype := (vandelay.marc21_record_type( marc )).code; FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE fixed_field = ff AND rec_type = rtype ORDER BY tag DESC LOOP - FOR tag_data IN SELECT * FROM metabib.full_rec WHERE tag = UPPER(ff_pos.tag) AND record = rid LOOP + FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(tag) || '"]/text()', marc ) ) x(value) LOOP val := SUBSTRING( tag_data.value, ff_pos.start_pos + 1, ff_pos.length ); RETURN val; END LOOP; @@ -516,8 +517,43 @@ BEGIN END; $func$ LANGUAGE PLPGSQL; +CREATE OR REPLACE FUNCTION biblio.marc21_extract_fixed_field( rid BIGINT, ff TEXT ) RETURNS TEXT AS $func$ + SELECT * FROM vandelay.marc21_extract_fixed_field( (SELECT marc FROM biblio.record_entry WHERE id = $1), $2 ); +$func$ LANGUAGE SQL; + +CREATE TYPE biblio.record_ff_map AS (record BIGINT, ff_name TEXT, ff_value TEXT); +CREATE OR REPLACE FUNCTION vandelay.marc21_extract_all_fixed_fields( marc TEXT ) RETURNS SETOF biblio.record_ff_map AS $func$ +DECLARE + tag_data TEXT; + rtype TEXT; + ff_pos RECORD; + output biblio.record_ff_map%ROWTYPE; +BEGIN + rtype := (vandelay.marc21_record_type( marc )).code; + + FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE rec_type = rtype ORDER BY tag DESC LOOP + output.ff_name := ff_pos.fixed_field; + output.ff_value := NULL; + + FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(tag) || '"]/text()', marc ) ) x(value) LOOP + output.ff_value := SUBSTRING( tag_data.value, ff_pos.start_pos + 1, ff_pos.length ); + IF output.ff_value IS NULL THEN output.ff_value := REPEAT( ff_pos.default_val, ff_pos.length ); END IF; + RETURN NEXT output; + output.ff_value := NULL; + END LOOP; + + END LOOP; + + RETURN; +END; +$func$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION biblio.marc21_extract_all_fixed_fields( rid BIGINT ) RETURNS SETOF biblio.record_ff_map AS $func$ + SELECT $1 AS record, ff_name, ff_value FROM vandelay.marc21_extract_all_fixed_fields( (SELECT marc FROM biblio.record_entry WHERE id = $1) ); +$func$ LANGUAGE SQL; + CREATE TYPE biblio.marc21_physical_characteristics AS ( id INT, record BIGINT, ptype TEXT, subfield INT, value INT ); -CREATE OR REPLACE FUNCTION biblio.marc21_physical_characteristics( rid BIGINT ) RETURNS SETOF biblio.marc21_physical_characteristics AS $func$ +CREATE OR REPLACE FUNCTION vandelay.marc21_physical_characteristics( marc TEXT) RETURNS SETOF biblio.marc21_physical_characteristics AS $func$ DECLARE rowid INT := 0; _007 RECORD; @@ -527,19 +563,18 @@ DECLARE retval biblio.marc21_physical_characteristics%ROWTYPE; BEGIN - SELECT * INTO _007 FROM metabib.full_rec WHERE record = rid AND tag = '007' LIMIT 1; + _007 := oils_xpath_string( '//*[@tag="007"]', marc ); - IF _007.id IS NOT NULL THEN - SELECT * INTO ptype FROM config.marc21_physical_characteristic_type_map WHERE ptype_key = SUBSTRING( _007.value, 1, 1 ); + IF _007 IS NOT NULL AND _007 <> '' THEN + SELECT * INTO ptype FROM config.marc21_physical_characteristic_type_map WHERE ptype_key = SUBSTRING( _007, 1, 1 ); IF ptype.ptype_key IS NOT NULL THEN FOR psf IN SELECT * FROM config.marc21_physical_characteristic_subfield_map WHERE ptype_key = ptype.ptype_key LOOP - SELECT * INTO pval FROM config.marc21_physical_characteristic_value_map WHERE ptype_subfield = psf.id AND value = SUBSTRING( _007.value, psf.start_pos + 1, psf.length ); + SELECT * INTO pval FROM config.marc21_physical_characteristic_value_map WHERE ptype_subfield = psf.id AND value = SUBSTRING( _007, psf.start_pos + 1, psf.length ); IF pval.id IS NOT NULL THEN rowid := rowid + 1; retval.id := rowid; - retval.record := rid; retval.ptype := ptype.ptype_key; retval.subfield := psf.id; retval.value := pval.id; @@ -554,6 +589,10 @@ BEGIN END; $func$ LANGUAGE PLPGSQL; +CREATE OR REPLACE FUNCTION biblio.marc21_physical_characteristics( rid BIGINT ) RETURNS SETOF biblio.marc21_physical_characteristics AS $func$ + SELECT id, $1 AS record, ptype, subfield, value FROM vandelay.marc21_physical_characteristics( (SELECT marc FROM biblio.record_entry WHERE id = $1) ); +$func$ LANGUAGE SQL; + CREATE OR REPLACE FUNCTION biblio.extract_quality ( marc TEXT, best_lang TEXT, best_type TEXT ) RETURNS INT AS $func$ DECLARE qual INT; @@ -705,37 +744,6 @@ BEGIN END; $func$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION metabib.reingest_metabib_rec_descriptor( bib_id BIGINT ) RETURNS VOID AS $func$ -BEGIN - PERFORM * FROM config.internal_flag WHERE name = 'ingest.assume_inserts_only' AND enabled; - IF NOT FOUND THEN - DELETE FROM metabib.rec_descriptor WHERE record = bib_id; - END IF; - INSERT INTO metabib.rec_descriptor (record, item_type, item_form, bib_level, control_type, enc_level, audience, lit_form, type_mat, cat_form, pub_status, item_lang, vr_format, date1, date2) - SELECT bib_id, - biblio.marc21_extract_fixed_field( bib_id, 'Type' ), - biblio.marc21_extract_fixed_field( bib_id, 'Form' ), - biblio.marc21_extract_fixed_field( bib_id, 'BLvl' ), - biblio.marc21_extract_fixed_field( bib_id, 'Ctrl' ), - biblio.marc21_extract_fixed_field( bib_id, 'ELvl' ), - biblio.marc21_extract_fixed_field( bib_id, 'Audn' ), - biblio.marc21_extract_fixed_field( bib_id, 'LitF' ), - biblio.marc21_extract_fixed_field( bib_id, 'TMat' ), - biblio.marc21_extract_fixed_field( bib_id, 'Desc' ), - biblio.marc21_extract_fixed_field( bib_id, 'DtSt' ), - biblio.marc21_extract_fixed_field( bib_id, 'Lang' ), - ( SELECT v.value - FROM biblio.marc21_physical_characteristics( bib_id) p - JOIN config.marc21_physical_characteristic_subfield_map s ON (s.id = p.subfield) - JOIN config.marc21_physical_characteristic_value_map v ON (v.id = p.value) - WHERE p.ptype = 'v' AND s.subfield = 'e' ), - LPAD(NULLIF(REGEXP_REPLACE(NULLIF(biblio.marc21_extract_fixed_field( bib_id, 'Date1'), ''), E'\\D', '0', 'g')::INT,0)::TEXT,4,'0'), - LPAD(NULLIF(REGEXP_REPLACE(NULLIF(biblio.marc21_extract_fixed_field( bib_id, 'Date2'), ''), E'\\D', '9', 'g')::INT,9999)::TEXT,4,'0'); - - RETURN; -END; -$func$ LANGUAGE PLPGSQL; - CREATE OR REPLACE FUNCTION metabib.reingest_metabib_full_rec( bib_id BIGINT ) RETURNS VOID AS $func$ BEGIN PERFORM * FROM config.internal_flag WHERE name = 'ingest.assume_inserts_only' AND enabled; @@ -917,10 +925,19 @@ $func$ LANGUAGE SQL; -- AFTER UPDATE OR INSERT trigger for biblio.record_entry CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$ +DECLARE + transformed_xml TEXT; + prev_xfrm TEXT; + normalizer RECORD; + xfrm config.xml_transform%ROWTYPE; + attr_value TEXT; + new_attrs HSTORE := ''::HSTORE; + attr_def config.record_attr_definition%ROWTYPE; BEGIN IF NEW.deleted IS TRUE THEN -- If this bib is deleted DELETE FROM metabib.metarecord_source_map WHERE source = NEW.id; -- Rid ourselves of the search-estimate-killing linkage + DELETE FROM metabib.record_attr WHERE id = NEW.id; -- Kill the attrs hash, useless on deleted records DELETE FROM authority.bib_linking WHERE bib = NEW.id; -- Avoid updating fields in bibs that are no longer visible RETURN NEW; -- and we're done END IF; @@ -943,9 +960,92 @@ BEGIN PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_full_rec' AND enabled; IF NOT FOUND THEN PERFORM metabib.reingest_metabib_full_rec(NEW.id); + + -- Now we pull out attribute data, which is dependent on the mfr for all but XPath-based fields PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_rec_descriptor' AND enabled; IF NOT FOUND THEN - PERFORM metabib.reingest_metabib_rec_descriptor(NEW.id); + FOR attr_def IN SELECT * FROM config.record_attr_definition ORDER BY format LOOP + + IF attr_def.tag THEN -- tag (and optional subfield list) selection + SELECT ARRAY_TO_STRING(ARRAY_ACCUM(value), COALESCE(attr_def.joiner,' ')) INTO attr_value + FROM (SELECT * FROM metabib.full_rec ORDER BY tag, subfield) AS x + WHERE record = NEW.id + AND tag LIKE attr_def.tag + AND CASE + WHEN attr_def.sf_list IS NOT NULL + THEN POSITION(subfield IN attr_def.sf_list) > 0 + ELSE TRUE + END + GROUP BY tag + ORDER BY tag + LIMIT 1; + + ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field + attr_value := biblio.marc21_extract_fixed_field(NEW.id, attr_def.fixed_field); + + ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression + + SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format; + + -- See if we can skip the XSLT ... it's expensive + IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN + -- Can't skip the transform + IF xfrm.xslt <> '---' THEN + transformed_xml := oils_xslt_process(NEW.marc,xfrm.xslt); + ELSE + transformed_xml := NEW.marc; + END IF; + + prev_xfrm := xfrm.name; + END IF; + + IF xfrm.name IS NULL THEN + -- just grab the marcxml (empty) transform + SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1; + prev_xfrm := xfrm.name; + END IF; + + attr_value := oils_xpath_string(attr_def.xpath, transformed_xml, COALESCE(attr_def.joiner,' '), ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]); + + ELSIF attr_def.phys_char IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map + SELECT value::TEXT INTO attr_value + FROM biblio.marc21_physical_characteristics(NEW.id) + WHERE subfield = attr_def.phys_char_sf + LIMIT 1; -- Just in case ... + + END IF; + + -- apply index normalizers to attr_value + FOR normalizer IN + SELECT n.func AS func, + n.param_count AS param_count, + m.params AS params + FROM config.index_normalizer n + JOIN config.record_attr_index_norm_map m ON (m.norm = n.id) + WHERE attr = attr_def.name + ORDER BY m.pos LOOP + EXECUTE 'SELECT ' || normalizer.func || '(' || + quote_literal( attr_value ) || + CASE + WHEN normalizer.param_count > 0 + THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'') + ELSE '' + END || + ')' INTO attr_value; + + END LOOP; + + -- Add the new value to the hstore + new_attrs := new_attrs || hstore( attr_def.name, attr_value ); + + END LOOP; + + IF TG_OP = 'INSERT' OR OLD.deleted THEN -- initial insert OR revivication + INSERT INTO metabib.record_attr (id, attrs) VALUES (NEW.id, new_attrs); + ELSE + UPDATE metabib.record_attr SET attrs = attrs || new_attrs WHERE id = NEW.id; + END IF; + END IF; END IF; diff --git a/Open-ILS/src/sql/Pg/040.schema.asset.sql b/Open-ILS/src/sql/Pg/040.schema.asset.sql index c7c1448..18357ef 100644 --- a/Open-ILS/src/sql/Pg/040.schema.asset.sql +++ b/Open-ILS/src/sql/Pg/040.schema.asset.sql @@ -363,33 +363,6 @@ CREATE TABLE asset.copy_template ( mint_condition BOOL ); -CREATE VIEW stats.fleshed_copy AS - SELECT cp.*, - CAST(cp.create_date AS DATE) AS create_date_day, - CAST(cp.edit_date AS DATE) AS edit_date_day, - DATE_TRUNC('hour', cp.create_date) AS create_date_hour, - DATE_TRUNC('hour', cp.edit_date) AS edit_date_hour, - cn.label AS call_number_label, - cn.owning_lib, - rd.item_lang, - rd.item_type, - rd.item_form - FROM asset.copy cp - JOIN asset.call_number cn ON (cp.call_number = cn.id) - JOIN metabib.rec_descriptor rd ON (rd.record = cn.record); - -CREATE VIEW stats.fleshed_call_number AS - SELECT cn.*, - CAST(cn.create_date AS DATE) AS create_date_day, - CAST(cn.edit_date AS DATE) AS edit_date_day, - DATE_TRUNC('hour', cn.create_date) AS create_date_hour, - DATE_TRUNC('hour', cn.edit_date) AS edit_date_hour, - rd.item_lang, - rd.item_type, - rd.item_form - FROM asset.call_number cn - JOIN metabib.rec_descriptor rd ON (rd.record = cn.record); - CREATE OR REPLACE FUNCTION asset.opac_ou_record_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ DECLARE ans RECORD; diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index fc86d8a..cb7391a 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -290,21 +290,6 @@ CREATE OR REPLACE VIEW action.billable_circulations AS FROM action.circulation WHERE xact_finish IS NULL; -CREATE VIEW stats.fleshed_circulation AS - SELECT c.*, - CAST(c.xact_start AS DATE) AS start_date_day, - CAST(c.xact_finish AS DATE) AS finish_date_day, - DATE_TRUNC('hour', c.xact_start) AS start_date_hour, - DATE_TRUNC('hour', c.xact_finish) AS finish_date_hour, - cp.call_number_label, - cp.owning_lib, - cp.item_lang, - cp.item_type, - cp.item_form - FROM "action".circulation c - JOIN stats.fleshed_copy cp ON (cp.id = c.target_copy); - - CREATE OR REPLACE FUNCTION action.circulation_claims_returned () RETURNS TRIGGER AS $$ BEGIN IF OLD.stop_fines IS NULL OR OLD.stop_fines <> NEW.stop_fines THEN diff --git a/Open-ILS/src/sql/Pg/100.circ_matrix.sql b/Open-ILS/src/sql/Pg/100.circ_matrix.sql index e0f3b3d..ca268cb 100644 --- a/Open-ILS/src/sql/Pg/100.circ_matrix.sql +++ b/Open-ILS/src/sql/Pg/100.circ_matrix.sql @@ -83,9 +83,9 @@ CREATE TABLE config.circ_matrix_matchpoint ( org_unit INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top OU for the matchpoint applicability range; we can use org_unit_prox to choose the "best" grp INT NOT NULL REFERENCES permission.grp_tree (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top applicable group from the group tree; will need descendents and prox functions for filtering circ_modifier TEXT REFERENCES config.circ_modifier (code) DEFERRABLE INITIALLY DEFERRED, - marc_type TEXT REFERENCES config.item_type_map (code) DEFERRABLE INITIALLY DEFERRED, - marc_form TEXT REFERENCES config.item_form_map (code) DEFERRABLE INITIALLY DEFERRED, - marc_vr_format TEXT REFERENCES config.videorecording_format_map (code) DEFERRABLE INITIALLY DEFERRED, + marc_type TEXT, + marc_form TEXT, + marc_vr_format TEXT, copy_circ_lib INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, copy_owning_lib INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, user_home_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, diff --git a/Open-ILS/src/sql/Pg/110.hold_matrix.sql b/Open-ILS/src/sql/Pg/110.hold_matrix.sql index a025979..3b954af 100644 --- a/Open-ILS/src/sql/Pg/110.hold_matrix.sql +++ b/Open-ILS/src/sql/Pg/110.hold_matrix.sql @@ -41,9 +41,9 @@ CREATE TABLE config.hold_matrix_matchpoint ( usr_grp INT REFERENCES permission.grp_tree (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top applicable group from the group tree; will need descendents and prox functions for filtering requestor_grp INT NOT NULL REFERENCES permission.grp_tree (id) DEFERRABLE INITIALLY DEFERRED, -- Set to the top applicable group from the group tree; will need descendents and prox functions for filtering circ_modifier TEXT REFERENCES config.circ_modifier (code) DEFERRABLE INITIALLY DEFERRED, - marc_type TEXT REFERENCES config.item_type_map (code) DEFERRABLE INITIALLY DEFERRED, - marc_form TEXT REFERENCES config.item_form_map (code) DEFERRABLE INITIALLY DEFERRED, - marc_vr_format TEXT REFERENCES config.videorecording_format_map (code) DEFERRABLE INITIALLY DEFERRED, + marc_type TEXT, + marc_form TEXT, + marc_vr_format TEXT, juvenile_flag BOOL, ref_flag BOOL, -- "Result" Fields diff --git a/Open-ILS/src/sql/Pg/800.fkeys.sql b/Open-ILS/src/sql/Pg/800.fkeys.sql index 346ed98..619441d 100644 --- a/Open-ILS/src/sql/Pg/800.fkeys.sql +++ b/Open-ILS/src/sql/Pg/800.fkeys.sql @@ -69,8 +69,6 @@ ALTER TABLE metabib.keyword_field_entry ADD CONSTRAINT metabib_keyword_field_ent 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; ALTER TABLE metabib.metarecord_source_map ADD CONSTRAINT metabib_metarecord_source_map_source_fkey FOREIGN KEY (source) 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 d330929..cc6b98b 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -26,6 +26,36 @@ INSERT INTO config.xml_transform VALUES ( 'mods32', 'http://www.loc.gov/mods/v3' INSERT INTO config.xml_transform VALUES ( 'mods33', 'http://www.loc.gov/mods/v3', 'mods33', ''); INSERT INTO config.xml_transform VALUES ( 'marc21expand880', 'http://www.loc.gov/MARC21/slim', 'marc', '' ); +-- record attributes +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('alph','Alph','Alph'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('audience','Audn','Audn'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('bib_level','BLvl','BLvl'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('biog','Biog','Biog'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('conf','Conf','Conf'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('control_type','Ctrl','Ctrl'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('ctry','Ctry','Ctry'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('date1','Date1','Date1'); +INSERT INTO config.record_attr_definition (name,label,fixed_field,sorter,filter) values ('pubdate','Pub Date','Date1',TRUE,FALSE); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('date2','Date2','Date2'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('cat_form','Desc','Desc'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('pub_status','DtSt','DtSt'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('enc_level','ELvl','ELvl'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('fest','Fest','Fest'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('item_form','Form','Form'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('gpub','GPub','GPub'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('ills','Ills','Ills'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('indx','Indx','Indx'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('item_lang','Lang','Lang'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('lit_form','LitF','LitF'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('mrec','MRec','MRec'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('ff_sl','S/L','S/L'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('type_mat','TMat','TMat'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('item_type','Type','Type'); +INSERT INTO config.record_attr_definition (name,label,phys_char_sf) values ('vr_format','Videorecording format',72); +INSERT INTO config.record_attr_definition (name,label,sorter,filter,tag) values ('titlesort','Title',TRUE,FALSE,'tnf'); +INSERT INTO config.record_attr_definition (name,label,sorter,filter,tag) values ('authorsort','Author',TRUE,FALSE,'1%'); + +-- Index Definitions INSERT INTO config.metabib_field ( id, field_class, name, label, format, xpath, facet_field ) VALUES (1, 'series', 'seriestitle', oils_i18n_gettext(1, 'Series Title', 'cmf', 'label'), 'mods32', $$//mods32:mods/mods32:relatedItem[@type="series"]/mods32:titleInfo$$, TRUE ); diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.svf-phase-one.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.svf-phase-one.sql new file mode 100644 index 0000000..c2294fb --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.svf-phase-one.sql @@ -0,0 +1,452 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('XXXX'); -- miker + +CREATE TABLE config.record_attr_definition ( + name TEXT PRIMARY KEY, + label TEXT NOT NULL, -- I18N + filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true + sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true + +-- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering + tag TEXT, -- LIKE format + sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d + +-- This is used for both tag/sf and xpath entries + joiner TEXT, + +-- For xpath-extracted attrs + xpath TEXT, + format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + start_pos INT, + string_len INT, + +-- For fixed fields + fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field + +-- For phys-char fields + phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id) +); + +CREATE TABLE config.record_attr_index_norm_map ( + id SERIAL PRIMARY KEY, + attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + params TEXT, + pos INT NOT NULL DEFAULT 0 +); + +CREATE TABLE config.coded_value_map ( + id SERIAL PRIMARY KEY, + ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + code TEXT NOT NULL, + value TEXT NOT NULL, + description TEXT +); + +-- record attributes +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('alph','Alph','Alph'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('audience','Audn','Audn'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('bib_level','BLvl','BLvl'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('biog','Biog','Biog'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('conf','Conf','Conf'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('control_type','Ctrl','Ctrl'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('ctry','Ctry','Ctry'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('date1','Date1','Date1'); +INSERT INTO config.record_attr_definition (name,label,fixed_field,sorter,filter) values ('pubdate','Pub Date','Date1',TRUE,FALSE); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('date2','Date2','Date2'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('cat_form','Desc','Desc'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('pub_status','DtSt','DtSt'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('enc_level','ELvl','ELvl'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('fest','Fest','Fest'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('item_form','Form','Form'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('gpub','GPub','GPub'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('ills','Ills','Ills'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('indx','Indx','Indx'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('item_lang','Lang','Lang'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('lit_form','LitF','LitF'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('mrec','MRec','MRec'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('ff_sl','S/L','S/L'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('type_mat','TMat','TMat'); +INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('item_type','Type','Type'); +INSERT INTO config.record_attr_definition (name,label,phys_char_sf) values ('vr_format','Videorecording format',72); +INSERT INTO config.record_attr_definition (name,label,sorter,filter,tag) values ('titlesort','Title',TRUE,FALSE,'tnf'); +INSERT INTO config.record_attr_definition (name,label,sorter,filter,tag) values ('authorsort','Author',TRUE,FALSE,'1%'); + +INSERT INTO config.coded_value_map (ctype,code,value,description) + SELECT 'item_lang' AS ctype, code, value, NULL FROM config.language_map + UNION + SELECT 'bib_level' AS ctype, code, value, NULL FROM config.bib_level_map + UNION + SELECT 'item_form' AS ctype, code, value, NULL FROM config.item_form_map + UNION + SELECT 'item_type' AS ctype, code, value, NULL FROM config.item_type_map + UNION + SELECT 'lit_form' AS ctype, code, value, description FROM config.lit_form_map + UNION + SELECT 'audience' AS ctype, code, value, description FROM config.audience_map + UNION + SELECT 'vr_format' AS ctype, code, value, NULL FROM config.videorecording_format_map; + +ALTER TABLE config.i18n_locale DROP CONSTRAINT i18n_locale_marc_code_fkey; + +ALTER TABLE config.circ_matrix_matchpoint DROP CONSTRAINT circ_matrix_matchpoint_marc_form_fkey; +ALTER TABLE config.circ_matrix_matchpoint DROP CONSTRAINT circ_matrix_matchpoint_marc_type_fkey; +ALTER TABLE config.circ_matrix_matchpoint DROP CONSTRAINT circ_matrix_matchpoint_marc_vr_format_fkey; + +ALTER TABLE config.hold_matrix_matchpoint DROP CONSTRAINT hold_matrix_matchpoint_marc_form_fkey; +ALTER TABLE config.hold_matrix_matchpoint DROP CONSTRAINT hold_matrix_matchpoint_marc_type_fkey; +ALTER TABLE config.hold_matrix_matchpoint DROP CONSTRAINT hold_matrix_matchpoint_marc_vr_format_fkey; + +DROP TABLE config.language_map; +DROP TABLE config.bib_level_map; +DROP TABLE config.item_form_map; +DROP TABLE config.item_type_map; +DROP TABLE config.lit_form_map; +DROP TABLE config.audience_map; +DROP TABLE config.videorecording_format_map; + +CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang'; +CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level'; +CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form'; +CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type'; +CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form'; +CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience'; +CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format'; + +CREATE TABLE metabib.record_attr ( + id BIGINT PRIMARY KEY REFERENCES biblio.record_entry (id) ON DELETE CASCADE, + attrs HSTORE NOT NULL DEFAULT ''::HSTORE +); +CREATE INDEX metabib_svf_attrs_idx ON metabib.record_attr USING GIST (attrs); +CREATE INDEX metabib_svf_date1_idx ON metabib.record_attr ( (attrs->'date1') ); +CREATE INDEX metabib_svf_dates_idx ON metabib.record_attr ( (attrs->'date1'), (attrs->'date2') ); + +INSERT INTO metabib.record_attr (id,attrs) + SELECT mrd.record, hstore(mrd) - '{id,record}'::TEXT[] FROM metabib.rec_descriptor mrd; + +-- Back-compat view ... we're moving to an HSTORE world +CREATE TYPE metabib.rec_desc_type AS ( + item_type TEXT, + item_form TEXT, + bib_level TEXT, + control_type TEXT, + char_encoding TEXT, + enc_level TEXT, + audience TEXT, + lit_form TEXT, + type_mat TEXT, + cat_form TEXT, + pub_status TEXT, + item_lang TEXT, + vr_format TEXT, + date1 TEXT, + date2 TEXT +); + +DROP TABLE metabib.rec_descriptor CASCADE; + +CREATE VIEW metabib.rec_descriptor AS + SELECT id, + id AS record, + (populate_record(NULL::metabib.rec_desc_type, attrs)).* + FROM metabib.record_attr; + +CREATE OR REPLACE FUNCTION vandelay.marc21_record_type( marc TEXT ) RETURNS config.marc21_rec_type_map AS $func$ +DECLARE + ldr TEXT; + tval TEXT; + tval_rec RECORD; + bval TEXT; + bval_rec RECORD; + retval config.marc21_rec_type_map%ROWTYPE; +BEGIN + ldr := oils_xpath_string( '//*[local-name()="leader"]', marc ); + + IF ldr IS NULL OR ldr = '' THEN + SELECT * INTO retval FROM config.marc21_rec_type_map WHERE code = 'BKS'; + RETURN retval; + END IF; + + SELECT * INTO tval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'Type' LIMIT 1; -- They're all the same + SELECT * INTO bval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'BLvl' LIMIT 1; -- They're all the same + + + tval := SUBSTRING( ldr, tval_rec.start_pos + 1, tval_rec.length ); + bval := SUBSTRING( ldr, bval_rec.start_pos + 1, bval_rec.length ); + + -- RAISE NOTICE 'type %, blvl %, ldr %', tval, bval, ldr; + + SELECT * INTO retval FROM config.marc21_rec_type_map WHERE type_val LIKE '%' || tval || '%' AND blvl_val LIKE '%' || bval || '%'; + + + IF retval.code IS NULL THEN + SELECT * INTO retval FROM config.marc21_rec_type_map WHERE code = 'BKS'; + END IF; + + RETURN retval; +END; +$func$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION biblio.marc21_record_type( rid BIGINT ) RETURNS config.marc21_rec_type_map AS $func$ + SELECT * FROM vandelay.marc21_record_type( (SELECT marc FROM biblio.record_entry WHERE id = $1) ); +$func$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION vandelay.marc21_extract_fixed_field( marc TEXT, ff TEXT ) RETURNS TEXT AS $func$ +DECLARE + rtype TEXT; + ff_pos RECORD; + tag_data RECORD; + val TEXT; +BEGIN + rtype := (vandelay.marc21_record_type( marc )).code; + FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE fixed_field = ff AND rec_type = rtype ORDER BY tag DESC LOOP + FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(tag) || '"]/text()', marc ) ) x(value) LOOP + val := SUBSTRING( tag_data.value, ff_pos.start_pos + 1, ff_pos.length ); + RETURN val; + END LOOP; + val := REPEAT( ff_pos.default_val, ff_pos.length ); + RETURN val; + END LOOP; + + RETURN NULL; +END; +$func$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION biblio.marc21_extract_fixed_field( rid BIGINT, ff TEXT ) RETURNS TEXT AS $func$ + SELECT * FROM vandelay.marc21_extract_fixed_field( (SELECT marc FROM biblio.record_entry WHERE id = $1), $2 ); +$func$ LANGUAGE SQL; + +CREATE TYPE biblio.record_ff_map AS (record BIGINT, ff_name TEXT, ff_value TEXT); +CREATE OR REPLACE FUNCTION vandelay.marc21_extract_all_fixed_fields( marc TEXT ) RETURNS SETOF biblio.record_ff_map AS $func$ +DECLARE + tag_data TEXT; + rtype TEXT; + ff_pos RECORD; + output biblio.record_ff_map%ROWTYPE; +BEGIN + rtype := (vandelay.marc21_record_type( marc )).code; + + FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE rec_type = rtype ORDER BY tag DESC LOOP + output.ff_name := ff_pos.fixed_field; + output.ff_value := NULL; + + FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(tag) || '"]/text()', marc ) ) x(value) LOOP + output.ff_value := SUBSTRING( tag_data.value, ff_pos.start_pos + 1, ff_pos.length ); + IF output.ff_value IS NULL THEN output.ff_value := REPEAT( ff_pos.default_val, ff_pos.length ); END IF; + RETURN NEXT output; + output.ff_value := NULL; + END LOOP; + + END LOOP; + + RETURN; +END; +$func$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION biblio.marc21_extract_all_fixed_fields( rid BIGINT ) RETURNS SETOF biblio.record_ff_map AS $func$ + SELECT $1 AS record, ff_name, ff_value FROM vandelay.marc21_extract_all_fixed_fields( (SELECT marc FROM biblio.record_entry WHERE id = $1) ); +$func$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION vandelay.marc21_physical_characteristics( marc TEXT) RETURNS SETOF biblio.marc21_physical_characteristics AS $func$ +DECLARE + rowid INT := 0; + _007 RECORD; + ptype config.marc21_physical_characteristic_type_map%ROWTYPE; + psf config.marc21_physical_characteristic_subfield_map%ROWTYPE; + pval config.marc21_physical_characteristic_value_map%ROWTYPE; + retval biblio.marc21_physical_characteristics%ROWTYPE; +BEGIN + + _007 := oils_xpath_string( '//*[@tag="007"]', marc ); + + IF _007 IS NOT NULL AND _007 <> '' THEN + SELECT * INTO ptype FROM config.marc21_physical_characteristic_type_map WHERE ptype_key = SUBSTRING( _007, 1, 1 ); + + IF ptype.ptype_key IS NOT NULL THEN + FOR psf IN SELECT * FROM config.marc21_physical_characteristic_subfield_map WHERE ptype_key = ptype.ptype_key LOOP + SELECT * INTO pval FROM config.marc21_physical_characteristic_value_map WHERE ptype_subfield = psf.id AND value = SUBSTRING( _007, psf.start_pos + 1, psf.length ); + + IF pval.id IS NOT NULL THEN + rowid := rowid + 1; + retval.id := rowid; + retval.ptype := ptype.ptype_key; + retval.subfield := psf.id; + retval.value := pval.id; + RETURN NEXT retval; + END IF; + + END LOOP; + END IF; + END IF; + + RETURN; +END; +$func$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION biblio.marc21_physical_characteristics( rid BIGINT ) RETURNS SETOF biblio.marc21_physical_characteristics AS $func$ + SELECT id, $1 AS record, ptype, subfield, value FROM vandelay.marc21_physical_characteristics( (SELECT marc FROM biblio.record_entry WHERE id = $1) ); +$func$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$ +DECLARE + transformed_xml TEXT; + prev_xfrm TEXT; + normalizer RECORD; + xfrm config.xml_transform%ROWTYPE; + attr_value TEXT; + new_attrs HSTORE := ''::HSTORE; + attr_def config.record_attr_definition%ROWTYPE; +BEGIN + + IF NEW.deleted IS TRUE THEN -- If this bib is deleted + DELETE FROM metabib.metarecord_source_map WHERE source = NEW.id; -- Rid ourselves of the search-estimate-killing linkage + DELETE FROM metabib.record_attr WHERE id = NEW.id; -- Kill the attrs hash, useless on deleted records + DELETE FROM authority.bib_linking WHERE bib = NEW.id; -- Avoid updating fields in bibs that are no longer visible + RETURN NEW; -- and we're done + END IF; + + IF TG_OP = 'UPDATE' THEN -- re-ingest? + PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled; + + IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change + RETURN NEW; + END IF; + END IF; + + -- Record authority linking + PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_linking' AND enabled; + IF NOT FOUND THEN + PERFORM biblio.map_authority_linking( NEW.id, NEW.marc ); + END IF; + + -- Flatten and insert the mfr data + PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_full_rec' AND enabled; + IF NOT FOUND THEN + PERFORM metabib.reingest_metabib_full_rec(NEW.id); + + -- Now we pull out attribute data, which is dependent on the mfr for all but XPath-based fields + PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_rec_descriptor' AND enabled; + IF NOT FOUND THEN + FOR attr_def IN SELECT * FROM config.record_attr_definition ORDER BY format LOOP + + IF attr_def.tag THEN -- tag (and optional subfield list) selection + SELECT ARRAY_TO_STRING(ARRAY_ACCUM(value), COALESCE(attr_def.joiner,' ')) INTO attr_value + FROM (SELECT * FROM metabib.full_rec ORDER BY tag, subfield) AS x + WHERE record = NEW.id + AND tag LIKE attr_def.tag + AND CASE + WHEN attr_def.sf_list IS NOT NULL + THEN POSITION(subfield IN attr_def.sf_list) > 0 + ELSE TRUE + END + GROUP BY tag + ORDER BY tag + LIMIT 1; + + ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field + attr_value := biblio.marc21_extract_fixed_field(NEW.id, attr_def.fixed_field); + + ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression + + SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format; + + -- See if we can skip the XSLT ... it's expensive + IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN + -- Can't skip the transform + IF xfrm.xslt <> '---' THEN + transformed_xml := oils_xslt_process(NEW.marc,xfrm.xslt); + ELSE + transformed_xml := NEW.marc; + END IF; + + prev_xfrm := xfrm.name; + END IF; + + IF xfrm.name IS NULL THEN + -- just grab the marcxml (empty) transform + SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1; + prev_xfrm := xfrm.name; + END IF; + + attr_value := oils_xpath_string(attr_def.xpath, transformed_xml, COALESCE(attr_def.joiner,' '), ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]); + + ELSIF attr_def.phys_char IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map + SELECT value::TEXT INTO attr_value + FROM biblio.marc21_physical_characteristics(NEW.id) + WHERE subfield = attr_def.phys_char_sf + LIMIT 1; -- Just in case ... + + END IF; + + -- apply index normalizers to attr_value + FOR normalizer IN + SELECT n.func AS func, + n.param_count AS param_count, + m.params AS params + FROM config.index_normalizer n + JOIN config.record_attr_index_norm_map m ON (m.norm = n.id) + WHERE attr = attr_def.name + ORDER BY m.pos LOOP + EXECUTE 'SELECT ' || normalizer.func || '(' || + quote_literal( attr_value ) || + CASE + WHEN normalizer.param_count > 0 + THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'') + ELSE '' + END || + ')' INTO attr_value; + + END LOOP; + + -- Add the new value to the hstore + new_attrs := new_attrs || hstore( attr_def.name, attr_value ); + + END LOOP; + + IF TG_OP = 'INSERT' OR OLD.deleted THEN -- initial insert OR revivication + INSERT INTO metabib.record_attr (id, attrs) VALUES (NEW.id, new_attrs); + ELSE + UPDATE metabib.record_attr SET attrs = attrs || new_attrs WHERE id = NEW.id; + END IF; + + END IF; + END IF; + + -- Gather and insert the field entry data + PERFORM metabib.reingest_metabib_field_entries(NEW.id); + + -- Located URI magic + IF TG_OP = 'INSERT' THEN + PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled; + IF NOT FOUND THEN + PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor ); + END IF; + ELSE + PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled; + IF NOT FOUND THEN + PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor ); + END IF; + END IF; + + -- (re)map metarecord-bib linking + IF TG_OP = 'INSERT' THEN -- if not deleted and performing an insert, check for the flag + PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_insert' AND enabled; + IF NOT FOUND THEN + PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint ); + END IF; + ELSE -- we're doing an update, and we're not deleted, remap + PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_update' AND enabled; + IF NOT FOUND THEN + PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint ); + END IF; + END IF; + + RETURN NEW; +END; +$func$ LANGUAGE PLPGSQL; + +DROP FUNCTION metabib.reingest_metabib_rec_descriptor( bib_id BIGINT ); + +ROLLBACK; + -- 1.7.2.5