How to get releases, recordings, artists related to instruments

MusicBrainz Development
1

Hey everyone,

I’m doing some music related research and have downloaded a dump of the musicbrainz core dataset and set up a postgres database. I’m looking to connect releases, recordings and artists to instruments through a sql query, but I am not able to determine which tables to use.

To give a better a sense of what I want, consider this page: String instrument “guitar” - MusicBrainz where you can get all kinds of relationships for that particular instrument.

Any sort of help would be appreciated.

1 Like
2

(Disclaimer: I’m just looking at the admin/sql/CreateTables.sql file.)

If I’m understanding correctly, I think that you’re looking for the following joins:

instrument.id ↔ l_artist_instrument.entity1
l_artist_instrument.entity0 ↔ artist.id

instrument.id ↔ l_instrument_recording.entity0
l_instrument_recording.entity1 ↔ recording.id

instrument.id ↔ l_instrument_release.entity0
l_instrument_release.entity1 ↔ release.id

In all three cases, you can use the following to see the relationship type:

l_<whatever>.id ↔ link.id
link.link_type ↔ link_type.id

Does that make sense?

2 Likes
3

It makes sense to me at least. See also the documentation (slightly off but on l_* tables):
https://musicbrainz.org/doc/MusicBrainz_Database/Schema

1 Like
4

Hey, thanks for the replies. The problem with those tables is that they are empty, i.e., are not in the musicbrainz dataset (Index of /pub/musicbrainz/data/fullexport/20230515-180030/). Maybe I’m missing something about where the data about those tables is supposed to be?

5

They are part of the full export (more specifically the file mbdump.tar.bz2). Note that these files are meant to be loaded using a local instance of MusicBrainz Server, see GitHub - metabrainz/musicbrainz-docker: Docker Compose project for the MusicBrainz Server with replication, search, and development setup.

2 Likes
6

I don’t think this reply is entirely correct. I’ve used mbslave GitHub - acoustid/mbslave: MusicBrainz Database Mirror to import the mbdump file into a postgres database (as I don’t need the whole musicbrainz setup). However, when you unzip mbdump, there are no files that are supposed to populate l_instrument_recording, etc. These are the l_* files in the unzipped version:
l_area_area
l_area_event
l_area_genre
l_area_instrument
l_area_recording
l_area_release
l_area_series
l_area_url
l_area_work
l_artist_artist
l_artist_event
l_artist_instrument
l_artist_label
l_artist_place
l_artist_recording
l_artist_release
l_artist_release_group
l_artist_series
l_artist_url
l_artist_work
l_event_event
l_event_place
l_event_recording
l_event_release
l_event_release_group
l_event_series
l_event_url
l_event_work
l_genre_genre
l_genre_instrument
l_genre_url
l_instrument_instrument
l_instrument_label
l_instrument_url
l_label_label
l_label_place
l_label_recording
l_label_release
l_label_release_group
l_label_series
l_label_url
l_label_work
l_place_place
l_place_recording
l_place_release
l_place_series
l_place_url
l_place_work
l_recording_recording
l_recording_release
l_recording_series
l_recording_url
l_recording_work
l_release_group_release_group
l_release_group_series
l_release_group_url
l_release_release
l_release_series
l_release_url
l_series_series
l_series_url
l_series_work
l_url_work
l_work_work

7

Indeed! :slight_smile: The instruments are not directly related to the recordings, for example. Sadly, it’s more complicated than that. Luckily, we ourselves get these lists via queries that you can probably use as a basis for your own:

1 Like
8

Thank you @reosarevok! I’ll definitely look into these.