Aggregating ISBNs From Horizon

I’m working on creating a spreadsheet that will be imported into another application. The format for the spreadsheet is fairly picky, as it expects to match titles, authors, ISBNs, etc. The problem is that it wants each bib to be a new line.

Sounds simple, right? Well, when you look beneath the hood (this is a Horizon ILS using a SQL Server backend), each ISBN (and author, and UPC, etc) is a separate line in the database. This is great from a normalization standpoint, but despite my many years of writing SQL queries for SQL Server, this never ceases to amaze me how much of a pain in the butt it can be.

MySQL has an awesome GROUP_CONCAT function that handles this perfectly. MSSQL does not.

Here’s a bit of code I used to make this work. You can easily modify it to pull off of any MARC field. My actual implementation uses the 592 note field to look for a tag that flags the title as being an upcoming bestseller for the next season, but for demonstration purposes, I made a couple examples that should be easier to follow.

592 Note Field

SELECT bib, LEFT(isbn_list, LEN(isbn_list)-1) AS isbn_list
FROM (
	SELECT bib, (
		SELECT isbn.processed + ';' AS [text()]
		FROM isbn
		WHERE isbn.bib# = bibs_to_use.bib
		FOR XML PATH('')
	) as isbn_list
	FROM (
		SELECT b.bib# bib
		FROM bib b
		WHERE b.tag='592'
			AND b.text LIKE '%New and Forthcoming Fiction Winter 2011%'
	) as bibs_to_use
) as outter_query;

082 Call Number

SELECT bib, LEFT(isbn_list, LEN(isbn_list)-1) AS isbn_list
FROM (
	SELECT bib, (
		SELECT isbn.processed + ';' AS [text()]
		FROM isbn
		WHERE isbn.bib# = bibs_to_use.bib
		FOR XML PATH('')
	) as isbn_list
	FROM (
		SELECT b.bib# bib
		FROM bib b
		WHERE b.tag='082'
			AND b.text LIKE '%005%'
	) as bibs_to_use
) as outter_query;

260 Publisher

SELECT bib, LEFT(isbn_list, LEN(isbn_list)-1) AS isbn_list
FROM (
	SELECT bib, (
		SELECT isbn.processed + ';' AS [text()]
		FROM isbn
		WHERE isbn.bib# = bibs_to_use.bib
		FOR XML PATH('')
	) as isbn_list
	FROM (
		SELECT b.bib# bib
		FROM bib b
		WHERE b.tag='260'
			AND b.text LIKE '%Little, Brown and Co%'
	) as bibs_to_use
) as outter_query;

To be fair, I used an old example from a blog called Rational Relational and then converted that over to our needs in Horizon.

Update – 21 Jan 11

Here’s a little update that adds in a similar method for authors. It is very common to need to list authors in a comma seperated list, and it was a little tricky for me, so I’d assume it could be very tough for some readers.

SELECT bib, LEFT(isbn_list, LEN(isbn_list)-1) AS isbn_list,LEFT(author_list, LEN(author_list)-1) AS author_list
FROM (
	SELECT bib, (
		SELECT isbn.processed + ';' AS [text()]
		FROM isbn
		WHERE isbn.bib# = bibs_to_use.bib
		FOR XML PATH('')
	) as isbn_list, (
		SELECT a.processed + ';' AS [text()]
		FROM bib_auth ba, author a
		WHERE ba.bib# = bibs_to_use.bib
			AND a.auth# = ba.auth#
		FOR XML PATH('')
	) as author_list
	FROM (
		SELECT b.bib# bib
		FROM bib b
		WHERE b.tag='260'
			AND b.text LIKE '%Little, Brown and Co%'
	) as bibs_to_use
) as outter_query;

Pretty Search Results

Something that I do quite commonly is link to a search result. This might be linking to a collection, linking to a subject heading, linking to multiple subject headings, or linking to something that is heavily faceted.

This can often leave a less then desirable title on the page. Take this example. I started with an Advanced search for language of Italian. I’ve then faceted by format DVD, and by Italian as a primary language (as opposed to English materials with Italian subtitles).

Screen Shot Of Result Before Changes

This is a pretty cool link to have on a page for newcomers, or a page for ESL patrons. You’ll notice though, that the page’s title ends up being the initial advanced search string: language:”ita”. Not very useful.

