postgres fix

I'm having the same problem too.

Same here. I'd suggest we insert something like:

switch ($GLOBALS['db_type']) {
case 'mysql':
case 'mysqli':
$regex_query_operator .= 'rlike "%s$" ';
break;
case 'pgsql':
$regex_query_operator .= '~* \'%s$\' ';
break;
}

and replace the operators by $regex_query_operator then.

If that is ok I would try to do the changes and to make a diff for 6.x-1.0-beta9 - would that be integrated into the package?

Sure that would be great. Post it here when ready and I'll roll it into the code.

subscribing :-)

Hi,

just a quick report on my work... I tried to apply the changes to 6.x-1.0-rc1, what I did was to update only biblio.page.inc and PARSECREATORS.php so far. Those changes worked with the prior version, there was only one bug left:

In biblio.page.inc I changed (In every statement I changed I added a "//R" just to mark the changes more explicit. Please delete them once we see it's working).

(1) Create a function that returns the right regexp operator:

/** //R
 * @return string consisting of the regexp query operator for the current db
 */
function _regex_query_operator() { 
  switch ($GLOBALS['db_type']) {
    case 'mysql':
    case 'mysqli':
      return ' RLIKE ';
    case 'pgsql':
      return " SIMILAR TO ";
    default:
      return " SIMILAR TO ";
  }
}

I guess something can be otimized there, but for the moment it should return the right string, for me at least.

(2) Replace the RLIKEs with " . _regex_query_operator() . "

(3) Postgres likes ' as string delimiters, mysql does not care as far as I know, so I had to change them in some cases

(4) Postgres wants all the ORDER BY clauses to appear in the SELECT parts as well. For this I use the variable "$select_sortby", which is then included in the select statement, e.g.:
$sortby = "ORDER BY bcd.lastname %s ";
$select_sortby = " , bcd.lastname AS order_by_author "; //R

I fixed a few SELECT statement where that happens manually (e.g. in biblio_citekey_view() )

(5) Postgres does not know "MID" and "LEFT", both can be replaced by "SUBSTRING" if I am right, e.g.
WHEN LEFT(n.title,1)="\"" THEN MID(title,2)
should be the same as
WHEN SUBSTRING(n.title,0, 1)='\"' THEN SUBSTRING(n.title,2,1)
- is it?

(6) In PARSECREATORS.php I changed only the line with RLIKE.

What still has to be done is to change biblio.install (lots of errors on update) and to test the rest... I will keep you updated. I would ask someone to maybe test the adapted biblio.page.inc, just to check that I did not break anything for mysql...

Cheers,
Reinhard

I just saw that 1.0-rc2 is the current version. Should I rather try to make a diff for this version to get it in or wait for 1.1-BETA? Either way is right for me, the second one seems safer, although the first one has that advantage to have it done...

I'm pretty new to Drupal (and consequently Biblio as well). I hope this is the right place for this...

My Administer > Report > Recent log entries shows an error ...
in /usr/share/drupal/sites/all/modules/biblio/biblio.pages.inc on line 77.

My analysis would be that PostgreSQL's not liking the ORDER BY ... SUBSTRING(n.title,1,3) ASC in the following (wrapped for sanity):

SELECT DISTINCT n.*, b.*, bt.name as biblio_type_name 
  FROM node n 
  left  join biblio                b   on n.vid=b.vid 
  left  join biblio_types          bt  on b.biblio_type=bt.tid 
  inner join biblio_contributor as bc1 on n.vid = bc1.vid 
  WHERE (bc1.cid = '0') 
   AND  (n.type='biblio')
   AND  (n.status = 1) 
  ORDER BY b.biblio_year DESC, 
           SUBSTRING(n.title,1,3) ASC 
  LIMIT 25 OFFSET 0;

ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

Removing the SUBSTRING(n.title,1,3) ASC did not produce this error. I'm not enough of an SQL wizard to offer an alternative, other than perhaps:

SELECT DISTINCT n.*, b.*, bt.name as biblio_type_name, SUBSTRING(n.title,1,3) as qwert
  ...
  ORDER BY ..., qwert ASC...

replacing qwert with something more intelligent.

"Is it beautiful? No. But that's what I like about it." to paraphrase Captain Amazing from "Mystery Men". ;-)

The attached (biblio.pages.inc) gets past the various SUBSTRING and MID errors produced when dealing w/ PostgreSQL. As to it functioning correctly, well, that's anyone's guess. And it's certainly not beautiful.

Sorry. You can delete this comment, and the one preceeding. I see now that I've reinvented the wheel solved earlier in this thread.

Actually, I can't delete it, only God and the Drupal.org admin have that power :-)

This bug is still existent in the latest biblio version for Drupal. When trying to access biblio/author/lastname when using a postgresql database, this error happens:

PDOException: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "RLIKE" LINE 11: WHERE (bcd.name RLIKE '[[:<:]]steels[[:>:]]') AND(( (na.gid... ^: SELECT COUNT(*) AS expression FROM (SELECT DISTINCT n.nid AS nid, bt.name AS biblio_type_name, b.biblio_year AS biblio_year, b.biblio_date AS biblio_date, biblio_sort_title AS biblio_sort_title, 1 AS expression FROM {node} n LEFT OUTER JOIN {biblio} b ON n.vid=b.vid INNER JOIN {biblio_types} bt ON b.biblio_type=bt.tid INNER JOIN {biblio_contributor} bc ON n.vid = bc.vid INNER JOIN {biblio_contributor_data} bcd ON bcd.cid = bc.cid INNER JOIN {node_access} na ON na.nid = n.nid WHERE (bcd.name RLIKE :db_condition_placeholder_0) AND(( (na.gid = :db_condition_placeholder_1) AND (na.realm = :db_condition_placeholder_2) )OR( (na.gid = :db_condition_placeholder_3) AND (na.realm = :db_condition_placeholder_4) )OR( (na.gid = :db_condition_placeholder_5) AND (na.realm = :db_condition_placeholder_6) ))AND (na.grant_view >= :db_condition_placeholder_7) ) subquery; Array ( [:db_condition_placeholder_0] => [[:<:]]steels[[:>:]] [:db_condition_placeholder_1] => 0 [:db_condition_placeholder_2] => all [:db_condition_placeholder_3] => 0 [:db_condition_placeholder_4] => content_access_author [:db_condition_placeholder_5] => 1 [:db_condition_placeholder_6] => content_access_rid [:db_condition_placeholder_7] => 1 ) in PagerDefault->execute() (line 74 of /var/www/ai.vub.ac.be/drupal/includes/pager.inc).

That's fixed now...

http://drupalcode.org/project/biblio.git/commit/4be1610

Ron.