Bug 111209

For me too, I have the same problem with AmaroK 1.3: the option 'watch folders for change' does not work at all (fortunately, manual rescan work fine).

I use MandrivaLinux package (KDE 3.4.2).

Which filesystem are your collections on?

The collections are on a reiserfs local filesystem

My collections are on local journalised FS EXT3. 

I have the same problem, library is on NFS filesystem (ext3).  On Suse 9.3 Pro using Suse GURU RPM's and on Gentoo 2005.0 compiled from source.  Postgresql is on same server as the music library, Postgresql version 8.  Works nice as I can share the database on all the 'puters I have, if I manually update the collection it works fine.

http://amarok.kde.org/component/option,com_simpleboard/Itemid,57/func,view/id,7645/catid,8/

I can confirm this bug. I'm using MySQL. So its probably nothing to do with the database being used. Its just a big fat regression.

I'am using both, Postgresql (8.0) and MySQL on amaroK 1.3.1; it works well with MySQL, but not with Postgresql. Obviously the bug is caused by the database-backend. It's sad! Manually updating changes nothing.

I just installed amarok with postgresql 8.0.4 + SUSE 10,

I can click update, and this is what I get... nothing more

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "album_temp_pkey" for table "album_temp"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "artist_temp_pkey" for table "artist_temp"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "genre_temp_pkey" for table "genre_temp"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "year_temp_pkey" for table "year_temp"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "directories_temp_dir_key" for table "directories_temp"
ERROR:  relation "amazon" already exists
ERROR:  relation "album_seq" already exists
ERROR:  relation "artist_seq" already exists
ERROR:  relation "genre_seq" already exists
ERROR:  relation "year_seq" already exists

It obviously created them when it initially created the database.

--

pg_dump 

--

--
-- PostgreSQL database dump
--

SET client_encoding = 'UNICODE';
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'Standard public schema';


SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = true;

--
-- Name: album; Type: TABLE; Schema: public; Owner: amarok; Tablespace:
--

CREATE TABLE album (
    id integer DEFAULT nextval('album_seq'::text) NOT NULL,
    name text
);


ALTER TABLE public.album OWNER TO amarok;

--
-- Name: album_seq; Type: SEQUENCE; Schema: public; Owner: amarok
--

CREATE SEQUENCE album_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.album_seq OWNER TO amarok;

[snip]

so it's obviously there... but the postgresql code is failing miserably... :(

Hello Mich, I'm coming back to you for your offer to look at Postgres issues in amaroK. Would you be so kind to take a look at this report?

Created attachment 13100 [details]
Fixes SQL errors with PgSQL

The attached patch fixes the SQL errors mentioned in this BR, and a couple of
other errors I've stumbled upon.

The "amazon relation already exists" is still there, but it should be a
non-issue. A possible fix would be to do something like if(!conn) &&
the-db-bla-bla != postgresql).. 

This patch, however, does not fix the originally reported error; the fact that
new additions to the collection are not automatically added - you need to
rescan the collection manually. I beleive I've tracked it down to the
directories table - but I've not been able to get any help from any
amarok-developers, in addition the code is poorly documented - to verify this.

I'm still not able to see how to the directories table is populated, so mine
remains empty at all times. If I read the code correctly, it should hold a list
of my collection directories with last_modif timestamps.

Adding an entry manually to the directories table, will make the auto-scanning
work - however for some bizarre reason the table is truncated every now and
then.

If someone with a bit more clue about the DB code, would give me a hand - I'd
like to track this further... 


Btw, as usual the patch should be tested on a MySQL backend, just in case I
broke something.

Just looking at the diff, 100% of the code changed should only affect postgresql, correct?

Yes.

I have auto-update which I believe is working correctly in amarok-1.3.2. The problem with PostgreSQL is that there is no REPLACE INTO SQL statement. 

In collectiondb.cpp, replace

     if (m_dbConnPool->getDbConnectionType() == DbConnection::postgresql) {
        query( QString( "UPDATE directories%1 SET changedate=%2 WHERE dir='%3';")
                        .arg( conn ? "_temp" : "" )
                        .arg( datetime )
                        .arg( escapeString( path ) ), conn );

with

     if (m_dbConnPool->getDbConnectionType() == DbConnection::postgresql) {
        QStringList values =
           query( QString( "SELECT * FROM directories%1 WHERE dir='%2';" )
                           .arg( conn ? "_temp" : "" )
                           .arg( escapeString( path ) ), conn );
        if (values.isEmpty()) {
           query( QString( "INSERT INTO directories%1 ( dir, changedate ) VALUES ( '%2', %3 );")
                           .arg( conn ? "_temp" : "" )
                           .arg( escapeString( path ) )
                           .arg( datetime ), conn );
        }
        else {
           query( QString( "UPDATE directories%1 SET changedate=%2 WHERE dir='%3';")
                           .arg( conn ? "_temp" : "" )
                           .arg( datetime )
                           .arg( escapeString( path ) ), conn );
        }

Created attachment 13148 [details]
Fixes autoscan with pgsql

Pheew, this patch fix the autoscan problem with autoscan on a postgresql
backend. I also removes many of the errors, that the previous patch I supplied
were suppose to remove (only this time, it removes them the correct way)

Basically, we only want to attempt to create the sequence relations if we
initalize the tables (calling createTables() without any db connection).
We do not want to create any temporary sequences, that was wrong on my part.
With the above fixed, we can again use 'select currval()' as the session is
initalized.

Now, the reason that the autoscan process did not work, was because that on
MySQL you can use "REPLACE INTO() .." which basically checks whether or not a
given row exists, and then appropriately either does an UPDATE() or a INSERT().
Since this is not valid SQL (except for MySQL), the orignal author of
postgresql support, had set an UPDATE call instead of REPLACE INTO, and as
there were no rows in the directory table, it could not do that.

Needless to say, it works now.

I've tested with SQLITE and PostgreSQL and all seems to work. Someone should
check with MySQL !

Patch committed, and backported to 1.3.6