From 334a1a03414f0a1910a4d4bf1b53731f0ca3b22c Mon Sep 17 00:00:00 2001 From: miker Date: Mon, 7 Mar 2011 16:03:48 +0000 Subject: [PATCH] moving the upgrade script into place git-svn-id: svn://svn.open-ils.org/ILS/trunk@19624 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- .../sql/Pg/upgrade/0495.schema.svf-phase-one.sql | 519 ++++++++++++++++++++ .../sql/Pg/upgrade/XXXX.schema.svf-phase-one.sql | 519 -------------------- 2 files changed, 519 insertions(+), 519 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0495.schema.svf-phase-one.sql delete mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.svf-phase-one.sql diff --git a/Open-ILS/src/sql/Pg/upgrade/0495.schema.svf-phase-one.sql b/Open-ILS/src/sql/Pg/upgrade/0495.schema.svf-phase-one.sql new file mode 100644 index 0000000..281dfe1 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0495.schema.svf-phase-one.sql @@ -0,0 +1,519 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0495'); -- miker + +CREATE TABLE config.record_attr_definition ( + name TEXT PRIMARY KEY, + label TEXT NOT NULL, -- I18N + description TEXT, + 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; + +UPDATE config.i18n_core SET fq_field = 'ccvm.value', identity_value = ccvm.id FROM config.coded_value_map AS ccvm WHERE fq_field = 'clm.value' AND ccvm.ctype = 'item_lang' AND identity_value = ccvm.code; +UPDATE config.i18n_core SET fq_field = 'ccvm.value', identity_value = ccvm.id FROM config.coded_value_map AS ccvm WHERE fq_field = 'cblvl.value' AND ccvm.ctype = 'bib_level' AND identity_value = ccvm.code; +UPDATE config.i18n_core SET fq_field = 'ccvm.value', identity_value = ccvm.id FROM config.coded_value_map AS ccvm WHERE fq_field = 'cifm.value' AND ccvm.ctype = 'item_form' AND identity_value = ccvm.code; +UPDATE config.i18n_core SET fq_field = 'ccvm.value', identity_value = ccvm.id FROM config.coded_value_map AS ccvm WHERE fq_field = 'citm.value' AND ccvm.ctype = 'item_type' AND identity_value = ccvm.code; +UPDATE config.i18n_core SET fq_field = 'ccvm.value', identity_value = ccvm.id FROM config.coded_value_map AS ccvm WHERE fq_field = 'clfm.value' AND ccvm.ctype = 'lit_form' AND identity_value = ccvm.code; +UPDATE config.i18n_core SET fq_field = 'ccvm.value', identity_value = ccvm.id FROM config.coded_value_map AS ccvm WHERE fq_field = 'cam.value' AND ccvm.ctype = 'audience' AND identity_value = ccvm.code; +UPDATE config.i18n_core SET fq_field = 'ccvm.value', identity_value = ccvm.id FROM config.coded_value_map AS ccvm WHERE fq_field = 'cvrfm.value' AND ccvm.ctype = 'vr_format' AND identity_value = ccvm.code; + +UPDATE config.i18n_core SET fq_field = 'ccvm.description', identity_value = ccvm.id FROM config.coded_value_map AS ccvm WHERE fq_field = 'clfm.description' AND ccvm.ctype = 'lit_form' AND identity_value = ccvm.code; +UPDATE config.i18n_core SET fq_field = 'ccvm.description', identity_value = ccvm.id FROM config.coded_value_map AS ccvm WHERE fq_field = 'cam.description' AND ccvm.ctype = 'audience' AND identity_value = ccvm.code; + +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(ff_pos.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 TEXT; + 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 IS NOT NULL 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_sf 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 ); + +CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$ + SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' ); +$func$ LANGUAGE SQL STRICT IMMUTABLE; + +CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$ + SELECT approximate_date( $1, '0'); +$func$ LANGUAGE SQL STRICT IMMUTABLE; + +CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$ + SELECT approximate_date( $1, '9'); +$func$ LANGUAGE SQL STRICT IMMUTABLE; + +CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$ + SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END +$func$ LANGUAGE SQL STRICT IMMUTABLE; + +CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$ + SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END +$func$ LANGUAGE SQL STRICT IMMUTABLE; + +CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$ + use Business::ISBN; + use strict; + use warnings; + + # Find the first ISBN, force it to ISBN13 and return it + + my $input = shift; + + foreach my $word (split(/\s/, $input)) { + my $isbn = Business::ISBN->new($word); + + # First check the checksum; if it is not valid, fix it and add the original + # bad-checksum ISBN to the output + if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) { + $isbn->fix_checksum(); + } + + # If we now have a valid ISBN, force it to ISBN13 and return it + return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid()); + } + return undef; +$func$ LANGUAGE PLPERLU; + +COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$ +/* + * Copyright (C) 2011 Equinox Software + * Mike Rylander + * + * Inspired by translate_isbn1013 + * + * The force_to_isbn13 function takes an input ISBN and returns the ISBN13 + * version without hypens and with a repaired checksum if the checksum was bad + */ +$$; + +COMMIT; 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 deleted file mode 100644 index 281dfe1..0000000 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.svf-phase-one.sql +++ /dev/null @@ -1,519 +0,0 @@ -BEGIN; - -INSERT INTO config.upgrade_log (version) VALUES ('0495'); -- miker - -CREATE TABLE config.record_attr_definition ( - name TEXT PRIMARY KEY, - label TEXT NOT NULL, -- I18N - description TEXT, - 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; - -UPDATE config.i18n_core SET fq_field = 'ccvm.value', identity_value = ccvm.id FROM config.coded_value_map AS ccvm WHERE fq_field = 'clm.value' AND ccvm.ctype = 'item_lang' AND identity_value = ccvm.code; -UPDATE config.i18n_core SET fq_field = 'ccvm.value', identity_value = ccvm.id FROM config.coded_value_map AS ccvm WHERE fq_field = 'cblvl.value' AND ccvm.ctype = 'bib_level' AND identity_value = ccvm.code; -UPDATE config.i18n_core SET fq_field = 'ccvm.value', identity_value = ccvm.id FROM config.coded_value_map AS ccvm WHERE fq_field = 'cifm.value' AND ccvm.ctype = 'item_form' AND identity_value = ccvm.code; -UPDATE config.i18n_core SET fq_field = 'ccvm.value', identity_value = ccvm.id FROM config.coded_value_map AS ccvm WHERE fq_field = 'citm.value' AND ccvm.ctype = 'item_type' AND identity_value = ccvm.code; -UPDATE config.i18n_core SET fq_field = 'ccvm.value', identity_value = ccvm.id FROM config.coded_value_map AS ccvm WHERE fq_field = 'clfm.value' AND ccvm.ctype = 'lit_form' AND identity_value = ccvm.code; -UPDATE config.i18n_core SET fq_field = 'ccvm.value', identity_value = ccvm.id FROM config.coded_value_map AS ccvm WHERE fq_field = 'cam.value' AND ccvm.ctype = 'audience' AND identity_value = ccvm.code; -UPDATE config.i18n_core SET fq_field = 'ccvm.value', identity_value = ccvm.id FROM config.coded_value_map AS ccvm WHERE fq_field = 'cvrfm.value' AND ccvm.ctype = 'vr_format' AND identity_value = ccvm.code; - -UPDATE config.i18n_core SET fq_field = 'ccvm.description', identity_value = ccvm.id FROM config.coded_value_map AS ccvm WHERE fq_field = 'clfm.description' AND ccvm.ctype = 'lit_form' AND identity_value = ccvm.code; -UPDATE config.i18n_core SET fq_field = 'ccvm.description', identity_value = ccvm.id FROM config.coded_value_map AS ccvm WHERE fq_field = 'cam.description' AND ccvm.ctype = 'audience' AND identity_value = ccvm.code; - -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(ff_pos.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 TEXT; - 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 IS NOT NULL 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_sf 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 ); - -CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$ - SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' ); -$func$ LANGUAGE SQL STRICT IMMUTABLE; - -CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$ - SELECT approximate_date( $1, '0'); -$func$ LANGUAGE SQL STRICT IMMUTABLE; - -CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$ - SELECT approximate_date( $1, '9'); -$func$ LANGUAGE SQL STRICT IMMUTABLE; - -CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$ - SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END -$func$ LANGUAGE SQL STRICT IMMUTABLE; - -CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$ - SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END -$func$ LANGUAGE SQL STRICT IMMUTABLE; - -CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$ - use Business::ISBN; - use strict; - use warnings; - - # Find the first ISBN, force it to ISBN13 and return it - - my $input = shift; - - foreach my $word (split(/\s/, $input)) { - my $isbn = Business::ISBN->new($word); - - # First check the checksum; if it is not valid, fix it and add the original - # bad-checksum ISBN to the output - if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) { - $isbn->fix_checksum(); - } - - # If we now have a valid ISBN, force it to ISBN13 and return it - return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid()); - } - return undef; -$func$ LANGUAGE PLPERLU; - -COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$ -/* - * Copyright (C) 2011 Equinox Software - * Mike Rylander - * - * Inspired by translate_isbn1013 - * - * The force_to_isbn13 function takes an input ISBN and returns the ISBN13 - * version without hypens and with a repaired checksum if the checksum was bad - */ -$$; - -COMMIT; -- 1.7.2.5