def get(self):
        args = self.get_parser.parse_args()

        queryset = Project.query

        if args.query:
            queryset = queryset.filter(
                or_(
                    func.lower(Project.name).contains(args.query.lower()),
                    func.lower(Project.slug).contains(args.query.lower()),
                ),
            )

        if args.status:
            queryset = queryset.filter(
                Project.status == ProjectStatus[args.status]
            )

        if args.sort == 'name':
            queryset = queryset.order_by(Project.name.asc())
        elif args.sort == 'date':
            queryset = queryset.order_by(Project.date_created.asc())

        project_list = list(queryset)

        context = []
        if project_list:
            latest_build_results = get_latest_builds_query(project_list)
            latest_build_map = dict(
                zip([b.project_id for b in latest_build_results],
                    self.serialize(latest_build_results))
            )

            passing_build_map = {}
            missing_passing_builds = set()
            for build in latest_build_results:
                if build.result == Result.passed:
                    passing_build_map[build.project_id] = build
                else:
                    passing_build_map[build.project_id] = None
                    missing_passing_builds.add(build.project_id)

            if missing_passing_builds:
                passing_build_results = get_latest_builds_query(
                    project_list, result=Result.passed,
                )
                passing_build_map.update(dict(
                    zip([b.project_id for b in passing_build_results],
                        self.serialize(passing_build_results))
                ))

            for project, data in zip(project_list, self.serialize(project_list)):
                # TODO(dcramer): build serializer is O(N) for stats
                data['lastBuild'] = latest_build_map.get(project.id)
                data['lastPassingBuild'] = passing_build_map.get(project.id)
                context.append(data)

        return self.respond(context)

    def search(self, string, show_error):
        """
        Search the database for a given item.

        :param string: The search string
        :param show_error: The error string to be show.
        """
        search = '%' + string.lower() + '%'
        search_results = self.plugin.db_manager.get_all_objects(CustomSlide,
                                                                or_(func.lower(CustomSlide.title).like(search),
                                                                    func.lower(CustomSlide.text).like(search)),
                                                                order_by_ref=CustomSlide.title)
        return [[custom.id, custom.title] for custom in search_results]

def tag_search(text, count=5):
    """Search in the tag_search_items table (for location tags)."""
    QTag = aliased(LocationTag)
    QParent = aliased(LocationTag)
    text = text.lower().strip()
    query = meta.Session.query(TagSearchItem)\
        .join(QTag)\
        .outerjoin((QParent, QParent.id==QTag.parent_id))\
        .filter(TagSearchItem.terms.op('@@')(func.plainto_tsquery(text)))\
        .order_by(or_(func.lower(func.btrim(QParent.title)) == text, func.lower(func.btrim(QParent.title_short)) == text).desc())\
        .order_by(or_(func.lower(func.btrim(QTag.title)) == text, func.lower(func.btrim(QTag.title_short)) == text).desc())\
        .order_by(func.ts_rank_cd(TagSearchItem.terms, func.plainto_tsquery(text)))
    if count is not None:
        query = query.limit(count)
    return query.all()

    def get(self, project_id):
        project = Project.get(project_id)
        if project is None:
            return '', 404

        args = self.get_parser.parse_args()

        queryset = Plan.query.filter(
            Plan.project_id == project.id,
        )

        if args.query:
            queryset = queryset.filter(
                func.lower(Plan.label).contains(args.query.lower()),
            )

        if args.status:
            queryset = queryset.filter(
                Plan.status == PlanStatus[args.status],
            )

        if args.sort == 'name':
            queryset = queryset.order_by(Plan.label.asc())
        elif args.sort == 'date':
            queryset = queryset.order_by(Plan.date_created.asc())

        return self.paginate(queryset)

async def find_synonym(name, what):
    async with engine.acquire() as conn:
         synonym = model.Synonym.__table__
         res=await conn.execute(select([synonym.c.our_name]).where(and_(func.lower(synonym.c.other_name) == name.lower(), 
                                                                   synonym.c.category == what)))
         s = await res.fetchone()
         if s: return s[0]

 def __init__(self, word):
     if isinstance(word, basestring):
         self.word = word.lower()
     elif isinstance(word, CaseInsensitiveWord):
         self.word = word.word
     else:
         self.word = func.lower(word)

    def handle_list(self, event, owner, status, milestone):
        session = ibid.databases.trac()

        status = status or 'open'
        if status.lower() == 'open':
            statuses = (u'new', u'assigned', u'reopened')
        else:
            statuses = (status.lower(),)

        query = session.query(Ticket).filter(Ticket.status.in_(statuses))

        if owner:
            if owner.lower() == 'my':
                owner = event.sender['nick']
            else:
                owner = owner.lower().replace("'s", '')
            query = query.filter(func.lower(Ticket.owner)==(owner.lower()))

        if milestone:
            query = query.filter_by(milestone=milestone)

        tickets = query.order_by(Ticket.id).all()

        if len(tickets) > 0:
            event.addresponse(u', '.join(['%s (%s): "%s"' % (ticket.id, ticket.owner, ticket.summary) for ticket in tickets]))
        else:
            event.addresponse(u"No tickets found")

        session.close()

