I have a encountered a problem with an update from v1.97 to
v2.4.6-stable+timestamp.2013.04.06.17.37.38. Only found out I had these
problems after pushing to production server which uses Python 2.6, while my
local test set ups all use Python 2.7. In both cases SQLite DBs are used.
Queries fail whenever web2py's contains() is used:
web2py query:
qiosqNr = 'blaat'
db(db.node.qiosqNumber.contains(qiosqNr)).select()
Web2py Error:
OperationalError: no such function: REPLACE
SQLquery:
SELECT node.id, node.qiosqNumber, node.mac1, node.mac2, node.is_active,
node.created_on, node.created_by, node.modified_on, node.modified_by FROM
node WHERE (node.qiosqNumber LIKE (('%' || (REPLACE('blaat','%','%%')) ||
'%')));
Compaired to web2py's like() instead of contains() -> manual states
.like('%blaat%') it is equal to .contains('blaat'):
web2py query:
qiosqNr = 'blaat'
db(db.node.qiosqNumber.like('%'+qiosqNr+'%')).select()
SQLquery:
SELECT node.id, node.qiosqNumber, node.mac1, node.mac2, node.is_active,
node.created_on, node.created_by, node.modified_on, node.modified_by FROM
node WHERE (node.qiosqNumber LIKE '%blaat%');
SQLquery shows the difference, even if results are the same. Yet where
contains() throws an error on the production server, like() doesn't.
gluon/dal.py (v2.4.6):
def CONTAINS(self, first, second, case_sensitive=False):
# silently ignore, only case sensitive
# There is a technical difference, but mongodb doesn't support
# that, but the result will be the same
return {self.expand(first) : ('/%s/' % \
self.expand(second, 'string'))}
gluon/dal.py (v1.9.7):
def CONTAINS(self, first, second):
if first.type in ('string', 'text'):
key = '%'+str(second).replace('%','%%')+'%'
elif first.type.startswith('list:'):
key = '%|'+str(second).replace('|','||').replace('%','%%')+'|%'
return '(%s LIKE %s)' % (self.expand(first),self.
expand(key,'string'))
gluon/dal.py (trunk):
def CONTAINS(self,first,second,case_sensitive=False):
if first.type in ('string','text', 'json'):
second = Expression(None,self.CONCAT('%',Expression(
None,self.REPLACE(second,('%','%%'))),'%'))
elif first.type.startswith('list:'):
second =
Expression(None,self.CONCAT('%|',Expression(None,self.REPLACE(
Expression(None,self.REPLACE(second,('%','%%'))),('|','||'))),'|%'))
op = case_sensitive and self.LIKE or self.ILIKE
return op(first,second)
What would be the best option for me to fix this problem asap? Should I
roll CONTAINS() in gluon/dal.py back to v1.97 spec? Or perhaps get it from
trunk?
--
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
