Dear all!

I have issues when importing my old collection.db from amarok 1.4 to amarok2: From my 9666 songs it only imports 3066.

I tracked the problem down with an SQlite browser to entries in the "statistics" area of collection.db:

I have my all my music files mounted on "/mnt/net/10.0.0.1/NAS/Music & Video/CD/file.ogg"

the collection.db
contains 2 types of entries:
*SOME* tracks have an entry of:
"./CD/file.ogg" with a deviceid of "12". All ratings are there
and *ONLY SOME* have an entry of
"./mnt/net/10.0.0.1/NAS/Music & Videos/CD/file.ogg" with a deviceid of "-1" all are there as well.
*SOME* have both entries.

It seems only the files with entries "./mnt/net/..." get imported correctly.

QUESTION: How could I also import the 6000 other files with only the "./CD/file.ogg" entries?
I tried to mount it directly under root, i.e. "mkdir /CD" and mount it as "/CD/file.ogg" but it didn't get imported.

Is there a (free) SQ-Database editor that I could use to fix the links in the db?

Is there another way to extract mainly the ratings and get that into amarok2?
YES, I know, I could print out the old amarok.db and manually re-rate my files..., but is there a clever way to do so?

Regards, sulla

Don't know, if I get you right:
All 9666 songs have ratings, or only the 3066?

As a SQLite editor you can use this extension for firefox. It opens the db in a seperate window or a tab.

Greetings
m0nk

Hi!

All 9666 songs have "rating"s. I rated them manually a few years ago only very few are unrated, but in collection.db they still have a "rating" of "0"

in the "statistics" table of collection.db it seems that only the 3066 files have an "url" starting with "./mnt/net/10.0.0.1/.../CD/..."
the others have a field "url" starting with "./CD/..." which apparently was o.k. with amarok 1.3 and 1.4.

I'll try the firefox extension to edit collection.db and see if amarok2 will import after "fixing" the 6000 urls starting with "./CD/..."

regards, sulla

I tried the firefox extension. However, I can only edit the entries one by one, I would need to mass-edit the urls. This seems not possible with this editor...

Hm...

Then you must do it the hard way:
All italics are commands in console
- backup your collection.db
-cd to the directory containing your collection(.db)
-sqlite3 collection.db
You should be connected to the db and have a sqlite prompt.
Now write your statistics table to file
sqlite>.output statistics.txt
sqlite>select * from statistics;
sqlite>delete from statistics;
Wipes out all stats (Hopefully your back-up is in save place!!!)
sqlite>.quit
Now you should have a file statistics.txt in which you can search and replace the string "./CD" with ""./mnt/net/10.0.0.1/NAS/Music & Videos/CD".
Save the file and reconnect to db.
sqlite>.separator |
Setting the separator to | because entries in statistics are divided in this way
sqlite>.import statistics.txt statistics
sqlite>select * from statistics;
sqlite>.quit
Et voila, you're done.
The dots in front of sqlite3 commands are necessary, as are the semicolon at the end of SQL-statements
Don't know, if it will help with the import, but it works. I've changed 30,000+ entries
Remember, that only your playcounts actually get imported.
You can also fix the statistics table (ratings) in A2 by hand. But that is another story.

Greetings
m0nk

Dear Dieter!

Thanks for your help!
before going through editing the database via a terminal (GREAT that that works!) I gave the firefox-sqlite editor a chance. I inspected the database a bit and discovered a table "devices" which defined the device "12" as "/mnt/net/10.0.0.1/NAS/NAS/CD..."
Apparently (I am sure the devs know this, but for me it was a discovery) the filenames in the "statistics" table are given relative to the path specified in the "device" table.
For some reason, the divice mapping in the ubuntu upgrade from 8.10 to 9.04 (=upgrade form KDE3 to KDE4) changed, and NFS drives are mounted somewhat differently now, i.e. my mountpoint was
/mnt/net/10.0.0.1/NAS/NAS/CD...
and became
/mnt/net/10.0.0.1/NAS/CD...
after the kubuntu upgrade, so the "import collection" feature did not find the files any more.
Why some entries in the collection.db are specified absolute and some relative to a device, I don't know.

I fixed the "12" entry in the "devices" table from the firefox-sql-GUI, and amarok2 imported my ratings correctly.
WOW!!!

The "import collection" feature still did not import the tracks themselves, but after a rescan of the collection (to import the tracks themselves freshly) it seems that ALL my ratings could be recovered and associated with the right tracks.

So, after all, this issue seems fixed for me!

I hobe amarok2 brings with it a more robust database system that amarok1.x did, i mean in terms of tracking files when varying mountpoints (or moving files, which might be equivalent)

Thanx for help!

Fine, that it worked.
Btw. Amarok 2 does the same. I mean relative path in config file and devices in the db. That's perhaps the cause for some trouble I have with my external drive, holding my audio files.
But you said, that amarok also imported your ratings correctly? With score and the ratings calculated on playcount, or only the playcount?

Greetings
m0nk

Hi m0nk!

I can't say for sure.
The new format that amarok2 stores its statistics in, i.e. the 3 files "statistics.frm", "statistics.MYD" and "statistics.MYI" are not sq-lite databases and they are binary, I can't open them. If you can tell me how to open the amarok 2 databases I can check if the entries contain all columns of the amarok 1.4 database correctly.

The only thing I can say that it obviously imported all my stars-ratings and also the lyrics correctly, as far as I can see.

regards, sulla

Thank you! That was all  wanted to know. I'm using a ruby script to read in my last.fm stats into a amarok 1.4 database and it only updates the playcount. So I wanted to know, if import of score and rating is working.

Greetings
m0nk