DB Tables still use references to library_old(track_id) = Leftover from wrongly altering library in Schema update #3

[Eve00000]

Bug Description

in revision version="1" -> The base schema for the Mixxx SQLITE database.
the tables PlaylistTracks, crate_tracks, cues are created with REFERENCES to library(id)

CREATE TABLE PlaylistTracks (
        id INTEGER PRIMARY KEY,
        playlist_id INTEGER REFERENCES Playlists(id),
        track_id INTEGER REFERENCES library(id),
        position INTEGER);

CREATE TABLE IF NOT EXISTS crate_tracks (
        crate_id INTEGER NOT NULL REFERENCES crates(id),
        track_id INTEGER NOT NULL REFERENCES library(id),
        UNIQUE (crate_id, track_id));

CREATE TABLE IF NOT EXISTS cues (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        track_id INTEGER NOT NULL REFERENCES library(id),
        type INTEGER DEFAULT 0 NOT NULL,
        <!-- Positions and lengths are actually stored as SQLite REAL (double) frame values.
        This allows for bpm-accurate sub-sample accuracy positions and lengths. -->
        position INTEGER DEFAULT -1 NOT NULL,
        length INTEGER DEFAULT 0 NOT NULL,
        hotcue INTEGER DEFAULT -1 NOT NULL,
        label TEXT DEFAULT '' NOT NULL);

in revision version="3" : Change the location column to be a an integer. Change comment to be
varchar(256) and album/artist/title to be varchar(64).
the library table is altered to change the location field
from
location varchar(512) REFERENCES track_locations(location),
to
location INTEGER REFERENCES track_locations(location),

for this operation the library table is 1st renamed to library_old, then new library table is created and the contents of the old table is copied to the new one.

      ALTER TABLE library RENAME TO library_old;

      CREATE TABLE IF NOT EXISTS library (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        artist varchar(64),
        title varchar(64),
        album varchar(64),
        year varchar(16),
        genre varchar(64),
        tracknumber varchar(3),
        location INTEGER REFERENCES track_locations(location),
        comment varchar(256),
        url varchar(256),
        duration FLOAT,
        bitrate INTEGER,
        samplerate INTEGER,
        cuepoint INTEGER,
        bpm FLOAT,
        wavesummaryhex BLOB,
        channels INTEGER,
        datetime_added DEFAULT CURRENT_TIMESTAMP,
        mixxx_deleted INTEGER,
        played INTEGER,
        header_parsed INTEGER DEFAULT 0);

      INSERT INTO library (id, artist, title, album, year, genre, tracknumber, location, comment, url, duration, bitrate, samplerate, bpm, cuepoint, bpm, wavesummaryhex, channels, datetime_added, mixxx_deleted, played, header_parsed) SELECT id, artist, title, album, year, genre, tracknumber, location, comment, url, duration, bitrate, samplerate, bpm, cuepoint, bpm, wavesummaryhex, channels, datetime_added, mixxx_deleted, played, header_parsed from library_old;

after which the old table was dropped

      DROP TABLE library_old;

While experimenting with creating some virtual tracks for stems, I saw strange results when joining the PlaylistTracks with tracks_locations, some study brought me to these leftovers from this operation.
The track_id field in the tables PlaylistTracks, crate_tracks and cues REFERENCES to library_old(id).
This happened with 'ALTER TABLE library RENAME TO library_old;' but wasn't corrected. The PlaylistTracks, crate_tracks and cues tables needed to be altered (renamed) ->recreated -> filled with content to.

Attention: as the references are not correct, their could be orphans in the crate_tracks / PlaylistTracks / cues
I chose to delete the orphans.
SQL:

1. crate_tracks
ALTER TABLE crate_tracks RENAME TO crate_tracks_old;
-- this disables the references to the library_old, needed to delete the orphans
PRAGMA foreign_keys = OFF;
-- this deletes the orphans 
DELETE FROM crate_tracks_old WHERE track_id NOT IN (SELECT id FROM library);
DELETE FROM crate_tracks_old WHERE crate_id NOT IN (SELECT id FROM crates);

CREATE TABLE crate_tracks (
    crate_id INTEGER NOT NULL REFERENCES crates(id),
    track_id INTEGER NOT NULL REFERENCES library(id),
    UNIQUE (crate_id, track_id)
);
INSERT INTO crate_tracks (crate_id, track_id)
SELECT crate_id, track_id FROM crate_tracks_old;
DROP TABLE crate_tracks_old;

2. PlaylistTracks

ALTER TABLE PlaylistTracks RENAME TO PlaylistTracks_old;
-- this disables the references to the library_old, needed to delete the orphans
PRAGMA foreign_keys = OFF;
-- this deletes the orphans 
DELETE FROM PlaylistTracks_old WHERE track_id NOT IN (SELECT id FROM library);
DELETE FROM PlaylistTracks_old WHERE playlist_id NOT IN (SELECT id FROM Playlists);
CREATE TABLE PlaylistTracks (
    id INTEGER PRIMARY KEY,
    playlist_id INTEGER REFERENCES Playlists(id) ON DELETE CASCADE,
    track_id INTEGER REFERENCES library(id) ON DELETE CASCADE,
    position INTEGER,
    pl_datetime_added
);
INSERT INTO PlaylistTracks (id, playlist_id, track_id, position, pl_datetime_added)
SELECT id, playlist_id, track_id, position, pl_datetime_added 
FROM PlaylistTracks_old;
DROP TABLE PlaylistTracks_old;

3. cues

ALTER TABLE cues RENAME TO cues_old;
-- this disables the references to the library_old, needed to delete the orphans
PRAGMA foreign_keys = OFF;
-- this deletes the orphans 
DELETE FROM cues_old 
WHERE track_id NOT IN (SELECT id FROM library);
CREATE TABLE cues (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    track_id INTEGER NOT NULL REFERENCES library(id),
    type INTEGER DEFAULT 0 NOT NULL,
    position INTEGER DEFAULT -1 NOT NULL,
    length INTEGER DEFAULT 0 NOT NULL,
    hotcue INTEGER DEFAULT -1 NOT NULL,
    label TEXT DEFAULT '' NOT NULL,
    color INTEGER DEFAULT 4294901760 NOT NULL,
);
INSERT INTO cues (track_id, type, position, length, hotcue, label, color)
SELECT track_id, type, position, length, hotcue, label, color
FROM cues_old
WHERE track_id IN (SELECT id FROM library);
DROP TABLE cues_old;

I have no idea of the whole effect of this mislinking. But it could make some changes in speed + not creating orphans -> Data integrety improvement.

Version

No response

OS

No response