Exemplo n.º 1
0
 def handle_info_json(self, model, info, fulltextsearch=None):
     """Handle info JSON query filter."""
     clauses = []
     headlines = []
     order_by_ranks = []
     if '::' in info:
         pairs = info.split('|')
         for pair in pairs:
             if pair != '':
                 k,v = pair.split("::")
                 if fulltextsearch == '1':
                     vector = _entity_descriptor(model, 'info')[k].astext
                     clause = func.to_tsvector(vector).match(v)
                     clauses.append(clause)
                     if len(headlines) == 0:
                         headline = func.ts_headline(self.language, vector, func.to_tsquery(v))
                         headlines.append(headline)
                         order = func.ts_rank_cd(func.to_tsvector(vector), func.to_tsquery(v), 4).label('rank')
                         order_by_ranks.append(order)
                 else:
                     clauses.append(_entity_descriptor(model,
                                                       'info')[k].astext == v)
     else:
         info = json.dumps(info)
         clauses.append(cast(_entity_descriptor(model, 'info'),
                             Text) == info)
     return clauses, headlines, order_by_ranks
Exemplo n.º 2
0
 def handle_info_json(self, model, info, fulltextsearch=None):
     """Handle info JSON query filter."""
     clauses = []
     headlines = []
     order_by_ranks = []
     if '::' in info:
         pairs = info.split('|')
         for pair in pairs:
             if pair != '':
                 k, v = pair.split("::")
                 if fulltextsearch == '1':
                     vector = _entity_descriptor(model, 'info')[k].astext
                     clause = func.to_tsvector(vector).match(v)
                     clauses.append(clause)
                     if len(headlines) == 0:
                         headline = func.ts_headline(
                             self.language, vector, func.to_tsquery(v))
                         headlines.append(headline)
                         order = func.ts_rank_cd(func.to_tsvector(vector),
                                                 func.to_tsquery(v),
                                                 4).label('rank')
                         order_by_ranks.append(order)
                 else:
                     clauses.append(
                         _entity_descriptor(model, 'info')[k].astext == v)
     else:
         info = json.dumps(info)
         clauses.append(
             cast(_entity_descriptor(model, 'info'), Text) == info)
     return clauses, headlines, order_by_ranks
Exemplo n.º 3
0
def _apply_dynamic_fulltext_filters(ingredients, backup_search=False):
    """
    Applies full text filters similar to v1 but uses full text search approach for lexemes and speed up with
    index usage. N.B. that spaces in ingredient names will be replaced with '&'
    :param ingredients: List<string>: ["onion", "chicken", "pork tenderloin"]
    :return:
    """
    dynamic_filters = []
    max_ingredients = 500 if len(
        ingredients
    ) > 2 else 25  # 5 if backup_search else 250 # limits the amount of match ingredients; necessary in large or backup search
    title_subquery = lambda _: IngredientRecipe.recipe.in_(
        [-1])  # function to add title checking fo backup query
    if backup_search:
        max_ingredients = 10
        # searches for some combination of ingredients instead of individual ingredients in the backup search
        ingredients = ['|'.join(i) for i in combinations(ingredients, 3)] \
            if len(ingredients) >= 3 else ['|'.join(ingredients)]
        # print(len(ingredients))
        random.shuffle(ingredients)
        ingredients = ingredients[:5]
        title_subquery = lambda ingredient: (IngredientRecipe.recipe.in_(
            db.session.query(Recipe.pk).filter(
                func.to_tsquery(FULLTEXT_INDEX_CONFIG, ingredient).op('@@')
                (func.to_tsvector(FULLTEXT_INDEX_CONFIG,
                                  func.coalesce(Recipe.title)))).limit(50)))
        # print(ingredients)
    for ingredient in ingredients:
        dynamic_filters.append(
            or_(
                IngredientRecipe.recipe.in_(
                    db.session.query(IngredientRecipe.recipe).filter(
                        IngredientRecipe.ingredient.in_(
                            db.session.query(Ingredient.pk).filter(
                                    func.to_tsquery(FULLTEXT_INDEX_CONFIG, func.coalesce(ingredient)).op('@@')(
                                        func.to_tsvector(FULLTEXT_INDEX_CONFIG, func.coalesce(Ingredient.name))
                                    )
                            ).\
                            order_by(
                                desc(
                                    func.similarity(
                                        ingredient,
                                        Ingredient.name
                                    )
                                )
                            ).limit(max_ingredients)
                        )
                    ),
                ),
                title_subquery(ingredient)
            )
        )
    return dynamic_filters
Exemplo n.º 4
0
 def search(self, search_string, limit=None, offset=None):
     query = (db.session.query(IndexedCategory.id)
              .filter(IndexedCategory.title_vector.op('@@')(
                  func.to_tsquery('simple', preprocess_ts_string(search_string))))
              .limit(limit)
              .offset(offset))
     return [x[0] for x in query]
Exemplo n.º 5
0
    def _get_fts_phrase_clauses(self, data):
        """Return full-text search phrase clauses."""
        q = self._parse_json('fts_phrase', data)
        err_base = 'invalid "fts_phrase" clause'
        clauses = []
        for col, settings in q.items():
            vector = self._get_vector(col)

            # Check params
            if not isinstance(settings, dict):
                msg = '{0}: {1} is not {2}'.format(err_base, col, dict)
                raise ValueError(msg)
            query = settings.get('query')
            if not query:
                msg = '{0}: "query" is required'.format(err_base)
                raise ValueError(msg)
            distance = settings.get('distance', 1)
            operator = ' <{}> '.format(distance)

            # Generate clause
            tokens = query.split()
            word_clauses = []
            query_str = operator.join(tokens)
            ts_query = func.to_tsquery(query_str)
            clause = func.to_tsvector(vector).op('@@')(ts_query)
            clauses.append(clause)

        return clauses
Exemplo n.º 6
0
 def search(self, qs):
     qs_ = get_ts_search_string(qs)
     if qs_:
         query = func.to_tsquery('english', qs_)
     else:
         query = func.plainto_tsquery('english', qs)
     return self.model_col.op('@@')(query)
