Import ratings from other players esp Clementine



  • 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.



  • 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
    


  • 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.


Log in to reply