Postgresql compatibility

Thanks for the testing on PostgreSQL and the patch! I will run this through its paces and get it into SBP as soon as I can.

Setting to Needs Review so the testing bot can test it...

I took a look at your suggested patch.

There is a problem with the chunk here:

-  $join .= ' INNER JOIN ({node} sbpa_n ' . $stuff[0] . ') ON sbpa_n.nid = saf.nid AND sbpa_n.vid = saf.vid ';
+  $join .= ' INNER JOIN {node} sbpa_n ' . $stuff[0] . ' ON sbpa_n.nid = saf.nid AND sbpa_n.vid = saf.vid ';

In the general case (if you are using node access modules) those parentheses are actually needed, because that will be a complex join. So we can't actually leave out the parentheses.

There's a similar issue farther down in the patch.

I am not familiar enough with PostgreSQL to know what to do about this... any thoughts?

Oh I see, you are saying that if $stuff is empty, to leave out the parens... Do you think PostgreSQL will be happy with that?

OK, I'll see what I can do. Thanks for the report and information.

And yes, you have to be using a custom or contributed access control module in order to see something happening in there other than an empty $stuff.

Just an update: I've set up a PostgreSQL testing box, and I am seeing multiple problems running the Search by Page tests in Postgres, with both D6 and D7 versions of Search by Page.

I'm working on fixing all the errors, and will report back...

I have just committed fixes to Search by Page for PostgreSQL compatibility to the development branch in 6.x. You can get them from CVS now, or wait several hours and download from the project page (they dont' update the zip files there immediately - just check the time stamp and make sure it's after this comment was submitted, i.e. Aug 17 or later).

I will probably make a 6.x-1.12 release in the next week or two, but feel free to test.

Next task: get 7.x working with PostgreSQL also.

Update: 7.x is not working with PostgreSQL yet. I believe the tests are failing due to one or two core Drupal issues. More investigation coming...

Meanwhile, 7.x does work fine with SQLite and MySQL (all the tests pass).

Oh, one other note on Postgres in 6.x - if you are running Drupal 6.19, you will also need the fix here http://drupal.org/node/488166#comment-3339748 to do much with the Search module (i.e. the core Search module is broken for Postgres).

Update on Drupal 7:
- I've committed some changes to the Drupal 7 development version for PostgreSQL compatibility.
- In the process of testing on PostgreSQL, I uncovered two core Drupal issues. Until they are fixed, the Search by Page module family will not work quite right in Drupal 7, at least in PostgreSQL, although one of the issues probably affects MySQL and SQLite as well (but only for the case of using Search by Page Attachments along with a node permissions/access module such as Content Access or Workflow Access).

The Drupal core issues that need to be fixed for Search by Page to work correctly in PostgreSQL:
#886970: DB API putting wrong db placeholders in complex queries
#886752: SQL error on PostgreSQL in comment.module

Meanwhile, the Search by Page module itself is now PostgreSQL (D6 and D7) and SQLite (D7) compatible, as far as I can tell (it at least passes all the automated tests for all 5 combinations of version and database, at least with the latest development versions of Drupal and Search by Page). So I will go ahead and mark this issue fixed.

Oh, one more note: If you are running Drupal 6.19, you may be affected by this issue in PostgreSQL:
#488166: Search relevance calculation fails if last_comment_timestamp is NULL