Exemplo n.º 7
0
 def search(self, search_string, limit=None, offset=None):
     query = (db.session.query(IndexedCategory.id)
              .filter(IndexedCategory.title_vector.op('@@')(
                  func.to_tsquery('simple', preprocess_ts_string(search_string))))
              .limit(limit)
              .offset(offset))
     return [x[0] for x in query]
Exemplo n.º 8
0
def make_fts_expr_tsvec(languages, target, searchstring, op="&"):
    """Searches tsvector column. `target` should have a gin index.
    :param language: postgresql language string
    :param target: SQLAlchemy expression with type tsvector
    :param searchstring: string of space-separated words to search
    :param op: operator used to join searchterms separated by space, | or &
    """
    languages = list(languages)
    prepared_searchstring = _prepare_searchstring(op, searchstring)

    ts_query = func.to_tsquery(languages[0], prepared_searchstring)

    for language in languages[1:]:
        ts_query = ts_query.op("||")(func.to_tsquery(language, prepared_searchstring))

    return target.op("@@")(ts_query)
Exemplo n.º 9
0
    def finalize_query(self, query, fltr, session, qstring=None, order_by=None):
        search_query = None
        ranked = False
        if qstring is not None:
            ft_query = and_(SearchObjectIndex.so_uuid == ObjectInfoIndex.uuid, query)
            q = session.query(
                ObjectInfoIndex,
                func.ts_rank_cd(
                    SearchObjectIndex.search_vector,
                    func.plainto_tsquery(qstring)
                ).label('rank'))\
            .options(subqueryload(ObjectInfoIndex.search_object))\
            .options(subqueryload(ObjectInfoIndex.properties)).filter(ft_query)

            query_result = search(q, qstring, vector=SearchObjectIndex.search_vector, sort=order_by is None, regconfig='simple')
            ranked = True
        else:
            query_result = session.query(ObjectInfoIndex).options(subqueryload(ObjectInfoIndex.properties)).filter(query)

        if order_by is not None:
            query_result = query_result.order_by(order_by)
        elif ranked is True:
            query_result = query_result.order_by(
                desc(
                    func.ts_rank_cd(
                        SearchObjectIndex.search_vector,
                        func.to_tsquery(search_query)
                    )
                )
            )
        if 'limit' in fltr:
            query_result = query_result.limit(fltr['limit'])
        return query_result, ranked
Exemplo n.º 10
0
def create_fulltext_ingredient_search(ingredients,
                                      limit=DEFAULT_SEARCH_RESULT_SIZE,
                                      op=and_,
                                      backup_search=False):
    """
    Function to create a fulltext query to filter out all recipes not containing <min_ingredients> ingredients. Ranks by
    recipe that contains the most ingredients, and then ranks by match of ingredients list to the title. This could
    probably be improved by adding additional search criteria similar to the previous fulltext search approach in
    create_fulltext_search_query.
    :param ingredients: List<string> ["onion", "chicken", "peppers"]
    :param limit: number of recipes to return
    :param order_by: the operation/func with which to order searches
    :return: List<Recipe>
    """
    ingredients = _clean_and_stringify_ingredients_query(ingredients)
    return db.session.query(Recipe). \
        join(IngredientRecipe). \
        join(Ingredient). \
        filter(
            op(
                *_apply_dynamic_fulltext_filters(ingredients, backup_search=backup_search)
            )
        ). \
        group_by(Recipe.pk). \
        order_by(desc(
            func.ts_rank_cd(
                func.to_tsvector(FULLTEXT_INDEX_CONFIG, func.coalesce(Recipe.title)),
                func.to_tsquery(FULLTEXT_INDEX_CONFIG, '|'.join(i for i in ingredients)),
                32
            ) * RECIPE_TITLE_WEIGHT +
            func.ts_rank_cd(
                func.to_tsvector(FULLTEXT_INDEX_CONFIG, func.coalesce(Recipe.recipe_ingredients_text)),
                func.to_tsquery(FULLTEXT_INDEX_CONFIG, '|'.join(i for i in ingredients)),
                32
            ) * RECIPE_INGREDIENTS_WEIGHT +
            func.sum(
                func.ts_rank(
                    func.to_tsvector(FULLTEXT_INDEX_CONFIG, func.coalesce(Ingredient.name)),
                    func.to_tsquery(FULLTEXT_INDEX_CONFIG, '|'.join(i for i in ingredients))
                )
            ) * INGREDIENTS_WEIGHT +
            func.ts_rank_cd(
                func.to_tsvector(FULLTEXT_INDEX_CONFIG, func.coalesce(Recipe.recipe_ingredients_text)),
                func.to_tsquery(FULLTEXT_INDEX_CONFIG, '&'.join(i for i in ingredients)),
                32
            ) * RECIPE_MODIFIERS_WEIGHT
        )).limit(limit).all()
Exemplo n.º 11
0
 def _db_match(self, dbquery, size, start, query, search_vector):
     if query:
         query = re.sub("\W+", " ", query).strip().replace(" ", " & ")
         dbquery = dbquery.filter(search_vector.match(query))
     total = dbquery.count()
     if query:
         dbquery = dbquery.order_by(func.ts_rank(search_vector, func.to_tsquery(query)).desc())
     dbquery = dbquery.offset(start).limit(size)
     return dbquery, total
Exemplo n.º 12
0
    def finalize_query(self,
                       query,
                       fltr,
                       session,
                       qstring=None,
                       order_by=None):
        search_query = None
        if qstring is not None:
            search_query = parse_search_query(qstring)
            ft_query = and_(
                SearchObjectIndex.search_vector.match(
                    search_query,
                    sort=order_by is None,
                    postgresql_regconfig='simple'),
                SearchObjectIndex.so_uuid == ObjectInfoIndex.uuid, query)
        else:
            ft_query = query

        ranked = False

        if search_query is not None:
            query_result = session.query(
                ObjectInfoIndex,
                func.ts_rank_cd(
                    SearchObjectIndex.search_vector,
                    func.to_tsquery(search_query)).label('rank')).options(
                        joinedload(ObjectInfoIndex.search_object)).options(
                            joinedload(
                                ObjectInfoIndex.properties)).filter(ft_query)
            ranked = True
        else:
            query_result = session.query(ObjectInfoIndex).options(
                joinedload(ObjectInfoIndex.properties)).filter(ft_query)

        if order_by is not None:
            query_result = query_result.order_by(order_by)
        elif ranked is True:
            query_result = query_result.order_by(
                desc(
                    func.ts_rank_cd(SearchObjectIndex.search_vector,
                                    func.to_tsquery(search_query))))
        if 'limit' in fltr:
            query_result = query_result.limit(fltr['limit'])
        return query_result, ranked
