postgresql dml function problems

Dear Developers!

I am using Debian Lenny and apache2 with PostgreSQL 8.4 and created an UTF-8 database for the gallery. My gallery reside at piwigo.kaktusz.eu.

So far I uploaded 1 picture, created 2 categories ('Egyeb' and 'Egyéb' < é is a hungarian character) and the picture is in the category 'Egyéb'.

I have the following errors as a guest user:

1. Specials > Random pictures
Warning: pg_query() [function.pg-query]: Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 7: ORDER BY date_available DESC, file ASC, id ASC ^ in /home/www/piwigoKaktuszEu/include/dblayer/functions_pgsql.inc.php on line 122
SELECT DISTINCT(id) FROM piwigo_images INNER JOIN piwigo_image_category AS ic ON id = ic.image_id WHERE image_id IN (-1) AND (category_id NOT IN (2,1) AND level<=0) ORDER BY date_available DESC, file ASC, id ASC ;
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 7: ORDER BY date_available DESC, file ASC, id ASC ^

2. Specials > Recent pictures
Warning: pg_query() [function.pg-query]: Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 8: ORDER BY date_available DESC,date_available DESC, file AS... ^ in /home/www/piwigoKaktuszEu/include/dblayer/functions_pgsql.inc.php on line 122
SELECT DISTINCT(id) FROM piwigo_images INNER JOIN piwigo_image_category AS ic ON id = ic.image_id WHERE date_available >= (CURRENT_DATE - '7 DAY'::interval)::date AND (category_id NOT IN (2,1) AND level<=0) ORDER BY date_available DESC,date_available DESC, file ASC, id ASC ;
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 8: ORDER BY date_available DESC,date_available DESC, file AS... ^



I have similar errors as user 'admin' plus these:

1. Clicking on both 'Egyéb' or 'Egyeb'
Warning: pg_query() [function.pg-query]: Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 8: ORDER BY date_available DESC, file ASC, id ASC ^ in /home/www/piwigoKaktuszEu/include/dblayer/functions_pgsql.inc.php on line 122
SELECT DISTINCT(image_id) FROM piwigo_image_category INNER JOIN piwigo_images ON id = image_id WHERE category_id = 2 ORDER BY date_available DESC, file ASC, id ASC ;
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 8: ORDER BY date_available DESC, file ASC, id ASC ^

2. Clicking on the link 'Administration' after installing some plugins
Warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "REPLACE" LINE 2: REPLACE INTO piwigo_plugins ^ in /home/www/piwigoKaktuszEu/include/dblayer/functions_pgsql.inc.php on line 122
REPLACE INTO piwigo_plugins (id, state) VALUES ('c13y_upgrade', 'inactive') ;
ERROR: syntax error at or near "REPLACE" LINE 2: REPLACE INTO piwigo_plugins ^

Piwigo_plugins
piwigo=# select * from piwigo_plugins;
id        | state  | version
------------------+--------+---------
c13y_upgrade     | active | 2.0.2
language_switch  | active | 2.1.1
LocalFilesEditor | active | 2.1.0
admin_multi_view | active | 2.1
rightClick       | active | 2.1.b
(5 rows)

If You need any further information or access I will answer here or I can change the password admin and let you into the gallery and You can reach me at miklos[dot]kolovics[at]psns[dot]hu

Best regards,
Miklós

Re: postgresql dml function problems

Dear Developers!

I found this 'REPLACE INTO' problem in some other components too, like in the install of 'Grum Plugins Classes.3' plugin.

I looked into this problem a bit and found what I thought earlier, that PostgreSQL do not have such sql command. I tried to look into the 'functions_pgsql.inc.php' but it's too difficult for me to develop a solution.

I found this page, which I hope will help you!
http://en.wikibooks.org/wiki/Converting … PostgreSQL

It is giving a solution for the usage of the MySQL REPLACE INTO command in PostgreSQL.

