Best posts made by 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