The attached patch fixes the SQL query used with PostgreSQL. Namely :

- PostgreSQL does not have a ifnull, but it does have coalesce
- I found the concat function (which had been created for PgSQL) didn't work, so I used || instead.

The attached SQL works fine with PostgreSQL v8.2

CommentFileSizeAuthor
pgsql-diff.patch2.84 KBDavid Goodwin

Comments

greggles’s picture

greggles
he/him
Primary language English
Location Denver, Colorado, USA
commented
Status: Needs review » Needs work

Generally speaking the standard in Drupal is to try to avoid checking the $_GLOBALS['dbtype'] unless you are in a .install file. I think we should be able to do that here as well.

It seems like a lot of the special sql syntax is related to building the md5. Perhaps we could do that in php instead of in sql?

David Goodwin’s picture

David Goodwin commented

I'm sure it could all be done in PHP; however there would be a performance hit associated with doing it outside of the database.

Surely other modules encounter the same issue(s) (i.e. supporting more than one database backend means you need to have some differentiation between SQL queries).

greggles’s picture

greggles
he/him
Primary language English
Location Denver, Colorado, USA
commented

Some other modules do it this way, but none that are widely respected. Core never does.

Why do you say it would be faster? I'm not sure I get that PHP necessarily is slower than the DB.

greggles’s picture

greggles
he/him
Primary language English
Location Denver, Colorado, USA
commented
Status: Needs work » Closed (duplicate)

Hi David - I've now refactored the module's data storage quite a bit which I think will provide for PostgreSQL compliance. Can you help me test it in #232564: refactor data structure so core tables are not altered and move towards PostgreSQL compliance?