def _sticky_from_report(self, req, data):
     ids = []
     for value_for_group, row_group in data['row_groups']:
         ids.extend([int(row['id']) for row in row_group
                     if row['resource'] and
                        row['resource'].realm == 'ticket' and
                        'TICKET_VIEW' in req.perm(row['resource'])])
     cols = ['id', 'summary', 'type']
     for col in self._fields:
         if col not in cols:
             cols.append(col)
     if hasattr(self.env, 'get_read_db'):
         db = self.env.get_read_db()
     else:
         db = self.env.get_db_cnx()
     start = 0
     count = 100
     tickets = []
     while start < len(ids):
         constraints = [{
             'id': [str(id) for id in ids[start:start + count]],
         }]
         query = Query(self.env, constraints=constraints, cols=cols, max=0)
         tickets.extend(query.execute(req, db))
         start += count
     tickets_dict = dict((int(ticket['id']), ticket) for ticket in tickets)
     tickets = [tickets_dict[id] for id in ids if id in tickets_dict]
     return self._sticky(req, tickets)

    def _get_permitted_tickets(self, req, constraints=None, columns=None):
        """
        If we don't pass a list of column/field values, the Query module 
        defaults to the first seven colums - see get_default_columns().
        """

        if columns is None:
            columns = []
        else:
            columns = columns[:] # avoid mutating callers list
        # make sure that we get certain ticket fields
        for f in ('summary', 'type', 'remaininghours', 'effort', 'date'):
            if f not in columns:
                columns.append(f)

        # what field data should we get
        query = Query(self.env, constraints=constraints, max=0, cols=columns)
        tickets = []
        for ticket in query.execute(req):
            if 'TICKET_VIEW' in req.perm('ticket', ticket['id']):
                for k in ('effort', 'remaininghours'):
                    try:
                        ticket[k] = float(ticket[k])
                    except KeyError:
                        pass
                    except TypeError:
                        ticket[k] = 0.0
                tickets.append(ticket)
        return tickets

