Merging two different Amarok 2 collections

Submitted by Falken on

I don't think it's that uncommon to have two different installs of the excellent media player Amarok (for instance one at work and one at home) which share a media collection (over Dropbox or manually with rsync, for instance).
The problem is there is no way to synchronise the scores and ratings between these two computers, so I've had to build one.
I figured it might be useful, so here are the details of the steps to take, and a small Perl utility need to help out.

1) On the source machine, archive the collection database and send it to somewhere (like /tmp/) on the destination machine.

#cd ~/.kde/share/apps
#tar cjvf amarok.bz2 --exclude amarok/albumcovers amarok
#now copy it to the destination

2) Unpack the source collection database and start it up as a standalone MySQL (yeah, this is the cool bit !)

#cd /tmp
#tar xjvf amarok.bz2
#cd amarok
#/usr/sbin/mysqld --defaults-file=`pwd`/my.cnf \
--default-storage-engine=MyISAM --datadir=`pwd`/mysqle \
--socket=`pwd`/sock --skip-grant-tables

You should see something like "13:18:38 [Note] /usr/sbin/mysqld: ready for connections".

3) Export the data we will need later. Note the path translation ('perl -pe ...'). This is because the same files are in different locations at each end, so you must replace the path from the source with that of the destination. Note the leading dot.

#mysqldump --host= --skip-opt --complete-insert amarok | \
egrep '(INSERT INTO `statistics`|INSERT INTO `urls)' | \
perl -pe 's!./home/sourceUsername/mp3!./home/localUsername/smb4k/mp3!' \
> /tmp/import.sql

4) Stop the standalone MySQL that we started earlier by finding the process and killing it. CTRL-C doesn't seem to work for some reason.

#ps -efww|grep mysq
local    2044  2042  0 12:07 ?        00:00:03 /usr/sbin/mysqld-akonadi ....
local    4917  2797  0 15:26 pts/2    00:00:00 /usr/sbin/mysqld --defaults-file=/tmp/amarok/my.cnf ...
#kill 4917

5) Make sure the local (aka destination) Amarok is stopped.

6) Make sure the local (aka destination) Amarok is stopped, and double check it's not in the system tray either. I wrote it twice because it's important :-)

7) Now start up your local Amarok's collection as a standalone MySQL. These steps are the same as what you did at the source end:

#cd ~/.kde/share/apps/amarok
#/usr/sbin/mysqld --defaults-file=`pwd`/my.cnf \
--default-storage-engine=MyISAM --datadir=`pwd`/mysqle \
--socket=`pwd`/sock --skip-grant-tables

8) Feed the import.sql file we created earlier into the attached Perl script

#./ /tmp/import.sql 

The script will attempt to locate files in the local collection with the same path and file name as in the source collection. If it finds a match, and that file was played in the source collection (i.e. has a score and rating) then those will be imported. It overwrites any local score/rating if present (this should probably be optional, or a weighted merge done), or creates a whole new record (with creation dates of 'now') if needed.
You should see something at the end of the output like:

Attempted to import 4698. Found 4472, updated 540 and inserted 3932. 226 failed.

9) Shut down the stand alone MySQL instance just like you did last time:

#ps -efww|grep mysq
local    2044  2042  0 12:07 ?        00:00:03 /usr/sbin/mysqld-akonadi ....
local    4917  2797  0 15:26 pts/2    00:00:00 /usr/sbin/mysqld --defaults-file=/home/local/.kde/...
#kill 4917
Attachment Size
merge_amarok_collections.pl_.txt2.79 KB 2.79 KB
Section (old Drupal 7 content)

Thanks so much!  Recently Amarok wiped out the metadata for a large number of my files but I didn't realize this until after I added several dozen ratings.  I used your script to merge in the lost ratings from a database backup I had made.

One thing I noticed when running your script was that I got hundreds of errors of the following form:

Use of uninitialized value $id in hash element at /home/psy/bin/ line 24.

Use of uninitialized value $2 in concatenation (.) or string at /home/psy/bin/ line 22.

Use of uninitialized value $3 in concatenation (.) or string at /home/psy/bin/ line 22.                                             

The script seemed to succeed in spite of these error messages.

The new Amarok 2.4 has a configuration option to write back the statistics to the audio file. Some people don't like this as it updates the playcount after every played song. However I copied my song collection a couple of times over the years and I always write ratings back to the file. I even had a script for mpd to do that. Your ratings will never get lost if you do this.