I created a new db, amarok2, and amarok 2 works correctly with it. Of course, no ratings.

The first discrepancy I've noticed is the playlists table. Here is the description for amarok 1.4:

Code: Select all

+----------+----------------+------+-----+---------+-------+
| Field    | Type           | Null | Key | Default | Extra |
+----------+----------------+------+-----+---------+-------+
| playlist | varchar(255)   | YES  | MUL | NULL    |       |
| url      | varbinary(255) | YES  | MUL | NULL    |       |
| tracknum | int(11)        | YES  |     | NULL    |       |
+----------+----------------+------+-----+---------+-------+

and here is the description for amarok 2:

Code: Select all

+-------------+---------------+------+-----+---------+----------------+
| Field       | Type          | Null | Key | Default | Extra          |
+-------------+---------------+------+-----+---------+----------------+
| id          | int(11)       | NO   | PRI | NULL    | auto_increment |
| parent_id   | int(11)       | YES  | MUL | NULL    |                |
| name        | varchar(255)  | YES  |     | NULL    |                |
| description | varchar(255)  | YES  |     | NULL    |                |
| urlid       | varchar(1000) | YES  |     | NULL    |                |
+-------------+---------------+------+-----+---------+----------------+

If you tell me how, I'd be happy to upload these two databases somewhere for you to compare them. I just don't know how to locate them or dump them onto the file system. Otherwise, do you have any hints on how to reconcile these differences?

Sure, I would be happy to take a look. Here is a link that does a good job of telling you how to dump the db to the filesystem:
http://www.go2linux.org/mysql-backup-mysqldump

essentially,

Code: Select all

mysqldump -u [user] -p [database_name] > [backupfile].dump

do that for each database, zip-tar the two files into a single tarball and email them to me. my username on this forum @sparksonline.net

I think you have definitely found A if not, THE problem. It looks like they are quite different and not easy to reconcile. However, inserting your old statistics into the new db might be fairly straight forward.

Perhaps it's just my ignorance about how mysql works, or with how programs interact with mysql, but the solution I was thinking of didn't seem too hard.

It's more of a hack, though. I was thinking of dropping the playlists table, and making a new one with all of the fields of both tables. That way, maybe the database would be compatible with both amarok 1.4 and amarok 2. While this would probably keep playlists from being in sync with both versions, that wouldn't be a problem for me because I use ratings to auto-generate playslists.

Again, thanks for the time you're taking to help me!

EDIT:
I noticed that in both databases the playlists table was empty; select * from playlists: returns no data. I dropped the playlists table in the old amarok database and started amarok 2. Amarok 2 recreated it, but with the same old description. It did not create the playlists table with the new, shorter description like it did when it was given an entirely new database.

EDIT 2:
I made a new super awesome playlists table with all fields merged. It was fun, but it didn't fix the problem of not being able to populate the playing pane. I also recreated the playlists table in the amarok 2 format and that had no effect as well. If the playlists table is part of the problem, it's definitely not the whole problem.

Sparken emailed me back saying that he is looking into merging the ratings and statistics from the original database into the new one. It looks like this is going to end with me upgrading all installations of amarok to amarok 2 and using the new database without trying to reconcile differences between the two database formats.

This is how I managed to import the ratings from the Amarok 1.4 style db
to the Amarok 2.x db. If anyone has an easier way or cleaner queries, please post them.

The way Am1 tracked stats and the way Am2 tracked stats are completely
different. Am2's DB is actually a smarter way to go as it tracks stats using a common url number.
It is the same URL number that the tracks table uses. SO it's an easy query... return
Title and stats join on URL #.

Am1 attached the stats to each file by using the actual URL as in "/media/music/song.mp3".
This would undoubtedly put a lot of db engine work into attaching stats to songs. The only way to attach the
old stats to the new table (that I could think of) was to return the URL number and ratings
from the old stats table by matching on Am2's URLS table's "rpath" and the old stats table's "URL"
as a subquery who's output got inserted into the new empty statistics table.

So long story short:

Code: Select all

// Step one)Dump the stats table out of the old amarok db:
mysqldump -u username -p password amarok --tables statistics > ~/stats.dump

// Step Two) Cheating Part
//Edit the stats.dump file with your favorite text editor replacing all mentions
//of the table name `statistics` with a different table name `statistics_old`
//there will probably be like 5 or 6 mentions.

// Step Three) Import the modified old stats table into the amarok2 db:
mysql  -u username -p password amarok2 <  ~/stats.dump

// now if you log into the mysql server you should see a table called "statistics_old"
mysql> describe statistics_old;
+-------------+----------------+------+-----+---------+-------+
| Field       | Type           | Null | Key | Default | Extra |
+-------------+----------------+------+-----+---------+-------+
| url         | varbinary(255) | NO   | PRI |         |       |
| deviceid    | int(11)        | NO   | PRI | 0       |       |
| createdate  | int(11)        | YES  | MUL | NULL    |       |
| accessdate  | int(11)        | YES  | MUL | NULL    |       |
| percentage  | float          | YES  | MUL | NULL    |       |
| rating      | int(11)        | YES  | MUL | 0       |       |
| playcounter | int(11)        | YES  | MUL | NULL    |       |
| uniqueid    | varbinary(32)  | YES  | UNI | NULL    |       |
| deleted     | tinyint(1)     | YES  |     | 0       |       |
+-------------+----------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

// Step Four) Now the query to match the old URL style to the new and update
// The new stats table with the old data:

mysql> insert into statistics(url, score, rating, playcount) select * from (select urls.id as url, statistics_old.percentage as score, statistics_old.rating, statistics_old.playcounter as playcount from urls join statistics_old on urls.rpath = statistics_old.url) as temptable;

Query OK, ## rows affected (0.01 sec)
Records: ##  Duplicates: 0  Warnings: 0

// TaDa It is done.
// You can drop the statistics_old table now if you wish

PS: Another thing I noticed was that both the old style db and the new style db have a value called "uniqueID" for each song in the stats. It appears to be something like an md5sum of each media file. I thought I could join on that. It turns out that Amarok 2 and Amarok 1 use a different method to generate that UniqueID hash because on the same source mp3 files, the values are completely different. Yet another compatibility problem.


Oh and I want to give a thank-you shout out to ascendant512 for sending me the databases and the Apache Friends (http://www.apachefriends.org/en) for their awesome XAMPP package!

Thanks for your help, Sparken!

Initially, I was unable to use your insert command. MySQL returned "ERROR 1062 (23000): Duplicate entry '403' for key 2" and then "ERROR 1062 (23000): Duplicate entry '2' for key 2". I tried manually re-making the statistics dump and got the same error.

It had successfully merged a some ratings, but many were still missing.

I ended up just dropping the amarok2 database and recreating it. After doing that, the statistics merged without issue. It is a mystery....

Yeah, that probably occurred because it had accumulated some stats since I started messing with the data. Now that I think about it, even playing one song would have probably been enough to cause a conflict.

By recreating the db, you fixed that issue.

So.... do the ratings look correct?

The ratings that are there are correct.
There are some missing, but that may just be because the songs aren't rated even if I thought I rated them. Either way, I don't really expect them to be in the original database.

You were a great help in re-enabling music on my desktop, thanks much!