XSLT, SQL, and Delicious Library

Posted on by bkjones

Ok, I’m publicly committing myself to this pet project. I’m going to make it possible to take the data in Delicious Library’s xml backend and store it in a properly normalized database, so that other useful things can be done with the data.

Here’s the quandry I find myself in: I have a good number of technical books. I’d like to catalog them, but I see little use in cataloging them for my own use. I want to catalog them, then review them, then post the reviews on my technical web site. I also want the book cover art images to link off to Amazon, and if someone buys something I’ve reviewed via a link I’ve provided, I’d like to be paid by Amazon for the referral, so I can recover some of the cost of running the site without having ads all over the place.

Delicious Library doesn’t help me here. Where it *does* help me is with compiling the initial data set. It has a really really really slick barcode reader: I can just hold the book in front of the camera built into my macbook pro, and it automagically finds the book and adds it to my library.

So, I’m writing an XSLT stylesheet: xml2sql.xsl. When used with Delicious Library’s xml file, it will output sql suitable for use with (right now) MySQL. I will, in fact, attempt to make the SQL as generic as humanly possible, but I’m currently testing with MySQL. I worked on this for about 30 minutes last night and came up with this:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="text"/>
        <xsl:template match="/library">
            <xsl:for-each select="items">
                <xsl:for-each select="book">
                    <xsl:variable name="author" match="@author" />
                    <xsl:variable name="fullTitle" match="@fullTitle" />
                    <xsl:variable name="publisher" match="@publisher" />
                    TITLE:
                    <xsl:value-of select="@fullTitle"/>
                    AUTHOR(S):
                    <xsl:value-of select="@author"/>
                    PUBLISHER:
                    <xsl:value-of select="@publisher"/>

                </xsl:for-each>
            </xsl:for-each>
      </xsl:template>
</xsl:stylesheet>

If you save this to a file, then reference it from *A COPY OF* your Delicious Library file (which is, by default, ~/Library/Application\ Support/Delicious\ Library/Library Media Data.xml) by adding this line as the second linee in the file:

<?xml-stylesheet type=”text/xsl” href=”xml2sql.xsl”?>

then that xsl stylesheet will be used to parse that copy of your Library. I’m testing on a Mac using the following command:

xsltproc -v Library\ Media\ Data.xml

If you don’t want to alter the Library file, then you don’t *have* to add that line to your Library file. In that case you’d test using this command (assuming you named the stylesheet xml2sql.xsl):

xsltproc -v xml2sql.xsl Library Media Data.xml

That spits out some really ugly output, but the point right now is just to see that we’re getting data out of it at all. Keep in mind that this is like the second XSLT stylesheet I’ve ever composed, so have some patience.

If there’s interest in this, or ideas about how it might be done, let me know.

Technorati Tags: , , , , , ,