I wanted to migrate from mysql to postgres.
so i\'ve selected added a postgres user for amarok and let amarok insert all it\'s data by rescanning the collection.

Then i manually deleted all the data from the tables with:

DELETE FROM table;

only the data not the tables, so i had the structure as created by amaroK!

The next thing to do was to export the data from mysql.

mysqldump -u myuser -p --compatible=postgresql --extended-insert=TRUE -c amarok amazon > amazon.sql

This i did for every table with >=1 row except admin.

Then i manually removed the drop and create statements from the mysql exports, so i had all the data as insert statements.

one last minor setback was i had to change the boolean sample values in the mysql export from 1 to TRUE and 0 to FALSE manually.

After this i started amaroK again and voiala my data where all there in psotgresql.
But the statistics in the contextbrowser where representing only "not rated" and score=0 in the suggested field and favourite songs by artist.
(But the values in statistics where there i could sort by them in the collection).

So i begann to research a bit and digged out the sql command used for creating such a statement:

SELECT tags.title,tags.url,round(statistics.percentage + 0.4 ),statistics.rating,statistics.percentage as __discard FROM tags LEFT JOIN statistics ON statistics.url=tags.url WHERE true AND ( false OR tags.artist ILIKE \'3168\' ESCAPE \'/\' ) ORDER BY statistics.percentage DESC LIMIT 10 OFFSET 0 ;

i tested this manually, and as in amaroK it left me with empty fields in percentage score and rating.
As i figured out, when doing this left join and sort by percantage descending, all rows from tags which have no corresponding row in statistics (not heared yet) are weighted higher than those with values.

So my question to those who know sth about amarok and db is, have i done sth wrong while importing (if so what), or is this a problem with postgresql?

SELECT tags.url, rating, percentage, artist, playcounter FROM tags LEFT JOIN statistics ON statistics.url=tags.url
WHERE tags.url LIKE \'%Twenty_Four_Seven%\' ORDER BY statistics.percentage DESC;

When executing this on mysql the rows with actual values come first.
With postgres it\'s vice versa.

Any help or hint is appreciated
s|mon

Post edited by: simon, at: 2006/04/24 07:49

Ok some updates.

In postgresql NULL seems to be greater than values in ORDER BY DESC.
So to get around this one could write:

ORDER BY coalesce(stats.percentage,-100) DESC;

or

ORDER BY stats.percentage is NULL, stats.percentage desc;

i\'m currently trying and testing, but atm with current svn it seems not to be able to create a collection with postgresql. (Tables are created fine tough).

Here is what i had in mind so far for QueryBuilder :: sortBy
[code:1]
if ( CollectionDB::instance()->getType() == DbConnection::«»postgresql
&& ( descending ) &&
( ( value & valScore) || (value&valPercentage) ) //only adapt at score or rating
)
{
m_sort += tableName( table ) + ".";
m_sort += valueName( value );
m_sort += " is NULL,";
//
}
[/code:1]

but this is still not tested, maybe i\'m able to get my roK running with postgres later, then i\'ll test this.

P.S. I\'m not quite sure where in the code the query builder gets called, so maybe a separate sortby for the statistics is better.

Ok, Idon\'t know what i\'ve messed up, but with this patch and amarok installed (not just compiled and started from src dir) it seems to work.
I\'ve tested it only 5 mins with a collection of 3 albums. I\'ll do further testings now, with my whole collection. I\'ll write back and bugreport when everything works fine.

here the patch:
[code:1]
--- original_collectiondb.cpp 2006-04-24 23:20:27.000000000 +0200
+++ collectiondb.cpp 2006-04-24 23:29:04.000000000 +0200
@@ -4890,8 +4890,21 @@ QueryBuilder::«»sortBy( int table, Q_INT64
if ( b ) m_sort += \"LOWER( \";
if ( table & tabYear ) m_sort += \"(\";

+ // null values not at first
+ if ( CollectionDB::instance()->getType() == DbConnection::«»postgresql && ( descending ) &&
+ (( value & valScore) || (value & valPercentage))
+ ){
+ //only adapt at score or rating
+ m_sort += \"coalesce(\";
+ m_sort += tableName( table ) + \".\";
+ m_sort += valueName( value );
+ m_sort += \",-1)\";
+ //order by coalesce(stats.percentage,-1)
+
+ } else {
m_sort += tableName( table ) + \".\";
m_sort += valueName( value );
+ }

if (CollectionDB::instance()->getType() == DbConnection::«»postgresql)
{
[/code:1]

P.S.: maybe we should return 0 instead if -1 there (got to test this )
greeting s|mon

To make the migration complete i had to adapt the Sequences the amarok database uses, e.g.:
SELECT max(id) from album;
SELECT setval(\'album_seq\', yournewvalue);

As now everything looks ok on my side i submitted this under :
126228

Post edited by: simon, at: 2006/04/25 12:25

Post edited by: simon, at: 2006/04/25 12:34