#musicbrainz

hey guys, this is my sql query to get the tracks and their associated releases/release groups for all iranian singers

SELECT a.name, a.comment, t.name, rg.id

FROM artist a join artist_credit_name acn on a.id=acn.artist

join track t on t.artist_credit=acn.artist_credit

join medium m on t.medium=m.id

join release r on m.release=r.id

join release_group rg on r.release_group=rg.id

WHERE UPPER(a.comment) LIKE '%IRAN%' OR area=101 or Upper(a.comment) LIKE '%PERSIA%' ;

how could i extend this to get the composer/lyricist information?

that will require a lot of joins, so not sure it makes sense to do all in one query :)

they are stored in l_artist_work but to get the work you have to go through l_recording_work

bitmap ahh ok so like this? SELECT DIstinct a.name, a.comment, t.name,rec.id, artwork.*

FROM artist a join artist_credit_name acn on a.id=acn.artist

join track t on t.artist_credit=acn.artist_credit

join recording rec on t.recording=rec.id

join l_recording_work recwork on rec.id=recwork.entity0

join l_artist_work artwork on recwork.entity1=artwork.entity1

WHERE UPPER(a.comment) LIKE '%IRAN%' OR area=101 or Upper(a.comment) LIKE '%PERSIA%' ;

jazzi: I think so. you can probably skip the recording join, and just do t.recording=recwork.entity0. also, to filter the type of l_artist_work relationship, join link on link.id = artwork.link, and check that link.link_type in (165, 167, 168).

if you want the actual artist names / mbids, join the artist table a second time to l_artist_work.entity0

the link type IDs are listed in our documentation, e.g., https://musicbrainz.org/relationship/a255bca1-b...

165 = lyricist, 168 = composer

bitmap ahh ok that makes more sense, but im still confused where i can pull the link type from each of these artists.

SELECT DIstinct a.name, a.comment, a.area, t.name,rec.id, b.*

FROM artist a join artist_credit_name acn on a.id=acn.artist

join track t on t.artist_credit=acn.artist_credit

join recording rec on t.recording=rec.id

join l_recording_work recwork on rec.id=recwork.entity0

join l_artist_work artwork on recwork.entity1=artwork.entity1

join artist b on b.id=artwork.entity0

WHERE

a.comment ~* '\y(IRAN|IRANIAN|PERSIA|PERSIAN)\y'

OR a.area=101

i tried doing artist (b) .* but i dont see how to get the link type ID's

join link l on (l.id=l_artist_work.link AND l.link_type IN (165, 167, 168))

btw, for pasting long sql queries in irc, use pastebin.com to avoid spamming the channel :)

bitmap thank you so much it works now i think

oh im sorry ill do that next time

np

just one last question, sorry for all the questions... is my method of doing the Where clause above with the  ~ appropriate to get all the iranian/persian singers songs?

and also including the area code clause of iran

one improvement you could do is to use area_containment to check if a.area is a sub-area of iran

but if you are using a replicated mirror server, you may have to build this table...

join area_containment ac on ac.descendant = a.area, and then WHERE a.area = 101 OR ac.parent = 101 (I think)

are you using musicbrainz-docker?

bitmap i ahh i assume this is the line sudo docker-compose exec musicbrainz bash -c './admin/BuildMaterializedTables --database=MAINTENANCE all' to do this for the local postgres installation?

ill try that now ty

yup

you can replace 'all' with 'area_containment'

since you only need that table

right perfect

oh and is this appropriate for getting all the genres for the tracks? u gave me this advice yesterday on how to do it but i never checked if this was fully correct

looks reasonable

but instead of DISTINCT it might make the results easier to work with if you use GROUP BY and then array_agg(genre.name) ?

so there is not a duplicate row for each genre

oh i didnt know u could do that lol yeah thatd be better ty

bitmap hmm so more like this? https://pastebin.com/XLwvQb8t

sql is so confusing haha

oh also i just assumed the release_group_type was 1 for album, 2 for single and 3 for ep but maybe theres documentation on this? like im not sure what 11 means

yeah that looks better

you can strip NULL from the genres array with FILTER - array_agg(DISTINCT genre.name) FILTER (WHERE genre.name IS NOT NULL) AS genre_names

hmm, I'm not sure if we document the RG type IDs on the website, but you can just do `SELECT id, name FROM release_group_primary_type` to list them all

11 appears to be "Other"

right got u, i just had a scary thought if my queries were taking into consideration 'featured artists' or is that included in the links

those should be included in the track artist credit, if they were entered correctly

ah so track.artist credit will give me the artist ids who featured

then i just need to join that back to the artist to get there names

i might do this in a seperate query lol

bitmap that command to build the area containment didnt do anything. perhaps i need to change the database in the line to 'musicbrainz_db'? like this: sudo docker-compose exec musicbrainz bash -c './admin/BuildMaterializedTables --database=musicbrainz_db area_containment'

im just using the local postgres version of the database not the full server btw

did it log any output? if there is anything in the table in will be skipped, so maybe it was built already

the name passed to --database corresponds to an entry in DBDefs.pm (in the register_databases call)

so either MAINTENANCE or READWRITE should work

it didnt log any output no, but i also tried select * from area_containment and it returns empty

weird, try with READWRITE. you can also pass --force to the script so it builds even if it's non-empty

hmm ive tried both those changes still didn't work

i assume its not a major problem cause most iranian artists have the word iran or iranian in their comments?

atleast i hope haha

or are just area 101

oh maybe theres a way to get all the subareas and i could just add to the sql query manually one by one?

hmm, the command should log something though, are you running it from the musicbrainz-docker dir?

yeah i cd into cd musicbrainz-docker

and i do sudo docker ps by container shows up correctly

and i ran from there and it just outputs nothing goes back to my username

I at least get https://gist.github.com/mwiencek/4852b349aaf7d7...

oh do i need to enter the container first?

you shouldn't, but you can try (I'm running musicbrainz-server outside of docker, hence the different command)

maybe it will log something in an interactive terminal

~/musicbrainz-docker$ sudo docker exec -it 0c52e35228bc bash

root@0c52e35228bc:/# bash -c './admin/BuildMaterializedTables --database=MAINTENANCE area_containment'

bash: line 1: ./admin/BuildMaterializedTables: No such file or directory

srry for the spam... im making food at same time as this haha

huh, well maybe the command in the README is wrong

yvanzo ^?

perhaps try docker-compose exec --user musicbrainz musicbrainz bash -c '...'

ok i just tried that and i got no output and the table is still empty lol