Clementine-->Strawberry migration, fixes to SQL
-
First - Thank You for bringing us the sweet taste of Strawberry!
Second - Sorry my first post is so long!I followed the guide but, for me, it failed. Because of:
- A "Not Null" constraint violation on the insert to strawberry.playlist_items for the url column (which is sourced from the clementine filename column). Fixed by adding a case expression to convert the null from clementine to an empty string.
- The cover art path "art_manual" points to the old clementine directory. Fixed by adding a REPLACE expression to modify the offending part of the path to match the new location for album covers. (in the playlist_items and songs tables).
As I had to re-run it a few times I pulled the listed statements from the wiki page into a script which can be run in one shot with:
sqlite3 < migrate_clementine_to_strawberry.sql
The script is (See -- SQLFIX comments for my changes):
-- "full/path/to" needs to be replaced with the absolute path to your user home directory ATTACH '/full/path/to/.config/Clementine/clementine.db' AS clementine; ATTACH '/full/path/to/.local/share/strawberry/strawberry/strawberry.db' AS strawberry; DELETE FROM strawberry.directories; DELETE FROM strawberry.subdirectories; DELETE FROM strawberry.songs; DELETE FROM strawberry.playlists; DELETE FROM strawberry.playlist_items; INSERT INTO strawberry.directories (path, subdirs) SELECT path, subdirs FROM clementine.directories; INSERT INTO strawberry.subdirectories (directory_id, path, mtime) SELECT directory, path, mtime FROM clementine.subdirectories; INSERT INTO strawberry.songs (ROWID, title, album, artist, albumartist, track, disc, year, originalyear, genre, compilation, composer, performer, grouping, comment, lyrics, beginning, length, bitrate, samplerate, directory_id, url, filetype, filesize, mtime, ctime, unavailable, playcount, skipcount, lastplayed, compilation_detected, compilation_on, compilation_off, compilation_effective, art_automatic, art_manual, effective_albumartist, effective_originalyear, cue_path, rating) SELECT ROWID, title, album, artist, albumartist, track, disc, year, originalyear, genre, compilation, composer, performer, grouping, comment, lyrics, beginning, length, bitrate, samplerate, directory, filename, filetype, filesize, mtime, ctime, unavailable, playcount, skipcount, lastplayed, sampler, forced_compilation_on, forced_compilation_off, effective_compilation, art_automatic, -- SQLFIX: Not sure if an equivalent expression is needed for the 'art_automatic' column, above, too? REPLACE(art_manual, '.config/Clementine/albumcovers','.local/share/strawberry/strawberry/collectionalbumcovers'), effective_albumartist, effective_originalyear, cue_path, rating FROM clementine.songs WHERE unavailable = 0; UPDATE strawberry.songs SET source = 2; UPDATE strawberry.songs SET artist_id = ""; UPDATE strawberry.songs SET album_id = ""; UPDATE strawberry.songs SET song_id = ""; INSERT INTO strawberry.playlists (ROWID, name, last_played, special_type, ui_path, is_favorite, dynamic_playlist_type, dynamic_playlist_data, dynamic_playlist_backend) SELECT ROWID, name, last_played, special_type, ui_path, is_favorite, dynamic_playlist_type, dynamic_playlist_data, dynamic_playlist_backend FROM clementine.playlists WHERE dynamic_playlist_type ISNULL; INSERT INTO strawberry.playlist_items (ROWID, playlist, collection_id, title, album, artist, albumartist, track, disc, year, originalyear, genre, compilation, composer, performer, grouping, comment, lyrics, beginning, length, bitrate, samplerate, directory_id, url, filetype, filesize, mtime, ctime, unavailable, playcount, skipcount, lastplayed, compilation_detected, compilation_on, compilation_off, compilation_effective, art_automatic, art_manual, effective_albumartist, effective_originalyear, cue_path, rating ) SELECT ROWID, playlist, library_id, title, album, artist, albumartist, track, disc, year, originalyear, genre, compilation, composer, performer, grouping, comment, lyrics, beginning, length, bitrate, samplerate, directory, -- SQLFIX Constraint violation on the insert without this case as strawberry has "NOT NULL" constraint on url column, whereas clementine didn't case when filename IS NULL then "" else filename end, filetype, filesize, mtime, ctime, unavailable, playcount, skipcount, lastplayed, sampler, forced_compilation_on, forced_compilation_off, effective_compilation, art_automatic, -- SQLFIX: Not sure if an equivalent expression is needed for the 'art_automatic' column, above, too? REPLACE(art_manual, '.config/Clementine/albumcovers','.local/share/strawberry/strawberry/collectionalbumcovers'), effective_albumartist, effective_originalyear, cue_path, rating FROM clementine.playlist_items WHERE type = 'Library'; UPDATE strawberry.playlist_items SET source = 2; UPDATE strawberry.playlist_items SET type = 2; UPDATE strawberry.playlist_items SET artist_id = ""; UPDATE strawberry.playlist_items SET album_id = ""; UPDATE strawberry.playlist_items SET song_id = ""; DELETE FROM strawberry.songs_fts; INSERT INTO strawberry.songs_fts (ROWID, ftstitle, ftsalbum, ftsartist, ftsalbumartist, ftscomposer, ftsperformer, ftsgrouping, ftsgenre, ftscomment) SELECT ROWID, title, album, artist, albumartist, composer, performer, grouping, genre, comment FROM strawberry.songs; DETACH clementine; DETACH strawberry;
-
Nice work - just saved a few hours on import it seems.
thanks! -
@cookie A bit of a threadcromancy but I also ran into some issues there and made the account to suggest fixes
Double vs single quotes got me but also after importing, loads of my playlists were empty. This is because not all of my stuff is within the Library DB. So on the statement where you're adding to the
playlist_items
table, there are two considerations:As OP made a case, the
filename
column in Clementine isNULL
for Library type entries, and you need to insert''
instead.ADDITIONALLY, you DO want to insert also where
type = 'File'
.I'm bad with SQL so i just ran the thing twice instead of looking up how to inline OR, but yeah, this needs to get fixed in the wiki too