Exemplo n.º 13
0
    def handle_info_json(self, model, info, fulltextsearch=None):
        """Handle info JSON query filter."""
        clauses = []
        headlines = []
        order_by_ranks = []

        if info and '::' in info:
            pairs = info.split('|')
            for pair in pairs:
                if pair != '':
                    k, v = pair.split("::")
                    if fulltextsearch == '1':
                        vector = _entity_descriptor(model, 'info')[k].astext
                        clause = func.to_tsvector(vector).match(v)
                        clauses.append(clause)
                        if len(headlines) == 0:
                            headline = func.ts_headline(
                                self.language,
                                vector,
                                func.to_tsquery(v))
                            headlines.append(headline)
                            order = func.ts_rank_cd(
                                func.to_tsvector(vector),
                                func.to_tsquery(v), 4).label('rank')
                            order_by_ranks.append(order)
                    else:
                        clauses.append(
                            _entity_descriptor(model, 'info')[k].astext == v)
        else:
            if type(info) == dict:
                clauses.append(_entity_descriptor(model, 'info') == info)
            if type(info) == str or type(info) == str:
                try:
                    info = json.loads(info)
                    if type(info) == int or type(info) == float:
                        info = '"%s"' % info
                except ValueError:
                    info = '"%s"' % info
                clauses.append(_entity_descriptor(model,
                                                  'info').contains(info))
        return clauses, headlines, order_by_ranks
Exemplo n.º 14
0
    def search(self, search_string, order_by='start'):
        if order_by == 'start':
            order = IndexedEvent.start_date.desc()
        elif order_by == 'id':
            order = IndexedEvent.id
        else:
            order = None

        return (db.session.query(IndexedEvent.id)
                  .filter(IndexedEvent.title_vector.op('@@')(
                      func.to_tsquery('simple', preprocess_ts_string(search_string))))
                  .order_by(order))
Exemplo n.º 15
0
 def text_search_by_words(self, words: list) -> list:
     search_words = ' & '.join(words)
     with session_scope(self.get_session) as session:
         results = session.query(Tournament) \
             .filter(Tournament.tsv.match(search_words, postgresql_regconfig='english')).all()
         return [Tournament(id=t.id,
                            name=t.name,
                            description=session.query(
                                func.ts_headline('english', t.description,
                                                 func.to_tsquery(search_words, postgresql_regconfig='english')))
                            .first())
                 for t in results]
Exemplo n.º 16
0
def _make_languages_tsquery(languages, searchstring, op):
    """
    Prepares a `tsquery` expression to be used
    in the construction of search expressions.
    :param languages: postgresql language string
    :param searchstring: string of space-separated words to search
    :param op: operator used to join searchterms separated by space, | or &
    """
    prepared_searchstring = _prepare_searchstring(op, searchstring)

    mk_query = lambda lang: func.to_tsquery(lang, prepared_searchstring)
    ts_queries = _itertools.imap(mk_query, languages)
    return reduce(lambda query1, query2: query1.op("||")(query2), ts_queries)
Exemplo n.º 17
0
    def text_search(self, tquery_str: str) -> "BookmarkViewQueryBuilder":
        self._query = self._query.outerjoin(
            FullText, FullText.url_uuid == SQLABookmark.url_uuid)
        # necessary to coalesce this as there may be no fulltext
        fulltext = func.coalesce(FullText.tsvector, func.to_tsvector(""))

        self._combined_tsvector = (func.to_tsvector(
            SQLABookmark.title).op("||")(func.to_tsvector(
                SQLABookmark.description)).op("||")(fulltext))
        self._tsquery = func.to_tsquery(tquery_str)
        self._query = self._query.filter(
            self._combined_tsvector.op("@@")(self._tsquery))
        return self