MySQL:
REPLACE [INTO] table [(column, [...])] VALUES (value, [...])

PostgreSQL:
CREATE FUNCTION someplpgsqlfunction() RETURNS void AS $$
BEGIN
IF EXISTS( SELECT * FROM phonebook WHERE name = 'john doe' ) THEN
UPDATE phonebook
SET extension = '1234' WHERE name = 'john doe';
ELSE
INSERT INTO phonebook VALUES( 'john doe', '1234' );
END IF;

RETURN;
END;
$$ LANGUAGE plpgsql;

Best regards,
Miklós

Re: postgresql dml function problems

You're right miklos.kolovics and we should use REPLACE INTO only in include/dblayer/functions_mysql.inc.php because it is a SQL command specific to MySQL.

That said, nicolas has tried to automatically replace the REPLACE INTO syntax into something more PostgreSQL compliant, see [Subversion] r4886 , I don't know why this trick doesn't work in your case.

Re: postgresql dml function problems

Hi plg!

I just checked the trunk diff against my "functions_pgsql.inc.php" file, and found the two pieces of code exactly the same in "function pwg_query($query)".

One weird thing. I always use 'vi' as an editor on debian, and in the trunk diff it shows code from line 71 to line 104. But in 'vi', if I "set number" then it shows me the code between line 92 and line 125.


Another weird thing I found when I just checked back things, that if I activate the plugin 'Check upgrades' I can still navigate between the tabs 'Plugins list',  'Check for updates' and 'Other plugins available' and if navigate away e.g. clicking on the link "Administration Home", then it gives me the error:

Warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "REPLACE" LINE 2: REPLACE INTO piwigo_plugins ^ in /home/www/piwigoKaktuszEu/include/dblayer/functions_pgsql.inc.php on line 122
REPLACE INTO piwigo_plugins (id, state) VALUES ('c13y_upgrade', 'inactive') ;
ERROR: syntax error at or near "REPLACE" LINE 2: REPLACE INTO piwigo_plugins ^

But when I press the 'Back' button and manually deactivate it, it just able to update the state in the table piwigo_plugins and everything goes on normally.

Any clue?

Best regards,
Miklós

Re: postgresql dml function problems

Hi plg!

One more thing. Maybe it is a good point to start. As I see the error messages, they give back the real MySQL statement. Maybe the RegExp is not satisfing in this part:

$replace_pattern = '`REPLACE INTO\s(\S*)\s*([^)]*\))\s*VALUES\(([^,]*),(.*)\)\s*`mi';

and that's why this is giving back zero:

if (preg_match($replace_pattern, $query, $matches)

and the 'else' is activated:

else
    ($result = pg_query($query)) or die($query."\n<br>".pg_last_error());
  }

giving back the original statement.

Best regards,
Miklós

Last edited by miklos.kolovics (2010-10-28 23:13:14)

Re: postgresql dml function problems

Dear Developers!

As Piwigo 2.1.4 was released I tried to install it as described in the 'Automatic Upgrade' guide. I successfully downloaded and installed 'Piwigo AutoUpgrade' plugin through the admin panel, but when I tried to active it, I got the following error message:

Warning: pg_query() [function.pg-query]: Query failed: ERROR: column "autoupdate_ignore_list" does not exist LINE 3: VALUES ("autoupdate_ignore_list" , "a:0:{}" , "Ignored plugi... ^ in /home/www/piwigoKaktuszEu/include/dblayer/functions_pgsql.inc.php on line 122
INSERT INTO piwigo_config (param,value,comment) VALUES ("autoupdate_ignore_list" , "a:0:{}" , "Ignored plugin list for Piwigo Auto Update plugin"); 
ERROR: column "autoupdate_ignore_list" does not exist LINE 3: VALUES ("autoupdate_ignore_list" , "a:0:{}" , "Ignored plugi... ^

Best regards,
Miklós