Save track position in medium to database

[hasezoey]

Describe what you want

Currently sorting in the database can only be done by created date and alphabetical order, but it would likely be more natural to sort a album by its tracks positions (1-01, 2-01).

See:

• Consider track and disc number when adding an album to queue #532
• [Feature Request] Adding filter database by album artist and sorting by track number #138

Do you have already an idea for the implementation?

--- Medium-track Positions

-- the table to store the mediums (cd 1, cd 2, etc)
CREATE TABLE IF NOT EXITS mediums(
    id INTEGER PRIMARY KEY,
    album INTEGER NOT NULL REFERENCES albums(id) ON DELETE CASCADE,
    position INTEGER NOT NULL
);

-- unique index on the mediums.{album, position} columns as those combine to be a unique medium
CREATE UNIQUE INDEX IF NOT EXISTS mediums_album_position ON mediums(album, position);

-- store the position within a given medium (track 1, track 2)
CREATE TABLE IF NOT EXISTS medium_positions(
    id INTEGER PRIMARY KEY,
    medium INTEGER NOT NULL REFERENCES mediums(id) ON DELETE CASCADE,
    track INTEGER NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,
    position INTEGER NOT NULL
);

-- unique index on the medium_positions.{medium, track} columns as a given track should only be associated with one medium
CREATE UNIQUE INDEX IF NOT EXISTS medium_positions_track ON medium_positions(medium, track);

This takes inspiration from musicbrainz's handling of medium-track positions.

Note that i did not make position be unique in the medium_positions case, as there could be duplicate tracks that have the same position (bad metadata, or simply duplicated tracks, ex testing in different codecs)

Thankfully, lofty already parses the tag correctly into the following available tag variants (which should be self-explanatory):

• TrackNumber
• TrackTotal
• DiscNumber
• DiscTotal

Though we dont really need the *Total values.

If the tags are correctly used, they should be plain parse-able numbers like 1, 10 maybe even 01.
Also note that lofty currently does not parse them as numbers, but provides them as strings.

It is still open how we should treat files that dont have the necessary metadata. I would treat them as 1-1, but should that also be added to the database in such a way?