Hello:
I was getting incorrect SQL from statements like:
db(db.paper.authors.contains(person.id)).select()
where
db.define_table('paper',
Field('title'),
Field('authors', 'list:reference person'),
)
so I rewrote dal.MySQLAdapter.CONTAINS from:
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'))
into:
def CONTAINS(self, first, second):
if first.type in ('string', 'text'):
key = '%'+str(second).replace('%','%%')+'%'
elif first.type.startswith('list:reference'):
return 'INSTR(%s, %s)'%(first, str(second))
elif first.type.startswith('list:'):
key = '%|'+str(second).replace('|','||').replace('%','%%')+'|%'
return '(%s LIKE %s)' %
(self.expand(first),self.expand(key,'string'))
now the above query works, but that's in MySQL, not SQLite, which
apparently does not have a INSTR operation, despite the fact that it is
advertised!
https://www.sqlite.org/lang_corefunc.html
Can anybody think of a solution that works in more databases
--
