I've found 2 bugs in SMF 2.0 beta 3 using postgresql so far.
1, In Sources/Subs-Db-postgresql.php
'db_unescape_string' => 'smf_postg_unescape_string',
should be
'db_unescape_string' => 'smf_db_unescape_string',
2, FIND_IN_SET not avail in postgresql
ERROR: function find_in_set(integer, character varying) does not exist
LINE 4: WHERE id_group = 1 OR FIND_IN_SET(1, additional_groups)
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
File: /srv/nfs4/homes/anders/public_html/smf/Sources/Subs-Membergroups.php
Line: 556
1. Good catch. Apparently the function got renamed between Beta 2.1 and Beta 3...
2. SMF does define a "find_in_set" function for PostgreSQL, but this is only where both parameters are text. Try running this query in phpPgAdmin:
CREATE OR REPLACE FUNCTION FIND_IN_SET(integer, character varying) RETURNS boolean AS
'SELECT
COALESCE(CAST($1 AS text) = ANY(STRING_TO_ARRAY(CAST($2 AS text), '','')), FALSE) AS result'
LANGUAGE 'sql';
Unfortunately my PostgreSQL test board is down at the moment, but I think that will work.
Your FIND_IN_SET does not fix all issues.
The problem is, that Subs-Post.php is not prepared for the boolean return value.
This leads to a broken send pm functionality. Only moderators will be able to post PMs.
Others will see
QuoteUser xxx has blocked your personal message
My quick and dirty fix for beta 3.1:
Replace Subs-Post.php line 1097:
Quoteif (!empty($row['ignored' ]))
with:
Quoteif (!empty($row['ignored' ]) && $row['ignored'] != f)
Maybe it would be better to fix the postgresql function.
But i guess the devs will know what to fix for 2.0 final :)
regards,
Bernhard Neuhauser
Thanks for the info. I'll take a look at it after dinner and report it if necessary :)
Which version of PostgreSQL are you using?
I just tested this on my PostgreSQL test board using the latest 7.4 release and I was unable to reproduce this.
Currently im running ubuntu 8.04tls which has 8.3.3-0ubuntu0.8.04 installed.
Additionally im using the 2.0 beta 3.1 public release so im not aware if your test-branch might already include some fixes in this direction.
My test board is running the latest development snapshot, but the particular code you referenced hasn't changed in Beta 4. The only difference is that SMF passes the user ID as a string instead of as an integer, so no extra "find_in_set" function is needed (just the one that comes with SMF).
I'll go set up SMF on my virtual machine (openSUSE with PostgreSQL 8.3.3) and see if I can reproduce it on that version of PostgreSQL.
Tested this in PostgreSQL 8.3.3 using the latest dev release and was able to reproduce it. I'll report it shortly.
Bug #2260: PostgreSQL functions return 't' or 'f' for boolean... (http://dev.simplemachines.org/mantis/view.php?id=2260)
Moving to bug reports
thx for the fast investigation ;)
Hi,
I am using SMF 2.0 RC 1 w/ Pg 8.3.6 and the coded solution still is wrong.
In line 1103 the conditions must be ANDed
if (!empty($row['ignored']) and strtolower($row['ignored']) === 't')
not ORed.
That will only work for PostgreSQL, and then causes things to break for all other systems (1 isn't the same as 't'). I'll take another look at this issue soon and see what solution I can come up with.