Random useful Evergreen database queries

Posted on Fri 02 July 2010 in Libraries

Occasionally I drop down to the database level to generate some reporting information. You could probably get the same information through the reporter but I like the precision of SQL. Here are a couple of queries that I've put together recently.

List titles for periodicals published by "Human Kinetics" with subscriptions owned by library ID "OSUL"

SELECT rsr.id, rsr.title   FROM metabib.full_rec mfr    INNER JOIN metabib.rec_descriptor mrd ON mfr.record = mrd.record    INNER JOIN asset.call_number acn ON acn.record = mrd.record    INNER JOIN reporter.super_simple_record rsr ON rsr.id = mrd.record    INNER JOIN actor.org_unit aou ON aou.id = acn.owning_lib  WHERE mfr.tag = '260'     AND mfr.subfield = 'b'    AND mfr.value ilike 'Human Kinetics%'    AND mrd.bib_level = 's'    AND aou.shortname = 'OSUL';

Strip out URLs for an online resource to which we no longer subscribe

Occasionally we drop subscriptions to an online resource that we happened to catalogue with an inline 856 field. Our new approach relies on just-in-time results from our link resolver to display accurate access to online resources (or at least consistent representations of what we have access to!), but our legacy records placed all of that information directly in the 856 field in the corresponding bibliographic record. The PostgreSQL regexp_replace() function lets you use regular expressions to match subsets of the MARC record and replace it with... well... nothing, in this case.

As we want to subsequently reingest the MARC records, and we're not running Evergreen trunk yet in which a reingest will automatically be triggered by an update to the biblio.record_entry table, I first push the list of affected IDs into a scratch table. This also lets me put limits on the MARC records that I'm going to touch, so that I don't inadvertently destroy content in another library's set of bibliographic records.

CREATE TABLE scratchpad.urls_to_delete (id BIGINT);INSERT INTO scratchpad.urls_to_delete   SELECT acn.record    FROM asset.uri au      INNER JOIN asset.uri_call_number_map aucnm ON au.id = aucnm.uri      INNER JOIN asset.call_number acn ON aucnm.call_number = acn.id      INNER JOIN actor.org_unit aou ON acn.owning_lib = aou.id    WHERE au.href ILIKE '%/search.ebscohost.com/direct.asp?db=rch%'      AND aou.shortname = 'OSUL';BEGIN; UPDATE biblio.record_entry  SET marc = regexp_replace(    marc,    E'<datafield tag="856" ind1="4" ind2="0"><subfield code="z">Available online from Ebsco.*?search.ebscohost.com/direct.asp\\?db=rch.*?</datafield>',    ''  )  WHERE id IN (SELECT id FROM scratchpad.urls_to_delete);

Note that the UPDATE statement is preceded by a BEGIN statement so that we can check our results and issue a ROLLBACK if we inadvertently changed too much, or created mangled records. Once you check your work with a SELECT statement or two, you can issue a COMMIT statement to make the changes take effect.

Evergreen