Hard time with requests on the VM Musicbrainz server

MusicResearch · May 19, 2017, 9:14pm

Dear MetaBrainz community,

Thank you for your nice work, I really appreciate using MusicBrainz for my research!

I have a question concerning the MusicBrainz server on a Virtual Machine under VirtualBox. I have successfully installed it and I can make some postgresql requests. Unfortunately, I find that there is some missing information on the schema provided here:
https://musicbrainz.org/doc/MusicBrainz_Database/Schema
For example, I would like to join the ISRC table with the instrument table in order to gather the list of ISRC for each instrument. But I could not find how are this two tables linked and how can I join them? Could you point me to a complete database schema or give me the postgresql line of code that can do it?

Thank you by advance for your help!
Cheers

dns_server · May 20, 2017, 12:14am

You could take a look at the table creation script as a starting point.

github.com

metabrainz/musicbrainz-server/blob/master/admin/sql/CreateTables.sql

\set ON_ERROR_STOP 1
BEGIN;

CREATE TABLE alternative_release ( -- replicate
    id                      SERIAL, -- PK
    gid                     UUID NOT NULL,
    release                 INTEGER NOT NULL, -- references release.id
    name                    VARCHAR,
    artist_credit           INTEGER, -- references artist_credit.id
    type                    INTEGER NOT NULL, -- references alternative_release_type.id
    language                INTEGER NOT NULL, -- references language.id
    script                  INTEGER NOT NULL, -- references script.id
    comment                 VARCHAR(255) NOT NULL DEFAULT ''
    CHECK (name != '')
);

CREATE TABLE alternative_release_type ( -- replicate
    id                  SERIAL, -- PK
    name                TEXT NOT NULL,
    parent              INTEGER, -- references alternative_release_type.id

This file has been truncated. show original

reosarevok · May 20, 2017, 10:48am

ISRCs are for recordings (it’s the international recording code after all) - if you mean you want ISRCs for recordings where a specific instrument is performed, that’s a tricky query, but should involve joining ISRC, recording, l_artist_recording, link, link_attribute and possibly a few others.

MusicResearch · May 21, 2017, 8:07pm

Thank you both for your answers. I’ll try to see how I can get this big multiple join query and let you know! Meanwhile, if somebody has more info on how to do that I will happily take it!

I would like to know I can I know how musicbrainz did display the instruments for the recording displayed here:
https://musicbrainz.org/recording/e9c5b049-4bcd-4556-a86b-8759d1ac26fb
Can I have access to the sql query code that does display this page? If yes, I think it would be straightforward for me to solve this issue.

MusicResearch · May 21, 2017, 11:16pm

Yes that’s it, I want for each ISRC, the list of instruments used, just like displayed here:
https://musicbrainz.org/recording/e9c5b049-4bcd-4556-a86b-8759d1ac26fb

reosarevok · May 22, 2017, 6:14am

Well, this might help:

github.com

metabrainz/musicbrainz-server/blob/master/lib/MusicBrainz/Server/Data/Relationship.pm#L122


      
              $self->_check_types($type0, $type1);
          
              my $query = "SELECT * FROM l_${type0}_${type1} WHERE id = ?";
              my $row = $self->sql->select_single_row_hash($query, $id)
                  or return undef;
          
              return $self->_new_from_row($row, $type0, $type1);
          }
          
          sub get_by_ids
          {
              my ($self, $type0, $type1, @ids) = @_;
              $self->_check_types($type0, $type1);
          
              my $query = "SELECT * FROM l_${type0}_${type1} WHERE id IN (" . placeholders(@ids) . ')';
              my $rows = $self->sql->select_list_of_hashes($query, @ids) or return undef;
          
              return { map { $_->{id} => $self->_new_from_row($_, $type0, $type1) } @$rows };
          }
          
          sub _load

In your case l_${type0}_${type1} will always be l_artist_recording, and instead of directly loading the recording with a MBID you’ll need to reach it via the ISRC table (keep in mind one ISRC can be assigned to more than one MB recording, even though it’s rare).

MusicResearch · May 22, 2017, 11:58pm

Concerning the links, I’m still wondering what are the relationships:
From the table isrc I get to recording then to artist and then I should have a table that links the artist with instruments but l_artist_instrument is an empty table.

The mapping between ISRC and MBID is pretty straightforward:

And yes, I know and will keep in mind that multiple ISRC can be linked to one MBID, which is a kind of internal ISWC?

reosarevok · May 23, 2017, 5:46am

Well, it’s our ID for everything, but it’s not strictly internal (the BBC for example uses them publicly, and you can query Last.fm using them).

You don’t want that, you want l_artist_recording (and to get the artist from there too). Instruments here are attributes of the performer relationship, not direct relationships to the instrument entities (which is a bit annoying, but the schema doesn’t support real 3-point relationships so we can’t do l_artist_instrument_recording or something).