Announcement

  •  » Beta testing
  •  » PostgreSQL installation problem [2.1.1]

#16 2010-07-01 16:53:07

nicolas
Former Piwigo Team
2004-12-30
1176

Re: PostgreSQL installation problem [2.1.1]

Gijs wrote:

I managed to fix the error by making three changes in the include/functions_user.inc.php



line 681
<    $query = 'SELECT c.id cat_id, global_rank';
>    $query = 'SELECT c.id AS cat_id, global_rank';

line 684
<   MAX(date_available) date_last, COUNT(date_available) nb_images
>   MAX(date_available) AS date_last, COUNT(date_available) AS nb_images

This allows me to complete the setup. I assume I will find other errors however :-/

Hope this helps others.

Yes you're right. The AS keyword is mandatory for older release of PostgreSQL. (older that 8.4)

Offline

 

#17 2010-07-01 17:24:33

Gijs
Guest

Re: PostgreSQL installation problem [2.1.1]

I will not move to 8.4 soon.. But I am slowly including all AS where needed..

I'm guessing:

SELECT t.*, count(*) counter
SELECT t.*, count(*) AS counter

is the next one..

 

#18 2010-07-01 21:15:25

nicolas
Former Piwigo Team
2004-12-30
1176

Re: PostgreSQL installation problem [2.1.1]

Gijs wrote:

I will not move to 8.4 soon.. But I am slowly including all AS where needed..

I think it will hapen soon because next release (9.0) comes !

Gijs wrote:

I'm guessing:

SELECT t.*, count(*) counter
SELECT t.*, count(*) AS counter

is the next one..

Il open a ticket in our bug tracker : [Bugtracker, ticket 1754, fixed] The keyword AS is mandatory for alias in POstgreSQL queries. Don't hesitate to post queries here after if I forgot somes !

Last edited by nicolas (2010-07-01 21:20:45)

Offline

 

#19 2010-07-02 07:41:30

Gijs
Guest

Re: PostgreSQL installation problem [2.1.1]

nicolas wrote:

Gijs wrote:

I will not move to 8.4 soon.. But I am slowly including all AS where needed..

I think it will hapen soon because next release (9.0) comes !

Not that hung up on following the latest. I use postgresql for more than just piwigo. Not keen on migrating all databases at this point in time.

Gijs wrote:

I'm guessing:

SELECT t.*, count(*) counter
SELECT t.*, count(*) AS counter

is the next one..

Il open a ticket in our bug tracker : [Bugtracker, ticket 1754, fixed] The keyword AS is mandatory for alias in POstgreSQL queries. Don't hesitate to post queries here after if I forgot somes !

Hi Nicolas!

Thanks for this.

so far I have added 'AS' in a few places. Did not keep a record, but will start doing that now.

these are the files I've edited so far:

config_default.inc.php
include/functions_user.inc.php
include/functions_notification.inc.php
include/functions_tag.inc.php

I'm running into loads more errors, with tags, with rss feed, with adding photo's .... I'll try and keep more detailed notes. I hoped a few quick edits would suffice, but ..

 

#20 2010-07-02 07:47:51

Gijs
Guest

Re: PostgreSQL installation problem [2.1.1]

I could be posting this in the wrong thread. But this is one of the errors I get, when trying to install the local files plugin. Have not been able to find the bug ..