def get_results(
    transcript_name, minimum_CG, maximum_CG, maximum_offtarget,
    scaffold, immunostimulatory
):
    try:
        stored_input = db_session.query(InputData).filter(
            func.lower(InputData.transcript_name) == transcript_name.lower(),
            InputData.minimum_CG == minimum_CG,
            InputData.maximum_CG == maximum_CG,
            InputData.maximum_offtarget == maximum_offtarget,
            func.lower(InputData.scaffold) == scaffold.lower(),
            func.lower(InputData.immunostimulatory) == immunostimulatory.lower()
        ).outerjoin(InputData.results).one()
    except NoResultFound:
        return None
    return [result.as_json() for result in stored_input.results]

def frames_by_scaffold(scaffold):
    if scaffold == 'all':
        return db_session.query(Backbone).all()

    return db_session.query(Backbone).filter(
        func.lower(Backbone.name) == scaffold.lower()
    ).all()

    def get(self, build_id):
        build = Build.query.get(build_id)
        if build is None:
            return '', 404

        args = self.parser.parse_args()

        test_list = TestCase.query.options(
            contains_eager('job')
        ).join(
            Job, TestCase.job_id == Job.id,
        ).filter(
            Job.build_id == build.id,
        )

        if args.query:
            test_list = test_list.filter(
                func.lower(TestCase.name).contains(args.query.lower()),
            )

        if args.result:
            test_list = test_list.filter(
                TestCase.result == Result[args.result],
            )

        if args.sort == 'duration':
            sort_by = TestCase.duration.desc()
        elif args.sort == 'name':
            sort_by = TestCase.name.asc()
        elif args.sort == 'retries':
            sort_by = TestCase.reruns.desc()

        test_list = test_list.order_by(sort_by)

        return self.paginate(test_list, max_per_page=None)

async def find_series(ser):
    async with engine.acquire() as conn:
        series = model.Series.__table__
        res = await conn.execute(select([series.c.id, series.c.title]).where(func.lower(series.c.title) == ser['title'].lower()))
        s = await res.fetchone()
        if s:
            return {'id': s[0], 'title': s[1]}

    def extract_users(session, comment_txt):
        """ extract referenced users from a message

        replaces "@name" with "@NN" where NN is userid (because username might change, but id won't)
        returns new comment string and list of resolved users
        """

        userpat = re.compile("@([a-zA-Z0-9_]+)", re.I)

        # look them all up in the db
        usernames = set(m.group(1) for m in userpat.finditer(comment_txt))
        users = (
            session.query(UserAccount)
            .filter(func.lower(UserAccount.username).in_([u.lower() for u in usernames]))
            .all()
        )
        user_lookup = dict((u.username.lower(), u) for u in users)

        def user_replace(m):
            if m.group(1).lower() not in user_lookup:
                return m.group(0)
            user = user_lookup[m.group(1).lower()]
            return u"@%d" % (user.id,)

        comment_txt = userpat.sub(user_replace, comment_txt)

        return comment_txt, users

    def get(self):
        args = self.get_parser.parse_args()

        queryset = Repository.query

        if args.query:
            queryset = queryset.filter(
                func.lower(Repository.url).contains(args.query.lower()),
            )

        if args.backend:
            queryset = queryset.filter(
                Repository.backend == RepositoryBackend[args.backend]
            )

        if args.status:
            queryset = queryset.filter(
                Repository.status == RepositoryStatus[args.status],
            )

        if args.sort == 'url':
            queryset = queryset.order_by(Repository.url.asc())
        elif args.sort == 'date':
            queryset = queryset.order_by(Repository.date_created.asc())

        return self.paginate(queryset)

def get_user_by_username(db_conn, username):
    s = select(['id', 'name', 'email', 'is_superuser']) \
            .select_from(user_tbl) \
            .where(func.lower(user_tbl.c.name) == username.lower())
    rs = db_conn.execute(s)
    assert rs.rowcount == 1
    return rs.fetchone()

def get_by_name_query(table, name):
    """Finds a single row in the given table by name, ignoring case.

    Don't use this for Pokémon!  Use `pokemon()`, as it knows about forms.
    """
    q = pokedex_session.query(table).filter(func.lower(table.name)
                                            == name.lower())

    return q