Exemplo n.º 18
0
def search(name):
    name = name.lower()
    queries = name.split() # makes an array
    print(queries)
    tup = ()

    a = and_(Character.tsvector_col.match(q) for q in queries)
    o = or_(Character.tsvector_col.match(q) for q in queries)
    #achar = and_(*tup)
    #ochar = or_(tup)
    a_string = ''
    for index in range(0, len(queries)):
        a_string += queries[index]
        if index + 1 is not len(queries):
            a_string += ' & '
    print(a_string)
    o_string = ''
    for index in range(0, len(queries)):
        o_string += queries[index]
        if index + 1 is not len(queries):
            o_string += ' | '
    print(o_string)

    acharacters = db.session.query(Character, func.ts_headline('english', Character.name, func.to_tsquery(a_string)).label('hname'), func.ts_headline('english', Character.publisher_name, func.plainto_tsquery(a_string)).label('hpub'), func.ts_headline('english', Character.appear, func.plainto_tsquery(a_string)).label('happear')).filter(and_(Character.tsvector_col.match(q) for q in queries)).all()

    ocharacters = db.session.query(Character, func.ts_headline('english', Character.name, func.to_tsquery(o_string)).label('hname'), func.ts_headline('english', Character.publisher_name, func.plainto_tsquery(o_string)).label('hpub'), func.ts_headline('english', Character.appear, func.plainto_tsquery(o_string)).label('happear')).filter(or_(Character.tsvector_col.match(q) for q in queries)).all()

    apublishers = db.session.query(Publisher, func.ts_headline('english', Publisher.state, func.to_tsquery(a_string)).label('hstate'), func.ts_headline('english', Publisher.name, func.to_tsquery(a_string)).label('hname'), func.ts_headline('english', Publisher.address, func.plainto_tsquery(a_string)).label('haddress'), func.ts_headline('english', Publisher.city, func.plainto_tsquery(name)).label('hcity')).filter(and_(Publisher.tsvector_col.match(q) for q in queries)).all()
    opublishers = db.session.query(Publisher, func.ts_headline('english', Publisher.state, func.to_tsquery(o_string)).label('hstate'), func.ts_headline('english', Publisher.name, func.to_tsquery(o_string)).label('hname'), func.ts_headline('english', Publisher.address, func.plainto_tsquery(o_string)).label('haddress'), func.ts_headline('english', Publisher.city, func.plainto_tsquery(name)).label('hcity')).filter(or_(Publisher.tsvector_col.match(q) for q in queries)).all()
    ateams = db.session.query(Team, func.ts_headline('english', Team.name, func.to_tsquery(a_string)).label('hname'), func.ts_headline('english', Team.publisher_name, func.plainto_tsquery(a_string)).label('hpub'), func.ts_headline('english', Team.appear, func.plainto_tsquery(a_string)).label('happear')).filter(and_(Team.tsvector_col.match(q) for q in queries)).all()
    oteams = db.session.query(Team, func.ts_headline('english', Team.name, func.to_tsquery(o_string)).label('hname'), func.ts_headline('english', Team.publisher_name, func.plainto_tsquery(o_string)).label('hpub'), func.ts_headline('english', Team.appear, func.plainto_tsquery(o_string)).label('happear')).filter(or_(Team.tsvector_col.match(q) for q in queries)).all()
    avolumes = db.session.query(Volume, func.ts_headline('english', Volume.name, func.to_tsquery(a_string)).label('hname'), func.ts_headline('english', Volume.publisher_name, func.plainto_tsquery(a_string)).label('hpub'), func.ts_headline('english', Volume.start_year, func.plainto_tsquery(a_string)).label('hstart')).filter(and_(Volume.tsvector_col.match(q) for q in queries)).all()
    ovolumes = db.session.query(Volume, func.ts_headline('english', Volume.name, func.to_tsquery(o_string)).label('hname'), func.ts_headline('english', Volume.publisher_name, func.plainto_tsquery(o_string)).label('hpub'), func.ts_headline('english', Volume.start_year, func.plainto_tsquery(o_string)).label('hstart')).filter(or_(Volume.tsvector_col.match(q) for q in queries)).all()

    #print(publishers)
    #print(teams)
    #print(volumes)
    return render_template('search_result_template.html',  acharacters=acharacters, ocharacters=ocharacters, opublishers=opublishers, apublishers=apublishers, avolumes=avolumes, ovolumes=ovolumes, ateams=ateams, oteams=oteams)
Exemplo n.º 19
0
    def search(self, search_string, order_by='start'):
        if order_by == 'start':
            order = IndexedEvent.start_date.desc()
        elif order_by == 'id':
            order = IndexedEvent.id
        else:
            order = None

        return (db.session.query(IndexedEvent.id).join(
            Event,
            Event.id == IndexedEvent.id).filter(~Event.is_deleted).filter(
                IndexedEvent.title_vector.op('@@')(func.to_tsquery(
                    'simple',
                    preprocess_ts_string(search_string)))).order_by(order))
Exemplo n.º 20
0
    def handle_info_json(self, model, info, fulltextsearch=None):
        """Handle info JSON query filter."""
        clauses = []
        headlines = []
        order_by_ranks = []

        if info and '::' in info:
            pairs = info.split('|')
            for pair in pairs:
                if pair != '':
                    k,v = pair.split("::")
                    if fulltextsearch == '1':
                        vector = _entity_descriptor(model, 'info')[k].astext
                        clause = func.to_tsvector(vector).match(v)
                        clauses.append(clause)
                        if len(headlines) == 0:
                            headline = func.ts_headline(self.language, vector, func.to_tsquery(v))
                            headlines.append(headline)
                            order = func.ts_rank_cd(func.to_tsvector(vector), func.to_tsquery(v), 4).label('rank')
                            order_by_ranks.append(order)
                    else:
                        clauses.append(_entity_descriptor(model,
                                                          'info')[k].astext == v)
        else:
            if type(info) == dict:
                clauses.append(_entity_descriptor(model, 'info') == info)
            if type(info) == str or type(info) == unicode:
                try:
                    info = json.loads(info)
                    if type(info) == int or type(info) == float:
                        info = '"%s"' % info
                except ValueError:
                    info = '"%s"' % info
                clauses.append(_entity_descriptor(model,
                                                  'info').contains(info))
        return clauses, headlines, order_by_ranks
Exemplo n.º 21
0
 def text_search_by_word(self, word) -> list:
     with session_scope(self.get_session) as session:
         results = session.query(Team).filter(
             Team.tsv.match(word, postgresql_regconfig='english')).all()
         return [
             Team(id=t.id,
                  name=t.name,
                  description=session.query(
                      func.ts_headline(
                          'english', t.description,
                          func.to_tsquery(
                              word,
                              postgresql_regconfig='english'))).first())
             for t in results
         ]
Exemplo n.º 22
0
 def fulltext_search(self, query: str, including: bool):
     """
     SELECT id, ts_headline(description, q) FROM (
         SELECT id, description, q
             FROM goods, to_tsquery('english', 'query & here') q
             WHERE to_tsvector('english', description) @@ q
     ) search_t;
     """
     query = self.__prepare_query(query, including)
     q = func.to_tsquery('english', query)
     inner_statement = self.__session \
         .query(Goods.id, Goods.description, q) \
         .select_from(q) \
         .filter(func.to_tsvector('english', Goods.description).match(query, postgresql_regconfig='english')) \
         .subquery()
     return self.__session.query(
         inner_statement.c.id,
         func.ts_headline(inner_statement.c.description, q)).all()
Exemplo n.º 23
0
 def resolve_search(self, info, title, types=None, result=None):
     tsquery = func.to_tsquery(f'\'{title}\'')
     query = (
         TitleModel
         .query
         .filter(TitleModel.title_search_col.op('@@')(tsquery))
     )
     query = (
         query.filter(TitleModel._type.in_(types))
         if types is not None else query
     )
     query = (
         query
         .join(TitleModel.rating)
         .order_by(
             desc(RatingModel.numVotes >= 1000),
             desc(TitleModel.primaryTitle.ilike(title)),
             desc(RatingModel.numVotes),
             desc(func.ts_rank_cd(TitleModel.title_search_col, tsquery, 1))
         )
         .limit(result)
     )
     return query
