Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Import ratings from Clementine to Strawberry Music Player
#!/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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment