Usage with PostgreSQL

The first errors are probably not at all related to PostgreSQL as the erros seems to be pretty same as with MySQL - see http://drupal.org/node/191313

I marked http://drupal.org/node/206748 as a duplicate of this. It has ideas on how to fix it for Postgresql. szimszon - can you try it out? Thanks.

PostgreSQL problems are related to use the of ifnull() which PostgreSQL doesn't support. It does have a 'coalesce' function instead, which appears to do the same 'sort of thing'.

The closest I seem to be able to get the SQL is something like :

$result = db_query("SELECT c.cid,u.init,c.uid,c.name,c.nid,c.notify,c.status,
CAST(if(length(c.mail)<1, coalesce(u.mail,u.init),c.mail) AS text) as mymail,
c.uid,c.name AS cid, md5(c.mail || CAST(coalesce(u.mail, u.init) AS TEXT) || c.uid || c.name || c.nid ) as mymd5
FROM comments c
LEFT OUTER JOIN users u on u.uid=c.uid
WHERE nid = %d AND c.notify = 1 AND c.status = 0", $nid);

But that still doesn't work....

Note the requirement to cast strings.

I used || instead of concat - I believe the result is the same, but || is cross db, while concat isn't. Annoyingly PgSQL doesn't seem too keen on allowing me to add a "AND mymail like '%@%.%'" on the end and comes back with an 'unknown column' sort of error.

(that's for around line 368 on comment_notify.module).

Perhaps I should find the CVS repos for this module.

Thanks for your help.

From the project homepage you can see the link to Browse the CVS repository.

Hi,
See attached; this _appears_ to make it work with PgSQL etc.

1) I fixed the PGSQL query, in a sort of way. I don't think it does the same as the MySQL query, but PgSQL is a bit more strict / fussy so I couldn't do some things :-/

2) the drupal_mail() call seemed to be broken; in that I wasn't receiving mail from it. This was fixed by adding a $header['To'] = blah@blah... I'm wondering if some other MTAs patched this behaviour up silently, and my MTA (Postfix) doesn't?

3) It won't send an email to the same email address twice... as this seems pointless/annoying.

4) The original code checked to make sure both commenters names were different before sending an email. I don't think this is that good a check, and instead I've changed it to do a simple comparison on both email addresses.

Bugs remaining:

1) If a signed in member of a site posts a comment, no emails are sent as notification(s) to $users. This is because the publish hook(s) (or whatever) aren't being fired. This is annoying - as I don't have moderation turned on for registered users.

Some of the lines changes are purely whitespace changes.... blame me/vim; sorry.

Created with :

diff -uw comment_notify.module comment_notify.module.new

This is great!

...but it would be much easier to review and commit this if it were provided as separate patches. I know that makes it harder for you, but as it is this whole patch is unlikely to get in. There are separate issues for some of these (mail not getting sent is one for sure).

fix for mail ....

patch to stop duplicate emails - incase someone has commented twice in a thread.

patch to fix postgresql issues - queries should now work....

(Sorry I'm too lazy to post them as seperate issues/tickets/whatever)

The postgresql piece has been split out to http://drupal.org/node/211684 right?

In case anyone else is interested, please note that I'm working on this in #232564: refactor data structure so core tables are not altered and move towards PostgreSQL compliance and would love to have some reviewers help test it.

Thanks.