postgresql 7.4.5 / drupal 4.5.1

forum module version information:
// $Id: forum.module,v 1.217 2004/11/24 22:56:21 dries Exp $

in the forum module, the IF statement in the SQL queries is not postgresql compliant:
IF(l.last_comment_uid, cu.name, l.last_comment_name) as last_comment_name

needs to be a case statement (or an IF function in postgresql could be written...):
CASE WHEN l.last_comment_uid = 1 THEN cu.name ELSE l.last_comment_name END as last_comment_name

case statements are supported by both mySQL and postgresql...

--------------------------
IF results in errors:
--------------------------
warning: pg_query(): Query failed: ERROR: invalid input syntax for type boolean: "13" in /usr/local/apache/htdocs/includes/database.pgsql.inc on line 45.

user error:
query: SELECT DISTINCT(n.nid), l.last_comment_timestamp, IF(l.last_comment_uid, cu.name, l.last_comment_name) as last_comment_name, l.last_comment_uid FROM node n , node_comment_statistics l, users cu, term_node r WHERE n.nid = r.nid AND r.tid = 354 AND n.status = 1 AND n.type = 'forum' AND l.last_comment_uid = cu.uid AND n.nid = l.nid AND '1' ORDER BY l.last_comment_timestamp DESC LIMIT 1 OFFSET 0 in /usr/local/apache/htdocs/includes/database.pgsql.inc on line 62.

warning: pg_query(): Query failed: ERROR: invalid input syntax for type boolean: "2" in /usr/local/apache/htdocs/includes/database.pgsql.inc on line 45.

user error:
query: SELECT DISTINCT(n.nid), l.last_comment_timestamp, IF(l.last_comment_uid, cu.name, l.last_comment_name) as last_comment_name, l.last_comment_uid FROM node n , node_comment_statistics l, users cu, term_node r WHERE n.nid = r.nid AND r.tid = 353 AND n.status = 1 AND n.type = 'forum' AND l.last_comment_uid = cu.uid AND n.nid = l.nid AND '1' ORDER BY l.last_comment_timestamp DESC LIMIT 1 OFFSET 0 in /usr/local/apache/htdocs/includes/database.pgsql.inc on line 62.

--------------------------
suggested patch:
--------------------------
function forum_get_forums($tid = 0) { // ~at around line 380:
// remove:
-- $topic = db_fetch_object(db_query_range('SELECT DISTINCT(n.nid), l.last_comment_timestamp, IF(l.last_comment_uid, cu.name, l.last_comment_name) as last_comment_name, l.last_comment_uid FROM {node} n ' . node_access_join_sql() . ", {node_comment_statistics} l /*! USE INDEX (node_comment_timestamp) */, {users} cu, {term_node} r WHERE n.nid = r.nid AND r.tid = %d AND n.status = 1 AND n.type = 'forum' AND l.last_comment_uid = cu.uid AND n.nid = l.nid AND " . node_access_where_sql() . ' ORDER BY l.last_comment_timestamp DESC', $forum->tid, 0, 1));

// add:
++ $topic = db_fetch_object(db_query_range('SELECT DISTINCT(n.nid), l.last_comment_timestamp, CASE WHEN l.last_comment_uid = 1 THEN cu.name ELSE l.last_comment_name END as last_comment_name, l.last_comment_uid FROM {node} n ' . node_access_join_sql() . ", {node_comment_statistics} l, {users} cu, {term_node} r WHERE n.nid = r.nid AND r.tid = %d AND n.status = 1 AND n.type = 'forum' AND l.last_comment_uid = cu.uid AND n.nid = l.nid AND " . node_access_where_sql() . ' ORDER BY l.last_comment_timestamp DESC', $forum->tid, 0, 1));

function _forum_topics_read($term, $uid) { // ~at around line 420:
// remove:
-- $sql = "SELECT DISTINCT(n.nid), f.tid, n.title, n.sticky, u.name, u.uid, n.created AS timestamp, n.comment AS comment_mode, l.last_comment_timestamp, IF(l.last_comment_uid, cu.name, l.last_comment_name) as last_comment_name, l.last_comment_uid, l.comment_count AS num_comments FROM {node} n ". node_access_join_sql() .", {node_comment_statistics} l, {users} cu, {term_node} r, {users} u, {forum} f WHERE n.status = 1 AND l.last_comment_uid = cu.uid AND n.nid = l.nid AND n.nid = r.nid AND r.tid = $check_tid AND n.uid = u.uid AND n.nid = f.nid AND ". node_access_where_sql();
$sql .= tablesort_sql($forum_topic_list_header, 'n.sticky DESC,');

// add:
++ $sql = "SELECT DISTINCT(n.nid), f.tid, n.title, n.sticky, u.name, u.uid, n.created AS timestamp, n.comment AS comment_mode, l.last_comment_timestamp, CASE WHEN l.last_comment_uid = 1 THEN cu.name ELSE l.last_comment_name END as last_comment_name, l.last_comment_uid, l.comment_count AS num_comments FROM {node} n ". node_access_join_sql() .", {node_comment_statistics} l, {users} cu, {term_node} r, {users} u, {forum} f WHERE n.status = 1 AND l.last_comment_uid = cu.uid AND n.nid = l.nid AND n.nid = r.nid AND r.tid = $check_tid AND n.uid = u.uid AND n.nid = f.nid AND ". node_access_where_sql();
$sql .= tablesort_sql($forum_topic_list_header, 'n.sticky DESC,');

CommentFileSizeAuthor
#2 forum_3.patch3.17 KBZed Pobre

Comments

Anonymous’s picture

just wanted to chime in... this patch works perfectly.

thanks,
aaron

Zed Pobre’s picture

StatusFileSize
new3.17 KB

I'll chime in noting that it works for me as well. I've attached the above solution as an actual patch file to 4.5.2.

chx’s picture