Julia Ridgely

Date: Nov. 24, 2009

Almost 30 years after the release of their last original album, the Beatles’ career continues to hum along. EMI Musics’s release of a re-mastered catalogue on 9/9/09 sold 2.5 million copies in the first five days alone. The band, which broke up in 1970, wields chart-busting power that few contemporary artists can equal.

The EMI set includes their 12 “official” British studio releases, but the Beatles catalogue features dozens of albums, including greatest hits, foreign issues, and re-releases—a subject of constant discussion and controversy for Beatles fans. What’s in that catalogue? Let’s take a look in the MusicBrainz database using a few HTSQL queries. (MusicBrainz is a community music metadatabase with information on more than 700,00 recorded music releases.) To follow along, click on the queries, then click the "Go" button in the query box above to execute the query. When you're done, click "Reset" to continue.

First, we’ll create the basic album list by joining two tables, album and artist, using the foreign key they share in common (artist id). This query returns all rows in the album table where the artist name is “The Beatles":

http://musicdemo.htsql.com/album?artist.name='The Beatles'

(Believe it or not, not everyone loves the Beatles! Try substituting the name of your favorite artist in the URL. Remember to use single quotes around the name.)

There are a number of columns in the album table that aren’t very interesting from a user standpoint. Now that we can see the choices, let’s select the column that we really care about: album name. To do that, we add a selector to the name of the table. While we’re at it, we can add a + or – to sort in ascending or descending order. Give it a try:

http://musicdemo.htsql.com/album{name+}?artist.name='The Beatles'

What if we’d like to see the year each album was released? That information isn’t in any of the tables we’ve looked at so far. To find out where that information is stored, we can take a peek at the database schema:

http://musicdemo.htsql.com/diagram().svg

HTSQL provides a built-in way to do this, by dynamically generating an image. From this diagram, we can see that the release date column (releasedate) is part of the release table:

http://musicdemo.htsql.com/release

To browse any table, just click on its name. (Note that many of the tables contain “administrivia” related to the database itself and are not that interesting from a user standpoint. For your own HTSQL installation, you can choose to hide these from users.)

To save space and ensure uniqueness, most of the identifiers are numerical. To begin to make sense of this table, let’s sort it by release date, as well as get rid of a few columns that are only of interest to the database itself, using the same method as above. The result shows the release date, album ID, and country ID:

http://musicdemo.htsql.com/release{releasedate+,album,country}?album.artist.name='The Beatles'

Now we have a chronological list of all the Beatles’ releases. But it isn’t very readable! Let’s use HTSQL’s join function again to look up the IDs in the relevant tables. (If you forget the name of the column, just click on any of the ID numbers to see what’s in the tables.)

http://musicdemo.htsql.com/release{releasedate+,album.name,country.name}?album.artist.name='The Beatles'

We now have a chronological list of all the Beatles’ releases and the country in which they were released. It’s easy to imagine quickly writing an application that would accept the name of any artist and return a list of their releases—perhaps on a timeline. As a final step, we can generate the output in XML by adding .xml before the query. You can try the same trick using CSV, TXT, YAML or JSON instead of XML.

http://musicdemo.htsql.com/release{releasedate+,album.name,country.name}.xml?album.artist.name='The Beatles'

Seems pretty easy, doesn’t it? But take a moment to appreciate what you did. You executed SQL queries against a PostgreSQL database and generated the output in five different formats. To share your success, copy and paste the URL from the Query box or use one of the social media links at the right. To learn more about HTSQL, visit the product section of our website.