def search_users(name):
    ''' Search users in a team'''
    if not require.team.read():
        abort(403)

    team = cached_teams.get_team(name)
    form = SearchForm(request.form)
    users = db.session.query(User).all()

    if request.method == 'POST' and form.user.data:
        query = '%' + form.user.data.lower() + '%'
        founds = db.session.query(User)\
                  .filter(or_(func.lower(User.name).like(query),
                              func.lower(User.fullname).like(query)))\
                  .all()

        if not founds:
            msg = gettext('Ooops!  We didn\'t find a user matching your query:')
            flash(msg)

            return render_template(
                '/team/search_users.html',
                founds = [],
                team = team,
                title = gettext('Search name of User'))
        else:
            for found in founds:
                user2team = User2Team.query\
                                .filter(User2Team.team_id==team.id)\
                                .filter(User2Team.user_id==found.id)\
                                .first()
                found.belong = (1, 0)[user2team is None]

            return render_template(
                '/team/search_users.html',
                founds = founds,
                team = team,
                title = gettext('Search User'))

    return render_template(
        '/team/search_users.html',
        founds = [],
        team = team,
        title = gettext('Search User'))

 def _after_create(target, conn, **kw):
     assert target is column.table
     col_func = func.indico_unaccent(func.lower(column))
     index_kwargs = {}
     if not current_app.config['TESTING'] and has_extension(conn, 'pg_trgm'):
         index_kwargs = {'postgresql_using': 'gin',
                         'postgresql_ops': {col_func.key: 'gin_trgm_ops'}}
     elif not current_app.config['TESTING']:
         print 'Warning: pg_trgm extension is not available'
     Index(conv('ix_{}_{}_unaccent'.format(column.table.name, column.name)), col_func, **index_kwargs).create(conn)

    def get_artist2scrobbles(user, min_count, max_uts=None):
        where = Scrobble.user == user
        if max_uts is not None:
            where = where & (Scrobble.uts <= max_uts)

        return defaultdict(lambda: 0,
                           session.query(func.lower(Scrobble.artist), func.count(Scrobble.id)).\
                                   group_by(Scrobble.artist).\
                                   filter(where).\
                                   having(func.count(Scrobble.id) >= min_count))

def search_teams(type):
    ''' Search Teams '''
    if not require.team.read():
        abort(403)
    
    title = gettext('Search name of teams')
    form = SearchForm(request.form)
    teams = db.session.query(Team).all()

    if request.method == 'POST' and form.user.data:
        query = '%' + form.user.data.lower() + '%'
        if type == 'public':
            founds = db.session.query(Team)\
                       .filter(func.lower(Team.name).like(query))\
                       .filter(Team.public == True)\
                       .all()
        else:
            founds = db.session.query(Team)\
                       .join(User2Team)\
                       .filter(func.lower(Team.name).like(query))\
                       .filter(User2Team.user_id == current_user.id)\
                       .all()
        if not founds:
            msg = gettext('Ooops! We didn\'t find a team matching your query:')
            flash(msg)

            return render_template(
                '/team/search_teams.html',
                founds= [],
                team_type = type,
                title=gettext('Search Team'))
        else:
            return render_template(
                '/team/search_teams.html',
                founds = founds,
                team_type = type,
                title = gettext('Search Team'))

    return render_template(
            '/team/search_teams.html',
            found = [],
            team_type = type,
            title = gettext('Search Team'))

    def js_add_tag(self):
        if hasattr(self, 'form_result'):
            json = {'success': '', 'error': ''}
            parent = None
            created = None
            location = self.form_result['location']
            newlocation = self.form_result['newlocation']
            for index, item in enumerate(newlocation):
                if item['title'] == '' and location[index] != '':
                    try:
                        parent = meta.Session.query(LocationTag
                                ).filter(LocationTag.title == location[index]
                                ).filter(LocationTag.parent == parent
                                ).one()
                    except: # XXX bare except
                        break
                else:
                    try:
                        InURLValidator.to_python(item['title_short'])

                        existing = meta.Session.query(LocationTag).filter(or_(func.lower(LocationTag.title_short) == item['title_short'].lower(),
                                                                              func.lower(LocationTag.title) == item['title'].lower()))\
                            .filter(LocationTag.parent == parent).first()
                        if existing is not None:
                            if existing.title.lower() == item['title'].lower():
                                json['error'] = _('The entry already exists')
                                break
                            else:
                                json['error'] = _('Choose a different short title')
                                break
                    except: # XXX bare except
                        json['error'] = _('The short title must contain no spaces')
                        break

                    created = LocationTag(item['title'], item['title_short'], u'', parent, confirmed=False)

                    meta.Session.add(created)
                    meta.Session.commit()
                    break
            if created is not None:
                json['success'] = created.title
            return json