Apply missing data_source to albums when items have it / SQL exercise

[JOJ0]

I have a weird problem, most probably due to a bug in an older beets version I used back when I did my first experiments with beets on my second computer (the DJing computer).

I'll do my research now if I find that bug, I just can't remember that something like that was exisiting (It can't be that one: #4726 , because it seems to apply to each and every one of my albums with few exceptions. Can't imagine that I reimported all of them).

In the end it doesn't matter anymore though why I have this situation in this particular beets installation, I just want to somehow fix it.

Reimporting everything is not an option. I tried that with a distance of 0.04 for strong matches. That fixes around half of my collection (400).I still have about 600 albums to fix so my pragmatical approach would be to just fix it in the database. As far as my testing goes, this bug, or whatever it was, won't hit me again. Using the latest beets dev which runs find and fresh imports go very well.

Usually my approach to sql would be to find a select that shows me the issue and than move to an update statement that fixes it. I'm struggling with the select already:

I'd like to show all albums that have data_source missing but show what data_source actually should be from a track contained on the album (all my tracks have it set correctly!)

How to inner join/left join those 3 tables to at least show me where/what is missing. We have:

albums 1:n album_attributes
items 1:n items_attributes
albums 1:n items

Certainly hints on how regular beets commands could be used to fix it, are appreciated. Currently I'm not seeing how I could fix it that way though.

beet mod -a data_source=MusicBrainz some album can't really work because where would I put the "condition" to "use what the containing item has set" for data_source? Either we query albums (-a) or not. We can't query both at the same time, or can we?

[JOJ0]

Some insight since when data_source should be applied to album level on imports (merged mid-2019):

#1693

#3350

I might have used a version prior to this being merged but it would be very odd since my first beets experiments started around end of 2020...

[wisp3rwind]

I'd like to show all albums that have data_source missing but show what data_source actually should be from a track contained on the album (all my tracks have it set correctly!)

I only skimmed this, but couldn't you define an album-level inline field item_data_source that returns the first/most common data_source from the items within the album, and then do a query like beet ls -a data_source::^$ -f "$album - $item_data_source"?

[govynnus]

In the past I've resorted to shell scripts and long pipelines for this type of problem.
Would something like this work?

beet ls -f '$album_id $data_source' album_id+ | uniq | xargs -o -n 2 sh -c 'beet modify -a id:$0 data_source=$1'

(Edited: make sorting numeric make beets do sorting)

[arsaboo]

Oooh...I am thinking some version of this could be used to answer the query here - passing search_id from the database for reimporting

[JOJ0]

You all are awesome! That was exactly the creative ideas I was missing. In the end I used @wisp3rwind's idea #4784 (comment) but I'm sure a oneliner like @govynnus suggested #4784 (comment) would have worked as well.

This is what I did:

album level inline field:

    item_data_source: |
        for item in items:
            if item.get('data_source'):
                return f'{item.data_source}'

Fix:

$ beet mod -a data_source='$item_data_source' album::'.+' data_source::^$ item_data_source:MusicBrainz

$ beet mod -a data_source='$item_data_source' album::'.+' data_source::^$ item_data_source:Discogs

[JOJ0]

And great there was inspiration for another fix for something else @arsaboo #4784 (reply in thread) Very nice! 💯