• 0 Posts
  • 3 Comments
Joined 2 years ago
cake
Cake day: August 2nd, 2023

help-circle

  • Hey just following up on this, I took your advice and got it working! For anyone seeing this in the future I took the old navidrome.db and called it navidrome_fav_artist_backup.db and then I loaded into the new database (after making a backup of it) with sqlite3 navidrome.db and then I ran this

    -- Attach the backup database as "backup"
    ATTACH 'navidrome_fav_artist_backup.db' AS backup;
    
    -- Update annotations for favorited artists by matching on artist name
    UPDATE annotation
    SET starred = 1,
        starred_at = CURRENT_TIMESTAMP
    WHERE item_type = 'artist'
      AND item_id IN (
        SELECT newArtist.id
        FROM artist AS newArtist
        JOIN backup.artist AS oldArtist ON newArtist.name = oldArtist.name
        JOIN backup.annotation AS oldAnnot ON oldArtist.id = oldAnnot.item_id
        WHERE oldAnnot.item_type = 'artist'
          AND oldAnnot.starred = 1
      );
    
    -- Detach the backup database
    DETACH backup;
    .quit