#musicbrainz

/

      • Souler has quit
      • 2023-11-01 30528, 2023

      • jeffbyrnes joined the channel
      • 2023-11-01 30546, 2023

      • petitminion has quit
      • 2023-11-01 30537, 2023

      • anonn has quit
      • 2023-11-01 30505, 2023

      • lusciouslover joined the channel
      • 2023-11-01 30521, 2023

      • Souler joined the channel
      • 2023-11-01 30522, 2023

      • iconoclasthero joined the channel
      • 2023-11-01 30521, 2023

      • gchound_ joined the channel
      • 2023-11-01 30523, 2023

      • gchound_ has quit
      • 2023-11-01 30507, 2023

      • antlarr has quit
      • 2023-11-01 30543, 2023

      • Souler has quit
      • 2023-11-01 30521, 2023

      • BestSteve joined the channel
      • 2023-11-01 30500, 2023

      • trolley has quit
      • 2023-11-01 30547, 2023

      • trolley joined the channel
      • 2023-11-01 30556, 2023

      • ErBear joined the channel
      • 2023-11-01 30515, 2023

      • Erin has quit
      • 2023-11-01 30508, 2023

      • Rawr joined the channel
      • 2023-11-01 30507, 2023

      • ErBear has quit
      • 2023-11-01 30540, 2023

      • crism has quit
      • 2023-11-01 30559, 2023

      • crism joined the channel
      • 2023-11-01 30558, 2023

      • ErBear joined the channel
      • 2023-11-01 30515, 2023

      • Rawr has quit
      • 2023-11-01 30503, 2023

      • otisolsen70 joined the channel
      • 2023-11-01 30529, 2023

      • ErBear has quit
      • 2023-11-01 30552, 2023

      • ErBear joined the channel
      • 2023-11-01 30557, 2023

      • ErBear has quit
      • 2023-11-01 30505, 2023

      • ErBear joined the channel
      • 2023-11-01 30525, 2023

      • iconoclasthero has quit
      • 2023-11-01 30535, 2023

      • Maxr1998_ joined the channel
      • 2023-11-01 30539, 2023

      • Maxr1998 has quit
      • 2023-11-01 30522, 2023

      • antlarr joined the channel
      • 2023-11-01 30535, 2023

      • otisolsen70 has quit
      • 2023-11-01 30544, 2023

      • petitminion joined the channel
      • 2023-11-01 30529, 2023

      • petitminion has quit
      • 2023-11-01 30513, 2023

      • SothoTalKer has quit
      • 2023-11-01 30551, 2023

      • theraspberry has quit
      • 2023-11-01 30553, 2023

      • theraspberry joined the channel
      • 2023-11-01 30556, 2023

      • SothoTalKer joined the channel
      • 2023-11-01 30556, 2023

      • iconoclasthero joined the channel
      • 2023-11-01 30508, 2023

      • theraspberry has quit
      • 2023-11-01 30554, 2023

      • theraspberry joined the channel
      • 2023-11-01 30532, 2023

      • MeatPupp3t has quit
      • 2023-11-01 30544, 2023

      • MeatPupp3t joined the channel
      • 2023-11-01 30511, 2023

      • jazzi joined the channel
      • 2023-11-01 30543, 2023

      • jazzi
        hey guys, this is my sql query to get the tracks and their associated releases/release groups for all iranian singers
      • 2023-11-01 30544, 2023

      • jazzi
        SELECT a.name, a.comment, t.name, rg.id
      • 2023-11-01 30544, 2023

      • jazzi
        FROM artist a join artist_credit_name acn on a.id=acn.artist
      • 2023-11-01 30545, 2023

      • jazzi
        join track t on t.artist_credit=acn.artist_credit
      • 2023-11-01 30545, 2023

      • jazzi
        join medium m on t.medium=m.id
      • 2023-11-01 30546, 2023

      • jazzi
        join release r on m.release=r.id
      • 2023-11-01 30546, 2023

      • jazzi
        join release_group rg on r.release_group=rg.id
      • 2023-11-01 30547, 2023

      • jazzi
        WHERE UPPER(a.comment) LIKE '%IRAN%' OR area=101 or Upper(a.comment) LIKE '%PERSIA%' ;
      • 2023-11-01 30547, 2023

      • jazzi
        how could i extend this to get the composer/lyricist information?
      • 2023-11-01 30532, 2023

      • bitmap
        that will require a lot of joins, so not sure it makes sense to do all in one query :)
      • 2023-11-01 30542, 2023

      • bitmap
        they are stored in l_artist_work but to get the work you have to go through l_recording_work
      • 2023-11-01 30545, 2023

      • jazzi
        bitmap ahh ok so like this? SELECT DIstinct a.name, a.comment, t.name,rec.id, artwork.*
      • 2023-11-01 30546, 2023

      • jazzi
        FROM artist a join artist_credit_name acn on a.id=acn.artist
      • 2023-11-01 30546, 2023

      • jazzi
        join track t on t.artist_credit=acn.artist_credit
      • 2023-11-01 30547, 2023

      • jazzi
        join recording rec on t.recording=rec.id
      • 2023-11-01 30547, 2023

      • jazzi
        join l_recording_work recwork on rec.id=recwork.entity0
      • 2023-11-01 30548, 2023

      • jazzi
        join l_artist_work artwork on recwork.entity1=artwork.entity1
      • 2023-11-01 30548, 2023

      • jazzi
        WHERE UPPER(a.comment) LIKE '%IRAN%' OR area=101 or Upper(a.comment) LIKE '%PERSIA%' ;
      • 2023-11-01 30528, 2023

      • ttpcodes has quit
      • 2023-11-01 30538, 2023

      • ttpcodes_ joined the channel
      • 2023-11-01 30526, 2023

      • bitmap
        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).
      • 2023-11-01 30546, 2023

      • bitmap likes the alias "artwork" for l_artist_work
      • 2023-11-01 30531, 2023

      • bitmap
        if you want the actual artist names / mbids, join the artist table a second time to l_artist_work.entity0
      • 2023-11-01 30525, 2023

      • bitmap
        the link type IDs are listed in our documentation, e.g., https://musicbrainz.org/relationship/a255bca1-b15…
      • 2023-11-01 30547, 2023

      • bitmap
        165 = lyricist, 168 = composer
      • 2023-11-01 30558, 2023

      • jazzi
        bitmap ahh ok that makes more sense, but im still confused where i can pull the link type from each of these artists.
      • 2023-11-01 30559, 2023

      • jazzi
        SELECT DIstinct a.name, a.comment, a.area, t.name,rec.id, b.*
      • 2023-11-01 30559, 2023

      • jazzi
        FROM artist a join artist_credit_name acn on a.id=acn.artist
      • 2023-11-01 30500, 2023

      • jazzi
        join track t on t.artist_credit=acn.artist_credit
      • 2023-11-01 30500, 2023

      • jazzi
        join recording rec on t.recording=rec.id
      • 2023-11-01 30501, 2023

      • jazzi
        join l_recording_work recwork on rec.id=recwork.entity0
      • 2023-11-01 30501, 2023

      • jazzi
        join l_artist_work artwork on recwork.entity1=artwork.entity1
      • 2023-11-01 30502, 2023

      • jazzi
        join artist b on b.id=artwork.entity0
      • 2023-11-01 30502, 2023

      • jazzi
        WHERE
      • 2023-11-01 30503, 2023

      • jazzi
        a.comment ~* '\y(IRAN|IRANIAN|PERSIA|PERSIAN)\y'
      • 2023-11-01 30503, 2023

      • jazzi
        OR a.area=101
      • 2023-11-01 30504, 2023

      • jazzi
        i tried doing artist (b) .* but i dont see how to get the link type ID's
      • 2023-11-01 30543, 2023

      • bitmap
        join link l on (l.id=l_artist_work.link AND l.link_type IN (165, 167, 168))
      • 2023-11-01 30545, 2023

      • anonn joined the channel
      • 2023-11-01 30534, 2023

      • bitmap
        btw, for pasting long sql queries in irc, use pastebin.com to avoid spamming the channel :)
      • 2023-11-01 30545, 2023

      • jazzi
        bitmap thank you so much it works now i think
      • 2023-11-01 30554, 2023

      • jazzi
        oh im sorry ill do that next time
      • 2023-11-01 30513, 2023

      • bitmap
        np
      • 2023-11-01 30530, 2023

      • jazzi
        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?
      • 2023-11-01 30547, 2023

      • jazzi
        and also including the area code clause of iran
      • 2023-11-01 30555, 2023

      • bitmap
        one improvement you could do is to use area_containment to check if a.area is a sub-area of iran
      • 2023-11-01 30551, 2023

      • bitmap
        but if you are using a replicated mirror server, you may have to build this table...
      • 2023-11-01 30548, 2023

      • bitmap
        join area_containment ac on ac.descendant = a.area, and then WHERE a.area = 101 OR ac.parent = 101 (I think)
      • 2023-11-01 30534, 2023

      • bitmap
        are you using musicbrainz-docker?
      • 2023-11-01 30535, 2023

      • jazzi
        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?
      • 2023-11-01 30543, 2023

      • jazzi
        ill try that now ty
      • 2023-11-01 30543, 2023

      • bitmap
        yup
      • 2023-11-01 30549, 2023

      • bitmap
        you can replace 'all' with 'area_containment'
      • 2023-11-01 30558, 2023

      • bitmap
        since you only need that table
      • 2023-11-01 30522, 2023

      • jazzi
        right perfect
      • 2023-11-01 30521, 2023

      • jazzi
      • 2023-11-01 30522, 2023

      • jazzi
        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
      • 2023-11-01 30518, 2023

      • bitmap
        looks reasonable
      • 2023-11-01 30552, 2023

      • bitmap
        but instead of DISTINCT it might make the results easier to work with if you use GROUP BY and then array_agg(genre.name) ?
      • 2023-11-01 30508, 2023

      • bitmap
        so there is not a duplicate row for each genre
      • 2023-11-01 30539, 2023

      • jazzi
        oh i didnt know u could do that lol yeah thatd be better ty
      • 2023-11-01 30549, 2023

      • jazzi
        bitmap hmm so more like this? https://pastebin.com/XLwvQb8t
      • 2023-11-01 30501, 2023

      • jazzi
        sql is so confusing haha
      • 2023-11-01 30530, 2023

      • jazzi
        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
      • 2023-11-01 30540, 2023

      • bitmap
        yeah that looks better
      • 2023-11-01 30528, 2023

      • bitmap
        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
      • 2023-11-01 30525, 2023

      • bitmap
        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
      • 2023-11-01 30556, 2023

      • bitmap
        11 appears to be "Other"
      • 2023-11-01 30522, 2023

      • jazzi
        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
      • 2023-11-01 30510, 2023

      • bitmap
        those should be included in the track artist credit, if they were entered correctly
      • 2023-11-01 30554, 2023

      • jazzi
        ah so track.artist credit will give me the artist ids who featured
      • 2023-11-01 30504, 2023

      • jazzi
        then i just need to join that back to the artist to get there names
      • 2023-11-01 30513, 2023

      • jazzi
        i might do this in a seperate query lol
      • 2023-11-01 30539, 2023

      • iconoclasthero has quit
      • 2023-11-01 30553, 2023

      • relaxoMob has quit
      • 2023-11-01 30506, 2023

      • relaxoMob joined the channel
      • 2023-11-01 30510, 2023

      • jazzi
        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'
      • 2023-11-01 30538, 2023

      • jazzi
        im just using the local postgres version of the database not the full server btw
      • 2023-11-01 30505, 2023

      • bitmap
        did it log any output? if there is anything in the table in will be skipped, so maybe it was built already
      • 2023-11-01 30549, 2023

      • bitmap
        the name passed to --database corresponds to an entry in DBDefs.pm (in the register_databases call)
      • 2023-11-01 30500, 2023

      • bitmap
        so either MAINTENANCE or READWRITE should work
      • 2023-11-01 30520, 2023

      • jazzi
        it didnt log any output no, but i also tried select * from area_containment and it returns empty
      • 2023-11-01 30556, 2023

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

      • jazzi
        hmm ive tried both those changes still didn't work
      • 2023-11-01 30537, 2023

      • jazzi
        i assume its not a major problem cause most iranian artists have the word iran or iranian in their comments?
      • 2023-11-01 30546, 2023

      • jazzi
        atleast i hope haha
      • 2023-11-01 30557, 2023

      • jazzi
        or are just area 101
      • 2023-11-01 30530, 2023

      • jazzi
        oh maybe theres a way to get all the subareas and i could just add to the sql query manually one by one?
      • 2023-11-01 30551, 2023

      • bitmap
        hmm, the command should log something though, are you running it from the musicbrainz-docker dir?
      • 2023-11-01 30506, 2023

      • jazzi
        yeah i cd into cd musicbrainz-docker
      • 2023-11-01 30517, 2023

      • jazzi
        and i do sudo docker ps by container shows up correctly
      • 2023-11-01 30544, 2023

      • jazzi
        and i ran from there and it just outputs nothing goes back to my username
      • 2023-11-01 30556, 2023

      • bitmap
      • 2023-11-01 30528, 2023

      • jazzi
        oh do i need to enter the container first?
      • 2023-11-01 30522, 2023

      • bitmap
        you shouldn't, but you can try (I'm running musicbrainz-server outside of docker, hence the different command)
      • 2023-11-01 30551, 2023

      • bitmap
        maybe it will log something in an interactive terminal
      • 2023-11-01 30518, 2023

      • jazzi
        ~/musicbrainz-docker$ sudo docker exec -it 0c52e35228bc bash
      • 2023-11-01 30528, 2023

      • jazzi
        root@0c52e35228bc:/# bash -c './admin/BuildMaterializedTables --database=MAINTENANCE area_containment'
      • 2023-11-01 30528, 2023

      • jazzi
        bash: line 1: ./admin/BuildMaterializedTables: No such file or directory
      • 2023-11-01 30538, 2023

      • jazzi
        srry for the spam... im making food at same time as this haha
      • 2023-11-01 30513, 2023

      • bitmap
        huh, well maybe the command in the README is wrong
      • 2023-11-01 30526, 2023

      • bitmap
        yvanzo ^?
      • 2023-11-01 30505, 2023

      • bitmap
        perhaps try docker-compose exec --user musicbrainz musicbrainz bash -c '...'
      • 2023-11-01 30519, 2023

      • ttpcodes_ has quit
      • 2023-11-01 30547, 2023

      • ttpcodes joined the channel
      • 2023-11-01 30522, 2023

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