For a very long time, I’ve been wanting to be able to use the same amarok database from all my computers in a relatively seamless fashion. I’ve hacked it before by moving the underlying single-file database (collection.db) to the network drive that also housed the music. That worked ok, except that for some reason, it updated far too slowly and I couldn’t get it to play music. The other thing is, I also eventually want to have a multi-user amarok, one where multiple users listen to the same music database. The options that opens up are endless. Let’s say several people are in the same room playing games; now you can have a dynamic playlist that only plays music everyone likes. Or you’re having a party. Now you can play music that you don’t usually like without it screwing up your personal scores, but still taking advantage of the power of the dynamic playlists. Maybe you’re having an impromptu eighties party, and you want to play lots of 80’s music without taking hours making playlists, but you don’t like 80’s music, so you don’t want it to effect your own score.
You could, with a multi-user amarok.
Anyway, that’s why I started down this path.
So, step 1 was to enable the external database and move my data over. Since I haven’t listened to music on amarok 2.2.2 (the version I’m going to be using) very much, that means all my statistics are still in my 1.4.x database. That actually makes things a little easier.
Following this guide, I got started. There’s a couple of things missing from the guide. First, you have to actually create a database. Then, you can create your user and grant them rights to it. However, when you grant them rights, don’t use localhost. Use %. So:
grant all on amarokdb.* to user 'amarokuser'@'%' rather than amarokuser@localhost.
Then, you go into amarok, set up your external db as stated. But don’t do anything right then. It won’t actually create the db tables. So you have to exit amarok, then start it again. That will cause the tables to be created assuming you’ve everything correctly set up. Which I did, so I can’t give you any infos about it not being set up.
Then, you go through the import process for your old collection. This will mostly go fine. BIG NOTE: there is info on the web about making sure that all your versions of amarok are the same, and that your music is mounted on the EXACT SAME PATH on all the computers that are going to connect. For the import, this is also true in that all the music has to be mounted on the same path for the mysql database doing the import that it was on the computer using the 1.4.x sqlite db. SAME PATH!
Anyway, the collection seems to import just fine, but in my case, it’s missing all the scores. I’ll get to that in a minute.
Great, now it’s working on one computer. So how do we get it working on another?
I’ve been dealing with this off and on for awhile, so my computers all share the same mount-point and are running the same version of ubuntu, so no worries there. I checked to make sure that all the versions are the same, just to be safe, and then set up the external db on the second box. Nothing. So, I started searching the web, and low and behold, you have to modify a couple of mysql settings to get it to work. so you edit /etc/mysql/my.cnf and change bind_address to your actual IP. And, if and when your IP changes because you’re using dhcp like mine is sure to, this is going to blow up on you. Not sure how to fix that one, actually. Well, gain control of your router and set a static ip for the mac address of your server, but I have to get regina to let me do that.
Anyway, there was also an instruction for changing mysql.db(host) to %, but I don’t think that’s necessary. We’ll see if I ever change it back. I did it. I haven’t undone it. But I don’t think it’s necessary.
So, finally, I”m able to connect from my other computer. It’s got the rows there. Now to solve the scores issue. I’ve been using amarok for 3 years now. I’m not about to give up my scores.
After digging in for a couple of hours and playing with it, here’s what I did. First I exported the data from the old sqlite database, following the instructions here:
sqlite3 collection.db .dump | \
egrep -vi '^(BEGIN TRANSACTION|COMMIT|CREATE|INSERT INTO "devices")' | \
perl -pe 's/INSERT INTO \"(.*)\" VALUES/INSERT INTO \1 VALUES/' > temp.sql
Then, I used a little grep magic strip out the statistics info, and a little sed to change “statistics” into “tempstats” for the insert statements. Next, I used
sqlite3 collection.db .schema statistics > tempstats.sql to get my create statement. You have to change the table name again to tempstats or whatever, and reduce it from 1024 to something less for the url field. I used 256, you might be able to get away with like 384 if you need the extra characters.
Then, back in your mysql instance, you can do
source /path/to/temp.sql to import the statistics table.
I should note now that this is NOT efficient. It’s a dirty, unoptimized hack. If you have a better way of doing it, please, for the love of spaghetti, let me know.
Anyway, at that point, you can do a really ugly update on join to get your scores back:
update statistics right join urls on urls.id = statistics.url right join tempstats on tempstats.url = urls.rpath set statistics.score = tempstats.percentage;
It took my nettop about 7 minutes to do this on about 4k rows updated. That join is NASTY. I just don’t know enough about db’s to make it cleaner.
I’m thinking about learning enough python to do this that way, but let’s be honest. It’s working now, so I’ll probably never get around to it.
Next step, I gotta sit down and change the database schema to handle multiple users.