[SQL-CVS] r2583 - in SQLObject/trunk/sqlobject: . tests

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

Author: phd
Date: 2007-04-24 05:30:44 -0600 (Tue, 24 Apr 2007)
New Revision: 2583

Modified:
   SQLObject/trunk/sqlobject/sqlbuilder.py
   SQLObject/trunk/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/trunk/sqlobject/sqlbuilder.py
===================================================================
--- SQLObject/trunk/sqlobject/sqlbuilder.py	2007-04-24 11:30:14 UTC (rev 2582)
+++ SQLObject/trunk/sqlobject/sqlbuilder.py	2007-04-24 11:30:44 UTC (rev 2583)
@@ -669,14 +669,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)
@@ -685,6 +685,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
 
@@ -702,13 +704,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/trunk/sqlobject/tests/test_select.py
===================================================================
--- SQLObject/trunk/sqlobject/tests/test_select.py	2007-04-24 11:30:14 UTC (rev 2582)
+++ SQLObject/trunk/sqlobject/tests/test_select.py	2007-04-24 11:30:44 UTC (rev 2583)
@@ -1,5 +1,6 @@
 from __future__ import generators # for enumerate
 from sqlobject import *
+from sqlobject.sqlbuilder import func
 from sqlobject.main import SQLObjectIntegrityError
 from dbtest import *
 from dbtest import setSQLiteConnectionFactory
@@ -73,6 +74,19 @@
         pass
     assert count == len(names)
 
+def test_05_select_limit():
+    setupIter()
+    assert len(list(IterTest.select(limit=2))) == 2
+    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_select_getOne():
     setupClass(IterTest)
     a = IterTest(name='a')
@@ -85,12 +99,6 @@
     raises(SQLObjectIntegrityError, 'IterTest.selectBy(name="b").getOne()')
     raises(SQLObjectIntegrityError, 'IterTest.selectBy(name="b").getOne(None)')
 
-def test_05_select_limit():
-    setupIter()
-    assert len(list(IterTest.select(limit=2))) == 2
-    raises(AssertionError, IterTest.select(limit=2).distinct)
-    raises(AssertionError, IterTest.select(limit=2).clone, start=1)
-
 def test_selectBy():
     setupClass(IterTest)
     a = IterTest(name='a')