Using Access with the Musicbrainz Database

These instructions have been updated to work with the new postgresql database format.

1. Download and install postgresql for cygwin.
2. Download the zip file containing the database (compressed using bzip2) and the batch file.
3. Make a directory called \mbdump under the cygwin root directory (usually c:\cygwin), and copy the .sh file there.
4. Download the Musicbrainz database from the ftp site, and save it c:\cygwin.
5. Uncompress the database with bzip2 and tar
6. Run cygwin (usually cygwin.bat) in the c:\cygwin directory.
7. Start postgresql.
8. Run the doit.sh script.
9. Run the filefixer program that strips out everything but the data from the .tmp files. The program specifically looks for the c:\cygwin\mbdump directory, and will refuse to run if it doesn't exist.
10. Open musicbrainzdb00.mdb in Access, and run the macro called ImportFiles.

Access will delete any information in the tables, then import the new data from the downloaded file. The Track file should be the only table that gives you error messages. These can be safely ignored, and the tracks_import_errors table can be deleted.

Now comes the fun part. There are several pre-made queries that can be used to discover errors in the database. I suggest running the make-table first. The tables they create are great to browse through.

I discovered a checksum that is very useful in discovering bad albums. We first calculate the number of tracks on an album by counting the number of tracks listed in the Albumjoin table, grouped by album. Say an album has 13 tracks. These tracks should be numbered from 1 to 13. To create the checksum, just add 1+2+3..13, which equals 91. We take the Sequence value in the Albumjoin table, and add up the values for each album. That value is compared to the checksum value for the number of tracks.

This checksum is incorporated in the MakeCountOfTracksByAlbum and updatecorrectsum queries. The MakeCountOfTracksByAlbum query must be run before the updatecorrectsum query. After these are run, the countoftracksbyalbum table is ready to browse, either directly, or by using the badtrackcount queries.

Other Interesting Query Descriptions

• Update queries start with update, delete queries start with delete, and make table queries start with make
• The Albums with 2 duplicate sequences query displays all albums with one pair of duplicate track listings, i.e., two tracks with a track number of 3.
• The MP3names query selects all tracks with mp3 somewhere in the name.
• The Linux query selects all artists with Linux somewhere in the name that aren't marked as a Data CD.
• The tracklookup query takes a artist id, and returns all the albums and tracks associated with the artist
• The tracklookupalbum query takes an album id, and returns all the tracks and the artist associated with that album.
• The tracklookupalbumname query takes a album name, and returns all the tracks and the artist associated with that album name

When you find an error in the database, you can copy either the album or artist id, and paste it into TheID field in the ToBeFixed table. In the Album0Artist1 field, enter 0 if it's an album id, or 1 if it's an Artist id. Once you have several entries in ToBeFixed, you can run the MakeToBeFixedPage Macro. This creates \download\mbdump\ToBeFixed.htm that contains links to the records in the musicbrainz.org database which need to be fixed.