BEGIN; -- check whether patch can be applied SELECT evergreen.upgrade_deps_block_check('0816', :eg_version); -- To avoid problems with altering a table column after doing an -- update. ALTER TABLE authority.control_set_authority_field DISABLE TRIGGER ALL; ALTER TABLE authority.control_set_authority_field ADD COLUMN display_sf_list TEXT; UPDATE authority.control_set_authority_field SET display_sf_list = REGEXP_REPLACE(sf_list, '[w254]', '', 'g'); ALTER TABLE authority.control_set_authority_field ALTER COLUMN display_sf_list SET NOT NULL; ALTER TABLE authority.control_set_authority_field ENABLE TRIGGER ALL; ALTER TABLE metabib.browse_entry_def_map ADD COLUMN authority BIGINT REFERENCES authority.record_entry (id) ON DELETE SET NULL; ALTER TABLE config.metabib_field ADD COLUMN authority_xpath TEXT; ALTER TABLE config.metabib_field ADD COLUMN browse_sort_xpath TEXT; UPDATE config.metabib_field SET authority_xpath = '//@xlink:href' WHERE format = 'mods32' AND field_class IN ('subject','series','title','author') AND browse_field IS TRUE; ALTER TYPE metabib.field_entry_template ADD ATTRIBUTE authority BIGINT; ALTER TYPE metabib.field_entry_template ADD ATTRIBUTE sort_value TEXT; CREATE OR REPLACE FUNCTION metabib.reingest_metabib_field_entries( bib_id BIGINT, skip_facet BOOL DEFAULT FALSE, skip_browse BOOL DEFAULT FALSE, skip_search BOOL DEFAULT FALSE ) RETURNS VOID AS $func$ DECLARE fclass RECORD; ind_data metabib.field_entry_template%ROWTYPE; mbe_row metabib.browse_entry%ROWTYPE; mbe_id BIGINT; b_skip_facet BOOL; b_skip_browse BOOL; b_skip_search BOOL; value_prepped TEXT; BEGIN SELECT COALESCE(NULLIF(skip_facet, FALSE), EXISTS (SELECT enabled FROM config.internal_flag WHERE name = 'ingest.skip_facet_indexing' AND enabled)) INTO b_skip_facet; SELECT COALESCE(NULLIF(skip_browse, FALSE), EXISTS (SELECT enabled FROM config.internal_flag WHERE name = 'ingest.skip_browse_indexing' AND enabled)) INTO b_skip_browse; SELECT COALESCE(NULLIF(skip_search, FALSE), EXISTS (SELECT enabled FROM config.internal_flag WHERE name = 'ingest.skip_search_indexing' AND enabled)) INTO b_skip_search; PERFORM * FROM config.internal_flag WHERE name = 'ingest.assume_inserts_only' AND enabled; IF NOT FOUND THEN IF NOT b_skip_search THEN FOR fclass IN SELECT * FROM config.metabib_class LOOP -- RAISE NOTICE 'Emptying out %', fclass.name; EXECUTE $$DELETE FROM metabib.$$ || fclass.name || $$_field_entry WHERE source = $$ || bib_id; END LOOP; END IF; IF NOT b_skip_facet THEN DELETE FROM metabib.facet_entry WHERE source = bib_id; END IF; IF NOT b_skip_browse THEN DELETE FROM metabib.browse_entry_def_map WHERE source = bib_id; END IF; END IF; FOR ind_data IN SELECT * FROM biblio.extract_metabib_field_entry( bib_id ) LOOP IF ind_data.field < 0 THEN ind_data.field = -1 * ind_data.field; END IF; IF ind_data.facet_field AND NOT b_skip_facet THEN INSERT INTO metabib.facet_entry (field, source, value) VALUES (ind_data.field, ind_data.source, ind_data.value); END IF; IF ind_data.browse_field AND NOT b_skip_browse THEN -- A caveat about this SELECT: this should take care of replacing -- old mbe rows when data changes, but not if normalization (by -- which I mean specifically the output of -- evergreen.oils_tsearch2()) changes. It may or may not be -- expensive to add a comparison of index_vector to index_vector -- to the WHERE clause below. value_prepped := metabib.browse_normalize(ind_data.value, ind_data.field); SELECT INTO mbe_row * FROM metabib.browse_entry WHERE value = value_prepped AND sort_value = ind_data.sort_value; IF FOUND THEN mbe_id := mbe_row.id; ELSE INSERT INTO metabib.browse_entry ( value, sort_value ) VALUES ( value_prepped, ind_data.sort_value ); mbe_id := CURRVAL('metabib.browse_entry_id_seq'::REGCLASS); END IF; INSERT INTO metabib.browse_entry_def_map (entry, def, source, authority) VALUES (mbe_id, ind_data.field, ind_data.source, ind_data.authority); END IF; IF ind_data.search_field AND NOT b_skip_search THEN EXECUTE $$ INSERT INTO metabib.$$ || ind_data.field_class || $$_field_entry (field, source, value) VALUES ($$ || quote_literal(ind_data.field) || $$, $$ || quote_literal(ind_data.source) || $$, $$ || quote_literal(ind_data.value) || $$);$$; END IF; END LOOP; IF NOT b_skip_search THEN PERFORM metabib.update_combined_index_vectors(bib_id); END IF; RETURN; END; $func$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION biblio.extract_metabib_field_entry ( rid BIGINT, default_joiner TEXT ) RETURNS SETOF metabib.field_entry_template AS $func$ DECLARE bib biblio.record_entry%ROWTYPE; idx config.metabib_field%ROWTYPE; xfrm config.xml_transform%ROWTYPE; prev_xfrm TEXT; transformed_xml TEXT; xml_node TEXT; xml_node_list TEXT[]; facet_text TEXT; browse_text TEXT; sort_value TEXT; raw_text TEXT; curr_text TEXT; joiner TEXT := default_joiner; -- XXX will index defs supply a joiner? authority_text TEXT; authority_link BIGINT; output_row metabib.field_entry_template%ROWTYPE; BEGIN -- Get the record SELECT INTO bib * FROM biblio.record_entry WHERE id = rid; -- Loop over the indexing entries FOR idx IN SELECT * FROM config.metabib_field ORDER BY format LOOP SELECT INTO xfrm * from config.xml_transform WHERE name = idx.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(bib.marc,xfrm.xslt); ELSE transformed_xml := bib.marc; END IF; prev_xfrm := xfrm.name; END IF; xml_node_list := oils_xpath( idx.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] ); raw_text := NULL; FOR xml_node IN SELECT x FROM unnest(xml_node_list) AS x LOOP CONTINUE WHEN xml_node !~ E'^\\s*<'; curr_text := ARRAY_TO_STRING( oils_xpath( '//text()', REGEXP_REPLACE( -- This escapes all &s not followed by "amp;". Data ise returned from oils_xpath (above) in UTF-8, not entity encoded REGEXP_REPLACE( -- This escapes embeded [^<]+)(<)([^>]+<)$re$, E'\\1<\\3', 'g' ), '&(?!amp;)', '&', 'g' ) ), ' ' ); CONTINUE WHEN curr_text IS NULL OR curr_text = ''; IF raw_text IS NOT NULL THEN raw_text := raw_text || joiner; END IF; raw_text := COALESCE(raw_text,'') || curr_text; -- autosuggest/metabib.browse_entry IF idx.browse_field THEN IF idx.browse_xpath IS NOT NULL AND idx.browse_xpath <> '' THEN browse_text := oils_xpath_string( idx.browse_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] ); ELSE browse_text := curr_text; END IF; IF idx.browse_sort_xpath IS NOT NULL AND idx.browse_sort_xpath <> '' THEN sort_value := oils_xpath_string( idx.browse_sort_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] ); ELSE sort_value := browse_text; END IF; output_row.field_class = idx.field_class; output_row.field = idx.id; output_row.source = rid; output_row.value = BTRIM(REGEXP_REPLACE(browse_text, E'\\s+', ' ', 'g')); output_row.sort_value := public.search_normalize(sort_value); output_row.authority := NULL; IF idx.authority_xpath IS NOT NULL AND idx.authority_xpath <> '' THEN authority_text := oils_xpath_string( idx.authority_xpath, xml_node, joiner, ARRAY[ ARRAY[xfrm.prefix, xfrm.namespace_uri], ARRAY['xlink','http://www.w3.org/1999/xlink'] ] ); IF authority_text ~ '^\d+$' THEN authority_link := authority_text::BIGINT; PERFORM * FROM authority.record_entry WHERE id = authority_link; IF FOUND THEN output_row.authority := authority_link; END IF; END IF; END IF; output_row.browse_field = TRUE; RETURN NEXT output_row; output_row.browse_field = FALSE; output_row.sort_value := NULL; END IF; -- insert raw node text for faceting IF idx.facet_field THEN IF idx.facet_xpath IS NOT NULL AND idx.facet_xpath <> '' THEN facet_text := oils_xpath_string( idx.facet_xpath, xml_node, joiner, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] ); ELSE facet_text := curr_text; END IF; output_row.field_class = idx.field_class; output_row.field = -1 * idx.id; output_row.source = rid; output_row.value = BTRIM(REGEXP_REPLACE(facet_text, E'\\s+', ' ', 'g')); output_row.facet_field = TRUE; RETURN NEXT output_row; output_row.facet_field = FALSE; END IF; END LOOP; CONTINUE WHEN raw_text IS NULL OR raw_text = ''; -- insert combined node text for searching IF idx.search_field THEN output_row.field_class = idx.field_class; output_row.field = idx.id; output_row.source = rid; output_row.value = BTRIM(REGEXP_REPLACE(raw_text, E'\\s+', ' ', 'g')); output_row.search_field = TRUE; RETURN NEXT output_row; output_row.search_field = FALSE; END IF; END LOOP; END; $func$ LANGUAGE PLPGSQL; -- 953.data.MODS32-xsl.sql UPDATE config.xml_transform SET xslt=$$ BK SE BK MM CF MP VM MU b afgk abfgk <xsl:value-of select="substring($titleChop,@ind2+1)"/> <xsl:value-of select="$titleChop"/> b b afgk b afgk abfgk <xsl:value-of select="$title"/> b b afgk <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:call-template name="subfieldSelect"> <xsl:with-param name="codes">a</xsl:with-param> </xsl:call-template> </xsl:with-param> </xsl:call-template> a <xsl:value-of select="$titleChop" /> <xsl:value-of select="substring($titleChop,@ind2+1)"/> <xsl:value-of select="$titleChop" /> <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:call-template name="subfieldSelect"> <!-- 1/04 removed $h, $b --> <xsl:with-param name="codes">af</xsl:with-param> </xsl:call-template> </xsl:with-param> </xsl:call-template> <xsl:value-of select="$titleChop"/> <xsl:value-of select="substring($titleChop,$nfi+1)"/> <xsl:value-of select="$titleChop"/> ah <xsl:value-of select="$titleChop" /> <xsl:value-of select="substring($titleChop,@ind1+1)"/> <xsl:value-of select="$titleChop" /> creator creator creator personal yes yes text cartographic notated music sound recording-nonmusical sound recording-musical still image moving image three dimensional object software, multimedia mixed material globe remote sensing image map atlas database loose-leaf series newspaper periodical web site abstract or summary bibliography catalog dictionary encyclopedia handbook legal article index discography legislation theses survey of literature review programmed text filmography directory statistics technical report legal case and case notes law report or digest treaty conference publication numeric data database font game patent festschrift biography essay drama comic strip fiction humor, satire letter novel short story speech biography conference publication drama essay fiction folktale history humor, satire memoir poetry rehearsal reporting sound speech art original kit art reproduction diorama filmstrip legal article picture graphic technical drawing motion picture chart flash card microscope slide model realia slide transparency videorecording toy abvxyz - code marccountry code iso3166 text :,;/ monographic continuing ab reformatted digital digitized microfilm digitized other analog
braille
print
electronic
microfiche
microfilm
access preservation replacement
chip cartridge
computer optical disc cartridge
magnetic disc
magneto-optical disc
optical disc
remote
tape cartridge
tape cassette
tape reel
celestial globe
earth moon globe
planetary or lunar globe
terrestrial globe
kit
atlas
diagram
map
model
profile
remote-sensing image
section
view
aperture card
microfiche
microfiche cassette
microfilm cartridge
microfilm cassette
microfilm reel
microopaque
film cartridge
film cassette
film reel
chart
collage
drawing
flash card
painting
photomechanical print
photonegative
photoprint
picture
print
technical drawing
notated music
filmslip
filmstrip cartridge
filmstrip roll
other filmstrip type
slide
transparency
remote-sensing image
cylinder
roll
sound cartridge
sound cassette
sound disc
sound-tape reel
sound-track film
wire recording
braille
combination
moon
tactile, with no writing system
braille
large print
regular print
text in looseleaf binder
videocartridge
videocassette
videodisc
videoreel
abce
ab agrt ab adolescent adult general juvenile preschool specialized defg marcgac iso3166 ab abx ab ab av <xsl:value-of select="$titleChop" /> <xsl:value-of select="substring($titleChop,@ind2+1)"/> <xsl:value-of select="$titleChop" /> <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:call-template name="subfieldSelect"> <xsl:with-param name="codes">av</xsl:with-param> </xsl:call-template> </xsl:with-param> </xsl:call-template> abcx3 <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:call-template name="specialSubfieldSelect"> <xsl:with-param name="anyCodes">tfklmorsv</xsl:with-param> <xsl:with-param name="axis">t</xsl:with-param> <xsl:with-param name="afterCodes">g</xsl:with-param> </xsl:call-template> </xsl:with-param> </xsl:call-template> aq t g <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:call-template name="specialSubfieldSelect"> <xsl:with-param name="anyCodes">tfklmorsv</xsl:with-param> <xsl:with-param name="axis">t</xsl:with-param> <xsl:with-param name="afterCodes">dg</xsl:with-param> </xsl:call-template> </xsl:with-param> </xsl:call-template> c t dgn <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:call-template name="specialSubfieldSelect"> <xsl:with-param name="anyCodes">tfklsv</xsl:with-param> <xsl:with-param name="axis">t</xsl:with-param> <xsl:with-param name="afterCodes">g</xsl:with-param> </xsl:call-template> </xsl:with-param> </xsl:call-template> aqdc t gn <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:call-template name="subfieldSelect"> <xsl:with-param name="codes">adfgklmorsv</xsl:with-param> </xsl:call-template> </xsl:with-param> </xsl:call-template> <xsl:value-of select="$titleChop" /> <xsl:value-of select="substring($titleChop,@ind1+1)"/> <xsl:value-of select="$titleChop" /> <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:call-template name="specialSubfieldSelect"> <xsl:with-param name="anyCodes">tfklmorsv</xsl:with-param> <xsl:with-param name="axis">t</xsl:with-param> <xsl:with-param name="afterCodes">g</xsl:with-param> </xsl:call-template> </xsl:with-param> </xsl:call-template> aq t g <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:call-template name="specialSubfieldSelect"> <xsl:with-param name="anyCodes">tfklmorsv</xsl:with-param> <xsl:with-param name="axis">t</xsl:with-param> <xsl:with-param name="afterCodes">dg</xsl:with-param> </xsl:call-template> </xsl:with-param> </xsl:call-template> c t dgn <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:call-template name="specialSubfieldSelect"> <xsl:with-param name="anyCodes">tfklsv</xsl:with-param> <xsl:with-param name="axis">t</xsl:with-param> <xsl:with-param name="afterCodes">g</xsl:with-param> </xsl:call-template> </xsl:with-param> </xsl:call-template> aqdc t gn adfgklmorsv <xsl:value-of select="$titleChop" /> <xsl:value-of select="substring($titleChop,@ind2+1)"/> <xsl:value-of select="$titleChop" /> isbn isrc ismn sici ab issn lccn issue number matrix number music plate music publisher videorecording identifier ba ab ab doi hdl uri y3z y3 z abje abcd35 abcde35
n n fgkdlmor p p fgkdlmor g g pst p p fgkdlmor
cdn aq :,;/ acdeq constituent <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:value-of select="."></xsl:value-of> </xsl:with-param> </xsl:call-template> <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:value-of select="."></xsl:value-of> </xsl:with-param> </xsl:call-template> <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:value-of select="."></xsl:value-of> </xsl:with-param> </xsl:call-template> <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:value-of select="."></xsl:value-of> </xsl:with-param> </xsl:call-template> code marcgac lcsh lcshac mesh nal csh rvm aq cdnp abcdeqnp adfhklor <xsl:value-of select="$titleChop" /> <xsl:value-of select="substring($titleChop,@ind1+1)"/> <xsl:value-of select="$titleChop" /> abcd bc yes Arabic Latin Chinese, Japanese, Korean Cyrillic Hebrew Greek summary or subtitle sung or spoken text libretto table of contents accompanying material translation summary or subtitle sung or spoken text libretto table of contents accompanying material translation .:,;/
$$ WHERE name = 'mods32'; -- 954.data.MODS33-xsl.sql UPDATE config.xml_transform SET xslt=$$ !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~ ¡¢£¤¥¦§¨©ª«¬­®¯°±²³´µ¶·¸¹º»¼½¾¿ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿ !'()*-.0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ_abcdefghijklmnopqrstuvwxyz~ 0123456789ABCDEF BK SE BK MM CF MP VM MU b afgk abfgk <xsl:value-of select="substring($titleChop,@ind2+1)"/> <xsl:value-of select="$titleChop"/> b b afgk <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:call-template name="subfieldSelect"> <xsl:with-param name="codes">a</xsl:with-param> </xsl:call-template> </xsl:with-param> </xsl:call-template> <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:call-template name="subfieldSelect"> <!-- 1/04 removed $h, b --> <xsl:with-param name="codes">a</xsl:with-param> </xsl:call-template> </xsl:with-param> </xsl:call-template> <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:call-template name="subfieldSelect"> <!-- 1/04 removed $h, $b --> <xsl:with-param name="codes">af</xsl:with-param> </xsl:call-template> </xsl:with-param> </xsl:call-template> <xsl:call-template name="uri"/> <xsl:variable name="str"> <xsl:for-each select="marc:subfield"> <xsl:if test="(contains('adfklmors',@code) and (not(../marc:subfield[@code='n' or @code='p']) or (following-sibling::marc:subfield[@code='n' or @code='p'])))"> <xsl:value-of select="text()"/> <xsl:text> </xsl:text> </xsl:if> </xsl:for-each> </xsl:variable> <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:value-of select="substring($str,1,string-length($str)-1)"/> </xsl:with-param> </xsl:call-template> <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:call-template name="subfieldSelect"> <xsl:with-param name="codes">ah</xsl:with-param> </xsl:call-template> </xsl:with-param> </xsl:call-template> creator creator creator personal yes yes text cartographic notated music sound recording-nonmusical sound recording-musical still image moving image three dimensional object software, multimedia mixed material globe remote-sensing image map atlas database loose-leaf series newspaper periodical web site abstract or summary bibliography catalog dictionary encyclopedia handbook legal article index discography legislation theses survey of literature review programmed text filmography directory statistics technical report legal case and case notes law report or digest treaty conference publication numeric data database font game patent offprint festschrift biography essay drama comic strip fiction humor, satire letter novel short story speech biography conference publication drama essay fiction folktale history humor, satire memoir poetry rehearsal reporting sound speech art original kit art reproduction diorama filmstrip legal article picture graphic technical drawing motion picture chart flash card microscope slide model realia slide transparency videorecording toy abcdef - abvxyz - code marccountry code iso3166 text :,;/ monographic continuing ab reformatted digital digitized microfilm digitized other analog
braille
print
electronic
microfiche
microfilm
access preservation replacement
chip cartridge
computer optical disc cartridge
magnetic disc
magneto-optical disc
optical disc
remote
tape cartridge
tape cassette
tape reel
celestial globe
earth moon globe
planetary or lunar globe
terrestrial globe
kit
atlas
diagram
map
model
profile
remote-sensing image
section
view
aperture card
microfiche
microfiche cassette
microfilm cartridge
microfilm cassette
microfilm reel
microopaque
film cartridge
film cassette
film reel
chart
collage
drawing
flash card
painting
photomechanical print
photonegative
photoprint
picture
print
technical drawing
notated music
filmslip
filmstrip cartridge
filmstrip roll
other filmstrip type
slide
transparency
remote-sensing image
cylinder
roll
sound cartridge
sound cassette
sound disc
sound-tape reel
sound-track film
wire recording
braille
combination
moon
tactile, with no writing system
braille
large print
regular print
text in looseleaf binder
videocartridge
videocassette
videodisc
videoreel
abce
ab agrt ab adolescent adult general juvenile preschool specialized defg marcgac iso3166 ab abx ab ab <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:call-template name="subfieldSelect"> <xsl:with-param name="codes">av</xsl:with-param> </xsl:call-template> </xsl:with-param> </xsl:call-template> <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:call-template name="subfieldSelect"> <xsl:with-param name="codes">av</xsl:with-param> </xsl:call-template> </xsl:with-param> </xsl:call-template> abcx3 <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:call-template name="specialSubfieldSelect"> <xsl:with-param name="anyCodes">tfklmorsv</xsl:with-param> <xsl:with-param name="axis">t</xsl:with-param> <xsl:with-param name="afterCodes">g</xsl:with-param> </xsl:call-template> </xsl:with-param> </xsl:call-template> aq t g <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:call-template name="specialSubfieldSelect"> <xsl:with-param name="anyCodes">tfklmorsv</xsl:with-param> <xsl:with-param name="axis">t</xsl:with-param> <xsl:with-param name="afterCodes">dg</xsl:with-param> </xsl:call-template> </xsl:with-param> </xsl:call-template> c t dgn <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:call-template name="specialSubfieldSelect"> <xsl:with-param name="anyCodes">tfklsv</xsl:with-param> <xsl:with-param name="axis">t</xsl:with-param> <xsl:with-param name="afterCodes">g</xsl:with-param> </xsl:call-template> </xsl:with-param> </xsl:call-template> aqdc t gn <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:call-template name="subfieldSelect"> <xsl:with-param name="codes">adfgklmorsv</xsl:with-param> </xsl:call-template> </xsl:with-param> </xsl:call-template> <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:value-of select="marc:subfield[@code='a']"/> </xsl:with-param> </xsl:call-template> <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:call-template name="specialSubfieldSelect"> <xsl:with-param name="anyCodes">tfklmorsv</xsl:with-param> <xsl:with-param name="axis">t</xsl:with-param> <xsl:with-param name="afterCodes">g</xsl:with-param> </xsl:call-template> </xsl:with-param> </xsl:call-template> aq t g <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:call-template name="specialSubfieldSelect"> <xsl:with-param name="anyCodes">tfklmorsv</xsl:with-param> <xsl:with-param name="axis">t</xsl:with-param> <xsl:with-param name="afterCodes">dg</xsl:with-param> </xsl:call-template> </xsl:with-param> </xsl:call-template> c t dgn <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:call-template name="specialSubfieldSelect"> <xsl:with-param name="anyCodes">tfklsv</xsl:with-param> <xsl:with-param name="axis">t</xsl:with-param> <xsl:with-param name="afterCodes">g</xsl:with-param> </xsl:call-template> </xsl:with-param> </xsl:call-template> aqdc t gn <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:call-template name="subfieldSelect"> <xsl:with-param name="codes">adfgklmorsv</xsl:with-param> </xsl:call-template> </xsl:with-param> </xsl:call-template> isbn isrc ismn sici ab issn issn-l lccn issue number matrix number music plate music publisher videorecording identifier ba ab ab doi hdl uri y3z y3 z y3 z abe u hijklmt abcd35 abcde35 aacr2
n n fgkdlmor p p fgkdlmor g g pst p p fgkdlmor
cdn aq :,;/ acdeq constituent <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:value-of select="."/> </xsl:with-param> </xsl:call-template> <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:value-of select="."/> </xsl:with-param> </xsl:call-template> <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:value-of select="."/> </xsl:with-param> </xsl:call-template> <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:value-of select="."/> </xsl:with-param> </xsl:call-template> code marcgac lcsh lcshac mesh nal csh rvm aq cdnp abcdeqnp <xsl:call-template name="chopPunctuation"> <xsl:with-param name="chopString"> <xsl:call-template name="subfieldSelect"> <xsl:with-param name="codes">adfhklor</xsl:with-param> </xsl:call-template> </xsl:with-param> </xsl:call-template> abcd abcd bc yes Arabic Latin Chinese, Japanese, Korean Cyrillic Hebrew Greek summary or subtitle sung or spoken text libretto table of contents accompanying material translation summary or subtitle sung or spoken text libretto table of contents accompanying material translation abcdefghijklmnopqrstuvwxyz .:,;/ .:,;/] Warning: string contains a character that is out of range! Substituting "?". 63
$$ WHERE name = 'mods33'; INSERT INTO config.global_flag (name, value, enabled, label) VALUES ( 'opac.browse.warnable_regexp_per_class', '{"title": "^(a|the|an)\\s"}', FALSE, oils_i18n_gettext( 'opac.browse.warnable_regexp_per_class', 'Map of search classes to regular expressions to warn user about leading articles.', 'cgf', 'label' ) ), ( 'opac.browse.holdings_visibility_test_limit', '100', TRUE, oils_i18n_gettext( 'opac.browse.holdings_visibility_test_limit', 'Don''t look for more than this number of records with holdings when displaying browse headings with visible record counts.', 'cgf', 'label' ) ); ALTER TABLE metabib.browse_entry DROP CONSTRAINT browse_entry_value_key; ALTER TABLE metabib.browse_entry ADD COLUMN sort_value TEXT; DELETE FROM metabib.browse_entry_def_map; -- Yeah. DELETE FROM metabib.browse_entry WHERE sort_value IS NULL; ALTER TABLE metabib.browse_entry ALTER COLUMN sort_value SET NOT NULL; ALTER TABLE metabib.browse_entry ADD UNIQUE (sort_value, value); DROP TRIGGER IF EXISTS mbe_sort_value ON metabib.browse_entry; CREATE INDEX browse_entry_sort_value_idx ON metabib.browse_entry USING BTREE (sort_value); -- NOTE If I understand ordered indices correctly, an index on sort_value DESC -- is not actually needed, even though we do have a query that does ORDER BY -- on this column in that direction. The previous index serves for both -- directions, and ordering in an index is only helpful for multi-column -- indices, I think. See http://www.postgresql.org/docs/9.1/static/indexes-ordering.html -- CREATE INDEX CONCURRENTLY browse_entry_sort_value_idx_desc -- ON metabib.browse_entry USING BTREE (sort_value DESC); CREATE TYPE metabib.flat_browse_entry_appearance AS ( browse_entry BIGINT, value TEXT, fields TEXT, authorities TEXT, sources INT, -- visible ones, that is row_number INT, -- internal use, sort of accurate BOOL, -- Count in sources field is accurate? Not -- if we had more than a browse superpage -- of records to look at. pivot_point BIGINT ); CREATE OR REPLACE FUNCTION metabib.browse_pivot( search_field INT[], browse_term TEXT ) RETURNS BIGINT AS $p$ DECLARE id BIGINT; BEGIN SELECT INTO id mbe.id FROM metabib.browse_entry mbe JOIN metabib.browse_entry_def_map mbedm ON ( mbedm.entry = mbe.id AND mbedm.def = ANY(search_field) ) WHERE mbe.sort_value >= public.search_normalize(browse_term) ORDER BY mbe.sort_value, mbe.value LIMIT 1; RETURN id; END; $p$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION metabib.staged_browse( query TEXT, fields INT[], context_org INT, context_locations INT[], staff BOOL, browse_superpage_size INT, count_up_from_zero BOOL, -- if false, count down from -1 result_limit INT, next_pivot_pos INT ) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$ DECLARE curs REFCURSOR; rec RECORD; qpfts_query TEXT; result_row metabib.flat_browse_entry_appearance%ROWTYPE; results_skipped INT := 0; row_counter INT := 0; row_number INT; slice_start INT; slice_end INT; full_end INT; all_records BIGINT[]; superpage_of_records BIGINT[]; superpage_size INT; BEGIN IF count_up_from_zero THEN row_number := 0; ELSE row_number := -1; END IF; OPEN curs FOR EXECUTE query; LOOP FETCH curs INTO rec; IF NOT FOUND THEN IF result_row.pivot_point IS NOT NULL THEN RETURN NEXT result_row; END IF; RETURN; END IF; -- Gather aggregate data based on the MBE row we're looking at now SELECT INTO all_records, result_row.authorities, result_row.fields ARRAY_AGG(DISTINCT source), ARRAY_TO_STRING(ARRAY_AGG(DISTINCT authority), $$,$$), ARRAY_TO_STRING(ARRAY_AGG(DISTINCT def), $$,$$) FROM metabib.browse_entry_def_map WHERE entry = rec.id AND def = ANY(fields); result_row.sources := 0; full_end := ARRAY_LENGTH(all_records, 1); superpage_size := COALESCE(browse_superpage_size, full_end); slice_start := 1; slice_end := superpage_size; WHILE result_row.sources = 0 AND slice_start <= full_end LOOP superpage_of_records := all_records[slice_start:slice_end]; qpfts_query := 'SELECT NULL::BIGINT AS id, ARRAY[r] AS records, ' || '1::INT AS rel FROM (SELECT UNNEST(' || quote_literal(superpage_of_records) || '::BIGINT[]) AS r) rr'; -- We use search.query_parser_fts() for visibility testing. -- We're calling it once per browse-superpage worth of records -- out of the set of records related to a given mbe, until we've -- either exhausted that set of records or found at least 1 -- visible record. SELECT INTO result_row.sources visible FROM search.query_parser_fts( context_org, NULL, qpfts_query, NULL, context_locations, 0, NULL, NULL, FALSE, staff, FALSE ) qpfts WHERE qpfts.rel IS NULL; slice_start := slice_start + superpage_size; slice_end := slice_end + superpage_size; END LOOP; -- Accurate? Well, probably. result_row.accurate := browse_superpage_size IS NULL OR browse_superpage_size >= full_end; IF result_row.sources > 0 THEN -- We've got a browse entry with visible holdings. Yay. -- The function that calls this function needs row_number in order -- to correctly order results from two different runs of this -- functions. result_row.row_number := row_number; -- Now, if row_counter is still less than limit, return a row. If -- not, but it is less than next_pivot_pos, continue on without -- returning actual result rows until we find -- that next pivot, and return it. IF row_counter < result_limit THEN result_row.browse_entry := rec.id; result_row.value := rec.value; RETURN NEXT result_row; ELSE result_row.browse_entry := NULL; result_row.authorities := NULL; result_row.fields := NULL; result_row.value := NULL; result_row.sources := NULL; result_row.accurate := NULL; result_row.pivot_point := rec.id; IF row_counter >= next_pivot_pos THEN RETURN NEXT result_row; RETURN; END IF; END IF; IF count_up_from_zero THEN row_number := row_number + 1; ELSE row_number := row_number - 1; END IF; -- row_counter is different from row_number. -- It simply counts up from zero so that we know when -- we've reached our limit. row_counter := row_counter + 1; END IF; END LOOP; END; $p$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION metabib.browse( search_field INT[], browse_term TEXT, context_org INT DEFAULT NULL, context_loc_group INT DEFAULT NULL, staff BOOL DEFAULT FALSE, pivot_id BIGINT DEFAULT NULL, result_limit INT DEFAULT 10 ) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$ DECLARE core_query TEXT; back_query TEXT; forward_query TEXT; pivot_sort_value TEXT; pivot_sort_fallback TEXT; context_locations INT[]; browse_superpage_size INT; results_skipped INT := 0; back_limit INT; back_to_pivot INT; forward_limit INT; forward_to_pivot INT; BEGIN -- First, find the pivot if we were given a browse term but not a pivot. IF pivot_id IS NULL THEN pivot_id := metabib.browse_pivot(search_field, browse_term); END IF; SELECT INTO pivot_sort_value, pivot_sort_fallback sort_value, value FROM metabib.browse_entry WHERE id = pivot_id; -- Bail if we couldn't find a pivot. IF pivot_sort_value IS NULL THEN RETURN; END IF; -- Transform the context_loc_group argument (if any) (logc at the -- TPAC layer) into a form we'll be able to use. IF context_loc_group IS NOT NULL THEN SELECT INTO context_locations ARRAY_AGG(location) FROM asset.copy_location_group_map WHERE lgroup = context_loc_group; END IF; -- Get the configured size of browse superpages. SELECT INTO browse_superpage_size value -- NULL ok FROM config.global_flag WHERE enabled AND name = 'opac.browse.holdings_visibility_test_limit'; -- First we're going to search backward from the pivot, then we're going -- to search forward. In each direction, we need two limits. At the -- lesser of the two limits, we delineate the edge of the result set -- we're going to return. At the greater of the two limits, we find the -- pivot value that would represent an offset from the current pivot -- at a distance of one "page" in either direction, where a "page" is a -- result set of the size specified in the "result_limit" argument. -- -- The two limits in each direction make four derived values in total, -- and we calculate them now. back_limit := CEIL(result_limit::FLOAT / 2); back_to_pivot := result_limit; forward_limit := result_limit / 2; forward_to_pivot := result_limit - 1; -- This is the meat of the SQL query that finds browse entries. We'll -- pass this to a function which uses it with a cursor, so that individual -- rows may be fetched in a loop until some condition is satisfied, without -- waiting for a result set of fixed size to be collected all at once. core_query := ' SELECT mbe.id, mbe.value, mbe.sort_value FROM metabib.browse_entry mbe WHERE EXISTS (SELECT 1 FROM metabib.browse_entry_def_map mbedm WHERE mbedm.entry = mbe.id AND mbedm.def = ANY(' || quote_literal(search_field) || ') ) AND '; -- This is the variant of the query for browsing backward. back_query := core_query || ' mbe.sort_value <= ' || quote_literal(pivot_sort_value) || ' ORDER BY mbe.sort_value DESC, mbe.value DESC '; -- This variant browses forward. forward_query := core_query || ' mbe.sort_value > ' || quote_literal(pivot_sort_value) || ' ORDER BY mbe.sort_value, mbe.value '; -- We now call the function which applies a cursor to the provided -- queries, stopping at the appropriate limits and also giving us -- the next page's pivot. RETURN QUERY SELECT * FROM metabib.staged_browse( back_query, search_field, context_org, context_locations, staff, browse_superpage_size, TRUE, back_limit, back_to_pivot ) UNION SELECT * FROM metabib.staged_browse( forward_query, search_field, context_org, context_locations, staff, browse_superpage_size, FALSE, forward_limit, forward_to_pivot ) ORDER BY row_number DESC; END; $p$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION metabib.browse( search_class TEXT, browse_term TEXT, context_org INT DEFAULT NULL, context_loc_group INT DEFAULT NULL, staff BOOL DEFAULT FALSE, pivot_id BIGINT DEFAULT NULL, result_limit INT DEFAULT 10 ) RETURNS SETOF metabib.flat_browse_entry_appearance AS $p$ BEGIN RETURN QUERY SELECT * FROM metabib.browse( (SELECT COALESCE(ARRAY_AGG(id), ARRAY[]::INT[]) FROM config.metabib_field WHERE field_class = search_class), browse_term, context_org, context_loc_group, staff, pivot_id, result_limit ); END; $p$ LANGUAGE PLPGSQL; UPDATE config.metabib_field SET xpath = $$//mods32:mods/mods32:relatedItem[@type="series"]/mods32:titleInfo[@type="nfi"]$$, browse_sort_xpath = $$*[local-name() != "nonSort"]$$, browse_xpath = NULL WHERE field_class = 'series' AND name = 'seriestitle' ; UPDATE config.metabib_field SET xpath = $$//mods32:mods/mods32:titleInfo[mods32:title and not (@type)]$$, browse_sort_xpath = $$*[local-name() != "nonSort"]$$, browse_xpath = NULL, browse_field = TRUE WHERE field_class = 'title' AND name = 'proper' ; UPDATE config.metabib_field SET xpath = $$//mods32:mods/mods32:titleInfo[mods32:title and (@type='alternative-nfi')]$$, browse_sort_xpath = $$*[local-name() != "nonSort"]$$, browse_xpath = NULL WHERE field_class = 'title' AND name = 'alternative' ; UPDATE config.metabib_field SET xpath = $$//mods32:mods/mods32:titleInfo[mods32:title and (@type='uniform-nfi')]$$, browse_sort_xpath = $$*[local-name() != "nonSort"]$$, browse_xpath = NULL WHERE field_class = 'title' AND name = 'uniform' ; UPDATE config.metabib_field SET xpath = $$//mods32:mods/mods32:titleInfo[mods32:title and (@type='translated-nfi')]$$, browse_sort_xpath = $$*[local-name() != "nonSort"]$$, browse_xpath = NULL WHERE field_class = 'title' AND name = 'translated' ; -- This keeps extra terms like "creator" out of browse headings. UPDATE config.metabib_field SET browse_xpath = $$//*[local-name()='namePart']$$ -- vim */ WHERE browse_field AND browse_xpath IS NULL AND field_class = 'author'; INSERT INTO config.org_unit_setting_type ( name, label, grp, description, datatype ) VALUES ( 'opac.browse.pager_shortcuts', 'Paging shortcut links for OPAC Browse', 'opac', 'The characters in this string, in order, will be used as shortcut links for quick paging in the OPAC browse interface. Any sequence surrounded by asterisks will be taken as a whole label, not split into individual labels at the character level, but only the first character will serve as the basis of the search.', 'string' ); COMMIT; \qecho This is a browse-only reingest of your bib records. It may take a while. \qecho You may cancel now without losing the effect of the rest of the \qecho upgrade script, and arrange the reingest later. \qecho . SELECT metabib.reingest_metabib_field_entries(id, TRUE, FALSE, TRUE) FROM biblio.record_entry;