[OPEN-ILS-DEV] Parts label cleanup

[Thomas Berezansky]

MVLC has some scripts I have used. Apparently I have shared them via  
pastebin before (Feb of 2014, even), so here is a link:

http://pastebin.com/eitN8KMx

Quoting Josh Stompro <stomproj at exchange.larl.org>:

> Hello, I've just noticed that our parts labels are somewhat messed  
> up because different locations have been using different formats for  
> entering month abbreviations.  So for one record we have "AUG", "AUG  
> ", "Aug","August","aug."  all being used.  This makes the process of  
> placing a request for a specific part much more exciting for users  
> since instead of 12 options they have <num variants>x12 to choose  
> from in the dropdown.
>
> Has anyone gone through a cleanup of their part labels before?   
> Would you have any scripts that I can take a look at?
>
> I've been trying to figure out if this can be done purely in sql or  
> if a script is needed.  I'm guessing that a script will be needed  
> since in some cases the correct label entry will exist, so the  
> asset.copy_part_map will need to be modified to point to the correct  
> label, and in other cases the biblio.monographic_part entry can just  
> be updated.  Along with specifying the mapping patterns.
>
> Although maybe just handling the two cases separately would work  
> fine.  Something like.
> "-update labels where there is no correct entry.
> UPDATE
> biblio.monograph_part AS bmp SET
> label='JAN'
> FROM
> (
> SELECT DISTINCT ON (bmp.record)
> bmp.id, bmp.record
> FROM
> biblio.monograph_part AS bmp
> WHERE
> bmp.label IN ('jan.','JAN ','jan','JANUARY','Jan')
> AND NOT EXISTS (
> SELECT id
> FROM biblio.monograph_part
> WHERE label='JAN' AND record=bmp.record)
> ORDER BY bmp.record
> ) AS sub
> WHERE
> bmp.id=sub.id
> ;
>
> Then figure out how to do the same for the case where the correct  
> label already exists.  Update asset.copy_part_map to point to the  
> correct entry.  Then look for holds that were referencing the  
> non-standard part labels and update them.  And finally delete all  
> the biblio.monograph_part entries that are not referenced in  
> asset.copy_part_map for cleanup.
>
> If anyone has any suggestions I would appreciate it.
> Thanks
> Josh


-- 
Thomas Berezansky
Assistant Network Administrator
Merrimack Valley Library Consortium
4 High ST, Suite 175
North Andover, MA 01845
Phone: 978-557-8161