[SQL-CVS] r3983 - in SQLObject/trunk: docs sqlobject sqlobject/tests

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

Author: phd
Date: 2009-09-20 10:34:07 -0600 (Sun, 20 Sep 2009)
New Revision: 3983

Modified:
   SQLObject/trunk/docs/News.txt
   SQLObject/trunk/sqlobject/sqlbuilder.py
   SQLObject/trunk/sqlobject/tests/test_select.py
Log:
Fixed a bug: q.startswith(), q.contains() and q.endswith() escape (with a
backslash) all special characters (backslashes, underscores and percent signs).


Modified: SQLObject/trunk/docs/News.txt
===================================================================
--- SQLObject/trunk/docs/News.txt	2009-09-20 16:27:23 UTC (rev 3982)
+++ SQLObject/trunk/docs/News.txt	2009-09-20 16:34:07 UTC (rev 3983)
@@ -106,6 +106,10 @@
 
 * Fixed a bug: Sybase tables with identity column fire two identity_inserts.
 
+* Fixed a bug: q.startswith(), q.contains() and q.endswith() escape (with a
+  backslash) all special characters (backslashes, underscores and percent
+  signs).
+
 SQLObject 0.10.6
 ================
 

Modified: SQLObject/trunk/sqlobject/sqlbuilder.py
===================================================================
--- SQLObject/trunk/sqlobject/sqlbuilder.py	2009-09-20 16:27:23 UTC (rev 3982)
+++ SQLObject/trunk/sqlobject/sqlbuilder.py	2009-09-20 16:34:07 UTC (rev 3983)
@@ -840,13 +840,13 @@
         return NOT(_IN(item, list))
 
 def STARTSWITH(expr, pattern):
-    return SQLOp("LIKE", expr, _LikeQuoted(pattern) + '%')
+    return LIKE(expr, _LikeQuoted(pattern) + '%', escape='\\')
 
 def ENDSWITH(expr, pattern):
-    return SQLOp("LIKE", expr, '%' + _LikeQuoted(pattern))
+    return LIKE(expr, '%' + _LikeQuoted(pattern), escape='\\')
 
 def CONTAINSSTRING(expr, pattern):
-    return SQLOp("LIKE", expr, '%' + _LikeQuoted(pattern) + '%')
+    return LIKE(expr, '%' + _LikeQuoted(pattern) + '%', escape='\\')
 
 def ISNULL(expr):
     return SQLOp("IS", expr, None)
@@ -888,7 +888,7 @@
             values = []
             if self.prefix:
                 values.append("'%s'" % self.prefix)
-            s = _quote_percent(sqlrepr(s, db), db)
+            s = _quote_like_special(sqlrepr(s, db), db)
             values.append(s)
             if self.postfix:
                 values.append("'%s'" % self.postfix)
@@ -897,16 +897,17 @@
             else:
                 return " || ".join(values)
         elif isinstance(s, basestring):
-            s = _quote_percent(sqlrepr(s, db)[1:-1], db)
+            s = _quote_like_special(sqlrepr(s, db)[1:-1], db)
             return "'%s%s%s'" % (self.prefix, s, self.postfix)
         else:
            raise TypeError, "expected str, unicode or SQLExpression, got %s" % type(s)
 
-def _quote_percent(s, db):
-    if db in ('postgres', 'mysql'):
-        s = s.replace('%', '\\%')
+def _quote_like_special(s, db):
+    if db == 'postgres':
+        escape = r'\\'
     else:
-        s = s.replace('%', '%%')
+        escape = '\\'
+    s = s.replace('\\', r'\\').replace('%', escape+'%').replace('_', escape+'_')
     return s
 
 ########################################
@@ -1135,11 +1136,17 @@
 class LIKE(SQLExpression):
     op = "LIKE"
 
-    def __init__(self, expr, string):
+    def __init__(self, expr, string, escape=None):
         self.expr = expr
         self.string = string
+        self.escape = escape
     def __sqlrepr__(self, db):
-        return "(%s %s (%s))" % (sqlrepr(self.expr, db), self.op, sqlrepr(self.string, db))
+        escape = self.escape
+        like = "%s %s (%s)" % (sqlrepr(self.expr, db), self.op, sqlrepr(self.string, db))
+        if escape is None:
+            return "(%s)" % like
+        else:
+            return "(%s ESCAPE %s)" % (like, sqlrepr(escape, db))
     def components(self):
         return [self.expr, self.string]
     def execute(self, executor):
@@ -1157,15 +1164,16 @@
 class RLIKE(LIKE):
     op = "RLIKE"
 
+    op_db = {
+        'firebird': 'RLIKE',
+        'maxdb': 'RLIKE',
+        'mysql': 'RLIKE',
+        'postgres': '~',
+        'sqlite': 'REGEXP'
+    }
+
     def _get_op(self, db):
-        if db in ('mysql', 'maxdb', 'firebird'):
-            return "RLIKE"
-        elif db == 'sqlite':
-            return "REGEXP"
-        elif db == 'postgres':
-            return "~"
-        else:
-            return "LIKE"
+        return self.op_db.get(db, 'LIKE')
     def __sqlrepr__(self, db):
         return "(%s %s (%s))" % (
             sqlrepr(self.expr, db), self._get_op(db), sqlrepr(self.string, db)

Modified: SQLObject/trunk/sqlobject/tests/test_select.py
===================================================================
--- SQLObject/trunk/sqlobject/tests/test_select.py	2009-09-20 16:27:23 UTC (rev 3982)
+++ SQLObject/trunk/sqlobject/tests/test_select.py	2009-09-20 16:34:07 UTC (rev 3983)
@@ -69,14 +69,23 @@
     assert len(list(IterTest.select(limit=2))) == 2
     raises(AssertionError, IterTest.select(limit=2).count)
 
-def test_06_like():
+def test_06_contains():
     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
     assert len(list(IterTest.select(IterTest.q.name.contains("a'b")))) == 0
+    assert len(list(IterTest.select(IterTest.q.name.endswith('a')))) == 1
 
+def test_07_contains_special():
+    setupClass(IterTest)
+    a = IterTest(name='\\test')
+    b = IterTest(name='100%')
+    c = IterTest(name='test_')
+    assert list(IterTest.select(IterTest.q.name.startswith('\\'))) == [a]
+    assert list(IterTest.select(IterTest.q.name.contains('%'))) == [b]
+    assert list(IterTest.select(IterTest.q.name.endswith('_'))) == [c]
+
 def test_select_getOne():
     setupClass(IterTest)
     a = IterTest(name='a')