Exemplo n.º 24
0
    def fulltextsearch(self):
        lang = locale_negotiator(self.request)

        try:
            language = self.languages[lang]
        except KeyError:
            return HTTPInternalServerError(
                detail="%s not defined in languages" % lang)

        if "query" not in self.request.params:
            return HTTPBadRequest(detail="no query")
        terms = self.request.params.get("query")

        maxlimit = self.settings.get("maxlimit", 200)

        try:
            limit = int(self.request.params.get(
                "limit",
                self.settings.get("defaultlimit", 30)))
        except ValueError:
            return HTTPBadRequest(detail="limit value is incorrect")
        if limit > maxlimit:
            limit = maxlimit

        try:
            partitionlimit = int(self.request.params.get("partitionlimit", 0))
        except ValueError:
            return HTTPBadRequest(detail="partitionlimit value is incorrect")
        if partitionlimit > maxlimit:
            partitionlimit = maxlimit

        terms_ts = "&".join(w + ":*"
                            for w in IGNORED_CHARS_RE.sub(" ", terms).split(" ") if w != "")
        _filter = FullTextSearch.ts.op("@@")(func.to_tsquery(language, terms_ts))

        if self.request.user is None or self.request.user.role is None:
            _filter = and_(_filter, FullTextSearch.public.is_(True))
        else:
            _filter = and_(
                _filter,
                or_(
                    FullTextSearch.public.is_(True),
                    FullTextSearch.role_id.is_(None),
                    FullTextSearch.role_id == self.request.user.role.id
                )
            )

        if "interface" in self.request.params:
            _filter = and_(_filter, or_(
                FullTextSearch.interface_id.is_(None),
                FullTextSearch.interface_id == self._get_interface_id(
                    self.request.params["interface"]
                )
            ))
        else:
            _filter = and_(_filter, FullTextSearch.interface_id.is_(None))

        _filter = and_(_filter, or_(
            FullTextSearch.lang.is_(None),
            FullTextSearch.lang == lang,
        ))

        # The numbers used in ts_rank_cd() below indicate a normalization method.
        # Several normalization methods can be combined using |.
        # 2 divides the rank by the document length
        # 8 divides the rank by the number of unique words in document
        # By combining them, shorter results seem to be preferred over longer ones
        # with the same ratio of matching words. But this relies only on testing it
        # and on some assumptions about how it might be calculated
        # (the normalization is applied two times with the combination of 2 and 8,
        # so the effect on at least the one-word-results is therefore stronger).
        rank = func.ts_rank_cd(FullTextSearch.ts, func.to_tsquery(language, terms_ts), 2 | 8)

        if partitionlimit:
            # Here we want to partition the search results based on
            # layer_name and limit each partition.
            row_number = func.row_number().over(
                partition_by=FullTextSearch.layer_name,
                order_by=(desc(rank), FullTextSearch.label)
            ).label("row_number")
            subq = DBSession.query(FullTextSearch) \
                .add_columns(row_number).filter(_filter).subquery()
            query = DBSession.query(
                subq.c.id, subq.c.label, subq.c.params, subq.c.layer_name,
                subq.c.the_geom, subq.c.actions
            )
            query = query.filter(subq.c.row_number <= partitionlimit)
        else:
            query = DBSession.query(FullTextSearch).filter(_filter)
            query = query.order_by(desc(rank))
            query = query.order_by(FullTextSearch.label)

        query = query.limit(limit)
        objs = query.all()

        features = []
        for o in objs:
            properties = {
                "label": o.label,
            }
            if o.layer_name is not None:
                properties["layer_name"] = o.layer_name
            if o.params is not None:
                properties["params"] = o.params
            if o.actions is not None:
                properties["actions"] = o.actions
            if o.actions is None and o.layer_name is not None:
                properties["actions"] = [{
                    "action": "add_layer",
                    "data": o.layer_name,
                }]

            if o.the_geom is not None:
                geom = to_shape(o.the_geom)
                feature = Feature(
                    id=o.id, geometry=geom,
                    properties=properties, bbox=geom.bounds
                )
                features.append(feature)
            else:
                feature = Feature(
                    id=o.id, properties=properties
                )
                features.append(feature)

        # TODO: add callback function if provided in self.request, else return geojson
        return FeatureCollection(features)
