Hard time with requests on the VM Musicbrainz server

musicbrainz-server
postgresql
musicbrainz
Tags: #<Tag:0x00007f23c1dcc148> #<Tag:0x00007f23c1dd3f88> #<Tag:0x00007f23c1dd3dd0>

#1

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


#2

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


#3

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.


#4

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.


#5

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


#6

Well, this might help:

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).


#7

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?


#8

Well, it’s our ID for everything, but it’s not strictly internal :slight_smile: (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).