modeling-cvs Mailing List for Object-Relational Bridge for python (Page 16)

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

Update of /cvsroot/modeling/ProjectModeling/Modeling/DatabaseAdaptors/SQLiteAdaptorLayer
In directory sc8-pr-cvs1:/tmp/cvs-serv16799/Modeling/DatabaseAdaptors/SQLiteAdaptorLayer

Modified Files:
	SQLiteSQLExpression.py 
Log Message:
Fixed bug #785913: "LIKE qualifier w/ raw '%' and '_' characters does not
work w/ SQLite"
Fixed bug #786217: 'Qualifier LIKE can be case-insensitive' (bug was
observed for MySQL and SQLite)


Index: SQLiteSQLExpression.py
===================================================================
RCS file: /cvsroot/modeling/ProjectModeling/Modeling/DatabaseAdaptors/SQLiteAdaptorLayer/SQLiteSQLExpression.py,v
retrieving revision 1.1
retrieving revision 1.2
diff -C2 -d -r1.1 -r1.2
*** SQLiteSQLExpression.py	11 Jun 2003 11:10:17 -0000	1.1
--- SQLiteSQLExpression.py	10 Aug 2003 14:43:16 -0000	1.2
***************
*** 49,52 ****
--- 49,58 ----
  import string
  
+ import re
+ sqlite_esc_star_mark_replct='[*]'
+ sqlite_escaped_star=re.compile(r'\\\*')
+ sqlite_esc_question_mark_replct='[?]'
+ sqlite_escaped_question_mark=re.compile(r'\\\?')
+ 
  class SQLiteSQLExpression(SQLExpression):
    """
***************
*** 127,128 ****
--- 133,260 ----
  
      return (str, aliases)
+ 
+ 
+   def globPatternForShellPattern(self, pattern):
+     """
+     Transforms a shell pattern of a like/ilike qualifier to a shell pattern
+     accepted by SQLite's GLOB statement.
+ 
+     There's little to be done, except that escaped characters '\*' and '\?'
+     become, respectively, '[*]' and '[?]'
+     """
+     pattern=sqlite_escaped_question_mark.sub(sqlite_esc_question_mark_replct,
+                                              pattern)
+     pattern=sqlite_escaped_star.sub(sqlite_esc_star_mark_replct, pattern)
+     return pattern
+ 
+   def sqlStringForCaseInsensitiveLike(self, keyString, valueString):
+     """
+     Overrides the default implementation and returns the SQL string, to be
+     inserted in a WHERE clause, for case-insensitive comparison between a
+     lvalue and a rvalue. Note that sqlite LIKE sql operator is
+     case-insensitive.
+ 
+     Returns the following string::
+ 
+       <keyString> LIKE <valueString>
+ 
+     Parameters:
+ 
+       keyString -- the left value
+ 
+       valueString -- the right value
+ 
+     See also: sqlStringForKeyValueQualifier(),
+               sqlStringForKeyComparisonQualifier()
+     """
+     return "%s LIKE %s"% ( keyString, valueString )
+ 
+   def sqlStringForKeyValueQualifier(self, aQualifier):
+     """
+     Overrides the default behaviour to handle 'like' and 'ilike' correctly
+     wrt the way sqlite works.
+ 
+     Returns:
+ 
+       - the inherited SQLExpression.sqlStringForKeyValueQualifier() if
+         aQualifier.operator() is not like or ilike,
+ 
+       - if aQualifier.operator() is QualifierOperatorLike:
+ 
+             <key> GLOB <globPatternForShellPattern(value)>
+ 
+         for example, the qualifier 'title like "*abc*"' is turned into:
+ 
+             title GLOB "*abc*"
+ 
+       - if it is QualifierOperatorCaseInsensitiveLike, returns
+         SQLExpression.sqlStringForKeyValueQualifier() with arguments
+         keyString=<key> and
+         valueString=self.sqlPatternFromShellPattern(<value>)
+ 
+         For example, the qualifier 'title like "*abc*"' is turned into:
+ 
+             title LIKE "%abc%"
+ 
+     Note: the LIKE operator is case-insensitive in sqlite, while GLOB is
+           case-sensitive
+ 
+     See also: SQLExpression.sqlStringForKeyValueQualifier()
+               globPatternForShellPattern()
+     """
+     from Modeling.Qualifier import \
+          QualifierOperatorCaseInsensitiveLike as op_ilike, \
+          QualifierOperatorLike as op_like
+ 
+     qualifier_operator=aQualifier.operator()
+     if qualifier_operator not in (op_like, op_ilike):
+       return SQLExpression.sqlStringForKeyValueQualifier(self, aQualifier)
+ 
+     # Now we handle like or ilike, exclusively
+     key=aQualifier.key()
+     value=aQualifier.value()
+     
+     # do not transform the shell pattern to a sql pattern for LIKE, since
+     # we'll use the sql statement 'GLOB' for like, and 'GLOB' uses a shell
+     # pattern
+     if aQualifier.operator() is op_ilike:
+       value=self.sqlPatternFromShellPattern(value)
+     else: # like
+       operatorStr=self.sqlStringForSelector(aQualifier.operator(), value)
+       value=self.globPatternForShellPattern(value)
+ 
+ 
+     keyString=self.sqlStringForAttributeNamed(key)
+     valueString=self.sqlStringForValue(value, key)
+ 
+ 
+     if qualifier_operator is op_like:
+       return keyString+' '+operatorStr+' '+valueString
+     else:
+       return self.sqlStringForCaseInsensitiveLike(keyString, valueString)
+ 
+   def sqlStringForSelector(self, selector, value):
+     """
+     Overrides the default behaviour to ensure that Qualifier operator
+     'LIKE' matches case-sensitively.
+ 
+     Note: this is because the SQL LIKE operator is case-insensitive by default
+     in  mysql.
+ 
+     Returns:
+ 
+       - if selector is Modeling.Qualifier.QualifierOperatorLike,
+         returns "GLOB"
+ 
+       - otherwise returns SQLExpression.sqlStringForSelector()
+ 
+     See also: SQLExpression.sqlStringForSelector()
+               sqlStringForKeyValueQualifier()
+               globPatternForShellPattern()
+     """
+     from Modeling.Qualifier import QualifierOperatorLike
+     selectorIs=lambda s, selector=selector: s==selector
+     if selectorIs(QualifierOperatorLike):
+       return "GLOB"
+     else:
+       return SQLExpression.sqlStringForSelector(self, selector, value)

2003	Jan	Feb	Mar (54)	Apr (29)	May (94)	Jun (47)	Jul (156)	Aug (132)	Sep (40)	Oct (6)	Nov (18)	Dec (24)
2004	Jan (18)	Feb (59)	Mar (7)	Apr	May (8)	Jun (2)	Jul (12)	Aug (15)	Sep (12)	Oct (6)	Nov (25)	Dec (1)
2005	Jan	Feb	Mar	Apr	May (1)	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2006	Jan	Feb (27)	Mar	Apr (16)	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2009	Jan	Feb	Mar	Apr	May	Jun	Jul (1)	Aug	Sep	Oct	Nov	Dec

modeling-cvs Mailing List for Object-Relational Bridge for python (Page 16)

modeling-cvs — Receives the syncmail-generated messages (CVS checkins messages)