Warning: pg_query() [function.pg-query]: Query failed: ERROR: column "LocalFilesEditor" does not exist LINE 3: VALUES ("LocalFilesEditor" , "off" , "LocalFiles Editor plug... ^ in /(path) include/dblayer/functions_pgsql.inc.php on line 122
INSERT INTO piwigo_config (param,value,comment) VALUES ("LocalFilesEditor" , "off" , "LocalFiles Editor plugin parameters");
ERROR: column "LocalFilesEditor" does not exist LINE 3: VALUES ("LocalFilesEditor" , "off" , "LocalFiles Editor plug... ^

 

#21 2010-07-02 10:03:13

nicolas
Former Piwigo Team
2004-12-30
1176

Re: PostgreSQL installation problem [2.1.1]

Gijs wrote:

I could be posting this in the wrong thread. But this is one of the errors I get, when trying to install the local files plugin. Have not been able to find the bug ..

Warning: pg_query() [function.pg-query]: Query failed: ERROR: column "LocalFilesEditor" does not exist LINE 3: VALUES ("LocalFilesEditor" , "off" , "LocalFiles Editor plug... ^ in /(path) include/dblayer/functions_pgsql.inc.php on line 122
INSERT INTO piwigo_config (param,value,comment) VALUES ("LocalFilesEditor" , "off" , "LocalFiles Editor plugin parameters");
ERROR: column "LocalFilesEditor" does not exist LINE 3: VALUES ("LocalFilesEditor" , "off" , "LocalFiles Editor plug... ^

It's not related to AS keyword but it's also a PostgreSQL issue. PostgreSQL and SQLite (and probably others database engines) wanted fields values to enclosed by single quotes and not double ones. The error reads strange because PostgreSQL engine thinks "LocalFilesEditor" is a field and not a value. double quotes are use for fields to protect reserved words like "count" or things like that.

I created the new [Bugtracker, ticket 1755, fixed] Needs single quotes in queries official plugins Feel free to do it yourself next time.

Last edited by nicolas (2010-07-02 10:04:28)

Offline

 

#22 2010-07-02 11:42:34

Gijs
Guest

Re: PostgreSQL installation problem [2.1.1]

nicolas wrote:

Gijs wrote:

I could be posting this in the wrong thread. But this is one of the errors I get, when trying to install the local files plugin. Have not been able to find the bug ..

Warning: pg_query() [function.pg-query]: Query failed: ERROR: column "LocalFilesEditor" does not exist LINE 3: VALUES ("LocalFilesEditor" , "off" , "LocalFiles Editor plug... ^ in /(path) include/dblayer/functions_pgsql.inc.php on line 122
INSERT INTO piwigo_config (param,value,comment) VALUES ("LocalFilesEditor" , "off" , "LocalFiles Editor plugin parameters");
ERROR: column "LocalFilesEditor" does not exist LINE 3: VALUES ("LocalFilesEditor" , "off" , "LocalFiles Editor plug... ^

It's not related to AS keyword but it's also a PostgreSQL issue. PostgreSQL and SQLite (and probably others database engines) wanted fields values to enclosed by single quotes and not double ones. The error reads strange because PostgreSQL engine thinks "LocalFilesEditor" is a field and not a value. double quotes are use for fields to protect reserved words like "count" or things like that.

I created the new [Bugtracker, ticket 1755, fixed] Needs single quotes in queries official plugins Feel free to do it yourself next time.

Ah! Thanks. What can I do to fix this error locally? Replace some or all " by '  in files in /plugins/LocalFilesEditor?

 

#23 2010-07-02 12:31:26

nicolas
Former Piwigo Team
2004-12-30
1176

Re: PostgreSQL installation problem [2.1.1]

Gijs wrote:

Ah! Thanks. What can I do to fix this error locally? Replace some or all " by '  in files in /plugins/LocalFilesEditor?

If you can read and apply a diff file, here it comes :

Code:

index 35aca4b..0f1d31e 100644
--- a/plugins/LocalFilesEditor/maintain.inc.php
+++ b/plugins/LocalFilesEditor/maintain.inc.php
@@ -27,7 +27,7 @@ function plugin_install()
 
   $query = '
 INSERT INTO ' . CONFIG_TABLE . ' (param,value,comment)
-VALUES ("LocalFilesEditor" , "off" , "LocalFiles Editor plugin parameters");';
+VALUES (\'LocalFilesEditor\' , \'off\' , \'LocalFiles Editor plugin parameters\');';
 
   pwg_query($query);
 }
@@ -36,7 +36,7 @@ function plugin_uninstall()
 {
   global $prefixeTable;
 
-  $query = 'DELETE FROM ' . CONFIG_TABLE . ' WHERE param="LocalFilesEditor" LIMIT 1;';
+  $query = 'DELETE FROM ' . CONFIG_TABLE . ' WHERE param=\'LocalFilesEditor\' LIMIT 1;';
   pwg_query($query);
 }

Don't forget that apply that kind of patch prevents you to automatically upgrade to future releases.

Offline

 

#24 2010-07-02 12:55:52

Gijs
Guest

Re: PostgreSQL installation problem [2.1.1]

Aha!

Thanks for the patch!

vi  maintain.inc.php and then

:%s/\"/\'/g

does the same :-). I tried that... however, this brings me to a new error


Parse error: syntax error, unexpected T_STRING in plugins/LocalFilesEditor/maintain.inc.php on line 30


in spostgresql-8.3-main.log : I *also* keep seeing this. I expect it is a similar type or related.

CEST STATEMENT: 
        SELECT id AS id,username AS username
        FROM piwigo_users
          ORDER BY CONVERT(username,CHAR)

 

#25 2010-07-02 13:00:09

Gijs
Guest

Re: PostgreSQL installation problem [2.1.1]

Gijs wrote:

Aha!

Thanks for the patch!

vi  maintain.inc.php and then

:%s/\"/\'/g

does the same :-). I tried that... however, this brings me to a new error


Parse error: syntax error, unexpected T_STRING in plugins/LocalFilesEditor/maintain.inc.php on line 30


in spostgresql-8.3-main.log : I *also* keep seeing this. I expect it is a similar type or related.

CEST STATEMENT: 
        SELECT id AS id,username AS username
        FROM piwigo_users
          ORDER BY CONVERT(username,CHAR)

I should learn to read more closely. It is VERY warm, maybe that counts as an excuse. I overlooked the
escape \ in front of the '.

:%s/\"/\\\'/g  would have done it..

It works now.

The

CEST STATEMENT: 
        SELECT id AS id,username AS username
        FROM piwigo_users
          ORDER BY CONVERT(username,CHAR)


however remains..

 
  •  » Beta testing
  •  » PostgreSQL installation problem [2.1.1]

Board footer

Powered by FluxBB

github twitter facebook google+ newsletter Donate Piwigo.org © 2002-2019 · Contact