[kronolith] calendar not displaying events - sql syntax error

[Eric Rostetter]

Quoting Brian Bonnough <brian.bonnough at ce.gatech.edu>:

> The following error message occurs in the log file associated with
> kronolith, mnemo, and nag:
> 
> Jun 20 10:34:54 HORDE [error] [kronolith] DB Error: syntax error: SELECT
> DISTINCT a.category_id, c.category_name FROM horde_category_attributes a
> LEFT JOIN horde_categories c ON a.category_id = c.category_id WHERE
> c.group_uid = 'horde.shares.kronolith' AND ((attribute_name = 'owner' AND
> attribute_value = 'bb198') OR (attribute_name = 'perm_users' AND
> attribute_key = 'bb198' AND CASE WHEN CAST(attribute_value AS VARCHAR)
> SIMILAR TO '(-[0-9]+|[0-9]+)' THEN (CAST(attribute_value AS INTEGER) & 8)
> <> 0 ELSE FALSE END) OR (attribute_name = 'perm_default' AND CASE WHEN
> CAST(attribute_value AS VARCHAR) SIMILAR TO '(-[0-9]+|[0-9]+)' THEN
> (CAST(attribute_value AS INTEGER) & 8) <> 0 ELSE FALSE END))
> [nativecode=ERROR:  parser: parse error at or near "similar"
> 
> Any help tracking the cause of this problem down is greatly appreciated.

Yeah, I had this too.  The SQL should work, but doesn't for some reason.
It doesn't like the "SIMILAR TO" but it should.  If you edit 
horde/lib/SQL.php and change the "SIMILAR TO" to "~" it will work.  Patch is:

Index: SQL.php
===================================================================
RCS file: /usr/repository/horde/lib/SQL.php,v
retrieving revision 1.15
diff -u -r1.15 SQL.php
--- SQL.php     19 Jun 2003 18:47:44 -0000      1.15
+++ SQL.php     21 Jun 2003 03:05:22 -0000
@@ -37,7 +37,7 @@
             switch ($dbh->phptype) {
             case 'pgsql':
                 // This should conform completely to SQL99.
-                return sprintf('CASE WHEN CAST(%s AS VARCHAR) SIMILAR TO
\'(-[0-9]+|[0-9]+)\' THEN (CAST(%s AS INTEGER) %s %d) <> 0 ELSE FALSE END',
$lhs, $lhs, $op, (int)$rhs);
+                return sprintf('CASE WHEN CAST(%s AS VARCHAR) ~
\'(-[0-9]+|[0-9]+)\' THEN (CAST(%s AS INTEGER) %s %d) <> 0 ELSE FALSE END',
$lhs, $lhs, $op, (int)$rhs);
  
             default:
                 return sprintf('%s %s %d', $lhs, $op, (int)$rhs);

-- 
Eric Rostetter
The Department of Physics
The University of Texas at Austin

Why get even? Get odd!