Import collection library and playlists from Clementine

From Strawberry Music Player Wiki
Jump to navigation Jump to search

Before importing data from Clementine, start Strawberry once so that the initial database schemas (tables) are created. Then exit both Strawberry and Clementine before you start.

The following guide is only briefly tested.

ALL COLLECTION AND PLAYLISTS DATA IN STRAWBERRY WILL BE OVERWRITTEN REPLACED WITH THE DATA FROM CLEMENTINE.

Start sqlite3

On a terminal and type:

   sqlite3

You will then get a command prompt where you can manually perform SQL queries.

Attach databases

Replace username with your own.

Type the following commands to attach both databases;

   ATTACH '/home/username/.local/share/strawberry/strawberry/strawberry.db' AS strawberry;
   ATTACH '/home/username/.config/Clementine/clementine.db' AS clementine;

Delete all existing songs, playlists and playlist items in the strawberry.db

This must be done when importing all data from Clementine because playlists are based on ROWIDs.

   DELETE FROM strawberry.directories;
   DELETE FROM strawberry.subdirectories;
   DELETE FROM strawberry.songs;
   DELETE FROM strawberry.playlists;
   DELETE FROM strawberry.playlist_items;

Import all data from the collection library songs

   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, art_manual, 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 = '';

Import playlists

   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;

Import playlist items

   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,
      filename,
      filetype,
      filesize,
      mtime,
      ctime,
      unavailable,
      playcount,
      skipcount,
      lastplayed,
      sampler,
      forced_compilation_on,
      forced_compilation_off,
      effective_compilation,
      art_automatic,
      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 = '';

Recreate the FTS tables

   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 the databases and quit

   DETACH clementine;
   DETACH strawberry;
   .quit

Restore all album covers

Use the following command to restore all album covers cached in Clementine:

   cp ~/.config/Clementine/albumcovers/* ~/.local/share/strawberry/strawberry/collectionalbumcovers/