[dev] Category_sql & PostgreSQL

[Jason M. Felice]

Here is a patch that solves the issue with the Category sql
driver <-> PostgreSQL.  It introduces a Horde_SQL::buildTest() method
which can be used to build various comparisons in a database-independent
manner.

I'm not sure how common a boolean '&' operator is for different SQL platforms,
and I'm not sure what Chuck is using, but I coded the PostgreSQL method to
be SQL99-compliant.  Perhaps the case and default should be reversed?

-Jay 'Eraserhead' Felice

Index: SQL.php
===================================================================
RCS file: /repository/horde/lib/SQL.php,v
retrieving revision 1.13
diff -u -u -r1.13 SQL.php
--- SQL.php	28 May 2003 21:22:36 -0000	1.13
+++ SQL.php	30 May 2003 13:47:30 -0000
@@ -204,4 +204,38 @@
         return $dbh->execute($stmt, $values);
     }
 
+    /**
+     * Return a boolean expression using the specified operator.  Uses
+     * database-specific casting, if necessary.
+     *
+     * @access public
+     *
+     * @param         object $db         The database object.
+     * @param         string $lhs        The column or expression to test.
+     * @param         string $op         The operator.
+     * @param         string $rhs        The comparison value.
+     *
+     * @returns string  The SQL test expression.
+     */
+    function buildTest (&$db, $lhs, $op, $rhs)
+    {
+        switch ($op) {
+        case '&':
+            switch ($db->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) & %d) <> 0 ELSE FALSE END", $lhs, $lhs, $rhs);
+
+            default:
+                return sprintf('%s & %d', $lhs, $rhs);
+            }
+
+        case 'IN':
+            return sprintf('%s IN %s', $lhs, $rhs);
+
+        default:
+            return sprintf('%s %s %s', $lhs, $op, $db->quote($rhs));
+        }
+    }
+
 }
Index: Category/sql.php
===================================================================
RCS file: /repository/horde/lib/Category/sql.php,v
retrieving revision 1.71
diff -u -u -r1.71 sql.php
--- Category/sql.php	29 May 2003 17:58:50 -0000	1.71
+++ Category/sql.php	30 May 2003 13:47:31 -0000
@@ -623,47 +623,34 @@
     function _buildAttributeQuery($glue, $criteria)
     {
         $clause = '';
-        foreach ($criteria as $key => $vals) {
-            if (!empty($vals['OR']) || !empty($vals['AND'])) {
-                if (!empty($clause)) {
-                    $clause .= ' ' . $glue . ' ';
-                }
-                $clause .= '(' . $this->_buildAttributeQuery($glue, $vals) . ')';
-            } else {
-                if (isset($vals['field'])) {
-                    if (!empty($clause)) {
-                        $clause .= ' ' . $glue . ' ';
-                    }
-                    $value = $this->_quoteByOp($vals['op'], $vals['test']);
-                    $clause .= 'attribute_' . $vals['field'] . ' ' . $vals['op'] . ' ' . $value;
-                } else {
-                    foreach ($vals as $test) {
-                        if (!empty($clause)) {
-                            $clause .= ' ' . $key . ' ';
-                        }
 
-                        $value = $this->_quoteByOp($test['op'], $test['test']);
-                        $clause .= 'attribute_' . $test['field'] . ' ' . $test['op'] . ' ' . $value;
-                    }
+        require_once HORDE_BASE . '/lib/SQL.php';
+
+        foreach ($criteria as $key => $vals) {
+            if (!empty($vals['OR']))
+                $clauses[] = '(' . $this->_buildAttributeQuery('OR', $vals) . ')';
+            elseif (!empty($vals['AND']))
+                $clauses[] = '(' . $this->_buildAttributeQuery('AND', $vals) . ')';
+            else {
+                if (isset($vals['field']))
+                    $clauses[] = Horde_SQL::buildTest(
+                        $this->_db,
+                        'attribute_'.$vals['field'],
+                        $vals['op'],
+                        $vals['test']
+                        );
+                else {
+                    foreach ($vals as $test)
+                        $clauses[] = Horde_SQL::buildTest(
+                            $this->_db,
+                            'attribute_'.$test['field'],
+                            $test['op'],
+                            $test['test']
+                            );
                 }
             }
         }
-
-        return $clause;
-    }
-
-    function _quoteByOp($op, $value)
-    {
-        switch($op) {
-        case '&':
-            return (int)$value;
-
-        case 'IN':
-            return $value;
-
-        default:
-            return $this->_db->quote($value);
-        }
+        return join(' '.$glue.' ',$clauses);
     }
 
     /**