For some unfathomable reason, I’m thinking about resurrecting the Musicwhore.org artist directory. That thing was a pain in the butt to maintain, and it burned me out.

So why bring it back? Well, this time around, I have no intention of maintaining it. That is, I’m not going to spend my spare time doing data entry.

Rather, I want the directory to be powered by Musicbrainz.

I think some of my work is a tad more thorough than Musicbrainz, but they have breadth, depth and the technology to merge more easily with freedb. Since the content is open to the public domain, I can adapt what I’ve done with what the Musicbrainz community has done.

And it’ll be easier to incorporate some of the new stuff I’m writing now on Musicwhore.org proper.

There are, however, a number of obstacles.

  • Musicbrainz data dumps are in PostgreSQL.
  • Musicwhore.org is powered by MySQL.
  • I develop at home on Windows, while Musicbrainz and the web host for Musicwhore.org run UNIX.

At first, I tried using some of the PERL bindings from the client/software development kit. ActiveState doesn’t store any of the Musicbrainz modules listed over at CPAN, and the PPD files out there keep giving me errors. So I had to compile those. Lucky for me I have Visual Studio 6 installed.

After I went through all that drama, I ran across a module called AudioFile::Identify::Musicbrainz, which pretty much precluded the need to compile the existing Musicbrainz modules. D’oh.

Unfortunately, AudioFile::Identify::Musicbrainz has a somewhat narrow purpose. I couldn’t use it as a web service.

So I spent the first half of the day trying to set up PostgreSQL on Windows to browse a dump of the database. I hit a major wall with CVS — I used CVS very briefly at a job that won’t be named, but even then, I didn’t have to contend with WinCVS. I went so far as to install Cygwin (nice!) but at that point, I knew I didn’t want to have to go through all these steps setting up a PostgreSQL development environment at home.

It was that point when I found a throwaway paragraph in the installation instructions. Evidently, it was possible to use three commands to load the PostgreSQL dump in MySQL. As I would discover, it wasn’t necessarily that easy.

To wit, the commands are as follows:

mysqladmin create musicbrainz

mysql musicbrainz < CreateTables.sql

mysqlimport musicbrainz mbdump/*

In Windows, I used phpMyAdmin to create the musicbrainz database. When I tried to load <a href="CreateTables.sql with the mysql command, I received a number of errors.

Naturally, MySQL and PostgreSQL differ slightly in syntax, so I had to spend another hour or so searching the web for PostgreSQL-to-MySQL syntax conversion. There were a lot of links to migrate from MySQL, not to MySQL. I found one site that did a rough conversion, but there were still a few things to clean up manually (comments, some array-styled fields).

I have an old version of MySQL installed on my computer at work, and it gave me hassles for trying to use mysqlimport. So I had to use phpMyAdmin again. Eventually, I managed to put a Musicbrainz dump on MySQL.

So for posterity I’m going to list the steps I took to make this whole thing happen:

  1. Download a dump of the Musicbrainz database core.
  2. Uncompress the dump with bunzip and tar. Uncompressed, the dump is about 690MB.
  3. Download CreateTables.sql from the Musicbrainz CVS server.
  4. Edit CreateTables.sql to use MySQL syntax. You can sift it through a conversion script, edit it by hand or do both.
  5. Remove any CREATE queries from CreateTables.sql that aren’t included in the dump. There are a number of tables included in the SQL file that require a separate dump. Don’t bother with them.
  6. Load CreateTables.sql:

    mysql musicbrainz < CreateTables.sql

  7. If you can’t use mysqlimport for some reason or other, build another SQL file — let’s call it LoadMB.sql — with a series of separate LOAD INTO FILE queries for each dumped file. Each record is tab-delimted and each dumped file is named after its table.

    LOAD DATA LOCAL INFILE 'C:\\Path\\to\\mbdump\\album' REPLACE INTO TABLE `album` FIELDS TERMINATED BY '\t' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'

  8. Import the dump into MySQL:

    mysql musicbrainz < LoadMB.sql

Of course, remember to use the --host, --user, and --password options when you run mysqladmin, mysql and mysqlimport.

After I got the Musicbrainz dump loaded into MySQL, I ran a few queries on it, edited some Musicwhore.org pages to connect to it … looks promising. But there’s still a lot of work to be done resolving my database structure with Musicbrainz.

Still … it feels kind of neat knowing I could perhaps bring back the artist directory in a manner far more powerful than what it originally was.