Import ratings from Clementine to Strawberry Music Player

clementine2strawberry.sh

#!/bin/bash
# From https://forum.strawberrymusicplayer.org/post/335

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