[SQL-CVS] r2580 - in SQLObject/branches/0.7: docs sqlobject sqlobject/tests

[<sub...@co...>]

Author: phd
Date: 2007-04-24 05:29:22 -0600 (Tue, 24 Apr 2007)
New Revision: 2580

Modified:
   SQLObject/branches/0.7/docs/News.txt
   SQLObject/branches/0.7/sqlobject/sqlbuilder.py
   SQLObject/branches/0.7/sqlobject/tests/test_select.py
Log:
Fixed sqlbuilder - .startswith(), .endswith() and .contains() assumed
their parameter must be a string; now you can pass an SQLExpression.


Modified: SQLObject/branches/0.7/docs/News.txt
===================================================================
--- SQLObject/branches/0.7/docs/News.txt	2007-04-20 17:47:57 UTC (rev 2579)
+++ SQLObject/branches/0.7/docs/News.txt	2007-04-24 11:29:22 UTC (rev 2580)
@@ -19,6 +19,10 @@
 
 * Fixed a bug with absent comma in JOINs.
 
+* Fixed sqlbuilder - .startswith(), .endswith() and .contains() assumed
+  their parameter must be a string; now you can pass an SQLExpression:
+  Table.q.name.contains(func.upper('a')), for example.
+
 SQLObject 0.7.5
 ===============
 

Modified: SQLObject/branches/0.7/sqlobject/sqlbuilder.py
===================================================================
--- SQLObject/branches/0.7/sqlobject/sqlbuilder.py	2007-04-20 17:47:57 UTC (rev 2579)
+++ SQLObject/branches/0.7/sqlobject/sqlbuilder.py	2007-04-24 11:29:22 UTC (rev 2580)
@@ -660,14 +660,14 @@
     else:
         return NOT(_IN(item, list))
 
-def STARTSWITH(expr, string):
-    return SQLOp("LIKE", expr, _LikeQuoted(string) + '%')
+def STARTSWITH(expr, pattern):
+    return SQLOp("LIKE", expr, _LikeQuoted(pattern) + '%')
 
-def ENDSWITH(expr, string):
-    return SQLOp("LIKE", expr, '%' + _LikeQuoted(string))
+def ENDSWITH(expr, pattern):
+    return SQLOp("LIKE", expr, '%' + _LikeQuoted(pattern))
 
-def CONTAINSSTRING(expr, string):
-    return SQLOp("LIKE", expr, '%' + _LikeQuoted(string) + '%')
+def CONTAINSSTRING(expr, pattern):
+    return SQLOp("LIKE", expr, '%' + _LikeQuoted(pattern) + '%')
 
 def ISNULL(expr):
     return SQLOp("IS", expr, None)
@@ -676,6 +676,8 @@
     return SQLOp("IS NOT", expr, None)
 
 class _LikeQuoted:
+    # It assumes prefix and postfix are strings; usually just a percent sign.
+
     # @@: I'm not sure what the quoting rules really are for all the
     # databases
 
@@ -693,13 +695,30 @@
         return self
 
     def __sqlrepr__(self, db):
-        s = sqlrepr(self.expr, db)[1:-1] # remove quotes
-        if db in ('postgres', 'mysql'):
-            s = s.replace('%', '\\%')
-        else:
-            s = s.replace('%', '%%')
-        return "'%s%s%s'" % (self.prefix, s, self.postfix)
+        s = self.expr
+        if isinstance(s, SQLExpression):
+            values = []
+            if self.prefix:
+                values.append("'%s'" % self.prefix)
+            s = _quote_percent(sqlrepr(s, db), db)
+            values.append(s)
+            if self.postfix:
+                values.append("'%s'" % self.postfix)
+            if db == "mysql":
+                return "CONCAT(%s)" % ", ".join(values)
+            else:
+                return " || ".join(values)
+        else: # assuming s is a string
+            s = _quote_percent(s, db)
+            return "'%s%s%s'" % (self.prefix, s, self.postfix)
 
+def _quote_percent(s, db):
+    if db in ('postgres', 'mysql'):
+        s = s.replace('%', '\\%')
+    else:
+        s = s.replace('%', '%%')
+    return s
+
 ########################################
 ## SQL JOINs
 ########################################

Modified: SQLObject/branches/0.7/sqlobject/tests/test_select.py
===================================================================
--- SQLObject/branches/0.7/sqlobject/tests/test_select.py	2007-04-20 17:47:57 UTC (rev 2579)
+++ SQLObject/branches/0.7/sqlobject/tests/test_select.py	2007-04-24 11:29:22 UTC (rev 2580)
@@ -1,5 +1,6 @@
 from __future__ import generators # for enumerate
 from sqlobject import *
+from sqlobject.sqlbuilder import func
 from dbtest import *
 
 try:
@@ -77,6 +78,13 @@
     raises(AssertionError, IterTest.select(limit=2).distinct)
     raises(AssertionError, IterTest.select(limit=2).clone, start=1)
 
+def test_06_like():
+    setupIter()
+    assert len(list(IterTest.select(IterTest.q.name.startswith('a')))) == 1
+    assert len(list(IterTest.select(IterTest.q.name.endswith('a')))) == 1
+    assert len(list(IterTest.select(IterTest.q.name.contains('a')))) == 1
+    assert len(list(IterTest.select(IterTest.q.name.contains(func.lower('A'))))) == 1
+
 def test_selectBy():
     setupClass(IterTest)
     a = IterTest(name='a')