DB query for all vinyl albums

MusicBrainz Development
1

I’m trying to generate a list of all vinyl albums for all artists.

I can get this for a specific artist as follows:

select a.id as artistid, a.name, r.id, r.name as album_name, r.packaging
from artist_credit_name
join artist a ON artist_credit_name.artist = a.id
join artist_credit ac ON artist_credit_name.artist_credit = ac.id
join release r ON r.artist_credit = ac.id
where r.artist_credit = 1352 /* 1352 is AC/DC */
and r.packaging = 12

I am using packaging=12 because I think this means vinyl, but it may be possible for “Gatefold Cover” to also apply to CDs.

Is there a way to find any vinyl-only albums for a given artist (or all artists)? Ultimately, I’m after all vinyl albums in the db.

Thanks!

2

Vinyl is a medium format, so you’ll need to look at all mediums in the release and see if they’re all vinyl (do you want vinyl specifically, or also other phonographs like shellac discs?). If you want only vinyl: 29 is the ID for 7 inch vinyl, 30 for 10 inch, 31 for 12 inch, and 7 for vinyl of an unspecified size.

2 Likes
3

That’s awesome. I think I have something usable here:

select distinct a.id as artist_id, a.name as artist_name, r.id as album_id, 
	r.name as album_name, rgm.first_release_date_year, mf.name, r.comment
from artist_credit_name
join artist a ON artist_credit_name.artist = a.id
join artist_credit ac ON artist_credit_name.artist_credit = ac.id
join release r ON r.artist_credit = ac.id
JOIN release_group rg ON r.release_group = rg.id
join release_group_meta rgm ON rgm.id = rg.id
join medium m ON m.release = r.id
join medium_format mf ON mf.id = m.format
where r.artist_credit = 154555
and m.format in (7,29,30,30,31,48)
order by rgm.first_release_date_year

Now I just need to figure out how to extract the cover art jpeg data. I see a mime_type text field, but I’m not sure how to get the binary data out. Any ideas?

I’m also doing a count on the number of vinyl albums in the database, and I’m getting just over 308,000. Does that sound right?

select count(r.id)
from artist_credit_name
join artist a ON artist_credit_name.artist = a.id
join artist_credit ac ON artist_credit_name.artist_credit = ac.id
join release r ON r.artist_credit = ac.id
JOIN release_group rg ON r.release_group = rg.id
join release_group_meta rgm ON rgm.id = rg.id
join medium m ON m.release = r.id
join medium_format mf ON mf.id = m.format
where m.format in (7,29,30,30,31,48)

Thanks again!

4

The cover art is on the Cover Art Archive.

Our statistics show 180,689 + 58,952 + 40,188 + 5,327 = 285,156 releases. You might need count distinct.

1 Like
5

select count (distinct r.id) got me 252,911 which seems accurate, given that I’m still waiting for replication to catch up on this new VM.

So for cover art, ignore the local database and continue to use the API calls I’ve been using, e.g. musicbrainzngs.get_image_front?

1 Like
6

I’d say so, yes :slight_smile: Don’t think there’s a better way (there’s a cover art database too, but you’d need to eventually call the Archive for the image anyway, so that’s probably still by far the easiest).

1 Like