Exemplo n.º 25
0
    def fulltextsearch(self):
        lang = locale_negotiator(self.request)

        try:
            language = self.languages[lang]
        except KeyError:
            return HTTPInternalServerError(
                detail="{0!s} not defined in languages".format(lang))

        if "query" not in self.request.params:
            return HTTPBadRequest(detail="no query")
        terms = self.request.params.get("query")

        maxlimit = self.settings.get("maxlimit", 200)

        try:
            limit = int(
                self.request.params.get("limit",
                                        self.settings.get("defaultlimit", 30)))
        except ValueError:
            return HTTPBadRequest(detail="limit value is incorrect")
        if limit > maxlimit:
            limit = maxlimit

        try:
            partitionlimit = int(self.request.params.get("partitionlimit", 0))
        except ValueError:
            return HTTPBadRequest(detail="partitionlimit value is incorrect")
        if partitionlimit > maxlimit:
            partitionlimit = maxlimit

        terms_ts = "&".join(
            w + ":*" for w in IGNORED_CHARS_RE.sub(" ", terms).split(" ")
            if w != "")
        _filter = FullTextSearch.ts.op("@@")(func.to_tsquery(
            language, terms_ts))

        if self.request.user is None:
            _filter = and_(_filter, FullTextSearch.public.is_(True))
        else:
            _filter = and_(
                _filter,
                or_(
                    FullTextSearch.public.is_(True),
                    FullTextSearch.role_id.is_(None),
                    FullTextSearch.role_id.in_(
                        [r.id for r in self.request.user.roles]),
                ),
            )

        if "interface" in self.request.params:
            _filter = and_(
                _filter,
                or_(
                    FullTextSearch.interface_id.is_(None),
                    FullTextSearch.interface_id == self._get_interface_id(
                        self.request.params["interface"]),
                ),
            )
        else:
            _filter = and_(_filter, FullTextSearch.interface_id.is_(None))

        _filter = and_(
            _filter,
            or_(FullTextSearch.lang.is_(None), FullTextSearch.lang == lang))

        rank_system = self.request.params.get("ranksystem")
        if rank_system == "ts_rank_cd":
            # The numbers used in ts_rank_cd() below indicate a normalization method.
            # Several normalization methods can be combined using |.
            # 2 divides the rank by the document length
            # 8 divides the rank by the number of unique words in document
            # By combining them, shorter results seem to be preferred over longer ones
            # with the same ratio of matching words. But this relies only on testing it
            # and on some assumptions about how it might be calculated
            # (the normalization is applied two times with the combination of 2 and 8,
            # so the effect on at least the one-word-results is therefore stronger).
            rank = func.ts_rank_cd(FullTextSearch.ts,
                                   func.to_tsquery(language, terms_ts), 2 | 8)
        else:
            # Use similarity ranking system from module pg_trgm.
            rank = func.similarity(FullTextSearch.label, terms)

        if partitionlimit:
            # Here we want to partition the search results based on
            # layer_name and limit each partition.
            row_number = (func.row_number().over(
                partition_by=FullTextSearch.layer_name,
                order_by=(desc(rank),
                          FullTextSearch.label)).label("row_number"))
            subq = DBSession.query(FullTextSearch).add_columns(
                row_number).filter(_filter).subquery()
            query = DBSession.query(subq.c.id, subq.c.label, subq.c.params,
                                    subq.c.layer_name, subq.c.the_geom,
                                    subq.c.actions)
            query = query.filter(subq.c.row_number <= partitionlimit)
        else:
            query = DBSession.query(FullTextSearch).filter(_filter)
            query = query.order_by(desc(rank))
            query = query.order_by(FullTextSearch.label)

        query = query.limit(limit)
        objs = query.all()

        features = []
        for o in objs:
            properties = {"label": o.label}
            if o.layer_name is not None:
                properties["layer_name"] = o.layer_name
            if o.params is not None:
                properties["params"] = o.params
            if o.actions is not None:
                properties["actions"] = o.actions
            if o.actions is None and o.layer_name is not None:
                properties["actions"] = [{
                    "action": "add_layer",
                    "data": o.layer_name
                }]

            if o.the_geom is not None:
                geom = to_shape(o.the_geom)
                feature = Feature(id=o.id,
                                  geometry=geom,
                                  properties=properties,
                                  bbox=geom.bounds)
                features.append(feature)
            else:
                feature = Feature(id=o.id, properties=properties)
                features.append(feature)

        return FeatureCollection(features)
Exemplo n.º 26
0
def search(name):
    name = name.lower()
    queries = name.split()  # makes an array
    print(queries)
    tup = ()

    a = and_(Character.tsvector_col.match(q) for q in queries)
    o = or_(Character.tsvector_col.match(q) for q in queries)
    #achar = and_(*tup)
    #ochar = or_(tup)
    a_string = ''
    for index in range(0, len(queries)):
        a_string += queries[index]
        if index + 1 is not len(queries):
            a_string += ' & '
    print(a_string)
    o_string = ''
    for index in range(0, len(queries)):
        o_string += queries[index]
        if index + 1 is not len(queries):
            o_string += ' | '
    print(o_string)

    acharacters = db.session.query(
        Character,
        func.ts_headline('english', Character.name,
                         func.to_tsquery(a_string)).label('hname'),
        func.ts_headline('english', Character.publisher_name,
                         func.plainto_tsquery(a_string)).label('hpub'),
        func.ts_headline(
            'english', Character.appear,
            func.plainto_tsquery(a_string)).label('happear')).filter(
                and_(Character.tsvector_col.match(q) for q in queries)).all()

    ocharacters = db.session.query(
        Character,
        func.ts_headline('english', Character.name,
                         func.to_tsquery(o_string)).label('hname'),
        func.ts_headline('english', Character.publisher_name,
                         func.plainto_tsquery(o_string)).label('hpub'),
        func.ts_headline(
            'english', Character.appear,
            func.plainto_tsquery(o_string)).label('happear')).filter(
                or_(Character.tsvector_col.match(q) for q in queries)).all()

    apublishers = db.session.query(
        Publisher,
        func.ts_headline('english', Publisher.state,
                         func.to_tsquery(a_string)).label('hstate'),
        func.ts_headline('english', Publisher.name,
                         func.to_tsquery(a_string)).label('hname'),
        func.ts_headline('english', Publisher.address,
                         func.plainto_tsquery(a_string)).label('haddress'),
        func.ts_headline(
            'english', Publisher.city,
            func.plainto_tsquery(name)).label('hcity')).filter(
                and_(Publisher.tsvector_col.match(q) for q in queries)).all()
    opublishers = db.session.query(
        Publisher,
        func.ts_headline('english', Publisher.state,
                         func.to_tsquery(o_string)).label('hstate'),
        func.ts_headline('english', Publisher.name,
                         func.to_tsquery(o_string)).label('hname'),
        func.ts_headline('english', Publisher.address,
                         func.plainto_tsquery(o_string)).label('haddress'),
        func.ts_headline(
            'english', Publisher.city,
            func.plainto_tsquery(name)).label('hcity')).filter(
                or_(Publisher.tsvector_col.match(q) for q in queries)).all()
    ateams = db.session.query(
        Team,
        func.ts_headline('english', Team.name,
                         func.to_tsquery(a_string)).label('hname'),
        func.ts_headline('english', Team.publisher_name,
                         func.plainto_tsquery(a_string)).label('hpub'),
        func.ts_headline(
            'english', Team.appear,
            func.plainto_tsquery(a_string)).label('happear')).filter(
                and_(Team.tsvector_col.match(q) for q in queries)).all()
    oteams = db.session.query(
        Team,
        func.ts_headline('english', Team.name,
                         func.to_tsquery(o_string)).label('hname'),
        func.ts_headline('english', Team.publisher_name,
                         func.plainto_tsquery(o_string)).label('hpub'),
        func.ts_headline(
            'english', Team.appear,
            func.plainto_tsquery(o_string)).label('happear')).filter(
                or_(Team.tsvector_col.match(q) for q in queries)).all()
    avolumes = db.session.query(
        Volume,
        func.ts_headline('english', Volume.name,
                         func.to_tsquery(a_string)).label('hname'),
        func.ts_headline('english', Volume.publisher_name,
                         func.plainto_tsquery(a_string)).label('hpub'),
        func.ts_headline(
            'english', Volume.start_year,
            func.plainto_tsquery(a_string)).label('hstart')).filter(
                and_(Volume.tsvector_col.match(q) for q in queries)).all()
    ovolumes = db.session.query(
        Volume,
        func.ts_headline('english', Volume.name,
                         func.to_tsquery(o_string)).label('hname'),
        func.ts_headline('english', Volume.publisher_name,
                         func.plainto_tsquery(o_string)).label('hpub'),
        func.ts_headline(
            'english', Volume.start_year,
            func.plainto_tsquery(o_string)).label('hstart')).filter(
                or_(Volume.tsvector_col.match(q) for q in queries)).all()

    #print(publishers)
    #print(teams)
    #print(volumes)
    return render_template('search_result_template.html',
                           acharacters=acharacters,
                           ocharacters=ocharacters,
                           opublishers=opublishers,
                           apublishers=apublishers,
                           avolumes=avolumes,
                           ovolumes=ovolumes,
                           ateams=ateams,
                           oteams=oteams)
