Postgresql, Drupal 5.7, phpBB 3.0.0 - creating a new user (at the end of step install_module)

Posted by _Mitto_ on February 18, 2008 at 11:35pm

Fresh install this time with Drupal 5.7 and stabile release of phpBB3 forum.

First time i had problems with sequences.
This can be easy done like this:

connect with database:
~ $ psql drupal_db

To view sequence tables:
drupal_db=> \ds

And there i found sequence table that i looking for
drupal_db=> SELECT * FROM drupal_users_uid_seq;

First have to restart it
drupal_db=> ALTER SEQUENCE drupal_users_uid_seq RESTART WITH 1000;
and than fix minimal value
drupal_db=> ALTER SEQUENCE drupal_users_uid_seq MINVALUE 1000;

This sure does work!

My problem is with creating new user. I get worning:

warning: pg_query() [function.pg-query]: Query failed: ERROR: operator does not exist: ` integer LINE 1:

...

So problem is with charther `. PostgreSQL don't like it.

Comments

good news: postgresql sintax

Posted by _Mitto_ on February 23, 2008 at 11:52pm

i am so stupid ..

First i edited "modules/phpbb/phpbb_diagnostic.inc.php"
from

<?php
$row = db_fetch_array(db_query("SELECT * FROM {$phpbbcfg['db_groups']} WHERE <code>group_name</code> = 'REGISTERED'"));
?>

<?php
$row = db_fetch_array(db_query("SELECT * FROM {$phpbbcfg['db_groups']} WHERE group_name = 'REGISTERED'"));
?>

So http://yourdomain.com/drupal/phpbb/diagnostic works now

Second, I removed ALL ` charters from "modules/phpbb/phpbb.module"

And now i have only this warning

    * warning: pg_query() [function.pg-query]: Query failed: ERROR: operator does not exist: character varying + integer LINE 1: ...DATE phpbb_config SET config_value = config_value + 1 WHERE ... ^ HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. in /var/www/gavgarji/htdocs/includes/database.pgsql.inc on line 125.
    * user warning: query: UPDATE phpbb_config SET config_value = config_value + 1 WHERE config_name = 'num_users' in /var/www/gavgarji/htdocs/includes/database.pgsql.inc on line 144.

warning fix

Posted by _Mitto_ on February 24, 2008 at 12:26am

i don't know which line in "modules/phpbb/phpbb_diagnostic.inc.php"

from

<?php
  $query = "UPDATE {$phpbbcfg['db_config']} SET config_value = config_value + 1 WHERE config_name = 'num_users'";
?>

<?php
  $query = "UPDATE {$phpbbcfg['db_config']} SET config_value = 'config_value + 1' WHERE config_name = 'num_users'";
?>

And now it creating user in phpbb database too.

Next error:
I login new user and get this:

    * warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "&quot; LINE 1: INSERT INTO phpbb_sessions (session_id,session_user_id,... ^ in /var/www/gavgarji/htdocs/includes/database.pgsql.inc on line 125.
    * user warning: query: INSERT INTO phpbb_sessions (session_id,session_user_id,session_last_visit,session_start,session_time,session_ip,session_browser,session_forwarded_for,session_page,session_viewonline , session_autologin , session_admin ) VALUES ('5106682cec739683fd451039b8d6228e', 1000, 1203812046, 1203812046, 1203812046, '193.my.IP...141', 'Mozilla/5.0 (X11; U; Linux i686; sl; rv:1.8.1.12) Gecko/20080207 Ubuntu/7.10 (gutsy) Firefox/2.0.0.12', '', 'index.php', '1', '0', '0') in /..../htdocs/includes/database.pgsql.inc on line 144.

so i remove all ` from "modules/phpbb/phpbb_sessions.inc.php" too.

NOW WORKS!!

huh.. time for bed :D

So the conclusion is to get

Posted by arkepp on March 14, 2008 at 2:19am

So the conclusion is to get rid of all ` and it works in Postgresql too? I'll be happy to do that.

well i don't have problems

Posted by _Mitto_ on March 14, 2008 at 11:48pm

well i don't have problems about Postgresql with that sintax
(i have other problems that not belong here)

in acp_users is unique code:

<?php
switch ($db->sql_layer){
    case 'oracle':
    case 'firebird':
    case 'postgres':
        $right_delim = $left_delim = '"';
        break;
    case 'sqlite':
    case 'mssql':
    case 'mssql_odbc':
        $right_delim = ']';
        $left_delim = '[';
    break;
    case 'mysql':
    case 'mysql4':
    case 'mysqli':
        $right_delim = $left_delim = '`';
        break;
}
?>

So i shoud use " too?! But without any charter it works.

just one case was: (something like this)
table = table +1
and i chage to
table = 'table +1'

Yeah, I've unintentionally

Posted by arkepp on March 14, 2008 at 11:57pm

Yeah, I've unintentionally been abusing the db abstraction layer for the longest time, I didn't realize it had an printf kind of syntax.

I'll see whether I can do it properly, should make all your woes go away. At least in phpbb.module, need to check phpBB.

