Import ratings from other players esp Clementine

Oldbwl

Now we have smart playlists, this will be bound to attract more users from Clementine to swap, but if like me they have spent decades building up Play counts and ratings on which the Smart playlists are based, it would be a dreadful chore to try to recreate.

Therefore an imprt from the Clementine app or at least from the Id3 of the music during the initial full scan.

Rolf

For those running Linux, here's an experimental, quick-and-dirty bash script that updates a Strawberry database with rating, playcount, skipcount, lastplayed values taken from an existing Clementine database file. Save it e.g. as clementine2strawberry.sh and make it executable.

Never work on the original database files (~.config/Clementine/clementine.db and ~/.local/share/strawberry/strawberry/strawberry.db) - rather copy both to a working directory and store an additional backup in a safe place.

Usage of the script, in your working directory:

/path/to/clementine2strawberry.sh clementine.db strawberry.db

If the update is successful, you can try to replace ~/.local/share/strawberry/strawberry/strawberry.db with its updated version, fire up Strawberry, and see whether your Clementine data has been correctly imported.

A few technical notes:

The fields are simply overwritten with what is found in Clementine's DB - there is, e.g., no summing up of Clementine & Strawberry playcounts.
If you're reading this post in the "distant future", chances are that database schemata might have changed and you need to update the script to cope with that.
I had expected Clementine's songs.filename and Strawberry's songs.url to match, but oddly, without a TRIM(), they don't. Since a TRIM in the update statement makes that run for ages, I TRIM both columns upfront. That is, both .db files will be changed!
To cope with slightly different database schemes, it's necessary to use COALESCE() to supply suitable default values where clementine.db does not contain real values.

#!/bin/bash

CLEMENTINE_DB="$1"
STRAWBERRY_DB="$2"

UNIQUE_KEY_CLEMENTINE=filename
UNIQUE_KEY_STRAWBERRY=url

validate_db() {
    if [[ ! -f "${1}" ]]; then
        echo $2 database file $1 does not exist
        exit 1
    fi

    if sqlite3 "$1"  "pragma integrity_check;" > /dev/null
    then
        echo $2 database file $1 integrity check OK.
    else
        echo $2 database file $1 integrity check FAILED.
        exit 1
    fi
}

echo Validating database existence and integrity...
validate_db "$CLEMENTINE_DB" "Clementine"
validate_db "$STRAWBERRY_DB" "Strawberry"

# Columns to transfer, respective sequences must match:
# (COALESCE Clementine values to conform to Strawberry schema and default values.)
CLEMENTINE_COLUMNS="COALESCE(rating,-1),COALESCE(playcount,0),COALESCE(skipcount,0),COALESCE(lastplayed,-1)"
STRAWBERRY_COLUMNS="rating,playcount,skipcount,lastplayed"


# Trim unique key columns (rows won't match without):
sqlite3 "$1" "UPDATE songs SET $UNIQUE_KEY_CLEMENTINE = trim($UNIQUE_KEY_CLEMENTINE);"
sqlite3 "$2" "UPDATE songs SET $UNIQUE_KEY_STRAWBERRY = trim($UNIQUE_KEY_STRAWBERRY);"


echo
echo Updating database $STRAWBERRY_DB using this SQL statement:

if sqlite3 -echo "$2" "ATTACH '$1' AS clementine; UPDATE main.songs SET ($STRAWBERRY_COLUMNS)=(SELECT $CLEMENTINE_COLUMNS FROM clementine.songs WHERE main.songs.$UNIQUE_KEY_STRAWBERRY=clementine.songs.$UNIQUE_KEY_CLEMENTINE) WHERE EXISTS (SELECT * FROM clementine.songs WHERE main.songs.$UNIQUE_KEY_STRAWBERRY=clementine.songs.$UNIQUE_KEY_CLEMENTINE);"
then
    echo Database update successful. MAKE A BACKUP OF THE ORIGINAL, before you use the updated one.
else
    echo Database update FAILED.
fi

jonas

Nice job.
I made a guide now to import most data from Clementine:

https://github.com/strawberrymusicplayer/strawberry/wiki/Import-collection-library-and-playlists-data-from-Clementine

This is useful for users who want to import all their playlists from Clementine, since playlists are based on ROWID's from the collection library, it is necessary to import the collection too.

It would be great if you want to make a script.

PabloAB

@rolf great work! Moved to this Github gist. People could suggest changes there.
Now getting "Error: no such column: rating" (Clementine 1.4 rc1, Strawberry 0.9.3).

BTW, just to make it easier for others, default locations are

./clementine2strawberry.sh ~/.config/Clementine/clementine.db ~/.local/share/strawberry/strawberry/strawberry.db

Timpie

Hello,

I don't know if it's a related script branch but i imported my itunes metadata (playcount, lastplayed, .. first into clementine) and then used the https://musconv.com/itunes-to-clementine/ script which suited me fine!
I have ALL my data in strawberry ...