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 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_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 __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