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;