[Modeling-cvs] ProjectModeling/Modeling/DatabaseAdaptors/SQLiteAdaptorLayer SQLiteSQLExpression.py,1.1,1.2

[<sbi...@us...>]

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)