3 INSERT INTO config.upgrade_log (version) VALUES ('XXXX'); -- miker
5 CREATE TABLE config.record_attr_definition (
7 label TEXT NOT NULL, -- I18N
8 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
9 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
11 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
12 tag TEXT, -- LIKE format
13 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
15 -- This is used for both tag/sf and xpath entries
18 -- For xpath-extracted attrs
20 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
25 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
27 -- For phys-char fields
28 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id)
31 CREATE TABLE config.record_attr_index_norm_map (
32 id SERIAL PRIMARY KEY,
33 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
34 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
36 pos INT NOT NULL DEFAULT 0
39 CREATE TABLE config.coded_value_map (
40 id SERIAL PRIMARY KEY,
41 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
48 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('alph','Alph','Alph');
49 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('audience','Audn','Audn');
50 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('bib_level','BLvl','BLvl');
51 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('biog','Biog','Biog');
52 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('conf','Conf','Conf');
53 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('control_type','Ctrl','Ctrl');
54 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('ctry','Ctry','Ctry');
55 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('date1','Date1','Date1');
56 INSERT INTO config.record_attr_definition (name,label,fixed_field,sorter,filter) values ('pubdate','Pub Date','Date1',TRUE,FALSE);
57 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('date2','Date2','Date2');
58 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('cat_form','Desc','Desc');
59 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('pub_status','DtSt','DtSt');
60 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('enc_level','ELvl','ELvl');
61 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('fest','Fest','Fest');
62 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('item_form','Form','Form');
63 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('gpub','GPub','GPub');
64 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('ills','Ills','Ills');
65 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('indx','Indx','Indx');
66 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('item_lang','Lang','Lang');
67 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('lit_form','LitF','LitF');
68 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('mrec','MRec','MRec');
69 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('ff_sl','S/L','S/L');
70 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('type_mat','TMat','TMat');
71 INSERT INTO config.record_attr_definition (name,label,fixed_field) values ('item_type','Type','Type');
72 INSERT INTO config.record_attr_definition (name,label,phys_char_sf) values ('vr_format','Videorecording format',72);
73 INSERT INTO config.record_attr_definition (name,label,sorter,filter,tag) values ('titlesort','Title',TRUE,FALSE,'tnf');
74 INSERT INTO config.record_attr_definition (name,label,sorter,filter,tag) values ('authorsort','Author',TRUE,FALSE,'1%');
76 INSERT INTO config.coded_value_map (ctype,code,value,description)
77 SELECT 'item_lang' AS ctype, code, value, NULL FROM config.language_map
79 SELECT 'bib_level' AS ctype, code, value, NULL FROM config.bib_level_map
81 SELECT 'item_form' AS ctype, code, value, NULL FROM config.item_form_map
83 SELECT 'item_type' AS ctype, code, value, NULL FROM config.item_type_map
85 SELECT 'lit_form' AS ctype, code, value, description FROM config.lit_form_map
87 SELECT 'audience' AS ctype, code, value, description FROM config.audience_map
89 SELECT 'vr_format' AS ctype, code, value, NULL FROM config.videorecording_format_map;
91 ALTER TABLE config.i18n_locale DROP CONSTRAINT i18n_locale_marc_code_fkey;
93 ALTER TABLE config.circ_matrix_matchpoint DROP CONSTRAINT circ_matrix_matchpoint_marc_form_fkey;
94 ALTER TABLE config.circ_matrix_matchpoint DROP CONSTRAINT circ_matrix_matchpoint_marc_type_fkey;
95 ALTER TABLE config.circ_matrix_matchpoint DROP CONSTRAINT circ_matrix_matchpoint_marc_vr_format_fkey;
97 ALTER TABLE config.hold_matrix_matchpoint DROP CONSTRAINT hold_matrix_matchpoint_marc_form_fkey;
98 ALTER TABLE config.hold_matrix_matchpoint DROP CONSTRAINT hold_matrix_matchpoint_marc_type_fkey;
99 ALTER TABLE config.hold_matrix_matchpoint DROP CONSTRAINT hold_matrix_matchpoint_marc_vr_format_fkey;
101 DROP TABLE config.language_map;
102 DROP TABLE config.bib_level_map;
103 DROP TABLE config.item_form_map;
104 DROP TABLE config.item_type_map;
105 DROP TABLE config.lit_form_map;
106 DROP TABLE config.audience_map;
107 DROP TABLE config.videorecording_format_map;
109 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
110 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
111 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
112 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
113 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
114 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
115 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
117 CREATE TABLE metabib.record_attr (
118 id BIGINT PRIMARY KEY REFERENCES biblio.record_entry (id) ON DELETE CASCADE,
119 attrs HSTORE NOT NULL DEFAULT ''::HSTORE
121 CREATE INDEX metabib_svf_attrs_idx ON metabib.record_attr USING GIST (attrs);
122 CREATE INDEX metabib_svf_date1_idx ON metabib.record_attr ( (attrs->'date1') );
123 CREATE INDEX metabib_svf_dates_idx ON metabib.record_attr ( (attrs->'date1'), (attrs->'date2') );
125 INSERT INTO metabib.record_attr (id,attrs)
126 SELECT mrd.record, hstore(mrd) - '{id,record}'::TEXT[] FROM metabib.rec_descriptor mrd;
128 -- Back-compat view ... we're moving to an HSTORE world
129 CREATE TYPE metabib.rec_desc_type AS (
147 DROP TABLE metabib.rec_descriptor CASCADE;
149 CREATE VIEW metabib.rec_descriptor AS
152 (populate_record(NULL::metabib.rec_desc_type, attrs)).*
153 FROM metabib.record_attr;
155 CREATE OR REPLACE FUNCTION vandelay.marc21_record_type( marc TEXT ) RETURNS config.marc21_rec_type_map AS $func$
162 retval config.marc21_rec_type_map%ROWTYPE;
164 ldr := oils_xpath_string( '//*[local-name()="leader"]', marc );
166 IF ldr IS NULL OR ldr = '' THEN
167 SELECT * INTO retval FROM config.marc21_rec_type_map WHERE code = 'BKS';
171 SELECT * INTO tval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'Type' LIMIT 1; -- They're all the same
172 SELECT * INTO bval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'BLvl' LIMIT 1; -- They're all the same
175 tval := SUBSTRING( ldr, tval_rec.start_pos + 1, tval_rec.length );
176 bval := SUBSTRING( ldr, bval_rec.start_pos + 1, bval_rec.length );
178 -- RAISE NOTICE 'type %, blvl %, ldr %', tval, bval, ldr;
180 SELECT * INTO retval FROM config.marc21_rec_type_map WHERE type_val LIKE '%' || tval || '%' AND blvl_val LIKE '%' || bval || '%';
183 IF retval.code IS NULL THEN
184 SELECT * INTO retval FROM config.marc21_rec_type_map WHERE code = 'BKS';
189 $func$ LANGUAGE PLPGSQL;
191 CREATE OR REPLACE FUNCTION biblio.marc21_record_type( rid BIGINT ) RETURNS config.marc21_rec_type_map AS $func$
192 SELECT * FROM vandelay.marc21_record_type( (SELECT marc FROM biblio.record_entry WHERE id = $1) );
195 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_fixed_field( marc TEXT, ff TEXT ) RETURNS TEXT AS $func$
202 rtype := (vandelay.marc21_record_type( marc )).code;
203 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE fixed_field = ff AND rec_type = rtype ORDER BY tag DESC LOOP
204 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(tag) || '"]/text()', marc ) ) x(value) LOOP
205 val := SUBSTRING( tag_data.value, ff_pos.start_pos + 1, ff_pos.length );
208 val := REPEAT( ff_pos.default_val, ff_pos.length );
214 $func$ LANGUAGE PLPGSQL;
216 CREATE OR REPLACE FUNCTION biblio.marc21_extract_fixed_field( rid BIGINT, ff TEXT ) RETURNS TEXT AS $func$
217 SELECT * FROM vandelay.marc21_extract_fixed_field( (SELECT marc FROM biblio.record_entry WHERE id = $1), $2 );
220 CREATE TYPE biblio.record_ff_map AS (record BIGINT, ff_name TEXT, ff_value TEXT);
221 CREATE OR REPLACE FUNCTION vandelay.marc21_extract_all_fixed_fields( marc TEXT ) RETURNS SETOF biblio.record_ff_map AS $func$
226 output biblio.record_ff_map%ROWTYPE;
228 rtype := (vandelay.marc21_record_type( marc )).code;
230 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE rec_type = rtype ORDER BY tag DESC LOOP
231 output.ff_name := ff_pos.fixed_field;
232 output.ff_value := NULL;
234 FOR tag_data IN SELECT value FROM UNNEST( oils_xpath( '//*[@tag="' || UPPER(tag) || '"]/text()', marc ) ) x(value) LOOP
235 output.ff_value := SUBSTRING( tag_data.value, ff_pos.start_pos + 1, ff_pos.length );
236 IF output.ff_value IS NULL THEN output.ff_value := REPEAT( ff_pos.default_val, ff_pos.length ); END IF;
238 output.ff_value := NULL;
245 $func$ LANGUAGE PLPGSQL;
247 CREATE OR REPLACE FUNCTION biblio.marc21_extract_all_fixed_fields( rid BIGINT ) RETURNS SETOF biblio.record_ff_map AS $func$
248 SELECT $1 AS record, ff_name, ff_value FROM vandelay.marc21_extract_all_fixed_fields( (SELECT marc FROM biblio.record_entry WHERE id = $1) );
251 CREATE OR REPLACE FUNCTION vandelay.marc21_physical_characteristics( marc TEXT) RETURNS SETOF biblio.marc21_physical_characteristics AS $func$
255 ptype config.marc21_physical_characteristic_type_map%ROWTYPE;
256 psf config.marc21_physical_characteristic_subfield_map%ROWTYPE;
257 pval config.marc21_physical_characteristic_value_map%ROWTYPE;
258 retval biblio.marc21_physical_characteristics%ROWTYPE;
261 _007 := oils_xpath_string( '//*[@tag="007"]', marc );
263 IF _007 IS NOT NULL AND _007 <> '' THEN
264 SELECT * INTO ptype FROM config.marc21_physical_characteristic_type_map WHERE ptype_key = SUBSTRING( _007, 1, 1 );
266 IF ptype.ptype_key IS NOT NULL THEN
267 FOR psf IN SELECT * FROM config.marc21_physical_characteristic_subfield_map WHERE ptype_key = ptype.ptype_key LOOP
268 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 );
270 IF pval.id IS NOT NULL THEN
273 retval.ptype := ptype.ptype_key;
274 retval.subfield := psf.id;
275 retval.value := pval.id;
285 $func$ LANGUAGE PLPGSQL;
287 CREATE OR REPLACE FUNCTION biblio.marc21_physical_characteristics( rid BIGINT ) RETURNS SETOF biblio.marc21_physical_characteristics AS $func$
288 SELECT id, $1 AS record, ptype, subfield, value FROM vandelay.marc21_physical_characteristics( (SELECT marc FROM biblio.record_entry WHERE id = $1) );
291 CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
293 transformed_xml TEXT;
296 xfrm config.xml_transform%ROWTYPE;
298 new_attrs HSTORE := ''::HSTORE;
299 attr_def config.record_attr_definition%ROWTYPE;
302 IF NEW.deleted IS TRUE THEN -- If this bib is deleted
303 DELETE FROM metabib.metarecord_source_map WHERE source = NEW.id; -- Rid ourselves of the search-estimate-killing linkage
304 DELETE FROM metabib.record_attr WHERE id = NEW.id; -- Kill the attrs hash, useless on deleted records
305 DELETE FROM authority.bib_linking WHERE bib = NEW.id; -- Avoid updating fields in bibs that are no longer visible
306 RETURN NEW; -- and we're done
309 IF TG_OP = 'UPDATE' THEN -- re-ingest?
310 PERFORM * FROM config.internal_flag WHERE name = 'ingest.reingest.force_on_same_marc' AND enabled;
312 IF NOT FOUND AND OLD.marc = NEW.marc THEN -- don't do anything if the MARC didn't change
317 -- Record authority linking
318 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_authority_linking' AND enabled;
320 PERFORM biblio.map_authority_linking( NEW.id, NEW.marc );
323 -- Flatten and insert the mfr data
324 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_full_rec' AND enabled;
326 PERFORM metabib.reingest_metabib_full_rec(NEW.id);
328 -- Now we pull out attribute data, which is dependent on the mfr for all but XPath-based fields
329 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_metabib_rec_descriptor' AND enabled;
331 FOR attr_def IN SELECT * FROM config.record_attr_definition ORDER BY format LOOP
333 IF attr_def.tag THEN -- tag (and optional subfield list) selection
334 SELECT ARRAY_TO_STRING(ARRAY_ACCUM(value), COALESCE(attr_def.joiner,' ')) INTO attr_value
335 FROM (SELECT * FROM metabib.full_rec ORDER BY tag, subfield) AS x
336 WHERE record = NEW.id
337 AND tag LIKE attr_def.tag
339 WHEN attr_def.sf_list IS NOT NULL
340 THEN POSITION(subfield IN attr_def.sf_list) > 0
347 ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field
348 attr_value := biblio.marc21_extract_fixed_field(NEW.id, attr_def.fixed_field);
350 ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression
352 SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format;
354 -- See if we can skip the XSLT ... it's expensive
355 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
356 -- Can't skip the transform
357 IF xfrm.xslt <> '---' THEN
358 transformed_xml := oils_xslt_process(NEW.marc,xfrm.xslt);
360 transformed_xml := NEW.marc;
363 prev_xfrm := xfrm.name;
366 IF xfrm.name IS NULL THEN
367 -- just grab the marcxml (empty) transform
368 SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1;
369 prev_xfrm := xfrm.name;
372 attr_value := oils_xpath_string(attr_def.xpath, transformed_xml, COALESCE(attr_def.joiner,' '), ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]);
374 ELSIF attr_def.phys_char IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map
375 SELECT value::TEXT INTO attr_value
376 FROM biblio.marc21_physical_characteristics(NEW.id)
377 WHERE subfield = attr_def.phys_char_sf
378 LIMIT 1; -- Just in case ...
382 -- apply index normalizers to attr_value
384 SELECT n.func AS func,
385 n.param_count AS param_count,
387 FROM config.index_normalizer n
388 JOIN config.record_attr_index_norm_map m ON (m.norm = n.id)
389 WHERE attr = attr_def.name
391 EXECUTE 'SELECT ' || normalizer.func || '(' ||
392 quote_literal( attr_value ) ||
394 WHEN normalizer.param_count > 0
395 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
402 -- Add the new value to the hstore
403 new_attrs := new_attrs || hstore( attr_def.name, attr_value );
407 IF TG_OP = 'INSERT' OR OLD.deleted THEN -- initial insert OR revivication
408 INSERT INTO metabib.record_attr (id, attrs) VALUES (NEW.id, new_attrs);
410 UPDATE metabib.record_attr SET attrs = attrs || new_attrs WHERE id = NEW.id;
416 -- Gather and insert the field entry data
417 PERFORM metabib.reingest_metabib_field_entries(NEW.id);
420 IF TG_OP = 'INSERT' THEN
421 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
423 PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
426 PERFORM * FROM config.internal_flag WHERE name = 'ingest.disable_located_uri' AND enabled;
428 PERFORM biblio.extract_located_uris( NEW.id, NEW.marc, NEW.editor );
432 -- (re)map metarecord-bib linking
433 IF TG_OP = 'INSERT' THEN -- if not deleted and performing an insert, check for the flag
434 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_insert' AND enabled;
436 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
438 ELSE -- we're doing an update, and we're not deleted, remap
439 PERFORM * FROM config.internal_flag WHERE name = 'ingest.metarecord_mapping.skip_on_update' AND enabled;
441 PERFORM metabib.remap_metarecord_for_bib( NEW.id, NEW.fingerprint );
447 $func$ LANGUAGE PLPGSQL;
449 DROP FUNCTION metabib.reingest_metabib_rec_descriptor( bib_id BIGINT );