Raphael's Blog

Let's start with the first way:

cd ~/.kde/share/apps/amarok && \
sqlite3 collection.db .dump | \
grep -v "BEGIN TRANSACTION;" | \
grep -v "COMMIT;" | \
perl -pe 's/INSERT INTO \"(.*)\" VALUES/INSERT INTO \1 VALUES/' |
mysql -u root -p amarok

not bad, but there are some glitches :

• The sqlite3 ... line contains a space at the end, which makes the command fail (can't be noticed on this blog post, but check the HowTo and you'll see what I mean)
  
• You will get error messages like ERROR 1071 (42000) at line 25380: Specified key was too long; max key length is 1000 bytes
  
• Replacing every VARCHAR(1024) with VARCHAR(996) and every VARCHAR(256) with VARCHAR(255) doesn't help either
  

The second way (using sqlite browser) isn't very helpful either.

Export the statistics database to a dump file, amarok_dump.sql

only exporting the statistics database will result in many lost information .


So, let's hack around the first way :

First of all let's better let amarok create the database:

• Start Amarok
  
• change to MySQL
  
• close Amarok (don't play any song)
  

Up to this point it seems it is the second example of the MySQL HowTo .

Now let's export the old collection:

(cd ~/.kde/share/apps/amarok && \
sqlite3 collection.db .dump > ~/amarok.sql)

Now we got the dump and the new database. Let's remove stuff we don't need (because Amarok already created the new database):

cat ~/amarok.sql | \
grep -v "BEGIN TRANSACTION;" | \
grep -v "COMMIT;" | \
grep -v "CREATE TABLE" | \
grep -v "CREATE INDEX" | \
grep -v "CREATE UNIQUE INDEX" > ~/amarok.sql

At this point everything seems to be ok: we'll only import the data, not the structure.

If you try to import the resulting amarok.sql it will fail, so let's fix that too:

cat ~/amarok.sql | \
grep -v "INSERT INTO admin" | \
perl -pe 's/INSERT INTO \"(.*)\" VALUES/INSERT INTO \1 VALUES/' > ~/amarok.sql

Nice, time to import:

cat ~/amarok.sql | mysql -u amarok -p amarok

Enter the password you set for the database at Amarok.

That's it , normally you will have all your data in your mysql database.


This is for the lazy ones who don't want to part by part :

(cd ~/.kde/share/apps/amarok && \
sqlite3 collection.db .dump | \
grep -v "BEGIN TRANSACTION;" | \
grep -v "COMMIT;" | \
grep -v "CREATE TABLE" | \
grep -v "CREATE INDEX" | \
grep -v "CREATE UNIQUE INDEX" | \
grep -v "INSERT INTO admin" | \
perl -pe 's/INSERT INTO \"(.*)\" VALUES/INSERT INTO \1 VALUES/' | \
mysql -u amarok -p amarok)

By the way, report if it really works, it hasn't worked for me because my collection.db was updated by Amarok 1.4.6 but the MySQL database was created by 1.4.4 and the version of Amarok I'm using is 1.4.4 (in other words: versions mess! ).


EDIT: I fixed a missing .db in the all-in-one command. I can confirm that it works By the way, if you don't use megatunes (or whatever is called that shop thingy) but you downloaded it's music database and don't want to use it, don't forget to add a grep -v "megatunes" | \. Otherwise mysql will complain about a missing databse.