Database design to support multiple tracks per file

[Neurrone]

Split from #4269 (reply in thread)
@sampsyo any preferences on how the database migration should be performed? What I have in mind is the 2 new non-nullable columns for start and end duration.

• I noticed that length is a float in seconds, I was thinking of using an integer to store the start and end duration in ms instead. I usually default to integers when I can to avoid dealing with the complexity of floats.
• Would this be a good time to store file information in a separate files table? Or keep path as is for now.
• Would you want to keep the length column, or remove it because it can be deduced from end - start?

[wisp3rwind]

* I noticed that `length` is a float in seconds, I was thinking of using an integer to store the start and end duration in ms instead. I usually default to integers when I can to avoid dealing with the complexity of floats.

Might be worth considering what metadata sources typically provide.

* Would this be a good time to store file information in a separate files table? Or keep path as is for now.

Sounds like a good idea at a first glance (but probably requires some more thought): This would also help with commands that operate on a per-file (instead of per-item) basis, such as convert. I guess there's some overlap here with the "attachment proposal".

* Would you want to keep the `length` column, or remove it because it can be deduced from end - start?

Could be implemented as a computed field for backwards compatibility, so we could indeed avoid duplicating the information.

[pprkut]

There is some info in #1640, perhaps.

The idea back then was to add a file table to the database model, which would hold the information specific to that particular file. Format, samplerate, bitrate, etc. The track table that's currently already there would hold the track specific info that's independent of the actual file (artist, title, etc).
Using this you could then link one track to multiple files, and also the other way around, multiple tracks to a single file.

The initial step would be to just get the db split implemented. So still retain the 1:1 relationship in the beginning and just get the split structure right.

Edit: Some more background info, I wanted to keep the file information table to stay somewhat generic, so that we could also put attachment data in there, e.g. cover images. So perhaps a file_attributes table as well, that holds flex attrs.

[Neurrone]

Thanks, the linked issue is really helpful.

My plan now is to tackle setting up this files table first. I think it would be a good first contribution for me to get familiar with the codebase.

Having the file / track distinction can also allow us to start thinking about how to update plugins to be aware of this distinction to make it easier to add support for multiple tracks per file such as in #4269.

[Neurrone]

@sampsyo is there any existing code that deals with data migrations to accomodate schema evolution over time?

The only reference to migrations I see is in line 1010 of db.py in the _make_table function. It seems to only account for the case where new fields are added to the model definition, without also accounting for any updates to existing data that are needed.

I ask because adding the files table will require more than just the create table. So if there is already an existing way of handling this, I would like to reuse it if possible.

[sampsyo]

No, not really. We have some stuff in there for adding new fixed-attribute columns to tables, but that's all.