Exemplo n.º 27
0
 def cond_func(lang):
     return func.to_tsvector(lang, func.replace(target, ";", " ")).op("@@")(func.to_tsquery(lang, prepared_searchstring))
Exemplo n.º 28
0
expr3 = c2 / 5

expr4 = -c2

expr5 = ~(c2 == 5)

q = column("q", Boolean)
expr6 = ~q

expr7 = c1 + "x"

expr8 = c2 + 10

stmt = select(column("q")).where(lambda: column("g") > 5).where(c2 == 5)

expr9 = c1.bool_op("@@")(func.to_tsquery("some & query"))

if typing.TYPE_CHECKING:

    # as far as if this is ColumnElement, BinaryElement, SQLCoreOperations,
    # that might change.  main thing is it's SomeSQLColThing[bool] and
    # not 'bool' or 'Any'.
    # EXPECTED_RE_TYPE: sqlalchemy..*ColumnElement\[builtins.bool\]
    reveal_type(expr1)

    # EXPECTED_RE_TYPE: sqlalchemy..*ColumnClause\[builtins.str.?\]
    reveal_type(c1)

    # EXPECTED_RE_TYPE: sqlalchemy..*ColumnClause\[builtins.int.?\]
    reveal_type(c2)
Exemplo n.º 29
0
class UserSearch(object):
    def __init__(self, request):
        self.request = request
        self.page_size = 50
        self.frm = make_user_search_form(request)

    def get(self):
        if 'query' in self.request.GET and \
           self.request.GET['query'].strip() and \
           'clear_search' not in self.request.GET:
            return self.run_search()

        column = self.request.GET.get('column', 'username')
        order = self.request.GET.get('order', 'asc')

        # Capture the form values for re-use
        appstruct = {}
        appstruct['column'] = column
        appstruct['order'] = order

        query = self.request.db_session.query(UserProfile)
        orderby = self.get_order_by()
        query = query.order_by(orderby)

        results = query.all()
        paged = self.paginate_results(results)
        pager = paged.pager()
        sort_class = 'icon-arrow-down'
        if order == 'desc':
            sort_class = 'icon-arrow-up'
        return {
            'forms': [self.frm],
            'rendered_form': self.frm.render(appstruct=appstruct),
            'results': paged,
            'ran_search': False,
            'pager': pager,
            'column': column,
            'sort_class': sort_class,
        }

    def run_search(self):
        results = []
        pager = None
        ran_search = False

        try:
            controls = self.request.GET.items()
            appstruct = self.frm.validate(controls)
        except ValidationFailure, e:
            return {
                'forms': [self.frm],
                'rendered_form': e.render(),
                'results': results,
                'ran_search': ran_search,
                'pager': None
            }
        #XXX: always default to treating the query as a prefix query??
        tsquery = func.to_tsquery("'%s':*" % re.escape(appstruct['query']))
        # build the shared query bit
        query_select = select([tsquery.label('query')]).cte('query_select')
        # build the ordered-by clause, using the shared query
        orderby = self.get_order_by(query_select)
        res = self.request.db_session.query(UserProfile)
        res = res.filter(
            UserProfile.searchable_text.op('@@')(select([query_select.c.query
                                                         ])))
        res = res.order_by(orderby)

        results = res.all()
        paged = self.paginate_results(results)
        pager = paged.pager()
        ran_search = True
        sort_class = 'icon-arrow-down'
        if self.request.GET.get('order', '') == 'desc':
            sort_class = 'icon-arrow-up'
        return {
            'forms': [self.frm],
            'rendered_form': self.frm.render(appstruct=appstruct),
            'results': paged,
            'ran_search': ran_search,
            'pager': pager,
            'column': self.request.GET.get('column', 'username'),
            'sort_class': sort_class,
        }
