[OPEN-ILS-DEV] Cat: Maintain 001/003/035 according to theMARC21 specification

[Whalen, Liam]

> -----Original Message-----
> From: open-ils-dev-bounces at list.georgialibraries.org 
> [mailto:open-ils-dev-bounces at list.georgialibraries.org] On 
> Behalf Of Dan Scott
> Sent: December 2, 2011 13:47
> To: Evergreen Development Discussion List
> Subject: Re: [OPEN-ILS-DEV] Cat: Maintain 001/003/035 
> according to theMARC21 specification
> 
> On Fri, Dec 2, 2011 at 1:12 PM, Whalen, Liam 
> <Liam.Whalen at nrcan-rncan.gc.ca> wrote:
> > I am making some modifications to 035 tags in our records, 
> and if I do 
> > not have the Global Flag (Cat: Maintain 001/003/035 according to the
> > MARC21 specification) turned off, Evergreen will add a second 035 
> > entry to my record when I do my SQL update to change the 035 tags.  
> > This is not an issue, we can turn off the flag while these 
> updates are 
> > proceeding.
> >
> > However, if we turn the flag back on after the updates are complete 
> > will Evergreen try to modify these records again when they are 
> > interacted with at a later date?
...
> What are the values of the 001 and 003 in the record before 
> you apply the update? How are you making the SQL update 
> you're mentioning? Also, what version of Evergreen are you 
> on? There have been a number of updates to the 
> maintain_control_numbers() database function to address various bugs.
> 
> "\df+ maintain_control_numbers" will show you the code 
> underlying the function, if you want to see what you have and 
> compare to the latest version.
> 

The values of 001 seem to be the bib ids in a couple of the records that I have checked.
The values of 003 seem to vary.  Some times it is NRCAN-RNCAN, other times they can be various
ILL Codes.

This is the SQL code I'm using to do the update.

The specifications for this update are that I only change records that have items belonging to a single library.  If a record has items belonging to more than one library then I leave it alone.

UPDATE biblio.record_entry AS bre1
SET marc = regexp_replace(bre1.marc, E'(tag="035"[^>]*><subfield code="a">)\\(NRCAN-RNCAN\\)(([0-9]+)</subfield>)', E'\\1' || 
	(SELECT '(' || replace(ILL_Code, '"', '') || ')'
	FROM (SELECT DISTINCT aous.value AS ILL_Code
		FROM biblio.record_entry AS bre
		INNER JOIN asset.call_number AS acn
		ON bre.id = acn.record
		INNER JOIN asset.copy AS ac ON
		ac.call_number = acn.id
		INNER JOIN actor.org_unit AS aou
		ON aou.id = acn.owning_lib
		INNER JOIN actor.org_unit_setting AS aous
		ON aous.org_unit = aou.id
		WHERE bre.deleted = false AND bre.active = true
		AND aous.name = 'cat.marc_control_number_identifier'
		/* This says only grab the ILL Code
		for the id we are currently updating.  bre.id is the list of
		ids in this SELECT while bre1.id will be the id being handled
		in the UPDATE sequence*/
		AND bre.id = bre1.id) AS list2) 
	|| E'\\2')
WHERE bre1.id IN (SELECT bre2.id
	FROM biblio.record_entry AS bre2
	INNER JOIN asset.call_number AS acn
	ON bre2.id = acn.record
	INNER JOIN asset.copy AS ac ON
	ac.call_number = acn.id
	INNER JOIN metabib.real_full_rec AS mrfr
	ON mrfr.record = bre2.id
	WHERE bre2.deleted = false AND bre2.active = true
	AND mrfr.tag = '035' AND mrfr.subfield = 'a' AND mrfr.value ~* 'nrcan rncan'
	GROUP BY bre2.id 
	HAVING count(acn.owning_lib) = 1
	ORDER BY bre2.id
	LIMIT 1)

I have LIMIT 1 in the 'WHERE bre1.id. IN (SELECT' statement to only process one record at a time while I am testing this on our dev machine.  We are running Evergreen 2.1.1, Postgres 9.0.4, OpenSRF 2.0.1, and Ubuntu 10.04 on both our dev and production machines.

So, the maintain_control_numbers() function is what is triggered when the global flag is enabled?

Liam