To make this more user friendly, all you need to do as append a title parameter to the URL. Your browser will likely replace spaces with %20 characters automatically, so you’ll want to visit the page before using it for a link. Here is the end result of the example search, with a nice title.

Screen Shot Of Example With New Title

For me, it’s always been the small details that make or break a website’s design, but for some, this bit of accessibility will make a world of difference for their experience. This quick one will help with both of those for your site!

Get Jacket Covers From BC API

Here is a little function for getting jacket covers from the BiblioCommons API.

I keep a folder with functions like this, so that I’m never re-writing code to retreive data from the API. It’s also handy in the event that something changes with the API, be it the data returned, or the format of the call.

The Code

function getJacket($bib_id, $min_width = 150) {
	$timeout = 3;
	$bib_id = trim(mysql_escape_string($bib_id));

	$ch = curl_init();
	curl_setopt($ch, CURLOPT_URL, 'http://my_library.bibliocommons.com/api/ContentService/imageurl/' . urlencode($bib_id) .'/'. urlencode($min_width) .'/150');
	curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
	curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, $timeout);
	$result = curl_exec($ch);
	curl_close($ch);
	 
	$xml = simplexml_load_string($result);
	if($xml) {
		return $xml;
	} else {
		return 'images/book.jpg';
	}
}

Things To Remember

Remember to change my_library to your libraries sub-domain on BiblioCommons. If you are using this as an app developer, or a plugin/widget/add-on/module developer, you can swap out that entire line for this (making sure to set, or pass in, a $bc_key variable):

curl_setopt($ch, CURLOPT_URL, 'http://api.bibliocommons.com/api/ContentService/imageurl/' . urlencode($bib_id) .'/'. urlencode($min_width) .'/150?api_key=' . $bc_key);

This function requires that you have cURL installed and working correctly on your server. I use cURL to fetch the XML because then I am able to set a timeout.

You can change the $timeout variable to be whatever you want. I find 3 seconds is reasonable for my uses (including the front page of my library’s website).

The $bib_id variable is expecting a BiblioCommons item ID. This item ID can be made by appending your BiblioCommons library ID to the end of a Horizon item ID. So, my library ID is 001. I can make an item ID for Horizon item ID X as X001. Mileage may vary for non Horizon libraries.

Depending on the circumstance, an easier way to get an item ID is to pull it right from its URL in the catalog. Here is the URL to a title: http://opl.bibliocommons.com/item/show/612436001_harry_potter_and_the_deathly_hallows. The BC item ID is 612436001.

How It Works

This function calls the API imageurl function, passing in a few variables from our getJacket function call. We pass in the bib ID, and the width that we want the jackets set to.

The API call is fetched through cURL. If we don’t receive the results before our timeout, we will return a generic cover called book.jpg (feel free to change this line to match your path). The API may be slow, it may be down, your web server may be having issues with connection, DNS, or any other number of problems. For this reason, we set a timeout.

When I originally created this function, I had no timeout (and no cURL dependency). Every time we had an issue, this function would crash. This would cause the entire right column of our homepage, and countless other pages site wide, to not load. It would also cause the footer to not show up, as anything after this call would not load.

If the XML is returned, and loaded properly, we will return the URL that we receive. This may be for Amazon or Syndetics depending on your library’s setup. In the future, who knows where else these could come from (IMDB? LibraryThing?). We let the API handle that, and just interface with the API.

Calling the Function

Here’s two examples of how to call this function, both yielding the same results.

include('bc-api/get-item-jacket-function.php');

echo '<img alt="Books Title Here" src="';
echo getJacket($bib_id);
echo '">';
include('bc-api/get-item-jacket-function.php');
$jacket_url = getJacket($bib_id);

echo '<img alt="Books Title Here" src="' . $jacket_url . '">';

Edit/Update

After posting this, I received an email from one of the main developers at BiblioCommons (maybe the main developer, not sure your title Marty!). It turns out that this portion of the API won’t likely be available for your library. It will not be available in the “new” API, for select developers. For backwards compatibility, a few libraries will still have access to it, but it may not be available forever. Forward looking, any new libraries coming online with the service will not have access to this call.

I want this to be very clear for everyone that they won’t likely have access for it, I don’t want to mislead anyone. I will leave this post up, however, for those of us who do have it.