def test_trac(build_trac_env):
    """Create a sample Ticket and check if
    the Ticket-Count == 1
    """
    env = build_trac_env
    env_dict = {}
    env_dict['trac.base_url'] = 'http://127.0.0.1:8000/trac/'

    # Create the Request
    req = Request(env_dict, None)
    req.authname = 'JohnDoe'
    req.perm = 'TICKET_ADMIN'
    req.tz = 42
    req.locale = 42

    # Create a Ticket
    t = model.Ticket(env)
    t['summary'] = 'Summary'
    t['description'] = 'Description'
    t['reporter'] = req.authname
    t['status'] = 'new'
    t['resolution'] = ''
    t.insert()

    # Count Tickets
    q = Query(env)
    qc = q.count(req)
    assert qc == 1

 def test_repeated_constraint_field(self):
     like_query = Query.from_string(self.env, "owner!=someone|someone_else", order="id")
     query = Query.from_string(self.env, "owner!=someone&owner!=someone_else", order="id")
     like_sql, like_args = like_query.get_sql()
     sql, args = query.get_sql()
     self.assertEqualSQL(sql, like_sql)
     self.assertEqual(args, like_args)
     tickets = query.execute(self.req)

    def _get_product_info(self, product, href, resource, max_):
        penv = ProductEnvironment(self.env, product.prefix)
        results = []

        # some queries return a list/tuple, some a generator,
        # hence count() to get the result length
        def count(iter_):
            try:
                return len(iter_)
            except TypeError:
                return sum(1 for _ in iter_)

        query = resource['type'].select(penv)
        for q in itertools.islice(query, max_):
            q.url = href(resource['name'], q.name) \
                if resource.get('hrefurl') \
                else Query.from_string(penv,
                    '%s=%s&%s&col=%s' % (resource['name'], q.name,
                                         self.COMMON_QUERY, resource['name'])
            ).get_href(href)
            q.ticket_count = penv.db_query("""
                SELECT COUNT(*) FROM ticket WHERE ticket.%s='%s'
                AND ticket.status <> 'closed'
                """ % (resource['name'], q.name))[0][0]

            results.append(q)

        # add a '(No <milestone/component/version>)' entry if there are
        # tickets without an assigned resource in the product
        ticket_count = penv.db_query(
            """SELECT COUNT(*) FROM ticket WHERE %s=''
               AND status <> 'closed'""" % (resource['name'],))[0][0]
        if ticket_count != 0:
            q = resource['type'](penv)
            q.name = '(No %s)' % (resource['name'],)
            q.url = Query.from_string(penv,
               'status=!closed&col=id&col=summary&col=owner'
               '&col=status&col=priority&order=priority&%s='
               % (resource['name'],)
            ).get_href(href)
            q.ticket_count = ticket_count
            results.append(q)

        results.sort(key=lambda x: x.ticket_count, reverse=True)

        # add a link to the resource list if there are
        # more than max resources defined
        if count(query) > max_:
            q = resource['type'](penv)
            q.name = _('... more')
            q.ticket_count = None
            q.url = href(resource['name']) if resource.get('hrefurl') \
                else href.dashboard()
            results.append(q)

        return results

    def test_all_ordered_by_id_verbose(self):
        query = Query(self.env, order='id', verbose=1)
        sql, args = query.get_sql()
        self.assertEqualSQL(sql,
"""SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.reporter AS reporter,t.description AS description,t.time AS time,t.changetime AS changetime,priority.value AS priority_value
FROM ticket AS t
  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)
ORDER BY COALESCE(t.id,0)=0,t.id""")
        self.assertEqual([], args)
        tickets = query.execute(self.req)

    def test_all_ordered_by_id_desc(self):
        query = Query(self.env, order='id', desc=1)
        sql, args = query.get_sql()
        self.assertEqualSQL(sql,
"""SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.product AS product,t.time AS time,t.changetime AS changetime,priority.value AS priority_value
FROM ticket AS t
  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)
ORDER BY COALESCE(t.id,0)=0 DESC,t.id DESC""")
        self.assertEqual([], args)
        tickets = query.execute(self.req)

    def test_all_ordered_by_priority_desc(self):
        query = Query(self.env, desc=1) # priority is default order
        sql, args = query.get_sql()
        self.assertEqual(sql,
"""SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value
FROM ticket AS t
  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)
ORDER BY COALESCE(t.priority,'')='' DESC,priority.value DESC,t.id""")
        self.assertEqual([], args)
        tickets = query.execute(Mock(href=self.env.href))

    def __init__(self, env, **kwargs):
        self.env = env

        if kwargs:
            querystring = "type=testrun"
            for key, value in kwargs.iteritems():
                querystring += "&%s=%s" % (key, value)
            self.query = TicketQuery.from_string(env, querystring)
        else:
            # query * testrun tickets
            self.query = TicketQuery.from_string(env, "type=testrun")

 def query(self, req, qstr):
     """Retrieve all tickets associated with a query string"""
     q = Query.from_string(self.env, qstr)
     filters = Query.to_string(q)
     ticket_realm = Resource('ticket')
     out = []
     for t in q.execute(req):
         tid = t['id']
         if 'TICKET_VIEW' in req.perm(ticket_realm(id=tid)):
             out.append(tid)
     return filters, out

    def test_all_ordered_by_priority_desc(self):
        query = Query(self.env, desc=1) # priority is default order
        sql, args = query.get_sql()
        self.assertEqualSQL(sql,
"""SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value
FROM ticket AS t
  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)
ORDER BY COALESCE(priority.value,'')='' DESC,%(cast_priority)s DESC,t.id""" % {
          'cast_priority': self.env.get_db_cnx().cast('priority.value', 'int')})
        self.assertEqual([], args)
        tickets = query.execute(self.req)

    def test_grouped_by_priority(self):
        query = Query(self.env, group='priority')
        sql, args = query.get_sql()
        self.assertEqualSQL(sql,
"""SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.product AS product,t.milestone AS milestone,t.priority AS priority,t.time AS time,t.changetime AS changetime,priority.value AS priority_value
FROM ticket AS t
  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)
ORDER BY COALESCE(priority.value,'')='',%(cast_priority)s,t.id""" % {
          'cast_priority': self.env.get_read_db().cast('priority.value', 'int')})
        self.assertEqual([], args)
        tickets = query.execute(self.req)

    def test_all_grouped_by_milestone_desc(self):
        query = Query(self.env, order='id', group='milestone', groupdesc=1)
        sql, args = query.get_sql()
        self.assertEqualSQL(sql,
"""SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.product AS product,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value
FROM ticket AS t
  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)
  LEFT OUTER JOIN milestone ON (milestone.name=milestone)
ORDER BY COALESCE(t.milestone,'')='' DESC,COALESCE(milestone.completed,0)=0 DESC,milestone.completed DESC,COALESCE(milestone.due,0)=0 DESC,milestone.due DESC,t.milestone DESC,COALESCE(t.id,0)=0,t.id""")
        self.assertEqual([], args)
        tickets = query.execute(self.req)

    def test_all_ordered_by_version_desc(self):
        query = Query(self.env, order='version', desc=1)
        sql, args = query.get_sql()
        self.assertEqualSQL(sql,
"""SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.version AS version,t.time AS time,t.changetime AS changetime,priority.value AS priority_value
FROM ticket AS t
  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)
  LEFT OUTER JOIN version ON (version.name=version)
ORDER BY COALESCE(t.version,'')='' DESC,COALESCE(version.time,0)=0 DESC,version.time DESC,t.version DESC,t.id""")
        self.assertEqual([], args)
        tickets = query.execute(self.req)

    def test_grouped_by_custom_field(self):
        self.env.config.set('ticket-custom', 'foo', 'text')
        query = Query(self.env, group='foo', order='id')
        sql, args = query.get_sql()
        self.assertEqual(sql,
"""SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.milestone AS milestone,t.time AS time,t.changetime AS changetime,priority.value AS priority_value,foo.value AS foo
FROM ticket AS t
  LEFT OUTER JOIN ticket_custom AS foo ON (id=foo.ticket AND foo.name='foo')
  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)
ORDER BY COALESCE(foo.value,'')='',foo.value,COALESCE(t.id,0)=0,t.id""")
        self.assertEqual([], args)
        tickets = query.execute(Mock(href=self.env.href))

    def test_template_data(self):
        req = Mock(href=self.env.href, perm=MockPerm(), authname="anonymous", tz=None, locale=None)
        context = web_context(req, "query")

        query = Query.from_string(self.env, "owner=$USER&order=id")
        tickets = query.execute(req)
        data = query.template_data(context, tickets, req=req)
        self.assertEqual(["anonymous"], data["clauses"][0]["owner"]["values"])

        query = Query.from_string(self.env, "owner=$USER&order=id")
        tickets = query.execute(req)
        data = query.template_data(context, tickets)
        self.assertEqual(["$USER"], data["clauses"][0]["owner"]["values"])

    def test_grouped_by_custom_field(self):
        self.env.config.set('ticket-custom', 'foo', 'text')
        query = Query(self.env, group='foo', order='id')
        sql, args = query.get_sql()
        foo = self.env.get_read_db().quote('foo')
        self.assertEqualSQL(sql,
"""SELECT t.id AS id,t.summary AS summary,t.owner AS owner,t.type AS type,t.status AS status,t.priority AS priority,t.product AS product,t.time AS time,t.changetime AS changetime,priority.value AS priority_value,%s.value AS %s
FROM ticket AS t
  LEFT OUTER JOIN ticket_custom AS %s ON (id=%s.ticket AND %s.name='foo')
  LEFT OUTER JOIN enum AS priority ON (priority.type='priority' AND priority.name=priority)
ORDER BY COALESCE(%s.value,'')='',%s.value,COALESCE(t.id,0)=0,t.id""" %
        ((foo,) * 7))
        self.assertEqual([], args)
        tickets = query.execute(self.req)

    def test_template_data(self):
        req = Mock(href=self.env.href, perm=MockPerm(), authname='anonymous',
                   tz=None, locale=None)
        context = web_context(req, 'query')

        query = Query.from_string(self.env, 'owner=$USER&order=id')
        tickets = query.execute(req)
        data = query.template_data(context, tickets, req=req)
        self.assertEqual(['anonymous'], data['clauses'][0]['owner']['values'])

        query = Query.from_string(self.env, 'owner=$USER&order=id')
        tickets = query.execute(req)
        data = query.template_data(context, tickets)
        self.assertEqual(['$USER'], data['clauses'][0]['owner']['values'])

    def get_profile_actions(self, req, user):
        """
        Return list of actions
        """
        actions = []
        homeperm = self._get_home_perm(req)
        uresource = Resource('user', user.id)

        # Project settings for own account
        if user.username == req.authname:
            actions.append((-40, tag.a(_('View your projects'), href=homeperm.env.href('myprojects'))))
            actions.append((0, tag.a(_('Edit your settings'), href=homeperm.env.href('prefs'))))
        # View other user profile
        else:
            actions.append((-50, tag.a(_('View service profile'), href=homeperm.env.href('user', user.username))))

        # If user can fully manage account
        if homeperm.has_permission('USER_EDIT', uresource):
            label = 'Manage your account' if user.username == req.authname else 'Manage account'
            actions.append((-2, tag.a(_(label), href=homeperm.env.href('admin/users/manage', username=user.username))))

        # Tickets assigned to or created by user (if component is enabled and user has permissions to view them)
        # Note: href.kwargs cannot be used because of reserved word 'or' and un-ordered nature of dict
        if (self.env.is_component_enabled('trac.ticket.query.QueryModule') or
            self.env.is_component_enabled('multiproject.project.tickets.viewtickets.QueryModuleInterceptor')) \
            and 'TICKET_VIEW' in req.perm:
            qstring = 'owner={0}&or&reporter={0}&group=status'.format(user.username)
            query = Query.from_string(self.env, qstring)
            actions.append((5, (tag.a(_('View tickets'), href=query.get_href(req.href)))))

        return actions

    def expand_macro(self, formatter, name, content):
        req = formatter.req

        # Parse arguments
        args, kwargs = parse_args(content, strict=False)
        assert not args and not ('status' in kwargs or 'format' in kwargs), \
          "Invalid input!"
        # hack the `format` kwarg in order to display all-tickets stats
        # when no kwargs are supplied
        kwargs['format'] = 'count'

        # special case for values equal to 'self': replace with current
        # ticket number, if available
        for key in kwargs.keys():
            if kwargs[key] == 'self':
                current_ticket = self._this_ticket(req)
                if current_ticket: kwargs[key] = current_ticket

        # Create & execute the query string
        qstr = '&'.join(['%s=%s' % item
                                for item in kwargs.iteritems()])
        query = Query.from_string(self.env, qstr, max=0)

        # Calculate stats
        qres = query.execute(req)
        tickets = apply_ticket_permissions(self.env, req, qres)

        stats = get_ticket_stats(self.stats_provider, tickets)
        stats_data = query_stats_data(req, stats, query.constraints)

        # ... and finally display them
        add_stylesheet(req, 'common/css/roadmap.css')
        chrome = Chrome(self.env)
        return chrome.render_template(req, 'progressmeter.html', stats_data,
                                      fragment=True)