Exemplo n.º 30
0
def get_exercises(favorited_by=None):
    '''Get exercise collection, if favorited_by is set then get the
    collection of favorites of the user.'''

    user_id = auth.current_user.id if auth.current_user else None

    # client requests favorites that are not his.
    if favorited_by and favorited_by != user_id:
        raise AuthorizationError

    search = request.args.get('search')
    category = request.args.get('category')
    order_by = request.args.get('order_by')
    author = request.args.get('author')

    orderfunc = desc
    if order_by and len(order_by) > 0 and order_by[-1] in '+ -'.split():
        if order_by[-1] == '+':
            orderfunc = asc
        order_by = order_by[:-1]

    query = Exercise.query

    if search:
        search_terms = (' | ').join(search.split())
        query = query.add_columns(func.ts_rank(
            Exercise.tsv, func.to_tsquery(search_terms)).label('search_rank')).\
            filter(Exercise.tsv.match(search_terms))

        if order_by == 'relevance':
            query = query.order_by(nullslast(orderfunc('search_rank')))

    if user_id:
        user_rating = aliased(Rating, name='user_rating')

        query = query.add_entity(user_rating).\
            outerjoin(user_rating, and_(user_rating.exercise_id == Exercise.id,
                                        user_rating.user_id == user_id))

        if order_by == 'user_rating':
            query = query.order_by(nullslast(orderfunc(user_rating.rating)))
        elif order_by == 'user_fun_rating':
            query = query.order_by(nullslast(orderfunc(user_rating.fun)))
        elif order_by == 'user_effective_rating':
            query = query.order_by(nullslast(orderfunc(user_rating.effective)))
        elif order_by == 'user_clear_rating':
            query = query.order_by(nullslast(orderfunc(user_rating.clear)))

        # when if favorited_by is not None then we only want the user favorites
        # and isouter will be set to False. Meaning we will do an inner join If
        # favorited_by is None then isouter will be True and we will do an
        # outer join meaning we want to know which exercises the user favorited
        # but we want all of them.
        isouter = not bool(favorited_by)

        # include a column from the UserFavoriteExercise table or `0`.
        # this will get serialized as a Boolean to signify favorited or not.
        query = query.\
            add_columns(func.coalesce(UserFavoriteExercise.exercise_id, 0).label('favorited')).\
            join(UserFavoriteExercise,
                 and_(UserFavoriteExercise.exercise_id == Exercise.id,
                      UserFavoriteExercise.user_id == user_id),
                 isouter=isouter)

    if author:
        query = query.join(
            User, and_(User.id == Exercise.author_id, User.username == author))

    if category:
        category = parse_query_params(request.args, key='category')
        query = query.join(Category).filter(Category.name.in_(category))

    if 'author' in parse_query_params(request.args, key='expand'):
        query = query.options(joinedload(Exercise.author))

    if order_by in ['average_rating', 'rating']:
        query = query.order_by(nullslast(orderfunc(Exercise.avg_rating)))
    elif order_by == 'average_fun_rating':
        query = query.order_by(nullslast(orderfunc(Exercise.avg_fun_rating)))
    elif order_by == 'average_clear_rating':
        query = query.order_by(nullslast(orderfunc(Exercise.avg_clear_rating)))
    elif order_by == 'average_effective_rating':
        query = query.order_by(
            nullslast(orderfunc(Exercise.avg_effective_rating)))
    elif order_by == 'popularity':
        query = query.order_by(nullslast(orderfunc(Exercise.popularity)))
    elif order_by == 'updated_at':
        query = query.order_by(orderfunc(Exercise.updated_at))
    else:
        query = query.order_by(orderfunc(Exercise.created_at))

    page = Pagination(request, query=query)
    return Serializer(ExerciseSchema, request.args).dump_page(page)
Exemplo n.º 31
0
 def __eq__(self, other):
     return self.op('@@')(func.to_tsquery(other))
Exemplo n.º 32
0
 def match(column, language='german'):
     return column.op('@@')(func.to_tsquery(language, term))
Exemplo n.º 33
0
 def cond_func(lang):
     tsvector = func.to_tsvector(lang, func.replace(target, ";", " "))
     tsquery = func.to_tsquery(lang, prepared_searchstring)
     return tsvector.op("@@")(tsquery)
Exemplo n.º 34
0
def my_bookmarks() -> flask.Response:
    # FIXME: This viewfunc really needs to get split up and work via the data
    # layer to get what it wants.
    page_size = flask.current_app.config["PAGE_SIZE"]
    page = int(flask.request.args.get("page", "1"))
    offset = (page - 1) * page_size
    user = get_current_user()
    query = db.session.query(SQLABookmark).filter(
        SQLABookmark.user_uuid == user.user_uuid
    )

    if "q" in flask.request.args:
        query = query.outerjoin(FullText, FullText.url_uuid == SQLABookmark.url_uuid)

        search_str = flask.request.args["q"]
        tquery_str = parse_search_str(search_str)
        log.debug('search_str, tquery_str = ("%s", "%s")', search_str, tquery_str)

        # necessary to coalesce this as there may be no fulltext
        fulltext = func.coalesce(FullText.tsvector, func.to_tsvector(""))

        combined_tsvector = (
            func.to_tsvector(SQLABookmark.title)
            .op("||")(func.to_tsvector(SQLABookmark.description))
            .op("||")(fulltext)
        )
        tsquery = func.to_tsquery(tquery_str)
        query = query.filter(combined_tsvector.op("@@")(tsquery))
        query = query.order_by(func.ts_rank(combined_tsvector, tsquery, 1))
        page_title = search_str
    else:
        page_title = "Quarchive"

    if page > 1:
        page_title += " (page %s)" % page

    # omit deleted bookmarks
    query = query.filter(~SQLABookmark.deleted)

    sqla_objs = (
        query.order_by(SQLABookmark.created.desc()).offset(offset).limit(page_size)
    )

    prev_page_exists = page > 1
    next_page_exists: bool = db.session.query(
        query.order_by(SQLABookmark.created.desc()).offset(offset + page_size).exists()
    ).scalar()

    bookmarks = []
    for sqla_obj in sqla_objs:
        url = sqla_obj.url_obj.to_url()
        bookmarks.append((url, bookmark_from_sqla(url.to_string(), sqla_obj)))
    return flask.make_response(
        flask.render_template(
            "my_bookmarks.html",
            page_title=page_title,
            bookmarks=bookmarks,
            page=page,
            prev_page_exists=prev_page_exists,
            next_page_exists=next_page_exists,
            q=flask.request.args.get("q"),
        )
    )
Exemplo n.º 35
0
 def search_filter(cls, term):
     return (func.to_tsvector("simple", cls.name).op("||")(func.to_tsvector(
         "simple",
         cls.slug))).op("@@")(func.to_tsquery("simple", "'" + term + "':*"))