def on_value_list(self, s, loc, tokens):
"""
Called when the parser hits a value_list token
Search with a list of values is the broadest search and
searches all the mapper and the properties configured with
add_meta()
"""
# debug('values: %s' % tokens)
# debug(' s: %s' % s)
# debug(' loc: %s' % loc)
# debug(' toks: %s' % tokens)
# make searches case-insensitive, in postgres use ilike,
# in other use upper()
like = lambda table, col, val: \
utils.ilike(table.c[col], ('%%%s%%' % val))
for cls, columns in self._properties.iteritems():
q = self._session.query(cls)
cv = [(c,v) for c in columns for v in tokens]
# as of SQLAlchemy>=0.4.2 we convert the value to a unicode
# object if the col is a Unicode or UnicodeText column in order
# to avoid the "Unicode type received non-unicode bind param"
def unicol(col, v):
mapper = class_mapper(cls)
if isinstance(mapper.c[col].type, (Unicode,UnicodeText)):
return unicode(v)
else:
return v
mapper = class_mapper(cls)
q = q.filter(or_(*[like(mapper, c, unicol(c, v)) for c,v in cv]))
self._results.update(q.all())
def __init__(self, t):
logger.debug('IdentExpressionToken::__init__(%s)' % t)
self.op = t[0][1]
def not_implemented_yet(x, y):
# raise an exception
raise NotImplementedError
# cfr: SearchParser.binop
# = == != <> < <= > >= not like contains has ilike icontains ihas is
self.operation = {
'=': lambda x, y: x == y,
'==': lambda x, y: x == y,
'is': lambda x, y: x == y,
'!=': lambda x, y: x != y,
'<>': lambda x, y: x != y,
'not': lambda x, y: x != y,
'<': lambda x, y: x < y,
'<=': lambda x, y: x <= y,
'>': lambda x, y: x > y,
'>=': lambda x, y: x >= y,
'like': lambda x, y: utils.ilike(x, '%s' % y),
'contains': not_implemented_yet,
'has': not_implemented_yet,
'ilike': lambda x, y: utils.ilike(x, '%s' % y),
'icontains': not_implemented_yet,
'ihas': not_implemented_yet,
}[self.op]
self.operands = t[0][0::2] # every second object is an operand
def plant_get_completions(text):
# TODO: only return those plants with propagations
from bauble.plugins.garden.accession import Accession
from bauble.plugins.garden.plant import Plant
query = self.session.query(Plant).join('accession').\
filter(utils.ilike(Accession.code, u'%s%%' % text)).\
filter(Accession.id != self.model.accession.id)
return query
def invoke(self, search_strategy):
logger.debug('DomainExpressionAction:invoke')
try:
if self.domain in search_strategy._shorthand:
self.domain = search_strategy._shorthand[self.domain]
cls, properties = search_strategy._domains[self.domain]
except KeyError:
raise KeyError(_('Unknown search domain: %s') % self.domain)
query = search_strategy._session.query(cls)
## here is the place where to optionally filter out unrepresented
## domain values. each domain class should define its own 'I have
## accessions' filter. see issue #42
result = set()
# select all objects from the domain
if self.values == '*':
result.update(query.all())
return result
mapper = class_mapper(cls)
if self.cond in ('like', 'ilike'):
condition = lambda col: \
lambda val: utils.ilike(mapper.c[col], '%s' % val)
elif self.cond in ('contains', 'icontains', 'has', 'ihas'):
condition = lambda col: \
lambda val: utils.ilike(mapper.c[col], '%%%s%%' % val)
elif self.cond == '=':
condition = lambda col: \
lambda val: mapper.c[col] == utils.utf8(val)
else:
condition = lambda col: \
lambda val: mapper.c[col].op(self.cond)(val)
for col in properties:
ors = or_(*map(condition(col), self.values.express()))
result.update(query.filter(ors).all())
if None in result:
logger.warn('removing None from result set')
result = set(i for i in result if i is not None)
return result
def invoke(self, search_strategy):
logger.debug('DomainExpressionAction:invoke')
try:
if self.domain in search_strategy._shorthand:
self.domain = search_strategy._shorthand[self.domain]
cls, properties = search_strategy._domains[self.domain]
except KeyError:
raise KeyError(_('Unknown search domain: %s') % self.domain)
query = search_strategy._session.query(cls)
## here is the place where to optionally filter out unrepresented
## domain values. each domain class should define its own 'I have
## accessions' filter. see issue #42
result = set()
# select all objects from the domain
if self.values == '*':
result.update(query.all())
return result
mapper = class_mapper(cls)
if self.cond in ('like', 'ilike'):
condition = lambda col: \
lambda val: utils.ilike(mapper.c[col], '%s' % val)
elif self.cond in ('contains', 'icontains', 'has', 'ihas'):
condition = lambda col: \
lambda val: utils.ilike(mapper.c[col], '%%%s%%' % val)
elif self.cond == '=':
condition = lambda col: \
lambda val: mapper.c[col] == utils.utf8(val)
else:
condition = lambda col: \
lambda val: mapper.c[col].op(self.cond)(val)
for col in properties:
ors = or_(*map(condition(col), self.values.express()))
result.update(query.filter(ors).all())
if None in result:
logger.warn('removing None from result set')
result = set(i for i in result if i is not None)
return result
def sp_get_completions(text):
query = (
self.session.query(Species)
.join("genus")
.filter(utils.ilike(Genus.genus, "%s%%" % text))
.filter(Species.id != self.model.id)
.order_by(Genus.genus, Species.sp)
)
return query
def plant_get_completions(text):
# TODO: only return those plants with propagations
from bauble.plugins.garden.accession import Accession
from bauble.plugins.garden.plant import Plant
query = self.session.query(Plant).join('accession').\
filter(utils.ilike(Accession.code, u'%s%%' % text)).\
filter(Accession.id != self.model.accession.id).\
order_by(Accession.code, Plant.code)
return query
def on_entry_changed(entry, presenter):
logger.debug('on_entry_changed(%s, %s)', entry, presenter)
text = utils.utf8(entry.props.text)
if not text and not required:
presenter.remove_problem(PROBLEM, entry)
on_select(None)
return
# see if the text matches a completion string
comp = entry.get_completion()
compl_model = comp.get_model()
def _cmp(row, data):
return utils.utf8(row[0]) == data
found = utils.search_tree_model(compl_model, text, _cmp)
if len(found) == 1:
comp.emit('match-selected', compl_model, found[0])
return True
# if text looks like '(code) name', then split it into the two
# parts, then see if the text matches exactly a code or name
match = re_code_name_splitter.match(text)
if match:
code, name = match.groups()
else:
code = name = text
codes = presenter.session.query(Location).\
filter(utils.ilike(Location.code, '%s' % utils.utf8(code)))
names = presenter.session.query(Location).\
filter(utils.ilike(Location.name, '%s' % utils.utf8(name)))
if codes.count() == 1:
logger.debug('location matches code')
location = codes.first()
presenter.remove_problem(PROBLEM, entry)
on_select(location)
elif names.count() == 1:
logger.debug('location matches name')
location = names.first()
presenter.remove_problem(PROBLEM, entry)
on_select(location)
else:
logger.debug('location %s does not match anything' % text)
presenter.add_problem(PROBLEM, entry)
return True
def on_entry_changed(entry, presenter):
logger.debug('on_entry_changed(%s, %s)', entry, presenter)
text = utils.utf8(entry.props.text)
if not text and not required:
presenter.remove_problem(PROBLEM, entry)
on_select(None)
return
# see if the text matches a completion string
comp = entry.get_completion()
compl_model = comp.get_model()
def _cmp(row, data):
return utils.utf8(row[0]) == data
found = utils.search_tree_model(compl_model, text, _cmp)
if len(found) == 1:
comp.emit('match-selected', compl_model, found[0])
return True
# if text looks like '(code) name', then split it into the two
# parts, then see if the text matches exactly a code or name
match = re_code_name_splitter.match(text)
if match:
code, name = match.groups()
else:
code = name = text
codes = presenter.session.query(Location).\
filter(utils.ilike(Location.code, '%s' % utils.utf8(code)))
names = presenter.session.query(Location).\
filter(utils.ilike(Location.name, '%s' % utils.utf8(name)))
if codes.count() == 1:
logger.debug('location matches code')
location = codes.first()
presenter.remove_problem(PROBLEM, entry)
on_select(location)
elif names.count() == 1:
logger.debug('location matches name')
location = names.first()
presenter.remove_problem(PROBLEM, entry)
on_select(location)
else:
logger.debug('location %s does not match anything' % text)
presenter.add_problem(PROBLEM, entry)
return True
def on_domain_expression(self, s, loc, tokens):
"""
Called when the parser hits a domain_expression token.
Searching using domain expressions is a little more magical
and queries mapper properties that were passed to add_meta()
To do a case sensitive search for a specific string use the
double equals, '=='
"""
domain, cond, values = tokens
try:
if domain in self._shorthand:
domain = self._shorthand[domain]
cls, properties = self._domains[domain]
except KeyError:
raise KeyError(_('Unknown search domain: %s' % domain))
result_key = self._result_keys[cls]
query = self._session.query(cls)
# select all objects from the domain
if values == '*':
self._results[result_key] = query.all()
return
mapper = class_mapper(cls)
if cond in ('like', 'ilike', 'contains', 'icontains', 'has', 'ihas'):
condition = lambda col: \
lambda val: utils.ilike(mapper.c[col], '%%%s%%' % val)
elif cond == '=':
condition = lambda col: \
lambda val: utils.ilike(mapper.c[col], val)
#lambda val: utils.ilike(mapper.c[col], utils.utf8(val))
else:
condition = lambda col: \
lambda val: mapper.c[col].op(cond)(val)
ors = or_(*[
condition(prop)(value) for value in values for prop in properties
])
self._results[result_key] = query.filter(ors).all()
def invoke(self, search_strategy):
"""
Called when the whole search string is a value list.
Search with a list of values is the broadest search and
searches all the mapper and the properties configured with
add_meta()
"""
logger.debug('ValueListAction:invoke')
# make searches case-insensitive, in postgres use ilike,
# in other use upper()
like = lambda table, col, val: \
utils.ilike(table.c[col], ('%%%s%%' % val))
result = set()
for cls, columns in search_strategy._properties.iteritems():
column_cross_value = [(c, v) for c in columns
for v in self.express()]
# as of SQLAlchemy>=0.4.2 we convert the value to a unicode
# object if the col is a Unicode or UnicodeText column in order
# to avoid the "Unicode type received non-unicode bind param"
def unicol(col, v):
table = class_mapper(cls)
if isinstance(table.c[col].type, (Unicode, UnicodeText)):
return unicode(v)
else:
return v
table = class_mapper(cls)
q = search_strategy._session.query(cls) # prepares SELECT
q = q.filter(
or_(*[
like(table, c, unicol(c, v)) for c, v in column_cross_value
]))
result.update(q.all())
def replace(i):
try:
replacement = i.replacement()
logger.debug('replacing %s by %s in result set' %
(i, replacement))
return replacement
except:
return i
result = set([replace(i) for i in result])
logger.debug("result is now %s" % result)
if None in result:
logger.warn('removing None from result set')
result = set(i for i in result if i is not None)
return result
def on_domain_expression(self, s, loc, tokens):
"""
Called when the parser hits a domain_expression token.
Searching using domain expressions is a little more magical
and queries mapper properties that were passed to add_meta()
To do a case sensitive search for a specific string use the
double equals, '=='
"""
domain, cond, values = tokens
try:
if domain in self._shorthand:
domain = self._shorthand[domain]
cls, properties = self._domains[domain]
except KeyError:
raise KeyError(_('Unknown search domain: %s' % domain))
query = self._session.query(cls)
# select all objects from the domain
if values == '*':
self._results.update(query.all())
return
mapper = class_mapper(cls)
if cond in ('like', 'ilike', 'contains', 'icontains', 'has', 'ihas'):
condition = lambda col: \
lambda val: utils.ilike(mapper.c[col], '%%%s%%' % val)
elif cond == '=':
condition = lambda col: \
lambda val: utils.ilike(mapper.c[col], utils.utf8(val))
else:
condition = lambda col: \
lambda val: mapper.c[col].op(cond)(val)
for col in properties:
ors = or_(*map(condition(col), values))
self._results.update(query.filter(ors).all())
return tokens
def __init__(self, t):
self.op = t[0][1]
self.operation = {'>': lambda x, y: x > y,
'<': lambda x, y: x < y,
'>=': lambda x, y: x >= y,
'<=': lambda x, y: x <= y,
'=': lambda x, y: x == y,
'!=': lambda x, y: x != y,
'is': lambda x, y: x is y,
'like': lambda x, y: utils.ilike(x, '%s' % y)
}[self.op]
self.operands = t[0][0::2] # every second object is an operand
def __init__(self, t):
logger.debug("IdentExpressionToken::__init__(%s)" % t)
self.op = t[0][1]
def not_implemented_yet(x, y):
# raise an exception
raise NotImplementedError
# cfr: SearchParser.binop
# = == != <> < <= > >= not like contains has ilike icontains ihas is
self.operation = {
"=": lambda x, y: x == y,
"==": lambda x, y: x == y,
"is": lambda x, y: x == y,
"!=": lambda x, y: x != y,
"<>": lambda x, y: x != y,
"not": lambda x, y: x != y,
"<": lambda x, y: x < y,
"<=": lambda x, y: x <= y,
">": lambda x, y: x > y,
">=": lambda x, y: x >= y,
"like": lambda x, y: utils.ilike(x, "%s" % y),
"contains": lambda x, y: utils.ilike(x, "%%%s%%" % y),
"has": lambda x, y: utils.ilike(x, "%%%s%%" % y),
"ilike": lambda x, y: utils.ilike(x, "%s" % y),
"icontains": lambda x, y: utils.ilike(x, "%%%s%%" % y),
"ihas": lambda x, y: utils.ilike(x, "%%%s%%" % y),
}[self.op]
self.operands = t[0][0::2] # every second object is an operand
def plant_get_completions(text):
# TODO: only return those plants with propagations
from bauble.plugins.garden.accession import Accession
from bauble.plugins.garden.plant import Plant
query = (
self.session.query(Plant)
.join("accession")
.filter(utils.ilike(Accession.code, u"%s%%" % text))
.filter(Accession.id != self.model.accession.id)
.order_by(Accession.code, Plant.code)
)
return query
def __init__(self, t):
self.op = t[0][1]
self.operation = {
'>': lambda x, y: x > y,
'<': lambda x, y: x < y,
'>=': lambda x, y: x >= y,
'<=': lambda x, y: x <= y,
'=': lambda x, y: x == y,
'!=': lambda x, y: x != y,
'is': lambda x, y: x is y,
'like': lambda x, y: utils.ilike(x, '%s' % y)
}[self.op]
self.operands = t[0][0::2] # every second object is an operand
def on_domain_expression(self, s, loc, tokens):
"""
Called when the parser hits a domain_expression token.
Searching using domain expressions is a little more magical
and queries mapper properties that were passed to add_meta()
To do a case sensitive search for a specific string use the
double equals, '=='
"""
domain, cond, values = tokens
try:
if domain in self._shorthand:
domain = self._shorthand[domain]
cls, properties = self._domains[domain]
except KeyError:
raise KeyError(_("Unknown search domain: %s" % domain))
result_key = self._result_keys[cls]
query = self._session.query(cls)
# select all objects from the domain
if values == "*":
self._results[result_key] = query.all()
return
mapper = class_mapper(cls)
if cond in ("like", "ilike", "contains", "icontains", "has", "ihas"):
condition = lambda col: lambda val: utils.ilike(mapper.c[col], "%%%s%%" % val)
elif cond == "=":
condition = lambda col: lambda val: utils.ilike(mapper.c[col], val)
# lambda val: utils.ilike(mapper.c[col], utils.utf8(val))
else:
condition = lambda col: lambda val: mapper.c[col].op(cond)(val)
ors = or_(*[condition(prop)(value) for value in values for prop in properties])
self._results[result_key] = query.filter(ors).all()
def invoke(self, search_strategy):
"""
Called when the whole search string is a value list.
Search with a list of values is the broadest search and
searches all the mapper and the properties configured with
add_meta()
"""
logger.debug('ValueListAction:invoke')
# make searches case-insensitive, in postgres use ilike,
# in other use upper()
like = lambda table, col, val: \
utils.ilike(table.c[col], ('%%%s%%' % val))
result = set()
for cls, columns in search_strategy._properties.iteritems():
column_cross_value = [(c, v) for c in columns
for v in self.express()]
# as of SQLAlchemy>=0.4.2 we convert the value to a unicode
# object if the col is a Unicode or UnicodeText column in order
# to avoid the "Unicode type received non-unicode bind param"
def unicol(col, v):
table = class_mapper(cls)
if isinstance(table.c[col].type, (Unicode, UnicodeText)):
return unicode(v)
else:
return v
table = class_mapper(cls)
q = search_strategy._session.query(cls) # prepares SELECT
q = q.filter(or_(*[like(table, c, unicol(c, v))
for c, v in column_cross_value]))
result.update(q.all())
def replace(i):
try:
replacement = i.replacement()
logger.debug('replacing %s by %s in result set' %
(i, replacement))
return replacement
except:
return i
result = set([replace(i) for i in result])
logger.debug("result is now %s" % result)
if None in result:
logger.warn('removing None from result set')
result = set(i for i in result if i is not None)
return result
def invoke(self, search_strategy):
try:
if self.domain in search_strategy._shorthand:
self.domain = search_strategy._shorthand[self.domain]
cls, properties = search_strategy._domains[self.domain]
except KeyError:
raise KeyError(_("Unknown search domain: %s") % self.domain)
query = search_strategy._session.query(cls)
## here is the place where to optionally filter out unrepresented
## domain values. each domain class should define its own 'I have
## accessions' filter. see issue #42
result = set()
# select all objects from the domain
if self.values == "*":
result.update(query.all())
return result
mapper = class_mapper(cls)
if self.cond in ("like", "ilike"):
condition = lambda col: lambda val: utils.ilike(mapper.c[col], "%s" % val)
elif self.cond in ("contains", "icontains", "has", "ihas"):
condition = lambda col: lambda val: utils.ilike(mapper.c[col], "%%%s%%" % val)
elif self.cond == "=":
condition = lambda col: lambda val: mapper.c[col] == utils.utf8(val)
else:
condition = lambda col: lambda val: mapper.c[col].op(self.cond)(val)
for col in properties:
ors = or_(*map(condition(col), self.values.express()))
result.update(query.filter(ors).all())
return result
def on_value_list(self, s, loc, tokens):
"""
Called when the parser hits a value_list token
Search with a list of values is the broadest search and
searches all the mapper and the properties configured with
add_meta()
"""
# print('values: %s' % tokens)
# print(' s: %s' % s)
# print(' loc: %s' % loc)
# print(' toks: %s' % tokens)
# make searches case-insensitive, in postgres use ilike,
# in other use upper()
like = lambda table, col, val: utils.ilike(table.c[col], ("%%%s%%" % val))
for cls, columns in self._properties.items():
q = self._session.query(cls)
cv = [(c, v) for c in columns for v in tokens]
mapper = class_mapper(cls)
q = q.filter(or_(*(like(mapper, c, v) for c, v in cv)))
self._results[self._result_keys[cls]] = q.all()
def on_query(self, s, loc, tokens):
"""
Called when the parser hits a query token.
Queries can use more database specific features. This also
means that the same query might not work the same on different
database types. For example, on a PostgreSQL database you can
use ilike but this would raise an error on SQLite.
"""
# The method requires that the underlying database support
# union and intersect. At the time of writing this MySQL
# didn't.
# TODO: support 'not' a boolean op as well, e.g sp where
# genus.genus=Maxillaria and not genus.family=Orchidaceae
domain, expr = tokens
check(domain in self._domains or domain in self._shorthand,
'Unknown search domain: %s' % domain)
if domain in self._shorthand:
domain = self._shorthand[domain]
cls = self._domains[domain][0]
main_query = self._session.query(cls)
mapper = class_mapper(cls)
expr_iter = iter(expr)
boolop = None
for e in expr_iter:
idents, cond, val = e
# debug('cls: %s, idents: %s, cond: %s, val: %s'
# % (cls.__name__, idents, cond, val))
if val == 'None':
val = None
if cond == 'is':
cond = '='
elif cond == 'is not':
cond = '!='
elif cond in ('ilike', 'icontains', 'ihas'):
cond = lambda col: \
lambda val: utils.ilike(col, '%s' % val)
if len(idents) == 1:
# we get here when the idents only refer to a property
# on the mapper table..i.e. a column
col = idents[0]
msg = _('The %(tablename)s table does not have a '\
'column named "%(columname)s"') % \
dict(tablename=mapper.local_table.name,
columname=col)
check(col in mapper.c, msg)
if isinstance(cond, str):
clause = getattr(cls, col).op(cond)(utils.utf8(val))
else:
clause = cond(getattr(cls, col))(utils.utf8(val))
query = self._session.query(cls).filter(clause).order_by(None)
else:
# we get here when the idents refer to a relation on a
# mapper/table
relations = idents[:-1]
col = idents[-1]
query = self._session.query(cls)
query = query.join(*relations)
# NOTE: SA07 - this depends on Query._joinpoint not changing,
# it changed in SA05 which broke this
local_table = query._joinpoint['prev'][0][1].local_table
if isinstance(cond, str):
clause = local_table.c[col].op(cond)(utils.utf8(val))
else:
clause = cond(local_table.c[col])(utils.utf8(val))
query = query.filter(clause).order_by(None)
if boolop == 'or':
main_query = main_query.union(query)
elif boolop == 'and':
main_query = main_query.intersect(query)
else:
main_query = query
try:
boolop = expr_iter.next()
except StopIteration:
pass
self._results.update(main_query.order_by(None).all())
def sp_get_completions(text):
query = self.session.query(Species).join('genus').\
filter(utils.ilike(Genus.genus, '%s%%' % text)).\
filter(Species.id != self.model.id).\
order_by(Genus.genus, Species.sp)
return query
def gen_get_completions(text):
clause = utils.ilike(Genus.genus, '%s%%' % unicode(text))
return self.session.query(Genus).filter(clause).\
order_by(Genus.genus)
def sp_get_completions(text):
query = self.session.query(Species).join('genus').\
filter(utils.ilike(Genus.genus, '%s%%' % text)).\
filter(Species.id != self.model.id).\
order_by(Genus.genus, Species.sp)
return query
def gen_get_completions(text):
clause = utils.ilike(Genus.genus, '%s%%' % unicode(text))
return self.session.query(Genus).filter(clause).\
order_by(Genus.genus)
def __init__(self, t):
logger.debug('IdentExpressionToken::__init__(%s)' % t)
self.op = t[0][1]
def not_implemented_yet(x, y):
# raise an exception
raise NotImplementedError
# cfr: SearchParser.binop
# = == != <> < <= > >= not like contains has ilike icontains ihas is
self.operation = {'=': lambda x, y: x == y,
'==': lambda x, y: x == y,
'is': lambda x, y: x == y,
'!=': lambda x, y: x != y,
'<>': lambda x, y: x != y,
'not': lambda x, y: x != y,
'<': lambda x, y: x < y,
'<=': lambda x, y: x <= y,
'>': lambda x, y: x > y,
'>=': lambda x, y: x >= y,
'like': lambda x, y: utils.ilike(x, '%s' % y),
'contains': not_implemented_yet,
'has': not_implemented_yet,
'ilike': lambda x, y: utils.ilike(x, '%s' % y),
'icontains': not_implemented_yet,
'ihas': not_implemented_yet,
}[self.op]
self.operands = t[0][0::2] # every second object is an operand
