• Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Register
    • Login

    Clementine-->Strawberry migration, fixes to SQL

    Scheduled Pinned Locked Moved
    Wiki
    4
    4
    2.0k
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • C
      cookie
      last edited by

      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;
      V 1 Reply Last reply Reply Quote 2
      • D
        dark.angel.28179
        last edited by

        Nice work - just saved a few hours on import it seems.
        thanks!

        1 Reply Last reply Reply Quote 0
        • V
          VileLasagna @cookie
          last edited by

          @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 is NULL 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

          C 1 Reply Last reply Reply Quote 0
          • C
            chank0 @VileLasagna
            last edited by

            @cookie Thanks for sharing the script.

            I used it today, and I had to make some changes. In the latest version of strawberry, apparently there is no longer a songs_fts table.

            Additionally, I had an issue. After importing my collection from clementine, strawberry would rescan and generate duplicate entries for all my songs. Thus, for each song, I was seeing two entries in strawberry.

            I dug a bit and found the the filetype numbers in strawberry and clementine are different. Additionally, all entries in the songs table in my clementine database had directory_id set to 2 (where it should be 1).

            I modified the script you provided to fix both issues.

            • The different filetypes. I am not sure if strawberry would eventually correct these values.
            • The directory_id (Notice that this was a problem in my database in particular, others may not need my fix

            I include the modified script here in case it is useful for anyone

            -- "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 = "";

-- @chank0 Convert all filetypes to auxiliary values
UPDATE strawberry.songs SET filetype=1000 WHERE filetype=0;
UPDATE strawberry.songs SET filetype=1001 WHERE filetype=1;
UPDATE strawberry.songs SET filetype=1002 WHERE filetype=2;
UPDATE strawberry.songs SET filetype=1003 WHERE filetype=3;
UPDATE strawberry.songs SET filetype=1004 WHERE filetype=4;
UPDATE strawberry.songs SET filetype=1005 WHERE filetype=5;
UPDATE strawberry.songs SET filetype=1006 WHERE filetype=6;
UPDATE strawberry.songs SET filetype=1007 WHERE filetype=7;
UPDATE strawberry.songs SET filetype=1008 WHERE filetype=8;
UPDATE strawberry.songs SET filetype=1009 WHERE filetype=9;
UPDATE strawberry.songs SET filetype=1010 WHERE filetype=10;
UPDATE strawberry.songs SET filetype=1011 WHERE filetype=11;
UPDATE strawberry.songs SET filetype=1012 WHERE filetype=12;
UPDATE strawberry.songs SET filetype=1013 WHERE filetype=13;
UPDATE strawberry.songs SET filetype=1014 WHERE filetype=14;
UPDATE strawberry.songs SET filetype=1015 WHERE filetype=15;
UPDATE strawberry.songs SET filetype=1016 WHERE filetype=16;
UPDATE strawberry.songs SET filetype=1017 WHERE filetype=17;
UPDATE strawberry.songs SET filetype=1099 WHERE filetype=99;

-- @chank0 And now set the types understood by strawberry
UPDATE strawberry.songs SET filetype=0 WHERE filetype=1000;
UPDATE strawberry.songs SET filetype=10 WHERE filetype=1001; -- ASF was 1 in clementine, 10 in strawberry
UPDATE strawberry.songs SET filetype=2 WHERE filetype=1002; -- FLAC was 2 in clementine, 2 in strawberry
UPDATE strawberry.songs SET filetype=9 WHERE filetype=1003; -- MP4 was 3 in clementine, 9 in strawberry
UPDATE strawberry.songs SET filetype=12 WHERE filetype=1004; -- MPC was 4 in clementine, 12 in strawberry
UPDATE strawberry.songs SET filetype=8 WHERE filetype=1005; -- MPEG was 5 in clementine, 8 in strawberry
UPDATE strawberry.songs SET filetype=4 WHERE filetype=1006; -- OGGflac was 6 in clementine, 4 in strawberry
UPDATE strawberry.songs SET filetype=7 WHERE filetype=1007; -- OggSpeex was 7 in clementine, 7 in strawberry
UPDATE strawberry.songs SET filetype=5 WHERE filetype=1008; -- OggVorbis was 8 in clementine, 5 in strawberry
UPDATE strawberry.songs SET filetype=11 WHERE filetype=1009; -- AIFF was 9 in clementine, 11 in strawberry
UPDATE strawberry.songs SET filetype=1 WHERE filetype=1010; -- WAV was 10 in clementine, 1 in strawberry
UPDATE strawberry.songs SET filetype=13 WHERE filetype=1011; -- Trueaudio was 11 in clementine, 13 in strawberry
UPDATE strawberry.songs SET filetype=90 WHERE filetype=1012; -- cdda was 12 in clementine, 90 in strawberry
UPDATE strawberry.songs SET filetype=6 WHERE filetype=1013; -- OGGopus was 13 in clementine, 6 in strawberry
UPDATE strawberry.songs SET filetype=3 WHERE filetype=1014; -- Wavpack was 14 in clementine, 3 in strawberry
UPDATE strawberry.songs SET filetype=22 WHERE filetype=1015; -- Spc was 15 in clementine, 22 in strawberry
UPDATE strawberry.songs SET filetype=23 WHERE filetype=1016; -- VGM was 16 in clementine, 23 in strawberry
UPDATE strawberry.songs SET filetype=17 WHERE filetype=1017; -- APE was 17 in clementine, 17 in strawberry
UPDATE strawberry.songs SET filetype=91 WHERE filetype=1099; -- Stream was 99 in clementine, 91 in strawberry

-- @chank0: My clementine DB has directory_id=2 in all songs, but it should be directory=1
-- No one else should need this line. Uncomment only if you understand what you are doing
-- UPDATE strawberry.songs SET directory_id=1;

DETACH clementine;
DETACH strawberry;
            1 Reply Last reply Reply Quote 0
            • First post
              Last post
            Powered by NodeBB | Contributors