Postgresql 8.3 looks so good I'm considering coverting :)

` - problem

Posted by _Mitto_ on March 15, 2008 at 5:31pm

Yes .. i found that case.
And it doesn't show properly:

In ACP, ACP Index under Board statistics:

STATISTIC	VALUE
Number of posts:	1
Number of users:	config_value + 1
Number of attachments:	0

and i can't find where it is now?!

I made this problem:
when i chage this:

<?php
<code>config_value</code> = config_value + 1
?>

to this

<?php
config_value = 'config_value + 1'
?>

Not sure I follow, but you

Posted by arkepp on March 15, 2008 at 5:40pm

Not sure I follow, but you can just do a separate select, do the addition in Drupal and then update. Not entirely safe, but it shouldn't cause any major problems anyway.

I found! It is in

Posted by _Mitto_ on March 15, 2008 at 6:48pm

I found! It is in phpbb.module
// Increment total number of users

If i change this:

<?php
... <code>config_value</code> = config_value + 1 ...
?>

to this:

<?php
config_value = 'config_value + 1'
?>

I get config_value + 1 for numbers of users.

And if i change it to this:

<?php
... config_value = config_value + 1 ...
?>

i get error:

    * warning: pg_query() [function.pg-query]: Query failed: ERROR: operator does not exist: character varying + integer LINE 1: ...DATE phpbb_config SET config_value = config_value + 1 WHERE ... ^ HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. in /var/www/gavgarji/htdocs/includes/database.pgsql.inc on line 125.
    * user warning: query: UPDATE phpbb_config SET config_value = config_value + 1 WHERE config_name = 'num_users' in /var/www/gavgarji/htdocs/includes/database.pgsql.inc on line 144.

normaly, it is wrong as it writes.

So as you said we should fist select number of users and than write it to table. right?

<?php
 // Increment total number of users
  $row = db_fetch_array(db_query("SELECT config_value FROM {$phpbbcfg['db_config']} WHERE config_name='num_users'"));
  $num_users = $row['config_value'];

  $query = "UPDATE {$phpbbcfg['db_config']} SET config_value = {$num_users + 1} WHERE config_name = 'num_users'";
  $res = db_query($query);
}
?>

or:

I know that this:

<?php
... config_value = config_value*1 + 1 ...
?>

would work in javascript, or how shoud we convert it (char. -> int.) with php?

intval

Posted by arkepp on March 15, 2008 at 6:43pm

http://us3.php.net/intval

intval vs. SELECT

Posted by _Mitto_ on March 15, 2008 at 7:40pm

With intval:

<?php
  // Increment total number of users
  $query = "UPDATE {$phpbbcfg['db_config']} SET config_value = {intval(config_value) + 1} WHERE config_name =
'num_users'";
  $res = db_query($query);
}
?>

and

<?php
  // Increment total number of users
  $query = "UPDATE {$phpbbcfg['db_config']} SET config_value = intval(config_value) + 1 WHERE config_name =
'num_users'";
  $res = db_query($query);
}
?>

doesn't work:

    * warning: pg_query() [function.pg-query]: Query failed: ERROR: function drupal_intval(character varying) does not exist LINE 1: UPDATE phpbb_config SET config_value = drupal_intval(config_... ^ HINT: No function matches the given name and argument types. You may need to add explicit type casts. in /var/www/gavgarji/htdocs/includes/database.pgsql.inc on line 125.
    * user warning: query: UPDATE phpbb_config SET config_value = drupal_intval(config_value) + 1 WHERE config_name = 'num_users' in /var/www/gavgarji/htdocs/includes/database.pgsql.inc on line 144.

Any way .. this:

<?php
// Increment total number of users
  $row = db_fetch_array(db_query("SELECT config_value FROM {$phpbbcfg['db_config']} WHERE config_name='num_users'"));
  $num_users = $row['config_value'];

 
$query = "UPDATE {$phpbbcfg['db_config']} SET config_value = $num_users + 1 WHERE config_name = 'num_users'";
  $res = db_query($query);
}
?>

works fine for me!

And when i delete user from drupal, it stil exist in phpBB. I must delete him from there too.

You weren't doing the

Posted by arkepp on March 15, 2008 at 7:58pm

You weren't doing the increment in PHP, you were telling Postgresql to do this opreation:
"intval(config_value) + 1"

Postgresql has no clue about intval(), you need to do it outside of the string you send to the database:
$query = "UPDATE {$phpbbcfg['db_config']} SET config_value = " . intval($row['config_value']) + 1 ." WHERE config_name = 'num_users'";

The delete is documented and done this way for good reason. Drupal wouldn't give you the option of keeping or deleting a user's posts.

tnx for all replays! I don't

Posted by _Mitto_ on March 15, 2008 at 10:09pm

tnx for all replays!

I don't know why this

<?php
  $query = "UPDATE {$phpbbcfg['db_config']} SET <code>config_value</code> = config_value + 1 WHERE config_name = 'num_users'";
?>

works in MySQL, if it does. This is why i didn't even try to write some more properly code.

Maybe we should delete users from phpBB, but i have to check documentation!