Support storing multivalued composer/lyricist/arranger MBIDs in the database

[Maxr1998]

Motivation

Currently, beets stores the names of writers and arrangers in the database and the audio file tags. I'm currently using these to build automated playlists (with the smartplaylist plugin) based on these writers. However, this has several issues:

• Names of these writers are not necessarily unique, creating collisions and thus including unrelated tracks in my playlists
• Matching these single-valued tags makes it even more complex, since some names might have substring overlaps, but you can't do exact matches (because there can be multiple writers in a single field), and handling the separator char (comma) with RegEx is unnecessarily complex.

Proposal

Thus, I suggest adding new fields to the database and possibly to files:

• mb_composerids
• mb_lyricistids
• mb_arrangerids

All of these should be multivalued fields for future proofing. Adding legacy fields that simply join the values with a separator would be possible, but isn't strictly necessary in my opinion.

Implementation concepts

As a plugin

This feature could be implemented as a plugin, in theory:

Plugin implementation

from beets.plugins import BeetsPlugin
from beets.dbcore import types

MB_COMPOSERIDS = "mb_composerids"
MB_LYRICISTIDS = "mb_lyricistids"
MB_ARRANGERIDS = "mb_arrangerids"


class WriterMbidsPlugin(BeetsPlugin):
    item_types = {
        MB_COMPOSERIDS: types.MULTI_VALUE_DSV,
        MB_LYRICISTIDS: types.MULTI_VALUE_DSV,
        MB_ARRANGERIDS: types.MULTI_VALUE_DSV,
    }

    def __init__(self):
        super().__init__()
        self.register_listener("mb_track_extract", self.track_extract)

    @staticmethod
    def track_extract(data):
        recording = data
        composer_mbids = []
        lyricist_mbids = []
        arranger_mbids = []

        for work_relation in recording.get("work-relation-list", ()):
            if work_relation["type"] != "performance":
                continue

            work = work_relation["work"]
            for artist_relation in work.get("artist-relation-list", ()):
                if "type" in artist_relation:
                    artist_type = artist_relation["type"]
                    if artist_type == "composer":
                        composer_mbids.append(artist_relation["artist"]["id"])
                    elif artist_type == "lyricist":
                        lyricist_mbids.append(artist_relation["artist"]["id"])

        for artist_relation in recording.get("artist-relation-list", ()):
            if "type" in artist_relation:
                artist_type = artist_relation["type"]
                if artist_type == "arranger":
                    arranger_mbids.append(artist_relation["artist"]["id"])

        info = {}
        if composer_mbids:
            info[MB_COMPOSERIDS] = composer_mbids
        if lyricist_mbids:
            info[MB_LYRICISTIDS] = lyricist_mbids
        if arranger_mbids:
            info[MB_ARRANGERIDS] = arranger_mbids
        return info

However, there's one issue currently preventing this: plugins cannot use multivalued fields because beets doesn't handle those for flexible fields:

Stacktrace

…
  File "/usr/lib/python3.13/site-packages/beets/dbcore/db.py", line 679, in add
    self.store()
    ~~~~~~~~~~^^
  File "/usr/lib/python3.13/site-packages/beets/library.py", line 396, in store
    super().store(fields)
    ~~~~~~~~~~~~~^^^^^^^^
  File "/usr/lib/python3.13/site-packages/beets/dbcore/db.py", line 616, in store
    tx.mutate(
    ~~~~~~~~~^
        "INSERT INTO {} "
        ^^^^^^^^^^^^^^^^^
    ...<2 lines>...
        (self.id, key, value),
        ^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/usr/lib/python3.13/site-packages/beets/dbcore/db.py", line 987, in mutate
    cursor = self.db._connection().execute(statement, subvals)
sqlite3.ProgrammingError: Error binding parameter 3: type 'list' is not supported

From what I understand, this is because beets doesn't use the sql type conversions for flexible fields, only for built-in fields:

Current code

# dbcore.py
…

   def store(self, fields: Optional[Iterable[str]] = None):
        …
        for key in fields:
            if key != "id" and key in self._dirty:
                self._dirty.remove(key)
                assignments.append(key + "=?")
                value = self._type(key).to_sql(self[key]) # !!! TYPE IS CONVERTED !!!
                subvars.append(value)
        with db.transaction() as tx:
            # Main table update.
            if assignments:
                query = "UPDATE {} SET {} WHERE id=?".format(
                    self._table, ",".join(assignments)
                )
                subvars.append(self.id)
                tx.mutate(query, subvars)

            # Modified/added flexible attributes.
            for key, value in self._values_flex.items():
                if key in self._dirty:
                    self._dirty.remove(key)
                    tx.mutate(
                        "INSERT INTO {} "
                        "(entity_id, key, value) " # !!! NO TYPE CONVERSION !!!
                        "VALUES (?, ?, ?);".format(self._flex_table),
                        (self.id, key, value),
                    )
        …

To fix this, the value could be converted to SQL for flexible fields as well:

diff --git a/beets/dbcore/db.py b/beets/dbcore/db.py
old mode 100644
new mode 100644
index 4645d4b..53c462d
--- a/beets/dbcore/db.py
+++ b/beets/dbcore/db.py
@@ -611,6 +611,7 @@ class Model(ABC):
             for key, value in self._values_flex.items():
                 if key in self._dirty:
                     self._dirty.remove(key)
+                    value = self._type(key).to_sql(value)
                     tx.mutate(
                         "INSERT INTO {} "
                         "(entity_id, key, value) "

As a core feature

Alternatively, implementation as a core feature could also work. This would require a few changes to the Item model, but could otherwise be handled mostly in the mbsync plugin. The "bug" described above wouldn't impact this implementation, but should probably still be addressed.

Implementation

I'm interested in implementing this myself, please tell me which option you prefer, and I will open a pull request.

[Maxr1998]

@snejus what do you think about this? I'd love to get this supported, either in beets itself or as a (third-party) plugin, but that requires the underlying issue to be addressed first.

I already have a branch lying around that applies my suggested fix and implements a plugin for the writer mbids.

[snejus]

Regarding the casting issue, we've got a plugin types which is supposed to handle types for flexible attributes. I do not think that it supports multi value/list field type yet, so maybe this could be a possible solution to the problem?

Regarding the additional fields, I think it's fine to include the logic into the existing musicbrainz plugin.

[Maxr1998]

@snejus thank you! I don't know whether the types plugin can resolve the underlying issue, but generally, having support for multi-value fields there would be quite nice. That would likely require some CLI changes too, however.

Should I open a PR with the one-liner I suggested above for now? Did that now.

I can also take care of updated the musicbrainz plugin afterwards.

[amogus07]

Flexible fields, which can be used by plugins to store additional metadata, now also support list values. Previously, beets would throw an error while storing the data in the SQL database due to missing type conversion. #5698

This is what it says in the latest changelog… but it doesn't actually seem to work (yet)?

[Maxr1998]

@amogus07 which plugin are you testing this with? Note that the composer/lyricist/arranger MBIDs are not imported yet and are still waiting for #5847.

[amogus07]

@Maxr1998 just tried again, it works for item attributes, but not for album attributes. But Maybe I'm doing something wrong. I'm testing it with this plugin (haven't pushed the relevant changes yet): https://github.com/prTopi/beets-vocadb/tree/random-improvements

[Maxr1998]

@Maxr1998 just tried again, it works for item attributes, but not for album attributes.

Hm, I guess I have to take a closer look at the code again. But I also didn't see anything obvious on a quick glance for why this would require different behavior for album fields.